[Home] [Help]
PACKAGE BODY: APPS.FV_IPAC
Source
1 PACKAGE BODY fv_ipac AS
2 --$Header: FVIPPROB.pls 120.68 2011/09/14 17:48:04 snama ship $
3 --IPAC FY2003-04
4 gbl_gl_segment_name VARCHAR2(30);
5 gbl_gl_acc_value_set_id NUMBER;
6 g_start_billing_id NUMBER;
7 g_end_billing_id NUMBER;
8 errcode NUMBER;
9 errmsg VARCHAR2(1000);
10 g_module_name VARCHAR2(100) ;
11
12 PROCEDURE create_flat_file(p_statement VARCHAR2,
13 p_set_of_books_id NUMBER,
14 p_customer_trx_id NUMBER);
15
16 FUNCTION get_sgl_exception(p_acct_num IN VARCHAR2,
17 p_sgl_acct_num OUT NOCOPY VARCHAR2) RETURN VARCHAR2 ;
18
19 PROCEDURE delete_records ;
20
21 -- ------------------------------------
22 -- Stored Input Parameters
23 -- ------------------------------------
24 parm_transaction_type ra_cust_trx_types.cust_trx_type_id%TYPE;
25 parm_profile_class_id NUMBER;
26 parm_customer_category hz_parties.category_code%TYPE;
27 parm_customer_id hz_parties.party_id%TYPE;
28 parm_trx_date_low DATE;
29 parm_trx_date_high DATE;
30 parm_currency ra_customer_trx.invoice_currency_code%TYPE;
31 parm_contact_name fv_ipac_trx_all.cnt_nm%TYPE;
32 parm_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
33 parm_org_id fv_operating_units.org_id%TYPE;
34 parm_contact_ph_no VARCHAR2(17);
35 -- ------------------------------------
36 -- Stored Global Variables
37 -- ------------------------------------
38 v_segment VARCHAR2(25);
39 v_set_of_books_id NUMBER(15);
40 v_ledger_name VARCHAR2(30);
41 v_closing_status VARCHAR2(1);
42 v_bal_seg_name VARCHAR2(25);
43 v_treasury_symbol fv_treasury_symbols.treasury_symbol%TYPE;
44 v_boolean BOOLEAN;
45 flex_num NUMBER;
46 flex_code VARCHAR2(60) ;
47 apps_id NUMBER ;
48 seg_number NUMBER;
49 bl_seg_name VARCHAR2(60);
50 seg_app_name VARCHAR2(60);
51 seg_prompt VARCHAR2(60);
52 seg_value_set_name VARCHAR2(60);
53 v_ccid NUMBER;
54 v_org_id fv_operating_units.org_id%TYPE;
55 v_sender_alc fv_operating_units.alc_code%TYPE;
56 --v_customer_alc ap_bank_accounts.agency_location_code%TYPE; changed
57 v_customer_alc IBY_EXT_BANK_ACCOUNTS.agency_location_code%TYPE;
58 v_receipt_method_id NUMBER;
59 v_original_amount NUMBER;
60 v_paid_amount NUMBER;
61 trx_exception_flag VARCHAR2(1) ;
62 v_trx_excpt_cat fv_ipac_trx_all.exception_category%TYPE;
63 v_trx_exception fv_ipac_trx_all.exception_category%TYPE;
64 v_bulk_exception fv_ipac_trx_all.bulk_exception%TYPE;
65 v_pay_flag VARCHAR2(1) ;
66 v_invoice_currency ra_customer_trx.invoice_currency_code%TYPE;
67 -- Variables to populate who columns
68 v_created_by NUMBER(15);
69 v_creation_date DATE;
70 v_last_updated_by NUMBER(15);
71 v_last_update_date DATE;
72 v_last_update_login NUMBER(15);
73 l_module_name VARCHAR2(200) ;
74 v_coa_id NUMBER(15);
75 p_status VARCHAR2(1);
76 -- ------------------------------------
77 -- Cursors
78 -- ------------------------------------
79 -- Cursor to select the transaction info
80 -- based on the input parameters
81 CURSOR trx_select
82 IS
83 SELECT rct.customer_trx_id,
84 hzca.cust_account_id customer_id,
85 --hzp.party_name customer_name,
86 rct.trx_number,
87 rct.trx_date,
88 rct.purchase_order,
89 rtt.TYPE,
90 arem.address_lines_phonetic,
91 ffc.eliminations_id,
92 rct.receipt_method_id,
93 rct.initial_customer_trx_id,
94 DECODE(hzp.PARTY_TYPE,'ORGANIZATION', hzp.DUNS_NUMBER_C,NULL) duns_number_c,
95 rsu.Cust_Acct_site_ID bill_to_address_id,
96 rct.invoice_currency_code
97 FROM hz_parties hzp,
98 hz_cust_accounts hzca,
99 ra_customer_trx rct,
100 ra_cust_trx_types rtt,
101 HZ_CUST_SITE_USES rsu ,
102 ar_remit_to_addresses_v arem,
103 fv_facts_customers_v ffc
104 WHERE hzp.party_id = hzca.party_id
105 AND rct.bill_to_customer_id = hzca.cust_account_id
106 AND rct.set_of_books_id = v_set_of_books_id
107 AND rct.complete_flag = 'Y'
108 AND rtt.cust_trx_type_id = rct.cust_trx_type_id
109 AND rsu.site_use_id = rct.bill_to_site_use_id
110 AND rct.remit_to_address_id = arem.address_id
111 AND ffc.customer_id = hzca.cust_account_id
112 AND UPPER(hzca.customer_class_code) = 'FEDERAL'
113 AND rtt.TYPE = 'INV'
114 AND rct.cust_trx_type_id IN
115 (SELECT cust_trx_type_id
116 FROM ra_cust_trx_types
117 WHERE cust_trx_type_id = DECODE(parm_transaction_type,NULL, cust_trx_type_id,parm_transaction_type)
118 )
119 AND rct.bill_to_customer_id IN
120 (SELECT DISTINCT cust_account_id
121 FROM hz_customer_profiles
122 WHERE profile_class_id = DECODE(parm_profile_class_id,NULL, profile_class_id, parm_profile_class_id)
123 )
124 AND hzca.cust_account_id IN
125 (
126 (SELECT hzca.cust_account_id
127 FROM hz_parties hp,
128 hz_cust_accounts hca
129 WHERE hp.party_id = hca.party_id
130 AND NVL(category_code,'XXX') LIKE DECODE(parm_customer_category,NULL, NVL(category_code,'XXX'), parm_customer_category)
131 )
132 INTERSECT
133 (SELECT cust_account_id
134 FROM hz_cust_accounts
135 WHERE cust_account_id LIKE DECODE(parm_customer_id,NULL, '%',parm_customer_id)
136 )
137 )
138 AND rct.trx_date BETWEEN DECODE(parm_trx_date_low,NULL, TO_DATE('1990/1/1', 'yyyy/mm/dd'), parm_trx_date_low) AND DECODE(parm_trx_date_high,NULL,TRUNC(SYSDATE), parm_trx_date_high)
139 AND rct.invoice_currency_code = DECODE(parm_currency, NULL, rct.invoice_currency_code, parm_currency);
140 -- Cursor to select transaction detail line info
141 -- based on the passed customer_trx_id
142 -- Including the Receivable accounts
143 -- to get the Receivable accounts outer joines are used
144
145 -- Bug 12889681. Replaced rgld.acct_amount with arp_amount_due_remaining,
146 -- receipts are created with adjusted amount for transactions with adjustments.
147 CURSOR det_select(p_customer_trx_id VARCHAR2)
148 IS
149 SELECT rctl.line_number,
150 arp.amount_due_remaining,
151 rctl.quantity_invoiced,
152 rctl.description,
153 rgld.code_combination_id,
154 rctl.uom_code,
155 rctl.unit_selling_price,
156 fu.user_name,
157 rgld.percent,
158 rgld.account_class,
159 rctl.customer_trx_line_id
160 FROM ra_customer_trx_lines rctl,
161 ra_cust_trx_line_gl_dist rgld,
162 fnd_user fu,
163 ar_payment_schedules arp
164 WHERE rgld.customer_trx_id = p_customer_trx_id
165 AND rgld.customer_trx_id = rctl.customer_trx_id(+)
166 AND rctl.customer_trx_line_id(+) = rgld.customer_trx_line_id
167 AND rctl.created_by = fu.user_id(+)
168 AND rgld.set_of_books_id = v_set_of_books_id
169 AND arp.org_id = v_org_id
170 AND arp.customer_trx_id = rgld.customer_trx_id
171 AND NOT EXISTS
172 (SELECT 'X'
173 FROM fv_ipac_trx_all
174 WHERE set_of_books_id = v_set_of_books_id
175 AND org_id = v_org_id
176 AND customer_trx_id = p_customer_trx_id
177 AND trx_line_no = rctl.line_number
178 )
179 ;
180 -- Cursor to select individual transactions which have not been yet processed
181 -- for creating and applying receipts
182 -- Bug 12609586. Added fit.trn_set_id to the select clause
183 CURSOR trx_receipt_cur
184 IS
185 SELECT SUM(amount) amount,
186 fit.customer_trx_id,
187 fit.trx_number,
188 fit.trx_date,
189 fit.customer_id,
190 fit.cash_receipt_id ,
191 fit.accounted_flag,
192 fit.cnt_nm,
193 fit.trn_set_id
194 FROM fv_ipac_trx_all fit
195 WHERE fit.exclude_flag = 'N'
196 AND set_of_books_id = v_set_of_books_id
197 AND org_id = v_org_id
198 AND fit.report_flag = 'Y'
199 AND fit.processed_flag = 'N'
200 AND fit.account_class <>'REC'
201 AND fit.unt_iss <> '~RA'
202 AND ( fit.cash_receipt_id IS NULL
203 OR ( fit.cash_receipt_id IS NOT NULL
204 AND NVL(fit.accounted_flag, 'N') <> 'Y' ))
205 GROUP BY fit.customer_trx_id ,
206 fit.trx_number,
207 fit.trx_date,
208 fit.customer_id,
209 fit.cash_receipt_id,
210 fit.accounted_flag,
211 fit.cnt_nm,
212 fit.trn_set_id;
213 -- Cursor to get all the SLA accounts generated for a particular distribution
214 -- of a transaction after SLA accounting generation
215 CURSOR xla_acnt_cur (p_ae_header_id NUMBER)
216 IS
217 SELECT ae_header_id,
218 ae_line_num,
219 accounted_cr,
220 accounted_dr,
221 accounting_class_code,
222 code_combination_id
223 FROM xla_ae_lines
224 WHERE ae_header_id = p_ae_header_id;
225 -- Cursor to get all the Receipt accounting information stored after receipt
226 -- creation and SLA accounting generation
227 -- grouped on the basis of customer_trx_id and treasury symbol
228 -- for the purpose of bulk exception checking
229 CURSOR trx_receipt_acct_cur
230 IS
231 SELECT customer_trx_id,
232 snd_app_sym,
233 sgl_acct_num
234 FROM fv_ipac_trx_all
235 WHERE unt_iss = '~RA'
236 AND set_of_books_id = v_set_of_books_id
237 AND org_id = v_org_id
238 AND report_flag = 'Y'
239 AND exclude_flag = 'N'
240 AND processed_flag = 'N'
241 AND accounted_flag = 'Y'
242 GROUP BY customer_trx_id,
243 snd_app_sym;
244 -- Cursor to get all the distinct customer_trx_ids
245 -- whose receipt is already created and SLA accounting generated successfully
246 CURSOR hdr_det(p_set_of_books_id NUMBER, p_org_id NUMBER)
247 IS
248 SELECT DISTINCT customer_trx_id
249 FROM fv_ipac_trx_all trx
250 WHERE set_of_books_id = p_set_of_books_id
251 AND org_id = p_org_id
252 AND processed_flag = 'N'
253 AND exclude_flag = 'N'
254 AND report_flag = 'Y'
255 AND cash_receipt_id IS NOT NULL
256 AND accounted_flag = 'Y'
257 AND account_class <> 'REC'
258 AND unt_iss <> '~RA'
259 ORDER BY customer_trx_id;
260 -- Cursor to get the customer transaction details record
261 -- for bulk file creatation
262 -- grouped by trx_line_no ,and account symbol
263 CURSOR trx_detail_cur(p_cust_trx_id NUMBER)
264 IS
265 SELECT SUM(fit.amount) amount,
266 fit.cnt_nm,
267 fit.cnt_phn_nr,
268 fit.contract_no,
269 fit.dpr_cd,
270 fit.dsc,
271 fit.trx_number,
272 fit.trx_date,
273 fit.trn_set_id,
274 fit.obl_dcm_nr,
275 fit.pay_flg,
276 fit.po_number,
277 SUM(fit.qty) qty,
278 fit.cust_duns_num,
279 fit.snd_app_sym,
280 fit.unt_iss,
281 fit.unt_prc,
282 fit.customer_trx_id,
283 fit.customer_id,
284 fit.taxpayer_number,
285 fit.trx_line_no trx_line_no,
286 fit.cash_receipt_id,
287 fit.sender_do_sym,
288 fit.sender_alc,
289 rct.comments comments
290 FROM fv_ipac_trx_all fit,
291 ra_customer_trx rct
292 WHERE fit.org_id = v_org_id
293 AND fit.set_of_books_id = v_set_of_books_id
294 AND fit.customer_trx_id = p_cust_trx_id
295 AND fit.customer_trx_id = rct.customer_trx_id
296 AND fit.processed_flag = 'N'
297 AND fit.exclude_flag = 'N'
298 AND fit.report_flag = 'Y'
299 AND fit.account_class <>'REC'
300 AND fit.unt_iss <> '~RA'
301 GROUP BY fit.customer_trx_id,
302 fit.customer_id,
303 fit.taxpayer_number,
304 fit.trx_line_no,
305 fit.cash_receipt_id,
306 fit.snd_app_sym,
307 fit.cnt_nm,
308 fit.cnt_phn_nr,
309 fit.contract_no,
310 fit.dpr_cd,
311 fit.dsc,
312 fit.trx_number,
313 fit.trx_date,
314 fit.trn_set_id,
315 fit.obl_dcm_nr,
316 fit.pay_flg,
317 fit.po_number,
318 fit.cust_duns_num,
319 fit.unt_iss,
320 fit.unt_prc,
321 fit.sender_do_sym,
322 fit.sender_alc,
323 rct.comments
324 ORDER BY fit.customer_trx_id,
325 fit.trx_line_no;
326
327 -- -----------------------------------------------------------------------------
328 -- -----------------------------------------------------------------------------
329
330 -- Procedure to initialize variables being used in the package
331 PROCEDURE init_vars
332 IS
333 l_module_name VARCHAR2(200) ;
334 errbuf VARCHAR2(200);
335 BEGIN
336 l_module_name := g_module_name || 'init_vars ';
337 v_treasury_symbol := NULL;
338 v_original_amount := 0;
339 v_paid_amount := 0;
340 trx_exception_flag := 'N';
341 v_trx_excpt_cat := NULL;
342 v_trx_exception := NULL;
343 EXCEPTION
344 WHEN OTHERS THEN
345 errbuf := SQLERRM;
346 errbuf := 'When others ' || SQLERRM;
347 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
348 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,
349 l_module_name, errbuf);
350 END IF;
351 RAISE;
352 END ; --init_vars
353
354 -- -----------------------------------------------------------------------------
355 -- -----------------------------------------------------------------------------
356 -- Procedure to delete exception records
357 -- from fv_ipac_trx_all
358 PROCEDURE del_exception_recs
359 IS
360 l_module_name VARCHAR2(200) ;
361 BEGIN
362 l_module_name := g_module_name || 'del_exception_recs';
363 DELETE
364 FROM fv_ipac_trx_all trx
365 WHERE set_of_books_id = v_set_of_books_id
366 AND NVL(org_id,-99) = NVL(v_org_id,-99)
367 AND (report_flag = 'N'
368 OR ( report_flag = 'Y'
369 AND bulk_exception IS NOT NULL));
370 COMMIT;
371 EXCEPTION
372 WHEN OTHERS THEN
373 errcode := SQLCODE;
374 errmsg := SQLERRM || ' -- Error IN deleleting the records' || ' form IPAC TABLE PROCEDURE ' || ':- del_exception_recs' ;
375 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
376 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
377 END IF;
378 RAISE;
379 END; -- del_exception_recs
380
381 -- -----------------------------------------------------------------------------
382 -- -----------------------------------------------------------------------------
383
384 PROCEDURE delete_records
385 IS
386 l_module_name VARCHAR2(200) ;
387 BEGIN
388 l_module_name := g_module_name || 'delete_records';
389 DELETE
390 FROM fv_ipac_trx_all
391 WHERE set_of_books_id = v_set_of_books_id
392 AND NVL(org_id,-99) = NVL(v_org_id,-99)
393 AND ipac_billing_id BETWEEN g_start_billing_id AND g_end_billing_id ;
394 EXCEPTION
395
396 WHEN NO_DATA_FOUND THEN
397 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
398 'No Records For Deletion ' );
399
400 WHEN OTHERS THEN
401 errcode := SQLCODE;
402 errmsg := SQLERRM || ' -- Error IN deleleting the records' || ' form IPAC TABLE PROCEDURE ' || ':- delete_records' ;
403 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
404 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
405 END IF;
406 RAISE;
407 END delete_records;
408
409 -- -----------------------------------------------------------------------------
410 -- -----------------------------------------------------------------------------
411
412 -- Procedure to get the balancing segment, GL Account segment name
413 PROCEDURE get_bal_seg_name
414 IS
415 l_module_name VARCHAR2(200) ;
416 BEGIN
417 l_module_name := g_module_name || 'get_bal_seg_name ';
418 SELECT chart_of_accounts_id
419 INTO flex_num
420 FROM gl_sets_of_books
421 WHERE set_of_books_id = v_set_of_books_id;
422 v_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(apps_id,flex_code, flex_num, 'GL_BALANCING', seg_number);
423 IF (v_boolean) THEN
424 v_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO(apps_id, flex_code,flex_num, seg_number, bl_seg_name, seg_app_name, seg_prompt, seg_value_set_name);
425 END IF;
426 ------------------------------------------------------------------
427 -- IPAC FY2003-04
428 ------------------------------------------------------------------
429 -- Get the GL Account segment and the Value set ID attached
430 v_boolean:= FND_FLEX_APIS.GET_SEGMENT_COLUMN( apps_id, flex_code,flex_num, 'GL_ACCOUNT', gbl_gl_segment_name);
431 BEGIN
432 SELECT flex_value_set_id
433 INTO gbl_gl_acc_value_set_id
434 FROM fnd_id_flex_segments
435 WHERE application_column_name = gbl_gl_segment_name
436 AND id_flex_code = FLEX_CODE
437 AND id_flex_num = FLEX_NUM;
438 EXCEPTION
439 WHEN OTHERS THEN
440 errcode := SQLCODE;
441 errmsg := SQLERRM ||' -- Error in geting the Value set ID ' || 'attached to the GL Account segment :' || 'Procedure :- get_bal_seg_name' ;
442 RAISE;
443 END;
444 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Chart of Accounts Id,Bal Segment,Acct Segment,Flex Value Set Id:'|| flex_num||','||bl_seg_name||','||gbl_gl_segment_name||','|| gbl_gl_acc_value_set_id);
445 EXCEPTION
446 WHEN OTHERS THEN
447 IF errcode IS NULL THEN
448 errcode := SQLCODE;
449 errmsg := SQLERRM ||' -- Error IN geting the Segment Name :' || ' Procedure :- get_bal_seg_name' ;
450 END IF;
451 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
452 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
453 END IF;
454 RAISE;
455 END; -- get_bal_seg_name
456
457 -- -----------------------------------------------------------------------------
458 -- -----------------------------------------------------------------------------
459
460 -- Procedure to get the treasury symbol
461 -- based on the passed balancing segment name
462 -- and account ccid
463 PROCEDURE get_treasury_symbol(lv_bal_seg_name IN VARCHAR2, v_ccid IN NUMBER)
464 IS
465 l_module_name VARCHAR2(200);
466 BEGIN
467 l_module_name := g_module_name || 'get_treasury_symbol';
468 SELECT fts.treasury_symbol
469 INTO v_treasury_symbol
470 FROM fv_fund_parameters ffp,
471 fv_treasury_symbols fts,
472 gl_code_combinations glc
473 WHERE DECODE(lv_bal_seg_name, 'SEGMENT1', glc.segment1, 'SEGMENT2', glc.segment2, 'SEGMENT3', glc.segment3, 'SEGMENT4',
474 glc.segment4, 'SEGMENT5', glc.segment5, 'SEGMENT6', glc.segment6, 'SEGMENT7', glc.segment7, 'SEGMENT8', glc.segment8, 'SEGMENT9',
475 glc.segment9, 'SEGMENT10',glc.segment10, 'SEGMENT11',glc.segment11,'SEGMENT12',glc.segment12, 'SEGMENT13',glc.segment13,'SEGMENT14',
476 glc.segment14, 'SEGMENT15',glc.segment15,'SEGMENT16',glc.segment16, 'SEGMENT17',glc.segment17,'SEGMENT18',glc.segment18, 'SEGMENT19',
477 glc.segment19,'SEGMENT20',glc.segment20, 'SEGMENT21',glc.segment21,'SEGMENT22',glc.segment22, 'SEGMENT23',glc.segment23,'SEGMENT24',
478 glc.segment24, 'SEGMENT25',glc.segment25,'SEGMENT26',glc.segment26, 'SEGMENT27',glc.segment27,'SEGMENT28',glc.segment28, 'SEGMENT29',
479 glc.segment29, 'SEGMENT30',glc.segment30) = ffp.fund_value
480 AND glc.code_combination_id = v_ccid
481 AND ffp.treasury_symbol_id = fts.treasury_symbol_id
482 AND ffp.set_of_books_id = v_set_of_books_id;
483 EXCEPTION
484 WHEN NO_DATA_FOUND THEN
485 v_treasury_symbol := '1';
486 WHEN OTHERS THEN
487 errmsg := SQLERRM;
488 errcode := SQLCODE;
489 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
490 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
491 END IF;
492 RAISE;
493 END; -- get_treasury_symbol
494
495 -- -----------------------------------------------------------------------------
496 -- -----------------------------------------------------------------------------
497 -- Function to get the transaction exception category
498 -- based on the passed transaction record values
499 FUNCTION get_trx_exception( transaction_rec IN OUT NOCOPY fv_ipac_trx_all%ROWTYPE, p_cust_trx_line_id NUMBER, p_bill_to_address_id NUMBER) RETURN VARCHAR2
500 IS
501 l_sgl_acct_num gl_code_combinations.segment1%TYPE;
502 BEGIN
503 v_trx_excpt_cat := NULL;
504 -- Get the exception only if the account class is not REC
505 IF transaction_rec.account_class <> 'REC' THEN -- REC check
506 IF (v_receipt_method_id IS NULL ) THEN --RM check
507 v_trx_excpt_cat := 'MISSING_PAYMENT_METHOD';
508 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
509 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'MISSING_PAYMENT_METHOD');
510 END IF;
511 --
512 /*
513 ELSIF v_receipt_method_id IS NOT NULL THEN --RM check
514 BEGIN
515
516 SELECT aba.agency_location_code
517 INTO v_sender_alc
518 FROM ar_receipt_method_accounts_all arma,
519 ap_bank_accounts aba
520 WHERE aba.bank_account_id = arma.bank_account_id
521 AND aba.currency_code = nvl(parm_currency,v_invoice_currency)
522 AND arma.primary_flag = 'Y'
523 AND arma.receipt_method_id = v_receipt_method_id;
524
525 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
526 THEN
527 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
528 l_module_name,'receipt_method_id IS NOT null,
529 v_sender_alc ='|| v_sender_alc);
530 END IF;
531
532 IF v_sender_alc IS NULL THEN
533 v_trx_excpt_cat := 'MISSING_BANK_ACCT_ALC';
534 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
535 THEN
536 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
537 l_module_name,'MISSING_BANK_ACCT_ALC');
538 END IF;
539 END IF;
540 EXCEPTION
541 WHEN NO_DATA_FOUND THEN
542 IF v_default_alc <> 'N' THEN
543 v_sender_alc := v_default_alc;
544
545 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
546 THEN
547 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
548 l_module_name,'no data and DEFAULT alc v_sender_alc ='
549 || v_sender_alc);
550 END IF;
551 ELSE
552 v_trx_excpt_cat := 'MISSING_BANK_ACCT_ALC';
553 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
554 THEN
555 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
556 l_module_name,'MISSING_BANK_ACCT_ALC');
557 END IF;
558 END IF;
559 WHEN OTHERS THEN
560 errmsg := SQLERRM||'-- Error in Get_Trx_Exception when
561 getting the ALC,when receipt method is not null';
562 IF ( FND_LOG.LEVEL_UNEXPECTED >=
563 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
564 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,
565 l_module_name,errmsg);
566 END IF;
567 RAISE;
568 END;
569 ELSIF (v_receipt_method_id IS NULL AND v_default_alc <> 'N')
570 THEN --RM check
571 v_sender_alc := v_default_alc;
572 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
573 THEN
574 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
575 'Receipt Method is null and sender ALC ='|| v_sender_alc);
576 END IF;
577 */
578
579 --
580 END IF; -- end if for alc check --RM check
581 IF v_trx_excpt_cat IS NULL THEN
582 -- no exceptions found above so continue checking for exception
583 --find customer alc on the bill to customer account.
584 -- using the BILL_TO site to determine bank account assigned.
585 --Bug9025655. Since there could be multiple bank accounts
586 --without end dates, this select will bring in multiple rows.
587 --Restricting to fetch one row.
588 BEGIN
589 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
590 'Customer ID = ' || transaction_rec.customer_id ||
591 ' p_bill_to_address_id = ' || p_bill_to_address_id);
592 SELECT eb.agency_location_code
593 INTO v_customer_alc
594 FROM hz_cust_acct_sites_all hzcas,
595 hz_cust_site_uses_all hzcsu,
596 iby_external_payers_all payer,
597 iby_pmt_instr_uses_all iby_ins,
598 iby_ext_bank_accounts_v eb
599 WHERE hzcas.cust_account_id = transaction_rec.customer_id
600 AND hzcas.cust_acct_site_id = p_bill_to_address_id
601 AND hzcsu.cust_acct_site_id =hzcas.cust_acct_site_id
602 AND hzcsu.site_use_code = 'BILL_TO'
603 AND hzcsu.site_use_id = payer.acct_site_use_id
604 AND payer.ext_payer_id = iby_ins.ext_pmt_party_id
605 AND iby_ins.instrument_type = 'BANKACCOUNT'
606 AND transaction_rec.trx_date BETWEEN
607 iby_ins.start_date AND
608 NVL(iby_ins.end_date, TO_DATE('12/31/9999', 'MM/DD/YYYY'))
609 AND iby_ins.instrument_id = eb.ext_bank_account_id
610 AND rownum = 1;
611 EXCEPTION
612 WHEN NO_DATA_FOUND THEN
613 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
614 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'MISSING_CUST_ALC-No Primary Bank AcctDefined');
615 END IF;
616 v_trx_excpt_cat := 'MISSING_CUST_ALC';
617 END;
618 IF v_customer_alc IS NULL THEN
619 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
620 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'MISSING_CUST_ALC - No ALC defined');
621 END IF;
622 v_trx_excpt_cat := 'MISSING_CUST_ALC';
623 END IF;
624 END IF; -- end customer alc check
625 IF v_trx_excpt_cat IS NULL THEN
626 -- no exceptions found above so continue checking for exception
627 IF transaction_rec.cnt_nm IS NULL THEN
628 v_trx_excpt_cat := 'MISSING_ALC_CONTACT';
629 ELSIF transaction_rec.sender_do_sym IS NULL THEN
630 v_trx_excpt_cat := 'MISSING_DO_SYM';
631 ELSIF transaction_rec.trx_number IS NULL THEN
632 v_trx_excpt_cat := 'MISSING_INV_NUM';
633 ELSIF transaction_rec.po_number IS NULL THEN
634 v_trx_excpt_cat := 'MISSING_PO_NUM';
635 ELSIF transaction_rec.obl_dcm_nr IS NULL THEN
636 v_trx_excpt_cat := 'MISSING_OBL_DOC_NUM';
637 ELSIF transaction_rec.qty IS NULL THEN
638 v_trx_excpt_cat := 'MISSING_QUANTITY';
639 ELSIF transaction_rec.unt_prc IS NULL THEN
640 v_trx_excpt_cat := 'MISSING_UNIT_PRICE';
641 ELSIF transaction_rec.unt_iss IS NULL THEN
642 v_trx_excpt_cat := 'MISSING_UNIT_OF_ISSUE';
643 ELSIF transaction_rec.amount IS NULL THEN
644 v_trx_excpt_cat := 'MISSING_AMOUNT';
645 ELSIF transaction_rec.pay_flg IS NULL THEN
646 v_trx_excpt_cat := 'MISSING_PAY_FLAG';
647 ELSIF transaction_rec.dpr_cd IS NULL THEN
648 v_trx_excpt_cat := 'MISSING_RCVR_DEPT_CODE';
649 ELSIF (transaction_rec.snd_app_sym IS NULL) OR (transaction_rec.snd_app_sym = '1') THEN
650 v_trx_excpt_cat := 'MISSING_SNDR_APP_SYM';
651 ELSE
652 NULL;
653 END IF;
654 END IF;
655 END IF;-- End if account_class <> 'REC'
656 RETURN v_trx_excpt_cat;
657 EXCEPTION
658 WHEN OTHERS THEN
659 errmsg :=SQLERRM;
660 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
661 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
662 END IF;
663 RAISE;
664 END ; -- get_trx_exception
665
666 -- -----------------------------------------------------------------------------
667 -- -----------------------------------------------------------------------------
668
669 -- Procedure to insert transaction records
670 PROCEDURE insert_trx_rec(ins_trx IN fv_ipac_trx_all%ROWTYPE)
671 IS
672 v_trx_billing_id NUMBER;
673 v_trx_excl_flag VARCHAR2(1) ;
674 v_trx_proc_flag VARCHAR2(1) ;
675 l_module_name VARCHAR2(200) ;
676 BEGIN
677 v_trx_excl_flag := 'N';
678 v_trx_proc_flag := 'N';
679 l_module_name := g_module_name || 'insert_trx_rec';
680 SELECT fv_ipac_billing_id_s.NEXTVAL INTO v_trx_billing_id FROM dual;
681 INSERT
682 INTO fv_ipac_trx_all
683 (
684 set_of_books_id,
685 org_id,
686 run_date,
687 ipac_billing_id,
688 taxpayer_number,
689 sender_do_sym,
690 trn_set_id,
691 amount,
692 cnt_nm,
693 cnt_phn_nr,
694 dpr_cd,
695 dsc,
696 trx_number,
697 trx_date,
698 obl_dcm_nr,
699 pay_flg,
700 po_number,
701 qty,
702 snd_app_sym,
703 unt_iss,
704 unt_prc,
705 exception_category,
706 customer_trx_id,
707 customer_id,
708 report_flag,
709 trx_line_no,
710 exclude_flag,
711 processed_flag,
712 created_by,
713 creation_date,
714 last_updated_by,
715 last_update_date,
716 last_update_login,
717 contract_no,
718 cust_duns_num,
719 sgl_acct_num,
720 cr_dr_flag,
721 account_class
722 )
723 VALUES
724 (
725 v_set_of_books_id,
726 v_org_id,
727 TRUNC(SYSDATE),
728 v_trx_billing_id,
729 ins_trx.taxpayer_number,
730 ins_trx.sender_do_sym,
731 ins_trx.trn_set_id,
732 ins_trx.amount,
733 NVL(ins_trx.cnt_nm,-99),
734 parm_contact_ph_no,
735 ins_trx.dpr_cd,
736 ins_trx.dsc,
737 ins_trx.trx_number,
738 ins_trx.trx_date,
739 ins_trx.obl_dcm_nr,
740 ins_trx.pay_flg,
741 ins_trx.po_number,
742 ins_trx.qty,
743 ins_trx.snd_app_sym,
744 ins_trx.unt_iss,
745 ins_trx.unt_prc,
746 ins_trx.exception_category,
747 ins_trx.customer_trx_id,
748 ins_trx.customer_id,
749 ins_trx.report_flag,
750 ins_trx.trx_line_no,
751 v_trx_excl_flag,
752 v_trx_proc_flag,
753 v_created_by,
754 v_creation_date,
755 v_last_updated_by,
756 v_last_update_date,
757 v_last_update_login,
758 ins_trx.contract_no,
759 ins_trx.cust_duns_num,
760 ins_trx.sgl_acct_num,
761 ins_trx.cr_dr_flag,
762 ins_trx.account_class
763 )
764 ;
765 EXCEPTION
766 WHEN OTHERS THEN
767 errcode := SQLCODE;
768 errmsg := SQLERRM || ' -- Error in inserting the data into' ||
769 ' FV_IPAC_TRX_ALL table : Procedure :- insert_trx_rec';
770 RAISE;
771 END; -- insert_trx_rec
772
773 -- -----------------------------------------------------------------------------
774 -- -----------------------------------------------------------------------------
775
776 -- IPAC FY2003-04
777 -- Procedure to get the GL Account Value
778 -- Parameter : Code combination Id
779 -- Return Value : GL Account Number
780 FUNCTION gl_account_num(p_ccid NUMBER) RETURN VARCHAR2
781 IS
782 l_gl_account_num gl_code_combinations.segment1%TYPE;
783 l_module_name VARCHAR2(200);
784 BEGIN
785 l_module_name:=g_module_name||'gl_account_num';
786
787 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
788 'gl_account_num begins');
789
790
791 SELECT DECODE (gbl_gl_segment_name,
792 'SEGMENT1',glc.segment1,
793 'SEGMENT2', glc.segment2, 'SEGMENT3',
794 glc.segment3, 'SEGMENT4',
795 glc.segment4, 'SEGMENT5',
796 glc.segment5, 'SEGMENT6',
797 glc.segment6, 'SEGMENT7',
798 glc.segment7, 'SEGMENT8',
799 glc.segment8, 'SEGMENT9',
800 glc.segment9, 'SEGMENT10',
801 glc.segment10, 'SEGMENT11',
802 glc.segment11,'SEGMENT12',
803 glc.segment12, 'SEGMENT13',
804 glc.segment13,'SEGMENT14',
805 glc.segment14, 'SEGMENT15',
806 glc.segment15,'SEGMENT16',
807 glc.segment16, 'SEGMENT17',
808 glc.segment17,'SEGMENT18',
809 glc.segment18, 'SEGMENT19',
810 glc.segment19,'SEGMENT20',
811 glc.segment20, 'SEGMENT21',
812 glc.segment21,'SEGMENT22',
813 glc.segment22, 'SEGMENT23',
814 glc.segment23,'SEGMENT24',
815 glc.segment24, 'SEGMENT25',
816 glc.segment25,'SEGMENT26',
817 glc.segment26, 'SEGMENT27',
818 glc.segment27,'SEGMENT28',
819 glc.segment28, 'SEGMENT29',
820 glc.segment29,'SEGMENT30',
821 glc.segment30)
822 INTO l_gl_account_num
823 FROM gl_code_combinations glc
824 WHERE code_combination_id = p_ccid
825 AND glc.chart_of_accounts_id = flex_num;
826
827 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
828 'l_gl_account_num = '||l_gl_account_num);
829
830 RETURN l_gl_account_num;
831 EXCEPTION
832 WHEN OTHERS THEN
833 errcode := SQLCODE;
834 errmsg := SQLERRM
835 || ' -- Error in getting the GL Account number :'
836 || ' Funcation :- gl_account_num';
837
838 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
839 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,
840 l_module_name, errmsg);
841 END IF;
842 RAISE;
843 END gl_account_num;
844
845 -- -----------------------------------------------------------------------------
846 -- -----------------------------------------------------------------------------
847
848 --Function to get the GL Account Type of the given CCID
849 FUNCTION gl_account_type(p_gl_account VARCHAR2) RETURN VARCHAR2
850 IS
851 l_account_type VARCHAR2(1);
852 l_module_name VARCHAR2(200);
853 BEGIN
854 l_module_name:=g_module_name||'gl_account_type';
855
856 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
857 'gl_account_type begins');
858
859 -- Account Type of the Code combination ID
860 SELECT SUBSTR(compiled_value_attributes, 5, 1)
861 INTO l_account_type
862 FROM fnd_flex_values
863 WHERE flex_value = p_gl_account
864 AND flex_value_set_id = gbl_gl_acc_value_set_id;
865
866 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
867 'l_account_type='||l_account_type);
868
869 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
870 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
871 'Account: '||p_gl_account || ' Account type: '|| l_account_type );
872 END IF;
873 RETURN l_account_type;
874 EXCEPTION
875 WHEN OTHERS THEN
876 errcode :=SQLCODE;
877 errmsg := SQLERRM
878 || ' -- Error in getting the GL Account type :'
879 || ' Funcation :- gl_account_type';
880 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
881 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
882 errmsg);
883 END IF;
884 RAISE;
885 END gl_account_type;
886
887 -- -----------------------------------------------------------------------------
888 -- -----------------------------------------------------------------------------
889
890 -- Function to get the USSGL Account of GL Account
891 -- The function will return the USSGL account is GL Account is valid
892 -- The Function will set the p_valid_falg to 'Y' is the passed SGL Account is
893 -- USSGL Account else to 'N''
894 FUNCTION get_ussgl_account(p_gl_account_num IN VARCHAR2, p_valid_flag OUT NOCOPY VARCHAR2) RETURN VARCHAR2
895 IS
896 l_parent_gl_account_num gl_code_combinations.segment1%TYPE;
897 l_enabled_flag VARCHAR2(1);
898 BEGIN
899 --Check for the Validity of the SGL Account
900 SELECT ussgl_enabled_flag
901 INTO l_enabled_flag
902 FROM fv_facts_ussgl_accounts
903 WHERE ussgl_account = p_gl_account_num;
904 IF l_enabled_flag <> 'Y' THEN
905 -- SGL Account is not Enabled
906 p_valid_flag :='N' ;
907 RETURN p_gl_account_num;
908 ELSE
909 -- SGL Account is Enabled
910 p_valid_flag :='Y' ;
911 RETURN p_gl_account_num;
912 END IF;
913 EXCEPTION
914 WHEN NO_DATA_FOUND THEN
915 -- Check for the Parent
916 -- Check for the parent SGL Account
917 BEGIN
918 SELECT parent_flex_value
919 INTO l_parent_gl_account_num
920 FROM fnd_flex_value_hierarchies
921 WHERE (p_gl_account_num BETWEEN child_flex_value_low AND child_flex_value_high)
922 AND flex_value_set_id = gbl_gl_acc_value_set_id
923 AND parent_flex_value <> 'T'
924 AND parent_flex_value IN
925 (SELECT ussgl_account
926 FROM fv_facts_ussgl_accounts
927 WHERE ussgl_account = parent_flex_value
928 AND ussgl_enabled_flag ='Y'
929 )
930 ;
931 -- Parent is a valid Account
932 RETURN l_parent_gl_account_num;
933 EXCEPTION
934 WHEN NO_DATA_FOUND THEN
935 -- No parent USSGL account is defined
936 p_valid_flag := 'N';
937 RETURN p_gl_account_num;
938 WHEN TOO_MANY_ROWS THEN
939 -- More than one parent exist
940 p_valid_flag := 'N';
941 RETURN p_gl_account_num;
942 END ;
943 WHEN OTHERS THEN
944 errcode := SQLCODE;
945 errmsg := SQLERRM || ' -- Error in geting the USSGL Account :' || ' Function :- get_ussgl_account';
946 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
947 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
948 END IF;
949 RAISE;
950 END get_ussgl_account;
951
952 -- -----------------------------------------------------------------------------
953 -- -----------------------------------------------------------------------------
954
955 --Function to get the SGL Exception
956 FUNCTION get_sgl_exception(p_acct_num VARCHAR2, p_sgl_acct_num OUT NOCOPY VARCHAR2) RETURN VARCHAR2
957 IS
958 l_sgl_acct_num gl_code_combinations.segment1%TYPE;
959 l_valid_flag VARCHAR2(1);
960 BEGIN
961 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
962 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'IPAC FY2003-04 GET SGL EXCEPTION');
963 END IF;
964 -- Checking for the Account type
965 -- If the account type is budgetary Cr/Dr and account class
966 -- is equal to the REC then return the exception else return the
967 -- type in exception category
968 IF gl_account_type(p_acct_num) IN('C','D') THEN
969 RETURN 'BUDGETARY';
970 END IF ;
971 -- Get the USSGL Number and the Valid Flag
972 p_sgl_acct_num := get_ussgl_account(p_acct_num,l_valid_flag);
973 IF l_valid_flag = 'N' THEN
974 -- Invalid USSGL Account
975 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
976 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Exception SGL Account - Invalid SGL Account: ' || l_sgl_acct_num);
977 END IF;
978 RETURN 'INVALID_SGL_ACCOUNT';
979 END IF;
980 -- No SGL Exception
981 RETURN NULL;
982 EXCEPTION
983 WHEN OTHERS THEN
984 errcode :=SQLCODE;
985 errmsg :=SQLERRM|| ' -- Error in getting the USSGL Exception Function ' || ' - get_sgl_exception';
986 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
987 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
988 END IF;
989 RAISE;
990 END get_sgl_exception;
991 /*
992 -- Procedure to create REC line equal to a detail lines for each transaction
993 PROCEDURE equalize_rec_lines IS
994 -- cursor to get the customer_trx_id and and treasury Symbol
995 CURSOR trx_cur IS
996 SELECT customer_trx_id ,
997 snd_app_sym
998 FROM fv_ipac_trx_all
999 WHERE processed_flag ='N'
1000 AND set_of_books_id = v_set_of_books_id
1001 AND org_id = v_org_id
1002 AND exclude_flag ='N'
1003 AND unt_iss <> '~RA'
1004 AND ipac_billing_id BETWEEN
1005 g_start_billing_id AND
1006 g_end_billing_id
1007 GROUP BY customer_trx_id,
1008 snd_app_sym;
1009 -- cursor to get all the revenue lines and their respective amounts
1010 -- for a particular transaction excluding RA lines
1011 CURSOR trx_rec_cur (p_customer_trx_id NUMBER,
1012 p_snd_app_sym VARCHAR2 )
1013 IS SELECT trx_line_no,
1014 SUM(amount) amount
1015 FROM fv_ipac_trx_all
1016 WHERE org_id = v_org_id
1017 AND set_of_books_id = v_set_of_books_id
1018 AND processed_flag ='N'
1019 AND customer_trx_id = p_customer_trx_id
1020 AND snd_app_sym = p_snd_app_sym
1021 AND account_class <> 'REC'
1022 AND unt_iss <> '~RA'
1023 AND set_of_books_id = v_set_of_books_id
1024 AND exclude_flag ='N'
1025 GROUP BY trx_line_no ;
1026 l_trx_rec FV_IPAC_TRX_ALL%ROWTYPE;
1027 l_module_name VARCHAR2(200) ;
1028 BEGIN
1029 l_module_name := g_module_name ||'equalize_rec_lines';
1030 FOR trx_rec IN trx_cur
1031 LOOP
1032 FOR trx_rec_rec IN trx_rec_cur(trx_rec.customer_trx_id ,
1033 trx_rec.snd_app_sym )
1034 LOOP
1035 SELECT * INTO l_trx_rec
1036 FROM fv_ipac_trx_all
1037 WHERE org_id = v_org_id
1038 AND set_of_books_id = v_set_of_books_id
1039 AND customer_trx_id = trx_rec.customer_trx_id
1040 AND account_class = 'REC';
1041 l_trx_rec.snd_app_sym := trx_rec.snd_app_sym ;
1042 l_trx_rec.amount := trx_rec_rec.amount ;
1043 l_trx_rec.unt_iss := '~R~';
1044 l_trx_rec.trx_line_no := trx_rec_rec.trx_line_no ;
1045 insert_trx_rec(l_trx_rec);
1046 END LOOP;
1047 END LOOP;
1048 END equalize_rec_lines;
1049 */
1050
1051 -- -----------------------------------------------------------------------------
1052 -- -----------------------------------------------------------------------------
1053
1054 -- Procedure to find out the exceed_cr_dr_exception and
1055 -- SGL Accounts SUM mismatch
1056 PROCEDURE exceed_dr_cr_sgl_mismatch_exc
1057 IS
1058 -- cursor to get the customer_trx_id and and treasury Symbol
1059 CURSOR trx_cur
1060 IS
1061 SELECT customer_trx_id ,
1062 snd_app_sym
1063 FROM fv_ipac_trx_all
1064 WHERE processed_flag ='N'
1065 AND org_id = v_org_id
1066 AND set_of_books_id = v_set_of_books_id
1067 AND exclude_flag ='N'
1068 AND unt_iss <> '~RA'
1069 AND accounted_flag ='Y'
1070 AND report_flag ='Y'
1071 GROUP BY customer_trx_id,
1072 snd_app_sym;
1073 /* AND ipac_billing_id BETWEEN
1074 g_start_billing_id AND
1075 g_end_billing_id
1076 GROUP BY customer_trx_id,
1077 snd_app_sym;
1078 */
1079 -- cursor to get all the revenue lines and their respective amounts
1080 -- for a particular transaction excluding RA lines
1081 CURSOR trx_rec_cur (p_customer_trx_id NUMBER, p_snd_app_sym VARCHAR2 )
1082 IS
1083 SELECT trx_line_no,
1084 SUM(amount) amount
1085 FROM fv_ipac_trx_all
1086 WHERE org_id = v_org_id
1087 AND set_of_books_id = v_set_of_books_id
1088 AND processed_flag ='N'
1089 AND customer_trx_id = p_customer_trx_id
1090 AND snd_app_sym = p_snd_app_sym
1091 AND account_class <> 'REC'
1092 AND unt_iss <> '~RA'
1093 AND exclude_flag ='N'
1094 GROUP BY trx_line_no ;
1095 -- Cursor to count the no of accounts
1096 CURSOR trx_lines_cur (p_customer_trx_id NUMBER, P_snd_app_sym VARCHAR2 )
1097 IS
1098 SELECT COUNT(1) trx_count
1099 FROM fv_ipac_trx_all
1100 WHERE org_id = v_org_id
1101 AND set_of_books_id = v_set_of_books_id
1102 AND customer_trx_id = p_customer_trx_id
1103 AND snd_app_sym = p_snd_app_sym
1104 AND unt_iss = '~RA'
1105 AND processed_flag ='N'
1106 AND exclude_flag ='N'
1107 GROUP BY trx_line_no ;
1108 l_total_count NUMBER;
1109 l_amount NUMBER;
1110 l_count_sgl_acct NUMBER;
1111 l_module_name VARCHAR2(200) ;
1112 BEGIN
1113 l_module_name := g_module_name || 'exceed_dr_cr_sgl_mismatch_exc';
1114 FOR trx_rec IN trx_cur
1115 LOOP
1116 -- Check if the Sum of SGL Cr/Dr accounts is equal to the detail record
1117 -- If not raise an exception 'SGL_SUM_MISMATCH'
1118 SELECT SUM(DECODE(cr_dr_flag,'D',ABS(amount),0)) - SUM(DECODE(cr_dr_flag,'C',ABS(amount),0)),
1119 COUNT(sgl_acct_num)
1120 INTO l_amount,
1121 l_count_sgl_acct
1122 FROM fv_ipac_trx_all
1123 WHERE set_of_books_id = v_set_of_books_id
1124 AND org_id = v_org_id
1125 AND customer_trx_id = trx_rec.customer_trx_id
1126 AND (bulk_exception <> 'BUDGETARY'
1127 AND bulk_exception IS NULL)
1128 AND unt_iss = '~RA'
1129 AND snd_app_sym = trx_rec.snd_app_sym;
1130 IF MOD(l_count_sgl_acct,2) = 0 AND l_amount <> 0 THEN
1131 UPDATE fv_ipac_trx_all
1132 SET bulk_exception = 'SGL_SUM_MISMATCH',
1133 report_flag ='N' ,
1134 amount =
1135 (SELECT SUM(amount)
1136 FROM fv_ipac_trx_all
1137 WHERE set_of_books_id = v_set_of_books_id
1138 AND org_id = v_org_id
1139 AND customer_trx_id =trx_rec.customer_trx_id
1140 AND ACCOUNT_CLASS <> 'REC'
1141 AND unt_iss <> '~RA'
1142 )
1143 WHERE set_of_books_id = v_set_of_books_id
1144 AND org_id = v_org_id
1145 AND customer_trx_id = trx_rec.customer_trx_id
1146 AND snd_app_sym =trx_rec.snd_app_sym
1147 AND (bulk_exception IS NULL
1148 AND bulk_exception <> 'BUDGETARY')
1149 AND unt_iss = '~RA';
1150 END IF;
1151 -- Check for more than 8 SGL accounts for each detail record
1152 -- (customer Trx ID , treasury Symbol and line number)
1153 l_total_count := 0;
1154 FOR trx_lines_rec IN trx_lines_cur(trx_rec.customer_trx_id , trx_rec.snd_app_sym )
1155 LOOP
1156 IF trx_lines_rec.trx_count > 8 THEN
1157 UPDATE fv_ipac_trx_all
1158 SET bulk_exception = 'EXCEED_DR_CR',
1159 report_flag ='N' ,
1160 amount =
1161 (SELECT SUM(amount)
1162 FROM fv_ipac_trx_all
1163 WHERE set_of_books_id = v_set_of_books_id
1164 AND org_id = v_org_id
1165 AND customer_trx_id =trx_rec.customer_trx_id
1166 AND ACCOUNT_CLASS <> 'REC'
1167 AND unt_iss <> '~RA'
1168 )
1169 WHERE set_of_books_id = v_set_of_books_id
1170 AND org_id = v_org_id
1171 AND customer_trx_id =trx_rec.customer_trx_id
1172 AND snd_app_sym =trx_rec.snd_app_sym
1173 AND (bulk_exception IS NULL
1174 AND bulk_exception <> 'BUDGETARY')
1175 AND unt_iss = '~RA';
1176 END IF ;
1177 END LOOP;
1178 END LOOP;
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 errcode :=SQLCODE;
1182 errmsg := SQLERRM || ' -- Error IN getting' ||' the EXCEED_CR_DR EXCEPTION :' ||' PROCEDURE :- exceed_dr_cr_count_exception';
1183 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1184 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
1185 END IF;
1186 RAISE;
1187 END exceed_dr_cr_sgl_mismatch_exc;
1188
1189 -- -----------------------------------------------------------------------------
1190 -- -----------------------------------------------------------------------------
1191
1192 PROCEDURE main (errbuf OUT NOCOPY VARCHAR2,
1193 retcode OUT NOCOPY VARCHAR2,
1194 profile_class_id VARCHAR2,
1195 customer_category VARCHAR2,
1196 customer_id VARCHAR2,
1197 transaction_type VARCHAR2,
1198 trx_date_low VARCHAR2,
1199 trx_date_high VARCHAR2,
1200 contact_ph_no VARCHAR2)
1201 IS
1202 l_req_id NUMBER;
1203 l_call_status BOOLEAN ;
1204 rphase VARCHAR2(30);
1205 rstatus VARCHAR2(30);
1206 dphase VARCHAR2(30);
1207 dstatus VARCHAR2(30);
1208 message VARCHAR2(240);
1209 l_valid_flag VARCHAR2(1);
1210 l_ignore_budgetary_flag VARCHAR2(1);
1211 l_drcr_acct Gl_Ussgl_Account_Pairs.dr_account_segment_value%TYPE;
1212 trx_rec fv_ipac_trx_all%ROWTYPE;
1213 v_commitment_id ra_customer_trx_all.initial_customer_trx_id%TYPE;
1214 l_sgl_acct_num gl_code_combinations.segment1%TYPE;
1215 l_module_name VARCHAR2(200) ;
1216 l_bill_to_address_id Number;
1217 BEGIN
1218 l_module_name := g_module_name || 'main';
1219 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Input parameters are :');
1220 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'PROFILE_CLASS_ID :'||profile_class_id);
1221 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'CUSTOMER_CATEGORY :'||customer_category);
1222 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'CUSTOMER_ID :'||Customer_id);
1223 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'TRANSACTION_TYPE :'||transaction_type);
1224 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'TRX_DATE_LOW :'||trx_date_low);
1225 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'TRX_DATE_HIGH :'||trx_date_high);
1226 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'CONTACT_PH_NO :'||contact_ph_no);
1227 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'CURRENCY :'||parm_currency);
1228 -- Assign parameter values to variables
1229 parm_transaction_type := transaction_type;
1230 parm_profile_class_id := profile_class_id;
1231 parm_customer_category := customer_category;
1232 parm_customer_id := customer_id;
1233 parm_trx_date_low := fnd_date.canonical_to_date(trx_date_low);
1234 parm_trx_date_high := fnd_date.canonical_to_date(trx_date_high);
1235 -- parm_currency := currency;
1236 parm_contact_ph_no := contact_ph_no;
1237 -- Assign values to who column variables
1238 v_created_by := fnd_global.user_id;
1239 v_creation_date := SYSDATE;
1240 v_last_updated_by := fnd_global.user_id;
1241 v_last_update_date := SYSDATE;
1242 v_last_update_login := fnd_global.login_id;
1243 del_exception_recs;
1244 get_bal_seg_name;
1245
1246 --get the start of billing Id
1247 SELECT fv_ipac_billing_id_s.NEXTVAL+1 INTO g_start_billing_id FROM dual ;
1248 FOR trx_select_rec IN trx_select
1249 LOOP -- trx_select
1250 init_vars;
1251 v_receipt_method_id := trx_select_rec.receipt_method_id;
1252 trx_rec.taxpayer_number := NULL;
1253 trx_rec.sender_do_sym := trx_select_rec.address_lines_phonetic;
1254 trx_rec.dpr_cd := trx_select_rec.eliminations_id;
1255 trx_rec.trx_number := trx_select_rec.trx_number;
1256 trx_rec.trx_date := trx_select_rec.trx_date;
1257 trx_rec.obl_dcm_nr := trx_select_rec.trx_number;
1258 trx_rec.pay_flg := v_pay_flag;
1259 trx_rec.po_number := trx_select_rec.purchase_order;
1260 trx_rec.customer_trx_id := trx_select_rec.customer_trx_id;
1261 trx_rec.customer_id := trx_select_rec.customer_id;
1262 --trx_rec.customer_name := trx_select_rec.customer_name;
1263 l_bill_to_address_id := trx_select_rec.bill_to_address_id;
1264 v_commitment_id := trx_select_rec.initial_customer_trx_id;
1265 -------------------------------------------------------
1266 --IPAC FY2003 -04
1267 trx_rec.cust_duns_num := trx_select_rec.duns_number_c;
1268 v_invoice_currency := trx_select_rec.invoice_currency_code;
1269 --------------------------------------------------------
1270 IF v_commitment_id IS NULL THEN
1271 trx_rec.contract_no := NULL;
1272 ELSE
1273 SELECT trx_number
1274 INTO trx_rec.contract_no
1275 FROM Ra_Customer_Trx
1276 WHERE customer_trx_id = v_commitment_id;
1277 END IF;
1278 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Processing Transaction:'||trx_rec.trx_number);
1279 SELECT SUM(amount_due_original),
1280 SUM(NVL(amount_adjusted,0)+ NVL(amount_credited,0)+ NVL(amount_due_remaining,0) + NVL(amount_applied,0))
1281 INTO v_original_amount,
1282 v_paid_amount
1283 FROM ar_payment_schedules
1284 WHERE customer_trx_id = trx_select_rec.customer_trx_id
1285 AND org_id = v_org_id;
1286 IF v_original_amount < v_paid_amount THEN
1287 trx_exception_flag := 'Y';
1288 END IF;
1289 DELETE
1290 FROM fv_ipac_trx_all
1291 WHERE customer_trx_id = trx_select_rec.customer_trx_id
1292 AND processed_flag = 'N'
1293 AND exclude_flag = 'N'
1294 AND cash_receipt_id IS NULL
1295 AND set_of_books_id = v_set_of_books_id
1296 AND NVL(org_id,-99) = NVL(v_org_id,-99);
1297 FOR det_select_rec IN det_select(trx_select_rec.customer_trx_id)
1298 LOOP -- detail_select
1299 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1300 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '---- DETAILS -------');
1301 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Trx Number: '||trx_select_rec.trx_number);
1302 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Trx Date: '||trx_select_rec.trx_date);
1303 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Amount: '||det_select_rec.amount_due_remaining);
1304 END IF;
1305 get_treasury_symbol(bl_seg_name, det_select_rec.code_combination_id);
1306 trx_rec.trn_set_id := 810;
1307 trx_rec.amount := det_select_rec.amount_due_remaining;
1308 trx_rec.cnt_nm := det_select_rec.user_name;
1309 trx_rec.dsc := det_select_rec.description;
1310 trx_rec.qty := ROUND(det_select_rec.quantity_invoiced * (det_select_rec.percent/100),2);
1311 trx_rec.snd_app_sym := v_treasury_symbol;
1312 trx_rec.unt_iss := det_select_rec.uom_code;
1313 trx_rec.unt_prc := det_select_rec.unit_selling_price;
1314 trx_rec.trx_line_no := det_select_rec.line_number;
1315 trx_rec.account_class := det_select_rec.account_class;
1316 trx_rec.sgl_acct_num := gl_account_num(det_select_rec.code_combination_id);
1317 Fv_Utility.Log_Mesg(FND_LOG.LEVEL_STATEMENT,l_module_name, 'Account Class,Trx Line No,SGL Acct Num = '|| trx_rec.account_class||','||trx_rec.trx_line_no||','|| trx_rec.sgl_acct_num);
1318 -- Get the exception category
1319 trx_rec.exception_category := get_trx_exception(trx_rec, det_select_rec.customer_trx_line_id, l_bill_to_address_id);
1320 trx_rec.taxpayer_number := v_customer_alc;
1321 -- Set the Debit/Credit Flag based on the
1322 -- Account Class and sign
1323 IF (det_select_rec.account_class = 'REV' AND SIGN(det_select_rec .amount_due_remaining) =1) OR (det_select_rec.account_class = 'REC' AND SIGN(det_select_rec .amount_due_remaining) =-1 ) THEN
1324 trx_rec.cr_dr_flag := 'C';
1325 ELSE
1326 trx_rec.cr_dr_flag := 'D';
1327 END IF;
1328 IF trx_rec.exception_category IS NULL THEN
1329 trx_rec.report_flag := 'Y';
1330 ELSE
1331 trx_rec.report_flag := 'N';
1332 END IF;
1333 IF trx_rec.account_class = 'REC' THEN
1334 trx_rec.unt_iss := '~~R' ;
1335 END IF;
1336 insert_trx_rec(trx_rec);
1337 init_vars;
1338 END LOOP; -- detail_select
1339 END LOOP; -- trx_SELECT
1340 --get the end of billing id
1341 SELECT fv_ipac_billing_id_s.CURRVAL INTO g_end_billing_id FROM dual;
1342 -- To create REC line equal to a detail lines for each transaction
1343 -- equalize_rec_lines;
1344 -- UPDATE the fv_ipac_trx_all with report flag ='N'
1345 -- FOR each distibution line of a transaction
1346 -- if any of the distibution line has an exception
1347 UPDATE fv_ipac_trx_all trx
1348 SET report_flag ='N'
1349 WHERE org_id = v_org_id
1350 AND set_of_books_id = v_set_of_books_id
1351 AND report_flag <> 'N'
1352 AND EXISTS
1353 (SELECT 'X'
1354 FROM fv_ipac_trx_all
1355 WHERE org_id = v_org_id
1356 AND set_of_books_id = v_set_of_books_id
1357 AND customer_trx_id = trx.customer_trx_id
1358 AND report_flag = 'N'
1359 )
1360 ;
1361 COMMIT;
1362 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Process Complete');
1363 -- Submit the IPAC Detail Report
1364 l_req_id := FND_REQUEST.SUBMIT_REQUEST('FV', 'FVIPDTLR', '', '', FALSE, profile_class_id , customer_category ,
1365 customer_id, transaction_type, trx_date_low, trx_date_high, parm_currency, v_set_of_books_id,
1366 v_org_id );
1367 -- if concurrent request submission failed then abort process
1368 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'IPAC Detail Report Request ID =' ||l_req_id);
1369 IF l_req_id = 0 THEN
1370 errbuf := 'Unable to Submit IPAC transaction Detail Report';
1371 retcode := '-1';
1372 ROLLBACK;
1373 ELSE
1374 COMMIT;
1375 END IF;
1376 IF retcode <> 0 THEN
1377 delete_records;
1378 END IF;
1379 -- Check status of completed concurrent program
1380 -- and if complete exit
1381 l_call_status := fnd_concurrent.wait_for_request( l_req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
1382 IF l_call_status = FALSE THEN
1383 errbuf := 'Can not wait for the status of IPAC '|| 'Transaction Detail Report';
1384 retcode := '2';
1385 END IF;
1386 -- Submit the Exception report
1387 l_req_id := FND_REQUEST.SUBMIT_REQUEST('FV', 'FVIPEXCR', '', '', FALSE, v_set_of_books_id, v_org_id );
1388 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'IPAC TRANSACTION EXCEPTION Report '
1389 || 'Request ID ='||l_req_id);
1390 -- if concurrent request submission failed then abort process
1391 IF l_req_id = 0 THEN
1392 errbuf := 'Unable to Submit IPAC Transaction '|| 'Exception Report';
1393 retcode := '-1';
1394 ROLLBACK;
1395 ELSE
1396 COMMIT;
1397 END IF;
1398 IF retcode <> 0 THEN
1399 delete_records ;
1400 END IF;
1401 -- Check status of completed concurrent program
1402 -- and if complete exit
1403 l_call_status := fnd_concurrent.wait_for_request( l_req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);
1404 IF l_call_status = FALSE THEN
1405 errbuf := 'Can not wait for the status of IPAC '|| 'Transaction Exception Report';
1406 retcode := '-1';
1407 END IF;
1408 IF retcode <> 0 THEN
1409 delete_records ;
1410 END IF;
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 IF errcode IS NULL THEN
1414 errcode :=SQLCODE;
1415 errmsg := SQLERRM || ' -- Error IN IPAC selection' || ' process : Procedure :- main';
1416 END IF;
1417 retcode := errcode;
1418 errbuf := errmsg;
1419 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1420 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
1421 END IF;
1422 END; -- main
1423 -------------------------------------------------------------------------------
1424 ---------------------------- End of Selection Process -------------------------
1425 -------------------------------------------------------------------------------
1426
1427 -- Procedure to create File Id Record for the Bulk File
1428 PROCEDURE create_file_id_rec
1429 IS
1430 v_statement VARCHAR2(200);
1431 l_module_name VARCHAR2(200) ;
1432 BEGIN
1433 l_module_name := g_module_name || 'create_file_id_rec';
1434 v_statement := 'SELECT ''PCA '' FROM dual';
1435 fv_flatfiles.create_flat_file(v_statement);
1436 END ; -- create_file_id_rec
1437
1438 -- -----------------------------------------------------------------------------
1439 -- -----------------------------------------------------------------------------
1440
1441 -- Procedure to create Batch Header Record for the Bulk File
1442 PROCEDURE create_batch_header(lv_set_of_books_id IN NUMBER,
1443 lv_sender_alc VARCHAR2)
1444 IS
1445 CURSOR trx_hdr
1446 IS
1447 SELECT customer_trx_id
1448 FROM fv_ipac_trx_all trx
1449 WHERE set_of_books_id = lv_set_of_books_id
1450 AND org_id = v_org_id
1451 AND processed_flag = 'N'
1452 AND exclude_flag = 'N'
1453 AND report_flag = 'Y'
1454 GROUP BY customer_trx_id,
1455 trn_set_id;
1456 v_header_count NUMBER ;
1457 v_detail_count NUMBER ;
1458 v_total_count NUMBER ; -- For file header and Batch Header
1459 l_total_ussgl_count NUMBER ;
1460 l_ussgl_count NUMBER ;
1461 l_module_name VARCHAR2(200);
1462 v_statement VARCHAR2(2000);
1463 l_file_id VARCHAR2(19);
1464 BEGIN
1465 l_module_name:=g_module_name||'create_batch_header';
1466 v_header_count := 0;
1467 v_detail_count := 0;
1468 v_total_count := 2; -- For file header and Batch Header
1469 l_total_ussgl_count :=0;
1470 l_ussgl_count :=0;
1471
1472 -- get the total no of customer transactions
1473 SELECT COUNT(DISTINCT(customer_trx_id||trn_set_id))
1474 INTO v_header_count
1475 FROM fv_ipac_trx_all trx
1476 WHERE set_of_books_id = lv_set_of_books_id
1477 AND org_id = v_org_id
1478 AND processed_flag = 'N'
1479 AND exclude_flag = 'N'
1480 AND report_flag = 'Y'
1481 AND account_class <> 'REC'
1482 AND unt_iss <> '~RA'
1483 AND Bulk_Exception IS NULL
1484 AND cash_receipt_id IS NOT NULL
1485 AND accounted_flag='Y';
1486
1487 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1488 'v_header_count = '||v_header_count);
1489
1490 -- get the count of total ussgl accounts
1491 SELECT COUNT(1)
1492 INTO l_total_ussgl_count
1493 FROM fv_ipac_trx_all trx
1494 WHERE set_of_books_id = lv_set_of_books_id
1495 AND org_id = v_org_id
1496 AND trx_line_no is NOT NULL
1497 AND unt_iss = '~RA'
1498 AND bulk_exception is NULL
1499 AND customer_trx_id IN
1500 ( SELECT customer_trx_id
1501 FROM fv_ipac_trx_all trx
1502 WHERE set_of_books_id = lv_set_of_books_id
1503 AND org_id = v_org_id
1504 AND processed_flag = 'N'
1505 AND exclude_flag = 'N'
1506 AND report_flag = 'Y'
1507 AND account_class <> 'REC'
1508 AND unt_iss <> '~RA'
1509 AND bulk_exception IS NULL
1510 AND cash_receipt_id IS NOT NULL
1511 AND accounted_flag='Y'
1512 )
1513 ;
1514 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1515 'l_total_ussgl_count = '||l_total_ussgl_count);
1516
1517 --IF total ussgl count is 0 then nothing to report
1518 --write message and return
1519 IF l_total_ussgl_count = 0 THEN
1520 v_statement := 'SELECT ''No transactions found to report for Bulk File!'' FROM dual' ;
1521 fv_flatfiles.create_flat_file(v_statement);
1522 RETURN;
1523 END IF;
1524
1525 --Geting the count of total detail record
1526 SELECT COUNT(1)
1527 INTO v_detail_count
1528 FROM
1529 (SELECT customer_trx_id
1530 FROM fv_ipac_trx_all
1531 WHERE processed_flag = 'N'
1532 AND set_of_books_id = lv_set_of_books_id
1533 AND org_id = v_org_id
1534 AND exclude_flag = 'N'
1535 AND report_flag = 'Y'
1536 AND account_class <> 'REC'
1537 AND unt_iss <> '~RA'
1538 GROUP BY customer_trx_id,
1539 trx_line_no,
1540 snd_app_sym
1541 );
1542
1543 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1544 'v_detail_count = '||v_detail_count);
1545
1546 -- Multiply the detail Rec by 2 for Rec Records per detail record
1547 v_total_count :=v_total_count + v_header_count + v_detail_count
1548 + l_total_ussgl_count;
1549
1550 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1551 'v_total_count = '||v_total_count);
1552
1553 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1554 'lv_sender_alc = '||lv_sender_alc);
1555
1556 --Added for Bug 12839610
1557 SELECT lpad(lv_sender_alc,8,'0')||
1558 to_char(sysdate,'YYYYMMDD')||
1559 lpad(FV_IPAC_AR_BATCH_HDR_S.nextval,3,'0')
1560 INTO l_file_id
1561 FROM DUAL;
1562
1563 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1564 'l_file_id = '||l_file_id);
1565
1566 v_statement := 'SELECT ''B''||''IPAC'' || LPAD('
1567 || v_total_count||',8,''0'') ||
1568 '''||l_file_id||''' FROM dual' ;
1569
1570 fv_flatfiles.create_flat_file(v_statement);
1571
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1575 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module_name,
1576 errmsg);
1577 END IF;
1578 RAISE;
1579 END ; -- create_batch_header
1580
1581 -- -----------------------------------------------------------------------------
1582 -- -----------------------------------------------------------------------------
1583
1584 -- Procedure to create Transaction Header Record for the Bulk File
1585 PROCEDURE create_trx_header(lv_set_of_books_id IN NUMBER,
1586 lv_customer_trx_id IN NUMBER)
1587 IS
1588 v_statement VARCHAR2(2000);
1589 l_module_name VARCHAR2(200) ;
1590 BEGIN
1591 l_module_name := g_module_name || 'create_trx_header';
1592 -- Modified the format according to FV IPAC FY 2003-04
1593 v_statement :=
1594 'SELECT ''H''||
1595 LPAD(SUBSTR(fit.sender_alc,1,8),8,''0'')||
1596 REPLACE(TO_CHAR(SUM(fit.amount),''FM099999999999D00''),
1597 ''.'','''')||
1598 LPAD(SUBSTR(fit.taxpayer_number,1,8),8,'' '')||
1599 RPAD(SUBSTR(fit.sender_do_sym,1,5),5,'' '') ||
1600 fit.trn_set_id ||
1601 RPAD(NVL(SUBSTR(rct.ct_reference, 1, 8), '' ''), 8, '' '')||
1602 RPAD('' '',2)
1603 FROM fv_ipac_trx fit,
1604 ra_customer_trx rct
1605 WHERE fit.set_of_books_id = :b_set_of_books_id
1606 AND rct.customer_trx_id = fit.customer_trx_id
1607 AND fit.processed_flag = ''N''
1608 AND fit.exclude_flag = ''N''
1609 AND fit.report_flag = ''Y''
1610 AND fit.account_class <> ''REC''
1611 AND fit.unt_iss <> ''~RA''
1612 AND fit.customer_trx_id = :b_customer_trx_id
1613 GROUP BY fit.customer_trx_id,fit.sender_alc,
1614 fit.trn_set_id,
1615 fit.taxpayer_number,
1616 fit.sender_do_sym,rct.ct_reference
1617 ORDER BY fit.customer_trx_id, fit.trn_set_id'
1618 ;
1619 create_flat_file(v_statement,lv_set_of_books_id, lv_customer_trx_id );
1620 END ; -- create_trx_header
1621
1622 -- -----------------------------------------------------------------------------
1623 -- -----------------------------------------------------------------------------
1624
1625 -- Procedure to create Transaction Detail Record for the Bulk File
1626 PROCEDURE create_trx_dtl_rec(trx_details_rec trx_detail_cur%ROWTYPE)
1627 IS
1628 l_module_name VARCHAR2(200) ;
1629 l_trx_detail_rec VARCHAR2(8000);
1630 BEGIN
1631 l_module_name := g_module_name || 'create_trx_dtl_rec';
1632 -- Modified the format According to FV IPAC FY 2003-04
1633 SELECT 'D' || RPAD(' ',16)||RPAD(' ',12)|| REPLACE(TO_CHAR(trx_details_rec.amount, 'FM099999999999D00'),'.','')||
1634 RPAD(SUBSTR(trx_details_rec.cnt_nm,1,60),60,' ') || RPAD(nvl(trx_details_rec.cnt_phn_nr,' '),17,' ')||RPAD(' ',6)||
1635 DECODE(trx_details_rec.contract_no,NULL,RPAD(' ',17), RPAD(SUBSTR(trx_details_rec.contract_no,1,17),17,' '))||
1636 DECODE(trx_details_rec.dpr_cd,NULL,' ', RPAD(SUBSTR(trx_details_rec.dpr_cd,1,2),2,' '))||
1637 DECODE(trx_details_rec.dsc,NULL,RPAD(' ',320), RPAD(SUBSTR(trx_details_rec.dsc,1,320),320,' '))|| RPAD('0',8,'0')
1638 || DECODE(trx_details_rec.trx_number,NULL,RPAD(' ',22), RPAD(SUBSTR(trx_details_rec.trx_number,1,22),22,' '))||
1639 RPAD(' ',30)||RPAD(' ',20)|| RPAD(NVL(SUBSTR(trx_details_rec.comments, 1, 320), ' '), 320, ' ')||
1640 DECODE(trx_details_rec.obl_dcm_nr,NULL,RPAD(' ',17), RPAD(SUBSTR(trx_details_rec.obl_dcm_nr,1,17),17,' '))||
1641 DECODE(trx_details_rec.pay_flg,NULL,' ', trx_details_rec.pay_flg)|| DECODE(trx_details_rec.po_number,NULL,RPAD(' ',22),
1642 RPAD(SUBSTR(trx_details_rec.po_number,1,22),22,' '))|| LPAD(trx_details_rec.qty*100,14,0) || RPAD(' ',1) || RPAD(' ',27)||
1643 RPAD(' ',8)|| RPAD(NVL(trx_details_rec.cust_duns_num,' '),9)|| RPAD(' ',4)|| RPAD(' ',15)||
1644 DECODE(trx_details_rec.snd_app_sym,NULL,RPAD(' ',27), RPAD(SUBSTR(REPLACE(trx_details_rec.snd_app_sym, ' ', ''),1,27),27,' '))
1645 || RPAD(' ',8) || RPAD(' ',9) || RPAD(' ',4) || RPAD(' ',15) || DECODE(trx_details_rec.unt_iss,NULL,' ',
1646 RPAD(SUBSTR(trx_details_rec.unt_iss,1,2),2,' '))|| DECODE(trx_details_rec.unt_prc,NULL,RPAD('0',14,'0'),
1647 REPLACE(TO_CHAR(trx_details_rec.unt_prc,'FM099999999999D00') ,'.',''))|| RPAD(' ',15) trx_line_rec
1648 INTO l_trx_detail_rec
1649 FROM dual ;
1650 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_trx_detail_rec);
1651 EXCEPTION
1652 WHEN OTHERS THEN
1653 errcode :=SQLCODE;
1654 errmsg :=SQLERRM || 'Error in Creating the Detail Record ' || '- Procedure: create_trx_dtl_rec';
1655 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1656 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name, errmsg);
1657 END IF;
1658 RAISE;
1659 END create_trx_dtl_rec;
1660
1661 -- -----------------------------------------------------------------------------
1662 -- -----------------------------------------------------------------------------
1663
1664 ------------------------------------------------------------------------
1665 -- IPAC FY2003-04
1666 -- Procedure to create the USSGL Records corresponding to the
1667 -- Transaction details
1668 -----------------------------------------------------------------------
1669 PROCEDURE create_ussgl_rec(p_cust_trx_id IN NUMBER, l_trx_line_no IN NUMBER, p_snd_app_sym IN VARCHAR2)
1670 IS
1671 l_ussgl_rec VARCHAR2(100);
1672 CURSOR ussgl_cur ( p_cust_trx_id NUMBER )
1673 IS
1674 SELECT sgl_acct_num,
1675 amount,
1676 cr_dr_flag
1677 FROM fv_ipac_trx_all trx
1678 WHERE org_id = v_org_id
1679 AND set_of_books_id = v_set_of_books_id
1680 AND customer_trx_id = p_cust_trx_id
1681 AND unt_iss = '~RA'
1682 AND report_flag = 'Y'
1683 AND processed_flag = 'N'
1684 AND accounted_flag = 'Y'
1685 AND exclude_flag = 'N'
1686 AND bulk_exception is NULL
1687 AND cash_receipt_id IS NOT NULL
1688 AND snd_app_sym = p_snd_app_sym
1689 AND trx_line_no = l_trx_line_no;
1690 l_module_name VARCHAR2(200) ;
1691 l_output_string VARCHAR2(2000);
1692 BEGIN
1693 l_module_name := g_module_name || 'create_ussgl_rec';
1694 -- create the USSGL records
1695 FOR ussgl_rec IN ussgl_cur(p_cust_trx_id)
1696 LOOP
1697 IF (ussgl_rec.cr_dr_flag = 'D') THEN
1698 ussgl_rec.cr_dr_flag := 'C';
1699 ELSE
1700 ussgl_rec.cr_dr_flag := 'D';
1701 END IF;
1702 l_output_string := 'E' || 'A' || LPAD (ussgl_rec.sgl_acct_num, 4,0) || 'S' || 'F' || LPAD(REPLACE(ussgl_rec.amount*100,'-',NULL),14,0) || ussgl_rec.cr_dr_flag;
1703 fnd_file.put_line (fnd_file.output, l_output_string);
1704 END LOOP ; --END of USSGL record
1705 EXCEPTION
1706 WHEN OTHERS THEN
1707 errcode :=SQLCODE;
1708 errmsg :=SQLERRM|| '-- Error in creating' || ' the USSGL Record :' || 'Procedure - create_ussgl_rec';
1709 fv_utility.debug_mesg(fnd_log.level_unexpected, l_module_name, errmsg);
1710 RAISE;
1711 END create_ussgl_rec;
1712
1713 -- -----------------------------------------------------------------------------
1714 -- -----------------------------------------------------------------------------
1715
1716 ------------------------------------------------------------------------------
1717 -- Procedure for creating and applying cash receipts
1718 -- The code also generates the SLA accounting for its distributions incase
1719 -- it has a cash receipt applied to it.
1720 ------------------------------------------------------------------------------
1721 PROCEDURE create_receipt_accounting(trx_receipt_rec IN trx_receipt_cur%ROWTYPE,
1722 p_currency_code IN VARCHAR2,
1723 p_receipt_method_id IN NUMBER,
1724 p_receipt_date IN DATE,
1725 p_gl_date IN DATE,
1726 p_cash_receipt_id IN NUMBER)
1727 IS
1728 l_event_source_info xla_events_pub_pkg.t_event_source_info;
1729 l_entity_id NUMBER;
1730 l_legal_entity_id NUMBER;
1731 -- l_event_id NUMBER;
1732 -- l_event_type_code VARCHAR2(30);
1733 l_ae_header_id NUMBER;
1734 l_amount NUMBER;
1735 l_cr_dr_flag VARCHAR2(1);
1736 l_gl_account_num gl_code_combinations.segment1%TYPE;
1737 l_trx_dist_id NUMBER;
1738 l_trx_line_no NUMBER;
1739 l_trx_number VARCHAR2(20);
1740 l_trx_date DATE;
1741 l_customer_trx_id NUMBER;
1742 l_customer_id NUMBER;
1743 ussgl_flag VARCHAR(1);
1744 x_cash_receipt_id NUMBER;
1745 x_accounting_batch_id NUMBER;
1746 x_errbuf VARCHAR2(1000);
1747 x_retcode NUMBER;
1748 x_request_id NUMBER;
1749 sgl_acct_num varchar2(30);
1750 l_sgl_acct_num gl_code_combinations.segment1%TYPE;
1751 l_module_name VARCHAR2(200) ;
1752 -- l_event_stat VARCHAR2(1);
1753 l_is_line_no BOOLEAN;
1754
1755 --------- Bug 5451545 ---------------------------
1756 CURSOR get_evnt_cur(p_entity_id NUMBER,p_proc NUMBER)
1757 IS
1758 SELECT event_id,
1759 event_type_code
1760 FROM xla_events
1761 WHERE application_id = 222
1762 AND entity_id = p_entity_id
1763 AND
1764 ( (p_proc = 1 AND event_status_code <> 'P' )
1765 OR
1766 (p_proc = 2 AND event_status_code = 'P' )
1767 );
1768
1769 --------- Bug 5451545 ---------------------------
1770
1771 BEGIN
1772 l_module_name := g_module_name || 'create_receipt_accounting';
1773
1774 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1775 'Inside create_receipt_accounting');
1776
1777 IF trx_receipt_rec.cash_receipt_id IS NOT NULL
1778 OR p_cash_receipt_id IS NOT NULL THEN
1779
1780 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1781 'trx_receipt_rec.cash_receipt_id or p_Cash_receipt Is Not Null');
1782
1783 x_retcode := 0;
1784 -- l_event_stat := '';
1785 /* Check if the Account information is already avaliable in
1786 XLA tables, then need not generate SLA Accounting */
1787 -- Get the entity_id and event_id for the given
1788 -- transaction's cash_receipt_id
1789 SELECT entity_id,
1790 legal_entity_id
1791 INTO l_entity_id,
1792 l_legal_entity_id
1793 FROM xla_transaction_entities
1794 WHERE source_id_int_1 = NVL(p_cash_receipt_id , trx_receipt_rec.cash_receipt_id )
1795 AND application_id=222
1796 AND entity_code ='RECEIPTS';
1797
1798 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1799 'l_entity_id ='||l_entity_id);
1800 /*
1801 -- CHECK IT Won't there be two event_ids for receipt
1802 -- creation and application
1803 SELECT event_id,
1804 event_type_code
1805 INTO l_event_id,
1806 l_event_type_code
1807 FROM xla_events
1808 WHERE application_id = 222
1809 AND entity_id = l_entity_id
1810 and event_status_code <> 'P';
1811 */
1812
1813 FOR get_evnt_rec IN get_evnt_cur(l_entity_id,1)
1814 LOOP
1815 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1816 'l_event_id '|| get_evnt_rec.event_id);
1817
1818 l_event_source_info.application_id := 222;
1819 l_event_source_info.legal_entity_id := l_legal_entity_id;
1820 l_event_source_info.ledger_id := v_set_of_books_id;
1821 l_event_source_info.entity_type_code := get_evnt_rec.event_type_code; --l_event_type_code; -- CHECK IT
1822 l_event_source_info.transaction_number := trx_receipt_rec.trx_number;
1823 l_event_source_info.source_id_int_1 := nvl(p_cash_receipt_id,
1824 trx_receipt_rec.cash_receipt_id);
1825 -- Submit the xla_accounting_pub_pkg.accounting_program_
1826 -- document for the entity id.
1827 xla_accounting_pub_pkg.accounting_program_document(p_event_source_info => l_event_source_info,
1828 p_application_id => 222,
1829 p_valuation_method => NULL,
1830 p_entity_id => l_entity_id,
1831 p_accounting_flag => 'Y',
1832 p_accounting_mode => 'F',
1833 p_transfer_flag => 'N',
1834 p_gl_posting_flag => 'N',
1835 p_offline_flag => 'N',
1836 p_accounting_batch_id => x_accounting_batch_id,
1837 p_errbuf => x_errbuf,
1838 p_retcode => x_retcode,
1839 p_request_id => x_request_id);
1840 EXIT;
1841 END LOOP; -- End FOR get_evnt_rec IN get_evnt_cur(l_entity_id)
1842
1843 -- Check if SLA Accounting is done successfully or if the
1844 -- Account information is already avaliable in XLA tables
1845
1846
1847 IF x_retcode = 0 THEN
1848 -- Accounted flag is set for the distribution lines
1849 -- And since exceptions are generated again for these
1850 -- lines therefore we update them as null
1851 UPDATE fv_ipac_trx_all
1852 SET accounted_flag = 'Y',
1853 bulk_exception = NULL
1854 WHERE set_of_books_id = v_set_of_books_id
1855 AND org_id = v_org_id
1856 AND customer_trx_id = trx_receipt_rec.customer_trx_id
1857 AND exclude_flag = 'N'
1858 AND report_flag = 'Y'
1859 AND processed_flag = 'N';
1860
1861 FOR get_evnt_rec IN get_evnt_cur(l_entity_id,2)
1862 LOOP
1863
1864 -- Obtain accounting information
1865 SELECT ae_header_id
1866 INTO l_ae_header_id
1867 FROM xla_ae_headers
1868 WHERE event_id = get_evnt_rec.event_id;
1869
1870 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1871 'l_ae_header_id ='||l_ae_header_id);
1872
1873 FOR xla_acnt_rec IN xla_acnt_cur (l_ae_header_id)
1874 LOOP -- Accounting Information Record
1875
1876 /* Check that if the Receipt Line amounts are negative
1877 for the Credit and Debit Account pair,
1878 then Debit Account is updated with cr_dr_flag='C'
1879 and Credit Account cr_dr_flag='D',
1880 Amount =<positive > for both the Accounts.
1881 This is required for the Bulk File reporting..*/
1882 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1883 'xla_acnt_rec.accounted_cr= '||xla_acnt_rec.accounted_cr);
1884 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1885 'xla_acnt_rec.accounted_dr='||xla_acnt_rec.accounted_dr);
1886
1887 IF xla_acnt_rec.accounted_cr IS NOT NULL THEN
1888 -- Credit Account
1889 l_amount := xla_acnt_rec.accounted_cr;
1890 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1891 'l_amount= '||l_amount);
1892
1893 IF xla_acnt_rec.accounted_cr > 0 THEN
1894 l_cr_dr_flag := 'C';
1895 ELSE
1896 l_cr_dr_flag := 'D';
1897 l_amount := -l_amount;
1898 END IF;
1899 ELSIF xla_acnt_rec.accounted_dr IS NOT NULL THEN
1900 -- Debit Account
1901 l_amount := xla_acnt_rec.accounted_dr;
1902 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1903 'l_amount= '||l_amount);
1904
1905 IF xla_acnt_rec.accounted_dr > 0 THEN
1906 l_cr_dr_flag := 'D';
1907 ELSE
1908 l_cr_dr_flag := 'C';
1909 l_amount := -l_amount;
1910 END IF;
1911 END IF;
1912
1913 -- Determine the trx line number for each distribution
1914 BEGIN
1915
1916 l_is_line_no:=TRUE;
1917
1918 SELECT racust.line_number
1919 INTO l_trx_line_no
1920 FROM ra_customer_trx_lines racust,
1921 ar_distributions ardist,
1922 xla_distribution_links xladist
1923 WHERE
1924 xladist.ae_header_id = xla_acnt_rec.ae_header_id
1925 AND
1926 xladist.ae_line_num = xla_acnt_rec.ae_line_num
1927 AND
1928 xladist.application_id = 222
1929 AND
1930 xladist.SOURCE_DISTRIBUTION_ID_NUM_1 = ardist.line_id
1931 AND
1932 ardist.REF_CUSTOMER_TRX_LINE_ID = racust.CUSTOMER_TRX_LINE_ID;
1933
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936
1937 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1938 'No line_number exists for header_id ='||
1939 xla_acnt_rec.ae_header_id || ' and line_num = ' ||
1940 xla_acnt_rec.ae_line_num);
1941
1942 l_is_line_no :=FALSE;
1943 END;
1944
1945 IF l_is_line_no = TRUE THEN
1946
1947 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1948 'l_trx_line_no='||l_trx_line_no);
1949 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1950 'xla_acnt_rec.code_combination_id='||xla_acnt_rec.code_combination_id);
1951 -- Determine the Natural account from the ccid for
1952 -- each of the accounting lines.
1953 l_gl_account_num := gl_account_num(xla_acnt_rec.code_combination_id);
1954 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1955 'l_gl_account_num ='||l_gl_account_num );
1956 -- Determine the Treasury Symbol
1957
1958 get_treasury_symbol( bl_seg_name, xla_acnt_rec.code_combination_id );
1959
1960 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1961 'bl_seg_name='||bl_seg_name );
1962 -- Insertion of Receipt Accounting information into
1963 -- the table fv_ipac_trx_all
1964 v_bulk_exception := NULL;
1965
1966 v_bulk_exception := get_sgl_exception( l_gl_account_num,
1967 l_sgl_acct_num);
1968
1969 INSERT
1970 INTO fv_ipac_trx_all
1971 (
1972 set_of_books_id,
1973 org_id,
1974 ipac_billing_id,
1975 amount,
1976 cnt_nm,
1977 trx_number,
1978 trx_date,
1979 snd_app_sym,
1980 unt_iss,
1981 customer_trx_id,
1982 customer_id,
1983 trx_line_no,
1984 created_by,
1985 creation_date,
1986 last_updated_by,
1987 last_update_date,
1988 last_update_login,
1989 sgl_acct_num,
1990 bulk_exception,
1991 cr_dr_flag,
1992 PROCESSED_FLAG,
1993 REPORT_FLAG,
1994 ACCOUNTED_FLAG,
1995 RECEIPT_FLAG,
1996 cash_receipt_id,
1997 exclude_flag,
1998 trn_set_id
1999 )
2000 VALUES
2001 (
2002 v_set_of_books_id,
2003 v_org_id,
2004 fv_ipac_billing_id_s.NEXTVAL,
2005 l_amount,
2006 nvl(trx_receipt_rec.cnt_nm,-99),
2007 trx_receipt_rec.trx_number,
2008 trx_receipt_rec.trx_date,
2009 v_treasury_symbol,
2010 '~RA',
2011 trx_receipt_rec.customer_trx_id,
2012 trx_receipt_rec.customer_id,
2013 l_trx_line_no,
2014 fnd_global.user_id,
2015 SYSDATE,
2016 fnd_global.user_id,
2017 SYSDATE,
2018 fnd_global.user_id,
2019 l_gl_account_num,
2020 v_bulk_exception,
2021 l_cr_dr_flag,
2022 'N',
2023 'Y',
2024 'Y',
2025 'Y',
2026 NVL(p_cash_receipt_id,trx_receipt_rec.cash_receipt_id),
2027 'N',
2028 trx_receipt_rec.trn_set_id
2029 )
2030 ;
2031 END IF; -- End IF l_is_line_no = TRUE THEN
2032 END LOOP; -- Accounting Information Record
2033
2034 END LOOP; -- End FOR get_evnt_rec IN get_evnt_cur(l_entity_id)
2035
2036 ELSE -- SLA Accounting Failed
2037 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2038 x_errbuf);
2039 -- Add a new row in fv_ipac_trx_all with unt_iss = '~RA'
2040 -- and bulk_exception = 'ACCOUNTING_NOT_CREATED'
2041 -- for the particular transaction
2042 INSERT
2043 INTO fv_ipac_trx_all
2044 (
2045 set_of_books_id,
2046 org_id,
2047 ipac_billing_id,
2048 amount,
2049 cnt_nm,
2050 trx_number,
2051 trx_date,
2052 unt_iss,
2053 customer_trx_id,
2054 customer_id,
2055 report_flag,
2056 exclude_flag,
2057 processed_flag,
2058 created_by,
2059 creation_date,
2060 last_updated_by,
2061 last_update_date,
2062 last_update_login,
2063 receipt_flag,
2064 accounted_flag,
2065 bulk_exception,
2066 cash_receipt_id,
2067 trn_set_id
2068 )
2069 VALUES
2070 (
2071 v_set_of_books_id,
2072 v_org_id,
2073 fv_ipac_billing_id_s.NEXTVAL,
2074 trx_receipt_rec.amount,
2075 '-99',
2076 trx_receipt_rec.trx_number,
2077 trx_receipt_rec.trx_date,
2078 '~RA',
2079 trx_receipt_rec.customer_trx_id,
2080 trx_receipt_rec.customer_id,
2081 'Y',
2082 'N',
2083 'N',
2084 fnd_global.user_id,
2085 SYSDATE,
2086 fnd_global.user_id,
2087 SYSDATE,
2088 fnd_global.user_id,
2089 'Y',
2090 'N',
2091 'ACCOUNTING_NOT_CREATED',
2092 trx_receipt_rec.cash_receipt_id,
2093 trx_receipt_rec.trn_set_id
2094 )
2095 ;
2096 -- Also Update all the detail records related to this
2097 -- transaction with accounted_flag = 'N'
2098 UPDATE fv_ipac_trx_all
2099 SET accounted_flag = 'N'
2100 WHERE set_of_books_id = v_set_of_books_id
2101 AND org_id = v_org_id
2102 AND customer_trx_id= trx_receipt_rec.customer_trx_id
2103 AND exclude_flag = 'N'
2104 AND report_flag = 'Y'
2105 AND processed_flag = 'N';
2106 END IF; -- Check if SLA Accounting done successfully
2107 END IF; -- Check if Receipt Id
2108 -- created, then generate SLA Accounting -- SLA Accounting Done Successfully
2109 -- Bug 12609586: Added Commit to avoid XLA.XLA_EVT_CLASS_ORDERS_GT_U1 violation
2110 COMMIT;
2111 EXCEPTION
2112 WHEN OTHERS THEN
2113
2114 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2115 'Create_Receipt_Accounting - Unexpected Error, Calling Update');
2116
2117 UPDATE fv_ipac_trx_all
2118 SET accounted_flag = '',
2119 bulk_exception = NULL
2120 WHERE set_of_books_id = v_set_of_books_id
2121 AND org_id = v_org_id
2122 AND customer_trx_id = trx_receipt_rec.customer_trx_id
2123 AND exclude_flag = 'N'
2124 AND report_flag = 'Y'
2125 AND processed_flag = 'N';
2126 END create_receipt_accounting;
2127
2128 -- -----------------------------------------------------------------------------
2129 -- -----------------------------------------------------------------------------
2130
2131 PROCEDURE create_receipt_and_apply( errbuf OUT NOCOPY VARCHAR2,
2132 retcode OUT NOCOPY VARCHAR2,
2133 trx_receipt_rec IN trx_receipt_cur%ROWTYPE,
2134 p_currency_code IN VARCHAR2,
2135 p_receipt_method_id IN NUMBER,
2136 p_receipt_date IN DATE,
2137 p_gl_date IN DATE)
2138 IS
2139 x_return_status VARCHAR2(1);
2140 x_msg_count NUMBER;
2141 x_msg_data VARCHAR2(2000);
2142 x_cash_receipt_id NUMBER;
2143 x_accounting_batch_id NUMBER;
2144 x_errbuf VARCHAR2(1000);
2145 x_retcode NUMBER;
2146 x_request_id NUMBER;
2147 l_module_name VARCHAR2(200);
2148 l_payment_trxn_extension_id NUMBER;
2149 BEGIN
2150
2151 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2152 'create_receipt_and_apply begins');
2153
2154 -- Create new receipts only for those transactions which already don't have
2155 -- any receipts applied to them
2156
2157 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2158 'trx_receipt_rec.cash_receipt_id = ' || trx_receipt_rec.cash_receipt_id);
2159
2160 IF trx_receipt_rec.cash_receipt_id IS NULL THEN
2161
2162 BEGIN --Bug 5641377, 7113869
2163 SELECT cba.agency_location_code
2164 INTO v_sender_alc
2165 FROM ar_receipt_method_accounts arma,
2166 Ce_bank_accounts cba,
2167 CE_BANK_ACCT_USES_ALL cbal
2168 WHERE cbal.bank_account_id =cba.bank_account_id
2169 AND cbal.bank_acct_use_id = arma.remit_bank_acct_use_id
2170 AND cba.currency_code = (p_currency_code)
2171 AND arma.primary_flag = 'Y'
2172 AND arma.receipt_method_id =p_receipt_method_id
2173 AND arma.org_id = v_org_id;
2174
2175 EXCEPTION
2176 WHEN NO_DATA_FOUND THEN
2177 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2178 'There is no Billing ALC defined for the Transaction '||trx_receipt_rec.trx_number);
2179 END;
2180
2181 --bug 8654573
2182 begin
2183 select PAYMENT_TRXN_EXTENSION_ID
2184 into
2185 l_payment_trxn_extension_id
2186 from ra_customer_trx_all
2187 where CUSTOMER_TRX_ID = trx_receipt_rec.customer_trx_id;
2188
2189 exception
2190 WHEN NO_DATA_FOUND THEN
2191 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2192 'There is no Payment_trxn_extension_id for the Transaction '||trx_receipt_rec.trx_number);
2193 end;
2194 --bug 8654573
2195
2196 ar_receipt_api_pub.create_and_apply(p_api_version => 1.0,
2197 x_return_status => x_return_status,
2198 x_msg_count => x_msg_count,
2199 x_msg_data => x_msg_data,
2200 p_currency_code => p_currency_code,
2201 p_amount => trx_receipt_rec.amount,
2202 p_receipt_number => trx_receipt_rec.trx_number,
2203 p_receipt_date => p_receipt_date,
2204 p_gl_date => p_gl_date,
2205 p_customer_id => trx_receipt_rec.customer_id,
2206 p_payment_trxn_extension_id => l_payment_trxn_extension_id,
2207 p_deposit_date => SYSDATE,
2208 p_receipt_method_id => p_receipt_method_id,
2209 p_cr_id => x_cash_receipt_id,
2210 p_customer_trx_id => trx_receipt_rec.customer_trx_id,
2211 p_trx_number => trx_receipt_rec.trx_number,
2212 p_amount_applied => trx_receipt_rec.amount,
2213 p_apply_date => p_receipt_date,
2214 p_apply_gl_date => p_gl_date,
2215 p_org_id => v_org_id);
2216
2217 -- If the create_and_apply receipt is done successfully
2218 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2219
2220 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2221 'create_and_apply receipt is done successfully');
2222
2223 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2224 'The receipt is created for the transaction number: '
2225 || trx_receipt_rec.trx_number || ' with Receipt ID: '
2226 || to_char(x_cash_receipt_id));
2227
2228 /* SELECT cba.agency_location_code
2229 INTO v_sender_alc
2230 FROM ar_receipt_method_accounts_all arma,
2231 Ce_bank_accounts cba
2232 WHERE cba.bank_account_id = arma.remit_bank_acct_use_id
2233 AND cba.currency_code = (p_currency_code)
2234 AND arma.primary_flag = 'Y'
2235 AND arma.receipt_method_id = p_receipt_method_id; */
2236
2237
2238 -- Updating all the detail records of the selected
2239 -- transaction for its created receipt and sender alc
2240 UPDATE fv_ipac_trx_all
2241 SET sender_alc = v_sender_alc,
2242 cash_receipt_id = x_cash_receipt_id,
2243 receipt_flag = 'Y'
2244 WHERE set_of_books_id = v_set_of_books_id
2245 AND org_id = v_org_id
2246 AND customer_trx_id = trx_receipt_rec.customer_trx_id
2247 AND exclude_flag = 'N'
2248 AND report_flag = 'Y'
2249 AND processed_flag = 'N';
2250
2251 -- Insert data into fv_interagency_funds table for the
2252 -- created receipt id and receipt number
2253
2254 INSERT
2255 INTO fv_interagency_funds_all
2256 (
2257 INTERAGENCY_FUND_ID,
2258 SET_OF_BOOKS_ID,
2259 ORG_ID,
2260 PROCESSED_FLAG,
2261 CHARGEBACK_FLAG,
2262 LAST_UPDATE_DATE,
2263 LAST_UPDATED_BY,
2264 CREATED_BY,
2265 CREATION_DATE,
2266 LAST_UPDATE_LOGIN,
2267 CUSTOMER_ID,
2268 CASH_RECEIPT_ID,
2269 RECEIPT_NUMBER
2270 )
2271 VALUES
2272 (
2273 fv_interagency_funds_s.NEXTVAL,
2274 v_set_of_books_id,
2275 v_org_id,
2276 'N',
2277 'N',
2278 SYSDATE,
2279 fnd_global.user_id,
2280 fnd_global.user_id,
2281 SYSDATE,
2282 fnd_global.user_id,
2283 trx_receipt_rec.customer_id,
2284 x_cash_receipt_id,
2285 trx_receipt_rec.trx_number
2286 )
2287 ;
2288 create_receipt_accounting(trx_receipt_rec,
2289 p_currency_code ,
2290 p_receipt_method_id ,
2291 p_receipt_date ,
2292 p_gl_date,
2293 x_cash_receipt_id);
2294
2295 ELSE -- If the create_and_apply receipt fails
2296 -- Add a new row in fv_ipac_trx_all with unt_iss = '~RA'
2297 -- and bulk_exception = 'RECEIPT_NOT_CREATED' for the particular
2298 -- transaction
2299
2300 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2301 'create_and_apply receipt fails');
2302
2303 INSERT
2304 INTO fv_ipac_trx_all
2305 (
2306 set_of_books_id,
2307 org_id,
2308 ipac_billing_id,
2309 amount,
2310 cnt_nm,
2311 trx_number,
2312 trx_date,
2313 unt_iss,
2314 customer_trx_id,
2315 customer_id,
2316 report_flag,
2317 exclude_flag,
2318 processed_flag,
2319 created_by,
2320 creation_date,
2321 last_updated_by,
2322 last_update_date,
2323 last_update_login,
2324 receipt_flag,
2325 accounted_flag,
2326 bulk_exception,
2327 trn_set_id
2328 )
2329 VALUES
2330 (
2331 v_set_of_books_id,
2332 v_org_id,
2333 fv_ipac_billing_id_s.NEXTVAL,
2334 trx_receipt_rec.amount,
2335 '-99',
2336 trx_receipt_rec.trx_number,
2337 trx_receipt_rec.trx_date,
2338 '~RA',
2339 trx_receipt_rec.customer_trx_id,
2340 trx_receipt_rec.customer_id,
2341 'Y',
2342 'N',
2343 'N',
2344 fnd_global.user_id,
2345 SYSDATE,
2346 fnd_global.user_id,
2347 SYSDATE,
2348 fnd_global.user_id,
2349 'N',
2350 'N',
2351 'RECEIPT_NOT_CREATED',
2352 trx_receipt_rec.trn_set_id
2353 )
2354 ;
2355 -- Also Update all the detail records related to this
2356 -- transaction with receipt_flag = 'N'
2357 UPDATE fv_ipac_trx_all
2358 SET receipt_flag = 'N'
2359 WHERE set_of_books_id = v_set_of_books_id
2360 AND org_id = v_org_id
2361 AND customer_trx_id = trx_receipt_rec.customer_trx_id
2362 AND exclude_flag = 'N'
2363 AND report_flag = 'Y'
2364 AND processed_flag = 'N';
2365 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2366 'Error generated during the creation of receipt for the transaction number: '
2367 || trx_receipt_rec.trx_number);
2368
2369 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2370 'Return Status: ' || x_return_status);
2371
2372 IF x_msg_data IS NOT NULL THEN
2373 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2374 'Error Information: ' || x_msg_data );
2375 END IF;
2376 END IF; -- Condition related with the success or failure
2377 -- of create_and_apply receipt process
2378 else
2379 if NVL(trx_receipt_rec.accounted_flag,'N')='N' then
2380 create_receipt_accounting( trx_receipt_rec ,
2381 p_currency_code,
2382 p_receipt_method_id ,
2383 p_receipt_date ,
2384 p_gl_date,
2385 NULL );
2386 end if;
2387 END IF; -- Condition related with the creation of new receipts
2388 -- only for those transactions which already don't have
2389 -- any receipts applied to them
2390 EXCEPTION
2391 WHEN OTHERS THEN
2392 IF errcode IS NULL THEN
2393 errcode := SQLCODE;
2394 errmsg := SQLERRM;
2395 END IF;
2396 retcode := errcode;
2397 errbuf := errmsg;
2398 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2399 'Errbuf :'||errbuf);
2400 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2401 'retcode :'||retcode);
2402
2403 END; -- create_receipt_and_apply
2404
2405 -- -----------------------------------------------------------------------------
2406 -- -----------------------------------------------------------------------------
2407
2408 ------------------------------------------------------------------------------
2409 -- Procedure to create bulk file for all those transactions whose receipts
2410 -- and accounting are generated successfully without any bulk_exception.
2411 ------------------------------------------------------------------------------
2412 PROCEDURE create_receipt_acct_main( errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY VARCHAR2, p_receipt_method_id IN NUMBER, p_receipt_date IN DATE, p_gl_date IN DATE )
2413 IS
2414 l_req_id NUMBER;
2415 l_call_status BOOLEAN;
2416 rphase VARCHAR2(30);
2417 rstatus VARCHAR2(30);
2418 dphase VARCHAR2(30);
2419 dstatus VARCHAR2(30);
2420 message VARCHAR2(240);
2421 l_module_name VARCHAR2(200);
2422 BEGIN
2423 l_module_name:=g_module_name||'create_receipt_acct_main';
2424
2425 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2426 'create_receipt_acct_main begins');
2427
2428 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2429 'Input parameters are :');
2430 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2431 'Parm_CURRENCY_CODE :'||parm_currency);
2432 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2433 'P_RECEIPT_METHOD_ID :'||P_RECEIPT_METHOD_ID);
2434 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2435 'P_RECIPT_DATE :'||P_RECEIPT_DATE);
2436 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2437 'P_GL_DATE :'||P_GL_DATE);
2438
2439 get_bal_seg_name;
2440
2441
2442 for trx_receipt_rec in trx_receipt_cur
2443 loop
2444
2445 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2446 'Calling create_receipt_and_apply with customer_trx_id='||
2447 trx_receipt_rec.customer_trx_id);
2448
2449 create_receipt_and_apply(errbuf,
2450 retcode,
2451 trx_receipt_rec,
2452 parm_currency,
2453 p_receipt_method_id,
2454 p_receipt_date,
2455 p_gl_date);
2456 end loop;
2457
2458 exceed_dr_cr_sgl_mismatch_exc;
2459
2460 -- submit Bulk File Report
2461 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2462 'v_sender_alc =' ||v_sender_alc);
2463
2464 l_req_id := FND_REQUEST.SUBMIT_REQUEST('FV',
2465 'FVIBKRPT',
2466 '',
2467 '',
2468 FALSE,
2469 v_org_id,
2470 v_sender_alc);
2471
2472 -- if concurrent request submission failed then abort process
2473 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2474 'IPAC Bulk File Report Request ID =' ||l_req_id);
2475
2476 IF l_req_id = 0 THEN
2477 errbuf := 'Unable to Submit Bulk File Report ';
2478 retcode := '-1';
2479 ROLLBACK;
2480 ELSE
2481 COMMIT;
2482 END IF;
2483 -- Check status of completed concurrent program
2484 -- and if complete exit
2485 l_call_status := fnd_concurrent.wait_for_request(l_req_id,
2486 10,
2487 0,
2488 rphase,
2489 rstatus,
2490 dphase,
2491 dstatus,
2492 message);
2493 IF l_call_status = FALSE THEN
2494 errbuf := 'Can not wait for the status of '||
2495 'IPAC Bulk File Generation Process';
2496 retcode := '2';
2497 END IF;
2498 BEGIN
2499 -- submit IPAC Bulk File Exception Report
2500 l_req_id := FND_REQUEST.SUBMIT_REQUEST('FV',
2501 'FVIPCRBE',
2502 '',
2503 '',
2504 FALSE,
2505 v_set_of_books_id,
2506 v_org_id );
2507
2508 -- if concurrent request submission failed then abort process
2509 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2510 'IPAC Bulk File Exception Report Request ID =' ||l_req_id);
2511
2512 IF l_req_id = 0 THEN
2513 errbuf := 'Unable to Submit Apply Cash Receipts Process';
2514 retcode := '-1';
2515 ROLLBACK;
2516 ELSE
2517 COMMIT;
2518 END IF;
2519 -- Check status of completed concurrent program
2520 -- and if complete exit
2521 l_call_status := fnd_concurrent.wait_for_request( l_req_id,
2522 10,
2523 0,
2524 rphase,
2525 rstatus,
2526 dphase,
2527 dstatus,
2528 message);
2529 IF l_call_status = FALSE THEN
2530 errbuf := 'Can not wait for the status of '||
2531 'IPAC Bulk File Exception Report';
2532 retcode := '2';
2533
2534 END IF;
2535 EXCEPTION
2536 WHEN OTHERS THEN
2537 ROLLBACK;
2538 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2539 ' -- Error IN IPAC Bulk Exception process: Procedure:- create_bulk_file');
2540
2541 IF errcode IS NULL THEN
2542 errcode := SQLCODE;
2543 errmsg := SQLERRM;
2544 END IF;
2545 retcode := errcode;
2546 errbuf := errmsg;
2547
2548 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2549 'Errbuf :'||errbuf);
2550 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2551 'retcode :'||retcode);
2552 END;
2553 EXCEPTION
2554 WHEN OTHERS THEN
2555 IF errcode IS NULL THEN
2556 errcode := SQLCODE;
2557 errmsg := SQLERRM;
2558 END IF;
2559 retcode := errcode;
2560 errbuf := errmsg;
2561 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2562 'Errbuf :'||errbuf);
2563 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2564 'retcode :'||retcode);
2565
2566 END;
2567
2568 -- -----------------------------------------------------------------------------
2569 -- -----------------------------------------------------------------------------
2570
2571 PROCEDURE create_bulk_file(errbuf OUT NOCOPY VARCHAR2,
2572 retcode OUT NOCOPY VARCHAR2,
2573 p_org_id IN NUMBER,
2574 p_sender_alc IN VARCHAR2)
2575 IS
2576 l_count_bulk_exception NUMBER;
2577 l_req_id NUMBER;
2578 l_call_status BOOLEAN;
2579 rphase VARCHAR2(30);
2580 rstatus VARCHAR2(30);
2581 dphase VARCHAR2(30);
2582 dstatus VARCHAR2(30);
2583 message VARCHAR2(240);
2584 l_module_name VARCHAR2(200);
2585 l_rec_count NUMBER;
2586 BEGIN
2587 l_module_name:=g_module_name||'create_bulk_file';
2588 v_org_id :=p_org_id;
2589
2590 fv_utility.get_ledger_info(v_org_id,
2591 v_set_of_books_id,
2592 v_coa_id ,
2593 parm_currency ,
2594 p_status );
2595 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2596 'create_bulk_file begins'||l_rec_count);
2597
2598 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2599 'Org_id = '||p_org_id);
2600
2601 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2602 'v_set_of_books_id = '||v_set_of_books_id);
2603
2604
2605 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2606 'v_coa_id = '||v_coa_id);
2607
2608 SELECT count(1)
2609 INTO l_rec_count
2610 FROM fv_ipac_trx_all
2611 WHERE set_of_books_id = v_set_of_books_id
2612 AND org_id = v_org_id
2613 AND accounted_flag ='Y'
2614 AND report_flag ='Y'
2615 AND exclude_flag ='N'
2616 AND processed_flag = 'N'
2617 AND bulk_exception is null;
2618
2619 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2620 'l_rec_count = '||l_rec_count);
2621
2622 IF l_rec_count>0 then
2623 get_bal_seg_name;
2624
2625 -- Create the Header Record
2626 create_file_id_rec;
2627
2628 -- Create the Batch Header Record
2629 create_batch_header(v_set_of_books_id, p_sender_alc);
2630
2631 FOR hdr_det_rec IN hdr_det(v_set_of_books_id, v_org_id)
2632 LOOP
2633
2634 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2635 'Creating trx header for customer_trx_id = '||hdr_det_rec.customer_trx_id);
2636
2637 -- Create the Transaction Header Record
2638 create_trx_header(v_set_of_books_id,
2639 hdr_det_rec.customer_trx_id);
2640 -- IPAC FY2003 -04
2641 -------------------------------------------------------
2642 FOR trx_detail_rec IN trx_detail_cur(hdr_det_rec.customer_trx_id)
2643 LOOP
2644 -- Create the detail Header Record
2645 create_trx_dtl_rec(trx_detail_rec);
2646 -- Create the USSGL record
2647 create_ussgl_rec(trx_detail_rec.customer_trx_id,
2648 trx_detail_rec.trx_line_no,
2649 trx_detail_rec.snd_app_sym );
2650 END LOOP;
2651 ------------------------------------------------------
2652 END LOOP;
2653 -- In case the process of create_bulk_file is done
2654 -- successfully set the processed flag to 'Y'
2655 UPDATE fv_ipac_trx_all
2656 SET processed_flag = 'Y'
2657 WHERE set_of_books_id = v_set_of_books_id
2658 AND org_id = v_org_id
2659 AND bulk_exception IS NULL
2660 AND cash_receipt_id IS NOT NULL
2661 AND accounted_flag = 'Y'
2662 AND exclude_flag = 'N'
2663 AND report_flag = 'Y';
2664 else
2665 retcode:=1;
2666 errbuf := 'No Records Found for Bulk File Report.';
2667 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2668 'No Records Found for Bulk File Report.');
2669
2670 return;
2671 end if;
2672 EXCEPTION
2673 WHEN OTHERS THEN
2674 ROLLBACK;
2675 IF errcode IS NULL THEN
2676 errcode := SQLCODE;
2677 errmsg := SQLERRM;
2678 END IF;
2679 retcode := errcode;
2680 errbuf := errmsg;
2681 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2682 'Errbuf :'||errbuf);
2683 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2684 'retcode :'||retcode);
2685 END ; -- create_bulk_file;
2686
2687 -- -----------------------------------------------------------------------------
2688 -- -----------------------------------------------------------------------------
2689
2690 PROCEDURE create_flat_file(p_statement VARCHAR2,
2691 p_set_of_books_id NUMBER,
2692 p_customer_trx_id NUMBER)
2693 AS
2694 v_cursor_id INTEGER;
2695 l_fetch_count INTEGER;
2696 col1 VARCHAR2(2000);
2697 retcode NUMBER;
2698 errbuf VARCHAR2(200);
2699 l_module_name VARCHAR2(200) ;
2700 BEGIN
2701 l_module_name := g_module_name || 'create_flat_file';
2702 BEGIN
2703 v_cursor_id := DBMS_SQL.OPEN_CURSOR;
2704 EXCEPTION
2705 WHEN OTHERS THEN
2706 errbuf := SQLERRM;
2707 retcode := SQLCODE;
2708 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, errbuf);
2709 RAISE ;
2710 RETURN;
2711 END;
2712 BEGIN
2713 DBMS_SQL.PARSE(v_cursor_id, p_statement, DBMS_SQL.V7);
2714 DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':b_set_of_books_id',
2715 p_set_of_books_id);
2716 DBMS_SQL.BIND_VARIABLE(v_cursor_id, ':b_customer_trx_id',
2717 p_customer_trx_id);
2718 EXCEPTION
2719 WHEN OTHERS THEN
2720 retcode := SQLCODE ;
2721 errbuf := SQLERRM ;
2722 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,
2723 l_module_name, errbuf) ;
2724 RAISE ;
2725 END ;
2726 DBMS_SQL.DEFINE_COLUMN(v_cursor_id, 1, col1, 2000);
2727 BEGIN
2728 l_fetch_count := DBMS_SQL.EXECUTE(v_cursor_id);
2729 EXCEPTION
2730 WHEN OTHERS THEN
2731 retcode := SQLCODE ;
2732 errbuf := SQLERRM ;
2733 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2734 'Create Sql ');
2735 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
2736 errbuf) ;
2737 RAISE ;
2738 END;
2739 LOOP
2740 l_fetch_count := DBMS_SQL.FETCH_ROWS(v_cursor_id);
2741 IF l_fetch_count = 0 THEN
2742 RETURN;
2743 END IF;
2744 DBMS_SQL.COLUMN_VALUE(v_cursor_id, 1, col1);
2745 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, col1);
2746 END LOOP;
2747 EXCEPTION
2748 WHEN OTHERS THEN
2749 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2750 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED,
2751 l_module_name, errmsg);
2752 END IF;
2753 RAISE;
2754 END;
2755
2756 -- -----------------------------------------------------------------------------
2757 -- -----------------------------------------------------------------------------
2758
2759 BEGIN
2760 v_org_id := mo_global.get_current_org_id;
2761
2762 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2763 'fv.plsql.fv_ipac','Org Id : '||v_org_id);
2764
2765 fv_utility.get_ledger_info(v_org_id,
2766 v_set_of_books_id,
2767 v_coa_id ,
2768 parm_currency ,
2769 p_status );
2770
2771 g_module_name := 'fv.plsql.fv_ipac.';
2772 flex_code := 'GL#';
2773 apps_id := 101;
2774 trx_exception_flag := 'N';
2775 --New JFMIP REQUIREMENT pay flag should be P
2776 --v_pay_flag := 'F';
2777 v_pay_flag := 'P';
2778 --New JFMIP REQUIREMENT ENDS
2779 ---l_module_name := g_module_name || 'delete_records';
2780 END; -- package body