[Home] [Help]
PACKAGE BODY: APPS.QP_PRICE_BOOK_PVT
Source
1 PACKAGE BODY qp_price_book_pvt AS
2 /* $Header: QPXVGPBB.pls 120.83 2011/07/01 09:19:28 smbalara ship $*/
3
4 --Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'qp_price_book_pvt';
6
7 /*****************************************************************************
8 Internal API to create Delta Price Book
9 *****************************************************************************/
10 PROCEDURE Create_Delta_Price_Book(p_delta_price_book_header_id IN NUMBER,
11 p_delta_price_book_name IN VARCHAR2,
12 p_delta_price_book_customer_id IN NUMBER)
13 IS
14 CURSOR unchanged_lines_cur(a_price_book_header_id NUMBER)
15 IS
16 SELECT price_book_line_id
17 FROM qp_price_book_lines
18 WHERE sync_action_code = 'N'
19 AND price_book_header_id = a_price_book_header_id;
20
21 l_user_id NUMBER;
22 l_login_id NUMBER;
23
24 l_unchanged_line_id_tbl NUMBER_TYPE;
25 l_deleted_line_id_tbl NUMBER_TYPE;
26 l_new_deleted_line_id_tbl NUMBER_TYPE;
27 l_item_number_tbl NUMBER_TYPE;
28 l_uom_code_tbl VARCHAR3_TYPE;
29 l_list_price_tbl NUMBER_TYPE;
30 l_net_price_tbl NUMBER_TYPE;
31 l_line_status_code_tbl FLAG_TYPE;
32
33 l_full_price_book_header_id NUMBER;
34
35 CURSOR deleted_lines_cur(a_full_price_book_header_id NUMBER,
36 a_delta_price_book_header_id NUMBER)
37 IS
38 SELECT a.price_book_line_id, a.item_number, a.product_uom_code,
39 a.list_price, a.net_price, a.line_status_code
40 FROM qp_price_book_lines a
41 WHERE a.price_book_header_id = a_full_price_book_header_id
42 AND NOT EXISTS (SELECT 'X'
43 FROM qp_price_book_lines b
44 WHERE b.price_book_header_id=a_delta_price_book_header_id
45 AND b.item_number = a.item_number
46 AND b.product_uom_code = a.product_uom_code)
47 ORDER BY a.price_book_line_id;
48
49 CURSOR deleted_line_dets_cur(a_full_price_book_header_id NUMBER,
50 a_delta_price_book_header_id NUMBER,
51 a_deleted_line_id_first NUMBER,
52 a_deleted_line_id_last NUMBER)
53 IS
54 SELECT price_book_line_det_id, price_book_line_id
55 FROM qp_price_book_line_details
56 WHERE price_book_header_id = a_full_price_book_header_id
57 AND price_book_line_id IN (SELECT a.price_book_line_id
58 FROM qp_price_book_lines a
59 WHERE a.price_book_header_id =
60 a_full_price_book_header_id
61 AND NOT EXISTS
62 (SELECT 'X'
63 FROM qp_price_book_lines b
64 WHERE b.price_book_header_id =
65 a_delta_price_book_header_id
66 AND b.item_number = a.item_number
67 AND b.product_uom_code =
68 a.product_uom_code)
69 )
70 AND price_book_line_id BETWEEN
71 a_deleted_line_id_first AND a_deleted_line_id_last
72 ORDER BY price_book_line_det_id;
73
74 BEGIN
75 l_user_id := fnd_global.user_id;
76 l_login_id := fnd_global.conc_login_id;
77
78 --Get the price_book_header_id of the corresponding full price book
79 BEGIN
80 SELECT price_book_header_id
81 INTO l_full_price_book_header_id
82 FROM qp_price_book_headers_vl
83 WHERE price_book_type_code = 'F'
84 AND price_book_name = p_delta_price_book_name
85 AND customer_id = p_delta_price_book_customer_id;
86 EXCEPTION
87 WHEN OTHERS THEN
88 RAISE;
89 END;
90
91 --Mark those delta price book lines with 'N' (not changed) that have not
92 --changed wrt to previous full price book
93 UPDATE qp_price_book_lines a
94 SET a.sync_action_code = 'N' -- unchanged lines will be removed from delta
95 WHERE a.price_book_header_id = p_delta_price_book_header_id
96 AND EXISTS (SELECT 'X'
97 FROM qp_price_book_lines b
98 WHERE b.price_book_header_id = l_full_price_book_header_id
99 AND b.item_number = a.item_number
100 AND b.product_uom_code = a.product_uom_code
101 AND nvl(b.list_price, 0) = nvl(a.list_price, 0)
102 AND nvl(b.net_price, 0) = nvl(a.net_price, 0));
103
104 UPDATE qp_price_book_lines a
105 SET a.sync_action_code = 'A' --Add
106 WHERE a.price_book_header_id = p_delta_price_book_header_id
107 AND NOT EXISTS (SELECT 'X'
108 FROM qp_price_book_lines b
109 WHERE b.price_book_header_id = l_full_price_book_header_id
110 AND b.item_number = a.item_number
111 AND b.product_uom_code = a.product_uom_code);
112
113 UPDATE qp_price_book_lines a
114 SET sync_action_code = 'R' --Replace
115 WHERE price_book_header_id = p_delta_price_book_header_id
116 AND EXISTS (SELECT 'X'
117 FROM qp_price_book_lines b
118 WHERE b.price_book_header_id = l_full_price_book_header_id
119 AND b.item_number = a.item_number
120 AND b.product_uom_code = a.product_uom_code
121 AND (nvl(b.list_price, 0) <> nvl(a.list_price, 0) OR
122 nvl(b.net_price, 0) <> nvl(a.net_price, 0)));
123
124 --Insert into delta price book with sync_action_code = 'D', any item+uom that
125 --was present in the previous full price book but not present in delta
126 OPEN deleted_lines_cur(l_full_price_book_header_id,
127 p_delta_price_book_header_id);
128 LOOP
129 l_deleted_line_id_tbl.delete;
130 l_new_deleted_line_id_tbl.delete;
131 l_item_number_tbl.delete;
132 l_uom_code_tbl.delete;
133 l_list_price_tbl.delete;
134 l_net_price_tbl.delete;
135 l_line_status_code_tbl.delete;
136
137 FETCH deleted_lines_cur BULK COLLECT INTO l_deleted_line_id_tbl,
138 l_item_number_tbl, l_uom_code_tbl, l_list_price_tbl,
139 l_net_price_tbl, l_line_status_code_tbl LIMIT rows;
140
141 --For each record in any of the bulk collection returned by the
142 --deleted_lines_cur cursor
143 FORALL i IN l_item_number_tbl.FIRST..l_item_number_tbl.LAST
144 INSERT INTO qp_price_book_lines
145 (price_book_line_id, price_book_header_id,
146 item_number,
147 product_uom_code,
148 list_price,
149 net_price,
150 sync_action_code, line_status_code, creation_date,
151 created_by, last_update_date, last_updated_by,
152 last_update_login)
153 VALUES (qp_price_book_lines_s.nextval, p_delta_price_book_header_id,
154 l_item_number_tbl(i),
155 l_uom_code_tbl(i),
156 l_list_price_tbl(i),
157 l_net_price_tbl(i),
158 'D', l_line_status_code_tbl(i), sysdate,
159 l_user_id, sysdate, l_user_id,
160 l_login_id)
161 RETURNING price_book_line_id BULK COLLECT INTO l_new_deleted_line_id_tbl;
162
163 --For each of the deleted price book lines also copy the product attributes
164 --(Item Categories) , which have line_det_id = -1, since without the
165 --product attributes these lines will not be shown on Hgrid
166 FORALL i IN l_new_deleted_line_id_tbl.FIRST..l_new_deleted_line_id_tbl.LAST
167 INSERT INTO qp_price_book_attributes
168 (price_book_attribute_id, price_book_line_det_id,
169 price_book_line_id, price_book_header_id,
170 pricing_prod_context, pricing_prod_attribute,
171 pricing_prod_attr_value_from, pricing_attr_value_to,
172 comparison_operator_code, pricing_prod_attr_datatype,
173 attribute_type, creation_date, created_by, last_update_date,
174 last_updated_by, last_update_login)
175 SELECT qp_price_book_attributes_s.nextval, price_book_line_det_id,
176 l_new_deleted_line_id_tbl(i), p_delta_price_book_header_id,
177 pricing_prod_context, pricing_prod_attribute,
178 pricing_prod_attr_value_from, pricing_attr_value_to,
179 comparison_operator_code, pricing_prod_attr_datatype,
180 attribute_type, sysdate, l_user_id, sysdate, l_user_id,
181 l_login_id
182 FROM qp_price_book_attributes
183 WHERE price_book_line_id = l_deleted_line_id_tbl(i)
184 AND price_book_line_det_id = -1;
185
186 EXIT WHEN deleted_lines_cur%NOTFOUND;
187
188 END LOOP;
189 CLOSE deleted_lines_cur;
190
191
192 --For delta price book lines that are unchanged wrt to the full price book
193 OPEN unchanged_lines_cur(p_delta_price_book_header_id);
194 LOOP
195 l_unchanged_line_id_tbl.delete;
196 FETCH unchanged_lines_cur BULK COLLECT INTO l_unchanged_line_id_tbl
197 LIMIT rows;
198
199 --Delete the price book break lines
200 FORALL i IN l_unchanged_line_id_tbl.FIRST..l_unchanged_line_id_tbl.LAST
201 DELETE FROM qp_price_book_break_lines
202 WHERE price_book_line_id = l_unchanged_line_id_tbl(i);
203
204 --Delete the price book attributes
205 FORALL i IN l_unchanged_line_id_tbl.FIRST..l_unchanged_line_id_tbl.LAST
206 DELETE FROM qp_price_book_attributes
207 WHERE price_book_line_id = l_unchanged_line_id_tbl(i);
208
209 --Delete the price book line details
210 FORALL i IN l_unchanged_line_id_tbl.FIRST..l_unchanged_line_id_tbl.LAST
211 DELETE FROM qp_price_book_line_details
212 WHERE price_book_line_id = l_unchanged_line_id_tbl(i);
213
214 --Delete the price book lines
215 FORALL i IN l_unchanged_line_id_tbl.FIRST..l_unchanged_line_id_tbl.LAST
216 DELETE FROM qp_price_book_lines
217 WHERE price_book_line_id = l_unchanged_line_id_tbl(i);
218
219 --Delete the price book line level messages
220 FORALL i IN l_unchanged_line_id_tbl.FIRST..l_unchanged_line_id_tbl.LAST
221 DELETE FROM qp_price_book_messages
222 WHERE price_book_line_id = l_unchanged_line_id_tbl(i)
223 AND pb_input_header_id IS NULL;
224
225 EXIT WHEN unchanged_lines_cur%NOTFOUND;
226
227 END LOOP;
228 CLOSE unchanged_lines_cur;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 RAISE;
233 END Create_Delta_Price_Book;
234
235 /*****************************************************************************
236 Internal API to Insert Price Book Lines, Attributes, Break Lines and Messages
237 ******************************************************************************/
238 PROCEDURE Insert_Price_Book_Content(
239 p_pb_input_header_rec IN qp_pb_input_headers_vl%ROWTYPE,
240 p_pb_input_lines_tbl IN QP_PRICE_BOOK_UTIL.pb_input_lines_tbl,
241 p_price_book_header_id IN NUMBER
242 )
243 IS
244
245 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
246 l_return_status_text VARCHAR2(240) ;
247 l_count NUMBER := 0;
248
249 TYPE GLOBAL_STRUCT_REC IS RECORD(seeded_value_string VARCHAR2(2000),
250 user_value_string VARCHAR2(2000),
251 order_level_global_struct VARCHAR2(80),
252 line_level_global_struct VARCHAR2(80));
253 l_rec GLOBAL_STRUCT_REC;
254
255 l_line_index_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
256 l_line_type_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
257 l_pricing_effective_date_tbl QP_PREQ_GRP.DATE_TYPE;
258 l_active_date_first_tbl QP_PREQ_GRP.DATE_TYPE;
259 l_active_date_first_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
260 l_active_date_second_tbl QP_PREQ_GRP.DATE_TYPE;
261 l_active_date_second_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
262 l_line_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
263 l_line_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
264 l_request_type_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
265 l_priced_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
266 l_priced_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
267 l_currency_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
268 l_unit_price_tbl QP_PREQ_GRP.NUMBER_TYPE;
269 l_percent_price_tbl QP_PREQ_GRP.NUMBER_TYPE;
270 l_uom_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
271 l_adjusted_unit_price_tbl QP_PREQ_GRP.NUMBER_TYPE;
272 l_upd_adjusted_unit_price_tbl QP_PREQ_GRP.NUMBER_TYPE;
273 l_processed_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
274 l_price_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
275 l_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
276 l_processing_order_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
277 l_pricing_status_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
278 l_pricing_status_text_tbl QP_PREQ_GRP.VARCHAR_TYPE;
279 l_rounding_flag_tbl QP_PREQ_GRP.FLAG_TYPE;
280 l_rounding_factor_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
281 l_qualifiers_exist_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
282 l_pricing_attrs_exist_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
283 l_price_list_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
284 l_validated_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
285 l_price_request_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
286 l_usage_pricing_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
287 l_item_number_tbl NUMBER_TYPE;
288
289 CURSOR insert_lines2_cur (a_price_book_header_id NUMBER,
290 a_effective_date DATE,
291 a_item_quantity NUMBER,
292 a_request_type_code VARCHAR2,
293 a_currency_code VARCHAR2,
294 a_price_based_on VARCHAR2,
295 a_pl_agr_bsa_id NUMBER)
296 IS
297 SELECT 'LINE', a_effective_date,
298 null, null, --active_date_first, active_first_date_type
299 null, null, --active_date_second, active_first_second_type
300 a_item_quantity, --line_quantity
301 product_uom_code, --line_uom_code
302 a_request_type_code,
303 null, null, --priced_quantity, priced_uom_code
304 a_currency_code,
305 null, null, null, --unit_price, percent_price, uom_quantity
306 null, null, --adjusted_unit_price, upd_adjusted_unit_price
307 QP_PREQ_GRP.G_NOT_PROCESSED, --processed_flag
308 'Y', --price_flag
309 price_book_line_id, --line_id
310 null, --processing_order
311 QP_PREQ_GRP.G_STATUS_UNCHANGED, --pricing_status_code
312 null, --pricing_status_text
313 'Q', --rounding_flag
314 null, --rounding_factor
315 'N', --qualifiers_exist_flag
316 'N', --pricing_attrs_exist_flag
317 decode(a_price_based_on, 'PRICE_LIST',
318 a_pl_agr_bsa_id, NULL), --price_list_id
319 'N', --validated_flag
320 null, --price_request_code
321 null, --usage_pricing_type
322 item_number
323 FROM qp_price_book_lines
324 WHERE price_book_header_id = a_price_book_header_id
325 ORDER BY price_book_line_id;
326
327 l_attrs_line_index_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
328 l_attrs_line_detail_index_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
329 l_attrs_attribute_level_tbl QP_PREQ_GRP.VARCHAR_TYPE;
330 l_attrs_attribute_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
331 l_attrs_list_header_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
332 l_attrs_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
333 l_attrs_context_tbl QP_PREQ_GRP.VARCHAR_TYPE;
334 l_attrs_attribute_tbl QP_PREQ_GRP.VARCHAR_TYPE;
335 l_attrs_value_from_tbl QP_PREQ_GRP.VARCHAR_TYPE;
336 l_attrs_setup_value_from_tbl QP_PREQ_GRP.VARCHAR_TYPE;
337 l_attrs_value_to_tbl QP_PREQ_GRP.VARCHAR_TYPE;
338 l_attrs_setup_value_to_tbl QP_PREQ_GRP.VARCHAR_TYPE;
339 l_attrs_grouping_number_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
340 l_attrs_no_quals_in_grp_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
341 l_attrs_comp_oper_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
342 l_attrs_validated_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
343 l_attrs_applied_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
344 l_attrs_pri_status_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
345 l_attrs_pri_status_text_tbl QP_PREQ_GRP.VARCHAR_TYPE;
346 l_attrs_qual_precedence_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
347 l_attrs_datatype_tbl QP_PREQ_GRP.VARCHAR_TYPE;
348 l_attrs_pricing_attr_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
349 l_attrs_qualifier_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
350 l_attrs_product_uom_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
351 l_attrs_excluder_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
352 l_attrs_pricing_phase_id_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
353 l_attrs_incomp_grp_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
354 l_attrs_line_det_typ_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
355 l_attrs_modif_level_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
356 l_attrs_primary_uom_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
357
358
359 --Cursor to get 'User Entered' attributes to insert into line_attrs_tmp table
360 CURSOR insert_line_attrs2_cur (a_line_index NUMBER,
361 a_request_type_code VARCHAR2,
362 a_pb_input_header_id NUMBER)
363 IS
364 SELECT a_line_index, null, --line_index, line_detail_index
365 QP_PREQ_GRP.G_LINE_LEVEL, --attribute_level
366 decode(l.attribute_type,
367 'PRICING_ATTRIBUTE', QP_PREQ_GRP.G_PRICING_TYPE,
368 l.attribute_type), --attribute_type
369 null, null, --list_header_id, list_line_id
370 l.context, --context
371 l.attribute, --attribute
372 l.attribute_value, --value_from
373 null, null, null, --setup_value_from, value_to, setup_value_to
374 null, null, --grouping_number, no_qualifiers_in_group
375 null, --comparison_operator_type
376 'N', --validated_flag
377 QP_PREQ_GRP.G_LIST_NOT_APPLIED, --applied_flag
378 QP_PREQ_GRP.G_STATUS_UNCHANGED, --pricing_status_code
379 null, null, --pricing_status_text, qualifier_precedence
380 null, --datatype
381 QP_PREQ_GRP.G_YES, --pricing_attr_flag
382 null, null, --qualifier_type, product_uom_code
383 null, null, --excluder_flag, pricing_phase_id
384 null, null, --incompatibility_grp_code, line_detail_type_code
385 null, null --modifier_level_code, primary_uom_flag
386 FROM qp_pb_input_lines l
387 WHERE pb_input_header_id = a_pb_input_header_id
388 AND EXISTS (SELECT 'x'
389 FROM qp_pte_segments qppseg, qp_prc_contexts_b qpcon,
390 qp_segments_b qpseg, qp_pte_request_types_b qpreq
391 WHERE qpcon.prc_context_code = l.context
392 AND qpcon.prc_context_type = l.attribute_type
393 AND qpseg.prc_context_id = qpcon.prc_context_id
394 AND qpseg.segment_mapping_column = l.attribute
395 AND qppseg.segment_id = qpseg.segment_id
396 AND qpreq.request_type_code = a_request_type_code
397 AND qppseg.pte_code = qpreq.pte_code
398 AND qppseg.user_sourcing_method = 'USER ENTERED');
399
400 CURSOR global_struct_attrs_cur(a_request_type_code VARCHAR2,
401 a_sourcing_level VARCHAR2,
402 a_context_type VARCHAR2,
403 a_context_code VARCHAR2,
404 a_segment_mapping_column VARCHAR2)
405 IS
406 SELECT qpsour.seeded_value_string,
407 qpsour.user_value_string,
408 qpreq.order_level_global_struct,
409 qpreq.line_level_global_struct
410 FROM
411 qp_segments_b qpseg,
412 qp_attribute_sourcing qpsour,
413 qp_prc_contexts_b qpcon,
414 qp_pte_request_types_b qpreq,
415 qp_pte_segments qppseg
416 WHERE
417 qpsour.segment_id = qpseg.segment_id
418 AND qpsour.attribute_sourcing_level = a_sourcing_level
419 AND qppseg.user_sourcing_method = 'ATTRIBUTE MAPPING'
420 AND qpsour.request_type_code = a_request_type_code
421 AND qpseg.prc_context_id = qpcon.prc_context_id
422 AND qpreq.request_type_code = qpsour.request_type_code
423 AND qppseg.pte_code = qpreq.pte_code
424 AND qppseg.segment_id = qpsour.segment_id
425 AND qppseg.sourcing_enabled = 'Y'
426 AND qpcon.prc_context_type = a_context_type
427 AND qpcon.prc_context_code = a_context_code
428 AND qpseg.segment_mapping_column = a_segment_mapping_column
429 AND rownum = 1;
430
431 l_sql_stmt VARCHAR2(2000) := '';
432 l_adhoc_lines_tbl QP_PRICE_BOOK_UTIL.pb_input_lines_tbl;
433 k NUMBER;
434 l_blanket_number NUMBER;
435 l_bsa_hdr_price_list_id NUMBER;
436 l_bsa_line_price_list_id NUMBER;
437 l_application_id NUMBER;
438
439 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
440
441 CURSOR pb_items_cur(a_price_book_header_id NUMBER)
442 IS
443 SELECT price_book_line_id, item_number --inventory_item_id
444 FROM qp_price_book_lines
445 WHERE price_book_header_id = a_price_book_header_id;
446
447 l_inv_org_id NUMBER;
448 l_net_price_tbl NUMBER_TYPE;
449
450 CURSOR lines_cur
451 IS
452 SELECT line_index, line_unit_price list_price,
453 order_uom_selling_price net_price, line_id,
454 pricing_status_code, pricing_status_text
455 FROM qp_preq_lines_tmp
456 ORDER BY line_index;
457
458 l_cf_list_header_id_tbl NUMBER_TYPE;
459 l_cf_list_line_id_tbl NUMBER_TYPE;
460 l_list_line_no_tbl VARCHAR_TYPE;
461 l_list_price_tbl NUMBER_TYPE;
462 l_modifier_operand_tbl NUMBER_TYPE;
463 l_modifier_appl_method_tbl VARCHAR30_TYPE;
464 l_adjustment_amount_tbl NUMBER_TYPE;
465 l_list_line_type_code_tbl VARCHAR30_TYPE;
466 l_pricing_phase_id_tbl NUMBER_TYPE;
467
468 l_price_break_type_code_tbl VARCHAR_TYPE;
469 l_line_detail_index_tbl NUMBER_TYPE;
470
471 CURSOR line_dets_cur
472 IS
473 SELECT a.created_from_list_header_id, a.created_from_list_line_id,
474 a.list_line_no,
475 decode(pricing_phase_id, 1, b.line_unit_price, a.order_qty_adj_amt)
476 list_price,
477 a.order_qty_operand modifier_operand,
478 a.operand_calculation_code modifier_application_method,
479 a.order_qty_adj_amt adjustment_amount,
480 a.created_from_list_line_type, a.pricing_phase_id,
481 a.price_break_type_code, a.line_index, a.line_detail_index,
482 b.line_id
483 FROM qp_preq_ldets_tmp a, qp_preq_lines_tmp b
484 WHERE a.line_index = b.line_index
485 AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
486 AND a.line_detail_type_code = 'NULL' --not a child line
487 AND a.applied_flag = 'Y' --automatic and applied discounts
488 AND nvl(a.accrual_flag,'N') = 'N' --exclude accruals
489 ORDER BY a.line_index,
490 decode(pricing_phase_id, 1, 1, 2), --to order pll before modifiers
491 decode(a.created_from_list_line_type, 'FREIGHT_CHARGE', null,
492 a.pricing_group_sequence),
493 decode(a.created_from_list_line_type, 'FREIGHT_CHARGE', 2, 1);
494
495 l_pb_line_det_id_tbl NUMBER_TYPE;
496 l_pb_line_id_tbl NUMBER_TYPE;
497 l_line_index2_tbl NUMBER_TYPE;
498 l_line_detail_index2_tbl NUMBER_TYPE;
499 l_list_line_type_code2_tbl VARCHAR30_TYPE;
500
501 l_user_id NUMBER;
502 l_login_id NUMBER;
503
504 l_pricing_events VARCHAR2(2000); --Check the datatype and length
505 l_price_book_messages_tbl QP_PRICE_BOOK_UTIL.price_book_messages_tbl;
506
507 sql_exception EXCEPTION;
508 l_pb_line_count NUMBER := 0;
509
510 l_validated_flag VARCHAR2(1);
511
512 BEGIN
513
514 l_user_id := fnd_global.user_id;
515 l_login_id := fnd_global.conc_login_id;
516
517 --Set Policy Context to 'S' for Single Org, namely, the org on the price book
518 --request of the Price Book being created. Note that republishing should have
519 --access mode of 'M', only creation has access mode 'S'.
520 mo_global.set_policy_context(p_access_mode => 'S',
521 p_org_id => p_pb_input_header_rec.org_id);
522
523 BEGIN
524 SELECT 1
525 INTO l_pb_line_count
526 FROM qp_price_book_lines
527 WHERE price_book_header_id = p_price_book_header_id
528 AND rownum = 1;
529 EXCEPTION
530 WHEN OTHERS THEN
531 l_pb_line_count := 0;
532 END;
533
534 IF l_pb_line_count = 0 THEN
535 RAISE FND_API.G_EXC_ERROR;
536 END IF;
537
538 -- SNIMMAGA.
539 --
540 -- The following logic has problems. All the price book lines are
541 -- being sent to the pricing engine in a single go. When the size of
542 -- the price book is huge (something like half a million price book lines
543 -- or so), the pricing engine can get choked.
544 --
545 -- A solution for this issue is to have a profile option, indicating
546 -- a permissible batch size of price book lines sent to the pricing engine
547 -- at a time; read the value of the profile option in the beginning of
548 -- of the Price Book Generation Concurrent program, issue muliple calls to
549 -- the pricing engine with such optimized price book line data sets, till
550 -- all such sets get priced.
551
552 -- qp_price_request_context.set_request_id;
553
554 --Insert records in qp_preq_lines_tmp
555 OPEN insert_lines2_cur(p_price_book_header_id,
556 p_pb_input_header_rec.effective_date,
557 p_pb_input_header_rec.item_quantity,
558 p_pb_input_header_rec.request_type_code,
559 p_pb_input_header_rec.currency_code,
560 p_pb_input_header_rec.price_based_on,
561 p_pb_input_header_rec.pl_agr_bsa_id);
562 LOOP
563 -- This statement is moved into the loop (to process the price book
564 -- lines in batches.
565 qp_price_request_context.set_request_id;
566 fnd_file.put_line(fnd_file.Log, 'Pricing Engine Request ID: '
567 || qp_price_request_context.get_request_id);
568
569 --Delete the plsql table of records for each loop repetition
570 l_line_type_code_tbl.delete;
571 l_pricing_effective_date_tbl.delete;
572 l_active_date_first_tbl.delete;
573 l_active_date_first_type_tbl.delete;
574 l_active_date_second_tbl.delete;
575 l_active_date_second_type_tbl.delete;
576 l_line_quantity_tbl.delete;
577 l_line_uom_code_tbl.delete;
578 l_request_type_code_tbl.delete;
579 l_priced_quantity_tbl.delete;
580 l_priced_uom_code_tbl.delete;
581 l_currency_code_tbl.delete;
582 l_unit_price_tbl.delete;
583 l_percent_price_tbl.delete;
584 l_uom_quantity_tbl.delete;
585 l_adjusted_unit_price_tbl.delete;
586 l_upd_adjusted_unit_price_tbl.delete;
587 l_processed_flag_tbl.delete;
588 l_price_flag_tbl.delete;
589 l_line_id_tbl.delete;
590 l_pricing_status_code_tbl.delete;
591 l_pricing_status_text_tbl.delete;
592 l_rounding_flag_tbl.delete;
593 l_rounding_factor_tbl.delete;
594 l_qualifiers_exist_flag_tbl.delete;
595 l_pricing_attrs_exist_flag_tbl.delete;
596 l_price_list_id_tbl.delete;
597 l_validated_flag_tbl.delete;
598 l_price_request_code_tbl.delete;
599 l_usage_pricing_type_tbl.delete;
600 l_item_number_tbl.delete;
601
602 FETCH insert_lines2_cur BULK COLLECT INTO
603 l_line_type_code_tbl, l_pricing_effective_date_tbl,
604 l_active_date_first_tbl, l_active_date_first_type_tbl,
605 l_active_date_second_tbl, l_active_date_second_type_tbl,
606 l_line_quantity_tbl, l_line_uom_code_tbl, l_request_type_code_tbl,
607 l_priced_quantity_tbl, l_priced_uom_code_tbl,
608 l_currency_code_tbl,
609 l_unit_price_tbl, l_percent_price_tbl, l_uom_quantity_tbl,
610 l_adjusted_unit_price_tbl, l_upd_adjusted_unit_price_tbl,
611 l_processed_flag_tbl, l_price_flag_tbl,
612 l_line_id_tbl, l_processing_order_tbl,
613 l_pricing_status_code_tbl, l_pricing_status_text_tbl,
614 l_rounding_flag_tbl, l_rounding_factor_tbl,
615 l_qualifiers_exist_flag_tbl, l_pricing_attrs_exist_flag_tbl,
616 l_price_list_id_tbl, l_validated_flag_tbl,
617 l_price_request_code_tbl, l_usage_pricing_type_tbl,
618 l_item_number_tbl LIMIT rows;
619
620 --Set line_index values in the l_line_index_tbl plsql table
621 IF l_line_type_code_tbl.COUNT > 0 THEN
622
623 l_line_index_tbl.delete;
624
625 l_count := 0;
626
627 FOR i IN l_line_type_code_tbl.FIRST..l_line_type_code_tbl.LAST
628 LOOP
629 l_line_index_tbl(i) := l_count + i;
630 END LOOP;
631
632 l_count := l_count + insert_lines2_cur%ROWCOUNT; --highest index of the previous loop
633 fnd_file.put_line(FND_FILE.LOG, 'insert_lines2_cur rowcount = '|| l_count);
634
635 QP_PREQ_GRP.INSERT_LINES2(
636 p_LINE_INDEX => l_line_index_tbl,
637 p_LINE_TYPE_CODE => l_line_type_code_tbl,
638 p_PRICING_EFFECTIVE_DATE => l_pricing_effective_date_tbl,
639 p_ACTIVE_DATE_FIRST => l_active_date_first_tbl,
640 p_ACTIVE_DATE_FIRST_TYPE => l_active_date_first_type_tbl,
641 p_ACTIVE_DATE_SECOND => l_active_date_second_tbl,
642 p_ACTIVE_DATE_SECOND_TYPE => l_active_date_second_type_tbl,
643 p_LINE_QUANTITY => l_line_quantity_tbl,
644 p_LINE_UOM_CODE => l_line_uom_code_tbl,
645 p_REQUEST_TYPE_CODE => l_request_type_code_tbl,
646 p_PRICED_QUANTITY => l_priced_quantity_tbl,
647 p_PRICED_UOM_CODE => l_priced_uom_code_tbl,
648 p_CURRENCY_CODE => l_currency_code_tbl,
649 p_UNIT_PRICE => l_unit_price_tbl,
650 p_PERCENT_PRICE => l_percent_price_tbl,
651 p_UOM_QUANTITY => l_uom_quantity_tbl,
652 p_ADJUSTED_UNIT_PRICE => l_adjusted_unit_price_tbl,
653 p_UPD_ADJUSTED_UNIT_PRICE => l_upd_adjusted_unit_price_tbl,
654 p_PROCESSED_FLAG => l_processed_flag_tbl,
655 p_PRICE_FLAG => l_price_flag_tbl,
656 p_LINE_ID => l_line_id_tbl,
657 p_PROCESSING_ORDER => l_processing_order_tbl,
658 p_PRICING_STATUS_CODE => l_pricing_status_code_tbl,
659 p_PRICING_STATUS_TEXT => l_pricing_status_text_tbl,
660 p_ROUNDING_FLAG => l_rounding_flag_tbl,
661 p_ROUNDING_FACTOR => l_rounding_factor_tbl,
662 p_QUALIFIERS_EXIST_FLAG => l_qualifiers_exist_flag_tbl,
663 p_PRICING_ATTRS_EXIST_FLAG => l_pricing_attrs_exist_flag_tbl,
664 p_PRICE_LIST_ID => l_price_list_id_tbl,
665 p_VALIDATED_FLAG => l_validated_flag_tbl,
666 p_PRICE_REQUEST_CODE => l_price_request_code_tbl,
667 p_USAGE_PRICING_TYPE => l_usage_pricing_type_tbl,
668 x_status_code => l_return_status,
669 x_status_text => l_return_status_text
670 );
671
672 fnd_file.put_line(FND_FILE.LOG, 'insert_lines2 return status '|| l_return_status);
673 fnd_file.put_line(FND_FILE.LOG, 'insert_lines2 return text'|| l_return_status_text);
674
675 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
676 fnd_file.put_line(fnd_file.Log, ' Raising SQL Exception...');
677 RAISE SQL_EXCEPTION;
678 END IF;
679 END IF; --if l_line_type_code_tbl.count > 0
680
681 IF p_pb_input_lines_tbl.COUNT > 0 THEN
682 fnd_file.put_line(fnd_file.Log, ' p_pb_input_lines_tbl.count: ' || p_pb_input_lines_tbl.Count);
683
684 --Build sql stmts and execute them dynamically to assign values to ORDER
685 --level global structure columns
686 FOR j IN p_pb_input_lines_tbl.FIRST..p_pb_input_lines_tbl.COUNT
687 LOOP
688 --Get the orderlevel global structure col names from attributemapping setup
689 OPEN global_struct_attrs_cur(p_pb_input_header_rec.request_type_code,
690 'ORDER',
691 p_pb_input_lines_tbl(j).attribute_type,
692 p_pb_input_lines_tbl(j).context,
693 p_pb_input_lines_tbl(j).attribute);
694
695 FETCH global_struct_attrs_cur INTO l_rec;
696 --If global_struct_attrs_cur%FOUND THEN
697 --dbms_output.put_line('record fetched');
698 --END IF;
699
700 IF global_struct_attrs_cur%FOUND THEN
701 -- SYMANTEC THROUGHPUT Fix: removed the dynamic SQL execution with standard
702 -- PL/SQL code.
703 /*
704 --Assign the value to the global structure in a sql_stmt string
705 l_sql_stmt := 'BEGIN '||
706 nvl(l_rec.user_value_string, l_rec.seeded_value_string) ||
707 ' := :attr_value; ' ||
708 'END; ';
709 --Check if to_datatype( ) is required. Mostly yes.
710
711 BEGIN
712 EXECUTE IMMEDIATE l_sql_stmt
713 USING p_pb_input_lines_tbl(j).attribute_value;
714 EXCEPTION
715 WHEN OTHERS THEN
716 NULL;
717 END;
718 */
719 IF ( l_rec.user_value_string IS NOT NULL ) THEN
720 l_rec.user_value_string := p_pb_input_lines_tbl(j).attribute_value;
721 ELSE
722 l_rec.seeded_value_string := p_pb_input_lines_tbl(j).attribute_value;
723 END IF; -- check: l_rec.user_value_string is not NULL
724
725 END IF; -- check: global_struct_attrs_cur
726 CLOSE global_struct_attrs_cur;
727 END LOOP; --Loop over p_pb_input_lines_tbl l
728 END IF; --If p_pb_input_lines_tbl.count > 0
729
730 --Populate l_adhoc_lines_tbl with specific qualifier attributes - customer
731 --name, price list id and BSA id - from the price book request header.
732 --Before populating it, clean it up.
733 l_adhoc_lines_tbl.DELETE;
734
735 k := 1;
736 l_adhoc_lines_tbl(k).attribute_type := 'QUALIFIER';
737 l_adhoc_lines_tbl(k).context := 'CUSTOMER';
738 l_adhoc_lines_tbl(k).attribute := 'QUALIFIER_ATTRIBUTE16'; --Party Id
739 l_adhoc_lines_tbl(k).attribute_value :=
740 p_pb_input_header_rec.customer_attr_value;
741 k := k + 1;
742
743 l_adhoc_lines_tbl(k).attribute_type := 'QUALIFIER';
744 l_adhoc_lines_tbl(k).context := 'ASOPARTYINFO';
745 l_adhoc_lines_tbl(k).attribute := 'QUALIFIER_ATTRIBUTE1'; --Customer Party
746 l_adhoc_lines_tbl(k).attribute_value :=
747 p_pb_input_header_rec.customer_attr_value;
748 k := k + 1;
749
750 l_adhoc_lines_tbl(k).attribute_type := 'QUALIFIER';
751 l_adhoc_lines_tbl(k).context := 'CUSTOMER';
752 l_adhoc_lines_tbl(k).attribute := 'QUALIFIER_ATTRIBUTE2'; --Sold to Org Id
753 l_adhoc_lines_tbl(k).attribute_value := p_pb_input_header_rec.cust_account_id;
754 k := k + 1;
755
756 l_adhoc_lines_tbl(k).attribute_type := 'QUALIFIER';
757 l_adhoc_lines_tbl(k).context := 'INTERCOMPANY_INVOICING';
758 l_adhoc_lines_tbl(k).attribute := 'QUALIFIER_ATTRIBUTE3'; --Customer
759 l_adhoc_lines_tbl(k).attribute_value := p_pb_input_header_rec.cust_account_id;
760 k := k + 1;
761
762 IF p_pb_input_header_rec.limit_products_by = 'PRICE_LIST' OR
763 p_pb_input_header_rec.price_based_on = 'PRICE_LIST'
764 THEN
765 l_adhoc_lines_tbl(k).attribute_type := 'QUALIFIER';
766 l_adhoc_lines_tbl(k).context := 'MODLIST';
767 l_adhoc_lines_tbl(k).attribute := 'QUALIFIER_ATTRIBUTE4';
768 l_adhoc_lines_tbl(k).attribute_value :=
769 p_pb_input_header_rec.pl_agr_bsa_id;
770 k := k + 1;
771 END IF;
772
773 IF p_pb_input_header_rec.price_based_on = 'AGREEMENT'
774 THEN
775 l_adhoc_lines_tbl(k).attribute_type := 'QUALIFIER';
776 l_adhoc_lines_tbl(k).context := 'MODLIST';
777 l_adhoc_lines_tbl(k).attribute := 'QUALIFIER_ATTRIBUTE4';
778 BEGIN
779 SELECT price_list_id
780 INTO l_adhoc_lines_tbl(k).attribute_value
781 FROM oe_agreements_vl
782 WHERE agreement_id = p_pb_input_header_rec.pl_agr_bsa_id;
783 EXCEPTION
784 WHEN OTHERS THEN
785 l_adhoc_lines_tbl(k).attribute_value := null;
786 END;
787 k := k + 1;
788 END IF;
789
790 IF p_pb_input_header_rec.price_based_on = 'BSA' THEN
791 BEGIN
792 SELECT order_number
793 INTO l_blanket_number
794 FROM oe_blanket_headers_all
795 WHERE header_id = p_pb_input_header_rec.pl_agr_bsa_id;
796 EXCEPTION
797 WHEN OTHERS THEN
798 l_blanket_number := NULL;
799 --Add exception handling
800 END;
801 --source the blanket number as a qualifier attribute
802 l_adhoc_lines_tbl(k).attribute_type := 'QUALIFIER';
803 l_adhoc_lines_tbl(k).context := 'ORDER';
804 l_adhoc_lines_tbl(k).attribute := 'QUALIFIER_ATTRIBUTE3';
805 l_adhoc_lines_tbl(k).attribute_value := l_blanket_number;
806 k := k + 1;
807
808 --source the BSA's price list
809 l_adhoc_lines_tbl(k).attribute_type := 'QUALIFIER';
810 l_adhoc_lines_tbl(k).context := 'MODLIST';
811 l_adhoc_lines_tbl(k).attribute := 'QUALIFIER_ATTRIBUTE4';
812 BEGIN
813 SELECT price_list_id
814 INTO l_bsa_hdr_price_list_id
815 FROM oe_blanket_headers_all
816 WHERE header_id = p_pb_input_header_rec.pl_agr_bsa_id;
817 EXCEPTION
818 WHEN OTHERS THEN
819 l_bsa_hdr_price_list_id := null;
820 END;
821 l_adhoc_lines_tbl(k).attribute_value := l_bsa_hdr_price_list_id;
822 k := k + 1;
823 END IF;
824
825 IF l_adhoc_lines_tbl.COUNT > 0 THEN
826 --For the adhoc qualifiers set above build sql stmts and execute
827 --dynamically to assign values to the ORDER level global structure columns
828 FOR j IN 1..l_adhoc_lines_tbl.COUNT
829 LOOP
830 OPEN global_struct_attrs_cur(p_pb_input_header_rec.request_type_code,
831 'ORDER',
832 l_adhoc_lines_tbl(j).attribute_type,
833 l_adhoc_lines_tbl(j).context,
834 l_adhoc_lines_tbl(j).attribute);
835 FETCH global_struct_attrs_cur INTO l_rec;
836 --If global_struct_attrs_cur%FOUND THEN
837 --dbms_output.put_line('record fetched 2');
838 --END IF;
839 IF global_struct_attrs_cur%FOUND THEN
840 -- SYMANTEC THROUGHPUT Fix: removed the dynamic SQL execution with standard
841 -- PL/SQL code.
842 /*
843 --Assign the value to the global structure in a sql_stmt string
844 l_sql_stmt := ''; --initialize
845 l_sql_stmt := 'BEGIN '||
846 nvl(l_rec.user_value_string, l_rec.seeded_value_string) ||
847 ' := :attr_value; ' ||
848 'END; ';
849 --Check if to_datatype( ) is required. Mostly yes.
850
851 BEGIN
852 EXECUTE IMMEDIATE l_sql_stmt
853 USING l_adhoc_lines_tbl(j).attribute_value;
854 EXCEPTION
855 WHEN OTHERS THEN
856 NULL;
857 END;
858 */
859 IF ( l_rec.user_value_string IS NOT NULL ) THEN
860 l_rec.user_value_string := l_adhoc_lines_tbl(j).attribute_value;
861 ELSE
862 l_rec.seeded_value_string := l_adhoc_lines_tbl(j).attribute_value;
863 END IF;
864
865 END IF; -- check: global_struct_attrs_cur%FOuND
866 CLOSE global_struct_attrs_cur;
867 END LOOP; --over l_adhoc_lines_tbl
868 END IF; --If l_adhoc_lines_tbl.count > 0
869
870 --For agreement_id since there is no attribute to map the global structure
871 --column for agreement_id, directly assign value to the ORDER level global
872 --structure column
873 IF p_pb_input_header_rec.price_based_on = 'AGREEMENT' THEN
874 OE_ORDER_PUB.G_HDR.agreement_id := p_pb_input_header_rec.pl_agr_bsa_id;
875 END IF;
876
877 IF l_line_type_code_tbl.COUNT > 0 THEN
878 FOR ii IN l_line_type_code_tbl.FIRST..l_line_type_code_tbl.LAST
879 LOOP
880 IF p_pb_input_lines_tbl.COUNT > 0 THEN
881 --Build sql stmts and execute them dynamically to assign values to
882 --LINE level global structure columns
883 FOR j IN p_pb_input_lines_tbl.FIRST..p_pb_input_lines_tbl.LAST
884 LOOP
885 --Get the line level global structure col names from attribute
886 --mapping setup
887 OPEN global_struct_attrs_cur(p_pb_input_header_rec.request_type_code,
888 'LINE',
889 p_pb_input_lines_tbl(j).attribute_type,
890 p_pb_input_lines_tbl(j).context,
891 p_pb_input_lines_tbl(j).attribute);
892
893 FETCH global_struct_attrs_cur INTO l_rec;
894
895 IF global_struct_attrs_cur%FOUND THEN
896
897 -- uncommenting for Pattern since it is required to populate global variable
898 -- before calling build context otherwise attribute will not be sourced.
899
900 -- SYMANTEC THROUGHPUT Fix: removed the dynamic SQL execution with standard
901 -- PL/SQL code.
902
903 --Assign the value to the global structure in a sql_stmt string
904 l_sql_stmt := 'BEGIN '||
905 nvl(l_rec.user_value_string, l_rec.seeded_value_string) ||
906 ' := :attr_value; ' ||
907 'END; ';
908 --Check if to_datatype( ) is required. Mostly yes.
909
910 BEGIN
911 EXECUTE IMMEDIATE l_sql_stmt
912 USING p_pb_input_lines_tbl(j).attribute_value;
913 EXCEPTION
914 WHEN OTHERS THEN
915 NULL;
916 END;
917
918 IF ( l_rec.user_value_string IS NOT NULL ) THEN
919 l_rec.user_value_string := p_pb_input_lines_tbl(j).attribute_value;
920 ELSE
921 l_rec.seeded_value_string := p_pb_input_lines_tbl(j).attribute_value;
922 END IF;
923
924 END IF;
925 CLOSE global_struct_attrs_cur;
926
927 END LOOP;--Loop over p_pb_input_lines_tbl for LINE level global struct
928 END IF; --If p_pb_input_lines_tbl.count > 0
929
930 --Add inventory_item_id to the attributes in the adhoc_lines_tbl
931 --Note that we are not incrementing k after since we want the kth
932 --element to be overwritten for each repetition of the line_index loop
933 l_adhoc_lines_tbl(k).attribute_type := 'PRODUCT';
934 l_adhoc_lines_tbl(k).context := 'ITEM';
935 l_adhoc_lines_tbl(k).attribute := 'PRICING_ATTRIBUTE1';
936 l_adhoc_lines_tbl(k).attribute_value := l_item_number_tbl(ii);
937
938
939 IF l_adhoc_lines_tbl.COUNT > 0 THEN
940 --For the adhoc qualifiers set earlier build sql stmts and execute
941 --dynamically to assign values to the LINE level global structure
942 --columns
943 FOR j IN 1..l_adhoc_lines_tbl.COUNT
944 LOOP
945 OPEN global_struct_attrs_cur(p_pb_input_header_rec.request_type_code,
946 'LINE',
947 l_adhoc_lines_tbl(j).attribute_type,
948 l_adhoc_lines_tbl(j).context,
949 l_adhoc_lines_tbl(j).attribute);
950 FETCH global_struct_attrs_cur INTO l_rec;
951 IF global_struct_attrs_cur%FOUND THEN
952 --Assign the value to the global structure in a sql_stmt string
953 l_sql_stmt := 'BEGIN '||
954 nvl(l_rec.user_value_string, l_rec.seeded_value_string) ||
955 ' := :attr_value; ' ||
956 'END; ';
957 --Check if to_datatype( ) is required. Mostly yes.
958
959 BEGIN
960 EXECUTE IMMEDIATE l_sql_stmt
961 USING l_adhoc_lines_tbl(j).attribute_value;
962 EXCEPTION
963 WHEN OTHERS THEN
964 NULL;
965 END;
966 END IF;
967 CLOSE global_struct_attrs_cur;
968 END LOOP; --over l_adhoc_lines_tbl for LINE level global structure
969 END IF; --If l_adhoc_lines_tbl.count > 0
970
971 --For agreement_id since there is no attribute to map the global
972 --structure column for agreement_id, directly assign value to the LINE
973 --level global structure column
974 IF p_pb_input_header_rec.price_based_on = 'AGREEMENT' THEN
975 OE_ORDER_PUB.G_LINE.agreement_id :=
976 p_pb_input_header_rec.pl_agr_bsa_id;
977 l_validated_flag := 'Y';
978 ELSE
979 l_validated_flag := 'N';
980 END IF;
981
982 --If price_based_on is BSA then map the line-level global structure
983 --column for price list to the one on the BSA line for the specific
984 --item and pricing effectivity date
985 IF p_pb_input_header_rec.price_based_on = 'BSA' THEN
986 BEGIN
987 SELECT a.price_list_id
988 INTO l_bsa_line_price_list_id
989 FROM oe_blanket_lines_all a, oe_blanket_lines_ext b
990 WHERE a.header_id = p_pb_input_header_rec.pl_agr_bsa_id
991 AND a.line_id = b.line_id
992 AND a.inventory_item_id = l_item_number_tbl(ii)
993 AND p_pb_input_header_rec.effective_date BETWEEN
994 nvl(trunc(b.start_date_active),
995 p_pb_input_header_rec.effective_date) AND
996 nvl(trunc(b.end_date_active),
997 p_pb_input_header_rec.effective_date);
998 EXCEPTION
999 WHEN OTHERS THEN
1000 l_bsa_line_price_list_id := null;
1001 END;
1002
1003 --Assign blanket pricelist to appropriate line level structure column
1004 OE_ORDER_PUB.G_LINE.price_list_id :=
1005 nvl(l_bsa_line_price_list_id, l_bsa_hdr_price_list_id);
1006
1007 END IF; --If price_based_on is 'BSA'
1008
1009 --Call Build Contexts for line record
1010 QP_Attr_Mapping_PUB.Build_Contexts(
1011 p_request_type_code => p_pb_input_header_rec.request_type_code,
1012 p_line_index => l_line_index_tbl(ii),
1013 p_pricing_type_code => 'L',
1014 p_price_list_validated_flag => l_validated_flag,
1015 p_org_id => p_pb_input_header_rec.org_id);
1016
1017 --Clear the plsql tables, fetch User-Entered attributes from the
1018 --pricebook input lines table, then insert into the line attrs temp
1019 --table using the qp_preq_grp.insert_line_attrs2 API
1020 OPEN insert_line_attrs2_cur(l_line_index_tbl(ii),
1021 p_pb_input_header_rec.request_type_code,
1022 p_pb_input_header_rec.pb_input_header_id);
1023 LOOP
1024 l_attrs_line_index_tbl.delete;
1025 l_attrs_line_detail_index_tbl.delete;
1026 l_attrs_attribute_level_tbl.delete;
1027 l_attrs_attribute_type_tbl.delete;
1028 l_attrs_list_header_id_tbl.delete;
1029 l_attrs_list_line_id_tbl.delete;
1030 l_attrs_context_tbl.delete;
1031 l_attrs_attribute_tbl.delete;
1032 l_attrs_value_from_tbl.delete;
1033 l_attrs_setup_value_from_tbl.delete;
1034 l_attrs_value_to_tbl.delete;
1035 l_attrs_setup_value_to_tbl.delete;
1036 l_attrs_grouping_number_tbl.delete;
1037 l_attrs_no_quals_in_grp_tbl.delete;
1038 l_attrs_comp_oper_type_tbl.delete;
1039 l_attrs_validated_flag_tbl.delete;
1040 l_attrs_applied_flag_tbl.delete;
1041 l_attrs_pri_status_code_tbl.delete;
1042 l_attrs_pri_status_text_tbl.delete;
1043 l_attrs_qual_precedence_tbl.delete;
1044 l_attrs_datatype_tbl.delete;
1045 l_attrs_pricing_attr_flag_tbl.delete;
1046 l_attrs_qualifier_type_tbl.delete;
1047 l_attrs_product_uom_code_tbl.delete;
1048 l_attrs_excluder_flag_tbl.delete;
1049 l_attrs_pricing_phase_id_tbl.delete;
1050 l_attrs_incomp_grp_code_tbl.delete;
1051 l_attrs_line_det_typ_code_tbl.delete;
1052 l_attrs_modif_level_code_tbl.delete;
1053 l_attrs_primary_uom_flag_tbl.delete;
1054
1055 FETCH insert_line_attrs2_cur BULK COLLECT INTO
1056 l_attrs_line_index_tbl, l_attrs_line_detail_index_tbl,
1057 l_attrs_attribute_level_tbl, l_attrs_attribute_type_tbl,
1058 l_attrs_list_header_id_tbl, l_attrs_list_line_id_tbl,
1059 l_attrs_context_tbl, l_attrs_attribute_tbl,
1060 l_attrs_value_from_tbl, l_attrs_setup_value_from_tbl,
1061 l_attrs_value_to_tbl, l_attrs_setup_value_to_tbl,
1062 l_attrs_grouping_number_tbl, l_attrs_no_quals_in_grp_tbl,
1063 l_attrs_comp_oper_type_tbl, l_attrs_validated_flag_tbl,
1064 l_attrs_applied_flag_tbl, l_attrs_pri_status_code_tbl,
1065 l_attrs_pri_status_text_tbl, l_attrs_qual_precedence_tbl,
1066 l_attrs_datatype_tbl, l_attrs_pricing_attr_flag_tbl,
1067 l_attrs_qualifier_type_tbl, l_attrs_product_uom_code_tbl,
1068 l_attrs_excluder_flag_tbl, l_attrs_pricing_phase_id_tbl,
1069 l_attrs_incomp_grp_code_tbl, l_attrs_line_det_typ_code_tbl,
1070 l_attrs_modif_level_code_tbl, l_attrs_primary_uom_flag_tbl
1071 LIMIT rows;
1072
1073 IF l_attrs_line_index_tbl.count > 0 THEN
1074 QP_PREQ_GRP.INSERT_LINE_ATTRS2(
1075 p_LINE_INDEX_tbl => l_attrs_line_index_tbl,
1076 p_LINE_DETAIL_INDEX_tbl => l_attrs_line_detail_index_tbl,
1077 p_ATTRIBUTE_LEVEL_tbl => l_attrs_attribute_level_tbl,
1078 p_ATTRIBUTE_TYPE_tbl => l_attrs_attribute_type_tbl,
1079 p_LIST_HEADER_ID_tbl => l_attrs_list_header_id_tbl,
1080 p_LIST_LINE_ID_tbl => l_attrs_list_line_id_tbl,
1081 p_CONTEXT_tbl => l_attrs_context_tbl,
1082 p_ATTRIBUTE_tbl => l_attrs_attribute_tbl,
1083 p_VALUE_FROM_tbl => l_attrs_value_from_tbl,
1084 p_SETUP_VALUE_FROM_tbl => l_attrs_setup_value_from_tbl,
1085 p_VALUE_TO_tbl => l_attrs_value_to_tbl,
1086 p_SETUP_VALUE_TO_tbl => l_attrs_setup_value_to_tbl,
1087 p_GROUPING_NUMBER_tbl => l_attrs_grouping_number_tbl,
1088 p_NO_QUALIFIERS_IN_GRP_tbl => l_attrs_no_quals_in_grp_tbl,
1089 p_COMPARISON_OPERATOR_TYPE_tbl => l_attrs_comp_oper_type_tbl,
1090 p_VALIDATED_FLAG_tbl => l_attrs_validated_flag_tbl,
1091 p_APPLIED_FLAG_tbl => l_attrs_applied_flag_tbl,
1092 p_PRICING_STATUS_CODE_tbl => l_attrs_pri_status_code_tbl,
1093 p_PRICING_STATUS_TEXT_tbl => l_attrs_pri_status_text_tbl,
1094 p_QUALIFIER_PRECEDENCE_tbl => l_attrs_qual_precedence_tbl,
1095 p_DATATYPE_tbl => l_attrs_datatype_tbl,
1096 p_PRICING_ATTR_FLAG_tbl => l_attrs_pricing_attr_flag_tbl,
1097 p_QUALIFIER_TYPE_tbl => l_attrs_qualifier_type_tbl,
1098 p_PRODUCT_UOM_CODE_TBL => l_attrs_product_uom_code_tbl,
1099 p_EXCLUDER_FLAG_TBL => l_attrs_excluder_flag_tbl,
1100 p_PRICING_PHASE_ID_TBL => l_attrs_pricing_phase_id_tbl,
1101 p_INCOMPATABILITY_GRP_CODE_TBL => l_attrs_incomp_grp_code_tbl,
1102 p_LINE_DETAIL_TYPE_CODE_TBL => l_attrs_line_det_typ_code_tbl,
1103 p_MODIFIER_LEVEL_CODE_TBL => l_attrs_modif_level_code_tbl,
1104 p_PRIMARY_UOM_FLAG_TBL => l_attrs_primary_uom_flag_tbl,
1105 x_status_code => l_return_status,
1106 x_status_text => l_return_status_text
1107 );
1108
1109 fnd_file.put_line(FND_FILE.LOG, 'insert_line_attrs2 return status '||l_return_status);
1110 fnd_file.put_line(FND_FILE.LOG, 'insert_line_attrs2 return text'||l_return_status_text);
1111
1112 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1113 RAISE SQL_EXCEPTION;
1114 END IF;
1115
1116 END IF; -- If l_attrs_line_index_tbl.count > 0
1117
1118 EXIT WHEN insert_line_attrs2_cur%NOTFOUND;
1119
1120 END LOOP; --Loop over cursor insert_line_attrs2_cur
1121
1122 CLOSE insert_line_attrs2_cur;
1123
1124
1125 END LOOP; --Loop over l_line_type_code_tbl
1126
1127 END IF; --If l_line_type_code_tbl.count > 0
1128
1129 -- EXIT WHEN insert_lines2_cur%NOTFOUND;
1130
1131 -- SNIMMAGA.
1132 -- Call the pricing engine for the current 'chunk' of the price list lines,
1133 -- and attributes. Once that's done for this chunk, insert that data into
1134 -- price book.
1135
1136 --------
1137 -- TODO: Identify the records requiring clean-up per every iteration, and
1138 -- write the clean-up code before the immediate hyphenated string.
1139
1140 ----------------------------------
1141 QP_PREQ_PUB.g_call_from_price_book := 'Y';
1142
1143 --Fetch he Pricing Events info from the Pricing Parameters setup
1144 l_pricing_events := QP_Param_Util.Get_Parameter_Value (
1145 p_level => 'REQ', --For Request Type level
1146 p_level_name => p_pb_input_header_rec.pricing_perspective_code,
1147 p_parameter_code => 'QP_PRICE_BOOK_PRICING_EVENTS');
1148
1149 fnd_file.put_line(FND_FILE.LOG, 'Pricing Events - '||l_pricing_events);
1150 --Set the l_control_rec field values
1151 l_control_rec.calculate_flag := QP_PREQ_GRP.G_SEARCH_N_CALCULATE;
1152 l_control_rec.simulation_flag := 'Y';
1153 l_control_rec.pricing_event := l_pricing_events; --from pricing parameter
1154 l_control_rec.temp_table_insert_flag := 'N';
1155 l_control_rec.check_cust_view_flag := 'N'; --Find out what this is
1156 l_control_rec.request_type_code := p_pb_input_header_rec.request_type_code;
1157 l_control_rec.rounding_flag := 'Q';
1158 l_control_rec.use_multi_currency:= null;
1159 l_control_rec.function_currency:= null;
1160 l_control_rec.org_id := p_pb_input_header_rec.org_id;
1161 l_control_rec.full_pricing_call := 'Y';
1162
1163 --fnd_file.put_line(fnd_file.Log, 'l_request_type_code: '|| l_request_type_code);
1164 fnd_file.put_line(fnd_file.Log, 'Before Pricing Engine call');
1165
1166 --Call the Pricing Engine
1167
1168 QP_PREQ_PUB.Price_Request(l_control_rec,
1169 l_return_status,
1170 l_return_status_text);
1171
1172 fnd_file.put_line(fnd_file.Log, 'After Pricing Engine Call...');
1173 fnd_file.put_line(FND_FILE.LOG, 'Price_Request return status: '||l_return_status);
1174 fnd_file.put_line(FND_FILE.LOG, 'Price_Request return text: '||l_return_status_text);
1175
1176 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1177 RAISE SQL_EXCEPTION;
1178 END IF;
1179
1180 OPEN lines_cur;
1181 LOOP
1182 l_line_index_tbl.delete;
1183 l_line_id_tbl.delete;
1184 l_list_price_tbl.delete;
1185 l_net_price_tbl.delete;
1186 l_pricing_status_code_tbl.delete;
1187 l_pricing_status_text_tbl.delete;
1188
1189 FETCH lines_cur BULK COLLECT INTO l_line_index_tbl, l_list_price_tbl,
1190 l_net_price_tbl, l_line_id_tbl,
1191 l_pricing_status_code_tbl, l_pricing_status_text_tbl
1192 LIMIT rows;
1193
1194 --Update the list and net price in the price book lines table with the values
1195 --calculated by the engine that are in the lines temp table
1196 FORALL i IN l_line_index_tbl.FIRST..l_line_index_tbl.LAST
1197 UPDATE qp_price_book_lines
1198 SET list_price = l_list_price_tbl(i),
1199 net_price = l_net_price_tbl(i)
1200 WHERE price_book_line_id = l_line_id_tbl(i);
1201
1202 --Copy price book line level messages from lines_tmp table into
1203 --qp_price_book_messages
1204 IF l_line_id_tbl.COUNT > 0 THEN
1205 FOR i IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
1206 LOOP
1207 IF l_pricing_status_code_tbl(i) NOT IN ('UPDATED', 'UNCHANGED') THEN
1208 --Exclude informational messages
1209 INSERT INTO qp_price_book_messages
1210 (message_id, message_type, message_code, message_text,
1211 pb_input_header_id, price_book_header_id, price_book_line_id,
1212 creation_date, created_by, last_update_date, last_updated_by,
1213 last_update_login
1214 )
1215 VALUES
1216 (qp_price_book_messages_s.nextval, 'E', l_pricing_status_code_tbl(i),
1217 l_pricing_status_text_tbl(i), null, p_price_book_header_id,
1218 l_line_id_tbl(i), sysdate, l_user_id, sysdate, l_user_id,
1219 l_login_id
1220 );
1221 END IF;
1222 END LOOP;
1223 END IF; --If l_line_id_tbl.COUNT > 0
1224
1225 EXIT WHEN lines_cur%NOTFOUND;
1226
1227 END LOOP; --Loop over lines_cur
1228 CLOSE lines_cur;
1229
1230 l_inv_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
1231
1232 --For each item(line) in the price book insert the parent categories into the
1233 --qp_price_book_attributes table. The line_detail_id will be -1 since these
1234 --attributes are at the price book line level and used for UI and get_catalog
1235 --purposes.
1236 OPEN pb_items_cur (p_price_book_header_id);
1237 LOOP
1238
1239 l_line_id_tbl.delete;
1240 l_item_number_tbl.delete;
1241
1242 FETCH pb_items_cur BULK COLLECT INTO l_line_id_tbl, l_item_number_tbl
1243 LIMIT rows;
1244
1245 FORALL i IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
1246 INSERT INTO qp_price_book_attributes
1247 (price_book_attribute_id, price_book_line_det_id,
1248 price_book_line_id, price_book_header_id,
1249 pricing_prod_context, pricing_prod_attribute,
1250 comparison_operator_code, pricing_prod_attr_value_from,
1251 pricing_attr_value_to, pricing_prod_attr_datatype,
1252 attribute_type, creation_date, created_by, last_update_date,
1253 last_updated_by, last_update_login
1254 )
1255 SELECT qp_price_book_attributes_s.nextval, -1,
1256 l_line_id_tbl(i), p_price_book_header_id,
1257 'ITEM', 'PRICING_ATTRIBUTE2', --Item Category
1258 '=', category_id, null, 'N',
1259 'PRODUCT', sysdate, l_user_id, sysdate,
1260 l_user_id, l_login_id
1261 FROM (SELECT DISTINCT a.category_id
1262 FROM mtl_item_categories a, mtl_categories_b b,
1263 mtl_category_sets_b c, mtl_default_category_sets d
1264 WHERE a.inventory_item_id = l_item_number_tbl(i)
1265 AND a.organization_id = l_inv_org_id --inventory org, not OU
1266 AND b.category_id = a.category_id
1267 AND c.structure_id = b.structure_id
1268 AND d.category_set_id = c.category_set_id
1269 AND d.functional_area_id IN
1270 (SELECT ssf.functional_area_id
1271 FROM qp_pte_source_systems pss,
1272 qp_pte_request_types_b prt,
1273 qp_sourcesystem_fnarea_map ssf
1274 WHERE pss.pte_code = prt.pte_code
1275 AND pss.enabled_flag = 'Y'
1276 AND prt.enabled_flag = 'Y'
1277 AND ssf.enabled_flag = 'Y'
1278 AND prt.request_type_code = p_pb_input_header_rec.request_type_code
1279 AND pss.pte_source_system_id = ssf.pte_source_system_id)
1280 );
1281
1282 EXIT WHEN pb_items_cur%NOTFOUND;
1283
1284 END LOOP; --loop over pb_items_cur
1285 CLOSE pb_items_cur;
1286
1287 OPEN line_dets_cur;
1288 LOOP
1289 l_cf_list_header_id_tbl.delete;
1290 l_cf_list_line_id_tbl.delete;
1291 l_list_line_no_tbl.delete;
1292 l_list_price_tbl.delete;
1293 l_modifier_operand_tbl.delete;
1294 l_modifier_appl_method_tbl.delete;
1295 l_adjustment_amount_tbl.delete;
1296 l_list_line_type_code_tbl.delete;
1297 l_price_break_type_code_tbl.delete;
1298 l_line_index_tbl.delete;
1299 l_line_detail_index_tbl.delete;
1300 l_line_id_tbl.delete;
1301 l_pricing_phase_id_tbl.delete;
1302
1303 l_pb_line_det_id_tbl.delete;
1304 l_pb_line_id_tbl.delete;
1305 l_line_index2_tbl.delete;
1306 l_line_detail_index2_tbl.delete;
1307 l_list_line_type_code2_tbl.delete;
1308
1309 FETCH line_dets_cur
1310 BULK COLLECT INTO l_cf_list_header_id_tbl,
1311 l_cf_list_line_id_tbl, l_list_line_no_tbl,
1312 l_list_price_tbl, l_modifier_operand_tbl,
1313 l_modifier_appl_method_tbl, l_adjustment_amount_tbl,
1314 l_list_line_type_code_tbl, l_pricing_phase_id_tbl,
1315 l_price_break_type_code_tbl, l_line_index_tbl,
1316 l_line_detail_index_tbl, l_line_id_tbl
1317 LIMIT rows;
1318
1319 fnd_file.put_line(FND_FILE.LOG, 'No of line details '||to_char(l_line_id_tbl.count));
1320
1321 --Insert records into the price book line details table from the line
1322 --details temp table
1323 FORALL j IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
1324 INSERT INTO qp_price_book_line_details
1325 (price_book_line_det_id, price_book_line_id,
1326 price_book_header_id,
1327 list_header_id, list_line_id,
1328 list_line_no, list_price,
1329 modifier_operand, modifier_application_method, adjustment_amount,
1330 adjusted_net_price, list_line_type_code,
1331 price_break_type_code,
1332 creation_date, created_by, last_update_date,
1333 last_updated_by, last_update_login
1334 )
1335 VALUES
1336 (qp_price_book_line_details_s.nextval, l_line_id_tbl(j),
1337 p_price_book_header_id,
1338 l_cf_list_header_id_tbl(j), l_cf_list_line_id_tbl(j),
1339 decode(l_pricing_phase_id_tbl(j), 1, null, l_list_line_no_tbl(j)),
1340 --insert null for list_line_no in case of PLL, PBH price list line
1341 decode(l_list_price_tbl(j), l_adjustment_amount_tbl(j), null,
1342 l_list_price_tbl(j)),
1343 l_modifier_operand_tbl(j), l_modifier_appl_method_tbl(j),
1344 l_adjustment_amount_tbl(j), null,
1345 l_list_line_type_code_tbl(j), l_price_break_type_code_tbl(j),
1346 sysdate, l_user_id, sysdate, l_user_id, l_login_id)
1347 RETURNING price_book_line_det_id, price_book_line_id, l_line_index_tbl(j),
1348 l_line_detail_index_tbl(j), l_list_line_type_code_tbl(j)
1349 BULK COLLECT INTO l_pb_line_det_id_tbl, l_pb_line_id_tbl,
1350 l_line_index2_tbl, l_line_detail_index2_tbl, l_list_line_type_code2_tbl;
1351
1352 --Insert records into the price book attrs table from the line attrs temp
1353 --table for Price List Lines
1354 FORALL k IN l_line_index2_tbl.FIRST..l_line_index2_tbl.LAST
1355 INSERT INTO qp_price_book_attributes
1356 (price_book_attribute_id, price_book_line_det_id,
1357 price_book_line_id, price_book_header_id,
1358 pricing_prod_context, pricing_prod_attribute,
1359 comparison_operator_code, pricing_prod_attr_value_from,
1360 pricing_attr_value_to, pricing_prod_attr_datatype,
1361 attribute_type, creation_date, created_by, last_update_date,
1362 last_updated_by, last_update_login
1363 )
1364 SELECT qp_price_book_attributes_s.nextval, a.pb_line_det_id,
1365 a.pb_line_id, a.price_book_header_id,
1366 a.context, a.attribute,
1367 a.comparison_operator_type_code, a.setup_value_from,
1368 a.setup_value_to, a.datatype, a.attribute_type,
1369 sysdate, l_user_id, sysdate,
1370 l_user_id, l_login_id
1371 FROM (SELECT DISTINCT l_pb_line_det_id_tbl(k) pb_line_det_id,
1372 l_pb_line_id_tbl(k) pb_line_id,
1373 p_price_book_header_id price_book_header_id,
1374 a.context, a.attribute,
1375 a.comparison_operator_type_code, a.setup_value_from,
1376 a.setup_value_to, a.datatype,
1377 decode(a.attribute_type, QP_PREQ_GRP.G_PRICING_TYPE,
1378 'PRICING_ATTRIBUTE', a.attribute_type) attribute_type
1379 FROM qp_preq_line_attrs_tmp a, qp_list_headers_vl b
1380 WHERE a.line_index = l_line_index2_tbl(k)
1381 AND a.line_detail_index = l_line_detail_index2_tbl(k)
1382 AND a.list_header_id = b.list_header_id
1383 AND b.list_type_code = 'PRL'
1384 AND a.attribute_type = QP_PREQ_GRP.G_PRICING_TYPE
1385 --Only pricing attributes
1386 AND a.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW) a;
1387
1388 -- (08/03/06) Added distinct in the select statement above since the
1389 -- pricing engine creates multiple records for pricing attributes in
1390 -- the tmp table depending on the number of qualifier attributes
1391 -- attached to a pricelist. However split the select stmt since
1392 -- sequence.next cannot be selected in conjunction with DISTINCT.
1393
1394 fnd_file.put_line(FND_FILE.LOG, 'No of pricing attributes '||to_char(sql%rowcount));
1395
1396 --Insert records into the price book break lines table from the line attrs
1397 --temp table for PBH Price List and Modifier Lines
1398 FORALL k IN l_line_index2_tbl.FIRST..l_line_index2_tbl.LAST
1399 INSERT INTO qp_price_book_break_lines
1400 (price_book_break_line_id, price_book_line_det_id,
1401 price_book_line_id, price_book_header_id,
1402 pricing_context, pricing_attribute, pricing_attr_value_from,
1403 pricing_attr_value_to, comparison_operator_code,
1404 pricing_attribute_datatype, operand, application_method,
1405 recurring_value,
1406 creation_date, created_by, last_update_date, last_updated_by,
1407 last_update_login)
1408 SELECT /*+ ORDERED index(a QP_PREQ_LINE_ATTRS_TMP_N3) */ qp_price_book_break_lines_s.nextval, l_pb_line_det_id_tbl(k),
1409 l_pb_line_id_tbl(k), p_price_book_header_id,
1410 a.context, a.attribute, a.value_from,
1411 a.value_to, a.comparison_operator_type_code,
1412 a.datatype, b.operand_value, b.operand_calculation_code,
1413 b.recurring_value,
1414 sysdate, l_user_id, sysdate, l_user_id, l_login_id
1415 FROM qp_preq_rltd_lines_tmp r, qp_preq_ldets_tmp b, qp_preq_line_attrs_tmp a
1416 WHERE l_list_line_type_code2_tbl(k) = 'PBH'
1417 AND r.line_index = l_line_index2_tbl(k)
1418 AND r.line_detail_index = l_line_detail_index2_tbl(k)
1419 AND r.relationship_type_code = QP_PREQ_GRP.G_PBH_LINE -- just in case db goes to table before first condition
1420 AND b.line_detail_index = r.related_line_detail_index
1421 AND a.line_detail_index = b.line_detail_index;
1422
1423 fnd_file.put_line(FND_FILE.LOG, 'No of price breaks '||to_char(sql%rowcount));
1424
1425 EXIT WHEN line_dets_cur%NOTFOUND;
1426
1427 END LOOP; --loop over line_dets_cur
1428 CLOSE line_dets_cur;
1429 ----------------------------------
1430 EXIT WHEN insert_lines2_cur%NOTFOUND;
1431 END LOOP; --Loop over cursor insert_lines2_cur
1432 CLOSE insert_lines2_cur;
1433
1434 EXCEPTION
1435 WHEN FND_API.G_EXC_ERROR THEN
1436 RAISE;
1437 WHEN OTHERS THEN
1438 RAISE;
1439 END Insert_Price_Book_Content;
1440
1441
1442
1443 /*****************************************************************************
1444 Private API to Generate and/or Publish Price Book
1445 ******************************************************************************/
1446 PROCEDURE Generate_Publish_Price_Book(p_pb_input_header_id IN NUMBER,
1447 x_request_id OUT NOCOPY NUMBER,
1448 x_return_status OUT NOCOPY VARCHAR2,
1449 x_retcode OUT NOCOPY NUMBER,
1450 x_err_buf OUT NOCOPY VARCHAR2)
1451 IS
1452 x_return_text VARCHAR2(2000) := NULL;
1453 l_generation_time_code VARCHAR2(30);
1454 l_gen_schedule_date DATE;
1455
1456 BEGIN
1457
1458 --Perform validation of input criteria
1459 QP_PRICE_BOOK_UTIL.Validate_PB_Inp_Criteria_Wrap(
1460 p_pb_input_header_id => p_pb_input_header_id,
1461 x_return_status => x_return_status,
1462 x_return_text => x_return_text);
1463
1464 IF x_return_status = 'E' THEN
1465 RETURN;
1466 END IF;
1467
1468 --submit the parent concurrent request
1469
1470 x_request_id := FND_REQUEST.SUBMIT_REQUEST(
1471 'QP', 'QPXPRBKB', 'Price Book Generate and Publish',
1472 '',
1473 FALSE, p_pb_input_header_id);
1474
1475 IF x_request_id = 0 THEN --Error occurred
1476 x_err_buf := substr(FND_MESSAGE.GET, 1, 240);
1477 x_retcode := 2;
1478 x_return_status := 'E';
1479 ELSE -- conc request submission successful
1480 x_err_buf := '';
1481 x_retcode := 0;
1482 x_return_status := 'S';
1483
1484 --Update the input header table with the request_id of the parent conc request
1485 UPDATE qp_pb_input_headers_b
1486 SET request_id = x_request_id
1487 WHERE pb_input_header_id = p_pb_input_header_id;
1488
1489 COMMIT; --To complete parent concurrent request submission
1490 --dbms_output.put_line('after submitting parent concurrent pgm');
1491
1492 END IF;
1493
1494 END Generate_Publish_Price_Book;
1495
1496
1497 /*******************************************************************************
1498 Concurrent Program to Generate and/or Publish Price Book. Called by the Private API Generate_Publish_Price_Book
1499 *******************************************************************************/
1500 PROCEDURE Price_Book_Conc_Pgm(
1501 retcode OUT NOCOPY NUMBER,
1502 errbuf OUT NOCOPY VARCHAR2,
1503 p_pb_input_header_id IN NUMBER,
1504 p_customer_id IN NUMBER := NULL,
1505 p_price_book_header_id IN NUMBER := NULL,
1506 p_spawned_request IN VARCHAR2 := 'N')
1507 IS
1508 l_pb_input_header_rec qp_pb_input_headers_vl%ROWTYPE;
1509 l_pb_input_lines_tbl QP_PRICE_BOOK_UTIL.pb_input_lines_tbl;
1510 l_publish_price_book_header_id NUMBER := NULL;
1511 l_overwrite_pb_header_id NUMBER := NULL;
1512 l_item_validation_org_id NUMBER;
1513 l_child_request_id NUMBER;
1514 l_submit VARCHAR2(30) := NULL;
1515 l_document_type VARCHAR2(30) := NULL;
1516
1517 TYPE uom_tbl IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
1518 TYPE item_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1519
1520 l_item_tbl item_tbl;
1521 l_item2_tbl item_tbl;
1522 l_uom_tbl uom_tbl;
1523
1524 CURSOR pl_items_cur(a_price_list_id NUMBER)
1525 IS
1526 SELECT DISTINCT product_attr_value item_id
1527 --Since an item can occur multiple times in a pl if attributes differ
1528 FROM qp_pricing_attributes
1529 WHERE list_header_id = a_price_list_id
1530 AND product_attribute = 'PRICING_ATTRIBUTE1' --Item Number
1531 AND product_attribute_context = 'ITEM';
1532
1533 CURSOR cat_items_cur(a_category_id NUMBER, a_organization_id NUMBER)
1534 IS
1535 SELECT DISTINCT cat.inventory_item_id item_id
1536 FROM mtl_item_categories cat
1537 WHERE cat.organization_id = a_organization_id
1538 AND (cat.category_id = a_category_id
1539 OR
1540 EXISTS (SELECT 'Y'
1541 FROM eni_denorm_hierarchies
1542 WHERE parent_id = a_category_id and
1543 child_id = cat.category_id)
1544 );
1545
1546 CURSOR items_cur(a_organization_id NUMBER, a_pricing_perspective VARCHAR2)
1547 IS
1548 SELECT msi.inventory_item_id item_id
1549 FROM mtl_system_items_b msi
1550 WHERE msi.organization_id = a_organization_id
1551 AND msi.purchasing_enabled_flag = decode(a_pricing_perspective,
1552 'PO', 'Y', msi.purchasing_enabled_flag)
1553 AND EXISTS (SELECT 'X'
1554 FROM mtl_item_categories mic
1555 WHERE inventory_item_id = msi.inventory_item_id
1556 AND organization_id = msi.organization_id);
1557
1558 /*
1559 Bug 8970504
1560 Added a union to fetch all uoms which do not belong to the same class
1561 to which primary uom of given item belongs and for which price list line
1562 has been defined. As mentioned in the user guide about the UOMs which should
1563 be considered for price book-
1564
1565 The following UOM are considered:
1566 1. Primary UOM of the item.
1567 2. UOM in other UOM classes (provided those UOM are present in a price list).
1568 3. UOM in the same UOM class as the item's primary UOM code.
1569
1570 First union will satisfy point 1. Second and third union will satisfy point 3.
1571 Fourth (new) union has been added to satisfy point 2.
1572 */
1573
1574 CURSOR item_uom_cur(a_item_id NUMBER, a_organization_id NUMBER,
1575 a_effective_date DATE)
1576 IS
1577 SELECT msi.inventory_item_id item_id, msi.primary_uom_code uom_code
1578 FROM mtl_system_items msi, mtl_units_of_measure muom
1579 WHERE msi.organization_id = a_organization_id
1580 AND msi.inventory_item_id = a_item_id
1581 AND muom.uom_code = msi.primary_uom_code
1582 AND nvl(muom.disable_date, trunc(a_effective_date) + 1) >
1583 trunc(a_effective_date)
1584 UNION
1585 SELECT a_item_id item_id, muom.uom_code uom_code
1586 FROM mtl_system_items msi2,
1587 mtl_units_of_measure muom2,
1588 mtl_uom_conversions mcon,
1589 mtl_units_of_measure muom,
1590 mtl_uom_classes mcl
1591 WHERE muom2.uom_code = msi2.primary_uom_code
1592 and msi2.organization_id = a_organization_id
1593 and msi2.inventory_item_id = a_item_id
1594 and nvl(muom2.disable_date,
1595 trunc(a_effective_date) + 1) >
1596 trunc(a_effective_date)
1597 AND mcon.uom_class = muom2.uom_class
1598 AND mcon.inventory_item_id = 0
1599 AND mcon.uom_code = muom.uom_code
1600 AND nvl(mcon.disable_date,trunc(a_effective_date)+1) >
1601 trunc(a_effective_date)
1602 AND mcl.uom_class = muom.uom_class
1603 AND nvl(mcl.disable_date,trunc(a_effective_date)+1) >
1604 trunc(a_effective_date)
1605 AND nvl(muom.disable_date,trunc(a_effective_date)+1) >
1606 trunc(a_effective_date)
1607 AND EXISTS (
1608 SELECT 'x'
1609 FROM qp_pricing_attributes pa
1610 WHERE pa.product_attribute_context = 'ITEM'
1611 AND pa.product_attribute = 'PRICING_ATTRIBUTE1'
1612 AND pa.product_attr_value = To_Char(a_item_id)
1613 AND pa.product_uom_code = muom.uom_code
1614 AND pa.qualification_ind IN (4,6,20,22)
1615 AND pa.pricing_phase_id = 1
1616 )
1617 UNION
1618 SELECT a_item_id item_id, muom.uom_code uom_code
1619 FROM mtl_units_of_measure muom,
1620 mtl_uom_conversions mcon,
1621 mtl_uom_classes mcl
1622 WHERE mcon.uom_code = muom.uom_code
1623 AND mcon.inventory_item_id = a_item_id
1624 AND mcl.uom_class = muom.uom_class
1625 AND nvl(mcl.disable_date,trunc(a_effective_date)+1) >
1626 trunc(a_effective_date)
1627 AND nvl(muom.disable_date,trunc(a_effective_date)+1) >
1628 trunc(a_effective_date)
1629 AND nvl(mcon.disable_date,trunc(a_effective_date)+1) >
1630 trunc(a_effective_date)
1631 --bug 8970504
1632 UNION
1633 SELECT a_item_id item_id, a.product_uom_code
1634 FROM qp_list_lines_v a,
1635 mtl_system_items msi,
1636 mtl_units_of_measure muom,
1637 mtl_uom_classes mcl
1638 WHERE a.product_attribute_context = 'ITEM'
1639 and a.product_attribute = 'PRICING_ATTRIBUTE1'
1640 and a.product_attr_value = to_char(a_item_id)
1641 AND a.pricing_attribute_context IS NULL
1642 AND a.pricing_phase_id = 1
1643 AND msi.inventory_item_id = a.product_attr_value
1644 AND msi.organization_id = a_organization_id
1645 AND muom.uom_code = a.product_uom_code
1646 AND nvl(muom.disable_date,trunc(NVL(a_effective_date,sysdate))+1) >
1647 trunc(NVL(a_effective_date,sysdate))
1648 AND mcl.uom_class = muom.uom_class
1649 AND nvl(mcl.disable_date,trunc(NVL(a_effective_date,sysdate))+1) >
1650 trunc(NVL(a_effective_date,sysdate))
1651 AND Trunc(NVL(a_effective_date,sysdate))
1652 BETWEEN TRUNC(Nvl(a.start_date_active,NVL(a_effective_date,sysdate)))
1653 AND TRUNC(Nvl(a.end_date_active,NVL(a_effective_date,sysdate)));
1654
1655 l_user_id NUMBER;
1656 l_login_id NUMBER;
1657
1658 l_price_book_messages_tbl QP_PRICE_BOOK_UTIL.price_book_messages_tbl;
1659 l_message_text VARCHAR2(2000);
1660 i NUMBER :=1;
1661 m NUMBER;
1662 l_net_price NUMBER;
1663
1664 l_line_id_tbl NUMBER_TYPE;
1665 l_list_price_tbl NUMBER_TYPE;
1666 l_line_det_id_tbl NUMBER_TYPE;
1667 l_adjustment_amount_tbl NUMBER_TYPE;
1668 l_line_det_id_tbl2 NUMBER_TYPE;
1669 l_net_price_tbl NUMBER_TYPE;
1670 l_line_id_tbl2 NUMBER_TYPE;
1671
1672 CURSOR pb_lines_cur
1673 IS
1674 SELECT price_book_line_id, list_price
1675 FROM qp_price_book_lines
1676 WHERE price_book_header_id = p_price_book_header_id
1677 ORDER BY price_book_line_id;
1678
1679 l_old_input_header_id NUMBER;
1680 l_debug_file VARCHAR2(240);
1681 l_return_status VARCHAR2(30);
1682 l_return_status_text VARCHAR2(2000);
1683
1684 l_document_id NUMBER;
1685 l_delta_document_id NUMBER;
1686
1687 l_corr_delta_pb_header_id NUMBER;
1688 l_delta_input_header_id NUMBER;
1689
1690 l_old_request_id NUMBER;
1691 l_old_delta_request_id NUMBER;
1692 l_req_phase VARCHAR2(400);
1693 l_req_status VARCHAR2(400);
1694 l_req_dev_status VARCHAR2(400);
1695 l_req_dev_phase VARCHAR2(400);
1696 l_req_message VARCHAR2(4000);
1697 l_result BOOLEAN;
1698
1699 -- Introduced for the purpose of PL/SQL profiling (snimmaga)
1700 err NUMBER;
1701
1702 BEGIN
1703
1704 -- Introduced for the purpose of PL/SQL profiling (snimmaga)
1705 /*
1706 fnd_file.put_line(fnd_file.Log, 'Switching on PL/SQL Profiler...');
1707 err := DBMS_PROFILER.START_PROFILER (to_char(sysdate,'dd-Mon-YYYY hh:mi:ss'));
1708 */
1709
1710 fnd_file.put_line(FND_FILE.LOG, 'In Conc Program');
1711
1712 BEGIN
1713 SELECT *
1714 INTO l_pb_input_header_rec
1715 FROM qp_pb_input_headers_vl
1716 WHERE pb_input_header_id = p_pb_input_header_id;
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719 FND_MESSAGE.SET_NAME('QP', 'QP_INPUT_REC_NOT_FOUND');
1720 l_message_text := FND_MESSAGE.GET;
1721 l_price_book_messages_tbl(i).message_code :=
1722 'QP_INPUT_REC_NOT_FOUND';
1723 l_price_book_messages_tbl(i).message_text := l_message_text;
1724 l_price_book_messages_tbl(i).pb_input_header_id := p_pb_input_header_id;
1725 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(l_price_book_messages_tbl);
1726 l_price_book_messages_tbl.delete;
1727 commit;
1728 retcode := 2;
1729 errbuf := substr(l_message_text,1,240);
1730 fnd_file.put_line(FND_FILE.LOG, errbuf);
1731 RETURN;
1732 END;
1733
1734 l_user_id := fnd_global.user_id;
1735 l_login_id := fnd_global.login_id;
1736
1737 --dbms_output.put_line('user id = '||l_user_id);
1738 --Multi-Org Init since Conc Program is run in a different session (check this)
1739 IF MO_GLOBAL.get_access_mode is null THEN
1740 MO_GLOBAL.Init('QP');
1741 END IF;
1742
1743 fnd_file.put_line(FND_FILE.LOG, 'Orgs initialized');
1744 fnd_file.put_line(FND_FILE.LOG, 'Price Book Name = '|| l_pb_input_header_rec.price_book_name);
1745 fnd_file.put_line(FND_FILE.LOG, 'Price Book Type = '|| l_pb_input_header_rec.price_book_type_code);
1746 fnd_file.put_line(FND_FILE.LOG, 'Customer = '|| l_pb_input_header_rec.customer_attr_value);
1747
1748 -- SNIMMAGA.
1749 --
1750 -- If not already done, initialize the global variable:
1751 -- G_pb_Processor_Batchsize
1752 --
1753 IF ( G_pb_Processor_Batchsize IS NULL ) THEN
1754 G_pb_Processor_Batchsize :=
1755 QP_PRICE_BOOK_UTIL.Get_Processing_BatchSize;
1756 END IF;
1757
1758 rows := QP_PRICE_BOOK_PVT.G_pb_Processor_Batchsize;
1759
1760 fnd_file.put_line(fnd_file.Log, 'Processing Batch Size: ' ||
1761 qp_price_book_pvt.G_pb_Processor_Batchsize);
1762
1763
1764 IF p_spawned_request = 'N' THEN --Parent request
1765
1766 fnd_file.put_line(FND_FILE.LOG, 'In Parent Concurrent Request');
1767
1768 IF l_pb_input_header_rec.publish_existing_pb_flag = 'Y' THEN
1769 --Publish existing price book
1770 BEGIN
1771 SELECT price_book_header_id
1772 INTO l_publish_price_book_header_id
1773 FROM qp_price_book_headers_vl
1774 WHERE customer_id = l_pb_input_header_rec.customer_attr_value
1775 AND price_book_type_code =
1776 l_pb_input_header_rec.price_book_type_code
1777 AND price_book_name = l_pb_input_header_rec.price_book_name;
1778 EXCEPTION
1779 WHEN OTHERS THEN
1780 FND_MESSAGE.SET_NAME('QP', 'QP_PRICE_BOOK_DOES_NOT_EXIST');
1781 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_NAME',
1782 l_pb_input_header_rec.price_book_name);
1783 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_TYPE_CODE',
1784 l_pb_input_header_rec.price_book_type_code);
1785 l_message_text := FND_MESSAGE.GET;
1786 l_price_book_messages_tbl(i).message_code :=
1787 'QP_PRICE_BOOK_DOES_NOT_EXIST';
1788 l_price_book_messages_tbl(i).message_text := l_message_text;
1789 l_price_book_messages_tbl(i).pb_input_header_id :=
1790 p_pb_input_header_id;
1791 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
1792 l_price_book_messages_tbl);
1793 l_price_book_messages_tbl.delete;
1794 commit;--inserted message with pb_input_header_id
1795 --since price_book_header_id not yet usable.
1796 retcode := 2;
1797 errbuf := substr(l_message_text,1,240);
1798 fnd_file.put_line(FND_FILE.LOG, errbuf);
1799 RETURN;
1800 END;
1801 fnd_file.put_line(FND_FILE.LOG, ' price book header id to publish '||l_publish_price_book_header_id);
1802 ELSIF nvl(l_pb_input_header_rec.publish_existing_pb_flag, 'N') = 'N' THEN
1803 --Generate new price book or existing price book to publish not found
1804
1805 fnd_file.put_line(FND_FILE.LOG, 'Publish_existing_pb_flag = N');
1806
1807 BEGIN
1808 SELECT price_book_header_id, request_id
1809 INTO l_overwrite_pb_header_id, l_old_request_id
1810 FROM qp_price_book_headers_vl
1811 WHERE price_book_name = l_pb_input_header_rec.price_book_name
1812 AND price_book_type_code =
1813 l_pb_input_header_rec.price_book_type_code
1814 AND customer_id = l_pb_input_header_rec.customer_attr_value;
1815 EXCEPTION
1816 WHEN OTHERS THEN
1817 l_overwrite_pb_header_id := NULL;
1818 END;
1819
1820 IF nvl(l_pb_input_header_rec.overwrite_existing_pb_flag, 'N') = 'Y' THEN
1821
1822 IF l_overwrite_pb_header_id IS NOT NULL THEN
1823
1824 --Get concurrent request status for request_id of the price book to be
1825 --overwritten
1826 l_result := fnd_concurrent.get_request_status(
1827 request_id => l_old_request_id, --Input parameter
1828 phase => l_req_phase,
1829 status => l_req_status,
1830 dev_phase => l_req_dev_phase,
1831 dev_status => l_req_dev_status,
1832 message => l_req_message);
1833
1834 IF nvl(l_req_dev_phase, 'COMPLETE') <> 'COMPLETE' THEN
1835 l_price_book_messages_tbl(i).message_code :=
1836 'QP_CONC_REQUEST_IN_PROGRESS';
1837 l_message_text := substr(sqlerrm, 1, 240);
1838 l_price_book_messages_tbl(i).message_text := l_message_text;
1839 l_price_book_messages_tbl(i).pb_input_header_id :=
1840 p_pb_input_header_id;
1841 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
1842 l_price_book_messages_tbl);
1843 l_price_book_messages_tbl.delete;
1844 commit; --inserted message with pb_input_header_id
1845 --since price_book_header_id not yet usable.
1846 retcode := 2;
1847 errbuf := l_message_text;
1848 fnd_file.put_line(FND_FILE.LOG, errbuf);
1849 RETURN;
1850
1851 ELSE --Old Request is Null or Completed
1852 DELETE FROM qp_price_book_headers_b
1853 WHERE price_book_header_id = l_overwrite_pb_header_id
1854 RETURNING pb_input_header_id, document_id
1855 INTO l_old_input_header_id, l_document_id;
1856
1857 DELETE FROM qp_price_book_headers_tl
1858 WHERE price_book_header_id = l_overwrite_pb_header_id;
1859
1860 DELETE FROM qp_price_book_lines
1861 WHERE price_book_header_id = l_overwrite_pb_header_id;
1862
1863 DELETE FROM qp_price_book_line_details
1864 WHERE price_book_header_id = l_overwrite_pb_header_id;
1865
1866 DELETE FROM qp_price_book_attributes
1867 WHERE price_book_header_id = l_overwrite_pb_header_id;
1868
1869 DELETE FROM qp_price_book_break_lines
1870 WHERE price_book_header_id = l_overwrite_pb_header_id;
1871
1872 DELETE FROM qp_price_book_messages
1873 WHERE price_book_header_id = l_overwrite_pb_header_id;
1874
1875 DELETE FROM qp_documents
1876 WHERE document_id = l_document_id;
1877
1878 DELETE FROM qp_price_book_messages
1879 WHERE pb_input_header_id = l_old_input_header_id;
1880
1881 DELETE FROM qp_pb_input_headers_b
1882 WHERE pb_input_header_id = l_old_input_header_id;
1883
1884 DELETE FROM qp_pb_input_headers_tl
1885 WHERE pb_input_header_id = l_old_input_header_id;
1886
1887 DELETE FROM qp_pb_input_lines
1888 WHERE pb_input_header_id = l_old_input_header_id;
1889
1890 --If full price book is to be overwritten, then delete the
1891 --corresponding Delta price book
1892 IF l_pb_input_header_rec.price_book_type_code = 'F' THEN
1893 BEGIN
1894 SELECT price_book_header_id, request_id
1895 INTO l_corr_delta_pb_header_id, l_old_delta_request_id
1896 FROM qp_price_book_headers_vl
1897 WHERE price_book_name = l_pb_input_header_rec.price_book_name
1898 AND price_book_type_code = 'D'
1899 AND customer_id = l_pb_input_header_rec.customer_attr_value;
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 l_corr_delta_pb_header_id := NULL;
1903 END;
1904
1905 IF l_corr_delta_pb_header_id IS NOT NULL THEN
1906
1907 --Get concurrent request status for request_id of the
1908 --corresponding delta price book to be overwritten
1909 l_result := fnd_concurrent.get_request_status(
1910 request_id => l_old_delta_request_id, --Input parameter
1911 phase => l_req_phase,
1912 status => l_req_status,
1913 dev_phase => l_req_dev_phase,
1914 dev_status => l_req_dev_status,
1915 message => l_req_message);
1916
1917 IF nvl(l_req_dev_phase, 'COMPLETE') <> 'COMPLETE' THEN
1918 rollback; --Delete stmts on corresponding Full Price Book data
1919 l_price_book_messages_tbl(i).message_code :=
1920 'QP_CONC_REQUEST_IN_PROGRESS';
1921 l_message_text := substr(sqlerrm, 1, 240);
1922 l_price_book_messages_tbl(i).message_text := l_message_text;
1923 l_price_book_messages_tbl(i).pb_input_header_id :=
1924 p_pb_input_header_id;
1925 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
1926 l_price_book_messages_tbl);
1927 l_price_book_messages_tbl.delete;
1928 commit; --inserted message with pb_input_header_id
1929 --since price_book_header_id not yet usable.
1930 retcode := 2;
1931 errbuf := l_message_text;
1932 fnd_file.put_line(FND_FILE.LOG, errbuf);
1933 RETURN;
1934
1935 ELSE --Old Delta Request is Null or Complete
1936 DELETE FROM qp_price_book_headers_b
1937 WHERE price_book_header_id = l_corr_delta_pb_header_id
1938 RETURNING pb_input_header_id, document_id
1939 INTO l_delta_input_header_id, l_delta_document_id;
1940
1941 DELETE FROM qp_price_book_headers_tl
1942 WHERE price_book_header_id = l_corr_delta_pb_header_id;
1943
1944 DELETE FROM qp_price_book_lines
1945 WHERE price_book_header_id = l_corr_delta_pb_header_id;
1946
1947 DELETE FROM qp_price_book_line_details
1948 WHERE price_book_header_id = l_corr_delta_pb_header_id;
1949
1950 DELETE FROM qp_price_book_attributes
1951 WHERE price_book_header_id = l_corr_delta_pb_header_id;
1952
1953 DELETE FROM qp_price_book_break_lines
1954 WHERE price_book_header_id = l_corr_delta_pb_header_id;
1955
1956 DELETE FROM qp_price_book_messages
1957 WHERE price_book_header_id = l_corr_delta_pb_header_id;
1958
1959 DELETE FROM qp_documents
1960 WHERE document_id = l_delta_document_id;
1961
1962 DELETE FROM qp_price_book_messages
1963 WHERE pb_input_header_id = l_delta_input_header_id;
1964
1965 DELETE FROM qp_pb_input_headers_b
1966 WHERE pb_input_header_id = l_delta_input_header_id;
1967
1968 DELETE FROM qp_pb_input_headers_tl
1969 WHERE pb_input_header_id = l_delta_input_header_id;
1970
1971 DELETE FROM qp_pb_input_lines
1972 WHERE pb_input_header_id = l_delta_input_header_id;
1973
1974 END IF; --If Old Delta Request is Not Null or Complete
1975
1976 END IF; --If l_corr_delta_pb_header_id is not null
1977
1978 END IF; --If full price book is being overwritten
1979
1980 END IF; --If Old request is not Null or Completed
1981
1982 END IF; --If l_overwrite_pb_header_id is not null
1983
1984 fnd_file.put_line(FND_FILE.LOG, 'Before inserting price book header');
1985
1986 --Create Price Book Header
1987 BEGIN
1988 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Header(
1989 p_pb_input_header_rec => l_pb_input_header_rec,
1990 x_price_book_header_id => l_publish_price_book_header_id);
1991
1992 fnd_file.put_line(FND_FILE.LOG, 'After inserting price book header'||
1993 to_char(l_publish_price_book_header_id));
1994 EXCEPTION
1995 WHEN FND_API.G_EXC_ERROR THEN
1996 FND_MESSAGE.SET_NAME('QP', 'QP_PB_EXISTS_IN_ANOTHER_ORG');
1997 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_NAME',
1998 l_pb_input_header_rec.price_book_name);
1999 l_message_text := FND_MESSAGE.GET;
2000 l_price_book_messages_tbl(i).message_code :=
2001 'QP_PB_EXISTS_IN_ANOTHER_ORG';
2002 l_price_book_messages_tbl(i).message_text := l_message_text;
2003 l_price_book_messages_tbl(i).pb_input_header_id :=
2004 p_pb_input_header_id;
2005 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
2006 l_price_book_messages_tbl);
2007 l_price_book_messages_tbl.delete;
2008 commit; --inserted message with pb_input_header_id
2009 --since price_book_header_id not yet usable.
2010 retcode := 2;
2011 errbuf := l_message_text;
2012 fnd_file.put_line(FND_FILE.LOG, errbuf);
2013 RETURN;
2014
2015 WHEN OTHERS THEN
2016 l_price_book_messages_tbl(i).message_code :=
2017 'INSERT_PRICE_BOOK_HEADER_ERROR';
2018 l_message_text := substr(sqlerrm, 1, 240);
2019 l_price_book_messages_tbl(i).message_text := l_message_text;
2020 l_price_book_messages_tbl(i).pb_input_header_id :=
2021 p_pb_input_header_id;
2022 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
2023 l_price_book_messages_tbl);
2024 l_price_book_messages_tbl.delete;
2025 commit; --inserted message with pb_input_header_id
2026 --since price_book_header_id not yet usable.
2027 retcode := 2;
2028 errbuf := l_message_text;
2029 fnd_file.put_line(FND_FILE.LOG, errbuf);
2030 RETURN;
2031 END;
2032
2033 --dbms_output.put_line('Conc Program price book header id = '||l_publish_price_book_header_id);
2034 ELSE --If overwrite_flag = 'N'
2035
2036 IF l_overwrite_pb_header_id IS NULL THEN
2037
2038 BEGIN
2039 --Create Price Book Header
2040 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Header(
2041 p_pb_input_header_rec => l_pb_input_header_rec,
2042 x_price_book_header_id => l_publish_price_book_header_id);
2043 EXCEPTION
2044 WHEN FND_API.G_EXC_ERROR THEN
2045 FND_MESSAGE.SET_NAME('QP', 'QP_PB_EXISTS_IN_ANOTHER_ORG');
2046 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_NAME',
2047 l_pb_input_header_rec.price_book_name);
2048 l_message_text := FND_MESSAGE.GET;
2049 l_price_book_messages_tbl(i).message_code :=
2050 'QP_PB_EXISTS_IN_ANOTHER_ORG';
2051 l_price_book_messages_tbl(i).message_text := l_message_text;
2052 l_price_book_messages_tbl(i).pb_input_header_id :=
2053 p_pb_input_header_id;
2054 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
2055 l_price_book_messages_tbl);
2056 l_price_book_messages_tbl.delete;
2057 commit; --inserted message with pb_input_header_id
2058 --since price_book_header_id not yet usable.
2059 retcode := 2;
2060 errbuf := l_message_text;
2061 fnd_file.put_line(FND_FILE.LOG, errbuf);
2062 RETURN;
2063
2064 WHEN OTHERS THEN
2065 l_price_book_messages_tbl(i).message_code :=
2066 'INSERT_PRICE_BOOK_HEADER_ERROR';
2067 l_message_text := substr(sqlerrm, 1, 240);
2068 l_price_book_messages_tbl(i).message_text := l_message_text;
2069 l_price_book_messages_tbl(i).pb_input_header_id :=
2070 p_pb_input_header_id;
2071 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
2072 l_price_book_messages_tbl);
2073 l_price_book_messages_tbl.delete;
2074 commit; --inserted message with pb_input_header_id
2075 --since price_book_header_id not yet usable.
2076 retcode := 2;
2077 errbuf := l_message_text;
2078 fnd_file.put_line(FND_FILE.LOG, errbuf);
2079 RETURN;
2080 END;
2081
2082 ELSE
2083 FND_MESSAGE.SET_NAME('QP', 'QP_PRICE_BOOK_ALREADY_EXISTS');
2084 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_NAME',
2085 l_pb_input_header_rec.price_book_name);
2086 FND_MESSAGE.SET_TOKEN('PRICE_BOOK_TYPE_CODE',
2087 l_pb_input_header_rec.price_book_type_code);
2088 l_message_text := FND_MESSAGE.GET;
2089 l_price_book_messages_tbl(i).message_code :=
2090 'QP_PRICE_BOOK_ALREADY_EXISTS';
2091 l_price_book_messages_tbl(i).message_text := l_message_text;
2092 l_price_book_messages_tbl(i).pb_input_header_id :=
2093 p_pb_input_header_id;
2094 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
2095 l_price_book_messages_tbl);
2096 l_price_book_messages_tbl.delete;
2097 commit; --inserted message with pb_input_header_id
2098 --since price_book_header_id not yet usable.
2099 retcode := 2;
2100 errbuf := substr(l_message_text,1,240);
2101 fnd_file.put_line(FND_FILE.LOG, errbuf);
2102 RETURN;
2103 END IF; --overwrite_price_book_header_id is null
2104
2105 END IF;--If overwrite flag = 'Y'
2106
2107 --Get Item-Uom combinations and create preliminary price book lines
2108 --that will be updated by child process with information obtained from
2109 --pricing engine
2110 l_item_validation_org_id := QP_UTIL.Get_Item_Validation_Org;
2111
2112 fnd_file.put_line(FND_FILE.LOG, 'Item Validation Org = ' ||
2113 to_char(l_item_validation_org_id));
2114 --dbms_output.put_line('l_item_validation_org_id = '||l_item_validation_org_id);
2115 IF l_pb_input_header_rec.limit_products_by = 'PRICE_LIST' THEN
2116
2117 fnd_file.put_line(fnd_file.Log, 'limit products by = PRICE_LIST ');
2118 fnd_file.put_line(fnd_file.Log, 'pl_agr_bsa_id = '||l_pb_input_header_rec.pl_agr_bsa_id);
2119
2120 --For items in price list
2121 OPEN pl_items_cur(l_pb_input_header_rec.pl_agr_bsa_id);
2122 LOOP
2123 l_item_tbl.delete;
2124
2125 FETCH pl_items_cur BULK COLLECT INTO l_item_tbl LIMIT rows;
2126
2127 IF l_item_tbl.COUNT > 0 THEN
2128 --dbms_output.put_line(' item tbl count '||l_item_tbl.count);
2129 FOR i IN l_item_tbl.FIRST..l_item_tbl.LAST
2130 LOOP
2131 --get item-uom combinations and insert prelim pricebook lines
2132 OPEN item_uom_cur(l_item_tbl(i), l_item_validation_org_id,
2133 l_pb_input_header_rec.effective_date);
2134 LOOP
2135 l_item2_tbl.delete;
2136 l_uom_tbl.delete;
2137
2138 FETCH item_uom_cur BULK COLLECT INTO l_item2_tbl, l_uom_tbl
2139 LIMIT rows;
2140
2141 --dbms_output.put_line(' item2 tbl count '||l_item2_tbl.count);
2142 FORALL j IN l_item2_tbl.FIRST..l_item2_tbl.LAST
2143 INSERT INTO qp_price_book_lines
2144 (price_book_line_id,
2145 price_book_header_id,
2146 item_number,
2147 product_uom_code,
2148 sync_action_code,
2149 creation_date,
2150 created_by,
2151 last_update_date,
2152 last_updated_by,
2153 last_update_login
2154 )
2155 VALUES
2156 (qp_price_book_lines_s.nextval,
2157 l_publish_price_book_header_id,
2158 l_item2_tbl(j),
2159 l_uom_tbl(j),
2160 'R',
2161 sysdate,
2162 l_user_id,
2163 sysdate,
2164 l_user_id,
2165 l_login_id
2166 );
2167
2168 EXIT WHEN item_uom_cur%NOTFOUND;
2169
2170 END LOOP; --Loop over item_uom_cur
2171 CLOSE item_uom_cur;
2172
2173 END LOOP; --For loop over l_item_tbl
2174 END IF; --If l_item_tbl.count > 0
2175
2176 EXIT WHEN pl_items_cur%NOTFOUND;
2177
2178 END LOOP; --loop over pl_items_cur
2179 CLOSE pl_items_cur;
2180
2181
2182 ELSIF l_pb_input_header_rec.limit_products_by = 'ITEM' THEN
2183
2184 --get item and uom combinations for item and insert prelim price book lines
2185 fnd_file.put_line(fnd_file.Log, 'limit_products_by = ITEM');
2186
2187 OPEN item_uom_cur(l_pb_input_header_rec.product_attr_value,
2188 l_item_validation_org_id,
2189 l_pb_input_header_rec.effective_date);
2190 LOOP
2191 l_item2_tbl.delete;
2192 l_uom_tbl.delete;
2193
2194 FETCH item_uom_cur BULK COLLECT INTO l_item2_tbl, l_uom_tbl
2195 LIMIT rows;
2196
2197 FORALL j IN l_item2_tbl.FIRST..l_item2_tbl.LAST
2198 INSERT INTO qp_price_book_lines
2199 (price_book_line_id,
2200 price_book_header_id,
2201 item_number,
2202 product_uom_code,
2203 sync_action_code,
2204 creation_date,
2205 created_by,
2206 last_update_date,
2207 last_updated_by,
2208 last_update_login
2209 )
2210 VALUES
2211 (qp_price_book_lines_s.nextval,
2212 l_publish_price_book_header_id,
2213 l_item2_tbl(j),
2214 l_uom_tbl(j),
2215 'R',
2216 sysdate,
2217 l_user_id,
2218 sysdate,
2219 l_user_id,
2220 l_login_id
2221 );
2222
2223 EXIT WHEN item_uom_cur%NOTFOUND;
2224
2225 END LOOP; --Loop over item_uom_cur
2226 CLOSE item_uom_cur;
2227
2228
2229 ELSIF l_pb_input_header_rec.limit_products_by = 'ITEM_CATEGORY' THEN
2230
2231 --For items in category
2232 fnd_file.put_line(fnd_file.Log, 'limit_products_by = ITEM_CATEGORY');
2233
2234 OPEN cat_items_cur(l_pb_input_header_rec.product_attr_value,
2235 l_item_validation_org_id);
2236 LOOP
2237 l_item_tbl.delete;
2238 FETCH cat_items_cur BULK COLLECT INTO l_item_tbl LIMIT rows;
2239
2240 IF l_item_tbl.COUNT > 0 THEN
2241 FOR i IN l_item_tbl.FIRST..l_item_tbl.LAST
2242 LOOP
2243 --get item and uom combinations and insert prelim pricebook lines
2244 OPEN item_uom_cur(l_item_tbl(i),
2245 l_item_validation_org_id,
2246 l_pb_input_header_rec.effective_date);
2247 LOOP
2248 l_item2_tbl.delete;
2249 l_uom_tbl.delete;
2250
2251 FETCH item_uom_cur BULK COLLECT INTO l_item2_tbl, l_uom_tbl
2252 LIMIT rows;
2253
2254 FORALL j IN l_item2_tbl.FIRST..l_item2_tbl.LAST
2255 INSERT INTO qp_price_book_lines
2256 (price_book_line_id,
2257 price_book_header_id,
2258 item_number,
2259 product_uom_code,
2260 sync_action_code,
2261 creation_date,
2262 created_by,
2263 last_update_date,
2264 last_updated_by,
2265 last_update_login
2266 )
2267 VALUES
2268 (qp_price_book_lines_s.nextval,
2269 l_publish_price_book_header_id,
2270 l_item2_tbl(j),
2271 l_uom_tbl(j),
2272 'R',
2273 sysdate,
2274 l_user_id,
2275 sysdate,
2276 l_user_id,
2277 l_login_id
2278 );
2279
2280 EXIT WHEN item_uom_cur%NOTFOUND;
2281
2282 END LOOP; --Loop over item_uom_cur
2283 CLOSE item_uom_cur;
2284
2285 END LOOP; --For loop over l_item_tbl
2286 END IF; --If l_item_tbl.count > 0
2287
2288 EXIT WHEN cat_items_cur%NOTFOUND;
2289
2290 END LOOP; --loop over cat_items_cur
2291 CLOSE cat_items_cur;
2292
2293
2294 ELSIF l_pb_input_header_rec.limit_products_by = 'ALL_ITEMS' THEN
2295
2296 --get all item-uom combinations and create price book lines
2297 fnd_file.put_line(fnd_file.Log, 'limit_products_by = ALL_ITEMS');
2298
2299 OPEN items_cur(l_item_validation_org_id,
2300 l_pb_input_header_rec.pricing_perspective_code);
2301 LOOP
2302 l_item_tbl.delete;
2303 FETCH items_cur BULK COLLECT INTO l_item_tbl LIMIT rows;
2304
2305 IF l_item_tbl.COUNT > 0 THEN
2306 FOR i IN l_item_tbl.FIRST..l_item_tbl.LAST
2307 LOOP
2308 --get item-uom combinations and insert prelim pricebook lines
2309 OPEN item_uom_cur(l_item_tbl(i),
2310 l_item_validation_org_id,
2311 l_pb_input_header_rec.effective_date);
2312 LOOP
2313 l_item2_tbl.delete;
2314 l_uom_tbl.delete;
2315
2316 FETCH item_uom_cur BULK COLLECT INTO l_item2_tbl, l_uom_tbl
2317 LIMIT rows;
2318
2319 FORALL j IN l_item2_tbl.FIRST..l_item2_tbl.LAST
2320 INSERT INTO qp_price_book_lines
2321 (price_book_line_id,
2322 price_book_header_id,
2323 item_number,
2324 product_uom_code,
2325 sync_action_code,
2326 creation_date,
2327 created_by,
2328 last_update_date,
2329 last_updated_by,
2330 last_update_login
2331 )
2332 VALUES
2333 (qp_price_book_lines_s.nextval,
2334 l_publish_price_book_header_id,
2335 l_item2_tbl(j),
2336 l_uom_tbl(j),
2337 'R',
2338 sysdate,
2339 l_user_id,
2340 sysdate,
2341 l_user_id,
2342 l_login_id
2343 );
2344
2345 EXIT WHEN item_uom_cur%NOTFOUND;
2346
2347 END LOOP; --Loop over item_uom_cur
2348 CLOSE item_uom_cur;
2349
2350 END LOOP; --For loop over l_item_tbl
2351 END IF; --if l_item_tbl.count > 0
2352
2353 EXIT WHEN items_cur%NOTFOUND;
2354
2355 END LOOP; --loop over items_cur
2356 CLOSE items_cur;
2357
2358 END IF; --If limit_products_by is price_list, item or item_category
2359
2360 END IF; --Publish existing price book
2361
2362 fnd_file.put_line(FND_FILE.LOG, 'Preparing to submit child request');
2363
2364 --dbms_output.put_line('Came to end');--test code to be removed
2365 --commit; --test code to be removed
2366
2367 IF l_pb_input_header_rec.generation_time_code = 'SCHEDULE' THEN
2368 l_submit :=
2369 fnd_date.date_to_canonical(l_pb_input_header_rec.gen_schedule_date);
2370 END IF;
2371
2372 fnd_file.put_line(FND_FILE.LOG, 'Before submitting child request');
2373
2374 --submit a child concurrent request
2375
2376 l_child_request_id := FND_REQUEST.SUBMIT_REQUEST(
2377 'QP', 'QPXPRBKB', 'Price Book Generate and Publish', l_submit, FALSE,
2378 l_pb_input_header_rec.pb_input_header_id,
2379 l_pb_input_header_rec.customer_attr_value,
2380 l_publish_price_book_header_id,
2381 'Y');
2382
2383 --dbms_output.put_line('child request id = '||l_child_request_id);
2384
2385 IF l_child_request_id = 0 THEN
2386 errbuf := substr(FND_MESSAGE.GET, 1, 240);
2387 retcode := 2;
2388 fnd_file.put_line(FND_FILE.LOG, errbuf);
2389 ELSE --submit request successful
2390 errbuf := '';
2391 retcode := 0;
2392
2393 UPDATE qp_price_book_headers_b
2394 SET request_id = l_child_request_id
2395 WHERE price_book_header_id = l_publish_price_book_header_id;
2396
2397 COMMIT; --to complete request submission
2398
2399 --dbms_output.put_line('after submitting child request ');
2400 fnd_file.put_line(FND_FILE.LOG, 'After submitting child request');
2401
2402 END IF; --If child_request_id = 0
2403
2404 ELSE --p_spawned_request = 'Y'
2405
2406 fnd_file.put_line(FND_FILE.LOG, 'In child request');
2407
2408 --generate engine debug file if the 'QP: Debug' profile is on
2409 IF FND_PROFILE.VALUE_SPECIFIC(name => 'QP_DEBUG', application_id => 661)
2410 IN ('Y','V')
2411 THEN
2412 oe_debug_pub.SetDebugLevel(10);
2413 oe_debug_pub.Initialize;
2414 oe_debug_pub.debug_on;
2415 l_debug_file := oe_debug_pub.Set_Debug_Mode('FILE');
2416 fnd_file.put_line(FND_FILE.LOG, 'Engine Debug File = '||l_debug_file);
2417 END IF;
2418
2419 IF nvl(l_pb_input_header_rec.publish_existing_pb_flag, 'N') = 'N' THEN
2420 --Price Book is to be generated
2421
2422 SELECT * BULK COLLECT
2423 INTO l_pb_input_lines_tbl
2424 FROM qp_pb_input_lines
2425 WHERE pb_input_header_id = p_pb_input_header_id;
2426
2427 fnd_file.put_line(FND_FILE.LOG, 'Before Insert_Price_Book_Content ');
2428
2429 --Create price book line details, pricing attributes, price break lines
2430 BEGIN
2431 Insert_Price_Book_Content(
2432 p_pb_input_header_rec => l_pb_input_header_rec,
2433 p_pb_input_lines_tbl => l_pb_input_lines_tbl,
2434 p_price_book_header_id => p_price_book_header_id);
2435 EXCEPTION
2436 WHEN FND_API.G_EXC_ERROR THEN
2437 l_price_book_messages_tbl(i).message_code :=
2438 'QP_NO_PB_LINES_TO_PRICE';
2439 FND_MESSAGE.SET_NAME('FND', 'QP_NO_PB_LINES_TO_PRICE');
2440 l_message_text := FND_MESSAGE.GET;
2441 l_price_book_messages_tbl(i).message_text := l_message_text;
2442 l_price_book_messages_tbl(i).pb_input_header_id :=
2443 p_pb_input_header_id;
2444 l_price_book_messages_tbl(i).price_book_header_id :=
2445 p_price_book_header_id;
2446 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
2447 l_price_book_messages_tbl);
2448 l_price_book_messages_tbl.delete;
2449 commit;
2450 retcode := 0;
2451 RETURN;
2452 END;
2453
2454 fnd_file.put_line(FND_FILE.LOG, 'After Insert_Price_Book_Content ');
2455
2456 --Update price book line details with running net price
2457 OPEN pb_lines_cur;
2458 LOOP
2459 l_line_id_tbl.delete;
2460 l_list_price_tbl.delete;
2461 l_line_det_id_tbl2.delete;
2462 l_net_price_tbl.delete;
2463 l_line_id_tbl2.delete;
2464 m := 1;
2465
2466 FETCH pb_lines_cur BULK COLLECT INTO l_line_id_tbl, l_list_price_tbl;
2467
2468 IF l_line_id_tbl.COUNT > 0 THEN
2469 FOR i IN l_line_id_tbl.FIRST..l_line_id_tbl.LAST
2470 LOOP
2471 l_line_det_id_tbl.delete;
2472 l_adjustment_amount_tbl.delete;
2473
2474 --For each pricebook line set the starting net_price to list_price
2475 l_net_price := nvl(l_list_price_tbl(i), 0);
2476
2477 SELECT /*+ index(qpbdtls qp_price_book_line_details_n1) */
2478 qpbdtls.price_book_line_det_id, qpbdtls.adjustment_amount --bug 8933586
2479 BULK COLLECT INTO l_line_det_id_tbl, l_adjustment_amount_tbl
2480 FROM qp_price_book_line_details qpbdtls
2481 WHERE qpbdtls.price_book_line_id = l_line_id_tbl(i)
2482 ORDER BY qpbdtls.price_book_line_det_id;
2483
2484 IF l_line_det_id_tbl.COUNT > 0 THEN
2485 --For each pricebook line det id, calculate cumulative net price
2486 FOR j IN l_line_det_id_tbl.FIRST..l_line_det_id_tbl.LAST
2487 LOOP
2488 l_net_price := l_net_price + nvl(l_adjustment_amount_tbl(j),0);
2489 --copy the line det plsql tables to another set of plsql
2490 --tables which will hold all line details for all lines in
2491 --current iteration of pb_lines_cur
2492 l_line_det_id_tbl2(m) := l_line_det_id_tbl(j);
2493 l_net_price_tbl(m) := l_net_price;
2494 l_line_id_tbl2(m) := l_line_id_tbl(i);
2495 m := m + 1; --increment m
2496 END LOOP; --Loop over l_line_det_id_tbl
2497 END IF; --If l_line_det_id_tbl.count > 0
2498
2499 END LOOP; --Loop over l_line_id_tbl
2500 END IF;
2501
2502 --Bulk update price book line details with cumulative net prices
2503 --for all line details belonging to all lines in current iteration of
2504 --pb_lines_cur
2505 FORALL m IN l_line_det_id_tbl2.FIRST..l_line_det_id_tbl2.LAST
2506 UPDATE qp_price_book_line_details
2507 SET adjusted_net_price = l_net_price_tbl(m)
2508 WHERE price_book_line_det_id = l_line_det_id_tbl2(m);
2509
2510 --Bulk update the net price on the summary price book line to the
2511 --the calculated running net price. This is to ensure that the summary
2512 --net price includes any freight and special charges.
2513 FORALL m IN l_line_id_tbl2.FIRST..l_line_id_tbl2.LAST
2514 UPDATE qp_price_book_lines
2515 SET net_price = l_net_price_tbl(m)
2516 WHERE price_book_line_id = l_line_id_tbl2(m);
2517
2518 EXIT WHEN pb_lines_cur%NOTFOUND;
2519
2520 END LOOP; --Loop over pb_lines_cur
2521
2522
2523 --Create Delta price book
2524 IF l_pb_input_header_rec.price_book_type_code = 'D' THEN
2525 BEGIN
2526 Create_Delta_Price_Book(p_price_book_header_id,
2527 l_pb_input_header_rec.price_book_name,
2528 l_pb_input_header_rec.customer_attr_value);
2529 EXCEPTION
2530 WHEN OTHERS THEN
2531 l_price_book_messages_tbl(i).message_code :=
2532 'CREATE_DELTA_PRICE_BOOK_ERROR';
2533 l_message_text := substr(sqlerrm, 1, 240);
2534 l_price_book_messages_tbl(i).message_text := l_message_text;
2535 l_price_book_messages_tbl(i).pb_input_header_id :=
2536 p_pb_input_header_id;
2537 l_price_book_messages_tbl(i).price_book_header_id :=
2538 p_price_book_header_id;
2539 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(
2540 l_price_book_messages_tbl);
2541 l_price_book_messages_tbl.delete;
2542 commit;
2543 retcode := 2;
2544 errbuf := l_message_text;
2545 fnd_file.put_line(FND_FILE.LOG, errbuf);
2546 RETURN;
2547 END;
2548 END IF;
2549
2550 ELSE --publish_existing_pb_flag = 'Y'
2551
2552 --Delete publish-related error messages for the previous publish request
2553 DELETE FROM qp_price_book_messages
2554 WHERE price_book_header_id = p_price_book_header_id
2555 AND message_code like 'PUB_%';
2556
2557 --Republish request has null template-code
2558 IF l_pb_input_header_rec.pub_template_code IS NULL THEN
2559
2560 BEGIN
2561 --select the previous document_id if it exists on the price book
2562 SELECT document_id
2563 INTO l_document_id
2564 FROM qp_price_book_headers_all_b
2565 WHERE price_book_header_id = p_price_book_header_id;
2566 EXCEPTION
2567 WHEN OTHERS THEN
2568 l_document_id := null;
2569 END;
2570
2571 IF l_document_id IS NOT NULL THEN
2572 UPDATE qp_price_book_headers_all_b
2573 SET document_id = null
2574 WHERE price_book_header_id = p_price_book_header_id;
2575
2576 DELETE FROM qp_documents
2577 WHERE document_id = l_document_id;
2578 END IF;
2579
2580 END IF; --template-code is null
2581
2582 END IF; --publish_existing_pb_flag = 'N', i.e. price book is to be generated
2583
2584 --Publish the price book identified by p_price_book_header_id
2585 fnd_file.put_line(FND_FILE.LOG,'Begin Publishing');
2586 /** KDURGASI **/
2587 SELECT PUB_OUTPUT_DOCUMENT_TYPE
2588 INTO l_document_type
2589 FROM QP_PB_INPUT_HEADERS_B
2590 WHERE pb_input_header_id = p_pb_input_header_id;
2591
2592 QP_PRICE_BOOK_UTIL.GENERATE_PRICE_BOOK_XML
2593 (
2594 p_price_book_header_id,
2595 QP_PRICE_BOOK_UTIL.get_content_type(l_document_type),
2596 QP_PRICE_BOOK_UTIL.get_document_name(p_pb_input_header_id,l_document_type),
2597 l_return_status,
2598 l_return_status_text
2599 );
2600
2601 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2603 END IF;
2604
2605 /** KDURGASI **/
2606 --If a template has been specified
2607 IF l_pb_input_header_rec.pub_template_code IS NOT NULL
2608 THEN
2609
2610 QP_PRICE_BOOK_UTIL.Publish_and_Deliver(
2611 p_pb_input_header_id => p_pb_input_header_id,
2612 p_price_book_header_id => p_price_book_header_id,
2613 x_return_status => l_return_status,
2614 x_return_status_text => l_return_status_text);
2615
2616 fnd_file.put_line(FND_FILE.LOG, 'Publish_and_Deliver return status '||l_return_status);
2617 fnd_file.put_line(FND_FILE.LOG, 'Publish_and_Deliver return text'||l_return_status_text);
2618
2619 END IF;
2620
2621 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2622 retcode := 1;
2623 errbuf := l_return_status_text;
2624 END IF;
2625
2626 --If XML Message flag checked
2627 IF l_pb_input_header_rec.dlv_xml_flag = 'Y' THEN
2628 QP_PRICE_BOOK_UTIL.Send_Sync_Catalog(
2629 p_price_book_header_id => p_price_book_header_id,
2630 x_return_status => l_return_status,
2631 x_return_status_text => l_return_status_text);
2632 fnd_file.put_line(FND_FILE.LOG, 'XML Message return status '||l_return_status);
2633 fnd_file.put_line(FND_FILE.LOG, 'XML Message return text'||l_return_status_text);
2634
2635 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2636 l_price_book_messages_tbl(i).message_code := 'SYNC_CATALOG_ERROR';
2637 l_price_book_messages_tbl(i).message_text := l_return_status_text;
2638 l_price_book_messages_tbl(i).pb_input_header_id := p_pb_input_header_id;
2639 l_price_book_messages_tbl(i).price_book_header_id := p_price_book_header_id;
2640 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(l_price_book_messages_tbl);
2641 l_price_book_messages_tbl.delete;
2642 commit;
2643 retcode := 1;
2644 errbuf := l_return_status_text;
2645 i := i + 1;
2646 END IF;
2647
2648 END IF; --If xml flag checked
2649
2650 fnd_file.put_line(FND_FILE.LOG,'Done Publishing');
2651
2652 IF FND_PROFILE.VALUE_SPECIFIC(name => 'QP_DEBUG', application_id => 661)
2653 IN ('Y','V')
2654 THEN
2655 oe_debug_pub.debug_off;
2656 END IF;
2657
2658 END IF; -- p_spawned_request = 'N'
2659
2660 COMMIT;
2661
2662 -- Introduced for the purpose of PL/SQL Profiling (snimmaga)
2663 /*
2664 fnd_file.put_line(fnd_file.Log, 'Stopping PL/SQL Profiler...');
2665 err := DBMS_PROFILER.STOP_PROFILER ;
2666 */
2667
2668 EXCEPTION
2669 WHEN OTHERS THEN
2670 l_message_text := substr(sqlerrm, 1, 240);
2671 l_price_book_messages_tbl(i).message_code := 'PRICE_BOOK_CONC_PGM_ERROR';
2672 l_price_book_messages_tbl(i).message_text := l_message_text;
2673 l_price_book_messages_tbl(i).pb_input_header_id :=
2674 l_pb_input_header_rec.pb_input_header_id;
2675 l_price_book_messages_tbl(i).price_book_header_id := p_price_book_header_id;
2676 QP_PRICE_BOOK_UTIL.Insert_Price_Book_Messages(l_price_book_messages_tbl);
2677 l_price_book_messages_tbl.delete;
2678 commit;
2679 retcode := 2;
2680 errbuf := l_message_text;
2681 fnd_file.put_line(FND_FILE.LOG, errbuf);
2682 END Price_Book_Conc_Pgm;
2683
2684
2685 END qp_price_book_pvt;