VIEW: APPS.JG_PO_TAX_LINES_SUMMARY_V

Source

View Text - Preformatted

SELECT poh.po_header_id ,pol.po_line_id ,poll.line_location_id ,pod.po_distribution_id ,null ,fsp.set_of_books_id ,poh.request_id ,poh.segment1 ,null ,poh.last_update_date ,poh.currency_code ,poh.rate ,fc.minimum_accountable_unit ,fc.precision ,poh.vendor_id ,null ,pov.vendor_name ,poh.vendor_site_id ,povs.country ,povs.state ,povs.county ,null ,null ,povs.zip ,null ,null ,pol.line_num ,plt.line_type ,pol.item_id ,msi.segment1 ,poll.quantity ,pod.quantity_ordered ,pol.unit_price ,poll.price_override ,poll.taxable_flag ,pod.code_combination_id ,poh.fob_lookup_code ,null ,pol.ussgl_transaction_code ,pol.government_context ,poll.ship_to_location_id ,poll.ship_to_organization_id ,null ,hrl.country ,null ,null ,hrl.town_or_city ,null ,hrl.postal_code ,null ,null ,null ,null /*Get tax_id from ap_tax_codes for tax group.*/ ,atc.tax_id ,null ,poll.tax_user_override_flag ,atc.tax_rate ,null ,null ,null ,null ,null ,null ,null ,null /*Change the view to get recovery rate from document for a tax group.*/ ,pod.recovery_rate ,pod.tax_recovery_override_flag ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,povs.ap_tax_rounding_rule ,atc.tax_type ,atc.description ,null ,pod.project_id ,pod.task_id ,pod.expenditure_type ,pod.expenditure_organization_id ,pod.expenditure_item_date ,null ,null ,null ,null ,null ,null FROM po_headers poh ,po_lines pol ,po_line_locations poll ,po_distributions pod ,po_vendors pov ,po_vendor_sites povs ,po_line_types plt ,mtl_system_items msi ,hr_locations hrl ,fnd_currencies fc ,financials_system_parameters fsp ,mtl_categories mc ,ap_tax_codes atc ,ar_tax_group_codes atg WHERE poh.po_header_id = pol.po_header_id AND pol.po_header_id = poll.po_header_id AND poll.po_header_id = pod.po_header_id AND pol.po_line_id = poll.po_line_id AND poll.po_line_id = pod.po_line_id AND poll.line_location_id = pod.line_location_id AND pol.item_id = msi.inventory_item_id(+) AND fsp.org_id = nvl(msi.organization_id,fsp.org_id) AND pol.category_id = mc.category_id(+) AND poh.vendor_id = pov.vendor_id AND poh.vendor_site_id = povs.vendor_site_id AND poll.ship_to_location_id = hrl.location_id AND poh.currency_code = fc.currency_code(+) AND pol.line_type_id = plt.line_type_id AND nvl(poll.calculate_tax_flag, 'Y') != 'N' AND poll.tax_code_id = atg.tax_group_id AND atg.tax_group_type = 'AP' AND atg.tax_code_id = atc.tax_id AND atg.start_date <= poh.last_update_date AND NVL(atg.end_date, poh.last_update_date) >= poh.last_update_date AND NVL(atg.enabled_flag, 'Y') = 'Y' AND atc.start_date <= poh.last_update_date AND NVL(atc.inactive_date, poh.last_update_date) >=poh.last_update_date AND NVL(atc.enabled_flag, 'Y') = 'Y' UNION /*For transactions with regular tax code (tax_type <> 'TAX_GROUP') */ SELECT poh.po_header_id ,pol.po_line_id ,poll.line_location_id ,pod.po_distribution_id ,null ,fsp.set_of_books_id ,poh.request_id ,poh.segment1 ,null ,poh.last_update_date ,poh.currency_code ,poh.rate ,fc.minimum_accountable_unit ,fc.precision ,poh.vendor_id ,null ,pov.vendor_name ,poh.vendor_site_id ,povs.country ,povs.state ,povs.county ,null ,null ,povs.zip ,null ,null ,pol.line_num ,plt.line_type ,pol.item_id ,msi.segment1 ,poll.quantity ,pod.quantity_ordered ,pol.unit_price ,poll.price_override ,poll.taxable_flag ,pod.code_combination_id ,poh.fob_lookup_code ,null ,pol.ussgl_transaction_code ,pol.government_context ,poll.ship_to_location_id ,poll.ship_to_organization_id ,null ,hrl.country ,null ,null ,hrl.town_or_city ,null ,hrl.postal_code ,null ,null ,null ,null /*Use the tax_code_id on the document for regular tax code. */ ,poll.tax_code_id ,null ,poll.tax_user_override_flag ,atc.tax_rate ,null ,null ,null ,null ,null ,null ,null ,null /*Use the recovery_rate on the document for regular tax code.*/ ,pod.recovery_rate ,pod.tax_recovery_override_flag ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,povs.ap_tax_rounding_rule ,atc.tax_type ,atc.description ,null ,pod.project_id ,pod.task_id ,pod.expenditure_type ,pod.expenditure_organization_id ,pod.expenditure_item_date ,null ,null ,null ,null ,null ,null FROM po_headers poh ,po_lines pol ,po_line_locations poll ,po_distributions pod ,po_vendors pov ,po_vendor_sites povs ,po_line_types plt ,mtl_system_items msi ,hr_locations hrl ,fnd_currencies fc ,financials_system_parameters fsp ,mtl_categories mc ,ap_tax_codes atc WHERE poh.po_header_id = pol.po_header_id AND pol.po_header_id = poll.po_header_id AND poll.po_header_id = pod.po_header_id AND pol.po_line_id = poll.po_line_id AND poll.po_line_id = pod.po_line_id AND poll.line_location_id = pod.line_location_id AND pol.item_id = msi.inventory_item_id(+) AND fsp.org_id = nvl(msi.organization_id,fsp.org_id) AND pol.category_id = mc.category_id(+) AND poh.vendor_id = pov.vendor_id AND poh.vendor_site_id = povs.vendor_site_id AND poll.ship_to_location_id = hrl.location_id AND poh.currency_code = fc.currency_code(+) AND pol.line_type_id = plt.line_type_id AND nvl(poll.calculate_tax_flag, 'Y') != 'N' AND poll.tax_code_id = atc.tax_id(+) AND atc.tax_type <> 'TAX_GROUP'

View Text - HTML Formatted

SELECT POH.PO_HEADER_ID
, POL.PO_LINE_ID
, POLL.LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID
, NULL
, FSP.SET_OF_BOOKS_ID
, POH.REQUEST_ID
, POH.SEGMENT1
, NULL
, POH.LAST_UPDATE_DATE
, POH.CURRENCY_CODE
, POH.RATE
, FC.MINIMUM_ACCOUNTABLE_UNIT
, FC.PRECISION
, POH.VENDOR_ID
, NULL
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.COUNTRY
, POVS.STATE
, POVS.COUNTY
, NULL
, NULL
, POVS.ZIP
, NULL
, NULL
, POL.LINE_NUM
, PLT.LINE_TYPE
, POL.ITEM_ID
, MSI.SEGMENT1
, POLL.QUANTITY
, POD.QUANTITY_ORDERED
, POL.UNIT_PRICE
, POLL.PRICE_OVERRIDE
, POLL.TAXABLE_FLAG
, POD.CODE_COMBINATION_ID
, POH.FOB_LOOKUP_CODE
, NULL
, POL.USSGL_TRANSACTION_CODE
, POL.GOVERNMENT_CONTEXT
, POLL.SHIP_TO_LOCATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID
, NULL
, HRL.COUNTRY
, NULL
, NULL
, HRL.TOWN_OR_CITY
, NULL
, HRL.POSTAL_CODE
, NULL
, NULL
, NULL
, NULL /*GET TAX_ID
FROM AP_TAX_CODES FOR TAX GROUP.*/
, ATC.TAX_ID
, NULL
, POLL.TAX_USER_OVERRIDE_FLAG
, ATC.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL /*CHANGE THE VIEW TO GET RECOVERY RATE
FROM DOCUMENT FOR A TAX GROUP.*/
, POD.RECOVERY_RATE
, POD.TAX_RECOVERY_OVERRIDE_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, POVS.AP_TAX_ROUNDING_RULE
, ATC.TAX_TYPE
, ATC.DESCRIPTION
, NULL
, POD.PROJECT_ID
, POD.TASK_ID
, POD.EXPENDITURE_TYPE
, POD.EXPENDITURE_ORGANIZATION_ID
, POD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS POLL
, PO_DISTRIBUTIONS POD
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_LINE_TYPES PLT
, MTL_SYSTEM_ITEMS MSI
, HR_LOCATIONS HRL
, FND_CURRENCIES FC
, FINANCIALS_SYSTEM_PARAMETERS FSP
, MTL_CATEGORIES MC
, AP_TAX_CODES ATC
, AR_TAX_GROUP_CODES ATG
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_HEADER_ID = POLL.PO_HEADER_ID
AND POLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POLL.PO_LINE_ID = POD.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND FSP.ORG_ID = NVL(MSI.ORGANIZATION_ID
, FSP.ORG_ID)
AND POL.CATEGORY_ID = MC.CATEGORY_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID
AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND POLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID
AND POH.CURRENCY_CODE = FC.CURRENCY_CODE(+)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND NVL(POLL.CALCULATE_TAX_FLAG
, 'Y') != 'N'
AND POLL.TAX_CODE_ID = ATG.TAX_GROUP_ID
AND ATG.TAX_GROUP_TYPE = 'AP'
AND ATG.TAX_CODE_ID = ATC.TAX_ID
AND ATG.START_DATE <= POH.LAST_UPDATE_DATE
AND NVL(ATG.END_DATE
, POH.LAST_UPDATE_DATE) >= POH.LAST_UPDATE_DATE
AND NVL(ATG.ENABLED_FLAG
, 'Y') = 'Y'
AND ATC.START_DATE <= POH.LAST_UPDATE_DATE
AND NVL(ATC.INACTIVE_DATE
, POH.LAST_UPDATE_DATE) >=POH.LAST_UPDATE_DATE
AND NVL(ATC.ENABLED_FLAG
, 'Y') = 'Y' UNION /*FOR TRANSACTIONS WITH REGULAR TAX CODE (TAX_TYPE <> 'TAX_GROUP') */ SELECT POH.PO_HEADER_ID
, POL.PO_LINE_ID
, POLL.LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID
, NULL
, FSP.SET_OF_BOOKS_ID
, POH.REQUEST_ID
, POH.SEGMENT1
, NULL
, POH.LAST_UPDATE_DATE
, POH.CURRENCY_CODE
, POH.RATE
, FC.MINIMUM_ACCOUNTABLE_UNIT
, FC.PRECISION
, POH.VENDOR_ID
, NULL
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.COUNTRY
, POVS.STATE
, POVS.COUNTY
, NULL
, NULL
, POVS.ZIP
, NULL
, NULL
, POL.LINE_NUM
, PLT.LINE_TYPE
, POL.ITEM_ID
, MSI.SEGMENT1
, POLL.QUANTITY
, POD.QUANTITY_ORDERED
, POL.UNIT_PRICE
, POLL.PRICE_OVERRIDE
, POLL.TAXABLE_FLAG
, POD.CODE_COMBINATION_ID
, POH.FOB_LOOKUP_CODE
, NULL
, POL.USSGL_TRANSACTION_CODE
, POL.GOVERNMENT_CONTEXT
, POLL.SHIP_TO_LOCATION_ID
, POLL.SHIP_TO_ORGANIZATION_ID
, NULL
, HRL.COUNTRY
, NULL
, NULL
, HRL.TOWN_OR_CITY
, NULL
, HRL.POSTAL_CODE
, NULL
, NULL
, NULL
, NULL /*USE THE TAX_CODE_ID ON THE DOCUMENT FOR REGULAR TAX CODE. */
, POLL.TAX_CODE_ID
, NULL
, POLL.TAX_USER_OVERRIDE_FLAG
, ATC.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL /*USE THE RECOVERY_RATE ON THE DOCUMENT FOR REGULAR TAX CODE.*/
, POD.RECOVERY_RATE
, POD.TAX_RECOVERY_OVERRIDE_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, POVS.AP_TAX_ROUNDING_RULE
, ATC.TAX_TYPE
, ATC.DESCRIPTION
, NULL
, POD.PROJECT_ID
, POD.TASK_ID
, POD.EXPENDITURE_TYPE
, POD.EXPENDITURE_ORGANIZATION_ID
, POD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS POLL
, PO_DISTRIBUTIONS POD
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, PO_LINE_TYPES PLT
, MTL_SYSTEM_ITEMS MSI
, HR_LOCATIONS HRL
, FND_CURRENCIES FC
, FINANCIALS_SYSTEM_PARAMETERS FSP
, MTL_CATEGORIES MC
, AP_TAX_CODES ATC
WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_HEADER_ID = POLL.PO_HEADER_ID
AND POLL.PO_HEADER_ID = POD.PO_HEADER_ID
AND POL.PO_LINE_ID = POLL.PO_LINE_ID
AND POLL.PO_LINE_ID = POD.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND FSP.ORG_ID = NVL(MSI.ORGANIZATION_ID
, FSP.ORG_ID)
AND POL.CATEGORY_ID = MC.CATEGORY_ID(+)
AND POH.VENDOR_ID = POV.VENDOR_ID
AND POH.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND POLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID
AND POH.CURRENCY_CODE = FC.CURRENCY_CODE(+)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND NVL(POLL.CALCULATE_TAX_FLAG
, 'Y') != 'N'
AND POLL.TAX_CODE_ID = ATC.TAX_ID(+)
AND ATC.TAX_TYPE <> 'TAX_GROUP'