Home » Applications » Oracle Fusion Apps & E-Business Suite » Please convert this to 11i
Please convert this to 11i [message #206138] Tue, 28 November 2006 22:33 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
Can anyone please convert the following select statement to be made 11i compatible.

SELECT
SL.CREATION_DATE CREATION_DATE,
DEP.DATE_CLOSED LAST_UPDATE_DATE,
SUBSTR(SL.ATTRIBUTE1, 1, INSTR(SL.ATTRIBUTE1, '^', 1, 1)-1) ATTRIBUTE1,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 1)+1, INSTR(SL.ATTRIBUTE1, '^', 1, 2)-INSTR(SL.ATTRIBUTE1, '^', 1, 1)-1) ATTRIBUTE2,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 2)+1, INSTR(SL.ATTRIBUTE1, '^', 1, 3)-INSTR(SL.ATTRIBUTE1, '^', 1, 2)-1) ATTRIBUTE3,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 3)+1, LENGTH(SL.ATTRIBUTE1)-INSTR(SL.ATTRIBUTE1, '^', 1, 3)-1) ATTRIBUTE5,
PLD.WAREHOUSE_ID ORGANIZATION_ID ,
PL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID ,
MSI1.SEGMENT1||MSI1.SEGMENT2 INVENTORY_ITEM,
SUM(PLD.SHIPPED_QUANTITY) SHIPPED_QUANTITY,
DEP.ACTUAL_DEPARTURE_DATE ACTUAL_DEPARTURE_DATE,
TRUNC(DEP.ACTUAL_DEPARTURE_DATE - 1,'D') + 1 FORECAST_DATE,
MSI1.ATTRIBUTE9 MRP_FLAG,
MSI1.ITEM_TYPE ITEM_TYPE,
MSI1.END_ASSEMBLY_PEGGING_FLAG END_ASSEMBLY_PEGGING_FLAG
FROM
apps.MTL_SYSTEM_ITEMS MSI1 ,
apps.NUMP_CATEGORIES_V NCV,
apps.SO_PICKING_LINE_DETAILS PLD ,
apps.SO_LINES_ALL SL ,
apps.MTL_PARAMETERS ORG ,
apps.HR_ORGANIZATION_UNITS H_ORG ,
apps.SO_PICKING_LINES_ALL PL ,
apps.SO_HEADERS_ALL SH ,
apps.SO_PICKING_BATCHES_ALL PB ,
apps.SO_PICKING_HEADERS_ALL PH ,
apps.WSH_DELIVERIES DEL ,
apps.WSH_DEPARTURES DEP
WHERE
DEP.DATE_CLOSED > (SELECT NVL(MAX(LAST_UPDATE_DATE), TO_DATE('2000/01/01', 'YYYY/MM/DD'))
FROM NUMP_RESULTS
WHERE RESULT_QUANTITY != 0)
AND PLD.PICKING_LINE_ID = PL.PICKING_LINE_ID
AND PH.PICKING_HEADER_ID = PL.PICKING_HEADER_ID
AND PH.BATCH_ID = PB.BATCH_ID
AND PH.ORDER_HEADER_ID = SH.HEADER_ID
AND ORG.ORGANIZATION_ID = PLD.WAREHOUSE_ID
AND H_ORG.ORGANIZATION_ID = PLD.WAREHOUSE_ID
AND PL.ORDER_LINE_ID = SL.LINE_ID
AND PL.INVENTORY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
AND PL.INVENTORY_ITEM_ID = NCV.INVENTORY_ITEM_ID
AND PLD.WAREHOUSE_ID = MSI1.ORGANIZATION_ID
AND DEL.ACTUAL_DEPARTURE_ID = DEP.DEPARTURE_ID
AND DEL.DELIVERY_ID = PLD.DELIVERY_ID
AND NCV.INVENTORY_ITEM_ID = PL.INVENTORY_ITEM_ID
GROUP BY SL.CREATION_DATE,
DEP.DATE_CLOSED,
SUBSTR(SL.ATTRIBUTE1, 1, INSTR(SL.ATTRIBUTE1, '^', 1, 1)-1) ,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 1)+1, INSTR(SL.ATTRIBUTE1, '^', 1, 2)-INSTR(SL.ATTRIBUTE1, '^', 1, 1)-1) ,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 2)+1, INSTR(SL.ATTRIBUTE1, '^', 1, 3)-INSTR(SL.ATTRIBUTE1, '^', 1, 2)-1) ,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 3)+1, LENGTH(SL.ATTRIBUTE1)-INSTR(SL.ATTRIBUTE1, '^', 1, 3)-1) ,
PLD.WAREHOUSE_ID ,
PL.INVENTORY_ITEM_ID ,
MSI1.SEGMENT1||MSI1.SEGMENT2 ,
DEP.ACTUAL_DEPARTURE_DATE ,
TRUNC(DEP.ACTUAL_DEPARTURE_DATE - 1,'D') + 1 ,
MSI1.ATTRIBUTE9 ,
MSI1.ITEM_TYPE ,
MSI1.END_ASSEMBLY_PEGGING_FLAG

Report message to a moderator

Re: Please convert this to 11i [message #206243 is a reply to message #206138] Wed, 29 November 2006 04:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
What is the problem in this query?
Are you getting any error?
By
Vamsi

Report message to a moderator

Re: Please convert this to 11i [message #206253 is a reply to message #206138] Wed, 29 November 2006 05:48 Go to previous messageGo to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
The SO tables are obsolete in 11i and are replaced with OE tables. I need someone to make the above select statement 11i compatible by changing the tables and their suitable mappings.

Report message to a moderator

Re: Please convert this to 11i [message #206273 is a reply to message #206138] Wed, 29 November 2006 07:01 Go to previous messageGo to next message
abed24
Messages: 18
Registered: October 2006
Location: Jordan
Junior Member
NUMP_CATEGORIES_V and NUMP_RESULTS doesn't exists in user_objects !!!


SELECT
SL.CREATION_DATE CREATION_DATE,
DEP.DATE_CLOSED LAST_UPDATE_DATE,
SUBSTR(SL.ATTRIBUTE1, 1, INSTR(SL.ATTRIBUTE1, '^', 1, 1)-1) ATTRIBUTE1,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 1)+1, INSTR(SL.ATTRIBUTE1, '^', 1, 2)-INSTR(SL.ATTRIBUTE1, '^', 1, 1)-1) ATTRIBUTE2,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 2)+1, INSTR(SL.ATTRIBUTE1, '^', 1, 3)-INSTR(SL.ATTRIBUTE1, '^', 1, 2)-1) ATTRIBUTE3,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 3)+1, LENGTH(SL.ATTRIBUTE1)-INSTR(SL.ATTRIBUTE1, '^', 1, 3)-1) ATTRIBUTE5,
PLD.WAREHOUSE_ID ORGANIZATION_ID ,
PL.INVENTORY_ITEM_ID INVENTORY_ITEM_ID ,
MSI1.SEGMENT1||MSI1.SEGMENT2 INVENTORY_ITEM,
SUM(PLD.SHIPPED_QUANTITY) SHIPPED_QUANTITY,
DEP.ACTUAL_DEPARTURE_DATE ACTUAL_DEPARTURE_DATE,
TRUNC(DEP.ACTUAL_DEPARTURE_DATE - 1,'D') + 1 FORECAST_DATE,
MSI1.ATTRIBUTE9 MRP_FLAG,
MSI1.ITEM_TYPE ITEM_TYPE,
MSI1.END_ASSEMBLY_PEGGING_FLAG END_ASSEMBLY_PEGGING_FLAG
FROM
apps.MTL_SYSTEM_ITEMS MSI1 ,
--apps.NUMP_CATEGORIES_V NCV,
apps.SO_PICKING_LINE_DETAILS PLD ,
apps.SO_LINES_ALL SL ,
apps.MTL_PARAMETERS ORG ,
apps.HR_ORGANIZATION_UNITS H_ORG ,
apps.SO_PICKING_LINES_ALL PL ,
apps.SO_HEADERS_ALL SH ,
apps.SO_PICKING_BATCHES_ALL PB ,
apps.SO_PICKING_HEADERS_ALL PH ,
apps.WSH_DELIVERIES DEL ,
apps.WSH_DEPARTURES DEP
WHERE
/*DEP.DATE_CLOSED > (SELECT NVL(MAX(LAST_UPDATE_DATE), TO_DATE('2000/01/01', 'YYYY/MM/DD'))
FROM NUMP_RESULTS
WHERE RESULT_QUANTITY != 0)
AND */PLD.PICKING_LINE_ID = PL.PICKING_LINE_ID
AND PH.PICKING_HEADER_ID = PL.PICKING_HEADER_ID
AND PH.BATCH_ID = PB.BATCH_ID
AND PH.ORDER_HEADER_ID = SH.HEADER_ID
AND ORG.ORGANIZATION_ID = PLD.WAREHOUSE_ID
AND H_ORG.ORGANIZATION_ID = PLD.WAREHOUSE_ID
AND PL.ORDER_LINE_ID = SL.LINE_ID
AND PL.INVENTORY_ITEM_ID = MSI1.INVENTORY_ITEM_ID
--AND PL.INVENTORY_ITEM_ID = NCV.INVENTORY_ITEM_ID
AND PLD.WAREHOUSE_ID = MSI1.ORGANIZATION_ID
AND DEL.ACTUAL_DEPARTURE_ID = DEP.DEPARTURE_ID
AND DEL.DELIVERY_ID = PLD.DELIVERY_ID
--AND NCV.INVENTORY_ITEM_ID = PL.INVENTORY_ITEM_ID
GROUP BY SL.CREATION_DATE,
DEP.DATE_CLOSED,
SUBSTR(SL.ATTRIBUTE1, 1, INSTR(SL.ATTRIBUTE1, '^', 1, 1)-1) ,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 1)+1, INSTR(SL.ATTRIBUTE1, '^', 1, 2)-INSTR(SL.ATTRIBUTE1, '^', 1, 1)-1) ,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 2)+1, INSTR(SL.ATTRIBUTE1, '^', 1, 3)-INSTR(SL.ATTRIBUTE1, '^', 1, 2)-1) ,
SUBSTR(SL.ATTRIBUTE1, INSTR(SL.ATTRIBUTE1, '^', 1, 3)+1, LENGTH(SL.ATTRIBUTE1)-INSTR(SL.ATTRIBUTE1, '^', 1, 3)-1) ,
PLD.WAREHOUSE_ID ,
PL.INVENTORY_ITEM_ID ,
MSI1.SEGMENT1||MSI1.SEGMENT2 ,
DEP.ACTUAL_DEPARTURE_DATE ,
TRUNC(DEP.ACTUAL_DEPARTURE_DATE - 1,'D') + 1 ,
MSI1.ATTRIBUTE9 ,
MSI1.ITEM_TYPE ,
MSI1.END_ASSEMBLY_PEGGING_FLAG

Report message to a moderator

Re: Please convert this to 11i [message #206276 is a reply to message #206273] Wed, 29 November 2006 07:14 Go to previous messageGo to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
what's this buddy. There is no change from the code that i has pasted. I said the SO tables do not exist in 11i and are replaced by OE tables. Like so_headers_all is now oe_order_headers_all etc. And btw the tables startin with NU* ARE CUSTOM TABLES

Report message to a moderator

Re: Please convert this to 11i [message #206313 is a reply to message #206276] Wed, 29 November 2006 09:59 Go to previous messageGo to next message
abed24
Messages: 18
Registered: October 2006
Location: Jordan
Junior Member
Hi rak007
I use Oracle Applications : 11.5.10.2
and this select worked fine after removing the custom tables.

So whats the problem ?!!!!!!!!!!!!!!!!

Report message to a moderator

Re: Please convert this to 11i [message #206395 is a reply to message #206313] Wed, 29 November 2006 21:56 Go to previous messageGo to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
The select will wok fine but i want the SO tables to be replaced with their OE counterparts. Although they will work but they are not inline with our project requirements.

Report message to a moderator

Re: Please convert this to 11i [message #207541 is a reply to message #206395] Tue, 05 December 2006 23:56 Go to previous message
ninja111
Messages: 1
Registered: December 2006
Junior Member

I am not familiar with the SO tables, but....I know that a lot of these SO tables have been merged into OE tables.....

For an Orders Header level information use OE_ORDER_HEADERS_ALL
and for each order's line level information use OE_ORDER_LINES_ALL.

join between these two table is by header_id. Its a one-to-many relationship between header and lines.

each line from oe_order_lines has a delivery associated with it.....to table wsh_delivery_details. The join is by line_id and source_line_id.

Thanks



Report message to a moderator

Previous Topic: ORACLE FUNCTIONAL OPENING
Next Topic: AIMS Documents
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jul 02 15:54:01 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.