abirama62

func submit rn

Jan 25th, 2021
1,081
148 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION in_submit_return_note(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.     vReturnNoteId   bigint;
  11.     vUserId         bigint;
  12.     vDatetime       character varying(14);
  13.     vFlagInvoice    character varying(1);
  14.     vEmptyId        bigint;
  15.     vStatusRelease  character varying(1);
  16.     vEmptyValue     character varying(1);
  17.     vStatusFinal    character varying(1);
  18.     vStatusDraft    character varying(1);  
  19.     vFlagYes            character varying(1);
  20.     vFlagNo             character varying(1);
  21.     vEmptyString        character varying(1);  
  22.     vJournalType        character varying(20);
  23.     vSignDebit          character varying(1);
  24.     vSignCredit         character varying(1);
  25.     vTypeRate           character varying(3);
  26.     vProductCOA         character varying(10);
  27.     vSystemCOA          character varying(10);
  28.     vParentOuId         bigint;
  29.     vJournalTrxId       bigint;
  30.     vNol                numeric;
  31.     vOuId               bigint;
  32.     vOuWarehouseId      bigint;
  33.     vSoDocTypeId        bigint;
  34.     vDigitRounding      integer := 0;
  35.    
  36.     vDocJournal                 DOC_JOURNAL%ROWTYPE;
  37.     vOuStructure                OU_BU_STRUCTURE%ROWTYPE;
  38.     vOuStructureJournalItem     OU_BU_STRUCTURE%ROWTYPE;
  39.     result                      RECORD;
  40.    
  41.     vReturnNoteDocTypeId bigint;
  42.     vRoundingModeNonTax character varying(5);
  43.    
  44.     vRnFromRRS  boolean;
  45.     vRrsDocTypeId bigint := 381;
  46.     vTotalQtyItem       numeric;
  47.    
  48. BEGIN
  49.    
  50.     vFlagInvoice := 'N';
  51.     vEmptyId := -99;
  52.     vEmptyValue := ' ';
  53.     vStatusRelease := 'R';
  54.     vStatusFinal := 'F';
  55.     vStatusDraft := 'D';   
  56.     vFlagYes := 'Y';
  57.     vFlagNo := 'N';
  58.     vEmptyString := ' ';
  59.     vSignDebit := 'D';
  60.     vSignCredit := 'C';
  61.     vTypeRate := 'COM';
  62.     vProductCOA := 'PRODUCT';
  63.     vSystemCOA := 'SYSTEM';
  64.     vNol := 0;
  65.     vSoDocTypeId := 301;
  66.    
  67.     vReturnNoteDocTypeId := 502;
  68.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  69.    
  70.    
  71.     SELECT A.process_message_id INTO vProcessId
  72.     FROM t_process_message A
  73.     WHERE A.tenant_id = pTenantId AND
  74.         A.process_name = 'in_submit_return_note' AND
  75.         A.process_no = pProcessNo;
  76.        
  77.     SELECT CAST(A.process_parameter_value AS bigint) INTO vReturnNoteId
  78.     FROM t_process_parameter A
  79.     WHERE A.process_message_id = vProcessId AND
  80.         A.process_parameter_key = 'returnNoteId';
  81.    
  82.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  83.     FROM t_process_parameter A
  84.     WHERE A.process_message_id = vProcessId AND
  85.         A.process_parameter_key = 'userId';
  86.  
  87.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  88.     FROM t_process_parameter A
  89.     WHERE A.process_message_id = vProcessId AND
  90.         A.process_parameter_key = 'datetime';
  91.  
  92.     DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
  93.    
  94.     -- Get total qty itemm
  95.     SELECT SUM(A.qty_realization) INTO vTotalQtyItem
  96.     FROM in_inventory_item A
  97.     WHERE A.inventory_id = vReturnNoteId;
  98.    
  99.     SELECT EXISTS(
  100.         SELECT 1
  101.         FROM in_inventory A
  102.         WHERE A.inventory_id = vReturnNoteId
  103.         AND A.ref_doc_type_id = vRrsDocTypeId
  104.     ) INTO vRnFromRRS;
  105.    
  106.     /*
  107.      * 1.update status doc in_inventory
  108.      * 2.update data ke in_product_balance_stock / insert data ke in_product-balance_stock
  109.      * 3.insert data ke in_log_product_balance_stock
  110.      * 4.update data sl_so_balance_item
  111.      * 5.insert data sl_log_so_balance_item
  112.      * 6.insert data sl_so_balance_invoice
  113.      * 7.insert data sl_so_balance_invoice_tax
  114.      * 8.update data in_balance_do_item
  115.      * 9.add trx jurnal
  116.      */
  117.        
  118.     SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
  119.     FROM in_inventory A
  120.     WHERE A.inventory_id = vReturnNoteId INTO result;
  121.    
  122.     vOuStructure := result.ou;
  123.     vDocJournal := result.doc; 
  124.    
  125.     UPDATE in_inventory SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
  126.     WHERE inventory_id = vReturnNoteId;
  127.    
  128.     /*
  129.      * insert ke in_product_balance jika belum ada balancenya
  130.      * added by erl 19 nov 2020, terkait rrs di erp
  131.      */
  132.     INSERT INTO in_product_balance(
  133.                 tenant_id, product_id, serial_number, lot_number,
  134.                 product_expired_date, product_year_made, version, create_datetime,
  135.                 create_user_id, update_datetime, update_user_id)
  136.     SELECT A.tenant_id, B.product_id, B.serial_number, B.lot_number,
  137.             B.product_expired_date, B.product_year_made, 0, vDatetime,
  138.             vUserId, vDatetime, vUserId
  139.     FROM in_inventory A, in_inventory_item B
  140.     WHERE A.inventory_id = B.inventory_id AND
  141.     A.inventory_id = vReturnNoteId AND
  142.     B.qty_realization > 0 AND
  143.     NOT EXISTS (SELECT 1 FROM in_product_balance F
  144.             WHERE F.tenant_id = A.tenant_id AND
  145.             F.product_id = B.product_id AND
  146.             F.serial_number = B.serial_number AND
  147.             F.lot_number = B.lot_number);
  148.    
  149.     /*
  150.      * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
  151.      */
  152.     INSERT INTO tt_in_product_balance_summary_stock
  153.             (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
  154.     SELECT  pSessionId, A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
  155.             B.base_uom_id, SUM(B.qty_realization)
  156.     FROM    in_inventory A, in_inventory_item B
  157.     WHERE   A.inventory_id = B.inventory_id
  158.     AND     A.inventory_id = vReturnNoteId
  159.     GROUP BY A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
  160.        
  161.     --Update product balance id jika ada
  162.     --added by erl 19 nov 2020, terkait rrs di erp
  163.     UPDATE tt_in_product_balance_summary_stock A SET product_balance_id = B.product_balance_id
  164.     FROM in_product_balance B, in_inventory_item C
  165.     WHERE A.session_id = pSessionId AND
  166.           B.tenant_id = A.tenant_id AND
  167.           B.product_id = A.product_id AND
  168.           B.serial_number = C.serial_number AND
  169.           B.lot_number = C.lot_number AND
  170.           C.inventory_id = vReturnNoteId AND
  171.           B.product_id = C.product_id;
  172.     /*
  173.      * update product_balance_stock
  174.      */
  175.     UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId,
  176.         version = version + 1
  177.     FROM tt_in_product_balance_summary_stock A
  178.     WHERE A.session_id = pSessionId AND
  179.         A.inventory_id = vReturnNoteId AND
  180.         in_product_balance_stock.tenant_id = A.tenant_id AND
  181.         in_product_balance_stock.warehouse_id = A.warehouse_id AND
  182.         in_product_balance_stock.product_id = A.product_id AND
  183.         in_product_balance_stock.product_balance_id = A.product_balance_id AND
  184.         in_product_balance_stock.product_status = A.product_status;
  185.    
  186. --  /*
  187. --   * insert data in_product_balance_stock
  188. --   */
  189. --  INSERT INTO in_product_balance_stock
  190. --  (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
  191. --  "version", create_datetime, create_user_id, update_datetime, update_user_id)
  192. --  SELECT A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
  193. --          0, vDatetime, vUserId, vDatetime, vUserId
  194. --  FROM in_inventory A, in_inventory_item B
  195. --  WHERE A.inventory_id = B.inventory_id AND
  196. --      A.inventory_id = vReturnNoteId AND
  197. --      NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
  198. --                  WHERE C.tenant_id = A.tenant_id AND
  199. --                      C.warehouse_id = A.warehouse_from_id AND
  200. --                      C.product_id = B.product_id AND
  201. --                      C.product_balance_id = B.product_balance_id AND
  202. --                      C.product_status = B.product_status)
  203. --  GROUP BY A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
  204.    
  205.     /*
  206.      * insert data in_product_balance_stock
  207.      * added by erl 19 nov 2020, terkait rrs di erp
  208.      * kurang lebih sama seperti versi erp namun qty diambil dari tt saja
  209.      */
  210.     INSERT INTO in_product_balance_stock
  211.     (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
  212.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  213.     SELECT A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty),
  214.             0, vDatetime, vUserId, vDatetime, vUserId
  215.     FROM in_inventory A, tt_in_product_balance_summary_stock B
  216.     WHERE A.inventory_id = B.inventory_id AND
  217.         A.inventory_id = vReturnNoteId AND
  218.         NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
  219.                     WHERE C.tenant_id = A.tenant_id AND
  220.                         C.warehouse_id = A.warehouse_from_id AND
  221.                         C.product_id = B.product_id AND
  222.                         C.product_balance_id = B.product_balance_id AND
  223.                         C.product_status = B.product_status)
  224.     GROUP BY A.tenant_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
  225.        
  226. --  /*
  227. --   * insert data in_log_product_balance_stock
  228. --   */    
  229. --  INSERT INTO in_log_product_balance_stock
  230. --  (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  231. --   product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  232. --  "version", create_datetime, create_user_id, update_datetime, update_user_id)
  233. --  SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
  234. --      B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
  235. --      0, vDatetime, vUserId, vDatetime, vUserId
  236. --  FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
  237. --  WHERE A.inventory_id = vReturnNoteId AND
  238. --      A.inventory_id = B.inventory_id AND
  239. --      A.warehouse_to_id = C.warehouse_id
  240. --  GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
  241. --      B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
  242.  
  243.     /*
  244.      * insert data in_log_product_balance_stock
  245.      * added by erl 19 nov 2020, terkait rrs di erp
  246.      * kurang lebih sama seperti versi erp namun qty diambil dari tt saja
  247.      */    
  248.     INSERT INTO in_log_product_balance_stock
  249.     (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  250.      product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  251.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  252.     SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
  253.         B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty),
  254.         0, vDatetime, vUserId, vDatetime, vUserId
  255.     FROM in_inventory A, tt_in_product_balance_summary_stock B, m_warehouse_ou C
  256.     WHERE A.inventory_id = vReturnNoteId AND
  257.         A.inventory_id = B.inventory_id AND
  258.         A.warehouse_to_id = C.warehouse_id
  259.     GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
  260.         B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id
  261.     HAVING SUM(B.qty) <> 0;
  262.  
  263.     IF vRnFromRRS THEN
  264.         -- BILA RRN DARI RRS
  265.         UPDATE sl_request_return_sales_balance
  266.         SET flg_realization=vFlagYes,
  267.             update_datetime=vDatetime, update_user_id=vUserId, version=sl_request_return_sales_balance.version+1
  268.         FROM in_inventory A
  269.         WHERE A.inventory_id = vReturnNoteId AND sl_request_return_sales_balance.request_return_sales_id = A.ref_id;
  270.        
  271.         --update sl_do_item_balance_for_rrs agar bisa sesuai jumlah qty yg bisa dibuat RRS lg
  272.         UPDATE sl_do_item_balance_for_rrs A
  273.         SET qty_request = A.qty_request - (B.qty_request - B.qty_realization),
  274.             flg_requested = CASE WHEN (A.qty_request - (B.qty_request - B.qty_realization)) = A.qty_dlv THEN vFlagYes
  275.                             ELSE vFlagNo END,
  276.             update_datetime=vDatetime,
  277.             update_user_id=vUserId,
  278.             version=A.version+1
  279.         FROM (
  280.             SELECT D.ref_id, SUM(C.qty_realization) AS qty_realization, C.qty_request
  281.             FROM in_inventory B
  282.             INNER JOIN in_inventory_item C ON C.inventory_id = B.inventory_id AND C.ref_doc_type_id = vRrsDocTypeId
  283.             INNER JOIN sl_request_return_sales_item D ON D.request_return_sales_item_id = C.ref_item_id
  284.             WHERE B.inventory_id = vReturnNoteId
  285.             GROUP BY D.ref_id, C.qty_request
  286.         ) B
  287.         WHERE A.do_item_balance_for_rrs_id = B.ref_id;     
  288.        
  289.         -- UPDATE status in_balance_do_item jika qty realization 0
  290.         UPDATE in_balance_do_item
  291.         SET status_item = CASE WHEN (in_balance_do_item.qty_dlv -in_balance_do_item.qty_return) > 0 THEN vStatusRelease
  292.                             ELSE vStatusFinal END
  293.         FROM (
  294.             SELECT D.ref_id, SUM(C.qty_realization) AS qty_realization, C.qty_request, F.do_item_id
  295.             FROM in_inventory B
  296.             INNER JOIN in_inventory_item C ON C.inventory_id = B.inventory_id AND C.ref_doc_type_id = vRrsDocTypeId
  297.             INNER JOIN sl_request_return_sales_item D ON D.request_return_sales_item_id = C.ref_item_id
  298.             INNER JOIN sl_do_item_balance_for_rrs E ON D.ref_id = E.do_item_balance_for_rrs_id
  299.             INNER JOIN sl_do_item F ON F.ref_id = E.so_item_id
  300.             WHERE B.inventory_id = vReturnNoteId
  301.             GROUP BY D.ref_id, C.qty_request, F.do_item_id
  302.         ) A
  303.         WHERE in_balance_do_item.do_item_id = A.do_item_id
  304.         AND A.qty_realization = 0;
  305.              
  306.         /*
  307.          * insert data temporer tt_in_so_balance_item
  308.          */
  309.         INSERT INTO tt_in_so_balance_item
  310.                 (session_id, tenant_id, ou_id, doc_type_id,
  311.                 doc_no, doc_date, inventory_id, partner_id,
  312.                 inventory_item_id,
  313.                 so_id, do_id, do_item_id,
  314.                 request_return_sales_id, request_return_sales_item_id,
  315.                 qty_return, base_uom_id, remark,
  316.                 so_item_id, curr_code, price,
  317.                 flg_tax_amount, qty_so, qty_int_so,
  318.                 so_uom_id, tax_id, tax_percentage)
  319.         SELECT  pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
  320.                 A.doc_no, A.doc_date, A.inventory_id, BX.partner_id,
  321.                 B.inventory_item_id,
  322.                 COALESCE(E.ref_id, -99), COALESCE(D.do_id, -99), COALESCE(D.do_item_id, -99),
  323.                 B.ref_id, B.ref_item_id,
  324.                 SUM(B.qty_realization), B.base_uom_id, A.remark,
  325.                 COALESCE(CX.so_item_id, -99) AS so_item_id, C.curr_code, C.nett_sell_price,
  326.                 C.flg_tax_amount, C.qty_return, C.qty_return,
  327.                 C.uom_id, C.tax_id, C.tax_percentage
  328.         FROM in_inventory A
  329.         INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
  330.         INNER JOIN sl_request_return_sales BX ON  B.ref_id = BX.request_return_sales_id AND B.ref_doc_type_id = BX.doc_type_id
  331.         INNER JOIN sl_request_return_sales_item C ON B.ref_item_id = C.request_return_sales_item_id AND BX.request_return_sales_id = C.request_return_sales_id
  332.         LEFT JOIN sl_do_item_balance_for_rrs CX ON C.ref_id = CX.do_item_balance_for_rrs_id
  333.         LEFT JOIN sl_do_item D ON CX.so_item_id = D.ref_id
  334.         LEFT JOIN sl_do E ON D.do_id = E.do_id
  335.         WHERE A.inventory_id = vReturnNoteId
  336.         GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
  337.                 A.doc_no, A.doc_date, A.inventory_id, BX.partner_id,
  338.                 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
  339.                 B.base_uom_id, C.curr_code, C.nett_sell_price, D.do_id, D.do_item_id,
  340.                 C.flg_tax_amount, C.qty_return, C.qty_return, C.uom_id, C.tax_id, C.tax_percentage, CX.so_item_id,
  341.                 E.ref_id
  342.         HAVING SUM(B.qty_realization) <> 0;
  343.                
  344.         INSERT INTO tt_calculate_amount_so(
  345.               session_id, tenant_id, ou_id, partner_id, so_id,
  346.               ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date, ref_item_id,
  347.               gross_sell_price, qty, so_uom_id,
  348.               promo_percentage, flg_tax_amount, tax_id, tax_percentage, gross_amount, curr_code,
  349.               gross_after_disc,
  350.               total_gross_after_disc, total_promo_disc_amount,
  351.               regular_disc_percentage, reg_disc_amount, total_reg_disc_amount, gross_after_regular_disc,
  352.               tax_amount, nett_amount, tax_price, nett_price, price_so, item_amount, do_receipt_item_id,
  353.               add_disc_percentage, total_add_disc_amount, add_disc_amount, gross_after_add_disc,
  354.               request_return_sales_id, request_return_sales_item_id)
  355.         SELECT pSessionId, A.tenant_id, A.ou_id, A.partner_id, A.so_id,
  356.               A.inventory_id, A.doc_type_id, A.doc_no, A.doc_date, A.do_item_id,
  357.               B.gross_sell_price, A.qty_return, A.so_uom_id,
  358.               0, A.flg_tax_amount, A.tax_id, A.tax_percentage, (A.qty_return * B.gross_sell_price) AS gross_amount, A.curr_code,
  359.               (A.qty_return * B.gross_sell_price),
  360.               (A.qty_return * B.gross_sell_price), 0 AS total_promo_disc_amount,
  361.               0, 0, 0, (A.qty_return * B.nett_sell_price),
  362.               0, 0, 0, 0, B.nett_sell_price, 0, -99,
  363.               0, 0, 0, 0,
  364.               A.request_return_sales_id, A.request_return_sales_item_id
  365.         FROM tt_in_so_balance_item A
  366.         INNER JOIN sl_request_return_sales_item B ON B.request_return_sales_item_id = A.request_return_sales_item_id
  367.         WHERE A.session_id = pSessionId;
  368.        
  369.         UPDATE tt_calculate_amount_so A
  370.         SET so_balance_invoice_id = B.so_balance_invoice_id
  371.         FROM (
  372.             SELECT nextval('sl_so_balance_invoice_seq') AS so_balance_invoice_id, ref_item_id
  373.             FROM tt_calculate_amount_so
  374.             WHERE session_id = pSessionId
  375.             AND ref_id = vReturnNoteId
  376.             GROUP BY ref_item_id
  377.         ) B
  378.         WHERE A.session_id = pSessionId
  379.         AND A.ref_item_id = B.ref_item_id;
  380.        
  381.         UPDATE tt_calculate_amount_so
  382.         SET gross_after_add_disc = gross_after_regular_disc - add_disc_amount,
  383.             tax_amount = f_get_tax_amount_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, curr_code)),
  384.             nett_amount = f_get_dpp_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, f_get_tax_amount_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, curr_code))),
  385.             tax_price = f_get_tax_amount_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, curr_code)) / qty,
  386.             nett_price = f_get_dpp_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, f_get_tax_amount_after_discount((gross_after_regular_disc - add_disc_amount), flg_tax_amount, tax_percentage, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, curr_code))) / qty
  387.         WHERE session_id = pSessionId
  388.              AND ref_id = vReturnNoteId;
  389.        
  390.         UPDATE tt_calculate_amount_so
  391.         SET price_so = ROUND((nett_amount+total_promo_disc_amount+reg_disc_amount+add_disc_amount)/qty, f_get_digit_decimal_doc_curr(vReturnNoteDocTypeId, curr_code)),
  392.             item_amount= (nett_amount+total_promo_disc_amount+reg_disc_amount+add_disc_amount)
  393.         WHERE session_id = pSessionId
  394.              AND ref_id = vReturnNoteId;
  395.        
  396.         INSERT INTO sl_so_balance_invoice(
  397.             so_balance_invoice_id, tenant_id, ou_id, partner_id, so_id, ref_doc_type_id, ref_id,
  398.             ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id, curr_code,
  399.             price_so, item_amount, flg_invoice, invoice_id,
  400.             regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
  401.             "version", create_datetime, create_user_id, update_datetime, update_user_id)   
  402.         SELECT A.so_balance_invoice_id, A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.ref_doc_type_id, A.ref_id,
  403.             A.ref_doc_no, A.ref_doc_date, A.ref_item_id,
  404.             A.qty, A.so_uom_id, A.curr_code,
  405.             A.price_so, A.item_amount, vFlagInvoice, vEmptyId,
  406.             0, 0, 0, 0,
  407.             0, vDatetime, vUserId, vDatetime, vUserId
  408.         FROM tt_calculate_amount_so A
  409.         WHERE session_id = pSessionId
  410.               AND ref_id = vReturnNoteId;
  411.        
  412.         INSERT INTO sl_so_balance_invoice_ext_for_return_note(
  413.                 so_balance_invoice_id, flg_from_rrs,
  414.                 request_return_sales_id, request_return_sales_item_id,
  415.                 "version", create_datetime,  create_user_id,
  416.                 update_datetime, update_user_id)
  417.         SELECT A.so_balance_invoice_id, vFlagYes,
  418.                 A.request_return_sales_id, A.request_return_sales_item_id,
  419.                 0, vDatetime, vUserId, vDatetime, vUserId
  420.         FROM tt_calculate_amount_so A
  421.         WHERE session_id = pSessionId
  422.         AND ref_id = vReturnNoteId;
  423.              
  424.         INSERT INTO sl_so_balance_invoice_tax(
  425.             tenant_id, ou_id, partner_id, so_id, ref_doc_type_id, ref_id,
  426.             ref_item_id, tax_id, flg_amount, tax_percentage, curr_code, base_amount,
  427.             tax_amount, flg_invoice, invoice_id,
  428.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  429.         SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.ref_doc_type_id, A.ref_id,
  430.             A.ref_item_id, A.tax_id, B.flg_amount, A.tax_percentage, A.curr_code, A.item_amount,
  431.             A.tax_amount, vFlagInvoice, vEmptyId,
  432.             0, vDatetime, vUserId, vDatetime, vUserId
  433.         FROM tt_calculate_amount_so A
  434.         INNER JOIN m_tax B ON A.tax_id = B.tax_id
  435.         WHERE A.session_id = pSessionId
  436.               AND A.ref_id = vReturnNoteId;
  437.        
  438.     ELSE
  439.         -- BILA RRN BUKAN DARI RRS         
  440.         UPDATE sl_do_item_balance_for_rrs A
  441.         SET qty_request = A.qty_request + B.qty_realization,
  442.             flg_requested = CASE WHEN (A.qty_request + B.qty_realization) = A.qty_dlv THEN vFlagYes
  443.                             ELSE vFlagNo END,
  444.             update_datetime=vDatetime,
  445.             update_user_id=vUserId,
  446.             version=A.version+1
  447.         FROM (
  448.             SELECT B.ref_id, SUM(D.qty_realization) AS qty_realization
  449.             FROM sl_do_item B
  450.             JOIN in_balance_do_item C ON B.do_item_id = C.do_item_id
  451.             JOIN in_inventory_item D ON C.do_item_id = D.ref_item_id
  452.             WHERE D.inventory_id = vReturnNoteId
  453.             GROUP BY B.ref_id
  454.         ) B
  455.         WHERE A.so_item_id = B.ref_id;
  456.        
  457.         /*
  458.          * insert data temporer tt_in_so_balance_item
  459.          */
  460.         INSERT INTO tt_in_so_balance_item
  461.         (session_id, tenant_id, ou_id, doc_type_id,
  462.         doc_no, doc_date, inventory_id, partner_id,
  463.         inventory_item_id, so_id, do_id, do_item_id,
  464.         qty_return, base_uom_id, remark,
  465.         so_item_id, curr_code, price,
  466.         flg_tax_amount, qty_so, qty_int_so,
  467.         so_uom_id, tax_id, tax_percentage,
  468.         flg_disc, discount_percentage, discount_amount,
  469.         nett_sell_price)
  470.         SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
  471.                 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
  472.                 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
  473.                 SUM(B.qty_realization), B.base_uom_id, A.remark,
  474.                 D.so_item_id, D.curr_code, D.gross_sell_price,
  475.                 D.flg_tax_amount, D.qty_so, D.qty_int,
  476.                 D.so_uom_id, D.tax_id, D.tax_percentage,
  477.                 D.flg_disc, D.discount_percentage, D.discount_amount,
  478.                 D.nett_sell_price
  479.         FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
  480.         WHERE A.inventory_id = vReturnNoteId AND
  481.               A.inventory_id = B.inventory_id AND
  482.               B.ref_item_id = C.do_item_id AND
  483.               C.so_item_id = D.so_item_id AND
  484.               D.so_id = E.so_id
  485.         GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
  486.                 A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
  487.                 B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
  488.                 B.base_uom_id, D.so_item_id, D.curr_code, D.nett_sell_price, D.qty_so, D.qty_int, D.so_uom_id, D.tax_id;   
  489.                
  490.         UPDATE tt_in_so_balance_item A
  491.         SET so_balance_invoice_id = B.so_balance_invoice_id
  492.         FROM (
  493.             SELECT nextval('sl_so_balance_invoice_seq') AS so_balance_invoice_id, do_item_id
  494.             FROM tt_in_so_balance_item
  495.             WHERE session_id = pSessionId
  496.             AND inventory_id = vReturnNoteId
  497.             GROUP BY do_item_id
  498.         ) B
  499.         WHERE A.session_id = pSessionId
  500.         AND A.do_item_id = B.do_item_id;
  501.        
  502.        
  503.         /*
  504.          * buat data sl_so_balance_invoice
  505.          * HS, 23 Okt 2020
  506.          * Diskon selalu dianggap 0, dan nilai item amount dan price so nya sudah terpotong diskon
  507.          * dan nilainya selalu bulat
  508.          */
  509.         WITH insert_balance AS (
  510.             SELECT  
  511.                 -- after
  512.                 ROUND(A.price * SUM((A.qty_return * A.qty_so) / A.qty_int_so)) AS gross_amount_after,
  513.                 f_get_gross_amount_after_discount_with_rounding_mode(A.price, A.flg_disc, A.discount_percentage, A.discount_amount,
  514.                 (SUM((A.qty_return * A.qty_so) / A.qty_int_so)), f_get_digit_decimal_doc_curr(vSoDocTypeId, A.curr_code), vRoundingModeNonTax) AS gaad_after,
  515.                 f_get_tax_amount_after_discount_with_rounding_mode(
  516.                         f_get_gross_amount_after_discount_with_rounding_mode(A.price, A.flg_disc, A.discount_percentage, A.discount_amount,
  517.                         SUM((A.qty_return * A.qty_so) / A.qty_int_so), f_get_digit_decimal_doc_curr(vSoDocTypeId, A.curr_code), vRoundingModeNonTax),
  518.                         A.flg_tax_amount,
  519.                         A.tax_percentage,
  520.                         f_get_digit_decimal_doc_curr(vSoDocTypeId, A.curr_code),
  521.                         vRoundingModeNonTax)
  522.                 AS tax_amount_after_disc_after,    
  523.                 -- other data
  524.                 A.tenant_id, A.ou_id, A.partner_id, A.so_id,
  525.                 A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id,
  526.                 SUM((A.qty_return * A.qty_so) / A.qty_int_so) AS qty_dlv_so, A.so_uom_id,
  527.                 A.curr_code, A.nett_sell_price, A.flg_tax_amount, A.tax_percentage,
  528.                 A.so_balance_invoice_id
  529.             FROM tt_in_so_balance_item A
  530.             WHERE A.session_id = pSessionId
  531.             GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id,
  532.                 A.doc_no, A.doc_date, A.do_item_id, A.so_uom_id, A.curr_code, A.nett_sell_price,
  533.                 A.flg_tax_amount, A.tax_percentage, A.price, A.flg_disc, A.discount_percentage, A.discount_amount,
  534.                 A.so_balance_invoice_id
  535.         )
  536.         INSERT INTO sl_so_balance_invoice
  537.             (so_balance_invoice_id, tenant_id, ou_id, partner_id, so_id,
  538.             ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
  539.             curr_code, price_so, item_amount, flg_invoice, invoice_id,
  540.             regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
  541.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  542.         SELECT so_balance_invoice_id, tenant_id, ou_id, partner_id, so_id,
  543.             doc_type_id, inventory_id, doc_no, doc_date, do_item_id, qty_dlv_so, so_uom_id,
  544.             curr_code, nett_sell_price,
  545.             f_get_dpp_after_discount(gaad_after, flg_tax_amount, tax_amount_after_disc_after),
  546.             vFlagInvoice, vEmptyId,
  547.             0, 0, 0, 0,
  548.             0, vDatetime, vUserId, vDatetime, vUserId
  549.         FROM insert_balance;
  550.  
  551.         INSERT INTO sl_so_balance_invoice_ext_for_return_note(
  552.                 so_balance_invoice_id, flg_from_rrs,
  553.                 request_return_sales_id, request_return_sales_item_id,
  554.                 "version", create_datetime,  create_user_id,
  555.                 update_datetime, update_user_id)
  556.         SELECT A.so_balance_invoice_id, vFlagNo,
  557.                 vEmptyId, vEmptyId,
  558.                 0, vDatetime, vUserId, vDatetime, vUserId
  559.         FROM tt_in_so_balance_item A
  560.         WHERE session_id = pSessionId;
  561.        
  562.         /*
  563.          * buat data sl_so_balance_invoice_tax
  564.          * nilai base amount nya sudah terpotong diskon, dan di RN diskon selalu terisi 0
  565.          * dan nilainya selalu bulat
  566.          */        
  567.         WITH insert_balance_tax AS (
  568.             SELECT
  569.                 -- AFTER
  570.                 ROUND(A.price * SUM((A.qty_return * A.qty_so) / A.qty_int_so)) AS gross_amount_after,
  571.                 f_get_gross_amount_after_discount_with_rounding_mode(A.price, A.flg_disc, A.discount_percentage, A.discount_amount,
  572.                 (SUM((A.qty_return * A.qty_so) / A.qty_int_so)), f_get_digit_decimal_doc_curr(vSoDocTypeId, A.curr_code), vRoundingModeNonTax) AS gaad_after,
  573.                 f_get_tax_amount_after_discount_with_rounding_mode(
  574.                         f_get_gross_amount_after_discount_with_rounding_mode(A.price, A.flg_disc, A.discount_percentage, A.discount_amount,
  575.                         SUM((A.qty_return * A.qty_so) / A.qty_int_so), f_get_digit_decimal_doc_curr(vSoDocTypeId, A.curr_code), vRoundingModeNonTax),
  576.                         A.flg_tax_amount,
  577.                         A.tax_percentage,
  578.                         f_get_digit_decimal_doc_curr(vSoDocTypeId, A.curr_code),
  579.                         vRoundingModeNonTax)
  580.                 AS tax_amount_after_disc_after,
  581.                 -- OTHER
  582.                 A.tenant_id, A.ou_id, A.partner_id, A.so_id,
  583.                 A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id, B.flg_amount,
  584.                 A.tax_percentage, A.curr_code, A.flg_tax_amount
  585.             FROM tt_in_so_balance_item A, m_tax B
  586.             WHERE A.session_id = pSessionId AND
  587.                   A.tax_id = B.tax_id
  588.             GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id,
  589.                 A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id, B.flg_amount,
  590.                 A.tax_percentage, A.curr_code, A.flg_tax_amount, A.price, A.flg_disc, A.discount_percentage, A.discount_amount
  591.         )
  592.         INSERT INTO sl_so_balance_invoice_tax
  593.                 (tenant_id, ou_id, partner_id, so_id,
  594.                 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  595.                 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  596.                 "version", create_datetime, create_user_id, update_datetime, update_user_id)
  597.         SELECT tenant_id, ou_id, partner_id, so_id,
  598.             doc_type_id, inventory_id, do_item_id, tax_id, flg_amount,
  599.             tax_percentage, curr_code,
  600.             f_get_dpp_after_discount(gaad_after, flg_tax_amount, tax_amount_after_disc_after),
  601.             tax_amount_after_disc_after, vFlagInvoice, vEmptyId,
  602.             0, vDatetime, vUserId, vDatetime, vUserId
  603.         FROM insert_balance_tax;       
  604.     END IF;
  605.            
  606.     /*
  607.      * update qty return di data sl_so_balance_item
  608.      */        
  609.     UPDATE sl_so_balance_item SET qty_return =  sl_so_balance_item.qty_return + ((A.qty_return * A.qty_so) / A.qty_int_so), qty_return_int = sl_so_balance_item.qty_return_int + A.qty_return, update_datetime = vDatetime, update_user_id = vUserId
  610.     FROM tt_in_so_balance_item A
  611.     WHERE A.session_id = pSessionId AND
  612.         sl_so_balance_item.so_item_id = A.so_item_id;
  613.            
  614.     /*
  615.      * buat data sl_log_so_balance_item
  616.      */
  617.     INSERT INTO sl_log_so_balance_item
  618.     (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
  619.      qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  620.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  621.     SELECT A.tenant_id, A.so_id, A.so_item_id, A.doc_type_id, A.inventory_id, A.inventory_item_id,
  622.         ((A.qty_return * A.qty_so) / A.qty_int_so ), A.so_uom_id, A.qty_return, A.base_uom_id, A.remark,
  623.         0, vDatetime, vUserId, vDatetime, vUserId
  624.     FROM tt_in_so_balance_item A
  625.     WHERE A.session_id = pSessionId;
  626.        
  627.     UPDATE in_balance_do_item
  628.     SET status_item = CASE WHEN (in_balance_do_item.qty_dlv - (in_balance_do_item.qty_return + + A.qty_return)) > 0 THEN vStatusRelease
  629.                         ELSE vStatusFinal END,
  630.         qty_return = in_balance_do_item.qty_return + A.qty_return,
  631.         qty_return_int = in_balance_do_item.qty_return_int + A.qty_return
  632.     FROM tt_in_so_balance_item A
  633.     WHERE A.session_id = pSessionId AND
  634.         in_balance_do_item.do_item_id = A.do_item_id;      
  635.        
  636.     /*
  637.      * @author TKP, 9 Jun 2016
  638.      * Cek jika OU pada warehouse sama dengan OU pada dokumen maka nilai ou_bu_id dan ou_sub_bu_id =-99
  639.      * 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;
  640.      */
  641.     SELECT A.ou_from_id, B.ou_id INTO vOuId, vOuWarehouseId
  642.     FROM in_inventory A
  643.     INNER JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
  644.     WHERE A.inventory_id = vReturnNoteId;
  645.    
  646.     IF (vOuId <> vOuWarehouseId) THEN
  647.         SELECT f_get_ou_bu_structure(vOuWarehouseId) as ou_structure INTO result;
  648.         vOuStructureJournalItem := result.ou_structure;
  649.     ELSE
  650.         vOuStructureJournalItem := ROW(-99, -99, -99);
  651.     END IF;
  652.        
  653.     /*
  654.      * journal return note
  655.      * Debit Inventory = dari nilai COGS
  656.      * Credit HPP
  657.      */
  658.     /*
  659.      * membuat data transaksi jurnal :
  660.      * 1. buat admin
  661.      * 2. buat temlate jurnal
  662.      */    
  663.  
  664.     IF (vTotalQtyItem > 0) THEN
  665.         PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
  666.         FROM in_inventory A
  667.         WHERE A.inventory_id = vReturnNoteId;
  668.            
  669.         SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  670.        
  671.         INSERT INTO gl_journal_trx
  672.         (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  673.         ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  674.         ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  675.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  676.         SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
  677.             (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
  678.             A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
  679.             0, vDatetime, vUserId, vDatetime, vUserId
  680.         FROM in_inventory A
  681.         WHERE A.inventory_id = vReturnNoteId;
  682.        
  683.         INSERT INTO tt_journal_trx_item
  684.         (session_id, tenant_id, journal_trx_id, line_no,
  685.         ref_doc_type_id, ref_id,
  686.         partner_id, product_id, cashbank_id, ou_rc_id,
  687.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  688.         coa_id, curr_code, qty, uom_id,
  689.         amount, journal_date, type_rate,
  690.         numerator_rate, denominator_rate, journal_desc, remark)
  691.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  692.             A.doc_type_id, B.inventory_item_id,
  693.             A.partner_id, B.product_id, vEmptyId, vEmptyId,
  694.             vEmptyId, vSignDebit, vProductCOA, vEmptyId,
  695.             f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
  696.             0 , A.doc_date, vTypeRate,
  697.             1, 1, 'PRODUCT_STOCK', B.remark
  698.         FROM in_inventory A, in_inventory_item B
  699.         WHERE A.inventory_id = vReturnNoteId AND
  700.             A.inventory_id = B.inventory_id;
  701.    
  702.            
  703.     /* NK, 1 Feb 2014, journal HPP tidak perlu break down sampai ke product    
  704.         INSERT INTO tt_journal_trx_item
  705.         (session_id, tenant_id, journal_trx_id, line_no,
  706.         ref_doc_type_id, ref_id,
  707.         partner_id, product_id, cashbank_id, ou_rc_id,
  708.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  709.         coa_id, curr_code, qty, uom_id,
  710.         amount, journal_date, type_rate,
  711.         numerator_rate, denominator_rate, journal_desc, remark)
  712.         SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  713.             A.doc_type_id, B.inventory_item_id,
  714.             A.partner_id, B.product_id, vEmptyId, vEmptyId,
  715.             vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  716.             f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
  717.             0 , A.doc_date, vTypeRate,
  718.             1, 1, 'HPP', B.remark
  719.         FROM in_inventory A, in_inventory_item B
  720.         WHERE A.inventory_id = vReturnNoteId AND
  721.             A.inventory_id = B.inventory_id;
  722.     */     
  723.     /*     
  724.         INSERT INTO tt_journal_trx_item
  725.         (session_id, tenant_id, journal_trx_id, line_no,
  726.         ref_doc_type_id, ref_id, ou_id, sub_ou_id,
  727.         partner_id, product_id, cashbank_id, ou_rc_id,
  728.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  729.         coa_id, curr_code, qty, uom_id,
  730.         amount, journal_date, type_rate,
  731.         numerator_rate, denominator_rate, journal_desc, remark)
  732.         SELECT pSessionId, A.tenant_id, B.journal_trx_id, 1,
  733.             A.doc_type_id, A.inventory_item_id, B.ou_id, B.sub_ou_id,
  734.             vEmptyId, C.product_id, vEmptyId, vEmptyId,
  735.             vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  736.             f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, A.qty_return, A.base_uom_id,
  737.             f_get_amount_before_tax((A.qty_return * A.qty_so * A.price) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage,0),
  738.             A.doc_date, vTypeRate,
  739.             1, 1, 'HPP', A.remark
  740.         FROM tt_in_so_balance_item A, gl_journal_trx B, in_inventory_item C
  741.         WHERE A.session_id = pSessionId AND
  742.             B.journal_trx_id = vJournalTrxId AND
  743.             A.inventory_item_id = C.inventory_item_id;
  744.    
  745.         INSERT INTO gl_journal_trx_mapping
  746.         (tenant_id, journal_trx_id, line_no,
  747.         ref_doc_type_id, ref_id,
  748.         partner_id, product_id, cashbank_id, ou_rc_id,
  749.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  750.         coa_id, curr_code, qty, uom_id,
  751.         amount, journal_date, type_rate,
  752.         numerator_rate, denominator_rate, journal_desc, remark,
  753.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  754.         SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  755.             A.ref_doc_type_id, A.ref_id,
  756.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  757.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  758.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  759.             A.amount, A.journal_date, A.type_rate,
  760.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  761.             0, vDatetime, vUserId, vDatetime, vUserId
  762.         FROM tt_journal_trx_item A
  763.         WHERE A.session_id = pSessionId AND
  764.             A.journal_desc = 'HPP';
  765.                    
  766.     */
  767.         INSERT INTO gl_journal_trx_item
  768.         (tenant_id, journal_trx_id, line_no,
  769.         ref_doc_type_id, ref_id,
  770.         partner_id, product_id, cashbank_id, ou_rc_id,
  771.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  772.         coa_id, curr_code, qty, uom_id,
  773.         amount, journal_date, type_rate,
  774.         numerator_rate, denominator_rate, journal_desc, remark,
  775.         "version", create_datetime, create_user_id, update_datetime, update_user_id,
  776.         ou_branch_id, ou_sub_bu_id)
  777.         SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  778.             A.ref_doc_type_id, A.ref_id,
  779.             A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  780.             A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  781.             A.coa_id, A.curr_code, A.qty, A.uom_id,
  782.             A.amount, A.journal_date, A.type_rate,
  783.             A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  784.             0, vDatetime, vUserId, vDatetime, vUserId,
  785.             (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
  786.         FROM tt_journal_trx_item A
  787.         WHERE A.session_id = pSessionId AND
  788.             A.journal_desc = 'PRODUCT_STOCK';
  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.             vEmptyId, vEmptyId,
  801.             vEmptyId, vEmptyId, vEmptyId, vEmptyId,
  802.             vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  803.             f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
  804.             0, A.journal_date, A.type_rate,
  805.             1, 1, 'COGS', vEmptyValue,
  806.             0, vDatetime, vUserId, vDatetime, vUserId
  807.         FROM tt_journal_trx_item A
  808.         WHERE A.session_id = pSessionId
  809.         GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
  810.     END IF;
  811.  
  812.     DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
  813.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;     
  814.     DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;         
  815. END;
  816. $BODY$
  817.   LANGUAGE plpgsql VOLATILE
  818.   COST 100;
  819.   /
RAW Paste Data