[Home] [Help]
PACKAGE BODY: APPS.CS_AUDIT_UPGRADE_CON_PRG
Source
1 PACKAGE BODY CS_AUDIT_UPGRADE_CON_PRG AS
2 /* $Header: csxaucpb.pls 120.8 2005/07/26 13:06:13 appldev ship $ */
3
4 PROCEDURE Perform_Audit_Upgrade
5 (x_errbuf OUT NOCOPY VARCHAR2,
6 x_retcode OUT NOCOPY VARCHAR2,
7 p_audit_date IN VARCHAR2,
8 p_total_workers IN NUMBER
9 --x_batch_size IN NUMBER,
10 --x_num_workers IN NUMBER
11 ) IS
12 BEGIN
13 -- Parent Processing
14 AD_CONC_UTILS_PKG.submit_subrequests
15 (x_errbuf => x_errbuf,
16 x_retcode => x_retcode,
17 x_workerconc_app_shortname => 'CS', --l_product,
18 x_workerconc_progname => 'CSVAWUPG',
19 x_batch_size => 1000,
20 x_num_workers => 3,
21 x_argument4 => p_audit_date,
22 x_argument5 => to_char(sysdate, 'yymmddhh24miss') -- to ensure re-runnable
23 );
24 END Perform_Audit_Upgrade;
25
26 -- Procedure for Worker Concurrent Program
27 PROCEDURE Worker_Audit_Upgrade
28 (x_errbuf OUT NOCOPY VARCHAR2,
29 x_retcode OUT NOCOPY VARCHAR2,
30 x_batch_size IN NUMBER,
31 x_worker_id IN NUMBER,
32 x_num_workers IN NUMBER,
33 p_audit_date IN VARCHAR2,
34 p_update_date IN VARCHAR2
35 ) IS
36
37 -- Variables Declared for the API
38
39 l_loop_counter NUMBER;
40 l_create_record VARCHAR2(1);
41
42 CURSOR c_sr_current(c_start_rowid ROWID, c_end_rowid ROWID, p_audit_date DATE) IS
43 SELECT
44 INCIDENT_ID ,
45 INCIDENT_NUMBER ,
46 LAST_UPDATE_DATE ,
47 LAST_UPDATED_BY ,
48 CREATION_DATE ,
49 CREATED_BY ,
50 CREATION_TIME ,
51 LAST_UPDATE_LOGIN ,
52 INCIDENT_STATUS_ID ,
53 to_number(NULL) OLD_INCIDENT_STATUS_ID ,
54 'N' CHANGE_INCIDENT_STATUS_FLAG ,
55 INCIDENT_TYPE_ID ,
56 to_number(NULL) OLD_INCIDENT_TYPE_ID ,
57 'N' CHANGE_INCIDENT_TYPE_FLAG ,
58 INCIDENT_URGENCY_ID ,
59 to_number(NULL) OLD_INCIDENT_URGENCY_ID ,
60 'N' CHANGE_INCIDENT_URGENCY_FLAG ,
61 INCIDENT_SEVERITY_ID ,
62 to_number(NULL) OLD_INCIDENT_SEVERITY_ID ,
63 'N' CHANGE_INCIDENT_SEVERITY_FLAG,
64 RESPONSIBLE_GROUP_ID ,
65 to_number(NULL) OLD_RESPONSIBLE_GROUP_ID ,
66 'N' CHANGE_RESPONSIBLE_GROUP_FLAG ,
67 INCIDENT_OWNER_ID ,
68 to_number(NULL) OLD_INCIDENT_OWNER_ID ,
69 'N' CHANGE_INCIDENT_OWNER_FLAG ,
70 EXPECTED_RESOLUTION_DATE,
71 TO_DATE(NULL) OLD_EXPECTED_RESOLUTION_DATE ,
72 'N' CHANGE_RESOLUTION_FLAG ,
73 OWNER_GROUP_ID GROUP_ID ,
74 to_number(NULL) OLD_GROUP_ID ,
75 'N' CHANGE_GROUP_FLAG ,
76 OBLIGATION_DATE ,
77 TO_DATE(NULL) OLD_OBLIGATION_DATE ,
78 'N' CHANGE_OBLIGATION_FLAG ,
79 SITE_ID ,
80 to_number(NULL) OLD_SITE_ID ,
81 'N' CHANGE_SITE_FLAG ,
82 BILL_TO_CONTACT_ID ,
83 to_number(NULL) OLD_BILL_TO_CONTACT_ID ,
84 'N' CHANGE_BILL_TO_FLAG ,
85 SHIP_TO_CONTACT_ID ,
86 to_number(NULL) OLD_SHIP_TO_CONTACT_ID ,
87 'N' CHANGE_SHIP_TO_FLAG ,
88 INCIDENT_DATE ,
89 TO_DATE(NULL) OLD_INCIDENT_DATE ,
90 'N' CHANGE_INCIDENT_DATE_FLAG ,
91 CLOSE_DATE ,
92 to_date(NULL) OLD_CLOSE_DATE ,
93 'N' CHANGE_CLOSE_DATE_FLAG ,
94 CUSTOMER_PRODUCT_ID ,
95 to_number(NULL) OLD_CUSTOMER_PRODUCT_ID ,
96 'N' CHANGE_CUSTOMER_PRODUCT_FLAG ,
97 PLATFORM_ID ,
98 to_number(NULL) OLD_PLATFORM_ID ,
99 'N' CHANGE_PLATFORM_ID_FLAG ,
100 PLATFORM_VERSION_ID ,
101 to_number(NULL) OLD_PLATFORM_VERSION_ID ,
102 'N' CHANGE_PLAT_VER_ID_FLAG ,
103 CP_COMPONENT_ID ,
104 to_number(NULL) OLD_CP_COMPONENT_ID ,
105 'N' CHANGE_CP_COMPONENT_ID_FLAG ,
106 CP_COMPONENT_VERSION_ID ,
107 to_number(NULL) OLD_CP_COMPONENT_VERSION_ID,
108 'N' CHANGE_CP_COMP_VER_ID_FLAG ,
109 CP_SUBCOMPONENT_ID ,
110 to_number(NULL) OLD_CP_SUBCOMPONENT_ID ,
111 'N' CHANGE_CP_SUBCOMPONENT_ID_FLAG ,
112 CP_SUBCOMPONENT_VERSION_ID ,
113 to_number(NULL) OLD_CP_SUBCOMPONENT_VERSION_ID ,
114 'N' CHANGE_CP_SUBCOMP_VER_ID_FLAG ,
115 LANGUAGE_ID ,
116 to_number(NULL) OLD_LANGUAGE_ID ,
117 'N' CHANGE_LANGUAGE_ID_FLAG ,
118 TERRITORY_ID ,
119 to_number(NULL) OLD_TERRITORY_ID ,
120 'N' CHANGE_TERRITORY_ID_FLAG ,
121 CP_REVISION_ID ,
122 to_number(NULL) OLD_CP_REVISION_ID ,
123 'N' CHANGE_CP_REVISION_ID_FLAG ,
124 INV_ITEM_REVISION ,
125 to_char(NULL) OLD_INV_ITEM_REVISION ,
126 'N' CHANGE_INV_ITEM_REVISION,
127 INV_COMPONENT_ID ,
128 to_number(NULL) OLD_INV_COMPONENT_ID ,
129 'N' CHANGE_INV_COMPONENT_ID ,
130 INV_COMPONENT_VERSION ,
131 to_char(NULL) OLD_INV_COMPONENT_VERSION ,
132 'N' CHANGE_INV_COMPONENT_VERSION ,
133 INV_SUBCOMPONENT_ID ,
134 to_number(NULL) OLD_INV_SUBCOMPONENT_ID ,
135 'N' CHANGE_INV_SUBCOMPONENT_ID ,
136 INV_SUBCOMPONENT_VERSION ,
137 to_char(NULL) OLD_INV_SUBCOMPONENT_VERSION ,
138 'N' CHANGE_INV_SUBCOMP_VERSION ,
139 RESOURCE_TYPE ,
140 to_char(NULL) OLD_RESOURCE_TYPE ,
141 'N' CHANGE_RESOURCE_TYPE_FLAG ,
142 to_char(NULL) OLD_GROUP_TYPE ,
143 GROUP_TYPE ,
144 'N' CHANGE_GROUP_TYPE_FLAG ,
145 to_date(NULL) OLD_OWNER_ASSIGNED_TIME ,
149 to_number(NULL) OLD_INV_PLATFORM_ORG_ID ,
146 OWNER_ASSIGNED_TIME ,
147 'N' CHANGE_ASSIGNED_TIME_FLAG ,
148 INV_PLATFORM_ORG_ID ,
150 'N' CHANGE_PLATFORM_ORG_ID_FLAG ,
151 COMPONENT_VERSION ,
152 to_char(NULL) OLD_COMPONENT_VERSION ,
153 'N' CHANGE_COMP_VER_FLAG ,
154 SUBCOMPONENT_VERSION ,
155 to_char(NULL) OLD_SUBCOMPONENT_VERSION ,
156 'N' CHANGE_SUBCOMP_VER_FLAG ,
157 PRODUCT_REVISION ,
158 to_char(NULL) OLD_PRODUCT_REVISION ,
159 'N' CHANGE_PRODUCT_REVISION_FLAG ,
160 INVENTORY_ITEM_ID ,
161 to_number(NULL) OLD_INVENTORY_ITEM_ID ,
162 'N' CHANGE_INVENTORY_ITEM_FLAG ,
163 INV_ORGANIZATION_ID ,
164 to_number(NULL) OLD_INV_ORGANIZATION_ID ,
165 'N' CHANGE_INV_ORGANIZATION_FLAG ,
166 STATUS_FLAG ,
167 to_char(NULL) OLD_STATUS_FLAG ,
168 'N' CHANGE_STATUS_FLAG ,
169 PRIMARY_CONTACT_ID ,
170 'N' CHANGE_PRIMARY_CONTACT_FLAG ,
171 to_number(NULL) OLD_PRIMARY_CONTACT_ID ,
175 AND creation_date > p_audit_date;
172 SECURITY_GROUP_ID
173 FROM CS_INCIDENTS_ALL_B
174 WHERE ROWID BETWEEN c_start_rowid AND c_end_rowid
176
177 CURSOR c_sr_audit(c_incident_id number) IS
178 SELECT
179 INCIDENT_ID ,
180 INCIDENT_AUDIT_ID,
181 LAST_UPDATE_DATE ,
182 LAST_UPDATED_BY ,
183 CREATION_DATE ,
184 CREATED_BY ,
185 LAST_UPDATE_LOGIN ,
186 CREATION_TIME ,
187 INCIDENT_STATUS_ID ,
188 OLD_INCIDENT_STATUS_ID ,
189 CHANGE_INCIDENT_STATUS_FLAG ,
190 INCIDENT_TYPE_ID ,
191 OLD_INCIDENT_TYPE_ID ,
192 CHANGE_INCIDENT_TYPE_FLAG ,
193 INCIDENT_URGENCY_ID ,
194 OLD_INCIDENT_URGENCY_ID ,
195 CHANGE_INCIDENT_URGENCY_FLAG ,
196 INCIDENT_SEVERITY_ID ,
197 OLD_INCIDENT_SEVERITY_ID ,
198 CHANGE_INCIDENT_SEVERITY_FLAG,
199 RESPONSIBLE_GROUP_ID ,
200 OLD_RESPONSIBLE_GROUP_ID ,
201 CHANGE_RESPONSIBLE_GROUP_FLAG ,
202 INCIDENT_OWNER_ID ,
203 OLD_INCIDENT_OWNER_ID ,
204 CHANGE_INCIDENT_OWNER_FLAG ,
205 CREATE_MANUAL_ACTION ,
206 ACTION_ID ,
207 EXPECTED_RESOLUTION_DATE,
208 OLD_EXPECTED_RESOLUTION_DATE ,
209 CHANGE_RESOLUTION_FLAG ,
210 GROUP_ID ,
211 OLD_GROUP_ID ,
212 CHANGE_GROUP_FLAG ,
213 OBLIGATION_DATE ,
214 OLD_OBLIGATION_DATE ,
215 CHANGE_OBLIGATION_FLAG ,
216 SITE_ID ,
217 OLD_SITE_ID ,
218 CHANGE_SITE_FLAG ,
219 BILL_TO_CONTACT_ID ,
220 OLD_BILL_TO_CONTACT_ID ,
221 CHANGE_BILL_TO_FLAG ,
222 SHIP_TO_CONTACT_ID ,
223 OLD_SHIP_TO_CONTACT_ID ,
224 CHANGE_SHIP_TO_FLAG ,
225 INCIDENT_DATE ,
226 OLD_INCIDENT_DATE ,
227 CHANGE_INCIDENT_DATE_FLAG ,
228 CLOSE_DATE ,
229 OLD_CLOSE_DATE ,
230 CHANGE_CLOSE_DATE_FLAG ,
231 CUSTOMER_PRODUCT_ID ,
232 OLD_CUSTOMER_PRODUCT_ID ,
233 CHANGE_CUSTOMER_PRODUCT_FLAG ,
234 AUDIT_FIELD ,
235 OBJECT_VERSION_NUMBER ,
236 PLATFORM_ID ,
237 OLD_PLATFORM_ID ,
238 CHANGE_PLATFORM_ID_FLAG ,
239 PLATFORM_VERSION_ID ,
240 OLD_PLATFORM_VERSION_ID ,
241 CHANGE_PLAT_VER_ID_FLAG ,
242 CP_COMPONENT_ID ,
243 OLD_CP_COMPONENT_ID ,
244 CHANGE_CP_COMPONENT_ID_FLAG ,
245 CP_COMPONENT_VERSION_ID ,
246 OLD_CP_COMPONENT_VERSION_ID,
247 CHANGE_CP_COMP_VER_ID_FLAG ,
251 CP_SUBCOMPONENT_VERSION_ID ,
248 CP_SUBCOMPONENT_ID ,
249 OLD_CP_SUBCOMPONENT_ID ,
250 CHANGE_CP_SUBCOMPONENT_ID_FLAG ,
252 OLD_CP_SUBCOMPONENT_VERSION_ID ,
253 CHANGE_CP_SUBCOMP_VER_ID_FLAG ,
254 LANGUAGE_ID ,
255 OLD_LANGUAGE_ID ,
256 CHANGE_LANGUAGE_ID_FLAG ,
257 TERRITORY_ID ,
258 OLD_TERRITORY_ID ,
259 CHANGE_TERRITORY_ID_FLAG ,
260 CP_REVISION_ID ,
261 OLD_CP_REVISION_ID ,
262 CHANGE_CP_REVISION_ID_FLAG ,
263 INV_ITEM_REVISION ,
264 OLD_INV_ITEM_REVISION ,
265 CHANGE_INV_ITEM_REVISION,
266 INV_COMPONENT_ID ,
267 OLD_INV_COMPONENT_ID ,
268 CHANGE_INV_COMPONENT_ID ,
269 INV_COMPONENT_VERSION ,
270 OLD_INV_COMPONENT_VERSION ,
271 CHANGE_INV_COMPONENT_VERSION ,
272 INV_SUBCOMPONENT_ID ,
273 OLD_INV_SUBCOMPONENT_ID ,
274 CHANGE_INV_SUBCOMPONENT_ID ,
275 INV_SUBCOMPONENT_VERSION ,
276 OLD_INV_SUBCOMPONENT_VERSION ,
277 CHANGE_INV_SUBCOMP_VERSION ,
278 RESOURCE_TYPE ,
279 OLD_RESOURCE_TYPE ,
280 CHANGE_RESOURCE_TYPE_FLAG ,
281 OLD_GROUP_TYPE ,
282 GROUP_TYPE ,
283 CHANGE_GROUP_TYPE_FLAG ,
284 OLD_OWNER_ASSIGNED_TIME ,
285 OWNER_ASSIGNED_TIME ,
286 CHANGE_ASSIGNED_TIME_FLAG ,
287 INV_PLATFORM_ORG_ID ,
288 OLD_INV_PLATFORM_ORG_ID ,
289 CHANGE_PLATFORM_ORG_ID_FLAG ,
290 COMPONENT_VERSION ,
291 OLD_COMPONENT_VERSION ,
292 CHANGE_COMP_VER_FLAG ,
293 SUBCOMPONENT_VERSION ,
294 OLD_SUBCOMPONENT_VERSION ,
295 CHANGE_SUBCOMP_VER_FLAG ,
296 PRODUCT_REVISION ,
297 OLD_PRODUCT_REVISION ,
298 CHANGE_PRODUCT_REVISION_FLAG ,
299 INVENTORY_ITEM_ID ,
300 OLD_INVENTORY_ITEM_ID ,
301 CHANGE_INVENTORY_ITEM_FLAG ,
302 INV_ORGANIZATION_ID ,
303 OLD_INV_ORGANIZATION_ID ,
304 CHANGE_INV_ORGANIZATION_FLAG ,
305 STATUS_FLAG ,
306 OLD_STATUS_FLAG ,
307 CHANGE_STATUS_FLAG ,
308 PRIMARY_CONTACT_ID ,
309 CHANGE_PRIMARY_CONTACT_FLAG ,
310 OLD_PRIMARY_CONTACT_ID ,
311 SECURITY_GROUP_ID
312 FROM cs_incidents_audit_b
313 WHERE INCIDENT_ID = c_incident_id
314 AND NVL(UPGRADE_FLAG_FOR_CREATE,'N') = 'N'
315 AND NVL(updated_entity_code, 'SR_HEADER') = 'SR_HEADER'
316 ORDER BY creation_date desc, incident_audit_id desc;
317
318 CURSOR c_sr_audit_asc(c_incident_id number) IS
319 SELECT
320 INCIDENT_ID ,
321 INCIDENT_AUDIT_ID ,
322 LAST_UPDATE_DATE ,
323 LAST_UPDATED_BY ,
324 CREATION_DATE ,
325 CREATED_BY ,
326 LAST_UPDATE_LOGIN ,
327 CREATION_TIME ,
328 INCIDENT_STATUS_ID ,
329 OLD_INCIDENT_STATUS_ID ,
330 CHANGE_INCIDENT_STATUS_FLAG ,
331 INCIDENT_TYPE_ID ,
332 OLD_INCIDENT_TYPE_ID ,
333 CHANGE_INCIDENT_TYPE_FLAG ,
334 INCIDENT_URGENCY_ID ,
335 OLD_INCIDENT_URGENCY_ID ,
336 CHANGE_INCIDENT_URGENCY_FLAG ,
337 INCIDENT_SEVERITY_ID ,
338 OLD_INCIDENT_SEVERITY_ID ,
339 CHANGE_INCIDENT_SEVERITY_FLAG,
340 RESPONSIBLE_GROUP_ID ,
341 OLD_RESPONSIBLE_GROUP_ID ,
342 CHANGE_RESPONSIBLE_GROUP_FLAG ,
343 INCIDENT_OWNER_ID ,
344 OLD_INCIDENT_OWNER_ID ,
345 CHANGE_INCIDENT_OWNER_FLAG ,
346 CREATE_MANUAL_ACTION ,
347 ACTION_ID ,
348 EXPECTED_RESOLUTION_DATE,
349 OLD_EXPECTED_RESOLUTION_DATE ,
350 CHANGE_RESOLUTION_FLAG ,
351 GROUP_ID ,
352 OLD_GROUP_ID ,
353 CHANGE_GROUP_FLAG ,
354 OBLIGATION_DATE ,
355 OLD_OBLIGATION_DATE ,
356 CHANGE_OBLIGATION_FLAG ,
357 SITE_ID ,
358 OLD_SITE_ID ,
359 CHANGE_SITE_FLAG ,
360 BILL_TO_CONTACT_ID ,
361 OLD_BILL_TO_CONTACT_ID ,
362 CHANGE_BILL_TO_FLAG ,
366 INCIDENT_DATE ,
363 SHIP_TO_CONTACT_ID ,
364 OLD_SHIP_TO_CONTACT_ID ,
365 CHANGE_SHIP_TO_FLAG ,
367 OLD_INCIDENT_DATE ,
368 CHANGE_INCIDENT_DATE_FLAG ,
369 CLOSE_DATE ,
370 OLD_CLOSE_DATE ,
371 CHANGE_CLOSE_DATE_FLAG ,
372 CUSTOMER_PRODUCT_ID ,
373 OLD_CUSTOMER_PRODUCT_ID ,
374 CHANGE_CUSTOMER_PRODUCT_FLAG ,
375 AUDIT_FIELD ,
376 OBJECT_VERSION_NUMBER ,
377 PLATFORM_ID ,
378 OLD_PLATFORM_ID ,
379 CHANGE_PLATFORM_ID_FLAG ,
380 PLATFORM_VERSION_ID ,
381 OLD_PLATFORM_VERSION_ID ,
382 CHANGE_PLAT_VER_ID_FLAG ,
383 CP_COMPONENT_ID ,
384 OLD_CP_COMPONENT_ID ,
385 CHANGE_CP_COMPONENT_ID_FLAG ,
386 CP_COMPONENT_VERSION_ID ,
387 OLD_CP_COMPONENT_VERSION_ID,
388 CHANGE_CP_COMP_VER_ID_FLAG ,
389 CP_SUBCOMPONENT_ID ,
390 OLD_CP_SUBCOMPONENT_ID ,
391 CHANGE_CP_SUBCOMPONENT_ID_FLAG ,
392 CP_SUBCOMPONENT_VERSION_ID ,
393 OLD_CP_SUBCOMPONENT_VERSION_ID ,
394 CHANGE_CP_SUBCOMP_VER_ID_FLAG ,
395 LANGUAGE_ID ,
396 OLD_LANGUAGE_ID ,
397 CHANGE_LANGUAGE_ID_FLAG ,
398 TERRITORY_ID ,
399 OLD_TERRITORY_ID ,
400 CHANGE_TERRITORY_ID_FLAG ,
401 CP_REVISION_ID ,
402 OLD_CP_REVISION_ID ,
403 CHANGE_CP_REVISION_ID_FLAG ,
404 INV_ITEM_REVISION ,
405 OLD_INV_ITEM_REVISION ,
406 CHANGE_INV_ITEM_REVISION,
407 INV_COMPONENT_ID ,
408 OLD_INV_COMPONENT_ID ,
409 CHANGE_INV_COMPONENT_ID ,
410 INV_COMPONENT_VERSION ,
411 OLD_INV_COMPONENT_VERSION ,
412 CHANGE_INV_COMPONENT_VERSION ,
413 INV_SUBCOMPONENT_ID ,
414 OLD_INV_SUBCOMPONENT_ID ,
415 CHANGE_INV_SUBCOMPONENT_ID ,
416 INV_SUBCOMPONENT_VERSION ,
417 OLD_INV_SUBCOMPONENT_VERSION ,
418 CHANGE_INV_SUBCOMP_VERSION ,
419 RESOURCE_TYPE ,
420 OLD_RESOURCE_TYPE ,
421 CHANGE_RESOURCE_TYPE_FLAG ,
422 OLD_GROUP_TYPE ,
423 GROUP_TYPE ,
424 CHANGE_GROUP_TYPE_FLAG ,
425 OLD_OWNER_ASSIGNED_TIME ,
426 OWNER_ASSIGNED_TIME ,
427 CHANGE_ASSIGNED_TIME_FLAG ,
428 INV_PLATFORM_ORG_ID ,
429 OLD_INV_PLATFORM_ORG_ID ,
430 CHANGE_PLATFORM_ORG_ID_FLAG ,
431 COMPONENT_VERSION ,
432 OLD_COMPONENT_VERSION ,
433 CHANGE_COMP_VER_FLAG ,
434 SUBCOMPONENT_VERSION ,
435 OLD_SUBCOMPONENT_VERSION ,
436 CHANGE_SUBCOMP_VER_FLAG ,
437 PRODUCT_REVISION ,
438 OLD_PRODUCT_REVISION ,
439 CHANGE_PRODUCT_REVISION_FLAG ,
440 INVENTORY_ITEM_ID ,
441 OLD_INVENTORY_ITEM_ID ,
442 CHANGE_INVENTORY_ITEM_FLAG ,
443 INV_ORGANIZATION_ID ,
444 OLD_INV_ORGANIZATION_ID ,
445 CHANGE_INV_ORGANIZATION_FLAG ,
446 STATUS_FLAG ,
447 OLD_STATUS_FLAG ,
448 CHANGE_STATUS_FLAG ,
449 PRIMARY_CONTACT_ID ,
450 CHANGE_PRIMARY_CONTACT_FLAG ,
451 OLD_PRIMARY_CONTACT_ID ,
452 SECURITY_GROUP_ID
453 FROM cs_incidents_audit_b
454 WHERE INCIDENT_ID = c_incident_id
455 AND NVL(UPGRADE_FLAG_FOR_CREATE,'N') = 'N'
456 AND NVL(updated_entity_code,'SR_HEADER') = 'SR_HEADER'
457 ORDER BY creation_date, incident_audit_id asc;
458
459 CURSOR c_close_flag(p_incident_status_id NUMBER) IS
460 SELECT close_flag
461 FROM cs_incident_statuses_b
462 WHERE incident_status_id = p_incident_status_id;
463
464 l_request_id number;
465 l_incident_audit_id number;
466 l_audit_count number;
467 l_audit_rec c_sr_audit%rowtype;
468 l_audit_rec_asc c_sr_audit_asc%rowtype;
469 l_create_audit_rec c_sr_current%rowtype;
470 loop_cnt number;
471 l_new_close_flag VARCHAR2(1) := 'N';
472 l_old_close_flag VARCHAR2(1) := 'N';
473 l_create_flag_close_date VARCHAR2(1) := 'N';
474
475 l_sta_update number :=0;
476 l_sev_update number :=0;
477 l_urg_update number :=0;
478 l_typ_update number :=0;
479 l_rgp_update number :=0;
480 l_own_update number :=0;
481 l_res_update number :=0;
482 l_grp_update number :=0;
483 l_obl_update number :=0;
484 l_sit_update number :=0;
485 l_bto_update number :=0;
486 l_sto_update number :=0;
487 l_idt_update number :=0;
488 l_cdt_update number :=0;
489 l_cpd_update number :=0;
490 l_pid_update number :=0;
491 l_pvf_update number :=0;
492 l_cci_update number :=0;
493 l_cvi_update number :=0;
494 l_sci_update number :=0;
495 l_svi_update number :=0;
496 l_lan_update number :=0;
497 l_ter_update number :=0;
498 l_cpr_update number :=0;
499 l_iiv_update number :=0;
500 l_ici_update number :=0;
501 l_icv_update number :=0;
502 l_isc_update number :=0;
503 l_isv_update number :=0;
504 l_rrp_update number :=0;
505 l_gpt_update number :=0;
506 l_oat_update number :=0;
507 l_por_update number :=0;
508 l_cov_update number :=0;
509 l_scv_update number :=0;
513 l_sfl_update number :=0;
510 l_prv_update number :=0;
511 l_inv_update number :=0;
512 l_ino_update number :=0;
514 l_pci_update number :=0;
515
516 l_inc_owner_id NUMBER := NULL;
517 l_inc_exp_res_date DATE := NULL;
518 l_inc_group_id NUMBER := NULL;
519 l_inc_oblig_date DATE := NULL;
520 l_inc_site_id NUMBER:= NULL;
521 l_inc_billto_contact NUMBER := NULL;
522 l_inc_shipto_contact NUMBER := NULL;
523 l_inc_inci_date DATE := NULL;
524 l_inc_close_date DATE := NULL;
525 l_inc_cust_prod_id NUMBER := NULL;
526 l_inc_platf_id NUMBER := NULL;
527 l_inc_platf_ver VARCHAR2(250) := NULL;
528 l_inc_cp_comp_id NUMBER := NULL;
529 l_inc_cp_comp_ver_id NUMBER := NULL;
530 l_inc_cp_subcomp_id NUMBER := NULL;
531 l_inc_cp_subcomp_ver_id NUMBER := NULL;
532 l_inc_lang_id NUMBER := NULL;
533 l_inc_terr_id NUMBER := NULL;
534 l_cp_rev_id NUMBER := NULL;
535 l_inc_item_rev VARCHAR2(250) := NULL;
536 l_inc_inv_comp_id NUMBER := NULL;
537 l_inc_inv_comp_ver VARCHAR2(3) := NULL;
538 l_inc_inv_subcomp_id NUMBER := NULL;
539 l_inc_inv_subcomp_ver VARCHAR2(3) := NULL;
540 l_inc_resource_type VARCHAR2(30) := NULL;
541 l_inc_group_type VARCHAR2(30) := NULL;
542 l_inc_owner_assi_time DATE := NULL;
543 l_inc_inv_item_id NUMBER := NULL;
544 l_inc_inv_org_id NUMBER := NULL;
545 l_inc_status_flag VARCHAR2(3) := NULL;
546 --
547 -- the APIs use a combination of TABLE_NAME and UPDATE_NAME to track an
551 -- If you have modified the script for upgrade logic and you want the
548 -- update. The update should be a no-op on a rerun, provided the TABLE_NAME
549 -- and UPDATE_NAME do not change.
550 --
552 -- script to reprocess the data, you must modify UPDATE_NAME to reflect
553 -- the change.
554 --
555 l_audit_date DATE;
556
557 l_worker_id NUMBER;
558 l_product VARCHAR2(30) := 'CS';
559 l_table_name VARCHAR2(30) := 'CS_INCIDENTS_ALL_B';
560 l_table_owner VARCHAR2(30);
561 l_update_name VARCHAR2(30) := 'csxaucpb.pls'; -- l_update_name will be appended with sysdate, do not make this longer than 18 characters
562 l_start_rowid ROWID;
563 l_end_rowid ROWID;
564 l_rows_processed NUMBER;
565 l_status VARCHAR2(30);
566 l_industry VARCHAR2(30);
567 l_retstatus BOOLEAN;
568 l_any_rows_to_process BOOLEAN;
569
570 BEGIN
571
572 l_retstatus := fnd_installation.get_app_info(
573 l_product, l_status, l_industry, l_table_owner);
574
575 IF ((l_retstatus = FALSE) OR (l_table_owner IS NULL)) THEN
576 RAISE_APPLICATION_ERROR(-20001,
577 'Cannot get schema name for product : '||l_product);
578 END IF;
579
580 FND_FILE.PUT_LINE(FND_FILE.LOG, ' X_Worker_Id : '||x_worker_id);
581 FND_FILE.PUT_LINE(FND_FILE.LOG, 'X_Num_Workers : '||x_num_workers);
582
583 BEGIN -- {begin1
584
585 l_audit_date := to_date(p_audit_date,'YYYY/MM/DD HH24:MI:SS');
586 FND_FILE.Put_Line(fnd_file.log, 'p_audit_date=' || p_audit_date);
587 ad_parallel_updates_pkg.initialize_rowid_range(
588 ad_parallel_updates_pkg.ROWID_RANGE,
589 l_table_owner,
590 l_table_name,
591 l_update_name || p_update_date, -- to ensure it is rerunnable
592 x_worker_id,
593 x_num_workers,
594 x_batch_size, 0);
595
596 ad_parallel_updates_pkg.get_rowid_range(
597 l_start_rowid,
598 l_end_rowid,
599 l_any_rows_to_process,
600 x_batch_size,
601 TRUE);
602
603 WHILE (l_any_rows_to_process) LOOP --{Loop1
604 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_start_rowid=' || l_start_rowid || ';l_end_rowid=' || l_end_rowid);
605 open c_sr_current(l_start_rowid,l_end_rowid,l_audit_date);
606 l_loop_counter := 0;
607 fnd_file.put_line(fnd_file.log,'Service Requests processed:');
608 LOOP -- {Loop2
609 fetch c_sr_current into l_create_audit_rec;
610 EXIT WHEN c_sr_current%NOTFOUND;
611 l_sta_update :=0;
612 l_sev_update :=0;
613 l_urg_update :=0;
614 l_typ_update :=0;
615 l_rgp_update :=0;
616 l_own_update :=0;
617 l_res_update :=0;
618 l_grp_update :=0;
619 l_obl_update :=0;
620 l_sit_update :=0;
621 l_bto_update :=0;
622 l_sto_update :=0;
623 l_idt_update :=0;
624 l_cdt_update :=0;
625 l_cpd_update :=0;
626 l_pid_update :=0;
627 l_pvf_update :=0;
628 l_cci_update :=0;
629 l_cvi_update :=0;
630 l_sci_update :=0;
631 l_svi_update :=0;
632 l_lan_update :=0;
633 l_ter_update :=0;
634 l_cpr_update :=0;
638 l_isc_update :=0;
635 l_iiv_update :=0;
636 l_ici_update :=0;
637 l_icv_update :=0;
639 l_isv_update :=0;
640 l_rrp_update :=0;
641 l_gpt_update :=0;
642 l_oat_update :=0;
643 l_por_update :=0;
644 l_cov_update :=0;
645 l_scv_update :=0;
646 l_prv_update :=0;
647 l_inv_update :=0;
648 l_ino_update :=0;
649 l_sfl_update :=0;
650 l_pci_update :=0;
651
652 l_inc_owner_id := NULL;
653 l_inc_exp_res_date := NULL;
654 l_inc_group_id := NULL;
655 l_inc_oblig_date := NULL;
656 l_inc_site_id := NULL;
657 l_inc_billto_contact := NULL;
658 l_inc_shipto_contact := NULL;
659 l_inc_inci_date := NULL;
660 l_inc_close_date := NULL;
661 l_inc_cust_prod_id := NULL;
662 l_inc_platf_id := NULL;
663 l_inc_platf_ver := NULL;
664 l_inc_cp_comp_id := NULL;
665 l_inc_cp_comp_ver_id := NULL;
666 l_inc_cp_subcomp_id := NULL;
667 l_inc_cp_subcomp_ver_id := NULL;
668 l_inc_lang_id := NULL;
669 l_inc_terr_id := NULL;
670 l_cp_rev_id := NULL;
671 l_inc_item_rev := NULL;
672 l_inc_inv_comp_id := NULL;
673 l_inc_inv_comp_ver := NULL;
674 l_inc_inv_subcomp_id := NULL;
675 l_inc_inv_subcomp_ver := NULL;
676 l_inc_resource_type := NULL;
677 l_inc_group_type := NULL;
678 l_inc_owner_assi_time := NULL;
679 l_inc_inv_item_id := NULL;
680 l_inc_inv_org_id := NULL;
681 l_inc_status_flag := NULL;
682
683 l_request_id := l_create_audit_rec.incident_id;
684 --l_loop_counter := 0;
685 select count(incident_audit_id) into l_audit_count
686 from cs_incidents_audit_b
687 where incident_id= l_request_id
688 and NVL(upgrade_flag_for_create,'N') = 'N'
689 and NVL(updated_entity_code, 'SR_HEADER') = 'SR_HEADER';
690
691 fnd_file.put_line(fnd_file.log,'SR Number:' ||l_create_audit_rec.incident_number
692 || '; SR ID: ' || l_request_id);
693 fnd_file.put_line(fnd_file.log,'l_audit_count:' ||l_audit_count);
694
695 if l_audit_count >0 then
696 open c_sr_audit(l_request_id);
697 LOOP
698 FETCH c_sr_audit into l_audit_rec;
699 EXIT WHEN c_sr_audit%NOTFOUND;
700 if l_audit_rec.CHANGE_INCIDENT_STATUS_FLAG = 'Y' then
701 l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG := 'Y';
702 l_create_audit_rec.OLD_INCIDENT_STATUS_ID := l_audit_rec.OLD_INCIDENT_STATUS_ID ;
703 l_create_audit_rec.INCIDENT_STATUS_ID := l_audit_rec.INCIDENT_STATUS_ID ;
704 l_sta_update := 1;
705 elsif NVL(l_audit_rec.CHANGE_INCIDENT_STATUS_FLAG, 'N') = 'N' then
706 l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG := 'N';
707 if l_sta_update >0 then
708 if l_create_audit_rec.old_INCIDENT_STATUS_ID is not null then
709 l_create_audit_rec.INCIDENT_STATUS_ID := l_create_audit_rec.old_INCIDENT_STATUS_ID ;
710 end if;
711 l_create_audit_rec.OLD_INCIDENT_STATUS_ID := l_audit_rec.OLD_INCIDENT_STATUS_ID ;
712 l_sta_update := 0;
713 else
714 l_create_audit_rec.OLD_INCIDENT_STATUS_ID := l_create_audit_rec.INCIDENT_STATUS_ID ;
715 end if;
716 end if;
717
718 if l_audit_rec.CHANGE_INCIDENT_TYPE_FLAG = 'Y' then
719 l_create_audit_rec.CHANGE_INCIDENT_TYPE_FLAG := 'Y';
720 l_create_audit_rec.OLD_INCIDENT_TYPE_ID := l_audit_rec.OLD_INCIDENT_TYPE_ID ;
724 l_create_audit_rec.CHANGE_INCIDENT_TYPE_FLAG := 'N';
721 l_create_audit_rec.INCIDENT_TYPE_ID := l_audit_rec.INCIDENT_TYPE_ID ;
722 l_typ_update := 1;
723 elsif NVL(l_audit_rec.CHANGE_INCIDENT_TYPE_FLAG, 'N') = 'N' then
725 if l_typ_update >0 then
726 if (l_create_audit_rec.OLD_INCIDENT_TYPE_ID is not null) then
727 l_create_audit_rec.INCIDENT_TYPE_ID := l_create_audit_rec.OLD_INCIDENT_TYPE_ID ;
728 end if;
729 l_create_audit_rec.OLD_INCIDENT_TYPE_ID := l_audit_rec.OLD_INCIDENT_TYPE_ID ;
730 l_typ_update := 0;
731 else
732 l_create_audit_rec.OLD_INCIDENT_TYPE_ID := l_create_audit_rec.INCIDENT_TYPE_ID ;
733 end if;
734 end if;
735
736 if l_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG = 'Y' then
737 l_create_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG := 'Y';
738 l_create_audit_rec.OLD_INCIDENT_URGENCY_ID := l_audit_rec.OLD_INCIDENT_URGENCY_ID ;
739 l_create_audit_rec.INCIDENT_URGENCY_ID := l_audit_rec.INCIDENT_URGENCY_ID ;
740 l_urg_update := 1;
741 elsif NVL(l_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG, 'N') = 'N' then
742 l_create_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG := 'N';
743 if l_urg_update >0 then
744 l_create_audit_rec.INCIDENT_URGENCY_ID := l_create_audit_rec.OLD_INCIDENT_URGENCY_ID ;
745 l_create_audit_rec.OLD_INCIDENT_URGENCY_ID := l_audit_rec.OLD_INCIDENT_URGENCY_ID ;
746 l_urg_update := 0;
747 else
748 l_create_audit_rec.OLD_INCIDENT_URGENCY_ID := l_create_audit_rec.INCIDENT_URGENCY_ID ;
749 end if;
750 end if;
751
752 if l_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG = 'Y' then
753 l_create_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG := 'Y';
754 l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID := l_audit_rec.OLD_INCIDENT_SEVERITY_ID ;
755 l_create_audit_rec.INCIDENT_SEVERITY_ID := l_audit_rec.INCIDENT_SEVERITY_ID ;
756 l_sev_update := 1;
757 elsif NVL(l_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG,'N') = 'N' then
758 l_create_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG := 'N';
759 if l_sev_update >0 then
760 if (l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID is not null) then
761 l_create_audit_rec.INCIDENT_SEVERITY_ID := l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID ;
762 end if;
763 l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID := l_audit_rec.OLD_INCIDENT_SEVERITY_ID ;
764 l_sev_update := 0;
765 else
766 l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID := l_create_audit_rec.INCIDENT_SEVERITY_ID ;
767 end if;
768 end if;
769
770 if l_audit_rec.CHANGE_RESPONSIBLE_GROUP_FLAG = 'Y' then
771 l_create_audit_rec.CHANGE_RESPONSIBLE_GROUP_FLAG := 'Y';
772 l_create_audit_rec.OLD_RESPONSIBLE_GROUP_ID := l_audit_rec.OLD_RESPONSIBLE_GROUP_ID ;
773 l_create_audit_rec.RESPONSIBLE_GROUP_ID := l_audit_rec.RESPONSIBLE_GROUP_ID ;
774 l_rgp_update := 1;
775 elsif NVL(l_audit_rec.CHANGE_RESPONSIBLE_GROUP_FLAG, 'N') = 'N' then
776 l_create_audit_rec.CHANGE_RESPONSIBLE_GROUP_FLAG := 'N';
777 if l_rgp_update >0 then
778 l_create_audit_rec.RESPONSIBLE_GROUP_ID := l_create_audit_rec.OLD_RESPONSIBLE_GROUP_ID ;
779 l_create_audit_rec.OLD_RESPONSIBLE_GROUP_ID := l_audit_rec.OLD_RESPONSIBLE_GROUP_ID ;
780 l_rgp_update := 0;
781 else
782 l_create_audit_rec.OLD_RESPONSIBLE_GROUP_ID := l_create_audit_rec.RESPONSIBLE_GROUP_ID ;
783 end if;
784 end if;
785
786 if l_audit_rec.CHANGE_INCIDENT_OWNER_FLAG = 'Y' then
787 l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG := 'Y';
788 l_create_audit_rec.OLD_INCIDENT_OWNER_ID := l_audit_rec.OLD_INCIDENT_OWNER_ID;
789 l_create_audit_rec.INCIDENT_OWNER_ID := l_audit_rec.INCIDENT_OWNER_ID;
790 l_create_audit_rec.CHANGE_ASSIGNED_TIME_FLAG := 'Y';
791 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := l_audit_rec.OLD_OWNER_ASSIGNED_TIME;
792 l_create_audit_rec.OWNER_ASSIGNED_TIME := l_audit_rec.OWNER_ASSIGNED_TIME;
793 if (l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME is null and l_audit_count > 1) then
794 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := l_create_audit_rec.creation_date;
795 end if;
796 l_oat_update := 1;
797 l_own_update := 1;
798 if l_audit_rec.CHANGE_RESOURCE_TYPE_FLAG = 'Y' then
799 l_create_audit_rec.CHANGE_RESOURCE_TYPE_FLAG := 'Y';
800 l_create_audit_rec.OLD_RESOURCE_TYPE := l_audit_rec.OLD_RESOURCE_TYPE;
801 l_create_audit_rec.RESOURCE_TYPE := l_audit_rec.RESOURCE_TYPE ;
802 l_rrp_update := 1;
803 elsif nvl(l_audit_rec.CHANGE_RESOURCE_TYPE_FLAG, 'N') = 'N' then
804 l_create_audit_rec.CHANGE_RESOURCE_TYPE_FLAG := 'N';
805 if l_rrp_update >0 then
806 l_create_audit_rec.RESOURCE_TYPE := l_create_audit_rec.OLD_RESOURCE_TYPE ;
807 l_create_audit_rec.OLD_RESOURCE_TYPE := l_audit_rec.OLD_RESOURCE_TYPE ;
808 l_rrp_update := 0;
809 else
810 l_create_audit_rec.OLD_RESOURCE_TYPE := l_create_audit_rec.RESOURCE_TYPE ;
811 end if;
812 end if;
813 elsif NVL(l_audit_rec.CHANGE_INCIDENT_OWNER_FLAG,'N') = 'N' then
814 l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG := 'N';
815 l_create_audit_rec.CHANGE_ASSIGNED_TIME_FLAG := 'N';
816 if l_own_update >0 then
817 l_create_audit_rec.INCIDENT_OWNER_ID := l_create_audit_rec.OLD_INCIDENT_OWNER_ID ;
818 l_create_audit_rec.OLD_INCIDENT_OWNER_ID := l_audit_rec.OLD_INCIDENT_OWNER_ID ;
819 l_own_update := 0;
820 else
821 l_create_audit_rec.OLD_INCIDENT_OWNER_ID := l_create_audit_rec.INCIDENT_OWNER_ID ;
822 end if;
823 if l_oat_update >0 then
824 l_create_audit_rec.OWNER_ASSIGNED_TIME := l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME ;
825 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := l_audit_rec.OLD_OWNER_ASSIGNED_TIME ;
826 l_oat_update := 0;
827 else
828 if (l_create_audit_rec.OWNER_ASSIGNED_TIME IS NULL) THEN
829 l_create_audit_rec.OWNER_ASSIGNED_TIME := l_create_audit_rec.creation_date;
830 end if;
831 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := l_create_audit_rec.OWNER_ASSIGNED_TIME ;
832 end if;
833
834 if NVL(l_audit_rec.CHANGE_RESOURCE_TYPE_FLAG, 'N') = 'N' then
835 l_create_audit_rec.CHANGE_RESOURCE_TYPE_FLAG := 'N';
836 if l_rrp_update >0 then
837 l_create_audit_rec.RESOURCE_TYPE := l_create_audit_rec.OLD_RESOURCE_TYPE ;
838 l_create_audit_rec.OLD_RESOURCE_TYPE := l_audit_rec.OLD_RESOURCE_TYPE ;
839 l_rrp_update := 0;
840 else
841 l_create_audit_rec.OLD_RESOURCE_TYPE := l_create_audit_rec.RESOURCE_TYPE ;
842 end if;
843 end if;
844 end if;
845
846 if l_audit_rec.CHANGE_RESOLUTION_FLAG = 'Y' then
847 l_create_audit_rec.CHANGE_RESOLUTION_FLAG := 'Y';
848 l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE := l_audit_rec.OLD_EXPECTED_RESOLUTION_DATE ;
849 l_create_audit_rec.EXPECTED_RESOLUTION_DATE := l_audit_rec.EXPECTED_RESOLUTION_DATE ;
850 l_res_update := 1;
851 elsif NVL(l_audit_rec.CHANGE_RESOLUTION_FLAG, 'N') = 'N' then
852 l_create_audit_rec.CHANGE_RESOLUTION_FLAG := 'N';
853 if l_res_update >0 then
854 l_create_audit_rec.EXPECTED_RESOLUTION_DATE := l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE ;
855 l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE := l_audit_rec.OLD_EXPECTED_RESOLUTION_DATE ;
856 l_res_update := 0;
857 else
858 l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE := l_create_audit_rec.EXPECTED_RESOLUTION_DATE ;
859 end if;
860 end if;
861
862 if l_audit_rec.CHANGE_GROUP_FLAG = 'Y' then
863 l_create_audit_rec.CHANGE_GROUP_FLAG := 'Y';
864 l_create_audit_rec.OLD_GROUP_ID := l_audit_rec.OLD_GROUP_ID ;
865 l_create_audit_rec.GROUP_ID := l_audit_rec.GROUP_ID ;
866 l_grp_update := 1;
867 if l_audit_rec.CHANGE_GROUP_TYPE_FLAG = 'Y' then
868 l_create_audit_rec.CHANGE_GROUP_TYPE_FLAG := 'Y';
869 l_create_audit_rec.OLD_GROUP_TYPE := l_audit_rec.OLD_GROUP_TYPE;
870 l_create_audit_rec.GROUP_TYPE := l_audit_rec.GROUP_TYPE;
871 l_gpt_update := 1;
872 end if;
873 elsif NVL(l_audit_rec.CHANGE_GROUP_FLAG, 'N') = 'N' then
874 l_create_audit_rec.CHANGE_GROUP_FLAG := 'N';
875 if l_grp_update >0 then
876 l_create_audit_rec.GROUP_ID := l_create_audit_rec.OLD_GROUP_ID ;
877 l_create_audit_rec.OLD_GROUP_ID := l_audit_rec.OLD_GROUP_ID ;
878 l_grp_update := 0;
879 else
880 l_create_audit_rec.OLD_GROUP_ID := l_create_audit_rec.GROUP_ID ;
881 end if;
882 if NVL(l_audit_rec.CHANGE_GROUP_TYPE_FLAG, 'N') = 'N' then
883 l_create_audit_rec.CHANGE_GROUP_TYPE_FLAG := 'N';
884 if l_gpt_update >0 then
885 l_create_audit_rec.GROUP_TYPE := l_create_audit_rec.OLD_GROUP_TYPE ;
886 l_create_audit_rec.OLD_GROUP_TYPE := l_audit_rec.OLD_GROUP_TYPE ;
887 l_gpt_update := 0;
888 else
889 l_create_audit_rec.OLD_GROUP_TYPE := l_create_audit_rec.GROUP_TYPE ;
890 end if;
891 end if;
892 end if;
893
894 if l_audit_rec.CHANGE_OBLIGATION_FLAG = 'Y' then
895 l_create_audit_rec.CHANGE_OBLIGATION_FLAG := 'Y';
896 l_create_audit_rec.OLD_OBLIGATION_DATE := l_audit_rec.OLD_OBLIGATION_DATE ;
897 l_create_audit_rec.OBLIGATION_DATE := l_audit_rec.OBLIGATION_DATE ;
898 l_obl_update := 1;
899 elsif NVL(l_audit_rec.CHANGE_OBLIGATION_FLAG, 'N') = 'N' then
900 l_create_audit_rec.CHANGE_OBLIGATION_FLAG := 'N';
901 if l_obl_update >0 then
902 l_create_audit_rec.OBLIGATION_DATE := l_create_audit_rec.OLD_OBLIGATION_DATE ;
903 l_create_audit_rec.OLD_OBLIGATION_DATE := l_audit_rec.OLD_OBLIGATION_DATE ;
904 l_obl_update := 0;
905 else
906 l_create_audit_rec.OLD_OBLIGATION_DATE := l_create_audit_rec.OBLIGATION_DATE ;
907 end if;
908 end if;
909
910 if l_audit_rec.CHANGE_SITE_FLAG = 'Y' then
911 l_create_audit_rec.CHANGE_SITE_FLAG := 'Y';
912 l_create_audit_rec.OLD_SITE_ID := l_audit_rec.OLD_SITE_ID ;
913 l_create_audit_rec.SITE_ID := l_audit_rec.SITE_ID ;
914 l_sit_update := 1;
915 elsif NVL(l_audit_rec.CHANGE_SITE_FLAG, 'N') = 'N' then
916 l_create_audit_rec.CHANGE_SITE_FLAG := 'N';
917 if l_sit_update >0 then
918 l_create_audit_rec.SITE_ID := l_create_audit_rec.OLD_SITE_ID ;
919 l_create_audit_rec.OLD_SITE_ID := l_audit_rec.OLD_SITE_ID ;
920 l_sit_update := 0;
921 else
922 l_create_audit_rec.OLD_SITE_ID := l_create_audit_rec.SITE_ID ;
923 end if;
924 end if;
925
926 if l_audit_rec.CHANGE_BILL_TO_FLAG = 'Y' then
927 l_create_audit_rec.CHANGE_BILL_TO_FLAG := 'Y';
928 l_create_audit_rec.OLD_BILL_TO_CONTACT_ID := l_audit_rec.OLD_BILL_TO_CONTACT_ID ;
929 l_create_audit_rec.BILL_TO_CONTACT_ID := l_audit_rec.BILL_TO_CONTACT_ID ;
930 l_bto_update := 1;
931 elsif NVL(l_audit_rec.CHANGE_BILL_TO_FLAG, 'N') = 'N' then
932 l_create_audit_rec.CHANGE_BILL_TO_FLAG := 'N';
933 if l_bto_update >0 then
934 l_create_audit_rec.BILL_TO_CONTACT_ID := l_create_audit_rec.OLD_BILL_TO_CONTACT_ID ;
935 l_create_audit_rec.OLD_BILL_TO_CONTACT_ID := l_audit_rec.OLD_BILL_TO_CONTACT_ID ;
936 l_bto_update := 0;
937 else
938 l_create_audit_rec.OLD_BILL_TO_CONTACT_ID := l_create_audit_rec.BILL_TO_CONTACT_ID ;
939 end if;
940 end if;
941
942 if l_audit_rec.CHANGE_SHIP_TO_FLAG = 'Y' then
943 l_create_audit_rec.CHANGE_SHIP_TO_FLAG := 'Y';
944 l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID := l_audit_rec.OLD_SHIP_TO_CONTACT_ID ;
945 l_create_audit_rec.SHIP_TO_CONTACT_ID := l_audit_rec.SHIP_TO_CONTACT_ID ;
946 l_sto_update := 1;
947 elsif NVL(l_audit_rec.CHANGE_SHIP_TO_FLAG, 'N') = 'N' then
948 l_create_audit_rec.CHANGE_SHIP_TO_FLAG := 'N';
949 if l_sto_update >0 then
950 l_create_audit_rec.SHIP_TO_CONTACT_ID := l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID ;
951 l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID := l_audit_rec.OLD_SHIP_TO_CONTACT_ID ;
952 l_sto_update := 0;
953 else
954 l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID := l_create_audit_rec.SHIP_TO_CONTACT_ID ;
955 end if;
956 end if;
957
958 if l_audit_rec.CHANGE_INCIDENT_DATE_FLAG = 'Y' then
959 l_create_audit_rec.CHANGE_INCIDENT_DATE_FLAG := 'Y';
960 l_create_audit_rec.OLD_INCIDENT_DATE := l_audit_rec.OLD_INCIDENT_DATE ;
961 l_create_audit_rec.INCIDENT_DATE := l_audit_rec.INCIDENT_DATE ;
962 l_idt_update := 1;
963 elsif NVL(l_audit_rec.CHANGE_INCIDENT_DATE_FLAG, 'N') = 'N' then
967 l_create_audit_rec.OLD_INCIDENT_DATE := l_audit_rec.OLD_INCIDENT_DATE ;
964 l_create_audit_rec.CHANGE_INCIDENT_DATE_FLAG := 'N';
965 if l_idt_update >0 then
966 l_create_audit_rec.INCIDENT_DATE := l_create_audit_rec.OLD_INCIDENT_DATE ;
968 l_idt_update := 0;
969 else
970 l_create_audit_rec.OLD_INCIDENT_DATE := l_create_audit_rec.INCIDENT_DATE ;
971 end if;
972 end if;
973
974 if l_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG = 'Y' then
978 l_cpd_update := 1;
975 l_create_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG := 'Y';
976 l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID := l_audit_rec.OLD_CUSTOMER_PRODUCT_ID ;
977 l_create_audit_rec.CUSTOMER_PRODUCT_ID := l_audit_rec.CUSTOMER_PRODUCT_ID ;
979 elsif NVL(l_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG, 'N') = 'N' then
980 l_create_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG := 'N';
981 if l_cpd_update >0 then
982 l_create_audit_rec.CUSTOMER_PRODUCT_ID := l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID ;
983 l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID := l_audit_rec.OLD_CUSTOMER_PRODUCT_ID ;
984 l_cpd_update := 0;
985 else
986 l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID := l_create_audit_rec.CUSTOMER_PRODUCT_ID ;
987 end if;
988 end if;
989
990 if l_audit_rec.CHANGE_PLATFORM_ID_FLAG = 'Y' then
991 l_create_audit_rec.CHANGE_PLATFORM_ID_FLAG := 'Y';
992 l_create_audit_rec.OLD_PLATFORM_ID := l_audit_rec.OLD_PLATFORM_ID ;
993 l_create_audit_rec.PLATFORM_ID := l_audit_rec.PLATFORM_ID ;
994 l_pid_update := 1;
995 elsif NVL(l_audit_rec.CHANGE_PLATFORM_ID_FLAG, 'N') = 'N' then
996 l_create_audit_rec.CHANGE_PLATFORM_ID_FLAG := 'N';
997 if l_pid_update >0 then
998 l_create_audit_rec.PLATFORM_ID := l_create_audit_rec.OLD_PLATFORM_ID ;
999 l_create_audit_rec.OLD_PLATFORM_ID := l_audit_rec.OLD_PLATFORM_ID ;
1000 l_pid_update := 0;
1001 else
1002 l_create_audit_rec.OLD_PLATFORM_ID := l_create_audit_rec.PLATFORM_ID ;
1003 end if;
1004 end if;
1005
1006 if l_audit_rec.CHANGE_PLAT_VER_ID_FLAG = 'Y' then
1007 l_create_audit_rec.CHANGE_PLAT_VER_ID_FLAG := 'Y';
1008 l_create_audit_rec.OLD_PLATFORM_VERSION_ID := l_audit_rec.OLD_PLATFORM_VERSION_ID ;
1009 l_create_audit_rec.PLATFORM_VERSION_ID := l_audit_rec.PLATFORM_VERSION_ID ;
1010 l_pvf_update := 1;
1011 elsif NVL(l_audit_rec.CHANGE_PLAT_VER_ID_FLAG, 'N') = 'N' then
1012 l_create_audit_rec.CHANGE_PLAT_VER_ID_FLAG := 'N';
1013 if l_pvf_update >0 then
1014 l_create_audit_rec.PLATFORM_VERSION_ID := l_create_audit_rec.OLD_PLATFORM_VERSION_ID ;
1015 l_create_audit_rec.OLD_PLATFORM_VERSION_ID := l_audit_rec.OLD_PLATFORM_VERSION_ID ;
1016 l_pvf_update := 0;
1017 else
1018 l_create_audit_rec.OLD_PLATFORM_VERSION_ID := l_create_audit_rec.PLATFORM_VERSION_ID ;
1019 end if;
1020 end if;
1021
1022 if l_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG = 'Y' then
1023 l_create_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG := 'Y';
1024 l_create_audit_rec.OLD_CP_COMPONENT_ID := l_audit_rec.OLD_CP_COMPONENT_ID ;
1025 l_create_audit_rec.CP_COMPONENT_ID := l_audit_rec.CP_COMPONENT_ID ;
1026 l_cci_update := 1;
1027 elsif NVL(l_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG, 'N') = 'N' then
1028 l_create_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG := 'N';
1029 if l_cci_update >0 then
1030 l_create_audit_rec.CP_COMPONENT_ID := l_create_audit_rec.OLD_CP_COMPONENT_ID ;
1031 l_create_audit_rec.OLD_CP_COMPONENT_ID := l_audit_rec.OLD_CP_COMPONENT_ID ;
1032 l_cci_update := 0;
1033 else
1034 l_create_audit_rec.OLD_CP_COMPONENT_ID := l_create_audit_rec.CP_COMPONENT_ID ;
1035 end if;
1036 end if;
1037
1038 if l_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG = 'Y' then
1039 l_create_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG := 'Y';
1040 l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID := l_audit_rec.OLD_CP_COMPONENT_VERSION_ID;
1041 l_create_audit_rec.CP_COMPONENT_VERSION_ID := l_audit_rec.CP_COMPONENT_VERSION_ID;
1042 l_cvi_update := 1;
1043 elsif NVL(l_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG, 'N') = 'N' then
1044 l_create_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG := 'N';
1045 if l_cvi_update >0 then
1046 l_create_audit_rec.CP_COMPONENT_VERSION_ID := l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID ;
1047 l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID := l_audit_rec.OLD_CP_COMPONENT_VERSION_ID ;
1048 l_cvi_update := 0;
1049 else
1050 l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID := l_create_audit_rec.CP_COMPONENT_VERSION_ID ;
1051 end if;
1052 end if;
1053
1054 if l_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG = 'Y' then
1058 l_sci_update := 1;
1055 l_create_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG := 'Y';
1056 l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID := l_audit_rec.OLD_CP_SUBCOMPONENT_ID ;
1057 l_create_audit_rec.CP_SUBCOMPONENT_ID := l_audit_rec.CP_SUBCOMPONENT_ID ;
1059 elsif NVL(l_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG, 'N') = 'N' then
1060 l_create_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG := 'N';
1061 if l_sci_update >0 then
1062 l_create_audit_rec.CP_SUBCOMPONENT_ID := l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID ;
1063 l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID := l_audit_rec.OLD_CP_SUBCOMPONENT_ID ;
1064 l_sci_update := 0;
1065 else
1066 l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID := l_create_audit_rec.CP_SUBCOMPONENT_ID ;
1067 end if;
1068 end if;
1069
1070 if l_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG = 'Y' then
1071 l_create_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG := 'Y';
1072 l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID := l_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID ;
1073 l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID := l_audit_rec.CP_SUBCOMPONENT_VERSION_ID ;
1074 l_svi_update := 1;
1075 elsif NVL(l_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG, 'N') = 'N' then
1076 l_create_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG := 'N';
1077 if l_svi_update >0 then
1078 l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID := l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID ;
1079 l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID := l_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID ;
1080 l_svi_update := 0;
1081 else
1082 l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID := l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID ;
1083 end if;
1084 end if;
1085
1086 if l_audit_rec.CHANGE_LANGUAGE_ID_FLAG = 'Y' then
1087 l_create_audit_rec.CHANGE_LANGUAGE_ID_FLAG := 'Y';
1088 l_create_audit_rec.OLD_LANGUAGE_ID := l_audit_rec.OLD_LANGUAGE_ID ;
1089 l_create_audit_rec.LANGUAGE_ID := l_audit_rec.LANGUAGE_ID ;
1090 l_lan_update := 1;
1091 elsif NVL(l_audit_rec.CHANGE_LANGUAGE_ID_FLAG, 'N') = 'N' then
1092 l_create_audit_rec.CHANGE_LANGUAGE_ID_FLAG := 'N';
1093 if l_lan_update >0 then
1094 l_create_audit_rec.LANGUAGE_ID := l_create_audit_rec.OLD_LANGUAGE_ID ;
1095 l_create_audit_rec.OLD_LANGUAGE_ID := l_audit_rec.OLD_LANGUAGE_ID ;
1096 l_lan_update := 0;
1100 end if;
1097 else
1098 l_create_audit_rec.OLD_LANGUAGE_ID := l_create_audit_rec.LANGUAGE_ID ;
1099 end if;
1101
1102 if l_audit_rec.CHANGE_TERRITORY_ID_FLAG = 'Y' then
1103 l_create_audit_rec.CHANGE_TERRITORY_ID_FLAG := 'Y';
1104 l_create_audit_rec.OLD_TERRITORY_ID := l_audit_rec.OLD_TERRITORY_ID ;
1105 l_create_audit_rec.TERRITORY_ID := l_audit_rec.TERRITORY_ID ;
1106 l_ter_update := 1;
1107 elsif NVL(l_audit_rec.CHANGE_TERRITORY_ID_FLAG, 'N') = 'N' then
1108 l_create_audit_rec.CHANGE_TERRITORY_ID_FLAG := 'N';
1109 if l_ter_update >0 then
1110 l_create_audit_rec.TERRITORY_ID := l_create_audit_rec.OLD_TERRITORY_ID ;
1111 l_create_audit_rec.OLD_TERRITORY_ID := l_audit_rec.OLD_TERRITORY_ID ;
1112 l_ter_update := 0;
1113 else
1114 l_create_audit_rec.OLD_TERRITORY_ID := l_create_audit_rec.TERRITORY_ID ;
1115 end if;
1116 end if;
1117
1118 if l_audit_rec.CHANGE_CP_REVISION_ID_FLAG = 'Y' then
1119 l_create_audit_rec.CHANGE_CP_REVISION_ID_FLAG := 'Y';
1120 l_create_audit_rec.OLD_CP_REVISION_ID := l_audit_rec.OLD_CP_REVISION_ID ;
1121 l_create_audit_rec.CP_REVISION_ID := l_audit_rec.CP_REVISION_ID ;
1122 l_cpr_update := 1;
1123 elsif NVL(l_audit_rec.CHANGE_CP_REVISION_ID_FLAG, 'N') = 'N' then
1124 l_create_audit_rec.CHANGE_CP_REVISION_ID_FLAG := 'N';
1125 if l_cpr_update >0 then
1126 l_create_audit_rec.CP_REVISION_ID := l_create_audit_rec.CP_REVISION_ID ;
1127 l_create_audit_rec.OLD_CP_REVISION_ID := l_audit_rec.OLD_CP_REVISION_ID ;
1128 l_cpr_update := 0;
1129 else
1130 l_create_audit_rec.OLD_CP_REVISION_ID := l_create_audit_rec.CP_REVISION_ID ;
1131 end if;
1132 end if;
1133
1134 if l_audit_rec.CHANGE_INV_ITEM_REVISION = 'Y' then
1135 l_create_audit_rec.CHANGE_INV_ITEM_REVISION := 'Y';
1136 l_create_audit_rec.OLD_INV_ITEM_REVISION := l_audit_rec.OLD_INV_ITEM_REVISION ;
1137 l_create_audit_rec.INV_ITEM_REVISION := l_audit_rec.INV_ITEM_REVISION ;
1138 l_iiv_update := 1;
1139 elsif NVL(l_audit_rec.CHANGE_INV_ITEM_REVISION, 'N') = 'N' then
1140 l_create_audit_rec.CHANGE_INV_ITEM_REVISION := 'N';
1141 if l_iiv_update >0 then
1142 l_create_audit_rec.INV_ITEM_REVISION := l_create_audit_rec.OLD_INV_ITEM_REVISION ;
1143 l_create_audit_rec.OLD_INV_ITEM_REVISION := l_audit_rec.OLD_INV_ITEM_REVISION ;
1144 l_iiv_update := 0;
1148 end if;
1145 else
1146 l_create_audit_rec.OLD_INV_ITEM_REVISION := l_create_audit_rec.INV_ITEM_REVISION ;
1147 end if;
1149
1150 if l_audit_rec.CHANGE_INV_COMPONENT_ID = 'Y' then
1151 l_create_audit_rec.CHANGE_INV_COMPONENT_ID := 'Y';
1152 l_create_audit_rec.OLD_INV_COMPONENT_ID := l_audit_rec.OLD_INV_COMPONENT_ID ;
1153 l_create_audit_rec.INV_COMPONENT_ID := l_audit_rec.INV_COMPONENT_ID ;
1154 l_ici_update := 1;
1155 elsif NVL(l_audit_rec.CHANGE_INV_COMPONENT_ID, 'N') = 'N' then
1156 l_create_audit_rec.CHANGE_INV_COMPONENT_ID := 'N';
1157 if l_ici_update >0 then
1158 l_create_audit_rec.INV_COMPONENT_ID := l_create_audit_rec.OLD_INV_COMPONENT_ID ;
1159 l_create_audit_rec.OLD_INV_COMPONENT_ID := l_audit_rec.OLD_INV_COMPONENT_ID ;
1160 l_ici_update := 0;
1161 else
1162 l_create_audit_rec.OLD_INV_COMPONENT_ID := l_create_audit_rec.INV_COMPONENT_ID ;
1163 end if;
1164 end if;
1165
1166 if l_audit_rec.CHANGE_INV_COMPONENT_VERSION = 'Y' then
1167 l_create_audit_rec.CHANGE_INV_COMPONENT_VERSION := 'Y';
1168 l_create_audit_rec.OLD_INV_COMPONENT_VERSION := l_audit_rec.OLD_INV_COMPONENT_VERSION ;
1169 l_create_audit_rec.INV_COMPONENT_VERSION := l_audit_rec.INV_COMPONENT_VERSION ;
1170 l_icv_update := 1;
1171 elsif NVL(l_audit_rec.CHANGE_INV_COMPONENT_VERSION, 'N') = 'N' then
1172 l_create_audit_rec.CHANGE_INV_COMPONENT_VERSION := 'N';
1173 if l_icv_update >0 then
1174 l_create_audit_rec.INV_COMPONENT_VERSION := l_create_audit_rec.OLD_INV_COMPONENT_VERSION ;
1175 l_create_audit_rec.INV_COMPONENT_VERSION := l_audit_rec.OLD_INV_COMPONENT_VERSION ;
1176 l_icv_update := 0;
1177 else
1178 l_create_audit_rec.OLD_INV_COMPONENT_VERSION := l_create_audit_rec.INV_COMPONENT_VERSION ;
1179 end if;
1180 end if;
1181
1182 if l_audit_rec.CHANGE_INV_SUBCOMPONENT_ID = 'Y' then
1183 l_create_audit_rec.CHANGE_INV_SUBCOMPONENT_ID := 'Y';
1184 l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID := l_audit_rec.OLD_INV_SUBCOMPONENT_ID ;
1185 l_create_audit_rec.INV_SUBCOMPONENT_ID := l_audit_rec.INV_SUBCOMPONENT_ID ;
1186 l_isc_update := 1;
1187 elsif NVL(l_audit_rec.CHANGE_INV_SUBCOMPONENT_ID, 'N') = 'N' then
1188 l_create_audit_rec.CHANGE_INV_SUBCOMPONENT_ID := 'N';
1189 if l_isc_update >0 then
1190 l_create_audit_rec.INV_SUBCOMPONENT_ID := l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID ;
1191 l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID := l_audit_rec.OLD_INV_SUBCOMPONENT_ID ;
1192 l_isc_update := 0;
1193 else
1194 l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID := l_create_audit_rec.INV_SUBCOMPONENT_ID ;
1195 end if;
1196 end if;
1197
1198 if l_audit_rec.CHANGE_INV_SUBCOMP_VERSION = 'Y' then
1199 l_create_audit_rec.CHANGE_INV_SUBCOMP_VERSION := 'Y';
1200 l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION := l_audit_rec.OLD_INV_SUBCOMPONENT_VERSION ;
1201 l_create_audit_rec.INV_SUBCOMPONENT_VERSION := l_audit_rec.INV_SUBCOMPONENT_VERSION ;
1202 l_isv_update := 1;
1203 elsif NVL(l_audit_rec.CHANGE_INV_SUBCOMP_VERSION, 'N') = 'N' then
1204 l_create_audit_rec.CHANGE_INV_SUBCOMP_VERSION := 'N';
1205 if l_isv_update >0 then
1206 l_create_audit_rec.INV_SUBCOMPONENT_VERSION := l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION ;
1207 l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION := l_audit_rec.OLD_INV_SUBCOMPONENT_VERSION ;
1208 l_isv_update := 0;
1209 else
1210 l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION := l_create_audit_rec.INV_SUBCOMPONENT_VERSION ;
1211 end if;
1212 end if;
1213
1214 if l_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG = 'Y' then
1215 l_create_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG := 'Y';
1216 l_create_audit_rec.OLD_INV_PLATFORM_ORG_ID := l_audit_rec.OLD_INV_PLATFORM_ORG_ID ;
1217 l_create_audit_rec.INV_PLATFORM_ORG_ID := l_audit_rec.INV_PLATFORM_ORG_ID ;
1218 l_por_update := 1;
1219 elsif NVL(l_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG, 'N') = 'N' then
1220 l_create_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG := 'N';
1221 if l_por_update >0 then
1225 else
1222 l_create_audit_rec.INV_PLATFORM_ORG_ID := l_create_audit_rec.OLD_INV_PLATFORM_ORG_ID ;
1223 l_create_audit_rec.OLD_INV_PLATFORM_ORG_ID := l_audit_rec.OLD_INV_PLATFORM_ORG_ID ;
1224 l_por_update := 0;
1226 l_create_audit_rec.OLD_INV_PLATFORM_ORG_ID := l_create_audit_rec.INV_PLATFORM_ORG_ID ;
1227 end if;
1228 end if;
1229
1230 if l_audit_rec.CHANGE_COMP_VER_FLAG = 'Y' then
1231 l_create_audit_rec.CHANGE_COMP_VER_FLAG := 'Y';
1232 l_create_audit_rec.OLD_COMPONENT_VERSION := l_audit_rec.OLD_COMPONENT_VERSION ;
1233 l_create_audit_rec.COMPONENT_VERSION := l_audit_rec.COMPONENT_VERSION ;
1234 l_cov_update := 1;
1235 elsif NVL(l_audit_rec.CHANGE_COMP_VER_FLAG, 'N') = 'N' then
1236 l_create_audit_rec.CHANGE_COMP_VER_FLAG := 'N';
1237 if l_cov_update >0 then
1238 l_create_audit_rec.COMPONENT_VERSION := l_create_audit_rec.OLD_COMPONENT_VERSION ;
1239 l_create_audit_rec.OLD_COMPONENT_VERSION := l_audit_rec.OLD_COMPONENT_VERSION ;
1240 l_cov_update := 0;
1241 else
1242 l_create_audit_rec.OLD_COMPONENT_VERSION := l_create_audit_rec.COMPONENT_VERSION ;
1243 end if;
1244 end if;
1245
1246 if l_audit_rec.CHANGE_SUBCOMP_VER_FLAG = 'Y' then
1247 l_create_audit_rec.CHANGE_SUBCOMP_VER_FLAG := 'Y';
1248 l_create_audit_rec.OLD_SUBCOMPONENT_VERSION := l_audit_rec.OLD_SUBCOMPONENT_VERSION ;
1249 l_create_audit_rec.SUBCOMPONENT_VERSION := l_audit_rec.SUBCOMPONENT_VERSION ;
1250 l_scv_update := 1;
1251 elsif NVL(l_audit_rec.CHANGE_SUBCOMP_VER_FLAG, 'N') = 'N' then
1252 l_create_audit_rec.CHANGE_SUBCOMP_VER_FLAG := 'N';
1253 if l_scv_update >0 then
1254 l_create_audit_rec.SUBCOMPONENT_VERSION := l_create_audit_rec.OLD_SUBCOMPONENT_VERSION ;
1255 l_create_audit_rec.OLD_SUBCOMPONENT_VERSION := l_audit_rec.OLD_SUBCOMPONENT_VERSION ;
1256 l_scv_update := 0;
1257 else
1258 l_create_audit_rec.OLD_SUBCOMPONENT_VERSION := l_create_audit_rec.SUBCOMPONENT_VERSION ;
1259 end if;
1260 end if;
1261
1262 if l_audit_rec.CHANGE_PRODUCT_REVISION_FLAG = 'Y' then
1263 l_create_audit_rec.CHANGE_PRODUCT_REVISION_FLAG := 'Y';
1264 l_create_audit_rec.OLD_PRODUCT_REVISION := l_audit_rec.OLD_PRODUCT_REVISION ;
1265 l_create_audit_rec.PRODUCT_REVISION := l_audit_rec.PRODUCT_REVISION ;
1266 l_prv_update := 1;
1267 elsif NVL(l_audit_rec.CHANGE_PRODUCT_REVISION_FLAG, 'N') = 'N' then
1268 l_create_audit_rec.CHANGE_PRODUCT_REVISION_FLAG := 'N';
1269 if l_prv_update >0 then
1270 l_create_audit_rec.PRODUCT_REVISION := l_create_audit_rec.OLD_PRODUCT_REVISION ;
1271 l_create_audit_rec.OLD_PRODUCT_REVISION := l_audit_rec.OLD_PRODUCT_REVISION ;
1272 l_prv_update := 0;
1273 else
1274 l_create_audit_rec.OLD_PRODUCT_REVISION := l_create_audit_rec.PRODUCT_REVISION ;
1275 end if;
1276 end if;
1277
1278 if l_audit_rec.CHANGE_INVENTORY_ITEM_FLAG = 'Y' then
1279 l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG := 'Y';
1280 l_create_audit_rec.OLD_INVENTORY_ITEM_ID := l_audit_rec.OLD_INVENTORY_ITEM_ID ;
1281 l_create_audit_rec.INVENTORY_ITEM_ID := l_audit_rec.INVENTORY_ITEM_ID ;
1282 l_inv_update := 1;
1283 elsif NVL(l_audit_rec.CHANGE_INVENTORY_ITEM_FLAG,'N') = 'N' then
1284 l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG := 'N';
1285 if l_inv_update >0 then
1286 l_create_audit_rec.INVENTORY_ITEM_ID := l_create_audit_rec.OLD_INVENTORY_ITEM_ID ;
1287 l_create_audit_rec.OLD_INVENTORY_ITEM_ID := l_audit_rec.OLD_INVENTORY_ITEM_ID ;
1288 l_inv_update := 0;
1289 else
1290 l_create_audit_rec.OLD_INVENTORY_ITEM_ID := l_create_audit_rec.INVENTORY_ITEM_ID ;
1291 end if;
1292 end if;
1293
1294 if l_audit_rec.CHANGE_INV_ORGANIZATION_FLAG = 'Y' then
1295 l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG := 'Y';
1296 l_create_audit_rec.OLD_INV_ORGANIZATION_ID := l_audit_rec.OLD_INV_ORGANIZATION_ID ;
1297 l_create_audit_rec.INV_ORGANIZATION_ID := l_audit_rec.INV_ORGANIZATION_ID ;
1298 l_ino_update := 1;
1299 elsif NVL(l_audit_rec.CHANGE_INV_ORGANIZATION_FLAG, 'N') = 'N' then
1300 l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG := 'N';
1301 if l_ino_update >0 then
1302 l_create_audit_rec.INV_ORGANIZATION_ID := l_create_audit_rec.OLD_INV_ORGANIZATION_ID ;
1303 l_create_audit_rec.OLD_INV_ORGANIZATION_ID := l_audit_rec.OLD_INV_ORGANIZATION_ID ;
1304 l_ino_update := 0;
1305 else
1306 l_create_audit_rec.OLD_INV_ORGANIZATION_ID := l_create_audit_rec.INV_ORGANIZATION_ID ;
1307 end if;
1308 end if;
1309
1310 if l_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG = 'Y' then
1311 l_create_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG := 'Y';
1312 l_create_audit_rec.OLD_PRIMARY_CONTACT_ID := l_audit_rec.OLD_PRIMARY_CONTACT_ID ;
1313 l_create_audit_rec.PRIMARY_CONTACT_ID := l_audit_rec.PRIMARY_CONTACT_ID ;
1314 l_pci_update := 1;
1315 elsif NVL(l_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG, 'N') = 'N' then
1316 l_create_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG := 'N';
1317 if l_pci_update >0 then
1318 l_create_audit_rec.PRIMARY_CONTACT_ID := l_create_audit_rec.OLD_PRIMARY_CONTACT_ID ;
1319 l_create_audit_rec.OLD_PRIMARY_CONTACT_ID := l_audit_rec.OLD_PRIMARY_CONTACT_ID ;
1320 l_pci_update := 0;
1321 else
1322 l_create_audit_rec.OLD_PRIMARY_CONTACT_ID := l_create_audit_rec.PRIMARY_CONTACT_ID ;
1323 end if;
1324 end if;
1325 end loop;
1326 close c_sr_audit;
1327
1328 -- Added code to fetch the close_flag for populating the Close_date for
1329 -- the record having the upgrade_flag_for_create as 'Y'.
1330 BEGIN
1331 OPEN c_close_flag(l_create_audit_rec.INCIDENT_STATUS_ID);
1332 FETCH c_close_flag INTO l_create_flag_close_date;
1333 CLOSE c_close_flag;
1334 IF (NVL(l_create_flag_close_date,'N') = 'N') THEN
1335 l_create_audit_rec.CLOSE_DATE := NULL;
1336 l_create_audit_rec.STATUS_FLAG := 'O';
1337 ELSE
1338 l_create_audit_rec.CLOSE_DATE := l_create_audit_rec.CREATION_DATE;
1339 l_create_audit_rec.STATUS_FLAG := 'C';
1340 END IF;
1341 OPEN c_close_flag(l_create_audit_rec.OLD_INCIDENT_STATUS_ID);
1342 FETCH c_close_flag INTO l_create_flag_close_date;
1343 CLOSE c_close_flag;
1344 IF (NVL(l_create_flag_close_date,'N') = 'N') THEN
1345 l_create_audit_rec.OLD_CLOSE_DATE := NULL;
1349 l_create_audit_rec.OLD_STATUS_FLAG := 'C';
1346 l_create_audit_rec.OLD_STATUS_FLAG := 'O';
1347 ELSE
1348 l_create_audit_rec.OLD_CLOSE_DATE := l_create_audit_rec.CREATION_DATE;
1350 END IF;
1354 l_create_audit_rec.OWNER_ASSIGNED_TIME := l_create_audit_rec.creation_date;
1351 -- Added this to populate the G_MISS_DATE in some records
1352 IF (l_create_audit_rec.OWNER_ASSIGNED_TIME > SYSDATE OR
1353 l_create_audit_rec.OWNER_ASSIGNED_TIME = FND_API.G_MISS_DATE) THEN
1355 END IF;
1356
1357 IF (l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME > SYSDATE OR
1358 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME = FND_API.G_MISS_DATE) THEN
1359 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := l_create_audit_rec.creation_date;
1360 END IF;
1361
1362 if (l_create_audit_rec.CLOSE_DATE > sysdate) then
1363 l_create_audit_rec.CLOSE_DATE := l_create_audit_rec.creation_date;
1364 end if;
1365
1366 Update cs_incidents_audit_b
1367 set INCIDENT_STATUS_ID = decode(l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG,'Y',l_create_audit_rec.OLD_INCIDENT_STATUS_ID,l_create_audit_rec.INCIDENT_STATUS_ID),
1368 CHANGE_INCIDENT_STATUS_FLAG = decode(l_create_audit_rec.INCIDENT_STATUS_ID,NULL,'N','Y'),
1369 INCIDENT_TYPE_ID = decode(l_create_audit_rec.CHANGE_INCIDENT_TYPE_FLAG,'Y',l_create_audit_rec.OLD_INCIDENT_TYPE_ID,l_create_audit_rec.INCIDENT_TYPE_ID),
1370 CHANGE_INCIDENT_TYPE_FLAG = decode(l_create_audit_rec.INCIDENT_TYPE_ID,NULL,'N','Y'),
1371 INCIDENT_URGENCY_ID = decode(l_create_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG,'Y',l_create_audit_rec.OLD_INCIDENT_URGENCY_ID,l_create_audit_rec.INCIDENT_URGENCY_ID),
1372 CHANGE_INCIDENT_URGENCY_FLAG = decode(l_create_audit_rec.INCIDENT_URGENCY_ID,NULL,'N','Y'),
1373 INCIDENT_SEVERITY_ID = decode(l_create_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG,'Y',l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID,l_create_audit_rec.INCIDENT_SEVERITY_ID),
1374 CHANGE_INCIDENT_SEVERITY_FLAG = decode(l_create_audit_rec.INCIDENT_SEVERITY_ID,NULL,'N','Y'),
1375 RESPONSIBLE_GROUP_ID = decode(l_create_audit_rec.CHANGE_RESPONSIBLE_GROUP_FLAG,'Y',l_create_audit_rec.OLD_RESPONSIBLE_GROUP_ID,l_create_audit_rec.RESPONSIBLE_GROUP_ID),
1376 CHANGE_RESPONSIBLE_GROUP_FLAG = decode(l_create_audit_rec.RESPONSIBLE_GROUP_ID,NULL,'N','Y'),
1377 INCIDENT_OWNER_ID = decode(l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG,'Y',l_create_audit_rec.OLD_INCIDENT_OWNER_ID,l_create_audit_rec.INCIDENT_OWNER_ID),
1378 CHANGE_INCIDENT_OWNER_FLAG = decode(decode(l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG,'Y',l_create_audit_rec.OLD_INCIDENT_OWNER_ID,l_create_audit_rec.INCIDENT_OWNER_ID),NULL,'N','Y'),
1379 EXPECTED_RESOLUTION_DATE= decode(l_create_audit_rec.CHANGE_RESOLUTION_FLAG,'Y',l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE,l_create_audit_rec.EXPECTED_RESOLUTION_DATE),
1380 CHANGE_RESOLUTION_FLAG = decode(decode(l_create_audit_rec.CHANGE_RESOLUTION_FLAG,'Y',l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE,l_create_audit_rec.EXPECTED_RESOLUTION_DATE),NULL,'N','Y'),
1381 GROUP_ID = decode(l_create_audit_rec.CHANGE_GROUP_FLAG,'Y',l_create_audit_rec.OLD_GROUP_ID,l_create_audit_rec.GROUP_ID),
1382 CHANGE_GROUP_FLAG = decode(decode(l_create_audit_rec.CHANGE_GROUP_FLAG,'Y',l_create_audit_rec.OLD_GROUP_ID,l_create_audit_rec.GROUP_ID),NULL,'N','Y'),
1383 OBLIGATION_DATE = decode(l_create_audit_rec.CHANGE_OBLIGATION_FLAG,'Y',l_create_audit_rec.OLD_OBLIGATION_DATE,l_create_audit_rec.OBLIGATION_DATE) ,
1384 CHANGE_OBLIGATION_FLAG = decode(decode(l_create_audit_rec.CHANGE_OBLIGATION_FLAG,'Y',l_create_audit_rec.OLD_OBLIGATION_DATE,l_create_audit_rec.OBLIGATION_DATE),NULL,'N','Y'),
1385 SITE_ID = decode(l_create_audit_rec.CHANGE_SITE_FLAG,'Y',l_create_audit_rec.OLD_SITE_ID,l_create_audit_rec.SITE_ID),
1386 CHANGE_SITE_FLAG = decode(decode(l_create_audit_rec.CHANGE_SITE_FLAG,'Y',l_create_audit_rec.OLD_SITE_ID,l_create_audit_rec.SITE_ID),NULL,'N','Y'),
1387 BILL_TO_CONTACT_ID = decode(l_create_audit_rec.CHANGE_BILL_TO_FLAG,'Y',l_create_audit_rec.OLD_BILL_TO_CONTACT_ID,l_create_audit_rec.BILL_TO_CONTACT_ID) ,
1388 CHANGE_BILL_TO_FLAG = decode(decode(l_create_audit_rec.CHANGE_BILL_TO_FLAG,'Y',l_create_audit_rec.OLD_BILL_TO_CONTACT_ID,l_create_audit_rec.BILL_TO_CONTACT_ID),NULL,'N','Y'),
1389 SHIP_TO_CONTACT_ID = decode(l_create_audit_rec.CHANGE_SHIP_TO_FLAG,'Y',l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID,l_create_audit_rec.SHIP_TO_CONTACT_ID),
1390 CHANGE_SHIP_TO_FLAG = decode(decode(l_create_audit_rec.CHANGE_SHIP_TO_FLAG,'Y',l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID,l_create_audit_rec.SHIP_TO_CONTACT_ID),NULL,'N','Y'),
1391 INCIDENT_DATE = decode(l_create_audit_rec.CHANGE_INCIDENT_DATE_FLAG,'Y',l_create_audit_rec.OLD_INCIDENT_DATE,l_create_audit_rec.INCIDENT_DATE),
1392 CHANGE_INCIDENT_DATE_FLAG = decode(decode(l_create_audit_rec.CHANGE_INCIDENT_DATE_FLAG,'Y',l_create_audit_rec.OLD_INCIDENT_DATE,l_create_audit_rec.INCIDENT_DATE),NULL,'N','Y'),
1393 CLOSE_DATE = decode(l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG,'Y',l_create_audit_rec.OLD_CLOSE_DATE,l_create_audit_rec.CLOSE_DATE),
1394 CHANGE_CLOSE_DATE_FLAG = decode(decode(l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG,'Y',l_create_audit_rec.OLD_CLOSE_DATE,l_create_audit_rec.CLOSE_DATE),NULL,'N','Y'),
1395 CUSTOMER_PRODUCT_ID = decode(l_create_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG,'Y',l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID,l_create_audit_rec.CUSTOMER_PRODUCT_ID),
1396 CHANGE_CUSTOMER_PRODUCT_FLAG = decode(decode(l_create_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG,'Y',l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID,l_create_audit_rec.CUSTOMER_PRODUCT_ID),NULL,'N','Y'),
1397 PLATFORM_ID = decode(l_create_audit_rec.CHANGE_PLATFORM_ID_FLAG,'Y',l_create_audit_rec.OLD_PLATFORM_ID,l_create_audit_rec.PLATFORM_ID),
1398 CHANGE_PLATFORM_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_PLATFORM_ID_FLAG,'Y',l_create_audit_rec.OLD_PLATFORM_ID,l_create_audit_rec.PLATFORM_ID),NULL,'N','Y'),
1399 PLATFORM_VERSION_ID = decode(l_create_audit_rec.CHANGE_PLAT_VER_ID_FLAG,'Y',l_create_audit_rec.OLD_PLATFORM_VERSION_ID,l_create_audit_rec.PLATFORM_VERSION_ID),
1400 CHANGE_PLAT_VER_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_PLAT_VER_ID_FLAG,'Y',l_create_audit_rec.OLD_PLATFORM_VERSION_ID,l_create_audit_rec.PLATFORM_VERSION_ID),NULL,'N','Y'),
1401 CP_COMPONENT_ID = decode(l_create_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_COMPONENT_ID,l_create_audit_rec.CP_COMPONENT_ID),
1402 CHANGE_CP_COMPONENT_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_COMPONENT_ID,l_create_audit_rec.CP_COMPONENT_ID),NULL,'N','Y'),
1403 CP_COMPONENT_VERSION_ID = decode(l_create_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID,l_create_audit_rec.CP_COMPONENT_VERSION_ID),
1404 CHANGE_CP_COMP_VER_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID,l_create_audit_rec.CP_COMPONENT_VERSION_ID),NULL,'N','Y'),
1405 CP_SUBCOMPONENT_ID = decode(l_create_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID,l_create_audit_rec.CP_SUBCOMPONENT_ID),
1406 CHANGE_CP_SUBCOMPONENT_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID,l_create_audit_rec.CP_SUBCOMPONENT_ID),NULL,'N','Y'),
1407 CP_SUBCOMPONENT_VERSION_ID = decode(l_create_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID,l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID),
1408 CHANGE_CP_SUBCOMP_VER_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID,l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID),NULL,'N','Y'),
1409 LANGUAGE_ID = decode(l_create_audit_rec.CHANGE_LANGUAGE_ID_FLAG,'Y',l_create_audit_rec.OLD_LANGUAGE_ID,l_create_audit_rec.LANGUAGE_ID),
1410 CHANGE_LANGUAGE_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_LANGUAGE_ID_FLAG,'Y',l_create_audit_rec.OLD_LANGUAGE_ID,l_create_audit_rec.LANGUAGE_ID),NULL,'N','Y'),
1411 TERRITORY_ID = decode(l_create_audit_rec.CHANGE_TERRITORY_ID_FLAG,'Y',l_create_audit_rec.OLD_TERRITORY_ID,l_create_audit_rec.TERRITORY_ID),
1412 CHANGE_TERRITORY_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_TERRITORY_ID_FLAG,'Y',l_create_audit_rec.OLD_TERRITORY_ID,l_create_audit_rec.TERRITORY_ID),NULL,'N','Y'),
1413 CP_REVISION_ID = decode(l_create_audit_rec.CHANGE_CP_REVISION_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_REVISION_ID,l_create_audit_rec.CP_REVISION_ID),
1414 CHANGE_CP_REVISION_ID_FLAG = decode(decode(l_create_audit_rec.CHANGE_CP_REVISION_ID_FLAG,'Y',l_create_audit_rec.OLD_CP_REVISION_ID,l_create_audit_rec.CP_REVISION_ID),NULL,'N','Y'),
1415 INV_ITEM_REVISION = decode(l_create_audit_rec.CHANGE_INV_ITEM_REVISION,'Y',l_create_audit_rec.OLD_INV_ITEM_REVISION,l_create_audit_rec.INV_ITEM_REVISION),
1416 CHANGE_INV_ITEM_REVISION = decode(decode(l_create_audit_rec.CHANGE_INV_ITEM_REVISION,'Y',l_create_audit_rec.OLD_INV_ITEM_REVISION,l_create_audit_rec.INV_ITEM_REVISION),NULL,'N','Y'),
1417 INV_COMPONENT_ID = decode(l_create_audit_rec.CHANGE_INV_COMPONENT_ID,'Y',l_create_audit_rec.OLD_INV_COMPONENT_ID,l_create_audit_rec.INV_COMPONENT_ID),
1418 CHANGE_INV_COMPONENT_ID = decode(decode(l_create_audit_rec.CHANGE_INV_COMPONENT_ID,'Y',l_create_audit_rec.OLD_INV_COMPONENT_ID,l_create_audit_rec.INV_COMPONENT_ID),NULL,'N','Y'),
1419 INV_COMPONENT_VERSION = decode(l_create_audit_rec.CHANGE_INV_COMPONENT_VERSION,'Y',l_create_audit_rec.OLD_INV_COMPONENT_VERSION,l_create_audit_rec.INV_COMPONENT_VERSION),
1420 CHANGE_INV_COMPONENT_VERSION = decode(decode(l_create_audit_rec.CHANGE_INV_COMPONENT_VERSION,'Y',l_create_audit_rec.OLD_INV_COMPONENT_VERSION,l_create_audit_rec.INV_COMPONENT_VERSION),NULL,'N','Y'),
1421 INV_SUBCOMPONENT_ID = decode(l_create_audit_rec.CHANGE_INV_SUBCOMPONENT_ID,'Y',l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID,l_create_audit_rec.INV_SUBCOMPONENT_ID),
1422 CHANGE_INV_SUBCOMPONENT_ID = decode(decode(l_create_audit_rec.CHANGE_INV_SUBCOMPONENT_ID,'Y',l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID,l_create_audit_rec.INV_SUBCOMPONENT_ID),NULL,'N','Y'),
1423 INV_SUBCOMPONENT_VERSION = decode(l_create_audit_rec.CHANGE_INV_SUBCOMP_VERSION,'Y',l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION,l_create_audit_rec.INV_SUBCOMPONENT_VERSION),
1424 CHANGE_INV_SUBCOMP_VERSION = decode(decode(l_create_audit_rec.CHANGE_INV_SUBCOMP_VERSION,'Y',l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION,l_create_audit_rec.INV_SUBCOMPONENT_VERSION),NULL,'N','Y'),
1425 RESOURCE_TYPE = decode(l_create_audit_rec.CHANGE_RESOURCE_TYPE_FLAG,'Y',l_create_audit_rec.OLD_RESOURCE_TYPE,l_create_audit_rec.RESOURCE_TYPE),
1426 CHANGE_RESOURCE_TYPE_FLAG = decode(decode(l_create_audit_rec.CHANGE_RESOURCE_TYPE_FLAG,'Y',l_create_audit_rec.OLD_RESOURCE_TYPE,l_create_audit_rec.RESOURCE_TYPE),NULL,'N','Y'),
1427 GROUP_TYPE = decode(l_create_audit_rec.CHANGE_GROUP_TYPE_FLAG,'Y',l_create_audit_rec.OLD_GROUP_TYPE,l_create_audit_rec.GROUP_TYPE),
1428 CHANGE_GROUP_TYPE_FLAG = decode(decode(l_create_audit_rec.CHANGE_GROUP_TYPE_FLAG,'Y',l_create_audit_rec.OLD_GROUP_TYPE,l_create_audit_rec.GROUP_TYPE),NULL,'N','Y'),
1429 OWNER_ASSIGNED_TIME= decode(l_create_audit_rec.change_assigned_time_flag,'Y',l_create_audit_rec.creation_date,l_create_audit_rec.owner_assigned_time),
1430 CHANGE_ASSIGNED_TIME_FLAG = decode(decode(l_create_audit_rec.change_assigned_time_flag,'Y',l_create_audit_rec.creation_date,l_create_audit_rec.owner_assigned_time),NULL,'N','Y'),
1431 INVENTORY_ITEM_ID = decode(l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG,'Y',l_create_audit_rec.OLD_INVENTORY_ITEM_ID,l_create_audit_rec.INVENTORY_ITEM_ID) ,
1432 CHANGE_INVENTORY_ITEM_FLAG = decode(decode(l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG,'Y',l_create_audit_rec.OLD_INVENTORY_ITEM_ID,l_create_audit_rec.INVENTORY_ITEM_ID),NULL,'N','Y'),
1433 INV_ORGANIZATION_ID = decode(l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG,'Y',l_create_audit_rec.OLD_INV_ORGANIZATION_ID,l_create_audit_rec.INV_ORGANIZATION_ID),
1434 CHANGE_INV_ORGANIZATION_FLAG = decode(decode(l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG,'Y',l_create_audit_rec.OLD_INV_ORGANIZATION_ID,l_create_audit_rec.INV_ORGANIZATION_ID),NULL,'N','Y'),
1435 STATUS_FLAG = decode(l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG,'Y',l_create_audit_rec.OLD_STATUS_FLAG,l_create_audit_rec.STATUS_FLAG),
1436 CHANGE_STATUS_FLAG = decode(decode(l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG,'Y',l_create_audit_rec.OLD_STATUS_FLAG,l_create_audit_rec.STATUS_FLAG),NULL,'N','Y'),
1437 UPGRADE_FLAG_FOR_CREATE = 'X',
1438 updated_entity_code = 'SR_HEADER'
1439 WHERE incident_id = l_create_audit_rec.incident_id
1440 AND upgrade_flag_for_create = 'Y';
1441
1442 END;
1443
1444 loop_cnt := 0;
1445 open c_sr_audit_asc(l_request_id);
1446 LOOP
1447 loop_cnt := loop_cnt +1;
1448 FETCH c_sr_audit_asc into l_audit_rec;
1449 EXIT WHEN c_sr_audit_asc%NOTFOUND;
1450 l_create_record := 'N';
1451 --jen1
1452 if (loop_cnt =1 and
1453 l_audit_rec.CHANGE_INCIDENT_STATUS_FLAG ='Y' and
1454 l_audit_rec.INCIDENT_STATUS_ID is not null and
1455 l_audit_rec.OLD_INCIDENT_STATUS_ID is null and
1456 l_audit_rec.CHANGE_INCIDENT_TYPE_FLAG = 'Y' and
1457 l_audit_rec.INCIDENT_TYPE_ID is not null and
1458 l_audit_rec.OLD_INCIDENT_TYPE_ID is null and
1459 l_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG = 'Y' and
1460 l_audit_rec.INCIDENT_SEVERITY_ID is not null and
1461 l_audit_rec.OLD_INCIDENT_SEVERITY_ID is null) then
1462 l_create_record := 'Y';
1463 end if;
1464 --jen1
1465 if l_audit_rec.CHANGE_INCIDENT_STATUS_FLAG = 'Y' then
1466 l_create_audit_rec.INCIDENT_STATUS_ID := l_audit_rec.INCIDENT_STATUS_ID ;
1467 l_create_audit_rec.OLD_INCIDENT_STATUS_ID := l_audit_rec.OLD_INCIDENT_STATUS_ID ;
1468 l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG := 'Y';
1469 if (l_audit_rec.old_incident_status_id is null and
1470 loop_cnt > 1) then
1471 l_create_audit_rec.OLD_INCIDENT_STATUS_ID := l_create_audit_rec.INCIDENT_STATUS_ID;
1472 l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG := 'N';
1473 end if;
1474 OPEN c_close_flag(l_audit_rec.INCIDENT_STATUS_ID);
1475 FETCH c_close_flag INTO l_new_close_flag;
1476 CLOSE c_close_flag;
1477 OPEN c_close_flag(l_audit_rec.OLD_INCIDENT_STATUS_ID);
1478 FETCH c_close_flag INTO l_old_close_flag;
1479 CLOSE c_close_flag;
1480
1481 If (nvl(l_new_close_flag,'N') = 'N') THEN
1482 If (nvl(l_old_close_flag,'N') = 'N') THEN
1483 l_create_audit_rec.CHANGE_CLOSE_DATE_FLAG := 'N';
1484 l_create_audit_rec.OLD_CLOSE_DATE := l_create_audit_rec.CLOSE_DATE;
1485 l_create_audit_rec.CLOSE_DATE := l_create_audit_rec.CLOSE_DATE;
1486 l_create_audit_rec.CHANGE_STATUS_FLAG := 'N';
1487 l_create_audit_rec.STATUS_FLAG := 'O';
1488 l_create_audit_rec.OLD_STATUS_FLAG := 'O';
1489 Else
1490 l_create_audit_rec.CHANGE_CLOSE_DATE_FLAG := 'Y';
1491 l_create_audit_rec.OLD_CLOSE_DATE := l_create_audit_rec.CLOSE_DATE;
1492 l_create_audit_rec.CLOSE_DATE := NULL;
1493 l_create_audit_rec.CHANGE_STATUS_FLAG := 'Y';
1494 l_create_audit_rec.STATUS_FLAG := 'O';
1495 l_create_audit_rec.OLD_STATUS_FLAG := 'C';
1496 End If;
1497 Else
1498 IF (nvl(l_old_close_flag, 'N') = 'N') THEN
1499 l_create_audit_rec.CHANGE_CLOSE_DATE_FLAG := 'Y';
1500 l_create_audit_rec.OLD_CLOSE_DATE := l_create_audit_rec.OLD_CLOSE_DATE;
1501 l_create_audit_rec.CLOSE_DATE := l_audit_rec.creation_date;
1502 l_create_audit_rec.CHANGE_STATUS_FLAG := 'Y';
1503 l_create_audit_rec.STATUS_FLAG := 'C';
1504 l_create_audit_rec.OLD_STATUS_FLAG := 'O';
1505 ELSE
1506 l_create_audit_rec.CHANGE_CLOSE_DATE_FLAG := 'Y';
1507 l_create_audit_rec.OLD_CLOSE_DATE := l_create_audit_rec.OLD_CLOSE_DATE;
1508 l_create_audit_rec.CLOSE_DATE := l_audit_rec.creation_date;
1509 l_create_audit_rec.CHANGE_STATUS_FLAG := 'Y';
1510 l_create_audit_rec.STATUS_FLAG := 'C';
1511 l_create_audit_rec.OLD_STATUS_FLAG := 'C';
1512 END IF;
1513 End IF;
1514 ELSE
1515 l_create_audit_rec.OLD_INCIDENT_STATUS_ID := l_create_audit_rec.INCIDENT_STATUS_ID ;
1516 l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG := 'N';
1520 If (nvl(l_new_close_flag,'N') = 'N') THEN
1517 OPEN c_close_flag(l_create_audit_rec.OLD_INCIDENT_STATUS_ID);
1518 FETCH c_close_flag INTO l_new_close_flag;
1519 CLOSE c_close_flag;
1521 l_create_audit_rec.CHANGE_CLOSE_DATE_FLAG := 'N';
1522 l_create_audit_rec.OLD_CLOSE_DATE := l_audit_rec.CLOSE_DATE;
1523 l_create_audit_rec.CLOSE_DATE := l_audit_rec.CLOSE_DATE;
1524 l_create_audit_rec.CHANGE_STATUS_FLAG := 'N';
1525 l_create_audit_rec.STATUS_FLAG := 'O';
1526 l_create_audit_rec.OLD_STATUS_FLAG := 'O';
1527 Else
1528 l_create_audit_rec.CHANGE_CLOSE_DATE_FLAG := 'N';
1529 l_create_audit_rec.OLD_CLOSE_DATE := l_audit_rec.CLOSE_DATE;
1530 l_create_audit_rec.CLOSE_DATE := l_audit_rec.CLOSE_DATE;
1531 l_create_audit_rec.CHANGE_STATUS_FLAG := 'N';
1532 l_create_audit_rec.STATUS_FLAG := 'C';
1533 l_create_audit_rec.OLD_STATUS_FLAG := 'C';
1534 End If;
1535 end if;
1536
1537 -- Added code for populating the Close_Date and Status_Flag if the
1538 -- count is 1.
1539 IF (loop_cnt = 1 AND
1540 l_create_audit_rec.OLD_INCIDENT_STATUS_ID IS NULL) THEN
1541 OPEN c_close_flag(l_audit_rec.INCIDENT_STATUS_ID);
1542 FETCH c_close_flag INTO l_new_close_flag;
1543 CLOSE c_close_flag;
1544 IF (NVL(l_new_close_flag,'N') = 'N') THEN
1545 l_create_audit_rec.CHANGE_STATUS_FLAG := 'Y';
1546 l_create_audit_rec.STATUS_FLAG := 'O';
1547 l_create_audit_rec.OLD_STATUS_FLAG := NULL;
1548 ELSE
1549 l_create_audit_rec.CHANGE_STATUS_FLAG := 'Y';
1550 l_create_audit_rec.STATUS_FLAG := 'C';
1551 l_create_audit_rec.OLD_STATUS_FLAG := NULL;
1552 END IF;
1553 END IF;
1554
1555 --if loop_cnt > 1 then
1556
1557 if l_audit_rec.CHANGE_INCIDENT_TYPE_FLAG = 'Y' then
1558 l_create_audit_rec.OLD_INCIDENT_TYPE_ID := l_audit_rec.OLD_INCIDENT_TYPE_ID ;
1559 l_create_audit_rec.INCIDENT_TYPE_ID := l_audit_rec.INCIDENT_TYPE_ID;
1560 l_create_audit_rec.CHANGE_INCIDENT_TYPE_FLAG := 'Y';
1561 if (l_audit_rec.OLD_INCIDENT_TYPE_ID is null and
1562 loop_cnt >1) then
1563 l_create_audit_rec.OLD_INCIDENT_TYPE_ID := l_create_audit_rec.INCIDENT_TYPE_ID;
1564 l_create_audit_rec.CHANGE_INCIDENT_TYPE_FLAG := 'N';
1565 end if;
1566 else
1567 l_create_audit_rec.OLD_INCIDENT_TYPE_ID := l_create_audit_rec.INCIDENT_TYPE_ID ;
1568 l_create_audit_rec.CHANGE_INCIDENT_TYPE_FLAG := 'N';
1569 end if;
1570
1571 if l_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG = 'Y' then
1572 l_create_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG := 'Y';
1573 l_create_audit_rec.OLD_INCIDENT_URGENCY_ID := l_audit_rec.OLD_INCIDENT_URGENCY_ID ;
1574 l_create_audit_rec.INCIDENT_URGENCY_ID := l_audit_rec.INCIDENT_URGENCY_ID ;
1575 else
1576 l_create_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG := 'N';
1577 l_create_audit_rec.OLD_INCIDENT_URGENCY_ID := l_create_audit_rec.INCIDENT_URGENCY_ID ;
1578 end if;
1579
1580 if l_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG = 'Y' then
1581 l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID := l_audit_rec.OLD_INCIDENT_SEVERITY_ID;
1582 l_create_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG := 'Y';
1583 l_create_audit_rec.INCIDENT_SEVERITY_ID := l_audit_rec.INCIDENT_SEVERITY_ID ;
1584 if (l_audit_rec.OLD_INCIDENT_SEVERITY_ID is null and
1585 loop_cnt >1) then
1586 l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID := l_create_audit_rec.INCIDENT_SEVERITY_ID;
1587 l_create_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG := 'N';
1588 end if;
1589 else
1590 l_create_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG := 'N';
1591 l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID := l_create_audit_rec.INCIDENT_SEVERITY_ID ;
1592 end if;
1593
1594 if l_audit_rec.CHANGE_RESPONSIBLE_GROUP_FLAG = 'Y' then
1595 l_create_audit_rec.CHANGE_RESPONSIBLE_GROUP_FLAG := 'Y';
1596 l_create_audit_rec.OLD_RESPONSIBLE_GROUP_ID := l_audit_rec.OLD_RESPONSIBLE_GROUP_ID ;
1597 l_create_audit_rec.RESPONSIBLE_GROUP_ID := l_audit_rec.RESPONSIBLE_GROUP_ID ;
1598 end if;
1599
1600 if nvl(l_audit_rec.CHANGE_INCIDENT_OWNER_FLAG,'N') = 'Y' then
1601 l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG := 'Y';
1602 l_create_audit_rec.OLD_INCIDENT_OWNER_ID := l_audit_rec.OLD_INCIDENT_OWNER_ID ;
1603 l_create_audit_rec.INCIDENT_OWNER_ID := l_audit_rec.INCIDENT_OWNER_ID;
1604 l_create_audit_rec.OLD_RESOURCE_TYPE := l_audit_rec.OLD_RESOURCE_TYPE;
1608 l_create_audit_rec.OWNER_ASSIGNED_TIME := l_audit_rec.CREATION_DATE;
1605 l_create_audit_rec.RESOURCE_TYPE := l_audit_rec.RESOURCE_TYPE ;
1606 l_create_audit_rec.CHANGE_RESOURCE_TYPE_FLAG := 'Y';
1607 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := l_create_audit_rec.old_OWNER_ASSIGNED_TIME;
1609 l_create_audit_rec.CHANGE_ASSIGNED_TIME_FLAG := 'Y';
1610 if (l_create_record = 'Y' and
1611 l_audit_rec.OWNER_ASSIGNED_TIME is null and
1612 l_audit_rec.OLD_OWNER_ASSIGNED_TIME is null and
1613 l_create_audit_rec.OWNER_ASSIGNED_TIME is not null and
1614 l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG = 'Y') then
1615 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := null;
1616 end if;
1617 else
1618 l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG := 'N';
1619 l_create_audit_rec.OLD_INCIDENT_OWNER_ID := l_create_audit_rec.INCIDENT_OWNER_ID ;
1620 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := l_create_audit_rec.OWNER_ASSIGNED_TIME;
1621 l_create_audit_rec.OLD_RESOURCE_TYPE := l_create_audit_rec.RESOURCE_TYPE;
1622 l_create_audit_rec.CHANGE_RESOURCE_TYPE_FLAG := 'N';
1623 if (l_create_record = 'Y' --and
1624 --l_audit_rec.RESOURCE_TYPE is null and
1625 --l_audit_rec.OLD_RESOURCE_TYPE is null and
1626 --l_create_audit_rec.RESOURCE_TYPE is not null
1627 ) then
1628 l_create_audit_rec.OLD_RESOURCE_TYPE := null;
1629 l_create_audit_rec.CHANGE_RESOURCE_TYPE_FLAG := 'Y';
1630 end if;
1631 l_create_audit_rec.CHANGE_ASSIGNED_TIME_FLAG := 'N';
1632 if (l_create_record = 'Y' and
1633 l_audit_rec.INCIDENT_OWNER_ID is null and
1634 l_audit_rec.OLD_INCIDENT_OWNER_ID is null and
1635 l_create_audit_rec.INCIDENT_OWNER_ID is not null) then
1636 l_create_audit_rec.OLD_INCIDENT_OWNER_ID := null;
1637 l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG := 'Y';
1638 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := null;
1639 l_create_audit_rec.CHANGE_ASSIGNED_TIME_FLAG := 'Y';
1640 end if;
1641 end if;
1642 if l_audit_rec.CHANGE_RESOLUTION_FLAG = 'Y' then
1643 l_create_audit_rec.CHANGE_RESOLUTION_FLAG := 'Y';
1644 l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE := l_audit_rec.OLD_EXPECTED_RESOLUTION_DATE ;
1645 l_create_audit_rec.EXPECTED_RESOLUTION_DATE := l_audit_rec.EXPECTED_RESOLUTION_DATE ;
1646 else
1647 l_create_audit_rec.CHANGE_RESOLUTION_FLAG := 'N';
1648 l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE := l_create_audit_rec.EXPECTED_RESOLUTION_DATE;
1649 if (l_create_record = 'Y' and
1650 l_audit_rec.EXPECTED_RESOLUTION_DATE is null and
1651 l_audit_rec.OLD_EXPECTED_RESOLUTION_DATE is null and
1652 l_create_audit_rec.EXPECTED_RESOLUTION_DATE is not null) then
1653 l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE := null;
1654 l_create_audit_rec.CHANGE_RESOLUTION_FLAG := 'Y';
1655 end if;
1656 end if;
1657
1658 if nvl(l_audit_rec.CHANGE_GROUP_FLAG, 'N') = 'Y' then
1659 l_create_audit_rec.CHANGE_GROUP_FLAG := 'Y';
1660 l_create_audit_rec.OLD_GROUP_ID := l_audit_rec.OLD_GROUP_ID ;
1661 l_create_audit_rec.GROUP_ID := l_audit_rec.GROUP_ID ;
1662 l_create_audit_rec.CHANGE_GROUP_TYPE_FLAG := 'Y';
1663 l_create_audit_rec.OLD_GROUP_TYPE := l_audit_rec.OLD_GROUP_TYPE;
1664 l_create_audit_rec.GROUP_TYPE := l_audit_rec.GROUP_TYPE;
1665 if (l_create_record = 'Y' ) then
1666 l_create_audit_rec.OLD_GROUP_TYPE := NULL;
1667 end if;
1668 else
1669 l_create_audit_rec.CHANGE_GROUP_FLAG := 'N';
1670 l_create_audit_rec.CHANGE_GROUP_TYPE_FLAG := 'N';
1671 l_create_audit_rec.OLD_GROUP_ID := l_create_audit_rec.GROUP_ID;
1672 l_create_audit_rec.OLD_GROUP_TYPE := l_create_audit_rec.GROUP_TYPE;
1673 if (l_create_record = 'Y' --and
1674 --l_audit_rec.GROUP_TYPE is null and
1675 --l_audit_rec.OLD_GROUP_TYPE is null and
1676 --l_create_audit_rec.GROUP_TYPE is not null
1677 ) then
1678 l_create_audit_rec.OLD_GROUP_TYPE := null;
1679 l_create_audit_rec.CHANGE_GROUP_TYPE_FLAG := 'Y';
1680 end if;
1681 if (l_create_record = 'Y' and
1682 l_audit_rec.GROUP_ID is null and
1683 l_audit_rec.OLD_GROUP_ID is null and
1684 l_create_audit_rec.GROUP_ID is not null) then
1685 l_create_audit_rec.OLD_GROUP_ID := null;
1686 l_create_audit_rec.CHANGE_GROUP_FLAG := 'Y';
1687 end if;
1688 end if;
1689
1690 if l_audit_rec.CHANGE_OBLIGATION_FLAG = 'Y' then
1691 l_create_audit_rec.CHANGE_OBLIGATION_FLAG := 'Y';
1692 l_create_audit_rec.OLD_OBLIGATION_DATE := l_audit_rec.OLD_OBLIGATION_DATE ;
1693 l_create_audit_rec.OBLIGATION_DATE := l_audit_rec.OBLIGATION_DATE ;
1694 else
1695 l_create_audit_rec.CHANGE_OBLIGATION_FLAG := 'N';
1696 l_create_audit_rec.OLD_OBLIGATION_DATE := l_create_audit_rec.OBLIGATION_DATE;
1697 if (l_create_record = 'Y' and
1698 l_audit_rec.OBLIGATION_DATE is null and
1699 l_audit_rec.OLD_OBLIGATION_DATE is null and
1700 l_create_audit_rec.OBLIGATION_DATE is not null) then
1701 l_create_audit_rec.OLD_OBLIGATION_DATE := null;
1702 l_create_audit_rec.CHANGE_OBLIGATION_FLAG := 'Y';
1703 end if;
1704 end if;
1705
1706 if l_audit_rec.CHANGE_SITE_FLAG = 'Y' then
1707 l_create_audit_rec.CHANGE_SITE_FLAG := 'Y';
1708 l_create_audit_rec.OLD_SITE_ID := l_audit_rec.OLD_SITE_ID ;
1709 l_create_audit_rec.SITE_ID := l_audit_rec.SITE_ID ;
1710 else
1711 l_create_audit_rec.CHANGE_SITE_FLAG := 'N';
1712 l_create_audit_rec.OLD_SITE_ID := l_create_audit_rec.SITE_ID;
1713 if (l_create_record = 'Y' and
1714 l_audit_rec.SITE_ID is null and
1715 l_audit_rec.OLD_SITE_ID is null and
1716 l_create_audit_rec.SITE_ID is not null) then
1717 l_create_audit_rec.OLD_SITE_ID := null;
1718 l_create_audit_rec.CHANGE_SITE_FLAG := 'Y';
1719 end if;
1720 end if;
1721
1722 if l_audit_rec.CHANGE_BILL_TO_FLAG = 'Y' then
1723 l_create_audit_rec.CHANGE_BILL_TO_FLAG := 'Y';
1724 l_create_audit_rec.OLD_BILL_TO_CONTACT_ID := l_audit_rec.OLD_BILL_TO_CONTACT_ID ;
1725 l_create_audit_rec.BILL_TO_CONTACT_ID := l_audit_rec.BILL_TO_CONTACT_ID ;
1726 else
1727 l_create_audit_rec.CHANGE_BILL_TO_FLAG := 'N';
1728 l_create_audit_rec.OLD_BILL_TO_CONTACT_ID := l_create_audit_rec.BILL_TO_CONTACT_ID;
1729 if (l_create_record = 'Y' and
1730 l_audit_rec.BILL_TO_CONTACT_ID is null and
1731 l_audit_rec.OLD_BILL_TO_CONTACT_ID is null and
1732 l_create_audit_rec.BILL_TO_CONTACT_ID is not null) then
1733 l_create_audit_rec.OLD_BILL_TO_CONTACT_ID := null;
1734 l_create_audit_rec.CHANGE_BILL_TO_FLAG := 'Y';
1735 end if;
1736 end if;
1737
1738 if l_audit_rec.CHANGE_SHIP_TO_FLAG = 'Y' then
1739 l_create_audit_rec.CHANGE_SHIP_TO_FLAG := 'Y';
1740 l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID := l_audit_rec.OLD_SHIP_TO_CONTACT_ID ;
1741 l_create_audit_rec.SHIP_TO_CONTACT_ID := l_audit_rec.SHIP_TO_CONTACT_ID ;
1742 else
1743 l_create_audit_rec.CHANGE_SHIP_TO_FLAG := 'N';
1744 l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID := l_create_audit_rec.SHIP_TO_CONTACT_ID;
1745 if (l_create_record = 'Y' and
1746 l_audit_rec.SHIP_TO_CONTACT_ID is null and
1747 l_audit_rec.OLD_SHIP_TO_CONTACT_ID is null and
1748 l_create_audit_rec.SHIP_TO_CONTACT_ID is not null) then
1749 l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID := null;
1750 l_create_audit_rec.CHANGE_SHIP_TO_FLAG := 'Y';
1751 end if;
1752 end if;
1753
1754 if l_audit_rec.CHANGE_INCIDENT_DATE_FLAG = 'Y' then
1755 l_create_audit_rec.CHANGE_INCIDENT_DATE_FLAG := 'Y';
1756 l_create_audit_rec.OLD_INCIDENT_DATE := l_audit_rec.OLD_INCIDENT_DATE ;
1757 l_create_audit_rec.INCIDENT_DATE := l_audit_rec.INCIDENT_DATE ;
1758 else
1759 l_create_audit_rec.CHANGE_INCIDENT_DATE_FLAG := 'N';
1760 if (l_create_record = 'Y' and
1761 l_audit_rec.INCIDENT_DATE is null and
1762 l_audit_rec.OLD_INCIDENT_DATE is null and
1763 l_create_audit_rec.INCIDENT_DATE is not null) then
1764 l_create_audit_rec.OLD_INCIDENT_DATE := null;
1765 l_create_audit_rec.CHANGE_INCIDENT_DATE_FLAG := 'Y';
1766 end if;
1767 l_create_audit_rec.OLD_INCIDENT_DATE := l_create_audit_rec.INCIDENT_DATE;
1768 end if;
1769
1770 if l_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG = 'Y' then
1771 l_create_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG := 'Y';
1772 l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID := l_audit_rec.OLD_CUSTOMER_PRODUCT_ID ;
1773 l_create_audit_rec.CUSTOMER_PRODUCT_ID := l_audit_rec.CUSTOMER_PRODUCT_ID ;
1774 else
1775 l_create_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG := 'N';
1776 l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID := l_create_audit_rec.CUSTOMER_PRODUCT_ID;
1777 if (l_create_record = 'Y' and
1778 l_audit_rec.CUSTOMER_PRODUCT_ID is null and
1779 l_audit_rec.OLD_CUSTOMER_PRODUCT_ID is null and
1780 l_create_audit_rec.CUSTOMER_PRODUCT_ID is not null) then
1781 l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID := null;
1782 l_create_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG := 'Y';
1783 end if;
1784 end if;
1785
1786 if l_audit_rec.CHANGE_PLATFORM_ID_FLAG = 'Y' then
1787 l_create_audit_rec.CHANGE_PLATFORM_ID_FLAG := 'Y';
1788 l_create_audit_rec.OLD_PLATFORM_ID := l_audit_rec.OLD_PLATFORM_ID ;
1789 l_create_audit_rec.PLATFORM_ID := l_audit_rec.PLATFORM_ID ;
1790 else
1791 l_create_audit_rec.CHANGE_PLATFORM_ID_FLAG := 'N';
1792 l_create_audit_rec.OLD_PLATFORM_ID := l_create_audit_rec.PLATFORM_ID;
1793 if (l_create_record = 'Y' and
1794 l_audit_rec.PLATFORM_ID is null and
1795 l_audit_rec.OLD_PLATFORM_ID is null and
1796 l_create_audit_rec.PLATFORM_ID is not null) then
1797 l_create_audit_rec.OLD_PLATFORM_ID := null;
1798 l_create_audit_rec.CHANGE_PLATFORM_ID_FLAG := 'Y';
1799 end if;
1800 end if;
1801
1802 if l_audit_rec.CHANGE_PLAT_VER_ID_FLAG = 'Y' then
1803 l_create_audit_rec.CHANGE_PLAT_VER_ID_FLAG := 'Y';
1804 l_create_audit_rec.OLD_PLATFORM_VERSION_ID := l_audit_rec.OLD_PLATFORM_VERSION_ID ;
1805 l_create_audit_rec.PLATFORM_VERSION_ID := l_audit_rec.PLATFORM_VERSION_ID ;
1806 else
1807 l_create_audit_rec.CHANGE_PLAT_VER_ID_FLAG := 'N';
1808 l_create_audit_rec.OLD_PLATFORM_VERSION_ID := l_create_audit_rec.PLATFORM_VERSION_ID;
1809 if (l_create_record = 'Y' and
1810 l_audit_rec.PLATFORM_VERSION_ID is null and
1811 l_audit_rec.OLD_PLATFORM_VERSION_ID is null and
1812 l_create_audit_rec.PLATFORM_VERSION_ID is not null) then
1813 l_create_audit_rec.OLD_PLATFORM_VERSION_ID := null;
1814 l_create_audit_rec.CHANGE_PLAT_VER_ID_FLAG := 'Y';
1815 end if;
1816 end if;
1817
1818 if l_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG = 'Y' then
1819 l_create_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG := 'Y';
1820 l_create_audit_rec.OLD_CP_COMPONENT_ID := l_audit_rec.OLD_CP_COMPONENT_ID ;
1821 l_create_audit_rec.CP_COMPONENT_ID := l_audit_rec.CP_COMPONENT_ID ;
1822 else
1823 l_create_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG := 'N';
1824 l_create_audit_rec.OLD_CP_COMPONENT_ID := l_create_audit_rec.CP_COMPONENT_ID;
1825 if (l_create_record = 'Y' and
1826 l_audit_rec.CP_COMPONENT_ID is null and
1827 l_audit_rec.OLD_CP_COMPONENT_ID is null and
1828 l_create_audit_rec.CP_COMPONENT_ID is not null) then
1829 l_create_audit_rec.OLD_CP_COMPONENT_ID := null;
1830 l_create_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG := 'Y';
1831 end if;
1832 end if;
1833
1834 if l_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG = 'Y' then
1835 l_create_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG := 'Y';
1836 l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID := l_audit_rec.OLD_CP_COMPONENT_VERSION_ID;
1837 l_create_audit_rec.CP_COMPONENT_VERSION_ID := l_audit_rec.CP_COMPONENT_VERSION_ID;
1838 else
1839 l_create_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG := 'N';
1840 l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID := l_create_audit_rec.CP_COMPONENT_VERSION_ID;
1841 if (l_create_record = 'Y' and
1842 l_audit_rec.CP_COMPONENT_VERSION_ID is null and
1843 l_audit_rec.OLD_CP_COMPONENT_VERSION_ID is null and
1844 l_create_audit_rec.CP_COMPONENT_VERSION_ID is not null) then
1845 l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID := null;
1846 l_create_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG := 'Y';
1847 end if;
1848 end if;
1849
1850 if l_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG = 'Y' then
1851 l_create_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG := 'Y';
1852 l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID := l_audit_rec.OLD_CP_SUBCOMPONENT_ID ;
1853 l_create_audit_rec.CP_SUBCOMPONENT_ID := l_audit_rec.CP_SUBCOMPONENT_ID ;
1854 else
1855 l_create_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG := 'N';
1856 l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID := l_create_audit_rec.CP_SUBCOMPONENT_ID;
1857 if (l_create_record = 'Y' and
1858 l_audit_rec.CP_SUBCOMPONENT_ID is null and
1859 l_audit_rec.OLD_CP_SUBCOMPONENT_ID is null and
1860 l_create_audit_rec.CP_SUBCOMPONENT_ID is not null) then
1861 l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID := null;
1862 l_create_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG := 'Y';
1863 end if;
1864 end if;
1865
1866 if l_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG = 'Y' then
1867 l_create_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG := 'Y';
1868 l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID := l_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID ;
1869 l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID := l_audit_rec.CP_SUBCOMPONENT_VERSION_ID ;
1870 else
1871 l_create_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG := 'N';
1872 l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID := l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID;
1873 if (l_create_record = 'Y' and
1874 l_audit_rec.CP_SUBCOMPONENT_VERSION_ID is null and
1875 l_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID is null and
1876 l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID is not null) then
1877 l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID := null;
1878 l_create_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG := 'Y';
1879 end if;
1880 end if;
1881
1882 if l_audit_rec.CHANGE_LANGUAGE_ID_FLAG = 'Y' then
1883 l_create_audit_rec.CHANGE_LANGUAGE_ID_FLAG := 'Y';
1884 l_create_audit_rec.OLD_LANGUAGE_ID := l_audit_rec.OLD_LANGUAGE_ID ;
1885 l_create_audit_rec.LANGUAGE_ID := l_audit_rec.LANGUAGE_ID ;
1886 else
1887 l_create_audit_rec.CHANGE_LANGUAGE_ID_FLAG := 'N';
1888 l_create_audit_rec.OLD_LANGUAGE_ID := l_create_audit_rec.LANGUAGE_ID;
1889 if (l_create_record = 'Y' and
1890 l_audit_rec.LANGUAGE_ID is null and
1891 l_audit_rec.OLD_LANGUAGE_ID is null and
1892 l_create_audit_rec.LANGUAGE_ID is not null) then
1893 l_create_audit_rec.OLD_LANGUAGE_ID := null;
1894 l_create_audit_rec.CHANGE_LANGUAGE_ID_FLAG := 'Y';
1895 end if;
1896 end if;
1897
1898 if l_audit_rec.CHANGE_TERRITORY_ID_FLAG = 'Y' then
1899 l_create_audit_rec.CHANGE_TERRITORY_ID_FLAG := 'Y';
1900 l_create_audit_rec.OLD_TERRITORY_ID := l_audit_rec.OLD_TERRITORY_ID ;
1901 l_create_audit_rec.TERRITORY_ID := l_audit_rec.TERRITORY_ID ;
1902 else
1903 l_create_audit_rec.CHANGE_TERRITORY_ID_FLAG := 'N';
1904 l_create_audit_rec.OLD_TERRITORY_ID :=l_create_audit_rec.TERRITORY_ID;
1905 if (l_create_record = 'Y' and
1906 l_audit_rec.TERRITORY_ID is null and
1907 l_audit_rec.OLD_TERRITORY_ID is null and
1908 l_create_audit_rec.TERRITORY_ID is not null) then
1909 l_create_audit_rec.OLD_TERRITORY_ID := null;
1910 l_create_audit_rec.CHANGE_TERRITORY_ID_FLAG := 'Y';
1911 end if;
1912 end if;
1913
1914 if l_audit_rec.CHANGE_CP_REVISION_ID_FLAG = 'Y' then
1915 l_create_audit_rec.CHANGE_CP_REVISION_ID_FLAG := 'Y';
1916 l_create_audit_rec.OLD_CP_REVISION_ID := l_audit_rec.OLD_CP_REVISION_ID ;
1917 l_create_audit_rec.CP_REVISION_ID := l_audit_rec.CP_REVISION_ID ;
1918 else
1919 l_create_audit_rec.CHANGE_CP_REVISION_ID_FLAG := 'N';
1920 l_create_audit_rec.OLD_CP_REVISION_ID := l_create_audit_rec.CP_REVISION_ID;
1921 if (l_create_record = 'Y' and
1922 l_audit_rec.CP_REVISION_ID is null and
1923 l_audit_rec.OLD_CP_REVISION_ID is null and
1924 l_create_audit_rec.CP_REVISION_ID is not null) then
1925 l_create_audit_rec.OLD_CP_REVISION_ID := null;
1926 l_create_audit_rec.CHANGE_CP_REVISION_ID_FLAG := 'Y';
1927 end if;
1928 end if;
1929
1930 if l_audit_rec.CHANGE_INV_ITEM_REVISION = 'Y' then
1931 l_create_audit_rec.CHANGE_INV_ITEM_REVISION := 'Y';
1932 l_create_audit_rec.OLD_INV_ITEM_REVISION := l_audit_rec.OLD_INV_ITEM_REVISION ;
1933 l_create_audit_rec.INV_ITEM_REVISION := l_audit_rec.INV_ITEM_REVISION ;
1934 else
1935 l_create_audit_rec.CHANGE_INV_ITEM_REVISION := 'N';
1936 l_create_audit_rec.OLD_INV_ITEM_REVISION := l_create_audit_rec.INV_ITEM_REVISION;
1937 if (l_create_record = 'Y' and
1938 l_audit_rec.INV_ITEM_REVISION is null and
1939 l_audit_rec.OLD_INV_ITEM_REVISION is null and
1940 l_create_audit_rec.INV_ITEM_REVISION is not null) then
1941 l_create_audit_rec.OLD_INV_ITEM_REVISION := null;
1942 l_create_audit_rec.CHANGE_INV_ITEM_REVISION := 'Y';
1943 end if;
1944 end if;
1945
1946 if l_audit_rec.CHANGE_INV_COMPONENT_ID = 'Y' then
1947 l_create_audit_rec.CHANGE_INV_COMPONENT_ID := 'Y';
1948 l_create_audit_rec.OLD_INV_COMPONENT_ID := l_audit_rec.OLD_INV_COMPONENT_ID ;
1949 l_create_audit_rec.INV_COMPONENT_ID := l_audit_rec.INV_COMPONENT_ID ;
1950 else
1951 l_create_audit_rec.CHANGE_INV_COMPONENT_ID := 'N';
1952 l_create_audit_rec.OLD_INV_COMPONENT_ID := l_create_audit_rec.INV_COMPONENT_ID;
1953 if (l_create_record = 'Y' and
1954 l_audit_rec.INV_COMPONENT_ID is null and
1955 l_audit_rec.OLD_INV_COMPONENT_ID is null and
1956 l_create_audit_rec.INV_COMPONENT_ID is not null) then
1957 l_create_audit_rec.OLD_INV_COMPONENT_ID := null;
1958 l_create_audit_rec.CHANGE_INV_COMPONENT_ID := 'Y';
1959 end if;
1960 end if;
1961
1962 if l_audit_rec.CHANGE_INV_COMPONENT_VERSION = 'Y' then
1963 l_create_audit_rec.CHANGE_INV_COMPONENT_VERSION := 'Y';
1964 l_create_audit_rec.OLD_INV_COMPONENT_VERSION := l_audit_rec.OLD_INV_COMPONENT_VERSION ;
1965 l_create_audit_rec.INV_COMPONENT_VERSION := l_audit_rec.INV_COMPONENT_VERSION ;
1966 else
1967 l_create_audit_rec.CHANGE_INV_COMPONENT_VERSION := 'N';
1968 l_create_audit_rec.OLD_INV_COMPONENT_VERSION := l_create_audit_rec.INV_COMPONENT_VERSION;
1969 if (l_create_record = 'Y' and
1970 l_audit_rec.INV_COMPONENT_VERSION is null and
1971 l_audit_rec.OLD_INV_COMPONENT_VERSION is null and
1972 l_create_audit_rec.INV_COMPONENT_VERSION is not null) then
1973 l_create_audit_rec.OLD_INV_COMPONENT_VERSION := null;
1974 l_create_audit_rec.CHANGE_INV_COMPONENT_VERSION := 'Y';
1975 end if;
1976 end if;
1977
1978 if l_audit_rec.CHANGE_INV_SUBCOMPONENT_ID = 'Y' then
1979 l_create_audit_rec.CHANGE_INV_SUBCOMPONENT_ID := 'Y';
1980 l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID := l_audit_rec.OLD_INV_SUBCOMPONENT_ID ;
1981 l_create_audit_rec.INV_SUBCOMPONENT_ID := l_audit_rec.INV_SUBCOMPONENT_ID ;
1982 else
1983 l_create_audit_rec.CHANGE_INV_SUBCOMPONENT_ID := 'N';
1984 l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID := l_create_audit_rec.INV_SUBCOMPONENT_ID;
1985 if (l_create_record = 'Y' and
1986 l_audit_rec.INV_SUBCOMPONENT_ID is null and
1987 l_audit_rec.OLD_INV_SUBCOMPONENT_ID is null and
1988 l_create_audit_rec.INV_SUBCOMPONENT_ID is not null) then
1989 l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID := null;
1990 l_create_audit_rec.CHANGE_INV_SUBCOMPONENT_ID := 'Y';
1991 end if;
1992 end if;
1993
1994 if l_audit_rec.CHANGE_INV_SUBCOMP_VERSION = 'Y' then
1995 l_create_audit_rec.CHANGE_INV_SUBCOMP_VERSION := 'Y';
1996 l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION := l_audit_rec.OLD_INV_SUBCOMPONENT_VERSION ;
1997 l_create_audit_rec.INV_SUBCOMPONENT_VERSION := l_audit_rec.INV_SUBCOMPONENT_VERSION ;
1998 else
1999 l_create_audit_rec.CHANGE_INV_SUBCOMP_VERSION := 'N';
2000 l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION := l_create_audit_rec.INV_SUBCOMPONENT_VERSION;
2001 if (l_create_record = 'Y' and
2002 l_audit_rec.INV_SUBCOMPONENT_VERSION is null and
2003 l_audit_rec.OLD_INV_SUBCOMPONENT_VERSION is null and
2004 l_create_audit_rec.INV_SUBCOMPONENT_VERSION is not null) then
2005 l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION := null;
2006 l_create_audit_rec.CHANGE_INV_SUBCOMP_VERSION := 'Y';
2007 end if;
2008 end if;
2009
2010 if l_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG = 'Y' then
2011 l_create_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG := 'Y';
2012 l_create_audit_rec.OLD_INV_PLATFORM_ORG_ID := l_audit_rec.OLD_INV_PLATFORM_ORG_ID ;
2013 l_create_audit_rec.INV_PLATFORM_ORG_ID := l_audit_rec.INV_PLATFORM_ORG_ID ;
2014 else
2015 l_create_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG := 'N';
2016 l_create_audit_rec.OLD_INV_PLATFORM_ORG_ID := l_create_audit_rec.INV_PLATFORM_ORG_ID;
2017 if (l_create_record = 'Y' and
2018 l_audit_rec.INV_PLATFORM_ORG_ID is null and
2019 l_audit_rec.OLD_INV_PLATFORM_ORG_ID is null and
2020 l_create_audit_rec.INV_PLATFORM_ORG_ID is not null) then
2021 l_create_audit_rec.OLD_INV_PLATFORM_ORG_ID := null;
2022 l_create_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG := 'Y';
2023 end if;
2024 end if;
2025
2026 if l_audit_rec.CHANGE_COMP_VER_FLAG = 'Y' then
2027 l_create_audit_rec.CHANGE_COMP_VER_FLAG := 'Y';
2028 l_create_audit_rec.OLD_COMPONENT_VERSION := l_audit_rec.OLD_COMPONENT_VERSION ;
2029 l_create_audit_rec.COMPONENT_VERSION := l_audit_rec.COMPONENT_VERSION ;
2030 else
2031 l_create_audit_rec.CHANGE_COMP_VER_FLAG := 'N';
2032 l_create_audit_rec.OLD_COMPONENT_VERSION := l_create_audit_rec.COMPONENT_VERSION;
2033 if (l_create_record = 'Y' and
2034 l_audit_rec.COMPONENT_VERSION is null and
2035 l_audit_rec.OLD_COMPONENT_VERSION is null and
2036 l_create_audit_rec.COMPONENT_VERSION is not null) then
2037 l_create_audit_rec.OLD_COMPONENT_VERSION := null;
2038 l_create_audit_rec.CHANGE_COMP_VER_FLAG := 'Y';
2039 end if;
2040 end if;
2041
2042 if l_audit_rec.CHANGE_SUBCOMP_VER_FLAG = 'Y' then
2043 l_create_audit_rec.CHANGE_SUBCOMP_VER_FLAG := 'Y';
2044 l_create_audit_rec.OLD_SUBCOMPONENT_VERSION := l_audit_rec.OLD_SUBCOMPONENT_VERSION ;
2045 l_create_audit_rec.SUBCOMPONENT_VERSION := l_audit_rec.SUBCOMPONENT_VERSION ;
2046 else
2047 l_create_audit_rec.CHANGE_SUBCOMP_VER_FLAG := 'N';
2048 l_create_audit_rec.OLD_SUBCOMPONENT_VERSION := l_create_audit_rec.SUBCOMPONENT_VERSION;
2049 if (l_create_record = 'Y' and
2050 l_audit_rec.SUBCOMPONENT_VERSION is null and
2051 l_audit_rec.OLD_SUBCOMPONENT_VERSION is null and
2052 l_create_audit_rec.SUBCOMPONENT_VERSION is not null) then
2053 l_create_audit_rec.OLD_SUBCOMPONENT_VERSION := null;
2054 l_create_audit_rec.CHANGE_SUBCOMP_VER_FLAG := 'Y';
2055 end if;
2056 end if;
2057
2058 if l_audit_rec.CHANGE_PRODUCT_REVISION_FLAG = 'Y' then
2059 l_create_audit_rec.CHANGE_PRODUCT_REVISION_FLAG := 'Y';
2060 l_create_audit_rec.OLD_PRODUCT_REVISION := l_audit_rec.OLD_PRODUCT_REVISION ;
2061 l_create_audit_rec.PRODUCT_REVISION := l_audit_rec.PRODUCT_REVISION ;
2062 else
2063 l_create_audit_rec.CHANGE_PRODUCT_REVISION_FLAG := 'N';
2064 l_create_audit_rec.OLD_PRODUCT_REVISION := l_create_audit_rec.PRODUCT_REVISION;
2065 if (l_create_record = 'Y' and
2066 l_audit_rec.PRODUCT_REVISION is null and
2067 l_audit_rec.OLD_PRODUCT_REVISION is null and
2068 l_create_audit_rec.PRODUCT_REVISION is not null) then
2069 l_create_audit_rec.OLD_PRODUCT_REVISION := null;
2070 l_create_audit_rec.CHANGE_PRODUCT_REVISION_FLAG := 'Y';
2071 end if;
2072 end if;
2073
2074 if l_audit_rec.CHANGE_INVENTORY_ITEM_FLAG = 'Y' then
2075 l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG := 'Y';
2076 l_create_audit_rec.OLD_INVENTORY_ITEM_ID := l_audit_rec.OLD_INVENTORY_ITEM_ID ;
2077 l_create_audit_rec.INVENTORY_ITEM_ID := l_audit_rec.INVENTORY_ITEM_ID ;
2078 else
2079 l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG := 'N';
2080 l_create_audit_rec.OLD_INVENTORY_ITEM_ID := l_create_audit_rec.INVENTORY_ITEM_ID;
2081 if (l_create_record = 'Y' and
2082 l_audit_rec.INVENTORY_ITEM_ID is null and
2083 l_audit_rec.OLD_INVENTORY_ITEM_ID is null and
2084 l_create_audit_rec.INVENTORY_ITEM_ID is not null) then
2085 l_create_audit_rec.OLD_INVENTORY_ITEM_ID := null;
2086 l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG := 'Y';
2087 end if;
2088 end if;
2089
2090 if l_audit_rec.CHANGE_INV_ORGANIZATION_FLAG = 'Y' then
2091 l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG := 'Y';
2092 l_create_audit_rec.OLD_INV_ORGANIZATION_ID := l_audit_rec.OLD_INV_ORGANIZATION_ID ;
2093 l_create_audit_rec.INV_ORGANIZATION_ID := l_audit_rec.INV_ORGANIZATION_ID ;
2094 else
2095 l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG := 'N';
2096 l_create_audit_rec.OLD_INV_ORGANIZATION_ID := l_create_audit_rec.INV_ORGANIZATION_ID;
2097 if (l_create_record = 'Y' and
2098 l_audit_rec.INV_ORGANIZATION_ID is null and
2099 l_audit_rec.OLD_INV_ORGANIZATION_ID is null and
2100 l_create_audit_rec.INV_ORGANIZATION_ID is not null) then
2101 l_create_audit_rec.OLD_INV_ORGANIZATION_ID := null;
2102 l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG := 'Y';
2103 end if;
2104 end if;
2105
2106 if l_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG = 'Y' then
2107 l_create_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG := 'Y';
2108 l_create_audit_rec.OLD_PRIMARY_CONTACT_ID := l_audit_rec.OLD_PRIMARY_CONTACT_ID ;
2109 l_create_audit_rec.PRIMARY_CONTACT_ID := l_audit_rec.PRIMARY_CONTACT_ID ;
2110 else
2111 l_create_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG := 'N';
2112 l_create_audit_rec.OLD_PRIMARY_CONTACT_ID := l_create_audit_rec.PRIMARY_CONTACT_ID;
2113 if (l_create_record = 'Y' and
2114 l_audit_rec.PRIMARY_CONTACT_ID is null and
2115 l_audit_rec.OLD_PRIMARY_CONTACT_ID is null and
2116 l_create_audit_rec.PRIMARY_CONTACT_ID is not null) then
2117 l_create_audit_rec.OLD_PRIMARY_CONTACT_ID := null;
2118 l_create_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG := 'Y';
2119 end if;
2120 end if;
2121
2122 -- Added this to populate the G_MISS_DATE in some records
2123 if (l_create_audit_rec.OWNER_ASSIGNED_TIME > sysdate OR
2124 l_create_audit_rec.OWNER_ASSIGNED_TIME = FND_API.G_MISS_DATE) then
2125 l_create_audit_rec.OWNER_ASSIGNED_TIME := l_create_audit_rec.creation_date;
2126 end if;
2127
2128 if (l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME > sysdate OR
2129 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME = FND_API.G_MISS_DATE) then
2130 l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME := l_create_audit_rec.creation_date;
2131 end if;
2132
2133 if (l_create_audit_rec.CLOSE_DATE > sysdate OR
2134 l_create_audit_rec.CLOSE_DATE = FND_API.G_MISS_DATE) then
2135 l_create_audit_rec.CLOSE_DATE := l_create_audit_rec.creation_date;
2136 end if;
2137 if (l_create_audit_rec.OLD_CLOSE_DATE > sysdate OR
2138 l_create_audit_rec.OLD_CLOSE_DATE = FND_API.G_MISS_DATE) then
2139 l_create_audit_rec.old_CLOSE_DATE := l_create_audit_rec.creation_date;
2140 end if;
2141
2142 UPDATE cs_incidents_audit_b
2143 SET LAST_UPDATE_DATE = sysdate,
2144
2145 INCIDENT_STATUS_ID = l_create_audit_rec.INCIDENT_STATUS_ID,
2146 OLD_INCIDENT_STATUS_ID = l_create_audit_rec.OLD_INCIDENT_STATUS_ID,
2147 CHANGE_INCIDENT_STATUS_FLAG = l_create_audit_rec.CHANGE_INCIDENT_STATUS_FLAG,
2148
2149 INCIDENT_TYPE_ID = l_create_audit_rec.INCIDENT_TYPE_ID ,
2150 OLD_INCIDENT_TYPE_ID = l_create_audit_rec.OLD_INCIDENT_TYPE_ID ,
2151 CHANGE_INCIDENT_TYPE_FLAG = l_create_audit_rec.CHANGE_INCIDENT_TYPE_FLAG,
2152
2153 INCIDENT_URGENCY_ID = l_create_audit_rec.INCIDENT_URGENCY_ID ,
2154 OLD_INCIDENT_URGENCY_ID = l_create_audit_rec.OLD_INCIDENT_URGENCY_ID,
2155 CHANGE_INCIDENT_URGENCY_FLAG = l_create_audit_rec.CHANGE_INCIDENT_URGENCY_FLAG,
2156
2157 INCIDENT_SEVERITY_ID = l_create_audit_rec.INCIDENT_SEVERITY_ID ,
2158 OLD_INCIDENT_SEVERITY_ID = l_create_audit_rec.OLD_INCIDENT_SEVERITY_ID ,
2159 CHANGE_INCIDENT_SEVERITY_FLAG = l_create_audit_rec.CHANGE_INCIDENT_SEVERITY_FLAG,
2160
2161 RESPONSIBLE_GROUP_ID = l_create_audit_rec.RESPONSIBLE_GROUP_ID ,
2162 OLD_RESPONSIBLE_GROUP_ID = l_create_audit_rec.OLD_RESPONSIBLE_GROUP_ID ,
2163 CHANGE_RESPONSIBLE_GROUP_FLAG = l_create_audit_rec.CHANGE_RESPONSIBLE_GROUP_FLAG,
2164
2165 INCIDENT_OWNER_ID = l_create_audit_rec.INCIDENT_OWNER_ID ,
2166 OLD_INCIDENT_OWNER_ID = l_create_audit_rec.OLD_INCIDENT_OWNER_ID ,
2167 CHANGE_INCIDENT_OWNER_FLAG = l_create_audit_rec.CHANGE_INCIDENT_OWNER_FLAG,
2168
2169 EXPECTED_RESOLUTION_DATE= l_create_audit_rec.EXPECTED_RESOLUTION_DATE,
2170 OLD_EXPECTED_RESOLUTION_DATE = l_create_audit_rec.OLD_EXPECTED_RESOLUTION_DATE ,
2171 CHANGE_RESOLUTION_FLAG = l_create_audit_rec.CHANGE_RESOLUTION_FLAG,
2172
2173 GROUP_ID = l_create_audit_rec.GROUP_ID ,
2174 OLD_GROUP_ID = l_create_audit_rec.OLD_GROUP_ID ,
2175 change_group_flag = l_create_audit_rec.change_group_flag,
2176
2177 OBLIGATION_DATE = l_create_audit_rec.OBLIGATION_DATE ,
2178 OLD_OBLIGATION_DATE= l_create_audit_rec.OLD_OBLIGATION_DATE ,
2179 CHANGE_OBLIGATION_FLAG = l_create_audit_rec.CHANGE_OBLIGATION_FLAG,
2180
2181 SITE_ID = l_create_audit_rec.SITE_ID ,
2182 OLD_SITE_ID = l_create_audit_rec.OLD_SITE_ID ,
2183 CHANGE_SITE_FLAG = l_create_audit_rec.CHANGE_SITE_FLAG,
2184
2185 BILL_TO_CONTACT_ID = l_create_audit_rec.BILL_TO_CONTACT_ID ,
2186 OLD_BILL_TO_CONTACT_ID = l_create_audit_rec.OLD_BILL_TO_CONTACT_ID ,
2187 CHANGE_BILL_TO_FLAG = l_create_audit_rec.CHANGE_BILL_TO_FLAG,
2188
2189 SHIP_TO_CONTACT_ID = l_create_audit_rec.SHIP_TO_CONTACT_ID ,
2190 OLD_SHIP_TO_CONTACT_ID= l_create_audit_rec.OLD_SHIP_TO_CONTACT_ID ,
2191 CHANGE_SHIP_TO_FLAG = l_create_audit_rec.CHANGE_SHIP_TO_FLAG,
2192
2193 INCIDENT_DATE = l_create_audit_rec.INCIDENT_DATE ,
2194 OLD_INCIDENT_DATE = l_create_audit_rec.OLD_INCIDENT_DATE ,
2195 CHANGE_INCIDENT_DATE_FLAG = l_create_audit_rec.CHANGE_INCIDENT_DATE_FLAG,
2196
2197 CLOSE_DATE = l_create_audit_rec.CLOSE_DATE ,
2198 OLD_CLOSE_DATE = l_create_audit_rec.OLD_CLOSE_DATE ,
2199 CHANGE_CLOSE_DATE_FLAG = l_create_audit_rec.CHANGE_CLOSE_DATE_FLAG,
2200
2201 CUSTOMER_PRODUCT_ID = l_create_audit_rec.CUSTOMER_PRODUCT_ID ,
2202 OLD_CUSTOMER_PRODUCT_ID= l_create_audit_rec.OLD_CUSTOMER_PRODUCT_ID ,
2203 CHANGE_CUSTOMER_PRODUCT_FLAG = l_create_audit_rec.CHANGE_CUSTOMER_PRODUCT_FLAG,
2204
2205 PLATFORM_ID = l_create_audit_rec.PLATFORM_ID ,
2206 OLD_PLATFORM_ID = l_create_audit_rec.OLD_PLATFORM_ID ,
2207 CHANGE_PLATFORM_ID_FLAG = l_create_audit_rec.CHANGE_PLATFORM_ID_FLAG,
2208
2209 PLATFORM_VERSION_ID = l_create_audit_rec.PLATFORM_VERSION_ID ,
2210 OLD_PLATFORM_VERSION_ID = l_create_audit_rec.OLD_PLATFORM_VERSION_ID ,
2211 CHANGE_PLAT_VER_ID_FLAG = l_create_audit_rec.CHANGE_PLAT_VER_ID_FLAG,
2212
2213 CP_COMPONENT_ID = l_create_audit_rec.CP_COMPONENT_ID ,
2214 OLD_CP_COMPONENT_ID = l_create_audit_rec.OLD_CP_COMPONENT_ID ,
2215 CHANGE_CP_COMPONENT_ID_FLAG = l_create_audit_rec.CHANGE_CP_COMPONENT_ID_FLAG,
2216
2217 CP_COMPONENT_VERSION_ID = l_create_audit_rec.CP_COMPONENT_VERSION_ID ,
2218 OLD_CP_COMPONENT_VERSION_ID= l_create_audit_rec.OLD_CP_COMPONENT_VERSION_ID,
2219 CHANGE_CP_COMP_VER_ID_FLAG = l_create_audit_rec.CHANGE_CP_COMP_VER_ID_FLAG,
2220
2221 CP_SUBCOMPONENT_ID = l_create_audit_rec.CP_SUBCOMPONENT_ID,
2222 OLD_CP_SUBCOMPONENT_ID = l_create_audit_rec.OLD_CP_SUBCOMPONENT_ID ,
2223 CHANGE_CP_SUBCOMPONENT_ID_FLAG = l_create_audit_rec.CHANGE_CP_SUBCOMPONENT_ID_FLAG,
2224
2225 CP_SUBCOMPONENT_VERSION_ID = l_create_audit_rec.CP_SUBCOMPONENT_VERSION_ID ,
2226 OLD_CP_SUBCOMPONENT_VERSION_ID= l_create_audit_rec.OLD_CP_SUBCOMPONENT_VERSION_ID ,
2227 CHANGE_CP_SUBCOMP_VER_ID_FLAG = l_create_audit_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG,
2228
2229 LANGUAGE_ID = l_create_audit_rec.LANGUAGE_ID ,
2230 OLD_LANGUAGE_ID = l_create_audit_rec.OLD_LANGUAGE_ID ,
2231 CHANGE_LANGUAGE_ID_FLAG = l_create_audit_rec.CHANGE_LANGUAGE_ID_FLAG,
2232
2233 TERRITORY_ID = l_create_audit_rec.TERRITORY_ID ,
2234 OLD_TERRITORY_ID = l_create_audit_rec.OLD_TERRITORY_ID ,
2235 CHANGE_TERRITORY_ID_FLAG = l_create_audit_rec.CHANGE_TERRITORY_ID_FLAG,
2236
2237 CP_REVISION_ID = l_create_audit_rec.CP_REVISION_ID ,
2238 OLD_CP_REVISION_ID = l_create_audit_rec.OLD_CP_REVISION_ID ,
2239 CHANGE_CP_REVISION_ID_FLAG = l_create_audit_rec.CHANGE_CP_REVISION_ID_FLAG,
2240
2241 INV_ITEM_REVISION = l_create_audit_rec.INV_ITEM_REVISION ,
2242 OLD_INV_ITEM_REVISION = l_create_audit_rec.OLD_INV_ITEM_REVISION ,
2243 CHANGE_INV_ITEM_REVISION = l_create_audit_rec.CHANGE_INV_ITEM_REVISION,
2244
2245 INV_COMPONENT_ID = l_create_audit_rec.INV_COMPONENT_ID ,
2246 OLD_INV_COMPONENT_ID = l_create_audit_rec.OLD_INV_COMPONENT_ID ,
2247 CHANGE_INV_COMPONENT_ID = l_create_audit_rec.CHANGE_INV_COMPONENT_ID,
2248
2249 INV_COMPONENT_VERSION = l_create_audit_rec.INV_COMPONENT_VERSION ,
2250 OLD_INV_COMPONENT_VERSION = l_create_audit_rec.OLD_INV_COMPONENT_VERSION ,
2251 CHANGE_INV_COMPONENT_VERSION = l_create_audit_rec.CHANGE_INV_COMPONENT_VERSION,
2252
2253 INV_SUBCOMPONENT_ID = l_create_audit_rec.INV_SUBCOMPONENT_ID ,
2254 OLD_INV_SUBCOMPONENT_ID = l_create_audit_rec.OLD_INV_SUBCOMPONENT_ID ,
2255 CHANGE_INV_SUBCOMPONENT_ID = l_create_audit_rec.CHANGE_INV_SUBCOMPONENT_ID,
2256
2257 INV_SUBCOMPONENT_VERSION = l_create_audit_rec.INV_SUBCOMPONENT_VERSION ,
2258 OLD_INV_SUBCOMPONENT_VERSION = l_create_audit_rec.OLD_INV_SUBCOMPONENT_VERSION ,
2259 CHANGE_INV_SUBCOMP_VERSION = l_create_audit_rec.CHANGE_INV_SUBCOMP_VERSION,
2260
2261 RESOURCE_TYPE = l_create_audit_rec.RESOURCE_TYPE ,
2262 OLD_RESOURCE_TYPE = l_create_audit_rec.OLD_RESOURCE_TYPE ,
2263 change_resource_type_flag = l_create_audit_rec.change_resource_type_flag,
2264
2265 OLD_GROUP_TYPE = l_create_audit_rec.OLD_GROUP_TYPE ,
2266 GROUP_TYPE = l_create_audit_rec.GROUP_TYPE ,
2267 change_group_type_flag = l_create_audit_rec.change_group_type_flag,
2268
2269 OLD_OWNER_ASSIGNED_TIME = l_create_audit_rec.OLD_OWNER_ASSIGNED_TIME ,
2270 OWNER_ASSIGNED_TIME= l_create_audit_rec.OWNER_ASSIGNED_TIME ,
2271 CHANGE_ASSIGNED_TIME_FLAG = l_create_audit_rec.CHANGE_ASSIGNED_TIME_FLAG,
2272
2273 INV_PLATFORM_ORG_ID = l_create_audit_rec.INV_PLATFORM_ORG_ID ,
2274 OLD_INV_PLATFORM_ORG_ID = l_create_audit_rec.OLD_INV_PLATFORM_ORG_ID ,
2275 CHANGE_PLATFORM_ORG_ID_FLAG = l_create_audit_rec.CHANGE_PLATFORM_ORG_ID_FLAG,
2276
2277 COMPONENT_VERSION = l_create_audit_rec.COMPONENT_VERSION ,
2278 OLD_COMPONENT_VERSION = l_create_audit_rec.OLD_COMPONENT_VERSION ,
2279 CHANGE_COMP_VER_FLAG = l_create_audit_rec.CHANGE_COMP_VER_FLAG,
2280
2281 SUBCOMPONENT_VERSION = l_create_audit_rec.SUBCOMPONENT_VERSION ,
2282 OLD_SUBCOMPONENT_VERSION = l_create_audit_rec.OLD_SUBCOMPONENT_VERSION ,
2283 CHANGE_SUBCOMP_VER_FLAG = l_create_audit_rec.CHANGE_SUBCOMP_VER_FLAG,
2284
2285 PRODUCT_REVISION = l_create_audit_rec.PRODUCT_REVISION ,
2286 OLD_PRODUCT_REVISION = l_create_audit_rec.OLD_PRODUCT_REVISION ,
2287 CHANGE_PRODUCT_REVISION_FLAG = l_create_audit_rec.CHANGE_PRODUCT_REVISION_FLAG,
2288
2289 INVENTORY_ITEM_ID = l_create_audit_rec.INVENTORY_ITEM_ID ,
2290 OLD_INVENTORY_ITEM_ID = l_create_audit_rec.OLD_INVENTORY_ITEM_ID ,
2291 CHANGE_INVENTORY_ITEM_FLAG = decode(l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG,NULL,'N',l_create_audit_rec.CHANGE_INVENTORY_ITEM_FLAG),
2292
2293 INV_ORGANIZATION_ID = l_create_audit_rec.INV_ORGANIZATION_ID ,
2294 OLD_INV_ORGANIZATION_ID = l_create_audit_rec.OLD_INV_ORGANIZATION_ID ,
2295 --CHANGE_INV_ORGANIZATION_FLAG = decode(l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG,NULL,'N',l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG),
2296 CHANGE_INV_ORGANIZATION_FLAG = l_create_audit_rec.CHANGE_INV_ORGANIZATION_FLAG,
2297
2298 STATUS_FLAG = l_create_audit_rec.STATUS_FLAG ,
2299 OLD_STATUS_FLAG = l_create_audit_rec.OLD_STATUS_FLAG,
2300 CHANGE_STATUS_FLAG = l_create_audit_rec.CHANGE_STATUS_FLAG,
2301
2302 PRIMARY_CONTACT_ID = l_create_audit_rec.PRIMARY_CONTACT_ID ,
2303 OLD_PRIMARY_CONTACT_ID= l_create_audit_rec.OLD_PRIMARY_CONTACT_ID,
2304 CHANGE_PRIMARY_CONTACT_FLAG = l_create_audit_rec.CHANGE_PRIMARY_CONTACT_FLAG,
2305
2306 UPGRADE_FLAG_FOR_CREATE = decode(l_create_record, 'Y', 'X', 'U'),
2307 updated_entity_code = 'SR_HEADER'
2308 WHERE incident_audit_id = l_audit_rec.incident_audit_id;
2309 commit;
2310 end loop;
2311 close c_sr_audit_asc;
2312 end if;
2313
2314 l_loop_counter := l_loop_counter+1;
2315 END LOOP; -- } Loop2
2316
2317 CLOSE c_sr_current;
2318
2319 ad_parallel_updates_pkg.processed_rowid_range(
2320 l_loop_counter,
2321 l_end_rowid);
2322
2323 --
2324 -- commit transaction here
2325 --
2326 COMMIT;
2327 --
2328
2329 -- get new range of rowids
2330 --
2331 ad_parallel_updates_pkg.get_rowid_range(
2332 l_start_rowid,
2333 l_end_rowid,
2334 l_any_rows_to_process,
2335 x_batch_size,
2336 FALSE);
2337
2338 END LOOP; -- } Loop1
2339
2340 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
2341
2342 EXCEPTION
2343 WHEN OTHERS THEN
2344 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
2345 RAISE;
2346 END; -- } begin1
2347
2348 END Worker_Audit_Upgrade;
2349
2350 END CS_AUDIT_UPGRADE_CON_PRG;