[Home] [Help]
PACKAGE BODY: APPS.FV_AP_CASH_POS_DTL_PKG
Source
1 PACKAGE BODY FV_AP_CASH_POS_DTL_PKG AS
2 --$Header: FVAPCPDB.pls 120.14 2006/12/12 09:27:37 bnarang ship $
3
4 g_module_name VARCHAR2(100) ;
5
6 /********** Global Variable Definitions **********/
7 g_bal_segment_num NUMBER(15);
8 g_acct_segment_num NUMBER(15);
9 g_bal_segment_name VARCHAR2(30);
10 g_acct_segment_name VARCHAR2(30);
11 g_check_date DATE;
12 g_set_of_books_id gl_ledgers_public_v.ledger_id %TYPE;
13 g_set_of_books_name gl_ledgers_public_v.name%type;
14 g_flex_num gl_code_combinations.chart_of_accounts_id%TYPE;
15 g_segment_value gl_code_combinations.segment1%TYPE;
16 g_error_code NUMBER(15);
17 g_error_buf VARCHAR2(500);
18 g_error_stage NUMBER(15);
19 g_checkrun_name ap_selected_invoices_all.checkrun_name%TYPE;
20 g_checkrun_id ap_selected_invoices_all.checkrun_id%TYPE;
21 g_apps_id NUMBER := 101;
22 g_flex_code VARCHAR2(25) ;
23 g_org_id NUMBER(15);
24 Invalid_segment EXCEPTION;
25 /*------------------------------------------------------------------
26 -- g_pooled_flag ap_bank_accounts.pooled_flag%TYPE;
27 g_pooled_flag ce_bank_accounts.pooled_flag%TYPE;
28 -- g_bank_acct_name ap_bank_accounts.bank_account_name%TYPE;
29 g_bank_acct_name ce_bank_accounts.bank_account_name%TYPE;
30 --------------------------------------------------------------------*/
31 g_value_set_id fnd_flex_values.flex_value_set_id%TYPE;
32 g_auto_offset_method ap_system_parameters.liability_post_lookup_code%TYPE;
33
34 /********** ********/
35 /********** PROCEDURE: MAIN ********/
36 /********** ********/
37 /** This Procedure calls the remaining procedures ********/
38
39 PROCEDURE MAIN (errbuf OUT NOCOPY VARCHAR2,
40 retcode OUT NOCOPY NUMBER,
41 p_payment_batch IN NUMBER,
42 p_org_id IN NUMBER) IS
43 l_module_name VARCHAR2(200) ;
44 req_id NUMBER;
45
46 BEGIN
47
48 g_checkrun_id := p_payment_batch;
49 /*------Commented out as org_id is passed in as parameter-----------------
50 g_set_of_books_id := TO_NUMBER(fnd_profile.value('GL_SET_OF_BKS_ID'));
51 g_org_id := TO_NUMBER(fnd_profile.value('ORG_ID'));
52 ------------End of comments---------------------------------------------*/
53 if (p_org_id is not null) then
54 g_org_id := p_org_id;
55 else
56 select org_id
57 into g_org_id
58 from ap_selected_invoices_all
59 where checkrun_id = g_checkrun_id
60 and rownum < 2;
61 end if;
62
63 mo_utils.Get_Ledger_Info
64 ( p_operating_unit => g_org_id
65 , p_ledger_id => g_set_of_books_id
66 , p_ledger_name => g_set_of_books_name);
67
68 l_module_name := g_module_name || 'MAIN';
69
70 DELETE FROM fv_ap_cash_pos_temp
71 WHERE checkrun_id = g_checkrun_id
72 AND set_of_books_id = g_set_of_books_id
73 AND org_id = g_org_id;
74
75 INITIALIZE_PROCESS;
76 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
77 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INITIALIZED PROCESS');
78 END IF;
79
80 IF g_error_code <> 0 THEN
81 retcode := g_error_code;
82 errbuf := g_error_buf;
83 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',g_error_buf);
84 RETURN;
85 END IF;
86
87 CREATE_CASH_POSITION_RECORD;
88 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
89 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CREATE_CASH_POSITION_RECORD');
90 END IF;
91
92 IF g_error_code <> 0 THEN
93 retcode := g_error_code;
94 errbuf := g_error_buf;
95 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error2',g_error_buf);
96 RETURN;
97 END IF;
98 COMMIT;
99
100 -- Added the below IF for the Bug # 2521634
101
102 IF g_auto_offset_method = 'None' THEN
103 retcode := 1;
104 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,' ');
105 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'The Automatic Offset option is set to None.');
106 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'It should be either Balancing or Account.');
107 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Please note that the report'||''''||'s G/L Cash Balance is determined by the');
108 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'funds of the invoices, while journal entries for the payments will be posted');
109 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'to the cash account as defined for the bank account.');
110 /*---------------------------comments begin-------------------------------------
111 ELSIF g_pooled_flag = 'Y' THEN
112 retcode := 0;
113 errbuf := '** Cash Position Detail Process completed successfully ** ';
114 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
115 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,errbuf);
116 END IF;
117 ------------------------------------------------------------------------------*/
118 END IF;
119
120 /*-------------------------------comments start---------------------------------
121 IF g_pooled_flag = 'N' THEN
122 retcode := 1;
123 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,' ');
124 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'The pooled flag is not selected on the bank account '||g_bank_acct_name||'.');
125 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'The G/L Cash Balance is determined by the funds of the invoices.');
126 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Please note that the journal entry for the payments will be posted');
127 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'to the fund of the cash account as defined on the bank account.');
128 END IF;
129 -------------------------------end of comments--------------------------------*/
130
131 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,' ');
132 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Please Note: It is assumed that payments are made using pooled bank accounts.');
133 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'If pooled accounts are not used, liabilities and payments are not properly distrubuted across multiple funds.');
134
135 -- make sure to pass g_org_id (not p_org_id) - p_org_id will most probably be NULL
136 req_id := FND_REQUEST.SUBMIT_REQUEST('FV', 'FVAPCPDR',NULL,
137 NULL, FALSE,p_payment_batch,g_org_id);
138
139 IF (req_id = 0) THEN
140 retcode := 2;
141 errbuf := 'Failed to submit request for Cash Position Detail Report .';
142 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,errbuf);
143 END IF;
144 IF (retcode <> 0 AND retcode = -1) THEN
145 --changed checkrun_name to checkrun_id
146 DELETE FROM fv_ap_cash_pos_temp
147 WHERE checkrun_id = g_checkrun_id
148 AND set_of_books_id = g_set_of_books_id
149 AND org_id = g_org_id;
150 END IF;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 retcode := 2;
155 errbuf := SQLERRM;
156 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
157 END MAIN;
158
159 /********** **********/
160 /********** PROCEDURE: Initialize_Process **********/
161 /********** **********/
162 /********** This procedure validates all the validations **********/
163
164 PROCEDURE Initialize_Process IS
165
166 l_module_name VARCHAR2(200) ;
167
168 CURSOR flex_value_set_cur(p_acct_segment_num NUMBER,
169 p_apps_id NUMBER,
170 p_flex_code VARCHAR2,
171 p_flex_num NUMBER ) IS
172 SELECT flex_value_set_id
173 FROM fnd_id_flex_segments
174 WHERE application_id = p_apps_id
175 AND id_flex_code = p_flex_code
176 AND id_flex_num = p_flex_num
177 AND segment_num = p_acct_segment_num ;
178
179 CURSOR cash_position_accounts_cur IS
180 SELECT ussgl_account
181 FROM fv_facts_ussgl_accounts
182 WHERE cash_position_flag = 'Y'
183 AND ussgl_enabled_flag = 'Y';
184
185 CURSOR parent_child_rollups_cur(p_acct VARCHAR2,p_value_set_id NUMBER) IS
186 SELECT 1
187 FROM fnd_flex_values
188 WHERE flex_value_set_id = p_value_set_id
189 AND flex_value = p_acct ;
190 --changed checkrun_name to checkrun_id
191 CURSOR check_date_cur(p_checkrun_id NUMBER) IS
192 SELECT check_date,checkrun_name
193 FROM ap_inv_selection_criteria_all
194 WHERE checkrun_id = p_checkrun_id;
195
196 --changed checkrun_name to checkrun_id
197 CURSOR invoice_count_cur(p_checkrun_id NUMBER) IS
198 SELECT count(asi.invoice_num||asi.vendor_id||asi.vendor_site_id) count
199 FROM ap_selected_invoices_all asi
200 WHERE asi.checkrun_id = p_checkrun_id;
201
202 /*---------comments-----------------------------------------------
203 CURSOR bank_acct_cur(p_checkrun_id NUMBER) IS
204 SELECT cba.pooled_flag, cba.bank_account_name
205 FROM ce_bank_accounts cba ,
206 Ce_bank_acct_uses_all cbaua,
207 ap_inv_selection_criteria_all apis
208 WHERE apis.checkrun_id = p_checkrun_id
209 ---;)-- AND apis.bank_account_id = cbaua.bank_account_id
210 AND apis.ce_bank_acount_use_id = cbaua.bank_account_id
211 AND cbaua.bank_account_id = cba.bank_account_id;
212 ----------------end of comments---------------------------------*/
213
214 -- Added the below cursor for the Bug # 2521634
215
216 CURSOR automatic_offset_method_cur IS
217 SELECT NVL(liability_post_lookup_code ,'None')
218 FROM ap_system_parameters;
219
220 l_record_count NUMBER;
221 l_count NUMBER;
222 l_cash_ussgl_acct fv_facts_ussgl_accounts.ussgl_account%TYPE;
223 l_parent_rollups VARCHAR2(1);
224
225 BEGIN
226 l_module_name := g_module_name || 'Initialize_Process';
227 g_error_stage := 1;
228 g_error_code := 0;
229 g_error_buf := Null;
230
231
232 -- get the check date for the payment batch
233
234 OPEN check_date_cur(g_checkrun_id);
235 FETCH check_date_cur INTO g_check_date,g_checkrun_name;
236 CLOSE check_date_cur;
237
238 -- get the count of the invoices selected for the payment batch
239
240 OPEN invoice_count_cur(g_checkrun_id);
241 FETCH invoice_count_cur INTO l_record_count;
242 CLOSE invoice_count_cur;
243
244 IF l_record_count = 0 THEN
245 g_error_code := -1;
246 g_error_buf := 'No Invoices were selected for this Payment Batch.';
247 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
248 RETURN;
249 END IF;
250
251 -- get the GL BALANCING and GL ACCOUNT segment numbers
252
253 get_segment_num;
254
255 -- get Automatic offset method from AP
256
257 OPEN automatic_offset_method_cur;
258 FETCH automatic_offset_method_cur INTO g_auto_offset_method;
259 CLOSE automatic_offset_method_cur;
260
261 /*----------------start comments---------------------------------
262 -- get the bank account details
263 OPEN bank_acct_cur(g_checkrun_id);
264 FETCH bank_acct_cur INTO g_pooled_flag, g_bank_acct_name;
265 CLOSE bank_acct_cur;
266 -------------------end comments---------------------------------*/
267
268 -- Get the value set attached to the accounting segment
269
270 OPEN flex_value_set_cur(g_acct_segment_num,
271 g_apps_id ,
272 g_flex_code,
273 g_flex_num);
274 FETCH flex_value_set_cur INTO g_value_set_id;
275 CLOSE flex_value_set_cur;
276
277 l_record_count := 0;
278 l_count := 0;
279
280 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
281 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'The USSGL Accounts identified as Cash Accounts are :');
282 END IF;
283
284
285 OPEN cash_position_accounts_cur ;
286 LOOP
287 FETCH cash_position_accounts_cur INTO l_cash_ussgl_acct ;
288 EXIT WHEN cash_position_accounts_cur%NOTFOUND;
289
290 -- get parent/child roll ups
291
292 OPEN parent_child_rollups_cur(l_cash_ussgl_acct,
293 g_value_set_id);
294 FETCH parent_child_rollups_cur INTO l_record_count;
295 CLOSE parent_child_rollups_cur;
296
297 IF l_record_count = 1 THEN
298 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
299 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_cash_ussgl_acct);
300 END IF;
301 l_record_count := 2 ;
302 END IF;
303 l_count := 1;
304
305 END LOOP;
306
307 CLOSE cash_position_accounts_cur ;
308
309 IF ( l_record_count = 0 ) THEN
310 g_error_code := -1;
311 g_error_buf := 'Please define US SGL Cash Position Accounts in General Ledger.';
312 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
313 RETURN;
314 END IF ;
315
316 IF l_count = 0 THEN
317 g_error_code := -1;
318 g_error_buf := 'Please define Cash Position Accounts in Define USSGL Accounts screen. ';
319 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
320 RETURN;
321 END IF ;
322
323
324 EXCEPTION
325
326 WHEN OTHERS THEN
327 g_error_code := -1;
328 g_error_buf := to_char(sqlcode) ||' '|| sqlerrm||' [Intialize Process]';
329 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
330 ROLLBACK;
331 RETURN;
332
333 END Initialize_Process;
334
335 /********** **********/
336 /********** PROCEDURE: Create_Cash_Position_Record **********/
337 /********** **********/
338 /********** This procedure pulls all the invoice distributions **********/
339 /********** and caluculates the gl balances for the funds. **********/
340 /********** **********/
341
342 PROCEDURE Create_Cash_Position_Record IS
343
344 l_module_name VARCHAR2(200) ;
345
346 CURSOR period_detail_cur(p_checkrun_date DATE,
347 p_apps_id NUMBER,
348 p_set_of_books_id NUMBER) IS
349 SELECT gps.period_num,gps.period_year,glpv.currency_CODE
350 FROM gl_period_statuses gps ,gl_ledgers_public_v glpv
351 WHERE Start_date <= p_checkrun_date
352 AND end_date >= p_checkrun_date
353 AND gps.application_id = p_apps_id
354 AND gps.set_of_books_id =p_set_of_books_id
355 AND gps.set_of_books_id = glpv.ledger_id
356 AND gps.adjustment_period_flag='N';
357
358
359 CURSOR cash_position_acct_cur(p_value_set NUMBER) IS
360 SELECT ussgl_account
361 FROM fv_facts_ussgl_accounts,fnd_flex_values
362 WHERE flex_value_set_id = p_value_set
363 AND flex_value = ussgl_account
364 AND cash_position_flag = 'Y'
365 AND ussgl_enabled_flag = 'Y';
366
367 --Modified the invoice cursor to include vendor_id,vendor_site_id and amount
368 -- formula to fix 2498036 Bug.
369 CURSOR invoice_cursor(p_checkrun_id NUMBER) IS
370 SELECT asi.invoice_num,
371 asi.invoice_date,
372 apd.distribution_line_number,
373 apd.dist_code_combination_id dist_code_combination_id,
374 ((asi.payment_amount) *(apd.amount/asi.invoice_amount)) amount,
375 asi.vendor_id,
376 asi.vendor_site_id
377 FROM ap_selected_invoices asi,
378 ap_invoice_distributions apd
379 WHERE asi.checkrun_id = p_checkrun_id
380 AND apd.invoice_id = asi.invoice_id
381 ORDER by asi.invoice_num;
382
383
384 CURSOR fund_exist_cur(p_fund VARCHAR2,p_checkrun_id NUMBER,
385 p_sob_id NUMBER,p_org_id NUMBER) IS
386 SELECT fund,gl_cash_balance
387 FROM fv_ap_cash_pos_temp
388 WHERE checkrun_id = p_checkrun_id
389 AND fund = p_fund
390 AND set_of_books_id = p_sob_id
391 AND org_id = p_org_id
392 AND rownum = 1;
393
394
395 vcheck_rec invoice_cursor%ROWTYPE;
396 l_acct_dist_tbl Fnd_Flex_Ext.segmentarray ;
397 l_get_segments_flag BOOLEAN;
398 l_period_num gl_balances.period_num%TYPE;
399 l_currency_code gl_balances.currency_code%TYPE;
400 l_period_year gl_balances.period_year%TYPE ;
401 l_boolean BOOLEAN;
402 l_num_segments NUMBER;
403 l_value_set_id fnd_flex_values.flex_value_set_id%TYPE;
404 l_gl_cash_balance NUMBER;
405 l_cash_ussgl_acct fv_facts_ussgl_accounts.ussgl_account%TYPE;
406 l_bal_cursor INTEGER ;
407 l_bal_select VARCHAR2(5000) ;
408 l_exec_ret INTEGER ;
409 l_tot_cash_balance NUMBER;
410 l_fund_exist VARCHAR2(80);
411 l_fund_gl_balance NUMBER;
412 l_counter NUMBER :=0;
413
414 BEGIN
415 l_module_name := g_module_name || 'Create_Cash_Position_Record';
416
417 g_error_stage := 2;
418
419 OPEN period_detail_cur(g_check_date,
420 g_apps_id,
421 g_set_of_books_id);
422 FETCH period_detail_cur INTO l_period_num,l_period_year,l_currency_code;
423 CLOSE period_detail_cur;
424
425 OPEN invoice_cursor(g_checkrun_id);
426 LOOP
427 FETCH invoice_cursor INTO vcheck_rec;
428 EXIT WHEN invoice_cursor%NOTFOUND;
429
430 l_get_segments_flag :=
431 fnd_flex_ext.get_segments
432 (application_short_name => 'SQLGL',
433 key_flex_code => g_flex_code,
434 structure_number => g_flex_num,
435 combination_id => vcheck_rec.dist_code_combination_id,
436 n_segments => l_num_segments,
437 segments => l_acct_dist_tbl) ;
438
439
440 l_tot_cash_balance :=0;
441 l_fund_exist := NULL;
442
443 OPEN fund_exist_cur(l_acct_dist_tbl(g_bal_segment_num),
444 g_checkrun_id,
445 g_set_of_books_id,
446 g_org_id);
447 FETCH fund_exist_cur INTO l_fund_exist,l_fund_gl_balance;
448 CLOSE fund_exist_cur;
449
450
451 IF l_fund_exist IS NULL THEN
452
453
454 OPEN cash_position_acct_cur(g_value_set_id);
455 LOOP
456 FETCH cash_position_acct_cur INTO l_cash_ussgl_acct ;
457 EXIT WHEN cash_position_acct_cur%NOTFOUND;
458
459 BEGIN
460 l_bal_cursor := DBMS_SQL.OPEN_CURSOR;
461
462 EXCEPTION
463 WHEN OTHERS THEN
464 g_error_code := sqlcode ;
465 g_error_buf := sqlerrm || ' [CALC_BALANCE - Open Cursor] ' ;
466 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception2',g_error_buf);
467 RETURN;
468 END ;
469
470 l_bal_select :=
471 'SELECT SUM((GLB.BEGIN_BALANCE_DR -
472 GLB.BEGIN_BALANCE_CR) + (GLB.PERIOD_NET_DR - PERIOD_NET_CR )) '||
473 ' FROM GL_BALANCES GLB,' ||
474 ' GL_CODE_COMBINATIONS GLCC' ||
475 ' WHERE GLB.code_combination_id = GLCC.code_combination_id ' ||
476 ' AND '||'glb.actual_flag = '|| '''' || 'A' || '''' ||
477 ' AND GLCC.'|| g_bal_segment_name ||' = '||'''' || l_acct_dist_tbl(g_bal_segment_num) ||''''||
478 ' AND ((GLCC.' ||g_acct_segment_name ||' = :l_cash_ussgl_acct ' ||
479 ' ) OR GLCC.'||g_acct_segment_name ||' IN (SELECT flex_value '||
480 'FROM fnd_flex_values ffv, fnd_flex_value_hierarchies ffvh '||
481 'WHERE ffv.flex_value BETWEEN ffvh.child_flex_value_low
482 AND ffvh.child_flex_value_high
483 AND ffv.flex_value_set_id = :g_value_set_id AND ffv.flex_value_set_id = ffvh.flex_value_set_id'||
484 ' AND parent_flex_value = :l_cash_ussgl_acct)) AND GLB.period_year = :l_period_year AND GLB.period_num = :l_period_num AND GLB.LEDGER_ID = :g_set_of_books_id AND glb.currency_code = :l_currency_code';
485
486 IBY_PAYMENT_FORMAT_VAL_PVT.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Dynamic SQL Statement');
487 IBY_PAYMENT_FORMAT_VAL_PVT.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_bal_select);
488 IBY_PAYMENT_FORMAT_VAL_PVT.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, ':l_cash_ussgl_acct = ' || l_cash_ussgl_acct);
489 IBY_PAYMENT_FORMAT_VAL_PVT.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, ':l_period_year = ' || l_period_year);
490 IBY_PAYMENT_FORMAT_VAL_PVT.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, ':l_period_num = ' || l_period_num);
491 IBY_PAYMENT_FORMAT_VAL_PVT.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, ':g_set_of_books_id = ' || g_set_of_books_id);
492 IBY_PAYMENT_FORMAT_VAL_PVT.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, ':l_currency_code = ' || l_currency_code);
493 IBY_PAYMENT_FORMAT_VAL_PVT.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, ':g_value_set_id = ' || g_value_set_id);
494
495
496
497
498 BEGIN
499 DBMS_SQL.PARSE(l_bal_cursor, l_bal_select, DBMS_SQL.V7) ;
500 DBMS_SQL.BIND_VARIABLE(l_bal_cursor,':l_cash_ussgl_acct',l_cash_ussgl_acct);
501 DBMS_SQL.BIND_VARIABLE(l_bal_cursor,':l_period_year',l_period_year);
502 DBMS_SQL.BIND_VARIABLE(l_bal_cursor,':l_period_num',l_period_num);
503 DBMS_SQL.BIND_VARIABLE(l_bal_cursor,':g_set_of_books_id',g_set_of_books_id);
504 DBMS_SQL.BIND_VARIABLE(l_bal_cursor,':l_currency_code',l_currency_code);
505 DBMS_SQL.BIND_VARIABLE(l_bal_cursor,':g_value_set_id',g_value_set_id);
506
507 DBMS_SQL.DEFINE_COLUMN(l_bal_cursor, 1, l_gl_cash_balance);
508
509 BEGIN
510 l_exec_ret := DBMS_SQL.EXECUTE(l_bal_cursor);
511
512 EXCEPTION
513 WHEN OTHERS THEN
514 g_error_code := sqlcode ;
515 g_error_buf := sqlerrm || 'Create_cash_position - Execute Cursor]' ;
516 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
517 RETURN ;
518 END ;
519
520 IF dbms_sql.fetch_rows(l_bal_cursor) = 0 THEN
521 EXIT;
522 ELSE
523 -- Fetch the Records into Variables
524 DBMS_SQL.COLUMN_VALUE(l_bal_cursor, 1, l_gl_cash_balance);
525 l_tot_cash_balance := NVL(l_gl_cash_balance,0) + l_tot_cash_balance;
526 END IF;
527
528
529 -- Close the SQL Cursor
530 BEGIN
531 DBMS_SQL.CLOSE_CURSOR(l_bal_cursor);
532 EXCEPTION
533 WHEN OTHERS THEN
534 g_error_code := sqlcode ;
535 g_error_buf := sqlerrm ||' Create_cash_position - Close Cursor]' ;
536 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_A',g_error_buf);
537 RETURN ;
538 END ;
539
540 EXCEPTION
541 WHEN OTHERS THEN
542 g_error_code := sqlcode ;
543 g_error_buf := sqlerrm ||' Create_cash_position';
544 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_B',g_error_buf);
545 RETURN ;
546 END ;
547 END LOOP;
548
549 CLOSE cash_position_acct_cur;
550 ELSE
551 l_tot_cash_balance := l_fund_gl_balance;
552 END IF;
553 --Modified the following statement to include vendor_id ,vendor_site_id
554 --and amount formula to fix 2498036 bug.
555 INSERT INTO fv_ap_cash_pos_temp
556 (checkrun_id,
557 checkrun_name, --bug 5564904
558 check_date,
559 fund,
560 invoice_num,
561 invoice_date,
562 distribution_line_number,
563 amount,
564 vendor_id,
565 vendor_site_id,
566 gl_cash_balance,
567 set_of_books_id,
568 org_id )
569 VALUES
570 (g_checkrun_id,
571 g_checkrun_name, --bug 5564904
572 g_check_date,
573 l_acct_dist_tbl(g_bal_segment_num),
574 vcheck_rec.invoice_num,
575 vcheck_rec.invoice_date,
576 vcheck_rec.distribution_line_number,
577 vcheck_rec.amount,
578 vcheck_rec.vendor_id,
579 vcheck_rec.vendor_site_id,
580 l_tot_cash_balance,
581 g_set_of_books_id,
582 g_org_id);
583 l_counter := l_counter + 1;
584 IF l_counter > 100 THEN
585 COMMIT;
586 l_counter := 0;
587 END IF;
588
589 END LOOP ; /* VCheck */
590 CLOSE invoice_cursor;
591 COMMIT;
592 EXCEPTION
593 WHEN invalid_segment THEN
594 g_error_code := -1;
595 g_error_buf := 'Unable to determine the segments [Create_Cash_Position_Record] ';
596 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
597 ROLLBACK;
598 RETURN;
599 WHEN OTHERS THEN
600 g_error_code := -1;
601 g_error_buf := to_char(sqlcode)||' '||sqlerrm||' [Create_Cash_Position_Record]';
602 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
603 ROLLBACK;
604 RETURN;
605 END Create_Cash_Position_Record;
606
607 /********** **********/
608 /********** PROCEDURE: get_segment_num **********/
609 /********** **********/
610 /********** This procedure gets the balancing segment and **********/
611 /********** accounting segment numbers of the flex field **********/
612
613 Procedure get_segment_num is
614
615 l_module_name VARCHAR2(200) ;
616
617 CURSOR chart_acct_id_cur(p_set_of_books_id NUMBER) IS
618 SELECT chart_of_accounts_id
619 FROM gl_ledgers_public_v
620 WHERE ledger_id = p_set_of_books_id;
621
622 l_num_boolean boolean;
623 l_seg_number Number ;
624 l_seg_app_name Varchar2(40) ;
625 l_seg_prompt Varchar2(25) ;
626 l_seg_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
627
628 BEGIN
629 l_module_name := g_module_name || 'get_segment_num';
630
631 OPEN chart_acct_id_cur(g_set_of_books_id);
632 FETCH chart_acct_id_cur INTO g_flex_num ;
633 CLOSE chart_acct_id_cur;
634 l_num_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
635 (g_apps_id,
636 g_flex_code,
637 g_flex_num ,
638 'GL_BALANCING',
639 g_bal_segment_num);
640
641 IF(l_num_boolean) THEN
642 l_num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
643 (g_apps_id,
644 g_flex_code,
645 g_flex_num,
646 g_bal_segment_num,
647 g_bal_segment_name,
648 l_seg_app_name,
649 l_seg_prompt,
650 l_seg_value_set_name);
651 ELSE
652 RAISE invalid_segment;
653 END IF;
654
655
656
657 IF l_num_boolean = FALSE THEN
658 RAISE invalid_segment;
659 END IF ;
660
661 l_num_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
662 (g_apps_id,
663 g_flex_code,
664 g_flex_num,
665 'GL_ACCOUNT',
666 g_acct_segment_num) ;
667
668
669 IF(l_num_boolean) then
670 l_num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
671 (g_apps_id,
672 g_flex_code,
673 g_flex_num,
674 g_acct_segment_num,
675 g_acct_segment_name,
676 l_seg_app_name,
677 l_seg_prompt,
678 l_seg_value_set_name);
679 ELSE
680 RAISE invalid_segment;
681
682 END IF;
683
684
685 IF l_num_boolean = FALSE THEN
686 RAISE invalid_segment;
687 END IF;
688
689 EXCEPTION
690 WHEN invalid_segment THEN
691 g_error_code := -1;
692 g_error_buf := 'Unable to determine the Segment Information [get_segment_num]';
693 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception1',g_error_buf);
694 ROLLBACK;
695 RETURN;
696 WHEN OTHERS THEN
697 g_error_code := -1;
698 g_error_buf := to_char(sqlcode)||' '||sqlerrm||' [get_segment_num]';
699 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
700 ROLLBACK;
701 RETURN;
702 END get_segment_num;
703 Begin
704 g_flex_code := 'GL#';
705 g_module_name := 'fv.plsql.FV_AP_CASH_POS_DTL_PKG.';
706
707
708 END fv_ap_cash_pos_dtl_pkg;