Data extraction issue

manasvi_naikdhure — Fri, 20 May 2022 10:00:31 GMT

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;

Re: Data extraction issue

rubenmarin — Fri, 20 May 2022 10:15:54 GMT

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.

Re: Data extraction issue

manasvi_naikdhure — Fri, 20 May 2022 10:22:19 GMT

Thank you for the help.I will try this.

topic Data extraction issue in App Development

Data extraction issue

Re: Data extraction issue

Re: Data extraction issue