eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.JL_ZZ_AR_TX_EXC_ITM_V

Source

View Text - Preformatted


SELECT tei.rowid, tei.exc_itm_id, tei.ship_from_code, val1.geography_name, tei.ship_to_segment_id, val2.Geography_code, val2.Geography_name, tei.inventory_item_id, tei.organization_id, itm.description, tei.tax_category_id, brt.tax_category, tei.base_rate, tei.tax_code, art.description, art.tax_rate, tei.start_date_active, tei.end_date_active, tei.org_id, tei.last_updated_by, tei.last_update_date, tei.last_update_login, tei.creation_date, tei.created_by, tei.attribute_category, tei.attribute1, tei.attribute2, tei.attribute3, tei.attribute4, tei.attribute5, tei.attribute6, tei.attribute7, tei.attribute8, tei.attribute9, tei.attribute10, tei.attribute11, tei.attribute12, tei.attribute13, tei.attribute14, tei.attribute15 FROM jl_zz_ar_tx_exc_itm tei, mtl_system_items itm, jl_zz_ar_tx_categ_all brt, ar_vat_tax_all_b art, ar_system_parameters_all sys, hz_geographies val1, hz_geographies val2 WHERE tei.ship_to_segment_id = val2.geography_id AND tei.ship_from_code = val1.geography_code AND val1.country_code = sys.default_country AND val1.geography_type = sys.global_attribute9 AND tei.inventory_item_id = itm.inventory_item_id AND tei.organization_id = itm.organization_id AND tei.tax_category_id = brt.tax_category_id AND brt.tax_rule_set = sys.global_attribute13 AND tei.tax_code IS NOT NULL AND tei.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(tei.org_id,-99) = nvl(brt.org_id,-99) AND nvl(tei.org_id,-99) = nvl(art.org_id,-99) AND nvl(tei.org_id,-99) = nvl(sys.org_id,-99) AND tei.tax_category_id = decode(ltrim(art.global_attribute1,'0123456789 '), null, to_number(art.global_attribute1), null) AND tei.start_date_active <= NVL(art.end_date, to_date('4712/12/31', 'YYYY/MM/DD')) AND tei.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 <= tei.end_date_active) UNION SELECT tei.rowid, tei.exc_itm_id, tei.ship_from_code, val1.geography_name, tei.ship_to_segment_id, val2.Geography_code, val2.Geography_name, tei.inventory_item_id, tei.organization_id, itm.description, tei.tax_category_id, brt.tax_category, tei.base_rate, tei.tax_code, to_char(null) description, to_number(null) tax_rate, tei.start_date_active, tei.end_date_active, tei.org_id, tei.last_updated_by, tei.last_update_date, tei.last_update_login, tei.creation_date, tei.created_by, tei.attribute_category, tei.attribute1, tei.attribute2, tei.attribute3, tei.attribute4, tei.attribute5, tei.attribute6, tei.attribute7, tei.attribute8, tei.attribute9, tei.attribute10, tei.attribute11, tei.attribute12, tei.attribute13, tei.attribute14, tei.attribute15 FROM jl_zz_ar_tx_exc_itm tei, mtl_system_items itm, jl_zz_ar_tx_categ_all brt, hz_geographies val1, hz_geographies val2, ar_system_parameters_all sys WHERE tei.ship_to_segment_id = val2.geography_id AND tei.ship_from_code = val1.geography_code AND val1.country_code = sys.default_country AND val1.geography_type = sys.global_attribute9 AND tei.inventory_item_id = itm.inventory_item_id AND tei.organization_id = itm.organization_id AND tei.tax_category_id = brt.tax_category_id AND brt.tax_rule_set = sys.global_attribute13 AND tei.tax_code IS NULL AND nvl(tei.org_id,-99) = nvl(brt.org_id,-99) /*AND NVL(tei.org_id,-99) = NVL(val2.org_id,-99)*/ AND nvl(tei.org_id,-99) = nvl(sys.org_id,-99)

View Text - HTML Formatted

SELECT TEI.ROWID
, TEI.EXC_ITM_ID
, TEI.SHIP_FROM_CODE
, VAL1.GEOGRAPHY_NAME
, TEI.SHIP_TO_SEGMENT_ID
, VAL2.GEOGRAPHY_CODE
, VAL2.GEOGRAPHY_NAME
, TEI.INVENTORY_ITEM_ID
, TEI.ORGANIZATION_ID
, ITM.DESCRIPTION
, TEI.TAX_CATEGORY_ID
, BRT.TAX_CATEGORY
, TEI.BASE_RATE
, TEI.TAX_CODE
, ART.DESCRIPTION
, ART.TAX_RATE
, TEI.START_DATE_ACTIVE
, TEI.END_DATE_ACTIVE
, TEI.ORG_ID
, TEI.LAST_UPDATED_BY
, TEI.LAST_UPDATE_DATE
, TEI.LAST_UPDATE_LOGIN
, TEI.CREATION_DATE
, TEI.CREATED_BY
, TEI.ATTRIBUTE_CATEGORY
, TEI.ATTRIBUTE1
, TEI.ATTRIBUTE2
, TEI.ATTRIBUTE3
, TEI.ATTRIBUTE4
, TEI.ATTRIBUTE5
, TEI.ATTRIBUTE6
, TEI.ATTRIBUTE7
, TEI.ATTRIBUTE8
, TEI.ATTRIBUTE9
, TEI.ATTRIBUTE10
, TEI.ATTRIBUTE11
, TEI.ATTRIBUTE12
, TEI.ATTRIBUTE13
, TEI.ATTRIBUTE14
, TEI.ATTRIBUTE15
FROM JL_ZZ_AR_TX_EXC_ITM TEI
, MTL_SYSTEM_ITEMS ITM
, JL_ZZ_AR_TX_CATEG_ALL BRT
, AR_VAT_TAX_ALL_B ART
, AR_SYSTEM_PARAMETERS_ALL SYS
, HZ_GEOGRAPHIES VAL1
, HZ_GEOGRAPHIES VAL2
WHERE TEI.SHIP_TO_SEGMENT_ID = VAL2.GEOGRAPHY_ID
AND TEI.SHIP_FROM_CODE = VAL1.GEOGRAPHY_CODE
AND VAL1.COUNTRY_CODE = SYS.DEFAULT_COUNTRY
AND VAL1.GEOGRAPHY_TYPE = SYS.GLOBAL_ATTRIBUTE9
AND TEI.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND TEI.ORGANIZATION_ID = ITM.ORGANIZATION_ID
AND TEI.TAX_CATEGORY_ID = BRT.TAX_CATEGORY_ID
AND BRT.TAX_RULE_SET = SYS.GLOBAL_ATTRIBUTE13
AND TEI.TAX_CODE IS NOT NULL
AND TEI.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(TEI.ORG_ID
, -99) = NVL(BRT.ORG_ID
, -99)
AND NVL(TEI.ORG_ID
, -99) = NVL(ART.ORG_ID
, -99)
AND NVL(TEI.ORG_ID
, -99) = NVL(SYS.ORG_ID
, -99)
AND TEI.TAX_CATEGORY_ID = DECODE(LTRIM(ART.GLOBAL_ATTRIBUTE1
, '0123456789 ')
, NULL
, TO_NUMBER(ART.GLOBAL_ATTRIBUTE1)
, NULL)
AND TEI.START_DATE_ACTIVE <= NVL(ART.END_DATE
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD'))
AND TEI.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 <= TEI.END_DATE_ACTIVE) UNION SELECT TEI.ROWID
, TEI.EXC_ITM_ID
, TEI.SHIP_FROM_CODE
, VAL1.GEOGRAPHY_NAME
, TEI.SHIP_TO_SEGMENT_ID
, VAL2.GEOGRAPHY_CODE
, VAL2.GEOGRAPHY_NAME
, TEI.INVENTORY_ITEM_ID
, TEI.ORGANIZATION_ID
, ITM.DESCRIPTION
, TEI.TAX_CATEGORY_ID
, BRT.TAX_CATEGORY
, TEI.BASE_RATE
, TEI.TAX_CODE
, TO_CHAR(NULL) DESCRIPTION
, TO_NUMBER(NULL) TAX_RATE
, TEI.START_DATE_ACTIVE
, TEI.END_DATE_ACTIVE
, TEI.ORG_ID
, TEI.LAST_UPDATED_BY
, TEI.LAST_UPDATE_DATE
, TEI.LAST_UPDATE_LOGIN
, TEI.CREATION_DATE
, TEI.CREATED_BY
, TEI.ATTRIBUTE_CATEGORY
, TEI.ATTRIBUTE1
, TEI.ATTRIBUTE2
, TEI.ATTRIBUTE3
, TEI.ATTRIBUTE4
, TEI.ATTRIBUTE5
, TEI.ATTRIBUTE6
, TEI.ATTRIBUTE7
, TEI.ATTRIBUTE8
, TEI.ATTRIBUTE9
, TEI.ATTRIBUTE10
, TEI.ATTRIBUTE11
, TEI.ATTRIBUTE12
, TEI.ATTRIBUTE13
, TEI.ATTRIBUTE14
, TEI.ATTRIBUTE15
FROM JL_ZZ_AR_TX_EXC_ITM TEI
, MTL_SYSTEM_ITEMS ITM
, JL_ZZ_AR_TX_CATEG_ALL BRT
, HZ_GEOGRAPHIES VAL1
, HZ_GEOGRAPHIES VAL2
, AR_SYSTEM_PARAMETERS_ALL SYS
WHERE TEI.SHIP_TO_SEGMENT_ID = VAL2.GEOGRAPHY_ID
AND TEI.SHIP_FROM_CODE = VAL1.GEOGRAPHY_CODE
AND VAL1.COUNTRY_CODE = SYS.DEFAULT_COUNTRY
AND VAL1.GEOGRAPHY_TYPE = SYS.GLOBAL_ATTRIBUTE9
AND TEI.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND TEI.ORGANIZATION_ID = ITM.ORGANIZATION_ID
AND TEI.TAX_CATEGORY_ID = BRT.TAX_CATEGORY_ID
AND BRT.TAX_RULE_SET = SYS.GLOBAL_ATTRIBUTE13
AND TEI.TAX_CODE IS NULL
AND NVL(TEI.ORG_ID
, -99) = NVL(BRT.ORG_ID
, -99) /*AND NVL(TEI.ORG_ID
, -99) = NVL(VAL2.ORG_ID
, -99)*/
AND NVL(TEI.ORG_ID
, -99) = NVL(SYS.ORG_ID
, -99)