abirama62

cb_submit_mapping_cash_bank_in_others_to_so

Jun 14th, 2021
783
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION cb_submit_mapping_cash_bank_in_others_to_so(bigint, character varying, character varying)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.     pTenantId           ALIAS FOR $1;
  6.     pSessionId          ALIAS FOR $2;
  7.     pProcessNo          ALIAS FOR $3;
  8.  
  9.     vProcessId              bigint;
  10.     vMappingCbinSoId        bigint;
  11.     vMappingCbinOtherId     bigint;
  12.     vUserId                 bigint;
  13.     vOuId                   bigint;
  14.     vOuBuId                 bigint;
  15.     vOuBranchId             bigint;
  16.     vOuSubBuId              bigint;
  17.     vAweFlowId              bigint;
  18.     vDatetime               character varying(14);
  19.     vJournalType            character varying(20);
  20.     vLedgerCode             character varying(20);
  21.     vRemark                 character varying;
  22.     vEmptyString            character varying := '';
  23.     vStatusRelease          character varying := 'R';
  24.     vFlagNo                 character varying := 'N';
  25.     vStatusVoid             character varying := 'V';
  26.     vSignDebit              character varying(1) := 'D';
  27.     vSignCredit             character varying(1) := 'C';
  28.     vTypeRate               character varying(3) := 'COM';
  29.     vActivityCOA            character varying(10) := 'ACTIVITY';
  30.     vSystemCOA              character varying(10) := 'SYSTEM';
  31.     vWorkflowApproved       character varying := 'APPROVED';
  32.     vCCBOPScheme            character varying := 'GB16';
  33.     vProcessName            character varying := 'cb_submit_conversion_cbin_other_to_partner';
  34.     vParamKey               character varying := 'cashbankInId';
  35.     vNewLine                character varying := E'\n';
  36.     vMappingBalanceId       bigint;
  37.     vDoId                   bigint;
  38.  
  39.     vDocTypeCCBIOP          bigint := 626;
  40.     vEmptyId                bigint := -99;
  41.  
  42. BEGIN
  43.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  44.     DELETE FROM temp_data_cashbank_in_other WHERE session_id = pSessionId;
  45.  
  46.     SELECT A.process_message_id INTO vProcessId
  47.     FROM t_process_message A
  48.     WHERE A.tenant_id = pTenantId AND
  49.         A.process_name = 'cb_submit_mapping_cash_bank_in_others_to_so' AND
  50.         A.process_no = pProcessNo;
  51.  
  52.     SELECT CAST(A.process_parameter_value AS bigint) INTO vMappingCbinSoId
  53.     FROM t_process_parameter A
  54.     WHERE A.process_message_id = vProcessId AND
  55.         A.process_parameter_key = 'mappingCbinSoId';
  56.  
  57.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  58.     FROM t_process_parameter A
  59.     WHERE A.process_message_id = vProcessId AND
  60.         A.process_parameter_key = 'userId';
  61.  
  62.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  63.     FROM t_process_parameter A
  64.     WHERE A.process_message_id = vProcessId AND
  65.         A.process_parameter_key = 'datetime';
  66.  
  67.     vRemark := 'AUTO GENERATE Conversion CB In Other To Parter for [Mapping CB In To SO]: ';
  68.  
  69.     SELECT ref_id, ou_id INTO vMappingCbinOtherId, vOuId
  70.         FROM cb_mapping_cbin_so A
  71.         WHERE mapping_cbin_so_id = vMappingCbinSoId;
  72.  
  73.     SELECT journal_type, ledger_code INTO vJournalType, vLedgerCode
  74.     FROM m_document_journal
  75.     WHERE doc_type_id = vDocTypeCCBIOP;
  76.  
  77.     SELECT ou_bu_id, ou_branch_id, ou_sub_bu_id INTO vOuBuId, vOuBranchId, vOuSubBuId
  78.     FROM m_ou_structure
  79.     WHERE ou_id = vOuId;
  80.  
  81.     -- 1. Update doc status to R
  82.     UPDATE cb_mapping_cbin_so
  83.     SET status_doc = vStatusRelease,
  84.         version = version + 1,
  85.         update_datetime = vDatetime,
  86.         update_user_id = vUserId
  87.     WHERE mapping_cbin_so_id = vMappingCbinSoId;
  88.  
  89.     -- 2. Insert data mapping balance
  90.     SELECT NEXTVAL('cb_mapping_cbin_so_balance_seq') INTO vMappingBalanceId;
  91.  
  92.     INSERT INTO cb_mapping_cbin_so_balance(
  93.             mapping_cbin_so_balance_id, tenant_id, ou_id, mapping_cbin_so_id, doc_no, doc_date,
  94.             ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date, payment_amount, flg_convert,
  95.             cashbank_id, version, create_datetime, create_user_id, update_datetime, update_user_id
  96.     )
  97.     SELECT vMappingBalanceId, A.tenant_id, A.ou_id, A.mapping_cbin_so_id, A.doc_no, A.doc_date,
  98.         A.ref_id, A.ref_doc_type_id, B.doc_no, B.doc_date, (F.receive_amount-F.convert_amount), vFlagNo,
  99.         C.cashbank_id, 0, vDatetime, vUserId, vDatetime, vUserId
  100.     -- SELECT *
  101.     FROM cb_mapping_cbin_so A
  102.     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
  103.     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
  104.     INNER JOIN cb_non_ar_receive_to_ar_balance F ON B.in_out_cashbank_id = F.in_out_cashbank_id
  105.     WHERE A.mapping_cbin_so_id = vMappingCbinSoId;
  106.  
  107.     INSERT INTO cb_mapping_cbin_so_balance_item(
  108.             tenant_id, mapping_cbin_so_balance_id,
  109.             so_id, doc_no, doc_date, amount, salesman_id, customer_id,
  110.             version, create_datetime, create_user_id, update_datetime, update_user_id
  111.     )
  112.     SELECT A.tenant_id, A.mapping_cbin_so_balance_id,
  113.         B.so_id, B.so_no, B.so_date, B.so_amount, B.so_salesman_id, B.so_customer_id,
  114.         0, vDatetime, vUserId, vDatetime, vUserId
  115.      -- SELECT *
  116.      FROM cb_mapping_cbin_so_balance A
  117.      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
  118.      WHERE A.mapping_cbin_so_balance_id = vMappingBalanceId
  119.      AND A.mapping_cbin_so_id = vMappingCbinSoId
  120.      AND A.tenant_id = pTenantId;
  121.  
  122.     -- 3. insert data untuk konversi CB In Other menjadi CB In Partner
  123.  
  124.     -- 3.0. /* insert into table temporary cashbank in other rcv */
  125.     INSERT INTO temp_data_cashbank_in_other(
  126.             session_id, tenant_id, in_out_cashbank_id, doc_type_id, doc_no, doc_date,
  127.             ou_id, partner_id, type_in_out_cashbank, ext_doc_no,
  128.             ext_doc_date, ref_doc_type_id, ref_id, ref_doc_date, ref_curr_code, ref_amount,
  129.             due_date, cost_remark, cashbank_remark, cashbank_id,
  130.             amount, status_doc, workflow_status,
  131.             version, create_datetime, create_user_id, update_datetime, update_user_id)
  132.     SELECT pSessionId, A.tenant_id, nextval('cb_in_out_cashbank_seq'), vDocTypeCCBIOP, CONCAT(A.doc_no, '-', B.line_no), A.doc_date,
  133.             A.ou_id, B.so_customer_id, 'I', vEmptyString,
  134.             vEmptyString, A.ref_doc_type_id, A.ref_id, C.doc_date, D.curr_code, E.payment_amount,
  135.             vEmptyString, C.remark, vRemark || vNewLine || C.doc_no || ' - ' || B.so_no, D.cashbank_id,
  136.             B.so_amount, vStatusRelease, vWorkflowApproved,
  137.             0, vDatetime, vUserId, vDatetime, vUserId
  138.     FROM cb_mapping_cbin_so A
  139.     INNER JOIN cb_mapping_cbin_so_item B ON A.mapping_cbin_so_id = B.mapping_cbin_so_id
  140.     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
  141.     INNER JOIN cb_in_out_cashbank_payment D ON C.in_out_cashbank_id = D.in_out_cashbank_id
  142.     INNER JOIN cb_mapping_cbin_so_balance E ON A.mapping_cbin_so_id = E.mapping_cbin_so_id
  143.     WHERE A.mapping_cbin_so_id = vMappingCbinSoId;
  144.  
  145.     -- 3.1. insert header dokumen konversi
  146.     INSERT INTO cb_in_out_cashbank(
  147.             in_out_cashbank_id, tenant_id, doc_type_id, doc_no, doc_date,
  148.             ou_id, partner_id, type_in_out_cashbank, ext_doc_no, ext_doc_date,
  149.             ref_doc_type_id, ref_id, ref_curr_code, ref_amount, due_date,
  150.             remark, status_doc, workflow_status, version, create_datetime,
  151.             create_user_id, update_datetime, update_user_id)
  152.     SELECT A.in_out_cashbank_id, A.tenant_id, A.doc_type_id, A.doc_no, A.doc_date,
  153.         A.ou_id, A.partner_id, A.type_in_out_cashbank, A.ext_doc_no, A.ext_doc_date,
  154.         A.ref_doc_type_id, A.ref_id, A.ref_curr_code, A.ref_amount, A.due_date,
  155.         A.cashbank_remark, A.status_doc, A.workflow_status, A.version, A.create_datetime,
  156.         A.create_user_id, A.update_datetime, A.update_user_id
  157.     FROM temp_data_cashbank_in_other A
  158.     WHERE A.session_id = pSessionId
  159.     AND A.tenant_id = pTenantId;
  160.  
  161.     -- 3.2. insert data cb_in_out_cashbank_ext
  162.     INSERT INTO cb_in_out_cashbank_ext(
  163.             in_out_cashbank_id, ou_id_cb_in_partner, version, create_datetime,
  164.             create_user_id, update_datetime, update_user_id)
  165.     SELECT A.in_out_cashbank_id, A.ou_id, A.version, A.create_datetime,
  166.         A.create_user_id, A.update_datetime, A.update_user_id
  167.     FROM temp_data_cashbank_in_other A
  168.     WHERE A.session_id = pSessionId
  169.     AND A.tenant_id = pTenantId;
  170.  
  171.     -- 3.3. insert data cb_in_out_cashbank_payment
  172.     INSERT INTO cb_in_out_cashbank_payment(
  173.             tenant_id, in_out_cashbank_id, cashbank_id,
  174.             mode_payment, bank_payment, no_payment, date_payment, curr_code,
  175.             cashbank_amount, payment_amount, version, create_datetime, create_user_id,
  176.             update_datetime, update_user_id)
  177.     SELECT A.tenant_id, A.in_out_cashbank_id, A.cashbank_id,
  178.         C.mode_payment, C.bank_payment, C.no_payment, C.date_payment, C.curr_code,
  179.         A.amount, A.amount, A.version, A.create_datetime, A.create_user_id,
  180.         A.update_datetime, A.update_user_id
  181.     FROM temp_data_cashbank_in_other A
  182.     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
  183.     INNER JOIN cb_in_out_cashbank_payment C ON C.in_out_cashbank_id = B.in_out_cashbank_id
  184.     WHERE A.session_id = pSessionId
  185.     AND A.tenant_id = pTenantId;
  186.  
  187.     -- 3.4. insert data fi_receipt_ar_balance
  188.     INSERT INTO fi_receipt_ar_balance
  189.         (receipt_ar_balance_id, tenant_id, ou_id, doc_type_id, doc_no, doc_date,
  190.         cashbank_id, partner_id, curr_code, amount, remark,
  191.         flg_alloc, ref_alloc_id, ref_item_id, cheque_giro_no, cheque_giro_date, cheque_giro_bank,
  192.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  193.     SELECT A.in_out_cashbank_id, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
  194.         A.cashbank_id, A.partner_id, A.ref_curr_code, A.amount, A.cashbank_remark,
  195.         'N', vEmptyId, A.in_out_cashbank_id, '' AS cheque_giro_no, '' AS cheque_giro_date, '' AS cheque_giro_bank,
  196.         0, vDatetime, vUserId, vDatetime, vUserId
  197.     FROM temp_data_cashbank_in_other A
  198.     WHERE A.session_id = pSessionId
  199.     AND A.tenant_id = pTenantId;
  200.  
  201.     --3.5. update data NON AR balance
  202.     WITH data_convert AS (
  203.         SELECT A.in_out_cashbank_id AS ref_id, B.payment_amount
  204.         FROM cb_in_out_cashbank A
  205.             INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  206.         WHERE A.in_out_cashbank_id = vMappingCbinOtherId
  207.     )
  208.     UPDATE cb_non_ar_receive_to_ar_balance A
  209.     SET convert_amount = convert_amount + payment_amount,
  210.         flg_convert = 'Y',
  211.         version = version + 1,
  212.         update_datetime = vDatetime,
  213.         update_user_id = vUserId
  214.     FROM data_convert B
  215.     WHERE A.in_out_cashbank_id = B.ref_id;
  216.  
  217.     --3.6. update status doc Cashbank In Other Id = Void
  218.     UPDATE cb_in_out_cashbank A
  219.     SET status_doc = vStatusVoid,
  220.         version = A.version + 1,
  221.         update_datetime = vDatetime,
  222.         update_user_id = vUserId
  223.     FROM cb_non_ar_receive_to_ar_balance B
  224.     WHERE A.in_out_cashbank_id = vMappingCbinOtherId
  225.         AND A.in_out_cashbank_id = B.in_out_cashbank_id
  226.         AND B.flg_convert = 'Y';
  227.  
  228.    INSERT INTO cb_log_non_ar_receive_to_ar_balance(
  229.                in_out_cashbank_id, cb_in_other_id,
  230.                cb_in_other_doc_type_id, tenant_id, doc_no, doc_date, ou_id_cb_in_ar,
  231.                partner_id, convert_amount, remark, version, create_datetime,
  232.                create_user_id, update_datetime, update_user_id)
  233.    SELECT A.in_out_cashbank_id, A.ref_id,
  234.         A.ref_doc_type_id, A.tenant_id, A.doc_no, A.doc_date, A.ou_id,
  235.         A.partner_id, A.amount, A.cashbank_remark, 0, vDatetime,
  236.         vUserId, vDatetime, vUserId
  237.    FROM temp_data_cashbank_in_other A
  238.    WHERE A.session_id = pSessionId
  239.    AND A.tenant_id = pTenantId;
  240.  
  241.     -- 3. Masukkan jurnal
  242.      -- 1. buat admin
  243.      -- 2. buat temlate jurnal
  244.  
  245.     PERFORM gl_manage_admin_journal_trx(A.tenant_id, vOuBuId, vOuBuId, vJournalType,
  246.         vLedgerCode, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
  247.     FROM temp_data_cashbank_in_other A
  248.     WHERE A.session_id = pSessionId
  249.     AND A.tenant_id = pTenantId;
  250.  
  251.     -- update journal trx id untuk tiap dokumen
  252.     UPDATE temp_data_cashbank_in_other A
  253.     SET journal_trx_id = NEXTVAL('gl_journal_trx_seq')
  254.     WHERE A.session_id = pSessionId
  255.     AND A.tenant_id = pTenantId;
  256.  
  257.     INSERT INTO gl_journal_trx
  258.     (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  259.     ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
  260.     ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  261.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  262.     SELECT A.journal_trx_id, A.tenant_id, vJournalType, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date,
  263.         vOuBuId, vEmptyId, vEmptyId, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
  264.         A.ref_doc_type_id, A.ref_id, A.due_date, A.ref_curr_code, A.cashbank_remark, A.status_doc, A.workflow_status,
  265.         0, vDatetime, vUserId, vDatetime, vUserId
  266.     FROM temp_data_cashbank_in_other A
  267.     WHERE A.session_id = pSessionId
  268.     AND A.tenant_id = pTenantId;
  269.  
  270.     INSERT INTO tt_journal_trx_item
  271.     (session_id, tenant_id, journal_trx_id, line_no,
  272.     ref_doc_type_id, ref_id,
  273.     partner_id, product_id, cashbank_id, ou_rc_id,
  274.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  275.     coa_id, curr_code, qty, uom_id,
  276.     amount, journal_date, type_rate,
  277.     numerator_rate, denominator_rate, journal_desc, remark)
  278.     SELECT pSessionId, A.tenant_id, A.journal_trx_id, 1,
  279.         A.doc_type_id, A.in_out_cashbank_id,
  280.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  281.         vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  282.         f_get_ar_coa_partner(A.tenant_id, A.partner_id), A.ref_curr_code, 0, vEmptyId,
  283.         A.amount, A.doc_date, vTypeRate,
  284.         1, 1, 'AR_CASHBANK_IN', A.cashbank_remark
  285.     FROM temp_data_cashbank_in_other A
  286.     WHERE A.session_id = pSessionId
  287.     AND A.tenant_id = pTenantId;
  288.  
  289.     INSERT INTO tt_journal_trx_item
  290.     (session_id, tenant_id, journal_trx_id, line_no,
  291.     ref_doc_type_id, ref_id,
  292.     partner_id, product_id, cashbank_id, ou_rc_id,
  293.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  294.     coa_id, curr_code, qty, uom_id,
  295.     amount, journal_date, type_rate,
  296.     numerator_rate, denominator_rate, journal_desc, remark)
  297.     SELECT pSessionId, A.tenant_id, A.journal_trx_id, 1,
  298.         A.doc_type_id, B.in_out_cashbank_cost_id,
  299.         A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
  300.         B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
  301.         E.coa_id, B.curr_code, 0, vEmptyId,
  302.         B.cost_amount, A.doc_date, vTypeRate,
  303.         1, 1, 'AR_COST_RECEIPT', B.remark
  304.     FROM temp_data_cashbank_in_other A
  305.     INNER JOIN cb_in_out_cashbank_cost B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  306.     INNER JOIN m_activity_gl E ON B.activity_gl_id = E.activity_gl_id
  307.     WHERE A.session_id = pSessionId
  308.     AND A.tenant_id = pTenantId;
  309.  
  310.     INSERT INTO tt_journal_trx_item
  311.     (session_id, tenant_id, journal_trx_id, line_no,
  312.     ref_doc_type_id, ref_id,
  313.     partner_id, product_id, cashbank_id, ou_rc_id,
  314.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  315.     coa_id, curr_code, qty, uom_id,
  316.     amount, journal_date, type_rate,
  317.     numerator_rate, denominator_rate, journal_desc, remark)
  318.     SELECT pSessionId, A.tenant_id, A.journal_trx_id, 1,
  319.         A.ref_doc_type_id, A.ref_id,
  320.         A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
  321.         B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
  322.         C.coa_id, B.curr_code, 0, vEmptyId,
  323.         A.amount, A.ref_doc_date, vTypeRate,
  324.         1, 1, 'OTHERS_RECEIVE', A.cost_remark
  325.     FROM temp_data_cashbank_in_other A
  326.     INNER JOIN cb_in_out_cashbank_cost B ON A.ref_id = B.in_out_cashbank_id
  327.     INNER JOIN m_activity_gl C ON B.activity_gl_id = C.activity_gl_id
  328.     WHERE A.session_id = pSessionId
  329.     AND A.tenant_id = pTenantId;
  330.  
  331.     INSERT INTO gl_journal_trx_item
  332.     (tenant_id, journal_trx_id, line_no,
  333.     ref_doc_type_id, ref_id,
  334.     partner_id, product_id, cashbank_id, ou_rc_id,
  335.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  336.     coa_id, curr_code, qty, uom_id,
  337.     amount, journal_date, type_rate,
  338.     numerator_rate, denominator_rate, journal_desc, remark,
  339.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  340.     ou_branch_id, ou_sub_bu_id)
  341.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  342.         A.ref_doc_type_id, A.ref_id,
  343.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  344.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  345.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  346.         A.amount, A.journal_date, A.type_rate,
  347.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  348.         0, vDatetime, vUserId, vDatetime, vUserId,
  349.         vOuBranchId, vOuSubBuId
  350.     FROM tt_journal_trx_item A
  351.     WHERE A.session_id = pSessionId
  352.     AND A.journal_desc IN ('AR_COST_RECEIPT', 'AR_CASHBANK_IN');
  353.  
  354.     INSERT INTO gl_journal_trx_mapping
  355.     (tenant_id, journal_trx_id, line_no,
  356.     ref_doc_type_id, ref_id,
  357.     partner_id, product_id, cashbank_id, ou_rc_id,
  358.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  359.     coa_id, curr_code, qty, uom_id,
  360.     amount, journal_date, type_rate,
  361.     numerator_rate, denominator_rate, journal_desc, remark,
  362.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  363.     ou_branch_id, ou_sub_bu_id)
  364.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  365.         A.ref_doc_type_id, A.ref_id,
  366.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  367.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  368.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  369.         A.amount, A.journal_date, A.type_rate,
  370.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  371.         0, vDatetime, vUserId, vDatetime, vUserId,
  372.         vOuBranchId, vOuSubBuId
  373.     FROM tt_journal_trx_item A
  374.     WHERE A.session_id = pSessionId
  375.     AND A.journal_desc = 'OTHERS_RECEIVE';
  376.  
  377.     -- ambil awe_flow CCBOP yang aktif
  378.     SELECT awe_flow_id INTO vAweFlowId
  379.     FROM awe_flow
  380.     WHERE scheme = vCCBOPScheme AND flg_validate = 'Y' AND active = 'Y' AND tenant_id = pTenantId
  381.     LIMIT 1;
  382.  
  383.     INSERT INTO awe_currdoc_status(
  384.         req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
  385.         remark, current_user_id, current_role_id, flg_user_role, label,
  386.         data, flow_id, create_datetime, create_user_id, create_role_id,
  387.         update_datetime, update_user_id, update_role_id, version)
  388.     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,
  389.         A.cashbank_remark, A.create_user_id, vEmptyId, 'R', 'Conversion CB In Other To Partner' || A.doc_no,
  390.         '{}', vAweFlowId, create_datetime, create_user_id, vEmptyId,
  391.         update_datetime, update_user_id, vEmptyId, 0
  392.     FROM temp_data_cashbank_in_other A
  393.     WHERE A.session_id = pSessionId
  394.     AND A.tenant_id = pTenantId;
  395.  
  396.     -- generate_process_message_for_submit_doc
  397.     PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessName,
  398.             A.in_out_cashbank_id ||'_'||A.doc_no,
  399.             vDatetime, vParamKey, A.in_out_cashbank_id::character varying, vUserId)
  400.     FROM temp_data_cashbank_in_other A
  401.         WHERE A.session_id = pSessionId
  402.         AND A.tenant_id = pTenantId;
  403.  
  404.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  405.     DELETE FROM temp_data_cashbank_in_other WHERE session_id = pSessionId;
  406.  
  407. END;
  408. $BODY$
  409.   LANGUAGE plpgsql VOLATILE
  410.   COST 100;
  411.   /
RAW Paste Data