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