Oracle EBS Query to Retrieve Customer Name and Account Information

SELECT  hca.account_number,
                 hp.party_name,
                rcta.trx_number,
                acra.receipt_number,
                rcta.trx_date,
                acra.creation_date,
                acra.amount
FROM     hz_parties hp,
                hz_cust_accounts hca,
                ra_customer_trx_all rcta,
                ar_cash_receipts_all acra,
                ar_receivable_applications_all araa
WHERE   hp.party_id = hca.party_id
                 and rcta.sold_to_customer_id = hca.cust_account_id
                 and araa.cash_receipt_id = acra.cash_receipt_id
                 and araa.applied_customer_trx_id = rcta.customer_trx_id
                 and account_number = '&account_number';

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Oracle Apps Receivables Tables: Key Tables Explained for EBS

Transactions
RA_CUSTOMER_TRX_ALL	Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL	Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL	Distribution for Transaction Lines
RA_CUST_TRX_LINE_SALESREPS_ALL	Salesrep information for Transaction Lines
Transaction Interface Tables
RA_INTERFACE_LINES_ALL	Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL	Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL	Transaction Distribution information
RA_INTERFACE_ERRORS_ALL	Transaction errors table
AR_PAYMENTS_INTERFACE_ALL	Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL	Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL	Lockbox transfers the receipts that pass validation to the interim tables
Receipts tables
AR_CASH_RECEIPTS_ALL	Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL	stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL	This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL	This table stores all of the activity that is contained for the life cycle of a receipt. • Each row represents one step. • The status field for that row tells you which step the receipt has reached. • Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.
Customer Tables
HZ_PARTIES	A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS	This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES	This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL	This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL	This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS	A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
Setup tables
RA_CUST_TRX_TYPES_ALL	This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES	This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS	This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Oracle Form Personalization Query: How to Retrieve Personalization Data

select fcr.id,

       fff.user_function_name,

       fcr.form_name,

       fcr.sequence,

       fcr.description,

       fcr.enabled,

       fu.user_name,

       fcr.trigger_event,

       fcr.trigger_object,

       fcr.last_update_date,

       fcr.condition,

       fff.function_name,

       ffca.sequence,

       ffca.property_value,

       ffca.target_object,

       ffca.message_text,

       fffs.irep_assoc_function_name,

       fffs.irep_description,

       fffs.irep_method_name

from fnd_form_custom_rules fcr,

     fnd_form_functions_vl fff,

     fnd_user fu,

     fnd_form_custom_actions ffca,

     fnd_form_functions fffs,

     fnd_form_functions_tl ffft

where fcr.function_name = fff.function_name

and ffca.created_by = fu.user_id

and fcr.last_updated_by = fu.user_id

and ffca.rule_id = fcr.id

and fffs.function_id = ffft.function_id;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Oracle API to Delete Form Personalization: Quick and Safe Method

DECLARE
       i_rule_key varchar2(30);
       i_rule_type varchar2(30);
       i_function_name varchar2(30);
       i_form_name varchar2(30);
    cursor c1
    is
    select * from fnd_form_custom_rules
    where id = '2222';
     
BEGIN
      fnd_form_custom_rules_pkg.
          delete_set (x_rule_key        => i_rule_key,
                      x_rule_type       => i_rule_type,
                      x_function_name   => i_function_name,
                      x_form_name       => i_form_name);
   DBMS_OUTPUT. put_line (' Personalization has been Successfully Completed');
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Oracle Forms Personalization Query Tips for Easy Customization

select fcr.id,
       fff.user_function_name,
       fcr.form_name,
       fcr.sequence,
       fcr.description,
       fcr.enabled,
       fu.user_name,
       fcr.trigger_event,
       fcr.trigger_object,
       fcr.last_update_date
from fnd_form_custom_rules fcr,
        fnd_user fu,
        fnd_form_functions_vl fff
where fcr.function_name = fff.function_name
and fcr.last_updated_by = fu.user_id
and user_name = '&user_name'
AND user_function_name = 'Quick Sales Orders';

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.