AP Credit Card Historical Transactions Management - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Credit Card Historical Transactions Management (XML) - Not Supported: Reserved For Future Use
Short Name: APXCCPUT_XML
DB package: AP_APXCCPUT_XMLP_PKG
            SELECT emp.full_name C_FULL_NAME,
                         icc.masked_cc_number C_TRX_CARD_NUMBER,
                         txn.posted_date C_TRX_POSTED_DATE,
                         txn.transaction_amount C_TRX_AMOUNT,
                         txn.trx_id   C_TRX_ID,
                         txn.posted_currency_code   C_CURRENCY_CODE,
                         txn.merchant_name1  C_MERCHANT_NAME,
                         AP_WEB_DB_CCARD_PKG.GETLOCATION(txn.merchant_city,
                        txn.merchant_province_state) C_LOCATION,
                        vendor.vendor_name   C_VENDOR_NAME,
                AP_APXCCPUT_XMLP_PKG.c_trx_amount_fformula(:C_TRX_AMOUNT_F) C_TRX_AMOUNT_F,
                AP_APXCCPUT_XMLP_PKG.cf_masked_card_numberformula(icc.masked_cc_number) CF_MASKED_CARD_NUMBER,
                AP_APXCCPUT_XMLP_PKG.CP_MASKED_CARD_NUMBER_p CP_MASKED_CARD_NUMBER,
                '1' dummy
            from  per_employees_x emp,
                     ap_cards_all card,
                     ap_credit_card_trxns_all txn,
                     po_vendors vendor,
                     ap_card_programs_all card_prog,
                     iby_creditcard icc
            where txn.inactive_emp_wf_item_key is null
             and    txn.org_id = nvl(:p_org_id, txn.org_id)
             and    txn.card_id = nvl(:p_card_number, txn.card_id)
             and    txn.transaction_amount = nvl(:p_amount, txn.transaction_amount)
            and    txn.posted_date between  (nvl(:lp_start_date, txn.posted_date-1))
                       and :lp_end_date
             and  ( ( txn.report_header_id is null and txn.category is null and :p_process='DEACTIVATE')
                    or (txn.report_header_id = -1 and txn.category = 'DEACTIVATED' and :p_process= 'ACTIVATE'))
             and    card.card_program_id = txn.card_program_id
             and    txn.card_id = card.card_id
             and   card.employee_id = emp.employee_id
             and   card.card_program_id = card_prog.card_program_id
             and   card_prog.vendor_id = vendor.vendor_id
             and txn.org_id = card_prog.org_id
            and card.card_reference_id = icc.instrid
             order by txn.posted_date
Parameter Name SQL text Validation
FND_NUMBER
 
Number
Send Notification
 
LOV Oracle
Transaction Amount
 
Number
Card Number
 
LOV Oracle
Operating Unit
 
LOV Oracle
Posted End Date
 
Date
Posted Start Date
 
Date
Process
 
LOV Oracle