最近工作中要添加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./13247/viewspace-1060267/,如需转载,请注明出处,否则将追究法律责任。
|