Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Adrian, Aug 7, 2017
- */
- CREATE OR REPLACE FUNCTION f_check_log_product_balance_stock(character varying, bigint, bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pWarehouseId ALIAS FOR $3;
- pDateFrom ALIAS FOR $4;
- pDateTo ALIAS FOR $5;
- vDocTypeIdReceiveGoods bigint;
- vDocTypeIdDeliveryOrder bigint;
- vDocTypeIdPOSShop bigint;
- vDocTypeIdPOSReturnShop bigint;
- vDocTypeIdPOSShopInShop bigint;
- vDocTypeIdPOSReturnShopInShop bigint;
- vDocTypeIdAdjStockPOS bigint;
- vDocTypeIdVoidPOSShop bigint;
- vDocTypeIdVoidPOSShopInShop bigint;
- vDocTypeIdReturnNote bigint;
- vDocTypeIdClaimNote bigint;
- vDocTypeIdAdjStockQty bigint;
- vDocTypeIdAdjStockAmount bigint;
- vDocTypeIdDoReceipt bigint;
- vDocTypeIdGoodsTrfOutOutlet bigint;
- vDocTypeIdGoodsTrfOut bigint;
- vDocTypeIdGoodsTrfInOutlet bigint;
- vDocTypeIdGoodsTrfIn bigint;
- vDocTypeIdGoodsTrfInReceipt bigint;
- vGoodsTransferInReceiptLostDocTypeId bigint;
- vDocTypeIdGoodsBorrowing bigint;
- vDocTypeIdReturnGoodsBorrowing bigint;
- vDocTypeIdGoodsOutOther bigint;
- vStatusRelease character varying(1);
- vSpaceValue character varying(1);
- vFlagYes character varying(1);
- vFlagNo character varying(1);
- vFlagLost character varying(1);
- vFlagMissing character varying(1);
- vFlagRejected character varying(1);
- vFlagCorrection character varying(1);
- vStatusS character varying(1);
- vEmptyId bigint;
- vStatusVoid character varying(1);
- vOutletUserId bigint;
- vProductStatus character varying(50);
- vProductStatusGood character varying(4);
- BEGIN
- vDocTypeIdReceiveGoods := 111;
- vDocTypeIdDeliveryOrder := 311;
- vDocTypeIdPOSShop := 401;
- vDocTypeIdPOSReturnShop := 402;
- vDocTypeIdPOSShopInShop := 403;
- vDocTypeIdPOSReturnShopInShop := 404;
- vDocTypeIdAdjStockPOS := 413;
- vDocTypeIdVoidPOSShop := 405;
- vDocTypeIdVoidPOSShopInShop := 406;
- vDocTypeIdReturnNote := 502;
- vDocTypeIdClaimNote := 511;
- vDocTypeIdAdjStockQty := 521;
- vDocTypeIdAdjStockAmount := 522;
- vDocTypeIdDoReceipt := 526;
- vDocTypeIdGoodsTrfOutOutlet := 532;
- vDocTypeIdGoodsTrfOut := 533;
- vDocTypeIdGoodsTrfInOutlet := 534;
- vDocTypeIdGoodsTrfIn := 535;
- vDocTypeIdGoodsTrfInReceipt := 536;
- vGoodsTransferInReceiptLostDocTypeId := 537;
- vDocTypeIdGoodsBorrowing := 551;
- vDocTypeIdReturnGoodsBorrowing := 552;
- vDocTypeIdGoodsOutOther := 568;
- vStatusRelease := 'R';
- vSpaceValue := ' ';
- vFlagYes := 'Y';
- vFlagNo := 'N';
- vFlagLost := 'L';
- vFlagMissing := 'M';
- vFlagRejected := 'R';
- vFlagCorrection := 'C';
- vStatusS := 'S';
- vEmptyId := -99;
- vStatusVoid := 'V';
- vOutletUserId := 2;
- vProductStatusGood := 'GOOD';
- SELECT product_status_code INTO vProductStatus
- FROM m_product_status
- WHERE flg_buy = vFlagYes;
- /* Kosongkan semua tabel temporary */
- DELETE FROM tt_check_log_product_balance_stock_for_trx WHERE session_id = pSessionId;
- DELETE FROM tt_check_log_product_balance_stock_for_log WHERE session_id = pSessionId;
- DELETE FROM tt_check_log_product_balance_stock_for_result WHERE session_id = pSessionId;
- /* 1. Simpan semua data transaksi yang seharusnya masuk ke in_log_product_balance_stock */
- /* 1.a. RECEIVE GOODS */
- /* 1.a.1) Item receive goods yang tidak memiliki serial number */
- WITH temp_pu_product_balance AS (
- SELECT A.tenant_id, A.doc_type_id, A.receive_goods_id AS ref_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue AS serial_number, vSpaceValue AS lot_number, A.warehouse_id, vProductStatus AS product_status, D.base_uom_id, SUM(B.qty_rcv_int) AS qty_int_rcv,
- D.flg_stock
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item D, pu_po E
- WHERE A.receive_goods_id = B.receive_goods_id AND
- NOT EXISTS (SELECT 1 FROM pu_receive_goods_product C
- WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
- NOT EXISTS (SELECT 1 FROM pu_receive_goods_product_auto_sn C
- WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
- B.ref_id = D.po_item_id AND
- D.po_id = E.po_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdReceiveGoods AND
- A.tenant_id = pTenantId AND
- A.warehouse_id = pWarehouseId
- GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
- A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, C.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv)
- FROM temp_pu_product_balance A, m_warehouse_ou B, in_product_balance C
- WHERE A.flg_stock = 'Y' AND
- A.warehouse_id = B.warehouse_id AND
- A.tenant_id = C.tenant_id AND
- A.product_id = C.product_id AND
- A.serial_number = C.serial_number AND
- A.lot_number = C.lot_number
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, C.product_balance_id, A.warehouse_id, A.base_uom_id;
- /* 1.a.2) Item receive goods yang memiliki serial number dan tidak di-generate */
- WITH temp_pu_product_balance AS (
- SELECT A.tenant_id, A.doc_type_id, A.receive_goods_id AS ref_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, C.serial_number AS serial_number, C.lot_number AS lot_number, A.warehouse_id, vProductStatus AS product_status, D.base_uom_id, SUM(C.qty_rcv_int) AS qty_int_rcv,
- D.flg_stock
- FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product C, pu_po_item D, pu_po E
- WHERE A.receive_goods_id = B.receive_goods_id AND
- B.receive_goods_item_id = C.receive_goods_item_id AND
- B.ref_id = D.po_item_id AND
- D.po_id = E.po_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdReceiveGoods AND
- A.tenant_id = pTenantId AND
- A.warehouse_id = pWarehouseId
- GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
- C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
- A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_id, B.receive_goods_item_id, A.doc_no,
- E.po_id, E.doc_no, E.doc_date, D.po_item_id,
- D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, C.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv)
- FROM temp_pu_product_balance A, m_warehouse_ou B, in_product_balance C
- WHERE A.flg_stock = vFlagYes AND
- A.warehouse_id = B.warehouse_id AND
- A.tenant_id = C.tenant_id AND
- A.product_id = C.product_id AND
- A.serial_number = C.serial_number AND
- A.lot_number = C.lot_number
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, C.product_balance_id, A.warehouse_id, A.base_uom_id;
- /* 1.b. DELIVERY ORDER */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
- C.product_id, vSpaceValue, vSpaceValue, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id, SUM(C.qty_dlv_int) * -1
- FROM sl_do A, sl_do_item B, sl_do_product C, m_warehouse_ou D
- WHERE A.do_id = B.do_id AND
- B.do_item_id = C.do_item_id AND
- A.warehouse_id = D.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdDeliveryOrder AND
- A.tenant_id = pTenantId AND
- A.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
- C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id;
- /* 1.c. POS Shop */
- /* 1.c.1) Item POS Shop yang bukan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
- B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusS, vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id <> vEmptyId AND
- C.ou_id = A.ou_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShop AND
- C.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.c.2) Item POS Shop yang merupakan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusS, vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id = vEmptyId AND
- B.trx_pos_id = C.trx_pos_id AND
- B.process_no = C.process_no AND
- B.tenant_id = C.tenant_id AND
- B.line_no = C.ref_line_no AND
- D.ou_id = A.ou_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShop AND
- D.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.c.3) Item POS Shop yang akan dimasukkan sebagai adjustment stock */
- /*WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id AS ref_id, vEmptyId AS partner_id,
- B.doc_no, B.doc_date, C.product_id, B.warehouse_from_id AS warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty_realization) AS qty
- FROM i_outlet A, i_trx_inventory B, i_trx_inventory_item C
- WHERE A.tenant_id = pTenantId AND
- A.tenant_id = B.tenant_id AND
- A.ou_id = B.ou_id AND
- B.process_no = C.process_no AND
- B.trx_inventory_id = C.trx_inventory_id AND
- B.tenant_id = C.tenant_id AND
- B.doc_date BETWEEN pDateFrom AND pDateTo AND
- B.doc_type_id = vDocTypeIdAdjStockPOS AND
- C.product_balance_id <> vEmptyId
- GROUP BY A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id, B.warehouse_from_id,
- B.doc_no, B.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty)
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- WHERE A.doc_type_id = vDocTypeIdAdjStockPOS AND
- A.product_balance_id <> vEmptyId
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id;*/
- /* 1.d. POS Return Shop */
- /* 1.d.1) Item POS Return Shop yang bukan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
- B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusS, vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id <> vEmptyId AND
- C.ou_id = A.ou_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSReturnShop AND
- C.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.d.2) Item POS Return Shop yang merupakan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusS, vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id = vEmptyId AND
- B.trx_pos_id = C.trx_pos_id AND
- B.process_no = C.process_no AND
- B.tenant_id = C.tenant_id AND
- B.line_no = C.ref_line_no AND
- D.ou_id = A.ou_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSReturnShop AND
- D.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.e. POS Shop In Shop */
- /* 1.e.1)a) Item POS Shop In Shop yang bukan product assembly dan memiliki status S*/
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
- B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusS) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id <> vEmptyId AND
- C.ou_id = A.ou_id AND
- --A.update_user_id <> vOutletUserId AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShopInShop AND
- C.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.e.1)b) Item POS Shop In Shop yang bukan product assembly dan memiliki status V*/
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
- B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id <> vEmptyId AND
- C.ou_id = A.ou_id AND
- A.update_user_id <> vOutletUserId AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShopInShop AND
- C.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.e.2)a) Item POS Shop In Shop yang merupakan product assembly dan memiliki status S*/
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusS) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id = vEmptyId AND
- B.trx_pos_id = C.trx_pos_id AND
- B.process_no = C.process_no AND
- B.tenant_id = C.tenant_id AND
- B.line_no = C.ref_line_no AND
- D.ou_id = A.ou_id AND
- --A.update_user_id <> vOutletUserId AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShopInShop AND
- D.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.e.2)b) Item POS Shop In Shop yang merupakan product assembly dan memiliki status V*/
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id = vEmptyId AND
- B.trx_pos_id = C.trx_pos_id AND
- B.process_no = C.process_no AND
- B.tenant_id = C.tenant_id AND
- B.line_no = C.ref_line_no AND
- D.ou_id = A.ou_id AND
- A.update_user_id <> vOutletUserId AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShopInShop AND
- D.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.e.3) Item POS Shop In Shop yang akan dimasukkan sebagai adjustment stock */
- /*WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id AS ref_id, vEmptyId AS partner_id,
- B.doc_no, B.doc_date, C.product_id, B.warehouse_from_id AS warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty_realization) AS qty
- FROM i_outlet A, i_trx_inventory B, i_trx_inventory_item C
- WHERE A.tenant_id = pTenantId AND
- A.tenant_id = B.tenant_id AND
- A.ou_id = B.ou_id AND
- B.process_no = C.process_no AND
- B.trx_inventory_id = C.trx_inventory_id AND
- B.tenant_id = C.tenant_id AND
- B.doc_date BETWEEN pDateFrom AND pDateTo AND
- B.doc_type_id = vDocTypeIdAdjStockPOS AND
- C.product_balance_id <> vEmptyId
- GROUP BY A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id, B.warehouse_from_id,
- B.doc_no, B.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty)
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- WHERE A.doc_type_id = vDocTypeIdAdjStockPOS AND
- A.product_balance_id <> vEmptyId
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id;*/
- /* 1.f. POS Return Shop In Shop */
- /* 1.f.1) Item POS Return Shop In Shop yang bukan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
- B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusS, vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id <> vEmptyId AND
- C.ou_id = A.ou_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSReturnShopInShop AND
- C.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.f.2) Item POS Return Shop In Shop yang merupakan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
- FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusS, vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id = vEmptyId AND
- B.trx_pos_id = C.trx_pos_id AND
- B.process_no = C.process_no AND
- B.tenant_id = C.tenant_id AND
- B.line_no = C.ref_line_no AND
- D.ou_id = A.ou_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSReturnShopInShop AND
- D.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id;
- /* 1.g. Void POS Shop */
- /* 1.g.1) Item Void POS Shop yang bukan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
- B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) * -1 AS qty
- FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id <> vEmptyId AND
- C.ou_id = A.ou_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShop AND
- C.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, vDocTypeIdVoidPOSShop, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.g.2) Item Void POS Shop yang merupakan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) * -1 AS qty
- FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id = vEmptyId AND
- B.trx_pos_id = C.trx_pos_id AND
- B.process_no = C.process_no AND
- B.tenant_id = C.tenant_id AND
- B.line_no = C.ref_line_no AND
- D.ou_id = A.ou_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShop AND
- D.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, vDocTypeIdVoidPOSShop, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.h. Void POS Shop In Shop */
- /* 1.h.1) Item Void POS Shop In Shop yang bukan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
- B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) * -1 AS qty
- FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id <> vEmptyId AND
- C.ou_id = A.ou_id AND
- A.update_user_id <> vOutletUserId AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShopInShop AND
- C.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, vDocTypeIdVoidPOSShopInShop, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.h.2) Item Void POS Shop In Shop yang merupakan product assembly */
- WITH temp_pos_product_balance_stock AS (
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
- A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
- C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) * -1 AS qty
- FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
- WHERE A.tenant_id = pTenantId AND
- A.status IN (vStatusVoid) AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id = vEmptyId AND
- B.trx_pos_id = C.trx_pos_id AND
- B.process_no = C.process_no AND
- B.tenant_id = C.tenant_id AND
- B.line_no = C.ref_line_no AND
- D.ou_id = A.ou_id AND
- A.update_user_id <> vOutletUserId AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_type_id = vDocTypeIdPOSShopInShop AND
- D.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, B.ou_id, vDocTypeIdVoidPOSShopInShop, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
- A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
- FROM temp_pos_product_balance_stock A
- INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
- GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /* 1.i. RETURN NOTE */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- A.warehouse_to_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdReturnNote AND
- A.tenant_id = pTenantId AND
- A.warehouse_to_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
- /* 1.j. CLAIM NOTE*/
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- C.warehouse_id = A.warehouse_from_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdClaimNote AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 1.k. ADJUSTMENT STOCK QTY*/
- /* 1.k.1) Item Adjustment Stock Qty dengan Qty > 0 */
- WITH temp_in_product_balance AS (
- SELECT A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_id, B.product_status,
- B.serial_number, B.lot_number, B.qty_realization, B.base_uom_id,
- COALESCE(C.flg_buy_konsinyasi, vFlagNo) AS flg_buy_konsinyasi
- FROM in_inventory A
- INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- LEFT JOIN m_product_custom C ON B.product_id = C.product_id AND C.flg_buy_konsinyasi = vFlagYes
- WHERE A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdAdjStockQty AND
- A.tenant_id = pTenantId
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.serial_number, B.lot_number, D.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, temp_in_product_balance B, m_warehouse_ou C, in_product_balance D
- WHERE A.inventory_id = B.inventory_id AND
- B.qty_realization > 0 AND
- B.flg_buy_konsinyasi = vFlagNo AND
- A.warehouse_from_id = C.warehouse_id AND
- B.tenant_id = D.tenant_id AND
- B.product_id = D.product_id AND
- B.serial_number = D.serial_number AND
- B.lot_number = D.lot_number AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.serial_number, B.lot_number, D.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 1.k.2) Item Adjustment Stock Qty dengan Qty < 0 */
- WITH temp_in_product_balance AS (
- SELECT A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_id, B.product_status,
- B.serial_number, B.lot_number, B.qty_realization, B.base_uom_id,
- COALESCE(C.flg_buy_konsinyasi, vFlagNo) AS flg_buy_konsinyasi
- FROM in_inventory A
- INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- LEFT JOIN m_product_custom C ON B.product_id = C.product_id AND C.flg_buy_konsinyasi = vFlagYes
- WHERE A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdAdjStockQty AND
- A.tenant_id = pTenantId
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.serial_number, B.lot_number, D.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, temp_in_product_balance B, m_warehouse_ou C, in_product_balance D
- WHERE A.inventory_id = B.inventory_id AND
- B.qty_realization < 0 AND
- B.flg_buy_konsinyasi = vFlagNo AND
- A.warehouse_from_id = C.warehouse_id AND
- B.tenant_id = D.tenant_id AND
- B.product_id = D.product_id AND
- B.serial_number = D.serial_number AND
- B.lot_number = D.lot_number AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.serial_number, B.lot_number, D.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 1.l. ADJUSTMENT STOCK AMOUNT */
- WITH temp_in_product_price_balance AS (
- SELECT A.tenant_id, A.ou_from_id AS ou_id, A.doc_type_id, A.doc_no, A.doc_date, A.partner_id, A.warehouse_from_id AS warehouse_id,
- A.inventory_id, B.inventory_item_id, B.product_id, B.product_status,
- B.serial_number, B.lot_number, B.qty_realization, B.base_uom_id
- FROM in_inventory A, in_inventory_item B
- WHERE A.inventory_id = B.inventory_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdAdjStockAmount AND
- A.tenant_id = pTenantId
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, B.tenant_id, C.ou_id, B.doc_type_id, B.inventory_id, B.doc_no, B.doc_date, B.partner_id,
- B.product_id, B.serial_number, B.lot_number, D.product_balance_id, B.warehouse_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM temp_in_product_price_balance B, m_warehouse_ou C, in_product_balance D
- WHERE B.warehouse_id = C.warehouse_id AND
- B.tenant_id = D.tenant_id AND
- B.product_id = D.product_id AND
- B.serial_number = D.serial_number AND
- B.lot_number = D.lot_number AND
- B.warehouse_id = pWarehouseId
- GROUP BY B.tenant_id, C.ou_id, B.doc_type_id, B.inventory_id, B.doc_no, B.doc_date, B.partner_id,
- B.product_id, B.serial_number, B.lot_number, D.product_balance_id, B.warehouse_id, B.product_status, B.base_uom_id;
- /* 1.m. DEIVERY ORDER RECEIPT */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
- C.product_id, vSpaceValue, vSpaceValue, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id, SUM(C.qty_return)
- FROM in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C, sl_do D, m_warehouse_ou E
- WHERE A.do_receipt_id = B.do_receipt_id AND
- B.do_receipt_item_id = C.do_receipt_item_id AND
- A.ref_id = D.do_id AND
- A.warehouse_id = E.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdDoReceipt AND
- A.tenant_id = pTenantId AND
- A.warehouse_id = pWarehouseId
- GROUP BY A.tenant_id, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
- C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id;
- /* 1.n. GOODS TRANSFER OUT FROM OUTLET*/
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- A.warehouse_from_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsTrfOutOutlet AND
- A.tenant_id = pTenantId AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 1.o. GOODS TRANSFER OUT */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- A.warehouse_from_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsTrfOut AND
- A.tenant_id = pTenantId AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 1.p. GOODS TRANSFER IN FROM OUTLET */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- A.warehouse_to_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsTrfInOutlet AND
- A.tenant_id = pTenantId AND
- A.warehouse_to_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
- /* 1.q. GOODS TRANSFER IN */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- A.warehouse_to_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsTrfIn AND
- A.tenant_id = pTenantId AND
- A.warehouse_to_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
- /* 1.r. GOODS TRANSFER IN RECEIPT */
- /* 1.r.1) Item GTI Receipt yang missing dan rejected */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- B.reason_receipt_code IN (vFlagMissing,vFlagRejected) AND
- A.warehouse_from_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsTrfInReceipt AND
- A.tenant_id = pTenantId AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 1.r.2) Item GTI Receipt yang correction */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- B.reason_receipt_code = vFlagCorrection AND
- A.warehouse_to_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsTrfInReceipt AND
- A.tenant_id = pTenantId AND
- A.warehouse_to_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
- /* 1.r.3) Item GTI Receipt yang lost */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, vGoodsTransferInReceiptLostDocTypeId, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(-1 * B.qty_realization)
- FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- B.reason_receipt_code = vFlagLost AND
- A.warehouse_from_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsTrfInReceipt AND
- A.tenant_id = pTenantId AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 1.s. DELIVERY GOODS BORROWING */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- A.warehouse_from_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsBorrowing AND
- A.tenant_id = pTenantId AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 1.t. RETURN GOODS BORROWING */
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- A.warehouse_to_id = C.warehouse_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdReturnGoodsBorrowing AND
- A.tenant_id = pTenantId AND
- A.warehouse_from_id = pWarehouseId
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
- /* 1.u. DELIVERY GOODS OUT OTHER */
- WITH temp_in_product_balance AS (
- SELECT A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_balance_id, B.product_id, B.product_status,
- B.serial_number, B.product_expired_date, B.product_year_made, B.lot_number, B.qty_realization, B.base_uom_id
- FROM in_inventory A, in_inventory_item B
- WHERE A.inventory_id = B.inventory_id AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.status_doc = vStatusRelease AND
- A.doc_type_id = vDocTypeIdGoodsOutOther AND
- A.tenant_id = pTenantId AND
- A.warehouse_from_id = pWarehouseId
- )
- INSERT INTO tt_check_log_product_balance_stock_for_trx
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, -1 * SUM(B.qty_realization)
- FROM in_inventory A, temp_in_product_balance B, m_warehouse_ou C
- WHERE A.inventory_id = B.inventory_id AND
- A.warehouse_from_id = C.warehouse_id
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- /* 2. Merge log untuk POS */
- WITH grouped_tt_check_log_product_balance_stock_for_trx AS(
- SELECT session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id,
- warehouse_id, product_status, base_uom_id, SUM(qty) AS summed_qty
- FROM tt_check_log_product_balance_stock_for_trx
- WHERE doc_type_id IN (vDocTypeIdPOSShop) AND
- session_id = pSessionId AND
- tenant_id = pTenantId AND
- warehouse_id = pWarehouseId
- GROUP BY session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id
- )
- UPDATE tt_check_log_product_balance_stock_for_trx Z SET qty = A.summed_qty
- FROM grouped_tt_check_log_product_balance_stock_for_trx A
- WHERE A.session_id = Z.session_id AND
- A.tenant_id = Z.tenant_id AND
- A.ou_id = Z.ou_id AND
- A.doc_type_id = Z.doc_type_id AND
- A.ref_id = Z.ref_id AND
- A.doc_no = Z.doc_no AND
- A.doc_date = Z.doc_date AND
- A.partner_id = Z.partner_id AND
- A.product_id = Z.product_id AND
- A.serial_number = Z.serial_number AND
- A.lot_number = Z.lot_number AND
- A.product_balance_id = Z.product_balance_id AND
- A.warehouse_id = Z.warehouse_id AND
- A.product_status = Z.product_status AND
- A.base_uom_id = Z.base_uom_id AND
- Z.session_id = pSessionId AND
- Z.tenant_id = pTenantId AND
- Z.warehouse_id = pWarehouseId AND
- Z.doc_type_id IN (vDocTypeIdPOSShop, vDocTypeIdPOSReturnShop, vDocTypeIdPOSShopInShop, vDocTypeIdPOSReturnShopInShop);
- /* 3. Simpan log_product_balance_stock yang termasuk dalam periode */
- INSERT INTO tt_check_log_product_balance_stock_for_log
- (session_id, log_product_balance_stock_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, warehouse_id, product_balance_id, product_status, base_uom_id, qty,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT pSessionId, log_product_balance_stock_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, warehouse_id, product_balance_id, product_status, base_uom_id, qty,
- version, create_datetime, create_user_id, update_datetime, update_user_id
- FROM in_log_product_balance_stock
- WHERE doc_date BETWEEN pDateFrom AND pDateTo AND
- tenant_id = pTenantId AND
- warehouse_id = pWarehouseId;
- /* 4. Update id untuk log_product_balance_stock yang ditemukan */
- UPDATE tt_check_log_product_balance_stock_for_trx Z
- SET log_product_balance_stock_id = A.log_product_balance_stock_id
- FROM tt_check_log_product_balance_stock_for_log A
- WHERE A.tenant_id = Z.tenant_id AND
- A.ou_id = Z.ou_id AND
- A.doc_type_id = Z.doc_type_id AND
- A.ref_id = Z.ref_id AND
- A.doc_no = Z.doc_no AND
- A.doc_date = Z.doc_date 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
- A.qty = Z.qty AND
- Z.session_id = pSessionId AND
- Z.tenant_id = pTenantId AND
- Z.warehouse_id = pWarehouseId;
- /* 5. Simpan log yang kurang sesuai ke dalam tabel result */
- INSERT INTO tt_check_log_product_balance_stock_for_result
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, expected_warehouse_id, expected_product_balance_id, product_status, base_uom_id, expected_qty)
- SELECT session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, warehouse_id, product_balance_id, product_status, base_uom_id, qty
- FROM tt_check_log_product_balance_stock_for_trx
- WHERE log_product_balance_stock_id = vEmptyId AND
- session_id = pSessionId AND
- tenant_id = pTenantId AND
- warehouse_id = pWarehouseId;
- /* 6. Cari log_product_balance_stock yang kurang sesuai */
- UPDATE tt_check_log_product_balance_stock_for_result Z
- SET actual_qty = A.qty, actual_product_balance_id = A.product_balance_id, actual_warehouse_id = A.warehouse_id
- FROM in_log_product_balance_stock A
- WHERE A.tenant_id = Z.tenant_id AND
- A.ou_id = Z.ou_id AND
- A.doc_type_id = Z.doc_type_id AND
- A.ref_id = Z.ref_id AND
- A.doc_no = Z.doc_no AND
- A.doc_date = Z.doc_date AND
- A.product_id = Z.product_id AND
- A.product_status = Z.product_status AND
- Z.session_id = pSessionId AND
- Z.tenant_id = pTenantId AND
- Z.expected_warehouse_id = pWarehouseId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement