abirama62

f_migrate_data_kasbon

Oct 14th, 2020 (edited)
1,036
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION f_migrate_data_cash_advance_employee(character varying, bigint, bigint, character varying)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.     pSessionId          alias for $1;
  6.     pTenantId             alias for $2;
  7.     pUserId               alias for $3;
  8.     pDatetime             alias for $4;
  9.  
  10.   vDocTypeIdRCA bigint := 602;
  11.   vDocTypeIdCBO bigint := 611;
  12.   vEmptyId    bigint := -99;
  13.   vNol              bigint := 0;
  14.   vEmptyValue   character varying := '';
  15.   vIdr              character varying := 'IDR';
  16.   vYes        character varying := 'Y';
  17.   vNo         character varying := 'N';
  18.   vFlgCash              character varying := 'C';
  19.   vStatusDoc                character varying := 'R';
  20.   vTypeCBOut                character varying := 'O';
  21.   vWorkFlowStatus           character varying := 'APPROVED';
  22.   vActivityGlCode   character varying := 'CASHADVANCE';
  23.   vModeCash         character varying := 'CASH';
  24.  
  25. BEGIN
  26.     DELETE FROM ul_import_data_saldo_kasbon WHERE session_id = pSessionId;
  27.  
  28.     -- insert data migrasi ke temp table
  29.     INSERT INTO ul_import_data_saldo_kasbon(
  30.       session_id, ou_code, partner_code, doc_no, doc_date, remark, amount)
  31.     SELECT pSessionId, A.ou_code, A.partner_code, A.doc_no, A.doc_date, A.remark, A.amount
  32.     FROM temp_migarate_kas_bon_karyawan A
  33.     WHERE NOT EXISTS (
  34.       SELECT 1 FROM cb_payment_order B WHERE A.doc_no = B.doc_no AND A.doc_date = B.doc_date
  35.     ) AND EXISTS (
  36.       SELECT 1 FROM m_partner C WHERE A.partner_code = C.partner_code AND C.tenant_id = pTenantId
  37.     ) AND EXISTS (
  38.       SELECT 1 FROM t_ou D WHERE A.ou_code = D.ou_code AND D.tenant_id = pTenantId
  39.     );
  40.  
  41.     -- insert into cb_payment_order
  42.     INSERT INTO cb_payment_order
  43.       (tenant_id, doc_type_id, doc_no, doc_date, ou_id, partner_id, partner_bank_id, ext_doc_no, ext_doc_date,
  44.       ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  45.       version, create_datetime, create_user_id, update_datetime, update_user_id)
  46.     SELECT pTenantId, vDocTypeIdRCA, A.doc_no, A.doc_date, B.ou_id, C.partner_id, vEmptyId, vEmptyValue, vEmptyValue,
  47.     vEmptyId, vEmptyId, A.doc_date AS due_date, vIdr, A.remark, vStatusDoc, vWorkFlowStatus,
  48.     vNol, pDatetime, pUserId, pDatetime, pUserId
  49.     FROM ul_import_data_saldo_kasbon A
  50.     INNER JOIN t_ou B ON A.ou_code = B.ou_code AND B.tenant_id = pTenantId
  51.     INNER JOIN m_partner C ON A.partner_code = C.partner_code AND C.tenant_id = pTenantId
  52.     WHERE A.session_id = pSessionId;
  53.  
  54.     -- insert into cb_payment_order_cost
  55.     INSERT INTO cb_payment_order_cost
  56.       (payment_order_id, tenant_id, line_no, activity_gl_id, ou_rc_id, segment_id, curr_code, add_amount,
  57.       remark, version, create_datetime, create_user_id, update_datetime, update_user_id)
  58.     SELECT A.payment_order_id, A.tenant_id, 1, B.activity_gl_id, vEmptyId, vEmptyId, vIdr, C.amount,
  59.     A.remark, vNol, pDatetime, pUserId, pDatetime, pUserId
  60.     FROM cb_payment_order A
  61.     INNER JOIN t_ou X ON A.ou_id = X.ou_id AND A.tenant_id = X.tenant_id
  62.     INNER JOIN m_activity_gl B ON A.tenant_id = B.tenant_id AND B.activity_gl_code = vActivityGlCode
  63.     INNER JOIN ul_import_data_saldo_kasbon C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND X.ou_code = C.ou_code
  64.     WHERE C.session_id = pSessionId;
  65.  
  66.     -- insert into cb_in_out_cashbank
  67.     INSERT INTO cb_in_out_cashbank
  68.       (tenant_id, doc_type_id, doc_no, doc_date, ou_id, partner_id, type_in_out_cashbank, ext_doc_no, ext_doc_date,
  69.       ref_doc_type_id, ref_id, ref_curr_code, ref_amount, due_date, remark, status_doc, workflow_status,
  70.       version, create_datetime, create_user_id, update_datetime, update_user_id)
  71.     SELECT A.tenant_id, vDocTypeIdCBO, A.doc_no, A.doc_date, A.ou_id, A.partner_id, vTypeCBOut, vEmptyValue, A.doc_date,
  72.     A.doc_type_id, A.payment_order_id, A.curr_code, vNol, A.doc_date, A.remark, vStatusDoc, vWorkFlowStatus,
  73.     vNol, pDatetime, pUserId, pDatetime, pUserId
  74.     FROM cb_payment_order A
  75.     INNER JOIN t_ou X ON A.ou_id = X.ou_id AND A.tenant_id = X.tenant_id
  76.     INNER JOIN ul_import_data_saldo_kasbon B ON A.doc_no = B.doc_no AND A.doc_date = B.doc_date AND X.ou_code = B.ou_code
  77.     WHERE B.session_id = pSessionId;
  78.  
  79.     -- insert into cb_trx_cashbank_balance
  80.     INSERT INTO cb_trx_cashbank_balance
  81.       (tenant_id, ou_id, doc_type_id, payment_id, payment_doc_no, payment_doc_date, payment_remark,
  82.       partner_id, partner_bank_id, curr_code, amount, due_date, flg_payment, ref_doc_type_id, ref_id,
  83.       version, create_datetime, create_user_id, update_datetime, update_user_id)
  84.     SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.payment_order_id, A.doc_no, A.doc_date, A.remark,
  85.     A.partner_id, A.partner_bank_id, A.curr_code, B.add_amount, A.due_date, vYes, C.doc_type_id, C.in_out_cashbank_id,
  86.     vNol, pDatetime, pUserId, pDatetime, pUserId
  87.     FROM cb_payment_order A
  88.     INNER JOIN t_ou X ON A.ou_id = X.ou_id AND A.tenant_id = X.tenant_id
  89.     INNER JOIN cb_payment_order_cost B ON A.payment_order_id = B.payment_order_id
  90.     INNER JOIN cb_in_out_cashbank C ON A.payment_order_id = C.ref_id AND A.doc_type_id = C.ref_doc_type_id
  91.     INNER JOIN ul_import_data_saldo_kasbon D ON A.doc_no = D.doc_no AND A.doc_date = D.doc_date AND X.ou_code = D.ou_code
  92.     WHERE D.session_id = pSessionId;
  93.  
  94.  
  95.     -- insert into cb_in_out_cashbank_payment
  96.     WITH tb_cashbank AS (
  97.       SELECT ROW_NUMBER() OVER (PARTITION BY A.ou_id ORDER BY A.cashbank_id) AS row_num,  A.cashbank_id, A.ou_id
  98.       FROM m_cashbank_ou_assignment A
  99.       INNER JOIN m_cashbank B ON A.cashbank_id = B.cashbank_id
  100.       WHERE B.flg_cash_bank = vFlgCash
  101.       ORDER BY A.cashbank_id
  102.     )
  103.     INSERT INTO cb_in_out_cashbank_payment
  104.       (tenant_id, in_out_cashbank_id, cashbank_id, mode_payment, bank_payment, no_payment, date_payment,
  105.       curr_code, cashbank_amount, payment_amount,
  106.       version, create_datetime, create_user_id, update_datetime, update_user_id)
  107.     SELECT A.tenant_id, A.ref_id, D.cashbank_id, vModeCash, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
  108.     B.curr_code, A.amount, A.amount,
  109.     vNol, pDatetime, pUserId, pDatetime, pUserId
  110.     FROM cb_trx_cashbank_balance A
  111.     INNER JOIN cb_payment_order B ON A.payment_id = B.payment_order_id
  112.     INNER JOIN t_ou X ON B.ou_id = X.ou_id AND B.tenant_id = X.tenant_id
  113.     INNER JOIN ul_import_data_saldo_kasbon C ON B.doc_no = C.doc_no AND B.doc_date = C.doc_date AND X.ou_code = C.ou_code
  114.     LEFT JOIN tb_cashbank D ON B.ou_id = D.ou_id AND D.row_num = 1
  115.     WHERE C.session_id = pSessionId;
  116.  
  117.     -- insert into cb_advance_balance
  118.     INSERT INTO cb_advance_balance
  119.       (advance_id, tenant_id, ou_id, partner_id, curr_code, amount, flg_payment, payment_doc_type_id,
  120.       cash_bank_payment_id, cash_bank_payment_date, flg_settle, ref_doc_type_id, ref_id, ref_amount,
  121.       version, create_datetime, create_user_id, update_datetime, update_user_id)
  122.     SELECT B.payment_order_id, B.tenant_id, B.ou_id, B.partner_id, B.curr_code, A.amount, vYes, vDocTypeIdCBO,
  123.     A.ref_id, B.doc_date, vNo, vEmptyId, vEmptyId, A.amount,
  124.     vNol, pDatetime, pUserId, pDatetime, pUserId
  125.     FROM cb_trx_cashbank_balance A
  126.     INNER JOIN cb_payment_order B ON A.payment_id = B.payment_order_id
  127.     INNER JOIN t_ou X ON B.ou_id = X.ou_id AND B.tenant_id = X.tenant_id
  128.     INNER JOIN ul_import_data_saldo_kasbon C ON B.doc_no = C.doc_no AND B.doc_date = C.doc_date AND X.ou_code = C.ou_code
  129.     WHERE C.session_id = pSessionId;
  130.  
  131.     -- update status migrasi
  132.     UPDATE temp_migarate_kas_bon_karyawan SET status_migrate = 'Y'
  133.     WHERE EXISTS (
  134.       SELECT 1 FROM cb_payment_order B WHERE A.doc_no = B.doc_no AND A.doc_date = B.doc_date
  135.     );
  136.  
  137.     DELETE FROM ul_import_data_saldo_kasbon WHERE session_id = pSessionId;
  138.  
  139.  
  140. END
  141. $BODY$
  142.   LANGUAGE plpgsql VOLATILE
  143.   COST 100;
  144.   /
RAW Paste Data