Objective:
The below code logic is used to extend the customer sites into the different org. (eg. Suppose customer sites exist in 82 org and the requirement is to extend the same site to other org like 100,102) then this below code logic creates the same with other orgs.
Code:
CREATE OR REPLACE PROCEDURE APPS.XX_CUST_SITE_EXT_PRC (errbuff out varchar2,
retcode out number,
p_party_site_number VARCHAR2
)
aS
CURSOR c(p_org_id NUMBER)
IS
SELECT organization_id org_id
FROM hr_operating_units
WHERE organization_id not in (p_org_id, 101, 102);
CURSOR cur_det (p_party_site_number VARCHAR2)
IS
SELECT *
FROM (SELECT hcasa.ROWID,hcasa.cust_account_id, hcasa.cust_acct_site_id,
hps.party_site_number, hcasa.party_site_id, hcasa.org_id,
hcasa.creation_date
FROM apps.hz_party_sites hps, apps.hz_cust_acct_sites_all hcasa
WHERE 1 = 1
AND hps.party_site_id = hcasa.party_site_id
AND TO_DATE (hcasa.creation_date, ‘DD-MON-RR’) >=
TO_DATE (’29-JAN-21′)
AND hps.party_site_number =
NVL (p_party_site_number, party_site_number)
AND hcasa.attribute12 IS NULL
–AND
);
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
x_site_use_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_location_rec_type hz_location_v2pub.location_rec_type;
l_party_site_rec_type hz_party_site_v2pub.party_site_rec_type;
l_cust_acct_site_rec_type hz_cust_account_site_v2pub.cust_acct_site_rec_type;
l_cust_site_use_rec_type hz_cust_account_site_v2pub.cust_site_use_rec_type;
ln_location_id NUMBER;
gc_api_return_status VARCHAR2 (100);
gn_msg_count NUMBER;
gc_msg_data VARCHAR2 (4000);
ln_party_site_id NUMBER;
lc_party_site_number VARCHAR (100);
ln_cust_acct_site_id NUMBER;
–ln_site_use_id NUMBER;
ln_bill_use_id NUMBER;
ln_ship_use_id NUMBER;
l_unit_name VARCHAR2 (100);
lc_add_val_status VARCHAR2 (4000);
lc_addr_warn_msg VARCHAR2 (4000);
l_city NUMBER;
l_state NUMBER;
l_cs_cnt NUMBER;
l_org_id NUMBER ;–:= FND_PROFILE.VALUE(‘ORG_ID’);
l_resp_id NUMBER := NULL;
l_site_use VARCHAR2(30);
l_use_cnt NUMBER;
lv_party_site_id NUMBER := NULL;
lv_gstnnum VARCHAR2(100);
lv_gst_reg varchar2(100);
lv_flag CHAR(1) :=’N’;
–lv_org_id NUMBER;
lv_acct_site_id NUMBER := NULL;
lv_org_id NUMBER := NULL;
lv_cust_acct_id number;
— p_org_id NUMBER := 82;
lv_cust_acct_count NUMBER;
lv_acc_number VARCHAR2(100);
lv_p_cnt number;
lv_s_cnt number;
lv_f_cnt number;
BEGIN
— mo_global.set_policy_context (‘S’, 82);
lv_p_cnt :=0;
lv_s_cnt :=0;
lv_f_cnt :=0;
For cur_det_data in cur_det(p_party_site_number)
LOOP
lv_p_cnt := lv_p_cnt+1;
lv_cust_acct_count := NULL;
BEGIN
SELECT COUNT(cust_acct_site_id)
INTO lv_cust_acct_count
FROM apps.hz_cust_acct_sites_all hcasa
WHERE party_site_id =cur_det_data.party_site_id;
EXCEPTION
WHEN OTHERS THEN
lv_cust_acct_count := NULL;
END;
BEGIN
SELECT hca.account_number,hca.cust_account_id
INTO lv_acc_number,lv_cust_acct_id
FROM apps.hz_cust_acct_sites_all hcasa,apps.hz_cust_accounts_all hca
WHERE hca.cust_account_id =hcasa.cust_account_id
AND party_site_id =cur_det_data.party_site_id;
EXCEPTION
WHEN OTHERS THEN
lv_cust_acct_count := NULL;
END;
IF lv_cust_acct_count =1
THEN
INSERT INTO XXSIFY_CUST_SITE_EXT_HIST
VALUES (lv_acc_number,cur_det_data.party_site_number,
cur_det_data.cust_acct_site_id,cur_det_data.org_id,SYSDATE,’PS’,’Site Extending Apart from this ORG..’);
UPDATE hz_cust_acct_sites_all hcasa
SET –hcasa.attribute_category =’Site Extension’,
hcasa.attribute12 =’Y’
WHERE hcasa.cust_account_id = cur_det_data.cust_account_id
AND ROWID =cur_det_data.ROWID;
DBMS_OUTPUT.PUT_LINE (‘Loop Start extension –‘);
for i in c(cur_det_data.org_id)
LOOP
DBMS_OUTPUT.PUT_LINE (‘Loop Start for Org –‘||i.org_id);
l_resp_id := NULL;
l_site_use := NULL;
l_use_cnt := NULL;
l_org_id := i.org_id;
—
lv_party_site_id :=NULL;
lv_gstnnum :=NULL;
lv_gst_reg := NULL;
— lv_org_id := NULL;
select DECODE(l_org_id, 404, 52107, 405, 52112, 50739)
into l_resp_id
FROM DUAL;
mo_global.init (‘AR’);
mo_global.set_policy_context (‘S’, l_org_id);
fnd_global.apps_initialize (1130, l_resp_id, 222);
fnd_global.set_nls_context(‘AMERICAN’);
FOR j in (SELECT hcasa.ROWID ,hp.party_id, party_name, party_type, hca.account_number,hps.party_site_number,
hca.cust_account_id, hcasa.party_site_id, hcasa.attribute1,
hcasa.attribute3–,hcasa.org_id
FROM apps.hz_parties hp,
apps.hz_cust_accounts hca,
apps.hz_party_sites hps,
apps.hz_cust_acct_sites_all hcasa
WHERE hp.party_id = hca.party_id
AND hp.party_id = hps.party_id
AND hca.party_id = hps.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hps.party_site_id = hcasa.party_site_id
and hps.party_site_id = cur_det_data.party_site_id
and hcasa.org_id =cur_det_data.org_id
)
LOOP
DBMS_OUTPUT.put_line (‘ln_party_site_id’ || ln_party_site_id);
— fnd_file.put_line (fnd_file.output, ‘Party Site ID: ‘||j.party_site_id);
fnd_file.put_line (fnd_file.output, ‘CustAcct Site API CALL for the site Number: ‘||j.party_site_number);
fnd_file.put_line (fnd_file.output, ‘Calling Acc Site Creation API’);
l_cust_acct_site_rec_type.cust_account_id := j.cust_account_id;
l_cust_acct_site_rec_type.party_site_id := j.party_site_id;–lv_party_site_id;– v_t (i).PARTY_SITE_ID;
l_cust_acct_site_rec_type.created_by_module := ‘TCA_V2_API’;
— l_cust_acct_site_rec_type.orig_system_reference := NULL;
l_cust_acct_site_rec_type.status := ‘A’;
l_cust_acct_site_rec_type.org_id := l_org_id;–FND_PROFILE.VALUE(‘ORG_ID’);
l_cust_acct_site_rec_type.attribute1 := j.attribute1;–lv_gstnnum;–v_t (i).GSTN_NUM;
l_cust_acct_site_rec_type.attribute3 := j.attribute3;–lv_gst_reg;–v_t (i).GSTN_REGISTERED;
hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => fnd_api.g_false,
p_cust_acct_site_rec => l_cust_acct_site_rec_type,
x_cust_acct_site_id => ln_cust_acct_site_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);
fnd_file.put_line (fnd_file.output, ‘CustAcct Site API API Return Status: ‘||gc_api_return_status);
DBMS_OUTPUT.put_line (‘hz_cust_account_site_v2pub.create_cust_site_use Ship TO API Call’);
fnd_file.put_line (fnd_file.output, ‘SiteUse API Call for Ship TO’);
COMMIT;
IF gc_api_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Creation of Customer Account Site is Successful ‘);
DBMS_OUTPUT.PUT_LINE(‘Output information ….’);
DBMS_OUTPUT.PUT_LINE(‘Customer Account Site Id is = ‘||ln_cust_acct_site_id);
fnd_file.put_line (fnd_file.output,’Creation of Customer Account Site is Successful ‘);
fnd_file.put_line (fnd_file.output,’Output information ….’);
fnd_file.put_line (fnd_file.output, ‘Customer Account Site Id is = ‘||ln_cust_acct_site_id);
INSERT INTO XXSIFY_CUST_SITE_EXT_HIST
VALUES (j.account_number,j.party_site_number,
ln_cust_acct_site_id,l_org_id,SYSDATE,’S’,’Site Extended Succesfully for org. ‘||l_org_id);
lv_s_cnt := lv_s_cnt+1;
UPDATE hz_cust_acct_sites_all hcasa
SET –hcasa.attribute_category =’Site Extension’,
hcasa.attribute12 =’Y’
WHERE 1=1–hcasa.cust_account_id = j.cust_account_id
AND CUST_ACCT_SITE_ID=ln_cust_acct_site_id;
–AND ROWID =j.ROWID;
ELSE
DBMS_OUTPUT.put_line (‘Creation of Customer Account Site got failed:’||gc_msg_data);
fnd_file.put_line(fnd_file.output,’Creation of Customer Account Site got failed:’||gc_msg_data);
ROLLBACK;
INSERT INTO XXSIFY_CUST_SITE_EXT_HIST
VALUES (j.account_number,j.party_site_number,
ln_cust_acct_site_id,l_org_id,SYSDATE,’E’,’Site Extended Fail due to ‘||gc_msg_data);
lv_f_cnt := lv_f_cnt+1;
FOR i IN 1 .. gn_msg_count
LOOP
gc_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => ‘F’);
dbms_output.put_line( i|| ‘) ‘|| gc_msg_data);
fnd_file.put_line(fnd_file.output,i|| ‘) ‘|| gc_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(‘Completion of API’);
for k in ( select CUST_ACCT_SITE_ID,SITE_USE_CODE,LOCATION
from hz_cust_site_uses_all where CUST_ACCT_SITE_ID=cur_det_data.cust_acct_site_id and org_id =cur_det_data.org_id–p_cust_acct_site_id
)
LOOP
l_cust_site_use_rec_type.LOCATION := k.location;–v_t (i).site_code;–||’-‘||’BILL_TO’;
l_cust_site_use_rec_type.created_by_module := ‘TCA_V2_API’;
l_cust_site_use_rec_type.status := ‘A’;
l_cust_site_use_rec_type.org_id := l_org_id;– FND_PROFILE.VALUE(‘ORG_ID’);–l_org_id;
l_cust_site_use_rec_type.cust_acct_site_id := ln_cust_acct_site_id;
l_cust_site_use_rec_type.site_use_code := k.SITE_USE_CODE;–l_site_use;–‘SHIP_TO’;
— l_cust_site_use_rec_type.attribute1 := to_char(v_t (i).site_use_id); –added 17-12-2020
hz_cust_account_site_v2pub.create_cust_site_use
(p_init_msg_list => fnd_api.g_false,
p_cust_site_use_rec => l_cust_site_use_rec_type,
p_customer_profile_rec => NULL,
— Modified by YerraS on 19-Sep-09
p_create_profile => fnd_api.g_true,
p_create_profile_amt => fnd_api.g_true,
x_site_use_id => ln_ship_use_id,
x_return_status => gc_api_return_status,
x_msg_count => gn_msg_count,
x_msg_data => gc_msg_data
);
DBMS_OUTPUT.put_line (‘gc_api_return_status Ship to’ || gc_api_return_status);
— fnd_file.put_line (fnd_file.output, ‘SiteUse SHIP TO API Return Status : ‘||gc_api_return_status);
COMMIT;
IF gc_api_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Creation of Customer Accnt Site use is Successful ‘);
DBMS_OUTPUT.PUT_LINE(‘Output information ….’);
DBMS_OUTPUT.PUT_LINE(‘Site Use Id = ‘||ln_ship_use_id);
ELSE
DBMS_OUTPUT.put_line (‘Creation of Customer Accnt Site use got failed:’||gc_msg_data);
— fnd_file.put_line(fnd_file.output,’Creation of Customer Account Site got failed:’||gc_msg_data);
ROLLBACK;
FOR i IN 1 .. gn_msg_count
LOOP
gc_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => ‘F’);
dbms_output.put_line( i|| ‘) ‘|| x_msg_data);
fnd_file.put_line(fnd_file.output,i|| ‘) ‘|| gc_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE(‘Completion of API’);
FOR i IN 1 .. gn_msg_count
LOOP
x_msg_data := fnd_msg_pub.get (p_msg_index => i, p_encoded => ‘F’);
DBMS_OUTPUT.put_line (i || ‘) ‘ || x_msg_data);
fnd_file.put_line (fnd_file.output, ‘API Final Msg Status ‘||i || ‘ ) ‘ || x_msg_data);
END LOOP;
COMMIT;
END LOOP;
— END IF;
end loop;
END LOOP;
END IF;
END LOOP;
—++———— Process Record History —————++–
fnd_file.put_line (fnd_file.log,’—++———— Process Record History —————++–‘);
fnd_file.put_line (fnd_file.log,”);
fnd_file.put_line (fnd_file.log,’Number of site selected–>’||lv_p_cnt);
fnd_file.put_line (fnd_file.log,’Number of Site extended successfully–>’||lv_s_cnt);
fnd_file.put_line (fnd_file.log,’Number of Site extended failed–>’||lv_f_cnt);
fnd_file.put_line (fnd_file.log,”);
fnd_file.put_line (fnd_file.log,’—++———— End Record History —————++–‘);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log, ‘Customer Site Creation Failed for Oracle Error’||SQLERRM);
END;
/
