f_migrate_data_kasbon

CREATE OR REPLACE FUNCTION f_migrate_data_cash_advance_employee(character varying, bigint, bigint, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          alias for $1;
    pTenantId             alias for $2;
    pUserId               alias for $3;
    pDatetime             alias for $4;

  vDocTypeIdRCA bigint := 602;
  vDocTypeIdCBO bigint := 611;
  vEmptyId    bigint := -99;
  vNol              bigint := 0;
  vEmptyValue   character varying := '';
  vIdr              character varying := 'IDR';
  vYes        character varying := 'Y';
  vNo         character varying := 'N';
  vFlgCash              character varying := 'C';
  vStatusDoc                character varying := 'R';
  vTypeCBOut                character varying := 'O';
  vWorkFlowStatus           character varying := 'APPROVED';
  vActivityGlCode   character varying := 'CASHADVANCE';
  vModeCash         character varying := 'CASH';

BEGIN
    DELETE FROM ul_import_data_saldo_kasbon WHERE session_id = pSessionId;

    -- insert data migrasi ke temp table
    INSERT INTO ul_import_data_saldo_kasbon(
      session_id, ou_code, partner_code, doc_no, doc_date, remark, amount)
    SELECT pSessionId, A.ou_code, A.partner_code, A.doc_no, A.doc_date, A.remark, A.amount
    FROM temp_migarate_kas_bon_karyawan A
    WHERE NOT EXISTS (
      SELECT 1 FROM cb_payment_order B WHERE A.doc_no = B.doc_no AND A.doc_date = B.doc_date
    ) AND EXISTS (
      SELECT 1 FROM m_partner C WHERE A.partner_code = C.partner_code AND C.tenant_id = pTenantId
    ) AND EXISTS (
      SELECT 1 FROM t_ou D WHERE A.ou_code = D.ou_code AND D.tenant_id = pTenantId
    );

    -- insert into cb_payment_order
    INSERT INTO cb_payment_order
      (tenant_id, doc_type_id, doc_no, doc_date, ou_id, partner_id, partner_bank_id, ext_doc_no, ext_doc_date,
      ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
      version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, vDocTypeIdRCA, A.doc_no, A.doc_date, B.ou_id, C.partner_id, vEmptyId, vEmptyValue, vEmptyValue,
    vEmptyId, vEmptyId, A.doc_date AS due_date, vIdr, A.remark, vStatusDoc, vWorkFlowStatus,
    vNol, pDatetime, pUserId, pDatetime, pUserId
    FROM ul_import_data_saldo_kasbon A
    INNER JOIN t_ou B ON A.ou_code = B.ou_code AND B.tenant_id = pTenantId
    INNER JOIN m_partner C ON A.partner_code = C.partner_code AND C.tenant_id = pTenantId
    WHERE A.session_id = pSessionId;

    -- insert into cb_payment_order_cost
    INSERT INTO cb_payment_order_cost
      (payment_order_id, tenant_id, line_no, activity_gl_id, ou_rc_id, segment_id, curr_code, add_amount,
      remark, version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.payment_order_id, A.tenant_id, 1, B.activity_gl_id, vEmptyId, vEmptyId, vIdr, C.amount,
    A.remark, vNol, pDatetime, pUserId, pDatetime, pUserId
    FROM cb_payment_order A
    INNER JOIN t_ou X ON A.ou_id = X.ou_id AND A.tenant_id = X.tenant_id
    INNER JOIN m_activity_gl B ON A.tenant_id = B.tenant_id AND B.activity_gl_code = vActivityGlCode
    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
    WHERE C.session_id = pSessionId;

    -- insert into cb_in_out_cashbank
    INSERT INTO cb_in_out_cashbank
      (tenant_id, doc_type_id, doc_no, doc_date, ou_id, partner_id, type_in_out_cashbank, ext_doc_no, ext_doc_date,
      ref_doc_type_id, ref_id, ref_curr_code, ref_amount, due_date, remark, status_doc, workflow_status,
      version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, vDocTypeIdCBO, A.doc_no, A.doc_date, A.ou_id, A.partner_id, vTypeCBOut, vEmptyValue, A.doc_date,
    A.doc_type_id, A.payment_order_id, A.curr_code, vNol, A.doc_date, A.remark, vStatusDoc, vWorkFlowStatus,
    vNol, pDatetime, pUserId, pDatetime, pUserId
    FROM cb_payment_order A
    INNER JOIN t_ou X ON A.ou_id = X.ou_id AND A.tenant_id = X.tenant_id
    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
    WHERE B.session_id = pSessionId;

    -- insert into cb_trx_cashbank_balance
    INSERT INTO cb_trx_cashbank_balance
      (tenant_id, ou_id, doc_type_id, payment_id, payment_doc_no, payment_doc_date, payment_remark,
      partner_id, partner_bank_id, curr_code, amount, due_date, flg_payment, ref_doc_type_id, ref_id,
      version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.payment_order_id, A.doc_no, A.doc_date, A.remark,
    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,
    vNol, pDatetime, pUserId, pDatetime, pUserId
    FROM cb_payment_order A
    INNER JOIN t_ou X ON A.ou_id = X.ou_id AND A.tenant_id = X.tenant_id
    INNER JOIN cb_payment_order_cost B ON A.payment_order_id = B.payment_order_id
    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
    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
    WHERE D.session_id = pSessionId;


    -- insert into cb_in_out_cashbank_payment
    WITH tb_cashbank AS (
      SELECT ROW_NUMBER() OVER (PARTITION BY A.ou_id ORDER BY A.cashbank_id) AS row_num,  A.cashbank_id, A.ou_id
      FROM m_cashbank_ou_assignment A
      INNER JOIN m_cashbank B ON A.cashbank_id = B.cashbank_id
      WHERE B.flg_cash_bank = vFlgCash
      ORDER BY A.cashbank_id
    )
    INSERT INTO cb_in_out_cashbank_payment
      (tenant_id, in_out_cashbank_id, cashbank_id, mode_payment, bank_payment, no_payment, date_payment,
      curr_code, cashbank_amount, payment_amount,
      version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ref_id, D.cashbank_id, vModeCash, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
    B.curr_code, A.amount, A.amount,
    vNol, pDatetime, pUserId, pDatetime, pUserId
    FROM cb_trx_cashbank_balance A
    INNER JOIN cb_payment_order B ON A.payment_id = B.payment_order_id
    INNER JOIN t_ou X ON B.ou_id = X.ou_id AND B.tenant_id = X.tenant_id
    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
    LEFT JOIN tb_cashbank D ON B.ou_id = D.ou_id AND D.row_num = 1
    WHERE C.session_id = pSessionId;

    -- insert into cb_advance_balance
    INSERT INTO cb_advance_balance
      (advance_id, tenant_id, ou_id, partner_id, curr_code, amount, flg_payment, payment_doc_type_id,
      cash_bank_payment_id, cash_bank_payment_date, flg_settle, ref_doc_type_id, ref_id, ref_amount,
      version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT B.payment_order_id, B.tenant_id, B.ou_id, B.partner_id, B.curr_code, A.amount, vYes, vDocTypeIdCBO,
    A.ref_id, B.doc_date, vNo, vEmptyId, vEmptyId, A.amount,
    vNol, pDatetime, pUserId, pDatetime, pUserId
    FROM cb_trx_cashbank_balance A
    INNER JOIN cb_payment_order B ON A.payment_id = B.payment_order_id
    INNER JOIN t_ou X ON B.ou_id = X.ou_id AND B.tenant_id = X.tenant_id
    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
    WHERE C.session_id = pSessionId;

    -- update status migrasi
    UPDATE temp_migarate_kas_bon_karyawan SET status_migrate = 'Y'
    WHERE EXISTS (
      SELECT 1 FROM cb_payment_order B WHERE A.doc_no = B.doc_no AND A.doc_date = B.doc_date
    );

    DELETE FROM ul_import_data_saldo_kasbon WHERE session_id = pSessionId;


END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /