[Home] [Help]
PACKAGE BODY: APPS.CN_COLLECTION_GEN
Source
1 PACKAGE BODY CN_COLLECTION_GEN AS
2 -- $Header: cncogenb.pls 120.13 2007/09/26 19:50:48 apink ship $
3
4 ----+
5 -- Private package variables
6 -- a constant which has a null value for passing to the insert_row
7 -- procedures of table handler APIs as the primary key value
8 ----+
9
10
11 null_id CONSTANT NUMBER := NULL;
12
13 l_org_id NUMBER;
14
15
16 FUNCTION isParallelEnabled
17 return boolean IS
18 g_cn_db_parallel_enabled VARCHAR2(1) := fnd_profile.value('CN_DB_PARALLEL_ENABLE');
19 BEGIN
20 IF (g_cn_db_parallel_enabled IS NOT NULL AND g_cn_db_parallel_enabled = 'Y') THEN
21 return true;
22 else
23 return false;
24 end if;
25 END;
26
27 ----+
28 -- Private package procedures
29 ----+
30
31 -- Procedure Name
32 -- Install_package_Object
33 -- Purpose
34 -- Gets code for a package Spec or Body from CN_SOURCE and uses AD_DDL to create that object
35 -- IF p_test = 'Y' then '_T' is appended to the package name - this allows you to test
36 -- creating the package without destroying the original in the database
37
38 PROCEDURE Install_Package_Object(
39 p_object_id IN NUMBER,
40 p_object_name IN VARCHAR2,
41 p_test IN VARCHAR2 := 'N',
42 x_comp_error OUT NOCOPY VARCHAR2,
43 x_org_id IN NUMBER)
44 IS
45
46 -----+
47 -- Removed Hardcoded 'APPS'
48 -- l_applsys_schema VARCHAR2(20) := 'APPS';
49 -- Hithanki/24-Dec-2003/Bug Fix : 3322008
50 -----+
51
52 l_applsys_schema VARCHAR2(20);
53 k NUMBER;
54 l_object_name VARCHAR2(80) := p_object_name;
55 l_line_length NUMBER;
56 l_send_position NUMBER;
57
58 CURSOR pkg_start (p_object_id NUMBER)
59 IS
60 SELECT cs.line_no, cs.text
61 FROM cn_source cs
62 WHERE cs.object_id = p_object_id
63 AND substr(cs.text, 1, 25) = 'CREATE OR REPLACE PACKAGE'
64 AND org_id = x_org_id
65 ORDER BY line_no;
66
67 l_pkg_start_rec pkg_start%ROWTYPE;
68
69 CURSOR pkg_end (p_object_id NUMBER)
70 IS
71 SELECT cs.line_no, cs.text
72 FROM cn_source cs
73 WHERE cs.object_id = p_object_id
74 AND cs.text LIKE 'END%'
75 AND org_id = x_org_id
76 ORDER BY line_no DESC;
77
78 l_pkg_end_rec pkg_end%ROWTYPE;
79
80 CURSOR fetch_code (p_pks_start NUMBER,
81 p_pks_end NUMBER,
82 p_pks_object_id NUMBER) IS
83 SELECT cs.text
84 FROM cn_source cs
85 WHERE cs.object_id = p_pks_object_id
86 AND cs.line_no BETWEEN p_pks_start AND (p_pks_end - 1)
87 AND org_id = x_org_id
88 ORDER BY line_no;
89
90 BEGIN
91 IF p_test = 'Y' THEN
92 l_object_name := l_object_name || '_T';
93 END IF;
94
95 --dbms_output.put_line('In install_package_object l_object_name '||l_object_name);
96
97 -- Find the locations of the first and last
98 -- lines of the package Spec or Body in the cn_source table, fetch the code
99 -- between these lines and then create the spec / body
100
101 OPEN pkg_start(p_object_id);
102 FETCH pkg_start INTO l_pkg_start_rec;
103 CLOSE pkg_start;
104
105 OPEN pkg_end(p_object_id);
106 FETCH pkg_end INTO l_pkg_end_rec;
107 CLOSE pkg_end;
108
109 k := 1;
110
111 FOR j IN fetch_code(l_pkg_start_rec.line_no, l_pkg_end_rec.line_no, p_object_id)
112 LOOP
113 l_line_length := LENGTHB(j.text);
114 l_send_position := 1;
115
116 -- If it's a test, create the package with the _T name instead
117
118 IF k = 1 AND p_test = 'Y' THEN
119 j.text := REPLACE(UPPER(j.text), UPPER(p_object_name), l_object_name);
120 --dbms_output.put_line(' k = 1 AND p_test = Y so j.text = '||j.text);
121 END IF;
122
123
124 LOOP
125 --if k=1 then dbms_output.put_line('k = '||k||', Text = '||j.text); end if;
126 ad_ddl.build_package(SUBSTRB(j.text,l_send_position,255), k);
127 k := k + 1;
128 l_send_position := l_send_position + 255;
129 IF l_line_length - l_send_position < 0 THEN
130 EXIT;
131 END IF;
132 END LOOP;
133 END LOOP;
134 ad_ddl.build_package('END;', k);
135
136 -----+
137 -- Added Select..Into.. From. Statement
138 -- Hithanki/24-Dec-2003/Bug Fix : 3322008
139 -----+
140
141 --Added to reomve hardcoded Schema
142 SELECT user
143 INTO l_applsys_schema
144 FROM dual;
145
146 ad_ddl.create_plsql_object(
147 applsys_schema => l_applsys_schema,
148 application_short_name => 'CN',
149 object_name => SUBSTR(l_object_name,1,30),
150 lb => 1,
151 ub => k,
152 insert_newlines => 'FALSE',
153 comp_error => x_comp_error);
154
155 --dbms_output.put_line('In install_package_object object_name '||l_object_name);
156 --dbms_output.put_line('In install_package_object comp_error '||x_comp_error);
157
158 END Install_Package_Object;
159
160 -- Procedure Name
161 -- call_start_debug
162 -- Purpose
163 -- Generates code to call start debugging message procedure.
164 -- History
165 -- 01-26-96 Jin Cheng Created
166
167 PROCEDURE call_start_debug(
168 procedure_name IN cn_obj_procedures_v.NAME%TYPE,
169 x_event_id IN cn_events.event_id%TYPE,
170 code IN OUT NOCOPY cn_utils.code_type,
171 x_org_id IN NUMBER)
172 IS
173
174 x_event_name cn_lookups.lookup_code%TYPE := 'CUSTOM';
175
176 BEGIN
177
178 cn_utils.set_org_id(x_org_id);
179
180 cn_utils.appendcr(code);
181
182 cn_utils.appindcr(code, 'x_proc_audit_id := NULL; -- Will get a value in the call below FOR TESTING');
183
184 IF (x_event_id = cn_global.inv_event_id) THEN
185 x_event_name := 'INV';
186
187 ELSIF (x_event_id = cn_global.pmt_event_id) THEN
188 x_event_name := 'PMT/GB';
189
190 ELSIF (x_event_id = cn_global.cbk_event_id) THEN
191 x_event_name := 'CB';
192
193 ELSIF (x_event_id = cn_global.wo_event_id) THEN
194 x_event_name := 'WO';
195
196 ELSIF (x_event_id = cn_global.ram_event_id) THEN
197 x_event_name := 'RAM';
198
199 ELSIF (x_event_id = cn_global.ord_event_id) THEN
200 x_event_name := 'ORD';
201
202 END IF;
203
204 IF(x_event_id > 0)
205 THEN
206 IF CN_COLLECTION_GEN.isParallelEnabled THEN
207 cn_utils.appindcr(code, 'EXECUTE IMMEDIATE ''ALTER SESSION ENABLE PARALLEL DML'';');
208 END IF;
209 END IF;
210
211 cn_utils.appindcr(code, 'cn_message_pkg.begin_batch(');
212 cn_utils.indent(code, 5);
213
214 cn_utils.appindcr(code, ' x_parent_proc_audit_id => dummy_num');
215 cn_utils.appindcr(code, ',x_process_audit_id => x_proc_audit_id');
216 cn_utils.appindcr(code, ',x_request_id => fnd_global.conc_request_id');
217 cn_utils.appindcr(code, ',x_process_type => '''|| x_event_name ||'''');
218 cn_utils.appindcr(code, ',p_org_id => x_org_id );');
219
220 cn_utils.unindent(code, 5);
221
222 cn_utils.appindcr(code, 'x_col_audit_id := x_proc_audit_id;');
223
224 --Condition added to fix bug 6203234
225 IF(x_event_id > 0)
226 THEN
227 cn_utils.record_process_start('COL', '''Collection run for process '' || x_proc_audit_id', 'x_col_audit_id', code);
228 END IF;
229
230 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| procedure_name || '>>'');');
231 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name || '>>'');');
232 cn_utils.appindcr(code, 'MO_GLOBAL.SET_POLICY_CONTEXT (''S'','||x_org_id||');');
233 cn_utils.appendcr(code);
234
235 cn_utils.unset_org_id();
236
237 END call_start_debug;
238
239
240
241 -- Procedure Name
242 -- call_notify
243 -- Purpose
244 -- Generates code to call the notification procedure.
245 -- History
246
247 PROCEDURE call_notify (
248 x_package_id cn_obj_packages_v.package_id%TYPE,
249 procedure_name cn_obj_procedures_v.NAME%TYPE,
250 x_module_id cn_modules.module_id%TYPE,
251 x_repository_id cn_repositories.repository_id%TYPE,
252 x_event_id cn_events.event_id%TYPE,
253 code IN OUT NOCOPY cn_utils.code_type,
254 x_org_id IN NUMBER) IS
255
256 x_row_count NUMBER;
257
258 BEGIN
259
260 cn_utils.set_org_id(x_org_id);
261
262 cn_utils.appendcr(code);
263 cn_utils.appendcr(code, '--******** NOTIFY PROCESS ********-- ');
264 cn_utils.appendcr(code);
265 cn_utils.unset_org_id();
266 cn_debug.print_msg('call_notify >>', 1);
267 fnd_file.put_line(fnd_file.Log, 'call_notify >>');
268
269 -- Translate the incoming period_names into period_ids. AE 02-28-96
270 -- We had to do this because AOL only passes VARCHAR2 input parms.
271
272 cn_utils.set_org_id(x_org_id);
273 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| procedure_name || ': Call notify process begin.'');');
274 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': Call notify process begin.'');');
275 cn_utils.appindcr(code, 'SELECT period_id ');
276 cn_utils.appindcr(code, ' INTO x_start_period_id ');
277 cn_utils.appindcr(code, ' FROM cn_periods ');
278 cn_utils.appindcr(code, 'WHERE period_name = x_start_period_name' );
279 cn_utils.appindcr(code, 'AND org_id = '||x_org_id||' ;');
280
281 cn_utils.appendcr(code);
282 cn_utils.appindcr(code, 'SELECT period_id ');
283 cn_utils.appindcr(code, ' INTO x_end_period_id ');
284 cn_utils.appindcr(code, ' FROM cn_periods ');
285 cn_utils.appindcr(code, 'WHERE period_name = x_end_period_name' );
286 cn_utils.appindcr(code, 'AND org_id = '||x_org_id||' ;');
287 cn_utils.appendcr(code);
288 cn_utils.unset_org_id();
289
290 -- Generate the call statement
291 cn_debug.print_msg('call_notify: Generating CALL statement.', 1);
292 fnd_file.put_line(fnd_file.Log, 'call_notify: Generating CALL statement.');
293
294 cn_utils.set_org_id(x_org_id);
295 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| procedure_name || ': entering notify.'');');
296 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': entering notify.'');');
297
298 IF (x_event_id = cn_global.inv_event_id) THEN
299 cn_utils.appindcr(code, 'cn_notify_invoices.notify(x_start_period_id, x_end_period_id, debug_pipe, debug_level,x_org_id);' );
300
301 ELSIF (x_event_id = cn_global.pmt_event_id) THEN
302 cn_utils.appindcr(code, 'cn_notify_payments.notify(x_start_period_id, x_end_period_id, debug_pipe, debug_level,x_org_id);' );
303
304 ELSIF (x_event_id = cn_global.cbk_event_id) THEN
305 cn_utils.appindcr(code, 'cn_notify_clawbacks.notify(x_start_period_id, x_end_period_id, debug_pipe, debug_level,x_org_id);' );
306
307 ELSIF (x_event_id = cn_global.wo_event_id) THEN
308 cn_utils.appindcr(code, 'cn_notify_writeoffs.notify(x_start_period_id, x_end_period_id, debug_pipe, debug_level,x_org_id);' );
309
310 ELSIF (x_event_id = cn_global.ord_event_id) THEN
311 cn_utils.appindcr(code, 'cn_notify_orders.regular_col_notify(');
312 cn_utils.indent(code, 8);
313 cn_utils.appindcr(code, ' x_start_period => x_start_period_id');
314 cn_utils.appindcr(code, ',x_end_period => x_end_period_id');
315 cn_utils.appindcr(code, ',x_adj_flag => ''N''');
316 cn_utils.appindcr(code, ',parent_proc_audit_id => x_proc_audit_id');
317 cn_utils.appindcr(code, ',debug_pipe => debug_pipe');
318 cn_utils.appindcr(code, ',debug_level => debug_level');
319 cn_utils.appindcr(code, ',x_org_id => '||x_org_id ||' );');
320 cn_utils.unindent(code, 8);
321
322 END IF;
323 cn_utils.appendcr(code);
324 cn_utils.unset_org_id();
325
326 cn_debug.print_msg('call_notify <<', 1);
327 fnd_file.put_line(fnd_file.Log, 'call_notify <<');
328
329 cn_utils.set_org_id(x_org_id);
330 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| procedure_name || ': exit from notify and start collection run.'');');
331 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': exit from notify and start collection run.'');');
332
333 IF (x_event_id = cn_global.ord_event_id) THEN
334 cn_utils.appendcr(code, '--******** COLLECT AJUSTMENTS (NEGATE IN API) ********-- ');
335 cn_utils.appindcr(code, '-- This will negate those adjusted trx in the API table');
336 cn_utils.appindcr(code, 'cn_not_trx_grp.col_adjustments(p_api_version => 1.0,');
337 cn_utils.appindcr(code, ' x_return_status => x_return_status,');
338 cn_utils.appindcr(code, ' x_msg_count => x_msg_count,');
339 cn_utils.appindcr(code, ' x_msg_data => x_msg_data,');
340 cn_utils.appindcr(code, ' p_org_id => '||x_org_id ||');');
341 cn_utils.appendcr(code);
342
343 END IF;
344
345 cn_utils.appendcr(code);
346
347 cn_utils.unset_org_id();
348
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351 cn_debug.print_msg('call_notify: in exception handler for NO_DATA_FOUND', 1);
352 fnd_file.put_line(fnd_file.Log, 'call_notify: in exception handler for NO_DATA_FOUND');
353 RETURN;
354 END call_notify;
355
356
357 -- Procedure Name
358 -- call_identify
359 -- Purpose
360 -- Generates code to call the RAM identify procedure.
361 -- History
362 -- 03-26-02 Harlen Chen Created
363
364
365 PROCEDURE call_identify (
366 x_package_id cn_obj_packages_v.package_id%TYPE,
367 procedure_name cn_obj_procedures_v.NAME%TYPE,
368 x_module_id cn_modules.module_id%TYPE,
369 x_repository_id cn_repositories.repository_id%TYPE,
370 x_event_id cn_events.event_id%TYPE,
371 code IN OUT NOCOPY cn_utils.code_type,
372 x_org_id IN NUMBER)
373 IS
374
375 x_row_count NUMBER;
376
377 BEGIN
378
379 l_org_id := x_org_id;
380
381 cn_utils.set_org_id(x_org_id);
382
383 cn_utils.appendcr(code);
384 cn_utils.appendcr(code, '--******** IDENTIFY PROCESS ********-- ');
385 cn_utils.appendcr(code);
386 cn_utils.unset_org_id();
387
388 cn_debug.print_msg('call_identify >>', 1);
389 fnd_file.put_line(fnd_file.Log, 'call_identify >>');
390
391 cn_utils.set_org_id(x_org_id);
392 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| procedure_name || ': start identify process.'');');
393 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': start identify process.'');');
394 cn_utils.appendcr(code);
395 cn_utils.appindcr(code, 'SELECT period_id ');
396 cn_utils.appindcr(code, ' INTO x_start_period_id ');
397 cn_utils.appindcr(code, ' FROM cn_periods ');
398 cn_utils.appindcr(code, 'WHERE period_name = x_start_period_name' );
399 cn_utils.appindcr(code, 'AND org_id = '||X_org_id||' ;');
400 cn_utils.appendcr(code);
401
402 cn_utils.appindcr(code, 'SELECT period_id ');
403 cn_utils.appindcr(code, ' INTO x_end_period_id ');
404 cn_utils.appindcr(code, ' FROM cn_periods ');
405 cn_utils.appindcr(code, 'WHERE period_name = x_end_period_name' );
406 cn_utils.appindcr(code, 'AND org_id = '||X_org_id||' ;');
407 cn_utils.appendcr(code);
408 cn_utils.unset_org_id();
409
410 -- Generate the call statement
411 cn_debug.print_msg('call_identify: Generating CALL statement.', 1);
412 fnd_file.put_line(fnd_file.Log, 'call_identify: Generating CALL statement.');
413
414 cn_utils.set_org_id(x_org_id);
415 cn_utils.appindcr(code, 'cn_ram_adjustments_pkg.identify(x_start_period_id, x_end_period_id, debug_pipe, debug_level,x_org_id);' );
416 cn_utils.appendcr(code);
417 cn_utils.unset_org_id();
418
419 cn_debug.print_msg('call_identify <<', 1);
420 fnd_file.put_line(fnd_file.Log, 'call_identify <<');
421
422 cn_utils.set_org_id(x_org_id);
423 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| procedure_name || ': end identify process.'');');
424 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': end identify process.'');');
425 cn_utils.appendcr(code);
426
427 cn_utils.unset_org_id();
428 EXCEPTION
429
430 WHEN NO_DATA_FOUND
431 THEN
432 cn_debug.print_msg('call_identify: in exception handler for NO_DATA_FOUND', 1);
433 fnd_file.put_line(fnd_file.Log, 'call_identify: in exception handler for NO_DATA_FOUND');
434 RETURN;
435
436 END call_identify;
437
438
439 -- Procedure Name
440 -- call_negate
441 -- Purpose
442 -- Generates code to call the RAM identify procedure.
443 -- History
444 -- 03-26-02 Harlen Chen Created
445
446 PROCEDURE call_negate (
447 x_package_id cn_obj_packages_v.package_id%TYPE,
448 procedure_name cn_obj_procedures_v.NAME%TYPE,
449 x_module_id cn_modules.module_id%TYPE,
450 x_repository_id cn_repositories.repository_id%TYPE,
451 x_event_id cn_events.event_id%TYPE,
452 code IN OUT NOCOPY cn_utils.code_type,
453 x_org_id IN NUMBER)
454 IS
455
456 x_row_count NUMBER;
457
458 BEGIN
459
460 cn_utils.set_org_id(x_org_id);
461
462 cn_utils.appendcr(code);
463 cn_utils.appendcr(code, '--******** NEGATE PROCESS ********-- ');
464 cn_utils.appendcr(code);
465 cn_utils.unset_org_id();
466
467 cn_debug.print_msg('call_negate >>', 1);
468 fnd_file.put_line(fnd_file.Log, 'call_negate >>');
469
470 cn_utils.set_org_id(x_org_id);
471 cn_utils.appindcr(code, 'x_ram_negate_profile := CN_SYSTEM_PARAMETERS.value(''CN_RAM_NEGATE'','|| x_org_id ||');');
472 cn_utils.appendcr(code);
473 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| procedure_name ||
474 ': Profile OSC: Negate during Revenue Adjustments Collection = '' || x_ram_negate_profile);');
475 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name ||
476 ': Profile OSC: Negate during Revenue Adjustments Collection = '' || x_ram_negate_profile);');
477 cn_utils.appendcr(code);
478 cn_utils.appindcr(code, 'IF x_ram_negate_profile = ''Y'' THEN ');
479 cn_utils.appindcr(code, ' cn_message_pkg.debug('''|| procedure_name || ': start negate process.'');');
480 cn_utils.appindcr(code, ' fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': start negate process.'');');
481 cn_utils.appindcr(code, ' cn_ram_adjustments_pkg.negate(debug_pipe, debug_level,'||x_org_id||');' );
482 cn_utils.appindcr(code, ' cn_message_pkg.debug('''|| procedure_name || ': end negate process.'');');
483 cn_utils.appindcr(code, ' fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': end negate process.'');');
484 cn_utils.appindcr(code, 'ELSE');
485 cn_utils.appindcr(code, ' cn_message_pkg.debug('''|| procedure_name || ': skip negate process.'');');
486 cn_utils.appindcr(code, ' fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': skip negate process.'');');
487 cn_utils.appendcr(code);
488 cn_utils.appindcr(code, ' UPDATE cn_trx_lines ctl');
489 cn_utils.appindcr(code, ' SET negated_flag = ''Y''');
490 cn_utils.appindcr(code, ' WHERE ctl.adjusted_flag = ''Y'' and');
491 cn_utils.appindcr(code, ' ctl.negated_flag = ''N'' and');
492 cn_utils.appindcr(code, ' ctl.collected_flag = ''N'' and');
493 cn_utils.appindcr(code, ' ctl.event_id = cn_global.inv_event_id');
494 cn_utils.appindcr(code, ' AND ctl.org_id = '||x_org_id||' ;');
495 cn_utils.appendcr(code);
496 cn_utils.appindcr(code, 'END IF;');
497 cn_utils.appendcr(code);
498 cn_utils.unset_org_id();
499
500 cn_debug.print_msg('call_negate <<', 1);
501 fnd_file.put_line(fnd_file.Log, 'call_negate <<');
502
503 cn_message_pkg.DEBUG('collect: Profile OSC: Negate during Revenue Adjustments Collection = ' ||
504 CN_SYSTEM_PARAMETERS.value('CN_RAM_NEGATE',x_org_id));
505 fnd_file.put_line(fnd_file.Log,'collect: Profile OSC: Negate during Revenue Adjustments Collection = ' ||
506 CN_SYSTEM_PARAMETERS.value('CN_RAM_NEGATE',x_org_id));
507
508 EXCEPTION
509 WHEN NO_DATA_FOUND THEN
510 cn_debug.print_msg('call_negate: in exception handler for NO_DATA_FOUND', 1);
511 fnd_file.put_line(fnd_file.Log, 'call_negate: in exception handler for NO_DATA_FOUND');
512 RETURN;
513 END call_negate;
514
515
516
517 -- Procedure Name
518 -- local_variables
519 -- Purpose
520 -- Generates some boilerplate text to declare local variables
521 -- History
522 -- 17-NOV-93 Devesh Khatu Created
523
524 PROCEDURE local_variables (
525 procedure_name VARCHAR2,
526 x_event_id cn_events.event_id%TYPE,
527 code IN OUT NOCOPY cn_utils.code_type,
528 x_org_id IN NUMBER) IS
529 BEGIN
530
531
532
533 IF (x_event_id = cn_global.ram_event_id) THEN
534
535 cn_debug.print_msg('local_variables>>', 1);
536 fnd_file.put_line(fnd_file.Log, 'local_variables>>');
537
538 cn_utils.set_org_id(x_org_id);
539 cn_utils.indent(code, 1);
540 cn_utils.appindcr(code, 'trx_sales_line_count NUMBER := 0;');
541 cn_utils.appindcr(code, 'trx_update_count NUMBER := 0;');
542 cn_utils.appindcr(code, 'trx_line_update_count NUMBER := 0;');
543 cn_utils.appindcr(code, 'trx_sales_line_update_count NUMBER := 0;');
544 cn_utils.appindcr(code, 'comm_lines_api_count NUMBER := 0;');
545 cn_utils.appindcr(code, 'comm_lines_api_update_count NUMBER := 0;');
546 cn_utils.appindcr(code, 'x_start_period_id NUMBER(15);');
547 cn_utils.appindcr(code, 'x_end_period_id NUMBER(15);');
548 cn_utils.appindcr(code, 'x_col_audit_id NUMBER;');
549 cn_utils.appindcr(code, 'x_proc_audit_id NUMBER;');
550 cn_utils.appindcr(code, 'x_conc_program_id NUMBER;');
551 cn_utils.appindcr(code, 'x_adj_batch_id NUMBER;');
552 cn_utils.appindcr(code, 'x_rowid ROWID;');
553 cn_utils.appindcr(code, 'debug_pipe VARCHAR2(30);');
554 cn_utils.appindcr(code, 'debug_level NUMBER := 1 ;');
555 cn_utils.appindcr(code, 'dummy_num NUMBER ;');
556 cn_utils.appindcr(code, 'x_return_status VARCHAR2(1);');
557 cn_utils.appindcr(code, 'x_msg_count NUMBER;');
558 cn_utils.appindcr(code, 'x_msg_data VARCHAR(2000);');
559 cn_utils.appindcr(code, 'x_created_by NUMBER := to_number(fnd_global.user_id);');
560 cn_utils.appindcr(code, 'x_creation_date DATE := sysdate;');
561 cn_utils.appindcr(code, 'x_last_updated_by NUMBER := to_number(fnd_global.user_id);');
562 cn_utils.appindcr(code, 'x_last_update_date DATE := sysdate;');
563 cn_utils.appindcr(code, 'x_last_update_login NUMBER := to_number(fnd_global.login_id);');
564 cn_utils.appindcr(code, 'x_ram_negate_profile VARCHAR2(1);');
565 --cn_utils.appindcr(code, 'X_org_id NUMBER;');
566
567 cn_utils.appendcr(code);
568 cn_utils.appindcr(code, 'CURSOR batches IS');
569 cn_utils.appindcr(code, ' SELECT DISTINCT adj_batch_id');
570 cn_utils.appindcr(code, ' FROM cn_trx_lines');
571 cn_utils.appindcr(code, ' WHERE adjusted_flag = ''Y'' AND');
572 cn_utils.appindcr(code, ' negated_flag = ''Y'' AND');
573 cn_utils.appindcr(code, ' collected_flag = ''N'' AND');
574 cn_utils.appindcr(code, ' event_id = cn_global.inv_event_id AND ');
575 cn_utils.appindcr(code, ' org_id = '||x_org_id||' ;');
576 cn_utils.appendcr(code);
577
578 cn_utils.unindent(code, 1);
579 cn_utils.unset_org_id();
580
581 cn_debug.print_msg('local_variables<<', 1);
582 fnd_file.put_line(fnd_file.Log, 'local_variables<<');
583
584 ELSE
585
586 cn_debug.print_msg('local_variables>>', 1);
587 fnd_file.put_line(fnd_file.Log, 'local_variables<<');
588
589 cn_utils.set_org_id(x_org_id);
590 cn_utils.indent(code, 1);
591 cn_utils.appindcr(code, 'trx_count NUMBER := 0;');
592 cn_utils.appindcr(code, 'trx_line_count NUMBER := 0;');
593 cn_utils.appindcr(code, 'trx_sales_line_count NUMBER := 0;');
594 cn_utils.appindcr(code, 'trx_update_count NUMBER := 0;'); --JC 02-13-97
595 cn_utils.appindcr(code, 'trx_line_update_count NUMBER := 0;');
596 cn_utils.appindcr(code, 'trx_sales_line_update_count NUMBER := 0;');
597 cn_utils.appindcr(code, 'comm_lines_api_count NUMBER := 0;');
598 cn_utils.appindcr(code, 'comm_lines_api_update_count NUMBER := 0;');
599 cn_utils.appindcr(code, 'x_start_period_id NUMBER(15);'); --AE 02-28-96
600 cn_utils.appindcr(code, 'x_end_period_id NUMBER(15);'); --AE 02-28-96
601 cn_utils.appindcr(code, 'x_col_audit_id NUMBER;'); --AE 08-01-95
602 cn_utils.appindcr(code, 'x_proc_audit_id NUMBER;');
603 cn_utils.appindcr(code, 'x_conc_program_id NUMBER;'); --AE 01-18-96
604 cn_utils.appindcr(code, 'x_batch_id NUMBER;');
605 cn_utils.appindcr(code, 'x_rowid ROWID;');
606 cn_utils.appindcr(code, 'debug_pipe VARCHAR2(30);'); --AE 02-28-96
607 cn_utils.appindcr(code, 'debug_level NUMBER := 1 ;'); --AE 02-28-96
608 cn_utils.appindcr(code, 'dummy_num NUMBER ;'); --JC 01-27-96
609 cn_utils.appindcr(code, 'x_return_status VARCHAR2(1);'); --DM 11-03-99
610 cn_utils.appindcr(code, 'x_msg_count NUMBER;'); --DM 11-03-99
611 cn_utils.appindcr(code, 'x_msg_data VARCHAR(2000);'); --DM 11-03-99
612 cn_utils.appindcr(code, 'x_created_by NUMBER := to_number(fnd_global.user_id);');
613 cn_utils.appindcr(code, 'x_creation_date DATE := sysdate;');
614 cn_utils.appindcr(code, 'x_last_updated_by NUMBER := to_number(fnd_global.user_id);');
615 cn_utils.appindcr(code, 'x_last_update_date DATE := sysdate;');
616 cn_utils.appindcr(code, 'x_last_update_login NUMBER := to_number(fnd_global.login_id);');
617 --cn_utils.appindcr(code, 'X_org_id NUMBER ;');
618 cn_utils.appendcr(code);
619
620
621 -- Change made to fix bug 6203234
622 if(x_event_id < 0)
623 then
624 cn_utils.appindcr(code, 'CURSOR batches IS');
625 cn_utils.appindcr(code, ' SELECT DISTINCT batch_id');
626 cn_utils.appindcr(code, ' FROM cn_not_trx');
627 cn_utils.appindcr(code, ' WHERE collected_flag = ''N''');
628 cn_utils.appindcr(code, ' AND event_id = ' || x_event_id );
629 cn_utils.appindcr(code, ' AND org_id = '||x_org_id||' ;');
630 cn_utils.appendcr(code);
631 cn_utils.unindent(code, 1);
632 cn_utils.unset_org_id();
633 end if;
634 cn_debug.print_msg('local_variables<<', 1);
635 fnd_file.put_line(fnd_file.Log, 'local_variables<<');
636 END IF; -- IF (procedure_name = 'cn_collect_ram')
637
638 cn_utils.unset_org_id;
639
640 END local_variables;
641 -- Procedure Name
642 -- who
643 -- Purpose
644 -- Generates some text to record the start of a collection
645 -- History
646 -- 17-NOV-93 Devesh Khatu Created
647
648 PROCEDURE who (
649 procedure_name cn_obj_procedures_v.NAME%TYPE,
650 code IN OUT NOCOPY cn_utils.code_type,
651 x_org_id IN NUMBER) IS
652 BEGIN
653 -- The who code has been temporarily commented out since the who package
654 -- is yet to be implemented.
655
656 cn_debug.print_msg('who>>', 1);
657 fnd_file.put_line(fnd_file.Log, 'who>>');
658
659 cn_utils.set_org_id(x_org_id);
660 cn_utils.appindcr(code, '-- who.set_program_name(''' || procedure_name || ''');');
661 cn_utils.appendcr(code);
662 cn_utils.unset_org_id();
663
664 cn_debug.print_msg('who<<', 1);
665 fnd_file.put_line(fnd_file.Log, 'who<<');
666
667 END who;
668
669 -- Procedure Name
670 -- proc_exception
671 -- Purpose
672 -- Generates some text to handle the exception
673 -- History
674 -- 01-27-97 Jin Cheng Created
675
676 PROCEDURE proc_exception (
677 x_procedure_name cn_obj_procedures_v.NAME%TYPE,
678 savepoint_name VARCHAR2,
679 location VARCHAR2,
680 code IN OUT NOCOPY cn_utils.code_type,
681 x_org_id IN NUMBER)
682 IS
683 BEGIN
684
685 cn_utils.set_org_id(x_org_id);
686 cn_utils.unindent(code, 1);
687 cn_utils.appindcr(code, 'EXCEPTION');
688 cn_utils.indent(code, 1);
689 IF (savepoint_name IS NOT NULL) THEN
690 cn_utils.appindcr(code, 'WHEN OTHERS THEN ROLLBACK TO ' || savepoint_name || ';');
691 cn_utils.appindcr(code, 'cn_message_pkg.debug(''' || x_procedure_name || ': Rollback.'');');
692 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''' || x_procedure_name || ': Rollback.'');');
693 ELSE
694 cn_utils.appindcr(code, 'WHEN OTHERS THEN ');
695 END IF;
696
697 cn_utils.appindcr(code, 'cn_message_pkg.debug(''' || x_procedure_name || ': In exception handler ' || location ||'.'');');
698 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''' || x_procedure_name || ': In exception handler ' || location ||'.'');');
699 cn_utils.appendcr(code);
700
701 cn_utils.appindcr(code, 'cn_process_audits_pkg.update_row(x_proc_audit_id, NULL, SYSDATE, SQLCODE,');
702 cn_utils.appindcr(code, ' SQLERRM);');
703 cn_utils.appendcr(code);
704 cn_utils.appindcr(code, 'cn_message_pkg.debug(sqlcode ||'''||' '||'''||sqlerrm);');
705 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, sqlcode ||'''||' '||'''||sqlerrm);');
706 cn_utils.appindcr(code, 'cn_message_pkg.end_batch(x_proc_audit_id);');
707 cn_utils.appendcr(code);
708
709 cn_utils.appindcr(code, 'app_exception.raise_exception;');
710 cn_utils.appendcr(code);
711 cn_utils.unset_org_id();
712
713 END proc_exception;
714
715 -- Procedure Name
716 -- pkg_proc_init
717 -- Purpose
718 -- This procedure generates procedure init code
719 -- History
720 -- 17-NOV-93 Devesh Khatu Created
721
722 PROCEDURE pkg_proc_init (
723 x_table_map_id cn_table_maps.table_map_id%TYPE,
724 x_procedure_name cn_obj_procedures_v.NAME%TYPE,
725 x_description cn_obj_procedures_v.description%TYPE,
726 x_parameter_list cn_obj_procedures_v.parameter_list%TYPE,
727 x_package_id cn_obj_packages_v.package_id%TYPE,
728 x_module_id cn_modules.module_id%TYPE, --AE 01-26-96
729 x_repository_id cn_repositories.repository_id%TYPE,
730 x_event_id cn_events.event_id%TYPE,
731 x_generic BOOLEAN,
732 spec_code IN OUT NOCOPY cn_utils.code_type,
733 body_code IN OUT NOCOPY cn_utils.code_type,
734 x_org_id IN NUMBER)
735 IS
736
737 BEGIN
738
739 l_org_id := x_org_id;
740
741 cn_debug.print_msg('pkg_proc_init>>', 1);
742 fnd_file.put_line(fnd_file.Log, 'pkg_proc_init>>');
743
744 cn_utils.set_org_id(X_org_id);
745 cn_utils.proc_init(x_procedure_name, x_description, x_parameter_list,
746 'P', NULL, x_package_id, x_repository_id, spec_code, body_code);
747
748 -- Generate code for declaring local variables
749 local_variables(x_procedure_name, x_event_id, body_code,l_org_id);
750
751 cn_utils.set_org_id(X_org_id);
752 cn_utils.proc_begin(x_procedure_name, 'Y', body_code);
753
754 -- Generate code to update who information
755 -- who(x_procedure_name, body_code);
756
757 -- Generate code to start debugging messages
758
759 --dbms_output.put_line(' Call Start Debug <<< ');
760 call_start_debug(x_procedure_name, x_event_id, body_code,X_org_id);
761 --dbms_output.put_line(' Call Start Debug >>> ');
762
763
764 -- Call_Notify generates different fixed notification code for
765 -- either the AR or OC mappings. For a completely generic (data-
766 -- driven) source, different code, generated by the cn_collection_custom_gen
767 -- package, is used.
768 IF NOT x_generic THEN
769
770 -- Insert any User Code specified for the 'Pre-Notification' location
771 cn_utils.unset_org_id();
772
773 cn_collection_custom_gen.Generate_User_Code(
774 p_table_map_id => x_table_map_id,
775 p_location_name => 'Pre-Notification',
776 code => body_code,
777 X_ORG_ID => X_ORG_ID);
778
779 IF (x_event_id = cn_global.ram_event_id) THEN -- RAM identify process
780 call_identify(x_package_id, x_procedure_name, x_module_id,x_repository_id, x_event_id, body_code,X_ORG_ID);
781 ELSE -- other notify process
782 call_notify(x_package_id, x_procedure_name, x_module_id,x_repository_id, x_event_id, body_code,X_ORG_ID);
783 END IF;
784
785 ELSE
786 cn_utils.unset_org_id();
787 cn_collection_custom_gen.insert_cn_not_trx (
788 x_table_map_id => x_table_map_id,
789 x_event_id => x_event_id,
790 code => body_code,
791 X_ORG_ID => X_ORG_ID);
792 END IF;
793
794
795 -- Insert any User Code specified for the 'Post-Notification' location
796 cn_utils.unset_org_id();
797 cn_collection_custom_gen.Generate_User_Code(
798 p_table_map_id => x_table_map_id,
799 p_location_name => 'Post-Notification',
800 code => body_code,
801 X_ORG_ID => X_ORG_ID);
802
803 IF (x_event_id = cn_global.ram_event_id) THEN -- RAM identify process
804 call_negate(x_package_id, x_procedure_name, x_module_id,x_repository_id, x_event_id, body_code,X_org_id);
805 END IF;
806
807 cn_utils.set_org_id(X_org_id);
808 cn_utils.appendcr(body_code, '--******** COLLECT PROCESS ********-- ');
809 cn_utils.appendcr(body_code);
810 cn_utils.appindcr(body_code, 'cn_message_pkg.debug('''|| x_procedure_name || ': start collection process.'');');
811
812 /* -- Generate code to record start of the collection
813 cn_utils.record_process_start('COL', '''Collection run''', 'NULL', body_code);
814
815 cn_utils.appindcr(body_code, 'x_col_audit_id := x_proc_audit_id;'); --AE 08-10-95
816 cn_utils.appendcr(body_code); */ -- JC 01-26-97
817
818 cn_utils.appindcr(body_code, 'x_conc_program_id := fnd_global.conc_program_id;'); --AE 01-18-96
819
820 --Condition added to fix bug 6203234
821 IF(x_event_id < 0)
822 THEN
823 cn_utils.appindcr(body_code, 'cn_message_pkg.debug('''|| x_procedure_name || ': entering cursor Batches loop.'');');
824 cn_utils.appindcr(body_code, 'fnd_file.put_line(fnd_file.Log, '''|| x_procedure_name || ': entering cursor Batches loop.'');');
825 cn_utils.appendcr(body_code);
826
827 cn_utils.appindcr(body_code, 'FOR b IN batches LOOP');
828 cn_utils.appendcr(body_code);
829 cn_utils.indent(body_code, 1);
830
831 IF (x_event_id = cn_global.ram_event_id) THEN -- RAM identify process
832 cn_utils.appindcr(body_code, 'x_adj_batch_id := b.adj_batch_id;');
833 cn_utils.appendcr(body_code);
834 cn_utils.record_process_start('COL', '''RAM Adjustments Collection run for batch '' || x_adj_batch_id', 'x_col_audit_id', body_code);
835 ELSE
836 cn_utils.appindcr(body_code, 'x_batch_id := b.batch_id;');
837 cn_utils.appendcr(body_code);
838 cn_utils.record_process_start('COL', '''Collection run for batch '' || x_batch_id', 'x_col_audit_id', body_code);
839 END IF;
840
841 cn_utils.appindcr(body_code, 'BEGIN');
842 cn_utils.indent(body_code, 1);
843 cn_utils.appindcr(body_code, 'SAVEPOINT start_transaction;');
844 cn_utils.appendcr(body_code);
845 END IF;
846 cn_utils.unset_org_id();
847 cn_debug.print_msg('pkg_proc_init<<', 1);
848 fnd_file.put_line(fnd_file.Log, 'pkg_proc_init<<');
849
850
851 END pkg_proc_init;
852
853
854
855 -- Procedure Name
856 -- collect_stmts
857 -- Purpose
858 -- This procedure generates the collection code
859 -- History
860 -- 17-NOV-93 Devesh Khatu Created
861
862 PROCEDURE collect_stmts (
863 x_package_id cn_obj_packages_v.package_id%TYPE,
864 x_procedure_name cn_obj_procedures_v.NAME%TYPE,
865 x_table_map_id cn_table_maps.table_map_id%TYPE,
866 x_module_id cn_modules.module_id%TYPE,
867 x_repository_id cn_repositories.repository_id%TYPE,
868 x_generic BOOLEAN,
869 x_event_id cn_events.event_id%TYPE,
870 code IN OUT NOCOPY cn_utils.code_type,
871 x_org_id IN NUMBER) IS
872
873 BEGIN
874
875 l_org_id := x_org_id;
876
877 cn_debug.print_msg('collect_stmts>>', 1);
878 fnd_file.put_line(fnd_file.Log, 'collect_stmts>>');
879
880 IF x_generic OR
881 x_event_id = cn_global.ord_event_id THEN
882 IF x_generic THEN
883
884 -- This is a package whose contents are comletely data-driven
885 -- Generate code to insert into API table for collection.
886
887 cn_collection_custom_gen.insert_comm_lines_api (x_table_map_id, x_event_id, code,X_org_id);
888 ELSE
889
890 -- This is for Order Capture.
891 -- Generate code to insert into API table for OE collection.
892 cn_collection_oe_gen.insert_comm_lines_api
893 (x_table_map_id, x_package_id, x_procedure_name, x_module_id,
894 x_repository_id, x_event_id, code,X_org_id);
895 END IF;
896
897 -- Insert any User Code specified for the 'Pre-Api-Update' location
898
899 cn_collection_custom_gen.Generate_User_Code(p_table_map_id => x_table_map_id,
900 p_location_name => 'Pre-Api-Update',
901 code => code,
902 X_ORG_ID => X_ORG_ID);
903
904 -- Generate code to apply Indirect Mappings to cn_comm_lines_api
905 --dbms_output.put_line('B4 cn_collection_custom_gen.update_comm_lines_api');
906 --COMMIT;
907 cn_collection_custom_gen.update_comm_lines_api (x_table_map_id, code,X_org_id);
908 --dbms_output.put_line('After cn_collection_custom_gen.update_comm_lines_api');
909 --+
910 -- Insert any User Code specified for the 'Pre-Api-Filter' location
911 --+
912
913 cn_collection_custom_gen.Generate_User_Code(p_table_map_id => x_table_map_id,
914 p_location_name => 'Pre-Api-Filter',
915 code => code,
916 X_ORG_ID => X_ORG_ID);
917 --+
918 -- Generate code to apply Filters to cn_comm_lines_api
919 --+
920
921 cn_collection_custom_gen.filter_comm_lines_api (x_table_map_id, code,X_org_id);
922
923 --+
924 -- Insert any User Code specified for the 'Post-Api-Filter' location
925 --+
926 cn_collection_custom_gen.Generate_User_Code(p_table_map_id => x_table_map_id,
927 p_location_name => 'Post-Api-Filter',
928 code => code,
929 X_ORG_ID => X_ORG_ID);
930
931 ELSE -- Receivables package
932 IF (x_event_id = cn_global.ram_event_id) THEN -- RAM Collection
933
934 cn_utils.set_org_id(X_org_id);
935 cn_utils.appendcr(code);
936 cn_utils.appendcr(code, '--******** UPDATE CN_TRX CN_TRX_LINES ********-- ');
937 cn_utils.appindcr(code, 'cn_message_pkg.debug(''' || x_procedure_name || ': update CN_TRX and CN_TRX_LINES.''); ');
938 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''' || x_procedure_name || ': update CN_TRX and CN_TRX_LINES.''); ');
939 cn_utils.appendcr(code);
940 cn_utils.appindcr(code, 'UPDATE cn_trx ');
941 cn_utils.appindcr(code, ' SET adj_collection_run_id = x_proc_audit_id ');
942 cn_utils.appindcr(code, ' WHERE trx_id IN (SELECT trx_id from cn_trx_lines ');
943 cn_utils.appindcr(code, ' WHERE adjusted_flag = ''Y'' ');
944 cn_utils.appindcr(code, ' AND negated_flag = ''Y'' ');
945 cn_utils.appindcr(code, ' AND collected_flag = ''N'' ');
946 cn_utils.appindcr(code, ' AND event_id = cn_global.inv_event_id ');
947 cn_utils.appindcr(code, ' AND adj_batch_id = x_adj_batch_id ');
948 cn_utils.appindcr(code, ' AND org_id = '||x_org_id||')');
949 cn_utils.appindcr(code, ' AND org_id = '||x_org_id||';');
950 cn_utils.appendcr(code);
951 cn_utils.appindcr(code, 'trx_update_count := SQL%ROWCOUNT; ');
952 cn_utils.appindcr(code, 'cn_message_pkg.debug(''' || x_procedure_name || ': Updated '' || trx_update_count || '' records in cn_trx.''); ');
953 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''' || x_procedure_name || ': Updated '' || trx_update_count || '' records in cn_trx.''); ');
954 cn_utils.appendcr(code);
955 cn_utils.appindcr(code, 'UPDATE cn_trx_lines ');
956 cn_utils.appindcr(code, ' SET adj_collection_run_id = x_proc_audit_id ');
957 cn_utils.appindcr(code, ' WHERE adjusted_flag = ''Y'' ');
958 cn_utils.appindcr(code, ' AND negated_flag = ''Y'' ');
959 cn_utils.appindcr(code, ' AND collected_flag = ''N'' ');
960 cn_utils.appindcr(code, ' AND event_id = cn_global.inv_event_id ');
961 cn_utils.appindcr(code, ' AND adj_batch_id = x_adj_batch_id ');
962 cn_utils.appindcr(code, ' AND org_id = '||x_org_id||';');
963 cn_utils.appendcr(code);
964 cn_utils.appindcr(code, 'trx_line_update_count := SQL%ROWCOUNT; ');
965 cn_utils.appindcr(code, 'cn_message_pkg.debug(''' || x_procedure_name || ': Updated '' || trx_line_update_count || '' records in cn_trx_lines.''); ');
966 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''' || x_procedure_name || ': Updated '' || trx_line_update_count || '' records in cn_trx_lines.''); ');
967 cn_utils.appendcr(code);
968 cn_utils.indent(code, 1);
969
970 ELSE -- Regular Receivables Collection
971
972 -- Call procedure to generate code for updating trx headers
973 cn_utils.unset_org_id();
974 cn_collection_ar_gen.insert_trx(x_procedure_name, x_module_id, x_event_id, code,X_org_id);
975
976 cn_utils.set_org_id(X_org_id);
977 cn_utils.appindcr(code, 'IF (trx_count <> 0) THEN -- Any New Transactions?');
978 cn_utils.appendcr(code);
979 cn_utils.indent(code, 1);
980 cn_utils.unset_org_id();
981
982 -- Generate code to update headers foreign key columns
983 cn_collection_ar_gen.update_trx(x_procedure_name, x_module_id, x_event_id, code,X_org_id);
984
985 -- Generate code to insert into lines
986 cn_collection_ar_gen.insert_lines(x_procedure_name, x_module_id, x_event_id, code,X_org_id);
987
988 -- Generate code to update invoice_total in CN_TRX table
989 cn_collection_ar_gen.update_invoice_total(x_procedure_name, code,l_org_id);
990
991 -- Generate code to update lines
992 cn_collection_ar_gen.update_lines(x_procedure_name, x_module_id, x_event_id, code,X_org_id);
993
994 END IF; --IF (x_event_id = cn_global.ram_event_id)
995
996 cn_utils.unset_org_id();
997 -- Generate code to insert into sales lines --AE 11-16-95
998 cn_collection_ar_gen.insert_sales_lines(x_procedure_name, x_module_id, x_event_id, code,X_org_id);
999
1000 -- Generate code to update sales_lines
1001 cn_collection_ar_gen.update_sales_lines(x_procedure_name, x_module_id, x_event_id, code,X_org_id);
1002
1003 -- Generate code to insert into cn_comm_lines_api --AE 08-29-95
1004 cn_collection_ar_gen.insert_comm_lines (x_procedure_name, x_module_id, x_event_id, code,X_org_id);
1005 --+
1006 -- Insert any User Code specified for the 'Pre-Api-Update' location
1007 --+
1008 cn_collection_custom_gen.Generate_User_Code(p_table_map_id => x_table_map_id,
1009 p_location_name => 'Pre-Api-Update',
1010 code => code,
1011 X_ORG_ID => X_ORG_ID);
1012 --+
1013 -- Generate code to apply Indirect Mappings to cn_comm_lines_api
1014 --+
1015 cn_collection_custom_gen.update_comm_lines_api (x_table_map_id, code,X_org_id);
1016 --+
1017 -- Insert any User Code specified for the 'Pre-Api-Filter' location
1018 --+
1019 cn_collection_custom_gen.Generate_User_Code(p_table_map_id => x_table_map_id,
1020 p_location_name => 'Pre-Api-Filter',
1021 code => code,
1022 X_ORG_ID => X_ORG_ID);
1023 --+
1024 -- Generate code to apply Filters to cn_comm_lines_api
1025 --+
1026 cn_collection_custom_gen.filter_comm_lines_api (x_table_map_id, code,X_org_id);
1027 --+
1028 -- Insert any User Code specified for the 'Post-Api-Filter' location
1029 --+
1030 cn_collection_custom_gen.Generate_User_Code(p_table_map_id => x_table_map_id,
1031 p_location_name => 'Post-Api-Filter',
1032 code => code,
1033 X_ORG_ID => X_ORG_ID);
1034
1035 IF (x_event_id <> cn_global.ram_event_id) THEN
1036 cn_utils.set_org_id(X_org_id);
1037 cn_utils.unindent(code, 1);
1038 cn_utils.appindcr(code, 'END IF; -- Any New Transactions?');
1039 cn_utils.appendcr(code);
1040 END IF;
1041 END IF;
1042 cn_utils.unset_org_id();
1043
1044 cn_debug.print_msg('collect_stmts<<', 1);
1045 fnd_file.put_line(fnd_file.Log, 'collect_stmts<<');
1046
1047
1048 END collect_stmts;
1049
1050
1051 --
1052 -- Procedure Name
1053 -- pkg_proc_end
1054 -- Purpose
1055 -- This procedure generates procedure end code
1056 -- History
1057 -- 17-NOV-93 Devesh Khatu Created
1058 --
1059 PROCEDURE pkg_proc_end (
1060 x_table_map_id cn_table_maps.table_map_id%TYPE,
1061 x_procedure_name cn_objects.NAME%TYPE,
1062 x_event_id cn_events.event_id%TYPE,
1063 code IN OUT NOCOPY cn_utils.code_type,
1064 x_org_id IN NUMBER) IS
1065 BEGIN
1066
1067 cn_debug.print_msg('pkg_proc_end>>', 1);
1068 fnd_file.put_line(fnd_file.Log, 'pkg_proc_end>>');
1069 cn_utils.set_org_id(x_org_id);
1070 -- Generate code to record success or failure of batch
1071 IF(x_event_id < 0)
1072 THEN
1073 IF (x_event_id = cn_global.ram_event_id) THEN
1074 cn_utils.record_process_success('''Finished collection run for batch '' || x_adj_batch_id', code);
1075 ELSE
1076 cn_utils.record_process_success('''Finished collection run for batch '' || x_batch_id', code);
1077 END IF;
1078
1079 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| x_procedure_name || ': inside loop<<'');');
1080 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| x_procedure_name || ': inside loop<<'');');
1081 cn_utils.appindcr(code, 'cn_message_pkg.end_batch(x_proc_audit_id);');
1082 cn_utils.appindcr(code, 'COMMIT;');
1083 cn_utils.appendcr(code);
1084
1085 -- cn_utils.record_process_exception(x_procedure_name, 'start_transaction', code);
1086 -- proc_exception(x_procedure_name, 'start_transaction', 'inside the loop', code);
1087
1088 cn_utils.unindent(code, 1);
1089 cn_utils.appindcr(code, 'END;');
1090 cn_utils.unindent(code, 1);
1091 cn_utils.appindcr(code, 'END LOOP;');
1092 cn_utils.appendcr(code);
1093 END IF;
1094 cn_utils.unset_org_id();
1095 --+
1096 -- Insert any User Code specified for the 'Post-Collection' location
1097 --+
1098 cn_collection_custom_gen.Generate_User_Code(
1099 p_table_map_id => x_table_map_id,
1100 p_location_name => 'Post-Collection',
1101 code => code,
1102 X_ORG_ID => X_ORG_ID);
1103
1104 cn_utils.set_org_id(x_org_id);
1105 cn_utils.appindcr(code, 'COMMIT;');
1106
1107 IF(x_event_id > 0)
1108 THEN
1109 IF CN_COLLECTION_GEN.isparallelenabled THEN
1110 cn_utils.appindcr(code, 'EXECUTE IMMEDIATE ''ALTER SESSION DISABLE PARALLEL DML'';');
1111 END IF;
1112 cn_utils.record_process_success('''Finished collection run for batch '' || x_proc_audit_id', code);
1113 END IF;
1114
1115 cn_utils.appendcr(code);
1116
1117 cn_utils.appindcr(code, 'x_proc_audit_id := x_col_audit_id;'); --AE 08-01-95
1118
1119 cn_utils.appindcr(code, 'cn_message_pkg.debug('''|| x_procedure_name ||'<<'');');
1120 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, '''|| x_procedure_name ||'<<'');');
1121
1122 cn_utils.appindcr(code, 'cn_message_pkg.end_batch(x_proc_audit_id);');
1123 cn_utils.appendcr(code);
1124
1125 -- cn_utils.proc_end(x_procedure_name, 'Y', code);
1126
1127 --proc_exception(x_procedure_name, NULL, 'outside the loop', code);
1128
1129 -- Generate end of procedure statement
1130 cn_utils.appendcr(code);
1131 cn_utils.unindent(code, 1);
1132 cn_utils.appindcr(code, 'END ' ||x_procedure_name || ';');
1133 cn_utils.appendcr(code);
1134 cn_utils.unset_org_id();
1135
1136 cn_debug.print_msg('pkg_proc_end<<', 1);
1137 fnd_file.put_line(fnd_file.Log, 'pkg_proc_end<<');
1138
1139 END pkg_proc_end;
1140
1141
1142 --+
1143 -- Procedure Name
1144 -- collection_proc
1145 -- Purpose
1146 -- This procedure generates a procedure for collecting source data.
1147 -- History
1148 -- 17-NOV-93 Devesh Khatu Created
1149 -- 08-JUN-94 Devesh Khatu Modified
1150 --+
1151 PROCEDURE collection_proc (
1152 package_name cn_objects.NAME%TYPE,
1153 package_id cn_objects.package_id%TYPE,
1154 x_table_map_id cn_table_maps.table_map_id%TYPE,
1155 module_id cn_modules.module_id%TYPE,
1156 repository_id cn_repositories.repository_id%TYPE,
1157 event_id cn_events.event_id%TYPE,
1158 x_generic BOOLEAN,
1159 spec_code IN OUT NOCOPY cn_utils.code_type,
1160 body_code IN OUT NOCOPY cn_utils.code_type,
1161 x_org_id IN NUMBER) IS
1162
1163 -- Declare and initialize procedure variables
1164
1165 procedure_name cn_objects.NAME%TYPE := 'collect';
1166 short_name cn_objects.NAME%TYPE := SUBSTR(package_name, 12) ;
1167 description cn_objects.description%TYPE
1168 := 'Collection procedure for ' || short_name ;
1169 parameter_list cn_objects.parameter_list%TYPE
1170 := 'errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER';
1171
1172 BEGIN
1173
1174 -- Non-generic collection packages (AR and OC, require start and end period names
1175 -- as input parameters
1176
1177 --dbms_output.put_line(' In collection_proc ');
1178
1179 IF NOT x_generic THEN
1180 parameter_list := parameter_list ||
1181 ', x_start_period_name IN VARCHAR2, x_end_period_name IN VARCHAR2, x_org_id IN NUMBER ';
1182 ELSE
1183 parameter_list := parameter_list ||
1184 ', x_org_id IN NUMBER ';
1185 END IF;
1186 --dbms_output.put_line('--- NOT x_generic parameter_list');
1187 --dbms_output.put_line(parameter_list);
1188
1189 cn_debug.print_msg('collection_proc>>', 1);
1190 fnd_file.put_line(fnd_file.Log, 'collection_proc>>');
1191
1192 -- Generate procedure definition, boilerplate text, local variables etc.
1193 --dbms_output.put_line(' Before pkg_proc_init ');
1194
1195 pkg_proc_init(x_table_map_id, procedure_name, description, parameter_list, package_id,
1196 module_id, repository_id, event_id, x_generic, spec_code, body_code,X_org_id);
1197
1198 --dbms_output.put_line(' after pkg_proc_init ');
1199
1200 -- Call procedure to generate code for updating trx headers
1201
1202 --dbms_output.put_line(' Before collect_stmts ');
1203
1204 collect_stmts(package_id, procedure_name, x_table_map_id, module_id,
1205 repository_id, x_generic, event_id, body_code,X_org_id);
1206
1207 --dbms_output.put_line(' after collect_stmts ');
1208
1209 -- Generate procedure end boilerplate text
1210
1211 pkg_proc_end(x_table_map_id, procedure_name, event_id,body_code,X_org_id);
1212
1213 cn_debug.print_msg('collection_proc<<', 1);
1214 fnd_file.put_line(fnd_file.Log, 'collection_proc<<');
1215
1216 END collection_proc;
1217 --+
1218 -- Procedure Name
1219 -- null_proc
1220 -- Purpose
1221 -- Generate a null procedure when collect flag= NO.
1222 -- History
1223 -- 12-08-95 A. Erickson Created
1224 --+
1225 PROCEDURE null_proc(
1226 package_name cn_objects.NAME%TYPE,
1227 package_id cn_obj_packages_v.package_id%TYPE,
1228 module_id cn_modules.module_id%TYPE,
1229 repository_id cn_repositories.repository_id%TYPE,
1230 event_id cn_events.event_id%TYPE,
1231 spec_code IN OUT NOCOPY cn_utils.code_type,
1232 body_code IN OUT NOCOPY cn_utils.code_type,
1233 x_org_id IN NUMBER) IS
1234
1235 -- Declare and initialize procedure variables
1236
1237 procedure_name cn_objects.NAME%TYPE := 'collect';
1238 short_name cn_objects.NAME%TYPE := SUBSTR(package_name, 12) ;
1239 description cn_objects.description%TYPE
1240 := 'NULL collection procedure for ' || short_name;
1241 parameter_list cn_objects.parameter_list%TYPE
1242 := 'errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER, x_start_period_name IN VARCHAR2, x_end_period_name IN VARCHAR2, x_org_id IN NUMBER ';
1243
1244 BEGIN
1245
1246 l_org_id := x_org_id;
1247
1248 cn_debug.print_msg('null_proc>>', 1);
1249 fnd_file.put_line(fnd_file.Log, 'null_proc>>');
1250
1251 -- Generate procedure definition, boilerplate text, local variables etc.
1252
1253 cn_utils.set_org_id(X_org_id);
1254 cn_utils.proc_init(procedure_name, description, parameter_list,
1255 'P', NULL, package_id, repository_id, spec_code, body_code);
1256
1257 -- generate local variables.
1258 cn_utils.indent(body_code, 1);
1259 cn_utils.appindcr(body_code, 'x_proc_audit_id NUMBER := NULL ;');
1260 cn_utils.appindcr(body_code, 'x_col_audit_id NUMBER;');
1261 cn_utils.appindcr(body_code, 'debug_pipe VARCHAR2(30);'); --J.C 02-FEB-97
1262 cn_utils.appindcr(body_code, 'debug_level NUMBER := 1 ;'); --J.C 02-FEB-97
1263 cn_utils.appindcr(body_code, 'dummy_num NUMBER ;');
1264 cn_utils.appendcr(body_code);
1265 cn_utils.unindent(body_code, 1);
1266
1267 cn_utils.proc_begin(procedure_name, 'Y', body_code);
1268 call_start_debug(procedure_name, event_id, body_code,X_org_id);
1269
1270 cn_utils.set_org_id(X_org_id);
1271 cn_utils.appindcr(body_code, 'NULL;');
1272 cn_utils.appendcr(body_code);
1273
1274 cn_utils.appindcr(body_code, 'cn_message_pkg.debug('''|| procedure_name || ': Nothing is being collected.'');');
1275 cn_utils.appindcr(body_code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name || ': Nothing is being collected.'');');
1276 cn_utils.appindcr(body_code, 'cn_message_pkg.debug('''|| procedure_name ||'<<'');');
1277 cn_utils.appindcr(body_code, 'fnd_file.put_line(fnd_file.Log, '''|| procedure_name ||'<<'');');
1278 cn_utils.appindcr(body_code, 'cn_message_pkg.end_batch(x_proc_audit_id);');
1279 cn_utils.appendcr(body_code);
1280
1281
1282 -- Generate procedure end.
1283 -- Generate end of procedure statement
1284 cn_utils.appendcr(body_code);
1285 cn_utils.unindent(body_code, 1);
1286 cn_utils.appindcr(body_code, 'END ' || procedure_name || ';');
1287 cn_utils.appendcr(body_code);
1288 cn_utils.unset_org_id();
1289
1290 cn_debug.print_msg('null_proc<<', 1);
1291 fnd_file.put_line(fnd_file.Log, 'null_proc<<');
1292
1293 END null_proc;
1294
1295
1296 --+
1297 -- Procedure Name
1298 -- collection_pkg_code
1299 -- Purpose
1300 -- Generate collection package code
1301 -- History
1302 -- 03-17-00 Dave Maskell Created
1303 --+
1304 PROCEDURE collection_pkg_code (
1305 debug_pipe VARCHAR2,
1306 debug_level NUMBER := 1,
1307 x_package_name cn_objects.NAME%TYPE,
1308 x_org_append VARCHAR2,
1309 x_collect_flag cn_modules.collect_flag%TYPE,
1310 x_module_id cn_modules.module_id%TYPE,
1311 x_generic BOOLEAN,
1312 x_table_map_id cn_table_maps.table_map_id%TYPE,
1313 x_org_id IN NUMBER) IS
1314
1315 l_package_type cn_objects.package_type%TYPE;
1316 l_package_spec_id cn_obj_packages_v.package_id%TYPE;
1317 l_package_body_id cn_obj_packages_v.package_id%TYPE;
1318 l_package_spec_desc cn_obj_packages_v.description%TYPE;
1319 l_package_body_desc cn_obj_packages_v.description%TYPE;
1320 l_repository_id cn_repositories.repository_id%TYPE
1321 := cn_utils.get_repository(X_MODULE_ID,x_org_id);
1322 l_spec_code cn_utils.code_type;
1323 l_body_code cn_utils.code_type;
1324 l_event_id cn_events.event_id%TYPE
1325 := cn_utils.get_event(x_module_id,x_org_id);
1326
1327 BEGIN
1328
1329
1330 --dbms_output.put_line(' In collection_pkg_code');
1331 --dbms_output.put_line('--- l_repository_id '||l_repository_id);
1332 --dbms_output.put_line('--- l_event_id '||l_event_id);
1333
1334
1335 IF (debug_pipe IS NOT NULL) THEN
1336 cn_debug.init_pipe(debug_pipe, debug_level);
1337 END IF;
1338 cn_debug.print_msg('collection_pkg_code>>', 1);
1339 fnd_file.put_line(fnd_file.Log, 'collection_pkg_code>>');
1340
1341 --+
1342 -- Define package in Commissions dictionary, initialize code etc.
1343 -- Note: pkg_init calls delete_module in cn_utils to delete current mod.
1344 -- Note: l_package_type can be left NULL - it's not actually used.
1345 --+
1346
1347 cn_utils.set_org_id(x_org_id);
1348
1349 cn_utils.pkg_init(x_module_id, x_package_name, x_org_append, l_package_type,
1350 l_package_spec_id, l_package_body_id, l_package_spec_desc,
1351 l_package_body_desc, l_spec_code, l_body_code);
1352
1353 IF x_collect_flag = 'YES' THEN
1354 -- Generate the collection procedure to collect invoices data
1355 collection_proc(x_package_name, l_package_spec_id, x_table_map_id, x_module_id,
1356 l_repository_id, l_event_id, x_generic, l_spec_code, l_body_code,X_org_id);
1357 ELSE
1358 -- Generate the a null procedure.
1359 null_proc(x_package_name, l_package_spec_id, x_module_id,
1360 l_repository_id, l_event_id, l_spec_code, l_body_code,X_org_id);
1361 END IF;
1362
1363 -- Generate end package statements, dump code into cn_objects
1364
1365 cn_utils.set_org_id(X_org_id);
1366 cn_utils.pkg_end(x_package_name, l_package_spec_id, l_package_body_id,
1367 l_spec_code, l_body_code);
1368
1369 cn_utils.unset_org_id();
1370 cn_debug.print_msg('collection_pkg_code<<', 1);
1371 fnd_file.put_line(fnd_file.Log, 'collection_pkg_code<<');
1372
1373 END collection_pkg_code;
1374
1375 --------------------------------------------------------+
1376 -- Public procedures
1377 --------------------------------------------------------+
1378
1379 FUNCTION get_org_append
1380 RETURN VARCHAR2 IS
1381 l_org_append VARCHAR2(10);
1382 BEGIN
1383 --+
1384 -- Get org_id for current user and use it to derive Org_Id suffix (e.g. _204)
1385 --+
1386
1387 --MO_GLOBAL.INIT('CN');
1388 IF l_org_id IS NULL OR l_org_id = '' OR l_org_id = -1 then
1389 l_org_id := mo_global.get_current_org_id;
1390 END IF;
1391
1392 IF l_org_id = -99 THEN
1393 l_org_append := '_M99';
1394 ELSE
1395 l_org_append := '_' || l_org_id;
1396 END IF;
1397 RETURN l_org_append;
1398
1399 END get_org_append;
1400
1401 PROCEDURE unset_org_id --Added by Ashley as part of MOAC Changes
1402 IS
1403 BEGIN
1404 l_org_id := null;
1405 END;
1406
1407 PROCEDURE set_org_id(p_org_id IN NUMBER) --Added by Ashley as part of MOAC Changes
1408 IS
1409 BEGIN
1410 l_org_id := p_org_id;
1411 END;
1412
1413 --+
1414 -- Procedure Name
1415 -- collection_pkg
1416 -- Purpose
1417 -- Generate any collection package
1418 -- History
1419 -- 03-17-00 Dave Maskell Created
1420 --+
1421 PROCEDURE collection_pkg (
1422 debug_pipe VARCHAR2,
1423 debug_level NUMBER := 1,
1424 x_table_map_id cn_table_maps.table_map_id%TYPE,
1425 x_org_id IN NUMBER) IS
1426
1427 l_generic BOOLEAN := FALSE;
1428 l_module_id cn_modules.module_id%TYPE;
1429 l_package_name cn_objects.NAME%TYPE;
1430 l_mapping_type cn_objects.package_type%TYPE;
1431 --l_org_append VARCHAR2(100) := get_org_append;
1432 l_org_append VARCHAR2(100);
1433 BEGIN
1434
1435 l_org_id := x_org_id;
1436 l_org_append := get_org_append;
1437 --dbms_output.put_line('x_org_id:1'||x_org_id);
1438 --dbms_output.put_line('l_org_id:1'||l_org_id);
1439
1440
1441 IF (debug_pipe IS NOT NULL) THEN
1442 cn_debug.init_pipe(debug_pipe, debug_level);
1443 END IF;
1444 cn_debug.print_msg('collection_pkg>>', 1);
1445 fnd_file.put_line(fnd_file.Log, 'collection_pkg>>');
1446 --+
1447 -- Get info about this data source from cn_table_maps
1448 --+
1449 SELECT mapping_type, module_id
1450 INTO l_mapping_type, l_module_id
1451 FROM cn_table_maps
1452 WHERE table_map_id = x_table_map_id
1453 AND org_id = X_ORG_ID;
1454 --+
1455 -- Process according to the mapping data source
1456 --+
1457 IF l_mapping_type = 'AR' THEN
1458 --+
1459 -- If source is Receivables then we must generate up to
1460 -- four collection packages, depending on whether they
1461 -- are currently flagged for collection
1462 --+
1463 FOR rec IN
1464 (SELECT module_id, module_type, collect_flag FROM cn_modules
1465 WHERE module_type IN ('INV','CBK','PMT','WO','RAM') AND org_id = x_org_id)
1466 LOOP
1467 IF rec.module_type = 'INV' THEN
1468 l_package_name := 'cn_collect_invoices';
1469 ELSIF rec.module_type = 'PMT' THEN
1470 l_package_name := 'cn_collect_payments';
1471 ELSIF rec.module_type = 'WO' THEN
1472 l_package_name := 'cn_collect_writeoffs';
1473 ELSIF rec.module_type = 'CBK' THEN
1474 l_package_name := 'cn_collect_clawbacks';
1475 ELSIF rec.module_type = 'RAM' THEN
1476 l_package_name := 'cn_collect_ram';
1477 END IF;
1478 --+
1479 -- Generate package internals
1480 --+
1481
1482 collection_pkg_code (
1483 debug_pipe => debug_pipe,
1484 debug_level => debug_level,
1485 x_package_name => l_package_name,
1486 x_org_append => l_org_append,
1487 x_collect_flag => rec.collect_flag,
1488 x_module_id => rec.module_id,
1489 x_generic => l_generic,
1490 x_table_map_id => x_table_map_id,
1491 x_org_id => X_org_id);
1492 END LOOP;
1493 ELSE
1494 --+
1495 -- For Order Capture (OC) and custom data sources there is only one package
1496 -- and it is always generated (collect_flag = 'YES')
1497 -- +
1498 IF l_mapping_type <> 'OC' THEN
1499 l_generic := TRUE;
1500 --+
1501 -- For custom packages, the package name (as stored in CN_OBJECTS)
1502 -- is of the format cn_collect_<mapping_type>_<org_id>
1503 --+
1504 l_org_append := NULL;
1505
1506 END IF;
1507
1508 SELECT OBJ.name OBJECT_NAME
1509 INTO l_package_name
1510 FROM cn_table_map_objects tmov, cn_objects obj
1511 WHERE tmov.table_map_id = x_table_map_id
1512 AND tmov.tm_object_type = 'PKS'
1513 and tmov.org_id = obj.org_id
1514 and tmov.object_id = obj.object_id
1515 AND tmov.org_id = X_org_id;
1516
1517 --dbms_output.put_line(' l_org_append before collection_pkg_code in collection_pkg');
1518 --dbms_output.put_line(' l_org_append '||l_org_append);
1519 --dbms_output.put_line(' l_package_name '||l_package_name);
1520
1521 --dbms_output.put_line(' After Select '||l_package_name);
1522
1523 --dbms_output.put_line(' Before collection_pkg_code ');
1524
1525 collection_pkg_code (
1526 debug_pipe => debug_pipe,
1527 debug_level => debug_level,
1528 x_package_name => l_package_name,
1529 x_org_append => l_org_append,
1530 x_collect_flag => 'YES',
1531 x_module_id => l_module_id,
1532 x_generic => l_generic,
1533 x_table_map_id => x_table_map_id,
1534 x_org_id => X_org_id);
1535
1536 --dbms_output.put_line(' Out OF collection_pkg_code ');
1537 END IF;
1538 --COMMIT;
1539 cn_debug.print_msg('collection_pkg<<', 1);
1540 fnd_file.put_line(fnd_file.Log, 'collection_pkg<<');
1541 END collection_pkg;
1542
1543 PROCEDURE Collection_Install(
1544 x_errbuf OUT NOCOPY VARCHAR2,
1545 x_retcode OUT NOCOPY NUMBER,
1546 p_table_map_id IN cn_table_maps.table_map_id%TYPE,
1547 p_test IN VARCHAR2 ,
1548 x_org_id IN NUMBER)
1549 IS
1550 l_org_append VARCHAR2(10);
1551 l_test_append VARCHAR2(5);
1552 l_mapping_type cn_table_maps.mapping_type%TYPE;
1553 l_module_id cn_table_maps.module_id%TYPE;
1554 l_comp_error VARCHAR2(10);
1555 l_errors BOOLEAN := FALSE;
1556 l_max_len NUMBER := 1800;
1557 l_remainder NUMBER;
1558 -- Variable for Notes
1559 l_note_msg VARCHAR2(4000);
1560 x_note_id NUMBER;
1561 l_context_code VARCHAR2(4000);
1562 l_context_id NUMBER;
1563 l_pkg_name VARCHAR2(4000);
1564 x_return_status VARCHAR2(4000);
1565 x_msg_count NUMBER;
1566 x_msg_data VARCHAR2(4000);
1567
1568
1569
1570 BEGIN
1571 x_retcode := 0;
1572 x_errbuf := ' ';
1573
1574 l_org_id := x_org_id;
1575
1576 --dbms_output.put_line(' In Collection Install ');
1577 --dbms_output.put_line('p_table_map_id '||p_table_map_id);
1578 --dbms_output.put_line('l_org_id '||l_org_id);
1579
1580 --+
1581 -- We need to get the exact package name. For custom
1582 -- data sources, this is the same as the name stored in CN_OBJECTS. For AR and OC
1583 -- the name stored in CN_OBJECTS does not have the Org_id on it, so we must
1584 -- append this. (Also get module_id for later use)
1585 --+
1586
1587 SELECT mapping_type, module_id
1588 INTO l_mapping_type, l_module_id
1589 FROM cn_table_maps
1590 WHERE table_map_id = p_table_map_id
1591 AND org_id = X_org_id;
1592
1593 --dbms_output.put_line(' In Collection Install l_mapping_type : '||l_mapping_type);
1594 --dbms_output.put_line(' In Collection Install l_module_id'||l_module_id);
1595
1596 IF l_mapping_type NOT IN ('AR','OC')
1597 THEN
1598 l_org_append := NULL;
1599 ELSE
1600 l_org_append := get_org_append;
1601 END IF;
1602
1603 --dbms_output.put_line(' In Collection Install l_org_append '||l_org_append);
1604
1605 --+
1606 -- Loop for each package belonging to the source, getting
1607 -- the object_ids for the Spec and Body, plus the package
1608 -- name (from CN_OBJECTS). For AR there will
1609 -- be 4 packages, for any other source there will only be one.
1610 --+
1611
1612 FOR rec IN
1613 (SELECT
1614 UPPER(OBJ.name) NAME ,
1615 MAX(DECODE(tmov.tm_object_type,'PKS',tmov.object_id,NULL)) spec_id,
1616 MAX(DECODE(tmov.tm_object_type,'PKB',tmov.object_id,NULL)) body_id
1617 FROM cn_table_map_objects tmov, cn_objects obj
1618 WHERE tmov.tm_object_type IN ('PKS','PKB')
1619 and obj.object_id = tmov.object_id
1620 and tmov.org_id = obj.org_id
1621 AND tmov.table_map_id = p_table_map_id
1622 AND tmov.org_id = X_org_id
1623 GROUP BY OBJ.name)
1624 LOOP
1625 --+
1626 -- Create the Spec and Body.
1627 --+
1628 install_package_object(
1629 p_object_id => rec.spec_id,
1630 p_object_name => rec.name||l_org_append,
1631 p_test => p_test,
1632 x_comp_error => l_comp_error,
1633 x_org_id => X_org_id);
1634
1635 IF l_comp_error = 'TRUE' THEN
1636 l_errors := TRUE;
1637 END IF;
1638 install_package_object(
1639 p_object_id => rec.body_id,
1640 p_object_name => rec.name||l_org_append,
1641 p_test => p_test,
1642 x_comp_error => l_comp_error,
1643 x_org_id => X_org_id);
1644
1645
1646
1647 IF l_comp_error = 'TRUE' THEN
1648 l_errors := TRUE;
1649 END IF;
1650 END LOOP;
1651 ------------------------------------------------------------------------------+
1652 -- The rest of the procedure is concerned with providing log messages if the
1653 -- creation of any of the packages failed
1654 ------------------------------------------------------------------------------+
1655 IF l_errors THEN -- some specs/bodies were in error
1656 x_retcode := 1; -- set failure return code
1657 --+
1658 -- If p_test = 'Y', we have generated a test package, which will
1659 -- have the _T suffix.
1660 --+
1661 IF p_test = 'Y' THEN
1662 l_test_append := '_T';
1663 ELSE
1664 l_test_append := NULL;
1665 END IF;
1666 --+
1667 -- Search the User_Errors table, for errors belonging to any of the Collection
1668 -- packages for this Org.
1669 --+
1670 FOR obj_rec IN
1671 (SELECT
1672 DISTINCT UPPER(OBJ.name) NAME
1673 FROM cn_table_map_objects tmov, cn_objects obj
1674 WHERE tm_object_type IN ('PKS','PKB')
1675 AND tmov.table_map_id = p_table_map_id
1676 and tmov.object_id = obj.object_id
1677 and tmov.org_id = obj.org_id
1678 AND tmov.org_id = X_org_id
1679 GROUP BY OBJ.name)
1680 LOOP <<outer>>
1681 FOR err_rec IN
1682 (SELECT
1683 '*** '||TYPE||' '||LOWER(NAME)||' LINE: '||line||'/'||position||
1684 fnd_global.local_chr(10)||text||fnd_global.local_chr(10) outstr
1685 FROM user_errors WHERE NAME = obj_rec.name||l_org_append||l_test_append)
1686 LOOP <<inner>>
1687 -- If there is enough space, append this error to the end of the
1688 -- Errbuf, otherwise aappend as mauch as possible and then quit
1689 -- the loop.
1690 IF LENGTHB(x_errbuf) + LENGTHB(err_rec.outstr) <= l_max_len THEN
1691 x_errbuf := x_errbuf || err_rec.outstr;
1692 ELSE
1693 l_remainder := l_max_len - LENGTHB(x_errbuf);
1694 x_errbuf := x_errbuf || SUBSTRB(err_rec.outstr,1,l_remainder);
1695 EXIT outer;
1696 END IF;
1697 END LOOP;
1698 END LOOP;
1699 END IF;
1700
1701 -- Update the status of the module to 'GENERATED' if we were installing
1702 -- the actual package rather than the test version.
1703 IF p_test = 'N' and x_retcode<>1 THEN
1704 cn_modules_pkg.update_row(x_module_id => l_module_id,
1705 x_module_status => 'GENERATED',
1706 x_org_id => X_org_id);
1707 FOR pkg_names IN
1708 (SELECT
1709 DISTINCT UPPER(OBJ.name) NAME
1710 FROM cn_table_map_objects tmov, cn_objects obj
1711 WHERE tm_object_type IN ('PKS','PKB')
1712 AND tmov.table_map_id = p_table_map_id
1713 and tmov.object_id = obj.object_id
1714 and tmov.org_id = obj.org_id
1715 AND tmov.org_id = X_org_id
1716 GROUP BY OBJ.name)
1717 LOOP
1718
1719 IF(p_table_map_id < 0)
1720 THEN
1721 l_pkg_name := pkg_names.NAME||l_org_append;
1722 l_context_code := 'CN_REPOSITORIES';
1723 l_context_id := x_org_id;
1724 ELSE
1725 l_pkg_name := pkg_names.NAME;
1726 l_context_code := 'CN_MODULES';
1727 l_context_id := l_module_id;
1728 END IF;
1729 -- Adding notes for Transaction Source Type
1730 FND_MESSAGE.SET_NAME('CN', 'CN_COL_PKG_GEN_SUC');
1731 FND_MESSAGE.SET_TOKEN('PACKAGENAME', l_pkg_name);
1732 l_note_msg := FND_MESSAGE.GET;
1733
1734 jtf_notes_pub.create_note
1735 ( p_api_version => 1.0,
1736 x_return_status => x_return_status,
1737 x_msg_count => x_msg_count,
1738 x_msg_data => x_msg_data,
1739 p_source_object_id => l_context_id,
1740 p_source_object_code => l_context_code,
1741 p_notes => l_note_msg,
1742 p_notes_detail => l_note_msg,
1743 p_note_type => 'CN_SYSGEN',
1744 x_jtf_note_id => x_note_id
1745 );
1746 END LOOP;
1747
1748 END IF;
1749
1750 IF p_test = 'N' and x_retcode=1 THEN
1751 FOR pkg_names IN
1752 (SELECT
1753 DISTINCT UPPER(OBJ.name) NAME
1754 FROM cn_table_map_objects tmov, cn_objects obj
1755 WHERE tm_object_type IN ('PKS','PKB')
1756 AND tmov.table_map_id = p_table_map_id
1757 and tmov.object_id = obj.object_id
1758 and tmov.org_id = obj.org_id
1759 AND tmov.org_id = X_org_id
1760 GROUP BY OBJ.name)
1761 LOOP
1762
1763 IF(p_table_map_id < 0)
1764 THEN
1765 l_pkg_name := pkg_names.NAME||l_org_append;
1766 l_context_code := 'CN_REPOSITORIES';
1767 l_context_id := x_org_id;
1768 ELSE
1769 l_pkg_name := pkg_names.NAME;
1770 l_context_code := 'CN_MODULES';
1771 l_context_id := l_module_id;
1772 END IF;
1773 -- Adding notes for Transaction Source Type
1774 FND_MESSAGE.SET_NAME('CN', 'CN_COL_PKG_GEN_FAIL');
1775 FND_MESSAGE.SET_TOKEN('PACKAGENAME', l_pkg_name);
1776 l_note_msg := FND_MESSAGE.GET;
1777
1778 jtf_notes_pub.create_note
1779 ( p_api_version => 1.0,
1780 x_return_status => x_return_status,
1781 x_msg_count => x_msg_count,
1782 x_msg_data => x_msg_data,
1783 p_source_object_id => l_context_id,
1784 p_source_object_code => l_context_code,
1785 p_notes => l_note_msg,
1786 p_notes_detail => l_note_msg,
1787 p_note_type => 'CN_SYSGEN',
1788 x_jtf_note_id => x_note_id
1789 );
1790 END LOOP;
1791
1792 END IF;
1793 --dbms_output.put_line('Leaving In Collection Install l_comp_error ');
1794 --COMMIT;
1795 END Collection_Install;
1796
1797 -- Procedure to be called by CN_COLLECT_GEN concurrent program
1798 PROCEDURE generate_collect_conc(
1799 errbuf OUT NOCOPY VARCHAR2,
1800 retcode OUT NOCOPY NUMBER,
1801 p_org_id NUMBER)
1802 IS
1803 l_return_status VARCHAR2(30);
1804 l_msg_count NUMBER;
1805 l_msg_data VARCHAR2(2000);
1806 l_debug_pipe VARCHAR2(2000);
1807 l_debug_level NUMBER;
1808
1809 CURSOR maps IS
1810 SELECT tm.table_map_id,
1811 tm.org_id,
1812 tm.module_id,
1813 mo.module_status
1814 FROM cn_table_maps_all tm,
1815 cn_modules_all_b mo
1816 WHERE tm.module_id = mo.module_id
1817 AND tm.org_id = mo.org_id
1818 AND mo.module_status = 'GENERATED'
1819 AND mo.org_id = nvl(p_org_id, mo.org_id)
1820 AND mo.org_id <> -3113
1821 ORDER BY tm.table_map_id, tm.org_id, tm.module_id;
1822
1823 CURSOR compile_pkg_cur IS
1824 SELECT object_name || ' ' ||
1825 decode(object_type, 'PACKAGE BODY','compile body','PACKAGE','compile') stmt
1826 FROM user_objects
1827 WHERE object_name LIKE 'CN_COLLECT%'
1828 AND substr(object_name, (INSTR(object_name,'_',1,3)+1), 1)IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
1829 AND object_type LIKE 'PACKAGE%'
1830 AND status = 'INVALID';
1831
1832
1833 CURSOR drop_pkg_cur IS
1834 SELECT object_name stmt
1835 FROM user_objects
1836 WHERE object_name LIKE 'CN_COLLECT%'
1837 AND substr(object_name, (INSTR(object_name,'_',1,3)+1), 1)IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
1838 AND object_type LIKE 'PACKAGE%'
1839 AND status = 'INVALID';
1840
1841
1842
1843 BEGIN
1844 -- ==============================
1845 -- Regenerate Collection packages
1846 -- ==============================
1847
1848 FOR tablemaprec IN maps LOOP
1849
1850 -- dbms_application_info.set_client_info(map.org_id);
1851
1852 -- Generate Collection code. Code is stored in CN_SOURCE.
1853 collection_pkg(
1854 l_debug_pipe,
1855 l_debug_level,
1856 tablemaprec.table_map_id,
1857 tablemaprec.org_id);
1858
1859 -- Install the collection test package.
1860 collection_install(
1861 x_errbuf => errbuf,
1862 x_retcode => retcode,
1863 p_table_map_id => tablemaprec.table_map_id,
1864 p_test => 'Y',
1865 x_org_id => tablemaprec.org_id);
1866
1867 -- Install the collection package.
1868 collection_install(
1869 x_errbuf => errbuf,
1870 x_retcode => retcode,
1871 p_table_map_id => tablemaprec.table_map_id,
1872 p_test => 'N',
1873 x_org_id => tablemaprec.org_id);
1874
1875 END LOOP;
1876
1877 -- Try one round of compiling the invalid formula packages
1878
1879 FOR i IN compile_pkg_cur
1880
1881 LOOP
1882
1883 BEGIN
1884
1885 execute IMMEDIATE 'alter package '|| i.stmt;
1886
1887 EXCEPTION
1888
1889 WHEN others THEN
1890
1891 NULL;
1892
1893 END;
1894
1895 END LOOP;
1896
1897
1898
1899 -- Drop the collection package if still invalid
1900
1901 FOR i IN drop_pkg_cur
1902
1903 LOOP
1904
1905 BEGIN
1906
1907 execute immediate 'drop package '|| i.stmt;
1908
1909 EXCEPTION
1910
1911 WHEN others
1912
1913 THEN
1914
1915 NULL;
1916
1917 END;
1918
1919 END LOOP;
1920
1921 retcode := 0;
1922
1923 errbuf := 'Batch runner completes successfully.';
1924
1925 COMMIT;
1926
1927 EXCEPTION
1928
1929 WHEN OTHERS THEN
1930
1931 retcode := 2;
1932
1933 errbuf := sqlerrm;
1934
1935 END generate_collect_conc;
1936
1937 END cn_collection_gen;
1938