package for customer conversion through API approach

create or replace
PACKAGE BODY XX_NARK_CUST_PKG IS
PROCEDURE MAP(RETCODE OUT VARCHAR2,ERRBUF OUT VARCHAR2)IS
BEGIN
update xx_narsimlu_cust_stg set errored_flag='N',error_message=null;
END MAP;
PROCEDURE VALID_LOAD(RETCODE OUT VARCHAR2,ERRBUF OUT VARCHAR2)IS
-----***validation starts here for customer***----------------
 --v_party_id number;
 cursor party_cur is select * from xx_narsimlu_cust_stg;
----------***party declaration starts here***----------------------------
p_organization_rec hz_party_v2pub.organization_rec_type;
party_rec         hz_party_v2pub.party_rec_type;
  x_return_status VARCHAR2 (2000);
  x_msg_count     NUMBER;
  x_msg_data      VARCHAR2 (2000);
  x_party_id      NUMBER;
  x_party_number  VARCHAR2 (2000);
  x_profile_id    NUMBER;
----------****party account declaration starts here***---------------
 p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
-- p_organization_rec hz_party_v2pub.organization_rec_type;
 x_cust_account_id NUMBER;
 x_account_number  VARCHAR2 (2000);
---------*****validations for locations********-------------------------
p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
x_location_id number;
--------------*****validations for standard terms*****--------------
v_term_id number;
-------------*********validations for party sites *********---------------------
p_party_site_rec hz_party_site_v2pub.party_site_rec_type;
x_party_site_id     NUMBER;
x_party_site_number VARCHAR2 (2000);

 -------------********party account profile declaration starts here*****--------

p_customer_profile_rec_type HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
x_cust_account_profile_id   NUMBER;

-------------*******party cust acount site declaration starts here**********-----------
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
x_cust_acct_site_id NUMBER;
-------------------******party cust site uses declaration starts here *****----------
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
x_site_use_id   NUMBER;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
     
     
--------------*******party phone contacts declarations stats here********--------------
p_contact_point_rec            hz_contact_point_v2pub.contact_point_rec_type;
p_phone_rec                 hz_contact_point_v2pub.phone_rec_type;
      x_contact_point_id            NUMBER;
 
-------------********party email contact declarations starts here ****-------------
--p_contact_point_rec             hz_contact_point_v2pub.contact_point_rec_type;
    p_email_rec                   hz_contact_point_v2pub.email_rec_type;
   -- x_contact_point_id            NUMBER;
 
 BEGIN
 for p_party_rec in party_cur loop
 begin
 select party_id into x_party_id from hz_parties where upper(party_name)=upper(p_party_rec.party_name);
if x_party_id is not null then
 update xx_narsimlu_cust_stg set errored_flag='E',error_message='already existing customer'
 where trans_id=p_party_rec.trans_id;
dbms_output.put_line('this party already existed'||x_party_id);
end if;
 exception
 when no_data_found then
---------*****party  creation api getting call here****------------------
  p_organization_rec.organization_name :=p_party_rec.party_name;
  p_organization_rec.created_by_module :='TCA_FORM_WRAPPER';
  hz_party_v2pub.create_organization ('T',
  p_organization_rec,
  x_return_status,
  x_msg_count,
  x_msg_data,
  x_party_id,
  x_party_number,
  x_profile_id );
  IF  x_return_status ='S' THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer  is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust party Id           = '||TO_CHAR(x_party_id));
    dbms_output.put_line('Cust party number       = '||TO_CHAR(x_party_number));
    dbms_output.put_line('Cust profile Id         = '||x_profile_id);
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer  got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
 when too_many_rows then
 update xx_narsimlu_cust_stg set errored_flag='E',error_message='duplicate customer existed'
 where trans_id=p_party_rec.trans_id;
 when others then
 update xx_narsimlu_cust_stg set errored_flag='X',error_message='some other errors with customer'
 where trans_id=p_party_rec.trans_id;
 end;
/*  this is validation for freight terms
for fri_rec in fri_cur loop
begin
select freight_term into v_freight_term from hz_cust_accounts_all where upper(freight_term)=(fri_rec.freight_terms);
if v_freight_term is not null then
update xx_narsimlu_cust_stg set freight_terms=v_freight_term where trans_id=fri_rec.trans_id;
end if;
exception
when no_data_found then
 update xx_narsimlu_cust_stg set freight_terms=v_freight_term,error_message='freight term is not existed'
 where trans_id=fri_rec.trans_id;
 when others then
 update xx_narsimlu_cust_stg set freight_terms=v_freight_term,error_message='freight term is not existed'
 where trans_id=fri_rec.trans_id;
end;
end loop;*/
-------------*****standard terms validations starts here****------------------
begin
select term_id into v_term_id from ra_terms where upper(name)=(p_party_rec.payment_terms);
update xx_narsimlu_cust_stg set error_message='term is present' where trans_id=p_party_rec.trans_id;
commit;
exception
when no_data_found then
 update xx_narsimlu_cust_stg set payment_terms=null,error_message='standard term is not existed'
 where trans_id=p_party_rec.trans_id;
 when too_many_rows then
 update xx_narsimlu_cust_stg set payment_terms=null,error_message='standard existed with duplicates'
 where trans_id=p_party_rec.trans_id;
 when others then
 update xx_narsimlu_cust_stg set payment_terms=null,error_message='standard existed'
 where trans_id=p_party_rec.trans_id;
end;
------****customer profile api getting call here *****---------------
begin
p_customer_profile_rec.party_id   :=x_party_id;
p_customer_profile_rec_type.standard_terms:=v_term_id;
p_customer_profile_rec.created_by_module := 'HZ_CPUI';
HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE
                  ('T',
    p_customer_profile_rec ,
    'T',
    x_cust_account_profile_id ,
    x_return_status           ,
    x_msg_count               ,
    x_msg_data               
);
IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer account Profile is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust account profile id           = '||TO_CHAR(x_cust_account_profile_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer account Profile got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
exception
when others then
 update xx_narsimlu_cust_stg set payment_terms=null,error_message='standard existed'
 where trans_id=p_party_rec.trans_id;
end;

-----------------****party account api getting call here *****-------------------
begin
p_cust_account_rec.account_name      := 'XX_INFO';
  p_cust_account_rec.created_by_module := 'HZ_CPUI';
  p_organization_rec.party_rec.party_id := x_party_id;
  p_customer_profile_rec.cust_account_profile_id:=x_cust_account_profile_id;
  hz_cust_account_v2pub.create_cust_account ('T',
    p_cust_account_rec       ,
    p_organization_rec       ,
    p_customer_profile_rec   ,
    'T',
    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              );
   IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer Profile is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust account id           = '||TO_CHAR(x_cust_account_id));
    dbms_output.put_line('Cust account number           = '||TO_CHAR(x_account_number));
    dbms_output.put_line('Cust party id           = '||TO_CHAR(x_party_id));
    dbms_output.put_line('Cust party number       = '||TO_CHAR(x_party_number));
    dbms_output.put_line('Cust profile Id         = '||x_profile_id);
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer Profile got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
exception
when others then
 update xx_narsimlu_cust_stg set payment_terms=null,error_message='standard existed'
 where trans_id=p_party_rec.trans_id;
end;
-------------******Validatios for locations *****------------------------------
begin
select location_id into x_location_id from hz_locations
where
nvl(upper(address1),'AAA')=nvl(upper(p_party_rec.address_1),'AAA')
and nvl(upper(address2),'AAA')=nvl(upper(p_party_rec.address_2),'AAA')
and nvl(upper(address3),'AAA')=nvl(upper(p_party_rec.address_3),'AAA')
and nvl(upper(city),'AAA')=nvl(upper(p_party_rec.city),'AAA')
and nvl(upper(country),'AAA')=nvl(upper(p_party_rec.country),'AAA')
and nvl(upper(state),'AAA')=nvl(upper(p_party_rec.state),'AAA')
and nvl(upper(postal_code),'AAA')=nvl(upper(p_party_rec.zip_code),'AAA');
update xx_narsimlu_cust_stg set error_message='loc is present' where trans_id=p_party_rec.trans_id;
commit;
exception
when no_data_found then
----------api for locations getting call here*****-------------------
  p_location_rec.country           := p_party_rec.country;
  p_location_rec.address1          := p_party_rec.address_1;
  p_location_rec.address2          := p_party_rec.address_2;
  p_location_rec.city              := p_party_rec.city;
  p_location_rec.postal_code       := p_party_rec.zip_code;
  p_location_rec.state             := p_party_rec.state;
  p_location_rec.created_by_module := 'HZ_CPUI';
  hz_location_v2pub.create_location( 'T',
  p_location_rec,
  x_location_id,
  x_return_status,
  x_msg_count,
  x_msg_data); IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer location is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust location Id           = '||TO_CHAR(x_location_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer location got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
 update xx_narsimlu_cust_stg set error_message='loc term is not existed'
 where trans_id=p_party_rec.trans_id;
 when too_many_rows then
 update xx_narsimlu_cust_stg set error_message='loc existed with duplicates'
 where trans_id=p_party_rec.trans_id;
select max(location_id) into x_location_id from hz_locations where
nvl(upper(address1),'AAA')=nvl(upper(p_party_rec.address_1),'AAA')
and nvl(upper(address2),'AAA')=nvl(upper(p_party_rec.address_2),'AAA')
and nvl(upper(address3),'AAA')=nvl(upper(p_party_rec.address_3),'AAA')
and nvl(upper(city),'AAA')=nvl(upper(p_party_rec.city),'AAA')
and nvl(upper(country),'AAA')=nvl(upper(p_party_rec.country),'AAA')
and nvl(upper(state),'AAA')=nvl(upper(p_party_rec.state),'AAA')
and nvl(upper(postal_code),'AAA')=nvl(upper(p_party_rec.zip_code),'AAA');
 when others then
 update xx_narsimlu_cust_stg set error_message='standard existed'
 where trans_id=p_party_rec.trans_id;
end;
begin
---------party sites api geting call here -------------------
  p_party_site_rec.party_id                 := x_party_id;
  p_party_site_rec.location_id              := x_location_id;
  p_party_site_rec.identifying_address_flag := 'Y';
  p_party_site_rec.created_by_module        :='HZ_CPUI';
  hz_party_site_v2pub.create_party_site ('T',
  p_party_site_rec,
  x_party_site_id,
  x_party_site_number,
  x_return_status,
  x_msg_count,
  x_msg_data );
  IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of party site  is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust party site Id           = '||TO_CHAR(x_party_site_id));
    dbms_output.put_line('Cust party site number           = '||TO_CHAR(x_party_site_number));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of party site got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
exception
when no_data_found then
update xx_narsimlu_cust_stg set error_message='party site id  not existed'
 where trans_id=p_party_rec.trans_id;
when too_many_rows then
update xx_narsimlu_cust_stg set error_message='party site id  existed duplicate'
 where trans_id=p_party_rec.trans_id;
when others then
 update xx_narsimlu_cust_stg set error_message='party site id with some other errors '
 where trans_id=p_party_rec.trans_id;
end;
/*
begin
p_customer_profile_rec_type.cust_account_id   :=x_party_id;
p_customer_profile_rec_type.standard_terms:=v_term_id;
p_customer_profile_rec_type.created_by_module := 'HZ_CPUI';
HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE
                  ( 'T',
    p_customer_profile_rec_type,
    'T',
    x_cust_account_profile_id,
    x_return_status,
    x_msg_count,
    x_msg_data                         
);
IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer account Profile is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust account profile id           = '||TO_CHAR(x_cust_account_profile_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer account Profile got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
exception
when others then
DBMS_OUTPUT.PUT_LINE('loading error');
 update xx_narsimlu_cust_stg set error_message='profile account error with other reasons'
 where trans_id=p_party_rec.trans_id;
end;
*/
-----------party cust site api getting call here********---------------
begin
p_cust_acct_site_rec.cust_account_id   :=x_cust_account_id;
  p_cust_acct_site_rec.party_site_id     :=x_party_site_id;
  --p_cust_acct_site_rec.LANGUAGE          := 'US';
  p_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
  hz_cust_account_site_v2pub.create_cust_acct_site ('T',
  p_cust_acct_site_rec,
  x_cust_acct_site_id,
  x_return_status,
  x_msg_count,
  x_msg_data );
  IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer account site is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust acct site id          = '||TO_CHAR( x_cust_acct_site_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer account site got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
exception
when others then
update xx_narsimlu_cust_stg set error_message='party cust site error with other reasons'
 where trans_id=p_party_rec.trans_id;
end;
-----------***party cust site use api getting call here ****-----------
begin
 p_cust_site_use_rec.cust_acct_site_id :=x_cust_acct_site_id;
  --p_cust_site_use_rec.site_use_code     := 'SHIP_TO';
  p_cust_site_use_rec.site_use_code     := 'BILL_TO';
  --p_cust_site_use_rec.site_use_code     := 'SOLD_TO';
  p_cust_site_use_rec.LOCATION          := 'TCA';
  p_cust_site_use_rec.created_by_module := 'HZ_CPUI';
  hz_cust_account_site_v2pub.create_cust_site_use (
    'T',
    p_cust_site_use_rec ,
    p_customer_profile_rec  ,
    'T',
    'T',
    x_site_use_id   ,
    x_return_status ,
    x_msg_count     ,
    x_msg_data      );
   IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer account site use  is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust party site use Id           = '||TO_CHAR(x_site_use_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer account site use got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
exception
when others then
update xx_narsimlu_cust_stg set error_message='party cust site error with other reasons'
 where trans_id=p_party_rec.trans_id;
end;

---------------party phone contact validation and load starts here***---------------
begin
  p_contact_point_rec.contact_point_type    := 'PHONE';
  p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
  p_contact_point_rec.owner_table_id        :=x_party_id;
  p_contact_point_rec.primary_flag          := 'Y';
  p_contact_point_rec.contact_point_purpose := 'BUSINESS';
 p_phone_rec.phone_number                  := p_party_rec.phone_no;
  p_phone_rec.phone_line_type               := 'GEN';
  p_contact_point_rec.created_by_module     := 'HZ_CPUI';
hz_contact_point_v2pub.create_phone_contact_point (
   'T',
    p_contact_point_rec        ,
    p_phone_rec                ,
    x_contact_point_id         ,
    x_return_status            ,
    x_msg_count                ,
    x_msg_data                
  );
IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer contact is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust contact point id         = '||TO_CHAR(x_contact_point_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer contact got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
exception
when others then
update xx_narsimlu_cust_stg set error_message='party contacts error with other reasons'
 where trans_id=p_party_rec.trans_id;
end;
-------******party email contacts valiadtions and load starts here *****----------------
begin
p_contact_point_rec.contact_point_type    := 'EMAIL';
  p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
  p_contact_point_rec.owner_table_id        := x_party_id;
  p_contact_point_rec.primary_flag          := 'Y';
  p_contact_point_rec.contact_point_purpose := 'BUSINESS';
   p_email_rec.email_address                 :=p_party_rec.email;
  p_email_rec.email_format                  :='MAILHTML';
  p_contact_point_rec.created_by_module     := 'HZ_CPUI';
hz_contact_point_v2pub.create_email_contact_point (
   'T',
    p_contact_point_rec        ,
    p_email_rec                ,
    x_contact_point_id         ,
    x_return_status            ,
    x_msg_count                ,
    x_msg_data                
  );
  IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer contact is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust contact point id         = '||TO_CHAR(x_contact_point_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer contact got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
exception
when others then
update xx_narsimlu_cust_stg set error_message='party contact error with other reasons'
 where trans_id=p_party_rec.trans_id;
end;
 end loop;
 commit;
-----***validation ends here for customer***----------------
END VALID_LOAD;
PROCEDURE ERROR_REPORT(RETCODE OUT VARCHAR2,ERRBUF OUT VARCHAR2)IS
BEGIN
NULL;
END ERROR_REPORT;
PROCEDURE CALL_ALL_PROD(RETCODE OUT VARCHAR2,ERRBUF OUT VARCHAR2)IS
BEGIN
NULL;
END CALL_ALL_PROD;
 END XX_NARK_CUST_PKG;








 

anonymous block for customer web contact creation

  --------------------**************anonymous block for customer web contact creation *********------------------- 
declare
p_contact_point_rec         hz_contact_point_v2pub.contact_point_rec_type;
    p_web_rec                  hz_contact_point_v2pub.web_rec_type;
    x_contact_point_id        NUMBER;
    x_return_status              VARCHAR2(200);
    x_msg_count                 NUMBER;
    x_msg_data                  VARCHAR2(200);
begin
p_contact_point_rec.contact_point_type    := 'WEB';
  p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
  p_contact_point_rec.owner_table_id        := '68277';
  p_contact_point_rec.primary_flag          := 'Y';
  p_contact_point_rec.contact_point_purpose :=null;
  p_contact_point_rec.created_by_module     := 'HZ_CPUI';
  p_web_rec.web_type:='com';
  p_web_rec.url:='www.mangalaparthy';
 hz_contact_point_v2pub.create_web_contact_point ('T',
    p_contact_point_rec ,
    p_web_rec                 ,
    x_contact_point_id        ,
    x_return_status           ,
    x_msg_count               ,
    x_msg_data               
  );
  IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer contact is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust contact point id         = '||TO_CHAR(x_contact_point_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer contact got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
end;


 

anonymous block for customer email contact point creation

-----------------********anonymous block for customer email contact point creation *****-------------------

declare
p_contact_point_rec             hz_contact_point_v2pub.contact_point_rec_type;
    p_email_rec                   hz_contact_point_v2pub.email_rec_type;
    x_contact_point_id            NUMBER;
    x_return_status                 VARCHAR2(200);
    x_msg_count                    NUMBER;
    x_msg_data                      VARCHAR2(200);
begin
p_contact_point_rec.contact_point_type    := 'EMAIL';
  p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
  p_contact_point_rec.owner_table_id        := '68277';
  p_contact_point_rec.primary_flag          := 'Y';
  p_contact_point_rec.contact_point_purpose := 'BUSINESS';
   p_email_rec.email_address                 :='narsimlu@live.com';
  p_email_rec.email_format                  :='MAILHTML';
  p_contact_point_rec.created_by_module     := 'HZ_CPUI';
hz_contact_point_v2pub.create_email_contact_point (
   'T',
    p_contact_point_rec        ,
    p_email_rec                ,
    x_contact_point_id         ,
    x_return_status            ,
    x_msg_count                ,
    x_msg_data                
  );
  IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer contact is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust contact point id         = '||TO_CHAR(x_contact_point_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer contact got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
  end;

anonymous block for customer phone contact creation

----------------******anonymous block for customer phone contact creation*******------------------

declare
   p_contact_point_rec            hz_contact_point_v2pub.contact_point_rec_type;
    p_phone_rec                 hz_contact_point_v2pub.phone_rec_type;
    x_contact_point_id            NUMBER;
    x_return_status                 VARCHAR2(200);
    x_msg_count                     NUMBER;
    x_msg_data                      VARCHAR2(200);
begin
  p_contact_point_rec.contact_point_type    := 'PHONE';
  p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
  p_contact_point_rec.owner_table_id        := '68277';
  p_contact_point_rec.primary_flag          := 'Y';
  p_contact_point_rec.contact_point_purpose := 'BUSINESS';
 p_phone_rec.phone_number                  := '506-7000';
  p_phone_rec.phone_line_type               := 'GEN';
  p_contact_point_rec.created_by_module     := 'HZ_CPUI';
hz_contact_point_v2pub.create_phone_contact_point (
   'T',
    p_contact_point_rec        ,
    p_phone_rec                ,
    x_contact_point_id         ,
    x_return_status            ,
    x_msg_count                ,
    x_msg_data                
  );
IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer contact is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust contact point id         = '||TO_CHAR(x_contact_point_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer contact got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
end;
/
 

anonymous block for contact point creation

------------------*****anonymous block for contact point creation****---------------
DECLARE
  p_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
  p_edi_rec hz_contact_point_v2pub.edi_rec_type;
  p_email_rec hz_contact_point_v2pub.email_rec_type;
  p_phone_rec hz_contact_point_v2pub.phone_rec_type;
  p_telex_rec hz_contact_point_v2pub.telex_rec_type;
  p_web_rec hz_contact_point_v2pub.web_rec_type;
  x_return_status    VARCHAR2 (2000);
  x_msg_count        NUMBER;
  x_msg_data         VARCHAR2 (2000);
  x_contact_point_id NUMBER;
BEGIN
  p_contact_point_rec.contact_point_type    := 'PHONE';
  p_contact_point_rec.owner_table_name      := 'HZ_PARTIES';
  p_contact_point_rec.owner_table_id        := '63294';
  p_contact_point_rec.primary_flag          :='Y';
  p_contact_point_rec.contact_point_purpose := 'BUSINESS';
  p_phone_rec.phone_area_code               := '650';
  p_phone_rec.phone_country_code            := '1';
  p_phone_rec.phone_number                  := '506-7000';
  p_phone_rec.phone_line_type               := 'GEN';
  p_contact_point_rec.created_by_module     := 'HZ_CPUI';
  hz_contact_point_v2pub.create_contact_point ('T',
  p_contact_point_rec,
  p_edi_rec,
  p_email_rec,
  p_phone_rec,
  p_telex_rec,
  p_web_rec,
  x_contact_point_id,
  x_return_status,
  x_msg_count,
  x_msg_data );
  IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer Profile is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust account id           = '||TO_CHAR(x_contact_point_id));
    --dbms_output.put_line('Cust account number           = '||TO_CHAR(x_account_number));
    --dbms_output.put_line('Cust party id           = '||TO_CHAR(x_party_id));
    --dbms_output.put_line('Cust party number       = '||TO_CHAR(x_party_number));
    --dbms_output.put_line('Cust profile Id         = '||x_profile_id);
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer Profile got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
end;
 

anonymous block for customer profile updation

--------------********anonymous block for customer profile updation*********------------------------

declare
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count     NUMBER;
x_msg_data      VARCHAR2 (2000);
P_OBJECT_VERSION_NUMBER number:=1;
begin
p_customer_profile_rec.party_id:=68277;
p_customer_profile_rec.cust_account_profile_id:=20936;
p_customer_profile_rec.cust_account_id:=17070;
p_customer_profile_rec.site_use_id:=9444;
--p_customer_profile_rec.standard_terms:=v_term_id;
--p_customer_profile_rec.cust_account_id='14072';
hz_customer_profile_v2pub.update_customer_profile(
    'T',
    p_customer_profile_rec            ,
    p_object_version_number           ,
    x_return_status                   ,
    x_msg_count                       ,
    x_msg_data                       
);
IF  x_return_status ='S' THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer Profile updation  is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    --dbms_output.put_line('Cust party Id           = '||TO_CHAR(x_party_id));
    --dbms_output.put_line('Cust party number       = '||TO_CHAR(x_party_number));
    --dbms_output.put_line('Cust profile Id         = '||x_profile_id);
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer Profile updation got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
end;
/
 

anonymous block for customer account site uses creation

-----------*********anonymous block for customer account site uses creation ****--------------------
declare
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
x_site_use_id   NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count     NUMBER;
x_msg_data      VARCHAR2 (2000);
begin
p_cust_site_use_rec.cust_acct_site_id :=7697;
  --p_cust_site_use_rec.site_use_code     := 'SHIP_TO';
  p_cust_site_use_rec.site_use_code     := 'BILL_TO';
  --p_cust_site_use_rec.site_use_code     := 'SOLD_TO';
  p_cust_site_use_rec.LOCATION          := 'TCA';
  p_cust_site_use_rec.created_by_module := 'HZ_CPUI';
  hz_cust_account_site_v2pub.create_cust_site_use (
    'T',
    p_cust_site_use_rec ,
    p_customer_profile_rec  ,
    'T',
    'T',
    x_site_use_id   ,
    x_return_status ,
    x_msg_count     ,
    x_msg_data      );
   IF  x_return_status ='S'THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer account site use  is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust party site use Id           = '||TO_CHAR(x_site_use_id));
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer account site use got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
end;
/