ZX_RULES_EVALUATION

Details

Schema: FUSION
Object owner: FND
Object type: VIEW

Columns

Name
TAX_RULE_ID TAX_RULE_CODE CONTENT_OWNER_ID CONFIGURATION_OWNER_NAME TAX_REGIME_CODE TAX SERVICE_TYPE_CODE LOCATION_TYPE DET_FACTOR_TEMPL_CODE RULE_EFFECTIVE_FROM RULE_EFFECTIVE_TO EVENT_CLASS GEOGRAPHY_NAME GEOGRAPHY_TYPE APPLICATION RULE_ORDER RECOVERY_TYPE EVENT_CLASS_CATEGORY PARENT_GEOGRAPHY_TYPE PARENT_GEOGRAPHY_NAME ENABLED_FLAG FIRST_PARTY_ORG_ID SUBSCRIPTION_EFFECTIVE_FROM SUBSCRIPTION_EFFECTIVE_TO EVALUATION_PRIORITY_ORDER TAX_STATUS_CODE

Name

TAX_RULE_ID

TAX_RULE_CODE

CONTENT_OWNER_ID

CONFIGURATION_OWNER_NAME

TAX_REGIME_CODE

TAX

SERVICE_TYPE_CODE

LOCATION_TYPE

DET_FACTOR_TEMPL_CODE

RULE_EFFECTIVE_FROM

RULE_EFFECTIVE_TO

EVENT_CLASS

GEOGRAPHY_NAME

GEOGRAPHY_TYPE

APPLICATION

RULE_ORDER

RECOVERY_TYPE

EVENT_CLASS_CATEGORY

PARENT_GEOGRAPHY_TYPE

PARENT_GEOGRAPHY_NAME

ENABLED_FLAG

FIRST_PARTY_ORG_ID

SUBSCRIPTION_EFFECTIVE_FROM

SUBSCRIPTION_EFFECTIVE_TO

EVALUATION_PRIORITY_ORDER

TAX_STATUS_CODE

Query

SQL_Statement
SELECT r.tax_rule_id, r.tax_rule_code, r.content_owner_id, CASE WHEN r.party_type_code ='OU' THEN (SELECT bu.bu_name FROM FUN_ALL_BUSINESS_UNITS_V bu WHERE bu.bu_id = r.party_id) WHEN r.party_type_code ='FIRST_PARTY' THEN (SELECT xlep.name FROM xle_entity_profiles xlep WHERE xlep.party_id = r.party_id) WHEN r.party_type_code = 'GCO' THEN (SELECT co.meaning FROM fnd_lookups co WHERE co.lookup_type = 'ZX_PTP_PARTY_TYPE' AND co.lookup_code = 'GCO') END configuration_owner_name, r.tax_regime_code, r.tax, r.service_type_code, CASE WHEN r.location_type_code IS NOT NULL THEN (SELECT lkp2.meaning FROM fnd_lookups lkp2 WHERE lkp2.lookup_type = 'ZX_PLACE_OF_SUPPLY_TYPE' AND lkp2.lookup_code = r.location_type_code) END location_type, r.det_factor_templ_code, r.rule_effective_from, r.rule_effective_to, CASE WHEN r.event_class_category_code = 'EVENT_CLASS' THEN (SELECT appevent.name FROM xla_event_classes_tl appevent, zx_evnt_cls_mappings emap WHERE appevent.language = userenv('LANG') AND appevent.application_id = emap.application_id AND appevent.entity_code = emap.entity_code AND appevent.event_class_code = emap.event_class_code AND emap.event_class_mapping_id = r.event_class_mapping_id) WHEN r.event_class_category_code = 'TAX_EVENT_CLASS' THEN (SELECT tevent.tax_event_class_name FROM zx_event_classes_vl tevent WHERE tevent.tax_event_class_code =r.tax_event_class_code) END event_class, CASE WHEN r.geography_id IS NOT NULL THEN (SELECT g.geography_name FROM HZ_GEOGRAPHIES g WHERE g.geography_id = r.geography_id) END geography_name, r.geography_type, CASE WHEN r.application_id IS NOT NULL THEN (SELECT a.application_name FROM FND_APPLICATION_VL a WHERE a.application_id = r.application_id) END application, r.rule_order, r.recovery_type_code recovery_type, CASE WHEN r.event_class_category_code IS NOT NULL THEN (SELECT lkp1.meaning FROM fnd_lookups lkp1 WHERE lkp1.lookup_type = 'ZX_EVENT_CLASS_CATEGORY' AND lkp1.lookup_code = r.event_class_category_code) END event_class_category, r.parent_geography_type, CASE WHEN r.parent_geography_id IS NOT NULL THEN (SELECT pg.geography_name FROM HZ_GEOGRAPHIES pg WHERE pg.geography_id = r.parent_geography_id) END parent_geography_name, r.enabled_flag, sd.first_pty_org_id FIRST_PARTY_ORG_ID, sd.effective_from SUBSCRIPTION_EFFECTIVE_FROM, sd.effective_to SUBSCRIPTION_EFFECTIVE_TO, RANK() OVER (PARTITION BY sd.first_pty_org_id, r.tax_regime_code, r.tax, r.service_type_code ORDER BY r.evaluation_precedence, r.rule_order) evaluation_priority_order ,r.tax_status_code FROM (SELECT rule.tax_rule_id, rule.tax_rule_code, rule.content_owner_id, ptp.party_type_code, ptp.party_id, rule.tax_regime_code, rule.tax, rule.service_type_code, rule.determining_factor_cq_code location_type_code, rule.det_factor_templ_code, rule.effective_from rule_effective_from, rule.effective_to rule_effective_to, rule.tax_event_class_code, rule.event_class_mapping_id, rule.geography_id, rule.geography_type, rule.application_id, rule.priority rule_order, rule.recovery_type_code, CASE WHEN rule.event_class_mapping_id IS NOT NULL THEN 'EVENT_CLASS' WHEN (rule.event_class_mapping_id IS NULL and rule.tax_event_class_code IS NOT NULL) THEN 'TAX_EVENT_CLASS' END event_class_category_code, CASE WHEN geography_id IS NOT NULL THEN (SELECT gh1.parent_object_type FROM HZ_HIERARCHY_NODES gh1 WHERE gh1.child_id = rule.geography_id AND gh1.child_object_type = rule.geography_type AND gh1.level_number = 1 AND gh1.hierarchy_type = 'MASTER_REF') END parent_geography_type, CASE WHEN geography_id IS NOT NULL THEN (SELECT gh1.parent_id FROM HZ_HIERARCHY_NODES gh1 WHERE gh1.child_id = rule.geography_id AND gh1.child_object_type = rule.geography_type AND gh1.level_number = 1 AND gh1.hierarchy_type = 'MASTER_REF') END parent_geography_id, CASE WHEN rule.event_class_mapping_id IS NOT NULL THEN 1 WHEN (rule.event_class_mapping_id IS NULL and rule.tax_event_class_code IS NOT NULL) THEN 2 ELSE 3 END evaluation_precedence ,rule.enabled_flag ,rule.tax_status_code FROM zx_rules_b rule, zx_party_tax_profile ptp WHERE ptp.party_tax_profile_id = rule.content_owner_id) r, zx_subscription_details sd ,zx_subscription_options so WHERE r.tax_regime_code = sd.tax_regime_code AND r.content_owner_id = sd.parent_first_pty_org_id AND sd.view_options_code IN ('NONE','VFC','VFR') AND sd.subscription_option_id = so.subscription_option_id (+) AND (so.enabled_flag = 'Y' or sd.subscription_option_id = 0) ORDER BY sd.first_pty_org_id, r.tax_regime_code, r.tax, r.service_type_code, r.evaluation_precedence, r.rule_order

SQL_Statement

SELECT

r.tax_rule_id,

r.tax_rule_code,

r.content_owner_id,

CASE WHEN r.party_type_code ='OU' THEN

(SELECT bu.bu_name FROM FUN_ALL_BUSINESS_UNITS_V bu WHERE bu.bu_id = r.party_id)

WHEN r.party_type_code ='FIRST_PARTY' THEN

(SELECT xlep.name FROM xle_entity_profiles xlep

WHERE xlep.party_id = r.party_id)

WHEN r.party_type_code = 'GCO' THEN

(SELECT co.meaning FROM fnd_lookups co

WHERE co.lookup_type = 'ZX_PTP_PARTY_TYPE'

AND co.lookup_code = 'GCO')

END configuration_owner_name,

r.tax_regime_code,

r.tax,

r.service_type_code,

CASE WHEN r.location_type_code IS NOT NULL THEN

(SELECT lkp2.meaning FROM fnd_lookups lkp2

WHERE lkp2.lookup_type = 'ZX_PLACE_OF_SUPPLY_TYPE'

AND lkp2.lookup_code = r.location_type_code)

END location_type,

r.det_factor_templ_code,

r.rule_effective_from,

r.rule_effective_to,

CASE WHEN r.event_class_category_code = 'EVENT_CLASS' THEN

(SELECT appevent.name

FROM xla_event_classes_tl appevent, zx_evnt_cls_mappings emap

WHERE appevent.language = userenv('LANG')

AND appevent.application_id = emap.application_id

AND appevent.entity_code = emap.entity_code

AND appevent.event_class_code = emap.event_class_code

AND emap.event_class_mapping_id = r.event_class_mapping_id)

WHEN r.event_class_category_code = 'TAX_EVENT_CLASS' THEN

(SELECT tevent.tax_event_class_name FROM zx_event_classes_vl tevent

WHERE tevent.tax_event_class_code =r.tax_event_class_code)

END event_class,

CASE WHEN r.geography_id IS NOT NULL THEN

(SELECT g.geography_name FROM HZ_GEOGRAPHIES g

WHERE g.geography_id = r.geography_id)

END geography_name,

r.geography_type,

CASE WHEN r.application_id IS NOT NULL THEN

(SELECT a.application_name FROM FND_APPLICATION_VL a

WHERE a.application_id = r.application_id)

END application,

r.rule_order,

r.recovery_type_code recovery_type,

CASE WHEN r.event_class_category_code IS NOT NULL THEN

(SELECT lkp1.meaning FROM fnd_lookups lkp1

WHERE lkp1.lookup_type = 'ZX_EVENT_CLASS_CATEGORY'

AND lkp1.lookup_code = r.event_class_category_code)

END event_class_category,

r.parent_geography_type,

CASE WHEN r.parent_geography_id IS NOT NULL THEN

(SELECT pg.geography_name FROM HZ_GEOGRAPHIES pg

WHERE pg.geography_id = r.parent_geography_id)

END parent_geography_name,

r.enabled_flag,

sd.first_pty_org_id FIRST_PARTY_ORG_ID,

sd.effective_from SUBSCRIPTION_EFFECTIVE_FROM,

sd.effective_to SUBSCRIPTION_EFFECTIVE_TO,

RANK() OVER (PARTITION BY sd.first_pty_org_id, r.tax_regime_code, r.tax, r.service_type_code

ORDER BY r.evaluation_precedence, r.rule_order) evaluation_priority_order

,r.tax_status_code

FROM

(SELECT

rule.tax_rule_id,

rule.tax_rule_code,

rule.content_owner_id,

ptp.party_type_code,

ptp.party_id,

rule.tax_regime_code,

rule.tax,

rule.service_type_code,

rule.determining_factor_cq_code location_type_code,

rule.det_factor_templ_code,

rule.effective_from rule_effective_from,

rule.effective_to rule_effective_to,

rule.tax_event_class_code,

rule.event_class_mapping_id,

rule.geography_id,

rule.geography_type,

rule.application_id,

rule.priority rule_order,

rule.recovery_type_code,

CASE WHEN rule.event_class_mapping_id IS NOT NULL THEN 'EVENT_CLASS'

WHEN (rule.event_class_mapping_id IS NULL and rule.tax_event_class_code IS NOT NULL) THEN 'TAX_EVENT_CLASS'

END event_class_category_code,

CASE WHEN geography_id IS NOT NULL THEN

(SELECT gh1.parent_object_type FROM HZ_HIERARCHY_NODES gh1

WHERE gh1.child_id = rule.geography_id

AND gh1.child_object_type = rule.geography_type

AND gh1.level_number = 1

AND gh1.hierarchy_type = 'MASTER_REF')

END parent_geography_type,

CASE WHEN geography_id IS NOT NULL THEN

(SELECT gh1.parent_id FROM HZ_HIERARCHY_NODES gh1

WHERE gh1.child_id = rule.geography_id

AND gh1.child_object_type = rule.geography_type

AND gh1.level_number = 1

AND gh1.hierarchy_type = 'MASTER_REF')

END parent_geography_id,

CASE WHEN rule.event_class_mapping_id IS NOT NULL THEN 1

WHEN (rule.event_class_mapping_id IS NULL and rule.tax_event_class_code IS NOT NULL) THEN 2

ELSE 3

END evaluation_precedence

,rule.enabled_flag

,rule.tax_status_code

FROM zx_rules_b rule,

zx_party_tax_profile ptp

WHERE ptp.party_tax_profile_id = rule.content_owner_id) r,

zx_subscription_details sd

,zx_subscription_options so

WHERE r.tax_regime_code = sd.tax_regime_code

AND r.content_owner_id = sd.parent_first_pty_org_id

AND sd.view_options_code IN ('NONE','VFC','VFR')

AND sd.subscription_option_id = so.subscription_option_id (+)

AND (so.enabled_flag = 'Y' or sd.subscription_option_id = 0)

ORDER BY sd.first_pty_org_id, r.tax_regime_code, r.tax, r.service_type_code, r.evaluation_precedence, r.rule_order