Hi,
I have the following query, and its giving the "ORA-01417: a table may be at most outer joined to one other table" error.
SELECT n_current_cyclesk AS cycle_wid
, semtk.product_wid AS product_wid
, 0 AS ship_org_wid
, semtk.dest_org_wid AS dest_org_wid
, NVL(wpod.prod_org_wid,0) AS product_org_wid
, semtk.bucket_mnth_start_dt AS bucket_month_start_date
, semtk.bucket_qtr_start_dt AS bucket_qtr_start_date
, d_quarter_key AS cycle_quarter_key
, d_current_cycle_qtr_st_dt AS cycle_qtr_start_date
, semtk.dest_ou_wid AS dest_op_unit_wid
, semtk.dest_ou_key AS dest_op_unit_key
, semtk.dest_region_wid AS dest_region_wid
, semtk.dest_region_key AS dest_region_key
, semtk.prod_number AS prod_number
, 0 AS ship_org_key
, semtk.dest_org_key AS dest_org_key
, 'Shipped Intransit' AS metric_name
, (semtk.primary_quantity * -1) AS quantity
, scl.functional_cost AS scp_functional_cost
, scl.usd_cost AS scp_usd_cost
, scl.functional_currency AS scp_func_currency_cd
, 'Y' AS incl_ou_flg
, 'Y' AS incl_region_flg
, 'N' AS beginning_inv_flg
, 'N' AS actual_receipt_flg
, 'N' AS incoming_flg
, 'Y' AS confirmed_flg
, 'Y' AS unconstrained_flg
, 'Oracle Apps' AS source_system_name
, idl.run_date AS etl_update_date
, idl.run_date AS etl_load_date
FROM scp_emea_material_tran_kit semtk
, v_org_ou_region voor
, w_sub_org_d wsod
, product_dim pd
, edw_pres.w_prod_org_d wpod
, edw_pres.scp_cost_lkp scl
, (select ftv.territory_short_name country
, loc.postal_code
, casu.site_use_id
from hz_cust_acct_sites_all cas
, hz_party_sites hps
, hz_cust_site_uses_all casu
, hz_locations loc
, fnd_territories_vl ftv
where cas.party_site_id = hps.party_site_id
and casu.cust_acct_site_id = cas.cust_acct_site_id
and loc.location_id = hps.location_id
and ftv.territory_code = loc.country) cp
, scp_org_ship_to_cust_lt_kit ship
, interfaces_dates_lkp idl
WHERE (semtk.transaction_date + CASE
WHEN ship.ship_from_org_wid IS NULL
AND ship.ship_method IS NULL
AND ship.ship_to_country_idnt IS NULL
AND ship.ship_to_postal_code IS NULL
THEN 5
ELSE ship.lead_time
END) >= d_current_cycle_wk_st_dt
AND semtk.transaction_date < d_current_cycle_wk_st_dt
AND semtk.dest_org_wid = voor.org_wid
AND voor.dc_mf_flag = 'DC'
AND semtk.prod_number = wpod.product_number (+)
AND semtk.dest_org_key = wpod.organization_id (+)
AND semtk.transaction_type_id = 33
AND scl.ppv_flag (+) = 'N'
AND scl.cost_subelement(+) = 'Total Cost'
AND scl.product_wid(+) = semtk.product_wid
AND scl.org_wid(+) = semtk.dest_org_wid
AND scl.quarter_key(+) = d_quarter_key
AND idl.interface_id = 'EMEA'
AND semtk.so_ship_to_org = cp.site_use_id
AND semtk.dest_org_wid = ship.ship_from_org_wid(+)
AND NVL(semtk.so_shipping_method_code, ship.ship_method(+)) = ship.ship_method(+)
AND cp.country = ship.ship_to_country_idnt(+)
AND NVL(cp.postal_code, ship.ship_to_postal_code(+)) = ship.ship_to_postal_code(+)
AND NOT ((semtk.so_shipping_method_code IS NULL OR cp.postal_code IS NULL) AND NVL(ship.default_flag,'N') = 'N');
If I comment the 3rd last and the 2nd last line of the query, the problem gets resolved. But I need the joins in these lines for getting the result.
How do I modify the query so that the above ORA error does not arise.
Thanks in advance,