we have one query that is causing issue.I had done some changes,but before that also it was taking more time and it's because of one query which is select part.When i comment out this section i got all the rows within 2 mins and after putting that it took 45 minutes to completes.Is there any way we can handle this or write in some other way.
**NVL (**
PO.model_string,
(SELECT DISTINCT
FIRST_VALUE (
ml.model_string)
OVER (PARTITION BY ol.INVENTORY_ITEM_ID
ORDER BY ol.creation_date DESC)
model_string
FROM apps.xxom_3lp_sym_ora_order_lines ml,
apps.oe_order_lines_all ol
WHERE ml.LINE_ID = ol.LINE_ID
AND ol.ship_from_org_id = po.organization_id
AND ol.INVENTORY_ITEM_ID = po.INVENTORY_ITEM_ID
AND TRIM (ml.model_string) IS NOT NULL))
MODEL_STRING,
The actual query is:
WITH
mmt
AS
(SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_N9) */
mmt.SUBINVENTORY_CODE,
mmt.TRANSACTION_SOURCE_ID,
MMT.TRANSACTION_DATE,
MMT.LAST_UPDATE_DATE,
MMT.INVENTORY_ITEM_ID,
MMT.ORGANIZATION_ID,
MMT.RCV_TRANSACTION_ID,
MMT.last_updated_by,
MMT.LOCATOR_ID,
MMT.TRANSACTION_ID,
mmt.TRANSACTION_SOURCE_TYPE_ID,
mmt.transaction_type_id,
MP.CALENDAR_CODE,
MP.CALENDAR_EXCEPTION_SET_ID
FROM apps.MTL_MATERIAL_TRANSACTIONS MMT, apps.mtl_parameters mp
WHERE mmt.organization_id = mp.organization_id
AND mmt.transaction_type_id = 18
AND mmt.organization_id IN (:OrgId)
AND mp.organization_id IN (:OrgId) -- Added by
AND mp.organization_id = mmt.organization_id -- Added by
AND TRUNC (MMT.transaction_date) BETWEEN :DateFrom
AND :DateTo),
safety_stock
AS
(SELECT DISTINCT
FIRST_VALUE (
mssc.safety_stock_quantity)
OVER (
PARTITION BY mssc.inventory_item_id, mssc.organization_id
ORDER BY mssc.effectivity_date DESC)
safety_stock_quantity,
mssc.inventory_item_id,
mssc.organization_id
FROM apps.mtl_safety_stocks mssc
WHERE mssc.organization_id IN (:OrgId)
AND mssc.effectivity_date <= TRUNC (SYSDATE))
SELECT (SELECT cat.SEGMENT2
FROM apps.mtl_item_categories_v cat
WHERE cat.organization_id = po.organization_id
AND cat.inventory_item_id = po.inventory_item_id
AND cat.category_set_id = 1100000041)
PRODUCT_BRANCH,
(SELECT cat.SEGMENT1
FROM apps.mtl_item_categories_v cat
WHERE cat.organization_id = po.organization_id
AND cat.inventory_item_id = po.inventory_item_id
AND cat.category_set_id = 1100000041)
Busines_Unit,
PART_NO,
NVL (
PO.model_string,
(SELECT DISTINCT
FIRST_VALUE (
ml.model_string)
OVER (PARTITION BY ol.INVENTORY_ITEM_ID
ORDER BY ol.creation_date DESC)
model_string
FROM apps.xxom_3lp_sym_ora_order_lines ml,
apps.oe_order_lines_all ol
WHERE ml.LINE_ID = ol.LINE_ID
AND ol.ship_from_org_id = po.organization_id
AND ol.INVENTORY_ITEM_ID = po.INVENTORY_ITEM_ID
AND TRIM (ml.model_string) IS NOT NULL))
MODEL_STRING,
ITEM_DESCRIPTION,
(SELECT segment1
FROM apps.mtl_item_categories_v cat
WHERE po.organization_id = cat.organization_id
AND po.inventory_item_id = cat.inventory_item_id
AND cat.category_set_id = 1100000021)
HIERARCHY,
po.UOM,
po.LOT_STATUS_CODE,
po.SUPPLIER_NUMBER,
po.SUPPLIER_NAME,
po.SUPPLIER_SITE,
po.PO_NUMBER,
po.PO_LINE_NUMBER,
po.PO_DATE,
po.PO_TIME,
po.need_by_date
REQUESTED_DATE,
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE,
po.LOCATOR,
po.ME_NUMBER,
po.FREIGHT_FORWARDER,
po.WAYBILL_AIRBILL_NUM,
po.SERIAL_NUMBER,
po.ROUTING,
po.SUBINVENTORY_CODE,
po.TRANSACTION_TYPE_NAME,
po.TRANSACTION_QUANTITY,
po.QUANTITY_RECEIVED,
po.CURRENCY_CODE,
po.RATE_DATE,
po.UNIT_PRICE_USD,
po.EXTENDED_PRICE_USD,
po.TRANSACTION_SOURCE_TYPE_NAME,
po.SOURCE,
po.TRANSACTION_DATE
PO_RECEIPT_DATE,
po.PO_RECEIPT_TIME,
po.LAST_UPDATE_DATE,
po.USER_NAME,
po.SO_NUMBER,
po.SO_TYPE,
po.LINE_NUMBER
SO_LINE,
po.ADMIN_NAME,
po.ADMIN_CODE,
po.BUYER_NAME,
po.ABC_CLASSIFICATION,
po.LEAD_TIME_SUM,
CASE
WHEN TRIM (UPPER (po.RESCHEDULE_RESP)) = 'V'
THEN
'NOT ON-TIME'
WHEN TRIM (UPPER (po.RESCHEDULE_RESP)) = 'R'
THEN
'ON-TIME'
WHEN ( (TRUNC (po.TRANSACTION_DATE) - TRUNC (po.need_by_date))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (po.need_by_date, 'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (TRUNC (po.need_by_date),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.need_by_date)
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.need_by_date)
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.need_by_date)
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
po.need_by_date))) <
-5
AND po.EARLY_SHIPMENT = 'YES'
THEN
'ON-TIME'
WHEN ( (TRUNC (po.TRANSACTION_DATE) - TRUNC (po.need_by_date))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (po.need_by_date, 'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (TRUNC (po.need_by_date),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.need_by_date)
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.need_by_date)
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.need_by_date)
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
po.need_by_date))) <
-5
THEN
'NOT ON-TIME'
WHEN TRUNC (po.TRANSACTION_DATE) <= TRUNC (po.need_by_date)
THEN
'ON-TIME'
WHEN TRUNC (po.TRANSACTION_DATE) > TRUNC (po.need_by_date)
THEN
'NOT ON-TIME'
END
RDSL,
CASE
WHEN TRIM (UPPER (po.RESCHEDULE_RESP)) = 'V'
THEN
'NOT ON-TIME'
WHEN TRIM (UPPER (po.RESCHEDULE_RESP)) = 'R'
THEN
'ON-TIME'
WHEN ( ( TRUNC (po.TRANSACTION_DATE)
- TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE),
'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)))) <
-5
AND po.EARLY_SHIPMENT = 'YES'
THEN
'ON-TIME'
WHEN ( ( TRUNC (po.TRANSACTION_DATE)
- TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE, po.PROMISED_DATE)))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE, po.PROMISED_DATE),
'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)))) <
-5
THEN
'NOT ON-TIME'
WHEN TRUNC (po.TRANSACTION_DATE) <=
TRUNC (NVL (po.ORIGINAL_PROMISE_DATE, po.PROMISED_DATE))
THEN
'ON-TIME'
WHEN TRUNC (po.TRANSACTION_DATE) >
TRUNC (NVL (po.ORIGINAL_PROMISE_DATE, po.PROMISED_DATE))
THEN
'NOT ON-TIME'
END
PDSL,
CASE
WHEN TRIM (UPPER (po.RESCHEDULE_RESP)) = 'V'
THEN
'RDSL/PDSL not on-time due to vendor reschedule'
WHEN TRIM (UPPER (po.RESCHEDULE_RESP)) = 'R'
OR ( po.EARLY_SHIPMENT = 'YES'
AND ( (( ( TRUNC (po.TRANSACTION_DATE)
- TRUNC (po.need_by_date))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (po.need_by_date, 'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (
TRUNC (po.need_by_date),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.need_by_date)
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (
po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.need_by_date)
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code =
po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.need_by_date)
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code =
po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
po.need_by_date))) <
-5)
OR (( ( TRUNC (po.TRANSACTION_DATE)
- TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE),
'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (
TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (
po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code =
po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code =
po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)))) <
-5)))
THEN
'RDSL/PDSL on-time. Rosemount accepted the reschdule'
WHEN ( (( ( TRUNC (po.TRANSACTION_DATE)
- TRUNC (po.need_by_date))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (po.need_by_date, 'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (TRUNC (po.need_by_date),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.need_by_date)
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.need_by_date)
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.need_by_date)
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
po.need_by_date))) <
-5)
OR (( ( TRUNC (po.TRANSACTION_DATE)
- TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE),
'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (
NVL (po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE))
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
NVL (
po.ORIGINAL_PROMISE_DATE,
po.PROMISED_DATE)))) <
-5))
THEN
'RDSL/PDSL too early by more than 5 days'
WHEN TRUNC (po.TRANSACTION_DATE) > TRUNC (po.need_by_date)
OR TRUNC (po.TRANSACTION_DATE) >
TRUNC (NVL (po.ORIGINAL_PROMISE_DATE, po.PROMISED_DATE))
THEN
'Late'
END
RDSL_PDSL_LATE_REASON,
po.RECEIPT_NUM,
po.RECEIPT_LINE_NUM,
po.LOT_NUMBER,
po.LOT_QTY,
po.PERIOD_NAME,
po.WEEK_NUM,
po.DRAWING_REV,
po.BOM_REV,
(SELECT cat.category_concat_segs
FROM apps.mtl_item_categories_v cat
WHERE cat.organization_id = po.organization_id
AND cat.inventory_item_id = po.inventory_item_id
AND cat.category_set_id = 1)
COMMODITY_CODE,
po.PLANNER_CODE,
po.PLANNER_NAME,
po.RESCHEDULE_RESP,
po.BUYER_NOTES,
po.EARLY_SHIPMENT,
po.ITEM_COST,
(SELECT safety_stock.safety_stock_quantity
FROM safety_stock
WHERE safety_stock.organization_id = po.organization_id
AND safety_stock.inventory_item_id = po.inventory_item_id)
SAFETY_STOCK,
CASE
WHEN TRUNC (po.TRANSACTION_DATE) IS NULL
OR TRUNC (po.PO_DATE) IS NULL
THEN
NULL
ELSE
(TRUNC (po.TRANSACTION_DATE) - TRUNC (po.PO_DATE))
- ( ( TRUNC (po.TRANSACTION_DATE, 'D')
- TRUNC (po.PO_DATE, 'D'))
/ 7
* 2)
- (CASE
WHEN TO_CHAR (TRUNC (po.PO_DATE),
'DY',
'nls_date_language=english') =
'SUN'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.PO_DATE)
THEN
1
ELSE
0
END)
- (CASE
WHEN TO_CHAR (po.TRANSACTION_DATE,
'DY',
'nls_date_language=english') =
'SAT'
AND TRUNC (po.TRANSACTION_DATE) <>
TRUNC (po.PO_DATE)
THEN
1
ELSE
0
END)
- (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.PO_DATE)
AND TRUNC (
po.TRANSACTION_DATE))
+ (SELECT COUNT (BCE.exception_date) EXC
FROM apps.bom_calendar_exceptions BCE
WHERE BCE.calendar_code = po.CALENDAR_CODE
AND BCE.EXCEPTION_SET_ID =
po.CALENDAR_EXCEPTION_SET_ID
AND TRUNC (BCE.exception_date) BETWEEN TRUNC (
po.TRANSACTION_DATE)
AND TRUNC (
po.PO_DATE))
END
PO_TO_DOCK,
po.subinventory,
CASE
WHEN COALESCE (po.ABC_CLASSIFICATION, 'D') <> 'D'
AND ( po.subinventory IS NULL
OR po.subinventory NOT IN ('SPECIALS', 'OSP_FG'))
THEN
'ABC'
ELSE
'D'
END
STOCK_TYPE,
po.CALENDAR_CODE,
po.CALENDAR_EXCEPTION_SET_ID,
po.COMMENTS,
po.COO,
(SELECT t.NAME
FROM APPS.ap_terms t
WHERE po.TERMS_ID = t.TERM_ID)
payment_term,
po.receiver,
PO.RT_ORGANIZATION_CODE,
PO.FROZEN_MATERIAL_COST,
PO.POL_UNIT_PRICE
FROM ( SELECT msi.inventory_item_id,
msi.organization_id,
MSI.segment1
PART_NO,
MSI.DESCRIPTION
ITEM_DESCRIPTION,
MSI.primary_uom_code
UOM,
MSI.inventory_item_status_code
LOT_STATUS_CODE,
pv.segment1
SUPPLIER_NUMBER,
pv.vendor_name
SUPPLIER_NAME,
pvs.VENDOR_SITE_CODE
SUPPLIER_SITE,
ph.segment1
PO_NUMBER,
pl.line_num
PO_LINE_NUMBER,
TRUNC (ph.creation_date)
PO_DATE,
TO_CHAR (ph.creation_date, 'hh24:mi:ss')
PO_TIME,
TRUNC (pll.need_by_date)
need_by_date,
TRUNC (
(SELECT DISTINCT
FIRST_VALUE (plla.promised_date)
OVER (ORDER BY revision_num ASC)
AS original_promise_date
FROM apps.po_line_locations_archive_all plla
WHERE plla.line_location_id = pll.line_location_id
AND plla.promised_date IS NOT NULL))
ORIGINAL_PROMISE_DATE,
TRUNC (pll.PROMISED_DATE)
PROMISED_DATE,
mil.concatenated_segments
LOCATOR,
rcvh.attribute1
ME_NUMBER,
rcvh.Freight_Carrier_Code
FREIGHT_FORWARDER,
rcvh.WAYBILL_AIRBILL_NUM,
RTRIM (
XMLAGG (XMLELEMENT (E, MUT.SERIAL_NUMBER, ',').EXTRACT (
'//text()') ORDER BY MUT.SERIAL_NUMBER).GetClobVal (),
',')
SERIAL_NUMBER,
rrh.ROUTING_NAME
ROUTING,
mmt.SUBINVENTORY_CODE,
MTT.TRANSACTION_TYPE_NAME,
pll.QUANTITY,
per.PERIOD_NAME,
year_week.PERIOD_YEAR || year_week.PERIOD_NUM
WEEK_NUM,
pl.attribute1
DRAWING_REV,
pl.attribute4
BOM_REV,
MSI.PLANNER_CODE,
(SELECT description
FROM apps.MTL_PLANNERS p
WHERE p.planner_code = MSI.planner_code
AND p.organization_id = MSI.organization_id)
PLANNER_NAME,
pll.attribute2
RESCHEDULE_RESP,
pll.attribute3
BUYER_NOTES,
pll.attribute1
EARLY_SHIPMENT,
cic.ITEM_COST,
pod.destination_subinventory
subinventory,
LIN.model_string,
mmt.CALENDAR_CODE,
mmt.CALENDAR_EXCEPTION_SET_ID,
ph.COMMENTS,
MAX (
COALESCE (
m.c_attribute10,
MUT.attribute2,
(SELECT cat.SEGMENT10
FROM apps.mtl_item_categories_v cat
WHERE cat.organization_id = msi.organization_id
AND cat.inventory_item_id =
msi.inventory_item_id
AND cat.category_set_name =
'EMR COUNTRY OF ORIGIN')))
AS COO,
ph.TERMS_ID,
MAX (
(SELECT ppf.full_name
FROM apps.per_all_people_f ppf
WHERE ppf.person_id = rt.employee_id
AND ppf.effective_start_date <= rt.creation_date
AND ppf.effective_end_date >= rt.creation_date))
receiver,
ROUND (cic.MATERIAL_COST, 5)
FROZEN_MATERIAL_COST,
(SELECT MTLP.ORGANIZATION_CODE
FROM apps.mtl_parameters MTLP
WHERE 1 = 1 AND MTLP.ORGANIZATION_ID = rt.ORGANIZATION_ID)
RT_ORGANIZATION_CODE,
pl.UNIT_PRICE
POL_UNIT_PRICE
FROM MMT
INNER JOIN APPS.mtl_system_items_b MSI
ON MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
INNER JOIN APPS.MTL_TRANSACTION_TYPES MTT
ON MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
INNER JOIN apps.rcv_transactions rt
ON rt.transaction_id = MMT.RCV_TRANSACTION_ID
AND rt.ORGANIZATION_ID = MMT.ORGANIZATION_ID -- Added by
INNER JOIN apps.po_lines_all pl
ON rt.po_line_id = pl.po_line_id
INNER JOIN apps.po_headers_all ph
ON ph.po_header_id = pl.po_header_id
LEFT JOIN apps.po_line_locations_all pll
ON pll.po_header_id = rt.po_header_id
AND pll.po_line_id = rt.po_line_id
AND Pll.LINE_LOCATION_ID = rt.PO_LINE_LOCATION_ID
LEFT JOIN apps.po_distributions_all pod
ON pll.line_location_id = pod.line_location_id
LEFT JOIN apps.oe_drop_ship_sources ods
ON ph.po_header_id = ods.po_header_id
AND pl.po_line_id = ods.po_line_id
AND pll.line_location_id = ods.line_location_id
LEFT JOIN apps.oe_order_lines_all ool
ON ool.line_id = ods.line_id AND ool.cancelled_flag <> 'Y'
LEFT JOIN apps.oe_order_headers_all ooh
ON ooh.header_id = ool.header_id
LEFT JOIN apps.OE_TRANSACTION_TYPES_TL lt
ON ooh.order_type_id = lt.transaction_type_id
AND lt.LANGUAGE = ‘US’
LEFT JOIN apps.po_vendors pv ON ph.vendor_id = pv.vendor_id
LEFT JOIN apps.po_vendor_sites_all pvs ON ph.vendor_site_id = pvs.vendor_site_id
LEFT JOIN apps.ap_suppliers pv ON ph.vendor_id = pv.vendor_id --
LEFT JOIN apps.ap_supplier_sites_all pvs
ON ph.vendor_site_id = pvs.vendor_site_id -- commented by slk on 19-FEB-18
AND pv.vendor_id = pvs.vendor_id
LEFT JOIN APPS.po_buyers_all_v pba
ON pba.employee_id = msi.buyer_id
LEFT JOIN apps.fnd_user u ON u.user_id = MMT.last_updated_by
LEFT JOIN apps.ap_terms t ON t.term_id = ph.terms_id
LEFT JOIN apps.mtl_item_locations_kfv mil
ON mil.organization_id = MMT.organization_id
AND mil.inventory_location_id = MMT.LOCATOR_ID
LEFT JOIN apps.mtl_unit_transactions MUT
ON MUT.TRANSACTION_ID = MMT.TRANSACTION_ID
LEFT JOIN apps.mtl_txn_source_types mtst
ON mtst.transaction_source_type_id =
mmt.TRANSACTION_SOURCE_TYPE_ID
LEFT JOIN apps.rcv_shipment_headers rcvh
ON rt.shipment_header_id = rcvh.shipment_header_id
LEFT JOIN apps.rcv_shipment_lines rcvl
ON rt.shipment_header_id = rcvl.shipment_header_id
AND rt.shipment_line_id = rcvl.shipment_line_id
LEFT JOIN apps.RCV_LOT_TRANSACTIONS rlt
ON rlt.SHIPMENT_LINE_ID = rcvl.SHIPMENT_LINE_ID
AND rt.transaction_id = rlt.transaction_id
LEFT JOIN apps.MTL_TRANSACTION_LOT_NUMBERS m
ON m.transaction_id = MMT.transaction_id
LEFT JOIN apps.gl_period_statuses per
ON per.ledger_id = 1
AND per.application_id = 101
AND TRUNC (mmt.transaction_date) BETWEEN per.start_date
AND per.end_date
LEFT JOIN apps.gl_period_statuses year_week
ON year_week.ledger_id = 2
AND year_week.application_id = 275
AND TRUNC (mmt.transaction_date) BETWEEN year_week.start_date
AND year_week.end_date
LEFT JOIN APPS.RCV_ROUTING_HEADERS rrh
ON rrh.routing_header_id = pll.receiving_routing_id
LEFT JOIN apps.CST_ITEM_COSTS cic
ON cic.organization_id = mmt.organization_id
AND cic.inventory_item_id = mmt.inventory_item_id
AND cic.cost_type_id = 1
LEFT JOIN apps.xxom_3lp_sym_ora_order_hdr hdr
ON ooh.header_id = hdr.header_id
LEFT JOIN apps.xxom_3lp_sym_ora_order_lines lin
ON ool.header_id = lin.header_id
AND ool.line_id = lin.line_id
GROUP BY MSI.segment1,
MSI.DESCRIPTION,
pv.segment1,
pv.vendor_name,
pvs.VENDOR_SITE_CODE,
ph.segment1,
pl.line_num,
ph.creation_date,
TRUNC (pll.need_by_date),
TRUNC (pll.PROMISED_DATE),
mil.concatenated_segments,
rcvh.attribute1,
rcvh.Freight_Carrier_Code,
rcvh.WAYBILL_AIRBILL_NUM,
rrh.ROUTING_NAME,
mmt.SUBINVENTORY_CODE,
MTT.TRANSACTION_TYPE_NAME,
pll.QUANTITY,
pll.QUANTITY_RECEIVED,
MMT.TRANSACTION_SOURCE_ID,
MMT.INVENTORY_ITEM_ID,
MMT.organization_id,
mtst.transaction_source_type_name,
MMT.TRANSACTION_DATE,
MMT.LAST_UPDATE_DATE,
u.USER_NAME,
msi.inventory_item_id,
msi.organization_id,
ooh.order_number,
ooh.header_id,
lt.NAME,
ool.LINE_NUMBER,
ool.SHIPMENT_NUMBER,
pl.UNIT_PRICE,
MSI.primary_uom_code,
ph.CURRENCY_CODE,
ph.RATE_DATE,
pba.full_name,
MSI.inventory_item_status_code,
rcvh.RECEIPT_NUM,
rcvl.line_num,
per.PERIOD_NAME,
year_week.PERIOD_YEAR || year_week.PERIOD_NUM,
pl.attribute1,
pl.attribute4,
MSI.planner_code,
pll.attribute2,
pll.attribute3,
pll.attribute1,
cic.ITEM_COST,
hdr.order_admin,
pll.line_location_id,
pod.destination_subinventory,
NVL (msi.FULL_LEAD_TIME, 0)
+ NVL (msi.POSTPROCESSING_LEAD_TIME, 0),
LIN.model_string,
mmt.CALENDAR_CODE,
mmt.CALENDAR_EXCEPTION_SET_ID,
ph.COMMENTS,
ph.TERMS_ID,
cic.MATERIAL_COST,
rt.ORGANIZATION_ID) po