DECLARE
lc_return_status VARCHAR2(2000);
ln_msg_count NUMBER;
ll_msg_data LONG;
Ln_Vendor_Id NUMBER;
Ln_Vendor_site_Id NUMBER;
ln_message_int NUMBER;
Ln_Party_Id NUMBER;
lrec_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
cursor c_vendors is
select s.taxpayer_id, s.vendor_id_ora from temporary_suppliers s where s.taxpayer_id is not null and segment1='0'
and s.acc_end_date is null;
BEGIN
for i in c_vendors
loop
Ln_Vendor_Id := i.vendor_id_ora;
Lrec_Vendor_Rec.JGZZ_FISCAL_CODE := i.taxpayer_id;
ap_vendor_pub_pkg.update_vendor_public( p_api_version => 1,--
x_return_status => lc_return_status, --
x_msg_count => ln_msg_count, --
x_msg_data => ll_msg_data, --
p_vendor_rec => Lrec_Vendor_Rec, --
p_Vendor_Id => Ln_Vendor_Id);
IF (lc_return_status <> 'S') THEN
IF ln_msg_count >= 1 THEN
FOR v_index IN 1..ln_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => ll_msg_data, p_msg_index_out => ln_message_int );
Ll_Msg_Data := 'UPDATE_VENDOR '||SUBSTR(Ll_Msg_Data,1,3900);
dbms_output.put_line('Ll_Msg_Data - '||Ll_Msg_Data );
END LOOP;
End If;
END IF;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('SQLERRM - '||SQLERRM );
END;
Showing posts with label AR. Show all posts
Showing posts with label AR. Show all posts
Tuesday, October 17, 2017
API to update Customer Category
DECLARE
L_ORGANIZATION_REC hz_party_v2pub.organization_rec_type;
x_profile_id NUMBER;
l_vendor_id NUMBER;
l_party_id NUMBER;
l_object_version_number NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_return_status VARCHAR2(10);
begin
for i in ( select s.party_id, s.vendor_id, 'TEST' category_code from ap_suppliers s, hz_parties hp
where s.party_id=hp.party_id
and hp.category_code is null )
loop
SELECT aps.PARTY_ID, hzp.object_version_number
into l_party_id, l_object_version_number
FROM AP_SUPPLIERS aps, HZ_PARTIES hzp
WHERE
vendor_id =i.vendor_id
AND aps.party_id = hzp.party_id;
l_organization_rec.party_rec.party_id := l_party_id;
l_organization_rec.party_rec.category_code:=i.category_code;
HZ_PARTY_V2PUB.update_organization (
p_init_msg_list => fnd_api.g_true,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => x_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
dbms_output.put_line(l_return_status);
dbms_output.put_line(l_party_id ||i.category_code );
FOR I IN 1..l_msg_count
LOOP
l_msg_data := l_msg_data||SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);
dbms_output.put_line(l_msg_data);
END LOOP ;
end loop;
exception
when others then
raise;
end;
L_ORGANIZATION_REC hz_party_v2pub.organization_rec_type;
x_profile_id NUMBER;
l_vendor_id NUMBER;
l_party_id NUMBER;
l_object_version_number NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2(4000);
l_return_status VARCHAR2(10);
begin
for i in ( select s.party_id, s.vendor_id, 'TEST' category_code from ap_suppliers s, hz_parties hp
where s.party_id=hp.party_id
and hp.category_code is null )
loop
SELECT aps.PARTY_ID, hzp.object_version_number
into l_party_id, l_object_version_number
FROM AP_SUPPLIERS aps, HZ_PARTIES hzp
WHERE
vendor_id =i.vendor_id
AND aps.party_id = hzp.party_id;
l_organization_rec.party_rec.party_id := l_party_id;
l_organization_rec.party_rec.category_code:=i.category_code;
HZ_PARTY_V2PUB.update_organization (
p_init_msg_list => fnd_api.g_true,
p_organization_rec => l_organization_rec,
p_party_object_version_number => l_object_version_number,
x_profile_id => x_profile_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
dbms_output.put_line(l_return_status);
dbms_output.put_line(l_party_id ||i.category_code );
FOR I IN 1..l_msg_count
LOOP
l_msg_data := l_msg_data||SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);
dbms_output.put_line(l_msg_data);
END LOOP ;
end loop;
exception
when others then
raise;
end;
Monday, July 17, 2017
Please correct the receivable account assignment while running Autoinvoice Master Program
Cause:
Receivable Account and Tax Account are missing in Transaction Types.
Solution.
Receivable Manager / Setup/ Transactions/ Transaction Types
Fill Receivable Account and Tax Account fields.
Receivable Account and Tax Account are missing in Transaction Types.
Solution.
Receivable Manager / Setup/ Transactions/ Transaction Types
Fill Receivable Account and Tax Account fields.
Monday, April 24, 2017
API to Create Memo Line
declare
v_row_id varchar2(250);
v_memo_line_id number;
V_org_id number:=93;
V_SET_OF_BOOKS_ID NUMBER:=2022;
V_LE_TYPE VARCHAR2(200):='LINE';
V_START_DATE DATE:= sysdate;
V_GL_ID_REV NUMBER :=4095;
V_TAX_CODE VARCHAR2(200):='VAT18_EXCLUDE';
V_UOM_CODE VARCHAR2(200):='EA';
V_UNIT_STD_PRICE NUMBER:=1;
V_VOICG_RULE_ID NUMBER;
V_ACCOUNTG_RULE_ID NUMBER;
V_END_DATE DATE;
V_ATTRIBUTE_CATEGORY VARCHAR2(200);
V_ATTRIBUTE1 VARCHAR2(200);
V_ATTRIBUTE2 VARCHAR2(200);
V_ATTRIBUTE3 VARCHAR2(200);
V_ATTRIBUTE4 VARCHAR2(200);
V_ATTRIBUTE5 VARCHAR2(200);
V_ATTRIBUTE6 VARCHAR2(200);
V_ATTRIBUTE7 VARCHAR2(200);
V_ATTRIBUTE8 VARCHAR2(200);
V_ATTRIBUTE9 VARCHAR2(200);
V_ATTRIBUTE10 VARCHAR2(200);
V_ATTRIBUTE11 VARCHAR2(200);
V_ATTRIBUTE12 VARCHAR2(200);
V_ATTRIBUTE13 VARCHAR2(200);
V_ATTRIBUTE14 VARCHAR2(200);
V_ATTRIBUTE15 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(200);
V_GLOBAL_ATTRIBUTE1 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE2 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE3 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE4 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE5 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE6 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE7 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE8 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE9 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE10 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE11 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE12 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE13 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE14 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE15 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE16 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE17 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE18 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE19 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE20 VARCHAR2(200);
V_NAME VARCHAR2(200):='tetetete';
V_DESCRIPTION VARCHAR2(200):='tetete';
V_CREATION_DATE DATE:=sysdate;
V_CREATED_BY NUMBER;
V_LAST_UPDATE_DATE DATE;
V_LAST_UPDATED_BY NUMBER;
V_LAST_UPDATE_LOG NUMBER;
V_TAX_PRODUCT_CATEGORY VARCHAR2(200);
cursor c_memo_lines is
select ml.name,
ml.description,
ml.line_type,
ml.start_date,
ml.gl_id_rev,
ml.revenue_account,
ml.tax_code,
ml.uom_code,
ml.unit_std_price,
ml.memo_line_id,
ml.org_id,
ml.set_of_books_id
from temp_memo_line ml
;
begin
for c in c_memo_lines
loop
select ar_memo_lines_s.nextval into v_memo_line_id from dual;
AR_MEMO_LINES_PKG.LOAD_ROW(
X_org_id =>c.org_id,
X_MEMO_LINE_ID =>v_memo_line_id,
X_SET_OF_BOOKS_ID =>c.set_of_books_id,
X_LINE_TYPE =>c.line_type,
X_START_DATE =>c.start_date,
X_GL_ID_REV =>c.gl_id_rev,
X_TAX_CODE =>c.tax_code,
X_UOM_CODE =>c.uom_code,
X_UNIT_STD_PRICE => c.unit_std_price,
X_INVOICING_RULE_ID =>V_VOICG_RULE_ID,
X_ACCOUNTING_RULE_ID =>V_ACCOUNTG_RULE_ID,
X_END_DATE =>V_END_DATE,
X_ATTRIBUTE_CATEGORY =>V_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>V_ATTRIBUTE1,
X_ATTRIBUTE2 =>V_ATTRIBUTE2,
X_ATTRIBUTE3 =>V_ATTRIBUTE3,
X_ATTRIBUTE4 =>V_ATTRIBUTE4,
X_ATTRIBUTE5 =>V_ATTRIBUTE5,
X_ATTRIBUTE6 =>V_ATTRIBUTE6,
X_ATTRIBUTE7 =>V_ATTRIBUTE7,
X_ATTRIBUTE8 =>V_ATTRIBUTE8,
X_ATTRIBUTE9 =>V_ATTRIBUTE9,
X_ATTRIBUTE10 =>V_ATTRIBUTE10,
X_ATTRIBUTE11 =>V_ATTRIBUTE11,
X_ATTRIBUTE12 =>V_ATTRIBUTE12,
X_ATTRIBUTE13 =>V_ATTRIBUTE13,
X_ATTRIBUTE14 =>V_ATTRIBUTE14,
X_ATTRIBUTE15 =>V_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE_CATEGORY =>V_GLOBAL_ATTRIBUTE_CATEGORY,
X_GLOBAL_ATTRIBUTE1 =>V_GLOBAL_ATTRIBUTE1,
X_GLOBAL_ATTRIBUTE2 =>V_GLOBAL_ATTRIBUTE2,
X_GLOBAL_ATTRIBUTE3 =>V_GLOBAL_ATTRIBUTE3,
X_GLOBAL_ATTRIBUTE4 =>V_GLOBAL_ATTRIBUTE4,
X_GLOBAL_ATTRIBUTE5 =>V_GLOBAL_ATTRIBUTE5,
X_GLOBAL_ATTRIBUTE6 =>V_GLOBAL_ATTRIBUTE6,
X_GLOBAL_ATTRIBUTE7 =>V_GLOBAL_ATTRIBUTE7,
X_GLOBAL_ATTRIBUTE8 =>V_GLOBAL_ATTRIBUTE8,
X_GLOBAL_ATTRIBUTE9 =>V_GLOBAL_ATTRIBUTE9,
X_GLOBAL_ATTRIBUTE10 =>V_GLOBAL_ATTRIBUTE10,
X_GLOBAL_ATTRIBUTE11 =>V_GLOBAL_ATTRIBUTE11,
X_GLOBAL_ATTRIBUTE12 =>V_GLOBAL_ATTRIBUTE12,
X_GLOBAL_ATTRIBUTE13 =>V_GLOBAL_ATTRIBUTE13,
X_GLOBAL_ATTRIBUTE14 =>V_GLOBAL_ATTRIBUTE14,
X_GLOBAL_ATTRIBUTE15 =>V_GLOBAL_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE16 =>V_GLOBAL_ATTRIBUTE16,
X_GLOBAL_ATTRIBUTE17 =>V_GLOBAL_ATTRIBUTE17,
X_GLOBAL_ATTRIBUTE18 =>V_GLOBAL_ATTRIBUTE18,
X_GLOBAL_ATTRIBUTE19 =>V_GLOBAL_ATTRIBUTE19,
X_GLOBAL_ATTRIBUTE20 =>V_GLOBAL_ATTRIBUTE20,
X_NAME =>c.name,
X_DESCRIPTION =>c.description,
X_OWNER => 'SEED',
/*X_CREATION_DATE =>V_CREATION_DATE,
X_CREATED_BY =>V_CREATED_BY,
X_LAST_UPDATE_DATE =>V_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY =>V_LAST_UPDATED_BY,
X_LAST_UPDATE_LOG =>V_LAST_UPDATE_LOG,*/
X_TAX_PRODUCT_CATEGORY =>V_TAX_PRODUCT_CATEGORY
);
commit;
end loop;
end;
v_row_id varchar2(250);
v_memo_line_id number;
V_org_id number:=93;
V_SET_OF_BOOKS_ID NUMBER:=2022;
V_LE_TYPE VARCHAR2(200):='LINE';
V_START_DATE DATE:= sysdate;
V_GL_ID_REV NUMBER :=4095;
V_TAX_CODE VARCHAR2(200):='VAT18_EXCLUDE';
V_UOM_CODE VARCHAR2(200):='EA';
V_UNIT_STD_PRICE NUMBER:=1;
V_VOICG_RULE_ID NUMBER;
V_ACCOUNTG_RULE_ID NUMBER;
V_END_DATE DATE;
V_ATTRIBUTE_CATEGORY VARCHAR2(200);
V_ATTRIBUTE1 VARCHAR2(200);
V_ATTRIBUTE2 VARCHAR2(200);
V_ATTRIBUTE3 VARCHAR2(200);
V_ATTRIBUTE4 VARCHAR2(200);
V_ATTRIBUTE5 VARCHAR2(200);
V_ATTRIBUTE6 VARCHAR2(200);
V_ATTRIBUTE7 VARCHAR2(200);
V_ATTRIBUTE8 VARCHAR2(200);
V_ATTRIBUTE9 VARCHAR2(200);
V_ATTRIBUTE10 VARCHAR2(200);
V_ATTRIBUTE11 VARCHAR2(200);
V_ATTRIBUTE12 VARCHAR2(200);
V_ATTRIBUTE13 VARCHAR2(200);
V_ATTRIBUTE14 VARCHAR2(200);
V_ATTRIBUTE15 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE_CATEGORY VARCHAR2(200);
V_GLOBAL_ATTRIBUTE1 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE2 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE3 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE4 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE5 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE6 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE7 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE8 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE9 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE10 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE11 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE12 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE13 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE14 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE15 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE16 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE17 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE18 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE19 VARCHAR2(200);
V_GLOBAL_ATTRIBUTE20 VARCHAR2(200);
V_NAME VARCHAR2(200):='tetetete';
V_DESCRIPTION VARCHAR2(200):='tetete';
V_CREATION_DATE DATE:=sysdate;
V_CREATED_BY NUMBER;
V_LAST_UPDATE_DATE DATE;
V_LAST_UPDATED_BY NUMBER;
V_LAST_UPDATE_LOG NUMBER;
V_TAX_PRODUCT_CATEGORY VARCHAR2(200);
cursor c_memo_lines is
select ml.name,
ml.description,
ml.line_type,
ml.start_date,
ml.gl_id_rev,
ml.revenue_account,
ml.tax_code,
ml.uom_code,
ml.unit_std_price,
ml.memo_line_id,
ml.org_id,
ml.set_of_books_id
from temp_memo_line ml
;
begin
for c in c_memo_lines
loop
select ar_memo_lines_s.nextval into v_memo_line_id from dual;
AR_MEMO_LINES_PKG.LOAD_ROW(
X_org_id =>c.org_id,
X_MEMO_LINE_ID =>v_memo_line_id,
X_SET_OF_BOOKS_ID =>c.set_of_books_id,
X_LINE_TYPE =>c.line_type,
X_START_DATE =>c.start_date,
X_GL_ID_REV =>c.gl_id_rev,
X_TAX_CODE =>c.tax_code,
X_UOM_CODE =>c.uom_code,
X_UNIT_STD_PRICE => c.unit_std_price,
X_INVOICING_RULE_ID =>V_VOICG_RULE_ID,
X_ACCOUNTING_RULE_ID =>V_ACCOUNTG_RULE_ID,
X_END_DATE =>V_END_DATE,
X_ATTRIBUTE_CATEGORY =>V_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>V_ATTRIBUTE1,
X_ATTRIBUTE2 =>V_ATTRIBUTE2,
X_ATTRIBUTE3 =>V_ATTRIBUTE3,
X_ATTRIBUTE4 =>V_ATTRIBUTE4,
X_ATTRIBUTE5 =>V_ATTRIBUTE5,
X_ATTRIBUTE6 =>V_ATTRIBUTE6,
X_ATTRIBUTE7 =>V_ATTRIBUTE7,
X_ATTRIBUTE8 =>V_ATTRIBUTE8,
X_ATTRIBUTE9 =>V_ATTRIBUTE9,
X_ATTRIBUTE10 =>V_ATTRIBUTE10,
X_ATTRIBUTE11 =>V_ATTRIBUTE11,
X_ATTRIBUTE12 =>V_ATTRIBUTE12,
X_ATTRIBUTE13 =>V_ATTRIBUTE13,
X_ATTRIBUTE14 =>V_ATTRIBUTE14,
X_ATTRIBUTE15 =>V_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE_CATEGORY =>V_GLOBAL_ATTRIBUTE_CATEGORY,
X_GLOBAL_ATTRIBUTE1 =>V_GLOBAL_ATTRIBUTE1,
X_GLOBAL_ATTRIBUTE2 =>V_GLOBAL_ATTRIBUTE2,
X_GLOBAL_ATTRIBUTE3 =>V_GLOBAL_ATTRIBUTE3,
X_GLOBAL_ATTRIBUTE4 =>V_GLOBAL_ATTRIBUTE4,
X_GLOBAL_ATTRIBUTE5 =>V_GLOBAL_ATTRIBUTE5,
X_GLOBAL_ATTRIBUTE6 =>V_GLOBAL_ATTRIBUTE6,
X_GLOBAL_ATTRIBUTE7 =>V_GLOBAL_ATTRIBUTE7,
X_GLOBAL_ATTRIBUTE8 =>V_GLOBAL_ATTRIBUTE8,
X_GLOBAL_ATTRIBUTE9 =>V_GLOBAL_ATTRIBUTE9,
X_GLOBAL_ATTRIBUTE10 =>V_GLOBAL_ATTRIBUTE10,
X_GLOBAL_ATTRIBUTE11 =>V_GLOBAL_ATTRIBUTE11,
X_GLOBAL_ATTRIBUTE12 =>V_GLOBAL_ATTRIBUTE12,
X_GLOBAL_ATTRIBUTE13 =>V_GLOBAL_ATTRIBUTE13,
X_GLOBAL_ATTRIBUTE14 =>V_GLOBAL_ATTRIBUTE14,
X_GLOBAL_ATTRIBUTE15 =>V_GLOBAL_ATTRIBUTE15,
X_GLOBAL_ATTRIBUTE16 =>V_GLOBAL_ATTRIBUTE16,
X_GLOBAL_ATTRIBUTE17 =>V_GLOBAL_ATTRIBUTE17,
X_GLOBAL_ATTRIBUTE18 =>V_GLOBAL_ATTRIBUTE18,
X_GLOBAL_ATTRIBUTE19 =>V_GLOBAL_ATTRIBUTE19,
X_GLOBAL_ATTRIBUTE20 =>V_GLOBAL_ATTRIBUTE20,
X_NAME =>c.name,
X_DESCRIPTION =>c.description,
X_OWNER => 'SEED',
/*X_CREATION_DATE =>V_CREATION_DATE,
X_CREATED_BY =>V_CREATED_BY,
X_LAST_UPDATE_DATE =>V_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY =>V_LAST_UPDATED_BY,
X_LAST_UPDATE_LOG =>V_LAST_UPDATE_LOG,*/
X_TAX_PRODUCT_CATEGORY =>V_TAX_PRODUCT_CATEGORY
);
commit;
end loop;
end;
Monday, April 17, 2017
API to create AR customer
declare
l_num_user_id NUMBER;
l_num_appl_id NUMBER;
l_num_resp_id NUMBER;
cust_account_rec_type hz_cust_account_v2pub.cust_account_rec_type;
l_num_obj_ver_num NUMBER;
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (500);
l_num_profile_id NUMBER;
cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
l_organization_rec hz_party_v2pub.organization_rec_type;
l_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
l_num_cust_id NUMBER;
l_chr_acct_num VARCHAR2 (500);
l_num_party_id NUMBER;
l_chr_party_number VARCHAR2 (500);
l_cust_acct_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
l_chr_sit_return_status VARCHAR2 (500);
l_num_sit_msg_count NUMBER;
l_chr_sit_msg_data VARCHAR2 (500);
l_chr_situ_return_status VARCHAR2 (500);
l_num_situ_msg_count NUMBER;
l_chr_situ_msg_data VARCHAR2 (500);
l_num_site_use_id NUMBER;
CURSOR update_base_tables_cur
IS
select distinct ps.party_site_name, a.party_number, ps.party_site_number, a.party_id,
'CUST-' ||a.party_id||'-'||ass.org_id as customer_ref,
'ADD-'||ps.party_site_id||'-'||ass.org_id as address_ref,
ps.party_site_id,
ass.address_line1,
ass.city, ass.state, ass.zip, ass.country,
'N' primary_site_use_flag,
a.orig_system_reference as party_ref,
a.party_name as customer_name, ass.org_id
from apps.ap_suppliers ap,
apps.ap_supplier_sites_all ass,
apps.hz_parties a,
apps.hz_party_sites ps
where ap.vendor_id = ass.vendor_id
and a.party_id = ap.party_id
and ps.party_id = ap.party_id
and ass.party_site_id = ps.party_site_id
and a.party_name = ap.vendor_name
and not exists (select * from hz_cust_accounts ac
where ac.party_id = a.party_id);
begin
fnd_global.apps_initialize( 6837, 50546, 222); -- input from 1st sql
dbms_output.put_line('***************************');
for update_base_tables_rec in update_base_tables_cur
loop
null;
cust_account_rec_type.cust_account_id := fnd_api.g_miss_num;
cust_account_rec_type.account_name := update_base_tables_rec.customer_name;
cust_account_rec_type.customer_type := 'R';
--l_organization_rec.party_rec_type.category_code := 'ROAMING';
l_organization_rec.party_rec.party_id := update_base_tables_rec.party_id;
l_organization_rec.party_rec.party_number := update_base_tables_rec.party_number;
l_organization_rec.organization_name := update_base_tables_rec.customer_name;
cust_account_rec_type.orig_system_reference := update_base_tables_rec.customer_ref;
l_customer_profile_rec.party_id := update_base_tables_rec.party_id;
l_customer_profile_rec.profile_class_id := 0 ; -- use default profile with id=0
l_customer_profile_rec.created_by_module := 'HZ_CPUI';
cust_account_rec_type.created_by_module := 'HZ_CPUI';
hz_cust_account_v2pub.create_cust_account
(p_init_msg_list => fnd_api.g_false,
p_cust_account_rec => cust_account_rec_type,
p_organization_rec => l_organization_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile_amt => fnd_api.g_true,
x_cust_account_id => l_num_cust_id,
x_account_number => l_chr_acct_num,
x_party_id => l_num_party_id,
x_party_number => l_chr_party_number,
x_profile_id => l_num_profile_id,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data
);
dbms_output.put_line('x_return_status: '||l_chr_return_status);
dbms_output.put_line('x_cust_account_id: '||l_num_cust_id);
dbms_output.put_line('x_account_number: '||l_chr_acct_num);
dbms_output.put_line('x_party_id: '||l_num_party_id);
dbms_output.put_line(l_chr_return_status ||' ' ||l_chr_msg_data );
if l_chr_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
-- dbms_output.put_line (j);
l_chr_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
-- dbms_output.put_line ('Message(' || j || '):= ' || l_chr_msg_data);
end loop;
end if;
begin
select cust_account_id
into l_cust_acct_site_rec.cust_account_id
from hz_cust_accounts
where orig_system_reference = update_base_tables_rec.customer_ref;
dbms_output.put_line(2 );
exception
when others
then
l_cust_acct_site_rec.cust_account_id := fnd_api.g_miss_num;
end;
l_cust_acct_site_rec.party_site_id := update_base_tables_rec.party_site_id;
l_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
l_cust_acct_site_rec.orig_system_reference := update_base_tables_rec.address_ref;
l_cust_acct_site_rec.org_id := update_base_tables_rec.org_id;
mo_global.init ('ONT');
mo_global.set_policy_context (p_access_mode => 'S',
p_org_id => update_base_tables_rec.org_id
);
dbms_output.put_line(3 );
hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => 'T',
p_cust_acct_site_rec => l_cust_acct_site_rec,
x_cust_acct_site_id => l_num_obj_ver_num,
x_return_status => l_chr_sit_return_status,
x_msg_count => l_num_sit_msg_count,
x_msg_data => l_chr_sit_msg_data
);
dbms_output.put_line('x_cust_acct_site_id: '||l_num_obj_ver_num);
dbms_output.put_line('x_return_status: '||l_chr_sit_return_status);
dbms_output.put_line(4||' '|| l_chr_sit_return_status );
if l_chr_sit_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
l_chr_sit_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
end loop;
end if;
begin
select cust_acct_site_id
into l_cust_acct_site_use_rec.cust_acct_site_id
from hz_cust_acct_sites_all
where orig_system_reference = update_base_tables_rec.address_ref;
exception
when others
then
l_cust_acct_site_use_rec.cust_acct_site_id := fnd_api.g_miss_num;
end;
l_cust_acct_site_use_rec.org_id := update_base_tables_rec.org_id;
l_cust_acct_site_use_rec.site_use_code := 'BILL_TO';
l_cust_acct_site_use_rec.status := 'A';
l_cust_acct_site_use_rec.location := update_base_tables_rec.party_site_name;
l_cust_acct_site_use_rec.primary_flag := 'Y';
l_cust_acct_site_use_rec.orig_system_reference :=
update_base_tables_rec.address_ref;
l_cust_acct_site_use_rec.created_by_module := 'HZ_CPUI';
mo_global.set_policy_context (p_access_mode => 'S',
p_org_id => update_base_tables_rec.org_id
);
hz_cust_account_site_v2pub.create_cust_site_use
(p_init_msg_list => 'T',
p_cust_site_use_rec => l_cust_acct_site_use_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile => fnd_api.g_true,
p_create_profile_amt => fnd_api.g_true,
x_site_use_id => l_num_site_use_id,
x_return_status => l_chr_situ_return_status,
x_msg_count => l_num_situ_msg_count,
x_msg_data => l_chr_situ_msg_data
);
dbms_output.put_line('x_site_use_id: '||l_num_site_use_id);
dbms_output.put_line('x_return_status: '||l_chr_situ_return_status);
if l_chr_situ_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
l_chr_situ_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
end loop;
end if;
end loop;
end;
l_num_user_id NUMBER;
l_num_appl_id NUMBER;
l_num_resp_id NUMBER;
cust_account_rec_type hz_cust_account_v2pub.cust_account_rec_type;
l_num_obj_ver_num NUMBER;
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (500);
l_num_profile_id NUMBER;
cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
l_organization_rec hz_party_v2pub.organization_rec_type;
l_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
l_num_cust_id NUMBER;
l_chr_acct_num VARCHAR2 (500);
l_num_party_id NUMBER;
l_chr_party_number VARCHAR2 (500);
l_cust_acct_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
l_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
l_chr_sit_return_status VARCHAR2 (500);
l_num_sit_msg_count NUMBER;
l_chr_sit_msg_data VARCHAR2 (500);
l_chr_situ_return_status VARCHAR2 (500);
l_num_situ_msg_count NUMBER;
l_chr_situ_msg_data VARCHAR2 (500);
l_num_site_use_id NUMBER;
CURSOR update_base_tables_cur
IS
select distinct ps.party_site_name, a.party_number, ps.party_site_number, a.party_id,
'CUST-' ||a.party_id||'-'||ass.org_id as customer_ref,
'ADD-'||ps.party_site_id||'-'||ass.org_id as address_ref,
ps.party_site_id,
ass.address_line1,
ass.city, ass.state, ass.zip, ass.country,
'N' primary_site_use_flag,
a.orig_system_reference as party_ref,
a.party_name as customer_name, ass.org_id
from apps.ap_suppliers ap,
apps.ap_supplier_sites_all ass,
apps.hz_parties a,
apps.hz_party_sites ps
where ap.vendor_id = ass.vendor_id
and a.party_id = ap.party_id
and ps.party_id = ap.party_id
and ass.party_site_id = ps.party_site_id
and a.party_name = ap.vendor_name
and not exists (select * from hz_cust_accounts ac
where ac.party_id = a.party_id);
begin
fnd_global.apps_initialize( 6837, 50546, 222); -- input from 1st sql
dbms_output.put_line('***************************');
for update_base_tables_rec in update_base_tables_cur
loop
null;
cust_account_rec_type.cust_account_id := fnd_api.g_miss_num;
cust_account_rec_type.account_name := update_base_tables_rec.customer_name;
cust_account_rec_type.customer_type := 'R';
--l_organization_rec.party_rec_type.category_code := 'ROAMING';
l_organization_rec.party_rec.party_id := update_base_tables_rec.party_id;
l_organization_rec.party_rec.party_number := update_base_tables_rec.party_number;
l_organization_rec.organization_name := update_base_tables_rec.customer_name;
cust_account_rec_type.orig_system_reference := update_base_tables_rec.customer_ref;
l_customer_profile_rec.party_id := update_base_tables_rec.party_id;
l_customer_profile_rec.profile_class_id := 0 ; -- use default profile with id=0
l_customer_profile_rec.created_by_module := 'HZ_CPUI';
cust_account_rec_type.created_by_module := 'HZ_CPUI';
hz_cust_account_v2pub.create_cust_account
(p_init_msg_list => fnd_api.g_false,
p_cust_account_rec => cust_account_rec_type,
p_organization_rec => l_organization_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile_amt => fnd_api.g_true,
x_cust_account_id => l_num_cust_id,
x_account_number => l_chr_acct_num,
x_party_id => l_num_party_id,
x_party_number => l_chr_party_number,
x_profile_id => l_num_profile_id,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data
);
dbms_output.put_line('x_return_status: '||l_chr_return_status);
dbms_output.put_line('x_cust_account_id: '||l_num_cust_id);
dbms_output.put_line('x_account_number: '||l_chr_acct_num);
dbms_output.put_line('x_party_id: '||l_num_party_id);
dbms_output.put_line(l_chr_return_status ||' ' ||l_chr_msg_data );
if l_chr_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
-- dbms_output.put_line (j);
l_chr_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
-- dbms_output.put_line ('Message(' || j || '):= ' || l_chr_msg_data);
end loop;
end if;
begin
select cust_account_id
into l_cust_acct_site_rec.cust_account_id
from hz_cust_accounts
where orig_system_reference = update_base_tables_rec.customer_ref;
dbms_output.put_line(2 );
exception
when others
then
l_cust_acct_site_rec.cust_account_id := fnd_api.g_miss_num;
end;
l_cust_acct_site_rec.party_site_id := update_base_tables_rec.party_site_id;
l_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
l_cust_acct_site_rec.orig_system_reference := update_base_tables_rec.address_ref;
l_cust_acct_site_rec.org_id := update_base_tables_rec.org_id;
mo_global.init ('ONT');
mo_global.set_policy_context (p_access_mode => 'S',
p_org_id => update_base_tables_rec.org_id
);
dbms_output.put_line(3 );
hz_cust_account_site_v2pub.create_cust_acct_site
(p_init_msg_list => 'T',
p_cust_acct_site_rec => l_cust_acct_site_rec,
x_cust_acct_site_id => l_num_obj_ver_num,
x_return_status => l_chr_sit_return_status,
x_msg_count => l_num_sit_msg_count,
x_msg_data => l_chr_sit_msg_data
);
dbms_output.put_line('x_cust_acct_site_id: '||l_num_obj_ver_num);
dbms_output.put_line('x_return_status: '||l_chr_sit_return_status);
dbms_output.put_line(4||' '|| l_chr_sit_return_status );
if l_chr_sit_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
l_chr_sit_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
end loop;
end if;
begin
select cust_acct_site_id
into l_cust_acct_site_use_rec.cust_acct_site_id
from hz_cust_acct_sites_all
where orig_system_reference = update_base_tables_rec.address_ref;
exception
when others
then
l_cust_acct_site_use_rec.cust_acct_site_id := fnd_api.g_miss_num;
end;
l_cust_acct_site_use_rec.org_id := update_base_tables_rec.org_id;
l_cust_acct_site_use_rec.site_use_code := 'BILL_TO';
l_cust_acct_site_use_rec.status := 'A';
l_cust_acct_site_use_rec.location := update_base_tables_rec.party_site_name;
l_cust_acct_site_use_rec.primary_flag := 'Y';
l_cust_acct_site_use_rec.orig_system_reference :=
update_base_tables_rec.address_ref;
l_cust_acct_site_use_rec.created_by_module := 'HZ_CPUI';
mo_global.set_policy_context (p_access_mode => 'S',
p_org_id => update_base_tables_rec.org_id
);
hz_cust_account_site_v2pub.create_cust_site_use
(p_init_msg_list => 'T',
p_cust_site_use_rec => l_cust_acct_site_use_rec,
p_customer_profile_rec => l_customer_profile_rec,
p_create_profile => fnd_api.g_true,
p_create_profile_amt => fnd_api.g_true,
x_site_use_id => l_num_site_use_id,
x_return_status => l_chr_situ_return_status,
x_msg_count => l_num_situ_msg_count,
x_msg_data => l_chr_situ_msg_data
);
dbms_output.put_line('x_site_use_id: '||l_num_site_use_id);
dbms_output.put_line('x_return_status: '||l_chr_situ_return_status);
if l_chr_situ_return_status != 'S'
then
--display all the error messages
for j in 1 .. fnd_msg_pub.count_msg
loop
l_chr_situ_msg_data :=
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F');
end loop;
end if;
end loop;
end;