I have to extract data using below oracle query. But No records are coming. Please help
OPM_INVENTORY:
//LOAD GRP,
// ITEM,
// DESCRIPTION,
// UOM,
// Date(PO_RECPT_DATE,'DD-MMM-YYYY') as PO_RECPT_DATE,
// Date(MISC_RECPT_DATE,'DD-MMM-YYYY') as MISC_RECPT_DATE,
// AMOUNT,
// AGE,
// QTY,
// [3Months],
// [6Months],
// [1Year],
// [2Year],
// Grtr2Year;
//FROM
//[D:\Manasvi\New Dev\BPU OPM DATA APR-22.xls]
//(biff, embedded labels, table is [Sheet 1$]);
SELECT '$(vStartDateGA)'as period,
GRP,
-- organization_code,
item,
REGEXP_REPLACE(REPLACE(REPLACE(description,chr(13),' '),chr(10),' '),'(.+\w+\.) (\w+\..+)','\1\2')description,
uom,
-- SUBINVENTORY_CODE,
po_recpt_date,
MISC_RECPT_DATE,
nvl(po_recpt_date,MISC_RECPT_DATE) REC_DATE,
SUM (amount) amount,
OPM_AGE AGE,
qty,
--COST,
-- organization_name,
(CASE WHEN OPM_AGE <= 90 THEN SUM (amount) END) "3Months",
(CASE WHEN OPM_AGE > 90 AND OPM_AGE <= 180 THEN SUM (amount) END) "6Months",
(CASE WHEN OPM_AGE > 180 AND OPM_AGE <= 365 THEN SUM (amount) END) "1Year",
(CASE WHEN OPM_AGE > 365 AND OPM_AGE < 730 THEN SUM (amount) END) "2Year",
(CASE WHEN OPM_AGE > 730 THEN SUM (amount) END) "Grtr2Year"
FROM (SELECT item,
description,
uom,
-- SUBINVENTORY_CODE,
po_recpt_date,
MISC_RECPT_DATE,
GRP,
COST,
NVL (qty * COST, 0) amount,
inventory_item_id,
qty,
-- organization_name,
ROUND(TRUNC (TO_DATE (NVL ('$(vStartDateGA)', SYSDATE)))
- TRUNC (po_recpt_date))
age,OPM_AGE
-- organization_code
FROM ( SELECT
a.item,
a.description,
a.uom,
-- SUBINVENTORY_CODE,
SUM (a.qty) qty,
a.GRP,
a.inventory_item_id,
b.COST,
a.po_recpt_date,
A.MISC_RECPT_DATE,
-- a.organization_name,
ROUND(TRUNC (TO_DATE (NVL ('$(vStartDateGA)', SYSDATE)))
- TRUNC(NVL (a.po_recpt_date,
A.MISC_RECPT_DATE)))
age ,
--(to_date(NVL('$(vStartDateGA)',sysdate)))-to_date(max(TRX_DATE)) OPM_AGE
-- (CASE WHEN sum(bal_qty) > 0 THEN sum(to_date('$(vStartDateGA)')-to_date(LAST_TRX_ADTE) )else 0 END) Age
(CASE WHEN to_date('$(vStartDateGA)')>='31-MAR-22' THEN
(to_date(NVL('$(vStartDateGA)',sysdate)))-to_date(max(NVL(ATT_TRX_DATE,'$(vStartDateGA)')))
else
(to_date('$(vStartDateGA)')-NVL(MAX (TRUNC (po_recpt_date)),max(to_date(ATT_TRX_DATE))))
END ) OPM_AGE
-- organization_code
FROM (
SELECT msi.segment1 item,
msi.description,
SUBSTR (msi.segment1, 4, 2) GRP,
msi.primary_unit_of_measure uom,
msi.inventory_item_id,
SUM (mtr.primary_quantity) qty,
(SELECT MAX(RSH.creation_date)
FROM apps.MTL_MATERIAL_TRANSACTIONS MTR,
apps.RCV_TRANSACTIONS RT,
apps.rcv_shipment_lines RHL,
apps.rcv_shipment_headers RSH
WHERE INVENTORY_ITEM_ID =
MSI.INVENTORY_ITEM_ID
-- and MTR.TRANSACTION_ID=293305189
AND RCV_TRANSACTION_ID =
RT.TRANSACTION_ID
AND RT.SHIPMENT_LINE_ID =
RHL.SHIPMENT_LINE_ID
AND RT.SHIPMENT_HEADER_ID =
RSH.SHIPMENT_HEADER_ID
AND TRUNC(RSH.CREATION_DATE) <=
'$(vStartDateGA)')
po_recpt_date ,----------------------------------------------------- ,
(SELECT decode(trunc(MAX (A.transaction_date)),'31-MAR-22',to_date(max(nvl(A.attribute1,A.transaction_date))),trunc(MAX (A.transaction_date)))
FROM apps.MTL_MATERIAL_TRANSACTIONS A,
apps.mtl_system_items c
WHERE 1 = 1
AND A.INVENTORY_ITEM_ID =
C.INVENTORY_ITEM_ID
AND A.organization_id =
C.organization_id
AND c.segment1 =
msi.segment1
AND A.transaction_date <=
'$(vStartDateGA)'--AND ROWNUM = 1
)
MISC_RECPT_DATE,
SUBINVENTORY_CODE,
--NVL(MTR.ATTRIBUTE1,trunc(transaction_date)) TRX_DATE
MTR.ATTRIBUTE1 ATT_TRX_DATE
-- organization_code
-------------------------------------------------------
FROM apps.mtl_system_items msi,
apps.mtl_material_transactions mtr
-- org_organization_definitions ood
WHERE 1 = 1 -- mtr.organization_id = '$(Org_ID)'
-- and ood.organization_id=msi.organization_id
AND mtr.organization_id IN (select organization_id from apps.CM_WHSE_ASC where COST_ORGANIZATION_ID=DECODE (:ORGANIZATION ID,'83',247,'84',329))
AND TRUNC (mtr.transaction_date) <= to_date('$(vStartDateGA)')
AND msi.inventory_item_id =
mtr.inventory_item_id
AND msi.organization_id =
mtr.organization_id
and msi.segment1 not like 'IP%'
--and msi.segment1='BPP100101XX01L002'
GROUP BY msi.segment1,
mtr.ATTRIBUTe1,
msi.description,
msi.primary_unit_of_measure,
msi.organization_id,
msi.inventory_item_id,
SUBINVENTORY_CODE,transaction_date
--organization_code
) a,
(SELECT inventory_item_id, SUM (DISTINCT (ROUND (cmpnt_cost, 4))) COST
FROM apps.cm_cmpt_dtl
WHERE period_id = (SELECT DISTINCT period_id
FROM apps.gmf_period_statuses
WHERE PERIOD_CODE = '$(Period)'
AND LEGAL_ENTITY_ID = DECODE ('$(Org_ID)',
'83',
23275,
'84',
23276))
group by inventory_item_id
) b
WHERE a.inventory_item_id = b.inventory_item_id
GROUP BY a.item,
a.description,
a.uom,
-- SUBINVENTORY_CODE,
-- organization_code,
-- a.qty,
a.GRP,
a.inventory_item_id,
b.COST,
a.po_recpt_date,
A.MISC_RECPT_DATE))
WHERE 1=1-- qty <> 0 AND amount <> 0 AND SIGN (amount) <> '-1'
-- and age not like '%-%'
GROUP BY GRP,
item,
description,
uom,
--SUBINVENTORY_CODE,
--organization_code,
po_recpt_date,
MISC_RECPT_DATE,
--amount,
age,
OPM_AGE,
qty
ORDER BY 2, 7;
EXIT SCRIPT;
