VIEW: APPS.JG_PO_REL_TAX_LINES_SUMMARY_V

Source

View Text - Preformatted

SELECT por.po_release_id ,null ,poll.line_location_id ,pod.po_distribution_id ,null ,fsp.set_of_books_id ,por.request_id ,null ,null ,por.last_update_date ,por.currency_code ,null ,fc.minimum_accountable_unit ,fc.precision ,por.vendor_id ,null ,pov.vendor_name ,por.vendor_site_id ,povs.country ,povs.state ,povs.county ,null ,null ,povs.zip ,null ,null ,poll.source_line_num ,plt.line_type ,poll.item_id ,msi.segment1 ,poll.quantity ,pod.quantity_ordered ,poll.unit_price ,poll.price_override ,poll.taxable_flag ,pod.code_combination_id ,poll.fob_lookup_code ,null ,poll.ussgl_transaction_code ,poll.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 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_releases_v por, po_line_locations_release_v 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 por.po_release_id = poll.po_release_id AND poll.po_release_id = pod.po_release_id AND poll.po_line_id = pod.po_line_id AND poll.line_location_id = pod.line_location_id AND poll.item_id = msi.inventory_item_id(+) AND fsp.org_id = nvl(msi.organization_id,fsp.org_id) AND poll.category_id = mc.category_id(+) AND por.vendor_id = pov.vendor_id AND por.vendor_site_id = povs.vendor_site_id AND poll.ship_to_location_id = hrl.location_id AND por.currency_code = fc.currency_code(+) AND poll.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 <= por.last_update_date AND NVL(atg.end_date, por.last_update_date) >= por.last_update_date AND NVL(atg.enabled_flag, 'Y') = 'Y' AND atc.start_date <= por.last_update_date AND NVL(atc.inactive_date, por.last_update_date) >= por.last_update_date AND NVL(atc.enabled_flag, 'Y') = 'Y' UNION /* For transactions with a regular tax code (tax_type <> 'TAX_GROUP'). */ SELECT por.po_release_id ,null ,poll.line_location_id ,pod.po_distribution_id ,null ,fsp.set_of_books_id ,por.request_id ,null ,null ,por.last_update_date ,por.currency_code ,null ,fc.minimum_accountable_unit ,fc.precision ,por.vendor_id ,null ,pov.vendor_name ,por.vendor_site_id ,povs.country ,povs.state ,povs.county ,null ,null ,povs.zip ,null ,null ,poll.source_line_num ,plt.line_type ,poll.item_id ,msi.segment1 ,poll.quantity ,pod.quantity_ordered ,poll.unit_price ,poll.price_override ,poll.taxable_flag ,pod.code_combination_id ,poll.fob_lookup_code ,null ,poll.ussgl_transaction_code ,poll.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_code_id directly from document for regular tax code (tax_type <> 'TAX_GROUP'). */ ,poll.tax_code_id ,null ,poll.tax_user_override_flag ,atc.tax_rate ,null ,null ,null ,null ,null ,null ,null ,null /*Get recovery_rate directly from document for regular tax code (tax_type <> '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_releases_v por, po_line_locations_release_v 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 por.po_release_id = poll.po_release_id AND poll.po_release_id = pod.po_release_id AND poll.po_line_id = pod.po_line_id AND poll.line_location_id = pod.line_location_id AND poll.item_id = msi.inventory_item_id(+) AND fsp.org_id = nvl(msi.organization_id,fsp.org_id) AND poll.category_id = mc.category_id(+) AND por.vendor_id = pov.vendor_id AND por.vendor_site_id = povs.vendor_site_id AND poll.ship_to_location_id = hrl.location_id AND por.currency_code = fc.currency_code(+) AND poll.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 POR.PO_RELEASE_ID
, NULL
, POLL.LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID
, NULL
, FSP.SET_OF_BOOKS_ID
, POR.REQUEST_ID
, NULL
, NULL
, POR.LAST_UPDATE_DATE
, POR.CURRENCY_CODE
, NULL
, FC.MINIMUM_ACCOUNTABLE_UNIT
, FC.PRECISION
, POR.VENDOR_ID
, NULL
, POV.VENDOR_NAME
, POR.VENDOR_SITE_ID
, POVS.COUNTRY
, POVS.STATE
, POVS.COUNTY
, NULL
, NULL
, POVS.ZIP
, NULL
, NULL
, POLL.SOURCE_LINE_NUM
, PLT.LINE_TYPE
, POLL.ITEM_ID
, MSI.SEGMENT1
, POLL.QUANTITY
, POD.QUANTITY_ORDERED
, POLL.UNIT_PRICE
, POLL.PRICE_OVERRIDE
, POLL.TAXABLE_FLAG
, POD.CODE_COMBINATION_ID
, POLL.FOB_LOOKUP_CODE
, NULL
, POLL.USSGL_TRANSACTION_CODE
, POLL.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 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_RELEASES_V POR
, PO_LINE_LOCATIONS_RELEASE_V 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 POR.PO_RELEASE_ID = POLL.PO_RELEASE_ID
AND POLL.PO_RELEASE_ID = POD.PO_RELEASE_ID
AND POLL.PO_LINE_ID = POD.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POLL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND FSP.ORG_ID = NVL(MSI.ORGANIZATION_ID
, FSP.ORG_ID)
AND POLL.CATEGORY_ID = MC.CATEGORY_ID(+)
AND POR.VENDOR_ID = POV.VENDOR_ID
AND POR.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND POLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID
AND POR.CURRENCY_CODE = FC.CURRENCY_CODE(+)
AND POLL.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 <= POR.LAST_UPDATE_DATE
AND NVL(ATG.END_DATE
, POR.LAST_UPDATE_DATE) >= POR.LAST_UPDATE_DATE
AND NVL(ATG.ENABLED_FLAG
, 'Y') = 'Y'
AND ATC.START_DATE <= POR.LAST_UPDATE_DATE
AND NVL(ATC.INACTIVE_DATE
, POR.LAST_UPDATE_DATE) >= POR.LAST_UPDATE_DATE
AND NVL(ATC.ENABLED_FLAG
, 'Y') = 'Y' UNION /* FOR TRANSACTIONS WITH A REGULAR TAX CODE (TAX_TYPE <> 'TAX_GROUP'). */ SELECT POR.PO_RELEASE_ID
, NULL
, POLL.LINE_LOCATION_ID
, POD.PO_DISTRIBUTION_ID
, NULL
, FSP.SET_OF_BOOKS_ID
, POR.REQUEST_ID
, NULL
, NULL
, POR.LAST_UPDATE_DATE
, POR.CURRENCY_CODE
, NULL
, FC.MINIMUM_ACCOUNTABLE_UNIT
, FC.PRECISION
, POR.VENDOR_ID
, NULL
, POV.VENDOR_NAME
, POR.VENDOR_SITE_ID
, POVS.COUNTRY
, POVS.STATE
, POVS.COUNTY
, NULL
, NULL
, POVS.ZIP
, NULL
, NULL
, POLL.SOURCE_LINE_NUM
, PLT.LINE_TYPE
, POLL.ITEM_ID
, MSI.SEGMENT1
, POLL.QUANTITY
, POD.QUANTITY_ORDERED
, POLL.UNIT_PRICE
, POLL.PRICE_OVERRIDE
, POLL.TAXABLE_FLAG
, POD.CODE_COMBINATION_ID
, POLL.FOB_LOOKUP_CODE
, NULL
, POLL.USSGL_TRANSACTION_CODE
, POLL.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_CODE_ID DIRECTLY
FROM DOCUMENT FOR REGULAR TAX CODE (TAX_TYPE <> 'TAX_GROUP'). */
, POLL.TAX_CODE_ID
, NULL
, POLL.TAX_USER_OVERRIDE_FLAG
, ATC.TAX_RATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL /*GET RECOVERY_RATE DIRECTLY
FROM DOCUMENT FOR REGULAR TAX CODE (TAX_TYPE <> '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_RELEASES_V POR
, PO_LINE_LOCATIONS_RELEASE_V 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 POR.PO_RELEASE_ID = POLL.PO_RELEASE_ID
AND POLL.PO_RELEASE_ID = POD.PO_RELEASE_ID
AND POLL.PO_LINE_ID = POD.PO_LINE_ID
AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POLL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND FSP.ORG_ID = NVL(MSI.ORGANIZATION_ID
, FSP.ORG_ID)
AND POLL.CATEGORY_ID = MC.CATEGORY_ID(+)
AND POR.VENDOR_ID = POV.VENDOR_ID
AND POR.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND POLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID
AND POR.CURRENCY_CODE = FC.CURRENCY_CODE(+)
AND POLL.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'