[Home] [Help]
PACKAGE BODY: APPS.GL_CONS_HISTORY_PKG
Source
1 PACKAGE BODY GL_CONS_HISTORY_PKG AS
2 /* $Header: glicohib.pls 120.23 2005/12/08 10:31:30 mikeward ship $ */
3
4
5 --+
6 --+ PUBLIC FUNCTIONS
7 --+
8
9 PROCEDURE Check_Calendar(X_To_Ledger_Id NUMBER,
10 X_From_Ledger_Id NUMBER) IS
11 CURSOR C1 IS
12 SELECT 'x' FROM GL_LEDGERS L1
13 WHERE L1.LEDGER_ID = X_To_Ledger_Id
14 AND L1.PERIOD_SET_NAME =
15 (SELECT L2.PERIOD_SET_NAME FROM GL_LEDGERS L2
16 WHERE L2.LEDGER_ID = X_From_Ledger_Id);
17
18 dummy VARCHAR(1);
19
20 BEGIN
21 OPEN C1;
22 FETCH C1 INTO dummy;
23 IF (C1%NOTFOUND) THEN
24 CLOSE C1;
25 fnd_message.set_name('SQLGL','GL_SAME_CALENDAR');
26 app_exception.raise_exception;
27 END IF;
28 CLOSE C1;
29 EXCEPTION
30 WHEN app_exceptions.application_exception THEN RAISE;
31 WHEN OTHERS THEN
32 fnd_message.set_name('SQLGL','Unhandled Exception');
33 fnd_message.set_token('PROCEDURE', 'Check_Unique_User_Type');
34 RAISE;
35 END Check_Calendar;
36
37 PROCEDURE Get_New_Id(next_val IN OUT NOCOPY NUMBER) IS
38
39 BEGIN
40 select GL_CONSOLIDATION_HISTORY_S.NEXTVAL
41 into next_val
42 from dual;
43
44 END Get_New_Id;
45
46
47 /* Name: first_period_of_quarter
48 * Desc: Returns the first non-adjusting period of the specified quarter
49 */
50 PROCEDURE first_period_of_quarter(
51 LedgerId NUMBER,
52 QuarterNum NUMBER,
53 QuarterYear NUMBER,
54 PeriodName IN OUT NOCOPY VARCHAR2,
55 StartDate IN OUT NOCOPY DATE,
56 ClosingStatus IN OUT NOCOPY VARCHAR2
57 ) IS
58 BEGIN
59 SELECT period_name, start_date, closing_status
60 INTO PeriodName, StartDate, ClosingStatus
61 FROM gl_period_statuses
62 WHERE application_id = 101
63 AND ledger_id = LedgerId
64 AND adjustment_period_flag = 'N'
65 AND quarter_num = QuarterNum
66 AND period_year = QuarterYear
67 AND quarter_start_date = start_date;
68 END first_period_of_quarter;
69
70
71 /* Name: first_period_of_year
72 * Desc: Returns the first non-adjusting period of the specified year
73 */
74 PROCEDURE first_period_of_year(
75 LedgerId NUMBER,
76 PeriodYear NUMBER,
77 PeriodName IN OUT NOCOPY VARCHAR2,
78 StartDate IN OUT NOCOPY DATE,
79 ClosingStatus IN OUT NOCOPY VARCHAR2
80 ) IS
81 BEGIN
82 SELECT period_name, start_date, closing_status
83 INTO PeriodName, StartDate, ClosingStatus
84 FROM gl_period_statuses
85 WHERE application_id = 101
86 AND ledger_id = LedgerId
87 AND adjustment_period_flag = 'N'
88 AND period_year = PeriodYear
89 AND year_start_date = start_date;
90 END first_period_of_year;
91
92
93 /* Name: insert_average_record
94 * Desc: Copy the standard consolidation record for average consolidation.
95 */
96 PROCEDURE insert_average_record(
97 SourceRunId NUMBER,
98 TargetRunId NUMBER,
99 AverageToPeriodName VARCHAR2,
100 AvgAmountType VARCHAR2,
101 FromDateEntered DATE
102 ) IS
103 BEGIN
104 INSERT INTO GL_CONSOLIDATION_HISTORY(
105 consolidation_run_id,
106 consolidation_id,
107 last_update_date,
108 last_updated_by,
109 last_update_login,
110 creation_date,
111 created_by,
112 from_period_name,
113 to_period_name,
114 to_currency_code,
115 method_flag,
116 run_easylink_flag,
117 run_posting_flag,
118 actual_flag,
119 from_budget_name,
120 to_budget_name,
121 from_budget_version_id,
122 to_budget_version_id,
123 average_consolidation_flag,
124 amount_type,
125 from_date,
126 target_resp_name,
127 target_user_name,
128 target_database_name
129 ) SELECT
130 TargetRunId,
131 consolidation_id,
132 last_update_date,
133 last_updated_by,
134 last_updated_by,
135 last_update_date,
136 last_updated_by,
137 from_period_name,
138 AverageToPeriodName,
139 to_currency_code,
140 method_flag,
141 run_easylink_flag,
142 run_posting_flag,
143 actual_flag,
144 NULL,
145 NULL,
146 NULL,
147 NULL,
148 'Y',
149 AvgAmountType,
150 FromDateEntered,
151 target_resp_name,
152 target_user_name,
153 target_database_name
154 FROM gl_consolidation_history
155 WHERE NOT EXISTS (SELECT 1
156 FROM gl_consolidation_history
157 WHERE consolidation_run_id = TargetRunId)
158 AND consolidation_run_id = SourceRunId;
159 END insert_average_record;
160
161
162 /* Name: insert_row
163 * Desc: Table handler for insertion.
164 */
165 PROCEDURE Insert_Row(
166 X_Usage_Code VARCHAR2,
167 X_Rowid IN OUT NOCOPY VARCHAR2,
168 X_Consolidation_Run_Id NUMBER,
169 X_StdRunId IN OUT NOCOPY NUMBER,
170 X_AvgRunId IN OUT NOCOPY NUMBER,
171 X_Consolidation_Id NUMBER,
172 X_Last_Update_Date DATE,
173 X_Last_Updated_By NUMBER,
174 X_From_Period_Name VARCHAR2,
175 X_Standard_To_Period_Name VARCHAR2,
176 X_To_Period_Name IN OUT NOCOPY VARCHAR2,
177 X_To_Currency_Code VARCHAR2,
178 X_Method_Flag VARCHAR2,
179 X_Run_Easylink_Flag VARCHAR2,
180 X_Run_Posting_Flag VARCHAR2,
181 X_Actual_Flag VARCHAR2,
182 X_From_Budget_Name VARCHAR2,
183 X_To_Budget_Name VARCHAR2,
184 X_From_Budget_Version_Id NUMBER,
185 X_To_Budget_Version_Id NUMBER,
186 X_Amount_Type_Code VARCHAR2,
187 X_Amount_Type VARCHAR2,
188 X_StdAmountType VARCHAR2,
189 X_AvgAmountType VARCHAR2,
190 X_From_Date_Entered DATE,
191 X_From_Date IN OUT NOCOPY DATE,
192 X_Average_To_Period_Name VARCHAR2,
193 X_Target_Resp_Name VARCHAR2,
194 X_Target_User_Name VARCHAR2,
195 X_Target_DB_Name VARCHAR2
196 ) IS
197 CURSOR C(Run_Id NUMBER) IS SELECT rowid FROM GL_CONSOLIDATION_HISTORY
198 WHERE consolidation_run_id = Run_Id;
199 CURSOR C2 IS SELECT gl_consolidation_history_s.nextval FROM sys.dual;
200 TempRowid VARCHAR2(60);
201 TempRunId NUMBER;
202 BEGIN
203 X_To_Period_Name := X_Standard_To_Period_Name;
204 IF (X_StdAmountType = 'EOD') THEN
205 X_From_Date := X_From_Date_Entered;
206 ELSE
207 X_From_Date := NULL;
208 END IF;
209
210 IF (X_Usage_Code IN ('S', 'B')) THEN
211 INSERT INTO GL_CONSOLIDATION_HISTORY(
212 consolidation_run_id,
213 consolidation_id,
214 last_update_date,
215 last_updated_by,
216 last_update_login,
217 creation_date,
218 created_by,
219 from_period_name,
220 to_period_name,
221 to_currency_code,
222 method_flag,
223 run_easylink_flag,
224 run_posting_flag,
225 actual_flag,
226 from_budget_name,
227 to_budget_name,
228 from_budget_version_id,
229 to_budget_version_id,
230 average_consolidation_flag,
231 amount_type,
232 from_date,
233 target_resp_name,
234 target_user_name,
235 target_database_name
236 ) VALUES (
237 X_Consolidation_Run_Id,
238 X_Consolidation_Id,
239 X_Last_Update_Date,
240 X_Last_Updated_By,
241 X_Last_Updated_By,
242 X_Last_Update_Date,
243 X_Last_Updated_By,
244 X_From_Period_Name,
245 X_Standard_To_Period_Name,
246 X_To_Currency_Code,
247 X_Method_Flag,
248 X_Run_Easylink_Flag,
249 X_Run_Posting_Flag,
250 X_Actual_Flag,
251 X_From_Budget_Name,
252 X_To_Budget_Name,
253 X_From_Budget_Version_Id,
254 X_To_Budget_Version_Id,
255 'N',
256 X_StdAmountType,
257 X_From_Date,
258 X_Target_Resp_Name,
259 X_Target_User_Name,
260 X_Target_DB_Name
261 );
262
263 OPEN C(X_Consolidation_Run_Id);
264 FETCH C INTO X_Rowid;
265 IF (C%NOTFOUND) THEN
266 CLOSE C;
267 Raise NO_DATA_FOUND;
268 END IF;
269 CLOSE C;
270
271 X_StdRunId := X_Consolidation_Run_Id;
272 END IF;
273
274 IF (X_Usage_Code IN ('A', 'B')) THEN
275 IF (X_Usage_Code = 'B') THEN
276 OPEN C2;
277 FETCH C2 INTO TempRunId;
278 CLOSE C2;
279 ELSE
280 TempRunId := X_Consolidation_Run_Id;
281 END IF;
282
283 X_AvgRunId := TempRunId;
284 END IF;
285
286 IF (X_Usage_Code = 'A') THEN
287 INSERT INTO GL_CONSOLIDATION_HISTORY(
288 consolidation_run_id,
289 consolidation_id,
290 last_update_date,
291 last_updated_by,
292 last_update_login,
293 creation_date,
294 created_by,
295 from_period_name,
296 to_period_name,
297 to_currency_code,
298 method_flag,
299 run_easylink_flag,
300 run_posting_flag,
301 actual_flag,
302 from_budget_name,
303 to_budget_name,
304 from_budget_version_id,
305 to_budget_version_id,
306 average_consolidation_flag,
307 amount_type,
308 from_date,
309 target_resp_name,
310 target_user_name,
311 target_database_name
312 ) VALUES (
313 TempRunId,
314 X_Consolidation_Id,
315 X_Last_Update_Date,
316 X_Last_Updated_By,
317 X_Last_Updated_By,
318 X_Last_Update_Date,
319 X_Last_Updated_By,
320 X_From_Period_Name,
321 X_Average_To_Period_Name,
322 X_To_Currency_Code,
323 X_Method_Flag,
324 X_Run_Easylink_Flag,
325 X_Run_Posting_Flag,
326 X_Actual_Flag,
327 NULL,
328 NULL,
329 NULL,
330 NULL,
331 'Y',
332 X_AvgAmountType,
333 X_From_Date_Entered,
334 X_Target_Resp_Name,
335 X_Target_User_Name,
336 X_Target_DB_Name
337 );
338
339 OPEN C(TempRunId);
340 FETCH C INTO TempRowid;
341 IF (C%NOTFOUND) THEN
342 CLOSE C;
343 Raise NO_DATA_FOUND;
344 END IF;
345 CLOSE C;
346
347 X_Rowid := TempRowId;
348 X_From_Date := X_From_Date_Entered;
349 X_To_Period_Name := X_Average_To_Period_Name;
350 END IF;
351
352 END Insert_Row;
353
354
355 PROCEDURE Insert_Cons_Set_Row(
356 X_Usage_Code VARCHAR2,
357 X_Rowid VARCHAR2,
358 X_Std_Amounttype VARCHAR2,
359 X_Avg_Amounttype VARCHAR2,
360 X_Consolidation_Id NUMBER,
361 X_Consolidation_Set_Id NUMBER,
362 X_Last_Updated_By NUMBER,
363 X_From_Period_Name VARCHAR2,
364 X_Standard_To_Period_Name VARCHAR2,
365 X_Average_To_Period_Name VARCHAR2,
366 X_Average_To_Start_Date DATE,
367 X_To_Currency_Code VARCHAR2,
368 X_Method_Flag VARCHAR2,
369 X_Run_Journal_Import_Flag VARCHAR2,
370 X_Audit_Mode_Flag VARCHAR2,
371 X_Summary_Journals_Flag VARCHAR2,
372 X_Run_Posting_Flag VARCHAR2,
373 X_Actual_Flag VARCHAR2,
374 X_Consolidation_Name VARCHAR2,
375 X_From_Date_Entered DATE,
376 X_From_Ledger_Id NUMBER,
377 X_To_Ledger_Id NUMBER,
378 X_Check_Batches IN OUT NOCOPY VARCHAR2,
379 X_num_conc_requests IN OUT NOCOPY NUMBER,
380 X_Target_Resp_Name VARCHAR2,
381 X_Target_User_Name VARCHAR2,
382 X_Target_DB_Name VARCHAR2,
383 X_first_request_Id IN OUT NOCOPY number,
384 X_last_request_Id IN OUT NOCOPY number,
385 X_access_set_id NUMBER
386 ) IS
387 CURSOR Select_Rowid(Runid NUMBER) IS
388 SELECT rowid
389 FROM GL_CONSOLIDATION_HISTORY
390 WHERE consolidation_run_id = Runid;
391 temp_rowid ROWID;
392 std_runid NUMBER;
393 avg_runid NUMBER;
394 std_request_id NUMBER;
395 avg_request_id NUMBER;
396 remote_flag VARCHAR2(1);
397 valid_return VARCHAR2(100);
398 wait_result VARCHAR2(200);
399 source_group_id NUMBER;
400 CIRequestId NUMBER;
401 msgbuf VARCHAR2(200) := '';
402 BEGIN
403 --+ Get the consolidation run id for standard usage
404 GL_CONS_HISTORY_PKG.Get_New_Id(std_runid);
405
406 --+ The remote flag should be Y if it is a cross-instance consolidation,
407 --+ that is, when the target dabase is not null.
408 IF (X_Target_DB_Name IS NOT NULL) THEN
409 remote_flag := 'Y';
410 ELSE
411 remote_flag := 'N';
412 END IF;
413
414 --+ Submit the request for consolidation program
415
416 IF (X_Usage_Code IN ('S', 'B')) THEN
417
418 --+ For Transactions method check to see whether
419 --+ unconsolidated batches have been successfully inserted
420 --+ in GL_CONS_BATCHES.
421
422 IF ((X_method_flag = 'T') AND (X_Check_Batches = 'Y')) THEN
423 IF NOT(GL_CONS_BATCHES_PKG.Insert_Consolidation_Batches(
424 'U',
425 X_Consolidation_Id,
426 std_runid,
427 X_Last_Updated_By,
428 X_From_Ledger_Id,
429 X_To_Ledger_Id,
430 X_From_Period_Name,
431 X_To_Currency_Code)) THEN
432
433 X_Check_Batches := 'N';
434 Return;
435 END IF;
436
437 END IF;
438
439 --+ submit a standard request.
440 std_request_id := FND_REQUEST.SUBMIT_REQUEST(
441 'SQLGL',
442 'GLCCON',
443 '',
444 '',
445 FALSE,
446 To_Char(X_consolidation_id),
447 'S',
448 X_From_Period_Name,
449 To_Char(X_From_Date_Entered, 'YYYY/MM/DD'),
450 X_Standard_To_Period_Name,
451 NULL,
452 NULL,
453 X_Std_Amounttype,
454 X_Run_Journal_Import_Flag,
455 X_Method_Flag,
456 to_char(std_runid),
457 X_Actual_Flag,
458 NULL,
459 NULL,
460 X_Audit_Mode_Flag,
461 X_Summary_Journals_Flag,
462 'Y',
463 X_Run_Posting_Flag,
464 remote_flag,
465 X_access_set_id,
466 chr(0),'','','','','','','','','',
467 '','','','','','','','','','','','','','','','',
468 '','','','','','','','','','','','','','','','',
469 '','','','','','','','','','','','','','','','',
470 '','','','','','','','','','','','','','','','',
471 '','','','','','');
472 IF (Std_Request_Id = 0) THEN
473
474 --+ submission failed
475 FND_MESSAGE.set_name('SQLGL', 'GL_CONS_SET_REQUEST_FAILED');
476 FND_MESSAGE.set_token('CONSOLIDATION',X_Consolidation_Name, FALSE);
477 APP_EXCEPTION.RAISE_EXCEPTION;
478
479 ELSE
480 --+ Set the conc_request variable
481 X_num_conc_requests := X_num_conc_requests + 1;
482 IF X_first_request_Id = 0 THEN
483 X_first_request_Id := Std_Request_Id;
484 END IF;
485
486 IF remote_flag = 'N' THEN
487 IF X_last_request_Id < Std_Request_Id THEN
488 X_last_request_Id := Std_Request_Id;
489 END IF;
490 END IF;
491
492 --+ insert into gl_consolidation_history
493 INSERT INTO GL_CONSOLIDATION_HISTORY(
494 consolidation_run_id,
495 consolidation_id,
496 consolidation_set_id,
497 last_update_date,
498 last_updated_by,
499 last_update_login,
500 creation_date,
501 created_by,
502 from_period_name,
503 to_period_name,
504 to_currency_code,
505 method_flag,
506 run_easylink_flag,
507 run_posting_flag,
508 actual_flag,
509 average_consolidation_flag,
510 amount_type,
511 from_date,
512 status,
513 request_id,
514 target_resp_name,
515 target_user_name,
516 target_database_name
517 ) VALUES (
518 std_runid,
519 X_Consolidation_Id,
520 X_Consolidation_Set_Id,
521 sysdate,
522 X_Last_Updated_By,
523 X_Last_Updated_By,
524 sysdate,
525 X_Last_Updated_By,
526 X_From_Period_Name,
527 X_Standard_To_Period_Name,
528 X_To_Currency_Code,
529 X_Method_Flag,
530 X_Run_Journal_Import_Flag,
531 X_Run_Posting_Flag,
532 X_Actual_Flag,
533 'N',
534 X_Std_AmountType,
535 X_From_Date_Entered,
536 'TS',
537 Std_Request_Id,
538 X_Target_Resp_Name,
539 X_Target_User_Name,
540 X_Target_DB_Name
541 );
542
543 --+ check whether rows inserted successfully
544 OPEN Select_Rowid(std_runid);
545 FETCH Select_Rowid INTO temp_rowid;
546 IF (Select_Rowid%NOTFOUND) THEN
547 CLOSE Select_Rowid;
548 Raise NO_DATA_FOUND;
549 END IF;
550 CLOSE Select_Rowid;
551 commit;
552 END IF; --+ request id is NOT 0
553
554 END IF; --+ Usage in 'S', 'B'
555
556 IF (X_Usage_Code IN ('A', 'B')) THEN
557 IF (X_Usage_Code = 'B') THEN
558
559 --+ If the AvgAmounttype IS NULL then the consolidation
560 --+ corresponds to a standard ledger, so don't
561 --+ submit an average consolidation.
562 IF (X_Avg_Amounttype IS NULL) THEN
563 RETURN;
564 ELSE
565 --+ Get the consolidation run id for average usage
566 GL_CONS_HISTORY_PKG.Get_New_Id(avg_runid);
567 END IF;
568 ELSE
569 avg_runid := std_runid;
570 END IF;
571
572 --+ submit a average request.
573 avg_request_id := FND_REQUEST.SUBMIT_REQUEST(
574 'SQLGL',
575 'GLCCON',
576 '',
577 '',
578 FALSE,
579 To_Char(X_consolidation_id),
580 'A',
581 X_From_Period_Name,
582 To_Char(X_From_Date_Entered, 'YYYY/MM/DD'),
583 X_Average_To_Period_Name,
584 To_Char(X_Average_To_Start_Date, 'YYYY/MM/DD'),
585 NULL,
586 X_Avg_Amounttype,
587 X_Run_Journal_Import_Flag,
588 X_Method_Flag,
589 to_char(avg_runid),
590 X_Actual_Flag,
591 NULL,
592 NULL,
593 X_Audit_Mode_Flag,
594 X_Summary_Journals_Flag,
595 'Y',
596 X_Run_Posting_Flag,
597 remote_flag,
598 X_access_set_id,
599 chr(0),'','','','','','','','','',
600 '','','','','','','','','','','','','','','','',
601 '','','','','','','','','','','','','','','','',
602 '','','','','','','','','','','','','','','','',
603 '','','','','','','','','','','','','','','','',
604 '','','','','','');
605
606 IF (avg_request_id = 0) THEN
607 --+ submission failed
608 FND_MESSAGE.set_name('SQLGL', 'GL_CONS_SET_REQUEST_FAILED');
609 FND_MESSAGE.set_token('CONSOLIDATION',X_Consolidation_Name, FALSE);
610 APP_EXCEPTION.RAISE_EXCEPTION;
611
612 ELSE
613
614 --+ Set the conc_request variable
615 X_num_conc_requests := X_num_conc_requests + 1;
616 IF X_first_request_Id = 0 THEN
617 X_first_request_Id := avg_Request_Id;
618 END IF;
619
620 IF remote_flag = 'N' THEN
621 IF X_last_request_Id < avg_Request_Id THEN
622 X_last_request_Id := avg_Request_Id;
623 END IF;
624 END IF;
625 --+ insert into gl_consolidation_history
626
627 INSERT INTO GL_CONSOLIDATION_HISTORY(
628 consolidation_run_id,
629 consolidation_id,
630 consolidation_set_id,
631 last_update_date,
632 last_updated_by,
633 last_update_login,
634 creation_date,
635 created_by,
636 from_period_name,
637 to_period_name,
638 to_currency_code,
639 method_flag,
640 run_easylink_flag,
641 run_posting_flag,
642 actual_flag,
643 average_consolidation_flag,
644 amount_type,
645 from_date,
646 status,
647 request_id,
648 target_resp_name,
649 target_user_name,
650 target_database_name
651 ) VALUES (
652 avg_runid,
653 X_Consolidation_Id,
654 X_Consolidation_Set_Id,
655 sysdate,
656 X_Last_Updated_By,
657 X_Last_Updated_By,
658 sysdate,
659 X_Last_Updated_By,
660 X_From_Period_Name,
661 X_Average_To_Period_Name,
662 X_To_Currency_Code,
663 X_Method_Flag,
664 X_Run_Journal_Import_Flag,
665 X_Run_Posting_Flag,
666 X_Actual_Flag,
667 'Y',
668 X_Avg_AmountType,
669 X_From_Date_Entered,
670 'TS',
671 avg_request_id,
672 X_Target_Resp_Name,
673 X_Target_User_Name,
674 X_Target_DB_Name
675 );
676
677 --+ check whether rows inserted successfully
678 OPEN Select_Rowid(avg_runid);
679 FETCH Select_Rowid INTO temp_rowid;
680 IF (Select_Rowid%NOTFOUND) THEN
681 CLOSE Select_Rowid;
682 Raise NO_DATA_FOUND;
683 END IF;
684 CLOSE Select_Rowid;
685 commit;
686 END IF; --+ request_id is not 0
687
688 END IF; --+ usage is 'B' or 'A'
689
690 IF (remote_flag = 'Y') and (X_Usage_Code IN ('S', 'B')) AND (std_request_id <> 0) THEN
691 --+ GL_CI_REMOTE_INVOKE_PKG.Wait_For_Request(Std_Request_Id, wait_result);
692 --+ IF wait_result = 'COMPLETE:PASS' THEN
693 --+ source_group_id := GL_CI_DATA_TRANSFER_PKG.Get_Source_Group_ID(
694 --+ X_consolidation_id, Std_RunId);
695 source_group_id := 0;
696 CIRequestId := FND_REQUEST.SUBMIT_REQUEST(
697 'SQLGL',
698 'GLCCIPRE',
699 '',
700 '',
701 FALSE,
702 X_Target_Resp_Name,
703 Std_Request_Id,
704 X_consolidation_id,
705 Std_RunId,
706 X_Standard_To_Period_Name,
707 X_To_Ledger_Id,
708 X_Target_User_Name,
709 X_Target_DB_Name,
710 source_group_id,
711 X_From_Ledger_Id,
712 X_Standard_To_Period_Name,
713 NULL,
714 X_Run_Journal_Import_Flag,
715 X_Run_Posting_Flag,
716 X_Actual_Flag,
717 Std_Request_Id,
718 X_Summary_Journals_Flag,
719 'N',
720 chr(0),'','','','','','','','','','', --+ 24 arguments so far
721 '','','','','','','','','','','','','','','','',
722 '','','','','','','','','','','','','','','','',
723 '','','','','','','','','','','','','','','','',
724 '','','','','','','','','','','','','','','','', --+ 16 in a row
725 '','','','','','','');
726
727 IF CIRequestId <> 0 THEN
728 --+ get program name
729 --+ gl_message.get(msgbuf, 'GL_US_CI_CROSSINSTANCE');
730 X_num_conc_requests := X_num_conc_requests + 1;
731 IF X_last_request_Id < CIRequestId THEN
732 X_last_request_Id := CIRequestId;
733 END IF;
734 FND_MESSAGE.set_name('SQLGL', 'GL_CONC_REQUEST_SUBMITTED');
735 FND_MESSAGE.set_token('MODULE',msgbuf, FALSE);
736 FND_MESSAGE.set_token('REQUEST_ID', TO_CHAR(CIRequestId), FALSE);
737
738 ELSE
739 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_PRE_FAIL');
740 APP_EXCEPTION.RAISE_EXCEPTION;
741 --+ FND_MESSAGE.show;
742 END IF;
743 --+ END IF; --+if wait_result is complete:pass
744 END IF;
745 IF (remote_flag = 'Y') and (X_Usage_Code IN ('A', 'B')) AND (avg_request_id <> 0) THEN
746 --+ GL_CI_REMOTE_INVOKE_PKG.Wait_For_Request(Avg_Request_Id, wait_result);
747 --+ IF wait_result = 'COMPLETE:PASS' THEN
748 --+ source_group_id := GL_CI_DATA_TRANSFER_PKG.Get_Source_Group_ID(
749 --+ X_consolidation_id, Avg_RunId);
750 --+MESSAGE('Average source group_id is ' || source_group_id);
751 source_group_id := 0;
752 CIRequestId := FND_REQUEST.SUBMIT_REQUEST(
753 'SQLGL',
754 'GLCCIPRE',
755 '',
756 '',
757 FALSE,
758 X_Target_Resp_Name,
759 Avg_Request_Id,
760 X_consolidation_id,
761 Avg_RunId,
762 X_Average_To_Period_Name,
763 X_To_Ledger_Id,
764 X_Target_User_Name,
765 X_Target_DB_Name,
766 source_group_id,
767 X_From_Ledger_Id,
768 X_Average_To_Period_Name,
769 NULL,
770 X_Run_Journal_Import_Flag,
771 X_Run_Posting_Flag,
772 X_Actual_Flag,
773 Avg_Request_Id,
774 X_Summary_Journals_Flag,
775 'N',
776 chr(0),'','','','','','','','','','', --+ 24 arguments so far
777 '','','','','','','','','','','','','','','','',
778 '','','','','','','','','','','','','','','','',
779 '','','','','','','','','','','','','','','','',
780 '','','','','','','','','','','','','','','','', --+ 16 in a row
781 '','','','','','','');
782
783 IF CIRequestId <> 0 THEN
784 X_num_conc_requests := X_num_conc_requests + 1;
785 IF X_last_request_Id < CIRequestId THEN
786 X_last_request_Id := CIRequestId;
787 END IF;
788 --+ get program name
789 --+ gl_message.get(msgbuf, 'GL_US_CI_CROSSINSTANCE');
790 FND_MESSAGE.set_name('SQLGL', 'GL_CONC_REQUEST_SUBMITTED');
791 FND_MESSAGE.set_token('MODULE',msgbuf, FALSE);
792 FND_MESSAGE.set_token('REQUEST_ID', TO_CHAR(CIRequestId), FALSE);
793 ELSE
794 FND_MESSAGE.set_name('SQLGL', 'GL_US_CI_PRE_FAIL');
795 APP_EXCEPTION.RAISE_EXCEPTION;
796 END IF;
797 --+ END IF; --+if wait_result is complete:pass
798 END IF;
799 EXCEPTION
800 WHEN app_exceptions.application_exception THEN RAISE;
801 WHEN OTHERS THEN
802 fnd_message.set_name('SQLGL','Unhandled Exception');
803 fnd_message.set_token('PROCEDURE', 'Insert_Cons_Set_Row');
804 RAISE;
805
806 END Insert_Cons_Set_Row;
807
808
809 PROCEDURE Insert_For_Budgetyear(
810 X_Consolidation_Run_Id NUMBER,
811 X_Consolidation_Id NUMBER,
812 X_Last_Update_Date DATE,
813 X_Last_Updated_By NUMBER,
814 X_From_Period_Name VARCHAR2,
815 X_To_Period_Name VARCHAR2,
816 X_To_Currency_Code VARCHAR2,
817 X_Method_Flag VARCHAR2,
818 X_Run_Easylink_Flag VARCHAR2,
819 X_Run_Posting_Flag VARCHAR2,
820 X_Actual_Flag VARCHAR2,
821 X_From_Budget_Name VARCHAR2,
822 X_To_Budget_Name VARCHAR2,
823 X_From_Budget_Version_Id NUMBER,
824 X_To_Budget_Version_Id NUMBER,
825 X_Consolidation_Set_Id NUMBER,
826 X_Status VARCHAR2,
827 X_Request_Id NUMBER,
828 X_Amount_Type_Code VARCHAR2,
829 X_ledger_id NUMBER,
830 X_Period_Year NUMBER,
831 X_Target_Resp_Name VARCHAR2,
832 X_Target_User_Name VARCHAR2,
833 X_Target_DB_Name VARCHAR2) IS
834 BEGIN
835 INSERT INTO GL_CONSOLIDATION_HISTORY(
836 consolidation_run_id,
837 consolidation_id,
838 last_update_date,
839 last_updated_by,
840 last_update_login,
841 creation_date,
842 created_by,
843 from_period_name,
844 to_period_name,
845 to_currency_code,
846 method_flag,
847 run_easylink_flag,
848 run_posting_flag,
849 actual_flag,
850 from_budget_name,
851 to_budget_name,
852 from_budget_version_id,
853 to_budget_version_id,
854 consolidation_set_id,
855 status,
856 request_id,
857 average_consolidation_flag,
858 amount_type,
859 target_resp_name,
860 target_user_name,
861 target_database_name
862 )
863 SELECT X_Consolidation_Run_Id,
864 X_Consolidation_Id,
865 X_Last_Update_Date,
866 X_Last_Updated_By,
867 X_Last_Updated_By,
868 X_Last_Update_Date,
869 X_Last_Updated_By,
870 PS.period_name,
871 PS.period_name,
872 X_To_Currency_Code,
873 X_Method_Flag,
874 X_Run_Easylink_Flag,
875 X_Run_Posting_Flag,
876 X_Actual_Flag,
877 X_From_Budget_Name,
878 X_To_Budget_Name,
879 X_From_Budget_Version_Id,
880 X_To_Budget_Version_Id,
881 X_Consolidation_Set_Id,
882 X_Status,
883 X_Request_Id,
884 'N',
885 X_Amount_Type_Code,
886 X_Target_Resp_Name,
887 X_Target_User_Name,
888 X_Target_DB_Name
889 FROM
890 GL_PERIOD_STATUSES PS,
891 GL_BUDGET_PERIOD_RANGES BPR
892 WHERE BPR.budget_version_id = X_From_Budget_Version_Id
893 AND BPR.period_year = X_Period_Year
894 AND BPR.open_flag = 'O'
895 AND BPR.period_year = ps.period_year
896 AND PS.application_id = 101
897 AND PS.ledger_id = X_ledger_id
898 AND PS.effective_period_num
899 BETWEEN BPR.period_year * 10000 + BPR.start_period_num
900 AND BPR.period_year * 10000 + BPR.end_period_num ;
901
902 END Insert_For_Budgetyear;
903
904
905 PROCEDURE Insert_Status_ReqId(
906 X_StdRunId NUMBER,
907 X_AvgRunId NUMBER,
908 X_StdReqId NUMBER,
909 X_AvgReqId NUMBER) IS
910 BEGIN
911 IF (X_StdRunId IS NOT NULL) THEN
912 UPDATE GL_CONSOLIDATION_HISTORY
913 SET status = 'TS',
914 request_id = X_StdReqId
915 WHERE consolidation_run_id = X_StdRunId;
916 END IF;
917
918 IF (X_AvgRunId IS NOT NULL) THEN
919 UPDATE GL_CONSOLIDATION_HISTORY
920 SET status = 'TS',
921 request_id = X_AvgReqId
922 WHERE consolidation_run_id = X_AvgRunId;
923 END IF;
924
925 END Insert_Status_ReqId;
926
927 END GL_CONS_HISTORY_PKG;