[Home] [Help]
PACKAGE BODY: APPS.QP_PRICE_BOOK_PUB
Source
1 PACKAGE BODY QP_PRICE_BOOK_PUB AS
2 /*$Header: QPXPPRBB.pls 120.17 2006/04/27 15:13 rchellam noship $*/
3
4 --Global constant holding the package name
8 Public API to Create and Publish Full/Delta Price Book
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_PRICE_BOOK_PUB';
6
7 /*****************************************************************************
9 *****************************************************************************/
10 PROCEDURE Create_Publish_Price_Book(
11 p_pb_input_header_rec IN pb_input_header_rec,
12 p_pb_input_lines_tbl IN pb_input_lines_tbl,
13 x_request_id OUT NOCOPY NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_retcode OUT NOCOPY NUMBER,
16 x_err_buf OUT NOCOPY VARCHAR2,
17 x_price_book_messages_tbl OUT NOCOPY price_book_messages_tbl)
18 IS
19 l_user_id NUMBER;
20 l_login_id NUMBER;
21 l_sysdate DATE;
22 l_pb_input_header_rec pb_input_header_rec;
23 l_pb_input_header_id NUMBER;
24 l_full_pb_input_header_id NUMBER;
25 l_message_text VARCHAR2(2000);
26
27 l_context_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
28 l_attribute_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
29 l_attribute_value_tbl QP_PRICE_BOOK_UTIL.VARCHAR_TYPE;
30 l_attribute_type_tbl QP_PRICE_BOOK_UTIL.VARCHAR30_TYPE;
31
32 BEGIN
33
34 l_sysdate := sysdate;
35 l_user_id := fnd_global.user_id;
36 l_login_id := fnd_global.login_id;
37
38 x_return_status := 'S';
39
40 --added for moac
41 --Initialize MOAC and set org context to Multiple
42
43 IF MO_GLOBAL.get_access_mode is null THEN
44 MO_GLOBAL.Init('QP'); --specifying an MOAC enabled application although
45 --org context is based on the current responsibility
46 -- MO_GLOBAL.set_policy_context('M', null);--commented as MO_GLOBAL.Init will set_policy_context to 'M' or 'S' based on profile settings
47 END IF;--MO_GLOBAL
48
49 --Assign input parameter to local variable so that columns can be modified.
50 l_pb_input_header_rec := p_pb_input_header_rec;
51
52 --Perform Value To Id conversion
53 QP_PRICE_BOOK_UTIL.Convert_PB_Input_Value_to_Id(l_pb_input_header_rec);
54 --IN OUT parameter
55
56 --Perform Defaulting
57 QP_PRICE_BOOK_UTIL.Default_PB_Input_Criteria(l_pb_input_header_rec);
58 --IN OUT parameter
59
60 --IF publishing an existing price book
61 IF l_pb_input_header_rec.publish_existing_pb_flag = 'Y' THEN
62
63 BEGIN
64 SELECT pb_input_header_id
65 INTO l_pb_input_header_id
66 FROM qp_pb_input_headers_vl
67 WHERE price_book_name = l_pb_input_header_rec.price_book_name
68 AND customer_attr_value = l_pb_input_header_rec.customer_attr_value
69 AND customer_context = 'CUSTOMER'
70 AND customer_attribute = 'QUALIFIER_ATTRIBUTE2'
71 AND price_book_type_code = l_pb_input_header_rec.price_book_type_code;
72 EXCEPTION
73 WHEN OTHERS THEN
74 x_return_status := 'E';
75 FND_MESSAGE.SET_NAME('QP', 'QP_INPUT_REC_NOT_FOUND');
76 l_message_text := FND_MESSAGE.GET;
77 BEGIN
78 SELECT qp_price_book_messages_s.nextval
79 INTO x_price_book_messages_tbl(1).message_id FROM dual;
80 EXCEPTION
81 WHEN OTHERS THEN
82 x_price_book_messages_tbl(1).message_id := NULL;
83 END;
84 x_price_book_messages_tbl(1).message_type := 'E';
85 x_price_book_messages_tbl(1).message_code := 'QP_INPUT_REC_NOT_FOUND';
86 x_price_book_messages_tbl(1).message_text := l_message_text;
87 x_price_book_messages_tbl(1).creation_date := l_sysdate;
88 x_price_book_messages_tbl(1).created_by := l_user_id;
89 x_price_book_messages_tbl(1).last_update_date := l_sysdate;
90 x_price_book_messages_tbl(1).last_updated_by := l_user_id;
91 x_price_book_messages_tbl(1).last_update_login := l_login_id;
92 RETURN;
93 END;
94
95 UPDATE qp_pb_input_headers_b
96 SET publish_existing_pb_flag =
97 l_pb_input_header_rec.publish_existing_pb_flag,
98 dlv_xml_flag = l_pb_input_header_rec.dlv_xml_flag,
99 pub_template_code = l_pb_input_header_rec.pub_template_code,
100 pub_language = l_pb_input_header_rec.pub_language,
101 pub_territory = l_pb_input_header_rec.pub_territory,
102 pub_output_document_type =
103 l_pb_input_header_rec.pub_output_document_type,
104 dlv_email_flag = l_pb_input_header_rec.dlv_email_flag,
105 dlv_email_addresses = l_pb_input_header_rec.dlv_email_addresses,
106 dlv_printer_flag = l_pb_input_header_rec.dlv_printer_flag,
107 dlv_printer_name = l_pb_input_header_rec.dlv_printer_name,
108 dlv_xml_site_id = l_pb_input_header_rec.dlv_xml_site_id,
109 generation_time_code = l_pb_input_header_rec.generation_time_code,
110 gen_schedule_date = l_pb_input_header_rec.gen_schedule_date,
111 request_origination_code = 'API',
112 last_update_date = l_sysdate,
113 last_updated_by = l_user_id,
114 last_update_login = l_login_id
115 WHERE pb_input_header_id = l_pb_input_header_id;
116
117 UPDATE qp_pb_input_headers_tl
118 SET pub_template_name = l_pb_input_header_rec.pub_template_name,
119 last_update_date = l_sysdate,
120 last_updated_by = l_user_id,
121 last_update_login = l_login_id
122 WHERE pb_input_header_id = l_pb_input_header_id;
123
124 ELSE --Creating price book
125
126 IF l_pb_input_header_rec.price_book_type_code = 'D' THEN
127 --Delta Price Book. Insert input records where all columns other than
128 --effective_date and publishing criteria are same as corresponding full
129 --price book
130
131 --Fetch pb_input_header_id of corresponding full price book
132 BEGIN
133 SELECT pb_input_header_id
134 INTO l_full_pb_input_header_id
135 FROM qp_pb_input_headers_vl
136 WHERE price_book_name = l_pb_input_header_rec.price_book_name
137 AND customer_attr_value = l_pb_input_header_rec.customer_attr_value
138 AND customer_context = 'CUSTOMER'
139 AND customer_attribute = 'QUALIFIER_ATTRIBUTE2'
140 AND price_book_type_code = 'F';
141 EXCEPTION
142 WHEN OTHERS THEN
143 x_return_status := 'E';
144 FND_MESSAGE.SET_NAME('QP', 'QP_FULL_PRICE_BOOK_MUST_EXIST');
145 l_message_text := FND_MESSAGE.GET;
146 BEGIN
147 SELECT qp_price_book_messages_s.nextval
148 INTO x_price_book_messages_tbl(1).message_id FROM dual;
149 EXCEPTION
150 WHEN OTHERS THEN
151 x_price_book_messages_tbl(1).message_id := NULL;
152 END;
153 x_price_book_messages_tbl(1).message_type := 'E';
154 x_price_book_messages_tbl(1).message_code :=
155 'QP_FULL_PRICE_BOOK_MUST_EXIST';
156 x_price_book_messages_tbl(1).message_text := l_message_text;
157 x_price_book_messages_tbl(1).creation_date := l_sysdate;
158 x_price_book_messages_tbl(1).created_by := l_user_id;
159 x_price_book_messages_tbl(1).last_update_date := l_sysdate;
160 x_price_book_messages_tbl(1).last_updated_by := l_user_id;
161 x_price_book_messages_tbl(1).last_update_login := l_login_id;
162 RETURN;
163 END;
164
165 --Fetch values of certain columns from the corresponding columns of the
166 --full price book
167 BEGIN
168 SELECT customer_context, customer_attribute, customer_attr_value,
169 cust_account_id, --internal id for customer number
170 currency_code, limit_products_by, product_context,
171 product_attribute, product_attr_value,
172 item_quantity, org_id, price_based_on, pl_agr_bsa_id,
173 pricing_perspective_code, request_type_code,
174 pl_agr_bsa_name
175 INTO l_pb_input_header_rec.customer_context,
176 l_pb_input_header_rec.customer_attribute,
177 l_pb_input_header_rec.customer_attr_value,
178 l_pb_input_header_rec.cust_account_id,
179 l_pb_input_header_rec.currency_code,
180 l_pb_input_header_rec.limit_products_by,
181 l_pb_input_header_rec.product_context,
182 l_pb_input_header_rec.product_attribute,
183 l_pb_input_header_rec.product_attr_value,
184 l_pb_input_header_rec.item_quantity,
185 l_pb_input_header_rec.org_id,
186 l_pb_input_header_rec.price_based_on,
187 l_pb_input_header_rec.pl_agr_bsa_id,
188 l_pb_input_header_rec.pricing_perspective_code,
189 l_pb_input_header_rec.request_type_code,
190 l_pb_input_header_rec.pl_agr_bsa_name
191 FROM qp_pb_input_headers_vl
192 WHERE pb_input_header_id = l_full_pb_input_header_id;
193 EXCEPTION
194 WHEN OTHERS THEN
195 x_return_status := 'E';
196 FND_MESSAGE.SET_NAME('QP', 'QP_FULL_PRICE_BOOK_MUST_EXIST');
197 l_message_text := FND_MESSAGE.GET;
198 BEGIN
199 SELECT qp_price_book_messages_s.nextval
200 INTO x_price_book_messages_tbl(1).message_id FROM dual;
201 EXCEPTION
202 WHEN OTHERS THEN
203 x_price_book_messages_tbl(1).message_id := NULL;
204 END;
205 x_price_book_messages_tbl(1).message_type := 'E';
206 x_price_book_messages_tbl(1).message_code :=
207 'QP_FULL_PRICE_BOOK_MUST_EXIST';
208 x_price_book_messages_tbl(1).message_text := l_message_text;
209 x_price_book_messages_tbl(1).creation_date := l_sysdate;
210 x_price_book_messages_tbl(1).created_by := l_user_id;
211 x_price_book_messages_tbl(1).last_update_date := l_sysdate;
212 x_price_book_messages_tbl(1).last_updated_by := l_user_id;
213 x_price_book_messages_tbl(1).last_update_login := l_login_id;
214 RETURN;
215 END;
216
217 INSERT INTO qp_pb_input_headers_b
218 ( pb_input_header_id, customer_context, customer_attribute,
219 customer_attr_value, cust_account_id,
220 dlv_xml_site_id, currency_code, limit_products_by,
221 product_context, product_attribute, product_attr_value,
222 effective_date, item_quantity,
223 dlv_xml_flag, pub_template_code, pub_language, pub_territory,
224 pub_output_document_type,
225 dlv_email_flag, dlv_email_addresses, dlv_printer_flag,
226 dlv_printer_name, generation_time_code, gen_schedule_date,
227 --request_id,
228 org_id, price_book_type_code, price_based_on, pl_agr_bsa_id,
229 pricing_perspective_code,
230 publish_existing_pb_flag, overwrite_existing_pb_flag,
231 request_origination_code,
232 request_type_code,
233 --validation_error_flag,
234 creation_date, created_by, last_update_date, last_updated_by,
235 last_update_login
236 )
237 VALUES(
238 qp_pb_input_headers_b_s.nextval,
239 l_pb_input_header_rec.customer_context,
240 l_pb_input_header_rec.customer_attribute,
241 l_pb_input_header_rec.customer_attr_value,
242 l_pb_input_header_rec.cust_account_id,
243 l_pb_input_header_rec.dlv_xml_site_id,
244 l_pb_input_header_rec.currency_code,
245 l_pb_input_header_rec.limit_products_by,
246 l_pb_input_header_rec.product_context,
247 l_pb_input_header_rec.product_attribute,
248 l_pb_input_header_rec.product_attr_value,
249 l_pb_input_header_rec.effective_date,
250 l_pb_input_header_rec.item_quantity,
251 l_pb_input_header_rec.dlv_xml_flag,
252 l_pb_input_header_rec.pub_template_code,
253 l_pb_input_header_rec.pub_language,
254 l_pb_input_header_rec.pub_territory,
255 l_pb_input_header_rec.pub_output_document_type,
256 l_pb_input_header_rec.dlv_email_flag,
257 l_pb_input_header_rec.dlv_email_addresses,
258 l_pb_input_header_rec.dlv_printer_flag,
259 l_pb_input_header_rec.dlv_printer_name,
260 l_pb_input_header_rec.generation_time_code,
261 l_pb_input_header_rec.gen_schedule_date,
262 --request_id, --not populated with a value at this point
263 l_pb_input_header_rec.org_id,
264 l_pb_input_header_rec.price_book_type_code,
265 l_pb_input_header_rec.price_based_on,
266 l_pb_input_header_rec.pl_agr_bsa_id,
267 l_pb_input_header_rec.pricing_perspective_code,
268 l_pb_input_header_rec.publish_existing_pb_flag,
269 l_pb_input_header_rec.overwrite_existing_pb_flag,
270 l_pb_input_header_rec.request_origination_code,
271 l_pb_input_header_rec.request_type_code,
272 --l_pb_input_header_rec.validation_error_flag, --not populated
273 l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id)
274 RETURNING pb_input_header_id
275 INTO l_pb_input_header_id;
276
277 INSERT INTO qp_pb_input_headers_tl
278 (pb_input_header_id, price_book_name, pl_agr_bsa_name,
282 )
279 pub_template_name, creation_date, created_by,
280 last_update_date, last_updated_by,
281 last_update_login, language, source_lang
283 SELECT
284 l_pb_input_header_id,
285 l_pb_input_header_rec.price_book_name,
286 l_pb_input_header_rec.pl_agr_bsa_name,
287 l_pb_input_header_rec.pub_template_name,
288 l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id,
289 l.language_code,
290 userenv('LANG')
291 FROM fnd_languages l
292 WHERE l.installed_flag IN ('I', 'B')
293 AND NOT EXISTS (SELECT NULL
294 FROM qp_pb_input_headers_tl t
295 WHERE t.pb_input_header_id =
296 l_pb_input_header_id
297 AND t.language = l.language_code);
298
299
300 --Select the certain columns of input lines from the full price book
301 BEGIN
302 SELECT context, attribute, attribute_value, attribute_type
303 BULK COLLECT INTO l_context_tbl, l_attribute_tbl,
304 l_attribute_value_tbl, l_attribute_type_tbl
305 FROM qp_pb_input_lines
306 WHERE pb_input_header_id = l_full_pb_input_header_id;
307 EXCEPTION
308 WHEN OTHERS THEN
309 NULL;
310 END;
311
312 --Insert the Input criteria into input lines tables
313 FORALL k IN l_context_tbl.FIRST..l_context_tbl.LAST
314 INSERT INTO qp_pb_input_lines
315 (pb_input_line_id, pb_input_header_id,
316 context, attribute, attribute_value,
317 attribute_type, creation_date, created_by, last_update_date,
318 last_updated_by, last_update_login
319 )
320 VALUES
321 (qp_pb_input_lines_s.nextval,
322 l_pb_input_header_id,
323 l_context_tbl(k), l_attribute_tbl(k),
324 l_attribute_value_tbl(k), l_attribute_type_tbl(k),
325 l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id
326 );
327
328 ELSE --Not Delta price book request
329
330 --Insert the Input criteria into the price book input header tables
331 INSERT INTO qp_pb_input_headers_b
332 (pb_input_header_id, customer_context, customer_attribute,
333 customer_attr_value, cust_account_id,
334 dlv_xml_site_id, currency_code, limit_products_by,
335 product_context, product_attribute, product_attr_value,
336 effective_date, item_quantity,
337 dlv_xml_flag, pub_template_code, pub_language, pub_territory,
338 pub_output_document_type,
339 dlv_email_flag, dlv_email_addresses, dlv_printer_flag,
340 dlv_printer_name, generation_time_code, gen_schedule_date,
341 --request_id,
342 org_id, price_book_type_code, price_based_on, pl_agr_bsa_id,
343 pricing_perspective_code,
344 publish_existing_pb_flag, overwrite_existing_pb_flag,
345 request_origination_code,
346 request_type_code,
347 --validation_error_flag,
348 creation_date, created_by, last_update_date, last_updated_by,
349 last_update_login
350 )
351 VALUES
352 (qp_pb_input_headers_b_s.nextval,
353 l_pb_input_header_rec.customer_context,
354 l_pb_input_header_rec.customer_attribute,
355 l_pb_input_header_rec.customer_attr_value,
356 l_pb_input_header_rec.cust_account_id,
357 l_pb_input_header_rec.dlv_xml_site_id,
358 l_pb_input_header_rec.currency_code,
359 l_pb_input_header_rec.limit_products_by,
360 l_pb_input_header_rec.product_context,
361 l_pb_input_header_rec.product_attribute,
362 l_pb_input_header_rec.product_attr_value,
363 l_pb_input_header_rec.effective_date,
364 l_pb_input_header_rec.item_quantity,
365 l_pb_input_header_rec.dlv_xml_flag,
366 l_pb_input_header_rec.pub_template_code,
367 l_pb_input_header_rec.pub_language,
368 l_pb_input_header_rec.pub_territory,
369 l_pb_input_header_rec.pub_output_document_type,
370 l_pb_input_header_rec.dlv_email_flag,
371 l_pb_input_header_rec.dlv_email_addresses,
372 l_pb_input_header_rec.dlv_printer_flag,
373 l_pb_input_header_rec.dlv_printer_name,
374 l_pb_input_header_rec.generation_time_code,
375 l_pb_input_header_rec.gen_schedule_date,
376 --l_pb_input_header_rec.request_id,
377 l_pb_input_header_rec.org_id,
378 l_pb_input_header_rec.price_book_type_code,
379 l_pb_input_header_rec.price_based_on,
380 l_pb_input_header_rec.pl_agr_bsa_id,
381 l_pb_input_header_rec.pricing_perspective_code,
382 l_pb_input_header_rec.publish_existing_pb_flag,
383 l_pb_input_header_rec.overwrite_existing_pb_flag,
384 l_pb_input_header_rec.request_origination_code,
385 l_pb_input_header_rec.request_type_code,
386 --l_pb_input_header_rec.validation_error_flag,
387 l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id
388 ) RETURNING pb_input_header_id INTO
389 l_pb_input_header_id;
390
391 INSERT INTO qp_pb_input_headers_tl
392 (pb_input_header_id, price_book_name, pl_agr_bsa_name,
393 pub_template_name, creation_date, created_by,
394 last_update_date, last_updated_by,
395 last_update_login, language, source_lang
396 )
397 SELECT
398 l_pb_input_header_id,
399 l_pb_input_header_rec.price_book_name,
400 l_pb_input_header_rec.pl_agr_bsa_name,
401 l_pb_input_header_rec.pub_template_name,
402 l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id,
403 l.language_code,
404 userenv('LANG')
405 FROM fnd_languages l
406 WHERE l.installed_flag IN ('I', 'B')
407 AND NOT EXISTS (SELECT NULL
408 FROM qp_pb_input_headers_tl t
409 WHERE t.pb_input_header_id =
410 l_pb_input_header_id
411 AND t.language = l.language_code);
412
413
414 --Insert the Input criteria into input lines tables
415 IF p_pb_input_lines_tbl.COUNT > 0 THEN
416 FOR k IN p_pb_input_lines_tbl.FIRST..p_pb_input_lines_tbl.LAST
417 LOOP
418 l_context_tbl(k) := p_pb_input_lines_tbl(k).context;
419 l_attribute_tbl(k) := p_pb_input_lines_tbl(k).attribute;
420 l_attribute_value_tbl(k) := p_pb_input_lines_tbl(k).attribute_value;
421 l_attribute_type_tbl(k) := p_pb_input_lines_tbl(k).attribute_type;
422 END LOOP;
423 END IF; --If p_pb_input_lines_tbl.count > 0
424
425 FORALL i IN l_context_tbl.FIRST..l_context_tbl.LAST
426 INSERT INTO qp_pb_input_lines
427 (pb_input_line_id, pb_input_header_id,
428 context, attribute, attribute_value,
429 attribute_type, creation_date, created_by, last_update_date,
430 last_updated_by, last_update_login
431 )
432 VALUES
433 (qp_pb_input_lines_s.nextval, l_pb_input_header_id,
434 l_context_tbl(i), l_attribute_tbl(i),
435 l_attribute_value_tbl(i),
436 l_attribute_type_tbl(i),
437 l_sysdate, l_user_id, l_sysdate, l_user_id, l_login_id
438 );
439
440 END IF; --If Delta Price Book request
441
442 END IF; --If publishing existing price book
443
444 QP_PRICE_BOOK_PVT.Generate_Publish_Price_Book(
445 p_pb_input_header_id => l_pb_input_header_id,
446 x_request_id => x_request_id,
447 x_return_status => x_return_status,
448 x_retcode => x_retcode,
449 x_err_buf => x_err_buf);
450
451 IF x_return_status = 'E' THEN --If input validation errors occur
452 SELECT * BULK COLLECT INTO x_price_book_messages_tbl
453 FROM qp_price_book_messages
454 WHERE pb_input_header_id = l_pb_input_header_id;
455
456 DELETE FROM qp_price_book_messages
457 WHERE pb_input_header_id = l_pb_input_header_id;
458
459 DELETE FROM qp_pb_input_headers_b
460 WHERE pb_input_header_id = l_pb_input_header_id;
461
462 DELETE FROM qp_pb_input_headers_tl
463 WHERE pb_input_header_id = l_pb_input_header_id;
464
465 DELETE FROM qp_pb_input_lines
466 WHERE pb_input_header_id = l_pb_input_header_id;
467
468 END IF;
469
470 --Commit Stmt may be needed here
471
472 END Create_Publish_Price_Book;
473
474
475 /*****************************************************************************
476 Public API to Query an existing Full/Delta Price Book
477 *****************************************************************************/
478 PROCEDURE Get_Price_Book(
479 p_price_book_name IN VARCHAR2,
480 p_customer_id IN NUMBER,
481 p_price_book_type_code IN VARCHAR2,
482 x_price_book_header_rec OUT NOCOPY price_book_header_rec,
483 x_price_book_lines_tbl OUT NOCOPY price_book_lines_tbl,
484 x_price_book_line_details_tbl OUT NOCOPY price_book_line_details_tbl,
485 x_price_book_attributes_tbl OUT NOCOPY price_book_attributes_tbl,
486 x_price_book_break_lines_tbl OUT NOCOPY price_book_break_lines_tbl,
487 x_price_book_messages_tbl OUT NOCOPY price_book_messages_tbl,
488 x_return_status OUT NOCOPY VARCHAR2,
489 x_query_messages OUT NOCOPY VARCHAR_TBL)
490 IS
491 i NUMBER := 1;
492 l_count NUMBER;
493 l_message_text VARCHAR2(2000);
494 l_customer_id NUMBER;
495 l_user_id NUMBER;
496 l_party_id_match VARCHAR2(1);
497
498 BEGIN
499
503
500 x_return_status := 'S';
501
502 l_user_id := fnd_global.user_id;
504 --added for moac
505 --Initialize MOAC and set org context to Multiple
506
507 IF MO_GLOBAL.get_access_mode is null THEN
508 MO_GLOBAL.Init('QP'); --specifying an MOAC enabled application although
509 --org context is based on the current responsibility
510 -- MO_GLOBAL.set_policy_context('M', null);--commented as MO_GLOBAL.Init will set_policy_context to 'M' or 'S' based on profile settings
511 END IF;--MO_GLOBAL
512
513 IF p_price_book_type_code IS NULL THEN
514 x_return_status := 'E';
515 FND_MESSAGE.SET_NAME('QP', 'QP_PARAMETER_REQUIRED');
516 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_PRICE_BOOK_TYPE_CODE');
517 l_message_text := FND_MESSAGE.GET;
518 x_query_messages(i) := substr(l_message_text, 1, 240);
519 i := i + 1;
520 ELSE
521 IF NOT (p_price_book_type_code = 'F' OR p_price_book_type_code = 'D') THEN
522 x_return_status := 'E';
523 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_PARAMETER');
524 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_PRICE_BOOK_TYPE_CODE');
525 l_message_text := FND_MESSAGE.GET;
526 x_query_messages(i) := substr(l_message_text, 1, 240);
527 i := i + 1;
528 END IF;
529 END IF;
530
531 IF p_price_book_name IS NULL THEN
532 x_return_status := 'E';
533 FND_MESSAGE.SET_NAME('QP', 'QP_PARAMETER_REQUIRED');
534 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_PRICE_BOOK_NAME');
535 l_message_text := FND_MESSAGE.GET;
536 x_query_messages(i) := substr(l_message_text, 1, 240);
537 i := i + 1;
538 END IF;
539
540 IF p_customer_id IS NULL THEN
541 x_return_status := 'E';
542 FND_MESSAGE.SET_NAME('QP', 'QP_PARAMETER_REQUIRED');
543 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_CUSTOMER_ID');
544 l_message_text := FND_MESSAGE.GET;
545 x_query_messages(i) := substr(l_message_text, 1, 240);
546 i := i + 1;
547 ELSE
548 --Check if the user is an external or internal user
549 BEGIN
550 SELECT customer_id
551 INTO l_customer_id
552 FROM fnd_user
553 WHERE user_id = l_user_id;
554 EXCEPTION
555 WHEN OTHERS THEN
556 l_customer_id := NULL;
557 END; --Check if external or internal user
558
559 IF l_customer_id IS NOT NULL THEN --External User
560 --Check if the customer id on pb matches the parent org of the
561 --customer associated with user
562 BEGIN
563 SELECT 'Y'
564 INTO l_party_id_match
565 FROM dual
566 WHERE EXISTS (
567 SELECT 'x'
568 FROM hz_relationships rel, hz_parties party3,
569 hz_parties party4, hz_parties party5
570 WHERE rel.party_id = party5.party_id
571 AND party5.party_type = 'PARTY_RELATIONSHIP'
572 AND party5.status = 'A'
573 AND trunc(rel.start_date) <= trunc(sysdate)
574 AND trunc(nvl(rel.end_date, sysdate)) >= trunc(sysdate)
575 AND rel.subject_id = party3.party_id
576 AND party3.party_type = 'PERSON'
577 AND party3.status = 'A'
578 AND rel.object_id = party4.party_id
579 AND party4.party_type = 'ORGANIZATION'
580 AND party4.status = 'A'
581 AND rel.subject_table_name = 'HZ_PARTIES'
582 AND rel.object_table_name = 'HZ_PARTIES'
583 AND rel.relationship_id IN
584 (SELECT party_relationship_id
585 FROM hz_org_contacts org_con
586 WHERE rel.relationship_id =
587 org_con.party_relationship_id
588 AND org_con.status ='A' )
589 AND party5.party_id = l_customer_id --customer id on user
590 AND party4.party_id = p_customer_id);--customer id of pb
591 EXCEPTION
592 WHEN OTHERS THEN
593 l_party_id_match := 'N';
594 END;
595
596 IF p_customer_id <> l_customer_id AND l_party_id_match <> 'Y'
597 THEN
598 x_return_status := 'E';
599 FND_MESSAGE.SET_NAME('QP', 'QP_CUSTOMER_NOT_MATCHING');
600 l_message_text := FND_MESSAGE.GET;
601 x_query_messages(i) := substr(l_message_text, 1, 240);
602 i := i + 1;
603 END IF;
604
605 ELSE -- Internal User
606 BEGIN
607 SELECT 1
608 INTO l_count
609 FROM hz_parties
610 WHERE party_id = p_customer_id
611 AND rownum = 1;
612 EXCEPTION
613 WHEN OTHERS THEN
614 l_count := 0;
615 END;
616
617 IF l_count = 0 AND p_customer_id <> -1 THEN -- invalid customer
618 x_return_status := 'E';
619 FND_MESSAGE.SET_NAME('QP', 'QP_INVALID_CUSTOMER');
620 l_message_text := FND_MESSAGE.GET;
621 x_query_messages(i) := substr(l_message_text, 1, 240);
622 i := i + 1;
623 END IF;
624
625 END IF; --External User
626
627 END IF;
628
629 IF x_return_status = 'E' THEN
630 RETURN;
631 END IF;
632
633 --Query price book
634 BEGIN
635 SELECT price_book_header_id,
636 price_book_type_code,
637 currency_code,
638 effective_date,
639 org_id,
640 customer_id,
641 cust_account_id,
642 document_id,
646 pricing_perspective_code,
643 item_category,
644 price_based_on,
645 pl_agr_bsa_id,
647 item_quantity,
648 request_id,
649 request_type_code,
650 pb_input_header_id,
651 pub_status_code,
652 price_book_name,
653 pl_agr_bsa_name,
654 creation_date,
655 created_by,
656 last_update_date,
657 last_updated_by,
658 last_update_login,
659 price_book_type,
660 currency,
661 operating_unit,
662 customer_name
663 INTO x_price_book_header_rec
664 FROM qp_price_book_headers_v
665 WHERE price_book_name = p_price_book_name
666 AND price_book_type_code = p_price_book_type_code
667 AND customer_id = p_customer_id;
668 EXCEPTION
669 WHEN OTHERS THEN
670 x_return_status := 'E';
671 FND_MESSAGE.SET_NAME('QP', 'QP_PRICE_BOOK_DOES_NOT_EXIST');
672 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_NAME', p_price_book_name);
673 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_TYPE_CODE', p_price_book_type_code);
674 l_message_text := FND_MESSAGE.GET;
675 x_query_messages(i) := substr(l_message_text, 1, 240);
676 RETURN;
677 END;
678
679 SELECT price_book_line_id, price_book_header_id, item_number,
680 product_uom_code, list_price, net_price, sync_action_code,
681 line_status_code, creation_date, created_by, last_update_date,
682 last_updated_by, last_update_login, description, customer_item_number,
683 customer_item_desc, display_item_number, sync_action
684 BULK COLLECT INTO x_price_book_lines_tbl
685 FROM qp_price_book_lines_v
686 WHERE price_book_header_id = x_price_book_header_rec.price_book_header_id;
687
688 SELECT price_book_line_det_id, price_book_line_id, price_book_header_id,
689 list_header_id, list_line_id, list_line_no, list_price,
690 modifier_operand, modifier_application_method, adjustment_amount,
691 adjusted_net_price, list_line_type_code, price_break_type_code,
692 creation_date, created_by, last_update_date, last_updated_by,
693 last_update_login, list_name, list_line_type, price_break_type,
694 application_method_name
695 BULK COLLECT INTO x_price_book_line_details_tbl
696 FROM qp_price_book_line_details_v
697 WHERE price_book_header_id = x_price_book_header_rec.price_book_header_id;
698
699 SELECT price_book_attribute_id, price_book_line_det_id, price_book_line_id,
700 price_book_header_id, pricing_prod_context, pricing_prod_attribute,
701 comparison_operator_code, pricing_prod_attr_value_from,
702 pricing_attr_value_to, pricing_prod_attr_datatype, attribute_type,
703 creation_date, created_by, last_update_date, last_updated_by,
704 last_update_login, context_name, attribute_name, attribute_value_name,
705 attribute_value_to_name, comparison_operator_name
706 BULK COLLECT INTO x_price_book_attributes_tbl
707 FROM qp_price_book_attributes_v
708 WHERE price_book_header_id = x_price_book_header_rec.price_book_header_id;
709
710 SELECT price_book_break_line_id, price_book_header_id, price_book_line_id,
711 price_book_line_det_id, pricing_context, pricing_attribute,
712 comparison_operator_code, pricing_attr_value_from,
713 pricing_attr_value_to, pricing_attribute_datatype, operand,
714 application_method, recurring_value,
715 creation_date, created_by, last_update_date,
716 last_updated_by, last_update_login, context_name, attribute_name,
717 attribute_value_name, attribute_value_to_name,
718 comparison_operator_name, application_method_name
719 BULK COLLECT INTO x_price_book_break_lines_tbl
720 FROM qp_price_book_break_lines_v
721 WHERE price_book_header_id = x_price_book_header_rec.price_book_header_id;
722
723 SELECT * BULK COLLECT INTO x_price_book_messages_tbl
724 FROM qp_price_book_messages
725 WHERE price_book_header_id = x_price_book_header_rec.price_book_header_id;
726
727 END Get_Price_Book;
728
729 /*****************************************************************************
730 Overloaded Public API to Query an existing Full/Delta Price Book along with
731 the attached formatted (.pdf, etc.) document
732 *****************************************************************************/
733 PROCEDURE Get_Price_Book(
734 p_price_book_name IN VARCHAR2,
735 p_customer_id IN NUMBER,
736 p_price_book_type_code IN VARCHAR2,
737 x_price_book_header_rec OUT NOCOPY price_book_header_rec,
738 x_price_book_lines_tbl OUT NOCOPY price_book_lines_tbl,
739 x_price_book_line_details_tbl OUT NOCOPY price_book_line_details_tbl,
740 x_price_book_attributes_tbl OUT NOCOPY price_book_attributes_tbl,
741 x_price_book_break_lines_tbl OUT NOCOPY price_book_break_lines_tbl,
742 x_price_book_messages_tbl OUT NOCOPY price_book_messages_tbl,
743 x_documents_rec OUT NOCOPY documents_rec,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_query_messages OUT NOCOPY VARCHAR_TBL)
746 IS
747 BEGIN
748
749 --Call the Get_Price_Book without the x_documents_rec parameter
750 Get_Price_Book(
751 p_price_book_name => p_price_book_name,
752 p_customer_id => p_customer_id,
753 p_price_book_type_code => p_price_book_type_code,
754 x_price_book_header_rec => x_price_book_header_rec,
755 x_price_book_lines_tbl => x_price_book_lines_tbl,
756 x_price_book_line_details_tbl => x_price_book_line_details_tbl,
757 x_price_book_attributes_tbl => x_price_book_attributes_tbl,
758 x_price_book_break_lines_tbl => x_price_book_break_lines_tbl,
759 x_price_book_messages_tbl => x_price_book_messages_tbl,
760 x_return_status => x_return_status,
761 x_query_messages => x_query_messages);
762
763 IF x_return_status = 'S' THEN
764 IF x_price_book_header_rec.document_id IS NOT NULL THEN
765 BEGIN
766 SELECT document_id, document_content, document_content_type,
767 document_name, creation_date, created_by, last_update_date,
768 last_updated_by, last_update_login
769 INTO x_documents_rec
770 FROM qp_documents
771 WHERE document_id = x_price_book_header_rec.document_id;
772 EXCEPTION
773 WHEN OTHERS THEN
774 NULL;
775 END;
776 END IF;
777 END IF;
778
779 END Get_Price_Book; --Overloaded
780
781 END QP_PRICE_BOOK_PUB;