eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IGI_CIS_CERT_INV_PMT_VCH_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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