abirama62

f_regenerate_journal_trx_void_pos_shop

Dec 3rd, 2020 (edited)
924
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION f_regenerate_journal_trx_void_pos_shop(
  2.     character varying,
  3.     bigint,
  4.     bigint,
  5.     bigint,
  6.     character varying,
  7.     bigint,
  8.     character varying)
  9.   RETURNS void AS
  10. $BODY$
  11. DECLARE
  12.     pSessionId          ALIAS FOR $1;
  13.     pTenantId           ALIAS FOR $2;
  14.     pOuId               ALIAS FOR $3;
  15.     pTrxPosId           ALIAS FOR $4;
  16.     pProcessNo          ALIAS FOR $5;
  17.     pUserId             ALIAS FOR $6;
  18.     pDatetime           ALIAS FOR $7;
  19.  
  20.     vFlagNo             character varying(1);
  21.     vProductStatus      character varying(5);
  22.     vEmptyId            bigint;
  23.     vDocTypeAdjStock    bigint;
  24.     vFlagCash           character varying(1);
  25.     vDebitType          character varying(1);
  26.     vFlagYes            character varying(1);
  27.     vEmptyValue         character varying(1);
  28.     vJournalType        character varying(20);
  29.     vJournalTypeDoPos   character varying(20);
  30.     vJournalTypeAdjStock character varying(20);
  31.     vSignDebit          character varying(1);
  32.     vSignCredit         character varying(1);
  33.     vTypeRate           character varying(3);
  34.     vProductCOA         character varying(10);
  35.     vSystemCOA          character varying(10);
  36.     vTaxCOA             character varying(10);
  37.     vCashBankCOA        character varying(10);
  38.     vDocTypeDoPos       bigint;
  39.     vRounding           integer;
  40.     vParentOuId         bigint;
  41.     vActivityCOA        character varying(10);
  42.     vStatusPos          character varying(1);
  43.     vStatusDraft        character varying(1);
  44.     vActivityChargesId  bigint;
  45.     vDocJournalDoPos    DOC_JOURNAL%ROWTYPE;
  46.     vDocJournalAdjStock DOC_JOURNAL%ROWTYPE;
  47.     vOuStructure        OU_BU_STRUCTURE%ROWTYPE;
  48.     result              RECORD;
  49.     vLedgerCode         character varying(10);
  50.  
  51.     vPosDocTypeId           bigint;
  52.     vGroupProductNonJasaId  bigint;
  53.     vGroupProductJasaId     bigint;
  54.     vRoundingModeNonTax     character varying(5);
  55.  
  56.     vFlgPkp                 character varying;
  57.     vCountBelumDiBayar      numeric;
  58.     vDefaultRoleId          bigint;
  59.     vFlowArDebtNoteId       bigint;
  60.     vDebtNoteARDocScheme    character varying(10);
  61.     vStateDraft             character varying(10);
  62.     vDateVoid               character varying(8);
  63.     vFlagRole               character varying(1);
  64.     vDocTypeDebtNoteAr      bigint;
  65.     vCount                  character varying;
  66.     vTotalTaxItem           numeric;
  67.     vTotalTaxItemNempil     numeric;
  68.     vJournalTrxId           bigint;
  69.     vRemarkJournalTrx       text := '';
  70.     vProcessId              bigint;
  71.     pOutletId               bigint;
  72.  
  73. BEGIN
  74.  
  75.     vDocTypeDebtNoteAr := 241;
  76.     vFlagRole := 'R';
  77.     vStateDraft := 'DRAFT';
  78.     vDebtNoteARDocScheme := 'DF01';
  79.     vCountBelumDiBayar := 0;
  80.     vTotalTaxItem := 0;
  81.     vTotalTaxItemNempil := 0;
  82.     vFlgPkp := 'N';
  83.     vStatusPos := 'S';
  84.     vFlagNo := 'N';
  85.     vProductStatus := 'GOOD';
  86.     vEmptyId := -99;
  87.     vDocTypeAdjStock := 413;
  88.     vFlagCash := 'C';
  89.     vDebitType := 'D';
  90.     vFlagYes := 'Y';
  91.     vEmptyValue := ' ';
  92.     vJournalType := 'POS.SHOP';
  93.     vSignDebit := 'D';
  94.     vSignCredit := 'C';
  95.     vTypeRate := 'COM';
  96.     vProductCOA := 'PRODUCT';
  97.     vSystemCOA := 'SYSTEM';
  98.     vTaxCOA := 'TAX';
  99.     vCashBankCOA := 'CASHBANK';
  100.     vParentOuId := -99;
  101.     vActivityCOA := 'ACTIVITY'; -- DanieL : tambahan sementara agar tidak error
  102.     --vActivityChargesId := 2;
  103.     vActivityChargesId := f_get_value_system_config_by_param_code(pTenantId, 'CHARGES');
  104.     vPosDocTypeId := 401;
  105.  
  106.     SELECT A.process_message_id INTO vProcessId
  107.     FROM t_process_message A
  108.     WHERE A.tenant_id = pTenantId AND
  109.         A.process_name = 'i_ho_process_pos_shop' AND
  110.         A.process_no = pProcessNo;
  111.  
  112.     SELECT CAST(A.process_parameter_value AS bigint) INTO pOutletId
  113.     FROM t_process_parameter A
  114.     WHERE A.process_message_id = vProcessId AND
  115.         A.process_parameter_key = 'outletId';
  116.  
  117.     SELECT group_product_id INTO vGroupProductNonJasaId
  118.     FROM m_group_product A WHERE A.group_product_code = 'FG';
  119.  
  120.     SELECT group_product_id INTO vGroupProductJasaId
  121.     FROM m_group_product A WHERE A.group_product_code = 'SERVICE';
  122.  
  123.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  124.  
  125.     /*
  126.      * rounding nilai tax
  127.      */
  128.     vRounding := 0;
  129.  
  130.     -- get flag_pkp
  131.     SELECT C.flg_pkp INTO vFlgPkp
  132.     FROM m_ou_structure A
  133.     INNER JOIN t_ou B ON A.ou_bu_id = B.ou_id
  134.     INNER JOIN t_ou_legal C ON B.ou_id = C.ou_id AND B.tenant_id = C.tenant_id
  135.     WHERE A.ou_id = pOuId AND B.tenant_id = pTenantId;
  136.  
  137.     -- cek LUNAS , jika count = 0 berarti LUNAS
  138.     -- lUNAS = guest atau corporate bayar lunas
  139.     -- get Lunas
  140.     SELECT count(1) INTO vCountBelumDiBayar
  141.     FROM i_trx_pos A
  142.     INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
  143.     INNER JOIN t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
  144.     WHERE A.tenant_id = pTenantId
  145.         AND A.process_no = pProcessNo
  146.         AND A.ou_id = pOuId
  147.         AND B.under_payment_amount > 0;
  148.  
  149.     -- get void date from i_trx_log_voided_pos_custom
  150.     SELECT A.doc_date INTO vDateVoid
  151.     FROM i_trx_log_voided_pos_custom A
  152.         WHERE A.tenant_id = pTenantId
  153.         AND A.process_no = pProcessNo
  154.         AND A.ou_id = pOuId;
  155.  
  156.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  157.  
  158.     -- Cek ada journal nya atau tidak
  159.     IF EXISTS (
  160.         SELECT 1 FROM i_trx_pos A
  161.         INNER JOIN gl_journal_trx B ON A.tenant_id = B.tenant_id
  162.                                     AND A.doc_type_id = B.doc_type_id
  163.                                     AND CONCAT(A.doc_no, '_VOID') = B.doc_no
  164.                                     AND B.journal_type = vJournalType
  165.         WHERE A.tenant_id = pTenantId AND A.trx_pos_id = pTrxPosId AND A.process_no = pProcessNo) THEN
  166.  
  167.         -- Get Journal trx id & remark journal
  168.         SELECT A.journal_trx_id, A.remark INTO vJournalTrxId, vRemarkJournalTrx
  169.         FROM gl_journal_trx A
  170.         INNER JOIN i_trx_pos B ON A.tenant_id = B.tenant_id
  171.                                AND A.doc_type_id = B.doc_type_id
  172.                                AND A.doc_no = CONCAT(B.doc_no, '_VOID')
  173.         WHERE A.journal_type = vJournalType
  174.         AND B.tenant_id = pTenantId
  175.         AND B.trx_pos_id = pTrxPosId
  176.         AND B.process_no = pProcessNo;
  177.  
  178.         -- Hapus journal item dan journal mapping
  179.         DELETE FROM gl_journal_trx_item WHERE journal_trx_id = vJournalTrxId;
  180.         DELETE FROM gl_journal_trx_mapping WHERE journal_trx_id = vJournalTrxId;
  181.         DELETE FROM gl_journal_trx_fx WHERE journal_trx_id = vJournalTrxId;
  182.  
  183.         --==== Insert ulang journal item dan journal mapping ====--
  184.  
  185.         --jurnal untuk item POS SHOP non Jasa
  186.             INSERT INTO tt_journal_trx_item
  187.                 (session_id, tenant_id, journal_trx_id, line_no,
  188.                 ref_doc_type_id, ref_id,
  189.                 partner_id, product_id, cashbank_id, ou_rc_id,
  190.                 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  191.                 coa_id, curr_code, qty, uom_id,
  192.                 amount, journal_date, type_rate,
  193.                 numerator_rate, denominator_rate, journal_desc, remark)
  194.             SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  195.                     A.doc_type_id, vEmptyId,
  196.                     A.partner_id, B.product_id, vEmptyId, vEmptyId,
  197.                     vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  198.                     f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), B.curr_code, SUM(B.qty), B.base_uom_id,
  199.                     SUM(B.nett_amount_item), vDateVoid, vTypeRate,
  200.                     1, 1, 'SALES_POS', vEmptyValue
  201.             FROM i_trx_pos A, i_trx_pos_item B, m_product D, m_ctgr_product E
  202.             WHERE A.tenant_id = pTenantId AND
  203.                     A.ou_id = pOuId AND
  204.                     A.process_no = pProcessNo AND
  205.                     --A.status = vStatusPos AND -- tidak perlu memperhatikan status doc POS
  206.                     A.trx_pos_id = B.trx_pos_id AND
  207.                     A.process_no = B.process_no AND
  208.                     A.tenant_id = B.tenant_id AND
  209.             B.product_id = D.product_id AND
  210.                     A.tenant_id = D.tenant_id AND
  211.                     D.ctgr_product_id = E.ctgr_product_id AND
  212.                     E.group_product_id = vGroupProductNonJasaId
  213.             GROUP BY A.tenant_id, A.doc_type_id,
  214.                     A.partner_id, B.product_id, B.curr_code, B.base_uom_id;
  215.  
  216.         --jurnal untuk item POS SHOP Jasa
  217.         INSERT INTO tt_journal_trx_item
  218.         (session_id, tenant_id, journal_trx_id, line_no,
  219.         ref_doc_type_id, ref_id,
  220.         partner_id, product_id, cashbank_id, ou_rc_id,
  221.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  222.         coa_id, curr_code, qty, uom_id,
  223.         amount, journal_date, type_rate,
  224.         numerator_rate, denominator_rate, journal_desc, remark)
  225.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  226.             A.doc_type_id, vEmptyId,
  227.             A.partner_id, B.product_id, vEmptyId, vEmptyId,
  228.             vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  229.             f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualanJasa'), B.curr_code, SUM(B.qty), B.base_uom_id,
  230.                     SUM(B.nett_amount_item), vDateVoid, vTypeRate,
  231.             1, 1, 'SALES_POS', vEmptyValue
  232.         FROM i_trx_pos A, i_trx_pos_item B, m_product D, m_ctgr_product E
  233.         WHERE A.tenant_id = pTenantId AND
  234.             A.ou_id = pOuId AND
  235.             A.process_no = pProcessNo AND
  236.             --A.status = vStatusPos AND -- tidak perlu memperhatikan status doc POS
  237.             A.trx_pos_id = B.trx_pos_id AND
  238.             A.process_no = B.process_no AND
  239.             A.tenant_id = B.tenant_id AND
  240.             B.product_id = D.product_id AND
  241.             A.tenant_id = D.tenant_id AND
  242.             D.ctgr_product_id = E.ctgr_product_id AND
  243.             E.group_product_id = vGroupProductJasaId
  244.         GROUP BY A.tenant_id, A.doc_type_id,
  245.             A.partner_id, B.product_id, B.curr_code, B.base_uom_id;
  246.  
  247.         -- jurnal penjualan dari produk nempil, 6 Nov 2015
  248.         WITH data_tax_pos AS(
  249.             SELECT A.trx_pos_id, A.process_no, A.tenant_id,
  250.                 A.doc_type_id, SUM(B.tax_amount) AS tax_amount
  251.             FROM i_trx_pos A, i_trx_pos_item B
  252.             WHERE A.tenant_id = pTenantId AND
  253.                 A.ou_id = pOuId AND
  254.                 A.process_no = pProcessNo AND
  255.                 --A.status = vStatusPos AND
  256.                 A.trx_pos_id = B.trx_pos_id AND
  257.                 A.process_no = B.process_no AND
  258.                 A.tenant_id = B.tenant_id
  259.             GROUP BY A.trx_pos_id, A.tenant_id, A.process_no, A.doc_type_id
  260.         ),
  261.         data_tax_total AS(
  262.             SELECT A.trx_pos_id, A.process_no, A.tenant_id,
  263.                 A.doc_type_id, SUM(B.tax_amount) AS tax_amount
  264.             FROM i_trx_pos A, i_trx_pos_tax B
  265.             WHERE A.tenant_id = pTenantId AND
  266.                 A.ou_id = pOuId AND
  267.                 A.process_no = pProcessNo AND
  268.                 --A.status = vStatusPos AND
  269.                 A.trx_pos_id = B.trx_pos_id AND
  270.                 A.process_no = B.process_no AND
  271.                 A.tenant_id = B.tenant_id
  272.             GROUP BY A.trx_pos_id, A.tenant_id, A.process_no, A.doc_type_id
  273.         ),
  274.         data_tax_nempil AS(
  275.             SELECT A.trx_pos_id, A.process_no, A.tenant_id,
  276.                 A.doc_type_id, A.tax_amount - COALESCE(B.tax_amount,0) AS tax_amount
  277.             FROM data_tax_total A
  278.             LEFT JOIN data_tax_pos B ON A.trx_pos_id = B.trx_pos_id AND
  279.                                            A.tenant_id = B.tenant_id AND
  280.                                            A.process_no = B.process_no AND
  281.                                            A.doc_type_id = B.doc_type_id
  282.         )
  283.         INSERT INTO tt_journal_trx_item
  284.         (session_id, tenant_id, journal_trx_id, line_no,
  285.         ref_doc_type_id, ref_id,
  286.         partner_id, product_id, cashbank_id, ou_rc_id,
  287.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  288.         coa_id, curr_code, qty, uom_id,
  289.         amount, journal_date, type_rate,
  290.         numerator_rate, denominator_rate, journal_desc, remark)
  291.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  292.             A.doc_type_id, vEmptyId,
  293.             A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  294.             vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  295.             f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualanBarangBengkel'), B.curr_code, SUM(B.qty), vEmptyId,
  296.             CASE WHEN vFlgPkp = vFlagYes THEN
  297.             SUM(B.gross_sell_price) - COALESCE(D.tax_amount, 0) ELSE -- gross sell price = sell price * qty
  298.             SUM(B.gross_sell_price)
  299.             END,
  300.             vDateVoid, vTypeRate,
  301.             1, 1, 'SALES_POS', vEmptyValue
  302.         FROM i_trx_pos A
  303.         INNER JOIN i_trx_pos_item_nempil_barang B ON A.trx_pos_id = B.trx_pos_id AND
  304.                                                      A.process_no = B.process_no AND
  305.                                                      A.tenant_id = B.tenant_id
  306.         LEFT JOIN data_tax_nempil D ON A.trx_pos_id = D.trx_pos_id AND
  307.                                        A.tenant_id = D.tenant_id AND
  308.                                        A.doc_type_id = D.doc_type_id AND A.process_no = D.process_no
  309.         WHERE A.tenant_id = pTenantId AND
  310.             A.ou_id = pOuId AND
  311.             A.process_no = pProcessNo
  312.             -- AND A.status = vStatusPos -- tidak perlu memperhatikan status doc POS
  313.         GROUP BY A.tenant_id, A.doc_type_id,
  314.             A.partner_id, B.curr_code, D.tax_amount;
  315.  
  316.             --jurnal untuk biaya kartu kredit
  317.         INSERT INTO tt_journal_trx_item
  318.         (session_id, tenant_id, journal_trx_id, line_no,
  319.         ref_doc_type_id, ref_id,
  320.         partner_id, product_id, cashbank_id, ou_rc_id,
  321.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  322.         coa_id, curr_code, qty, uom_id,
  323.         amount, journal_date, type_rate,
  324.         numerator_rate, denominator_rate, journal_desc, remark)
  325.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  326.             A.doc_type_id, B.trx_pos_add_cost_id,
  327.             A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  328.             vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  329.             f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaKartuKredit'), B.curr_code, 0, vEmptyId,
  330.             B.add_amount, vDateVoid, vTypeRate,
  331.             1, 1, 'CHARGES_POS', B.remark
  332.         FROM i_trx_pos A, i_trx_pos_add_cost B
  333.         WHERE A.tenant_id = pTenantId AND
  334.             A.ou_id = pOuId AND
  335.             A.process_no = pProcessNo AND
  336.             --A.status = vStatusPos AND
  337.             A.trx_pos_id = B.trx_pos_id AND
  338.             A.process_no = B.process_no AND
  339.             A.tenant_id = B.tenant_id AND
  340.             B.activity_gl_id = vActivityChargesId;
  341.  
  342.         -- add if By: Ping An , 27 Nov 2015
  343.         -- Jurnal VAT Hanya untuk yang PKP saja , kalo non PKP gak perlu dijurnal PPN karena non PKP tidak memungut dan melaporkan PPN
  344.         IF vFlgPkp = vFlagYes THEN
  345.  
  346.             INSERT INTO tt_journal_trx_item
  347.             (session_id, tenant_id, journal_trx_id, line_no,
  348.             ref_doc_type_id, ref_id,
  349.             partner_id, product_id, cashbank_id, ou_rc_id,
  350.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  351.             coa_id, curr_code, qty, uom_id,
  352.             amount, journal_date, type_rate,
  353.             numerator_rate, denominator_rate, journal_desc, remark)
  354.             SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  355.                 A.doc_type_id, vEmptyId,
  356.                 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  357.                 vEmptyId, vSignDebit, vTaxCOA, vEmptyId,
  358.                 D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
  359.                 B.tax_amount , vDateVoid, vTypeRate,
  360.                 1, 1, 'VAT_OUT_POS', vEmptyValue
  361.             FROM i_trx_pos A, i_trx_pos_tax B, m_tax D
  362.             WHERE A.tenant_id = pTenantId AND
  363.                 A.ou_id = pOuId AND
  364.                 A.process_no = pProcessNo AND
  365.                 --A.status = vStatusPos AND
  366.                 A.trx_pos_id = B.trx_pos_id AND
  367.                 A.process_no = B.process_no AND
  368.                 A.tenant_id = B.tenant_id AND
  369.                 B.tax_id = D.tax_id;
  370.  
  371.  
  372.         END IF;
  373.  
  374.             --jurnal balik untuk rounding amount
  375.         INSERT INTO tt_journal_trx_item
  376.         (session_id, tenant_id, journal_trx_id, line_no,
  377.         ref_doc_type_id, ref_id,
  378.         partner_id, product_id, cashbank_id, ou_rc_id,
  379.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  380.         coa_id, curr_code, qty, uom_id,
  381.         amount, journal_date, type_rate,
  382.         numerator_rate, denominator_rate, journal_desc, remark)
  383.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  384.             A.doc_type_id, vEmptyId,
  385.             A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  386.             vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  387.             f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaPembulatanNilai'), A.curr_code, 0, vEmptyId,
  388.             A.rounding_amount, vDateVoid, vTypeRate,
  389.             1, 1, 'ROUNDING_POS', vRemarkJournalTrx
  390.         FROM i_trx_pos A
  391.         WHERE A.tenant_id = pTenantId AND
  392.             A.ou_id = pOuId AND
  393.             A.process_no = pProcessNo;
  394.  
  395.             --jurnal balik untuk refund, jika ada
  396.         INSERT INTO tt_journal_trx_item
  397.         (session_id, tenant_id, journal_trx_id, line_no,
  398.         ref_doc_type_id, ref_id,
  399.         partner_id, product_id, cashbank_id, ou_rc_id,
  400.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  401.         coa_id, curr_code, qty, uom_id,
  402.         amount, journal_date, type_rate,
  403.         numerator_rate, denominator_rate, journal_desc, remark)
  404.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  405.             A.doc_type_id, vEmptyId,
  406.             A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
  407.             vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
  408.             E.coa_id, A.curr_code, 0, vEmptyId,
  409.             A.total_refund, vDateVoid, vTypeRate,
  410.             1, 1, 'REFUND_POS', A.remark
  411.         FROM i_trx_pos A, i_cash_bank_data_outlet D, m_cashbank E
  412.         WHERE A.tenant_id = pTenantId AND
  413.             A.ou_id = pOuId AND
  414.             A.process_no = pProcessNo AND
  415.             --A.status = vStatusPos AND
  416.             D.outlet_id = pOutletId AND
  417.             D.cashbank_id = E.cashbank_id AND
  418.             A.curr_code = E.curr_code AND
  419.             E.flg_cash_bank = vFlagCash AND
  420.             A.total_refund > 0;
  421.  
  422.             --jurnal untuk cash payment
  423.         INSERT INTO tt_journal_trx_item
  424.         (session_id, tenant_id, journal_trx_id, line_no,
  425.         ref_doc_type_id, ref_id,
  426.         partner_id, product_id, cashbank_id, ou_rc_id,
  427.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  428.         coa_id, curr_code, qty, uom_id,
  429.         amount, journal_date, type_rate,
  430.         numerator_rate, denominator_rate, journal_desc, remark)
  431.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  432.             A.doc_type_id, B.trx_pos_cash_payment_id,
  433.             A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
  434.             vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
  435.             E.coa_id, B.curr_payment_code, 0, vEmptyId,
  436.             B.payment_amount, vDateVoid, vTypeRate,
  437.             1, 1, 'CASH_PAYMENT_POS', B.remark
  438.         FROM i_trx_pos A, i_trx_pos_cash_payment B, i_cash_bank_data_outlet D, m_cashbank E
  439.         WHERE A.tenant_id = pTenantId AND
  440.             A.ou_id = pOuId AND
  441.             A.process_no = pProcessNo AND
  442.             --A.status = vStatusPos AND
  443.             A.process_no = B.process_no AND
  444.             A.trx_pos_id = B.trx_pos_id AND
  445.             A.tenant_id = B.tenant_id AND
  446.             D.outlet_id = pOutletId AND
  447.             D.cashbank_id = E.cashbank_id AND
  448.             B.curr_payment_code = E.curr_code AND
  449.             E.flg_cash_bank = vFlagCash AND
  450.             B.payment_amount > 0; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal
  451.  
  452.         INSERT INTO tt_journal_trx_item
  453.         (session_id, tenant_id, journal_trx_id, line_no,
  454.         ref_doc_type_id, ref_id,
  455.         partner_id, product_id, cashbank_id, ou_rc_id,
  456.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  457.         coa_id, curr_code, qty, uom_id,
  458.         amount, journal_date, type_rate,
  459.         numerator_rate, denominator_rate, journal_desc, remark)
  460.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  461.             A.doc_type_id, B.trx_pos_non_cash_payment_id,
  462.             A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
  463.             vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
  464.             E.settle_coa_id, B.curr_payment_code, 0, vEmptyId,
  465.             B.payment_amount, vDateVoid, vTypeRate,
  466.             1, 1, 'NON_CASH_PAYMENT_POS', B.remark
  467.         FROM i_trx_pos A, i_trx_pos_non_cash_payment B, i_device_merchant_data_outlet D, m_device_merchant E
  468.         WHERE A.tenant_id = pTenantId AND
  469.             A.ou_id = pOuId AND
  470.             A.process_no = pProcessNo AND
  471.             --A.status = vStatusPos AND
  472.             A.process_no = B.process_no AND
  473.             A.trx_pos_id = B.trx_pos_id AND
  474.             A.tenant_id = B.tenant_id AND
  475.             D.outlet_id = pOutletId AND
  476.             B.device_merchant_id = D.device_merchant_id AND
  477.             D.device_merchant_id = E.device_merchant_id AND
  478.             B.payment_amount > 0; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal
  479.  
  480.         INSERT INTO gl_journal_trx_item
  481.         (tenant_id, journal_trx_id, line_no,
  482.         ref_doc_type_id, ref_id,
  483.         partner_id, product_id, cashbank_id, ou_rc_id,
  484.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  485.         coa_id, curr_code, qty, uom_id,
  486.         amount, journal_date, type_rate,
  487.         numerator_rate, denominator_rate, journal_desc, remark,
  488.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  489.         SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  490.             A.ref_doc_type_id, A.ref_id,
  491.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  492.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  493.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  494.             A.amount, A.journal_date, A.type_rate,
  495.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  496.             0, pDatetime, pUserId, pDatetime, pUserId
  497.         FROM tt_journal_trx_item A
  498.         WHERE A.session_id = pSessionId AND
  499.             A.journal_desc IN ('SALES_POS','CHARGES_POS','VAT_OUT_POS', 'ROUNDING_POS', 'REFUND_POS');
  500.  
  501.         INSERT INTO gl_journal_trx_mapping
  502.         (tenant_id, journal_trx_id, line_no,
  503.         ref_doc_type_id, ref_id,
  504.         partner_id, product_id, cashbank_id, ou_rc_id,
  505.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  506.         coa_id, curr_code, qty, uom_id,
  507.         amount, journal_date, type_rate,
  508.         numerator_rate, denominator_rate, journal_desc, remark,
  509.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  510.         SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  511.             A.ref_doc_type_id, A.ref_id,
  512.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  513.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  514.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  515.             A.amount, A.journal_date, A.type_rate,
  516.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  517.             0, pDatetime, pUserId, pDatetime, pUserId
  518.         FROM tt_journal_trx_item A
  519.         WHERE A.session_id = pSessionId AND
  520.              -- rev by Ping An 18nov2015 A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS');
  521.              A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS');
  522.  
  523.         -- update status gl_journal_trx
  524.         UPDATE gl_journal_trx
  525.         SET status_doc = 'D',
  526.             workflow_status = 'DRAFT',
  527.             update_user_id = pUserId,
  528.             update_datetime = pDatetime
  529.         WHERE journal_trx_id = vJournalTrxId;
  530.  
  531.  
  532.     END IF;
  533.  
  534.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  535.  
  536. END;
  537. $BODY$
  538.   LANGUAGE plpgsql VOLATILE
  539.   COST 100;
  540.   /
RAW Paste Data