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