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.

[Home] [Help]

View: AP_TAX_LINES_SUMMARY_V

Product:	AP - Payables
Description:
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT AI.INVOICE_ID
, NULL
, NULL
, AID.INVOICE_DISTRIBUTION_ID
, NULL
, AID.SET_OF_BOOKS_ID
, AID.REQUEST_ID
, AI.INVOICE_NUM
, AID.PARENT_INVOICE_ID
, AI.INVOICE_DATE
, AI.INVOICE_CURRENCY_CODE
, AI.EXCHANGE_RATE
, NULL
, NULL
, AI.VENDOR_ID
, POV.SEGMENT1
, POV.VENDOR_NAME
, AI.VENDOR_SITE_ID
, POVS.COUNTRY
, POVS.STATE
, POVS.COUNTY
, NULL
, NULL
, POVS.ZIP
, NULL
, NULL
, AID.DISTRIBUTION_LINE_NUMBER
, AID.LINE_TYPE_LOOKUP_CODE
, NULL
, NULL
, NULL
, NULL
, DECODE(AID.AMOUNT_INCLUDES_TAX_FLAG
, 'Y'
, DECODE(ATC1.OFFSET_TAX_CODE_ID
, NULL
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY((AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT)- AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT) * AP_TAX_ENGINE_PKG.SUM_TAX_GROUP_RATE(ATG.TAX_GROUP_ID
, AI.INVOICE_DATE
, AI.VENDOR_SITE_ID) / (AP_TAX_ENGINE_PKG.SUM_TAX_GROUP_RATE(ATG.TAX_GROUP_ID
, AI.INVOICE_DATE
, AI.VENDOR_SITE_ID) + 100)) * (DECODE(ATC1.TAX_TYPE
, 'OFFSET'
, ABS(ATC1.TAX_RATE)
, ATC1.TAX_RATE)/100 + 1)
, AI.INVOICE_CURRENCY_CODE)
, DECODE(NVL(POVS.OFFSET_TAX_FLAG
, 'N')
, 'N'
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY((AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT) - AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT) * AP_TAX_ENGINE_PKG.SUM_TAX_GROUP_RATE(ATG.TAX_GROUP_ID
, AI.INVOICE_DATE
, AI.VENDOR_SITE_ID) / (AP_TAX_ENGINE_PKG.SUM_TAX_GROUP_RATE(ATG.TAX_GROUP_ID
, AI.INVOICE_DATE
, AI.VENDOR_SITE_ID) + 100)) * ( DECODE(ATC1.TAX_TYPE
, 'OFFSET'
, ABS(ATC1.TAX_RATE)
, ATC1.TAX_RATE)/100 + 1)
, AI.INVOICE_CURRENCY_CODE)
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY((AP_UTILITIES_PKG.AP_ROUND_CURRENCY( AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT) - AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT) * (AP_TAX_ENGINE_PKG.SUM_TAX_GROUP_RATE(ATG.TAX_GROUP_ID
, AI.INVOICE_DATE
, AI.VENDOR_SITE_ID) / (AP_TAX_ENGINE_PKG.SUM_TAX_GROUP_RATE(ATG.TAX_GROUP_ID
, AI.INVOICE_DATE
, AI.VENDOR_SITE_ID) + 100))
, AI.INVOICE_CURRENCY_CODE) * (DECODE(ATC1.TAX_TYPE
, 'OFFSET'
, ABS(ATC1.TAX_RATE)
, ATC1.TAX_RATE)/100 + 1))
, AI.INVOICE_CURRENCY_CODE)))
, AID.AMOUNT)
, NULL
, NULL
, DECODE (FSP.NON_RECOVERABLE_TAX_FLAG
, 'Y'
, AID.DIST_CODE_COMBINATION_ID
, ATC1.TAX_CODE_COMBINATION_ID)
, NULL
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PO_DISTRIBUTION_ID)
, AID.USSGL_TRANSACTION_CODE
, AID.USSGL_TRX_CODE_CONTEXT
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, ATC1.TAX_ID
, ATC1.NAME
, NVL(AID.TAX_CODE_OVERRIDE_FLAG
, 'N')
, ATC1.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(ATC1.TAX_TYPE
, 'OFFSET'
, ATC1.TAX_RECOVERY_RATE
, AID.TAX_RECOVERY_RATE)
, NVL(AID.TAX_RECOVERY_OVERRIDE_FLAG
, 'N')
, ATG.TAX_GROUP_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(AI.AUTO_TAX_CALC_FLAG
, 'N')
, NVL (POVS.AP_TAX_ROUNDING_RULE
, 'N')
, ATC1.TAX_TYPE
, ATC1.DESCRIPTION
, NULL
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PROJECT_ID)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.TASK_ID)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.AWARD_ID)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_TYPE)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_ORGANIZATION_ID)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_ITEM_DATE)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PA_QUANTITY)
, NULL
, NULL
, NVL(AID.AMOUNT_INCLUDES_TAX_FLAG
, 'N')
, NVL(AID.TAX_CALCULATED_FLAG
, 'N')
, NVL(AID.TAX_RECOVERABLE_FLAG
, 'N')
, NULL
, AID.INVOICE_INCLUDES_PREPAY_FLAG
, AID.ACCOUNTING_DATE
, AID.RCV_TRANSACTION_ID
, AID.ASSETS_TRACKING_FLAG
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, AR_TAX_GROUP_CODES ATG
, AP_TAX_CODES ATC1
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE AI.INVOICE_ID = AID.INVOICE_ID
AND AI.VENDOR_ID = POV.VENDOR_ID
AND AI.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND NVL(AID.TAX_CALCULATED_FLAG
, 'N') = 'N'
AND AID.LINE_TYPE_LOOKUP_CODE NOT IN ('TAX'
, 'AWT')
AND AID.TAX_CODE_ID = ATG.TAX_GROUP_ID
AND ATG.TAX_GROUP_TYPE = 'AP'
AND ATG.TAX_CODE_ID = ATC1.TAX_ID
AND ATG.START_DATE <= AI.INVOICE_DATE
AND NVL(ATG.END_DATE
, AI.INVOICE_DATE) >= AI.INVOICE_DATE
AND NVL(ATG.ENABLED_FLAG
, 'Y') = 'Y'
AND ATC1.START_DATE <= AI.INVOICE_DATE
AND NVL(ATC1.INACTIVE_DATE
, AI.INVOICE_DATE) >= AI.INVOICE_DATE
AND NVL(ATC1.ENABLED_FLAG
, 'Y') = 'Y' UNION ALL SELECT AI.INVOICE_ID
, NULL
, NULL
, AID.INVOICE_DISTRIBUTION_ID
, NULL
, AID.SET_OF_BOOKS_ID
, AID.REQUEST_ID
, AI.INVOICE_NUM
, AID.PARENT_INVOICE_ID
, AI.INVOICE_DATE
, AI.INVOICE_CURRENCY_CODE
, AI.EXCHANGE_RATE
, NULL
, NULL
, AI.VENDOR_ID
, POV.SEGMENT1
, POV.VENDOR_NAME
, AI.VENDOR_SITE_ID
, POVS.COUNTRY
, POVS.STATE
, POVS.COUNTY
, NULL
, NULL
, POVS.ZIP
, NULL
, NULL
, AID.DISTRIBUTION_LINE_NUMBER
, AID.LINE_TYPE_LOOKUP_CODE
, NULL
, NULL
, NULL
, NULL
, DECODE(AID.AMOUNT_INCLUDES_TAX_FLAG
, 'Y'
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY ((AP_UTILITIES_PKG.AP_ROUND_CURRENCY(AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT)- AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT) * (AP_TAX_ENGINE_PKG.SUM_TAX_GROUP_RATE(ATG.TAX_GROUP_ID
, AI.INVOICE_DATE
, AI.VENDOR_SITE_ID) / (AP_TAX_ENGINE_PKG.SUM_TAX_GROUP_RATE(ATG.TAX_GROUP_ID
, AI.INVOICE_DATE
, AI.VENDOR_SITE_ID) + 100))
, AI.INVOICE_CURRENCY_CODE) * ( DECODE(ATC3.TAX_TYPE
, 'OFFSET'
, ABS(ATC3.TAX_RATE)
, ATC3.TAX_RATE)/100 + 1))
, AI.INVOICE_CURRENCY_CODE)
, AID.AMOUNT)
, NULL
, NULL
, DECODE (FSP.NON_RECOVERABLE_TAX_FLAG
, 'Y'
, AID.DIST_CODE_COMBINATION_ID
, ATC3.TAX_CODE_COMBINATION_ID)
, NULL
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PO_DISTRIBUTION_ID)
, AID.USSGL_TRANSACTION_CODE
, AID.USSGL_TRX_CODE_CONTEXT
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, ATC3.TAX_ID
, ATC3.NAME
, NVL(AID.TAX_CODE_OVERRIDE_FLAG
, 'N')
, ATC3.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(ATC3.TAX_TYPE
, 'OFFSET'
, ATC3.TAX_RECOVERY_RATE
, AID.TAX_RECOVERY_RATE)
, NVL(AID.TAX_RECOVERY_OVERRIDE_FLAG
, 'N')
, ATG.TAX_GROUP_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(AI.AUTO_TAX_CALC_FLAG
, 'N')
, NVL (POVS.AP_TAX_ROUNDING_RULE
, 'N')
, ATC3.TAX_TYPE
, ATC3.DESCRIPTION
, NULL
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PROJECT_ID)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.TASK_ID)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.AWARD_ID)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_TYPE)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_ORGANIZATION_ID)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_ITEM_DATE)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PA_QUANTITY)
, NULL
, NULL
, NVL(AID.AMOUNT_INCLUDES_TAX_FLAG
, 'N')
, NVL(AID.TAX_CALCULATED_FLAG
, 'N')
, NVL(AID.TAX_RECOVERABLE_FLAG
, 'N')
, NULL
, AID.INVOICE_INCLUDES_PREPAY_FLAG
, AID.ACCOUNTING_DATE
, AID.RCV_TRANSACTION_ID
, AID.ASSETS_TRACKING_FLAG
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, AR_TAX_GROUP_CODES ATG
, AP_TAX_CODES ATC1
, AP_TAX_CODES ATC2
, AP_TAX_CODES ATC3
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE AI.INVOICE_ID = AID.INVOICE_ID
AND AI.VENDOR_ID = POV.VENDOR_ID
AND AI.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND NVL(AID.TAX_CALCULATED_FLAG
, 'N') = 'N'
AND AID.LINE_TYPE_LOOKUP_CODE NOT IN ('TAX'
, 'AWT')
AND AID.TAX_CODE_ID = ATG.TAX_GROUP_ID
AND ATG.TAX_GROUP_TYPE = 'AP'
AND ATG.TAX_CODE_ID = ATC2.TAX_ID
AND ATC2.OFFSET_TAX_CODE_ID = ATC1.TAX_ID
AND POVS.OFFSET_TAX_FLAG = 'Y'
AND ATG.START_DATE <= AI.INVOICE_DATE
AND NVL(ATG.END_DATE
, AI.INVOICE_DATE) >= AI.INVOICE_DATE
AND NVL(ATG.ENABLED_FLAG
, 'Y') = 'Y'
AND ATC3.NAME=ATC1.NAME
AND NVL(ATC3.ENABLED_FLAG
, 'N')='Y'
AND ATC3.START_DATE <= AI.INVOICE_DATE
AND NVL(ATC3.INACTIVE_DATE
, AI.INVOICE_DATE) >= AI.INVOICE_DATE UNION ALL SELECT AI.INVOICE_ID
, NULL
, NULL
, AID.INVOICE_DISTRIBUTION_ID
, NULL
, AID.SET_OF_BOOKS_ID
, AID.REQUEST_ID
, AI.INVOICE_NUM
, AID.PARENT_INVOICE_ID
, AI.INVOICE_DATE
, AI.INVOICE_CURRENCY_CODE
, AI.EXCHANGE_RATE
, NULL
, NULL
, AI.VENDOR_ID
, POV.SEGMENT1
, POV.VENDOR_NAME
, AI.VENDOR_SITE_ID
, POVS.COUNTRY
, POVS.STATE
, POVS.COUNTY
, NULL
, NULL
, POVS.ZIP
, NULL
, NULL
, AID.DISTRIBUTION_LINE_NUMBER
, AID.LINE_TYPE_LOOKUP_CODE
, NULL
, NULL
, NULL
, NULL
, AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT)
, NULL
, NULL
, DECODE (FSP.NON_RECOVERABLE_TAX_FLAG
, 'Y'
, AID.DIST_CODE_COMBINATION_ID
, ATC1.TAX_CODE_COMBINATION_ID)
, NULL
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PO_DISTRIBUTION_ID)
, AID.USSGL_TRANSACTION_CODE
, AID.USSGL_TRX_CODE_CONTEXT
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, ATC1.TAX_ID
, ATC1.NAME
, NVL(AID.TAX_CODE_OVERRIDE_FLAG
, 'N')
, ATC1.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(ATC1.TAX_TYPE
, 'OFFSET'
, ATC1.TAX_RECOVERY_RATE
, AID.TAX_RECOVERY_RATE)
, NVL(AID.TAX_RECOVERY_OVERRIDE_FLAG
, 'N')
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(AI.AUTO_TAX_CALC_FLAG
, 'N')
, NVL (POVS.AP_TAX_ROUNDING_RULE
, 'N')
, ATC1.TAX_TYPE
, ATC1.DESCRIPTION
, NULL
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PROJECT_ID)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.TASK_ID)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.AWARD_ID)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_TYPE)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_ORGANIZATION_ID)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_ITEM_DATE)
, DECODE (ATC1.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PA_QUANTITY)
, NULL
, NULL
, NVL(AID.AMOUNT_INCLUDES_TAX_FLAG
, 'N')
, NVL(AID.TAX_CALCULATED_FLAG
, 'N')
, NVL(AID.TAX_RECOVERABLE_FLAG
, 'N')
, NULL
, AID.INVOICE_INCLUDES_PREPAY_FLAG
, AID.ACCOUNTING_DATE
, AID.RCV_TRANSACTION_ID
, AID.ASSETS_TRACKING_FLAG
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, AP_TAX_CODES ATC1
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE AI.INVOICE_ID = AID.INVOICE_ID
AND AI.VENDOR_ID = POV.VENDOR_ID
AND AI.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND NVL(AID.TAX_CALCULATED_FLAG
, 'N') = 'N'
AND AID.LINE_TYPE_LOOKUP_CODE NOT IN ('TAX'
, 'AWT')
AND ATC1.TAX_TYPE <> 'TAX_GROUP'
AND AID.TAX_CODE_ID = ATC1.TAX_ID UNION ALL SELECT AI.INVOICE_ID
, NULL
, NULL
, AID.INVOICE_DISTRIBUTION_ID
, NULL
, AID.SET_OF_BOOKS_ID
, AID.REQUEST_ID
, AI.INVOICE_NUM
, AID.PARENT_INVOICE_ID
, AI.INVOICE_DATE
, AI.INVOICE_CURRENCY_CODE
, AI.EXCHANGE_RATE
, NULL
, NULL
, AI.VENDOR_ID
, POV.SEGMENT1
, POV.VENDOR_NAME
, AI.VENDOR_SITE_ID
, POVS.COUNTRY
, POVS.STATE
, POVS.COUNTY
, NULL
, NULL
, POVS.ZIP
, NULL
, NULL
, AID.DISTRIBUTION_LINE_NUMBER
, AID.LINE_TYPE_LOOKUP_CODE
, NULL
, NULL
, NULL
, NULL
, AP_TAX_ENGINE_PKG.GET_AMOUNT( AID.INVOICE_DISTRIBUTION_ID
, AID.LINE_TYPE_LOOKUP_CODE
, AID.AMOUNT_INCLUDES_TAX_FLAG
, AID.AMOUNT)
, NULL
, NULL
, DECODE (FSP.NON_RECOVERABLE_TAX_FLAG
, 'Y'
, AID.DIST_CODE_COMBINATION_ID
, ATC3.TAX_CODE_COMBINATION_ID)
, NULL
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PO_DISTRIBUTION_ID)
, AID.USSGL_TRANSACTION_CODE
, AID.USSGL_TRX_CODE_CONTEXT
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, ATC3.TAX_ID
, ATC3.NAME
, NVL(AID.TAX_CODE_OVERRIDE_FLAG
, 'N')
, ATC3.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, DECODE(ATC3.TAX_TYPE
, 'OFFSET'
, ATC3.TAX_RECOVERY_RATE
, AID.TAX_RECOVERY_RATE)
, NVL(AID.TAX_RECOVERY_OVERRIDE_FLAG
, 'N')
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NVL(AI.AUTO_TAX_CALC_FLAG
, 'N')
, NVL (POVS.AP_TAX_ROUNDING_RULE
, 'N')
, ATC3.TAX_TYPE
, ATC3.DESCRIPTION
, NULL
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PROJECT_ID)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.TASK_ID)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.AWARD_ID)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_TYPE)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_ORGANIZATION_ID)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.EXPENDITURE_ITEM_DATE)
, DECODE (ATC3.TAX_TYPE
, 'OFFSET'
, NULL
, AID.PA_QUANTITY)
, NULL
, NULL
, NVL(AID.AMOUNT_INCLUDES_TAX_FLAG
, 'N')
, NVL(AID.TAX_CALCULATED_FLAG
, 'N')
, NVL(AID.TAX_RECOVERABLE_FLAG
, 'N')
, NULL
, AID.INVOICE_INCLUDES_PREPAY_FLAG
, AID.ACCOUNTING_DATE
, AID.RCV_TRANSACTION_ID
, AID.ASSETS_TRACKING_FLAG
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, AP_TAX_CODES ATC1
, AP_TAX_CODES ATC2
, AP_TAX_CODES ATC3
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE AI.INVOICE_ID = AID.INVOICE_ID
AND AI.VENDOR_ID = POV.VENDOR_ID
AND AI.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND NVL(AID.TAX_CALCULATED_FLAG
, 'N') = 'N'
AND AID.LINE_TYPE_LOOKUP_CODE NOT IN ('TAX'
, 'AWT')
AND ATC1.TAX_TYPE <> 'TAX_GROUP'
AND ATC1.TAX_ID = ATC2.OFFSET_TAX_CODE_ID
AND ATC2.TAX_ID = AID.TAX_CODE_ID
AND POVS.OFFSET_TAX_FLAG = 'Y'
AND ATC3.NAME=ATC1.NAME
AND NVL(ATC3.ENABLED_FLAG
, 'N')='Y'
AND ATC3.START_DATE <= AI.INVOICE_DATE
AND NVL(ATC3.INACTIVE_DATE
, AI.INVOICE_DATE) >= AI.INVOICE_DATE

Columns

Name
TRX_HEADER_ID
TRX_LINE_ID
TRX_SHIPMENT_ID
TRX_DISTRIBUTION_ID
TAX_LINE_NUMBER
SET_OF_BOOKS_ID
REQUEST_ID
TRX_NUMBER
PREVIOUS_TRX_HEADER_ID
TRX_DATE
TRX_CURRENCY_CODE
EXCHANGE_RATE
MINIMUM_ACCOUNTABLE_UNIT
PRECISION
SHIP_FROM_SUPPLIER_ID
SHIP_FROM_SUPPLIER_NUMBER
SHIP_FROM_SUPPLIER_NAME
SHIP_FROM_SITE_USE_ID
SHIP_FROM_COUNTRY
SHIP_FROM_STATE
SHIP_FROM_COUNTY
SHIP_FROM_CITY
SHIP_FROM_PROVINCE
SHIP_FROM_POSTAL_CODE
SHIP_FROM_IN_CITY_LIMITS_FLAG
SHIP_FROM_GEOCODE
LINE_NUMBER
TRX_LINE_TYPE
INVENTORY_ITEM_ID
PART_NUMBER
QUANTITY
QUANTITY_ORDERED
UNIT_PRICE
PRICE_OVERRIDE
TAXABLE_FLAG
CODE_COMBINATION_ID
FOB_CODE
PREVIOUS_TRX_LINE_ID
USSGL_TRANSACTION_CODE
USSGL_TRX_CODE_CONTEXT
SHIP_TO_LOCATION_ID
SHIP_TO_ORGANIZATION_ID
SHIP_TO_WAREHOUSE_ID
SHIP_TO_COUNTRY
SHIP_TO_STATE
SHIP_TO_COUNTY
SHIP_TO_CITY
SHIP_TO_PROVINCE
SHIP_TO_POSTAL_CODE
SHIP_TO_IN_CITY_LIMITS_FLAG
SHIP_TO_GEOCODE
POO_ADDRESS_CODE
POA_ADDRESS_CODE
TAX_CODE_ID
TAX_CODE
TAX_USER_OVERRIDE_FLAG
TAX_RATE
TOTAL_TAX_AMOUNT
RECOVERABLE_TAX
NONRECOVERABLE_TAX
LOCATION_QUALIFIER
COMPOUNDING_PRECEDENCE
TAX_EXEMPTION_ID
TAX_EXCEPTION_ID
VENDOR_CONTROL_EXEMPTIONS
TAX_RECOVERY_RATE
TAX_RECOVERY_OVERRIDE_FLAG
GLOBAL_ATTRIBUTE1
GLOBAL_ATTRIBUTE2
GLOBAL_ATTRIBUTE3
GLOBAL_ATTRIBUTE4
GLOBAL_ATTRIBUTE5
GLOBAL_NUMERIC_ATTRIBUTE1
GLOBAL_NUMERIC_ATTRIBUTE2
GLOBAL_NUMERIC_ATTRIBUTE3
GLOBAL_NUMERIC_ATTRIBUTE4
GLOBAL_NUMERIC_ATTRIBUTE5
TAX_EXEMPT_FLAG
TAX_EXEMPT_NUMBER
TAX_EXEMPT_REASON_CODE
COMPANY_CODE
DIVISION_CODE
AUDIT_FLAG
TAX_HEADER_LEVEL_FLAG
TAX_ROUNDING_RULE
TAX_TYPE
TAX_DESCRIPTION
ALLOW_TAX_CODE_OVERRIDE_FLAG
PROJECT_ID
TASK_ID
AWARD_ID
EXPENDITURE_TYPE
EXPENDITURE_ORGANIZATION_ID
EXPENDITURE_ITEM_DATE
PA_QUANTITY
TAX_MINIMUM_ACCOUNTABLE_UNIT
TAX_PRECISION
AMOUNT_INCLUDES_TAX_FLAG
TAX_CALCULATED_FLAG
TAX_RECOVERABLE_FLAG
CURRENCY_UNIT_PRICE
INVOICE_INCLUDES_PREPAY_FLAG
ACCOUNT_GL_DATE
RCV_TRANSACTION_ID
ASSETS_TRACKING_FLAG