OracleAppsKnowHows: Create Invoice and Credit memo using API in oracle apps and saving blob file in custom table

PROCEDURE create_invoice (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_itemtype IN VARCHAR2,
p_itemkey IN number,
p_lease_id IN number,
P_chlist_trx_lines_id IN NUMBER,
p_org_id IN NUMBER )
IS
v_layout BOOLEAN;
v_call_status BOOLEAN;
v_phase VARCHAR2 (80);
v_status VARCHAR2 (80);
v_dev_phase VARCHAR2 (30);
-- v_status VARCHAR2(30);
v_message VARCHAR2 (240);
v_add_layout_status BOOLEAN;
v_att_status VARCHAR2 (1);
v_request_id NUMBER;
l_user_id NUMBER := (fnd_profile.VALUE('USER_ID'));--nvl(fnd_profile.VALUE('USER_ID'),2023);
l_resp_id NUMBER;-- :=nvl (fnd_profile.VALUE('RESP_ID'),52005);
l_resp_appl_id NUMBER;-- :=nvl( fnd_profile.VALUE('RESP_APPL_ID'),240);
l_login_id number ;--:= fnd_profile.VALUE('LOGIN_ID');
p_customer_trx_id number;
vCurrCustTrxID NUMBER;
vCurrCustTrxlINEID NUMBER;
vTrxDisId NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
ln_rec_cnt number;
l_err_cnt number;
l_batch_id number;
l_SQLERRM varchar2(4000);
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;

ln_segment1 VARCHAR2 (25);
ln_segment2 VARCHAR2 (25);
ln_segment3 VARCHAR2 (25);
ln_segment4 VARCHAR2 (25);
ln_segment5 VARCHAR2 (25);
ln_segment6 VARCHAR2 (25);
ln_segment7 VARCHAR2 (25);
ln_segment8 VARCHAR2 (25);
ln_segment9 VARCHAR2 (25);
ln_segment10 VARCHAR2 (25);
rev_code_ccid NUMBER;
v_term_name VARCHAR2 (100);
v_term_id NUMBER;
l_count number;
v_trx_number number;
v_trx_date date;

CURSOR cBatch(p_batch_id NUMBER) IS
select customer_trx_id
from ra_customer_trx_all
where batch_id = p_batch_id;

CURSOR c1 (l_org_id IN NUMBER)
IS
SELECT set_of_books_id, organization_id, default_legal_context_id
FROM hr_operating_units
WHERE organization_id = l_org_id;

CURSOR C2 (
L_LEASE_id number,l_chlist_trx_lines_id number)

IS
SELECT ctl.chlist_trx_lines_id,
ctl.task_name,
L.LEASE_ID,
cth.LEASE_NUM,
ctl.task_no,
NVL (ctl.penalty_charge, ctl.system_penalty_charges) CHARGES,
l.org_id,
l.attribute7,
t.customer_id,
t.cust_trx_type_id,
t.currency_code,
d.distribution_id,
d.account_id,
D.ACCOUNT_CLASS,
D.PERCENTAGE,
v.ATTRIBUTE2,
v.meaning
FROM xxpn_checklist_trx_headers cth,
xxpn_checklist_trx_lines ctl,
xxpn_leases_all l,
xxpn_payment_terms_all t,
PN_DISTRIBUTIONS_ALL d,
FND_LOOKUP_values_vl v
WHERE cth.chlist_id = ctl.chlist_id
AND l.new_lease_id = cth.new_lease_id
AND t.new_lease_id = l.new_lease_id
AND ctl.task_name = v.meaning
AND v.lookup_type = 'XXPN_CHECKLIST_TERM_TEMPL_MAP'
AND d.term_template_id = v.ATTRIBUTE1
-- AND cth.chlist_id = 168
AND d.account_class IN ('REV')
AND l.LEASE_ID = (L_LEASE_id)
and ctl.chlist_trx_lines_id=P_chlist_trx_lines_id
and rownum<2;--2878;

CURSOR err
IS
SELECT * FROM ar_trx_errors_gt;
BEGIN
mo_global.init ('AR'); -- Added by Anand on 03-SEP-2013
mo_global.set_policy_context ('S', p_org_id);

l_resp_id := wf_engine.getitemattrnumber(itemtype => p_itemtype,
itemkey => p_itemkey,
aname => 'RESP_ID');

l_resp_appl_id := wf_engine.getitemattrnumber(itemtype => p_itemtype,
itemkey => p_itemkey,
aname => 'RESP_APPL_ID');

/* BEGIN
SELECT application_id INTO l_resp_appl_id
FROM applsys.fnd_responsibility_tl
where 1=1 and RESPONSIBILITY_ID =l_resp_id-- in ( 'xxProperty Manager Superuser' ,'xxProperty Manager Super User' )--or responsibility_id=52005
AND LANGUAGE='US';
EXCEPTION WHEN OTHERS THEN
l_resp_appl_id:=240;
END; */
l_user_id:=2023;
fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);-- (2023, 52005, 240);

SELECT count(1) into l_count
FROM xxpn_checklist_trx_headers cth,
xxpn_checklist_trx_lines ctl,
xxpn_leases_all l,
xxpn_payment_terms_all t,
PN_DISTRIBUTIONS_ALL d,
FND_LOOKUP_values_vl v
WHERE cth.chlist_id = ctl.chlist_id
AND l.new_lease_id = cth.new_lease_id
AND t.new_lease_id = l.new_lease_id
AND ctl.task_name = v.meaning
AND v.lookup_type = 'XXPN_CHECKLIST_TERM_TEMPL_MAP'
AND d.term_template_id = v.ATTRIBUTE1
-- AND cth.chlist_id = 168
AND d.account_class IN ('REV')
AND l.lease_num =to_char(p_lease_id)
and ctl.chlist_trx_lines_id=P_chlist_trx_lines_id;--2878;

-- for k in 1..l_count LOOP
FOR i IN c2 (p_lease_id,P_chlist_trx_lines_id)
LOOP
-- FOR j IN C1 (p_org_id) LOOP
vCurrCustTrxID := ra_customer_trx_lines_s.NEXTVAL;
vCurrCustTrxlINEID := ra_customer_trx_lines_s.NEXTVAL; --ra_customer_trx_lines_s.NEXTVAL;
vTrxDisId := ra_cust_trx_line_gl_dist_s.NEXTVAL;

BEGIN
SELECT v.term_id
INTO v_term_id
FROM ra_terms_vl v
WHERE name = 'IMMEDIATE';
EXCEPTION
WHEN NO_DATA_FOUND
THEN

write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Create_Invoice' ,
'Error while retrieving the term_id from the ra_terms ' ) ;
errbuf :=
'Error while retrieving the term_id from the ra_terms '
|| SQLERRM;
retcode := '2';
WHEN OTHERS
THEN
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Create_Invoice' ,
'Error while retrieving the term_id from the ra_terms in others exception' ) ;
errbuf :=
'Error while retrieving the term_id from the ra_terms '
|| SQLERRM;
retcode := '2';
END;

ln_segment1 := i.attribute7;

BEGIN
SELECT segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10
INTO ln_segment2,
ln_segment3,
ln_segment4,
ln_segment5,
ln_segment6,
ln_segment7,
ln_segment8,
ln_segment9,
ln_segment10
FROM gl_code_combinations
WHERE code_combination_id = i.account_id;

DBMS_OUTPUT.put_line ('seg3 -> ' || ln_segment3);--vCurrCustTrxID

DBMS_OUTPUT.put_line ('vCurrCustTrxID -> ' || vCurrCustTrxID);--vCurrCustTrxID
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Create_Invoice' ,
'Error while retrieving gl_id_rev from gl_code_combinations' ) ;

errbuf :=
'Error while retrieving gl_id_rev from gl_code_combinations '
|| SQLERRM;
retcode := '2';
WHEN OTHERS
THEN
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Create_Invoice' ,
'Error while retrieving gl_id_rev from gcc' ) ;
errbuf :=
'Error while retrieving gl_id_rev from gcc' || SQLERRM;
retcode := '2';
END;

BEGIN
SELECT code_combination_id
INTO rev_code_ccid
FROM GL_code_combinations
WHERE segment1 = ln_segment1
AND segment2 = ln_segment2
AND segment3 = ln_segment3
AND segment4 = ln_segment4
AND segment5 = ln_segment5
AND segment6 = ln_segment6
AND segment7 = ln_segment7
AND segment8 = ln_segment8
AND segment9 = ln_segment9
AND segment10 = ln_segment10;

DBMS_OUTPUT.put_line ('ccid rev = ' || rev_code_ccid);
fnd_file.
put_line (fnd_file.LOG, 'ccid rev = ' || rev_code_ccid);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.
put_line (
'Error while retrieving revenue account id' || SQLERRM);
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Create_Invoice' ,
'Error while retrieving revenue account id' ) ;

errbuf :=
'Error while retrieving revenue account id' || SQLERRM;
retcode := '2';
WHEN OTHERS
THEN
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Create_Invoice' ,
'Error while retrieving revenue account id in others exception' ) ;
DBMS_OUTPUT.
put_line (
'Error while retrieving revenue account id' || SQLERRM);
errbuf :=
'Error while retrieving revenue account id' || SQLERRM;
retcode := '2';
END;
l_batch_source_rec.batch_source_id := i.attribute2;
l_trx_header_tbl (1).trx_header_id := vCurrCustTrxID; --now
-- l_trx_header_tbl (1).gl_date := TO_DATE ('02-MAY-2017', 'DD-MON-YYYY'); --SYSDATE-420; --9898;
l_trx_header_tbl (1).trx_date := SYSDATE;
l_trx_header_tbl (1).trx_currency := i.currency_code;
l_trx_header_tbl (1).cust_trx_type_id := i.cust_trx_type_id;
l_trx_header_tbl (1).bill_to_customer_id := i.customer_id;
-- l_trx_header_tbl (1).DESCRIPTION:='rent';
l_trx_header_tbl (1).term_id := v_term_id; --
l_trx_header_tbl (1).finance_charges := 'N';
l_trx_header_tbl (1).status_trx := 'OP';
l_trx_header_tbl (1).printing_option := 'PRI'; -- 'NOT';
l_trx_header_tbl (1).interface_header_context := 'XXPN_INVOICES';
l_trx_header_tbl (1).interface_header_attribute1 := i.lease_id;
l_trx_header_tbl (1).interface_header_attribute2 := i.chlist_trx_lines_id;
l_trx_header_tbl (1).interface_header_attribute3 := i.task_name;
l_trx_header_tbl (1).interface_header_attribute4 := sysdate;

/* Line level data below */
--l_trx_header_tbl(1).reference_number := '1111';
l_trx_lines_tbl (1).trx_header_id := vCurrCustTrxID; -- 9898;
l_trx_lines_tbl (1).trx_line_id := vCurrCustTrxlINEID; -- 101;
l_trx_lines_tbl (1).line_number := 1;
l_trx_lines_tbl (1).inventory_item_id := NULL;
l_trx_lines_tbl (1).description :=i.TASK_NAME;
l_trx_lines_tbl (1).quantity_invoiced := 1;
l_trx_lines_tbl (1).unit_selling_price := i.charges; --Price
l_trx_lines_tbl (1).interface_line_context := 'XXPN_INVOICES';
l_trx_lines_tbl (1).interface_line_attribute1 := i.lease_id;
l_trx_lines_tbl (1).interface_line_attribute2 := i.chlist_trx_lines_id;
l_trx_lines_tbl (1).interface_line_attribute3 := i.task_name;
l_trx_lines_tbl (1).interface_line_attribute4 := sysdate;
-- l_trx_lines_tbl (1).uom_code := 'EAC';
l_trx_lines_tbl (1).line_type := 'LINE';
l_trx_dist_tbl (1).trx_dist_id := vTrxDisId; --10164;
l_trx_dist_tbl (1).trx_line_id := vCurrCustTrxlINEID; -- 101;
l_trx_dist_tbl (1).ACCOUNT_CLASS := i.ACCOUNT_CLASS;
l_trx_dist_tbl (1).percent := i.percentage;
l_trx_dist_tbl (1).CODE_COMBINATION_ID := rev_code_ccid; --573496;--61345;
-- END LOOP; --END C1
END LOOP; ---END C2
-- END LOOP;--K

--Here we call the API to create Invoice with the stored values
AR_INVOICE_API_PUB.create_invoice (p_api_version => 1.0,
p_commit => 'T',
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;

IF l_return_status = fnd_api.g_ret_sts_error
OR l_return_status = fnd_api.g_ret_sts_unexp_error
THEN
---dbms_output.put_line('unexpected errors found!');

errbuf := SUBSTR (l_msg_data, 1, 255);
--insert into tmp_util_Errors_Gt
--select * from Ar_Trx_Errors_Gt;

ELSE
IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
THEN
dbms_output.put_line ( 'BATCH ID '|| ar_invoice_api_pub.g_api_outputs.batch_id );

for cBatchRec in cBatch(ar_invoice_api_pub.g_api_outputs.batch_id)
loop

select trx_number , trx_date into v_trx_number,v_trx_date from ra_customer_trx_all where customer_trx_id=cBatchRec.customer_trx_id;

UPDATE xxpn_checklist_trx_lines SET AR_INVOICE_NUMBER=v_trx_number , AR_INV_DATE=v_trx_date WHERE
chlist_trx_lines_id=P_chlist_trx_lines_id;
dbms_output.put_line ( 'Cust Trx Id '|| cBatchRec.customer_trx_id );
end loop;

commit;

begin

v_layout := fnd_request.add_layout('XXCUST',
'XXARCMDMPR',
'en',
'US',
'RTF');

v_request_id :=
fnd_request.submit_request (
application => 'XXCUST',
program => 'XXARCMDMPR',
description => 'Credit/Debit Memo, Invoice Printing New (XEC)',
start_time => SYSDATE,
sub_request => FALSE,
argument1 =>p_org_id,
argument2 => 'TRX_NUMBER',
argument3 => 'INV',
argument4 =>V_TRX_NUMBER,
argument5 =>V_TRX_NUMBER
);
--
COMMIT; --
v_call_status :=
fnd_concurrent.wait_for_request (v_request_id,
INTERVAL => 10,
max_wait => 1000,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_status,
MESSAGE => v_message);

IF v_request_id = 0
THEN
DBMS_OUTPUT.put_line ('Concurrent request failed to submit');
--p_return_status := 'E';
ELSE
DBMS_OUTPUT.put_line ('Successfully Submitted the Concurrent Request');

--p_request_id := l_request_id;
DECLARE
src_lob BFILE
:= BFILENAME ('CP_OUT_DIR',
'XXARCMDMPR_' || v_request_id || '_1.RTF');--XXARCMDMPR_25105504_1
dest_lob BLOB;
BEGIN
UPDATE xxpn_checklist_trx_lines SET AR_INV_PRINT =EMPTY_BLOB (), file_name='XXARCMDMPR_'|| v_request_id || '_1.RTF' ,file_content_type='application/RTF' --, REQUEST_ID=v_request_id --, CHLIST_TRX_LINES_ID=P_chlist_trx_lines_id
WHERE CHLIST_TRX_LINES_ID= P_chlist_trx_lines_id
RETURNING AR_INV_PRINT INTO dest_lob;

/* INSERT INTO XXCUST.xxpn_checklist_trx_ar_DATA (FILE_DATA,REQUEST_ID,CHLIST_TRX_LINES_ID)
VALUES (EMPTY_BLOB (), v_request_id, 2878)
RETURNING FILE_DATA
INTO dest_lob; */

DBMS_LOB.open (src_lob, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (dest_lob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (dest_lob => dest_lob,
src_lob => src_lob,
amount => DBMS_LOB.getlength (src_lob));
DBMS_LOB.close (src_lob);
DBMS_LOB.close (dest_lob);
--
COMMIT;
END;

END IF;

exception when others then
errbuf := 'Errors found while saving invoice PDF to table';
retcode := '2';
l_SQLERRM:=sqlerrm;
insert into test values ('Error while inserting into fnd_lobs for file: '||l_SQLERRM) ;
-- insert into test values('step_insert : '||v_SQLERRM);
commit;
end;

-- p_batch_source_id := ar_invoice_api_pub.g_api_outputs.batch_id;
-- p_customer_trx_id := l_cust_trx_id;
-- dbms_output.put_line('p_customer_trx_id '||p_customer_trx_id);
/* SELECT CUSTOMER_TRX_ID
INTO v_CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID; */
NULL;
ELSE
errbuf := 'Errors found!';
retcode := '2';
END IF;
END IF;

SELECT COUNT (*) INTO l_err_cnt FROM ar_trx_errors_gt;

IF l_err_cnt > 0
THEN
errbuf := 'Transaction not Created,';

FOR rec_err IN err
LOOP
errbuf := errbuf || rec_err.ERROR_MESSAGE || CHR (10);
write_log('Transaction not Created',
p_lease_id ,
P_chlist_trx_lines_id,
'Create_Invoice' ,
rec_err.ERROR_MESSAGE ) ;
END LOOP;
--errbuf := 'Transaction not Created, Please check ar_trx_errors_gt table';
END IF;
END;
PROCEDURE cancel_invoice (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_itemtype IN VARCHAR2,
p_itemkey IN number,
p_lease_id IN number,
P_chlist_trx_lines_id IN NUMBER,
p_org_id IN NUMBER )
IS
v_layout BOOLEAN;
v_call_status BOOLEAN;
v_phase VARCHAR2 (80);
v_status VARCHAR2 (80);
v_dev_phase VARCHAR2 (30);
-- v_status VARCHAR2(30);
v_message VARCHAR2 (240);
v_add_layout_status BOOLEAN;
v_att_status VARCHAR2 (1);
v_request_id NUMBER;
l_user_id NUMBER := (fnd_profile.VALUE('USER_ID'));--nvl(fnd_profile.VALUE('USER_ID'),2023);
l_resp_id NUMBER;-- :=nvl (fnd_profile.VALUE('RESP_ID'),52005);
l_resp_appl_id NUMBER;-- :=nvl( fnd_profile.VALUE('RESP_APPL_ID'),240);
l_login_id number ;--:= fnd_profile.VALUE('LOGIN_ID');
p_customer_trx_id number;
vCurrCustTrxID NUMBER;
vCurrCustTrxlINEID NUMBER;
vTrxDisId NUMBER;
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
ln_rec_cnt number;
l_err_cnt number;
l_batch_id number;
l_SQLERRM varchar2(4000);
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;

ln_segment1 VARCHAR2 (25);
ln_segment2 VARCHAR2 (25);
ln_segment3 VARCHAR2 (25);
ln_segment4 VARCHAR2 (25);
ln_segment5 VARCHAR2 (25);
ln_segment6 VARCHAR2 (25);
ln_segment7 VARCHAR2 (25);
ln_segment8 VARCHAR2 (25);
ln_segment9 VARCHAR2 (25);
ln_segment10 VARCHAR2 (25);
rev_code_ccid NUMBER;
v_term_name VARCHAR2 (100);
v_term_id NUMBER;
l_count number;
v_trx_number number;
v_trx_date date;

CURSOR cBatch(p_batch_id NUMBER) IS
select customer_trx_id
from ra_customer_trx_all
where batch_id = p_batch_id;

CURSOR c1 (l_org_id IN NUMBER)
IS
SELECT set_of_books_id, organization_id, default_legal_context_id
FROM hr_operating_units
WHERE organization_id = l_org_id;

CURSOR C2 (
L_LEASE_id number,l_chlist_trx_lines_id number)

IS
SELECT ctl.chlist_trx_lines_id,
ctl.task_name,
L.LEASE_ID,
cth.LEASE_NUM,
ctl.task_no,
NVL (ctl.penalty_charge, ctl.system_penalty_charges) CHARGES,
l.org_id,
l.attribute7,
t.customer_id,
t.cust_trx_type_id,
t.currency_code,
d.distribution_id,
d.account_id,
D.ACCOUNT_CLASS,
D.PERCENTAGE,
v.ATTRIBUTE2,
v.meaning,
ctl.AR_INVOICE_NUMBER
FROM xxpn_checklist_trx_headers cth,
xxpn_checklist_trx_lines ctl,
xxpn_leases_all l,
xxpn_payment_terms_all t,
PN_DISTRIBUTIONS_ALL d,
FND_LOOKUP_values_vl v
WHERE cth.chlist_id = ctl.chlist_id
AND l.new_lease_id = cth.new_lease_id
AND t.new_lease_id = l.new_lease_id
AND ctl.task_name = v.meaning
AND v.lookup_type = 'XXPN_CHECKLIST_TERM_TEMPL_MAP'
AND d.term_template_id = v.ATTRIBUTE1
-- AND cth.chlist_id = 168
AND d.account_class IN ('REV')
AND l.LEASE_ID = (L_LEASE_id)
and ctl.chlist_trx_lines_id=P_chlist_trx_lines_id
and rownum<2;--2878;

cursor invoice_data (p_trx_number in varchar2, p_org_id in number,p_batch_id in varchar2)
is select rct.customer_trx_id, rctl.customer_trx_line_id ,rct.trx_date,ars.gl_date ,dis.CUST_TRX_LINE_GL_DIST_ID from
ra_customer_trx_all rct, ra_customer_trx_lines_all rctl,ar_payment_schedules_all ars,RA_CUST_TRX_LINE_GL_DIST_ALL DIS
where rct.org_id=rctl.org_id
and rct.customer_trx_id=rctl.customer_trx_id and rct.trx_number=p_trx_number and rct.org_id=p_org_id
and ars.customer_trx_id=rct.customer_trx_id
and dis.CUSTOMER_TRX_LINE_ID=rctl.CUSTOMER_TRX_LINE_ID
and rct.batch_source_id=20101;--p_batch_id;
CURSOR err
IS
SELECT * FROM ar_trx_errors_gt;
BEGIN
mo_global.init ('AR'); -- Added by Anand on 03-SEP-2013
mo_global.set_policy_context ('S', p_org_id);

l_resp_id := wf_engine.getitemattrnumber(itemtype => p_itemtype,
itemkey => p_itemkey,
aname => 'RESP_ID');

l_resp_appl_id := wf_engine.getitemattrnumber(itemtype => p_itemtype,
itemkey => p_itemkey,
aname => 'RESP_APPL_ID');

/* BEGIN
SELECT application_id INTO l_resp_appl_id
FROM applsys.fnd_responsibility_tl
where 1=1 and RESPONSIBILITY_ID =l_resp_id-- in ( 'XX super user 2' ,'XX super user 1' )--or responsibility_id=52005
AND LANGUAGE='US';
EXCEPTION WHEN OTHERS THEN
l_resp_appl_id:=240;
END; */
l_user_id:=2023;
fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);-- (2023, 52005, 240);

FOR i IN c2 (p_lease_id,P_chlist_trx_lines_id)
LOOP
write_log('loop1',
p_lease_id ,
P_chlist_trx_lines_id,
'CANCEL_Invoice' ,
' loop 1' ) ;
-- FOR j IN C1 (p_org_id) LOOP
for k in invoice_data(i.AR_INVOICE_NUMBER,p_org_id,i.attribute2) Loop
-- vCurrCustTrxID := ra_customer_trx_lines_s.NEXTVAL;
-- vCurrCu stTrxlINEID := ra_customer_trx_lines_s.NEXTVAL; --ra_customer_trx_lines_s.NEXTVAL;
-- vTrxDisId := ra_cust_trx_line_gl_dist_s.NEXTVAL;
write_log('loop2',
p_lease_id ,
P_chlist_trx_lines_id,
'CANCEL_Invoice' ,
' loop 2' ) ;

BEGIN
SELECT v.term_id
INTO v_term_id
FROM ra_terms_vl v
WHERE name = 'IMMEDIATE';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Cancel_Invoice' ,
'Error while retrieving the term_id from the ra_terms ' ) ;

errbuf :=
'Error while retrieving the term_id from the ra_terms '
|| SQLERRM;
retcode := '2';
WHEN OTHERS
THEN
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Cancel_Invoice' ,
'Error while retrieving the term_id from the ra_terms IN OTHERS EXCEPTION' ) ;

errbuf :=
'Error while retrieving the term_id from the ra_terms '
|| SQLERRM;
retcode := '2';
END;

ln_segment1 := i.attribute7;

BEGIN
SELECT segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10
INTO ln_segment2,
ln_segment3,
ln_segment4,
ln_segment5,
ln_segment6,
ln_segment7,
ln_segment8,
ln_segment9,
ln_segment10
FROM gl_code_combinations
WHERE code_combination_id = i.account_id;

write_log('seg3',
p_lease_id ,
P_chlist_trx_lines_id,
'CANCEL_Invoice' ,
' seg3 '||ln_segment3 ) ;

DBMS_OUTPUT.put_line ('seg3 -> ' || ln_segment3);--vCurrCustTrxID

DBMS_OUTPUT.put_line ('vCurrCustTrxID -> ' || vCurrCustTrxID);--vCurrCustTrxID
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Cancel_Invoice' ,
'Error while retrieving gl_id_rev from gl_code_combinations ' ) ;
errbuf :=
'Error while retrieving gl_id_rev from gl_code_combinations '
|| SQLERRM;
retcode := '2';
WHEN OTHERS
THEN
write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'CANCEL_Invoice' ,
'Error while retrieving gl_id_rev from gcc' ) ;
errbuf :=
'Error while retrieving gl_id_rev from gcc' || SQLERRM;
retcode := '2';
END;

BEGIN
SELECT code_combination_id
INTO rev_code_ccid
FROM GL_code_combinations
WHERE segment1 = ln_segment1
AND segment2 = ln_segment2
AND segment3 = ln_segment3
AND segment4 = ln_segment4
AND segment5 = ln_segment5
AND segment6 = ln_segment6
AND segment7 = ln_segment7
AND segment8 = ln_segment8
AND segment9 = ln_segment9
AND segment10 = ln_segment10;

write_log('ccid rev =',
p_lease_id ,
P_chlist_trx_lines_id,
'CANCEL_Invoice' ,
' ccid rev = '||rev_code_ccid ) ;

DBMS_OUTPUT.put_line ('ccid rev = ' || rev_code_ccid);
fnd_file.
put_line (fnd_file.LOG, 'ccid rev = ' || rev_code_ccid);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.
put_line (
'Error while retrieving revenue account id' || SQLERRM);
errbuf :=
'Error while retrieving revenue account id' || SQLERRM;
retcode := '2';
WHEN OTHERS
THEN
DBMS_OUTPUT.
put_line (
'Error while retrieving revenue account id' || SQLERRM);
errbuf :=
'Error while retrieving revenue account id' || SQLERRM;
retcode := '2';
END;
l_batch_source_rec.batch_source_id := i.attribute2;
l_trx_header_tbl (1).trx_header_id := k.customer_trx_id; --now
-- l_trx_header_tbl (1).gl_date := k.gl_date;-- TO_DATE ('02-MAY-2017', 'DD-MON-YYYY'); --SYSDATE-420; --9898;
l_trx_header_tbl (1).trx_date := k.trx_date;
l_trx_header_tbl(1).TRX_CLASS := 'CM';
l_trx_header_tbl (1).trx_currency := i.currency_code;
l_trx_header_tbl (1).cust_trx_type_id := 2;--i.cust_trx_type_id;
l_trx_header_tbl (1).bill_to_customer_id := i.customer_id;
-- l_trx_header_tbl (1).term_id := v_term_id; --
l_trx_header_tbl (1).finance_charges := 'N';
l_trx_header_tbl (1).status_trx := 'OP';
l_trx_header_tbl (1).printing_option := 'PRI'; -- 'NOT';
l_trx_header_tbl (1).interface_header_context := 'XXPN_INVOICES';
l_trx_header_tbl (1).interface_header_attribute1 := i.lease_id||'- CM-2';
l_trx_header_tbl (1).interface_header_attribute2 := i.chlist_trx_lines_id||'- CM-2';
l_trx_header_tbl (1).interface_header_attribute3 := i.task_name||'- CM-2';
l_trx_header_tbl (1).interface_header_attribute4 := sysdate;
/* Line level data below */
--l_trx_header_tbl(1).reference_number := '1111';
l_trx_lines_tbl (1).trx_header_id := k.customer_trx_id; -- 9898;
l_trx_lines_tbl (1).trx_line_id := k.customer_trx_line_id; -- 101;
l_trx_lines_tbl (1).line_number := 1;
l_trx_lines_tbl (1).inventory_item_id := NULL;
l_trx_lines_tbl (1).description := i.TASK_NAME;
l_trx_lines_tbl (1).quantity_invoiced := 1;
l_trx_lines_tbl (1).unit_selling_price := (- i.charges); --Price
-- l_trx_lines_tbl (1).uom_code := 'EAC';
l_trx_lines_tbl (1).line_type := 'LINE';
l_trx_lines_tbl (1).interface_line_context := 'XXPN_INVOICES';
l_trx_lines_tbl (1).interface_line_attribute1 := i.lease_id||'- CM-2';
l_trx_lines_tbl (1).interface_line_attribute2 := i.chlist_trx_lines_id||'- CM-2';
l_trx_lines_tbl (1).interface_line_attribute3 := i.task_name||'- CM-2';
l_trx_lines_tbl (1).interface_line_attribute4 := sysdate;

l_trx_dist_tbl (1).trx_dist_id := k.CUST_TRX_LINE_GL_DIST_ID; --10164;
l_trx_dist_tbl (1).trx_line_id := k.customer_trx_line_id; -- 101;
l_trx_dist_tbl (1).ACCOUNT_CLASS := i.ACCOUNT_CLASS;
l_trx_dist_tbl (1).percent := i.percentage;
l_trx_dist_tbl (1).CODE_COMBINATION_ID := rev_code_ccid; --573496;--61345;
END LOOP;--invoice_cur
-- END LOOP; --END C1
END LOOP; ---END C2

--Here we call the API to create Invoice with the stored values
AR_INVOICE_API_PUB.create_invoice (p_api_version => 1.0,
p_commit => 'T',
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;

write_log('l_return_status',
p_lease_id ,
P_chlist_trx_lines_id,
'CANCEL_Invoice' ,
' l_return_status= '||l_return_status ) ;

IF l_return_status = fnd_api.g_ret_sts_error
OR l_return_status = fnd_api.g_ret_sts_unexp_error
THEN
---dbms_output.put_line('unexpected errors found!');

errbuf := SUBSTR (l_msg_data, 1, 255);
--insert into tmp_util_Errors_Gt
--select * from Ar_Trx_Errors_Gt;

ELSE
IF (ar_invoice_api_pub.g_api_outputs.batch_id IS NOT NULL)
THEN
dbms_output.put_line ( 'BATCH ID '|| ar_invoice_api_pub.g_api_outputs.batch_id );

write_log('BATCH ID',
p_lease_id ,
P_chlist_trx_lines_id,
'CANCEL_Invoice' ,
'BATCH ID '|| ar_invoice_api_pub.g_api_outputs.batch_id ) ;

for cBatchRec in cBatch(ar_invoice_api_pub.g_api_outputs.batch_id)
loop

select trx_number , trx_date into v_trx_number,v_trx_date from ra_customer_trx_all where customer_trx_id=cBatchRec.customer_trx_id;

UPDATE xxpn_checklist_trx_lines SET AR_CR_NOTE_REF=v_trx_number , AR_CM_DATE=v_trx_date WHERE
chlist_trx_lines_id=P_chlist_trx_lines_id;
dbms_output.put_line ( 'Cust Trx Id '|| cBatchRec.customer_trx_id );

write_log(SQLERRM,
p_lease_id ,
P_chlist_trx_lines_id,
'Cancel_Invoice' ,
'Cust Trx Id : '|| cBatchRec.customer_trx_id ) ;

write_log('after update',
p_lease_id ,
P_chlist_trx_lines_id,
'Cancel_Invoice' ,
'update count: '|| SQL%ROWCOUNT ) ;

end loop;

commit;

begin

v_layout := fnd_request.add_layout('XXCUST',
'XXARCMDMPR',
'en',
'US',
'RTF');

v_request_id :=
fnd_request.submit_request (
application => 'XXCUST',
program => 'XXARCMDMPR',
description => 'Credit/Debit Memo, Invoice Printing New (XEC)',
start_time => SYSDATE,
sub_request => FALSE,
argument1 =>p_org_id,
argument2 => 'TRX_NUMBER',
argument3 => 'CM',
argument4 =>V_TRX_NUMBER,
argument5 =>V_TRX_NUMBER
);
--
COMMIT; --
v_call_status :=
fnd_concurrent.wait_for_request (v_request_id,
INTERVAL => 10,
max_wait => 1000,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_status,
MESSAGE => v_message);

IF v_request_id = 0
THEN
DBMS_OUTPUT.put_line ('Concurrent request failed to submit');
--p_return_status := 'E';
ELSE
DBMS_OUTPUT.put_line ('Successfully Submitted the Concurrent Request');

--p_request_id := l_request_id;
DECLARE
src_lob BFILE
:= BFILENAME ('CP_OUT_DIR',
'XXARCMDMPR_' || v_request_id || '_1.RTF');--XXARCMDMPR_25105504_1
dest_lob BLOB;
BEGIN
UPDATE xxpn_checklist_trx_lines SET AR_CM_PRINT =EMPTY_BLOB (), CM_file_name='XXARCMDMPR_'|| v_request_id || '_1.RTF' ,file_content_type='application/RTF' --, REQUEST_ID=v_request_id --, CHLIST_TRX_LINES_ID=P_chlist_trx_lines_id
WHERE CHLIST_TRX_LINES_ID= P_chlist_trx_lines_id
RETURNING AR_CM_PRINT INTO dest_lob;

/* INSERT INTO XXCUST.xxpn_checklist_trx_ar_DATA (FILE_DATA,REQUEST_ID,CHLIST_TRX_LINES_ID)
VALUES (EMPTY_BLOB (), v_request_id, 2878)
RETURNING FILE_DATA
INTO dest_lob; */

DBMS_LOB.open (src_lob, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (dest_lob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (dest_lob => dest_lob,
src_lob => src_lob,
amount => DBMS_LOB.getlength (src_lob));
DBMS_LOB.close (src_lob);
DBMS_LOB.close (dest_lob);
--
COMMIT;
END;

END IF;

exception when others then
errbuf := 'Errors found while saving invoice PDF to table';
retcode := '2';
l_SQLERRM:=sqlerrm;
insert into test values ('Error while inserting into fnd_lobs for file: '||l_SQLERRM) ;
-- insert into test values('step_insert : '||v_SQLERRM);
commit;
end;

-- p_batch_source_id := ar_invoice_api_pub.g_api_outputs.batch_id;
-- p_customer_trx_id := l_cust_trx_id;
-- dbms_output.put_line('p_customer_trx_id '||p_customer_trx_id);
/* SELECT CUSTOMER_TRX_ID
INTO v_CUSTOMER_TRX_ID
FROM RA_CUSTOMER_TRX_ALL
WHERE CUSTOMER_TRX_ID = L_CUSTOMER_TRX_ID; */
NULL;
ELSE
errbuf := 'Errors found!';
retcode := '2';
END IF;
END IF;

SELECT COUNT (*) INTO l_err_cnt FROM ar_trx_errors_gt;

IF l_err_cnt > 0
THEN
errbuf := 'Transaction not Created,';

FOR rec_err IN err
LOOP
errbuf := errbuf || rec_err.ERROR_MESSAGE || CHR (10);
write_log('Transaction not Created',
p_lease_id ,
P_chlist_trx_lines_id,
'Cancel_Invoice' ,
rec_err.ERROR_MESSAGE ) ;

END LOOP;
--errbuf := 'Transaction not Created, Please check ar_trx_errors_gt table';
END IF;
END;

OracleAppsKnowHows

Wednesday, April 11, 2018

Create Invoice and Credit memo using API in oracle apps and saving blob file in custom table

No comments:

Post a Comment