[Home] [Help]
PACKAGE BODY: APPS.PO_GEN_DIFF_PKG
Source
1 PACKAGE BODY PO_GEN_DIFF_PKG AS
2 /* $Header: PO_GEN_DIFF_PKG.plb 120.2.12020000.4 2013/02/15 11:16:45 amalick ship $ */
3
4 FUNCTION getBasePK1 RETURN NUMBER IS
5 BEGIN
6 RETURN base_pk1;
7 END;
8
9 FUNCTION getBasePK2 RETURN NUMBER IS
10 BEGIN
11 RETURN base_pk2;
12 END;
13
17 END;
14 FUNCTION getBasePK3 RETURN NUMBER IS
15 BEGIN
16 RETURN base_pk3;
18
19 FUNCTION getBasePK4 RETURN NUMBER IS
20 BEGIN
21 RETURN base_pk4;
22 END;
23
24 FUNCTION getBasePK5 RETURN NUMBER IS
25 BEGIN
26 RETURN base_pk5;
27 END;
28
29 FUNCTION getModPK1 RETURN NUMBER IS
30 BEGIN
31 RETURN mod_pk1;
32 END;
33
34 FUNCTION getModPK2 RETURN NUMBER IS
35 BEGIN
36 RETURN mod_pk2;
37 END;
38
39 FUNCTION getModPK3 RETURN NUMBER IS
40 BEGIN
41 RETURN mod_pk3;
42 END;
43
44 FUNCTION getModPK4 RETURN NUMBER IS
45 BEGIN
46 RETURN mod_pk4;
47 END;
48
49 FUNCTION getModPK5 RETURN NUMBER IS
50 BEGIN
51 RETURN mod_pk5;
52 END;
53
54 --<PAR Project>
55 PROCEDURE generate_par_differences(p_document_type VARCHAR2,
56 base_pk1_value NUMBER,
57 base_pk2_value NUMBER,
58 base_pk3_value NUMBER,
59 base_pk4_value NUMBER,
60 base_pk5_value NUMBER,
61 mod_pk1_value NUMBER,
62 mod_pk2_value NUMBER,
63 mod_pk3_value NUMBER,
64 mod_pk4_value NUMBER,
65 mod_pk5_value NUMBER,
66 dest_table_name VARCHAR2);
67
68 PROCEDURE generate_differences(p_document_type VARCHAR2,
69 p_entity_name VARCHAR2,
70 base_doc_source VARCHAR2,
71 base_pk1_value NUMBER,
72 base_pk2_value NUMBER,
73 base_pk3_value NUMBER,
74 base_pk4_value NUMBER,
75 base_pk5_value NUMBER,
76 base_source_alias VARCHAR2,
77 mod_doc_source VARCHAR2,
78 mod_pk1_value NUMBER,
79 mod_pk2_value NUMBER,
80 mod_pk3_value NUMBER,
81 mod_pk4_value NUMBER,
82 mod_pk5_value NUMBER,
83 mod_source_alias VARCHAR2,
84 dest_table_name VARCHAR2,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_errormsg OUT NOCOPY VARCHAR2,
87 filter_predicate1 VARCHAR2 default null,
88 filter_predicate2 VARCHAR2 default null ) IS
89
90 l_sql_stmt CLOB;
91 l_insert_stmt CLOB;
92 TYPE rc_type IS REF CURSOR;
93 l_rc rc_type;
94
95 l_base_where VARCHAR2(500);
96 l_mod_where VARCHAR2(500);
97 l_sql_where CLOB;
98 l_temp VARCHAR2(1000);
99
100 TYPE l_varchar4k_tbl_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
101 TYPE l_varchar60_tbl_type IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
102 TYPE l_number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
103
104 base_pk1_value_tbl l_number_tbl_type;
105 base_pk2_value_tbl l_number_tbl_type;
106 base_pk3_value_tbl l_number_tbl_type;
107 base_pk4_value_tbl l_number_tbl_type;
108 base_pk5_value_tbl l_number_tbl_type;
109 mod_pk1_value_tbl l_number_tbl_type;
110 mod_pk2_value_tbl l_number_tbl_type;
111 mod_pk3_value_tbl l_number_tbl_type;
112 mod_pk4_value_tbl l_number_tbl_type;
113 mod_pk5_value_tbl l_number_tbl_type;
114 l_colname_tbl l_varchar60_tbl_type;
115 l_oldvalue_tbl l_varchar4k_tbl_type;
116 l_newvalue_tbl l_varchar4k_tbl_type;
117 l_oldvalue_desc_tbl l_varchar4k_tbl_type;
118 l_newvalue_desc_tbl l_varchar4k_tbl_type;
119
120 l_base_pk1 NUMBER;
121 l_base_pk2 NUMBER;
122 l_base_pk3 NUMBER;
123 l_base_pk4 NUMBER;
124 l_base_pk5 NUMBER;
125 l_mod_pk1 NUMBER;
126 l_mod_pk2 NUMBER;
127 l_mod_pk3 NUMBER;
128 l_mod_pk4 NUMBER;
129 l_mod_pk5 NUMBER;
130
131 l_current_user_id NUMBER := FND_GLOBAL.User_Id;
132 l_progress VARCHAR2(10);
133
134 BEGIN
135
136 l_progress := '0010';
137 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
138 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: generate_differences');
139 END IF;
140
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142
143 --assigning all pk_values to package variables for usuage in the views
144 base_pk1 := base_pk1_value;
145 base_pk2 := base_pk2_value;
146 base_pk3 := base_pk3_value;
147 base_pk4 := base_pk4_value;
148 base_pk5 := base_pk5_value;
149 mod_pk1 := mod_pk1_value;
150 mod_pk2 := mod_pk2_value;
151 mod_pk3 := mod_pk3_value;
152 mod_pk4 := mod_pk4_value;
153 mod_pk5 := mod_pk5_value;
154
155 l_progress := '0020';
156 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
157 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' pk_values assigned');
158 END IF;
159
160
161 -- forming where condition for base document
162 IF base_pk1_value IS NOT NULL THEN
163 l_base_where := base_source_alias || '.pk1_value (+) = :1 ';
164 l_base_pk1 := base_pk1_value ;
165 ELSE
166 l_base_where := '1 = :1 ';
167 l_base_pk1 := 1 ;
168 END IF;
169 IF base_pk2_value IS NOT NULL THEN
170 l_base_where := l_base_where || ' and ' || base_source_alias || '.pk2_value (+) = :2 ';
171 l_base_pk2 := base_pk2_value ;
172 ELSE
173 l_base_where := l_base_where || ' and ' || '1 = :2 ';
174 l_base_pk2 := 1 ;
175 END IF;
176 IF base_pk3_value IS NOT NULL THEN
177 l_base_where := l_base_where || ' and ' || base_source_alias || '.pk3_value (+) = :3 ';
178 l_base_pk3 := base_pk3_value ;
179 ELSE
180 l_base_where := l_base_where || ' and ' || '1 = :3 ';
181 l_base_pk3 := 1 ;
182 END IF;
183 IF base_pk4_value IS NOT NULL THEN
184 l_base_where := l_base_where || ' and ' || base_source_alias || '.pk4_value (+) = :4 ';
185 l_base_pk4 := base_pk4_value ;
186 ELSE
187 l_base_where := l_base_where || ' and ' || '1 = :4 ';
188 l_base_pk4 := 1 ;
189 END IF;
190 IF base_pk5_value IS NOT NULL THEN
191 l_base_where := l_base_where || ' and ' || base_source_alias || '.pk5_value (+) = :5 ';
192 l_base_pk5 := base_pk5_value ;
193 ELSE
194 l_base_where := l_base_where || ' and ' || '1 = :5 ';
195 l_base_pk5 := 1 ;
196 END IF;
197
198
199 -- forming where condition for mod document
200 IF mod_pk1_value IS NOT NULL THEN
201 l_mod_where := mod_source_alias || '.pk1_value = :6 ';
202 l_mod_pk1 := mod_pk1_value ;
203 ELSE
204 l_mod_where := '1 = :6 ';
205 l_mod_pk1 := 1 ;
206 END IF;
207 IF mod_pk2_value IS NOT NULL THEN
208 l_mod_where := l_mod_where || ' and ' || mod_source_alias || '.pk2_value = :7 ';
209 l_mod_pk2 := mod_pk2_value ;
210 ELSE
211 l_mod_where := l_mod_where || ' and ' || '1 = :7 ';
212 l_mod_pk2 := 1 ;
213 END IF;
214 IF mod_pk3_value IS NOT NULL THEN
215 l_mod_where := l_mod_where || ' and ' || mod_source_alias || '.pk3_value = :8 ';
216 l_mod_pk3 := mod_pk3_value ;
217 ELSE
218 l_mod_where := l_mod_where || ' and ' || '1 = :8 ';
219 l_mod_pk3 := 1 ;
220 END IF;
221 IF mod_pk4_value IS NOT NULL THEN
222 l_mod_where := l_mod_where || ' and ' || mod_source_alias || '.pk4_value = :9 ';
223 l_mod_pk4 := mod_pk4_value ;
224 ELSE
225 l_mod_where := l_mod_where || ' and ' || '1 = :9 ';
226 l_mod_pk4 := 1 ;
227 END IF;
228 IF mod_pk5_value IS NOT NULL THEN
229 l_mod_where := l_mod_where || ' and ' || mod_source_alias || '.pk5_value = :10 ';
230 l_mod_pk5 := mod_pk5_value ;
231 ELSE
232 l_mod_where := l_mod_where || ' and ' || '1 = :10 ';
233 l_mod_pk5 := 1 ;
234 END IF;
235
236 l_progress := '0030';
237 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
238 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' comparision where clause formed');
239 END IF;
240
241
242
243
244 --inserting base document records into gt
245 l_temp := 'INSERT INTO PO_ENTITY_GT ( PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE, COL_NAME, COL_VALUE, COL_DESC )
246 select PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE, COL_NAME, COL_VALUE, COL_DESC from '|| base_doc_source;
247 execute immediate l_temp;
248
249 l_progress := '0040';
250 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
251 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' base records inserted into gt');
252 END IF;
253
254
255
256 --inserting mod document records into gt.
257 l_temp := 'INSERT INTO PO_ENTITY_GT ( PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE, COL_NAME, COL_VALUE, COL_DESC )
258 select PK1_VALUE, PK2_VALUE, PK3_VALUE, PK4_VALUE, PK5_VALUE, COL_NAME, COL_VALUE, COL_DESC from '|| mod_doc_source;
259 execute immediate l_temp;
260
261 l_progress := '0050';
262 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
263 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' mod records inserted into gt');
264 END IF;
265
266
267
268 --forming comparision where clause
269 l_sql_where := l_base_where || ' and ' || l_mod_where || ' and ' || base_source_alias || '.col_name (+) = ' || mod_source_alias || '.col_name and nvl(' ||
270 base_source_alias || '.col_value, ''#*'') <> ' || mod_source_alias || '.col_value ';
271
272
273
274 --adding filter predicates to where condition
275 IF filter_predicate1 IS NOT NULL THEN
276 l_sql_where := l_sql_where || ' and ' || filter_predicate1;
277 END IF;
278 IF filter_predicate2 IS NOT NULL THEN
279 l_sql_where := l_sql_where || ' and ' || filter_predicate2;
280 END IF;
281
282
283
284 --forming the comparision sql
285 l_sql_stmt := 'SELECT ' || base_source_alias || '.pk1_value, ' ||
286 base_source_alias || '.pk2_value, ' ||
287 base_source_alias || '.pk3_value, ' ||
288 base_source_alias || '.pk4_value, ' ||
289 base_source_alias || '.pk5_value, ' ||
290 mod_source_alias || '.pk1_value, ' ||
291 mod_source_alias || '.pk2_value, ' ||
292 mod_source_alias || '.pk3_value, ' ||
293 mod_source_alias || '.pk4_value, ' ||
294 mod_source_alias || '.pk5_value, ' ||
295 mod_source_alias || '.col_name, ' ||
296 base_source_alias || '.col_value old_value, ' ||
297 mod_source_alias || '.col_value new_value, ' ||
298 base_source_alias || '.col_desc old_desc, ' ||
299 mod_source_alias || '.col_desc new_desc '||
300 ' FROM PO_ENTITY_GT ' || base_source_alias || ', PO_ENTITY_GT ' || mod_source_alias ||
301 ' WHERE '|| l_sql_where;
302
303
304 --looping and inserting values into the destination table
305 OPEN l_rc FOR l_sql_stmt USING l_base_pk1, l_base_pk2, l_base_pk3, l_base_pk4, l_base_pk5, l_mod_pk1, l_mod_pk2, l_mod_pk3, l_mod_pk4, l_mod_pk5 ;
306 LOOP
307 FETCH l_rc BULK COLLECT INTO base_pk1_value_tbl, base_pk2_value_tbl, base_pk3_value_tbl, base_pk4_value_tbl, base_pk5_value_tbl,
308 mod_pk1_value_tbl, mod_pk2_value_tbl, mod_pk3_value_tbl, mod_pk4_value_tbl, mod_pk5_value_tbl,
309 l_colname_tbl, l_oldvalue_tbl, l_newvalue_tbl, l_oldvalue_desc_tbl, l_newvalue_desc_tbl LIMIT 1000;
310 EXIT WHEN l_colname_tbl.count = 0;
311
312 IF p_document_type IN ('AWARD','IDV') THEN
313 FORALL i in 1 .. base_pk1_value_tbl.count
314
315 INSERT INTO po_entity_differences (diff_id, document_type, entity_name, mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val,
316 base_doc_pk3_val, base_doc_pk4_val, base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val,
317 mod_doc_pk3_val, mod_doc_pk4_val, mod_doc_pk5_val, column_name, mod_value, orig_value,
318 mod_value_desc, orig_value_desc, creation_date, created_by, last_update_date, last_updated_by)
319 values(PO_ENTITY_DIFFERENCES_S.nextval, p_document_type, p_entity_name, mod_doc_source, base_pk1_value_tbl(i), base_pk2_value_tbl(i),
320 base_pk3_value_tbl(i), base_pk4_value_tbl(i), base_pk5_value_tbl(i), mod_pk1_value_tbl(i), mod_pk2_value_tbl(i),
321 mod_pk3_value_tbl(i), mod_pk4_value_tbl(i), mod_pk5_value_tbl(i), l_colname_tbl(i), l_newvalue_tbl(i),
322 l_oldvalue_tbl(i), l_newvalue_desc_tbl(i), l_oldvalue_desc_tbl(i), sysdate, l_current_user_id, sysdate, l_current_user_id);
323 ELSIF p_document_type IN ('SOL') THEN
324 FORALL i in 1 .. base_pk1_value_tbl.count
325
326 INSERT INTO pon_entity_differences (diff_id, document_type, entity_name, mod_doc_source_name, base_doc_pk1_val, base_doc_pk2_val,
327 base_doc_pk3_val, base_doc_pk4_val, base_doc_pk5_val, mod_doc_pk1_val, mod_doc_pk2_val,
328 mod_doc_pk3_val, mod_doc_pk4_val, mod_doc_pk5_val, column_name, mod_value, orig_value,
329 mod_value_desc, orig_value_desc, creation_date, created_by, last_update_date, last_updated_by)
330 values(PON_ENTITY_DIFFERENCES_S.nextval,p_document_type, p_entity_name, mod_doc_source, base_pk1_value_tbl(i), base_pk2_value_tbl(i),
331 base_pk3_value_tbl(i), base_pk4_value_tbl(i), base_pk5_value_tbl(i), mod_pk1_value_tbl(i), mod_pk2_value_tbl(i),
332 mod_pk3_value_tbl(i), mod_pk4_value_tbl(i), mod_pk5_value_tbl(i), l_colname_tbl(i), l_newvalue_tbl(i),
333 l_oldvalue_tbl(i), l_newvalue_desc_tbl(i), l_oldvalue_desc_tbl(i), sysdate, l_current_user_id, sysdate, l_current_user_id);
334 END IF;
335 END LOOP;
336
337 l_progress := '0060';
338 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
339 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' differences inserted into '||dest_table_name);
340 END IF;
341
342
343 --deleting mod and base records from gt
344 delete from PO_ENTITY_GT;
345
346 l_progress := '0070';
347 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
348 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' deleted records from gt');
349 END IF;
350
351
352 l_progress := '0080';
353 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
354 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' End proc:generate_differences');
355 END IF;
356
357 EXCEPTION
358 WHEN OTHERS THEN
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360 x_errormsg := 'Unexpectional error occured';
361
362 l_progress := '0090';
363 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
364 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured');
365 END IF;
366 END;
367
368
369
370
371 PROCEDURE find_differences (p_document_type VARCHAR2,
372 p_entity_name VARCHAR2,
373 dest_table_name VARCHAR2,
374 base_pk1_value NUMBER,
375 base_pk2_value NUMBER,
376 base_pk3_value NUMBER,
377 base_pk4_value NUMBER,
378 base_pk5_value NUMBER,
379 mod_pk1_value NUMBER,
380 mod_pk2_value NUMBER,
381 mod_pk3_value NUMBER,
382 mod_pk4_value NUMBER,
383 mod_pk5_value NUMBER) IS
384
385 CURSOR c_entities IS
386 SELECT DISTINCT entity_name, base_doc_source_name, mod_doc_source_name FROM po_diff_config WHERE document_type = p_document_type and nvl(ignore_source_flag, 'N') = 'N';
387
388 CURSOR c_sources IS
389 SELECT DISTINCT base_doc_source_name, mod_doc_source_name FROM po_diff_config WHERE document_type = p_document_type and entity_name = p_entity_name and nvl(ignore_source_flag, 'N') = 'N';
390
391 CURSOR c_filter_preds(l_entity_name VARCHAR2, l_base_doc_source_name VARCHAR2, l_mod_doc_source_name VARCHAR2) IS
392 SELECT column_name FROM po_diff_config WHERE document_type = p_document_type and entity_name = l_entity_name and
393 base_doc_source_name = l_base_doc_source_name and mod_doc_source_name = l_mod_doc_source_name
394 and nvl(cmp_filter_pred_flag, 'N') = 'Y';
395
396
397 l_filter_predicate VARCHAR2(32767);
398 l_return_status VARCHAR2(1);
399 l_error_msg VARCHAR2(100);
400
401 l_progress VARCHAR2(10);
402
403 BEGIN
404
405 l_progress := '0010';
406 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
407 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: find_differences');
408 END IF;
409
410 IF p_entity_name = 'ALL' THEN
411 FOR REC IN c_entities LOOP
412
413 --forming filter predicate string
414 l_filter_predicate := NULL;
415 FOR PRED IN c_filter_preds(REC.entity_name, REC.base_doc_source_name, REC.mod_doc_source_name) LOOP
416 IF l_filter_predicate IS NULL THEN
417 l_filter_predicate := 'base.' || PRED.column_name || ' (+) = mod.' || PRED.column_name;
418 ELSE
419 l_filter_predicate := l_filter_predicate || ' and base.' || PRED.column_name || ' (+) = mod.' || PRED.column_name;
420 END IF;
421 END LOOP;
422
423 generate_differences (p_document_type,
424 REC.entity_name,
425 REC.base_doc_source_name,
426 base_pk1_value,
427 base_pk2_value,
428 base_pk3_value,
429 base_pk4_value,
430 base_pk5_value,
431 'base',
432 REC.mod_doc_source_name,
433 mod_pk1_value,
434 mod_pk2_value,
435 mod_pk3_value,
436 mod_pk4_value,
437 mod_pk5_value,
438 'mod',
439 dest_table_name,
440 l_return_status,
441 l_error_msg,
442 l_filter_predicate);
443 END LOOP;
444 ELSE
445 FOR REC IN c_sources LOOP
446
447 --forming filter predicate string
448 l_filter_predicate := NULL;
449 FOR PRED IN c_filter_preds(p_entity_name, REC.base_doc_source_name, REC.mod_doc_source_name) LOOP
450 IF l_filter_predicate IS NULL THEN
451 l_filter_predicate := 'base.' || PRED.column_name || '(+) = mod.' || PRED.column_name;
452 ELSE
453 l_filter_predicate := l_filter_predicate || ' and base.' || PRED.column_name || '(+) = mod.' || PRED.column_name;
454 END IF;
455 END LOOP;
456
457 generate_differences (p_document_type,
458 p_entity_name,
459 REC.base_doc_source_name,
460 base_pk1_value,
461 base_pk2_value,
462 base_pk3_value,
463 base_pk4_value,
464 base_pk5_value,
465 'base',
466 REC.mod_doc_source_name,
467 mod_pk1_value,
468 mod_pk2_value,
469 mod_pk3_value,
470 mod_pk4_value,
471 mod_pk5_value,
472 'mod',
473 dest_table_name,
474 l_return_status,
475 l_error_msg,
476 l_filter_predicate);
477 END LOOP;
478 END IF;
479
480 l_progress := '0020';
481 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
482 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' return_status:' ||l_return_status);
483 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' error_msg:' ||l_error_msg);
484 END IF;
485
486 l_progress := '0030';
487 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
488 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' End proc: find_differences');
489 END IF;
490
491 END;
492
493
494
495 PROCEDURE delete_differences (p_document_type VARCHAR2,
496 p_entity_name VARCHAR2,
497 dest_table_name VARCHAR2,
498 base_pk1_value NUMBER,
499 base_pk2_value NUMBER,
500 base_pk3_value NUMBER,
501 base_pk4_value NUMBER,
502 base_pk5_value NUMBER,
503 mod_pk1_value NUMBER,
504 mod_pk2_value NUMBER,
505 mod_pk3_value NUMBER,
506 mod_pk4_value NUMBER,
507 mod_pk5_value NUMBER) IS
508
509 l_sql_stmt VARCHAR2(2000);
510 l_where_stmt VARCHAR2(2000);
511 l_progress VARCHAR2(10);
512
513 BEGIN
514
515 l_progress := '0010';
516 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
517 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: delete_differences');
518 END IF;
519
520
521 --forming base document where clause
522 IF base_pk1_value IS NOT NULL THEN
523 l_where_stmt := 'WHERE base_doc_pk1_val = ' || base_pk1_value;
524 END IF;
525 IF base_pk2_value IS NOT NULL THEN
526 IF l_where_stmt IS NOT NULL THEN
527 l_where_stmt := l_where_stmt || ' and base_doc_pk2_val = ' || base_pk2_value;
528 ELSE
529 l_where_stmt := 'WHERE base_doc_pk2_val = ' || base_pk2_value;
530 END IF;
531 END IF;
532 IF base_pk3_value IS NOT NULL THEN
533 IF l_where_stmt IS NOT NULL THEN
534 l_where_stmt := l_where_stmt || ' and base_doc_pk3_val = ' || base_pk3_value;
535 ELSE
536 l_where_stmt := 'WHERE base_doc_pk3_val = ' || base_pk3_value;
537 END IF;
538 END IF;
539 IF base_pk4_value IS NOT NULL THEN
540 IF l_where_stmt IS NOT NULL THEN
541 l_where_stmt := l_where_stmt || ' and base_doc_pk4_val = ' || base_pk4_value;
542 ELSE
543 l_where_stmt := 'WHERE base_doc_pk4_val = ' || base_pk4_value;
544 END IF;
545 END IF;
546 IF base_pk5_value IS NOT NULL THEN
547 IF l_where_stmt IS NOT NULL THEN
548 l_where_stmt := l_where_stmt || ' and base_doc_pk5_val = ' || base_pk5_value;
549 ELSE
550 l_where_stmt := 'WHERE base_doc_pk5_val = ' || base_pk5_value;
551 END IF;
552 END IF;
553
554
555
556 --forming mod document where clause
557 IF mod_pk1_value IS NOT NULL THEN
558 IF l_where_stmt IS NOT NULL THEN
559 l_where_stmt := l_where_stmt || ' and mod_doc_pk1_val = ' || mod_pk1_value;
560 ELSE
561 l_where_stmt := 'WHERE mod_doc_pk1_val = ' || mod_pk1_value;
562 END IF;
563 END IF;
564 IF mod_pk2_value IS NOT NULL THEN
565 IF l_where_stmt IS NOT NULL THEN
566 l_where_stmt := l_where_stmt || ' and mod_doc_pk2_val = ' || mod_pk2_value;
567 ELSE
568 l_where_stmt := 'WHERE mod_doc_pk2_val = ' || mod_pk2_value;
569 END IF;
570 END IF;
571 IF mod_pk3_value IS NOT NULL THEN
572 IF l_where_stmt IS NOT NULL THEN
573 l_where_stmt := l_where_stmt || ' and mod_doc_pk3_val = ' || mod_pk3_value;
574 ELSE
575 l_where_stmt := 'WHERE mod_doc_pk3_val = ' || mod_pk3_value;
576 END IF;
577 END IF;
578 IF mod_pk4_value IS NOT NULL THEN
579 IF l_where_stmt IS NOT NULL THEN
580 l_where_stmt := l_where_stmt || ' and mod_doc_pk4_val = ' || mod_pk4_value;
581 ELSE
582 l_where_stmt := 'WHERE mod_doc_pk4_val = ' || mod_pk4_value;
583 END IF;
584 END IF;
585 IF mod_pk5_value IS NOT NULL THEN
586 IF l_where_stmt IS NOT NULL THEN
587 l_where_stmt := l_where_stmt || ' and mod_doc_pk5_val = ' || mod_pk5_value;
588 ELSE
589 l_where_stmt := 'WHERE mod_doc_pk5_val = ' || mod_pk5_value;
590 END IF;
591 END IF;
592
593
594
595 IF p_document_type IS NOT NULL THEN
596 IF l_where_stmt IS NOT NULL THEN
597 l_where_stmt := l_where_stmt || ' and document_type = ''' || p_document_type ||'''';
598 ELSE
599 l_where_stmt := 'WHERE document_type = ''' || p_document_type ||'''';
600 END IF;
601 END IF;
602
603 IF p_entity_name <> 'ALL' and p_entity_name IS NOT NULL THEN
604 IF l_where_stmt IS NOT NULL THEN
605 l_where_stmt := l_where_stmt || ' and entity_name = ''' || p_entity_name ||'''';
606 ELSE
607 l_where_stmt := 'WHERE entity_name = ''' || p_entity_name ||'''';
608 END IF;
609 END IF;
610
611
612 --deleting differences from the destination table
613 l_sql_stmt := 'DELETE FROM ' || dest_table_name || ' ' || l_where_stmt;
614 execute immediate l_sql_stmt;
615
616 l_progress := '0020';
617 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
618 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' deleted differences from '||dest_table_name);
619 END IF;
620
621
622 l_progress := '0030';
623 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
624 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' End proc: delete_differences');
625 END IF;
626
627
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 l_progress := '0040';
632 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
633 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured');
634 END IF;
635 END;
636
637
638 --<PAR Project>
639 /* ========================================================================================
640 NAME: generate_par_differences
641 DESC: populates po_entity_differences with the changes done to par wrt the entities revison
642 ===========================================================================================*/
643 PROCEDURE generate_par_differences(p_document_type VARCHAR2,
644 base_pk1_value NUMBER,
645 base_pk2_value NUMBER,
646 base_pk3_value NUMBER,
647 base_pk4_value NUMBER,
648 base_pk5_value NUMBER,
649 mod_pk1_value NUMBER,
650 mod_pk2_value NUMBER,
651 mod_pk3_value NUMBER,
652 mod_pk4_value NUMBER,
653 mod_pk5_value NUMBER,
654 dest_table_name VARCHAR2)
655 IS
656
657 TYPE l_varchar4k_tbl_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
658 TYPE l_varchar60_tbl_type IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;
659 TYPE l_number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
660
661 base_pk1_value_tbl l_number_tbl_type;
662 base_pk2_value_tbl l_number_tbl_type;
663 base_pk3_value_tbl l_number_tbl_type;
664 base_pk4_value_tbl l_number_tbl_type;
665 base_pk5_value_tbl l_number_tbl_type;
666 par_pk1_value_tbl l_number_tbl_type;
667 par_pk2_value_tbl l_number_tbl_type;
668 par_pk3_value_tbl l_number_tbl_type;
669 par_pk4_value_tbl l_number_tbl_type;
670 par_pk5_value_tbl l_number_tbl_type;
671 l_colname_tbl l_varchar60_tbl_type;
672 l_oldvalue_tbl l_varchar4k_tbl_type;
673 l_newvalue_tbl l_varchar4k_tbl_type;
674 l_entity_tbl l_varchar4k_tbl_type;
675
676 l_current_user_id NUMBER := FND_GLOBAL.User_Id;
677 l_progress VARCHAR2(10);
678
679 BEGIN
680 l_progress := '0010';
681
682 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
686 base_pk1 := base_pk1_value;
683 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: generate_par_differences');
684 END IF;
685
687 base_pk2 := base_pk2_value;
688 base_pk3 := base_pk3_value;
689 base_pk4 := base_pk4_value;
690 base_pk5 := base_pk5_value;
691 mod_pk1 := mod_pk1_value;
692 mod_pk2 := mod_pk2_value;
693 mod_pk3 := mod_pk3_value;
694 mod_pk4 := mod_pk4_value;
695 mod_pk5 := mod_pk5_value;
696
697 l_progress := '0020';
698 --populate po_entity_gt with header level archive draft records
699 populate_par_header;
700
701 l_progress := '0030';
702 --populate po_entity_gt with line level archive draft records
703 populate_par_lines;
704
705 l_progress := '0040';
706 --populate po_entity_gt with shipment level archive draft records
707 populate_par_line_locs;
708
709 l_progress := '0050';
710 --populate po_entity_gt with distribution level archive draft records
711 populate_par_distributions;
712
713 l_progress := '0055';
714 --populate po_entity_gt with distribution level archive draft records
715 populate_par_elins;
716
717 --Comparison SQL
718
719 SELECT base.pk1_value,
720 base.pk2_value,
721 base.pk3_value,
722 base.pk4_value,
723 base.pk5_value,
724 par.pk1_value,
725 par.pk2_value,
726 par.pk3_value,
727 par.pk4_value,
728 par.pk5_value,
729 par.col_name,
730 base.col_value old_value,
731 par.col_value new_value,
732 par.col_desc
733 BULK COLLECT
734 INTO base_pk1_value_tbl,
735 base_pk2_value_tbl,
736 base_pk3_value_tbl,
737 base_pk4_value_tbl,
738 base_pk5_value_tbl,
739 par_pk1_value_tbl,
740 par_pk2_value_tbl,
741 par_pk3_value_tbl,
742 par_pk4_value_tbl,
743 par_pk5_value_tbl,
744 l_colname_tbl,
745 l_oldvalue_tbl,
746 l_newvalue_tbl,
747 l_entity_tbl
748 FROM PO_ENTITY_GT base, PO_ENTITY_GT par
749 WHERE base.pk1_value (+) = base_pk1
750 AND base.pk2_value (+) = -1
751 AND par.pk1_value = mod_pk1
752 AND par.pk2_value = mod_pk2
753 AND base.col_name (+) = par.col_name
754 AND base.col_desc (+) = par.col_desc --col_desc in case of PAR comparisons corresponds to entity level
755 AND Nvl(base.pk3_value (+), -9999) = Nvl(par.pk3_value, -9999)
756 AND Nvl(base.pk4_value (+), -9999) = Nvl(par.pk4_value, -9999)
757 AND Nvl(base.pk5_value (+), -9999) = Nvl(par.pk5_value, -9999)
758 AND nvl(base.col_value, G_NULL_CHAR) <> Nvl(par.col_value, G_NULL_CHAR);
759
760 l_progress := '0060';
761
762 FORALL i in 1 .. base_pk1_value_tbl.count
763
764 INSERT INTO po_entity_differences (
765 diff_id,
766 document_type,
767 entity_name,
768 mod_doc_source_name,
769 base_doc_pk1_val,
770 base_doc_pk2_val,
771 base_doc_pk3_val,
772 base_doc_pk4_val,
773 base_doc_pk5_val,
774 mod_doc_pk1_val,
775 mod_doc_pk2_val,
776 mod_doc_pk3_val,
777 mod_doc_pk4_val,
778 mod_doc_pk5_val,
779 column_name,
780 mod_value,
781 orig_value,
782 creation_date,
783 created_by,
784 last_update_date,
785 last_updated_by)
786 VALUES (
787 PO_ENTITY_DIFFERENCES_S.nextval,
788 p_document_type,
789 Decode(l_entity_tbl(i), 'PO_HEADERS' , 'HEADER',
790 'PO_LINES' , 'LINE',
791 'PO_LINELOCS' , 'LINELOCATION',
792 'PO_DISTRIBUTIONS' , 'DISTRIBUTION',
793 'PO_HEADERS_UDA' , 'HEADER',
794 'PO_HEADERS_ADDRESSES' , 'HEADER',
795 'PO_LINES_UDA' , 'LINE',
796 'PO_LINELOCS_UDA' , 'LINELOCATION',
797 'PO_ELINS' , 'ELINS'),
798
799 Decode(l_entity_tbl(i), 'PO_HEADERS' , 'PO_HEADERS_DRAFT_DIFF_V',
800 'PO_LINES' , 'PO_LINES_DRAFT_DIFF_V',
801 'PO_LINELOCS' , 'PO_LINELOCS_DRAFT_DIFF_V',
802 'PO_DISTRIBUTIONS' , 'PO_DISTRIBUTIONS_DRAFT_DIFF_V',
803 'PO_HEADERS_UDA' , 'PO_HEADERS_EXT_DRAFT_DIFF_V',
804 'PO_LINES_UDA' , 'PO_LINES_EXT_DRAFT_DIFF_V',
805 'PO_LINELOCS_UDA' , 'PO_LINE_LOCS_DRAFT_EXT_DIFF_V',
806 'PO_HEADERS_ADDRESSES' , 'PO_ADDRESSES_DRAFT_DIFF_V',
807 'PO_ELINS' , 'PO_EXHIBITS_DRAFT_DIFF_V'),
808 base_pk1_value_tbl(i),
809 base_pk2_value_tbl(i),
810 base_pk3_value_tbl(i),
811 base_pk4_value_tbl(i),
812 base_pk5_value_tbl(i),
813 par_pk1_value_tbl(i),
814 par_pk2_value_tbl(i),
815 par_pk3_value_tbl(i),
816 par_pk4_value_tbl(i),
817 par_pk5_value_tbl(i),
818 l_colname_tbl(i),
819 l_newvalue_tbl(i),
820 l_oldvalue_tbl(i),
821 sysdate,
822 l_current_user_id,
823 sysdate,
824 l_current_user_id);
825
826 delete from PO_ENTITY_GT;
827
828 EXCEPTION
829 WHEN OTHERS THEN
830 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
831 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||'0080'||' Unexpected error occured ' || SQLERRM );
832 END IF;
833 delete from PO_ENTITY_GT;
834 END generate_par_differences;
835
836
837
838
839 /* ============================================================================
840 NAME: populate_par_header
841 DESC: populates po_entity_gt with header level archive data and draft data
842 ===============================================================================*/
843
844 PROCEDURE populate_par_header
845 IS
846
847 l_progress VARCHAR2(10);
848
849 BEGIN
850
851 l_progress := '0010';
852
853 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
854 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: populate_par_header');
855 END IF;
856
857 --Insert archive data for a given revision
858 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
859 PK2_VALUE,
860 PK3_VALUE,
861 PK4_VALUE,
862 PK5_VALUE,
863 COL_NAME,
864 COL_VALUE,
865 COL_DESC
866 )
867 SELECT phdv.PK1_VALUE,
868 phdv.PK2_VALUE,
869 phdv.PK3_VALUE,
870 phdv.PK4_VALUE,
871 phdv.PK5_VALUE,
872 phdv.COL_NAME,
873 phdv.COL_VALUE,
874 'PO_HEADERS'
875 FROM PO_HEADERS_DIFF_V phdv, po_headers_draft_all phd
876 WHERE phdv.pk1_value = base_pk1
877 AND phdv.pk2_value = -1
878 AND phdv.pk1_value = phd.po_header_id
879 AND phd.draft_id = mod_pk2
880 AND phdv.revision_num = phd.revision_num;
881
882
883 l_progress := '0020';
884 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
885 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from archive : ' || SQL%ROWCOUNT);
886 END IF;
887
888 l_progress := '0025';
889 --Insert uda archive data for a given revision
890 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
891 PK2_VALUE,
892 PK3_VALUE,
893 PK4_VALUE,
894 PK5_VALUE,
895 COL_NAME,
896 COL_VALUE,
897 COL_DESC
898 )
899 SELECT phdv.PK1_VALUE,
900 phdv.PK2_VALUE,
901 phdv.PK3_VALUE,
902 phdv.PK4_VALUE,
903 phdv.PK5_VALUE,
904 phdv.COL_NAME,
905 phdv.COL_VALUE,
906 'PO_HEADERS_UDA'
907 FROM PO_HEADERS_EXT_DIFF_V phdv, po_headers_draft_all phd
908 WHERE phdv.pk1_value = base_pk1
909 AND phdv.pk2_value = -1
910 AND phdv.pk1_value = phd.po_header_id
911 AND phd.draft_id = mod_pk2
912 AND phdv.revision_num = phd.revision_num;
913
914
915 l_progress := '0027';
916 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
917 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from uda archive : ' || SQL%ROWCOUNT);
918 END IF;
919
920 --Insert addresses uda archive data for a given revision
921 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
922 PK2_VALUE,
923 PK3_VALUE,
924 PK4_VALUE,
925 PK5_VALUE,
926 COL_NAME,
927 COL_VALUE,
928 COL_DESC
929 )
930 SELECT phdv.PK1_VALUE,
931 phdv.PK2_VALUE,
932 phdv.PK3_VALUE,
933 phdv.PK4_VALUE,
934 phdv.PK5_VALUE,
935 phdv.COL_NAME,
936 phdv.COL_VALUE,
937 'PO_HEADERS_ADDRESSES'
938 FROM PO_ADDRESSES_DIFF_V phdv, po_headers_draft_all phd
939 WHERE phdv.pk1_value = base_pk1
940 AND phdv.pk2_value = -1
941 AND phdv.pk1_value = phd.po_header_id
942 AND phd.draft_id = mod_pk2
943 AND phdv.revision_num = phd.revision_num;
944
945
946 l_progress := '0029';
947 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
948 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from addresses uda archive : ' || SQL%ROWCOUNT);
949 END IF;
950
951 --Insert current PAR data i.e. draft data
952 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
953 PK2_VALUE,
954 PK3_VALUE,
955 PK4_VALUE,
956 PK5_VALUE,
957 COL_NAME,
958 COL_VALUE,
959 COL_DESC
960 )
961 SELECT phdv.PK1_VALUE,
962 phdv.PK2_VALUE,
963 phdv.PK3_VALUE,
964 phdv.PK4_VALUE,
965 phdv.PK5_VALUE,
966 phdv.COL_NAME,
967 phdv.COL_VALUE,
968 'PO_HEADERS'
969 FROM PO_HEADERS_DRAFT_DIFF_V phdv
970 WHERE phdv.pk1_value = mod_pk1
971 AND phdv.pk2_value = mod_pk2;
972
973 l_progress := '0030';
974 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
975 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from draft : ' || SQL%ROWCOUNT);
976 END IF;
977
978 --Insert current PAR uda data i.e. ext data
979 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
980 PK2_VALUE,
981 PK3_VALUE,
982 PK4_VALUE,
983 PK5_VALUE,
984 COL_NAME,
985 COL_VALUE,
986 COL_DESC
987 )
988 SELECT phdv.PK1_VALUE,
989 phdv.PK2_VALUE,
990 phdv.PK3_VALUE,
991 phdv.PK4_VALUE,
992 phdv.PK5_VALUE,
993 phdv.COL_NAME,
994 phdv.COL_VALUE,
995 'PO_HEADERS_UDA'
996 FROM PO_HEADERS_EXT_DRAFT_DIFF_V phdv
997 WHERE phdv.pk1_value = mod_pk1
998 AND phdv.pk2_value = mod_pk2;
999
1000 l_progress := '0040';
1001 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1002 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from draft : ' || SQL%ROWCOUNT);
1003 END IF;
1004
1005 --Insert current PAR uda addresses data i.e. ext data
1006 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1007 PK2_VALUE,
1008 PK3_VALUE,
1009 PK4_VALUE,
1010 PK5_VALUE,
1011 COL_NAME,
1012 COL_VALUE,
1013 COL_DESC
1014 )
1015 SELECT phdv.PK1_VALUE,
1016 phdv.PK2_VALUE,
1017 phdv.PK3_VALUE,
1018 phdv.PK4_VALUE,
1019 phdv.PK5_VALUE,
1020 phdv.COL_NAME,
1021 phdv.COL_VALUE,
1022 'PO_HEADERS_ADDRESSES'
1026
1023 FROM PO_ADDRESSES_DRAFT_DIFF_V phdv
1024 WHERE phdv.pk1_value = mod_pk1
1025 AND phdv.pk2_value = mod_pk2;
1027 l_progress := '0050';
1028 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1029 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from draft : ' || SQL%ROWCOUNT);
1030 END IF;
1031
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 l_progress := '0060';
1035 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1036 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured ' || SQLERRM);
1037 END IF;
1038
1039 DELETE FROM PO_ENTITY_GT;
1040 RAISE;
1041 END populate_par_header;
1042
1043
1044
1045 /* ============================================================================
1046 NAME: populate_par_lines
1047 DESC: populates po_entity_gt with line level archive data and draft data
1048 ===============================================================================*/
1049
1050 PROCEDURE populate_par_lines
1051 IS
1052
1053 l_progress VARCHAR2(10);
1054
1055 BEGIN
1056
1057 l_progress := '0010';
1058
1059 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1060 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: populate_par_lines');
1061 END IF;
1062
1063 --Insert archive data for a given revision
1064 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1065 PK2_VALUE,
1066 PK3_VALUE,
1067 PK4_VALUE,
1068 PK5_VALUE,
1069 COL_NAME,
1070 COL_VALUE,
1071 COL_DESC
1072 )
1073 SELECT pldv.PK1_VALUE,
1074 pldv.PK2_VALUE,
1075 pldv.PK3_VALUE,
1076 pldv.PK4_VALUE,
1077 pldv.PK5_VALUE,
1078 pldv.COL_NAME,
1079 pldv.COL_VALUE,
1080 'PO_LINES'
1081 FROM PO_LINES_DIFF_V pldv, po_lines_draft_all pld
1082 WHERE pldv.pk1_value = base_pk1
1083 AND pldv.pk2_value = -1
1084 AND pldv.pk1_value = pld.po_header_id
1085 AND pldv.pk3_value = pld.po_line_id
1086 AND pld.draft_id = mod_pk2
1087 AND pldv.revision_num = pld.revision_num;
1088
1089
1090 l_progress := '0020';
1091 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1092 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from archive : ' || SQL%ROWCOUNT);
1093 END IF;
1094
1095 --Insert uda archive data for a given revision
1096 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1097 PK2_VALUE,
1098 PK3_VALUE,
1099 PK4_VALUE,
1100 PK5_VALUE,
1101 COL_NAME,
1102 COL_VALUE,
1103 COL_DESC
1104 )
1105 SELECT pldv.PK1_VALUE,
1106 pldv.PK2_VALUE,
1107 pldv.PK3_VALUE,
1108 pldv.PK4_VALUE,
1109 pldv.PK5_VALUE,
1110 pldv.COL_NAME,
1111 pldv.COL_VALUE,
1112 'PO_LINES_UDA'
1113 FROM PO_LINES_EXT_DIFF_V pldv, po_lines_draft_all pld
1114 WHERE pldv.pk1_value = base_pk1
1115 AND pldv.pk2_value = -1
1116 AND pldv.pk1_value = pld.po_header_id
1117 AND pldv.pk3_value = pld.po_line_id
1118 AND pld.draft_id = mod_pk2
1119 AND pldv.revision_num = pld.revision_num;
1120
1121
1122 l_progress := '0025';
1123 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1124 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from uda archive : ' || SQL%ROWCOUNT);
1125 END IF;
1126
1127 --Insert current PAR data i.e. draft data
1128 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1129 PK2_VALUE,
1130 PK3_VALUE,
1131 PK4_VALUE,
1132 PK5_VALUE,
1133 COL_NAME,
1134 COL_VALUE,
1135 COL_DESC
1136 )
1137 SELECT pldv.PK1_VALUE,
1138 pldv.PK2_VALUE,
1139 pldv.PK3_VALUE,
1140 pldv.PK4_VALUE,
1141 pldv.PK5_VALUE,
1142 pldv.COL_NAME,
1143 pldv.COL_VALUE,
1144 'PO_LINES'
1145 FROM PO_LINES_DRAFT_DIFF_V pldv
1146 WHERE pldv.pk1_value = mod_pk1
1147 AND pldv.pk2_value = mod_pk2;
1148
1149 l_progress := '0030';
1150 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1151 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from draft : ' || SQL%ROWCOUNT);
1152 END IF;
1153
1154 --Insert current PAR uda data i.e. ext data
1155 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1156 PK2_VALUE,
1157 PK3_VALUE,
1158 PK4_VALUE,
1159 PK5_VALUE,
1160 COL_NAME,
1161 COL_VALUE,
1162 COL_DESC
1163 )
1164 SELECT pldv.PK1_VALUE,
1165 pldv.PK2_VALUE,
1166 pldv.PK3_VALUE,
1167 pldv.PK4_VALUE,
1168 pldv.PK5_VALUE,
1169 pldv.COL_NAME,
1170 pldv.COL_VALUE,
1171 'PO_LINES_UDA'
1172 FROM PO_LINES_EXT_DRAFT_DIFF_V pldv
1173 WHERE pldv.pk1_value = mod_pk1
1174 AND pldv.pk2_value = mod_pk2;
1175
1176 l_progress := '0040';
1177 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1178 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from draft : ' || SQL%ROWCOUNT);
1179 END IF;
1180
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 l_progress := '0050';
1184 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1185 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured ' || SQLERRM);
1186 END IF;
1187
1188 DELETE FROM PO_ENTITY_GT;
1189 RAISE;
1190 END populate_par_lines;
1191
1192
1193
1194 /* ============================================================================
1195 NAME: populate_par_line_locs
1196 DESC: populates po_entity_gt with shipment level archive data and draft data
1197 ===============================================================================*/
1198
1199 PROCEDURE populate_par_line_locs
1200 IS
1201
1202 l_progress VARCHAR2(10);
1203
1204 BEGIN
1205
1206 l_progress := '0010';
1207
1208 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1209 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: populate_par_line_locs');
1210 END IF;
1211
1212 --Insert archive data for a given revision
1213 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1214 PK2_VALUE,
1215 PK3_VALUE,
1216 PK4_VALUE,
1217 PK5_VALUE,
1218 COL_NAME,
1219 COL_VALUE,
1220 COL_DESC
1221 )
1222 SELECT pldv.PK1_VALUE,
1223 pldv.PK2_VALUE,
1224 pldv.PK3_VALUE,
1225 pldv.PK4_VALUE,
1226 pldv.PK5_VALUE,
1227 pldv.COL_NAME,
1228 pldv.COL_VALUE,
1229 'PO_LINELOCS'
1230 FROM PO_LINE_LOCS_DIFF_V pldv, po_line_locations_draft_all pld
1231 WHERE pldv.pk1_value = base_pk1
1232 AND pldv.pk2_value = -1
1233 AND pldv.pk1_value = pld.po_header_id
1234 AND pldv.pk4_value = pld.line_location_id
1235 AND pld.draft_id = mod_pk2
1236 AND pldv.revision_num = pld.revision_num;
1237
1238
1239 l_progress := '0020';
1240 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1241 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from archive : ' || SQL%ROWCOUNT);
1242 END IF;
1243
1244 --Insert archive uda data for a given revision
1245 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1246 PK2_VALUE,
1247 PK3_VALUE,
1248 PK4_VALUE,
1249 PK5_VALUE,
1250 COL_NAME,
1251 COL_VALUE,
1252 COL_DESC
1253 )
1254 SELECT pldv.PK1_VALUE,
1255 pldv.PK2_VALUE,
1256 pldv.PK3_VALUE,
1257 pldv.PK4_VALUE,
1258 pldv.PK5_VALUE,
1259 pldv.COL_NAME,
1260 pldv.COL_VALUE,
1261 'PO_LINELOCS_UDA'
1262 FROM PO_LINE_LOCS_EXT_DIFF_V pldv, po_line_locations_draft_all pld
1263 WHERE pldv.pk1_value = base_pk1
1264 AND pldv.pk2_value = -1
1265 AND pldv.pk1_value = pld.po_header_id
1266 AND pldv.pk4_value = pld.line_location_id
1267 AND pld.draft_id = mod_pk2
1268 AND pldv.revision_num = pld.revision_num;
1269
1270
1271 l_progress := '0025';
1272 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1273 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from uda archive : ' || SQL%ROWCOUNT);
1274 END IF;
1275
1276 --Insert current PAR data i.e. draft data
1277 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1278 PK2_VALUE,
1279 PK3_VALUE,
1280 PK4_VALUE,
1281 PK5_VALUE,
1282 COL_NAME,
1283 COL_VALUE,
1284 COL_DESC
1285 )
1286 SELECT pldv.PK1_VALUE,
1287 pldv.PK2_VALUE,
1288 pldv.PK3_VALUE,
1289 pldv.PK4_VALUE,
1290 pldv.PK5_VALUE,
1291 pldv.COL_NAME,
1292 pldv.COL_VALUE,
1293 'PO_LINELOCS'
1294 FROM PO_LINE_LOCS_DRAFT_DIFF_V pldv
1295 WHERE pldv.pk1_value = mod_pk1
1296 AND pldv.pk2_value = mod_pk2;
1297
1298 l_progress := '0030';
1299 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1300 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from draft : ' || SQL%ROWCOUNT);
1301 END IF;
1302
1303 --Insert current PAR uda data i.e. ext data
1304 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1305 PK2_VALUE,
1306 PK3_VALUE,
1307 PK4_VALUE,
1308 PK5_VALUE,
1309 COL_NAME,
1310 COL_VALUE,
1311 COL_DESC
1312 )
1313 SELECT pldv.PK1_VALUE,
1314 pldv.PK2_VALUE,
1315 pldv.PK3_VALUE,
1316 pldv.PK4_VALUE,
1317 pldv.PK5_VALUE,
1318 pldv.COL_NAME,
1319 pldv.COL_VALUE,
1320 'PO_LINELOCS_UDA'
1321 FROM PO_LINE_LOCS_EXT_DRAFT_DIFF_V pldv
1322 WHERE pldv.pk1_value = mod_pk1
1323 AND pldv.pk2_value = mod_pk2;
1324
1325 l_progress := '0040';
1326 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1327 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from uda : ' || SQL%ROWCOUNT);
1328 END IF;
1329
1330
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 l_progress := '0050';
1334 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1335 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured ' || SQLERRM);
1336 END IF;
1337
1338 DELETE FROM PO_ENTITY_GT;
1339 RAISE;
1340 END populate_par_line_locs;
1341
1342
1343
1344 /* ============================================================================
1345 NAME: populate_par_distributions
1346 DESC: populates po_entity_gt with distribution level archive data and draft data
1347 ===============================================================================*/
1348
1349 PROCEDURE populate_par_distributions
1350 IS
1351
1352 l_progress VARCHAR2(10);
1353
1354 BEGIN
1355
1356 l_progress := '0010';
1357
1358 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1359 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: populate_par_distributions');
1360 END IF;
1361
1362 --Insert archive data for a given revision
1363 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1364 PK2_VALUE,
1365 PK3_VALUE,
1366 PK4_VALUE,
1367 PK5_VALUE,
1368 COL_NAME,
1369 COL_VALUE,
1370 COL_DESC
1371 )
1372 SELECT pdv.PK1_VALUE,
1373 pdv.PK2_VALUE,
1374 pdv.PK3_VALUE,
1375 pdv.PK4_VALUE,
1376 pdv.PK5_VALUE,
1377 pdv.COL_NAME,
1378 pdv.COL_VALUE,
1379 'PO_DISTRIBUTIONS'
1380 FROM PO_DISTRIBUTIONS_DIFF_V pdv, po_distributions_draft_all pd
1381 WHERE pdv.pk1_value = base_pk1
1382 AND pdv.pk2_value = -1
1383 AND pdv.pk1_value = pd.po_header_id
1384 AND pdv.pk5_value = pd.po_distribution_id
1385 AND pd.draft_id = mod_pk2
1386 AND pdv.revision_num = pd.revision_num;
1387
1388
1389 l_progress := '0020';
1390 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1391 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from archive : ' || SQL%ROWCOUNT);
1392 END IF;
1393
1394 --Insert current PAR data i.e. draft data
1395 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1396 PK2_VALUE,
1397 PK3_VALUE,
1398 PK4_VALUE,
1399 PK5_VALUE,
1400 COL_NAME,
1401 COL_VALUE,
1402 COL_DESC
1403 )
1404 SELECT pdv.PK1_VALUE,
1405 pdv.PK2_VALUE,
1406 pdv.PK3_VALUE,
1407 pdv.PK4_VALUE,
1408 pdv.PK5_VALUE,
1409 pdv.COL_NAME,
1410 pdv.COL_VALUE,
1411 'PO_DISTRIBUTIONS'
1412 FROM PO_DISTRIBUTIONS_DRAFT_DIFF_V pdv
1413 WHERE pdv.pk1_value = mod_pk1
1414 AND pdv.pk2_value = mod_pk2;
1415
1416 l_progress := '0030';
1417 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1418 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from draft : ' || SQL%ROWCOUNT);
1419 END IF;
1420
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 l_progress := '0040';
1424 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1425 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured ' || SQLERRM);
1426 END IF;
1427
1428 DELETE FROM PO_ENTITY_GT;
1429 RAISE;
1430 END populate_par_distributions;
1431
1432
1433 /* =======================================================================================
1434 NAME: generate_par_differences_wf
1435 DESC: Called from workflow just before PAR approval. Calls out to generate_par_differences
1436 to generate sgd for PAR
1437 ==========================================================================================*/
1438 PROCEDURE generate_par_differences_wf(itemtype IN VARCHAR2,
1439 itemkey IN VARCHAR2,
1440 actid IN NUMBER,
1441 funcmode IN VARCHAR2,
1442 resultout OUT NOCOPY VARCHAR2)
1443 IS
1444 l_progress VARCHAR2(1000);
1445 l_par_draft_id NUMBER;
1446 l_document_id NUMBER;
1447 BEGIN
1448
1449 l_progress := 'PO_GEN_DIFF_PKG.generate_par_differences_wf Start';
1450 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, l_progress );
1451
1452 l_par_draft_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
1453 itemkey => itemkey,
1454 aname => 'DRAFT_ID');
1455 l_document_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
1456 itemkey => itemkey,
1457 aname => 'DOCUMENT_ID');
1458
1459 generate_par_differences(p_document_type => 'PAR',
1460 base_pk1_value => l_document_id,
1461 base_pk2_value => -1,
1462 base_pk3_value => NULL,
1463 base_pk4_value => NULL,
1464 base_pk5_value => NULL,
1465 mod_pk1_value => l_document_id,
1466 mod_pk2_value => l_par_draft_id,
1467 mod_pk3_value => NULL,
1468 mod_pk4_value => NULL,
1469 mod_pk5_value => NULL,
1470 dest_table_name => 'PO_ENTITY_DIFFERENCES');
1471
1472 resultout := wf_engine.eng_completed;
1473
1474 l_progress := 'PO_GEN_DIFF_PKG.generate_par_differences_wf End';
1475 po_wf_debug_pkg.Insert_debug(itemtype,itemkey, l_progress );
1476
1477 END generate_par_differences_wf;
1478
1479
1480
1481 /* ============================================================================
1482 NAME: populate_par_elins
1483 DESC: populates po_entity_gt with elins archive data and draft data
1484 ===============================================================================*/
1485 PROCEDURE populate_par_elins
1486 IS
1487
1488 l_progress VARCHAR2(10);
1489
1490 BEGIN
1491
1492 l_progress := '0010';
1493
1494 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1495 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: populate_par_elins');
1496 END IF;
1497
1498 --Insert archive data for a given revision
1499 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1500 PK2_VALUE,
1501 PK3_VALUE,
1502 PK4_VALUE,
1503 PK5_VALUE,
1504 COL_NAME,
1505 COL_VALUE,
1506 COL_DESC
1507 )
1508 SELECT pdv.PK1_VALUE,
1509 pdv.PK2_VALUE,
1510 pdv.PK3_VALUE,
1511 pdv.PK4_VALUE,
1512 pdv.PK5_VALUE,
1513 pdv.COL_NAME,
1514 pdv.COL_VALUE,
1515 'PO_ELINS'
1516 FROM po_exhibits_diff_v pdv, po_exhibit_details_draft pd
1517 WHERE pdv.pk1_value = base_pk1
1518 AND pdv.pk2_value = -1
1519 AND pdv.pk1_value = pd.po_header_id
1520 AND pdv.pk3_value = pd.po_exhibit_details_id
1521 AND pd.draft_id = mod_pk2
1522 AND pdv.revision_num = pd.revision_num;
1523
1524
1525 l_progress := '0020';
1526 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1527 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from archive : ' || SQL%ROWCOUNT);
1528 END IF;
1529
1530 --Insert current PAR data i.e. draft data
1531 INSERT INTO PO_ENTITY_GT ( PK1_VALUE,
1532 PK2_VALUE,
1533 PK3_VALUE,
1534 PK4_VALUE,
1535 PK5_VALUE,
1536 COL_NAME,
1537 COL_VALUE,
1538 COL_DESC
1539 )
1540 SELECT pdv.PK1_VALUE,
1541 pdv.PK2_VALUE,
1542 pdv.PK3_VALUE,
1543 pdv.PK4_VALUE,
1544 pdv.PK5_VALUE,
1545 pdv.COL_NAME,
1546 pdv.COL_VALUE,
1547 'PO_ELINS'
1548 FROM po_exhibits_draft_diff_v pdv
1549 WHERE pdv.pk1_value = mod_pk1
1550 AND pdv.pk2_value = mod_pk2;
1551
1552 l_progress := '0030';
1553 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1554 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Rows Inserted from draft : ' || SQL%ROWCOUNT);
1555 END IF;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559 l_progress := '0040';
1560 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1561 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured ' || SQLERRM);
1562 END IF;
1563
1564 DELETE FROM PO_ENTITY_GT;
1565 RAISE;
1566 END populate_par_elins;
1567
1568
1569
1570 END;
1571