DBA Data[Home] [Help]

VIEW: APPS.JL_ZZ_AR_TX_GROUPS_V

Source

View Text - Preformatted

SELECT tgr.rowid, tgr.group_tax_id, tgr.tax_category_id, tgr.tax_group_record_id, cat.tax_category, tgr.establishment_type, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tgr.establishment_type, 'JLZZ_AR_TX_ATTR_VALUE'),1,80), tgr.contributor_type, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tgr.contributor_type, 'JLZZ_AR_TX_ATTR_VALUE'),1,80), tgr.transaction_nature, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tgr.transaction_nature, 'JLZZ_AR_TX_ATTR_VALUE'),1,80), cat.org_tax_attribute, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning (cat.org_tax_attribute, 'ORGANIZATION_ATTRIBUTE'),1,80), cat.cus_tax_attribute, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(cat.cus_tax_attribute, 'CONTRIBUTOR_ATTRIBUTE'),1,80), cat.txn_tax_attribute, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(cat.txn_tax_attribute, 'TRANSACTION_ATTRIBUTE'),1,80), tgr.end_date_active, tgr.use_tx_categ_thresholds, tgr.last_update_date, tgr.last_updated_by, tgr.compound_precedence, tgr.min_amount, tgr.min_taxable_basis, tgr.min_percentage, tgr.tax_inclusive, tgr.tributary_substitution, tgr.used_to_reduce, tgr.tax_code, art.description, art.tax_rate, tgr.base_rate, tgr.start_date_active, tgr.org_id, tgr.last_update_login, tgr.creation_date, tgr.created_by, tgr.tax_category_to_reduce_id, cat_reduce.tax_category, tgr.calculate_in_oe, tgr.attribute_category, tgr.attribute1, tgr.attribute2, tgr.attribute3, tgr.attribute4, tgr.attribute5, tgr.attribute6, tgr.attribute7, tgr.attribute8, tgr.attribute9, tgr.attribute10, tgr.attribute11, tgr.attribute12, tgr.attribute13, tgr.attribute14, tgr.attribute15 FROM jl_zz_ar_tx_groups tgr, jl_zz_ar_tx_categ_all cat_reduce, jl_zz_ar_tx_categ_all cat, ar_vat_tax_all_b art, ar_system_parameters_all sys WHERE tgr.tax_category_id = cat.tax_category_id AND cat.tax_rule_set = sys.global_attribute13 AND nvl(cat_reduce.tax_rule_set,sys.global_attribute13) = sys.global_attribute13 AND tgr.tax_code is not null AND tgr.tax_code = art.tax_code AND art.set_of_books_id = sys.set_of_books_id AND NVL(art.tax_type, 'VAT') = 'VAT' AND NVL(art.tax_class, 'O') = 'O' AND NVL(art.enabled_flag, 'Y') = 'Y' AND NVL(tgr.org_id,-99) = NVL(cat.org_id,-99) AND NVL(tgr.org_id,-99) = NVL(art.org_id,-99) AND NVL(tgr.org_id,-99) = NVL(sys.org_id,-99) AND tgr.tax_category_id = decode(ltrim(art.global_attribute1,'0123456789 '), null, to_number(art.global_attribute1), null) AND tgr.start_date_active <= NVL(art.end_date, to_date('4712/12/31','YYYY/MM/DD')) AND tgr.end_date_active >= art.start_date AND NOT EXISTS (select 1 from ar_vat_tax art1 where art1.tax_code = art.tax_code and art1.set_of_books_id = art.set_of_books_id and nvl(art1.tax_type, 'VAT') = 'VAT' and nvl(art1.tax_class, 'O') = 'O' and nvl(art1.enabled_flag, 'Y') = 'Y' and art1.global_attribute1 = art.global_attribute1 and art1.start_date > art.start_date and art1.start_date <= tgr.end_date_active) AND tgr.tax_category_to_reduce_id = cat_reduce.tax_category_id(+) UNION SELECT tgr.rowid, tgr.group_tax_id, tgr.tax_category_id, tgr.tax_group_record_id, cat.tax_category, tgr.establishment_type, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tgr.establishment_type, 'JLZZ_AR_TX_ATTR_VALUE'),1,80), tgr.contributor_type, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tgr.contributor_type, 'JLZZ_AR_TX_ATTR_VALUE'),1,80), tgr.transaction_nature, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(tgr.transaction_nature, 'JLZZ_AR_TX_ATTR_VALUE'),1,80), cat.org_tax_attribute, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning (cat.org_tax_attribute, 'ORGANIZATION_ATTRIBUTE'),1,80), cat.cus_tax_attribute, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(cat.cus_tax_attribute, 'CONTRIBUTOR_ATTRIBUTE'),1,80), cat.txn_tax_attribute, substr(JL_ZZ_AR_TX_LIB_PKG.get_lookup_meaning(cat.txn_tax_attribute, 'TRANSACTION_ATTRIBUTE'),1,80), tgr.end_date_active, tgr.use_tx_categ_thresholds, tgr.last_update_date, tgr.last_updated_by, tgr.compound_precedence, tgr.min_amount, tgr.min_taxable_basis, tgr.min_percentage, tgr.tax_inclusive, tgr.tributary_substitution, tgr.used_to_reduce, tgr.tax_code, to_char(NULL) description, to_number(NULL) tax_rate, tgr.base_rate, tgr.start_date_active, tgr.org_id, tgr.last_update_login, tgr.creation_date, tgr.created_by, tgr.tax_category_to_reduce_id, cat_reduce.tax_category, tgr.calculate_in_oe, tgr.attribute_category, tgr.attribute1, tgr.attribute2, tgr.attribute3, tgr.attribute4, tgr.attribute5, tgr.attribute6, tgr.attribute7, tgr.attribute8, tgr.attribute9, tgr.attribute10, tgr.attribute11, tgr.attribute12, tgr.attribute13, tgr.attribute14, tgr.attribute15 FROM jl_zz_ar_tx_groups tgr, jl_zz_ar_tx_categ cat_reduce, jl_zz_ar_tx_categ_all cat, ar_system_parameters_all sys WHERE tgr.tax_category_id = cat.tax_category_id AND cat.tax_rule_set = sys.global_attribute13 AND nvl(cat_reduce.tax_rule_set,sys.global_attribute13) = sys.global_attribute13 AND tgr.tax_category_to_reduce_id = cat_reduce.tax_category_id(+) AND tgr.tax_code is null AND NVL(tgr.org_id,-99) = NVL(cat.org_id,-99) AND NVL(tgr.org_id,-99) = NVL(sys.org_id,-99)
View Text - HTML Formatted

SELECT TGR.ROWID
, TGR.GROUP_TAX_ID
, TGR.TAX_CATEGORY_ID
, TGR.TAX_GROUP_RECORD_ID
, CAT.TAX_CATEGORY
, TGR.ESTABLISHMENT_TYPE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(TGR.ESTABLISHMENT_TYPE
, 'JLZZ_AR_TX_ATTR_VALUE')
, 1
, 80)
, TGR.CONTRIBUTOR_TYPE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(TGR.CONTRIBUTOR_TYPE
, 'JLZZ_AR_TX_ATTR_VALUE')
, 1
, 80)
, TGR.TRANSACTION_NATURE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(TGR.TRANSACTION_NATURE
, 'JLZZ_AR_TX_ATTR_VALUE')
, 1
, 80)
, CAT.ORG_TAX_ATTRIBUTE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING (CAT.ORG_TAX_ATTRIBUTE
, 'ORGANIZATION_ATTRIBUTE')
, 1
, 80)
, CAT.CUS_TAX_ATTRIBUTE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(CAT.CUS_TAX_ATTRIBUTE
, 'CONTRIBUTOR_ATTRIBUTE')
, 1
, 80)
, CAT.TXN_TAX_ATTRIBUTE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(CAT.TXN_TAX_ATTRIBUTE
, 'TRANSACTION_ATTRIBUTE')
, 1
, 80)
, TGR.END_DATE_ACTIVE
, TGR.USE_TX_CATEG_THRESHOLDS
, TGR.LAST_UPDATE_DATE
, TGR.LAST_UPDATED_BY
, TGR.COMPOUND_PRECEDENCE
, TGR.MIN_AMOUNT
, TGR.MIN_TAXABLE_BASIS
, TGR.MIN_PERCENTAGE
, TGR.TAX_INCLUSIVE
, TGR.TRIBUTARY_SUBSTITUTION
, TGR.USED_TO_REDUCE
, TGR.TAX_CODE
, ART.DESCRIPTION
, ART.TAX_RATE
, TGR.BASE_RATE
, TGR.START_DATE_ACTIVE
, TGR.ORG_ID
, TGR.LAST_UPDATE_LOGIN
, TGR.CREATION_DATE
, TGR.CREATED_BY
, TGR.TAX_CATEGORY_TO_REDUCE_ID
, CAT_REDUCE.TAX_CATEGORY
, TGR.CALCULATE_IN_OE
, TGR.ATTRIBUTE_CATEGORY
, TGR.ATTRIBUTE1
, TGR.ATTRIBUTE2
, TGR.ATTRIBUTE3
, TGR.ATTRIBUTE4
, TGR.ATTRIBUTE5
, TGR.ATTRIBUTE6
, TGR.ATTRIBUTE7
, TGR.ATTRIBUTE8
, TGR.ATTRIBUTE9
, TGR.ATTRIBUTE10
, TGR.ATTRIBUTE11
, TGR.ATTRIBUTE12
, TGR.ATTRIBUTE13
, TGR.ATTRIBUTE14
, TGR.ATTRIBUTE15
FROM JL_ZZ_AR_TX_GROUPS TGR
, JL_ZZ_AR_TX_CATEG_ALL CAT_REDUCE
, JL_ZZ_AR_TX_CATEG_ALL CAT
, AR_VAT_TAX_ALL_B ART
, AR_SYSTEM_PARAMETERS_ALL SYS
WHERE TGR.TAX_CATEGORY_ID = CAT.TAX_CATEGORY_ID
AND CAT.TAX_RULE_SET = SYS.GLOBAL_ATTRIBUTE13
AND NVL(CAT_REDUCE.TAX_RULE_SET
, SYS.GLOBAL_ATTRIBUTE13) = SYS.GLOBAL_ATTRIBUTE13
AND TGR.TAX_CODE IS NOT NULL
AND TGR.TAX_CODE = ART.TAX_CODE
AND ART.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND NVL(ART.TAX_TYPE
, 'VAT') = 'VAT'
AND NVL(ART.TAX_CLASS
, 'O') = 'O'
AND NVL(ART.ENABLED_FLAG
, 'Y') = 'Y'
AND NVL(TGR.ORG_ID
, -99) = NVL(CAT.ORG_ID
, -99)
AND NVL(TGR.ORG_ID
, -99) = NVL(ART.ORG_ID
, -99)
AND NVL(TGR.ORG_ID
, -99) = NVL(SYS.ORG_ID
, -99)
AND TGR.TAX_CATEGORY_ID = DECODE(LTRIM(ART.GLOBAL_ATTRIBUTE1
, '0123456789 ')
, NULL
, TO_NUMBER(ART.GLOBAL_ATTRIBUTE1)
, NULL)
AND TGR.START_DATE_ACTIVE <= NVL(ART.END_DATE
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD'))
AND TGR.END_DATE_ACTIVE >= ART.START_DATE
AND NOT EXISTS (SELECT 1
FROM AR_VAT_TAX ART1
WHERE ART1.TAX_CODE = ART.TAX_CODE
AND ART1.SET_OF_BOOKS_ID = ART.SET_OF_BOOKS_ID
AND NVL(ART1.TAX_TYPE
, 'VAT') = 'VAT'
AND NVL(ART1.TAX_CLASS
, 'O') = 'O'
AND NVL(ART1.ENABLED_FLAG
, 'Y') = 'Y'
AND ART1.GLOBAL_ATTRIBUTE1 = ART.GLOBAL_ATTRIBUTE1
AND ART1.START_DATE > ART.START_DATE
AND ART1.START_DATE <= TGR.END_DATE_ACTIVE)
AND TGR.TAX_CATEGORY_TO_REDUCE_ID = CAT_REDUCE.TAX_CATEGORY_ID(+) UNION SELECT TGR.ROWID
, TGR.GROUP_TAX_ID
, TGR.TAX_CATEGORY_ID
, TGR.TAX_GROUP_RECORD_ID
, CAT.TAX_CATEGORY
, TGR.ESTABLISHMENT_TYPE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(TGR.ESTABLISHMENT_TYPE
, 'JLZZ_AR_TX_ATTR_VALUE')
, 1
, 80)
, TGR.CONTRIBUTOR_TYPE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(TGR.CONTRIBUTOR_TYPE
, 'JLZZ_AR_TX_ATTR_VALUE')
, 1
, 80)
, TGR.TRANSACTION_NATURE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(TGR.TRANSACTION_NATURE
, 'JLZZ_AR_TX_ATTR_VALUE')
, 1
, 80)
, CAT.ORG_TAX_ATTRIBUTE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING (CAT.ORG_TAX_ATTRIBUTE
, 'ORGANIZATION_ATTRIBUTE')
, 1
, 80)
, CAT.CUS_TAX_ATTRIBUTE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(CAT.CUS_TAX_ATTRIBUTE
, 'CONTRIBUTOR_ATTRIBUTE')
, 1
, 80)
, CAT.TXN_TAX_ATTRIBUTE
, SUBSTR(JL_ZZ_AR_TX_LIB_PKG.GET_LOOKUP_MEANING(CAT.TXN_TAX_ATTRIBUTE
, 'TRANSACTION_ATTRIBUTE')
, 1
, 80)
, TGR.END_DATE_ACTIVE
, TGR.USE_TX_CATEG_THRESHOLDS
, TGR.LAST_UPDATE_DATE
, TGR.LAST_UPDATED_BY
, TGR.COMPOUND_PRECEDENCE
, TGR.MIN_AMOUNT
, TGR.MIN_TAXABLE_BASIS
, TGR.MIN_PERCENTAGE
, TGR.TAX_INCLUSIVE
, TGR.TRIBUTARY_SUBSTITUTION
, TGR.USED_TO_REDUCE
, TGR.TAX_CODE
, TO_CHAR(NULL) DESCRIPTION
, TO_NUMBER(NULL) TAX_RATE
, TGR.BASE_RATE
, TGR.START_DATE_ACTIVE
, TGR.ORG_ID
, TGR.LAST_UPDATE_LOGIN
, TGR.CREATION_DATE
, TGR.CREATED_BY
, TGR.TAX_CATEGORY_TO_REDUCE_ID
, CAT_REDUCE.TAX_CATEGORY
, TGR.CALCULATE_IN_OE
, TGR.ATTRIBUTE_CATEGORY
, TGR.ATTRIBUTE1
, TGR.ATTRIBUTE2
, TGR.ATTRIBUTE3
, TGR.ATTRIBUTE4
, TGR.ATTRIBUTE5
, TGR.ATTRIBUTE6
, TGR.ATTRIBUTE7
, TGR.ATTRIBUTE8
, TGR.ATTRIBUTE9
, TGR.ATTRIBUTE10
, TGR.ATTRIBUTE11
, TGR.ATTRIBUTE12
, TGR.ATTRIBUTE13
, TGR.ATTRIBUTE14
, TGR.ATTRIBUTE15
FROM JL_ZZ_AR_TX_GROUPS TGR
, JL_ZZ_AR_TX_CATEG CAT_REDUCE
, JL_ZZ_AR_TX_CATEG_ALL CAT
, AR_SYSTEM_PARAMETERS_ALL SYS
WHERE TGR.TAX_CATEGORY_ID = CAT.TAX_CATEGORY_ID
AND CAT.TAX_RULE_SET = SYS.GLOBAL_ATTRIBUTE13
AND NVL(CAT_REDUCE.TAX_RULE_SET
, SYS.GLOBAL_ATTRIBUTE13) = SYS.GLOBAL_ATTRIBUTE13
AND TGR.TAX_CATEGORY_TO_REDUCE_ID = CAT_REDUCE.TAX_CATEGORY_ID(+)
AND TGR.TAX_CODE IS NULL
AND NVL(TGR.ORG_ID
, -99) = NVL(CAT.ORG_ID
, -99)
AND NVL(TGR.ORG_ID
, -99) = NVL(SYS.ORG_ID
, -99)