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.AHL_APPROVERS_PKG

Source


1 package body AHL_APPROVERS_PKG as
2 /*$Header: AHLLAPPB.pls 115.6 2002/12/04 01:21:55 ssurapan noship $*/
3 
4 procedure INSERT_ROW
5 (
6   X_ROWID in out NOCOPY VARCHAR2,
7   X_APPROVAL_APPROVER_ID in NUMBER,
8   X_OBJECT_VERSION_NUMBER in NUMBER,
9   X_APPROVAL_RULE_ID in NUMBER,
10   X_APPROVER_TYPE_CODE in VARCHAR2,
11   X_APPROVER_SEQUENCE in NUMBER,
12   X_APPROVER_ID in NUMBER,
13   --X_ACTIVE_START_DATE IN DATE,
14   --X_ACTIVE_END_DATE IN DATE,
15   X_LAST_UPDATE_DATE IN DATE,
16   X_LAST_UPDATED_BY IN NUMBER,
17   X_CREATION_DATE IN DATE,
18   X_CREATED_BY IN NUMBER,
19   X_LAST_UPDATE_LOGIN IN NUMBER,
20   X_ATTRIBUTE_CATEGORY IN VARCHAR2,
21   X_ATTRIBUTE1 IN VARCHAR2,
22   X_ATTRIBUTE2 IN VARCHAR2,
23   X_ATTRIBUTE3 IN VARCHAR2,
24   X_ATTRIBUTE4 IN VARCHAR2,
25   X_ATTRIBUTE5 IN VARCHAR2,
26   X_ATTRIBUTE6 IN VARCHAR2,
27   X_ATTRIBUTE7 IN VARCHAR2,
28   X_ATTRIBUTE8 IN VARCHAR2,
29   X_ATTRIBUTE9 IN VARCHAR2,
30   X_ATTRIBUTE10 IN VARCHAR2,
31   X_ATTRIBUTE11 IN VARCHAR2,
32   X_ATTRIBUTE12 IN VARCHAR2,
33   X_ATTRIBUTE13 IN VARCHAR2,
34   X_ATTRIBUTE14 IN VARCHAR2,
35   X_ATTRIBUTE15 IN VARCHAR2
36 )
37 is
38 cursor C is select ROWID from AHL_APPROVERS where
39             APPROVAL_APPROVER_ID = X_APPROVAL_APPROVER_ID;
40 begin
41 insert into AHL_APPROVERS
42 (
43   APPROVAL_APPROVER_ID,
44   OBJECT_VERSION_NUMBER,
45   APPROVAL_RULE_ID,
46   APPROVER_TYPE_CODE,
47   APPROVER_SEQUENCE,
48   APPROVER_ID,
49   --ACTIVE_START_DATE,
50   --ACTIVE_END_DATE,
51   LAST_UPDATE_DATE,
52   LAST_UPDATED_BY,
53   CREATION_DATE,
54   CREATED_BY,
55   LAST_UPDATE_LOGIN,
56   ATTRIBUTE_CATEGORY,
57   ATTRIBUTE1,
58   ATTRIBUTE2,
59   ATTRIBUTE3,
60   ATTRIBUTE4,
61   ATTRIBUTE5,
62   ATTRIBUTE6,
63   ATTRIBUTE7,
64   ATTRIBUTE8,
65   ATTRIBUTE9,
66   ATTRIBUTE10,
67   ATTRIBUTE11,
68   ATTRIBUTE12,
69   ATTRIBUTE13,
70   ATTRIBUTE14,
71   ATTRIBUTE15
72   )
73  values
74 (
75   X_APPROVAL_APPROVER_ID,
76   X_OBJECT_VERSION_NUMBER,
77   X_APPROVAL_RULE_ID,
78   X_APPROVER_TYPE_CODE,
79   X_APPROVER_SEQUENCE,
80   X_APPROVER_ID,
81   --X_ACTIVE_START_DATE,
82   --X_ACTIVE_END_DATE,
83   X_LAST_UPDATE_DATE,
84   X_LAST_UPDATED_BY,
85   X_CREATION_DATE,
86   X_CREATED_BY,
87   X_LAST_UPDATE_LOGIN,
88   X_ATTRIBUTE_CATEGORY,
89   X_ATTRIBUTE1,
90   X_ATTRIBUTE2,
91   X_ATTRIBUTE3,
92   X_ATTRIBUTE4,
93   X_ATTRIBUTE5,
94   X_ATTRIBUTE6,
95   X_ATTRIBUTE7,
96   X_ATTRIBUTE8,
97   X_ATTRIBUTE9,
98   X_ATTRIBUTE10,
99   X_ATTRIBUTE11,
100   X_ATTRIBUTE12,
101   X_ATTRIBUTE13,
102   X_ATTRIBUTE14,
103   X_ATTRIBUTE15
104   );
105 
106  open c;
107  fetch c into X_ROWID;
108  if (c%notfound) then
109     close c;
110  end if;
111  close c;
112 end INSERT_ROW;
113 
114 procedure UPDATE_ROW
115 (
116   X_APPROVAL_APPROVER_ID in NUMBER,
117   X_OBJECT_VERSION_NUMBER in NUMBER,
118   X_APPROVAL_RULE_ID in NUMBER,
119   X_APPROVER_TYPE_CODE in VARCHAR2,
120   X_APPROVER_SEQUENCE in NUMBER,
121   X_APPROVER_ID in NUMBER,
122   --X_ACTIVE_START_DATE IN DATE,
123   --X_ACTIVE_END_DATE IN DATE,
124   X_LAST_UPDATE_DATE IN DATE,
125   X_LAST_UPDATED_BY IN NUMBER,
126   X_LAST_UPDATE_LOGIN IN NUMBER,
127   X_ATTRIBUTE_CATEGORY IN VARCHAR2,
128   X_ATTRIBUTE1 IN VARCHAR2,
129   X_ATTRIBUTE2 IN VARCHAR2,
130   X_ATTRIBUTE3 IN VARCHAR2,
131   X_ATTRIBUTE4 IN VARCHAR2,
132   X_ATTRIBUTE5 IN VARCHAR2,
133   X_ATTRIBUTE6 IN VARCHAR2,
134   X_ATTRIBUTE7 IN VARCHAR2,
135   X_ATTRIBUTE8 IN VARCHAR2,
136   X_ATTRIBUTE9 IN VARCHAR2,
137   X_ATTRIBUTE10 IN VARCHAR2,
138   X_ATTRIBUTE11 IN VARCHAR2,
139   X_ATTRIBUTE12 IN VARCHAR2,
140   X_ATTRIBUTE13 IN VARCHAR2,
141   X_ATTRIBUTE14 IN VARCHAR2,
142   X_ATTRIBUTE15 IN VARCHAR2
143   ) is
144 begin
145   update AHL_APPROVERS set
146   OBJECT_VERSION_NUMBER =  X_OBJECT_VERSION_NUMBER,
147   APPROVAL_RULE_ID =  X_APPROVAL_RULE_ID,
148   APPROVER_TYPE_CODE =  X_APPROVER_TYPE_CODE,
149   APPROVER_SEQUENCE = X_APPROVER_SEQUENCE,
150   APPROVER_ID = X_APPROVER_ID,
151   --ACTIVE_START_DATE =  X_ACTIVE_START_DATE,
152   --ACTIVE_END_DATE =  X_ACTIVE_END_DATE,
153   LAST_UPDATE_DATE =  X_LAST_UPDATE_DATE,
154   LAST_UPDATED_BY =  X_LAST_UPDATED_BY,
155   LAST_UPDATE_LOGIN =  X_LAST_UPDATE_LOGIN,
156   ATTRIBUTE_CATEGORY =  X_ATTRIBUTE_CATEGORY,
157   ATTRIBUTE1 =  X_ATTRIBUTE1,
158   ATTRIBUTE2 =  X_ATTRIBUTE2,
159   ATTRIBUTE3 =  X_ATTRIBUTE3,
160   ATTRIBUTE4 =  X_ATTRIBUTE4,
161   ATTRIBUTE5 =  X_ATTRIBUTE5,
162   ATTRIBUTE6 =  X_ATTRIBUTE6,
163   ATTRIBUTE7 =  X_ATTRIBUTE7,
164   ATTRIBUTE8 =  X_ATTRIBUTE8,
165   ATTRIBUTE9 =  X_ATTRIBUTE9,
166   ATTRIBUTE10 =  X_ATTRIBUTE10,
167   ATTRIBUTE11 =  X_ATTRIBUTE11,
168   ATTRIBUTE12 =  X_ATTRIBUTE12,
169   ATTRIBUTE13 =  X_ATTRIBUTE13,
170   ATTRIBUTE14 =  X_ATTRIBUTE14,
171   ATTRIBUTE15 =  X_ATTRIBUTE15
172   where APPROVAL_APPROVER_ID = X_APPROVAL_APPROVER_ID;
173 
174 if (sql%notfound) then
175   raise no_data_found;
176 end if;
177 
178 end UPDATE_ROW;
179 
180 procedure DELETE_ROW
181 (
182   X_APPROVAL_APPROVER_ID in NUMBER
183 )
184 is
185 begin
186   delete from AHL_APPROVERS
187   where APPROVAL_APPROVER_ID = X_APPROVAL_APPROVER_ID;
188 
189   if (sql%notfound) then
190     raise no_data_found;
191   end if;
192 
193 end DELETE_ROW;
194 
195 
196 PROCEDURE LOAD_ROW (
197   X_APPROVER_ID IN NUMBER,
198   X_APPROVAL_RULE_ID IN NUMBER,
199   X_APPROVAL_APPROVER_ID IN NUMBER,
200   X_APPROVER_TYPE_CODE IN VARCHAR2,
201   X_APPROVER_SEQUENCE IN VARCHAR2,
202   X_OWNER       IN    VARCHAR2,
203   X_ATTRIBUTE_CATEGORY IN VARCHAR2,
204   X_ATTRIBUTE1 IN VARCHAR2,
205   X_ATTRIBUTE2 IN VARCHAR2,
206   X_ATTRIBUTE3 IN VARCHAR2,
207   X_ATTRIBUTE4 IN VARCHAR2,
208   X_ATTRIBUTE5 IN VARCHAR2,
209   X_ATTRIBUTE6 IN VARCHAR2,
210   X_ATTRIBUTE7 IN VARCHAR2,
211   X_ATTRIBUTE8 IN VARCHAR2,
212   X_ATTRIBUTE9 IN VARCHAR2,
213   X_ATTRIBUTE10 IN VARCHAR2,
214   X_ATTRIBUTE11 IN VARCHAR2,
215   X_ATTRIBUTE12 IN VARCHAR2,
216   X_ATTRIBUTE13 IN VARCHAR2,
217   X_ATTRIBUTE14 IN VARCHAR2,
218   X_ATTRIBUTE15 IN VARCHAR2
219   )
220 
221 IS
222   l_user_id     NUMBER := 0;
223   l_obj_verno   NUMBER;
224   l_dummy_char  VARCHAR2(1);
225   l_row_id      VARCHAR2(100);
226   l_approval_approver_id      NUMBER;
227 
228 
229 
230 CURSOR  c_obj_verno IS
231   SELECT  object_version_number
232   FROM    AHL_APPROVERS
233   WHERE   approval_approver_id =  X_APPROVAL_APPROVER_ID;
234 
235 CURSOR c_chk_approver_exists IS
236   SELECT 'x'
237   FROM   AHL_APPROVERS
238   WHERE  approval_approver_id = X_APPROVAL_APPROVER_ID;
239 
240 CURSOR c_get_approver_id IS
241    SELECT ahl_approvers_s.NEXTVAL
242    FROM dual;
243 
244 
245 BEGIN
246 
247   IF X_OWNER = 'SEED' THEN
248      l_user_id := 1;
249  END IF;
250 
251  OPEN c_chk_approver_exists;
252  FETCH c_chk_approver_exists INTO l_dummy_char;
253  IF c_chk_approver_exists%NOTFOUND
254  THEN
255     CLOSE c_chk_approver_exists;
256 
257     IF X_APPROVAL_APPROVER_ID IS NULL THEN
258         OPEN c_get_approver_id;
259         FETCH c_get_approver_id INTO l_approval_approver_id;
260         CLOSE c_get_approver_id;
261     ELSE
262        l_approval_approver_id := X_APPROVAL_APPROVER_ID;
263     END IF ;
264 
265     l_obj_verno := 1;
266 
267 AHL_APPROVERS_PKG.INSERT_ROW (
268   X_ROWID                       => l_row_id,
269   X_APPROVER_ID 		=> X_APPROVER_ID,
270   X_OBJECT_VERSION_NUMBER 	=> l_obj_verno,
271   X_APPROVAL_RULE_ID 		=> X_APPROVAL_RULE_ID ,
272   X_APPROVAL_APPROVER_ID 	=> l_approval_approver_id ,
273   X_APPROVER_TYPE_CODE 		=> X_APPROVER_TYPE_CODE ,
274   X_APPROVER_SEQUENCE 		=> X_APPROVER_SEQUENCE ,
275   X_ATTRIBUTE_CATEGORY 		=> X_ATTRIBUTE_CATEGORY,
276   X_ATTRIBUTE1			=>  X_ATTRIBUTE1,
277   X_ATTRIBUTE2			=>  X_ATTRIBUTE2,
278   X_ATTRIBUTE3			=>  X_ATTRIBUTE3,
279   X_ATTRIBUTE4			=>  X_ATTRIBUTE4,
280   X_ATTRIBUTE5			=>  X_ATTRIBUTE5,
281   X_ATTRIBUTE6			=>  X_ATTRIBUTE6,
282   X_ATTRIBUTE7			=>  X_ATTRIBUTE7,
283   X_ATTRIBUTE8			=>  X_ATTRIBUTE8,
284   X_ATTRIBUTE9			=>  X_ATTRIBUTE9,
285   X_ATTRIBUTE10			=>  X_ATTRIBUTE10,
286   X_ATTRIBUTE11			=>  X_ATTRIBUTE11,
287   X_ATTRIBUTE12			=>  X_ATTRIBUTE12,
288   X_ATTRIBUTE13			=>  X_ATTRIBUTE13,
289   X_ATTRIBUTE14			=>  X_ATTRIBUTE14,
290   X_ATTRIBUTE15			=>  X_ATTRIBUTE15,
291   X_CREATION_DATE 		=> SYSDATE,
292   X_CREATED_BY                  => l_user_id,
293   X_LAST_UPDATE_DATE            => SYSDATE,
294   X_LAST_UPDATED_BY             => l_user_id,
295   X_LAST_UPDATE_LOGIN           => 0
296 
297 );
298 
299 
300 ELSE
301    CLOSE c_chk_approver_exists;
302    OPEN c_obj_verno;
303    FETCH c_obj_verno INTO l_obj_verno;
304    CLOSE c_obj_verno;
305 
306 
307 
308 
309 AHL_APPROVERS_PKG.UPDATE_ROW (
310   X_APPROVER_ID 		=> X_APPROVER_ID,
311   X_OBJECT_VERSION_NUMBER 	=> l_obj_verno + 1,
312   X_APPROVAL_RULE_ID 		=> X_APPROVAL_RULE_ID ,
313   X_APPROVAL_APPROVER_ID 	=> X_APPROVAL_APPROVER_ID ,
314   X_APPROVER_TYPE_CODE 		=> X_APPROVER_TYPE_CODE ,
315   X_APPROVER_SEQUENCE 		=> X_APPROVER_SEQUENCE ,
316   X_ATTRIBUTE_CATEGORY 		=> X_ATTRIBUTE_CATEGORY,
317   X_ATTRIBUTE1			=>  X_ATTRIBUTE1,
318   X_ATTRIBUTE2			=>  X_ATTRIBUTE2,
319   X_ATTRIBUTE3			=>  X_ATTRIBUTE3,
320   X_ATTRIBUTE4			=>  X_ATTRIBUTE4,
321   X_ATTRIBUTE5			=>  X_ATTRIBUTE5,
322   X_ATTRIBUTE6			=>  X_ATTRIBUTE6,
323   X_ATTRIBUTE7			=>  X_ATTRIBUTE7,
324   X_ATTRIBUTE8			=>  X_ATTRIBUTE8,
325   X_ATTRIBUTE9			=>  X_ATTRIBUTE9,
326   X_ATTRIBUTE10			=>  X_ATTRIBUTE10,
327   X_ATTRIBUTE11			=>  X_ATTRIBUTE11,
328   X_ATTRIBUTE12			=>  X_ATTRIBUTE12,
329   X_ATTRIBUTE13			=>  X_ATTRIBUTE13,
330   X_ATTRIBUTE14			=>  X_ATTRIBUTE14,
331   X_ATTRIBUTE15			=>  X_ATTRIBUTE15,
332   X_LAST_UPDATE_DATE            => SYSDATE,
333   X_LAST_UPDATED_BY             => l_user_id,
334   X_LAST_UPDATE_LOGIN           => 0
335 
336 );
337 
338 
339 
340 END IF;
341 
342 END LOAD_ROW ;
343 
344 
345 end AHL_APPROVERS_PKG;