select
nvl(gl.short_name, gl.name) Ledger,
haou2.name Operating_Unit,
:p_sourcing_rule_type Sourcing_Rule_Type,
mp_to_org.organization_code To_Org,
-- Revision for version 1.8
-- mp_src_org.organization_code Src_Org,
mp_src_org.organization_code From_Org_or_Supplier,
mas.assignment_set_name Assignment_Set,
msiv.concatenated_segments Item_Number,
msiv.description Item_Description,
-- Revision for version 1.11
muomv.uom_code UOM_Code,
-- Revision for version 1.9
&category_columns
msr.sourcing_rule_name Sourcing_Rule,
msr.creation_date Creation_Date,
msro.effective_date Effective_Date,
msro.disable_date Disable_Date,
-- Revision for version 1.9
fcl.meaning Item_Type,
-- Revision for version 1.11
misv.inventory_item_status_code Item_Status,
ml.meaning Make_Buy_Code,
ml2.meaning Based_on_Rollup
from mrp_sr_source_org msso,
mrp_sr_receipt_org msro,
mrp_sourcing_rules msr,
mrp_sr_assignments msa,
mrp_assignment_sets mas,
mtl_system_items_vl msiv,
-- Revision for version 1.11
mtl_item_status_vl misv,
mtl_units_of_measure_vl muomv,
-- End revision for version 1.11
cst_item_costs cic_to_org,
mtl_parameters mp_to_org,
mtl_parameters mp_src_org,
mfg_lookups ml,
mfg_lookups ml2,
-- Revision for version 1.9
fnd_common_lookups fcl,
hr_organization_information hoi,
hr_all_organization_units_vl haou, -- inv_organization_id
hr_all_organization_units_vl haou2, -- operating unit
gl_ledgers gl
-- ====================================
-- Sourcing_Rule Joins
-- ====================================
where msso.sr_receipt_id = msro.sr_receipt_id
and msr.sourcing_rule_id = msro.sourcing_rule_id
and msa.sourcing_rule_id = msr.sourcing_rule_id
and msa.assignment_set_id = mas.assignment_set_id
and msiv.organization_id = msa.organization_id
and msiv.inventory_item_id = msa.inventory_item_id
and msiv.inventory_item_status_code <> 'Inactive'
-- Revision for version 1.11
and msiv.primary_uom_code = muomv.uom_code
and msiv.inventory_item_status_code = misv.inventory_item_status_code
-- End revision for version 1.11
-- Revision for version 1.9
and (msr.organization_id is null or msr.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id))
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1 -- p_item_number, p_to_org_code, p_operating_unit, p_ledger
and decode(xxen_util.lookup_code(:p_sourcing_rule_type,'MTL_SOURCE_TYPES',700),'2',-999,msso.source_organization_id) = msso.source_organization_id
-- End revision for version 1.9
-- ====================================
-- Lookup Code Joins
-- ====================================
and msiv.planning_make_buy_code = ml.lookup_code
and ml.lookup_type = 'MTL_PLANNING_MAKE_BUY'
and ml2.lookup_code = cic_to_org.based_on_rollup_flag
and ml2.lookup_type = 'SYS_YES_NO'
-- Revision for version 1.9
and fcl.lookup_code (+) = msiv.item_type
and fcl.lookup_type (+) = 'ITEM_TYPE'
-- ====================================
-- Joins for to_org
-- ====================================
and msiv.organization_id = cic_to_org.organization_id
and msiv.inventory_item_id = cic_to_org.inventory_item_id
and cic_to_org.cost_type_id = mp_to_org.primary_cost_method
and msiv.organization_id = mp_to_org.organization_id
and mp_src_org.organization_id = msso.source_organization_id
-- ========================================================
-- Organization joins to the HR org model
-- ========================================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = mp_to_org.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id
-- Avoid selecting disabled inventory organizations
and sysdate < nvl(haou.date_to, sysdate + 1)
-- ====================================
-- Revision for version 1.8
-- Get the Vendor Sourcing_Rules
-- ====================================
union all
select
nvl(gl.short_name, gl.name) Ledger,
haou2.name Operating_Unit,
:p_sourcing_rule_type Sourcing_Rule_Type,
mp_to_org.organization_code To_Org,
pv.vendor_name From_Org_or_Supplier,
mas.assignment_set_name Assignment_Set,
msiv.concatenated_segments Item_Number,
msiv.description Item_Description,
-- Revision for version 1.11
muomv.uom_code UOM_Code,
-- Revision for version 1.9
&category_columns
msr.sourcing_rule_name Sourcing_Rule,
msr.creation_date Creation_Date,
msro.effective_date Effective_Date,
msro.disable_date Disable_Date,
-- Revision for version 1.9
fcl.meaning Item_Type,
-- Revision for version 1.11
misv.inventory_item_status_code Item_Status,
ml.meaning Make_Buy_Code,
ml2.meaning Based_on_Rollup
from mrp_sr_source_org msso,
mrp_sr_receipt_org msro,
mrp_sourcing_rules msr,
mrp_sr_assignments msa,
mrp_assignment_sets mas,
mtl_system_items_vl msiv,
-- Revision for version 1.11
mtl_item_status_vl misv,
mtl_units_of_measure_vl muomv,
-- End revision for version 1.11
cst_item_costs cic_to_org,
mtl_parameters mp_to_org,
po_vendors pv,
mfg_lookups ml,
mfg_lookups ml2,
-- Revision for version 1.9
fnd_common_lookups fcl,
hr_organization_information hoi,
hr_all_organization_units_vl haou, -- inv_organization_id
hr_all_organization_units_vl haou2, -- operating unit
gl_ledgers gl
-- ====================================
-- Sourcing_Rule Joins
-- ====================================
where msso.sr_receipt_id = msro.sr_receipt_id
and msr.sourcing_rule_id = msro.sourcing_rule_id
and msa.sourcing_rule_id = msr.sourcing_rule_id
and msa.assignment_set_id = mas.assignment_set_id
and msiv.organization_id = msa.organization_id
and msiv.inventory_item_id = msa.inventory_item_id
and msiv.inventory_item_status_code <> 'Inactive'
-- Revision for version 1.11
and msiv.primary_uom_code = muomv.uom_code
and msiv.inventory_item_status_code = misv.inventory_item_status_code
-- End revision for version 1.11
-- Revision for version 1.9
and (msr.organization_id is null or msr.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id))
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1 -- p_item_number, p_to_org_code, p_operating_unit, p_ledger
and decode(xxen_util.lookup_code(:p_sourcing_rule_type,'MTL_SOURCE_TYPES',700),'1',-999,-1000) = nvl(msso.source_organization_id, -1000)
-- End revision for version 1.9
-- ====================================
-- Lookup Code Joins
-- ====================================
and msiv.planning_make_buy_code = ml.lookup_code
and ml.lookup_type = 'MTL_PLANNING_MAKE_BUY'
and ml2.lookup_code = cic_to_org.based_on_rollup_flag
and ml2.lookup_type = 'SYS_YES_NO'
and fcl.lookup_code (+) = msiv.item_type
and fcl.lookup_type (+) = 'ITEM_TYPE'
-- End revision for version 1.9
-- ====================================
-- Joins for to_org
-- ====================================
and msiv.organization_id = cic_to_org.organization_id
and msiv.inventory_item_id = cic_to_org.inventory_item_id
and cic_to_org.cost_type_id = mp_to_org.primary_cost_method
and msiv.organization_id = mp_to_org.organization_id
-- ====================================
-- Vendor joins
-- ====================================
and pv.vendor_id = msso.vendor_id
and msso.source_organization_id is null
-- End revision for version 1.8
-- ========================================================
-- Organization joins to the HR org model
-- ========================================================
and hoi.org_information_context = 'Accounting Information'
and hoi.organization_id = mp_to_org.organization_id
and hoi.organization_id = haou.organization_id -- this gets the organization name
and haou2.organization_id = to_number(hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id = to_number(hoi.org_information1) -- get the ledger_id
-- Avoid selecting disabled inventory organizations
and sysdate < nvl(haou.date_to, sysdate + 1)
-- Order by Ledger, Operating_Unit, Sourcing_Rule_Type, To_Org, From_Org_or_Supplier, Assignment_Set and Item_Number
order by 1,2,3,4,5,6,7 |