abirama62

in_process_costing_raw_materials

Jul 26th, 2021
1,121
116 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- WTC, 160212, menambahkan costing untuk tipe dokumen DO MLM, return/buyback, dan exchange
  2. -- WTC, 161113, saat summary untuk membuat saldo awal bulan berikutnya, ubah filter qty menjadi qty <> 0 (sblmnya qty > 0)
  3. -- WTC, 170418, nilai claim note diperhitungkan sebagai pengurang perhitungan COGS
  4. -- WTC, 170502, khusus untuk product tidak ada COGS, maka ambil nilainya dari master harga standar
  5. CREATE OR REPLACE FUNCTION in_process_costing_raw_materials(bigint, character varying, character varying, bigint, character varying, bigint)
  6.   RETURNS void AS
  7. $BODY$
  8. DECLARE
  9.  
  10.     pTenantId           ALIAS FOR $1;
  11.     pSessionId          ALIAS FOR $2;
  12.     pYearMonth          ALIAS FOR $3;
  13.     pOuId               ALIAS FOR $4;
  14.     pDatetime           ALIAS FOR $5;
  15.     pUserId             ALIAS FOR $6;
  16.  
  17.     vNextYearMonth          character varying(6);
  18.     vCurrGL                 character varying(5);
  19.     vFlagNo                 character varying(1);
  20.     vFlagYes                character varying(1);
  21.     vEmptyValue             character varying(1);
  22.     vEmptyId                bigint;
  23.     vDocTypeAwal            bigint;
  24.     vGroupProductFG         character varying(5);
  25.     vGroupProductRM         character varying(5);
  26.     vRoundingDigit          integer;
  27.     vTypeRate               character varying(5);
  28.     vStatusRelease          character varying(1);
  29.    
  30.     vGenerateAdjustmentStockAmount      character varying(1);
  31.     vParamGenerateAdjustmentStockAmount character varying := 'generate.adjustment.stock.amount.on.costing.raw.material';
  32.     vCountItemToGenerate                bigint;
  33.    
  34. BEGIN
  35.    
  36.     vFlagNo := 'N';
  37.     vFlagYes := 'Y';
  38.     vDocTypeAwal := -99;
  39.     vEmptyId := -99;
  40.     vEmptyValue := ' ';
  41.     vGroupProductFG := 'FG';
  42.     vGroupProductRM := 'RM';
  43.     vTypeRate := 'COM';
  44.     vStatusRelease := 'R';
  45.    
  46.     vCountItemToGenerate := 0;
  47.    
  48.     SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
  49.    
  50.     vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
  51.     vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
  52.     vGenerateAdjustmentStockAmount := f_get_value_system_config_by_param_code(pTenantId,vParamGenerateAdjustmentStockAmount);
  53.  
  54.     DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;
  55.    
  56.     DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;
  57.    
  58.     DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;
  59.    
  60.     DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;
  61.    
  62.     DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;
  63.    
  64.     DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;
  65.    
  66.     /*
  67.      * costing product hanya berdasarkan ou business unit ( artinya tbl cogs hanya ada ou_id )
  68.      */
  69.    
  70.     /*
  71.      * membuat summary nilai pembelian berdasarkan hasil jurnal
  72.      * untuk transaksi receive goods
  73.      * NK, 16 Feb 2015 : Pembelian Internal menghasilkan dokumen semua sesuai doc type Receive Goods
  74.      * Receive Goods : 111
  75.      */
  76.     INSERT INTO in_summary_monthly_amount
  77.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  78.     doc_type_id, product_id, base_uom_id, qty,
  79.     gl_curr_code, gl_amount, flg_amount,
  80.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  81.     SELECT pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  82.         A.doc_type_id, C.product_id, C.uom_id, SUM(C.qty),
  83.         C.gl_curr_code, SUM(CASE WHEN C.sign_journal = 'D' THEN C.gl_amount ELSE -1 * C.gl_amount END), 'PURCH_PRICE',
  84.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  85.     FROM gl_journal_trx A, dt_date B, gl_journal_trx_item C, m_document_journal D, vw_raw_materials_product E
  86.     WHERE A.tenant_id = pTenantId AND
  87.         A.ou_bu_id = pOuId AND
  88.         A.status_doc = vStatusRelease AND
  89.         A.doc_date = B.string_date AND
  90.         B.year_month_date = pYearMonth AND
  91.         A.journal_trx_id = C.journal_trx_id AND
  92.         A.doc_type_id = D.doc_type_id AND
  93.         D.ledger_code IN ('PURCH') AND
  94.         C.journal_desc = 'PRODUCT_STOCK' AND
  95.         A.doc_type_id IN (111) AND
  96.         C.product_id = E.product_id
  97.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, C.product_id, C.uom_id, C.gl_curr_code;
  98.  
  99.     /*
  100.      * ambil data receive goods dari in_summary_monthly_amount yang product Raw Materials
  101.      * NK, 16 Feb 2015 : receive goods internal menggunakan doc type receive goods
  102.      */        
  103.     INSERT INTO tt_in_doc_product_price
  104.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  105.      doc_type_id, ref_id, doc_no, doc_date,
  106.      curr_code, amount, qty, uom_id,
  107.      numerator_rate, denominator_rate,
  108.      gl_curr_code, gl_amount)
  109.     SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.product_id,
  110.         A.doc_type_id, vEmptyId, vEmptyValue, vEmptyValue,
  111.         A.gl_curr_code, A.gl_amount, A.qty, A.base_uom_id,
  112.         1, 1,
  113.         A.gl_curr_code, A.gl_amount
  114.     FROM in_summary_monthly_amount A, vw_raw_materials_product E
  115.     WHERE A.date_year_month = pYearMonth AND
  116.         A.doc_type_id IN (111) AND
  117.         A.tenant_id = pTenantId AND
  118.         A.ou_bu_id = pOuId AND
  119.         A.product_id = E.product_id;   
  120.        
  121.     /*
  122.      * ambil data adjusment stok amount
  123.      * jika curr <> curr G/L, maka lakukan hitung rate untuk ke nilai sesuai valuta G/L
  124.      * 522 : adj stock amount ( mempengaruhi COGS )
  125.      * 523 : adj stock amount balance ( mempengaruhi saldo akhir )
  126.      * 528 : costing allocation to product ( mempengaruhi COGS )
  127.      * 511 : claim note (mempengaruhi COGS), added by WTC 170418
  128.      */
  129.     INSERT INTO tt_in_doc_product_price
  130.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  131.      doc_type_id, ref_id, doc_no, doc_date,
  132.      curr_code, amount, qty, uom_id,
  133.      numerator_rate, denominator_rate,
  134.      gl_curr_code, gl_amount)
  135.     SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
  136.         A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
  137.         A.curr_code, A.amount, A.qty, A.uom_id,
  138.         1, 1, vCurrGL, ROUND( A.amount, vRoundingDigit)
  139.     FROM in_product_price_balance A, m_ou_structure B, dt_date C, vw_raw_materials_product E
  140.     WHERE A.doc_date = C.string_date AND
  141.         C.year_month_date = pYearMonth AND
  142.         A.tenant_id = pTenantId AND
  143.         A.ou_id = B.ou_id AND
  144.         B.ou_bu_id = pOuId AND
  145.         A.doc_type_id IN (522, 523, 528) AND
  146.         A.product_id = E.product_id;   
  147.    
  148.     -- hitung nilai pembukuan untuk transaksi adjusment stok amount
  149.     UPDATE tt_in_doc_product_price SET numerator_rate = B.amount_to, denominator_rate = B.amount_from
  150.     FROM m_exchange_rate B
  151.     WHERE tt_in_doc_product_price.session_id = pSessionId AND
  152.         tt_in_doc_product_price.tenant_id = B.tenant_id AND
  153.         B.type_exchange_rate = vTypeRate AND
  154.         tt_in_doc_product_price.doc_date = B.date_from AND
  155.         tt_in_doc_product_price.curr_code = B.curr_code_from AND
  156.         tt_in_doc_product_price.gl_curr_code = B.curr_code_to AND
  157.         tt_in_doc_product_price.doc_type_id IN (522, 523, 528) AND
  158.         tt_in_doc_product_price.curr_code <> vCurrGL;
  159.  
  160.     UPDATE tt_in_doc_product_price SET gl_amount = ROUND(amount * numerator_rate / denominator_rate, vRoundingDigit)
  161.     WHERE tt_in_doc_product_price.session_id = pSessionId AND
  162.           tt_in_doc_product_price.doc_type_id IN (522, 523, 528) AND
  163.           tt_in_doc_product_price.curr_code <> vCurrGL;
  164.    
  165.     /*
  166.      * Moved by WTC, 170418, sblmnya tidak diperhitungkan sbg pengurang COGS.
  167.      * membuat summary nilai claim note berdasarkan hasil jurnal
  168.      * Claim Note : 511
  169.      */
  170.     INSERT INTO tt_in_doc_product_price
  171.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  172.      doc_type_id, ref_id, doc_no, doc_date,
  173.      curr_code, amount, qty, uom_id,
  174.      numerator_rate, denominator_rate,
  175.      gl_curr_code, gl_amount)
  176.     SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, C.product_id,
  177.         A.doc_type_id, vEmptyId, vEmptyValue, vEmptyValue,
  178.         C.gl_curr_code, -1 * C.gl_amount, -1 * C.qty, C.uom_id,
  179.         1, 1,
  180.         C.gl_curr_code, -1 * C.gl_amount
  181.     FROM gl_journal_trx A, dt_date B, gl_journal_trx_item C, m_document_journal D, vw_raw_materials_product E
  182.     WHERE A.tenant_id = pTenantId AND
  183.         A.ou_bu_id = pOuId AND
  184.         A.status_doc = vStatusRelease AND
  185.         A.doc_date = B.string_date AND
  186.         B.year_month_date = pYearMonth AND
  187.         A.journal_trx_id = C.journal_trx_id AND
  188.         A.doc_type_id = D.doc_type_id AND
  189.         D.ledger_code IN ('INV') AND
  190.         C.journal_desc = 'PRODUCT_STOCK' AND
  191.         A.doc_type_id IN (511) AND
  192.         C.product_id = E.product_id;
  193.          
  194.     /*
  195.      * WTC, 160211, tambahkan dokumen return/buyback
  196.      * product non assembly
  197.      * ambil data transaksi yg tambah stok, tp belum memiliki nilai
  198.      * 1.return note : 502
  199.      * 2.adj stok qty plus (non konsinyasi): 521
  200.      * 3.adj stok outlet qty plus (non konsinyasi) : 413
  201.      * 4.Return MLM : 562
  202.      * 5.Buyback MLM : 563
  203.      * 6.Exchange MLM in : 560
  204.      */
  205.     INSERT INTO tt_in_add_product_unamount
  206.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  207.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  208.     SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
  209.         A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
  210.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  211.     WHERE A.ou_id = B.ou_id AND
  212.         B.ou_bu_id = pOuId AND
  213.         A.date_year_month = pYearMonth AND
  214.         A.doc_type_id IN ( 502, 562, 563 ) AND
  215.         A.product_id = E.product_id;   
  216.  
  217.     -- WTC, 160621, khusus untuk adj stock qty plus, ambil yg non konsinyasi saja
  218.     INSERT INTO tt_in_add_product_unamount
  219.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  220.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  221.     SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
  222.         A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
  223.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  224.     WHERE A.ou_id = B.ou_id AND
  225.         B.ou_bu_id = pOuId AND
  226.         A.date_year_month = pYearMonth AND
  227.         A.doc_type_id IN ( 521, 413 ) AND
  228.         A.product_id = E.product_id AND
  229.         A.qty > 0 AND
  230.         A.flg_buy_konsinyasi = vFlagNo;
  231.  
  232.     -- WTC, 160621, ambil data exchange MLM in, baik yg konsinyasi maupun non konsinyasi
  233.     INSERT INTO tt_in_add_product_unamount
  234.     (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
  235.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  236.     SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
  237.         A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
  238.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  239.     WHERE A.ou_id = B.ou_id AND
  240.         B.ou_bu_id = pOuId AND
  241.         A.date_year_month = pYearMonth AND
  242.         A.doc_type_id = 560 AND
  243.         A.product_id = E.product_id AND
  244.         A.qty > 0
  245.        
  246. /* NK, 25 Jan 2014, di submit adj stok qty diubah sehingga log product balance stock akan ada 2 kelompok,
  247.  * kelompok yang qty > 0 dan qty < 0
  248.                    
  249.     INSERT INTO tt_in_add_product_unamount
  250.     (session_id, tenant_id, ou_id, sub_ou_id, product_id,
  251.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  252.     SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id, B.product_id,
  253.         A.doc_type_id, B.qty_realization, B.base_uom_id, vCurrGL, 0, vEmptyValue
  254.     FROM in_inventory A, in_inventory_item B, dt_date C, m_ou_structure D, vw_raw_materials_product E
  255.     WHERE A.tenant_id = pTenantId AND
  256.         A.doc_date = C.string_date AND
  257.         C.year_month_date = pYearMonth AND
  258.         A.inventory_id = B.inventory_id AND
  259.         A.ou_from_id = D.ou_id AND
  260.         D.ou_bu_id = pOuId AND
  261.         A.doc_type_id = 521 AND
  262.         B.qty_realization > 0 AND
  263.         B.product_id = E.product_id;   
  264.                    
  265.     INSERT INTO tt_in_add_product_unamount
  266.     (session_id, tenant_id, ou_id, sub_ou_id, product_id,
  267.      doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
  268.     SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id, B.product_id,
  269.         A.doc_type_id, B.qty_realization, B.base_uom_id, vCurrGL, 0, vEmptyValue
  270.     FROM i_trx_inventory A, i_trx_inventory_item B, dt_date C, m_ou_structure D, vw_raw_materials_product E
  271.     WHERE A.tenant_id = pTenantId AND
  272.         A.doc_date = C.string_date AND
  273.         C.year_month_date = pYearMonth AND
  274.         A.trx_inventory_id = B.trx_inventory_id AND
  275.         A.ou_id = D.ou_id AND
  276.         D.ou_bu_id = pOuId AND
  277.         A.doc_type_id = 413 AND
  278.         B.qty_realization > 0 AND
  279.         B.product_id = E.product_id;   
  280.  */
  281.     /*
  282.      * buat data yang akan menjadi acuan harga standard
  283.      * 1.data purch price di bulan bersangkutan
  284.      * 2.data nilai barang di awal bulan
  285.      * 3.data master harga standard
  286.      */
  287.     INSERT INTO tt_in_product_base_price
  288.     (session_id, tenant_id, ou_id, product_id,
  289.      flg_amount, qty, uom_id, gl_curr_code, gl_amount)
  290.     SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.product_id,
  291.         'PURCH_PRICE', SUM(A.qty), A.uom_id, A.gl_curr_code, SUM(A.gl_amount)
  292.     FROM tt_in_doc_product_price A
  293.     WHERE A.session_id = pSessionId AND
  294.         A.doc_type_id IN (111)
  295.     GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.product_id,
  296.         A.uom_id, A.gl_curr_code;
  297.    
  298.     INSERT INTO tt_in_product_base_price
  299.     (session_id, tenant_id, ou_id, product_id,
  300.      flg_amount, qty, uom_id, gl_curr_code, gl_amount)
  301.     SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.product_id,
  302.         'BGN_BALANCE', SUM(A.qty), A.base_uom_id, A.gl_curr_code, SUM(A.gl_amount)
  303.     FROM in_summary_monthly_amount A, vw_raw_materials_product E
  304.     WHERE A.date_year_month = pYearMonth AND
  305.         A.doc_type_id = vDocTypeAwal AND
  306.         A.tenant_id = pTenantId AND
  307.         A.ou_bu_id = pOuId AND
  308.         A.product_id = E.product_id
  309.     GROUP BY A.tenant_id, A.ou_bu_id, A.product_id,
  310.         A.base_uom_id, A.gl_curr_code;
  311.    
  312.     INSERT INTO tt_in_product_base_price
  313.     (session_id, tenant_id, ou_id, product_id,
  314.      flg_amount, qty, uom_id, gl_curr_code, gl_amount)
  315.     SELECT pSessionId, A.tenant_id, A.ou_id, A.product_id,
  316.         'STANDARD_PRICE', 0, E.base_uom_id, A.curr_code, SUM(A.amount)
  317.     FROM in_product_standard_cogs A, vw_raw_materials_product E
  318.     WHERE A.tenant_id = pTenantId AND
  319.         A.ou_id = pOuId AND
  320.         A.year_month_date = pYearMonth AND
  321.         A.product_id = E.product_id
  322.     GROUP BY A.tenant_id, A.ou_id, A.product_id, A.curr_code, E.base_uom_id;
  323.    
  324.     INSERT INTO tt_in_product_base_price
  325.     (session_id, tenant_id, ou_id, product_id,
  326.      flg_amount, qty, uom_id, gl_curr_code, gl_amount)
  327.     SELECT pSessionId, A.tenant_id, A.ou_id, A.product_id,
  328.         'PREV_MONTH_COGS', A.qty_total, E.base_uom_id, A.curr_code, A.amount_total
  329.     FROM in_summary_monthly_cogs A, vw_raw_materials_product E
  330.     WHERE A.tenant_id = pTenantId AND
  331.         A.ou_id = pOuId AND
  332.         A.product_id = E.product_id AND
  333.         A.date_year_month = (SELECT MAX(Z.date_year_month)
  334.                              FROM in_summary_monthly_cogs Z
  335.                              WHERE A.tenant_id = Z.tenant_id AND
  336.                                 Z.date_year_month < pYearMonth AND
  337.                                 Z.ou_id = A.ou_id AND
  338.                                 Z.product_id = A.product_id AND
  339.                                 Z.qty_total <> 0);
  340.    
  341.     /*
  342.      * isi nilai data tt_in_add_product_unamount dengan urutan :
  343.      * 1. update dengan data flg amount STANDARD_PRICE
  344.      * 2. jika belum ada, update dengan data flg amount BGN_BALANCE
  345.      * 3. jika belum ada, update dengan data flg amount PURCH_PRICE
  346.      * 4. jika belum ada, update dengan data flg amount PREV_MONTH_COGS
  347.      */
  348.    
  349.     UPDATE tt_in_add_product_unamount SET gl_amount = tt_in_add_product_unamount.qty * A.gl_amount, flg_amount = A.flg_amount
  350.     FROM tt_in_product_base_price A
  351.     WHERE A.session_id = pSessionId AND
  352.         tt_in_add_product_unamount.session_id = A.session_id AND
  353.         tt_in_add_product_unamount.tenant_id = A.tenant_id AND
  354.         tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
  355.         tt_in_add_product_unamount.product_id = A.product_id AND
  356.         tt_in_add_product_unamount.flg_amount = vEmptyValue AND
  357.         A.flg_amount = 'STANDARD_PRICE';
  358.    
  359.     UPDATE tt_in_add_product_unamount SET gl_amount = ROUND(tt_in_add_product_unamount.qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
  360.     FROM tt_in_product_base_price A
  361.     WHERE A.session_id = pSessionId AND
  362.         tt_in_add_product_unamount.session_id = A.session_id AND
  363.         tt_in_add_product_unamount.tenant_id = A.tenant_id AND
  364.         tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
  365.         tt_in_add_product_unamount.product_id = A.product_id AND
  366.         tt_in_add_product_unamount.flg_amount = vEmptyValue AND    
  367.         A.flg_amount = 'BGN_BALANCE' AND
  368.         A.qty <> 0;
  369.        
  370.     UPDATE tt_in_add_product_unamount SET gl_amount = ROUND(tt_in_add_product_unamount.qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
  371.     FROM tt_in_product_base_price A
  372.     WHERE A.session_id = pSessionId AND
  373.         tt_in_add_product_unamount.session_id = A.session_id AND
  374.         tt_in_add_product_unamount.tenant_id = A.tenant_id AND
  375.         tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
  376.         tt_in_add_product_unamount.product_id = A.product_id AND
  377.         tt_in_add_product_unamount.flg_amount = vEmptyValue AND    
  378.         A.flg_amount = 'PURCH_PRICE' AND
  379.         A.qty <> 0;
  380.  
  381.     -- Ambil dari monthly cogs terakhir
  382.     UPDATE tt_in_add_product_unamount SET gl_amount = ROUND(tt_in_add_product_unamount.qty * ( A.gl_amount / A.qty ), vRoundingDigit), flg_amount = A.flg_amount
  383.     FROM tt_in_product_base_price A
  384.     WHERE A.session_id = pSessionId AND
  385.         tt_in_add_product_unamount.session_id = A.session_id AND
  386.         tt_in_add_product_unamount.tenant_id = A.tenant_id AND
  387.         tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
  388.         tt_in_add_product_unamount.product_id = A.product_id AND
  389.         tt_in_add_product_unamount.flg_amount = vEmptyValue AND    
  390.         A.flg_amount = 'PREV_MONTH_COGS' AND
  391.         A.qty <> 0;
  392.        
  393. ------------------------------------------------------------------------------------------------------------------------
  394.     /*
  395.      * ABRM, 20210727
  396.      * Isi otomatis harga standard berdasarkan :
  397.      * a. harga standard bulan sebelumnya, jika costingnya hasil nya 0
  398.      */
  399.     WITH from_standard_price_previous_months AS (
  400.         SELECT MAX(A.year_month_date) AS year_month_date, A.tenant_id, A.ou_id, A.product_id
  401.         FROM in_product_standard_cogs A
  402.         WHERE A.amount <> 0
  403.         GROUP BY A.tenant_id, A.ou_id, A.product_id
  404.     )
  405.     UPDATE tt_in_add_product_unamount A
  406.     SET gl_amount = A.qty * C.amount,
  407.         flg_amount = 'STD_PRC_PREVMONTH'
  408.     FROM from_standard_price_previous_months B
  409.     INNER JOIN in_product_standard_cogs C ON B.tenant_id = C.tenant_id AND
  410.                                              B.year_month_date = C.year_month_date AND
  411.                                              B.ou_id = C.ou_id AND
  412.                                              B.product_id = C.product_id
  413.     WHERE A.session_id = pSessionId AND
  414.           A.tenant_id = B.tenant_id AND
  415.           A.ou_bu_id = B.ou_id AND
  416.           A.product_id = B.product_id AND
  417.           A.flg_amount = vEmptyValue;
  418. ------------------------------------------------------------------------------------------------------------------------
  419.        
  420. /*
  421.  *  menghitung harga rata rata product (non assembly ) berdasarkan :
  422.  *  1. harga purchasing
  423.  *  2. harga adj stock amount ( bukan yg balance amount )
  424.  *  3. transaksi tambah stok qty, dengan harga sudah diproses : retur jual, adj stok qty > 0, return MLM, buyback MLM, exchange in
  425.  *  4. harga saldo awal
  426.  */    
  427.     INSERT INTO tt_in_summary_monthly_product
  428.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  429.     doc_type_id, product_id, base_uom_id, qty,
  430.     gl_curr_code, gl_amount, flg_amount)
  431.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  432.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  433.         A.gl_curr_code, SUM(A.gl_amount), 'PURCH_PRICE'
  434.     FROM tt_in_doc_product_price A
  435.     WHERE A.session_id = pSessionId AND
  436.         A.doc_type_id IN (111)
  437.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;
  438.    
  439.     -- Mod by WTC, 20170418, transaksi claim note juga ikut diambil
  440.     INSERT INTO tt_in_summary_monthly_product
  441.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  442.     doc_type_id, product_id, base_uom_id, qty,
  443.     gl_curr_code, gl_amount, flg_amount)
  444.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  445.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  446.         A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
  447.     FROM tt_in_doc_product_price A
  448.     WHERE A.session_id = pSessionId AND
  449.         A.doc_type_id IN ( 522, 528, 511 )
  450.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;
  451.  
  452.     INSERT INTO tt_in_summary_monthly_product
  453.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  454.     doc_type_id, product_id, base_uom_id, qty,
  455.     gl_curr_code, gl_amount, flg_amount)
  456.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  457.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  458.         A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
  459.     FROM tt_in_add_product_unamount A
  460.     WHERE A.session_id = pSessionId
  461.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code, A.flg_amount;
  462.  
  463.     INSERT INTO tt_in_summary_monthly_product
  464.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  465.     doc_type_id, product_id, base_uom_id, qty,
  466.     gl_curr_code, gl_amount, flg_amount)
  467.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  468.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  469.         A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
  470.     FROM in_summary_monthly_amount A, vw_raw_materials_product E
  471.     WHERE A.date_year_month = pYearMonth AND
  472.         A.doc_type_id = vDocTypeAwal AND
  473.         A.ou_bu_id = pOuId AND
  474.         A.tenant_id = pTenantId AND
  475.         A.product_id = E.product_id
  476.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id, A.gl_curr_code, A.flg_amount;
  477.  
  478.     /*
  479.      * hitung harga rata-rata sebagai dasar harga pokok
  480.      */
  481.     INSERT INTO tt_in_summary_monthly_cogs
  482.     (session_id, date_year_month, tenant_id, ou_id,
  483.     product_id, qty_total, curr_code, amount_total, avg_price)
  484.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id,
  485.         A.product_id, SUM(A.qty), A.gl_curr_code, SUM(A.gl_amount), 0
  486.     FROM tt_in_summary_monthly_product A
  487.     WHERE A.session_id = pSessionId
  488.     GROUP BY A.tenant_id, A.ou_bu_id, A.product_id, A.gl_curr_code;
  489.    
  490.     UPDATE tt_in_summary_monthly_cogs
  491.       SET avg_price = amount_total / qty_total
  492.     WHERE session_id = pSessionId AND
  493.       qty_total <> 0;
  494.    
  495.     /*
  496.      * memberikan nilai untuk transaksi stok yang mengurangi qty, sesuai harga rata rata bulan ini
  497.      * transaksi selain adj stok qty(521) dan adj stok qty outlet(413) , yang qty < 0
  498.      * transaksi pemakaian Nota klaim (511), DO (311) , POS Shop (431), Pos Shop In Shop(431)
  499.      * NK, 16 Feb 2015 : tambahan transaksi DO Receipt ( 526 )
  500.      * Sales Invoice Konsinyasi(354), DO Internal tidak jadi dipakai(154)
  501.      * Return POS Shop, Return POS SHop In SHop belum ada
  502.      * WTC, 160622 : tambahan transaksi GTI receipt lost ( 537 )
  503.      * WTC, 160211 : tambahan transaksi DO MLM ( 313 ), exchange MLM out (560), exclude claim
  504.      */
  505.     INSERT INTO tt_in_summary_monthly_product
  506.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  507.     doc_type_id, product_id, base_uom_id, qty,
  508.     gl_curr_code, gl_amount, flg_amount)
  509.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
  510.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  511.         vCurrGL, 0, 'MONTHLY_AVG'
  512.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  513.     WHERE A.ou_id = B.ou_id AND
  514.         B.ou_bu_id = pOuId AND
  515.         A.date_year_month = pYearMonth AND
  516.         A.doc_type_id IN (311,431,526,313,537) AND
  517.         A.product_id = E.product_id
  518.     GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;  
  519.  
  520.     INSERT INTO tt_in_summary_monthly_product
  521.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  522.     doc_type_id, product_id, base_uom_id, qty,
  523.     gl_curr_code, gl_amount, flg_amount)
  524.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
  525.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  526.         vCurrGL, 0, 'MONTHLY_AVG'
  527.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  528.     WHERE A.ou_id = B.ou_id AND
  529.         B.ou_bu_id = pOuId AND
  530.         A.date_year_month = pYearMonth AND
  531.         A.doc_type_id IN (521, 413) AND
  532.         A.product_id = E.product_id AND
  533.         A.qty < 0 AND
  534.         A.flg_buy_konsinyasi = vFlagNo
  535.     GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;
  536.  
  537.     -- WTC, 160622, ambil data exchange MLM out
  538.     INSERT INTO tt_in_summary_monthly_product
  539.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  540.     doc_type_id, product_id, base_uom_id, qty,
  541.     gl_curr_code, gl_amount, flg_amount)
  542.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
  543.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  544.         vCurrGL, 0, 'MONTHLY_AVG'
  545.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  546.     WHERE A.ou_id = B.ou_id AND
  547.         B.ou_bu_id = pOuId AND
  548.         A.date_year_month = pYearMonth AND
  549.         A.doc_type_id = 560 AND
  550.         A.product_id = E.product_id AND
  551.         A.qty < 0
  552.     GROUP BY A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.doc_type_id, A.product_id, A.base_uom_id;
  553.    
  554. /*  NK, 25 Jan 2014, submit adj stok qty diubah sehingga menghasilkan 2 jenis log data product balance stock
  555.  *  qty < 0 dan qty > 0 dipisahkan
  556.     INSERT INTO tt_in_summary_monthly_product
  557.     (session_id, date_year_month, tenant_id, ou_id, sub_ou_id,
  558.     doc_type_id, product_id, base_uom_id, qty,
  559.     gl_curr_code, gl_amount, flg_amount)
  560.     SELECT pSessionId, pYearMonth, A.tenant_id, C.ou_bu_id, C.ou_id,
  561.         A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
  562.         vCurrGL, 0, 'MONTHLY_AVG'
  563.     FROM in_inventory A, in_inventory_item B, m_ou_structure C, dt_date D, vw_raw_materials_product E
  564.     WHERE A.ou_from_id = C.ou_id AND
  565.         C.ou_bu_id = pOuId AND
  566.         A.inventory_id = B.inventory_id AND
  567.         A.doc_date = D.string_date AND
  568.         D.year_month_date = pYearMonth AND
  569.         A.doc_type_id IN (521, 413) AND
  570.         B.qty_realization < 0 AND
  571.         B.product_id = E.product_id
  572.     GROUP BY A.tenant_id, C.ou_bu_id, C.ou_id, A.doc_type_id, B.product_id, B.base_uom_id;
  573. */
  574.     UPDATE tt_in_summary_monthly_product SET gl_amount = ROUND(qty * (A.amount_total / A.qty_total), vRoundingDigit)
  575.     FROM tt_in_summary_monthly_cogs A
  576.     WHERE A.session_id = pSessionId AND
  577.         tt_in_summary_monthly_product.session_id = A.session_id AND
  578.         tt_in_summary_monthly_product.tenant_id = A.tenant_id AND
  579.         tt_in_summary_monthly_product.ou_bu_id = A.ou_id AND
  580.         tt_in_summary_monthly_product.product_id = A.product_id AND
  581.         tt_in_summary_monthly_product.doc_type_id IN (311,431,526,521,413,313,560,537) AND
  582.         tt_in_summary_monthly_product.flg_amount = 'MONTHLY_AVG' AND
  583.         A.qty_total <> 0;
  584.  
  585.     -- WTC, 170502, khusus untuk product tidak ada COGS, maka ambil nilainya dari master harga standar
  586.     UPDATE tt_in_summary_monthly_product SET gl_amount = ROUND(tt_in_summary_monthly_product.qty * A.gl_amount, vRoundingDigit), flg_amount = A.flg_amount
  587.     FROM tt_in_product_base_price A
  588.     WHERE A.session_id = pSessionId AND
  589.         A.flg_amount = 'STANDARD_PRICE' AND
  590.         tt_in_summary_monthly_product.session_id = A.session_id AND
  591.         tt_in_summary_monthly_product.tenant_id = A.tenant_id AND
  592.         tt_in_summary_monthly_product.ou_bu_id = A.ou_id AND
  593.         tt_in_summary_monthly_product.product_id = A.product_id AND
  594.         tt_in_summary_monthly_product.doc_type_id IN (311,431,526,521,413,313,560,537) AND
  595.         tt_in_summary_monthly_product.flg_amount = 'MONTHLY_AVG' AND
  596.         NOT EXISTS (SELECT 1 FROM tt_in_summary_monthly_cogs B
  597.             WHERE tt_in_summary_monthly_product.session_id = B.session_id AND
  598.                 tt_in_summary_monthly_product.tenant_id = B.tenant_id AND
  599.                 tt_in_summary_monthly_product.ou_bu_id = B.ou_id AND
  600.                 tt_in_summary_monthly_product.product_id = B.product_id AND
  601.                 B.qty_total <> 0);
  602.  
  603. /*
  604.      * untuk menghitung saldo akhir di bulan tersebut, dengan cara membuat record saldo awal untuk bulan berikutnya
  605.      * 1. ambil data adj stok balance amount ( adjust nilai akhir saldo barang )
  606.      * 2. hitung saldo akhir
  607.      */    
  608.     INSERT INTO tt_in_summary_monthly_product
  609.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  610.     doc_type_id, product_id, base_uom_id, qty,
  611.     gl_curr_code, gl_amount, flg_amount)
  612.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  613.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  614.         A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
  615.     FROM tt_in_doc_product_price A
  616.     WHERE A.session_id = pSessionId AND
  617.         A.doc_type_id = 523
  618.     GROUP BY A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.doc_type_id, A.product_id, A.uom_id, A.gl_curr_code;
  619.        
  620.     /*
  621.      * Persiapan data saldo awal untuk bulan berikutnya. Periksa terlebih dahulu apakah ada data yg qty = 0, tetapi gl_amount <> 0.
  622.      * Nilai gl_amount tsb akan dialokasikan ke data transaksi pengeluaran stok dengan id terbesar untuk product ybs.
  623.      */
  624.     INSERT INTO tt_in_summary_monthly_amount_remain
  625.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  626.     doc_type_id, product_id, base_uom_id, qty,
  627.     gl_curr_code, gl_amount, flg_amount, summary_monthly_product_id)
  628.     SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
  629.         vDocTypeAwal, A.product_id, A.base_uom_id, SUM(A.qty),
  630.         vCurrGL, SUM(A.gl_amount), 'BGN_BALANCE', vEmptyId
  631.     FROM tt_in_summary_monthly_product A
  632.     WHERE A.session_id = pSessionId
  633.     GROUP BY A.date_year_month, A.tenant_id, A.ou_bu_id, A.product_id, A.base_uom_id;
  634.    
  635.     -- Cari id data sebagai penampung alokasi nilai
  636.     /**
  637.      * Adrian, Feb 08, 2018
  638.      * Menambahkan coalesce, sehingga summary_monthly_product_id tidak null
  639.      */
  640.     UPDATE tt_in_summary_monthly_amount_remain
  641.         SET summary_monthly_product_id = COALESCE((
  642.             SELECT MAX(A.summary_monthly_product_id)
  643.             FROM tt_in_summary_monthly_product A
  644.             WHERE A.session_id = pSessionId AND
  645.                 tt_in_summary_monthly_amount_remain.session_id = A.session_id AND
  646.                 tt_in_summary_monthly_amount_remain.date_year_month = A.date_year_month AND
  647.                 tt_in_summary_monthly_amount_remain.tenant_id = A.tenant_id AND
  648.                 tt_in_summary_monthly_amount_remain.ou_bu_id = A.ou_bu_id AND
  649.                 tt_in_summary_monthly_amount_remain.product_id = A.product_id AND
  650.                 A.doc_type_id IN (311,431,526,521,413,313,560,537) AND
  651.                 A.flg_amount = 'MONTHLY_AVG'),
  652.             -99)
  653.     WHERE session_id = pSessionId AND qty = 0 AND gl_amount <> 0;
  654.    
  655.    
  656.     IF (vGenerateAdjustmentStockAmount = vFlagYes) THEN
  657.    
  658.         /**
  659.          * Adrian, Feb 08, 2018
  660.          * Generate Adjustment Stock Amount untuk tt_in_summary_monthly_amount_remain
  661.          * yang memiliki gl_amount <> 0, qty = 0, dan summary_monthly_product_id = vEmptyId
  662.          */
  663.         INSERT INTO tt_data_summary_monthly_amount_exclude(
  664.             session_id, tenant_id, date_year_month,
  665.             ou_bu_id, ou_branch_id, ou_sub_bu_id,
  666.             product_id, qty, base_uom_id,
  667.             gl_curr_code, gl_amount)
  668.         SELECT pSessionId, A.tenant_id, A.date_year_month,
  669.                 A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  670.                 A.product_id, A.qty AS qty, A.base_uom_id,
  671.                 A.gl_curr_code, -1 * A.gl_amount AS gl_amount
  672.         FROM tt_in_summary_monthly_amount_remain A
  673.         WHERE A.session_id = pSessionId AND
  674.             A.tenant_id = pTenantId AND
  675.             A.gl_amount <> 0 AND
  676.             A.qty = 0 AND
  677.             A.summary_monthly_product_id = vEmptyId;
  678.            
  679.         SELECT COUNT(1) FROM tt_data_summary_monthly_amount_exclude INTO vCountItemToGenerate;
  680.    
  681.         IF (vCountItemToGenerate > 0) THEN
  682.            
  683.             PERFORM in_generate_adj_stock_amount_doc_for_process_costing(pTenantId, pSessionId, pYearMonth, pOuId, pDatetime, pUserId, vGroupProductRM);
  684.            
  685.         END IF;
  686.    
  687.         /*
  688.          * NK, 16 Feb 2015
  689.          * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  690.          * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  691.          */
  692.         /**
  693.          * Adrian, Feb 08, 2018
  694.          * Ubah filter menjadi gl_amount <> 0 dan qty = 0
  695.          */
  696.         INSERT INTO in_summary_monthly_zero_qty_amount
  697.         (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  698.         product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  699.         doc_type_id, qty, gl_amount, remark)
  700.         SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  701.             A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  702.             B.doc_type_id, B.qty, B.gl_amount, vGroupProductRM
  703.         FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
  704.         WHERE A.session_id = pSessionId AND
  705.             A.gl_amount <> 0 AND A.qty = 0 AND
  706.             B.summary_monthly_product_id = A.summary_monthly_product_id;
  707.            
  708.     ELSE
  709.    
  710.         /*
  711.          * NK, 16 Feb 2015
  712.          * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  713.          * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  714.          */
  715.         INSERT INTO in_summary_monthly_zero_qty_amount
  716.         (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  717.         product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  718.         doc_type_id, qty, gl_amount, remark)
  719.         SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  720.             A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  721.             B.doc_type_id, B.qty, B.gl_amount, vGroupProductRM
  722.         FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
  723.         WHERE A.session_id = pSessionId AND
  724.             A.summary_monthly_product_id <> vEmptyId AND
  725.             B.summary_monthly_product_id = A.summary_monthly_product_id;
  726.            
  727.     END IF;
  728.    
  729.                
  730.     UPDATE tt_in_summary_monthly_product SET gl_amount = tt_in_summary_monthly_product.gl_amount - A.gl_amount
  731.     FROM tt_in_summary_monthly_amount_remain A
  732.     WHERE A.session_id = pSessionId AND
  733.         A.summary_monthly_product_id <> vEmptyId AND
  734.         tt_in_summary_monthly_product.summary_monthly_product_id = A.summary_monthly_product_id;
  735.        
  736.    
  737.     IF ((vGenerateAdjustmentStockAmount = vFlagYes) AND (vCountItemToGenerate > 0)) THEN
  738.        
  739.         /**
  740.          * Adrian, Feb 08, 2018
  741.          * Update tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  742.          * dan product id nya terdaftar di
  743.          * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
  744.          */
  745.         UPDATE tt_in_summary_monthly_product Z
  746.         SET gl_amount = Z.gl_amount - A.gl_amount
  747.         FROM tt_in_summary_monthly_amount_remain A
  748.         WHERE Z.session_id = pSessionId AND
  749.             Z.tenant_id = pTenantId AND
  750.             Z.session_id = A.session_id AND
  751.             Z.date_year_month = A.date_year_month AND
  752.             Z.tenant_id = A.tenant_id AND
  753.             Z.ou_bu_id = A.ou_bu_id AND
  754.             Z.product_id = A.product_id AND
  755.             Z.doc_type_id = 522 AND
  756.             Z.flg_amount = 'TRANSACTION' AND
  757.             A.gl_amount <> 0 AND
  758.             A.qty = 0 AND
  759.             A.summary_monthly_product_id = vEmptyId;
  760.            
  761.         /**
  762.          * Adrian, Feb 08, 2018
  763.          * Insert tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  764.          * jika product id yang terdaftar di
  765.          * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
  766.          * belum terdapat dalam tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  767.          */
  768.         INSERT INTO tt_in_summary_monthly_product(
  769.                 session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  770.                 doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
  771.         SELECT A.session_id, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  772.             522, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, (-1 * A.gl_amount), 'TRANSACTION'
  773.         FROM tt_in_summary_monthly_amount_remain A
  774.         WHERE A.session_id = pSessionId AND
  775.             A.tenant_id = pTenantId AND
  776.             A.gl_amount <> 0 AND
  777.             A.qty = 0 AND
  778.             A.summary_monthly_product_id = vEmptyId AND
  779.             NOT EXISTS(
  780.                 SELECT 1
  781.                 FROM tt_in_summary_monthly_product B
  782.                 WHERE B.session_id = A.session_id AND
  783.                     B.date_year_month = A.date_year_month AND
  784.                     B.tenant_id = A.tenant_id AND
  785.                     B.ou_bu_id = A.ou_bu_id AND
  786.                     B.product_id = A.product_id AND
  787.                     B.doc_type_id = 522 AND
  788.                     B.flg_amount = 'TRANSACTION'
  789.             );
  790.            
  791.     END IF;
  792.        
  793.        
  794.     INSERT INTO tt_in_summary_monthly_product
  795.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  796.     doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
  797.     SELECT A.session_id, vNextYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  798.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, A.gl_amount, A.flg_amount
  799.     FROM tt_in_summary_monthly_amount_remain A
  800.     WHERE A.session_id = pSessionId AND A.qty <> 0;
  801.                
  802.     INSERT INTO in_summary_monthly_cogs
  803.     (date_year_month, tenant_id, ou_id, product_id,
  804.     qty_total, curr_code, amount_total, avg_price,
  805.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  806.     SELECT A.date_year_month, A.tenant_id, A.ou_id, A.product_id,
  807.             A.qty_total, A.curr_code, A.amount_total, A.avg_price,
  808.             0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  809.     FROM tt_in_summary_monthly_cogs A
  810.     WHERE A.session_id = pSessionId;
  811.        
  812.     INSERT INTO in_summary_monthly_amount
  813.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  814.     doc_type_id, product_id, base_uom_id, qty,
  815.     gl_curr_code, gl_amount, flg_amount,
  816.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  817.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  818.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  819.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  820.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  821.     FROM tt_in_summary_monthly_product A
  822.     WHERE A.session_id = pSessionId AND
  823.             A.date_year_month = pYearMonth AND
  824.             A.doc_type_id NOT IN (vDocTypeAwal, 111);
  825.    
  826.     --insert data anomaly yg amount/qty < 0
  827.     INSERT INTO in_anomaly_gl_amount_from_process_costing
  828.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  829.     doc_type_id, product_id, base_uom_id, qty,
  830.     gl_curr_code, gl_amount, flg_amount,
  831.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  832.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  833.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  834.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  835.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  836.     FROM tt_in_summary_monthly_product A
  837.     WHERE A.session_id = pSessionId AND
  838.             A.date_year_month = pYearMonth AND
  839.             A.doc_type_id NOT IN (vDocTypeAwal, 111) AND
  840.             A.gl_amount / A.qty < 0 AND
  841.             A.qty <> 0;
  842.  
  843.     INSERT INTO in_summary_monthly_amount
  844.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  845.     doc_type_id, product_id, base_uom_id, qty,
  846.     gl_curr_code, gl_amount, flg_amount,
  847.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  848.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  849.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  850.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  851.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  852.     FROM tt_in_summary_monthly_product A
  853.     WHERE A.session_id = pSessionId AND
  854.         A.date_year_month = vNextYearMonth;
  855.    
  856.     --insert data anomaly yg amount/qty < 0 untuk saldo awal bulan berikutnya
  857.     INSERT INTO in_anomaly_gl_amount_from_process_costing
  858.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  859.     doc_type_id, product_id, base_uom_id, qty,
  860.     gl_curr_code, gl_amount, flg_amount,
  861.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  862.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  863.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  864.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  865.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  866.     FROM tt_in_summary_monthly_product A
  867.     WHERE A.session_id = pSessionId AND
  868.         A.date_year_month = vNextYearMonth AND
  869.             A.gl_amount/ A.qty < 0 AND
  870.             A.qty <> 0;
  871.        
  872.     DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;
  873.    
  874.     DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;
  875.    
  876.     DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;
  877.    
  878.     DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;
  879.    
  880.     DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;
  881.    
  882.     DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;
  883.    
  884.  END;
  885. $BODY$
  886.   LANGUAGE plpgsql VOLATILE
  887.   COST 100;
  888.   /
  889.  
RAW Paste Data