[Home] [Help]
PACKAGE BODY: APPS.CS_SR_ACTION_TRIGGERS_PKG
Source
1 package body CS_SR_ACTION_TRIGGERS_PKG as
2 /* $Header: csxtnatb.pls 120.0 2005/08/12 15:29:23 aneemuch noship $ */
3 procedure INSERT_ROW (
4 PX_EVENT_CONDITION_ID in out NOCOPY NUMBER,
5 P_FROM_TO_STATUS_CODE in VARCHAR2,
6 P_INCIDENT_STATUS_ID in NUMBER,
7 P_RELATIONSHIP_TYPE_ID in NUMBER,
8 P_START_DATE_ACTIVE in DATE,
9 P_END_DATE_ACTIVE in DATE,
10 P_SEEDED_FLAG in VARCHAR2,
11 P_APPLICATION_ID in NUMBER,
12 P_EVENT_CODE in VARCHAR2,
13 P_CREATION_DATE in DATE,
14 P_CREATED_BY in NUMBER,
15 P_LAST_UPDATE_DATE in DATE,
16 P_LAST_UPDATED_BY in NUMBER,
17 P_LAST_UPDATE_LOGIN in NUMBER,
18 X_OBJECT_VERSION_NUMBER out NOCOPY NUMBER
19 ) is
20 CURSOR C2 IS SELECT CS_SR_ACTION_TRIGGERS_S.nextval FROM sys.dual;
21 l_object_version_number number := 1;
22 begin
23 If (px_EVENT_CONDITION_ID IS NULL) OR (px_EVENT_CONDITION_ID = FND_API.G_MISS_NUM) then
24 OPEN C2;
25 FETCH C2 INTO px_EVENT_CONDITION_ID;
26 CLOSE C2;
27 End If;
28
29 insert into CS_SR_ACTION_TRIGGERS (
30 EVENT_CONDITION_ID,
31 EVENT_CODE,
32 FROM_TO_STATUS_CODE,
33 INCIDENT_STATUS_ID,
34 RELATIONSHIP_TYPE_ID,
35 START_DATE_ACTIVE,
36 END_DATE_ACTIVE,
37 SEEDED_FLAG,
38 APPLICATION_ID,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATE_DATE,
42 LAST_UPDATE_LOGIN,
43 LAST_UPDATED_BY,
44 OBJECT_VERSION_NUMBER
45 ) values (
46 PX_EVENT_CONDITION_ID,
47 P_EVENT_CODE,
48 P_FROM_TO_STATUS_CODE,
49 P_INCIDENT_STATUS_ID,
50 P_RELATIONSHIP_TYPE_ID,
51 P_START_DATE_ACTIVE,
52 P_END_DATE_ACTIVE,
53 P_SEEDED_FLAG,
54 P_APPLICATION_ID,
55 P_CREATED_BY,
56 P_CREATION_DATE,
57 P_LAST_UPDATE_DATE,
58 P_LAST_UPDATE_LOGIN,
59 P_LAST_UPDATED_BY,
60 L_OBJECT_VERSION_NUMBER
61 );
62
63 x_object_version_number := l_object_version_number;
64
65 end INSERT_ROW;
66
67 procedure LOCK_ROW (
68 P_EVENT_CONDITION_ID in NUMBER,
69 P_OBJECT_VERSION_NUMBER in NUMBER
70 ) is
71
72 cursor c is select
73 OBJECT_VERSION_NUMBER
74 from CS_SR_ACTION_TRIGGERS
75 where EVENT_CONDITION_ID = P_EVENT_CONDITION_ID
76 for update of EVENT_CONDITION_ID nowait;
77
78 l_object_version_number number := 0;
79
80 begin
81 open c;
82 fetch c into l_object_Version_number;
83 if (c%notfound) then
84 close c;
85 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
86 app_exception.raise_exception;
87 end if;
88 close c;
89
90 if (l_object_version_number = P_OBJECT_VERSION_NUMBER) then
91 null;
92 else
93 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
94 app_exception.raise_exception;
95 end if;
96
97 return;
98 end LOCK_ROW;
99
100 procedure UPDATE_ROW (
101 P_EVENT_CONDITION_ID in NUMBER,
102 P_FROM_TO_STATUS_CODE in VARCHAR2,
103 P_INCIDENT_STATUS_ID in NUMBER,
104 P_RELATIONSHIP_TYPE_ID in NUMBER,
105 P_START_DATE_ACTIVE in DATE,
106 P_END_DATE_ACTIVE in DATE,
107 P_SEEDED_FLAG in VARCHAR2,
108 P_APPLICATION_ID in NUMBER,
109 P_EVENT_CODE in VARCHAR2,
110 P_LAST_UPDATE_DATE in DATE,
111 P_LAST_UPDATED_BY in NUMBER,
112 P_LAST_UPDATE_LOGIN in NUMBER,
113 X_OBJECT_VERSION_NUMBER out NOCOPY NUMBER
114 ) is
115 l_object_Version_number number;
116 begin
117 update CS_SR_ACTION_TRIGGERS set
118 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
119 FROM_TO_STATUS_CODE = P_FROM_TO_STATUS_CODE,
120 INCIDENT_STATUS_ID = P_INCIDENT_STATUS_ID,
121 RELATIONSHIP_TYPE_ID = P_RELATIONSHIP_TYPE_ID,
122 START_DATE_ACTIVE = P_START_DATE_ACTIVE,
123 END_DATE_ACTIVE = P_END_DATE_ACTIVE,
124 SEEDED_FLAG = P_SEEDED_FLAG,
125 APPLICATION_ID = P_APPLICATION_ID,
126 EVENT_CODE = P_EVENT_CODE,
127 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
128 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
129 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
130 where EVENT_CONDITION_ID = P_EVENT_CONDITION_ID
131 RETURNING OBJECT_VERSION_NUMBER INTO L_OBJECT_VERSION_NUMBER;
132
133 X_OBJECT_VERSION_NUMBER := l_object_version_number;
134
135 if (sql%notfound) then
136 raise no_data_found;
137 end if;
138 end UPDATE_ROW;
139
140 procedure DELETE_ROW (
141 P_EVENT_CONDITION_ID in NUMBER
142 ) is
143 begin
144 delete from CS_SR_ACTION_TRIGGERS
145 where EVENT_CONDITION_ID = P_EVENT_CONDITION_ID;
146
147 if (sql%notfound) then
148 raise no_data_found;
149 end if;
150
151 end DELETE_ROW;
152
153 PROCEDURE LOAD_ROW (
154 P_EVENT_CONDITION_ID IN NUMBER,
155 P_FROM_TO_STATUS_CODE IN VARCHAR2,
156 P_INCIDENT_STATUS_ID IN NUMBER,
157 P_RELATIONSHIP_TYPE_ID IN NUMBER,
158 P_START_DATE_ACTIVE IN VARCHAR2,
159 P_END_DATE_ACTIVE IN VARCHAR2,
160 P_SEEDED_FLAG IN VARCHAR2,
161 P_APPLICATION_ID IN NUMBER,
162 P_EVENT_CODE IN VARCHAR2,
163 P_OWNER IN VARCHAR2,
164 P_CREATION_DATE IN VARCHAR2,
165 P_CREATED_BY IN NUMBER,
166 P_LAST_UPDATE_DATE IN VARCHAR2,
167 P_LAST_UPDATED_BY IN NUMBER,
168 P_LAST_UPDATE_LOGIN IN NUMBER,
169 P_OBJECT_VERSION_NUMBER IN NUMBER
170 )
171 IS
172
173 -- Out local variables for the update / insert row procedures.
174 lx_object_version_number NUMBER := 0;
175 l_user_id NUMBER := 0;
176
177 -- needed to be passed as the parameter value for the insert's in/out
178 -- parameter.
179 l_event_condition_id NUMBER;
180
181 BEGIN
182
183 if ( p_owner = 'SEED' ) then
184 l_user_id := 1;
185 end if;
186
187 l_event_condition_id := p_event_condition_id;
188
189 UPDATE_ROW (
190 P_EVENT_CONDITION_ID =>l_event_condition_id,
191 P_FROM_TO_STATUS_CODE =>p_from_to_status_code,
192 P_INCIDENT_STATUS_ID =>p_incident_status_id,
193 P_RELATIONSHIP_TYPE_ID =>p_relationship_type_id,
194 P_START_DATE_ACTIVE =>to_date(p_start_date_active,'DD-MM-YYYY'),
195 P_END_DATE_ACTIVE =>to_date(p_end_date_active,'DD-MM-YYYY'),
196 P_SEEDED_FLAG =>p_seeded_flag,
197 P_APPLICATION_ID =>p_application_id,
198 P_EVENT_CODE =>p_event_code,
199 P_LAST_UPDATE_DATE =>nvl(to_date(p_last_update_date,
200 'DD-MM-YYYY'),sysdate),
201 P_LAST_UPDATED_BY =>l_user_id,
202 P_LAST_UPDATE_LOGIN =>0,
203 X_OBJECT_VERSION_NUMBER =>lx_object_version_number
204 );
205
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 INSERT_ROW (
209 PX_EVENT_CONDITION_ID =>l_event_condition_id,
210 P_FROM_TO_STATUS_CODE =>p_from_to_status_code,
211 P_INCIDENT_STATUS_ID =>p_incident_status_id,
212 P_RELATIONSHIP_TYPE_ID =>p_relationship_type_id,
213 P_START_DATE_ACTIVE =>to_date(p_start_date_active,'DD-MM-YYYY'),
214 P_END_DATE_ACTIVE =>to_date(p_end_date_active,'DD-MM-YYYY'),
215 P_SEEDED_FLAG =>p_seeded_flag,
216 P_APPLICATION_ID =>p_application_id,
217 P_EVENT_CODE =>p_event_code,
218 P_CREATION_DATE =>nvl(to_date( p_creation_date,
219 'DD-MM-YYYY'),sysdate),
220 P_CREATED_BY =>l_user_id,
221 P_LAST_UPDATE_DATE =>nvl(to_date(p_last_update_date,
222 'DD-MM-YYYY'),sysdate),
223 P_LAST_UPDATED_BY =>l_user_id,
224 P_LAST_UPDATE_LOGIN =>0,
225 X_OBJECT_VERSION_NUMBER =>lx_object_version_number
226 );
227
228 END LOAD_ROW;
229
230 end CS_SR_ACTION_TRIGGERS_PKG;