TCA API: Sample Code to Create AR Customer & Site from AP Supplier & Supplier Site using same Party

Doc ID 1447883.1

We will use following sql to find out Party details from Supplier tables

SELECT DISTINCT a.party_number, ps.party_site_number, a.party_id,
'CUST-' ||a.party_id||'-'||ass.org_id AS customer_ref,
'ADD-'||ps.party_site_id||'-'||ass.org_id AS address_ref,
ps.party_site_id,
ass.address_line1,
ass.city, ass.state, ass.zip, ass.country,
a.orig_system_reference AS party_ref,
a.party_name AS customer_name, 
ass.org_id
FROM ap_suppliers ap,ap_supplier_sites_all ass,
hz_parties a,hz_party_sites ps
WHERE ap.vendor_id = ass.vendor_id
AND a.party_id = ap.party_id
AND ps.party_id = ap.party_id
AND ass.party_site_id = ps.party_site_id
AND a.party_name = ap.vendor_name
and a.party_id = 12465 -- Please pass your Party_id or Comment this line
and NOT Exists (select 1 from hz_cust_accounts a
where a.party_id = a.party_id);


DECLARE
 l_num_user_id       NUMBER;
 l_num_appl_id       NUMBER;
 l_num_resp_id       NUMBER;
 cust_account_rec_type   hz_cust_account_v2pub.cust_account_rec_type;
 l_num_obj_ver_num     NUMBER;
 l_chr_return_status    VARCHAR2 (2000);
 l_num_msg_count      NUMBER;
 l_chr_msg_data       VARCHAR2 (500);
 l_num_profile_id      NUMBER;
 l_organization_rec     hz_party_v2pub.organization_rec_type;
 l_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;
 l_num_cust_id       NUMBER;
 l_chr_acct_num       VARCHAR2 (500);
 l_num_party_id       NUMBER;
 l_chr_party_number     VARCHAR2 (500);
 l_cust_acct_site_use_rec  hz_cust_account_site_v2pub.cust_site_use_rec_type;
 l_cust_acct_site_rec    hz_cust_account_site_v2pub.cust_acct_site_rec_type;
 l_chr_sit_return_status  VARCHAR2 (500);
 l_num_sit_msg_count    NUMBER;
 l_chr_sit_msg_data     VARCHAR2 (500);
 l_chr_situ_return_status  VARCHAR2 (500);
 l_num_situ_msg_count    NUMBER;
 l_chr_situ_msg_data    VARCHAR2 (500);
 l_num_site_use_id     NUMBER;
 CURSOR update_base_tables_cur
 IS
   SELECT DISTINCT a.party_number, ps.party_site_number, a.party_id,
           'CUST-' ||a.party_id||'-'||ass.org_id AS customer_ref,
           'ADD-'||ps.party_site_id||'-'||ass.org_id AS address_ref,
           ps.party_site_id,
           ass.address_line1,
           ass.city, ass.state, ass.zip, ass.country,
           'N' primary_site_use_flag,
           a.orig_system_reference AS party_ref,
           a.party_name AS customer_name, ass.org_id
        FROM apps.ap_suppliers ap,
           apps.ap_supplier_sites_all ass,
           apps.hz_parties a,
           apps.hz_party_sites ps
        WHERE ap.vendor_id = ass.vendor_id
         AND a.party_id = ap.party_id
         AND ps.party_id = ap.party_id
         AND ass.party_site_id = ps.party_site_id
         AND a.party_name = ap.vendor_name
         and a.party_id = 12465 -- modify this part/query
         --and ass.org_id = 204
         and NOT Exists (select 1 from hz_cust_accounts a
         where a.party_id = a.party_id);
BEGIN
FND_GLOBAL.APPS_INITIALIZE( &user_id, &responsibility_id, 222); -- input from 1st sql
dbms_output.put_line('***************************');
 FOR update_base_tables_rec IN update_base_tables_cur
 LOOP
   NULL;
   cust_account_rec_type.cust_account_id := fnd_api.g_miss_num;
   cust_account_rec_type.account_name := update_base_tables_rec.customer_name;
   l_organization_rec.party_rec.party_id := update_base_tables_rec.party_id;
   l_organization_rec.party_rec.party_number := update_base_tables_rec.party_number;
   l_organization_rec.organization_name := update_base_tables_rec.customer_name;
   cust_account_rec_type.orig_system_reference := update_base_tables_rec.customer_ref;
   l_customer_profile_rec.party_id := update_base_tables_rec.party_id;
   l_customer_profile_rec.profile_class_id := 0 ; -- use DEFAULT profile with id=0
   l_customer_profile_rec.created_by_module := 'HZ_CPUI';
   cust_account_rec_type.created_by_module := 'HZ_CPUI';
   hz_cust_account_v2pub.create_cust_account
             (p_init_msg_list       => fnd_api.g_false,
              p_cust_account_rec     => cust_account_rec_type,
              p_organization_rec     => l_organization_rec,
              p_customer_profile_rec   => l_customer_profile_rec,
              p_create_profile_amt    => fnd_api.g_true,
              x_cust_account_id      => l_num_cust_id,
              x_account_number      => l_chr_acct_num,
              x_party_id         => l_num_party_id,
              x_party_number       => l_chr_party_number,
              x_profile_id        => l_num_profile_id,
              x_return_status       => l_chr_return_status,
              x_msg_count         => l_num_msg_count,
              x_msg_data         => l_chr_msg_data
             );
dbms_output.put_line('x_return_status: '||l_chr_return_status);
dbms_output.put_line('x_cust_account_id: '||l_num_cust_id);
dbms_output.put_line('x_account_number: '||l_chr_acct_num);
dbms_output.put_line('x_party_id: '||l_num_party_id);
   IF l_chr_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line ('Message(' || j || '):= ' || l_chr_msg_data);
    END LOOP;
   END IF;
   BEGIN
    SELECT cust_account_id
     INTO l_cust_acct_site_rec.cust_account_id
     FROM hz_cust_accounts
     WHERE orig_system_reference = update_base_tables_rec.customer_ref;
   EXCEPTION
    WHEN OTHERS
    THEN
      l_cust_acct_site_rec.cust_account_id := fnd_api.g_miss_num;
   END;
   l_cust_acct_site_rec.party_site_id := update_base_tables_rec.party_site_id;
   l_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
   l_cust_acct_site_rec.orig_system_reference := update_base_tables_rec.address_ref;
   l_cust_acct_site_rec.status := 'A';
   l_cust_acct_site_rec.org_id := update_base_tables_rec.org_id;
   mo_global.init ('ONT');
   mo_global.set_policy_context (p_access_mode   => 'S',
                  p_org_id      => update_base_tables_rec.org_id
                 );
   hz_cust_account_site_v2pub.create_cust_acct_site
                (p_init_msg_list      => 'T',
                p_cust_acct_site_rec   => l_cust_acct_site_rec,
                x_cust_acct_site_id    => l_num_obj_ver_num,
                x_return_status      => l_chr_sit_return_status,
                x_msg_count        => l_num_sit_msg_count,
                x_msg_data        => l_chr_sit_msg_data
                );
dbms_output.put_line('x_cust_acct_site_id: '||l_num_obj_ver_num);
dbms_output.put_line('x_return_status: '||l_chr_sit_return_status);
   IF l_chr_sit_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_sit_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line (  'Site Message('
                 || j
                 || '):= '
                 || l_chr_sit_msg_data
                );
    END LOOP;
   END IF;
   BEGIN
    SELECT cust_acct_site_id
     INTO l_cust_acct_site_use_rec.cust_acct_site_id
     FROM hz_cust_acct_sites_all
     WHERE orig_system_reference = update_base_tables_rec.address_ref;
   EXCEPTION
    WHEN OTHERS
    THEN
      l_cust_acct_site_use_rec.cust_acct_site_id := fnd_api.g_miss_num;
   END;
   l_cust_acct_site_use_rec.org_id := update_base_tables_rec.org_id;
   l_cust_acct_site_use_rec.site_use_code := 'BILL_TO';
   l_cust_acct_site_use_rec.status := 'A';
   l_cust_acct_site_use_rec.primary_flag := 'Y';
   l_cust_acct_site_use_rec.orig_system_reference :=
                      update_base_tables_rec.address_ref;
   l_cust_acct_site_use_rec.created_by_module := 'HZ_CPUI';
   mo_global.set_policy_context (p_access_mode   => 'S',
                  p_org_id      => update_base_tables_rec.org_id
                 );
   hz_cust_account_site_v2pub.create_cust_site_use
              (p_init_msg_list       => 'T',
              p_cust_site_use_rec     => l_cust_acct_site_use_rec,
              p_customer_profile_rec   => l_customer_profile_rec,
              p_create_profile      => fnd_api.g_true,
              p_create_profile_amt    => fnd_api.g_true,
              x_site_use_id        => l_num_site_use_id,
              x_return_status       => l_chr_situ_return_status,
              x_msg_count         => l_num_situ_msg_count,
              x_msg_data         => l_chr_situ_msg_data
              );
dbms_output.put_line('x_site_use_id: '||l_num_site_use_id);
dbms_output.put_line('x_return_status: '||l_chr_situ_return_status);
   IF l_chr_situ_return_status != 'S'
   THEN
    --Display all the error messages
    FOR j IN 1 .. fnd_msg_pub.count_msg
    LOOP
      DBMS_OUTPUT.put_line (j);
      l_chr_situ_msg_data :=
            fnd_msg_pub.get (p_msg_index   => j,
                     p_encoded    => 'F');
      DBMS_OUTPUT.put_line (  'Site Use Message('
                 || j
                 || '):= '
                 || l_chr_situ_msg_data
                );
    END LOOP;
   END IF;
 END LOOP;
END;
/

Oracle Trading Community: TCA API Overview, Setup, Sample Scripts and Troubleshooting (Doc ID 1637029.2)

Metalink Id for TCA API's

Oracle Trading Community: TCA API Overview, Setup, Sample Scripts and Troubleshooting (Doc ID 1637029.2)

Function to convert number to Word

FUNCTION to_word_i( amount IN NUMBER)
   RETURN VARCHAR2
AS
   v_length                                     INTEGER := 0;
   v_num2                                       VARCHAR2(50) := NULL;
   v_amount                                     VARCHAR2(50) := TO_CHAR( TRUNC( amount));
   v_word                                       VARCHAR2(4000) := NULL;
   v_word1                                      VARCHAR2(4000) := NULL;

   TYPE myarray IS TABLE OF VARCHAR2(255);

   v_str                                        myarray
                                                   := myarray(
                                                         ' thousand '
                                                       , ' lakh '
                                                       , ' crore '
                                                       , ' arab '
                                                       , ' kharab '
                                                       , ' shankh ');
BEGIN
   IF TO_CHAR( amount) LIKE '%.%'
   THEN
      IF SUBSTR( amount, INSTR( amount, '.') + 1) > 0
      THEN
         v_num2                                    := SUBSTR( amount, INSTR( amount, '.') + 1);

         IF LENGTH( v_num2) < 2
         THEN
            v_num2                                    := v_num2 * 10;
         END IF;

         v_length                                  := LENGTH( v_num2);
         v_word1                                   :=
               ' AND '
            || (TO_CHAR( TO_DATE( SUBSTR( v_num2, LENGTH( v_num2) - 1, 2), 'j'), 'Jsp'))
            || ' paise ';
      END IF;
   END IF;

   IF v_amount = '0'
   THEN
      v_word                                    := 'zero';
   ELSE
      IF (SUBSTR( v_amount, LENGTH( v_amount) - 2, 3) <> 0)
      THEN
         v_word                                    :=
               TO_CHAR( TO_DATE( SUBSTR( v_amount, LENGTH( v_amount) - 2, 3), 'J'), 'Jsp')
            || v_word;
      END IF;

      v_amount                                  := SUBSTR( v_amount, 1, LENGTH( v_amount) - 3);

      FOR i IN 1 .. v_str.COUNT
      LOOP
         EXIT WHEN v_amount IS NULL;

         IF (SUBSTR( v_amount, LENGTH( v_amount) - 1, 2) <> 0)
         THEN
            v_word                                    :=
                  TO_CHAR( TO_DATE( SUBSTR( v_amount, LENGTH( v_amount) - 1, 2), 'J'), 'Jsp')
               || v_str( i)
               || v_word;
         END IF;

         v_amount                                  := SUBSTR( v_amount, 1, LENGTH( v_amount) - 2);
      END LOOP;
   END IF;

   v_word                                    :=
         v_word
      || ' '
      || v_word1
      || ' only ';
   v_word                                    := REPLACE( RTRIM( v_word), '  ', ' ');
   v_word                                    := REPLACE( RTRIM( v_word), '-', ' ');
   RETURN INITCAP( v_word);
END to_word_i;

How to run Api Hz_party_search.Find_parties Example

Doc ID 804145.1 

 

 --Create Party

DECLARE
   l_organization_rec   hz_party_v2pub.organization_rec_type;
   p_party_id           NUMBER;
   p_party_number       VARCHAR2 (20);
   p_profile_id         NUMBER;
   l_return_status      VARCHAR2 (2000);
   l_msg_count          NUMBER;
   l_msg_data           VARCHAR2 (2000);
BEGIN
   l_organization_rec.organization_name := 'Test Party';
   l_organization_rec.created_by_module := 'HZ_TRADING_COMMUNITY';
   hz_party_v2pub.create_organization ('T',
                                       l_organization_rec,
                                       l_return_status,
                                       l_msg_count,
                                       l_msg_data,
                                       p_party_id,
                                       p_party_number,
                                       p_profile_id);
   DBMS_OUTPUT.put_line ('l_return_status:' || l_return_status);

   IF l_msg_count > 1
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         DBMS_OUTPUT.put_line (
               i
            || '. '
            || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255));
      END LOOP;
   END IF;
END;

l_return_status:S

PL/SQL procedure successfully completed.

-- In our test case DQM profile HZ: DQM syncronization method  is set at Batch - Ran the DQM synchronization program so the party is moved to --the HZ Staged tables

--- Now Find that party

 

DECLARE
   l_party_rec         hz_party_search.party_search_rec_type;
   l_site_list         hz_party_search.party_site_list;
   l_cont_list         hz_party_search.contact_list;
   l_cont_point_list   hz_party_search.contact_point_list;
   l_return_id         NUMBER;
   l_num_matches       NUMBER;
   l_ret_status        VARCHAR2 (2000);
   l_msg_count         NUMBER;
   l_msg_data          VARCHAR2 (2000);
   l_rule_id           NUMBER := 61;
BEGIN
   l_party_rec.party_name := 'Test Party';
   hz_party_search.find_parties (

p_init_msg_list        => fnd_api.g_true,
x_rule_id              => l_rule_id,
p_party_search_rec     => l_party_rec,
p_party_site_list      => l_site_list,
p_contact_list         => l_cont_list,
p_contact_point_list   => l_cont_point_list,
p_restrict_sql         => NULL-- ,p_match_type => NULL
,
p_search_merged        => 'N',
x_search_ctx_id        => l_return_id,
x_num_matches          => l_num_matches,
x_return_status        => l_ret_status,
x_msg_count            => l_msg_count,
x_msg_data             => l_msg_data);

   DBMS_OUTPUT.put_line ('l_return_status:' || l_ret_status);
   DBMS_OUTPUT.put_line ('l_num_matches:' || l_num_matches);

   IF l_ret_status <> 'S'
   THEN
      IF l_msg_count > 1
      THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            DBMS_OUTPUT.put_line (
                  i
               || '.'
               || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
                          1,
                          255));
         END LOOP;
      END IF;
   END IF;
END; 

 

l_return_status:S
l_num_matches:1


PL/SQL procedure successfully completed.

PTO Configuration Cycle In Order Management

Applies To

Oracle Order Management - Version 11.5.9 to 12.2.2 [Release 11.5 to 12.2]
Information in this document applies to any platform.Checked for relevance on 07-Dec-2012
Purpose

The purpose of this bulletin is to provide Oracle Customers with a good understanding and the to build a test case for basic PTO Configurations.
Details
Setup Steps

Login

  User : MFG / WELCOME

  Responsibility : Manufacturing and Distribution Manager

 Create Items

- Create PTO Model (PTO_MODEL_WIKI) using the PTO Model item template and assign it to

organization M1


Ensure that the BOM Item type is Model in the Bills of Material tab and the Pick Components

check box is checked in the Order Management tab

- Create PTO Option Class (PTO_OC_WIKI) using the PTO Option Class item template and assign it to organization M1.Ensure that the BOM Item type is Option Class in the Bills of Material tab and the Pick Components check box is checked in the Order Management tab

- Create 3 Items (PTO_ITEM, PTO_ITEM_1 & PTO_ITEM2) using the Finished Goods Item Template and assign it to M1 Org
 Add the Items to a Price List

- Navigate to Oracle Pricing Manager responsibility, query an Active Price list and add the items to the Price List and mention a value for price


- Navigation: Oracle Pricing Manager ->Price Lists

Create stock for the options for a sufficient test quantity

- Navigation: Inventory Responsibility ->Miscellaneous Transactions ->Miscellaneous Receipt

Create Bill Of Materials (BOMs)

Using a BOM Responsibility, create the BOM for both Model and Option Class in V1 Org and then copy it to M1. OR create it in M1 Org and make it a Common BOM for V1 org.

The BOM has to exist in both the OU (Validation Org.) and the Shipping Org.


- Create Option Class BOM (BOM Responsibility, navigate to BOM -> Bill Of Materials -> Define)


- Enter the Finished Goods items (PTO_ITEM and PTO_ITEM_1) and mark them as Optional

  - Create BOM for the PTO Model (BOM Responsibility, navigate to BOM -> Bill Of Materials -> Define)

- Enter the Option Class created above and also an Finished Good included item(PTO_OC_WIKI and PTO_ITEM2)

Indented BOM - final result is as depicted below

Testflow steps

 Creation of a Sales Order and Configuration of the PTO Model

- Enter a Sales Order Header

- Enter PTO_MODEL_WIKI as the Ordered Item and save the Order Line

- Click on Configurator Button and select the Options

Selection of the Mandatory Options satisfies the Configuration. Observe that the Unsatisfied Box is empty . Click on Finish to complete the Configuration

The cursor is back to the sales order entry form and the order can be booked as the configuration is complete.

Note that the Options are in a status of Awaiting Shipping

Pick Release the Sales Order

- Navigation: Order Management->Shipping->Transactions->Input the Order number->Select both the Options->Actions->Launch Pick Release

Ship Confirm the Sales Order

- Navigation: Order Management->Shipping->Transactions->Query the Sales Order->
- Navigate to Delivery Tab->Actions->Ship Confirm->Click OK

- Navigate to Sales Order ->Lines tab . The Options are in Shipped Status

The Lines are in Fulfill Deferred Status

 Run the Workflow Background Process

- Navigation: OM Responsibility->Requests->Run Requests

After the above program completes, the Sales Order Lines are in Closed Status

Accessing the Receivables Interface to observe the Sales Order

- Navigation: Accounts Receivables->Control->Auto Invoice->Interface Lines
   Do a F11->Enter the Order Number->Cntl F11

The Sales Order lines are now in the auto invoice interface table, ready to be processed by Receivables . Observe that only components marked as optional in BOM can be shown on invoice in addition to the PTO model.

-- This completes the steps for the PTO Configurations testflow


Reference :- Order Management Testcase Repository Library (Doc ID 743389.1)