Advertisement
tercnem

in_process_costing_raw_materials

Sep 24th, 2020
1,728
0
Never
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.  *  menghitung harga rata rata product (non assembly ) berdasarkan :
  396.  *  1. harga purchasing
  397.  *  2. harga adj stock amount ( bukan yg balance amount )
  398.  *  3. transaksi tambah stok qty, dengan harga sudah diproses : retur jual, adj stok qty > 0, return MLM, buyback MLM, exchange in
  399.  *  4. harga saldo awal
  400.  */    
  401.     INSERT INTO tt_in_summary_monthly_product
  402.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  403.     doc_type_id, product_id, base_uom_id, qty,
  404.     gl_curr_code, gl_amount, flg_amount)
  405.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  406.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  407.         A.gl_curr_code, SUM(A.gl_amount), 'PURCH_PRICE'
  408.     FROM tt_in_doc_product_price A
  409.     WHERE A.session_id = pSessionId AND
  410.         A.doc_type_id IN (111)
  411.     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;
  412.    
  413.     -- Mod by WTC, 20170418, transaksi claim note juga ikut diambil
  414.     INSERT INTO tt_in_summary_monthly_product
  415.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  416.     doc_type_id, product_id, base_uom_id, qty,
  417.     gl_curr_code, gl_amount, flg_amount)
  418.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  419.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  420.         A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
  421.     FROM tt_in_doc_product_price A
  422.     WHERE A.session_id = pSessionId AND
  423.         A.doc_type_id IN ( 522, 528, 511 )
  424.     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;
  425.  
  426.     INSERT INTO tt_in_summary_monthly_product
  427.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  428.     doc_type_id, product_id, base_uom_id, qty,
  429.     gl_curr_code, gl_amount, flg_amount)
  430.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  431.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  432.         A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
  433.     FROM tt_in_add_product_unamount A
  434.     WHERE A.session_id = pSessionId
  435.     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;
  436.  
  437.     INSERT INTO tt_in_summary_monthly_product
  438.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  439.     doc_type_id, product_id, base_uom_id, qty,
  440.     gl_curr_code, gl_amount, flg_amount)
  441.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  442.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  443.         A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
  444.     FROM in_summary_monthly_amount A, vw_raw_materials_product E
  445.     WHERE A.date_year_month = pYearMonth AND
  446.         A.doc_type_id = vDocTypeAwal AND
  447.         A.ou_bu_id = pOuId AND
  448.         A.tenant_id = pTenantId AND
  449.         A.product_id = E.product_id
  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.base_uom_id, A.gl_curr_code, A.flg_amount;
  451.  
  452.     /*
  453.      * hitung harga rata-rata sebagai dasar harga pokok
  454.      */
  455.     INSERT INTO tt_in_summary_monthly_cogs
  456.     (session_id, date_year_month, tenant_id, ou_id,
  457.     product_id, qty_total, curr_code, amount_total, avg_price)
  458.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id,
  459.         A.product_id, SUM(A.qty), A.gl_curr_code, SUM(A.gl_amount), 0
  460.     FROM tt_in_summary_monthly_product A
  461.     WHERE A.session_id = pSessionId
  462.     GROUP BY A.tenant_id, A.ou_bu_id, A.product_id, A.gl_curr_code;
  463.    
  464.     UPDATE tt_in_summary_monthly_cogs
  465.       SET avg_price = amount_total / qty_total
  466.     WHERE session_id = pSessionId AND
  467.       qty_total <> 0;
  468.    
  469.     /*
  470.      * memberikan nilai untuk transaksi stok yang mengurangi qty, sesuai harga rata rata bulan ini
  471.      * transaksi selain adj stok qty(521) dan adj stok qty outlet(413) , yang qty < 0
  472.      * transaksi pemakaian Nota klaim (511), DO (311) , POS Shop (431), Pos Shop In Shop(431)
  473.      * NK, 16 Feb 2015 : tambahan transaksi DO Receipt ( 526 )
  474.      * Sales Invoice Konsinyasi(354), DO Internal tidak jadi dipakai(154)
  475.      * Return POS Shop, Return POS SHop In SHop belum ada
  476.      * WTC, 160622 : tambahan transaksi GTI receipt lost ( 537 )
  477.      * WTC, 160211 : tambahan transaksi DO MLM ( 313 ), exchange MLM out (560), exclude claim
  478.      */
  479.     INSERT INTO tt_in_summary_monthly_product
  480.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  481.     doc_type_id, product_id, base_uom_id, qty,
  482.     gl_curr_code, gl_amount, flg_amount)
  483.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
  484.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  485.         vCurrGL, 0, 'MONTHLY_AVG'
  486.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  487.     WHERE A.ou_id = B.ou_id AND
  488.         B.ou_bu_id = pOuId AND
  489.         A.date_year_month = pYearMonth AND
  490.         A.doc_type_id IN (311,431,526,313,537) AND
  491.         A.product_id = E.product_id
  492.     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;  
  493.  
  494.     INSERT INTO tt_in_summary_monthly_product
  495.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  496.     doc_type_id, product_id, base_uom_id, qty,
  497.     gl_curr_code, gl_amount, flg_amount)
  498.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
  499.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  500.         vCurrGL, 0, 'MONTHLY_AVG'
  501.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  502.     WHERE A.ou_id = B.ou_id AND
  503.         B.ou_bu_id = pOuId AND
  504.         A.date_year_month = pYearMonth AND
  505.         A.doc_type_id IN (521, 413) AND
  506.         A.product_id = E.product_id AND
  507.         A.qty < 0 AND
  508.         A.flg_buy_konsinyasi = vFlagNo
  509.     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;
  510.  
  511.     -- WTC, 160622, ambil data exchange MLM out
  512.     INSERT INTO tt_in_summary_monthly_product
  513.     (session_id, date_year_month, tenant_id,  ou_bu_id, ou_branch_id, ou_sub_bu_id,
  514.     doc_type_id, product_id, base_uom_id, qty,
  515.     gl_curr_code, gl_amount, flg_amount)
  516.     SELECT pSessionId, pYearMonth, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id,
  517.         A.doc_type_id, A.product_id, A.base_uom_id, SUM(A.qty),
  518.         vCurrGL, 0, 'MONTHLY_AVG'
  519.     FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_raw_materials_product E
  520.     WHERE A.ou_id = B.ou_id AND
  521.         B.ou_bu_id = pOuId AND
  522.         A.date_year_month = pYearMonth AND
  523.         A.doc_type_id = 560 AND
  524.         A.product_id = E.product_id AND
  525.         A.qty < 0
  526.     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;
  527.    
  528. /*  NK, 25 Jan 2014, submit adj stok qty diubah sehingga menghasilkan 2 jenis log data product balance stock
  529.  *  qty < 0 dan qty > 0 dipisahkan
  530.     INSERT INTO tt_in_summary_monthly_product
  531.     (session_id, date_year_month, tenant_id, ou_id, sub_ou_id,
  532.     doc_type_id, product_id, base_uom_id, qty,
  533.     gl_curr_code, gl_amount, flg_amount)
  534.     SELECT pSessionId, pYearMonth, A.tenant_id, C.ou_bu_id, C.ou_id,
  535.         A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
  536.         vCurrGL, 0, 'MONTHLY_AVG'
  537.     FROM in_inventory A, in_inventory_item B, m_ou_structure C, dt_date D, vw_raw_materials_product E
  538.     WHERE A.ou_from_id = C.ou_id AND
  539.         C.ou_bu_id = pOuId AND
  540.         A.inventory_id = B.inventory_id AND
  541.         A.doc_date = D.string_date AND
  542.         D.year_month_date = pYearMonth AND
  543.         A.doc_type_id IN (521, 413) AND
  544.         B.qty_realization < 0 AND
  545.         B.product_id = E.product_id
  546.     GROUP BY A.tenant_id, C.ou_bu_id, C.ou_id, A.doc_type_id, B.product_id, B.base_uom_id;
  547. */
  548.     UPDATE tt_in_summary_monthly_product SET gl_amount = ROUND(qty * (A.amount_total / A.qty_total), vRoundingDigit)
  549.     FROM tt_in_summary_monthly_cogs A
  550.     WHERE A.session_id = pSessionId AND
  551.         tt_in_summary_monthly_product.session_id = A.session_id AND
  552.         tt_in_summary_monthly_product.tenant_id = A.tenant_id AND
  553.         tt_in_summary_monthly_product.ou_bu_id = A.ou_id AND
  554.         tt_in_summary_monthly_product.product_id = A.product_id AND
  555.         tt_in_summary_monthly_product.doc_type_id IN (311,431,526,521,413,313,560,537) AND
  556.         tt_in_summary_monthly_product.flg_amount = 'MONTHLY_AVG' AND
  557.         A.qty_total <> 0;
  558.  
  559.     -- WTC, 170502, khusus untuk product tidak ada COGS, maka ambil nilainya dari master harga standar
  560.     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
  561.     FROM tt_in_product_base_price A
  562.     WHERE A.session_id = pSessionId AND
  563.         A.flg_amount = 'STANDARD_PRICE' AND
  564.         tt_in_summary_monthly_product.session_id = A.session_id AND
  565.         tt_in_summary_monthly_product.tenant_id = A.tenant_id AND
  566.         tt_in_summary_monthly_product.ou_bu_id = A.ou_id AND
  567.         tt_in_summary_monthly_product.product_id = A.product_id AND
  568.         tt_in_summary_monthly_product.doc_type_id IN (311,431,526,521,413,313,560,537) AND
  569.         tt_in_summary_monthly_product.flg_amount = 'MONTHLY_AVG' AND
  570.         NOT EXISTS (SELECT 1 FROM tt_in_summary_monthly_cogs B
  571.             WHERE tt_in_summary_monthly_product.session_id = B.session_id AND
  572.                 tt_in_summary_monthly_product.tenant_id = B.tenant_id AND
  573.                 tt_in_summary_monthly_product.ou_bu_id = B.ou_id AND
  574.                 tt_in_summary_monthly_product.product_id = B.product_id AND
  575.                 B.qty_total <> 0);
  576.  
  577. /*
  578.      * untuk menghitung saldo akhir di bulan tersebut, dengan cara membuat record saldo awal untuk bulan berikutnya
  579.      * 1. ambil data adj stok balance amount ( adjust nilai akhir saldo barang )
  580.      * 2. hitung saldo akhir
  581.      */    
  582.     INSERT INTO tt_in_summary_monthly_product
  583.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  584.     doc_type_id, product_id, base_uom_id, qty,
  585.     gl_curr_code, gl_amount, flg_amount)
  586.     SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  587.         A.doc_type_id, A.product_id, A.uom_id, SUM(A.qty),
  588.         A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
  589.     FROM tt_in_doc_product_price A
  590.     WHERE A.session_id = pSessionId AND
  591.         A.doc_type_id = 523
  592.     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;
  593.        
  594.     /*
  595.      * Persiapan data saldo awal untuk bulan berikutnya. Periksa terlebih dahulu apakah ada data yg qty = 0, tetapi gl_amount <> 0.
  596.      * Nilai gl_amount tsb akan dialokasikan ke data transaksi pengeluaran stok dengan id terbesar untuk product ybs.
  597.      */
  598.     INSERT INTO tt_in_summary_monthly_amount_remain
  599.     (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  600.     doc_type_id, product_id, base_uom_id, qty,
  601.     gl_curr_code, gl_amount, flg_amount, summary_monthly_product_id)
  602.     SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
  603.         vDocTypeAwal, A.product_id, A.base_uom_id, SUM(A.qty),
  604.         vCurrGL, SUM(A.gl_amount), 'BGN_BALANCE', vEmptyId
  605.     FROM tt_in_summary_monthly_product A
  606.     WHERE A.session_id = pSessionId
  607.     GROUP BY A.date_year_month, A.tenant_id, A.ou_bu_id, A.product_id, A.base_uom_id;
  608.    
  609.     -- Cari id data sebagai penampung alokasi nilai
  610.     /**
  611.      * Adrian, Feb 08, 2018
  612.      * Menambahkan coalesce, sehingga summary_monthly_product_id tidak null
  613.      */
  614.     UPDATE tt_in_summary_monthly_amount_remain
  615.         SET summary_monthly_product_id = COALESCE((
  616.             SELECT MAX(A.summary_monthly_product_id)
  617.             FROM tt_in_summary_monthly_product A
  618.             WHERE A.session_id = pSessionId AND
  619.                 tt_in_summary_monthly_amount_remain.session_id = A.session_id AND
  620.                 tt_in_summary_monthly_amount_remain.date_year_month = A.date_year_month AND
  621.                 tt_in_summary_monthly_amount_remain.tenant_id = A.tenant_id AND
  622.                 tt_in_summary_monthly_amount_remain.ou_bu_id = A.ou_bu_id AND
  623.                 tt_in_summary_monthly_amount_remain.product_id = A.product_id AND
  624.                 A.doc_type_id IN (311,431,526,521,413,313,560,537) AND
  625.                 A.flg_amount = 'MONTHLY_AVG'),
  626.             -99)
  627.     WHERE session_id = pSessionId AND qty = 0 AND gl_amount <> 0;
  628.    
  629.    
  630.     IF (vGenerateAdjustmentStockAmount = vFlagYes) THEN
  631.    
  632.         /**
  633.          * Adrian, Feb 08, 2018
  634.          * Generate Adjustment Stock Amount untuk tt_in_summary_monthly_amount_remain
  635.          * yang memiliki gl_amount <> 0, qty = 0, dan summary_monthly_product_id = vEmptyId
  636.          */
  637.         INSERT INTO tt_data_summary_monthly_amount_exclude(
  638.             session_id, tenant_id, date_year_month,
  639.             ou_bu_id, ou_branch_id, ou_sub_bu_id,
  640.             product_id, qty, base_uom_id,
  641.             gl_curr_code, gl_amount)
  642.         SELECT pSessionId, A.tenant_id, A.date_year_month,
  643.                 A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  644.                 A.product_id, A.qty AS qty, A.base_uom_id,
  645.                 A.gl_curr_code, -1 * A.gl_amount AS gl_amount
  646.         FROM tt_in_summary_monthly_amount_remain A
  647.         WHERE A.session_id = pSessionId AND
  648.             A.tenant_id = pTenantId AND
  649.             A.gl_amount <> 0 AND
  650.             A.qty = 0 AND
  651.             A.summary_monthly_product_id = vEmptyId;
  652.            
  653.         SELECT COUNT(1) FROM tt_data_summary_monthly_amount_exclude INTO vCountItemToGenerate;
  654.    
  655.         IF (vCountItemToGenerate > 0) THEN
  656.            
  657.             PERFORM in_generate_adj_stock_amount_doc_for_process_costing(pTenantId, pSessionId, pYearMonth, pOuId, pDatetime, pUserId, vGroupProductRM);
  658.            
  659.         END IF;
  660.    
  661.         /*
  662.          * NK, 16 Feb 2015
  663.          * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  664.          * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  665.          */
  666.         /**
  667.          * Adrian, Feb 08, 2018
  668.          * Ubah filter menjadi gl_amount <> 0 dan qty = 0
  669.          */
  670.         INSERT INTO in_summary_monthly_zero_qty_amount
  671.         (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  672.         product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  673.         doc_type_id, qty, gl_amount, remark)
  674.         SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  675.             A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  676.             B.doc_type_id, B.qty, B.gl_amount, vGroupProductRM
  677.         FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
  678.         WHERE A.session_id = pSessionId AND
  679.             A.gl_amount <> 0 AND A.qty = 0 AND
  680.             B.summary_monthly_product_id = A.summary_monthly_product_id;
  681.            
  682.     ELSE
  683.    
  684.         /*
  685.          * NK, 16 Feb 2015
  686.          * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
  687.          * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
  688.          */
  689.         INSERT INTO in_summary_monthly_zero_qty_amount
  690.         (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  691.         product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
  692.         doc_type_id, qty, gl_amount, remark)
  693.         SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  694.             A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
  695.             B.doc_type_id, B.qty, B.gl_amount, vGroupProductRM
  696.         FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
  697.         WHERE A.session_id = pSessionId AND
  698.             A.summary_monthly_product_id <> vEmptyId AND
  699.             B.summary_monthly_product_id = A.summary_monthly_product_id;
  700.            
  701.     END IF;
  702.    
  703.                
  704.     UPDATE tt_in_summary_monthly_product SET gl_amount = tt_in_summary_monthly_product.gl_amount - A.gl_amount
  705.     FROM tt_in_summary_monthly_amount_remain A
  706.     WHERE A.session_id = pSessionId AND
  707.         A.summary_monthly_product_id <> vEmptyId AND
  708.         tt_in_summary_monthly_product.summary_monthly_product_id = A.summary_monthly_product_id;
  709.        
  710.    
  711.     IF ((vGenerateAdjustmentStockAmount = vFlagYes) AND (vCountItemToGenerate > 0)) THEN
  712.        
  713.         /**
  714.          * Adrian, Feb 08, 2018
  715.          * Update tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  716.          * dan product id nya terdaftar di
  717.          * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
  718.          */
  719.         UPDATE tt_in_summary_monthly_product Z
  720.         SET gl_amount = Z.gl_amount - A.gl_amount
  721.         FROM tt_in_summary_monthly_amount_remain A
  722.         WHERE Z.session_id = pSessionId AND
  723.             Z.tenant_id = pTenantId AND
  724.             Z.session_id = A.session_id AND
  725.             Z.date_year_month = A.date_year_month AND
  726.             Z.tenant_id = A.tenant_id AND
  727.             Z.ou_bu_id = A.ou_bu_id AND
  728.             Z.product_id = A.product_id AND
  729.             Z.doc_type_id = 522 AND
  730.             Z.flg_amount = 'TRANSACTION' AND
  731.             A.gl_amount <> 0 AND
  732.             A.qty = 0 AND
  733.             A.summary_monthly_product_id = vEmptyId;
  734.            
  735.         /**
  736.          * Adrian, Feb 08, 2018
  737.          * Insert tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  738.          * jika product id yang terdaftar di
  739.          * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
  740.          * belum terdapat dalam tt_in_summary_monthly_product yang memiliki doc_type_id = 522
  741.          */
  742.         INSERT INTO tt_in_summary_monthly_product(
  743.                 session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  744.                 doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
  745.         SELECT A.session_id, pYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  746.             522, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, (-1 * A.gl_amount), 'TRANSACTION'
  747.         FROM tt_in_summary_monthly_amount_remain A
  748.         WHERE A.session_id = pSessionId AND
  749.             A.tenant_id = pTenantId AND
  750.             A.gl_amount <> 0 AND
  751.             A.qty = 0 AND
  752.             A.summary_monthly_product_id = vEmptyId AND
  753.             NOT EXISTS(
  754.                 SELECT 1
  755.                 FROM tt_in_summary_monthly_product B
  756.                 WHERE B.session_id = A.session_id AND
  757.                     B.date_year_month = A.date_year_month AND
  758.                     B.tenant_id = A.tenant_id AND
  759.                     B.ou_bu_id = A.ou_bu_id AND
  760.                     B.product_id = A.product_id AND
  761.                     B.doc_type_id = 522 AND
  762.                     B.flg_amount = 'TRANSACTION'
  763.             );
  764.            
  765.     END IF;
  766.        
  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, vNextYearMonth, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  772.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, A.gl_amount, A.flg_amount
  773.     FROM tt_in_summary_monthly_amount_remain A
  774.     WHERE A.session_id = pSessionId AND A.qty <> 0;
  775.                
  776.     INSERT INTO in_summary_monthly_cogs
  777.     (date_year_month, tenant_id, ou_id, product_id,
  778.     qty_total, curr_code, amount_total, avg_price,
  779.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  780.     SELECT A.date_year_month, A.tenant_id, A.ou_id, A.product_id,
  781.             A.qty_total, A.curr_code, A.amount_total, A.avg_price,
  782.             0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  783.     FROM tt_in_summary_monthly_cogs A
  784.     WHERE A.session_id = pSessionId;
  785.        
  786.     INSERT INTO in_summary_monthly_amount
  787.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  788.     doc_type_id, product_id, base_uom_id, qty,
  789.     gl_curr_code, gl_amount, flg_amount,
  790.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  791.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  792.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  793.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  794.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  795.     FROM tt_in_summary_monthly_product A
  796.     WHERE A.session_id = pSessionId AND
  797.             A.date_year_month = pYearMonth AND
  798.             A.doc_type_id NOT IN (vDocTypeAwal, 111);
  799.    
  800.     --insert data anomaly yg amount/qty < 0
  801.     INSERT INTO in_anomaly_gl_amount_from_process_costing
  802.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  803.     doc_type_id, product_id, base_uom_id, qty,
  804.     gl_curr_code, gl_amount, flg_amount,
  805.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  806.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  807.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  808.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  809.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  810.     FROM tt_in_summary_monthly_product A
  811.     WHERE A.session_id = pSessionId AND
  812.             A.date_year_month = pYearMonth AND
  813.             A.doc_type_id NOT IN (vDocTypeAwal, 111) AND
  814.             A.gl_amount / A.qty < 0 AND
  815.             A.qty <> 0;
  816.  
  817.     INSERT INTO in_summary_monthly_amount
  818.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  819.     doc_type_id, product_id, base_uom_id, qty,
  820.     gl_curr_code, gl_amount, flg_amount,
  821.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  822.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  823.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  824.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  825.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  826.     FROM tt_in_summary_monthly_product A
  827.     WHERE A.session_id = pSessionId AND
  828.         A.date_year_month = vNextYearMonth;
  829.    
  830.     --insert data anomaly yg amount/qty < 0 untuk saldo awal bulan berikutnya
  831.     INSERT INTO in_anomaly_gl_amount_from_process_costing
  832.     (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
  833.     doc_type_id, product_id, base_uom_id, qty,
  834.     gl_curr_code, gl_amount, flg_amount,
  835.     "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
  836.     SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
  837.         A.doc_type_id, A.product_id, A.base_uom_id, A.qty,
  838.         A.gl_curr_code, A.gl_amount, A.flg_amount,
  839.         0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductRM
  840.     FROM tt_in_summary_monthly_product A
  841.     WHERE A.session_id = pSessionId AND
  842.         A.date_year_month = vNextYearMonth AND
  843.             A.gl_amount/ A.qty < 0 AND
  844.             A.qty <> 0;
  845.        
  846.     DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;
  847.    
  848.     DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;
  849.    
  850.     DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;
  851.    
  852.     DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;
  853.    
  854.     DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;
  855.    
  856.     DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;
  857.    
  858.  END;
  859. $BODY$
  860.   LANGUAGE plpgsql VOLATILE
  861.   COST 100;
  862.   /
  863.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement