Friday, September 14, 2012

Query to Fetch the Customer Payment Attributes and Receipt Method details in R12



Query to Fetch the Payment attribute details of the customer account level or customer account site level. At data level, customer account site level payment attribute record will have its column  acct_site_use_id populated.


SELECT iep.ext_payer_id,
       iep.payment_function,
       iep.party_id,
       iep.org_type,
       iep.org_id,
       ou.NAME org_name,
       iep.cust_account_id,
       iep.acct_site_use_id,
       iep.bank_charge_bearer_code,
       iep.dirdeb_instruction_code,
       iep.debit_advice_delivery_method,
       iep.debit_advice_email,
       iep.debit_advice_fax
  FROM iby_external_payers_all iep,
       hr_operating_units ou
 WHERE iep.org_id          = ou.organization_id(+)
   AND iep.cust_account_id = vl_cust_account_id
   AND iep.acct_site_use_id= vl_acct_site_use_id


Query to find the receipt methods associated with Customer Account/Site


SELECT rcrm.cust_receipt_method_id,
       rcrm.customer_id,
       rcrm.receipt_method_id,
       rcrm.receipt_method_name,
       rcrm.primary_flag,
       rcrm.site_use_id,
       rcrm.start_date,
       rcrm.end_date
  FROM ar_cust_receipt_methods_v rcrm
 WHERE 1 = 1
   AND rcrm.customer_id = :vl_cust_account_id
   AND rcrm.site_use_id = :vl_cust_site_use_id

1 Responses to “Query to Fetch the Customer Payment Attributes and Receipt Method details in R12”

Unknown said...
March 22, 2017 at 10:26 PM

I created a bank and attached to a receipt method. But later when I query, I am not able to get bank created in that receipt method. Kindly help.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.