[Home] [Help]
PACKAGE BODY: APPS.QP_DELAYED_REQUESTS_PVT
Source
1 PACKAGE BODY QP_Delayed_Requests_PVT AS
2 /* $Header: QPXVREQB.pls 120.5 2006/02/27 00:06:51 prarasto noship $ */
3 -- Global constant holding the package name
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Delayed_Requests_PVT';
6
7 g_delayed_requests QP_QUALIFIER_RULES_PUB.Request_Tbl_Type;
8 g_requesting_entities QP_QUALIFIER_RULES_PUB.Requesting_Entity_Tbl_Type;
9 G_MAX_REQUESTS NUMBER := 1000; --2502849
10 /* Local Procedures */
11 /* Local procedure to check if a request exists for a given entity, request
12 return the result in p_request_search_result which is set to FND_API.G_TRUE
13 if the request exists. The index of the request in request table is returned
14 in parameter p_request_ind
15 */
16 /* BUG 2502849-
17 Changes to improve scalability of this search when there
18 is a large number of requests:
19
20 The index value where the request is stored is a function of the
21 entity_id value (the function was chosen to be 'mod' as this has
22 a high probability of resulting in a unique value as line ids are
23 generated sequentially). Therefore, this check would search only
24 through requests for the entity_id that resolves to the same
25 mod value.
26
27 If the request does NOT exist, then x_result is set to FND_API.G_FALSE
28 and the parameter x_request_ind has the index value where this request
29 should be inserted.
30 */
31 Procedure Check_for_Request( p_entity_code in Varchar2
32 ,p_entity_id in Number
33 ,p_request_type in Varchar2
34 ,p_request_unique_key1 IN VARCHAR2
35 ,p_request_unique_key2 IN VARCHAR2
36 ,p_request_unique_key3 IN VARCHAR2
37 ,p_request_unique_key4 IN VARCHAR2
38 ,p_request_unique_key5 IN VARCHAR2
39 ,x_request_ind OUT NOCOPY Number
40 ,x_result OUT NOCOPY Varchar2
41 ,x_return_status OUT NOCOPY Varchar2)
42 IS
43 l_ind pls_integer;
44 l_max_ind pls_integer;
45 BEGIN
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47 x_result := FND_API.G_FALSE;
48
49 oe_debug_pub.add('Entering Procedure Check_for_Request in Package QP_Delayed_Requests_Pvt');
50
51 -- 2502849 l_ind to l_max_ind is the range of index positions that can
52 -- hold requests for this entity id - for e.g. if entity_id is
53 -- 2341 and G_MAX_REQUESTS is 1000 then the range would be:
54 -- 2341001 - 2342000
55
56 l_ind := (mod(p_entity_id,100000) * G_MAX_REQUESTS)+1;
57 l_max_ind := l_ind + G_MAX_REQUESTS - 1;
58
59 -- 2502849 Starting from l_ind, search for the first index position
60 -- with a request. This is required as requests can be
61 -- deleted later which will result in indexes without any
62 -- requests. However, the search should still go over the
63 -- requests in the range from l_ind to l_max_ind.
64
65 IF NOT G_Delayed_Requests.Exists(l_ind) THEN
66 x_request_ind := l_ind;
67 l_ind := G_Delayed_Requests.Next(l_ind);
68 END IF;
69
70 WHILE G_Delayed_Requests.Exists(l_ind)
71 AND l_ind <= l_max_ind LOOP
72 x_request_ind := l_ind+1;
73
74 IF G_Delayed_Requests(l_ind).Entity_code = p_entity_code
75 AND
76 G_Delayed_Requests(l_ind).Entity_id = p_entity_id
77 AND
78 G_Delayed_Requests(l_ind).Request_Type = p_request_type
79 AND
80 NVL(G_Delayed_Requests(l_ind).request_unique_key1, FND_API.G_MISS_CHAR) =
81 NVL(p_request_unique_key1, FND_API.G_MISS_CHAR)
82 AND
83 NVL(G_Delayed_Requests(l_ind).request_unique_key2, FND_API.G_MISS_CHAR) =
84 NVL(p_request_unique_key2, FND_API.G_MISS_CHAR)
85 AND
86 NVL(G_Delayed_Requests(l_ind).request_unique_key3, FND_API.G_MISS_CHAR) =
87 NVL(p_request_unique_key3, FND_API.G_MISS_CHAR)
88 AND
89 NVL(G_Delayed_Requests(l_ind).request_unique_key4, FND_API.G_MISS_CHAR) =
90 NVL(p_request_unique_key4, FND_API.G_MISS_CHAR)
91 AND
92 NVL(G_Delayed_Requests(l_ind).request_unique_key5, FND_API.G_MISS_CHAR) =
93 NVL(p_request_unique_key5, FND_API.G_MISS_CHAR)
94 AND NVL(G_Delayed_Requests(l_ind).processed,'N') = 'N' -- added for 2502849
95 THEN
96 x_request_ind := l_ind;
97 x_result := FND_API.G_TRUE;
98 EXIT;
99 END IF;
100
101 l_ind := G_Delayed_Requests.Next(l_ind);
102
103 END LOOP;
104
105 /* Added for 2502849 */
106
107 IF x_request_ind > l_max_ind THEN
108 FND_MESSAGE.SET_NAME('QP','QP_MAX_REQUESTS_EXCEEDED');
109 OE_MSG_PUB.ADD;
110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
111 END IF;
112 EXCEPTION
113 WHEN OTHERS THEN
114 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
115 THEN
116 OE_MSG_PUB.Add_Exc_Msg
117 (G_PKG_NAME
118 ,'CheckForRequest');
119 END IF;
120
121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122
123 End Check_For_Request;
124
125 PROCEDURE Process_Request_Pvt
126 (p_request_ind IN Number
127 ,p_delete IN Varchar2 default FND_API.G_FALSE
128 ,x_return_status OUT NOCOPY VARCHAR2)
129 IS
130 l_request_rec QP_QUALIFIER_RULES_PUB.request_rec_type;
131 l_request_type VARCHAR2(30);
132 l_return_status VARCHAR2(1);
133 l_entity_id NUMBER;
134 l_entity_code Varchar2(30);
135 l_req_entity_ind number;
136 l_set_index NUMBER;
137 l_set_request QP_QUALIFIER_RULES_PUB.request_tbl_type;
138 l_deleted_options_tbl QP_QUALIFIER_RULES_PUB.request_tbl_type;
139 l_updated_options_tbl QP_QUALIFIER_RULES_PUB.request_tbl_type;
140 l_d_index NUMBER;
141 l_u_index NUMBER;
142 K NUMBER;
143 I NUMBER := 1;
144 l_dup_sdate DATE := NULL;
145 l_dup_edate DATE := NULL;
146 l_count NUMBER;
147
148
149 BEGIN
150
151 oe_debug_pub.add('Entering Procedure Process_Request_Pvt ');
152 --dbms_output.put_line('Entering Procedure Process_Request_Pvt ');
153
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 oe_debug_pub.add('Request processed '||G_Delayed_Requests(p_request_ind).processed);
157
158 -- if request has already been processed ('Y') or if the request is
159 -- being processed ('I'): this would occur if request resulted in
160 -- to a recursive call to process order.
161 if (G_Delayed_Requests(p_request_ind).processed = 'Y'
162 OR G_Delayed_Requests(p_request_ind).processed = 'I')
163 then
164 RETURN;
165 end if;
166 l_request_rec := G_Delayed_Requests(p_request_ind);
167 l_entity_code := l_request_rec.entity_code;
168 l_entity_Id := l_request_rec.entity_Id;
169 l_request_type := l_request_rec.request_type;
170
171 oe_debug_pub.add('Request type '||l_request_type);
172 oe_debug_pub.add('entity code '||l_entity_code);
173 oe_debug_pub.add('entity id '||l_entity_Id);
174
175 G_Delayed_Requests(p_request_ind).processed := 'I';
176
177
178
179
180
181 --- Add your code here to execute procedures/functions based
182 --- on request type.
183
184 IF l_request_type = 'DUPLICATE_QUALIFIERS'
185 THEN
186
187 --dbms_output.put_line('calling qualifier dup');
188 QP_DELAYED_REQUESTS_UTIL.CHECK_FOR_DUPLICATE_QUALIFIERS(l_return_status,l_entity_Id);
189
190
191 END IF;
192
193 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
195 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198
199 -- Start bug2091362
200 IF l_request_type = 'DUPLICATE_MODIFIER_LINES'
201 THEN
202
203 --dbms_output.put_line('calling Modifier Line dup');
204 QP_DELAYED_REQUESTS_UTIL.CHECK_DUPLICATE_MODIFIER_LINES
205 ( p_Start_Date_Active => fnd_date.canonical_to_date(l_request_rec.param2) --2752265
206 , p_End_Date_Active => fnd_date.canonical_to_date(l_request_rec.param3) --2752265
207 , p_List_Line_ID => l_entity_id
208 , p_List_Header_ID => l_request_rec.param1
209 , p_pricing_attribute_context => l_request_rec.param4
210 , p_pricing_attribute => l_request_rec.param5
211 , p_pricing_attr_value => l_request_rec.param6
212 , x_return_status => l_return_status);
213
214
215 END IF;
216
217 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
220 RAISE FND_API.G_EXC_ERROR;
221 END IF;
222
223 -- end bug2091362
224
225 IF l_request_type = 'DUPLICATE_LIST_LINES'
226 THEN
227
228 --dbms_output.put_line('calling qualifier dup');
229 QP_DELAYED_REQUESTS_UTIL.CHECK_DUPLICATE_LIST_LINES
230 ( p_Start_Date_Active => fnd_date.canonical_to_date(l_request_rec.param2) --2739511
231 , p_End_Date_Active => fnd_date.canonical_to_date(l_request_rec.param3) --2739511
232 , p_Revision => l_request_rec.param4
233 , p_List_Line_ID => l_entity_id
234 , p_List_Header_ID => l_request_rec.param1
235 , x_return_status => l_return_status
236 , x_dup_sdate => l_dup_sdate
237 , x_dup_edate => l_dup_edate);
238
239
240 END IF;
241
242 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
244 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247
248
249 IF l_request_type = 'MAINTAIN_LIST_HEADER_PHASES'
250 THEN
251
252 --dbms_output.put_line('calling qualifier dup');
253 QP_DELAYED_REQUESTS_UTIL.maintain_list_header_phases
254 (p_List_Header_ID => l_request_rec.param1
255 , x_return_status => l_return_status);
256
257 END IF;
258
259 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
261 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
262 RAISE FND_API.G_EXC_ERROR;
263 END IF;
264
265
266 IF l_request_type = 'VALIDATE_LINES_FOR_CHILD'
267 THEN
268
269 --dbms_output.put_line('calling qualifier dup');
270 QP_DELAYED_REQUESTS_UTIL.validate_lines_for_child
271 ( p_List_Line_ID => l_entity_id
272 ,p_list_line_type_code =>l_request_rec.param1
273 , x_return_status => l_return_status);
274
275 END IF;
276
277 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
280 RAISE FND_API.G_EXC_ERROR;
281 END IF;
282
283
284 IF l_request_type = QP_GLOBALS.G_MULTIPLE_PRICE_BREAK_ATTRS
285 THEN
286 oe_debug_pub.add('Processing check_mult_price_break_attrs');
287 QP_DELAYED_REQUESTS_UTIL.Check_Mult_Price_Break_Attrs(
288 p_parent_list_line_id => l_request_rec.param1,
289 x_return_status => l_return_status);
290 END IF;
291
292 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
295 RAISE FND_API.G_EXC_ERROR;
296 END IF;
297
298
299 IF l_request_type = QP_GLOBALS.G_MIXED_QUAL_SEG_LEVELS
300 THEN
301 oe_debug_pub.add('Processing check_mult_price_break_attrs');
302 QP_DELAYED_REQUESTS_UTIL.Check_Mixed_Qual_Seg_Levels(
303 x_return_status => l_return_status,
304 p_qualifier_rule_id => l_request_rec.param1);
305 END IF;
306
307 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
309 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
310 RAISE FND_API.G_EXC_ERROR;
311 END IF;
312
313
314 IF l_request_type = 'OVERLAPPING_BREAKS'
315 THEN
316 IF l_request_rec.param2 = 'Y' THEN
317 --call continuous price breaks validation function
318 QP_DELAYED_REQUESTS_UTIL.Check_Continuous_Price_Breaks
319 ( p_List_Line_ID => l_request_rec.param1
320 ,x_return_status => l_return_status);
321 ELSE
322 --dbms_output.put_line('calling overlapping breaks');
323 QP_DELAYED_REQUESTS_UTIL.Check_For_overlapping_Breaks
324 --changed by svdeshmu on Aril 07
325 --( p_List_Line_ID => l_entity_id
326 ( p_List_Line_ID => l_request_rec.param1
327 ,x_return_status => l_return_status);
328 END IF;
329
330 END IF;
331
332 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
334 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
338 IF l_request_type = QP_Globals.G_UPGRADE_PRICE_BREAKS
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337
339 THEN
340 QP_DELAYED_REQUESTS_UTIL.Upgrade_Price_Breaks
341 ( p_pbh_id => l_entity_id
342 ,p_list_line_no => l_request_rec.param1
343 ,p_product_attribute => l_request_rec.param2
344 ,p_product_attr_value => l_request_rec.param3
345 ,p_list_type => l_request_rec.param4
346 ,p_start_date_active => l_request_rec.param5
347 ,p_end_date_active => l_request_rec.param6
348 ,x_return_status => l_return_status);
349 END IF;
350
351 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
353 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
354 RAISE FND_API.G_EXC_ERROR;
355 END IF;
356
357 IF l_request_type = 'SINGLE_PRICE_LIST'
358 THEN
359
360 --dbms_output.put_line('calling single price list');
361 QP_DELAYED_REQUESTS_UTIL.Check_multiple_prl
362 ( p_List_header_ID => l_entity_id
363 ,x_return_status => l_return_status);
364
365 END IF;
366
367 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
370 RAISE FND_API.G_EXC_ERROR;
371 END IF;
372
373
374 IF l_request_type = QP_GLOBALS.G_MAINTAIN_QUALIFIER_DEN_COLS
375 THEN
376 QP_DELAYED_REQUESTS_UTIL.Maintain_Qualifier_Den_Cols(
377 p_list_header_id => l_entity_id,
378 x_return_status => l_return_status);
379 END IF;
380
381 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
383 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
384 RAISE FND_API.G_EXC_ERROR;
385 END IF;
386
387 IF l_request_type = QP_GLOBALS.G_UPDATE_LIST_QUAL_IND
388 THEN
389
390 --dbms_output.put_line('calling list qualification indicator');
391 QP_DELAYED_REQUESTS_UTIL.Update_List_Qualification_Ind
392 ( p_List_header_ID => l_entity_id
393 ,x_return_status => l_return_status);
394 END IF;
395
396 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
398 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
399 RAISE FND_API.G_EXC_ERROR;
400 END IF;
401
402 IF l_request_type = QP_GLOBALS.G_UPDATE_LIMITS_COLUMNS
403 THEN
404
405 --dbms_output.put_line('calling UPDATE_LIMITS_COLUMNS');
406 QP_DELAYED_REQUESTS_UTIL.Update_Limits_Columns
407 ( p_Limit_Id => l_entity_id
408 ,x_return_status => l_return_status);
409 END IF;
410
411 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416
417 IF l_request_type = QP_GLOBALS.G_UPDATE_QUALIFIER_STATUS
418 THEN
419 --dbms_output.put_line('calling UPDATE_QUALIFIER_STATUS ');
420 QP_DELAYED_REQUESTS_UTIL.Update_Qualifier_Status
421 ( p_list_header_id => l_entity_id
422 ,p_active_flag => l_request_rec.param1
423 ,x_return_status => l_return_status);
424 END IF;
425
426 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
429 RAISE FND_API.G_EXC_ERROR;
430 END IF;
431
432 IF l_request_type = QP_GLOBALS.G_CREATE_SECURITY_PRIVILEGE
433 THEN
434 --dbms_output.put_line('calling CREATE_SECURITY_PRIVILEGE ');
435 QP_DELAYED_REQUESTS_UTIL.Create_Security_Privilege
436 ( p_list_header_id => l_entity_id
437 ,p_list_type_code => l_request_rec.param1
438 ,x_return_status => l_return_status);
439 END IF;
440
441 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
444 RAISE FND_API.G_EXC_ERROR;
445 END IF;
446
447 IF l_request_type = QP_GLOBALS.G_UPDATE_ATTRIBUTE_STATUS
448 THEN
449 --dbms_output.put_line('calling UPDATE_ATTRIBUTE_STATUS ');
450 QP_DELAYED_REQUESTS_UTIL.Update_Attribute_Status
451 ( p_list_header_id => l_entity_id
452 ,p_list_line_id => l_request_rec.param1
453 ,p_context_type => l_request_rec.param2
454 ,p_context_code => l_request_rec.param3
455 ,p_segment_mapping_column => l_request_rec.param4
456 ,x_return_status => l_return_status);
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END IF;
458
459 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
461 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
462 RAISE FND_API.G_EXC_ERROR;
463 END IF;
464
465
466 IF l_request_type = QP_GLOBALS.G_UPDATE_CHILD_BREAKS
467 THEN
468 QP_DELAYED_REQUESTS_UTIL.update_child_break_lines(
469 p_list_line_id => l_entity_id,
470 x_return_status => l_return_status);
471 END IF;
472
473 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
475 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
476 RAISE FND_API.G_EXC_ERROR;
477 END IF;
478
479 IF l_request_type = QP_GLOBALS.G_UPDATE_CHILD_PRICING_ATTR
480 THEN
481 QP_DELAYED_REQUESTS_UTIL.update_child_pricing_attr(
482 x_return_status => l_return_status,
483 p_list_line_id => l_entity_id);
484 END IF;
485
486 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
488 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491
492 /*included by spgopal for performance problem, to include phase_id and header_idinfo in qp_pricing_attributes table*/
493
494 IF l_request_type = QP_GLOBALS.G_UPDATE_PRICING_ATTR_PHASE
495 THEN
496 BEGIN --[Bug 4457903] Call update_pricing_attr_phase only if
497 --the line id is present in the QP_LIST_LINES table
498 SELECT 1 into l_count from QP_LIST_LINES
499 WHERE LIST_LINE_ID = l_entity_id;
500 EXCEPTION
501 WHEN NO_DATA_FOUND THEN
502 l_count := 0;
503 END;
504
505 IF l_count = 1 THEN
506 QP_DELAYED_REQUESTS_UTIL.update_pricing_attr_phase(
507 p_list_line_id => l_entity_id,
508 x_return_status => l_return_status);
509 END IF;
510 END IF;
511
512 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
514 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518 --Added by rchellam on 29-AUG-2001. POSCO Change.
519 IF l_request_type = QP_GLOBALS.G_MAINTAIN_FACTOR_LIST_ATTRS
520 THEN
521 QP_DELAYED_REQUESTS_UTIL.Maintain_Factor_List_Attrs(
522 p_list_line_id => l_entity_id,
523 x_return_status => l_return_status);
524 END IF;
525
526 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
528 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
529 RAISE FND_API.G_EXC_ERROR;
530 END IF;
531
532 /*fix for bug 1501138*/
533 IF l_request_type = QP_GLOBALS.G_WARN_SAME_QUALIFIER_GROUP
534 THEN
535 oe_debug_pub.add('qual context'||l_request_rec.param3||' attr '||l_request_rec.param4||' grp no '||l_request_rec.param2);
536
537 /*
538 QP_DELAYED_REQUESTS_UTIL.Warn_same_qualifier_group(
539 p_list_header_id => l_entity_id,
540 p_list_line_id => l_request_rec.param1,
541 p_qualifier_grouping_no => l_request_rec.param2,
542 p_qualifier_context => l_request_rec.param3,
543 p_qualifier_attribute => l_request_rec.param4,
544 x_return_status => l_return_status);
545 */
546
547 oe_debug_pub.add('after qual context'||l_request_rec.param3||' attr '||l_request_rec.param4||' grp no '||l_request_rec.param3);
548 null;
549 END IF;
550
551 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
553 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
554 RAISE FND_API.G_EXC_ERROR;
555 END IF;
556
557
558 IF l_request_type = QP_GLOBALS.G_UPDATE_LINE_QUAL_IND
559 THEN
560
561 --dbms_output.put_line('calling line qualification indicator');
562 QP_DELAYED_REQUESTS_UTIL.Update_Line_Qualification_Ind
563 ( p_List_line_ID => l_entity_id
564 ,x_return_status => l_return_status);
565 END IF;
566
567 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
570 RAISE FND_API.G_EXC_ERROR;
571 END IF;
572
573 /*included by spgopal for pricing phase seeded data over-write problem to update the denormalised columns in QP_PRICING_PHASES*/
574
575 IF l_request_type = QP_GLOBALS.G_UPDATE_PRICING_PHASE
576 THEN
577 oe_debug_pub.add('processing req UPDATE_PRICING_PHASE');
578 QP_DELAYED_REQUESTS_UTIL.update_pricing_phase(
579 p_pricing_phase_id => l_request_rec.param1,
580 p_automatic_flag => l_request_rec.param2, --fix for bug 3756625
581 p_count => l_request_rec.param3,
582 p_call_from => l_request_rec.param4,
583 x_return_status => l_return_status);
584 END IF;
585
586 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
587 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
588 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
589 RAISE FND_API.G_EXC_ERROR;
593 IF l_request_type = QP_GLOBALS.G_UPDATE_MANUAL_MODIFIER_FLAG
590 END IF;
591
592 -- Essilor Fix bug 2789138
594 THEN
595 oe_debug_pub.add('processing req UPDATE_MANUAL_MODIFIER_FLAG');
596 QP_DELAYED_REQUESTS_UTIL.Update_manual_modifier_flag(
597 p_pricing_phase_id => l_request_rec.param1,
598 p_automatic_flag => l_request_rec.param2,
599 x_return_status => l_return_status);
600 END IF;
601
602 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
605 RAISE FND_API.G_EXC_ERROR;
606 END IF;
607
608 --hvop
609 IF l_request_type = QP_GLOBALS.G_UPDATE_HVOP
610 THEN
611 oe_debug_pub.add('processing req UPDATE_HVOP');
612 QP_DELAYED_REQUESTS_UTIL.HVOP_Pricing_Setup (x_return_status => l_return_status);
613 END IF;
614
615 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
617 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
618 RAISE FND_API.G_EXC_ERROR;
619 END IF;
620 --hvop
621
622 --pattern
623 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
624
625 IF l_request_type = QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN THEN
626 oe_debug_pub.add('Processing req Attribute Groups');
627 QP_DELAYED_REQUESTS_UTIL.maintain_header_pattern(
628 p_list_header_id => l_request_rec.entity_Id
629 , p_qualifier_group => l_request_rec.request_unique_key1
630 , p_setup_action => l_request_rec.request_unique_key2
631 , x_return_status => l_return_status);
632 END IF;
633 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
636 RAISE FND_API.G_EXC_ERROR;
637 END IF;
638 IF l_request_type = QP_GLOBALS.G_MAINTAIN_LINE_PATTERN THEN
639 oe_debug_pub.add('Processing req Attribute Groups');
640 QP_DELAYED_REQUESTS_UTIL.maintain_line_pattern(
641 p_list_header_id => l_request_rec.entity_Id
642 , p_list_line_id => l_request_rec.request_unique_key1
643 , p_qualifier_group => l_request_rec.request_unique_key2
644 , p_setup_action => l_request_rec.request_unique_key3
645 , x_return_status => l_return_status);
646 END IF;
647 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
649 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
650 RAISE FND_API.G_EXC_ERROR;
651 END IF;
652
653 IF l_request_type = QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN THEN
654 oe_debug_pub.add('Processing req Attribute Groups');
655 QP_DELAYED_REQUESTS_UTIL.maintain_product_pattern(
656 p_list_header_id => l_request_rec.entity_Id
657 , p_list_line_id => l_request_rec.request_unique_key1
658 , p_setup_action => l_request_rec.request_unique_key2
659 , x_return_status => l_return_status);
660 END IF;
661 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
663 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
664 RAISE FND_API.G_EXC_ERROR;
665 END IF;
666
667 END IF; --Java Engine Installed
668
669 --pattern
670
671 IF l_request_type = QP_GLOBALS.G_VALIDATE_SELLING_ROUNDING
672 THEN
673 oe_debug_pub.add('processing req VALIDATE_SELLING_ROUNDING');
674 QP_DELAYED_REQUESTS_UTIL.validate_selling_rounding(
675 p_currency_header_id => l_entity_id,
676 p_to_currency_code => l_request_rec.param1,
677 x_return_status => l_return_status);
678 END IF;
679
680 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
683 RAISE FND_API.G_EXC_ERROR;
684 END IF;
685
686
687 IF l_request_type = QP_GLOBALS.G_CHECK_SEGMENT_LEVEL_IN_GROUP
688 THEN
689 oe_debug_pub.add('processing req CHECK_SEGMENT_LEVEL_IN_GROUP');
690 QP_DELAYED_REQUESTS_UTIL.check_segment_level_in_group(
691 p_list_line_id => l_entity_id,
692 p_list_header_id => l_request_rec.request_unique_key1,
693 p_qualifier_grouping_no => l_request_rec.request_unique_key2,
694 x_return_status => l_return_status);
695 END IF;
696
697 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703 IF l_request_type = QP_GLOBALS.G_CHECK_LINE_FOR_HEADER_QUAL
704 THEN
705 oe_debug_pub.add('processing req CHECK_LINE_FOR_HEADER_QUAL');
706 QP_DELAYED_REQUESTS_UTIL.CHECK_LINE_FOR_HEADER_QUAL(
707 p_list_header_id => l_entity_id,
708 p_list_line_id => l_request_rec.request_unique_key1,
709 x_return_status => l_return_status);
710 END IF;
711
712 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
714 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
718 -- Hierarchical Categories (sfiresto)
715 RAISE FND_API.G_EXC_ERROR;
716 END IF;
717
719 IF l_request_type = QP_GLOBALS.G_CHECK_ENABLED_FUNC_AREAS THEN
720 oe_debug_pub.add('processing req CHECK_ENABLED_FUNC_AREAS');
721 QP_DELAYED_REQUESTS_UTIL.Check_Enabled_Func_Areas(
722 p_pte_source_system_id => l_entity_id,
723 x_return_status => l_return_status);
724 END IF;
725
726 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
728 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731
732
733 --hw
734 -- delayed request for changed lines
735 if QP_PERF_PVT.enabled = 'Y' then
736
737 if l_request_type = QP_GLOBALS.G_UPDATE_CHANGED_LINES_ADD then
738 oe_debug_pub.add('processing req G_UPDATE_CHANGED_LINES_ADD');
739 QP_DELAYED_REQUESTS_UTIL.update_changed_lines_add(
740 p_list_line_id => l_entity_id,
741 p_list_header_id => l_request_rec.param2,
742 p_pricing_phase_id => l_request_rec.param1,
743 x_return_status => l_return_status);
744 elsif l_request_type = QP_GLOBALS.G_UPDATE_CHANGED_LINES_DEL then
745 oe_debug_pub.add('processing req G_UPDATE_CHANGED_LINES_DEL');
746 QP_DELAYED_REQUESTS_UTIL.update_changed_lines_del(
747 p_list_line_id => l_entity_id,
748 p_list_header_id => l_request_rec.param2,
749 p_pricing_phase_id => l_request_rec.param1,
750 p_product_attribute => l_request_rec.param3,
751 p_product_attr_value => l_request_rec.param4,
752 x_return_status => l_return_status);
753 elsif l_request_type = QP_GLOBALS.G_UPDATE_CHANGED_LINES_PH then
754 oe_debug_pub.add('processing req G_UPDATE_CHANGED_LINES_PHASE');
755 QP_DELAYED_REQUESTS_UTIL.update_changed_lines_ph(
756 p_list_line_id => l_entity_id,
757 p_list_header_id => l_request_rec.param2,
758 p_pricing_phase_id => l_request_rec.param1,
759 p_old_pricing_phase_id => l_request_rec.param3,
760 x_return_status => l_return_status);
761 elsif l_request_type = QP_GLOBALS.G_UPDATE_CHANGED_LINES_ACT then
762 oe_debug_pub.add('processing req G_UPDATE_CHANGED_LINES_ACTIVE');
763 QP_DELAYED_REQUESTS_UTIL.update_changed_lines_act(
764 p_list_header_id => l_entity_id,
765 p_active_flag => l_request_rec.param1,
766 x_return_status => l_return_status);
767 --hvop elsif l_request_type = QP_GLOBALS.G_UPDATE_HVOP then
768 oe_debug_pub.add('processing req G_UPDATE_HVOP')
769 ;
770 QP_DELAYED_REQUESTS_UTIL.HVOP_Pricing_Setup(x_return_status => l_return_status);
771 --hvop
772 end if;
773 end if;
774
775 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
776 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
777 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
778 RAISE FND_API.G_EXC_ERROR;
779 END IF;
780
781
782 IF (p_delete = FND_API.G_TRUE) then
783
784 G_Delayed_Requests.Delete(p_request_ind);
785 /*
786 BUG 2502849- do not delete from req entities table
787 , delete table in the end when all requests are processed
788 This is to improve performance as this search loops through
789 the entire pl/sql table which becomes very large with
790 greater number of lines being processed
791
792 -- Delete all the records in the requesting entities table
793 -- that have this request.
794
795 l_req_entity_ind := G_Requesting_Entities.First;
796
797
798 WHILE l_req_entity_ind IS NOT NULL LOOP
799 IF G_Requesting_Entities(l_req_entity_ind).request_index = p_request_ind
800 THEN
801 G_Requesting_Entities.Delete(l_req_entity_ind);
802 END IF;
803 l_req_entity_ind := G_Requesting_Entities.Next(l_req_entity_ind);
804 END LOOP;
805 */
806 ELSE
807
808 G_Delayed_Requests(p_request_ind).processed := 'Y';
809
810 END IF;
811
812
813 EXCEPTION
814
815 WHEN FND_API.G_EXC_ERROR THEN
816 G_Delayed_Requests(p_request_ind).processed := 'N';
817 x_return_status := FND_API.G_RET_STS_ERROR;
818
819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
820 G_Delayed_Requests(p_request_ind).processed := 'N';
821 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
822
823 WHEN NO_DATA_FOUND THEN
824 oe_debug_pub.add('Exiting Process_Request_Pvt no_data_found exception ');
825 G_Delayed_Requests(p_request_ind).processed := 'N';
826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
827
828
829 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
830 THEN
831 OE_MSG_PUB.Add_Exc_Msg
832 ( G_PKG_NAME
833 , 'Process_Request_Pvt'
834 );
835 END IF;
836
837 WHEN OTHERS THEN
838 G_Delayed_Requests(p_request_ind).processed := 'N';
839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
840
841 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
842 THEN
843 OE_MSG_PUB.Add_Exc_Msg
844 ( G_PKG_NAME
845 , 'Process_Request_Pvt'
846 );
847 END IF;
848
852 End Process_Request_Pvt;
849 oe_debug_pub.add('Exiting Process_Request_Pvt with others exception ');
850
851
853 /** End Local Procedures **/
854
855 /** Global Procedures **/
856 Procedure Log_Request
857 ( p_entity_code IN VARCHAR2
858 , p_entity_id IN NUMBER
859 , p_requesting_entity_code IN VARCHAR2
860 , p_requesting_entity_id IN NUMBER
861 , p_request_type IN VARCHAR2
862 , p_request_unique_key1 IN VARCHAR2 := NULL
863 , p_request_unique_key2 IN VARCHAR2 := NULL
864 , p_request_unique_key3 IN VARCHAR2 := NULL
865 , p_request_unique_key4 IN VARCHAR2 := NULL
866 , p_request_unique_key5 IN VARCHAR2 := NULL
867 , p_param1 IN VARCHAR2 := NULL
868 , p_param2 IN VARCHAR2 := NULL
869 , p_param3 IN VARCHAR2 := NULL
870 , p_param4 IN VARCHAR2 := NULL
871 , p_param5 IN VARCHAR2 := NULL
872 , p_param6 IN VARCHAR2 := NULL
873 , p_param7 IN VARCHAR2 := NULL
874 , p_param8 IN VARCHAR2 := NULL
875 , p_param9 IN VARCHAR2 := NULL
876 , p_param10 IN VARCHAR2 := NULL
877 , p_param11 IN VARCHAR2 := NULL
878 , p_param12 IN VARCHAR2 := NULL
879 , p_param13 IN VARCHAR2 := NULL
880 , p_param14 IN VARCHAR2 := NULL
881 , p_param15 IN VARCHAR2 := NULL
882 , p_param16 IN VARCHAR2 := NULL
883 , p_param17 IN VARCHAR2 := NULL
884 , p_param18 IN VARCHAR2 := NULL
885 , p_param19 IN VARCHAR2 := NULL
886 , p_param20 IN VARCHAR2 := NULL
887 , p_param21 IN VARCHAR2 := NULL
888 , p_param22 IN VARCHAR2 := NULL
889 , p_param23 IN VARCHAR2 := NULL
890 , p_param24 IN VARCHAR2 := NULL
891 , p_param25 IN VARCHAR2 := NULL
892 , p_long_param1 IN VARCHAR2 := NULL
893 , x_return_status OUT NOCOPY VARCHAR2
894 )
895 IS
896 l_request_search_rslt VARCHAR2(1);
897 l_return_status VARCHAR2(1);
898 l_request_ind NUMBER;
899 l_req_entity_ind NUMBER;
900 l_request QP_QUALIFIER_RULES_PUB.REQUEST_REC_TYPE;
901 l_req_entity QP_QUALIFIER_RULES_PUB.Requesting_Entity_Rec_Type;
902 BEGIN
903
904 oe_debug_pub.add('Entering Procedure Log_Request in Package QP_Delayed_Requests_Pvt');
905 oe_debug_pub.add('log_request_type'||p_request_type);
906
907 -- Initialize the request_record
908 l_request.request_type := p_request_type;
909 l_request.entity_id := p_entity_id;
910 l_request.entity_code := p_entity_code;
911 l_request.request_unique_key1 := p_request_unique_key1;
912 l_request.request_unique_key2 := p_request_unique_key2;
913 l_request.request_unique_key3 := p_request_unique_key3;
914 l_request.request_unique_key4 := p_request_unique_key4;
915 l_request.request_unique_key5 := p_request_unique_key5;
916 l_request.param1 := p_param1;
917 l_request.param2 := p_param2;
918 l_request.param3 := p_param3;
919 l_request.param4 := p_param4;
920 l_request.param5 := p_param5;
921 l_request.param6 := p_param6;
922 l_request.param7 := p_param7;
923 l_request.param8 := p_param8;
924 l_request.param9 := p_param9;
925 l_request.param10 := p_param10;
926 l_request.param11 := p_param11;
927 l_request.param12 := p_param12;
928 l_request.param13 := p_param13;
929 l_request.param14 := p_param14;
930 l_request.param15 := p_param15;
931 l_request.param16 := p_param16;
932 l_request.param17 := p_param17;
933 l_request.param18 := p_param18;
934 l_request.param19 := p_param19;
935 l_request.param20 := p_param20;
936 l_request.param21 := p_param21;
937 l_request.param22 := p_param22;
938 l_request.param23 := p_param23;
939 l_request.param24 := p_param24;
940 l_request.param25 := p_param25;
941 l_request.long_param1 := p_long_param1;
942
943 -- Initialize the return variable
944 x_return_status := FND_API.G_RET_STS_SUCCESS;
945 IF p_entity_id IS NOT NULL THEN --2650093
946 Check_For_Request(p_entity_code,
947 p_entity_id,
948 p_request_type,
949 p_request_unique_key1,
950 p_request_unique_key2,
951 p_request_unique_key3,
952 p_request_unique_key4,
953 p_request_unique_key5,
954 l_request_ind,
955 l_request_search_rslt,
956 l_return_status);
957
958 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
960 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
961 RAISE FND_API.G_EXC_ERROR;
962 END IF;
963
964 l_request.processed := 'N';
965 IF l_request_search_rslt = FND_API.g_true -- replace the request
966 THEN
967 OE_Debug_PUB.ADD('Request replaced');
968 g_delayed_requests(l_request_ind) := l_request;
969
970 ELSE -- insert the new request
971 OE_Debug_PUB.ADD('New request inserted');
972 -- l_request_ind := nvl(g_delayed_requests.LAST, 0) + 1; --2502849
973 g_delayed_requests(l_request_ind) := l_request; --Added for 2502849
974 END IF;
975
976 -- Initialize the requesting entity record
977 l_req_entity.entity_code := p_requesting_entity_code;
978 l_req_entity.entity_id := p_requesting_entity_id;
979 l_req_entity.request_index := l_request_ind;
980
984 END IF;--2650093
981 l_req_entity_ind := nvl(g_requesting_entities.LAST, 0) + 1;
982 -- Insert into the requesting entities table
983 g_requesting_entities(l_req_entity_ind) := l_req_entity;
985 oe_debug_pub.add('end of log request');
986
987 EXCEPTION
988
989 WHEN FND_API.G_EXC_ERROR THEN
990
991 x_return_status := FND_API.G_RET_STS_ERROR;
992
993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
994
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
996
997 WHEN OTHERS THEN
998 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
999 THEN
1000 OE_MSG_PUB.Add_Exc_Msg
1001 (G_PKG_NAME
1002 ,'LOGREQUEST');
1003 END IF;
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005
1006 End Log_Request;
1007
1008
1009
1010 Function Check_for_Request( p_entity_code IN VARCHAR2,
1011 p_entity_id IN NUMBER,
1012 p_request_type IN VARCHAR2,
1013 p_request_unique_key1 IN VARCHAR2 := NULL,
1014 p_request_unique_key2 IN VARCHAR2 := NULL,
1015 p_request_unique_key3 IN VARCHAR2 := NULL,
1016 p_request_unique_key4 IN VARCHAR2 := NULL,
1017 p_request_unique_key5 IN VARCHAR2 := NULL
1018 )
1019 RETURN BOOLEAN IS
1020 l_ind Number;
1021 x_result Varchar2(30);
1022 x_return_status Varchar2(30);
1023 Begin
1024 IF p_entity_id IS NOT NULL THEN --2650093
1025 Check_for_Request( p_entity_code => p_entity_code
1026 ,p_entity_id => p_entity_id
1027 ,p_request_type => p_request_type
1028 ,p_request_unique_key1 => p_request_unique_key1
1029 ,p_request_unique_key2 => p_request_unique_key2
1030 ,p_request_unique_key3 => p_request_unique_key3
1031 ,p_request_unique_key4 => p_request_unique_key4
1032 ,p_request_unique_key5 => p_request_unique_key5
1033 ,x_request_ind => l_ind
1034 ,x_result => x_result
1035 ,x_return_status => x_return_status);
1036
1037 if x_result = FND_API.G_TRUE then
1038 return(TRUE);
1039 else
1040 return(FALSE);
1041 end if;
1042
1043 /* Added for 2650093 */
1044 ELSE
1045 return(FALSE);
1046 END IF;
1047 End;
1048
1049 Procedure Delete_Request(p_entity_code IN VARCHAR2
1050 ,p_entity_id IN NUMBER
1051 ,p_request_Type IN VARCHAR2
1052 ,p_request_unique_key1 IN VARCHAR2 := NULL
1053 ,p_request_unique_key2 IN VARCHAR2 := NULL
1054 ,p_request_unique_key3 IN VARCHAR2 := NULL
1055 ,p_request_unique_key4 IN VARCHAR2 := NULL
1056 ,p_request_unique_key5 IN VARCHAR2 := NULL
1057 ,x_return_status OUT NOCOPY VARCHAR2)
1058 IS
1059 l_request_search_rslt Varchar2(1);
1060 l_return_status Varchar2(1);
1061 l_request_ind number;
1062 l_req_entity_ind number;
1063 BEGIN
1064
1065 oe_debug_pub.add('Entering Procedure Delete_Request in Package QP_Delayed_Requests_Pvt');
1066
1067 x_return_status := FND_API.G_RET_STS_SUCCESS;
1068 IF p_entity_id IS NOT NULL THEN --2650093
1069 Check_for_Request( p_entity_code => p_entity_code
1070 ,p_entity_id => p_entity_id
1071 ,p_request_type => p_request_type
1072 ,p_request_unique_key1 => p_request_unique_key1
1073 ,p_request_unique_key2 => p_request_unique_key2
1074 ,p_request_unique_key3 => p_request_unique_key3
1075 ,p_request_unique_key4 => p_request_unique_key4
1076 ,p_request_unique_key5 => p_request_unique_key5
1077 ,x_request_ind => l_request_ind
1078 ,x_result => l_request_search_rslt
1079 ,x_return_status => l_return_status);
1080
1081
1082 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1085 RAISE FND_API.G_EXC_ERROR;
1086 END IF;
1087
1088 if l_request_search_rslt = FND_API.G_TRUE -- delete the request
1089 then
1090 G_Delayed_Requests.Delete(l_request_ind);
1091
1092 /* BUG 2502849- do not delete from req entities table
1093 , delete table in the end when all requests are processed
1094 This is to improve performance as this search loops through
1095 the entire pl/sql table which becomes very large with
1096 greater number of lines being processed
1097
1098 -- Delete all the records in the requesting entities table
1099 -- that have this request.
1100 l_req_entity_ind := G_Requesting_Entities.First;
1101 WHILE l_req_entity_ind IS NOT NULL LOOP
1102 IF G_Requesting_Entities(l_req_entity_ind).request_index = l_request_ind
1103 THEN
1104 G_Requesting_Entities.Delete(l_req_entity_ind);
1105 END IF;
1106 l_req_entity_ind := G_Requesting_Entities.Next(l_req_entity_ind);
1107 END LOOP; */
1108 end if;
1109 END IF; --2650093
1110 EXCEPTION
1111
1112 WHEN FND_API.G_EXC_ERROR THEN
1113
1114 x_return_status := FND_API.G_RET_STS_ERROR;
1115
1116 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1117
1121
1118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1119
1120 when others THEN
1122 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1123 OE_MSG_PUB.Add_Exc_Msg
1124 (G_PKG_NAME
1125 ,'DeleteRequest');
1126 END IF;
1127
1128 x_return_status := FND_API.G_RET_STS_ERROR;
1129
1130 End Delete_Request;
1131
1132 Procedure Clear_Request( x_return_status OUT NOCOPY VARCHAR2)
1133 IS
1134 BEGIN
1135
1136 oe_debug_pub.add('Entering Procedure Clear_Request in Package QP_Delayed_Requests_Pvt');
1137
1138 x_return_status := FND_API.G_RET_STS_SUCCESS;
1139
1140 G_Delayed_Requests.DELETE;
1141 g_requesting_entities.DELETE;
1142
1143 EXCEPTION
1144
1145 WHEN OTHERS THEN
1146
1147 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1148 OE_MSG_PUB.Add_Exc_Msg
1149 (G_PKG_NAME
1150 ,'Clear_Request');
1151 END IF;
1152
1153 x_return_status := FND_API.G_RET_STS_ERROR;
1154
1155 End Clear_Request;
1156
1157 Procedure Process_Request( p_entity_code IN VARCHAR2
1158 ,p_entity_id IN Number
1159 ,p_request_Type IN VARCHAR2
1160 ,p_request_unique_key1 IN VARCHAR2 := NULL
1161 ,p_request_unique_key2 IN VARCHAR2 := NULL
1162 ,p_request_unique_key3 IN VARCHAR2 := NULL
1163 ,p_request_unique_key4 IN VARCHAR2 := NULL
1164 ,p_request_unique_key5 IN VARCHAR2 := NULL
1165 ,p_delete IN Varchar2 Default
1166 FND_API.G_TRUE
1167 ,x_return_status OUT NOCOPY Varchar2)
1168 IS
1169 l_request_ind Number;
1170 l_request_search_rslt Varchar2(30);
1171 l_return_status Varchar2(30);
1172 Begin
1173 x_return_status := FND_API.G_RET_STS_SUCCESS;
1174
1175 oe_debug_pub.add('Entering Procedure Process_Request in Package QP_Delayed_Requests_Pvt');
1176 IF p_entity_id IS NOT NULL THEN --2650093
1177 Check_for_Request( p_entity_code => p_entity_code
1178 ,p_entity_id => p_entity_id
1179 ,p_request_type => p_request_type
1180 ,p_request_unique_key1 => p_request_unique_key1
1181 ,p_request_unique_key2 => p_request_unique_key2
1182 ,p_request_unique_key3 => p_request_unique_key3
1183 ,p_request_unique_key4 => p_request_unique_key4
1184 ,p_request_unique_key5 => p_request_unique_key5
1185 ,x_request_ind => l_request_ind
1186 ,x_result => l_request_search_rslt
1187 ,x_return_status => l_return_status);
1188
1189 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1191 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1192 RAISE FND_API.G_EXC_ERROR;
1193 END IF;
1194
1195 if l_request_search_rslt = FND_API.G_TRUE then
1196 Process_Request_Pvt
1197 (p_request_ind => l_request_ind
1198 ,p_delete => p_delete
1199 ,x_return_status => l_return_status
1200 );
1201 end if;
1202
1203 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1205 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1206 RAISE FND_API.G_EXC_ERROR;
1207 END IF;
1208 END IF; --2650093
1209 EXCEPTION
1210
1211 WHEN FND_API.G_EXC_ERROR THEN
1212
1213 x_return_status := FND_API.G_RET_STS_ERROR;
1214
1215 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1216
1217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1218
1219 WHEN OTHERS THEN
1220
1221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1222
1223 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1224 THEN
1225 OE_MSG_PUB.Add_Exc_Msg
1226 ( G_PKG_NAME
1227 , 'Process_Request'
1228 );
1229 END IF;
1230
1231 End Process_Request;
1232
1233
1234 Procedure Process_Request_for_Entity
1235 ( p_entity_code in Varchar2
1236 ,p_delete in Varchar2 Default FND_API.G_TRUE
1237 ,x_return_status OUT NOCOPY Varchar2) IS
1238 l_return_status Varchar2(30);
1239 l_ind Number;
1240 Begin
1241 x_return_status := FND_API.G_RET_STS_SUCCESS;
1242
1243 oe_debug_pub.add('Entering Procedure Process_Request_for_Entity in Package QP_Delayed_Requests_Pvt');
1244 --dbms_output.put_line('Entering Procedure Process_Request_for_Entity in Package QP_Delayed_Requests_Pvt');
1245 --dbms_output.put_line('entity id is ' ||p_entity_code);
1246
1247 l_ind := G_Delayed_Requests.first;
1248
1249 /*WHILE l_ind IS NOT NULL loop
1250 oe_debug_pub.add('entity is ' ||G_Delayed_Requests(l_ind).Entity_code);
1251 --dbms_output.put_line('entity is ' ||G_Delayed_Requests(l_ind).Entity_code);
1252 oe_debug_pub.add('entity id is ' ||G_Delayed_Requests(l_ind).Entity_id);
1253 --dbms_output.put_line('entity id is ' ||G_Delayed_Requests(l_ind).Entity_id);
1254 END LOOP;*/
1255
1256 l_ind := G_Delayed_Requests.first;
1257
1261 --dbms_output.put_line('found the match');
1258 WHILE l_ind IS NOT NULL LOOP
1259 --dbms_output.put_line('entity id is ' ||G_Delayed_Requests(l_ind).Entity_id);
1260 IF G_Delayed_Requests(l_ind).Entity_code = p_entity_code THEN
1262 --dbms_output.put_line('l_ind is '||l_ind);
1263 Process_Request_Pvt
1264 (p_request_ind => l_ind
1265 ,p_delete => p_delete
1266 ,x_return_status => l_return_status
1267 );
1268
1269 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1272 RAISE FND_API.G_EXC_ERROR;
1273 END IF;
1274 --EXIT;
1275 END IF;
1276 l_ind := G_Delayed_Requests.Next(l_ind);
1277 END LOOP;
1278 oe_debug_pub.add('Exiting Process_Request_for_Entity ');
1279 --dbms_output.put_line('Exiting Process_Request_for_Entity ');
1280
1281 EXCEPTION
1282
1283 WHEN FND_API.G_EXC_ERROR THEN
1284
1285 x_return_status := FND_API.G_RET_STS_ERROR;
1286
1287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1288
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1290
1291 WHEN OTHERS THEN
1292
1293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1294
1295 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1296 THEN
1297 OE_MSG_PUB.Add_Exc_Msg
1298 ( G_PKG_NAME
1299 , 'Process_Request_for_Entity'
1300 );
1301 END IF;
1302
1303 oe_debug_pub.add('Exiting Process_Request_for_Entity ');
1304
1305 End Process_Request_for_Entity;
1306
1307 Procedure Process_Request_for_ReqType
1308 ( p_request_type in Varchar2
1309 ,p_delete in Varchar2 Default FND_API.G_TRUE
1310 ,x_return_status OUT NOCOPY Varchar2
1311 ) IS
1312 l_return_status Varchar2(30);
1313 l_ind Number;
1314 Begin
1315 x_return_status := FND_API.G_RET_STS_SUCCESS;
1316
1317 oe_debug_pub.add('Entering Procedure Process_Request_for_ReqType in Package QP_Delayed_Requests_Pvt');
1318
1319 l_ind := G_Delayed_Requests.first;
1320
1321 WHILE l_ind IS NOT NULL LOOP
1322 IF G_Delayed_Requests(l_ind).request_type = p_request_type THEN
1323 Process_Request_Pvt
1324 (p_request_ind => l_ind
1325 ,p_delete => p_delete
1326 ,x_return_status => l_return_status
1327 );
1328
1329 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1331 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1332 RAISE FND_API.G_EXC_ERROR;
1333 END IF;
1334 END IF;
1335 l_ind := G_Delayed_Requests.Next(l_ind);
1336 END LOOP;
1337
1338 EXCEPTION
1339
1340 WHEN FND_API.G_EXC_ERROR THEN
1341
1342 x_return_status := FND_API.G_RET_STS_ERROR;
1343
1344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1345
1346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1347
1348 WHEN OTHERS THEN
1349
1350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1351
1352 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1353 THEN
1354 OE_MSG_PUB.Add_Exc_Msg
1355 ( G_PKG_NAME
1356 , 'Process_Request_for_ReqType'
1357 );
1358 END IF;
1359
1360 End Process_Request_for_ReqType;
1361
1362
1363 Procedure Process_Delayed_Requests(
1364 x_return_status OUT NOCOPY Varchar2
1365 ) IS
1366 l_return_status Varchar2(30);
1367 Begin
1368
1369 oe_debug_pub.add('Entering Procedure Process_Delayed_Requests in Package QP_Delayed_Requests_Pvt');
1370
1371 -- Process requests as per the dependency
1372 -- This procedure processes all requests.
1373 -- For each request type defined in QP_GLOBALS
1374 -- write one code block as shown below.
1375
1376 -- start bug2091362
1377 Process_Request_for_ReqType
1378 (p_request_type =>QP_GLOBALS.G_DUPLICATE_MODIFIER_LINES
1379 ,p_delete => FND_API.G_TRUE
1380 ,x_return_status => l_return_status
1381 );
1382 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1385 RAISE FND_API.G_EXC_ERROR;
1386 END IF;
1387
1388 -- end bug2091362
1389
1390
1391 Process_Request_for_ReqType
1392 (p_request_type =>QP_GLOBALS.G_DUPLICATE_LIST_LINES
1393 ,p_delete => FND_API.G_TRUE
1394 ,x_return_status => l_return_status
1395 );
1396 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1398 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1399 RAISE FND_API.G_EXC_ERROR;
1400 END IF;
1401
1402
1403 /*included by spgopal for performance problem, to include phase_id and header_idinfo in qp_pricing_attributes table*/
1404
1405 Process_Request_for_ReqType
1409 );
1406 (p_request_type =>QP_GLOBALS.G_UPDATE_PRICING_ATTR_PHASE
1407 ,p_delete => FND_API.G_TRUE
1408 ,x_return_status => l_return_status
1410 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1412 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1413 RAISE FND_API.G_EXC_ERROR;
1414 END IF;
1415
1416
1417 /*included by spgopal for pricing phase seeded data over-write problem to update the denormalised columns in QP_PRICING_PHASES*/
1418
1419 Process_Request_for_ReqType
1420 (p_request_type =>QP_GLOBALS.G_UPDATE_PRICING_PHASE
1421 ,p_delete => FND_API.G_TRUE
1422 ,x_return_status => l_return_status
1423 );
1424 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1426 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1427 RAISE FND_API.G_EXC_ERROR;
1428 END IF;
1429
1430 /*fix for bug 1501138*/
1431 /*
1432 Process_Request_for_ReqType
1433 (p_request_type =>QP_GLOBALS.G_UPDATE_PRICING_PHASE
1434 ,p_delete => FND_API.G_TRUE
1435 ,x_return_status => l_return_status
1436 );
1437 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1439 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1440 RAISE FND_API.G_EXC_ERROR;
1441 END IF;
1442
1443 */
1444
1445 Process_Request_for_ReqType
1446 (p_request_type =>QP_GLOBALS.G_MULTIPLE_PRICE_BREAK_ATTRS
1447 ,p_delete => FND_API.G_TRUE
1448 ,x_return_status => l_return_status
1449 );
1450 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1451 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1452 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1453 RAISE FND_API.G_EXC_ERROR;
1454 END IF;
1455
1456
1457 Process_Request_for_ReqType
1458 (p_request_type => QP_GLOBALS.G_MIXED_QUAL_SEG_LEVELS
1459 ,p_delete => FND_API.G_TRUE
1460 ,x_return_status => l_return_status
1461 );
1462 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1464 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1465 RAISE FND_API.G_EXC_ERROR;
1466 END IF;
1467
1468
1469 Process_Request_for_ReqType
1470 (p_request_type =>QP_GLOBALS.G_OVERLAPPING_PRICE_BREAKS
1471 ,p_delete => FND_API.G_TRUE
1472 ,x_return_status => l_return_status
1473 );
1474 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1476 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1477 RAISE FND_API.G_EXC_ERROR;
1478 END IF;
1479
1480
1481 Process_Request_for_ReqType
1482 (p_request_type =>QP_GLOBALS.G_MAINTAIN_QUALIFIER_DEN_COLS
1483 ,p_delete => FND_API.G_TRUE
1484 ,x_return_status => l_return_status
1485 );
1486 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1488 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1489 RAISE FND_API.G_EXC_ERROR;
1490 END IF;
1491
1492 Process_Request_for_ReqType
1493 (p_request_type =>QP_GLOBALS.G_UPDATE_LIST_QUAL_IND
1494 ,p_delete => FND_API.G_TRUE
1495 ,x_return_status => l_return_status
1496 );
1497 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1499 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1500 RAISE FND_API.G_EXC_ERROR;
1501 END IF;
1502
1503 Process_Request_for_ReqType
1504 (p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND
1505 ,p_delete => FND_API.G_TRUE
1506 ,x_return_status => l_return_status
1507 );
1508 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1510 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1511 RAISE FND_API.G_EXC_ERROR;
1512 END IF;
1513
1514 Process_Request_for_ReqType
1515 (p_request_type =>QP_GLOBALS.G_UPDATE_LIMITS_COLUMNS
1516 ,p_delete => FND_API.G_TRUE
1517 ,x_return_status => l_return_status
1518 );
1519 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1521 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1522 RAISE FND_API.G_EXC_ERROR;
1523 END IF;
1524
1525 Process_Request_for_ReqType
1526 (p_request_type =>QP_GLOBALS.G_UPDATE_QUALIFIER_STATUS
1527 ,p_delete => FND_API.G_TRUE
1528 ,x_return_status => l_return_status
1529 );
1530 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1532 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1533 RAISE FND_API.G_EXC_ERROR;
1534 END IF;
1535
1539 ,x_return_status => l_return_status
1536 Process_Request_for_ReqType
1537 (p_request_type =>QP_GLOBALS.G_CREATE_SECURITY_PRIVILEGE
1538 ,p_delete => FND_API.G_TRUE
1540 );
1541 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1543 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1544 RAISE FND_API.G_EXC_ERROR;
1545 END IF;
1546
1547 Process_Request_for_ReqType
1548 (p_request_type =>QP_GLOBALS.G_UPDATE_ATTRIBUTE_STATUS
1549 ,p_delete => FND_API.G_TRUE
1550 ,x_return_status => l_return_status
1551 );
1552 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1555 RAISE FND_API.G_EXC_ERROR;
1556 END IF;
1557
1558 Process_Request_for_ReqType
1559 (p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND
1560 ,p_delete => FND_API.G_TRUE
1561 ,x_return_status => l_return_status
1562 );
1563 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1565 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1566 RAISE FND_API.G_EXC_ERROR;
1567 END IF;
1568
1569 Process_Request_for_ReqType
1570 (p_request_type =>QP_GLOBALS.G_MAINTAIN_LIST_HEADER_PHASES
1571 ,p_delete => FND_API.G_TRUE
1572 ,x_return_status => l_return_status
1573 );
1574 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1576 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1577 RAISE FND_API.G_EXC_ERROR;
1578 END IF;
1579
1580 Process_Request_for_ReqType
1581 (p_request_type =>QP_GLOBALS.G_MAINTAIN_FACTOR_LIST_ATTRS
1582 ,p_delete => FND_API.G_TRUE
1583 ,x_return_status => l_return_status
1584 );
1585 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1587 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1588 RAISE FND_API.G_EXC_ERROR;
1589 END IF;
1590
1591 -- mkarya for attribute manager
1592 Process_Request_for_ReqType
1593 (p_request_type =>QP_GLOBALS.G_CHECK_LINE_FOR_HEADER_QUAL
1594 ,p_delete => FND_API.G_TRUE
1595 ,x_return_status => l_return_status
1596 );
1597 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1599 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1600 RAISE FND_API.G_EXC_ERROR;
1601 END IF;
1602
1603 -- Hierarchical Categories (sfiresto)
1604 Process_Request_for_ReqType
1605 (p_request_type =>QP_GLOBALS.G_CHECK_ENABLED_FUNC_AREAS
1606 ,p_delete => FND_API.G_TRUE
1607 ,x_return_status => l_return_status
1608 );
1609 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1611 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1612 RAISE FND_API.G_EXC_ERROR;
1613 END IF;
1614
1615
1616 --hw
1617
1618 -- delayed request for changed lines
1619 if QP_PERF_PVT.enabled = 'Y' then
1620 Process_Request_for_ReqType
1621 (p_request_type =>QP_GLOBALS.G_UPDATE_CHANGED_LINES_ADD
1622 ,p_delete => FND_API.G_TRUE
1623 ,x_return_status => l_return_status
1624 );
1625 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1627 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1628 RAISE FND_API.G_EXC_ERROR;
1629 END IF;
1630
1631 Process_Request_for_ReqType
1632 (p_request_type =>QP_GLOBALS.G_UPDATE_CHANGED_LINES_DEL
1633 ,p_delete => FND_API.G_TRUE
1634 ,x_return_status => l_return_status
1635 );
1636 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1638 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1639 RAISE FND_API.G_EXC_ERROR;
1640 END IF;
1641
1642 Process_Request_for_ReqType
1643 (p_request_type =>QP_GLOBALS.G_UPDATE_CHANGED_LINES_ACT
1644 ,p_delete => FND_API.G_TRUE
1645 ,x_return_status => l_return_status
1646 );
1647 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1649 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1650 RAISE FND_API.G_EXC_ERROR;
1651 END IF;
1652
1653 Process_Request_for_ReqType
1654 (p_request_type =>QP_GLOBALS.G_UPDATE_CHANGED_LINES_PH
1655 ,p_delete => FND_API.G_TRUE
1656 ,x_return_status => l_return_status
1657 );
1658 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1659 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1660 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1661 RAISE FND_API.G_EXC_ERROR;
1662 END IF;
1666 -- mkarya for pattern delayed requests
1663 end if;
1664 oe_debug_pub.add('Ren: in process delayed request');
1665
1667 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
1668 Process_Request_for_ReqType
1669 (p_request_type =>QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN
1670 ,p_delete => FND_API.G_TRUE
1671 ,x_return_status => l_return_status
1672 );
1673
1674 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1676 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1677 RAISE FND_API.G_EXC_ERROR;
1678 END IF;
1679
1680 Process_Request_for_ReqType
1681 (p_request_type =>QP_GLOBALS.G_MAINTAIN_LINE_PATTERN
1682 ,p_delete => FND_API.G_TRUE
1683 ,x_return_status => l_return_status
1684 );
1685
1686 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1688 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1689 RAISE FND_API.G_EXC_ERROR;
1690 END IF;
1691
1692 Process_Request_for_ReqType
1693 (p_request_type =>QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN
1694 ,p_delete => FND_API.G_TRUE
1695 ,x_return_status => l_return_status
1696 );
1697
1698 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1700 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1701 RAISE FND_API.G_EXC_ERROR;
1702 END IF;
1703
1704 END IF; --IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y'
1705
1706
1707 -- clear the delayed request cache
1708 Clear_Request(x_return_status);
1709
1710 EXCEPTION
1711
1712 WHEN FND_API.G_EXC_ERROR THEN
1713
1714 x_return_status := FND_API.G_RET_STS_ERROR;
1715
1716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1717
1718 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1719
1720 WHEN OTHERS THEN
1721
1722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1723
1724 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1725 THEN
1726 OE_MSG_PUB.Add_Exc_Msg
1727 ( G_PKG_NAME
1728 , 'Process_Delayed_Requests'
1729 );
1730 END IF;
1731
1732 End Process_Delayed_Requests;
1733
1734
1735 Procedure Delete_Reqs_for_Deleted_Entity ( p_entity_code IN Varchar2
1736 , p_entity_id in Number
1737 , x_return_status OUT NOCOPY Varchar2)
1738 IS
1739 i number;
1740 j number;
1741 req_ind number;
1742 request_delete BOOLEAN;
1743 BEGIN
1744
1745
1746
1747 x_return_status := FND_API.G_RET_STS_SUCCESS;
1748
1749
1750 -- DELETING REQUESTS LOGGED AGAINST THIS ENTITY
1751
1752 i := G_Delayed_Requests.first;
1753
1754 WHILE i IS NOT NULL LOOP
1755
1756 IF (G_Delayed_Requests(i).entity_code = p_entity_code
1757 AND G_Delayed_Requests(i).entity_id = p_entity_id) THEN
1758
1759 -- delete records in requesting entity tables with this request
1760 j := G_Requesting_Entities.first;
1761 WHILE j IS NOT NULL LOOP
1762 IF G_Requesting_Entities(j).request_index = i THEN
1763 G_Requesting_Entities.Delete(j);
1764 END IF;
1765 j := G_Requesting_Entities.Next(j);
1766 END LOOP;
1767
1768 -- delete the delayed request
1769 G_Delayed_Requests.Delete(i);
1770
1771 END IF;
1772
1773 i := G_Delayed_Requests.Next(i);
1774
1775 END LOOP;
1776
1777
1778 -- DELETING REQUESTS LOGGED BY THIS ENTITY
1779
1780 i := G_Requesting_Entities.first;
1781
1782 WHILE i IS NOT NULL LOOP
1783
1784 -- search for requests logged by this entity
1785
1786 IF (G_Requesting_Entities(i).entity_code = p_entity_code
1787 AND G_Requesting_Entities(i).entity_id = p_entity_id) THEN
1788
1789 req_ind := G_Requesting_Entities(i).request_index;
1790
1791 -- initialize request delete to TRUE
1792 request_delete := TRUE;
1793
1794 -- set the delete to FALSE if there are other entities that
1795 -- logged the same request but if the same entity has logged this
1796 -- request, then delete in the requesting entities table
1797
1798 j := G_Requesting_Entities.first;
1799 WHILE j IS NOT NULL LOOP
1800 IF G_Requesting_Entities(j).request_index = req_ind THEN
1801 IF (G_Requesting_Entities(j).entity_code = p_entity_code
1802 AND G_Requesting_Entities(j).entity_id = p_entity_id) THEN
1803 G_Requesting_Entities.Delete(j);
1804 ELSE
1805 request_delete := FALSE;
1806 END IF;
1807 END IF;
1808 j := G_Requesting_Entities.Next(j);
1809 END LOOP;
1810
1811 -- deleting the delayed request
1812 IF request_delete
1813 AND G_Delayed_Requests.Exists(req_ind) THEN
1814 G_Delayed_Requests.Delete(req_ind);
1815 END IF;
1816
1817 END IF;
1818
1819 i := G_Requesting_Entities.Next(i);
1820
1821 END LOOP;
1822
1823 EXCEPTION
1824
1825 WHEN FND_API.G_EXC_ERROR THEN
1826
1827 x_return_status := FND_API.G_RET_STS_ERROR;
1828
1829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1830
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1832
1833 when others THEN
1834
1835 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1836 OE_MSG_PUB.Add_Exc_Msg
1837 (G_PKG_NAME
1838 ,'Delete_Reqs_for_Deleted_Entity');
1839 END IF;
1840
1841 x_return_status := FND_API.G_RET_STS_ERROR;
1842
1843 End Delete_Reqs_for_Deleted_Entity;
1844
1845
1846
1847
1848 END QP_Delayed_Requests_Pvt;