Oracle EBS (ERP) Apps R12 Functional & Technical Solutions

Wednesday, June 24, 2026

How to find the RMA order receipt details in Oracle Apps R12

SELECT ooha.ORDER_NUMBER "SALES ORDER"

,ooha.ORDER_CATEGORY_CODE

,oola.ORDERED_ITEM

,oola.SUBINVENTORY

,rsh.SHIPMENT_NUM

,rsh.RECEIPT_NUM

,rsh.CUSTOMER_ID

,rsl.UNIT_OF_MEASURE

,rsl.ITEM_DESCRIPTION

,rsl.SHIPMENT_LINE_STATUS_CODE

,rsl.SOURCE_DOCUMENT_CODE

FROM OE_ORDER_HEADERS_ALL ooha

,OE_ORDER_LINES_ALL oola

,RCV_SHIPMENT_HEADERS rsh

,RCV_SHIPMENT_LINES rsl

WHERE 1=1

AND ooha.header_id = oola.header_id

AND ooha.header_id = rsl.OE_ORDER_HEADER_ID

AND rsh.shipment_header_id = rsl.shipment_header_id

AND rsl.OE_ORDER_LINE_ID = o ola.line_id

AND ooha.ORDER_NUMBER = '56'

AND rsl.SOURCE_DOCUMENT_CODE = 'RMA';

Payables invoice prepayment query in Oracle Apps R12

SELECT pv.vendor_name C_vendor_name,

pvs.address_line1 C_address_line1,

pvs.address_line2 C_address_line2,

pvs.address_line3 C_address_line3,

DECODE (pvs.city, '', '', pvs.city || ', ')

|| DECODE (pvs.state, '', '', pvs.state || ' ')

|| pvs.zip

C_city_state_zip,

pvs.country C_country,

aipp.last_update_date C_application_date,

aipp.prepayment_amount_applied C_amount_applied,

inv.invoice_currency_code C_currency_code,

pp.invoice_num C_prepay_num,

inv.invoice_num C_invoice_num,

NVL (inv.invoice_amount, 0) - NVL (inv.amount_paid, 0)

C_amt_remaining

FROM ap_suppliers pv,

ap_supplier_sites_all pvs,

ap_invoices_all inv,

ap_invoices_all pp,

ap_invoice_prepays_all aipp

WHERE aipp.invoice_id = inv.invoice_id

AND aipp.prepay_id = pp.invoice_id

AND inv.vendor_id = pp.vendor_id

AND inv.vendor_id = pv.vendor_id

AND pv.vendor_id = pvs.vendor_id

AND pvs.vendor_site_id = inv.vendor_site_id

AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'

AND aipp.last_update_date >= &InvDate

UNION

SELECT pv.vendor_name C_vendor_name,

pvs.address_line1 C_address_line1,

pvs.address_line2 C_address_line2,

pvs.address_line3 C_address_line3,

DECODE (pvs.city, '', '', pvs.city || ', ')

|| DECODE (pvs.state, '', '', pvs.state || ' ')

|| pvs.zip

C_city_state_zip,

pvs.country C_country,

aid2.last_update_date C_application_date,

NVL (

ap_invoices_utility_pkg.get_pp_amt_applied_on_date (

inv.invoice_id,

pp.invoice_id,

aid2.last_update_date

)

C_amount_applied,

inv.invoice_currency_code C_currency_code,

pp.invoice_num C_prepay_num,

inv.invoice_num C_invoice_num,

NVL (inv.invoice_amount, 0)

- (ap_invoices_pkg.get_prepaid_amount (inv.invoice_id))

C_amt_remaining

FROM ap_suppliers pv,

ap_supplier_sites_all pvs,

ap_invoices_all inv,

ap_invoices_all pp,

ap_invoice_distributions_all aid1,

ap_invoice_distributions_all aid2

WHERE aid1.invoice_id = inv.invoice_id

AND aid2.invoice_id = pp.invoice_id

AND aid2.invoice_distribution_id = aid1.prepay_distribution_id

AND aid1.line_type_lookup_code = 'PREPAY'

AND inv.vendor_id = pp.vendor_id

AND inv.vendor_id = pv.vendor_id

AND pv.vendor_id = pvs.vendor_id

AND pvs.vendor_site_id = inv.vendor_site_id

AND NVL (aid1.reversal_flag, 'N') != 'Y'

AND NVL (pvs.LANGUAGE, 'AMERICAN') = 'AMERICAN'

AND inv.invoice_date >= &InvDat

How to create MISCELLANEOUS Receipt in AR in Oracle Apps R12

--CREATING A PROCEDURE TO CREATE MISCELLANEOUS RECEIPT FOR 3400 org

CREATE OR REPLACE PROCEDURE xxfin_create_misc_rec_prc(

errbuff OUT VARCHAR2,

retcode OUT VARCHAR2,

ip_receipt_num IN VARCHAR2)

p_api_version NUMBER;

p_init_msg_list VARCHAR2(200);

p_commit VARCHAR2(200);

p_validation_level NUMBER;

x_return_status VARCHAR2(200);

x_msg_count NUMBER;

x_msg_data VARCHAR2(200);

p_usr_currency_code VARCHAR2(200);

p_currency_code VARCHAR2(200);

p_usr_exchange_rate_type VARCHAR2(200);

p_exchange_rate_type VARCHAR2(200);

p_exchange_rate NUMBER;

p_exchange_rate_date DATE;

p_amount NUMBER;

p_org_id NUMBER;

p_receipt_number VARCHAR2(200);

p_receipt_date DATE;

p_gl_date DATE;

p_receivables_trx_id NUMBER;

p_activity VARCHAR2(200) DEFAULT NULL;

p_misc_payment_source VARCHAR2(200):='Created by New Custom Receipt Screen';

p_tax_code VARCHAR2(200);

p_vat_tax_id VARCHAR2(200);

p_tax_rate NUMBER;

p_tax_amount NUMBER DEFAULT NULL;

p_deposit_date DATE;

p_reference_type VARCHAR2(200);

p_reference_num VARCHAR2(200);

p_reference_id NUMBER;

p_remittance_bank_account_id NUMBER;

p_remittance_bank_account_num VARCHAR2(200);

p_remittance_bank_account_name VARCHAR2(200);

p_receipt_method_id NUMBER;

p_receipt_method_name VARCHAR2(200);

p_doc_sequence_value NUMBER;

p_ussgl_transaction_code VARCHAR2(200);

p_anticipated_clearing_date DATE;

p_attribute_record AR_RECEIPT_API_PUB.attribute_rec_type;

p_global_attribute_record AR_RECEIPT_API_PUB.global_attribute_rec_type;

p_comments VARCHAR2(200);

p_misc_receipt_id NUMBER;

p_called_from VARCHAR2(200);

gc_user_name VARCHAR2 (100);

gc_responsibility_name VARCHAR2 (100);

gc_application_short_name VARCHAR2 (100);

gc_org_id NUMBER;

l_error_msg VARCHAR2 (2000);

l_receipt_id NUMBER;

l_bank_ref_number VARCHAR2(100);

lv_receipt_method VARCHAR2(150);

ln_receipt_method_id NUMBER(15);

lv_receipt_num VARCHAR2(30);

ln_user_id NUMBER:=fnd_global.user_id;

l_attribute_rec ar_receipt_api_pub.attribute_rec_type;

CURSOR cu_login_variables

SELECT fu.user_id user_id,

frv.responsibility_id resp_id,

fav.application_id resp_appl_id

FROM fnd_application_vl fav,

fnd_responsibility_vl frv,

fnd_user fu

WHERE fu.user_id = ln_user_id

AND frv.responsibility_name = gc_responsibility_name

AND fav.application_short_name = gc_application_short_name;

lr_login_variables cu_login_variables%ROWTYPE;

CURSOR misc_receipts

SELECT SUM(xct.amount_applied) amount ,

xcr.comments ,

xcr.ATTRIBUTE_CATEGORY ,

xcr.ATTRIBUTE1 ,

xcr.ATTRIBUTE2 ,

xcr.ATTRIBUTE3 ,

xcr.ATTRIBUTE4 ,

xcr.attribute5 ,

xcr.receipt_date ,

xcr.gl_date ,

xct.invoice_currency_code ,

XCR.RECEIPT_METHOD_ID ,

XCR.RECEIPT_METHOD ,

xcr.receipt_number

FROM xxfin_custom_receipt xcr ,

xxfin_cust_trx xct

WHERE xcr.receipt_number=xct.receipt_number

--and xct.status='Receipt Applied'

AND xcr.receipt_number=ip_receipt_num

GROUP BY xcr.comments,

xcr.ATTRIBUTE_CATEGORY,

xcr.ATTRIBUTE1,

xcr.ATTRIBUTE2,

xcr.ATTRIBUTE3,

xcr.ATTRIBUTE4,

xcr.attribute5,

xcr.receipt_date,

xcr.gl_date,

XCT.INVOICE_CURRENCY_CODE,

XCR.RECEIPT_METHOD_ID,

XCR.RECEIPT_METHOD,

xcr.receipt_number;

BEGIN

SELECT lookup_code,

meaning,

description,

tag

INTO gc_org_id,

gc_user_name,

gc_responsibility_name,

gc_application_short_name

FROM fnd_lookup_values_vl

WHERE enabled_flag = 'Y'

AND lookup_code = 3400

AND lookup_type = 'XX_APPLICATION_INITIATION';

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_error_msg := 'select for XX_APPLICATION_INITIATION failed. ';

fnd_file.put_line (fnd_file.LOG, l_error_msg);

RAISE;

WHEN TOO_MANY_ROWS THEN

l_error_msg := 'select for XX_APPLICATION_INITIATION failed due to too many rows. ';

fnd_file.put_line (fnd_file.LOG, l_error_msg);

RAISE;

WHEN OTHERS THEN

l_error_msg := 'select for XX_APPLICATION_INITIATION failed due to other reasons ';

fnd_file.put_line (fnd_file.LOG, l_error_msg);

RAISE;

END;

OPEN cu_login_variables;

FETCH cu_login_variables INTO lr_login_variables;

CLOSE cu_login_variables;

BEGIN

FOR rec_misc IN misc_receipts

LOOP

----------------------------changin receipt method internally------------------------------------------

BEGIN

lv_receipt_num :=rec_misc.receipt_number;

lv_receipt_method:=rec_misc.receipt_method;

IF lv_receipt_method LIKE '%Cash%' THEN

ln_receipt_method_id:=102;

elsif lv_receipt_method LIKE '%Cheque%' THEN

ln_receipt_method_id:=103;

elsif lv_receipt_method LIKE '%PDC%' THEN

ln_receipt_method_id:=103;

elsif lv_receipt_method LIKE '%Bank Transfer%' THEN

ln_receipt_method_id:=105;

ELSE

ln_receipt_method_id:=rec_misc.receipt_method_id;

END IF;

END;

---------------------------------------end receipt method changing internally---------------------------

fnd_global.apps_initialize ( lr_login_variables.user_id

, lr_login_variables.resp_id

, lr_login_variables.resp_appl_id );

mo_global.init (gc_application_short_name);

mo_global.set_policy_context ('S', 3400);

fnd_file.put_line(fnd_file.log,'Application Code :' ||gc_application_short_name);

p_receipt_date := SYSDATE;

p_gl_date := rec_misc.gl_date ;

p_misc_receipt_id := NULL;

l_attribute_rec.attribute_category:=rec_misc.attribute_category;

l_attribute_rec.attribute2 :=rec_misc.attribute1;

l_attribute_rec.attribute3 :=rec_misc.attribute2;

l_attribute_rec.attribute4 :=rec_misc.attribute4;

l_attribute_rec.attribute5 :=rec_misc.attribute3;

l_attribute_rec.attribute8 :=NVL(rec_misc.attribute5,'000000');

SELECT receivables_trx_id

INTO p_receivables_trx_id

FROM AR_RECEIVABLES_TRX_ALL

WHERE name IN

(SELECT meaning

FROM ar_lookups

WHERE lookup_type = 'XXIMD_MISC_RECEIPT_ACTIVITY'

) ;

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 => x_return_status

, x_msg_count => x_msg_count

, x_msg_data => x_msg_data

, p_usr_currency_code => p_usr_currency_code

, p_currency_code => rec_misc.invoice_currency_code

, p_usr_exchange_rate_type => p_usr_exchange_rate_type

, p_exchange_rate_type => p_exchange_rate_type

, p_exchange_rate => p_exchange_rate

, p_exchange_rate_date => p_exchange_rate_date

, p_amount => rec_misc.amount

, p_receipt_number => lv_receipt_num

, p_receipt_date => TRUNC(SYSDATE)

, p_gl_date => rec_misc.gl_date

, p_receivables_trx_id => p_receivables_trx_id

, p_activity => p_activity

, p_misc_payment_source => p_misc_payment_source

, p_tax_code => p_tax_code

, p_vat_tax_id => p_vat_tax_id

, p_tax_rate => p_tax_rate

, p_tax_amount => p_tax_amount

, p_deposit_date => TRUNC(SYSDATE)

, p_reference_type => p_reference_type

, p_reference_num => p_reference_num

, p_reference_id => p_reference_id

, p_remittance_bank_account_id => p_remittance_bank_account_id

, p_remittance_bank_account_num => p_remittance_bank_account_num

, p_remittance_bank_account_name => p_remittance_bank_account_name

, p_receipt_method_id => ln_receipt_method_id

, p_receipt_method_name => lv_receipt_method

, p_doc_sequence_value => p_doc_sequence_value

, p_ussgl_transaction_code => p_ussgl_transaction_code

, p_anticipated_clearing_date => p_anticipated_clearing_date

, p_comments => rec_misc.comments

, p_attribute_record => l_attribute_rec

, p_misc_receipt_id => p_misc_receipt_id

, p_called_from => p_called_from

, P_Org_Id => 3400 );

IF (x_return_status = 'S') THEN

COMMIT;

fnd_file.put_line(fnd_file.log,'SUCCESS');

ELSE

ROLLBACK;

fnd_file.put_line(fnd_file.log,'ERROR');

fnd_file.put_line(fnd_file.log,'Return Status = '|| SUBSTR (x_return_status,1,255)||','||x_msg_data);

fnd_file.put_line(fnd_file.log,APPS.FND_MSG_PUB.Get ( p_msg_index => APPS.FND_MSG_PUB.G_LAST, p_encoded => APPS.FND_API.G_FALSE));

IF x_msg_count >=0 THEN

FOR I IN 1..10

LOOP

fnd_file.put_line(fnd_file.log,I||'. '|| SUBSTR (FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));

END LOOP;

END IF;

END LOOP;

fnd_file.put_line(fnd_file.log,'After end loop');

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line(fnd_file.log,'Exception :'||sqlerrm);

END;

COMMIT;

END xxfin_create_misc_rec_prc;

--END OF CREATING xxfin_create_misc_rec_prc.

Sample control file for GL daily rates interface program in Oracle Apps R12

-- Sample control file

Options (Skip =2)

Load data

infile '$XBOL_TOP/bin/xe.csv'

badfile '$XBOL_TOP/bin/xe.bad'

discardfile '$XBOL_TOP/bin/xe.dis'

insert into table xxgl_exchange_rates_stg

fields terminated by ','

optionally enclosed by '"'

Trailing nullcols

(from_currency

,amount

,from_conversion_date

,to_currency

,conversion_rate)

Customer invoices interface by using API in Oracle Apps R12

create or replace package body XBOL_SORDER_PKG is

p_batch_process VARCHAR2 (1);

gc_user_name VARCHAR2 (100);

gc_responsibility_name VARCHAR2 (100);

gc_application_short_name VARCHAR2 (100);

gc_org_id NUMBER;

-- This procedure will CREATE customer invoices in ra_customer_trx_table.

-------------------------------------------------------------------------

PROCEDURE XBOL_CREATE_AR_TRANSACTION(errbuf OUT VARCHAR2,

retcode OUT VARCHAR2,

p_wonum IN VARCHAR2)

ip_trx_number NUMBER;

l_return_status VARCHAR2 (1);

l_msg_count NUMBER;

l_msg_data VARCHAR2 (2000);

l_batch_id NUMBER;

l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;

l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;

l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;

l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;

l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;

l_trx_contingencies_tbl ar_invoice_api_pub.trx_contingencies_tbl_type;

trx_header_id_v NUMBER;

trx_line_id_v NUMBER;

trx_dist_id_v NUMBER;

ip_code_combination_id VARCHAR2 (80);

ip_org_id NUMBER;

lv_msg_data VARCHAR2 (1000);

lv_ret_code VARCHAR2 (1);

lv_msg_data_receipt VARCHAR2 (1000);

lv_ret_code_receipt VARCHAR2 (1);

lp_receipt_id NUMBER;

lp_org_id NUMBER;

v_trx_Id NUMBER;

lv_msg_data_misc VARCHAR2 (1000);

l_batch_source_id NUMBER;

l_default_term_id NUMBER;

l_cust_trx_type_id NUMBER;

l_customer_id NUMBER;

l_customer_number VARCHAR2 (30);

l_project_code VARCHAR2 (240);

l_responsibility_id NUMBER;

l_application_id NUMBER;

l_error_flag VARCHAR2 (1):='S';

l_error_msg VARCHAR2 (1000);

l_bank_name VARCHAR2 (100);

l_bank_account_id NUMBER (20);

l_trx_header_id NUMBER;

l_trx_error_header_id NUMBER;

l_desc VARCHAR2 (100);

l_wonum VARCHAR2 (100);

l_bankrefnum VARCHAR2 (50);

CURSOR c_data1

SELECT *

FROM XBOL.XXAR_INV_REC_EXTERNAL

WHERE NVL(STATUS,'A') NOT IN ('C')

AND paymentmode IN ('ONLINE', 'CASH')

AND ( (wonum = p_wonum) OR (p_wonum IS NULL));

CURSOR cBatch IS

select customer_trx_id

from ra_customer_trx_all

where batch_id = l_batch_id;

CURSOR cValidTxn IS

SELECT trx_header_id

From ar_trx_header_gt

WHERE trx_header_id not in (

SELECT trx_header_id

FROM ar_trx_errors_gt);

Begin

mo_global.set_policy_context('S',140);

FOR c_dt IN c_data1

LOOP

l_bankrefnum := c_dt.bankrefnum;

IF c_dt.description IS NULL

THEN

l_desc := c_dt.paymentmode;

ELSE

l_desc := c_dt.description;

END IF;

l_wonum := NVL (p_wonum, c_dt.wonum);

SELECT XBOL_AR_TRX_ID_S.NEXTVAL

INTO trx_header_id_v

FROM DUAL;

SELECT XBOL_AR_TRX_LINEID_S.NEXTVAL

INTO trx_line_id_v

FROM DUAL;

SELECT XBOL_AR_TRX_DISTID_S.NEXTVAL

INTO trx_dist_id_v

FROM DUAL;

SELECT b.BATCH_SOURCE_ID,

c.DEFAULT_TERM Term_id,

c.CUST_TRX_TYPE_ID

INTO l_batch_source_id, l_default_term_id, l_cust_trx_type_id

FROM hr_operating_units a,

ra_batch_sources_all b,

ra_cust_trx_types_all c

WHERE a.organization_id = b.org_id

AND a.organization_id = c.org_id

AND a.organization_id ='140'

AND b.NAME = 'MANUAL'

AND c.name IN

(SELECT meaning

FROM ar_lookups arl

WHERE lookup_type = 'XX_EPAY_TRX_TYPES'

AND lookup_code ='140')

AND c.END_DATE IS NULL;

fnd_file.put_line (fnd_file.LOG, 'FOR LOOP :'|| ' BANK REFE' ||l_bankrefnum);

BEGIN

SELECT

description

INTO l_project_code

FROM fnd_lookup_values_vl

WHERE enabled_flag = 'Y'

AND lookup_code = '140'

AND lookup_type = 'XX_EPAY_ACC_DET';

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_project_code := '000';

WHEN OTHERS

THEN

l_error_flag:='E';

FND_FILE.PUT_LINE (FND_FILE.LOG,

'Error Finding Project Code ' || SQLERRM);

END;

BEGIN

SELECT bank_account_id

INTO L_bank_account_id

FROM apps.ce_bank_accounts cba

WHERE bank_account_name IN

(SELECT tag

FROM fnd_lookup_values_vl

WHERE enabled_flag = 'Y'

AND lookup_code = '140'

AND lookup_type = 'XX_EPAY_ACC_DET');

EXCEPTION

WHEN OTHERS

THEN

l_error_flag:='E';

FND_FILE.PUT_LINE (

FND_FILE.LOG,

'Error Finding Bank Id - Please check and rerun' || SQLERRM

);

L_bank_account_id := NULL;

RAISE;

END;

BEGIN

SELECT code_combination_id

INTO ip_code_combination_id

FROM gl_code_combinations

WHERE segment1=1013 and segment2=1630 and segment3=11620 and segment4=0000 and segment5=000 and segment6=0000;

EXCEPTION

when no_data_found then

ip_code_combination_id:=10075;

WHEN OTHERS

THEN

l_error_flag:='E';

FND_FILE.PUT_LINE (FND_FILE.LOG,

'Error Finding code_combination_id ' || SQLERRM);

END;

fnd_file.put_line (

fnd_file.LOG,

'CCID'

|| ip_code_combination_id

|| 'ProjectCode'

|| l_project_code

);

fnd_file.put_line (

fnd_file.LOG,

' ip_code_combination_id:' || ip_code_combination_id

);

BEGIN

SELECT customer_id, customer_number

INTO l_customer_id, l_customer_number

FROM ar_customers

WHERE customer_name IN 'Cash Customer';

EXCEPTION

when no_data_found then

l_customer_id:=769734;

l_customer_number:=20074;

WHEN OTHERS

THEN

l_error_flag:='E';

fnd_file.put_line (fnd_file.LOG,

'Error Finding Customer ' || SQLERRM);

END;

DBMS_OUTPUT.put_line ('c_dt.siteid:' || c_dt.siteid);

--FOR CODE COMBINATION ID FOR CREATE INVOICES

l_batch_source_rec.batch_source_id := l_batch_source_id;

l_trx_header_tbl (1).trx_header_id := trx_header_id_v;

l_trx_header_tbl (1).bill_to_customer_id := l_customer_id;

l_trx_header_tbl (1).cust_trx_type_id := l_cust_trx_type_id;

l_trx_header_tbl (1).trx_date := SYSDATE;

l_trx_header_tbl (1).trx_currency := 'AED';

l_trx_header_tbl (1).term_id := l_default_term_id;

l_trx_header_tbl (1).reference_number := l_bankrefnum;

l_trx_header_tbl (1).finance_charges := NULL;

l_trx_header_tbl (1).status_trx := 'OP';

l_trx_header_tbl (1).printing_option := 'PRI';

l_trx_header_tbl (1).attribute_category :=

'Non_Property_Transactions';

l_trx_header_tbl (1).attribute11 := l_project_code;

l_trx_header_tbl (1).attribute14 := l_bankrefnum;

l_trx_header_tbl (1).attribute9 := l_bank_account_id;

l_trx_lines_tbl (1).trx_header_id := trx_header_id_v;

l_trx_lines_tbl (1).trx_line_id := trx_line_id_v;

l_trx_lines_tbl (1).line_number := 1;

l_trx_lines_tbl (1).description := l_desc;

l_trx_lines_tbl (1).quantity_invoiced := 1;

l_trx_lines_tbl (1).unit_selling_price := c_dt.paidamount;

l_trx_lines_tbl (1).line_type := 'LINE';

l_trx_lines_tbl (1).sales_order := l_wonum;

l_trx_dist_tbl (1).trx_dist_id := trx_dist_id_v;

l_trx_dist_tbl (1).trx_line_id := trx_line_id_v;

l_trx_dist_tbl (1).account_class := 'REV';

l_trx_dist_tbl (1).PERCENT := 100;

l_trx_dist_tbl (1).code_combination_id :=

TO_NUMBER (ip_code_combination_id);

ar_invoice_api_pub.CREATE_invoice (

p_api_version => 1.0,

p_batch_source_rec => l_batch_source_rec,

p_trx_header_tbl => l_trx_header_tbl,

p_trx_lines_tbl => l_trx_lines_tbl,

p_trx_dist_tbl => l_trx_dist_tbl,

p_trx_salescredits_tbl => l_trx_salescredits_tbl,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data

);

COMMIT;

fnd_file.put_line (

fnd_file.output,

'Request ID: '

|| fnd_global.conc_request_id

|| ' Date: '

|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')

);

fnd_file.put_line (fnd_file.output, ' ');

fnd_file.put_line (

fnd_file.output,

' Importing To Oracle EBS Interface '

);

fnd_file.put_line (fnd_file.output,

' ---------------------------------------------');

fnd_file.put_line (fnd_file.output, '');

fnd_file.put_line (fnd_file.LOG, 'Return Status' || l_return_status);

fnd_file.put_line (fnd_file.LOG, 'Err Message' || l_msg_data);

fnd_file.put_line (

fnd_file.LOG,'ip_code_combination_id ' || ip_code_combination_id);

IF l_return_status = fnd_api.g_ret_sts_error

OR l_return_status = fnd_api.g_ret_sts_unexp_error

THEN

fnd_file.put_line (fnd_file.LOG,'unexpected errors found! at API ');

ELSE

FOR cvalidtxnrec IN cvalidtxn

LOOP

IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)

THEN

fnd_file.put_line (fnd_file.LOG,'Invoice(s) suceessfully created!');

fnd_file.put_line (fnd_file.LOG, 'Batch ID: '

|| ar_invoice_api_pub.g_api_outputs.batch_id

);

l_batch_id := ar_invoice_api_pub.g_api_outputs.batch_id;

FOR cbatchrec IN cbatch

LOOP

fnd_file.put_line (fnd_file.LOG, 'Cust Trx Id '

|| cbatchrec.customer_trx_id

);

fnd_file.put_line (fnd_file.output, 'Cust Trx Id '

|| cbatchrec.customer_trx_id

);

v_trx_Id := cbatchrec.customer_trx_id;

END LOOP;

ELSE

fnd_file.put_line (fnd_file.LOG,'Errors found! at API');

END IF;

END LOOP;

END IF;

END LOOP;

END XBOL_CREATE_AR_TRANSACTION;

-- END FOR INVOICE CREATION PROCEDURE

end XBOL_SORDER_PKG;

How to find customer credit limit amount in Oracle Apps R12

SELECT a.overall_credit_limit

FROM HZ_CUST_PROFILE_AMTS a

, HZ_CUST_ACCOUNTS b

, ar_customers c

, hz_cust_site_uses_all d

, ar_payment_schedules_all e

, hz_cust_acct_sites_all f

, hz_party_sites g

WHERE overall_credit_limit IS NOT NULL

and a.cust_account_id = b.cust_account_id

and b.account_number = c.customer_number

and a.site_use_id = d.site_use_id

and c.customer_id = e.customer_id

--and e.STATUS <> 'CL'

--AND e.CLASS = 'INV'

and d.site_use_id = e.customer_site_use_id

and c.customer_number = p_account_number

and d.cust_acct_site_id = f.cust_acct_site_id

and g.party_site_id = f.party_site_id

and e.org_id =102;

How to create and apply receipt in AR by using API in Oracle Apps R12

--CREATING PACKAGE SPECIFICATION TO CREATE MULTIPLE RECEIPTS WITH SAME RECEIPT NUMBER FOR DIFFERENT ORGANIZATIONS.

CREATE OR REPLACE PACKAGE xxfin_cus_form_pkg

PROCEDURE xxfin_cus_form_rec_prc(

errbuff OUT VARCHAR2 ,

retcode OUT NUMBER ,

ip_receipt_num VARCHAR2 ,

ip_cus_num VARCHAR2);

PROCEDURE xxfin_cus_form_rec_apply(

ip_cash_receipt_id IN NUMBER ,

ip_cus_num IN NUMBER ,

ip_receipt_num IN VARCHAR2 ,

ip_org_id IN NUMBER ,

op_return_status1 OUT VARCHAR2 );

END xxfin_cus_form_pkg;

--CREATING PACKAGE BODY TO CREATE MULTIPLE RECEIPTS WITH SAME RECEIPT NUMBER FOR DIFFERENT ORGANIZATIONS.

CREATE OR REPLACE PACKAGE body xxfin_cus_form_pkg

--CREATING PROCEDURE TO CREATE MULTIPLE RECEIPT WITH SAME RECEIPT NUMBER FOR DIFFERENT ORG'S.

PROCEDURE xxfin_cus_form_rec_prc(

errbuff OUT VARCHAR2 ,

retcode OUT NUMBER ,

ip_receipt_num VARCHAR2 ,

ip_cus_num VARCHAR2 )

l_return_status VARCHAR2(1);

l_msg_count NUMBER;

lv_trx_number VARCHAR2(200);

l_msg_data VARCHAR2(240);

l_cash_receipt_id NUMBER;

p_count NUMBER := 0;

l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;

l_customer_trx_id NUMBER;

gc_user_name VARCHAR2 (100);

gc_responsibility_name VARCHAR2 (100);

gc_application_short_name VARCHAR2 (100);

gc_org_id NUMBER;

l_error_msg VARCHAR2 (500);

l_org_id NUMBER;

l_return_status1_apply VARCHAR2(240);

l_status VARCHAR2(30);

lv_cr_id ar_cash_receipts_all.cash_receipt_id%TYPE;

ln_user_id NUMBER;

lv_receipt_method VARCHAR2(150);

ln_receipt_method_id NUMBER(15);

CURSOR recipt_create_stg

SELECT SUM(xt.AMOUNT_APPLIED) amount,

xt.INVOICE_CURRENCY_CODE,

xr.RECEIPT_NUMBER,

xr.GL_DATE,

xt.account_number,

xr.receipt_method,

xr.RECEIPT_METHOD_ID,

xr.comments,

xr.ATTRIBUTE_CATEGORY,

xr.ATTRIBUTE1,

xr.ATTRIBUTE2,

xr.ATTRIBUTE3,

xr.ATTRIBUTE4,

xr.attribute5,

xt.org_id

FROM xxfin_custom_receipt xr,

xxfin_cust_trx xt

WHERE xr.receipt_number=xt.receipt_number

AND xt.rec_status ='Y'

AND xt.receipt_number =ip_receipt_num

AND xt.customer_num =ip_cus_num

GROUP BY xt.org_id,

xt.INVOICE_CURRENCY_CODE,

xr.RECEIPT_NUMBER,

xr.GL_DATE,

xt.account_number,

xr.RECEIPT_METHOD_ID,

xr.ATTRIBUTE_CATEGORY,

xr.ATTRIBUTE1,

xr.ATTRIBUTE2,

xr.ATTRIBUTE3,

xr.ATTRIBUTE4,

xr.ATTRIBUTE5,

xr.comments,

xr.receipt_method;

CURSOR cu_login_variables

SELECT fu.user_id user_id,

frv.responsibility_id resp_id,

fav.application_id resp_appl_id

FROM fnd_application_vl fav,

fnd_responsibility_vl frv,

fnd_user fu

WHERE fu.user_id = ln_user_id

AND frv.responsibility_name = gc_responsibility_name

AND fav.application_short_name = gc_application_short_name;

lr_login_variables cu_login_variables%ROWTYPE;

lv_receipt_number VARCHAR2(200);

BEGIN

ln_user_id:=fnd_global.user_id;

DELETE FROM xxfin_cust_trx WHERE rec_status='N';

COMMIT;

FOR cur_create_stg IN recipt_create_stg

LOOP

BEGIN

SELECT lookup_code,

meaning,

description,

tag

INTO gc_org_id,

gc_user_name,

gc_responsibility_name,

gc_application_short_name

FROM fnd_lookup_values_vl

WHERE enabled_flag = 'Y'

AND lookup_code = cur_create_stg.org_id

AND lookup_type = 'XX_APPLICATION_INITIATION12';

EXCEPTION

WHEN NO_DATA_FOUND THEN

l_error_msg := 'select for XX_APPLICATION_INITIATION12 failed. ';

fnd_file.put_line (fnd_file.LOG, l_error_msg);

RAISE;

WHEN TOO_MANY_ROWS THEN

l_error_msg := 'select for XX_APPLICATION_INITIATION12 failed due to too many rows. ';

fnd_file.put_line (fnd_file.LOG, l_error_msg);

RAISE;

END;

OPEN cu_login_variables;

FETCH cu_login_variables INTO lr_login_variables;

CLOSE cu_login_variables;

fnd_global.apps_initialize (lr_login_variables.user_id

,lr_login_variables.resp_id

,lr_login_variables.resp_appl_id );

mo_global.init(gc_application_short_name);

mo_global.set_policy_context('S',cur_create_stg.org_id);

-----------------------validating for duplicate receipt.--------------------------------

DECLARE

lc_receipt_count NUMBER(3);

lv_error_msg VARCHAR2(500);

BEGIN

SELECT COUNT(receipt_number)

INTO lc_receipt_count

FROM ar_cash_receipts_all

WHERE receipt_number = cur_create_stg.receipt_number

AND org_id = cur_create_stg.org_id;

IF lc_receipt_count >0 THEN

lv_error_msg := 'Error: Receipt Number ' || cur_create_stg.receipt_number || ' already in the System for '||cur_create_stg.org_id;

UPDATE xxfin_cust_trx

SET ERRBUF =lv_error_msg

WHERE RECEIPT_NUMBER=cur_create_stg.receipt_number

AND ORG_ID =cur_create_stg.org_id;

DBMS_OUTPUT.put_line (lv_error_msg);

ELSE

NULL;

END IF;

EXCEPTION

WHEN OTHERS THEN

UPDATE xxfin_cust_trx

SET ERRBUF ='Receipt Values not found in custom receipt table'

WHERE RECEIPT_NUMBER=cur_create_stg.receipt_number

AND ORG_ID =cur_create_stg.org_id;

END;

------------------------end of duplicate receipt validation.--------------------------------

BEGIN

lv_receipt_number := cur_create_stg.RECEIPT_NUMBER;

----------------------------------changing the receipt method internally---------------

BEGIN

lv_receipt_method:=cur_create_stg.receipt_method;

IF lv_receipt_method LIKE '%Cash%' THEN

ln_receipt_method_id:=1502;

elsif lv_receipt_method LIKE '%Cheque%' THEN

ln_receipt_method_id:=1503;

elsif lv_receipt_method LIKE '%PDC%' THEN

ln_receipt_method_id:=1503;

elsif lv_receipt_method LIKE '%Bank Transfer%' THEN

ln_receipt_method_id:=505;

ELSE

ln_receipt_method_id:=cur_create_stg.RECEIPT_METHOD_ID;

END IF;

END;

----------------------------------------------------------------------------------------

l_cash_receipt_id := NULL;

l_attribute_rec.attribute_category:=cur_create_stg.attribute_category;

l_attribute_rec.attribute2 :=cur_create_stg.attribute1;

l_attribute_rec.attribute3 :=cur_create_stg.attribute2;

l_attribute_rec.attribute4 :=cur_create_stg.attribute4;

l_attribute_rec.attribute5 :=cur_create_stg.attribute3;

l_attribute_rec.attribute8 :=cur_create_stg.attribute5;

-- 2) Call the API

AR_RECEIPT_API_PUB.CREATE_CASH ( 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 => cur_create_stg.INVOICE_CURRENCY_CODE

, p_amount => cur_create_stg.amount

, p_receipt_number => cur_create_stg.RECEIPT_NUMBER

, p_receipt_date => TRUNC(SYSDATE)

, p_gl_date => cur_create_stg.GL_DATE

, p_customer_number => cur_create_stg.account_number

, p_receipt_method_id => ln_receipt_method_id

, p_comments => cur_create_stg.comments

, P_ORG_ID => cur_create_stg.org_id

, p_attribute_rec => l_attribute_rec

, p_cr_id => l_cash_receipt_id );

COMMIT;

END;

-- 3) Review the API output

dbms_output.put_line('Status ' || l_return_status);

dbms_output.put_line('Cash Receipt id ' || l_cash_receipt_id );

dbms_output.put_line('Message count ' || l_msg_count);

l_status:='Receipt Not Created';

COMMIT;

IF l_return_status='S'

THEN

BEGIN

l_status:='Receipt Created';

xxfin_cust_trx_prc(l_cash_receipt_id

,cur_create_stg.RECEIPT_NUMBER

,l_status,l_msg_data

,cur_create_stg.org_id

,ip_cus_num);

COMMIT;

dbms_output.put_line('Message Apply: '|| cur_create_stg.org_id );

xxfin_cus_form_pkg.xxfin_cus_form_rec_apply (ip_cash_receipt_id => l_cash_receipt_id

, ip_cus_num => ip_cus_num

, ip_receipt_num => cur_create_stg.RECEIPT_NUMBER

, ip_org_id => cur_create_stg.org_id

, op_return_status1 =>l_return_status1_apply );

dbms_output.put_line('Message Apply2: '|| l_return_status1_apply );

END;

l_status:='Receipt Applied';

COMMIT;

ELSIF l_return_status='E' THEN

l_status :='Receipt UnApplied Error';

xxfin_cust_trx_prc(l_cash_receipt_id

,cur_create_stg.RECEIPT_NUMBER

,l_status

,l_msg_data

,cur_create_stg.org_id

,ip_cus_num);

COMMIT;

END IF;

IF l_msg_count = 1 THEN

dbms_output.put_line('l_msg_data '||l_msg_data|| cur_create_stg.org_id|| 'Org_id');

l_status :='Receipt UnApplied EE';

xxfin_cust_trx_prc(l_cash_receipt_id

,cur_create_stg.RECEIPT_NUMBER

,l_status

,l_msg_data

, cur_create_stg.org_id

,ip_cus_num);

COMMIT;

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);

l_status :='Receipt UnApplied EEE';

xxfin_cust_trx_prc(l_cash_receipt_id

,cur_create_stg.RECEIPT_NUMBER

,l_status

,l_msg_data

,cur_create_stg.org_id,ip_cus_num);

COMMIT;

IF l_msg_data IS NULL THEN

EXIT;

END IF;

dbms_output.put_line('Message ' || p_count ||'. '||l_msg_data);

END LOOP;

END IF;

COMMIT;

END LOOP;

COMMIT;

DECLARE

lv_err_msg VARCHAR2(2000);

lv_ret_code VARCHAR2(2000);

lv_receipt_number VARCHAR2(30);

BEGIN

lv_receipt_number:=ip_receipt_num;

xxfin_create_misc_rec_prc(lv_err_msg,lv_ret_code,lv_receipt_number);

dbms_output.put_line('receipt number is '||lv_receipt_number);

dbms_output.put_line('error message is '||lv_err_msg);

dbms_output.put_line('error code is '||lv_ret_code);

END;

EXCEPTION

WHEN OTHERS THEN

xxfin_cust_trx_prc_apply(l_cash_receipt_id,lv_receipt_number,lv_TRX_NUMBER,'Step2'||SQLERRM,l_msg_data,ip_cus_num);

END xxfin_cus_form_rec_prc;

--END OF PROCEDURE XXFIN_CUS_FORM_REC_PRC.

--CREATING A PROCEDURE TO APPLY THE RECEIPTS, WHICH IS CREATED BY THE XXFIN_CUS_FORM_REC_PRC PROCEDURE.

PROCEDURE xxfin_cus_form_rec_apply(

ip_cash_receipt_id IN NUMBER ,

ip_cus_num IN NUMBER ,

ip_receipt_num IN VARCHAR2 ,

ip_org_id IN NUMBER ,

op_return_status1 OUT VARCHAR2 )

l_error_msg VARCHAR2 (500);

l_return_status VARCHAR2(1);

l_msg_count NUMBER;

l_msg_data VARCHAR2(240);

l_cash_receipt_id NUMBER;

p_count NUMBER := 0;

L_ATTRIBUTE_REC VARCHAR2(150);

l_customer_trx_id NUMBER;

gc_user_name VARCHAR2 (100);

gc_responsibility_name VARCHAR2 (100);

gc_application_short_name VARCHAR2 (100);

gc_org_id NUMBER;

l_status VARCHAR2(30);

lv_error_message VARCHAR2(2000);

ln_user_id NUMBER;

CURSOR recipt_apply_stg

SELECT xt.AMOUNT_APPLIED,

xr.RECEIPT_NUMBER,

xt.org_id,

xt.TRX_NUMBER,

xt.apply_date

FROM xxfin_custom_receipt xr,

xxfin_cust_trx xt

WHERE xr.receipt_number=xt.receipt_number

AND xt.rec_status ='Y'

AND xt.receipt_number =ip_receipt_num

AND xt.customer_num =ip_cus_num

AND xt.org_id =ip_org_id;

CURSOR cu_login_variables

SELECT fu.user_id user_id,

frv.responsibility_id resp_id,

fav.application_id resp_appl_id

FROM fnd_application_vl fav,

fnd_responsibility_vl frv,

fnd_user fu

WHERE fu.user_id = ln_user_id

AND frv.responsibility_name = gc_responsibility_name

AND fav.application_short_name = gc_application_short_name;

lr_login_variables cu_login_variables%ROWTYPE;

lv_trx_number NUMBER;

BEGIN

ln_user_id:=fnd_global.user_id;

FOR cur_apply_stg IN recipt_apply_stg

LOOP

BEGIN

ar_receipt_api_pub.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_cash_receipt_id => ip_cash_receipt_id

, x_return_status => l_return_status

, x_msg_count => l_msg_count

, x_msg_data => l_msg_data

, p_trx_number => cur_apply_stg.trx_number

, p_customer_trx_id => l_customer_trx_id

, p_amount_applied => cur_apply_stg.AMOUNT_APPLIED

, p_org_id => ip_org_id

, p_apply_date => cur_apply_stg.apply_date

, p_show_closed_invoices => 'Y' );

COMMIT;

END ;

IF l_return_status='S' THEN

l_status :='Receipt Applied';

xxfin_cust_trx_prc_apply(ip_cash_receipt_id,cur_apply_stg.RECEIPT_NUMBER,cur_apply_stg.TRX_NUMBER,l_status,l_msg_data,ip_cus_num);

ELSE

l_status:='Receipt UnApplied';

xxfin_cust_trx_prc_apply(ip_cash_receipt_id,cur_apply_stg.RECEIPT_NUMBER,cur_apply_stg.TRX_NUMBER,l_status,l_msg_data,ip_cus_num);

END IF;

END;

END LOOP;

xxfin_applied_amount(ip_receipt_num);

EXCEPTION

WHEN OTHERS THEN

lv_error_message := sqlerrm;

UPDATE xxfin_cust_trx

SET errbuf =lv_error_message,

status = 'Step6',

CASH_RECEIPT_ID =l_cash_receipt_id

WHERE receipt_number=ip_receipt_num

AND rec_status ='Y';

COMMIT;

END xxfin_cus_form_rec_apply;

--END OF CREATING XXFIN_CUS_FORM_REC_APPLY.

END xxfin_cus_form_pkg;

--END OF PACKAGE BODY.

Oracle EBS (ERP) Apps R12 Functional & Technical Solutions

Translate

Wednesday, June 24, 2026

How to find the RMA order receipt details in Oracle Apps R12

Payables invoice prepayment query in Oracle Apps R12

How to create MISCELLANEOUS Receipt in AR in Oracle Apps R12

Sample control file for GL daily rates interface program in Oracle Apps R12

Customer invoices interface by using API in Oracle Apps R12

How to find customer credit limit amount in Oracle Apps R12

How to create and apply receipt in AR by using API in Oracle Apps R12

Friday, June 5, 2026

PO 3 types of Match Approval Levels in Oracle EBS R12

How to create a employee by using hr employee API