Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
manasvi_naikdhure
Contributor II
Contributor II

Data extraction issue

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;

Labels (1)
2 Replies
rubenmarin

Hi, it should be somethig it his part:

FROM apps.mtl_system_items msi,
apps.mtl_material_transactions mtr
WHERE 1 = 1
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%'

You can try first loading only from apps.mtl_material_transactions mtr Where TRUNC (mtr.transaction_date) <= to_date('$(vStartDateGA)') to check if the issue is here

If that works you can try retrieving each table individually, and check the relations in the other conditions to detect wich one is prevnting to retrieve any data.

manasvi_naikdhure
Contributor II
Contributor II
Author

Thank you for the help.I will try this.