Oracle Apps R12: Customer Details and Order Management Guide

SELECT
     h.order_number
    ,h.sold_to_org_id bill_cust_account_id
    ,h.ship_to_org_id ship_to_site_use_id
    ,h.invoice_to_org_id bill_to_site_use_id
    ,hp.party_name "Customer Name"
    ,hca.account_name
    ,hca.org_id
    ,hcasab.orig_system_reference   BILL_TO_ORIG_REF
    ,hpb.status                     BILL_TO_STATUS
    ,'ADDRESS1 - '||bill_loc.address1||','||CHR(10)||
     'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||
     'ADDRESS3 - '||bill_loc.address3||','||CHR(10)||
     'CITY     - '||bill_loc.city||','||CHR(10)||
     'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)||
     'COUNTRY  - '|| bill_loc.country  BILL_TO_ADDRESS
    ,hcasas.orig_system_reference      SHIP_TO_ORIG_REF
    ,hps.status                        SHIP_TO_STATUS
    ,'ADDRESS1 - '||ship_loc.address1||','||CHR(10)||
     'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||
     'ADDRESS3 - '||ship_loc.address3||','||CHR(10)||
     'CITY     - '||ship_loc.city||','||CHR(10)||
     'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)||
     'COUNTRY  - '|| ship_loc.country  SHIP_TO_ADDRESS
FROM oe_order_headers_all h
    ,hz_parties hp
    ,hz_cust_accounts hca
    ,hz_cust_acct_sites_all hcasab
    ,hz_cust_acct_sites_all hcasas
    ,hz_cust_site_uses_all hzsuab
    ,hz_cust_site_uses_all hzsuas
    ,hz_party_sites hps
    ,hz_party_sites hpb
    ,hz_locations bill_loc
    ,hz_locations ship_loc
WHERE 1 =1
AND hp.party_id             = hca.party_id
AND hca.CUST_ACCOUNT_ID     = h.sold_to_org_id
AND hcasab.cust_account_id  = hca.cust_account_id
AND hcasas.cust_account_id  = hca.cust_account_id
AND hpb.location_id         = bill_loc.location_id
AND hps.location_id         = ship_loc.location_id
AND hcasab.party_site_id    = hpb.party_site_id
AND hcasas.party_site_id    = hps.party_site_id
AND hcasab.cust_acct_site_id= hzsuab.cust_acct_site_id
AND hcasas.cust_acct_site_id= hzsuas.cust_acct_site_id
AND h.ship_to_org_id        = hzsuas.site_use_id
AND h.invoice_to_org_id     = hzsuab.site_use_id
AND h.order_number          = '&order_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 R12 Trading Community Architecture (TCA) Process Flow Explained

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

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

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;

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

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;

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