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