[Home] [Help]
PACKAGE BODY: APPS.QP_PRICE_BOOK_UTIL
Source
1 PACKAGE BODY QP_PRICE_BOOK_UTIL AS
2 /*$Header: QPXUPBKB.pls 120.106.12010000.2 2008/10/15 14:08:52 dnema ship $*/
3
4 --Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_PRICE_BOOK_UTIL';
6
7 /*****************************************************************************
8 Procedure to insert Price Book related error and warning messages into
9 qp_price_book_messages table.
10 *****************************************************************************/
11 PROCEDURE Insert_Price_Book_Messages (
12 p_price_book_messages_tbl IN price_book_messages_tbl)
13 IS
14
15 i NUMBER;
16 l_user_id NUMBER;
17 l_login_id NUMBER;
18
19 l_message_type_tbl FLAG_TYPE;
20 l_message_code_tbl VARCHAR30_TYPE;
21 l_message_text_tbl VARCHAR2000_TYPE;
22 l_pb_input_header_id_tbl NUMBER_TYPE;
23 l_price_book_header_id_tbl NUMBER_TYPE;
24 l_price_book_line_id_tbl NUMBER_TYPE;
25
26 BEGIN
27
28 l_user_id := fnd_global.user_id;
29 l_login_id := fnd_global.conc_login_id;
30
31 --The following assignments in a loop is required since the database(including
32 --10g) does not support the use of a record's attribute in a DML with FORALL.
33 IF p_price_book_messages_tbl.COUNT > 0 THEN
34 FOR i IN p_price_book_messages_tbl.FIRST..p_price_book_messages_tbl.LAST
35 LOOP
36 l_message_type_tbl(i) := p_price_book_messages_tbl(i).message_type;
37 l_message_code_tbl(i) := p_price_book_messages_tbl(i).message_code;
38 l_message_text_tbl(i) := p_price_book_messages_tbl(i).message_text;
39 l_pb_input_header_id_tbl(i) :=
40 p_price_book_messages_tbl(i).pb_input_header_id;
41 l_price_book_header_id_tbl(i) :=
42 p_price_book_messages_tbl(i).price_book_header_id;
43 l_price_book_line_id_tbl(i) :=
44 p_price_book_messages_tbl(i).price_book_line_id;
45 END LOOP;
46 END IF; --If p_price_book_messages_tbl.count > 0
47
48 FORALL i IN p_price_book_messages_tbl.FIRST..p_price_book_messages_tbl.LAST
49 INSERT INTO qp_price_book_messages
50 (message_id,
51 message_type,
52 message_code,
53 message_text,
54 pb_input_header_id,
55 price_book_header_id,
56 price_book_line_id,
57 creation_date,
58 created_by,
59 last_update_date,
60 last_updated_by,
61 last_update_login
62 )
63 VALUES
64 (qp_price_book_messages_s.nextval,
65 l_message_type_tbl(i),
66 l_message_code_tbl(i),
67 l_message_text_tbl(i),
68 l_pb_input_header_id_tbl(i),
69 l_price_book_header_id_tbl(i),
70 l_price_book_line_id_tbl(i),
71 sysdate,
72 l_user_id,
73 sysdate,
74 l_user_id,
75 l_login_id);
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 RAISE;
80
81 END Insert_Price_Book_Messages;
82
83 FUNCTION GET_PRICE_LIST_ID
84 (
85 p_price_list_name IN VARCHAR2
86 )
87 RETURN NUMBER IS
88 x_return NUMBER;
89 BEGIN
90 SELECT list_header_id
91 INTO x_return
92 FROM qp_list_headers_vl
93 WHERE name = p_price_list_name
94 AND list_type_code = 'PRL'
95 AND rownum = 1;
96 RETURN x_return;
97 EXCEPTION
98 WHEN OTHERS THEN
99 RETURN null;
100 END GET_PRICE_LIST_ID;
101
102 FUNCTION GET_AGREEMENT_ID
103 (
104 p_agreement_name IN VARCHAR2,
105 p_pricing_effective_date IN DATE
106 )
107 RETURN NUMBER IS
108 x_return NUMBER;
109 BEGIN
110 SELECT agreement_id
111 INTO x_return
112 FROM oe_agreements_vl a
113 WHERE name = p_agreement_name
114 AND trunc(nvl(p_pricing_effective_date, sysdate))
115 between trunc(nvl(a.start_date_active,
116 p_pricing_effective_date))
117 and trunc(nvl(a.end_date_active,
118 p_pricing_effective_date))
119 AND rownum = 1;
120 RETURN x_return;
121 EXCEPTION
122 WHEN OTHERS THEN
123 RETURN null;
124 END GET_AGREEMENT_ID;
125
126 FUNCTION GET_BSA_ID
127 (
128 p_bsa_name IN VARCHAR2
129 )
130 RETURN NUMBER IS
131 x_return NUMBER;
132 BEGIN
133 SELECT header_id
134 INTO x_return
135 FROM oe_blanket_headers_all
136 WHERE order_number = p_bsa_name;
137 RETURN x_return;
138 EXCEPTION
139 WHEN OTHERS THEN
140 RETURN null;
141 END GET_BSA_ID;
142
143 PROCEDURE DEFAULT_CUST_ACCOUNT_ID
144 (
145 p_customer_attr_value IN VARCHAR2,
146 x_cust_account_id OUT NOCOPY VARCHAR2
147 )
148 IS
149 BEGIN
150 SELECT cust_account_id
151 INTO x_cust_account_id
152 FROM hz_cust_accounts
153 WHERE party_id = p_customer_attr_value;
154 EXCEPTION
155 WHEN OTHERS THEN
156 x_cust_account_id := null;
157 END DEFAULT_CUST_ACCOUNT_ID;
158
159
160 /*****************************************************************************
161 Procedure to convert values to ids for Price Book Request's Input Criteria.
162 *****************************************************************************/
163 PROCEDURE Convert_PB_Input_Value_to_Id (
164 p_pb_input_header_rec IN OUT NOCOPY QP_PRICE_BOOK_PUB.pb_input_header_rec)
165 IS
166 BEGIN
167
168 IF p_pb_input_header_rec.limit_products_by = 'PRICE_LIST' THEN
169 IF p_pb_input_header_rec.pl_agr_bsa_id IS NULL AND
170 p_pb_input_header_rec.pl_agr_bsa_name IS NOT NULL
171 THEN
172 p_pb_input_header_rec.pl_agr_bsa_id :=
173 GET_PRICE_LIST_ID(p_pb_input_header_rec.pl_agr_bsa_name);
174 END IF;
175 END IF;
176
177 IF p_pb_input_header_rec.price_based_on = 'PRICE_LIST' THEN
178 IF p_pb_input_header_rec.pl_agr_bsa_id IS NULL AND
179 p_pb_input_header_rec.pl_agr_bsa_name IS NOT NULL
180 THEN
181 p_pb_input_header_rec.pl_agr_bsa_id :=
182 GET_PRICE_LIST_ID( p_pb_input_header_rec.pl_agr_bsa_name);
183 END IF;
184
185 ELSIF p_pb_input_header_rec.price_based_on = 'AGREEMENT' THEN
186 IF p_pb_input_header_rec.pl_agr_bsa_id IS NULL AND
187 p_pb_input_header_rec.pl_agr_bsa_name IS NOT NULL
188 THEN
189 p_pb_input_header_rec.pl_agr_bsa_id :=
190 GET_AGREEMENT_ID(p_pb_input_header_rec.pl_agr_bsa_name,
191 p_pb_input_header_rec.effective_date);
192 END IF;
193
194 ELSIF p_pb_input_header_rec.price_based_on = 'BSA' THEN
195 IF p_pb_input_header_rec.pl_agr_bsa_id IS NULL AND
196 p_pb_input_header_rec.pl_agr_bsa_name IS NOT NULL
197 THEN
198 p_pb_input_header_rec.pl_agr_bsa_id :=
199 GET_BSA_ID(p_pb_input_header_rec.pl_agr_bsa_name);
200 END IF;
201
202 END IF;
203
204 IF p_pb_input_header_rec.pub_template_code IS NULL AND
205 p_pb_input_header_rec.pub_template_name IS NOT NULL
206 THEN
207 BEGIN
208 SELECT template_code
209 INTO p_pb_input_header_rec.pub_template_code
210 FROM xdo_templates_vl
211 WHERE template_name = p_pb_input_header_rec.pub_template_name
212 AND application_short_name = 'QP'
213 AND rownum = 1;
214 EXCEPTION
215 WHEN OTHERS THEN
216 p_pb_input_header_rec.pub_template_code := NULL;
217 END;
218 END IF;
219
220 END Convert_PB_Input_Value_to_Id;
221
222
223 /*****************************************************************************
224 Procedure to default values for Price Book Request's Input Criteria.
225 *****************************************************************************/
226 PROCEDURE Default_PB_Input_Criteria (
227 p_pb_input_header_rec IN OUT NOCOPY QP_PRICE_BOOK_PUB.pb_input_header_rec)
228 IS
229 l_application_id NUMBER;
230 l_user_id NUMBER;
231 l_customer_id NUMBER;
232 l_resp_appl_id NUMBER;
233 l_sold_to_org_id NUMBER;
234
235 BEGIN
236
237 l_user_id := fnd_global.user_id;
238 l_resp_appl_id := fnd_global.resp_appl_id;
239
240 IF p_pb_input_header_rec.pricing_perspective_code IS NULL THEN
241 BEGIN
242 SELECT customer_id
243 INTO l_customer_id
244 FROM fnd_user
245 WHERE user_id = l_user_id;
246 EXCEPTION
247 WHEN OTHERS THEN
248 l_customer_id := NULL;
249 END;
250
251 IF l_customer_id IS NOT NULL --external customer
252 THEN
253 p_pb_input_header_rec.pricing_perspective_code :=
254 fnd_profile.value('QP_EXT_DEFAULT_PRICING_PERSPECTIVE');
255 ELSE --internal customer
256 p_pb_input_header_rec.pricing_perspective_code :=
257 fnd_profile.value('QP_INT_DEFAULT_PRICING_PERSPECTIVE');
258 END IF;
259 END IF;
260
261 IF p_pb_input_header_rec.customer_context IS NULL THEN
262 p_pb_input_header_rec.customer_context := 'CUSTOMER';
263 END IF;
264
265 --default customer_attribute to 'Customer Name'
266 IF p_pb_input_header_rec.customer_attribute IS NULL THEN
267 p_pb_input_header_rec.customer_attribute := 'QUALIFIER_ATTRIBUTE2';
268 END IF;
269
270 IF p_pb_input_header_rec.customer_attr_value IS NULL THEN
271 IF p_pb_input_header_rec.pricing_perspective_code = 'PO' THEN
272 p_pb_input_header_rec.customer_attr_value := -1;
273 ELSE
274 BEGIN
275 SELECT customer_id
276 INTO l_customer_id
277 FROM fnd_user
278 WHERE user_id = l_user_id;
279 EXCEPTION
280 WHEN OTHERS THEN
281 l_customer_id := NULL;
282 END;
283
284 IF l_customer_id IS NOT NULL THEN
285 --If external customer then the price book can be generated only for that
286 --customer
287 p_pb_input_header_rec.customer_attr_value := l_customer_id;
288 END IF;
289 END IF; --pricing perspective is 'PO'
290 END IF; --customer_attr_value is null
291
292
293 IF p_pb_input_header_rec.price_book_type_code IS NULL THEN
294 p_pb_input_header_rec.price_book_type_code := 'F';
295 END IF;
296
297 --Default the cust_account_id using the party_id(customer_id) if there is
298 --only one cust_account_id for the party.
299 IF p_pb_input_header_rec.cust_account_id IS NULL AND
300 p_pb_input_header_rec.customer_attr_value IS NOT NULL
301 THEN
302 DEFAULT_CUST_ACCOUNT_ID(p_pb_input_header_rec.customer_attr_value,
303 p_pb_input_header_rec.cust_account_id);
304 END IF;
305
306 --Default party_id(customer_id) using cust_account_id if specified
307 IF p_pb_input_header_rec.customer_attr_value IS NULL AND
308 p_pb_input_header_rec.cust_account_id IS NOT NULL
309 THEN
310 BEGIN
311 SELECT party_id
312 INTO p_pb_input_header_rec.customer_attr_value
313 FROM hz_cust_accounts
314 WHERE cust_account_id = p_pb_input_header_rec.cust_account_id;
315 EXCEPTION
316 WHEN OTHERS THEN
317 p_pb_input_header_rec.customer_attr_value := NULL;
318 END;
319 END IF;
320
321 --Get application id for the appl corresponding to pricing perspective
322 BEGIN
323 SELECT application_id
324 INTO l_application_id
325 FROM fnd_application
326 WHERE application_short_name =
327 p_pb_input_header_rec.pricing_perspective_code;
328 EXCEPTION
329 WHEN OTHERS THEN
330 l_application_id := NULL;
331 END;
332
333 --Set the apps context for the pricing perspective application so that
334 --profile options at the application level are retrieved for this
335 --application and not QP.
336 fnd_global.apps_initialize(l_user_id,
337 fnd_global.resp_id,
338 l_application_id);
339
340 --No defaulting reqd for columns that do not impact Delta price book creation
341 IF nvl(p_pb_input_header_rec.price_book_type_code, 'F') <> 'D' THEN
342
343 IF p_pb_input_header_rec.limit_products_by IN ('ITEM', 'ITEM_CATEGORY',
344 'ALL_ITEMS') AND
345 p_pb_input_header_rec.product_context IS NULL
346 THEN
347 p_pb_input_header_rec.product_context := 'ITEM';
348 END IF;
349
350 IF p_pb_input_header_rec.limit_products_by = 'ITEM' AND
351 p_pb_input_header_rec.product_attribute IS NULL
352 THEN
353 p_pb_input_header_rec.product_attribute := 'PRICING_ATTRIBUTE1';
354 END IF;
355
356 IF p_pb_input_header_rec.limit_products_by = 'ITEM_CATEGORY' AND
357 p_pb_input_header_rec.product_attribute IS NULL
358 THEN
359 p_pb_input_header_rec.product_attribute := 'PRICING_ATTRIBUTE2';
360 END IF;
361
362 IF p_pb_input_header_rec.limit_products_by = 'ALL_ITEMS' AND
363 p_pb_input_header_rec.product_attribute IS NULL
364 THEN
365 p_pb_input_header_rec.product_attribute := 'PRICING_ATTRIBUTE3';
366 p_pb_input_header_rec.product_attr_value := 'ALL';
367 END IF;
368
369 IF p_pb_input_header_rec.limit_products_by = 'PRICE_LIST' AND
370 p_pb_input_header_rec.price_based_on IS NULL
371 THEN
372 p_pb_input_header_rec.price_based_on := 'PRICE_LIST';
373 END IF;
374
375
376 IF p_pb_input_header_rec.currency_code IS NULL THEN
377 IF p_pb_input_header_rec.limit_products_by = 'PRICE_LIST' AND
378 p_pb_input_header_rec.pl_agr_bsa_id IS NOT NULL
379 THEN
380
381 --If multi-currency is not installed, default to currency of pricelist
382 IF NOT(UPPER(fnd_profile.value('QP_MULTI_CURRENCY_INSTALLED')) IN
383 ('Y','YES'))
384 AND nvl(fnd_profile.value('QP_MULTI_CURRENCY_USAGE'), 'N') <> 'Y'
385 THEN
386 BEGIN
387 SELECT currency_code
388 INTO p_pb_input_header_rec.currency_code
389 FROM qp_list_headers_b
390 WHERE list_header_id = p_pb_input_header_rec.pl_agr_bsa_id;
391 EXCEPTION
392 WHEN OTHERS THEN
393 p_pb_input_header_rec.currency_code := NULL;
394 END;
395 END IF; --If multi-currency is not installed
396
397 END IF; --if pl_agr_bsa_id is not null
398 END IF;--if currency_code is null
399
400 IF p_pb_input_header_rec.item_quantity IS NULL THEN
401 p_pb_input_header_rec.item_quantity := 1;
402 END IF;
403
404 END IF; --Defaulting only if not Delta Price Book
405
406 IF p_pb_input_header_rec.effective_date IS NULL THEN
407 p_pb_input_header_rec.effective_date := sysdate;
408 END IF;
409
410 IF p_pb_input_header_rec.dlv_xml_flag IS NULL THEN
411 p_pb_input_header_rec.dlv_xml_flag := 'N';
412 END IF;
413
414 IF p_pb_input_header_rec.dlv_email_flag IS NULL THEN
415 p_pb_input_header_rec.dlv_email_flag := 'N';
416 END IF;
417
418 IF p_pb_input_header_rec.dlv_printer_flag IS NULL THEN
419 p_pb_input_header_rec.dlv_printer_flag := 'N';
420 END IF;
421
422 IF p_pb_input_header_rec.generation_time_code IS NULL THEN
423 p_pb_input_header_rec.generation_time_code := 'IMMEDIATE';
424 END IF;
425
426 IF p_pb_input_header_rec.org_id IS NULL THEN
427 p_pb_input_header_rec.org_id := MO_UTILS.get_default_org_id;
428 END IF;
429
430 IF p_pb_input_header_rec.request_type_code IS NULL THEN
431 p_pb_input_header_rec.request_type_code :=
432 fnd_profile.value('QP_PRICING_PERSPECTIVE_REQUEST_TYPE');
433 END IF;
434
435 IF p_pb_input_header_rec.publish_existing_pb_flag IS NULL THEN
436 p_pb_input_header_rec.publish_existing_pb_flag := 'N';
437 END IF;
438
439 IF p_pb_input_header_rec.overwrite_existing_pb_flag IS NULL THEN
440 p_pb_input_header_rec.overwrite_existing_pb_flag := 'N';
441 END IF;
442
443 IF p_pb_input_header_rec.request_origination_code IS NULL THEN
444 p_pb_input_header_rec.request_origination_code := 'API';
445 END IF;
446
447 IF p_pb_input_header_rec.pub_template_code IS NOT NULL AND
448 p_pb_input_header_rec.pub_output_document_type IS NULL AND
449 p_pb_input_header_rec.dlv_printer_flag = 'Y' AND
450 nvl(p_pb_input_header_rec.dlv_email_flag, 'N') <> 'Y'
451 THEN
452 p_pb_input_header_rec.pub_output_document_type := 'PDF';
453 END IF;
454
455 --reset the application_id back to the original appl id
456 fnd_global.apps_initialize(l_user_id,
457 fnd_global.resp_id,
458 l_resp_appl_id);
459
460 END Default_PB_Input_Criteria;
461
462
463 /*****************************************************************************
464 Procedure to Validate Price Book Input Validation messages into
465 qp_price_book_messages table and also return all messages concatenated in
466 x_return_status for use with get_catalog and UI.
467 *****************************************************************************/
468 PROCEDURE Validate_PB_Input_Criteria (
469 p_pb_input_header_rec IN qp_pb_input_headers_vl%ROWTYPE,
470 p_pb_input_lines_tbl IN pb_input_lines_tbl,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_return_text IN OUT NOCOPY VARCHAR2)
473 IS
474
475 TYPE currency_code_tbl IS TABLE OF fnd_currencies_vl.currency_code%TYPE
476 INDEX BY BINARY_INTEGER;
477
478 l_customer_id NUMBER;
479 l_valid_pl VARCHAR2(1);
480 l_pte_code VARCHAR2(30);
481 l_request_type_code VARCHAR2(30);
482 l_count NUMBER;
483 l_valid_currency VARCHAR2 (1);
484 l_currency_code_tbl currency_code_tbl;
485 l_price_book_messages_tbl price_book_messages_tbl;
486 l_message_text VARCHAR2(2000);
487 i NUMBER := 1;
488 l_application_id NUMBER;
489 l_user_id NUMBER;
490 l_pricing_status VARCHAR2(1);
491 l_resp_appl_id NUMBER;
492 l_party_id_match VARCHAR2(1);
493 l_category_valid BOOLEAN;
494 l_name VARCHAR2(2000);
495 l_desc VARCHAR2(2000);
496 l_count2 NUMBER := 0;
497
498 v_valueset_r fnd_vset.valueset_r;
499 v_valueset_dr fnd_vset.valueset_dr;
500 l_datatype VARCHAR2(1);
501 l_value VARCHAR2(150);
502 l_id VARCHAR2(150);
503 l_valueset_id NUMBER;
504
505 BEGIN
506
507 l_user_id := fnd_global.user_id;
508 l_resp_appl_id := fnd_global.resp_appl_id;
509
510 --If generating price book
511 IF nvl(p_pb_input_header_rec.publish_existing_pb_flag, 'N') <> 'Y' THEN
512 --Check if org_id is not null and valid. Else return, do not proceed.
513 IF p_pb_input_header_rec.org_id IS NULL THEN
514 x_return_status := 'E';
515 FND_MESSAGE.SET_NAME('FND', 'MO_ORG_REQUIRED');
516 l_message_text := FND_MESSAGE.GET;
517 l_price_book_messages_tbl(i).message_code := 'MO_ORG_REQUIRED';
518 l_price_book_messages_tbl(i).message_text := l_message_text;
519 l_price_book_messages_tbl(i).pb_input_header_id :=
520 p_pb_input_header_rec.pb_input_header_id;
521 i := i + 1;
522 x_return_text := x_return_text || substr(l_message_text, 1, 240);
523 Insert_Price_Book_Messages (l_price_book_messages_tbl);
524 l_price_book_messages_tbl.delete;
525 RETURN;
526 ELSE
527 IF MO_GLOBAL.check_access(p_pb_input_header_rec.org_id) <> 'Y' THEN
528 x_return_status := 'E';
529 FND_MESSAGE.SET_NAME('FND', 'MO_ORG_INVALID');
530 l_message_text := FND_MESSAGE.GET;
531 l_price_book_messages_tbl(i).message_code := 'MO_ORG_INVALID';
532 l_price_book_messages_tbl(i).message_text := l_message_text;
533 l_price_book_messages_tbl(i).pb_input_header_id :=
534 p_pb_input_header_rec.pb_input_header_id;
535 i := i + 1;
536 x_return_text := x_return_text || substr(l_message_text, 1, 240);
537 Insert_Price_Book_Messages (l_price_book_messages_tbl);
538 l_price_book_messages_tbl.delete;
539 RETURN;
540 END IF; --If check_access returns 'N'
541 END IF; --If org id is null
542 END IF;--If generating new pb
543
544
545 IF p_pb_input_header_rec.pricing_perspective_code IS NULL THEN
546 x_return_status := 'E';
547 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
548 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRICING_PERSPECTIVE_CODE');
549 l_message_text := FND_MESSAGE.GET;
550 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
551 l_price_book_messages_tbl(i).message_text := l_message_text;
552 l_price_book_messages_tbl(i).pb_input_header_id :=
553 p_pb_input_header_rec.pb_input_header_id;
554 i := i + 1;
555 x_return_text := x_return_text || substr(l_message_text, 1, 240);
556 Insert_Price_Book_Messages (l_price_book_messages_tbl);
557 l_price_book_messages_tbl.delete;
558 RETURN;
559 ELSE
560 --Get application id for the appl corresponding to pricing perspective
561 BEGIN
562 SELECT application_id
563 INTO l_application_id
564 FROM fnd_application
565 WHERE application_short_name =
566 p_pb_input_header_rec.pricing_perspective_code;
567 EXCEPTION
568 WHEN OTHERS THEN
569 x_return_status := 'E';
570 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_PRICING_PERSPECTIVE');
571 FND_MESSAGE.SET_TOKEN('CODE',
572 p_pb_input_header_rec.pricing_perspective_code);
573 l_message_text := FND_MESSAGE.GET;
574 l_price_book_messages_tbl(i).message_code
575 := 'QP_INVALID_PRICING_PERSPECTIVE';
576 l_price_book_messages_tbl(i).message_text := l_message_text;
577 l_price_book_messages_tbl(i).pb_input_header_id :=
578 p_pb_input_header_rec.pb_input_header_id;
579 i := i + 1;
580 l_application_id := NULL;
581 x_return_text := x_return_text || substr(l_message_text, 1, 240);
582 Insert_Price_Book_Messages (l_price_book_messages_tbl);
583 l_price_book_messages_tbl.delete;
584 RETURN;
585 END;
586
587 IF p_pb_input_header_rec.pricing_perspective_code = 'QP' THEN
588 x_return_status := 'E';
589 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_PRICING_PERSPECTIVE');
590 FND_MESSAGE.SET_TOKEN('CODE',
591 p_pb_input_header_rec.pricing_perspective_code);
592 l_message_text := FND_MESSAGE.GET;
593 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_PRICING_PERSPECTIVE';
594 l_price_book_messages_tbl(i).message_text := l_message_text;
595 l_price_book_messages_tbl(i).pb_input_header_id :=
596 p_pb_input_header_rec.pb_input_header_id;
597 i := i + 1;
598 x_return_text := x_return_text || substr(l_message_text, 1, 240);
599 Insert_Price_Book_Messages (l_price_book_messages_tbl);
600 l_price_book_messages_tbl.delete;
601 RETURN;
602 END IF;
603
604 --Set the application id corresponding to the pricing perspective so that
605 --profile options at the application level are retrieved for this
606 --application and not QP.
607 fnd_global.apps_initialize(l_user_id,
608 fnd_global.resp_id,
609 l_application_id);
610
611 l_request_type_code :=
612 fnd_profile.value('QP_PRICING_PERSPECTIVE_REQUEST_TYPE');
613
614 IF l_request_type_code IS NULL THEN
615 x_return_status := 'E';
616 FND_MESSAGE.SET_NAME('QP', 'QP_REQUEST_TYPE_NOT_FOUND');
617 FND_MESSAGE.SET_TOKEN('APPLICATION',
618 p_pb_input_header_rec.pricing_perspective_code);
619 l_message_text := FND_MESSAGE.GET;
620 l_price_book_messages_tbl(i).message_code := 'QP_REQUEST_TYPE_NOT_FOUND';
621 l_price_book_messages_tbl(i).message_text := l_message_text;
622 l_price_book_messages_tbl(i).pb_input_header_id :=
623 p_pb_input_header_rec.pb_input_header_id;
624 i := i + 1;
625 l_application_id := NULL;
626 x_return_text := x_return_text || substr(l_message_text, 1, 240);
627 Insert_Price_Book_Messages (l_price_book_messages_tbl);
628 l_price_book_messages_tbl.delete;
629 RETURN;
630 END IF;
631
632 --Update the input_header table with the request_type_code
633 UPDATE qp_pb_input_headers_b
634 SET request_type_code = l_request_type_code
635 WHERE pb_input_header_id = p_pb_input_header_rec.pb_input_header_id;
636
637 BEGIN
638 SELECT pte_code
639 INTO l_pte_code
640 FROM qp_pte_request_types_v
641 WHERE request_type_code = l_request_type_code;
642 EXCEPTION
643 WHEN OTHERS THEN
644 l_pte_code := NULL;
645 END;
646 END IF; --pricing_perspective_code is null
647
648 --Check if Customer Context is valid
649 IF p_pb_input_header_rec.customer_context IS NULL THEN
650 x_return_status := 'E';
651 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
652 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'CUSTOMER_CONTEXT');
653 l_message_text := FND_MESSAGE.GET;
654 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
655 l_price_book_messages_tbl(i).message_text := l_message_text;
656 l_price_book_messages_tbl(i).pb_input_header_id :=
657 p_pb_input_header_rec.pb_input_header_id;
658 i := i + 1;
659 x_return_text := x_return_text || substr(l_message_text, 1, 240);
660 ELSIF p_pb_input_header_rec.customer_context <> 'CUSTOMER' THEN
661 x_return_status := 'E';
662 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_CUSTOMER_CONTEXT');
663 l_message_text := FND_MESSAGE.GET;
664 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_CUSTOMER_CONTEXT';
665 l_price_book_messages_tbl(i).message_text := l_message_text;
666 l_price_book_messages_tbl(i).pb_input_header_id :=
667 p_pb_input_header_rec.pb_input_header_id;
668 i := i + 1;
669 x_return_text := x_return_text || substr(l_message_text, 1, 240);
670 END IF;
671
672
673 --Check if Customer Attribute is valid
674 IF p_pb_input_header_rec.customer_attribute IS NULL THEN
675 x_return_status := 'E';
676 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
677 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'CUSTOMER_ATTRIBUTE');
678 l_message_text := FND_MESSAGE.GET;
679 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
680 l_price_book_messages_tbl(i).message_text := l_message_text;
681 l_price_book_messages_tbl(i).pb_input_header_id :=
682 p_pb_input_header_rec.pb_input_header_id;
683 i := i + 1;
684 x_return_text := x_return_text || substr(l_message_text, 1, 240);
685 ELSIF p_pb_input_header_rec.customer_attribute <> 'QUALIFIER_ATTRIBUTE2'
686 THEN
687 x_return_status := 'E';
688 --Must be only Customer Name
689 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_CUSTOMER_ATTRIBUTE');
690 l_message_text := FND_MESSAGE.GET;
691 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_CUSTOMER_ATTRIBUTE';
692 l_price_book_messages_tbl(i).message_text := l_message_text;
693 l_price_book_messages_tbl(i).pb_input_header_id :=
694 p_pb_input_header_rec.pb_input_header_id;
695 i := i + 1;
696 x_return_text := x_return_text || substr(l_message_text, 1, 240);
697 END IF;
698
699
700 --Check if the user is an external or internal user
701 BEGIN
702 SELECT customer_id
703 INTO l_customer_id
704 FROM fnd_user
705 WHERE user_id = l_user_id;
706
707 EXCEPTION
708 WHEN OTHERS THEN
709 l_customer_id := NULL;
710
711 END; --Check if external or internal user
712
713 IF l_customer_id IS NOT NULL THEN --External User
714
715 BEGIN
716 SELECT 'Y'
717 INTO l_party_id_match
718 FROM dual
719 WHERE EXISTS (
720 SELECT 'x'
721 FROM hz_relationships rel, hz_parties party3,
722 hz_parties party4, hz_parties party5
723 WHERE rel.party_id = party5.party_id
724 AND party5.party_type = 'PARTY_RELATIONSHIP'
725 AND party5.status = 'A'
726 AND trunc(rel.start_date) <= trunc(sysdate)
727 AND trunc(nvl(rel.end_date, sysdate)) >= trunc(sysdate)
728 AND rel.subject_id = party3.party_id
729 AND party3.party_type = 'PERSON'
730 AND party3.status = 'A'
731 AND rel.object_id = party4.party_id
732 AND party4.party_type = 'ORGANIZATION'
733 AND party4.status = 'A'
734 AND rel.subject_table_name = 'HZ_PARTIES'
735 AND rel.object_table_name = 'HZ_PARTIES'
736 AND rel.relationship_id IN
737 (SELECT party_relationship_id
738 FROM hz_org_contacts org_con
739 WHERE rel.relationship_id =
740 org_con.party_relationship_id
741 AND org_con.status ='A' )
742 AND party5.party_id = l_customer_id
743 AND party4.party_id = p_pb_input_header_rec.customer_attr_value);
744 EXCEPTION
745 WHEN OTHERS THEN
746 l_party_id_match := 'N';
747 END;
748
749 --Input customer must match the customer associated with the user
750 IF p_pb_input_header_rec.customer_attr_value IS NULL THEN
751 x_return_status := 'E';
752 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
753 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'CUSTOMER_ATTR_VALUE');
754 l_message_text := FND_MESSAGE.GET;
755 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
756 l_price_book_messages_tbl(i).message_text := l_message_text;
757 l_price_book_messages_tbl(i).pb_input_header_id :=
758 p_pb_input_header_rec.pb_input_header_id;
759 i := i + 1;
760 x_return_text := x_return_text || substr(l_message_text, 1, 240);
761
762 --either the party id on the price book should match the party id on the
763 --user or the party id on the user must belong to another party of type
764 --organization such that the org party id matches the one on the price book
765 ELSIF (p_pb_input_header_rec.customer_attr_value <> l_customer_id AND
766 l_party_id_match <> 'Y')
767 THEN
768 x_return_status := 'E';
769 FND_MESSAGE.SET_NAME('QP', 'QP_CUSTOMER_NOT_MATCHING');
770 l_message_text := FND_MESSAGE.GET;
771 l_price_book_messages_tbl(i).message_code := 'QP_CUSTOMER_NOT_MATCHING';
772 l_price_book_messages_tbl(i).message_text := l_message_text;
773 l_price_book_messages_tbl(i).pb_input_header_id :=
774 p_pb_input_header_rec.pb_input_header_id;
775 i := i + 1;
776 x_return_text := x_return_text || substr(l_message_text, 1, 240);
777 END IF;
778
779 IF p_pb_input_header_rec.pricing_perspective_code = 'PO' THEN
780 x_return_status := 'E';
781 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
782 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRICING_PERSPECTIVE_CODE');
783 l_message_text := FND_MESSAGE.GET;
784 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
785 l_price_book_messages_tbl(i).message_text := l_message_text;
786 l_price_book_messages_tbl(i).pb_input_header_id :=
787 p_pb_input_header_rec.pb_input_header_id;
788 i := i + 1;
789 x_return_text := x_return_text || substr(l_message_text, 1, 240);
790 END IF;
791
792 ELSE -- Internal User
793
794 --Get Catalog not supported for Internal User
795 IF p_pb_input_header_rec.request_origination_code = 'XML' THEN
796 x_return_status := 'E';
797 FND_MESSAGE.SET_NAME('QP', 'QP_GET_CAT_NOT_FOR_INT_USER');
798 l_message_text := FND_MESSAGE.GET;
799 l_price_book_messages_tbl(i).message_code := 'QP_GET_CAT_NOT_FOR_INT_USER';
800 l_price_book_messages_tbl(i).message_text := l_message_text;
801 l_price_book_messages_tbl(i).pb_input_header_id :=
802 p_pb_input_header_rec.pb_input_header_id;
803 i := i + 1;
804 x_return_text := x_return_text || substr(l_message_text, 1, 240);
805 END IF;
806
807 IF p_pb_input_header_rec.customer_attr_value IS NULL THEN
808 x_return_status := 'E';
809 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
810 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'CUSTOMER_ATTR_VALUE');
811 l_message_text := FND_MESSAGE.GET;
812 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
813 l_price_book_messages_tbl(i).message_text := l_message_text;
814 l_price_book_messages_tbl(i).pb_input_header_id :=
815 p_pb_input_header_rec.pb_input_header_id;
816 i := i + 1;
817 x_return_text := x_return_text || substr(l_message_text, 1, 240);
818
819 ELSE --If customer_attr_value is not null
820
821 --If pricing perspective is PO then cust_attr_value must be -1
822 IF p_pb_input_header_rec.pricing_perspective_code = 'PO' THEN
823 IF p_pb_input_header_rec.customer_attr_value <> -1 THEN
824 x_return_status := 'E';
825 FND_MESSAGE.SET_NAME('QP', 'QP_PO_CUSTOMER');
826 l_message_text := FND_MESSAGE.GET;
827 l_price_book_messages_tbl(i).message_code := 'QP_PO_CUSTOMER';
828 l_price_book_messages_tbl(i).message_text := l_message_text;
829 l_price_book_messages_tbl(i).pb_input_header_id :=
830 p_pb_input_header_rec.pb_input_header_id;
831 i := i + 1;
832 x_return_text := x_return_text || substr(l_message_text, 1, 240);
833 END IF;
834
835 ELSIF (p_pb_input_header_rec.customer_context = 'CUSTOMER') AND
836 (p_pb_input_header_rec.customer_attribute = 'QUALIFIER_ATTRIBUTE2') THEN
837 --Check if Customer is valid
838 BEGIN
839 SELECT 1
840 INTO l_count
841 FROM hz_parties
842 WHERE party_id = p_pb_input_header_rec.customer_attr_value
843 AND rownum = 1;
844 EXCEPTION
845 WHEN OTHERS THEN
846 l_count := 0;
847 END;
848
849 IF l_count = 0 THEN -- invalid customer
850 x_return_status := 'E';
851 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_CUSTOMER');
852 l_message_text := FND_MESSAGE.GET;
853 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_CUSTOMER';
854 l_price_book_messages_tbl(i).message_text := l_message_text;
855 l_price_book_messages_tbl(i).pb_input_header_id :=
856 p_pb_input_header_rec.pb_input_header_id;
857 i := i + 1;
858 x_return_text := x_return_text || substr(l_message_text, 1, 240);
859 END IF;
860
861 END IF; --Check if customer is valid
862
863 END IF; --If customer attr value is null
864
865 END IF; --If External User
866
867 IF p_pb_input_header_rec.pricing_perspective_code = 'PO' THEN
868 --If pricing perspective is PO then cust_account_id must be null
869 IF p_pb_input_header_rec.cust_account_id IS NOT NULL THEN
870 x_return_status := 'E';
871 FND_MESSAGE.SET_NAME('QP', 'QP_PO_CUST_ACCT');
872 l_message_text := FND_MESSAGE.GET;
873 l_price_book_messages_tbl(i).message_code := 'QP_PO_CUST_ACCT';
874 l_price_book_messages_tbl(i).message_text := l_message_text;
875 l_price_book_messages_tbl(i).pb_input_header_id :=
876 p_pb_input_header_rec.pb_input_header_id;
877 i := i + 1;
878 x_return_text := x_return_text || substr(l_message_text, 1, 240);
879 END IF;
880
881 ELSE --pricing perspective is not PO
882
883 --Validate if the party_id(customer) and cust_account_id combination is valid
884 IF p_pb_input_header_rec.cust_account_id IS NOT NULL
885 THEN
886 BEGIN
887 SELECT 1
888 INTO l_count
889 FROM hz_cust_accounts
890 WHERE cust_account_id = p_pb_input_header_rec.cust_account_id
891 AND party_id = p_pb_input_header_rec.customer_attr_value;
892 EXCEPTION
893 WHEN OTHERS THEN
894 l_count := 0;
895 END;
896
897 IF l_count = 0 THEN
898 x_return_status := 'E';
899 FND_MESSAGE.SET_NAME('QP', 'QP_CUST_AND_ACCT_COMBI_INVALID');
900 l_message_text := FND_MESSAGE.GET;
901 l_price_book_messages_tbl(i).message_code :=
902 'QP_CUST_AND_ACCT_COMBI_INVALID';
903 l_price_book_messages_tbl(i).message_text := l_message_text;
904 l_price_book_messages_tbl(i).pb_input_header_id :=
905 p_pb_input_header_rec.pb_input_header_id;
906 i := i + 1;
907 x_return_text := x_return_text || substr(l_message_text, 1, 240);
908 END IF;
909 END IF; --If cust_account_id is not null
910
911 END IF; --If pricing perspective is PO
912
913 --Get Pricing Status - Basic Pricing or Advanced Pricing
914 l_pricing_status := QP_UTIL.Get_QP_Status;
915
916 --Check if price_book_type_code is valid
917 IF p_pb_input_header_rec.price_book_type_code IS NULL THEN
918 x_return_status := 'E';
919 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
920 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRICE_BOOK_TYPE_CODE');
921 l_message_text := FND_MESSAGE.GET;
922 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
923 l_price_book_messages_tbl(i).message_text := l_message_text;
924 l_price_book_messages_tbl(i).pb_input_header_id :=
925 p_pb_input_header_rec.pb_input_header_id;
926 i := i + 1;
927 x_return_text := x_return_text || substr(l_message_text, 1, 240);
928 ELSE
929 IF l_pricing_status = 'I' THEN --Advanced Pricing
930 IF NOT (p_pb_input_header_rec.price_book_type_code = 'F' OR
931 p_pb_input_header_rec.price_book_type_code = 'D')
932 THEN -- invalid price_book_type_code
933 x_return_status := 'E';
934 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
935 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRICE_BOOK_TYPE_CODE');
936 l_message_text := FND_MESSAGE.GET;
937 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
938 l_price_book_messages_tbl(i).message_text := l_message_text;
939 l_price_book_messages_tbl(i).pb_input_header_id :=
940 p_pb_input_header_rec.pb_input_header_id;
941 i := i + 1;
942 x_return_text := x_return_text || substr(l_message_text, 1, 240);
943 END IF;
944 ELSIF l_pricing_status = 'S' THEN --Basic Pricing
945 IF nvl(p_pb_input_header_rec.price_book_type_code, 'F') <> 'F' THEN
946 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
947 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRICE_BOOK_TYPE_CODE');
948 l_message_text := FND_MESSAGE.GET;
949 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
950 l_price_book_messages_tbl(i).message_text := l_message_text;
951 l_price_book_messages_tbl(i).pb_input_header_id :=
952 p_pb_input_header_rec.pb_input_header_id;
953 i := i + 1;
954 x_return_text := x_return_text || substr(l_message_text, 1, 240);
955 END IF;
956 END IF; --If Advanced Pricing
957 END IF; --If price_book_type_code is null
958
959
960 --Check if price book name is not-null
961 IF p_pb_input_header_rec.price_book_name IS NULL THEN
962 x_return_status := 'E';
963 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
964 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRICE_BOOK_NAME');
965 l_message_text := FND_MESSAGE.GET;
966 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
967 l_price_book_messages_tbl(i).message_text := l_message_text;
968 l_price_book_messages_tbl(i).pb_input_header_id :=
969 p_pb_input_header_rec.pb_input_header_id;
970 i := i + 1;
971 x_return_text := x_return_text || substr(l_message_text, 1, 240);
972 END IF;
973
974
975 --If delta price book to be generated
976 IF (nvl(p_pb_input_header_rec.publish_existing_pb_flag, 'N') <> 'Y') AND
977 (p_pb_input_header_rec.price_book_type_code = 'D')
978 THEN
979 --Check if a corresponding full price book exists
980 BEGIN
981 SELECT 1
982 INTO l_count
983 FROM qp_price_book_headers_vl
984 WHERE price_book_name = p_pb_input_header_rec.price_book_name
985 AND price_book_type_code = 'F'
986 AND customer_id = p_pb_input_header_rec.customer_attr_value
987 AND rownum = 1;
988 EXCEPTION
989 WHEN OTHERS THEN
990 l_count := 0;
991 END;
992
993 IF l_count = 0 THEN --corresponding full price book does not exist
994 FND_MESSAGE.SET_NAME('QP', 'QP_FULL_PRICE_BOOK_MUST_EXIST');
995 x_return_status := 'E';
996 l_message_text := FND_MESSAGE.GET;
997 l_price_book_messages_tbl(i).message_code := 'QP_FULL_PRICE_BOOK_MUST_EXIST';
998 l_price_book_messages_tbl(i).message_text := l_message_text;
999 l_price_book_messages_tbl(i).pb_input_header_id :=
1000 p_pb_input_header_rec.pb_input_header_id;
1001 i := i + 1;
1002 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1003 END IF;
1004
1005 END IF; --If delta price book to be generated, check if full price book exists
1006
1007 --If Basic Pricing user then xml messaging is not supported
1008 IF l_pricing_status = 'S' THEN
1009 IF p_pb_input_header_rec.request_origination_code = 'XML' OR
1010 p_pb_input_header_rec.dlv_xml_flag = 'Y'
1011 THEN
1012 x_return_status := 'E';
1013 FND_MESSAGE.SET_NAME('QP', 'QP_XML_NOT_FOR_BASIC');
1014 l_message_text := FND_MESSAGE.GET;
1015 l_price_book_messages_tbl(i).message_code := 'QP_XML_NOT_FOR_BASIC';
1016 l_price_book_messages_tbl(i).message_text := l_message_text;
1017 l_price_book_messages_tbl(i).pb_input_header_id :=
1018 p_pb_input_header_rec.pb_input_header_id;
1019 i := i + 1;
1020 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1021 END IF;
1022 END IF;
1023
1024 --If pricing perspective is PO then xml messaging is not supported
1025 IF p_pb_input_header_rec.pricing_perspective_code = 'PO' THEN
1026 IF p_pb_input_header_rec.request_origination_code = 'XML' OR
1027 p_pb_input_header_rec.dlv_xml_flag = 'Y'
1028 THEN
1029 x_return_status := 'E';
1030 FND_MESSAGE.SET_NAME('QP', 'QP_XML_NOT_FOR_PO');
1031 l_message_text := FND_MESSAGE.GET;
1032 l_price_book_messages_tbl(i).message_code := 'QP_XML_NOT_FOR_PO';
1033 l_price_book_messages_tbl(i).message_text := l_message_text;
1034 l_price_book_messages_tbl(i).pb_input_header_id :=
1035 p_pb_input_header_rec.pb_input_header_id;
1036 i := i + 1;
1037 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1038 END IF;
1039
1040 IF p_pb_input_header_rec.dlv_xml_site_id IS NOT NULL THEN
1041 x_return_status := 'E';
1042 FND_MESSAGE.SET_NAME('QP', 'QP_PO_XML_SITE');
1043 l_message_text := FND_MESSAGE.GET;
1044 l_price_book_messages_tbl(i).message_code := 'QP_PO_XML_SITE';
1045 l_price_book_messages_tbl(i).message_text := l_message_text;
1046 l_price_book_messages_tbl(i).pb_input_header_id :=
1047 p_pb_input_header_rec.pb_input_header_id;
1048 i := i + 1;
1049 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1050 END IF;
1051 END IF;
1052
1053 --If called from Get_Catalog only XML Message allowed, not printing or email.
1054 IF p_pb_input_header_rec.request_origination_code = 'XML' THEN
1055 IF nvl(p_pb_input_header_rec.dlv_printer_flag, 'N') = 'Y' OR
1056 nvl(p_pb_input_header_rec.dlv_email_flag, 'N') = 'Y'
1057 THEN
1058 x_return_status := 'E';
1059 FND_MESSAGE.SET_NAME('QP', 'QP_ONLY_XML_DELIVERY_ALLOWED');
1060 l_message_text := FND_MESSAGE.GET;
1061 l_price_book_messages_tbl(i).message_code := 'QP_ONLY_XML_DELIVERY_ALLOWED';
1062 l_price_book_messages_tbl(i).message_text := l_message_text;
1063 l_price_book_messages_tbl(i).pb_input_header_id :=
1064 p_pb_input_header_rec.pb_input_header_id;
1065 i := i + 1;
1066 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1067 END IF;
1068 END IF;
1069
1070
1071 --Validate that flags are either 'Y', 'N' or null
1072 IF p_pb_input_header_rec.publish_existing_pb_flag IS NOT NULL AND
1073 p_pb_input_header_rec.publish_existing_pb_flag NOT IN ('Y', 'N')
1074 THEN
1075 x_return_status := 'E';
1076 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1077 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PUBLISH_EXISTING_PB_FLAG');
1078 l_message_text := FND_MESSAGE.GET;
1079 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1080 l_price_book_messages_tbl(i).message_text := l_message_text;
1081 l_price_book_messages_tbl(i).pb_input_header_id :=
1082 p_pb_input_header_rec.pb_input_header_id;
1083 i := i + 1;
1084 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1085 END IF;
1086
1087 IF p_pb_input_header_rec.overwrite_existing_pb_flag IS NOT NULL AND
1088 p_pb_input_header_rec.overwrite_existing_pb_flag NOT IN ('Y', 'N')
1089 THEN
1090 x_return_status := 'E';
1091 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1092 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'OVERWRITE_EXISTING_PB_FLAG');
1093 l_message_text := FND_MESSAGE.GET;
1094 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1095 l_price_book_messages_tbl(i).message_text := l_message_text;
1096 l_price_book_messages_tbl(i).pb_input_header_id :=
1097 p_pb_input_header_rec.pb_input_header_id;
1098 i := i + 1;
1099 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1100 END IF;
1101
1102 IF p_pb_input_header_rec.dlv_email_flag IS NOT NULL AND
1103 p_pb_input_header_rec.dlv_email_flag NOT IN ('Y', 'N')
1104 THEN
1105 x_return_status := 'E';
1106 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1107 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'DLV_EMAIL_FLAG');
1108 l_message_text := FND_MESSAGE.GET;
1109 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1110 l_price_book_messages_tbl(i).message_text := l_message_text;
1111 l_price_book_messages_tbl(i).pb_input_header_id :=
1112 p_pb_input_header_rec.pb_input_header_id;
1113 i := i + 1;
1114 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1115 END IF;
1116
1117 IF p_pb_input_header_rec.dlv_printer_flag IS NOT NULL AND
1118 p_pb_input_header_rec.dlv_printer_flag NOT IN ('Y', 'N')
1119 THEN
1120 x_return_status := 'E';
1121 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1122 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'DLV_PRINTER_FLAG');
1123 l_message_text := FND_MESSAGE.GET;
1124 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1125 l_price_book_messages_tbl(i).message_text := l_message_text;
1126 l_price_book_messages_tbl(i).pb_input_header_id :=
1127 p_pb_input_header_rec.pb_input_header_id;
1128 i := i + 1;
1129 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1130 END IF;
1131
1132 IF p_pb_input_header_rec.dlv_xml_flag IS NOT NULL AND
1133 p_pb_input_header_rec.dlv_xml_flag NOT IN ('Y', 'N')
1134 THEN
1135 x_return_status := 'E';
1136 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1137 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'DLV_XML_FLAG');
1138 l_message_text := FND_MESSAGE.GET;
1139 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1140 l_price_book_messages_tbl(i).message_text := l_message_text;
1141 l_price_book_messages_tbl(i).pb_input_header_id :=
1142 p_pb_input_header_rec.pb_input_header_id;
1143 i := i + 1;
1144 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1145 END IF;
1146
1147
1148
1149 --If delivery option is email then email_addresses must be specified.
1150 IF p_pb_input_header_rec.dlv_email_flag = 'Y' THEN
1151 IF p_pb_input_header_rec.dlv_email_addresses IS NULL THEN
1152 x_return_status := 'E';
1153 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1154 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'DLV_EMAIL_ADDRESSES');
1155 l_message_text := FND_MESSAGE.GET;
1156 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1157 l_price_book_messages_tbl(i).message_text := l_message_text;
1158 l_price_book_messages_tbl(i).pb_input_header_id :=
1159 p_pb_input_header_rec.pb_input_header_id;
1160 i := i + 1;
1161 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1162 END IF;
1163 END IF;
1164
1165 IF p_pb_input_header_rec.generation_time_code NOT IN ('IMMEDIATE', 'SCHEDULE')
1166 THEN
1167 x_return_status := 'E';
1168 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1169 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'GENERATION_TIME_CODE');
1170 l_message_text := FND_MESSAGE.GET;
1171 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1172 l_price_book_messages_tbl(i).message_text := l_message_text;
1173 l_price_book_messages_tbl(i).pb_input_header_id :=
1174 p_pb_input_header_rec.pb_input_header_id;
1175 i := i + 1;
1176 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1177 END IF;
1178
1179 IF p_pb_input_header_rec.generation_time_code = 'SCHEDULE' THEN
1180 IF p_pb_input_header_rec.gen_schedule_date IS NULL THEN
1181 x_return_status := 'E';
1182 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1183 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'GEN_SCHEDULE_DATE');
1184 l_message_text := FND_MESSAGE.GET;
1185 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1186 l_price_book_messages_tbl(i).message_text := l_message_text;
1187 l_price_book_messages_tbl(i).pb_input_header_id :=
1188 p_pb_input_header_rec.pb_input_header_id;
1189 i := i + 1;
1190 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1191 END IF;
1192 END IF;
1193
1194 --Validate publishing template information
1195 IF nvl(p_pb_input_header_rec.dlv_email_flag, 'N') = 'Y' OR
1196 nvl(p_pb_input_header_rec.dlv_printer_flag, 'N') = 'Y' OR
1197 p_pb_input_header_rec.pub_template_code IS NOT NULL --view document
1198 THEN
1199
1200 IF p_pb_input_header_rec.pub_template_code is NULL
1201 THEN
1202 x_return_status := 'E';
1203 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1204 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'pub_template_code');
1205 l_message_text := FND_MESSAGE.GET;
1206 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1207 l_price_book_messages_tbl(i).message_text := l_message_text;
1208 l_price_book_messages_tbl(i).pb_input_header_id :=
1209 p_pb_input_header_rec.pb_input_header_id;
1210 i := i + 1;
1211 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1212 END IF;
1213
1214 IF p_pb_input_header_rec.pub_language is NULL
1215 THEN
1216 x_return_status := 'E';
1217 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1218 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'pub_language');
1219 l_message_text := FND_MESSAGE.GET;
1220 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1221 l_price_book_messages_tbl(i).message_text := l_message_text;
1222 l_price_book_messages_tbl(i).pb_input_header_id :=
1223 p_pb_input_header_rec.pb_input_header_id;
1224 i := i + 1;
1225 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1226 END IF;
1227
1228 IF p_pb_input_header_rec.pub_territory is NULL
1229 THEN
1230 x_return_status := 'E';
1231 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1232 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'pub_territory');
1233 l_message_text := FND_MESSAGE.GET;
1234 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1235 l_price_book_messages_tbl(i).message_text := l_message_text;
1236 l_price_book_messages_tbl(i).pb_input_header_id :=
1237 p_pb_input_header_rec.pb_input_header_id;
1238 i := i + 1;
1239 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1240 END IF;
1241
1242 BEGIN
1243 SELECT 1
1244 INTO l_count
1245 FROM xdo_templates_vl
1246 WHERE template_code = p_pb_input_header_rec.pub_template_code
1247 AND application_short_name = 'QP'
1248 AND rownum = 1;
1249 EXCEPTION
1250 WHEN OTHERS THEN
1251 l_count := 0;
1252 END;
1253
1254 IF l_count = 0 THEN
1255 x_return_status := 'E';
1256 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1257 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PUB_TEMPLATE_CODE');
1258 l_message_text := FND_MESSAGE.GET;
1259 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1260 l_price_book_messages_tbl(i).message_text := l_message_text;
1261 l_price_book_messages_tbl(i).pb_input_header_id :=
1262 p_pb_input_header_rec.pb_input_header_id;
1263 i := i + 1;
1264 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1265 END IF;
1266
1267 BEGIN
1268 SELECT 1
1269 INTO l_count
1270 FROM xdo_lobs tmpl, xdo_templates_vl t
1271 WHERE t.APPLICATION_SHORT_NAME = 'QP'
1272 AND t.TEMPLATE_CODE = tmpl.LOB_CODE
1273 AND tmpl.LOB_TYPE in ('TEMPLATE','MLS_TEMPLATE')
1274 AND tmpl.FILE_STATUS = 'E'
1275 AND t.template_code = p_pb_input_header_rec.pub_template_code
1276 AND lower(tmpl.LANGUAGE) = lower(p_pb_input_header_rec.pub_language)
1277 AND upper(tmpl.TERRITORY) = upper(p_pb_input_header_rec.pub_territory);
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 l_count := 0;
1281 END;
1282
1283 IF l_count = 0 THEN
1284 x_return_status := 'E';
1285 FND_MESSAGE.SET_NAME('QP', 'QP_TEMPLATE_COMBI_INVALID');
1286 l_message_text := FND_MESSAGE.GET;
1287 l_price_book_messages_tbl(i).message_code := 'QP_TEMPLATE_COMBI_INVALID';
1288 l_price_book_messages_tbl(i).message_text := l_message_text;
1289 l_price_book_messages_tbl(i).pb_input_header_id :=
1290 p_pb_input_header_rec.pb_input_header_id;
1291 i := i + 1;
1292 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1293 END IF;
1294
1295 END IF; --If email or printer delivery flag is 'Y' or template code not null
1296
1297 --If printer flag is checked and email flag is not checked then only
1298 --pdf output doc type is permitted
1299 IF nvl(p_pb_input_header_rec.dlv_printer_flag, 'N') = 'Y' AND
1300 nvl(p_pb_input_header_rec.dlv_email_flag, 'N') <> 'Y'
1301 THEN
1302 IF p_pb_input_header_rec.pub_output_document_type <> 'PDF' THEN
1303 x_return_status := 'E';
1304 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1305 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PUB_OUTPUT_DOCUMENT_TYPE');
1306 l_message_text := FND_MESSAGE.GET;
1307 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1308 l_price_book_messages_tbl(i).message_text := l_message_text;
1309 l_price_book_messages_tbl(i).pb_input_header_id :=
1310 p_pb_input_header_rec.pb_input_header_id;
1311 i := i + 1;
1312 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1313 END IF;
1314 END IF;
1315
1316 --If template_code is not null but document type is null
1317 IF p_pb_input_header_rec.dlv_email_flag= 'Y' OR
1318 (nvl(p_pb_input_header_rec.dlv_email_flag, 'N') = 'N' AND
1319 nvl(p_pb_input_header_rec.dlv_printer_flag, 'N') = 'N')
1320 THEN
1321 IF p_pb_input_header_rec.pub_template_code IS NOT NULL AND
1322 p_pb_input_header_rec.pub_output_document_type IS NULL
1323 THEN
1324 x_return_status := 'E';
1325 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1326 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PUB_OUTPUT_DOCUMENT_TYPE');
1327 l_message_text := FND_MESSAGE.GET;
1328 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1329 l_price_book_messages_tbl(i).message_text := l_message_text;
1330 l_price_book_messages_tbl(i).pb_input_header_id :=
1331 p_pb_input_header_rec.pb_input_header_id;
1332 i := i + 1;
1333 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1334 END IF;
1335 END IF;
1336
1337 --If dlv_xml_flag is checked then dlv_xml_site_id is required
1338 IF p_pb_input_header_rec.dlv_xml_flag = 'Y' THEN
1339 IF p_pb_input_header_rec.dlv_xml_site_id IS NULL THEN
1340 x_return_status := 'E';
1341 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1342 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'DLV_XML_SITE_ID');
1343 l_message_text := FND_MESSAGE.GET;
1344 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1345 l_price_book_messages_tbl(i).message_text := l_message_text;
1346 l_price_book_messages_tbl(i).pb_input_header_id :=
1347 p_pb_input_header_rec.pb_input_header_id;
1348 i := i + 1;
1349 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1350 ELSE --dlv_xml_site_id is not null
1351 BEGIN
1352 SELECT 1
1353 INTO l_count
1354 FROM ecx_tp_headers eth, ecx_tp_details etd,
1355 ecx_ext_processes eep, ecx_transactions et,
1356 hz_parties hp, hz_party_sites hps, hz_locations hl
1357 WHERE eth.party_id = p_pb_input_header_rec.customer_attr_value
1358 AND eth.party_site_id = p_pb_input_header_rec.dlv_xml_site_id
1359 AND eth.tp_header_id = etd.tp_header_id
1360 AND etd.EXT_PROCESS_ID = eep.EXT_PROCESS_ID
1361 AND eth.party_id = hp.party_id
1362 AND eth.party_site_id = hps.party_site_id
1363 AND hps.location_id = hl.location_id
1364 AND eep.transaction_id = et.transaction_id
1365 AND et.transaction_type = 'QP'
1366 AND et.transaction_subtype = 'CATSO'
1367 AND eep.direction = 'OUT';
1368 EXCEPTION
1369 WHEN OTHERS THEN
1370 l_count := 0;
1371 END;
1372
1373 IF l_count = 0 THEN
1374 x_return_status := 'E';
1375 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1376 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'DLV_XML_SITE_ID');
1377 l_message_text := FND_MESSAGE.GET;
1378 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1379 l_price_book_messages_tbl(i).message_text := l_message_text;
1380 l_price_book_messages_tbl(i).pb_input_header_id :=
1381 p_pb_input_header_rec.pb_input_header_id;
1382 i := i + 1;
1383 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1384 END IF;
1385
1386 END IF;
1387 END IF;
1388
1389 --If re-publishing an existing price book, no generation is involved
1390 IF p_pb_input_header_rec.publish_existing_pb_flag = 'Y' THEN
1391
1392 --The price book to be re-published must exist
1393 BEGIN
1394 SELECT 1
1395 INTO l_count
1396 FROM qp_price_book_headers_vl
1397 WHERE price_book_name = p_pb_input_header_rec.price_book_name
1398 AND price_book_type_code = p_pb_input_header_rec.price_book_type_code
1399 AND customer_id = p_pb_input_header_rec.customer_attr_value
1400 AND rownum = 1;
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 l_count := 0;
1404 END;
1405
1406 IF l_count = 0 THEN
1407 x_return_status := 'E';
1408 FND_MESSAGE.SET_NAME('QP', 'QP_PRICE_BOOK_DOES_NOT_EXIST');
1409 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_NAME',
1410 p_pb_input_header_rec.price_book_name);
1411 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_TYPE_CODE',
1412 p_pb_input_header_rec.price_book_type_code);
1413 l_message_text := FND_MESSAGE.GET;
1414 l_price_book_messages_tbl(i).message_code := 'QP_PRICE_BOOK_DOES_NOT_EXIST';
1415 l_price_book_messages_tbl(i).message_text := l_message_text;
1416 l_price_book_messages_tbl(i).pb_input_header_id :=
1417 p_pb_input_header_rec.pb_input_header_id;
1418 i := i + 1;
1419 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1420 END IF;
1421
1422 ELSE--Generate and/or publish new price book
1423
1424 --Check that Price Book(s) to be generated does(do) not exist
1425 BEGIN
1426 SELECT 1
1427 INTO l_count
1428 FROM qp_price_book_headers_vl
1429 WHERE price_book_name = p_pb_input_header_rec.price_book_name
1430 AND price_book_type_code = p_pb_input_header_rec.price_book_type_code
1431 AND customer_id = p_pb_input_header_rec.customer_attr_value
1432 AND rownum = 1;
1433 EXCEPTION
1434 WHEN OTHERS THEN
1435 l_count := 0;
1436 END;
1437
1438 IF l_count > 0 AND nvl(p_pb_input_header_rec.overwrite_existing_pb_flag, 'N') = 'N'
1439 THEN
1440 x_return_status := 'E';
1441 -- Price book to be generated already exists
1442 FND_MESSAGE.SET_NAME('QP', 'QP_PRICE_BOOK_ALREADY_EXISTS');
1443 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_NAME',
1444 p_pb_input_header_rec.price_book_name);
1445 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_TYPE_CODE',
1446 p_pb_input_header_rec.price_book_type_code);
1447 l_message_text := FND_MESSAGE.GET;
1448 l_price_book_messages_tbl(i).message_code := 'QP_PRICE_BOOK_ALREADY_EXISTS';
1449 l_price_book_messages_tbl(i).message_text := l_message_text;
1450 l_price_book_messages_tbl(i).pb_input_header_id :=
1451 p_pb_input_header_rec.pb_input_header_id;
1452 i := i + 1;
1453 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1454 END IF;
1455
1456 --Check that Price Book(s) to be generated does(do) not exist in another
1457 --org outside the accessible orgs
1458 BEGIN
1459 SELECT 1
1460 INTO l_count2
1461 FROM qp_price_book_headers_all_b b, qp_price_book_headers_tl t
1462 WHERE b.price_book_header_id = t.price_book_header_id
1463 AND t.language = userenv('LANG')
1464 AND b.price_book_type_code = p_pb_input_header_rec.price_book_type_code
1465 AND b.customer_id = p_pb_input_header_rec.customer_attr_value
1466 AND t.price_book_name = p_pb_input_header_rec.price_book_name
1467 AND rownum = 1;
1468 EXCEPTION
1469 WHEN OTHERS THEN
1470 l_count2 := 0;
1471 END;
1472
1473 IF l_count = 0 AND l_count2 > 0 THEN
1474 x_return_status := 'E';
1475 -- Price book to be generated already exists
1476 FND_MESSAGE.SET_NAME('QP', 'QP_PB_EXISTS_IN_ANOTHER_ORG');
1477 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_NAME',
1478 p_pb_input_header_rec.price_book_name);
1479 l_message_text := FND_MESSAGE.GET;
1480 l_price_book_messages_tbl(i).message_code := 'QP_PB_EXISTS_IN_ANOTHER_ORG';
1481 l_price_book_messages_tbl(i).message_text := l_message_text;
1482 l_price_book_messages_tbl(i).pb_input_header_id :=
1483 p_pb_input_header_rec.pb_input_header_id;
1484 i := i + 1;
1485 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1486 END IF;
1487
1488 --Check if limit_products_by is valid
1489 IF p_pb_input_header_rec.limit_products_by IS NULL THEN
1490 x_return_status := 'E';
1491 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1492 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'LIMIT_PRODUCTS_BY');
1493 l_message_text := FND_MESSAGE.GET;
1494 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1495 l_price_book_messages_tbl(i).message_text := l_message_text;
1496 l_price_book_messages_tbl(i).pb_input_header_id :=
1497 p_pb_input_header_rec.pb_input_header_id;
1498 i := i + 1;
1499 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1500 ELSIF p_pb_input_header_rec.limit_products_by NOT IN
1501 ('ITEM', 'ITEM_CATEGORY', 'ALL_ITEMS', 'PRICE_LIST')
1502 THEN
1503 x_return_status := 'E';
1504 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1505 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'LIMIT_PRODUCTS_BY');
1506 l_message_text := FND_MESSAGE.GET;
1507 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1508 l_price_book_messages_tbl(i).message_text := l_message_text;
1509 l_price_book_messages_tbl(i).pb_input_header_id :=
1510 p_pb_input_header_rec.pb_input_header_id;
1511 i := i + 1;
1512 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1513 ELSE --Validate product_context, product_attribute, product_attr_value
1514 IF p_pb_input_header_rec.limit_products_by = 'ITEM' THEN
1515 IF p_pb_input_header_rec.product_context IS NULL THEN
1516 x_return_status := 'E';
1517 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1518 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_CONTEXT');
1519 l_message_text := FND_MESSAGE.GET;
1520 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1521 l_price_book_messages_tbl(i).message_text := l_message_text;
1522 l_price_book_messages_tbl(i).pb_input_header_id :=
1523 p_pb_input_header_rec.pb_input_header_id;
1524 i := i + 1;
1525 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1526 ELSIF p_pb_input_header_rec.product_attribute IS NULL THEN
1527 x_return_status := 'E';
1528 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1529 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTRIBUTE');
1530 l_message_text := FND_MESSAGE.GET;
1531 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1532 l_price_book_messages_tbl(i).message_text := l_message_text;
1533 l_price_book_messages_tbl(i).pb_input_header_id :=
1534 p_pb_input_header_rec.pb_input_header_id;
1535 i := i + 1;
1536 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1537 ELSIF p_pb_input_header_rec.product_context <> 'ITEM' THEN
1538 x_return_status := 'E';
1539 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1540 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_CONTEXT');
1541 l_message_text := FND_MESSAGE.GET;
1542 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1543 l_price_book_messages_tbl(i).message_text := l_message_text;
1544 l_price_book_messages_tbl(i).pb_input_header_id :=
1545 p_pb_input_header_rec.pb_input_header_id;
1546 i := i + 1;
1547 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1548 ELSIF p_pb_input_header_rec.product_attribute <> 'PRICING_ATTRIBUTE1'
1549 THEN --Product Attribute must be 'Item Number'
1550 x_return_status := 'E';
1551 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1552 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTRIBUTE');
1553 l_message_text := FND_MESSAGE.GET;
1554 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1555 l_price_book_messages_tbl(i).message_text := l_message_text;
1556 l_price_book_messages_tbl(i).pb_input_header_id :=
1557 p_pb_input_header_rec.pb_input_header_id;
1558 i := i + 1;
1559 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1560 ELSIF p_pb_input_header_rec.product_attr_value IS NULL THEN
1561 x_return_status := 'E';
1562 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1563 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTR_VALUE');
1564 l_message_text := FND_MESSAGE.GET;
1565 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1566 l_price_book_messages_tbl(i).message_text := l_message_text;
1567 l_price_book_messages_tbl(i).pb_input_header_id :=
1568 p_pb_input_header_rec.pb_input_header_id;
1569 i := i + 1;
1570 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1571 ELSE
1572 BEGIN
1573 SELECT 1
1574 INTO l_count
1575 FROM mtl_system_items_kfv
1576 WHERE inventory_item_id =
1577 to_number(p_pb_input_header_rec.product_attr_value)
1578 AND organization_id = QP_UTIL.Get_Item_Validation_Org
1579 AND purchasing_enabled_flag =
1580 decode(p_pb_input_header_rec.pricing_perspective_code,
1581 'PO', 'Y', purchasing_enabled_flag)
1582 AND rownum = 1;
1583 EXCEPTION
1584 WHEN OTHERS THEN
1585 l_count := 0;
1586 END;
1587
1588 IF l_count = 0 THEN
1589 x_return_status := 'E';
1590 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1591 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTR_VALUE');
1592 l_message_text := FND_MESSAGE.GET;
1593 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1594 l_price_book_messages_tbl(i).message_text := l_message_text;
1595 l_price_book_messages_tbl(i).pb_input_header_id :=
1596 p_pb_input_header_rec.pb_input_header_id;
1597 i := i + 1;
1598 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1599 END IF;
1600 END IF;
1601
1602 ELSIF p_pb_input_header_rec.limit_products_by = 'ITEM_CATEGORY' THEN
1603 IF p_pb_input_header_rec.product_context IS NULL THEN
1604 x_return_status := 'E';
1605 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1606 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_CONTEXT');
1607 l_message_text := FND_MESSAGE.GET;
1608 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1609 l_price_book_messages_tbl(i).message_text := l_message_text;
1610 l_price_book_messages_tbl(i).pb_input_header_id :=
1611 p_pb_input_header_rec.pb_input_header_id;
1612 i := i + 1;
1613 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1614 ELSIF p_pb_input_header_rec.product_attribute IS NULL THEN
1615 x_return_status := 'E';
1616 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1617 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTRIBUTE');
1618 l_message_text := FND_MESSAGE.GET;
1619 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1620 l_price_book_messages_tbl(i).message_text := l_message_text;
1621 l_price_book_messages_tbl(i).pb_input_header_id :=
1622 p_pb_input_header_rec.pb_input_header_id;
1623 i := i + 1;
1624 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1625 ELSIF p_pb_input_header_rec.product_context <> 'ITEM' THEN
1626 x_return_status := 'E';
1627 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1628 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_CONTEXT');
1629 l_message_text := FND_MESSAGE.GET;
1630 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1631 l_price_book_messages_tbl(i).message_text := l_message_text;
1632 l_price_book_messages_tbl(i).pb_input_header_id :=
1633 p_pb_input_header_rec.pb_input_header_id;
1634 i := i + 1;
1635 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1636 ELSIF p_pb_input_header_rec.product_attribute <> 'PRICING_ATTRIBUTE2'
1637 THEN --Product Attribute must be 'Item Category'
1638 x_return_status := 'E';
1639 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1640 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTRIBUTE');
1641 l_message_text := FND_MESSAGE.GET;
1642 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1643 l_price_book_messages_tbl(i).message_text := l_message_text;
1644 l_price_book_messages_tbl(i).pb_input_header_id :=
1645 p_pb_input_header_rec.pb_input_header_id;
1646 i := i + 1;
1647 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1648 ELSIF p_pb_input_header_rec.product_attr_value IS NULL THEN
1649 x_return_status := 'E';
1650 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1651 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTR_VALUE');
1652 l_message_text := FND_MESSAGE.GET;
1653 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1654 l_price_book_messages_tbl(i).message_text := l_message_text;
1655 l_price_book_messages_tbl(i).pb_input_header_id :=
1656 p_pb_input_header_rec.pb_input_header_id;
1657 i := i + 1;
1658 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1659 ELSE
1660 --Get pte_code for the request_type_code
1661 BEGIN
1662 SELECT pte_code
1663 INTO l_pte_code
1664 FROM qp_pte_request_types_b
1665 WHERE request_type_code = l_request_type_code;
1666 EXCEPTION
1667 WHEN OTHERS THEN
1668 l_pte_code := 'ORDFUL';
1669 END;
1670
1671 --Check if category is valid using Product Hierarchy validation API
1672 QP_UTIL.get_item_cat_info(
1673 p_item_id => to_number(p_pb_input_header_rec.product_attr_value),
1674 --category_id
1675 p_item_pte => l_pte_code,
1676 p_item_ss => null, --all source systems in the pte
1677 x_item_name => l_name,
1678 x_item_desc => l_desc,
1679 x_is_valid => l_category_valid);
1680
1681 IF l_category_valid = FALSE THEN
1682 x_return_status := 'E';
1683 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1684 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTR_VALUE');
1685 l_message_text := FND_MESSAGE.GET;
1686 l_price_book_messages_tbl(i).message_code :=
1687 'QP_INVALID_ATTRIBUTE';
1688 l_price_book_messages_tbl(i).message_text := l_message_text;
1689 l_price_book_messages_tbl(i).pb_input_header_id :=
1690 p_pb_input_header_rec.pb_input_header_id;
1691 i := i + 1;
1692 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1693 END IF;
1694 END IF; --If product_context is null...
1695
1696 ELSIF p_pb_input_header_rec.limit_products_by = 'ALL_ITEMS' THEN
1697 IF p_pb_input_header_rec.product_context IS NULL THEN
1698 x_return_status := 'E';
1699 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1700 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_CONTEXT');
1701 l_message_text := FND_MESSAGE.GET;
1702 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1703 l_price_book_messages_tbl(i).message_text := l_message_text;
1704 l_price_book_messages_tbl(i).pb_input_header_id :=
1705 p_pb_input_header_rec.pb_input_header_id;
1706 i := i + 1;
1707 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1708 ELSIF p_pb_input_header_rec.product_attribute IS NULL THEN
1709 x_return_status := 'E';
1710 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1711 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTRIBUTE');
1712 l_message_text := FND_MESSAGE.GET;
1713 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1714 l_price_book_messages_tbl(i).message_text := l_message_text;
1715 l_price_book_messages_tbl(i).pb_input_header_id :=
1716 p_pb_input_header_rec.pb_input_header_id;
1717 i := i + 1;
1718 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1719 ELSIF p_pb_input_header_rec.product_context <> 'ITEM' THEN
1720 x_return_status := 'E';
1721 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1722 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_CONTEXT');
1723 l_message_text := FND_MESSAGE.GET;
1724 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1725 l_price_book_messages_tbl(i).message_text := l_message_text;
1726 l_price_book_messages_tbl(i).pb_input_header_id :=
1727 p_pb_input_header_rec.pb_input_header_id;
1728 i := i + 1;
1729 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1730 ELSIF p_pb_input_header_rec.product_attribute <> 'PRICING_ATTRIBUTE3'
1731 THEN --Product Attribute must be 'All Items'
1732 x_return_status := 'E';
1733 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1734 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTRIBUTE');
1735 l_message_text := FND_MESSAGE.GET;
1736 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1737 l_price_book_messages_tbl(i).message_text := l_message_text;
1738 l_price_book_messages_tbl(i).pb_input_header_id :=
1739 p_pb_input_header_rec.pb_input_header_id;
1740 i := i + 1;
1741 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1742 ELSIF p_pb_input_header_rec.product_attr_value IS NULL THEN
1743 x_return_status := 'E';
1744 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1745 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTR_VALUE');
1746 l_message_text := FND_MESSAGE.GET;
1747 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1748 l_price_book_messages_tbl(i).message_text := l_message_text;
1749 l_price_book_messages_tbl(i).pb_input_header_id :=
1750 p_pb_input_header_rec.pb_input_header_id;
1751 i := i + 1;
1752 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1753 ELSIF p_pb_input_header_rec.product_attr_value <> 'ALL' THEN
1754 x_return_status := 'E';
1755 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1756 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRODUCT_ATTR_VALUE');
1757 l_message_text := FND_MESSAGE.GET;
1758 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1759 l_price_book_messages_tbl(i).message_text := l_message_text;
1760 l_price_book_messages_tbl(i).pb_input_header_id :=
1761 p_pb_input_header_rec.pb_input_header_id;
1762 i := i + 1;
1763 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1764 END IF;--If product context is null when limit by is ALL_ITEMS
1765
1766 ELSIF p_pb_input_header_rec.limit_products_by = 'PRICE_LIST' THEN
1767 IF p_pb_input_header_rec.pl_agr_bsa_id IS NULL THEN
1768 x_return_status := 'E';
1769 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1770 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PL_AGR_BSA_ID');
1771 l_message_text := FND_MESSAGE.GET;
1772 l_price_book_messages_tbl(i).message_code :=
1773 'QP_ATTRIBUTE_REQUIRED';
1774 l_price_book_messages_tbl(i).message_text := l_message_text;
1775 l_price_book_messages_tbl(i).pb_input_header_id :=
1776 p_pb_input_header_rec.pb_input_header_id;
1777 i := i + 1;
1778 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1779 ELSIF nvl(p_pb_input_header_rec.price_based_on, 'X') <> 'PRICE_LIST' THEN
1780 x_return_status := 'E';
1781 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1782 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRICE_BASED_ON');
1783 l_message_text := FND_MESSAGE.GET;
1784 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1785 l_price_book_messages_tbl(i).message_text := l_message_text;
1786 l_price_book_messages_tbl(i).pb_input_header_id :=
1787 p_pb_input_header_rec.pb_input_header_id;
1788 i := i + 1;
1789 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1790 ELSE
1791 BEGIN
1792 SELECT 1
1793 INTO l_count
1794 FROM qp_list_headers_vl
1795 WHERE list_type_code = 'PRL'
1796 AND nvl(list_source_code, 'X') <> 'BSO'
1797 AND nvl(active_flag, 'N') = 'Y'
1798 AND (global_flag = 'Y' OR
1799 orig_org_id = p_pb_input_header_rec.org_id)
1800 AND source_system_code IN (SELECT application_short_name
1801 FROM qp_pte_source_systems
1802 WHERE pte_code = l_pte_code)
1803 AND list_header_id = p_pb_input_header_rec.pl_agr_bsa_id
1804 AND rownum = 1;
1805 EXCEPTION
1806 WHEN OTHERS THEN
1807 l_count := 0;
1808 END;
1809
1810 IF l_count = 0 THEN
1811 x_return_status := 'E';
1812 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1813 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PL_AGR_BSA_ID');
1814 l_message_text := FND_MESSAGE.GET;
1815 l_price_book_messages_tbl(i).message_code :=
1816 'QP_INVALID_ATTRIBUTE';
1817 l_price_book_messages_tbl(i).message_text := l_message_text;
1818 l_price_book_messages_tbl(i).pb_input_header_id :=
1819 p_pb_input_header_rec.pb_input_header_id;
1820 i := i + 1;
1821 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1822 END IF;
1823
1824 END IF; --If pl_agr_bsa_id is null
1825 END IF; --If limit_products_by = 'ITEM'
1826 END IF; --If limit_products_by <> ITEM,ITEM_CATEGORY,ALL_ITEMS or PRICE_LIST
1827
1828
1829 --Validate price_based_on
1830 IF p_pb_input_header_rec.price_based_on IS NOT NULL THEN
1831
1832 --If pricing perspective is PO then only PRICE_LIST is valid
1833 IF p_pb_input_header_rec.pricing_perspective_code = 'PO' AND
1834 p_pb_input_header_rec.price_based_on <> 'PRICE_LIST'
1835 THEN
1836 x_return_status := 'E';
1837 FND_MESSAGE.SET_NAME('QP', 'QP_PO_PRICE_BASED_ON');
1838 l_message_text := FND_MESSAGE.GET;
1839 l_price_book_messages_tbl(i).message_code := 'QP_PO_PRICE_BASED_ON';
1840 l_price_book_messages_tbl(i).message_text := l_message_text;
1841 l_price_book_messages_tbl(i).pb_input_header_id :=
1842 p_pb_input_header_rec.pb_input_header_id;
1843 i := i + 1;
1844 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1845 ELSIF p_pb_input_header_rec.price_based_on NOT IN ('PRICE_LIST',
1846 'AGREEMENT', 'BSA')
1847 THEN
1848 x_return_status := 'E';
1849 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1850 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PRICE_BASED_ON');
1851 l_message_text := FND_MESSAGE.GET;
1852 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
1853 l_price_book_messages_tbl(i).message_text := l_message_text;
1854 l_price_book_messages_tbl(i).pb_input_header_id :=
1855 p_pb_input_header_rec.pb_input_header_id;
1856 i := i + 1;
1857 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1858 ELSE --validate pl_agr_bsa_id
1859 IF p_pb_input_header_rec.pl_agr_bsa_id IS NULL THEN
1860 x_return_status := 'E';
1861 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
1862 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PL_AGR_BSA_ID');
1863 l_message_text := FND_MESSAGE.GET;
1864 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
1865 l_price_book_messages_tbl(i).message_text := l_message_text;
1866 l_price_book_messages_tbl(i).pb_input_header_id :=
1867 p_pb_input_header_rec.pb_input_header_id;
1868 i := i + 1;
1869 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1870 ELSE
1871 IF p_pb_input_header_rec.price_based_on = 'PRICE_LIST' THEN
1872 BEGIN
1873 SELECT 1
1874 INTO l_count
1875 FROM qp_list_headers_vl
1876 WHERE list_type_code = 'PRL'
1877 AND nvl(list_source_code, 'X') <> 'BSO'
1878 AND nvl(active_flag, 'N') = 'Y'
1879 AND (global_flag = 'Y' OR
1880 orig_org_id = p_pb_input_header_rec.org_id)
1881 AND source_system_code IN (SELECT application_short_name
1882 FROM qp_pte_source_systems
1883 WHERE pte_code = l_pte_code)
1884 AND list_header_id = p_pb_input_header_rec.pl_agr_bsa_id
1885 AND rownum = 1;
1886 EXCEPTION
1887 WHEN OTHERS THEN
1888 l_count := 0;
1889 END;
1890
1891 IF l_count = 0 THEN
1892 x_return_status := 'E';
1893 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1894 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PL_AGR_BSA_ID');
1895 l_message_text := FND_MESSAGE.GET;
1896 l_price_book_messages_tbl(i).message_code :=
1897 'QP_INVALID_ATTRIBUTE';
1898 l_price_book_messages_tbl(i).message_text := l_message_text;
1899 l_price_book_messages_tbl(i).pb_input_header_id :=
1900 p_pb_input_header_rec.pb_input_header_id;
1901 i := i + 1;
1902 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1903 END IF;
1904
1905 ELSIF p_pb_input_header_rec.price_based_on = 'AGREEMENT' THEN
1906 BEGIN
1907 SELECT 1
1908 INTO l_count
1909 FROM oe_agreements_vl
1910 WHERE agreement_id = p_pb_input_header_rec.pl_agr_bsa_id
1911 AND (sold_to_org_id = -1 OR
1912 sold_to_org_id IN (SELECT cust_account_id
1913 FROM hz_cust_accounts
1914 WHERE party_id =
1915 p_pb_input_header_rec.customer_attr_value
1916 AND cust_account_id =
1917 nvl(p_pb_input_header_rec.cust_account_id, cust_account_id))
1918 )
1919 AND price_list_id IN (SELECT list_header_id
1920 FROM qp_list_headers_vl
1921 WHERE list_type_code IN ('PRL','AGR')
1922 AND nvl(active_flag, 'N') = 'Y'
1923 AND (global_flag = 'Y' OR
1924 orig_org_id = p_pb_input_header_rec.org_id)
1925 AND source_system_code IN
1926 (SELECT application_short_name
1927 FROM qp_pte_source_systems
1928 WHERE pte_code = l_pte_code)
1929 )
1930 AND (trunc(nvl(p_pb_input_header_rec.effective_date, sysdate))
1931 between trunc(nvl(start_date_active,
1932 p_pb_input_header_rec.effective_date))
1933 and trunc(nvl(end_date_active,
1934 p_pb_input_header_rec.effective_date)))
1935 AND rownum = 1;
1936 EXCEPTION
1937 WHEN OTHERS THEN
1938 l_count := 0;
1939 END;
1940
1941 IF l_count = 0 THEN
1942 x_return_status := 'E';
1943 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1944 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PL_AGR_BSA_ID');
1945 l_message_text := FND_MESSAGE.GET;
1946 l_price_book_messages_tbl(i).message_code :=
1947 'QP_INVALID_ATTRIBUTE';
1948 l_price_book_messages_tbl(i).message_text := l_message_text;
1949 l_price_book_messages_tbl(i).pb_input_header_id :=
1950 p_pb_input_header_rec.pb_input_header_id;
1951 i := i + 1;
1952 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1953 END IF;
1954
1955 ELSIF p_pb_input_header_rec.price_based_on = 'BSA' THEN
1956 BEGIN
1957 SELECT 1
1958 INTO l_count
1959 FROM oe_blanket_headers_all a
1960 WHERE a.header_id = p_pb_input_header_rec.pl_agr_bsa_id
1961 AND (a.sold_to_org_id IS NULL OR
1962 a.sold_to_org_id IN (SELECT cust_account_id
1963 FROM hz_cust_accounts
1964 WHERE party_id =
1965 p_pb_input_header_rec.customer_attr_value
1966 AND cust_account_id =
1967 nvl(p_pb_input_header_rec.cust_account_id, cust_account_id))
1968 )
1969 AND a.org_id = p_pb_input_header_rec.org_id
1970 AND EXISTS (SELECT 'x'
1971 FROM qp_qualifiers
1972 WHERE qualifier_context = 'ORDER'
1973 AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE5'
1974 AND qualifier_attr_value = a.header_id)
1975 AND rownum = 1;
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 l_count := 0;
1979 END;
1980
1981 IF l_count = 0 THEN
1982 x_return_status := 'E';
1983 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
1984 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'PL_AGR_BSA_ID');
1985 l_message_text := FND_MESSAGE.GET;
1986 l_price_book_messages_tbl(i).message_code :=
1987 'QP_INVALID_ATTRIBUTE';
1988 l_price_book_messages_tbl(i).message_text := l_message_text;
1989 l_price_book_messages_tbl(i).pb_input_header_id :=
1990 p_pb_input_header_rec.pb_input_header_id;
1991 i := i + 1;
1992 x_return_text := x_return_text || substr(l_message_text, 1, 240);
1993 END IF;
1994
1995 END IF; --if price_based_on = 'PRICE_LIST'
1996
1997 END IF; --If pl_agr_bsa_id is null
1998 END IF; --if price_based_on <> 'PRICE_LIST','AGREEMENT' or 'BSA'
1999 END IF; --if price_based_on is not null
2000
2001 --Check if currency_code is not null
2002 IF p_pb_input_header_rec.currency_code IS NULL THEN
2003 x_return_status := 'E';
2004 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
2005 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'CURRENCY_CODE');
2006 l_message_text := FND_MESSAGE.GET;
2007 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
2008 l_price_book_messages_tbl(i).message_text := l_message_text;
2009 l_price_book_messages_tbl(i).pb_input_header_id :=
2010 p_pb_input_header_rec.pb_input_header_id;
2011 i := i + 1;
2012 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2013 END IF;
2014
2015 --Check if currency_code is valid
2016 IF p_pb_input_header_rec.limit_products_by = 'PRICE_LIST' AND
2017 p_pb_input_header_rec.pl_agr_bsa_id IS NOT NULL
2018 THEN
2019 QP_UTIL_PUB.Validate_Price_list_Curr_code (
2020 l_price_list_id => p_pb_input_header_rec.pl_agr_bsa_id,
2021 l_currency_code => p_pb_input_header_rec.currency_code,
2022 l_pricing_effective_date => p_pb_input_header_rec.effective_date,
2023 l_validate_result => l_valid_currency);
2024
2025 IF l_valid_currency = 'N' THEN
2026 x_return_status := 'E';
2027 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_PRICELIST_N_CURR');
2028 l_message_text := FND_MESSAGE.GET;
2029 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_PRICELIST_N_CURR';
2030 l_price_book_messages_tbl(i).message_text := l_message_text;
2031 l_price_book_messages_tbl(i).pb_input_header_id :=
2032 p_pb_input_header_rec.pb_input_header_id;
2033 i := i + 1;
2034 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2035 END IF;
2036 ELSE
2037 BEGIN
2038 SELECT 1
2039 INTO l_count
2040 FROM fnd_currencies_vl
2041 WHERE currency_flag = 'Y'
2042 AND currency_code = p_pb_input_header_rec.currency_code
2043 AND enabled_flag = 'Y'
2044 AND trunc(NVL(start_date_active,
2045 p_pb_input_header_rec.effective_date)
2046 ) <= trunc(p_pb_input_header_rec.effective_date)
2047 AND trunc(NVL(end_date_active, p_pb_input_header_rec.effective_date))
2048 >= trunc(p_pb_input_header_rec.effective_date)
2049 AND rownum = 1;
2050 EXCEPTION
2051 WHEN OTHERS THEN
2052 l_count := 0;
2053 END;
2054
2055 IF l_count = 0 THEN -- invalid currency_code
2056 x_return_status := 'E';
2057 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
2058 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'CURRENCY_CODE');
2059 l_message_text := FND_MESSAGE.GET;
2060 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
2061 l_price_book_messages_tbl(i).message_text := l_message_text;
2062 l_price_book_messages_tbl(i).pb_input_header_id :=
2063 p_pb_input_header_rec.pb_input_header_id;
2064 i := i + 1;
2065 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2066 END IF;
2067 END IF; -- check if currency_code is valid
2068
2069 --Validate item_quantity
2070 IF p_pb_input_header_rec.item_quantity <= 0 THEN
2071 x_return_status := 'E';
2072 FND_MESSAGE.SET_NAME('QP', 'QP_ITEM_QTY_NEGATIVE_OR_ZERO');
2073 l_message_text := FND_MESSAGE.GET;
2074 l_price_book_messages_tbl(i).message_code := 'QP_ITEM_QTY_NEGATIVE_OR_ZERO';
2075 l_price_book_messages_tbl(i).message_text := l_message_text;
2076 l_price_book_messages_tbl(i).pb_input_header_id :=
2077 p_pb_input_header_rec.pb_input_header_id;
2078 i := i + 1;
2079 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2080 END IF;
2081
2082 --Validate the Input Line Records
2083 IF p_pb_input_lines_tbl.COUNT > 0 THEN
2084 FOR j IN p_pb_input_lines_tbl.FIRST..p_pb_input_lines_tbl.LAST
2085 LOOP
2086
2087 IF p_pb_input_lines_tbl(j).context IS NULL THEN
2088 x_return_status := 'E';
2089 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
2090 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'CONTEXT');
2091 l_message_text := FND_MESSAGE.GET;
2092 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
2093 l_price_book_messages_tbl(i).message_text := l_message_text;
2094 l_price_book_messages_tbl(i).pb_input_header_id :=
2095 p_pb_input_header_rec.pb_input_header_id;
2096 i := i + 1;
2097 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2098 ELSIF p_pb_input_lines_tbl(j).attribute IS NULL THEN
2099 x_return_status := 'E';
2100 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
2101 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'ATTRIBUTE');
2102 l_message_text := FND_MESSAGE.GET;
2103 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
2104 l_price_book_messages_tbl(i).message_text := l_message_text;
2105 l_price_book_messages_tbl(i).pb_input_header_id :=
2106 p_pb_input_header_rec.pb_input_header_id;
2107 i := i + 1;
2108 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2109 ELSIF p_pb_input_lines_tbl(j).attribute_type IS NULL THEN
2110 x_return_status := 'E';
2111 FND_MESSAGE.SET_NAME('QP', 'QP_ATTRIBUTE_REQUIRED');
2112 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'ATTRIBUTE_TYPE');
2113 l_message_text := FND_MESSAGE.GET;
2114 l_price_book_messages_tbl(i).message_code := 'QP_ATTRIBUTE_REQUIRED';
2115 l_price_book_messages_tbl(i).message_text := l_message_text;
2116 l_price_book_messages_tbl(i).pb_input_header_id :=
2117 p_pb_input_header_rec.pb_input_header_id;
2118 i := i + 1;
2119 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2120 ELSIF p_pb_input_lines_tbl(j).attribute_type NOT IN
2121 ('QUALIFIER', 'PRICING_ATTRIBUTE') THEN
2122 x_return_status := 'E';
2123 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
2124 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'ATTRIBUTE_TYPE');
2125 l_message_text := FND_MESSAGE.GET;
2126 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
2127 l_price_book_messages_tbl(i).message_text := l_message_text;
2128 l_price_book_messages_tbl(i).pb_input_header_id :=
2129 p_pb_input_header_rec.pb_input_header_id;
2130 i := i + 1;
2131 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2132 ELSE
2133
2134 --Check if context is valid
2135 BEGIN
2136 SELECT 1
2137 INTO l_count
2138 FROM qp_prc_contexts_b
2139 WHERE prc_context_code = p_pb_input_lines_tbl(j).context
2140 AND prc_context_type = p_pb_input_lines_tbl(j).attribute_type;
2141 EXCEPTION
2142 WHEN OTHERS THEN
2143 l_count := 0;
2144 END;
2145
2146 IF l_count = 0 THEN
2147 x_return_status := 'E';
2148 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
2149 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2150 'CONTEXT'||'-'||substr(p_pb_input_lines_tbl(j).context, 1, 20));
2151 l_message_text := FND_MESSAGE.GET;
2152 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
2153 l_price_book_messages_tbl(i).message_text := l_message_text;
2154 l_price_book_messages_tbl(i).pb_input_header_id :=
2155 p_pb_input_header_rec.pb_input_header_id;
2156 i := i + 1;
2157 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2158 END IF;
2159
2160 --Check if attribute is valid
2161 BEGIN
2162 SELECT 1, nvl(user_valueset_id, seeded_valueset_id)
2163 INTO l_count, l_valueset_id
2164 FROM qp_segments_b s, qp_prc_contexts_b c,
2165 qp_pte_segments ps, qp_pte_request_types_b pr
2166 WHERE s.segment_mapping_column = p_pb_input_lines_tbl(j).attribute
2167 AND s.prc_context_id = c.prc_context_id
2168 AND c.prc_context_code = p_pb_input_lines_tbl(j).context
2169 AND c.prc_context_type = p_pb_input_lines_tbl(j).attribute_type
2170 AND c.prc_context_code <> 'ITEM'
2171 AND NOT ((c.prc_context_code = 'CUSTOMER' AND
2172 s.segment_code = 'PARTY_ID') OR
2173 (c.prc_context_code = 'ASOPARTYINFO' AND
2174 s.segment_code = 'CUSTOMER PARTY') OR
2175 (c.prc_context_code = 'CUSTOMER' AND
2176 s.segment_code = 'SOLD_TO_ORG_ID') OR
2177 (c.prc_context_code = 'MODLIST' AND
2178 s.segment_code = 'PRICE_LIST') OR
2179 (c.prc_context_code = 'CUSTOMER' AND
2180 s.segment_code = 'AGREEMENT_NAME') OR
2181 (c.prc_context_code = 'ORDER' AND
2182 s.segment_code = 'BLANKET_NUMBER') OR
2183 (c.prc_context_code = 'ORDER' AND
2184 s.segment_code = 'BLANKET_HEADER_ID')
2185 )
2186 AND s.segment_id = ps.segment_id
2187 AND ps.pte_code = pr.pte_code
2188 AND pr.request_type_code = l_request_type_code
2189 AND (l_pricing_status = 'I' OR
2190 l_pricing_status = 'S' AND
2191 s.availability_in_basic IN ('Y','F')
2192 )
2193 AND (nvl(ps.user_sourcing_method, ps.seeded_sourcing_method) =
2194 'USER ENTERED'
2195 OR
2196 nvl(ps.user_sourcing_method, ps.seeded_sourcing_method) =
2197 'ATTRIBUTE MAPPING'
2198 AND EXISTS (SELECT 'X'
2199 FROM qp_attribute_sourcing a
2200 WHERE a.request_type_code = pr.request_type_code
2201 AND a.segment_id = s.segment_id
2202 AND a.enabled_flag = 'Y'
2203 AND a.attribute_sourcing_level <> 'LINE'
2204 AND nvl(user_value_string,
2205 seeded_value_string)
2206 LIKE pr.order_level_global_struct||'%'
2207 )
2208 ) ;
2209 EXCEPTION
2210 WHEN OTHERS THEN
2211 l_count := 0;
2212 l_valueset_id := null;
2213 END;
2214
2215 IF l_count = 0 THEN
2216 x_return_status := 'E';
2217 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
2218 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'ATTRIBUTE'||'-'||substr(p_pb_input_lines_tbl(j).attribute, 1, 20));
2219 l_message_text := FND_MESSAGE.GET;
2220 l_price_book_messages_tbl(i).message_code := 'QP_INVALID_ATTRIBUTE';
2221 l_price_book_messages_tbl(i).message_text := l_message_text;
2222 l_price_book_messages_tbl(i).pb_input_header_id :=
2223 p_pb_input_header_rec.pb_input_header_id;
2224 i := i + 1;
2225 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2226
2227 ELSE --If l_count <> 0, that is, attribute is valid
2228
2229 IF l_valueset_id IS NOT NULL THEN
2230
2231 fnd_vset.get_valueset(l_valueset_id, v_valueset_r, v_valueset_dr);
2232 l_datatype := v_valueset_dr.format_type;
2233
2234 IF (v_valueset_r.validation_type = 'I') AND
2235 --Validation type is independent
2236 NOT QP_UTIL.value_exists(l_valueset_id,
2237 p_pb_input_lines_tbl(j).attribute_value)
2238 OR (v_valueset_r.validation_type = 'F') AND
2239 --Validation type is table
2240 NOT QP_UTIL.value_exists_in_table(v_valueset_r.table_info,
2241 p_pb_input_lines_tbl(j).attribute_value, l_id, l_value)
2242 OR ((v_valueset_r.validation_type = 'N') OR
2243 l_datatype in( 'N','X','Y')) AND
2244 --added for handling of dates/number in multilingual envs.
2245 QP_UTIL.validate_num_date(l_datatype,
2246 p_pb_input_lines_tbl(j).attribute_value) <> 0
2247 THEN
2248 x_return_status := 'E';
2249 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_ATTRIBUTE');
2250 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'VALUE'||'-'||substr(p_pb_input_lines_tbl(j).attribute_value, 1, 20));
2251 l_message_text := FND_MESSAGE.GET;
2252 l_price_book_messages_tbl(i).message_code :=
2253 'QP_INVALID_ATTRIBUTE';
2254 l_price_book_messages_tbl(i).message_text := l_message_text;
2255 l_price_book_messages_tbl(i).pb_input_header_id :=
2256 p_pb_input_header_rec.pb_input_header_id;
2257 i := i + 1;
2258 x_return_text := x_return_text || substr(l_message_text,1,240);
2259 END IF; --validation_type = 'I'
2260
2261 END IF; --If l_valueset_id is not null
2262
2263 END IF;--If l_count = 0
2264
2265 END IF;
2266
2267 END LOOP; --Loop over Input Line records
2268 END IF; --If p_pb_input_lines_tbl.count > 0
2269
2270 END IF; --Publish existing price book. No generation.
2271
2272 --reset the application_id back to the original appl id
2273 fnd_global.apps_initialize(l_user_id,
2274 fnd_global.resp_id,
2275 l_resp_appl_id);
2276
2277 IF x_return_status = 'E' THEN
2278 Insert_Price_Book_Messages (l_price_book_messages_tbl);
2279 --commit;
2280 l_price_book_messages_tbl.delete;
2281 END IF;
2282
2283 END Validate_PB_Input_Criteria;
2284
2285
2286 /*****************************************************************************
2287 Wrapper procedure around Validate_PB_Input_Criteria that can be called
2288 directly by get_catalog and private API to validate Price Book input criteria
2289 *****************************************************************************/
2290 PROCEDURE Validate_PB_Inp_Criteria_Wrap(
2291 p_pb_input_header_id IN NUMBER,
2292 x_return_status OUT NOCOPY VARCHAR2,
2293 x_return_text IN OUT NOCOPY VARCHAR2)
2294 IS
2295 l_pb_input_header_rec qp_pb_input_headers_vl%ROWTYPE;
2296 l_price_book_messages_tbl price_book_messages_tbl;
2297 l_pb_input_lines_tbl pb_input_lines_tbl;
2298 l_message_text VARCHAR2(2000);
2299 i NUMBER := 1;
2300
2301 BEGIN
2302 --Fetch the Price Book Input Header record into variable
2303 BEGIN
2304 SELECT *
2305 INTO l_pb_input_header_rec
2306 FROM qp_pb_input_headers_vl
2307 WHERE pb_input_header_id = p_pb_input_header_id;
2308 EXCEPTION
2309 WHEN OTHERS THEN
2310 x_return_status := 'E';
2311 FND_MESSAGE.SET_NAME('QP', 'QP_INPUT_REC_NOT_FOUND');
2312 l_message_text := FND_MESSAGE.GET;
2313 l_price_book_messages_tbl(i).message_code :=
2314 'QP_INPUT_REC_NOT_FOUND';
2315 l_price_book_messages_tbl(i).message_text := l_message_text;
2316 l_price_book_messages_tbl(i).pb_input_header_id := p_pb_input_header_id;
2317 x_return_text := x_return_text || substr(l_message_text, 1, 240);
2318 END;
2319
2320 IF x_return_status = 'E' THEN
2321 --Insert error associated with the retrieving the price book input criteria
2322 Insert_Price_Book_Messages (l_price_book_messages_tbl);
2323 l_price_book_messages_tbl.delete;
2324 --commit;
2325 RETURN;
2326 END IF;
2327
2328 --Fetch the Price Book Input Lines into a table of records
2329 SELECT * BULK COLLECT
2330 INTO l_pb_input_lines_tbl
2331 FROM qp_pb_input_lines
2332 WHERE pb_input_header_id = p_pb_input_header_id;
2333
2334 --Perform validation of input criteria
2335 Validate_PB_Input_Criteria(p_pb_input_header_rec => l_pb_input_header_rec,
2336 p_pb_input_lines_tbl => l_pb_input_lines_tbl,
2337 x_return_status => x_return_status,
2338 x_return_text => x_return_text);
2339
2340 END Validate_PB_Inp_Criteria_Wrap;
2341
2342
2343 /******************************************************************************
2344 Procedure to insert Price Book Header info into qp_price_book_headers_b
2345 and _tl tables.
2346 ******************************************************************************/
2347 PROCEDURE Insert_Price_Book_Header (
2348 p_pb_input_header_rec IN qp_pb_input_headers_vl%ROWTYPE,
2349 x_price_book_header_id OUT NOCOPY NUMBER)
2350 IS
2351 l_application_id NUMBER;
2352 l_request_type_code VARCHAR2(30);
2353 l_user_id NUMBER;
2354 l_price_book_header_id NUMBER;
2355 l_count NUMBER;
2356
2357 BEGIN
2358
2359 l_user_id := fnd_global.user_id;
2360
2361 /*Not required since request type code is already available in input header rec
2362 --Get application id for the appl corresponding to pricing perspective
2363 BEGIN
2364 SELECT application_id
2365 INTO l_application_id
2366 FROM fnd_application
2367 WHERE application_short_name =
2368 p_pb_input_header_rec.pricing_perspective_code;
2369 EXCEPTION
2370 l_application_id := NULL;
2371 END;
2372
2373 l_request_type_code := fnd_profile.value_specific(
2374 name => 'QP_PRICING_PERSPECTIVE_REQUEST_TYPE',
2375 application_id => l_application_id);
2376 */
2377
2378 --Check if the price book already exists for an org that is not accessible
2379 BEGIN
2380 SELECT 1
2381 INTO l_count
2382 FROM qp_price_book_headers_all_b b, qp_price_book_headers_tl t
2383 WHERE b.price_book_header_id = t.price_book_header_id
2384 AND t.language = userenv('LANG')
2385 AND b.price_book_type_code = p_pb_input_header_rec.price_book_type_code
2386 AND b.customer_id = p_pb_input_header_rec.customer_attr_value
2387 AND t.price_book_name = p_pb_input_header_rec.price_book_name
2388 AND rownum = 1;
2389 EXCEPTION
2390 WHEN OTHERS THEN
2391 l_count := 0;
2392 END;
2393
2394 IF l_count > 0 THEN
2395 RAISE FND_API.G_EXC_ERROR;
2396 END IF;
2397
2398 INSERT INTO qp_price_book_headers_all_b (
2399 price_book_header_id,
2400 price_book_type_code,
2401 currency_code,
2402 effective_date,
2403 org_id,
2404 customer_id,
2405 cust_account_id,
2406 item_category,
2407 price_based_on,
2408 pl_agr_bsa_id,
2409 pricing_perspective_code,
2410 item_quantity,
2411 request_id,
2412 request_type_code,
2413 pb_input_header_id,
2414 creation_date,
2415 created_by,
2416 last_update_date,
2417 last_updated_by,
2418 last_update_login
2419 )
2420 VALUES
2421 (qp_price_book_headers_all_b_s.nextval,
2422 p_pb_input_header_rec.price_book_type_code,
2423 p_pb_input_header_rec.currency_code,
2424 p_pb_input_header_rec.effective_date,
2425 p_pb_input_header_rec.org_id,
2426 p_pb_input_header_rec.customer_attr_value,
2427 p_pb_input_header_rec.cust_account_id,
2428 decode(p_pb_input_header_rec.product_attribute,
2429 'PRICING_ATTRIBUTE2', p_pb_input_header_rec.product_attr_value,
2430 null),
2431 p_pb_input_header_rec.price_based_on,
2432 p_pb_input_header_rec.pl_agr_bsa_id,
2433 p_pb_input_header_rec.pricing_perspective_code,
2434 p_pb_input_header_rec.item_quantity,
2435 null, --Will be updated with the child request id later
2436 p_pb_input_header_rec.request_type_code,
2437 p_pb_input_header_rec.pb_input_header_id,
2438 sysdate,
2439 l_user_id,
2440 sysdate,
2441 l_user_id,
2442 fnd_global.conc_login_id
2443 ) RETURNING price_book_header_id INTO l_price_book_header_id;
2444
2445 INSERT INTO qp_price_book_headers_tl (
2446 price_book_header_id,
2447 price_book_name,
2448 pl_agr_bsa_name,
2449 creation_date,
2450 created_by,
2451 last_update_date,
2452 last_updated_by,
2453 last_update_login,
2454 language,
2455 source_lang
2456 )
2457 SELECT
2458 l_price_book_header_id,
2459 p_pb_input_header_rec.price_book_name,
2460 p_pb_input_header_rec.pl_agr_bsa_name,
2461 sysdate,
2462 l_user_id,
2463 sysdate,
2464 l_user_id,
2465 fnd_global.conc_login_id,
2466 l.language_code,
2467 userenv('LANG')
2468 FROM FND_LANGUAGES L
2469 WHERE L.INSTALLED_FLAG in ('I','B')
2470 AND NOT EXISTS (SELECT NULL
2471 FROM qp_price_book_headers_tl T
2472 WHERE t.price_book_header_id =
2473 l_price_book_header_id
2474 AND t.language = l.language_code);
2475
2476 x_price_book_header_id := l_price_book_header_id;
2477
2478 END Insert_Price_Book_Header;
2479
2480 FUNCTION value_to_meaning( p_code IN VARCHAR2,p_type IN VARCHAR2) RETURN VARCHAR2
2481 IS
2482 l_meaning VARCHAR2(80) := Null;
2483 BEGIN
2484 select meaning into l_meaning from qp_lookups where lookup_code = p_code and
2485 lookup_type = p_type;
2486 return l_meaning;
2487 exception
2488 when no_data_found then return NULL;
2489 end value_to_meaning;
2490
2491 FUNCTION get_attribute_name(p_context_code in varchar2,p_attribute_code in
2492 varchar2,p_attribute_type in varchar2)
2493 return varchar2 is
2494 l_attribute_name varchar2(80) := Null;
2495 begin
2496 select nvl(s.user_segment_name,s.seeded_segment_name) into l_attribute_name from qp_segments_v
2497 s,qp_prc_contexts_v p where s.segment_mapping_column = p_attribute_code and
2498 s.prc_context_id = p.prc_context_id and p.prc_context_code = p_context_code and
2499 p.prc_context_type = p_attribute_type;
2500 return l_attribute_name;
2501 exception
2502 when no_data_found then return NULL;
2503 end;
2504
2505 FUNCTION get_product_value(p_attribute_code in varchar2,p_attribute_value_code in
2506 varchar2,p_org_id in varchar2)
2507 return varchar2 is
2508 l_attribute_value varchar2(40) := Null;
2509 begin
2510 if (p_attribute_code = 'PRICING_ATTRIBUTE1') then
2511 select concatenated_segments
2512 into l_attribute_value
2513 from mtl_system_items_kfv
2514 where inventory_item_id = to_number(p_attribute_value_code) and rownum = 1;
2515
2516 elsif (p_attribute_code = 'PRICING_ATTRIBUTE2') then
2517 begin
2518 select concat_cat_parentage
2519 into l_attribute_value
2520 from eni_prod_den_hrchy_parents_v
2521 where category_id = to_number(p_attribute_value_code) and rownum = 1;
2522 exception when others then
2523 select concatenated_segments
2524 into l_attribute_value
2525 from mtl_categories_kfv
2526 where category_id = to_number(p_attribute_value_code);
2527 return l_attribute_value;
2528 end;
2529
2530 end if;
2531 return l_attribute_value;
2532 exception
2533 when no_data_found then return null;
2534 end;
2535
2536 FUNCTION get_customer_value(p_attribute_code in varchar2, p_attribute_value_code in
2537 varchar2) return varchar2 is
2538 l_customer_name varchar2(360) := Null;
2539 begin
2540 if (p_attribute_code = 'QUALIFIER_ATTRIBUTE2') then
2541 IF to_number(p_attribute_value_code) = -1 THEN
2542 RETURN NULL;
2543 ELSE
2544 BEGIN
2545 select party_name into l_customer_name
2546 from hz_parties
2547 where party_id = to_number(p_attribute_value_code);
2548 END;
2549 END IF;
2550 elsif (p_attribute_code = 'QUALIFIER_ATTRIBUTE1') then
2551 select meaning into l_customer_name from ar_lookups where lookup_code=
2552 p_attribute_value_code and lookup_type= 'CUSTOMER CLASS';
2553 end if;
2554 return l_customer_name;
2555 exception
2556 when no_data_found then return null;
2557 end;
2558
2559
2560 FUNCTION get_customer_name(p_customer_id in varchar2)
2561 return varchar2 is
2562 l_customer_name varchar2(360) := Null;
2563 begin
2564 IF TO_NUMBER(p_customer_id) = -1 THEN
2565 RETURN null;
2566 else
2567 begin
2568 SELECT party_name INTO l_customer_name
2569 FROM hz_parties where party_id = to_number(p_customer_id);
2570 RETURN l_customer_name;
2571 EXCEPTION
2572 WHEN no_data_found THEN RETURN null;
2573 end;
2574 end if;
2575 end;
2576
2577 FUNCTION get_operating_unit(p_orgid in number) return varchar2
2578 is
2579 l_operating_unit varchar2(240) :=Null;
2580 begin
2581 select name into l_operating_unit
2582 from HR_ALL_ORGANIZATION_UNITS_TL
2583 WHERE ORGANIZATION_ID = p_orgid
2584 AND LANGUAGE = userenv('LANG');
2585 return l_operating_unit;
2586 exception
2587 when no_data_found then return null;
2588 end;
2589
2590 FUNCTION get_context_name (p_context in varchar2,p_attribute_type in varchar2) return varchar2
2591 is
2592 l_context_name varchar2(240) :=Null;
2593 begin
2594 select nvl(user_prc_context_name,seeded_prc_context_name) into l_context_name from qp_prc_contexts_v where prc_context_code =
2595 p_context and prc_context_type = p_attribute_type;
2596 return l_context_name;
2597 exception
2598 when no_data_found then return null;
2599 end;
2600
2601 FUNCTION get_item_description(p_item_number in number,p_pb_header_id in number)
2602 return varchar2 is
2603 l_item_description varchar2(240) := Null;
2604 begin
2605 select description into l_item_description from mtl_system_items_tl where
2606 language = userenv('LANG') and inventory_item_id = p_item_number and rownum =1;
2607 return l_item_description;
2608 exception
2609 when no_data_found then return null;
2610 end;
2611
2612 FUNCTION get_item_category (p_item_category in number) return varchar2 is
2613 l_item_category varchar2(240) :=null;
2614 begin
2615 begin
2616 select concat_cat_parentage into l_item_category from eni_prod_den_hrchy_parents_v
2617 where category_id = p_item_category and rownum = 1;
2618 return l_item_category;
2619 exception when others then
2620 select concatenated_segments into l_item_category from mtl_categories_kfv where
2621 category_id = p_item_category ;
2622 return l_item_category;
2623 end;
2624 exception
2625 when no_data_found then return null;
2626 end;
2627
2628 FUNCTION get_item_cat_description (p_item_category in number) return varchar2 is
2629 l_item_cat_description varchar2(240) := Null;
2630 begin
2631 begin
2632 select category_desc into l_item_cat_description from eni_prod_den_hrchy_parents_v where
2633 category_id = p_item_category and rownum = 1;
2634 return l_item_cat_description;
2635 exception when others then
2636 select description into l_item_cat_description from mtl_categories_kfv where
2637 category_id = p_item_category;
2638 return l_item_cat_description;
2639 end ;
2640 exception
2641 when no_data_found then return null;
2642 end;
2643
2644 FUNCTION get_item_number(p_item_number in number, p_pb_header_id in number)
2645 return varchar2 is
2646 l_item_number varchar2(40) := Null;
2647 begin
2648 select concatenated_segments into l_item_number from mtl_system_items_kfv where
2649 inventory_item_id = p_item_number and rownum = 1;
2650 return l_item_number;
2651 exception
2652 when no_data_found then return null;
2653 end;
2654
2655 /*** Function to get customer_item_number given the inventory_item_id ***/
2656 FUNCTION get_customer_number (p_item_number in number,p_pb_header_id in number)
2657 RETURN varchar2
2658 IS
2659 l_customer_item_number VARCHAR2(50) := null;
2660 l_cust_account_id NUMBER := null;
2661 l_master_org NUMBER;
2662 BEGIN
2663 BEGIN
2664 SELECT cust_account_id
2665 INTO l_cust_account_id
2666 FROM qp_price_book_headers_b
2667 WHERE price_book_header_id = p_pb_header_id;
2668 EXCEPTION
2669 WHEN OTHERS THEN
2670 l_cust_account_id := null;
2671 END;
2672
2673 IF l_cust_account_id IS NULL THEN
2674 RETURN null;
2675 END IF;
2676
2677 --Getting the master_organization_id for the Inventory Org Id.
2678 BEGIN
2679 SELECT master_organization_id
2680 INTO l_master_org
2681 FROM mtl_parameters
2682 WHERE organization_id = QP_UTIL.Get_Item_Validation_Org;
2683 END;
2684
2685 --Per M. Antyakula of INV team specifying master org filter as well in
2686 --the query below will ensure that a unique customer_item_number is selected.
2687 BEGIN
2688 SELECT ci.customer_item_number
2689 INTO l_customer_item_number
2690 FROM mtl_customer_item_xrefs xref, mtl_customer_items_all_v ci
2691 WHERE xref.inventory_item_id = p_item_number
2692 AND xref.master_organization_id = l_master_org
2693 AND xref.inactive_flag = 'N'
2694 AND ci.customer_item_id = xref.customer_item_id
2695 AND ci.customer_id = l_cust_account_id
2696 AND ci.address_id is null
2697 AND ci.customer_category_code is null
2698 AND ci.item_definition_level = 1;
2699 EXCEPTION
2700 WHEN OTHERS THEN
2701 l_customer_item_number := null;
2702 END;
2703
2704 RETURN l_customer_item_number;
2705 END get_customer_number;
2706
2707 FUNCTION get_customer_item_desc (p_item_number in number,p_pb_header_id in number)
2708 RETURN varchar2
2709 IS
2710 l_customer_item_desc VARCHAR2(240) := null;
2711 l_cust_account_id NUMBER := null;
2712 l_master_org NUMBER;
2713 BEGIN
2714 BEGIN
2715 SELECT cust_account_id
2716 INTO l_cust_account_id
2717 FROM qp_price_book_headers_b
2718 WHERE price_book_header_id = p_pb_header_id;
2719 EXCEPTION
2720 WHEN OTHERS THEN
2721 l_cust_account_id := null;
2722 END;
2723
2724 IF l_cust_account_id IS NULL THEN
2725 RETURN null;
2726 END IF;
2727
2728 --Getting the master_organization_id for the Inventory Org Id.
2729 BEGIN
2730 SELECT master_organization_id
2731 INTO l_master_org
2732 FROM mtl_parameters
2733 WHERE organization_id = QP_UTIL.Get_Item_Validation_Org;
2734 END;
2735
2736 --Per M. Antyakula of INV team specifying master org filter as well in
2737 --the query below will ensure that a unique record is selected.
2738 -- Replaceing mtl_customer_items_all_v with the view definition for
2739 -- fix for sql id 17903884
2740 BEGIN
2741 SELECT MCI.CUSTOMER_ITEM_DESC
2742 INTO l_customer_item_desc
2743 FROM MTL_CUSTOMER_ITEMS MCI,
2744 HZ_PARTIES HZP, HZ_CUST_ACCOUNTS HZC, (SELECT LOC.COUNTRY,
2745 ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
2746 FROM HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2747 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
2748 HZ_PARTY_SITES PARTY_SITE
2749 WHERE ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
2750 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2751 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
2752 AND NVL(ACCT_SITE.ORG_ID,- 99) = NVL(LOC_ASSIGN.ORG_ID,- 99)
2753 AND NVL(ACCT_SITE.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1, 10))),- 99)) =
2754 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1, 1),' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'), 1,10))),- 99)) RAD,
2755 MTL_COMMODITY_CODES MCC,
2756 MTL_CUSTOMER_ITEMS MCIM, ( SELECT B.TERRITORY_CODE
2757 FROM FND_TERRITORIES_TL T, FND_TERRITORIES B
2758 WHERE B.TERRITORY_CODE = T.TERRITORY_CODE
2759 AND T.LANGUAGE = USERENV('LANG')) TERR,
2760 AR_LOOKUPS ARL,
2761 MFG_LOOKUPS MFL, mtl_customer_item_xrefs xref
2762 WHERE MCI.CUSTOMER_ID = HZC.CUST_ACCOUNT_ID
2763 AND MCI.ADDRESS_ID = RAD.ADDRESS_ID(+)
2764 AND MCI.COMMODITY_CODE_ID = MCC.COMMODITY_CODE_ID
2765 AND MCI.MODEL_CUSTOMER_ITEM_ID = MCIM.CUSTOMER_ITEM_ID(+)
2766 AND TERR.TERRITORY_CODE(+) = RAD.COUNTRY
2767 AND MCI.CUSTOMER_CATEGORY_CODE = ARL.LOOKUP_CODE(+)
2768 AND ARL.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY'
2769 AND MCI.ITEM_DEFINITION_LEVEL = MFL.LOOKUP_CODE
2770 AND MFL.LOOKUP_TYPE = 'INV_ITEM_DEFINITION_LEVEL'
2771 AND HZC.PARTY_ID = HZP.PARTY_ID
2772 AND xref.inventory_item_id = p_item_number
2773 AND xref.master_organization_id = l_master_org
2774 AND xref.inactive_flag = 'N'
2775 AND mci.customer_item_id = xref.customer_item_id
2776 AND mci.customer_id = l_cust_account_id
2777 AND mci.address_id is null
2778 AND mci.customer_category_code is null
2779 AND mci.item_definition_level = 1;
2780
2781 EXCEPTION
2782 WHEN OTHERS THEN
2783 l_customer_item_desc := null;
2784 END;
2785
2786 RETURN l_customer_item_desc;
2787 END get_customer_item_desc;
2788
2789
2790 FUNCTION get_attribute_value_common(p_attribute_type in varchar2,p_context in
2791 varchar2,p_attribute in varchar2,p_attribute_value in
2792 varchar2,p_comparison_operator varchar2 default'=')
2793 return varchar2 is
2794 l_attribute_value varchar2(30) := Null;
2795 begin
2796 if p_attribute_type = 'QUALIFIER' then
2797 l_attribute_value := QP_UTIL.Get_Attribute_Value('QP_ATTR_DEFNS_QUALIFIER',
2798 p_context,
2799 p_attribute,
2800 p_attribute_value,
2801 p_comparison_operator);
2802 elsif p_attribute_type = 'PRICING_ATTRIBUTE' then
2803 if p_context = 'ITEM' and p_attribute = 'PRICING_ATTRIBUTE2' then
2804 begin
2805 select concat_cat_parentage
2806 into l_attribute_value
2807 from eni_prod_den_hrchy_parents_v
2808 where category_id = to_number(p_attribute_value) and rownum = 1;
2809 exception when others then
2810 select concatenated_segments
2811 into l_attribute_value
2812 from mtl_categories_kfv
2813 where category_id = to_number(p_attribute_value);
2814 return l_attribute_value;
2815 end;
2816 else
2817 l_attribute_value := QP_UTIL.Get_Attribute_Value('QP_ATTR_DEFNS_PRICING',
2818 p_context,
2819 p_attribute,
2820 p_attribute_value,
2821 p_comparison_operator);
2822 end if;
2823 end if;
2824 return l_attribute_value;
2825 exception
2826 when others then return null;
2827 end;
2828
2829 FUNCTION get_list_name(p_list_header_id in number)
2830 return varchar2 is
2831 l_list_name varchar2(240) := Null;
2832 begin
2833 select name into l_list_name from qp_list_headers_tl
2834 where list_header_id = p_list_header_id and
2835 language = userenv('LANG');
2836 return l_list_name;
2837 exception
2838 when no_data_found then return null;
2839 end;
2840
2841 PROCEDURE Delete_PriceBook_Info(p_price_book_header_id in number)
2842 is
2843
2844 l_pb_input_header_id number := null;
2845 l_price_book_name varchar2(2000) := null;
2846 d_pb_input_header_id number := null;
2847 d_price_book_header_id number :=null;
2848 l_customer_id number := null;
2849 l_document_id number := null;
2850 d_document_id number := null;
2851
2852 BEGIN
2853
2854 BEGIN
2855 SELECT CUSTOMER_ID,PB_INPUT_HEADER_ID,PRICE_BOOK_NAME,DOCUMENT_ID
2856 into l_customer_id,l_pb_input_header_id ,l_price_book_name,l_document_id
2857 from QP_PRICE_BOOK_HEADERS_V
2858 WHERE PRICE_BOOK_HEADER_ID = p_price_book_header_id;
2859 EXCEPTION
2860 when no_data_found then
2861 null;
2862 END;
2863
2864 BEGIN
2865 SELECT PB_INPUT_HEADER_ID,PRICE_BOOK_HEADER_ID,DOCUMENT_ID
2866 into d_pb_input_header_id,d_price_book_header_id, d_document_id
2867 FROM QP_PRICE_BOOK_HEADERS_V
2868 WHERE PRICE_BOOK_HEADER_ID <> p_price_book_header_id AND
2869 PRICE_BOOK_NAME = l_price_book_name and
2870 PRICE_BOOK_TYPE_CODE = 'D' and
2871 CUSTOMER_ID = l_customer_id;
2872 EXCEPTION
2873 when no_data_found then
2874 null;
2875 END;
2876
2877 -- Commiting after each delete as it will give rollback segment error if the data is huge
2878
2879 DELETE FROM QP_PRICE_BOOK_ATTRIBUTES WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
2880 IF SQL%FOUND THEN
2881 COMMIT;
2882 END IF;
2883
2884 DELETE FROM QP_PRICE_BOOK_BREAK_LINES WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
2885 IF SQL%FOUND THEN
2886 COMMIT;
2887 END IF;
2888
2889 DELETE FROM QP_PRICE_BOOK_LINE_DETAILS WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
2890 IF SQL%FOUND THEN
2891 COMMIT;
2892 END IF;
2893
2894 DELETE FROM QP_PRICE_BOOK_LINES WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
2895 IF SQL%FOUND THEN
2896 COMMIT;
2897 END IF;
2898
2899 DELETE FROM QP_PRICE_BOOK_HEADERS_TL WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
2900 IF SQL%FOUND THEN
2901 COMMIT;
2902 END IF;
2903
2904 DELETE FROM QP_PRICE_BOOK_HEADERS_B WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
2905 IF SQL%FOUND THEN
2906 COMMIT;
2907 END IF;
2908
2909 DELETE FROM QP_PB_INPUT_LINES WHERE PB_INPUT_HEADER_ID in (l_pb_input_header_id, d_pb_input_header_id);
2910 IF SQL%FOUND THEN
2911 COMMIT;
2912 END IF;
2913
2914 DELETE FROM QP_PB_INPUT_HEADERS_TL WHERE PB_INPUT_HEADER_ID in (l_pb_input_header_id, d_pb_input_header_id);
2915 IF SQL%FOUND THEN
2916 COMMIT;
2917 END IF;
2918
2919 DELETE FROM QP_PB_INPUT_HEADERS_B WHERE PB_INPUT_HEADER_ID in (l_pb_input_header_id, d_pb_input_header_id);
2920 IF SQL%FOUND THEN
2921 COMMIT;
2922 END IF;
2923
2924 DELETE FROM QP_PRICE_BOOK_MESSAGES WHERE PRICE_BOOK_HEADER_ID in (p_price_book_header_id,d_price_book_header_id);
2925 IF SQL%FOUND THEN
2926 COMMIT;
2927 END IF;
2928
2929
2930 DELETE FROM QP_DOCUMENTS WHERE DOCUMENT_ID in (l_document_id,d_document_id);
2931 IF SQL%FOUND THEN
2932 COMMIT;
2933 END IF;
2934
2935 END;
2936
2937
2938 PROCEDURE Delete_Input_Criteria(p_pb_input_header_id in number)
2939 is
2940
2941 BEGIN
2942 --[prarasto]Deleting the Input Header and Lines is not required as the same header_id will be
2943 --updated in case of an error. Commenting the code.
2944 /*
2945 -- Commiting after each delete as it will give rollback segment error if the data is huge
2946 DELETE FROM QP_PB_INPUT_LINES WHERE PB_INPUT_HEADER_ID in (p_pb_input_header_id);
2947 IF SQL%FOUND THEN
2948 COMMIT;
2949 END IF;
2950
2951 DELETE FROM QP_PB_INPUT_HEADERS_TL WHERE PB_INPUT_HEADER_ID in (p_pb_input_header_id);
2952 IF SQL%FOUND THEN
2953 COMMIT;
2954 END IF;
2955
2956 DELETE FROM QP_PB_INPUT_HEADERS_B WHERE PB_INPUT_HEADER_ID in (p_pb_input_header_id);
2957 IF SQL%FOUND THEN
2958 COMMIT;
2959 END IF;
2960 */
2961 DELETE FROM QP_PRICE_BOOK_MESSAGES WHERE PB_INPUT_HEADER_ID in (p_pb_input_header_id);
2962 IF SQL%FOUND THEN
2963 --COMMIT;
2964 null; --Commit will be done only after successful insertion.
2965 END IF;
2966
2967 END;
2968
2969
2970 FUNCTION get_currency_name (p_currency_code in varchar2) return varchar2
2971 is
2972 l_currency_name varchar2(240) := null;
2973 begin
2974 select name into l_currency_name from fnd_currencies_vl where currency_code =
2975 p_currency_code;
2976 return l_currency_name;
2977 exception
2978 when no_data_found then return null;
2979 end;
2980
2981 ---------------------------------------------------------
2982
2983 PROCEDURE INSERT_PB_TL_RECORDS
2984 (
2985 p_pb_input_header_id IN VARCHAR2,
2986 p_price_book_name IN VARCHAR2,
2987 p_pl_agr_bsa_name IN VARCHAR2
2988 )
2989 IS
2990 BEGIN
2991 INSERT INTO QP_PB_INPUT_HEADERS_TL (
2992 PB_INPUT_HEADER_ID,
2993 CREATION_DATE,
2994 CREATED_BY,
2995 LAST_UPDATE_DATE,
2996 LAST_UPDATED_BY,
2997 LAST_UPDATE_LOGIN,
2998 PRICE_BOOK_NAME,
2999 PL_AGR_BSA_NAME,
3000 LANGUAGE,
3001 SOURCE_LANG
3002 ) SELECT
3003 p_pb_input_header_id,
3004 PBIH.CREATION_DATE,
3005 PBIH.CREATED_BY,
3006 PBIH.LAST_UPDATE_DATE,
3007 PBIH.LAST_UPDATED_BY,
3008 PBIH.LAST_UPDATE_LOGIN,
3009 p_price_book_name,
3010 p_pl_agr_bsa_name,
3011 L.LANGUAGE_CODE,
3012 userenv('LANG')
3013 FROM FND_LANGUAGES L, QP_PB_INPUT_HEADERS_B PBIH
3014 WHERE L.INSTALLED_FLAG in ('I', 'B')
3015 AND PBIH.PB_INPUT_HEADER_ID = p_pb_input_header_id
3016 AND NOT EXISTS
3017 (SELECT NULL
3018 FROM QP_PB_INPUT_HEADERS_TL T
3019 WHERE T.PB_INPUT_HEADER_ID = p_pb_input_header_id
3020 AND T.LANGUAGE = L.LANGUAGE_CODE);
3021 END INSERT_PB_TL_RECORDS;
3022
3023 PROCEDURE CATGI_HEADER_CONVERSIONS
3024 (
3025 p_org_id IN NUMBER,
3026 p_pricing_effective_date IN DATE,
3027 p_limit_products_by_code IN VARCHAR2,
3028 p_price_based_on_code IN VARCHAR2,
3029 p_customer_id IN VARCHAR2,
3030 p_item_number IN VARCHAR2,
3031 p_item_number_cust IN VARCHAR2,
3032 p_item_id IN VARCHAR2,
3033 p_item_category_name IN VARCHAR2,
3034 p_item_category_id IN VARCHAR2,
3035 p_price_list_name IN VARCHAR2,
3036 p_price_list_id IN VARCHAR2,
3037 p_agreement_name IN VARCHAR2,
3038 p_agreement_id IN VARCHAR2,
3039 p_bsa_name IN VARCHAR2,
3040 p_bsa_id IN VARCHAR2,
3041 x_prod_attr_value OUT NOCOPY VARCHAR2,
3042 x_pl_agr_bsa_id OUT NOCOPY VARCHAR2,
3043 x_pl_agr_bsa_name OUT NOCOPY VARCHAR2,
3044 x_return_status OUT NOCOPY VARCHAR2,
3045 x_return_text OUT NOCOPY VARCHAR2
3046 )
3047 IS
3048 BEGIN
3049
3050 --[julin] x_prod_attr_value - item number, item category
3051 IF (p_limit_products_by_code = 'ITEM') THEN
3052 IF (p_item_id is not null) THEN
3053 x_prod_attr_value := p_item_id;
3054 ELSIF (p_item_number is not null) THEN
3055 BEGIN
3056 SELECT inventory_item_id
3057 INTO x_prod_attr_value
3058 FROM mtl_system_items_vl
3059 WHERE concatenated_segments = p_item_number
3060 AND organization_id = p_org_id;
3061 EXCEPTION
3062 WHEN NO_DATA_FOUND THEN
3063 x_prod_attr_value := 'ITEM_LOOKUP_FAILED';
3064 x_return_status := FND_API.G_RET_STS_ERROR;
3065 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ITEM_NOT_FOUND');
3066 FND_MESSAGE.SET_TOKEN('ITEM_NUMBER', p_item_number);
3067 x_return_text := FND_MESSAGE.GET;
3068 WHEN TOO_MANY_ROWS THEN
3069 x_prod_attr_value := 'ITEM_LOOKUP_FAILED';
3070 x_return_status := FND_API.G_RET_STS_ERROR;
3071 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ITEM_MULTI_FOUND');
3072 FND_MESSAGE.SET_TOKEN('ITEM_NUMBER', p_item_number);
3073 x_return_text := FND_MESSAGE.GET;
3074 END;
3075 /*
3076 ELSIF (p_item_number_cust is not null) THEN
3077 BEGIN
3078 SELECT inventory_item_id
3079 INTO x_prod_attr_value
3080 FROM (
3081 SELECT Inventory_Item_Id
3082 FROM MTL_CUSTOMER_ITEM_XREFS x, MTL_CUSTOMER_ITEMS i
3083 WHERE i.customer_id = p_customer_id
3084 AND i.customer_item_number = p_item_number_cust
3085 AND i.Customer_Item_Id = x.customer_item_id
3086 AND x.Master_Organization_Id =
3087 (SELECT Master_Organization_Id
3088 FROM MTL_PARAMETERS
3089 WHERE Organization_Id = p_org_id)
3090 ORDER BY Preference_Number ASC)
3091 WHERE rownum = 1;
3092 EXCEPTION
3093 WHEN NO_DATA_FOUND Then
3094 x_prod_attr_value := 'CUST_ITEM_NUM_LOOKUP_FAILED';
3095 END;
3096 */
3097 END IF;
3098 ELSIF (p_limit_products_by_code = 'ITEM_CATEGORY') THEN
3099 IF (p_item_category_id is not null) THEN
3100 x_prod_attr_value := p_item_category_id;
3101 ELSIF (p_item_category_name is not null) THEN
3102 BEGIN
3103 SELECT distinct category_id
3104 INTO x_prod_attr_value
3105 FROM qp_item_categories_v
3106 WHERE category_name = p_item_category_name;
3107 EXCEPTION
3108 WHEN NO_DATA_FOUND THEN
3109 x_prod_attr_value := 'CATEGORY_LOOKUP_FAILED';
3110 x_return_status := FND_API.G_RET_STS_ERROR;
3111 FND_MESSAGE.SET_NAME('QP', 'QP_XML_CATEGORY_NOT_FOUND');
3112 FND_MESSAGE.SET_TOKEN('CATEGORY_NAME', p_item_category_name);
3113 x_return_text := FND_MESSAGE.GET;
3114 WHEN TOO_MANY_ROWS THEN
3115 x_prod_attr_value := 'CATEGORY_LOOKUP_FAILED';
3116 x_return_status := FND_API.G_RET_STS_ERROR;
3117 FND_MESSAGE.SET_NAME('QP', 'QP_XML_CATEGORY_MULTI_FOUND');
3118 FND_MESSAGE.SET_TOKEN('CATEGORY_NAME', p_item_category_name);
3119 x_return_text := FND_MESSAGE.GET;
3120 END;
3121 END IF;
3122 ELSIF (p_limit_products_by_code = 'ALL_ITEMS') THEN
3123 x_prod_attr_value := 'ALL';
3124 END IF;
3125
3126 --[julin] x_pl_agr_bsa_id - price list, agreement, bsa
3127 IF (p_price_based_on_code = 'PRICE_LIST') THEN
3128 IF (p_price_list_id is not null) THEN
3129 x_pl_agr_bsa_id := p_price_list_id;
3130 ELSIF (p_price_list_name is not null) THEN
3131 x_pl_agr_bsa_id := GET_PRICE_LIST_ID(p_price_list_name);
3132 IF x_pl_agr_bsa_id is null THEN
3133 x_return_status := FND_API.G_RET_STS_ERROR;
3134 FND_MESSAGE.SET_NAME('QP', 'QP_XML_PRICELIST_NOT_FOUND');
3135 FND_MESSAGE.SET_TOKEN('PRICELIST_NAME', p_price_list_name);
3136 x_return_text := FND_MESSAGE.GET;
3137 END IF;
3138 x_pl_agr_bsa_name := p_price_list_name;
3139 END IF;
3140 ELSIF (p_price_based_on_code = 'AGREEMENT') THEN
3141 IF (p_agreement_id is not null) THEN
3142 x_pl_agr_bsa_id := p_agreement_id;
3143 ELSIF (p_agreement_name is not null) THEN
3144 x_pl_agr_bsa_id := GET_AGREEMENT_ID(p_agreement_name, p_pricing_effective_date);
3145 IF x_pl_agr_bsa_id is null THEN
3146 x_return_status := FND_API.G_RET_STS_ERROR;
3147 FND_MESSAGE.SET_NAME('QP', 'QP_XML_AGREEMENT_NOT_FOUND');
3148 FND_MESSAGE.SET_TOKEN('AGREEMENT_NAME', p_agreement_name);
3149 FND_MESSAGE.SET_TOKEN('EFFECTIVE_DATE', p_pricing_effective_date);
3150 x_return_text := FND_MESSAGE.GET;
3151 END IF;
3152 x_pl_agr_bsa_name := p_agreement_name;
3153 END IF;
3154 ELSIF (p_price_based_on_code = 'BSA') THEN
3155 IF (p_bsa_id is not null) THEN
3156 x_pl_agr_bsa_id := p_bsa_id;
3157 ELSIF (p_bsa_name is not null) THEN
3158 x_pl_agr_bsa_id := GET_BSA_ID(p_bsa_name);
3159 IF x_pl_agr_bsa_id is null THEN
3160 x_return_status := FND_API.G_RET_STS_ERROR;
3161 FND_MESSAGE.SET_NAME('QP', 'QP_XML_BSA_NOT_FOUND');
3162 FND_MESSAGE.SET_TOKEN('BSA_NAME', p_bsa_name);
3163 x_return_text := FND_MESSAGE.GET;
3164 END IF;
3165 x_pl_agr_bsa_name := p_bsa_name;
3166 END IF;
3167 END IF;
3168
3169 END CATGI_HEADER_CONVERSIONS;
3170
3171 PROCEDURE GET_CONTEXT_CODE
3172 (
3173 p_context_name IN VARCHAR2,
3174 p_attribute_type IN VARCHAR2,
3175 x_context_code OUT NOCOPY VARCHAR2,
3176 x_return_status OUT NOCOPY VARCHAR2,
3177 x_return_text OUT NOCOPY VARCHAR2
3178 )
3179 IS
3180 BEGIN
3181 SELECT c.prc_context_code
3182 INTO x_context_code
3183 FROM qp_prc_contexts_v c
3184 WHERE nvl(c.user_prc_context_name,c.seeded_prc_context_name) = p_context_name
3185 AND prc_context_type = p_attribute_type;
3186 EXCEPTION
3187 WHEN NO_DATA_FOUND THEN
3188 x_context_code := null;
3189 x_return_status := FND_API.G_RET_STS_ERROR;
3190 FND_MESSAGE.SET_NAME('QP', 'QP_XML_CONTEXT_NOT_FOUND');
3191 FND_MESSAGE.SET_TOKEN('CONTEXT_NAME', p_context_name);
3192 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3193 x_return_text := FND_MESSAGE.GET;
3194 WHEN TOO_MANY_ROWS THEN
3195 x_context_code := null;
3196 x_return_status := FND_API.G_RET_STS_ERROR;
3197 FND_MESSAGE.SET_NAME('QP', 'QP_XML_CONTEXT_MULTI_FOUND');
3198 FND_MESSAGE.SET_TOKEN('CONTEXT_NAME', p_context_name);
3199 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3200 x_return_text := FND_MESSAGE.GET;
3201 END GET_CONTEXT_CODE;
3202
3203 PROCEDURE GET_ATTRIBUTE_CODE
3204 (
3205 p_context_code IN VARCHAR2,
3206 p_attribute_name IN VARCHAR2,
3207 p_attribute_type IN VARCHAR2,
3208 x_attribute_code OUT NOCOPY VARCHAR2,
3209 x_return_status OUT NOCOPY VARCHAR2,
3210 x_return_text OUT NOCOPY VARCHAR2
3211 )
3212 IS
3213 BEGIN
3214 SELECT sb.segment_mapping_column
3215 INTO x_attribute_code
3216 FROM qp_prc_contexts_b p, qp_segments_b sb, qp_segments_tl stl
3217 WHERE p.prc_context_code = p_context_code
3218 AND p.prc_context_type = p_attribute_type
3219 AND sb.prc_context_id = p.prc_context_id
3220 AND stl.segment_id = sb.segment_id
3221 AND stl.language = userenv('LANG')
3222 AND nvl(stl.user_segment_name,stl.seeded_segment_name) = p_attribute_name;
3223 EXCEPTION
3224 WHEN NO_DATA_FOUND THEN
3225 x_attribute_code := null;
3226 x_return_status := FND_API.G_RET_STS_ERROR;
3227 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ATTRIBUTE_NOT_FOUND');
3228 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_NAME', p_attribute_name);
3229 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3230 FND_MESSAGE.SET_TOKEN('CONTEXT_CODE', p_context_code);
3231 x_return_text := FND_MESSAGE.GET;
3232 WHEN TOO_MANY_ROWS THEN
3233 x_attribute_code := null;
3234 x_return_status := FND_API.G_RET_STS_ERROR;
3235 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ATTRIBUTE_MULTI_FOUND');
3236 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_NAME', p_attribute_name);
3237 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3238 FND_MESSAGE.SET_TOKEN('CONTEXT_CODE', p_context_code);
3239 x_return_text := FND_MESSAGE.GET;
3240 END GET_ATTRIBUTE_CODE;
3241
3242 PROCEDURE GET_ATTRIBUTE_VALUE_CODE
3243 (
3244 p_context_code IN VARCHAR2,
3245 p_attribute_code IN VARCHAR2,
3246 p_attribute_value_name IN VARCHAR2,
3247 p_attribute_type IN VARCHAR2,
3248 x_attribute_value_code OUT NOCOPY VARCHAR2,
3249 x_return_status OUT NOCOPY VARCHAR2,
3250 x_return_text OUT NOCOPY VARCHAR2
3251 )
3252 IS
3253 Vset FND_VSET.valueset_r;
3254 Fmt FND_VSET.valueset_dr;
3255
3256 Found BOOLEAN;
3257 ROW NUMBER;
3258 VALUE FND_VSET.value_dr;
3259
3260 x_Format_Type VARCHAR2(1);
3261
3262 x_Validation_Type VARCHAR2(1);
3263 x_Vsid NUMBER;
3264 l_count NUMBER := 0;
3265 l_segment_code VARCHAR2(240);
3266 l_flexfield_name VARCHAR2(240);
3267
3268 BEGIN
3269 -- get segment_code from segment_mapping_column
3270 SELECT segment_code
3271 INTO l_segment_code
3272 FROM qp_prc_contexts_b p, qp_segments_b sb, qp_segments_tl stl
3273 WHERE p.prc_context_code = p_context_code
3274 AND p.prc_context_type = p_attribute_type
3275 AND sb.prc_context_id = p.prc_context_id
3276 AND stl.segment_id = sb.segment_id
3277 AND stl.language = userenv('LANG')
3278 AND sb.segment_mapping_column = p_attribute_code;
3279
3280 -- get flexfield name
3281 IF p_attribute_type = 'QUALIFIER' THEN
3282 l_flexfield_name := 'QP_ATTR_DEFNS_QUALIFIER';
3283 ELSIF p_attribute_type = 'PRICING_ATTRIBUTE' THEN
3284 l_flexfield_name := 'QP_ATTR_DEFNS_PRICING';
3285 END IF;
3286
3287 -- get valueset
3288 qp_util.get_valueset_id(l_FlexField_Name, p_Context_Code,
3289 l_segment_code, x_Vsid,
3290 x_Format_Type, x_Validation_Type);
3291
3292 IF x_Validation_Type IN('F', 'I') AND x_Vsid IS NOT NULL THEN
3293
3294 FND_VSET.get_valueset(x_Vsid, Vset, Fmt);
3295 FND_VSET.get_value_init(Vset, TRUE);
3296 FND_VSET.get_value(Vset, ROW, Found, VALUE);
3297
3298 IF Fmt.Has_Id THEN -- id defined, get id
3299 WHILE(Found) LOOP
3300 IF p_attribute_value_name = VALUE.VALUE THEN
3301 x_attribute_value_code := VALUE.id;
3302 l_count := l_count + 1;
3303 EXIT;
3304 END IF;
3305 FND_VSET.get_value(Vset, ROW, Found, VALUE);
3306 END LOOP;
3307 ELSE -- id not defined, get value
3308 WHILE(Found) LOOP
3309 IF p_attribute_value_name = VALUE.VALUE THEN
3310 x_attribute_value_code := p_attribute_value_name;
3311 l_count := l_count + 1;
3312 EXIT;
3313 END IF;
3314 FND_VSET.get_value(Vset, ROW, Found, VALUE);
3315 END LOOP;
3316 END IF; -- end of Fmt.Has_Id
3317
3318 FND_VSET.get_value_end(Vset);
3319
3320 IF l_count = 0 THEN
3321 RAISE NO_DATA_FOUND;
3322 ELSIF l_count > 1 THEN
3323 RAISE TOO_MANY_ROWS;
3324 END IF;
3325
3326 ELSE -- if validation type is not F or I or valueset id is null (not defined)
3327
3328 x_attribute_value_code := p_attribute_value_name;
3329
3330 END IF;
3331
3332 EXCEPTION
3333 WHEN NO_DATA_FOUND THEN
3334 x_attribute_value_code := null;
3335 x_return_status := FND_API.G_RET_STS_ERROR;
3336 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ATTR_VALUE_NOT_FOUND');
3337 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_NAME', p_attribute_value_name);
3338 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_CODE', p_attribute_code);
3339 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3340 FND_MESSAGE.SET_TOKEN('CONTEXT_CODE', p_context_code);
3341 x_return_text := FND_MESSAGE.GET;
3342 WHEN TOO_MANY_ROWS THEN
3343 x_attribute_value_code := null;
3344 x_return_status := FND_API.G_RET_STS_ERROR;
3345 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ATTR_VALUE_MULTI_FOUND');
3346 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_NAME', p_attribute_value_name);
3347 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_CODE', p_attribute_code);
3348 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3349 FND_MESSAGE.SET_TOKEN('CONTEXT_CODE', p_context_code);
3350 x_return_text := FND_MESSAGE.GET;
3351 END GET_ATTRIBUTE_VALUE_CODE;
3352
3353 PROCEDURE PUBLISH_AND_DELIVER_CP
3354 (
3355 err_buff OUT NOCOPY VARCHAR2,
3356 retcode OUT NOCOPY NUMBER,
3357 p_pb_input_header_id NUMBER,
3358 p_price_book_id NUMBER,
3359 p_servlet_url IN VARCHAR2
3360 )
3361 IS
3362 l_status VARCHAR2(30);
3363 l_status_text VARCHAR2(2000);
3364 BEGIN
3365 PUBLISH_AND_DELIVER(p_pb_input_header_id, p_price_book_id, p_servlet_url, l_status, l_status_text);
3366 IF l_status <> FND_API.G_RET_STS_SUCCESS THEN
3367 retcode := 2;
3368 err_buff := l_status_text;
3369 ELSE
3370 retcode := 0;
3371 err_buff := '';
3372 END IF;
3373 EXCEPTION
3374 WHEN OTHERS THEN
3375 retcode := 2;
3376 err_buff := l_status || ':' || l_status_text;
3377 END PUBLISH_AND_DELIVER_CP;
3378
3379 PROCEDURE PUBLISH_AND_DELIVER
3380 (
3381 p_pb_input_header_id IN NUMBER,
3382 p_price_book_header_id IN NUMBER,
3383 p_servlet_url IN VARCHAR2,
3384 x_return_status OUT NOCOPY VARCHAR2,
3385 x_return_status_text OUT NOCOPY VARCHAR2
3386 )
3387 IS
3388 L_MAX_STATUS_REQUESTS NUMBER:=240;
3389 L_STATUS_REQUEST_INTERVAL NUMBER:=10; -- seconds
3390 L_TRANSFER_TIMEOUT NUMBER:=3600; -- seconds
3391 l_routine VARCHAR2(240):='QP_PRICE_BOOK_UTIL.PUBLISH_AND_DELIVER';
3392 l_output_file VARCHAR2(240);
3393 l_debug VARCHAR2(3);
3394 l_url_servlet_string VARCHAR2(240);
3395 l_url_param_string VARCHAR2(240);
3396 l_return_status VARCHAR2(240);
3397 l_return_status_text VARCHAR2(2000);
3398 l_status_request_cnt NUMBER;
3399 l_dummy_return_details UTL_HTTP.HTML_PIECES;
3400 l_status_code VARCHAR(240);
3401
3402 err_buff VARCHAR2(240);
3403 retcode NUMBER;
3404
3405 INVALID_PARAMS_ERROR EXCEPTION;
3406 E_ROUTINE_ERRORS EXCEPTION;
3407 MAX_STATUS_REQUESTS_REACHED EXCEPTION;
3408 INVALID_PRICE_BOOK_HEADER EXCEPTION;
3409
3410 BEGIN
3411
3412 QP_PREQ_GRP.Set_QP_Debug;
3413 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
3414 IF l_debug = FND_API.G_TRUE THEN
3415 l_output_file := OE_DEBUG_PUB.SET_DEBUG_MODE('FILE');
3416 FND_FILE.PUT_LINE( FND_FILE.LOG, 'The output file is : ' || l_output_file );
3417 END IF;
3418
3419 IF (p_pb_input_header_id IS NULL or p_price_book_header_id IS NULL) THEN
3420 RAISE INVALID_PARAMS_ERROR;
3421 END IF;
3422
3423 BEGIN
3424 UPDATE QP_PRICE_BOOK_HEADERS_B
3425 SET PUB_STATUS_CODE = 'REQUESTED'
3426 WHERE PRICE_BOOK_HEADER_ID = p_price_book_header_id;
3427 COMMIT;
3428 EXCEPTION
3429 WHEN NO_DATA_FOUND THEN
3430 RAISE INVALID_PRICE_BOOK_HEADER;
3431 END;
3432
3433 l_url_servlet_string := nvl(p_servlet_url, fnd_profile.value('APPS_FRAMEWORK_AGENT') ||
3434 '/OA_HTML/RequestPriceBook');
3435 l_url_param_string := 'pbInputHeaderId='||nvl(p_pb_input_header_id, -1)||
3436 qp_java_engine_util_pub.G_HARD_CHAR||'priceBookHeaderId='||nvl(p_price_book_header_id, -1);
3437 qp_java_engine_util_pub.send_java_request(l_url_servlet_string,
3438 l_url_param_string,
3439 l_return_status,
3440 l_return_status_text,
3441 l_dummy_return_details,
3442 false,
3443 L_TRANSFER_TIMEOUT,
3444 FND_API.G_TRUE);
3445
3446 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3447 IF (l_return_status_text = 'UTL_TCP.END_OF_INPUT') THEN
3448 l_status_request_cnt := 0;
3449 BEGIN
3450 LOOP
3451 DBMS_LOCK.SLEEP(L_STATUS_REQUEST_INTERVAL);
3452
3453 SELECT PUB_STATUS_CODE
3454 INTO l_status_code
3455 FROM QP_PRICE_BOOK_HEADERS_B
3456 WHERE PRICE_BOOK_HEADER_ID = p_price_book_header_id;
3457
3458 IF l_status_code = 'ERROR' THEN
3459 RAISE E_ROUTINE_ERRORS;
3460 END IF;
3461 EXIT WHEN l_status_code = 'COMPLETED';
3462 IF l_status_request_cnt > L_MAX_STATUS_REQUESTS THEN
3463 RAISE MAX_STATUS_REQUESTS_REACHED;
3464 END IF;
3465 l_status_request_cnt := l_status_request_cnt + 1;
3466 END LOOP;
3467 EXCEPTION
3468 WHEN NO_DATA_FOUND THEN
3469 RAISE INVALID_PRICE_BOOK_HEADER;
3470 END;
3471 ELSE
3472 RAISE E_ROUTINE_ERRORS;
3473 END IF;
3474 END IF;
3475
3476 x_return_status := FND_API.G_RET_STS_SUCCESS;
3477
3478 EXCEPTION
3479 WHEN INVALID_PARAMS_ERROR THEN
3480 x_return_status := FND_API.G_RET_STS_ERROR;
3481 x_return_status_text := 'Invalid parameter values.';
3482 WHEN E_ROUTINE_ERRORS THEN
3483 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
3484 QP_PREQ_GRP.engine_debug(l_routine||'l_return_status_text:'||l_return_status_text);
3485 QP_PREQ_GRP.engine_debug(l_routine||'SQLERRM:'||SQLERRM);
3486 END IF;
3487 x_return_status := FND_API.G_RET_STS_ERROR;
3488 x_return_status_text := l_return_status_text;
3489 WHEN MAX_STATUS_REQUESTS_REACHED THEN
3490 x_return_status := FND_API.G_RET_STS_ERROR;
3491 x_return_status_text := 'Request has exceeded '||(L_MAX_STATUS_REQUESTS*L_STATUS_REQUEST_INTERVAL)||' seconds.';
3492 WHEN INVALID_PRICE_BOOK_HEADER THEN
3493 x_return_status := FND_API.G_RET_STS_ERROR;
3494 x_return_status_text := 'Invalid price book header id.';
3495 WHEN OTHERS THEN
3496 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
3497 QP_PREQ_GRP.engine_debug(l_routine||'l_return_status_text:'||l_return_status_text);
3498 QP_PREQ_GRP.engine_debug(l_routine||'SQLERRM:'||SQLERRM);
3499 END IF;
3500 x_return_status := FND_API.G_RET_STS_ERROR;
3501 x_return_status_text := l_return_status_text;
3502 END PUBLISH_AND_DELIVER;
3503
3504 PROCEDURE SEND_SYNC_CATALOG
3505 (
3506 p_price_book_header_id IN NUMBER,
3507 x_return_status OUT NOCOPY VARCHAR2,
3508 x_return_status_text OUT NOCOPY VARCHAR2
3509 )
3510 IS
3511 x_progress VARCHAR2(1000);
3512 transaction_type VARCHAR2(240);
3513 transaction_subtype VARCHAR2(240);
3514 document_direction VARCHAR2(240);
3515 party_id NUMBER;
3516 party_site_id NUMBER;
3517 party_type VARCHAR2(30);
3518 return_code PLS_INTEGER;
3519 errmsg VARCHAR2(2000);
3520 result BOOLEAN;
3521 l_error_code NUMBER;
3522 l_error_msg VARCHAR2(2000);
3523
3524 -- parameters for raising event
3525 l_send_syct_event VARCHAR2(100);
3526 l_create_cln_event VARCHAR2(100);
3527 l_event_key VARCHAR2(100);
3528 l_syncctlg_seq NUMBER;
3529 l_send_syct_parameter_list wf_parameter_list_t;
3530 l_create_cln_parameter_list wf_parameter_list_t;
3531 l_operating_unit_id NUMBER;
3532 l_inv_org_id NUMBER;
3533 l_date DATE;
3534 l_canonical_date VARCHAR2(100);
3535
3536 -- parameters for dealing with the number of items restriction
3537 counter BINARY_INTEGER;
3538 msgs_sent_flag BOOLEAN;
3539
3540 l_debug VARCHAR2(3);
3541
3542 BEGIN
3543 -- set debug level
3544 QP_PREQ_GRP.Set_QP_Debug;
3545 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
3546
3547 IF (l_debug = FND_API.G_TRUE) THEN
3548 QP_PREQ_GRP.engine_debug('ENTERING QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG');
3549 END IF;
3550
3551 IF (l_debug = FND_API.G_TRUE) THEN
3552 QP_PREQ_GRP.engine_debug('With the following parameters:');
3553 QP_PREQ_GRP.engine_debug('p_price_book_header_id:' || p_price_book_header_id);
3554 END IF;
3555
3556 -- initialize parameters
3557 x_progress := '000';
3558 transaction_type := 'QP';
3559 transaction_subtype := 'CATSO';
3560 document_direction := 'OUT';
3561 party_type := 'C';
3562 result := FALSE;
3563
3564 l_send_syct_event := 'oracle.apps.qp.pricebook.catso';
3565 l_create_cln_event := 'oracle.apps.cln.ch.collaboration.create';
3566
3567 --transaction_type := 'CLN';
3568 --transaction_subtype := 'SYNCCTLGO';
3569 --l_send_syct_event := 'oracle.apps.cln.event.syncctlg';
3570
3571 l_send_syct_parameter_list := wf_parameter_list_t();
3572 l_create_cln_parameter_list := wf_parameter_list_t();
3573
3574 counter := 1;
3575 msgs_sent_flag := FALSE;
3576
3577 SELECT i.customer_attr_value, i.DLV_XML_SITE_ID
3578 INTO party_id, party_site_id
3579 FROM qp_price_book_headers_b p, qp_pb_input_headers_b i
3580 WHERE p.price_book_header_id = p_price_book_header_id
3581 AND p.pb_input_header_id = i.pb_input_header_id;
3582
3583 IF (l_debug = FND_API.G_TRUE) THEN
3584 QP_PREQ_GRP.engine_debug('party_id:' || party_id);
3585 QP_PREQ_GRP.engine_debug('party_site_id:' || party_site_id);
3586 END IF;
3587
3588 SELECT FND_PROFILE.VALUE('ORG_ID')
3589 INTO l_operating_unit_id
3590 FROM dual;
3591
3592 IF (l_debug = FND_API.G_TRUE) THEN
3593 QP_PREQ_GRP.engine_debug('l_operating_unit_id:' || l_operating_unit_id);
3594 END IF;
3595
3596 l_inv_org_id := qp_util.Get_Item_Validation_Org;
3597 IF (l_debug = FND_API.G_TRUE) THEN
3598 QP_PREQ_GRP.engine_debug('l_inv_org_id:' || l_inv_org_id);
3599 END IF;
3600
3601 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Parameters Initialized';
3602 IF (l_debug = FND_API.G_TRUE) THEN
3603 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3604 END IF;
3605
3606 -- XML Setup Check
3607 IF (l_debug = FND_API.G_TRUE) THEN
3608 QP_PREQ_GRP.engine_debug('Parameters before ecx_document.isDeliveryRequired:');
3609 QP_PREQ_GRP.engine_debug('transaction_type:' || transaction_type);
3610 QP_PREQ_GRP.engine_debug('transaction_subtype:' || transaction_subtype);
3611 QP_PREQ_GRP.engine_debug('party_id:' || party_id);
3612 QP_PREQ_GRP.engine_debug('party_site_id:' || party_site_id);
3613 QP_PREQ_GRP.engine_debug('return_code:' || return_code);
3614 QP_PREQ_GRP.engine_debug('errmsg:' || errmsg);
3615 END IF;
3616
3617 ecx_document.isDeliveryRequired(
3618 transaction_type => transaction_type,
3619 transaction_subtype => transaction_subtype,
3620 party_id => party_id,
3621 party_site_id => party_site_id,
3622 resultout => result,
3623 retcode => return_code,
3624 errmsg => errmsg);
3625
3626 IF (l_debug = FND_API.G_TRUE) THEN
3627 QP_PREQ_GRP.engine_debug('Values returned from ecx_document.isDeliveryRequired:');
3628 QP_PREQ_GRP.engine_debug('return_code:' || return_code);
3629 QP_PREQ_GRP.engine_debug('errmsg:' || errmsg);
3630 END IF;
3631
3632 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : XML Setup Check';
3633 IF (l_debug = FND_API.G_TRUE) THEN
3634 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3635 END IF;
3636
3637 IF NOT(result) THEN
3638 -- trading partner not found
3639 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : No Trading Partner found during XML Setup Check';
3640 IF (l_debug = FND_API.G_TRUE) THEN
3641 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3642 END IF;
3643
3644 ELSE -- no number specified, send in one message
3645
3646 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : No Number Limit Specified';
3647 IF (l_debug = FND_API.G_TRUE) THEN
3648 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3649 END IF;
3650
3651 -- create unique key
3652 SELECT QP_XML_MESSAGES_S.NEXTVAL INTO l_syncctlg_seq FROM dual;
3653 IF (l_debug = FND_API.G_TRUE) THEN
3654 QP_PREQ_GRP.engine_debug('l_syncctlg_seq:' || l_syncctlg_seq);
3655 END IF;
3656 l_event_key := to_char(p_price_book_header_id) || '.' || to_char(l_syncctlg_seq);
3657
3658 SELECT SYSDATE INTO l_date FROM dual;
3659 IF (l_debug = FND_API.G_TRUE) THEN
3660 QP_PREQ_GRP.engine_debug('l_date:' || l_date);
3661 END IF;
3662 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
3663
3664 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Created Unique Key';
3665 IF (l_debug = FND_API.G_TRUE) THEN
3666 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3667 END IF;
3668
3669 -- add parameters to list for create collaboration event
3670 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
3671 p_value => transaction_type,
3672 p_parameterlist => l_create_cln_parameter_list);
3673 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
3674 p_value => transaction_subtype,
3675 p_parameterlist => l_create_cln_parameter_list);
3676 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
3677 p_value => document_direction,
3678 p_parameterlist => l_create_cln_parameter_list);
3679 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
3680 p_value => l_event_key,
3681 p_parameterlist => l_create_cln_parameter_list);
3682 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
3683 p_value => party_id,
3684 p_parameterlist => l_create_cln_parameter_list);
3685 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
3686 p_value => party_site_id,
3687 p_parameterlist => l_create_cln_parameter_list);
3688 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
3689 p_value => party_type,
3690 p_parameterlist => l_create_cln_parameter_list);
3691 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
3692 p_value => l_event_key,
3693 p_parameterlist => l_create_cln_parameter_list);
3694 wf_event.AddParameterToList(p_name => 'ORG_ID',
3695 p_value => l_operating_unit_id,
3696 p_parameterlist => l_create_cln_parameter_list);
3697 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
3698 p_value => l_canonical_date,
3699 p_parameterlist => l_create_cln_parameter_list);
3700
3701 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Initialize Create Event Parameters';
3702 IF (l_debug = FND_API.G_TRUE) THEN
3703 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3704 end if;
3705
3706 -- raise create collaboration event
3707 wf_event.raise(p_event_name => l_create_cln_event,
3708 p_event_key => l_event_key,
3709 p_parameters => l_create_cln_parameter_list);
3710
3711 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Create Event Raised';
3712 IF (l_debug = FND_API.G_TRUE) THEN
3713 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3714 end if;
3715
3716 -- add parameters to list for send show shipment document
3717 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
3718 p_value => transaction_type,
3719 p_parameterlist => l_send_syct_parameter_list);
3720 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
3721 p_value => transaction_subtype,
3722 p_parameterlist => l_send_syct_parameter_list);
3723 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
3724 p_value => transaction_type,
3725 p_parameterlist => l_send_syct_parameter_list);
3726 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
3727 p_value => transaction_subtype,
3728 p_parameterlist => l_send_syct_parameter_list);
3729 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
3730 p_value => document_direction,
3731 p_parameterlist => l_send_syct_parameter_list);
3732 wf_event.AddParameterToList(p_name => 'ECX_PARTY_ID',
3733 p_value => party_id,
3734 p_parameterlist => l_send_syct_parameter_list);
3735 wf_event.AddParameterToList(p_name => 'ECX_PARTY_SITE_ID',
3736 p_value => party_site_id,
3737 p_parameterlist => l_send_syct_parameter_list);
3738 wf_event.AddParameterToList(p_name => 'ECX_PARTY_TYPE',
3739 p_value => party_type,
3740 p_parameterlist => l_send_syct_parameter_list);
3741 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
3742 p_value => party_id,
3743 p_parameterlist => l_send_syct_parameter_list);
3744 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
3745 p_value => party_site_id,
3746 p_parameterlist => l_send_syct_parameter_list);
3747 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
3748 p_value => party_type,
3749 p_parameterlist => l_send_syct_parameter_list);
3750 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
3751 p_value => l_event_key,
3752 p_parameterlist => l_send_syct_parameter_list);
3753 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
3754 p_value => l_event_key,
3755 p_parameterlist => l_send_syct_parameter_list);
3756 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
3757 p_value => l_event_key,
3758 p_parameterlist => l_send_syct_parameter_list);
3759 wf_event.AddParameterToList(p_name => 'USER_ID',
3760 p_value => fnd_global.user_id,
3761 p_parameterlist => l_send_syct_parameter_list);
3762 wf_event.AddParameterToList(p_name => 'APPLICATION_ID',
3763 p_value => fnd_global.resp_appl_id,
3764 p_parameterlist => l_send_syct_parameter_list);
3765 wf_event.AddParameterToList(p_name => 'RESPONSIBILITY_ID',
3766 p_value => fnd_global.resp_id,
3767 p_parameterlist => l_send_syct_parameter_list);
3768 wf_event.AddParameterToList(p_name => 'ORG_ID',
3769 p_value => l_inv_org_id,
3770 p_parameterlist => l_send_syct_parameter_list);
3771 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
3772 p_value => l_canonical_date,
3773 p_parameterlist => l_send_syct_parameter_list);
3774 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER1',
3775 p_value => p_price_book_header_id,
3776 p_parameterlist => l_send_syct_parameter_list);
3777 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER2',
3778 p_value => NULL,
3779 p_parameterlist => l_send_syct_parameter_list);
3780 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER3',
3781 p_value => NULL,
3782 p_parameterlist => l_send_syct_parameter_list);
3783 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER4',
3784 p_value => NULL,
3785 p_parameterlist => l_send_syct_parameter_list);
3786 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER5',
3787 p_value => NULL,
3788 p_parameterlist => l_send_syct_parameter_list);
3789
3790 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Send Document Event Parameters Initialized';
3791 IF (l_debug = FND_API.G_TRUE) THEN
3792 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3793 END IF;
3794
3795 -- raise event for send show shipment document
3796 wf_event.RAISE(p_event_name => l_send_syct_event,
3797 p_event_key => l_event_key,
3798 p_parameters => l_send_syct_parameter_list);
3799
3800 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Send Document Event Raised';
3801 IF (l_debug = FND_API.G_TRUE) THEN
3802 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3803 END IF;
3804
3805 END IF;
3806
3807 IF (l_debug = FND_API.G_TRUE) THEN
3808 QP_PREQ_GRP.engine_debug('EXITING QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG Successfully');
3809 END IF;
3810
3811 x_return_status := FND_API.G_RET_STS_SUCCESS;
3812
3813 EXCEPTION
3814 WHEN OTHERS THEN
3815 l_error_code := SQLCODE;
3816 l_error_msg := SQLERRM;
3817 IF (l_debug = FND_API.G_TRUE) THEN
3818 QP_PREQ_GRP.engine_debug('Exception ' || ':' || l_error_code || ':' || l_error_msg);
3819 END IF;
3820
3821 x_progress := 'EXITING QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG in Error ';
3822 IF (l_debug = FND_API.G_TRUE) THEN
3823 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3824 END IF;
3825 x_return_status := FND_API.G_RET_STS_ERROR;
3826 x_return_status_text := l_error_msg;
3827 END SEND_SYNC_CATALOG;
3828
3829 PROCEDURE GENERATE_PUBLISH_PRICE_BOOK_WF
3830 (
3831 itemtype in VARCHAR2,
3832 itemkey in VARCHAR2,
3833 actid in NUMBER,
3834 funcmode in VARCHAR2,
3835 resultout in OUT NOCOPY VARCHAR2
3836 )
3837 IS
3838 l_pb_input_header_id VARCHAR2(240);
3839 l_pricing_perspective_code VARCHAR2(30);
3840 l_user_name VARCHAR2(240);
3841 l_status_code VARCHAR2(240);
3842 l_status_text VARCHAR2(240);
3843 l_request_id NUMBER;
3844 l_ret_code NUMBER;
3845 l_err_buf VARCHAR2(240);
3846 l_debug VARCHAR2(3);
3847 l_routine VARCHAR2(240):='QP_PRICE_BOOK_UTIL.GENERATE_PUBLISH_PRICE_BOOK_WF:';
3848 BEGIN
3849 l_pb_input_header_id := Wf_Engine.GetItemAttrText(itemtype, itemkey, 'PARAMETER1');
3850 IF (l_pb_input_header_id is null) THEN
3851 wf_core.token('PARAMETER1','NULL');
3852 wf_core.raise('WFSQL_ARGS');
3853 END IF;
3854
3855 /*
3856 l_pricing_perspective_code := Wf_Engine.GetItemAttrText(itemtype, itemkey, 'PARAMETER2');
3857 IF (l_pricing_perspective_code is null) THEN
3858 wf_core.token('PARAMETER2','NULL');
3859 wf_core.raise('WFSQL_ARGS');
3860 END IF;
3861 */
3862
3863 l_user_name := Wf_Engine.GetItemAttrText(itemtype, itemkey, 'PARAMETER4');
3864 IF (l_user_name is null) THEN
3865 wf_core.token('PARAMETER4','NULL');
3866 wf_core.raise('WFSQL_ARGS');
3867 END IF;
3868
3869 SET_XML_CONTEXT(l_user_name, l_status_code, l_status_text);
3870
3871 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
3872 IF l_debug = FND_API.G_TRUE THEN
3873 QP_PREQ_GRP.engine_debug(l_routine||'calling GENERATE_PUBLISH_PRICE_BOOK');
3874 END IF;
3875 QP_PRICE_BOOK_PVT.GENERATE_PUBLISH_PRICE_BOOK(l_pb_input_header_id,
3876 l_request_id,
3877 l_status_code,
3878 l_ret_code,
3879 l_err_buf);
3880 IF l_debug = FND_API.G_TRUE THEN
3881 QP_PREQ_GRP.engine_debug(l_routine||'returned from GENERATE_PUBLISH_PRICE_BOOK');
3882 END IF;
3883
3884 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
3885
3886 EXCEPTION
3887 WHEN OTHERS THEN
3888 Wf_Core.Context('QP_PRICE_BOOK_UTIL', 'GENERATE_PUBLISH_PRICE_BOOK_WF', itemtype, itemkey, to_char(actid), funcmode);
3889 raise;
3890 END GENERATE_PUBLISH_PRICE_BOOK_WF;
3891
3892 PROCEDURE CATSO_SELECTOR
3893 ( p_itemtype in varchar2,
3894 p_itemkey in varchar2,
3895 p_actid in number,
3896 p_funcmode in varchar2,
3897 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
3898 )
3899 IS
3900 l_user_id NUMBER;
3901 l_resp_id NUMBER;
3902 l_resp_appl_id NUMBER;
3903 l_org_id NUMBER;
3904 l_current_org_id NUMBER;
3905 l_client_org_id NUMBER;
3906 l_parameter1 NUMBER;
3907 l_debug VARCHAR2(3);
3908 l_application_code VARCHAR2(30);
3909 a varchar2(100);
3910
3911 BEGIN
3912
3913 QP_PREQ_GRP.Set_QP_Debug;
3914 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
3915
3916 IF l_debug = FND_API.G_TRUE THEN
3917 QP_PREQ_GRP.engine_debug( 'ENTERING CATSO_SELECTOR PROCEDURE' ) ;
3918 QP_PREQ_GRP.engine_debug( 'THE WORKFLOW FUNCTION MODE IS: FUNCMODE='||P_FUNCMODE ) ;
3919 END IF;
3920
3921 IF (p_funcmode = 'RUN') THEN
3922 IF l_debug = FND_API.G_TRUE THEN
3923 QP_PREQ_GRP.engine_debug( 'P_FUNCMODE IS RUN' ) ;
3924 END IF;
3925 p_x_result := 'COMPLETE';
3926 ELSIF(p_funcmode = 'TEST_CTX') THEN
3927 IF l_debug = FND_API.G_TRUE THEN
3928 QP_PREQ_GRP.engine_debug( 'P_FUNCMODE IS TEST_CTX' ) ;
3929 END IF;
3930
3931 l_org_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3932 itemkey => p_itemkey,
3933 aname => 'ORG_ID');
3934
3935 IF l_debug = FND_API.G_TRUE THEN
3936 QP_PREQ_GRP.engine_debug( 'l_org_id (from workflow)=>'|| l_org_id ) ;
3937 QP_PREQ_GRP.engine_debug( 'mo_global.get_current_org_id =>'|| mo_global.get_current_org_id ) ;
3938 END IF;
3939
3940 IF (mo_global.get_current_org_id is null OR MO_GLOBAL.get_access_mode is null) THEN
3941 p_x_result := 'NOTSET';
3942 ELSE
3943 IF (NVL(mo_global.get_current_org_id,-99) <> l_Org_Id) THEN
3944 p_x_result := 'FALSE';
3945 ELSE
3946 p_x_result := 'TRUE';
3947 END IF;
3948 END IF;
3949
3950 ELSIF(p_funcmode = 'SET_CTX') THEN
3951 IF l_debug = FND_API.G_TRUE THEN
3952 QP_PREQ_GRP.engine_debug( 'P_FUNCMODE IS SET_CTX' ) ;
3953 END IF;
3954
3955 l_user_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3956 itemkey => p_itemkey,
3957 aname => 'USER_ID');
3958 l_resp_appl_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3959 itemkey => p_itemkey,
3960 aname => 'APPLICATION_ID');
3961 l_resp_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3962 itemkey => p_itemkey,
3963 aname => 'RESPONSIBILITY_ID');
3964 l_org_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3965 itemkey => p_itemkey,
3966 aname => 'ORG_ID');
3967
3968 IF l_debug = FND_API.G_TRUE THEN
3969 QP_PREQ_GRP.engine_debug('l_user_id =>' || l_user_id || ' l_resp_id =>' || l_resp_id || ' l_resp_appl_id =>' || l_resp_appl_id || ' l_org_id =>' || l_org_id);
3970 END IF;
3971
3972 IF l_resp_appl_id is null OR l_resp_id is null THEN
3973 dbms_application_info.set_client_info(l_org_id);
3974 IF l_debug = FND_API.G_TRUE THEN
3975 QP_PREQ_GRP.engine_debug('set org using dbms_application_info.set_client_info');
3976 END IF;
3977 ELSE
3978 -- Set the database session context
3979 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
3980 /*
3981 BEGIN
3982 SELECT application_short_name
3983 INTO l_application_code
3984 FROM fnd_application
3985 WHERE application_id = fnd_global.resp_appl_id; --Responsibility of user
3986 EXCEPTION
3987 WHEN OTHERS THEN
3988 l_application_code := 'QP';
3989 END;
3990 */
3991 MO_GLOBAL.Init('QP');
3992 --mo_global.set_policy_context(p_access_mode => 'S', p_org_id=>l_Org_Id);
3993 END IF;
3994
3995 p_x_result := 'COMPLETE';
3996 END IF;
3997
3998 IF l_debug = FND_API.G_TRUE THEN
3999 QP_PREQ_GRP.engine_debug('p_x_result =>'||p_x_result);
4000 END IF;
4001
4002 EXCEPTION
4003 WHEN OTHERS THEN NULL;
4004 WF_CORE.Context('QP_PRICE_BOOK_UTIL', 'CATSO_SELECTOR',
4005 p_itemtype, p_itemkey, p_actid, p_funcmode);
4006 RAISE;
4007
4008 END CATSO_SELECTOR;
4009
4010 PROCEDURE SET_XML_CONTEXT
4011 (
4012 p_user_name IN VARCHAR2,
4013 x_return_status OUT NOCOPY VARCHAR2,
4014 x_return_text IN OUT NOCOPY VARCHAR2
4015 )
4016 IS
4017 l_user_id NUMBER;
4018 l_pricing_perspective_code VARCHAR(30);
4019 l_pricing_perspective_appl_id NUMBER;
4020 l_resp_id NUMBER;
4021 l_resp_appl_id NUMBER;
4022 l_resp_appl_name VARCHAR2(30);
4023 l_debug VARCHAR2(3);
4024 l_routine VARCHAR2(240):='QP_PRICE_BOOK_UTIL.SET_XML_CONTEXT';
4025 BEGIN
4026 -- get user based on user name
4027 SELECT user_id
4028 INTO l_user_id
4029 FROM fnd_user
4030 WHERE user_name = upper(p_user_name);
4031
4032 -- get pricing perpective based on user
4033 fnd_global.apps_initialize(l_user_id,
4034 null,
4035 null);
4036 l_pricing_perspective_code := FND_PROFILE.VALUE('QP_EXT_DEFAULT_PRICING_PERSPECTIVE');
4037 QP_PREQ_GRP.Set_QP_Debug;
4038 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
4039 IF l_debug = FND_API.G_TRUE THEN
4040 QP_PREQ_GRP.engine_debug(l_routine || ':l_user_id=' || l_user_id);
4041 QP_PREQ_GRP.engine_debug(l_routine || ':l_pricing_perspective_code=' || l_pricing_perspective_code);
4042 END IF;
4043
4044 -- get pricing perspective application id based on pricing perspective code
4045 SELECT a.application_id
4046 INTO l_pricing_perspective_appl_id
4047 FROM fnd_application a
4048 WHERE a.application_short_name = l_pricing_perspective_code;
4049 IF l_debug = FND_API.G_TRUE THEN
4050 QP_PREQ_GRP.engine_debug(l_routine || ':l_pricing_perspective_appl_id=' || l_pricing_perspective_appl_id);
4051 END IF;
4052
4053 -- get responsibility based on pricing perspective
4054 fnd_global.apps_initialize(l_user_id,
4055 null,
4056 l_pricing_perspective_appl_id);
4057 l_resp_id := FND_PROFILE.VALUE('QP_XML_RESP');
4058 IF l_debug = FND_API.G_TRUE THEN
4059 QP_PREQ_GRP.engine_debug(l_routine || ':l_resp_id=' || l_resp_id);
4060 END IF;
4061
4062 -- get application id and short name based on responsibility
4063 SELECT a.application_id, a.application_short_name
4064 INTO l_resp_appl_id, l_resp_appl_name
4065 FROM fnd_responsibility r, fnd_application a
4066 WHERE r.responsibility_id = l_resp_id
4067 AND a.application_id = r.application_id;
4068 IF l_debug = FND_API.G_TRUE THEN
4069 QP_PREQ_GRP.engine_debug(l_routine || ':l_resp_appl_id=' || l_resp_appl_id || ',l_resp_appl_name=' || l_resp_appl_name);
4070 END IF;
4071
4072 -- set context
4073 fnd_global.apps_initialize(l_user_id,
4074 l_resp_id,
4075 l_resp_appl_id);
4076 MO_GLOBAL.Init('QP');
4077
4078 EXCEPTION
4079 WHEN NO_DATA_FOUND THEN
4080 x_return_status := FND_API.G_RET_STS_ERROR;
4081 FND_MESSAGE.SET_NAME('QP', 'QP_XML_RESPONSIBILITY_REQUIRED');
4082 x_return_text := FND_MESSAGE.GET;
4083 END;
4084
4085 PROCEDURE CATGI_UPDATE_PUBLISH_OPTIONS
4086 (
4087 p_price_book_name IN VARCHAR2,
4088 p_customer_attr_value IN NUMBER,
4089 p_effective_date IN DATE,
4090 p_price_book_type_code IN VARCHAR2,
4091 p_dlv_xml_site_id IN NUMBER,
4092 p_generation_time_code IN VARCHAR2,
4093 p_gen_schedule_date IN DATE,
4094 x_pb_input_header_id OUT NOCOPY NUMBER,
4095 x_return_status OUT NOCOPY VARCHAR2,
4096 x_return_text IN OUT NOCOPY VARCHAR2
4097 )
4098 IS
4099 l_pb_input_header_id NUMBER;
4100 BEGIN
4101
4102 SELECT pb_input_header_id
4103 INTO l_pb_input_header_id
4104 FROM qp_pb_input_headers_vl
4105 WHERE price_book_name = p_price_book_name
4106 AND customer_attr_value = p_customer_attr_value
4107 --AND effective_date = p_effective_date
4108 AND price_book_type_code = p_price_book_type_code;
4109
4110 UPDATE QP_PB_INPUT_HEADERS_B
4111 SET PUB_TEMPLATE_CODE = NULL,
4112 PUB_LANGUAGE = NULL,
4113 PUB_TERRITORY = NULL,
4114 PUB_OUTPUT_DOCUMENT_TYPE = NULL,
4115 DLV_XML_FLAG = 'Y',
4116 DLV_XML_SITE_ID = p_dlv_xml_site_id,
4117 DLV_EMAIL_FLAG = 'N',
4118 DLV_EMAIL_ADDRESSES = NULL,
4119 DLV_PRINTER_FLAG = 'N',
4120 DLV_PRINTER_NAME = NULL,
4121 PUBLISH_EXISTING_PB_FLAG = 'Y',
4122 GENERATION_TIME_CODE = p_generation_time_code,
4123 GEN_SCHEDULE_DATE = p_gen_schedule_date,
4124 REQUEST_ORIGINATION_CODE = 'XML',
4125 LAST_UPDATE_DATE = SYSDATE,
4126 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
4127 WHERE pb_input_header_id = l_pb_input_header_id;
4128
4129 x_pb_input_header_id := l_pb_input_header_id;
4130 x_return_status := FND_API.G_RET_STS_SUCCESS;
4131 x_return_text := '';
4132
4133 EXCEPTION
4134 WHEN NO_DATA_FOUND THEN
4135 x_return_status := FND_API.G_RET_STS_ERROR;
4136 x_return_text := 'Could not find price book ' || p_price_book_name || ', ' || p_customer_attr_value || ', ' || p_effective_date;
4137 END CATGI_UPDATE_PUBLISH_OPTIONS;
4138
4139 PROCEDURE CATGI_POST_INSERT_PROCESSING
4140 (
4141 p_pb_input_header_id IN NUMBER,
4142 x_return_status OUT NOCOPY VARCHAR2,
4143 x_return_text IN OUT NOCOPY VARCHAR2
4144 )
4145 IS
4146 l_pb_input_header_id NUMBER;
4147 l_pb_input_header_rec qp_pb_input_headers_vl%ROWTYPE;
4148 l_full_pb_input_header_rec qp_pb_input_headers_vl%ROWTYPE;
4149
4150 l_context_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
4151 l_attribute_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
4152 l_attribute_value_tbl QP_PRICE_BOOK_UTIL.VARCHAR_TYPE;
4153 l_attribute_type_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
4154
4155 l_user_id NUMBER;
4156 l_login_id NUMBER;
4157 l_sysdate DATE;
4158
4159 l_cust_account_id NUMBER;
4160 BEGIN
4161 --Fetch the Price Book Input Header record into variable
4162 BEGIN
4163 SELECT *
4164 INTO l_pb_input_header_rec
4165 FROM qp_pb_input_headers_vl
4166 WHERE pb_input_header_id = p_pb_input_header_id;
4167 EXCEPTION
4168 WHEN OTHERS THEN
4169 x_return_status := FND_API.G_RET_STS_ERROR;
4170 x_return_text := 'CATGI_POST_INSERT_PROCESSING: pb not found - ' || SQLERRM;
4171 END;
4172
4173 IF l_pb_input_header_rec.cust_account_id is null THEN
4174 DEFAULT_CUST_ACCOUNT_ID(l_pb_input_header_rec.customer_attr_value,
4175 l_cust_account_id);
4176 IF l_cust_account_id is not null THEN
4177 UPDATE QP_PB_INPUT_HEADERS_B
4178 SET CUST_ACCOUNT_ID = l_cust_account_id
4179 WHERE pb_input_header_id = p_pb_input_header_id;
4180 END IF;
4181 END IF;
4182
4183 IF l_pb_input_header_rec.price_book_type_code = 'D' THEN
4184 --Fetch the Price Book Input Header record into variable
4185 BEGIN
4186 SELECT *
4187 INTO l_full_pb_input_header_rec
4188 FROM qp_pb_input_headers_vl
4189 WHERE price_book_name = l_pb_input_header_rec.price_book_name
4190 AND customer_attr_value = l_pb_input_header_rec.customer_attr_value
4191 AND customer_context = l_pb_input_header_rec.customer_context
4192 AND customer_attribute = l_pb_input_header_rec.customer_attribute
4193 AND price_book_type_code = 'F';
4194 EXCEPTION
4195 WHEN OTHERS THEN
4196 x_return_status := FND_API.G_RET_STS_ERROR;
4197 FND_MESSAGE.SET_NAME('QP', 'QP_FULL_PRICE_BOOK_MUST_EXIST');
4198 x_return_text := FND_MESSAGE.GET;
4199 RETURN;
4200 END;
4201
4202 UPDATE qp_pb_input_headers_b
4203 SET customer_context = l_full_pb_input_header_rec.customer_context,
4204 customer_attribute = l_full_pb_input_header_rec.customer_attribute,
4205 customer_attr_value = l_full_pb_input_header_rec.customer_attr_value,
4206 cust_account_id = l_full_pb_input_header_rec.cust_account_id,
4207 currency_code = l_full_pb_input_header_rec.currency_code,
4208 limit_products_by = l_full_pb_input_header_rec.limit_products_by,
4209 product_context = l_full_pb_input_header_rec.product_context,
4210 product_attribute = l_full_pb_input_header_rec.product_attribute,
4211 product_attr_value = l_full_pb_input_header_rec.product_attr_value,
4212 item_quantity = l_full_pb_input_header_rec.item_quantity,
4213 org_id = l_full_pb_input_header_rec.org_id,
4214 price_based_on = l_full_pb_input_header_rec.price_based_on,
4215 pl_agr_bsa_id = l_full_pb_input_header_rec.pl_agr_bsa_id,
4216 pricing_perspective_code = l_full_pb_input_header_rec.pricing_perspective_code,
4217 request_type_code = l_full_pb_input_header_rec.request_type_code
4218 WHERE pb_input_header_id = p_pb_input_header_id;
4219
4220 UPDATE qp_pb_input_headers_tl
4221 SET pl_agr_bsa_name = l_full_pb_input_header_rec.pl_agr_bsa_name
4222 WHERE pb_input_header_id = p_pb_input_header_id;
4223
4224 --Select the certain columns of input lines from the full price book
4225 BEGIN
4226 SELECT context, attribute, attribute_value, attribute_type
4227 BULK COLLECT INTO l_context_tbl, l_attribute_tbl,
4228 l_attribute_value_tbl, l_attribute_type_tbl
4229 FROM qp_pb_input_lines
4230 WHERE pb_input_header_id = l_full_pb_input_header_rec.pb_input_header_id;
4231 EXCEPTION
4232 WHEN NO_DATA_FOUND THEN
4233 null;
4234 END;
4235
4236 IF l_context_tbl.count > 0 THEN
4237 --Insert the Input criteria into input lines tables
4238 l_sysdate := sysdate;
4239 l_user_id := fnd_global.user_id;
4240 l_login_id := fnd_global.login_id;
4241 BEGIN
4242 FORALL k IN l_context_tbl.FIRST..l_context_tbl.LAST
4243 INSERT INTO qp_pb_input_lines
4244 (pb_input_line_id, pb_input_header_id,
4245 context, attribute, attribute_value,
4246 attribute_type, creation_date, created_by, last_update_date,
4247 last_updated_by, last_update_login
4248 )
4249 VALUES
4250 (qp_pb_input_lines_s.nextval,
4251 p_pb_input_header_id,
4252 l_context_tbl(k), l_attribute_tbl(k),
4253 l_attribute_value_tbl(k), l_attribute_type_tbl(k),
4254 l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id
4255 );
4256 EXCEPTION
4257 WHEN OTHERS THEN
4258 x_return_status := FND_API.G_RET_STS_ERROR;
4259 x_return_text := 'CATGI_POST_INSERT_PROCESSING: error while inserting lines - ' || SQLERRM;
4260 END;
4261 END IF;
4262
4263 END IF;
4264
4265 --x_pb_input_header_id := l_pb_input_header_id;
4266 x_return_status := FND_API.G_RET_STS_SUCCESS;
4267 x_return_text := '';
4268
4269 EXCEPTION
4270 WHEN NO_DATA_FOUND THEN
4271 x_return_status := FND_API.G_RET_STS_ERROR;
4272 x_return_text := 'Could not find price book ' || p_pb_input_header_id;
4273 WHEN OTHERS THEN
4274 x_return_status := FND_API.G_RET_STS_ERROR;
4275 x_return_text := 'CATGI_POST_INSERT_PROCESSING: general error - ' || SQLERRM;
4276 END CATGI_POST_INSERT_PROCESSING;
4277
4278 PROCEDURE CATGI_UPDATE_CUST_ACCOUNT_ID
4279 (
4280 p_pb_input_header_id IN NUMBER,
4281 p_cust_account_id IN NUMBER,
4282 x_return_status OUT NOCOPY VARCHAR2,
4283 x_return_text IN OUT NOCOPY VARCHAR2
4284 )
4285 IS
4286 BEGIN
4287 UPDATE QP_PB_INPUT_HEADERS_B
4288 SET CUST_ACCOUNT_ID = p_cust_account_id
4289 WHERE pb_input_header_id = p_pb_input_header_id;
4290 x_return_status := FND_API.G_RET_STS_SUCCESS;
4291 x_return_text := '';
4292 EXCEPTION
4293 WHEN NO_DATA_FOUND THEN
4294 x_return_status := FND_API.G_RET_STS_ERROR;
4295 x_return_text := 'Could not find price book ' || p_pb_input_header_id;
4296 WHEN OTHERS THEN
4297 x_return_status := FND_API.G_RET_STS_ERROR;
4298 x_return_text := 'CATGI_UPDATE_MISC: general error - ' || SQLERRM;
4299 END CATGI_UPDATE_CUST_ACCOUNT_ID;
4300
4301 ---------------------------------------------------------
4302
4303 FUNCTION GET_PTE_CODE(p_request_type_code VARCHAR2) RETURN VARCHAR2
4304 IS
4305 l_pte_code VARCHAR2(30);
4306 BEGIN
4307 select pte_code
4308 into l_pte_code
4309 from qp_pte_request_types_b
4310 where request_type_code = p_request_type_code;
4311 return l_pte_code;
4312 EXCEPTION
4313 when others then
4314 l_pte_code := 'ORDFUL';
4315 return l_pte_code;
4316 END GET_PTE_CODE;
4317
4318 -- bug 7034445
4319 ---------------------------------------------------------
4320
4321 -- SNIMMAGA.
4322 --
4323 -- Added implementation of this function.
4324
4325 FUNCTION Get_Processing_BatchSize RETURN NATURAL
4326 IS
4327 l_value NATURAL;
4328 BEGIN
4329 l_value := To_Number(
4330 fnd_profile.Value('QP_PRICEBOOK_PROCESSOR_BATCH_SIZE')
4331 );
4332 RETURN Nvl(l_value, 5000);
4333 EXCEPTION
4334 WHEN Others THEN
4335 RETURN 5000;
4336 END Get_Processing_BatchSize;
4337
4338
4339 END QP_PRICE_BOOK_UTIL;