Saled Orders Pegged to PO

SELECT /*+ NO_MERGE(MED) */


med.planner_code F01

,SUBSTR(med.category_name,INSTR(med.category_name,'.',1,2)+1,INSTR(med.category_name,'.')) F02

,SUBSTR(med.category_name,INSTR(med.category_name,'.',1,3)+1,INSTR(med.category_name,'.')) F03

,vendor.vend_name F04

,calc_date.organization_code F05

,SUBSTR(med.order_number,1,7) F06 ----PO Number

,pha.comments F07

,DECODE(ms.firm_planned_type,1,'Y','N') F08

,ms.need_by_date F09

,calc_date.calendar_date F10

,med.item_segments F11

,med.item_description F12

,med.quantity F13

,calc_date.full_lead_time F14

,SUBSTR(med.end_order_number,1,INSTR(med.end_order_number,'.')-1)

' '

SUBSTR(med.end_order_number,INSTR(med.end_order_number,'(')) f15,---Sales Order Number

party.customer f16

,party.NAME f17

,TRUNC(med.dmd_satisfied_date) f18

,party.TimeSlot f19

,party.PaidFlag f20

,party.SHIPPING_METHOD_CODE f21

FROM msc_exception_details_v med

,msc_supplies ms

,msc_plans mp

,PO_HEADERS_ALL pha

,msc_demands md

,( SELECT /*+ INDEX(TP MSC_TRADING_PARTNERS_U2) */ tp.organization_code, tp.partner_name, msi.plan_id, msi.inventory_item_id, msi.full_lead_time, msi.postprocessing_lead_time, msi.organization_id, c2.CALENDAR_DATE

FROM MSC_CALENDAR_DATES C,

MSC_CALENDAR_DATES C2,

MSC_TRADING_PARTNERS TP,

msc_system_items msi

WHERE TP.SR_INSTANCE_ID = 1

AND TP.PARTNER_TYPE = 3

AND C.CALENDAR_CODE = TP.CALENDAR_CODE

AND C.EXCEPTION_SET_ID = TP.CALENDAR_EXCEPTION_SET_ID

AND C.SR_INSTANCE_ID = TP.SR_INSTANCE_ID

AND C.CALENDAR_DATE = TRUNC(SYSDATE)

AND c2.exception_set_id = c.exception_set_id

AND c2.calendar_code = c.calendar_code

AND c2.seq_num = c.prior_seq_num + NVL(msi.full_lead_time,0)

AND msi.organization_id = tp.sr_tp_id) calc_date,

(SELECT b.partner_id, c.partner_site_id, b.partner_name

' - '

c.tp_site_code vend_name

FROM msc_trading_partners b, msc_trading_partner_sites c

WHERE b.partner_id = c.partner_id

AND b.partner_type = 1

) vendor,

(SELECT partyname.last_name

' '

partyname.first_name

' ('

phone

')' customer,

oel.line_id, HAO.NAME , OEL.ATTRIBUTE3 TimeSlot,

nvl2(OEL.ATTRIBUTE7,'Y','N') PaidFlag, OEL.SHIPPING_METHOD_CODE

FROM OE_ORDER_LINES_ALL OEL, HR_ALL_ORGANIZATION_UNITS HAO,

(SELECT shp_site_use.site_use_id invoice_to_org_id

,shipaddr.address1

,shipaddr.address2

,shipaddr.address3

,shipaddr.address4

,shipaddr.city

,shipaddr.province

,shipaddr.postal_code

,shp_party_site.party_id

,shipaddr.country

,DECODE( hp.person_last_name, NULL

,hp.party_name

,hp.person_last_name) last_name

,DECODE( hp.person_pre_name_adjunct, NULL

,DECODE ( hp.person_first_name, NULL

,''

,hp.person_first_name)

,lv.meaning

' '

hp.person_first_name ) first_name

,DECODE( cp.phone_area_code,NULL

,''

,cp.phone_area_code

'-')



cp.phone_number



DECODE( cp.phone_extension,NULL

,''

,' x'

cp.phone_extension) phone

FROM hz_locations shipaddr

, hz_party_sites shp_party_site

, hz_cust_acct_sites_all shp_acct_site

, hz_cust_site_uses_all shp_site_use

, hz_parties hp

, fnd_lookup_values_vl lv

, hz_contact_points cp

WHERE shp_site_use.site_use_code = 'BILL_TO'

AND shp_site_use.cust_acct_site_id = shp_acct_site.cust_acct_site_id

AND shp_acct_site.party_site_id = shp_party_site.party_site_id

AND shipaddr.location_id = shp_party_site.location_id

AND shp_party_site.party_id = hp.party_id

AND lv.lookup_type(+) = 'CONTACT_TITLE'

AND lv.lookup_code(+) = hp.person_pre_name_adjunct

AND cp.owner_table_name(+) = 'HZ_PARTIES'

AND cp.owner_table_id(+) = shp_party_site.party_id

AND cp.status(+) = 'A'

AND cp.primary_flag(+) = 'Y'

AND cp.contact_point_type(+) = 'PHONE') partyname

WHERE partyname.invoice_to_org_id = oel.invoice_to_org_id

AND HAO.ORGANIZATION_ID = OEL.SHIP_FROM_ORG_ID

and oel.line_id in(6851629,6851624,6851627)) party

WHERE med.plan_id = mp.plan_id

AND mp.compile_designator = 'HBC_DSO'

AND med.category_set_id = 5

AND med.exception_type= 15

AND med.plan_id = calc_date.plan_id

AND med.end_order_number LIKE '%COFI Standard Sales Order%'

AND med.order_type_id = 1

AND med.organization_id = calc_date.organization_id

AND med.inventory_item_id = calc_date.inventory_item_id

AND ms.plan_id = med.plan_id

AND ms.sr_instance_id = med.sr_instance_id

AND ms.transaction_id = med.transaction_id

---AND ms.need_by_date > calc_date.calendar_date

AND ms.supplier_id = vendor.partner_id

AND ms.supplier_site_id = vendor.partner_site_id

AND md.plan_id = med.plan_id

AND md.sr_instance_id = med.sr_instance_id

AND md.inventory_item_id = med.inventory_item_id

AND md.demand_id = med.demand_id

AND party.LINE_ID = md.sales_order_line_id

AND SUBSTR(med.order_number,1,7) = pha.segment1

--ORDER BY med.planner_code, DECODE(ms.firm_planned_type,1,'Y','N') DESC,

---vendor.vend_name,calc_date.organization_code,med.order_number,med.item_segments;