ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual US 0FCD251C2352892AE0636DFB09051082Y with sra_query as ( select -- assignment set mas.assignment_set_name assignment_set, mas.description assignment_set_description, -- assignment case msav.assignment_type when 1 then 'Instance' when 2 then 'Category' when 3 then 'Item-Instance' when 4 then 'Instance-Organization' when 5 then 'Category-Instance-Organization' when 6 then 'Item-Instance-Organization' when 8 then 'Category-Instance-Region' when 7 then 'Instance-Region' when 9 then 'Item-Instance-Region' end assignment_type, decode(msav.assignment_type,3,null,msc_get_name.org_code(msav.organization_id, msav.sr_instance_id)) organization, msav.organization_code, case when msav.assignment_type in (3,6,9) then msav.entity_name else null end item, case when msav.assignment_type in (2,5,8) then msav.entity_name else null end category, msav.description, msav.customer_name, hca.partner_number customer_number, hcsua.location customer_site, xxen_util.meaning(hcsua.tp_site_code,'SITE_USE_CODE',222) customer_site_use, msav.ship_to_address, hcsua.operating_unit_name customer_site_ou, msav.sourcing_rule_type_text sourcing_rule_type, msav.sourcing_rule_name, mp.organization_code sourcing_rule_org, msav.item_type_value_text condition, -- case when mr.region_id is null then null when mr.zone is not null then 'Zone' else 'Region' end zone_or_region, mr.zone, mr.country region_country, mr.state region_state, mr.city region_city, mr.postal_code_from region_postal_code_from, mr.postal_code_to region_postal_code_to, msav.region_code region_zone_description, -- attributes mas.attribute_category set_attribute_category, mas.attribute1 set_attribute1, mas.attribute2 set_attribute2, mas.attribute3 set_attribute3, mas.attribute4 set_attribute4, mas.attribute5 set_attribute5, mas.attribute6 set_attribute6, mas.attribute7 set_attribute7, mas.attribute8 set_attribute8, mas.attribute9 set_attribute9, mas.attribute10 set_attribute10, mas.attribute11 set_attribute11, mas.attribute12 set_attribute12, mas.attribute13 set_attribute13, mas.attribute14 set_attribute14, mas.attribute15 set_attribute15, -- msav.attribute_category assign_attribute_category, msav.attribute1 assign_attribute1, msav.attribute2 assign_attribute2, msav.attribute3 assign_attribute3, msav.attribute4 assign_attribute4, msav.attribute5 assign_attribute5, msav.attribute6 assign_attribute6, msav.attribute7 assign_attribute7, msav.attribute8 assign_attribute8, msav.attribute9 assign_attribute9, msav.attribute10 assign_attribute10, msav.attribute11 assign_attribute11, msav.attribute12 assign_attribute12, msav.attribute13 assign_attribute13, msav.attribute14 assign_attribute14, msav.attribute15 assign_attribute15, -- xxen_util.meaning(nvl(mas.collected_flag,2),'SYS_YES_NO',700) set_collected_flag, xxen_util.meaning(nvl(msav.collected_flag,2),'SYS_YES_NO',700) collected_flag, -- ids mas.sr_instance_id, mas.assignment_set_id, msav.assignment_id, msav.sourcing_rule_id, msav.assignment_type assignment_type_id, -- msav.last_updated_by, msav.last_update_date from msc_assignment_sets mas, msc_sr_assignments_v msav, msc_sourcing_rules msr, msc_trading_partners mp, msc_trading_partners hca, msc_trading_partner_sites hcsua, msc_regions mr where mas.sr_instance_id = :p_sr_instance_id and mas.assignment_set_id = msav.assignment_set_id and mas.sr_instance_id = msav.sr_assignment_instance_id and msav.sourcing_rule_id = msr.sourcing_rule_id (+) and msav.sr_assignment_instance_id = msr.sr_instance_id (+) and -- msr.sr_instance_id = mp.sr_instance_id (+) and msr.organization_id = mp.sr_tp_id (+) and mp.partner_type (+) = 3 and -- msav.sr_assignment_instance_id = hca.sr_instance_id (+) and msav.customer_id = hca.partner_id (+) and hca.partner_type (+) = 2 and msav.sr_assignment_instance_id = hcsua.sr_instance_id (+) and msav.ship_to_site_id = hcsua.partner_site_id (+) and hcsua.partner_type (+) = 2 and msav.sr_assignment_instance_id = mr.sr_instance_id (+) and msav.region_id = mr.region_id (+) ) -- -- Main Query Starts Here -- select /*+ push_pred(sra) */ null action_, null status_, null message_, null request_id_, null modified_columns_, null set_row_id, null ass_row_id, :p_upload_mode upload_mode, :p_sr_instance_code instance, -- assignment set sra.assignment_set, sra.assignment_set_description, -- assignment sra.assignment_type, sra.organization, sra.item, sra.category, sra.description, sra.customer_name, sra.customer_number, sra.customer_site, sra.customer_site_use, sra.customer_site_ou, sra.ship_to_address, -- sra.zone_or_region, sra.zone, sra.region_country, sra.region_state, sra.region_city, sra.region_postal_code_from, sra.region_postal_code_to, -- sra.sourcing_rule_type, sra.sourcing_rule_name, sra.sourcing_rule_org, sra.condition, -- attributes sra.set_attribute_category, sra.set_attribute1, sra.set_attribute2, sra.set_attribute3, sra.set_attribute4, sra.set_attribute5, sra.set_attribute6, sra.set_attribute7, sra.set_attribute8, sra.set_attribute9, sra.set_attribute10, sra.set_attribute11, sra.set_attribute12, sra.set_attribute13, sra.set_attribute14, sra.set_attribute15, -- sra.assign_attribute_category, sra.assign_attribute1, sra.assign_attribute2, sra.assign_attribute3, sra.assign_attribute4, sra.assign_attribute5, sra.assign_attribute6, sra.assign_attribute7, sra.assign_attribute8, sra.assign_attribute9, sra.assign_attribute10, sra.assign_attribute11, sra.assign_attribute12, sra.assign_attribute13, sra.assign_attribute14, sra.assign_attribute15, -- null delete_option, sra.collected_flag, -- ids sra.sr_instance_id, sra.assignment_set_id, sra.assignment_id, sra.sourcing_rule_id, sra.assignment_type_id, :p_master_org_id master_organization_id, to_number(null) upload_row from sra_query sra where :p_master_org_code is not null and 1=1 Convert result SQL from the legacy lexical style to the standard Error/Success/Order By style; add the hidden upload_row column; order the result by upload_row (the order rows were uploaded). Kept the sra_query WITH block (complex and reused in the result query). U A xxen_msc_upload.upload_sourcing_rule_assignmt xxen_msc_upload.validate_sourcing_rule_assign( p_sr_instance_id=>:$flex$.sr_instance_id, p_assignment_type=>:$column$.assignment_type, p_organization=> :$column$.organization, p_item=>:$column$.item, p_category=>:$column$.category, p_customer_name=>:$column$.customer_name, p_customer_number=>:$column$.customer_number, p_customer_site=>:$column$.customer_site, p_customer_site_use=>:$column$.customer_site_use, p_customer_site_ou=>:$column$.customer_site_ou, p_zone_or_region=>:$column$.zone_or_region, p_zone=>:$column$.zone, p_region_country=>:$column$.region_country, p_region_state=>:$column$.region_state, p_region_city=>:$column$.region_city, p_region_postal_code_from=>:$column$.region_postal_code_from, p_region_postal_code_to=>:$column$.region_postal_code_to, p_sourcing_rule_type=>:$column$.sourcing_rule_type, p_sourcing_rule_name=>:$column$.sourcing_rule_name, p_sourcing_rule_org=>:$column$.sourcing_rule_org ) US MSC Sourcing Rule Assignment Upload MSC Sourcing Rule Assignment Upload ============================= - Upload new Assignment Sets and Sourcing Rule Assignments - Update Existing Assignment Sets and Sourcing Rule Assignments Notes: If an Assignment Organization is specified, then this determines the selecatable items. If an Assignment Organization is not specified, then the Master Organization determines the selectable Items. The selectable Assignment Organizations is not restricted by the Master Organization. This is the same logic as used in the Sourcing Assignments form Enginatics Upload 1=1 :p_master_org_code :p_master_org_id :p_sr_instance_code :p_sr_instance_id 1 -50 :p_sr_instance_id Number select instance_id from msc_apps_instances where instance_code = :$flex$.planning_instance US sr_instance_id 2 -40 :p_master_org_id Number select mtp.sr_tp_id from msc_trading_partners mtp where mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_type = 3 and mtp.organization_code = :$flex$.Master_Organization_Code Y US Master Organization ID 3 -30 1=1 :p_category_set is not null Char select mcs.category_set_name from msc_category_sets mcs where mcs.sr_instance_id = :$flex$.sr_instance_id and mcs.category_set_id = to_number(fnd_profile.value('MSC_SRA_CATEGORY_SET')) Y US Category Set 4 5 :p_sr_instance_code LOV custom Y select instance_code value, null description from msc_apps_instances order by instance_code Y US Planning Instance 5 10 :p_master_org_code LOV custom Y select mtp.organization_code value, mtp.partner_name description from msc_trading_partners mtp where mtp.partner_type = 3 and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.sr_tp_id = mtp.master_organization order by mtp.organization_code select mtpm.organization_code from msc_trading_partners mtp, msc_trading_partners mtpm where mtp.sr_instance_id = mtpm.sr_instance_id and mtp.master_organization = mtpm.sr_tp_id and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.sr_tp_id = fnd_profile.value('MFG_ORGANIZATION_ID') and mtp.partner_type = 3 and mtpm.partner_type = 3 order by mtpm.organization_code Y US Master Organization Code 6 20 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 7 30 1=1 sra.assignment_set = :p_assign_set_name LOV custom Y select mas.assignment_set_name value, mas.description || decode(mas.collected_flag,1,' (Collected)',null) description from msc_assignment_sets mas where mas.sr_instance_id = :$flex$.sr_instance_id order by mas.assignment_set_name US Assignment Set 8 40 1=1 sra.assignment_type = :p_assign_type LOV custom Y select x.value, null description from ( select '1' id, 'Instance' value from dual union all select '2' id, 'Category' value from dual where :$flex$.category_set is not null and (select mcs.control_level from msc_category_sets mcs where mcs.category_set_name = :$flex$.category_set and mcs.sr_instance_id = :$flex$.sr_instance_id) != 2 union all select '3' id, 'Item-Instance' value from dual union all select '4' id, 'Instance-Organization' value from dual union all select '5' id, 'Category-Instance-Organization' value from dual where :$flex$.category_set is not null union all select '6' id, 'Item-Instance-Organization' value from dual union all select '7' id, 'Instance-Region' value from dual union all select '8' id, 'Category-Instance-Region' value from dual union all select '9' id, 'Item-Instance-Region' value from dual ) x order by x.value US Assignment Type 9 50 1=1 sra.sourcing_rule_type = :p_src_rule_type LOV custom Y select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type = 'MRP_SOURCING_RULE_TYPE' and ml.lookup_code in (1, 2) and (:$flex$.assignment_type is null or (:$flex$.assignment_type in ('Instance', 'Category', 'Item-Instance') and ml.lookup_code in (1, 2)) or (:$flex$.assignment_type in ('Instance-Organization', 'Category-Instance-Organization', 'Item-Instance-Organization') and ml.lookup_code = 1) or (:$flex$.assignment_type in ('Instance-Region', 'Category-Instance-Region', 'Item-Instance-Region') and ml.lookup_code = 1) ) order by value US Sourcing Rule Type 10 60 1=1 sra.sourcing_rule_name = :p_src_rule_name LOV custom Y select msr.sourcing_rule_name value, msr.description || ' (' || xxen_util.meaning(msr.sourcing_rule_type,'MRP_SOURCING_RULE_TYPE',700) || ')' || decode(msr.sourcing_rule_type,1,' (' || nvl(msc_get_name.org_code(msr.organization_id, msr.sr_instance_id),'All Orgs') || ')') description from msc_sourcing_rules msr where msr.sr_instance_id = :$flex$.sr_instance_id and (:$flex$.sourcing_rule_type is null or xxen_util.contains(:$flex$.sourcing_rule_type,xxen_util.meaning(msr.sourcing_rule_type,'MRP_SOURCING_RULE_TYPE',700)) = 'Y' ) and (:$flex$.sourcing_rule_organization is null or xxen_util.contains(:$flex$.sourcing_rule_organization,msc_get_name.org_code(msr.organization_id, msr.sr_instance_id)) = 'Y' ) order by value US Sourcing Rule Name 11 70 1=1 sra.organization = :p_assign_org LOV custom Y select mtp.organization_code value, mtp.partner_name description from msc_trading_partners mtp where mtp.partner_type = 3 and mtp.sr_instance_id = :$flex$.sr_instance_id order by mtp.organization_code US Organization Code 12 80 1=1 sra.category = :p_assign_category LOV custom Y Y select distinct mic.category_name value, null description from msc_category_sets mcs, msc_item_categories mic where mcs.sr_instance_id = :$flex$.sr_instance_id and mcs.category_set_name = :$flex$.category_set and mic.sr_instance_id = mcs.sr_instance_id and mic.category_set_id = mcs.category_set_id order by lower(mic.category_name) US Category 13 90 1=1 sra.item = :p_assign_item LOV custom Y Y select msi.item_name value, min(msi.description) description from msc_system_items msi, msc_trading_partners mtp where msi.sr_instance_id = :$flex$.sr_instance_id and msi.plan_id = -1 and msi.bom_item_type in (1,2,4) and msi.sr_instance_id = mtp.sr_instance_id and msi.organization_id = mtp.sr_tp_id and mtp.partner_type = 3 and ((:$flex$.organization_code is not null and xxen_util.contains(:$flex$.organization_code,mtp.organization_code) = 'Y') or (:$flex$.organization_code is null and mtp.organization_code = :$flex$.master_organization_code) ) group by msi.item_name order by lower(msi.item_name) US Item 14 100 1=1 sra.customer_name = :p_assign_cust_name LOV custom Y select mtp.partner_name value, mtp.partner_number description from msc_trading_partners mtp where mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_type = 2 order by value US Customer 15 110 1=1 sra.customer_number = :p_assign_cust_num LOV custom Y Y select mtp.partner_number value, mtp.partner_name description from msc_trading_partners mtp where mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_type = 2 order by value US Customer Number 16 120 1=1 sra.customer_Site_ou = :p_assign_cust_site_ou LOV custom Y select distinct mtps.operating_unit_name value, null description from msc_trading_partner_sites mtps where mtps.sr_instance_id = :$flex$.sr_instance_id and mtps.partner_type = 2 order by value US Customer Operating Unit 17 130 1=1 sra.region_zone_description = :p_region_zone LOV custom Y select distinct msav.region_code value, null description from msc_sr_assignments_v msav where msav.sr_instance_id = :$flex$.sr_instance_id and (:$flex$.assignment_set is null or msav.assignment_set_id in (select mas.assignment_set_id from msc_assignment_sets mas where mas.sr_instance_id = :$flex$.sr_instance_id and xxen_util.contains(:$flex$.assignment_set,mas.assignment_set_name) = 'Y') ) and msav.region_code is not null order by value US Region/Zone 18 140 1=1 sra.condition = :p_condition LOV custom Y select meaning value, description from mfg_lookups where lookup_type='MSC_PART_CONDITION' order by value US Condition 19 150 1=1 (sra.collected_flag = :p_collected_data or sra.set_collected_flag = :p_collected_data) LOV custom select meaning value, null description from mfg_lookups where lookup_type='SYS_YES_NO' order by value xxen_util.meaning(2,'SYS_YES_NO',700) select nvl( (select xxen_util.meaning(1,'SYS_YES_NO',700) from msc_assignment_sets mas where mas.sr_instance_id = :$flex$.sr_instance_id and xxen_util.contains(:$flex$.assignment_set,mas.assignment_set_name) = 'Y' and mas.collected_flag = 1 and rownum <= 1 ), xxen_util.meaning(2,'SYS_YES_NO',700) ) from dual Y US Include Collected Data E7337EF85F42CCCAC628E90435B91CAE Default SYSADMIN -58.0018 ASSIGNMENT_ID 1 ASSIGNMENT_SET 2 ASSIGNMENT_SET_DESCRIPTION -58.0017 ASSIGNMENT_SET_ID 3 ASSIGNMENT_TYPE -58.002 ASSIGNMENT_TYPE_ID 6 CATEGORY 26 COLLECTED_FLAG 24 CONDITION 8 CUSTOMER_NAME 9 CUSTOMER_NUMBER 10 CUSTOMER_SITE 12 CUSTOMER_SITE_OU 11 CUSTOMER_SITE_USE 25 DELETE_OPTION 7 DESCRIPTION 5 ITEM 4 ORGANIZATION 18 REGION_CITY 16 REGION_COUNTRY 19 REGION_POSTAL_CODE_FROM 20 REGION_POSTAL_CODE_TO 17 REGION_STATE 13 SHIP_TO_ADDRESS -58.0019 SOURCING_RULE_ID 22 SOURCING_RULE_NAME 23 SOURCING_RULE_ORG 21 SOURCING_RULE_TYPE 15 ZONE 14 ZONE_OR_REGION S Site E7337EF85F42CCCAC628E90435B91CAE 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Char varchar2 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 SET_ROW_ID Char varchar2 Y 7 ASS_ROW_ID Char varchar2 Y 8 UPLOAD_MODE Char varchar2 2000 :$flex$.Upload_Mode Y 9 INSTANCE Char varchar2 2000 :$flex$.planning_instance Y 10 ASSIGNMENT_SET LOV custom varchar2 34 select mas.assignment_set_name value, mas.description || decode(mas.collected_flag,1,' (Collected)',null) description from msc_assignment_sets mas where mas.sr_instance_id = :$flex$.sr_instance_id order by mas.assignment_set_name Y 11 ASSIGNMENT_SET_DESCRIPTION Char varchar2 80 select mas.description from msc_assignment_sets mas where mas.sr_instance_id = :$flex$.sr_instance_id and mas.assignment_set_name = :$column$.assignment_set 12 ASSIGNMENT_TYPE LOV custom varchar2 30 Y select x.value, null description from ( select '1' id, 'Instance' value from dual union all select '2' id, 'Category' value from dual where :$flex$.category_set is not null and (select mcs.control_level from msc_category_sets mcs where mcs.category_set_name = :$flex$.category_set and mcs.sr_instance_id = :$flex$.sr_instance_id) != 2 union all select '3' id, 'Item-Instance' value from dual union all select '4' id, 'Instance-Organization' value from dual union all select '5' id, 'Category-Instance-Organization' value from dual where :$flex$.category_set is not null union all select '6' id, 'Item-Instance-Organization' value from dual union all select '7' id, 'Instance-Region' value from dual union all select '8' id, 'Category-Instance-Region' value from dual where :$flex$.category_set is not null union all select '9' id, 'Item-Instance-Region' value from dual ) x order by x.value select x.id, x.value from ( select '1' id, 'Instance' value from dual union all select '2' id, 'Category' value from dual where :$flex$.category_set is not null and (select mcs.control_level from msc_category_sets mcs where mcs.category_set_name = :$flex$.category_set and mcs.sr_instance_id = :$flex$.sr_instance_id) != 2 union all select '3' id, 'Item-Instance' value from dual union all select '4' id, 'Instance-Organization' value from dual union all select '5' id, 'Category-Instance-Organization' value from dual where :$flex$.category_set is not null union all select '6' id, 'Item-Instance-Organization' value from dual union all select '7' id, 'Instance-Region' value from dual union all select '8' id, 'Category-Instance-Region' value from dual union all select '9' id, 'Item-Instance-Region' value from dual ) x Y Y 13 ORGANIZATION LOV custom varchar2 Y select mtp.organization_code value, mtp.partner_name description from msc_trading_partners mtp where :$column$.assignment_type like '%Organization%' and mtp.partner_type = 3 and mtp.sr_instance_id = :$flex$.sr_instance_id order by mtp.organization_code select sr_tp_id id, organization_code value from msc_trading_partners where partner_type = 3 14 ITEM LOV custom varchar2 Y Y select msi.item_name value, msi.description from msc_system_items msi, msc_trading_partners mtp where :$column$.assignment_type like '%Item%' and msi.sr_instance_id = :$flex$.sr_instance_id and msi.plan_id = -1 and msi.bom_item_type in (1,2,4) and msi.sr_instance_id = mtp.sr_instance_id and msi.organization_id = mtp.sr_tp_id and mtp.partner_type = 3 and ( (:$column$.organization is not null and mtp.organization_code = :$column$.organization) or (:$column$.organization is null and mtp.organization_code = :$flex$.master_organization_code) ) order by lower(msi.item_name) 15 CATEGORY LOV custom varchar2 Y select distinct mic.category_name value, null description from msc_category_sets mcs, msc_item_categories mic where :$column$.assignment_type like '%Category%' and mcs.sr_instance_id = :$flex$.sr_instance_id and mcs.category_set_name = :$flex$.category_set and mic.sr_instance_id = mcs.sr_instance_id and mic.category_set_id = mcs.category_set_id and (nvl(mic.disable_date,sysdate+1) > sysdate) order by lower(mic.category_name) 16 DESCRIPTION LOV custom varchar2 Y select x.value, null description from ( select msi.description value from msc_system_items msi, msc_trading_partners mtp where :$column$.assignment_type like '%Item' and msi.sr_instance_id = mtp.sr_instance_id and msi.organization_id = mtp.sr_tp_id and msi.sr_instance_id = :$flex$.sr_instance_id and msi.item_name = :$column$.item and msi.bom_item_type in (1,2,4) and mtp.partner_type = 3 and ( (:$column$.organization is not null and mtp.organization_code = :$column$.organization) or (:$column$.organization is null and mtp.organization_code = :$flex$.master_organization_code) ) and :$column$.category is null union select mic.description value from msc_category_sets mcs, msc_item_categories mic where :$column$.assignment_type like '%Category%' and mic.sr_instance_id = mcs.sr_instance_id and mic.category_set_id = mcs.category_set_id and mcs.sr_instance_id = :$flex$.sr_instance_id and mcs.category_set_name = :$flex$.category_set and mic.category_name = :$column$.category and (nvl(mic.disable_date,sysdate+1) > sysdate) and :$column$.item is null and rownum <= 1 ) x select x.value from ( select msi.description value from msc_system_items msi, msc_trading_partners mtp where :$column$.assignment_type like '%Item%' and msi.sr_instance_id = mtp.sr_instance_id and msi.plan_id = -1 and msi.organization_id = mtp.sr_tp_id and msi.sr_instance_id = :$flex$.sr_instance_id and msi.item_name = :$column$.item and msi.bom_item_type in (1,2,4) and mtp.partner_type = 3 and ( (:$column$.organization is not null and mtp.organization_code = :$column$.organization) or (:$column$.organization is null and mtp.organization_code = :$flex$.master_organization_code) ) and :$column$.category is null and rownum <= 1 union select mic.description value from msc_category_sets mcs, msc_item_categories mic where :$column$.assignment_type like '%Category%' and mic.sr_instance_id = mcs.sr_instance_id and mic.category_set_id = mcs.category_set_id and mcs.sr_instance_id = :$flex$.sr_instance_id and mcs.category_set_name = :$flex$.category_set and mic.category_name = :$column$.category and (nvl(mic.disable_date,sysdate+1) > sysdate) and :$column$.item is null and rownum <= 1 ) x Y 17 CUSTOMER_NAME LOV custom varchar2 Y select mtp.partner_name value, mtp.partner_number description from msc_trading_partners mtp where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_type = 2 and exists (select null from msc_trading_partner_sites mtps where mtps.sr_instance_id = mtp.sr_instance_id and mtps.partner_id = mtp.partner_id and mtps.partner_type = mtp.partner_type and mtps.tp_site_code in ('SHIP_TO','BILL_TO') ) and (:$column$.organization is null or exists (select null from msc_cust_sup_org_v mcsov where mcsov.sr_instance_id = :$flex$.sr_instance_id and mcsov.organization_code = :$column$.organization and mcsov.customer_name is not null and mcsov.customer_id = mtp.partner_id ) ) order by value select mcsov.customer_name from msc_cust_sup_org_v mcsov where mcsov.sr_instance_id = :$flex$.sr_instance_id and mcsov.organization_code = :$column$.organization and mcsov.customer_name is not null 18 CUSTOMER_NUMBER LOV custom varchar2 154 Y select mtp.partner_number value, mtp.partner_name description from msc_trading_partners mtp where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_name = :$column$.customer_name and mtp.partner_type = 2 order by value select mtp.partner_number from msc_trading_partners mtp where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_name = :$column$.customer_name and mtp.partner_type = 2 19 CUSTOMER_SITE LOV custom varchar2 60 Y select mtps.location value, xxen_util.meaning(mtps.tp_site_code,'SITE_USE_CODE',222) || ': ' || mtps.partner_address || ' / ' || mtps.operating_unit_name description from msc_trading_partners mtp, msc_trading_partner_sites mtps where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = mtps.sr_instance_id and mtp.partner_id = mtps.partner_id and mtp.partner_type = mtps.partner_type and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_name = :$column$.customer_name and mtp.partner_number = nvl(:$column$.customer_number,mtp.partner_number) and mtp.partner_type = 2 and mtps.tp_site_code in ('SHIP_TO','BILL_TO') and (:$column$.organization is null or exists (select null from msc_cust_sup_org_v mcsov where mcsov.sr_instance_id = :$flex$.sr_instance_id and mcsov.organization_code = :$column$.organization and mcsov.customer_name = :$column$.customer_name and mcsov.customer_id = mtps.partner_id and mcsov.ship_to_site_id = mtps.partner_site_id ) ) order by value select mtps.location from msc_cust_sup_org_v mcsov, msc_trading_partner_sites mtps where :$column$.organization is not null and mcsov.sr_instance_id = :$flex$.sr_instance_id and mcsov.organization_code = :$column$.organization and mcsov.customer_name is not null and mcsov.sr_instance_id = mtps.sr_instance_id and mcsov.customer_id = mtps.partner_id and mcsov.ship_to_site_id = mtps.partner_site_id and mtps.partner_type = 2 union select mtps.location from msc_trading_partners mtp, msc_trading_partner_sites mtps where :$column$.assignment_type like '%Organization%' and :$column$.organization is null and mtp.sr_instance_id = mtps.sr_instance_id and mtp.partner_id = mtps.partner_id and mtp.partner_type = mtps.partner_type and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_name = :$column$.customer_name and mtp.partner_number = nvl(:$column$.customer_number,mtp.partner_number) and mtp.partner_type = 2 and mtps.tp_site_code in ('SHIP_TO','BILL_TO') 20 CUSTOMER_SITE_USE LOV custom varchar2 Y select xxen_util.meaning(mtps.tp_site_code,'SITE_USE_CODE',222) value, null description from msc_trading_partners mtp, msc_trading_partner_sites mtps where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = mtps.sr_instance_id and mtp.partner_id = mtps.partner_id and mtp.partner_type = mtps.partner_type and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_type = 2 and mtp.partner_name = :$column$.customer_name and mtp.partner_number = nvl(:$column$.customer_number,mtp.partner_number) and mtps.location = :$column$.customer_site and mtps.tp_site_code in ('SHIP_TO','BILL_TO') order by value select flvv.lookup_code id, flvv.meaning value from fnd_lookup_values_vl flvv where flvv.lookup_type = 'SITE_USE_CODE' and flvv.view_application_id = 222 and flvv.security_group_id = fnd_global.security_group_id select xxen_util.meaning(mtps.tp_site_code,'SITE_USE_CODE',222) from msc_trading_partners mtp, msc_trading_partner_sites mtps where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = mtps.sr_instance_id and mtp.partner_id = mtps.partner_id and mtp.partner_type = mtps.partner_type and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_name = :$column$.customer_name and mtp.partner_number = nvl(:$column$.customer_number,mtp.partner_number) and mtps.location = nvl(:$column$.customer_site,mtps.location) and mtps.tp_site_code in ('SHIP_TO','BILL_TO') 21 CUSTOMER_SITE_OU LOV custom varchar2 240 Y select mtps.operating_unit_name value, null description from msc_trading_partners mtp, msc_trading_partner_sites mtps where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = mtps.sr_instance_id and mtp.partner_id = mtps.partner_id and mtp.partner_type = mtps.partner_type and mtp.partner_type = 2 and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_name = :$column$.customer_name and mtp.partner_number = nvl(:$column$.customer_number,mtp.partner_number) and mtps.location = :$column$.customer_site and mtps.tp_site_code = xxen_util.lookup_code( :$column$.customer_site_use,'SITE_USE_CODE',222) and mtps.tp_site_code in ('SHIP_TO','BILL_TO') order by value select mtps.operating_unit_name value from msc_trading_partners mtp, msc_trading_partner_sites mtps where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = mtps.sr_instance_id and mtp.partner_id = mtps.partner_id and mtp.partner_type = mtps.partner_type and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_name = :$column$.customer_name and mtp.partner_number = nvl(:$column$.customer_number,mtp.partner_number) and mtps.location = nvl(:$column$.customer_site,mtps.location) and mtps.tp_site_code = nvl(xxen_util.lookup_code( :$column$.customer_site_use,'SITE_USE_CODE',222),mtps.tp_site_code) and mtps.tp_site_code in ('SHIP_TO','BILL_TO') 22 SHIP_TO_ADDRESS Char varchar2 1600 select mtps.partner_address value from msc_trading_partners mtp, msc_trading_partner_sites mtps where :$column$.assignment_type like '%Organization%' and mtp.sr_instance_id = mtps.sr_instance_id and mtp.partner_id = mtps.partner_id and mtp.partner_type = mtps.partner_type and mtp.partner_type = 2 and mtp.sr_instance_id = :$flex$.sr_instance_id and mtp.partner_name = :$column$.customer_name and mtp.partner_number = nvl(:$column$.customer_number,mtp.partner_number) and mtps.location = nvl(:$column$.customer_site,mtps.location) and mtps.tp_site_code = nvl(xxen_util.lookup_code( :$column$.customer_site_use,'SITE_USE_CODE',222),mtps.tp_site_code) and mtps.operating_unit_name = nvl(:$column$.customer_site_ou,mtps.operating_unit_name) Y 23 ZONE_OR_REGION LOV custom varchar2 6 Y select x.value, null description from (select 'Region' value from dual union select 'Zone' value from dual ) x where :$column$.assignment_type like '%Region%' order by x.value 24 ZONE LOV custom varchar2 60 Y select zone value, null description from msc_regions where :$column$.zone_or_region = 'Zone' and sr_instance_id = :$flex$.sr_instance_id and zone is not null order by value 25 REGION_COUNTRY LOV custom varchar2 60 Y select country value, country_code description from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and region_type = 0 order by value 26 REGION_STATE LOV custom varchar2 60 Y select state value, country description from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and country = :$column$.region_country and region_type = 1 order by value select state value from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and country = :$column$.region_country and region_type = 1 27 REGION_CITY LOV custom varchar2 60 Y select city value, country || ',' || state description from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and country = :$column$.region_country and nvl(state,'??') = nvl(:$column$.region_state,'??') and region_type = 2 order by value select city value from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and country = :$column$.region_country and nvl(state,'??') = nvl(:$column$.region_state,'??') and region_type = 2 28 REGION_POSTAL_CODE_FROM LOV custom varchar2 60 Y select postal_code_from value, country || ',' || state || ',' || city description from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and country = :$column$.region_country and nvl(state,'??') = nvl(:$column$.region_state,'??') and nvl(city,'??') = nvl(:$column$.region_city,'??') and postal_code_from is not null and region_type = 3 order by value select postal_code_from value from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and country = :$column$.region_country and nvl(state,'??') = nvl(:$column$.region_state,'??') and nvl(city,'??') = nvl(:$column$.region_city,'??') and postal_code_from is not null and region_type = 3 29 REGION_POSTAL_CODE_TO LOV custom varchar2 60 Y select postal_code_to value, country || ',' || state || ',' || city || ',' || postal_code_from description from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and country = :$column$.region_country and nvl(state,'??') = nvl(:$column$.region_state,'??') and nvl(city,'??') = nvl(:$column$.region_city,'??') and postal_code_from = :$column$.region_postal_code_from and postal_code_to is not null and region_type = 3 order by value select postal_code_to value from msc_regions where :$column$.zone_or_region = 'Region' and sr_instance_id = :$flex$.sr_instance_id and country = :$column$.region_country and nvl(state,'??') = nvl(:$column$.region_state,'??') and nvl(city,'??') = nvl(:$column$.region_city,'??') and postal_code_from = :$column$.region_postal_code_from and postal_code_to is not null and region_type = 3 30 SOURCING_RULE_TYPE LOV custom varchar2 80 Y select ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type = 'MRP_SOURCING_RULE_TYPE' and ((:$column$.assignment_type in ('Instance', 'Category', 'Item-Instance') and ml.lookup_code in (1, 2)) or ml.lookup_code = 1 ) order by value select lookup_code id, meaning value from mfg_lookups where lookup_type = 'MRP_SOURCING_RULE_TYPE' Y 31 SOURCING_RULE_NAME LOV custom varchar2 80 Y select msr.sourcing_rule_name value, msr.description || ' (' || nvl(mtp.organization_code,'All Orgs') || ')' description from msc_sourcing_rules msr, msc_trading_partners mtp where msr.sr_instance_id = mtp.sr_instance_id (+) and msr.organization_id = mtp.sr_tp_id (+) and mtp.partner_type (+) = 3 and msr.sr_instance_id = :$flex$.sr_instance_id and msr.sourcing_rule_type = xxen_util.lookup_code(:$column$.sourcing_rule_type,'MRP_SOURCING_RULE_TYPE',700) and msr.planning_active = 1 and ((:$column$.assignment_type in ('Instance', 'Category', 'Item-Instance') and msr.organization_id is null) or (:$column$.assignment_type in ('Instance-Organization', 'Category-Instance-Organization', 'Item-Instance-Organization') and (msr.organization_id is null or mtp.organization_code = :$column$.organization)) or (:$column$.assignment_type in ('Instance-Region', 'Category-Instance-Region', 'Item-Instance-Region') and msr.organization_id is null) ) and nvl(msr.sourcing_association_type,2)=2 order by value Y 32 SOURCING_RULE_ORG LOV custom varchar2 7 Y select mtp.organization_code value, null description from msc_sourcing_rules msr, msc_trading_partners mtp where msr.sr_instance_id = mtp.sr_instance_id and msr.organization_id = mtp.sr_tp_id and mtp.partner_type = 3 and msr.sr_instance_id = :$flex$.sr_instance_id and msr.sourcing_rule_type = xxen_util.lookup_code(:$column$.sourcing_rule_type,'MRP_SOURCING_RULE_TYPE',700) and msr.sourcing_rule_name = :$column$.sourcing_rule_name and :$column$.assignment_type in ('Instance-Organization', 'Category-Instance-Organization', 'Item-Instance-Organization') and mtp.organization_code = :$column$.organization order by value select sr_tp_id id, organization_code value from msc_trading_partners where partner_type = 3 select mtp.organization_code value from msc_sourcing_rules msr, msc_trading_partners mtp where msr.sr_instance_id = mtp.sr_instance_id and msr.organization_id = mtp.sr_tp_id and mtp.partner_type = 3 and msr.sr_instance_id = :$flex$.sr_instance_id and msr.sourcing_rule_type = xxen_util.lookup_code(:$column$.sourcing_rule_type,'MRP_SOURCING_RULE_TYPE',700) and msr.sourcing_rule_name = :$column$.sourcing_rule_name and :$column$.assignment_type in ('Instance-Organization', 'Category-Instance-Organization', 'Item-Instance-Organization') and mtp.organization_code = :$column$.organization 33 CONDITION LOV custom varchar2 Y select meaning value, null description from mfg_lookups where lookup_type = 'MSC_PART_CONDITION' order by value xxen_util.meaning(1,'MSC_PART_CONDITION',700) 34 SET_ATTRIBUTE_CATEGORY Char varchar2 30 35 SET_ATTRIBUTE1 Char varchar2 150 36 SET_ATTRIBUTE2 Char varchar2 150 37 SET_ATTRIBUTE3 Char varchar2 150 38 SET_ATTRIBUTE4 Char varchar2 150 39 SET_ATTRIBUTE5 Char varchar2 150 40 SET_ATTRIBUTE6 Char varchar2 150 41 SET_ATTRIBUTE7 Char varchar2 150 42 SET_ATTRIBUTE8 Char varchar2 150 43 SET_ATTRIBUTE9 Char varchar2 150 44 SET_ATTRIBUTE10 Char varchar2 150 45 SET_ATTRIBUTE11 Char varchar2 150 46 SET_ATTRIBUTE12 Char varchar2 150 47 SET_ATTRIBUTE13 Char varchar2 150 48 SET_ATTRIBUTE14 Char varchar2 150 49 SET_ATTRIBUTE15 Char varchar2 150 50 ASSIGN_ATTRIBUTE_CATEGORY Char varchar2 30 51 ASSIGN_ATTRIBUTE1 Char varchar2 150 52 ASSIGN_ATTRIBUTE2 Char varchar2 150 53 ASSIGN_ATTRIBUTE3 Char varchar2 150 54 ASSIGN_ATTRIBUTE4 Char varchar2 150 55 ASSIGN_ATTRIBUTE5 Char varchar2 150 56 ASSIGN_ATTRIBUTE6 Char varchar2 150 57 ASSIGN_ATTRIBUTE7 Char varchar2 150 58 ASSIGN_ATTRIBUTE8 Char varchar2 150 59 ASSIGN_ATTRIBUTE9 Char varchar2 150 60 ASSIGN_ATTRIBUTE10 Char varchar2 150 61 ASSIGN_ATTRIBUTE11 Char varchar2 150 62 ASSIGN_ATTRIBUTE12 Char varchar2 150 63 ASSIGN_ATTRIBUTE13 Char varchar2 150 64 ASSIGN_ATTRIBUTE14 Char varchar2 150 65 ASSIGN_ATTRIBUTE15 Char varchar2 150 66 DELETE_OPTION LOV custom varchar2 Y select value, description from ( select 'Assignment Set' value, 'Delete the Assignment Set' description from dual union all select 'Assignment' value, 'Delete the Assignment' description from dual ) order by value 67 COLLECTED_FLAG Char varchar2 xxen_util.meaning(2,'SYS_YES_NO',700) Y 68 SR_INSTANCE_ID Number number :$flex$.sr_instance_id Y 69 ASSIGNMENT_SET_ID Number number Y 70 ASSIGNMENT_ID Number number Y 71 SOURCING_RULE_ID Number number Y 72 ASSIGNMENT_TYPE_ID Number number Y 73 MASTER_ORGANIZATION_ID Number number :$flex$.master_organization_id Y 74 UPLOAD_ROW Number number Y U 1 action_ in Char ACTION_CODE_ U 2 p_upload_mode in Char UPLOAD_MODE U 3 p_sr_instance_id in Number SR_INSTANCE_ID U 4 p_assignment_set_id in Number ASSIGNMENT_SET_ID U 5 p_assignment_set_name in Char ASSIGNMENT_SET U 6 p_assignment_set_description in Char ASSIGNMENT_SET_DESCRIPTION U 7 p_set_attribute_category in Char SET_ATTRIBUTE_CATEGORY U 8 p_set_attribute1 in Char SET_ATTRIBUTE1 U 9 p_set_attribute2 in Char SET_ATTRIBUTE2 U 10 p_set_attribute3 in Char SET_ATTRIBUTE3 U 11 p_set_attribute4 in Char SET_ATTRIBUTE4 U 12 p_set_attribute5 in Char SET_ATTRIBUTE5 U 13 p_set_attribute6 in Char SET_ATTRIBUTE6 U 14 p_set_attribute7 in Char SET_ATTRIBUTE7 U 15 p_set_attribute8 in Char SET_ATTRIBUTE8 U 16 p_set_attribute9 in Char SET_ATTRIBUTE9 U 17 p_set_attribute10 in Char SET_ATTRIBUTE10 U 18 p_set_attribute11 in Char SET_ATTRIBUTE11 U 19 p_set_attribute12 in Char SET_ATTRIBUTE12 U 20 p_set_attribute13 in Char SET_ATTRIBUTE13 U 21 p_set_attribute14 in Char SET_ATTRIBUTE14 U 22 p_set_attribute15 in Char SET_ATTRIBUTE15 U 23 p_assignment_id in Number ASSIGNMENT_ID U 24 p_assignment_type in Number ASSIGNMENT_TYPE U 25 p_organization_id in Number ORGANIZATION U 26 p_item in Char ITEM U 27 p_inventory_item_id in Number U 28 p_category_set_id in Number U 29 p_category in Char CATEGORY U 30 p_customer_name in Char CUSTOMER_NAME U 31 p_customer_number in Char CUSTOMER_NUMBER U 32 p_customer_site in Char CUSTOMER_SITE U 33 p_customer_site_use in Char CUSTOMER_SITE_USE U 34 p_customer_site_ou in Char CUSTOMER_SITE_OU U 35 p_customer_id in Number U 36 p_customer_site_id in Number U 37 p_sourcing_rule_type in Number SOURCING_RULE_TYPE U 38 p_sourcing_rule_name in Char SOURCING_RULE_NAME U 39 p_sourcing_rule_org_id in Number SOURCING_RULE_ORG U 40 p_sourcing_rule_id in Number U 41 p_condition in Char CONDITION U 42 p_zone_or_region in Char ZONE_OR_REGION U 43 p_zone in Char ZONE U 44 p_region_country in Char REGION_COUNTRY U 45 p_region_state in Char REGION_STATE U 46 p_region_city in Char REGION_CITY U 47 p_region_postal_code_from in Char REGION_POSTAL_CODE_FROM U 48 p_region_postal_code_to in Char REGION_POSTAL_CODE_TO U 49 p_ass_attribute_category in Char ASSIGN_ATTRIBUTE_CATEGORY U 50 p_ass_attribute1 in Char ASSIGN_ATTRIBUTE1 U 51 p_ass_attribute2 in Char ASSIGN_ATTRIBUTE2 U 52 p_ass_attribute3 in Char ASSIGN_ATTRIBUTE3 U 53 p_ass_attribute4 in Char ASSIGN_ATTRIBUTE4 U 54 p_ass_attribute5 in Char ASSIGN_ATTRIBUTE5 U 55 p_ass_attribute6 in Char ASSIGN_ATTRIBUTE6 U 56 p_ass_attribute7 in Char ASSIGN_ATTRIBUTE7 U 57 p_ass_attribute8 in Char ASSIGN_ATTRIBUTE8 U 58 p_ass_attribute9 in Char ASSIGN_ATTRIBUTE9 U 59 p_ass_attribute10 in Char ASSIGN_ATTRIBUTE10 U 60 p_ass_attribute11 in Char ASSIGN_ATTRIBUTE11 U 61 p_ass_attribute12 in Char ASSIGN_ATTRIBUTE12 U 62 p_ass_attribute13 in Char ASSIGN_ATTRIBUTE13 U 63 p_ass_attribute14 in Char ASSIGN_ATTRIBUTE14 U 64 p_ass_attribute15 in Char ASSIGN_ATTRIBUTE15 U 65 p_delete_option in Char DELETE_OPTION U 67 p_master_organization_id in Number MASTER_ORGANIZATION_ID U 68 p_set_row_id in/out Char SET_ROW_ID U 69 p_ass_row_id in/out Char ASS_ROW_ID U 70 request_id_ out Number REQUEST_ID_ U 71 status_ out Char STATUS_CODE_ U 72 message_ out Char MESSAGE_ Error SQL Order By upload_row, assignment_set, assignment_type_id, organization, nvl(item,category), customer_name, customer_site, zone, region_country, region_state, region_city, region_postal_code_from, region_postal_code_to, sourcing_rule_name Success SQL select /*+ ordered push_pred(sra) */ xm.action_, case when xm.status_code_ = xxen_upload.status_success then case when xm.delete_option is not null and sra.assignment_set_id is null then xxen_upload.status_meaning(xxen_upload.status_success) when sra.assignment_set_id is null then xxen_upload.status_meaning(xxen_upload.status_error) else xm.status_ end else xm.status_ end status_, xm.message_, xm.request_id_, xm.modified_columns_, xm.set_row_id, xm.ass_row_id, xm.upload_mode, xm.instance, -- assignment set case when sra.assignment_id is null then xm.assignment_set else sra.assignment_set end, case when sra.assignment_id is null then xm.assignment_set_description else sra.assignment_set_description end, -- assignment case when sra.assignment_id is null then xm.assignment_type else sra.assignment_type end, case when sra.assignment_id is null then xm.organization else sra.organization end, case when sra.assignment_id is null then xm.item else sra.item end, case when sra.assignment_id is null then xm.category else sra.category end, case when sra.assignment_id is null then xm.description else sra.description end, case when sra.assignment_id is null then xm.customer_name else sra.customer_name end, case when sra.assignment_id is null then xm.customer_number else sra.customer_number end, case when sra.assignment_id is null then xm.customer_site else sra.customer_site end, case when sra.assignment_id is null then xm.customer_site_use else sra.customer_site_use end, case when sra.assignment_id is null then xm.customer_site_ou else sra.customer_site_ou end, case when sra.assignment_id is null then xm.ship_to_address else sra.ship_to_address end, -- case when sra.assignment_id is null then xm.zone_or_region else sra.zone_or_region end, case when sra.assignment_id is null then xm.zone else sra.zone end, case when sra.assignment_id is null then xm.region_country else sra.region_country end, case when sra.assignment_id is null then xm.region_state else sra.region_state end, case when sra.assignment_id is null then xm.region_city else sra.region_city end, case when sra.assignment_id is null then xm.region_postal_code_from else sra.region_postal_code_from end, case when sra.assignment_id is null then xm.region_postal_code_to else sra.region_postal_code_to end, -- case when sra.assignment_id is null then xm.sourcing_rule_type else sra.sourcing_rule_type end, case when sra.assignment_id is null then xm.sourcing_rule_name else sra.sourcing_rule_name end, case when sra.assignment_id is null then xm.sourcing_rule_org else sra.sourcing_rule_org end, case when sra.assignment_id is null then xm.condition else sra.condition end, -- attributes case when sra.assignment_id is null then xm.set_attribute_category else sra.set_attribute_category end, case when sra.assignment_id is null then xm.set_attribute1 else sra.set_attribute1 end, case when sra.assignment_id is null then xm.set_attribute2 else sra.set_attribute2 end, case when sra.assignment_id is null then xm.set_attribute3 else sra.set_attribute3 end, case when sra.assignment_id is null then xm.set_attribute4 else sra.set_attribute4 end, case when sra.assignment_id is null then xm.set_attribute5 else sra.set_attribute5 end, case when sra.assignment_id is null then xm.set_attribute6 else sra.set_attribute6 end, case when sra.assignment_id is null then xm.set_attribute7 else sra.set_attribute7 end, case when sra.assignment_id is null then xm.set_attribute8 else sra.set_attribute8 end, case when sra.assignment_id is null then xm.set_attribute9 else sra.set_attribute9 end, case when sra.assignment_id is null then xm.set_attribute10 else sra.set_attribute10 end, case when sra.assignment_id is null then xm.set_attribute11 else sra.set_attribute11 end, case when sra.assignment_id is null then xm.set_attribute12 else sra.set_attribute12 end, case when sra.assignment_id is null then xm.set_attribute13 else sra.set_attribute13 end, case when sra.assignment_id is null then xm.set_attribute14 else sra.set_attribute14 end, case when sra.assignment_id is null then xm.set_attribute15 else sra.set_attribute15 end, -- case when sra.assignment_id is null then xm.assign_attribute_category else sra.assign_attribute_category end, case when sra.assignment_id is null then xm.assign_attribute1 else sra.assign_attribute1 end, case when sra.assignment_id is null then xm.assign_attribute2 else sra.assign_attribute2 end, case when sra.assignment_id is null then xm.assign_attribute3 else sra.assign_attribute3 end, case when sra.assignment_id is null then xm.assign_attribute4 else sra.assign_attribute4 end, case when sra.assignment_id is null then xm.assign_attribute5 else sra.assign_attribute5 end, case when sra.assignment_id is null then xm.assign_attribute6 else sra.assign_attribute6 end, case when sra.assignment_id is null then xm.assign_attribute7 else sra.assign_attribute7 end, case when sra.assignment_id is null then xm.assign_attribute8 else sra.assign_attribute8 end, case when sra.assignment_id is null then xm.assign_attribute9 else sra.assign_attribute9 end, case when sra.assignment_id is null then xm.assign_attribute10 else sra.assign_attribute10 end, case when sra.assignment_id is null then xm.assign_attribute11 else sra.assign_attribute11 end, case when sra.assignment_id is null then xm.assign_attribute12 else sra.assign_attribute12 end, case when sra.assignment_id is null then xm.assign_attribute13 else sra.assign_attribute13 end, case when sra.assignment_id is null then xm.assign_attribute14 else sra.assign_attribute14 end, case when sra.assignment_id is null then xm.assign_attribute15 else sra.assign_attribute15 end, -- xm.delete_option, case when sra.assignment_id is null then xm.collected_flag else sra.collected_flag end, -- ids case when sra.assignment_id is null then xm.sr_instance_id else sra.sr_instance_id end, case when sra.assignment_id is null then xm.assignment_set_id else sra.assignment_set_id end, case when sra.assignment_id is null then xm.assignment_id else sra.assignment_id end, case when sra.assignment_id is null then xm.sourcing_rule_id else sra.sourcing_rule_id end, case when sra.assignment_id is null then xm.assignment_type_id else sra.assignment_type_id end, xm.master_organization_id, xm.upload_row from xxen_msc_sourcing_rule_6445_u xm, ( select -- assignment set mas.assignment_set_name assignment_set, mas.description assignment_set_description, -- assignment case msav.assignment_type when 1 then 'Instance' when 2 then 'Category' when 3 then 'Item-Instance' when 4 then 'Instance-Organization' when 5 then 'Category-Instance-Organization' when 6 then 'Item-Instance-Organization' when 8 then 'Category-Instance-Region' when 7 then 'Instance-Region' when 9 then 'Item-Instance-Region' end assignment_type, decode(msav.assignment_type,3,null,msc_get_name.org_code(msav.organization_id, msav.sr_instance_id)) organization, msav.organization_code, case when msav.assignment_type in (3,6,9) then msav.entity_name else null end item, case when msav.assignment_type in (2,5,8) then msav.entity_name else null end category, msav.description, msav.customer_name, hca.partner_number customer_number, hcsua.location customer_site, xxen_util.meaning(hcsua.tp_site_code,'SITE_USE_CODE',222) customer_site_use, msav.ship_to_address, hcsua.operating_unit_name customer_site_ou, msav.sourcing_rule_type_text sourcing_rule_type, msav.sourcing_rule_name, mp.organization_code sourcing_rule_org, msav.item_type_value_text condition, -- case when mr.region_id is null then null when mr.zone is not null then 'Zone' else 'Region' end zone_or_region, mr.zone, mr.country region_country, mr.state region_state, mr.city region_city, mr.postal_code_from region_postal_code_from, mr.postal_code_to region_postal_code_to, msav.region_code region_zone_description, -- attributes mas.attribute_category set_attribute_category, mas.attribute1 set_attribute1, mas.attribute2 set_attribute2, mas.attribute3 set_attribute3, mas.attribute4 set_attribute4, mas.attribute5 set_attribute5, mas.attribute6 set_attribute6, mas.attribute7 set_attribute7, mas.attribute8 set_attribute8, mas.attribute9 set_attribute9, mas.attribute10 set_attribute10, mas.attribute11 set_attribute11, mas.attribute12 set_attribute12, mas.attribute13 set_attribute13, mas.attribute14 set_attribute14, mas.attribute15 set_attribute15, -- msav.attribute_category assign_attribute_category, msav.attribute1 assign_attribute1, msav.attribute2 assign_attribute2, msav.attribute3 assign_attribute3, msav.attribute4 assign_attribute4, msav.attribute5 assign_attribute5, msav.attribute6 assign_attribute6, msav.attribute7 assign_attribute7, msav.attribute8 assign_attribute8, msav.attribute9 assign_attribute9, msav.attribute10 assign_attribute10, msav.attribute11 assign_attribute11, msav.attribute12 assign_attribute12, msav.attribute13 assign_attribute13, msav.attribute14 assign_attribute14, msav.attribute15 assign_attribute15, -- xxen_util.meaning(nvl(mas.collected_flag,2),'SYS_YES_NO',700) set_collected_flag, xxen_util.meaning(nvl(msav.collected_flag,2),'SYS_YES_NO',700) collected_flag, -- ids mas.sr_instance_id, mas.assignment_set_id, msav.assignment_id, msav.sourcing_rule_id, msav.assignment_type assignment_type_id, -- msav.last_updated_by, msav.last_update_date from msc_assignment_sets mas, msc_sr_assignments_v msav, msc_sourcing_rules msr, msc_trading_partners mp, msc_trading_partners hca, msc_trading_partner_sites hcsua, msc_regions mr where mas.sr_instance_id = :p_sr_instance_id and mas.assignment_set_id = msav.assignment_set_id and mas.sr_instance_id = msav.sr_assignment_instance_id and msav.sourcing_rule_id = msr.sourcing_rule_id (+) and msav.sr_assignment_instance_id = msr.sr_instance_id (+) and -- msr.sr_instance_id = mp.sr_instance_id (+) and msr.organization_id = mp.sr_tp_id (+) and mp.partner_type (+) = 3 and -- msav.sr_assignment_instance_id = hca.sr_instance_id (+) and msav.customer_id = hca.partner_id (+) and hca.partner_type (+) = 2 and msav.sr_assignment_instance_id = hcsua.sr_instance_id (+) and msav.ship_to_site_id = hcsua.partner_site_id (+) and hcsua.partner_type (+) = 2 and msav.sr_assignment_instance_id = mr.sr_instance_id (+) and msav.region_id = mr.region_id (+) ) sra where xm.status_code_=xxen_upload.status_success and sra.assignment_id (+) = decode(xm.status_code_,xxen_upload.status_success,to_number(xm.ass_row_id),to_number(null))