abirama62

func

Apr 29th, 2020
209
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  * WTC, 20161219:
  3.  *   1. Update sign journal untuk trx mapping adj stock qty (521), baik yg bernilai minus maupun plus (sebelumnya hanya yg bernilai minus)
  4.  *   2. Exclude adj stock amount dalam proses posting journal ini, melainkan set status doc nya saja menjadi R
  5.  */
  6. CREATE OR REPLACE FUNCTION gl_posting_finish_goods_costing_journal(bigint, character varying, bigint, bigint, character varying, character varying)
  7.   RETURNS void AS
  8. $BODY$
  9. DECLARE
  10.  
  11.     pTenantId           ALIAS FOR $1;
  12.     pSessionId          ALIAS FOR $2;
  13.     pOuId               ALIAS FOR $3;
  14.     pUserId             ALIAS FOR $4;
  15.     pYearMonth          ALIAS FOR $5;
  16.     pDatetime           ALIAS FOR $6;
  17.  
  18.     vStatusRelease          character varying(1);
  19.     vStatusDraft            character varying(1);
  20.     vEmptyValue             character varying(1);
  21.     vEmptyId                bigint;
  22.     vJournalProcessPeriode  character varying(10);
  23.     vLedgerCode             character varying(10);
  24.     vCurrGL                 character varying(5);
  25.     vRoundingAmount         integer;
  26.     vSignCredit             character varying(1);
  27.     vSignDebit              character varying(1);
  28.     vSystemCOA              character varying(10);
  29.    
  30. BEGIN
  31.    
  32.     vStatusRelease := 'R';
  33.     vStatusDraft := 'D';
  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.      * 1.ambil data journal trx yang melakukan Debit / Credit Stok finish goods product
  53.      *   doc type : 502, 521, 413, 511, 311, 154, 354, 431, 526, 537
  54.      *   doc_desc : return note, adj stok qty, adj stok outlet qty,
  55.      *              Nota klaim, DO, DO Internal, Sales Invoice Konsinyasi, POS Shop, Return POS Shop, Pos Shop In Shop, Return POS Shop In Shop,
  56.      *              DO Receipt, Goods transfer in receipt lost
  57.      * 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
  58.      * 3.update nilai amount, untuk memastikan jumlah amount di in_summary_monthly_product = jumlah nilai product di tt_gl_journal_costing
  59.      *
  60.      * WTC, 20161219, adjustment stock amount tidak perlu diset lagi dari hasil proses costing, melainkan diset saja status nya menjadi R
  61.      */
  62.     INSERT INTO tt_gl_journal_costing
  63.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  64.     journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
  65.     qty, uom_id, amount, total_amount_product, total_qty_product)
  66.     SELECT pSessionId, A.tenant_id, A.ou_bu_id,
  67.         CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_branch_id ELSE B.ou_branch_id END,
  68.         CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_sub_bu_id ELSE B.ou_sub_bu_id END,
  69.         A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
  70.         B.qty, B.uom_id, 0, 0, 0
  71.     FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product D
  72.     WHERE A.tenant_id = pTenantId AND
  73.         A.ou_bu_id = pOuId AND
  74.         A.doc_type_id IN (502, 521, 413, 511, 311, 154, 354, 431, 526, 537) AND
  75.         A.doc_date = C.string_date AND
  76.         C.year_month_date = pYearMonth AND
  77.         A.journal_trx_id = B.journal_trx_id AND
  78.         B.product_id <> vEmptyId AND
  79.         B.product_id = D.product_id;
  80.        
  81.     INSERT INTO tt_gl_journal_costing
  82.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  83.     journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
  84.     qty, uom_id, amount, total_amount_product, total_qty_product)
  85.     SELECT pSessionId, A.tenant_id, A.ou_bu_id,
  86.         CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_branch_id ELSE B.ou_branch_id END,
  87.         CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_sub_bu_id ELSE B.ou_sub_bu_id END,
  88.         A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
  89.         B.qty, B.uom_id, 0, 0, 0
  90.     FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product_assembly D
  91.     WHERE A.tenant_id = pTenantId AND
  92.         A.ou_bu_id = pOuId AND
  93.         A.doc_type_id IN (522, 502, 521, 413, 511, 311, 154, 354, 431, 526, 537) AND   
  94.         A.doc_date = C.string_date AND
  95.         C.year_month_date = pYearMonth AND
  96.         A.journal_trx_id = B.journal_trx_id AND
  97.         B.product_id <> vEmptyId AND
  98.         B.product_id = D.product_id;
  99.        
  100.     /* Update amount dipisahkan menjadi 3 bagian, yaitu:
  101.         1. Tuk doc selain adj qty
  102.         2. Tuk doc adj qty yg qty > 0
  103.         3. Tuk doc adj qty yg qty < 0
  104.        Hal ini perlu dilakukan, karena pada in_summary_monthly_amount bisa ada 2 nilai berbeda tuk doc adj qty dgn product_id yg sama,
  105.        yaitu yg mengurangi stock dan yg menambah stock, dimana yg mengurangi stock nilainya berdasarkan nilai cogs,
  106.        sedangkan yg menambah stock nilainya berdasarkan harga standar/
  107.     */
  108.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  109.                                     total_amount_product = ABS(A.gl_amount), total_qty_product = ABS(A.qty)
  110.     FROM in_summary_monthly_amount A
  111.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  112.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  113.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  114.         tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  115.         tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  116.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  117.         tt_gl_journal_costing.product_id = A.product_id AND
  118.         A.date_year_month = pYearMonth AND
  119.         A.doc_type_id <> 521;
  120.        
  121.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  122.                                     total_amount_product = ABS(A.gl_amount), total_qty_product = ABS(A.qty)
  123.     FROM in_summary_monthly_amount A
  124.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  125.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  126.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  127.         tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  128.         tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  129.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  130.         tt_gl_journal_costing.product_id = A.product_id AND
  131.         A.date_year_month = pYearMonth AND
  132.         A.doc_type_id = 521 AND
  133.         A.qty > 0 AND
  134.         tt_gl_journal_costing.sign_journal = vSignDebit;
  135.  
  136.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  137.                                     total_amount_product = ABS(A.gl_amount), total_qty_product = ABS(A.qty)
  138.     FROM in_summary_monthly_amount A
  139.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  140.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  141.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  142.         tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  143.         tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  144.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  145.         tt_gl_journal_costing.product_id = A.product_id AND
  146.         A.date_year_month = pYearMonth AND
  147.         A.doc_type_id = 521 AND
  148.         A.qty < 0 AND
  149.         tt_gl_journal_costing.sign_journal = vSignCredit;
  150.        
  151.     /*
  152.      * summary untuk menghitung total nilai alokasi hasil costing dari in_summary_monthly_amount
  153.      * selisih nilai alokasi dan total nilai dari in_summary_monthly_amount akan diberikan
  154.      * ke item journal trx yang paling maksimum id nya.
  155.      * Mod by WTC, 20161019, Khusus untuk dokumen adj stock qty, perlu memperhatikan sign journal nya juga
  156.      */    
  157.     INSERT INTO tt_gl_summary_amount_product
  158.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  159.      doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount)
  160.     SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  161.         A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product
  162.     FROM tt_gl_journal_costing A
  163.     WHERE A.session_id = pSessionId AND
  164.         A.doc_type_id NOT IN (521)
  165.     GROUP BY 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, A.total_amount_product;
  167.      
  168.     INSERT INTO tt_gl_summary_amount_product
  169.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  170.      doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount, sign_journal)
  171.     SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  172.         A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product, A.sign_journal
  173.     FROM tt_gl_journal_costing A
  174.     WHERE A.session_id = pSessionId AND
  175.         A.doc_type_id IN (521)
  176.     GROUP BY 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, A.total_amount_product, A.sign_journal;
  178.      
  179.     UPDATE tt_gl_journal_costing SET amount = amount + A.gl_amount - A.alloc_amount
  180.     FROM tt_gl_summary_amount_product A
  181.     WHERE A.session_id = pSessionId AND
  182.         tt_gl_journal_costing.session_id = A.session_id AND
  183.         tt_gl_journal_costing.journal_trx_item_id = A.journal_trx_item_id AND
  184.         A.alloc_amount <> A.gl_amount;
  185.    
  186.     /*
  187.      * update nilai buku untuk semua transaksi di gl_journal_trx_item
  188.      */
  189.     UPDATE gl_journal_trx_item SET numerator_rate = 1, denominator_rate = 1,
  190.                                 gl_amount = A.amount,
  191.                                 gl_curr_code = vCurrGL
  192.     FROM tt_gl_journal_costing A
  193.     WHERE A.session_id = pSessionId AND
  194.         gl_journal_trx_item.journal_trx_item_id = A.journal_trx_item_id;
  195.        
  196.     /*
  197.      * hitung summary journal per trx, nilai summary akan digunakan
  198.      *  1.untuk update nilai item journal mapping
  199.      *  2.untuk hitung selisih forex : hny untuk trx Nota Klaim  
  200.      */
  201.     INSERT INTO tt_gl_summary_journal
  202.     (session_id, ctgr_journal, journal_trx_id, sign_journal,
  203.     gl_amount, temp_amount)
  204.     SELECT A.session_id, 'ITEM', A.journal_trx_id, A.sign_journal,
  205.             SUM(A.amount), SUM(A.amount)
  206.     FROM tt_gl_journal_costing A
  207.     WHERE A.session_id = pSessionId
  208.     GROUP BY session_id, journal_trx_id, sign_journal;
  209.  
  210.     /*
  211.      * ambil data journal trx mapping untuk doc type id = Nota Klaim
  212.      * hitung rate dan nilai buku nya
  213.      * update langsung data gl_journal_trx_mapping nya
  214.      */
  215.     INSERT INTO tt_gl_journal_trx_mapping
  216.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  217.     sign_journal, curr_code, amount,
  218.     journal_date, type_rate)   
  219.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  220.         D.sign_journal, D.curr_code, D.amount,
  221.         D.journal_date, D.type_rate
  222.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  223.     WHERE A.session_id = pSessionId AND
  224.         A.doc_type_id = 511 AND
  225.         A.journal_trx_id = D.journal_trx_id
  226.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  227.         D.sign_journal, D.curr_code, D.amount, D.journal_date, D.type_rate;
  228.    
  229.     INSERT INTO tt_gl_admin_journal_rate
  230.     (session_id, type_rate, journal_date, curr_code, gl_curr_code, data_exchange_rate) 
  231.     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)
  232.     FROM (     
  233.         SELECT A.journal_date, A.type_rate, A.curr_code
  234.         FROM tt_gl_journal_trx_mapping A
  235.         WHERE A.session_id = pSessionId
  236.         GROUP BY journal_date, type_rate, curr_code) A;
  237.        
  238.     UPDATE tt_gl_journal_trx_mapping SET numerator_rate = (data_exchange_rate).numerator_rate,
  239.                                         denominator_rate = (data_exchange_rate).denominator_rate,
  240.                                         gl_amount = ROUND(amount * (data_exchange_rate).numerator_rate / (data_exchange_rate).denominator_rate, vRoundingAmount)                                       
  241.     FROM tt_gl_admin_journal_rate A
  242.     WHERE tt_gl_journal_trx_mapping.session_id = pSessionId AND
  243.         tt_gl_journal_trx_mapping.session_id = A.session_id AND
  244.         tt_gl_journal_trx_mapping.type_rate = A.type_rate AND
  245.         tt_gl_journal_trx_mapping.journal_date = A.journal_date AND
  246.         tt_gl_journal_trx_mapping.curr_code = A.curr_code;
  247.    
  248.     /*
  249.      * update nilai buku untuk item journal mapping Nota Klaim
  250.      */
  251.     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
  252.     FROM tt_gl_journal_trx_mapping A
  253.     WHERE A.session_id = pSessionId AND
  254.         gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;
  255.        
  256.     /*
  257.      * buat data journal trx mapping untuk doc type yang bukan nota klaim
  258.      * dengan menghitung total nilai dari tt_gl_journal_costing
  259.      * hal ini dilakukan untuk memastikan jika terjadi kesalahan coding dalam membuat template journal
  260.      * maka nanti akan bisa muncul Forex, padahal transaksi selain Nota Klaim seharusnya tidak ada forex.
  261.      *
  262.      * Khusus untuk adj qty, amountnya harus memperhatikan sign journal nya, karena dlm 1 trx adj qty,
  263.      * bisa saja memiliki 2 jenis journal item dengan sign journal yg berbeda.
  264.      */
  265.     INSERT INTO tt_gl_journal_trx_mapping
  266.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  267.     sign_journal, curr_code, amount, gl_amount,
  268.     journal_date, type_rate)   
  269.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  270.         D.sign_journal, D.curr_code, D.amount, SUM(A.amount),
  271.         D.journal_date, D.type_rate
  272.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  273.     WHERE A.session_id = pSessionId AND
  274.         A.doc_type_id NOT IN (511,521) AND
  275.         A.journal_trx_id = D.journal_trx_id
  276.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  277.         D.sign_journal, D.curr_code, D.journal_date, D.type_rate;
  278.            
  279.     INSERT INTO tt_gl_journal_trx_mapping
  280.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  281.     sign_journal, curr_code, amount, gl_amount,
  282.     journal_date, type_rate)   
  283.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  284.         D.sign_journal, D.curr_code, D.amount, SUM(CASE WHEN A.sign_journal = vSignDebit THEN A.amount ELSE -1 * A.amount END),
  285.         D.journal_date, D.type_rate
  286.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  287.     WHERE A.session_id = pSessionId AND
  288.         A.doc_type_id = 521 AND
  289.         A.journal_trx_id = D.journal_trx_id
  290.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  291.         D.sign_journal, D.curr_code, D.journal_date, D.type_rate;
  292.        
  293.     /*
  294.      * WTC, 170130, Harus update yg gl_amount nya positif dl. Jika terbalik, maka akibatnya semua akan terupdate sign_journal menjadi C (karena semua gl_amount sudah bernilai positif)
  295.      * Update sign_journal menjadi C untuk doc adj qty yg amountnya > 0
  296.      */
  297.     UPDATE tt_gl_journal_trx_mapping SET sign_journal = vSignCredit
  298.     WHERE doc_type_id = 521 AND
  299.         gl_amount > 0 AND
  300.         session_id = pSessionId;
  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 = 521 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.      */
  358.     INSERT INTO gl_journal_trx_fx
  359.     (tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id,  
  360.     partner_id, product_id, cashbank_id, ou_rc_id, segmen_id,
  361.     sign_journal, flg_source_coa, activity_gl_id, coa_id,
  362.     curr_code, amount, journal_date, type_rate,
  363.     numerator_rate, denominator_rate, gl_curr_code, gl_amount,  
  364.     journal_desc, remark,
  365.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  366.     SELECT B.tenant_id, A.journal_trx_id, 1, vEmptyId, vEmptyId,
  367.         vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
  368.         A.sign_journal, vSystemCOA, vEmptyId, f_get_system_coa_by_group_coa(B.tenant_id, 'BiayaSelisihKurs'),
  369.         vCurrGL, A.temp_amount, B.doc_date, vEmptyValue,
  370.         1, 1, vCurrGL, A.temp_amount,
  371.         'FX', 'Forex ' || C.doc_desc || ' ' || B.doc_date,
  372.         0, pDatetime, pUserId, pDatetime, pUserId
  373.     FROM tt_gl_summary_journal A,
  374.         gl_journal_trx B, m_document C
  375.     WHERE A.session_id = pSessionId AND
  376.         A.journal_trx_id = B.journal_trx_id AND
  377.         B.doc_type_id = C.doc_type_id AND
  378.         A.ctgr_journal = 'ALL' AND
  379.         A.temp_amount <> 0;
  380.        
  381.     /*
  382.      * data rate dari transaksi klaim
  383.      */    
  384.     INSERT INTO gl_admin_journal_rate
  385.     (tenant_id, journal_year_month, process_datetime, process_user_id,  
  386.     journal_date, type_rate, curr_code, gl_curr_code, numerator_rate, denominator_rate)
  387.     SELECT pTenantId, pYearMonth, pDatetime, pUserId,
  388.         journal_date, type_rate, curr_code, gl_curr_code, (data_exchange_rate).numerator_rate, (data_exchange_rate).denominator_rate
  389.     FROM tt_gl_admin_journal_rate
  390.     WHERE session_id = pSessionId AND
  391.         curr_code <> gl_curr_code;
  392.            
  393.     UPDATE gl_admin_journal_trx SET process_datetime = pDatetime, process_user_id = pUserId
  394.     WHERE tenant_id = pTenantId AND
  395.         ou_id = pOuId AND
  396.         journal_process_periode = vJournalProcessPeriode AND
  397.         ledger_code = vLedgerCode AND
  398.         journal_year_month = pYearMonth;
  399.        
  400.     UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
  401.     FROM (SELECT journal_trx_id
  402.             FROM tt_gl_journal_costing A
  403.             WHERE A.session_id = pSessionId
  404.             GROUP BY journal_trx_id) A
  405.     WHERE gl_journal_trx.journal_trx_id = A.journal_trx_id;
  406.    
  407.     -- update status document khusus untuk document cost allocation to product (528), dan adjustment stok amount (522)
  408.     UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
  409.     WHERE doc_type_id IN (528,522)
  410.     AND SUBSTRING(doc_date, 1, 6) = pYearMonth;
  411.    
  412.     DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
  413.     DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
  414.     DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
  415.     DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
  416.     DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;
  417.    
  418. END;
  419. $BODY$
  420.   LANGUAGE plpgsql VOLATILE
  421.   COST 100;
  422.   /
RAW Paste Data