Tuesday 10 April 2012

customer API - new

declare
--procedure call_update(p_customer  varchar2, p_address  number);
CURSOR cur_header
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_HDR_STG_T;
--
--Header Phone
CURSOR cur_header_phone( p_customer_number IN VARCHAR2)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_PHONE_STG_T
     WHERE customer_number = p_customer_number;
--
-- Header Contact
CURSOR cur_header_contact( p_customer_number IN VARCHAR2)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_CONTACT_STG_T
     WHERE customer_number = p_customer_number;

--
-- Header Contact Phone
CURSOR cur_header_con_phone( p_customer_number IN VARCHAR2
, p_contact_id IN NUMBER)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_CON_PHONE_STG_T
     WHERE contact_id = p_contact_id
     AND customer_number = p_customer_number;
--
-- Header Contact Roles
CURSOR cur_header_con_role( p_customer_number IN VARCHAR2
, p_contact_id IN NUMBER)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_CON_ROLE_STG_T
     WHERE contact_id = p_contact_id
     AND customer_number = p_customer_number;

--
-- Sites Location
CURSOR cur_sites_location( p_customer_number IN VARCHAR2)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_LOCATION_STG_T
     WHERE customer_number = p_customer_number;

--
-- Sites Phone
CURSOR cur_sites_phone( p_customer_number IN VARCHAR2)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_SITE_PHONE_STG_T
     WHERE customer_number = p_customer_number;

--
-- Sites Use
CURSOR cur_sites_use( p_customer_number IN VARCHAR2
, p_address_id IN NUMBER)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_SITE_USE_STG_T
     WHERE address_id = p_address_id
     AND customer_number = p_customer_number
     ORDER BY SITE_USE_CODE;

--
-- Sites Contact
CURSOR cur_sites_con( p_customer_number IN VARCHAR2
, p_address_id IN NUMBER)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_SITE_CON_STG_T
     WHERE address_id = p_address_id
     AND customer_number = p_customer_number;

--
-- Sites Contact Phone
CURSOR cur_sites_con_phone( p_customer_number IN VARCHAR2
, p_contact_id IN NUMBER)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_SI_CON_PH_STG_T
     WHERE contact_id = p_contact_id
     AND customer_number = p_customer_number;

--
-- Header Contact Role
CURSOR    cur_sites_con_role( p_customer_number IN VARCHAR2
, p_contact_id IN NUMBER)
      IS
         SELECT *
         FROM   XXMTZ.MAS_AR_CSTMR_SI_ROLE_STG_T
     WHERE contact_id = p_contact_id
     AND customer_number = p_customer_number;
   
------
-- API Record Type Declaration
       
      p_organization_rec            apps.hz_party_v2pub.organization_rec_type;
      p_cust_account_rec            apps.HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
      p_person_rec                  apps.hz_party_v2pub.person_rec_type;
      p_customer_profile_rec        apps.hz_customer_profile_v2pub.customer_profile_rec_type;
      p_org_contact_rec             apps.hz_party_contact_v2pub.org_contact_rec_type;
      p_org_contact_role_rec        apps.hz_party_contact_v2pub.org_contact_role_rec_type;
      p_location_rec                apps.hz_location_v2pub.location_rec_type;
      p_party_site_rec              apps.hz_party_site_v2pub.party_site_rec_type;
      p_party_site_use_rec          apps.hz_party_site_v2pub.party_site_use_rec_type;
      p_cust_site_use_rec           apps.hz_cust_account_site_v2pub.cust_site_use_rec_type;
      p_cust_profile_amt_rec_type    apps.HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
      p_cust_acct_site_rec          apps.hz_cust_account_site_v2pub.cust_acct_site_rec_type;
      p_cust_profile_amt_rec        apps.hz_customer_profile_v2pub.cust_profile_amt_rec_type;
      p_cr_cust_acc_role_rec        apps.hz_cust_account_role_v2pub.cust_account_role_rec_type;
      p_contact_point_rec           apps.hz_contact_point_v2pub.contact_point_rec_type;
      p_role_responsibility_rec        apps.HZ_CUST_ACCOUNT_ROLE_V2PUB.ROLE_RESPONSIBILITY_REC_TYPE;
      p_edi_rec                     apps.hz_contact_point_v2pub.edi_rec_type;
      p_email_rec                   apps.hz_contact_point_v2pub.email_rec_type;
      p_phone_rec                   apps.hz_contact_point_v2pub.phone_rec_type;
      p_telex_rec                   apps.hz_contact_point_v2pub.telex_rec_type;
      p_web_rec                     apps.hz_contact_point_v2pub.web_rec_type;
-----
-- Variable Declaration
    
      l_header_count                NUMBER := 0;
      l_custname_exists             NUMBER := 0;
      l_lookups_exist               NUMBER := 0;
      l_addr1_count                 NUMBER := 0;
      l_header_total_count          NUMBER := 0;
      l_header_success_count        NUMBER := 0;
      l_header_fail_count           NUMBER := 0;
      l_site_success_count          NUMBER := 0;
      l_site_fail_count             NUMBER := 0;
      l_site_total_count            NUMBER := 0;
      l_comment_total_count         NUMBER := 0;
      l_comment_success_count       NUMBER := 0;
      l_comment_fail_count          NUMBER := 0;
      l_home_phone_count            NUMBER := 0;
      l_business_phone_count        NUMBER := 0;
      l_site_rollback               VARCHAR2 (1) := 'N';
      l_org_flag                    VARCHAR2 (1);
      l_cstmr_flag                  VARCHAR2 (1);
      l_location_flag               VARCHAR2 (1);
      l_party_site_flag             VARCHAR2 (1);
      l_party_site_use_flag         VARCHAR2 (1);
      l_cust_acct_site_flag         VARCHAR2 (1);
      l_cust_acct_site_use_flag     VARCHAR2 (1);
      l_home_contact_flag           VARCHAR2 (1);
      l_business_contact_flag       VARCHAR2 (1);
      l_email1_contact_flag         VARCHAR2 (1);
      l_email2_contact_flag         VARCHAR2 (1);
      l_notes_flag                  VARCHAR2 (1);
      l_message_out                 VARCHAR2 (4000) := NULL;
      l_concat_party_name           VARCHAR2 (300);
      l_contact_party_id        NUMBER;
      l_contact_party_number        NUMBER;
      l_contact_cust_account_id     NUMBER;
      l_contact_profile_id        NUMBER;
      l_org_contact_role_id         NUMBER;
      l_org_contact_id            NUMBER;
      l_party_rel_id            NUMBER;
      l_party_id                    NUMBER;
      l_cust_account_id             NUMBER;
      l_profile_id                  NUMBER;
      l_location_id                 NUMBER;
      l_cust_contact_flag        NUMBER;
      l_cust_contact_party_id       NUMBER;
      l_cust_contact_party_number   NUMBER;
      l_cust_contact_profile_id        NUMBER;
      l_party_site_id               NUMBER;
      l_party_site_use_id           NUMBER;
      l_party_site_number           NUMBER;
      l_party_number                NUMBER;
      l_cust_acct_site_id           NUMBER;
      l_site_use_id                 NUMBER;
      l_home_contact_point_id       NUMBER;
      l_business_contact_point_id   NUMBER;
      l_email1_contact_point_id     NUMBER;
      l_email2_contact_point_id     NUMBER;
      --l_jtf_note_id            NUMBER;
      l_msg_data                    VARCHAR2 (8000);
      l_return_status               VARCHAR2 (2000);
      l_api_header_msg              VARCHAR2 (8000);
      l_api_site_msg                VARCHAR2 (8000);
      --api_msg            VARCHAR2(8000);
      l_msg_count                   NUMBER;
      x_cust_account_id             NUMBER;
      x_account_number              VARCHAR2 (2000);
      x_org_contact_id            NUMBER;
      x_party_rel_id            NUMBER;
      x_party_id                    NUMBER;
      x_party_number                VARCHAR2 (2000);
      x_contact_point_id            number;
      x_profile_id                  NUMBER;
      x_cust_acct_profile_amt_id    NUMBER;
      x_org_contact_role_id         NUMBER;
      x_return_status               VARCHAR2 (2000);
      x_msg_count                   NUMBER;
      x_msg_data                    VARCHAR2 (2000);
      x_location_id                 NUMBER;
      x_party_site_id               NUMBER;
      x_party_site_number           VARCHAR2 (2000);
      x_party_site_use_id           NUMBER;
      x_cust_acct_site_id           NUMBER;
      x_site_use_id                 NUMBER;
      x_home_contact_point_id       NUMBER;
      x_business_contact_point_id   NUMBER;
      x_email1_contact_point_id     NUMBER;
      x_cust_account_role_id        NUMBER;
      x_email2_contact_point_id     NUMBER;
      x_responsibility_id           NUMBER;
      gc_created_by_module  VARCHAR2(60):= 'CUST_INTERFACE';
      a varchar2(100);
l_profile_class_id number;
 l_interested_period_days number;
 l_cust_account_profile_id  Number;
 l_location_check varchar2(1) := 'N';
 l_party_site_check varchar2(1) := 'N';
 l_bill_to_check varchar2(1) := 'N';
 l_ship_to_check varchar2(1) := 'N';
 l_cm_to_check varchar2(1) := 'N';
 l_contact_person_check varchar2(1) := 'N';
 l_cust_site_check varchar2(1) := 'N';
 l_cust_site_bill_to_check varchar2(1) := 'N';
 l_cust_site_ship_to_check varchar2(1):= 'N';
 l_cust_site_cm_to_check varchar2(1) := 'N';
 l_phone_contact_check varchar2(1) := 'N';
 l_contact_definition_check varchar2(1) := 'N';
 l_bill_to_location_id NUMBER;
 l_statement_cycle_id NUMBER;
 l_cust_contact_party_rel_id number;
 l_header_con_party_id NUMBER;
 l_header_acc_role_party_id NUMBER;
 l_header_account_role_id NUMBER;
 l_sites_con_party_id NUMBER;
 l_site_acc_role_party_id NUMBER;
 l_site_account_role_id NUMBER;
 l_site_location_id NUMBER;
 l_bill_to_site_use_id NUMBER;

             
begin

/* BEGIN
 UPDATE XXMTZ.MAS_AR_CSTMR_SITES_STG_T
      SET V_ORG_CUST_FLAG                 = 'N'  
    ,V_CUST_PHONE_FLAG          = 'N'
    ,V_CUST_CONTACT_FLAG             = 'N'
    ,V_CUST_CONTACT_PHONE_FLAG       = 'N'
    ,V_CUST_CONTACT_ROLE_FLAG        = 'N'
    ,V_CUST_PROFILE_AMT_FLAG    = 'N'
    ,V_CONTACT_POINT_FLAG = 'N'
    ,V_CONTACT_PARTY_FLAG = 'N'
    ,V_CONTACT_PHONE_FLAG = 'N'
    ,V_CONTACT_ROLE_FLAG = 'N'
    ,V_LOCATION_FLAG        = 'N'
    ,V_PARTY_SITE_FLAG       = 'N'
    ,V_CUST_SITE_FLAG= 'N'
    ,V_CUST_SITE_PHONE_FLAG = 'N'
    ,V_CUST_SITE_USE_FLAG        = 'N'
    ,V_CUST_SITE_USE_PROFILE_FLAG = 'N'
    ,V_CUST_SITE_CONTACT_FLAG = 'N'
    ,V_CUST_SITE_CONTACT_PHONE_FLAG = 'N'
            ,api_msg = 'N'
          END;*/
   

      -- Customer Header
      FOR rec_header IN cur_header
      LOOP
       
-- --------------------------------------------------------------
-- Create Organization, Customer Account and Customer Profile
-- --------------------------------------------------------------              
        BEGIN
          SELECT profile_class_id
          INTO l_profile_class_id
          FROM HZ_CUST_PROFILE_CLASSES
          WHERE name = rec_header.profile_class_name;
          dbms_output.put_line('SS:'||l_profile_class_id);
        EXCEPTION
          WHEN OTHERS THEN
          dbms_output.put_line('ERROR');
        END;   
     
        p_cust_account_rec.status := rec_header.status_party;
        p_cust_account_rec.account_name := rec_header.customer_name;
        p_cust_account_rec.created_by_module := gc_created_by_module;
        p_cust_account_rec.customer_type := rec_header.customer_type;
        --p_cust_account_rec.attribute10 := rec_header.attribute10;
        --p_cust_account_rec.attribute11 := rec_header.attribute11;
        --p_cust_account_rec.attribute12 := rec_header.attribute12;
        --p_cust_account_rec.customer_class_code := 'commercial';
       -- p_cust_account_rec.tax_header_level_flag := rec_header.tax_header_level_flag;
        --p_cust_account_rec.arrivalsets_include_lines_flag := rec_header.arrivalsets_include_lines_flag;
        --p_cust_account_rec.ship_sets_include_lines_flag := rec_header.ship_sets_include_lines_flag;
        --p_cust_account_rec.sched_date_push_flag := rec_header.sched_date_push_flag;
        p_organization_rec.organization_name := rec_header.customer_name; --'Test_ADESTA COMMUNICATIONS, INC.';
        p_organization_rec.duns_number_c := rec_header.duns_number_c;
        p_organization_rec.sic_code := rec_header.sic_code;
        If rec_header.sic_code_type is null Then
        p_organization_rec.sic_code_type := 'OTHER';
        Else
        p_organization_rec.sic_code_type := rec_header.sic_code_type;
        End If;
        p_organization_rec.created_by_module := gc_created_by_module;
        If rec_header.gsa_indicator is not null Then
        p_organization_rec.gsa_indicator_flag := rec_header.gsa_indicator;
        End If;
        p_organization_rec.organization_name := rec_header.customer_name;
         p_organization_rec.created_by_module := gc_created_by_module;
        p_customer_profile_rec.collector_id := rec_header.collector_id;
        p_customer_profile_rec.status := rec_header.status_profile;
        p_customer_profile_rec.profile_class_id := l_profile_class_id;
        p_customer_profile_rec.credit_checking := rec_header.credit_checking;     
        p_customer_profile_rec.tolerance := rec_header.tolerance;        --validation between -100 and 100
        p_customer_profile_rec.discount_terms := rec_header.discount_terms;
        p_customer_profile_rec.dunning_letters := rec_header.dunning_letters;       
        p_customer_profile_rec.interest_charges := rec_header.interest_charges;
        p_customer_profile_rec.send_statements := rec_header.statements;          
        p_customer_profile_rec.credit_balance_statements := rec_header.credit_balance_statements;  
        p_customer_profile_rec.credit_hold := rec_header.credit_hold;
        p_customer_profile_rec.standard_terms := rec_header.standard_terms;        --validation against ar_terms
        p_customer_profile_rec.override_terms := rec_header.override_terms;
        If rec_header.INTEREST_PERIOD_DAYS is not null Then
        p_customer_profile_rec.interest_charges :=  'Y';
        p_customer_profile_rec.charge_on_finance_charge_flag := 'Y';
        p_customer_profile_rec.interest_period_days :=  rec_header.INTEREST_PERIOD_DAYS;
        End If;
        p_customer_profile_rec.auto_rec_incl_disputed_flag := rec_header.auto_rec_incl_disputed_flag;  
        p_customer_profile_rec.tax_printing_option    := rec_header.tax_printing_option;   -- validation tax_printing_option lookup  
        p_customer_profile_rec.cons_inv_flag := rec_header.cons_inv_flag;           
                    
         
            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
                                                      );
l_party_id := x_party_id;
l_cust_account_id := x_cust_account_id;
          dbms_output.put_line('x_return_status = '||SUBSTR(x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Party Id = '||TO_CHAR(x_party_id));
dbms_output.put_line('Party Number = '||x_party_number);
dbms_output.put_line('Profile Id = '||TO_CHAR(x_profile_id));
dbms_output.put_line('x_msg_data = '|| SUBSTR (x_msg_data,1,255));
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;

-- --------------------------------------------------------------
-- Create Customer Profile Amount
-- --------------------------------------------------------------
      
        BEGIN
          select cust_account_profile_id
          Into l_cust_account_profile_id
          From hz_customer_profiles Where cust_account_id in
          (Select cust_account_id
          from hz_cust_accounts where party_id = x_party_id);
          End;
        
        p_cust_profile_amt_rec_type.cust_account_id := x_cust_account_id;
        p_cust_profile_amt_rec_type.cust_account_profile_id := l_cust_account_profile_id ;
        p_cust_profile_amt_rec_type.currency_code := rec_header.currency_code; --Validation against fnd_currencies
        p_cust_profile_amt_rec_type.trx_credit_limit := rec_header.trx_credit_limit;
        p_cust_profile_amt_rec_type.overall_credit_limit := rec_header.overall_credit_limit;
        p_cust_profile_amt_rec_type.created_by_module := gc_created_by_module;
        hz_customer_profile_v2pub.create_cust_profile_amt(
            'T',
            'T',
            p_cust_profile_amt_rec_type,
            x_cust_acct_profile_amt_id,
            x_return_status,
            x_msg_count,
            x_msg_data);
            dbms_output.put_line('x_return_status = '||SUBSTR(x_return_status,1,255));
dbms_output.put_line('Cust Account Profile Id = '||TO_CHAR(x_cust_acct_profile_amt_id));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('x_msg_data = '|| SUBSTR (x_msg_data,1,255));
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;
  
------------------
-- Create Header Location
---------------------

p_location_rec.country := rec_header.country; --'US';
p_location_rec.address1 := rec_header.address1;
p_location_rec.address2 := rec_header.address2;
p_location_rec.address2 := rec_header.address3;
p_location_rec.city := rec_header.city;
p_location_rec.postal_code := rec_header.postal_code;
p_location_rec.state := rec_header.state;
p_location_rec.created_by_module := gc_created_by_module;
hz_location_v2pub.create_location(
'T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line('Location id '||x_location_id);
dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
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;


-----------------------
-- Create Party Site
---------------------------
p_party_site_rec.party_id := l_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 := 'CUST_INTERFACE';
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);
dbms_output.put_line('PartySiteid '||x_party_site_id);
dbms_output.put_line(SubStr('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
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 rec_header_phone IN cur_header_phone (rec_header.customer_number)
LOOP
----------------------------
-- Create Header Phones
-----------------------------
IF rec_header_phone.contact_point_type = 'PHONE'
THEN

-----------------------
-- Create Header Phone
---------------------------
         p_contact_point_rec.contact_point_type := 'PHONE';
                  p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
                  p_contact_point_rec.owner_table_id := l_party_id;--l_party_site_id;
                  p_contact_point_rec.primary_flag := rec_header_phone.primary_flag;
                 -- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
                  -- p_contact_point_rec.status := rec_header_phone.status;
                  p_phone_rec.phone_area_code := rec_header_phone.area_code;
                  p_phone_rec.phone_country_code := rec_header_phone.country_code;
                  p_phone_rec.phone_number := rec_header_phone.phone_number;
                  p_phone_rec.phone_line_type := rec_header_phone.phone_type;
                  p_contact_point_rec.created_by_module := 'CUST_INTERFACE';
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);
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact PhonePoint Id = '||TO_CHAR(x_contact_point_id));
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;

END IF; -- Phone

IF rec_header_phone.contact_point_type = 'EMAIL'
THEN

-----------------------
-- Create Header Email
---------------------------
         p_contact_point_rec.contact_point_type := 'EMAIL';
                  p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
                  p_contact_point_rec.owner_table_id := l_party_id;--l_party_site_id;
                  p_contact_point_rec.primary_flag := rec_header_phone.primary_flag;
                 -- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
                  p_contact_point_rec.status := rec_header_phone.status;
                  p_email_rec.email_format := rec_header_phone.email_format;
                  p_email_rec.email_address := rec_header_phone.email_address;
                  --p_email_rec.url := rec_header_phone.url;
                  p_phone_rec.phone_line_type := rec_header_phone.phone_type;
                  p_contact_point_rec.created_by_module := 'CUST_INTERFACE';
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);
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact EmailPoint Id = '||TO_CHAR(x_contact_point_id));
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;

END IF; -- EMail

END LOOP; -- Customer Header Phones

FOR rec_header_contact IN cur_header_contact (rec_header.customer_number)
LOOP

-----------------------
-- Create Header Contact
---------------------------
p_person_rec.person_first_name := rec_header_contact.first_name;
        p_person_rec.person_last_name := rec_header_contact.last_name;
        p_person_rec.created_by_module := 'CUST_INTERFACE';
        HZ_PARTY_V2PUB.create_person(
                    'T',
                     p_person_rec,
                     x_party_id,
                     x_party_number,
                     x_profile_id,
                     x_return_status,
                     x_msg_count,
                     x_msg_data);
      
l_header_con_party_id := x_party_id;  
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact Definition Party Id = '||TO_CHAR(x_party_id));
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;

--------------------------------------
-- Create Header Contact Relationship
---------------------------------------

--p_org_contact_rec.department_code := 'ACCOUNTING';
          --p_org_contact_rec.job_title := 'ACCOUNTS OFFICER';
          --p_org_contact_rec.decision_maker_flag := 'Y';
--          p_org_contact_rec.job_title_code := ;
         p_org_contact_rec.contact_number := '12345';
          --p_org_contact_rec.status := rec_sites.status_contact;
          p_org_contact_rec.created_by_module := 'CUST_INTERFACE';
          -- p_org_contact_rec.party_site_id := 26211;
          p_org_contact_rec.party_rel_rec.subject_id := l_header_con_party_id ; --<<value for party_id from step 7>
          p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
          p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
          p_org_contact_rec.party_rel_rec.object_id := l_party_id; --<<value for party_id from step 2>
          p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
          p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
          p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
          p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
          p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
          hz_party_contact_v2pub.create_org_contact(
                        'T',
                        p_org_contact_rec,
                        x_org_contact_id,
                        x_party_rel_id,
                        x_party_id,
                        x_party_number,
                        x_return_status,
                        x_msg_count,
                        x_msg_data);
l_header_acc_role_party_id := x_party_id;
dbms_output.put_line('x_return_status = '|| SUBSTR (x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Relationship Id ='||TO_CHAR(x_party_rel_id));
dbms_output.put_line('Party Id = '||TO_CHAR(x_party_id));
dbms_output.put_line('Org Contact Id = '|| to_char (x_org_contact_id));
dbms_output.put_line('x_msg_data = '|| SUBSTR (x_msg_data,1,255));
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;

--------------------------------------
-- Create Header Contact Role
---------------------------------------

p_cr_cust_acc_role_rec.party_id := l_header_acc_role_party_id; --<<value for party_id from step 8>
        p_cr_cust_acc_role_rec.cust_account_id := l_cust_account_id; --<<value for cust_account_id from step 2> (Headerlevel)
        --p_cr_cust_acc_role_rec.cust_acct_site_id := 23124; --<<value for cust_acct_site_id from step 5> (Site Level)
        p_cr_cust_acc_role_rec.primary_flag := 'Y';
        p_cr_cust_acc_role_rec.role_type := 'CONTACT';
        p_cr_cust_acc_role_rec.created_by_module := 'CUST_INTERFACE';
        HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role(
                    'T',
                    p_cr_cust_acc_role_rec,
                    x_cust_account_role_id,
                    x_return_status,
                    x_msg_count,
                    x_msg_data);
l_header_account_role_id := x_cust_account_role_id;
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact cust account role Id = '||TO_CHAR(x_cust_account_role_id));
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 rec_header_con_phone IN cur_header_con_phone (rec_header.customer_number
, rec_header_contact.contact_id)
LOOP
--------------------------------------
-- Create Header Contact Phones
---------------------------------------

p_contact_point_rec.contact_point_type := 'PHONE';
                  p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
                  p_contact_point_rec.owner_table_id := l_header_acc_role_party_id;--l_party_site_id;
                  p_contact_point_rec.primary_flag := rec_header_con_phone.primary_flag;
                 -- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
                  -- p_contact_point_rec.status := rec_header_con_phone.status;
                  p_phone_rec.phone_area_code := rec_header_con_phone.area_code;
                  p_phone_rec.phone_country_code := rec_header_con_phone.country_code;
                  p_phone_rec.phone_number := rec_header_con_phone.phone_number;
                  p_phone_rec.phone_line_type := rec_header_con_phone.phone_type;
                  p_contact_point_rec.created_by_module := 'CUST_INTERFACE';
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);
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact PhonePoint Id = '||TO_CHAR(x_contact_point_id));
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;

END LOOP; -- Header Contact Phones

FOR rec_header_con_role IN cur_header_con_role (rec_header.customer_number
, rec_header_contact.contact_id)
LOOP

----------------------------------------------
-- Create Header Contact Role Responsibility
-----------------------------------------------

p_role_responsibility_rec.responsibility_type := rec_header_con_role.usage_code;
p_role_responsibility_rec.cust_account_role_id := l_header_account_role_id;
        p_role_responsibility_rec.primary_flag := rec_header_con_role.primary_flag;
        p_role_responsibility_rec.created_by_module := 'CUST_INTERFACE';
        HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility(
                    'T',
                    p_role_responsibility_rec,
                    x_responsibility_id,
                    x_return_status,
                    x_msg_count,
                    x_msg_data);

dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact x_responsibility_id = '||TO_CHAR(x_responsibility_id));
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;

END LOOP; -- Header Contact Roles

END LOOP; --Header Contact

FOR rec_sites_location IN cur_sites_location (rec_header.customer_number)
LOOP
-----------------------------------------
-- Create Location
------------------------------------------
p_location_rec.country := rec_sites_location.country;
p_location_rec.address1 := rec_sites_location.address1;
p_location_rec.address2 := rec_sites_location.address2;
p_location_rec.address3 := rec_sites_location.address3;
p_location_rec.city := rec_sites_location.city;
p_location_rec.postal_code := rec_sites_location.postal_code;
p_location_rec.state := rec_sites_location.state;
p_location_rec.province := rec_sites_location.province;
--p_location_rec.attribute1 := rec_sites_location.attribute1;
p_location_rec.sales_tax_geocode := rec_sites_location.sales_tax_geocode;
p_location_rec.sales_tax_inside_city_limits := rec_sites_location.sales_tax_inside_city_limits;
p_location_rec.created_by_module := 'CUST_INTERFACE';
hz_location_v2pub.create_location(
'T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data);
l_site_location_id := x_location_id;
dbms_output.put_line('x_return_status = '||SUBSTR(x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Location Id = '||TO_CHAR(x_location_id));
dbms_output.put_line('Country = '|| SUBSTR(p_location_rec.country,1,255));
dbms_output.put_line('Address1 = '|| SUBSTR(p_location_rec.Address1,1,255));
dbms_output.put_line('State = '|| SUBSTR(p_location_rec.state,1,255));
dbms_output.put_line('Created By = '|| SUBSTR(p_location_rec.created_by_module,1,255));
dbms_output.put_line('x_msg_data = '|| SUBSTR(x_msg_data,1,255));
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;

-----------------------------------------
-- Create Party Site
------------------------------------------
p_party_site_rec.party_id := l_party_id;
p_party_site_rec.location_id := l_site_location_id;
p_party_site_rec.identifying_address_flag := rec_sites_location.identifying_address_flag;
--p_party_site_rec.status := 'Y';
p_party_site_rec.created_by_module := 'CUST_INTERFACE';
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);
l_party_site_id := x_party_site_id;
dbms_output.put_line('x_return_status = '||SUBSTR(x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Party Site Id = '||TO_CHAR(x_party_site_id));
dbms_output.put_line('Party Site Number = '||SUBSTR(x_party_site_number,1,255));
dbms_output.put_line('x_msg_data = '||SUBSTR(x_msg_data,1,255));
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;
-----------------------------------------
-- Create Customer Account Site
------------------------------------------
  fnd_global.set_nls_context('AMERICAN');
  fnd_global.apps_initialize(1236, 50559, 222);
MO_GLOBAL.INIT('AR');
MO_GLOBAL.set_policy_context('M', 14460);
p_cust_acct_site_rec.cust_account_id := l_cust_account_id;
p_cust_acct_site_rec.party_site_id := l_party_site_id;
p_cust_acct_site_rec.territory := rec_sites_location.territory;
p_cust_acct_site_rec.created_by_module := 'CUST_INTERFACE';
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);
l_cust_acct_site_id := x_cust_acct_site_id;
dbms_output.put_line('x_return_status = '||SUBSTR(x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Customer Account Site Id is = '||TO_CHAR(x_cust_acct_site_id));
dbms_output.put_line('x_msg_data = '|| SUBSTR(x_msg_data,1,255));
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 rec_sites_use IN cur_sites_use (rec_header.customer_number
, rec_sites_location.address_id)
LOOP
-----------------------------------------
-- Create Party Site Use
------------------------------------------
p_party_site_use_rec.site_use_type := rec_sites_use.site_use_code;
p_party_site_use_rec.party_site_id := l_party_site_id;
p_party_site_use_rec.status := rec_sites_use.site_use_status;
p_party_site_use_rec.primary_per_type := rec_sites_use.primary_flag;
p_party_site_use_rec.created_by_module := 'CUST_INTERFACE';
hz_party_site_v2pub.create_party_site_use(
'T',
p_party_site_use_rec,
x_party_site_use_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line('x_return_status = '||substr(x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Site Use Id = '||TO_CHAR(x_party_site_use_id));
dbms_output.put_line(SubStr('x_msg_data = '||x_msg_data,1,255));
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;
---------------------------------------------
-- Create a Customer Site Use for BILL_TO
---------------------------------------------
IF rec_sites_use.site_use_code = 'BILL_TO'
THEN
p_cust_site_use_rec.cust_acct_site_id := l_cust_acct_site_id;
p_cust_site_use_rec.site_use_code := rec_sites_use.site_use_code;
p_cust_site_use_rec.status := rec_sites_use.site_use_status;
p_cust_site_use_rec.primary_flag := rec_sites_use.primary_flag;
p_cust_site_use_rec.payment_term_id := rec_sites_use.payment_term_id;
p_cust_site_use_rec.gsa_indicator := rec_sites_use.gsa_indicator;
--p_cust_site_use_rec.tax_code := 'Sales Tax';
p_cust_site_use_rec.location := rec_sites_use.location;
p_cust_site_use_rec.gl_id_rec := rec_sites_use.gl_id_rec;
p_cust_site_use_rec.gl_id_rev := rec_sites_use.gl_id_rev;
p_cust_site_use_rec.gl_id_tax :=rec_sites_use.gl_id_tax;
p_cust_site_use_rec.gl_id_clearing := rec_sites_use.gl_id_clearing;
p_cust_site_use_rec.gl_id_freight := rec_sites_use.gl_id_freight;
p_cust_site_use_rec.gl_id_unbilled := rec_sites_use.gl_id_unbilled;
p_cust_site_use_rec.gl_id_unearned := rec_sites_use.gl_id_unearned;
p_cust_site_use_rec.gl_id_remittance := rec_sites_use.gl_id_remittance;
p_cust_site_use_rec.gl_id_unpaid_rec := rec_sites_use.gl_id_unpaid_rec;
p_cust_site_use_rec.gl_id_factor := rec_sites_use.gl_id_factor;
--p_cust_site_use_rec.ship_sets_include_lines_flag := rec_sites_use.ship_sets_include_lines_flag;
--p_cust_site_use_rec.arrivalsets_include_lines_flag := rec_sites_use.arrivalsets_include_lines_flag;
--p_cust_site_use_rec.sched_date_push_flag := rec_sites_use.sched_date_push_flag;
            p_cust_site_use_rec.created_by_module := gc_created_by_module;
 p_customer_profile_rec.collector_id := rec_sites_use.collector_id;
--        p_customer_profile_rec.status := rec_sites_use.status_cust_profile;
        p_customer_profile_rec.profile_class_id := rec_sites_use.customer_profile_class_id;
        p_customer_profile_rec.credit_checking := rec_sites_use.credit_checking;     
        p_customer_profile_rec.tolerance := rec_sites_use.tolerance;        --validation between -100 and 100
        p_customer_profile_rec.discount_terms := rec_sites_use.discount_terms;
        p_customer_profile_rec.dunning_letters := rec_sites_use.dunning_letters;       
        p_customer_profile_rec.interest_charges := rec_sites_use.interest_charges;   
        p_customer_profile_rec.credit_balance_statements := rec_sites_use.credit_balance_statements;  
        p_customer_profile_rec.credit_hold := rec_sites_use.credit_hold;
        p_customer_profile_rec.standard_terms := rec_sites_use.standard_terms;        --validation against ar_terms
        If rec_sites_use.statement_cycle_id is not null Then
          Select count(1)
          Into l_statement_cycle_id
          from AR_STATEMENT_CYCLES
          where statement_cycle_id = rec_sites_use.statement_cycle_id;
          If l_statement_cycle_id > 0 Then          
        p_customer_profile_rec.send_statements := rec_sites_use.statements;     
            p_customer_profile_rec.standard_terms := rec_sites_use.statement_cycle_id;        --validation against ar_statement_cycles
          End If;
        End If;  
        p_customer_profile_rec.override_terms := rec_sites_use.override_terms;
       /* If rec_sites_use.INTEREST_PERIOD_DAYS is not null Then
        p_customer_profile_rec.interest_charges :=  'Y';
        p_customer_profile_rec.charge_on_finance_charge_flag := 'Y';
        p_customer_profile_rec.interest_period_days :=  rec_sites_use.INTEREST_PERIOD_DAYS;          
        End If;*/
        p_customer_profile_rec.auto_rec_incl_disputed_flag :=rec_sites_use.auto_rec_incl_disputed_flag;  
        p_customer_profile_rec.tax_printing_option    := rec_sites_use.tax_printing_option;   -- validation tax_printing_option lookup  
        p_customer_profile_rec.cons_inv_flag := rec_sites_use.cons_inv_flag;               
            hz_cust_account_site_v2pub.create_cust_site_use
                                                      ('T'
                                                      ,p_cust_site_use_rec
                                                      ,p_customer_profile_rec
                                                      ,''
                                                      ,''
                                                      ,x_site_use_id
                                                      ,x_return_status
                                                      ,x_msg_count
                                                      ,x_msg_data
                                                      );
l_bill_to_site_use_id := x_site_use_id;                                                  
dbms_output.put_line('x_return_status = '||SUBSTR(x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Cust Site Use Id Btll To = '||TO_CHAR(x_site_use_id));
dbms_output.put_line('Site Use = '|| SUBSTR (p_cust_site_use_rec.site_use_code,1,255));
dbms_output.put_line('x_msg_data = '|| SUBSTR (x_msg_data,1,255));
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;
END IF; -- Bill_To
IF rec_sites_use.site_use_code IN ('SHIP_TO' , 'CM')
THEN
p_cust_site_use_rec.cust_acct_site_id := l_cust_acct_site_id;
p_cust_site_use_rec.site_use_code := rec_sites_use.site_use_code;
p_cust_site_use_rec.status := rec_sites_use.site_use_status;
p_cust_site_use_rec.primary_flag := rec_sites_use.primary_flag;
p_cust_site_use_rec.payment_term_id := rec_sites_use.payment_term_id;
p_cust_site_use_rec.gsa_indicator := rec_sites_use.gsa_indicator;
p_cust_site_use_rec.location := rec_sites_use.location;
If rec_sites_use.site_use_code IN ('SHIP_TO')
Then
  p_cust_site_use_rec.bill_to_site_use_id := l_bill_to_site_use_id;
End If;
p_cust_site_use_rec.created_by_module := 'CUST_INTERFACE';
hz_cust_account_site_v2pub.create_cust_site_use(
'T',
p_cust_site_use_rec,
p_customer_profile_rec,
'',
'',
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data);
dbms_output.put_line('x_return_status = '||SUBSTR(x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Site Use Id = '||TO_CHAR(x_site_use_id));
dbms_output.put_line('Site Use = '|| SUBSTR (p_cust_site_use_rec.site_use_code,1,255));
dbms_output.put_line('x_msg_data = '|| SUBSTR (x_msg_data,1,255));
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;
END IF; --Ship To, CM

END LOOP; -- Site Use

FOR rec_sites_phone IN cur_sites_phone (rec_header.customer_number)
LOOP
----------------------------
-- Create Site Phones
-----------------------------
IF rec_sites_phone.contact_point_type = 'PHONE'
THEN

-----------------------
-- Create Site Phone
---------------------------
         p_contact_point_rec.contact_point_type := 'PHONE';
                  p_contact_point_rec.owner_table_name := 'HZ_PARTY_SITES';
                  p_contact_point_rec.owner_table_id := l_party_site_id;
                  p_contact_point_rec.primary_flag := rec_sites_phone.primary_flag;
                  p_contact_point_rec.contact_point_purpose := 'BUSINESS';
                  -- p_contact_point_rec.status := rec_sites_phone.status;
                  p_phone_rec.phone_area_code := rec_sites_phone.area_code;
                  p_phone_rec.phone_country_code := rec_sites_phone.country_code;
                  p_phone_rec.phone_number := rec_sites_phone.phone_number;
                  p_phone_rec.phone_line_type := rec_sites_phone.phone_type;
                  p_contact_point_rec.created_by_module := 'CUST_INTERFACE';
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);
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact PhonePoint Id = '||TO_CHAR(x_contact_point_id));
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;

END IF; -- Phone

IF rec_sites_phone.contact_point_type = 'EMAIL'
THEN

-----------------------
-- Create Site Email
---------------------------
         p_contact_point_rec.contact_point_type := 'EMAIL';
                  p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
                  p_contact_point_rec.owner_table_id := l_party_site_id;
                  p_contact_point_rec.primary_flag := rec_sites_phone.primary_flag;
                 -- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
                  p_contact_point_rec.status := rec_sites_phone.status;
                  p_email_rec.email_format := rec_sites_phone.email_format;
                  p_email_rec.email_address := rec_sites_phone.email_address;
                  p_contact_point_rec.created_by_module := 'CUST_INTERFACE';
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);
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact EmailPoint Id = '||TO_CHAR(x_contact_point_id));
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;

END IF; -- EMail

END LOOP; -- Site Phone

FOR rec_sites_con IN cur_sites_con (rec_header.customer_number
, rec_sites_location.address_id)
LOOP

-----------------------
-- Create Sites Contact
---------------------------
p_person_rec.person_first_name := rec_sites_con.first_name;
        p_person_rec.person_last_name := rec_sites_con.last_name;
        p_person_rec.created_by_module := 'CUST_INTERFACE';
        HZ_PARTY_V2PUB.create_person(
                    'T',
                     p_person_rec,
                     x_party_id,
                     x_party_number,
                     x_profile_id,
                     x_return_status,
                     x_msg_count,
                     x_msg_data);
      
l_sites_con_party_id := x_party_id;  
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact Definition Party Id = '||TO_CHAR(x_party_id));
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;

--------------------------------------
-- Create Sites Contact Relationship
---------------------------------------


         p_org_contact_rec.contact_number := rec_sites_con.contact_number;
          p_org_contact_rec.created_by_module := 'CUST_INTERFACE';
          p_org_contact_rec.party_rel_rec.subject_id := l_sites_con_party_id ; --<<value for party_id from step 7>
          p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
          p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
          p_org_contact_rec.party_rel_rec.object_id := l_party_id; --<<value for party_id from step 2>
          p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
          p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
          p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
          p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
          p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
          hz_party_contact_v2pub.create_org_contact(
                        'T',
                        p_org_contact_rec,
                        x_org_contact_id,
                        x_party_rel_id,
                        x_party_id,
                        x_party_number,
                        x_return_status,
                        x_msg_count,
                        x_msg_data);
l_site_acc_role_party_id := x_party_id;
dbms_output.put_line('x_return_status = '|| SUBSTR (x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line('Relationship Id ='||TO_CHAR(x_party_rel_id));
dbms_output.put_line('Party Id = '||TO_CHAR(x_party_id));
dbms_output.put_line('Org Contact Id = '|| to_char (x_org_contact_id));
dbms_output.put_line('x_msg_data = '|| SUBSTR (x_msg_data,1,255));
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;

--------------------------------------
-- Create Sites Contact Role
---------------------------------------

p_cr_cust_acc_role_rec.party_id := l_site_acc_role_party_id; --<<value for party_id from step 8>
        p_cr_cust_acc_role_rec.cust_account_id := l_cust_account_id; --<<value for cust_account_id from step 2> (Headerlevel)
        p_cr_cust_acc_role_rec.cust_acct_site_id := l_cust_acct_site_id; --<<value for cust_acct_site_id from step 5> (Site Level)
        p_cr_cust_acc_role_rec.primary_flag := 'Y';
        p_cr_cust_acc_role_rec.role_type := 'CONTACT';
        p_cr_cust_acc_role_rec.created_by_module := 'CUST_INTERFACE';
        HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role(
                    'T',
                    p_cr_cust_acc_role_rec,
                    x_cust_account_role_id,
                    x_return_status,
                    x_msg_count,
                    x_msg_data);
l_site_account_role_id := x_cust_account_role_id;
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact cust account role Id = '||TO_CHAR(x_cust_account_role_id));
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 rec_sites_con_phone IN cur_sites_con_phone (rec_header.customer_number
, rec_sites_con.contact_id)
LOOP
--------------------------------------
-- Create Sites Contact Phones
---------------------------------------

p_contact_point_rec.contact_point_type := 'PHONE';
                  p_contact_point_rec.owner_table_name := 'HZ_PARTIES';
                  p_contact_point_rec.owner_table_id := l_site_acc_role_party_id;--l_party_site_id;
                  p_contact_point_rec.primary_flag := rec_sites_con_phone.primary_flag;
                 -- p_contact_point_rec.contact_point_purpose := 'BUSINESS';
                  -- p_contact_point_rec.status := rec_sites_con_phone.status;
                  p_phone_rec.phone_area_code := rec_sites_con_phone.area_code;
                  p_phone_rec.phone_country_code := rec_sites_con_phone.country_code;
                  p_phone_rec.phone_number := rec_sites_con_phone.phone_number;
                  p_phone_rec.phone_line_type := rec_sites_con_phone.phone_type;
                  p_contact_point_rec.created_by_module := 'CUST_INTERFACE';
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);
dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact PhonePoint Id = '||TO_CHAR(x_contact_point_id));
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;

END LOOP; -- Site Contact Phone
FOR rec_sites_con_role IN cur_sites_con_role (rec_header.customer_number
, rec_sites_con.contact_id)
LOOP

----------------------------------------------
-- Create Sites Contact Role Responsibility
-----------------------------------------------

p_role_responsibility_rec.responsibility_type := rec_sites_con_role.usage_code;
p_role_responsibility_rec.cust_account_role_id := l_site_account_role_id;
        p_role_responsibility_rec.primary_flag := rec_sites_con_role.primary_flag;
        p_role_responsibility_rec.created_by_module := 'CUST_INTERFACE';
        HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility(
                    'T',
                    p_role_responsibility_rec,
                    x_responsibility_id,
                    x_return_status,
                    x_msg_count,
                    x_msg_data);

dbms_output.put_line(SUBSTR('x_return_status = '||x_return_status,1,255));
dbms_output.put_line('x_msg_count = '||TO_CHAR(x_msg_count));
dbms_output.put_line(SUBSTR('x_msg_data = '||x_msg_data,1,255));
dbms_output.put_line('Contact x_responsibility_id = '||TO_CHAR(x_responsibility_id));
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;
END LOOP; -- Site Contact Role
END LOOP; -- Site Contact
END LOOP; -- Site Location
dbms_output.put_line('Customer Header');
END LOOP;
END;
Posted by at
Labels:

1 comment:

  1. Vikas Yadav21 May 2014 at 17:25

    Hi,

    Awesome post :)

    I was trying to create site contact but in you procedure you are passing party_id rather then site id.

    Please help me out with all parameters to create a site contact.

    Thank You



    ReplyDelete

Subscribe to: Post Comments (Atom)