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.

[Home] [Help]

View: IGI_SAP_EXCEPTIONS_V

Product:	IGI - Public Sector Financials International
Description:	Provides exceptions raised for invoices that require secondary approval.
Implementation/DBA Data:	APPS.IGI_SAP_EXCEPTIONS_V

View Text

SELECT DISTINCT API.INVOICE_ID
, LU.MEANING
, NULL
, NULL
, API.BATCH_ID
, API.VENDOR_ID
, APB.BATCH_NAME
, POV.VENDOR_NAME
, API.INVOICE_NUM
, API.INVOICE_DATE
, API.INVOICE_AMOUNT
, FU.USER_NAME
, API.CREATED_BY
, API.CREATION_DATE
, API.LAST_UPDATED_BY
, API.LAST_UPDATE_DATE
, API.LAST_UPDATE_LOGIN
, 1
FROM AP_INVOICES API
, AP_BATCHES APB
, IGI_INVOICE_DISTRIBUTIONS_V DIST
, GL_CODE_COMBINATIONS CC2
, FND_USER FU
, PO_VENDORS POV
, IGI_LOOKUPS LU
, IGI_SAP_INVOICE_CLERK IC
, IGI_SAP_DEPARTMENT_APPROVERS IA
, IGI_SAP_GROUP IG
WHERE API.BATCH_ID = APB.BATCH_ID(+)
AND API.INVOICE_ID = DIST.INVOICE_ID
AND CC2.CODE_COMBINATION_ID = DECODE(NVL(DIST.IGI_MPP_STATUS
, 'X')
, 'O'
, NVL(DIST.IGI_MPP_EXP_CCID
, DIST.DIST_CODE_COMBINATION_ID)
, DIST.DIST_CODE_COMBINATION_ID)
AND LU.LOOKUP_CODE = 'NO_FLEX'
AND LU.LOOKUP_TYPE = 'IGI_SIA_APP_STATUS'
AND API.VENDOR_ID = POV.VENDOR_ID(+)
AND FU.USER_ID = API.CREATED_BY
AND EXISTS (SELECT 'X'
FROM AP_HOLDS AH
WHERE AH.HOLD_LOOKUP_CODE = 'AWAIT_SEC_APP'
AND AH.RELEASE_LOOKUP_CODE IS NULL
AND AH.INVOICE_ID = API.INVOICE_ID)
AND IC.GROUP_ID = IG.GROUP_ID
AND IC.CLERK_ID = API.CREATED_BY
AND SYSDATE BETWEEN IC.START_DATE_ACTIVE
AND NVL(IC.END_DATE_ACTIVE
, SYSDATE + 1)
AND IA.GROUP_ID = IC.GROUP_ID
AND NOT EXISTS (SELECT 1
FROM IGI_INVOICE_DISTRIBUTIONS_V APD1
, IGI_SAP_APP_RANGES AR
, IGI_SAP_INVOICE_CLERK IC2
, GL_CODE_COMBINATIONS CC
, IGI_SAP_DEPARTMENT_APPROVERS IA2
WHERE CC.CODE_COMBINATION_ID = DECODE(NVL(APD1.IGI_MPP_STATUS
, 'X')
, 'O'
, NVL(APD1.IGI_MPP_EXP_CCID
, APD1.DIST_CODE_COMBINATION_ID)
, APD1.DIST_CODE_COMBINATION_ID)
AND CC.CODE_COMBINATION_ID = CC2.CODE_COMBINATION_ID
AND IC2.CLERK_ID = IC.CLERK_ID
AND SYSDATE BETWEEN IC2.START_DATE_ACTIVE
AND NVL(IC2.END_DATE_ACTIVE
, SYSDATE + 1)
AND AR.GROUP_ID = IC2.GROUP_ID
AND IA2.GROUP_ID = IC2.GROUP_ID
AND IA2.APPROVER_ID = AR.APPROVER_ID
AND SYSDATE BETWEEN IA2.START_DATE_ACTIVE
AND NVL(IA2.END_DATE_ACTIVE
, SYSDATE + 1)
AND NVL(APD1.IGI_SAP_FLAG
, 'N')='N'
AND NVL(APD1.IGI_MPP_STATUS
, 'X') NOT IN ('D'
, 'P')
AND APD1.INVOICE_ID = API.INVOICE_ID
AND LPAD(NVL(CC.SEGMENT1
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT1_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT1_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT2
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT2_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT2_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT3
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT3_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT3_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT4
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT4_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT4_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT5
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT5_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT5_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT6
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT6_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT6_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT7
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT7_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT7_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT8
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT8_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT8_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT9
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT9_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT9_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT10
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT10_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT10_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT11
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT11_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT11_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT12
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT12_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT12_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT13
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT13_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT13_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT14
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT14_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT14_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT15
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT15_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT15_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT16
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT16_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT16_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT17
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT17_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT17_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT18
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT18_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT18_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT19
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT19_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT19_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT20
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT20_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT20_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT21
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT21_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT21_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT22
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT22_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT22_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT23
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT23_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT23_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT24
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT24_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT24_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT25
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT25_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT25_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT26
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT26_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT26_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT27
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT27_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT27_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT28
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT28_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT28_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT29
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT29_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT29_HIGH
, 'X')
, 25
, '0')
AND LPAD(NVL(CC.SEGMENT30
, 'X')
, 25
, '0') BETWEEN LPAD(NVL(AR.SEGMENT30_LOW
, 'X')
, 25
, '0')
AND LPAD(NVL(AR.SEGMENT30_HIGH
, 'X')
, 25
, '0'))
AND EXISTS (SELECT 1
FROM IGI_SAP_APP_RANGES AR1
WHERE AR1.APPROVER_ID = IA.APPROVER_ID
AND AR1.GROUP_ID = IC.GROUP_ID)
AND NOT EXISTS (SELECT 1
FROM IGI_SAP_DEPARTMENT_APPROVERS AP1
WHERE AP1.GROUP_ID = IC.GROUP_ID
AND SYSDATE BETWEEN AP1.START_DATE_ACTIVE
AND NVL(AP1.END_DATE_ACTIVE
, SYSDATE+1)
AND NOT EXISTS (SELECT 1
FROM IGI_SAP_APP_RANGES RG1
WHERE RG1.GROUP_ID = IC.GROUP_ID
AND RG1.APPROVER_ID = AP1.APPROVER_ID)) UNION SELECT DISTINCT API.INVOICE_ID
, LU.MEANING
, NULL
, NULL
, API.BATCH_ID
, API.VENDOR_ID
, APB.BATCH_NAME
, POV.VENDOR_NAME
, API.INVOICE_NUM
, API.INVOICE_DATE
, API.INVOICE_AMOUNT
, FU.USER_NAME
, API.CREATED_BY
, API.CREATION_DATE
, API.LAST_UPDATED_BY
, API.LAST_UPDATE_DATE
, API.LAST_UPDATE_LOGIN
, 1
FROM AP_INVOICES API
, AP_BATCHES APB
, IGI_LOOKUPS LU
, FND_USER FU
, PO_VENDORS POV
WHERE API.BATCH_ID = APB.BATCH_ID(+)
AND LU.LOOKUP_CODE = 'NO_APP'
AND LU.LOOKUP_TYPE = 'IGI_SIA_APP_STATUS'
AND API.VENDOR_ID = POV.VENDOR_ID(+)
AND FU.USER_ID = API.CREATED_BY
AND EXISTS(SELECT 'X'
FROM AP_HOLDS AH
WHERE AH.HOLD_LOOKUP_CODE = 'AWAIT_SEC_APP'
AND AH.RELEASE_LOOKUP_CODE IS NULL
AND AH.INVOICE_ID = API.INVOICE_ID)
AND NOT EXISTS (SELECT 1
FROM IGI_SAP_INVOICE_CLERK IC1
, IGI_SAP_DEPARTMENT_APPROVERS IA1
, IGI_SAP_GROUP IG1
WHERE IC1.GROUP_ID = IG1.GROUP_ID
AND IC1.CLERK_ID = API.CREATED_BY
AND SYSDATE BETWEEN IA1.START_DATE_ACTIVE
AND NVL(IA1.END_DATE_ACTIVE
, SYSDATE+1)
AND SYSDATE BETWEEN IC1.START_DATE_ACTIVE
AND NVL(IC1.END_DATE_ACTIVE
, SYSDATE+1)
AND IA1.GROUP_ID = IC1.GROUP_ID)

Columns

Name
INVOICE_ID
APPROVAL_STATUS
APPROVER_ID
GROUP_NAME
BATCH_ID
VENDOR_ID
BATCH_NAME
VENDOR_NAME
INVOICE_NUM
INVOICE_DATE
INVOICE_AMOUNT
USER_NAME
CREATED_BY
CREATION_DATE
LAST_UPDATED_BY
LAST_UPDATE_DATE
LAST_UPDATE_LOGIN
ROW_ID