VIEW: APPS.JG_AP_TAX_LINES_SUMMARY_V

Source

View Text - Preformatted

SELECT /*For transactions with a tax group. Expand rows for each tax code in this tax group. */ 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(aid.amount - 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 , /*Change to get tax_recovery_rate from document for a tax group, and get tax_recovery_rate from ap_tax_codes for offset tax item */ decode(atc1.tax_type, 'OFFSET', atc1.tax_recovery_rate, aid.tax_recovery_rate) , nvl(aid.tax_recovery_override_flag, 'N') , /*Use this global attribute to stores the tax_group_id. Only use for AP for approval purpose.*/ 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.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.accounting_date FROM ap_invoices ai , ap_invoice_distributions aid , ap_suppliers pov , ap_supplier_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 /* For transactions with a tax group. Expanded the rows of offset tax codes linked with the tax codes in this tax group. If an offset tax code is used to link with multiple tax codes, multiple rows are expanded. */ 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(aid.amount - 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 , /*Change to get tax_recovery_rate from document for a tax group, and get tax_recovery_rate from ap_tax_codes for offset tax item */ decode(atc1.tax_type, 'OFFSET', atc1.tax_recovery_rate, aid.tax_recovery_rate) , nvl(aid.tax_recovery_override_flag, 'N') , /*Use this global attribute to stores the tax_group_id. Only use for AP for approval purpose.*/ 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.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.accounting_date FROM ap_invoices ai , ap_invoice_distributions aid , ap_suppliers pov , ap_supplier_sites povs , ar_tax_group_codes atg, ap_tax_codes atc1 , ap_tax_codes atc2, 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 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 distinct /*For transactions with regular tax code ( tax_type <> 'TAX_GROUP').*/ 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 , 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 , /*Change to get tax_recovery_rate from ap_tax_codes for offset tax item */ 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.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.accounting_date FROM ap_invoices ai , ap_invoice_distributions aid , ap_suppliers pov , ap_supplier_sites povs , ap_tax_codes atc1 , ap_tax_codes atc2, 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 OR (atc1.tax_id = atc2.offset_tax_code_id AND atc2.tax_id = aid.tax_code_id AND povs.offset_tax_flag = 'Y'))

View Text - HTML Formatted

SELECT /*FOR TRANSACTIONS WITH A TAX GROUP. EXPAND ROWS FOR EACH TAX CODE IN THIS TAX GROUP. */ 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(AID.AMOUNT - 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
, /*CHANGE TO GET TAX_RECOVERY_RATE
FROM DOCUMENT FOR A TAX GROUP
,
AND GET TAX_RECOVERY_RATE
FROM AP_TAX_CODES FOR OFFSET TAX ITEM */ DECODE(ATC1.TAX_TYPE
, 'OFFSET'
, ATC1.TAX_RECOVERY_RATE
, AID.TAX_RECOVERY_RATE)
, NVL(AID.TAX_RECOVERY_OVERRIDE_FLAG
, 'N')
, /*USE THIS GLOBAL ATTRIBUTE TO STORES THE TAX_GROUP_ID. ONLY USE FOR AP FOR APPROVAL PURPOSE.*/ 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.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.ACCOUNTING_DATE
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, AP_SUPPLIERS POV
, AP_SUPPLIER_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 /* FOR TRANSACTIONS WITH A TAX GROUP. EXPANDED THE ROWS OF OFFSET TAX CODES LINKED WITH THE TAX CODES IN THIS TAX GROUP. IF AN OFFSET TAX CODE IS USED TO LINK WITH MULTIPLE TAX CODES
, MULTIPLE ROWS ARE EXPANDED. */ 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(AID.AMOUNT - 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
, /*CHANGE TO GET TAX_RECOVERY_RATE
FROM DOCUMENT FOR A TAX GROUP
,
AND GET TAX_RECOVERY_RATE
FROM AP_TAX_CODES FOR OFFSET TAX ITEM */ DECODE(ATC1.TAX_TYPE
, 'OFFSET'
, ATC1.TAX_RECOVERY_RATE
, AID.TAX_RECOVERY_RATE)
, NVL(AID.TAX_RECOVERY_OVERRIDE_FLAG
, 'N')
, /*USE THIS GLOBAL ATTRIBUTE TO STORES THE TAX_GROUP_ID. ONLY USE FOR AP FOR APPROVAL PURPOSE.*/ 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.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.ACCOUNTING_DATE
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, AP_SUPPLIERS POV
, AP_SUPPLIER_SITES POVS
, AR_TAX_GROUP_CODES ATG
, AP_TAX_CODES ATC1
, AP_TAX_CODES ATC2
, 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 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 DISTINCT /*FOR TRANSACTIONS WITH REGULAR TAX CODE ( TAX_TYPE <> 'TAX_GROUP').*/ 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
, 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
, /*CHANGE TO GET TAX_RECOVERY_RATE
FROM AP_TAX_CODES FOR OFFSET TAX ITEM */ 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.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.ACCOUNTING_DATE
FROM AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, AP_SUPPLIERS POV
, AP_SUPPLIER_SITES POVS
, AP_TAX_CODES ATC1
, AP_TAX_CODES ATC2
, 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 OR (ATC1.TAX_ID = ATC2.OFFSET_TAX_CODE_ID
AND ATC2.TAX_ID = AID.TAX_CODE_ID
AND POVS.OFFSET_TAX_FLAG = 'Y'))