------------------------plsql code script starts here-----------------------------------
DECLARE
p_orgid NUMBER := 1733; --new OU/OrgID number
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;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
x_return_status VARCHAR2 ( 2000 );
x_msg_count NUMBER;
x_msg_data VARCHAR2 ( 2000 ) ;
x_cust_acct_site_id NUMBER;
p_site_use_id NUMBER;
po_return_status1 VARCHAR2 (100);
po_msg_count1 NUMBER;
po_msg_data1 VARCHAR2 (1000);
x_site_use_id NUMBER;
ln_business_group_id number;
ln_user_id number;
ln_org_id number;
ln_resp_id number;
v_error_message varchar2(240);
cursor c1 is
--select * from xxeec_cust_site_api where CREATION_STATUS='N'order by account_number; --and vendor_id in(326173);--This table contains below query data only --table details below
select * from xxeec_cust_site_api where CREATION_STATUS='N' order by account_number;
--and ACCOUNT_NUMBER in(1000,1001);--This table contains below query data only
/*select distinct hcas.CUST_ACCOUNT_ID,hcas.PARTY_SITE_ID,hp.PARTY_NAME,CUST_ACCT_SITE_ID
,hl.ADDRESS1,hl.ADDRESS2,hl.ADDRESS3,hl.ADDRESS4,hl.POSTAL_CODE,hl.STATE
,(SELECT ftt.territory_short_name
FROM fnd_territories_tl ftt, hz_locations hl
WHERE hl.country = ftt.territory_code
AND hl.location_id = hps.location_id and rownum=1) country
,(SELECT DISTINCT hcsua.LOCATION
FROM hz_cust_site_uses_all hcsua
WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsua.site_use_code = 'BILL_TO') bill_to_location,
(SELECT DISTINCT hcsua.LOCATION
FROM hz_cust_site_uses_all hcsua
WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsua.site_use_code = 'SHIP_TO'
and hcsua.status='A') ship_to_location,HCA.account_number
from
hz_cust_accounts hca
,HZ_CUST_ACCT_SITES_ALL hcas
,hz_parties hp
,hz_party_sites hps
,hz_locations hl
where 1=1
and hca.cust_account_id=hcas.cust_account_id
--and hcas.ORG_ID=801
and hca.PARTY_ID=hp.PARTY_ID
and hca.PARTY_ID=hps.PARTY_ID
and hps.LOCATION_ID=hl.LOCATION_ID
and hcas.ORG_ID=102
order by HCA.account_number
;*/
BEGIN
DBMS_OUTPUT.ENABLE(1000000); -- Clear DBMS_OUTPUT buffer.
ln_user_id := fnd_profile.VALUE ('USER_ID');-- Get the user_id
ln_org_id := fnd_profile.VALUE ('ORG_ID'); -- Get the Org_id
ln_resp_id := fnd_profile.VALUE ('RESP_ID');-- Get the Resp_id
FND_FILE.put_line(FND_FILE.output,'user id '||ln_user_id);
FND_FILE.put_line(FND_FILE.output,'org id ' ||ln_org_id);
FND_FILE.put_line(FND_FILE.output,'resp id '||ln_resp_id);
--Initializing the variables
--Initializing the variables
fnd_global.apps_initialize (user_id => 2605,--ln_user_id,
resp_id => 50603,--ln_resp_id, --50639,
resp_appl_id => 222--800
);
--mo_global.init ('PER');
mo_global.set_policy_context ('S', 1770);--ln_org_id);
For REC IN C1
Loop
begin
p_cust_acct_site_rec.cust_account_id := REC.cust_account_id; --219251;
p_cust_acct_site_rec.party_site_id := rec.party_site_id; --212126;
p_cust_acct_site_rec.org_id := P_ORGID;
--p_cust_acct_site_rec.location_id := 6640;
--p_cust_acct_site_rec.language := 'US';
--The Above Language Column is Obsolete in R12
p_cust_acct_site_rec.created_by_module := 'TCA_V2_API';
p_cust_site_use_rec.site_use_code :='BILL_TO';
p_cust_site_use_rec.cust_acct_site_id :=REC.cust_account_id; --219251; --p_cust_acct_site_id;
p_cust_site_use_rec.created_by_module :='HZ_IMPORT';
mo_global.init ( 'AR' ) ;
mo_global.set_org_context ( P_ORGID, NULL, 'AR' ) ;
fnd_global.set_nls_context ( 'AMERICAN' ) ;
mo_global.set_policy_context ( 'S', P_ORGID ) ;
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 ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) );
--dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
dbms_output.put_line ( 'Customer Account Site Id is = '||TO_CHAR ( x_cust_acct_site_id ) ) ;
--dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( 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 ) ) ;
fnd_file.put_line (fnd_file.output,rec.cust_account_id ||'ERROR in customer site creation'||fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false));
v_error_message :=rec.cust_account_id ||'orgid -'||P_ORGID||'-ERROR in customer site creation----'||fnd_msg_pub.get(fnd_msg_pub.g_next,fnd_api.g_false);
UPDATE xxeec_cust_site_api
SET creation_status = 'N',
error_message = v_error_message,
site_creation_date=sysdate
WHERE CUST_ACCOUNT_ID = rec.cust_account_id;
commit;
END LOOP;
----END IF;
else
-- dbms_output.put_line('customer Site Created!');
fnd_file.put_line (fnd_file.output,'customer Site Created for--'||rec.cust_account_id);
UPDATE xxeec_cust_site_api
SET creation_status = 'Y',
error_message = rec.cust_account_id ||'orgid-'||P_ORGID||'---customer Site Created',
site_creation_date=sysdate
WHERE CUST_ACCOUNT_ID = rec.cust_account_id;
COMMIT;
end if;
------------------------------------------------------------
begin
p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
p_cust_site_use_rec.site_use_code := 'BILL_TO';
p_cust_site_use_rec.location := REC.BILL_TO_LOCATION; --'NEW INV LOCATION-6582';
p_cust_site_use_rec.created_by_module := 'TCA_V2_API';
hz_cust_account_site_v2pub.create_cust_site_use ( p_init_msg_list => 'T' ,
p_cust_site_use_rec => p_cust_site_use_rec ,
p_customer_profile_rec => p_customer_profile_rec ,
p_create_profile => '' ,
p_create_profile_amt => '' ,
x_site_use_id => x_site_use_id ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data
);
-------------SHIP TOO-----------------
p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
p_cust_site_use_rec.site_use_code := 'SHIP_TO';
p_cust_site_use_rec.location := REC.BILL_TO_LOCATION; --'NEW INV LOCATION-6582';
p_cust_site_use_rec.created_by_module := 'TCA_V2_API';
hz_cust_account_site_v2pub.create_cust_site_use ( p_init_msg_list => 'T' ,
p_cust_site_use_rec => p_cust_site_use_rec ,
p_customer_profile_rec => p_customer_profile_rec ,
p_create_profile => '' ,
p_create_profile_amt => '' ,
x_site_use_id => x_site_use_id ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data
);
------------ENDS SHIP TO HERE -----------
--dbms_output.put_line ( 'x_return_status = '||SUBSTR ( x_return_status, 1, 255 ) ) ;
--dbms_output.put_line ( 'x_msg_count = '||TO_CHAR ( x_msg_count ) ) ;
dbms_output.put_line ( 'Site Use Id = '||TO_CHAR ( x_site_use_id ) ) ;
--dbms_output.put_line ( 'Site Use = '|| SUBSTR ( p_cust_site_use_rec.site_use_code, 1, 255 ) ) ;
--dbms_output.put_line ( 'x_msg_data = '|| SUBSTR ( 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;
DBMS_OUTPUT.ENABLE(1000000); -- Clear DBMS_OUTPUT buffer.
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ( 'Error: '||SQLERRM ) ;
DBMS_OUTPUT.ENABLE(1000000); -- Clear DBMS_OUTPUT buffer.
END;
END LOOP;
end ;
COMMIT;
--------------------------------------------------------script ends here-----------------------------
Table definition and insert statements......
CREATE TABLE APPS.XXEEC_CUST_SITE_API
(
CUST_ACCOUNT_ID NUMBER(15) NOT NULL,
PARTY_SITE_ID NUMBER(15) NOT NULL,
PARTY_NAME VARCHAR2(360 BYTE) NOT NULL,
CUST_ACCT_SITE_ID NUMBER(15) NOT NULL,
ADDRESS1 VARCHAR2(240 BYTE) NOT NULL,
ADDRESS2 VARCHAR2(240 BYTE),
ADDRESS3 VARCHAR2(240 BYTE),
ADDRESS4 VARCHAR2(240 BYTE),
POSTAL_CODE VARCHAR2(60 BYTE),
STATE VARCHAR2(60 BYTE),
COUNTRY VARCHAR2(80 BYTE),
BILL_TO_LOCATION VARCHAR2(40 BYTE),
SHIP_TO_LOCATION VARCHAR2(40 BYTE),
ACCOUNT_NUMBER VARCHAR2(30 BYTE) NOT NULL,
SITE_CREATION_DATE DATE,
CREATION_STATUS VARCHAR2(10 BYTE),
ERROR_MESSAGE VARCHAR2(240 BYTE)
)
truncate table XXEEC_CUST_SITE_API
commit
INSERT INTO XXEEC_CUST_SITE_API
select distinct hcas.CUST_ACCOUNT_ID,hcas.PARTY_SITE_ID,hp.PARTY_NAME,CUST_ACCT_SITE_ID
,hl.ADDRESS1,hl.ADDRESS2,hl.ADDRESS3,hl.ADDRESS4,hl.POSTAL_CODE,hl.STATE
,(SELECT ftt.territory_short_name
FROM fnd_territories_tl ftt, hz_locations hl
WHERE hl.country = ftt.territory_code
AND hl.location_id = hps.location_id and rownum=1) country
,(SELECT DISTINCT hcsua.LOCATION
FROM hz_cust_site_uses_all hcsua
WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsua.site_use_code = 'BILL_TO') bill_to_location,
(SELECT DISTINCT hcsua.LOCATION
FROM hz_cust_site_uses_all hcsua
WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsua.site_use_code = 'SHIP_TO'
and hcsua.status='A') ship_to_location,
lpad(HCA.account_number,7) account_number
,null ABC,'N',null DEF
from
hz_cust_accounts hca
,HZ_CUST_ACCT_SITES_ALL hcas
,hz_parties hp
,hz_party_sites hps
,hz_locations hl
where 1=1
and hca.cust_account_id=hcas.cust_account_id
and hcas.ORG_ID=1770 --existing customers orgID
and hca.PARTY_ID=hp.PARTY_ID
and hca.PARTY_ID=hps.PARTY_ID
and hps.LOCATION_ID=hl.LOCATION_ID
--AND ROWNUM <=50
--and hcas.ORG_ID=102
AND ACCOUNT_NUMBER <> 'FNCE1'
order by lpad(HCA.account_number,7)
---------------------------------------------
To Select the Customer Account Id run the query:
SELECT cust_account_id, created_by_module, party_id--, org_Id
FROM hz_cust_accounts
where account_number like 'FNCE1'
To Select the Party Site Id run the Query :
SELECT *--party_site_id
FROM hz_party_sites
--where creation_date like sysdate
where party_id = 10166487
select * from
hz_parties
where party_id = 10167321
------------------------------------------------------------------------
VERIFICATION SCRIPT:
select * from XXEEC_CUST_SITE_API
where creation_status <> 'Y'
SELECT cust_acct_site_id,
cust_account_id,
party_site_id,
org_id, status, bill_to_flag, ship_to_flag, created_by_module,application_id, creation_date
FROM hz_cust_acct_sites_all
where cust_account_id IN
(SELECT cust_account_id from
XXEEC_CUST_SITE_API)
and org_id = 1733
No comments:
Post a Comment