[Home] [Help]
Skip to content
PACKAGE BODY: APPS.FUN_PERIOD_STATUS_PKG
Source
1 PACKAGE BODY FUN_PERIOD_STATUS_PKG AS
2 /* $Header: funprdstsb.pls 120.34 2011/03/28 13:20:36 srsampat ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := ' FUN_PERIOD_STATUS_PKG';
4 G_DEBUG VARCHAR2(1);
5 PROCEDURE Print
6 (
7 P_string IN VARCHAR2
8 ) IS
9 BEGIN
10 IF G_DEBUG = 'Y' THEN
11 fnd_file.put_line(FND_FILE.LOG, p_string);
12 END IF;
13 END Print;
14 /***********************************************
15 * Procedure Close_Period :
16 * This Procedure Closes specified Intercompany Period *
17 * or all the periods. It checks whether there are any Open Intercompany *
18 * Transactions. If Yes, then user can sweep the transactions to next open *
19 * period before closing the Period. *
20 ***************************************************/
21 PROCEDURE Close_Period
22 (
23 p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 ,
25 p_commit IN VARCHAR2 ,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_message_count OUT NOCOPY NUMBER,
28 x_message_data OUT NOCOPY VARCHAR2,
29 p_period_name IN VARCHAR2,
30 p_trx_type_id IN NUMBER,
31 p_sweep IN VARCHAR2,
32 p_sweep_GL_date IN DATE,
33 x_request_id OUT NOCOPY NUMBER
34 ) IS
35 Cursor c_open_trx1(l_prd_name in Varchar2,l_trx_type_id in Number) is
36 Select 1 from dual where exists
37 (Select 'X' from fun_trx_batches ftb, fun_trx_headers fth,
38 fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
39 ftb.gl_date >= fps.start_date and ftb.gl_date <=
40 fps.end_date and fps.period_name = l_prd_name and
41 fps.trx_type_id = l_trx_type_id and ftb.trx_type_id = l_trx_type_id and
42 ftb.batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2
43 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
44 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED')
45 AND h2.batch_id = ftb.batch_id) -- Bug No : 6880343
46 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
47 AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
48 Cursor c_open_trx2(l_prd_name in Varchar2) is
49 Select 1 from dual where exists
50 (Select 'X' from fun_trx_batches ftb, fun_trx_headers fth,
51 fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
52 ftb.gl_date >= fps.start_date and ftb.gl_date <=
53 fps.end_date and fps.period_name = l_prd_name
54 and ftb.trx_type_id = fps.trx_type_id and
55 ftb.batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2
56 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
57 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED')
58 AND h2.batch_id = ftb.batch_id) -- Bug No : 6880343
59 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
60 AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
61 Cursor c_open_prd1(l_prd_name in Varchar2, l_trx_type_id in
62 Number) is
63 Select 1 from dual where exists
64 (Select 'X' from fun_period_statuses fps,fun_system_options fso where fps.status = 'O'
65 and trx_type_id = l_trx_type_id and period_name = l_prd_name
66 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
67 AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
68 Cursor c_open_prd2(l_prd_name in Varchar2) is
69 Select 1 from dual where exists
70 (Select 'X' from fun_period_statuses fps,fun_system_options fso where fps.status = 'O'
71 and period_name = l_prd_name
72 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
73 AND fps.inteco_period_type =nvl(fso.inteco_period_type,'~~'));
74 l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_PERIOD';
75 l_api_version CONSTANT NUMBER := 1.0;
76 l_count number;
77 l_open varchar2(1) ;
78 l_val varchar2(2000);
79 l_open_prd varchar2(15);
80 BEGIN
81 SAVEPOINT Close_Period_PUB;
82 l_open :='N';
83 -- Standard Call to check for API compatibility
84 IF NOT FND_API.Compatible_API_Call (l_api_version,
85 p_api_version,
86 l_api_name,
87 G_PKG_NAME)
88 THEN
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END IF;
91 -- Initialize API return status to success
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 /*
94 Validate the validity of parameters: All Mandatory Parameters should be
95 passed, API Version
96 */
97 IF ( P_api_version IS NULL OR
98 p_sweep IS NULL OR
99 p_period_name IS NULL) THEN
100 x_message_data := 'FUN_REQUIRED_FIELDS_INCOMPLETE';
101 Raise FND_API.G_EXC_ERROR;
102 END IF;
103 /* Check if the Period Passed is Open Period */
104 if (p_trx_type_id is not null) then
105 open c_open_prd1(p_period_name,p_trx_type_id);
106 fetch c_open_prd1 into l_open_prd;
107 if (c_open_prd1%notfound) then
108 x_message_data := 'FUN_PERIOD_NOT_OPEN';
109 Raise FND_API.G_EXC_ERROR;
110 End If;
111 close c_open_prd1;
112 else
113 open c_open_prd2(p_period_name);
114 fetch c_open_prd2 into l_open_prd;
115 if (c_open_prd2%notfound) then
116 x_message_data := 'FUN_PERIOD_NOT_OPEN';
117 Raise FND_API.G_EXC_ERROR;
118 End If;
119 close c_open_prd2;
120 End if;
121 /* if p_sweep is Yes, then p_sweep_GL_Date must not
122 null */
123 If (p_sweep = 'Y') then
124 If (p_sweep_GL_Date is null) then
125 /* Put the Stack Message */
126 x_return_status := FND_API.G_RET_STS_ERROR;
127 x_message_data := 'FUN_SWEEP_GL_DATE_REQ';
128 Raise FND_API.G_EXC_ERROR;
129 End If;
130 End If;
131 /* Check for open transactions */
132 if (p_trx_type_id is not null) then
133 open c_open_trx1(p_period_name,p_trx_type_id);
134 fetch c_open_trx1 into l_count;
135 if (c_open_trx1%found) then
136 l_open := 'Y';
137 End If;
138 close c_open_trx1;
139 else
140 open c_open_trx2(p_period_name) ;
141 fetch c_open_trx2 into l_count;
142 if (c_open_trx2%found) then
143 l_open := 'Y';
144 End If;
145 close c_open_trx2;
146 End if;
147 /* Calling Sweeping Program if p_sweep is Yes */
148 if (l_open = 'Y') then
149 if (p_sweep = 'Y') then
150 /* submit the sweeping concurrent program */
151 x_request_id := FND_REQUEST.SUBMIT_REQUEST(
152 application => 'FUN',
153 program => 'FUNPRDSTSB',
154 description=>' Manual Intercompany Transactions Sweep',
155 start_time => '',
156 argument1=>'1.0',
157 argument2=> p_period_name,
158 argument3=> p_trx_type_id,
159 argument4=> p_sweep_GL_date,
160 argument5=> 'Y',
161 argument6=> 'Y'
162 );
163 Else
164 x_message_data := 'FUN_OPEN_TRXS_NOT_SWEPT';
165 Raise FND_API.G_EXC_ERROR;
166 end if; /* p_sweep */
167 Else
168 If (p_trx_type_id is not null) then
169 Update fun_period_statuses set status = 'C'
170 where trx_type_id = p_trx_type_id and
171 period_name = p_period_name
172 AND (inteco_calendar,inteco_period_type) IN
173 (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
174 Else
175 Update fun_period_statuses set status = 'C' where
176 period_name = p_period_name and status in ('O')
177 AND (inteco_calendar,inteco_period_type) IN
178 (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
179 End If;
180 End If; /* l_open */
181 /* Commit if p_commit is not passed False */
182 IF FND_API.To_Boolean(nvl(p_commit,FND_API.G_FALSE) ) THEN
183 COMMIT;
184 END IF;
185 if (c_open_trx1%isopen) then
186 close c_open_trx1;
187 end if;
188 if (c_open_trx2%isopen) then
189 close c_open_trx2;
190 end if;
191 if (c_open_prd1%isopen) then
192 close c_open_trx1;
193 end if;
194 if (c_open_prd2%isopen) then
195 close c_open_trx2;
196 end if;
197 EXCEPTION
198 WHEN FND_API.G_EXC_ERROR THEN
199 ROLLBACK TO Close_Period_PUB;
200 x_return_status := FND_API.G_RET_STS_ERROR ;
201 if (c_open_trx1%isopen) then
202 close c_open_trx1;
203 end if;
204 if (c_open_trx2%isopen) then
205 close c_open_trx2;
206 end if;
207 if (c_open_prd1%isopen) then
208 close c_open_prd1;
209 end if;
210 if (c_open_prd2%isopen) then
211 close c_open_prd2;
212 end if;
213 WHEN OTHERS THEN
214 x_message_data := SQLERRM;
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216 ROLLBACK TO Close_Period_PUB;
217 if (c_open_trx1%isopen) then
218 close c_open_trx1;
219 end if;
220 if (c_open_trx2%isopen) then
221 close c_open_trx2;
222 end if;
223 if (c_open_prd1%isopen) then
224 close c_open_prd1;
225 end if;
226 if (c_open_prd2%isopen) then
227 close c_open_prd2;
228 end if;
229 END Close_Period;
230 /***********************************************
231 * Procedure Get_Period_Status :
232 * This API Checks whether Application Module (AP, AR, GL) can close their *
233 * periods. It checks whether Intercompany Period is closed and if yes then whether *
234 * any Open Transactions exists for the given Period. *
235 * *
236 ***************************************************/
237 PROCEDURE Get_Period_Status
238 (
239 p_api_version IN NUMBER,
240 p_application_id IN NUMBER,
241 x_return_status OUT NOCOPY VARCHAR2,
242 x_message_count OUT NOCOPY NUMBER,
243 x_message_data OUT NOCOPY VARCHAR2,
244 p_period_set_name IN VARCHAR2,
245 p_period_type IN VARCHAR2,
246 p_period_name IN VARCHAR2,
247 p_ledger_id IN NUMBER,
248 p_org_id IN NUMBER,
249 x_close OUT NOCOPY VARCHAR2
250 ) IS
251 Cursor c_ic_cal_defined is select inteco_calendar from
252 fun_system_options where
253 inteco_calendar is not null and
254 inteco_period_type is not null;
255 Cursor c_ic_cal(l_period_set_name in Varchar2, l_period_type in
256 Varchar2) IS select
257 inteco_calendar from
258 fun_system_options where inteco_calendar =
259 l_period_set_name and inteco_period_type =
260 l_period_type;
261 Cursor c_ic_prd_open(l_prd_name in Varchar2) is
262 Select 1 from dual where exists
263 (Select 'X' from
264 fun_period_statuses fps,fun_system_options fso where period_name
265 = l_prd_name and status = 'O'
266 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
267 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
268 Cursor c_open_trx_ap(l_prd_name in Varchar2) is
269 Select 1 from dual where exists
270 (Select 'X' from
271 fun_trx_batches ftb, fun_trx_headers fth,
272 fun_period_statuses fps,fun_system_options fso where
273 ftb.batch_id = fth.batch_id and
274 ftb.trx_type_id=fps.trx_type_id and
275 ftb.gl_date >= fps.start_date and ftb.gl_date <=
276 fps.end_date and fps.period_name = l_prd_name and
277 fth.status not in ('NEW', 'REJECTED', 'COMPLETE')
278 and fun_tca_pkg.get_ou_id(fth.recipient_id) = p_org_id
279 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
280 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
281 cursor c_open_int_ap(l_prd_name in Varchar2) is
282 Select 1 from dual where exists
283 (Select 'X' from
284 ap_invoices_interface api,
285 fun_period_statuses fps,fun_system_options fso
286 where api.source = 'GLOBAL_INTERCOMPANY' and
287 api.org_id = p_org_id and
288 api.gl_date >= fps.start_date and api.gl_date <=
289 fps.end_date and
290 fps.period_name = l_prd_name
291 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
292 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
293 Cursor c_open_trx_ar(l_prd_name in Varchar2) is
294 Select 1 from dual where exists
295 (Select 'X' from
296 fun_trx_batches ftb, fun_trx_headers fth,
297 fun_period_statuses fps where ftb.batch_id = fth.batch_id and
298 ftb.trx_type_id=fps.trx_type_id and
299 ftb.gl_date >= fps.start_date and ftb.gl_date <=
300 fps.end_date and fps.period_name = l_prd_name and
301 fth.status not in ('NEW', 'REJECTED', 'COMPLETE', 'XFER_AR')
302 and fun_tca_pkg.get_ou_id(fth.initiator_id) = p_org_id);
303 -- Bug 9634573 modified below cursor to fetch src name from table rather than hard coded value
304 cursor c_open_int_ar(l_prd_name in Varchar2) is
305 Select 1 from dual where exists
306 (Select 'X' from
307 ra_interface_lines_all ri,
308 fun_period_statuses fps
309 where ri.batch_source_name = (SELECT name FROM
310 RA_BATCH_SOURCES_ALL WHERE BATCH_SOURCE_ID = 22 AND org_id = p_org_id) and
311 ri.org_id = p_org_id and
312 ri.gl_date >= fps.start_date and ri.gl_date <=
313 fps.end_date and
314 fps.period_name = l_prd_name);
315 Cursor c_open_trx_gl(l_prd_name in Varchar2) is
316 Select 1 from dual where exists
317 (Select 'X' from
318 fun_trx_batches ftb, fun_trx_headers fth,
319 fun_period_statuses fps,fun_system_options fso where ftb.batch_id = fth.batch_id and
320 ftb.gl_date >= fps.start_date and ftb.gl_date <=
321 fps.end_date and
322 ftb.trx_type_id=fps.trx_type_id and
323 fps.period_name = l_prd_name and
324 fth.status not in ('NEW', 'REJECTED', 'COMPLETE') and
325 fth.to_ledger_id = p_ledger_id
326 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
327 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
328 cursor c_open_int_gl(l_prd_name in Varchar2) is
329 Select 1 from dual where exists
330 (Select 'X' from
331 gl_interface gi,
332 fun_period_statuses fps,fun_system_options fso
333 where gi.user_je_source_name = 'Global Intercompany' and
334 gi.user_je_category_name = 'Global Intercompany' and
335 gi.ledger_id = p_ledger_id and
336 gi.reference_date >= fps.start_date and gi.accounting_date <=
337 fps.end_date and
338 fps.period_name = l_prd_name
339 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
340 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
341 l_api_name CONSTANT VARCHAR2(30) := 'GET_PERIOD_STATUS';
342 l_api_version CONSTANT NUMBER := 1.0;
343 l_period_set_name Varchar2(15);
344 l_prd_cnt Number;
345 l_open_trx_ap_cnt Number;
346 l_open_trx_ar_cnt Number;
347 l_open_trx_gl_cnt Number;
348 l_count number;
349 BEGIN
350 -- Standard Call to check for API compatibility
351 IF NOT FND_API.Compatible_API_Call (l_api_version,
352 p_api_version,
353 l_api_name,
354 G_PKG_NAME)
355 THEN
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END IF;
358 -- Initialize API return status to success
359 x_return_status := FND_API.G_RET_STS_SUCCESS;
360 /*
361 Validate the validity of parameters: All Mandatory Parameters should be passed, API Version
362 */
363 IF ( P_api_version IS NULL OR
364 p_period_set_name IS NULL OR
365 p_period_type is NULL OR
366 p_application_id is NULL OR
367 p_period_name IS NULL)
368 THEN
369 x_message_data := 'FUN_REQUIRED_FIELDS_INCOMPLETE';
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 Raise FND_API.G_EXC_ERROR;
372 END IF;
373 IF (p_application_id = 200 or p_application_id = 222) then
374 If (p_org_id is NULL) then
375 x_message_data := 'FUN_ORG_OR_LEDGER_ID_REQ';
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 Raise FND_API.G_EXC_ERROR;
378 End If;
379 Elsif (p_application_id = 101) then
380 If (p_ledger_id is NULL) then
381 x_message_data := 'FUN_ORG_OR_LEDGER_ID_REQ';
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 Raise FND_API.G_EXC_ERROR;
384 End If;
385 End If;
386 open c_ic_cal_defined;
387 fetch c_ic_cal_defined into l_period_set_name;
388 if (c_ic_cal_defined%notfound) then
389 x_close := 'Y';
390 x_message_data := 'FUN_IC_CALENDAR_NOT_SET';
391 Close c_ic_cal_defined;
392 Raise FND_API.G_EXC_ERROR;
393 End If;
394 Close c_ic_cal_defined;
395 /*
396 Check if p_period_set_name is not equal to Calendar Name set
397 in Intercompany
398 */
399 open c_ic_cal(p_period_set_name,p_period_type);
400 fetch c_ic_cal into l_period_set_name;
401 if (c_ic_cal%notfound) then
402 x_close := 'Y';
403 x_message_data := 'FUN_IC_CORE_CALENDAR_DIFFERENT';
404 Else
405 /*
406 3. <Check whether Intercompany Period is closed.>
407 */
408 open c_ic_prd_open(p_period_name);
409 fetch c_ic_prd_open into l_count;
410 if (c_ic_prd_open%found) then
411 x_close := 'N';
412 x_message_data := 'FUN_IC_PERIOD_OPEN';
413 Raise FND_API.G_EXC_ERROR;
414 else
415 if (p_application_id = 200) then
416 /* Callin Application Is AP */
417 /* Check for Open transactions for AP */
418 open c_open_trx_ap(p_period_name);
419 fetch c_open_trx_ap into l_count;
420 if (c_open_trx_ap%found) then
421 x_close := 'N';
422 x_message_data := 'FUN_IC_TRXS_OPEN';
423 Raise FND_API.G_EXC_ERROR;
424 Else
425 /* Check for any Open transactions in AP Interface */
426 open c_open_int_ap(p_period_name);
427 fetch c_open_int_ap into l_count;
428 if (c_open_int_ap%found) then
429 x_close:= 'N';
430 x_message_data := 'FUN_IC_OPEN_TRXS_INTERFACE';
431 Raise FND_API.G_EXC_ERROR;
432 else
433 x_close := 'Y';
434 End If;
435 close c_open_int_ap;
436 End If;
437 close c_open_trx_ap;
438 Elsif (p_application_id = 222) then
439 /* Callin Application Is AR */
440 /* Check for Open transactions for AR */
441 open c_open_trx_ar(p_period_name);
442 fetch c_open_trx_ar into l_count;
443 if (c_open_trx_ar%found) then
444 x_close := 'N';
445 x_message_data := 'FUN_IC_TRXS_OPEN';
446 Raise FND_API.G_EXC_ERROR;
447 Else
448 /* Check for any Open transactions in AR Interface */
449 open c_open_int_ar(p_period_name);
450 fetch c_open_int_ar into l_count;
451 if (c_open_int_ar%found) then
452 x_close := 'N';
453 x_message_data := 'FUN_IC_OPEN_TRXS_INTERFACE';
454 Raise FND_API.G_EXC_ERROR;
455 else
456 x_close := 'Y';
457 End If;
458 close c_open_int_ar;
459 End If;
460 close c_open_trx_ar;
461 Elsif (p_application_id = 101) then
462 /* Callin Application Is GL */
463 /* Check for Open transactions for GL */
464 open c_open_trx_gl(p_period_name);
465 fetch c_open_trx_gl into l_count;
466 if (c_open_trx_gl%found) then
467 x_close := 'N';
468 x_message_data := 'FUN_IC_TRXS_OPEN';
469 Raise FND_API.G_EXC_ERROR;
470 Else
471 /* Check for any Open transactions in GL Interface */
472 open c_open_int_gl(p_period_name);
473 fetch c_open_int_gl into l_count;
474 if (c_open_int_gl%found) then
475 x_close := 'N';
476 x_message_data := 'FUN_IC_OPEN_TRXS_INTERFACE';
477 Raise FND_API.G_EXC_ERROR;
478 else
479 x_close := 'Y';
480 End If;
481 close c_open_int_gl;
482 End If;
483 close c_open_trx_gl;
484 Else
485 x_message_data := 'FUN_INVALID_APPLICATION';
486 Raise FND_API.G_EXC_ERROR;
487 End If;
488 End If; /* prd_open */
489 close c_ic_prd_open;
490 End If; /* l_period_set_name */
491 if (c_ic_cal%isopen) then
492 close c_ic_cal;
493 end if;
494 if (c_ic_prd_open%isopen) then
495 close c_ic_prd_open;
496 end if;
497 if (c_open_trx_ap%isopen) then
498 close c_open_trx_ap;
499 end if;
500 if (c_open_int_ap%isopen) then
501 close c_open_int_ap;
502 end if;
503 if (c_open_trx_ar%isopen) then
504 close c_open_trx_ar;
505 end if;
506 if (c_open_int_ar%isopen) then
507 close c_open_int_ar;
508 end if;
509 if (c_open_trx_gl%isopen) then
510 close c_open_trx_gl;
511 end if;
512 if (c_open_int_gl%isopen) then
513 close c_open_int_gl;
514 end if;
515 if (c_ic_cal_defined%isopen) then
516 Close c_ic_cal_defined;
517 end if;
518 EXCEPTION
519 WHEN FND_API.G_EXC_ERROR THEN
520 if (c_ic_cal%isopen) then
521 close c_ic_cal;
522 end if;
523 if (c_ic_prd_open%isopen) then
524 close c_ic_prd_open;
525 end if;
526 if (c_open_trx_ap%isopen) then
527 close c_open_trx_ap;
528 end if;
529 if (c_open_int_ap%isopen) then
530 close c_open_int_ap;
531 end if;
532 if (c_open_trx_ar%isopen) then
533 close c_open_trx_ar;
534 end if;
535 if (c_open_int_ar%isopen) then
536 close c_open_int_ar;
537 end if;
538 if (c_open_trx_gl%isopen) then
539 close c_open_trx_gl;
540 end if;
541 if (c_open_int_gl%isopen) then
542 close c_open_int_gl;
543 end if;
544 if (c_ic_cal_defined%isopen) then
545 Close c_ic_cal_defined;
546 end if;
547 WHEN OTHERS THEN
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 x_message_data := SQLERRM;
550 if (c_ic_cal%isopen) then
551 close c_ic_cal;
552 end if;
553 if (c_ic_prd_open%isopen) then
554 close c_ic_prd_open;
555 end if;
556 if (c_open_trx_ap%isopen) then
557 close c_open_trx_ap;
558 end if;
559 if (c_open_int_ap%isopen) then
560 close c_open_int_ap;
561 end if;
562 if (c_open_trx_ar%isopen) then
563 close c_open_trx_ar;
564 end if;
565 if (c_open_int_ar%isopen) then
566 close c_open_int_ar;
567 end if;
568 if (c_open_trx_gl%isopen) then
569 close c_open_trx_gl;
570 end if;
571 if (c_open_int_gl%isopen) then
572 close c_open_int_gl;
573 end if;
574 if (c_ic_cal_defined%isopen) then
575 Close c_ic_cal_defined;
576 end if;
577 END Get_Period_Status;
578
579 /**********************************************
580 * Procedure sweep_partial_batches
581 * Bug : 6892783
582 * This API Sweeps Partially complete Batches to given period
583 *
584 ************************************************/
585
586 PROCEDURE sweep_partial_batches
587 ( p_errbuff OUT NOCOPY VARCHAR2,
588 p_period_name IN VARCHAR2,
589 p_trx_type_id IN NUMBER,
590 p_sweep_GL_date IN DATE
591 ) IS
592
593 CURSOR c_trx_id (l_batch_id in NUMBER)
594 IS
595 SELECT TRX_ID
596 FROM FUN_TRX_HEADERS
597 WHERE BATCH_ID = l_batch_id
598 AND STATUS IN ('SENT','ERROR','RECEIVED');
599
600 l_control_date_tbl FUN_SEQ.CONTROL_DATE_TBL_TYPE;
601 l_control_date_rec FUN_SEQ.CONTROL_DATE_REC_TYPE;
602 l_seq_version_id NUMBER;
603 l_assignment_id NUMBER;
604 l_error_code VARCHAR2(1000);
605 l_batch_id FUN_TRX_BATCHES.BATCH_ID%TYPE;
606 l_batch_number FUN_TRX_BATCHES.BATCH_NUMBER%TYPE;
607 l_running_total_dr FUN_TRX_BATCHES.RUNNING_TOTAL_DR%TYPE;
608 l_running_total_cr FUN_TRX_BATCHES.RUNNING_TOTAL_CR%TYPE;
609 l_header_trx_id FUN_TRX_HEADERS.TRX_ID%TYPE;
610 l_line_id FUN_TRX_LINES.LINE_ID%TYPE;
611 l_start_date FUN_PERIOD_STATUSES.START_DATE%TYPE;
612 l_end_date FUN_PERIOD_STATUSES.END_DATE%TYPE;
613 sqlstmt VARCHAR2(3000);
614
615 TYPE c_partial_batches_type IS REF CURSOR;
616 -- Bug 7115161. Added Batch Number field
617 TYPE c_partial_batches_rec_type IS RECORD
618 (BATCH_ID FUN_TRX_BATCHES.BATCH_ID%TYPE,
619 BATCH_NUMBER FUN_TRX_BATCHES.BATCH_NUMBER%TYPE);
620
621 c_partial_batches c_partial_batches_type;
622 l_partial_batch c_partial_batches_rec_type;
623
624 ------Bug#: 7129198 --- START
625 l_sqlstmt VARCHAR2(3000);
626
627 TYPE c_trx_attachment IS REF CURSOR;
628 c_trx_attm c_trx_attachment;
629
630 -- Bug 9756187 Start
631 l_sqlstmt_batch VARCHAR2(3000);
632
633 TYPE c_batch_attachment IS REF CURSOR;
634 c_batch_attm c_batch_attachment;
635
636 l_batch_ATTACHED_DOCUMENT_ID FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE;
637 l_batch_DOCUMENT_ID FND_ATTACHED_DOCUMENTS.DOCUMENT_ID%TYPE;
638
639 -- Bug 9756187 end
640
641 icx_language VARCHAR2(100);
642
643 TYPE c_trx_attachment_rec_type IS RECORD
644 (ROWID VARCHAR2(4000),
645 ATTACHED_DOCUMENT_ID FND_ATTACHED_DOCUMENTS.ATTACHED_DOCUMENT_ID%TYPE,
646 DOCUMENT_ID FND_ATTACHED_DOCUMENTS.DOCUMENT_ID%TYPE,
647 SEQ_NUM FND_ATTACHED_DOCUMENTS.SEQ_NUM%TYPE,
648 ENTITY_NAME FND_ATTACHED_DOCUMENTS.ENTITY_NAME%TYPE,
649 COLUMN1 FND_ATTACHED_DOCUMENTS.COLUMN1%TYPE,
650 PK1_VALUE FND_ATTACHED_DOCUMENTS.PK1_VALUE%TYPE,
651 PK2_VALUE FND_ATTACHED_DOCUMENTS.PK2_VALUE%TYPE,
652 PK3_VALUE FND_ATTACHED_DOCUMENTS.PK3_VALUE%TYPE,
653 PK4_VALUE FND_ATTACHED_DOCUMENTS.PK4_VALUE%TYPE,
654 PK5_VALUE FND_ATTACHED_DOCUMENTS.PK5_VALUE%TYPE,
655 AUTOMATICALLY_ADDED_FLAG FND_ATTACHED_DOCUMENTS.AUTOMATICALLY_ADDED_FLAG%TYPE,
656 REQUEST_ID FND_ATTACHED_DOCUMENTS.REQUEST_ID%TYPE,
657 PROGRAM_APPLICATION_ID FND_ATTACHED_DOCUMENTS.PROGRAM_APPLICATION_ID%TYPE,
658 PROGRAM_ID FND_ATTACHED_DOCUMENTS.PROGRAM_ID%TYPE,
659 PROGRAM_UPDATE_DATE FND_ATTACHED_DOCUMENTS.PROGRAM_UPDATE_DATE%TYPE,
660 ATTRIBUTE_CATEGORY FND_ATTACHED_DOCUMENTS.ATTRIBUTE_CATEGORY%TYPE,
661 ATTRIBUTE1 FND_ATTACHED_DOCUMENTS.ATTRIBUTE1%TYPE,
662 ATTRIBUTE2 FND_ATTACHED_DOCUMENTS.ATTRIBUTE2%TYPE,
663 ATTRIBUTE3 FND_ATTACHED_DOCUMENTS.ATTRIBUTE3%TYPE,
664 ATTRIBUTE4 FND_ATTACHED_DOCUMENTS.ATTRIBUTE4%TYPE,
665 ATTRIBUTE5 FND_ATTACHED_DOCUMENTS.ATTRIBUTE5%TYPE,
666 ATTRIBUTE6 FND_ATTACHED_DOCUMENTS.ATTRIBUTE6%TYPE,
667 ATTRIBUTE7 FND_ATTACHED_DOCUMENTS.ATTRIBUTE7%TYPE,
668 ATTRIBUTE8 FND_ATTACHED_DOCUMENTS.ATTRIBUTE8%TYPE,
669 ATTRIBUTE9 FND_ATTACHED_DOCUMENTS.ATTRIBUTE9%TYPE,
670 ATTRIBUTE10 FND_ATTACHED_DOCUMENTS.ATTRIBUTE10%TYPE,
671 ATTRIBUTE11 FND_ATTACHED_DOCUMENTS.ATTRIBUTE11%TYPE,
672 ATTRIBUTE12 FND_ATTACHED_DOCUMENTS.ATTRIBUTE12%TYPE,
673 ATTRIBUTE13 FND_ATTACHED_DOCUMENTS.ATTRIBUTE13%TYPE,
674 ATTRIBUTE14 FND_ATTACHED_DOCUMENTS.ATTRIBUTE14%TYPE,
675 ATTRIBUTE15 FND_ATTACHED_DOCUMENTS.ATTRIBUTE15%TYPE,
676 -------------------------------------
677 DATATYPE_ID FND_DOCUMENTS.DATATYPE_ID%TYPE,
678 CATEGORY_ID FND_DOCUMENTS.CATEGORY_ID%TYPE,
679 SECURITY_TYPE FND_DOCUMENTS.SECURITY_TYPE%TYPE,
680 SECURITY_ID FND_DOCUMENTS.SECURITY_ID%TYPE,
681 PUBLISH_FLAG FND_DOCUMENTS.PUBLISH_FLAG%TYPE,
682 IMAGE_TYPE FND_DOCUMENTS.IMAGE_TYPE%TYPE,
683 STORAGE_TYPE FND_DOCUMENTS.STORAGE_TYPE%TYPE,
684 USAGE_TYPE FND_DOCUMENTS.USAGE_TYPE%TYPE,
685 START_DATE_ACTIVE FND_DOCUMENTS.START_DATE_ACTIVE%TYPE,
686 END_DATE_ACTIVE FND_DOCUMENTS.END_DATE_ACTIVE%TYPE,
687 ---------------------------------------
688 l_LANGUAGE fnd_documents_tl.LANGUAGE%TYPE,
689 DESCRIPTION fnd_documents_tl.DESCRIPTION%TYPE,
690 FILE_NAME fnd_documents.FILE_NAME%TYPE,
691 MEDIA_ID fnd_documents.MEDIA_ID%TYPE, -- IN OUT NOCOPY
692 DOC_ATTRIBUTE_CATEGORY fnd_documents_tl.DOC_ATTRIBUTE_CATEGORY%TYPE,
693 DOC_ATTRIBUTE1 fnd_documents_tl.DOC_ATTRIBUTE1%TYPE,
694 DOC_ATTRIBUTE2 fnd_documents_tl.DOC_ATTRIBUTE2%TYPE,
695 DOC_ATTRIBUTE3 fnd_documents_tl.DOC_ATTRIBUTE3%TYPE,
696 DOC_ATTRIBUTE4 fnd_documents_tl.DOC_ATTRIBUTE4%TYPE,
697 DOC_ATTRIBUTE5 fnd_documents_tl.DOC_ATTRIBUTE5%TYPE,
698 DOC_ATTRIBUTE6 fnd_documents_tl.DOC_ATTRIBUTE6%TYPE,
699 DOC_ATTRIBUTE7 fnd_documents_tl.DOC_ATTRIBUTE7%TYPE,
700 DOC_ATTRIBUTE8 fnd_documents_tl.DOC_ATTRIBUTE8%TYPE,
701 DOC_ATTRIBUTE9 fnd_documents_tl.DOC_ATTRIBUTE9%TYPE,
702 DOC_ATTRIBUTE10 fnd_documents_tl.DOC_ATTRIBUTE10%TYPE,
703 DOC_ATTRIBUTE11 fnd_documents_tl.DOC_ATTRIBUTE11%TYPE,
704 DOC_ATTRIBUTE12 fnd_documents_tl.DOC_ATTRIBUTE12%TYPE,
705 DOC_ATTRIBUTE13 fnd_documents_tl.DOC_ATTRIBUTE13%TYPE,
706 DOC_ATTRIBUTE14 fnd_documents_tl.DOC_ATTRIBUTE14%TYPE,
707 DOC_ATTRIBUTE15 fnd_documents_tl.DOC_ATTRIBUTE15%TYPE,
708 URL FND_DOCUMENTS.URL%TYPE,
709 TITLE fnd_documents_tl.TITLE%TYPE
710 );
711
712 -- Bug 9756187 Start
713
714 TYPE c_batch_attachment_rec_type IS RECORD
715 (ROWID VARCHAR2(4000),
716 creation_date FND_ATTACHED_DOCUMENTS.creation_date%TYPE,
717 created_by FND_ATTACHED_DOCUMENTS.created_by%TYPE,
718 SEQ_NUM FND_ATTACHED_DOCUMENTS.SEQ_NUM%TYPE,
719 ENTITY_NAME FND_ATTACHED_DOCUMENTS.ENTITY_NAME%TYPE,
720 COLUMN1 FND_ATTACHED_DOCUMENTS.COLUMN1%TYPE,
721 PK1_VALUE FND_ATTACHED_DOCUMENTS.PK1_VALUE%TYPE,
722 PK2_VALUE FND_ATTACHED_DOCUMENTS.PK2_VALUE%TYPE,
723 PK3_VALUE FND_ATTACHED_DOCUMENTS.PK3_VALUE%TYPE,
724 PK4_VALUE FND_ATTACHED_DOCUMENTS.PK4_VALUE%TYPE,
725 PK5_VALUE FND_ATTACHED_DOCUMENTS.PK5_VALUE%TYPE,
726 AUTOMATICALLY_ADDED_FLAG FND_ATTACHED_DOCUMENTS.AUTOMATICALLY_ADDED_FLAG%TYPE,
727 REQUEST_ID FND_ATTACHED_DOCUMENTS.REQUEST_ID%TYPE,
728 PROGRAM_APPLICATION_ID FND_ATTACHED_DOCUMENTS.PROGRAM_APPLICATION_ID%TYPE,
729 PROGRAM_ID FND_ATTACHED_DOCUMENTS.PROGRAM_ID%TYPE,
730 PROGRAM_UPDATE_DATE FND_ATTACHED_DOCUMENTS.PROGRAM_UPDATE_DATE%TYPE,
731 ATTRIBUTE_CATEGORY FND_ATTACHED_DOCUMENTS.ATTRIBUTE_CATEGORY%TYPE,
732 ATTRIBUTE1 FND_ATTACHED_DOCUMENTS.ATTRIBUTE1%TYPE,
733 ATTRIBUTE2 FND_ATTACHED_DOCUMENTS.ATTRIBUTE2%TYPE,
734 ATTRIBUTE3 FND_ATTACHED_DOCUMENTS.ATTRIBUTE3%TYPE,
735 ATTRIBUTE4 FND_ATTACHED_DOCUMENTS.ATTRIBUTE4%TYPE,
736 ATTRIBUTE5 FND_ATTACHED_DOCUMENTS.ATTRIBUTE5%TYPE,
737 ATTRIBUTE6 FND_ATTACHED_DOCUMENTS.ATTRIBUTE6%TYPE,
738 ATTRIBUTE7 FND_ATTACHED_DOCUMENTS.ATTRIBUTE7%TYPE,
739 ATTRIBUTE8 FND_ATTACHED_DOCUMENTS.ATTRIBUTE8%TYPE,
740 ATTRIBUTE9 FND_ATTACHED_DOCUMENTS.ATTRIBUTE9%TYPE,
741 ATTRIBUTE10 FND_ATTACHED_DOCUMENTS.ATTRIBUTE10%TYPE,
742 ATTRIBUTE11 FND_ATTACHED_DOCUMENTS.ATTRIBUTE11%TYPE,
743 ATTRIBUTE12 FND_ATTACHED_DOCUMENTS.ATTRIBUTE12%TYPE,
744 ATTRIBUTE13 FND_ATTACHED_DOCUMENTS.ATTRIBUTE13%TYPE,
745 ATTRIBUTE14 FND_ATTACHED_DOCUMENTS.ATTRIBUTE14%TYPE,
746 ATTRIBUTE15 FND_ATTACHED_DOCUMENTS.ATTRIBUTE15%TYPE,
747 -------------------------------------
748 DATATYPE_ID FND_DOCUMENTS.DATATYPE_ID%TYPE,
749 CATEGORY_ID FND_DOCUMENTS.CATEGORY_ID%TYPE,
750 SECURITY_TYPE FND_DOCUMENTS.SECURITY_TYPE%TYPE,
751 SECURITY_ID FND_DOCUMENTS.SECURITY_ID%TYPE,
752 PUBLISH_FLAG FND_DOCUMENTS.PUBLISH_FLAG%TYPE,
753 IMAGE_TYPE FND_DOCUMENTS.IMAGE_TYPE%TYPE,
754 STORAGE_TYPE FND_DOCUMENTS.STORAGE_TYPE%TYPE,
755 USAGE_TYPE FND_DOCUMENTS.USAGE_TYPE%TYPE,
756 START_DATE_ACTIVE FND_DOCUMENTS.START_DATE_ACTIVE%TYPE,
757 END_DATE_ACTIVE FND_DOCUMENTS.END_DATE_ACTIVE%TYPE,
758 ---------------------------------------
759 l_LANGUAGE fnd_documents_tl.LANGUAGE%TYPE,
760 DESCRIPTION fnd_documents_tl.DESCRIPTION%TYPE,
761 FILE_NAME fnd_documents.FILE_NAME%TYPE,
762 MEDIA_ID fnd_documents.MEDIA_ID%TYPE, -- IN OUT NOCOPY
763 DOC_ATTRIBUTE_CATEGORY fnd_documents_tl.DOC_ATTRIBUTE_CATEGORY%TYPE,
764 DOC_ATTRIBUTE1 fnd_documents_tl.DOC_ATTRIBUTE1%TYPE,
765 DOC_ATTRIBUTE2 fnd_documents_tl.DOC_ATTRIBUTE2%TYPE,
766 DOC_ATTRIBUTE3 fnd_documents_tl.DOC_ATTRIBUTE3%TYPE,
767 DOC_ATTRIBUTE4 fnd_documents_tl.DOC_ATTRIBUTE4%TYPE,
768 DOC_ATTRIBUTE5 fnd_documents_tl.DOC_ATTRIBUTE5%TYPE,
769 DOC_ATTRIBUTE6 fnd_documents_tl.DOC_ATTRIBUTE6%TYPE,
770 DOC_ATTRIBUTE7 fnd_documents_tl.DOC_ATTRIBUTE7%TYPE,
771 DOC_ATTRIBUTE8 fnd_documents_tl.DOC_ATTRIBUTE8%TYPE,
772 DOC_ATTRIBUTE9 fnd_documents_tl.DOC_ATTRIBUTE9%TYPE,
773 DOC_ATTRIBUTE10 fnd_documents_tl.DOC_ATTRIBUTE10%TYPE,
774 DOC_ATTRIBUTE11 fnd_documents_tl.DOC_ATTRIBUTE11%TYPE,
775 DOC_ATTRIBUTE12 fnd_documents_tl.DOC_ATTRIBUTE12%TYPE,
776 DOC_ATTRIBUTE13 fnd_documents_tl.DOC_ATTRIBUTE13%TYPE,
777 DOC_ATTRIBUTE14 fnd_documents_tl.DOC_ATTRIBUTE14%TYPE,
778 DOC_ATTRIBUTE15 fnd_documents_tl.DOC_ATTRIBUTE15%TYPE,
779 URL FND_DOCUMENTS.URL%TYPE,
780 TITLE fnd_documents_tl.TITLE%TYPE
781 );
782
783 -- Bug 9756187 End
784
785 l_attchmt_rec_type c_trx_attachment_rec_type;
786 l_batch_attchmt_rec_type c_batch_attachment_rec_type;
787
788 ------Bug#: 7129198 --- END
789 BEGIN
790
791 IF ( p_sweep_GL_Date IS NULL OR
792 p_period_name IS NULL) THEN
793 p_errbuff := 'Required Fields not Passed';
794 Print('REQUIRED FIELDS NOT PASSED');
795 Raise FND_API.G_EXC_ERROR;
796 END IF;
797
798
799 IF (p_trx_type_id is NOT NULL) THEN
800
801 SELECT START_DATE
802 INTO l_start_date
803 FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
804 WHERE FPS.PERIOD_NAME = p_period_name
805 AND FPS.TRX_TYPE_ID = p_trx_type_id
806 AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR, '~~')
807 AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
808
809 SELECT END_DATE
810 INTO l_end_date
811 FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
812 WHERE FPS.PERIOD_NAME = p_period_name
813 AND FPS.TRX_TYPE_ID = p_trx_type_id
814 AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,'~~')
815 AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,'~~');
816
817 --Bug 7115161. Changed the Query to fetch the batch number also
818
819 sqlstmt := 'SELECT BATCH_ID, BATCH_NUMBER
820 FROM FUN_TRX_BATCHES
821 WHERE GL_DATE >= ''' || l_start_date || '''
822 AND GL_DATE <= ''' || l_end_date || '''
823 AND TRX_TYPE_ID = ''' || p_trx_type_id || '''
824 AND BATCH_ID IN
825 (SELECT BATCH_ID
826 FROM FUN_TRX_HEADERS
827 WHERE STATUS IN (''SENT'',''ERROR'',''RECEIVED''))
828 AND STATUS NOT IN (''COMPLETE'', ''NEW'')';
829
830 ELSE
831 sqlstmt := 'SELECT BATCH_ID, BATCH_NUMBER
832 FROM FUN_TRX_BATCHES
833 WHERE GL_DATE >=
834 (SELECT DISTINCT START_DATE
835 FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
836 WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
837 AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
838 AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
839 AND GL_DATE <=
840 (SELECT DISTINCT END_DATE
841 FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
842 WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
843 AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
844 AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
845 AND TRX_TYPE_ID IN
846 (SELECT TRX_TYPE_ID
847 FROM FUN_PERIOD_STATUSES FPS,FUN_SYSTEM_OPTIONS FSO
848 WHERE FPS.PERIOD_NAME = ''' || p_period_name || '''
849 AND FPS.INTECO_CALENDAR=NVL(FSO.INTECO_CALENDAR,''~~'')
850 AND FPS.INTECO_PERIOD_TYPE=NVL(FSO.INTECO_PERIOD_TYPE,''~~''))
851 AND BATCH_ID IN
852 (SELECT BATCH_ID
853 FROM FUN_TRX_HEADERS
854 WHERE STATUS IN (''SENT'',''ERROR'',''RECEIVED''))
855 AND STATUS NOT IN (''COMPLETE'', ''NEW'');';
856 END IF;
857
858
859 l_control_date_tbl := FUN_SEQ.CONTROL_DATE_TBL_TYPE();
860 l_control_date_tbl.EXTEND(1);
861 l_control_date_rec.date_type := 'CREATION_DATE';
862 l_control_date_rec.date_value := sysdate;
863 l_control_date_tbl(1) := l_control_date_rec;
864
865 OPEN c_partial_batches FOR sqlstmt;
866 LOOP
867
868 FETCH c_partial_batches
869 INTO l_partial_batch;
870 EXIT WHEN c_partial_batches%NOTFOUND;
871
872
873
874 l_running_total_dr := 0;
875 l_running_total_cr := 0;
876
877 --Fecthing the New Batch_id
878 SELECT FUN_TRX_BATCHES_S.nextval INTO l_batch_id FROM DUAL;
879
880 --Fetching the New Batch Number
881 FUN_SEQ.GET_SEQUENCE_NUMBER('INTERCOMPANY_BATCH_SOURCE',
882 'LOCAL',
883 435,
884 'FUN_TRX_BATCHES',
885 'CREATION',
886 null,
887 l_control_date_tbl,
888 'N',
889 l_seq_version_id,
890 l_batch_number,
891 l_assignment_id,
892 l_error_code);
893
894 -- Bug 7115161. Populating the Old Batch number to the Notes field
895 INSERT INTO FUN_TRX_BATCHES
896 (BATCH_ID,
897 BATCH_NUMBER,
898 GL_DATE,
899 STATUS,
900 NOTE,
901 CREATED_BY,
902 CREATION_DATE,
903 LAST_UPDATED_BY,
904 LAST_UPDATE_DATE,
905 LAST_UPDATE_LOGIN,
906 INITIATOR_ID,
907 FROM_LE_ID,
908 FROM_LEDGER_ID,
909 CONTROL_TOTAL,
910 RUNNING_TOTAL_CR,
911 RUNNING_TOTAL_DR,
912 CURRENCY_CODE,
913 EXCHANGE_RATE_TYPE,
914 DESCRIPTION,
915 TRX_TYPE_ID,
916 TRX_TYPE_CODE,
917 BATCH_DATE,
918 REJECT_ALLOW_FLAG,
919 ORIGINAL_BATCH_ID,
920 REVERSED_BATCH_ID,
921 FROM_RECURRING_BATCH_ID,
922 INITIATOR_SOURCE,
923 ATTRIBUTE1,
924 ATTRIBUTE2,
925 ATTRIBUTE3,
926 ATTRIBUTE4,
927 ATTRIBUTE5,
928 ATTRIBUTE6,
929 ATTRIBUTE7,
930 ATTRIBUTE8,
931 ATTRIBUTE9,
932 ATTRIBUTE10,
933 ATTRIBUTE11,
934 ATTRIBUTE12,
935 ATTRIBUTE13,
936 ATTRIBUTE14,
937 ATTRIBUTE15,
938 ATTRIBUTE_CATEGORY,
939 AUTO_PRORATION_FLAG)
940 SELECT l_batch_id,
941 l_batch_number,
942 p_sweep_GL_date,
943 'NEW',
944 'Original Batch: ' || l_partial_batch.BATCH_NUMBER,
945 fnd_global.user_id,
946 sysdate,
947 fnd_global.user_id,
948 sysdate,
949 fnd_global.login_id,
950 INITIATOR_ID,
951 FROM_LE_ID,
952 FROM_LEDGER_ID,
953 CONTROL_TOTAL,
954 RUNNING_TOTAL_CR,
955 RUNNING_TOTAL_DR,
956 CURRENCY_CODE,
957 EXCHANGE_RATE_TYPE,
958 DESCRIPTION,
959 TRX_TYPE_ID,
960 TRX_TYPE_CODE,
961 BATCH_DATE,
962 REJECT_ALLOW_FLAG,
963 ORIGINAL_BATCH_ID,
964 REVERSED_BATCH_ID,
965 FROM_RECURRING_BATCH_ID,
966 INITIATOR_SOURCE,
967 ATTRIBUTE1,
968 ATTRIBUTE2,
969 ATTRIBUTE3,
970 ATTRIBUTE4,
971 ATTRIBUTE5,
972 ATTRIBUTE6,
973 ATTRIBUTE7,
974 ATTRIBUTE8,
975 ATTRIBUTE9,
976 ATTRIBUTE10,
977 ATTRIBUTE11,
978 ATTRIBUTE12,
979 ATTRIBUTE13,
980 ATTRIBUTE14,
981 ATTRIBUTE15,
982 ATTRIBUTE_CATEGORY,
983 AUTO_PRORATION_FLAG
984 FROM FUN_TRX_BATCHES
985 WHERE BATCH_ID = l_partial_batch.BATCH_ID;
986
987 FOR l_trx_id in c_trx_id(l_partial_batch.BATCH_ID)
988 LOOP
989 l_header_trx_id := 0;
990 l_line_id := 0;
991 SELECT FUN_TRX_HEADERS_S.nextval INTO l_header_trx_id FROM DUAL;
992 SELECT FUN_TRX_LINES_S.nextval INTO l_line_id FROM DUAL;
993
994 INSERT INTO FUN_TRX_HEADERS
995 (TRX_ID,
996 BATCH_ID,
997 STATUS,
998 INIT_WF_KEY,
999 RECI_WF_KEY,
1000 CREATED_BY,
1001 CREATION_DATE,
1002 LAST_UPDATED_BY,
1003 LAST_UPDATE_DATE,
1004 LAST_UPDATE_LOGIN,
1005 TRX_NUMBER,
1006 INITIATOR_ID,
1007 RECIPIENT_ID,
1008 TO_LE_ID,
1009 TO_LEDGER_ID,
1010 INIT_AMOUNT_CR,
1011 INIT_AMOUNT_DR,
1012 RECI_AMOUNT_CR,
1013 RECI_AMOUNT_DR,
1014 AR_INVOICE_NUMBER,
1015 INVOICE_FLAG,
1016 APPROVER_ID,
1017 APPROVAL_DATE,
1018 ORIGINAL_TRX_ID,
1019 REVERSED_TRX_ID,
1020 FROM_RECURRING_TRX_ID,
1021 INITIATOR_INSTANCE_FLAG,
1022 RECIPIENT_INSTANCE_FLAG,
1023 REJECT_REASON,
1024 DESCRIPTION,
1025 ATTRIBUTE1,
1026 ATTRIBUTE2,
1027 ATTRIBUTE3,
1028 ATTRIBUTE4,
1029 ATTRIBUTE5,
1030 ATTRIBUTE6,
1031 ATTRIBUTE7,
1032 ATTRIBUTE8,
1033 ATTRIBUTE9,
1034 ATTRIBUTE10,
1035 ATTRIBUTE11,
1036 ATTRIBUTE12,
1037 ATTRIBUTE13,
1038 ATTRIBUTE14,
1039 ATTRIBUTE15,
1040 ATTRIBUTE_CATEGORY)
1041 SELECT l_header_trx_id,
1042 l_batch_id,
1043 'NEW',
1044 NULL,
1045 NULL,
1046 fnd_global.user_id,
1047 sysdate,
1048 fnd_global.user_id,
1049 sysdate,
1050 fnd_global.login_id,
1051 TRX_NUMBER,
1052 INITIATOR_ID,
1053 RECIPIENT_ID,
1054 TO_LE_ID,
1055 TO_LEDGER_ID,
1056 INIT_AMOUNT_CR,
1057 INIT_AMOUNT_DR,
1058 RECI_AMOUNT_CR,
1059 RECI_AMOUNT_DR,
1060 AR_INVOICE_NUMBER,
1061 INVOICE_FLAG,
1062 APPROVER_ID,
1063 APPROVAL_DATE,
1064 ORIGINAL_TRX_ID,
1065 REVERSED_TRX_ID,
1066 FROM_RECURRING_TRX_ID,
1067 INITIATOR_INSTANCE_FLAG,
1068 RECIPIENT_INSTANCE_FLAG,
1069 REJECT_REASON,
1070 DESCRIPTION,
1071 ATTRIBUTE1,
1072 ATTRIBUTE2,
1073 ATTRIBUTE3,
1074 ATTRIBUTE4,
1075 ATTRIBUTE5,
1076 ATTRIBUTE6,
1077 ATTRIBUTE7,
1078 ATTRIBUTE8,
1079 ATTRIBUTE9,
1080 ATTRIBUTE10,
1081 ATTRIBUTE11,
1082 ATTRIBUTE12,
1083 ATTRIBUTE13,
1084 ATTRIBUTE14,
1085 ATTRIBUTE15,
1086 ATTRIBUTE_CATEGORY
1087 FROM FUN_TRX_HEADERS
1088 WHERE TRX_ID = l_trx_id.TRX_ID;
1089
1090 ------Bug#: 7129198 --- START
1091
1092 fnd_profile.get(FND_CONST.ICX_LANGUAGE,icx_language);
1093 SELECT language_code INTO icx_language FROM fnd_languages WHERE NLS_LANGUAGE=icx_language;
1094
1095 l_sqlstmt:=
1096 'SELECT
1097 atth.ROWID,
1098 atth.ATTACHED_DOCUMENT_ID,
1099 atth.DOCUMENT_ID,
1100 atth.SEQ_NUM,
1101 atth.ENTITY_NAME,
1102 atth.COLUMN1,
1103 atth.PK1_VALUE,
1104 atth.PK2_VALUE,
1105 atth.PK3_VALUE,
1106 atth.PK4_VALUE,
1107 atth.PK5_VALUE,
1108 atth.AUTOMATICALLY_ADDED_FLAG,
1109 atth.REQUEST_ID,
1110 atth.PROGRAM_APPLICATION_ID,
1111 atth.PROGRAM_ID,
1112 atth.PROGRAM_UPDATE_DATE,
1113 atth.ATTRIBUTE_CATEGORY,
1114 atth.ATTRIBUTE1,
1115 atth.ATTRIBUTE2,
1116 atth.ATTRIBUTE3,
1117 atth.ATTRIBUTE4,
1118 atth.ATTRIBUTE5,
1119 atth.ATTRIBUTE6,
1120 atth.ATTRIBUTE7,
1121 atth.ATTRIBUTE8,
1122 atth.ATTRIBUTE9,
1123 atth.ATTRIBUTE10,
1124 atth.ATTRIBUTE11,
1125 atth.ATTRIBUTE12,
1126 atth.ATTRIBUTE13,
1127 atth.ATTRIBUTE14,
1128 atth.ATTRIBUTE15,
1129
1130 docs.DATATYPE_ID,
1131 docs.CATEGORY_ID,
1132 docs.SECURITY_TYPE,
1133 docs.SECURITY_ID,
1134 docs.PUBLISH_FLAG,
1135 docs.IMAGE_TYPE,
1136 docs.STORAGE_TYPE,
1137 docs.USAGE_TYPE,
1138 docs.START_DATE_ACTIVE,
1139 docs.END_DATE_ACTIVE,
1140
1141 tl.LANGUAGE,
1142 tl.DESCRIPTION,
1143 docs.FILE_NAME,
1144 docs.MEDIA_ID,
1145 tl.DOC_ATTRIBUTE_CATEGORY,
1146 tl.DOC_ATTRIBUTE1,
1147 tl.DOC_ATTRIBUTE2,
1148 tl.DOC_ATTRIBUTE3,
1149 tl.DOC_ATTRIBUTE4,
1150 tl.DOC_ATTRIBUTE5,
1151 tl.DOC_ATTRIBUTE6,
1152 tl.DOC_ATTRIBUTE7,
1153 tl.DOC_ATTRIBUTE8,
1154 tl.DOC_ATTRIBUTE9,
1155 tl.DOC_ATTRIBUTE10,
1156 tl.DOC_ATTRIBUTE11,
1157 tl.DOC_ATTRIBUTE12,
1158 tl.DOC_ATTRIBUTE13,
1159 tl.DOC_ATTRIBUTE14,
1160 tl.DOC_ATTRIBUTE15,
1161 docs.URL,
1162 tl.TITLE
1163
1164 from fnd_documents docs,
1165 FND_ATTACHED_DOCUMENTS atth,
1166 fnd_documents_tl tl
1167
1168 where entity_name = ''FUN_TRX_HEADERS''
1169 and tl.LANGUAGE=''' || icx_language || '''
1170 and pk1_value=''' || l_trx_id.TRX_ID || '''
1171 and docs.document_id = atth.document_id
1172 and tl.document_id = atth.document_id';
1173
1174
1175 OPEN c_trx_attm FOR l_sqlstmt;
1176 LOOP
1177 FETCH c_trx_attm INTO l_attchmt_rec_type;
1178 EXIT WHEN c_trx_attm%NOTFOUND;
1179
1180 fnd_attached_documents_pkg.Update_Row(
1181 X_Rowid => l_attchmt_rec_type.ROWID,
1182 X_attached_document_id => l_attchmt_rec_type.ATTACHED_DOCUMENT_ID,
1183 X_document_id => l_attchmt_rec_type.DOCUMENT_ID,
1184 X_last_update_date => sysdate,
1185 X_last_updated_by => FND_GLOBAL.USER_ID,
1186 X_last_update_login => FND_GLOBAL.USER_ID,
1187 X_seq_num => l_attchmt_rec_type.SEQ_NUM,
1188 X_entity_name => l_attchmt_rec_type.ENTITY_NAME,
1189 X_column1 => l_attchmt_rec_type.COLUMN1,
1190 X_pk1_value => l_header_trx_id,
1191 X_pk2_value => l_attchmt_rec_type.PK2_VALUE,
1192 X_pk3_value => l_attchmt_rec_type.PK3_VALUE,
1193 X_pk4_value => l_attchmt_rec_type.PK4_VALUE,
1194 X_pk5_value => l_attchmt_rec_type.PK5_VALUE,
1195 X_automatically_added_flag => l_attchmt_rec_type.AUTOMATICALLY_ADDED_FLAG,
1196 X_request_id => l_attchmt_rec_type.REQUEST_ID,
1197 X_program_application_id => l_attchmt_rec_type.PROGRAM_APPLICATION_ID,
1198 X_program_id => l_attchmt_rec_type.PROGRAM_ID,
1199 X_program_update_date => l_attchmt_rec_type.PROGRAM_UPDATE_DATE,
1200 X_Attribute_Category => l_attchmt_rec_type.ATTRIBUTE_CATEGORY,
1201 X_Attribute1 => l_attchmt_rec_type.ATTRIBUTE1,
1202 X_Attribute2 => l_attchmt_rec_type.ATTRIBUTE2,
1203 X_Attribute3 => l_attchmt_rec_type.ATTRIBUTE3,
1204 X_Attribute4 => l_attchmt_rec_type.ATTRIBUTE4,
1205 X_Attribute5 => l_attchmt_rec_type.ATTRIBUTE5,
1206 X_Attribute6 => l_attchmt_rec_type.ATTRIBUTE6,
1207 X_Attribute7 => l_attchmt_rec_type.ATTRIBUTE7,
1208 X_Attribute8 => l_attchmt_rec_type.ATTRIBUTE8,
1209 X_Attribute9 => l_attchmt_rec_type.ATTRIBUTE9,
1210 X_Attribute10 => l_attchmt_rec_type.ATTRIBUTE10,
1211 X_Attribute11 => l_attchmt_rec_type.ATTRIBUTE11,
1212 X_Attribute12 => l_attchmt_rec_type.ATTRIBUTE12,
1213 X_Attribute13 => l_attchmt_rec_type.ATTRIBUTE13,
1214 X_Attribute14 => l_attchmt_rec_type.ATTRIBUTE14,
1215 X_Attribute15 => l_attchmt_rec_type.ATTRIBUTE15,
1216
1217
1218 /* columns necessary for creating a document on the fly */
1219 X_datatype_id => l_attchmt_rec_type.DATATYPE_ID,
1220 X_category_id => l_attchmt_rec_type.CATEGORY_ID,
1221 X_security_type => l_attchmt_rec_type.SECURITY_TYPE,
1222 X_security_id => l_attchmt_rec_type.SECURITY_ID,
1223 X_publish_flag => l_attchmt_rec_type.PUBLISH_FLAG,
1224 X_image_type => l_attchmt_rec_type.IMAGE_TYPE,
1225 X_storage_type => l_attchmt_rec_type.STORAGE_TYPE,
1226 X_usage_type => l_attchmt_rec_type.USAGE_TYPE,
1227 X_start_date_active => l_attchmt_rec_type.START_DATE_ACTIVE,
1228 X_end_date_active => l_attchmt_rec_type.END_DATE_ACTIVE,
1229 X_language => l_attchmt_rec_type.l_LANGUAGE,
1230 X_description => l_attchmt_rec_type.DESCRIPTION,
1231 X_file_name => l_attchmt_rec_type.FILE_NAME,
1232 X_media_id => l_attchmt_rec_type.MEDIA_ID,
1233 X_doc_Attribute_Category => l_attchmt_rec_type.DOC_ATTRIBUTE_CATEGORY,
1234 X_doc_Attribute1 => l_attchmt_rec_type.DOC_ATTRIBUTE1,
1235 X_doc_Attribute2 => l_attchmt_rec_type.DOC_ATTRIBUTE2,
1236 X_doc_Attribute3 => l_attchmt_rec_type.DOC_ATTRIBUTE3,
1237 X_doc_Attribute4 => l_attchmt_rec_type.DOC_ATTRIBUTE4,
1238 X_doc_Attribute5 => l_attchmt_rec_type.DOC_ATTRIBUTE5,
1239 X_doc_Attribute6 => l_attchmt_rec_type.DOC_ATTRIBUTE6,
1240 X_doc_Attribute7 => l_attchmt_rec_type.DOC_ATTRIBUTE7,
1241 X_doc_Attribute8 => l_attchmt_rec_type.DOC_ATTRIBUTE8,
1242 X_doc_Attribute9 => l_attchmt_rec_type.DOC_ATTRIBUTE9,
1243 X_doc_Attribute10 => l_attchmt_rec_type.DOC_ATTRIBUTE10,
1244 X_doc_Attribute11 => l_attchmt_rec_type.DOC_ATTRIBUTE11,
1245 X_doc_Attribute12 => l_attchmt_rec_type.DOC_ATTRIBUTE12,
1246 X_doc_Attribute13 => l_attchmt_rec_type.DOC_ATTRIBUTE13,
1247 X_doc_Attribute14 => l_attchmt_rec_type.DOC_ATTRIBUTE14,
1248 X_doc_Attribute15 => l_attchmt_rec_type.DOC_ATTRIBUTE15,
1249 X_url => l_attchmt_rec_type.URL,
1250 X_title => l_attchmt_rec_type.TITLE
1251 );
1252
1253 END LOOP;
1254 CLOSE c_trx_attm;
1255
1256
1257 INSERT INTO FUN_TRX_LINES
1258 (LINE_ID,
1259 TRX_ID,
1260 CREATED_BY,
1261 CREATION_DATE,
1262 LAST_UPDATED_BY,
1263 LAST_UPDATE_DATE,
1264 LAST_UPDATE_LOGIN,
1265 LINE_NUMBER,
1266 LINE_TYPE_FLAG,
1267 INIT_AMOUNT_CR,
1268 INIT_AMOUNT_DR,
1269 RECI_AMOUNT_CR,
1270 RECI_AMOUNT_DR,
1271 DESCRIPTION)
1272 SELECT l_line_id,
1273 l_header_trx_id,
1274 fnd_global.user_id,
1275 sysdate,
1276 fnd_global.user_id,
1277 sysdate,
1278 fnd_global.login_id,
1279 LINE_NUMBER,
1280 LINE_TYPE_FLAG,
1281 INIT_AMOUNT_CR,
1282 INIT_AMOUNT_DR,
1283 RECI_AMOUNT_CR,
1284 RECI_AMOUNT_DR,
1285 DESCRIPTION
1286 FROM FUN_TRX_LINES
1287 WHERE TRX_ID = l_trx_id.TRX_ID;
1288
1289 INSERT INTO FUN_DIST_LINES
1290 (TRX_ID,
1291 DIST_ID,
1292 LINE_ID,
1293 CREATED_BY,
1294 CREATION_DATE,
1295 LAST_UPDATED_BY,
1296 LAST_UPDATE_DATE,
1297 LAST_UPDATE_LOGIN,
1298 DIST_NUMBER,
1299 PARTY_ID,
1300 PARTY_TYPE_FLAG,
1301 DIST_TYPE_FLAG,
1302 BATCH_DIST_ID,
1303 AMOUNT_CR,
1304 AMOUNT_DR,
1305 CCID,
1306 DESCRIPTION,
1307 AUTO_GENERATE_FLAG,
1308 ATTRIBUTE1,
1309 ATTRIBUTE2,
1310 ATTRIBUTE3,
1311 ATTRIBUTE4,
1312 ATTRIBUTE5,
1313 ATTRIBUTE6,
1314 ATTRIBUTE7,
1315 ATTRIBUTE8,
1316 ATTRIBUTE9,
1317 ATTRIBUTE10,
1318 ATTRIBUTE11,
1319 ATTRIBUTE12,
1320 ATTRIBUTE13,
1321 ATTRIBUTE14,
1322 ATTRIBUTE15,
1323 ATTRIBUTE_CATEGORY)
1324 SELECT l_header_trx_id,
1325 FUN_DIST_LINES_S.nextval,
1326 l_line_id,
1327 fnd_global.user_id,
1328 sysdate,
1329 fnd_global.user_id,
1330 sysdate,
1331 fnd_global.login_id,
1332 DIST_NUMBER,
1333 PARTY_ID,
1334 PARTY_TYPE_FLAG,
1335 DIST_TYPE_FLAG,
1336 BATCH_DIST_ID,
1337 AMOUNT_CR,
1338 AMOUNT_DR,
1339 CCID,
1340 DESCRIPTION,
1341 AUTO_GENERATE_FLAG,
1342 ATTRIBUTE1,
1343 ATTRIBUTE2,
1344 ATTRIBUTE3,
1345 ATTRIBUTE4,
1346 ATTRIBUTE5,
1347 ATTRIBUTE6,
1348 ATTRIBUTE7,
1349 ATTRIBUTE8,
1350 ATTRIBUTE9,
1351 ATTRIBUTE10,
1352 ATTRIBUTE11,
1353 ATTRIBUTE12,
1354 ATTRIBUTE13,
1355 ATTRIBUTE14,
1356 ATTRIBUTE15,
1357 ATTRIBUTE_CATEGORY
1358 FROM FUN_DIST_LINES
1359 WHERE TRX_ID = l_trx_id.TRX_ID;
1360
1361 END LOOP;
1362
1363
1364 ------Bug#: 7129198 --- END
1365 -- Bug 9756187 Start
1366
1367 l_sqlstmt_batch:=
1368 'SELECT
1369 atth.ROWID,
1370 atth.creation_date,
1371 atth.created_by,
1372 atth.SEQ_NUM,
1373 atth.ENTITY_NAME,
1374 atth.COLUMN1,
1375 atth.PK1_VALUE,
1376 atth.PK2_VALUE,
1377 atth.PK3_VALUE,
1378 atth.PK4_VALUE,
1379 atth.PK5_VALUE,
1380 atth.AUTOMATICALLY_ADDED_FLAG,
1381 atth.REQUEST_ID,
1382 atth.PROGRAM_APPLICATION_ID,
1383 atth.PROGRAM_ID,
1384 atth.PROGRAM_UPDATE_DATE,
1385 atth.ATTRIBUTE_CATEGORY,
1386 atth.ATTRIBUTE1,
1387 atth.ATTRIBUTE2,
1388 atth.ATTRIBUTE3,
1389 atth.ATTRIBUTE4,
1390 atth.ATTRIBUTE5,
1391 atth.ATTRIBUTE6,
1392 atth.ATTRIBUTE7,
1393 atth.ATTRIBUTE8,
1394 atth.ATTRIBUTE9,
1395 atth.ATTRIBUTE10,
1396 atth.ATTRIBUTE11,
1397 atth.ATTRIBUTE12,
1398 atth.ATTRIBUTE13,
1399 atth.ATTRIBUTE14,
1400 atth.ATTRIBUTE15,
1401
1402 docs.DATATYPE_ID,
1403 docs.CATEGORY_ID,
1404 docs.SECURITY_TYPE,
1405 docs.SECURITY_ID,
1406 docs.PUBLISH_FLAG,
1407 docs.IMAGE_TYPE,
1408 docs.STORAGE_TYPE,
1409 docs.USAGE_TYPE,
1410 docs.START_DATE_ACTIVE,
1411 docs.END_DATE_ACTIVE,
1412
1413 tl.LANGUAGE,
1414 tl.DESCRIPTION,
1415 docs.FILE_NAME,
1416 docs.MEDIA_ID,
1417 tl.DOC_ATTRIBUTE_CATEGORY,
1418 tl.DOC_ATTRIBUTE1,
1419 tl.DOC_ATTRIBUTE2,
1420 tl.DOC_ATTRIBUTE3,
1421 tl.DOC_ATTRIBUTE4,
1422 tl.DOC_ATTRIBUTE5,
1423 tl.DOC_ATTRIBUTE6,
1424 tl.DOC_ATTRIBUTE7,
1425 tl.DOC_ATTRIBUTE8,
1426 tl.DOC_ATTRIBUTE9,
1427 tl.DOC_ATTRIBUTE10,
1428 tl.DOC_ATTRIBUTE11,
1429 tl.DOC_ATTRIBUTE12,
1430 tl.DOC_ATTRIBUTE13,
1431 tl.DOC_ATTRIBUTE14,
1432 tl.DOC_ATTRIBUTE15,
1433 docs.URL,
1434 tl.TITLE
1435
1436 from fnd_documents docs,
1437 FND_ATTACHED_DOCUMENTS atth,
1438 fnd_documents_tl tl
1439
1440 where entity_name = ''FUN_TRX_BATCHES''
1441 and tl.LANGUAGE=''' || icx_language || '''
1442 and pk1_value=''' || l_partial_batch.BATCH_ID || '''
1443 and docs.document_id = atth.document_id
1444 and tl.document_id = atth.document_id';
1445
1446
1447 OPEN c_batch_attm FOR l_sqlstmt_batch;
1448 LOOP
1449 FETCH c_batch_attm INTO l_batch_attchmt_rec_type;
1450 EXIT WHEN c_batch_attm%NOTFOUND;
1451
1452 select FND_ATTACHED_DOCUMENTS_S.nextval into l_batch_ATTACHED_DOCUMENT_ID from dual;
1453 l_batch_DOCUMENT_ID := NULL;
1454
1455 fnd_attached_documents_pkg.Insert_Row(
1456 X_Rowid => l_batch_attchmt_rec_type.ROWID,
1457 X_attached_document_id => l_batch_ATTACHED_DOCUMENT_ID,
1458 X_document_id => l_batch_DOCUMENT_ID,
1459 X_creation_date => l_batch_attchmt_rec_type.creation_date,
1460 X_created_by => l_batch_attchmt_rec_type.created_by,
1461
1462 X_last_update_date => sysdate,
1463 X_last_updated_by => FND_GLOBAL.USER_ID,
1464 X_last_update_login => FND_GLOBAL.USER_ID,
1465 X_seq_num => l_batch_attchmt_rec_type.SEQ_NUM,
1466 X_entity_name => l_batch_attchmt_rec_type.ENTITY_NAME,
1467 X_column1 => l_batch_attchmt_rec_type.COLUMN1,
1468 X_pk1_value => l_batch_id,
1469 X_pk2_value => l_batch_attchmt_rec_type.PK2_VALUE,
1470 X_pk3_value => l_batch_attchmt_rec_type.PK3_VALUE,
1471 X_pk4_value => l_batch_attchmt_rec_type.PK4_VALUE,
1472 X_pk5_value => l_batch_attchmt_rec_type.PK5_VALUE,
1473 X_automatically_added_flag => l_batch_attchmt_rec_type.AUTOMATICALLY_ADDED_FLAG,
1474 X_request_id => l_batch_attchmt_rec_type.REQUEST_ID,
1475 X_program_application_id => l_batch_attchmt_rec_type.PROGRAM_APPLICATION_ID,
1476 X_program_id => l_batch_attchmt_rec_type.PROGRAM_ID,
1477 X_program_update_date => l_batch_attchmt_rec_type.PROGRAM_UPDATE_DATE,
1478 X_Attribute_Category => l_batch_attchmt_rec_type.ATTRIBUTE_CATEGORY,
1479 X_Attribute1 => l_batch_attchmt_rec_type.ATTRIBUTE1,
1480 X_Attribute2 => l_batch_attchmt_rec_type.ATTRIBUTE2,
1481 X_Attribute3 => l_batch_attchmt_rec_type.ATTRIBUTE3,
1482 X_Attribute4 => l_batch_attchmt_rec_type.ATTRIBUTE4,
1483 X_Attribute5 => l_batch_attchmt_rec_type.ATTRIBUTE5,
1484 X_Attribute6 => l_batch_attchmt_rec_type.ATTRIBUTE6,
1485 X_Attribute7 => l_batch_attchmt_rec_type.ATTRIBUTE7,
1486 X_Attribute8 => l_batch_attchmt_rec_type.ATTRIBUTE8,
1487 X_Attribute9 => l_batch_attchmt_rec_type.ATTRIBUTE9,
1488 X_Attribute10 => l_batch_attchmt_rec_type.ATTRIBUTE10,
1489 X_Attribute11 => l_batch_attchmt_rec_type.ATTRIBUTE11,
1490 X_Attribute12 => l_batch_attchmt_rec_type.ATTRIBUTE12,
1491 X_Attribute13 => l_batch_attchmt_rec_type.ATTRIBUTE13,
1492 X_Attribute14 => l_batch_attchmt_rec_type.ATTRIBUTE14,
1493 X_Attribute15 => l_batch_attchmt_rec_type.ATTRIBUTE15,
1494
1495
1496 /* columns necessary for creating a document on the fly */
1497 X_datatype_id => l_batch_attchmt_rec_type.DATATYPE_ID,
1498 X_category_id => l_batch_attchmt_rec_type.CATEGORY_ID,
1499 X_security_type => l_batch_attchmt_rec_type.SECURITY_TYPE,
1500 X_security_id => l_batch_attchmt_rec_type.SECURITY_ID,
1501 X_publish_flag => l_batch_attchmt_rec_type.PUBLISH_FLAG,
1502 X_image_type => l_batch_attchmt_rec_type.IMAGE_TYPE,
1503 X_storage_type => l_batch_attchmt_rec_type.STORAGE_TYPE,
1504 X_usage_type => l_batch_attchmt_rec_type.USAGE_TYPE,
1505 X_language => l_batch_attchmt_rec_type.l_LANGUAGE,
1506 X_description => l_batch_attchmt_rec_type.DESCRIPTION,
1507 X_file_name => l_batch_attchmt_rec_type.FILE_NAME,
1508 X_media_id => l_batch_attchmt_rec_type.MEDIA_ID,
1509 X_doc_Attribute_Category => l_batch_attchmt_rec_type.DOC_ATTRIBUTE_CATEGORY,
1510 X_doc_Attribute1 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE1,
1511 X_doc_Attribute2 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE2,
1512 X_doc_Attribute3 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE3,
1513 X_doc_Attribute4 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE4,
1514 X_doc_Attribute5 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE5,
1515 X_doc_Attribute6 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE6,
1516 X_doc_Attribute7 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE7,
1517 X_doc_Attribute8 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE8,
1518 X_doc_Attribute9 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE9,
1519 X_doc_Attribute10 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE10,
1520 X_doc_Attribute11 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE11,
1521 X_doc_Attribute12 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE12,
1522 X_doc_Attribute13 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE13,
1523 X_doc_Attribute14 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE14,
1524 X_doc_Attribute15 => l_batch_attchmt_rec_type.DOC_ATTRIBUTE15,
1525 X_create_doc => 'N',
1526 X_url => l_batch_attchmt_rec_type.URL,
1527 X_title => l_batch_attchmt_rec_type.TITLE
1528 );
1529 END LOOP;
1530 -- Bug 9756187 End
1531
1532 --Update Total of the newly created batch
1533 SELECT SUM(INIT_AMOUNT_DR),
1534 SUM (INIT_AMOUNT_CR)
1535 INTO l_running_total_dr, l_running_total_cr
1536 FROM FUN_TRX_HEADERS
1537 WHERE BATCH_ID = l_batch_id;
1538
1539 UPDATE FUN_TRX_BATCHES
1540 SET RUNNING_TOTAL_DR = l_running_total_dr,
1541 RUNNING_TOTAL_CR = l_running_total_cr
1542 WHERE BATCH_ID = l_batch_id;
1543
1544 -- Update status of old transactions
1545 UPDATE FUN_TRX_HEADERS
1546 SET STATUS = 'REJECTED',
1547 REJECT_REASON = 'Swept to New Batch ' || l_batch_number,
1548 LAST_UPDATED_BY = fnd_global.user_id,
1549 LAST_UPDATE_DATE = sysdate,
1550 LAST_UPDATE_LOGIN = fnd_global.login_id
1551 WHERE BATCH_ID = l_partial_batch.BATCH_ID
1552 AND STATUS IN ('SENT','ERROR','RECEIVED');
1553
1554 -- Update status of old batch to complete
1555 UPDATE FUN_TRX_BATCHES
1556 SET STATUS = DECODE ((SELECT 1
1557 FROM DUAL
1558 WHERE EXISTS (SELECT 'X' FROM FUN_TRX_HEADERS
1559 WHERE BATCH_ID = l_partial_batch.BATCH_ID
1560 AND STATUS NOT IN ('COMPLETE', 'REJECTED'))), 1, 'SENT', 'COMPLETE'),
1561 LAST_UPDATED_BY = fnd_global.user_id,
1562 LAST_UPDATE_DATE = sysdate,
1563 LAST_UPDATE_LOGIN = fnd_global.login_id
1564 WHERE BATCH_ID = l_partial_batch.BATCH_ID;
1565
1566 END LOOP;
1567
1568 END sweep_partial_batches;
1569
1570 /***********************************************
1571 * Procedure Sweep_Transactions :
1572 * This API sweeps transactions from the given period to specified next *
1573 * open period. *
1574 * *
1575 ***************************************************/
1576 PROCEDURE sweep_transactions
1577 (
1578 p_errbuff OUT NOCOPY VARCHAR2,
1579 p_retcode OUT NOCOPY NUMBER,
1580 p_api_version IN VARCHAR2,
1581 p_period_name IN VARCHAR2,
1582 p_trx_type_id IN NUMBER,
1583 p_sweep_GL_date IN DATE,
1584 p_debug IN VARCHAR2,
1585 p_close IN VARCHAR2
1586 ) IS
1587 cursor c_chk_open1(l_trx_type_id in Number)
1588 is
1589 Select 1 from dual where exists
1590 (Select 'X' from
1591 fun_period_statuses fps,fun_system_options fso where p_sweep_GL_date
1592 >= fps.start_date and p_sweep_GL_date <= fps.end_date and
1593 status = 'O' and trx_type_id = l_trx_type_id
1594 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1595 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1596 cursor c_chk_prd_open1(l_prd_name in Varchar2,l_trx_type_id in
1597 Number) is
1598 Select 1 from dual where exists
1599 (Select 'X' from
1600 fun_period_statuses fps,fun_system_options fso where period_name = l_prd_name and
1601 trx_type_id = l_trx_type_id and status IN ('O','S')
1602 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1603 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1604 cursor c_chk_open2(l_prd_name in Varchar2) is
1605 Select 1 from dual where exists
1606 (Select 'X'
1607 from
1608 fun_period_statuses
1609 fps1, fun_period_statuses fps2,fun_system_options fso where p_sweep_GL_date
1610 >= fps1.start_date and p_sweep_GL_date <= fps1.end_date and
1611 fps1.status = 'O' and fps2.period_name = l_prd_name and fps2.status = 'S'
1612 and fps1.trx_type_id = fps2.trx_type_id
1613 AND fps1.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1614 AND fps1.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1615 AND fps2.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1616 AND fps2.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1617 cursor c_chk_prd_open2(l_prd_name in Varchar2) is
1618 Select 1 from dual where exists
1619 (Select 'X'
1620 from
1621 fun_period_statuses fps,fun_system_options fso where period_name = l_prd_name and status = 'O'
1622 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1623 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'));
1624 l_count number;
1625 l_api_name constant varchar2(30) := 'SWEEPING_TRANSACTIONS';
1626 l_api_version CONSTANT NUMBER := 1.0;
1627 BEGIN
1628
1629 -- Bug 11729217 Modified the p_api_version type to varchar2 and modified the below api call to pass l_api_verison
1630
1631 Print(' +++ Start of Sweeping Transactions +++');
1632 G_debug := nvl(p_debug,'Y');
1633 -- Standard Call to check for API compatibility
1634 IF NOT FND_API.Compatible_API_Call (l_api_version,
1635 l_api_version,
1636 l_api_name,
1637 G_PKG_NAME)
1638 THEN
1639 Print('Non compatible API call');
1640 p_errbuff := 'Non Compatible API';
1641 RAISE FND_API.G_EXC_ERROR;
1642 END IF;
1643 /* Validate: Whether Required Fields Passed */
1644 IF ( P_api_version IS NULL OR
1645 p_sweep_GL_Date IS NULL OR
1646 p_period_name IS NULL) THEN
1647 p_errbuff := 'Required Fields not Passed';
1648 Print('REQUIRED FIELDS NOT PASSED');
1649 Raise FND_API.G_EXC_ERROR;
1650 END IF;
1651 /*
1652 Validate the validity of passing parameters>
1653 */
1654 if (p_trx_type_id is not null) then
1655 /* Validate whether period passed is open */
1656 Print('Validation: Period to be sweeped should be Open Period');
1657 open c_chk_prd_open1(p_period_name,p_trx_type_id);
1658 fetch c_chk_prd_open1 into l_count;
1659 if (c_chk_prd_open1%notfound) then
1660 p_errbuff := 'Period name not in open period for the trx';
1661 Print('Period name not in open period for the trx');
1662 Update fun_period_statuses set status = 'O' where
1663 trx_type_id = p_trx_type_id and period_name = p_period_name;
1664 Raise FND_API.G_EXC_ERROR;
1665 End If;
1666 close c_chk_prd_open1;
1667 Print('Updating Period Status to Sweep In Progress');
1668 /* Update period to Sweep In Progress */
1669 Update fun_period_statuses set status = 'S' where
1670 trx_type_id = p_trx_type_id and period_name = p_period_name
1671 AND (inteco_calendar,inteco_period_type) IN
1672 (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
1673 Print('Validation: GL_DATE Passed should be Open Period');
1674 /* Whether GL_DATE passed is in Open Period */
1675 open c_chk_open1(p_trx_type_id);
1676 fetch c_chk_open1 into l_count;
1677 if (c_chk_open1%notfound) then
1678 p_errbuff := 'GL DATE Passed is not in Open Period';
1679 Print('GL_DATE Passed is not in Open Period');
1680 Update fun_period_statuses set status = 'O' where
1681 trx_type_id = p_trx_type_id and period_name = p_period_name;
1682 Raise FND_API.G_EXC_ERROR;
1683 End If;
1684 close c_chk_open1;
1685 Print('Sweeping the transactions');
1686 /* Update the Periods */
1687 Update fun_trx_batches set gl_date =
1688 p_sweep_GL_date Where gl_date >= (select start_date from
1689 fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1690 fps.trx_type_id = p_trx_type_id
1691 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1692 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')) and gl_Date <=
1693 (select end_date from fun_period_statuses fps,fun_system_options fso where fps.period_name
1694 = p_period_name and fps.trx_type_id = p_trx_type_id
1695 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1696 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1697 And trx_type_id = p_trx_type_id
1698 And batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
1699 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED') AND h2.batch_id = batch_id)
1700 and status NOT IN ('COMPLETE', 'NEW'); -- Bug No : 6880343
1701
1702 -- 6892783: Added to Sweep Partially Complete Batches
1703 sweep_partial_batches(p_errbuff, p_period_name, p_trx_type_id, p_sweep_GL_date);
1704
1705 /* Update the Status of Period as Closed */
1706 if (nvl(p_close,'Y') = 'Y') then
1707 Update fun_period_statuses set status = 'C' where
1708 trx_type_id = p_trx_type_id and period_name = p_period_name
1709 AND (inteco_calendar,inteco_period_type) IN (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') from fun_system_options);
1710 Print('Closing for Period ');
1711 End If;
1712 Else
1713 /* Validate whether period passed is open */
1714 open c_chk_prd_open2(p_period_name);
1715 fetch c_chk_prd_open2 into l_count;
1716 if (c_chk_prd_open2%notfound) then
1717 p_errbuff := 'Period name not in open period for the trx';
1718 Print('Period name not in open period for the trx');
1719 Raise FND_API.G_EXC_ERROR;
1720 End If;
1721 close c_chk_prd_open2;
1722 /* Update period to Sweep In Progress */
1723 Update fun_period_statuses set status = 'S' where
1724 period_name = p_period_name and status = ('O');
1725 /* Whether GL_DATE passed is in Open Period */
1726 open c_chk_open2(p_period_name);
1727 fetch c_chk_open2 into l_count;
1728 if (c_chk_open2%notfound) then
1729 p_errbuff := 'GL DATE Passed is not in Open Period';
1730 Print('GL_DATE Passed is not in Open Period');
1731 Raise FND_API.G_EXC_ERROR;
1732 End If;
1733 close c_chk_open2;
1734 /* Sweep the transactions */
1735 Update fun_trx_batches set gl_date = p_sweep_GL_date
1736 Where gl_date >= (select distinct start_date from
1737 fun_period_statuses fps,fun_system_options fso where fps.period_name = p_period_name and
1738 fps.trx_type_id = p_trx_type_id
1739 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1740 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1741 and gl_Date <= (select distinct end_date from fun_period_statuses fps,fun_system_options fso
1742 where fps.period_name = p_period_name and
1743 fps.trx_type_id = p_trx_type_id
1744 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1745 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1746 AND trx_type_id IN (select trx_type_id from fun_period_statuses fps,fun_system_options fso
1747 where fps.period_name = p_period_name and
1748 fps.trx_type_id = p_trx_type_id
1749 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1750 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~'))
1751 And batch_id not in (SELECT h2.batch_id FROM fun_trx_headers h2 WHERE h2.status IN ('APPROVED', 'COMPLETE', 'XFER_RECI_GL',
1752 'XFER_AR', 'XFER_INI_GL','XFER_AP','REJECTED') AND h2.batch_id = batch_id)
1753 and status NOT IN ('COMPLETE', 'NEW'); -- Bug No : 6880343
1754
1755 -- 6892783: Added to Sweep Partially Complete Batches
1756 sweep_partial_batches(p_errbuff, p_period_name, p_trx_type_id, p_sweep_GL_date);
1757
1758 /* Close the Period */
1759 if (nvl(p_close,'Y') = 'Y') then
1760 Update fun_period_statuses set status = 'C' where period_name =
1761 p_period_name and status in ('S')
1762 AND (inteco_calendar,inteco_period_type) IN (SELECT nvl(inteco_calendar,'~~'),nvl(inteco_period_type,'~~') FROM fun_system_options);
1763 Print('Closing for Period for all trx types');
1764 End If;
1765 End If; /* if p_trx_type_id */
1766 p_errbuff := null;
1767 p_retcode := 0;
1768 if (c_chk_open1%isopen) then
1769 close c_chk_open1;
1770 end if;
1771 if (c_chk_open2%isopen) then
1772 close c_chk_open2;
1773 end if;
1774 if (c_chk_prd_open1%isopen) then
1775 close c_chk_prd_open1;
1776 end if;
1777 if (c_chk_prd_open2%isopen) then
1778 close c_chk_prd_open2;
1779 end if;
1780 EXCEPTION
1781 WHEN FND_API.G_EXC_ERROR THEN
1782 p_retcode := 2;
1783 /* As we are updating the status to 'S' before calling
1784 this routine when trx_type_id is not null, hence we have
1785 to set status to 'O' if error happens, hence we cannot
1786 rollback */
1787 if (p_trx_type_id is null) THEN
1788 rollback;
1789 end if;
1790 if (c_chk_open1%isopen) then
1791 close c_chk_open1;
1792 end if;
1793 if (c_chk_open2%isopen) then
1794 close c_chk_open2;
1795 end if;
1796 if (c_chk_prd_open1%isopen) then
1797 close c_chk_prd_open1;
1798 end if;
1799 if (c_chk_prd_open2%isopen) then
1800 close c_chk_prd_open2;
1801 end if;
1802 WHEN OTHERS THEN
1803 p_errbuff := SQLERRM;
1804 Print(SQLERRM);
1805 p_retcode := 2;
1806 if (c_chk_open1%isopen) then
1807 close c_chk_open1;
1808 end if;
1809 if (c_chk_open2%isopen) then
1810 close c_chk_open2;
1811 end if;
1812 if (c_chk_prd_open1%isopen) then
1813 close c_chk_prd_open1;
1814 end if;
1815 if (c_chk_prd_open2%isopen) then
1816 close c_chk_prd_open2;
1817 end if;
1818 END sweep_transactions;
1819 PROCEDURE insert_details_for_years (p_per_year number , p_per_type varchar2 , p_per_set_name varchar2,p_trx_type_id number)
1820 AS
1821 BEGIN
1822 INSERT INTO fun_period_statuses
1823 (PERIOD_NAME
1824 , PERIOD_YEAR
1825 , START_DATE
1826 , END_DATE
1827 ,YEAR_START_DATE
1828 ,QUARTER_START_DATE
1829 ,STATUS
1830 ,TRX_TYPE_ID
1831 ,PERIOD_NUM
1832 ,CREATED_BY
1833 ,LAST_UPDATED_BY
1834 ,LAST_UPDATE_DATE
1835 ,LAST_UPDATE_LOGIN
1836 ,CREATION_DATE
1837 ,INTECO_CALENDAR
1838 ,INTECO_PERIOD_TYPE
1839 ) SELECT period_name ,period_year, START_DATE
1840 , END_DATE
1841 ,YEAR_START_DATE
1842 ,QUARTER_START_DATE
1843 ,'N'
1844 ,p_trx_type_id
1845 ,PERIOD_NUM
1846 ,FND_GLOBAL.LOGIN_ID
1847 ,FND_GLOBAL.LOGIN_ID
1848 ,SYSDATE
1849 ,FND_GLOBAL.LOGIN_ID
1850 ,sysdate
1851 ,inteco_calendar
1852 ,inteco_period_type
1853 from gl_periods,fun_system_options
1854 where period_set_name = p_per_set_name and period_type = p_per_type and
1855 period_year >p_per_year;
1856 END;
1857 PROCEDURE insert_details_for_periods (p_per_year number , p_per_type varchar2 , p_per_set_name varchar2 , p_period_num number,p_trx_type_id number )
1858 AS
1859 BEGIN
1860 INSERT INTO fun_period_statuses
1861 (PERIOD_NAME
1862 , PERIOD_YEAR
1863 , START_DATE
1864 , END_DATE
1865 ,YEAR_START_DATE
1866 ,QUARTER_START_DATE
1867 ,STATUS
1868 ,TRX_TYPE_ID
1869 ,PERIOD_NUM
1870 ,CREATED_BY
1871 ,LAST_UPDATED_BY
1872 ,LAST_UPDATE_DATE
1873 ,LAST_UPDATE_LOGIN
1874 ,CREATION_DATE
1875 ,INTECO_CALENDAR
1876 ,INTECO_PERIOD_TYPE
1877 ) SELECT period_name ,period_year, START_DATE
1878 , END_DATE
1879 ,YEAR_START_DATE
1880 ,QUARTER_START_DATE
1881 ,'N'
1882 ,p_trx_type_id
1883 ,PERIOD_NUM
1884 ,FND_GLOBAL.LOGIN_ID
1885 ,FND_GLOBAL.LOGIN_ID
1886 ,SYSDATE
1887 ,FND_GLOBAL.LOGIN_ID
1888 ,sysdate
1889 ,INTECO_CALENDAR
1890 ,INTECO_PERIOD_TYPE
1891 from gl_periods,
1892 fun_system_options
1893 where period_set_name = p_per_set_name and period_type = p_per_type and
1894 period_year =p_per_year and period_num > p_period_num ;
1895 END;
1896 PROCEDURE sync_calendars IS
1897 CURSOR c_min_gl_per_yr IS
1898 select min(period_year) from gl_periods gl, fun_system_options fun
1899 where
1900 gl.period_set_name = fun.inteco_calendar
1901 and gl.period_type = fun.inteco_period_type ;
1902 CURSOR c_max_gl_per_yr IS
1903 select max(period_year) from gl_periods gl, fun_system_options fun
1904 where
1905 gl.period_set_name = fun.inteco_calendar
1906 and gl.period_type = fun.inteco_period_type ;
1907 CURSOR c_max_gl_per_num(c_per_year number)IS
1908 select max(period_num) from gl_periods gl , fun_system_options fun
1909 where
1910 gl.period_set_name = fun.inteco_calendar
1911 and gl.period_type = fun.inteco_period_type
1912 and gl.period_year = c_per_year;
1913 CURSOR c_max_fun_per_yr IS
1914 select max(period_year) period_year,trx_type_id from fun_period_statuses fps, fun_system_options fso
1915 WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1916 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1917 group by trx_type_id ;
1918 CURSOR c_max_fun_per_num(c_per_yr number,c_trx_type number ) IS
1919 select max(period_num) from fun_period_statuses fps,fun_system_options fso
1920 where period_year = c_per_yr
1921 and trx_type_id = c_trx_type
1922 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1923 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~');
1924 CURSOR c_inexist_fun_trx(c_per_year number,c_per_name varchar2 ) IS
1925 select trx_type_id from fun_trx_types_vl v
1926 where not exists
1927 ( select fps.trx_type_id from fun_period_statuses fps,fun_system_options fso
1928 WHERE fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1929 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~') and fps.period_year = c_per_year
1930 and fps.period_name = c_per_name
1931 and fps.trx_type_id = v.trx_type_id);
1932
1933 l_min_gl_year number;
1934 l_gl_year number;
1935 l_gl_num number;
1936 l_sys_opt_cal_name varchar2(50);
1937 l_sys_opt_per_type varchar2(50);
1938 l_fun_num number;
1939 --Bug: 7006981
1940 l_max_fun_year number(15,0);
1941 l_fun_trx_type_id number(15,0);
1942 l_PERIOD_NUM number(15,0);
1943 l_period_name VARCHAR2(15);
1944 -- End Bug: 7006981
1945 PRAGMA autonomous_transaction;
1946 BEGIN
1947 /********* Open cursor for gl details *****************/
1948 OPEN c_max_gl_per_yr;
1949 FETCH c_max_gl_per_yr into l_gl_year ;
1950 CLOSE c_max_gl_per_yr;
1951 OPEN c_max_gl_per_num(l_gl_year);
1952 FETCH c_max_gl_per_num into l_gl_num ;
1953 CLOSE c_max_gl_per_num;
1954 SELECT inteco_calendar,inteco_period_type into l_sys_opt_cal_name , l_sys_opt_per_type from fun_system_options;
1955 /************* for inexistent trx types ********************/
1956 OPEN c_min_gl_per_yr;
1957 FETCH c_min_gl_per_yr into l_min_gl_year ;
1958 CLOSE c_min_gl_per_yr;
1959 --Bug 7006981
1960
1961 OPEN c_max_fun_per_yr;
1962 FETCH c_max_fun_per_yr INTO l_max_fun_year,l_fun_trx_type_id;
1963 IF c_max_fun_per_yr%notfound THEN
1964 for l_inexist_fun_trx in c_inexist_fun_trx(0, 'No FUN Periods')
1965 loop
1966 /***** Call procedure to insert for the year passed insert_details_for_years ****/
1967 --Bug: 6512412. Passing l_min_gl_year-1 instead of l_min_gl_year.
1968 insert_details_for_years (p_per_year => (l_min_gl_year-1),
1969 p_per_type => l_sys_opt_per_type,
1970 p_per_set_name =>l_sys_opt_cal_name ,p_trx_type_id => l_inexist_fun_trx.trx_type_id);
1971 end loop;
1972 ELSE
1973 select PERIOD_NUM, period_name
1974 into l_PERIOD_NUM, l_period_name
1975 from
1976 (select min(fps.PERIOD_NUM) PERIOD_NUM, fps.period_name from fun_period_statuses fps, fun_system_options fso
1977 where fps.period_year = l_max_fun_year
1978 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
1979 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~')
1980 group by fps.period_name
1981 order by 1 asc )
1982 where rownum = 1;
1983 --call modified with l_max_fun_year, l_PERIOD_NUM;
1984 for l_inexist_fun_trx in c_inexist_fun_trx( l_max_fun_year, l_period_name)
1985 loop
1986 /***** Call procedure to insert for the year passed insert_details_for_years ****/
1987 --Bug: 6512412. Passing l_min_gl_year-1 instead of l_min_gl_year.
1988 insert_details_for_years (p_per_year => (l_min_gl_year-1),
1989 p_per_type => l_sys_opt_per_type,
1990 p_per_set_name =>l_sys_opt_cal_name ,p_trx_type_id => l_inexist_fun_trx.trx_type_id);
1991 end loop;
1992 END IF;
1993 CLOSE c_max_fun_per_yr;
1994 --End 7006981
1995
1996 FOR l_max_fun_per_yr in c_max_fun_per_yr
1997 loop
1998 /********* Case 1: All Periods defined correct in this trx **********************/
1999 if (l_max_fun_per_yr.period_year = l_gl_year ) then
2000 OPEN c_max_fun_per_num(l_max_fun_per_yr.period_year,l_max_fun_per_yr.trx_type_id);
2001 FETCH c_max_fun_per_num into l_fun_num;
2002 if (l_fun_num < l_gl_num) then
2003 /***** Call procedure to insert for the year passed insert_details_for_years****/
2004 insert_details_for_periods (p_per_year => (l_gl_year), -- bug 6761607.
2005 p_per_type => l_sys_opt_per_type,
2006 p_per_set_name =>l_sys_opt_cal_name ,
2007 p_period_num => l_fun_num, p_trx_type_id => l_max_fun_per_yr.trx_type_id);
2008 end if ;
2009 CLOSE c_max_fun_per_num;
2010 /********* Case 2: All Periods not defined from particular year onwards in this trx **********************/
2011 elsif (l_max_fun_per_yr.period_year < l_gl_year ) then
2012 OPEN c_max_fun_per_num(l_max_fun_per_yr.period_year,l_max_fun_per_yr.trx_type_id) ;
2013 FETCH c_max_fun_per_num into l_fun_num;
2014 -- bug 6761607.
2015 -- if (l_fun_num < l_gl_num ) then
2016 /***** Call procedure to insert for the year passed insert_details_for_periods ****/
2017 insert_details_for_periods (p_per_year => l_max_fun_per_yr.period_year,
2018 p_per_type => l_sys_opt_per_type,
2019 p_per_set_name =>l_sys_opt_cal_name ,
2020 p_period_num => l_fun_num, p_trx_type_id => l_max_fun_per_yr.trx_type_id);
2021 /***** Call procedure to insert for the year passed insert_details_for_years ****/
2022 -- bug 6761607.
2023 /*insert_details_for_years (p_per_year => l_max_fun_per_yr.period_year,
2024 p_per_type => l_sys_opt_per_type,
2025 p_per_set_name =>l_sys_opt_cal_name ,p_trx_type_id => l_max_fun_per_yr.trx_type_id); */
2026 -- end if ; bug 6761607.
2027 /***** Call procedure to insert for the year passed insert_details_for_years ****/
2028 insert_details_for_years (p_per_year => l_max_fun_per_yr.period_year,
2029 p_per_type => l_sys_opt_per_type,
2030 p_per_set_name =>l_sys_opt_cal_name ,p_trx_type_id => l_max_fun_per_yr.trx_type_id);
2031 CLOSE c_max_fun_per_num;
2032 end if;
2033 end loop;
2034 COMMIT;
2035 end;
2036 /***********************************************
2037 * Function get_fun_prd_status :
2038 * This API accepts date and trx type and returns status in Intercompany *
2039 * *
2040 ***************************************************/
2041 FUNCTION get_fun_prd_status(p_date Date, p_trx_type_id number)
2042 RETURN VARCHAR2 IS
2043 CURSOR c_calendar_defined IS
2044 SELECT fso.inteco_calendar,fso.inteco_period_type
2045 FROM fun_system_options fso;
2046 CURSOR c_select_status IS
2047 select fps.status
2048 from fun_period_statuses fps,fun_system_options fso,
2049 fun_trx_types_vl ftt
2050 where trunc(p_date) between fps.start_date and fps.end_date
2051 and fps.trx_type_id = ftt.trx_type_id
2052 and ftt.trx_type_id = p_trx_type_id
2053 AND fps.status <> 'N'
2054 AND fps.inteco_calendar=nvl(fso.inteco_calendar,'~~')
2055 AND fps.inteco_period_type=nvl(fso.inteco_period_type,'~~');
2056 l_status VARCHAR2(1);
2057 l_inteco_calendar varchar2(50);
2058 l_period_type varchar2(50);
2059 BEGIN
2060
2061 OPEN c_calendar_defined;
2062 FETCH c_calendar_defined INTO l_inteco_calendar,l_period_type;
2063 IF c_calendar_defined%notfound THEN
2064 l_status:='O';
2065 ELSIF l_inteco_calendar IS NULL OR l_period_type IS NULL THEN
2066 l_status:='O';
2067 ELSE
2068 OPEN c_select_status;
2069 FETCH c_select_status into l_status;
2070 if c_select_status%notfound then
2071 l_status :='C';
2072 end if;
2073 CLOSE c_select_status;
2074 END IF;
2075 CLOSE c_calendar_defined;
2076 RETURN l_status;
2077 END;
2078 END FUN_PERIOD_STATUS_PKG; -- Package Body
2079