ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US 92EDCA82621F0FBDE053BB6B6358DEED MRP Assignment Set Y select mas.assignment_set_name value, mas.description from mrp_assignment_sets mas order by mas.assignment_set_name US 2D0A693569454826E0630100007FB419 Sourcing Rule Type Y select ml.lookup_code id, ml.meaning value, null description from mfg_lookups ml where ml.lookup_type = 'MRP_SOURCING_RULE_TYPE' and ml.lookup_code in (1,2) order by value US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US 07CA5F53B08F709DE0630100007F4B0E select -- Assignment Set mas.assignment_set_name, mas.description assignment_set_description, xxen_util.meaning(msav.assignment_type,'MRP_ASSIGNMENT_TYPE',700) assignment_type, msav.organization_code assignment_organization, msav.customer_name assignment_customer, msav.ship_to_address assignment_customer_site, mck.concatenated_segments assignment_category, mck.description assignment_category_descriptn, msiv.concatenated_segments assignment_item, msiv.description assignment_item_descriptn, muomv.unit_of_measure_tl primary_uom, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type, misv.inventory_item_status_code_tl item_status, xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_or_buy, xxen_util.meaning(decode(cic.based_on_rollup_flag,1,1),'SYS_YES_NO',700) based_on_rollup, &category_columns -- Sourcing Rule xxen_util.meaning(msr.sourcing_rule_type,'MRP_SOURCING_RULE_TYPE',700) sourcing_rule_type, msr.sourcing_rule_name sourcing_rule_name, msr.description sourcing_rule_description, xxen_util.meaning(nvl2(mp.organization_code,'2','1'),'INV_YES_NO',3) global_flag, mp.organization_code sourcing_rule_organization, xxen_util.meaning(decode(msr.planning_active,1,1),'SYS_YES_NO',700) planning_active, -- Receiving Org msrov.organization_code receipt_organization, msrov.customer receipt_customer, msrov.address receipt_customer_site, msrov.effective_date receipt_effective_date_from, msrov.disable_date receipt_effective_date_to, -- Sourcing Org xxen_util.meaning(mssov.source_type,'MRP_SOURCE_TYPE',700) source_type, mssov.source_organization_code source_organization, mssov.vendor_name source_supplier, mssov.vendor_site source_supplier_site, mssov.allocation_percent, mssov.rank, mssov.ship_method, mssov.intransit_time, -- Audit xxen_util.user_name(msr.created_by) rule_created_by, xxen_util.client_time(msr.creation_date) rule_creation_date, xxen_util.user_name(msr.last_updated_by) rule_last_updated_by, xxen_util.client_time(msr.last_update_date) rule_last_update_date, xxen_util.user_name(mssov.created_by) source_created_by, xxen_util.client_time(mssov.creation_date) source_creation_date, xxen_util.user_name(mssov.last_updated_by) source_last_updated_by, xxen_util.client_time(mssov.last_update_date) source_last_update_date, xxen_util.user_name(msav.created_by) assignment_created_by, xxen_util.client_time(msav.creation_date) assignment_creation_date, xxen_util.user_name(msav.last_updated_by) assignment_last_updated_by, xxen_util.client_time(msav.last_update_date) assignment_last_update_date from mrp_sourcing_rules msr, mtl_parameters mp, mrp_sr_receipt_org_v msrov, mrp_sr_source_org_v mssov, ( select msav.*, (select mp.primary_cost_method from mtl_parameters mp where msav.organization_id=mp.organization_id) primary_cost_method from ( select ( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=nvl(msav.organization_id,fnd_profile.value('MFG_ORGANIZATION_ID')) or nvl(msav.organization_id,fnd_profile.value('MFG_ORGANIZATION_ID')) is null and mp.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) ) organization_id_, msav.* from mrp_sr_assignments_v msav ) msav where msav.assignment_type in (1,2,4,5) or msav.organization_id is not null or msav.assignment_type in (3,6) and msav.organization_id is null and (msav.organization_id_,msav.inventory_item_id) in (select msib.organization_id, msib.inventory_item_id from mtl_system_items_b msib) ) msav, mrp_assignment_sets mas, mtl_system_items_vl msiv, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, cst_item_costs cic, mtl_categories_kfv mck where 1=1 and msr.organization_id=mp.organization_id(+) and msr.sourcing_rule_id=msrov.sourcing_rule_id(+) and msrov.sr_receipt_id=mssov.sr_receipt_id(+) and msr.sourcing_rule_id=msav.sourcing_rule_id(+) and msav.assignment_set_id=mas.assignment_set_id(+) and msav.organization_id_=msiv.organization_id(+) and msav.inventory_item_id=msiv.inventory_item_id(+) and msiv.primary_uom_code=muomv.uom_code(+) and msiv.inventory_item_status_code=misv.inventory_item_status_code(+) and msav.inventory_item_id=cic.inventory_item_id(+) and msav.organization_id=cic.organization_id(+) and msav.primary_cost_method=cic.cost_type_id(+) and msav.category_id=mck.category_id(+) order by mas.assignment_set_name, msav.assignment_type, msav.organization_code, mck.concatenated_segments, msiv.concatenated_segments, msav.customer_name, msav.ship_to_address, msr.sourcing_rule_type desc, mp.organization_code nulls first, msr.sourcing_rule_name, msrov.organization_code, mssov.allocation_percent desc, mssov.rank Y US MRP Sourcing Rules and Bills of Distribution Sourcing rules for all or a selected assignment set, along with with the item's make / buy flag and based on rollup flag for costing. You can either choose Organization or Vendor (Supplier) sourcing rules or get all sourcing rules by not selecting a sourcing rule type. Enginatics &category_columns 1=1 1 10 1=1 mas.assignment_set_name=:p_assignment_set LOV MRP Assignment Set 92EDCA82621F0FBDE053BB6B6358DEED Y select mas.assignment_set_name value, mas.description from mrp_assignment_sets mas order by mas.assignment_set_name US Assignment Set 2 20 1=1 msav.assignment_type=:p_assignment_type LOV custom Y select ml.lookup_code id, ml.meaning value, null description from mfg_lookups ml where ml.lookup_type = 'MRP_ASSIGNMENT_TYPE' order by value US Assignment Type 3 30 1=1 msav.organization_id=:p_ass_org_id LOV custom Y select ood.organization_id id, ood.organization_code value, ood.organization_name description from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate and (:$flex$.assignment_type is null or :$flex$.assignment_type in (4,5,6) ) order by value US Assignment Organization 4 40 1=1 msav.customer_id=:p_ass_cust_id LOV custom Y Y select hca.cust_account_id id, hp.party_name value, hca.account_number description from hz_parties hp, hz_cust_accounts hca where hca.party_id = hp.party_id and (:$flex$.assignment_type is null or :$flex$.assignment_type in (4,5,6) ) and :$flex$.assignment_organization is null and exists (select null from hz_cust_site_uses_all hcsua, hz_cust_acct_sites_all hcasa where hcasa.cust_acct_site_id = hcsua.cust_acct_site_id and hcsua.site_use_code in ('SHIP_TO','BILL_TO') and hcsua.status = 'A' and hcasa.cust_account_id = hca.cust_account_id ) order by value US Assignment Customer 5 50 1=1 msiv.concatenated_segments=:p_ass_item LOV custom Y Y select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id= nvl( :$flex$.organization, (select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where (mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or (fnd_profile.value('MFG_ORGANIZATION_ID') is null and mp.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 msiv.bom_item_type in (1,2,4) and (:$flex$.assignment_type is null or :$flex$.assignment_type in (3,6) ) order by value US Assignment Item Number 6 60 1=1 mck.concatenated_segments=:p_ass_cat LOV custom Y select mcv.category_concat_segs value, mcv.description description from mtl_category_sets mcs, mtl_categories_v mcv where mcs.structure_id = mcv.structure_id and mcs.category_set_id = to_number(fnd_profile.value('MRP_SRA_CATEGORY_SET')) and (nvl(mcv.disable_date,sysdate+1) > sysdate) and (:$flex$.assignment_type is null or :$flex$.assignment_type in (2,5) ) order by value US Assignment Category 7 70 1=1 msr.sourcing_rule_type=:p_src_rule_type LOV Sourcing Rule Type 2D0A693569454826E0630100007FB419 Y select ml.lookup_code id, ml.meaning value, null description from mfg_lookups ml where ml.lookup_type = 'MRP_SOURCING_RULE_TYPE' and ml.lookup_code in (1,2) order by value US Sourcing Rule Type 8 80 1=1 msr.sourcing_rule_name=:sourcing_rule_name LOV custom Y select msr.sourcing_rule_name value, msr.description from mrp_sourcing_rules msr where 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) order by msr.sourcing_rule_name US Sourcing Rule Name 9 90 1=1 msr.organization_id is null LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Global Sourcing Rules 10 1=1 msr.organization_id is not null N US Global Sourcing Rules 11 100 1=1 msr.organization_id=:p_rule_org LOV custom Y select ood.organization_id id, ood.organization_code value, ood.organization_name description from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate order by value US Sourcing Rule Organization 12 110 1=1 msrov.receipt_organization_id=:receipt_organization_id LOV custom Y select ood.organization_id id, ood.organization_code value, ood.organization_name description from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate order by value US Receipt Organization 13 120 1=1 trunc(sysdate) between msrov.effective_date and nvl(msrov.disable_date,sysdate) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Active Receipt Orgs Only 14 130 1=1 mssov.source_type=:p_src_type LOV custom Y select ml.lookup_code id, ml.meaning value, null description from mfg_lookups ml where ml.lookup_type = 'MRP_SOURCE_TYPE' and lookup_code in (1,2,3) order by value US Source Type 15 135 1=1 mssov.source_organization_id=:p_src_org LOV custom Y select ood.organization_id id, ood.organization_code value, ood.organization_name description from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate and nvl(:$flex$.Source_Type,1) in (1,2) order by value US Source Organization 16 137 1=1 mssov.vendor_name=:p_src_vend LOV custom Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where nvl(:$flex$.Source_Type,3) = 3 order by aps.vendor_name, aps.vendor_id US Source Supplier 17 138 1=1 mssov.vendor_site=:p_src_vend_site LOV custom Y select distinct assa.vendor_site_code value, assa.address_line1||', '||assa.city||', '||nvl(ftv.territory_short_name,assa.country) description from ap_suppliers aps, ap_supplier_sites_all assa, fnd_territories_vl ftv where xxen_util.contains(:$flex$.source_supplier,aps.vendor_name)='Y' and aps.vendor_id=assa.vendor_id and assa.country=ftv.territory_code(+) order by assa.vendor_site_code US Source Supplier Site 18 140 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=1)) US Category Set 1 19 150 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=2)) US Category Set 2 :$flex$.Source_Type Source Type Source Organization :$flex$.Source_Type Source Type Source Supplier :$flex$.assignment_organization Assignment Organization Assignment Customer :$flex$.assignment_type Assignment Type Assignment Category :$flex$.assignment_type Assignment Type Assignment Customer :$flex$.assignment_type Assignment Type Assignment Item Number :$flex$.assignment_type Assignment Type Assignment Organization :$flex$.source_supplier Source Supplier Source Supplier Site Sourcing Rule Assignments Y Y Generates a report of the sourcing rules without the assignments SYSADMIN ALLOCATION_PERCENT 7 ASSIGNMENT_CATEGORY 5 8 ASSIGNMENT_CATEGORY_DESCRIPTN 22 ASSIGNMENT_CREATED_BY 23 ASSIGNMENT_CREATION_DATE 5 ASSIGNMENT_CUSTOMER 4 6 ASSIGNMENT_CUSTOMER_SITE 9 ASSIGNMENT_ITEM 6 10 ASSIGNMENT_ITEM_DESCRIPTN 24 ASSIGNMENT_LAST_UPDATED_BY 25 ASSIGNMENT_LAST_UPDATE_DATE 4 ASSIGNMENT_ORGANIZATION 3 2 ASSIGNMENT_SET_DESCRIPTION 1 ASSIGNMENT_SET_NAME 1 3 ASSIGNMENT_TYPE 2 15 BASED_ON_ROLLUP Class Commodity Family 19 GLOBAL_FLAG INTRANSIT_TIME 13 ITEM_STATUS Inv.Items Description Item Category 14 MAKE_OR_BUY 21 PLANNING_ACTIVE 11 PRIMARY_UOM Purchasing Description RANK RECEIPT_CUSTOMER RECEIPT_CUSTOMER_SITE RECEIPT_EFFECTIVE_DATE_FROM RECEIPT_EFFECTIVE_DATE_TO RECEIPT_ORGANIZATION -9.0011 RECEIPT_ORG_END_DATE_ACTIVE -9.001 RECEIPT_ORG_START_DATE_ACTIVE 26 RULE_CREATED_BY 27 RULE_CREATION_DATE 28 RULE_LAST_UPDATED_BY 29 RULE_LAST_UPDATE_DATE SHIP_METHOD SOURCE_CREATED_BY SOURCE_CREATION_DATE SOURCE_LAST_UPDATED_BY SOURCE_LAST_UPDATE_DATE SOURCE_ORGANIZATION SOURCE_SUPPLIER SOURCE_SUPPLIER_SITE SOURCE_TYPE 18 SOURCING_RULE_DESCRIPTION 17 SOURCING_RULE_NAME 8 20 SOURCING_RULE_ORGANIZATION 16 SOURCING_RULE_TYPE 7 12 USER_ITEM_TYPE S Site Sourcing Rules Y Generates a report of the sourcing rules without the assignments SYSADMIN 16 ALLOCATION_PERCENT 5 4 GLOBAL_FLAG 19 INTRANSIT_TIME 6 PLANNING_ACTIVE 17 RANK 4 8 RECEIPT_CUSTOMER 9 RECEIPT_CUSTOMER_SITE 10 RECEIPT_EFFECTIVE_DATE_FROM 11 RECEIPT_EFFECTIVE_DATE_TO 7 RECEIPT_ORGANIZATION 3 -9.0011 RECEIPT_ORG_END_DATE_ACTIVE -9.001 RECEIPT_ORG_START_DATE_ACTIVE 20 RULE_CREATED_BY 21 RULE_CREATION_DATE 22 RULE_LAST_UPDATED_BY 23 RULE_LAST_UPDATE_DATE 18 SHIP_METHOD 24 SOURCE_CREATED_BY 25 SOURCE_CREATION_DATE 26 SOURCE_LAST_UPDATED_BY 27 SOURCE_LAST_UPDATE_DATE 13 SOURCE_ORGANIZATION 14 SOURCE_SUPPLIER 15 SOURCE_SUPPLIER_SITE 12 SOURCE_TYPE 3 SOURCING_RULE_DESCRIPTION 2 SOURCING_RULE_NAME 2 5 SOURCING_RULE_ORGANIZATION 1 SOURCING_RULE_TYPE 1 S Site SYSADMIN Sourcing Rules