Oracle Application: TCA API Customer Interface

/* Formatted on 9/13/2012 4:20:29 PM (QP5 v5.115.810.9015) */
CREATE OR REPLACE PACKAGE APPS.xxbc_customer_tca_pkg
AS
PROCEDURE xxbc_customer_tca_main;

PROCEDURE xxbc_customer_tca_validation;

PROCEDURE xxbc_organizations_tca_api;

PROCEDURE xxbc_customer_account_api;

PROCEDURE xxbc_customer_location_api;

PROCEDURE xxbc_party_site;

PROCEDURE xxbc_cust_account_site;

PROCEDURE xxbc_cust_account_site_use;
END;

CREATE OR REPLACE PACKAGE BODY APPS.xxbc_customer_tca_pkg
AS
CURSOR cur_tca
IS
SELECT * FROM customer_tca_api_staging;

p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
p_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
p_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
p_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
p_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;

x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_cust_account_id NUMBER;
x_account_number VARCHAR2 (2000);
x_profile_id NUMBER;
x_location_id NUMBER;
x_party_site_id NUMBER;
x_party_site_number VARCHAR2 (2000);
x_cust_acct_site_id NUMBER;
x_site_use_id NUMBER;

PROCEDURE xxbc_customer_tca_main
AS
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('204');
xxbc_customer_tca_validation;
xxbc_organizations_tca_api;
xxbc_customer_account_api;
xxbc_customer_location_api;
xxbc_party_site;
xxbc_cust_account_site;
xxbc_cust_account_site_use;
END;

PROCEDURE xxbc_customer_tca_validation
AS
l_process_flag VARCHAR2 (1) DEFAULT 'N' ;
l_err_msg VARCHAR2 (2000);
l_country VARCHAR2 (20);
l_customer_type VARCHAR2 (1);
l_credit_rating VARCHAR2 (30);
l_job_title VARCHAR2 (30);
l_pri_name VARCHAR2 (10);

CURSOR cur_tca
IS
SELECT *
FROM customer_tca_api_staging
WHERE process_flag = 'N';
BEGIN
l_process_flag := NULL;

FOR i IN cur_tca
LOOP
BEGIN
SELECT TERRITORY_CODE
INTO l_country
FROM FND_TERRITORIES
WHERE TERRITORY_CODE = i.country;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_process_flag := 'E';
l_err_msg := 'country does not exists';
WHEN OTHERS
THEN
l_process_flag := 'E';
l_err_msg := l_err_msg || SQLERRM;
END;

BEGIN
IF i.cust_acct_status NOT IN ('A', 'I')
THEN
l_process_flag := 'E';
l_err_msg := 'account status must active and inactive';
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_process_flag := 'E';
l_err_msg := l_err_msg || SQLERRM;
END;

BEGIN
SELECT lookup_code
INTO l_customer_type
FROM ar_lookups
WHERE lookup_code = i.customer_type
AND lookup_type = 'CUSTOMER_TYPE';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_process_flag := 'E';
l_err_msg := 'customer type does not exits';
WHEN OTHERS
THEN
l_process_flag := 'E';
l_err_msg := l_err_msg || SQLERRM;
END;

BEGIN
IF i.primary_flag NOT IN ('Y', 'N')
THEN
l_process_flag := 'E';
l_err_msg := 'primary flag must be in y and n';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_process_flag := 'E';
l_err_msg := 'primary flag should be in y and n';
WHEN OTHERS
THEN
l_process_flag := 'E';
l_err_msg := l_err_msg || SQLERRM;
END;

BEGIN
SELECT lookup_code
INTO l_credit_rating
FROM ar_lookups
WHERE lookup_code = i.credit_rating
AND lookup_type = 'CREDIT_RATING';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_process_flag := 'E';
l_err_msg := 'credit rating does not exists';
WHEN OTHERS
THEN
l_process_flag := 'E';
l_err_msg := l_err_msg || SQLERRM;
END;

BEGIN
SELECT lookup_code
INTO l_job_title
FROM ar_lookups
WHERE lookup_code = i.job_title AND lookup_type = 'JOB_TITLE';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_process_flag := 'E';
l_err_msg := 'jot title does not exits';
WHEN OTHERS
THEN
l_process_flag := 'E';
l_err_msg := l_err_msg || SQLERRM;
END;

BEGIN
SELECT lookup_code
INTO l_pri_name
FROM ar_lookups
WHERE lookup_code = i.person_pri_name_adjunct
AND lookup_type = 'CONTACT_TITLE';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_process_flag := 'E';
l_err_msg := 'contact should not exists';
WHEN OTHERS
THEN
l_process_flag := 'E';
l_err_msg := l_err_msg || SQLERRM;
END;

IF l_process_flag = 'E'
THEN
UPDATE customer_tca_api_staging
SET PROCESS_FLAG = L_PROCESS_FLAG,
ERR_MSG = SUBSTR (L_ERR_MSG, 1, 2000)
WHERE party_old_id = i.party_old_id;

COMMIT;
ELSE
xxbc_organizations_tca_api;
xxbc_customer_account_api;
xxbc_customer_location_api;
xxbc_party_site;
xxbc_cust_account_site;
xxbc_cust_account_site_use;

UPDATE customer_tca_api_staging
SET PROCESS_FLAG = 'Y',
ERR_MSG = SUBSTR (L_ERR_MSG, 1, 2000)
WHERE party_old_id = i.party_old_id;

COMMIT;
END IF;
END LOOP;
END xxbc_customer_tca_validation;

PROCEDURE xxbc_organizations_tca_api
AS
BEGIN
FOR i IN cur_tca
LOOP
p_organization_rec.organization_name := i.org_name;
p_organization_rec.known_as := i.known_as;
p_organization_rec.created_by_module := i.created_by_module;
hz_party_v2pub.create_organization ('T',
p_organization_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_party_id,
x_party_number,
x_profile_id);
DBMS_OUTPUT.put_line (
'return_status=' || SUBSTR (x_return_status, 1, 255)
);
DBMS_OUTPUT.put_line ('count=' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('Msg_data = ' || SUBSTR (x_msg_data, 1, 255));
DBMS_OUTPUT.put_line ('Party Id=' || SUBSTR (x_party_id, 1, 255));
DBMS_OUTPUT.put_line (
'Party_Number=' || SUBSTR (x_party_number, 1, 255)
);
DBMS_OUTPUT.put_line('Organization_Name='
|| SUBSTR (
p_organization_rec.organization_name,
1,
255
));
--dbms_output.put_line('CreatedBy='||SUBSTR (p_organization_rec.created_by_module,1,255));
DBMS_OUTPUT.put_line (
'Profile Id=' || SUBSTR (x_profile_id, 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 LOOP;
END xxbc_organizations_tca_api;

PROCEDURE xxbc_customer_account_api
AS
CURSOR cur_tca
IS
SELECT * FROM customer_tca_api_staging;
BEGIN
FOR i IN cur_tca
LOOP
p_cust_account_rec.account_name := i.account_name;
p_cust_account_rec.created_by_module := i.created_by_module;
p_organization_rec.organization_name := i.org_name;
p_organization_rec.created_by_module := i.created_by_module;
p_cust_account_rec.ORIG_SYSTEM_REFERENCE := i.ORIG_SYSTEM_ADDRESS_REF;
p_cust_account_rec.customer_type := i.customer_type;
p_cust_account_rec.status := i.CUST_ACCT_STATUS;

p_customer_profile_rec.profile_class_id := 2062;

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

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

IF x_msg_count > 1
THEN
FOR I IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line(I || '. '
|| SUBSTR (
FND_MSG_PUB.Get (
p_encoded => FND_API.G_FALSE
),
1,
255
));
END LOOP;
END IF;
END LOOP;
END xxbc_customer_account_api;

PROCEDURE xxbc_customer_location_api
AS
BEGIN
FOR i IN cur_tca
LOOP
p_location_rec.country := i.country;
p_location_rec.address1 := i.address1;
p_location_rec.address2 := i.address2;
p_location_rec.city := i.city;
p_location_rec.postal_code := i.postal_code;
p_location_rec.state := i.state;
p_location_rec.created_by_module := i.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;
END LOOP;
END xxbc_customer_location_api;

PROCEDURE xxbc_party_site
AS
CURSOR CUR
IS
SELECT PARTY_ID, LOCATION_ID, CREATED_BY_MODULE
FROM ( SELECT P.PARTY_ID, L.LOCATION_ID, P.CREATED_BY_MODULE
FROM HZ_PARTIES P, HZ_LOCATIONS L, HZ_PARTY_SITES S
WHERE P.PARTY_ID = S.PARTY_ID
AND S.LOCATION_ID = L.LOCATION_ID
ORDER BY P.CREATION_DATE DESC)
WHERE ROWNUM < 10;
BEGIN
FOR j IN cur
LOOP
p_party_site_rec.party_id := j.PARTY_ID;
p_party_site_rec.location_id := j.LOCATION_ID;
p_party_site_rec.identifying_address_flag := 'Y';
END LOOP;

FOR i IN cur_tca
LOOP
p_party_site_rec.created_by_module := I.CREATED_BY_MODULE;
p_party_site_rec.party_site_name := i.party_site_name;

HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE (
p_init_msg_list => FND_API.G_TRUE,
p_party_site_rec => p_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
);

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;
END xxbc_party_site;

PROCEDURE xxbc_cust_account_site
AS
CURSOR cur
IS
SELECT cust_account_id, party_site_id
FROM ( SELECT c.cust_account_id, ps.party_site_id
FROM hz_cust_accounts c, hz_party_sites ps
WHERE c.party_id = ps.party_id
ORDER BY c.creation_date DESC)
WHERE ROWNUM > 9;

CURSOR cur_tca
IS
SELECT * FROM customer_tca_api_staging;
BEGIN
FOR j IN cur
LOOP
p_cust_acct_site_rec.cust_account_id := j.cust_account_id;
p_cust_acct_site_rec.party_site_id := j.party_site_id;
END LOOP;

FOR i IN cur_tca
LOOP
p_cust_acct_site_rec.status := i.acct_site_status;
p_cust_acct_site_rec.created_by_module := i.created_by_module;
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
);

DBMS_OUTPUT.put_line ('***************************');
DBMS_OUTPUT.put_line ('Output information ....');
DBMS_OUTPUT.put_line (
'x_cust_acct_site_id: ' || x_cust_acct_site_id
);
DBMS_OUTPUT.put_line ('x_return_status: ' || x_return_status);
DBMS_OUTPUT.put_line ('x_msg_count: ' || x_msg_count);
DBMS_OUTPUT.put_line ('x_msg_data: ' || x_msg_data);
DBMS_OUTPUT.put_line ('***************************');

IF x_msg_count > 1
THEN
FOR I IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line(I || '. '
|| SUBSTR (
FND_MSG_PUB.Get (
p_encoded => FND_API.G_FALSE
),
1,
255
));
END LOOP;
END IF;
END LOOP;
END xxbc_cust_account_site;

PROCEDURE xxbc_cust_account_site_use
AS
CURSOR cur
IS
SELECT cust_acct_site_id
FROM ( SELECT cust_acct_site_id
FROM hz_cust_acct_sites_all
ORDER BY creation_date)
WHERE ROWNUM < 9;

CURSOR cur_tca
IS
SELECT * FROM customer_tca_api_staging;
BEGIN
FOR I IN CUR
LOOP
p_cust_site_use_rec.cust_acct_site_id := I.CUST_ACCT_SITE_ID;
END LOOP;

FOR i IN cur_tca
LOOP
p_cust_site_use_rec.site_use_code := i.site_use_code;
p_cust_site_use_rec.created_by_module := i.created_by_module;
p_cust_site_use_rec.primary_flag := i.primary_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
);
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;
END LOOP;
END xxbc_cust_account_site_use;
END XXBC_CUSTOMER_TCA_PKG;

Oracle Application

Friday, 14 February 2014

TCA API Customer Interface

No comments:

Post a Comment