1 PACKAGE BODY GL_CONS_BATCHES_PKG AS
2 /* $Header: glicobtb.pls 120.13 2011/08/18 14:57:17 sommukhe ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 FUNCTION Insert_Consolidation_Batches(
9 X_Batch_Query_Options_Flag VARCHAR2,
10 X_Consolidation_Id NUMBER,
11 X_Consolidation_Run_Id NUMBER,
12 X_Last_Updated_By NUMBER,
13 X_From_Ledger_Id NUMBER,
14 X_To_Ledger_Id NUMBER,
15 X_Default_Period_Name VARCHAR2,
16 X_Currency_Code VARCHAR2) RETURN BOOLEAN IS
17
18 CURSOR batches is
19 SELECT consolidation_run_id
20 FROM GL_CONS_BATCHES glcb
21 WHERE glcb.consolidation_run_id = X_Consolidation_run_Id;
22
23 dummy NUMBER;
24
25 BEGIN
26 IF (X_Batch_Query_Options_Flag = 'U' OR X_Batch_Query_Options_Flag is NULL)
27 THEN
28 INSERT INTO GL_CONS_BATCHES
29 (consolidation_id, consolidation_run_id, je_batch_id,
30 last_update_date, last_updated_by)
31 SELECT DISTINCT X_Consolidation_Id, X_Consolidation_Run_Id,
32 h.je_batch_id, sysdate, X_Last_Updated_By
33 FROM GL_JE_HEADERS h
34 WHERE h.status = 'P'
35 AND h.ACTUAL_FLAG = 'A'
36 AND h.period_name = X_Default_Period_Name
37 AND h.ledger_id = X_From_Ledger_Id
38 AND NOT EXISTS
39 (select 'X'
40 from gl_cons_batches cb, gl_consolidation c,
41 gl_consolidation_history ch
42 where c.consolidation_id = cb.consolidation_id
43 and c.to_ledger_id = X_To_Ledger_Id
44 and cb.je_batch_id = h.je_batch_id
45 and ch.consolidation_run_id = cb.consolidation_run_id
46 and ch.to_currency_code = X_Currency_Code
47 and c.consolidation_id = X_Consolidation_Id);
48
49 ELSIF (X_Batch_Query_Options_Flag = 'C') THEN
50 INSERT INTO GL_CONS_BATCHES
51 (consolidation_id, consolidation_run_id, je_batch_id,
52 last_update_date, last_updated_by)
53 SELECT DISTINCT
54 X_Consolidation_Id, X_Consolidation_Run_Id, h.je_batch_id,
55 sysdate, X_Last_Updated_By
56 FROM GL_JE_HEADERS h
57 WHERE status = 'P'
58 AND ACTUAL_FLAG = 'A'
59 AND period_name = X_Default_Period_Name
60 AND h.ledger_id = X_From_Ledger_Id
61 AND EXISTS
62 (select 'X'
63 from gl_cons_batches cb, gl_consolidation c,
64 gl_consolidation_history ch
65 where c.consolidation_id = cb.consolidation_id
66 and c.to_ledger_id = X_To_Ledger_Id
67 and cb.je_batch_id = h.je_batch_id
68 and ch.consolidation_run_id = cb.consolidation_run_id
69 and ch.to_currency_code = X_Currency_Code
70 and c.consolidation_id = X_Consolidation_Id);
71 ELSE
72 INSERT INTO GL_CONS_BATCHES
73 (consolidation_id, consolidation_run_id, je_batch_id,
74 last_update_date, last_updated_by)
75 SELECT DISTINCT
76 X_Consolidation_Id, X_Consolidation_Run_Id, h.je_batch_id,
77 sysdate, X_Last_Updated_By
78 FROM GL_JE_HEADERS h
79 WHERE status = 'P'
80 AND ACTUAL_FLAG = 'A'
81 AND period_name = X_Default_Period_Name
82 AND h.ledger_id = X_From_Ledger_Id;
83 END IF;
84
85 -- Check to see if any batches inserted so we can return a boolean value.
86 OPEN batches;
87 FETCH batches INTO dummy;
88
89 IF (batches%FOUND) THEN
90 CLOSE batches;
91 RETURN TRUE;
92 ELSE
93 CLOSE batches;
94 RETURN FALSE;
95 END IF;
96
97 END Insert_Consolidation_Batches;
98
99
100
101 -- Insert_Cons_Batch only inserts one batch at a time into
102 -- the Gl_Cons_Batches table. It is called from Pre-Update
103 -- of Batches block.
104
105 PROCEDURE Insert_Cons_Batch(
106 X_Consolidation_Id NUMBER,
107 X_Consolidation_Run_Id NUMBER,
108 X_Je_Batch_Id NUMBER,
109 X_User_Id NUMBER) IS
110
111 BEGIN
112
113 INSERT INTO gl_cons_batches
114 (consolidation_id, consolidation_run_id, je_batch_id,
115 last_update_date, last_updated_by) values
116 (X_Consolidation_Id, X_Consolidation_Run_Id, X_Je_Batch_Id,
117 sysdate, X_User_Id);
118
119 END Insert_Cons_Batch;
120
121
122
123 -- Delete_Cons_Batch only deletes one batch at a time from
124 -- the Gl_Cons_Batches table. It is called from Pre-Update
125 -- of Batches block.
126
127 PROCEDURE Delete_Cons_Batch( X_Je_Batch_Id NUMBER) IS
128
129 BEGIN
130
131 DELETE FROM gl_cons_batches
132 where je_batch_id = X_Je_Batch_Id;
133
134
135 END Delete_Cons_Batch;
136
137
138 -- Remove gl_cons_batches rows for a particular consolidation run
139
140 PROCEDURE Remove_Cons_Run_Batches(
141 x_errbuf OUT NOCOPY VARCHAR2,
142 x_retcode OUT NOCOPY VARCHAR2,
143 p_consolidation_run_id NUMBER) IS
144 BEGIN
145 DELETE FROM gl_cons_batches cb
146 WHERE cb.consolidation_run_id = p_consolidation_run_id;
147 END Remove_Cons_Run_Batches;
148
149 END GL_CONS_BATCHES_PKG;
[Home] [Help]