API to assign existing customer sites to other OUs(Operating Unit)

1. Create a custom table to hold error massages.

CREATE TABLE xxcona_cust_acct_sites_error

(

org_id NUMBER,

cust_account_id NUMBER,

party_site_id NUMBER,

cust_acct_site_id NUMBER,

site_use_id NUMBER,

process_flag VARCHAR2(1),

error_msg VARCHAR2(4000),

error_api VARCHAR2(250)

);

2. Use the below procedure.

CREATE OR REPLACE PROCEDURE xxcona_assign_cust_site_to_org (

p_cust_account_id IN NUMBER,

p_assign_from_org_id IN NUMBER,

p_assign_to_org_id IN NUMBER

)

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

-- File name : xxcona_assign_cust_site_to_org.prc

-- Author : Arjun Prasad

-- Created : 06-JUN-2015

-- Description : This PL/SQL procedure is used to assign customer sites to another operating units.

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

-- Date Author Version Reason

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

--06-JUN-2015 Arjun Prasad 1.0 Initial creation

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

---For create_cust_acct_site creation--------------

v_customer_acount_site_record hz_cust_account_site_v2pub.cust_acct_site_rec_type;

v_cust_acct_site_id NUMBER := NULL;

v_return_status VARCHAR2 (2) := NULL;

v_msg_count NUMBER := NULL;

v_msg_data VARCHAR2 (32000) := NULL;

---For cust_site_use_rec creation--------------

v_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;

v_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;

v_site_use_id NUMBER := NULL;

l_msg_index_out NUMBER;

CURSOR cust_acct_sites

SELECT hcas.org_id, hcas.cust_account_id, hcas.party_site_id,

hcas.cust_acct_site_id

FROM hz_cust_acct_sites_all hcas

WHERE hcas.org_id = p_assign_from_org_id

AND hcas.cust_account_id =

NVL (p_cust_account_id, hcas.cust_account_id);

CURSOR cust_acct_site_uses (

xx_cust_acct_site_id NUMBER,

xx_assign_from_org_id NUMBER

)

SELECT hcsu.org_id, hcsu.cust_acct_site_id, hcsu.site_use_id

FROM hz_cust_site_uses_all hcsu

WHERE hcsu.cust_acct_site_id = xx_cust_acct_site_id

AND hcsu.org_id = xx_assign_from_org_id;

BEGIN

--==============Initializa Application ======================--

fnd_global.apps_initialize (user_id => 1737,

resp_id => 20678,

resp_appl_id => 222

);

MO_GLOBAL.SET_POLICY_CONTEXT('S',p_assign_to_org_id);

MO_GLOBAL.INIT ('AR') ;

FOR cur_rec IN cust_acct_sites

LOOP

v_customer_acount_site_record := NULL;

--==============To get_cust_acct_site_rec =========================--

hz_cust_account_site_v2pub.get_cust_acct_site_rec

(p_init_msg_list => fnd_api.g_false,

p_cust_acct_site_id => cur_rec.cust_acct_site_id,

x_cust_acct_site_rec => v_customer_acount_site_record,

x_return_status => v_return_status,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data

);

IF NVL (v_return_status, 'E') = 'S'

THEN

DBMS_OUTPUT.put_line ( 'get_cust_acct_site_rec=>V_RETURN_STATUS:='

|| v_return_status

);

END IF;

--==============To create_cust_acct_site =========================--

v_customer_acount_site_record.org_id := p_assign_to_org_id;

v_customer_acount_site_record.cust_acct_site_id := NULL;

v_customer_acount_site_record.orig_system_reference := NULL;

hz_cust_account_site_v2pub.create_cust_acct_site

(fnd_api.g_true,

v_customer_acount_site_record,

v_cust_acct_site_id,

--returned

v_return_status,

--To indicagte success

v_msg_count, --

v_msg_data

);

DBMS_OUTPUT.put_line ( 'create_cust_acct_site=>V_RETURN_STATUS:='

|| v_return_status

);

IF NVL (v_return_status, 'E') = 'S' AND v_cust_acct_site_id IS NOT NULL

THEN

v_return_status := NULL;

FOR site_uses_rec IN cust_acct_site_uses (cur_rec.cust_acct_site_id,

p_assign_from_org_id

)

LOOP

v_cust_site_use_rec := NULL;

v_customer_profile_rec := NULL;

--==============To get_cust_site_use_rec =========================--

hz_cust_account_site_v2pub.get_cust_site_use_rec

(p_init_msg_list => fnd_api.g_false,

p_site_use_id => site_uses_rec.site_use_id,

x_cust_site_use_rec => v_cust_site_use_rec,

x_customer_profile_rec => v_customer_profile_rec,

x_return_status => v_return_status,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data

);

IF NVL (v_return_status, 'E') = 'S'

THEN

DBMS_OUTPUT.put_line

( 'get_cust_site_use_rec=>V_RETURN_STATUS:='

|| v_return_status

);

END IF;

--==============To create_cust_site_use =========================--

v_cust_site_use_rec.org_id := p_assign_to_org_id;

v_cust_site_use_rec.site_use_id := NULL;

v_cust_site_use_rec.cust_acct_site_id := v_cust_acct_site_id;

v_cust_site_use_rec.orig_system_reference := NULL;

v_customer_profile_rec.cust_account_profile_id := NULL;

hz_cust_account_site_v2pub.create_cust_site_use

(p_init_msg_list => fnd_api.g_true,

p_cust_site_use_rec => v_cust_site_use_rec,

p_customer_profile_rec => v_customer_profile_rec,

p_create_profile => fnd_api.g_true,

p_create_profile_amt => fnd_api.g_false,

x_site_use_id => v_site_use_id,

x_return_status => v_return_status,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data

);

DBMS_OUTPUT.put_line ( 'create_cust_site_use=>V_RETURN_STATUS:='

|| v_return_status

);

IF NVL (v_return_status, 'E') = 'S'

THEN

DBMS_OUTPUT.put_line ( 'ORG_ID:='

|| v_cust_site_use_rec.org_id

|| '***'

|| 'v_site_use_id:='

|| v_site_use_id

);

ELSE

FOR i IN 1 .. v_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => i,

p_data => v_msg_data,

p_encoded => fnd_api.g_false,

p_msg_index_out => l_msg_index_out

);

v_msg_data := v_msg_data || ' *** ' || v_msg_data;

END LOOP;

DBMS_OUTPUT.put_line ( 'create_cust_site_use=>Error: '

|| v_msg_data

);

INSERT INTO xxcona_cust_acct_sites_error

(org_id, cust_account_id,

party_site_id, cust_acct_site_id,

site_use_id, process_flag,

error_msg,

error_api

)

VALUES (cur_rec.org_id, cur_rec.cust_account_id,

cur_rec.party_site_id, cur_rec.cust_acct_site_id,

site_uses_rec.site_use_id, 'N',

SUBSTR (v_msg_data, 1, 4000),

'create_cust_site_use'

);

END IF;

END LOOP;

ELSE

FOR i IN 1 .. v_msg_count

LOOP

fnd_msg_pub.get (p_msg_index => i,

p_data => v_msg_data,

p_encoded => fnd_api.g_false,

p_msg_index_out => l_msg_index_out

);

v_msg_data := v_msg_data || ' *** ' || v_msg_data;

END LOOP;

DBMS_OUTPUT.put_line ('create_cust_acct_site=>Error: ' || v_msg_data);

INSERT INTO xxcona_cust_acct_sites_error

(org_id, cust_account_id,

party_site_id, cust_acct_site_id, site_use_id,

process_flag, error_msg,

error_api

)

VALUES (cur_rec.org_id, cur_rec.cust_account_id,

cur_rec.party_site_id, cur_rec.cust_acct_site_id, NULL,

'N', SUBSTR (v_msg_data, 1, 4000),

'create_cust_acct_site'

);

END IF;

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('ERROR MESSAGE :=' || SQLERRM);

END;

3. Syntax to call above procedure.

BEGIN

xxcona_assign_cust_site_to_org (p_cust_account_id => 5042,

-- Pass NULL to consider all customer sites.

p_assign_from_org_id => 81,

p_assign_to_org_id => 221

);

END;

Oracle APPS (Oracle Application E-BUSINESS SUITE) 11i - R12 - Fusion Cloud R13

Saturday, 6 June 2015

API to assign existing customer sites to other OUs(Operating Unit)

No comments:

Post a Comment

Contributors

Blog Archive