tercnem

gl_posting_finish_goods_costing_journal

Sep 24th, 2020
1,641
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  * 2016119, WTC, saat hitung total nilai alokasi costing ke jurnal, perlu ada special handling tuk adj stock qty dan exchange, yaitu harus perhatikan sign journal
  3.  */
  4. /**
  5.  * Adrian, Feb 13, 2018
  6.  * Exclude Adjustment Stock Amount
  7.  */
  8. CREATE OR REPLACE FUNCTION gl_posting_finish_goods_costing_journal(bigint, character varying, bigint, bigint, character varying, character varying)
  9.   RETURNS void AS
  10. $BODY$
  11. DECLARE
  12.  
  13.     pTenantId           ALIAS FOR $1;
  14.     pSessionId          ALIAS FOR $2;
  15.     pOuId               ALIAS FOR $3;
  16.     pUserId             ALIAS FOR $4;
  17.     pYearMonth          ALIAS FOR $5;
  18.     pDatetime           ALIAS FOR $6;
  19.  
  20.     vStatusRelease          character varying(1);
  21.     vStatusDraft            character varying(1);
  22.     vEmptyValue             character varying(1);
  23.     vEmptyId                bigint;
  24.     vJournalProcessPeriode  character varying(10);
  25.     vLedgerCode             character varying(10);
  26.     vCurrGL                 character varying(5);
  27.     vRoundingAmount         integer;
  28.     vSignCredit             character varying(1);
  29.     vSignDebit              character varying(1);
  30.     vSystemCOA              character varying(10);
  31.    
  32. BEGIN
  33.    
  34.     vStatusRelease := 'R';
  35.     vStatusDraft := 'D';
  36.     vEmptyId := -99;
  37.     vEmptyValue := ' ';
  38.     vJournalProcessPeriode := 'MONTHLY';
  39.     vLedgerCode := 'COSTING.FG';
  40.     vSignCredit := 'C';
  41.     vSignDebit := 'D';
  42.     vSystemCOA := 'SYSTEM';
  43.  
  44.     DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
  45.     DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
  46.     DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
  47.     DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
  48.     DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;
  49.    
  50.     vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
  51.     vRoundingAmount := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);   
  52.    
  53.     /*
  54.      * WTC, 160212, menambahkan update jurnal untuk dokumen DO MLM, Return/Buyback, dan Exchange.
  55.      * 1.ambil data journal trx yang melakukan Debit / Credit Stok finish goods product
  56.      *   doc type : 502, 521, 413, 511, 311, 154, 354, 431, 526, 537, 313, 562, 563, 560
  57.      *   doc_desc : adj stok amount, adj stok amount balance, return note, adj stok qty, adj stok outlet qty,
  58.      *              Nota klaim, DO, DO Internal, Sales Invoice Konsinyasi, POS Shop, Return POS Shop, Pos Shop In Shop, Return POS Shop In Shop,
  59.      *              DO Receipt, Goods Transfer In Receipt Lost, DO MLM, Return SO MLM, Buyback SO MLM, Exchange MLM
  60.      * 2.berikan nilai tiap journal trx item sesuai dengan product, doc_type_id, ou_bu_id, ou_branch_id, ou_sub_bu_id di in_summary_monthly_product
  61.      * 3.update nilai amount, untuk memastikan jumlah amount di in_summary_monthly_product = jumlah nilai product di tt_gl_journal_costing
  62.      */
  63.      /**
  64.      * Adrian, Feb 13, 2018
  65.      * Exclude Adjustment Stock Amount
  66.      */
  67.     INSERT INTO tt_gl_journal_costing
  68.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  69.     journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
  70.     qty, uom_id, amount, total_amount_product, total_qty_product)
  71.     SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  72.         A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
  73.         B.qty, B.uom_id, 0, 0, 0
  74.     FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product D
  75.     WHERE A.tenant_id = pTenantId AND
  76.         A.ou_bu_id = pOuId AND
  77.         A.doc_type_id IN (502, 521, 413, 311, 154, 354, 431, 526, 537, 313, 562, 563, 560) AND 
  78.         A.doc_date = C.string_date AND
  79.         C.year_month_date = pYearMonth AND
  80.         A.journal_trx_id = B.journal_trx_id AND
  81.         B.product_id <> vEmptyId AND
  82.         B.product_id = D.product_id;
  83.        
  84.     INSERT INTO tt_gl_journal_costing
  85.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  86.     journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
  87.     qty, uom_id, amount, total_amount_product, total_qty_product)
  88.     SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  89.         A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
  90.         B.qty, B.uom_id, 0, 0, 0
  91.     FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product_assembly D
  92.     WHERE A.tenant_id = pTenantId AND
  93.         A.ou_bu_id = pOuId AND
  94.         A.doc_type_id IN (522, 502, 521, 413, 311, 154, 354, 431, 526, 537, 313, 562, 563, 560) AND
  95.         A.doc_date = C.string_date AND
  96.         C.year_month_date = pYearMonth AND
  97.         A.journal_trx_id = B.journal_trx_id AND
  98.         B.product_id <> vEmptyId AND
  99.         B.product_id = D.product_id;
  100.        
  101.     /* WTC, 160212, Menambahkan tipe dokumen exchange, yg perilaku nya mirip dengan adj stock
  102.      * Update amount dipisahkan menjadi 5 bagian, yaitu:
  103.         1. Tuk doc selain adj qty dan exchange
  104.         2. Tuk doc adj qty dan exchange yg qty > 0
  105.         3. Tuk doc adj qty dan exchange yg qty < 0
  106.        Hal ini perlu dilakukan, karena pada in_summary_monthly_amount bisa ada 2 nilai berbeda tuk doc adj qty dgn product_id yg sama,
  107.        yaitu yg mengurangi stock dan yg menambah stock, dimana yg mengurangi stock nilainya berdasarkan nilai cogs,
  108.        sedangkan yg menambah stock nilainya berdasarkan harga standar. Hal ini berlaku juga untuk exchange
  109.     */
  110.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  111.                                     total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*abs(A.gl_amount) ELSE abs(A.gl_amount) end,
  112.                                     total_qty_product = ABS(A.qty)
  113.     FROM in_summary_monthly_amount A
  114.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  115.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  116.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  117.         tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  118.         tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  119.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  120.         tt_gl_journal_costing.product_id = A.product_id AND
  121.         A.date_year_month = pYearMonth AND
  122.         A.doc_type_id NOT IN (521, 560) AND
  123.         A.qty <> 0;
  124.        
  125.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  126.                                     total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*abs(A.gl_amount) ELSE abs(A.gl_amount) end,
  127.                                     total_qty_product = ABS(A.qty)
  128.     FROM in_summary_monthly_amount A
  129.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  130.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  131.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  132.         tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  133.         tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  134.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  135.         tt_gl_journal_costing.product_id = A.product_id AND
  136.         A.date_year_month = pYearMonth AND
  137.         A.doc_type_id IN (521, 560) AND
  138.         A.qty > 0 AND
  139.         tt_gl_journal_costing.sign_journal = vSignDebit;
  140.  
  141.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  142.                                     total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*abs(A.gl_amount) ELSE abs(A.gl_amount) end,
  143.                                     total_qty_product = ABS(A.qty)
  144.     FROM in_summary_monthly_amount A
  145.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  146.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  147.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  148.         tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  149.         tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  150.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  151.         tt_gl_journal_costing.product_id = A.product_id AND
  152.         A.date_year_month = pYearMonth AND
  153.         A.doc_type_id IN (521, 560) AND
  154.         A.qty < 0 AND
  155.         tt_gl_journal_costing.sign_journal = vSignCredit;
  156.        
  157.     /*
  158.      * summary untuk menghitung total nilai alokasi hasil costing dari in_summary_monthly_amount
  159.      * selisih nilai alokasi dan total nilai dari in_summary_monthly_amount akan diberikan
  160.      * ke item journal trx yang paling maksimum id nya.
  161.      */    
  162.     INSERT INTO tt_gl_summary_amount_product
  163.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  164.      doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount)
  165.     SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  166.         A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product
  167.     FROM tt_gl_journal_costing A
  168.     WHERE A.session_id = pSessionId AND
  169.         A.doc_type_id NOT IN (521, 560)
  170.     GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  171.             A.doc_type_id, A.product_id, A.total_amount_product;
  172.      
  173.     INSERT INTO tt_gl_summary_amount_product
  174.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  175.      doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount, sign_journal)
  176.     SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  177.         A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product, A.sign_journal
  178.     FROM tt_gl_journal_costing A
  179.     WHERE A.session_id = pSessionId AND
  180.         A.doc_type_id IN (521, 560)
  181.     GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  182.             A.doc_type_id, A.product_id, A.total_amount_product, A.sign_journal;
  183.      
  184.     UPDATE tt_gl_journal_costing SET amount = amount + A.gl_amount - A.alloc_amount
  185.     FROM tt_gl_summary_amount_product A
  186.     WHERE A.session_id = pSessionId AND
  187.         tt_gl_journal_costing.session_id = A.session_id AND
  188.         tt_gl_journal_costing.journal_trx_item_id = A.journal_trx_item_id AND
  189.         A.alloc_amount <> A.gl_amount;
  190.    
  191.     /*
  192.      * update nilai buku untuk semua transaksi di gl_journal_trx_item
  193.      */
  194.     UPDATE gl_journal_trx_item SET numerator_rate = 1, denominator_rate = 1,
  195.                                 gl_amount = A.amount,
  196.                                 gl_curr_code = vCurrGL
  197.     FROM tt_gl_journal_costing A
  198.     WHERE A.session_id = pSessionId AND
  199.         gl_journal_trx_item.journal_trx_item_id = A.journal_trx_item_id;
  200.        
  201.     /*
  202.      * hitung summary journal per trx, nilai summary akan digunakan
  203.      *  1.untuk update nilai item journal mapping
  204.      *  2.untuk hitung selisih forex : hny untuk trx Nota Klaim  
  205.      */
  206.     INSERT INTO tt_gl_summary_journal
  207.     (session_id, ctgr_journal, journal_trx_id, sign_journal,
  208.     gl_amount, temp_amount)
  209.     SELECT A.session_id, 'ITEM', A.journal_trx_id, A.sign_journal,
  210.             SUM(A.amount), SUM(A.amount)
  211.     FROM tt_gl_journal_costing A
  212.     WHERE A.session_id = pSessionId
  213.     GROUP BY session_id, journal_trx_id, sign_journal;
  214.  
  215.     /*
  216.      * ambil data journal trx mapping untuk doc type id = Nota Klaim
  217.      * hitung rate dan nilai buku nya
  218.      * update langsung data gl_journal_trx_mapping nya
  219.      */
  220.     /*
  221.     INSERT INTO tt_gl_journal_trx_mapping
  222.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  223.     sign_journal, curr_code, amount,
  224.     journal_date, type_rate)   
  225.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  226.         D.sign_journal, D.curr_code, D.amount,
  227.         D.journal_date, D.type_rate
  228.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  229.     WHERE A.session_id = pSessionId AND
  230.         A.doc_type_id = 511 AND
  231.         A.journal_trx_id = D.journal_trx_id
  232.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  233.         D.sign_journal, D.curr_code, D.amount, D.journal_date, D.type_rate;
  234.    
  235.     INSERT INTO tt_gl_admin_journal_rate
  236.     (session_id, type_rate, journal_date, curr_code, gl_curr_code, data_exchange_rate) 
  237.     SELECT pSessionId, A.type_rate, A.journal_date, A.curr_code, vCurrGL, f_get_exchange_rate(pTenantId, A.type_rate, A.journal_date, A.curr_code, vCurrGL)
  238.     FROM (     
  239.         SELECT A.journal_date, A.type_rate, A.curr_code
  240.         FROM tt_gl_journal_trx_mapping A
  241.         WHERE A.session_id = pSessionId
  242.         GROUP BY journal_date, type_rate, curr_code) A;
  243.        
  244.     UPDATE tt_gl_journal_trx_mapping SET numerator_rate = (data_exchange_rate).numerator_rate,
  245.                                         denominator_rate = (data_exchange_rate).denominator_rate,
  246.                                         gl_amount = ROUND(amount * (data_exchange_rate).numerator_rate / (data_exchange_rate).denominator_rate, vRoundingAmount)                                       
  247.     FROM tt_gl_admin_journal_rate A
  248.     WHERE tt_gl_journal_trx_mapping.session_id = pSessionId AND
  249.         tt_gl_journal_trx_mapping.session_id = A.session_id AND
  250.         tt_gl_journal_trx_mapping.type_rate = A.type_rate AND
  251.         tt_gl_journal_trx_mapping.journal_date = A.journal_date AND
  252.         tt_gl_journal_trx_mapping.curr_code = A.curr_code;
  253.     */
  254.    
  255.     /*
  256.      * update nilai buku untuk item journal mapping Nota Klaim
  257.      */
  258.     /*
  259.     UPDATE gl_journal_trx_mapping SET numerator_rate = A.numerator_rate, denominator_rate = A.denominator_rate, gl_amount = A.gl_amount, gl_curr_code = vCurrGL
  260.     FROM tt_gl_journal_trx_mapping A
  261.     WHERE A.session_id = pSessionId AND
  262.         gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;
  263.     */
  264.    
  265.     /*
  266.      * buat data journal trx mapping untuk doc type yang bukan nota klaim
  267.      * dengan menghitung total nilai dari tt_gl_journal_costing
  268.      * hal ini dilakukan untuk memastikan jika terjadi kesalahan coding dalam membuat template journal
  269.      * maka nanti akan bisa muncul Forex, padahal transaksi selain Nota Klaim seharusnya tidak ada forex.
  270.      *
  271.      * Khusus untuk adj qty, amountnya harus memperhatikan sign journal nya, karena dlm 1 trx adj qty,
  272.      * bisa saja memiliki 2 jenis journal item dengan sign journal yg berbeda. Hal ini berlaku juga untuk exchange (WTC, 160212)
  273.      */
  274.     INSERT INTO tt_gl_journal_trx_mapping
  275.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  276.     sign_journal, curr_code, amount, gl_amount,
  277.     journal_date, type_rate)   
  278.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  279.         D.sign_journal, D.curr_code, D.amount, SUM(A.amount),
  280.         D.journal_date, D.type_rate
  281.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  282.     WHERE A.session_id = pSessionId AND
  283.         A.doc_type_id NOT IN (521,560) AND
  284.         A.journal_trx_id = D.journal_trx_id
  285.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  286.         D.sign_journal, D.curr_code, D.journal_date, D.type_rate;
  287.            
  288.     INSERT INTO tt_gl_journal_trx_mapping
  289.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  290.     sign_journal, curr_code, amount, gl_amount,
  291.     journal_date, type_rate)   
  292.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  293.         D.sign_journal, D.curr_code, D.amount, SUM(CASE WHEN A.sign_journal = vSignDebit THEN A.amount ELSE -1 * A.amount END),
  294.         D.journal_date, D.type_rate
  295.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  296.     WHERE A.session_id = pSessionId AND
  297.         A.doc_type_id IN (521,560) AND
  298.         A.journal_trx_id = D.journal_trx_id
  299.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  300.         D.sign_journal, D.curr_code, D.journal_date, D.type_rate;
  301.        
  302.     /*
  303.      * Update sign_journal menjadi D untuk doc adj qty yg amountnya < 0
  304.      */
  305.     UPDATE tt_gl_journal_trx_mapping SET sign_journal = vSignDebit, gl_amount = ABS(gl_amount)
  306.     WHERE doc_type_id IN (521,560) AND
  307.         gl_amount < 0 AND
  308.         session_id = pSessionId;
  309.            
  310.     /*
  311.      * update gl_journal_trx_mapping untuk nilai buku transaksi yang bukan Nota Klaim
  312.      */
  313.     UPDATE gl_journal_trx_mapping SET numerator_rate = 1, denominator_rate = 1,
  314.                                 gl_amount = A.gl_amount,
  315.                                 gl_curr_code = vCurrGL,
  316.                                 sign_journal = A.sign_journal
  317.     FROM tt_gl_journal_trx_mapping A
  318.     WHERE A.session_id = pSessionId AND
  319.         A.doc_type_id <> 511 AND
  320.         gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;
  321.                        
  322.     INSERT INTO tt_gl_summary_journal
  323.     (session_id, ctgr_journal, journal_trx_id, sign_journal, gl_amount, temp_amount)
  324.     SELECT A.session_id, 'MAPPING', A.journal_trx_id, A.sign_journal, SUM(A.gl_amount), SUM(A.gl_amount)
  325.     FROM tt_gl_journal_trx_mapping A
  326.     WHERE A.session_id = pSessionId
  327.     GROUP BY session_id, journal_trx_id, sign_journal;
  328.        
  329.     UPDATE tt_gl_summary_journal SET temp_amount = temp_amount * -1
  330.     WHERE sign_journal = vSignCredit AND
  331.         session_id = pSessionId;
  332.    
  333.     INSERT INTO tt_gl_summary_journal
  334.     (session_id, ctgr_journal, journal_trx_id, sign_journal, gl_amount, temp_amount)
  335.     SELECT A.session_id, 'ALL', A.journal_trx_id, vSignCredit, 0, SUM(A.temp_amount)
  336.     FROM tt_gl_summary_journal A
  337.     WHERE A.session_id = pSessionId
  338.     GROUP BY A.session_id, A.journal_trx_id;
  339.    
  340.     UPDATE tt_gl_summary_journal SET sign_journal = vSignDebit, temp_amount = temp_amount * -1
  341.     WHERE session_id = pSessionId AND
  342.         ctgr_journal = 'ALL' AND
  343.         temp_amount < 0;
  344.        
  345.     DELETE FROM gl_journal_trx_fx A
  346.     WHERE EXISTS (
  347.         SELECT 1
  348.         FROM tt_gl_summary_journal B
  349.         WHERE A.journal_trx_id = B.journal_trx_id AND
  350.               B.ctgr_journal = 'ALL' AND
  351.               B.session_id = pSessionId
  352.     );
  353.        
  354.     /*
  355.      * buat transaksi forex
  356.      * seharusnya cuma ada untuk transaksi Nota Klaim
  357.      * WTC, 160628, khusus untuk transaksi Nota Klaim, selisih nilai dimasukkan ke COA HPP.
  358.      *     Selain nota klaim, dimasukkan sebagai biaya selisih kurs.
  359.      */
  360.     INSERT INTO gl_journal_trx_fx
  361.     (tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id,  
  362.     partner_id, product_id, cashbank_id, ou_rc_id, segmen_id,
  363.     sign_journal, flg_source_coa, activity_gl_id, coa_id,
  364.     curr_code, amount, journal_date, type_rate,
  365.     numerator_rate, denominator_rate, gl_curr_code, gl_amount,  
  366.     journal_desc, remark,
  367.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  368.     SELECT B.tenant_id, A.journal_trx_id, 1, vEmptyId, vEmptyId,
  369.         vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
  370.         A.sign_journal, vSystemCOA, vEmptyId, f_get_system_coa_by_group_coa(B.tenant_id, 'BiayaSelisihKurs'),
  371.         vCurrGL, A.temp_amount, B.doc_date, vEmptyValue,
  372.         1, 1, vCurrGL, A.temp_amount,
  373.         'FX', 'Forex ' || C.doc_desc || ' ' || B.doc_date,
  374.         0, pDatetime, pUserId, pDatetime, pUserId
  375.     FROM tt_gl_summary_journal A,
  376.         gl_journal_trx B, m_document C
  377.     WHERE A.session_id = pSessionId AND
  378.         A.journal_trx_id = B.journal_trx_id AND
  379.         B.doc_type_id = C.doc_type_id AND
  380.         A.ctgr_journal = 'ALL' AND
  381.         A.temp_amount <> 0 AND
  382.         B.doc_type_id NOT IN (511, 560);
  383.  
  384.     -- WTC, 160628, khusus untuk transaksi Nota Klaim, selisih nilai dimasukkan ke COA HPP
  385.     INSERT INTO gl_journal_trx_fx
  386.     (tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id,  
  387.     partner_id, product_id, cashbank_id, ou_rc_id, segmen_id,
  388.     sign_journal, flg_source_coa, activity_gl_id, coa_id,
  389.     curr_code, amount, journal_date, type_rate,
  390.     numerator_rate, denominator_rate, gl_curr_code, gl_amount,  
  391.     journal_desc, remark,
  392.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  393.     SELECT B.tenant_id, A.journal_trx_id, 1, vEmptyId, vEmptyId,
  394.         vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
  395.         A.sign_journal, vSystemCOA, vEmptyId, f_get_system_coa_by_group_coa(B.tenant_id, 'HargaPokokPenjualan'),
  396.         vCurrGL, A.temp_amount, B.doc_date, vEmptyValue,
  397.         1, 1, vCurrGL, A.temp_amount,
  398.         'COGS', 'Selisih COGS atas Nota Klaim ' || C.doc_desc || ' ' || B.doc_date,
  399.         0, pDatetime, pUserId, pDatetime, pUserId
  400.     FROM tt_gl_summary_journal A,
  401.         gl_journal_trx B, m_document C
  402.     WHERE A.session_id = pSessionId AND
  403.         A.journal_trx_id = B.journal_trx_id AND
  404.         B.doc_type_id = C.doc_type_id AND
  405.         A.ctgr_journal = 'ALL' AND
  406.         A.temp_amount <> 0 AND
  407.         B.doc_type_id IN (560);
  408.        
  409.     /*
  410.      * data rate dari transaksi klaim
  411.      */    
  412.     INSERT INTO gl_admin_journal_rate
  413.     (tenant_id, journal_year_month, process_datetime, process_user_id,  
  414.     journal_date, type_rate, curr_code, gl_curr_code, numerator_rate, denominator_rate)
  415.     SELECT pTenantId, pYearMonth, pDatetime, pUserId,
  416.         journal_date, type_rate, curr_code, gl_curr_code, (data_exchange_rate).numerator_rate, (data_exchange_rate).denominator_rate
  417.     FROM tt_gl_admin_journal_rate
  418.     WHERE session_id = pSessionId AND
  419.         curr_code <> gl_curr_code;
  420.            
  421.     UPDATE gl_admin_journal_trx SET process_datetime = pDatetime, process_user_id = pUserId
  422.     WHERE tenant_id = pTenantId AND
  423.         ou_id = pOuId AND
  424.         journal_process_periode = vJournalProcessPeriode AND
  425.         ledger_code = vLedgerCode AND
  426.         journal_year_month = pYearMonth;
  427.        
  428.     UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
  429.     FROM (SELECT journal_trx_id
  430.             FROM tt_gl_journal_costing A
  431.             WHERE A.session_id = pSessionId
  432.             GROUP BY journal_trx_id) A
  433.     WHERE gl_journal_trx.journal_trx_id = A.journal_trx_id;
  434.    
  435.     -- update status document khusus untuk document cost allocation to product (528)
  436.     UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
  437.     WHERE doc_type_id IN (528,522)
  438.     AND SUBSTRING(doc_date, 1, 6) = pYearMonth;
  439.    
  440.     DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
  441.     DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
  442.     DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
  443.     DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
  444.     DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;
  445.    
  446. END;
  447. $BODY$
  448.   LANGUAGE plpgsql VOLATILE
  449.   COST 100;
  450.   /
RAW Paste Data