samuel025

Function Goods Purchasing

Jun 16th, 2021
742
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --pu_submit_goods_purchasing
  2.  
  3. -- DROP FUNCTION pu_submit_goods_purchasing(bigint, character varying, character varying);
  4.  
  5. CREATE OR REPLACE FUNCTION pu_submit_goods_purchasing(bigint, character varying, character varying)
  6.   RETURNS void AS  
  7. $BODY$
  8. DECLARE
  9.     pTenantId           ALIAS FOR $1;
  10.     pSessionId          ALIAS FOR $2;
  11.     pProcessNo          ALIAS FOR $3;
  12.  
  13.     vProcessId              bigint;
  14.     vPoId                   bigint;
  15.     vGoodsPurchasingId      bigint;
  16.     vUserId                 bigint;
  17.     vDatetime               character varying(14);
  18.     vStatusRelease          character varying(1);
  19.     vStatusFinal            character varying(1);
  20.     vEmptyValue             character varying(1);
  21.     vFlagInvoice            character varying(1);
  22.     vFlagInvoiceY           character varying(1);
  23.     vStatusDraft            character varying(1);
  24.     vSignJournalD           character varying(1);
  25.     vSignJournalC           character varying(1);
  26.     vFlagPayment            character varying(1);
  27.     vFlgSourceCoa           character varying(7);
  28.     vTypeRate               character varying(3);
  29.     vEmptyId                bigint;
  30.     vPurchaseOrderDocTypeId bigint;
  31.     vJournalTrxId           bigint;
  32.     vJournalTrxIdNew        bigint;
  33.     vReceiveGoodsDocTypeId  bigint;
  34.     vInvoiceApBalanceId     bigint;
  35.     vRoundingModeNonTax     character varying(5);
  36.     vProductStatus          character varying(50);
  37.     vGoodsPurchasingDocTypeId   bigint;
  38.     vJournalReceiveGoodsId      bigint;
  39.     vTypeDocumentJournal        bigint;
  40.     vJointDppPpn            character varying(1);
  41.    
  42. BEGIN
  43.    
  44.     vFlagInvoice := 'N';
  45.     vFlagInvoiceY := 'Y';
  46.     vFlagPayment := 'N';
  47.     vStatusRelease := 'R';
  48.     vStatusFinal := 'F';
  49.     vStatusDraft := 'D';
  50.     vSignJournalD := 'D';
  51.     vSignJournalC := 'C';
  52.     vFlgSourceCoa := 'PRODUCT';
  53.     vTypeRate := 'COM';
  54.     vEmptyId := -99;
  55.     vEmptyValue := ' ';
  56.    
  57.     --vJournalReceiveGoodsId := 111;
  58.     vReceiveGoodsDocTypeId := 111;
  59.     vGoodsPurchasingDocTypeId := 160;
  60.     vTypeDocumentJournal := 131;
  61.    
  62.     vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');
  63.    
  64.     vProductStatus := 'GOOD';
  65.    
  66.     /*
  67.     SELECT product_status_code INTO vProductStatus
  68.     FROM m_product_status
  69.     WHERE tenant_id = pTenantId AND flg_buy = 'Y';
  70.     */
  71.    
  72.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  73.    
  74.     SELECT A.process_message_id INTO vProcessId
  75.     FROM t_process_message A
  76.     WHERE A.tenant_id = pTenantId AND
  77.         A.process_name = 'pu_submit_goods_purchasing' AND
  78.         A.process_no = pProcessNo;
  79.        
  80.     SELECT CAST(A.process_parameter_value AS bigint) INTO vGoodsPurchasingId
  81.     FROM t_process_parameter A
  82.     WHERE A.process_message_id = vProcessId AND
  83.         A.process_parameter_key = 'goodsPurchasingId';
  84.    
  85.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  86.     FROM t_process_parameter A
  87.     WHERE A.process_message_id = vProcessId AND
  88.         A.process_parameter_key = 'userId';
  89.  
  90.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  91.     FROM t_process_parameter A
  92.     WHERE A.process_message_id = vProcessId AND
  93.         A.process_parameter_key = 'datetime';
  94.        
  95.    
  96.     DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
  97.    
  98.     UPDATE pu_po SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
  99.     WHERE po_id = vGoodsPurchasingId;
  100.    
  101.    
  102.     INSERT INTO pu_po_balance_invoice
  103.     (tenant_id, ou_id, partner_id, po_id,
  104.     ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
  105.     curr_code, price_po, item_amount, flg_invoice, invoice_id,
  106.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  107.     SELECT pTenantId, A.ou_id, A.partner_id, A.ref_id,
  108.         A.doc_type_id, A.po_id, A.doc_no, A.doc_date, B.po_item_id, B.qty_po, B.po_uom_id,
  109.         B.curr_code, B.nett_price_po,
  110.         f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
  111.         vFlagInvoiceY, vEmptyId,
  112.         0, vDatetime, vUserId, vDatetime, vUserId
  113.     FROM pu_po A
  114.     INNER JOIN pu_po_item B
  115.         ON A.po_id = B.po_id
  116.     WHERE A.po_id = vGoodsPurchasingId
  117.     AND A.po_id = B.po_id
  118.     AND A.tenant_id = pTenantId;
  119.     --FILTER LAINNYA
  120.    
  121.    
  122.     INSERT INTO pu_po_balance_invoice_tax
  123.     (tenant_id, ou_id, partner_id, po_id,
  124.     ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  125.     tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  126.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  127.     SELECT pTenantId, A.ou_id, A.partner_id, A.po_id,
  128.         A.doc_type_id, A.po_id, B.po_item_id, B.tax_id, C.flg_amount,
  129.         B.tax_percentage, B.curr_code,
  130.         f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
  131.         f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage),    
  132.         vFlagInvoiceY, vEmptyId,
  133.         0, vDatetime, vUserId, vDatetime, vUserId
  134.     FROM pu_po A
  135.     INNER JOIN pu_po_item B
  136.         ON A.po_id = B.po_id
  137.     INNER JOIN m_tax C
  138.         ON B.tax_id = C.tax_id
  139.     WHERE A.po_id = vGoodsPurchasingId
  140.     AND A.po_id = B.po_id
  141.     AND B.tax_id = C.tax_id
  142.     AND A.tenant_id = pTenantId;
  143.     --FILTER LAINNYA
  144.    
  145.    
  146.     --Insert ke tt_pu_product_balance produck nya belum terdaftar di product balance--
  147.     INSERT INTO tt_pu_product_balance
  148.     (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
  149.     serial_number, lot_number, product_expired_date, product_year_made,
  150.     product_price_balance_id, product_buy_date, partner_id,
  151.     doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
  152.     po_id, po_no, po_date, po_item_id,
  153.     curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
  154.     SELECT pSessionId, B.warehouse_id, vEmptyId, pTenantId, B.ou_id, A.product_id,
  155.         A.serial_number, A.lot_number, A.expired_date, A.year_made,
  156.         vEmptyId, B.doc_date, B.partner_id,
  157.         B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
  158.         A.po_id, B.doc_no, B.doc_date, A.po_item_id,
  159.         C.curr_code, C.gross_price_po, SUM(C.qty_po), C.po_uom_id, SUM(C.qty_int), C.base_uom_id, C.flg_stock
  160.     FROM pu_po_item_product A
  161.     INNER JOIN pu_po B
  162.         ON A.po_id = B.po_id
  163.     INNER JOIN pu_po_item C
  164.         ON C.po_item_id = A.po_item_id
  165.     WHERE A.po_id = vGoodsPurchasingId
  166.     AND A.tenant_id = pTenantId
  167.     AND NOT EXISTS
  168.         (
  169.             SELECT 1
  170.             FROM in_product_balance Z
  171.             WHERE Z.tenant_id = pTenantId
  172.                 AND Z.product_id = A.product_id
  173.                 AND Z.serial_number = A.serial_number
  174.                 AND Z.lot_number = A.lot_number
  175.         )
  176.     GROUP BY B.warehouse_id, A.tenant_id, B.ou_id, A.product_id, B.doc_date, B.partner_id,
  177.         B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
  178.         A.po_id, B.doc_no, B.doc_date, A.po_item_id,
  179.         C.curr_code, C.gross_price_po,  C.po_uom_id, C.base_uom_id, C.flg_stock;
  180.        
  181.        
  182.        
  183.     --Insert ke tt_pu_product_balance produck nya terdaftar di product balance--
  184.     INSERT INTO tt_pu_product_balance
  185.     (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
  186.     serial_number, lot_number, product_expired_date, product_year_made,
  187.     product_price_balance_id, product_buy_date, partner_id,
  188.     doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
  189.     po_id, po_no, po_date, po_item_id,
  190.     curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
  191.     SELECT pSessionId, B.warehouse_id, vEmptyId, A.tenant_id, B.ou_id, A.product_id,
  192.         A.serial_number, A.lot_number, A.expired_date, A.year_made,
  193.         vEmptyId, B.doc_date, B.partner_id,
  194.         B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
  195.         A.po_id, B.doc_no, B.doc_date, A.po_item_id,
  196.         C.curr_code, C.gross_price_po, SUM(C.qty_po), C.po_uom_id, SUM(C.qty_int), C.base_uom_id, C.flg_stock
  197.     FROM pu_po_item_product A
  198.     INNER JOIN pu_po B
  199.         ON B.po_id = A.po_id
  200.     INNER JOIN pu_po_item C
  201.         ON C.po_item_id = A.po_item_id
  202.     INNER JOIN in_product_balance D
  203.         ON D.tenant_id = A.tenant_id
  204.         AND D.product_id = A.product_id
  205.         AND D.serial_number = A.serial_number
  206.         AND D.lot_number = A.lot_number
  207.     WHERE A.po_id = vGoodsPurchasingId
  208.     AND A.tenant_id = pTenantId
  209.     GROUP BY B.warehouse_id, A.tenant_id, B.ou_id, A.product_id, B.doc_date, B.partner_id,
  210.         B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
  211.         A.po_id, B.doc_no, B.doc_date, A.po_item_id,
  212.         C.curr_code, C.gross_price_po,  C.po_uom_id, C.base_uom_id, C.flg_stock;
  213.        
  214.        
  215.        
  216.     /*
  217.      * update product_balance_id dari in_product_balance
  218.      */    
  219.     UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
  220.     FROM in_product_balance A
  221.     WHERE tt_pu_product_balance.session_id = pSessionId AND
  222.         tt_pu_product_balance.tenant_id = A.tenant_id AND
  223.         tt_pu_product_balance.product_id = A.product_id AND
  224.         tt_pu_product_balance.serial_number = A.serial_number AND
  225.         tt_pu_product_balance.lot_number = A.lot_number;
  226.    
  227.    
  228.     --Insert ke in_product_balance yang id nya masih emptyId/ tidak ada di in product balance--
  229.     INSERT INTO in_product_balance
  230.     (tenant_id, product_id,
  231.     serial_number, lot_number, product_expired_date, product_year_made,
  232.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  233.     SELECT A.tenant_id, A.product_id,
  234.         A.serial_number, A.lot_number, A.product_expired_date, A.product_year_made,
  235.         0, vDatetime, vUserId, vDatetime, vUserId
  236.     FROM tt_pu_product_balance A
  237.     WHERE A.session_id = pSessionId AND
  238.         A.product_balance_id = vEmptyId
  239.     GROUP BY A.tenant_id, A.product_id, serial_number, lot_number, product_expired_date, product_year_made;
  240.    
  241.    
  242.     /*
  243.      * update product_balance_id yang masih empty, ambil dari in_product_balance yang sebelumnya sudah diinsert
  244.      */
  245.     UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
  246.     FROM in_product_balance A
  247.     WHERE tt_pu_product_balance.session_id = pSessionId AND
  248.         tt_pu_product_balance.product_balance_id = vEmptyId AND
  249.         tt_pu_product_balance.tenant_id = A.tenant_id AND
  250.         tt_pu_product_balance.product_id = A.product_id AND
  251.         tt_pu_product_balance.serial_number = A.serial_number AND
  252.         tt_pu_product_balance.lot_number = A.lot_number;
  253.    
  254.        
  255.     --Insert ke in_product_price_balance dengan product balance yang sudah diinsert sebelumnya--
  256.     INSERT INTO in_product_price_balance
  257.     (tenant_id, ou_id, product_id, product_balance_id,
  258.     product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
  259.     curr_code, amount, qty, uom_id,
  260.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  261.     SELECT A.tenant_id, A.ou_id, A.product_id, A.product_balance_id,
  262.         A.doc_date, A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.doc_date,
  263.         A.curr_code, SUM(f_get_amount_before_tax(B.qty_po * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
  264.         SUM(B.qty_int), A.base_uom_id,
  265.         0, vDatetime, vUserId, vDatetime, vUserId
  266.     FROM tt_pu_product_balance A
  267.     INNER JOIN pu_po_item B
  268.         ON A.po_id = B.po_id
  269.     WHERE A.session_id = pSessionId AND
  270.          A.po_id = B.po_id
  271.     GROUP BY A.tenant_id, A.ou_id, A.product_id, A.product_balance_id, A.doc_date,
  272.         A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.curr_code, A.base_uom_id;
  273.    
  274.    
  275.     UPDATE tt_pu_product_balance SET product_price_balance_id = A.product_price_balance_id
  276.     FROM in_product_price_balance A
  277.     WHERE tt_pu_product_balance.session_id = pSessionId AND
  278.         tt_pu_product_balance.tenant_id = A.tenant_id AND
  279.         tt_pu_product_balance.ou_id = A.ou_id AND
  280.         tt_pu_product_balance.product_id = A.product_id AND    
  281.         tt_pu_product_balance.product_balance_id = A.product_balance_id AND
  282.         tt_pu_product_balance.partner_id = A.partner_id AND
  283.         tt_pu_product_balance.doc_type_id = A.doc_type_id AND
  284.         tt_pu_product_balance.ref_item_id = A.ref_id AND
  285.         tt_pu_product_balance.doc_no = A.doc_no AND
  286.         tt_pu_product_balance.doc_date = A.doc_date;
  287.        
  288.    
  289.     /*
  290.      * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
  291.      */
  292.     INSERT INTO tt_pu_product_balance_summary_stock
  293.             (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
  294.             product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
  295.     SELECT  pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
  296.             vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
  297.     FROM    tt_pu_product_balance A
  298.     WHERE   A.session_id = pSessionId
  299.     GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
  300.              A.po_uom_id, A.base_uom_id, A.flg_stock;
  301.    
  302.    
  303.     /*
  304.      * update product_balance_stock, yang sudah ada di in_product_balance_stock
  305.      */
  306.     UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv,
  307.     update_datetime = vDatetime, update_user_id = vUserId,
  308.     version = version + 1
  309.     FROM tt_pu_product_balance_summary_stock A
  310.     WHERE A.session_id = pSessionId AND
  311.         in_product_balance_stock.product_id = A.product_id AND
  312.         in_product_balance_stock.tenant_id = A.tenant_id AND
  313.         in_product_balance_stock.warehouse_id = A.warehouse_id AND
  314.         in_product_balance_stock.product_balance_id = A.product_balance_id AND
  315.         in_product_balance_stock.product_status = vProductStatus AND
  316.         A.flg_stock = 'Y';
  317.              
  318.        
  319.        
  320.     /*
  321.      * insert data in_product_balance_stock yang datanya belum ada
  322.      */
  323.     INSERT INTO in_product_balance_stock
  324.     (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
  325.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  326.     SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, vProductStatus,
  327.         A.base_uom_id, SUM(A.qty_int_rcv),
  328.         0, vDatetime, vUserId, vDatetime, vUserId
  329.     FROM tt_pu_product_balance A
  330.     WHERE A.session_id = pSessionId AND
  331.         A.flg_stock = 'Y' AND
  332.         NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
  333.                     WHERE A.tenant_id = B.tenant_id AND
  334.                         A.warehouse_id = B.warehouse_id AND
  335.                         A.product_id = B.product_id AND
  336.                         A.product_balance_id = B.product_balance_id AND
  337.                         B.product_status = vProductStatus)
  338.     GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.base_uom_id;
  339.        
  340.    
  341.     /*
  342.      * insert data product_price_balance_stock
  343.      */
  344.     INSERT INTO in_product_price_balance_stock
  345.     (tenant_id, warehouse_id, product_id, product_balance_id,
  346.     product_price_balance_id, product_status, base_uom_id, qty,
  347.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  348.     SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
  349.         A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
  350.         0, vDatetime, vUserId, vDatetime, vUserId
  351.     FROM tt_pu_product_balance A
  352.     WHERE A.session_id = pSessionId AND
  353.         A.flg_stock = 'Y'
  354.     GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
  355.    
  356.    
  357.     /*
  358.      * buat data log product balance stock
  359.      */
  360.     INSERT INTO in_log_product_balance_stock
  361.     (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  362.      product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  363.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  364.     SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  365.         A.product_id, A.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
  366.         0, vDatetime, vUserId, vDatetime, vUserId      
  367.     FROM tt_pu_product_balance A, m_warehouse_ou B
  368.     WHERE A.session_id = pSessionId AND
  369.         A.flg_stock = 'Y' AND
  370.         A.warehouse_id = B.warehouse_id
  371.     GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  372.         A.product_id, A.product_balance_id, A.warehouse_id, A.base_uom_id;
  373.  
  374.        
  375.     /*
  376.      * buat data log product price balance stock
  377.      */
  378.     INSERT INTO in_log_product_price_balance_stock
  379.     (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  380.      product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
  381.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  382.     SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  383.         A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
  384.         0, vDatetime, vUserId, vDatetime, vUserId
  385.     FROM tt_pu_product_balance A
  386.     WHERE A.session_id = pSessionId AND
  387.         A.flg_stock = 'Y'
  388.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  389.         A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;
  390.        
  391.    
  392.     /*
  393.      * add data balance receive goods item yang akan digunakan di inventory,
  394.      * saat akan membuat claim note
  395.      */
  396.     INSERT INTO in_balance_receive_goods_item
  397.     (receive_goods_item_id, tenant_id, ou_id, receive_goods_id, doc_no, doc_date, partner_id,
  398.       po_id, po_no, po_date, po_item_id,
  399.       qty_rcv, qty_return, po_uom_id, qty_int_rcv, 
  400.       qty_int_return, base_uom_id, status_item,
  401.       "version", create_datetime, create_user_id, update_datetime, update_user_id)
  402.     SELECT A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  403.             A.po_id, A.po_no, A.po_date, A.po_item_id,
  404.             SUM(A.qty_rcv), 0, A.po_uom_id, SUM(A.qty_int_rcv),
  405.             0, A.base_uom_id, vStatusRelease,
  406.         0, vDatetime, vUserId, vDatetime, vUserId      
  407.     FROM tt_pu_product_balance A
  408.     WHERE A.session_id = pSessionId
  409.     GROUP BY A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
  410.             A.po_id, A.po_no, A.po_date, A.po_item_id, A.po_uom_id, A.base_uom_id;
  411.        
  412.    
  413.            
  414.     /*
  415.      * create monthly price product
  416.      */
  417.     INSERT INTO tt_pu_monthly_price_product
  418.     (session_id, tenant_id, ou_id, year_month_date,
  419.     product_id, curr_code, amount, qty, base_uom_id,
  420.      ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, source_price,
  421.      flg_tax_amount, tax_id, tax_percentage)
  422.     SELECT pSessionId, A.tenant_id, A.ou_id, C.year_month_date,
  423.         A.product_id, A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
  424.         SUM(A.qty_int_rcv), A.base_uom_id,
  425.         A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
  426.         'PURCHASING PO NO : '|| A.po_no || ' DATE : '|| A.po_date,
  427.         B.flg_tax_amount, B.tax_id, B.tax_percentage
  428.     FROM tt_pu_product_balance A, pu_po_item B, dt_date C
  429.     WHERE A.session_id = pSessionId AND
  430.         A.po_item_id = B.po_item_id AND
  431.         A.doc_date = C.string_date
  432.     GROUP BY A.tenant_id, A.ou_id, C.year_month_date, A.product_id, A.curr_code, A.base_uom_id,
  433.         A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.po_no, A.po_date, B.flg_tax_amount, B.tax_id, B.tax_percentage;
  434.      
  435.     /*
  436.      * Update pu_monthly_price_product
  437.      */
  438.     UPDATE pu_monthly_price_product SET curr_code = A.curr_code, amount = A.amount,
  439.                                         qty = A.qty, ref_doc_type_id = A.ref_doc_type_id,
  440.                                         ref_id = A.ref_id, ref_doc_no = A.ref_doc_no, ref_doc_date = A.ref_doc_date,
  441.                                         source_price = A.source_price, flg_tax_amount = A.flg_tax_amount,
  442.                                         tax_id = A.tax_id, tax_percentage = A.tax_percentage
  443.     FROM tt_pu_monthly_price_product A
  444.     WHERE A.session_id = pSessionId AND
  445.         pu_monthly_price_product.tenant_id = A.tenant_id AND
  446.         pu_monthly_price_product.ou_id = A.ou_id AND
  447.         pu_monthly_price_product.year_month_date = A.year_month_date AND
  448.         pu_monthly_price_product.product_id = A.product_id;
  449.        
  450.     INSERT INTO pu_monthly_price_product
  451.     (tenant_id, ou_id, year_month_date, product_id,
  452.     curr_code, amount, qty, base_uom_id, source_price,
  453.     ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
  454.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  455.     flg_tax_amount, tax_id, tax_percentage)
  456.     SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
  457.             A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
  458.             A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
  459.             0, vDatetime, vUserId, vDatetime, vUserId,
  460.             A.flg_tax_amount, A.tax_id, A.tax_percentage
  461.     FROM tt_pu_monthly_price_product A
  462.     WHERE A.session_id = pSessionId AND
  463.         NOT EXISTS (SELECT 1 FROM pu_monthly_price_product B
  464.                     WHERE A.tenant_id = B.tenant_id AND
  465.                         A.ou_id = B.ou_id AND
  466.                         A.product_id = B.product_id AND
  467.                         A.year_month_date = B.year_month_date)
  468.     GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id, A.curr_code, A.base_uom_id, A.source_price,
  469.              A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
  470.    
  471.     INSERT INTO pu_log_monthly_price_product
  472.     (tenant_id, ou_id, year_month_date, product_id,
  473.     curr_code, amount, qty, base_uom_id, source_price,
  474.     ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
  475.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  476.     flg_tax_amount, tax_id, tax_percentage)
  477.     SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
  478.         A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
  479.         A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
  480.         0, vDatetime, vUserId, vDatetime, vUserId,
  481.         A.flg_tax_amount, A.tax_id, A.tax_percentage
  482.     FROM tt_pu_monthly_price_product A
  483.     WHERE A.session_id = pSessionId
  484.     GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
  485.              A.curr_code, A.base_uom_id, A.source_price, A.ref_doc_type_id, A.ref_id,
  486.              A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;
  487.              
  488.              
  489.    
  490.  
  491.     /*
  492.      * membuat data transaksi jurnal
  493.      */
  494.  
  495.    
  496.     SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  497.    
  498.     INSERT INTO gl_journal_trx
  499.     (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  500.     ou_bu_id, ou_branch_id, ou_sub_bu_id,
  501.     partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  502.     ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
  503.     due_date, curr_code, remark, status_doc, workflow_status,
  504.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  505.     SELECT vJournalTrxId, pTenantId, (f_get_document_journal(vReceiveGoodsDocTypeId)).journal_type, vReceiveGoodsDocTypeId, A.po_id, A.doc_no, A.doc_date,
  506.         (f_get_ou_bu_structure(A.ou_id)).ou_bu_id, (f_get_ou_bu_structure(A.ou_id)).ou_branch_id, (f_get_ou_bu_structure(A.ou_id)).ou_sub_bu_id,
  507.         A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
  508.         A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
  509.         A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
  510.         0, vDatetime, vUserId, vDatetime, vUserId
  511.     FROM pu_po A
  512.     WHERE A.po_id = vGoodsPurchasingId
  513.     AND A.tenant_id = pTenantId;
  514.         --A.ref_doc_type_id = B.doc_type_id AND
  515.         --A.ref_id = B.po_id;
  516.    
  517.     /*------------------------------------------------*/
  518.  
  519.     INSERT INTO gl_journal_trx_item
  520.     (tenant_id, journal_trx_id, line_no,
  521.     ref_doc_type_id, ref_id,
  522.     partner_id, product_id, cashbank_id, ou_rc_id,
  523.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  524.     coa_id, curr_code, qty, uom_id,
  525.     amount,
  526.     journal_date, type_rate,
  527.     numerator_rate, denominator_rate, journal_desc, remark,
  528.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  529.     ou_branch_id, ou_sub_bu_id)
  530.     SELECT pTenantId, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId ),
  531.         A.doc_type_id, B.po_item_id,
  532.         A.partner_id, B.product_id, vEmptyId, B.ou_rc_id,
  533.         vEmptyId, vSignJournalD, 'PRODUCT', B.activity_gl_id,
  534.         B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id,
  535.         SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
  536.         A.doc_date, 'COM',
  537.         1, 1, 'PRODUCT_STOCK', B.remark,
  538.         0, vDatetime, vUserId, vDatetime, vUserId,
  539.         vEmptyId, vEmptyId
  540.     FROM pu_po A
  541.     INNER JOIN pu_po_item B
  542.         ON B.po_id = A.po_id
  543.     WHERE A.po_id = vGoodsPurchasingId
  544.     AND A.tenant_id = pTenantId
  545.     AND B.flg_stock = 'Y'
  546.     GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
  547.           B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
  548.    
  549.     /*------------------------------------------------*/
  550.          
  551.     INSERT INTO gl_journal_trx_mapping
  552.     (tenant_id, journal_trx_id, line_no,
  553.     ref_doc_type_id, ref_id,
  554.     partner_id, product_id, cashbank_id, ou_rc_id,
  555.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  556.     coa_id,
  557.     curr_code, qty, uom_id,
  558.     amount,
  559.     journal_date, type_rate,
  560.     numerator_rate, denominator_rate, journal_desc, remark,
  561.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  562.     SELECT A.tenant_id, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId ),
  563.         vEmptyId, vEmptyId,
  564.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  565.         vEmptyId, vSignJournalC, 'SYSTEM', vEmptyId,
  566.         f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'),
  567.         A.curr_code, 0, vEmptyId,
  568.         SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
  569.         A.doc_date, 'COM',
  570.         1, 1, 'ACCR_AP', B.remark,
  571.         0, vDatetime, vUserId, vDatetime, vUserId
  572.     FROM pu_po A
  573.     INNER JOIN pu_po_item B
  574.         ON B.po_id = A.po_id
  575.     WHERE A.po_id = vGoodsPurchasingId
  576.     AND A.tenant_id = pTenantId
  577.     AND B.flg_stock = 'Y'
  578.     GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
  579.           A.curr_code, B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
  580.    
  581.     /*------------------------------------------------*/
  582.    
  583.     SELECT nextval('fi_invoice_ap_balance_seq') INTO vInvoiceApBalanceId;
  584.          
  585.     IF vJointDppPpn = 'N' THEN
  586.        
  587.         INSERT INTO fi_invoice_ap_balance
  588.         ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
  589.         doc_no, doc_date, ext_doc_no, ext_doc_date,
  590.         ref_doc_type_id, ref_id,  partner_id, due_date,  
  591.         curr_code, amount, remark, payment_amount, flg_payment,
  592.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  593.         SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
  594.                 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
  595.                 vEmptyId, vEmptyId, A.partner_id, A.doc_date,
  596.                 A.curr_code, SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
  597.                 A.remark, 0, vFlagPayment,
  598.                 0, vDatetime, vUserId, vDatetime, vUserId
  599.         FROM pu_po A
  600.         INNER JOIN pu_po_item B
  601.             ON B.po_id = A.po_id
  602.         WHERE A.po_id = vGoodsPurchasingId
  603.         AND A.tenant_id = pTenantId
  604.         GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
  605.                  A.curr_code, A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
  606.                  
  607.                  
  608.         INSERT INTO fi_invoice_tax_ap_balance
  609.         ( tenant_id, ou_id, doc_type_id, invoice_ap_balance_id,
  610.         partner_id, tax_id, tax_no, tax_date,
  611.         curr_code, tax_amount, tax_curr_code, gov_tax_amount,
  612.         due_date, remark, payment_amount, flg_payment,
  613.         "version", create_datetime, create_user_id, update_datetime, update_user_id,
  614.         gov_base_amount )
  615.         SELECT pTenantId, A.ou_id, A.doc_type_id, vInvoiceApBalanceId,     
  616.             A.partner_id, B.tax_id, B.tax_no, B.tax_date,
  617.             A.curr_code,
  618.             SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
  619.             A.curr_code,
  620.             SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
  621.             A.doc_date, A.remark, 0, vFlagPayment,
  622.             0, vDatetime, vUserId, vDatetime, vUserId,
  623.             SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage))
  624.         FROM pu_po A
  625.         INNER JOIN pu_po_item B
  626.             ON B.po_id = A.po_id
  627.         INNER JOIN m_tax C
  628.             ON C.tax_id = B.tax_id
  629.         WHERE A.po_id = vGoodsPurchasingId
  630.         AND A.tenant_id = pTenantId;
  631.        
  632.         perform fi_insert_vat_in_for_efaktur(pTenantId, pSessionId, vGoodsPurchasingId, vUserId, vDatetime);
  633.                
  634.     ELSE
  635.    
  636.         INSERT INTO fi_invoice_ap_balance
  637.         ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
  638.         doc_no, doc_date, ext_doc_no, ext_doc_date,
  639.         ref_doc_type_id, ref_id,  partner_id, due_date, curr_code,
  640.         amount, remark, payment_amount, flg_payment,
  641.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  642.         SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
  643.                 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
  644.                 vEmptyId, vEmptyId, A.partner_id, A.doc_date, A.curr_code,
  645.                 SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax))
  646.                     + SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), COALESCE(B.tax_percentage,0))),    
  647.                 A.remark, 0, vFlagPayment,
  648.                 0, vDatetime, vUserId, vDatetime, vUserId
  649.         FROM pu_po A
  650.         INNER JOIN pu_po_item B
  651.             ON B.po_id = A.po_id
  652.         LEFT JOIN m_tax C
  653.             ON C.tax_id = B.tax_id
  654.         WHERE A.po_id = vGoodsPurchasingId
  655.         AND A.tenant_id = pTenantId
  656.         GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
  657.                  A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
  658.                  
  659.     /*
  660.         INSERT INTO fi_invoice_ap_balance
  661.         ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
  662.         doc_no, doc_date, ext_doc_no, ext_doc_date,
  663.         ref_doc_type_id, ref_id,  partner_id, due_date, curr_code,
  664.         amount, remark, payment_amount, flg_payment,
  665.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  666.         SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
  667.                 A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
  668.                 vEmptyId, vEmptyId, A.partner_id, A.doc_date, A.curr_code,
  669.                 SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax))
  670.                 + SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),    
  671.                 A.remark, 0, vFlagPayment,
  672.                 0, vDatetime, vUserId, vDatetime, vUserId
  673.         FROM pu_po A
  674.         INNER JOIN pu_po_item B
  675.             ON B.po_id = A.po_id
  676.         INNER JOIN m_tax C
  677.             ON C.tax_id = B.tax_id
  678.         WHERE A.po_id = vGoodsPurchasingId
  679.         AND A.tenant_id = pTenantId
  680.         GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
  681.                  A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
  682.     */
  683.                          
  684.         perform fi_insert_vat_in_invoice_ap_for_efaktur(pTenantId, pSessionId, vGoodsPurchasingId, vUserId, vDatetime);
  685.    
  686.     END IF;
  687.    
  688.    
  689.     /*
  690.      * Insert ke journal gl
  691.      */
  692.     SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxIdNew;
  693.    
  694.     INSERT INTO gl_journal_trx
  695.     (journal_trx_id, tenant_id, journal_type,
  696.     doc_type_id, doc_id, doc_no, doc_date,
  697.     ou_bu_id, ou_branch_id, ou_sub_bu_id,
  698.     partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  699.     ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
  700.     due_date, curr_code, remark, status_doc, workflow_status,
  701.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  702.     SELECT vJournalTrxIdNew, pTenantId, (f_get_document_journal(vTypeDocumentJournal)).journal_type,
  703.         vTypeDocumentJournal, A.po_id, A.doc_no, A.doc_date,
  704.         (f_get_ou_bu_structure(A.ou_id)).ou_bu_id, (f_get_ou_bu_structure(A.ou_id)).ou_branch_id, (f_get_ou_bu_structure(A.ou_id)).ou_sub_bu_id,
  705.         A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
  706.         A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
  707.         A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
  708.         0, vDatetime, vUserId, vDatetime, vUserId
  709.     FROM pu_po A
  710.     WHERE A.po_id = vGoodsPurchasingId
  711.     AND A.tenant_id = pTenantId;
  712.    
  713.    
  714.     --Insert untuk item 1--
  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,
  722.     journal_date, type_rate,
  723.     numerator_rate, denominator_rate, journal_desc, remark,
  724.     gl_curr_code, gl_amount )
  725.     SELECT pSessionId, pTenantId, vJournalTrxIdNew, 1,
  726.         A.doc_type_id, B.po_item_id,
  727.         A.partner_id,  vEmptyId, vEmptyId, vEmptyId,
  728.         vEmptyId, vSignJournalD, 'SYSTEM', vEmptyId,
  729.         f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), B.curr_code, 0, vEmptyId,
  730.         SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
  731.         A.doc_date, 'COM',
  732.         1, 1, 'ACCR_AP', B.remark,
  733.         vEmptyValue, vEmptyId
  734.     FROM pu_po A
  735.     INNER JOIN pu_po_item B
  736.         ON B.po_id = A.po_id
  737.     WHERE A.po_id = vGoodsPurchasingId
  738.     AND A.tenant_id = pTenantId
  739.     AND B.flg_stock = 'Y'
  740.     GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
  741.           B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
  742.          
  743.     --Insert untuk item 2--
  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,
  751.     journal_date, type_rate,
  752.     numerator_rate, denominator_rate, journal_desc, remark,
  753.     gl_curr_code, gl_amount )
  754.     SELECT pSessionId, pTenantId, vJournalTrxIdNew, 1,
  755.         A.doc_type_id, B.po_id,
  756.         A.partner_id, B.product_id, vEmptyId, vEmptyId,
  757.         vEmptyId, vSignJournalD, 'TAX', vEmptyId,
  758.         C.receive_coa_id, B.curr_code, 0, vEmptyId,
  759.         SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
  760.         A.doc_date, 'COM',
  761.         1, 1, 'VAT_IN', B.remark,
  762.         vEmptyId, vEmptyId
  763.     FROM pu_po A
  764.     INNER JOIN pu_po_item B
  765.         ON B.po_id = A.po_id
  766.     INNER JOIN m_tax C
  767.         ON C.tax_id = B.tax_id
  768.     WHERE A.po_id = vGoodsPurchasingId
  769.     AND A.tenant_id = pTenantId
  770.     AND B.flg_stock = 'Y'
  771.     GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
  772.           C.receive_coa_id, B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;
  773.          
  774.          
  775.     INSERT INTO gl_journal_trx_item
  776.     (tenant_id, journal_trx_id, line_no,
  777.     ref_doc_type_id, ref_id,
  778.     partner_id, product_id, cashbank_id, ou_rc_id,
  779.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  780.     coa_id, curr_code, qty, uom_id,
  781.     amount, journal_date, type_rate,
  782.     numerator_rate, denominator_rate, journal_desc, remark,
  783.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  784.     ou_branch_id, ou_sub_bu_id)
  785.     SELECT pTenantId, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  786.         A.ref_doc_type_id, A.ref_id,
  787.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  788.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  789.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  790.         A.amount, A.journal_date, A.type_rate,
  791.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  792.         0, vDatetime, vUserId, vDatetime, vUserId,
  793.         vEmptyId, vEmptyId
  794.     FROM tt_journal_trx_item A
  795.     WHERE A.session_id = pSessionId;
  796.    
  797.    
  798.     INSERT INTO gl_journal_trx_mapping
  799.     (tenant_id, journal_trx_id, line_no,
  800.     ref_doc_type_id, ref_id,
  801.     partner_id, product_id, cashbank_id, ou_rc_id,
  802.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  803.     coa_id,
  804.     curr_code, qty, uom_id,
  805.     amount,
  806.     journal_date, type_rate,
  807.     numerator_rate, denominator_rate, journal_desc, remark,
  808.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  809.     SELECT pTenantId, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  810.         vEmptyId, vEmptyId,
  811.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  812.         vEmptyId, vSignJournalC, 'SYSTEM', vEmptyId,
  813.         f_get_ap_coa_partner(A.tenant_id, A.partner_id),
  814.         A.curr_code, 0, vEmptyId,
  815.         SUM(A.amount),
  816.         A.journal_date, 'COM',
  817.         1, 1, 'AP', A.remark,
  818.         0, vDatetime, vUserId, vDatetime, vUserId
  819.     FROM tt_journal_trx_item A
  820.     WHERE A.session_id = pSessionId
  821.     GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate, A.remark;
  822.  
  823.    
  824.     --Delete table temp tt_pu_product_balance--
  825.     DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
  826.        
  827. END;
  828. $BODY$
  829.   LANGUAGE plpgsql VOLATILE
  830.   COST 100;
  831.   /
RAW Paste Data