DECLARE
TYPE refcursor IS REF CURSOR;
xdo_cursor refcursor;
TYPE trans_rectype IS RECORD
(
inv_org inv_org_parameters.organization_code%TYPE,
item_number egp_system_items_vl.item_number%TYPE,
item_description egp_system_items_vl.description%TYPE,
transaction_source VARCHAR2 (50),
period_level VARCHAR2 (50),
on_hand_qty_period_level NUMBER
);
l_trans_rec trans_rectype;
TYPE trans_tabtype IS TABLE OF trans_rectype
INDEX BY BINARY_INTEGER;
l_trans_tab trans_tabtype;
l_on_hand_qty_tab trans_tabtype;
l_onhand_post_period_qty_tab trans_tabtype;
l_data_clob CLOB;
CURSOR transaction_cur
IS
SELECT final.INV_ORG,
final.ITEM_NUMBER,
final.ITEM_DESCRIPTION,
final.TRANSACTION_SOURCE,
final.PERIOD_LEVEL,
SUM (final.ON_HAND_QTY_PERIOD_LEVEL) ON_HAND_QTY_PERIOD_LEVEL
FROM ( SELECT b.INV_ORG INV_ORG,
b.ITEM_NUMBER ITEM_NUMBER,
b.ITEM_DESCRIPTION ITEM_DESCRIPTION,
b.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID,
b.TRANSACTION_SOURCE TRANSACTION_SOURCE,
b.PERIOD_LEVEL PERIOD_LEVEL,
b.CURR_ON_HAND_QTY CURR_ON_HAND_QTY,
SUM (b.total_qty) ON_HAND_QTY_PERIOD_LEVEL
FROM (SELECT a.transaction_id transaction_id,
a.transaction_source_type_id transaction_source_type_id,
a.transaction_type_id transaction_type_id,
a.inv_org inv_org,
a.item_number item_number,
a.item_description item_description,
a.po_number po_number,
a.po_supplier_id po_supplier_id,
a.po_supplier_site_id po_supplier_site_id,
/*
a.so_number so_number,
a.so_supplier_id so_supplier_id,
a.so_supplier_site_id so_supplier_site_id,
*/
CASE
--WHEN UPPER(a.transaction_type_id) LIKE '%TRANSFER%INTERORGANIZATION%' THEN
WHEN (a.transaction_type_id) IN ('Transfer Order Interorganization Receipt','Transfer Order Interorganization Shipment','Transfer Order Interorganization Transfer')
THEN
CASE
WHEN (a.total_qty < 0) THEN
'INTERCOMPANY-ISSUE'
WHEN (a.total_qty >= 0) THEN
'INTERCOMPANY-RECEIPT'
END
WHEN a.po_supplier_id IS NULL THEN
CASE
--WHEN(UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'OTHERS-ISSUE'
WHEN ( a.transaction_type_id = 'Purchase Order Receipt' --LIKE'%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'OTHERS-RECEIPT'
END
WHEN NVL((SELECT 'INTERCOMPANY'
FROM fnd_lookup_values flv,
poz_suppliers_v psv,
poz_supplier_sites_v pssv
WHERE 1 = 1
AND flv.lookup_type = 'ICHOR_INTER_COMPANY_SUPPLIER'
AND flv.meaning = psv.vendor_name
AND flv.description = pssv.party_site_name
AND psv.vendor_id = pssv.vendor_id
AND psv.vendor_id = a.po_supplier_id
AND pssv.vendor_site_id = a.po_supplier_site_id
AND TRUNC (NVL (pssv.inactive_date,SYSDATE + 1)) > TRUNC (SYSDATE)
AND psv.enabled_flag = 'Y'
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(flv.start_date_active,SYSDATE-1)) AND TRUNC(NVL(flv.end_date_active,SYSDATE+1))
AND flv.language = 'US'),'SUPPLIERS') = 'INTERCOMPANY'
THEN
CASE
--WHEN (UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'INTERCOMPANY-ISSUE'
WHEN ((a.transaction_type_id) = 'Purchase Order Receipt' --LIKE '%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'INTERCOMPANY-RECEIPT'
END
WHEN NVL ((SELECT 'INTERCOMPANY'
FROM fnd_lookup_values flv,
poz_suppliers_v psv,
poz_supplier_sites_v pssv
WHERE 1 = 1
AND flv.lookup_type ='ICHOR_INTER_COMPANY_SUPPLIER'
AND flv.meaning = psv.vendor_name
AND flv.description = pssv.party_site_name
AND psv.vendor_id = pssv.vendor_id
AND psv.vendor_id = a.po_supplier_id
AND pssv.vendor_site_id = a.po_supplier_site_id
AND TRUNC (NVL (pssv.inactive_date,SYSDATE + 1)) > TRUNC (SYSDATE)
AND psv.enabled_flag = 'Y'
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(flv.start_date_active,SYSDATE-1)) AND TRUNC(NVL(flv.end_date_active,SYSDATE+1))
AND flv.language = 'US'),'SUPPLIERS') = 'SUPPLIERS'
THEN
CASE
--WHEN (UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'SUPPLIERS-ISSUE'
WHEN ((a.transaction_type_id) = 'Purchase Order Receipt' ---LIKE '%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'SUPPLIERS-RECEIPT'
END
END transaction_source,
a.transaction_action_id transaction_action_id,
TO_CHAR (a.transaction_date,'DD-MON-YYYY hh24:mi:ss','nls_date_language=American')transaction_date,
a.txn_period txn_period,
a.period_level period_level,
a.total_qty total_qty,
a.curr_on_hand_qty curr_on_hand_qty
FROM ( SELECT (SELECT TRANSACTION_SOURCE_TYPE_NAME
FROM INV_TXN_SOURCE_TYPES_TL
WHERE TRANSACTION_SOURCE_TYPE_ID = imt.TRANSACTION_SOURCE_TYPE_ID AND language = 'US') TRANSACTION_SOURCE_TYPE_ID,
(SELECT transaction_type_name
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) TRANSACTION_TYPE_ID,
iop.organization_code INV_ORG,
esib.item_number ITEM_NUMBER,
TRIM (esib.description) ITEM_DESCRIPTION,
imt.TRANSACTION_ACTION_ID,
imt.TRANSACTION_DATE,
TO_CHAR (imt.transaction_date,'MON-YYYY','nls_date_language=American')txn_period,
TO_CHAR (imt.transaction_date,'MM','nls_date_language=American')period_level,
imt.transaction_id,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_NUMBER,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT vendor_id
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_SUPPLIER_ID,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT vendor_site_id
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_SUPPLIER_SITE_ID,
/*(SELECT order_number
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id)SO_NUMBER,
(SELECT supplier_id
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id)SO_SUPPLIER_ID,
(SELECT supplier_site_id
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id)SO_SUPPLIER_SITE_ID,
*/
SUM (NVL (imt.transaction_quantity, 0))total_qty,
(SELECT SUM (NVL (ioqd.transaction_quantity,0))
FROM inv_onhand_quantities_detail ioqd
WHERE 1 = 1
AND ioqd.inventory_item_id = esib.inventory_item_id
AND ioqd.organization_id = esib.organization_id
AND EXISTS (SELECT 1
FROM inv_secondary_inventories sub
WHERE 1 = 1
AND sub.secondary_inventory_name = ioqd.subinventory_code
AND sub.availability_type = 1))CURR_ON_HAND_QTY
FROM inv_material_txns imt,
inv_org_parameters iop,
egp_system_items_vl esib
WHERE 1 = 1
AND esib.inventory_item_id = imt.inventory_item_id
AND esib.organization_id = imt.organization_id
AND iop.organization_id = imt.organization_id
AND iop.organization_code = NVL( DECODE (:p_inv_org,
'All', iop.organization_code,
:p_inv_org),
iop.organization_code)
AND esib.item_number IN
DECODE (:p_item_number,
'All', esib.item_number,
:p_item_number)
--Modification done for 1.1 Start here----------------------
/*AND TRUNC (imt.transaction_date) BETWEEN TRUNC(LAST_DAY(ADD_MONTHS(TO_DATE(p_period,'MON-YY','nls_date_language=American'),-1)))+ 1
AND TRUNC(LAST_DAY(TO_DATE(p_period,'MON-YY','nls_date_language=American')))
*/
AND EXISTS ( SELECT 1
FROM cst_period_statuses
WHERE 1=1
AND period_set_name in (SELECT period_set_name FROM gl_calendars )
AND period_name = :p_period
AND TRUNC(imt.transaction_date) BETWEEN TRUNC(start_date) AND TRUNC(end_date)
)
--Modification done for 1.1 End here----------------------
AND EXISTS
(SELECT 1
FROM egp_system_items_b esib1,
egp_system_items_tl esit1,
inv_material_txns imt1
WHERE 1 = 1
AND NVL(esib1.enabled_flag,'Y') = 'Y'
AND NVL(esib1.end_date_active,SYSDATE) >= SYSDATE
AND esib1.inventory_item_id = esit1.inventory_item_id
AND esib1.organization_id = esit1.organization_id
AND imt1.inventory_item_id = esib1.inventory_item_id
AND imt1.organization_id =esib1.organization_id
AND esib1.item_number = esib.item_number
AND EXISTS(SELECT 1
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt1.transaction_type_id
AND transaction_type_name = 'Purchase Order Receipt')
AND EXISTS(SELECT 1
FROM fnd_lookup_values flv, poz_suppliers_v psv, poz_supplier_sites_v pssv
WHERE 1 = 1
AND flv.lookup_type ='ICHOR_INTER_COMPANY_SUPPLIER'
AND flv.meaning = psv.vendor_name
AND flv.description = pssv.party_site_name
AND psv.vendor_id = pssv.vendor_id
AND psv.vendor_id = (SELECT vendor_id
FROM po_headers_all
WHERE po_header_id = imt1.transaction_source_id)
AND pssv.vendor_site_id =(SELECT vendor_site_id
FROM po_headers_all
WHERE po_header_id = imt1.transaction_source_id)
AND TRUNC(NVL(pssv.inactive_date,SYSDATE+1)) > TRUNC(SYSDATE)
AND psv.enabled_flag ='Y'
AND flv.enabled_flag ='Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(flv.start_date_active,SYSDATE-1)) AND TRUNC(NVL(flv.end_date_active,SYSDATE+1))
AND flv.language ='US'))
GROUP BY iop.organization_code,
esib.item_number,
TRIM (esib.description),
esib.inventory_item_id,
esib.organization_id,
imt.TRANSACTION_SOURCE_TYPE_ID,
imt.TRANSACTION_TYPE_ID,
imt.TRANSACTION_ACTION_ID,
imt.TRANSACTION_DATE,
imt.transaction_id,
imt.transaction_source_id) a
WHERE 1 = 1) b
WHERE 1 = 1
GROUP BY b.inv_org,
b.item_number,
b.item_description,
b.transaction_type_id,
b.transaction_source,
b.period_level,
b.curr_on_hand_qty) final
WHERE 1 = 1
GROUP BY final.INV_ORG,
final.ITEM_NUMBER,
final.ITEM_DESCRIPTION,
final.TRANSACTION_SOURCE,
final.PERIOD_LEVEL
ORDER BY final.ITEM_NUMBER,
final.TRANSACTION_SOURCE,
final.PERIOD_LEVEL;
l_opn_stk_supplier NUMBER := 0;
l_opn_stk_intercomp NUMBER := 0;
l_opn_stk_others NUMBER := 0;
l_qty_rec_supplier NUMBER := 0;
l_qty_rec_intercomp NUMBER := 0;
l_qty_rec_others NUMBER := 0;
l_qty_issue_supplier NUMBER := 0;
l_qty_issue_intercomp NUMBER := 0;
l_qty_issue_others NUMBER := 0;
l_clo_stk_supplier NUMBER := 0;
l_clo_stk_intercomp NUMBER := 0;
l_clo_stk_others NUMBER := 0;
CURSOR on_hand_qty_cur (
p_inventory_org VARCHAR2,
p_item_number VARCHAR2)
IS
SELECT final.INV_ORG,
final.ITEM_NUMBER,
final.ITEM_DESCRIPTION,
final.TRANSACTION_SOURCE,
NULL period_level,
SUM (final.ON_HAND_QTY_PERIOD_LEVEL) ON_HAND_QTY_PERIOD_LEVEL
FROM ( SELECT b.INV_ORG INV_ORG,
b.ITEM_NUMBER ITEM_NUMBER,
b.ITEM_DESCRIPTION ITEM_DESCRIPTION,
b.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID,
b.TRANSACTION_SOURCE TRANSACTION_SOURCE,
b.CURR_ON_HAND_QTY CURR_ON_HAND_QTY,
SUM (b.total_qty) ON_HAND_QTY_PERIOD_LEVEL
FROM (SELECT a.transaction_id transaction_id,
a.transaction_source_type_id transaction_source_type_id,
a.transaction_type_id transaction_type_id,
a.inv_org inv_org,
a.item_number item_number,
a.item_description item_description,
a.po_number po_number,
a.po_supplier_id po_supplier_id,
a.po_supplier_site_id po_supplier_site_id,
/*
a.so_number so_number,
a.so_supplier_id so_supplier_id,
a.so_supplier_site_id so_supplier_site_id,
*/
CASE
--WHEN UPPER (a.transaction_type_id) LIKE '%TRANSFER%INTERORGANIZATION%'
WHEN (a.transaction_type_id) IN ('Transfer Order Interorganization Receipt','Transfer Order Interorganization Shipment','Transfer Order Interorganization Transfer')
THEN
CASE
WHEN (a.total_qty < 0)
THEN
'INTERCOMPANY-ISSUE'
WHEN (a.total_qty >= 0)
THEN
'INTERCOMPANY-RECEIPT'
END
WHEN a.po_supplier_id IS NULL
THEN
CASE
--WHEN (UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'OTHERS-ISSUE'
WHEN ((a.transaction_type_id) = 'Purchase Order Receipt' --LIKE'%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'OTHERS-RECEIPT'
END
WHEN NVL ((SELECT 'INTERCOMPANY'
FROM fnd_lookup_values flv,
poz_suppliers_v psv,
poz_supplier_sites_v pssv
WHERE 1 = 1
AND flv.lookup_type ='ICHOR_INTER_COMPANY_SUPPLIER'
AND flv.meaning = psv.vendor_name
AND flv.description = pssv.party_site_name
AND psv.vendor_id = pssv.vendor_id
AND psv.vendor_id = a.po_supplier_id
AND pssv.vendor_site_id = a.po_supplier_site_id
AND TRUNC(NVL(pssv.inactive_date,SYSDATE + 1)) > TRUNC(SYSDATE)
AND psv.enabled_flag = 'Y'
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(flv.start_date_active,SYSDATE-1)) AND TRUNC(NVL(flv.end_date_active,SYSDATE+1))
AND flv.language = 'US'),'SUPPLIERS') = 'INTERCOMPANY'
THEN
CASE
--WHEN (UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'INTERCOMPANY-ISSUE'
WHEN ((a.transaction_type_id) = 'Purchase Order Receipt' --LIKE '%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'INTERCOMPANY-RECEIPT'
END
WHEN NVL ((SELECT 'INTERCOMPANY'
FROM fnd_lookup_values flv,
poz_suppliers_v psv,
poz_supplier_sites_v pssv
WHERE 1 = 1
AND flv.lookup_type ='ICHOR_INTER_COMPANY_SUPPLIER'
AND flv.meaning = psv.vendor_name
AND flv.description = pssv.party_site_name
AND psv.vendor_id = pssv.vendor_id
AND psv.vendor_id = a.po_supplier_id
AND pssv.vendor_site_id = a.po_supplier_site_id
AND TRUNC (NVL(pssv.inactive_date,SYSDATE + 1)) >TRUNC (SYSDATE)
AND psv.enabled_flag = 'Y'
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(flv.start_date_active,SYSDATE-1)) AND TRUNC(NVL(flv.end_date_active,SYSDATE+1))
AND flv.language = 'US'),'SUPPLIERS') = 'SUPPLIERS'
THEN
CASE
--WHEN (UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'SUPPLIERS-ISSUE'
WHEN ((a.transaction_type_id) = 'Purchase Order Receipt' --LIKE '%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'SUPPLIERS-RECEIPT'
END
END transaction_source,
a.transaction_action_id transaction_action_id,
TO_CHAR (a.transaction_date,'DD-MON-YYYY hh24:mi:ss','nls_date_language=American') transaction_date,
a.txn_period txn_period,
a.total_qty total_qty,
a.curr_on_hand_qty curr_on_hand_qty
FROM ( SELECT (SELECT TRANSACTION_SOURCE_TYPE_NAME
FROM INV_TXN_SOURCE_TYPES_TL
WHERE TRANSACTION_SOURCE_TYPE_ID =imt.TRANSACTION_SOURCE_TYPE_ID
AND language = 'US') TRANSACTION_SOURCE_TYPE_ID,
(SELECT transaction_type_name
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) TRANSACTION_TYPE_ID,
iop.organization_code INV_ORG,
esib.item_number ITEM_NUMBER,
TRIM (esib.description) ITEM_DESCRIPTION,
imt.TRANSACTION_ACTION_ID,
imt.TRANSACTION_DATE,
TO_CHAR (imt.transaction_date,'MON-YYYY','nls_date_language=American') txn_period,
TO_CHAR (imt.transaction_date,'MM','nls_date_language=American') period_level,
imt.transaction_id,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_NUMBER,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT vendor_id
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_SUPPLIER_ID,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT vendor_site_id
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_SUPPLIER_SITE_ID,
/*
(SELECT order_number
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id) SO_NUMBER,
(SELECT supplier_id
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id) SO_SUPPLIER_ID,
(SELECT supplier_site_id
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id) SO_SUPPLIER_SITE_ID,
*/
SUM (NVL (imt.transaction_quantity, 0)) total_qty,
(SELECT SUM(NVL(ioqd.transaction_quantity,0))
FROM inv_onhand_quantities_detail ioqd
WHERE 1 = 1
AND ioqd.inventory_item_id = esib.inventory_item_id
AND ioqd.organization_id = esib.organization_id
AND EXISTS(SELECT 1
FROM inv_secondary_inventories sub
WHERE 1 = 1
AND sub.secondary_inventory_name = ioqd.subinventory_code
AND sub.availability_type = 1))CURR_ON_HAND_QTY
FROM inv_material_txns imt,
inv_org_parameters iop,
egp_system_items_vl esib
WHERE 1 = 1
AND esib.inventory_item_id = imt.inventory_item_id
AND esib.organization_id = imt.organization_id
AND iop.organization_id =imt.organization_id
AND iop.organization_code =NVL (p_inventory_org,iop.organization_code)
AND esib.item_number =(CASE
WHEN NVL (p_item_number,'All') = 'All'
THEN
esib.item_number
ELSE
p_item_number
END)
GROUP BY iop.organization_code,
esib.item_number,
TRIM (esib.description),
esib.inventory_item_id,
esib.organization_id,
imt.TRANSACTION_SOURCE_TYPE_ID,
imt.TRANSACTION_TYPE_ID,
imt.TRANSACTION_ACTION_ID,
imt.TRANSACTION_DATE,
imt.transaction_id,
imt.transaction_source_id) a
WHERE 1 = 1) b
WHERE 1 = 1
GROUP BY b.inv_org,
b.item_number,
b.item_description,
b.transaction_type_id,
b.transaction_source,
b.curr_on_hand_qty) final
WHERE 1 = 1
GROUP BY final.INV_ORG,
final.ITEM_NUMBER,
final.ITEM_DESCRIPTION,
final.TRANSACTION_SOURCE
ORDER BY final.ITEM_NUMBER, final.TRANSACTION_SOURCE;
l_onhand_stk_supplier NUMBER := 0;
l_onhand_stk_intercomp NUMBER := 0;
l_onhand_stk_others NUMBER := 0;
CURSOR onhand_qty_post_period_cur (
p_inventory_org VARCHAR2,
p_item_number VARCHAR2,
p_period VARCHAR2)
IS
SELECT final.INV_ORG,
final.ITEM_NUMBER,
final.ITEM_DESCRIPTION,
final.TRANSACTION_SOURCE,
NULL PERIOD_LEVEL,
SUM (final.ON_HAND_QTY_PERIOD_LEVEL) ON_HAND_QTY_PERIOD_LEVEL
FROM ( SELECT b.INV_ORG INV_ORG,
b.ITEM_NUMBER ITEM_NUMBER,
b.ITEM_DESCRIPTION ITEM_DESCRIPTION,
b.TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID,
b.TRANSACTION_SOURCE TRANSACTION_SOURCE,
b.PERIOD_LEVEL PERIOD_LEVEL,
b.CURR_ON_HAND_QTY CURR_ON_HAND_QTY,
SUM (b.total_qty) ON_HAND_QTY_PERIOD_LEVEL
FROM (SELECT a.transaction_id transaction_id,
a.transaction_source_type_id transaction_source_type_id,
a.transaction_type_id transaction_type_id,
a.inv_org inv_org,
a.item_number item_number,
a.item_description item_description,
a.po_number po_number,
a.po_supplier_id po_supplier_id,
a.po_supplier_site_id po_supplier_site_id,
/*
a.so_number so_number,
a.so_supplier_id so_supplier_id,
a.so_supplier_site_id so_supplier_site_id,
*/
CASE
--WHEN UPPER (a.transaction_type_id) LIKE '%TRANSFER%INTERORGANIZATION%'
WHEN (a.transaction_type_id) IN ('Transfer Order Interorganization Receipt','Transfer Order Interorganization Shipment','Transfer Order Interorganization Transfer')
THEN
CASE
WHEN (a.total_qty < 0)
THEN
'INTERCOMPANY-ISSUE'
WHEN (a.total_qty >= 0)
THEN
'INTERCOMPANY-RECEIPT'
END
WHEN a.po_supplier_id IS NULL
THEN
CASE
--WHEN (UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'OTHERS-ISSUE'
WHEN ((a.transaction_type_id) = 'Purchase Order Receipt' --LIKE'%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'OTHERS-RECEIPT'
END
WHEN NVL ((SELECT 'INTERCOMPANY'
FROM fnd_lookup_values flv,
poz_suppliers_v psv,
poz_supplier_sites_v pssv
WHERE 1 = 1
AND flv.lookup_type = 'ICHOR_INTER_COMPANY_SUPPLIER'
AND flv.meaning = psv.vendor_name
AND flv.description = pssv.party_site_name
AND psv.vendor_id = pssv.vendor_id
AND psv.vendor_id = a.po_supplier_id
AND pssv.vendor_site_id = a.po_supplier_site_id
AND TRUNC(NVL(pssv.inactive_date,SYSDATE + 1)) > TRUNC(SYSDATE)
AND psv.enabled_flag = 'Y'
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(flv.start_date_active,SYSDATE-1)) AND TRUNC(NVL(flv.end_date_active,SYSDATE+1))
AND flv.language = 'US'),'SUPPLIERS') = 'INTERCOMPANY'
THEN
CASE
--WHEN (UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'INTERCOMPANY-ISSUE'
WHEN ((a.transaction_type_id) = 'Purchase Order Receipt' --LIKE '%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'INTERCOMPANY-RECEIPT'
END
WHEN NVL((SELECT 'INTERCOMPANY'
FROM fnd_lookup_values flv,
poz_suppliers_v psv,
poz_supplier_sites_v pssv
WHERE 1 = 1
AND flv.lookup_type = 'ICHOR_INTER_COMPANY_SUPPLIER'
AND flv.meaning = psv.vendor_name
AND flv.description = pssv.party_site_name
AND psv.vendor_id = pssv.vendor_id
AND psv.vendor_id = a.po_supplier_id
AND pssv.vendor_site_id = a.po_supplier_site_id
AND TRUNC (NVL(pssv.inactive_date,SYSDATE + 1)) > TRUNC (SYSDATE)
AND psv.enabled_flag = 'Y'
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(flv.start_date_active,SYSDATE-1)) AND TRUNC(NVL(flv.end_date_active,SYSDATE+1))
AND flv.language = 'US'),'SUPPLIERS') = 'SUPPLIERS'
THEN
CASE
--WHEN (UPPER (a.transaction_type_id) LIKE '%ISSUE%' OR UPPER (a.transaction_type_id) LIKE '%RETURN%' OR (a.total_qty < 0))
WHEN ((a.transaction_type_id) IN ('Account Alias Issue','Account Issue','Miscellaneous issue','Movement Request Issue','Residual Quantity Issue','Sales Order Issue',
'Transfer Order Issue','Work in Process Material Issue','Work in Process Negative Material Issue')
OR
(a.transaction_type_id) IN ('RMA Return','Receipt Advice Return','Return to Supplier','Supplier Return Scrap Without Receipt Reference','Supplier Return Shipment Without Receipt Reference',
'Transfer Order Return Pick','Transfer Order Return Receipt','Transfer Order Return Shipment','Transfer Order Return Transfer','Transfer Order Return With Scrap',
'Work in Process Material Return','Work in Process Negative Material Return','Work in Process Product Return')
OR
(a.total_qty < 0)
)
THEN
'SUPPLIERS-ISSUE'
WHEN ((a.transaction_type_id) = 'Purchase Order Receipt' --LIKE '%RECEIPT%'
OR (a.total_qty >= 0))
THEN
'SUPPLIERS-RECEIPT'
END
END transaction_source,
a.transaction_action_id transaction_action_id,
TO_CHAR (a.transaction_date,'DD-MON-YYYY hh24:mi:ss','nls_date_language=American') transaction_date,
a.txn_period txn_period,
a.period_level period_level,
a.total_qty total_qty,
a.curr_on_hand_qty curr_on_hand_qty
FROM ( SELECT (SELECT TRANSACTION_SOURCE_TYPE_NAME
FROM INV_TXN_SOURCE_TYPES_TL
WHERE TRANSACTION_SOURCE_TYPE_ID = imt.TRANSACTION_SOURCE_TYPE_ID AND language = 'US') TRANSACTION_SOURCE_TYPE_ID,
(SELECT transaction_type_name
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) TRANSACTION_TYPE_ID,
iop.organization_code INV_ORG,
esib.item_number ITEM_NUMBER,
TRIM (esib.description) ITEM_DESCRIPTION,
imt.TRANSACTION_ACTION_ID,
imt.TRANSACTION_DATE,
TO_CHAR (imt.transaction_date,'MON-YYYY','nls_date_language=American') txn_period,
TO_CHAR (imt.transaction_date,'MM','nls_date_language=American') period_level,
imt.transaction_id,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT segment1
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_NUMBER,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT vendor_id
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_SUPPLIER_ID,
CASE
WHEN (SELECT transaction_type_name --UPPER(transaction_type_name)
FROM inv_transaction_types_tl
WHERE transaction_type_id = imt.TRANSACTION_TYPE_ID) = 'Purchase Order Receipt' --LIKE '%PURCHASE%ORDER%RECEIPT%'
THEN
(SELECT vendor_site_id
FROM po_headers_all
WHERE po_header_id = imt.transaction_source_id)
ELSE
NULL
END PO_SUPPLIER_SITE_ID,
/*
(SELECT order_number
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id) SO_NUMBER,
(SELECT supplier_id
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id) SO_SUPPLIER_ID,
(SELECT supplier_site_id
FROM doo_headers_all
WHERE header_id = imt.transaction_source_id) SO_SUPPLIER_SITE_ID,
*/
SUM (NVL (imt.transaction_quantity, 0)) total_qty,
(SELECT SUM(NVL (ioqd.transaction_quantity,0))
FROM inv_onhand_quantities_detail ioqd
WHERE 1 = 1
AND ioqd.inventory_item_id = esib.inventory_item_id
AND ioqd.organization_id = esib.organization_id
AND EXISTS(SELECT 1
FROM inv_secondary_inventories sub
WHERE 1 = 1
AND sub.secondary_inventory_name =ioqd.subinventory_code
AND sub.availability_type = 1)) CURR_ON_HAND_QTY
FROM inv_material_txns imt,
inv_org_parameters iop,
egp_system_items_vl esib
WHERE 1 = 1
AND esib.inventory_item_id = imt.inventory_item_id
AND esib.organization_id = imt.organization_id
AND iop.organization_id = imt.organization_id
AND iop.organization_code = NVL (p_inventory_org,iop.organization_code)
AND esib.item_number = (CASE
WHEN NVL (p_item_number,'All') = 'All'
THEN
esib.item_number
ELSE
p_item_number
END)
AND TRUNC(imt.transaction_date) > TRUNC(LAST_DAY(TO_DATE(p_period,'MON-YY','nls_date_language=American')))
GROUP BY iop.organization_code,
esib.item_number,
TRIM (esib.description),
esib.inventory_item_id,
esib.organization_id,
imt.TRANSACTION_SOURCE_TYPE_ID,
imt.TRANSACTION_TYPE_ID,
imt.TRANSACTION_ACTION_ID,
imt.TRANSACTION_DATE,
imt.transaction_id,
imt.transaction_source_id) a
WHERE 1 = 1) b
WHERE 1 = 1
GROUP BY b.inv_org,
b.item_number,
b.item_description,
b.transaction_type_id,
b.transaction_source,
b.period_level,
b.curr_on_hand_qty) final
WHERE 1 = 1
GROUP BY final.INV_ORG,
final.ITEM_NUMBER,
final.ITEM_DESCRIPTION,
final.TRANSACTION_SOURCE
--final.PERIOD_LEVEL
ORDER BY final.ITEM_NUMBER, final.TRANSACTION_SOURCE;
l_onhand_post_period_supplier NUMBER := 0;
l_onhand_post_period_intercomp NUMBER := 0;
l_onhand_post_period_others NUMBER := 0;
l_cal_summary VARCHAR2 (1) := 'N';
l_inv_org inv_org_parameters.organization_code%TYPE;
l_item_number egp_system_items_vl.item_number%TYPE;
BEGIN
l_trans_tab.DELETE;
OPEN transaction_cur;
FETCH transaction_cur BULK COLLECT INTO l_trans_tab;
IF l_trans_tab.COUNT = 0
THEN
CLOSE transaction_cur;
NULL;
ELSE
l_data_clob := EMPTY_CLOB ();
FOR j IN l_trans_tab.FIRST .. l_trans_tab.LAST
LOOP
IF (j = 1)
THEN
l_opn_stk_supplier := 0;
l_opn_stk_intercomp := 0;
l_opn_stk_others := 0;
l_qty_rec_supplier := 0;
l_qty_rec_intercomp := 0;
l_qty_rec_others := 0;
l_qty_issue_supplier := 0;
l_qty_issue_intercomp := 0;
l_qty_issue_others := 0;
l_clo_stk_supplier := 0;
l_clo_stk_intercomp := 0;
l_clo_stk_others := 0;
l_cal_summary := 'N';
--starting the summary details for the first item being processed
l_data_clob :=
l_data_clob
|| '<G_1>'
|| '<INV_ORG>'
|| l_trans_tab (j).inv_org
|| '</INV_ORG>'
|| '<ITEM_NUMBER>'
--|| l_trans_tab (j).item_number
|| REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(l_trans_tab(j).item_number, '&', '&'),'<','<'),'>','>')
|| '</ITEM_NUMBER>'
|| '<ITEM_DESCRIPTION>'
|| REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(l_trans_tab(j).item_description, '&', '&'),'<','<'),'>','>')
--|| REGEXP_REPLACE (l_trans_tab (j).item_description,'&','&')
|| '</ITEM_DESCRIPTION>';
ELSE
--check if current iteration is for a new item
IF ( l_trans_tab (j).inv_org <> l_trans_tab (j - 1).inv_org
OR l_trans_tab (j).item_number <>
l_trans_tab (j - 1).item_number
OR l_trans_tab (j).item_description <>
l_trans_tab (j - 1).item_description) --new combination
THEN
l_cal_summary := 'Y';
--Now closing the stock details for the last item (j-1) being processed------------------------------
-----------Finding On Hand Qty based on trx source----------------
l_on_hand_qty_tab.DELETE;
OPEN on_hand_qty_cur (l_trans_tab (j - 1).inv_org,
l_trans_tab (j - 1).item_number);
FETCH on_hand_qty_cur BULK COLLECT INTO l_on_hand_qty_tab;
IF l_on_hand_qty_tab.COUNT = 0
THEN
CLOSE on_hand_qty_cur;
NULL;
ELSE
FOR k IN l_on_hand_qty_tab.FIRST .. l_on_hand_qty_tab.LAST
LOOP
CASE
WHEN l_on_hand_qty_tab (k).transaction_source =
'SUPPLIERS-ISSUE'
THEN
l_onhand_stk_supplier :=
l_onhand_stk_supplier
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'SUPPLIERS-RECEIPT'
THEN
l_onhand_stk_supplier :=
l_onhand_stk_supplier
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'INTERCOMPANY-ISSUE'
THEN
l_onhand_stk_intercomp :=
l_onhand_stk_intercomp
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'INTERCOMPANY-RECEIPT'
THEN
l_onhand_stk_intercomp :=
l_onhand_stk_intercomp
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'OTHERS-ISSUE'
THEN
l_onhand_stk_others :=
l_onhand_stk_others
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'OTHERS-RECEIPT'
THEN
l_onhand_stk_others :=
l_onhand_stk_others
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
ELSE
NULL;
END CASE;
END LOOP;
CLOSE on_hand_qty_cur;
END IF;
-------------------------------------------------------------------
-----------Finding On Hand Qty post period based on trx source----------------
l_onhand_post_period_qty_tab.DELETE;
OPEN onhand_qty_post_period_cur (l_trans_tab (j - 1).inv_org,
l_trans_tab (j - 1).item_number,
:p_period);
FETCH onhand_qty_post_period_cur
BULK COLLECT INTO l_onhand_post_period_qty_tab;
IF l_onhand_post_period_qty_tab.COUNT = 0
THEN
CLOSE onhand_qty_post_period_cur;
NULL;
ELSE
FOR l IN l_onhand_post_period_qty_tab.FIRST ..
l_onhand_post_period_qty_tab.LAST
LOOP
CASE
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'SUPPLIERS-ISSUE'
THEN
l_onhand_post_period_supplier :=
l_onhand_post_period_supplier
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'SUPPLIERS-RECEIPT'
THEN
l_onhand_post_period_supplier :=
l_onhand_post_period_supplier
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'INTERCOMPANY-ISSUE'
THEN
l_onhand_post_period_intercomp :=
l_onhand_post_period_intercomp
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'INTERCOMPANY-RECEIPT'
THEN
l_onhand_post_period_intercomp :=
l_onhand_post_period_intercomp
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'OTHERS-ISSUE'
THEN
l_onhand_post_period_others :=
l_onhand_post_period_others
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'OTHERS-RECEIPT'
THEN
l_onhand_post_period_others :=
l_onhand_post_period_others
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
ELSE
NULL;
END CASE;
END LOOP;
CLOSE onhand_qty_post_period_cur;
END IF;
-------------------------------------------------------------------
---making issue quantity as positive for Display and calculation purpose---------------
l_qty_issue_supplier := ABS (l_qty_issue_supplier);
l_qty_issue_intercomp := ABS (l_qty_issue_intercomp);
l_qty_issue_others := ABS (l_qty_issue_others);
---making issue quantity as positive for Display and calculation purpose---------------
l_clo_stk_supplier :=
l_onhand_stk_supplier - l_onhand_post_period_supplier;
l_clo_stk_intercomp :=
l_onhand_stk_intercomp - l_onhand_post_period_intercomp;
l_clo_stk_others :=
l_onhand_stk_others - l_onhand_post_period_others;
--------------------Finding Opening Stock based on transaction Source------------------------------
l_opn_stk_supplier :=
(l_clo_stk_supplier + l_qty_issue_supplier)
- l_qty_rec_supplier;
l_opn_stk_intercomp :=
(l_clo_stk_intercomp + l_qty_issue_intercomp)
- l_qty_rec_intercomp;
l_opn_stk_others :=
(l_clo_stk_others + l_qty_issue_others) - l_qty_rec_others;
------------------------------------------------------------------------------------------------------
--closing the aging details for the last item being processed
l_data_clob :=
l_data_clob
|| '<OPN_STK_SUPPLIER>'
|| TO_CHAR (l_opn_stk_supplier)
|| '</OPN_STK_SUPPLIER>'
|| '<OPN_STK_INTERCOMP>'
|| TO_CHAR (l_opn_stk_intercomp)
|| '</OPN_STK_INTERCOMP>'
|| '<OPN_STK_OTHERS>'
|| TO_CHAR (l_opn_stk_others)
|| '</OPN_STK_OTHERS>'
|| '<QTY_REC_SUPPLIER>'
|| TO_CHAR (l_qty_rec_supplier)
|| '</QTY_REC_SUPPLIER>'
|| '<QTY_REC_INTERCOMP>'
|| TO_CHAR (l_qty_rec_intercomp)
|| '</QTY_REC_INTERCOMP>'
|| '<QTY_REC_OTHERS>'
|| TO_CHAR (l_qty_rec_others)
|| '</QTY_REC_OTHERS>'
|| '<QTY_ISSUE_SUPPLIER>'
|| TO_CHAR (l_qty_issue_supplier)
|| '</QTY_ISSUE_SUPPLIER>'
|| '<QTY_ISSUE_INTERCOMP>'
|| TO_CHAR (l_qty_issue_intercomp)
|| '</QTY_ISSUE_INTERCOMP>'
|| '<QTY_ISSUE_OTHERS>'
|| TO_CHAR (l_qty_issue_others)
|| '</QTY_ISSUE_OTHERS>'
|| '<CLO_STK_SUPPLIER>'
|| TO_CHAR (l_clo_stk_supplier)
|| '</CLO_STK_SUPPLIER>'
|| '<CLO_STK_INTERCOMP>'
|| TO_CHAR (l_clo_stk_intercomp)
|| '</CLO_STK_INTERCOMP>'
|| '<CLO_STK_OTHERS>'
|| TO_CHAR (l_clo_stk_others)
|| '</CLO_STK_OTHERS>'
|| '</G_1>';
--resetting the quantities
l_opn_stk_supplier := 0;
l_opn_stk_intercomp := 0;
l_opn_stk_others := 0;
l_qty_rec_supplier := 0;
l_qty_rec_intercomp := 0;
l_qty_rec_others := 0;
l_qty_issue_supplier := 0;
l_qty_issue_intercomp := 0;
l_qty_issue_others := 0;
l_clo_stk_supplier := 0;
l_clo_stk_intercomp := 0;
l_clo_stk_others := 0;
l_onhand_stk_supplier := 0;
l_onhand_stk_intercomp := 0;
l_onhand_stk_others := 0;
l_onhand_post_period_supplier := 0;
l_onhand_post_period_intercomp := 0;
l_onhand_post_period_others := 0;
l_cal_summary := 'N';
--starting the summary details for the current item being processed
l_data_clob :=
l_data_clob
|| '<G_1>'
|| '<INV_ORG>'
|| l_trans_tab (j).inv_org
|| '</INV_ORG>'
|| '<ITEM_NUMBER>'
--|| l_trans_tab (j).item_number
|| REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(l_trans_tab(j).item_number, '&', '&'),'<','<'),'>','>')
|| '</ITEM_NUMBER>'
|| '<ITEM_DESCRIPTION>'
--|| REGEXP_REPLACE (l_trans_tab (j).item_description,'&','&')
|| REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(l_trans_tab(j).item_description, '&', '&'),'<','<'),'>','>')
|| '</ITEM_DESCRIPTION>';
END IF;
END IF;
--cummalting/summing up issue and receive quantities based on transaction source
CASE
WHEN l_trans_tab (j).transaction_source = 'SUPPLIERS-ISSUE'
THEN
l_qty_issue_supplier :=
l_qty_issue_supplier
+ l_trans_tab (j).on_hand_qty_period_level;
WHEN l_trans_tab (j).transaction_source = 'SUPPLIERS-RECEIPT'
THEN
l_qty_rec_supplier :=
l_qty_rec_supplier
+ l_trans_tab (j).on_hand_qty_period_level;
WHEN l_trans_tab (j).transaction_source = 'INTERCOMPANY-ISSUE'
THEN
l_qty_issue_intercomp :=
l_qty_issue_intercomp
+ l_trans_tab (j).on_hand_qty_period_level;
WHEN l_trans_tab (j).transaction_source = 'INTERCOMPANY-RECEIPT'
THEN
l_qty_rec_intercomp :=
l_qty_rec_intercomp
+ l_trans_tab (j).on_hand_qty_period_level;
WHEN l_trans_tab (j).transaction_source = 'OTHERS-ISSUE'
THEN
l_qty_issue_others :=
l_qty_issue_others
+ l_trans_tab (j).on_hand_qty_period_level;
WHEN l_trans_tab (j).transaction_source = 'OTHERS-RECEIPT'
THEN
l_qty_rec_others :=
l_qty_rec_others + l_trans_tab (j).on_hand_qty_period_level;
ELSE
NULL;
END CASE;
l_inv_org := l_trans_tab (j).inv_org;
l_item_number := l_trans_tab (j).item_number;
END LOOP;
IF l_cal_summary = 'N'
THEN
-----------Finding On Hand Qty based on trx source----------------
l_on_hand_qty_tab.DELETE;
OPEN on_hand_qty_cur (l_inv_org, l_item_number);
FETCH on_hand_qty_cur BULK COLLECT INTO l_on_hand_qty_tab;
IF l_on_hand_qty_tab.COUNT = 0
THEN
CLOSE on_hand_qty_cur;
NULL;
ELSE
FOR k IN l_on_hand_qty_tab.FIRST .. l_on_hand_qty_tab.LAST
LOOP
CASE
WHEN l_on_hand_qty_tab (k).transaction_source =
'SUPPLIERS-ISSUE'
THEN
l_onhand_stk_supplier :=
l_onhand_stk_supplier
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'SUPPLIERS-RECEIPT'
THEN
l_onhand_stk_supplier :=
l_onhand_stk_supplier
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'INTERCOMPANY-ISSUE'
THEN
l_onhand_stk_intercomp :=
l_onhand_stk_intercomp
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'INTERCOMPANY-RECEIPT'
THEN
l_onhand_stk_intercomp :=
l_onhand_stk_intercomp
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'OTHERS-ISSUE'
THEN
l_onhand_stk_others :=
l_onhand_stk_others
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
WHEN l_on_hand_qty_tab (k).transaction_source =
'OTHERS-RECEIPT'
THEN
l_onhand_stk_others :=
l_onhand_stk_others
+ l_on_hand_qty_tab (k).on_hand_qty_period_level;
ELSE
NULL;
END CASE;
END LOOP;
CLOSE on_hand_qty_cur;
END IF;
-------------------------------------------------------------------
-----------Finding On Hand Qty post period based on trx source----------------
l_onhand_post_period_qty_tab.DELETE;
OPEN onhand_qty_post_period_cur (l_inv_org, l_item_number, :p_period);
FETCH onhand_qty_post_period_cur
BULK COLLECT INTO l_onhand_post_period_qty_tab;
IF l_onhand_post_period_qty_tab.COUNT = 0
THEN
CLOSE onhand_qty_post_period_cur;
NULL;
ELSE
FOR l IN l_onhand_post_period_qty_tab.FIRST ..
l_onhand_post_period_qty_tab.LAST
LOOP
CASE
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'SUPPLIERS-ISSUE'
THEN
l_onhand_post_period_supplier :=
l_onhand_post_period_supplier
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'SUPPLIERS-RECEIPT'
THEN
l_onhand_post_period_supplier :=
l_onhand_post_period_supplier
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'INTERCOMPANY-ISSUE'
THEN
l_onhand_post_period_intercomp :=
l_onhand_post_period_intercomp
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'INTERCOMPANY-RECEIPT'
THEN
l_onhand_post_period_intercomp :=
l_onhand_post_period_intercomp
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'OTHERS-ISSUE'
THEN
l_onhand_post_period_others :=
l_onhand_post_period_others
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
WHEN l_onhand_post_period_qty_tab (l).transaction_source =
'OTHERS-RECEIPT'
THEN
l_onhand_post_period_others :=
l_onhand_post_period_others
+ l_onhand_post_period_qty_tab (l).on_hand_qty_period_level;
ELSE
NULL;
END CASE;
END LOOP;
CLOSE onhand_qty_post_period_cur;
END IF;
-------------------------------------------------------------------
---making issue quantity as positive for Display and calculation purpose---------------
l_qty_issue_supplier := ABS (l_qty_issue_supplier);
l_qty_issue_intercomp := ABS (l_qty_issue_intercomp);
l_qty_issue_others := ABS (l_qty_issue_others);
---Finding Closing Stock based on overall onhand and post period onhand---------------
l_clo_stk_supplier :=
l_onhand_stk_supplier - l_onhand_post_period_supplier;
l_clo_stk_intercomp :=
l_onhand_stk_intercomp - l_onhand_post_period_intercomp;
l_clo_stk_others :=
l_onhand_stk_others - l_onhand_post_period_others;
--------------------Finding Opening Stock based on transaction Source------------------------------
l_opn_stk_supplier :=
(l_clo_stk_supplier + l_qty_issue_supplier) - l_qty_rec_supplier;
l_opn_stk_intercomp :=
(l_clo_stk_intercomp + l_qty_issue_intercomp)
- l_qty_rec_intercomp;
l_opn_stk_others :=
(l_clo_stk_others + l_qty_issue_others) - l_qty_rec_others;
------------------------------------------------------------------------------------------------------
END IF;
--closing the aging details for the last item being processed
l_data_clob :=
l_data_clob
|| '<OPN_STK_SUPPLIER>'
|| TO_CHAR (l_opn_stk_supplier)
|| '</OPN_STK_SUPPLIER>'
|| '<OPN_STK_INTERCOMP>'
|| TO_CHAR (l_opn_stk_intercomp)
|| '</OPN_STK_INTERCOMP>'
|| '<OPN_STK_OTHERS>'
|| TO_CHAR (l_opn_stk_others)
|| '</OPN_STK_OTHERS>'
|| '<QTY_REC_SUPPLIER>'
|| TO_CHAR (l_qty_rec_supplier)
|| '</QTY_REC_SUPPLIER>'
|| '<QTY_REC_INTERCOMP>'
|| TO_CHAR (l_qty_rec_intercomp)
|| '</QTY_REC_INTERCOMP>'
|| '<QTY_REC_OTHERS>'
|| TO_CHAR (l_qty_rec_others)
|| '</QTY_REC_OTHERS>'
|| '<QTY_ISSUE_SUPPLIER>'
|| TO_CHAR (l_qty_issue_supplier)
|| '</QTY_ISSUE_SUPPLIER>'
|| '<QTY_ISSUE_INTERCOMP>'
|| TO_CHAR (l_qty_issue_intercomp)
|| '</QTY_ISSUE_INTERCOMP>'
|| '<QTY_ISSUE_OTHERS>'
|| TO_CHAR (l_qty_issue_others)
|| '</QTY_ISSUE_OTHERS>'
|| '<CLO_STK_SUPPLIER>'
|| TO_CHAR (l_clo_stk_supplier)
|| '</CLO_STK_SUPPLIER>'
|| '<CLO_STK_INTERCOMP>'
|| TO_CHAR (l_clo_stk_intercomp)
|| '</CLO_STK_INTERCOMP>'
|| '<CLO_STK_OTHERS>'
|| TO_CHAR (l_clo_stk_others)
|| '</CLO_STK_OTHERS>'
|| '</G_1>';
CLOSE transaction_cur;
OPEN :xdo_cursor FOR SELECT l_data_clob FROM DUAL;
END IF;
END;