Sample Script to Copy Responsibilities of one user account to another user account using API

fnd_user_pkg.addresp is an Oracle standard API to add responsibilities to a user account.

Below is the script to copy responsibilities of user “INTG_USER” to “IMPL_USER” 

DECLARE

  --
  resp_count NUMBER := 0;
  --
  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key
       FROM fnd_application fa      ,
      fnd_responsibility fr         ,
      fnd_user fu                   ,
      fnd_user_resp_groups_all furga,
      fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'INTG_USER'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
      -- AND furga.end_date IS NULL OR trunc(furga.end_date) > trunc(SYSDATE)
    AND furga.end_date IS NULL;
  --
  --
BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => 'IMPL_USER',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
      --
      resp_count := resp_count + 1;
      --
    EXCEPTION
    WHEN OTHERS THEN
      --
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
      --
    END;
  END LOOP;
  --
  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );
  --
  COMMIT;
END;

Thanks,

Amar

Oracle Apps + Get On Hand Quantities through API

DECLARE

v_api_return_status                     VARCHAR2 (1);
v_qty_oh                                      NUMBER;
v_qty_res_oh                               NUMBER;
v_qty_res                                     NUMBER;
v_qty_sug                                    NUMBER;
v_qty_att                                      NUMBER;
v_qty_atr                                      NUMBER;
v_msg_count                                NUMBER;
v_msg_data                                  VARCHAR2(1000);
v_inventory_item_id                    VARCHAR2(250) := '376676';
v_organization_id                        VARCHAR2(10)  := '93';

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line ('Transaction Mode');
DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );
DBMS_OUTPUT.put_line ('Organization        :'|| v_organization_id);

apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
(p_api_version_number  => 1.0,
 p_init_msg_lst               =>     apps.fnd_api.g_false,
 x_return_status              =>     v_api_return_status,
 x_msg_count                 =>     v_msg_count,
 x_msg_data                   =>      v_msg_data,
 p_organization_id         =>      v_organization_id,
 p_inventory_item_id    =>      v_inventory_item_id,
 p_tree_mode                 =>      apps.inv_quantity_tree_pub.g_transaction_mode,
 p_onhand_source         =>      3,
 p_is_revision_control  =>      FALSE,
 p_is_lot_control           =>     FALSE,
 p_is_serial_control      =>     FALSE,
 p_revision                    =>     NULL,
 p_lot_number              =>     NULL,
 p_subinventory_code  =>     NULL,
 p_locator_id                =>     NULL,
 x_qoh                          =>     v_qty_oh,
 x_rqoh                         =>     v_qty_res_oh,
 x_qr                             =>     v_qty_res,
 x_qs                             =>     v_qty_sug,
 x_att                             =>     v_qty_att,
 x_atr                            =>      v_qty_atr);

DBMS_OUTPUT.put_line ('on hand Quantity                :'|| v_qty_oh);
DBMS_OUTPUT.put_line ('Reservable quantity on hand     :'|| v_qty_res_oh);
DBMS_OUTPUT.put_line ('Quantity reserved               :'|| v_qty_res);
DBMS_OUTPUT.put_line ('Quantity suggested              :'|| v_qty_sug);
DBMS_OUTPUT.put_line ('Quantity Available To Transact  :'|| v_qty_att);
DBMS_OUTPUT.put_line ('Quantity Available To Reserve   :'|| v_qty_atr);

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);

END;

Thanks
Amar Alam

Delete Concurrent Program from the Back-End

If you create an Executable without creating a concurrent program, the system will allow to delete the Executable. But once you create the Concurrent Program for that Executable, the system never allows you to delete the program -- it only gives the option to disable the Concurrent Program.

At that point, your only option is to delete the Concurrent Program and its Executable from the back-end. Following is a simple straight-forward query that you can use for deleting a Concurrent Program. This query first checks if the concurrent program and its executable exist in the system. If found, it will delete the program; if not found, it will just display a message.

In this example, 'XX_TEST' is my Concurrent Program's Short Name and 'XX' is the Application Short Name. You will have to use appropriate program name and application short name according to your need.

 -------------------------------------------------------------------------------
-- delete concurrent program definition and executable from back-end
-------------------------------------------------------------------------------
-- syntax:
--     delete_program    (program_short_name, application_short_name)
--     delete_executable (program_short_name, application_short_name)
-------------------------------------------------------------------------------
DECLARE
  lv_prog_short_name    VARCHAR2(240);
  lv_appl_short_name    VARCHAR2(240);

BEGIN
   -- set the variables first
   lv_prog_short_name := 'XXAJ_IFFCO_COST_SUMARY';     -- concurrent program short name
   lv_appl_short_name := 'XXIFF';          -- application short name
 
   -- see if the program exists. if found, delete the program
   IF fnd_program.program_exists    (lv_prog_short_name, lv_appl_short_name) AND
      fnd_program.executable_exists (lv_prog_short_name, lv_appl_short_name)   
   THEN
    
      fnd_program.delete_program(lv_prog_short_name, lv_appl_short_name);
      fnd_program.delete_executable(lv_prog_short_name, lv_appl_short_name);
    
      COMMIT;
 
      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' deleted successfully');
 
   -- if the program does not exist in the system
   ELSE
      DBMS_OUTPUT.PUT_LINE (lv_prog_short_name || ' not found');
   END IF;
 
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);
 
END;

Ur's
AmarALam

RICE components

Mostly an oracle apps technical consultant will be working on RICE components.
R--Reports
I--Interfaces
C--Conversions
E--Extensions(Forms personalization)

Reports:
For suppose I'm running a business named 'ALAM' which is wide spread across the globe.Now,I want to know how my business is running across the globe.I will ask a tech guy(oracle apps technical consultant) to develop a report.In that report I ask him to simply print the details of profit or loss,of every branch of my business across the world.He(tech guy) will come up with the report to me then I will come to know whether my company is running fine or not.Thereby, I can take necessary decisions to run my business fine.
Clearly we have seen that report will be developed by technical person,who must know the report building.
We build a report using sql,pl/sql.So,if your are strong in sql,pl/sql then its not a big deal to learn the report building.Its enough to learn the report developer tool.

Conversion:
As I said earlier I'm running a 'ALAM' business(which is factious).Suppose I'm using excel sheets to store the data.Now I want to install oracle apps(E-Business Suite) in my company. So,What ever data that is present in my excel sheets must also be present in my oracle apps tables(A table is a collection of rows and columns).Now I ask a tech guy to write a code such that my excel sheet data will get into oracle apps base tables.Then that tech guy will use 'conversion' to get the data from the legacy system(in our case it is excel sheet) into oracle apps base tables.

Interfaces:
Interfaces are similar to conversions.Conversion is a one-time process where as Interface is on-going process every now and then.We run interfaces daily or periodically.
Interfaces are of two types
1)Inbound Interface
2)Outbound Interface
Inbound Interface:Transferring the data from the legacy system(in our case it is excel sheet) into the Oracle apps base tables.
Outbound Interface:Transferring the data from the Oracle apps base tables into the legacy system(It might be any of these SAP,People soft etc).

Extensions:
Extensions are nothing but personalizing the forms.In oracle e-business suite we have some where around 5000 to 6000 forms.In my 'ALAM' business i want to customise the po(purchase order) form of the oracle apps then i ask the tech guy to do that.He will use form builder tool to do that.


Ur's
AmarAlam

On-Hand quantity details as per Oracle Form

Below is the PL/SQL, which gives you the On-hand Quantity details as per oracle form.

Oracle form show details like On-Hand Quantity, Available to reserve, Quantity Reserved,Quantity Suggested, Available to Transact and Available to Reserve.

All These details can be fetched using API => inv_quantity_tree_pub.query_quantities

****************************************************************************
DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_organization_id       NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
BEGIN
   SELECT   inventory_item_id, mp.organization_id
     INTO   v_item_id, v_organization_id
     FROM   mtl_system_items_b msib, mtl_parameters mp
    WHERE       segment1 = :item_number
            AND msib.organization_id = mp.organization_id
            AND mp.organization_code = :organization_code;


   v_qoh := NULL;
   v_rqoh := NULL;
   v_atr := NULL;
   v_lot_control_code := FALSE;
   v_serial_control_code := FALSE;


   fnd_client_info.set_org_context (1);


   inv_quantity_tree_pub.query_quantities (
      p_api_version_number           => 1.0,
      p_init_msg_lst                        => 'F',
      x_return_status                      => x_return_status,
      x_msg_count                         => x_msg_count,
      x_msg_data                           => x_msg_data,
      p_organization_id                  => v_organization_id,
      p_inventory_item_id              => v_item_id,
      p_tree_mode                         => apps.inv_quantity_tree_pub.g_transaction_mode,
      p_is_revision_control             => FALSE,
      p_is_lot_control                     => v_lot_control_code,
      p_is_serial_control                 => v_serial_control_code,
      p_revision                              => NULL,                          -- p_revision,
      p_lot_number                        => NULL,                        -- p_lot_number,
      p_lot_expiration_date            => SYSDATE,
      p_subinventory_code            => NULL,                 -- p_subinventory_code,
      p_locator_id                         => NULL,                        -- p_locator_id,
      p_onhand_source                 => 3,
      x_qoh                                   => v_qoh,                    -- Quantity on-hand
      x_rqoh                                  => v_rqoh,         --reservable quantity on-hand
      x_qr                                     => v_qr,
      x_qs                                     => v_qs,
      x_att                                     => v_att,               -- available to transact
      x_atr                                    => v_atr                 -- available to reserve
   );


   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);


EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;


****************************************************************************


Ur's
AmarAlam

Code for attaching request set to request group

set serveroutput on
column date_column new_value today_var
select to_char(sysdate,'YYYYMMDDHHMI') date_column from dual
/
--
spool XXAJ_PO_APPROVAL_DETAILS_DATA
--
BEGIN
-- Add Request Set to request group.
BEGIN 
fnd_set.add_set_to_group (request_set => 'XXAJ_PO_APPROVAL_DETAILS',
set_application => 'Order Management',
request_group => 'ALL Reports',
group_application => 'ONT'
);
DBMS_OUTPUT.PUT_LINE ('"XXAJ_PO_APPROVAL_DETAILS" attached to request group uccessfully ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error in attaching "XXAJ_PO_APPROVAL_DETAILS" to equest group ' || SQLERRM);
END;
--
COMMIT;


Ur's
AmarAlam

Creating Purchase Order Requisitions

Below is the example to create a requisition.

We just need to insert data into Interface table and then we need to call standard oracle import program.

In below example, i have created a Internal Requisition.

************************************************************************************************
DECLARE
   l_request_id   NUMBER;
BEGIN
   INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (interface_source_code,
                                              source_type_code,
                                              requisition_type,
                                              destination_type_code,
                                              item_id,
                                              item_description,
                                              quantity,
                                              authorization_status,
                                              preparer_id,
                                              autosource_flag,
                                              uom_code,
                                              destination_organization_id,
                                              deliver_to_location_id,
                                              deliver_to_requestor_id,
                                              need_by_date,
                                              gl_date,
                                              charge_account_id,
                                              org_id,
                                              suggested_vendor_id,
                                              suggested_vendor_site_id,
                                              unit_price,
                                              creation_date,
                                              created_by,
                                              last_update_date,
                                              last_updated_by,
                                              batch_id
                                              )
     VALUES   ('INV',
               'INVENTORY',
               'INTERNAL',
               'INVENTORY',
               1831768,
               'leadsets',
               50,
               'APPROVED',
               48880,
               'P',
               'EA',
               2609,
               75018,
               48880,
               SYSDATE,
               SYSDATE,
               237125,
               2592,
               1058,
               607,
               150,
               SYSDATE,
               59944,
               SYSDATE,
               59944,
               454
               );

   COMMIT;

   l_request_id :=
      fnd_request.submit_request (application   => 'PO',
                                  program       => 'REQIMPORT',
                                  argument1     => 'INV',
                                  argument2     => '454',
                                  argument3     => 'ALL',
                                  argument4     => '',
                                  argument5     => '',
                                  argument6     => 'N');
   COMMIT;
   DBMS_OUTPUT.put_line ('request_id - ' || l_request_id);
END;
/


Ur's
AmarAlam

API to Update a Customer Site Use TCA R12 (hz_cust_account_site_v2pub.update_cust_site_use)

CREATE OR REPLACE procedure APPS.xxaj_site_use_status
is

                    x_return_status     varchar2(10);          
                    x_msg_count         number(10);              
                    x_msg_data          varchar2(1200);
                    p_object_version_number number(10):=4;
                    P_CUST_SITE_USE_REC hz_cust_account_site_v2pub.CUST_SITE_USE_REC_TYPE;

begin
       --Apps Initialization
       
         FND_GLOBAL.APPS_INITIALIZE (
                                            USER_ID => 1318,
                                            RESP_ID => 50583,
                                            RESP_APPL_ID => 401  
                                            );
                 
         P_CUST_SITE_USE_REC.site_use_id:=4126;
         P_CUST_SITE_USE_REC.status:= 'A';
         P_CUST_SITE_USE_REC.cust_acct_site_id :=3995;
         P_CUST_SITE_USE_REC.SITE_USE_CODE := 'BILL_TO';
         P_CUST_SITE_USE_REC.CREATED_BY_MODULE := 'TCA_V2_API';                    
                                           
         hz_cust_account_site_v2pub.update_cust_site_use
         (
                   p_init_msg_list             =>    'T',        
                   P_CUST_SITE_USE_REC        => P_CUST_SITE_USE_REC,
                   p_object_version_number     => p_object_version_number,  
                   x_return_status             => x_return_status,    
                   x_msg_count                 => x_msg_count ,  
                   x_msg_data                  => x_msg_data  
         );
            IF x_return_status = 'S' THEN
                dbms_output.put_line(' Now site use is active' );  
            ELSE
                IF NVL (x_msg_count, 0) > 1 THEN
                    FOR i IN 1 .. x_msg_count LOOP
             
                        dbms_output.put_line(' Error Status ' ||x_return_status);
                        dbms_output.put_line(' Error message ' ||x_msg_data);
                    END LOOP;
                ELSE
                    dbms_output.put_line(' Error message ' ||x_msg_data);
                END IF;
            END IF;
         commit;                              
exception when others then
    dbms_output.put_line(' Error Here'||sqlcode||sqlerrm);
end;
/

SQL> EXEC xxaj_site_use_status;


Ur's
AmarAlam

Wait for concurrent request using fnd_concurrent.wait_for_request

Most of the times while submitting concurrent request one has to wait for its completion to perform sequence of steps. This can be achieved through fnd_concurrent.wait_for_request in oracle apps. It returns the status of the previously submitted concurrent program upon interval set.

This is a conjunction to the SUBMIT_REQUEST. Follow ARTICLE on submitting concurrent program and use below call upon completed. There are some parameter that has to be declared.

      lb_complete      BOOLEAN;
      lc_phase           VARCHAR2 (100);
      lc_status           VARCHAR2 (100);
      lc_dev_phase   VARCHAR2 (100);
      lc_dev_status   VARCHAR2 (100);
      lc_message      VARCHAR2 (100);

ln_request_id is return variable for concurrent_request_id from above article. If ln_request_id > 0 (means request submitted successfully) then wait_for_request.

   Arguments (input)
     request_id    - Request ID to wait on
     interval         - time b/w checks. Number of seconds to sleep (default 60 seconds)
     max_wait      - Max amount of time to wait (in seconds) for request's completion
  Arguments (output)
                 User version of      phase and status
                 Developer version of phase and status
                 Completion text if any
     phase            - Request phase ( from meaning in fnd_lookups )
     status            - Request status( for display purposes          )
     dev_phase    - Request phase as a constant string so that it can be used for comparisons
     dev_status    - Request status as a constatnt string
     message       - Completion message if request has completed


      IF ln_request_id > 0
      THEN
         lb_complete :=
            fnd_concurrent.wait_for_request (request_id      => ln_request_id
                                                             ,interval            => 2
                                                             ,max_wait        => 60
                                                             -- out arguments
                                                             ,phase              => lc_phase
                                                             ,status              => lc_status
                                                             ,dev_phase      => lc_dev_phase
                                                             ,dev_status      => lc_dev_status
                                                             ,message         => lc_message
                                            );
         COMMIT;

         IF UPPER (lc_dev_phase) IN ('COMPLETE')
         THEN
            dbms_output.put_line('Concurrent request completed successfully');
         END IF;
      END IF;

Ur's
AmarAlam

Item Import Program Using API in 11i

We can use the below code to Submit the Item Import Program using API.

Code is tested in 11i.

 CREATE OR REPLACE PROCEDURE Item_import
 IS

 v_phase               VARCHAR2(240);
 v_status              VARCHAR2(240);
 v_request_phase   VARCHAR2(240);
 v_request_status  VARCHAR2(240);
 v_finished            BOOLEAN;
 v_message          VARCHAR2(240);
 l_request_id         NUMBER;

 BEGIN
 
   --We need to apps initialize before calling the import program
   --Apps Initialize is explained in another section. Check below link to know in detail
          Apps Initialize;

      l_request_id := Fnd_Request.submit_request (
                                application   => 'INV',
                               program        => 'INCOIN',
                              description     => NULL,
                              start_time      => SYSDATE,
                              sub_request   => FALSE,
                              argument1     => 1,     --Mode to run this request(Insert new cost information only)
                              argument2     => 1,     --Group ID option (All)
                              argument3     => 1,     -- Group ID Dummy
                             argument4     => 1,
                             argument5     => 1,
                             argument6     => ,     -- Group ID passed in Interface table
                             argument7     => <1 2="" or="">          -- 1 for Create and 2 for Update
                       );                  
      COMMIT;
   
      IF  ( l_request_id = 0 ) THEN
         dbms_output.put_line( 'Submission of Import failed ');
      END IF;
      -- Wait for request to run the import Program to Finish
      v_finished := fnd_concurrent.wait_for_request (request_id      => l_request_id,
                                                     interval        => 60,
                                                     max_wait      => 0,
                                                     phase          => v_phase,
                                                     status          => v_status,
                                                     dev_phase    => v_request_phase,
                                                     dev_status   => v_request_status,
                                                     message      => v_message);

      dbms_output.put_line('Request Phase : '|| v_request_phase );
      dbms_output.put_line('Request Status : ' || v_request_status );

      --Testing end status
      IF ( UPPER(v_request_status) = 'NORMAL') THEN
          dbms_output.put_line( 'Submission of Item Import is Success ');
      ELSE
          dbms_output.put_line( 'Submission of Item Import failed ');
      END IF;
   
 EXCEPTION
    WHEN OTHERS THEN
         dbms_output.put_line( 'Submission of Import failed ');
 END ;
 /

Ur's
AmarAlam

Supplier Contact Creation Using API

CREATE OR REPLACE PROCEDURE xxaj_supplier_contact_creation
l_vendor_contact_rec   ap_vendor_pub_pkg.r_vendor_contact_rec_type;
l_return_status     VARCHAR2(10);
l_msg_count     NUMBER;
l_msg_data  VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id  NUMBER;
l_vendor_contact_id NUMBER;
l_per_party_id      NUMBER;
l_rel_party_id      NUMBER;
l_rel_id            NUMBER;
l_org_contact_id    NUMBER;
l_party_site_id     NUMBER;

BEGIN
fnd_global.apps_initialize(user_id => fnd_global.USER_ID,resp_id => fnd_global.RESP_ID,resp_appl_id => fnd_global.RESP_APPL_ID);
fnd_global.apps_initialize(0,20707,200);
--Query to get Vendor Id
    SELECT vendor_id
    INTO l_vendor_contact_rec.vendor_id
    FROM pos_po_vendors_v
    WHERE vendor_name = 'SIVA L ERP';  --Vendor Name
    dbms_output.put_line('Vendor id is :'||l_vendor_contact_rec.vendor_id);
--Query to get Party Site Id
    SELECT party_site_id
    INTO   l_vendor_contact_rec.org_party_site_id
    FROM ap_supplier_sites_all
    WHERE vendor_site_code = 'ERP_SITE'
    AND vendor_id = l_vendor_contact_rec.vendor_id ;
    dbms_output.put_line('Party site id is :'||l_vendor_contact_rec.org_party_site_id);
--l_vendor_contact_rec.org_party_site_id := 175402;     --Party Site ID
l_vendor_contact_rec.org_id := 204;                --Org Id
l_vendor_contact_rec.PERSON_FIRST_NAME:= 'Amar';  --First Name
l_vendor_contact_rec.PERSON_LAST_NAME := 'Alam';   --Last Name

l_vendor_contact_rec.phone := 9966410696;                 --Phone Number
l_vendor_contact_rec.email_address := 'test@gmail.com' ;         --Email Address

pos_vendor_pub_pkg.create_vendor_contact
( p_vendor_contact_rec  => l_vendor_contact_rec,
  x_return_status       => l_return_status,
  x_msg_count           => l_msg_count,
  x_msg_data            => l_msg_data,
  x_vendor_contact_id   => l_vendor_contact_id,
  x_per_party_id        => l_per_party_id,
  x_rel_party_id        => l_rel_party_id,
  x_rel_id              => l_rel_id,
  x_org_contact_id      => l_org_contact_id,
  x_party_site_id       => l_party_site_id
 );
 IF x_msg_count > 1 THEN
    FOR i IN 1 .. x_msg_count LOOP
      DBMS_OUTPUT.put_line(SUBSTR (fnd_msg_pub.get(p_encoded => 'F'),1,255));
    END LOOP;
ELSE
 DBMS_OUTPUT.put_line('API ERROR :'||x_msg_data);
END IF;

 COMMIT;
  dbms_output.put_line('return_status: '||l_return_status);
  dbms_output.put_line('msg_data: '||l_msg_data);
  dbms_output.put_line('msg_data_count: '||l_msg_count);
  dbms_output.put_line('vendor Contact Id: '||l_vendor_contact_id);
  dbms_output.put_line('per_party_id: '||l_per_party_id);
  dbms_output.put_line('rel_party_id: '||l_rel_party_id);
  dbms_output.put_line('rel_id: '||l_rel_id);
  dbms_output.put_line('org_contact_id: '||l_org_contact_id);
  dbms_output.put_line('party_site_id: '||l_party_site_id);
End xxaj_supplier_contact_creation;


Exec xxaj_supplier_contact_creation;


Ur's
AmarAlam

Supplier Site Creation Using API

CREATE OR REPLACE PROCEDURE xxaj_supplier_site_creation
p_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
x_return_status    VARCHAR2(8);
x_msg_count        NUMBER;
x_msg_data         VARCHAR2(4000);
x_vendor_site_id   NUMBER;
x_party_site_id    NUMBER;
x_location_id      NUMBER;
BEGIN
p_vendor_site_rec.vendor_site_code := 'ERP_SITE';
p_vendor_site_rec.vendor_id        := 39173;
p_vendor_site_rec.address_line1    := 'ERP Bangalore';
p_vendor_site_rec.ADDRESS_LINE2    := '#C-21 2nd Cross';
p_vendor_site_rec.country          := 'US';
p_vendor_site_rec.county           := 'New York';
p_vendor_site_rec.city             := 'Brooklyn';
p_vendor_site_rec.state            := 'FL';
p_vendor_site_rec.zip              := 10292;
p_vendor_site_rec.org_id           := 204;

POS_VENDOR_PUB_PKG.Create_Vendor_Site
( p_vendor_site_rec,
  x_return_status,
  x_msg_count,
  x_msg_data,
  x_vendor_site_id,
  x_party_site_id,
  x_location_id
);
DBMS_OUTput.put_line('The Status is :'||x_return_status);
DBMS_OUTput.put_line('The x_msg_count is :'||x_msg_count);
DBMS_OUTput.put_line('The x_msg_data is :'||x_msg_data);
DBMS_OUTput.put_line('The x_vendor_site_id is :'||x_vendor_site_id);
DBMS_OUTput.put_line('The x_party_site_id is :'||x_party_site_id);
DBMS_OUTput.put_line('The x_location_id is :'||x_location_id);
commit;
END xxaj_supplier_site_creation;

Exec xxaj_supplier_site_creation;

Ur's
AmarAlam

Supplier Creation Using API

CREATE OR REPLACE PROCEDURE xxaj_supplier_creation
IS
v_vendor_rec     AP_VENDOR_PUB_PKG.r_vendor_rec_type;
x_return_status  VARCHAR2(10);
x_msg_count      NUMBER;
x_msg_data       VARCHAR2(4000);
x_vendor_id      NUMBER;
x_party_id       NUMBER;
v_error_message  VARCHAR2(4000);
v_valdation_flag VARCHAR2(5);

BEGIN

v_vendor_rec.vendor_name :='SIVA L ERP' ;
DBMS_OUTput.put_line(' Calling POS_VENDOR_PUB_PKG');
POS_VENDOR_PUB_PKG.Create_Vendor
( v_vendor_rec,
  x_return_status,
  x_msg_count,
  x_msg_data,
  x_vendor_id,
  x_party_id
);
IF x_return_status = 'S' then
  DBMS_OUTput.put_line('Success');
ELSE
 v_error_message :=x_msg_data;
 v_valdation_flag :='E';
END IF;
DBMS_OUTput.put_line('The Status is :'||x_return_status);
DBMS_OUTput.put_line('The x_msg_count is :'||x_msg_count);
DBMS_OUTput.put_line('The x_msg_data is :'||x_msg_data);
DBMS_OUTput.put_line('The x_vendor_id is :'||x_vendor_id);
DBMS_OUTput.put_line('The x_party_id is :'||x_party_id);
COMMIT;
END xxaj_supplier_creation;


Exec xxaj_supplier_creation;

Ur's
AmarAlam