OracleAppsKnowHows: April 2018

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

Monday, April 9, 2018

Sunday, April 1, 2018

Save Blog file from concurrent program to Custom table column