APPS.ARP_PROCESS_PAYINFO SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 57

SELECT
      inv.trx_number,
      inv.customer_trx_id customer_trx_id,
      cus.party_id,
      inv.paying_customer_id,
      inv.paying_site_use_id,
      inv.org_id
FROM
      ra_customer_trx inv, hz_cust_accounts cus
WHERE
      inv.request_id         = fnd_global.conc_request_id
  AND inv.payment_attributes IS NOT NULL
  AND inv.paying_customer_id = cus.cust_account_id
  AND NOT EXISTS (
      SELECT /*+ leading(L) use_nl_with_index(E, RA_INTERFACE_ERRORS_N1) */ 1
      FROM  ra_customer_trx_lines l, ra_interface_errors e
      WHERE l.customer_trx_id = inv.customer_trx_id
      AND   l.customer_trx_line_id = e.interface_line_id
      AND   l.request_id = FND_GLOBAL.CONC_REQUEST_ID);  -- 7039838

Line: 114

      SELECT
            DISTINCT line.payment_trxn_extension_id
      BULK COLLECT INTO
            l_ext_entity_tab
      FROM
            ra_customer_trx_lines line
      WHERE
            line.customer_trx_id = c01_rec.customer_trx_id
      AND   line.line_type       = 'LINE'
      AND   line.request_id = FND_GLOBAL.CONC_REQUEST_ID; -- 7039838

Line: 203

               arp_standard.debug('Inserting into errors...');

Line: 206

            INSERT INTO ra_interface_errors
                   (
                   org_id,
                   interface_line_id,
                   message_text,
                   invalid_value
                   )
            SELECT
                   org_id,
                   customer_trx_line_id,
                   l_msg,
                   payment_trxn_extension_id
            FROM
                   ra_customer_trx_lines lines
            WHERE
                   lines.customer_trx_id = c01_rec.customer_trx_id ;

Line: 232

         UPDATE
               ra_customer_trx
         SET
               payment_trxn_extension_id = l_extension_id
         WHERE
               customer_trx_id = c01_rec.customer_trx_id;

Line: 298

  UPDATE ra_interface_lines il
  SET payment_attributes =
    (SELECT
      CASE
      WHEN authorized_flag = 'Y' THEN
        CASE
       WHEN SUM(decode(auth.settle_req_auth_flag,    'Y',    1,    0)) > 0 THEN
          'PERSISTENT-AUTH~' || ext.instr_assignment_id || '~' || ext.trxn_extension_id
       ELSE
          'NON-PERSISTENT-AUTH~' || ext.instr_assignment_id || '~'
       END
     ELSE
         'NO-AUTH~' || ext.instr_assignment_id || '~'
     END
     FROM iby_fndcpt_tx_extensions ext,
        (SELECT /*+  leading(op) use_nl(summ,seft,ueft)  */
        op.trxn_extension_id,
         decode(decode(summ.status,    0,    'Y',    100,    'Y',    NULL), 'N',    'Y') authorized_flag,
         nvl(seft.settle_require_vrfy_flag,    'N') settle_req_auth_flag
       FROM iby_trxn_summaries_all summ,
         iby_fndcpt_tx_operations op,
         iby_fndcpt_sys_eft_pf_b seft,
         iby_fndcpt_user_eft_pf_b ueft
       WHERE(summ.transactionid = op.transactionid)
       AND(reqtype = 'ORAPMTREQ')
       AND(trxntypeid IN(2,    3,    20))
       AND(decode(instrtype,    'BANKACCOUNT',    summ.process_profile_code, NULL) = ueft.user_eft_profile_code(+))
       AND(ueft.sys_eft_profile_code = seft.sys_eft_profile_code(+)))
    auth,
       fnd_application a
     WHERE ext.trxn_extension_id = il.payment_trxn_extension_id
     AND ext.trxn_extension_id = auth.trxn_extension_id(+)
     AND auth.settle_req_auth_flag(+) = 'Y'
     AND ext.origin_application_id = a.application_id
     GROUP BY auth.authorized_flag,
       ext.instr_assignment_id,
       ext.trxn_extension_id)
  WHERE request_id = fnd_global.conc_request_id
   AND payment_trxn_extension_id IS NOT NULL;

Line: 389

   INSERT INTO RA_INTERFACE_ERRORS
    (INTERFACE_LINE_ID,
     MESSAGE_TEXT,
     INVALID_VALUE,
     ORG_ID)
    SELECT /*+ cardinality(L,10) leading(L) use_nl(rm,rc, P,X) */
           L.INTERFACE_LINE_ID,
           CASE
              WHEN l.receipt_method_id IS NULL THEN
                 arp_standard.fnd_message('AR_REC_MTHD_REQD_FOR_EXT_ID')
              WHEN rc.creation_method_code NOT IN ('AUTOMATIC', 'BR') THEN
                 arp_standard.fnd_message('AR_RECEIPT_METHOD_AUTO_OR_BR')
              WHEN NVL(rm.payment_channel_code, 'BILLS_RECEIVABLE')
                      <> p.payment_channel_code THEN
                 arp_standard.fnd_message('AR_PMT_CHNL_MISMTCH_REC_MTHD')
              ELSE
                 'VALIDATE_EXT_ID:NO_MSG'
           END,
           L.PAYMENT_TRXN_EXTENSION_ID,
           L.ORG_ID
    FROM   RA_INTERFACE_LINES_GT L,
           AR_RECEIPT_METHODS rm,
           AR_RECEIPT_CLASSES rc,
	   IBY_FNDCPT_PMT_CHNNLS_B P,
           IBY_FNDCPT_TX_EXTENSIONS X
WHERE      L.REQUEST_ID = fnd_global.conc_request_id
    AND    L.CUSTOMER_TRX_ID IS NOT NULL
    AND    NVL(L.INTERFACE_STATUS, '~') <> 'P'
    AND    l.receipt_method_id = rm.receipt_method_id (+)
    AND    rm.receipt_class_id = rc.receipt_class_id (+)
    AND    L.PAYMENT_TRXN_EXTENSION_ID IS NOT NULL
    AND    (l.receipt_method_id IS NULL OR rc.creation_method_code NOT IN ('AUTOMATIC', 'BR') OR
            NVL(rm.payment_channel_code, 'BILLS_RECEIVABLE') <> p.payment_channel_code )
    AND    l.PAYMENT_TRXN_EXTENSION_ID = X.TRXN_EXTENSION_ID
    AND x.payment_channel_code = p.payment_channel_code;