View: JAI_PO_RCV_TAXES_V

Product:	JA - Asia/Pacific Localizations
Description:	View for PO RCV taxes
Implementation/DBA Data:	APPS.JAI_PO_RCV_TAXES_V

View Text

SELECT 'RCV_MATCHING' MATCH_TYPE
, 'NON_PPA' SOURCE
, TO_CHAR(RT.TRANSACTION_ID) TRX_ID
, JRL.VENDOR_ID
, JRL.TAX_ID
, JRL.TAX_AMOUNT TAX_AMOUNT
, JRL.CURRENCY
, JRL.TAX_TYPE TAX_TYPE
, JRL.TAX_LINE_NO TAX_LINE_NO
, JRL.PRECEDENCE_1
, JRL.PRECEDENCE_2
, JRL.PRECEDENCE_3
, JRL.PRECEDENCE_4
, JRL.PRECEDENCE_5
, JRL.PRECEDENCE_6
, JRL.PRECEDENCE_7
, JRL.PRECEDENCE_8
, JRL.PRECEDENCE_9
, JRL.PRECEDENCE_10
, NVL(JRL.MODVAT_FLAG
, 'Y') MODVAT_FLAG
, NVL(JCTA.INCLUSIVE_TAX_FLAG
, 'N') INC_TAX_FLAG
, JCTA.VAT_FLAG VAT_FLAG
, NVL(JCTA.ADHOC_FLAG
, 'N') ADHOC_FLAG
, NVL(JCTA.REVERSE_CHARGE_FLAG
, 'N') REVERSE_CHARGE_FLAG
FROM JAI_RCV_LINE_TAXES JRL
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, JAI_CMN_TAXES_ALL JCTA
WHERE JRL.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND JCTA.TAX_ID = JRL.TAX_ID
AND NVL(UPPER(JRL.TAX_TYPE)
, 'TDS') NOT IN ( 'TDS'
, 'CVD'
, 'ADDITIONAL_CVD'
, 'CUSTOMS'
, 'CUSTOMS_SH_EDU_CESS'
, 'CUSTOMS_EDUCATION_CESS'
, 'CVD_SH_EDU_CESS'
, 'CVD_EDUCATION_CESS' ) UNION SELECT 'RCV_MATCHING' MATCH_TYPE
, 'PPA' SOURCE
, TO_CHAR(RT.TRANSACTION_ID) TRX_ID
, JRLC.VENDOR_ID
, JRL.TAX_ID
, (JRL.MODIFIED_TAX_AMOUNT - JRL.ORIGINAL_TAX_AMOUNT) TAX_AMOUNT
, JRL.CURRENCY_CODE CURRENCY
, JRL.TAX_TYPE TAX_TYPE
, JRL.TAX_LINE_NO TAX_LINE_NO
, JRLT.PRECEDENCE_1
, JRLT.PRECEDENCE_2
, JRLT.PRECEDENCE_3
, JRLT.PRECEDENCE_4
, JRLT.PRECEDENCE_5
, JRLT.PRECEDENCE_6
, JRLT.PRECEDENCE_7
, JRLT.PRECEDENCE_8
, JRLT.PRECEDENCE_9
, JRLT.PRECEDENCE_10
, JRL.RECOVERABLE_FLAG MODVAT_FLAG
, NVL(JCTA.INCLUSIVE_TAX_FLAG
, 'N') INC_TAX_FLAG
, JCTA.VAT_FLAG VAT_FLAG
, NVL(JCTA.ADHOC_FLAG
, 'N') ADHOC_FLAG
, NVL(JCTA.REVERSE_CHARGE_FLAG
, 'N') REVERSE_CHARGE_FLAG
FROM JAI_RETRO_TAX_CHANGES JRL
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, JAI_RETRO_LINE_CHANGES JRLC
, JAI_RCV_LINE_TAXES JRLT
, JAI_CMN_TAXES_ALL JCTA
WHERE JRLC.DOC_LINE_ID = RSL.SHIPMENT_LINE_ID
AND JRLC.LINE_CHANGE_ID = JRL.LINE_CHANGE_ID
AND JRLT.SHIPMENT_LINE_ID = JRLC.DOC_LINE_ID
AND JRLT.TAX_LINE_NO = JRL.TAX_LINE_NO
AND JRLC.DOC_VERSION_NUMBER = (SELECT MAX(DOC_VERSION_NUMBER)
FROM JAI_RETRO_LINE_CHANGES
WHERE DOC_TYPE = 'RECEIPT'
AND DOC_LINE_ID = JRLC.DOC_LINE_ID )
AND JRLC.DOC_TYPE = 'RECEIPT'
AND JRL.TAX_ID = JCTA.TAX_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND NVL(UPPER(JRL.TAX_TYPE)
, 'TDS') NOT IN ( 'TDS'
, 'CVD'
, 'ADDITIONAL_CVD'
, 'CUSTOMS'
, 'CUSTOMS_SH_EDU_CESS'
, 'CUSTOMS_EDUCATION_CESS'
, 'CVD_SH_EDU_CESS'
, 'CVD_EDUCATION_CESS' )
AND NVL(JRL.THIRD_PARTY_FLAG
, 'N') = 'N' UNION SELECT 'PO_MATCHING' MATCH_TYPE
, 'NON_PPA' SOURCE
, TO_CHAR(JPT.LINE_LOCATION_ID) TRX_ID
, JPT.VENDOR_ID
, JPT.TAX_ID
, JPT.TAX_AMOUNT
, JPT.CURRENCY
, JPT.TAX_TYPE
, JPT.TAX_LINE_NO
, JPT.PRECEDENCE_1
, JPT.PRECEDENCE_2
, JPT.PRECEDENCE_3
, JPT.PRECEDENCE_4
, JPT.PRECEDENCE_5
, JPT.PRECEDENCE_6
, JPT.PRECEDENCE_7
, JPT.PRECEDENCE_8
, JPT.PRECEDENCE_9
, JPT.PRECEDENCE_10
, NVL(JPT.MODVAT_FLAG
, 'Y') MODVAT_FLAG
, NVL(JCTA.INCLUSIVE_TAX_FLAG
, 'N') INC_TAX_FLAG
, NVL(JCTA.VAT_FLAG
, 'N') VAT_FLAG
, NVL(JCTA.ADHOC_FLAG
, 'N') ADHOC_FLAG
, NVL(JCTA.REVERSE_CHARGE_FLAG
, 'N') REVERSE_CHARGE_FLAG
FROM JAI_PO_TAXES JPT
, JAI_CMN_TAXES_ALL JCTA
WHERE JCTA.TAX_ID = JPT.TAX_ID
AND NVL(UPPER(JPT.TAX_TYPE)
, 'A') NOT IN('TDS'
, 'CVD'
, 'ADDITIONAL_CVD'
, 'CUSTOMS'
, 'CUSTOMS_SH_EDU_CESS'
, 'CUSTOMS_EDUCATION_CESS'
, 'CVD_SH_EDU_CESS'
, 'CVD_EDUCATION_CESS' ) UNION SELECT 'PO_MATCHING' MATCH_TYPE
, 'PPA' SOURCE
, TO_CHAR(JRLC.LINE_LOCATION_ID) TRX_ID
, JRLC.VENDOR_ID
, JRL.TAX_ID
, (JRL.MODIFIED_TAX_AMOUNT - JRL.ORIGINAL_TAX_AMOUNT) TAX_AMOUNT
, JRL.CURRENCY_CODE CURRENCY
, JRL.TAX_TYPE TAX_TYPE
, JRL.TAX_LINE_NO TAX_LINE_NO
, JPT.PRECEDENCE_1
, JPT.PRECEDENCE_2
, JPT.PRECEDENCE_3
, JPT.PRECEDENCE_4
, JPT.PRECEDENCE_5
, JPT.PRECEDENCE_6
, JPT.PRECEDENCE_7
, JPT.PRECEDENCE_8
, JPT.PRECEDENCE_9
, JPT.PRECEDENCE_10
, JRL.RECOVERABLE_FLAG MODVAT_FLAG
, NVL(JCTA.INCLUSIVE_TAX_FLAG
, 'N') INC_TAX_FLAG
, NVL(JCTA.VAT_FLAG
, 'N') VAT_FLAG
, NVL(JCTA.ADHOC_FLAG
, 'N') ADHOC_FLAG
, NVL(JCTA.REVERSE_CHARGE_FLAG
, 'N') REVERSE_CHARGE_FLAG
FROM JAI_RETRO_TAX_CHANGES JRL
, JAI_RETRO_LINE_CHANGES JRLC
, JAI_PO_TAXES JPT
, JAI_CMN_TAXES_ALL JCTA
WHERE JRLC.LINE_LOCATION_ID = JPT.LINE_LOCATION_ID
AND JRL.TAX_LINE_NO = JPT.TAX_LINE_NO
AND JRLC.LINE_CHANGE_ID = JRL.LINE_CHANGE_ID
AND JRLC.DOC_VERSION_NUMBER = (SELECT MAX(DOC_VERSION_NUMBER)
FROM JAI_RETRO_LINE_CHANGES
WHERE DOC_TYPE IN ('STANDARD PO'
, 'RELEASE')
AND DOC_LINE_ID = JRLC.DOC_LINE_ID )
AND JRLC.DOC_TYPE IN ('STANDARD PO'
, 'RELEASE')
AND JRL.TAX_ID = JCTA.TAX_ID
AND NVL(UPPER(JRL.TAX_TYPE)
, 'TDS') NOT IN ('TDS'
, 'CVD'
, 'ADDITIONAL_CVD'
, 'CUSTOMS'
, 'CUSTOMS_SH_EDU_CESS'
, 'CUSTOMS_EDUCATION_CESS'
, 'CVD_SH_EDU_CESS'
, 'CVD_EDUCATION_CESS' )
AND NVL(JRL.THIRD_PARTY_FLAG
, 'N') = 'N' UNION SELECT 'PAY_ON_RECEIPT' MATCH_TYPE
, 'N_ASBN' SOURCE
, TO_CHAR(JPT.LINE_LOCATION_ID) TRX_ID
, JPT.VENDOR_ID
, JPT.TAX_ID
, JPT.TAX_AMOUNT
, JPT.CURRENCY
, JPT.TAX_TYPE
, JPT.TAX_LINE_NO
, JPT.PRECEDENCE_1
, JPT.PRECEDENCE_2
, JPT.PRECEDENCE_3
, JPT.PRECEDENCE_4
, JPT.PRECEDENCE_5
, JPT.PRECEDENCE_6
, JPT.PRECEDENCE_7
, JPT.PRECEDENCE_8
, JPT.PRECEDENCE_9
, JPT.PRECEDENCE_10
, JPT.MODVAT_FLAG
, NVL(JCTA.INCLUSIVE_TAX_FLAG
, 'N') INC_TAX_FLAG
, JCTA.VAT_FLAG VAT_FLAG
, NVL(JCTA.ADHOC_FLAG
, 'N') ADHOC_FLAG
, NVL(JCTA.REVERSE_CHARGE_FLAG
, 'N')REVERSE_CHARGE_FLAG
FROM JAI_PO_TAXES JPT
, JAI_CMN_TAXES_ALL JCTA
WHERE JCTA.TAX_ID = JPT.TAX_ID
AND NVL(UPPER(JPT.TAX_TYPE)
, 'A') NOT IN ( 'TDS'
, 'CVD'
, 'ADDITIONAL_CVD'
, 'CUSTOMS'
, 'CUSTOMS_SH_EDU_CESS'
, 'CUSTOMS_EDUCATION_CESS'
, 'CVD_SH_EDU_CESS'
, 'CVD_EDUCATION_CESS' ) UNION SELECT 'PAY_ON_RECEIPT' MATCH_TYPE
, 'ASBN' SOURCE
, LINES.PO_LINE_LOCATION_ID ||'_' ||LINES.SHIPMENT_NUMBER TRX_ID
, TAXES.VENDOR_ID
, TAXES.TAX_ID
, TAXES.TAX_AMT TAX_AMOUNT
, TAXES.CURRENCY_CODE CURRENCY
, TAXES.TAX_TYPE
, TAXES.TAX_LINE_NO
, TAXES.PRECEDENCE_1
, TAXES.PRECEDENCE_2
, TAXES.PRECEDENCE_3
, TAXES.PRECEDENCE_4
, TAXES.PRECEDENCE_5
, TAXES.PRECEDENCE_6
, TAXES.PRECEDENCE_7
, TAXES.PRECEDENCE_8
, TAXES.PRECEDENCE_9
, TAXES.PRECEDENCE_10
, TAXES.MODVAT_FLAG
, NVL(JCTA.INCLUSIVE_TAX_FLAG
, 'N') INC_TAX_FLAG
, JCTA.VAT_FLAG VAT_FLAG
, NVL(JCTA.ADHOC_FLAG
, 'N') ADHOC_FLAG
, NVL(JCTA.REVERSE_CHARGE_FLAG
, 'N') REVERSE_CHARGE_FLAG
FROM JAI_CMN_LINES LINES
, JAI_CMN_DOCUMENT_TAXES TAXES
, JAI_CMN_TAXES_ALL JCTA
WHERE LINES.CMN_LINE_ID = TAXES.SOURCE_DOC_LINE_ID
AND TAXES.SOURCE_DOC_TYPE = 'ASBN'
AND JCTA.TAX_ID = TAXES.TAX_ID
AND NVL(UPPER(TAXES.TAX_TYPE)
, 'A') NOT IN ( 'TDS'
, 'CVD'
, 'ADDITIONAL_CVD'
, 'CUSTOMS'
, 'CUSTOMS_SH_EDU_CESS'
, 'CUSTOMS_EDUCATION_CESS'
, 'CVD_SH_EDU_CESS'
, 'CVD_EDUCATION_CESS')

Columns