[Home] [Help]
PACKAGE BODY: APPS.EGO_ITEM_CATALOG_PVT
Source
1 PACKAGE BODY EGO_ITEM_CATALOG_PVT AS
2 /* $Header: EGOVCAGB.pls 120.1 2005/06/02 05:42:22 lkapoor noship $ */
3
4 /* Private API for processing catalog groups
5 ** Applications should not call this catalog group api directly.
6 ** return_status: this is returned by the api to indicate the success/failure of the call
7 ** msg_count: this is returned by the api to indicate the number of message logged for this
8 ** call.
9 **
10 */
11
12 Procedure Process_Catalog_Groups
13 ( x_return_status OUT NOCOPY VARCHAR2
14 , x_msg_count OUT NOCOPY NUMBER
15 )
16 IS
17 l_Table_Index NUMBER;
18 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
19 l_other_token_tbl Error_Handler.Token_Tbl_Type;
20 l_other_message VARCHAR2(2000);
21 l_err_text VARCHAR2(2000);
22 l_valid BOOLEAN := TRUE;
23 l_Return_Status VARCHAR2(1);
24 l_bo_return_status VARCHAR2(1);
25
26 BEGIN
27
28 -- Business Object starts with a status of Success
29
30 l_bo_return_status := 'S';
31
32 Error_Handler.Write_Debug('Starting PVT processing in Process_Catalog_Groups ');
33 -- dbms_output.put_line('Starting PVT processing in Process_Catalog_Groups ');
34
35 -- begin processing the catalog group table
36
37 --
38 -- all references to the input data must be made from the global reference.
39 -- That must be maintained as the source of truth for all data.
40 --
41
42 -- Every catalog group record is a business object.
43
44 -- dbms_output.Put_Line('Records to Process: ' || EGO_Globals.G_Catalog_Group_Tbl.COUNT);
45
46 FOR l_Table_Index IN 1..EGO_Globals.G_Catalog_Group_Tbl.COUNT
47 LOOP
48 BEGIN
49
50 l_return_status := 'S';
51
52 EGO_Globals.G_Catalog_Group_Rec := EGO_Globals.G_Catalog_Group_Tbl(l_Table_Index);
53
54 EGO_Item_Catalog_Pub.G_KF_Segment_Values(1) :=
55 EGO_Globals.G_Catalog_Group_Rec.Segment1;
56 EGO_Item_Catalog_Pub.G_KF_Segment_Values(2) :=
57 EGO_Globals.G_Catalog_Group_Rec.Segment2;
58 EGO_Item_Catalog_Pub.G_KF_Segment_Values(3) :=
59 EGO_Globals.G_Catalog_Group_Rec.Segment3;
60 EGO_Item_Catalog_Pub.G_KF_Segment_Values(4) :=
61 EGO_Globals.G_Catalog_Group_Rec.Segment4;
62 EGO_Item_Catalog_Pub.G_KF_Segment_Values(5) :=
63 EGO_Globals.G_Catalog_Group_Rec.Segment5;
64 EGO_Item_Catalog_Pub.G_KF_Segment_Values(6) :=
65 EGO_Globals.G_Catalog_Group_Rec.Segment6;
66 EGO_Item_Catalog_Pub.G_KF_Segment_Values(7) :=
67 EGO_Globals.G_Catalog_Group_Rec.Segment7;
68 EGO_Item_Catalog_Pub.G_KF_Segment_Values(8) :=
69 EGO_Globals.G_Catalog_Group_Rec.Segment8;
70 EGO_Item_Catalog_Pub.G_KF_Segment_Values(9) :=
71 EGO_Globals.G_Catalog_Group_Rec.Segment9;
72 EGO_Item_Catalog_Pub.G_KF_Segment_Values(10) :=
73 EGO_Globals.G_Catalog_Group_Rec.Segment10;
74 EGO_Item_Catalog_Pub.G_KF_Segment_Values(11) :=
75 EGO_Globals.G_Catalog_Group_Rec.Segment11;
76 EGO_Item_Catalog_Pub.G_KF_Segment_Values(12) :=
77 EGO_Globals.G_Catalog_Group_Rec.Segment12;
78 EGO_Item_Catalog_Pub.G_KF_Segment_Values(13) :=
79 EGO_Globals.G_Catalog_Group_Rec.Segment13;
80 EGO_Item_Catalog_Pub.G_KF_Segment_Values(14) :=
81 EGO_Globals.G_Catalog_Group_Rec.Segment14;
82 EGO_Item_Catalog_Pub.G_KF_Segment_Values(15) :=
83 EGO_Globals.G_Catalog_Group_Rec.Segment15;
84 EGO_Item_Catalog_Pub.G_KF_Segment_Values(16) :=
85 EGO_Globals.G_Catalog_Group_Rec.Segment16;
86 EGO_Item_Catalog_Pub.G_KF_Segment_Values(17) :=
87 EGO_Globals.G_Catalog_Group_Rec.Segment17;
88 EGO_Item_Catalog_Pub.G_KF_Segment_Values(18) :=
89 EGO_Globals.G_Catalog_Group_Rec.Segment18;
90 EGO_Item_Catalog_Pub.G_KF_Segment_Values(19) :=
91 EGO_Globals.G_Catalog_Group_Rec.Segment19;
92 EGO_Item_Catalog_Pub.G_KF_Segment_Values(20) :=
93 EGO_Globals.G_Catalog_Group_Rec.Segment20;
94
95
96
97 /* Assign null to the segments that are not used */
98
99 FOR i in 1..20
100 LOOP
101 IF EGO_Item_Catalog_Pub.G_KF_Segment_Values(i) = FND_API.G_MISS_CHAR
102 THEN
103 EGO_Item_Catalog_Pub.G_KF_Segment_Values(i) := null;
104 END IF;
105 END LOOP;
106
107 --
108 -- Transaction Type Validity
109 --
110
111 Error_Handler.Write_Debug('Performing trasaction type validity ');
112 -- dbms_output.put_line('Performing trasaction type validity ');
113
114 EGO_Globals.Transaction_Type_Validity
115 ( p_Entity_Id => EGO_Globals.G_ITEM_CATALOG_GROUP
116 , p_Entity => EGO_Globals.G_ITEM_CATALOG_GROUP
117 , p_transaction_type => EGO_Globals.G_Catalog_Group_Rec.Transaction_Type
118 , x_valid => l_valid
119 , x_mesg_token_tbl => l_mesg_token_tbl
120 );
121
122
123 IF l_valid <> true
124 THEN
125 -- quit since the record does not have a valid transaction type.
126 l_return_status := FND_API.G_RET_STS_ERROR;
127 RAISE EGO_Globals.G_EXC_SEV_QUIT_RECORD;
128 END IF;
129
130
131 --
132 -- Process Flow step 4.a - User Unique Index to Unique index conversion - I
133 --
134 Error_Handler.Write_Debug('Performing UUI-UI conversion ');
135 -- dbms_output.put_line('Performing UUI-UI conversion ');
136 EGO_ItemCat_Val_To_Id.EGO_ItemCatalog_UUI_To_UI
137 ( x_return_status => l_return_status
138 , x_mesg_token_tbl => l_mesg_token_tbl
139 );
140
141 Error_Handler.Write_Debug('UUI conversion returned . . .');
142 IF l_return_status = Error_Handler.G_STATUS_ERROR
143 THEN
144 Error_Handler.Write_Debug('Raising exception . . .QUIT_RECORD');
145 -- dbms_output.put_line('Raising exception . . .QUIT_RECORD');
146 RAISE EGO_Globals.G_EXC_SEV_QUIT_RECORD;
147 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
148 THEN
149 l_other_message := 'EGO_CATG_UUI_UNEXP_SKIP';
150 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
151 l_other_token_tbl(1).token_value :=
152 EGO_Globals.G_Catalog_Group_Rec.catalog_group_name;
153 RAISE EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT;
154 END IF;
155
156 --
157 -- Process Flow step 4.b - User Unique index to Unique Index conversion II
158 --
159
160 -- This is not required for catalog groups
161
162 --
163 -- Process Flow step 5: Verify existence
164 --
165 Error_Handler.Write_Debug('Performing check existence ');
166 -- dbms_output.put_line('Performing check existence ');
167 Ego_Validate_Catalog_Group.Check_Existence
168 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
169 , x_return_status => l_Return_Status
170 );
171
172 IF l_return_status = Error_Handler.G_STATUS_ERROR
173 THEN
174 RAISE EGO_Globals.G_EXC_SEV_QUIT_RECORD;
175 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
176 THEN
177 l_other_message := 'EGO_CATGRP_EXS_UNEXP_SKIP';
178 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
179 l_other_token_tbl(1).token_value :=
180 Ego_globals.G_Catalog_Group_Rec.Catalog_Group_Name;
181 RAISE EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT;
182 END IF;
183
184 --
185 -- To support SYNC, copy the transaction type from the old record which will
186 -- be set in check existence
187 --
188 IF Ego_Globals.G_Catalog_Group_Rec.transaction_type = 'SYNC'
189 THEN
190 Ego_Globals.G_Catalog_Group_Rec.transaction_type :=
191 Ego_Globals.G_Old_Catalog_Group_Rec.transaction_type;
192 END IF;
193
194 --
195 -- Process Flow Step 7: Check Lineage not required for Catalog Groups
196 --
197
198 --
199 -- Process Flow Step 8: Check Access.
200 -- Check if the user has access to create the catalog group.
201 --
202
203 Error_Handler.Write_Debug('Performing check access');
204 -- dbms_output.put_line('Performing check access');
205
206 Ego_Validate_Catalog_Group.Check_Access
207 ( x_return_status => l_return_status
208 , x_mesg_token_tbl => l_mesg_token_tbl
209 );
210
211 IF l_return_status = Error_Handler.G_STATUS_ERROR
212 THEN
213 l_other_message := 'EGO_CATGRP_ACC_FAT_FATAL';
214 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
215 l_other_token_tbl(1).token_value :=
216 Ego_Globals.G_Catalog_Group_rec.Catalog_Group_Name;
217 l_return_status := 'F';
218 RAISE EGO_Globals.G_EXC_FAT_QUIT_SIBLINGS;
219 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
220 THEN
221 l_other_message := 'BOM_CATGRP_ACC_UNEXP_SKIP';
222 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
223 l_other_token_tbl(1).token_value :=
224 Ego_Globals.G_Catalog_Group_rec.Catalog_Group_Name;
225
226 RAISE EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT;
227 END IF;
228
229 --
230 -- Process Flow step 9: Check Attributes
231 --
232
233 IF EGO_Globals.G_Catalog_Group_Rec.transaction_type IN
234 (Ego_Globals.G_OPR_UPDATE, Ego_Globals.G_OPR_CREATE)
235 THEN
236 Error_Handler.Write_Debug('Performing check attributes');
237 -- dbms_output.put_line('Performing check attributes');
238 EGO_Validate_Catalog_Group.Check_Attributes
239 ( x_return_status => l_return_status
240 , x_mesg_token_tbl => l_mesg_token_tbl
241 );
242
243 IF l_return_status = Error_Handler.G_STATUS_ERROR
244 THEN
245 RAISE EGO_Globals.G_EXC_SEV_QUIT_RECORD;
246 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
247 THEN
248 RAISE EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT;
249 END IF;
250
251 END IF;
252
253 --
254 -- Process Flow step 10: Populate null columns for UPDATE
255 -- OR Perform attribute defaulting for CREATE
256 --
257
258 IF EGO_Globals.G_Catalog_Group_Rec.transaction_type IN
259 (EGO_Globals.G_OPR_UPDATE, EGO_Globals.G_OPR_DELETE)
260 THEN
261 Error_Handler.Write_Debug('Populate NULL columns . . .');
262
263 Ego_Default_Catalog_Group.Populate_Null_Columns;
264
265 -- Bug 3324531
266 -- changed the global Bom_Globals.G_OPR_CREATE to EGO_Globals.G_OPR_CREATE
267 ELSIF EGO_Globals.G_Catalog_Group_Rec.transaction_type = EGO_Globals.G_OPR_CREATE
268 THEN
269 Error_Handler.Write_Debug('Attribute Defaulting . . .');
270
271 Ego_Default_Catalog_Group.Attribute_Defaulting
272 ( x_return_status => l_return_status
273 , x_mesg_token_tbl => l_mesg_token_tbl
274 );
275
276 IF l_return_status = Error_Handler.G_STATUS_ERROR
277 THEN
278 l_other_message := 'EGO_CATGRP_ATTDEF_CSEV_SKIP';
279 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
280 l_other_token_tbl(1).token_value :=
281 EGO_Globals.G_Catalog_Group_Rec.Catalog_Group_Name;
282 RAISE EGO_Globals.G_EXC_SEV_SKIP_BRANCH;
283 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
284 THEN
285 l_other_message := 'EGO_CATGRP_ATTDEF_UNEXP_SKIP';
286 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
287 l_other_token_tbl(1).token_value :=
288 EGO_Globals.G_Catalog_Group_Rec.Catalog_Group_Name;
289 RAISE EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT;
290 END IF;
291 END IF;
292
293 Error_Handler.write_debug('Finished with attribute defaulting . . .');
294 Error_Handler.write_debug('Proceeding with entity validations . . .');
295 -- dbms_output.put_line('Proceeding with entity validations . . .');
296
297 IF EGO_Globals.G_Catalog_Group_Rec.transaction_type <> EGO_Globals.G_OPR_DELETE
298 THEN
299 Ego_Validate_Catalog_Group.Check_Entity
300 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
301 , x_return_status => l_Return_Status
302 );
303
304 IF l_return_status = Error_Handler.G_STATUS_ERROR
305 THEN
306 RAISE EGO_Globals.G_EXC_SEV_QUIT_RECORD;
307 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
308 THEN
309 l_other_message := 'EGO_CATGRP_ENTVAL_UNEXP_SKIP';
310 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
311 l_other_token_tbl(1).token_value :=
312 EGO_Globals.G_Catalog_Group_Rec.Catalog_Group_Name;
313 RAISE EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT;
314 END IF;
315 ELSIF EGO_Globals.G_Catalog_Group_Rec.transaction_type = EGO_Globals.G_OPR_DELETE
316 THEN
317 Ego_Validate_Catalog_Group.Check_Entity_Delete
318 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
319 , x_return_status => l_Return_Status
320 );
321
322 -- dbms_output.put_line('Return Status is . . .'||l_return_status);
323
324 IF l_return_status = Error_Handler.G_STATUS_ERROR
325 THEN
326 -- dbms_output.put_line('Raising exception in delete');
327 RAISE EGO_Globals.G_EXC_SEV_QUIT_RECORD;
328 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
329 THEN
330 l_other_message := 'EGO_CATGRP_ENTVAL_UNEXP_SKIP';
331 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
332 l_other_token_tbl(1).token_value :=
333 EGO_Globals.G_Catalog_Group_Rec.Catalog_Group_Name;
334 RAISE EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT;
335 END IF;
336
337 END IF ;
338
339
340 --
341 -- Process Flow step 13 : Database Writes
342 --
343
344 Error_Handler.Write_Debug('Writing to the database');
345 -- dbms_output.put_line('Writing to the database');
346
347 Ego_Catalog_Group_Util.Perform_Writes
348 ( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
349 , x_return_status => l_return_status
350 );
351
352 IF l_return_status = Error_Handler.G_STATUS_ERROR
353 THEN
354 RAISE EGO_Globals.G_EXC_SEV_QUIT_RECORD;
355 ELSIF l_return_status = Error_Handler.G_STATUS_UNEXPECTED
356 THEN
357 l_other_message := 'EGO_CATGRP_ENTVAL_UNEXP_SKIP';
358 l_other_token_tbl(1).token_name := 'CATALOG_GROUP_NAME';
359 l_other_token_tbl(1).token_value :=
360 EGO_Globals.G_Catalog_Group_Rec.Catalog_Group_Name;
361 RAISE EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT;
362 END IF;
363
364 -- return the record back to the table after processing
365
366 EGO_Globals.G_Catalog_Group_Tbl(l_Table_Index) := EGO_Globals.G_Catalog_Group_Rec;
367
368 x_return_status := l_bo_return_status;
369
370 Error_Handler.Write_Debug('Process Completed');
371
372 EXCEPTION
373 WHEN EGO_Globals.G_EXC_SEV_QUIT_RECORD THEN
374 Error_Handler.Write_Debug('Handling exception G_EXC_SEV_QUIT_RECORD');
375 -- dbms_output.put_line('Handling exception G_EXC_SEV_QUIT_RECORD');
376 EGO_Catalog_Group_Err_Handler.Log_Error
377 ( p_mesg_token_tbl => l_mesg_token_tbl
378 , p_error_status => Error_Handler.G_STATUS_ERROR
379 , p_error_scope => Error_Handler.G_SCOPE_RECORD
380 , p_error_level => EGO_Globals.G_ITEM_CATALOG_GROUP_LEVEL
381 , p_entity_index => l_Table_Index
382 );
383
384 IF l_bo_return_status = 'S'
385 THEN
386 l_bo_return_status := l_return_status;
387 END IF;
388
389 x_return_status := l_bo_return_status;
390
391 WHEN EGO_Globals.G_EXC_SEV_QUIT_BRANCH THEN
392 EGO_Catalog_Group_Err_Handler.Log_Error
393 ( p_mesg_token_tbl => l_mesg_token_tbl
394 , p_error_status => Error_Handler.G_STATUS_ERROR
395 , p_error_scope => Error_Handler.G_SCOPE_CHILDREN
396 , p_error_level => EGO_Globals.G_ITEM_CATALOG_GROUP_LEVEL
397 , p_entity_index => l_Table_Index
398 );
399
400 IF l_bo_return_status = 'S'
401 THEN
402 l_bo_return_status := l_return_status;
403 END IF;
404
405 x_return_status := l_bo_return_status;
406
407 WHEN EGO_Globals.G_EXC_SEV_QUIT_SIBLINGS THEN
408 EGO_Catalog_Group_Err_Handler.Log_Error
409 ( p_mesg_token_tbl => l_mesg_token_tbl
410 , p_error_status => Error_Handler.G_STATUS_ERROR
411 , p_error_scope => Error_Handler.G_SCOPE_SIBLINGS
412 , p_error_level => EGO_Globals.G_ITEM_CATALOG_GROUP_LEVEL
413 , p_entity_index => l_Table_Index
414 );
415
416 IF l_bo_return_status = 'S'
417 THEN
418 l_bo_return_status := l_return_status;
419 END IF;
420
421 x_return_status := l_bo_return_status;
422
423 WHEN EGO_Globals.G_EXC_FAT_QUIT_SIBLINGS THEN
424 EGO_Catalog_Group_Err_Handler.Log_Error
425 ( p_mesg_token_tbl => l_mesg_token_tbl
426 , p_error_status => Error_Handler.G_STATUS_FATAL
427 , p_error_scope => Error_Handler.G_SCOPE_SIBLINGS
428 , p_error_level => EGO_Globals.G_ITEM_CATALOG_GROUP_LEVEL
429 , p_entity_index => l_Table_Index
430 );
431
432 IF l_bo_return_status = 'S'
433 THEN
434 l_bo_return_status := l_return_status;
435 END IF;
436
437 x_return_status := l_bo_return_status;
438
439 WHEN EGO_Globals.G_EXC_FAT_QUIT_OBJECT THEN
440 EGO_Catalog_Group_Err_Handler.Log_Error
441 ( p_mesg_token_tbl => l_mesg_token_tbl
442 , p_error_status => Error_Handler.G_STATUS_FATAL
443 , p_error_scope => Error_Handler.G_SCOPE_ALL
444 , p_error_level => EGO_Globals.G_ITEM_CATALOG_GROUP_LEVEL
445 , p_entity_index => l_Table_Index
446 );
447
448 IF l_bo_return_status = 'S'
449 THEN
450 l_bo_return_status := l_return_status;
451 END IF;
452
453 x_return_status := l_bo_return_status;
454
455 WHEN EGO_Globals.G_EXC_UNEXP_SKIP_OBJECT THEN
456 EGO_Catalog_Group_Err_Handler.Log_Error
457 ( p_mesg_token_tbl => l_mesg_token_tbl
458 , p_error_status => Error_Handler.G_STATUS_UNEXPECTED
459 , p_other_status => Error_Handler.G_STATUS_NOT_PICKED
460 , p_other_message => l_other_message
461 , p_other_token_tbl => l_other_token_tbl
462 , p_error_scope => Error_Handler.G_SCOPE_ALL
463 , p_error_level => EGO_Globals.G_ITEM_CATALOG_GROUP_LEVEL
464 , p_entity_index => l_Table_Index
465 );
466
467 IF l_bo_return_status = 'S'
468 THEN
469 l_bo_return_status := l_return_status;
470 END IF;
471
472 x_return_status := l_bo_return_status;
473 END;
474
475 /* End of the loop block */
476
477 IF l_return_status in ('Q', 'U')
478 THEN
479 x_return_status := l_return_status;
480 RETURN;
481 END IF;
482 END LOOP; /* End For Loop - processing of catalog group tbl */
483
484 /* End Processing all the catalog groups */
485 -- dbms_output.put_line('End of Private API');
486 END;
487
488 END EGO_ITEM_CATALOG_PVT;