MSC_AP_MRSL_RULE_DETAILS

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
MRSL_RULE_DETAIL_ID MRSL_RULE_ID MRSL_RULE_NAME ASSIGNMENT_TYPE INVENTORY_ITEM_ID ITEM_NAME ORGANIZATION_ID ORGANIZATION_CODE CUSTOMER_ID CUSTOMER_NUMBER CUSTOMER_SITE_ID TP_SITE_CODE CATEGORY_ID CATEGORY_NAME DEMAND_CLASS GEOGRAPHY_ID REGION_NAME COUNTRY_CODE PARENT_REGION_NAME_1 PARENT_REGION_NAME_2 PARENT_REGION_NAME_3 PARENT_REGION_NAME_4 PARENT_REGION_NAME_5 PARENT_REGION_NAME_6 PARENT_REGION_NAME_7 PARENT_REGION_NAME_8 ZONE_ID ZONE_NAME ZONE_TYPE MRSL_DAYS

Name

MRSL_RULE_DETAIL_ID

MRSL_RULE_ID

MRSL_RULE_NAME

ASSIGNMENT_TYPE

INVENTORY_ITEM_ID

ITEM_NAME

ORGANIZATION_ID

ORGANIZATION_CODE

CUSTOMER_ID

CUSTOMER_NUMBER

CUSTOMER_SITE_ID

TP_SITE_CODE

CATEGORY_ID

CATEGORY_NAME

DEMAND_CLASS

GEOGRAPHY_ID

REGION_NAME

COUNTRY_CODE

PARENT_REGION_NAME_1

PARENT_REGION_NAME_2

PARENT_REGION_NAME_3

PARENT_REGION_NAME_4

PARENT_REGION_NAME_5

PARENT_REGION_NAME_6

PARENT_REGION_NAME_7

PARENT_REGION_NAME_8

ZONE_ID

ZONE_NAME

ZONE_TYPE

MRSL_DAYS

Query

SQL_Statement
SELECT mrsld.mrsl_rule_detail_id, mrsl.mrsl_rule_id, mrsl.mrsl_rule_name, mrsld.assignment_type, mrsld.inventory_item_id, ( SELECT itm.item_number FROM egp_system_items_b itm WHERE itm.inventory_item_id = mrsld.inventory_item_id AND ROWNUM <= 1 ) item_name, mrsld.organization_id, org.organization_code, mrsld.customer_id, cust.party_number customer_number, mrsld.customer_site_id, custsite.party_site_number tp_site_code, mrsld.category_id, cat.category_name, dmdclass.meaning demand_class, mrsld.geography_id, geo.geography_name region_name, NVL(geo.country_code,zon.country_code) country_code, geo.geography_element2 parent_region_name_1, geo.geography_element3 parent_region_name_2, geo.geography_element4 parent_region_name_3, geo.geography_element5 parent_region_name_4, geo.geography_element6 parent_region_name_5, geo.geography_element7 parent_region_name_6, geo.geography_element8 parent_region_name_7, geo.geography_element9 parent_region_name_8, mrsld.zone_id, zon.geography_name zone_name, zon.geography_type zone_type, mrsld.mrsl_days FROM MSC_SRC_MRSL_RULES_VL mrsl, MSC_SRC_MRSL_RULE_DETAILS mrsld, inv_org_parameters org, hz_parties cust, hz_party_sites custsite, egp_categories_vl cat, fnd_lookup_values_vl dmdclass, hz_geographies geo, hz_geographies zon WHERE mrsld.mrsl_rule_id = mrsl.mrsl_rule_id AND org.organization_id(+) = mrsld.organization_id AND cust.party_id(+) = mrsld.customer_id AND custsite.party_site_id(+) = mrsld.customer_site_id AND cat.category_id(+) = mrsld.category_id AND dmdclass.lookup_type(+) = 'ORA_DOO_DEMAND_CLASS' AND dmdclass.lookup_code(+) = mrsld.demand_class AND geo.geography_id(+) = mrsld.geography_id AND zon.geography_id(+) = mrsld.zone_id

SQL_Statement

SELECT

mrsld.mrsl_rule_detail_id,

mrsl.mrsl_rule_id,

mrsl.mrsl_rule_name,

mrsld.assignment_type,

mrsld.inventory_item_id,

(

SELECT itm.item_number

FROM egp_system_items_b itm

WHERE itm.inventory_item_id = mrsld.inventory_item_id

AND ROWNUM <= 1

) item_name,

mrsld.organization_id,

org.organization_code,

mrsld.customer_id,

cust.party_number customer_number,

mrsld.customer_site_id,

custsite.party_site_number tp_site_code,

mrsld.category_id,

cat.category_name,

dmdclass.meaning demand_class,

mrsld.geography_id,

geo.geography_name region_name,

NVL(geo.country_code,zon.country_code) country_code,

geo.geography_element2 parent_region_name_1,

geo.geography_element3 parent_region_name_2,

geo.geography_element4 parent_region_name_3,

geo.geography_element5 parent_region_name_4,

geo.geography_element6 parent_region_name_5,

geo.geography_element7 parent_region_name_6,

geo.geography_element8 parent_region_name_7,

geo.geography_element9 parent_region_name_8,

mrsld.zone_id,

zon.geography_name zone_name,

zon.geography_type zone_type,

mrsld.mrsl_days

FROM MSC_SRC_MRSL_RULES_VL mrsl,

MSC_SRC_MRSL_RULE_DETAILS mrsld,

inv_org_parameters org,

hz_parties cust,

hz_party_sites custsite,

egp_categories_vl cat,

fnd_lookup_values_vl dmdclass,

hz_geographies geo,

hz_geographies zon

WHERE mrsld.mrsl_rule_id = mrsl.mrsl_rule_id

AND org.organization_id(+) = mrsld.organization_id

AND cust.party_id(+) = mrsld.customer_id

AND custsite.party_site_id(+) = mrsld.customer_site_id

AND cat.category_id(+) = mrsld.category_id

AND dmdclass.lookup_type(+) = 'ORA_DOO_DEMAND_CLASS'

AND dmdclass.lookup_code(+) = mrsld.demand_class

AND geo.geography_id(+) = mrsld.geography_id

AND zon.geography_id(+) = mrsld.zone_id