[Home] [Help]
PACKAGE BODY: APPS.CSM_HA_AUDIT_PKG
Source
1 PACKAGE BODY CSM_HA_AUDIT_PKG AS
2 /* $Header: csmhadtb.pls 120.0.12010000.1 2010/04/08 06:38:17 saradhak noship $*/
3
4 FUNCTION get_listfrom_String(p_object_name IN VARCHAR2) return CSM_VARCHAR_LIST
5 IS
6 l_temp VARCHAR2(1000);
7 list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
8 l_item1 VARCHAR2(500);
9 l_item2 VARCHAR2(500);
10 l_cnt NUMBER :=1;
11 BEGIN
12 l_temp:=p_object_name;
13 IF instr(l_temp,',') > 0 THEN
14 LOOP
15 l_item1 := trim(substr(l_temp,1,instr(l_temp,',')-1));
16 list.extend(1);
17 list(l_cnt) := l_item1;
18 l_cnt := l_cnt+1;
19 l_item2 := trim(substr(l_temp,instr(l_temp,',')+1));
20 l_temp:= l_item2;
21 EXIT WHEN instr(l_temp,',') = 0;
22 END LOOP;
23 ELSE
24 l_item2:=l_temp;
25 END IF;
26
27 IF(length(l_item2)>0) THEN
28 list.extend(1);
29 list(l_cnt) := l_item2;
30 END IF;
31
32 RETURN list;
33
34 END get_listfrom_String;
35
36 PROCEDURE AUDIT_RECORD(p_ha_payload_id IN NUMBER, p_audit_type IN VARCHAR2)
37 IS
38 PRAGMA AUTONOMOUS_TRANSACTION;
39 l_audit_id NUMBER;
40 l_XML_PAYLOAD CLOB :=NULL;
41 l_session_id NUMBER;
42 l_tab_name VARCHAR2(100);
43 l_XML_CONTEXT CLOB:=NULL;
44 l_dml VARCHAR2(1);
45 l_mobile_data VARCHAR2(1);
46 l_RETURN_STATUS VARCHAR2(100);
47 l_ERROR_MESSAGE VARCHAR2(4000):='';
48
49 CURSOR c_get_sess
50 IS
51 SELECT SESSION_ID FROM CSM_HA_SESSION_INFO
52 WHERE p_ha_payload_id BETWEEN HA_PAYLOAD_START AND HA_PAYLOAD_END;
53
54 CURSOR c_get_details
55 IS
56 SELECT OBJECT_NAME,PK_VALUE,DML_TYPE,MOBILE_DATA
57 FROM CSM_HA_PAYLOAD_DATA
58 WHERE HA_PAYLOAD_ID=p_ha_payload_id;
59
60 l_PK_NAME_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
61 l_PK_TYPE_LIST CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
62 l_pk_value_list CSM_VARCHAR_LIST:=CSM_VARCHAR_LIST();
63
64 l_pk_name VARCHAR2(500);
65 l_pk_value VARCHAR2(1000);
66 BEGIN
67
68 select CSM_HA_AUDIT_S.nextval INTO l_audit_id from dual;
69
70 IF p_audit_type='APPLY' THEN
71 OPEN c_get_sess;
72 FETCH c_get_sess INTO l_session_id;
73 CLOSE c_get_sess;
74
75 OPEN c_get_details;
76 FETCH c_get_details INTO l_tab_name,l_pk_value,l_dml,l_mobile_data;
77 CLOSE c_get_details;
78
79 IF l_dml='U' AND l_mobile_data='N' THEN
80 l_pk_name:=CSM_HA_EVENT_PKG.GET_PK_COLUMN_NAME(l_tab_name);
81 l_pk_name_list := get_listfrom_String(l_pk_name);
82 l_pk_value_list:= get_listfrom_String(l_pk_value);
83 l_pk_type_list.extend(l_pk_name_list.COUNT);
84
85 FOR I IN 1..l_pk_name_list.COUNT
86 LOOP
87 l_pk_type_list(I):='VARCHAR';
88 END LOOP;
89
90 IF l_pk_name_list.COUNT =0 THEN
91 CSM_UTIL_PKG.log('COULD NOT EXTRACT OLD PAYLOAD as no PK column name found for ' || l_tab_name
92 , 'CSM_HA_AUDIT_PKG.AUDIT_RECORD', FND_LOG.LEVEL_PROCEDURE);
93 ELSE
94 CSM_HA_EVENT_PKG.GET_XML_PAYLOAD(l_tab_name,l_PK_NAME_LIST,l_PK_TYPE_LIST,l_PK_VALUE_LIST,
95 l_XML_PAYLOAD,l_XML_CONTEXT,l_RETURN_STATUS,l_ERROR_MESSAGE);
96 END IF;
97 END IF;
98
99 ELSE
100 l_session_id:=CSM_HA_EVENT_PKG.G_HA_SESSION_SEQUENCE;
101 END IF;
102
103 INSERT INTO CSM_HA_AUDIT(HA_AUDIT_ID,SESSION_ID,HA_PAYLOAD_ID, AUDIT_TYPE, AUDIT_TIMESTAMP,OLD_PAYLOAD,
104 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
105 VALUES(l_audit_id,l_session_id,p_ha_payload_id,p_audit_type,systimestamp,DECODE(p_audit_type,'APPLY',xmltype(l_xml_payload)),sysdate,1,sysdate,1,1);
106
107 COMMIT;
108 EXCEPTION
109 WHEN OTHERS THEN
110 l_ERROR_MESSAGE :=SUBSTR(SQLERRM,1,3000)||SUBSTR(l_ERROR_MESSAGE,1,999);
111 CSM_UTIL_PKG.LOG('Exception occurred in AUDIT_RECORD -' ||l_ERROR_MESSAGE ,
112 'CSM_HA_EVENT_PKG.AUDIT_RECORD',FND_LOG.LEVEL_EXCEPTION);
113 COMMIT;
114 RAISE;
115 END AUDIT_RECORD;
116
117 END CSM_HA_AUDIT_PKG;
118