Supplier and Site Bank detail in Oracle Apps R12

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

PL/SQL - Create Quote in Oracle Apps

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;

Query to get the customer name,account number,transaction number,receipt number and amount detail

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

Receivables Tables in Oracle Apps R12

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

Form Personalization Query in Oracle Apps

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;

API To Delete Form Personalization in Oracle Apps

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;

Forms Personalization Query in Oracle Apps

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

Ten New Forms and OAF Personalization Examples

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

Trade Community Architecture All Tables and API

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

Trade Community Architecture All Tables

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

Quote Table in Oracle Apps R12

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

Procure To Pay and Order To Cash Document

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

Order Management Tables in Oracle Apps

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

Oracle Apps - Application Object Library (AOL)

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

Oracle TCA Tables Descriptions

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

Bulk Collection and FOR ALL in Oracle Apps

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

Oracle All Tables Details

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

Forms Personalization in Oracle Apps

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

Oracle Application R12 Customer Account Creation Flow

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

Complete set of oracle API

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

How to use API

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

Trading Community Architecture

Query to get the customer name,account number,transaction number,receipt and amount detail

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

API to create application user in Oracle Apps R12

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 TCA Lookups

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;

Query to Find credit card type in Oracle Apps R12

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

Uses of Oracle API

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