DECLARE K_api_version CONSTANT NUMBER := 1; K_init_msg_list CONSTANT VARCHAR2(1) := FND_API.g_false; K_comments CONSTANT ar_receivable_applications.comments%TYPE := 'FR billing File invoice issue For DEC-2019'; K_commit CONSTANT VARCHAR2(1) := FND_API.g_false; l_acctd_amount_applied_from ar_receivable_applications_all. acctd_amount_applied_from%TYPE; l_acctd_amount_applied_to ar_receivable_applications_all. acctd_amount_applied_to%TYPE; l_cm_app_rec AR_CM_API_PUB.cm_app_rec_type; l_msg_count NUMBER; l_msg_data VARCHAR2(2555); l_out_rec_application_id NUMBER; l_return_status VARCHAR2(1); cursor c1 is select * from im_fr_cm_data where 1=1 and process_flag is null; ------and trx_number not in('19052767','19052768'); --and trx_number = '19052768'; v_cm_trx_id number; v_trx_id number; v_inv_amount number; v_cm_amount number; BEGIN Mo_global.init('AR'); Mo_global.set_policy_context('S', 376); -- Set User/Resp ------Fnd_Global.apps_initialize(111872,52711,222); Fnd_Global.apps_initialize(96746,52711,222); for r1 in c1 loop begin select a.customer_trx_id,sum(extended_amount) into v_cm_trx_id,v_cm_amount from apps.ra_customer_trx_lines_all b ,apps.ra_customer_trx_all a where 1=1 and a.customer_trx_id = b.customer_trx_id and a.trx_number = r1.cm_number and a.org_id = 376 group by a.trx_number,a.customer_trx_id; exception when others then dbms_output.put_line('Error at CM invoice selection '||r1.trx_number||'_1'||' is '||SQLERRM); end; begin select a.customer_trx_id,sum(extended_amount) into v_trx_id,v_inv_amount from apps.ra_customer_trx_lines_all b ,apps.ra_customer_trx_all a where 1=1 and a.customer_trx_id = b.customer_trx_id and a.trx_number = r1.trx_number and a.org_id = 376 group by a.trx_number,a.customer_trx_id; exception when others then dbms_output.put_line('Error at invoice selection '||r1.trx_number||' is '||SQLERRM); end; if v_cm_amount*-1 = v_inv_amount then l_cm_app_rec.cm_customer_trx_id := v_cm_trx_id;---52672249; l_cm_app_rec.cm_trx_number := null; -- Credit Memo Number l_cm_app_rec.inv_customer_trx_id := v_trx_id;---52622215; l_cm_app_rec.inv_trx_number := null; -- Invoice Number l_cm_app_rec.installment := null; l_cm_app_rec.applied_payment_schedule_id := null; l_cm_app_rec.amount_applied := v_inv_amount;----65.80; l_cm_app_rec.apply_date := TRUNC(TO_DATE('01-MAR-2020','DD-MON-YYYY')); l_cm_app_rec.gl_date := TRUNC(TO_DATE('01-MAR-2020','DD-MON-YYYY')); l_cm_app_rec.inv_customer_trx_line_id := null; l_cm_app_rec.inv_line_number := null; l_cm_app_rec.show_closed_invoices := null; l_cm_app_rec.ussgl_transaction_code := null; l_cm_app_rec.attribute_category := null; l_cm_app_rec.attribute1 := null; l_cm_app_rec.attribute2 := null; l_cm_app_rec.attribute3 := null; l_cm_app_rec.attribute4 := null; l_cm_app_rec.attribute5 := null; l_cm_app_rec.attribute6 := null; l_cm_app_rec.attribute7 := null; l_cm_app_rec.attribute8 := null; l_cm_app_rec.attribute9 := null; l_cm_app_rec.attribute10 := null; l_cm_app_rec.attribute11 := null; l_cm_app_rec.attribute12 := null; l_cm_app_rec.attribute13 := null; l_cm_app_rec.attribute14 := null; l_cm_app_rec.attribute15 := null; l_cm_app_rec.global_attribute_category := null; l_cm_app_rec.global_attribute1 := null; l_cm_app_rec.global_attribute2 := null; l_cm_app_rec.global_attribute3 := null; l_cm_app_rec.global_attribute4 := null; l_cm_app_rec.global_attribute5 := null; l_cm_app_rec.global_attribute6 := null; l_cm_app_rec.global_attribute7 := null; l_cm_app_rec.global_attribute8 := null; l_cm_app_rec.global_attribute9 := null; l_cm_app_rec.global_attribute10 := null; l_cm_app_rec.global_attribute11 := null; l_cm_app_rec.global_attribute12 := null; l_cm_app_rec.global_attribute12 := null; l_cm_app_rec.global_attribute14 := null; l_cm_app_rec.global_attribute15 := null; l_cm_app_rec.global_attribute16 := null; l_cm_app_rec.global_attribute17 := null; l_cm_app_rec.global_attribute18 := null; l_cm_app_rec.global_attribute19 := null; l_cm_app_rec.global_attribute20 := null; l_cm_app_rec.comments := K_comments; l_cm_app_rec.called_from := null; ar_cm_api_pub.apply_on_account(p_api_version => K_api_version, p_init_msg_list => K_init_msg_list, p_commit => K_commit, p_cm_app_rec => l_cm_app_rec, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, x_out_rec_application_id => l_out_rec_application_id, x_acctd_amount_applied_from => l_acctd_amount_applied_from, x_acctd_amount_applied_to => l_acctd_amount_applied_to); COMMIT; dbms_output.put_line('return_status: ' || l_return_status); dbms_output.put_line('msg_count: ' || l_msg_count); dbms_output.put_line('out_rec_application_id: ' || l_out_rec_application_id); dbms_output.put_line('acctd_amount_applied_from: ' || l_acctd_amount_applied_from); dbms_output.put_line('acctd_amount_applied_to: ' || l_acctd_amount_applied_to); if l_return_status = 'S' then update im_fr_cm_data set process_flag = 'P' where trx_number = r1.trx_number and process_flag is null; commit; IF l_msg_count = 1 THEN dbms_output.put_line(l_msg_data); ELSIF l_msg_count > 1 THEN FOR I IN 1 .. l_msg_count LOOP dbms_output.put_line(I || '. ' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API. G_FALSE), 1, 255)); END LOOP; END IF; else update im_fr_cm_data set process_flag = 'E' where trx_number = r1.trx_number and process_flag is null; commit; end if; else dbms_output.put_line('CM and INV amounts are not matching '||r1.trx_number||' CM amount '||v_cm_amount||' and INV amount '||v_inv_amount); end if; end loop; END;