samuel025

Function Submit Purchase Invoice

Jun 16th, 2021
795
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION pu_submit_purch_invoice(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.     vPurchInvoiceId         bigint;
  11.     vInvoiceApBalanceId     bigint;
  12.     vUserId         bigint;
  13.     vDatetime       character varying(14);
  14.     vFlagInvoice    character varying(1);
  15.     vFlagPayment    character varying(1);
  16.     vEmptyId        bigint;
  17.     vStatusRelease  character varying(1);
  18.     vStatusDraft  character varying(1);
  19.     vStatusFinal    character varying(1);
  20.     vEmptyValue     character varying(1);
  21.     vSignDebit      character varying(1);
  22.     vSignCredit     character varying(1);
  23.     vTypeRate       character varying(3);
  24.     vActivityCOA    character varying(10);
  25.     vSystemCOA      character varying(10);
  26.     vTaxCOA         character varying(10);
  27.     vJournalTrxId           bigint;
  28.    
  29.     vDocJournal             DOC_JOURNAL%ROWTYPE;
  30.     vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
  31.     result                  RECORD;
  32.     vFakturPajakMasukan     bigint;
  33.    
  34.     vPurchaseInvoiceDocTypeId bigint;
  35.     vInvPrepaymentDocTypeId bigint;
  36.     vRoundingModeNonTax     character varying(5);
  37.    
  38.     vJointDppPpn            character varying(1);
  39. BEGIN
  40.    
  41.     vFlagInvoice := 'Y';
  42.     vFlagPayment := 'N';
  43.     vEmptyId := -99;
  44.     vStatusRelease := 'R';
  45.     vStatusDraft := 'D';
  46.     vStatusFinal := 'F';
  47.     vEmptyValue := ' ';
  48.     vSignDebit := 'D';
  49.     vSignCredit := 'C';
  50.     vTypeRate := 'COM';
  51.     vActivityCOA := 'ACTIVITY';
  52.     vSystemCOA := 'SYSTEM';
  53.     vTaxCOA := 'TAX';  
  54.     vFakturPajakMasukan := 221;
  55.     vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');
  56.    
  57.     vPurchaseInvoiceDocTypeId := 131;
  58.     vInvPrepaymentDocTypeId := 203;
  59.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  60.    
  61.    
  62.     SELECT A.process_message_id INTO vProcessId
  63.     FROM t_process_message A
  64.     WHERE A.tenant_id = pTenantId AND
  65.         A.process_name = 'pu_submit_purch_invoice' AND
  66.         A.process_no = pProcessNo;
  67.        
  68.     SELECT CAST(A.process_parameter_value AS bigint) INTO vPurchInvoiceId
  69.     FROM t_process_parameter A
  70.     WHERE A.process_message_id = vProcessId AND
  71.         A.process_parameter_key = 'purchInvoiceId';
  72.    
  73.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  74.     FROM t_process_parameter A
  75.     WHERE A.process_message_id = vProcessId AND
  76.         A.process_parameter_key = 'userId';
  77.  
  78.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  79.     FROM t_process_parameter A
  80.     WHERE A.process_message_id = vProcessId AND
  81.         A.process_parameter_key = 'datetime';
  82.  
  83.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;     
  84. /*
  85.  * 1. update pu_po_balance_invoice
  86.  * 2. update pu_po_balance_invoice_tax
  87.  * 3. update status pu_invoice
  88.  * 4. add fi_invoice_ap_balance
  89.  * 5. add fi_invoice_tax_ap_balance
  90.  * 6. update fi_invoice_advance_ap_balance untuk invoice prepayment yang digunakan oleh purch invoice
  91.  * 7.add gl_journal_trx
  92.  * 8.add gl_journal_trx_item
  93.  *   1.debit accr hutang dagang sesuai doc receive goods
  94.  *   2.debit activity gl sesuai doc invoice cost
  95.  * 9.add gl_journal_trx_mapping
  96.  */    
  97.    
  98.     SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
  99.     FROM pu_invoice A
  100.     WHERE A.invoice_id = vPurchInvoiceId INTO result;
  101.    
  102.     vOuStructure := result.ou;
  103.     vDocJournal := result.doc;
  104.    
  105.     UPDATE pu_po_balance_invoice SET flg_invoice = vFlagInvoice, update_datetime = vDatetime, update_user_id = vUserId
  106.     FROM pu_invoice A
  107.     WHERE A.invoice_id = vPurchInvoiceId AND
  108.         pu_po_balance_invoice.invoice_id = A.invoice_id;
  109.        
  110.     UPDATE pu_po_balance_invoice_tax SET flg_invoice = vFlagInvoice, update_datetime = vDatetime, update_user_id = vUserId
  111.     FROM pu_invoice A
  112.     WHERE A.invoice_id = vPurchInvoiceId AND
  113.         pu_po_balance_invoice_tax.invoice_id = A.invoice_id;
  114. /* NK, 17 JAN 2013, cara ini tidak digunakan,karena program input sudah mengisi nilai invoice_id
  115.     UPDATE pu_po_balance_invoice SET flg_invoice = vFlagInvoice, invoice_id = vPurchInvoiceId
  116.     FROM pu_invoice A, pu_invoice_item B
  117.     WHERE A.invoice_id = vPurchInvoiceId AND
  118.         A.invoice_id = B.invoice_id AND
  119.         pu_po_balance_invoice.tenant_id = A.tenant_id AND
  120.         pu_po_balance_invoice.ou_id = A.ou_id AND
  121.         pu_po_balance_invoice.po_id = A.ref_id AND
  122.         pu_po_balance_invoice.ref_doc_type_id = B.ref_doc_type_id AND
  123.         pu_po_balance_invoice.ref_item_id = B.ref_item_id;
  124.        
  125.     UPDATE pu_po_balance_invoice_tax SET flg_invoice = vFlagInvoice, invoice_id = vPurchInvoiceId
  126.     FROM pu_invoice A, pu_invoice_item B
  127.     WHERE A.invoice_id = vPurchInvoiceId AND
  128.         A.invoice_id = B.invoice_id AND
  129.         pu_po_balance_invoice_tax.tenant_id = A.tenant_id AND
  130.         pu_po_balance_invoice_tax.ou_id = A.ou_id AND
  131.         pu_po_balance_invoice_tax.po_id = A.ref_id AND
  132.         pu_po_balance_invoice_tax.ref_doc_type_id = B.ref_doc_type_id AND
  133.         pu_po_balance_invoice_tax.ref_item_id = B.ref_item_id;
  134. */
  135.        
  136.     UPDATE pu_invoice SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
  137.     WHERE invoice_id = vPurchInvoiceId;
  138.        
  139.     -- Modified by fredi, 3 Dec add next val for using for insert into vat in
  140.     SELECT nextval('fi_invoice_ap_balance_seq') INTO vInvoiceApBalanceId;
  141.  
  142.     IF vJointDppPpn = 'N' THEN
  143.         INSERT INTO fi_invoice_ap_balance
  144.         ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
  145.         doc_no, doc_date, ext_doc_no, ext_doc_date,
  146.         ref_doc_type_id, ref_id,  partner_id, due_date,  
  147.         curr_code, amount, remark, payment_amount, flg_payment,
  148.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  149.         SELECT vInvoiceApBalanceId, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
  150.                 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
  151.                 A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
  152.                 A.curr_code, A.total_amount, A.remark, 0, vFlagPayment,
  153.                 0, vDatetime, vUserId, vDatetime, vUserId
  154.         FROM pu_invoice A
  155.         WHERE A.invoice_id = vPurchInvoiceId;
  156.        
  157.         -- Modified by Hans, 11 Apr 2016 update flg_payment = 'Y' jika amount invoice = 0
  158.         UPDATE fi_invoice_ap_balance A
  159.         SET flg_payment = 'Y'
  160.         WHERE A.invoice_ap_balance_id = vInvoiceApBalanceId
  161.             AND A.amount = 0;
  162.            
  163.         -- Modified by fredi, 3 Dec add insert gov_base_amount into fi_invoice_tax_ap_balance
  164.         -- get gov_base_tax_amount from pu_invoice_tax.base_amount - pu_invoice_tax.advance_amount
  165.         INSERT INTO fi_invoice_tax_ap_balance
  166.         (tenant_id, ou_id, doc_type_id, invoice_ap_balance_id,
  167.         partner_id, tax_id, tax_no, tax_date,
  168.         curr_code, tax_amount, tax_curr_code, gov_tax_amount,
  169.         due_date, remark, payment_amount, flg_payment,
  170.         "version", create_datetime, create_user_id, update_datetime, update_user_id,
  171.         gov_base_amount )
  172.         SELECT A.tenant_id, A.ou_id, vFakturPajakMasukan, C.invoice_ap_balance_id,     
  173.             A.partner_id, B.tax_id, B.tax_no, B.tax_date,
  174.             A.curr_code, B.tax_amount, B.tax_curr_code, B.gov_tax_amount,
  175.             A.due_date, B.remark, 0, vFlagPayment,
  176.             0, vDatetime, vUserId, vDatetime, vUserId,
  177.             f_get_amount_in_gov_tax_rate(A.tenant_id, B.base_amount - B.advance_amount, B.tax_date, A.curr_code)
  178.         FROM pu_invoice A, pu_invoice_tax B, fi_invoice_ap_balance C
  179.         WHERE A.invoice_id = vPurchInvoiceId AND
  180.             A.invoice_id = B.invoice_id AND
  181.             A.tenant_id = C.tenant_id AND
  182.             A.ou_id = C.ou_id AND
  183.             A.partner_id = C.partner_id AND
  184.             A.doc_type_id = C.doc_type_id AND
  185.             A.invoice_id = C.invoice_ap_id;
  186.    
  187.         /**
  188.          * Fredi, 3 Dec 2014
  189.          * Call function for insert tax data into fi_vat_in_reporting for tax
  190.          * @see http://jleaf.org:8181/browse/ERPDB-211
  191.          */
  192.         PERFORM fi_insert_vat_in_for_reporting(pTenantId, pSessionId, vInvoiceApBalanceId, vUserId, vDatetime);
  193.     ELSE
  194.         INSERT INTO fi_invoice_ap_balance
  195.         ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
  196.         doc_no, doc_date, ext_doc_no, ext_doc_date,
  197.         ref_doc_type_id, ref_id,  partner_id, due_date,  
  198.         curr_code, amount, remark, payment_amount, flg_payment,
  199.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  200.         SELECT vInvoiceApBalanceId, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
  201.                 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
  202.                 A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
  203.                 A.curr_code, A.total_amount + COALESCE(B.gov_tax_amount, 0), A.remark, 0, vFlagPayment,
  204.                 0, vDatetime, vUserId, vDatetime, vUserId
  205.         FROM pu_invoice A
  206.         LEFT OUTER JOIN pu_invoice_tax B ON A.invoice_id = B.invoice_id
  207.         WHERE A.invoice_id = vPurchInvoiceId;
  208.        
  209.         -- Modified by Hans, 11 Apr 2016 update flg_payment = 'Y' jika amount invoice = 0
  210.         UPDATE fi_invoice_ap_balance A
  211.         SET flg_payment = 'Y'
  212.         WHERE A.invoice_ap_balance_id = vInvoiceApBalanceId
  213.             AND A.amount = 0;
  214.        
  215.         PERFORM fi_insert_vat_in_invoice_ap_for_reporting(pTenantId, pSessionId, vInvoiceApBalanceId, vUserId, vDatetime);
  216.     END IF;
  217.    
  218.     /*
  219.      * NK, 4 Nov 2014
  220.      * Update fi_invoice_advance_ap_balance sehubungan dengan invoice prepayment yang digunakan oleh purch invoice
  221.      */
  222.     UPDATE fi_invoice_advance_ap_balance SET purch_invoice_id = A.invoice_id, purch_invoice_date = B.doc_date,
  223.                                             update_datetime = vDatetime, update_user_id = vUserId,
  224.                                             version = fi_invoice_advance_ap_balance.version + 1
  225.     FROM pu_invoice_advance A, fi_invoice_ap_balance B
  226.     WHERE A.invoice_id = vPurchInvoiceId AND
  227.         A.ref_id = B.invoice_ap_balance_id AND
  228.         B.invoice_ap_id = fi_invoice_advance_ap_balance.invoice_ap_id;
  229.    
  230.     /*
  231.      * NK, 18 Nov 2014
  232.      * Update fi_invoice_advance_ap_balance sehubungan dengan invoice prepayment yang digunakan oleh purch invoice
  233.      */
  234.     UPDATE fi_invoice_tax_advance_ap_balance SET purch_invoice_id = A.invoice_id, purch_invoice_date = B.doc_date,
  235.                                             update_datetime = vDatetime, update_user_id = vUserId,
  236.                                             version = fi_invoice_tax_advance_ap_balance.version + 1
  237.     FROM pu_invoice_advance A, fi_invoice_ap_balance B
  238.     WHERE A.invoice_id = vPurchInvoiceId AND
  239.         A.ref_id = B.invoice_ap_balance_id AND
  240.         B.invoice_ap_id = fi_invoice_tax_advance_ap_balance.invoice_ap_id;
  241.     /*
  242.      * NK, 18 Nov 2014
  243.      * update flg saldo po advance invoice, karena sudah digunakan oleh purch invoice
  244.      */
  245.     UPDATE pu_po_balance_advance_invoice SET flg_invoice = 'Y', invoice_id = vPurchInvoiceId,
  246.                                             update_datetime = vDatetime, update_user_id = vUserId,
  247.                                             version = pu_po_balance_advance_invoice.version + 1
  248.     FROM pu_invoice_advance A
  249.     WHERE A.invoice_id = vPurchInvoiceId AND
  250.         A.ref_id = pu_po_balance_advance_invoice.ref_id;
  251.        
  252.     IF vJointDppPpn = 'N' THEN
  253.         /**
  254.          * added by Fredi, 10 Feb 2016
  255.          * Call function for insert tax data into fi_vat_in_for_efaktur for tax
  256.          * @see http://jleaf.org:8112/issue/ERP-56
  257.          */
  258.         PERFORM fi_insert_vat_in_for_efaktur(pTenantId, pSessionId, vInvoiceApBalanceId, vUserId, vDatetime);
  259.     ELSE
  260.         PERFORM fi_insert_vat_in_invoice_ap_for_efaktur(pTenantId, pSessionId, vInvoiceApBalanceId, vUserId, vDatetime);
  261.     END IF;
  262.  
  263.     /*
  264.      * membuat data transaksi jurnal :
  265.      * 1. buat admin
  266.      * 2. buat temlate jurnal
  267.      */
  268.     PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
  269.     FROM pu_invoice A
  270.     WHERE A.invoice_id = vPurchInvoiceId;
  271.        
  272.     SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  273.    
  274.     INSERT INTO gl_journal_trx
  275.     (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  276.     ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  277.     ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  278.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  279.     SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.invoice_id, A.doc_no, A.doc_date,
  280.         (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
  281.         A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
  282.         0, vDatetime, vUserId, vDatetime, vUserId
  283.     FROM pu_invoice A
  284.     WHERE A.invoice_id = vPurchInvoiceId;
  285.    
  286.     /*
  287.      * journal detail item receive goods
  288.      * tanggal rate menggunakan tanggal receive goods
  289.      *
  290.      */
  291.     INSERT INTO tt_journal_trx_item
  292.     (session_id, tenant_id, journal_trx_id, line_no,
  293.     ref_doc_type_id, ref_id,
  294.     partner_id, product_id, cashbank_id, ou_rc_id,
  295.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  296.     coa_id, curr_code, qty, uom_id,
  297.     amount, journal_date, type_rate,
  298.     numerator_rate, denominator_rate, journal_desc, remark)
  299.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  300.         C.doc_type_id, C.receive_goods_id,
  301.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  302.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  303.         f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
  304.         B.ref_item_amount, C.doc_date, vTypeRate,
  305.         1, 1, 'ACCR_AP', B.remark
  306.     FROM pu_invoice A, pu_invoice_item B, pu_receive_goods C
  307.     WHERE A.invoice_id = vPurchInvoiceId AND
  308.         A.invoice_id = B.invoice_id AND
  309.         B.ref_id = C.receive_goods_id;
  310.     /*
  311.      * journal detail additional cost purch invoice
  312.      * tanggal rate menggunakan tanggal invoice
  313.      */
  314.     INSERT INTO tt_journal_trx_item
  315.     (session_id, tenant_id, journal_trx_id, line_no,
  316.     ref_doc_type_id, ref_id,
  317.     partner_id, product_id, cashbank_id, ou_rc_id,
  318.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  319.     coa_id, curr_code, qty, uom_id,
  320.     amount, journal_date, type_rate,
  321.     numerator_rate, denominator_rate, journal_desc, remark)
  322.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  323.         A.doc_type_id, B.invoice_cost_id,
  324.         A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
  325. -- NK, 30 Agustus 2014 - add segment_id    
  326.         B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
  327.         C.coa_id, B.curr_code, 0, vEmptyId,
  328.         B.add_amount, A.doc_date, vTypeRate,
  329.         1, 1, 'PURCH_COST', B.remark
  330.     FROM pu_invoice A, pu_invoice_cost B, m_activity_gl C
  331.     WHERE A.invoice_id = vPurchInvoiceId AND
  332.         A.invoice_id = B.invoice_id AND
  333.         B.activity_gl_id = C.activity_gl_id;
  334.  
  335.     /*
  336.      * journal detail alokasi advance purch invoice
  337.      * tanggal rate menggunakan tanggal advance invoice
  338.      */    
  339.     INSERT INTO tt_journal_trx_item
  340.     (session_id, tenant_id, journal_trx_id, line_no,
  341.     ref_doc_type_id, ref_id,
  342.     partner_id, product_id, cashbank_id, ou_rc_id,
  343.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  344.     coa_id, curr_code, qty, uom_id,
  345.     amount, journal_date, type_rate,
  346.     numerator_rate, denominator_rate, journal_desc, remark)
  347.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  348.         A.doc_type_id, B.invoice_advance_id,
  349.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  350.         vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  351.         f_get_system_coa_by_group_coa(A.tenant_id, 'UangMukaPembelian'), B.advance_curr_code, 0, vEmptyId,
  352.         B.advance_amount, E.doc_date, vTypeRate,
  353.         1, 1, 'PURCH_ADVANCE', B.remark
  354.     FROM pu_invoice A, pu_invoice_advance B, fi_invoice_ap_balance E
  355.     WHERE A.invoice_id = vPurchInvoiceId AND
  356.         A.invoice_id = B.invoice_id AND
  357.         B.ref_doc_type_id = E.doc_type_id AND
  358.         B.ref_id = E.invoice_ap_balance_id;
  359.     /*
  360.      * journal detail tax purch invoice
  361.      * tanggal rate menggunakan tanggal invoice
  362.      */
  363.     INSERT INTO tt_journal_trx_item
  364.     (session_id, tenant_id, journal_trx_id, line_no,
  365.     ref_doc_type_id, ref_id,
  366.     partner_id, product_id, cashbank_id, ou_rc_id,
  367.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  368.     coa_id, curr_code, qty, uom_id,
  369.     amount, journal_date, type_rate,
  370.     numerator_rate, denominator_rate, journal_desc, remark)
  371.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  372.         A.doc_type_id, B.invoice_tax_id,
  373.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  374.         vEmptyId, vSignDebit, vTaxCOA, vEmptyId,
  375.         C.receive_coa_id, C.tax_curr_code, 0, vEmptyId,
  376.         B.gov_tax_amount, A.doc_date, vTypeRate,
  377.         1, 1, 'VAT_IN', B.remark
  378.     FROM pu_invoice A, pu_invoice_tax B, m_tax C
  379.     WHERE A.invoice_id = vPurchInvoiceId AND
  380.         A.invoice_id = B.invoice_id AND
  381.         B.tax_id = C.tax_id;   
  382.     /*
  383.      * buat juournal offside untuk yg credit ap
  384.      * dengan grouping data journal yang lain melakukan debit account lain
  385.      */
  386.     INSERT INTO tt_journal_trx_item
  387.     (session_id, tenant_id, journal_trx_id, line_no,
  388.     ref_doc_type_id, ref_id,
  389.     partner_id, product_id, cashbank_id, ou_rc_id,
  390.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  391.     coa_id, curr_code, qty, uom_id,
  392.     amount, journal_date, type_rate,
  393.     numerator_rate, denominator_rate, journal_desc, remark)
  394.     SELECT A.session_id, B.tenant_id, B.journal_trx_id, 1,
  395.         B.doc_type_id, B.doc_id,
  396.         B.partner_id, vEmptyId, vEmptyId, vEmptyId,
  397.         vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  398.         f_get_ap_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vEmptyId,
  399.         SUM(A.amount), B.doc_date, vTypeRate,
  400.         1, 1, 'AP', B.remark
  401.     FROM tt_journal_trx_item A, gl_journal_trx B
  402.     WHERE A.session_id = pSessionId AND
  403.         A.journal_trx_id = B.journal_trx_id AND
  404.         B.journal_trx_id = vJournalTrxId AND
  405.         A.sign_journal = vSignDebit AND
  406.         A.journal_desc IN ('ACCR_AP','PURCH_COST','VAT_IN')
  407.     GROUP BY A.session_id, B.tenant_id, B.journal_trx_id, B.doc_type_id, B.doc_id,
  408.         B.partner_id, A.curr_code, B.remark;
  409.  
  410.     /*
  411.      * buat juournal offside untuk yg debit ap
  412.      * dengan grouping data journal yang lain melakukan credit account lain
  413.      */
  414.     INSERT INTO tt_journal_trx_item
  415.     (session_id, tenant_id, journal_trx_id, line_no,
  416.     ref_doc_type_id, ref_id,
  417.     partner_id, product_id, cashbank_id, ou_rc_id,
  418.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  419.     coa_id, curr_code, qty, uom_id,
  420.     amount, journal_date, type_rate,
  421.     numerator_rate, denominator_rate, journal_desc, remark)
  422.     SELECT A.session_id, B.tenant_id, B.journal_trx_id, 1,
  423.         B.doc_type_id, B.doc_id,
  424.         B.partner_id, vEmptyId, vEmptyId, vEmptyId,
  425.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  426.         f_get_ap_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vEmptyId,
  427.         SUM(A.amount), B.doc_date, vTypeRate,
  428.         1, 1, 'AP', B.remark
  429.     FROM tt_journal_trx_item A, gl_journal_trx B
  430.     WHERE A.session_id = pSessionId AND
  431.         A.journal_trx_id = B.journal_trx_id AND
  432.         B.journal_trx_id = vJournalTrxId AND
  433.         A.sign_journal = vSignCredit AND
  434.         A.journal_desc IN ('PURCH_ADVANCE')
  435.     GROUP BY A.session_id, B.tenant_id, B.journal_trx_id, B.doc_type_id, B.doc_id,
  436.         B.partner_id, A.curr_code, B.remark;
  437.  
  438.     INSERT INTO gl_journal_trx_item
  439.     (tenant_id, journal_trx_id, line_no,
  440.     ref_doc_type_id, ref_id,
  441.     partner_id, product_id, cashbank_id, ou_rc_id,
  442.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  443.     coa_id, curr_code, qty, uom_id,
  444.     amount, journal_date, type_rate,
  445.     numerator_rate, denominator_rate, journal_desc, remark,
  446.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  447.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  448.         A.ref_doc_type_id, A.ref_id,
  449.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  450.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  451.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  452.         A.amount, A.journal_date, A.type_rate,
  453.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  454.         0, vDatetime, vUserId, vDatetime, vUserId
  455.     FROM tt_journal_trx_item A
  456.     WHERE A.session_id = pSessionId AND
  457.         journal_desc IN ('ACCR_AP','PURCH_COST','PURCH_ADVANCE','VAT_IN');
  458.  
  459.     INSERT INTO gl_journal_trx_mapping
  460.     (tenant_id, journal_trx_id, line_no,
  461.     ref_doc_type_id, ref_id,
  462.     partner_id, product_id, cashbank_id, ou_rc_id,
  463.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  464.     coa_id, curr_code, qty, uom_id,
  465.     amount, journal_date, type_rate,
  466.     numerator_rate, denominator_rate, journal_desc, remark,
  467.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  468.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  469.         A.ref_doc_type_id, A.ref_id,
  470.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  471.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  472.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  473.         A.amount, A.journal_date, A.type_rate,
  474.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  475.         0, vDatetime, vUserId, vDatetime, vUserId
  476.     FROM tt_journal_trx_item A
  477.     WHERE A.session_id = pSessionId AND
  478.         journal_desc IN ('AP');
  479.        
  480.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;     
  481.        
  482. END;   
  483. $BODY$
  484.   LANGUAGE plpgsql VOLATILE
  485.   COST 100;
  486.   /
  487.  
RAW Paste Data