FND Design Data [Home] [Help]

View: JG_AP_TAX_LINES_SUMMARY_V

Product: JG - Regional Localizations
Description: No longer used
Implementation/DBA Data: ViewAPPS.JG_AP_TAX_LINES_SUMMARY_V
View Text

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'))

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
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
ACCOUNT_GL_DATE