AP Credit Card Outstanding Transactions Management (Details)- Not Supported: Reserved For Future Use
Description
- Categories
- BI Publisher
- Application
- Payables
- Source
- Credit Card Outstanding Transactions Management (Details) (XML) - Not Supported: Reserved For Future Use
- Short Name
- APXCCOUT_XML
- DB package
- AP_APXCCOUT_XMLP_PKG
Imported from BI Publisher
Credit Card Outstanding Transactions Management (Details)
Credit Card Outstanding Transactions Management (Details)
SQL 293 lines
select age_employee_id, age_supervisor_id, age_emp_status, age_billed_date, age_posted_date, aging_amount, aging_report_status_code, dispute_flag, trx_id, person_id, AP_APXCCOUT_XMLP_PKG.cf_aging_sup_nameformula(age_supervisor_id) CF_aging_sup_name, posted_date, billed_amount, AP_APXCCOUT_XMLP_PKG.cf_age_sup_pend_bucklet1formul(:CS_sup_bucket1, :CS_sup_bucket2, :CS_sup_bucket3, :CS_sup_bucket4) CF_age_sup_pend_bucklet1, AP_APXCCOUT_XMLP_PKG.CP_age_sup_name_p CP_age_sup_name, AP_APXCCOUT_XMLP_PKG.CP_sup_pend_bucket1_p CP_sup_pend_bucket1, AP_APXCCOUT_XMLP_PKG.CP_sup_pend_bucket2_p CP_sup_pend_bucket2, AP_APXCCOUT_XMLP_PKG.CP_sup_pend_bucket3_p CP_sup_pend_bucket3, AP_APXCCOUT_XMLP_PKG.CP_sup_pend_bucket4_p CP_sup_pend_bucket4, AP_APXCCOUT_XMLP_PKG.cf_age_amp_nameformula(person_id, 'Active') CF_age_amp_name, AP_APXCCOUT_XMLP_PKG.CP_age_emp_name_p CP_age_emp_name, AP_APXCCOUT_XMLP_PKG.cf_aging_bucketsformula(posted_date, billed_amount) CF_aging_buckets, AP_APXCCOUT_XMLP_PKG.cf_pending_amountsformula(aging_report_status_code, posted_date, billed_amount) CF_pending_amounts, AP_APXCCOUT_XMLP_PKG.CP_appr_pend_bucket1_p CP_appr_pend_bucket1, AP_APXCCOUT_XMLP_PKG.CP_appr_pend_bucket3_p CP_appr_pend_bucket3, AP_APXCCOUT_XMLP_PKG.CP_appr_pend_bucket4_p CP_appr_pend_bucket4, AP_APXCCOUT_XMLP_PKG.CP_appr_pend_bucket2_p CP_appr_pend_bucket2, AP_APXCCOUT_XMLP_PKG.CP_emp_pend_bucket1_p CP_emp_pend_bucket1, AP_APXCCOUT_XMLP_PKG.CP_emp_pend_bucket2_p CP_emp_pend_bucket2, AP_APXCCOUT_XMLP_PKG.CP_emp_pend_bucket3_p CP_emp_pend_bucket3, AP_APXCCOUT_XMLP_PKG.CP_emp_pend_bucket4_p CP_emp_pend_bucket4, AP_APXCCOUT_XMLP_PKG.CP_sys_pend_bucket2_p CP_sys_pend_bucket2, AP_APXCCOUT_XMLP_PKG.CP_sys_pend_bucket3_p CP_sys_pend_bucket3, AP_APXCCOUT_XMLP_PKG.CP_sys_pend_bucket4_p CP_sys_pend_bucket4, AP_APXCCOUT_XMLP_PKG.CP_sys_pend_bucket1_p CP_sys_pend_bucket1, AP_APXCCOUT_XMLP_PKG.CP_mgr_pend_bucket1_p CP_mgr_pend_bucket1, AP_APXCCOUT_XMLP_PKG.CP_mgr_pend_bucket2_p CP_mgr_pend_bucket2, AP_APXCCOUT_XMLP_PKG.CP_mgr_pend_bucket3_p CP_mgr_pend_bucket3, AP_APXCCOUT_XMLP_PKG.CP_mgr_pend_bucket4_p CP_mgr_pend_bucket4, AP_APXCCOUT_XMLP_PKG.CP_bucket1_p CP_bucket1, AP_APXCCOUT_XMLP_PKG.CP_bucket2_p CP_bucket2, AP_APXCCOUT_XMLP_PKG.CP_bucket3_p CP_bucket3, AP_APXCCOUT_XMLP_PKG.CP_bucket4_p CP_bucket4, AP_APXCCOUT_XMLP_PKG.CP_emp_pending_p CP_emp_pending, AP_APXCCOUT_XMLP_PKG.CP_sys_pending_p CP_sys_pending, AP_APXCCOUT_XMLP_PKG.CP_mgr_pending_p CP_mgr_pending, AP_APXCCOUT_XMLP_PKG.CP_appr_pending_p CP_appr_pending from (select distinct emp.person_id age_employee_id, pera.supervisor_id age_supervisor_id, 'Active' age_emp_status, nvl(cct.billed_date,cct.posted_date) age_billed_date, cct.posted_date age_posted_date, cct.billed_amount aging_amount, AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source, erh.workflow_approved_flag,erh.report_header_id) aging_report_status_code, 'N' dispute_flag, cct.trx_id trx_id, cct.posted_date posted_date, cct.billed_amount billed_amount, emp.person_id from ap_credit_card_trxns cct, ap_cards_all ac, ap_card_programs_all cp, ap_expense_report_headers erh, per_all_people_f emp, per_assignments_f pera, per_assignment_status_types peras where cct.card_program_id = :p_card_program_id and cct.validate_code = 'Y' and cct.payment_flag <> 'Y' and nvl(cct.category,'BUSINESS') = 'PERSONAL' and erh.report_header_id = cct.report_header_id and NVL(erh.vouchno, 0) = 0 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode( erh.source,erh.workflow_approved_flag,erh.report_header_id) in ('SAVED', 'INPROGRESS', 'EMPAPPR', 'REJECTED', 'RESOLUTN', 'RETURNED', 'PENDMGR', 'MGRAPPR','ERROR','INVOICED','WITHDRAWN') and exists (select 'Y' from ap_expense_report_lines xl where erh.report_header_id = xl.report_header_id) and ac.card_program_id = cct.card_program_id and ac.card_id = cct.card_id and cp.card_program_id = cct.card_program_id and ac.employee_id = emp.person_id and emp.person_id = pera.person_id and pera.primary_flag = 'Y' and pera.assignment_status_type_id = peras.assignment_status_type_id and peras.per_system_status = 'ACTIVE_ASSIGN' and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date &lp_min_amt_where &lp_emp_mgr UNION ALL select distinct emp.person_id age_employee_id, pera.supervisor_id age_supervisor_id, 'Active' age_emp_status, nvl(cct.billed_date,cct.posted_date) age_billed_date, cct.posted_date age_posted_date, cct.billed_amount aging_amount, AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source, erh.workflow_approved_flag,erh.report_header_id) aging_report_status_code, 'N' dispute_flag, cct.trx_id trx_id, cct.posted_date posted_date, cct.billed_amount billed_amount, emp.person_id from ap_credit_card_trxns cct, ap_cards_all ac, ap_card_programs_all cp, ap_expense_report_headers erh, per_all_people_f emp, per_assignments_f pera, per_assignment_status_types peras where cct.card_program_id = :p_card_program_id and cct.validate_code = 'Y' and cct.payment_flag <> 'Y' and nvl(cct.category,'BUSINESS') not in ( 'DISPUTED','PERSONAL', 'DEACTIVATED') and nvl(cct.expensed_amount, 0) <> 0 and erh.report_header_id = cct.report_header_id and NVL(erh.vouchno, 0) = 0 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode( erh.source,erh.workflow_approved_flag,erh.report_header_id) in ('SAVED', 'INPROGRESS','EMPAPPR', 'REJECTED', 'RESOLUTN', 'RETURNED', 'PENDMGR', 'MGRAPPR','ERROR','INVOICED','WITHDRAWN') and ac.card_program_id = cct.card_program_id and ac.card_id = cct.card_id and cp.card_program_id = cct.card_program_id and ac.employee_id = emp.person_id and emp.person_id = pera.person_id and pera.primary_flag='Y' and pera.assignment_status_type_id = peras.assignment_status_type_id and peras.per_system_status = 'ACTIVE_ASSIGN' and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date &lp_min_amt_where &lp_emp_mgr UNION ALL select distinct emp.person_id age_employee_id, pera.supervisor_id age_supervisor_id, 'Active' age_emp_status, nvl(cct.billed_date,cct.posted_date) age_billed_date, cct.posted_date age_posted_date, cct.billed_amount aging_amount, 'UNUSED' aging_report_status_code, 'N' dispute_flag, cct.trx_id trx_id, cct.posted_date posted_date, cct.billed_amount billed_amount, emp.person_id from ap_credit_card_trxns cct, ap_cards_all ac, ap_card_programs_all cp, per_all_people_f emp, per_assignments_f pera, per_assignment_status_types peras where cct.card_program_id = :p_card_program_id and cct.validate_code = 'Y' and cct.payment_flag <> 'Y' and nvl(cct.category,'BUSINESS') not in ( 'DISPUTED','PERSONAL', 'DEACTIVATED','MATCHED','CREDIT') and nvl(cct.expensed_amount, 0) = 0 and cct.report_header_id IS NULL and ac.card_program_id = cct.card_program_id and ac.card_id = cct.card_id and cp.card_program_id = cct.card_program_id and ac.employee_id = emp.person_id and emp.person_id = pera.person_id and pera.primary_flag='Y' and pera.assignment_status_type_id = peras.assignment_status_type_id and peras.per_system_status = 'ACTIVE_ASSIGN' and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date &lp_min_amt_where &lp_emp_mgr UNION ALL select distinct perf.person_id, pera.supervisor_id, 'Inactive', nvl(cct.billed_date, cct.posted_date), decode(cct.category,'DISPUTED',cct.posted_date+nvl(:p_grace_days,0),cct.posted_date), cct.billed_amount, nvl(AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source, erh.workflow_approved_flag,erh.report_header_id),'UNUSED') , 'N' dispute_flag, cct.trx_id trx_id, cct.posted_date posted_date, cct.billed_amount billed_amount, perf.person_id from ap_credit_card_trxns cct, ap_cards_all ac, ap_card_programs_all cp, ap_expense_report_headers erh, per_all_people_f perf, per_assignments_f pera, per_assignment_status_types peras where cct.card_program_id = :p_card_program_id and cct.validate_code = 'Y' and cct.payment_flag <> 'Y' and nvl(cct.category, 'BUSINESS') <> 'DEACTIVATED' and erh.report_header_id = cct.report_header_id and nvl(cct.expensed_amount, 0) <> 0 and NVL(erh.vouchno, 0) = 0 and nvl( AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode( erh.source,erh.workflow_approved_flag,erh.report_header_id), 'UNUSED') in ('UNUSED','SAVED', 'INPROGRESS','EMPAPPR', 'REJECTED', 'RESOLUTN', 'RETURNED', 'PENDMGR', 'MGRAPPR','ERROR','INVOICED','WITHDRAWN') and ac.card_program_id = cct.card_program_id and ac.card_id = cct.card_id and cp.card_program_id = cct.card_program_id and nvl(cct.billed_date,cct.posted_date) between nvl(:p_billed_start_date, nvl(cct.billed_date,cct.posted_date)-1) and nvl(:p_billed_end_date, nvl(cct.billed_date,cct.posted_date)+1) &lp_min_amt_where and ac.employee_id = perf.person_id &lp_emp_mgr1 and perf.person_id = pera.person_id and pera.assignment_type = 'E' and pera.primary_flag = 'Y' and perf.employee_number is not null and pera.assignment_status_type_id = peras.assignment_status_type_id and peras.per_system_status in ( 'TERM_ASSIGN', 'SUSP_ASSIGN') and trunc(sysdate) between perf.effective_start_date and perf.effective_end_date and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date UNION ALL select distinct perf.person_id, pera.supervisor_id, 'Inactive', nvl(cct.billed_date,cct.posted_date), decode(cct.category,'DISPUTED',cct.posted_date+nvl(:p_grace_days,0),cct.posted_date), cct.billed_amount, 'UNUSED' , 'N' dispute_flag, cct.trx_id trx_id, cct.posted_date posted_date, cct.billed_amount billed_amount, perf.person_id from ap_credit_card_trxns cct, ap_cards_all ac, ap_card_programs_all cp, per_all_people_f perf, per_assignments_f pera, per_assignment_status_types peras where cct.card_program_id = :p_card_program_id and cct.validate_code = 'Y' and cct.payment_flag <> 'Y' and nvl(cct.category, 'BUSINESS') <> 'DEACTIVATED' and nvl(cct.expensed_amount, 0) = 0 and cct.report_header_id IS NULL and ac.card_program_id = cct.card_program_id and ac.card_id = cct.card_id and cp.card_program_id = cct.card_program_id and nvl(cct.billed_date,cct.posted_date) between nvl(:p_billed_start_date, nvl(cct.billed_date,cct.posted_date)-1) and nvl(:p_billed_end_date, nvl(cct.billed_date,cct.posted_date)+1) &lp_min_amt_where and ac.employee_id = perf.person_id &lp_emp_mgr1 and perf.person_id = pera.person_id and pera.assignment_type = 'E' and pera.primary_flag = 'Y' and perf.employee_number is not null and pera.assignment_status_type_id = peras.assignment_status_type_id and peras.per_system_status in ( 'TERM_ASSIGN', 'SUSP_ASSIGN') and trunc(sysdate) between perf.effective_start_date and perf.effective_end_date and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date UNION ALL select distinct emp.person_id age_employee_id, pera.supervisor_id age_supervisor_id, 'Active' age_emp_status, nvl(cct.billed_date,cct.posted_date) age_billed_date, cct.posted_date+nvl(:p_grace_days,0) age_posted_date, cct.billed_amount aging_amount, 'DISPUTED' aging_report_status_code, 'Y' dispute_flag, cct.trx_id trx_id, cct.posted_date posted_date, cct.billed_amount billed_amount, emp.person_id from ap_credit_card_trxns cct, ap_cards_all ac, ap_card_programs_all cp, per_all_people_f emp, per_assignments_f pera, per_assignment_status_types peras where cct.card_program_id = :p_card_program_id and cct.validate_code = 'Y' and cct.payment_flag <> 'Y' and nvl(cct.category,'BUSINESS') = 'DISPUTED' and nvl(cct.expensed_amount,0) = 0 and ac.card_program_id = cct.card_program_id and ac.card_id = cct.card_id and cp.card_program_id = cct.card_program_id and ac.employee_id = emp.person_id and emp.person_id = pera.person_id and pera.primary_flag = 'Y' and pera.assignment_status_type_id = peras.assignment_status_type_id and peras.per_system_status = 'ACTIVE_ASSIGN' and trunc(sysdate) between pera.effective_start_date and pera.effective_end_date &lp_min_amt_where &lp_emp_mgr) |
Parameters 9
| Parameter | Type | List of values SQL |
|---|---|---|
| Operating Unit | LOV | |
| Card Program | LOV Oracle | |
| Employee Status | LOV Oracle | |
| Employee Name | LOV Oracle | |
| Include Direct Reports | LOV Oracle | |
| Send Notifications | LOV Oracle | |
| Minimum Transaction Amount | Number | |
| Billed Start Date | Date | |
| Billed End Date | Date |