最近工作中要添加PARTY里面的PROFILE,因为界面上没有添加和修改的地方。可能ORACLE是希望这些PROFILE都从PROFILE CLASS那里带过来,不希望维护客户的地方改这些PROFILE.
不过TCA API还是能实现改PROFILE和PROFILE AMT的值。
下面是CREATE PROFILE , CREATE PROFILE AMT & UPDATE PROFILE AMT的脚本,希望对大家有用。
[@more@]--CREATE CUSTOMER PROFILE & CUSTOMER PROFILE AMT
declare
-- Local variables here
p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
p_cust_acct_relate_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCT_RELATE_REC_TYPE;
p_cust_profile_amt_rec HZ_CUSTOMER_PROFILE_V2PUB.cust_profile_amt_rec_type;
p_PROFILE_CLASS_ID NUMBER;
x_cust_account_id NUMBER;
x_account_number VARCHAR2(20);
x_party_id NUMBER;
x_party_number VARCHAR2(20);
x_profile_id NUMBER;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_cust_account_profile_id NUMBER;
x_cust_acct_profile_amt_id NUMBER;
begin
-- Test statements here
-- Create Customer Profile Amount Record
p_customer_profile_rec.PARTY_ID := 1820253;
p_customer_profile_rec.created_by_module := 'TCA_V2_API';
p_cust_profile_amt_rec.currency_code := 'CNY';
p_cust_profile_amt_rec.trx_credit_limit := 50000;
p_cust_profile_amt_rec.overall_credit_limit := 200000;
p_cust_profile_amt_rec.created_by_module := 'TCA_V2_API';
HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile
(
'T',
p_customer_profile_rec ,
'T',--p_customer_profile_amt,
x_cust_account_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_profile_id: '||x_cust_account_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('***************************');
p_cust_profile_amt_rec.cust_account_profile_id := x_cust_account_profile_id;
p_cust_profile_amt_rec.cust_account_id := -1;
HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt (
'T',
'T',
p_cust_profile_amt_rec ,
x_cust_acct_profile_amt_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_profile_amt_id: '||x_cust_acct_profile_amt_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('***************************');
end;
--UPDATE PROFLIE AMT
declare
-- Local variables here
p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
p_cust_acct_relate_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCT_RELATE_REC_TYPE;
p_cust_profile_amt_rec HZ_CUSTOMER_PROFILE_V2PUB.cust_profile_amt_rec_type;
P_cust_account_profile_id NUMBER;
p_cust_acct_profile_amt_id NUMBER;
p_PROFILE_CLASS_ID NUMBER;
x_profile_id NUMBER;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_cust_account_profile_id NUMBER;
x_cust_acct_profile_amt_id NUMBER;
x_object_version_number NUMBER ;
begin
-- Test statements here
-- Create Customer Profile Amount Record
p_cust_profile_amt_rec.currency_code := 'CNY';
p_cust_profile_amt_rec.trx_credit_limit := 7000;
p_cust_profile_amt_rec.overall_credit_limit := 10000;
--p_cust_profile_amt_rec.created_by_module := 'TCA_V2_API';
BEGIN
SELECT cust_account_profile_id
INTO P_cust_account_profile_id
FROM hz_customer_profiles
WHERE party_id = 1819254
AND cust_account_id = -1;
EXCEPTION
WHEN OTHERS THEN
P_cust_account_profile_id:= NULL;
END;
BEGIN
SELECT cust_acct_profile_amt_id ,object_version_number
INTO p_cust_acct_profile_amt_id,x_object_version_number
FROM HZ_CUST_PROFILE_AMTS
WHERE cust_account_profile_id = P_cust_account_profile_id
AND currency_code = 'CNY';
EXCEPTION
WHEN OTHERS THEN
p_cust_acct_profile_amt_id:= NULL;
x_object_version_number:= 0;
END;
p_cust_profile_amt_rec.cust_account_profile_id := P_cust_account_profile_id;
p_cust_profile_amt_rec.cust_acct_profile_amt_id := p_cust_acct_profile_amt_id;
p_cust_profile_amt_rec.cust_account_id := -1;
HZ_CUSTOMER_PROFILE_V2PUB.update_cust_profile_amt (
'T',
p_cust_profile_amt_rec ,
x_object_version_number,
x_return_status ,
x_msg_count,
x_msg_data
);
dbms_output.put_line('***************************');
dbms_output.put_line('Output information ....');
-- dbms_output.put_line('x_object_version_number: '||x_object_version_number);
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('***************************');
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13247/viewspace-1060267/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13247/viewspace-1060267/