ORACLE PROJECTS

CREATE OR REPLACE PACKAGE XXAVX_CUSTOMER_CONVERSION_PKG

PROCEDURE CONVERSION_CUSTOMER_PROC(

xc_errbuf OUT NOCOPY VARCHAR2

,xn_retcode OUT NOCOPY NUMBER

);

PROCEDURE CUSTOMER_VALIDATE(

xc_errbuf OUT NOCOPY VARCHAR2

,xn_retcode OUT NOCOPY NUMBER

);

PROCEDURE CUSTOMER_LOAD (

xc_errbuf OUT NOCOPY VARCHAR2

,xn_retcode OUT NOCOPY NUMBER

);

END XXAVX_CUSTOMER_CONVERSION_PKG;

create or replace PACKAGE BODY XXAVX_CUSTOMER_CONVERSION_PKG

-- +================================================================+

-- | Name :conversion_customer_proc

-- | Type : Procedure

-- | Description : This program validates the customers data of staging table inserts into basetables with the help of coresponding API

-- | Parameters : p_<field_name> <Field Name>

-- | p_<field_name> <Field Name>

-- |

-- | Returns : xc_errbuf

-- | xn_retcode

-- +================================================================+

------------------------Validation Procedure---------------------------------

PROCEDURE CUSTOMER_VALIDATE(

XC_ERRBUF OUT VARCHAR2

,XN_RETCODE OUT NUMBER

)

CURSOR val_customer

SELECT *

FROM xxavx_customers_stg

WHERE status_flag='N';

--Variables declaration-----------

ln_territory_code VARCHAR2(2);

ln_salesrep_id NUMBER;

ln_term_id NUMBER;

ln_collector_id NUMBER;

ln_profile_class_id NUMBER;

ln_bill_to_site_use_id NUMBER;

ln_total_records NUMBER:=0;

lc_update_flag VARCHAR2(1);

lc_currency_code VARCHAR2(15);

lc_error_msg VARCHAR2(4000);

BEGIN---Validate Pro

SELECT COUNT(*)

INTO ln_total_records

FROM xxavx_customers_stg

WHERE status_flag='N';

fnd_file.put_line (fnd_file.log,'Total Records:'||ln_total_records);

FOR val_custrec IN val_customer

LOOP

ln_territory_code :=NULL;

ln_salesrep_id :=0;

ln_term_id :=0;

ln_collector_id :=0;

ln_profile_class_id:=0;

ln_bill_to_site_use_id:=0;

lc_update_flag :='N';

lc_error_msg :=NULL;

lc_currency_code :=NULL;

---------------Country Validation Starts-------------------------

BEGIN

SELECT territory_code

INTO ln_territory_code

FROM fnd_territories

WHERE territory_code=UPPER(val_custrec.country);

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'territory_code:'||val_custrec.country||'Not Defined' ;

fnd_file.put_line (fnd_file.log,'No records found for territory_code:'||val_custrec.country);

WHEN TOO_MANY_ROWS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'territory_code:'||val_custrec.country||'Most times Defined' ;

fnd_file.put_line (fnd_file.log,'MOre records found for territory_code:'||val_custrec.country);

WHEN OTHERS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'territory_code:'||val_custrec.country||'Other error'||SQLERRM ;

fnd_file.put_line (fnd_file.log,'Other error for territory_code:'||val_custrec.country||SQLERRM );

END;

----------------Currency Validation Start------------------------

IF val_custrec.site_use_code='BILL_TO'

THEN

BEGIN

SELECT currency_code

INTO lc_currency_code

FROM fnd_currencies

WHERE currency_code=UPPER(val_custrec.currency_code);

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Currency_code:'||val_custrec.currency_code||'Not Defined' ;

fnd_file.put_line (fnd_file.log,'No records found for Currency_code:'||val_custrec.currency_code);

WHEN TOO_MANY_ROWS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Currency_code:'||val_custrec.currency_code||'Most times Defined' ;

fnd_file.put_line (fnd_file.log,'MOre records found for Currency_code:'||val_custrec.currency_code);

WHEN OTHERS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Currency_code:'||val_custrec.country||'Other error'||SQLERRM ;

fnd_file.put_line (fnd_file.log,'Other error for Currency_code:'||val_custrec.currency_code||SQLERRM );

END;

END IF;

-----Sales Rep-------------------------

IF val_custrec.primary_sales_person_number IS NOT NULL

THEN

BEGIN

SELECT salesrep_id

INTO ln_salesrep_id

FROM ra_salesreps_all

WHERE salesrep_number=val_custrec.primary_sales_person_number;

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Sales Rep:'||val_custrec.primary_sales_person_number||'Not Defined' ;

fnd_file.put_line (fnd_file.log,'No records found for Sales person:'||val_custrec.primary_sales_person_number);

WHEN TOO_MANY_ROWS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'More Records found forSales Rep:'||val_custrec.primary_sales_person_number ;

fnd_file.put_line (fnd_file.log,'More than one record found for Sales person:'||val_custrec.primary_sales_person_number);

WHEN OTHERS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Sales Rep:'||val_custrec.primary_sales_person_number||'Other error'||SQLERRM;

fnd_file.put_line (fnd_file.log,'Other error for Sales Rep:'||val_custrec.primary_sales_person_number||SQLERRM );

END;

END IF;

----Term_id-----------------

IF val_custrec.standard_terms IS NOT NULL

THEN

BEGIN

SELECT term_id

INTO ln_term_id

FROM ra_terms

WHERE name=val_custrec.standard_terms;

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Term:'||val_custrec.standard_terms||'Not Defined' ;

fnd_file.put_line (fnd_file.log,'No records found for Term:'||val_custrec.standard_terms);

WHEN TOO_MANY_ROWS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'More Records found for Term:'||val_custrec.standard_terms ;

fnd_file.put_line (fnd_file.log,'More than one record found for Term:'||val_custrec.standard_terms);

WHEN OTHERS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Term:'||val_custrec.standard_terms||'Other error'||SQLERRM;

fnd_file.put_line (fnd_file.log,'Other error for term:'||val_custrec.standard_terms||SQLERRM );

END;

END IF;

----Collector_id-----------------

IF val_custrec.collector_id IS NOT NULL

THEN

BEGIN

SELECT collector_id

INTO ln_collector_id

FROM ar_collectors

WHERE name=val_custrec.collector_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Colletor:'||val_custrec.collector_id||'Not Defined' ;

fnd_file.put_line (fnd_file.log,'No records found for Collector:'||val_custrec.collector_id);

WHEN TOO_MANY_ROWS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'More Records found for Collector:'||val_custrec.collector_id ;

fnd_file.put_line (fnd_file.log,'More than one record found for Collector:'||val_custrec.collector_id);

WHEN OTHERS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'collector:'||val_custrec.collector_id||'Other error'||SQLERRM;

fnd_file.put_line (fnd_file.log,'Other error for Collector:'||val_custrec.collector_id||SQLERRM );

END;

END IF;

----------------------Profile Class---------------------------

IF val_custrec.customer_profile_class IS NOT NULL

THEN

BEGIN

SELECT profile_class_id

INTO ln_profile_class_id

FROM hz_cust_profile_classes

WHERE name=val_custrec.customer_profile_class;

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'profile_class:'||val_custrec.customer_profile_class||'Not Defined' ;

fnd_file.put_line (fnd_file.log,'No records found for profile_class:'||val_custrec.customer_profile_class);

WHEN TOO_MANY_ROWS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'More Records for profile_class:'||val_custrec.customer_profile_class ;

fnd_file.put_line (fnd_file.log,'More than one record found for profile_class:'||val_custrec.customer_profile_class);

WHEN OTHERS THEN

lc_update_flag :='Y';

lc_error_msg :=lc_error_msg||' '||'Profile:'||val_custrec.customer_profile_class||'Other error'||SQLERRM;

fnd_file.put_line (fnd_file.log,'Other error for Profile:'||val_custrec.customer_profile_class||SQLERRM );

END;

END IF;

------------Site Use Id-------------------------------------------------------------------------

IF val_custrec.bill_to_site_use_id IS NOT NULL

THEN

BEGIN

SELECT hsua.site_use_id

INTO ln_bill_to_site_use_id

FROM hz_cust_site_uses_all hsua

,hz_party_sites hps

,hz_cust_acct_sites_all hasa

WHERE hps.party_site_id = hasa.party_site_id

AND hasa.cust_acct_site_id= hsua.cust_acct_site_id

AND hps.party_site_number =TO_CHAR(val_custrec.bill_to_site_use_id);

EXCEPTION

WHEN NO_DATA_FOUND THEN

fnd_file.put_line (fnd_file.log,'No site use id for site :'||TO_CHAR(val_custrec.bill_to_site_use_id));

ln_bill_to_site_use_id:= TO_CHAR(val_custrec.bill_to_site_use_id);

WHEN TOO_MANY_ROWS THEN

fnd_file.put_line (fnd_file.log,'More than one site use id for site :'||TO_CHAR(val_custrec.bill_to_site_use_id));

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log,SQLERRM);

END;

-- ELSE

--- ln_bill_to_site_use_id:= TO_CHAR(val_custrec.bill_to_site_use_id);

END IF;

---------------------------Update Staging Table-------------------------------------------------

IF lc_update_flag ='Y' THEN

UPDATE xxavx_customers_stg

SET status_flag='E'

,error_msg=lc_error_msg

WHERE account_number=val_custrec.account_number

ANd location = val_custrec.location;

ELSE

UPDATE xxavx_customers_stg

SET status_flag='V'

WHERE account_number=val_custrec.account_number

ANd location = val_custrec.location;

END IF;

END LOOP;

END CUSTOMER_VALIDATE; --Validation Procedure

----------------------------------Validation Procedure End---------------------------

----------------------------------Load Procedure Start---------------------------

PROCEDURE CUSTOMER_LOAD(

XC_ERRBUF OUT VARCHAR2

,XN_RETCODE OUT NUMBER

)

---Variables declaration------------

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_cust_profile_amt_rec hz_customer_profile_v2pub. cust_profile_amt_rec_type;

p_create_profile_amt VARCHAR2(240);

p_create_person_rec hz_party_v2pub.person_rec_type;

p_person_rec hz_party_v2pub.person_rec_type;

-- party_rec hz_party_v2pub.party_rec;

-- party_rec hz_cust_account_v2pub.party_rec;

p_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;

p_cr_cust_acc_role_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;

p_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;

p_phone_rec hz_contact_point_v2pub.phone_rec_type;

p_edi_rec_type hz_contact_point_v2pub.edi_rec_type;

p_email_rec_type hz_contact_point_v2pub.email_rec_type;

p_telex_rec_type hz_contact_point_v2pub.telex_rec_type;

p_web_rec_type hz_contact_point_v2pub.web_rec_type;

xn_contact_point_id NUMBER:=0;

xn_cust_account_id NUMBER;

xn_org_contact_id NUMBER:=0;

xn_party_rel_id NUMBER:=0;

xn_cust_account_role_id NUMBER:=0;

xc_account_number VARCHAR2 (2000);

xn_party_id NUMBER;

xn_party_number VARCHAR2 (2000);

xn_profile_id NUMBER:=0;

xc_return_status VARCHAR2 (1);

xn_msg_count NUMBER;

xc_msg_data VARCHAR2 (4000);

p_location_rec hz_location_v2pub.location_rec_type;

xn_location_id NUMBER;

p_party_site_rec hz_party_site_v2pub.party_site_rec_type;

xn_party_site_id NUMBER;

xc_party_site_number VARCHAR2 (2000);

p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;

xn_cust_acct_site_id NUMBER;

p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;

xn_site_use_id NUMBER;

l_organization_name VARCHAR2 (240);

pn_org_id NUMBER := apps.fnd_profile.VALUE ('ORG_ID');

ln2_party_id NUMBER:=0;

ln3_location_id NUMBER:=0;

ln4_party_site_id NUMBER:=0;

ln7_party_id NUMBER:=0;

ln8_party_id NUMBER:=0;

ln11_party_id NUMBER:=0;

ln2_cust_account_id NUMBER:=0;

ln5_cust_acct_site_id NUMBER:=0;

ln_count NUMBER:=0;

lc_country_code VARCHAR2(20);

ln_salesrep_id NUMBER;-----6thFeb--------------

ln_bill_to_site_use_id NUMBER;

lno_party_id NUMBER; --added on 16-feb-09

ln_account_id NUMBER;

ln_party_site_id NUMBER;

ln_profile_id NUMBER;

ln_location_id NUMBER;

ln_cust_acct_site_id NUMBER;

ln_site_use_id NUMBER;

ln_cust_account_profile_id NUMBER;

ln_processed_records NUMBER:=0;

ln_cust_acct_profile_amt_id NUMBER;

ln_term_id NUMBER;

ln_collector_id NUMBER;

lc_sitecontact_party_no VARCHAR2(30):=1;

ln_sitecontact_party_id NUMBER;

ln_relationparty_id NUMBER;

ln_profile_class_id NUMBER;

ln_object_version_number NUMBER;

ln_cust_account_role_id NUMBER;

P_object_version_number NUMBER;

ln_increment NUMBER:=0;

lc_status_flag VARCHAR2(2);

lc_err_msg VARCHAR2(240);

lc_party_flag VARCHAR2(2);

lc_Account_flag VARCHAR2(2);

lc_site_flag VARCHAR2(2);

lc_party_site_flag VARCHAR2(2);

lc_loc_flag VARCHAR2(2);

lc_profile_flag VARCHAR2(2);

lc_profile_amount_flag VARCHAR2(2);

lc_error_msg VARCHAR2(4000);

ln_rejected NUMBER:=0;

ln_count_validated NUMBER:=0;

xn_cust_account_profile_id NUMBER;

xn_cust_acct_profile_amt_id NUMBER;

xn_account_number NUMBER:=0;

CURSOR cur_customer

SELECT *

FROM xxavx_customers_stg

WHERE status_flag='V' ;

BEGIN ---1

SELECT count(*)

INTO ln_count_validated

FROM xxavx_customers_stg

WHERE status_flag='V' ;

fnd_file.put_line (fnd_file.log,'No of records Validated :'||ln_count_validated);

FOR v_cust_rec IN cur_customer

LOOP

BEGIN---2

ln_count:=ln_count+1;

fnd_file.put_line (fnd_file.log,'Customer:'||v_cust_rec.organization_name);

fnd_file.put_line (fnd_file.log,'+==============================================================================+');

fnd_file.put_line (fnd_file.log,'Record no:'||ln_count||'processing Starts' );

fnd_file.put_line (fnd_file.log,'+==============================================================================+');

lc_party_flag :='Y';

lc_Account_flag :='Y';

lc_site_flag :='Y';

lc_profile_flag :='Y';

lc_profile_amount_flag :='Y';

lno_party_id :=NULL;

xn_party_id :=NULL;

xn_cust_account_id :=NULL;

xn_location_id :=NULL;

xn_party_site_id :=NULL;

xn_cust_acct_site_id :=NULL;

xn_site_use_id :=NULL;

ln_account_id :=NULL;

ln_party_site_id :=NULL;

ln_profile_id :=NULL;

ln_location_id :=NULL;

ln_term_id :=NULL;

ln_cust_acct_site_id :=NULL;

ln_salesrep_id :=NULL;

ln_collector_id :=NULL;

ln_site_use_id :=NULL;

ln_cust_account_role_id:=0;

ln_cust_account_profile_id :=NULL;

ln_sitecontact_party_id :=NULL;

ln_relationparty_id :=NULL;

ln_bill_to_site_use_id :=0; -----Updated NULL to 0

ln_profile_class_id :=NULL;

ln_cust_acct_profile_amt_id :=NULL;

ln_object_version_number:=NULL;

P_object_version_number :=NULL;

xn_cust_account_profile_id :=NULL;

xn_cust_acct_profile_amt_id:=NULL;

xn_account_number :=NULL;

p_create_profile_amt :=NULL;

p_customer_profile_rec.site_use_id :=NULL;

p_party_site_rec.party_id :=NULL;

p_party_site_rec.location_id :=NULL;

p_cust_acct_site_rec.cust_account_id :=NULL;

p_cust_acct_site_rec.party_site_id :=NULL;

p_cust_acct_site_rec.org_id :=NULL;

lc_error_msg :=NULL;

xc_return_status :=NULL;

xn_msg_count :=0;

xc_msg_data :=NULL;

----------Checking Organization(Party) ------------------------------------

BEGIN

SELECT hzp.party_id

INTO lno_party_id

FROM hz_parties hzp

,hz_party_usg_assignments hpua

WHERE hpua.party_id=hzp.party_id

AND party_name = v_cust_rec.organization_name;

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_party_flag:='N';

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG, SQLERRM);

END;

----------Checking Customer(Account) ------------------------------------

BEGIN

SELECT hca.cust_account_id

INTO ln_account_id

FROM hz_cust_accounts hca

WHERE hca.account_number= v_cust_rec.account_number;

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_account_flag:='N';

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG, SQLERRM);

END;

----------Checking party site ------------------------------------

BEGIN

SELECT hps.party_site_id

INTO ln_party_site_id

FROM hz_party_sites hps

WHERE TO_CHAR(hps.party_site_number)=TO_CHAR(v_cust_rec.location);

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_site_flag:='N';

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG, SQLERRM);

END;

------------------Cust account site--------------------------------------------

BEGIN

SELECT cust_acct_site_id

INTO ln_cust_acct_site_id

FROM hz_cust_acct_sites_all

WHERE party_site_id=ln_party_site_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

fnd_file.put_line (fnd_file.LOG, 'Error for cust account');

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG, SQLERRM);

END;

------------------------Call Party API-------------------------------------------------------------------------------------------------

IF v_cust_rec.organization_name IS NOT NULL AND lc_party_flag ='N' THEN ---Party

p_organization_rec.organization_name := v_cust_rec.organization_name;

p_organization_rec.created_by_module := v_cust_rec.created_by_module;

p_organization_rec.party_rec.party_number := v_cust_rec.party_number;

p_organization_rec.duns_number_c :=v_cust_rec.duns_number;

p_cust_account_rec.account_name := v_cust_rec.account_name;

p_cust_account_rec.created_by_module := v_cust_rec.created_by_module;

p_cust_account_rec.account_number:=v_cust_rec.account_number;

------------------------------------

-- 1. Create a party and an account

------------------------------------

hz_cust_account_v2pub.create_cust_account ('T'

,p_cust_account_rec

,p_organization_rec

,p_customer_profile_rec

,'F'

,xn_cust_account_id

,xc_account_number

,xn_party_id

,xn_party_number

,xn_profile_id

,xc_return_status

,xn_msg_count

,xc_msg_data

);

fnd_file.put_line(fnd_file.LOG,'Org_id :'|| pn_org_id);

fnd_file.put_line (fnd_file.LOG,'2 ');

fnd_file.put_line(fnd_file.LOG,'xn_cust_account_id :'|| xn_cust_account_id);

fnd_file.put_line(fnd_file.LOG,'xc_account_number :'|| xc_account_number);

fnd_file.put_line(fnd_file.LOG,'xn_party_id :'|| xn_party_id);

fnd_file.put_line(fnd_file.LOG,'xn_party_number :'|| xn_party_number);

--- commit;

lno_party_id := xn_party_id;

ln_account_id := xn_cust_account_id;

---

fnd_file.put_line (fnd_file.LOG,SUBSTR ('xc_return_status = ' || xc_return_status,

255

) );

fnd_file.put_line (fnd_file.LOG,'xn_msg_count = ' || TO_CHAR (xn_msg_count));

fnd_file.put_line (fnd_file.LOG,SUBSTR ('xc_msg_data = ' || xc_msg_data, 1, 255));

IF xn_msg_count > 1

THEN

FOR i IN 1 .. xn_msg_count

LOOP

fnd_file.put_line (fnd_file.LOG,

|| '. '

|| SUBSTR

(fnd_msg_pub.get (p_encoded => fnd_api.g_false),

255

)

);

END LOOP;

END IF;

----------Update staging Table-----------------------

IF xc_return_status='E'

THEN

UPDATE xxavx_customers_stg

SET status_flag='E'

,error_msg=error_msg||' '||'Party Not created '||xc_msg_data

WHERE account_number=v_cust_rec.account_number

ANd location = v_cust_rec.location;

END IF;

END IF; ----Party

----

---------------------------------------Call Location,Site,site use API'S---------------------------------------

IF TO_CHAR(v_cust_rec.location) IS NOT NULL

AND lc_site_flag='N'

AND lno_party_id IS NOT NULL

AND ln_account_id IS NOT NULL ---Location

THEN

fnd_file.put_line (fnd_file.LOG,'Site No:'||TO_CHAR(v_cust_rec.location));

------------------------------------

-- 2. Create a physical location

------------------------------------

fnd_file.put_line (fnd_file.LOG,'Location');

p_location_rec.country := v_cust_rec.country;

p_location_rec.state := v_cust_rec.state;

p_location_rec.province := v_cust_rec.province;

-- p_location_rec.county :='BURLINGTON'; ---added for address validation

p_location_rec.address1 := v_cust_rec.address1;

p_location_rec.address2 := v_cust_rec.address2;

p_location_rec.address3 := v_cust_rec.address3;

p_location_rec.address4 := v_cust_rec.address4;

p_location_rec.city := v_cust_rec.city;

p_location_rec.postal_code := v_cust_rec.postal_code;

p_location_rec.created_by_module :=v_cust_rec.created_by_module;

p_location_rec.language :=v_cust_rec.language;

hz_location_v2pub.create_location ('T',

p_location_rec,

xn_location_id,

xc_return_status,

xn_msg_count,

xc_msg_data

);

ln_location_id := xn_location_id;

fnd_file.put_line (fnd_file.LOG,'xc_return_status = ' || xc_return_status);

fnd_file.put_line (fnd_file.LOG,'xn_msg_count = ' || TO_CHAR (xn_msg_count));

fnd_file.put_line (fnd_file.LOG,'xc_msg_data = ' || xc_msg_data);

IF xn_msg_count > 1

THEN

FOR i IN 1 .. xn_msg_count

LOOP

fnd_file.put_line (fnd_file.LOG,

|| '. '

|| fnd_msg_pub.get (p_encoded => fnd_api.g_false));

END LOOP;

END IF;

---------------------------Update Staging Table-------------------------------------------------

IF xc_return_status='E'

THEN

UPDATE xxavx_customers_stg

SET status_flag='E'

,error_msg=error_msg||' '||'Location Not created '||xc_msg_data

WHERE account_number=v_cust_rec.account_number

ANd location = v_cust_rec.location;

END IF;

-- commit;

----------------------------------------------------------------------------------------------------------------

-- 3. Create a party site using party_id from step 1 and location_id from step 2

--------------------------------------------------------------------------------------------------------------------

IF ln_location_id IS NOT NULL THEN --Party site

fnd_file.put_line (fnd_file.LOG,'Party Site');

p_party_site_rec.party_id :=lno_party_id;

p_party_site_rec.location_id :=ln_location_id;

p_party_site_rec.identifying_address_flag :=v_cust_rec.identifying_address_flag;

p_party_site_rec.created_by_module :=v_cust_rec.created_by_module;

p_party_site_rec.party_site_number :=TO_CHAR(v_cust_rec.location);

p_party_site_rec.addressee :=v_cust_rec.addressee;

hz_party_site_v2pub.create_party_site ('T',

p_party_site_rec,

xn_party_site_id,

xc_party_site_number,

xc_return_status,

xn_msg_count,

xc_msg_data

);

ln_party_site_id := xn_party_site_id;

fnd_file.put_line (fnd_file.LOG,'xc_return_status = ' || xc_return_status);

fnd_file.put_line (fnd_file.LOG,'xn_msg_count = ' || TO_CHAR (xn_msg_count));

fnd_file.put_line (fnd_file.LOG,'xc_msg_data = ' || xc_msg_data);

IF xn_msg_count > 1

THEN

FOR i IN 1 .. xn_msg_count

LOOP

fnd_file.put_line (fnd_file.LOG,

|| '. '

|| SUBSTR

(fnd_msg_pub.get (p_encoded => fnd_api.g_false),

255

)

);

END LOOP;

END IF;

---------------------------Update Staging Table-------------------------------------------------

IF xc_return_status='E'

THEN

UPDATE xxavx_customers_stg

SET status_flag='E'

,error_msg=error_msg||' '||'Party Site Not created '||xc_msg_data

WHERE account_number=v_cust_rec.account_number

ANd location = v_cust_rec.location;

END IF;

--commit;

----

fnd_file.put_line (fnd_file.LOG,'Create an account site');

------------------------------------

-- 5. Create an account site using cust_account_id from step 1 and party_site_id from step 3.

------------------------------------

IF ln_party_site_id IS NOT NULL THEN ----Account site

p_cust_acct_site_rec.cust_account_id := ln_account_id;

p_cust_acct_site_rec.party_site_id := ln_party_site_id;

p_cust_acct_site_rec.created_by_module := v_cust_rec.created_by_module;

p_cust_acct_site_rec.org_id:=pn_org_id ; ----------------Orgid

hz_cust_account_site_v2pub.create_cust_acct_site

('T',

p_cust_acct_site_rec,

xn_cust_acct_site_id,

xc_return_status,

xn_msg_count,

xc_msg_data

);

ln_cust_acct_site_id := xn_cust_acct_site_id;

---

fnd_file.put_line (fnd_file.LOG,'xc_return_status = ' || xc_return_status);

fnd_file.put_line (fnd_file.LOG,'xn_msg_count = ' || TO_CHAR (xn_msg_count));

fnd_file.put_line (fnd_file.LOG,'xc_msg_data = ' || xc_msg_data);

IF xn_msg_count > 1

THEN

FOR i IN 1 .. xn_msg_count

LOOP

fnd_file.put_line (fnd_file.LOG,

|| '. '

|| SUBSTR

(fnd_msg_pub.get (p_encoded => fnd_api.g_false),

255

)

);

END LOOP;

END IF;

---------------------------Update Staging Table-------------------------------------------------

IF xc_return_status='E'

THEN

UPDATE xxavx_customers_stg

SET status_flag='E'

,error_msg=error_msg||' '||'Account Site Not created '||xc_msg_data

WHERE account_number=v_cust_rec.account_number

ANd location = v_cust_rec.location;

END IF;

---commit;

----

fnd_file.put_line (fnd_file.LOG,'Create an account site use');

------------------------------------

-- 6. Create an account site use using cust_acct_site_id from step 4

------------------------------------

IF ln_cust_acct_site_id IS NOT NULL THEN ----account site use

IF v_cust_rec.bill_to_site_use_id IS NOT NULL

THEN

BEGIN

SELECT hsua.site_use_id

INTO ln_bill_to_site_use_id

FROM hz_cust_site_uses_all hsua

,hz_party_sites hps

,hz_cust_acct_sites_all hasa

WHERE hps.party_site_id = hasa.party_site_id

AND hasa.cust_acct_site_id= hsua.cust_acct_site_id

AND hps.party_site_number =TO_CHAR(v_cust_rec.bill_to_site_use_id);

EXCEPTION

WHEN NO_DATA_FOUND THEN

fnd_file.put_line (fnd_file.log,'No site use id for site :'||TO_CHAR(v_cust_rec.bill_to_site_use_id));

ln_bill_to_site_use_id:= TO_CHAR(v_cust_rec.bill_to_site_use_id);

WHEN TOO_MANY_ROWS THEN

fnd_file.put_line (fnd_file.log,'More than one site use id for site :'||TO_CHAR(v_cust_rec.bill_to_site_use_id));

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log,SQLERRM);

END;

-- ELSE

--- ln_bill_to_site_use_id:= TO_CHAR(v_cust_rec.bill_to_site_use_id);

END IF;

fnd_file.put_line (fnd_file.log,ln_bill_to_site_use_id);

p_cust_site_use_rec.cust_acct_site_id := ln_cust_acct_site_id;

p_cust_site_use_rec.site_use_code := v_cust_rec.site_use_code; --'BILL_TO';----'BILL_TO';

p_cust_site_use_rec.location := TO_CHAR(v_cust_rec.location);---'TCA';

p_cust_site_use_rec.created_by_module := v_cust_rec.created_by_module;

p_cust_site_use_rec.primary_salesrep_id:=ln_salesrep_id; ---6th Feb-----------------------

p_customer_profile_rec.dunning_letter_set_id:=v_cust_rec.dunning_letters_set_id;

p_cust_site_use_rec.bill_to_site_use_id:= ln_bill_to_site_use_id;

p_cust_site_use_rec.attribute_category:= 'AVX Site Use Details';

p_cust_site_use_rec.attribute1:= v_cust_rec.site_use_attribute_1;

p_cust_site_use_rec.attribute2:= v_cust_rec.site_use_attribute_2;

p_cust_site_use_rec.attribute3:= v_cust_rec.site_use_attribute_3;

p_cust_site_use_rec.attribute4:= v_cust_rec.site_use_attribute_4;

p_cust_site_use_rec.attribute5:= to_char(to_date(v_cust_rec.site_use_attribute_5,'DD-MON-RR'),'MM/DD/YYYY');

p_cust_site_use_rec.attribute6:= v_cust_rec.site_use_attribute_6;

p_cust_site_use_rec.attribute7:= v_cust_rec.site_use_attribute_7;

p_cust_site_use_rec.attribute8:= v_cust_rec.site_use_attribute_8;

p_cust_site_use_rec.attribute9:= v_cust_rec.site_use_attribute_9;

p_cust_site_use_rec.attribute10:= v_cust_rec.site_use_attribute_10;

p_cust_site_use_rec.attribute11:= v_cust_rec.site_use_attribute_11;

p_cust_site_use_rec.attribute12:= v_cust_rec.site_use_attribute_12;

p_cust_site_use_rec.attribute13:= v_cust_rec.site_use_attribute_13;

p_cust_site_use_rec.attribute14:= v_cust_rec.site_use_attribute_14;

p_customer_profile_rec.standard_terms:=ln_term_id;------------ln_term_id; ---ln_term_id v_cust_rec.standard_terms ; commented by nambi (Mahipal Select from RA_TERMS)

p_customer_profile_rec.collector_id:= ln_collector_id; -- v_cust_rec.collector_id; commented by nambi (Mahipal Select from AR_COLLECTORS)

p_customer_profile_rec.risk_code:= substr(v_cust_rec.risk_code,1,1);---- 'LOW';----v_cust_rec.risk_code; ---Commented on 27th Feb v_cust_rec.risk_code;

p_customer_profile_rec.credit_classification:= v_cust_rec.credit_classification; ---;commented on 27th Feb

p_customer_profile_rec.discount_grace_days:=v_cust_rec.discount_grace_days;

hz_cust_account_site_v2pub.create_cust_site_use

('T',

p_cust_site_use_rec,

p_customer_profile_rec,

'',

xn_site_use_id,

xc_return_status,

xn_msg_count,

xc_msg_data

);

ln_site_use_id:= xn_site_use_id;

--- commit;

---

fnd_file.put_line (fnd_file.LOG,SUBSTR ('xc_return_status = ' || xc_return_status,

255

) );

fnd_file.put_line (fnd_file.LOG,'xn_msg_count = ' || TO_CHAR (xn_msg_count));

fnd_file.put_line (fnd_file.LOG,SUBSTR ('xc_msg_data = ' || xc_msg_data, 1, 255));

IF xn_msg_count > 1

THEN

FOR i IN 1 .. xn_msg_count

LOOP

fnd_file.put_line (fnd_file.LOG,

|| '. '

|| SUBSTR

(fnd_msg_pub.get (p_encoded => fnd_api.g_false),

255

)

);

END LOOP;

END IF;

---------------------------Update Staging Table-------------------------------------------------

IF xc_return_status='E'

THEN

UPDATE xxavx_customers_stg

SET status_flag='E'

,error_msg=error_msg||' '||'Account Site Use Not created '||xc_msg_data

WHERE account_number=v_cust_rec.account_number

ANd location = v_cust_rec.location;

END IF;

----Profile--------------------------------------------

IF ln_site_use_id IS NOT NULL THEN -----------Profile

IF v_cust_rec.site_use_code='BILL_TO' --- Bill_to

THEN

fnd_file.put_line (fnd_file.LOG,'Profile');

-----Sales Rep-------------------------

IF v_cust_rec.primary_sales_person_number IS NOT NULL

THEN

BEGIN

SELECT salesrep_id

INTO ln_salesrep_id

FROM ra_salesreps_all

WHERE salesrep_number=v_cust_rec.primary_sales_person_number;

EXCEPTION

WHEN NO_DATA_FOUND THEN

fnd_file.put_line (fnd_file.log,'No records found for Sales person:'||v_cust_rec.primary_sales_person_number);

WHEN TOO_MANY_ROWS THEN

fnd_file.put_line (fnd_file.log,'More than one record found for Sales person:'||v_cust_rec.primary_sales_person_number);

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log,SQLCODE);

fnd_file.put_line (fnd_file.log,SQLERRM);

END;

END IF;

----Term_id-----------------

IF v_cust_rec.standard_terms IS NOT NULL

THEN

BEGIN

SELECT term_id

INTO ln_term_id

FROM ra_terms

WHERE name=v_cust_rec.standard_terms;

EXCEPTION

WHEN NO_DATA_FOUND THEN

fnd_file.put_line (fnd_file.log,'No term Id found for :'||v_cust_rec.standard_terms);

WHEN TOO_MANY_ROWS THEN

fnd_file.put_line (fnd_file.log,'More than one term Id found for :'||v_cust_rec.standard_terms);

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log,SQLCODE);

fnd_file.put_line (fnd_file.log,SQLERRM);

END;

END IF;

----Collector_id-----------------

IF v_cust_rec.collector_id IS NOT NULL

THEN

BEGIN

SELECT collector_id

INTO ln_collector_id

FROM ar_collectors

WHERE name=v_cust_rec.collector_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

fnd_file.put_line (fnd_file.log,'No Collector found :'||v_cust_rec.collector_id);

WHEN TOO_MANY_ROWS THEN

fnd_file.put_line (fnd_file.log,'More than one Collector found for :'||v_cust_rec.collector_id);

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log,SQLCODE);

fnd_file.put_line (fnd_file.log,SQLERRM);

END;

END IF;

----------------------Profile Class---------------------------

IF v_cust_rec.customer_profile_class IS NOT NULL

THEN

BEGIN

SELECT profile_class_id

INTO ln_profile_class_id

FROM hz_cust_profile_classes

WHERE name=v_cust_rec.customer_profile_class;

EXCEPTION

WHEN NO_DATA_FOUND THEN

fnd_file.put_line (fnd_file.log,'Customer profile not defined :'||v_cust_rec.customer_profile_class);

WHEN TOO_MANY_ROWS THEN

fnd_file.put_line (fnd_file.log,'More than one profile defined for :'||v_cust_rec.customer_profile_class);

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log,SQLCODE);

fnd_file.put_line (fnd_file.log,SQLERRM);

END;

END IF;

----------------------------

p_customer_profile_rec.cust_account_id := ln_account_id;

p_customer_profile_rec.site_use_id :=ln_site_use_id;

---l_customer_profile_rec.statement_cycle_id := p_statment_cycle_id;

p_customer_profile_rec.created_by_module := v_cust_rec.created_by_module;

p_customer_profile_rec.credit_checking:=v_cust_rec.profile_credit_checking_flag;

p_customer_profile_rec.profile_class_id:=ln_profile_class_id;

-----------------

p_customer_profile_rec.standard_terms:=ln_term_id;

p_customer_profile_rec.collector_id:= ln_collector_id;

p_customer_profile_rec.risk_code:= substr(v_cust_rec.risk_code,1,1);

p_customer_profile_rec.credit_classification:= v_cust_rec.credit_classification;

p_customer_profile_rec.discount_grace_days:=v_cust_rec.discount_grace_days;

--------------------

p_customer_profile_rec.attribute_category :='AVX Credit Profile Details';---------v_cust_rec.creditprofile_attribcategory;

p_customer_profile_rec.attribute1:=to_char(to_date(v_cust_rec.credit_profile_attribute_1,'DD-MON-RR'),'MM/DD/YYYY');

p_customer_profile_rec.attribute2:=to_char(to_date(v_cust_rec.credit_profile_attribute_2,'DD-MON-RR'),'MM/DD/YYYY');

p_customer_profile_rec.attribute3:=v_cust_rec.credit_profile_attribute_3;

p_customer_profile_rec.attribute4:=v_cust_rec.credit_profile_attribute_4;

p_customer_profile_rec.attribute5:=to_char(to_date(v_cust_rec.credit_profile_attribute_5,'DD-MON-RR'),'MM/DD/YYYY');

p_customer_profile_rec.attribute6:=v_cust_rec.credit_profile_attribute_6;

p_customer_profile_rec.attribute7:=v_cust_rec.credit_profile_attribute_7;

p_customer_profile_rec.attribute8:=v_cust_rec.credit_profile_attribute_8;

p_customer_profile_rec.attribute9:=v_cust_rec.credit_profile_attribute_9;

p_customer_profile_rec.attribute10:=to_char(to_date(v_cust_rec.credit_profile_attribute_10,'DD-MON-RR'),'MM/DD/YYYY');

p_customer_profile_rec.attribute11:=v_cust_rec.credit_profile_attribute_11;

p_customer_profile_rec.attribute12:=v_cust_rec.credit_profile_attribute_12;

p_customer_profile_rec.attribute13:=v_cust_rec.credit_profile_attribute_13;

p_customer_profile_rec.attribute14:=v_cust_rec.credit_profile_attribute_14;

hz_customer_profile_v2pub.create_customer_profile ('T'

,p_customer_profile_rec

,p_create_profile_amt

,xn_cust_account_profile_id

,xc_return_status

,xn_msg_count

,xc_msg_data

);

-- commit;

ln_cust_account_profile_id:=xn_cust_account_profile_id;

fnd_file.put_line (fnd_file.LOG,'xn_cust_account_profile_id:'||xn_cust_account_profile_id);

fnd_file.put_line (fnd_file.LOG,SUBSTR ('xc_return_status = ' || xc_return_status,

255

) );

fnd_file.put_line (fnd_file.LOG,'xn_msg_count = ' || TO_CHAR (xn_msg_count));

fnd_file.put_line (fnd_file.LOG,SUBSTR ('xc_msg_data = ' || xc_msg_data, 1, 255));

IF xn_msg_count > 1

THEN

FOR i IN 1 .. xn_msg_count

LOOP

fnd_file.put_line (fnd_file.LOG,

|| '. '

|| SUBSTR

(fnd_msg_pub.get (p_encoded => fnd_api.g_false),

255

)

);

END LOOP;

END IF;

---------------------------Update Staging Table-------------------------------------------------

IF xc_return_status='E'

THEN

UPDATE xxavx_customers_stg

SET status_flag='E'

,error_msg=error_msg||' '||'Profile Not created '||xc_msg_data

WHERE account_number=v_cust_rec.account_number

ANd location = v_cust_rec.location;

END IF;

----Profile Amount--------------------------------------------

--- IF v_cust_rec.currency_code='USD'

--- THEN

BEGIN

SELECT cust_acct_profile_amt_id

,object_version_number

INTO ln_cust_acct_profile_amt_id

,ln_object_version_number

FROM hz_cust_profile_amts

WHERE cust_account_id=ln_account_id

AND site_use_id=ln_site_use_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

lc_profile_amount_flag:='N';

fnd_file.put_line (fnd_file.log,'Customer profile amounts not defined :');

WHEN TOO_MANY_ROWS THEN

fnd_file.put_line (fnd_file.log,'More than one profile amounts defined for :');

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log,SQLCODE);

fnd_file.put_line (fnd_file.log,SQLERRM);

END;

IF lc_profile_amount_flag='Y' --Profile Amount

THEN

fnd_file.put_line (fnd_file.log,'Update Profile Amount');

p_cust_profile_amt_rec.CUST_ACCT_PROFILE_AMT_ID:=ln_cust_acct_profile_amt_id;

P_OBJECT_VERSION_NUMBER :=ln_object_version_number;

p_cust_profile_amt_rec.created_by_module := v_cust_rec.created_by_module;

p_cust_profile_amt_rec.TRX_CREDIT_LIMIT:=v_cust_rec.PROFILE_OVERALL_CREDIT_LIMIT;

p_cust_profile_amt_rec.OVERALL_CREDIT_LIMIT:=v_cust_rec.PROFILE_OVERALL_CREDIT_LIMIT;

p_cust_profile_amt_rec.cust_account_profile_id:=ln_cust_account_profile_id;

p_cust_profile_amt_rec.currency_code:=v_cust_rec.currency_code;

p_cust_profile_amt_rec.cust_account_id:=ln_account_id;

p_cust_profile_amt_rec.site_use_id:= ln_site_use_id;

p_cust_profile_amt_rec.attribute_category:='AVX Profile Amounts Details';

p_cust_profile_amt_rec.attribute1:=to_char(to_date(v_cust_rec.profile_amounts_attribute_1,'DD-MON-RR'),'MM/DD/YYYY');

p_cust_profile_amt_rec.attribute2:=v_cust_rec.profile_amounts_attribute_2;

p_cust_profile_amt_rec.attribute3:=to_char(to_date(v_cust_rec.profile_amounts_attribute_3,'DD-MON-RR'),'MM/DD/YYYY');

p_cust_profile_amt_rec.attribute4:=v_cust_rec.profile_amounts_attribute_4;

p_cust_profile_amt_rec.attribute5:=v_cust_rec.profile_amounts_attribute_5;

p_cust_profile_amt_rec.attribute6:=v_cust_rec.profile_amounts_attribute_6;

p_cust_profile_amt_rec.attribute7:=v_cust_rec.profile_amounts_attribute_7;

p_cust_profile_amt_rec.attribute8:=v_cust_rec.profile_amounts_attribute_8;

p_cust_profile_amt_rec.attribute9:=v_cust_rec.profile_amounts_attribute_9;

p_cust_profile_amt_rec.attribute10:=to_char(to_date(v_cust_rec.profile_amounts_attribute_10,'DD-MON-RR'),'MM/DD/YYYY');

p_cust_profile_amt_rec.attribute11:=v_cust_rec.profile_amounts_attribute_11;

hz_customer_profile_v2pub.update_cust_profile_amt ( 'T'

,p_cust_profile_amt_rec

,p_object_version_number

,xc_return_status

,xn_msg_count

,xc_msg_data

);

-- commit;

fnd_file.put_line (fnd_file.LOG,'p_object_version_number:'||p_object_version_number);

fnd_file.put_line (fnd_file.LOG,SUBSTR ('xc_return_status = ' || xc_return_status,

255

) );

fnd_file.put_line (fnd_file.LOG,'xn_msg_count = ' || TO_CHAR (xn_msg_count));

fnd_file.put_line (fnd_file.LOG,SUBSTR ('xc_msg_data = ' || xc_msg_data, 1, 255));

IF xn_msg_count > 1

THEN

FOR i IN 1 .. xn_msg_count

LOOP

fnd_file.put_line (fnd_file.LOG,

|| '. '

|| SUBSTR

(fnd_msg_pub.get (p_encoded => fnd_api.g_false),

255

)

);

END LOOP;

END IF;

---------------------------Update Staging Table-------------------------------------------------

IF xc_return_status='E'

THEN

UPDATE xxavx_customers_stg

SET status_flag='E'

,error_msg=error_msg||' '||'Profile amounts Not updated '||xc_msg_data

WHERE account_number=v_cust_rec.account_number

ANd location = v_cust_rec.location;

END IF;

ELSE

fnd_file.put_line (fnd_file.LOG,'Create Profile amount');