— Criteria For expense report selection during expense report export
— Criteria for this cursor is:
— Expense status code should not be ‘ERROR’ or ‘PEND_HOLDS_CLEARANCE’ or ‘HOLD_PENDING_RECEIPTS’
— Vouchno = 0
— XH.hold_lookup_code is null
SELECT XH.report_header_id report_header_id,
NVL(emps.employee_id, -1) employee_id,
emps.employee_num employee_number,
XH.week_end_date week_end_date,
NVL(XH.invoice_num, ”) invoice_num,
TO_CHAR(ap_utilities_pkg.ap_round_currency(XH.total, XH.default_currency_code)) total,
NVL(XH.description, ”) description,
substrb(rtrim(emps.last_name
|| ‘, ‘
|| emps.first_name
|| DECODE(people.middle_names, NULL, ”, ‘ ‘)
|| people.middle_names), 1, 240) name,
NVL(locs.location_code, ”) location_code,
locs.address_line_1 address_line_1,
locs.address_line_2 address_line_2,
locs.address_line_3 address_line_3,
locs.town_or_city city,
DECODE(locs.STYLE, ‘CA’, ”, ‘CA_GLB’, ”, NVL(locs.region_2, ”)) state,
locs.postal_code postal_code,
DECODE(locs.STYLE, ‘US’, ”, ‘US_GLB’, ”, ‘IE’, ”, ‘IE_GLB’, ”, ‘GB’, ”, ‘CA’, NVL(locs.REGION_1, ”), ‘JP’, NVL(locs.REGION_1, ”), NVL(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE, locs.REGION_1), ”)) province,
DECODE(locs.STYLE, ‘US’, NVL(locs.REGION_1, ”), ‘US_GLB’, NVL(locs.REGION_1, ”), ‘IE’, NVL(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE, locs.REGION_1), ”), ‘IE_GLB’, NVL(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE, locs.REGION_1), ”), ‘GB’, NVL(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE, locs.REGION_1), ”), ”) county,
locs.country,
NVL(V.vendor_id, -1) vendor_id,
NVL(XH.vendor_id, -1) header_vendor_id,
–nvl(XH.hold_lookup_code, ”) hold_lookup_code,
–nvl(l1.displayed_field, ”) nls_hold_code,
–l1.description hold_description,
XH.created_by created_by,
XH.default_currency_code default_currency_code,
NVL(XH.default_exchange_rate_type, ”) default_exchange_rate_type,
NVL(XH.default_exchange_rate,-1) default_exchange_rate,
NVL(TO_CHAR(XH.default_exchange_date), ”) default_exchange_date,
NVL(XH.accts_pay_code_combination_id, -1) accts_pay_ccid,
XH.set_of_books_id set_of_books_id,
XH.accounting_date accounting_date,
NVL(XH.vendor_site_id, -1) header_vendor_site_id,
NVL(XH.apply_advances_default, ‘N’) apply_advances_flag,
NVL(XH.advance_invoice_to_apply, -1) advance_invoice_to_apply,
TO_CHAR(NVL(XH.maximum_amount_to_apply, XH.amt_due_employee)) amount_want_to_apply,
XH.expense_check_address_flag home_or_office,
NVL(emps.employee_id, -1) current_emp_id,
XH.voucher_num voucher_num,
” base_amount,
NVL(XH.doc_category_code, ”) doc_category_code,
NVL(XH.reference_1, ”) reference_1,
XH.reference_2 reference_2,
NVL(TO_CHAR(XH.awt_group_id), ”) awt_group_id,
XH.global_attribute1,
XH.global_attribute2,
XH.global_attribute3,
XH.global_attribute4,
XH.global_attribute5,
XH.global_attribute6,
XH.global_attribute7,
XH.global_attribute8,
XH.global_attribute9,
XH.global_attribute10,
XH.global_attribute11,
XH.global_attribute12,
XH.global_attribute13,
XH.global_attribute14,
XH.global_attribute15,
XH.global_attribute16,
XH.global_attribute17,
XH.global_attribute18,
XH.global_attribute19,
XH.global_attribute20,
XH.global_attribute_category,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute1), ”) attribute1,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute2), ”) attribute2,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute3), ”) attribute3,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute4), ”) attribute4,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute5), ”) attribute5,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute6), ”) attribute6,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute7), ”) attribute7,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute8), ”) attribute8,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute9), ”) attribute9,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute10), ”) attribute10,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute11), ”) attribute11,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute12), ”) attribute12,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute13), ”) attribute13,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute14), ”) attribute14,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute15), ”) attribute15,
NVL(DECODE(:p_transfer_flag, ‘Y’, XH.attribute_category), ”) attribute_category,
NVL(XH.payment_currency_code, XH.default_currency_code) payment_currency_code,
NVL(XH.payment_cross_rate_type, ”) payment_cross_rate_type,
NVL(XH.payment_cross_rate_date, XH.week_end_date) payment_cross_rate_date,
NVL(XH.payment_cross_rate, 1) payment_cross_rate,
NVL(XH.prepay_num, ”) prepay_num,
NVL(XH.prepay_dist_num, ”) prepay_dist_num,
NVL(TO_CHAR(XH.prepay_gl_date), ”) prepay_gl_date,
NVL(xh.paid_on_behalf_employee_id, ”) paid_on_behalf_employee_id,
TO_CHAR(NVL(xh.amt_due_employee, TO_CHAR(0))) amt_due_employee,
TO_CHAR(NVL(xh.amt_due_ccard_company, TO_CHAR(0))) amt_due_ccard_company,
substrb(rtrim(DECODE(people.per_information18, NULL, DECODE(people.per_information19, NULL, NULL, people.per_information19), people.per_information18
|| ‘, ‘
|| people.per_information19)), 1, 240) per_information18_19,
people.per_information_category per_information_category,
XH.source source,
:p_group_id group_id,
locs.style style,
XH.org_id org_id,
” invoice_id,
” invoice_type_lookup_code,
” gl_date,
” alternate_name,
” amount_app_to_discount,
V.payment_method_lookup_code,
emps.is_contingent
FROM ap_expense_report_headers_all XH,
hr_locations locs,
per_all_people_f people,
(SELECT h.employee_id,
h.full_name,
h.employee_num,
h.organization_id,
h.last_name,
h.first_name,
h.business_group_id,
h.location_id,
‘N’ is_contingent
FROM per_employees_x h
WHERE AP_WEB_DB_HR_INT_PKG.isPersonCwk(h.employee_id)=’N’
UNION ALL
SELECT h.person_id employee_id,
h.full_name,
h.npw_number employee_num,
h.organization_id,
h.last_name,
h.first_name,
h.business_group_id,
h.location_id,
‘Y’ is_contingent
FROM PER_CONT_WORKERS_CURRENT_X h
) emps,
ap_suppliers V
–ap_lookup_codes l1
WHERE vouchno = 0
AND XH.employee_id = V.employee_id(+)
AND XH.employee_id = emps.employee_id(+)
AND (TRUNC(sysdate) BETWEEN people.effective_start_date(+) AND people.effective_end_date(+))
AND ((emps.business_group_id IS NULL)
OR (emps.business_group_id IN
(SELECT NVL(FSP.business_group_id, 0) FROM financials_system_params_all FSP
)))
AND emps.employee_id = people.person_id(+)
AND emps.location_id = locs.location_id(+)
AND xh.report_header_id = 693624
AND DECODE(XH.source, ‘CREDIT CARD’, ‘SelfService’, ‘Both Pay’, ‘SelfService’, XH.source) = :p_source
AND NVL(XH.expense_status_code, ‘NO ERROR’) NOT IN (‘ERROR’, ‘PEND_HOLDS_CLEARANCE’, ‘HOLD_PENDING_RECEIPTS’)
AND XH.hold_lookup_code IS NULL
–AND l1.lookup_type(+) = ‘HOLD CODE’
–AND l1.lookup_code(+) = XH.hold_lookup_code
AND ((XH.org_id IS NOT NULL
AND :p_org_id IS NOT NULL
AND XH.org_id = :p_org_id)
OR (:p_org_id IS NULL
AND XH.org_id IS NOT NULL
AND (mo_global.check_access(XH.org_id)= ‘Y’))
OR (:p_org_id IS NOT NULL
AND XH.org_id IS NULL)
OR (:p_org_id IS NULL
AND XH.org_id IS NULL))
AND EXISTS
(SELECT ‘Y’
FROM AP_EXPENSE_REPORT_LINES_all XL
WHERE XH.REPORT_HEADER_ID = XL.REPORT_HEADER_ID
)
ORDER BY UPPER(emps.last_name),
UPPER(emps.first_name),
UPPER(people.middle_names),
total,
week_end_date
–FOR UPDATE OF XH.report_header_id NOWAIT;
Enterprise Resource Planning and Integrations BlogSpot 