tercnem

in_process_costing_finish_goods

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