Hi All,
I’m trying to optimize the following query, but am getting the error -
This type of SQL statement is not supported.
, any ideas why? I can run this in TOAD fine and return results…
WITH POHDRS(POH_ID,PO_NUMBER,PO_SUPP,EXPEDITER,SUP_ID, SUPPLIER,PROJ_ID,AWARD_DATE,LAST_CONTACT_DATE)
AS
(
SELECT POH_ID,PO_NUMBER,PO_SUPP,EXPEDITER,SUP_ID, SUPPLIER,PROJ_ID,AWARD_DATE,LAST_CONTACT_DATE
*FROM EGG_SYS.M006_HDR *
WHERE proj_id = ‘H337777’
AND poh_id IN (‘32810’)
AND sup_id IN (‘17848’) AND expediter IN (‘Joe Blow’)
)
,
PODETAILS (poh_id,
description,
poli_pos, poli_sub_pos,ish_pos,ish_sub_pos,item_ship_id
,item,ish_master_indicator,ident,tag_number,
item_desc,
ship_qty,unit_code,origin,delv_dest,
incoterm,contr_date, contr_delv,contr_act,act_trf_arr_date,eta_fcst_date,mrr_date,
ros_date,ish_float,
hdr_comment,line_comments,
dp_id,insp_forecasted_date,osd_numbers,poli_deviation)
AS
(
SELECT
base_poh.poh_id
,EGG_SYS.get_nls_desc_rep (‘M_PO_HEADER_NLS’, ‘1’,‘DESCRIPTION’, base_poh.poh_id,‘POH_ID’)
, poli.poli_pos
,poli.poli_sub_pos, ish.item_ship_pos AS ish_pos, ish.item_ship_sub_pos AS ish_sub_pos, ish.item_ship_id,
poli.poli_pos || ‘.’ || poli.poli_sub_pos || ‘.’ || ish.item_ship_pos || ‘.’ || ish.item_ship_sub_pos AS item,
EGG_SYS.m006_utility.get_ish_master_indicator (ish.item_ship_id) AS ish_master_indicator,
ish.ident, EGG_SYS.m006_utility.get_tag_ident (mcc.ity_id,ish.ident,ish.tag_number) AS tag_number,
EGG_SYS.get_hatch_ident_desc (ish.ident, ish.proj_id,‘ISH’, ish.item_ship_id, ‘1’, ish.dp_id,‘S’),
ish.item_ship_qty AS ship_qty, qty_unit.unit_code, ish.fab_location AS origin, ish.rev_freight_value AS delv_dest,
frt.frt_code AS incoterm, ish.prom_contract_date AS contr_date,
------------------
mr.forecasted_date as contr_delv, mr.actual_date as contr_act,
------------------
ish.actual_traffic_arrival_date AS act_trf_arr_date,
EGG_SYS.m006_utility.get_forecasted_date (ish.item_ship_id) eta_fcst_date,
TRUNC (m_sys.m_pck_exped_workload.get_isd_actual_date (ish.item_ship_id,‘AOS’)) AS mrr_date,
*ish.req_site_date AS ros_date, *
ish.req_site_date - NVL (EGG_SYS.m006_utility.get_forecasted_date (ish.item_ship_id), ish.pred_traffic_arrival_date) AS ish_float,
EGG_SYS.m006_utility.get_hdr_comment (base_poh.poh_id, ‘A’) hdr_comment,
EGG_SYS.m006_utility.get_line_comment (ish.item_ship_id) AS line_comments,
ish.dp_id, m_sys.m_pck_exped_workload.get_isd_info (ish.item_ship_id,‘INSP’).forecasted_date AS insp_forecasted_date,
EGG_SYS.m006_utility.get_osd_numbers (ish.item_ship_id) AS osd_numbers,
EGG_SYS.hatch_utility.get_poli_attch_attr (‘DEVIATIONS’, ish.item_ship_id,‘ISH’) AS poli_deviation
**
FROM
m_sys.m_po_headers base_poh
INNER JOIN m_sys.m_po_line_items poli ON poli.poh_id = base_poh.poh_id
INNER JOIN m_sys.m_actual_polis apoli ON poli.poli_id = apoli.poli_id
INNER JOIN m_sys.m_item_ships ish ON poli.poli_id=ish.poli_id AND ish.proj_id = apoli.proj_id
INNER JOIN m_sys.m_units qty_unit ON qty_unit.unit_id = ish.qty_unit_id
INNER JOIN m_sys.m_idents mi ON mi.ident = ish.ident
INNER JOIN m_sys.m_commodity_codes mcc ON mi.commodity_id = mcc.commodity_id
INNER JOIN m_sys.m_reqs r ON r.r_supp = m_sys.m_pck_exped_workload.get_max_ish_r_suppl (poli.poli_id)
INNER JOIN m_sys.m_req_to_pos rp ON base_poh.poh_id = rp.base_poh_id AND r.r_id = rp.r_id
INNER JOIN m_sys.m_req_li_to_polis rltp ON rltp.r_id = rp.r_id AND apoli.poli_id = rltp.poli_id
INNER JOIN
(Select misd.forecasted_date, misd.actual_date,misd.item_ship_id from m_sys.m_rm_schedules mrs INNER JOIN m_sys.m_item_ship_dates misd
ON mrs.rms_id = misd.rms_id WHERE delv_date_ind=‘Y’) mr
ON mr.item_ship_id = ish.item_ship_id
INNER JOIN m_sys.m_po_to_base_pos ptbp ON ptbp.rp_id = rp.rp_id AND ptbp.poh_id = rltp.poh_id
LEFT JOIN m_sys.m_freight_terms frt ON frt.frt_id = ish.frt_id
WHERE base_poh.proj_id = ‘H337777’
AND ish.item_ship_qty > 0 AND rp.rli_id IS NULL
AND UPPER (NVL (ish.owl, ‘NON’)) NOT LIKE ‘EXCLUDE%’
)
SELECT PO_NUMBER,PO_SUPP,EXPEDITER,SUP_ID, SUPPLIER,PROJ_ID,AWARD_DATE,LAST_CONTACT_DATE,
PODETAILS.poh_id,PODETAILS.description, poli_pos, poli_sub_pos,ish_pos,ish_sub_pos,item_ship_id
,item,ish_master_indicator,ident,tag_number,PODETAILS.item_desc,
ship_qty,unit_code,origin,delv_dest,
incoterm,contr_date, contr_delv,contr_act,act_trf_arr_date,eta_fcst_date,mrr_date,
ros_date,ish_float,
hdr_comment,line_comments,
dp_id,insp_forecasted_date,osd_numbers,poli_deviation FROM POHDRS LEFT JOIN PODETAILS ON POHDRS.poh_id=PODETAILS.poh_id
WHERE (PODETAILS.eta_fcst_date BETWEEN ‘01-JAN-2013’ AND ‘31-AUG-2013’)
AND
(
(mrr_date IS NULL) OR
( (SYSDATE - (7 * (‘2’))) <=
CASE (‘N’)
WHEN ‘Y’ THEN mrr_date
ELSE (SYSDATE - (7 * (‘2’)))
END
)
**
)
AND
NVL (PODETAILS.ish_float, 0) <= ‘1000’
AND PODETAILS.ish_master_indicator = (CASE (‘N’) WHEN ‘N’ THEN ‘N’ ELSE ish_master_indicator END)
AND ( (‘N’ = ‘Y’ AND ish_float < 0) OR NVL (‘N’, ‘N’) != ‘Y’)
**