tercnem

gl_posting_raw_materials_costing_journal

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