[Home] [Help]
PACKAGE BODY: APPS.AHL_APPROVAL_API_PKG
Source
1 PACKAGE BODY AHL_APPROVAL_API_PKG as
2 /* $Header: AHLLAPIB.pls 115.2 2002/12/04 00:03:53 ssurapan noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_APPROVAL_API_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_API_USED_BY in VARCHAR2,
8 X_APPROVAL_OBJECT_TYPE in VARCHAR2,
9 X_APPROVAL_TYPE in VARCHAR2,
10 X_ACTIVITY_TYPE in VARCHAR2,
11 X_PACKAGE_NAME in VARCHAR2,
12 X_PROCEDURE_NAME in VARCHAR2,
13 X_CREATION_DATE in DATE,
14 X_CREATED_BY in NUMBER,
15 X_LAST_UPDATE_DATE in DATE,
16 X_LAST_UPDATED_BY in NUMBER,
17 X_LAST_UPDATE_LOGIN in NUMBER
18 ) is
19 cursor C is select ROWID from AHL_APPROVAL_API
20 where APPROVAL_API_ID = X_APPROVAL_API_ID
21 ;
22 begin
23 insert into ahl_approval_api (
24 APPROVAL_API_ID,
25 OBJECT_VERSION_NUMBER,
26 API_USED_BY,
27 APPROVAL_OBJECT_TYPE,
28 APPROVAL_TYPE,
29 ACTIVITY_TYPE,
30 PACKAGE_NAME,
31 PROCEDURE_NAME,
32 CREATION_DATE,
33 CREATED_BY,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 LAST_UPDATE_LOGIN
37 ) values (
38 X_APPROVAL_API_ID,
39 X_OBJECT_VERSION_NUMBER,
40 X_API_USED_BY,
41 X_APPROVAL_OBJECT_TYPE,
42 X_APPROVAL_TYPE,
43 X_ACTIVITY_TYPE,
44 X_PACKAGE_NAME,
45 X_PROCEDURE_NAME,
46 X_CREATION_DATE,
47 X_CREATED_BY,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_BY,
50 X_LAST_UPDATE_LOGIN
51 );
52
53 open c;
54 fetch c into X_ROWID;
55 if (c%notfound) then
56 close c;
57 raise no_data_found;
58 end if;
59 close c;
60
61 end INSERT_ROW;
62
63 procedure UPDATE_ROW
64 (
65 X_APPROVAL_API_ID in NUMBER,
66 X_OBJECT_VERSION_NUMBER in NUMBER,
67 X_API_USED_BY in VARCHAR2,
68 X_APPROVAL_OBJECT_TYPE in VARCHAR2,
69 X_APPROVAL_TYPE in VARCHAR2,
70 X_ACTIVITY_TYPE in VARCHAR2,
71 X_PACKAGE_NAME in VARCHAR2,
72 X_PROCEDURE_NAME in VARCHAR2,
73 X_LAST_UPDATE_DATE in DATE,
74 X_LAST_UPDATED_BY in NUMBER,
75 X_LAST_UPDATE_LOGIN in NUMBER
76 ) is
77 begin
78 update AHL_APPROVAL_API set
79 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
80 API_USED_BY = X_API_USED_BY,
81 APPROVAL_OBJECT_TYPE = X_APPROVAL_OBJECT_TYPE,
82 APPROVAL_TYPE = X_APPROVAL_TYPE,
83 ACTIVITY_TYPE = X_ACTIVITY_TYPE,
84 PACKAGE_NAME = X_PACKAGE_NAME,
85 PROCEDURE_NAME = X_PROCEDURE_NAME,
86 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
87 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
88 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
89 where APPROVAL_API_ID = X_APPROVAL_API_ID;
90
91 if (sql%notfound) then
92 raise no_data_found;
93 end if;
94
95 end UPDATE_ROW;
96
97 procedure DELETE_ROW
98 (
99 X_APPROVAL_API_ID in NUMBER
100 )
101 is
102 begin
103 delete from AHL_APPROVAL_API
104 where APPROVAL_API_ID = X_APPROVAL_API_ID;
105
106 if (sql%notfound) then
107 raise no_data_found;
108 end if;
109
110 end DELETE_ROW;
111
112 procedure LOAD_ROW(
113 X_APPROVAL_API_ID in NUMBER,
114 X_API_USED_BY in VARCHAR2,
115 X_APPROVAL_OBJECT_TYPE in VARCHAR2,
116 X_APPROVAL_TYPE in VARCHAR2,
117 X_ACTIVITY_TYPE in VARCHAR2,
118 X_PACKAGE_NAME in VARCHAR2,
119 X_PROCEDURE_NAME in VARCHAR2,
120 X_OWNER in VARCHAR2
121 )
122 IS
123 l_user_id number := 0;
124 l_obj_verno number;
125 l_dummy_char varchar2(1);
126 l_row_id varchar2(100);
127 l_api_id number;
128
129 cursor c_obj_verno is
130 select object_version_number
131 from AHL_APPROVAL_API
132 where approval_api_id = X_APPROVAL_API_ID;
133
134 cursor c_chk_api_exists is
135 select 'x'
136 from AHL_APPROVAL_API
137 where approval_api_id = X_APPROVAL_API_ID;
138
139 cursor c_get_api_id is
140 select ahl_approval_api_s.nextval
141 from dual;
142
143
144 BEGIN
145
146 if X_OWNER = 'SEED' then
147 l_user_id := 1;
148 end if;
149
150 open c_chk_api_exists;
151 fetch c_chk_api_exists into l_dummy_char;
152 if c_chk_api_exists%notfound
153 then
154 close c_chk_api_exists;
155
156 if X_APPROVAL_API_ID is null then
157 open c_get_api_id;
158 fetch c_get_api_id into l_api_id;
159 close c_get_api_id;
160 else
161 l_api_id := X_APPROVAL_API_ID;
162 end if ;
163
164 l_obj_verno := 1;
165
166 AHL_APPROVAL_API_PKG.INSERT_ROW (
167 X_ROWID => l_row_id,
168 X_APPROVAL_API_ID => l_api_id,
169 X_OBJECT_VERSION_NUMBER => l_obj_verno,
170 X_API_USED_BY => X_API_USED_BY ,
171 X_APPROVAL_OBJECT_TYPE => X_APPROVAL_OBJECT_TYPE ,
172 X_APPROVAL_TYPE => X_APPROVAL_TYPE ,
173 X_ACTIVITY_TYPE => X_ACTIVITY_TYPE ,
174 X_PACKAGE_NAME => X_PACKAGE_NAME ,
175 X_PROCEDURE_NAME => X_PROCEDURE_NAME ,
176 X_CREATION_DATE => SYSDATE,
177 X_CREATED_BY => l_user_id,
178 X_LAST_UPDATE_DATE => SYSDATE,
179 X_LAST_UPDATED_BY => l_user_id,
180 X_LAST_UPDATE_LOGIN => 0
181 );
182
183
184 else
185 close c_chk_api_exists;
186 open c_obj_verno;
187 fetch c_obj_verno into l_obj_verno;
188 close c_obj_verno;
189
190
191
192 AHL_APPROVAL_API_PKG.UPDATE_ROW (
193 X_APPROVAL_API_ID => X_APPROVAL_API_ID,
194 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
195 X_API_USED_BY => X_API_USED_BY ,
196 X_APPROVAL_OBJECT_TYPE => X_APPROVAL_OBJECT_TYPE ,
197 X_APPROVAL_TYPE => X_APPROVAL_TYPE ,
198 X_ACTIVITY_TYPE => X_ACTIVITY_TYPE ,
199 X_PACKAGE_NAME => X_PACKAGE_NAME ,
200 X_PROCEDURE_NAME => X_PROCEDURE_NAME ,
201 X_LAST_UPDATE_DATE => SYSDATE,
202 X_LAST_UPDATED_BY => l_user_id,
203 X_LAST_UPDATE_LOGIN => 0
204 );
205
206
207 end if;
208
209 END LOAD_ROW ;
210
211
212 end AHL_APPROVAL_API_PKG;