View: IGI_CIS_CERT_INV_PMT_VCH_V

Product:	IGI - Public Sector Financials International
Description:	Displays Invoice and Invoice Payment details along with associated CIS Withholding Tax Certificate details and CIS Payment Voucher details.
Implementation/DBA Data:	APPS.IGI_CIS_CERT_INV_PMT_VCH_V

View Text

SELECT ICIP.ROW_ID
, PV.VENDOR_ID
, PV.VENDOR_NAME
, PV.SEGMENT1
, PV.SET_OF_BOOKS_ID
, PVS.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PVS.ADDRESS_LINE1
, PVS.ADDRESS_LINE2
, PVS.ADDRESS_LINE3
, PVS.ZIP
, ICATR.CERTIFICATE_TYPE
, ICATR.CERTIFICATE_NUMBER
, SUBSTR(ICATR.COMMENTS
, 1
, 50) CERTIFICATE_DESCRIPTION
, ICATR.NI_NUMBER
, AI.INVOICE_NUM
, AI.PAYMENT_STATUS_FLAG
, ICIP.AMOUNT
, ICIP.PAYMENT_NUM
, AC.CHECK_NUMBER
, AC.CHECK_DATE
, SUM(DECODE(AID.AWT_GROUP_ID
, AAG.GROUP_ID
, 0
, NVL(DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'ITEM'
, AID.AMOUNT
, 0)
, 0) ) ) MATERIAL_AMOUNT
, SUM(NVL(DECODE(AID.AWT_GROUP_ID
, AAG.GROUP_ID
, AID.AMOUNT
, 0)
, 0) ) LABOUR_AMOUNT
, ABS(SUM(NVL(DECODE(ATC.NAME
, IGI_CIS_GET_PROFILE.CIS_TAX_CODE
, AID.AMOUNT
, 0)
, 0) )) CIS_AMOUNT
, (NVL(ICIP.AMOUNT
, 0) - SUM(NVL(DECODE(AID.LINE_TYPE_LOOKUP_CODE
, 'TAX'
, AID.AMOUNT
, 0)
, 0) ) ) NET_AMOUNT
, ICIP.PMT_VCH_NUMBER
, ICIP.PMT_VCH_AMOUNT
, ICIP.PMT_VCH_RECEIVED_DATE
, ICIP.PMT_VCH_DESCRIPTION
, ICIP.CREATED_BY
, ICIP.CREATION_DATE
, ICIP.LAST_UPDATE_DATE
, ICIP.LAST_UPDATE_LOGIN
, ICIP.LAST_UPDATED_BY
, ICIP.INVOICE_ID
, ICIP.INVOICE_PAYMENT_ID
FROM AP_TAX_CODES ATC
, AP_AWT_GROUPS AAG
, AP_CHECKS AC
, AP_INVOICES AI
, AP_INVOICE_DISTRIBUTIONS AID
, IGI_CIS_AWT_TAX_RATES ICATR
, IGI_CIS_INVOICE_PAYMENTS ICIP
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
WHERE AI.INVOICE_ID = ICIP.INVOICE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.VENDOR_ID = PVS.VENDOR_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PV.ENABLED_FLAG IN ('Y'
, 'Y')
AND PVS.ALLOW_AWT_FLAG IN ('Y'
, 'Y')
AND ICATR.VENDOR_ID(+) = PVS.VENDOR_ID
AND ICATR.VENDOR_SITE_ID(+) = PVS.VENDOR_SITE_ID
AND ICATR.TAX_NAME(+) = IGI_CIS_GET_PROFILE.CIS_TAX_CODE
AND AID.TAX_CODE_ID(+) = ATC.TAX_ID
AND ( (TRUNC(AC.CHECK_DATE) BETWEEN TRUNC(NVL(ICATR.START_DATE
, AC.CHECK_DATE))
AND TRUNC(NVL(ICATR.END_DATE
, AC.CHECK_DATE))
AND NVL(ICATR.PRIORITY
, -1) = (SELECT NVL(MIN(ICATR2.PRIORITY)
, -1)
FROM IGI_CIS_AWT_TAX_RATES ICATR2
WHERE ICATR2.VENDOR_ID = PVS.VENDOR_ID
AND ICATR2.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND ICATR2.TAX_NAME = IGI_CIS_GET_PROFILE.CIS_TAX_CODE
AND TRUNC(AC.CHECK_DATE) >= TRUNC(NVL(ICATR2.START_DATE
, AC.CHECK_DATE))
AND TRUNC(AC.CHECK_DATE) <= TRUNC(NVL(ICATR2.END_DATE
, AC.CHECK_DATE)) ) ) OR (TRUNC(AC.CHECK_DATE) > TRUNC(NVL(ICATR.END_DATE
, AC.CHECK_DATE))
AND NOT EXISTS (SELECT 'X'
FROM IGI_CIS_AWT_TAX_RATES ICATR3
WHERE ICATR3.VENDOR_ID = PVS.VENDOR_ID
AND ICATR3.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND ICATR3.TAX_NAME = IGI_CIS_GET_PROFILE.CIS_TAX_CODE
AND TRUNC(NVL(ICATR3.START_DATE
, AC.CHECK_DATE)) > TRUNC(NVL(ICATR.END_DATE
, AC.CHECK_DATE)) ) ) )
AND EXISTS (SELECT 'X'
FROM AP_CHECKS AC2
WHERE AC2.CHECK_ID = AC.CHECK_ID
AND NOT (VOID_DATE IS NOT NULL)
AND TRUNC(AC2.CHECK_DATE) >= TRUNC(NVL(ICATR.START_DATE
, AC2.CHECK_DATE)) )
AND AC.CHECK_ID = ICIP.CHECK_ID
AND AID.INVOICE_ID = AI.INVOICE_ID
AND AAG.GROUP_ID = AI.AWT_GROUP_ID
AND AAG.GROUP_ID = PVS.AWT_GROUP_ID
AND AAG.NAME = IGI_CIS_GET_PROFILE.CIS_TAX_GROUP GROUP BY ICIP.ROW_ID
, PV.VENDOR_ID
, PV.VENDOR_NAME
, PV.SEGMENT1
, PV.SET_OF_BOOKS_ID
, PVS.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, PVS.ADDRESS_LINE1
, PVS.ADDRESS_LINE2
, PVS.ADDRESS_LINE3
, PVS.ZIP
, ICATR.CERTIFICATE_TYPE
, ICATR.CERTIFICATE_NUMBER
, ICATR.COMMENTS
, ICATR.NI_NUMBER
, ICIP.AMOUNT
, ICIP.PAYMENT_NUM
, AC.CHECK_NUMBER
, AC.CHECK_DATE
, AI.INVOICE_NUM
, AI.PAYMENT_STATUS_FLAG
, ICIP.PMT_VCH_NUMBER
, ICIP.PMT_VCH_AMOUNT
, ICIP.PMT_VCH_RECEIVED_DATE
, ICIP.PMT_VCH_DESCRIPTION
, ICIP.CREATED_BY
, ICIP.CREATION_DATE
, ICIP.LAST_UPDATE_DATE
, ICIP.LAST_UPDATE_LOGIN
, ICIP.LAST_UPDATED_BY
, ICIP.INVOICE_ID
, ICIP.INVOICE_PAYMENT_ID

Columns