[Home] [Help]
PACKAGE BODY: APPS.FV_GEN_ARTRX_REIMB_PROC
Source
1 PACKAGE BODY FV_GEN_ARTRX_REIMB_PROC AS
2 -- $Header: FVGARTRB.pls 120.0.12010000.16 2009/11/16 16:01:06 snama noship $
3
4 g_ledger_id NUMBER;
5 g_coa_id NUMBER;
6 g_org_id NUMBER;
7 g_ledger_name VARCHAR2(50);
8 g_agreement_num VARCHAR2(30);
9 g_period_name VARCHAR2(50);
10 g_period_num NUMBER;
11 g_period_year NUMBER;
12 C_STATE_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 g_log_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 g_module_name VARCHAR2(50) := 'fv.plsql.fv_gen_artrx_reimb_proc.';
15 g_errbuf VARCHAR2(500);
16 g_retcode NUMBER := 0;
17 g_customer_id NUMBER; --ra_customer_trx_all.bill_to_customer_id%TYPE;
18 g_currency VARCHAR2(30):='USD'; --ra_customer_trx_all.invoice_currency_code%TYPE;
19 g_coll_hdr_tbl ar_invoice_api_pub.trx_header_tbl_type;
20 g_rec_hdr_tbl ar_invoice_api_pub.trx_header_tbl_type;
21 g_coll_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
22 g_rec_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
23 g_coll_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
24 g_rec_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
25 g_trx_coll_hdr_id NUMBER;
26 g_trx_rec_hdr_id NUMBER;
27 g_header_printed BOOLEAN := FALSE;
28 i INTEGER := 0;
29 j INTEGER := 0;
30 k INTEGER := 0;
31
32 g_agreement varchar2(50);
33 g_rec_due_trx_type_id NUMBER;
34 g_liq_adv_trx_type_id NUMBER;
35 g_rec_due_prefix VARCHAR2(6);
36 g_liq_adv_prefix VARCHAR2(6);
37 g_trx_source_id NUMBER;
38 g_invoice_date DATE;
39 g_gl_balancing_segment VARCHAR2(30);
40 g_gl_nat_acc_segment VARCHAR2(30);
41 g_reimb_agreement_segment VARCHAR2(50);
42 g_advance_acc VARCHAR2(30);
43 g_revenue_acc VARCHAR2(30);
44 g_expenditure_acc VARCHAR2(30);
45 g_bfy_segment VARCHAR2(30);
46 g_org_segment VARCHAR2(30);
47 g_tot_expenses NUMBER;
48 g_tot_revenues NUMBER;
49 g_tot_advances NUMBER;
50 g_ussgl_flex_value_set_id NUMBER;
51
52
53 GET_SEGMENTS_EXCEP EXCEPTION;
54 GET_QUALIFIER_SEGNUM_EXCEP EXCEPTION;
55 GET_COMBINATION_ID_EXCEP EXCEPTION;
56
57 l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
58 g_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
59 g_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
60 g_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
61 l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
62 l_return_status varchar2(250);
63 l_msg_count number;
64 l_msg_data varchar2(2000);
65 l_customer_trx_id number;
66
67
68 PROCEDURE log (
69 p_level IN NUMBER,
70 p_procedure_name IN VARCHAR2,
71 p_debug_info IN VARCHAR2);
72
73 PROCEDURE Create_AR_Trx(p_trx_header_tbl IN ar_invoice_api_pub.trx_header_tbl_type,
74 p_trx_lines_tbl IN ar_invoice_api_pub.trx_line_tbl_type,
75 p_trx_dist_tbl IN ar_invoice_api_pub.trx_dist_tbl_type);
76
77 PROCEDURE create_header(p_trx_type IN VARCHAR2);
78
79 PROCEDURE create_line_dist(p_trx_type IN VARCHAR2,
80 p_amount IN NUMBER,
81 p_agreement IN VARCHAR2);
82
83 FUNCTION get_ccid(p_agreement IN VARCHAR2
84 ,p_trx_type_id IN NUMBER
85 )
86 RETURN NUMBER;
87
88 PROCEDURE print_header;
89
90 PROCEDURE print_line(p_customer_name IN VARCHAR2,
91 p_trx_num IN VARCHAR2,
92 p_trx_type IN VARCHAR2,
93 p_reimb_agree_num IN VARCHAR2,
94 p_amt IN NUMBER,
95 p_terms IN VARCHAR2);
96
97
98
99 PROCEDURE main
100 ( p_errbuf OUT NOCOPY VARCHAR2,
101 p_retcode OUT NOCOPY NUMBER,
102 p_period_name IN VARCHAR2,
103 p_invoice_date IN VARCHAR2) IS
104
105 l_module VARCHAR2(500) := g_module_name||'.Main';
106 l_log_mesg VARCHAR2(1000);
107 l_amt_not_billed NUMBER;
108 l_error_message VARCHAR2(600);
109 l_error_code BOOLEAN;
110 l_flag BOOLEAN;
111
112 l_sql_agreement VARCHAR2(2500);
113 l_sql_glbal VARCHAR2(2500);
114 l_sql_glbc VARCHAR2(2500);
115 l_sql_balances VARCHAR2(3500);
116 invalid_acct_segment_error EXCEPTION;
117
118 TYPE ref_type IS REF CURSOR ;
119 agree_cur ref_type;
120 glbal_cur ref_type;
121 glbc_cur ref_type;
122
123 l_err_code NUMBER;
124 l_exp_child NUMBER:=0;
125 l_rev_child NUMBER:=0;
126 l_adv_child NUMBER:=0;
127 l_amt_rem_rec NUMBER:=0;
128 l_sql_exp_child VARCHAR2(600);
129 l_sql_rev_child VARCHAR2(600);
130 l_sql_adv_child VARCHAR2(600);
131 l_cnt number := 0;
132 l_cnt_trx number:=0;
133 l_sql_agree_range VARCHAR2(1000);
134 agree_range_cur ref_type;
135
136
137 BEGIN
138
139 fv_utility.log_mesg('Parameters: ');
140 fv_utility.log_mesg('p_period_name: '||p_period_name);
141
142 g_org_id := mo_global.get_current_org_id;
143
144 fv_utility.log_mesg('Org Id: '||g_org_id);
145
146 g_period_name := p_period_name;
147 g_invoice_date :=fnd_date.canonical_to_date(p_invoice_date);
148
149 mo_utils.Get_Ledger_Info
150 ( p_operating_unit => g_org_id
151 , p_ledger_id => g_ledger_id
152 , p_ledger_name => g_ledger_name);
153
154 SELECT period_year, period_num
155 INTO g_period_year, g_period_num
156 FROM gl_period_statuses
157 WHERE application_id = 101
158 AND set_of_books_id = g_ledger_id
159 AND period_name = g_period_name;
160
161 fv_utility.log_mesg('period year: '||g_period_year);
162 fv_utility.log_mesg('period num: '||g_period_num);
163 fv_utility.log_mesg('p_invoice_date: '||p_invoice_date);
164 fv_utility.log_mesg('Ledger: '||g_ledger_name);
165 fv_utility.log_mesg('-----------------------------');
166
167 select CHART_OF_ACCOUNTS_ID
168 into g_coa_id
169 from gl_ledgers
170 where ledger_id = g_ledger_id;
171
172 fv_utility.log_mesg('g_coa_id: '||g_coa_id);
173
174
175 BEGIN
176 SELECT REC_DUE_TRANSACTION_TYPE_ID,
177 LIQ_ADV_TRANSACTION_TYPE_ID,
178 REIM_TRANSACTION_SOURCE_ID,
179 REC_DUE_PREFIX,
180 LIQ_ADV_PREFIX
181 INTO
182 g_rec_due_trx_type_id,
183 g_liq_adv_trx_type_id,
184 g_trx_source_id,
185 g_rec_due_prefix,
186 g_liq_adv_prefix
187 From FV_OPERATING_UNITS_ALL
188 where set_of_books_id = g_ledger_id
189 and org_id = g_org_id;
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 log(C_STATE_LEVEL, l_module, 'Transaction Set up not done in Federal Options form ');
193 END;
194
195 log(C_STATE_LEVEL, l_module, 'g_rec_due_trx_type_id: '||g_rec_due_trx_type_id);
196 log(C_STATE_LEVEL, l_module, 'g_liq_adv_trx_type_id: '||g_liq_adv_trx_type_id);
197 log(C_STATE_LEVEL, l_module, 'g_trx_source_id: '||g_trx_source_id);
198 log(C_STATE_LEVEL, l_module, 'g_rec_due_prefix: '||g_rec_due_prefix);
199 log(C_STATE_LEVEL, l_module, 'g_liq_adv_prefix: '||g_liq_adv_prefix);
200
201 BEGIN
202 SELECT application_column_name
203 INTO g_bfy_segment
204 FROM fv_pya_fiscalyear_segment
205 WHERE set_of_books_id = g_ledger_id;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 log(C_STATE_LEVEL, l_module, 'Error deriving the bfy segment ');
209 END;
210
211 -- Bug 8968764: Added Begin/Exception block to handle invalid_acct_segment_error
212 -- exception
213 BEGIN
214 log(C_STATE_LEVEL, l_module, 'bfy segment: '||g_bfy_segment);
215 -- finding the Account and Balancing segments
216 FV_UTILITY.get_segment_col_names(g_coa_id,
217 g_gl_nat_acc_segment,
218 g_gl_balancing_segment,
219 l_error_code,
220 l_error_message);
221 log(C_STATE_LEVEL, l_module, 'g_gl_balancing_segment: '||g_gl_balancing_segment);
222 log(C_STATE_LEVEL, l_module, 'g_gl_nat_acc_segment: '||g_gl_nat_acc_segment);
223
224 --finding the flex_value_set_id for the Natural Account segment
225 SELECT flex_value_set_id into g_ussgl_flex_value_set_id
226 FROM fnd_id_flex_segments
227 WHERE application_column_name = g_gl_nat_acc_segment
228 AND application_id = 101
229 AND id_flex_code = 'GL#'
230 AND id_flex_num = g_coa_id;
231 log(C_STATE_LEVEL, l_module, 'g_ussgl_flex_value_set_id: '||g_ussgl_flex_value_set_id);
232
233 EXCEPTION
234
235 WHEN invalid_acct_segment_error THEN
236 log(C_STATE_LEVEL, l_module, 'Error deriving flex value set id.');
237 END;
238
239 --Finding the Reimbursable Segment name from the reimb segment defined
240 -- in the Federal Financial Options
241 BEGIN
242 SELECT application_column_name
243 INTO g_reimb_agreement_segment
244 FROM FND_ID_FLEX_SEGMENTS_VL
245 WHERE application_id = 101
246 AND id_flex_code = 'GL#'
247 AND id_flex_num = g_coa_id
248 AND enabled_flag = 'Y'
249 AND segment_name like
250 (Select REIMB_AGREEMENT_SEGMENT_VALUE
251 from fv_reimb_segment
252 where set_of_books_id = g_ledger_id);
253
254
255 EXCEPTION
256 WHEN no_data_found then
257 log(C_STATE_LEVEL, l_module, 'Error deriving the Reimbursable Agreement Segment ');
258 END;
259
260 log(C_STATE_LEVEL, l_module, 'g_reimb_agreement_segment: '||g_reimb_agreement_segment);
261
262
263
264 BEGIN
265 Select
266 ADVANCE_SEGMENT_VALUE,
267 REVENUE_SEGMENT_VALUE,
268 EXPENDITURE_SEGMENT_VALUE
269 INTO
270 g_advance_acc,
271 g_revenue_acc,
272 g_expenditure_acc
273 from fv_reimb_segment
274 where set_of_books_id = g_ledger_id;
275
276 EXCEPTION
277 when no_data_found then
278 log(C_STATE_LEVEL, l_module, 'Error deriving the Advance, Expenditure and Revenue Account ');
279 END;
280
281 log(C_STATE_LEVEL, l_module, 'g_advance_acc: '||g_advance_acc);
282 log(C_STATE_LEVEL, l_module, 'g_revenue_acc: '||g_revenue_acc);
283 log(C_STATE_LEVEL, l_module, 'g_expenditure_acc: '||g_expenditure_acc);
284
285 BEGIN
286 --Finding Child values if expenditure account on federal Financials options form is a parent account
287
288 select count(distinct(FLEX_VALUE)) into l_exp_child
289 from FND_FLEX_VALUE_CHILDREN_V
290 where FLEX_VALUE_SET_ID = g_ussgl_flex_value_set_id
291 start with PARENT_FLEX_VALUE = g_expenditure_acc
292 connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE;
293
294 If l_exp_child > 0 THEN
295
296 l_sql_exp_child := 'select distinct(FLEX_VALUE)
297 from FND_FLEX_VALUE_CHILDREN_V
298 where FLEX_VALUE_SET_ID = '||g_ussgl_flex_value_set_id||'
299 start with PARENT_FLEX_VALUE = '||''''||g_expenditure_acc||''''||'
300 connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE';
301
302 ELSE
303
304 l_sql_exp_child:= g_expenditure_acc;
305 END IF;
306 EXCEPTION
307 When others then
308 l_err_code:=SQLCODE;
309 l_error_message:=substr(SQLERRM, 1, 200);
310 log(C_STATE_LEVEL, l_module, 'Error deriving the child values for parent expenditure account: '||l_error_message);
311 END;
312 log(C_STATE_LEVEL, l_module, 'Child accounts of g_expenditure_acc: '||l_exp_child);
313
314
315 BEGIN
316 select count(distinct(FLEX_VALUE)) into l_rev_child
317 from FND_FLEX_VALUE_CHILDREN_V
318 where FLEX_VALUE_SET_ID = g_ussgl_flex_value_set_id
319 start with PARENT_FLEX_VALUE = g_revenue_acc
320 connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE;
321
322
323 If l_rev_child >0 THEN
324
325 l_sql_rev_child := 'select distinct(FLEX_VALUE)
326 from FND_FLEX_VALUE_CHILDREN_V
327 where FLEX_VALUE_SET_ID = '||g_ussgl_flex_value_set_id||'
328 start with PARENT_FLEX_VALUE = '||''''||g_revenue_acc||''''||'
329 connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE';
330
331 ELSE
332 l_sql_rev_child:= g_revenue_acc;
333 END IF;
334
335 EXCEPTION
336 When others then
337 l_err_code:=SQLCODE;
338 l_error_message:=substr(SQLERRM, 1, 200);
339 log(C_STATE_LEVEL, l_module, 'Error deriving the child values for parent revenue account: '||l_error_message);
340 END;
341 log(C_STATE_LEVEL, l_module, 'Child Accounts of g_revenue_acc: '||l_rev_child);
342
343 BEGIN
344 select count(distinct(FLEX_VALUE)) into l_adv_child
345 from FND_FLEX_VALUE_CHILDREN_V
346 where FLEX_VALUE_SET_ID = g_ussgl_flex_value_set_id
347 start with PARENT_FLEX_VALUE = g_advance_acc
348 connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE;
349
350 If l_adv_child >0 THEN
351
352 l_sql_adv_child := 'select distinct(FLEX_VALUE)
353 from FND_FLEX_VALUE_CHILDREN_V
354 where FLEX_VALUE_SET_ID = '||g_ussgl_flex_value_set_id||'
355 start with PARENT_FLEX_VALUE = '||''''||g_advance_acc||''''||'
356 connect by nocycle prior FLEX_VALUE = PARENT_FLEX_VALUE';
357
358 ELSE
359 l_sql_adv_child:= g_advance_acc;
360 END IF;
361
362 EXCEPTION
363 When others then
364 l_err_code:=SQLCODE;
365 l_error_message:=substr(SQLERRM, 1, 200);
366 log(C_STATE_LEVEL, l_module, 'Error deriving the child values for parent advance account: '||l_error_message);
367 END;
368 log(C_STATE_LEVEL, l_module, 'Child values of g_advance_acc: '||l_adv_child);
369
370 l_sql_agree_range :=
371 'SELECT f.flex_value
372 FROM fnd_flex_values_vl f, fnd_id_flex_segments segs, ra_customer_trx_all r,
373 ra_cust_trx_types_all t
374 WHERE f.flex_value_set_id =segs.flex_value_set_id AND
375 segs.application_column_name = :g_reimb_agreement_segment AND
376 segs.application_id = 101 AND
377 segs.id_flex_code = ''GL#'' AND
378 segs.id_flex_num = :g_coa_id AND
379 f.flex_value = r.trx_number AND
380 r.set_of_books_id = :g_ledger_id AND
381 r.invoice_currency_code = :g_currency AND
382 r.cust_trx_type_id = t.cust_trx_type_id AND
383 t.type = ''GUAR''';
384
385 log(C_STATE_LEVEL, l_module, 'l_sql_agree_range '||l_sql_agree_range);
386
387
388 l_sql_glbal := '(SELECT
389 sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_exp_child ||') then (nvl(period_net_dr,0) - nvl(period_net_cr,0)) else 0 end) expenses,
390 sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_rev_child ||') then (nvl(period_net_dr,0) - nvl(period_net_cr,0)) else 0 end) revenues,
391 sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_adv_child ||') then (nvl(period_net_dr,0) - nvl(period_net_cr,0)) else 0 end) advances
392 FROM gl_balances glb,
393 gl_code_combinations glc
394 WHERE glb.actual_flag = ''A''
395 and glb.ledger_id = :g_ledger_id
396 AND glb.template_id is NULL
397 AND glb.currency_code = ''USD''
398 AND glb.code_combination_id = glc.code_combination_id
399 and glc.chart_of_accounts_id = :g_coa_id
400 and glc.'||g_gl_balancing_segment||' <> ''0''
401 and glb.period_year = :g_period_year
402 and glc.'||g_reimb_agreement_segment||' = :g_agreement)';
403
404
405 log(C_STATE_LEVEL, l_module, 'l_sql_glbal: '||l_sql_glbal);
406
407 -- Modified for bug 8815978
408 l_sql_glbc := '(SELECT
409 sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_exp_child ||') then (nvl(accounted_dr,0) - nvl(accounted_cr,0)) else 0 end) expenses,
410 sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_rev_child ||') then (nvl(accounted_dr,0) - nvl(accounted_cr,0)) else 0 end) revenues,
411 sum(case when glc.'||g_gl_nat_acc_segment||' in ( '||l_sql_adv_child ||') then (nvl(accounted_dr,0) - nvl(accounted_cr,0)) else 0 end) advances
412 FROM gl_bc_packets glbc,
413 gl_code_combinations glc
414 WHERE glbc.actual_flag = ''A''
415 and glbc.ledger_id = :g_ledger_id
416 AND glbc.template_id is NULL
417 AND glbc.status_code = ''A''
418 AND glbc.currency_code = ''USD''
419 AND glbc.code_combination_id = glc.code_combination_id
420 and glc.chart_of_accounts_id = :g_coa_id
421 and glc.'||g_gl_balancing_segment||' <> ''0''
422 and glbc.period_year = :g_period_year
423 and glc.'||g_reimb_agreement_segment||' = :g_agreement)';
424
425
426 log(C_STATE_LEVEL, l_module, 'l_sql_glbc: '||l_sql_glbc);
427
428
429 l_sql_balances:='select sum(expenses) tot_exp, sum(revenues) tot_rev,
430 sum(advances) tot_adv from
431 ('||l_sql_glbal||' UNION ALL '||l_sql_glbc||')';
432
433 log(C_STATE_LEVEL, l_module, 'SQL calculates sum of the union from gl_balances and gl_bc_packets ');
434 log(C_STATE_LEVEL, l_module, 'l_sql_balances: '||l_sql_balances);
435
436
437 OPEN agree_range_cur FOR l_sql_agree_range USING g_reimb_agreement_segment,g_coa_id,g_ledger_id,g_currency;
438
439 log(C_STATE_LEVEL, l_module, 'Opened agree_range_cur ');
440
441 LOOP
442 fetch agree_range_cur INTO g_agreement_num;
443 exit when agree_range_cur%notfound;
444
445 log(C_STATE_LEVEL, l_module, 'Agreement Num from Range: '||g_agreement_num);
446
447
448 l_amt_not_billed := 0;
449 g_coll_hdr_tbl.DELETE;
450 g_rec_hdr_tbl.DELETE;
451 g_coll_lines_tbl.DELETE;
452 g_rec_lines_tbl.DELETE;
453 g_coll_dist_tbl.DELETE;
454 g_rec_dist_tbl.DELETE;
455 i := 0;
456 j := 0;
457 --Get the customer id and other details from the reimb agreement
458 BEGIN
459
460
461 SELECT bill_to_customer_id
462 INTO g_customer_id
463 FROM ra_customer_trx_all
464 WHERE trx_number = g_agreement_num
465 AND invoice_currency_code = g_currency;
466
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 l_log_mesg :=
470 'No data found for agreement: '||g_agreement||' - Aborting process!!';
471 log(C_STATE_LEVEL, l_module, l_log_mesg);
472 p_errbuf := l_log_mesg;
473 p_retcode := 2;
474
475 WHEN OTHERS THEN
476 l_log_mesg :=
477 'When others error in exception 1: '||l_module||': '||sqlerrm;
478 log(C_STATE_LEVEL, l_module, l_log_mesg);
479 p_errbuf := l_log_mesg;
480 p_retcode := 2;
481 END;
482
483 log(C_STATE_LEVEL, l_module, 'Fetching balances from gl_balances and gl_bc_packets...');
484
485 OPEN glbal_cur FOR l_sql_balances USING g_ledger_id,g_coa_id,g_period_year,g_agreement_num,g_ledger_id,g_coa_id,g_period_year,g_agreement_num;
486 LOOP
487
488 FETCH glbal_cur into g_tot_expenses,g_tot_revenues,g_tot_advances;
489
490 exit when glbal_cur%notfound;
491
492
493 log(C_STATE_LEVEL, l_module, 'Expenses: '||ABS(g_tot_expenses));
494 log(C_STATE_LEVEL, l_module, 'Revenues: '||g_tot_revenues);
495 log(C_STATE_LEVEL, l_module, 'Advances: '||g_tot_advances);
496
497 l_amt_not_billed := ABS(g_tot_expenses) - g_tot_revenues;
498 log(C_STATE_LEVEL, l_module, 'l_amt_not_billed: '||l_amt_not_billed);
499
500
501 --If expenses are greater than revenues, then need to create ar trx
502 --for the excess of expenses over revenues (l_amt_not_billed).
503 --Check if there are any advances. If there are advances and l_amt_not_billed
504 --greater than the advances, create ar trx for the advance amount, then create
505 --ar trx for l_amt_not_billed less advance amount
506 IF l_amt_not_billed > 0 THEN
507 --fv_utility.log_mesg('To be billed: '||l_amt_not_billed);
508 ---if there is advance
509 IF ABS(g_tot_advances) <> 0 THEN
510 ---if difference of expenses and revenues is greater than advance
511 IF ABS(g_tot_advances) <= l_amt_not_billed THEN
512 --create a transaction for the amount of advance
513 l_cnt_trx:=l_cnt_trx+1;
514 create_header('COLL');
515 log(C_STATE_LEVEL, l_module, 'Total Advances liquidated, Line Amount for Liquidating: '||ABS(g_tot_advances));
516 create_line_dist('COLL',ABS(g_tot_advances),
517 g_agreement_num);
518
519 IF g_retcode <> 0 THEN
520 p_errbuf := g_errbuf;
521 p_retcode := g_retcode;
522 RETURN;
523 END IF;
524 -----------------------------------------------------------------------------
525 --Create trx for rec trx type
526 IF l_amt_not_billed - ABS(g_tot_advances) > 0 THEN
527 l_cnt_trx:=l_cnt_trx+1;
528 l_amt_rem_rec:=l_amt_not_billed - ABS(g_tot_advances);
529 create_header('REC');
530 log(C_STATE_LEVEL, l_module, 'Rec due for unbilled amount Amount after liquidating advances : '||l_amt_rem_rec);
531 create_line_dist('REC',(l_amt_not_billed - ABS(g_tot_advances)),
532 g_agreement_num);
533 IF g_retcode <> 0 THEN
534 p_errbuf := g_errbuf;
535 p_retcode := g_retcode;
536 RETURN;
537 END IF;
538
539 END IF;
540
541 ELSIF (ABS(g_tot_advances) > l_amt_not_billed) THEN
542 l_cnt_trx:=l_cnt_trx+1;
543 create_header('COLL');
544 log(C_STATE_LEVEL, l_module, 'Total Advances more, liquidating part of advance, equal to unbilled amt, Line Amount for Liquidating: '||l_amt_not_billed);
545 create_line_dist('COLL',l_amt_not_billed,
546 g_agreement_num);
547 IF g_retcode <> 0 THEN
548 p_errbuf := g_errbuf;
549 p_retcode := g_retcode;
550 RETURN;
551 END IF;
552
553 END IF;
554
555 ELSE --if advances = 0
556 ---------------------------------------
557 l_cnt_trx:=l_cnt_trx+1;
558 create_header('REC');
559 log(C_STATE_LEVEL, l_module, 'No Advances,creating rec due for unbilled amount , line amount: '||l_amt_not_billed);
560 create_line_dist('REC',l_amt_not_billed,
561 g_agreement_num);
562 IF g_retcode <> 0 THEN
563 p_errbuf := g_errbuf;
564 p_retcode := g_retcode;
565 RETURN;
566 END IF;
567
568 END IF;
569 END IF;
570
571 END LOOP;
572 log(C_STATE_LEVEL, l_module, 'No of Transactions that will be created,l_cnt_trx: '||l_cnt_trx);
573 close glbal_cur;
574
575 log(C_STATE_LEVEL, l_module, 'No of Transactions that will be created,l_cnt_trx: '||l_cnt_trx);
576
577 --If all hdrs, lines and dist created, then
578 --submit api to create ar trx
579 IF g_coll_lines_tbl.COUNT > 0 THEN
580 create_ar_trx(g_coll_hdr_tbl,
581 g_coll_lines_tbl,
582 g_coll_dist_tbl);
583 IF g_retcode <> 0 THEN
584 p_errbuf := g_errbuf;
585 p_retcode := g_retcode;
586 RETURN;
587 END IF;
588 END IF;
589
590 IF g_rec_lines_tbl.COUNT > 0 THEN
591 create_ar_trx(g_rec_hdr_tbl,
592 g_rec_lines_tbl,
593 g_rec_dist_tbl);
594 IF g_retcode <> 0 THEN
595 p_errbuf := g_errbuf;
596 p_retcode := g_retcode;
597 RETURN;
598 END IF;
599 END IF;
600
601 END LOOP;
602 CLOSE agree_range_cur;
603
604 END main;
605 -------------------------------------------------------------------------------
606 PROCEDURE log (
607 p_level IN NUMBER,
608 p_procedure_name IN VARCHAR2,
609 p_debug_info IN VARCHAR2)
610 IS
611
612 BEGIN
613 IF (p_level >= g_log_level ) THEN
614 FND_LOG.STRING(p_level,
615 p_procedure_name,
616 p_debug_info);
617 END IF;
618 END log;
619 -------------------------------------------------------------------------------
620 PROCEDURE Create_AR_Trx(p_trx_header_tbl IN ar_invoice_api_pub.trx_header_tbl_type,
621 p_trx_lines_tbl IN ar_invoice_api_pub.trx_line_tbl_type,
622 p_trx_dist_tbl IN ar_invoice_api_pub.trx_dist_tbl_type)
623
624 IS
625 l_module VARCHAR2(240) := g_module_name||'Create_AR_Trx';
626 l_debug_info VARCHAR2(240);
627
628
629 CURSOR cur_ar_trx_err IS
630 SELECT * FROM ar_trx_errors_gt;
631 l_return_status varchar2(250);
632 l_msg_count number;
633 l_msg_data varchar2(2000);
634 l_customer_trx_id number;
635 l_cnt number := 0;
636 l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
637 l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
638
639 l_reimb_agree_num VARCHAR2(50);
640 l_api_version CONSTANT NUMBER := 1.0;
641
642 l_reimb_seg_sql VARCHAR2(300);
643 l_customer_name VARCHAR2(500);
644 l_trx_type VARCHAR2(100);
645 l_terms VARCHAR2(100);
646 BEGIN
647
648 log(C_STATE_LEVEL, l_module, 'Begin: '||l_module);
649
650 log(C_STATE_LEVEL, l_module, '----------Header----------');
651 log(C_STATE_LEVEL, l_module, 'trx_header_id: '||p_trx_header_tbl(1).trx_header_id);
652 log(C_STATE_LEVEL, l_module, 'trx_number: '||p_trx_header_tbl(1).trx_number);
653 log(C_STATE_LEVEL, l_module, 'cust_trx_type_id: '||p_trx_header_tbl(1).cust_trx_type_id);
654 log(C_STATE_LEVEL, l_module, 'trx_date: '||p_trx_header_tbl(1).trx_date);
655 log(C_STATE_LEVEL, l_module, 'bill_to_customer_id: '||p_trx_header_tbl(1).bill_to_customer_id);
656 log(C_STATE_LEVEL, l_module, 'gl_date: '||p_trx_header_tbl(1).gl_date);
657 log(C_STATE_LEVEL, l_module, 'trx_currency: '||p_trx_header_tbl(1).trx_currency) ;
658 log(C_STATE_LEVEL, l_module, 'primary_salesrep_id: '||p_trx_header_tbl(1).primary_salesrep_id);
659
660
661 FOR i IN 1..p_trx_lines_tbl.COUNT LOOP
662 log(C_STATE_LEVEL, l_module, '----------Lines----------');
663 log(C_STATE_LEVEL, l_module, 'trx_header_id: '||p_trx_lines_tbl(i).trx_header_id);
664 log(C_STATE_LEVEL, l_module, 'trx_line_id: '||p_trx_lines_tbl(i).trx_line_id);
665 log(C_STATE_LEVEL, l_module, 'line_number: '||p_trx_lines_tbl(i).line_number);
666 log(C_STATE_LEVEL, l_module, 'quantity_invoiced: '||p_trx_lines_tbl(i).quantity_invoiced);
667 log(C_STATE_LEVEL, l_module, 'unit_selling_price: '||p_trx_lines_tbl(i).unit_selling_price);
668 log(C_STATE_LEVEL, l_module, 'uom_code: '||p_trx_lines_tbl(i).uom_code);
669 log(C_STATE_LEVEL, l_module, 'description: '||p_trx_lines_tbl(i).description);
670 log(C_STATE_LEVEL, l_module, 'line_type: '||p_trx_lines_tbl(i).line_type);
671 END LOOP;
672
673
674 FOR i IN 1..p_trx_dist_tbl.COUNT LOOP
675 log(C_STATE_LEVEL, l_module, '----------Dist----------');
676 log(C_STATE_LEVEL, l_module, 'trx_header_id: '||p_trx_dist_tbl(i).trx_header_id);
677 log(C_STATE_LEVEL, l_module, 'trx_line_id: '||p_trx_dist_tbl(i).trx_line_id);
678 log(C_STATE_LEVEL, l_module, 'trx_dist_id: '||p_trx_dist_tbl(i).trx_dist_id);
679 log(C_STATE_LEVEL, l_module, 'account_class: '||p_trx_dist_tbl(i).account_class);
680 log(C_STATE_LEVEL, l_module, 'code_combination_id: '||p_trx_dist_tbl(i).code_combination_id);
681 log(C_STATE_LEVEL, l_module, 'amount: '||p_trx_dist_tbl(i).amount);
682
683
684
685 --Get the reimbursable agreement number from ccid
686 /* l_reimb_seg_sql:=
687 'SELECT ' || g_reimb_agreement_segment || '
688 FROM gl_code_combinations
689 WHERE code_combination_id = ' ||p_trx_dist_tbl(i).code_combination_id;
690
691
692 log(C_STATE_LEVEL, l_module, 'Reimb segment sql: '||l_reimb_seg_sql);
693
694
695 EXECUTE IMMEDIATE l_reimb_seg_sql into l_reimb_agree_num;*/
696 -- Bug 8824917
697 l_reimb_agree_num:= g_agreement_num;
698 log(C_STATE_LEVEL, l_module, 'l_reimb_agree_num: '||l_reimb_agree_num);
699 BEGIN
700 Select rtt.name into l_terms
701 from ra_customer_trx_all rct,
702 ra_terms rtt
703 where rct.term_id=rtt.term_id
704 and rct.trx_number = l_reimb_agree_num
705 and rct.set_of_books_id = g_ledger_id;
706 EXCEPTION
707 when no_data_found then
708 log(C_STATE_LEVEL, l_module, 'unexpected errors found while deriving Terms!');
709 END;
710 log(C_STATE_LEVEL, l_module, 'l_terms: '||l_terms);
711
712 BEGIN
713 select hzp.party_name into l_customer_name
714 from hz_parties hzp ,
715 HZ_CUST_ACCounts hza
716 where hzp.party_id = hza.party_id
717 and hza.cust_account_id = p_trx_header_tbl(1).bill_to_customer_id;
718 EXCEPTION when others then
719 null;
720 END;
721 log(C_STATE_LEVEL, l_module, 'l_customer_name: '||l_customer_name);
722
723 Select name into l_trx_type
724 from ra_cust_trx_types_all
725 where cust_trx_type_id = p_trx_header_tbl(1).cust_trx_type_id;
726
727 log(C_STATE_LEVEL, l_module, 'l_trx_type: '||l_trx_type);
728
729
730
731 END LOOP;
732 log(C_STATE_LEVEL, l_module, 'Befor mo_global Out of Loop after printing lines ');
733 -- mo_global.init('AR');
734 log(C_STATE_LEVEL, l_module, 'After MO_global');
735
736 l_batch_source_rec.batch_source_id :=g_trx_source_id;
737 log(C_STATE_LEVEL, l_module, 'g_trx_source_id: '||l_batch_source_rec.batch_source_id);
738
739
740 AR_INVOICE_API_PUB.create_single_invoice(
741 p_api_version => l_api_version,
742 p_init_msg_list => FND_API.G_TRUE,
743 p_commit => FND_API.G_FALSE,
744 p_batch_source_rec => l_batch_source_rec,
745 p_trx_header_tbl => p_trx_header_tbl,
746 p_trx_lines_tbl => p_trx_lines_tbl,
747 p_trx_dist_tbl => p_trx_dist_tbl,
748 p_trx_salescredits_tbl => l_trx_salescredits_tbl,
749 x_customer_trx_id => l_customer_trx_id,
750 x_return_status => l_return_status,
751 x_msg_count => l_msg_count,
752 x_msg_data => l_msg_data);
753
754 log(C_STATE_LEVEL, l_module, 'After calling single_invoice');
755 IF (l_return_status = fnd_api.g_ret_sts_error OR
756 l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
757 log(C_STATE_LEVEL, l_module, 'unexpected errors found!');
758 log(C_STATE_LEVEL, l_module, 'l_msg_data: ' || l_msg_data);
759 ELSE
760 -- Check whether any record exist in error table
761
762 SELECT count(*)
763 Into l_cnt
764 From ar_trx_errors_gt;
765
766 log(C_STATE_LEVEL, l_module, 'rows in ar_trx_errors_gt '|| l_cnt);
767
768 IF (l_cnt = 0) THEN
769 log(C_STATE_LEVEL, l_module, 'Customer Trx id '|| l_customer_trx_id);
770
771 FOR i IN 1..p_trx_dist_tbl.COUNT LOOP
772 --Print report header
773 IF NOT g_header_printed THEN
774 print_header;
775 g_header_printed := TRUE;
776 END IF;
777 --Print report line --1
778 print_line(l_customer_name,p_trx_header_tbl(1).trx_number,l_trx_type, l_reimb_agree_num,
779 p_trx_dist_tbl(i).amount,l_terms);
780 END LOOP;
781
782 ELSE
783 log(C_STATE_LEVEL, l_module, 'Transaction not Created, Please check ar_trx_errors_gt table');
784 log(C_STATE_LEVEL, l_module, '==============================================================');
785 FOR err IN cur_ar_trx_err LOOP
786 log(C_STATE_LEVEL, l_module, 'ar_trx_error_gt column TRX HEADER ID: ' || err.invalid_value);
787 log(C_STATE_LEVEL, l_module, 'ar_trx_error_gt column TRX LINE ID: ' || err.invalid_value);
788 log(C_STATE_LEVEL, l_module, 'ar_trx_error_gt column ERROR MESSAGE: ' || err.error_message);
789 log(C_STATE_LEVEL, l_module, 'ar_trx_error_gt column INVALID VALUE: ' || err.invalid_value);
790 END LOOP;
791 log(C_STATE_LEVEL, l_module, '==============================================================');
792 END IF;
793 END IF;
794
795 -- ================================== FND_LOG ==================================
796 l_debug_info := 'End of procedure '||l_module;
797 log(C_STATE_LEVEL, l_module, l_debug_info);
798 -- ================================== FND_LOG ==================================
799
800 EXCEPTION
801 WHEN OTHERS THEN
802 log(C_STATE_LEVEL, l_module, 'Transaction not Created, because of SQL error: ' || SQLCODE);
803 log(C_STATE_LEVEL, l_module, 'Transaction not Created, because of SQL error: ' || SQLERRM);
804 g_errbuf := 'Transaction not Created, because of SQL error: ' || SQLERRM;
805 g_retcode := sqlerrm;
806 END;
807 -------------------------------------------------------------------------------
808 --Overlay the CCID for REV
809 FUNCTION get_ccid(p_agreement IN VARCHAR2
810 ,p_trx_type_id IN NUMBER) RETURN NUMBER IS
811
812 l_segment4 VARCHAR2(25);
813 l_segment5 VARCHAR2(25);
814 l_ccid NUMBER;
815 l_agreement_rev_segments FND_FLEX_EXT.SEGMENTARRAY;
816 l_var NUMBER;
817 l_concat_segs VARCHAR2(500);
818 l_module VARCHAR2(100) := g_module_name||' get_ccid';
819 l_rev_nat_acc_sql VARCHAR2(400);
820 l_rev_nat_acc_tt VARCHAR2(50);
821 l_agreement_rev_ccid NUMBER;
822 l_nat_acct_seg_num NUMBER;
823 l_num_segments NUMBER;
824
825 BEGIN
826
827 log(C_STATE_LEVEL, l_module, 'In: '||l_module);
828 log(C_STATE_LEVEL, l_module, 'p_agreement: '||p_agreement);
829 log(C_STATE_LEVEL, l_module, 'p_trx_type_id: '||p_trx_type_id);
830
831 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM( APPL_ID => 101,
832 KEY_FLEX_CODE => 'GL#',
833 STRUCTURE_NUMBER => g_coa_id,
834 FLEX_QUAL_NAME => 'GL_ACCOUNT',
835 SEGMENT_NUMBER => l_nat_acct_seg_num)) -- OUT
836
837
838
839
840 then
841 RAISE GET_QUALIFIER_SEGNUM_EXCEP;
842 else
843 log(C_STATE_LEVEL, l_module, 'Segment Number for the USSGL segment is : ' || l_nat_acct_seg_num);
844 end if;
845
846 l_var := 1;
847
848 --Step 1 Get the Revenue acc ccid from Reimbursible Agreement Transaction
849 -- As per FD the Reimbursible Agreement Trx will have only one Revenue Line.
850
851 SELECT rctd.code_combination_id
852 into l_agreement_rev_ccid
853 FROM ra_customer_trx_all rct,
854 RA_CUST_TRX_LINE_GL_DIST_ALL rctd
855 WHERE rct.trx_number = p_agreement
856 AND rct.customer_trx_id = rctd.customer_trx_id
857 AND rctd.account_class = 'REV'
858 AND rctd.set_of_books_id = g_ledger_id;
859
860 log(C_STATE_LEVEL, l_module, 'l_agreement_rev_ccid: '||l_agreement_rev_ccid);
861 --Step 2 Get the Natural account segment of the Rec account of the transation type
862 --Step 2a combine gl_c_c and ra_cust_trx_types_all to get the natural segment
863 l_rev_nat_acc_sql:=
864 'SELECT g.'||g_gl_nat_acc_segment||'
865 FROM ra_cust_trx_types_all t,
866 gl_code_combinations g
867 WHERE t.cust_trx_type_id = :p_trx_type_id
868 AND t.gl_id_rev = g.code_combination_id
869 and g.chart_of_accounts_id = :g_coa_id';
870
871 EXECUTE IMMEDIATE l_rev_nat_acc_sql into l_rev_nat_acc_tt USING p_trx_type_id, g_coa_id;
872
873 log(C_STATE_LEVEL, l_module, 'l_rev_nat_acc_tt: '||l_rev_nat_acc_tt);
874
875 IF (NOT FND_FLEX_EXT.GET_SEGMENTS(
876 APPLICATION_SHORT_NAME => 'SQLGL',
877 KEY_FLEX_CODE => 'GL#',
878 STRUCTURE_NUMBER => g_coa_id,
879 COMBINATION_ID => l_agreement_rev_ccid,
880 N_SEGMENTS => l_num_segments, -- OUT
881 SEGMENTS => l_agreement_rev_segments)) -- OUT
882 THEN
883 RAISE GET_SEGMENTS_EXCEP;
884 ELSE
885 log(C_STATE_LEVEL, l_module, 'Segment Number for the USSGL segment is : ' || l_nat_acct_seg_num);
886 END IF;
887
888 ---Assign the natural account segment value of the transaction type Rev acc to
889 -- the ccid for the reimbursable agreement
890 l_agreement_rev_segments(l_nat_acct_seg_num) := l_rev_nat_acc_tt;
891 Begin
892 l_concat_segs:= fnd_flex_ext.concatenate_segments(n_segments =>l_num_segments,
893 segments =>l_agreement_rev_segments,
894 delimiter =>'.');
895
896
897 log(C_STATE_LEVEL, l_module, 'Concatenated Segments: ' || l_concat_segs);
898 --STEP 3 Check of ccid exists for the new concatenated segments, else Create new ccid
899
900 SELECT code_combination_id
901 INTO l_ccid
902 FROM gl_code_combinations_kfv
903 WHERE chart_of_accounts_id = g_coa_id
904 AND concatenated_segments = l_concat_segs;
905 log(C_STATE_LEVEL, l_module, 'CCID already existing: ' || l_ccid);
906
907 EXCEPTION
908 when no_data_found then
909 l_ccid:=fnd_flex_ext.get_ccid( APPLICATION_SHORT_NAME => 'SQLGL',
910 KEY_FLEX_CODE => 'GL#',
911 STRUCTURE_NUMBER => g_coa_id,
912 validation_date => SYSDATE,
913 concatenated_segments =>l_concat_segs);
914 log(C_STATE_LEVEL, l_module, 'CCID created new: ' || l_ccid);
915
916 IF l_ccid = 0 THEN
917 log(C_STATE_LEVEL, l_module, l_module||'ERROR: Could not create ccid.');
918 g_errbuf := l_module||'ERROR: Could not create ccid.';
919 g_retcode := 2;
920 RETURN l_ccid;
921 END IF;
922 log(C_STATE_LEVEL, l_module, 'Returning ccid: '||l_ccid);
923 RETURN l_ccid;
924 END;
925
926 log(C_STATE_LEVEL, l_module, 'Returning ccid: '||l_ccid);
927 RETURN l_ccid;
928
929 END get_ccid;
930 -------------------------------------------------------------------------------
931 PROCEDURE create_header(p_trx_type IN VARCHAR2) IS
932
933 l_module VARCHAR2(100) := g_module_name||'create_header';
934 BEGIN
935
936 log(C_STATE_LEVEL, l_module, 'In :'||l_module);
937 log(C_STATE_LEVEL, l_module, 'Trx Type: '||p_trx_type);
938
939 IF (p_trx_type = 'COLL' AND g_coll_hdr_tbl.COUNT = 0) THEN
940
941
942 SELECT fv_gen_ar_trx_s.nextval
943 INTO g_trx_coll_hdr_id
944 FROM DUAL;
945
946 g_coll_hdr_tbl(1).trx_header_id := g_trx_coll_hdr_id;
947 -- Use the prefix defined for Liquidate Advance in the
948 -- define Federal options Form. FV_OPERATING_UNITS_ALL
949
950 SELECT g_liq_adv_prefix||fv_gen_coll_ar_trx_s.nextval
951 INTO g_coll_hdr_tbl(1).trx_number
952 FROM DUAL;
953
954 fv_utility.log_mesg('Ar Transaction: '||g_coll_hdr_tbl(1).trx_number);
955
956 --Transaction type defined for Liquidate Advance in the
957 --define Federal options Form. FV_OPERATING_UNITS_ALL
958
959 g_coll_hdr_tbl(1).cust_trx_type_id :=g_liq_adv_trx_type_id;
960 g_coll_hdr_tbl(1).trx_date := g_invoice_date;
961 g_coll_hdr_tbl(1).bill_to_customer_id := g_customer_id;
962 g_coll_hdr_tbl(1).gl_date := g_invoice_date;
963 g_coll_hdr_tbl(1).trx_currency := g_currency;
964
965 END IF;
966
967 IF (p_trx_type = 'REC' AND g_rec_hdr_tbl.COUNT = 0) THEN
968
969 SELECT fv_gen_ar_trx_s.nextval
970 INTO g_trx_rec_hdr_id
971 FROM DUAL;
972
973 g_rec_hdr_tbl(1).trx_header_id := g_trx_rec_hdr_id;
974
975 --Use the prefix defined for Receivables Due in the
976 --define Federal options Form. FV_OPERATING_UNITS_ALL
977
978 SELECT g_rec_due_prefix||fv_gen_rec_ar_trx_s.nextval
979 INTO g_rec_hdr_tbl(1).trx_number
980 FROM DUAL;
981
982 fv_utility.log_mesg('Ar Transaction: '||g_rec_hdr_tbl(1).trx_number);
983
984 --Transaction type defined for Receivables Due in the
985 --define Federal options Form. FV_OPERATING_UNITS_ALL
986
987 --The trx_date and gl_date to be that of the parameter invoice_date passed as parameter
988
989 g_rec_hdr_tbl(1).cust_trx_type_id := g_rec_due_trx_type_id; --3896; --Reimb Earned - Rec
990 g_rec_hdr_tbl(1).trx_date := g_invoice_date;
991 g_rec_hdr_tbl(1).bill_to_customer_id := g_customer_id;
992 g_rec_hdr_tbl(1).gl_date := g_invoice_date;
993 g_rec_hdr_tbl(1).trx_currency := g_currency;
994 g_rec_hdr_tbl(1).primary_salesrep_id := -3;
995 END IF;
996 EXCEPTION
997 WHEN OTHERS THEN
998 log(C_STATE_LEVEL, l_module, 'When others error in'||l_module||': '||sqlerrm);
999 g_errbuf := 'When others error in'||l_module||': '||sqlerrm ;
1000 g_retcode := sqlcode;
1001 END create_header;
1002 -------------------------------------------------------------------------------
1003 PROCEDURE create_line_dist(p_trx_type IN VARCHAR2,
1004 p_amount IN NUMBER,
1005 p_agreement IN VARCHAR2) IS
1006 BEGIN
1007
1008 IF p_trx_type = 'COLL' THEN
1009 i := i+1;
1010
1011 g_coll_lines_tbl(i).trx_header_id := g_trx_coll_hdr_id;
1012
1013 SELECT ra_customer_trx_lines_s.nextval
1014 INTO g_coll_lines_tbl(i).trx_line_id
1015 FROM DUAL;
1016
1017 g_coll_lines_tbl(i).line_number := i;
1018 g_coll_lines_tbl(i).quantity_invoiced := p_amount;
1019 g_coll_lines_tbl(i).unit_selling_price := 1;
1020 g_coll_lines_tbl(i).description := 'Generate Receivable for Reimbursable Related Expense - Liquidate Advance';
1021 g_coll_lines_tbl(i).line_type := 'LINE';
1022 g_coll_lines_tbl(i).uom_code := 'EA';
1023
1024
1025 --Create distribution
1026 g_coll_dist_tbl(i).trx_line_id := g_coll_lines_tbl(i).trx_line_id;
1027 g_coll_dist_tbl(i).trx_header_id := g_coll_lines_tbl(i).trx_header_id;
1028
1029 SELECT ra_cust_trx_line_gl_dist_s.nextval
1030 INTO g_coll_dist_tbl(i).trx_dist_id
1031 FROM DUAL;
1032
1033 g_coll_dist_tbl(i).account_class := 'REV';
1034 g_coll_dist_tbl(i).code_combination_id :=
1035 get_ccid(p_agreement,
1036 g_liq_adv_trx_type_id);
1037
1038 IF g_retcode <> 0 THEN
1039 RETURN;
1040 END IF;
1041
1042 g_coll_dist_tbl(i).amount := p_amount;
1043 END IF;
1044
1045 IF p_trx_type = 'REC' THEN
1046 j := j+1;
1047
1048 g_rec_lines_tbl(j).trx_header_id := g_trx_rec_hdr_id;
1049
1050 SELECT ra_customer_trx_lines_s.nextval
1051 INTO g_rec_lines_tbl(j).trx_line_id
1052 FROM DUAL;
1053
1054
1055 g_rec_lines_tbl(j).line_number := j;
1056 --bug 8903169
1057 /*g_rec_lines_tbl(j).quantity_invoiced := p_amount;
1058 g_rec_lines_tbl(j).unit_selling_price := 1;*/
1059 g_rec_lines_tbl(j).quantity_invoiced := 1;
1060 g_rec_lines_tbl(j).unit_selling_price := p_amount;
1061 --End of bug 8903169
1062 g_rec_lines_tbl(j).description := 'Generate Receivable for Reimbursable Related Expense - Receivable Due';
1063 g_rec_lines_tbl(j).line_type := 'LINE';
1064 g_rec_lines_tbl(j).uom_code := 'EA';
1065
1066
1067 --Create distribution
1068 g_rec_dist_tbl(j).trx_line_id := g_rec_lines_tbl(j).trx_line_id;
1069 g_rec_dist_tbl(j).trx_header_id := g_rec_lines_tbl(j).trx_header_id;
1070
1071 SELECT ra_cust_trx_line_gl_dist_s.nextval
1072 INTO g_rec_dist_tbl(j).trx_dist_id
1073 FROM DUAL;
1074
1075 --We need to get the ccid for the Revenue line of the reimbursable Agreement
1076 --Replace the natural Account with the NAcc in the Transaction Type defined
1077
1078 g_rec_dist_tbl(j).account_class := 'REV';
1079 g_rec_dist_tbl(j).code_combination_id :=
1080 get_ccid(p_agreement,
1081 g_rec_due_trx_type_id);
1082
1083 IF g_retcode <> 0 THEN
1084 RETURN;
1085 END IF;
1086
1087 g_rec_dist_tbl(j).amount := p_amount;
1088 END IF;
1089
1090 END create_line_dist;
1091 -------------------------------------------------------------------------------
1092 PROCEDURE print_header IS
1093 l_head VARCHAR2(100);
1094
1095 BEGIN
1096
1097 fnd_file.put_line(fnd_file.output,'Date: '||g_invoice_date);
1098 fnd_file.put_line(fnd_file.output,' ');
1099 fnd_file.put_line(fnd_file.output,' ');
1100 l_head := ' Generate Receivables for Reimbursable Related Expenses Report';
1101 fnd_file.put_line(fnd_file.output,l_head);
1102 fnd_file.put_line(fnd_file.output,' ');
1103 fnd_file.put_line(fnd_file.output,' ');
1104 fnd_file.put_line(fnd_file.output,
1105 'Customer Transaction Transaction Reimbursable Amount Terms ');
1106 fnd_file.put_line(fnd_file.output,
1107 ' Number Type Agreement ');
1108 fnd_file.put_line(fnd_file.output,
1109 '--------- ------------ ------------ ------------- ------- ------ ');
1110
1111 fnd_file.put_line(fnd_file.output,' ');
1112 END;
1113 -------------------------------------------------------------------------------
1114 PROCEDURE print_line(p_customer_name IN VARCHAR2,
1115 p_trx_num IN VARCHAR2,
1116 p_trx_type IN VARCHAR2,
1117 p_reimb_agree_num IN VARCHAR2,
1118 p_amt IN NUMBER,
1119 p_terms IN VARCHAR2) IS
1120 BEGIN
1121
1122 fnd_file.put_line(fnd_file.output, rpad(p_customer_name,39)||' '||rpad(p_trx_num,20)||' '||rpad(p_trx_type,20)||' '||rpad(p_reimb_agree_num,20)||
1123 ' '||rpad(p_amt,10)||' '||rpad(p_terms,15));
1124
1125
1126 END;
1127 -------------------------------------------------------------------------------
1128 END fv_gen_artrx_reimb_proc;