VIEW: APPS.AP_EXPENSE_REPORT_HEADERS_V

Source

View Text - Preformatted

SELECT AERH.ROWID ROW_ID , AERH.ACCOUNTING_DATE ACCOUNTING_DATE , AERH.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID , AERH.ATTRIBUTE1 ATTRIBUTE1 , AERH.ATTRIBUTE10 ATTRIBUTE10 , AERH.ATTRIBUTE11 ATTRIBUTE11 , AERH.ATTRIBUTE12 ATTRIBUTE12 , AERH.ATTRIBUTE13 ATTRIBUTE13 , AERH.ATTRIBUTE14 ATTRIBUTE14 , AERH.ATTRIBUTE15 ATTRIBUTE15 , AERH.ATTRIBUTE2 ATTRIBUTE2 , AERH.ATTRIBUTE3 ATTRIBUTE3 , AERH.ATTRIBUTE4 ATTRIBUTE4 , AERH.ATTRIBUTE5 ATTRIBUTE5 , AERH.ATTRIBUTE6 ATTRIBUTE6 , AERH.ATTRIBUTE7 ATTRIBUTE7 , AERH.ATTRIBUTE8 ATTRIBUTE8 , AERH.ATTRIBUTE9 ATTRIBUTE9 , AERH.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY , AERH.AWT_GROUP_ID AWT_GROUP_ID , AERH.CREATED_BY CREATED_BY , AERH.CREATION_DATE CREATION_DATE , AERH.DEFAULT_CURRENCY_CODE DEFAULT_CURRENCY_CODE , AERH.DEFAULT_EXCHANGE_DATE DEFAULT_EXCHANGE_DATE , AERH.DEFAULT_EXCHANGE_RATE DEFAULT_EXCHANGE_RATE , AERH.DEFAULT_EXCHANGE_RATE_TYPE DEFAULT_EXCHANGE_RATE_TYPE , AERH.DESCRIPTION DESCRIPTION , AERH.DOC_CATEGORY_CODE DOC_CATEGORY_CODE , AERH.EMPLOYEE_CCID EMPLOYEE_CCID , AERH.EMPLOYEE_ID EMPLOYEE_ID , AERH.EXPENSE_CHECK_ADDRESS_FLAG EXPENSE_CHECK_ADDRESS_FLAG , AERH.EXPENSE_REPORT_ID EXPENSE_REPORT_ID , AERH.HOLD_LOOKUP_CODE HOLD_LOOKUP_CODE , AERH.INVOICE_NUM INVOICE_NUM , AERH.LAST_UPDATED_BY LAST_UPDATED_BY , AERH.LAST_UPDATE_DATE LAST_UPDATE_DATE , AERH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , AERH.ORG_ID ORG_ID , AERH.PURGEABLE_FLAG PURGEABLE_FLAG , AERH.REFERENCE_1 REFERENCE_1 , AERH.REFERENCE_2 REFERENCE_2 , AERH.REJECT_CODE REJECT_CODE , AERH.REPORT_HEADER_ID REPORT_HEADER_ID , AERH.SET_OF_BOOKS_ID SET_OF_BOOKS_ID , AERH.SOURCE SOURCE , AERH.TOTAL TOTAL , AERH.VENDOR_ID VENDOR_ID , AERH.VENDOR_SITE_ID VENDOR_SITE_ID , AERH.VOUCHER_NUM VOUCHER_NUM , AERH.VOUCHNO VOUCHNO , AERH.WEEK_END_DATE WEEK_END_DATE , PAP.FULL_NAME EMPLOYEE_NAME , PAP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER , AAG.NAME AWT_GROUP_NAME , AER.REPORT_TYPE REPORT_TYPE , GDCT.USER_CONVERSION_TYPE DISPLAYED_EXCHANGE_RATE_TYPE , ALC1.DISPLAYED_FIELD DISPLAYED_HOLD_CODE , AERH.WORKFLOW_APPROVED_FLAG WORKFLOW_APPROVED_FLAG , decode(AERH.WORKFLOW_APPROVED_FLAG, 'Y','Y', 'A','Y', 'M','Y', 'N') MGMT_REVIEWED_FLAG , decode(AERH.WORKFLOW_APPROVED_FLAG, 'Y','Y', 'A','Y', 'P','Y', 'N') AP_REVIEWED_FLAG , AERH.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY , AERH.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1 , AERH.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2 , AERH.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3 , AERH.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4 , AERH.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5 , AERH.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6 , AERH.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7 , AERH.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8 , AERH.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9 , AERH.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10 , AERH.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11 , AERH.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12 , AERH.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13 , AERH.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14 , AERH.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15 , AERH.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16 , AERH.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17 , AERH.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18 , AERH.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19 , AERH.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20 , AERH.CORE_WF_STATUS_FLAG , AERH.PAYMENT_CROSS_RATE_TYPE , AERH.PAYMENT_CROSS_RATE_DATE , AERH.PAYMENT_CROSS_RATE , AERH.PAYMENT_CURRENCY_CODE , AERH.APPLY_ADVANCES_DEFAULT , AERH.ADVANCE_INVOICE_TO_APPLY , AERH.MAXIMUM_AMOUNT_TO_APPLY , AERH.PREPAY_NUM , AERH.PREPAY_DIST_NUM , AERH.PREPAY_GL_DATE , ap_web_expense_form.get_num_total_violations(aerh.report_header_id) , ap_web_expense_form.get_num_violation_lines(aerh.report_header_id) , ap_web_expense_form.is_employee_active(aerh.employee_id) FROM AP_EXPENSE_REPORT_HEADERS AERH, PER_ALL_PEOPLE_F PAP, AP_AWT_GROUPS AAG, AP_EXPENSE_REPORTS AER, GL_DAILY_CONVERSION_TYPES GDCT, AP_LOOKUP_CODES ALC1 WHERE AERH.EMPLOYEE_ID = PAP.PERSON_ID AND (TRUNC(SYSDATE) BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE) AND DECODE(HR_SECURITY.VIEW_ALL,'Y','TRUE',HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', PAP.PERSON_ID,PAP.PERSON_TYPE_ID,PAP.EMPLOYEE_NUMBER,PAP.APPLICANT_NUMBER)) = 'TRUE' AND AERH.AWT_GROUP_ID = AAG.GROUP_ID(+) AND AERH.EXPENSE_REPORT_ID = AER.EXPENSE_REPORT_ID(+) AND AERH.DEFAULT_EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE(+) AND AERH.HOLD_LOOKUP_CODE = ALC1.LOOKUP_CODE(+) AND ALC1.LOOKUP_TYPE(+) = 'HOLD CODE'

View Text - HTML Formatted

SELECT AERH.ROWID ROW_ID
, AERH.ACCOUNTING_DATE ACCOUNTING_DATE
, AERH.ACCTS_PAY_CODE_COMBINATION_ID ACCTS_PAY_CODE_COMBINATION_ID
, AERH.ATTRIBUTE1 ATTRIBUTE1
, AERH.ATTRIBUTE10 ATTRIBUTE10
, AERH.ATTRIBUTE11 ATTRIBUTE11
, AERH.ATTRIBUTE12 ATTRIBUTE12
, AERH.ATTRIBUTE13 ATTRIBUTE13
, AERH.ATTRIBUTE14 ATTRIBUTE14
, AERH.ATTRIBUTE15 ATTRIBUTE15
, AERH.ATTRIBUTE2 ATTRIBUTE2
, AERH.ATTRIBUTE3 ATTRIBUTE3
, AERH.ATTRIBUTE4 ATTRIBUTE4
, AERH.ATTRIBUTE5 ATTRIBUTE5
, AERH.ATTRIBUTE6 ATTRIBUTE6
, AERH.ATTRIBUTE7 ATTRIBUTE7
, AERH.ATTRIBUTE8 ATTRIBUTE8
, AERH.ATTRIBUTE9 ATTRIBUTE9
, AERH.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AERH.AWT_GROUP_ID AWT_GROUP_ID
, AERH.CREATED_BY CREATED_BY
, AERH.CREATION_DATE CREATION_DATE
, AERH.DEFAULT_CURRENCY_CODE DEFAULT_CURRENCY_CODE
, AERH.DEFAULT_EXCHANGE_DATE DEFAULT_EXCHANGE_DATE
, AERH.DEFAULT_EXCHANGE_RATE DEFAULT_EXCHANGE_RATE
, AERH.DEFAULT_EXCHANGE_RATE_TYPE DEFAULT_EXCHANGE_RATE_TYPE
, AERH.DESCRIPTION DESCRIPTION
, AERH.DOC_CATEGORY_CODE DOC_CATEGORY_CODE
, AERH.EMPLOYEE_CCID EMPLOYEE_CCID
, AERH.EMPLOYEE_ID EMPLOYEE_ID
, AERH.EXPENSE_CHECK_ADDRESS_FLAG EXPENSE_CHECK_ADDRESS_FLAG
, AERH.EXPENSE_REPORT_ID EXPENSE_REPORT_ID
, AERH.HOLD_LOOKUP_CODE HOLD_LOOKUP_CODE
, AERH.INVOICE_NUM INVOICE_NUM
, AERH.LAST_UPDATED_BY LAST_UPDATED_BY
, AERH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AERH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AERH.ORG_ID ORG_ID
, AERH.PURGEABLE_FLAG PURGEABLE_FLAG
, AERH.REFERENCE_1 REFERENCE_1
, AERH.REFERENCE_2 REFERENCE_2
, AERH.REJECT_CODE REJECT_CODE
, AERH.REPORT_HEADER_ID REPORT_HEADER_ID
, AERH.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AERH.SOURCE SOURCE
, AERH.TOTAL TOTAL
, AERH.VENDOR_ID VENDOR_ID
, AERH.VENDOR_SITE_ID VENDOR_SITE_ID
, AERH.VOUCHER_NUM VOUCHER_NUM
, AERH.VOUCHNO VOUCHNO
, AERH.WEEK_END_DATE WEEK_END_DATE
, PAP.FULL_NAME EMPLOYEE_NAME
, PAP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, AAG.NAME AWT_GROUP_NAME
, AER.REPORT_TYPE REPORT_TYPE
, GDCT.USER_CONVERSION_TYPE DISPLAYED_EXCHANGE_RATE_TYPE
, ALC1.DISPLAYED_FIELD DISPLAYED_HOLD_CODE
, AERH.WORKFLOW_APPROVED_FLAG WORKFLOW_APPROVED_FLAG
, DECODE(AERH.WORKFLOW_APPROVED_FLAG
, 'Y'
, 'Y'
, 'A'
, 'Y'
, 'M'
, 'Y'
, 'N') MGMT_REVIEWED_FLAG
, DECODE(AERH.WORKFLOW_APPROVED_FLAG
, 'Y'
, 'Y'
, 'A'
, 'Y'
, 'P'
, 'Y'
, 'N') AP_REVIEWED_FLAG
, AERH.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, AERH.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, AERH.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, AERH.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, AERH.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, AERH.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, AERH.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, AERH.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, AERH.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, AERH.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, AERH.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, AERH.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, AERH.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, AERH.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, AERH.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, AERH.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, AERH.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, AERH.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, AERH.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, AERH.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, AERH.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, AERH.CORE_WF_STATUS_FLAG
, AERH.PAYMENT_CROSS_RATE_TYPE
, AERH.PAYMENT_CROSS_RATE_DATE
, AERH.PAYMENT_CROSS_RATE
, AERH.PAYMENT_CURRENCY_CODE
, AERH.APPLY_ADVANCES_DEFAULT
, AERH.ADVANCE_INVOICE_TO_APPLY
, AERH.MAXIMUM_AMOUNT_TO_APPLY
, AERH.PREPAY_NUM
, AERH.PREPAY_DIST_NUM
, AERH.PREPAY_GL_DATE
, AP_WEB_EXPENSE_FORM.GET_NUM_TOTAL_VIOLATIONS(AERH.REPORT_HEADER_ID)
, AP_WEB_EXPENSE_FORM.GET_NUM_VIOLATION_LINES(AERH.REPORT_HEADER_ID)
, AP_WEB_EXPENSE_FORM.IS_EMPLOYEE_ACTIVE(AERH.EMPLOYEE_ID)
FROM AP_EXPENSE_REPORT_HEADERS AERH
, PER_ALL_PEOPLE_F PAP
, AP_AWT_GROUPS AAG
, AP_EXPENSE_REPORTS AER
, GL_DAILY_CONVERSION_TYPES GDCT
, AP_LOOKUP_CODES ALC1
WHERE AERH.EMPLOYEE_ID = PAP.PERSON_ID
AND (TRUNC(SYSDATE) BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, PAP.PERSON_ID
, PAP.PERSON_TYPE_ID
, PAP.EMPLOYEE_NUMBER
, PAP.APPLICANT_NUMBER)) = 'TRUE'
AND AERH.AWT_GROUP_ID = AAG.GROUP_ID(+)
AND AERH.EXPENSE_REPORT_ID = AER.EXPENSE_REPORT_ID(+)
AND AERH.DEFAULT_EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE(+)
AND AERH.HOLD_LOOKUP_CODE = ALC1.LOOKUP_CODE(+)
AND ALC1.LOOKUP_TYPE(+) = 'HOLD CODE'