samuel025

Function Submit DO

Jun 18th, 2021
787
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Function: sl_submit_do(bigint, character varying, character varying)
  2.  
  3. -- DROP FUNCTION sl_submit_do(bigint, character varying, character varying);
  4.  
  5. CREATE OR REPLACE FUNCTION sl_submit_do(
  6.     bigint,
  7.     character varying,
  8.     character varying)
  9.   RETURNS void AS
  10. $BODY$
  11. DECLARE
  12.     pTenantId           ALIAS FOR $1;
  13.     pSessionId          ALIAS FOR $2;
  14.     pProcessNo          ALIAS FOR $3;
  15.  
  16.     vProcessId              bigint;
  17.     vProcessIdForSI         bigint;
  18.     vDoId                   bigint;
  19.     vUserId                 bigint;
  20.     vDatetime               character varying(14);
  21.     vFlagInvoice            character varying(1);
  22.     vEmptyId                bigint;
  23.     vStatusRelease          character varying(1);
  24.     vStatusDraft            character varying(1);  
  25.     vStatusFinal            character varying(1);
  26.     vEmptyValue             character varying(1);
  27.     vProductStatus          character varying(5);
  28.     vSignDebit              character varying(1);
  29.     vSignCredit             character varying(1);
  30.     vTypeRate               character varying(3);
  31.     vProductCOA             character varying(10);
  32.     vSystemCOA              character varying(10);
  33.     vSoId                   bigint;
  34.     vUnfinishedItem         bigint;
  35.     vNol                    bigint;
  36.     vParentOuId             bigint;
  37.     vOuId                   bigint;
  38.     vOuWarehouseId          bigint;
  39.     vJournalTrxId           bigint;    
  40.     vCoaIdGIT               bigint;
  41.     vRgId                   bigint;
  42.     vPartnerShipId          bigint;
  43.     vPartnerBillId          bigint;
  44.     vJournalType            character varying(20)
  45.    
  46.     vRoleId                 bigint;
  47.     vFlgUserRole            character varying;
  48.    
  49.     vAutonumIdSI            bigint;
  50.     vDocNoNewSI             character varying;
  51.     vProcessNoRG            character varying;
  52.    
  53.     vFlagDropship           character varying(1);
  54.     vFlgPkp                 character varying(1);
  55.    
  56.     vYes                    character varying(1);
  57.     vNo                     character varying(1);
  58.    
  59.     vDocJournal                 DOC_JOURNAL%ROWTYPE;
  60.     vOuStructure                OU_BU_STRUCTURE%ROWTYPE;
  61.     vOuStructureJournalItem     OU_BU_STRUCTURE%ROWTYPE;
  62.     result                      RECORD;
  63.    
  64.     vSchemeDO               character varying(20);
  65.     vSchemeRG               character varying(20);
  66.    
  67.     vDeliveryOrderDocTypeId bigint;
  68.     vRoundingModeNonTax     character varying(5);
  69.    
  70. BEGIN
  71.    
  72.     vFlagInvoice := 'N';
  73.     vEmptyId := -99;
  74.     vStatusRelease := 'R';
  75.     vStatusDraft := 'D';   
  76.     vStatusFinal := 'F';
  77.     vEmptyValue := ' ';
  78.     vProductStatus := 'GOOD';
  79.     vSignDebit := 'D';
  80.     vSignCredit := 'C';
  81.     vTypeRate := 'COM';
  82.     vProductCOA := 'PRODUCT';
  83.     vSystemCOA := 'SYSTEM';
  84.     vUnfinishedItem := 0;
  85.     vNol := 0;
  86.     vCoaIdGIT:=f_get_system_coa_by_group_coa(pTenantId, 'PersediaanInTransit');
  87.     vYes  := 'Y';
  88.     vNo   := 'N';
  89.    
  90.     vRoleId                 := -99;
  91.     vFlgUserRole            := '';
  92.    
  93.     vSchemeDO := 'FB01';
  94.     vSchemeRG := 'CB01';
  95.    
  96.     vDeliveryOrderDocTypeId = 311;
  97.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  98.    
  99.    
  100.     SELECT A.process_message_id INTO vProcessId
  101.     FROM t_process_message A
  102.     WHERE A.tenant_id = pTenantId AND
  103.         A.process_name = 'sl_submit_do' AND
  104.         A.process_no = pProcessNo;
  105.        
  106.     SELECT CAST(A.process_parameter_value AS bigint) INTO vDoId
  107.     FROM t_process_parameter A
  108.     WHERE A.process_message_id = vProcessId AND
  109.         A.process_parameter_key = 'doId';
  110.    
  111.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  112.     FROM t_process_parameter A
  113.     WHERE A.process_message_id = vProcessId AND
  114.         A.process_parameter_key = 'userId';
  115.  
  116.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  117.     FROM t_process_parameter A
  118.     WHERE A.process_message_id = vProcessId AND
  119.         A.process_parameter_key = 'datetime';
  120.  
  121.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; 
  122. /*
  123.  * 1. update status doc sl_do
  124.  * 2. add sl_log_so_balance_item
  125.  * 3. add sl_so_balance_invoice
  126.  * 4. add sl_so_balance_invoice_tax
  127.  * 5. add in_log_product_balance_stock
  128.  * 6. add in_balance_do_item
  129.  * 7. update status sl_so_balance_item
  130.  * 8. update status sl_so. Jika seluruh balance item sudah final/cancel, maka status menjadi Final.
  131.  * 9. add gl_journal_trx
  132.  * 10. add gl_journal_trx_item
  133.  * 11. add gl_journal_trx_mapping
  134.  *
  135.  */
  136.  
  137.     SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
  138.     FROM sl_do A
  139.     WHERE A.do_id = vDoId INTO result;
  140.        
  141.     vSoId := result.ref_id;
  142.     vOuStructure := result.ou;
  143.     vDocJournal := result.doc;
  144.    
  145.     UPDATE sl_do SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
  146.     WHERE do_id = vDoId;
  147.    
  148.     INSERT INTO sl_log_so_balance_item
  149.     (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
  150.      qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  151.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  152.     SELECT A.tenant_id, C.so_id, C.so_item_id, A.doc_type_id, A.do_id, B.do_item_id,
  153.         B.qty_dlv_so * -1, B.so_uom_id, B.qty_dlv_int  *-1, B.base_uom_id, B.remark,
  154.         0, vDatetime, vUserId, vDatetime, vUserId
  155.     FROM sl_do A, sl_do_item B, sl_so_item C
  156.     WHERE A.do_id = vDoId AND
  157.           A.do_id = B.do_id AND
  158.           B.ref_id = C.so_item_id;
  159.  
  160.     INSERT INTO sl_balance_do_custom_for_dkb(
  161.             do_id, tenant_id, doc_no, doc_date, ou_id, warehouse_id, partner_id,
  162.             partner_ship_to_id, so_id, so_no, so_date, flg_dkb, dkb_id, flg_packing_list,
  163.             packing_list_id, create_datetime, create_user_id, update_datetime,
  164.             update_user_id, version)
  165.     SELECT A.do_id,A.tenant_id,A.doc_no,A.doc_date,A.ou_id,A.warehouse_id,B.partner_id,
  166.         A.partner_ship_to_id,B.so_id AS so_id,B.doc_no AS so_no,B.doc_date AS so_date,'N',-99,'N',
  167.         -99,A.create_datetime, A.create_user_id, A.update_datetime,
  168.         A.update_user_id, A.version
  169.     FROM sl_do A
  170.     INNER JOIN sl_so B ON A.ref_id = B.so_id
  171.     WHERE  A.do_id = vDoId;
  172.          
  173.     INSERT INTO sl_so_balance_invoice
  174.     (tenant_id, ou_id, partner_id, so_id,
  175.     ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
  176.     curr_code, price_so, item_amount, flg_invoice, invoice_id,
  177.     regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
  178.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  179.     gross_sell_price_so, flg_tax_amount, tax_id, tax_percentage, discount_percentage, discount_amount)
  180.    
  181.     SELECT A.tenant_id, A.ou_id, D.partner_bill_to_id, A.ref_id,
  182.         A.doc_type_id, A.do_id, A.doc_no, A.doc_date, B.do_item_id, B.qty_dlv_so, B.so_uom_id,
  183.         C.curr_code, C.nett_sell_price,
  184.         f_get_amount_before_tax_and_disc(B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax),
  185.         vFlagInvoice, vEmptyId,
  186.         C.discount_amount * B.qty_dlv_so, 0, 0, 0,
  187.         0, vDatetime, vUserId, vDatetime, vUserId,
  188.        
  189.         C.gross_sell_price, C.flg_tax_amount, C.tax_id, C.tax_percentage, C.discount_percentage, C.discount_amount
  190.     FROM sl_do A, sl_do_item B, sl_so_item C, sl_so D
  191.     WHERE A.do_id = vDoId AND
  192.           A.do_id = B.do_id AND
  193.           B.ref_id = C.so_item_id AND
  194.           C.so_id = D.so_id;
  195.        
  196.     INSERT INTO sl_so_balance_invoice_tax
  197.     (tenant_id, ou_id, partner_id, so_id,
  198.     ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  199.     tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  200.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  201.     SELECT A.tenant_id, A.ou_id, E.partner_bill_to_id, A.ref_id,
  202.         A.doc_type_id, A.do_id, B.do_item_id, C.tax_id, D.flg_amount,
  203.         C.tax_percentage, C.curr_code,
  204.         f_get_amount_before_tax_and_disc(B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount, C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax),
  205.         f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_dlv_so * (C.gross_sell_price - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code), vRoundingModeNonTax), C.tax_percentage), vFlagInvoice, vEmptyId,
  206.         0, vDatetime, vUserId, vDatetime, vUserId
  207.     FROM sl_do A, sl_do_item B, sl_so_item C, m_tax D, sl_so E
  208.     WHERE A.do_id = vDoId AND
  209.           A.do_id = B.do_id AND
  210.           B.ref_id = C.so_item_id AND
  211.           C.tax_id = D.tax_id AND
  212.           C.so_id = E.so_id;
  213.          
  214.     /*
  215.      * buat data log product balance stock
  216.      * ref item id = do_product_id
  217.      */      
  218.     INSERT INTO in_log_product_balance_stock
  219.     (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  220.      product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  221.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  222.     SELECT A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
  223.         C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id, SUM(C.qty_dlv_int) * -1,
  224.         0, vDatetime, vUserId, vDatetime, vUserId      
  225.     FROM sl_do A, sl_do_item B, sl_do_product C, m_warehouse_ou D
  226.     WHERE A.do_id = vDoId AND
  227.         A.do_id = B.do_id AND
  228.         B.do_item_id = C.do_item_id AND
  229.         A.warehouse_id = D.warehouse_id
  230.     GROUP BY A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
  231.             C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id;
  232.  
  233.     /*
  234.      * add data balance do item yang akan digunakan di inventory untuk pembuatan return note,
  235.      * saat akan membuat return note
  236.      */
  237.     INSERT INTO in_balance_do_item
  238.     (do_item_id, tenant_id, ou_id, do_id, doc_no, doc_date, partner_id,
  239.       so_id, so_no, so_date, so_item_id,
  240.       qty_dlv, qty_return, so_uom_id, qty_dlv_int, 
  241.       qty_return_int, base_uom_id, status_item,
  242.       "version", create_datetime, create_user_id, update_datetime, update_user_id)
  243.     SELECT B.do_item_id, A.tenant_id, A.ou_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
  244.             A.ref_id, C.doc_no, C.doc_date, B.ref_id,
  245.             SUM(B.qty_dlv_so), 0, B.so_uom_id, SUM(B.qty_dlv_int),
  246.             0, B.base_uom_id, vStatusRelease,
  247.         0, vDatetime, vUserId, vDatetime, vUserId      
  248.     FROM sl_do A, sl_do_item B, sl_so C
  249.     WHERE A.do_id = vDoId AND
  250.         A.do_id = B.do_id AND
  251.         A.ref_id = C.so_id
  252.     GROUP BY B.do_item_id, A.tenant_id, A.ou_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
  253.         A.ref_id, C.doc_no, C.doc_date, B.ref_id, B.so_uom_id, B.base_uom_id;
  254.    
  255.     UPDATE sl_so_balance_item SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
  256.     FROM sl_do_item A
  257.     WHERE sl_so_balance_item.so_item_id = A.ref_id AND
  258.         sl_so_balance_item.tenant_id = A.tenant_id AND
  259.         A.do_id = vDoId AND
  260.         sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv > 0;
  261.  
  262.     UPDATE sl_so_balance_item SET status_item = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
  263.     FROM sl_do_item A
  264.     WHERE sl_so_balance_item.so_item_id = A.ref_id AND
  265.         sl_so_balance_item.tenant_id = A.tenant_id AND
  266.         A.do_id = vDoId AND
  267.         sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv <= 0;
  268.    
  269.            
  270.     SELECT COUNT(1) INTO vUnfinishedItem
  271.     FROM sl_so_balance_item A, sl_so_item B
  272.     WHERE A.so_item_id = B.so_item_id AND
  273.         B.so_id = vSoId AND
  274.         A.status_item = vStatusRelease;
  275.        
  276.     IF vUnfinishedItem = 0 THEN
  277.         UPDATE sl_so SET status_doc = vStatusFinal
  278.         WHERE so_id = vSoId;
  279.     END IF;
  280.    
  281.     /*
  282.      * @author TKP, 9 Jun 2016
  283.      * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
  284.      * jika OU pada warehouse tidak sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id didapat pada f_get_ou_bu_structure;
  285.      */
  286.    
  287.     SELECT A.ou_id, B.ou_id INTO vOuId, vOuWarehouseId
  288.     FROM sl_do A
  289.     INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
  290.     WHERE A.do_id = vDoId;
  291.    
  292.     IF (vOuId <> vOuWarehouseId) THEN
  293.         SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
  294.         vOuStructureJournalItem := result.ou_structure;
  295.     ELSE
  296.         vOuStructureJournalItem := ROW(-99, -99, -99);
  297.     END IF;
  298.    
  299.    
  300.     /*
  301.      * Create otomatis dokumen Sales Invoice
  302.      * Jika SO flag dropship Yes, SO dibuatkan Goods Receive, dan customer nya flg PKP No
  303.      * vSoId
  304.      */
  305.    
  306.  
  307.  
  308.     SELECT flag_dropship INTO vFlagDropship
  309.     FROM sl_so_ext
  310.     WHERE so_id = vSoId
  311.     AND tenant_id = pTenantId;
  312.    
  313.    
  314.     SELECT A.receive_goods_id INTO vRgId
  315.     FROM pu_receive_goods A
  316.     INNER JOIN pu_po B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.po_id
  317.     INNER JOIN sl_so C ON B.ref_doc_type_id = C.doc_type_id AND B.ref_id = C.so_id
  318.     WHERE C.so_id = vSoId;
  319.    
  320.     IF NOT FOUND THEN
  321.           vRgId = vEmptyId;
  322.     END IF;
  323.    
  324.    
  325.     SELECT partner_ship_to_id, partner_bill_to_id INTO vPartnerShipId, vPartnerBillId
  326.     FROM sl_so
  327.     WHERE so_id = vSoId;
  328.    
  329.     /*
  330.     --Generate otomatis dokumen Sales Invoice
  331.     SELECT flg_pkp INTO vFlgPkp
  332.     FROM m_partner_npwp
  333.     WHERE partner_id = vPartnerBillId
  334.     AND tenant_id = pTenantId;
  335.    
  336.     IF NOT FOUND THEN
  337.           vFlgPkp = vNo;
  338.     END IF;
  339.    
  340.     --Create Sales Invoice dengan kondisi flag dropship Yes, sudah dibuat Receive Goods, dan flg pkp partner bill to nya No
  341.     IF (vFlagDropship = vYes AND vRgId <> vEmptyId AND vFlgPkp = vNo) THEN
  342.    
  343.             --Get receive goods id and doc no
  344.             SELECT CAST(D.receive_goods_id AS character varying)|| '_' ||D.doc_no INTO vProcessNoRG
  345.             FROM sl_do A
  346.             INNER JOIN sl_so B ON A.tenant_id = B.tenant_id AND A.ou_id = B.ou_id AND A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.so_id
  347.             INNER JOIN pu_po C ON C.tenant_id = B.tenant_id AND C.ou_id = B.ou_id AND C.ref_doc_type_id = B.doc_type_id AND C.ref_id = B.so_id
  348.             INNER JOIN pu_receive_goods D ON D.tenant_id = C.tenant_id AND D.ou_id = C.ou_id AND D.ref_doc_type_id = C.doc_type_id AND D.ref_id = C.po_id
  349.             WHERE A.do_id = vDoId
  350.             AND A.tenant_id = pTenantId;
  351.            
  352.            
  353.            
  354.             --Get Rreceive Goods process No
  355.             SELECT A.process_message_id INTO vProcessIdForSI
  356.             FROM t_process_message A
  357.             WHERE A.tenant_id = pTenantId AND
  358.                 A.process_name = 'pu_submit_receive_goods' AND
  359.                 A.process_no = vProcessNoRG;
  360.        
  361.             SELECT CAST(A.process_parameter_value AS bigint) INTO vAutonumIdSI
  362.             FROM t_process_parameter A
  363.             WHERE A.process_message_id = vProcessIdForSI
  364.             AND A.process_parameter_key = 'autonumIdSI';
  365.        
  366.             -- Ambil doc no SI baru
  367.             SELECT A.process_parameter_value INTO vDocNoNewSI
  368.             FROM t_process_parameter A
  369.             WHERE A.process_message_id = vProcessIdForSI
  370.             AND A.process_parameter_key = 'autonumSI';
  371.            
  372.            
  373.             -- Get role id, flg user role awe submit RG
  374.             SELECT role_id, flg_user_role INTO vRoleId, vFlgUserRole
  375.             FROM awe_historydoc
  376.             WHERE scheme = vSchemeRG
  377.             AND doc_id = vRgId
  378.             AND activity = 'SUBMIT';
  379.            
  380.            
  381.         PERFORM sl_automatic_create_sales_invoice(pSessionId, pTenantId, vUserId, vDatetime, vSoId, vDoId, vAutonumIdSI, vDocNoNewSI, vRoleId, vFlgUserRole);
  382.    
  383.     END IF;
  384.     */
  385.    
  386.    
  387.    
  388.    
  389.     /*
  390.      * membuat data transaksi jurnal :
  391.      * 1. buat admin
  392.      * 2. buat temlate jurnal
  393.      */
  394.  
  395.     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), 'MONTHLY', vDatetime, vUserId)
  396.     FROM sl_do A
  397.     WHERE A.do_id = vDoId;
  398.    
  399.     SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  400.    
  401.     INSERT INTO gl_journal_trx
  402.     (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  403.     ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  404.     ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  405.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  406.     SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.do_id, A.doc_no, A.doc_date,
  407.         (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_ship_to_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
  408.         A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
  409.         0, vDatetime, vUserId, vDatetime, vUserId
  410.     FROM sl_do A, sl_so B
  411.     WHERE A.do_id = vDoId AND
  412.         A.ref_doc_type_id = B.doc_type_id AND
  413.         A.ref_id = B.so_id;
  414.    
  415.     INSERT INTO tt_journal_trx_item
  416.     (session_id, tenant_id, journal_trx_id, line_no,
  417.     ref_doc_type_id, ref_id,
  418.     partner_id, product_id, cashbank_id, ou_rc_id,
  419.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  420.     coa_id, curr_code, qty, uom_id,
  421.     amount, journal_date, type_rate,
  422.     numerator_rate, denominator_rate, journal_desc, remark)
  423.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  424.         A.doc_type_id, B.do_item_id,
  425.         A.partner_ship_to_id, B.product_id, vEmptyId, vEmptyId,
  426.         vEmptyId, vSignCredit, vProductCOA, vEmptyId,
  427.         f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_dlv_int, B.base_uom_id,
  428.         0, A.doc_date, vTypeRate,
  429.         1, 1, 'PRODUCT_STOCK', B.remark
  430.     FROM sl_do A, sl_do_item B, sl_so_item C
  431.     WHERE A.do_id = vDoId AND
  432.         A.do_id = B.do_id AND
  433.         B.ref_id = C.so_item_id;
  434.        
  435.     INSERT INTO gl_journal_trx_item
  436.     (tenant_id, journal_trx_id, line_no,
  437.     ref_doc_type_id, ref_id,
  438.     partner_id, product_id, cashbank_id, ou_rc_id,
  439.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  440.     coa_id, curr_code, qty, uom_id,
  441.     amount, journal_date, type_rate,
  442.     numerator_rate, denominator_rate, journal_desc, remark,
  443.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  444.     ou_branch_id, ou_sub_bu_id)
  445.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  446.         A.ref_doc_type_id, A.ref_id,
  447.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  448.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  449.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  450.         A.amount, A.journal_date, A.type_rate,
  451.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  452.         0, vDatetime, vUserId, vDatetime, vUserId,
  453.         (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
  454.     FROM tt_journal_trx_item A
  455.     WHERE A.session_id = pSessionId;
  456.    
  457.     /* untuk perubahan lama
  458.     INSERT INTO gl_journal_trx_mapping
  459.     (tenant_id, journal_trx_id, line_no,
  460.     ref_doc_type_id, ref_id,
  461.     partner_id, product_id, cashbank_id, ou_rc_id,
  462.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  463.     coa_id, curr_code, qty, uom_id,
  464.     amount, journal_date, type_rate,
  465.     numerator_rate, denominator_rate, journal_desc, remark,
  466.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  467.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  468.         vEmptyId, vEmptyId,
  469.         vEmptyId, vEmptyId, vEmptyId, vEmptyId,
  470.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  471.         f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
  472.         0, A.journal_date, A.type_rate,
  473.         1, 1, 'COGS', vEmptyValue,
  474.         0, vDatetime, vUserId, vDatetime, vUserId
  475.     FROM tt_journal_trx_item A
  476.     WHERE A.session_id = pSessionId
  477.     GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
  478.     */
  479.    
  480.     --Deo 26 Februari 2021
  481.     --Untuk disamakan dengan SASA
  482.     INSERT INTO gl_journal_trx_mapping
  483.     (tenant_id, journal_trx_id, line_no,
  484.     ref_doc_type_id, ref_id,
  485.     partner_id, product_id, cashbank_id, ou_rc_id,
  486.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  487.     coa_id, curr_code, qty, uom_id,
  488.     amount, journal_date, type_rate,
  489.     numerator_rate, denominator_rate, journal_desc, remark,
  490.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  491.     ou_branch_id, ou_sub_bu_id)
  492.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  493.         A.ref_doc_type_id, A.ref_id,
  494.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  495.         A.segmen_id, vSignDebit, A.flg_source_coa, A.activity_gl_id,
  496.         vCoaIdGIT,A.curr_code , A.qty, A.uom_id,
  497.         A.amount, A.journal_date, A.type_rate,
  498.         A.numerator_rate, A.denominator_rate, 'PRODUCT_STOCK_IN_TRANSIT', A.remark,
  499.         0, vDatetime, vUserId, vDatetime, vUserId,
  500.         (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id     
  501.     FROM tt_journal_trx_item A
  502.     WHERE A.session_id = pSessionId;
  503.        
  504.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; 
  505. END;   
  506. $BODY$
  507.   LANGUAGE plpgsql VOLATILE
  508.   COST 100;
  509. /
RAW Paste Data