GRANT ALL ON XXTNPL.tnpl_om_stock_in_hand_reel TO APPS.XXTNPL.tnpl_om_stock_in_hand_reel
CREATE SYNONYM XXTNPL.tnpl_om_stock_in_hand_reel FOR APPS.XXTNPL.tnpl_om_stock_in_hand_reel
GRANT ALL ON XXTNPL.TNPL_OM_STOCK_IN_HAND TO APPS
CREATE SYNONYM APPS.TNPL_OM_STOCK_IN_HAND FOR XXTNPL.TNPL_OM_STOCK_IN_HAND;
CREATE OR REPLACE PROCEDURE XXTNPL.tnpl_om_stock_in_hand_reel (
p_from_date IN DATE DEFAULT NULL,
p_to_date IN DATE DEFAULT NULL,
p_variety IN VARCHAR2 DEFAULT NULL,
p_gsm IN VARCHAR2 DEFAULT NULL,
p_size IN VARCHAR2 DEFAULT NULL,
p_seg IN VARCHAR2 DEFAULT NULL,
p_org_id IN NUMBER DEFAULT NULL
)
IS
--DECLARE
-- p_from DATE := '1-May-2013';
-- p_to_date DATE := '02-May-2013';
-- p_variety VARCHAR2 (25) := NULL;
-- p_gsm VARCHAR2 (25) := NULL;
-- p_size VARCHAR2 (25) := NULL;
-- p_seg VARCHAR2 (35) := NULL;
-- p_org_id NUMBER := 110;
-- l_opn_balace NUMBER := NULL;
l_opn_balace NUMBER;
l_close_qty_cal NUMBER;
l_open_qty_bal NUMBER;
l_open_qty_balance NUMBER;
l_receipt_qty NUMBER;
l_openinig_lot NUMBER;
l_open_lot NUMBER;
l_closing_lot NUMBER;
CURSOR c1
IS
SELECT *
FROM TNPL_OM_STOCK_IN_HAND
ORDER BY creation_date;
BEGIN
BEGIN
execute immediate 'truncate table TNPL_OM_STOCK_IN_HAND';
INSERT INTO TNPL_OM_STOCK_IN_HAND
(creation_date, receipt_qty, receipt_lot, variety, gsm,
width_size, dispatch_qty, dispatch_lot, sales_return,
sales_lot, stock_return, stock_lot, item_code)
(SELECT creation_date, SUM (receipt_qty) receipt_qty,
SUM (receipt_lot) receipt_lot, variety, gsm, width_size,
SUM (dispatch_qty) dispatch_qty,
SUM (dispatch_lot) dispatch_lot,
SUM (sales_return) sales_return, SUM (sales_lot)
sales_lot,
SUM (stock_return) stock_return,
SUM (stock_retrun_lot) stock_lot, item_code
FROM (SELECT creation_date, /* transaction_type_name,*/ SUM
(receipt_qty
)
receipt_qty,
(receipt_lot_plus - receipt_lot_minus
) receipt_lot,
variety, gsm, width_size,
SUM (dispatch_qty) dispatch_qty,
(dispatch_lot_plus - dispatch_lot_minus
) dispatch_lot,
sales_return,
(sales_lot_plus - sales_lot_minus) sales_lot,
stock_return, stock_retrun_lot, item_code
FROM (SELECT creation_date, transaction_type_name,
SUM (receipt_qty) receipt_qty,
COUNT
(receipt_lot_plus)
receipt_lot_plus,
COUNT
(receipt_lot_minus
) receipt_lot_minus,
variety, gsm, width_size,
SUM (dispatch_qty) dispatch_qty,
COUNT
(dispatch_lot_plus
) dispatch_lot_plus,
COUNT
(dispatch_lot_minus
) dispatch_lot_minus,
SUM (sales_qty) sales_return,
COUNT (sales_lot_plus) sales_lot_plus,
COUNT (sales_lot_minus)
sales_lot_minus,
COUNT
(stock_retrun_lot)
stock_retrun_lot,
SUM (stock_return) stock_return,
item_code
FROM (SELECT TRUNC
(mmt.transaction_date
) creation_date,
mtt.transaction_type_name,
msib.segment1 item_code,
(CASE
WHEN mtt.transaction_type_name NOT IN
('Sales order issue',
'Int Order Intr Ship',
'RMA Receipt')
THEN mlot.transaction_quantity
ELSE 0
END
) receipt_qty,
(CASE
WHEN mlot.transaction_quantity >
0
AND mtt.transaction_type_name NOT IN
('Sales order issue',
'Int Order Intr Ship',
'RMA Receipt')
THEN mlot.lot_number
ELSE NULL
END
) receipt_lot_plus,
(CASE
WHEN mlot.transaction_quantity <
0
AND mtt.transaction_type_name NOT IN
('Sales order issue',
'Int Order Intr Ship',
'RMA Receipt')
THEN mlot.lot_number
ELSE NULL
END
) receipt_lot_minus,
SUBSTR (segment1, 4,
3) variety,
SUBSTR (segment1, 8, 4) gsm,
SUBSTR (segment1,
12,
4
) width_size,
(CASE
WHEN mtt.transaction_type_name IN
('Sales order issue',
'Int Order Intr Ship')
THEN mlot.transaction_quantity
ELSE 0
END
) dispatch_qty,
(CASE
WHEN mlot.transaction_quantity >
0
AND mtt.transaction_type_name IN
('Sales order issue',
'Int Order Intr Ship')
THEN mlot.lot_number
ELSE NULL
END
) dispatch_lot_plus,
(CASE
WHEN mlot.transaction_quantity <
0
AND mtt.transaction_type_name IN
('Sales order issue',
'Int Order Intr Ship')
THEN mlot.lot_number
ELSE NULL
END
) dispatch_lot_minus,
(CASE
WHEN mtt.transaction_type_name IN
('RMA Receipt')
THEN mlot.transaction_quantity
ELSE 0
END
) sales_qty,
(CASE
WHEN mlot.transaction_quantity >
0
AND mtt.transaction_type_name IN
('RMA Receipt')
THEN mlot.lot_number
ELSE NULL
END
) sales_lot_plus,
(CASE
WHEN mlot.transaction_quantity <
0
AND mtt.transaction_type_name IN
('RMA Receipt')
THEN mlot.lot_number
ELSE NULL
END
) sales_lot_minus,
(CASE
WHEN mlot.transaction_quantity <
0
AND mtt.transaction_type_name IN
('Subinventory Transfer')
AND mmt.subinventory_code IN
('ASRS-1', 'ASRS-2',
'GODOWN-1')
AND mmt.transfer_subinventory IN
('BUNDLE_ST',
'BOX_ST',
'PACK_ST')
THEN mlot.lot_number
ELSE NULL
END
) stock_retrun_lot,
(CASE
WHEN mtt.transaction_type_name IN
('Subinventory Transfer')
AND mmt.subinventory_code IN
('ASRS-1', 'ASRS-2',
'GODOWN-1')
AND mmt.transfer_subinventory IN
('BUNDLE_ST',
'BOX_ST',
'PACK_ST')
AND mlot.transaction_quantity <
0
THEN mlot.transaction_quantity
ELSE 0
END
) stock_return
FROM apps.mtl_system_items_b msib,
apps.mtl_material_transactions mmt,
apps.mtl_transaction_types mtt,
apps.rcv_transactions rt,
apps.mtl_transaction_lot_numbers mlot
WHERE msib.segment1 =
NVL (p_seg, msib.segment1)
AND SUBSTR (msib.segment1, 1, 1) =
'R'
AND msib.inventory_item_id =
mmt.inventory_item_id
AND msib.organization_id =
mmt.organization_id
AND mmt.transaction_source_type_id =
mlot.transaction_source_type_id
AND mmt.inventory_item_id =
mlot.inventory_item_id
AND mmt.transaction_id =
mlot.transaction_id
AND msib.inventory_item_id =
mmt.inventory_item_id
-- And Msib.Segment1 = 'U1Nrdpr006005850910004'
AND rt.transaction_id(+) =
mmt.transaction_id
AND msib.organization_id =
NVL (p_org_id,
msib.organization_id
)
AND SUBSTR (segment1, 4, 3) =
NVL (p_variety,
SUBSTR (segment1, 4, 3)
)
AND SUBSTR (segment1, 8, 4) =
NVL (p_gsm,
SUBSTR (segment1, 8, 4)
)
AND SUBSTR (segment1, 12, 4) =
NVL (p_size,
SUBSTR (segment1, 12,
4)
)
AND mtt.transaction_type_id =
mmt.transaction_type_id
AND mmt.transaction_source_type_id =
mtt.transaction_source_type_id
AND mmt.transaction_date
BETWEEN (p_from_date)
AND (p_to_date))
GROUP BY creation_date,
transaction_type_name,
variety,
gsm,
width_size,
item_code)
GROUP BY creation_date,
receipt_lot_plus,
receipt_lot_minus,
variety,
gsm,
width_size,
dispatch_lot_plus,
dispatch_lot_minus,
sales_return,
sales_lot_plus,
sales_lot_minus,
stock_return,
stock_retrun_lot,
item_code)
GROUP BY creation_date, variety, gsm, width_size, item_code);
END;
BEGIN
SELECT SUM (stock) stock
INTO l_opn_balace
FROM (SELECT NVL (SUM (mmt.transaction_quantity), 0) AS stock,
SUBSTR (segment1, 4, 3) variety,
SUBSTR (segment1, 8, 4) p_gsm, msib.segment1,
msib.organization_id, SUBSTR (segment1, 12, 4)
FROM apps.mtl_system_items_b msib,
apps.mtl_material_transactions mmt,
apps.rcv_transactions rt
WHERE msib.inventory_item_id = mmt.inventory_item_id
AND msib.organization_id = mmt.organization_id
AND msib.segment1 = NVL (p_seg, msib.segment1)
AND SUBSTR (segment1, 4, 3) =
NVL (p_variety, SUBSTR (segment1, 4, 3))
AND SUBSTR (segment1, 8, 4) =
NVL (p_gsm, SUBSTR (segment1, 8, 4))
AND SUBSTR (segment1, 12, 4) =
NVL (p_size, SUBSTR (segment1, 12, 4))
AND rt.transaction_id(+) = mmt.transaction_id
AND msib.organization_id =
NVL (p_org_id, msib.organization_id)
AND mmt.transaction_date < p_from_date
AND transaction_action_id NOT IN (24, 30)
AND (logical_transaction = 2 OR logical_transaction IS NULL
)
GROUP BY SUBSTR (segment1, 4, 3),
SUBSTR (segment1, 8, 4),
msib.organization_id,
SUBSTR (segment1, 12, 4),
msib.segment1,
mmt.transaction_date
ORDER BY mmt.transaction_date);
END;
DBMS_OUTPUT.put_line (l_opn_balace);
BEGIN
SELECT COUNT (lot_count)
INTO l_open_lot ----- opening lot number
FROM (SELECT CASE
WHEN trans_qty <> 0
THEN (lot_number)
END AS lot_count, item_id, loc
FROM (SELECT SUM (mlot.transaction_quantity) AS trans_qty,
mlot.lot_number AS lot_number,
mmt1.inventory_item_id AS item_id,
mmt1.locator_id AS loc
FROM apps.mtl_transaction_lot_numbers mlot,
apps.mtl_material_transactions mmt1,
apps.mtl_system_items_b msib
WHERE 1 = 1
-- and mmt1.inventory_item_id = mlot.inventory_item_id
AND msib.inventory_item_id =
mmt1.inventory_item_id
AND msib.organization_id = mmt1.organization_id
AND mmt1.transaction_id = mlot.transaction_id
AND msib.segment1 = p_seg
AND mmt1.subinventory_code IN
('ASRS-1', 'ASRS-2', 'GODOWN-1')
AND mlot.transaction_date < p_from_date
AND mlot.organization_id = p_org_id
GROUP BY mlot.lot_number,
mmt1.inventory_item_id,
mmt1.locator_id,
mlot.lot_number)
WHERE lot_number IS NOT NULL
GROUP BY trans_qty, item_id, loc, lot_number);
END;
FOR i IN c1
LOOP
IF p_seg IS NULL
THEN
SELECT SUM (stock) stock
INTO l_open_qty_bal
FROM (SELECT NVL (SUM (mmt.transaction_quantity), 0) AS stock,
SUBSTR (segment1, 4, 3) variety,
SUBSTR (segment1, 8, 4) p_gsm, msib.segment1,
msib.organization_id, SUBSTR (segment1, 12, 4)
FROM apps.mtl_system_items_b msib,
apps.mtl_material_transactions mmt,
apps.rcv_transactions rt
WHERE msib.inventory_item_id = mmt.inventory_item_id
AND msib.organization_id = mmt.organization_id
AND msib.segment1 = i.item_code
AND SUBSTR (segment1, 4, 3) =
NVL (p_variety, SUBSTR (segment1, 4, 3))
AND SUBSTR (segment1, 8, 4) =
NVL (p_gsm, SUBSTR (segment1, 8, 4))
AND SUBSTR (segment1, 12, 4) =
NVL (p_size, SUBSTR (segment1, 12, 4))
AND rt.transaction_id(+) = mmt.transaction_id
AND msib.organization_id =
NVL (p_org_id, msib.organization_id)
AND mmt.transaction_date < p_from_date
AND transaction_action_id NOT IN (24, 30)
AND ( logical_transaction = 2
OR logical_transaction IS NULL
)
GROUP BY SUBSTR (segment1, 4, 3),
SUBSTR (segment1, 8, 4),
msib.organization_id,
SUBSTR (segment1, 12, 4),
msib.segment1,
mmt.transaction_date
ORDER BY mmt.transaction_date);
ELSE
l_open_qty_bal := NVL (l_opn_balace, 0);
END IF;
IF p_seg IS NULL
THEN
SELECT COUNT (lot_count)
INTO l_openinig_lot ----- opening lot number
FROM (SELECT CASE
WHEN trans_qty <> 0
THEN (lot_number)
END AS lot_count,
item_id, loc
FROM (SELECT SUM
(mlot.transaction_quantity
) AS trans_qty,
mlot.lot_number AS lot_number,
mmt1.inventory_item_id AS item_id,
mmt1.locator_id AS loc
FROM apps.mtl_transaction_lot_numbers mlot,
apps.mtl_material_transactions mmt1,
apps.mtl_system_items_b msib
WHERE 1 = 1
-- and mmt1.inventory_item_id = mlot.inventory_item_id
AND msib.inventory_item_id =
mmt1.inventory_item_id
AND msib.organization_id =
mmt1.organization_id
AND mmt1.transaction_id = mlot.transaction_id
AND msib.segment1 = i.item_code
AND mmt1.subinventory_code IN
('ASRS-1', 'ASRS-2', 'GODOWN-1')
AND mlot.transaction_date < p_from_date
AND mlot.organization_id =
NVL (p_org_id, mlot.organization_id)
GROUP BY mlot.lot_number,
mmt1.inventory_item_id,
mmt1.locator_id,
mlot.lot_number)
WHERE lot_number IS NOT NULL
GROUP BY trans_qty, item_id, loc, lot_number);
ELSE
DBMS_OUTPUT.put_line ('l_open_qty_bal' || l_open_qty_bal);
l_openinig_lot := NVL (l_open_lot, 0);
END IF;
l_closing_lot :=
NVL (l_openinig_lot, 0)
+ NVL (i.receipt_lot, 0)
+ NVL (i.dispatch_lot, 0);
l_close_qty_cal :=
NVL (l_open_qty_bal, 0)
+ NVL (i.receipt_qty, 0)
+ NVL (i.dispatch_qty, 0);
DBMS_OUTPUT.put_line ('l_open_qty_bal' || l_open_qty_bal);
UPDATE TNPL_OM_STOCK_IN_HAND
SET bal_stock = l_open_qty_bal,
closing_qty = l_close_qty_cal,
bal_sto_lot = l_openinig_lot,
closing_lot = l_closing_lot
WHERE creation_date = i.creation_date AND item_code = i.item_code;
l_opn_balace := l_close_qty_cal;
l_open_lot := l_closing_lot;
END LOOP;
END;
/
------------------------------BUNDLE--------------------------------------------------------
CREATE OR REPLACE PROCEDURE XXTNPL.tnpl_om_stock_in_hand_bundle (
p_from_date IN DATE DEFAULT NULL,
p_to_date IN DATE DEFAULT NULL,
p_variety IN VARCHAR2 DEFAULT NULL,
p_gsm IN VARCHAR2 DEFAULT NULL,
p_size IN VARCHAR2 DEFAULT NULL,
p_seg IN VARCHAR2 DEFAULT NULL,
p_org_id IN NUMBER DEFAULT NULL
)
IS
--DECLARE
-- p_from_date DATE := '1-May-2013';
-- p_to_date DATE := '02-May-2013';
-- p_variety VARCHAR2 (25) := NULL;
-- p_gsm VARCHAR2 (25) := NULL;
-- p_size VARCHAR2 (25) := NULL;
-- p_seg VARCHAR2 (35) := NULL; --'U3NRDPR008005850910003';
-- p_org_id NUMBER := 110;
-- l_opn_balace NUMBER := NULL;
l_opn_balace NUMBER;
l_close_qty_cal NUMBER;
l_open_qty_bal NUMBER;
l_open_qty_balance NUMBER;
l_receipt_qty NUMBER;
l_openinig_lot NUMBER;
l_open_lot NUMBER;
l_closing_lot NUMBER;
CURSOR c1
IS
SELECT *
FROM TNPL_OM_STOCK_IN_HAND
ORDER BY creation_date;
BEGIN
BEGIN
execute immediate 'truncate table TNPL_OM_STOCK_IN_HAND';
INSERT INTO TNPL_OM_STOCK_IN_HAND
(creation_date, receipt_qty, receipt_lot, variety, gsm,
width_size, dispatch_qty, dispatch_lot, sales_return,
sales_lot, stock_return, stock_lot, item_code)
(SELECT creation_date, SUM (receipt_qty) receipt_qty,
ROUND ( NVL ((receipt_qty / NVL (conversion_rate, 1)), 0)
* 1000
) receipt_lot,
variety, gsm, width_size, SUM (dispatch_qty) dispatch_qty,
ROUND ( NVL ((dispatch_qty / NVL (conversion_rate, 1)), 0)
* 1000
) dispatch_lot,
SUM (sales_return) sales_return,
ROUND ( NVL ((sales_return / NVL (conversion_rate, 1)), 0)
* 1000
) sales_lot,
SUM (stock_return) stock_return,
ROUND ( NVL ((stock_return / NVL (conversion_rate, 1)), 0)
* 1000
) stock_lot,
item_code
FROM (SELECT creation_date, conversion_rate,
SUM (receipt_qty) receipt_qty, variety, gsm,
width_size, SUM (dispatch_qty) dispatch_qty,
sales_return, stock_return, item_code
FROM (SELECT creation_date, transaction_type_name,
conversion_rate,
SUM (receipt_qty) receipt_qty, variety,
gsm, width_size,
SUM (dispatch_qty) dispatch_qty,
SUM (sales_qty) sales_return,
SUM (stock_return) stock_return,
item_code
FROM (SELECT TRUNC
(mmt.transaction_date
) creation_date,
msib.segment1 item_code,
mucc.conversion_rate
conversion_rate,
mtt.transaction_type_name,
(CASE
WHEN mtt.transaction_type_name NOT IN
('Sales order issue',
'Int Order Intr Ship',
'RMA Receipt')
THEN mmt.transaction_quantity
ELSE 0
END
) receipt_qty,
SUBSTR (segment1, 4,
3) variety,
SUBSTR (segment1, 8, 4) gsm,
SUBSTR (segment1,
12,
4
) width_size,
(CASE
WHEN mtt.transaction_type_name IN
('Sales order issue',
'Int Order Intr Ship')
THEN mmt.transaction_quantity
ELSE 0
END
) dispatch_qty,
(CASE
WHEN mtt.transaction_type_name IN
('RMA Receipt')
THEN mmt.transaction_quantity
ELSE 0
END
) sales_qty,
(CASE
WHEN mtt.transaction_type_name IN
('Subinventory Transfer')
AND mmt.subinventory_code IN
('ASRS-1', 'ASRS-2',
'GODOWN-1')
AND mmt.transfer_subinventory IN
('BUNDLE_ST',
'BOX_ST',
'PACK_ST')
AND mmt.transaction_quantity <
0
THEN mmt.transaction_quantity
ELSE 0
END
) stock_return
FROM apps.mtl_system_items_b msib,
apps.mtl_material_transactions mmt,
apps.mtl_transaction_types mtt,
apps.rcv_transactions rt,
apps.mtl_uom_class_conversions mucc
WHERE msib.segment1 =
NVL (p_seg, msib.segment1)
AND SUBSTR (msib.segment1, 1, 1) IN
('U', 'P', 'R', 'S', 'X',
'Z')
AND msib.inventory_item_id =
mmt.inventory_item_id
AND msib.organization_id =
mmt.organization_id
AND msib.inventory_item_id =
mmt.inventory_item_id
AND rt.transaction_id(+) =
mmt.transaction_id
AND msib.organization_id =
NVL (p_org_id,
msib.organization_id
)
AND SUBSTR (segment1, 4, 3) =
NVL (p_variety,
SUBSTR (segment1, 4, 3)
)
AND SUBSTR (segment1, 8, 4) =
NVL (p_gsm,
SUBSTR (segment1, 8, 4)
)
AND SUBSTR (segment1, 12, 4) =
NVL (p_size,
SUBSTR (segment1, 12,
4)
)
AND mtt.transaction_type_id =
mmt.transaction_type_id
AND mmt.transaction_source_type_id =
mtt.transaction_source_type_id
AND mmt.transaction_date
BETWEEN (p_from_date)
AND (p_to_date)
AND mucc.inventory_item_id =
mmt.inventory_item_id)
GROUP BY creation_date,
transaction_type_name,
variety,
gsm,
width_size,
conversion_rate,
item_code)
GROUP BY creation_date,
variety,
gsm,
width_size,
sales_return,
stock_return,
conversion_rate,
item_code)
GROUP BY creation_date,
variety,
gsm,
width_size,
receipt_qty,
conversion_rate,
dispatch_qty,
sales_return,
stock_return,
item_code);
END;
BEGIN
SELECT stock
INTO l_opn_balace
FROM (SELECT NVL (SUM (mmt.transaction_quantity), 0) AS stock
FROM apps.mtl_system_items_b msib,
apps.mtl_material_transactions mmt,
apps.rcv_transactions rt
WHERE msib.inventory_item_id = mmt.inventory_item_id
AND msib.organization_id = mmt.organization_id
AND msib.segment1 = NVL (p_seg, msib.segment1)
AND rt.transaction_id(+) = mmt.transaction_id
AND msib.organization_id =
NVL (p_org_id, msib.organization_id)
AND mmt.transaction_date < p_from_date
AND transaction_action_id NOT IN (24, 30)
AND (logical_transaction = 2 OR logical_transaction IS NULL
)
ORDER BY mmt.transaction_date);
END;
BEGIN
SELECT SUM (stock) stock
INTO l_open_lot
FROM (SELECT ROUND (NVL ((stock / NVL (conversion_rate, 1)), 0) * 1000
) stock
FROM (SELECT NVL ((mmt.transaction_quantity), 0) AS stock,
uom.conversion_rate
FROM apps.mtl_system_items_b msib,
apps.mtl_material_transactions mmt,
apps.rcv_transactions rt,
apps.mtl_uom_class_conversions uom
WHERE msib.inventory_item_id = mmt.inventory_item_id
AND msib.organization_id = mmt.organization_id
AND msib.segment1 = NVL (p_seg, msib.segment1)
AND rt.transaction_id(+) = mmt.transaction_id
AND msib.organization_id =
NVL (p_org_id, msib.organization_id)
AND mmt.transaction_date < p_from_date
AND uom.inventory_item_id = mmt.inventory_item_id
AND transaction_action_id NOT IN (24, 30)
AND ( logical_transaction = 2
OR logical_transaction IS NULL
)
--group by uom.conversion_rate , mmt.transaction_date
ORDER BY mmt.transaction_date));
END;
DBMS_OUTPUT.put_line (l_opn_balace);
FOR i IN c1
LOOP
IF p_seg IS NULL
THEN
SELECT stock
INTO l_opn_balace
FROM (SELECT NVL (SUM (mmt.transaction_quantity), 0) AS stock
FROM apps.mtl_system_items_b msib,
apps.mtl_material_transactions mmt,
apps.rcv_transactions rt
WHERE msib.inventory_item_id = mmt.inventory_item_id
AND msib.organization_id = mmt.organization_id
AND msib.segment1 = i.item_code
AND rt.transaction_id(+) = mmt.transaction_id
AND msib.organization_id =
NVL (p_org_id, msib.organization_id)
AND mmt.transaction_date < p_from_date
AND transaction_action_id NOT IN (24, 30)
AND ( logical_transaction = 2
OR logical_transaction IS NULL
)
ORDER BY mmt.transaction_date);
ELSE
l_open_qty_bal := NVL (l_opn_balace, 0);
END IF;
DBMS_OUTPUT.put_line ('l_open_qty_bal' || l_open_qty_bal);
IF p_seg IS NULL
THEN
SELECT SUM (stock) stock
INTO l_openinig_lot
FROM (SELECT ROUND ( NVL ((stock / NVL (conversion_rate, 1)), 0)
* 1000
) stock
FROM (SELECT NVL ((mmt.transaction_quantity),
0
) AS stock,
uom.conversion_rate
FROM apps.mtl_system_items_b msib,
apps.mtl_material_transactions mmt,
apps.rcv_transactions rt,
apps.mtl_uom_class_conversions uom
WHERE msib.inventory_item_id =
mmt.inventory_item_id
AND msib.organization_id = mmt.organization_id
AND msib.segment1 = i.item_code
AND rt.transaction_id(+) = mmt.transaction_id
AND msib.organization_id =
NVL (p_org_id, msib.organization_id)
AND mmt.transaction_date < p_from_date
AND uom.inventory_item_id =
mmt.inventory_item_id
AND transaction_action_id NOT IN (24, 30)
AND ( logical_transaction = 2
OR logical_transaction IS NULL
)
--group by uom.conversion_rate , mmt.transaction_date
ORDER BY mmt.transaction_date));
ELSE
DBMS_OUTPUT.put_line (l_open_lot);
l_openinig_lot := NVL (l_open_lot, 0);
DBMS_OUTPUT.put_line ('l_openinig_lot' || l_openinig_lot);
END IF;
---- DBMS_OUTPUT.put_line ('l_open_lot' || l_open_lot);
l_closing_lot :=
NVL (l_openinig_lot, 0)
+ NVL (i.receipt_lot, 0)
+ NVL (i.dispatch_lot, 0);
l_open_qty_bal := NVL (l_opn_balace, 0);
DBMS_OUTPUT.put_line (l_open_qty_bal);
l_close_qty_cal :=
NVL (l_open_qty_bal, 0)
+ NVL (i.receipt_qty, 0)
+ NVL (i.dispatch_qty, 0);
UPDATE TNPL_OM_STOCK_IN_HAND
SET bal_stock = l_open_qty_bal,
closing_qty = l_close_qty_cal,
bal_sto_lot = l_openinig_lot,
closing_lot = l_closing_lot
WHERE creation_date = i.creation_date AND item_code = i.item_code;
l_opn_balace := l_close_qty_cal;
l_open_lot := l_closing_lot;
END LOOP;
END;
/
