VIEW: APPS.JAI_AP_TDS_DISTRIBUTION_V

Source

View Text - Preformatted

SELECT AID.rowid row_id, INVOICE_ID, INVOICE_LINE_NUMBER, DISTRIBUTION_LINE_NUMBER, INVOICE_DISTRIBUTION_ID, DIST_CODE_COMBINATION_ID, AMOUNT, DESCRIPTION, MATCH_STATUS_FLAG, LINE_TYPE_LOOKUP_CODE, NULL TAX_CATEGORY_ID, NULL SECTION_CODE, NULL TAX_CATEGORY_OVERRIDEN FROM AP_INVOICE_DISTRIBUTIONS_ALL AID WHERE LINE_TYPE_LOOKUP_CODE NOT IN ('PREPAY', 'TIPV', 'TERV', 'TAX', 'MISCELLANEOUS') AND NOT EXISTS (SELECT 1 FROM AP_INVOICES_ALL ap_hdrs WHERE AID.INVOICE_ID = ap_hdrs.invoice_id AND SOURCE = 'INDIA TDS') UNION ALL SELECT AIDA.rowid row_id, INVOICE_ID, INVOICE_LINE_NUMBER, DISTRIBUTION_LINE_NUMBER, INVOICE_DISTRIBUTION_ID, DIST_CODE_COMBINATION_ID, AMOUNT, DESCRIPTION, MATCH_STATUS_FLAG, LINE_TYPE_LOOKUP_CODE ,NULL TAX_CATEGORY_ID, NULL SECTION_CODE, NULL TAX_CATEGORY_OVERRIDEN FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA WHERE LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS') AND NOT EXISTS (SELECT 1 FROM JAI_AP_MATCH_INV_TAXES JAMIT, JAI_CMN_TAXES_ALL JCT WHERE JAMIT.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID AND JCT.TAX_ID = JAMIT.TAX_ID AND UPPER(JCT.TAX_TYPE) IN ('Excise', 'Addl. Excise', 'Other Excise', 'EXCISE_EDUCATION_CESS', 'EXCISE_SH_EDU_CESS', 'Customs', 'CUSTOMS_EDUCATION_CESS', 'CUSTOMS_SH_EDU_CESS')) AND NOT EXISTS (SELECT 1 FROM AP_INVOICES_ALL AIA WHERE AIA.invoice_id = AIDA.invoice_id AND SOURCE = 'INDIA TDS')

View Text - HTML Formatted

SELECT AID.ROWID ROW_ID
, INVOICE_ID
, INVOICE_LINE_NUMBER
, DISTRIBUTION_LINE_NUMBER
, INVOICE_DISTRIBUTION_ID
, DIST_CODE_COMBINATION_ID
, AMOUNT
, DESCRIPTION
, MATCH_STATUS_FLAG
, LINE_TYPE_LOOKUP_CODE
, NULL TAX_CATEGORY_ID
, NULL SECTION_CODE
, NULL TAX_CATEGORY_OVERRIDEN
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE LINE_TYPE_LOOKUP_CODE NOT IN ('PREPAY'
, 'TIPV'
, 'TERV'
, 'TAX'
, 'MISCELLANEOUS')
AND NOT EXISTS (SELECT 1
FROM AP_INVOICES_ALL AP_HDRS
WHERE AID.INVOICE_ID = AP_HDRS.INVOICE_ID
AND SOURCE = 'INDIA TDS') UNION ALL SELECT AIDA.ROWID ROW_ID
, INVOICE_ID
, INVOICE_LINE_NUMBER
, DISTRIBUTION_LINE_NUMBER
, INVOICE_DISTRIBUTION_ID
, DIST_CODE_COMBINATION_ID
, AMOUNT
, DESCRIPTION
, MATCH_STATUS_FLAG
, LINE_TYPE_LOOKUP_CODE
, NULL TAX_CATEGORY_ID
, NULL SECTION_CODE
, NULL TAX_CATEGORY_OVERRIDEN
FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE LINE_TYPE_LOOKUP_CODE IN ('MISCELLANEOUS')
AND NOT EXISTS (SELECT 1
FROM JAI_AP_MATCH_INV_TAXES JAMIT
, JAI_CMN_TAXES_ALL JCT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND JCT.TAX_ID = JAMIT.TAX_ID
AND UPPER(JCT.TAX_TYPE) IN ('EXCISE'
, 'ADDL. EXCISE'
, 'OTHER EXCISE'
, 'EXCISE_EDUCATION_CESS'
, 'EXCISE_SH_EDU_CESS'
, 'CUSTOMS'
, 'CUSTOMS_EDUCATION_CESS'
, 'CUSTOMS_SH_EDU_CESS'))
AND NOT EXISTS (SELECT 1
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_ID = AIDA.INVOICE_ID
AND SOURCE = 'INDIA TDS')