[Home] [Help]
PACKAGE BODY: APPS.PA_EVENT_TYPES_PKG
Source
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1 PACKAGE BODY PA_EVENT_TYPES_PKG as
2 /* $Header: PAXSUETB.pls 120.3 2005/08/19 17:20:53 mwasowic noship $ */
3
5 X_Event_type_Id NUMBER, /** 2363945 **/
6 X_Event_Type VARCHAR2,
7 X_Last_Update_Date DATE,
8 X_Last_Updated_By NUMBER,
9 X_Creation_Date DATE,
10 X_Created_By NUMBER,
11 X_Last_Update_Login NUMBER,
12 X_Start_Date_Active DATE,
13 X_Event_Type_Classification VARCHAR2,
14 X_End_Date_Active DATE,
15 X_Description VARCHAR2,
16 X_Revenue_Category_Code VARCHAR2,
17 /* X_Output_tax_code VARCHAR2, Shared Services*/
18 X_Attribute_Category VARCHAR2,
19 X_Attribute1 VARCHAR2,
20 X_Attribute2 VARCHAR2,
21 X_Attribute3 VARCHAR2,
22 X_Attribute4 VARCHAR2,
23 X_Attribute5 VARCHAR2,
24 X_Attribute6 VARCHAR2,
25 X_Attribute7 VARCHAR2,
26 X_Attribute8 VARCHAR2,
27 X_Attribute9 VARCHAR2,
28 X_Attribute10 VARCHAR2,
29 X_Attribute11 VARCHAR2,
30 X_Attribute12 VARCHAR2,
31 X_Attribute13 VARCHAR2,
32 X_Attribute14 VARCHAR2,
33 X_Attribute15 VARCHAR2
34
35 ) IS
36 CURSOR C IS SELECT rowid FROM PA_EVENT_TYPES
37 WHERE event_type = X_Event_Type;
38
39 /*
40 CURSOR T IS SELECT VAT_TAX_ID
41 FROM PA_OUTPUT_TAX_CODE_SETUP_V
42 WHERE TAX_CODE = X_Output_tax_code ;
43 */ --by hsiu
44
45 CURSOR O IS SELECT ORG_ID
46 FROM PA_IMPLEMENTATIONS;
47
48 L_TAX_ID NUMBER;
49 L_ORG_ID NUMBER;
50
51 BEGIN
52
53
54 INSERT INTO PA_EVENT_TYPES(
55
56 event_type,
57 event_type_id,
58 last_update_date,
59 last_updated_by,
60 creation_date,
61 created_by,
62 last_update_login,
63 start_date_active,
64 event_type_classification,
65 end_date_active,
66 description,
67 revenue_category_code,
68 attribute_category,
69 attribute1,
70 attribute2,
71 attribute3,
72 attribute4,
73 attribute5,
74 attribute6,
75 attribute7,
76 attribute8,
77 attribute9,
78 attribute10,
79 attribute11,
80 attribute12,
81 attribute13,
82 attribute14,
83 attribute15
84 ) VALUES (
85
86 X_Event_Type,
87 X_Event_Type_Id,
88 X_Last_Update_Date,
89 X_Last_Updated_By,
90 X_Creation_Date,
91 X_Created_By,
92 X_Last_Update_Login,
93 X_Start_Date_Active,
94 X_Event_Type_Classification,
95 X_End_Date_Active,
96 X_Description,
97 X_Revenue_Category_Code,
98 X_Attribute_Category,
99 X_Attribute1,
100 X_Attribute2,
101 X_Attribute3,
102 X_Attribute4,
103 X_Attribute5,
104 X_Attribute6,
105 X_Attribute7,
106 X_Attribute8,
107 X_Attribute9,
108 X_Attribute10,
109 X_Attribute11,
110 X_Attribute12,
111 X_Attribute13,
112 X_Attribute14,
113 X_Attribute15
114 );
115 OPEN C;
116 FETCH C INTO X_Rowid;
117 if (C%NOTFOUND) then
118 CLOSE C;
119 Raise NO_DATA_FOUND;
120 end if;
121 CLOSE C;
122
123 /* Commented this block for shared Service
124 REM OPEN T;
125 REM FETCH T INTO L_TAX_ID;
126 REM CLOSE T;
127
128 OPEN O;
129 FETCH O INTO L_ORG_ID;
130 CLOSE O;
131
132 If x_output_tax_code IS NOT NULL
133 Then
134 INSERT INTO PA_EVENT_TYPE_OUS
135 ( EVENT_TYPE,
136 OUTPUT_TAX_CLASSIFICATION_CODE,
137 LAST_UPDATE_DATE,
138 CREATION_DATE,
139 LAST_UPDATED_BY,
140 CREATED_BY,
141 LAST_UPDATE_LOGIN,
142 ORG_ID
143 )
144 VALUES
145 ( X_Event_Type,
146 X_OUTPUT_TAX_CODE,
147 X_Last_Update_Date,
148 X_Creation_Date,
149 X_Last_Updated_By,
150 X_Created_By,
151 X_Last_Update_Login,
152 L_ORG_ID );
153 END IF;*/
154
155 END Insert_Row;
156
157
158 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
159
160 X_Event_Type VARCHAR2,
161 X_Start_Date_Active DATE,
162 X_Event_Type_Classification VARCHAR2,
163 X_End_Date_Active DATE,
164 X_Description VARCHAR2,
165 X_Revenue_Category_Code VARCHAR2,
166 /* X_Output_tax_code VARCHAR2, Shared Services*/
167 X_Attribute_Category VARCHAR2,
168 X_Attribute1 VARCHAR2,
169 X_Attribute2 VARCHAR2,
170 X_Attribute3 VARCHAR2,
171 X_Attribute4 VARCHAR2,
172 X_Attribute5 VARCHAR2,
173 X_Attribute6 VARCHAR2,
174 X_Attribute7 VARCHAR2,
175 X_Attribute8 VARCHAR2,
176 X_Attribute9 VARCHAR2,
177 X_Attribute10 VARCHAR2,
178 X_Attribute11 VARCHAR2,
179 X_Attribute12 VARCHAR2,
180 X_Attribute13 VARCHAR2,
181 X_Attribute14 VARCHAR2,
182 X_Attribute15 VARCHAR2
183 ) IS
184 --hsiu: modified cursor
188 WHERE ET.rowid = RTRIM(X_Rowid)
185 CURSOR C IS
186 SELECT ET.*
187 FROM PA_EVENT_TYPES ET
189 FOR UPDATE of ET.Event_Type NOWAIT;
190
191 /*Shared Services
192 SELECT ET.*, EOUS.OUTPUT_TAX_CLASSIFICATION_CODE OUTPUT_TAX_CODE
193 FROM PA_EVENT_TYPES ET,
194 PA_EVENT_TYPE_OUS EOUS
195 WHERE ET.rowid = RTRIM(X_Rowid)
196 and RTRIM(ET.EVENT_TYPE) = RTRIM(EOUS.EVENT_TYPE(+))
197 FOR UPDATE of ET.Event_Type NOWAIT;*/
198 Recinfo C%ROWTYPE;
199
200
201 BEGIN
202 OPEN C;
203 FETCH C INTO Recinfo;
204 if (C%NOTFOUND) then
205 CLOSE C;
206 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
207 APP_EXCEPTION.Raise_Exception;
208 end if;
209 if (
210
211 (RTRIM(Recinfo.event_type) = RTRIM(X_Event_Type))
212 AND (trunc(Recinfo.start_date_active) = trunc(X_Start_Date_Active))
213 AND (RTRIM(Recinfo.event_type_classification) = RTRIM(X_Event_Type_Classification)
214 )
215 AND ( (trunc(Recinfo.end_date_active) = trunc(X_End_Date_Active))
216 OR ( (Recinfo.end_date_active IS NULL)
217 AND (X_End_Date_Active IS NULL)))
218 AND ( (rtrim(Recinfo.description) = rtrim(X_Description))
219 OR ( (Recinfo.description IS NULL)
220 AND (X_Description IS NULL)))
221 AND ( rtrim(Recinfo.revenue_category_code) = rtrim(X_Revenue_Category_Code))
222 /* Shared Services
223 AND ( (trim(Recinfo.output_tax_code) = rtrim(X_Output_tax_code))
224 OR ( (Recinfo.output_tax_code IS NULL)
225 AND (X_Output_tax_code IS NULL)))*/
226 AND ( (trim(Recinfo.Attribute_Category) = rtrim(X_Attribute_Category))
227 OR ( (Recinfo.Attribute_Category IS NULL)
228 AND (X_Attribute_Category IS NULL)))
229 AND ( (trim(Recinfo.Attribute1) = rtrim(X_Attribute1))
230 OR ( (Recinfo.Attribute1 IS NULL)
231 AND (X_Attribute1 IS NULL)))
232 AND ( (trim(Recinfo.Attribute2) = rtrim(X_Attribute2))
233 OR ( (Recinfo.Attribute2 IS NULL)
234 AND (X_Attribute2 IS NULL)))
235 AND ( (trim(Recinfo.Attribute3) = rtrim(X_Attribute3))
236 OR ( (Recinfo.Attribute3 IS NULL)
237 AND (X_Attribute3 IS NULL)))
238 AND ( (trim(Recinfo.Attribute4) = rtrim(X_Attribute4))
239 OR ( (Recinfo.Attribute4 IS NULL)
240 AND (X_Attribute4 IS NULL)))
241 AND ( (trim(Recinfo.Attribute5) = rtrim(X_Attribute5))
242 OR ( (Recinfo.Attribute5 IS NULL)
243 AND (X_Attribute5 IS NULL)))
244 AND ( (trim(Recinfo.Attribute6) = rtrim(X_Attribute6))
245 OR ( (Recinfo.Attribute6 IS NULL)
246 AND (X_Attribute6 IS NULL)))
247 AND ( (trim(Recinfo.Attribute7) = rtrim(X_Attribute7))
248 OR ( (Recinfo.Attribute7 IS NULL)
249 AND (X_Attribute7 IS NULL)))
250 AND ( (trim(Recinfo.Attribute8) = rtrim(X_Attribute8))
251 OR ( (Recinfo.Attribute8 IS NULL)
252 AND (X_Attribute8 IS NULL)))
253 AND ( (trim(Recinfo.Attribute9) = rtrim(X_Attribute9))
254 OR ( (Recinfo.Attribute9 IS NULL)
255 AND (X_Attribute9 IS NULL)))
256 AND ( (trim(Recinfo.Attribute10) = rtrim(X_Attribute10))
257 OR ( (Recinfo.Attribute10 IS NULL)
258 AND (X_Attribute10 IS NULL)))
259 AND ( (trim(Recinfo.Attribute11) = rtrim(X_Attribute11))
260 OR ( (Recinfo.Attribute11 IS NULL)
261 AND (X_Attribute11 IS NULL)))
262 AND ( (trim(Recinfo.Attribute12) = rtrim(X_Attribute12))
263 OR ( (Recinfo.Attribute12 IS NULL)
264 AND (X_Attribute12 IS NULL)))
265 AND ( (trim(Recinfo.Attribute13) = rtrim(X_Attribute13))
266 OR ( (Recinfo.Attribute13 IS NULL)
267 AND (X_Attribute13 IS NULL)))
268 AND ( (trim(Recinfo.Attribute14) = rtrim(X_Attribute14))
269 OR ( (Recinfo.Attribute14 IS NULL)
270 AND (X_Attribute14 IS NULL)))
271 AND ( (trim(Recinfo.Attribute15) = rtrim(X_Attribute15))
272 OR ( (Recinfo.Attribute15 IS NULL)
273 AND (X_Attribute15 IS NULL)))
274 ) then
275 CLOSE C;
276 return;
277 else
278 CLOSE C;
279 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
280 APP_EXCEPTION.Raise_Exception;
281 end if;
282 END Lock_Row;
283
284
285
286 PROCEDURE Update_Row(X_Rowid VARCHAR2,
287 X_Event_Type VARCHAR2,
288 X_Last_Update_Date DATE,
289 X_Last_Updated_By NUMBER,
290 X_Last_Update_Login NUMBER,
291 X_Start_Date_Active DATE,
292 X_Event_Type_Classification VARCHAR2,
293 X_End_Date_Active DATE,
294 X_Description VARCHAR2,
295 X_Revenue_Category_Code VARCHAR2,
296 /* X_Output_tax_code VARCHAR2, Shared Services*/
297 X_Attribute_Category VARCHAR2,
298 X_Attribute1 VARCHAR2,
299 X_Attribute2 VARCHAR2,
300 X_Attribute3 VARCHAR2,
301 X_Attribute4 VARCHAR2,
302 X_Attribute5 VARCHAR2,
303 X_Attribute6 VARCHAR2,
304 X_Attribute7 VARCHAR2,
305 X_Attribute8 VARCHAR2,
306 X_Attribute9 VARCHAR2,
307 X_Attribute10 VARCHAR2,
308 X_Attribute11 VARCHAR2,
312 X_Attribute15 VARCHAR2
309 X_Attribute12 VARCHAR2,
310 X_Attribute13 VARCHAR2,
311 X_Attribute14 VARCHAR2,
313 ) IS
314
315 /*
316 CURSOR T IS SELECT VAT_TAX_ID
317 FROM PA_OUTPUT_TAX_CODE_SETUP_V
318 WHERE TAX_CODE = X_Output_tax_code ;
319 */--commented by hsiu
320
321 /* Shared Services
322 CURSOR O IS SELECT ORG_ID
323 FROM PA_IMPLEMENTATIONS;*/
324
325 L_TAX_ID NUMBER;
326 L_ORG_ID NUMBER;
327
328 BEGIN
329 UPDATE PA_EVENT_TYPES
330 SET
331 event_type = X_Event_Type,
332 last_update_date = X_Last_Update_Date,
333 last_updated_by = X_Last_Updated_By,
334 last_update_login = X_Last_Update_Login,
335 start_date_active = X_Start_Date_Active,
336 event_type_classification = X_Event_Type_Classification,
337 end_date_active = X_End_Date_Active,
338 description = X_Description,
339 revenue_category_code = X_Revenue_Category_Code,
340 attribute_Category = X_Attribute_Category,
341 attribute1 = X_Attribute1,
342 attribute2 = X_Attribute2,
343 attribute3 = X_Attribute3,
344 attribute4 = X_Attribute4,
345 attribute5 = X_Attribute5,
346 attribute6 = X_Attribute6,
347 attribute7 = X_Attribute7,
348 attribute8 = X_Attribute8,
349 attribute9 = X_Attribute9,
350 attribute10 = X_Attribute10,
351 attribute11 = X_Attribute11,
352 attribute12 = X_Attribute12,
353 attribute13 = X_Attribute13,
354 attribute14 = X_Attribute14,
355 attribute15 = X_Attribute15
356 WHERE rowid = X_Rowid;
357
358 if (SQL%NOTFOUND) then
359 Raise NO_DATA_FOUND;
360 end if;
361
362 /* Get Tax Id for X_Output_tax_code */
363 /*
364 OPEN T;
365 FETCH T INTO L_TAX_ID;
366 CLOSE T;
367 */
368
369 /* Commented for shared Service
370 UPDATE PA_EVENT_TYPE_OUS
371 SET OUTPUT_TAX_CLASSIFICATION_CODE = x_output_tax_code,
372 LAST_UPDATE_DATE = X_Last_Update_Date,
373 LAST_UPDATED_BY = X_Last_Updated_By,
374 LAST_UPDATE_LOGIN = X_Last_Update_Login
375 WHERE EVENT_TYPE = X_Event_Type;
376
377 IF (SQL%ROWCOUNT = 0 )
378 AND (X_OUTPUT_TAX_CODE IS NOT NULL)
379 THEN
380 OPEN O;
381 FETCH O INTO L_ORG_ID;
382 CLOSE O;
383
384 INSERT INTO PA_EVENT_TYPE_OUS
385 ( EVENT_TYPE,
386 OUTPUT_TAX_CLASSIFICATION_CODE,
387 LAST_UPDATE_DATE,
388 CREATION_DATE,
389 LAST_UPDATED_BY,
390 CREATED_BY,
391 LAST_UPDATE_LOGIN,
392 ORG_ID
393 )
394 VALUES
395 ( X_Event_Type,
396 X_OUTPUT_TAX_CODE,
397 X_Last_Update_Date,
398 X_Last_Update_Date,
399 X_Last_Updated_By,
400 X_Last_Updated_By,
401 X_Last_Update_Login,
402 L_ORG_ID );
403
404 END IF;*/
405
406 END Update_Row;
407
408 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
409 BEGIN /* Rearranged the DELETE statements for bug 3182079 */
410 BEGIN
411 /* Delete record from PA_EVENT_TYPE_OUS */
412 DELETE FROM PA_EVENT_TYPE_OUS_ALL
413 WHERE EVENT_TYPE = ( SELECT EVENT_TYPE
414 FROM PA_EVENT_TYPES
415 WHERE rowid = X_Rowid);
416 EXCEPTION
417 WHEN NO_DATA_FOUND THEN
418 null;
419 WHEN OTHERS THEN
420 raise ;
421 END;
422 BEGIN
423
424 DELETE FROM PA_EVENT_TYPES
425 WHERE rowid = X_Rowid;
426
427 if (SQL%NOTFOUND) then
428 Raise NO_DATA_FOUND;
429 end if;
430 EXCEPTION
431 WHEN OTHERS THEN
432 raise ;
433 END;
434 END Delete_Row;
435
436 END PA_EVENT_TYPES_PKG;