Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Adrian, Dec 28, 2017
- * Function untuk melakukan recalculate p_product_balance stock
- */
- CREATE OR REPLACE FUNCTION f_recalculate_product_balance_stock(character varying, bigint, bigint, bigint, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pOuId ALIAS FOR $3;
- pUserId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- vSpaceValue character varying(1);
- vFlagYes character varying(1);
- vFlagNo character varying(1);
- vFlagError character varying(1);
- vStatusDraft character varying(1);
- vEmptyId bigint;
- vDefaultSummaryPeriod character varying(6);
- vLastSummaryPeriod character varying(6);
- vStartTrx character varying(8);
- BEGIN
- vSpaceValue := ' ';
- vFlagYes := 'Y';
- vFlagNo := 'N';
- vFlagError := 'E';
- vStatusDraft := 'D';
- vEmptyId := -99;
- vDefaultSummaryPeriod := '200001';
- /* Year month tutup bulan terakhir */
- SELECT TO_CHAR(TO_DATE(MAX(date_year_month),'YYYYMM') + interval '1 Month','YYYYMM') INTO vLastSummaryPeriod
- FROM p_admin_monthly_process
- WHERE tenant_id = pTenantId AND
- ou_id = pOuId AND
- flg_process = vFlagYes;
- SELECT vLastSummaryPeriod || '01' INTO vStartTrx;
- /* Kosongkan semua tabel temporary */
- DELETE FROM tt_recalculate_product_balance_stock_for_log WHERE session_id = pSessionId;
- DELETE FROM tt_recalculate_product_balance_stock_for_temp WHERE session_id = pSessionId;
- /* Cek Apakah ada Dokumen yang masih dalam progress */
- IF EXISTS(
- SELECT 1 FROM p_trx_inventory A WHERE A.status IN (vStatusDraft)
- ) THEN
- RAISE EXCEPTION 'POS-cannot.do.recalculate.for.there.is.in.progress.document';
- END IF;
- /* 1. Simpan stock awal product, doc_type_id dibuat -99 */
- INSERT INTO tt_recalculate_product_balance_stock_for_log
- (session_id, tenant_id, warehouse_id,
- ou_id, doc_type_id, ref_id, doc_no, doc_date,
- product_id, product_balance_id, product_status, base_uom_id, summary_qty)
- SELECT pSessionId, tenant_id, warehouse_id,
- sub_ou_id, vEmptyId, summary_monthly_qty_id, vSpaceValue, date_year_month,
- product_id, product_balance_id, product_status, base_uom_id, qty
- FROM p_summary_monthly_qty
- WHERE tenant_id = pTenantId AND
- sub_ou_id = pOuId AND
- date_year_month = vLastSummaryPeriod;
- /* 2. Simpan perubahan qty product dari log */
- /* 2.a. Simpan perubahan qty product dari log untuk qty > 0 */
- INSERT INTO tt_recalculate_product_balance_stock_for_log
- (session_id, tenant_id, warehouse_id,
- ou_id, doc_type_id, ref_id, doc_no, doc_date,
- product_id, product_balance_id, product_status, base_uom_id, add_qty)
- SELECT pSessionId, tenant_id, warehouse_id,
- ou_id, doc_type_id, ref_id, doc_no, doc_date,
- product_id, product_balance_id, product_status, base_uom_id, qty
- FROM p_log_product_balance_stock
- WHERE tenant_id = pTenantId AND
- ou_id = pOuId AND
- doc_date >= vStartTrx AND
- qty > 0;
- /* 2.b. Simpan perubahan qty product dari log untuk qty < 0 */
- INSERT INTO tt_recalculate_product_balance_stock_for_log
- (session_id, tenant_id, warehouse_id,
- ou_id, doc_type_id, ref_id, doc_no, doc_date,
- product_id, product_balance_id, product_status, base_uom_id, sub_qty)
- SELECT pSessionId, tenant_id, warehouse_id,
- ou_id, doc_type_id, ref_id, doc_no, doc_date,
- product_id, product_balance_id, product_status, base_uom_id, qty
- FROM p_log_product_balance_stock
- WHERE tenant_id = pTenantId AND
- ou_id = pOuId AND
- doc_date >= vStartTrx AND
- qty < 0;
- /* 3. Simpan ke dalam table temp, group by unique index p_product_balance stock */
- INSERT INTO tt_recalculate_product_balance_stock_for_temp
- (session_id, product_balance_stock_id, tenant_id, warehouse_id,
- product_id, product_balance_id, product_status, base_uom_id,
- summary_qty, add_qty, sub_qty)
- SELECT session_id, vEmptyId, tenant_id, warehouse_id,
- product_id, product_balance_id, product_status, base_uom_id,
- SUM(summary_qty), SUM(add_qty), SUM(sub_qty)
- FROM tt_recalculate_product_balance_stock_for_log
- WHERE session_id = pSessionId AND
- tenant_id = pTenantId AND
- ou_id = pOuId
- GROUP BY session_id, tenant_id, warehouse_id,
- product_id, product_balance_id, product_status, base_uom_id;
- /* 4.a. Update total qty yang diharapkan pada table temp */
- UPDATE tt_recalculate_product_balance_stock_for_temp Z
- SET expected_qty = Z.summary_qty + Z.add_qty + Z.sub_qty
- FROM p_product_balance_stock A
- WHERE Z.session_id = pSessionId AND
- Z.tenant_id = pTenantId;
- /* 4.b. Update product balance stock id dan qty yang tersimpan */
- UPDATE tt_recalculate_product_balance_stock_for_temp Z
- SET product_balance_stock_id = A.product_balance_stock_id,
- actual_qty = A.qty
- FROM p_product_balance_stock A
- WHERE A.tenant_id = Z.tenant_id AND
- A.warehouse_id = Z.warehouse_id AND
- A.product_id = Z.product_id AND
- A.product_balance_id = Z.product_balance_id AND
- A.product_status = Z.product_status AND
- Z.session_id = pSessionId AND
- Z.tenant_id = pTenantId;
- /* 5. Simpan ke dalam table update untuk data yang tidak ditemukan/tidak sesuai */
- INSERT INTO p_recalculate_product_balance_stock
- (product_balance_stock_id, tenant_id, warehouse_id,
- product_id, product_balance_id, product_status, base_uom_id,
- summary_qty, add_qty, sub_qty, expected_qty, actual_qty, flg_update,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT product_balance_stock_id, tenant_id, warehouse_id,
- product_id, product_balance_id, product_status, base_uom_id,
- summary_qty, add_qty, sub_qty, expected_qty, actual_qty, vFlagNo,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_recalculate_product_balance_stock_for_temp
- WHERE session_id = pSessionId AND
- tenant_id = pTenantId AND
- (product_balance_stock_id = vEmptyId OR
- expected_qty <> actual_qty);
- /* 6. Update flg_update menjadi E untuk expected qty yang minus */
- UPDATE p_recalculate_product_balance_stock
- SET flg_update = vFlagError
- WHERE tenant_id = pTenantId
- AND flg_update = vFlagNo
- AND expected_qty < 0;
- /* 7. Update product_balance stock yang tidak sesuai */
- UPDATE p_product_balance_stock Z
- SET qty = A.expected_qty,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- version = Z.version + 1
- FROM p_recalculate_product_balance_stock A
- WHERE A.tenant_id = pTenantId AND
- A.flg_update = vFlagNo AND
- A.product_balance_stock_id = Z.product_balance_stock_id AND
- A.expected_qty <> A.actual_qty;
- /* 8. Insert untuk product balance stock yang tidak ditemukan */
- INSERT INTO p_product_balance_stock(
- tenant_id, warehouse_id, product_id,
- product_balance_id, product_status, base_uom_id, qty, version,
- create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.warehouse_id, A.product_id,
- A.product_balance_id, A.product_status, A.base_uom_id, A.expected_qty, 0,
- pDatetime, pUserId, pDatetime, pUserId
- FROM p_recalculate_product_balance_stock A
- WHERE A.tenant_id = pTenantId AND
- A.flg_update = vFlagNo AND
- A.product_balance_stock_id = vEmptyId;
- /* 8. Update flg_update menjadi Y */
- UPDATE p_recalculate_product_balance_stock
- SET flg_update = vFlagYes
- WHERE tenant_id = pTenantId AND
- flg_update = vFlagNo;
- /* Kosongkan semua tabel temporary */
- DELETE FROM tt_recalculate_product_balance_stock_for_log WHERE session_id = pSessionId;
- DELETE FROM tt_recalculate_product_balance_stock_for_temp WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement