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_AP_INVOICE_V

Product:	IGI - Public Sector Financials International
Description:	Provides invoices which fall within approvers flexfield range and are awaiting secondary approval.
Implementation/DBA Data:	APPS.IGI_AP_INVOICE_V

View Text

SELECT DISTINCT API.INVOICE_ID
, IA.APPROVER_ID
, FU2.USER_NAME
, 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
, 'N'
, API.ROWID
FROM AP_INVOICES API
, AP_BATCHES APB
, FND_USER FU
, FND_USER FU2
, PO_VENDORS POV
, IGI_SAP_INVOICE_CLERK IC
, IGI_SAP_DEPARTMENT_APPROVERS IA
, IGI_SAP_GROUP IG
WHERE API.BATCH_ID = APB.BATCH_ID(+)
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 IA.APPROVER_ID = FU2.USER_ID
AND SYSDATE BETWEEN IA.START_DATE_ACTIVE
AND NVL(IA.END_DATE_ACTIVE
, SYSDATE+1)
AND SYSDATE BETWEEN IC.START_DATE_ACTIVE
AND NVL(IC.END_DATE_ACTIVE
, SYSDATE+1)
AND IA.GROUP_ID = IC.GROUP_ID
AND (EXISTS (SELECT 1
FROM IGI_INVOICE_DISTRIBUTIONS_V APD1
, IGI_SAP_APP_RANGES AR
, GL_CODE_COMBINATIONS CC
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 AR.APPROVER_ID = IA.APPROVER_ID
AND NVL(APD1.IGI_SAP_FLAG
, 'N') = 'N'
AND NVL(APD1.IGI_MPP_STATUS
, 'X') NOT IN ('D'
, 'P')
AND AR.GROUP_ID = IC.GROUP_ID
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')) OR NOT EXISTS (SELECT 1
FROM IGI_SAP_APP_RANGES AR1
WHERE AR1.APPROVER_ID = IA.APPROVER_ID
AND AR1.GROUP_ID = IC.GROUP_ID))

Columns

Name
INVOICE_ID
APPROVER_ID
APPROVER_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
PAYMENT_HOLD
ROW_ID