Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- WTC, 160212, menambahkan costing untuk tipe dokumen DO MLM, return/buyback, dan exchange
- -- WTC, 161113, saat summary untuk membuat saldo awal bulan berikutnya, ubah filter qty menjadi qty <> 0 (sblmnya qty > 0)
- -- WTC, 170418, nilai claim note diperhitungkan sebagai pengurang perhitungan COGS
- -- WTC, 170502, khusus untuk product tidak ada COGS, maka ambil nilainya dari master harga standar
- CREATE OR REPLACE FUNCTION in_process_costing_finish_goods(bigint, character varying, character varying, bigint, character varying, bigint)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pYearMonth ALIAS FOR $3;
- pOuId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pUserId ALIAS FOR $6;
- vNextYearMonth character varying(6);
- vCurrGL character varying(5);
- vFlagNo character varying(1);
- vFlagYes character varying(1);
- vEmptyValue character varying(1);
- vEmptyId bigint;
- vDocTypeAwal bigint;
- vGroupProductFG character varying(5);
- vRoundingDigit integer;
- vTypeRate character varying(5);
- vStatusRelease character varying(1);
- vGenerateAdjustmentStockAmount character varying(1);
- vParamGenerateAdjustmentStockAmount character varying := 'generate.adjustment.stock.amount.on.costing.finish.goods';
- vCountItemToGenerate bigint;
- BEGIN
- vFlagNo := 'N';
- vFlagYes := 'Y';
- vDocTypeAwal := -99;
- vEmptyId := -99;
- vEmptyValue := ' ';
- vGroupProductFG := 'FG';
- vTypeRate := 'COM';
- vStatusRelease := 'R';
- vCountItemToGenerate := 0;
- SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
- vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
- vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
- vGenerateAdjustmentStockAmount := f_get_value_system_config_by_param_code(pTenantId,vParamGenerateAdjustmentStockAmount);
- DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;
- DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;
- DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;
- DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;
- DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;
- DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;
- DELETE FROM tt_data_summary_monthly_amount_exclude WHERE session_id = pSessionId;
- /*
- * costing product hanya berdasarkan ou business unit, jadi untuk sementara sub_ou_id diisi dengan -99
- */
- /*
- * membuat summary nilai pembelian berdasarkan hasil jurnal
- * untuk transaksi receive goods
- * NK, 16 Feb 2015 : Pembelian Internal menghasilkan dokumen semua sesuai doc type Receive Goods
- * Receive Goods : 111
- *
- * Ali, 22 Nov
- * Penambahan untuk exclude RG yang nilai Qty-nya minus
- */
- INSERT INTO in_summary_monthly_amount
- (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
- SELECT pYearMonth, 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, SUM(C.qty),
- C.gl_curr_code, SUM(CASE WHEN C.sign_journal = 'D' THEN C.gl_amount ELSE -1 * C.gl_amount END), 'PURCH_PRICE',
- 0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
- FROM gl_journal_trx A, dt_date B, gl_journal_trx_item C, m_document_journal D, vw_finish_goods_product E
- WHERE A.tenant_id = pTenantId AND
- A.ou_bu_id = pOuId AND
- A.status_doc = vStatusRelease AND
- A.doc_date = B.string_date AND
- B.year_month_date = pYearMonth AND
- A.journal_trx_id = C.journal_trx_id AND
- A.doc_type_id = D.doc_type_id AND
- D.ledger_code IN ('PURCH') AND
- C.journal_desc = 'PRODUCT_STOCK' AND
- A.doc_type_id IN (111) AND
- C.product_id = E.product_id AND
- C.qty > 0
- 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;
- /*
- * ambil data receive goods dari in_summary_monthly_amount yang product Finish Goods
- * NK, 16 Feb 2015 : receive goods internal menggunakan doc type receive goods
- */
- INSERT INTO tt_in_doc_product_price
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
- doc_type_id, ref_id, doc_no, doc_date,
- curr_code, amount, qty, uom_id,
- numerator_rate, denominator_rate,
- gl_curr_code, gl_amount)
- SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.product_id,
- A.doc_type_id, vEmptyId, vEmptyValue, vEmptyValue,
- A.gl_curr_code, A.gl_amount, A.qty, A.base_uom_id,
- 1, 1,
- A.gl_curr_code, A.gl_amount
- FROM in_summary_monthly_amount A, vw_finish_goods_product E
- WHERE A.date_year_month = pYearMonth AND
- A.doc_type_id IN (111) AND
- A.tenant_id = pTenantId AND
- A.ou_bu_id = pOuId AND
- A.product_id = E.product_id;
- /*
- * ambil data adjusment stok amount
- * jika curr <> curr G/L, maka lakukan hitung rate untuk ke nilai sesuai valuta G/L
- * 522 : adj stock amount ( mempengaruhi COGS )
- * 523 : adj stock amount balance ( mempengaruhi saldo akhir )
- * 528 : costing allocation to product ( mempengaruhi COGS )
- * 511 : claim note (mempengaruhi COGS), added by WTC 170418
- */
- INSERT INTO tt_in_doc_product_price
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
- doc_type_id, ref_id, doc_no, doc_date,
- curr_code, amount, qty, uom_id,
- numerator_rate, denominator_rate,
- gl_curr_code, gl_amount)
- SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
- A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.curr_code, A.amount, A.qty, A.uom_id,
- 1, 1, vCurrGL, ROUND( A.amount, vRoundingDigit)
- FROM in_product_price_balance A, m_ou_structure B, dt_date C, vw_finish_goods_product E
- WHERE A.doc_date = C.string_date AND
- C.year_month_date = pYearMonth AND
- A.tenant_id = pTenantId AND
- A.ou_id = B.ou_id AND
- B.ou_bu_id = pOuId AND
- A.doc_type_id IN (522, 523, 528) AND
- A.product_id = E.product_id;
- -- hitung nilai pembukuan untuk transaksi adjusment stok amount
- UPDATE tt_in_doc_product_price SET numerator_rate = B.amount_to, denominator_rate = B.amount_from
- FROM m_exchange_rate B
- WHERE tt_in_doc_product_price.session_id = pSessionId AND
- tt_in_doc_product_price.tenant_id = B.tenant_id AND
- B.type_exchange_rate = vTypeRate AND
- tt_in_doc_product_price.doc_date = B.date_from AND
- tt_in_doc_product_price.curr_code = B.curr_code_from AND
- tt_in_doc_product_price.gl_curr_code = B.curr_code_to AND
- tt_in_doc_product_price.doc_type_id IN (522, 523, 528) AND
- tt_in_doc_product_price.curr_code <> vCurrGL;
- UPDATE tt_in_doc_product_price SET gl_amount = ROUND(amount * numerator_rate / denominator_rate, vRoundingDigit)
- WHERE tt_in_doc_product_price.session_id = pSessionId AND
- tt_in_doc_product_price.doc_type_id IN (522, 523, 528) AND
- tt_in_doc_product_price.curr_code <> vCurrGL;
- /*
- * Moved by WTC, 170418, sblmnya tidak diperhitungkan sbg pengurang COGS.
- * membuat summary nilai claim note berdasarkan hasil jurnal
- * Claim Note : 511
- */
- INSERT INTO tt_in_doc_product_price
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
- doc_type_id, ref_id, doc_no, doc_date,
- curr_code, amount, qty, uom_id,
- numerator_rate, denominator_rate,
- gl_curr_code, gl_amount)
- SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, C.product_id,
- A.doc_type_id, vEmptyId, vEmptyValue, vEmptyValue,
- C.gl_curr_code, -1 * C.gl_amount, -1 * C.qty, C.uom_id,
- 1, 1,
- C.gl_curr_code, -1 * C.gl_amount
- FROM gl_journal_trx A, dt_date B, gl_journal_trx_item C, m_document_journal D, vw_finish_goods_product E
- WHERE A.tenant_id = pTenantId AND
- A.ou_bu_id = pOuId AND
- A.status_doc = vStatusRelease AND
- A.doc_date = B.string_date AND
- B.year_month_date = pYearMonth AND
- A.journal_trx_id = C.journal_trx_id AND
- A.doc_type_id = D.doc_type_id AND
- D.ledger_code IN ('INV') AND
- C.journal_desc = 'PRODUCT_STOCK' AND
- A.doc_type_id IN (511) AND
- C.product_id = E.product_id;
- /**
- * Ali, 22 Nov, dikarenakan RG Qty Minus diexclude perlu dilakukan penambahan data.
- *
- */
- INSERT INTO tt_in_doc_product_price
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
- doc_type_id, ref_id, doc_no, doc_date,
- curr_code, amount, qty, uom_id,
- numerator_rate, denominator_rate,
- gl_curr_code, gl_amount)
- SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, C.product_id,
- A.doc_type_id, vEmptyId, vEmptyValue, vEmptyValue,
- 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,
- 1, 1,
- C.gl_curr_code, SUM(CASE WHEN C.sign_journal = 'D' THEN C.gl_amount ELSE -1 * C.gl_amount END)
- FROM gl_journal_trx A, dt_date B, gl_journal_trx_item C, m_document_journal D, vw_finish_goods_product E
- WHERE A.tenant_id = pTenantId AND
- A.ou_bu_id = pOuId AND
- A.status_doc = vStatusRelease AND
- A.doc_date = B.string_date AND
- B.year_month_date = pYearMonth AND
- A.journal_trx_id = C.journal_trx_id AND
- A.doc_type_id = D.doc_type_id AND
- D.ledger_code IN ('PURCH') AND
- C.journal_desc = 'PRODUCT_STOCK' AND
- A.doc_type_id IN (111) AND
- C.product_id = E.product_id AND
- C.qty < 0
- 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;
- /*
- * WTC, 160211, tambahkan dokumen return/buyback
- * product non assembly
- * ambil data transaksi yg tambah stok, tp belum memiliki nilai
- * 1.return note : 502
- * 2.adj stok qty plus (non konsinyasi) : 521
- * 3.adj stok outlet qty plus (non konsinyasi) : 413
- * 4.Return MLM : 562
- * 5.Buyback MLM : 563
- * 6.Exchange MLM in : 560
- */
- INSERT INTO tt_in_add_product_unamount
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
- doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
- A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
- FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_finish_goods_product E
- WHERE A.ou_id = B.ou_id AND
- B.ou_bu_id = pOuId AND
- A.date_year_month = pYearMonth AND
- A.doc_type_id IN ( 502, 562, 563 ) AND
- A.product_id = E.product_id;
- INSERT INTO tt_in_add_product_unamount
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
- doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
- A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
- FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_finish_goods_product E
- WHERE A.ou_id = B.ou_id AND
- B.ou_bu_id = pOuId AND
- A.date_year_month = pYearMonth AND
- A.doc_type_id IN ( 521, 413 ) AND
- A.product_id = E.product_id AND
- A.qty > 0 AND
- A.flg_buy_konsinyasi = vFlagNo;
- -- WTC, 160621, ambil data exchange MLM in, baik yg konsinyasi maupun non konsinyasi
- INSERT INTO tt_in_add_product_unamount
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id, product_id,
- doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, A.tenant_id, B.ou_bu_id, B.ou_branch_id, B.ou_sub_bu_id, A.product_id,
- A.doc_type_id, A.qty, A.base_uom_id, vCurrGL, 0, vEmptyValue
- FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_finish_goods_product E
- WHERE A.ou_id = B.ou_id AND
- B.ou_bu_id = pOuId AND
- A.date_year_month = pYearMonth AND
- A.doc_type_id = 560 AND
- A.product_id = E.product_id AND
- A.qty > 0;
- /* NK, 25 Jan 2014, di submit adj stok qty diubah sehingga log product balance stock akan ada 2 kelompok,
- * kelompok yang qty > 0 dan qty < 0
- INSERT INTO tt_in_add_product_unamount
- (session_id, tenant_id, ou_id, sub_ou_id, product_id,
- doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id, B.product_id,
- A.doc_type_id, B.qty_realization, B.base_uom_id, vCurrGL, 0, vEmptyValue
- FROM in_inventory A, in_inventory_item B, dt_date C, m_ou_structure D, vw_finish_goods_product E
- WHERE A.tenant_id = pTenantId AND
- A.doc_date = C.string_date AND
- C.year_month_date = pYearMonth AND
- A.inventory_id = B.inventory_id AND
- A.ou_from_id = D.ou_id AND
- D.ou_bu_id = pOuId AND
- A.doc_type_id = 521 AND
- B.qty_realization > 0 AND
- B.product_id = E.product_id;
- INSERT INTO tt_in_add_product_unamount
- (session_id, tenant_id, ou_id, sub_ou_id, product_id,
- doc_type_id, qty, uom_id, gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, A.tenant_id, D.ou_bu_id, D.ou_id, B.product_id,
- A.doc_type_id, B.qty_realization, B.base_uom_id, vCurrGL, 0, vEmptyValue
- FROM i_trx_inventory A, i_trx_inventory_item B, dt_date C, m_ou_structure D, vw_finish_goods_product E
- WHERE A.tenant_id = pTenantId AND
- A.doc_date = C.string_date AND
- C.year_month_date = pYearMonth AND
- A.trx_inventory_id = B.trx_inventory_id AND
- A.ou_id = D.ou_id AND
- D.ou_bu_id = pOuId AND
- A.doc_type_id = 413 AND
- B.qty_realization > 0 AND
- B.product_id = E.product_id;
- */
- /*
- * buat data yang akan menjadi acuan harga standard
- * 1.data purch price di bulan bersangkutan
- * 2.data nilai barang di awal bulan
- * 3.data master harga standard
- */
- INSERT INTO tt_in_product_base_price
- (session_id, tenant_id, ou_id, product_id,
- flg_amount, qty, uom_id, gl_curr_code, gl_amount)
- SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.product_id,
- 'PURCH_PRICE', SUM(A.qty), A.uom_id, A.gl_curr_code, SUM(A.gl_amount)
- FROM tt_in_doc_product_price A
- WHERE A.session_id = pSessionId AND
- A.doc_type_id IN ( 111)
- GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.product_id,
- A.uom_id, A.gl_curr_code;
- INSERT INTO tt_in_product_base_price
- (session_id, tenant_id, ou_id, product_id,
- flg_amount, qty, uom_id, gl_curr_code, gl_amount)
- SELECT pSessionId, A.tenant_id, A.ou_bu_id, A.product_id,
- 'BGN_BALANCE', SUM(A.qty), A.base_uom_id, A.gl_curr_code, SUM(A.gl_amount)
- FROM in_summary_monthly_amount A, vw_finish_goods_product E
- WHERE A.date_year_month = pYearMonth AND
- A.doc_type_id = vDocTypeAwal AND
- A.tenant_id = pTenantId AND
- A.ou_bu_id = pOuId AND
- A.product_id = E.product_id
- GROUP BY A.tenant_id, A.ou_bu_id, A.product_id,
- A.base_uom_id, A.gl_curr_code;
- INSERT INTO tt_in_product_base_price
- (session_id, tenant_id, ou_id, product_id,
- flg_amount, qty, uom_id, gl_curr_code, gl_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.product_id,
- 'STANDARD_PRICE', 0, E.base_uom_id, A.curr_code, SUM(A.amount)
- FROM in_product_standard_cogs A, vw_finish_goods_product E
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.year_month_date = pYearMonth AND
- A.product_id = E.product_id
- GROUP BY A.tenant_id, A.ou_id, A.product_id, A.curr_code, E.base_uom_id;
- INSERT INTO tt_in_product_base_price
- (session_id, tenant_id, ou_id, product_id,
- flg_amount, qty, uom_id, gl_curr_code, gl_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.product_id,
- 'PREV_MONTH_COGS', A.qty_total, E.base_uom_id, A.curr_code, A.amount_total
- FROM in_summary_monthly_cogs A, vw_finish_goods_product E
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.product_id = E.product_id AND
- A.date_year_month = (SELECT MAX(Z.date_year_month)
- FROM in_summary_monthly_cogs Z
- WHERE A.tenant_id = Z.tenant_id AND
- Z.date_year_month < pYearMonth AND
- Z.ou_id = A.ou_id AND
- Z.product_id = A.product_id AND
- Z.qty_total <> 0);
- /*
- * isi nilai data tt_in_add_product_unamount dengan urutan :
- * 1. update dengan data flg amount STANDARD_PRICE
- * 2. jika belum ada, update dengan data flg amount BGN_BALANCE
- * 3. jika belum ada, update dengan data flg amount PURCH_PRICE
- * 4. jika belum ada, update dengan data flg amount PREV_MONTH_COGS
- */
- UPDATE tt_in_add_product_unamount SET gl_amount = tt_in_add_product_unamount.qty * A.gl_amount, flg_amount = A.flg_amount
- FROM tt_in_product_base_price A
- WHERE A.session_id = pSessionId AND
- tt_in_add_product_unamount.session_id = A.session_id AND
- tt_in_add_product_unamount.tenant_id = A.tenant_id AND
- tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
- tt_in_add_product_unamount.product_id = A.product_id AND
- tt_in_add_product_unamount.flg_amount = vEmptyValue AND
- A.flg_amount = 'STANDARD_PRICE';
- 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
- FROM tt_in_product_base_price A
- WHERE A.session_id = pSessionId AND
- tt_in_add_product_unamount.session_id = A.session_id AND
- tt_in_add_product_unamount.tenant_id = A.tenant_id AND
- tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
- tt_in_add_product_unamount.product_id = A.product_id AND
- tt_in_add_product_unamount.flg_amount = vEmptyValue AND
- A.flg_amount = 'BGN_BALANCE' AND
- A.qty <> 0;
- 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
- FROM tt_in_product_base_price A
- WHERE A.session_id = pSessionId AND
- tt_in_add_product_unamount.session_id = A.session_id AND
- tt_in_add_product_unamount.tenant_id = A.tenant_id AND
- tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
- tt_in_add_product_unamount.product_id = A.product_id AND
- tt_in_add_product_unamount.flg_amount = vEmptyValue AND
- A.flg_amount = 'PURCH_PRICE' AND
- A.qty <> 0;
- -- Ambil dari monthly cogs terakhir
- 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
- FROM tt_in_product_base_price A
- WHERE A.session_id = pSessionId AND
- tt_in_add_product_unamount.session_id = A.session_id AND
- tt_in_add_product_unamount.tenant_id = A.tenant_id AND
- tt_in_add_product_unamount.ou_bu_id = A.ou_id AND
- tt_in_add_product_unamount.product_id = A.product_id AND
- tt_in_add_product_unamount.flg_amount = vEmptyValue AND
- A.flg_amount = 'PREV_MONTH_COGS' AND
- A.qty <> 0;
- /*
- * menghitung harga rata rata product (non assembly ) berdasarkan :
- * 1. harga purchasing
- * 2. harga adj stock amount ( bukan yg balance amount ), cost allocation product
- * 3. transaksi tambah stok qty, dengan harga sudah diproses : retur jual, adj stok qty > 0
- * 4. harga saldo awal
- */
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, 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, SUM(A.qty),
- A.gl_curr_code, SUM(A.gl_amount), 'PURCH_PRICE'
- FROM tt_in_doc_product_price A
- WHERE A.session_id = pSessionId AND
- A.doc_type_id IN (111) AND
- A.qty > 0
- 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;
- -- Mod by WTC, 20170418, transaksi claim note juga ikut diambil
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, 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, SUM(A.qty),
- A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
- FROM tt_in_doc_product_price A
- WHERE A.session_id = pSessionId AND
- (A.doc_type_id IN ( 522, 528, 511 ) OR (A.doc_type_id = 111 AND A.qty < 0))
- 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;
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, 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, SUM(A.qty),
- A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
- FROM tt_in_add_product_unamount A
- WHERE A.session_id = pSessionId
- 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;
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, 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, SUM(A.qty),
- A.gl_curr_code, SUM(A.gl_amount), A.flg_amount
- FROM in_summary_monthly_amount A, vw_finish_goods_product E
- WHERE A.date_year_month = pYearMonth AND
- A.doc_type_id = vDocTypeAwal AND
- A.ou_bu_id = pOuId AND
- A.tenant_id = pTenantId AND
- A.product_id = E.product_id
- 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;
- /*
- * hitung harga rata-rata sebagai dasar harga pokok
- */
- INSERT INTO tt_in_summary_monthly_cogs
- (session_id, date_year_month, tenant_id, ou_id,
- product_id, qty_total, curr_code, amount_total, avg_price)
- SELECT pSessionId, pYearMonth, A.tenant_id, A.ou_bu_id,
- A.product_id, SUM(A.qty), A.gl_curr_code, SUM(A.gl_amount), 0
- FROM tt_in_summary_monthly_product A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_bu_id, A.product_id, A.gl_curr_code;
- UPDATE tt_in_summary_monthly_cogs
- SET avg_price = amount_total / qty_total
- WHERE session_id = pSessionId AND
- qty_total <> 0;
- /*
- * memberikan nilai untuk transaksi stok yang mengurangi qty, sesuai harga rata rata bulan ini
- * transaksi selain adj stok qty(521) dan adj stok qty outlet(413) , yang qty < 0
- * transaksi pemakaian Nota klaim, DO, DO Internal, Sales Invoice Konsinyasi, POS Shop, Return POS Shop, Pos Shop In Shop, Return POS Shop In Shop
- * transaksi pemakaian Nota klaim (511), DO (311) , POS Shop (431), Pos Shop In Shop(431)
- * NK, 16 Feb 2015 : tambahan transaksi DO Receipt ( 526 )
- * Sales Invoice Konsinyasi(354), DO Internal tidak jadi dipakai(154)
- * Return POS Shop, Return POS SHop In SHop belum ada
- * WTC, 160622 : tambahan transaksi GTI receipt lost ( 537 )
- * WTC, 160211 : tambahan transaksi DO MLM ( 313 ), exchange MLM out (560), exclude claim
- */
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, 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, SUM(A.qty),
- vCurrGL, 0, 'MONTHLY_AVG'
- FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_finish_goods_product E
- WHERE A.ou_id = B.ou_id AND
- B.ou_bu_id = pOuId AND
- A.date_year_month = pYearMonth AND
- A.doc_type_id IN (311,431,526,313,537) AND
- A.product_id = E.product_id
- 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;
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, 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, SUM(A.qty),
- vCurrGL, 0, 'MONTHLY_AVG'
- FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_finish_goods_product E
- WHERE A.ou_id = B.ou_id AND
- B.ou_bu_id = pOuId AND
- A.date_year_month = pYearMonth AND
- A.doc_type_id IN (521, 413) AND
- A.product_id = E.product_id AND
- A.qty < 0 AND
- A.flg_buy_konsinyasi = vFlagNo
- 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;
- -- WTC, 160622, ambil data exchange MLM out
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, 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, SUM(A.qty),
- vCurrGL, 0, 'MONTHLY_AVG'
- FROM in_combine_log_product_balance_stock A, m_ou_structure B, vw_finish_goods_product E
- WHERE A.ou_id = B.ou_id AND
- B.ou_bu_id = pOuId AND
- A.date_year_month = pYearMonth AND
- A.doc_type_id = 560 AND
- A.product_id = E.product_id AND
- A.qty < 0
- 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;
- /* NK, 25 Jan 2014, submit adj stok qty diubah sehingga menghasilkan 2 jenis log data product balance stock
- * qty < 0 dan qty > 0 dipisahkan
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_id, sub_ou_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, A.tenant_id, C.ou_bu_id, C.ou_id,
- A.doc_type_id, B.product_id, B.base_uom_id, SUM(B.qty_realization),
- vCurrGL, 0, 'MONTHLY_AVG'
- FROM in_inventory A, in_inventory_item B, m_ou_structure C, dt_date D, vw_finish_goods_product E
- WHERE A.ou_from_id = C.ou_id AND
- C.ou_bu_id = pOuId AND
- A.inventory_id = B.inventory_id AND
- A.doc_date = D.string_date AND
- D.date_year_month = pYearMonth AND
- A.doc_type_id IN (521, 413) AND
- B.qty_realization < 0 AND
- B.product_id = E.product_id
- GROUP BY A.tenant_id, C.ou_bu_id, C.ou_id, A.doc_type_id, B.product_id, B.base_uom_id;
- */
- UPDATE tt_in_summary_monthly_product SET gl_amount = ROUND(qty * (A.amount_total / A.qty_total), vRoundingDigit)
- FROM tt_in_summary_monthly_cogs A
- WHERE A.session_id = pSessionId AND
- tt_in_summary_monthly_product.session_id = A.session_id AND
- tt_in_summary_monthly_product.tenant_id = A.tenant_id AND
- tt_in_summary_monthly_product.ou_bu_id = A.ou_id AND
- tt_in_summary_monthly_product.product_id = A.product_id AND
- tt_in_summary_monthly_product.doc_type_id IN (311,431,526,521,413,313,560,537) AND
- tt_in_summary_monthly_product.flg_amount = 'MONTHLY_AVG' AND
- A.qty_total <> 0;
- -- WTC, 170502, khusus untuk product tidak ada COGS, maka ambil nilainya dari master harga standar
- 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
- FROM tt_in_product_base_price A
- WHERE A.session_id = pSessionId AND
- A.flg_amount = 'STANDARD_PRICE' AND
- tt_in_summary_monthly_product.session_id = A.session_id AND
- tt_in_summary_monthly_product.tenant_id = A.tenant_id AND
- tt_in_summary_monthly_product.ou_bu_id = A.ou_id AND
- tt_in_summary_monthly_product.product_id = A.product_id AND
- tt_in_summary_monthly_product.doc_type_id IN (311,431,526,521,413,313,560,537) AND
- tt_in_summary_monthly_product.flg_amount = 'MONTHLY_AVG' AND
- NOT EXISTS (SELECT 1 FROM tt_in_summary_monthly_cogs B
- WHERE tt_in_summary_monthly_product.session_id = B.session_id AND
- tt_in_summary_monthly_product.tenant_id = B.tenant_id AND
- tt_in_summary_monthly_product.ou_bu_id = B.ou_id AND
- tt_in_summary_monthly_product.product_id = B.product_id AND
- B.qty_total <> 0);
- /*
- * untuk menghitung saldo akhir di bulan tersebut, dengan cara membuat record saldo awal untuk bulan berikutnya
- * 1. ambil data adj stok balance amount ( adjust nilai akhir saldo barang )
- * 2. hitung saldo akhir
- */
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount)
- SELECT pSessionId, pYearMonth, 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, SUM(A.qty),
- A.gl_curr_code, SUM(A.gl_amount), 'TRANSACTION'
- FROM tt_in_doc_product_price A
- WHERE A.session_id = pSessionId AND
- A.doc_type_id = 523
- 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;
- /*
- * Persiapan data saldo awal untuk bulan berikutnya. Periksa terlebih dahulu apakah ada data yg qty = 0, tetapi gl_amount <> 0.
- * Nilai gl_amount tsb akan dialokasikan ke data transaksi pengeluaran stok dengan id terbesar untuk product ybs.
- */
- INSERT INTO tt_in_summary_monthly_amount_remain
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount, summary_monthly_product_id)
- SELECT pSessionId, A.date_year_month, A.tenant_id, A.ou_bu_id, vEmptyId, vEmptyId,
- vDocTypeAwal, A.product_id, A.base_uom_id, SUM(A.qty),
- vCurrGL, SUM(A.gl_amount), 'BGN_BALANCE', vEmptyId
- FROM tt_in_summary_monthly_product A
- WHERE A.session_id = pSessionId
- GROUP BY A.date_year_month, A.tenant_id, A.ou_bu_id, A.product_id, A.base_uom_id;
- -- Cari id data sebagai penampung alokasi nilai
- /**
- * Adrian, Feb 08, 2018
- * Menambahkan coalesce, sehingga summary_monthly_product_id tidak null
- */
- UPDATE tt_in_summary_monthly_amount_remain
- SET summary_monthly_product_id = COALESCE((
- SELECT MAX(A.summary_monthly_product_id)
- FROM tt_in_summary_monthly_product A
- WHERE A.session_id = pSessionId AND
- tt_in_summary_monthly_amount_remain.session_id = A.session_id AND
- tt_in_summary_monthly_amount_remain.date_year_month = A.date_year_month AND
- tt_in_summary_monthly_amount_remain.tenant_id = A.tenant_id AND
- tt_in_summary_monthly_amount_remain.ou_bu_id = A.ou_bu_id AND
- tt_in_summary_monthly_amount_remain.product_id = A.product_id AND
- A.doc_type_id IN (311,431,526,521,413,313,560,537) AND
- A.flg_amount = 'MONTHLY_AVG'),
- -99)
- WHERE session_id = pSessionId AND qty = 0 AND gl_amount <> 0;
- IF (vGenerateAdjustmentStockAmount = vFlagYes) THEN
- /**
- * Adrian, Feb 08, 2018
- * Generate Adjustment Stock Amount untuk tt_in_summary_monthly_amount_remain
- * yang memiliki gl_amount <> 0, qty = 0, dan summary_monthly_product_id = vEmptyId
- */
- INSERT INTO tt_data_summary_monthly_amount_exclude(
- session_id, tenant_id, date_year_month,
- ou_bu_id, ou_branch_id, ou_sub_bu_id,
- product_id, qty, base_uom_id,
- gl_curr_code, gl_amount)
- SELECT pSessionId, A.tenant_id, A.date_year_month,
- A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
- A.product_id, SUM(A.qty) AS qty, A.base_uom_id,
- A.gl_curr_code, (-1 * SUM(A.gl_amount)) AS gl_amount
- FROM tt_in_summary_monthly_amount_remain A
- WHERE A.session_id = pSessionId AND
- A.tenant_id = pTenantId AND
- A.gl_amount <> 0 AND
- A.qty = 0 AND
- A.summary_monthly_product_id = vEmptyId
- GROUP BY A.tenant_id, A.date_year_month, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id, A.product_id, A.base_uom_id, A.gl_curr_code
- HAVING SUM(qty) = 0
- AND SUM(gl_amount) <> 0;
- SELECT COUNT(1) FROM tt_data_summary_monthly_amount_exclude INTO vCountItemToGenerate;
- IF (vCountItemToGenerate > 0) THEN
- PERFORM in_generate_adj_stock_amount_doc_for_process_costing(pTenantId, pSessionId, pYearMonth, pOuId, pDatetime, pUserId, vGroupProductFG);
- END IF;
- /*
- * NK, 16 Feb 2015
- * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
- * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
- */
- /**
- * Adrian, Feb 08, 2018
- * Ubah filter menjadi gl_amount <> 0 dan qty = 0
- */
- INSERT INTO in_summary_monthly_zero_qty_amount
- (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
- doc_type_id, qty, gl_amount, remark)
- SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
- A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
- B.doc_type_id, B.qty, B.gl_amount, vGroupProductFG
- FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
- WHERE A.session_id = pSessionId AND
- --A.summary_monthly_product_id <> vEmptyId AND
- A.gl_amount <> 0 AND A.qty = 0 AND
- B.summary_monthly_product_id = A.summary_monthly_product_id;
- ELSE
- /*
- * NK, 16 Feb 2015
- * simpan terlebih dahulu data product yang qty = 0, dan nilai <> 0
- * dimana nilai dari product tersebut akan dialokasi ke trx yang menggunakan produk tersebut
- */
- INSERT INTO in_summary_monthly_zero_qty_amount
- (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- product_id, qty_sum, base_uom_id, gl_curr_code, amount_sum,
- doc_type_id, qty, gl_amount, remark)
- SELECT A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
- A.product_id, A.qty, A.base_uom_id, A.gl_curr_code, A.gl_amount,
- vEmptyId, B.qty, B.gl_amount, vGroupProductFG
- FROM tt_in_summary_monthly_amount_remain A, tt_in_summary_monthly_product B
- WHERE A.session_id = pSessionId AND
- A.summary_monthly_product_id <> vEmptyId AND
- B.summary_monthly_product_id = A.summary_monthly_product_id;
- END IF;
- UPDATE tt_in_summary_monthly_product SET gl_amount = tt_in_summary_monthly_product.gl_amount - A.gl_amount
- FROM tt_in_summary_monthly_amount_remain A
- WHERE A.session_id = pSessionId AND
- A.summary_monthly_product_id <> vEmptyId AND
- tt_in_summary_monthly_product.summary_monthly_product_id = A.summary_monthly_product_id;
- IF ((vGenerateAdjustmentStockAmount = vFlagYes) AND (vCountItemToGenerate > 0)) THEN
- /**
- * Adrian, Feb 08, 2018
- * Update tt_in_summary_monthly_product yang memiliki doc_type_id = 522
- * dan product id nya terdaftar di
- * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
- */
- UPDATE tt_in_summary_monthly_product Z
- SET gl_amount = Z.gl_amount - A.gl_amount
- FROM tt_in_summary_monthly_amount_remain A
- WHERE Z.session_id = pSessionId AND
- Z.tenant_id = pTenantId AND
- A.session_id = pSessionId AND
- A.tenant_id = pTenantId AND
- Z.date_year_month = A.date_year_month AND
- Z.doc_type_id = 522 AND
- A.gl_amount <> 0 AND
- A.qty = 0 AND
- A.summary_monthly_product_id = vEmptyId AND
- Z.product_id = A.product_id;
- /**
- * Adrian, Feb 08, 2018
- * Insert tt_in_summary_monthly_product yang memiliki doc_type_id = 522
- * jika product id yang terdaftar di
- * (tt_in_summary_monthly_amount_remain yg gl_amount <> 0, qty = 0, summary_monthly_product_id = vEmptyId)
- * belum terdapat dalam tt_in_summary_monthly_product yang memiliki doc_type_id = 522
- */
- INSERT INTO tt_in_summary_monthly_product(
- session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
- SELECT A.session_id, A.date_year_month, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
- 522, A.product_id, A.base_uom_id, A.qty, A.gl_curr_code, (-1 * A.gl_amount), A.flg_amount
- FROM tt_in_summary_monthly_amount_remain A
- WHERE A.session_id = pSessionId AND
- A.gl_amount <> 0 AND
- A.qty = 0 AND
- A.summary_monthly_product_id = vEmptyId AND
- NOT EXISTS(
- SELECT 1
- FROM tt_in_summary_monthly_product B
- WHERE B.session_id = pSessionId AND
- B.tenant_id = pTenantId AND
- B.date_year_month = A.date_year_month AND
- B.doc_type_id = 522 AND
- A.product_id = B.product_id
- );
- END IF;
- INSERT INTO tt_in_summary_monthly_product
- (session_id, date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty, gl_curr_code, gl_amount, flg_amount)
- SELECT A.session_id, vNextYearMonth, 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.qty, A.gl_curr_code, A.gl_amount, A.flg_amount
- FROM tt_in_summary_monthly_amount_remain A
- WHERE A.session_id = pSessionId AND A.qty <> 0;
- INSERT INTO in_summary_monthly_cogs
- (date_year_month, tenant_id, ou_id, product_id,
- qty_total, curr_code, amount_total, avg_price,
- "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
- SELECT A.date_year_month, A.tenant_id, A.ou_id, A.product_id,
- A.qty_total, A.curr_code, A.amount_total, A.avg_price,
- 0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
- FROM tt_in_summary_monthly_cogs A
- WHERE A.session_id = pSessionId;
- INSERT INTO in_summary_monthly_amount
- (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
- SELECT A.date_year_month, 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.qty,
- A.gl_curr_code, A.gl_amount, A.flg_amount,
- 0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
- FROM tt_in_summary_monthly_product A
- WHERE A.session_id = pSessionId AND
- A.date_year_month = pYearMonth AND
- (A.doc_type_id NOT IN (vDocTypeAwal, 111) OR (A.doc_type_id = 111 AND A.qty < 0));
- INSERT INTO in_summary_monthly_amount
- (date_year_month, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, base_uom_id, qty,
- gl_curr_code, gl_amount, flg_amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id, remark)
- SELECT A.date_year_month, 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.qty,
- A.gl_curr_code, A.gl_amount, A.flg_amount,
- 0, pDatetime, pUserId, pDatetime, pUserId, vGroupProductFG
- FROM tt_in_summary_monthly_product A
- WHERE A.session_id = pSessionId AND
- A.date_year_month = vNextYearMonth;
- -- assembly belum dipakai, jadi dihapus dulu saja
- DELETE FROM tt_in_doc_product_price WHERE session_id = pSessionId;
- DELETE FROM tt_in_add_product_unamount WHERE session_id = pSessionId;
- DELETE FROM tt_in_product_base_price WHERE session_id = pSessionId;
- DELETE FROM tt_in_summary_monthly_product WHERE session_id = pSessionId;
- DELETE FROM tt_in_summary_monthly_cogs WHERE session_id = pSessionId;
- DELETE FROM tt_in_summary_monthly_amount_remain WHERE session_id = pSessionId;
- DELETE FROM tt_data_summary_monthly_amount_exclude WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement