Product: | AP - Payables |
---|---|
Description: | |
Implementation/DBA Data: | Not implemented in this database |
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