Recently I had a requirement to move unapplied AR receipts from one customer to another customer and apply to the related customer invoice.
I did not find any API to update the customer details of an unapplied receipt, nasty way to update the customer on unapplied receipt is to update related base tables (as_cash_receipts_all and ar_payment_schedules_all), I won't suggest to go with this.
There is an API to move unidentified receipt (Receipt without customer details) to unapplied receipts.
So solution offered was to run data loader for unapplied receipts to remove customers to make those to unidentified receipts and then use API ar_receipt_update_api_pub.update_receipt_unid_to_unapp to make it unapplied on required customer and then using API apps.ar_receipt_api_pub.apply, apply that receipt to related customer invocie.
Please find the sample script for API 'ar_receipt_update_api_pub.update_receipt_unid_to_unapp'
---------------------------------------------------------
declare
cursor cur_unid_rcpts
is
select acr.org_id
,acr.receipt_number
,hca.cust_account_id
,acr.cash_receipt_id
from ar_cash_receipts_all acr
,hz_cust_accounts hca
where acr.status = 'UNID'
-- Account number stored in attribute3
and trim(leading 0 from acr.attribute3 )= hca.account_number
and acr.type = 'CASH'
and acr.receipt_number = 'AKTest'--<<ReceiptNumber>>
order by acr.receipt_number;
x_return_status varchar2 (200);
x_msg_count number;
x_msg_data varchar2 (200);
x_status varchar2 (200);
p_count number;
begin
for rec_unid_rcpts in cur_unid_rcpts loop
-- Setting up the org context
apps.mo_global.init ('AR');
mo_global.set_policy_context('S' , rec_unid_rcpts.org_id) ;
ar_receipt_update_api_pub.update_receipt_unid_to_unapp
(p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => apps.fnd_api.g_false,
p_validation_level => apps.fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cash_receipt_id => rec_unid_rcpts.cash_receipt_id,
p_pay_from_customer => rec_unid_rcpts.cust_account_id,
x_status => x_status
);
dbms_output.put_line ('API Status = ' || x_return_status);
dbms_output.put_line ('Receipt Status = ' || x_status);
if x_msg_count = 1 then
dbms_output.put_line ('API Error Msg: ' || x_msg_data);
elsif x_msg_count > 1 then
for i in 1 .. x_msg_count
loop
x_msg_data := fnd_msg_pub.get (i, fnd_api.g_false);
dbms_output.put_line ('Error Message_' || i || ': ' || x_msg_data);
end loop;
else
-- Commit the changes
commit;
end if;
end loop;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
end;
---------------------------------------------------------
I did not find any API to update the customer details of an unapplied receipt, nasty way to update the customer on unapplied receipt is to update related base tables (as_cash_receipts_all and ar_payment_schedules_all), I won't suggest to go with this.
There is an API to move unidentified receipt (Receipt without customer details) to unapplied receipts.
So solution offered was to run data loader for unapplied receipts to remove customers to make those to unidentified receipts and then use API ar_receipt_update_api_pub.update_receipt_unid_to_unapp to make it unapplied on required customer and then using API apps.ar_receipt_api_pub.apply, apply that receipt to related customer invocie.
Please find the sample script for API 'ar_receipt_update_api_pub.update_receipt_unid_to_unapp'
---------------------------------------------------------
declare
cursor cur_unid_rcpts
is
select acr.org_id
,acr.receipt_number
,hca.cust_account_id
,acr.cash_receipt_id
from ar_cash_receipts_all acr
,hz_cust_accounts hca
where acr.status = 'UNID'
-- Account number stored in attribute3
and trim(leading 0 from acr.attribute3 )= hca.account_number
and acr.type = 'CASH'
and acr.receipt_number = 'AKTest'--<<ReceiptNumber>>
order by acr.receipt_number;
x_return_status varchar2 (200);
x_msg_count number;
x_msg_data varchar2 (200);
x_status varchar2 (200);
p_count number;
begin
for rec_unid_rcpts in cur_unid_rcpts loop
-- Setting up the org context
apps.mo_global.init ('AR');
mo_global.set_policy_context('S' , rec_unid_rcpts.org_id) ;
ar_receipt_update_api_pub.update_receipt_unid_to_unapp
(p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => apps.fnd_api.g_false,
p_validation_level => apps.fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cash_receipt_id => rec_unid_rcpts.cash_receipt_id,
p_pay_from_customer => rec_unid_rcpts.cust_account_id,
x_status => x_status
);
dbms_output.put_line ('API Status = ' || x_return_status);
dbms_output.put_line ('Receipt Status = ' || x_status);
if x_msg_count = 1 then
dbms_output.put_line ('API Error Msg: ' || x_msg_data);
elsif x_msg_count > 1 then
for i in 1 .. x_msg_count
loop
x_msg_data := fnd_msg_pub.get (i, fnd_api.g_false);
dbms_output.put_line ('Error Message_' || i || ': ' || x_msg_data);
end loop;
else
-- Commit the changes
commit;
end if;
end loop;
exception
when others then
dbms_output.put_line('Error: '||sqlerrm);
end;
---------------------------------------------------------
Hi Abhay Kumar,
ReplyDeleteI was reproduced your code for change Receipt from Unidentified to Unapplied but I get this message:
X_MSG_COUNT = 1
X_MSG_DATA = Location could not be defaulted for the specified customer.
Receipt Status = X_STATUS =
l_msg_data Location could not be defaulted for the specified customer.
Wold you help me?
Any idea?
Tks in avanced,
Regards,
Jorge Aparicio (japaricio_lopez@hotmail.com)
HI Jorge,
DeleteIt looks like the flag 'Require Billing Location for Receipts' for the customer you are using is yes but does not have primary bill to site.
Check the 'Require Billing Location for Receipts' flag for the customer you are using.
Receivables > Setup > System > System Options >> Miscellaneous tab.
I hope this will help.
Regards
Abhay
Hi Abhay,
DeleteFirst of all, thank you very much for your help.
Indeed, 'Require Billing Location for Receipts' was 'YES'
I changed the api and it worked, but not very well.
Now I have another problem.
The API does not generate this information on the screen when inquiring receipt, Customer Name, Customer Number, Location.
Normally, if I change the status of the receipt manually, by entering the number of the customer in full automatic the other boxes, Customer Name and Location.
any suggest pls.
Tks in advantage.
Hi Abhay,
ReplyDeleteFirst of all, thank you very much for your help.
Indeed, 'Require Billing Location for Receipts' was 'YES'
I changed it and the API worked, but not very well.
Now I have another problem.
The API does not generate this information on the screen when inquiring receipt, Customer Name, Customer Number, Location.
Normally, if I change the status of the receipt manually, by entering the number of the customer in full automatic the other boxes, Customer Name and Location.
Any idea?
Tanks in avantage.
Hi Jorge,
DeleteCustomer details should appear on screen. I hope you are enquiring the receipt number in the correct operating unit. You can have same receipt number in different operating units.