[Home] [Help]
PACKAGE BODY: APPS.CN_COLLECTION_CUSTOM_GEN
Source
1 PACKAGE BODY CN_COLLECTION_CUSTOM_GEN AS
2 -- $Header: cncusgenb.pls 120.16 2007/09/27 10:14:13 apink ship $
3
4
5 ---------------- Private Procedures -------------------+
6
7 --+
8 -- Changed to fix Bug 6203234 to include PDML
9 --
10 --+
11
12
13 PROCEDURE create_parallel_hint(
14 x_source_string IN VARCHAR2,
15 x_parallel_hint IN OUT NOCOPY VARCHAR2
16 ) IS
17 l_notification_tables varchar2(1000);
18 l_notification_aliases varchar2(1000);
19 l_comma_at number;
20 l_last_comma_at number;
21 l_space_at number;
22
23 BEGIN
24 l_notification_tables := x_source_string;
25 loop
26
27 l_last_comma_at := instr(l_notification_tables, ',',-1,1);
28
29 if(l_last_comma_at=0)
30 then
31 l_space_at := instr(l_notification_tables, ' ',1,1);
32 l_notification_aliases := substr(l_notification_tables,l_space_at+1, length(l_notification_tables)-l_space_at);
33 x_parallel_hint := x_parallel_hint || ' PARALLEL('||l_notification_aliases||')';
34 exit;
35 end if;
36 l_notification_aliases := substr(l_notification_tables,l_last_comma_at+1, length(l_notification_tables)-l_last_comma_at);
37 l_space_at := instr(l_notification_aliases, ' ',1,1);
38 l_notification_aliases := substr(l_notification_aliases,l_space_at+1, length(l_notification_aliases)-l_space_at);
39
40 x_parallel_hint := x_parallel_hint || ' ' || 'PARALLEL('||l_notification_aliases||')';
41
42 l_notification_tables := substr(l_notification_tables,1, l_last_comma_at-1);
43
44 end loop;
45 x_parallel_hint := x_parallel_hint || ' *'||'/';
46 END create_parallel_hint;
47 ---------------- Public Procedures -------------------+
48
49
50
51 -- Procedure Name
52 -- insert_cn_not_trx
53 -- Purpose
54 -- This procedure generates the Notification code
55 -- History
56 -- 03-17-00 Dave Maskell Created for Release 11i2.
57
58 PROCEDURE insert_cn_not_trx (
59 x_table_map_id cn_table_maps.table_map_id%TYPE,
60 x_event_id cn_events.event_id%TYPE,
61 code IN OUT NOCOPY cn_utils.code_type,
62 x_org_id IN NUMBER)
63 IS
64
65 l_return_status VARCHAR2(4000);
66 l_msg_count NUMBER;
67 l_msg_data VARCHAR2(4000);
68 l_notify_from VARCHAR2(4000);
69 l_notify_where VARCHAR2(4000);
70 l_collect_from VARCHAR2(4000);
71 l_collect_where VARCHAR2(4000);
72 l_table_map_rec cn_table_maps_v%ROWTYPE;
73 l_header_key VARCHAR2(100);
74 l_line_key VARCHAR2(100);
75 l_org_id NUMBER;
76 l_parallel_hint VARCHAR2(4000) := '/'||'*'||'+';
77
78 CURSOR c_param IS
79 SELECT tmov.object_id, LOWER(OBJ.name) object_name
80 FROM cn_table_map_objects tmov, cn_objects obj
81 WHERE tmov.table_map_id = x_table_map_id
82 AND tmov.tm_object_type = 'PARAM'
83 and obj.object_id = tmov.object_id
84 and tmov.org_id = obj.org_id
85 AND tmov.org_id = x_org_id;
86 -- Added For R12 MOAC Change
87 BEGIN
88
89 l_org_id := x_org_id;
90
91
92 -- Added For R12 MOAC Change
93
94 --+
95 -- Get the Table Map details for this data source
96 --+
97
98 SELECT *
99 INTO l_table_map_rec
100 FROM cn_table_maps_v
101 WHERE table_map_id = x_table_map_id
102 AND org_id = l_org_id; -- Added For R12 MOAC Change
103 --+
104 -- Get the name of the Line Table primary key
105 -- using the form 'alias.column_name'
106 --+
107 l_line_key := LOWER(
108 NVL(l_table_map_rec.source_table_alias,l_table_map_rec.source_table_name)||
109 '.'||l_table_map_rec.linepk_name);
110 --+
111 -- Get the name of the Header Table primary key. There may be no value for
112 -- this, in which case we need to use the string 'NULL' for the purposes of
113 -- of our generated INSERT statement.
114 --+
115 IF l_table_map_rec.hdrpk_name IS NULL THEN
116 l_header_key := 'NULL';
117 ELSE
118 l_header_key := LOWER(
119 NVL(l_table_map_rec.header_table_alias,l_table_map_rec.header_table_name)||
120 '.'||l_table_map_rec.hdrpk_name);
121 END IF;
122
123 cn_utils.set_org_id(p_org_id => l_org_id);
124 cn_utils.appendcr(code);
125 cn_utils.appendcr(code, '--******** NOTIFICATION PROCESSING *********-- ');
126 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserting new transactions into CN_NOT_TRX.'');');
127 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''Inserting new transactions into CN_NOT_TRX.'');');
128 cn_utils.appendcr(code);
129 --+
130 -- Most local variables for the collect procedure are part of a static set, which is generated
131 -- for all pacakges (inclding OC and AR) at the start of the procedure by the cn_collection_gen
132 -- package. However, for the notification query of Custom data sources, we need local variables
133 -- which have the names of the any parameters which have been specified by the user on the Queries
134 -- tab of the Collections form. To allow use to do this, we need to enclose the notification query
135 -- in its own block.
136 --+
137 cn_utils.appindcr(code, 'DECLARE -- Notification Insert Block');
138 --+
139 -- Generate Declarations of parameter variables
140 --+
141 cn_utils.indent(code, 1);
142 cn_utils.appindcr(code, '--*** Declaring user-defined Notification Parameters');
143 FOR rec IN c_param
144 LOOP
145 cn_utils.appindcr(code, rec.object_name||' cn_objects.object_value%TYPE;');
146 END LOOP;
147 cn_utils.unindent(code, 1);
148 cn_utils.appindcr(code, 'BEGIN');
149 --+
150 -- Generate code to initialise parameter variables
151 --+
152 cn_utils.indent(code, 1);
153 cn_utils.appindcr(code, '--*** Initializing user-defined Notification Parameters');
154 FOR rec IN c_param
155 LOOP
156 cn_utils.appindcr(code, 'SELECT object_value');
157 cn_utils.appindcr(code, 'INTO '||rec.object_name);
158 cn_utils.appindcr(code, 'FROM cn_objects');
159 cn_utils.appindcr(code, 'WHERE object_id = '||rec.object_id||' --*** Object_Id for '||rec.object_name);
160 cn_utils.appindcr(code, 'AND org_id = '||l_org_id||';');
161 cn_utils.appendcr(code);
162 END LOOP;
163
164 -- Added For R12 MOAC Change
165 cn_utils.appendcr(code);
166 --+
167 -- Insert any User Code specified for the 'Pre-Notification' location
168 --+
169 Generate_User_Code(
170 p_table_map_id => x_table_map_id,
171 p_location_name => 'Pre-Notification',
172 code => code,
173 x_org_id => l_org_id);
174
175 --+
176 -- Generate the FROM and WHERE clauses. Changed position fo fix Bug 6203234
177 --+
178 cn_table_maps_pvt.get_sql_clauses(
179 p_api_version => 1.0,
180 x_return_status => l_return_status,
181 x_msg_count => l_msg_count,
182 x_msg_data => l_msg_data,
183 p_table_map_id => x_table_map_id,
184 x_notify_from => l_notify_from,
185 x_notify_where => l_notify_where,
186 x_collect_from => l_collect_from,
187 x_collect_where => l_collect_where,
188 p_org_id => l_org_id);
189
190
191 --+
192 -- Changed to fix Bug 6203234 to include PDML in Notification Query
193 --
194 --+
195 IF CN_COLLECTION_GEN.isParallelEnabled THEN
196 create_parallel_hint(x_source_string => l_notify_from,
197 x_parallel_hint => l_parallel_hint
198 );
199 END IF;
200
201 --+
202 -- Generate the INSERT INTO API .. SELECT portion of
203 -- the statement
204 --+
205 cn_utils.set_org_id(l_org_id);
206 cn_utils.appindcr(code, '--');
207 cn_utils.appindcr(code, '-- Insert new lines into cn_not_trx');
208 cn_utils.appindcr(code, '--');
209 cn_utils.appindcr(code, '--CN_DB_PARALLEL_ENABLE :'||fnd_profile.value('CN_DB_PARALLEL_ENABLE'));
210 IF CN_COLLECTION_GEN.isParallelEnabled THEN
211 cn_utils.appindcr(code, 'INSERT /'||'*+ PARALLEL(cn_not_trx) */ INTO cn_not_trx (');
212 ELSE
213 cn_utils.appindcr(code, 'INSERT INTO cn_not_trx (');
214 END IF;
215 cn_utils.indent(code, 1);
216 cn_utils.appindcr(code, 'not_trx_id,');
217 cn_utils.appindcr(code, 'batch_id,');
218 cn_utils.appindcr(code, 'notified_date,');
219 cn_utils.appindcr(code, 'notification_run_id,');
220 cn_utils.appindcr(code, 'collected_flag,');
221 cn_utils.appindcr(code, 'event_id,');
222 cn_utils.appindcr(code, 'source_trx_id,');
223 cn_utils.appindcr(code, 'source_trx_line_id,');
224 cn_utils.appindcr(code, 'source_doc_type,');
225 cn_utils.appindcr(code, 'org_id)'); -- Added For R12 MOAC Change
226 cn_utils.unindent(code, 1);
227 --+
228 -- Generate the SELECT clause for the insert statement
229 --+
230 IF CN_COLLECTION_GEN.isParallelEnabled THEN
231 cn_utils.appindcr(code, 'SELECT '||l_parallel_hint);
232 ELSE
233 cn_utils.appindcr(code, 'SELECT ');
234 END IF;
235 cn_utils.indent(code, 1);
236 cn_utils.appindcr(code, 'cn_not_trx_s.NEXTVAL,');
237
238 -- Changed for Bug 6203234
239 --cn_utils.appindcr(code, 'FLOOR(cn_not_trx_s.CURRVAL/cn_global_var.g_system_batch_size),');
240 cn_utils.appindcr(code, 'x_proc_audit_id,');
241
242 cn_utils.appindcr(code, 'SYSDATE,');
246 cn_utils.appindcr(code, l_header_key||', --*** Header Table Key Column');
243 cn_utils.appindcr(code, 'x_proc_audit_id,');
244 cn_utils.appindcr(code, '''N'',');
245 cn_utils.appindcr(code, x_event_id||',');
247 cn_utils.appindcr(code, l_line_key||', --*** Line Table Key Column');
248 cn_utils.appindcr(code, ''''||l_table_map_rec.mapping_type||''', --*** Source Type');
249 cn_utils.appindcr(code, l_org_id); -- Added For R12 MOAC Change
250 cn_utils.unindent(code, 1);
251 cn_utils.appindcr(code, 'FROM --*** Line and (optional) Header Table');
252 cn_utils.indent(code, 1);
253 cn_utils.appindcr(code, l_notify_from);
254 cn_utils.unindent(code, 1);
255 cn_utils.appindcr(code, 'WHERE --*** Header.Primary_Key = Line.Foreign_Key');
256 cn_utils.indent(code, 1);
257 cn_utils.appindcr(code, l_notify_where);
258 --+
259 -- The generated WHERE clause does not include the supplementary
260 -- WHERE clause information that the user may specified, so this
261 -- needs to be added on.
262 -- Before doing so, strip off trailing ';' on the clause, if one
263 -- is present
264 --+
265 cn_utils.appindcr(code, '--*** Any extra user-defined WHERE statement lines');
266 IF l_table_map_rec.notify_where IS NOT NULL THEN
267 cn_utils.appindcr(code, 'AND '||RTRIM(l_table_map_rec.notify_where,';'));
268 END IF;
269 cn_utils.appindcr(code, '--*** End of user-defined WHERE statement lines');
270
271 --+
272 -- Finish off the statement
273 --+
274
275 cn_utils.appindcr(code, ' AND NOT EXISTS (');
276 IF CN_COLLECTION_GEN.isParallelEnabled THEN
277 cn_utils.appindcr(code, ' SELECT /'||'*'||'+ PARALLEL(cn_not_trx) '||'*/ 1');
278 ELSE
279 cn_utils.appindcr(code, ' SELECT 1');
280 END IF;
281 cn_utils.appindcr(code, ' FROM cn_not_trx');
282 cn_utils.appindcr(code, ' WHERE source_trx_line_id = ' || l_line_key ||' --*** Line.Primary_Key');
283 cn_utils.appindcr(code, ' AND event_id = ' || x_event_id );
284 cn_utils.appindcr(code, ' AND org_id = ' || l_org_id || ');');
285 cn_utils.unindent(code, 1);
286
287 cn_utils.appindcr(code, 'END; -- Notification Insert Block');
288
289 cn_utils.appendcr(code);
290 cn_utils.appindcr(code, '-- Commit the notification inserts - they are permanent even if collection fails');
291 cn_utils.appindcr(code, 'COMMIT;');
292 cn_utils.unset_org_id();
293
294 cn_message_pkg.debug('*** Finished notification run ');
295 fnd_file.put_line(fnd_file.Log,'*** Finished notification run ');
296
297 cn_utils.set_org_id(p_org_id => l_org_id);
298 cn_utils.appendcr(code);
299
300 cn_utils.appendcr(code, '--******** COLLECT AJUSTMENTS (NEGATE IN API) ********-- ');
301 cn_utils.appindcr(code, '-- This will negate those adjusted trx in the API table');
302 cn_utils.appindcr(code, 'cn_not_trx_grp.col_adjustments(p_api_version => 1.0,');
303 cn_utils.appindcr(code, ' x_return_status => x_return_status,');
304 cn_utils.appindcr(code, ' x_msg_count => x_msg_count,');
305 cn_utils.appindcr(code, ' x_msg_data => x_msg_data,');
306
307 cn_utils.appindcr(code, ' p_org_id => '|| l_org_id ||');');
308 -- Added For R12 MOAC Changes
309 cn_utils.appendcr(code);
310
311 cn_utils.unset_org_id();
312 END insert_cn_not_trx;
313
314 --
315 -- Procedure Name
316 -- insert_comm_lines_api_select
317 -- Purpose
318 -- This procedure uses the Direct Column Mappings to
319 -- generate the 'INSERT INTO cn_comm_lines_api VALUES (...) SELECT ...'
320 -- portion of the SQL statement wich populates the api table
321 --
322 -- History
323 -- 03-17-00 Dave Maskell Created for Release 11i2.
324 --
325 --
326 PROCEDURE insert_comm_lines_api_select(
327 x_table_map_id IN cn_table_maps_v.table_map_id%TYPE,
328 code IN OUT NOCOPY cn_utils.code_type,
329 x_org_id IN NUMBER,
330 x_parallel_hint IN VARCHAR2)
331 IS -- Added For R12 MOAC Changes
332
333
334 CURSOR api_direct_maps IS
335 SELECT column_map_id, expression, cno.NAME dest_column FROM cn_column_maps ccmv, cn_objects cno
336 WHERE ccmv.table_map_id = x_table_map_id
337 AND ccmv.expression IS NOT NULL
338 AND ccmv.calc_ext_table_id IS NULL
339 AND ccmv.update_clause IS NULL
340 AND ccmv.org_id = x_org_id
341 AND ccmv.destination_column_id = cno.object_id
342 AND ccmv.org_id = cno.org_id
343 -- Added For R12 MOAC Changes
344 ORDER BY dest_column;
345
346 BEGIN
347
348 cn_utils.set_org_id(p_org_id => x_org_id);
349 IF CN_COLLECTION_GEN.isParallelEnabled THEN
350 IF x_table_map_id > 0
351 THEN
352 cn_utils.appindcr(code, 'INSERT /'||'*+ PARALLEL(cn_comm_lines_api) */ INTO cn_comm_lines_api (');
353 ELSE
354 cn_utils.appindcr(code, 'INSERT INTO cn_comm_lines_api (');
355 END IF;
356 ELSE
357 cn_utils.appindcr(code, 'INSERT INTO cn_comm_lines_api (');
358 END IF;
359 cn_utils.indent(code, 1);
360
361 cn_utils.appindcr(code, 'comm_lines_api_id,');
362 cn_utils.appindcr(code, 'conc_batch_id,');
366 -- Insert custom columns using api_direct_maps cursor
363 cn_utils.appindcr(code, 'process_batch_id,');
364 cn_utils.appindcr(code, 'org_id,');
365
367 cn_utils.appindcr(code, '--*** Direct Mapping Destination Columns');
368 FOR l IN api_direct_maps LOOP
369 cn_utils.appindcr(code, l.dest_column || ',');
370 END LOOP;
371
372 --Changing the number of places to shift to 2 cos of introduction of new char
373 cn_utils.strip_prev(code, 1); -- remove trailing comma
374 cn_utils.appindcr(code, ')');
375 cn_utils.unindent(code, 1);
376
377
378 -- Generate the SELECT clause for the insert statement
379 IF CN_COLLECTION_GEN.isParallelEnabled THEN
380 cn_utils.appindcr(code, 'SELECT '||x_parallel_hint);
381 ELSE
382 cn_utils.appindcr(code, 'SELECT ');
383 END IF;
384
385 cn_utils.indent(code, 1);
386
387 cn_utils.appindcr(code, 'cn_comm_lines_api_s.NEXTVAL,');
388 cn_utils.appindcr(code, 'x_conc_program_id,');
389 cn_utils.appindcr(code, 'x_proc_audit_id,');
390 cn_utils.appindcr(code, x_org_id||',');
391
392 -- Select other columns using the api_direct_maps cursors. Note that
393 -- we fetch the records in the cursor in the same order as before due to
394 -- the order by column_id in the cursor where clause.
395
396 cn_utils.appindcr(code, '--*** Direct Mapping Source Expressions');
397
398 FOR l IN api_direct_maps LOOP
399 -- R12 Related Changes
400 IF (l.column_map_id = -1083) THEN
401 l.expression := RTRIM(l.expression,')},')||'),'||x_org_id||')';
402 END IF;
403 IF (l.column_map_id = -1092 OR l.column_map_id = -1093) THEN
404 l.expression := RTRIM(l.expression,')},')||','||x_org_id||')';
405 END IF;
406 cn_utils.appindcr(code, l.expression || ',');
407 END LOOP;
408
409 cn_utils.strip_prev(code, 1); -- remove trailing comma
410 cn_utils.appendcr(code);
411 cn_utils.unindent(code, 1);
412 cn_utils.unset_org_id();
413
414 END insert_comm_lines_api_select;
415
416 --
417 -- Procedure Name
418 -- insert_comm_lines_api
419 -- Purpose
420 -- This procedure generates the code to insert into the CN_COMM_LINES_API table
421 -- This includes the generation of the the code for the Notification Process
422 -- History
423 -- 03-17-00 Dave Maskell Created for Release 11i2.
424 --
425 --
426 PROCEDURE insert_comm_lines_api (
427 x_table_map_id cn_table_maps.table_map_id%TYPE,
428 x_event_id cn_events.event_id%TYPE,
429 code IN OUT NOCOPY cn_utils.code_type,
430 x_org_id IN NUMBER)
431 IS
432
433 l_return_status VARCHAR2(4000);
434 l_msg_count NUMBER;
435 l_msg_data VARCHAR2(4000);
436 l_notify_from VARCHAR2(4000);
437 l_notify_where VARCHAR2(4000);
438 l_collect_from VARCHAR2(4000);
439 l_collect_where VARCHAR2(4000);
440 l_table_map_rec cn_table_maps_v%ROWTYPE;
441 l_parallel_hint VARCHAR2(4000) := '/'||'*'||'+';
442 l_org_id NUMBER;
443
444 BEGIN
445
446 l_org_id := x_org_id;
447
448
449
450 --+
451 -- Get the Table Map details for this data source
452 --+
453 SELECT *
454 INTO l_table_map_rec
455 FROM cn_table_maps_v
456 WHERE table_map_id = x_table_map_id
457 AND org_id = l_org_id;
458
459 --+
460 -- Generate the FROM and WHERE clause code. Moved here to fix Bug 6203234
461 --+
462 cn_table_maps_pvt.get_sql_clauses(
463 p_api_version => 1.0,
464 x_return_status => l_return_status,
465 x_msg_count => l_msg_count,
466 x_msg_data => l_msg_data,
467 p_table_map_id => x_table_map_id,
468 x_notify_from => l_notify_from,
469 x_notify_where => l_notify_where,
470 x_collect_from => l_collect_from,
471 x_collect_where => l_collect_where,
472 p_org_id => l_org_id);
473
474 --+
475 -- Changed to fix Bug 6203234 to include PDML in Collection Query
476 --
477 --+
478 IF CN_COLLECTION_GEN.isParallelEnabled THEN
479 create_parallel_hint(x_source_string => l_collect_from,
480 x_parallel_hint => l_parallel_hint
481 );
482 END IF;
483 --+
484 -- Generate the Collection Process code
485 --+
486 cn_utils.set_org_id(p_org_id => X_org_id);
487 cn_utils.appendcr(code);
488 cn_utils.appendcr(code, '--******** INSERT CN_COMM_LINES_API *********-- ');
489 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserting into CN_COMM_LINES_API.'');');
490 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''Inserting into CN_COMM_LINES_API.'');');
491 cn_utils.appendcr(code);
492 --+
493 -- Generate the INSERT INTO API .. SELECT portion of
494 -- the statement
495 --+
496 cn_utils.appindcr(code, '--');
497 cn_utils.appindcr(code, '-- Insert new lines into CN_COMM_LINES_API');
498 cn_utils.appindcr(code, '--');
499
500 cn_utils.unset_org_id();
501 -- Change for Bug 6203234
505 cn_utils.appindcr(code, 'FROM --*** Line, (optional) Header, and any Extra Collection Tables');
502 insert_comm_lines_api_select(x_table_map_id, code, X_org_id, l_parallel_hint);
503 cn_utils.set_org_id(p_org_id => l_org_id);
504
506 cn_utils.indent(code, 1);
507 cn_utils.appindcr(code, l_collect_from);
508 cn_utils.unindent(code, 1);
509 cn_utils.appindcr(code, 'WHERE');
510 cn_utils.appindcr(code, '--*** Line.Primary_Key = cnt.source_trx_line_id AND (optional) Header.Primary_Key = cnt.source_trx_id');
511 cn_utils.indent(code, 1);
512 cn_utils.appindcr(code, l_collect_where);
513
514 --+
515 -- The generated WHERE clause does not include the supplementary
516 -- WHERE clause information that the user may specified, so this
517 -- needs to be added on.
518 -- Before doing so, strip off trailing ';' on the clause, if one
519 -- is present
520 --+
521
522 cn_utils.appindcr(code, '--*** Any extra user-defined WHERE statement lines');
523
524 IF l_table_map_rec.collect_where IS NOT NULL THEN
525 cn_utils.appindcr(code, 'AND '||RTRIM(l_table_map_rec.collect_where,';'));
526 END IF;
527
528 cn_utils.appindcr(code, '--*** End of user-defined WHERE statement lines');
529
530 --+
531 -- Finish off the statement
532 --+
533
534 cn_utils.appindcr(code, ' AND cnt.event_id = ' || x_event_id);
535 cn_utils.appindcr(code, ' AND cnt.collected_flag = ''N''');
536 cn_utils.appindcr(code, ' AND cnt.batch_id = x_proc_audit_id');
537 cn_utils.appindcr(code, ' AND cnt.org_id = '||l_org_id ||' ;');
538 cn_utils.unindent(code, 1);
539
540 cn_utils.appendcr(code);
541 cn_utils.appindcr(code, 'comm_lines_api_count := SQL%ROWCOUNT;');
542 cn_utils.appendcr(code);
543
544 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserted '' || comm_lines_api_count || '' line records into CN_COMM_LINES_API.'');');
545 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''Inserted '' || comm_lines_api_count || '' line records into CN_COMM_LINES_API.'');');
546
547 cn_utils.appendcr(code);
548 cn_utils.appendcr(code);
549
550 -- Update the collected_flag in CN_NOT_TRX
551 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Updating collected_flag in CN_NOT_TRX .'');');
552 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''Updating collected_flag in CN_NOT_TRX .'');');
553
554 cn_utils.appendcr(code);
555
556 IF CN_COLLECTION_GEN.isParallelEnabled THEN
557 cn_utils.appindcr(code, 'UPDATE /'||'*+ PARALLEL(cnt) */ cn_not_trx cnt');
558 ELSE
559 cn_utils.appindcr(code, 'UPDATE cn_not_trx cnt');
560 END IF;
561
562 cn_utils.appindcr(code, ' SET collected_flag = ''Y''');
563 cn_utils.appindcr(code, ' WHERE cnt.event_id = '|| x_event_id);
564 cn_utils.appindcr(code, ' AND cnt.collected_flag = ''N''');
565 cn_utils.appindcr(code, ' AND cnt.batch_id = x_proc_audit_id');
566 cn_utils.appindcr(code, ' AND cnt.org_id = '||l_org_id||' ;');
567 cn_utils.appendcr(code);
568 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Updated collected_flag in cn_not_trx.'');');
569
570 IF CN_COLLECTION_GEN.isParallelEnabled THEN
571 cn_utils.appindcr(code, 'COMMIT;');
572 END IF;
573
574 cn_utils.appendcr(code);
575 cn_utils.unset_org_id();
576
577 EXCEPTION
578 WHEN NO_DATA_FOUND THEN
579 cn_debug.print_msg('insert_lines: in exception handler for NO_DATA_FOUND',1);
580 fnd_file.put_line(fnd_file.Log, 'insert_lines: in exception handler for NO_DATA_FOUND');
581
582 RETURN;
583 END insert_comm_lines_api;
584
585 ---------------------------------------------------------- +
586 -- Procedure Name
587 -- update_comm_lines_api
588 --
589 -- Purpose
590 -- Generates code to update the CN_COMM_LINES_API table
591 -- using Indirect Mappings
592 -- History
593 -- 16-Mar-00 Dave Maskell Created
594 --
595 PROCEDURE update_comm_lines_api (
596 x_table_map_id cn_table_maps.table_map_id%TYPE,
597 code IN OUT NOCOPY cn_utils.code_type,
598 x_org_id IN NUMBER)
599 IS
600
601 x_dest_table_name cn_obj_tables_v.name%TYPE;
602 x_dest_alias cn_obj_tables_v.alias%TYPE;
603 x_row_count NUMBER;
604 x_count NUMBER;
605 CURSOR indmap IS
606 SELECT
607 cm.expression,
608 cm.calc_ext_table_id,
609 cm.update_clause,
610 ext.name relationship_name,
611 LOWER(obj.NAME) external_table_name,
612 LOWER(NVL(ext.alias,obj.NAME)) external_table_alias,
613 LOWER(destcol.name) dest_column
614 FROM
615 cn_column_maps cm,
616 cn_obj_columns_v destcol,
617 cn_calc_ext_tables ext,
618 cn_objects obj
619 WHERE
620 cm.table_map_id = x_table_map_id
621 AND cm.expression IS NOT NULL
622 AND (cm.calc_ext_table_id IS NOT NULL
623 OR cm.update_clause IS NOT NULL)
624 AND ext.calc_ext_table_id(+) = cm.calc_ext_table_id
625 AND obj.object_id(+) = ext.external_table_id
626 AND destcol.column_id = cm.destination_column_id
627 -- make sure no old pre 11iv2 mappings to CN_TRX etc. can slip in
628 AND destcol.table_id = -1008 --CN_COMM_LINES_API
632 AND obj.org_id(+) = ext.org_id
629 AND cm.org_id = x_org_id
630 AND destcol.org_id = cm.org_id
631 AND ext.org_id(+) = cm.org_id
633 ORDER BY destcol.name;
634
635 BEGIN
636 -- MO_GLOBAL.INIT('CN');
637 cn_utils.set_org_id(x_org_id);
638 cn_utils.appendcr(code);
639 cn_utils.appendcr(code, '--******** UPDATE CN_COMM_LINES_API ********-- ');
640 cn_utils.appindcr(code, '--*** Update columns populated by all INDIRECT mappings');
641 --+
642 -- Get name and alias of destination table (should be 'cn_comm_lines_api' and 'api')
643 --+
644
645 -- SELECT
646 -- LOWER(ctmv.destination_table_name),
647 -- LOWER(NVL(ctmv.destination_table_alias,ctmv.destination_table_name))
648
649 -- INTO
650 -- x_dest_table_name,
651 -- x_dest_alias
652
653 -- FROM cn_table_maps_v ctmv
654 -- WHERE ctmv.table_map_id = x_table_map_id
655 -- AND org_id =x_org_id;
656
657 -- Code Change added by pradeep
658 SELECT
659 LOWER(cB.name),
660 LOWER(NVL(cB.alias , cB.name))
661
662 INTO
663 x_dest_table_name,
664 x_dest_alias
665 FROM cn_table_maps ctm, CN_OBJECTS CB
666 WHERE
667 ctm.table_map_id = x_table_map_id
668 and CB.object_id = CTM.destination_table_id
669 and cb.org_id = ctm.org_id
670 AND cb.org_id =x_org_id;
671 -- End of Code Change
672
673 --+
674 -- Generate the UPDATE statement
675 --+
676 -- Check for empty cursor before adding UPDATE command.
677 --+
678 x_row_count := 0;
679 FOR rec IN indmap LOOP
680 x_row_count := SQL%ROWCOUNT ;
681 IF (x_row_count > 0) THEN EXIT;
682 END IF;
683 END LOOP;
684 IF x_row_count >0 THEN
685 --+
686 -- There are some mappings so generate the statement.
687 --+
688 cn_utils.appindcr(code, 'UPDATE ' || x_dest_table_name || ' ' || x_dest_alias);
689 cn_utils.appindcr(code, ' SET');
690 cn_utils.indent(code, 1);
691
692 FOR indmap_rec IN indmap LOOP
693 cn_utils.appindcr(code, indmap_rec.dest_column || ' = ( --*** Indirect Mapping Destination Column');
694 cn_utils.appindcr(code, ' SELECT ');
695 cn_utils.appindcr(code, ' ' || indmap_rec.expression ||' --*** Indirect Mapping Source Expression');
696 IF indmap_rec.external_table_alias IS NOT NULL THEN
697 --+
698 -- This mapping is based on a defined foreign key relationship
699 --+
700 cn_utils.appindcr(code, ' --*** FROM/WHERE derived from Relationship: '||indmap_rec.relationship_name);
701 cn_utils.appindcr(code, ' FROM '||indmap_rec.external_table_name||' '||indmap_rec.external_table_alias);
702 cn_utils.appindcr(code, ' WHERE');
703 x_count := 0;
704 --+
705 -- Build where clause based on all join columns in the relationship
706 --+
707 FOR indmap_fk IN
708 (SELECT LOWER(pkcol.name) pkcolumn_name,
709 LOWER(fkcol.name) fkcolumn_name
710 FROM cn_calc_ext_tbl_dtls dtls,
711 cn_obj_columns_v pkcol,
712 cn_obj_columns_v fkcol
713 WHERE dtls.calc_ext_table_id = indmap_rec.calc_ext_table_id
714 AND pkcol.column_id = dtls.external_column_id
715 AND fkcol.column_id = dtls.internal_column_id
716 AND dtls.org_id = x_org_id
717 AND pkcol.org_id = dtls.org_id
718 AND fkcol.org_id = pkcol.org_id)
719 LOOP
720 IF x_count = 0 THEN
721 x_count := 1;
722 ELSE
723 cn_utils.appindcr(code, ' AND ');
724 END IF;
725 cn_utils.appindcr(code, ' '||indmap_rec.external_table_alias||'.'||indmap_fk.pkcolumn_name||' = api.'||indmap_fk.fkcolumn_name);
726 END LOOP;
727 --Added by Ashley for MOAC
728 cn_utils.appindcr(code, ' AND api.org_id = '||x_org_id);
729 cn_utils.appindcr(code, '),');
730 ELSE
731 --+
732 -- This mapping is based on a free-form update clause
733 --+
734 cn_utils.appindcr(code, ' --*** FROM/WHERE taken from Update Clause');
735 cn_utils.appindcr(code, ' '||indmap_rec.update_clause||'),');
736 END IF;
737 END LOOP;
738 cn_utils.strip_prev(code, 1); -- remove trailing comma
739 cn_utils.appendcr(code);
740 cn_utils.unindent(code, 1);
741 cn_utils.appindcr(code, 'WHERE ' || x_dest_alias || '.process_batch_id = x_proc_audit_id');
742 --Added by Ashley for MOAC
743 cn_utils.appindcr(code, 'AND ' || x_dest_alias || '.org_id = ' || x_org_id || ';');
744 cn_utils.appendcr(code);
745 END IF;
746 --+
747 -- Generate code to update the comm_lines_api_update_count variable
748 --+
749 cn_utils.appendcr(code);
750 cn_utils.appindcr(code, 'cn_message_pkg.debug(''For all INDIRECT mappings updated '' || SQL%ROWCOUNT || '' rows in cn_comm_lines_api.'');');
751 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''For all INDIRECT mappings updated '' || SQL%ROWCOUNT || '' rows in cn_comm_lines_api.'');');
752 cn_utils.appendcr(code);
753 cn_utils.unset_org_id();
754
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 cn_debug.print_msg('update_lines: in exception handler for NO_DATA_FOUND', 1);
758 fnd_file.put_line(fnd_file.Log, 'update_lines: in exception handler for NO_DATA_FOUND');
759 RETURN;
760 END update_comm_lines_api;
761
762 --
763 -- Procedure Name
764 -- filter_comm_lines_api
765 -- Purpose
766 -- This procedure generates the code to filter out unwanted
767 -- lines from cn_comm_lines_api
768 -- History
769 -- 03-29-00 Dave Maskell Created for Release 11i2.
770 --
771 --
772 PROCEDURE filter_comm_lines_api(
773 x_table_map_id cn_table_maps.table_map_id%TYPE,
774 code IN OUT NOCOPY cn_utils.code_type,
775 x_org_id IN NUMBER)
776 IS
777
778 l_delete_flag VARCHAR2(1);
779 l_statement VARCHAR2(100);
780 CURSOR c_filter IS
781 SELECT OBJ.object_value
782 FROM cn_table_map_objects tmov, cn_objects obj
783 WHERE tmov.table_map_id = x_table_map_id
784 AND tmov.tm_object_type = 'FILTER'
785 and tmov.object_id = obj.object_id
786 and tmov.org_id = obj.org_id
787 AND tmov.org_id = x_org_id;
788 BEGIN
789 --+
790 -- Decide whether to DELETE lines or just set them to 'FILTERED'
791 --+
792 SELECT delete_flag
793 INTO l_delete_flag
794 FROM cn_table_maps
795 WHERE table_map_id = x_table_map_id
796 AND org_id = x_org_id;
797
798 IF l_delete_flag = 'Y' THEN
799 l_statement := 'DELETE FROM cn_comm_lines_api api';
800 ELSE
801 l_statement := 'UPDATE cn_comm_lines_api api SET load_status = ''FILTERED''';
802 END IF;
803 cn_utils.set_org_id(x_org_id);
804 cn_utils.appendcr(code);
805 cn_utils.appendcr(code, '--******** FILTER PROCESSING *********-- ');
806 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Filtering unwanted transactions from cn_comm_lines_api.'');');
807 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''Filtering unwanted transactions from cn_comm_lines_api.'');');
808 cn_utils.appendcr(code);
809 --+
810 -- Generate deletion code
811 --+
812 cn_utils.appindcr(code, '--*** User-defined filter statements');
813 FOR rec IN c_filter
814 LOOP
815 --+
816 -- Before doing so, strip off trailing ';' on the clause, if one
817 -- is present
818 --+
819 IF SUBSTR(rec.object_value,LENGTH(rec.object_value),1) = ';' THEN
820 rec.object_value := SUBSTR(rec.object_value,1,LENGTH(rec.object_value)-1);
821 END IF;
822 cn_utils.appindcr(code, l_statement);
823 cn_utils.appindcr(code, 'WHERE');
824 cn_utils.appindcr(code, ' '||rec.object_value);
825 cn_utils.appindcr(code, ' AND api.process_batch_id = x_proc_audit_id;');
826 cn_utils.appendcr(code);
827 END LOOP;
828 cn_utils.appindcr(code, '--*** End of User-defined filter statements');
829 cn_utils.unset_org_id();
830 END filter_comm_lines_api;
831
832 --
833 -- Procedure Name
834 -- Generate_user_code
835 -- Purpose
836 -- Gets user-specificed code for a particular location and generates that code
837 -- History
838 -- 04-03-00 Dave Maskell Created
839 --
840 PROCEDURE Generate_User_Code(
841 p_table_map_id IN NUMBER,
842 p_location_name IN VARCHAR2,
843 code IN OUT NOCOPY cn_utils.code_type,
844 x_org_id IN NUMBER)
845 IS
846 BEGIN
847 cn_utils.set_org_id(x_org_id);
848 cn_utils.appendcr(code);
849 cn_utils.appindcr(code, '--*** User Code, Location: '||p_location_name);
850 --+
851 -- For every line of User Code registered for this location
852 --+
853 FOR rec IN
854 (SELECT OBJ.object_value
855 FROM cn_table_map_objects tmov, cn_objects obj
856 WHERE tmov.table_map_id = p_table_map_id
857 and obj.object_id = tmov.object_id
858 and tmov.org_id = obj.org_id
859 AND tmov.tm_object_type = 'USERCODE'
860 AND UPPER(OBJ.name) = UPPER(p_location_name)
861 AND tmov.org_id = x_org_id
862 ORDER BY table_map_object_id)
863 LOOP
864 --+
868 cn_utils.appindcr(code, rec.object_value);
865 -- Add a terminal ';' if none was registered
866 --+
867 IF rec.object_value LIKE '%;' THEN
869 ELSE
870 cn_utils.appindcr(code, rec.object_value||';');
871 END IF;
872 END LOOP;
873 cn_utils.appindcr(code, '--*** End of User Code, Location: '||p_location_name);
874 cn_utils.appendcr(code);
875 cn_utils.unset_org_id();
876 END Generate_User_Code;
877
878
879 END cn_collection_custom_gen;