[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.7 2009/11/30 05:04:48 jputta 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(240) := Null; --8721800
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(240) := Null; --8721800
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(240) := Null; --8721800
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 /** KDURGASI **/
2982
2983 FUNCTION get_content_type (p_document_type IN VARCHAR2) return VARCHAR2 IS
2984 l_mime VARCHAR2(50):= G_MIME_PDF;
2985 BEGIN
2986 IF (G_TYPE_PDF = p_document_type) THEN
2987 l_mime := G_MIME_PDF;
2988 ELSIF (G_TYPE_HTML = p_document_type) THEN
2989 l_mime := G_MIME_HTML;
2990 ELSIF (G_TYPE_EXCEL = p_document_type) THEN
2991 l_mime := G_MIME_EXCEL;
2992 ELSIF (G_TYPE_RTF = p_document_type) THEN
2993 l_mime := G_MIME_RTF;
2994 END IF;
2995 RETURN l_mime;
2996
2997 END;
2998
2999 FUNCTION get_document_name (p_pb_input_header_id IN NUMBER, p_document_type in varchar2) return VARCHAR2 IS
3000 l_extension VARCHAR2(50):= G_EXT_PDF;
3001 BEGIN
3002 IF (G_TYPE_PDF = p_document_type) THEN
3003 l_extension := G_EXT_PDF;
3004 ELSIF (G_TYPE_HTML = p_document_type) THEN
3005 l_extension := G_EXT_HTML;
3006 ELSIF (G_TYPE_EXCEL = p_document_type) THEN
3007 l_extension := G_EXT_EXCEL;
3008 ELSIF (G_TYPE_RTF = p_document_type) THEN
3009 l_extension := G_EXT_RTF;
3010 END IF;
3011 RETURN G_FILE_NAME_PREFIX || p_pb_input_header_id ||'.'||l_extension;
3012 END;
3013 /** KDURGASI **/
3014 ---------------------------------------------------------
3015
3016 PROCEDURE INSERT_PB_TL_RECORDS
3017 (
3018 p_pb_input_header_id IN VARCHAR2,
3019 p_price_book_name IN VARCHAR2,
3020 p_pl_agr_bsa_name IN VARCHAR2
3021 )
3022 IS
3023 BEGIN
3024 INSERT INTO QP_PB_INPUT_HEADERS_TL (
3025 PB_INPUT_HEADER_ID,
3026 CREATION_DATE,
3027 CREATED_BY,
3028 LAST_UPDATE_DATE,
3029 LAST_UPDATED_BY,
3030 LAST_UPDATE_LOGIN,
3031 PRICE_BOOK_NAME,
3032 PL_AGR_BSA_NAME,
3033 LANGUAGE,
3034 SOURCE_LANG
3035 ) SELECT
3036 p_pb_input_header_id,
3037 PBIH.CREATION_DATE,
3038 PBIH.CREATED_BY,
3039 PBIH.LAST_UPDATE_DATE,
3040 PBIH.LAST_UPDATED_BY,
3041 PBIH.LAST_UPDATE_LOGIN,
3042 p_price_book_name,
3043 p_pl_agr_bsa_name,
3044 L.LANGUAGE_CODE,
3045 userenv('LANG')
3046 FROM FND_LANGUAGES L, QP_PB_INPUT_HEADERS_B PBIH
3047 WHERE L.INSTALLED_FLAG in ('I', 'B')
3048 AND PBIH.PB_INPUT_HEADER_ID = p_pb_input_header_id
3049 AND NOT EXISTS
3050 (SELECT NULL
3051 FROM QP_PB_INPUT_HEADERS_TL T
3052 WHERE T.PB_INPUT_HEADER_ID = p_pb_input_header_id
3053 AND T.LANGUAGE = L.LANGUAGE_CODE);
3054 END INSERT_PB_TL_RECORDS;
3055
3056 PROCEDURE CATGI_HEADER_CONVERSIONS
3057 (
3058 p_org_id IN NUMBER,
3059 p_pricing_effective_date IN DATE,
3060 p_limit_products_by_code IN VARCHAR2,
3061 p_price_based_on_code IN VARCHAR2,
3062 p_customer_id IN VARCHAR2,
3063 p_item_number IN VARCHAR2,
3064 p_item_number_cust IN VARCHAR2,
3065 p_item_id IN VARCHAR2,
3066 p_item_category_name IN VARCHAR2,
3067 p_item_category_id IN VARCHAR2,
3068 p_price_list_name IN VARCHAR2,
3069 p_price_list_id IN VARCHAR2,
3070 p_agreement_name IN VARCHAR2,
3071 p_agreement_id IN VARCHAR2,
3072 p_bsa_name IN VARCHAR2,
3073 p_bsa_id IN VARCHAR2,
3074 x_prod_attr_value OUT NOCOPY VARCHAR2,
3075 x_pl_agr_bsa_id OUT NOCOPY VARCHAR2,
3076 x_pl_agr_bsa_name OUT NOCOPY VARCHAR2,
3077 x_return_status OUT NOCOPY VARCHAR2,
3078 x_return_text OUT NOCOPY VARCHAR2
3079 )
3080 IS
3081 BEGIN
3082
3083 --[julin] x_prod_attr_value - item number, item category
3084 IF (p_limit_products_by_code = 'ITEM') THEN
3085 IF (p_item_id is not null) THEN
3086 x_prod_attr_value := p_item_id;
3087 ELSIF (p_item_number is not null) THEN
3088 BEGIN
3089 SELECT inventory_item_id
3090 INTO x_prod_attr_value
3091 FROM mtl_system_items_vl
3092 WHERE concatenated_segments = p_item_number
3093 AND organization_id = p_org_id;
3094 EXCEPTION
3095 WHEN NO_DATA_FOUND THEN
3096 x_prod_attr_value := 'ITEM_LOOKUP_FAILED';
3097 x_return_status := FND_API.G_RET_STS_ERROR;
3098 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ITEM_NOT_FOUND');
3099 FND_MESSAGE.SET_TOKEN('ITEM_NUMBER', p_item_number);
3100 x_return_text := FND_MESSAGE.GET;
3101 WHEN TOO_MANY_ROWS THEN
3102 x_prod_attr_value := 'ITEM_LOOKUP_FAILED';
3103 x_return_status := FND_API.G_RET_STS_ERROR;
3104 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ITEM_MULTI_FOUND');
3105 FND_MESSAGE.SET_TOKEN('ITEM_NUMBER', p_item_number);
3106 x_return_text := FND_MESSAGE.GET;
3107 END;
3108 /*
3109 ELSIF (p_item_number_cust is not null) THEN
3110 BEGIN
3111 SELECT inventory_item_id
3112 INTO x_prod_attr_value
3113 FROM (
3114 SELECT Inventory_Item_Id
3115 FROM MTL_CUSTOMER_ITEM_XREFS x, MTL_CUSTOMER_ITEMS i
3116 WHERE i.customer_id = p_customer_id
3117 AND i.customer_item_number = p_item_number_cust
3118 AND i.Customer_Item_Id = x.customer_item_id
3119 AND x.Master_Organization_Id =
3120 (SELECT Master_Organization_Id
3121 FROM MTL_PARAMETERS
3122 WHERE Organization_Id = p_org_id)
3123 ORDER BY Preference_Number ASC)
3124 WHERE rownum = 1;
3125 EXCEPTION
3126 WHEN NO_DATA_FOUND Then
3127 x_prod_attr_value := 'CUST_ITEM_NUM_LOOKUP_FAILED';
3128 END;
3129 */
3130 END IF;
3131 ELSIF (p_limit_products_by_code = 'ITEM_CATEGORY') THEN
3132 IF (p_item_category_id is not null) THEN
3133 x_prod_attr_value := p_item_category_id;
3134 ELSIF (p_item_category_name is not null) THEN
3135 BEGIN
3136 SELECT distinct category_id
3137 INTO x_prod_attr_value
3138 FROM qp_item_categories_v
3139 WHERE category_name = p_item_category_name;
3140 EXCEPTION
3141 WHEN NO_DATA_FOUND THEN
3142 x_prod_attr_value := 'CATEGORY_LOOKUP_FAILED';
3143 x_return_status := FND_API.G_RET_STS_ERROR;
3144 FND_MESSAGE.SET_NAME('QP', 'QP_XML_CATEGORY_NOT_FOUND');
3145 FND_MESSAGE.SET_TOKEN('CATEGORY_NAME', p_item_category_name);
3146 x_return_text := FND_MESSAGE.GET;
3147 WHEN TOO_MANY_ROWS THEN
3148 x_prod_attr_value := 'CATEGORY_LOOKUP_FAILED';
3149 x_return_status := FND_API.G_RET_STS_ERROR;
3150 FND_MESSAGE.SET_NAME('QP', 'QP_XML_CATEGORY_MULTI_FOUND');
3151 FND_MESSAGE.SET_TOKEN('CATEGORY_NAME', p_item_category_name);
3152 x_return_text := FND_MESSAGE.GET;
3153 END;
3154 END IF;
3155 ELSIF (p_limit_products_by_code = 'ALL_ITEMS') THEN
3156 x_prod_attr_value := 'ALL';
3157 END IF;
3158
3159 --[julin] x_pl_agr_bsa_id - price list, agreement, bsa
3160 IF (p_price_based_on_code = 'PRICE_LIST') THEN
3161 IF (p_price_list_id is not null) THEN
3162 x_pl_agr_bsa_id := p_price_list_id;
3163 ELSIF (p_price_list_name is not null) THEN
3164 x_pl_agr_bsa_id := GET_PRICE_LIST_ID(p_price_list_name);
3165 IF x_pl_agr_bsa_id is null THEN
3166 x_return_status := FND_API.G_RET_STS_ERROR;
3167 FND_MESSAGE.SET_NAME('QP', 'QP_XML_PRICELIST_NOT_FOUND');
3168 FND_MESSAGE.SET_TOKEN('PRICELIST_NAME', p_price_list_name);
3169 x_return_text := FND_MESSAGE.GET;
3170 END IF;
3171 x_pl_agr_bsa_name := p_price_list_name;
3172 END IF;
3173 ELSIF (p_price_based_on_code = 'AGREEMENT') THEN
3174 IF (p_agreement_id is not null) THEN
3175 x_pl_agr_bsa_id := p_agreement_id;
3176 ELSIF (p_agreement_name is not null) THEN
3177 x_pl_agr_bsa_id := GET_AGREEMENT_ID(p_agreement_name, p_pricing_effective_date);
3178 IF x_pl_agr_bsa_id is null THEN
3179 x_return_status := FND_API.G_RET_STS_ERROR;
3180 FND_MESSAGE.SET_NAME('QP', 'QP_XML_AGREEMENT_NOT_FOUND');
3181 FND_MESSAGE.SET_TOKEN('AGREEMENT_NAME', p_agreement_name);
3182 FND_MESSAGE.SET_TOKEN('EFFECTIVE_DATE', p_pricing_effective_date);
3183 x_return_text := FND_MESSAGE.GET;
3184 END IF;
3185 x_pl_agr_bsa_name := p_agreement_name;
3186 END IF;
3187 ELSIF (p_price_based_on_code = 'BSA') THEN
3188 IF (p_bsa_id is not null) THEN
3189 x_pl_agr_bsa_id := p_bsa_id;
3190 ELSIF (p_bsa_name is not null) THEN
3191 x_pl_agr_bsa_id := GET_BSA_ID(p_bsa_name);
3192 IF x_pl_agr_bsa_id is null THEN
3193 x_return_status := FND_API.G_RET_STS_ERROR;
3194 FND_MESSAGE.SET_NAME('QP', 'QP_XML_BSA_NOT_FOUND');
3195 FND_MESSAGE.SET_TOKEN('BSA_NAME', p_bsa_name);
3196 x_return_text := FND_MESSAGE.GET;
3197 END IF;
3198 x_pl_agr_bsa_name := p_bsa_name;
3199 END IF;
3200 END IF;
3201
3202 END CATGI_HEADER_CONVERSIONS;
3203
3204 PROCEDURE GET_CONTEXT_CODE
3205 (
3206 p_context_name IN VARCHAR2,
3207 p_attribute_type IN VARCHAR2,
3208 x_context_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 c.prc_context_code
3215 INTO x_context_code
3216 FROM qp_prc_contexts_v c
3217 WHERE nvl(c.user_prc_context_name,c.seeded_prc_context_name) = p_context_name
3218 AND prc_context_type = p_attribute_type;
3219 EXCEPTION
3220 WHEN NO_DATA_FOUND THEN
3221 x_context_code := null;
3222 x_return_status := FND_API.G_RET_STS_ERROR;
3223 FND_MESSAGE.SET_NAME('QP', 'QP_XML_CONTEXT_NOT_FOUND');
3224 FND_MESSAGE.SET_TOKEN('CONTEXT_NAME', p_context_name);
3225 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3226 x_return_text := FND_MESSAGE.GET;
3227 WHEN TOO_MANY_ROWS THEN
3228 x_context_code := null;
3229 x_return_status := FND_API.G_RET_STS_ERROR;
3230 FND_MESSAGE.SET_NAME('QP', 'QP_XML_CONTEXT_MULTI_FOUND');
3231 FND_MESSAGE.SET_TOKEN('CONTEXT_NAME', p_context_name);
3232 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3233 x_return_text := FND_MESSAGE.GET;
3234 END GET_CONTEXT_CODE;
3235
3236 PROCEDURE GET_ATTRIBUTE_CODE
3237 (
3238 p_context_code IN VARCHAR2,
3239 p_attribute_name IN VARCHAR2,
3240 p_attribute_type IN VARCHAR2,
3241 x_attribute_code OUT NOCOPY VARCHAR2,
3242 x_return_status OUT NOCOPY VARCHAR2,
3243 x_return_text OUT NOCOPY VARCHAR2
3244 )
3245 IS
3246 BEGIN
3247 SELECT sb.segment_mapping_column
3248 INTO x_attribute_code
3249 FROM qp_prc_contexts_b p, qp_segments_b sb, qp_segments_tl stl
3250 WHERE p.prc_context_code = p_context_code
3251 AND p.prc_context_type = p_attribute_type
3252 AND sb.prc_context_id = p.prc_context_id
3253 AND stl.segment_id = sb.segment_id
3254 AND stl.language = userenv('LANG')
3255 AND nvl(stl.user_segment_name,stl.seeded_segment_name) = p_attribute_name;
3256 EXCEPTION
3257 WHEN NO_DATA_FOUND THEN
3258 x_attribute_code := null;
3259 x_return_status := FND_API.G_RET_STS_ERROR;
3260 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ATTRIBUTE_NOT_FOUND');
3261 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_NAME', p_attribute_name);
3262 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3263 FND_MESSAGE.SET_TOKEN('CONTEXT_CODE', p_context_code);
3264 x_return_text := FND_MESSAGE.GET;
3265 WHEN TOO_MANY_ROWS THEN
3266 x_attribute_code := null;
3267 x_return_status := FND_API.G_RET_STS_ERROR;
3268 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ATTRIBUTE_MULTI_FOUND');
3269 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_NAME', p_attribute_name);
3270 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3271 FND_MESSAGE.SET_TOKEN('CONTEXT_CODE', p_context_code);
3272 x_return_text := FND_MESSAGE.GET;
3273 END GET_ATTRIBUTE_CODE;
3274
3275 PROCEDURE GET_ATTRIBUTE_VALUE_CODE
3276 (
3277 p_context_code IN VARCHAR2,
3278 p_attribute_code IN VARCHAR2,
3279 p_attribute_value_name IN VARCHAR2,
3280 p_attribute_type IN VARCHAR2,
3281 x_attribute_value_code OUT NOCOPY VARCHAR2,
3282 x_return_status OUT NOCOPY VARCHAR2,
3283 x_return_text OUT NOCOPY VARCHAR2
3284 )
3285 IS
3286 Vset FND_VSET.valueset_r;
3287 Fmt FND_VSET.valueset_dr;
3288
3289 Found BOOLEAN;
3290 ROW NUMBER;
3291 VALUE FND_VSET.value_dr;
3292
3293 x_Format_Type VARCHAR2(1);
3294
3295 x_Validation_Type VARCHAR2(1);
3296 x_Vsid NUMBER;
3297 l_count NUMBER := 0;
3298 l_segment_code VARCHAR2(240);
3299 l_flexfield_name VARCHAR2(240);
3300
3301 BEGIN
3302 -- get segment_code from segment_mapping_column
3303 SELECT segment_code
3304 INTO l_segment_code
3305 FROM qp_prc_contexts_b p, qp_segments_b sb, qp_segments_tl stl
3306 WHERE p.prc_context_code = p_context_code
3307 AND p.prc_context_type = p_attribute_type
3308 AND sb.prc_context_id = p.prc_context_id
3309 AND stl.segment_id = sb.segment_id
3310 AND stl.language = userenv('LANG')
3311 AND sb.segment_mapping_column = p_attribute_code;
3312
3313 -- get flexfield name
3314 IF p_attribute_type = 'QUALIFIER' THEN
3315 l_flexfield_name := 'QP_ATTR_DEFNS_QUALIFIER';
3316 ELSIF p_attribute_type = 'PRICING_ATTRIBUTE' THEN
3317 l_flexfield_name := 'QP_ATTR_DEFNS_PRICING';
3318 END IF;
3319
3320 -- get valueset
3321 qp_util.get_valueset_id(l_FlexField_Name, p_Context_Code,
3322 l_segment_code, x_Vsid,
3323 x_Format_Type, x_Validation_Type);
3324
3325 IF x_Validation_Type IN('F', 'I') AND x_Vsid IS NOT NULL THEN
3326
3327 FND_VSET.get_valueset(x_Vsid, Vset, Fmt);
3328 FND_VSET.get_value_init(Vset, TRUE);
3329 FND_VSET.get_value(Vset, ROW, Found, VALUE);
3330
3331 IF Fmt.Has_Id THEN -- id defined, get id
3332 WHILE(Found) LOOP
3333 IF p_attribute_value_name = VALUE.VALUE THEN
3334 x_attribute_value_code := VALUE.id;
3335 l_count := l_count + 1;
3336 EXIT;
3337 END IF;
3338 FND_VSET.get_value(Vset, ROW, Found, VALUE);
3339 END LOOP;
3340 ELSE -- id not defined, get value
3341 WHILE(Found) LOOP
3342 IF p_attribute_value_name = VALUE.VALUE THEN
3343 x_attribute_value_code := p_attribute_value_name;
3344 l_count := l_count + 1;
3345 EXIT;
3346 END IF;
3347 FND_VSET.get_value(Vset, ROW, Found, VALUE);
3348 END LOOP;
3349 END IF; -- end of Fmt.Has_Id
3350
3351 FND_VSET.get_value_end(Vset);
3352
3353 IF l_count = 0 THEN
3354 RAISE NO_DATA_FOUND;
3355 ELSIF l_count > 1 THEN
3356 RAISE TOO_MANY_ROWS;
3357 END IF;
3358
3359 ELSE -- if validation type is not F or I or valueset id is null (not defined)
3360
3361 x_attribute_value_code := p_attribute_value_name;
3362
3363 END IF;
3364
3365 EXCEPTION
3366 WHEN NO_DATA_FOUND THEN
3367 x_attribute_value_code := null;
3368 x_return_status := FND_API.G_RET_STS_ERROR;
3369 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ATTR_VALUE_NOT_FOUND');
3370 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_NAME', p_attribute_value_name);
3371 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_CODE', p_attribute_code);
3372 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3373 FND_MESSAGE.SET_TOKEN('CONTEXT_CODE', p_context_code);
3374 x_return_text := FND_MESSAGE.GET;
3375 WHEN TOO_MANY_ROWS THEN
3376 x_attribute_value_code := null;
3377 x_return_status := FND_API.G_RET_STS_ERROR;
3378 FND_MESSAGE.SET_NAME('QP', 'QP_XML_ATTR_VALUE_MULTI_FOUND');
3379 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_VALUE_NAME', p_attribute_value_name);
3380 FND_MESSAGE.SET_TOKEN('ATTRIBUTE_CODE', p_attribute_code);
3381 FND_MESSAGE.SET_TOKEN('CONTEXT_TYPE', p_attribute_type);
3382 FND_MESSAGE.SET_TOKEN('CONTEXT_CODE', p_context_code);
3383 x_return_text := FND_MESSAGE.GET;
3384 END GET_ATTRIBUTE_VALUE_CODE;
3385
3386 PROCEDURE PUBLISH_AND_DELIVER_CP
3387 (
3388 err_buff OUT NOCOPY VARCHAR2,
3389 retcode OUT NOCOPY NUMBER,
3390 p_pb_input_header_id NUMBER,
3391 p_price_book_id NUMBER,
3392 p_servlet_url IN VARCHAR2
3393 )
3394 IS
3395 l_status VARCHAR2(30);
3396 l_status_text VARCHAR2(2000);
3397 BEGIN
3398 PUBLISH_AND_DELIVER(p_pb_input_header_id, p_price_book_id, p_servlet_url, l_status, l_status_text);
3399 IF l_status <> FND_API.G_RET_STS_SUCCESS THEN
3400 retcode := 2;
3401 err_buff := l_status_text;
3402 ELSE
3403 retcode := 0;
3404 err_buff := '';
3405 END IF;
3406 EXCEPTION
3407 WHEN OTHERS THEN
3408 retcode := 2;
3409 err_buff := l_status || ':' || l_status_text;
3410 END PUBLISH_AND_DELIVER_CP;
3411
3412 PROCEDURE PUBLISH_AND_DELIVER
3413 (
3414 p_pb_input_header_id IN NUMBER,
3415 p_price_book_header_id IN NUMBER,
3416 p_servlet_url IN VARCHAR2,
3417 x_return_status OUT NOCOPY VARCHAR2,
3418 x_return_status_text OUT NOCOPY VARCHAR2
3419 )
3420 IS
3421 L_MAX_STATUS_REQUESTS NUMBER:=240;
3422 L_STATUS_REQUEST_INTERVAL NUMBER:=10; -- seconds
3423 L_TRANSFER_TIMEOUT NUMBER:=3600; -- seconds
3424 l_routine VARCHAR2(240):='QP_PRICE_BOOK_UTIL.PUBLISH_AND_DELIVER';
3425 l_output_file VARCHAR2(240);
3426 l_debug VARCHAR2(3);
3427 l_url_servlet_string VARCHAR2(240);
3428 l_url_param_string VARCHAR2(240);
3429 l_return_status VARCHAR2(240);
3430 l_return_status_text VARCHAR2(2000);
3431 l_status_request_cnt NUMBER;
3432 l_dummy_return_details UTL_HTTP.HTML_PIECES;
3433 l_status_code VARCHAR(240);
3434
3435 err_buff VARCHAR2(240);
3436 retcode NUMBER;
3437
3438 INVALID_PARAMS_ERROR EXCEPTION;
3439 E_ROUTINE_ERRORS EXCEPTION;
3440 MAX_STATUS_REQUESTS_REACHED EXCEPTION;
3441 INVALID_PRICE_BOOK_HEADER EXCEPTION;
3442
3443 BEGIN
3444
3445 QP_PREQ_GRP.Set_QP_Debug;
3446 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
3447 IF l_debug = FND_API.G_TRUE THEN
3448 l_output_file := OE_DEBUG_PUB.SET_DEBUG_MODE('FILE');
3449 FND_FILE.PUT_LINE( FND_FILE.LOG, 'The output file is : ' || l_output_file );
3450 END IF;
3451
3452 IF (p_pb_input_header_id IS NULL or p_price_book_header_id IS NULL) THEN
3453 RAISE INVALID_PARAMS_ERROR;
3454 END IF;
3455
3456 BEGIN
3457 UPDATE QP_PRICE_BOOK_HEADERS_B
3458 SET PUB_STATUS_CODE = 'REQUESTED'
3459 WHERE PRICE_BOOK_HEADER_ID = p_price_book_header_id;
3460 COMMIT;
3461 EXCEPTION
3462 WHEN NO_DATA_FOUND THEN
3463 RAISE INVALID_PRICE_BOOK_HEADER;
3464 END;
3465
3466 l_url_servlet_string := nvl(p_servlet_url, fnd_profile.value('APPS_FRAMEWORK_AGENT') ||
3467 '/OA_HTML/RequestPriceBook');
3468 l_url_param_string := 'pbInputHeaderId='||nvl(p_pb_input_header_id, -1)||
3469 qp_java_engine_util_pub.G_HARD_CHAR||'priceBookHeaderId='||nvl(p_price_book_header_id, -1);
3470 qp_java_engine_util_pub.send_java_request(l_url_servlet_string,
3471 l_url_param_string,
3472 l_return_status,
3473 l_return_status_text,
3474 l_dummy_return_details,
3475 false,
3476 L_TRANSFER_TIMEOUT,
3477 FND_API.G_TRUE);
3478
3479 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3480 IF (l_return_status_text = 'UTL_TCP.END_OF_INPUT') THEN
3481 l_status_request_cnt := 0;
3482 BEGIN
3483 LOOP
3484 DBMS_LOCK.SLEEP(L_STATUS_REQUEST_INTERVAL);
3485
3486 SELECT PUB_STATUS_CODE
3487 INTO l_status_code
3488 FROM QP_PRICE_BOOK_HEADERS_B
3489 WHERE PRICE_BOOK_HEADER_ID = p_price_book_header_id;
3490
3491 IF l_status_code = 'ERROR' THEN
3492 RAISE E_ROUTINE_ERRORS;
3493 END IF;
3494 EXIT WHEN l_status_code = 'COMPLETED';
3495 IF l_status_request_cnt > L_MAX_STATUS_REQUESTS THEN
3496 RAISE MAX_STATUS_REQUESTS_REACHED;
3497 END IF;
3498 l_status_request_cnt := l_status_request_cnt + 1;
3499 END LOOP;
3500 EXCEPTION
3501 WHEN NO_DATA_FOUND THEN
3502 RAISE INVALID_PRICE_BOOK_HEADER;
3503 END;
3504 ELSE
3505 RAISE E_ROUTINE_ERRORS;
3506 END IF;
3507 END IF;
3508
3509 x_return_status := FND_API.G_RET_STS_SUCCESS;
3510
3511 EXCEPTION
3512 WHEN INVALID_PARAMS_ERROR THEN
3513 x_return_status := FND_API.G_RET_STS_ERROR;
3514 x_return_status_text := 'Invalid parameter values.';
3515 WHEN E_ROUTINE_ERRORS THEN
3516 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
3517 QP_PREQ_GRP.engine_debug(l_routine||'l_return_status_text:'||l_return_status_text);
3518 QP_PREQ_GRP.engine_debug(l_routine||'SQLERRM:'||SQLERRM);
3519 END IF;
3520 x_return_status := FND_API.G_RET_STS_ERROR;
3521 x_return_status_text := l_return_status_text;
3522 WHEN MAX_STATUS_REQUESTS_REACHED THEN
3523 x_return_status := FND_API.G_RET_STS_ERROR;
3524 x_return_status_text := 'Request has exceeded '||(L_MAX_STATUS_REQUESTS*L_STATUS_REQUEST_INTERVAL)||' seconds.';
3525 WHEN INVALID_PRICE_BOOK_HEADER THEN
3526 x_return_status := FND_API.G_RET_STS_ERROR;
3527 x_return_status_text := 'Invalid price book header id.';
3528 WHEN OTHERS THEN
3529 IF QP_PREQ_GRP.G_DEBUG_ENGINE = FND_API.G_TRUE THEN
3530 QP_PREQ_GRP.engine_debug(l_routine||'l_return_status_text:'||l_return_status_text);
3531 QP_PREQ_GRP.engine_debug(l_routine||'SQLERRM:'||SQLERRM);
3532 END IF;
3533 x_return_status := FND_API.G_RET_STS_ERROR;
3534 x_return_status_text := l_return_status_text;
3535 END PUBLISH_AND_DELIVER;
3536
3537 PROCEDURE SEND_SYNC_CATALOG
3538 (
3539 p_price_book_header_id IN NUMBER,
3540 x_return_status OUT NOCOPY VARCHAR2,
3541 x_return_status_text OUT NOCOPY VARCHAR2
3542 )
3543 IS
3544 x_progress VARCHAR2(1000);
3545 transaction_type VARCHAR2(240);
3546 transaction_subtype VARCHAR2(240);
3547 document_direction VARCHAR2(240);
3548 party_id NUMBER;
3549 party_site_id NUMBER;
3550 party_type VARCHAR2(30);
3551 return_code PLS_INTEGER;
3552 errmsg VARCHAR2(2000);
3553 result BOOLEAN;
3554 l_error_code NUMBER;
3555 l_error_msg VARCHAR2(2000);
3556
3557 -- parameters for raising event
3558 l_send_syct_event VARCHAR2(100);
3559 l_create_cln_event VARCHAR2(100);
3560 l_event_key VARCHAR2(100);
3561 l_syncctlg_seq NUMBER;
3562 l_send_syct_parameter_list wf_parameter_list_t;
3563 l_create_cln_parameter_list wf_parameter_list_t;
3564 l_operating_unit_id NUMBER;
3565 l_inv_org_id NUMBER;
3566 l_date DATE;
3567 l_canonical_date VARCHAR2(100);
3568
3569 -- parameters for dealing with the number of items restriction
3570 counter BINARY_INTEGER;
3571 msgs_sent_flag BOOLEAN;
3572
3573 l_debug VARCHAR2(3);
3574
3575 BEGIN
3576 -- set debug level
3577 QP_PREQ_GRP.Set_QP_Debug;
3578 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
3579
3580 IF (l_debug = FND_API.G_TRUE) THEN
3581 QP_PREQ_GRP.engine_debug('ENTERING QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG');
3582 END IF;
3583
3584 IF (l_debug = FND_API.G_TRUE) THEN
3585 QP_PREQ_GRP.engine_debug('With the following parameters:');
3586 QP_PREQ_GRP.engine_debug('p_price_book_header_id:' || p_price_book_header_id);
3587 END IF;
3588
3589 -- initialize parameters
3590 x_progress := '000';
3591 transaction_type := 'QP';
3592 transaction_subtype := 'CATSO';
3593 document_direction := 'OUT';
3594 party_type := 'C';
3595 result := FALSE;
3596
3597 l_send_syct_event := 'oracle.apps.qp.pricebook.catso';
3598 l_create_cln_event := 'oracle.apps.cln.ch.collaboration.create';
3599
3600 --transaction_type := 'CLN';
3601 --transaction_subtype := 'SYNCCTLGO';
3602 --l_send_syct_event := 'oracle.apps.cln.event.syncctlg';
3603
3604 l_send_syct_parameter_list := wf_parameter_list_t();
3605 l_create_cln_parameter_list := wf_parameter_list_t();
3606
3607 counter := 1;
3608 msgs_sent_flag := FALSE;
3609
3610 SELECT i.customer_attr_value, i.DLV_XML_SITE_ID
3611 INTO party_id, party_site_id
3612 FROM qp_price_book_headers_b p, qp_pb_input_headers_b i
3613 WHERE p.price_book_header_id = p_price_book_header_id
3614 AND p.pb_input_header_id = i.pb_input_header_id;
3615
3616 IF (l_debug = FND_API.G_TRUE) THEN
3617 QP_PREQ_GRP.engine_debug('party_id:' || party_id);
3618 QP_PREQ_GRP.engine_debug('party_site_id:' || party_site_id);
3619 END IF;
3620
3621 SELECT FND_PROFILE.VALUE('ORG_ID')
3622 INTO l_operating_unit_id
3623 FROM dual;
3624
3625 IF (l_debug = FND_API.G_TRUE) THEN
3626 QP_PREQ_GRP.engine_debug('l_operating_unit_id:' || l_operating_unit_id);
3627 END IF;
3628
3629 l_inv_org_id := qp_util.Get_Item_Validation_Org;
3630 IF (l_debug = FND_API.G_TRUE) THEN
3631 QP_PREQ_GRP.engine_debug('l_inv_org_id:' || l_inv_org_id);
3632 END IF;
3633
3634 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Parameters Initialized';
3635 IF (l_debug = FND_API.G_TRUE) THEN
3636 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3637 END IF;
3638
3639 -- XML Setup Check
3640 IF (l_debug = FND_API.G_TRUE) THEN
3641 QP_PREQ_GRP.engine_debug('Parameters before ecx_document.isDeliveryRequired:');
3642 QP_PREQ_GRP.engine_debug('transaction_type:' || transaction_type);
3643 QP_PREQ_GRP.engine_debug('transaction_subtype:' || transaction_subtype);
3644 QP_PREQ_GRP.engine_debug('party_id:' || party_id);
3645 QP_PREQ_GRP.engine_debug('party_site_id:' || party_site_id);
3646 QP_PREQ_GRP.engine_debug('return_code:' || return_code);
3647 QP_PREQ_GRP.engine_debug('errmsg:' || errmsg);
3648 END IF;
3649
3650 ecx_document.isDeliveryRequired(
3651 transaction_type => transaction_type,
3652 transaction_subtype => transaction_subtype,
3653 party_id => party_id,
3654 party_site_id => party_site_id,
3655 resultout => result,
3656 retcode => return_code,
3657 errmsg => errmsg);
3658
3659 IF (l_debug = FND_API.G_TRUE) THEN
3660 QP_PREQ_GRP.engine_debug('Values returned from ecx_document.isDeliveryRequired:');
3661 QP_PREQ_GRP.engine_debug('return_code:' || return_code);
3662 QP_PREQ_GRP.engine_debug('errmsg:' || errmsg);
3663 END IF;
3664
3665 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : XML Setup Check';
3666 IF (l_debug = FND_API.G_TRUE) THEN
3667 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3668 END IF;
3669
3670 IF NOT(result) THEN
3671 -- trading partner not found
3672 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : No Trading Partner found during XML Setup Check';
3673 IF (l_debug = FND_API.G_TRUE) THEN
3674 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3675 END IF;
3676
3677 ELSE -- no number specified, send in one message
3678
3679 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : No Number Limit Specified';
3680 IF (l_debug = FND_API.G_TRUE) THEN
3681 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3682 END IF;
3683
3684 -- create unique key
3685 SELECT QP_XML_MESSAGES_S.NEXTVAL INTO l_syncctlg_seq FROM dual;
3686 IF (l_debug = FND_API.G_TRUE) THEN
3687 QP_PREQ_GRP.engine_debug('l_syncctlg_seq:' || l_syncctlg_seq);
3688 END IF;
3689 l_event_key := to_char(p_price_book_header_id) || '.' || to_char(l_syncctlg_seq);
3690
3691 SELECT SYSDATE INTO l_date FROM dual;
3692 IF (l_debug = FND_API.G_TRUE) THEN
3693 QP_PREQ_GRP.engine_debug('l_date:' || l_date);
3694 END IF;
3695 l_canonical_date := FND_DATE.DATE_TO_CANONICAL(l_date);
3696
3697 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Created Unique Key';
3698 IF (l_debug = FND_API.G_TRUE) THEN
3699 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3700 END IF;
3701
3702 -- add parameters to list for create collaboration event
3703 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
3704 p_value => transaction_type,
3705 p_parameterlist => l_create_cln_parameter_list);
3706 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
3707 p_value => transaction_subtype,
3708 p_parameterlist => l_create_cln_parameter_list);
3709 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
3710 p_value => document_direction,
3711 p_parameterlist => l_create_cln_parameter_list);
3712 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
3713 p_value => l_event_key,
3714 p_parameterlist => l_create_cln_parameter_list);
3715 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
3716 p_value => party_id,
3717 p_parameterlist => l_create_cln_parameter_list);
3718 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
3719 p_value => party_site_id,
3720 p_parameterlist => l_create_cln_parameter_list);
3721 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
3722 p_value => party_type,
3723 p_parameterlist => l_create_cln_parameter_list);
3724 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
3725 p_value => l_event_key,
3726 p_parameterlist => l_create_cln_parameter_list);
3727 wf_event.AddParameterToList(p_name => 'ORG_ID',
3728 p_value => l_operating_unit_id,
3729 p_parameterlist => l_create_cln_parameter_list);
3730 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
3731 p_value => l_canonical_date,
3732 p_parameterlist => l_create_cln_parameter_list);
3733
3734 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Initialize Create Event Parameters';
3735 IF (l_debug = FND_API.G_TRUE) THEN
3736 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3737 end if;
3738
3739 -- raise create collaboration event
3740 wf_event.raise(p_event_name => l_create_cln_event,
3741 p_event_key => l_event_key,
3742 p_parameters => l_create_cln_parameter_list);
3743
3744 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Create Event Raised';
3745 IF (l_debug = FND_API.G_TRUE) THEN
3746 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3747 end if;
3748
3749 -- add parameters to list for send show shipment document
3750 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_TYPE',
3751 p_value => transaction_type,
3752 p_parameterlist => l_send_syct_parameter_list);
3753 wf_event.AddParameterToList(p_name => 'ECX_TRANSACTION_SUBTYPE',
3754 p_value => transaction_subtype,
3755 p_parameterlist => l_send_syct_parameter_list);
3756 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_TYPE',
3757 p_value => transaction_type,
3758 p_parameterlist => l_send_syct_parameter_list);
3759 wf_event.AddParameterToList(p_name => 'XMLG_INTERNAL_TXN_SUBTYPE',
3760 p_value => transaction_subtype,
3761 p_parameterlist => l_send_syct_parameter_list);
3762 wf_event.AddParameterToList(p_name => 'DOCUMENT_DIRECTION',
3763 p_value => document_direction,
3764 p_parameterlist => l_send_syct_parameter_list);
3765 wf_event.AddParameterToList(p_name => 'ECX_PARTY_ID',
3766 p_value => party_id,
3767 p_parameterlist => l_send_syct_parameter_list);
3768 wf_event.AddParameterToList(p_name => 'ECX_PARTY_SITE_ID',
3769 p_value => party_site_id,
3770 p_parameterlist => l_send_syct_parameter_list);
3771 wf_event.AddParameterToList(p_name => 'ECX_PARTY_TYPE',
3772 p_value => party_type,
3773 p_parameterlist => l_send_syct_parameter_list);
3774 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_ID',
3775 p_value => party_id,
3776 p_parameterlist => l_send_syct_parameter_list);
3777 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_SITE',
3778 p_value => party_site_id,
3779 p_parameterlist => l_send_syct_parameter_list);
3780 wf_event.AddParameterToList(p_name => 'TRADING_PARTNER_TYPE',
3781 p_value => party_type,
3782 p_parameterlist => l_send_syct_parameter_list);
3783 wf_event.AddParameterToList(p_name => 'ECX_DOCUMENT_ID',
3784 p_value => l_event_key,
3785 p_parameterlist => l_send_syct_parameter_list);
3786 wf_event.AddParameterToList(p_name => 'XMLG_DOCUMENT_ID',
3787 p_value => l_event_key,
3788 p_parameterlist => l_send_syct_parameter_list);
3789 wf_event.AddParameterToList(p_name => 'DOCUMENT_NO',
3790 p_value => l_event_key,
3791 p_parameterlist => l_send_syct_parameter_list);
3792 wf_event.AddParameterToList(p_name => 'USER_ID',
3793 p_value => fnd_global.user_id,
3794 p_parameterlist => l_send_syct_parameter_list);
3795 wf_event.AddParameterToList(p_name => 'APPLICATION_ID',
3796 p_value => fnd_global.resp_appl_id,
3797 p_parameterlist => l_send_syct_parameter_list);
3798 wf_event.AddParameterToList(p_name => 'RESPONSIBILITY_ID',
3799 p_value => fnd_global.resp_id,
3800 p_parameterlist => l_send_syct_parameter_list);
3801 wf_event.AddParameterToList(p_name => 'ORG_ID',
3802 p_value => l_inv_org_id,
3803 p_parameterlist => l_send_syct_parameter_list);
3804 wf_event.AddParameterToList(p_name => 'DOCUMENT_CREATION_DATE',
3805 p_value => l_canonical_date,
3806 p_parameterlist => l_send_syct_parameter_list);
3807 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER1',
3808 p_value => p_price_book_header_id,
3809 p_parameterlist => l_send_syct_parameter_list);
3810 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER2',
3811 p_value => NULL,
3812 p_parameterlist => l_send_syct_parameter_list);
3813 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER3',
3814 p_value => NULL,
3815 p_parameterlist => l_send_syct_parameter_list);
3816 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER4',
3817 p_value => NULL,
3818 p_parameterlist => l_send_syct_parameter_list);
3819 wf_event.AddParameterToList(p_name => 'ECX_PARAMETER5',
3820 p_value => NULL,
3821 p_parameterlist => l_send_syct_parameter_list);
3822
3823 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Send Document Event Parameters Initialized';
3824 IF (l_debug = FND_API.G_TRUE) THEN
3825 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3826 END IF;
3827
3828 -- raise event for send show shipment document
3829 wf_event.RAISE(p_event_name => l_send_syct_event,
3830 p_event_key => l_event_key,
3831 p_parameters => l_send_syct_parameter_list);
3832
3833 x_progress := 'QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG : Send Document Event Raised';
3834 IF (l_debug = FND_API.G_TRUE) THEN
3835 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3836 END IF;
3837
3838 END IF;
3839
3840 IF (l_debug = FND_API.G_TRUE) THEN
3841 QP_PREQ_GRP.engine_debug('EXITING QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG Successfully');
3842 END IF;
3843
3844 x_return_status := FND_API.G_RET_STS_SUCCESS;
3845
3846 EXCEPTION
3847 WHEN OTHERS THEN
3848 l_error_code := SQLCODE;
3849 l_error_msg := SQLERRM;
3850 IF (l_debug = FND_API.G_TRUE) THEN
3851 QP_PREQ_GRP.engine_debug('Exception ' || ':' || l_error_code || ':' || l_error_msg);
3852 END IF;
3853
3854 x_progress := 'EXITING QP_PRICE_BOOK_UTIL.SEND_SYNC_CATALOG in Error ';
3855 IF (l_debug = FND_API.G_TRUE) THEN
3856 QP_PREQ_GRP.engine_debug('Failure point ' || x_progress);
3857 END IF;
3858 x_return_status := FND_API.G_RET_STS_ERROR;
3859 x_return_status_text := l_error_msg;
3860 END SEND_SYNC_CATALOG;
3861
3862 PROCEDURE GENERATE_PUBLISH_PRICE_BOOK_WF
3863 (
3864 itemtype in VARCHAR2,
3865 itemkey in VARCHAR2,
3866 actid in NUMBER,
3867 funcmode in VARCHAR2,
3868 resultout in OUT NOCOPY VARCHAR2
3869 )
3870 IS
3871 l_pb_input_header_id VARCHAR2(240);
3872 l_pricing_perspective_code VARCHAR2(30);
3873 l_user_name VARCHAR2(240);
3874 l_status_code VARCHAR2(240);
3875 l_status_text VARCHAR2(240);
3876 l_request_id NUMBER;
3877 l_ret_code NUMBER;
3878 l_err_buf VARCHAR2(240);
3879 l_debug VARCHAR2(3);
3880 l_routine VARCHAR2(240):='QP_PRICE_BOOK_UTIL.GENERATE_PUBLISH_PRICE_BOOK_WF:';
3881 BEGIN
3882 l_pb_input_header_id := Wf_Engine.GetItemAttrText(itemtype, itemkey, 'PARAMETER1');
3883 IF (l_pb_input_header_id is null) THEN
3884 wf_core.token('PARAMETER1','NULL');
3885 wf_core.raise('WFSQL_ARGS');
3886 END IF;
3887
3888 /*
3889 l_pricing_perspective_code := Wf_Engine.GetItemAttrText(itemtype, itemkey, 'PARAMETER2');
3890 IF (l_pricing_perspective_code is null) THEN
3891 wf_core.token('PARAMETER2','NULL');
3892 wf_core.raise('WFSQL_ARGS');
3893 END IF;
3894 */
3895
3896 l_user_name := Wf_Engine.GetItemAttrText(itemtype, itemkey, 'PARAMETER4');
3897 IF (l_user_name is null) THEN
3898 wf_core.token('PARAMETER4','NULL');
3899 wf_core.raise('WFSQL_ARGS');
3900 END IF;
3901
3902 SET_XML_CONTEXT(l_user_name, l_status_code, l_status_text);
3903
3904 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
3905 IF l_debug = FND_API.G_TRUE THEN
3906 QP_PREQ_GRP.engine_debug(l_routine||'calling GENERATE_PUBLISH_PRICE_BOOK');
3907 END IF;
3908 QP_PRICE_BOOK_PVT.GENERATE_PUBLISH_PRICE_BOOK(l_pb_input_header_id,
3909 l_request_id,
3910 l_status_code,
3911 l_ret_code,
3912 l_err_buf);
3913 IF l_debug = FND_API.G_TRUE THEN
3914 QP_PREQ_GRP.engine_debug(l_routine||'returned from GENERATE_PUBLISH_PRICE_BOOK');
3915 END IF;
3916
3917 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
3918
3919 EXCEPTION
3920 WHEN OTHERS THEN
3921 Wf_Core.Context('QP_PRICE_BOOK_UTIL', 'GENERATE_PUBLISH_PRICE_BOOK_WF', itemtype, itemkey, to_char(actid), funcmode);
3922 raise;
3923 END GENERATE_PUBLISH_PRICE_BOOK_WF;
3924
3925 PROCEDURE CATSO_SELECTOR
3926 ( p_itemtype in varchar2,
3927 p_itemkey in varchar2,
3928 p_actid in number,
3929 p_funcmode in varchar2,
3930 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
3931 )
3932 IS
3933 l_user_id NUMBER;
3934 l_resp_id NUMBER;
3935 l_resp_appl_id NUMBER;
3936 l_org_id NUMBER;
3937 l_current_org_id NUMBER;
3938 l_client_org_id NUMBER;
3939 l_parameter1 NUMBER;
3940 l_debug VARCHAR2(3);
3941 l_application_code VARCHAR2(30);
3942 a varchar2(100);
3943
3944 BEGIN
3945
3946 QP_PREQ_GRP.Set_QP_Debug;
3947 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
3948
3949 IF l_debug = FND_API.G_TRUE THEN
3950 QP_PREQ_GRP.engine_debug( 'ENTERING CATSO_SELECTOR PROCEDURE' ) ;
3951 QP_PREQ_GRP.engine_debug( 'THE WORKFLOW FUNCTION MODE IS: FUNCMODE='||P_FUNCMODE ) ;
3952 END IF;
3953
3954 IF (p_funcmode = 'RUN') THEN
3955 IF l_debug = FND_API.G_TRUE THEN
3956 QP_PREQ_GRP.engine_debug( 'P_FUNCMODE IS RUN' ) ;
3957 END IF;
3958 p_x_result := 'COMPLETE';
3959 ELSIF(p_funcmode = 'TEST_CTX') THEN
3960 IF l_debug = FND_API.G_TRUE THEN
3961 QP_PREQ_GRP.engine_debug( 'P_FUNCMODE IS TEST_CTX' ) ;
3962 END IF;
3963
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_org_id (from workflow)=>'|| l_org_id ) ;
3970 QP_PREQ_GRP.engine_debug( 'mo_global.get_current_org_id =>'|| mo_global.get_current_org_id ) ;
3971 END IF;
3972
3973 IF (mo_global.get_current_org_id is null OR MO_GLOBAL.get_access_mode is null) THEN
3974 p_x_result := 'NOTSET';
3975 ELSE
3976 IF (NVL(mo_global.get_current_org_id,-99) <> l_Org_Id) THEN
3977 p_x_result := 'FALSE';
3978 ELSE
3979 p_x_result := 'TRUE';
3980 END IF;
3981 END IF;
3982
3983 ELSIF(p_funcmode = 'SET_CTX') THEN
3984 IF l_debug = FND_API.G_TRUE THEN
3985 QP_PREQ_GRP.engine_debug( 'P_FUNCMODE IS SET_CTX' ) ;
3986 END IF;
3987
3988 l_user_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3989 itemkey => p_itemkey,
3990 aname => 'USER_ID');
3991 l_resp_appl_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3992 itemkey => p_itemkey,
3993 aname => 'APPLICATION_ID');
3994 l_resp_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3995 itemkey => p_itemkey,
3996 aname => 'RESPONSIBILITY_ID');
3997 l_org_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
3998 itemkey => p_itemkey,
3999 aname => 'ORG_ID');
4000
4001 IF l_debug = FND_API.G_TRUE THEN
4002 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);
4003 END IF;
4004
4005 IF l_resp_appl_id is null OR l_resp_id is null THEN
4006 dbms_application_info.set_client_info(l_org_id);
4007 IF l_debug = FND_API.G_TRUE THEN
4008 QP_PREQ_GRP.engine_debug('set org using dbms_application_info.set_client_info');
4009 END IF;
4010 ELSE
4011 -- Set the database session context
4012 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
4013 /*
4014 BEGIN
4015 SELECT application_short_name
4016 INTO l_application_code
4017 FROM fnd_application
4018 WHERE application_id = fnd_global.resp_appl_id; --Responsibility of user
4019 EXCEPTION
4020 WHEN OTHERS THEN
4021 l_application_code := 'QP';
4022 END;
4023 */
4024 MO_GLOBAL.Init('QP');
4025 --mo_global.set_policy_context(p_access_mode => 'S', p_org_id=>l_Org_Id);
4026 END IF;
4027
4028 p_x_result := 'COMPLETE';
4029 END IF;
4030
4031 IF l_debug = FND_API.G_TRUE THEN
4032 QP_PREQ_GRP.engine_debug('p_x_result =>'||p_x_result);
4033 END IF;
4034
4035 EXCEPTION
4036 WHEN OTHERS THEN NULL;
4037 WF_CORE.Context('QP_PRICE_BOOK_UTIL', 'CATSO_SELECTOR',
4038 p_itemtype, p_itemkey, p_actid, p_funcmode);
4039 RAISE;
4040
4041 END CATSO_SELECTOR;
4042
4043 PROCEDURE SET_XML_CONTEXT
4044 (
4045 p_user_name IN VARCHAR2,
4046 x_return_status OUT NOCOPY VARCHAR2,
4047 x_return_text IN OUT NOCOPY VARCHAR2
4048 )
4049 IS
4050 l_user_id NUMBER;
4051 l_pricing_perspective_code VARCHAR(30);
4052 l_pricing_perspective_appl_id NUMBER;
4053 l_resp_id NUMBER;
4054 l_resp_appl_id NUMBER;
4055 l_resp_appl_name VARCHAR2(30);
4056 l_debug VARCHAR2(3);
4057 l_routine VARCHAR2(240):='QP_PRICE_BOOK_UTIL.SET_XML_CONTEXT';
4058 BEGIN
4059 -- get user based on user name
4060 SELECT user_id
4061 INTO l_user_id
4062 FROM fnd_user
4063 WHERE user_name = upper(p_user_name);
4064
4065 -- get pricing perpective based on user
4066 fnd_global.apps_initialize(l_user_id,
4067 null,
4068 null);
4069 l_pricing_perspective_code := FND_PROFILE.VALUE('QP_EXT_DEFAULT_PRICING_PERSPECTIVE');
4070 QP_PREQ_GRP.Set_QP_Debug;
4071 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
4072 IF l_debug = FND_API.G_TRUE THEN
4073 QP_PREQ_GRP.engine_debug(l_routine || ':l_user_id=' || l_user_id);
4074 QP_PREQ_GRP.engine_debug(l_routine || ':l_pricing_perspective_code=' || l_pricing_perspective_code);
4075 END IF;
4076
4077 -- get pricing perspective application id based on pricing perspective code
4078 SELECT a.application_id
4079 INTO l_pricing_perspective_appl_id
4080 FROM fnd_application a
4081 WHERE a.application_short_name = l_pricing_perspective_code;
4082 IF l_debug = FND_API.G_TRUE THEN
4083 QP_PREQ_GRP.engine_debug(l_routine || ':l_pricing_perspective_appl_id=' || l_pricing_perspective_appl_id);
4084 END IF;
4085
4086 -- get responsibility based on pricing perspective
4087 fnd_global.apps_initialize(l_user_id,
4088 null,
4089 l_pricing_perspective_appl_id);
4090 l_resp_id := FND_PROFILE.VALUE('QP_XML_RESP');
4091 IF l_debug = FND_API.G_TRUE THEN
4092 QP_PREQ_GRP.engine_debug(l_routine || ':l_resp_id=' || l_resp_id);
4093 END IF;
4094
4095 -- get application id and short name based on responsibility
4096 SELECT a.application_id, a.application_short_name
4097 INTO l_resp_appl_id, l_resp_appl_name
4098 FROM fnd_responsibility r, fnd_application a
4099 WHERE r.responsibility_id = l_resp_id
4100 AND a.application_id = r.application_id;
4101 IF l_debug = FND_API.G_TRUE THEN
4102 QP_PREQ_GRP.engine_debug(l_routine || ':l_resp_appl_id=' || l_resp_appl_id || ',l_resp_appl_name=' || l_resp_appl_name);
4103 END IF;
4104
4105 -- set context
4106 fnd_global.apps_initialize(l_user_id,
4107 l_resp_id,
4108 l_resp_appl_id);
4109 MO_GLOBAL.Init('QP');
4110
4111 EXCEPTION
4112 WHEN NO_DATA_FOUND THEN
4113 x_return_status := FND_API.G_RET_STS_ERROR;
4114 FND_MESSAGE.SET_NAME('QP', 'QP_XML_RESPONSIBILITY_REQUIRED');
4115 x_return_text := FND_MESSAGE.GET;
4116 END;
4117
4118 PROCEDURE CATGI_UPDATE_PUBLISH_OPTIONS
4119 (
4120 p_price_book_name IN VARCHAR2,
4121 p_customer_attr_value IN NUMBER,
4122 p_effective_date IN DATE,
4123 p_price_book_type_code IN VARCHAR2,
4124 p_dlv_xml_site_id IN NUMBER,
4125 p_generation_time_code IN VARCHAR2,
4126 p_gen_schedule_date IN DATE,
4127 x_pb_input_header_id OUT NOCOPY NUMBER,
4128 x_return_status OUT NOCOPY VARCHAR2,
4129 x_return_text IN OUT NOCOPY VARCHAR2
4130 )
4131 IS
4132 l_pb_input_header_id NUMBER;
4133 BEGIN
4134
4135 SELECT pb_input_header_id
4136 INTO l_pb_input_header_id
4137 FROM qp_pb_input_headers_vl
4138 WHERE price_book_name = p_price_book_name
4139 AND customer_attr_value = p_customer_attr_value
4140 --AND effective_date = p_effective_date
4141 AND price_book_type_code = p_price_book_type_code;
4142
4143 UPDATE QP_PB_INPUT_HEADERS_B
4144 SET PUB_TEMPLATE_CODE = NULL,
4145 PUB_LANGUAGE = NULL,
4146 PUB_TERRITORY = NULL,
4147 PUB_OUTPUT_DOCUMENT_TYPE = NULL,
4148 DLV_XML_FLAG = 'Y',
4149 DLV_XML_SITE_ID = p_dlv_xml_site_id,
4150 DLV_EMAIL_FLAG = 'N',
4151 DLV_EMAIL_ADDRESSES = NULL,
4152 DLV_PRINTER_FLAG = 'N',
4153 DLV_PRINTER_NAME = NULL,
4154 PUBLISH_EXISTING_PB_FLAG = 'Y',
4155 GENERATION_TIME_CODE = p_generation_time_code,
4156 GEN_SCHEDULE_DATE = p_gen_schedule_date,
4157 REQUEST_ORIGINATION_CODE = 'XML',
4158 LAST_UPDATE_DATE = SYSDATE,
4159 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
4160 WHERE pb_input_header_id = l_pb_input_header_id;
4161
4162 x_pb_input_header_id := l_pb_input_header_id;
4163 x_return_status := FND_API.G_RET_STS_SUCCESS;
4164 x_return_text := '';
4165
4166 EXCEPTION
4167 WHEN NO_DATA_FOUND THEN
4168 x_return_status := FND_API.G_RET_STS_ERROR;
4169 x_return_text := 'Could not find price book ' || p_price_book_name || ', ' || p_customer_attr_value || ', ' || p_effective_date;
4170 END CATGI_UPDATE_PUBLISH_OPTIONS;
4171
4172 PROCEDURE CATGI_POST_INSERT_PROCESSING
4173 (
4174 p_pb_input_header_id IN NUMBER,
4175 x_return_status OUT NOCOPY VARCHAR2,
4176 x_return_text IN OUT NOCOPY VARCHAR2
4177 )
4178 IS
4179 l_pb_input_header_id NUMBER;
4180 l_pb_input_header_rec qp_pb_input_headers_vl%ROWTYPE;
4181 l_full_pb_input_header_rec qp_pb_input_headers_vl%ROWTYPE;
4182
4183 l_context_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
4184 l_attribute_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
4185 l_attribute_value_tbl QP_PRICE_BOOK_UTIL.VARCHAR_TYPE;
4186 l_attribute_type_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
4187
4188 l_user_id NUMBER;
4189 l_login_id NUMBER;
4190 l_sysdate DATE;
4191
4192 l_cust_account_id NUMBER;
4193 BEGIN
4194 --Fetch the Price Book Input Header record into variable
4195 BEGIN
4196 SELECT *
4197 INTO l_pb_input_header_rec
4198 FROM qp_pb_input_headers_vl
4199 WHERE pb_input_header_id = p_pb_input_header_id;
4200 EXCEPTION
4201 WHEN OTHERS THEN
4202 x_return_status := FND_API.G_RET_STS_ERROR;
4203 x_return_text := 'CATGI_POST_INSERT_PROCESSING: pb not found - ' || SQLERRM;
4204 END;
4205
4206 IF l_pb_input_header_rec.cust_account_id is null THEN
4207 DEFAULT_CUST_ACCOUNT_ID(l_pb_input_header_rec.customer_attr_value,
4208 l_cust_account_id);
4209 IF l_cust_account_id is not null THEN
4210 UPDATE QP_PB_INPUT_HEADERS_B
4211 SET CUST_ACCOUNT_ID = l_cust_account_id
4212 WHERE pb_input_header_id = p_pb_input_header_id;
4213 END IF;
4214 END IF;
4215
4216 IF l_pb_input_header_rec.price_book_type_code = 'D' THEN
4217 --Fetch the Price Book Input Header record into variable
4218 BEGIN
4219 SELECT *
4220 INTO l_full_pb_input_header_rec
4221 FROM qp_pb_input_headers_vl
4222 WHERE price_book_name = l_pb_input_header_rec.price_book_name
4223 AND customer_attr_value = l_pb_input_header_rec.customer_attr_value
4224 AND customer_context = l_pb_input_header_rec.customer_context
4225 AND customer_attribute = l_pb_input_header_rec.customer_attribute
4226 AND price_book_type_code = 'F';
4227 EXCEPTION
4228 WHEN OTHERS THEN
4229 x_return_status := FND_API.G_RET_STS_ERROR;
4230 FND_MESSAGE.SET_NAME('QP', 'QP_FULL_PRICE_BOOK_MUST_EXIST');
4231 x_return_text := FND_MESSAGE.GET;
4232 RETURN;
4233 END;
4234
4235 UPDATE qp_pb_input_headers_b
4236 SET customer_context = l_full_pb_input_header_rec.customer_context,
4237 customer_attribute = l_full_pb_input_header_rec.customer_attribute,
4238 customer_attr_value = l_full_pb_input_header_rec.customer_attr_value,
4239 cust_account_id = l_full_pb_input_header_rec.cust_account_id,
4240 currency_code = l_full_pb_input_header_rec.currency_code,
4241 limit_products_by = l_full_pb_input_header_rec.limit_products_by,
4242 product_context = l_full_pb_input_header_rec.product_context,
4243 product_attribute = l_full_pb_input_header_rec.product_attribute,
4244 product_attr_value = l_full_pb_input_header_rec.product_attr_value,
4245 item_quantity = l_full_pb_input_header_rec.item_quantity,
4246 org_id = l_full_pb_input_header_rec.org_id,
4247 price_based_on = l_full_pb_input_header_rec.price_based_on,
4248 pl_agr_bsa_id = l_full_pb_input_header_rec.pl_agr_bsa_id,
4249 pricing_perspective_code = l_full_pb_input_header_rec.pricing_perspective_code,
4250 request_type_code = l_full_pb_input_header_rec.request_type_code
4251 WHERE pb_input_header_id = p_pb_input_header_id;
4252
4253 UPDATE qp_pb_input_headers_tl
4254 SET pl_agr_bsa_name = l_full_pb_input_header_rec.pl_agr_bsa_name
4255 WHERE pb_input_header_id = p_pb_input_header_id;
4256
4257 --Select the certain columns of input lines from the full price book
4258 BEGIN
4259 SELECT context, attribute, attribute_value, attribute_type
4260 BULK COLLECT INTO l_context_tbl, l_attribute_tbl,
4261 l_attribute_value_tbl, l_attribute_type_tbl
4262 FROM qp_pb_input_lines
4263 WHERE pb_input_header_id = l_full_pb_input_header_rec.pb_input_header_id;
4264 EXCEPTION
4265 WHEN NO_DATA_FOUND THEN
4266 null;
4267 END;
4268
4269 IF l_context_tbl.count > 0 THEN
4270 --Insert the Input criteria into input lines tables
4271 l_sysdate := sysdate;
4272 l_user_id := fnd_global.user_id;
4273 l_login_id := fnd_global.login_id;
4274 BEGIN
4275 FORALL k IN l_context_tbl.FIRST..l_context_tbl.LAST
4276 INSERT INTO qp_pb_input_lines
4277 (pb_input_line_id, pb_input_header_id,
4278 context, attribute, attribute_value,
4279 attribute_type, creation_date, created_by, last_update_date,
4280 last_updated_by, last_update_login
4281 )
4282 VALUES
4283 (qp_pb_input_lines_s.nextval,
4284 p_pb_input_header_id,
4285 l_context_tbl(k), l_attribute_tbl(k),
4286 l_attribute_value_tbl(k), l_attribute_type_tbl(k),
4287 l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id
4288 );
4289 EXCEPTION
4290 WHEN OTHERS THEN
4291 x_return_status := FND_API.G_RET_STS_ERROR;
4292 x_return_text := 'CATGI_POST_INSERT_PROCESSING: error while inserting lines - ' || SQLERRM;
4293 END;
4294 END IF;
4295
4296 END IF;
4297
4298 --x_pb_input_header_id := l_pb_input_header_id;
4299 x_return_status := FND_API.G_RET_STS_SUCCESS;
4300 x_return_text := '';
4301
4302 EXCEPTION
4303 WHEN NO_DATA_FOUND THEN
4304 x_return_status := FND_API.G_RET_STS_ERROR;
4305 x_return_text := 'Could not find price book ' || p_pb_input_header_id;
4306 WHEN OTHERS THEN
4307 x_return_status := FND_API.G_RET_STS_ERROR;
4308 x_return_text := 'CATGI_POST_INSERT_PROCESSING: general error - ' || SQLERRM;
4309 END CATGI_POST_INSERT_PROCESSING;
4310
4311 PROCEDURE CATGI_UPDATE_CUST_ACCOUNT_ID
4312 (
4313 p_pb_input_header_id IN NUMBER,
4314 p_cust_account_id IN NUMBER,
4315 x_return_status OUT NOCOPY VARCHAR2,
4316 x_return_text IN OUT NOCOPY VARCHAR2
4317 )
4318 IS
4319 BEGIN
4320 UPDATE QP_PB_INPUT_HEADERS_B
4321 SET CUST_ACCOUNT_ID = p_cust_account_id
4322 WHERE pb_input_header_id = p_pb_input_header_id;
4323 x_return_status := FND_API.G_RET_STS_SUCCESS;
4324 x_return_text := '';
4325 EXCEPTION
4326 WHEN NO_DATA_FOUND THEN
4327 x_return_status := FND_API.G_RET_STS_ERROR;
4328 x_return_text := 'Could not find price book ' || p_pb_input_header_id;
4329 WHEN OTHERS THEN
4330 x_return_status := FND_API.G_RET_STS_ERROR;
4331 x_return_text := 'CATGI_UPDATE_MISC: general error - ' || SQLERRM;
4332 END CATGI_UPDATE_CUST_ACCOUNT_ID;
4333
4334 ---------------------------------------------------------
4335
4336 FUNCTION GET_PTE_CODE(p_request_type_code VARCHAR2) RETURN VARCHAR2
4337 IS
4338 l_pte_code VARCHAR2(30);
4339 BEGIN
4340 select pte_code
4341 into l_pte_code
4342 from qp_pte_request_types_b
4343 where request_type_code = p_request_type_code;
4344 return l_pte_code;
4345 EXCEPTION
4346 when others then
4347 l_pte_code := 'ORDFUL';
4348 return l_pte_code;
4349 END GET_PTE_CODE;
4350
4351 ---------------------------------------------------------
4352
4353 -- SNIMMAGA.
4354 --
4355 -- Added implementation of this function.
4356
4357 FUNCTION Get_Processing_BatchSize RETURN NATURAL
4358 IS
4359 l_value NATURAL;
4360 BEGIN
4361 l_value := To_Number(
4362 fnd_profile.Value('QP_PRICEBOOK_PROCESSOR_BATCH_SIZE')
4363 );
4364 RETURN Nvl(l_value, 5000);
4365 EXCEPTION
4366 WHEN Others THEN
4367 RETURN 5000;
4368 END Get_Processing_BatchSize;
4369
4370 /** KDURGASI **/
4371 PROCEDURE GENERATE_PRICE_BOOK_XML
4372 (
4373 p_price_book_hdr_id IN NUMBER,
4374 p_document_content_type IN VARCHAR2,
4375 p_document_name IN VARCHAR2,
4376 x_return_status OUT NOCOPY VARCHAR2,
4377 x_return_text OUT NOCOPY VARCHAR2
4378 ) IS
4379 l_qryCtx DBMS_XMLQUERY.ctxHandle;
4380 l_st_time number;
4381 l_end_time number;
4382 l_result CLOB;
4383 l_doc_id number;
4384 BEGIN
4385 l_qryCtx := DBMS_XMLQUERY.newContext('SELECT XMLElement(
4386 "PriceBookHeadersVORow",
4387 XMLForest( PBHDR.PRICE_BOOK_HEADER_ID "PriceBookHeaderId",
4388 replace(to_char(hz_timezone_pub.convert_datetime(FND_PROFILE.VALUE(''SERVER_TIMEZONE_ID''),0,PBHDR.CREATION_DATE), ''YYYY-MM-DD HH24:MI:SS''), '' '', ''T'') "CreationDate",
4389 PBHDR.CREATED_BY "CreatedBy",
4390 replace(to_char(hz_timezone_pub.convert_datetime(FND_PROFILE.VALUE(''SERVER_TIMEZONE_ID''),0,PBHDR.LAST_UPDATE_DATE), ''YYYY-MM-DD HH24:MI:SS''), '' '', ''T'') "LastUpdateDate",
4391 PBHDR.LAST_UPDATED_BY "LastUpdatedBy",
4392 PBHDR.LAST_UPDATE_LOGIN "LastUpdateLogin",
4393 PBHDR.CUSTOMER_ID "CustomerId",
4394 PBHDR.CURRENCY_CODE "CurrencyCode",
4395 replace(to_char(hz_timezone_pub.convert_datetime(FND_PROFILE.VALUE(''SERVER_TIMEZONE_ID''),0,PBHDR.EFFECTIVE_DATE), ''YYYY-MM-DD HH24:MI:SS''), '' '', ''T'') "EffectiveDate",
4396 PBHDR.ITEM_QUANTITY "ItemQuantity",
4397 PBHDR.REQUEST_ID "RequestId",
4398 PBHDR.ORG_ID "OrgId",
4399 PBHDR.OPERATING_UNIT "OperatingUnit",
4400 PBHDR.PRICE_BOOK_TYPE_CODE "PriceBookTypeCode",
4401 PBHDR.REQUEST_TYPE_CODE "RequestTypeCode",
4402 PBHDR.PRICE_BOOK_NAME "PriceBookName",
4403 PBHDR.CUSTOMER_NAME "CustomerName",
4404 PBHDR.ITEM_CATEGORY "ItemCategory",
4405 PBHDR.PB_INPUT_HEADER_ID "PbInputHeaderId",
4406 PBHDR.PRICE_BOOK_TYPE "PriceBookType",
4407 PBHDR.CURRENCY "Currency",
4408 PBHDR.PRICING_PERSPECTIVE_CODE "PricingPerspectiveCode",
4409 PBHDR.PL_AGR_BSA_ID "PlAgrBsaId",
4410 PBHDR.PL_AGR_BSA_NAME "PlAgrBsaName",
4411 PBHDR.LANGUAGE "Language",
4412 PBHDR.SOURCE_LANG "SourceLang",
4413 PBHDR.PRICE_BASED_ON "PriceBasedOn",
4414 PBHDR.CUST_ACCOUNT_ID "CustAccountId"),
4415 XMLElement(
4416 "PBInputHeadersVO",
4417 (SELECT XMLAgg(
4418 XMLElement(
4419 "PBInputHeadersVORow",
4420 XMLForest(PBInputHDR.PB_INPUT_HEADER_ID "PbInputHeaderId",
4421 PBInputHDR.CUSTOMER_CONTEXT "CustomerContext",
4422 PBInputHDR.CUSTOMER_ATTRIBUTE "CustomerAttribute",
4423 PBInputHDR.CUSTOMER_ATTR_VALUE "CustomerAttrValue",
4424 PBInputHDR.CURRENCY_CODE "CurrencyCode",
4425 PBInputHDR.PRODUCT_CONTEXT "ProductContext",
4426 PBInputHDR.PRODUCT_ATTRIBUTE "ProductAttribute",
4427 PBInputHDR.PRODUCT_ATTR_VALUE "ProductAttrValue",
4428 replace(to_char(hz_timezone_pub.convert_datetime(FND_PROFILE.VALUE(''SERVER_TIMEZONE_ID''),0,PBInputHDR.EFFECTIVE_DATE), ''YYYY-MM-DD HH24:MI:SS''), '' '', ''T'') "EffectiveDate",
4429 PBInputHDR.ITEM_QUANTITY "ItemQuantity",
4430 PBInputHDR.GENERATION_TIME_CODE "GenerationTimeCode",
4431 PBInputHDR.GEN_SCHEDULE_DATE "GenScheduleDate",
4432 PBInputHDR.REQUEST_ID "RequestId",
4433 PBInputHDR.ORG_ID "OrgId",
4434 PBInputHDR.OPERATING_UNIT "OperatingUnit",
4435 PBInputHDR.PRICE_BOOK_TYPE_CODE "PriceBookTypeCode",
4436 PBInputHDR.PUBLISH_EXISTING_PB_FLAG "PublishExistingPbFlag",
4437 PBInputHDR.REQUEST_TYPE_CODE "RequestTypeCode",
4438 PBInputHDR.PRICE_BOOK_NAME "PriceBookName",
4439 PBInputHDR.CUSTOMER_NAME "CustomerName",
4440 PBInputHDR.PRODUCT_NAME "ProductName",
4441 PBInputHDR.GENERATION_TIME "GenerationTime",
4442 PBInputHDR.PRICE_BOOK_TYPE "PriceBookType",
4443 PBInputHDR.PRODUCT_ATTRIBUTE_NAME "ProductAttributeName",
4444 PBInputHDR.CUSTOMER_ATTRIBUTE_NAME "CustomerAttributeName",
4445 PBInputHDR.VALIDATION_ERROR_FLAG "ValidationErrorFlag",
4446 PBInputHDR.PRICING_PERSPECTIVE_CODE "PricingPerspectiveCode",
4447 PBInputHDR.OVERWRITE_EXISTING_PB_FLAG "OverwriteExistingPbFlag",
4448 PBInputHDR.CURRENCY "Currency",
4449 PBInputHDR.LIMIT_PRODUCTS_BY "LimitProductsBy",
4450 PBInputHDR.PRICE_BASED_ON "PriceBasedOn",
4451 PBInputHDR.PL_AGR_BSA_ID "PlAgrBsaId",
4452 PBInputHDR.LIMIT_PRODUCTS_BY_NAME "LimitProductsByName",
4453 PBInputHDR.PRICE_BASED_ON_NAME "PriceBasedOnName",
4454 PBInputHDR.PL_AGR_BSA_NAME "PlAgrBsaName",
4455 PBInputHDR.PUB_TEMPLATE_CODE "PubTemplateCode",
4456 PBInputHDR.PUB_LANGUAGE "PubLanguage",
4457 PBInputHDR.PUB_TERRITORY "PubTerritory",
4458 PBInputHDR.PUB_OUTPUT_DOCUMENT_TYPE "PubOutputDocumentType",
4459 PBInputHDR.DLV_XML_FLAG "DlvXmlFlag",
4460 PBInputHDR.DLV_EMAIL_FLAG "DlvEmailFlag",
4461 PBInputHDR.DLV_EMAIL_ADDRESSES "DlvEmailAddresses",
4462 PBInputHDR.DLV_PRINTER_FLAG "DlvPrinterFlag",
4463 PBInputHDR.DLV_PRINTER_NAME "DlvPrinterName",
4464 PBInputHDR.PRICING_PERSPECTIVE "PricingPerspective"),
4465 XMLElement(
4466 "PBInputLinesVO",
4467 (SELECT XMLAgg(
4468 XMLElement(
4469 "PBInputLinesVORow",
4470 XMLForest(PBInputLIN.PB_INPUT_LINE_ID "PbInputLineId",
4471 PBInputLIN.PB_INPUT_HEADER_ID "PbInputHeaderId",
4472 PBInputLIN.CONTEXT "Context",
4473 PBInputLIN.ATTRIBUTE "Attribute",
4474 PBInputLIN.ATTRIBUTE_VALUE "AttributeValue",
4475 PBInputLIN.ATTRIBUTE_TYPE "AttributeType",
4476 PBInputLIN.CONTEXT_NAME "ContextName",
4477 PBInputLIN.ATTRIBUTE_NAME "AttributeName",
4478 PBInputLIN.ATTRIBUTE_VALUE_NAME "AttributeValueName",
4479 PBInputLIN.ATTRIBUTE_TYPE_VALUE "AttributeTypeValue",
4480 QP_Price_Book_Util.value_to_meaning(''='',''COMPARISON_OPERATOR_FWK'') "OperatorCodeName")
4481 )
4482 )
4483 FROM QP_PB_INPUT_LINES_V PBInputLIN
4484 WHERE PBInputLIN.Pb_Input_Header_Id = PBInputHDR.Pb_Input_Header_Id
4485 )
4486 )
4487 )
4488 )
4489 FROM QP_PB_INPUT_HEADERS_V PBInputHDR
4490 WHERE PBInputHDR.Pb_Input_Header_Id = PBHDR.Pb_Input_Header_Id
4491 )
4492 ),
4493 XMLElement(
4494 "PriceBookLinesVO",
4495 (SELECT XMLAgg(
4496 XMLElement(
4497 "PriceBookLinesVORow",
4498 XMLForest(PBLin.PRICE_BOOK_LINE_ID "PriceBookLineId",
4499 PBLin.PRICE_BOOK_HEADER_ID "PriceBookHeaderId",
4500 PBLin.ITEM_NUMBER "ItemNumber",
4501 PBLin.PRODUCT_UOM_CODE "ProductUomCode",
4502 PBLin.LIST_PRICE "ListPrice",
4503 PBLin.NET_PRICE "NetPrice",
4504 PBLin.SYNC_ACTION_CODE "SyncActionCode",
4505 PBLin.LINE_STATUS_CODE "LineStatusCode",
4506 PBLin.DESCRIPTION "Description",
4507 PBLin.CUSTOMER_ITEM_NUMBER "CustomerItemNumber",
4508 PBLin.DISPLAY_ITEM_NUMBER "DisplayItemNumber",
4509 PBLin.SYNC_ACTION "SyncAction",
4510 nvl(PBLin.CUSTOMER_ITEM_NUMBER,PBLin.DISPLAY_ITEM_NUMBER) "UiItemNumber",
4511 PBLin.CUSTOMER_ITEM_DESC "CustomerItemDesc",
4512 to_char(PBLin.LIST_PRICE,FND_CURRENCY.GET_FORMAT_MASK(PBHDR.CURRENCY_CODE,60)) "ListPriceDisp",
4513 to_char(PBLin.NET_PRICE,FND_CURRENCY.GET_FORMAT_MASK(PBHDR.CURRENCY_CODE,60)) "NetPriceDisp"),
4514 XMLElement(
4515 "PriceBookLineDetailsVO",
4516 (SELECT XMLAgg(
4517 XMLElement(
4518 "PriceBookLineDetailsVORow",
4519 XMLForest(PBLinDet.PRICE_BOOK_LINE_ID "PriceBookLineId",
4520 PBLinDet.PRICE_BOOK_HEADER_ID "PriceBookHeaderId",
4521 PBLinDet.LIST_PRICE "ListPrice",
4522 PBLinDet.ADJUSTED_NET_PRICE "AdjustedNetPrice",
4523 PBLinDet.PRICE_BOOK_LINE_DET_ID "PriceBookLineDetId",
4524 PBLinDet.LIST_HEADER_ID "ListHeaderId",
4525 PBLinDet.LIST_LINE_ID "ListLineId",
4526 PBLinDet.LIST_LINE_NO "ListLineNo",
4527 PBLinDet.MODIFIER_OPERAND "ModifierOperand",
4528 PBLinDet.MODIFIER_APPLICATION_METHOD "ModifierApplicationMethod",
4529 PBLinDet.ADJUSTMENT_AMOUNT "AdjustmentAmount",
4530 PBLinDet.LIST_LINE_TYPE_CODE "ListLineTypeCode",
4531 PBLinDet.PRICE_BREAK_TYPE_CODE "PriceBreakTypeCode",
4532 PBLinDet.LIST_NAME "ListName",
4533 PBLinDet.LIST_LINE_TYPE "ListLineType",
4534 PBLinDet.PRICE_BREAK_TYPE "PriceBreakType",
4535 DECODE((SELECT ''X''
4536 from dual
4537 where exists(SELECT ''X''
4538 from QP_PRICE_BOOK_ATTRIBUTES_V pba
4539 where pba.PRICE_BOOK_LINE_DET_ID = PBLinDet.PRICE_BOOK_LINE_DET_ID
4540 and pba.PRICE_BOOK_LINE_ID = PBLinDet.PRICE_BOOK_LINE_ID)),''X'',''PricingAttrEnabled'',''PricingAttrDisabled'') "PricingAttribute",
4541 DECODE((SELECT ''X''
4542 from dual
4543 where exists(SELECT ''X''
4544 from QP_PRICE_BOOK_BREAK_LINES_V pbb
4545 where pbb.PRICE_BOOK_LINE_DET_ID= PBLinDet.PRICE_BOOK_LINE_DET_ID
4546 and pbb.PRICE_BOOK_LINE_ID = PBLinDet.PRICE_BOOK_LINE_ID)),''X'',''BreaksEnabled'',''BreaksDisabled'') "Breaks",
4547 ''MessageCheck'' "Messages",
4548 to_char(PBLinDet.LIST_PRICE,FND_CURRENCY.GET_FORMAT_MASK(PBHDR.CURRENCY_CODE,60)) "ListPriceDisp",
4549 to_char(PBLinDet.ADJUSTED_NET_PRICE,FND_CURRENCY.GET_FORMAT_MASK(PBHDR.CURRENCY_CODE,60)) "AdjustedNetPriceDisp"),
4550 DECODE(PBLinDet.LIST_LINE_TYPE_CODE,''PBH'',
4551 XMLElement(
4552 "PriceBookBreakLinesVO",
4553 (SELECT XMLAgg(
4554 XMLElement(
4555 "PriceBookBreakLinesVORow",
4556 XMLForest(pbk.PRICE_BOOK_LINE_DET_ID "PriceBookLineDetId",
4557 pbk.COMPARISON_OPERATOR_NAME "ComparisonOperatorName",
4558 pbk.ATTRIBUTE_NAME "AttributeName",
4559 pbk.PRICING_ATTR_VALUE_FROM "PricingAttrValueFrom",
4560 pbk.PRICING_ATTR_VALUE_TO "PricingAttrValueTo",
4561 decode(LD.LIST_LINE_NO, null, fnd_message.get_string(''QP'',''QP_PRICE_BOOK_LISTPRICE''), fnd_message.get_string(''QP'',''QP_PRICE_BOOK_ADDITIONAL''))
4562 ||'' ''
4563 ||pbk.ATTRIBUTE_NAME
4564 ||'' ''
4565 ||decode(pbk.PRICING_ATTR_VALUE_TO,
4566 999999999999999,fnd_message.get_string(''QP'',''QP_PRICE_BOOK_PBH_GREATER'')||'' ''||pbk.PRICING_ATTR_VALUE_FROM,
4567 decode(pll.continuous_price_break_flag,
4568 ''Y'', fnd_message.get_string(''QP'',''QP_PRICE_BOOK_COMPARISON''),
4569 pbk.COMPARISON_OPERATOR_NAME)||'' ''|| pbk.PRICING_ATTR_VALUE_FROM||'' ''
4570 || decode(pll.continuous_price_break_flag, ''Y'',
4571 fnd_message.get_string(''QP'',''QP_PRICE_BOOK_MORE''),fnd_message.get_string(''QP'',''QP_PRICE_BOOK_AND''))||'' ''|| pbk.PRICING_ATTR_VALUE_TO
4572 ) "Description",
4573 LD.LIST_LINE_NO "ModifierNumber",
4574 pbk.OPERAND "Operand",
4575 pbk.APPLICATION_METHOD_NAME "ApplicationMethodName",
4576 pbk.RECURRING_VALUE "RecurringValue")
4577 )
4578 )
4579 FROM QP_PRICE_BOOK_LINE_DETAILS_V LD, QP_PRICE_BOOK_BREAK_LINES_V pbk, qp_list_lines pll
4580 WHERE LD.PRICE_BOOK_LINE_DET_ID = pbk.PRICE_BOOK_LINE_DET_ID
4581 AND ld.list_line_id = pll.list_line_id
4582 AND LD.PRICE_BOOK_LINE_DET_ID = PBLinDet.PRICE_BOOK_LINE_DET_ID
4583 )
4584 )
4585 ,
4586 NULL)
4587 )
4588 )
4589 FROM QP_PRICE_BOOK_LINE_DETAILS_V PBLinDet
4590 WHERE PBLinDet.Price_Book_Line_Id = PBLin.Price_Book_Line_Id
4591 )
4592 ),
4593 DECODE((SELECT ''X''
4594 from dual
4595 where exists(SELECT ''X''
4596 from QP_PRICE_BOOK_MESSAGES_V pbm
4597 where pbm.Price_Book_Line_Id = PBLin.Price_Book_Line_Id )),
4598 ''X'',
4599 XMLElement(
4600 "PriceBookMessagesVO",
4601 (SELECT XMLAgg(
4602 XMLElement(
4603 "PriceBookMessagesVORow",
4604 XMLForest(QPPBMSGS.MESSAGE_ID "MessageId",
4605 QPPBMSGS.MESSAGE_TYPE "MessageType",
4606 QPPBMSGS.MESSAGE_CODE "MessageCode",
4607 QPPBMSGS.MESSAGE_TEXT "MessageText",
4608 QPPBMSGS.PB_INPUT_HEADER_ID "PbInputHeaderId",
4609 QPPBMSGS.PRICE_BOOK_HEADER_ID "PriceBookHeaderId",
4610 QPPBMSGS.PRICE_BOOK_LINE_ID "PriceBookLineId")
4611 )
4612 )
4613 FROM QP_PRICE_BOOK_MESSAGES_V QPPBMSGS
4614 WHERE QPPBMSGS.Price_Book_Line_Id = PBLin.Price_Book_Line_Id
4615
4616 )
4617 )
4618 ,NULL),
4619 XMLElement(
4620 "PriceBookLineCatsVO",
4621 (SELECT XMLAgg(
4622 XMLElement(
4623 "PriceBookLineCatsVORow",
4624 XMLForest(QPPBATTRS.price_book_line_id "PriceBookLineId",
4625 QPPBATTRS.attribute_value_name "CategoryName",
4626 QPPBATTRS.PRICING_PROD_ATTR_VALUE_FROM "CategoryId")
4627 )
4628 )
4629 FROM QP_PRICE_BOOK_ATTRIBUTES_V QPPBATTRS
4630 WHERE QPPBATTRS.PRICE_BOOK_LINE_DET_ID = -1
4631 AND QPPBATTRS.ATTRIBUTE_TYPE = ''PRODUCT''
4632 AND QPPBATTRS.PRICING_PROD_CONTEXT = ''ITEM''
4633 AND QPPBATTRS.PRICING_PROD_ATTRIBUTE = ''PRICING_ATTRIBUTE2''
4634 AND QPPBATTRS.Price_Book_Line_Id = PBLin.Price_Book_Line_Id
4635
4636 )
4637 )
4638 )
4639 )
4640 FROM QP_PRICE_BOOK_LINES_V PBLin
4641 WHERE PBLin.Price_Book_Header_Id = PBHDR.Price_Book_Header_Id
4642 )
4643 )
4644 ) as "PriceBookHeadersVO"
4645 FROM QP_PRICE_BOOK_HEADERS_V PBHDR
4646 WHERE PRICE_BOOK_HEADER_ID = :PBHDRID');
4647 -- Set the row header to be QP_PRICE_BOOK
4648 DBMS_XMLQUERY.setRowSetTag(l_qryCtx, 'QP_PRICE_BOOK');
4649 DBMS_XMLQUERY.setRowTag(l_qryCtx, NULL);
4650
4651 DBMS_XMLQUERY.setBindValue(l_qryCtx, 'PBHDRID', p_price_book_hdr_id);
4652 --DBMS_XMLQUERY.setNullHandling(l_qryCtx, DBMS_XMLQUERY.DROP_NULLS);
4653 -- Get the result
4654 l_st_time := dbms_utility.get_time;
4655 DBMS_XMLQUERY.SETENCODINGTAG(l_qryCtx,'UTF-8');
4656 l_result := DBMS_XMLQUERY.getXML(l_qryCtx);
4657
4658
4659 l_end_time := dbms_utility.get_time;
4660 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Time Taken for Creation of XML: '||((l_end_time-l_st_time)/100));
4661
4662 --delete from qp_xml_documents where PRICE_BOOK_HEADER_ID=p_price_book_hdr_id;
4663 DELETE FROM QP_DOCUMENTS
4664 WHERE DOCUMENT_ID = (SELECT DOCUMENT_ID
4665 FROM qp_price_book_headers_b
4666 WHERE PRICE_BOOK_HEADER_ID = p_price_book_hdr_id);
4667
4668 --INSERT INTO qp_xml_documents VALUES(p_price_book_hdr_id,result);
4669 INSERT INTO QP_DOCUMENTS(
4670 DOCUMENT_ID,
4671 DOCUMENT_CONTENT,
4672 DOCUMENT_CONTENT_TYPE,
4673 DOCUMENT_NAME,
4674 CREATION_DATE,
4675 CREATED_BY,
4676 LAST_UPDATE_DATE,
4677 LAST_UPDATED_BY,
4678 LAST_UPDATE_LOGIN,
4679 XML_CONTENT
4680 )
4681 VALUES(
4682 qp_price_book_messages_s.nextval,
4683 EMPTY_BLOB(),
4684 p_document_content_type,
4685 p_document_name,
4686 sysdate,
4687 fnd_global.user_id,
4688 sysdate,
4689 fnd_global.user_id,
4690 fnd_global.conc_login_id,
4691 l_result
4692 ) RETURNING DOCUMENT_ID INTO l_doc_id;
4693
4694 UPDATE qp_price_book_headers_b
4695 SET document_id=l_doc_id
4696 WHERE PRICE_BOOK_HEADER_ID = p_price_book_hdr_id;
4697
4698 --Close context
4699 DBMS_XMLQUERY.closeContext(l_qryCtx);
4700
4701 x_return_status := FND_API.G_RET_STS_SUCCESS;
4702
4703 COMMIT;
4704 EXCEPTION
4705 WHEN OTHERS
4706 THEN
4707 FND_FILE.PUT_LINE( FND_FILE.LOG, 'error message:' || SQLERRM );
4708 x_return_status := FND_API.G_RET_STS_ERROR;
4709 x_return_text := 'Error in XML Generation: '||SQLERRM;
4710 END GENERATE_PRICE_BOOK_XML;
4711 /** KDURGASI **/
4712
4713 END QP_PRICE_BOOK_UTIL;