VIEW: APPS.JG_PO_REQ_TAX_LINES_SUMMARY_V

Source

View Text - Preformatted

SELECT prh.requisition_header_id ,prl.requisition_line_id ,prl.requisition_line_id ,prd.distribution_id ,null ,fsp.set_of_books_id ,prh.request_id ,prh.segment1 ,null ,prh.last_update_date ,prl.currency_code ,prl.rate ,fc.minimum_accountable_unit ,fc.precision ,prl.vendor_id ,null ,prv.vendor_name ,prl.vendor_site_id ,prvs.country ,prvs.state ,prvs.county ,prvs.city ,prvs.province ,prvs.zip ,null ,null ,prl.line_num ,plt.line_type ,prl.item_id ,msi.segment1 ,prl.quantity ,prd.req_line_quantity ,prl.unit_price ,null ,null ,prd.code_combination_id ,null ,null ,prl.ussgl_transaction_code ,prl.government_context ,prl.deliver_to_location_id ,prl.destination_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 ,prl.tax_user_override_flag ,atc.tax_rate ,null ,null ,null ,null ,null ,null ,null ,null /*Change to get recovery_rate from document for a tax group.*/ ,prd.recovery_rate ,prd.tax_recovery_override_flag ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,prvs.ap_tax_rounding_rule ,atc.tax_type ,atc.description ,null ,prd.project_id ,prd.task_id ,prd.expenditure_type ,prd.expenditure_organization_id ,prd.expenditure_item_date ,null ,null ,null ,null ,null ,null ,prl.currency_unit_price FROM po_requisition_headers prh ,po_requisition_lines prl ,po_req_distributions prd ,po_vendors prv ,po_vendor_sites prvs ,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 prh.requisition_header_id = prl.requisition_header_id AND prl.requisition_line_id = prd.requisition_line_id AND prl.item_id = msi.inventory_item_id(+) AND fsp.org_id = nvl(msi.organization_id,fsp.org_id) AND prl.category_id = mc.category_id(+) AND prl.vendor_id = prv.vendor_id (+) AND prl.vendor_site_id = prvs.vendor_site_id (+) AND prl.deliver_to_location_id = hrl.location_id AND prl.currency_code = fc.currency_code (+) AND prl.line_type_id = plt.line_type_id AND prl.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 <= prh.last_update_date AND NVL(atg.end_date, prh.last_update_date ) >= prh.last_update_date AND NVL(atg.enabled_flag, 'Y') = 'Y' AND atc.start_date <= prh.last_update_date AND NVL(atc.inactive_date, prh.last_update_date ) >= prh.last_update_date AND NVL(atc.enabled_flag, 'Y') = 'Y' UNION /*For transactions with regular tax code. */ SELECT prh.requisition_header_id ,prl.requisition_line_id ,prl.requisition_line_id ,prd.distribution_id ,null ,fsp.set_of_books_id ,prh.request_id ,prh.segment1 ,null ,prh.last_update_date ,prl.currency_code ,prl.rate ,fc.minimum_accountable_unit ,fc.precision ,prl.vendor_id ,null ,prv.vendor_name ,prl.vendor_site_id ,prvs.country ,prvs.state ,prvs.county ,prvs.city ,prvs.province ,prvs.zip ,null ,null ,prl.line_num ,plt.line_type ,prl.item_id ,msi.segment1 ,prl.quantity ,prd.req_line_quantity ,prl.unit_price ,null ,null ,prd.code_combination_id ,null ,null ,prl.ussgl_transaction_code ,prl.government_context ,prl.deliver_to_location_id ,prl.destination_organization_id ,null ,hrl.country ,null ,null ,hrl.town_or_city ,null ,hrl.postal_code ,null ,null ,null ,null /*Get tax_code_id from document for regular tax code.*/ ,prl.tax_code_id ,null ,prl.tax_user_override_flag ,atc.tax_rate ,null ,null ,null ,null ,null ,null ,null ,null /*Get recovery_rate from document for regular tax code. */ ,prd.recovery_rate ,prd.tax_recovery_override_flag ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,null ,prvs.ap_tax_rounding_rule ,atc.tax_type ,atc.description ,null ,prd.project_id ,prd.task_id ,prd.expenditure_type ,prd.expenditure_organization_id ,prd.expenditure_item_date ,null ,null ,null ,null ,null ,null ,prl.currency_unit_price FROM po_requisition_headers prh ,po_requisition_lines prl ,po_req_distributions prd ,po_vendors prv ,po_vendor_sites prvs ,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 prh.requisition_header_id = prl.requisition_header_id AND prl.requisition_line_id = prd.requisition_line_id AND prl.item_id = msi.inventory_item_id(+) AND fsp.org_id = nvl(msi.organization_id,fsp.org_id) AND prl.category_id = mc.category_id(+) AND prl.vendor_id = prv.vendor_id (+) AND prl.vendor_site_id = prvs.vendor_site_id (+) AND prl.deliver_to_location_id = hrl.location_id AND prl.currency_code = fc.currency_code (+) AND prl.line_type_id = plt.line_type_id AND prl.tax_code_id = atc.tax_id(+) AND atc.tax_type <> 'TAX_GROUP'

View Text - HTML Formatted

SELECT PRH.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRL.REQUISITION_LINE_ID
, PRD.DISTRIBUTION_ID
, NULL
, FSP.SET_OF_BOOKS_ID
, PRH.REQUEST_ID
, PRH.SEGMENT1
, NULL
, PRH.LAST_UPDATE_DATE
, PRL.CURRENCY_CODE
, PRL.RATE
, FC.MINIMUM_ACCOUNTABLE_UNIT
, FC.PRECISION
, PRL.VENDOR_ID
, NULL
, PRV.VENDOR_NAME
, PRL.VENDOR_SITE_ID
, PRVS.COUNTRY
, PRVS.STATE
, PRVS.COUNTY
, PRVS.CITY
, PRVS.PROVINCE
, PRVS.ZIP
, NULL
, NULL
, PRL.LINE_NUM
, PLT.LINE_TYPE
, PRL.ITEM_ID
, MSI.SEGMENT1
, PRL.QUANTITY
, PRD.REQ_LINE_QUANTITY
, PRL.UNIT_PRICE
, NULL
, NULL
, PRD.CODE_COMBINATION_ID
, NULL
, NULL
, PRL.USSGL_TRANSACTION_CODE
, PRL.GOVERNMENT_CONTEXT
, PRL.DELIVER_TO_LOCATION_ID
, PRL.DESTINATION_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
, PRL.TAX_USER_OVERRIDE_FLAG
, ATC.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL /*CHANGE TO GET RECOVERY_RATE
FROM DOCUMENT FOR A TAX GROUP.*/
, PRD.RECOVERY_RATE
, PRD.TAX_RECOVERY_OVERRIDE_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PRVS.AP_TAX_ROUNDING_RULE
, ATC.TAX_TYPE
, ATC.DESCRIPTION
, NULL
, PRD.PROJECT_ID
, PRD.TASK_ID
, PRD.EXPENDITURE_TYPE
, PRD.EXPENDITURE_ORGANIZATION_ID
, PRD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PRL.CURRENCY_UNIT_PRICE
FROM PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS PRD
, PO_VENDORS PRV
, PO_VENDOR_SITES PRVS
, 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 PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND FSP.ORG_ID = NVL(MSI.ORGANIZATION_ID
, FSP.ORG_ID)
AND PRL.CATEGORY_ID = MC.CATEGORY_ID(+)
AND PRL.VENDOR_ID = PRV.VENDOR_ID (+)
AND PRL.VENDOR_SITE_ID = PRVS.VENDOR_SITE_ID (+)
AND PRL.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID
AND PRL.CURRENCY_CODE = FC.CURRENCY_CODE (+)
AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PRL.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 <= PRH.LAST_UPDATE_DATE
AND NVL(ATG.END_DATE
, PRH.LAST_UPDATE_DATE ) >= PRH.LAST_UPDATE_DATE
AND NVL(ATG.ENABLED_FLAG
, 'Y') = 'Y'
AND ATC.START_DATE <= PRH.LAST_UPDATE_DATE
AND NVL(ATC.INACTIVE_DATE
, PRH.LAST_UPDATE_DATE ) >= PRH.LAST_UPDATE_DATE
AND NVL(ATC.ENABLED_FLAG
, 'Y') = 'Y' UNION /*FOR TRANSACTIONS WITH REGULAR TAX CODE. */ SELECT PRH.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRL.REQUISITION_LINE_ID
, PRD.DISTRIBUTION_ID
, NULL
, FSP.SET_OF_BOOKS_ID
, PRH.REQUEST_ID
, PRH.SEGMENT1
, NULL
, PRH.LAST_UPDATE_DATE
, PRL.CURRENCY_CODE
, PRL.RATE
, FC.MINIMUM_ACCOUNTABLE_UNIT
, FC.PRECISION
, PRL.VENDOR_ID
, NULL
, PRV.VENDOR_NAME
, PRL.VENDOR_SITE_ID
, PRVS.COUNTRY
, PRVS.STATE
, PRVS.COUNTY
, PRVS.CITY
, PRVS.PROVINCE
, PRVS.ZIP
, NULL
, NULL
, PRL.LINE_NUM
, PLT.LINE_TYPE
, PRL.ITEM_ID
, MSI.SEGMENT1
, PRL.QUANTITY
, PRD.REQ_LINE_QUANTITY
, PRL.UNIT_PRICE
, NULL
, NULL
, PRD.CODE_COMBINATION_ID
, NULL
, NULL
, PRL.USSGL_TRANSACTION_CODE
, PRL.GOVERNMENT_CONTEXT
, PRL.DELIVER_TO_LOCATION_ID
, PRL.DESTINATION_ORGANIZATION_ID
, NULL
, HRL.COUNTRY
, NULL
, NULL
, HRL.TOWN_OR_CITY
, NULL
, HRL.POSTAL_CODE
, NULL
, NULL
, NULL
, NULL /*GET TAX_CODE_ID
FROM DOCUMENT FOR REGULAR TAX CODE.*/
, PRL.TAX_CODE_ID
, NULL
, PRL.TAX_USER_OVERRIDE_FLAG
, ATC.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL /*GET RECOVERY_RATE
FROM DOCUMENT FOR REGULAR TAX CODE. */
, PRD.RECOVERY_RATE
, PRD.TAX_RECOVERY_OVERRIDE_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PRVS.AP_TAX_ROUNDING_RULE
, ATC.TAX_TYPE
, ATC.DESCRIPTION
, NULL
, PRD.PROJECT_ID
, PRD.TASK_ID
, PRD.EXPENDITURE_TYPE
, PRD.EXPENDITURE_ORGANIZATION_ID
, PRD.EXPENDITURE_ITEM_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PRL.CURRENCY_UNIT_PRICE
FROM PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS PRD
, PO_VENDORS PRV
, PO_VENDOR_SITES PRVS
, 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 PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND FSP.ORG_ID = NVL(MSI.ORGANIZATION_ID
, FSP.ORG_ID)
AND PRL.CATEGORY_ID = MC.CATEGORY_ID(+)
AND PRL.VENDOR_ID = PRV.VENDOR_ID (+)
AND PRL.VENDOR_SITE_ID = PRVS.VENDOR_SITE_ID (+)
AND PRL.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID
AND PRL.CURRENCY_CODE = FC.CURRENCY_CODE (+)
AND PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PRL.TAX_CODE_ID = ATC.TAX_ID(+)
AND ATC.TAX_TYPE <> 'TAX_GROUP'