AP Credit Card Outstanding Transactions Summary - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Credit Card Outstanding Transactions Summary Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXCCOUT_SUMMARY_XML
DB package: AP_APXCCOUT_XMLP_PKG
            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)
Parameter Name SQL text Validation
FND_NUMBER
 
Number
Operations
 
LOV Oracle
Billed End Date
 
Date
Billed Start Date
 
Date
Include Direct Reports
 
LOV Oracle
Employee Name
 
LOV Oracle
Employee Status
 
LOV Oracle
Card Program
 
LOV Oracle
Ask a question