[Home] [Help]
PACKAGE BODY: APPS.CSTPGLXF
Source
1 PACKAGE BODY CSTPGLXF AS
2 /* $Header: CSTGLXFB.pls 115.6 2004/01/08 22:39:48 rzhu ship $ */
3
4 /*============================================================================+
5 | This procedure is the called by the GL Transfer SRS. It first validates |
6 | the input parameters, then calls the common GL transfer API. |
7 |============================================================================*/
8
9 PROCEDURE CST_GL_TRANSFER (
10 p_errbuf OUT NOCOPY VARCHAR2,
11 p_retcode OUT NOCOPY NUMBER,
12 p_application_id NUMBER,
13 p_user_id NUMBER,
14 p_legal_entity NUMBER,
15 p_cost_type_id NUMBER,
16 p_cost_group_id NUMBER,
17 p_period_id NUMBER,
18 p_batch_name VARCHAR2,
19 p_gl_transfer_mode VARCHAR2,
20 p_submit_journal_import VARCHAR2,
21 p_debug_flag VARCHAR2
22 ) IS
23 l_sob_list XLA_GL_TRANSFER_PKG.T_SOB_LIST := XLA_GL_TRANSFER_PKG.T_SOB_LIST();
24 l_ae_category XLA_GL_TRANSFER_PKG.T_AE_CATEGORY;
25 l_start_date DATE;
26 l_end_date DATE;
27 l_stmt_num NUMBER;
28 l_le_exists NUMBER;
29 l_ct_exists NUMBER;
30 l_cg_exists NUMBER;
31 l_per_exists NUMBER;
32 l_set_of_books_id NUMBER;
33 l_sob_name VARCHAR2(30);
34 l_base_currency_code VARCHAR2(10);
35 l_request_id NUMBER;
36 l_err_num NUMBER;
37 l_err_code VARCHAR2(240);
38 l_err_msg VARCHAR2(240);
39 CONC_STATUS BOOLEAN;
40 CST_NO_LE EXCEPTION;
41 CST_NO_CT EXCEPTION;
42 CST_NO_CG EXCEPTION;
43 CST_NO_PER EXCEPTION;
44 BEGIN
45 xla_util.enable_debug;
46
47 -------------------------------------------------------------------
48 -- Get request ID
49 -------------------------------------------------------------------
50
51 l_request_id := FND_GLOBAL.conc_request_id; -- Populate concurrent Request Id.
52
53 l_request_id := Nvl(l_request_id,-1); --for now
54
55
56 --------------------------------------------------------------------
57 -- Display the Input parameters
58 --------------------------------------------------------------------
59
60 fnd_file.put_line(fnd_file.log,'Application ID = '||to_char(p_application_id ));
61 fnd_file.put_line(fnd_file.log,'User ID = '||to_char(p_user_id));
62 fnd_file.put_line(fnd_file.log,'Legal Entity = '||to_char(p_legal_entity));
63 fnd_file.put_line(fnd_file.log,'Cost Type = '||to_char(p_cost_type_id));
64 fnd_file.put_line(fnd_file.log,'Cost Group = '||to_char(p_cost_group_id));
65 fnd_file.put_line(fnd_file.log,'Period = '||to_char(p_period_id));
66 fnd_file.put_line(fnd_file.log,'Batch Name = '||p_batch_name );
67 fnd_file.put_line(fnd_file.log,'GL Transfer Mode = '||p_gl_transfer_mode);
68 fnd_file.put_line(fnd_file.log,'Submit Journal Import = '||p_submit_journal_import);
69 fnd_file.put_line(fnd_file.log,'Debug Flag = '||p_debug_flag);
70
71 l_stmt_num := 10;
72
73 -------------------------------------------------------------------
74 -- Validate the Legal Entity
75 -- the legal entity should
76 -- exist in cst_le_cost_types
77 -- post_to_gl flag for it should be 'Y'
78 -------------------------------------------------------------------
79
80 SELECT
81 count(*)
82 INTO
83 l_le_exists
84 FROM
85 cst_le_cost_types
86 WHERE
87 legal_entity = p_legal_entity AND
88 post_to_gl ='Y';
89
90 IF (l_le_exists = 0) THEN
91 RAISE CST_NO_LE;
92 END IF;
93
94 l_stmt_num := 20;
95
96 ------------------------------------------------------------------
97 -- Validate the Cost Type
98 -- cost type should
99 -- exist in cst_le_cost_types
100 -- post_to_gl flag for le-ct should be 'Y'
101 -- and the cost type should not be disabled
102 ------------------------------------------------------------------
103
104 SELECT
105 count(*)
106 INTO
107 l_ct_exists
108 FROM
109 cst_le_cost_types clct,
110 cst_cost_types cct
111 WHERE clct.legal_entity = p_legal_entity AND
112 clct.cost_type_id = p_cost_type_id AND
113 clct.post_to_gl = 'Y' AND
114 clct.cost_type_id = cct.cost_type_id AND
115 NVL(cct.disable_date, SYSDATE +1) > SYSDATE;
116
117 IF (l_ct_exists = 0) THEN
118 RAISE CST_NO_CT;
119 END IF;
120
121 l_stmt_num := 30;
122
123 ------------------------------------------------------------------
124 -- Validate Cost Group
125 -- cost group should
126 -- exist in cst_cost_group for the le
127 ------------------------------------------------------------------
128
129 SELECT
130 count(*)
131 INTO
132 l_cg_exists
133 FROM
134 cst_cost_groups ccg
135 WHERE legal_entity = p_legal_entity AND
136 cost_group_id = p_cost_group_id;
137
138 IF (l_cg_exists = 0) THEN
139 RAISE CST_NO_CG;
140 END IF;
141
142 l_stmt_num := 40;
143
144
145 ------------------------------------------------------------------
146 -- Validate Period
147 -- period should
148 -- exist in cst_pac_periods for the le-ct
149 -- should be closed
150 ------------------------------------------------------------------
151
152 SELECT
153 count(*)
154 INTO
155 l_per_exists
156 FROM
157 cst_pac_periods
158 WHERE open_flag = 'N' AND
159 legal_entity = p_legal_entity AND
160 cost_type_id = p_cost_type_id AND
161 pac_period_id = p_period_id;
162
163 IF (l_per_exists = 0) THEN
164 RAISE CST_NO_PER;
165 END IF;
166
167 l_stmt_num := 50;
168
169
170 ----------------------------------------------------------------
171 -- Get the set of books info
172 ----------------------------------------------------------------
173
174 SELECT
175 clct.set_of_books_id,
176 glsob.name
177 INTO
178 l_set_of_books_id,
179 l_sob_name
180 FROM
181 cst_le_cost_types clct,
182 gl_sets_of_books glsob
183 WHERE
184 clct.legal_entity = p_legal_entity AND
185 clct.cost_type_id = p_cost_type_id AND
186 clct.set_of_books_id = glsob.set_of_books_id;
187
188
189 l_stmt_num := 60;
190
191 ----------------------------------------------------------------
192 -- Get the currency code
193 ----------------------------------------------------------------
194
195 SELECT
196 currency_code
197 INTO
198 l_base_currency_code
199 FROM
200 gl_sets_of_books
201 WHERE
202 set_of_books_id = l_set_of_books_id;
203
204 l_stmt_num := 70;
205
206
207 ------------------------------------------------------------------
208 -- Get period start and end date
209 -- the GL common API takes period start and end dates as input
210 -- instead of the period id
211 ------------------------------------------------------------------
212
213 SELECT
214 period_start_date,
215 period_end_date
216 INTO
217 l_start_date,
218 l_end_date
219 FROM
220 cst_pac_periods
221 WHERE
222 pac_period_id = p_period_id;
223
224
225 ---------------------------------------------------------------
226 -- Populate the structure to be passed to the common API
227 ---------------------------------------------------------------
228
229 l_sob_list.EXTEND;
230 l_sob_list(1).sob_id := l_set_of_books_id;
231 l_sob_list(1).sob_name := l_sob_name;
232 l_sob_list(1).sob_curr_code := l_base_currency_code;
233
234 /* Bug 3233033: change the encum_flag from NULL to 'Y' */
235 l_sob_list(1).encum_flag := 'Y';
236
237 l_sob_list(1).legal_entity_id := p_legal_entity;
238 l_sob_list(1).cost_type_id := p_cost_type_id;
239 l_sob_list(1).cost_group_id := p_cost_group_id;
240
241
242 --------------------------------------------------------------
243 -- Populate the category structure
244 -- for Periodic Costing, there is only one row, and the value
245 -- is 'A' for 'ALL'
246 --------------------------------------------------------------
247
248 l_ae_category(1) := 'A';
249
250 l_stmt_num := 80;
251
252
253 ----------------------------------------------------------------
254 -- Call the common transfer API
255 ----------------------------------------------------------------
256
257 fnd_file.put_line(fnd_file.log,'Calling Common Transfer API ...');
258
259 xla_gl_transfer_pkg.xla_gl_transfer(
260 p_application_id => p_application_id,
261 p_user_id => p_user_id,
262 p_org_id => NULL,
263 p_request_id => l_request_id,
264 p_program_name => 'CST1',
265 p_selection_type => 1,
266 p_sob_list => l_sob_list,
267 p_batch_name => p_batch_name,
268 p_source_doc_id => NULL,
269 p_source_document_table => NULL,
270 p_start_date => l_start_date,
271 p_end_date => l_end_date,
272 p_journal_category => l_ae_category,
273 p_gl_transfer_mode => p_gl_transfer_mode,
274 p_submit_journal_import => p_submit_journal_import,
275 p_summary_journal_entry => 'N',
276 p_process_days => NULL,
277 p_batch_desc => p_legal_entity || ' ' || p_cost_type_id || ' ' || p_cost_group_id || ' ' || p_batch_name,
278 p_je_desc => p_legal_entity || ' ' || p_cost_type_id || ' ' || p_cost_group_id || ' ' || p_batch_name,
279 p_je_line_desc => p_legal_entity || ' ' || p_cost_type_id || ' ' || p_cost_group_id || ' ' || p_batch_name,
280 p_debug_flag => p_debug_flag
281 );
282
283 EXCEPTION
284 WHEN CST_NO_LE THEN
285 l_err_num := 30001;
286 l_err_code := SQLCODE;
287 FND_MESSAGE.set_name('BOM', 'CST_PAC_GL_INVALID_LE');
288 l_err_msg := FND_MESSAGE.Get;
289 l_err_msg := 'CSTGLXFB.cst_gl_transfer : (' || to_char(l_err_num) || '):'|| l_err_code ||' : '||l_err_msg;
290 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
291 fnd_file.put_line(fnd_file.log,l_err_msg);
292 WHEN CST_NO_CT THEN
293 l_err_num := 30002;
294 l_err_code := SQLCODE;
295 FND_MESSAGE.set_name('BOM', 'CST_PAC_GL_INVALID_CT');
296 l_err_msg := FND_MESSAGE.Get;
297 l_err_msg := 'CSTGLXFB.cst_gl_transfer : (' || to_char(l_err_num) || '):'|| l_err_code ||' : '||l_err_msg;
298 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
299 fnd_file.put_line(fnd_file.log,l_err_msg);
300 WHEN CST_NO_CG THEN
301 l_err_num := 30003;
302 l_err_code := SQLCODE;
303 FND_MESSAGE.set_name('BOM', 'CST_PAC_CG_INVALID');
304 l_err_msg := FND_MESSAGE.Get;
305 l_err_msg := 'CSTGLXFB.cst_gl_transfer : (' || to_char(l_err_num) || '):'|| l_err_code ||' : '||l_err_msg;
306 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
307 fnd_file.put_line(fnd_file.log,l_err_msg);
308 WHEN CST_NO_PER THEN
309 l_err_num := 30004;
310 l_err_code := SQLCODE;
311 FND_MESSAGE.set_name('BOM', 'CST_PAC_GL_INVALID_PER');
312 l_err_msg := FND_MESSAGE.Get;
313 l_err_msg := 'CSTGLXFB.cst_gl_transfer : (' || to_char(l_err_num) || '):'|| l_err_code ||' : '||l_err_msg;
314 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
315 fnd_file.put_line(fnd_file.log,l_err_msg);
316 WHEN OTHERS THEN
317 l_err_num := 30009;
318 l_err_code := SQLCODE;
319 l_err_msg :=to_char(l_err_num)||' : '||l_err_code||':'|| SUBSTR('CSTPGLXF.cst_gl_transfer('
320 ||to_char(l_stmt_num)
321 ||'):'
322 ||SQLERRM,1,240);
323 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
324 fnd_file.put_line(fnd_file.log,l_err_msg);
325 END CST_GL_TRANSFER;
326 END CSTPGLXF;