APPS.IBY_FNDCPT_VLD_PUB SQL Statements

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

Line: 112

select	ts.instrtype,
	tc.card_data_level,
	ts.tangibleid,
	tc.instr_owner_postalcode,
	tc.instr_owner_country,
	tc.shipfromzip,
	tc.shiptozip
  from	iby_batches_all b,
  	iby_trxn_summaries_all ts,
  	iby_trxn_core tc
 where	b.mbatchid = P_MBATCH_ID
   and	b.mbatchid = ts.mbatchid
   and	ts.trxnmid = tc.trxnmid;

Line: 150

  select length(batchid)
    into l_batchid_size
    from iby_batches_all
   where mbatchid = P_MBATCH_ID;

Line: 274

select	ts.instrtype,
	tc.card_data_level,
	ts.tangibleid,
	ts.reqtype,
	tc.instr_owner_postalcode,
	tc.instr_owner_country,
	tc.shipfromzip,
	tc.shiptozip
  from	iby_batches_all b,
  	iby_trxn_summaries_all ts,
  	iby_trxn_core tc
 where	b.mbatchid = P_MBATCH_ID
   and	b.mbatchid = ts.mbatchid
   and	ts.trxnmid = tc.trxnmid;

Line: 329

  SELECT
    mpayeeid,
    TRUNC(nvl(batchclosedate, SYSDATE))
  INTO
    l_payee_id,
    l_batch_close_date
  FROM
    iby_batches_all
  WHERE
    mbatchid = P_MBATCH_ID
   ;

Line: 343

  SELECT
    bepkey
  INTO
    l_bepkey
  FROM
    iby_trxn_summaries_all
  WHERE mbatchid = P_MBATCH_ID
  AND rownum=1;

Line: 358

  SELECT
    val.account_option_value
  INTO
    l_security_code
  FROM
    IBY_BEP_ACCT_OPT_VALS val,
    IBY_BEPKEYS           key,
    IBY_PAYEE             payee
  WHERE
    val.bep_account_id = key.bep_account_id
    AND  payee.payeeid = key.ownerid
    AND  val.account_option_code = 'SEC_CODE'
    AND  payee.mpayeeid = l_payee_id
    AND  key.key = l_bepkey
    ;

Line: 381

   * Select batch count based on the security code
   * linked to the payee account (not by the
   * payee id itself).
   */
--Bug# 13977390
-- Added another filter condition with IBY_BEPKEYS
SELECT
   COUNT(*)
INTO
 l_batch_count
FROM
 IBY_BEP_ACCT_OPT_VALS val,
 IBY_BEPKEYS           bepkey,
 IBY_PAYEE             payee,
 IBY_BATCHES_ALL       batch
WHERE
 val.account_option_code = 'SEC_CODE'
 AND
 val.account_option_value = l_security_code
 AND
 val.bep_account_id = bepkey.bep_account_id
 AND
 bepkey.key = l_bepkey
 AND
 payee.payeeid = bepkey.ownerid
 AND
 payee.mpayeeid = l_payee_id
 AND
 TRUNC(batchclosedate) = l_batch_close_date;

Line: 542

             	   select count(*)
             	     into l_doc_line_count
             	     from ar_invoice_lines_v
             	    where customer_trx_id = l_cust_trx_id;

Line: 556

                   select  hl.postal_code, hl.country
                     into  l_ship_to_zip, l_ship_to_country
                     from  hz_locations hl,
                  	   hz_party_sites hps,
                  	   hz_cust_acct_sites_all hcasa,
                  	   hz_cust_site_uses_all hcsua,
                  	   ar_invoice_header_v arihv
                    where  hl.location_id = hps.location_id and
                 	   hps.party_site_id = hcasa.party_site_id and
                 	   hcasa.cust_acct_site_id = hcsua.cust_acct_site_id and
                 	   hcsua.site_use_id = arihv.ship_to_site_use_id and
                 	   arihv.customer_trx_id = l_cust_trx_id;

Line: 605

     update iby_batches_all
        set SENTCOUNTERDAILY = l_batch_count
      where mbatchid = P_MBATCH_ID;

Line: 637

select	/*+INDEX(TC IBY_TRXN_CORE_TRXNMID_U1)*/
        ts.instrtype,
	tc.card_data_level,
	ts.tangibleid,
	tc.instr_owner_postalcode,
	tc.instr_owner_country,
	tc.shipfromzip,
	tc.shiptozip
  from	iby_batches_all b,
  	iby_trxn_summaries_all ts,
  	iby_trxn_core tc
 where	b.mbatchid = P_MBATCH_ID
   and	b.mbatchid = ts.mbatchid
   and	ts.trxnmid = tc.trxnmid;

Line: 676

  select length(batchid)
    into l_batchid_size
    from iby_batches_all
   where mbatchid = P_MBATCH_ID;

Line: 787

                   select  hl.postal_code, hl.country
                     into  l_ship_to_zip, l_ship_to_country
                     from  hz_locations hl,
                  	   hz_party_sites hps,
                  	   hz_cust_acct_sites_all hcasa,
                  	   hz_cust_site_uses_all hcsua,
                  	   ar_invoice_header_v arihv
                    where  hl.location_id = hps.location_id and
                 	   hps.party_site_id = hcasa.party_site_id and
                 	   hcasa.cust_acct_site_id = hcsua.cust_acct_site_id and
                 	   hcsua.site_use_id = arihv.ship_to_site_use_id and
                 	   arihv.customer_trx_id = l_cust_trx_id;

Line: 858

    SELECT  da.authorization_reference_number
            ,da.auth_sign_date
	    ,da.creditor_legal_entity_id
            ,da.creditor_identifier
	    ,da.cust_addr_id
      FROM  iby_debit_authorizations da
     WHERE   debit_auth_flag = 'Y'
       --- AND auth_cancel_date is null
       AND nvl(da.auth_cancel_date,sysdate+10) >= sysdate
       AND nvl(da.debit_auth_end,sysdate+10) >= sysdate
       AND da.debit_authorization_id =  (  SELECT MAX(debit_authorization_id)
		                                FROM iby_debit_authorizations
		                           WHERE external_bank_account_use_id = ci_account_use_id );

Line: 908

 SELECT eb.iban , bbv.eft_swift_code into
        l_iban , l_swift_code
   FROM iby_pmt_instr_uses_all uses,
        iby_ext_bank_accounts eb,
        ce_bank_branches_v bbv
  WHERE uses.instrument_id = eb.ext_bank_account_id
    AND uses.payment_flow = 'FUNDS_CAPTURE'
    AND uses.instrument_type = 'BANKACCOUNT'
    AND eb.branch_id = bbv.branch_party_id
    AND uses.instrument_payment_use_id = p_assignment_id;

Line: 929

     SELECT  (address_line_1  || ', '
            || DECODE(address_line_2, NULL, '', ',')
            || DECODE(address_line_3, NULL, '', ',')
            || town_or_city || ', '  || country  || ', '
            || postal_code) PayeeAddress
      INTO  l_payee_address
      FROM  xle_firstparty_information_v
     WHERE  legal_entity_id = l_creditor_legal_entity_id;

Line: 1020

select	ts.trxnmid
  from	iby_batches_all b
  	,iby_trxn_summaries_all ts
 where	b.mbatchid = P_MBATCH_ID
   and	b.mbatchid = ts.mbatchid ;

Line: 1029

select	 da.authorization_reference_number
        ,da.auth_sign_date
        ,da.creditor_legal_entity_id
        ,da.creditor_identifier
	,da.cust_addr_id
	,da.creditor_le_name
	,da.external_bank_account_use_id
  from	iby_trxn_summaries_all ts
        ,iby_debit_authorizations da
 where	ts.trxnmid = ci_trxnmid
   and  ts.debit_authorization_id  =  da.debit_authorization_id
   --  and  da.auth_cancel_date is NULL
   AND nvl(da.auth_cancel_date,sysdate+10) >= sysdate
   AND nvl(da.debit_auth_end,sysdate+10) >= sysdate
   and  da.curr_rec_indi = 'Y' ;

Line: 1049

 select  vals.account_option_code
         ,vals.account_option_value
   from  iby_trxn_summaries_all ts
         ,iby_bepkeys keys
         ,iby_bep_acct_opt_vals vals
         ,iby_payee payee
  where  ts.trxnmid = ci_trxnmid
    and  ts.payeeid = payee.payeeid
    and  ts.payeeid = keys.ownerid
    and  ts.bepkey = keys.key
    and  keys.ownertype = 'PAYEE'
    and  keys.bep_account_id = vals.bep_account_id (+)
  --  and  vals.account_option_code = ci_acct_option_code;

Line: 1139

       SELECT eb.iban , bbv.eft_swift_code into
              l_iban , l_swift_code
         FROM iby_pmt_instr_uses_all uses,
              iby_ext_bank_accounts eb,
              ce_bank_branches_v bbv
        WHERE uses.instrument_id = eb.ext_bank_account_id
          AND uses.payment_flow = 'FUNDS_CAPTURE'
          AND uses.instrument_type = 'BANKACCOUNT'
          AND eb.branch_id = bbv.branch_party_id
          AND uses.instrument_payment_use_id = l_external_bank_account_use_id;

Line: 1164

             SELECT  (address_line_1  || ', '
                     || DECODE(address_line_2, NULL, '', ',')
                     || DECODE(address_line_3, NULL, '', ',')
                     || town_or_city || ', '  || country  || ', '
                     || postal_code ) PayeeAddress
		     , name LegalEntityName
               INTO  l_payee_address, l_le_name
               FROM  xle_firstparty_information_v
              WHERE  legal_entity_id = l_creditor_legal_entity_id ;

Line: 1372

  update iby_debit_authorizations
     set debit_auth_flag = 'N' , debit_auth_end = sysdate
   where debit_authorization_id in
         ( select debit_authorization_id
             from iby_trxn_summaries_all
            where trxntypeid = 100
              and instrtype = 'BANKACCOUNT'
              and mbatchid = P_MBATCH_ID
              and seq_type in ('FNAL', 'OOFF')
                   );

Line: 1411

    SELECT ts.trxnmid
    FROM iby_batches_all b ,
      iby_trxn_summaries_all ts
    WHERE b.mbatchid = P_MBATCH_ID
    AND b.mbatchid   = ts.mbatchid ;

Line: 1419

    SELECT da.authorization_reference_number
    FROM iby_trxn_summaries_all ts ,
      iby_debit_authorizations da
    WHERE ts.trxnmid                      = ci_trxnmid
    AND ts.debit_authorization_id         = da.debit_authorization_id
    AND NVL(da.auth_cancel_date,sysdate) >= ts.settledate
    AND NVL(da.debit_auth_end,sysdate)   >= ts.settledate
    AND NVL(da.auth_sign_date,sysdate)   <= ts.settledate
    AND da.curr_rec_indi                  = 'Y' ;