abirama62

gl_posting_finish_goods_costing_journal

Mar 17th, 2021 (edited)
364
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- FUNCTION: public.gl_posting_finish_goods_costing_journal(bigint, character varying, bigint, bigint, character varying, character varying)
  2.  
  3. -- DROP FUNCTION public.gl_posting_finish_goods_costing_journal(bigint, character varying, bigint, bigint, character varying, character varying);
  4.  
  5. CREATE OR REPLACE FUNCTION public.gl_posting_finish_goods_costing_journal(
  6.     bigint,
  7.     character varying,
  8.     bigint,
  9.     bigint,
  10.     character varying,
  11.     character varying)
  12.     RETURNS void
  13.     LANGUAGE 'plpgsql'
  14.     COST 100
  15.     VOLATILE PARALLEL UNSAFE
  16. AS $BODY$
  17. DECLARE
  18.  
  19.     pTenantId           ALIAS FOR $1;
  20.     pSessionId          ALIAS FOR $2;
  21.     pOuId               ALIAS FOR $3;
  22.     pUserId             ALIAS FOR $4;
  23.     pYearMonth          ALIAS FOR $5;
  24.     pDatetime           ALIAS FOR $6;
  25.  
  26.     vStatusRelease          character varying(1);
  27.     vStatusDraft            character varying(1);
  28.     vEmptyValue             character varying(1);
  29.     vEmptyId                bigint;
  30.     vJournalProcessPeriode  character varying(10);
  31.     vLedgerCode             character varying(10);
  32.     vCurrGL                 character varying(5);
  33.     vRoundingAmount         integer;
  34.     vSignCredit             character varying(1);
  35.     vSignDebit              character varying(1);
  36.     vSystemCOA              character varying(10);
  37.    
  38.     vCoaIdForCnFx           bigint := -99;
  39.     vSystemConfigCodeCoaCN  character varying := 'main.sub.acc.coa.journal.fx.monthly.posting.cn';
  40. BEGIN
  41.    
  42.     vStatusRelease := 'R';
  43.     vStatusDraft := 'D';
  44.     vEmptyId := -99;
  45.     vEmptyValue := ' ';
  46.     vJournalProcessPeriode := 'MONTHLY';
  47.     vLedgerCode := 'COSTING.FG';
  48.     vSignCredit := 'C';
  49.     vSignDebit := 'D';
  50.     vSystemCOA := 'SYSTEM';
  51.  
  52.     DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
  53.     DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
  54.     DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
  55.     DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
  56.     DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;
  57.    
  58.     vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
  59.     vRoundingAmount := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);   
  60.    
  61.     SELECT coa_id INTO vCoaIdForCnFx
  62.     FROM m_coa
  63.     WHERE tenant_id = pTenantId AND
  64.           main_acc ||'.'||sub_acc = f_get_value_system_config_by_param_code(pTenantId,vSystemConfigCodeCoaCN);
  65.          
  66.     /*
  67.      * 1.ambil data journal trx yang melakukan Debit / Credit Stok finish goods product
  68.      *   doc type : 502, 521, 413, 511, 311, 154, 354, 431, 526, 533, 535, 536, 537
  69.      *   doc_desc : return note, adj stok qty, adj stok outlet qty,
  70.      *              Nota klaim, DO, DO Internal, Sales Invoice Konsinyasi, POS Shop, Return POS Shop, Pos Shop In Shop, Return POS Shop In Shop,
  71.      *              DO Receipt, Goods Transfer Out, Goods Transfer In, Goods transfer in receipt, Goods transfer in receipt lost
  72.      * 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
  73.      * 3.update nilai amount, untuk memastikan jumlah amount di in_summary_monthly_product = jumlah nilai product di tt_gl_journal_costing
  74.      *
  75.      * WTC, 20161219, adjustment stock amount tidak perlu diset lagi dari hasil proses costing, melainkan diset saja status nya menjadi R
  76.      */
  77.     INSERT INTO tt_gl_journal_costing
  78.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  79.     journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
  80.     qty, uom_id, amount, total_amount_product, total_qty_product)
  81.     SELECT pSessionId, A.tenant_id, A.ou_bu_id,
  82.         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,
  83.         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,
  84.         A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
  85.         B.qty, B.uom_id, 0, 0, 0
  86.     FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product D
  87.     WHERE A.tenant_id = pTenantId AND
  88.         A.ou_bu_id = pOuId AND
  89.         A.doc_type_id IN (502, 521, 413, 511, 311, 154, 354, 431, 526, 533, 535, 536, 537) AND
  90.         A.doc_date = C.string_date AND
  91.         C.year_month_date = pYearMonth AND
  92.         A.journal_trx_id = B.journal_trx_id AND
  93.         B.product_id <> vEmptyId AND
  94.         B.product_id = D.product_id;
  95.        
  96.     INSERT INTO tt_gl_journal_costing
  97.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  98.     journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
  99.     qty, uom_id, amount, total_amount_product, total_qty_product)
  100.     SELECT pSessionId, A.tenant_id, A.ou_bu_id,
  101.         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,
  102.         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,
  103.         A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
  104.         B.qty, B.uom_id, 0, 0, 0
  105.     FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_finish_goods_product_assembly D
  106.     WHERE A.tenant_id = pTenantId AND
  107.         A.ou_bu_id = pOuId AND
  108.         A.doc_type_id IN (522, 502, 521, 413, 511, 311, 154, 354, 431, 526, 533, 535, 536, 537) AND
  109.         A.doc_date = C.string_date AND
  110.         C.year_month_date = pYearMonth AND
  111.         A.journal_trx_id = B.journal_trx_id AND
  112.         B.product_id <> vEmptyId AND
  113.         B.product_id = D.product_id;
  114.        
  115.     /* Update amount dipisahkan menjadi 3 bagian, yaitu:
  116.         1. Tuk doc selain adj qty
  117.         2. Tuk doc adj qty yg qty > 0
  118.         3. Tuk doc adj qty yg qty < 0
  119.        Hal ini perlu dilakukan, karena pada in_summary_monthly_amount bisa ada 2 nilai berbeda tuk doc adj qty dgn product_id yg sama,
  120.        yaitu yg mengurangi stock dan yg menambah stock, dimana yg mengurangi stock nilainya berdasarkan nilai cogs,
  121.        sedangkan yg menambah stock nilainya berdasarkan harga standar/
  122.     */
  123.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  124.                                     total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*ABS(A.gl_amount) ELSE ABS(A.gl_amount) END,
  125.                                     total_qty_product = ABS(A.qty)
  126.     FROM in_summary_monthly_amount A
  127.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  128.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  129.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  130.         --tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  131.         --tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  132.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  133.         tt_gl_journal_costing.product_id = A.product_id AND
  134.         A.date_year_month = pYearMonth AND
  135.         A.qty <> 0 AND
  136.         A.doc_type_id <> 521;
  137.        
  138.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  139.                                     total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*ABS(A.gl_amount) ELSE ABS(A.gl_amount) END,
  140.                                     total_qty_product = ABS(A.qty)
  141.     FROM in_summary_monthly_amount A
  142.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  143.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  144.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  145.         --tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  146.         --tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  147.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  148.         tt_gl_journal_costing.product_id = A.product_id AND
  149.         A.date_year_month = pYearMonth AND
  150.         A.doc_type_id = 521 AND
  151.         A.qty > 0 AND
  152.         tt_gl_journal_costing.sign_journal = vSignDebit;
  153.  
  154.     UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
  155.                                     total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*ABS(A.gl_amount) ELSE ABS(A.gl_amount) END,
  156.                                     total_qty_product = ABS(A.qty)
  157.     FROM in_summary_monthly_amount A
  158.     WHERE  tt_gl_journal_costing.session_id = pSessionId AND
  159.         tt_gl_journal_costing.tenant_id = A.tenant_id AND
  160.         tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
  161.         --tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
  162.         --tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
  163.         tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
  164.         tt_gl_journal_costing.product_id = A.product_id AND
  165.         A.date_year_month = pYearMonth AND
  166.         A.doc_type_id = 521 AND
  167.         A.qty < 0 AND
  168.         tt_gl_journal_costing.sign_journal = vSignCredit;
  169.        
  170.     /*
  171.      * summary untuk menghitung total nilai alokasi hasil costing dari in_summary_monthly_amount
  172.      * selisih nilai alokasi dan total nilai dari in_summary_monthly_amount akan diberikan
  173.      * ke item journal trx yang paling maksimum id nya.
  174.      * Mod by WTC, 20161019, Khusus untuk dokumen adj stock qty, perlu memperhatikan sign journal nya juga
  175.      */    
  176.     INSERT INTO tt_gl_summary_amount_product
  177.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  178.      doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount)
  179.     SELECT A.session_id, A.tenant_id, A.ou_bu_id,vEmptyId, vEmptyId,
  180.         A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product
  181.     FROM tt_gl_journal_costing A
  182.     WHERE A.session_id = pSessionId AND
  183.         A.doc_type_id NOT IN (521)
  184.     GROUP BY A.session_id, A.tenant_id, A.ou_bu_id,
  185.             A.doc_type_id, A.product_id, A.total_amount_product;
  186.      
  187.     INSERT INTO tt_gl_summary_amount_product
  188.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  189.      doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount, sign_journal)
  190.     SELECT A.session_id, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
  191.         A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product, A.sign_journal
  192.     FROM tt_gl_journal_costing A
  193.     WHERE A.session_id = pSessionId AND
  194.         A.doc_type_id IN (521)
  195.     GROUP BY A.session_id, A.tenant_id, A.ou_bu_id,
  196.             A.doc_type_id, A.product_id, A.total_amount_product, A.sign_journal;
  197.      
  198.     UPDATE tt_gl_journal_costing SET amount = amount + A.gl_amount - A.alloc_amount
  199.     FROM tt_gl_summary_amount_product A
  200.     WHERE A.session_id = pSessionId AND
  201.         tt_gl_journal_costing.session_id = A.session_id AND
  202.         tt_gl_journal_costing.journal_trx_item_id = A.journal_trx_item_id AND
  203.         A.alloc_amount <> A.gl_amount;
  204.    
  205.     /*
  206.      * update nilai buku untuk semua transaksi di gl_journal_trx_item
  207.      */
  208.     UPDATE gl_journal_trx_item SET numerator_rate = 1, denominator_rate = 1,
  209.                                 gl_amount = A.amount,
  210.                                 gl_curr_code = vCurrGL
  211.     FROM tt_gl_journal_costing A
  212.     WHERE A.session_id = pSessionId AND
  213.         gl_journal_trx_item.journal_trx_item_id = A.journal_trx_item_id;
  214.        
  215.     /*
  216.      * hitung summary journal per trx, nilai summary akan digunakan
  217.      *  1.untuk update nilai item journal mapping
  218.      *  2.untuk hitung selisih forex : hny untuk trx Nota Klaim  
  219.      */
  220.     INSERT INTO tt_gl_summary_journal
  221.     (session_id, ctgr_journal, journal_trx_id, sign_journal,
  222.     gl_amount, temp_amount)
  223.     SELECT A.session_id, 'ITEM', A.journal_trx_id, A.sign_journal,
  224.             SUM(A.amount), SUM(A.amount)
  225.     FROM tt_gl_journal_costing A
  226.     WHERE A.session_id = pSessionId
  227.     GROUP BY session_id, journal_trx_id, sign_journal;
  228.  
  229.     /*
  230.      * ambil data journal trx mapping untuk doc type id = Nota Klaim
  231.      * hitung rate dan nilai buku nya
  232.      * update langsung data gl_journal_trx_mapping nya
  233.      */
  234.     INSERT INTO tt_gl_journal_trx_mapping
  235.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  236.     sign_journal, curr_code, amount,
  237.     journal_date, type_rate)   
  238.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  239.         D.sign_journal, D.curr_code, D.amount,
  240.         D.journal_date, D.type_rate
  241.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  242.     WHERE A.session_id = pSessionId AND
  243.         A.doc_type_id = 511 AND
  244.         A.journal_trx_id = D.journal_trx_id
  245.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  246.         D.sign_journal, D.curr_code, D.amount, D.journal_date, D.type_rate;
  247.    
  248.     INSERT INTO tt_gl_admin_journal_rate
  249.     (session_id, type_rate, journal_date, curr_code, gl_curr_code, data_exchange_rate) 
  250.     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)
  251.     FROM (     
  252.         SELECT A.journal_date, A.type_rate, A.curr_code
  253.         FROM tt_gl_journal_trx_mapping A
  254.         WHERE A.session_id = pSessionId
  255.         GROUP BY journal_date, type_rate, curr_code) A;
  256.        
  257.     UPDATE tt_gl_journal_trx_mapping SET numerator_rate = (data_exchange_rate).numerator_rate,
  258.                                         denominator_rate = (data_exchange_rate).denominator_rate,
  259.                                         gl_amount = ROUND(amount * (data_exchange_rate).numerator_rate / (data_exchange_rate).denominator_rate, vRoundingAmount)                                       
  260.     FROM tt_gl_admin_journal_rate A
  261.     WHERE tt_gl_journal_trx_mapping.session_id = pSessionId AND
  262.         tt_gl_journal_trx_mapping.session_id = A.session_id AND
  263.         tt_gl_journal_trx_mapping.type_rate = A.type_rate AND
  264.         tt_gl_journal_trx_mapping.journal_date = A.journal_date AND
  265.         tt_gl_journal_trx_mapping.curr_code = A.curr_code;
  266.    
  267.     /*
  268.      * update nilai buku untuk item journal mapping Nota Klaim
  269.      */
  270.     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
  271.     FROM tt_gl_journal_trx_mapping A
  272.     WHERE A.session_id = pSessionId AND
  273.         gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;
  274.        
  275.     /*
  276.      * buat data journal trx mapping untuk doc type yang bukan nota klaim
  277.      * dengan menghitung total nilai dari tt_gl_journal_costing
  278.      * hal ini dilakukan untuk memastikan jika terjadi kesalahan coding dalam membuat template journal
  279.      * maka nanti akan bisa muncul Forex, padahal transaksi selain Nota Klaim seharusnya tidak ada forex.
  280.      *
  281.      * Khusus untuk adj qty, amountnya harus memperhatikan sign journal nya, karena dlm 1 trx adj qty,
  282.      * bisa saja memiliki 2 jenis journal item dengan sign journal yg berbeda.
  283.      *
  284.      * 20200914, Erl exclude juga untuk journal DO dan DOR, untuk DO dan DOR akan dibuat query terpisah -> terkait persediaan in transit
  285.      */
  286.     INSERT INTO tt_gl_journal_trx_mapping
  287.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  288.     sign_journal, curr_code, amount, gl_amount,
  289.     journal_date, type_rate)   
  290.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  291.         D.sign_journal, D.curr_code, D.amount, SUM(A.amount),
  292.         D.journal_date, D.type_rate
  293.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  294.     WHERE A.session_id = pSessionId AND
  295.         A.doc_type_id NOT IN (511,521, 311, 526, 536) AND
  296.         A.journal_trx_id = D.journal_trx_id
  297.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  298.         D.sign_journal, D.curr_code, D.journal_date, D.type_rate;
  299.            
  300.     INSERT INTO tt_gl_journal_trx_mapping
  301.     (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
  302.     sign_journal, curr_code, amount, gl_amount,
  303.     journal_date, type_rate)   
  304.     SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  305.         D.sign_journal, D.curr_code, D.amount, SUM(CASE WHEN A.sign_journal = vSignDebit THEN A.amount ELSE -1 * A.amount END),
  306.         D.journal_date, D.type_rate
  307.     FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
  308.     WHERE A.session_id = pSessionId AND
  309.         A.doc_type_id IN (521, 536) AND
  310.         A.journal_trx_id = D.journal_trx_id
  311.     GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
  312.         D.sign_journal, D.curr_code, D.journal_date, D.type_rate;
  313.        
  314.     /*
  315.      * 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)
  316.      * Update sign_journal menjadi C untuk doc adj qty yg amountnya > 0
  317.      */
  318.     UPDATE tt_gl_journal_trx_mapping SET sign_journal = vSignCredit
  319.     WHERE doc_type_id IN (521, 536) AND
  320.         gl_amount > 0 AND
  321.         session_id = pSessionId;
  322.        
  323.     /*
  324.      * Update sign_journal menjadi D untuk doc adj qty yg amountnya < 0
  325.      */
  326.     UPDATE tt_gl_journal_trx_mapping SET sign_journal = vSignDebit, gl_amount = ABS(gl_amount)
  327.     WHERE doc_type_id IN (521, 536) AND
  328.         gl_amount < 0 AND
  329.         session_id = pSessionId;
  330.            
  331.     /*
  332.      * update gl_journal_trx_mapping untuk nilai buku transaksi yang bukan Nota Klaim
  333.      */
  334.     UPDATE gl_journal_trx_mapping SET numerator_rate = 1, denominator_rate = 1,
  335.                                 gl_amount = A.gl_amount,
  336.                                 gl_curr_code = vCurrGL,
  337.                                 sign_journal = A.sign_journal
  338.     FROM tt_gl_journal_trx_mapping A
  339.     WHERE A.session_id = pSessionId AND
  340.         A.doc_type_id <> 511 AND
  341.         gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;
  342.    
  343.     -- 20200914, Erl query update journal mapping DO dan DOR dgn journal desc PERSEDIAAN_IN_TRANSIT    
  344.     UPDATE gl_journal_trx_mapping D
  345.     SET numerator_rate = 1, denominator_rate = 1,
  346.         gl_amount = A.amount,
  347.         gl_curr_code = vCurrGL
  348.     FROM tt_gl_journal_costing A
  349.     JOIN gl_journal_trx_item B ON A.journal_trx_item_id = B.journal_trx_item_id
  350.     WHERE A.session_id = pSessionId AND
  351.         A.doc_type_id IN (311,526) AND
  352.         A.journal_trx_id = D.journal_trx_id AND
  353.         B.ref_id = D.ref_id;
  354.  
  355.     -- 20200914, Erl query update journal mapping SI dgn journal desc PERSEDIAAN_IN_TRANSIT
  356.     UPDATE gl_journal_trx_mapping G
  357.     SET gl_amount = A.gl_amount
  358.     FROM (
  359.         SELECT A.journal_trx_id,
  360.             CASE WHEN D.sign_journal = vSignDebit THEN D.gl_amount ELSE D.gl_amount * -1 END +
  361.             CASE WHEN F.sign_journal = vSignDebit THEN COALESCE(F.gl_amount,0) ELSE COALESCE(F.gl_amount,0) * -1 END AS gl_amount,
  362.             D.ref_id
  363.         FROM gl_journal_trx A
  364.         JOIN dt_date AA ON A.doc_date = AA.string_date
  365.         INNER JOIN sl_invoice_temp_intransit B ON A.journal_trx_id = B.invoice_journal_trx_id
  366.         INNER JOIN gl_journal_trx C ON B.do_id = C.doc_id AND C.doc_type_id = 311 -- DO
  367.         INNER JOIN gl_journal_trx_mapping D ON C.journal_trx_id = D.journal_trx_id
  368.         LEFT JOIN gl_journal_trx E ON B.do_receipt_id = E.doc_id AND E.doc_type_id = 526 -- DOR
  369.         LEFT JOIN gl_journal_trx_mapping F ON E.journal_trx_id = F.journal_trx_id AND D.product_id = F.product_id
  370.         WHERE A.doc_type_id = 321 -- SI
  371.         AND AA.year_month_date = pYearMonth
  372.     ) A
  373.     WHERE A.journal_trx_id = G.journal_trx_id
  374.     AND G.journal_desc = 'PRODUCT_STOCK_IN_TRANSIT'
  375.     AND A.ref_id = G.ref_id;
  376.    
  377.     -- 20200914, Erl query update journal item SI dgn journal desc COGS
  378.     UPDATE gl_journal_trx_item G
  379.     SET gl_amount = A.gl_amount
  380.     FROM (
  381.         SELECT A.journal_trx_id, SUM(D.gl_amount) AS gl_amount
  382.         FROM gl_journal_trx A
  383.         JOIN dt_date C ON A.doc_date = C.string_date
  384.         JOIN gl_journal_trx_mapping D ON A.journal_trx_id = D.journal_trx_id
  385.         WHERE A.doc_type_id = 321 -- SI
  386.         AND C.year_month_date = pYearMonth
  387.         AND D.journal_desc = 'PRODUCT_STOCK_IN_TRANSIT'
  388.         GROUP BY A.journal_trx_id
  389.     ) A
  390.     WHERE A.journal_trx_id = G.journal_trx_id
  391.     AND G.journal_desc = 'COGS';
  392.        
  393.     INSERT INTO tt_gl_summary_journal
  394.     (session_id, ctgr_journal, journal_trx_id, sign_journal, gl_amount, temp_amount)
  395.     SELECT A.session_id, 'MAPPING', A.journal_trx_id, A.sign_journal, SUM(A.gl_amount), SUM(A.gl_amount)
  396.     FROM tt_gl_journal_trx_mapping A
  397.     WHERE A.session_id = pSessionId
  398.     GROUP BY session_id, journal_trx_id, sign_journal;
  399.        
  400.     UPDATE tt_gl_summary_journal SET temp_amount = temp_amount * -1
  401.     WHERE sign_journal = vSignCredit AND
  402.         session_id = pSessionId;
  403.    
  404.     INSERT INTO tt_gl_summary_journal
  405.     (session_id, ctgr_journal, journal_trx_id, sign_journal, gl_amount, temp_amount)
  406.     SELECT A.session_id, 'ALL', A.journal_trx_id, vSignCredit, 0, SUM(A.temp_amount)
  407.     FROM tt_gl_summary_journal A
  408.     WHERE A.session_id = pSessionId
  409.     GROUP BY A.session_id, A.journal_trx_id;
  410.    
  411.     UPDATE tt_gl_summary_journal SET sign_journal = vSignDebit, temp_amount = temp_amount * -1
  412.     WHERE session_id = pSessionId AND
  413.         ctgr_journal = 'ALL' AND
  414.         temp_amount < 0;
  415.        
  416.     DELETE FROM gl_journal_trx_fx A
  417.     WHERE EXISTS (
  418.         SELECT 1
  419.         FROM tt_gl_summary_journal B
  420.         WHERE A.journal_trx_id = B.journal_trx_id AND
  421.               B.ctgr_journal = 'ALL' AND
  422.               B.session_id = pSessionId
  423.     );
  424.        
  425.     /*
  426.      * buat transaksi forex
  427.      * yg bukan claim note, DO , DOR
  428.      */
  429.     INSERT INTO gl_journal_trx_fx
  430.     (tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id,  
  431.     partner_id, product_id, cashbank_id, ou_rc_id, segmen_id,
  432.     sign_journal, flg_source_coa, activity_gl_id, coa_id,
  433.     curr_code, amount, journal_date, type_rate,
  434.     numerator_rate, denominator_rate, gl_curr_code, gl_amount,  
  435.     journal_desc, remark,
  436.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  437.     SELECT B.tenant_id, A.journal_trx_id, 1, vEmptyId, vEmptyId,
  438.         vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
  439.         A.sign_journal, vSystemCOA, vEmptyId, f_get_system_coa_by_group_coa(B.tenant_id, 'BiayaSelisihKurs'),
  440.         vCurrGL, A.temp_amount, B.doc_date, vEmptyValue,
  441.         1, 1, vCurrGL, A.temp_amount,
  442.         'FX', 'Forex ' || C.doc_desc || ' ' || B.doc_date,
  443.         0, pDatetime, pUserId, pDatetime, pUserId
  444.     FROM tt_gl_summary_journal A,
  445.         gl_journal_trx B, m_document C
  446.     WHERE A.session_id = pSessionId AND
  447.         A.journal_trx_id = B.journal_trx_id AND
  448.         B.doc_type_id = C.doc_type_id AND
  449.         A.ctgr_journal = 'ALL' AND
  450.         A.temp_amount <> 0 AND
  451.         B.doc_type_id NOT IN (511, 311, 526);
  452.        
  453.     /*
  454.      * buat transaksi forex
  455.      * claim note --> ke coa COGS
  456.      */
  457.     INSERT INTO gl_journal_trx_fx
  458.     (tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id,  
  459.     partner_id, product_id, cashbank_id, ou_rc_id, segmen_id,
  460.     sign_journal, flg_source_coa, activity_gl_id, coa_id,
  461.     curr_code, amount, journal_date, type_rate,
  462.     numerator_rate, denominator_rate, gl_curr_code, gl_amount,  
  463.     journal_desc, remark,
  464.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  465.     SELECT B.tenant_id, A.journal_trx_id, 1, vEmptyId, vEmptyId,
  466.         vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
  467.         A.sign_journal, vSystemCOA, vEmptyId, vCoaIdForCnFx,
  468.         vCurrGL, A.temp_amount, B.doc_date, vEmptyValue,
  469.         1, 1, vCurrGL, A.temp_amount,
  470.         'FX', f_get_coa_main_account(vCoaIdForCnFx) ||' - '||
  471.              f_get_coa_sub_account(vCoaIdForCnFx) || ' : ' ||
  472.              f_get_coa_desc(vCoaIdForCnFx) ||' - '||
  473.              B.doc_no,
  474.         0, pDatetime, pUserId, pDatetime, pUserId
  475.     FROM tt_gl_summary_journal A,
  476.         gl_journal_trx B, m_document C
  477.     WHERE A.session_id = pSessionId AND
  478.         A.journal_trx_id = B.journal_trx_id AND
  479.         B.doc_type_id = C.doc_type_id AND
  480.         A.ctgr_journal = 'ALL' AND
  481.         A.temp_amount <> 0 AND
  482.         B.doc_type_id = 511;
  483.        
  484.     /*
  485.      * data rate dari transaksi klaim
  486.      */    
  487.     INSERT INTO gl_admin_journal_rate
  488.     (tenant_id, journal_year_month, process_datetime, process_user_id,  
  489.     journal_date, type_rate, curr_code, gl_curr_code, numerator_rate, denominator_rate)
  490.     SELECT pTenantId, pYearMonth, pDatetime, pUserId,
  491.         journal_date, type_rate, curr_code, gl_curr_code, (data_exchange_rate).numerator_rate, (data_exchange_rate).denominator_rate
  492.     FROM tt_gl_admin_journal_rate
  493.     WHERE session_id = pSessionId AND
  494.         curr_code <> gl_curr_code;
  495.            
  496.     UPDATE gl_admin_journal_trx SET process_datetime = pDatetime, process_user_id = pUserId
  497.     WHERE tenant_id = pTenantId AND
  498.         ou_id = pOuId AND
  499.         journal_process_periode = vJournalProcessPeriode AND
  500.         ledger_code = vLedgerCode AND
  501.         journal_year_month = pYearMonth;
  502.        
  503.     UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
  504.     FROM (SELECT journal_trx_id
  505.             FROM tt_gl_journal_costing A
  506.             WHERE A.session_id = pSessionId
  507.             GROUP BY journal_trx_id) A
  508.     WHERE gl_journal_trx.journal_trx_id = A.journal_trx_id;
  509.    
  510.     -- update status document khusus untuk document cost allocation to product (528), dan adjustment stok amount (522)
  511.     UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
  512.     WHERE doc_type_id IN (528,522)
  513.     AND SUBSTRING(doc_date, 1, 6) = pYearMonth;
  514.    
  515.     DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
  516.     DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
  517.     DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
  518.     DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
  519.     DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;
  520.    
  521. END;
  522. $BODY$;
  523.  
  524. ALTER FUNCTION public.gl_posting_finish_goods_costing_journal(bigint, character varying, bigint, bigint, character varying, character varying)
  525.     OWNER TO sts;
  526.  
RAW Paste Data