Creating Customers
Through API
Createcreate table XX_CUSTOMER_MIGRATION
( OPERATING_UNIT VARCHAR2(100)
,CUSTOMER_NAME NOT NULL VARCHAR2(240)
,CUSTOMER_NUMBER NOT NULL VARCHAR2(30)
,CITY VARCHAR2(100)
,COUNTRY VARCHAR2(100)
,ADDRESS1 VARCHAR2(200)
,PAYMENT_TERMS VARCHAR2(50)
,KNOWN_AS VARCHAR2(311)
,MESSAGE VARCHAR2(240)
,STATUS VARCHAR2(2));
CREATE OR REPLACE PROCEDURE CUSTOMER_MIGRATION (
errbuf OUT VARCHAR2 , retcode OUT NUMBER)
AS
p_location_rec hz_location_v2pub.location_rec_type;
x_location_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
v_organization_rec hz_party_v2pub.organization_rec_type;
v_customer_account_rec hz_cust_account_v2pub.cust_account_rec_type;
v_party_rec hz_party_v2pub.party_rec_type;
v_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
v_location_rec hz_location_v2pub.location_rec_type;
v_party_site_rec hz_party_site_v2pub.party_site_rec_type;
v_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
v_customer_profile_amt hz_customer_profile_v2pub.cust_profile_amt_rec_type;
v_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
v_customer_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
v_customer_ship_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
v_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
v_phone_rec hz_contact_point_v2pub.phone_rec_type;
v_email_rec hz_contact_point_v2pub.email_rec_type;
v_person_rec hz_party_v2pub.person_rec_type;
v_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
x_party_site_id NUMBER;
x_party_site_number VARCHAR2(2000);
v_party_tax_profile_id NUMBER;
x_cust_acct_site_id NUMBER;
x_site_use_id NUMBER;
x_contact_point_id NUMBER;
x_cust_account_id NUMBER;
x_account_number VARCHAR2(2000);
x_party_id NUMBER;
x_party_number VARCHAR2(2000);
x_profile_id NUMBER;
l_party_id NUMBER;
l_success NUMBER;
l_count NUMBER;
l_cust_acc_id NUMBER;
v_count NUMBER;
v_customer_account_id NUMBER
x_cust_account_profile_id NUMBER;
v_cust_act_prof_amt_id NUMBER;
v_org_id NUMBER;
v_gl_id_rec NUMBER;
x_object_version_number NUMBER;
v_term_id number;
v_application_id NUMBER;
v_profile_class_id hz_cust_profile_classes.profile_class_id%TYPE;
-- Get all the customer records from staging table.
CURSOR cur_rec
IS
SELECT *
FROM XX_CUSTOMER_MIGRATION XX
WHERE status is null
AND NOT EXISTS
(SELECT 1 FROM HZ_PARTIES HP WHERE TRIM(PARTY_NAME) = TRIM(customer_name)
)
AND ROWNUM <=500
ORDER BY XX.customer_name;
CURSOR cur_ou (p_operating_unit VARCHAR2)
IS
SELECT *
FROM hr_operating_units
WHERE upper(trim(name))=upper(trim(p_operating_unit));
BEGIN
l_success:=1;
mo_global.init('AR');
FOR rec_cur IN cur_rec
LOOP
Select application_id into v_application_id
from fnd_application_vl where
upper(application_name) = upper('Receivables');
BEGIN
SELECT COUNT(1)
INTO v_count
FROM hz_parties
WHERE party_name = TRIM(rec_cur.customer_name);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(FND_FILE.LOG,'Error while get Party Name');
RAISE;
END;
IF v_count = 0 THEN -- Party Name creation
fnd_file.put_line(FND_FILE.LOG,'count=0');
IF rec_cur.customer_name IS NOT NULL THEN -- Party Name not Null
BEGIN
SELECT profile_class_id INTO v_profile_class_id FROM hz_cust_profile_classes;
--WHERE name = rec_cur.profile_class;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_profile_class_id := 0;
WHEN OTHERS THEN
v_profile_class_id := 0;
END;
------------------------------------
-- 1. Create a party and an account
------------------------------------
v_organization_rec.organization_name := TRIM(rec_cur.customer_name);
-- v_organization_rec.party_rec.orig_system_reference := REC_CUR.CUST_CODE ;
v_organization_rec.created_by_module := 'TCA_V1_API';
v_organization_rec.known_as := rec_cur.known_as;
--v_customer_account_rec.account_number := REC_CUR.cust_name;
v_customer_account_rec.account_number := REC_CUR.customer_number;
--v_customer_account_rec.account_name := rec_cur.cust_type_eng;
v_customer_account_rec.account_name := rec_cur.customer_name;
v_customer_account_rec.application_id := v_application_id;
-- v_customer_account_rec.customer_class_code := rec_cur.Classification;
-- v_customer_account_rec.orig_system_reference := REC_CUR.CUST_CODE ;
v_customer_account_rec.created_by_module :='TCA_V1_API';
v_customer_profile_rec.profile_class_id := v_profile_class_id;
v_customer_profile_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_v2pub.create_cust_account ( p_init_msg_list =>FND_API.G_FALSE, p_cust_account_rec => v_customer_account_rec, -- Customer Account Record
p_organization_rec => v_organization_rec, -- Party Organization Record
p_customer_profile_rec => v_customer_profile_rec, -- Customer Profile Record
p_create_profile_amt => fnd_api.g_true,
x_cust_account_id => l_cust_acc_id, x_account_number => x_account_number,
x_party_id => x_party_id, x_party_number => x_party_number,
x_profile_id => x_profile_id, x_return_status =>x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data );
COMMIT;
IF x_return_status = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XXGG_AR_CUST_INTFC_STG
SET MESSAGE='Create a party and an account: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
fnd_file.put_line(FND_FILE.LOG,'customer account id '||l_cust_acc_id);
fnd_file.put_line(FND_FILE.LOG,'customer account number '||x_account_number);
fnd_file.put_line(FND_FILE.LOG,'customer party id '||x_party_id);
/*SELECT cust_account_profile_id
INTO x_cust_account_profile_id
FROM hz_customer_profiles
WHERE cust_account_id = l_cust_acc_id
AND site_use_id IS NULL;
v_customer_profile_amt.cust_account_profile_id := x_cust_account_profile_id;*/
v_customer_profile_amt.cust_account_id := l_cust_acc_id;
--v_customer_profile_amt.CUST_ACCT_PROFILE_AMT_ID := l_cust_acc_id;
-- v_customer_profile_amt.currency_code := 'SAR';
--- v_customer_profile_amt.overall_credit_limit := rec_cur.cust_credit_limit;
-- v_customer_profile_amt.created_by_module := 'TCA_V1_API';
/*hz_customer_profile_v2pub.create_cust_profile_amt
( p_init_msg_list => 'T' , p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => v_customer_profile_amt, x_cust_acct_profile_amt_id => v_cust_act_prof_amt_id,
x_return_status =>x_return_status, x_msg_count => x_msg_count, x_msg_data =>x_msg_data );
*/
/*dbms_output.put_line('Account : '|| X_RETURN_STATUS||' '||rec_cur.customer_number ||' '||x_msg_data);
IF X_RETURN_STATUS = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XXGG_AR_CUST_INTFC_STG
SET MESSAGE='Create a party and an account: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;*/
--commit;
--IF rec_cur.site_address IS NOT NULL THEN -- Region not Null
IF rec_cur.customer_number IS NOT NULL THEN -- Region not Null
------------------------------------
-- 2. Create a physical location
------------------------------------
fnd_file.put_line(FND_FILE.LOG,'before create location');
v_location_rec.country := rec_cur.country; --'SA';
--v_location_rec.address1 := rec_cur.site_address;
v_location_rec.address1 := rec_cur.address1;
--v_location_rec.address2 := rec_cur.site_Address2;
v_location_rec.city := rec_cur.city;
v_location_rec.application_id := v_application_id;
v_location_rec.created_by_module := 'TCA_V1_API';
hz_location_v2pub.create_location(P_INIT_MSG_LIST => 'T', P_LOCATION_REC => v_location_rec,
X_LOCATION_ID => X_LOCATION_ID,
X_RETURN_STATUS =>X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA);
commit;
IF X_RETURN_STATUS = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE='error when create location: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
------------------------------------
-- 3. Create a party site using party_id from step 1 and location_id from step 2
------------------------------------
fnd_file.put_line(FND_FILE.LOG,'location id'||x_location_id);
fnd_file.put_line(FND_FILE.LOG,'customer party id '||x_party_id);
IF x_location_id IS NOT NULL THEN -- Location ID not Null
v_party_site_rec.party_id := x_party_id;
v_party_site_rec.location_id := x_location_id;
v_party_site_rec.application_id := v_application_id;
v_party_site_rec.party_site_number := NULL;
v_party_site_rec.identifying_address_flag := 'Y';
v_party_site_rec.created_by_module := 'TCA_V1_API';
hz_party_site_v2pub.create_party_site( P_INIT_MSG_LIST => 'T',
P_PARTY_SITE_REC => v_party_site_rec,
X_PARTY_SITE_ID => X_PARTY_SITE_ID,
X_PARTY_SITE_NUMBER => X_PARTY_SITE_NUMBER,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
commit;
IF X_RETURN_STATUS = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE=' error when create party site: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
-- commit;
------------------------------------
-- 4. Create an account site using cust_account_id from step 1 and party_site_id from step 3.
------------------------------------
IF x_party_site_id IS NOT NULL THEN -- Party Site ID not null
FOR rec_cur_ou IN cur_ou(rec_cur.operating_unit)
LOOP
SELECT term_id
INTO v_term_id
FROM apps.ra_terms
WHERE UPPER (name) = UPPER(rec_cur.Payment_terms)
AND ROWNUM = 1;
v_org_id := rec_cur_ou.organization_id;
v_cust_acct_site_rec.cust_account_id := l_cust_acc_id;
v_cust_acct_site_rec.party_site_id := x_party_site_id;
v_cust_acct_site_rec.application_id := v_application_id;
v_cust_acct_site_rec.org_id := v_org_id;
fnd_file.put_line(FND_FILE.LOG,'organization id '||v_org_id);
-- p_cust_acct_site_rec.orig_system_reference := p_cust_acct_site_id;
v_cust_acct_site_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_acct_site(P_INIT_MSG_LIST => 'T', P_CUST_ACCT_SITE_REC => v_cust_acct_site_rec, X_CUST_ACCT_SITE_ID => X_CUST_ACCT_SITE_ID, X_RETURN_STATUS => X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA);
fnd_file.put_line(FND_FILE.LOG,'customer account site id '||x_cust_acct_site_id);
commit;
IF x_cust_acct_site_id IS NOT NULL THEN -- Customer Account Site ID is not null
-- v_gl_id_rec := NULL;
v_customer_ship_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
v_customer_ship_site_use_rec.site_use_code := 'SHIP_TO';
-- v_customer_site_use_rec.gl_id_rec := 7006;
v_customer_ship_site_use_rec.org_id := v_org_id;
v_customer_ship_site_use_rec.payment_term_id := v_term_id;
v_customer_ship_site_use_rec.application_id := v_application_id;
v_customer_ship_site_use_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_ship_site_use_rec, v_customer_profile_rec, '', '', x_site_use_id, x_return_status, x_msg_count, x_msg_data);
commit;
v_customer_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
v_customer_site_use_rec.site_use_code := 'BILL_TO';
v_customer_site_use_rec.payment_term_id := v_term_id;
-- v_customer_site_use_rec.gl_id_rec := rec_cur.ar_receivable_account;--7006;
v_customer_site_use_rec.org_id := v_org_id;
v_customer_site_use_rec.application_id := v_application_id;
v_customer_site_use_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_site_use_rec, v_customer_profile_rec, '', '', x_site_use_id, x_return_status, x_msg_count, x_msg_data);
commit;
ELSE
ROLLBACK;
END IF;--X_CUST_ACCT_SITE_ID
END LOOP;
COMMIT;
------------------------------------
-- 5. Create an account site use using cust_acct_site_id from step 4 and site_use_code='BILL_TO'
------------------------------------
ELSE
ROLLBACK;
END IF;--X_PARTY_SITE_ID
ELSE
ROLLBACK;
END IF;--LOCATION ID
ELSE
ROLLBACK;
END IF;-- Region not Null
ELSE
ROLLBACK;
END IF;-- Party Name not Null
ELSE
/* ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE='Customer aleady Exist',
STATUS='E'
WHERE INTERFACE_ID=REC_CUR.INTERFACE_ID;
*/
IF v_count > 0 THEN
SELECT party_id
INTO x_party_id
FROM hz_parties
WHERE party_name = TRIM(rec_cur.customer_name);
--x_PARTY_ID := 38084;
SELECT term_id
INTO v_term_id
FROM apps.ra_terms
WHERE UPPER (name) = UPPER (rec_cur.Payment_terms)
AND ROWNUM = 1;
IF rec_cur.site_address1 IS NOT NULL and status IS NOT NULL THEN -- Region not Null
------------------------------------
-- 2. Create a physical location
------------------------------------
v_location_rec.country := rec_cur.country; --'SA';
v_location_rec.address1 := rec_cur.address1;
--v_location_rec.address2 := rec_cur.site_address2;
v_location_rec.city := rec_cur.city;
v_location_rec.application_id := v_application_id;
v_location_rec.created_by_module := 'TCA_V1_API';
HZ_LOCATION_V2PUB.create_location(p_init_msg_list => 'T', p_location_rec => v_location_rec, x_location_id => x_location_id, x_return_status =>x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Create a party location');
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(FND_FILE.LOG,'x_location_id: ' || x_location_id);
fnd_file.put_line(FND_FILE.LOG,'l_return_status: ' || x_return_status);
fnd_file.put_line(FND_FILE.LOG,'l_msg_count: ' || x_msg_count);
fnd_file.put_line(FND_FILE.LOG,'l_msg_data: ' || x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
IF x_return_status = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE='error when create location: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
--commit;
COMMIT;
------------------------------------
-- 3. Create a party site using party_id from step 1 and location_id from step 2
------------------------------------
IF x_location_id IS NOT NULL THEN -- Location ID not Null
v_party_site_rec.party_id := x_party_id;
v_party_site_rec.location_id := x_location_id;
v_party_site_rec.application_id := v_application_id;
v_party_site_rec.party_site_number := NULL;
v_party_site_rec.identifying_address_flag := 'Y';
v_party_site_rec.created_by_module := 'TCA_V1_API';
hz_party_site_v2pub.create_party_site(p_init_msg_list => 'T', p_party_site_rec => v_party_site_rec
,x_party_site_id => x_party_site_id
,x_party_site_number => x_party_site_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(FND_FILE.LOG,'X_PARTY_SITE_ID: ' || X_PARTY_SITE_ID);
fnd_file.put_line(FND_FILE.LOG,'X_PARTY_SITE_NUMBER: ' || X_PARTY_SITE_NUMBER);
fnd_file.put_line(FND_FILE.LOG,'l_return_status: ' || x_return_status);
fnd_file.put_line(FND_FILE.LOG,'l_msg_count: ' || x_msg_count);
fnd_file.put_line(FND_FILE.LOG,'l_msg_data: ' || x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(FND_FILE.LOG,'Create a party site');
IF X_RETURN_STATUS = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE=' error when create party site: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
-- commit;
------------------------------------
-- 4. Create an account site using cust_account_id from step 1 and party_site_id from step 3.
-- LOOP
------------------------------------
IF x_party_site_id IS NOT NULL THEN -- Party Site ID not null
FOR rec_cur_ou IN cur_ou(rec_cur.operating_unit)
LOOP
SELECT cust_account_id
INTO l_cust_acc_id
FROM hz_cust_accounts
WHERE party_id = x_party_id;
v_org_id := rec_cur_ou.organization_id;
v_cust_acct_site_rec.party_site_id := x_party_site_id;
v_cust_acct_site_rec.org_id := v_org_id;
v_cust_acct_site_rec.application_id := v_application_id;
-- p_cust_acct_site_rec.orig_system_reference := p_cust_acct_site_id;
v_cust_acct_site_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_acct_site(p_init_msg_list => 'T'
, p_cust_acct_site_rec => v_cust_acct_site_rec
, x_cust_acct_site_id => x_cust_acct_site_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Create a party account site');
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(FND_FILE.LOG,'x_cust_acct_site_id : ' || x_cust_acct_site_id );
fnd_file.put_line(FND_FILE.LOG,'l_return_status: ' || x_return_status);
fnd_file.put_line(FND_FILE.LOG,'l_msg_count: ' || x_msg_count);
fnd_file.put_line(FND_FILE.LOG,'l_msg_data: ' || x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
IF x_cust_acct_site_id IS NOT NULL THEN -- Customer Account Site ID is not null
--v_gl_id_rec := NULL;
v_customer_ship_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
v_customer_ship_site_use_rec.site_use_code := 'SHIP_TO';
-- v_customer_site_use_rec.gl_id_rec := 7006;
v_customer_ship_site_use_rec.org_id := v_org_id;
v_customer_ship_site_use_rec.application_id := v_application_id;
v_customer_ship_site_use_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_ship_site_use_rec, v_customer_profile_rec, '', '', X_SITE_USE_ID, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
fnd_file.put_line(FND_FILE.LOG,'Create a party account site11');
v_customer_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
v_customer_site_use_rec.site_use_code := 'BILL_TO';
-- v_customer_site_use_rec.gl_id_rec := rec_cur.ar_receivable_account;--7006;
v_customer_site_use_rec.org_id := v_org_id;
v_customer_site_use_rec.payment_term_id := v_term_id;
v_customer_site_use_rec.application_id := v_application_id;
v_customer_site_use_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_site_use_rec, v_customer_profile_rec, '', '', x_site_use_id, x_return_status, x_msg_count, x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Create a party account site');
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(fnd_file.LOG,'x_cust_acct_site_id : ' || x_cust_acct_site_id );
fnd_file.put_line(FND_FILE.LOG,'l_return_status: ' || x_return_status);
fnd_file.put_line(FND_FILE.LOG,'l_msg_count: ' || x_msg_count);
fnd_file.put_line(FND_FILE.LOG,'l_msg_data: ' || x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
ELSE
ROLLBACK;
END IF;--X_CUST_ACCT_SITE_ID
END LOOP;
COMMIT;
------------------------------------
-- 6. Create a definition contact
------------------------------------
v_contact_point_rec.created_by_module := 'TCA_V1_API';
------------------------------------
-- 5. Create an account site use using cust_acct_site_id from step 4 and site_use_code='BILL_TO'
------------------------------------
COMMIT;
ELSE
ROLLBACK;
END IF;--X_PARTY_SITE_ID
ELSE
ROLLBACK;
END IF;--LOCATION ID
ELSE
ROLLBACK;
END IF;-- Region not Null
ELSE
ROLLBACK;
END IF;
END IF;-- Party Name creation
COMMIT;
IF l_success = 1 THEN
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE ='Loaded Successfully',
status ='S'
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
END IF;
COMMIT;
END LOOP;
END customer_migration;
Through API
Createcreate table XX_CUSTOMER_MIGRATION
( OPERATING_UNIT VARCHAR2(100)
,CUSTOMER_NAME NOT NULL VARCHAR2(240)
,CUSTOMER_NUMBER NOT NULL VARCHAR2(30)
,CITY VARCHAR2(100)
,COUNTRY VARCHAR2(100)
,ADDRESS1 VARCHAR2(200)
,PAYMENT_TERMS VARCHAR2(50)
,KNOWN_AS VARCHAR2(311)
,MESSAGE VARCHAR2(240)
,STATUS VARCHAR2(2));
CREATE OR REPLACE PROCEDURE CUSTOMER_MIGRATION (
errbuf OUT VARCHAR2 , retcode OUT NUMBER)
AS
p_location_rec hz_location_v2pub.location_rec_type;
x_location_id NUMBER;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
v_organization_rec hz_party_v2pub.organization_rec_type;
v_customer_account_rec hz_cust_account_v2pub.cust_account_rec_type;
v_party_rec hz_party_v2pub.party_rec_type;
v_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
v_location_rec hz_location_v2pub.location_rec_type;
v_party_site_rec hz_party_site_v2pub.party_site_rec_type;
v_party_site_use_rec hz_party_site_v2pub.party_site_use_rec_type;
v_customer_profile_amt hz_customer_profile_v2pub.cust_profile_amt_rec_type;
v_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
v_customer_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
v_customer_ship_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
v_contact_point_rec hz_contact_point_v2pub.contact_point_rec_type;
v_phone_rec hz_contact_point_v2pub.phone_rec_type;
v_email_rec hz_contact_point_v2pub.email_rec_type;
v_person_rec hz_party_v2pub.person_rec_type;
v_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
x_party_site_id NUMBER;
x_party_site_number VARCHAR2(2000);
v_party_tax_profile_id NUMBER;
x_cust_acct_site_id NUMBER;
x_site_use_id NUMBER;
x_contact_point_id NUMBER;
x_cust_account_id NUMBER;
x_account_number VARCHAR2(2000);
x_party_id NUMBER;
x_party_number VARCHAR2(2000);
x_profile_id NUMBER;
l_party_id NUMBER;
l_success NUMBER;
l_count NUMBER;
l_cust_acc_id NUMBER;
v_count NUMBER;
v_customer_account_id NUMBER
x_cust_account_profile_id NUMBER;
v_cust_act_prof_amt_id NUMBER;
v_org_id NUMBER;
v_gl_id_rec NUMBER;
x_object_version_number NUMBER;
v_term_id number;
v_application_id NUMBER;
v_profile_class_id hz_cust_profile_classes.profile_class_id%TYPE;
-- Get all the customer records from staging table.
CURSOR cur_rec
IS
SELECT *
FROM XX_CUSTOMER_MIGRATION XX
WHERE status is null
AND NOT EXISTS
(SELECT 1 FROM HZ_PARTIES HP WHERE TRIM(PARTY_NAME) = TRIM(customer_name)
)
AND ROWNUM <=500
ORDER BY XX.customer_name;
CURSOR cur_ou (p_operating_unit VARCHAR2)
IS
SELECT *
FROM hr_operating_units
WHERE upper(trim(name))=upper(trim(p_operating_unit));
BEGIN
l_success:=1;
mo_global.init('AR');
FOR rec_cur IN cur_rec
LOOP
Select application_id into v_application_id
from fnd_application_vl where
upper(application_name) = upper('Receivables');
BEGIN
SELECT COUNT(1)
INTO v_count
FROM hz_parties
WHERE party_name = TRIM(rec_cur.customer_name);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(FND_FILE.LOG,'Error while get Party Name');
RAISE;
END;
IF v_count = 0 THEN -- Party Name creation
fnd_file.put_line(FND_FILE.LOG,'count=0');
IF rec_cur.customer_name IS NOT NULL THEN -- Party Name not Null
BEGIN
SELECT profile_class_id INTO v_profile_class_id FROM hz_cust_profile_classes;
--WHERE name = rec_cur.profile_class;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_profile_class_id := 0;
WHEN OTHERS THEN
v_profile_class_id := 0;
END;
------------------------------------
-- 1. Create a party and an account
------------------------------------
v_organization_rec.organization_name := TRIM(rec_cur.customer_name);
-- v_organization_rec.party_rec.orig_system_reference := REC_CUR.CUST_CODE ;
v_organization_rec.created_by_module := 'TCA_V1_API';
v_organization_rec.known_as := rec_cur.known_as;
--v_customer_account_rec.account_number := REC_CUR.cust_name;
v_customer_account_rec.account_number := REC_CUR.customer_number;
--v_customer_account_rec.account_name := rec_cur.cust_type_eng;
v_customer_account_rec.account_name := rec_cur.customer_name;
v_customer_account_rec.application_id := v_application_id;
-- v_customer_account_rec.customer_class_code := rec_cur.Classification;
-- v_customer_account_rec.orig_system_reference := REC_CUR.CUST_CODE ;
v_customer_account_rec.created_by_module :='TCA_V1_API';
v_customer_profile_rec.profile_class_id := v_profile_class_id;
v_customer_profile_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_v2pub.create_cust_account ( p_init_msg_list =>FND_API.G_FALSE, p_cust_account_rec => v_customer_account_rec, -- Customer Account Record
p_organization_rec => v_organization_rec, -- Party Organization Record
p_customer_profile_rec => v_customer_profile_rec, -- Customer Profile Record
p_create_profile_amt => fnd_api.g_true,
x_cust_account_id => l_cust_acc_id, x_account_number => x_account_number,
x_party_id => x_party_id, x_party_number => x_party_number,
x_profile_id => x_profile_id, x_return_status =>x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data );
COMMIT;
IF x_return_status = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XXGG_AR_CUST_INTFC_STG
SET MESSAGE='Create a party and an account: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
fnd_file.put_line(FND_FILE.LOG,'customer account id '||l_cust_acc_id);
fnd_file.put_line(FND_FILE.LOG,'customer account number '||x_account_number);
fnd_file.put_line(FND_FILE.LOG,'customer party id '||x_party_id);
/*SELECT cust_account_profile_id
INTO x_cust_account_profile_id
FROM hz_customer_profiles
WHERE cust_account_id = l_cust_acc_id
AND site_use_id IS NULL;
v_customer_profile_amt.cust_account_profile_id := x_cust_account_profile_id;*/
v_customer_profile_amt.cust_account_id := l_cust_acc_id;
--v_customer_profile_amt.CUST_ACCT_PROFILE_AMT_ID := l_cust_acc_id;
-- v_customer_profile_amt.currency_code := 'SAR';
--- v_customer_profile_amt.overall_credit_limit := rec_cur.cust_credit_limit;
-- v_customer_profile_amt.created_by_module := 'TCA_V1_API';
/*hz_customer_profile_v2pub.create_cust_profile_amt
( p_init_msg_list => 'T' , p_check_foreign_key => FND_API.G_TRUE,
p_cust_profile_amt_rec => v_customer_profile_amt, x_cust_acct_profile_amt_id => v_cust_act_prof_amt_id,
x_return_status =>x_return_status, x_msg_count => x_msg_count, x_msg_data =>x_msg_data );
*/
/*dbms_output.put_line('Account : '|| X_RETURN_STATUS||' '||rec_cur.customer_number ||' '||x_msg_data);
IF X_RETURN_STATUS = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XXGG_AR_CUST_INTFC_STG
SET MESSAGE='Create a party and an account: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;*/
--commit;
--IF rec_cur.site_address IS NOT NULL THEN -- Region not Null
IF rec_cur.customer_number IS NOT NULL THEN -- Region not Null
------------------------------------
-- 2. Create a physical location
------------------------------------
fnd_file.put_line(FND_FILE.LOG,'before create location');
v_location_rec.country := rec_cur.country; --'SA';
--v_location_rec.address1 := rec_cur.site_address;
v_location_rec.address1 := rec_cur.address1;
--v_location_rec.address2 := rec_cur.site_Address2;
v_location_rec.city := rec_cur.city;
v_location_rec.application_id := v_application_id;
v_location_rec.created_by_module := 'TCA_V1_API';
hz_location_v2pub.create_location(P_INIT_MSG_LIST => 'T', P_LOCATION_REC => v_location_rec,
X_LOCATION_ID => X_LOCATION_ID,
X_RETURN_STATUS =>X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA);
commit;
IF X_RETURN_STATUS = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE='error when create location: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
------------------------------------
-- 3. Create a party site using party_id from step 1 and location_id from step 2
------------------------------------
fnd_file.put_line(FND_FILE.LOG,'location id'||x_location_id);
fnd_file.put_line(FND_FILE.LOG,'customer party id '||x_party_id);
IF x_location_id IS NOT NULL THEN -- Location ID not Null
v_party_site_rec.party_id := x_party_id;
v_party_site_rec.location_id := x_location_id;
v_party_site_rec.application_id := v_application_id;
v_party_site_rec.party_site_number := NULL;
v_party_site_rec.identifying_address_flag := 'Y';
v_party_site_rec.created_by_module := 'TCA_V1_API';
hz_party_site_v2pub.create_party_site( P_INIT_MSG_LIST => 'T',
P_PARTY_SITE_REC => v_party_site_rec,
X_PARTY_SITE_ID => X_PARTY_SITE_ID,
X_PARTY_SITE_NUMBER => X_PARTY_SITE_NUMBER,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
commit;
IF X_RETURN_STATUS = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE=' error when create party site: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
-- commit;
------------------------------------
-- 4. Create an account site using cust_account_id from step 1 and party_site_id from step 3.
------------------------------------
IF x_party_site_id IS NOT NULL THEN -- Party Site ID not null
FOR rec_cur_ou IN cur_ou(rec_cur.operating_unit)
LOOP
SELECT term_id
INTO v_term_id
FROM apps.ra_terms
WHERE UPPER (name) = UPPER(rec_cur.Payment_terms)
AND ROWNUM = 1;
v_org_id := rec_cur_ou.organization_id;
v_cust_acct_site_rec.cust_account_id := l_cust_acc_id;
v_cust_acct_site_rec.party_site_id := x_party_site_id;
v_cust_acct_site_rec.application_id := v_application_id;
v_cust_acct_site_rec.org_id := v_org_id;
fnd_file.put_line(FND_FILE.LOG,'organization id '||v_org_id);
-- p_cust_acct_site_rec.orig_system_reference := p_cust_acct_site_id;
v_cust_acct_site_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_acct_site(P_INIT_MSG_LIST => 'T', P_CUST_ACCT_SITE_REC => v_cust_acct_site_rec, X_CUST_ACCT_SITE_ID => X_CUST_ACCT_SITE_ID, X_RETURN_STATUS => X_RETURN_STATUS, X_MSG_COUNT => X_MSG_COUNT, X_MSG_DATA => X_MSG_DATA);
fnd_file.put_line(FND_FILE.LOG,'customer account site id '||x_cust_acct_site_id);
commit;
IF x_cust_acct_site_id IS NOT NULL THEN -- Customer Account Site ID is not null
-- v_gl_id_rec := NULL;
v_customer_ship_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
v_customer_ship_site_use_rec.site_use_code := 'SHIP_TO';
-- v_customer_site_use_rec.gl_id_rec := 7006;
v_customer_ship_site_use_rec.org_id := v_org_id;
v_customer_ship_site_use_rec.payment_term_id := v_term_id;
v_customer_ship_site_use_rec.application_id := v_application_id;
v_customer_ship_site_use_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_ship_site_use_rec, v_customer_profile_rec, '', '', x_site_use_id, x_return_status, x_msg_count, x_msg_data);
commit;
v_customer_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
v_customer_site_use_rec.site_use_code := 'BILL_TO';
v_customer_site_use_rec.payment_term_id := v_term_id;
-- v_customer_site_use_rec.gl_id_rec := rec_cur.ar_receivable_account;--7006;
v_customer_site_use_rec.org_id := v_org_id;
v_customer_site_use_rec.application_id := v_application_id;
v_customer_site_use_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_site_use_rec, v_customer_profile_rec, '', '', x_site_use_id, x_return_status, x_msg_count, x_msg_data);
commit;
ELSE
ROLLBACK;
END IF;--X_CUST_ACCT_SITE_ID
END LOOP;
COMMIT;
------------------------------------
-- 5. Create an account site use using cust_acct_site_id from step 4 and site_use_code='BILL_TO'
------------------------------------
ELSE
ROLLBACK;
END IF;--X_PARTY_SITE_ID
ELSE
ROLLBACK;
END IF;--LOCATION ID
ELSE
ROLLBACK;
END IF;-- Region not Null
ELSE
ROLLBACK;
END IF;-- Party Name not Null
ELSE
/* ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE='Customer aleady Exist',
STATUS='E'
WHERE INTERFACE_ID=REC_CUR.INTERFACE_ID;
*/
IF v_count > 0 THEN
SELECT party_id
INTO x_party_id
FROM hz_parties
WHERE party_name = TRIM(rec_cur.customer_name);
--x_PARTY_ID := 38084;
SELECT term_id
INTO v_term_id
FROM apps.ra_terms
WHERE UPPER (name) = UPPER (rec_cur.Payment_terms)
AND ROWNUM = 1;
IF rec_cur.site_address1 IS NOT NULL and status IS NOT NULL THEN -- Region not Null
------------------------------------
-- 2. Create a physical location
------------------------------------
v_location_rec.country := rec_cur.country; --'SA';
v_location_rec.address1 := rec_cur.address1;
--v_location_rec.address2 := rec_cur.site_address2;
v_location_rec.city := rec_cur.city;
v_location_rec.application_id := v_application_id;
v_location_rec.created_by_module := 'TCA_V1_API';
HZ_LOCATION_V2PUB.create_location(p_init_msg_list => 'T', p_location_rec => v_location_rec, x_location_id => x_location_id, x_return_status =>x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Create a party location');
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(FND_FILE.LOG,'x_location_id: ' || x_location_id);
fnd_file.put_line(FND_FILE.LOG,'l_return_status: ' || x_return_status);
fnd_file.put_line(FND_FILE.LOG,'l_msg_count: ' || x_msg_count);
fnd_file.put_line(FND_FILE.LOG,'l_msg_data: ' || x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
IF x_return_status = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE='error when create location: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
--commit;
COMMIT;
------------------------------------
-- 3. Create a party site using party_id from step 1 and location_id from step 2
------------------------------------
IF x_location_id IS NOT NULL THEN -- Location ID not Null
v_party_site_rec.party_id := x_party_id;
v_party_site_rec.location_id := x_location_id;
v_party_site_rec.application_id := v_application_id;
v_party_site_rec.party_site_number := NULL;
v_party_site_rec.identifying_address_flag := 'Y';
v_party_site_rec.created_by_module := 'TCA_V1_API';
hz_party_site_v2pub.create_party_site(p_init_msg_list => 'T', p_party_site_rec => v_party_site_rec
,x_party_site_id => x_party_site_id
,x_party_site_number => x_party_site_number
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(FND_FILE.LOG,'X_PARTY_SITE_ID: ' || X_PARTY_SITE_ID);
fnd_file.put_line(FND_FILE.LOG,'X_PARTY_SITE_NUMBER: ' || X_PARTY_SITE_NUMBER);
fnd_file.put_line(FND_FILE.LOG,'l_return_status: ' || x_return_status);
fnd_file.put_line(FND_FILE.LOG,'l_msg_count: ' || x_msg_count);
fnd_file.put_line(FND_FILE.LOG,'l_msg_data: ' || x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(FND_FILE.LOG,'Create a party site');
IF X_RETURN_STATUS = 'S' THEN
NULL;
ELSE
ROLLBACK;
L_SUCCESS :=2;
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE=' error when create party site: '
|| x_msg_data
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
COMMIT;
END IF;
-- commit;
------------------------------------
-- 4. Create an account site using cust_account_id from step 1 and party_site_id from step 3.
-- LOOP
------------------------------------
IF x_party_site_id IS NOT NULL THEN -- Party Site ID not null
FOR rec_cur_ou IN cur_ou(rec_cur.operating_unit)
LOOP
SELECT cust_account_id
INTO l_cust_acc_id
FROM hz_cust_accounts
WHERE party_id = x_party_id;
v_org_id := rec_cur_ou.organization_id;
v_cust_acct_site_rec.party_site_id := x_party_site_id;
v_cust_acct_site_rec.org_id := v_org_id;
v_cust_acct_site_rec.application_id := v_application_id;
-- p_cust_acct_site_rec.orig_system_reference := p_cust_acct_site_id;
v_cust_acct_site_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_acct_site(p_init_msg_list => 'T'
, p_cust_acct_site_rec => v_cust_acct_site_rec
, x_cust_acct_site_id => x_cust_acct_site_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Create a party account site');
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(FND_FILE.LOG,'x_cust_acct_site_id : ' || x_cust_acct_site_id );
fnd_file.put_line(FND_FILE.LOG,'l_return_status: ' || x_return_status);
fnd_file.put_line(FND_FILE.LOG,'l_msg_count: ' || x_msg_count);
fnd_file.put_line(FND_FILE.LOG,'l_msg_data: ' || x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
IF x_cust_acct_site_id IS NOT NULL THEN -- Customer Account Site ID is not null
--v_gl_id_rec := NULL;
v_customer_ship_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
v_customer_ship_site_use_rec.site_use_code := 'SHIP_TO';
-- v_customer_site_use_rec.gl_id_rec := 7006;
v_customer_ship_site_use_rec.org_id := v_org_id;
v_customer_ship_site_use_rec.application_id := v_application_id;
v_customer_ship_site_use_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_ship_site_use_rec, v_customer_profile_rec, '', '', X_SITE_USE_ID, X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA);
fnd_file.put_line(FND_FILE.LOG,'Create a party account site11');
v_customer_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
v_customer_site_use_rec.site_use_code := 'BILL_TO';
-- v_customer_site_use_rec.gl_id_rec := rec_cur.ar_receivable_account;--7006;
v_customer_site_use_rec.org_id := v_org_id;
v_customer_site_use_rec.payment_term_id := v_term_id;
v_customer_site_use_rec.application_id := v_application_id;
v_customer_site_use_rec.created_by_module := 'TCA_V1_API';
hz_cust_account_site_v2pub.create_cust_site_use ( 'T', v_customer_site_use_rec, v_customer_profile_rec, '', '', x_site_use_id, x_return_status, x_msg_count, x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'Create a party account site');
fnd_file.put_line(FND_FILE.LOG,'***************************');
fnd_file.put_line(fnd_file.LOG,'x_cust_acct_site_id : ' || x_cust_acct_site_id );
fnd_file.put_line(FND_FILE.LOG,'l_return_status: ' || x_return_status);
fnd_file.put_line(FND_FILE.LOG,'l_msg_count: ' || x_msg_count);
fnd_file.put_line(FND_FILE.LOG,'l_msg_data: ' || x_msg_data);
fnd_file.put_line(FND_FILE.LOG,'***************************');
ELSE
ROLLBACK;
END IF;--X_CUST_ACCT_SITE_ID
END LOOP;
COMMIT;
------------------------------------
-- 6. Create a definition contact
------------------------------------
v_contact_point_rec.created_by_module := 'TCA_V1_API';
------------------------------------
-- 5. Create an account site use using cust_acct_site_id from step 4 and site_use_code='BILL_TO'
------------------------------------
COMMIT;
ELSE
ROLLBACK;
END IF;--X_PARTY_SITE_ID
ELSE
ROLLBACK;
END IF;--LOCATION ID
ELSE
ROLLBACK;
END IF;-- Region not Null
ELSE
ROLLBACK;
END IF;
END IF;-- Party Name creation
COMMIT;
IF l_success = 1 THEN
UPDATE XX_CUSTOMER_MIGRATION
SET MESSAGE ='Loaded Successfully',
status ='S'
WHERE customer_number=rec_cur.customer_number;
--interface_id=rec_cur.interface_id;
END IF;
COMMIT;
END LOOP;
END customer_migration;
No comments:
Post a Comment