samuel025

Function Submit Sales Invoice

Jun 18th, 2021
784
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION sl_submit_sales_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.     vSalesInvoiceId         bigint;
  11.     vSalesInvoiceTempId     bigint;
  12.     vInvoiceArBalanceId     bigint;
  13.     vUserId                 bigint;
  14.     vDatetime               character varying(14);
  15.     vFlagInvoice            character varying(1);
  16.     vFlagPayment            character varying(1);
  17.     vEmptyId                bigint;
  18.     vStatusRelease          character varying(1);
  19.     vStatusDraft            character varying(1);  
  20.     vStatusFinal            character varying(1);
  21.     vEmptyValue             character varying(1);
  22.     vEmptyString            character varying;
  23.     vJournalType            character varying(20);
  24.     vSignDebit              character varying(1);
  25.     vSignCredit             character varying(1);
  26.     vTypeRate               character varying(3);
  27.     vActivityCOA            character varying(10);
  28.     vSystemCOA              character varying(10);
  29.     vTaxCOA                 character varying(10);
  30.     vParentOuId             bigint;
  31.     vJournalTrxId           bigint;
  32.     vOuStructureDo          OU_BU_STRUCTURE%ROWTYPE;
  33.    
  34.     vDocJournal             DOC_JOURNAL%ROWTYPE;
  35.     vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
  36.     result                  RECORD;
  37.     vFakturPajakKeluaran    bigint;
  38.     vInvArBalanceId         bigint;
  39.     vInvTaxArBalanceId      bigint;
  40.     vSalesInvDate           character varying(8);
  41.     vSlsInvTempDocTypeId    bigint;
  42.     vDoDocTypeId            bigint;
  43.     vRegularDiscount        bigint;
  44.     vPromoDiscount          bigint;
  45.     vOuId                   bigint;
  46.     vOuWarehouseId          bigint;
  47.     vCoaIdGIT               bigint;
  48.     vProductCOA             character varying(10);
  49.     vRoundingModeNonTax     character varying(5);
  50.     vDiffAdjRegularDiscAmount       numeric;
  51.     vDiffAdjPromoDiscAmount         numeric;
  52.     vMaxItemAmount          numeric;
  53.     vMaxAdjPromoItemAmount  numeric;
  54.     vInProgress                 character varying(1);
  55.     vYes                        character varying(1);
  56.     vNo                         character varying(1);
  57.     vNol                        numeric;
  58.     vReleased                   character varying(1);
  59.     vFlgGunggung                character varying(1);
  60.    
  61.     vJointDppPpn            character varying(1);
  62. BEGIN
  63.    
  64.     vFlagInvoice := 'Y';
  65.     vFlagPayment := 'N';
  66.     vEmptyId := -99;
  67.     vStatusRelease := 'R';
  68.     vStatusDraft := 'D';   
  69.     vStatusFinal := 'F';
  70.     vEmptyValue := ' ';
  71.     vSignDebit := 'D';
  72.     vSignCredit := 'C';
  73.     vTypeRate := 'COM';
  74.     vActivityCOA := 'ACTIVITY';
  75.     vSystemCOA := 'SYSTEM';
  76.     vProductCOA := 'PRODUCT';
  77.     vTaxCOA := 'TAX';  
  78.     vFakturPajakKeluaran := 281;
  79.     vSlsInvTempDocTypeId := 361;
  80.     vDoDocTypeId := 311;
  81.     vInProgress := 'I';
  82.     vYes := 'Y';
  83.     vNo := 'N';
  84.     vNol := 0;
  85.     vReleased := 'R';
  86.     vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');
  87.     vEmptyString := '';
  88.     vCoaIdGIT:=f_get_system_coa_by_group_coa(pTenantId, 'PersediaanInTransit');
  89.    
  90.     /*
  91.      * membuat data transaksi jurnal :
  92.      * 1. buat admin
  93.      * 2. buat temlate jurnal
  94.      */    
  95.     SELECT A.process_message_id INTO vProcessId
  96.     FROM t_process_message A
  97.     WHERE A.tenant_id = pTenantId AND
  98.         A.process_name = 'sl_submit_sales_invoice' AND
  99.         A.process_no = pProcessNo;
  100.        
  101.     SELECT CAST(A.process_parameter_value AS bigint) INTO vSalesInvoiceId
  102.     FROM t_process_parameter A
  103.     WHERE A.process_message_id = vProcessId AND
  104.         A.process_parameter_key = 'salesInvoiceId';
  105.        
  106.        
  107.     SELECT B.ou_id, C.ou_id INTO vOuId, vOuWarehouseId
  108.     FROM sl_invoice_temp_intransit A
  109.     JOIN sl_do B ON A.do_id = B.do_id
  110.     INNER JOIN m_warehouse_ou C ON B.warehouse_id = C.warehouse_id
  111.     WHERE A.invoice_id = vSalesInvoiceId;
  112.    
  113.     IF (vOuId <> vOuWarehouseId) THEN
  114.         SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
  115.         vOuStructureDo := result.ou_structure;
  116.     ELSE
  117.         vOuStructureDo := ROW(-99, -99, -99);
  118.     END IF;
  119.    
  120.    
  121.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  122.     FROM t_process_parameter A
  123.     WHERE A.process_message_id = vProcessId AND
  124.         A.process_parameter_key = 'userId';
  125.  
  126.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  127.     FROM t_process_parameter A
  128.     WHERE A.process_message_id = vProcessId AND
  129.         A.process_parameter_key = 'datetime';
  130.    
  131.     WITH activity_gl_regular_disc AS (
  132.         SELECT A.activity_gl_code
  133.         FROM m_activity_gl A
  134.         WHERE A.activity_gl_code = 'REGULAR_DISC'
  135.     )
  136.     SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, A.activity_gl_code) AS bigint) INTO vRegularDiscount
  137.     FROM activity_gl_regular_disc A;
  138.    
  139.     WITH activity_gl_promo_disc AS (
  140.         SELECT A.activity_gl_code
  141.         FROM m_activity_gl A
  142.         WHERE A.activity_gl_code = 'PROMO_DISC'
  143.     )
  144.     SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, A.activity_gl_code) AS bigint) INTO vPromoDiscount
  145.     FROM activity_gl_promo_disc A;
  146.    
  147.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  148.  
  149.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;     
  150. /*
  151.  * 1. update status doc sl_invoice
  152.  * 2. update flg_invoice di tabel sl_so_balance_invoice
  153.  * 3. update adj_regular_disc_amount di tabel sl_so_balance_invoice
  154.  * 4. update adj_promo_disc_amount di tabel sl_so_balance_invoice
  155.  * 5. update sl_so_balance_invoice_tax
  156.  * 6. add fi_invoice_ar_balance
  157.  * 7. add fi_invoice_tax_ar_balance
  158.  * 8.add gl_journal_trx
  159.  * 9.add gl_journal_trx_item
  160.  * 10.add gl_journal_trx_mapping
  161.  */    
  162.     SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc, ref_inv_temp_id as salesInvTempId, A.doc_date
  163.     FROM sl_invoice A
  164.     WHERE A.invoice_id = vSalesInvoiceId INTO result;
  165.    
  166.     vOuStructure := result.ou;
  167.     vDocJournal := result.doc;
  168.     vSalesInvoiceTempId := result.salesInvTempId;
  169.     vSalesInvDate := result.doc_date;
  170.        
  171.     UPDATE sl_invoice SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
  172.     WHERE invoice_id = vSalesInvoiceId;
  173.    
  174.     UPDATE sl_so_balance_invoice A SET flg_invoice = vFlagInvoice, update_datetime = vDatetime, update_user_id = vUserId
  175.     FROM sl_invoice B, sl_invoice_item C
  176.     WHERE B.invoice_id = vSalesInvoiceId AND
  177.         B.invoice_id = C.invoice_id AND
  178.         A.ou_id = B.ou_id AND
  179.         A.so_id = B.ref_id AND
  180.         A.ref_doc_type_id = vDoDocTypeId AND
  181.         A.ref_id = C.ref_id AND
  182.         A.ref_item_id = C.ref_item_id AND
  183.         A.do_receipt_item_id = C.do_receipt_item_id;
  184.    
  185.     -- Update discount di sl_so_balance_invoice
  186.     IF vRoundingModeNonTax = 'RD' THEN
  187.    
  188.         -- Update adj_regular_disc_amount
  189.         WITH total_regular_disc_amount AS (
  190.             SELECT D.add_amount, SUM(A.regular_disc_amount) AS sum_regular_disc_amount
  191.             FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
  192.             WHERE B.invoice_id = vSalesInvoiceId AND
  193.                 B.invoice_id = C.invoice_id AND
  194.                 A.ou_id = B.ou_id AND
  195.                 A.so_id = B.ref_id AND
  196.                 A.ref_doc_type_id = vDoDocTypeId AND
  197.                 A.ref_id = C.ref_id AND
  198.                 A.ref_item_id = C.ref_item_id AND
  199.                 A.do_receipt_item_id = C.do_receipt_item_id AND
  200.                 D.invoice_id = B.invoice_id AND
  201.                 D.activity_gl_id = vRegularDiscount
  202.             GROUP BY D.add_amount
  203.         )
  204.         UPDATE sl_so_balance_invoice A SET adj_regular_disc_amount = TRUNC(((A.regular_disc_amount / B.sum_regular_disc_amount) * (B.add_amount * -1)) - A.regular_disc_amount, f_get_digit_decimal_doc_curr(vDoDocTypeId, A.curr_code)),
  205.             update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
  206.         FROM total_regular_disc_amount B
  207.         WHERE A.invoice_id = vSalesInvoiceId AND B.sum_regular_disc_amount > 0;
  208.        
  209.         -- Update adj_promo_disc_amount
  210.         WITH total_promo_disc_amount AS (
  211.             SELECT D.add_amount, SUM(A.promo_disc_amount) AS sum_promo_disc_amount
  212.             FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
  213.             WHERE B.invoice_id = vSalesInvoiceId AND
  214.                 B.invoice_id = C.invoice_id AND
  215.                 A.ou_id = B.ou_id AND
  216.                 A.so_id = B.ref_id AND
  217.                 A.ref_doc_type_id = vDoDocTypeId AND
  218.                 A.ref_id = C.ref_id AND
  219.                 A.ref_item_id = C.ref_item_id AND
  220.                 A.do_receipt_item_id = C.do_receipt_item_id AND
  221.                 D.invoice_id = B.invoice_id AND
  222.                 D.activity_gl_id = vPromoDiscount
  223.             GROUP BY D.add_amount
  224.         )
  225.         UPDATE sl_so_balance_invoice A SET adj_promo_disc_amount = TRUNC(((A.promo_disc_amount / B.sum_promo_disc_amount) * (B.add_amount * -1)) - A.promo_disc_amount, f_get_digit_decimal_doc_curr(vDoDocTypeId, A.curr_code)),
  226.             update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
  227.         FROM total_promo_disc_amount B
  228.         WHERE A.invoice_id = vSalesInvoiceId AND B.sum_promo_disc_amount > 0;
  229.    
  230.     ELSE
  231.    
  232.         -- Update adj_regular_disc_amount
  233.         WITH total_regular_disc_amount AS (
  234.             SELECT D.add_amount, SUM(A.regular_disc_amount) AS sum_regular_disc_amount
  235.             FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
  236.             WHERE B.invoice_id = vSalesInvoiceId AND
  237.                 B.invoice_id = C.invoice_id AND
  238.                 A.ou_id = B.ou_id AND
  239.                 A.so_id = B.ref_id AND
  240.                 A.ref_doc_type_id = vDoDocTypeId AND
  241.                 A.ref_id = C.ref_id AND
  242.                 A.ref_item_id = C.ref_item_id AND
  243.                 A.do_receipt_item_id = C.do_receipt_item_id AND
  244.                 D.invoice_id = B.invoice_id AND
  245.                 D.activity_gl_id = vRegularDiscount
  246.             GROUP BY D.add_amount
  247.         )
  248.         UPDATE sl_so_balance_invoice A SET adj_regular_disc_amount = ROUND(((A.regular_disc_amount / B.sum_regular_disc_amount) * (B.add_amount * -1)) - A.regular_disc_amount, f_get_digit_decimal_doc_curr(vDoDocTypeId, A.curr_code)),
  249.             update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
  250.         FROM total_regular_disc_amount B
  251.         WHERE A.invoice_id = vSalesInvoiceId AND B.sum_regular_disc_amount > 0;
  252.        
  253.         -- Update adj_promo_disc_amount
  254.         WITH total_promo_disc_amount AS (
  255.             SELECT D.add_amount, SUM(A.promo_disc_amount) AS sum_promo_disc_amount
  256.             FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
  257.             WHERE B.invoice_id = vSalesInvoiceId AND
  258.                 B.invoice_id = C.invoice_id AND
  259.                 A.ou_id = B.ou_id AND
  260.                 A.so_id = B.ref_id AND
  261.                 A.ref_doc_type_id = vDoDocTypeId AND
  262.                 A.ref_id = C.ref_id AND
  263.                 A.ref_item_id = C.ref_item_id AND
  264.                 A.do_receipt_item_id = C.do_receipt_item_id AND
  265.                 D.invoice_id = B.invoice_id AND
  266.                 D.activity_gl_id = vPromoDiscount
  267.             GROUP BY D.add_amount
  268.         )
  269.         UPDATE sl_so_balance_invoice A SET adj_promo_disc_amount = ROUND(((A.promo_disc_amount / B.sum_promo_disc_amount) * (B.add_amount * -1)) - A.promo_disc_amount, f_get_digit_decimal_doc_curr(vDoDocTypeId, A.curr_code)),
  270.             update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
  271.         FROM total_promo_disc_amount B
  272.         WHERE A.invoice_id = vSalesInvoiceId AND B.sum_promo_disc_amount > 0;
  273.    
  274.     END IF;
  275.    
  276.     -- cari sisa dari hasil pembulatan adj_regular_disc_amount
  277.     SELECT (D.add_amount * -1) - SUM(A.regular_disc_amount) - SUM(A.adj_regular_disc_amount) AS diff_adj_regular_disc_amount,
  278.             MAX(A.item_amount) AS max_item_amount INTO vDiffAdjRegularDiscAmount, vMaxItemAmount
  279.     FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
  280.     WHERE B.invoice_id = vSalesInvoiceId AND
  281.         B.invoice_id = C.invoice_id AND
  282.         A.ou_id = B.ou_id AND
  283.         A.so_id = B.ref_id AND
  284.         A.ref_doc_type_id = vDoDocTypeId AND
  285.         A.ref_id = C.ref_id AND
  286.         A.ref_item_id = C.ref_item_id AND
  287.         A.do_receipt_item_id = C.do_receipt_item_id AND
  288.         D.invoice_id = B.invoice_id AND
  289.         D.activity_gl_id = vRegularDiscount
  290.     GROUP BY D.add_amount;
  291.    
  292.     -- cari sisa dari hasil pembulatan adj_promo_disc_amount
  293.     SELECT (D.add_amount * -1) - SUM(A.promo_disc_amount) - SUM(A.adj_promo_disc_amount) AS diff_adj_promo_disc_amount,
  294.             MAX(A.item_amount) AS max_item_amount INTO vDiffAdjPromoDiscAmount, vMaxAdjPromoItemAmount
  295.     FROM sl_so_balance_invoice A, sl_invoice B, sl_invoice_item C, sl_invoice_cost D
  296.     WHERE B.invoice_id = vSalesInvoiceId AND
  297.         B.invoice_id = C.invoice_id AND
  298.         A.ou_id = B.ou_id AND
  299.         A.so_id = B.ref_id AND
  300.         A.ref_doc_type_id = vDoDocTypeId AND
  301.         A.ref_id = C.ref_id AND
  302.         A.ref_item_id = C.ref_item_id AND
  303.         A.do_receipt_item_id = C.do_receipt_item_id AND
  304.         D.invoice_id = B.invoice_id AND
  305.         D.activity_gl_id = vPromoDiscount
  306.     GROUP BY D.add_amount;
  307.    
  308.     vDiffAdjPromoDiscAmount := COALESCE(vDiffAdjPromoDiscAmount, 0);
  309.     vDiffAdjRegularDiscAmount := COALESCE(vDiffAdjRegularDiscAmount, 0);
  310.     vMaxItemAmount := COALESCE(vMaxItemAmount, 0);
  311.     vMaxAdjPromoItemAmount := COALESCE(vMaxAdjPromoItemAmount, 0);
  312.    
  313.     SELECT GREATEST(vMaxItemAmount, vMaxAdjPromoItemAmount) INTO vMaxItemAmount;
  314.    
  315.     -- tambahkan sisa pembulatan adj_regular_disc_amount dan adj_promo_disc_amount ke item yg punya invoice paling tinggi
  316.     UPDATE sl_so_balance_invoice A SET adj_regular_disc_amount = A.adj_regular_disc_amount + vDiffAdjRegularDiscAmount,
  317.         adj_promo_disc_amount = A.adj_promo_disc_amount + vDiffAdjPromoDiscAmount,
  318.         update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1
  319.     FROM (SELECT so_balance_invoice_id FROM sl_so_balance_invoice WHERE invoice_id = vSalesInvoiceId AND item_amount = vMaxItemAmount LIMIT 1) B
  320.     WHERE A.so_balance_invoice_id = B.so_balance_invoice_id;
  321.    
  322.        
  323.     UPDATE sl_so_balance_invoice_tax A SET flg_invoice = vFlagInvoice, update_datetime = vDatetime, update_user_id = vUserId
  324.     FROM sl_invoice B, sl_invoice_item C
  325.     WHERE B.invoice_id = vSalesInvoiceId AND
  326.         B.invoice_id = C.invoice_id AND
  327.         A.ou_id = B.ou_id AND
  328.         A.so_id = B.ref_id AND
  329.         A.ref_doc_type_id = vDoDocTypeId AND
  330.         A.ref_id = C.ref_id AND
  331.         A.ref_item_id = C.ref_item_id AND
  332.         A.do_receipt_item_id = C.do_receipt_item_id;
  333.        
  334.     -- Modified by fredi, 3 Dec add next val for using for insert into vat out
  335.     SELECT nextval('fi_invoice_ar_balance_seq') INTO vInvoiceArBalanceId;
  336.    
  337.     IF vJointDppPpn = 'N' THEN
  338.         INSERT INTO fi_invoice_ar_balance
  339.         ( invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
  340.         doc_no, doc_date, ext_doc_no, ext_doc_date,
  341.         ref_doc_type_id, ref_id,  partner_id, due_date,  
  342.         curr_code, amount, remark, payment_amount, flg_payment,
  343.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  344.         SELECT vInvoiceArBalanceId, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
  345.                 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
  346.                 A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
  347.                 A.curr_code, A.total_amount, A.remark, 0, vFlagPayment,
  348.                 0, vDatetime, vUserId, vDatetime, vUserId
  349.         FROM sl_invoice A
  350.         WHERE A.invoice_id = vSalesInvoiceId;
  351.                
  352.         -- Modified by fredi, 2 Dec add insert gov_base_amount into fi_invoice_tax_ap_balance
  353.         -- get gov_base_tax_amount from fi_invoice_ar_tax.base_amount
  354.         INSERT INTO fi_invoice_tax_ar_balance
  355.         (tenant_id, ou_id, doc_type_id, invoice_ar_balance_id,
  356.         partner_id, tax_id, tax_no, tax_date,
  357.         curr_code, tax_amount, tax_curr_code, gov_tax_amount,
  358.         due_date, remark, payment_amount, flg_payment,
  359.         "version", create_datetime, create_user_id, update_datetime, update_user_id,
  360.         gov_base_amount)
  361.         SELECT A.tenant_id, A.ou_id, vFakturPajakKeluaran, C.invoice_ar_balance_id,    
  362.             A.partner_id, B.tax_id, B.tax_no, B.tax_date,
  363.             A.curr_code, B.tax_amount, B.tax_curr_code, B.gov_tax_amount,
  364.             A.due_date, B.remark, 0, vFlagPayment,
  365.             0, vDatetime, vUserId, vDatetime, vUserId,
  366.             f_get_amount_in_gov_tax_rate(A.tenant_id, B.base_amount - B.advance_amount, B.tax_date, A.curr_code)
  367.         FROM sl_invoice A, sl_invoice_tax B, fi_invoice_ar_balance C
  368.         WHERE A.invoice_id = vSalesInvoiceId AND
  369.             A.invoice_id = B.invoice_id AND
  370.             A.tenant_id = C.tenant_id AND
  371.             A.doc_type_id = C.doc_type_id AND
  372.             A.invoice_id = C.invoice_ar_id;
  373.    
  374.     ELSE
  375.         INSERT INTO fi_invoice_ar_balance
  376.         ( invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
  377.         doc_no, doc_date, ext_doc_no, ext_doc_date,
  378.         ref_doc_type_id, ref_id,  partner_id, due_date,  
  379.         curr_code, amount, remark, payment_amount, flg_payment,
  380.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  381.         SELECT vInvoiceArBalanceId, A.tenant_id, A.ou_id, A.doc_type_id, A.invoice_id,
  382.                 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
  383.                 A.ref_doc_type_id, A.ref_id, A.partner_id, A.due_date,
  384.                 A.curr_code, A.total_amount + COALESCE(B.gov_tax_amount, 0), A.remark, 0, vFlagPayment,
  385.                 0, vDatetime, vUserId, vDatetime, vUserId
  386.         FROM sl_invoice A
  387.         LEFT OUTER JOIN sl_invoice_tax B ON A.invoice_id = B.invoice_id
  388.         WHERE A.invoice_id = vSalesInvoiceId;
  389.        
  390.     END IF;
  391.  
  392.     -- ====================================================================================== --
  393.     /* HS, 30 Jan 2020
  394.      * Menambahkan data invoice ar balance untuk keperluan serah terima invoice AR
  395.      * berdasarkan data balance invoice ar dari table fi_invoice_ar_balance dan fi_invoice_tax_ar_balance
  396.      */
  397.     INSERT INTO fi_invoice_ar_balance_handover(
  398.             tenant_id, invoice_id, doc_type_id, employee_id, flg_handover,
  399.             handover_id, handover_doc_type_id, flg_handover_back, handover_back_id, handover_back_doc_type_id,
  400.             handover_back_status, handover_back_remark, version, create_user_id, create_datetime,
  401.             update_user_id, update_datetime)
  402.     SELECT  tenant_id, invoice_ar_balance_id, doc_type_id, vEmptyId, vNo,
  403.             vEmptyId, vEmptyId, vNo, vEmptyId, vEmptyId,
  404.             vEmptyString, vEmptyString, 0, vUserId, vDatetime,
  405.             vUserId, vDatetime
  406.     FROM fi_invoice_ar_balance
  407.     WHERE invoice_ar_balance_id = vInvoiceArBalanceId;
  408.  
  409.     INSERT INTO fi_invoice_ar_balance_handover(
  410.             tenant_id, invoice_id, doc_type_id, employee_id, flg_handover,
  411.             handover_id, handover_doc_type_id, flg_handover_back, handover_back_id, handover_back_doc_type_id,
  412.             handover_back_status, handover_back_remark, version, create_user_id, create_datetime,
  413.             update_user_id, update_datetime)
  414.     SELECT  tenant_id, invoice_tax_ar_balance_id, doc_type_id, vEmptyId, vNo,
  415.             vEmptyId, vEmptyId, vNo, vEmptyId, vEmptyId,
  416.             vEmptyString, vEmptyString, 0, vUserId, vDatetime,
  417.             vUserId, vDatetime
  418.     FROM fi_invoice_tax_ar_balance
  419.     WHERE invoice_ar_balance_id = vInvoiceArBalanceId;
  420.    
  421.     /*
  422.      * Putra, 11 March 2015
  423.      * Call function for insert due_date from fi_invoice_ar_balance into fi_invoice_ar_balance_due_date
  424.      * Call function for insert due_date from fi_invoice_tax_ar_balance into fi_invoice_tax_ar_balance_due_date
  425.      * @see http://jleaf.org:8181/browse/ERPDB-211
  426.      */
  427.     PERFORM fi_insert_invoice_ar_balance_due_date(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
  428.    
  429.     IF vSalesInvoiceTempId <> -99 THEN
  430.    
  431.         SELECT invoice_ar_balance_id INTO vInvArBalanceId
  432.         FROM fi_invoice_ar_balance
  433.         WHERE invoice_ar_id = vSalesInvoiceTempId AND doc_type_id = vSlsInvTempDocTypeId;
  434.        
  435.         SELECT invoice_tax_ar_balance_id INTO vInvTaxArBalanceId
  436.         FROM fi_invoice_tax_ar_balance A
  437.         WHERE A.invoice_ar_balance_id = vInvArBalanceId;
  438.  
  439.         DELETE FROM fi_invoice_ar_balance_due_date WHERE invoice_ar_balance_id = vInvArBalanceId;
  440.        
  441.         DELETE FROM fi_invoice_tax_ar_balance_due_date WHERE invoice_tax_ar_balance_id = vInvTaxArBalanceId;
  442.        
  443.         DELETE FROM fi_invoice_ar_balance WHERE invoice_ar_balance_id = vInvArBalanceId;
  444.        
  445.         DELETE FROM fi_invoice_tax_ar_balance WHERE invoice_ar_balance_id = vInvArBalanceId;
  446.        
  447.  
  448.         UPDATE sl_do_inv SET flg_invoice = 'Y',
  449.                             update_datetime = vDatetime, update_user_id = vUserId, version = sl_do_inv.version + 1
  450.         FROM sl_invoice_item A
  451.         WHERE A.invoice_id = vSalesInvoiceId AND
  452.             A.ref_id = sl_do_inv.do_id;
  453.        
  454.     END IF;
  455.    
  456.     /*
  457.      * ========================================================================================================================
  458.      
  459.     IF vJointDppPpn = 'N' THEN
  460.         /**
  461.          * Fredi, 3 Dec 2014
  462.          * Call function for insert tax data into fi_vat_out_reporting for tax
  463.          * @see http://jleaf.org:8181/browse/ERPDB-211
  464.          */
  465.         PERFORM fi_insert_vat_out_for_reporting(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
  466.     ELSE
  467.         PERFORM fi_insert_vat_out_invoice_ar_for_reporting(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
  468.     END IF;*/
  469.    
  470.     /*
  471.      * NK, 14 Nov 2014
  472.      */
  473.     UPDATE fi_invoice_advance_ar_balance SET sales_invoice_id = vSalesInvoiceId, sales_invoice_date = vSalesInvDate,
  474.                                             update_datetime = vDatetime, update_user_id = vUserId,
  475.                                             version = fi_invoice_advance_ar_balance.version + 1
  476.     FROM sl_invoice_advance A, fi_invoice_ar_balance B
  477.     WHERE A.invoice_id = vSalesInvoiceId AND
  478.         A.ref_id = B.invoice_ar_balance_id AND
  479.         B.invoice_ar_id = fi_invoice_advance_ar_balance.invoice_ar_id;
  480.        
  481.     UPDATE fi_invoice_tax_advance_ar_balance SET sales_invoice_id = vSalesInvoiceId, sales_invoice_date = vSalesInvDate,
  482.                                             update_datetime = vDatetime, update_user_id = vUserId,
  483.                                             version = fi_invoice_tax_advance_ar_balance.version + 1
  484.     FROM sl_invoice_advance A, fi_invoice_ar_balance B
  485.     WHERE A.invoice_id = vSalesInvoiceId AND
  486.         A.ref_id = B.invoice_ar_balance_id AND
  487.         B.invoice_ar_id = fi_invoice_tax_advance_ar_balance.invoice_ar_id;
  488.        
  489.     /*
  490.      * modified by TKP 23 Maret 2016
  491.      * update nilai flg payment = y di fi_invoice_ar_balance jika amount invoice = 0
  492.      */
  493.     UPDATE fi_invoice_ar_balance A
  494.     SET flg_payment = 'Y'
  495.     WHERE A.invoice_ar_balance_id = vInvoiceArBalanceId
  496.         AND A.amount = 0;
  497.        
  498.     /*
  499.      * update flg saldo so advance invoice, karena sudah digunakan oleh sales invoice
  500.      */
  501.     UPDATE sl_so_balance_advance_invoice SET flg_invoice = 'Y', invoice_id = vSalesInvoiceId,
  502.                                             update_datetime = vDatetime, update_user_id = vUserId,
  503.                                             version = sl_so_balance_advance_invoice.version + 1
  504.     FROM sl_invoice_advance A
  505.     WHERE A.invoice_id = vSalesInvoiceId AND
  506.         A.ref_id = sl_so_balance_advance_invoice.ref_id;
  507.        
  508.     /*
  509.      * Get data flg Gunggung untuk keperluan buat efaktur
  510.      */
  511.     SELECT flg_gunggung INTO vFlgGunggung
  512.     FROM sl_invoice_tax
  513.     WHERE invoice_id = vSalesInvoiceId
  514.     AND tenant_id = pTenantId;
  515.    
  516.     IF NOT FOUND THEN
  517.           vFlgGunggung = vNo;
  518.     END IF;
  519.    
  520.     /*
  521.      * Add by Deo 7 april 2021
  522.      * if flg gunggung = 'N' then create efaktur
  523.      */
  524.     IF vFlgGunggung = 'N' THEN
  525.         IF vJointDppPpn = 'N' THEN
  526.             /**
  527.              * add by fredi, 16 Juni 2015
  528.              * - insert data for vat out
  529.              * http://jleaf.org:8112/issue/ERP-52
  530.              */
  531.             PERFORM fi_insert_vat_out_for_efaktur(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
  532.         ELSE
  533.             PERFORM fi_insert_vat_out_invoice_ar_for_efaktur(pTenantId, pSessionId, vInvoiceArBalanceId, vUserId, vDatetime);
  534.         END IF;
  535.     END IF;
  536.     /*
  537.      * Adrian, Dec 7 2016
  538.      * Menambahkan insert ke sl_do_item_balance_for_rrs untuk Request Return Sales
  539.      * modified Fitra 09 Jan 2017
  540.      *
  541.      */
  542.     INSERT INTO sl_do_item_balance_for_rrs
  543.         ( tenant_id, ou_id, partner_id, product_id,
  544.         invoice_id, invoice_type_id, invoice_no, invoice_date,
  545.         qty_request, qty_dlv,
  546.         so_uom_id, so_item_id,
  547.         gross_sell_price, curr_code, flg_tax_amount, tax_id, discount_amount,
  548.         flg_requested,
  549.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  550.     SELECT
  551.         A.tenant_id, B.ou_id, B.partner_id, C.product_id,
  552.         B.invoice_id, B.doc_type_id, B.doc_no, B.doc_date,
  553.         0, SUM(F.qty_dlv_so),
  554.         C.so_uom_id, E.so_item_id,
  555.         E.gross_sell_price, E.curr_code, E.flg_tax_amount, E.tax_id,
  556.         E.gross_sell_price - (SUM(F.item_amount -
  557.                 F.regular_disc_amount -
  558.                 F.adj_regular_disc_amount -
  559.                 F.promo_disc_amount -
  560.                 F.adj_promo_disc_amount +
  561.                 COALESCE(G.tax_amount, 0)) / SUM(F.qty_dlv_so)) AS discount_amount,
  562.         'N',
  563.         0, vDatetime, vUserId, vDatetime, vUserId
  564.     FROM sl_invoice_item A
  565.     INNER JOIN sl_invoice B ON B.invoice_id = A.invoice_id
  566.     INNER JOIN sl_do_item C ON A.ref_item_id = C.do_item_id
  567.     INNER JOIN sl_do D ON C.do_id = D.do_id
  568.     INNER JOIN sl_so_item E ON C.ref_id = E.so_item_id
  569.     INNER JOIN sl_so_balance_invoice F
  570.         ON F.tenant_id = B.tenant_id
  571.             AND F.ou_id = B.ou_id
  572.             AND F.partner_id = B.partner_id
  573.             AND F.ref_doc_type_id = A.ref_doc_type_id
  574.             AND F.ref_id = A.ref_id
  575.             AND F.ref_item_id = A.ref_item_id
  576.             AND F.do_receipt_item_id = A.do_receipt_item_id
  577.     LEFT JOIN sl_so_balance_invoice_tax G
  578.         ON F.tenant_id = G.tenant_id
  579.             AND F.ou_id = G.ou_id
  580.             AND F.partner_id = G.partner_id
  581.             AND F.ref_doc_type_id = G.ref_doc_type_id
  582.             AND F.ref_id = G.ref_id
  583.             AND F.ref_item_id = G.ref_item_id
  584.             AND F.do_receipt_item_id = G.do_receipt_item_id
  585.     WHERE A.invoice_id = vSalesInvoiceId
  586.     GROUP BY A.tenant_id, B.ou_id, B.invoice_id, B.doc_type_id, C.product_id, C.so_uom_id, E.so_item_id
  587.     HAVING SUM(F.qty_dlv_so) <> 0;
  588.  
  589.     /*
  590.      * membuat data transaksi jurnal :
  591.      * 1. buat admin
  592.      * 2. buat temlate jurnal
  593.      */    
  594.     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)
  595.     FROM sl_invoice A
  596.     WHERE A.invoice_id = vSalesInvoiceId;
  597.        
  598.     SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  599.    
  600.     INSERT INTO gl_journal_trx
  601.     (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  602.     ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  603.     ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  604.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  605.     SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.invoice_id, A.doc_no, A.doc_date,
  606.         (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,
  607.         A.ref_doc_type_id, A.ref_id, A.due_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
  608.         0, vDatetime, vUserId, vDatetime, vUserId
  609.     FROM sl_invoice A
  610.     WHERE A.invoice_id = vSalesInvoiceId;
  611.    
  612.     /*
  613.      * journal detail item do
  614.      * tanggal rate menggunakan tanggal invoice
  615.      * yang digunakan partner_id bukan partner_bill_to_id
  616.      */
  617.     INSERT INTO tt_journal_trx_item
  618.     (session_id, tenant_id, journal_trx_id, line_no,
  619.     ref_doc_type_id, ref_id,
  620.     partner_id, product_id, cashbank_id, ou_rc_id,
  621.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  622.     coa_id, curr_code, qty, uom_id,
  623.     amount, journal_date, type_rate,
  624.     numerator_rate, denominator_rate, journal_desc, remark)
  625.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  626.         A.doc_type_id, B.invoice_item_id,
  627.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  628.         vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  629.         f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), A.curr_code, 0, vEmptyId,
  630.         B.ref_item_amount, A.doc_date, vTypeRate,
  631.         1, 1, 'SALES_INCOME', B.remark
  632.     FROM sl_invoice A, sl_invoice_item B
  633.     WHERE A.invoice_id = vSalesInvoiceId AND
  634.         A.invoice_id = B.invoice_id;
  635.        
  636.     /*
  637.      * journal detail additional cost sales invoice
  638.      * tanggal rate menggunakan tanggal invoice
  639.      */
  640.     INSERT INTO tt_journal_trx_item
  641.     (session_id, tenant_id, journal_trx_id, line_no,
  642.     ref_doc_type_id, ref_id,
  643.     partner_id, product_id, cashbank_id, ou_rc_id,
  644.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  645.     coa_id, curr_code, qty, uom_id,
  646.     amount, journal_date, type_rate,
  647.     numerator_rate, denominator_rate, journal_desc, remark)
  648.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  649.         A.doc_type_id, B.invoice_cost_id,
  650.         A.partner_id, vEmptyId, vEmptyId, B.ou_rc_id,
  651. -- NK, 30 Agustus 2014 - add segment_id        
  652.         B.segment_id, vSignCredit, vActivityCOA, B.activity_gl_id,
  653.         C.coa_id, B.curr_code, 0, vEmptyId,
  654.         B.add_amount, A.doc_date, vTypeRate,
  655.         1, 1, 'SALES_COST', B.remark
  656.     FROM sl_invoice A, sl_invoice_cost B, m_activity_gl C
  657.     WHERE A.invoice_id = vSalesInvoiceId AND
  658.         A.invoice_id = B.invoice_id AND
  659.         B.activity_gl_id = C.activity_gl_id AND
  660.         B.add_amount <> 0;
  661.        
  662.     /*
  663.      * journal detail tax sales invoice
  664.      * tanggal rate menggunakan tanggal invoice
  665.      */
  666.     INSERT INTO tt_journal_trx_item
  667.     (session_id, tenant_id, journal_trx_id, line_no,
  668.     ref_doc_type_id, ref_id,
  669.     partner_id, product_id, cashbank_id, ou_rc_id,
  670.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  671.     coa_id, curr_code, qty, uom_id,
  672.     amount, journal_date, type_rate,
  673.     numerator_rate, denominator_rate, journal_desc, remark)
  674.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  675.         A.doc_type_id, B.invoice_tax_id,
  676.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  677.         vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
  678.         C.create_coa_id, C.tax_curr_code, 0, vEmptyId,
  679.         B.gov_tax_amount, A.doc_date, vTypeRate,
  680.         1, 1, 'VAT_OUT', B.remark
  681.     FROM sl_invoice A, sl_invoice_tax B, m_tax C
  682.     WHERE A.invoice_id = vSalesInvoiceId AND
  683.         A.invoice_id = B.invoice_id AND
  684.         B.tax_id = C.tax_id;
  685.    
  686.     /*
  687.      * journal detail alokasi advance sales invoice
  688.      * tanggal rate menggunakan tanggal advance invoice
  689.      */    
  690.     INSERT INTO tt_journal_trx_item
  691.     (session_id, tenant_id, journal_trx_id, line_no,
  692.     ref_doc_type_id, ref_id,
  693.     partner_id, product_id, cashbank_id, ou_rc_id,
  694.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  695.     coa_id, curr_code, qty, uom_id,
  696.     amount, journal_date, type_rate,
  697.     numerator_rate, denominator_rate, journal_desc, remark)
  698.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  699.         B.ref_doc_type_id, B.ref_id,
  700.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  701.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  702.         f_get_system_coa_by_group_coa(A.tenant_id, 'UangMukaPenjualan'), B.advance_curr_code, 0, vEmptyId,
  703.         B.advance_amount, E.doc_date, vTypeRate,
  704.         1, 1, 'AR_ADVANCE', B.remark
  705.     FROM sl_invoice A, sl_invoice_advance B, fi_invoice_ar_balance E
  706.     WHERE A.invoice_id = vSalesInvoiceId AND
  707.         A.invoice_id = B.invoice_id AND
  708.         B.ref_doc_type_id = E.doc_type_id AND
  709.         B.ref_id = E.invoice_ar_balance_id;
  710.        
  711.     /*
  712.      * buat juournal offside untuk yg debit ar
  713.      * dengan grouping data journal yang lain melakukan credit account lain
  714.      */
  715.     INSERT INTO tt_journal_trx_item
  716.     (session_id, tenant_id, journal_trx_id, line_no,
  717.     ref_doc_type_id, ref_id,
  718.     partner_id, product_id, cashbank_id, ou_rc_id,
  719.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  720.     coa_id, curr_code, qty, uom_id,
  721.     amount, journal_date, type_rate,
  722.     numerator_rate, denominator_rate, journal_desc, remark)
  723.     SELECT A.session_id, B.tenant_id, B.journal_trx_id, 1,
  724.         vEmptyId, vEmptyId,
  725.         B.partner_id, vEmptyId, vEmptyId, vEmptyId,
  726.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  727.         f_get_ar_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vEmptyId,
  728.         SUM(A.amount), B.doc_date, vTypeRate,
  729.         1, 1, 'AR', B.remark
  730.     FROM tt_journal_trx_item A, gl_journal_trx B
  731.     WHERE A.session_id = pSessionId AND
  732.         A.journal_trx_id = B.journal_trx_id AND
  733.         B.journal_trx_id = vJournalTrxId AND
  734.         A.sign_journal = vSignCredit AND
  735.         journal_desc IN ('SALES_INCOME','SALES_COST','VAT_OUT')
  736.     GROUP BY A.session_id, B.tenant_id, B.journal_trx_id,
  737.         B.partner_id, A.curr_code, B.remark;
  738.  
  739.     /*
  740.      * buat juournal offside untuk yg credit ar
  741.      * dengan grouping data journal yang lain melakukan debit account lain
  742.      * contoh : AR_ADVANCE
  743.      */
  744.     INSERT INTO tt_journal_trx_item
  745.     (session_id, tenant_id, journal_trx_id, line_no,
  746.     ref_doc_type_id, ref_id,
  747.     partner_id, product_id, cashbank_id, ou_rc_id,
  748.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  749.     coa_id, curr_code, qty, uom_id,
  750.     amount, journal_date, type_rate,
  751.     numerator_rate, denominator_rate, journal_desc, remark)
  752.     SELECT A.session_id, B.tenant_id, B.journal_trx_id, 1,
  753.         B.doc_type_id, B.doc_id,
  754.         B.partner_id, vEmptyId, vEmptyId, vEmptyId,
  755.         vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  756.         f_get_ar_coa_partner(B.tenant_id, B.partner_id), A.curr_code, 0, vEmptyId,
  757.         SUM(A.amount), B.doc_date, vTypeRate,
  758.         1, 1, 'AR', B.remark
  759.     FROM tt_journal_trx_item A, gl_journal_trx B
  760.     WHERE A.session_id = pSessionId AND
  761.         A.journal_trx_id = B.journal_trx_id AND
  762.         B.journal_trx_id = vJournalTrxId AND
  763.         A.sign_journal = vSignDebit AND
  764.         A.journal_desc IN ('AR_ADVANCE')
  765.     GROUP BY A.session_id, B.tenant_id, B.journal_trx_id, B.doc_type_id, B.doc_id,
  766.         B.partner_id, A.curr_code, B.remark;
  767.        
  768.     /*
  769.     INSERT INTO gl_journal_trx_item
  770.     (tenant_id, journal_trx_id, line_no,
  771.     ref_doc_type_id, ref_id,
  772.     partner_id, product_id, cashbank_id, ou_rc_id,
  773.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  774.     coa_id, curr_code, qty, uom_id,
  775.     amount, journal_date, type_rate,
  776.     numerator_rate, denominator_rate, journal_desc, remark,
  777.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  778.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  779.         A.ref_doc_type_id, A.ref_id,
  780.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  781.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  782.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  783.         A.amount, A.journal_date, A.type_rate,
  784.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  785.         0, vDatetime, vUserId, vDatetime, vUserId
  786.     FROM tt_journal_trx_item A
  787.     WHERE A.session_id = pSessionId AND
  788.         journal_desc IN ('SALES_INCOME','SALES_COST','VAT_OUT','AR_ADVANCE');
  789.  
  790.     INSERT INTO gl_journal_trx_mapping
  791.     (tenant_id, journal_trx_id, line_no,
  792.     ref_doc_type_id, ref_id,
  793.     partner_id, product_id, cashbank_id, ou_rc_id,
  794.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  795.     coa_id, curr_code, qty, uom_id,
  796.     amount, journal_date, type_rate,
  797.     numerator_rate, denominator_rate, journal_desc, remark,
  798.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  799.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  800.         A.ref_doc_type_id, A.ref_id,
  801.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  802.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  803.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  804.         A.amount, A.journal_date, A.type_rate,
  805.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  806.         0, vDatetime, vUserId, vDatetime, vUserId
  807.     FROM tt_journal_trx_item A
  808.     WHERE A.session_id = pSessionId AND
  809.         journal_desc IN ('AR');
  810.     */
  811.        
  812.     INSERT INTO gl_journal_trx_item
  813.                 (tenant_id, journal_trx_id, line_no,
  814.                 ref_doc_type_id, ref_id,
  815.                 partner_id, product_id, cashbank_id, ou_rc_id,
  816.                 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  817.                 coa_id, curr_code, qty, uom_id,
  818.                 amount, journal_date, type_rate,
  819.                 numerator_rate, denominator_rate, journal_desc, remark,
  820.                 "version", create_datetime, create_user_id, update_datetime, update_user_id)
  821.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  822.             A.ref_doc_type_id, A.ref_id,
  823.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  824.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  825.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  826.             A.amount, A.journal_date, A.type_rate,
  827.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  828.             0, vDatetime, vUserId, vDatetime, vUserId
  829.     FROM(
  830.         SELECT A.tenant_id, A.journal_trx_id,
  831.             A.ref_doc_type_id, A.ref_id,
  832.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  833.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  834.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  835.             A.amount, A.journal_date, A.type_rate,
  836.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  837.             vOuStructure.ou_branch_id AS ou_branch_id, vOuStructure.ou_sub_bu_id AS ou_sub_bu_id
  838.         FROM tt_journal_trx_item A
  839.         WHERE A.session_id = pSessionId AND
  840.             journal_desc IN ('SALES_INCOME','SALES_COST','VAT_OUT','AR_ADVANCE')
  841.         UNION ALL
  842.         -- tambahan jounal cogs
  843.         SELECT A.tenant_id, vJournalTrxId,
  844.             vEmptyId, vEmptyId,
  845.             A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  846.             vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  847.             f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
  848.             0, A.doc_date, vTypeRate,
  849.             1, 1, 'COGS', vEmptyString,
  850.             vOuStructureDo.ou_branch_id AS ou_branch_id, vOuStructureDo.ou_sub_bu_id AS ou_sub_bu_id
  851.         FROM sl_invoice A
  852.         WHERE A.invoice_id = vSalesInvoiceId
  853.     ) A;
  854.  
  855.     INSERT INTO gl_journal_trx_mapping
  856.                 (tenant_id, journal_trx_id, line_no,
  857.                 ref_doc_type_id, ref_id,
  858.                 partner_id, product_id, cashbank_id, ou_rc_id,
  859.                 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  860.                 coa_id, curr_code, qty, uom_id,
  861.                 amount, journal_date, type_rate,
  862.                 numerator_rate, denominator_rate, journal_desc, remark,
  863.                 "version", create_datetime, create_user_id, update_datetime, update_user_id)
  864.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  865.             A.ref_doc_type_id, A.ref_id,
  866.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  867.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  868.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  869.             A.amount, A.journal_date, A.type_rate,
  870.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  871.             0, vDatetime, vUserId, vDatetime, vUserId
  872.     FROM (
  873.         SELECT A.tenant_id, A.journal_trx_id,  
  874.             A.ref_doc_type_id, A.ref_id,
  875.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  876.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  877.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  878.             A.amount, A.journal_date, A.type_rate,
  879.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  880.             vOuStructure.ou_branch_id AS ou_branch_id, vOuStructure.ou_sub_bu_id AS ou_sub_bu_id
  881.         FROM tt_journal_trx_item A
  882.         WHERE A.session_id = pSessionId AND
  883.             journal_desc IN ('AR')
  884.         UNION ALL
  885.         SELECT h.tenant_id, vJournalTrxId,  
  886.             g.ref_doc_type_id, g.ref_item_id,
  887.             h.partner_id, C.product_id, vEmptyId, vEmptyId,
  888.             vEmptyId, vSignCredit, vProductCOA, vEmptyId,
  889.             vCoaIdGIT, g.curr_code, SUM(g.qty_dlv_so), g.so_uom_id,
  890.             0, h.doc_date, vTypeRate,
  891.             1, 1, 'PRODUCT_STOCK_IN_TRANSIT', C.remark,
  892.             vOuStructureDo.ou_branch_id AS ou_branch_id, vOuStructureDo.ou_sub_bu_id AS ou_sub_bu_id
  893.         FROM sl_invoice_item a
  894.         INNER JOIN sl_invoice h ON a.tenant_id = h.tenant_id
  895.             AND a.invoice_id = h.invoice_id
  896.         INNER JOIN sl_do b ON a.tenant_id = b.tenant_id
  897.             AND a.ref_id = b.do_id
  898.         INNER JOIN sl_do_item c ON a.tenant_id = c.tenant_id AND a.ref_item_id = c.do_item_id
  899.         INNER JOIN sl_so_balance_invoice g ON a.tenant_id = g.tenant_id
  900.             AND g.ou_id = h.ou_id
  901.             AND g.so_id = b.ref_id
  902.             AND g.ref_item_id = a.ref_item_id
  903.             AND g.ref_doc_type_id = 311
  904.             AND g.ref_id = b.do_id
  905.             AND a.do_receipt_item_id = g.do_receipt_item_id
  906.         WHERE A.invoice_id = vSalesInvoiceId
  907.         GROUP BY h.tenant_id, g.ref_doc_type_id, g.ref_item_id,
  908.                 h.partner_id, C.product_id, g.curr_code,
  909.                 g.so_uom_id, h.doc_date,C.remark
  910.     ) A;       
  911.        
  912.    
  913.     UPDATE sl_invoice_temp_intransit A
  914.     SET invoice_journal_trx_id = vJournalTrxId,
  915.         status_process = vReleased,
  916.         update_datetime = vDatetime,
  917.         update_user_id = vUserId,
  918.         version = A.version+1
  919.     FROM sl_invoice B
  920.     WHERE A.invoice_id = B.invoice_id
  921.     AND B.invoice_id = vSalesInvoiceId;
  922.    
  923.    
  924.     /*
  925.      * Insert ke table log fi_vat_out_balance_efaktur_gunggung_seq
  926.      */
  927.     INSERT INTO fi_vat_out_balance_efaktur_gunggung(
  928.             tenant_id, ou_id, invoice_id,
  929.             invoice_doc_type_id, invoice_tax_id, tax_code, tax_percentage,
  930.             doc_no, doc_date, invoice_dpp_amount,
  931.             invoice_tax_amount, invoice_gov_tax_amount,
  932.             flg_gunggung, flg_realization, realization_doc_type_id,
  933.             realization_id, realization_tax_no, realization_tax_date,
  934.             version, create_datetime,
  935.             create_user_id, update_datetime, update_user_id)
  936.            
  937.     SELECT pTenantId, A.ou_id, vSalesInvoiceId,
  938.             A.doc_type_id, B.invoice_tax_id, C.tax_code, B.tax_percentage,
  939.             A.doc_no, A.doc_date, B.base_amount,
  940.             B.tax_amount, B.gov_tax_amount,
  941.             B.flg_gunggung, vNo, vEmptyId,
  942.             vEmptyId, vEmptyString, vEmptyString,
  943.             0, vDatetime,
  944.             vUserId, vDatetime, vUserId
  945.            
  946.     FROM sl_invoice A, sl_invoice_tax B, m_tax C
  947.     WHERE A.invoice_id = vSalesInvoiceId AND
  948.         A.invoice_id = B.invoice_id AND
  949.         B.tax_id = C.tax_id;
  950.        
  951.     --GROUP BY A.doc_type_id, B.invoice_tax_id, C.tax_code, B.tax_percentage,
  952.     --      A.doc_no, A.doc_date,
  953.        
  954.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;     
  955.        
  956. END;   
  957. $BODY$
  958.   LANGUAGE plpgsql VOLATILE
  959.   COST 100;
  960.   /
RAW Paste Data