Oracle EBS Query to Retrieve Customer Name and Account Details

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 EBS API to Create Application Users – Step-by-Step Guide

DECLARE
      lv_user_name varchar2(50) :='&user_name';
      lv_password varchar2(30) :='&password';
      lv_session_id integer    := userenv('sessionid');
BEGIN
   fnd_user_pkg.createuser(x_user_name           => lv_user_name,
                          x_owner                => NULL,
                          x_unencrypted_password => lv_password,
                          x_session_number       => lv_session_id,
                          x_start_date           => sysdate,
                          x_end_date             => NULL,
                          x_email_address        => NULL
                         );

       DBMS_OUTPUT.put_line('User:' || lv_user_name || 'Created Successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Unable to create User due to' || SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 100));
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.

Important Oracle TCA Lookup Tables You Should Know

1. Lookup for Customer Class Code :
      select lookup_type,lookup_code,meaning
      from ar_lookups
      where lookup_type in (select class_category from HZ_CLASS_CATEGORIES);

2. Lookup for Customer Type :
       select lookup_type,lookup_code,meaning
       from ar_lookups
       where lookup_type='CUSTOMER_TYPE';

3. Lookup for Party Type :
       select lookup_type,lookup_code,meaning,description
       from ar_lookups
       where lookup_type='PARTY_TYPE';

4. Lookup for sales channel :
        select  lookup_type,lookup_code,meaning
        from oe_lookups
         where lookup_type='SALES_CHANNEL';

5. Lookup for SIC code :
        select  lookup_type,lookup_code,meaning
        from ar_lookups
        where lookup_type='SIC_CODE_TYPE';

6. Lookup for Status :
       SELECT lookup_type,lookup_code,
            meaning,
            start_date_active,
            end_date_active,
            description
     FROM ar_lookups
    WHERE lookup_type = 'REGISTRY_STATUS'
     AND enabled_flag  = 'Y';

7. Lookup for Countries :
       SELECT TERRITORY_CODE, NLS_TERRITORY
       FROM FND_TERRITORIES;

👋 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 EBS R12 Query to Identify Credit Card Types Easily

SELECT lookup_code,
      meaning,
      enabled_flag,
      start_date_active,
      end_date_active
FROM oe_lookups
WHERE lookup_type = 'CREDIT_CARD';

👋 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.

Complete Guide to Oracle EBS APIs and Their Practical Uses

·         API To Find Sales Order's Subtotal, discount, charges and Tax (OE_OE_TOTALS_SUMMARY.ORDER_TOTALS)

·         API for Cancelling the Purchase Order (PO) Document (PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT)

·         API for Deleting the category assignment to an item (INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT)

·         API for Updating Category Assignment of an item (INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT)

·         API to Book a sales order (OE_ORDER_PUB.PROCESS_ORDER )

·         API to Cancel a Sales Order (OE_ORDER_PUB.PROCESS_ORDER)

·         API to Cancel an Order Line (OE_ORDER_PUB.PROCESS_ORDER)

·         API to Check Existence of an Internal Bank R12 (CE_BANK_PUB.CHECK_BANK_EXIST)

·         API to Create Item Specific UOM Conversion (INV_CONVERT.CREATE_UOM_CONVERSION)

·         API to Create a Customer Account for an existing Party TCA R12 (HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT)

·         API to Create a Customer Profile TCA R12 (HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE)

·         API to Create a Customer Site TCA R12 (HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE)

·         API to Create a Customer Site Use TCA R12 (HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE)

·         API to Create a Party Site TCA R12 (HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE)

·         API to Create a Party Site Use TCA R12 (HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE_USE)

·         API to Create a Phone Number (Contacts) TCA R12 (HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT)

·         API to Create an Internal Bank Account in R12 CE_BANK_PUB.CREATE_BANK_ACCT

·         API to Create an Internal Bank Branch R12 (CE_BANK_PUB.CREATE_BANK_BRANCH)

·         API to Delete a Sales Order (OE_ORDER_PUB.PROCESS_ORDER )

·         API to Delete an Order Line (OE_ORDER_PUB.PROCESS_ORDER)

·         API to End Date an Internal Bank Branch in R12 - CE_BANK_PUB.SET_BANK_BRANCH_END_DATE

·         API to End Date an Internal Bank in R12 - CE_BANK_PUB.SET_BANK_END_DATE

·         API to Update Customer Address in Oracle TCA R12 (HZ_LOCATION_V2PUB.UPDATE_LOCATION)

·         API to Update Oracle Applications Password (fnd_user_pkg.updateuser)

·         API to Update Purchase Order Document (PO) (PO_CHANGE_API1_S.UPDATE_PO)

·         API to Update a Customer Account TCA R12 (HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT)

·         API to Update an Internal Bank Branch R12 (CE_BANK_PUB.UPDATE_BANK_BRANCH)

·         API to Update an Internal Bank in R12 (CE_BANK_PUB.UPDATE_BANK)

·         API to apply hold on AP invoice in R12 (AP_HOLDS_PKG.INSERT_SINGLE_HOLD)

·         API to cancel single AP invoice (AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE)

·         API to confirm on Order Header Status (OE_HEADER_STATUS_PUB)

·         API to confirm on Order Line Status (OE_LINE_STATUS_PUB)

·         API to create Group in TCA R12 (HZ_PARTY_V2PUB.CREATE_GROUP)

·         API to create Party and Customer Account in R12 (HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT)

·         API to create a Person Type Party and Customer Account TCA R12 (HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT)

·         API to create customer Addresses in TCA R12 (HZ_LOCATION_V2PUB.CREATE_LOCATION)

·         API to find AP INVOICE Status (AP_INVOICES_PKG.GET_APPROVAL_STATUS)

·         API to get combination id based on segment info (FND_FLEX_EXT.GET_COMBINATION_ID)

·         API to get open sales order quantity in oracle apps (OE_LINE_UTIL.GET_OPEN_QUANTITY)

·         API to get the concatenated segment values for a code combination id (FND_FLEX_EXT.GET_SEGS)

·         API to get the formatted contact details of a Party in oracle apps R12 (HZ_FORMAT_PHONE_V2PUB.PHONE_DISPLAY)

·         API to get the segment delimiter for the specified key flex field structure (FND_FLEX_EXT.GET_DELIMITER)

·         API to populate loc_id HZ_LOCATIONS R12 (HZ_TAX_ASSIGNMENT_V2PUB. CREATE_LOC_ASSIGNMENT )

·         API to populate the Descriptive element Value of an item ( inv_item_catalog_elem_pub.process_item_descr_elements)

·         API to release hold on AP invoice in R12 (AP_HOLDS_PKG.RELEASE_SINGLE_HOLD)

·         API to update AR Invoice Printing Details in R12 AR_INVOICE_SQL_FUNC_PUB

·         API to update AR Receipt in oracle apps R12 - AR_RECEIPT_UPDATE_API_PUB (UNIDENTIFIED to UNAPPLIED)

·         API to update a Customer Account Relationship TCA R12 (HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCT_RELATE)

·         API to update a Customer Profile TCA R12 (HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUSTOMER_PROFILE)

·         API to update an Internal Bank Account in Oracle Apps R12 (CE_BANK_PUB.UPDATE_BANK_ACCT)

·         AR: Reversal of Receipt through API (ar_receipt_api_pub.REVERSE)

·         AR: Unapplication of a Credit Memo through API (ar_cm_api_pub.unapply_on_account)

·         AR_INVOICE_API_PUB.create_single_invoice

·         AR_RECEIPT_API_PUB - Script to Create and Apply on account a AR Receipt

·         AR_RECEIPT_API_PUB.Apply_on_account ( Script to apply a receipt on account )

·         AR_RECEIPT_API_PUB.CREATE_MISC - R12 - Create Miscellaneous Cash Receipt in Oracle Apps

·         AR_RECEIPT_API_PUB.Unapply_on_account ( Script to unapply on account a Receipt in R12)

·         Add New Line to Existing Order Using the API (OE_ORDER_PUB.PROCESS_ORDER)

·         Ar_receipt_api_pub.Apply

·         Ar_receipt_api_pub.Create_and_apply

·         Ar_receipt_api_pub.Create_cash

·         Ar_receipt_api_pub.Unapply

·         Assign Delivery Details to a Delivery through API ( WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY )

·         Assigning Category set to Category via API in Oracle Apps (INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY)

·         Assigning category to an Item using API (INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT)

·         Autocreate Deliveries Through API WSH_DELIVERY_DETAILS_PUB.AUTOCREATE_DELIVERIES

·         CASH MANAGEMENT API'S (CE_BANK_PUB) IN ORACLE APPS R12

·         CE_BANK_PUB.CHECK_BRANCH_EXISTS - API to check existence of an Internal Bank Branch in R12

·         CE_BANK_PUB.CREATE_BANK API to Create an Internal Bank in R12

·         CE_BANK_PUB.UPDATE_BANK_BRANCH API to Update an Internal Bank Branch R12

·         Create Credit Card in Oracle Payments using API (IBY_FNDCPT_SETUP_PUB.CREATE_CARD)

·         Create Party of type Organization in Oracle TCA using API hz_party_v2pub.create_organization

·         Create a Customer Account Relationship API HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE

·         Create and Release Pciking Batch via API (wsh_picking_batches_pub.create_batch and wsh_picking_batches_pub.Release_batch)

·         Credit Memo Creation via API ( ar_credit_memo_api_pub.create_request )

·         FND_PROGRAM : Add Concurrent Program to Request Group via API

·         FND_PROGRAM : Delete Concurrent Program Definition via API

·         FND_PROGRAM : Delete Concurrent Program Executable via API

·         FND_PROGRAM : Delete Concurrent Program Parameter via API

·         FND_PROGRAM : Register Concurrent Program parameters via API

·         FND_PROGRAM : Register Concurrent Program via API

·         FND_PROGRAM : Remove Concurrent Program from Request Group via API

·         FND_PROGRAM : Create Concurrent Executable via API

·         FND_REQUEST.SUBMIT_REQUEST in R12

·         FND_USER_PKG.CREATEUSER ( Create Applications User via PLSQL)

·         GET ONHAND QUANTITIES THROUGH API in Oracle Apps R12 (INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES)

·         Generation of a LOT number for an Inventory item via API in R12

·         How to apply invoice in detail against the receipt through api (AR_RECEIPT_API_PUB.Apply_In_Detail)

·         How to attach documents from backend? ( fnd_webattch.add_attachment )

·         How to delete a attachment from backend (fnd_attached_documents2_pkg.delete_attachments)

·         How to do UOM Conversions through api? ( inv_convert.inv_um_convert_new )

·         How to get the Description of the Item based on Item catalog group using API (invicgds.inv_get_icg_desc)

·         How to get timezone based on ZIP code? (HZ_TIMEZONE_PUB.GET_TIMEZONE_ID)

·         How to set context and profile values from backend in R12 Oracle apps

·         IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK – R12 – API to Create External Bank

·         IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT – R12 – API to Create External Bank Account

·         IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH – R12 – API to Create External Bank Branch

·         IMPORT EXTERNAL BANK ACCOUNTS R12 ORACLE APPS

·         Item Categories Deletion through API in Oracle Apps (INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY)

·         Item Categories Updation through API in Oracle Apps (INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY)

·         Item categories creation through API in Oracle Apps (INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY)

·         Item categories in oracle apps R12

·         Item import based on Item template in R12 (insert script)

·         OE_HOLDS_PUB.APPLY_HOLDS -- Apply Hold Script

·         OE_HOLDS_PUB.RELEASE_HOLDS -- Release Holds Script

·         OE_ORDER_PUB.GET_ORDER -- API to collect existing sales order data in R12

·         OE_ORDER_PUB.PROCESS_ORDER ( Sample Script for R12)

·         OE_ORDER_PUB.PROCESS_ORDER in Oracle Apps R12

·         OE_ORDER_PUB.PROCESS_ORDER to Apply hold on a sales order

·         OE_ORDER_PUB.PROCESS_ORDER to Release a hold on sales order in R12

·         ORA-01403: no data found in Package AR_RECEIPT_API_PUB Procedure Apply

·         Picking Batch Creation Through API ( wsh_picking_batches_pub.create_batch)

·         Price List Import via API (QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST) in R12

·         Script to Submit Item Import (INCOIN) using FND_REQUEST in Oracle Apps R12

·         Script to get the Quantity reserved against an sales order line in oracle apps R12 (INV_RESERVATION_PUB.QUERY_RESERVATION_OM_HDR_LINE)

·         TRADING COMMUNITY ARCHITECTURE (TCA) API’S IN R12 ORACLE APPS

·         Unassign Delivery Details from Delivery through API ( WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY )

·         Update Order Header Details Using the API (OE_ORDER_PUB.PROCESS_ORDER)

·         hz_party_contact_v2pub.create_org_contact - API to create a Contact person for an organization in Oracle TCA.

👋 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.