[Home] [Help]
PACKAGE BODY: APPS.HZ_BUS_OBJ_DEFINITIONS_PKG
Source
1 PACKAGE BODY HZ_BUS_OBJ_DEFINITIONS_PKG AS
2 /*$Header: ARHBODTB.pls 120.2 2006/04/22 00:47:47 smattegu noship $ */
3
4 PROCEDURE Insert_Row (
5 x_business_object_code IN OUT NOCOPY VARCHAR2,
6 x_child_bo_code IN OUT NOCOPY VARCHAR2,
7 x_tca_mandated_flag IN VARCHAR2,
8 x_user_mandated_flag IN VARCHAR2,
9 x_root_node_flag IN VARCHAR2,
10 x_entity_name IN OUT NOCOPY VARCHAR2,
11 x_bo_indicator_flag IN VARCHAR2,
12 x_display_flag IN VARCHAR2,
13 x_multiple_flag IN VARCHAR2,
14 x_bo_version_number IN NUMBER,
15 x_creation_date IN DATE,
16 x_created_by IN NUMBER,
17 x_last_update_date IN DATE,
18 x_last_updated_by IN NUMBER,
19 x_last_update_login IN NUMBER,
20 x_object_version_number IN NUMBER
21 ) IS
22 l_success VARCHAR2(1) := 'N';
23 l_code varchar2(1);
24 cursor C1 is select '1' from ar_lookups a
25 where a.lookup_type = 'HZ_BUSINESS_OBJECTS' and
26 a.lookup_code = x_business_object_code;
27 cursor C2 is select '1' from ar_lookups b where
28 b.lookup_type = 'HZ_BUSINESS_OBJECTS' and
29 nvl( x_child_bo_code, b.lookup_code) = b.lookup_code;
30 BEGIN
31 OPEN C1;
32 FETCH C1 INTO l_code;
33 if (c1%notfound) then
34 close c1;
35 --raise no_data_found;
36 raise self_is_null ;
37 end if;
38 CLOSE C1;
39 OPEN C2;
40 FETCH C2 INTO l_code;
41 if (c2%notfound) then
42 close c2;
43 --raise no_data_found;
44 raise self_is_null ;
45 end if;
46 CLOSE C2;
47 INSERT INTO HZ_BUS_OBJ_DEFINITIONS (
48 business_object_code,
49 child_bo_code,
50 tca_mandated_flag,
51 user_mandated_flag,
52 root_node_flag,
53 entity_name,
54 bo_indicator_flag,
55 display_flag,
56 multiple_flag,
57 bo_version_number,
58 creation_date,
59 created_by,
60 last_update_date,
61 last_updated_by,
62 last_update_login,
63 object_version_number
64 )
65 VALUES (
66 x_business_object_code,
67 DECODE(x_child_bo_code,
68 FND_API.G_MISS_CHAR, NULL,
69 x_child_bo_code),
70 x_tca_mandated_flag,
71 x_user_mandated_flag,
72 x_root_node_flag,
73 x_entity_name,
74 x_bo_indicator_flag,
75 x_display_flag,
76 x_multiple_flag,
77 DECODE(x_bo_version_number,
78 FND_API.G_MISS_NUM, NULL,
79 x_bo_version_number),
80 x_creation_date,
81 x_created_by,
82 x_last_update_date,
83 x_last_updated_by,
84 x_last_update_login,
85 x_object_version_number
86 ) ;
87 END Insert_Row;
88 PROCEDURE Update_Row (
89 x_business_object_code IN VARCHAR2,
90 x_child_bo_code IN VARCHAR2,
91 x_tca_mandated_flag IN VARCHAR2,
92 x_user_mandated_flag IN VARCHAR2,
93 x_root_node_flag IN VARCHAR2,
94 x_entity_name IN VARCHAR2,
95 x_bo_indicator_flag IN VARCHAR2,
96 x_display_flag IN VARCHAR2,
97 x_multiple_flag IN VARCHAR2,
98 x_bo_version_number IN NUMBER,
99 x_last_update_date IN DATE,
100 x_last_updated_by IN NUMBER,
101 x_last_update_login IN NUMBER,
102 x_object_version_number IN NUMBER
103 ) IS
104 BEGIN
105 UPDATE HZ_BUS_OBJ_DEFINITIONS
106 SET
107 business_object_code = x_business_object_code,
108 child_bo_code = DECODE(x_child_bo_code,
109 NULL, child_bo_code,
110 FND_API.G_MISS_CHAR, NULL,
111 x_child_bo_code),
112 tca_mandated_flag = DECODE(x_tca_mandated_flag,
113 NULL, tca_mandated_flag,
114 FND_API.G_MISS_CHAR, tca_mandated_flag,
115 x_tca_mandated_flag),
116 user_mandated_flag = DECODE(x_user_mandated_flag,
117 NULL, user_mandated_flag,
118 FND_API.G_MISS_CHAR, user_mandated_flag,
119 x_user_mandated_flag),
120 root_node_flag = DECODE(x_root_node_flag,
121 NULL, root_node_flag,
122 FND_API.G_MISS_CHAR, root_node_flag,
123 x_root_node_flag),
124 entity_name = x_entity_name,
125 bo_indicator_flag = DECODE(x_bo_indicator_flag,
126 NULL, bo_indicator_flag,
127 FND_API.G_MISS_CHAR, bo_indicator_flag,
128 x_bo_indicator_flag),
129 display_flag = DECODE(x_display_flag,
130 NULL, display_flag,
131 FND_API.G_MISS_CHAR, display_flag,
132 x_display_flag),
133 multiple_flag = DECODE(x_multiple_flag,
134 NULL, multiple_flag,
135 FND_API.G_MISS_CHAR, multiple_flag,
136 x_multiple_flag),
137 bo_version_number = DECODE( x_bo_version_number,
138 NULL, bo_version_number,
139 FND_API.G_MISS_NUM, bo_version_number,
140 x_bo_version_number),
141 last_update_date = x_last_update_date,
142 last_updated_by = x_last_updated_by,
143 last_update_login = x_last_update_login,
144 object_version_number = x_object_version_number
145 WHERE business_object_code = x_business_object_code
146 and nvl(child_bo_code, 'N') = nvl( x_child_bo_code, 'N')
147 and entity_name = x_entity_name;
148 IF ( SQL%NOTFOUND ) THEN
149 RAISE NO_DATA_FOUND;
150 END IF;
151 END Update_Row;
152 PROCEDURE LOAD_ROW (
153 x_business_object_code IN OUT NOCOPY VARCHAR2,
154 x_child_bo_code IN OUT NOCOPY VARCHAR2,
155 x_entity_name IN OUT NOCOPY VARCHAR2,
156 x_tca_mandated_flag IN VARCHAR2,
157 x_user_mandated_flag IN VARCHAR2,
158 x_root_node_flag IN VARCHAR2,
159 x_bo_indicator_flag IN VARCHAR2,
160 x_display_flag IN VARCHAR2,
161 x_multiple_flag IN VARCHAR2,
162 x_bo_version_number IN NUMBER,
163 x_object_version_number IN NUMBER,
164 x_last_update_date IN VARCHAR2,
165 X_CUSTOM_MODE IN VARCHAR2,
166 x_owner IN VARCHAR2 ) IS
167
168 row_id varchar2(64);
169 l_object_version_number number;
170 l_bo_version_number number;
171 l_lud date; -- entity owner in file
172 l_luby fnd_user.user_id%TYPE; -- entity update date in file
173 db_luby number; -- entity owner in db
174 db_ludate date; -- entity update date in db
175
176 BEGIN
177 SELECT NVL2(x_object_version_number, 1, x_object_version_number + 1)
178 INTO l_object_version_number FROM dual;
179
180 SELECT NVL2(x_bo_version_number, 1, x_bo_version_number + 1)
181 INTO l_bo_version_number FROM dual;
182
183 l_lud := nvl(to_date(x_last_update_date,'YYYY/MM/DD'), sysdate);
184 l_luby := fnd_load_util.owner_id(X_OWNER);
185
186 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
187 INTO db_luby, db_ludate
188 FROM HZ_BUS_OBJ_DEFINITIONS
189 WHERE business_object_code = x_business_object_code
190 AND nvl(child_bo_code, 'N') = nvl( x_child_bo_code, 'N')
191 AND entity_name = x_entity_name;
192
193 IF (fnd_load_util.upload_test(l_luby, l_lud, db_luby,
194 db_ludate, X_CUSTOM_MODE)) THEN
195 Update_Row (
196 x_business_object_code => x_business_object_code,
197 x_child_bo_code => x_child_bo_code,
198 x_tca_mandated_flag => x_tca_mandated_flag,
199 x_user_mandated_flag => x_user_mandated_flag ,
200 x_root_node_flag => x_root_node_flag ,
201 x_entity_name => x_entity_name ,
202 x_bo_indicator_flag => x_bo_indicator_flag ,
203 x_display_flag => x_display_flag,
204 x_multiple_flag => x_multiple_flag,
205 x_bo_version_number => l_bo_version_number ,
206 x_last_update_date => l_lud,
207 x_last_updated_by => l_luby,
208 x_last_update_login => l_luby,
209 x_object_version_number => l_object_version_number);
210 END IF;
211
212 exception
213 when NO_DATA_FOUND then
214 Insert_Row (
215 x_business_object_code => x_business_object_code,
216 x_child_bo_code => x_child_bo_code,
217 x_tca_mandated_flag => x_tca_mandated_flag,
218 x_user_mandated_flag => x_user_mandated_flag ,
219 x_root_node_flag => x_root_node_flag ,
220 x_entity_name => x_entity_name ,
221 x_bo_indicator_flag => x_bo_indicator_flag ,
222 x_display_flag => x_display_flag,
223 x_multiple_flag => x_multiple_flag,
224 x_bo_version_number => l_bo_version_number ,
225 x_creation_date => l_lud,
226 x_created_by => l_luby,
227 x_last_update_date => l_lud,
228 x_last_updated_by => l_luby,
229 x_last_update_login => l_luby,
230 x_object_version_number => l_object_version_number);
231 end LOAD_ROW;
232 PROCEDURE Select_Row (
233 x_business_object_code IN OUT NOCOPY VARCHAR2,
234 x_child_bo_code IN OUT NOCOPY VARCHAR2,
235 x_tca_mandated_flag OUT NOCOPY VARCHAR2,
236 x_user_mandated_flag OUT NOCOPY VARCHAR2,
237 x_root_node_flag OUT NOCOPY VARCHAR2,
238 x_entity_name IN OUT NOCOPY VARCHAR2,
239 x_bo_indicator_flag OUT NOCOPY VARCHAR2,
240 x_display_flag OUT NOCOPY VARCHAR2,
241 x_bo_version_number OUT NOCOPY NUMBER,
242 x_object_version_number OUT NOCOPY NUMBER
243 ) IS
244 BEGIN
245 SELECT
246 business_object_code,
247 NVL(child_bo_code, FND_API.G_MISS_CHAR),
248 tca_mandated_flag,
249 user_mandated_flag,
250 NVL(root_node_flag, FND_API.G_MISS_CHAR),
251 entity_name,
252 bo_indicator_flag,
253 display_flag,
254 NVL(bo_version_number, FND_API.G_MISS_NUM),
255 object_version_number
256 INTO
257 x_business_object_code,
258 x_child_bo_code,
259 x_tca_mandated_flag,
260 x_user_mandated_flag,
261 x_root_node_flag,
262 x_entity_name,
263 x_bo_indicator_flag,
264 x_display_flag,
265 x_bo_version_number,
266 x_object_version_number
267 FROM HZ_BUS_OBJ_DEFINITIONS
268 WHERE business_object_code = x_business_object_code
269 and nvl(child_bo_code, 'N') = nvl( x_child_bo_code, 'N')
270 and entity_name = x_entity_name;
271 EXCEPTION
272 WHEN NO_DATA_FOUND THEN
273 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
274 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_bus_obj_rec');
275 FND_MESSAGE.SET_TOKEN('VALUE', x_business_object_code);
276 FND_MSG_PUB.ADD;
277 RAISE FND_API.G_EXC_ERROR;
278 END Select_Row;
279 PROCEDURE Delete_Row (
280 x_business_object_code IN VARCHAR2,
281 x_child_bo_code IN VARCHAR2,
282 x_entity_name IN VARCHAR2
283
284 ) IS
285 BEGIN
286 DELETE FROM HZ_BUS_OBJ_DEFINITIONS
287 WHERE business_object_code = x_business_object_code
288 and nvl(child_bo_code, 'N') = nvl( x_child_bo_code, 'N')
289 and entity_name = x_entity_name;
290 IF ( SQL%NOTFOUND ) THEN
291 RAISE NO_DATA_FOUND;
292 END IF;
293 END Delete_Row;
294 END HZ_BUS_OBJ_DEFINITIONS_PKG;