AR Receipts Creation Using API's


AR Receipts API Single Insert Creation Script

1.Creating a RECEIPT :

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_cr_id NUMBER;

BEGIN
ar_receipt_api_pub.create_cash
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => 'USD',
p_amount => 200,
p_receipt_number => 'XX_TEST1',
p_receipt_date => SYSDATE,
p_gl_date => SYSDATE,
p_customer_id => Customer id ,
p_customer_bank_account_id => ,
p_customer_site_use_id => ,
p_receipt_method_id => ,
p_cr_id => l_cr_id );

DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Status ' l_return_status);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF
l_msg_count > 1
THEN LOOP
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' '---' ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;
/

2. Applying to an Invoice:

DECLARE 
l_return_status VARCHAR2 (1); 
l_msg_count NUMBER; 
l_msg_data VARCHAR2 (240);

BEGIN 
ar_receipt_api_pub.APPLY 
(p_api_version => 1.0, 
p_init_msg_list => fnd_api.g_false, 
p_commit => fnd_api.g_false, 
p_validation_level => fnd_api.g_valid_level_full, 
x_return_status => l_return_status, 
x_msg_count => l_msg_count, 
x_msg_data => l_msg_data, 
p_cash_receipt_id => , 
p_customer_trx_id => , 
p_applied_payment_schedule_id => , 
p_amount_applied => , 
p_show_closed_invoices => 'Y', 
p_apply_date => SYSDATE, 
p_apply_gl_date => SYSDATE, 
p_line_number => 1 ); 

DBMS_OUTPUT.put_line ('Message count ' l_msg_count); 
DBMS_OUTPUT.put_line ('Status ' l_return_status);

IF l_msg_count = 1 
THEN 
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data); 
ELSIF l_msg_count > 1 
THEN LOOP 
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_msg_data IS NULL 
THEN 
EXIT; 
END IF; 

DBMS_OUTPUT.put_line ('Message' '---' ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;

3. UNApplying a receipt to an Invoice: 

DECLARE 
l_return_status VARCHAR2 (1); 
l_msg_count NUMBER; 
l_msg_data VARCHAR2 (240); 
l_count NUMBER; 
l_cash_receipt_id NUMBER; 
l_msg_data_out VARCHAR2 (240); 
l_mesg VARCHAR2 (240); 
p_count NUMBER;

BEGIN 
arp_standard.enable_debug; 
arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log');

ar_receipt_api_pub.unapply 
(p_api_version => 1.0, 
p_init_msg_list => fnd_api.g_true, 
p_commit => fnd_api.g_true, 
p_validation_level => fnd_api.g_valid_level_full, 
x_return_status => l_return_status, 
x_msg_count => l_msg_count, 
x_msg_data => l_msg_data, 
p_cash_receipt_id => &cash_receipt_id, 
p_applied_payment_schedule_id => &applied_payment_schedule_id, 
p_reversal_gl_date => '&reversal_gl_date' ); 

DBMS_OUTPUT.put_line ('Message count ' l_msg_count); 
DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id); 

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF l_msg_count > 1
THEN LOOP
p_count := p_count + 1;
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_msg_data IS NULL
THEN EXIT;
END IF;

DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;
/

4.CREATE AND APPLY A Receipt:

DECLARE 
l_return_status VARCHAR2 (1); 
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_count NUMBER; 
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2 (240); 
l_mesg VARCHAR2 (240); 
p_count NUMBER;

BEGIN 
DBMS_OUTPUT.put_line ('Start'); 
arp_standard.enable_debug; 

ar_receipt_api_pub.create_and_apply 
(p_api_version => 1.0, 
p_init_msg_list => fnd_api.g_true, 
p_commit => fnd_api.g_true, 
p_validation_level => fnd_api.g_valid_level_full, 
p_currency_code => 'USD', 
x_return_status => l_return_status,
x_msg_count => l_msg_count, 
x_msg_data => l_msg_data, 
p_amount => , 
p_receipt_number => ' ', 
p_receipt_date => SYSDATE, 
p_gl_date => SYSDATE, 
p_customer_id => , 
p_customer_bank_account_id => , 
p_customer_site_use_id => , 
p_receipt_method_id => , 
p_trx_number => ' ', 
p_cr_id => l_cash_receipt_id ); D

BMS_OUTPUT.put_line ('Return_status ' l_return_status); 
DBMS_OUTPUT.put_line ('Message count ' l_msg_count); 
DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id);

IF l_msg_count = 1 
THEN 
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data); 
ELSIF 
l_msg_count > 1 
THEN LOOP 
p_count := p_count + 1; 

l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL 
THEN 
EXIT; 
END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data); 
END LOOP; 
END IF;
arp_standard.disable_debug;
END;
/ 
5. Miscellanous receipt Creation:

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER; l_msg_data VARCHAR2 (240); l_count NUMBER; l_cash_receipt_id NUMBER; l_msg_data_out VARCHAR2 (240); l_mesg VARCHAR2 (240); p_count NUMBER; l_receipt_number VARCHAR (10);BEGIN l_receipt_number := '123'; DBMS_OUTPUT.put_line ('Start'); arp_standard.enable_debug; arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log'); ar_receipt_api_pub.create_misc (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, p_validation_level => fnd_api.g_valid_level_full, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_currency_code => 'USD', p_amount => &amount, p_receipt_date => '&receipt_date', p_gl_date => '&gl_date', p_receipt_method_id => &receipt_method_id, p_activity => 'Interest Income', p_misc_receipt_id => l_cash_receipt_id, p_receipt_number => l_receipt_number ); DBMS_OUTPUT.put_line ('Message count ' l_msg_count); DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id); DBMS_OUTPUT.put_line ('Status ' l_return_status);
IF l_msg_count = 1 THEN DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data); ELSIF l_msg_count > 1 THEN LOOP p_count := p_count + 1; l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL THEN EXIT; END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data); END LOOP; END IF;
arp_standard.disable_debug;END;/

6.REVERSING a Receipt:

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER; l_msg_data VARCHAR2 (240); l_count NUMBER; l_cash_receipt_id NUMBER; l_msg_data_out VARCHAR2 (240); l_mesg VARCHAR2 (240); p_count NUMBER;BEGIN arp_standard.enable_debug; arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log'); ar_receipt_api_pub.REVERSE (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, p_validation_level => fnd_api.g_valid_level_full, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_receipt_number => '&receipt_number', p_reversal_category_name => 'Reverse Payment', --values can be modified. p_reversal_reason_name => 'Nsf' -- values can be modified. ); DBMS_OUTPUT.put_line ('Message count ' l_msg_count); DBMS_OUTPUT.put_line ('Status ' l_return_status);
IF l_msg_count = 1 THEN DBMS_OUTPUT.put_line ( 'l_msg_data A ' RTRIM (LTRIM (l_msg_data)) '****' ); ELSIF l_msg_count > 1 THEN LOOP p_count := p_count + 1; l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL THEN EXIT; END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data); END LOOP; END IF;
arp_standard.disable_debug;END;/

Comments

Post a Comment

Popular posts from this blog

Queries For Oracle Interface Errors Records.

Below are the queries to get the error records information while processing interface data. AP Invoices:   SELECT    airl.last_update_date process_date,    HU.NAME,   nvl(ail.invoice_line_id,aii.invoice_id) process_id,   'AP' module,   'Import AP Invoice' interface_name,   aii.source source,   NVL(SUBSTR( ail.dist_code_concatenated,1,instr(ail.dist_code_concatenated,'.',1,1)-1),'ALL') company,   'ERROR' status,   ail.line_type_lookup_code ,   airl.parent_table ,   ail.description ,   NVL(ail.distribution_set_name,dist_code_concatenated) ,   pvsa.vendor_site_code ,   listagg(NVL(airl.reject_lookup_code,'AP_INV_ERROR') ,';') within group (order by NVL(airl.reject_lookup_code,'AP_INV_ERROR')) error_code,   listagg(flv.meaning,';')  within group (order by NVL(flv.meaning,'AP_INV_ERROR'))error_summary,   NULL error_details,   pv.vendor_name ,   pv.segment1 ,   aii.invoice_amount,   aii.i
Read more

Customising PO Output For Communication Report in Oracle Purchasing

Image
PO Print PDF Report: Oracle EBS provided  a standard functionality to print the PO report directly from the PO form itself by clicking tools menu and view PDF option. Once we select that option output will be rendered in PDF format in the browser instantly. Internally it will run the concurrent program 'PO Output For Communication'. This concurrent program is of JAVA type which will uses the seeded package  'PO_COMMUNICATION_PVT'  for generating the XML data and later this XML data will be processed and formatted by using the XSL template of data definition 'Standard Purchase Order Data Source' in case of standard PO's. Oracle by default will provide one standard BI publisher template for this. In case if we want to have custom layout for this report we can build a new template for the same data definition and this template has to be used while doing the setup for PO Document types. Creating Custom Template and Setup this for PO Document type: To c
Read more

Oracle APPS Useful Queries

                       ACCOUNT PAYABLES Payable Open Interface Rejection:   SELECT ail.* FROM AP_INVOICES_INTERFACE ai ,   ap_invoice_lines_interface ail ,   ap_interface_rejections AIR WHERE ai.GROUP_ID='EO_AUG-13_010813_443' AND ai.status    ='REJECTED' AND ai.invoice_id=ail.invoice_id   --and ail.dist_code_concatenated='66810-00-999-00000000-00000000-00000-FI100' AND air.parent_id = DECODE(air.parent_table,'AP_INVOICES_INTERFACE',AI.INVOICE_ID,'AP_INVOICE_LINES_INTERFACE',ail.invoice_line_id,NULL) ORDER BY 2; AP Invoices Query: /*+INDEX (AP_INVOICES_ALL [XXDG_TEST]) */ SELECT  apia.invoice_id,        alc.displayed_field "Invoice Type",        apia.invoice_num "Invoice Number",        apia.invoice_date "Invoice Date",        apia.gl_date   "Invoice GL Date",        nvl(apia.VOUCHER_NUM,doc_sequence_value)"Voucher Number",        apia.description inv_Description,  
Read more