Oracle Supplier and Site Bank Details: Essential Tables & Queries

SELECT   /*Supplier Information*/
         aps.segment1 oracle_supplier_number,
         aps.vendor_id,
         aps.vendor_name supplier_name,
         aps.party_id supplier_party_id,
         iepa.remit_advice_fax remit_advice_fax,
         iepa.remit_advice_email remit_advice_email/* Supplier Site Information */
         ,
         assa.vendor_site_id,
         assa.party_site_id supplier_party_site_id,
         assa.vendor_site_code vendor_site_code,
         assa.pay_site_flag pay_site_flag,
         assa.purchasing_site_flag purchasing_site_flag,
         assa.rfq_only_site_flag rfq_only_site_flag/* Bank Information*/
         ,
         ieba.ext_bank_account_id,
         hp.party_name Bank_party_name,
         ieba.bank_account_num bank_account_num,
         ieba.bank_account_name bank_account_name,
         ieba.country_code bank_acct_country_code,
         ieba.currency_code bank_acct_currency_code/* Bank Address */
         ,
         hp.address1 bank_address_line1,
         hp.address2 bank_address_line2,
         hp.address3 bank_address_line3,
         hp.city bank_address_city,
         hp.state bank_address_state,
         hp.postal_code bank_address_zip,
         hp.country bank_address_country/* Bank Branch Address */
         ,
         hp1.address1 branch_address_line1,
         hp1.address2 branch_address_line2,
         hp1.address3 branch_address_line3,
         hp1.city branch_address_city,
         hp1.state branch_address_state,
         hp1.postal_code branch_address_zip,
         hp1.country branch_address_country
  FROM   ap_supplier_sites_all assa,
         hz_parties hp,
         iby_ext_bank_accounts ieba,
         iby_external_payees_all iepa,
         iby_pmt_instr_uses_all ipiua,
         ap_suppliers aps,
         hz_parties hp1
 WHERE       assa.vendor_site_id = iepa.supplier_site_id
         AND hp.party_id = ieba.bank_id
         AND ipiua.instrument_id = ieba.ext_bank_account_id
         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
         AND assa.vendor_id = aps.vendor_id
         AND ieba.branch_id = hp1.party_id
         AND ipiua.instrument_type = 'BANKACCOUNT'
         AND aps.vendor_name LIKE '%vendor_name %'
         AND assa.vendor_site_id = '&vendor_site_id';





SELECT  aps.vendor_name "VERDOR NAME",
        apss.vendor_site_code "VENDOR SITE CODE",
        ieb.bank_name "BANK NAME",
        iebb.bank_branch_name "BANK BRANCH NAME",
        iebb.branch_number "BRANCH NUMBER",
        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM    ap.ap_suppliers aps,
        ap.ap_supplier_sites_all apss,
        apps.iby_ext_bank_accounts ieba,
        apps.iby_account_owners iao,
        apps.iby_ext_banks_v ieb,
        apps.iby_ext_bank_branches_v iebb
WHERE   aps.vendor_id = apss.vendor_id
        and iao.account_owner_party_id = aps.party_id
        and ieba.ext_bank_account_id = iao.ext_bank_account_id
        and ieb.bank_party_id = iebb.bank_party_id
        and ieba.branch_id = iebb.branch_party_id
        and ieba.bank_id = ieb.bank_party_id;

SQL Queries Related to Oracle Application Messages

SELECT    m.message_name,

                 m.message_text,

                 m.message_number,

                 a.application_short_name

FROM     FND_NEW_MESSAGES M,

                FND_APPLICATION a

WHERE  m.message_name LIKE '%&Enter_Message_Name%'

AND        m.language_code  = 'US'

AND        M.APPLICATION_ID = a.APPLICATION_ID;

How to Create Quote Anonymous Blocks in Oracle EBS

DECLARE
   l_org_id                        VARCHAR2 (1254);
   li_inventory_item_id            NUMBER;
   li_currency_code                VARCHAR2 (1254);
   li_uom_code                     VARCHAR2 (1254);
   li_quantity                     NUMBER;
   li_quote_category_code          VARCHAR2 (1254);
   li_line_list_price              VARCHAR2 (2000);
   li_order_type_id                VARCHAR2 (2000);
   li_price_list_id                NUMBER;
   li_quote_source_code            VARCHAR2 (2000);
   li_party_id                     NUMBER;
   li_payment_term_id              NUMBER;
   li_cust_account_id              NUMBER;
   li_invoice_to_cust_account_id   NUMBER;
   li_invoice_to_party_site_id     NUMBER;
   li_invoice_to_party_id          NUMBER;
   li_invoice_to_cust_party_id     NUMBER;
   li_total_quote_price            NUMBER;
   li_total_list_price             NUMBER;
   li_total_adjusted_amount        NUMBER;
   ln_line_number                  NUMBER;
   l_quote_line_id                 NUMBER;
   l_ref_line_id                   NUMBER;
   x_relationship_id               NUMBER;
   x_return_status                 VARCHAR2 (2000);
   x_msg_count                     VARCHAR2 (2000);
   x_msg_data                      VARCHAR2 (2000);
   l_px_quote_line_detail_id       NUMBER;
   px_quote_line_detail_id         NUMBER;
---create quote
   l_quote_status_id               NUMBER;
   l_control_rec                   aso_quote_pub.control_rec_type;
   l_qte_header_rec                aso_quote_pub.qte_header_rec_type;
   l_qte_line_rec                  aso_quote_pub.qte_line_rec_type;
   l_qte_line_tbl                  aso_quote_pub.qte_line_tbl_type;
   l_qte_line_dtl_tbl              aso_quote_pub.qte_line_dtl_tbl_type;
   -- l_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
   l_hd_payment_tbl                aso_quote_pub.payment_tbl_type;
   l_payment_rec                   aso_quote_pub.payment_rec_type;
   l_hd_shipment_rec               aso_quote_pub.shipment_rec_type;
   -- l_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
   l_hd_tax_detail_tbl             aso_quote_pub.tax_detail_tbl_type;
   l_tax_detail_rec                aso_quote_pub.tax_detail_rec_type;
   l_tax_control_rec               aso_tax_int.tax_control_rec_type;
   l_line_attr_ext_tbl             aso_quote_pub.line_attribs_ext_tbl_type;
   l_line_rltship_tbl              aso_quote_pub.line_rltship_tbl_type;
   l_price_adjustment_tbl          aso_quote_pub.price_adj_tbl_type;
   l_price_adj_attr_tbl            aso_quote_pub.price_adj_attr_tbl_type;
   l_price_adj_rltship_tbl         aso_quote_pub.price_adj_rltship_tbl_type;
   l_ln_price_attr_tbl             aso_quote_pub.price_attributes_tbl_type;
   l_ln_payment_tbl                aso_quote_pub.payment_tbl_type;
   l_ln_shipment_tbl               aso_quote_pub.shipment_tbl_type;
   l_ln_freight_charge_tbl         aso_quote_pub.freight_charge_tbl_type;
   l_ln_tax_detail_tbl             aso_quote_pub.tax_detail_tbl_type;
   lx_qte_header_rec               aso_quote_pub.qte_header_rec_type;
   lx_qte_line_tbl                 aso_quote_pub.qte_line_tbl_type;
   lx_qte_line_dtl_tbl             aso_quote_pub.qte_line_dtl_tbl_type;
   lx_hd_price_attr_tbl            aso_quote_pub.price_attributes_tbl_type;
   lx_hd_payment_tbl               aso_quote_pub.payment_tbl_type;
   lx_hd_shipment_rec              aso_quote_pub.shipment_rec_type;
   lx_hd_freight_charge_tbl        aso_quote_pub.freight_charge_tbl_type;
   lx_hd_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   lx_line_attr_ext_tbl            aso_quote_pub.line_attribs_ext_tbl_type;
   lx_line_rltship_tbl             aso_quote_pub.line_rltship_tbl_type;
   lx_price_adjustment_tbl         aso_quote_pub.price_adj_tbl_type;
   lx_price_adj_attr_tbl           aso_quote_pub.price_adj_attr_tbl_type;
   lx_price_adj_rltship_tbl        aso_quote_pub.price_adj_rltship_tbl_type;
   lx_ln_price_attr_tbl            aso_quote_pub.price_attributes_tbl_type;
   lx_ln_payment_tbl               aso_quote_pub.payment_tbl_type;
   lx_ln_shipment_tbl              aso_quote_pub.shipment_tbl_type;
   lx_ln_freight_charge_tbl        aso_quote_pub.freight_charge_tbl_type;
   lx_ln_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   lx_hd_shipment_tbl              aso_quote_pub.shipment_tbl_type;
   lx_return_status                VARCHAR2 (1);
   lx_msg_count                    NUMBER;
   l_quote_number                  NUMBER;
   lx_msg_data                     VARCHAR2 (2000);
   my_message                      VARCHAR2 (2000);
   l_file                          VARCHAR2 (2000);
   lnx_quote_line_id               NUMBER;
   l_hd_sales_credit_tbl           aso_quote_pub.sales_credit_tbl_type
                                     := aso_quote_pub.g_miss_sales_credit_tbl;
   l_ln_sales_credit_tbl           aso_quote_pub.sales_credit_tbl_type
                                     := aso_quote_pub.g_miss_sales_credit_tbl;
   lx_hd_sales_credit_tbl          aso_quote_pub.sales_credit_tbl_type;
   lx_quote_party_tbl              aso_quote_pub.quote_party_tbl_type;
   lx_ln_sales_credit_tbl          aso_quote_pub.sales_credit_tbl_type;
   lx_ln_quote_party_tbl           aso_quote_pub.quote_party_tbl_type;
--l_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;
   l_shipment_rec                  aso_quote_pub.shipment_rec_type;
--l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
   lx_hd_attr_ext_tbl              aso_quote_pub.line_attribs_ext_tbl_type;
   l_qte_line_dtl_rec              aso_quote_pub.qte_line_dtl_rec_type;
   lc_last_update_date             DATE;
---relatioship
   l_line_rtlship_rec              aso_quote_pub.line_rltship_rec_type;
   qte_lin                         NUMBER;
   my_message                      VARCHAR2 (2000);
BEGIN
--l_org_id := fnd_profile.VALUE('ORG_ID');
   l_org_id := 1;
   DBMS_APPLICATION_INFO.set_client_info (1);
   fnd_global.apps_initialize (1066, 50378, 279);
--user_id,responsibility_id,application id
   mo_global.init ('QOT');
   mo_global.set_policy_context ('S', 1);                   --For Multiple OU
--commit;
   l_control_rec.calculate_tax_flag := 'Y';
   l_control_rec.calculate_freight_charge_flag := 'Y';
   l_control_rec.pricing_request_type := 'ASO';
   l_control_rec.header_pricing_event := 'BATCH';
   l_qte_header_rec.quote_name := 'Payload Quote Testing';
   l_qte_header_rec.quote_source_code := 'Order Capture Quotes';
   l_qte_header_rec.currency_code := 'USD';
   l_qte_header_rec.party_id := 1241;
   l_qte_header_rec.price_list_id := 7040991;
----------------- LINES ---------------------------------
   l_qte_line_tbl (1).organization_id := 4;
   l_qte_line_tbl (1).operation_code := 'CREATE';
   l_qte_line_tbl (1).inventory_item_id := 28931;
   l_qte_line_tbl (1).quantity := 1;
   l_qte_line_tbl (1).uom_code := 'EA';
   l_qte_line_tbl (1).org_id := 1;
---------create quote
  dbms_output.put_line('begin for the create quote header id ');
   aso_quote_pub.create_quote
                        (p_api_version_number           => 1.0,
                         p_init_msg_list                => fnd_api.g_true,
                         p_control_rec                  => l_control_rec,
                         p_qte_header_rec               => l_qte_header_rec,
                         p_qte_line_tbl                 => l_qte_line_tbl,
                         p_hd_payment_tbl               => l_hd_payment_tbl,
                         p_hd_tax_detail_tbl            => l_hd_tax_detail_tbl,
                         x_qte_header_rec               => lx_qte_header_rec,
                         x_qte_line_tbl                 => lx_qte_line_tbl,
                         x_qte_line_dtl_tbl             => lx_qte_line_dtl_tbl,
                         x_hd_price_attributes_tbl      => lx_hd_price_attr_tbl,
                         x_hd_payment_tbl               => lx_hd_payment_tbl,
                         x_hd_shipment_rec              => lx_hd_shipment_rec,
                         x_hd_freight_charge_tbl        => lx_hd_freight_charge_tbl,
                         x_hd_tax_detail_tbl            => lx_hd_tax_detail_tbl,
                         x_line_attr_ext_tbl            => lx_line_attr_ext_tbl,
                         x_line_rltship_tbl             => lx_line_rltship_tbl,
                         x_price_adjustment_tbl         => lx_price_adjustment_tbl,
                         x_price_adj_attr_tbl           => lx_price_adj_attr_tbl,
                         x_price_adj_rltship_tbl        => lx_price_adj_rltship_tbl,
                         x_ln_price_attributes_tbl      => lx_ln_price_attr_tbl,
                         x_ln_payment_tbl               => lx_ln_payment_tbl,
                         x_ln_shipment_tbl              => lx_ln_shipment_tbl,
                         x_ln_freight_charge_tbl        => lx_ln_freight_charge_tbl,
                         x_ln_tax_detail_tbl            => lx_ln_tax_detail_tbl,
                         x_return_status                => lx_return_status,
                         x_msg_count                    => lx_msg_count,
                         x_msg_data                     => lx_msg_data
                        );

   fnd_msg_pub.count_and_get (p_encoded      => 'F',
                              p_count        => lx_msg_count,
                              p_data         => lx_msg_data
                             );
--   COMMIT;
   DBMS_OUTPUT.put_line ('no. of FND messages :' || lx_msg_count);

   FOR k IN 1 .. lx_msg_count
   LOOP
      lx_msg_data := fnd_msg_pub.get (p_msg_index => k, p_encoded => 'F');
      DBMS_OUTPUT.put_line ('Error msg: ' || SUBSTR (lx_msg_data, 1, 240));
   END LOOP;

   DBMS_OUTPUT.put_line ('Return Status: ' || lx_return_status);
   DBMS_OUTPUT.put_line ('msg count:' || TO_CHAR (lx_msg_count));
   DBMS_OUTPUT.put_line ('Message Data: ' || lx_msg_data);
   DBMS_OUTPUT.put_line (   'qte_header_id: '
                         || TO_CHAR (lx_qte_header_rec.quote_header_id)
                        );
   DBMS_OUTPUT.put_line ('end');
--   COMMIT;
END;

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';

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

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;

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;

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';

Top 10 New Oracle Forms and OAF Personalization Tips

_https://drive.google.com/open?id=1JrfoJD0Gg6jeVGDYOh8jXtr-enlx9xX_

Oracle TCA Tables and APIs: Complete Reference for EBS Developers

https://drive.google.com/open?id=1yMqDEqEpe5nhk5v8wWaxP_CIhWjRS-Cw

Complete List of Oracle TCA Tables for Customer Data Management

https://drive.google.com/open?id=1eDjwcVvPFreZhN3gLHGMc8hq6vH1Kikh

Oracle Quote Tables Overview: Key Tables Used in Quoting Process

https://drive.google.com/open?id=1-Ppbto_ufcFh6ggjJFzvLdK7JdbvjVaF

P2P and O2C Cycle in Oracle Apps: Complete Documentation

https://drive.google.com/open?id=1zG8ULRtgZTNHovTFC1OySEeWPR7HRGup

Key Oracle Order Management Tables You Must Know

https://drive.google.com/open?id=1p3AANuS6p2yhSZkP44Zdu9_9hiPE4TkY

Oracle Apps AOL (Application Object Library) Overview & Key Concepts

https://drive.google.com/open?id=1cs30CEQTZ4xgl6UX8xYJL2FnlHawWP-N

Oracle TCA Tables and Descriptions: A Complete Reference Guide

https://drive.google.com/open?id=1xy6fhNWG_FGt48ACOgJocQRzK8JK3fHg

Oracle Bulk Collect and FORALL Explained with Examples

https://drive.google.com/open?id=17gSmFNEWMKNG-NGmk_Ku8T4QZGV7p7MY

Oracle All Tables Detail: Comprehensive Guide to Key Database Tables

https://drive.google.com/file/d/1NDns42vwSSBj9QAiXpH9orwag04PPIVf/view?usp=sharing

Oracle Forms Personalization: Customize Your EBS Experience Easily

https://drive.google.com/open?id=1gC3Xi-Vic6LDVmFuNLJ1c4nscrCWxvC5

Oracle Customer Account Creation Flow: Step-by-Step Process

https://drive.google.com/open?id=15sMP22izuX1f9cntK45F-uXQhgT6L8MN

Complete Set of Essential Oracle APIs for Developers

https://drive.google.com/open?id=1tcGq5a5GXJonsIN8r9NRLcfU3F1YjiHl

How to Use Oracle APIs Effectively: A Beginner’s Guide

https://drive.google.com/open?id=1Aot6lm7DUCRtz_FNI3H8aqft0FbSd_j1

Oracle Trading Community Architecture (TCA) Explained: Key Concepts & Benefits

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';

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;

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;

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';

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.