R12 – Queries to Troubleshoot Payments

The following queries can be used to Troubleshoot Payments in R12.    In the example below replace ‘PAYMENT_PROCESS_REQUEST_NAME’ with your Payment file name.

select distinct * from ap_inv_selection_criteria_all where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from iby_pay_service_requests where call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from IBY_PAY_INSTRUCTIONS_ALL where PAY_ADMIN_ASSIGNED_REF_CODE = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select * from ap_invoices_all where invoice_id in
( select calling_app_doc_unique_ref2 from iby_docs_payable_all where payment_service_request_id in
( select a.payment_service_request_id from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from ap_selected_invoices_all where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from ap_selected_invoice_checks_all where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from ap_checks_all where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’;

select distinct * from ap_payment_schedules_all where checkrun_id in
( select a.checkrun_id from ap_inv_selection_criteria_all a where checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select * from iby_docs_payable_all where payment_service_request_id in
( select a.payment_service_request_id from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select * from iby_payments_all where payment_service_request_id in
( select a.payment_service_request_id from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’);

/*
INSTRUCTION_CREATED
FORMATTED
SUBMITTED_FOR_PRINTING
ISSUED
*/

select * from ce_payment_documents where payment_document_id in
( select pi.payment_document_id from IBY_PAY_INSTRUCTIONS_ALL pi where pi.payment_instruction_id in
( SELECT b.payment_instruction_id FROM iby_payments_all b WHERE b.payment_service_request_id in
( SELECT a.payment_service_request_id FROM iby_pay_service_requests a WHERE a.calling_app_id = 200 AND a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’)));

select distinct * from iby_acct_pmt_profiles_b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from iby_acct_pmt_profiles_tl where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’) ;

select distinct * from iby_sys_pmt_profiles_b where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_sys_pmt_profiles_tl where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_formats_b where FORMAT_CODE in
( select c.PAYMENT_FORMAT_CODE from iby_sys_pmt_profiles_b c where c.SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’)));

select distinct * from xdo_templates_b where template_code in
( select d.FORMAT_TEMPLATE_CODE from iby_formats_b d where d.FORMAT_CODE in
( select c.PAYMENT_FORMAT_CODE from iby_sys_pmt_profiles_b c where c.SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’))));

select distinct * from iby_applicable_pmt_profs where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_instr_creation_rules where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_pmt_creation_rules where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from iby_remit_advice_setup where SYSTEM_PROFILE_CODE in
( select b.SYSTEM_PROFILE_CODE from iby_acct_pmt_profiles_b b where PAYMENT_PROFILE_ID in
( select a.PAYMENT_PROFILE_ID from iby_pay_service_requests a where a.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from ap_currency_group where checkrun_id in ( select a.checkrun_id from ap_inv_selection_criteria_all a where a.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from ap_ou_group where checkrun_id in ( select a.checkrun_id from ap_inv_selection_criteria_all a where a.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from ap_le_group where checkrun_id in ( select a.checkrun_id from ap_inv_selection_criteria_all a where a.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from ap_pay_group where checkrun_id in ( select a.checkrun_id from ap_inv_selection_criteria_all a where a.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select * from iby_payment_profiles where payment_profile_id in
( select distinct(payment_profile_id) from iby_docs_payable_all where payment_service_request_id in
( select payment_service_request_id from iby_pay_service_requests where calling_app_id = 200 and call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select distinct * from ce_bank_accounts where bank_account_id in
( select a.internal_bank_account_id from IBY_PAY_INSTRUCTIONS_ALL a where a.PAY_ADMIN_ASSIGNED_REF_CODE = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select * from iby_ext_bank_accounts where ext_bank_account_id in
( select external_bank_account_id from iby_docs_payable_all where payment_service_request_id in
( select payment_service_request_id from iby_pay_service_requests where calling_app_id = 200 and call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

select * from iby_external_payees_all where payee_party_id in
( SELECT payee_party_id FROM iby_docs_payable_all where PAYMENT_SERVICE_REQUEST_ID in
( SELECT payment_service_request_id FROM iby_pay_service_requests WHERE calling_app_id = 200 AND call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’));

SELECT * FROM iby_pmt_instr_uses_all WHERE payment_flow = ‘DISBURSEMENTS’ AND ext_pmt_party_id IN
( select ext_payee_id from iby_external_payees_all where payee_party_id in
( SELECT payee_party_id FROM iby_docs_payable_all where PAYMENT_SERVICE_REQUEST_ID in
( SELECT payment_service_request_id FROM iby_pay_service_requests WHERE calling_app_id = 200 AND call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’)));

select distinct * from iby_process_conc_requests where object_type = ‘PAYMENT_REQUEST’ and object_id in
( select ipsr.payment_service_request_id from iby_pay_service_requests ipsr where ipsr.call_app_pay_service_req_code = ‘PAYMENT_PROCESS_REQUEST_NAME’) union select distinct * from iby_process_conc_requests where object_type = ‘PAYMENT_INSTRUCTION’ and object_id in
( select ipi.payment_instruction_id from iby_pay_instructions_all ipi where ipi.pay_admin_assigned_ref_code = ‘PAYMENT_PROCESS_REQUEST_NAME’);

select distinct * from fnd_concurrent_requests where request_id in
( select ais.request_id from ap_inv_selection_criteria_all ais where ais.checkrun_name = ‘PAYMENT_PROCESS_REQUEST_NAME’ union
select ipi.request_id from IBY_PAY_INSTRUCTIONS_ALL ipi where ipi.PAY_ADMIN_ASSIGNED_REF_CODE = ‘PAYMENT_PROCESS_REQUEST_NAME’);

References:

https://oracleappsstore.wordpress.com/2012/06/04/602/

Credit Card Sales Order Queries

/* -----List of Orders which are pick released on Sysdate ---------*/

SELECT oeh.header_id, ott.name Order_type,oeh.order_number,oeh.payment_type_code,rt.name payment_term,ool.line_number,
ool.ordered_item,ool.schedule_ship_date,ool.flow_status_code line_status,
ohd.name,ooh.order_hold_id,ooh.creation_date,ooh.released_flag
  FROM oe_order_headers_all oeh, oe_order_lines_all ool,oe_transaction_types_tl ott,
  ra_terms_tl rt,
  oe_order_holds_all ooh,
  oe_hold_sources_all ohs,
  oe_hold_definitions ohd
WHERE 1=1
 and oeh.header_id=ool.header_id
   AND oeh.flow_status_code != 'CLOSED'
   and ool.flow_status_code!='CANCELLED'
   and oeh.order_type_id=ott.transaction_type_id
   and ott.language='US'
   and oeh.payment_term_id=rt.term_id
   and rt.language='US'
   and oeh.header_id=ooh.header_id(+)
   and ooh.hold_source_id=ohs.hold_source_id(+)
   and ohs.hold_id=ohd.hold_id(+)
   and trunc(ool.schedule_ship_date)= to_date('30-APR-14','DD-MON-YY')
    and oeh.PAYMENT_TERM_ID in ( 1021 ,1143);

/*List of Standing Orders along with Credit Card Details on the first open line*/

   SELECT   OOHA.HEADER_ID,oola.line_number,oola.line_id,op.line_id,
  OOHA.ORDER_NUMBER,
  (SELECT hca.account_number
  FROM hz_cust_accounts hca
  WHERE hca.cust_account_id=ooha.sold_to_org_id
  ) CustomerNumber,
  OOHA.FLOW_STATUS_CODE "OrderStatus",
  OOHA.PAYMENT_TYPE_CODE,
  ooha.credit_card_number,
  OOLA.ORDERED_ITEM,
  OOLA.ORDERED_QUANTITY,
  OOLA.SHIPPED_QUANTITY,
  oola.invoiced_quantity,
  OOLA.TAX_VALUE,
  (OOLA.UNIT_SELLING_PRICE * OOLA.ORDERED_QUANTITY) ITEMTOTALAMOUNT,
  OOLA.FLOW_STATUS_CODE "LineStatus",
  IFTE.TRXN_EXTENSION_ID ,
  ic.ccnumber,
  ic.chname,
  ic.card_owner_id,
  ic.masked_cc_number,
  ic.card_issuer_code,
  ic.expirydate,
  ic.inactive_date
FROM OE_ORDER_HEADERS_ALL OOHA,
  OE_ORDER_LINES_ALL OOLA,
  OE_PAYMENTS OP,
  IBY_FNDCPT_TX_EXTENSIONS IFTE,
  IBY_PMT_INSTR_USES_ALL IPUA,
  IBY_CREDITCARD  IC
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
--AND OOHA.PAYMENT_TYPE_CODE LIKE 'CREDIT_CARD'
AND OP.line_ID(+)        =OOla.line_ID
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
  --AND IFTE.TRXN_EXTENSION_ID=ITSA.INITIATOR_EXTENSION_ID
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)
AND ooha.flow_status_code != 'CLOSED'
 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)
 and ooha.order_type_id=1010
 and oola.line_id = (select min(l.line_id) from oe_order_lines_all l where l.header_id=ooha.header_id
 and l.flow_status_code not in ('CLOSED','CANCELLED'));

/List of Standing Orders for which credit card details are populated at header level/

SELECT   OOHA.HEADER_ID,  OOHA.ORDER_NUMBER,
  (SELECT hca.account_number
  FROM hz_cust_accounts hca
  WHERE hca.cust_account_id=ooha.sold_to_org_id
  ) CustomerNumber,
  OOHA.FLOW_STATUS_CODE "OrderStatus",
  OOHA.PAYMENT_TYPE_CODE,
  ooha.credit_card_number,
  IFTE.TRXN_EXTENSION_ID ,
  ic.ccnumber,
  ic.chname,
  ic.card_owner_id,
  ic.masked_cc_number,
  ic.card_issuer_code,
  ic.expirydate,
  ic.inactive_date
FROM OE_ORDER_HEADERS_ALL OOHA,
  OE_PAYMENTS OP,
  IBY_FNDCPT_TX_EXTENSIONS IFTE,
  IBY_PMT_INSTR_USES_ALL IPUA,
  IBY_CREDITCARD  IC
WHERE OP.header_id(+)        =OOha.header_id
and op.payment_level_code(+)='ORDER'
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)
AND ooha.flow_status_code not in ('CLOSED' ,'CANCELLED')
 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)
 and ooha.order_type_id=1010;

XXXXXXXXXXXX0481


/**List of Standng order lines for with credit card details are shown******/

SELECT   OOHA.HEADER_ID,OOHA.ORDER_NUMBER,  OOHA.FLOW_STATUS_CODE "OrderStatus",oola.line_number,oola.flow_status_code,  oola.schedule_ship_date,
  (SELECT hca.account_number
  FROM hz_cust_accounts hca
  WHERE hca.cust_account_id=ooha.sold_to_org_id
  ) CustomerNumber,
  OOHA.PAYMENT_TYPE_CODE,
  ooha.credit_card_number,
  OOLA.ORDERED_ITEM,
  OOLA.ORDERED_QUANTITY,
  OOLA.SHIPPED_QUANTITY,
  oola.invoiced_quantity,
  ic.ccnumber,
  ic.chname,
  ic.card_owner_id,
  ic.masked_cc_number,
  ic.card_issuer_code,
  ic.expirydate,
  ic.inactive_date
FROM OE_ORDER_HEADERS_ALL OOHA,
  OE_ORDER_LINES_ALL OOLA,
  OE_PAYMENTS OP,
  IBY_FNDCPT_TX_EXTENSIONS IFTE,
  IBY_PMT_INSTR_USES_ALL IPUA,
  IBY_CREDITCARD  IC
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OP.line_ID(+)        =OOla.line_ID
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)
AND ooha.flow_status_code not in ('CLOSED' ,'CANCELLED')
 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)
 and ooha.order_type_id=1010
 --and trunc(oola.schedule_ship_date)>trunc(sysdate)
--and trunc(oola.schedule_ship_date)= to_date('02-MAY-14','DD-MON-YY')
 and oola.line_id = (select min(l.line_id) from oe_order_lines_all l where l.header_id=ooha.header_id
 and l.flow_status_code not in ('CLOSED','CANCELLED'))
 order by oola.schedule_ship_date asc;

/**********query which we run daily to knows orders on hold***************/

SELECT ooh.header_id,oeh.header_id, ott.name Order_type,oeh.order_number,oeh.payment_type_code,rt.name payment_term,ool.line_number,
ool.ordered_item,ool.schedule_ship_date,ool.flow_status_code line_status,
ohd.name,ooh.order_hold_id,ooh.creation_date,ooh.released_flag
,ic.ccnumber,
  ic.chname,
  ic.card_owner_id,
  ic.masked_cc_number,
  ic.card_issuer_code,
  ic.expirydate,
  ic.inactive_date
  FROM oe_order_headers_all oeh, oe_order_lines_all ool,oe_transaction_types_tl ott,
  ra_terms_tl rt,
  oe_order_holds_all ooh,
  oe_hold_sources_all ohs,
  oe_hold_definitions ohd,
  OE_PAYMENTS OP,
  IBY_FNDCPT_TX_EXTENSIONS IFTE,
  IBY_PMT_INSTR_USES_ALL IPUA,
  IBY_CREDITCARD  IC
WHERE 1=1
 and oeh.header_id=ool.header_id
   AND oeh.flow_status_code != 'CLOSED'
   and ool.flow_status_code!='CANCELLED'
   and oeh.order_type_id=ott.transaction_type_id
   and ott.language='US'
   and oeh.payment_term_id=rt.term_id
   and rt.language='US'
   and ool.header_id=ooh.header_id(+)
   and ool.line_id=nvl(ooh.line_id(+),ool.line_id)
 --  and ool.line_id=ooh.line_id(+)
   and ooh.hold_source_id=ohs.hold_source_id(+)
   and ohs.hold_id=ohd.hold_id(+)
   AND OP.line_ID(+)        =Ool.line_ID
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
--and op.payment_level_code(+)='ORDER'
  --AND IFTE.TRXN_EXTENSION_ID=ITSA.INITIATOR_EXTENSION_ID
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)
   and trunc(ool.schedule_ship_date)= to_date('08-MAY-14','DD-MON-YY')
    and oeh.PAYMENT_TERM_ID in ( 1021 ,1143);

 SELECT cra.receipt_number AS "Receipt Number",
         rm.name AS "Receipt Method",
         cra.amount AS "Amount",
         TO_CHAR (cra.receipt_date, 'DD-MON-YYYY') AS "Receipt Date",
         (SELECT rstat.meaning
            FROM applsys.fnd_lookup_values rstat
           WHERE     rstat.lookup_code = crh.status
                 AND rstat.lookup_type = 'RECEIPT_CREATION_STATUS'
                 AND rstat.language = 'US')
            AS "Receipt Status",
         TO_CHAR (crh.gl_date, 'DD-MON-YYYY') AS "GL Date",
         tsa.tangibleid AS "PSON",
         (SELECT treq.meaning
            FROM applsys.fnd_lookup_values treq
           WHERE     treq.lookup_code = tsa.reqtype
                 AND treq.lookup_type = 'IBY_TRXN_REQTYPES'
                 AND treq.language = 'US')
            AS "IBY Request Type",
         (SELECT ttype.meaning || ' | ' || ttype.description
            FROM applsys.fnd_lookup_values ttype
           WHERE     ttype.lookup_code = tsa.trxntypeid
                 AND ttype.lookup_type = 'IBY_TRXNTYPES'
                 AND ttype.language = 'US')
            AS "IBY Transaction Type",
         tsa.status AS "IBY Status Code",
         (SELECT istat.meaning || ' | ' || istat.description
            FROM applsys.fnd_lookup_values istat
           WHERE     istat.lookup_code = tsa.status
                 AND istat.lookup_type = 'IBY_TRANSACTION_STATUS'
                 AND istat.language = 'US')
            AS "IBY Status Description",
         tsa.bepcode AS "Payment System Error Code",
         tsa.bepmessage AS "Payment System Error Message"
    FROM ar.ar_cash_receipts_all cra
         INNER JOIN ar.ar_cash_receipt_history_all crh
            ON cra.cash_receipt_id = crh.cash_receipt_id
         INNER JOIN iby.iby_fndcpt_tx_operations fto
            ON cra.payment_trxn_extension_id = fto.trxn_extension_id
         INNER JOIN iby.iby_trxn_summaries_all tsa
            ON fto.transactionid = tsa.transactionid
         INNER JOIN ar.ar_receipt_methods rm
            ON cra.receipt_method_id = rm.receipt_method_id
   WHERE     1 = 1
                AND  crh.status NOT IN ('CLEARED', 'REVERSED')
         -- AND  cra.receipt_number                     = '11068970'--:Receipt_Number
         AND rm.name='CardConnect US'
         AND crh.current_record_flag = 'Y'
         AND cra.receipt_date >='01-OCT-2016'
         AND tsa.status<>0
         AND tsa.updatedate = (SELECT MAX (tsa2.updatedate)
                                 FROM iby.iby_trxn_summaries_all tsa2
                                WHERE tsa2.transactionid = tsa.transactionid)
ORDER BY cra.receipt_date DESC, cra.receipt_number;

References:
http://sairamgoudmalla.blogspot.com/2014/05/credit-card-sales-order-queries.html

iby_trxn_summaries_all.status & TRXNTYPEID

The table below contains the different statuses of transactions .Here is the meaning for them.

iby_trxn_summaries_all.status

SUCCESS 0

ERROR
1, Communication error
2, Duplicate request, order id
3, Duplicate batch id
4, Mandatory fields required or missing
5, Payment system specific error
6, Batch partially succeeded
7, Batch failed
8, Request action not supported
14, Request cancelled
15, Failed to schedule
16, Payment system failed
17, Unable to pay (insufficient funds)
19, Invalid Credit Card number
20, Declined
21, Failed (requires voice auth)
101, Trxn was sent in a batch that resulted in communication error
114, Trxn was cancelled in an open batch (void)
120, iPayment max number of batches exceeded for the day
-99, Invalid request

Value data-source is IBY_TRXN_SUMMARIES_ALL.TRXNTYPEID

• 2 (Auth only)
• 3 (Auth capture)
• 5 (Return)
• 8/9 (Capture/Settlement)
• 11 (Credit)
  
  

References:
http://sairamgoudmalla.blogspot.com/2014/05/ibytrxnsummariesallstatus.html

R12 Payment Process Request - Functional and Technical Information

Payment Process Requests
Overview
Under Funds disbursement page, users can submit Payment Process Requests (PPR) to generate payments. There is an option to submit a single Payment Process Request or schedule Payment Process Requests.
There are four steps in the processing of a PPR.
a) Document selection
b) Build Payments
c) Format Payments
d) Confirm Payments
Document selection and Confirm Payments are handled by Payables (AP) code while Build Payments and Format payments are handled by Payments (IBY) code.
Submitting a Single Payment Process Request
Mandatory Fields - Payment process request name, pay through date
Under Payment Attributes tab – Payment Date, Payment Exchange rate type.
Payment Process Profile and Disbursement bank account are optional fields.
Under Processing tab, options are available to stop the process after document selection / payment and also how to create Payment Instruction.
Under Validation Failure Results tab, choose option that best suits the business needs regarding how to handle validation failure on document(s) or payment(s).
Click on Submit to submit the Payment process request.
Document Selection – Payables
Code: AP_AUTOSELECT_PKG
When a Payment Process request is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name which is the same as the payment process request name.
Payment Profile and Internal Bank Account from which payments have to be made do not have to be specified during invoice selection. User who submits the PPR does not need know this information. These values can be provided by at a later stage by a Payments Manager or Administrator.
Selection:
Invoices are then selected based on due date, discount date, paygroup and other criteria provided by the user while submitting the PPR. The selection process is handled by the calling product
The table AP_SELECTED_INVOICES_ALL is populated with selected invoices.
AP_UNSELECTED_INVOICES_ALL is populated with unselected invoices.
Locking:
After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
AP_PAYMENT_SCHEDULES_ALL.checkrun_id is populated on the selected documents.
Review:
If the Payment Process Request has been setup to ‘Stop Process for Review After Scheduled Payment Selection’, the process stops for user review. The status of the PPR is set to Invoices Pending Review.
If the ‘Stop Process for Review After Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.
If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled - No Invoices Selected”
If user review required, after the user reviews the selected payment schedules and clicks on Submit, AP calls the IBYBUILD program.
Valid Statuses and actions
At the end of this step, the valid statuses are
a) Invoices Pending Review or
b) Cancelled - No Invoices Selected or
c) Other statuses from missing information such as Missing Exchange rates
If PPR status is Cancelled-No Invoices Selected, there are no valid actions available.
For others, the actions available are
a) Terminate the PPR or
b) Modify / proceed to submit the PPR and start the build process.
Build Payments - Payments
Code: IBY_DISBURSE_SUBMIT_PUB_PKG
Build Payments creates record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.
Primary Key: PAYMENT_SEVICE_REQUEST_ID
Key Columns:
CALL_AP_PAY_SERVICE_REQ_CODE -> PPR name
CALLING_APP_ID
PAYMENT_SERVICE_REQUEST_STATUS
INTERNAL_BANK_ACCOUNT_ID
MAXIMUM_PAYMENT_AMOUNT
MINIMUM_PAYMENT_AMOUNT
DOCUMENT_REJECTION_LEVEL_CODE
PAYMENT_REJECTION_LEVEL_CODE
REQUIRE_PROP_PMTS_REVIEW_FLAG
CREATE_PMT_INSTRUCTIONS_FLAG
Note: The displayed status of the PPR is generated by ibyvutlb.pls
There is a get_psr_status function that derives the display sttaus of the PPR on the dashboard.
Some of the values for PAYMENT_SERVICE_REQUEST_STATUS in the table are
PAYMENT_SERVICE_REQUEST_STATUS
------------------------------
DOCUMENTS_VALIDATED
INFORMATION_REQUIRED
INSERTED
PAYMENTS_CREATED
PENDING_REVIEW
TERMINATED
VALIDATION_FAILED
The build program populates the IBY_DOCS_PAYABLE_ALL table with the payments. Link to the payment service request table is through PAYMENT_SERVICE_REQUEST_ID.
Key Columns:
Payment_service_request_id
Calling_app_doc_ref_number -> invoice_number
Document_payable_id
Document_status
Payment_currency_code
Payment_amount
Document_amount
Exclusive_payment_flag
Payment_method_code
Payment_id
Formatting_payment_id
Ext_payee_id
Payee_party_id
Payment_profile_id
Internal_bank_account_id
Calling_app_doc_unique_ref2 -> invoice_id
Calling_app_doc_unique_ref3 -> payment number

a) Internal Bank Account/Payment Process Profile Assignment:
Code: IBY_ASSIGN_PUB
If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.
If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required - Pending Action”
User should complete the missing information and Run Payment Process to continue.
b) Document Validation
Code: IBY_VALIDATIONSETS_PUB
During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations..
b.1 - If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.
b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.
The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failure
REQUEST - Reject all documents in this PPR
DOCUMENT - Reject only the document in error
PAYEE - Reject all the documents related to the supplier
NONE - Stop the request for review
b.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
c) Create Payments
Code: IBY_PAYGROUP_PUB
The validated documents are then grouped into proposed payments based on the grouping rules, both user defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, its paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.
Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.
The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.
IBY_PAYMENTS_ALL links through payment_service_request_id.
Key Columns:
Payment_service_request_id
Payment_id
Payment_method_code
Payment_status
Payments_complete_flag
Payment_amount,
Dicount_amount_taken
Internal_bank_Account_id
Ext_payee_id
Payment_instruction_id
Payment_profile_id
Void_date
The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
REQUEST – Reject all payments in the request
PAYMENT – Reject only those payments in error
NONE – Stop the request for review
Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.
Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.
None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’
If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.
Format Payments - Payments
Code: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB
When a PPR is submitted, there are two options
The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.
If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID
If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id

Key Columns in IBY_PAYMENT_INSTRUCTIONS_ALL
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Format:
The following processing occurs during the format step.
a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
Confirm Payments - Payables
Code: AP_PMT_CALLOUT_PKG
Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.
This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is udpated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL

Oracle Payment Tables - Brief Description

Below post will give you the brief details about some of the important Oracle Payments tables (IBY)

IBY_EXT_BANK_ACCOUNTS

"This table contains records of the external bank accounts entered manually. This table corresponds to the Banking details page of supplier or customer. This table contains bank, branch, masking, encryption details of the externalbank accounts. This table should not be translated for any columns. This is a common table used by both Funds disbursement and funds Capture module.

IBY_ACCOUNT_OWNERS

This table contains records of the account owners of the external bank accounts entered manually. This table corresponds to the Banking details page of supplier or customer. When an external bank account is created a row will be stored in to this table as well as IBY_EXT_BANK_ACCOUNTS. The external bank account can be shared by multipleparties. When a joint owner is added to the external bank account, then one more entry with the joint owner details is entered in the IBY_ACCOUNT_OWNERS table. This table holds the primary flag, owner party identifier etc. details of the external bank account owner.This is a common table used by both Funds disbursement and Funds Capture modules.

IBY_PMT_INSTR_USES_ALL

"This table contains records of the relationship between payment instruments and Payer/Payee. Data can be entered manually.IBY_PMT_INSTR_USES_ALL table assigns payment instruments to external party payers and payees. This table is shared by both the funds disbursement and funds capture modules of Oracle Payments. The payment instruments can be credit cards, bank accounts, etc."

IBY_EXTERNAL_PAYERS_ALL

This table contains payment details of an external payer automatically created by the source product. For example when customer or customer site is created manually, system automatically insert row in to the IBY_EXTERNAL_PAYERS_ALL table.

A customer account can have multiple payers at following levels

·         Party

·          Site

·          Address

·          Address operating unit

This table contains the details of all such payers.

IBY_EXTERNAL_PAYERS_ALL stores payment-related attributes for the funds capture process for external party payers. This table stores the data entered through the payment details (header) screen of the customer account setup UI.

IBY_CREDITCARD

This table stores the credit card information for a customer

IBY_FNDCPT_TX_EXTENSIONS :

The data in this table will be created by the source products through the public API  IBY_FNDCPT_TRXN_PUB  using procedures : Create_Transaction_Extension and Copy_Transaction_Extension to register a Funds capture transaction.

IBY_FNDCPT_TX_EXTENSIONS table stores the payment processing-specific attributes of a source product's transaction. The payment entities and attributes include payment method, payment instrument assignment, and payment method-driven transaction attributes. The payment method-driven transaction attributes are credit card, security value, voice authorization flag,voice authorization code, and voice authorization date.

IBY_PMT_MTHD_ATTRIB_APPL 

This table stores payment channel or method attribute applicability settings. 

Example  of SQL statement  :

SELECT attribute_applicability

  FROM iby_pmt_mthd_attrib_appl

 WHERE (payment_flow = 'FUNDS_CAPTURE')

   AND (payment_method_code = &pmt_chnel)

   AND (attribute_code = 'PAYMENT_INSTRUMENT');

IBY_TRXN_CORE

This table contains the details of a payment request that are specific for basic credit card operations.