Home » Other » Test » testcode plsql table
testcode plsql table [message #513887] Wed, 29 June 2011 21:44
ora1980
Messages: 251
Registered: May 2008
Senior Member
TYPE xx_cust_tbl_typ IS TABLE OF xx_cust_rec_typ INDEX BY BINARY_INTEGER;




 PROCEDURE xx_pop_plsql
   IS
   
   xx_cust_tbl  xx_cust_tbl_typ;
   
   i number;
   
   CURSOR l_cust_cur 
   IS
   SELECT *
     FROM xx_cust;
   
   BEGIN
   i:=1;
   
   DBMS_APPLICATION_INFO.set_client_info(204);
   
   FOR l_cust_rec in l_cust_cur
   LOOP
   
      xx_cust_tbl(i).fname := l_cust_rec.fname;
      xx_cust_tbl(i).lname := l_cust_rec.lname;
      xx_cust_tbl(i).country := l_cust_rec.country;
      xx_cust_tbl(i).address1 := l_cust_rec.address1;
      xx_cust_tbl(i).city := l_cust_rec.city;
      xx_cust_tbl(i).postal_code := l_cust_rec.postal_code;
      xx_cust_tbl(i).state := l_cust_rec.state;
      
      
      i:= i+1;      
   
   
   END LOOP;
   
   xxgautam_create_customer(xx_cust_tbl);




 PROCEDURE xxgautam_create_customer(io_cust_details IN OUT NOCOPY xx_cust_tbl_typ)
   IS
   
     p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
      p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
     p_customer_profile_rec
      HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;

         p_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
         x_party_site_id NUMBER;
         x_party_site_number VARCHAR2(2000);

      x_cust_account_id NUMBER;
      x_account_number VARCHAR2(2000);
      x_party_id NUMBER;
     x_party_number VARCHAR2(2000);
       x_profile_id NUMBER;
         p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
         x_location_id NUMBER;
      x_return_status VARCHAR2(2000);
      x_msg_count NUMBER;
     x_msg_data VARCHAR2(2000);
     i number;

BEGIN
  i:= 1;
 FOR i in io_cust_details.first .. io_cust_details.last
 LOOP
    p_cust_account_rec.account_name := io_cust_details(i).fname; 
    p_cust_account_rec.created_by_module := 'NAG_EXAMPLE';
 
    p_organization_rec.organization_name := io_cust_details(i).lname;
    p_organization_rec.created_by_module := 'NAG_EXAMPLE';

    hz_cust_account_v2pub.create_cust_account(
                                               'T',
                                                p_cust_account_rec,
                                                p_organization_rec,
                                                 p_customer_profile_rec,
                                                 'F',
                                                 x_cust_account_id,
                                                x_account_number,
                                                 x_party_id,
                                                x_party_number,
                                                x_profile_id,
                                                x_return_status,
                                                x_msg_count,
                                                x_msg_data);

     dbms_output.put_line('***************************');
     dbms_output.put_line('Output information ....');
     dbms_output.put_line('***************************');
     dbms_output.put_line('x_cust_account_id: '||x_cust_account_id);
     dbms_output.put_line('x_account_number: '||x_account_number);
     dbms_output.put_line('x_party_id: '||x_party_id);
     dbms_output.put_line('x_party_number: '||x_party_number);
     dbms_output.put_line('x_profile_id: '||x_profile_id);
     dbms_output.put_line('x_return_status: '||x_return_status);
     dbms_output.put_line('x_msg_count: '||x_msg_count);
     dbms_output.put_line('x_msg_data: '||x_msg_data);
     dbms_output.put_line('***************************');

     IF x_msg_count >1 THEN
      FOR I IN 1..x_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;


 -- for location creation
   dbms_output.put_line(io_cust_details(i).country);
   dbms_output.put_line(io_cust_details(i).city);

      p_location_rec.country := io_cust_details(i).country;
 p_location_rec.address1 := io_cust_details(i).address1;
 p_location_rec.city := io_cust_details(i).city;
 p_location_rec.postal_code := io_cust_details(i).postal_code;
 p_location_rec.state := io_cust_details(i).state;
 p_location_rec.created_by_module := 'TCAPI_EXAMPLE';
 hz_location_v2pub.create_location(
 'T',
 p_location_rec,
 io_cust_details(i).location_id,--x_location_id,
 x_return_status,
 x_msg_count,
Previous Topic: refcr
Next Topic: Know RFQ no for Corresponding Requistion
Goto Forum:
  


Current Time: Sun Jul 12 18:09:41 CDT 2020