[Home] [Help]
PACKAGE BODY: APPS.QP_DELAYED_REQUESTS_PVT
Source
1 PACKAGE BODY QP_Delayed_Requests_PVT AS
2 /* $Header: QPXVREQB.pls 120.6.12020000.2 2012/09/18 11:56:19 kdurgasi ship $ */
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' and nvl(FND_PROFILE.VALUE('QP_CONTINUOUS_PB'),'Y')='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;
337
334 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
338 IF l_request_type = QP_Globals.G_UPGRADE_PRICE_BREAKS
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
387 IF l_request_type = QP_GLOBALS.G_UPDATE_LIST_QUAL_IND
384 RAISE FND_API.G_EXC_ERROR;
385 END IF;
386
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);
457 END IF;
458
459 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
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
497 --the line id is present in the QP_LIST_LINES table
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
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;
590 END IF;
591
592 -- Essilor Fix bug 2789138
593 IF l_request_type = QP_GLOBALS.G_UPDATE_MANUAL_MODIFIER_FLAG
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);
616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614
615 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
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
656 p_list_header_id => l_request_rec.entity_Id
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(
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 /* code for PL/SQL pattern search delayed request */
672
673 --pattern
674
675 -- g_qp_pattern_search := FND_PROFILE.VALUE('QP_PATTERN_SEARCH');
676
677 oe_debug_pub.ADD('JAGAN JAVA ENGINE INSTALLED : ' || QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed);
678 oe_debug_pub.ADD('JAGAN QP PATTERN SEARCH : ' || FND_PROFILE.VALUE('QP_PATTERN_SEARCH'));
679 oe_debug_pub.ADD('JAGAN REQUEST TYPE : ' || l_request_type);
680 oe_debug_pub.ADD('JAGAN ENTITY CODE : ' || l_entity_code);
681 oe_debug_pub.ADD('JAGAN REQUIRED ENTITY CODE IS : ' || QP_GLOBALS.G_ENTITY_MODIFIER_LIST ||','|| QP_GLOBALS.G_ENTITY_MODIFIERS);
682
683 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'N' THEN
684 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'M' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'P' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' THEN
685 --AND ( l_entity_code = QP_GLOBALS.G_ENTITY_MODIFIER_LIST OR l_entity_code = QP_GLOBALS.G_ENTITY_MODIFIERS OR l_entity_code = QP_GLOBALS.G_ENTITY_ALL) THEN
686
687 IF l_request_type = QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN THEN
688 oe_debug_pub.add('Processing req Attribute Groups');
689 QP_DELAYED_REQUESTS_UTIL.maintain_header_pattern(
690 p_list_header_id => l_request_rec.entity_Id
691 , p_qualifier_group => l_request_rec.request_unique_key1
692 , p_setup_action => l_request_rec.request_unique_key2
693 , x_return_status => l_return_status);
694 oe_debug_pub.ADD(' JAGAN 1 ENTITY CODE : ' || l_entity_code);
695 END IF;
696 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
698 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
699 RAISE FND_API.G_EXC_ERROR;
700 END IF;
701 IF l_request_type = QP_GLOBALS.G_MAINTAIN_LINE_PATTERN THEN
702 oe_debug_pub.add('Processing req Attribute Groups');
703 QP_DELAYED_REQUESTS_UTIL.maintain_line_pattern(
704 p_list_header_id => l_request_rec.entity_Id
705 , p_list_line_id => l_request_rec.request_unique_key1
706 , p_qualifier_group => l_request_rec.request_unique_key2
707 , p_setup_action => l_request_rec.request_unique_key3
708 , x_return_status => l_return_status);
709 oe_debug_pub.ADD(' JAGAN 2 ENTITY CODE : ' || l_entity_code);
710 END IF;
714 RAISE FND_API.G_EXC_ERROR;
711 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
713 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
715 END IF;
716
717 IF l_request_type = QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN THEN
718 oe_debug_pub.add('Processing req Attribute Groups');
719 QP_DELAYED_REQUESTS_UTIL.maintain_product_pattern(
720 p_list_header_id => l_request_rec.entity_Id
721 , p_list_line_id => l_request_rec.request_unique_key1
722 , p_setup_action => l_request_rec.request_unique_key2
723 , x_return_status => l_return_status);
724 oe_debug_pub.ADD('JAGAN 3 ENTITY CODE : ' || l_entity_code);
725 END IF;
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 END IF; --pl/sql pattern search profile on
733 END IF; --Java Engine Installed
734
735 --pattern
736 IF l_request_type = QP_GLOBALS.G_VALIDATE_SELLING_ROUNDING
737 THEN
738 oe_debug_pub.add('processing req VALIDATE_SELLING_ROUNDING');
739 QP_DELAYED_REQUESTS_UTIL.validate_selling_rounding(
740 p_currency_header_id => l_entity_id,
741 p_to_currency_code => l_request_rec.param1,
742 x_return_status => l_return_status);
743 END IF;
744
745 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
747 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
748 RAISE FND_API.G_EXC_ERROR;
749 END IF;
750
751
752 IF l_request_type = QP_GLOBALS.G_CHECK_SEGMENT_LEVEL_IN_GROUP
753 THEN
754 oe_debug_pub.add('processing req CHECK_SEGMENT_LEVEL_IN_GROUP');
755 QP_DELAYED_REQUESTS_UTIL.check_segment_level_in_group(
756 p_list_line_id => l_entity_id,
757 p_list_header_id => l_request_rec.request_unique_key1,
758 p_qualifier_grouping_no => l_request_rec.request_unique_key2,
759 x_return_status => l_return_status);
760 END IF;
761
762 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
764 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
765 RAISE FND_API.G_EXC_ERROR;
766 END IF;
767
768 IF l_request_type = QP_GLOBALS.G_CHECK_LINE_FOR_HEADER_QUAL
769 THEN
770 oe_debug_pub.add('processing req CHECK_LINE_FOR_HEADER_QUAL');
771 QP_DELAYED_REQUESTS_UTIL.CHECK_LINE_FOR_HEADER_QUAL(
772 p_list_header_id => l_entity_id,
773 p_list_line_id => l_request_rec.request_unique_key1,
774 x_return_status => l_return_status);
775 END IF;
776
777 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
779 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
780 RAISE FND_API.G_EXC_ERROR;
781 END IF;
782
783 -- Hierarchical Categories (sfiresto)
784 IF l_request_type = QP_GLOBALS.G_CHECK_ENABLED_FUNC_AREAS THEN
785 oe_debug_pub.add('processing req CHECK_ENABLED_FUNC_AREAS');
786 QP_DELAYED_REQUESTS_UTIL.Check_Enabled_Func_Areas(
787 p_pte_source_system_id => l_entity_id,
788 x_return_status => l_return_status);
789 END IF;
790
791 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
792 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
794 RAISE FND_API.G_EXC_ERROR;
795 END IF;
796
797
798 --hw
799 -- delayed request for changed lines
803 oe_debug_pub.add('processing req G_UPDATE_CHANGED_LINES_ADD');
800 if QP_PERF_PVT.enabled = 'Y' then
801
802 if l_request_type = QP_GLOBALS.G_UPDATE_CHANGED_LINES_ADD then
804 QP_DELAYED_REQUESTS_UTIL.update_changed_lines_add(
805 p_list_line_id => l_entity_id,
806 p_list_header_id => l_request_rec.param2,
807 p_pricing_phase_id => l_request_rec.param1,
808 x_return_status => l_return_status);
809 elsif l_request_type = QP_GLOBALS.G_UPDATE_CHANGED_LINES_DEL then
810 oe_debug_pub.add('processing req G_UPDATE_CHANGED_LINES_DEL');
811 QP_DELAYED_REQUESTS_UTIL.update_changed_lines_del(
812 p_list_line_id => l_entity_id,
813 p_list_header_id => l_request_rec.param2,
814 p_pricing_phase_id => l_request_rec.param1,
815 p_product_attribute => l_request_rec.param3,
816 p_product_attr_value => l_request_rec.param4,
817 x_return_status => l_return_status);
818 elsif l_request_type = QP_GLOBALS.G_UPDATE_CHANGED_LINES_PH then
819 oe_debug_pub.add('processing req G_UPDATE_CHANGED_LINES_PHASE');
820 QP_DELAYED_REQUESTS_UTIL.update_changed_lines_ph(
821 p_list_line_id => l_entity_id,
822 p_list_header_id => l_request_rec.param2,
823 p_pricing_phase_id => l_request_rec.param1,
824 p_old_pricing_phase_id => l_request_rec.param3,
825 x_return_status => l_return_status);
826 elsif l_request_type = QP_GLOBALS.G_UPDATE_CHANGED_LINES_ACT then
827 oe_debug_pub.add('processing req G_UPDATE_CHANGED_LINES_ACTIVE');
828 QP_DELAYED_REQUESTS_UTIL.update_changed_lines_act(
829 p_list_header_id => l_entity_id,
830 p_active_flag => l_request_rec.param1,
831 x_return_status => l_return_status);
832 --hvop elsif l_request_type = QP_GLOBALS.G_UPDATE_HVOP then
833 oe_debug_pub.add('processing req G_UPDATE_HVOP')
834 ;
835 QP_DELAYED_REQUESTS_UTIL.HVOP_Pricing_Setup(x_return_status => l_return_status);
836 --hvop
837 end if;
838 end if;
839
840 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845
846
847 IF (p_delete = FND_API.G_TRUE) then
848
849 G_Delayed_Requests.Delete(p_request_ind);
850 /*
851 BUG 2502849- do not delete from req entities table
852 , delete table in the end when all requests are processed
853 This is to improve performance as this search loops through
854 the entire pl/sql table which becomes very large with
855 greater number of lines being processed
856
857 -- Delete all the records in the requesting entities table
858 -- that have this request.
859
860 l_req_entity_ind := G_Requesting_Entities.First;
861
862
863 WHILE l_req_entity_ind IS NOT NULL LOOP
864 IF G_Requesting_Entities(l_req_entity_ind).request_index = p_request_ind
865 THEN
866 G_Requesting_Entities.Delete(l_req_entity_ind);
867 END IF;
868 l_req_entity_ind := G_Requesting_Entities.Next(l_req_entity_ind);
869 END LOOP;
870 */
871 ELSE
872
873 G_Delayed_Requests(p_request_ind).processed := 'Y';
874
875 END IF;
876
877
878 EXCEPTION
879
880 WHEN FND_API.G_EXC_ERROR THEN
881 G_Delayed_Requests(p_request_ind).processed := 'N';
882 x_return_status := FND_API.G_RET_STS_ERROR;
883
884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
885 G_Delayed_Requests(p_request_ind).processed := 'N';
886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
887
888 WHEN NO_DATA_FOUND THEN
889 oe_debug_pub.add('Exiting Process_Request_Pvt no_data_found exception ');
890 G_Delayed_Requests(p_request_ind).processed := 'N';
891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
892
893
894 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
895 THEN
896 OE_MSG_PUB.Add_Exc_Msg
897 ( G_PKG_NAME
898 , 'Process_Request_Pvt'
899 );
900 END IF;
901
902 WHEN OTHERS THEN
903 G_Delayed_Requests(p_request_ind).processed := 'N';
904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
905
906 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
907 THEN
908 OE_MSG_PUB.Add_Exc_Msg
909 ( G_PKG_NAME
910 , 'Process_Request_Pvt'
911 );
912 END IF;
913
914 oe_debug_pub.add('Exiting Process_Request_Pvt with others exception ');
915
916
917 End Process_Request_Pvt;
918 /** End Local Procedures **/
919
920 /** Global Procedures **/
921 Procedure Log_Request
922 ( p_entity_code IN VARCHAR2
923 , p_entity_id IN NUMBER
924 , p_requesting_entity_code IN VARCHAR2
925 , p_requesting_entity_id IN NUMBER
926 , p_request_type IN VARCHAR2
927 , p_request_unique_key1 IN VARCHAR2 := NULL
928 , p_request_unique_key2 IN VARCHAR2 := NULL
929 , p_request_unique_key3 IN VARCHAR2 := NULL
930 , p_request_unique_key4 IN VARCHAR2 := NULL
934 , p_param3 IN VARCHAR2 := NULL
931 , p_request_unique_key5 IN VARCHAR2 := NULL
932 , p_param1 IN VARCHAR2 := NULL
933 , p_param2 IN VARCHAR2 := NULL
935 , p_param4 IN VARCHAR2 := NULL
936 , p_param5 IN VARCHAR2 := NULL
937 , p_param6 IN VARCHAR2 := NULL
938 , p_param7 IN VARCHAR2 := NULL
939 , p_param8 IN VARCHAR2 := NULL
940 , p_param9 IN VARCHAR2 := NULL
941 , p_param10 IN VARCHAR2 := NULL
942 , p_param11 IN VARCHAR2 := NULL
943 , p_param12 IN VARCHAR2 := NULL
944 , p_param13 IN VARCHAR2 := NULL
945 , p_param14 IN VARCHAR2 := NULL
946 , p_param15 IN VARCHAR2 := NULL
947 , p_param16 IN VARCHAR2 := NULL
948 , p_param17 IN VARCHAR2 := NULL
949 , p_param18 IN VARCHAR2 := NULL
950 , p_param19 IN VARCHAR2 := NULL
951 , p_param20 IN VARCHAR2 := NULL
952 , p_param21 IN VARCHAR2 := NULL
953 , p_param22 IN VARCHAR2 := NULL
954 , p_param23 IN VARCHAR2 := NULL
955 , p_param24 IN VARCHAR2 := NULL
956 , p_param25 IN VARCHAR2 := NULL
957 , p_long_param1 IN VARCHAR2 := NULL
958 , x_return_status OUT NOCOPY VARCHAR2
959 )
960 IS
961 l_request_search_rslt VARCHAR2(1);
962 l_return_status VARCHAR2(1);
963 l_request_ind NUMBER;
964 l_req_entity_ind NUMBER;
965 l_request QP_QUALIFIER_RULES_PUB.REQUEST_REC_TYPE;
966 l_req_entity QP_QUALIFIER_RULES_PUB.Requesting_Entity_Rec_Type;
967 BEGIN
968
969 oe_debug_pub.add('Entering Procedure Log_Request in Package QP_Delayed_Requests_Pvt');
970 oe_debug_pub.add('log_request_type'||p_request_type);
971
972 -- Initialize the request_record
973 l_request.request_type := p_request_type;
974 l_request.entity_id := p_entity_id;
975 l_request.entity_code := p_entity_code;
976 l_request.request_unique_key1 := p_request_unique_key1;
977 l_request.request_unique_key2 := p_request_unique_key2;
978 l_request.request_unique_key3 := p_request_unique_key3;
979 l_request.request_unique_key4 := p_request_unique_key4;
980 l_request.request_unique_key5 := p_request_unique_key5;
981 l_request.param1 := p_param1;
982 l_request.param2 := p_param2;
983 l_request.param3 := p_param3;
984 l_request.param4 := p_param4;
985 l_request.param5 := p_param5;
986 l_request.param6 := p_param6;
987 l_request.param7 := p_param7;
988 l_request.param8 := p_param8;
989 l_request.param9 := p_param9;
990 l_request.param10 := p_param10;
991 l_request.param11 := p_param11;
992 l_request.param12 := p_param12;
993 l_request.param13 := p_param13;
994 l_request.param14 := p_param14;
995 l_request.param15 := p_param15;
996 l_request.param16 := p_param16;
997 l_request.param17 := p_param17;
998 l_request.param18 := p_param18;
999 l_request.param19 := p_param19;
1000 l_request.param20 := p_param20;
1001 l_request.param21 := p_param21;
1002 l_request.param22 := p_param22;
1003 l_request.param23 := p_param23;
1004 l_request.param24 := p_param24;
1005 l_request.param25 := p_param25;
1006 l_request.long_param1 := p_long_param1;
1007
1008 -- Initialize the return variable
1009 x_return_status := FND_API.G_RET_STS_SUCCESS;
1010 IF p_entity_id IS NOT NULL THEN --2650093
1011 Check_For_Request(p_entity_code,
1012 p_entity_id,
1013 p_request_type,
1014 p_request_unique_key1,
1015 p_request_unique_key2,
1016 p_request_unique_key3,
1017 p_request_unique_key4,
1018 p_request_unique_key5,
1019 l_request_ind,
1020 l_request_search_rslt,
1021 l_return_status);
1022
1023 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1025 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1026 RAISE FND_API.G_EXC_ERROR;
1027 END IF;
1028
1029 l_request.processed := 'N';
1030 IF l_request_search_rslt = FND_API.g_true -- replace the request
1031 THEN
1032 OE_Debug_PUB.ADD('Request replaced');
1033 g_delayed_requests(l_request_ind) := l_request;
1034
1035 ELSE -- insert the new request
1036 OE_Debug_PUB.ADD('New request inserted');
1037 -- l_request_ind := nvl(g_delayed_requests.LAST, 0) + 1; --2502849
1038 g_delayed_requests(l_request_ind) := l_request; --Added for 2502849
1039 END IF;
1040
1041 -- Initialize the requesting entity record
1042 l_req_entity.entity_code := p_requesting_entity_code;
1043 l_req_entity.entity_id := p_requesting_entity_id;
1044 l_req_entity.request_index := l_request_ind;
1045
1046 l_req_entity_ind := nvl(g_requesting_entities.LAST, 0) + 1;
1047 -- Insert into the requesting entities table
1048 g_requesting_entities(l_req_entity_ind) := l_req_entity;
1049 END IF;--2650093
1050 oe_debug_pub.add('end of log request');
1051
1052 EXCEPTION
1053
1054 WHEN FND_API.G_EXC_ERROR THEN
1055
1056 x_return_status := FND_API.G_RET_STS_ERROR;
1057
1058 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1059
1060 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1061
1062 WHEN OTHERS THEN
1063 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1064 THEN
1065 OE_MSG_PUB.Add_Exc_Msg
1066 (G_PKG_NAME
1067 ,'LOGREQUEST');
1068 END IF;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070
1071 End Log_Request;
1072
1073
1074
1075 Function Check_for_Request( p_entity_code IN VARCHAR2,
1076 p_entity_id IN NUMBER,
1077 p_request_type IN VARCHAR2,
1081 p_request_unique_key4 IN VARCHAR2 := NULL,
1078 p_request_unique_key1 IN VARCHAR2 := NULL,
1079 p_request_unique_key2 IN VARCHAR2 := NULL,
1080 p_request_unique_key3 IN VARCHAR2 := NULL,
1082 p_request_unique_key5 IN VARCHAR2 := NULL
1083 )
1084 RETURN BOOLEAN IS
1085 l_ind Number;
1086 x_result Varchar2(30);
1087 x_return_status Varchar2(30);
1088 Begin
1089 IF p_entity_id IS NOT NULL THEN --2650093
1090 Check_for_Request( p_entity_code => p_entity_code
1091 ,p_entity_id => p_entity_id
1092 ,p_request_type => p_request_type
1093 ,p_request_unique_key1 => p_request_unique_key1
1094 ,p_request_unique_key2 => p_request_unique_key2
1095 ,p_request_unique_key3 => p_request_unique_key3
1096 ,p_request_unique_key4 => p_request_unique_key4
1097 ,p_request_unique_key5 => p_request_unique_key5
1098 ,x_request_ind => l_ind
1099 ,x_result => x_result
1100 ,x_return_status => x_return_status);
1101
1102 if x_result = FND_API.G_TRUE then
1103 return(TRUE);
1104 else
1105 return(FALSE);
1106 end if;
1107
1108 /* Added for 2650093 */
1109 ELSE
1110 return(FALSE);
1111 END IF;
1112 End;
1113
1114 Procedure Delete_Request(p_entity_code IN VARCHAR2
1115 ,p_entity_id IN NUMBER
1116 ,p_request_Type IN VARCHAR2
1117 ,p_request_unique_key1 IN VARCHAR2 := NULL
1118 ,p_request_unique_key2 IN VARCHAR2 := NULL
1119 ,p_request_unique_key3 IN VARCHAR2 := NULL
1120 ,p_request_unique_key4 IN VARCHAR2 := NULL
1121 ,p_request_unique_key5 IN VARCHAR2 := NULL
1122 ,x_return_status OUT NOCOPY VARCHAR2)
1123 IS
1124 l_request_search_rslt Varchar2(1);
1125 l_return_status Varchar2(1);
1126 l_request_ind number;
1127 l_req_entity_ind number;
1128 BEGIN
1129
1130 oe_debug_pub.add('Entering Procedure Delete_Request in Package QP_Delayed_Requests_Pvt');
1131
1132 x_return_status := FND_API.G_RET_STS_SUCCESS;
1133 IF p_entity_id IS NOT NULL THEN --2650093
1134 Check_for_Request( p_entity_code => p_entity_code
1135 ,p_entity_id => p_entity_id
1136 ,p_request_type => p_request_type
1137 ,p_request_unique_key1 => p_request_unique_key1
1138 ,p_request_unique_key2 => p_request_unique_key2
1139 ,p_request_unique_key3 => p_request_unique_key3
1140 ,p_request_unique_key4 => p_request_unique_key4
1141 ,p_request_unique_key5 => p_request_unique_key5
1142 ,x_request_ind => l_request_ind
1143 ,x_result => l_request_search_rslt
1144 ,x_return_status => l_return_status);
1145
1146
1147 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1149 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1150 RAISE FND_API.G_EXC_ERROR;
1151 END IF;
1152
1153 if l_request_search_rslt = FND_API.G_TRUE -- delete the request
1154 then
1155 G_Delayed_Requests.Delete(l_request_ind);
1156
1157 /* BUG 2502849- do not delete from req entities table
1158 , delete table in the end when all requests are processed
1159 This is to improve performance as this search loops through
1160 the entire pl/sql table which becomes very large with
1161 greater number of lines being processed
1162
1163 -- Delete all the records in the requesting entities table
1164 -- that have this request.
1165 l_req_entity_ind := G_Requesting_Entities.First;
1166 WHILE l_req_entity_ind IS NOT NULL LOOP
1167 IF G_Requesting_Entities(l_req_entity_ind).request_index = l_request_ind
1168 THEN
1169 G_Requesting_Entities.Delete(l_req_entity_ind);
1170 END IF;
1171 l_req_entity_ind := G_Requesting_Entities.Next(l_req_entity_ind);
1172 END LOOP; */
1173 end if;
1174 END IF; --2650093
1175 EXCEPTION
1176
1177 WHEN FND_API.G_EXC_ERROR THEN
1178
1179 x_return_status := FND_API.G_RET_STS_ERROR;
1180
1181 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1182
1183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1184
1185 when others THEN
1186
1187 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1188 OE_MSG_PUB.Add_Exc_Msg
1189 (G_PKG_NAME
1190 ,'DeleteRequest');
1191 END IF;
1192
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194
1195 End Delete_Request;
1196
1197 Procedure Clear_Request( x_return_status OUT NOCOPY VARCHAR2)
1198 IS
1199 BEGIN
1200
1201 oe_debug_pub.add('Entering Procedure Clear_Request in Package QP_Delayed_Requests_Pvt');
1202
1203 x_return_status := FND_API.G_RET_STS_SUCCESS;
1204
1205 G_Delayed_Requests.DELETE;
1206 g_requesting_entities.DELETE;
1207
1208 EXCEPTION
1209
1210 WHEN OTHERS THEN
1211
1212 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1213 OE_MSG_PUB.Add_Exc_Msg
1214 (G_PKG_NAME
1215 ,'Clear_Request');
1216 END IF;
1217
1218 x_return_status := FND_API.G_RET_STS_ERROR;
1219
1220 End Clear_Request;
1221
1222 Procedure Process_Request( p_entity_code IN VARCHAR2
1223 ,p_entity_id IN Number
1224 ,p_request_Type IN VARCHAR2
1225 ,p_request_unique_key1 IN VARCHAR2 := NULL
1226 ,p_request_unique_key2 IN VARCHAR2 := NULL
1230 ,p_delete IN Varchar2 Default
1227 ,p_request_unique_key3 IN VARCHAR2 := NULL
1228 ,p_request_unique_key4 IN VARCHAR2 := NULL
1229 ,p_request_unique_key5 IN VARCHAR2 := NULL
1231 FND_API.G_TRUE
1232 ,x_return_status OUT NOCOPY Varchar2)
1233 IS
1234 l_request_ind Number;
1235 l_request_search_rslt Varchar2(30);
1236 l_return_status Varchar2(30);
1237 Begin
1238 x_return_status := FND_API.G_RET_STS_SUCCESS;
1239
1240 oe_debug_pub.add('Entering Procedure Process_Request in Package QP_Delayed_Requests_Pvt');
1241 IF p_entity_id IS NOT NULL THEN --2650093
1242 Check_for_Request( p_entity_code => p_entity_code
1243 ,p_entity_id => p_entity_id
1244 ,p_request_type => p_request_type
1245 ,p_request_unique_key1 => p_request_unique_key1
1246 ,p_request_unique_key2 => p_request_unique_key2
1247 ,p_request_unique_key3 => p_request_unique_key3
1248 ,p_request_unique_key4 => p_request_unique_key4
1249 ,p_request_unique_key5 => p_request_unique_key5
1250 ,x_request_ind => l_request_ind
1251 ,x_result => l_request_search_rslt
1252 ,x_return_status => l_return_status);
1253
1254 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1256 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1257 RAISE FND_API.G_EXC_ERROR;
1258 END IF;
1259
1260 if l_request_search_rslt = FND_API.G_TRUE then
1261 Process_Request_Pvt
1262 (p_request_ind => l_request_ind
1263 ,p_delete => p_delete
1264 ,x_return_status => l_return_status
1265 );
1266 end if;
1267
1268 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1270 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1271 RAISE FND_API.G_EXC_ERROR;
1272 END IF;
1273 END IF; --2650093
1274 EXCEPTION
1275
1276 WHEN FND_API.G_EXC_ERROR THEN
1277
1278 x_return_status := FND_API.G_RET_STS_ERROR;
1279
1280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1281
1282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1283
1284 WHEN OTHERS THEN
1285
1286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1287
1288 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1289 THEN
1290 OE_MSG_PUB.Add_Exc_Msg
1291 ( G_PKG_NAME
1292 , 'Process_Request'
1293 );
1294 END IF;
1295
1296 End Process_Request;
1297
1298
1299 Procedure Process_Request_for_Entity
1300 ( p_entity_code in Varchar2
1301 ,p_delete in Varchar2 Default FND_API.G_TRUE
1302 ,x_return_status OUT NOCOPY Varchar2) IS
1303 l_return_status Varchar2(30);
1304 l_ind Number;
1305 Begin
1306 x_return_status := FND_API.G_RET_STS_SUCCESS;
1307
1308 oe_debug_pub.add('Entering Procedure Process_Request_for_Entity in Package QP_Delayed_Requests_Pvt');
1309 --dbms_output.put_line('Entering Procedure Process_Request_for_Entity in Package QP_Delayed_Requests_Pvt');
1310 --dbms_output.put_line('entity id is ' ||p_entity_code);
1311
1312 l_ind := G_Delayed_Requests.first;
1313
1314 /*WHILE l_ind IS NOT NULL loop
1315 oe_debug_pub.add('entity is ' ||G_Delayed_Requests(l_ind).Entity_code);
1316 --dbms_output.put_line('entity is ' ||G_Delayed_Requests(l_ind).Entity_code);
1317 oe_debug_pub.add('entity id is ' ||G_Delayed_Requests(l_ind).Entity_id);
1318 --dbms_output.put_line('entity id is ' ||G_Delayed_Requests(l_ind).Entity_id);
1319 END LOOP;*/
1320
1321 l_ind := G_Delayed_Requests.first;
1322
1323 WHILE l_ind IS NOT NULL LOOP
1324 --dbms_output.put_line('entity id is ' ||G_Delayed_Requests(l_ind).Entity_id);
1325 IF G_Delayed_Requests(l_ind).Entity_code = p_entity_code THEN
1326 --dbms_output.put_line('found the match');
1327 --dbms_output.put_line('l_ind is '||l_ind);
1328 Process_Request_Pvt
1329 (p_request_ind => l_ind
1330 ,p_delete => p_delete
1331 ,x_return_status => l_return_status
1332 );
1333
1334 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1336 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1337 RAISE FND_API.G_EXC_ERROR;
1338 END IF;
1339 --EXIT;
1340 END IF;
1341 l_ind := G_Delayed_Requests.Next(l_ind);
1342 END LOOP;
1343 oe_debug_pub.add('Exiting Process_Request_for_Entity ');
1344 --dbms_output.put_line('Exiting Process_Request_for_Entity ');
1345
1346 EXCEPTION
1347
1348 WHEN FND_API.G_EXC_ERROR THEN
1349
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351
1352 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1353
1354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1355
1356 WHEN OTHERS THEN
1357
1358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1359
1360 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1361 THEN
1362 OE_MSG_PUB.Add_Exc_Msg
1363 ( G_PKG_NAME
1364 , 'Process_Request_for_Entity'
1365 );
1366 END IF;
1367
1368 oe_debug_pub.add('Exiting Process_Request_for_Entity ');
1369
1370 End Process_Request_for_Entity;
1371
1372 Procedure Process_Request_for_ReqType
1373 ( p_request_type in Varchar2
1377 l_return_status Varchar2(30);
1374 ,p_delete in Varchar2 Default FND_API.G_TRUE
1375 ,x_return_status OUT NOCOPY Varchar2
1376 ) IS
1378 l_ind Number;
1379 Begin
1380 x_return_status := FND_API.G_RET_STS_SUCCESS;
1381
1382 oe_debug_pub.add('Entering Procedure Process_Request_for_ReqType in Package QP_Delayed_Requests_Pvt');
1383
1384 l_ind := G_Delayed_Requests.first;
1385
1386 WHILE l_ind IS NOT NULL LOOP
1387 IF G_Delayed_Requests(l_ind).request_type = p_request_type THEN
1388 Process_Request_Pvt
1389 (p_request_ind => l_ind
1390 ,p_delete => p_delete
1391 ,x_return_status => l_return_status
1392 );
1393
1394 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1396 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1397 RAISE FND_API.G_EXC_ERROR;
1398 END IF;
1399 END IF;
1400 l_ind := G_Delayed_Requests.Next(l_ind);
1401 END LOOP;
1402
1403 EXCEPTION
1404
1405 WHEN FND_API.G_EXC_ERROR THEN
1406
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408
1409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1410
1411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1412
1413 WHEN OTHERS THEN
1414
1415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1416
1417 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1418 THEN
1419 OE_MSG_PUB.Add_Exc_Msg
1420 ( G_PKG_NAME
1421 , 'Process_Request_for_ReqType'
1422 );
1423 END IF;
1424
1425 End Process_Request_for_ReqType;
1426
1427
1428 Procedure Process_Delayed_Requests(
1429 x_return_status OUT NOCOPY Varchar2
1430 ) IS
1431 l_return_status Varchar2(30);
1432 Begin
1433
1434 oe_debug_pub.add('Entering Procedure Process_Delayed_Requests in Package QP_Delayed_Requests_Pvt');
1435
1436 -- Process requests as per the dependency
1437 -- This procedure processes all requests.
1438 -- For each request type defined in QP_GLOBALS
1439 -- write one code block as shown below.
1440
1441 -- start bug2091362
1442 Process_Request_for_ReqType
1443 (p_request_type =>QP_GLOBALS.G_DUPLICATE_MODIFIER_LINES
1444 ,p_delete => FND_API.G_TRUE
1445 ,x_return_status => l_return_status
1446 );
1447 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1449 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1450 RAISE FND_API.G_EXC_ERROR;
1451 END IF;
1452
1453 -- end bug2091362
1454
1455
1456 Process_Request_for_ReqType
1457 (p_request_type =>QP_GLOBALS.G_DUPLICATE_LIST_LINES
1458 ,p_delete => FND_API.G_TRUE
1459 ,x_return_status => l_return_status
1460 );
1461 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1463 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1464 RAISE FND_API.G_EXC_ERROR;
1465 END IF;
1466
1467
1468 /*included by spgopal for performance problem, to include phase_id and header_idinfo in qp_pricing_attributes table*/
1469
1470 Process_Request_for_ReqType
1471 (p_request_type =>QP_GLOBALS.G_UPDATE_PRICING_ATTR_PHASE
1472 ,p_delete => FND_API.G_TRUE
1473 ,x_return_status => l_return_status
1474 );
1475 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1478 RAISE FND_API.G_EXC_ERROR;
1479 END IF;
1480
1481
1482 /*included by spgopal for pricing phase seeded data over-write problem to update the denormalised columns in QP_PRICING_PHASES*/
1483
1484 Process_Request_for_ReqType
1485 (p_request_type =>QP_GLOBALS.G_UPDATE_PRICING_PHASE
1486 ,p_delete => FND_API.G_TRUE
1487 ,x_return_status => l_return_status
1488 );
1489 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1490 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1491 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1492 RAISE FND_API.G_EXC_ERROR;
1493 END IF;
1494
1495 /*fix for bug 1501138*/
1496 /*
1497 Process_Request_for_ReqType
1498 (p_request_type =>QP_GLOBALS.G_UPDATE_PRICING_PHASE
1499 ,p_delete => FND_API.G_TRUE
1500 ,x_return_status => l_return_status
1501 );
1502 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1504 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1505 RAISE FND_API.G_EXC_ERROR;
1506 END IF;
1507
1508 */
1509
1510 Process_Request_for_ReqType
1511 (p_request_type =>QP_GLOBALS.G_MULTIPLE_PRICE_BREAK_ATTRS
1512 ,p_delete => FND_API.G_TRUE
1513 ,x_return_status => l_return_status
1514 );
1515 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1517 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1518 RAISE FND_API.G_EXC_ERROR;
1519 END IF;
1520
1521
1522 Process_Request_for_ReqType
1523 (p_request_type => QP_GLOBALS.G_MIXED_QUAL_SEG_LEVELS
1524 ,p_delete => FND_API.G_TRUE
1525 ,x_return_status => l_return_status
1526 );
1530 RAISE FND_API.G_EXC_ERROR;
1527 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1529 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1531 END IF;
1532
1533
1534 Process_Request_for_ReqType
1535 (p_request_type =>QP_GLOBALS.G_OVERLAPPING_PRICE_BREAKS
1536 ,p_delete => FND_API.G_TRUE
1537 ,x_return_status => l_return_status
1538 );
1539 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1541 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1542 RAISE FND_API.G_EXC_ERROR;
1543 END IF;
1544
1545
1546 Process_Request_for_ReqType
1547 (p_request_type =>QP_GLOBALS.G_MAINTAIN_QUALIFIER_DEN_COLS
1548 ,p_delete => FND_API.G_TRUE
1549 ,x_return_status => l_return_status
1550 );
1551 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1553 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1554 RAISE FND_API.G_EXC_ERROR;
1555 END IF;
1556
1557 Process_Request_for_ReqType
1558 (p_request_type =>QP_GLOBALS.G_UPDATE_LIST_QUAL_IND
1559 ,p_delete => FND_API.G_TRUE
1560 ,x_return_status => l_return_status
1561 );
1562 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1563 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1564 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1565 RAISE FND_API.G_EXC_ERROR;
1566 END IF;
1567
1568 Process_Request_for_ReqType
1569 (p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND
1570 ,p_delete => FND_API.G_TRUE
1571 ,x_return_status => l_return_status
1572 );
1573 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1575 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1576 RAISE FND_API.G_EXC_ERROR;
1577 END IF;
1578
1579 Process_Request_for_ReqType
1580 (p_request_type =>QP_GLOBALS.G_UPDATE_LIMITS_COLUMNS
1581 ,p_delete => FND_API.G_TRUE
1582 ,x_return_status => l_return_status
1583 );
1584 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1586 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1587 RAISE FND_API.G_EXC_ERROR;
1588 END IF;
1589
1590 Process_Request_for_ReqType
1591 (p_request_type =>QP_GLOBALS.G_UPDATE_QUALIFIER_STATUS
1592 ,p_delete => FND_API.G_TRUE
1593 ,x_return_status => l_return_status
1594 );
1595 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1597 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1598 RAISE FND_API.G_EXC_ERROR;
1599 END IF;
1600
1601 Process_Request_for_ReqType
1602 (p_request_type =>QP_GLOBALS.G_CREATE_SECURITY_PRIVILEGE
1603 ,p_delete => FND_API.G_TRUE
1604 ,x_return_status => l_return_status
1605 );
1606 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1608 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1609 RAISE FND_API.G_EXC_ERROR;
1610 END IF;
1611
1612 Process_Request_for_ReqType
1613 (p_request_type =>QP_GLOBALS.G_UPDATE_ATTRIBUTE_STATUS
1614 ,p_delete => FND_API.G_TRUE
1615 ,x_return_status => l_return_status
1616 );
1617 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1619 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1620 RAISE FND_API.G_EXC_ERROR;
1621 END IF;
1622
1623 Process_Request_for_ReqType
1624 (p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND
1625 ,p_delete => FND_API.G_TRUE
1626 ,x_return_status => l_return_status
1627 );
1628 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1630 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1631 RAISE FND_API.G_EXC_ERROR;
1632 END IF;
1633
1634 Process_Request_for_ReqType
1635 (p_request_type =>QP_GLOBALS.G_MAINTAIN_LIST_HEADER_PHASES
1636 ,p_delete => FND_API.G_TRUE
1637 ,x_return_status => l_return_status
1638 );
1639 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1641 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1642 RAISE FND_API.G_EXC_ERROR;
1643 END IF;
1644
1645 Process_Request_for_ReqType
1646 (p_request_type =>QP_GLOBALS.G_MAINTAIN_FACTOR_LIST_ATTRS
1647 ,p_delete => FND_API.G_TRUE
1648 ,x_return_status => l_return_status
1649 );
1650 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1652 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1653 RAISE FND_API.G_EXC_ERROR;
1654 END IF;
1655
1656 -- mkarya for attribute manager
1657 Process_Request_for_ReqType
1658 (p_request_type =>QP_GLOBALS.G_CHECK_LINE_FOR_HEADER_QUAL
1659 ,p_delete => FND_API.G_TRUE
1660 ,x_return_status => l_return_status
1661 );
1662 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1667
1664 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1665 RAISE FND_API.G_EXC_ERROR;
1666 END IF;
1668 -- Hierarchical Categories (sfiresto)
1669 Process_Request_for_ReqType
1670 (p_request_type =>QP_GLOBALS.G_CHECK_ENABLED_FUNC_AREAS
1671 ,p_delete => FND_API.G_TRUE
1672 ,x_return_status => l_return_status
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
1681 --hw
1682
1683 -- delayed request for changed lines
1684 if QP_PERF_PVT.enabled = 'Y' then
1685 Process_Request_for_ReqType
1686 (p_request_type =>QP_GLOBALS.G_UPDATE_CHANGED_LINES_ADD
1687 ,p_delete => FND_API.G_TRUE
1688 ,x_return_status => l_return_status
1689 );
1690 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1692 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1693 RAISE FND_API.G_EXC_ERROR;
1694 END IF;
1695
1696 Process_Request_for_ReqType
1697 (p_request_type =>QP_GLOBALS.G_UPDATE_CHANGED_LINES_DEL
1698 ,p_delete => FND_API.G_TRUE
1699 ,x_return_status => l_return_status
1700 );
1701 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1703 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1704 RAISE FND_API.G_EXC_ERROR;
1705 END IF;
1706
1707 Process_Request_for_ReqType
1708 (p_request_type =>QP_GLOBALS.G_UPDATE_CHANGED_LINES_ACT
1709 ,p_delete => FND_API.G_TRUE
1710 ,x_return_status => l_return_status
1711 );
1712 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1714 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1715 RAISE FND_API.G_EXC_ERROR;
1716 END IF;
1717
1718 Process_Request_for_ReqType
1719 (p_request_type =>QP_GLOBALS.G_UPDATE_CHANGED_LINES_PH
1720 ,p_delete => FND_API.G_TRUE
1721 ,x_return_status => l_return_status
1722 );
1723 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1725 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1726 RAISE FND_API.G_EXC_ERROR;
1727 END IF;
1728 end if;
1729 oe_debug_pub.add('Ren: in process delayed request');
1730
1731 -- mkarya for pattern delayed requests
1732 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
1733 Process_Request_for_ReqType
1734 (p_request_type =>QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN
1735 ,p_delete => FND_API.G_TRUE
1736 ,x_return_status => l_return_status
1737 );
1738
1739 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1741 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1742 RAISE FND_API.G_EXC_ERROR;
1743 END IF;
1744
1745 Process_Request_for_ReqType
1746 (p_request_type =>QP_GLOBALS.G_MAINTAIN_LINE_PATTERN
1747 ,p_delete => FND_API.G_TRUE
1748 ,x_return_status => l_return_status
1749 );
1750
1751 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1753 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1754 RAISE FND_API.G_EXC_ERROR;
1755 END IF;
1756
1757 Process_Request_for_ReqType
1758 (p_request_type =>QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN
1759 ,p_delete => FND_API.G_TRUE
1760 ,x_return_status => l_return_status
1761 );
1762
1763 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1765 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1766 RAISE FND_API.G_EXC_ERROR;
1767 END IF;
1768
1769 END IF; --IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y'
1770
1771
1772 -- jagan's PL/SQL pattern
1773
1774 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'N' THEN
1775 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'M' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'P' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' THEN
1776
1777 Process_Request_for_ReqType
1778 (p_request_type =>QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN
1779 ,p_delete => FND_API.G_TRUE
1780 ,x_return_status => l_return_status
1781 );
1782
1783 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1785 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1786 RAISE FND_API.G_EXC_ERROR;
1787 END IF;
1788
1789 Process_Request_for_ReqType
1790 (p_request_type =>QP_GLOBALS.G_MAINTAIN_LINE_PATTERN
1791 ,p_delete => FND_API.G_TRUE
1792 ,x_return_status => l_return_status
1793 );
1794
1795 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1797 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1798 RAISE FND_API.G_EXC_ERROR;
1799 END IF;
1800
1801 Process_Request_for_ReqType
1802 (p_request_type =>QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN
1803 ,p_delete => FND_API.G_TRUE
1804 ,x_return_status => l_return_status
1805 );
1806
1810 RAISE FND_API.G_EXC_ERROR;
1807 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1809 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1811 END IF;
1812 END IF; --- QP pattern search set to 'Y'
1813 END IF; --IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'N'
1814 -- jagan's PL/SQL pattern
1815 -- clear the delayed request cache
1816 Clear_Request(x_return_status);
1817
1818 EXCEPTION
1819
1820 WHEN FND_API.G_EXC_ERROR THEN
1821
1822 x_return_status := FND_API.G_RET_STS_ERROR;
1823
1824 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1825
1826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1827
1828 WHEN OTHERS THEN
1829
1830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1831
1832 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1833 THEN
1834 OE_MSG_PUB.Add_Exc_Msg
1835 ( G_PKG_NAME
1836 , 'Process_Delayed_Requests'
1837 );
1838 END IF;
1839
1840 End Process_Delayed_Requests;
1841
1842
1843 Procedure Delete_Reqs_for_Deleted_Entity ( p_entity_code IN Varchar2
1844 , p_entity_id in Number
1845 , x_return_status OUT NOCOPY Varchar2)
1846 IS
1847 i number;
1848 j number;
1849 req_ind number;
1850 request_delete BOOLEAN;
1851 BEGIN
1852
1853
1854
1855 x_return_status := FND_API.G_RET_STS_SUCCESS;
1856
1857
1858 -- DELETING REQUESTS LOGGED AGAINST THIS ENTITY
1859
1860 i := G_Delayed_Requests.first;
1861
1862 WHILE i IS NOT NULL LOOP
1863
1864 IF (G_Delayed_Requests(i).entity_code = p_entity_code
1865 AND G_Delayed_Requests(i).entity_id = p_entity_id) THEN
1866
1867 -- delete records in requesting entity tables with this request
1868 j := G_Requesting_Entities.first;
1869 WHILE j IS NOT NULL LOOP
1870 IF G_Requesting_Entities(j).request_index = i THEN
1871 G_Requesting_Entities.Delete(j);
1872 END IF;
1873 j := G_Requesting_Entities.Next(j);
1874 END LOOP;
1875
1876 -- delete the delayed request
1877 G_Delayed_Requests.Delete(i);
1878
1879 END IF;
1880
1881 i := G_Delayed_Requests.Next(i);
1882
1883 END LOOP;
1884
1885
1886 -- DELETING REQUESTS LOGGED BY THIS ENTITY
1887
1888 i := G_Requesting_Entities.first;
1889
1890 WHILE i IS NOT NULL LOOP
1891
1892 -- search for requests logged by this entity
1893
1894 IF (G_Requesting_Entities(i).entity_code = p_entity_code
1895 AND G_Requesting_Entities(i).entity_id = p_entity_id) THEN
1896
1897 req_ind := G_Requesting_Entities(i).request_index;
1898
1899 -- initialize request delete to TRUE
1900 request_delete := TRUE;
1901
1902 -- set the delete to FALSE if there are other entities that
1903 -- logged the same request but if the same entity has logged this
1904 -- request, then delete in the requesting entities table
1905
1906 j := G_Requesting_Entities.first;
1907 WHILE j IS NOT NULL LOOP
1908 IF G_Requesting_Entities(j).request_index = req_ind THEN
1909 IF (G_Requesting_Entities(j).entity_code = p_entity_code
1910 AND G_Requesting_Entities(j).entity_id = p_entity_id) THEN
1911 G_Requesting_Entities.Delete(j);
1912 ELSE
1913 request_delete := FALSE;
1914 END IF;
1915 END IF;
1916 j := G_Requesting_Entities.Next(j);
1917 END LOOP;
1918
1919 -- deleting the delayed request
1920 IF request_delete
1921 AND G_Delayed_Requests.Exists(req_ind) THEN
1922 G_Delayed_Requests.Delete(req_ind);
1923 END IF;
1924
1925 END IF;
1926
1927 i := G_Requesting_Entities.Next(i);
1928
1929 END LOOP;
1930
1931 EXCEPTION
1932
1933 WHEN FND_API.G_EXC_ERROR THEN
1934
1935 x_return_status := FND_API.G_RET_STS_ERROR;
1936
1937 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1938
1939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1940
1941 when others THEN
1942
1943 IF OE_MSG_PUB.Check_MSg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1944 OE_MSG_PUB.Add_Exc_Msg
1945 (G_PKG_NAME
1946 ,'Delete_Reqs_for_Deleted_Entity');
1947 END IF;
1948
1949 x_return_status := FND_API.G_RET_STS_ERROR;
1950
1951 End Delete_Reqs_for_Deleted_Entity;
1952
1953
1954
1955
1956 END QP_Delayed_Requests_Pvt;