eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JTF_PC_HIERARCHIES_PKG

Source


1 Package Body JTF_PC_HIERARCHIES_PKG AS
2 /*$Header: jtfpjphb.pls 120.2 2005/08/18 22:54:58 stopiwal ship $*/
3 
4 procedure INSERT_ROW (
5   X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6   X_NODE_ID in NUMBER,
7   X_NODE_TYPE in VARCHAR2,
8   X_NODE_REFERENCE in NUMBER,
9   X_TOP_NODE_ID  in NUMBER,
10   X_PARENT_NODE_ID in NUMBER,
11   X_LEVEL_NUMBER in NUMBER,
12   X_ACTIVE in VARCHAR2,
13   X_ORG_ID in NUMBER DEFAULT NULL,
14   X_DEPENDENT in VARCHAR2 DEFAULT NULL,
15   X_START_DATE_EFFECTIVE in DATE,
16   X_END_DATE_EFFECTIVE in DATE DEFAULT NULL,
17   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
18   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
19   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
20   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
21   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
22   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
23   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
24   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
25   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
26   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
27   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
28   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
29   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
30   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
31   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
32   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
33   X_CREATION_DATE in DATE,
34   X_CREATED_BY in NUMBER,
35   X_LAST_UPDATE_DATE in DATE,
36   X_LAST_UPDATED_BY in NUMBER,
37   X_LAST_UPDATE_LOGIN in NUMBER DEFAULT NULL
38 ) is
39 
40   CURSOR C is SELECT rowid FROM JTF_PC_HIERARCHIES
41               WHERE  NODE_ID = X_Node_Id;
42 begin
43 
44 INSERT INTO jtf_pc_hierarchies (
45 NODE_ID,
46 NODE_TYPE,
47 NODE_REFERENCE,
48 TOP_NODE_ID,
49 PARENT_NODE_ID,
50 OBJECT_VERSION_NUMBER,
51 LEVEL_NUMBER,
52 ACTIVE,
53 ORG_ID,
54 DEPENDENT,
55 START_DATE_EFFECTIVE,
56 END_DATE_EFFECTIVE,
57 CREATED_BY,
58 CREATION_DATE,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_DATE,
61 LAST_UPDATE_LOGIN,
62 ATTRIBUTE_CATEGORY,
63 ATTRIBUTE1,
64 ATTRIBUTE2,
65 ATTRIBUTE3,
66 ATTRIBUTE4,
67 ATTRIBUTE5,
68 ATTRIBUTE6,
69 ATTRIBUTE7,
70 ATTRIBUTE8,
71 ATTRIBUTE9,
72 ATTRIBUTE10,
73 ATTRIBUTE11,
74 ATTRIBUTE12,
75 ATTRIBUTE13,
76 ATTRIBUTE14,
77 ATTRIBUTE15)
78 VALUES (
79 X_NODE_ID,
80 X_NODE_TYPE,
81 X_NODE_REFERENCE,
82 X_TOP_NODE_ID,
83 X_PARENT_NODE_ID,
84 1,
85 X_LEVEL_NUMBER,
86 X_ACTIVE,
87 X_ORG_ID,
88 X_DEPENDENT,
89 X_START_DATE_EFFECTIVE,
90 X_END_DATE_EFFECTIVE,
91 X_CREATED_BY,
92 X_CREATION_DATE,
93 X_LAST_UPDATED_BY,
94 X_LAST_UPDATE_DATE,
95 X_LAST_UPDATE_LOGIN,
96 X_ATTRIBUTE_CATEGORY,
97 X_ATTRIBUTE1,
98 X_ATTRIBUTE2,
99 X_ATTRIBUTE3,
100 X_ATTRIBUTE4,
101 X_ATTRIBUTE5,
102 X_ATTRIBUTE6,
103 X_ATTRIBUTE7,
104 X_ATTRIBUTE8,
105 X_ATTRIBUTE9,
106 X_ATTRIBUTE10,
107 X_ATTRIBUTE11,
108 X_ATTRIBUTE12,
109 X_ATTRIBUTE13,
110 X_ATTRIBUTE14,
111 X_ATTRIBUTE15
112   );
113 
114   OPEN C;
115   FETCH C INTO X_Rowid;
116   if (C%NOTFOUND) then
117      CLOSE C;
118      Raise NO_DATA_FOUND;
119   end if;
120   CLOSE C;
121   select count(node_id) into X_Rowid from jtf_pc_hierarchies;
122 
123 end INSERT_ROW;
124 
125 procedure LOCK_ROW (
126   X_NODE_ID in NUMBER,
127   X_NODE_TYPE in VARCHAR2,
128   X_NODE_REFERENCE in NUMBER,
129   X_TOP_NODE_ID  in NUMBER,
130   X_PARENT_NODE_ID in NUMBER,
131   X_LEVEL_NUMBER in NUMBER,
132   X_ACTIVE in VARCHAR2,
133   X_ORG_ID in NUMBER DEFAULT NULL,
134   X_DEPENDENT in VARCHAR2 DEFAULT NULL,
135   X_START_DATE_EFFECTIVE in DATE,
136   X_END_DATE_EFFECTIVE in DATE DEFAULT NULL,
137   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
138   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
139   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
140   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
141   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
142   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
143   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
144   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
145   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
146   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
147   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
148   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
149   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
150   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
151   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
152   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL
153 ) is
154 
155   CURSOR C is SELECT *
156               FROM   jtf_pc_hierarchies
157               WHERE  node_id = X_Node_Id
158               FOR UPDATE of node_id NOWAIT;
159   Recinfo C%ROWTYPE;
160 
161 begin
162 
163   OPEN C;
164   FETCH C INTO Recinfo;
165   if (C%NOTFOUND) then
166       CLOSE C;
167       FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
168       APP_EXCEPTION.Raise_Exception;
169   end if;
170   CLOSE C;
171   if (    (Recinfo.node_id = X_Node_Id)
172       AND (Recinfo.Node_Type = X_Node_Type)
173       AND (Recinfo.Node_Reference = X_Node_Reference)
174       AND (Recinfo.Top_Node_Id = X_Top_Node_Id)
175       AND (Recinfo.Parent_Node_Id = X_Parent_Node_Id)
176       AND (Recinfo.Active = X_Active)
177       AND (Recinfo.Level_Number = X_Level_Number)
178       AND ((Recinfo.Dependent = X_Dependent)
179           OR ((Recinfo.Dependent IS NULL)
180           AND (X_Dependent IS NULL)))
181       AND ((Recinfo.Org_Id = X_Org_Id)
182           OR ((Recinfo.Org_Id IS NULL)
183           AND (X_Org_Id IS NULL)))
184       AND (Recinfo.Start_Date_Effective = X_Start_Date_Effective)
185       AND ((Recinfo.End_Date_Effective = X_End_Date_Effective)
186           OR ((Recinfo.End_Date_Effective IS NULL)
187           AND (X_End_Date_Effective IS NULL)))
188       AND ((Recinfo.Attribute1 = X_Attribute1)
189           OR ((Recinfo.Attribute1 IS NULL)
190           AND (X_Attribute1 IS NULL)))
191       AND ((Recinfo.Attribute2 = X_Attribute2)
192           OR ((Recinfo.Attribute2 IS NULL)
193           AND (X_Attribute2 IS NULL)))
194       AND ((Recinfo.Attribute3 = X_Attribute3)
195           OR ((Recinfo.Attribute3 IS NULL)
196           AND (X_Attribute3 IS NULL)))
197       AND ((Recinfo.Attribute4 = X_Attribute4)
198           OR ((Recinfo.Attribute4 IS NULL)
199           AND (X_Attribute4 IS NULL)))
200       AND ((Recinfo.Attribute5 = X_Attribute5)
201           OR ((Recinfo.Attribute5 IS NULL)
202           AND (X_Attribute5 IS NULL)))
203       AND ((Recinfo.Attribute6 = X_Attribute6)
204           OR ((Recinfo.Attribute6 IS NULL)
205           AND (X_Attribute6 IS NULL)))
206       AND ((Recinfo.Attribute7 = X_Attribute7)
207           OR ((Recinfo.Attribute7 IS NULL)
208           AND (X_Attribute7 IS NULL)))
209       AND ((Recinfo.Attribute8 = X_Attribute8)
210           OR ((Recinfo.Attribute8 IS NULL)
211           AND (X_Attribute8 IS NULL)))
212       AND ((Recinfo.Attribute9 = X_Attribute9)
213           OR ((Recinfo.Attribute9 IS NULL)
214           AND (X_Attribute9 IS NULL)))
215       AND ((Recinfo.Attribute10 = X_Attribute10)
216           OR ((Recinfo.Attribute10 IS NULL)
217           AND (X_Attribute10 IS NULL)))
218       AND ((Recinfo.Attribute11 = X_Attribute11)
219           OR ((Recinfo.Attribute11 IS NULL)
220           AND (X_Attribute11 IS NULL)))
221       AND ((Recinfo.Attribute12 = X_Attribute12)
222           OR ((Recinfo.Attribute12 IS NULL)
223           AND (X_Attribute12 IS NULL)))
224       AND ((Recinfo.Attribute13 = X_Attribute13)
225           OR ((Recinfo.Attribute13 IS NULL)
226           AND (X_Attribute13 IS NULL)))
227       AND ((Recinfo.Attribute14 = X_Attribute14)
228           OR ((Recinfo.Attribute14 IS NULL)
229           AND (X_Attribute14 IS NULL)))
230       AND ((Recinfo.Attribute15 = X_Attribute15)
231           OR ((Recinfo.Attribute15 IS NULL)
232           AND (X_Attribute15 IS NULL)))
233       AND ((Recinfo.Attribute_Category = X_Attribute_Category)
234           OR ((Recinfo.Attribute_Category IS NULL)
235           AND (X_Attribute_Category IS NULL)))
236      ) then
237       return;
238   else
239       FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
240       APP_EXCEPTION.Raise_Exception;
241   end if;
242 
243 end LOCK_ROW;
244 
245 procedure UPDATE_ROW (
246   X_NODE_ID in NUMBER,
247   X_NODE_TYPE in VARCHAR2,
248   X_NODE_REFERENCE in NUMBER,
249   X_TOP_NODE_ID  in NUMBER,
250   X_PARENT_NODE_ID in NUMBER,
251   X_LEVEL_NUMBER in NUMBER,
252   X_ACTIVE in VARCHAR2,
253   X_ORG_ID in NUMBER DEFAULT NULL,
254   X_DEPENDENT in VARCHAR2 DEFAULT NULL,
255   X_START_DATE_EFFECTIVE in DATE,
256   X_END_DATE_EFFECTIVE in DATE DEFAULT NULL,
257   X_OBJECT_VERSION_NUMBER in NUMBER,
258   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
259   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
260   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
261   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
262   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
263   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
264   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
265   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
266   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
267   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
268   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
269   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
270   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
271   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
272   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
273   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
274   X_LAST_UPDATE_DATE in DATE,
275   X_LAST_UPDATED_BY in NUMBER,
276   X_LAST_UPDATE_LOGIN in NUMBER DEFAULT NULL
277 ) is
278 
279 begin
280 
281   UPDATE jtf_pc_hierarchies
282   SET    node_type = X_Node_Type,
283          node_reference = X_Node_Reference,
284          top_node_id = X_Top_Node_Id,
285          parent_node_id = X_Parent_Node_Id,
286          level_number = X_Level_Number,
287          active = X_Active,
288          org_id = X_Org_Id,
289          dependent = X_Dependent,
290          start_date_effective = X_Start_Date_Effective,
291          end_date_effective = X_End_Date_Effective,
292          object_version_number = X_Object_Version_Number + 1,
293          attribute_category = X_Attribute_Category,
294          attribute1 = X_Attribute1,
295          attribute2 = X_Attribute2,
296          attribute3 = X_Attribute3,
297          attribute4 = X_Attribute4,
298          attribute5 = X_Attribute5,
299          attribute6 = X_Attribute6,
300          attribute7 = X_Attribute7,
301          attribute8 = X_Attribute8,
302          attribute9 = X_Attribute9,
303          attribute10 = X_Attribute10,
304          attribute11 = X_Attribute11,
305          attribute12 = X_Attribute12,
306          attribute13 = X_Attribute13,
307          attribute14 = X_Attribute14,
308          attribute15 = X_Attribute15,
309          last_update_date = X_Last_Update_Date,
310          last_updated_by = X_Last_Updated_By,
311          last_update_login = X_Last_Update_Login
312   WHERE  node_id = X_Node_Id
313   AND object_version_number = X_Object_Version_Number;
314 
315   if (SQL%NOTFOUND) then
316      Raise NO_DATA_FOUND;
317   end if;
318 
319 end UPDATE_ROW;
320 
321 procedure DELETE_ROW (
322   X_NODE_ID in NUMBER,
323   X_OBJECT_VERSION_NUMBER in NUMBER
324 ) is
325 
326 begin
327 
328   DELETE FROM jtf_pc_hierarchies
329   WHERE  node_id = X_Node_Id
330   AND object_version_number = X_Object_Version_Number;
331 
332   if (SQL%NOTFOUND) then
333      Raise NO_DATA_FOUND;
334   end if;
335 
336 end DELETE_ROW;
337 
338 /*procedure LOAD_ROW (
339   X_NODE_ID in NUMBER,
340   X_NODE_TYPE in VARCHAR2,
341   X_NODE_REFERENCE in NUMBER,
342   X_TOP_NODE_ID  in NUMBER,
343   X_PARENT_NODE_ID in NUMBER,
344   X_LEVEL_NUMBER in NUMBER,
345   X_ACTIVE in VARCHAR2,
346   X_ORG_ID in NUMBER DEFAULT NULL,
347   X_DEPENDENT in VARCHAR2 DEFAULT NULL,
348   X_START_DATE_EFFECTIVE in DATE,
349   X_END_DATE_EFFECTIVE in DATE DEFAULT NULL,
350   X_OBJECT_VERSION_NUMBER in NUMBER,
351   X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
352   X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
353   X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
354   X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
355   X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
356   X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
357   X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
358   X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
359   X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
360   X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
361   X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
362   X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
363   X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
364   X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
365   X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
366   X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL
367 ) is
368 
369   h_record_exists       number(15);
370 
371   user_id               number;
372   row_id                varchar2(64);
373 
374 begin
375 
376      user_id := 1;
377 
378   select count(*)
379   into   h_record_exists
380   from   jtf_pc_hierarchies
381   where  node_id = X_Node_Id;
382 
383   if (h_record_exists > 0) then
384      jtf_pc_hierarchies_pkg.update_row (
385 	X_Node_Id			=> X_Node_Id,
386     X_Node_Type         => X_Node_Type,
387     X_Node_Reference    => X_Node_Reference,
388 	X_Top_Node_Id			=> X_Top_Node_Id,
389 	X_Parent_Node_Id		=> X_Parent_Node_Id,
390     X_Level_Number          => X_Level_Number,
391     X_Org_Id                => X_Org_Id,
392     X_Dependent             => X_Dependent,
393     X_Active                => X_Active,
394 	X_Start_Date_Effective	=> X_Start_Date_Effective,
395 	X_End_Date_Effective	=> X_End_Date_Effective,
396 	X_Attribute1			=> X_Attribute1,
397 	X_Attribute2			=> X_Attribute2,
398 	X_Attribute3			=> X_Attribute3,
399 	X_Attribute4			=> X_Attribute4,
400 	X_Attribute5			=> X_Attribute5,
401 	X_Attribute6			=> X_Attribute6,
402 	X_Attribute7			=> X_Attribute7,
403 	X_Attribute8			=> X_Attribute8,
404 	X_Attribute9			=> X_Attribute9,
405 	X_Attribute10			=> X_Attribute10,
406 	X_Attribute11			=> X_Attribute11,
407 	X_Attribute12			=> X_Attribute12,
408 	X_Attribute13			=> X_Attribute13,
409 	X_Attribute14			=> X_Attribute14,
410 	X_Attribute15			=> X_Attribute15,
411 	X_Attribute_Category	=> X_Attribute_Category,
412     X_Object_Version_Number => X_Object_Version_Number,
413 	X_Last_Update_Date		=> sysdate,
414 	X_Last_Updated_By		=> user_id,
415 	X_Last_Update_Login		=> 0
416      );
417   else
418      jtf_pc_hierarchies_pkg.insert_row (
419 	X_Rowid				=> row_id,
420 	X_Node_Id			=> X_Node_Id,
421     X_Node_Type         => X_Node_Type,
422     X_Node_Reference    => X_Node_Reference,
423 	X_Top_Node_Id			=> X_Top_Node_Id,
424 	X_Parent_Node_Id		=> X_Parent_Node_Id,
425     X_Level_Number          => X_Level_Number,
426     X_Org_Id                => X_Org_Id,
427     X_Dependent             => X_Dependent,
428     X_Active                => X_Active,
429 	X_Start_Date_Effective	=> X_Start_Date_Effective,
430 	X_End_Date_Effective	=> X_End_Date_Effective,
431 	X_Attribute1			=> X_Attribute1,
432 	X_Attribute2			=> X_Attribute2,
433 	X_Attribute3			=> X_Attribute3,
434 	X_Attribute4			=> X_Attribute4,
435 	X_Attribute5			=> X_Attribute5,
436 	X_Attribute6			=> X_Attribute6,
437 	X_Attribute7			=> X_Attribute7,
438 	X_Attribute8			=> X_Attribute8,
439 	X_Attribute9			=> X_Attribute9,
440 	X_Attribute10			=> X_Attribute10,
441 	X_Attribute11			=> X_Attribute11,
442 	X_Attribute12			=> X_Attribute12,
443 	X_Attribute13			=> X_Attribute13,
444 	X_Attribute14			=> X_Attribute14,
445 	X_Attribute15			=> X_Attribute15,
446 	X_Attribute_Category	=> X_Attribute_Category,
447 	X_Creation_Date			=> sysdate,
448 	X_Created_By			=> user_id,
449 	X_Last_Update_Date		=> sysdate,
450 	X_Last_Updated_By		=> user_id,
451 	X_Last_Update_Login		=> 0
452      );
453   end if;
454 
455 end LOAD_ROW;*/
456 
457 END JTF_PC_HIERARCHIES_PKG;