Oracle Apps Technical Consultant: 2018

Sunday, 24 June 2018

Open PO details query in Oracle Apps R12

--Open PO query.
SELECT
pha.segment1 ponumber,
hou.name organization_code,
pha.type_lookup_code potype,
trunc(pha.creation_date) cdate,
pv.vendor_name supplier,
pv.segment1 supplier_number,
pvs.vendor_site_code suppliersite,
hl1.location_code shipto_loc,
hl2.location_code billto_loc,
(SELECT pla1.quantity * pla1.unit_price
FROM po_lines_all pla1
WHERE 1=1
AND pla1.po_line_id = pla.po_line_id) PO_LINE_AMT ,
pha.currency_code currency,
papf.full_name buyer,
pha.authorization_status,
pha.comments comments,
atl.name terms,
plla.need_by_date,
plla.promised_date,
pha.approved_date,
pha.closed_code
FROM
apps.po_headers_all pha,
apps.ap_suppliers pv,
apps.ap_supplier_sites_all pvs,
hr_locations hl1,
hr_locations hl2,
apps.per_all_people_f papf,
apps.po_lines_all pla,
hr_operating_units hou,
apps.ap_terms_tl atl,
apps.po_line_locations_all plla
WHERE
pha.vendor_id = pv.vendor_id
AND pha.type_lookup_code NOT IN ('RFQ','QUOTATION')
AND pha.vendor_site_id = pvs.vendor_site_id
AND pha.ship_to_location_id = hl1.location_id
AND pha.bill_to_location_id = hl2.location_id
AND pha.agent_id = papf.person_id
AND pha.po_header_id = pla.po_header_id
AND pha.org_id = hou.organization_id
AND pha.terms_id = atl.term_id
AND plla.po_header_id = pha.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.org_id = plla.org_id
AND pha.closed_code = 'OPEN'
AND pha.org_id = 7891
AND pha.authorization_status = 'APPROVED'
ORDER BY 1

Open sales order details query in Oracle Apps R12

-- Open SO.

SELECT ooh.order_number
,ooh.org_id
,OOH.OPEN_FLAG
,ool.open_flag "Lines Flag"
,ool.inventory_item_id
FROM OE_ORDER_HEADERS_ALL ooh
,OE_ORDER_LINES_ALL ool
WHERE 1=1
AND ooh.org_id = 7891
AND ooh.header_id = ool.header_id
AND ooh.open_flag = 'Y';

Open po receipts query in Oracle Apps R12

-- Open PO Receipts.

SELECT h.receipt_num
,h.shipment_header_id
,l.SHIPMENT_LINE_STATUS_CODE
,pha.segment1 PO_NUM
,PHA.po_header_id
FROM rcv_shipment_headers h
,rcv_shipment_lines l
,po_headers_all pha
WHERE h.shipment_header_id = l.shipment_header_id
AND l.source_document_code = 'PO'
AND pha.type_lookup_code NOT IN ('RFQ','QUOTATION')
AND pha.po_header_id = L.PO_HEADER_ID
AND L.SHIPMENT_LINE_STATUS_CODE not in ('FULLY RECEIVED');

Outbound interface by using UTL_FILE api in Oracle Apps R12?

-- This is package is used to take the backup of the PLSQL objects.

create or replace PACKAGE XX_WRITE_FILES_PKG
IS

PROCEDURE XX_WRITE_FILES_PRC(P_OBJECT_NAME IN VARCHAR2);

PROCEDURE MAIN;

END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/

create or replace PACKAGE BODY XX_WRITE_FILES_PKG
IS

-- | |
-- |Description : XX_WRITE_FILES_PKG is used to take the backup of |
-- | Database objects like PROCEDURE,PACKAGE BODY,PACKAGE |
-- | TYPE BODY,TRIGGER,FUNCTION,TYPE. |

PROCEDURE Debug( p_message IN VARCHAR2
) IS
lv_message VARCHAR2(200);

BEGIN

lv_message := SUBSTR(p_message,1,240);
fnd_file.put_line(fnd_file.log, lv_message);
-- dbms_output.put_line(lv_message);

END Debug;

-- +====================================================================+
-- | Name : write_out |
-- | Description : To write to the Output file of a concurrent Prog|
-- | Parameters : pv_mesg - Message String |
-- | |
-- +====================================================================+
PROCEDURE write_out(pv_mesg IN VARCHAR2) IS
BEGIN

FND_FILE.PUT_LINE( FND_FILE.OUTPUT, substr(pv_mesg,1,500));
-- dbms_output.put_line(substr(pv_mesg,1,500));

END write_out;

PROCEDURE XX_WRITE_FILES_PRC (P_OBJECT_NAME IN VARCHAR2)
IS

CURSOR lcu_file_name (cv_object_name VARCHAR2)
IS
SELECT text
FROM user_source
WHERE NAME = cv_object_name;

l_file UTL_FILE.FILE_TYPE;

BEGIN

Debug('XX_WRITE_FILES_PRC => Begining of Procedure');

l_file := UTL_FILE.FOPEN('/usr/tmp',P_OBJECT_NAME||'.TXT','W');

Debug('XX_WRITE_FILES_PRC => After opening file '||P_OBJECT_NAME||'.TXT');
FOR lr_file_name_rec IN lcu_file_name(P_OBJECT_NAME) LOOP

UTL_FILE.PUT_LINE(l_file,lr_file_name_rec.TEXT);
END LOOP;
Debug('XX_WRITE_FILES_PRC => After closing the for-loop lr_file_name_rec');

UTL_FILE.FCLOSE(l_file);
Debug('XX_WRITE_FILES_PRC => End of procedure');

EXCEPTION
WHEN OTHERS THEN
Debug('XX_WRITE_FILES_PRC error at processing backup file for object '||P_OBJECT_NAME);
Debug('XX_WRITE_FILES_PRC => Error: '||SQLCODE ||','||SQLERRM);
END XX_WRITE_FILES_PRC;

PROCEDURE MAIN (ERRBUF OUT VARCHAR2
,RETCODE OUT VARCHAR2)
IS

CURSOR lcu_object_name
IS
SELECT xbon.OBJECT_NAME
FROM XX_BKUP_OBJECT_NAMES xbon;

BEGIN

Debug('MAIN => Begining of PROCEDURE');

FOR lr_object_name IN lcu_object_name LOOP
Debug('MAIN => Entered into for-loop lr_object_name');

XX_WRITE_FILES_PRC(lr_object_name.OBJECT_NAME);
write_out('Backup file created for object '||lr_object_name.OBJECT_NAME);
END LOOP;
Debug('MAIN => End of PROCEDURE');

EXCEPTION
WHEN OTHERS THEN
Debug('MAIN => Error: '||SQLCODE ||','||SQLERRM);
END MAIN;

END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/

How to take backup of PLSQL objects programatically in Oracle Apps R12 ?

This is package is used to take the backup of the PLSQL objects.

create or replace PACKAGE XX_WRITE_FILES_PKG
IS

PROCEDURE XX_WRITE_FILES_PRC(P_OBJECT_NAME IN VARCHAR2);

PROCEDURE MAIN;

END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/

create or replace PACKAGE BODY XX_WRITE_FILES_PKG
IS

-- | |
-- |Description : XX_WRITE_FILES_PKG is used to take the backup of |
-- | Database objects like PROCEDURE,PACKAGE BODY,PACKAGE |
-- | TYPE BODY,TRIGGER,FUNCTION,TYPE. |

PROCEDURE Debug( p_message IN VARCHAR2
) IS
lv_message VARCHAR2(200);

BEGIN

lv_message := SUBSTR(p_message,1,240);
fnd_file.put_line(fnd_file.log, lv_message);
-- dbms_output.put_line(lv_message);

END Debug;

-- +====================================================================+
-- | Name : write_out |
-- | Description : To write to the Output file of a concurrent Prog|
-- | Parameters : pv_mesg - Message String |
-- | |
-- +====================================================================+
PROCEDURE write_out(pv_mesg IN VARCHAR2) IS
BEGIN

FND_FILE.PUT_LINE( FND_FILE.OUTPUT, substr(pv_mesg,1,500));
-- dbms_output.put_line(substr(pv_mesg,1,500));

END write_out;

PROCEDURE XX_WRITE_FILES_PRC (P_OBJECT_NAME IN VARCHAR2)
IS

CURSOR lcu_file_name (cv_object_name VARCHAR2)
IS
SELECT text
FROM user_source
WHERE NAME = cv_object_name;

l_file UTL_FILE.FILE_TYPE;

BEGIN

Debug('XX_WRITE_FILES_PRC => Begining of Procedure');

l_file := UTL_FILE.FOPEN('/usr/tmp',P_OBJECT_NAME||'.TXT','W');

Debug('XX_WRITE_FILES_PRC => After opening file '||P_OBJECT_NAME||'.TXT');
FOR lr_file_name_rec IN lcu_file_name(P_OBJECT_NAME) LOOP

UTL_FILE.PUT_LINE(l_file,lr_file_name_rec.TEXT);
END LOOP;
Debug('XX_WRITE_FILES_PRC => After closing the for-loop lr_file_name_rec');

UTL_FILE.FCLOSE(l_file);
Debug('XX_WRITE_FILES_PRC => End of procedure');

EXCEPTION
WHEN OTHERS THEN
Debug('XX_WRITE_FILES_PRC error at processing backup file for object '||P_OBJECT_NAME);
Debug('XX_WRITE_FILES_PRC => Error: '||SQLCODE ||','||SQLERRM);
END XX_WRITE_FILES_PRC;

PROCEDURE MAIN (ERRBUF OUT VARCHAR2
,RETCODE OUT VARCHAR2)
IS

CURSOR lcu_object_name
IS
SELECT xbon.OBJECT_NAME
FROM XX_BKUP_OBJECT_NAMES xbon;

BEGIN

Debug('MAIN => Begining of PROCEDURE');

FOR lr_object_name IN lcu_object_name LOOP
Debug('MAIN => Entered into for-loop lr_object_name');

XX_WRITE_FILES_PRC(lr_object_name.OBJECT_NAME);
write_out('Backup file created for object '||lr_object_name.OBJECT_NAME);
END LOOP;
Debug('MAIN => End of PROCEDURE');

EXCEPTION
WHEN OTHERS THEN
Debug('MAIN => Error: '||SQLCODE ||','||SQLERRM);
END MAIN;

END XX_WRITE_FILES_PKG;
/
SHO ERRORS
/

Sunday, 17 June 2018

Vendor creation by using API in Orale Apps R12

DECLARE
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
cursor c1 is select * from xx_sup_stage;
BEGIN
-- --------------
-- Required
-- --------------
for i in c1 loop
l_vendor_rec.VENDOR_ID:= i.VENDOR_ID;
l_vendor_rec.VENDOR_NAME:= i.VENDOR_NAME;
l_vendor_rec.VENDOR_NAME_ALT:=i.VENDOR_NAME_ALT;
l_vendor_rec.SEGMENT1:= i.SEGMENT1;
l_vendor_rec.SUMMARY_FLAG:=i.SUMMARY_FLAG;
l_vendor_rec.ENABLED_FLAG:=i.ENABLED_FLAG;
l_vendor_rec.TERMS_ID:=i.TERMS_ID;
l_vendor_rec.PAY_DATE_BASIS_LOOKUP_CODE:=i.PAY_DATE_BASIS_LOOKUP_CODE;
l_vendor_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_LOOKUP_CODE;
l_vendor_rec.INVOICE_CURRENCY_CODE:=i.INVOICE_CURRENCY_CODE;
l_vendor_rec.PAYMENT_CURRENCY_CODE:=i.PAYMENT_CURRENCY_CODE;
l_vendor_rec.START_DATE_ACTIVE:=i.START_DATE_ACTIVE;

-- -------------
-- Optional
-- --------------
l_vendor_rec.match_option :='R';

pos_vendor_pub_pkg.create_vendor
( -- -------------------------
-- Input Parameters
-- -------------------------
p_vendor_rec => l_vendor_rec,
-- ----------------------------
-- Output Parameters
-- ----------------------------
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_id => l_vendor_id,
x_party_id => l_party_id
);

IF l_return_status ='S' THEN
-- Update vendor id in stage tables through autonomus prrogram.

ELSE
-- Update vendor id in stage tables through autonomus prrogram.
End if;

end loop;

commit;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

How to add the lay out to XML report when calling through form personalization in R12

In form personalization we have to do below steps, to add the layout to XML output concurrent programs.

Create a sequence 1 with type Built in and built in type as “Execute a procedure”

='declare

lv_layout BOOLEAN;

begin

lv_layout:=fnd_request.add_layout(template_appl_name => '''||'XXFIN'|| '''

,template_code => '''||'FMCBR201' || '''

,template_language => '''||'en'|| '''

,template_territory => '''||'US'||'''

,output_format => '''||'PDF'||''');

commit;

end'

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;

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)
IS
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
IS
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, ' ');
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;
/

AR Customer transactions interface by using API in Oracle Apps R12

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)

GL Daily rates Interface in Oracle Apps R12

create or replace PACKAGE XXFIN_GL_XE_DRATES_PKG
IS

PROCEDURE XXFIN_DAILY_RATES_PROC(
retcode OUT NUMBER,
errbuff OUT VARCHAR2);

END XXFIN_GL_XE_DRATES_PKG;
/

create or replace PACKAGE BODY XXFIN_GL_XE_DRATES_PKG
IS

PROCEDURE XXFIN_DAILY_RATES_PROC(
retcode OUT NUMBER,
errbuff OUT VARCHAR2)
IS

CURSOR cur_drates
IS
SELECT gers.from_currency from_currency ,
gers.amount amount ,
'Corporate' user_conversion_type ,
'I' MODE_FLAG ,
gers.from_conversion_date conversion_date
-- ,substr(gers.from_conversion_date,1,10) conversion_date_w
,
SUBSTR(gers.from_conversion_date,1,10)
||' '
||SUBSTR(gers.from_conversion_date,12,8) Conversion_date_from ,
SUBSTR(gers.from_conversion_date,1,10)
||' '
||SUBSTR(gers.from_conversion_date,12,8) Conversion_date_to ,
gers.to_currency to_currency ,
gers.conversion_rate conversion_rate
FROM xxfin_gl_exchange_rates_stg gers
WHERE SUBSTR(gers.from_conversion_date,1,10)=TO_CHAR(sysdate,'YYYY-MM-DD');

LV_FROM_CURRENCY VARCHAR2(15);
LV_FROM_CURRENCY2 VARCHAR2(15);
LV_TO_CURRENCY VARCHAR2(15);
LV_TO_CURRENCY2 VARCHAR2(15);
LV_USER_CONVERSION_TYPE VARCHAR2(30);
LV_USER_CONVERSION_TYPE2 VARCHAR2(30);
LV_CONVERSION_RATE NUMBER;
LN_USER_ID NUMBER;
LV_DATE_FROM DATE;
LV_DATE_TO DATE;
LV_STATUS varchar2(10);
LN_ACCESS_SET_ID NUMBER(20);
LN_LEDGER_ID NUMBER(20);
LN_APPLID NUMBER(20);
LV_UC_TYPE VARCHAR2(100);
LV_ERR_FLAG VARCHAR2(1):= 'A';

BEGIN

FOR i IN cur_drates
LOOP

LV_ERR_FLAG:='A';

BEGIN
LN_USER_ID:=FND_GLOBAL.USER_ID;
END;

--start from currency validation
BEGIN
LV_FROM_CURRENCY2:=i.from_currency;
SELECT CURRENCY_CODE
INTO LV_FROM_CURRENCY
FROM FND_CURRENCIES
WHERE CURRENCY_CODE=i.from_currency
AND ENABLED_FLAG='Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_from_currency := NULL;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code '||LV_FROM_CURRENCY2||' is not defined or not enabled.');
END;

--start to currency validation
BEGIN
LV_FROM_CURRENCY2:=i.to_currency;
SELECT CURRENCY_CODE
INTO LV_TO_CURRENCY
FROM FND_CURRENCIES
WHERE ENABLED_FLAG='Y'
AND CURRENCY_CODE =i.to_currency;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LV_TO_CURRENCY := NULL;
lv_err_flag := 'E';

FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code '||LV_TO_CURRENCY2||' is not defined or not enabled.');
END;
--end to currency validation

--start user conversion type validation.
BEGIN
LV_USER_CONVERSION_TYPE2:=i.user_conversion_type;
SELECT USER_CONVERSION_TYPE
INTO LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES
WHERE USER_CONVERSION_TYPE=i.user_conversion_type;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LV_USER_CONVERSION_TYPE := NULL;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE '||LV_USER_CONVERSION_TYPE2||' is not defined.');
END;
--end user conversion type validation

--start validating dates
begin
LV_DATE_FROM:=TO_DATE(i.Conversion_date_from,'YYYY-MM-DD HH24:MI:SS');
LV_DATE_TO:=TO_DATE(i.Conversion_date_to,'YYYY-MM-DD HH24:MI:SS');
LN_ACCESS_SET_ID:= FND_PROFILE.value('GL_ACCESS_SET_ID');
LN_LEDGER_ID:=GL_ACCESS_SET_SECURITY_PKG.get_default_ledger_id(ln_access_set_id,'R');
LN_APPLID:=fnd_profile.value('RESP_APPL_ID');
SELECT PS.CLOSING_STATUS
INTO LV_STATUS
FROM gl_period_statuses PS
WHERE PERIOD_NAME= TO_CHAR(TO_DATE(LV_DATE_FROM),'MON-YY')
AND PS.APPLICATION_ID=LN_APPLID
AND PS.LEDGER_ID=LN_LEDGER_ID;

IF LV_STATUS in ('O','F') THEN
NULL;
ELSE
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'Date '||LV_DATE_FROM||' is not in an open or future');
END IF;

exception
when no_data_found then
FND_FILE.PUT_line(FND_FILE.LOG,'Date '||LV_DATE_FROM||' is not in an open or future for this combination'||LN_APPLID||','||LN_LEDGER_ID||','||LN_ACCESS_SET_ID);
when others then
FND_FILE.PUT_line(FND_FILE.LOG,'Error caused by date issue '||SQLCODE||','||SQLERRM);
end;
--end validating dates

IF LV_ERR_FLAG='A' THEN
INSERT
INTO GL_DAILY_RATES_INTERFACE
(
FROM_CURRENCY,
TO_CURRENCY,
FROM_CONVERSION_DATE,
TO_CONVERSION_DATE,
USER_CONVERSION_TYPE,
CONVERSION_RATE,
MODE_FLAG,
USER_ID
)
VALUES
(
LV_FROM_CURRENCY,
LV_TO_CURRENCY,
LV_DATE_FROM,
LV_DATE_TO,
LV_USER_CONVERSION_TYPE,
i.conversion_rate,
i.MODE_FLAG,
LN_USER_ID);
END IF;
END LOOP;
COMMIT;
END XXFIN_DAILY_RATES_PROC;

END XXFIN_GL_XE_DRATES_PKG;
/

How to add the concurrent program to request group from backend in Oracle Apps R12

declare

l_program_short_name VARCHAR2 (200);
l_program_application VARCHAR2 (200);
l_request_group VARCHAR2 (200);
l_group_application VARCHAR2 (200);
l_check VARCHAR2 (2);

BEGIN

l_program_short_name := 'XXFIN_GL_XE_LOADER';
l_program_application := 'General Ledger';
l_request_group := 'GL Concurrent Program Group';
l_group_application := 'General Ledger';

--Calling API to assign concurrent program to a reqest group

fnd_program.add_to_group (program_short_name => l_program_short_name,
program_application => l_program_application,
request_group => l_request_group,
group_application => l_group_application
);

COMMIT;

BEGIN

--To check whether a Concurrent Program assigned to request group or not

SELECT 'Y'
INTO l_check
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs fcp
WHERE frg.request_group_id = frgu.request_group_id
AND frg.application_id = frgu.application_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.unit_application_id = fcp.application_id
AND fcp.concurrent_program_name = 'XXFIN_GL_XE_LOADER';

dbms_output.put_line ('Adding Concurrent Program "XXFIN_GL_XE_LOADER" to Request Group Succeeded');

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line ('Adding Concurrent Program "XXFIN_GL_XE_LOADER" to Request Group Failed');
END;
END;
/

How to add the request set to request group from backend in Oracle Apps R12

declare

l_request_set_code VARCHAR2 (200);
l_set_appl_short_name VARCHAR2 (200);
l_request_group VARCHAR2 (200);
l_group_application VARCHAR2 (200);
BEGIN

l_request_set_code:='FNDRSSUB94'; -- Request Set Code
l_set_appl_short_name:='SQLGL'; -- Application Code Short Name
l_request_group:='GL Concurrent Program Group'; -- Request Group Name
l_group_application:='SQLGL'; -- Application of the RG Name

fnd_set.add_set_to_group (request_set => l_request_set_code,
set_application => l_set_appl_short_name,
request_group => l_request_group,
group_application => l_group_application);

dbms_output.put_line('Request Set has been attached to Request Group Successfully ');
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
dbms_output.put_line ('Request Set is already available in the Request group');
WHEN OTHERS
THEN
dbms_output.put_line ('Others Exception adding Request Set. ERROR:' || SQLERRM);
END;
/

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
IS

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
IS

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

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 Apps Technical Consultant