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_AP_INVOICE_V

Source

View Text - Preformatted


SELECT distinct api.invoice_id, api.org_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_all apb, fnd_user fu, fnd_user fu2, po_vendors pov, igi_sap_invoice_clerk ic, igi_sap_department_approvers ia, igi_sap_group_all ig, igi_gcc_inst_options_all gcc WHERE api.batch_id = apb.batch_id(+) and api.vendor_id = pov.vendor_id(+) and api.org_id = gcc.org_id and gcc.option_name = 'SIA' and gcc.status_flag = 'Y' and fu.user_id = api.created_by and exists (select 'X' from ap_holds_all 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 ig.org_id = api.org_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))

View Text - HTML Formatted

SELECT DISTINCT API.INVOICE_ID
, API.ORG_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_ALL APB
, FND_USER FU
, FND_USER FU2
, PO_VENDORS POV
, IGI_SAP_INVOICE_CLERK IC
, IGI_SAP_DEPARTMENT_APPROVERS IA
, IGI_SAP_GROUP_ALL IG
, IGI_GCC_INST_OPTIONS_ALL GCC
WHERE API.BATCH_ID = APB.BATCH_ID(+)
AND API.VENDOR_ID = POV.VENDOR_ID(+)
AND API.ORG_ID = GCC.ORG_ID
AND GCC.OPTION_NAME = 'SIA'
AND GCC.STATUS_FLAG = 'Y'
AND FU.USER_ID = API.CREATED_BY
AND EXISTS (SELECT 'X'
FROM AP_HOLDS_ALL 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 IG.ORG_ID = API.ORG_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))