hDW0DctM

1CREATE OR REPLACE FUNCTION f_validate_structure_test_item_bank_upload(character varying, bigint, bigint, character varying, character varying)
2	RETURNS void AS
3	$BODY$
4DECLARE
5
6  pSessionId				ALIAS FOR $1;
7  pRecordOwnerId			ALIAS FOR $2;
8  pUploadId				ALIAS FOR $3;
9  pUsername				ALIAS FOR $4;
10  pDatetime				ALIAS FOR $5;
11
12  vYes					character varying;
13  vNo					character varying;
14  vInProgress				character varying;
15  vEmpty				character varying;
16  vSuccess				character varying;
17  vTitleItemCode			character varying;
18  vTitleItemName			character varying;
19  vTitleItemId				bigint;
20
21BEGIN
22	-- TODO: function untuk migrasi dari table staging ke table asli nya
23	
24	vYes = 'Y';
25	vNo = 'N';
26	vInProgress = 'I';
27	vEmpty = ' ';
28	vSuccess = 'S';
29	
30	RAISE NOTICE 'Function dimulai';
31	RAISE NOTICE 'Session ID: %', pSessionId;
32	RAISE NOTICE 'Record Owner ID: %', pRecordOwnerId;
33
34	/*
35	 * Breakdown:
36	 * 1. Validasi record_owner_id harus terdaftar di mstr.m_record_owner
37	 * 2. Validasi title_item_code, title_item_name tidak boleh kosong
38	 * 3. Validasi title_item_code dan record_owner_id belum terdaftar di m_title_item
39	 * 4. Validasi structure_test_code dan record_owner_id belum terdaftar di m_structure_test
40	 * 5. Validasi item_bank_desc, structure_test_id dan record_owner_id belum terdaftar di m_item_bank
41	 * 6. Validasi ctgr_test_id terdaftar di m_ctgr_test
42	 * 7. Validasi structure_test_id terdaftar di m_structure_test
43	 * 8. Validasi structure_test_code, structure_test_name tidak boleh kosong
44	 * 9. Validasi item_bank_desc, item_answer_type, item_bank_seq tidak boleh kosong
45	 */
46	
47	-- 1. Validasi record_owner_id harus terdaftar di mstr.m_record_owner
48	UPDATE upl_header A 
49	   SET message = message || 'Record Owner Id tidak terdaftar di m_record_owner, '
50	   WHERE session_id = pSessionId 
51	   AND upload_id = pUploadId
52	   AND NOT EXISTS (
53	   		SELECT 1 FROM mstr.m_record_owner B WHERE B.record_owner_id = pRecordOwnerId
54	   );
55
56	-- 2. Validasi title_item_code, title_item_name tidak boleh kosong
57	WITH uplHeader as (
58		SELECT title_item_code, title_item_name FROM upl_header
59		WHERE record_owner_id = pRecordOwnerId 
60		AND session_id = pSessionId 
61		AND upload_id = pUploadId
62	)
63	SELECT title_item_code, title_item_name INTO vTitleItemCode, vTitleItemName FROM uplHeader;
64
65	IF(vTitleItemCode = '') THEN
66		UPDATE upl_header A 
67			SET message = message || 'Title Item Code Tidak boleh kosong, '
68			WHERE session_id = pSessionId 
69			AND upload_id = pUploadId;
70	ELSIF (vTitleItemName = '') THEN
71		UPDATE upl_header A 
72			SET message = message || 'Title Item Name Tidak boleh kosong, '
73			WHERE session_id = pSessionId 
74			AND upload_id = pUploadId;
75	END IF;
76	
77	-- 3. Validasi title_item_code dan record_owner_id belum terdaftar di m_title_item
78	UPDATE upl_header A 
79	   SET message = message || 'Title Item Code sudah tedaftar di m_title_item, '
80	   WHERE session_id = pSessionId 
81	   AND upload_id = pUploadId
82	   AND NOT EXISTS (
83	   		SELECT 1 FROM m_title_item B WHERE B.record_owner_id = pRecordOwnerId
84	   		AND B.title_item_code = vTitleItemCode
85	   );
86
87END;
88$BODY$
89  LANGUAGE plpgsql VOLATILE
90  COST 100;