abirama62

in_process_costing_finish_goods

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