[Home] [Help]
6: g_object_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY_EVENT_PKG' ;
7: g_table_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY';
8: g_publication_item_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
9: CSM_ACC_PKG.t_publication_item_list('CSM_SERVICE_HISTORY');
10: g_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY_ACC';
11: g_pk1_name CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
12: g_pk2_name CONSTANT VARCHAR2(30) := 'HISTORY_INCIDENT_ID';
13: g_pk3_name CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
14: g_seq_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY_ACC_S' ;
10: g_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY_ACC';
11: g_pk1_name CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
12: g_pk2_name CONSTANT VARCHAR2(30) := 'HISTORY_INCIDENT_ID';
13: g_pk3_name CONSTANT VARCHAR2(30) := 'INSTANCE_ID';
14: g_seq_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_HISTORY_ACC_S' ;
15:
16: g_incidents_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_INCIDENTS_ALL_ACC';
17: g_incidents_table_name CONSTANT VARCHAR2(30) := 'CS_INCIDENTS_ALL';
18: g_incidents_seq_name CONSTANT VARCHAR2(30) := 'CSM_INCIDENTS_ALL_ACC_S' ;
118: AND nvl(tast.CANCELLED_FLAG,'N') <> 'Y'
119: AND nvl(tast.REJECTED_FLAG,'N') <> 'Y'
120: AND NOT EXISTS
121: (SELECT 1
122: FROM csm_service_history_acc acc
123: WHERE acc.user_id = b_user_id
124: AND acc.incident_id = b_incident_id
125: AND acc.history_incident_id = b_hist_incident_id
126: );
263:
264: CURSOR c_history( b_incident_id NUMBER, b_user_id NUMBER )
265: IS
266: SELECT HISTORY_INCIDENT_ID
267: FROM CSM_SERVICE_HISTORY_ACC
268: WHERE INCIDENT_ID = b_incident_id
269: AND USER_ID = b_user_id;
270:
271: TYPE history_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
458: -- this purges SR history that was not purged by the TA purge
459: -- ideally this shouldn't happen and this is more of a data fix
460: CURSOR l_purge_SR_history
461: IS
462: SELECT /*+INDEX (hacc CSM_SERVICE_HISTORY_ACC_U1)*/ user_id,
463: incident_id,
464: history_incident_id
465: FROM csm_service_history_acc hacc
466: WHERE NOT EXISTS
461: IS
462: SELECT /*+INDEX (hacc CSM_SERVICE_HISTORY_ACC_U1)*/ user_id,
463: incident_id,
464: history_incident_id
465: FROM csm_service_history_acc hacc
466: WHERE NOT EXISTS
467: (SELECT 'X'
468: FROM csm_incidents_all_acc acc
469: WHERE acc.user_id = hacc.user_id
582: l_return_status VARCHAR2(2000);
583:
584: CURSOR c_history ( b_incident_id NUMBER, b_task_assignment_id NUMBER, b_user_id NUMBER ) IS
585: SELECT HISTORY_INCIDENT_ID
586: FROM CSM_SERVICE_HISTORY_ACC
587: WHERE INCIDENT_ID = b_incident_id
588: AND USER_ID = b_user_id
589: AND NOT EXISTS (SELECT 'X'
590: FROM CSM_TASK_ASSIGNMENTS_ACC ACC,
699: FROM csm_party_assignment cpa
700: WHERE cpa.deleted_flag ='N'
701: )
702: AND NOT EXISTS ( SELECT 1
703: FROM csm_service_history_acc csha
704: WHERE csha.user_id = ciia.user_id
705: AND csha.history_incident_id = ciab.incident_id
706: );
707:
713: IS
714: SELECT csha.history_incident_id
715: , csha.user_id
716: , csha.instance_id
717: FROM csm_service_history_acc csha
718: WHERE csha.incident_id=1
719: AND NOT EXISTS ( SELECT 1
720: FROM csm_item_instances_acc ciia
721: WHERE ciia.user_id = csha.user_id
762: IF l_incident_id_tbl.COUNT > 0 THEN
763:
764: FOR i IN l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST LOOP
765:
766: --call the CSM_ACC_PKG to insert into csm_service_history_acc table
767:
768: CSM_ACC_PKG.Insert_Acc
769: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
770: ,P_ACC_TABLE_NAME => g_acc_table_name
833: IF l_incident_id_tbl.COUNT > 0 THEN
834:
835: FOR i IN l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST LOOP
836:
837: --call the CSM_ACC_PKG to delete from csm_service_history_acc table
838:
839: CSM_ACC_PKG.Delete_Acc
840: ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
841: ,P_ACC_TABLE_NAME => g_acc_table_name