cb_submit_mapping_cash_bank_in_others_to_so

CREATE OR REPLACE FUNCTION cb_submit_mapping_cash_bank_in_others_to_so(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vMappingCbinSoId        bigint;
    vMappingCbinOtherId     bigint;
    vUserId                 bigint;
    vOuId                   bigint;
    vOuBuId                 bigint;
    vOuBranchId             bigint;
    vOuSubBuId              bigint;
    vAweFlowId              bigint;
    vDatetime               character varying(14);
    vJournalType            character varying(20);
    vLedgerCode             character varying(20);
    vRemark                 character varying;
    vEmptyString            character varying := '';
    vStatusRelease          character varying := 'R';
    vFlagNo                 character varying := 'N';
    vStatusVoid             character varying := 'V';
    vSignDebit              character varying(1) := 'D';
    vSignCredit             character varying(1) := 'C';
    vTypeRate               character varying(3) := 'COM';
    vActivityCOA            character varying(10) := 'ACTIVITY';
    vSystemCOA              character varying(10) := 'SYSTEM';
    vWorkflowApproved       character varying := 'APPROVED';
    vCCBOPScheme            character varying := 'GB16';
    vProcessName            character varying := 'cb_submit_conversion_cbin_other_to_partner';
    vParamKey               character varying := 'cashbankInId';
    vNewLine                character varying := E'\n';
    vMappingBalanceId       bigint;
    vDoId                   bigint;

    vDocTypeCCBIOP          bigint := 626;
    vEmptyId                bigint := -99;

BEGIN
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM temp_data_cashbank_in_other WHERE session_id = pSessionId;

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'cb_submit_mapping_cash_bank_in_others_to_so' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vMappingCbinSoId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'mappingCbinSoId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    vRemark := 'AUTO GENERATE Conversion CB In Other To Parter for [Mapping CB In To SO]: ';

    SELECT ref_id, ou_id INTO vMappingCbinOtherId, vOuId
        FROM cb_mapping_cbin_so A
        WHERE mapping_cbin_so_id = vMappingCbinSoId;

    SELECT journal_type, ledger_code INTO vJournalType, vLedgerCode
    FROM m_document_journal
    WHERE doc_type_id = vDocTypeCCBIOP;

    SELECT ou_bu_id, ou_branch_id, ou_sub_bu_id INTO vOuBuId, vOuBranchId, vOuSubBuId
    FROM m_ou_structure
    WHERE ou_id = vOuId;

    -- 1. Update doc status to R
    UPDATE cb_mapping_cbin_so
    SET status_doc = vStatusRelease,
        version = version + 1,
        update_datetime = vDatetime,
        update_user_id = vUserId
    WHERE mapping_cbin_so_id = vMappingCbinSoId;

    -- 2. Insert data mapping balance
    SELECT NEXTVAL('cb_mapping_cbin_so_balance_seq') INTO vMappingBalanceId;

    INSERT INTO cb_mapping_cbin_so_balance(
            mapping_cbin_so_balance_id, tenant_id, ou_id, mapping_cbin_so_id, doc_no, doc_date,
            ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date, payment_amount, flg_convert,
            cashbank_id, version, create_datetime, create_user_id, update_datetime, update_user_id
    )
    SELECT vMappingBalanceId, A.tenant_id, A.ou_id, A.mapping_cbin_so_id, A.doc_no, A.doc_date,
        A.ref_id, A.ref_doc_type_id, B.doc_no, B.doc_date, (F.receive_amount-F.convert_amount), vFlagNo,
        C.cashbank_id, 0, vDatetime, vUserId, vDatetime, vUserId
    -- SELECT *
    FROM cb_mapping_cbin_so A
    INNER JOIN cb_in_out_cashbank B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.in_out_cashbank_id AND A.tenant_id = B.tenant_id
    INNER JOIN cb_in_out_cashbank_payment C ON B.in_out_cashbank_id = C.in_out_cashbank_id AND B.tenant_id = C.tenant_id
    INNER JOIN cb_non_ar_receive_to_ar_balance F ON B.in_out_cashbank_id = F.in_out_cashbank_id
    WHERE A.mapping_cbin_so_id = vMappingCbinSoId;

    INSERT INTO cb_mapping_cbin_so_balance_item(
            tenant_id, mapping_cbin_so_balance_id,
            so_id, doc_no, doc_date, amount, salesman_id, customer_id,
            version, create_datetime, create_user_id, update_datetime, update_user_id
    )
    SELECT A.tenant_id, A.mapping_cbin_so_balance_id,
        B.so_id, B.so_no, B.so_date, B.so_amount, B.so_salesman_id, B.so_customer_id,
        0, vDatetime, vUserId, vDatetime, vUserId
     -- SELECT *
     FROM cb_mapping_cbin_so_balance A
     INNER JOIN cb_mapping_cbin_so_item B ON A.mapping_cbin_so_id = B.mapping_cbin_so_id AND A.tenant_id = B.tenant_id
     WHERE A.mapping_cbin_so_balance_id = vMappingBalanceId
     AND A.mapping_cbin_so_id = vMappingCbinSoId
     AND A.tenant_id = pTenantId;

    -- 3. insert data untuk konversi CB In Other menjadi CB In Partner

    -- 3.0. /* insert into table temporary cashbank in other rcv */
    INSERT INTO temp_data_cashbank_in_other(
            session_id, tenant_id, in_out_cashbank_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_doc_date, ref_curr_code, ref_amount,
            due_date, cost_remark, cashbank_remark, cashbank_id,
            amount, status_doc, workflow_status,
            version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pSessionId, A.tenant_id, nextval('cb_in_out_cashbank_seq'), vDocTypeCCBIOP, CONCAT(A.doc_no, '-', B.line_no), A.doc_date,
            A.ou_id, B.so_customer_id, 'I', vEmptyString,
            vEmptyString, A.ref_doc_type_id, A.ref_id, C.doc_date, D.curr_code, E.payment_amount,
            vEmptyString, C.remark, vRemark || vNewLine || C.doc_no || ' - ' || B.so_no, D.cashbank_id,
            B.so_amount, vStatusRelease, vWorkflowApproved,
            0, vDatetime, vUserId, vDatetime, vUserId
    FROM cb_mapping_cbin_so A
    INNER JOIN cb_mapping_cbin_so_item B ON A.mapping_cbin_so_id = B.mapping_cbin_so_id
    INNER JOIN cb_in_out_cashbank C ON A.ref_doc_type_id = C.doc_type_id AND A.ref_id = C.in_out_cashbank_id
    INNER JOIN cb_in_out_cashbank_payment D ON C.in_out_cashbank_id = D.in_out_cashbank_id
    INNER JOIN cb_mapping_cbin_so_balance E ON A.mapping_cbin_so_id = E.mapping_cbin_so_id
    WHERE A.mapping_cbin_so_id = vMappingCbinSoId;

    -- 3.1. insert header dokumen konversi
    INSERT INTO cb_in_out_cashbank(
            in_out_cashbank_id, 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.in_out_cashbank_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date,
        A.ou_id, A.partner_id, A.type_in_out_cashbank, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.ref_curr_code, A.ref_amount, A.due_date,
        A.cashbank_remark, A.status_doc, A.workflow_status, A.version, A.create_datetime,
        A.create_user_id, A.update_datetime, A.update_user_id
    FROM temp_data_cashbank_in_other A
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    -- 3.2. insert data cb_in_out_cashbank_ext
    INSERT INTO cb_in_out_cashbank_ext(
            in_out_cashbank_id, ou_id_cb_in_partner, version, create_datetime,
            create_user_id, update_datetime, update_user_id)
    SELECT A.in_out_cashbank_id, A.ou_id, A.version, A.create_datetime,
        A.create_user_id, A.update_datetime, A.update_user_id
    FROM temp_data_cashbank_in_other A
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    -- 3.3. insert data cb_in_out_cashbank_payment
    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.in_out_cashbank_id, A.cashbank_id,
        C.mode_payment, C.bank_payment, C.no_payment, C.date_payment, C.curr_code,
        A.amount, A.amount, A.version, A.create_datetime, A.create_user_id,
        A.update_datetime, A.update_user_id
    FROM temp_data_cashbank_in_other A
    INNER JOIN cb_in_out_cashbank B ON A.ref_id = B.in_out_cashbank_id AND A.ref_doc_type_id = B.doc_type_id
    INNER JOIN cb_in_out_cashbank_payment C ON C.in_out_cashbank_id = B.in_out_cashbank_id
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    -- 3.4. insert data fi_receipt_ar_balance
    INSERT INTO fi_receipt_ar_balance
        (receipt_ar_balance_id, tenant_id, ou_id, doc_type_id, doc_no, doc_date,
        cashbank_id, partner_id, curr_code, amount, remark,
        flg_alloc, ref_alloc_id, ref_item_id, cheque_giro_no, cheque_giro_date, cheque_giro_bank,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.in_out_cashbank_id, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        A.cashbank_id, A.partner_id, A.ref_curr_code, A.amount, A.cashbank_remark,
        'N', vEmptyId, A.in_out_cashbank_id, '' AS cheque_giro_no, '' AS cheque_giro_date, '' AS cheque_giro_bank,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM temp_data_cashbank_in_other A
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    --3.5. update data NON AR balance
    WITH data_convert AS (
        SELECT A.in_out_cashbank_id AS ref_id, B.payment_amount
        FROM cb_in_out_cashbank A
            INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        WHERE A.in_out_cashbank_id = vMappingCbinOtherId
    )
    UPDATE cb_non_ar_receive_to_ar_balance A
    SET convert_amount = convert_amount + payment_amount,
        flg_convert = 'Y',
        version = version + 1,
        update_datetime = vDatetime,
        update_user_id = vUserId
    FROM data_convert B
    WHERE A.in_out_cashbank_id = B.ref_id;

    --3.6. update status doc Cashbank In Other Id = Void
    UPDATE cb_in_out_cashbank A
    SET status_doc = vStatusVoid,
        version = A.version + 1,
        update_datetime = vDatetime,
        update_user_id = vUserId
    FROM cb_non_ar_receive_to_ar_balance B
    WHERE A.in_out_cashbank_id = vMappingCbinOtherId
        AND A.in_out_cashbank_id = B.in_out_cashbank_id
        AND B.flg_convert = 'Y';

   INSERT INTO cb_log_non_ar_receive_to_ar_balance(
               in_out_cashbank_id, cb_in_other_id,
               cb_in_other_doc_type_id, tenant_id, doc_no, doc_date, ou_id_cb_in_ar,
               partner_id, convert_amount, remark, version, create_datetime,
               create_user_id, update_datetime, update_user_id)
   SELECT A.in_out_cashbank_id, A.ref_id,
        A.ref_doc_type_id, A.tenant_id, A.doc_no, A.doc_date, A.ou_id,
        A.partner_id, A.amount, A.cashbank_remark, 0, vDatetime,
        vUserId, vDatetime, vUserId
   FROM temp_data_cashbank_in_other A
   WHERE A.session_id = pSessionId
   AND A.tenant_id = pTenantId;

    -- 3. Masukkan jurnal
     -- 1. buat admin
     -- 2. buat temlate jurnal

    PERFORM gl_manage_admin_journal_trx(A.tenant_id, vOuBuId, vOuBuId, vJournalType,
        vLedgerCode, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
    FROM temp_data_cashbank_in_other A
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    -- update journal trx id untuk tiap dokumen
    UPDATE temp_data_cashbank_in_other A
    SET journal_trx_id = NEXTVAL('gl_journal_trx_seq')
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    INSERT INTO gl_journal_trx
    (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
    ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_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 A.journal_trx_id, A.tenant_id, vJournalType, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date,
        vOuBuId, vEmptyId, vEmptyId, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.due_date, A.ref_curr_code, A.cashbank_remark, A.status_doc, A.workflow_status,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM temp_data_cashbank_in_other A
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, A.journal_trx_id, 1,
        A.doc_type_id, A.in_out_cashbank_id,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.ref_curr_code, 0, vEmptyId,
        A.amount, A.doc_date, vTypeRate,
        1, 1, 'AR_CASHBANK_IN', A.cashbank_remark
    FROM temp_data_cashbank_in_other A
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, A.journal_trx_id, 1,
        A.doc_type_id, B.in_out_cashbank_cost_id,
        A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
        B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
        E.coa_id, B.curr_code, 0, vEmptyId,
        B.cost_amount, A.doc_date, vTypeRate,
        1, 1, 'AR_COST_RECEIPT', B.remark
    FROM temp_data_cashbank_in_other A
    INNER JOIN cb_in_out_cashbank_cost B ON A.in_out_cashbank_id = B.in_out_cashbank_id
    INNER JOIN m_activity_gl E ON B.activity_gl_id = E.activity_gl_id
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, A.journal_trx_id, 1,
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
        B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
        C.coa_id, B.curr_code, 0, vEmptyId,
        A.amount, A.ref_doc_date, vTypeRate,
        1, 1, 'OTHERS_RECEIVE', A.cost_remark
    FROM temp_data_cashbank_in_other A
    INNER JOIN cb_in_out_cashbank_cost B ON A.ref_id = B.in_out_cashbank_id
    INNER JOIN m_activity_gl C ON B.activity_gl_id = C.activity_gl_id
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    INSERT INTO gl_journal_trx_item
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    ou_branch_id, ou_sub_bu_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId,
        vOuBranchId, vOuSubBuId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    AND A.journal_desc IN ('AR_COST_RECEIPT', 'AR_CASHBANK_IN');

    INSERT INTO gl_journal_trx_mapping
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    ou_branch_id, ou_sub_bu_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId,
        vOuBranchId, vOuSubBuId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    AND A.journal_desc = 'OTHERS_RECEIVE';

    -- ambil awe_flow CCBOP yang aktif
    SELECT awe_flow_id INTO vAweFlowId
    FROM awe_flow
    WHERE scheme = vCCBOPScheme AND flg_validate = 'Y' AND active = 'Y' AND tenant_id = pTenantId
    LIMIT 1;

    INSERT INTO awe_currdoc_status(
        req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
        remark, current_user_id, current_role_id, flg_user_role, label,
        data, flow_id, create_datetime, create_user_id, create_role_id,
        update_datetime, update_user_id, update_role_id, version)
    SELECT A.in_out_cashbank_id||'_'||A.doc_no, A.tenant_id, vCCBOPScheme, A.in_out_cashbank_id, A.doc_no, A.doc_date, vWorkflowApproved,
        A.cashbank_remark, A.create_user_id, vEmptyId, 'R', 'Conversion CB In Other To Partner' || A.doc_no,
        '{}', vAweFlowId, create_datetime, create_user_id, vEmptyId,
        update_datetime, update_user_id, vEmptyId, 0
    FROM temp_data_cashbank_in_other A
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId;

    -- generate_process_message_for_submit_doc
    PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessName,
            A.in_out_cashbank_id ||'_'||A.doc_no,
            vDatetime, vParamKey, A.in_out_cashbank_id::character varying, vUserId)
    FROM temp_data_cashbank_in_other A
        WHERE A.session_id = pSessionId
        AND A.tenant_id = pTenantId;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM temp_data_cashbank_in_other WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /