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_SAP_EXCEPTIONS_V

Source

View Text - Preformatted


SELECT api.invoice_id, apl.line_number,apd.distribution_line_number, 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,api.org_id FROM AP_BATCHES_ALL APB, AP_INVOICES API, ap_invoice_lines_all apl, ap_invoice_distributions_all apd, FND_USER FU, IGI_LOOKUPS LU, PO_VENDORS POV WHERE api.batch_id = apb.batch_id(+) and api.invoice_id = apl.invoice_id and apl.invoice_id = apd.invoice_id and apl.line_number = apd.invoice_line_number 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_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 (( lu.lookup_code = 'NO_FLEX' and exists (select 1 from igi_sap_invoice_clerk ic, igi_sap_department_approvers ia,igi_sap_group_all ig1 where ic.clerk_id = api.created_by and ia.group_id = ic.group_id and ig1.org_id = api.org_id and ig1.group_id = ic.group_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 not exists (select 1 from igi_invoice_distributions_v apd1, gl_code_combinations cc , igi_sap_app_ranges ar, igi_sap_invoice_clerk ic2, igi_sap_department_approvers ia2,igi_sap_group_all ig where apd1.invoice_id = api.invoice_id and apd1.invoice_line_number = apl.line_number and apd1.distribution_line_number = apd.distribution_line_number and nvl(apd1.igi_sap_flag,'N') = 'N' and nvl(apd1.igi_mpp_status,'x') not in ('D','P') and ic2.clerk_id = ic.clerk_id and ia2.group_id = ic2.group_id and ig.org_id = apd1.org_id and ig.group_id = ar.group_id and sysdate between ia2.start_date_active and nvl(ia2.end_date_active,sysdate+1) and sysdate between ic2.start_date_active and nvl(ic2.end_date_active,sysdate+1) and ar.group_id = ic2.group_id and ar.approver_id = ia2.approver_id and 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 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 )) )) OR ( lu.lookup_code = 'NO_APP' 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 ig1.org_id = api.org_id and ic1.clerk_id = api.created_by and ia1.group_id = ic1.group_id 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)) ))

View Text - HTML Formatted

SELECT API.INVOICE_ID
, APL.LINE_NUMBER
, APD.DISTRIBUTION_LINE_NUMBER
, 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
, API.ORG_ID
FROM AP_BATCHES_ALL APB
, AP_INVOICES API
, AP_INVOICE_LINES_ALL APL
, AP_INVOICE_DISTRIBUTIONS_ALL APD
, FND_USER FU
, IGI_LOOKUPS LU
, PO_VENDORS POV
WHERE API.BATCH_ID = APB.BATCH_ID(+)
AND API.INVOICE_ID = APL.INVOICE_ID
AND APL.INVOICE_ID = APD.INVOICE_ID
AND APL.LINE_NUMBER = APD.INVOICE_LINE_NUMBER
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_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 (( LU.LOOKUP_CODE = 'NO_FLEX'
AND EXISTS (SELECT 1
FROM IGI_SAP_INVOICE_CLERK IC
, IGI_SAP_DEPARTMENT_APPROVERS IA
, IGI_SAP_GROUP_ALL IG1
WHERE IC.CLERK_ID = API.CREATED_BY
AND IA.GROUP_ID = IC.GROUP_ID
AND IG1.ORG_ID = API.ORG_ID
AND IG1.GROUP_ID = IC.GROUP_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 NOT EXISTS (SELECT 1
FROM IGI_INVOICE_DISTRIBUTIONS_V APD1
, GL_CODE_COMBINATIONS CC
, IGI_SAP_APP_RANGES AR
, IGI_SAP_INVOICE_CLERK IC2
, IGI_SAP_DEPARTMENT_APPROVERS IA2
, IGI_SAP_GROUP_ALL IG
WHERE APD1.INVOICE_ID = API.INVOICE_ID
AND APD1.INVOICE_LINE_NUMBER = APL.LINE_NUMBER
AND APD1.DISTRIBUTION_LINE_NUMBER = APD.DISTRIBUTION_LINE_NUMBER
AND NVL(APD1.IGI_SAP_FLAG
, 'N') = 'N'
AND NVL(APD1.IGI_MPP_STATUS
, 'X') NOT IN ('D'
, 'P')
AND IC2.CLERK_ID = IC.CLERK_ID
AND IA2.GROUP_ID = IC2.GROUP_ID
AND IG.ORG_ID = APD1.ORG_ID
AND IG.GROUP_ID = AR.GROUP_ID
AND SYSDATE BETWEEN IA2.START_DATE_ACTIVE
AND NVL(IA2.END_DATE_ACTIVE
, SYSDATE+1)
AND SYSDATE BETWEEN IC2.START_DATE_ACTIVE
AND NVL(IC2.END_DATE_ACTIVE
, SYSDATE+1)
AND AR.GROUP_ID = IC2.GROUP_ID
AND AR.APPROVER_ID = IA2.APPROVER_ID
AND 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 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 )) )) OR ( LU.LOOKUP_CODE = 'NO_APP'
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 IG1.ORG_ID = API.ORG_ID
AND IC1.CLERK_ID = API.CREATED_BY
AND IA1.GROUP_ID = IC1.GROUP_ID
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)) ))