原創 利用TCA-API建PARTY的PROFILE以及PROFILE AMT

最近工作中要新增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;