ZX_ROUNDING_LVL_HIER

Details

Schema: FUSION
Object owner: ZX
Object type: VIEW

Columns

Name
ROUNDINGPRECEDENCE PROD_FAMILY_GRP_CODE PARTYTYPE SOURCECODE SOURCE SOURCEPRECEDENCE ROUNDINGLEVEL ROUNDINGRULE PROCESSFORAPPLICABILITYFLAG APPLICATION_ID ENTITY_CODE EVENT_CLASS_CODE TRX_ID TRX_LINE_ID TRX_LEVEL_TYPE

Name

ROUNDINGPRECEDENCE

PROD_FAMILY_GRP_CODE

PARTYTYPE

SOURCECODE

SOURCE

SOURCEPRECEDENCE

ROUNDINGLEVEL

ROUNDINGRULE

PROCESSFORAPPLICABILITYFLAG

APPLICATION_ID

ENTITY_CODE

EVENT_CLASS_CODE

TRX_ID

TRX_LINE_ID

TRX_LEVEL_TYPE

Query

SQL_Statement
select 1 RoundingPrecedence, mappings.PROD_FAMILY_GRP_CODE, ptylkp.meaning PartyType, source.lookup_code SourceCode, source.meaning Source, decode(source.lookup_code,'SUPP_SITE',1,'CUST_SITE',1,'CUST',2,'PTP',3,'DEFAULT',4) SourcePrecedence, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.bill_third_pty_acct_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.ship_third_pty_acct_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.bill_to_cust_acct_site_use_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.ship_to_cust_acct_site_use_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END RoundingLevel, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.bill_third_pty_acct_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.ship_third_pty_acct_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.bill_to_cust_acct_site_use_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.ship_to_cust_acct_site_use_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END RoundingRule, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code IN ('CUST','CUST_SITE') THEN NULL WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END ProcessForApplicabilityFlag, lines.application_id, lines.entity_code, lines.event_class_code, lines.trx_id, lines.trx_line_id, lines.trx_level_type from zx_evnt_cls_OPTIONS options, zx_lines_det_factors lines, zx_evnt_cls_mappings mappings, fnd_lookups ptylkp, fnd_lookups source WHERE options.application_id (+) = lines.application_id AND options.entity_code (+) = lines.ENTITY_CODE AND options.event_class_code (+) = lines.event_class_code AND options.first_pty_org_id (+) = lines.first_pty_org_id AND lines.trx_date >= options.EFFECTIVE_FROM (+) and lines.trx_date <= nvl(options.EFFECTIVE_TO (+),lines.trx_date) AND options.enabled_flag (+) = 'Y' AND mappings.EVENT_CLASS_MAPPING_ID = lines.EVENT_CLASS_MAPPING_ID AND source.lookup_type = 'ZX_ROUND_LEVEL_SOURCE' AND ((source.lookup_code IN ('SUPP_SITE','PTP') and mappings.prod_family_grp_code = 'P2P') OR (source.lookup_code IN ('CUST','CUST_SITE','PTP') and mappings.prod_family_grp_code = 'O2C')) AND ptylkp.lookup_type = 'ZX_ROUND_LEVEL_HIER' AND ptylkp.lookup_code = decode(options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) AND NVL(mappings.INDIRECT_TAX_OR_WHT_FLAG,'I')='I' AND (options.INDIRECT_TAX_OR_WHT_FLAG(+) is null OR options.INDIRECT_TAX_OR_WHT_FLAG(+) = 'I') UNION select 2 RoundingPrecedence, mappings.PROD_FAMILY_GRP_CODE, ptylkp.meaning PartyType, source.lookup_code SourceCode, source.meaning Source, decode(source.lookup_code,'SUPP_SITE',1,'CUST_SITE',1,'CUST',2,'PTP',3,'DEFAULT',4) SourcePrecedence, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.bill_third_pty_acct_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.ship_third_pty_acct_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.bill_to_cust_acct_site_use_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.ship_to_cust_acct_site_use_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END RoundingLevel, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.bill_third_pty_acct_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.ship_third_pty_acct_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.bill_to_cust_acct_site_use_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.ship_to_cust_acct_site_use_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END RoundingRule, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code IN ('CUST', 'CUST_SITE') THEN NULL WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END ProcessForApplicabilityFlag, lines.application_id, lines.entity_code, lines.event_class_code, lines.trx_id, lines.trx_line_id, lines.trx_level_type from zx_evnt_cls_OPTIONS options, zx_lines_det_factors lines, zx_evnt_cls_mappings mappings, fnd_lookups ptylkp, fnd_lookups source WHERE options.application_id (+) = lines.application_id AND options.entity_code (+) = lines.ENTITY_CODE AND options.event_class_code (+) = lines.event_class_code AND options.first_pty_org_id (+) = lines.first_pty_org_id AND lines.trx_date >= options.EFFECTIVE_FROM (+) and lines.trx_date <= nvl(options.EFFECTIVE_TO (+),lines.trx_date) AND options.enabled_flag (+) = 'Y' AND mappings.EVENT_CLASS_MAPPING_ID = lines.EVENT_CLASS_MAPPING_ID AND source.lookup_type = 'ZX_ROUND_LEVEL_SOURCE' AND ((source.lookup_code IN ('SUPP_SITE','PTP') and mappings.prod_family_grp_code = 'P2P') OR (source.lookup_code IN ('CUST','CUST_SITE','PTP') and mappings.prod_family_grp_code = 'O2C')) AND ptylkp.lookup_type = 'ZX_ROUND_LEVEL_HIER' AND ptylkp.lookup_code = decode(options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) AND NVL(mappings.INDIRECT_TAX_OR_WHT_FLAG,'I')='I' AND (options.INDIRECT_TAX_OR_WHT_FLAG(+) is null OR options.INDIRECT_TAX_OR_WHT_FLAG(+) = 'I') UNION select 3 RoundingPrecedence, mappings.PROD_FAMILY_GRP_CODE, ptylkp.meaning PartyType, source.lookup_code SourceCode, source.meaning Source, decode(source.lookup_code,'SUPP_SITE',1,'CUST_SITE',1,'CUST',2,'PTP',3,'DEFAULT',4) SourcePrecedence, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.bill_third_pty_acct_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.ship_third_pty_acct_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.bill_to_cust_acct_site_use_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.ship_to_cust_acct_site_use_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END RoundingLevel, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.bill_third_pty_acct_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.ship_third_pty_acct_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.bill_to_cust_acct_site_use_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.ship_to_cust_acct_site_use_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END RoundingRule, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code IN ('CUST','CUST_SITE') THEN NULL WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END ProcessForApplicabilityFlag, lines.application_id, lines.entity_code, lines.event_class_code, lines.trx_id, lines.trx_line_id, lines.trx_level_type from zx_evnt_cls_OPTIONS options, zx_lines_det_factors lines, zx_evnt_cls_mappings mappings, fnd_lookups ptylkp, fnd_lookups source WHERE options.application_id (+) = lines.application_id AND options.entity_code (+) = lines.ENTITY_CODE AND options.event_class_code (+) = lines.event_class_code AND options.first_pty_org_id (+) = lines.first_pty_org_id AND lines.trx_date >= options.EFFECTIVE_FROM (+) and lines.trx_date <= nvl(options.EFFECTIVE_TO (+),lines.trx_date) AND options.enabled_flag (+) = 'Y' AND mappings.EVENT_CLASS_MAPPING_ID = lines.EVENT_CLASS_MAPPING_ID AND source.lookup_type = 'ZX_ROUND_LEVEL_SOURCE' AND ((source.lookup_code IN ('SUPP_SITE','PTP') and mappings.prod_family_grp_code = 'P2P') OR (source.lookup_code IN ('CUST','CUST_SITE','PTP') and mappings.prod_family_grp_code = 'O2C')) AND ptylkp.lookup_type = 'ZX_ROUND_LEVEL_HIER' AND ptylkp.lookup_code = decode(options. rounding_level_hier_3_code, NULL, mappings. rounding_level_hier_3_code, options. rounding_level_hier_3_code) AND NVL(mappings.INDIRECT_TAX_OR_WHT_FLAG,'I')='I' AND (options.INDIRECT_TAX_OR_WHT_FLAG(+) is null OR options.INDIRECT_TAX_OR_WHT_FLAG(+) = 'I') UNION select 4 RoundingPrecedence, mappings.PROD_FAMILY_GRP_CODE, ptylkp.meaning PartyType, source.lookup_code SourceCode, source.meaning Source, decode(source.lookup_code,'SUPP_SITE',1,'CUST_SITE',1,'CUST',2,'PTP',3,'DEFAULT',4) SourcePrecedence, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.bill_third_pty_acct_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.ship_third_pty_acct_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.bill_to_cust_acct_site_use_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.ship_to_cust_acct_site_use_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END RoundingLevel, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.bill_third_pty_acct_id) WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.ship_third_pty_acct_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.bill_to_cust_acct_site_use_id) WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.ship_to_cust_acct_site_use_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END RoundingRule, CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id) WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options.rounding_level_hier_4_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN (select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id) WHEN source.lookup_code IN ('CUST', 'CUST_SITE') THEN NULL WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'BILL_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_FROM_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_FROM_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_TO_PTY' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id) WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_4_code, NULL, mappings.rounding_level_hier_4_code, options. rounding_level_hier_4_code) = 'SHIP_TO_PTY_SITE' THEN (select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id) END ProcessForApplicabilityFlag, lines.application_id, lines.entity_code, lines.event_class_code, lines.trx_id, lines.trx_line_id, lines.trx_level_type from zx_evnt_cls_OPTIONS options, zx_lines_det_factors lines, zx_evnt_cls_mappings mappings, fnd_lookups ptylkp, fnd_lookups source WHERE options.application_id (+) = lines.application_id AND options.entity_code (+) = lines.ENTITY_CODE AND options.event_class_code (+) = lines.event_class_code AND options.first_pty_org_id (+) = lines.first_pty_org_id AND lines.trx_date >= options.EFFECTIVE_FROM (+) and lines.trx_date <= nvl(options.EFFECTIVE_TO (+),lines.trx_date) AND options.enabled_flag (+) = 'Y' AND mappings.EVENT_CLASS_MAPPING_ID = lines.EVENT_CLASS_MAPPING_ID AND source.lookup_type = 'ZX_ROUND_LEVEL_SOURCE' AND ((source.lookup_code IN ('SUPP_SITE','PTP') and mappings.prod_family_grp_code = 'P2P') OR (source.lookup_code IN ('CUST','CUST_SITE','PTP') and mappings.prod_family_grp_code = 'O2C')) AND ptylkp.lookup_type = 'ZX_ROUND_LEVEL_HIER' AND ptylkp.lookup_code = decode(options. rounding_level_hier_4_code, NULL, mappings. rounding_level_hier_4_code, options. rounding_level_hier_4_code) AND NVL(mappings.INDIRECT_TAX_OR_WHT_FLAG,'I')='I' AND (options.INDIRECT_TAX_OR_WHT_FLAG(+) is null OR options.INDIRECT_TAX_OR_WHT_FLAG(+) = 'I') UNION select 5 RoundingPrecedence, mappings.PROD_FAMILY_GRP_CODE, NULL PartyType, source.lookup_code SourceCode, source.meaning Source, 4 SourcePrecedence, roundlkp.meaning RoundingLevel, NULL RoundingRule, decode(lkp.lookup_code, 'Y', lkp.meaning, NULL) ProcessForApplicabilityFlag, lines.application_id, lines.entity_code, lines.event_class_code, lines.trx_id, lines.trx_line_id, lines.trx_level_type from zx_evnt_cls_OPTIONS options, zx_lines_det_factors lines, zx_evnt_cls_mappings mappings, fnd_lookups roundlkp, fnd_lookups source, fnd_lookups lkp WHERE options.application_id (+) = lines.application_id AND options.entity_code (+) = lines.ENTITY_CODE AND options.event_class_code (+) = lines.event_class_code AND options.first_pty_org_id (+) = lines.first_pty_org_id AND lines.trx_date >= options.EFFECTIVE_FROM (+) and lines.trx_date <= nvl(options.EFFECTIVE_TO (+),lines.trx_date) AND options.enabled_flag (+) = 'Y' AND mappings.EVENT_CLASS_MAPPING_ID = lines.EVENT_CLASS_MAPPING_ID AND source.lookup_type = 'ZX_ROUND_LEVEL_SOURCE' AND source.lookup_code = 'DEFAULT' AND roundlkp.lookup_type = 'ZX_ROUNDING_LEVEL' AND roundlkp.lookup_code = decode(options.DEFAULT_ROUNDING_LEVEL_CODE, NULL, mappings.DEFAULT_ROUNDING_LEVEL_CODE, options.DEFAULT_ROUNDING_LEVEL_CODE) AND lkp.lookup_type = 'YES_NO' AND lkp.lookup_code = decode(options.process_for_applicability_flag, NULL, mappings.process_for_applicability_flag, options.process_for_applicability_flag) AND NVL(mappings.INDIRECT_TAX_OR_WHT_FLAG,'I')='I' AND (options.INDIRECT_TAX_OR_WHT_FLAG(+) is null OR options.INDIRECT_TAX_OR_WHT_FLAG(+) = 'I') order by RoundingPrecedence, SourcePrecedence

SQL_Statement

select 1 RoundingPrecedence,

mappings.PROD_FAMILY_GRP_CODE,

ptylkp.meaning PartyType,

source.lookup_code SourceCode,

source.meaning Source,

decode(source.lookup_code,'SUPP_SITE',1,'CUST_SITE',1,'CUST',2,'PTP',3,'DEFAULT',4) SourcePrecedence,

CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id)

WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id)

WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.bill_third_pty_acct_id)

WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.ship_third_pty_acct_id)

WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.bill_to_cust_acct_site_use_id)

WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.ship_to_cust_acct_site_use_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id)

END RoundingLevel,

CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id)

WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id)

WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.bill_third_pty_acct_id)

WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.cust_account_id = lines.ship_third_pty_acct_id)

WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.bill_to_cust_acct_site_use_id)

WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = caa.tax_rounding_rule and caa.site_use_id = lines.ship_to_cust_acct_site_use_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_RULE' and lkp.lookup_code = ptp.rounding_rule_code and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id)

END RoundingRule,

CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id)

WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(povs.auto_tax_calc_flag,'Y'),'N',NULL,'Y') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id)

WHEN source.lookup_code IN ('CUST','CUST_SITE') THEN

NULL

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_FROM_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'BILL_TO_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_FROM_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code) = 'SHIP_TO_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'YES_NO' and lkp.lookup_code = decode(nvl(ptp.process_for_applicability_flag,'Y'),'N', NULL, 'Y') and ptp.party_tax_profile_id = lines.ship_to_site_tax_prof_id)

END ProcessForApplicabilityFlag,

lines.application_id,

lines.entity_code,

lines.event_class_code,

lines.trx_id,

lines.trx_line_id,

lines.trx_level_type

from zx_evnt_cls_OPTIONS options, zx_lines_det_factors lines, zx_evnt_cls_mappings mappings, fnd_lookups ptylkp, fnd_lookups source

WHERE options.application_id (+) = lines.application_id

AND options.entity_code (+) = lines.ENTITY_CODE

AND options.event_class_code (+) = lines.event_class_code

AND options.first_pty_org_id (+) = lines.first_pty_org_id

AND lines.trx_date >= options.EFFECTIVE_FROM (+) and lines.trx_date <= nvl(options.EFFECTIVE_TO (+),lines.trx_date)

AND options.enabled_flag (+) = 'Y'

AND mappings.EVENT_CLASS_MAPPING_ID = lines.EVENT_CLASS_MAPPING_ID

AND source.lookup_type = 'ZX_ROUND_LEVEL_SOURCE'

AND ((source.lookup_code IN ('SUPP_SITE','PTP') and mappings.prod_family_grp_code = 'P2P') OR (source.lookup_code IN ('CUST','CUST_SITE','PTP') and mappings.prod_family_grp_code = 'O2C'))

AND ptylkp.lookup_type = 'ZX_ROUND_LEVEL_HIER'

AND ptylkp.lookup_code = decode(options. rounding_level_hier_1_code, NULL, mappings. rounding_level_hier_1_code, options. rounding_level_hier_1_code)

AND NVL(mappings.INDIRECT_TAX_OR_WHT_FLAG,'I')='I'

AND (options.INDIRECT_TAX_OR_WHT_FLAG(+) is null OR options.INDIRECT_TAX_OR_WHT_FLAG(+) = 'I')

UNION

select 2 RoundingPrecedence,

mappings.PROD_FAMILY_GRP_CODE,

ptylkp.meaning PartyType,

source.lookup_code SourceCode,

source.meaning Source,

decode(source.lookup_code,'SUPP_SITE',1,'CUST_SITE',1,'CUST',2,'PTP',3,'DEFAULT',4) SourcePrecedence,

CASE WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_FROM_PTY', 'BILL_FROM_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.bill_third_pty_acct_id and povs.vendor_site_id = lines.bill_third_pty_acct_site_id)

WHEN source.lookup_code = 'SUPP_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_FROM_PTY', 'SHIP_FROM_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, poz_supplier_sites_all_m povs where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(povs.auto_tax_calc_flag,'L','LINE','H','HEADER','T','HEADER') and povs.vendor_id = lines.ship_third_pty_acct_id and povs.vendor_site_id = lines.ship_third_pty_acct_site_id)

WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.bill_third_pty_acct_id)

WHEN source.lookup_code = 'CUST' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_accounts caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.cust_account_id = lines.ship_third_pty_acct_id)

WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('BILL_TO_PTY', 'BILL_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.bill_to_cust_acct_site_use_id)

WHEN source.lookup_code = 'CUST_SITE' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) IN ('SHIP_TO_PTY', 'SHIP_TO_PTY_SITE') THEN

(select lkp.meaning from fnd_lookups lkp, hz_cust_site_uses_all caa where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = decode(caa.tax_header_level_flag,'Y','HEADER','LINE') and caa.site_use_id = lines.ship_to_cust_acct_site_use_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_FROM_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_FROM_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_from_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_TO_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'BILL_TO_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.bill_to_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_FROM_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_FROM_PTY_SITE' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_from_site_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_TO_PTY' THEN

(select lkp.meaning from fnd_lookups lkp, zx_party_tax_profile ptp where lkp.lookup_type = 'ZX_ROUNDING_LEVEL' and lkp.lookup_code = ptp.rounding_level_code and ptp.party_tax_profile_id = lines.ship_to_party_tax_prof_id)

WHEN source.lookup_code = 'PTP' and decode (options. rounding_level_hier_2_code, NULL, mappings. rounding_level_hier_2_code, options. rounding_level_hier_2_code) = 'SHIP_TO_PTY_SITE' THEN