[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS_GL_PKG_EXT
Source
1 PACKAGE BODY fv_facts_gl_pkg_ext AS
2 /* $Header: FVFCVCUB.pls 120.1.12000000.3 2007/03/08 21:45:40 snama ship $*/
3 g_module_name VARCHAR2(100);
4 g_FAILURE NUMBER;
5 g_SUCCESS NUMBER;
6 g_WARNING NUMBER;
7 g_request_id NUMBER;
8 g_user_id NUMBER;
9 g_login_id NUMBER;
10 g_set_of_books_id NUMBER;
11 g_enter VARCHAR2(10);
12 g_exit VARCHAR2(10);
13 g_conc_program_id NUMBER;
14 g_conc_program_name fnd_concurrent_programs.concurrent_program_name%TYPE;
15
16 --****************************************************************************************--
17 --* Name : initialize_global_variables *--
18 --* Type : Procedure *--
19 --* Purpose : To initialize all global variables *--
20 --* Parameters : None *--
21 --* Global Vars : As in procedure *--
22 --* Called from : Called when initializing the package *--
23 --* Calls : None *--
24 --* Tables Used : None *--
25 --* Logic : No Logic *--
26 --****************************************************************************************--
27 PROCEDURE initialize_global_variables
28 IS
29 BEGIN
30 g_module_name := 'fv.plsql.fv_facts_gl_pkg_ext.';
31 g_FAILURE := -1;
32 g_SUCCESS := 0;
33 g_WARNING := -2;
34 g_request_id := fnd_global.conc_request_id;
35 g_user_id := fnd_global.user_id;
36 g_login_id := fnd_global.login_id;
37 g_conc_program_id := fnd_global.conc_program_id;
38 -- Commented out for la uptake
39 -- g_set_of_books_id := TO_NUMBER(fnd_profile.value('GL_SET_OF_BKS_ID'));
40 g_enter := 'ENTER';
41 g_exit := 'EXIT';
42 END;
43
44 --****************************************************************************************--
45 --* Name : get_fed_system_parameters *--
46 --* Type : Procedure *--
47 --* Purpose : To get the federal system parameters *--
48 --* Parameters : p_vendor_attribute OUT Vendor Attribute column *--
49 --* : p_customer_attribute OUT Customer Attribute Column *--
50 --* : p_errbuf OUT Error Message *--
51 --* : p_retcode OUT Return Code *--
52 --* Global Vars : g_SUCCESS *--
53 --* : g_FAILURE *--
54 --* : g_enter *--
55 --* : g_exit *--
56 --* : fnd_log.level_procedure *--
57 --* : g_module_name *--
58 --* : fnd_log.g_current_runtime_level *--
59 --* : fnd_log.level_statement *--
60 --* : fnd_log.level_unexpected *--
61 --* Called from : process_parameters *--
62 --* Calls : fv_utility.debug_mesg *--
63 --* : fv_utility.log_mesg *--
64 --* Tables Used : fv_system_parameters SELECT *--
65 --* Logic : Select Vendor Attribute Column and Customer Attribute Column *--
66 --* : from fv_system_parameters *--
67 --****************************************************************************************--
68 PROCEDURE get_fed_system_parameters
69 (
70 p_vendor_attribute OUT NOCOPY VARCHAR2,
71 p_customer_attribute OUT NOCOPY VARCHAR2,
72 p_errbuf OUT NOCOPY VARCHAR2,
73 p_retcode OUT NOCOPY NUMBER
74 )
75 IS
76 l_module_name VARCHAR2(200);
77 l_location VARCHAR2(200);
78 BEGIN
79 l_module_name := g_module_name || 'get_fed_system_parameters';
80 p_retcode := g_SUCCESS;
81
82 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
83 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
84 END IF;
85
86 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
87 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '*****INPUT PARAMETERS*******');
88 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'None');
89 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '****************************');
90 END IF;
91
92 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
93 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'SELECT from fv_system_parameters');
94 END IF;
95 SELECT fsp.factsi_customer_attribute,
96 fsp.factsi_vendor_attribute
97 INTO p_customer_attribute,
98 p_vendor_attribute
99 FROM fv_system_parameters fsp;
100
101 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
102 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '*****OUTPUT PARAMETERS******');
103 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_vendor_attribute = '||p_vendor_attribute);
104 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_customer_attribute = '||p_customer_attribute);
105 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_errbuf = '||p_errbuf);
106 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_retcode = '||p_retcode);
107 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '****************************');
108 END IF;
109
110 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
111 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
112 END IF;
113 EXCEPTION
114 WHEN OTHERS THEN
115 p_retcode := g_FAILURE;
116 p_errbuf := SQLERRM;
117 l_location := l_module_name||'.final_exception';
118 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
119 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
120 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
121 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
122 END IF;
123 END;
124
125 --****************************************************************************************--
126 --* Name : process_parameters *--
127 --* Type : Procedure *--
128 --* Purpose : To process all the input parameters to the program *--
129 --* Parameters : p_vendor_or_cust IN Vendor (V) or Customer (C). *--
130 --* : p_vendor_or_cust_id IN Vendor or Customer Id *--
131 --* : p_from_period IN From Period *--
132 --* : p_to_period IN To Period *--
133 --* : p_vendor_type OUT Vendor Type *--
134 --* : p_elimination_id OUT Edlimination Id *--
135 --* : p_period_year OUT Period Year *--
136 --* : p_from_period_num OUT From Period Number *--
137 --* : p_to_period_num OUT To Period Number *--
138 --* : p_errbuf OUT Error Message *--
139 --* : p_retcode OUT Return Code *--
140 --* Global Vars : g_SUCCESS *--
141 --* : g_FAILURE *--
142 --* : g_enter *--
143 --* : g_exit *--
144 --* : fnd_log.level_procedure *--
145 --* : g_module_name *--
146 --* : fnd_log.g_current_runtime_level *--
147 --* : fnd_log.level_statement *--
148 --* : fnd_log.level_unexpected *--
149 --* : g_conc_program_id *--
150 --* : g_conc_program_id WRITE *--
151 --* Called from : main *--
152 --* Calls : fv_utility.debug_mesg *--
153 --* : fv_utility.log_mesg *--
154 --* : get_fed_system_parameters *--
155 --* Tables Used : gl_period_statuses SELECT *--
156 --* : po_vendors SELECT *--
157 --* : ra_customers SELECT *--
158 --* : fnd_concurrent_programs SELECT *--
159 --* Logic : *--
160 --****************************************************************************************--
161 PROCEDURE process_parameters
162 (
163 p_ledger_id IN NUMBER,
164 p_vendor_or_cust IN VARCHAR2,
165 p_vendor_or_cust_id IN NUMBER,
166 p_from_period IN VARCHAR2,
167 p_to_period IN VARCHAR2,
168 p_vendor_type OUT NOCOPY VARCHAR2,
169 p_elimination_id OUT NOCOPY VARCHAR2,
170 p_period_year OUT NOCOPY NUMBER,
171 p_from_period_num OUT NOCOPY NUMBER,
172 p_to_period_num OUT NOCOPY NUMBER,
173 p_errbuf OUT NOCOPY VARCHAR2,
174 p_retcode OUT NOCOPY NUMBER
175 )
176 IS
177 l_module_name VARCHAR2(200);
178 l_location VARCHAR2(200);
179 l_customer_attribute fv_system_parameters.factsi_customer_attribute%TYPE;
180 l_vendor_attribute fv_system_parameters.factsi_vendor_attribute%TYPE;
181 l_period_year gl_period_statuses.period_year%TYPE;
182 l_select_stmt VARCHAR2(1024);
183 l_cursor_id INTEGER;
184 l_ignore INTEGER;
185 BEGIN
186 l_module_name := g_module_name || 'process_parameters';
187 p_retcode := g_SUCCESS;
188
189 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
190 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
191 END IF;
192
193 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
194 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '*****INPUT PARAMETERS*******');
195 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_ledger_id = '||p_ledger_id);
196 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_vendor_or_cust = '||p_vendor_or_cust);
197 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_vendor_or_cust_id = '||p_vendor_or_cust_id);
198 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_from_period = '||p_from_period);
199 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_to_period = '||p_to_period);
200 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '****************************');
201 END IF;
202
203 IF (NVL(p_vendor_or_cust, 'X') NOT IN ('V', 'C')) THEN
204 p_retcode := g_FAILURE;
205 p_errbuf := 'Unknown Vendor or Customer Type Idenfier. Should be either V or C.';
206 l_location := l_module_name||'p_vendor_or_cust_required';
207 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
208 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
209 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
210 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
211 END IF;
212 END IF;
213
214 IF (p_vendor_or_cust_id IS NULL) THEN
215 p_retcode := g_FAILURE;
216 p_errbuf := 'Vendor or Customer Information is required';
217 l_location := l_module_name||'p_vendor_or_cust_id_required';
218 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
219 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
220 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
221 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
222 END IF;
223 END IF;
224
225 IF (p_from_period IS NULL) THEN
226 p_retcode := g_FAILURE;
227 p_errbuf := 'From Period is required';
228 l_location := l_module_name||'from_period_required';
229 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
230 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
231 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
232 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
233 END IF;
234 END IF;
235
236 IF (p_to_period IS NULL) THEN
237 p_retcode := g_FAILURE;
238 p_errbuf := 'To Period is required';
239 l_location := l_module_name||'to_period_required';
240 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
241 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
242 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
243 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
244 END IF;
245 END IF;
246
247 IF (p_retcode = g_SUCCESS) THEN
248 BEGIN
249 SELECT gps.period_year,
250 gps.period_num
251 INTO p_period_year,
252 p_from_period_num
253 FROM gl_period_statuses gps
254 WHERE gps.application_id = 101
255 AND gps.ledger_id = p_ledger_id --g_set_of_books_id
256 AND gps.period_name = p_from_period;
257 EXCEPTION
258 WHEN NO_DATA_FOUND THEN
259 p_retcode := g_FAILURE;
260 p_errbuf := 'No From Period found '||p_from_period;
261 l_location := l_module_name||'select_gl_period_statuses1';
262 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
263 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
264 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
265 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
266 END IF;
267 WHEN OTHERS THEN
268 p_retcode := g_FAILURE;
269 p_errbuf := SQLERRM;
270 l_location := l_module_name||'select_gl_period_statuses1';
271 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
272 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
273 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
274 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
275 END IF;
276 END;
277 END IF;
278
279 IF (p_retcode = g_SUCCESS) THEN
280 BEGIN
281 SELECT gps.period_year,
282 gps.period_num
283 INTO l_period_year,
284 p_to_period_num
285 FROM gl_period_statuses gps
286 WHERE gps.application_id = 101
287 AND gps.ledger_id = p_ledger_id --g_set_of_books_id
288 AND gps.period_name = p_to_period;
289 EXCEPTION
290 WHEN NO_DATA_FOUND THEN
291 p_retcode := g_FAILURE;
292 p_errbuf := 'No To Period found '||p_from_period;
293 l_location := l_module_name||'select_gl_period_statuses1';
294 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
295 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
296 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
297 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
298 END IF;
299 WHEN OTHERS THEN
300 p_retcode := g_FAILURE;
301 p_errbuf := SQLERRM;
302 l_location := l_module_name||'select_gl_period_statuses1';
303 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
304 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
305 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
306 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
307 END IF;
308 END;
309 END IF;
310
311 IF (p_period_year <> l_period_year) THEN
312 p_retcode := g_FAILURE;
313 p_errbuf := 'From Period Name and To Period Name are from 2 different fiscal years.';
314 l_location := l_module_name||'period_year_mismatch';
315 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
316 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
317 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
318 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
319 END IF;
320 END IF;
321
322 IF (p_from_period_num > p_to_period_num) THEN
323 p_retcode := g_FAILURE;
324 p_errbuf := 'From Period Name is greater than To Period Name.';
325 l_location := l_module_name||'period_name_mismatch';
326 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
327 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
328 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
329 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
330 END IF;
331 END IF;
332
333 IF (p_retcode = g_SUCCESS) THEN
334 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
335 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Calling get_fed_system_parameters');
336 END IF;
337 get_fed_system_parameters
338 (
339 p_vendor_attribute => l_vendor_attribute,
340 p_customer_attribute => l_customer_attribute,
341 p_errbuf => p_errbuf,
342 p_retcode => p_retcode
343 );
344 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
345 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Returned from get_fed_system_parameters');
346 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_vendor_attribute = '||l_vendor_attribute);
347 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_customer_attribute = '||l_customer_attribute);
348 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_errbuf = '||p_errbuf);
349 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_retcode = '||p_retcode);
350 END IF;
351 END IF;
352
353 IF (p_retcode = g_SUCCESS) THEN
354 IF (p_vendor_or_cust = 'V') THEN
355 l_select_stmt := 'SELECT vendor_type_lookup_code vendor_type, '||
356 l_vendor_attribute||' elimnation_id '||
357 'FROM po_vendors
358 WHERE vendor_id = :p_vendor_or_cust_id';
359 ELSE
360 l_select_stmt := 'SELECT customer_class_code vendor_type, '||
361 l_customer_attribute||' elimnation_id '||
362 'FROM ra_customers
363 WHERE customer_id = :p_vendor_or_cust_id';
364 END IF;
365
366 BEGIN
367 l_cursor_id := dbms_sql.open_cursor;
368 dbms_sql.parse(l_cursor_id, l_select_stmt, dbms_sql.v7);
369 dbms_sql.define_column(l_cursor_id, 1, p_vendor_type, 30);
370 dbms_sql.define_column(l_cursor_id, 2, p_elimination_id, 150);
371 dbms_sql.bind_variable(l_cursor_id,':p_vendor_or_cust_id',p_vendor_or_cust_id);
372 l_ignore := dbms_sql.execute(l_cursor_id);
373 p_vendor_type := NULL;
374 p_elimination_id := NULL;
375 LOOP
376 l_ignore := dbms_sql.fetch_rows(l_cursor_id);
377 EXIT WHEN l_ignore= 0;
378 dbms_sql.column_value(l_cursor_id, 1, p_vendor_type);
379 dbms_sql.column_value(l_cursor_id, 2, p_elimination_id);
380 EXIT;
381 END LOOP;
382 dbms_sql.close_cursor(l_cursor_id);
383 EXCEPTION
384 WHEN OTHERS THEN
385 p_retcode := g_FAILURE;
386 p_errbuf := SQLERRM;
387 l_location := l_module_name||'select_gl_period_statuses1';
388 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
389 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
390 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
391 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
392 END IF;
393 END;
394 END IF;
395
396 IF (p_retcode = g_SUCCESS) THEN
397 BEGIN
398 SELECT concurrent_program_name
399 INTO g_conc_program_name
400 FROM fnd_concurrent_programs fcp
401 WHERE fcp.concurrent_program_id = g_conc_program_id;
402 EXCEPTION
403 WHEN NO_DATA_FOUND THEN
404 g_conc_program_name := 'UNKNOWN ('||g_conc_program_id||')';
405 WHEN OTHERS THEN
406 p_retcode := g_FAILURE;
407 p_errbuf := SQLERRM;
408 l_location := l_module_name||'select_fnd_concurrent_programs';
409 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
410 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
411 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
412 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
413 END IF;
414 END;
415 END IF;
416
417 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
418 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '*****OUTPUT PARAMETERS******');
419 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_vendor_type = '||p_vendor_type);
420 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_elimination_id = '||p_elimination_id);
421 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_period_year = '||p_period_year);
422 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_from_period_num = '||p_from_period_num);
423 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_to_period_num = '||p_to_period_num);
424 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_errbuf = '||p_errbuf);
425 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_retcode = '||p_retcode);
426 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '****************************');
427 END IF;
428
429 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
430 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
431 END IF;
432 EXCEPTION
433 WHEN OTHERS THEN
434 p_retcode := g_FAILURE;
435 p_errbuf := SQLERRM;
436 l_location := l_module_name||'.final_exception';
437 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
438 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
439 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
440 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
441 END IF;
442 END;
443
444 --****************************************************************************************--
445 --* Name : main *--
446 --* Type : Procedure *--
447 --* Purpose : Called from the concurrent programs FACTS I Update Customer DFF and *--
448 --* : FACTS I Update Vendor DFF, *--
449 --* Parameters : p_errbuf OUT Error Message *--
450 --* : p_retcode OUT Return Code *--
451 --* : p_ledger_id IN Ledger_id (added for LA uptake) *--
452 --* : p_vendor_or_cust_id IN Vendor or Customer Id *--
453 --* : p_from_period IN From Period *--
454 --* : p_to_period IN To Period *--
455 --* : p_vendor_or_cust IN Vendor (V) or Customer (C). *--
456 --* Global Vars : g_SUCCESS *--
457 --* : g_FAILURE *--
458 --* : g_enter *--
459 --* : g_exit *--
460 --* : fnd_log.level_procedure *--
461 --* : g_module_name *--
462 --* : fnd_log.g_current_runtime_level *--
463 --* : fnd_log.level_statement *--
464 --* : fnd_log.level_unexpected *--
465 --* : g_conc_program_name *--
466 --* : g_user_id *--
467 --* : g_login_id *--
468 --* : g_set_of_books_id *--
469 --* Called from : FACTS I Update Vendor DFF (Concurrent Program) (FVFCTUVE) *--
470 --* : FACTS I Update Customer DFF (Concurrent Program) (FVFCTUCU) *--
471 --* Calls : process_parameters *--
472 --* : fv_utility.debug_mesg *--
473 --* : fv_utility.log_mesg *--
474 --* Tables Used : fv_facts_line_balances *--
475 --* Logic : *--
476 --****************************************************************************************--
477 PROCEDURE main
478 (
479 p_errbuf OUT NOCOPY VARCHAR2,
480 p_retcode OUT NOCOPY NUMBER,
481 p_ledger_id IN NUMBER,
482 p_vendor_or_cust_id IN NUMBER,
483 p_from_period IN VARCHAR2,
484 p_to_period IN VARCHAR2,
485 p_vendor_or_cust IN VARCHAR2
486 )
487 IS
488 l_module_name VARCHAR2(200);
489 l_location VARCHAR2(200);
490
491 l_vendor_type po_vendors.vendor_type_lookup_code%TYPE;
492 l_elimination_id po_vendors.attribute1%TYPE;
493 l_from_period_num gl_period_statuses.period_num%TYPE;
494 l_to_period_num gl_period_statuses.period_num%TYPE;
495 l_period_year gl_period_statuses.period_year%TYPE;
496 l_rows NUMBER := 0;
497 l_tot_rows NUMBER := 0;
498
499
500 BEGIN
501 l_module_name := g_module_name || 'main';
502 p_retcode := g_SUCCESS;
503
504 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
505 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_enter);
506 END IF;
507
508 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
509 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '*****INPUT PARAMETERS*******');
510 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_ledger_id = '||p_ledger_id);
511 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_vendor_or_cust = '||p_vendor_or_cust);
512 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_vendor_or_cust_id = '||p_vendor_or_cust_id);
513 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_from_period = '||p_from_period);
514 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_to_period = '||p_to_period);
515 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '****************************');
516 END IF;
517
518 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
519 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Calling process_parameters');
520 END IF;
521
522 IF (p_retcode = g_SUCCESS) THEN
523 process_parameters
524 (
525 p_ledger_id => p_ledger_id,
526 p_vendor_or_cust => p_vendor_or_cust,
527 p_vendor_or_cust_id => p_vendor_or_cust_id,
528 p_from_period => p_from_period,
529 p_to_period => p_to_period,
530 p_vendor_type => l_vendor_type,
531 p_elimination_id => l_elimination_id,
532 p_period_year => l_period_year,
533 p_from_period_num => l_from_period_num,
534 p_to_period_num => l_to_period_num,
535 p_errbuf => p_errbuf,
536 p_retcode => p_retcode
537 );
538 END IF;
539
540 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
541 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Returned from process_parameters');
542 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_vendor_type = '||l_vendor_type);
543 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_elimination_id = '||l_elimination_id);
544 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_period_year = '||l_period_year);
545 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_from_period_num = '||l_from_period_num);
546 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_to_period_num = '||l_to_period_num);
547 END IF;
548
549 IF (p_retcode = g_SUCCESS) THEN
550 BEGIN
551 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
552 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'Updating fv_facts_period_balances');
553 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_elimination_id = '||l_elimination_id);
554 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_vendor_type = '||l_vendor_type);
555 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_period_year = '||l_period_year);
556 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_from_period_num = '||l_from_period_num);
557 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'l_to_period_num = '||l_to_period_num);
558 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'g_set_of_books_id = '||g_set_of_books_id);
559 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_vendor_or_cust = '||p_vendor_or_cust);
560 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_ledger_id = '||p_ledger_id);
561 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_vendor_or_cust_id = '||p_vendor_or_cust_id);
562 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'g_conc_program_name = '||g_conc_program_name);
563 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'g_user_id = '||g_user_id);
564 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'g_login_id = '||g_login_id);
565 END IF;
566
567 FOR sob_rec IN (SELECT set_of_books_id
568 FROM fv_facts1_run
569 WHERE fiscal_year = l_period_year)
570 LOOP
571
572 UPDATE fv_facts1_line_balances ffpb
573 SET ffpb.party_classification = l_vendor_type,
574 ffpb.eliminations_dept =
575 DECODE (l_vendor_type, 'FEDERAL', SUBSTR(NVL(l_elimination_id, '00'), 1, 6), ' '),
576 g_ng_indicator = DECODE(l_vendor_type, 'FEDERAL', 'F', 'N')
577 WHERE ffpb.party_id = p_vendor_or_cust_id
578 AND ffpb.party_type = p_vendor_or_cust
579 AND ffpb.set_of_books_id = sob_rec.set_of_books_id
580 AND ffpb.period_year = l_period_year
581 AND ffpb.period_num BETWEEN l_from_period_num AND l_to_period_num;
582
583 l_rows := SQL%ROWCOUNT;
584 l_tot_rows := l_tot_rows + SQL%ROWCOUNT;
585
586
587 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
588 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,
589 'Updated fv_facts1_line_balances');
590 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,l_rows||' rows updated.');
591 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'for set of books id: '|| sob_rec.set_of_books_id);
592 END IF;
593
594
595 END LOOP;
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 p_retcode := g_FAILURE;
600 p_errbuf := SQLERRM;
601 l_location := l_module_name||'update_fv_facts1_line_balances';
602 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
603 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
604 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
605 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
606 END IF;
607 END;
608 END IF;
609
610 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
611 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '*****OUTPUT PARAMETERS******');
612 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_errbuf = '||p_errbuf);
613 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_retcode = '||p_retcode);
614 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '****************************');
615 END IF;
616
617 fv_utility.log_mesg(fnd_log.level_statement, l_module_name, 'UPDATED '||l_tot_rows||' ROWS IN FV_FACTS1_LINE_BALANCES.');
618
619 l_tot_rows := 0;
620
621
622 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
623 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
624 END IF;
625 EXCEPTION
626 WHEN OTHERS THEN
627 p_retcode := g_FAILURE;
628 p_errbuf := SQLERRM;
629 l_location := l_module_name||'.final_exception';
630 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,l_location) ;
631 fv_utility.log_mesg(fnd_log.level_unexpected, l_location,p_errbuf) ;
632 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
633 fv_utility.debug_mesg(fnd_log.level_procedure, l_module_name,g_exit||'('||p_retcode||')');
634 END IF;
635 ROLLBACK;
636 END;
637
638 BEGIN
639 initialize_global_variables;
640 END fv_facts_gl_pkg_ext;