VIEW: APPS.JAI_PO_RCV_TAXES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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