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_INV_DISTRIBUTIONS_V

Source

View Text - Preformatted


SELECT Distinct api.invoice_id,apd.invoice_line_number, pov.vendor_name, pov.vendor_id, apb.batch_name, apb.batch_id, fu.user_name entered_by, decode(nvl(apd.igi_sap_flag,'N'),'Y', apd.last_updated_by,ia.approver_id) approver_id, nvl(apd.igi_sap_flag,'N') igi_sap_flag, api.created_by, api.invoice_num, api.invoice_date, api.invoice_amount, apd.amount, decode(nvl(apd.igi_mpp_status,'x'),'O', nvl(apd.igi_mpp_exp_ccid,apd.dist_code_combination_id), apd.dist_code_combination_id) dist_code_combination_id, apd.distribution_line_number, lu.displayed_field line_type, apd.dist_rowid, apd.igi_rowid, apd.last_updated_by, apd.last_update_date, apd.last_update_login from ap_invoices_all api, igi_invoice_distributions_v apd, ap_lookup_codes lu, ap_batches_all apb, fnd_user fu, po_vendors pov, igi_sap_invoice_clerk ic, igi_sap_department_approvers ia, igi_sap_group_all ig WHERE api.batch_id = apb.batch_id(+) and api.vendor_id = pov.vendor_id(+) and fu.user_id = api.created_by and apd.line_type_lookup_code = lu.lookup_code and lu.lookup_type = 'INVOICE DISTRIBUTION TYPE' and api.invoice_id = apd.invoice_id and exists (select 'X' from ap_holds_all ah where ah.hold_lookup_code = 'AWAIT_SEC_APP' and ah.invoice_id = api.invoice_id) and ic.clerk_id = api.created_by and ic.group_id = ia.group_id and ig.group_id = ic.group_id and ig.org_id = apd.org_id and nvl(apd.igi_mpp_status, 'x') not in ('D','P') 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 (exists (select 1 from igi_sap_app_ranges ar, gl_code_combinations cc where cc.code_combination_id = decode(nvl(apd.igi_mpp_status,'x'),'O', nvl(apd.igi_mpp_exp_ccid,apd.dist_code_combination_id), apd.dist_code_combination_id) and ar.approver_id = ia.approver_id and ar.group_id = ic.group_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
, APD.INVOICE_LINE_NUMBER
, POV.VENDOR_NAME
, POV.VENDOR_ID
, APB.BATCH_NAME
, APB.BATCH_ID
, FU.USER_NAME ENTERED_BY
, DECODE(NVL(APD.IGI_SAP_FLAG
, 'N')
, 'Y'
, APD.LAST_UPDATED_BY
, IA.APPROVER_ID) APPROVER_ID
, NVL(APD.IGI_SAP_FLAG
, 'N') IGI_SAP_FLAG
, API.CREATED_BY
, API.INVOICE_NUM
, API.INVOICE_DATE
, API.INVOICE_AMOUNT
, APD.AMOUNT
, DECODE(NVL(APD.IGI_MPP_STATUS
, 'X')
, 'O'
, NVL(APD.IGI_MPP_EXP_CCID
, APD.DIST_CODE_COMBINATION_ID)
, APD.DIST_CODE_COMBINATION_ID) DIST_CODE_COMBINATION_ID
, APD.DISTRIBUTION_LINE_NUMBER
, LU.DISPLAYED_FIELD LINE_TYPE
, APD.DIST_ROWID
, APD.IGI_ROWID
, APD.LAST_UPDATED_BY
, APD.LAST_UPDATE_DATE
, APD.LAST_UPDATE_LOGIN
FROM AP_INVOICES_ALL API
, IGI_INVOICE_DISTRIBUTIONS_V APD
, AP_LOOKUP_CODES LU
, AP_BATCHES_ALL APB
, FND_USER FU
, PO_VENDORS POV
, IGI_SAP_INVOICE_CLERK IC
, IGI_SAP_DEPARTMENT_APPROVERS IA
, IGI_SAP_GROUP_ALL IG
WHERE API.BATCH_ID = APB.BATCH_ID(+)
AND API.VENDOR_ID = POV.VENDOR_ID(+)
AND FU.USER_ID = API.CREATED_BY
AND APD.LINE_TYPE_LOOKUP_CODE = LU.LOOKUP_CODE
AND LU.LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
AND API.INVOICE_ID = APD.INVOICE_ID
AND EXISTS (SELECT 'X'
FROM AP_HOLDS_ALL AH
WHERE AH.HOLD_LOOKUP_CODE = 'AWAIT_SEC_APP'
AND AH.INVOICE_ID = API.INVOICE_ID)
AND IC.CLERK_ID = API.CREATED_BY
AND IC.GROUP_ID = IA.GROUP_ID
AND IG.GROUP_ID = IC.GROUP_ID
AND IG.ORG_ID = APD.ORG_ID
AND NVL(APD.IGI_MPP_STATUS
, 'X') NOT IN ('D'
, 'P')
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 (EXISTS (SELECT 1
FROM IGI_SAP_APP_RANGES AR
, GL_CODE_COMBINATIONS CC
WHERE CC.CODE_COMBINATION_ID = DECODE(NVL(APD.IGI_MPP_STATUS
, 'X')
, 'O'
, NVL(APD.IGI_MPP_EXP_CCID
, APD.DIST_CODE_COMBINATION_ID)
, APD.DIST_CODE_COMBINATION_ID)
AND AR.APPROVER_ID = IA.APPROVER_ID
AND AR.GROUP_ID = IC.GROUP_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))