[Home] [Help]
PACKAGE BODY: APPS.PO_PRICE_ADJUSTMENTS_PKG
Source
1 PACKAGE BODY PO_PRICE_ADJUSTMENTS_PKG AS
2 -- $Header: PO_PRICE_ADJUSTMENTS_PKG.plb 120.0.12010000.4 2009/07/01 01:34:47 ababujan noship $
3
4 ---------------------------------------------------------------------------
5 -- Modules for debugging.
6 ---------------------------------------------------------------------------
7
8 -- The module base for this package.
9 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
10 PO_LOG.get_package_base('PO_PRICE_ADJUSTMENTS_PKG');
11
12 -- The module base for the subprogram.
13 D_popl_manual_overridden_adj CONSTANT VARCHAR2(100) :=
14 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'popl_manual_overridden_adj');
15
16 -- The module base for the subprogram.
17 D_extract_price_adjustments CONSTANT VARCHAR2(100) :=
18 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'extract_price_adjustments');
19
20 -- The module base for the subprogram.
21 D_complete_manual_mod_lov_map CONSTANT VARCHAR2(100) :=
22 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'complete_manual_mod_lov_map');
23
24 -- The module base for the subprogram.
25 D_copy_line_adjustments CONSTANT VARCHAR2(100) :=
26 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'copy_line_adjustments');
27
28 -- The module base for the subprogram.
29 D_delete_price_adjustments CONSTANT VARCHAR2(100) :=
30 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'delete_price_adjustments');
31
32 -- The module base for the subprogram.
33 D_line_modifier_exist CONSTANT VARCHAR2(100) :=
34 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'line_modifier_exist');
35
36 -- The module base for the subprogram.
37 D_check_man_ovr_mod_exist CONSTANT VARCHAR2(100) :=
38 PO_LOG.get_subprogram_base(D_PACKAGE_BASE, 'check_man_ovr_mod_exist');
39
40 ---------------------------------------------------------------------------
41 -- Global Constants.
42 ---------------------------------------------------------------------------
43
44 -- Private package constants
45 g_pkg_name CONSTANT varchar2(30) := 'PO_PRICE_ADJUSTMENTS_PKG';
46 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
47
48 -- Debugging
49 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
50 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
51
52
53 --------------------------------------------------------------------------------
54 -- Forward procedure declarations.
55 --------------------------------------------------------------------------------
56
57 PROCEDURE delete_line_adjs
58 (p_draft_id IN NUMBER
59 ,p_order_header_id IN NUMBER
60 ,p_order_line_id_tbl IN QP_PREQ_GRP.NUMBER_TYPE
61 ,p_pricing_events IN VARCHAR2
62 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
63 ,p_pricing_call_src IN VARCHAR2
64 --To fix price override not allowed error
65 ,p_allow_price_override_flag IN VARCHAR2
66 ,p_log_head IN VARCHAR2
67 );
68
69 PROCEDURE update_adj
70 (p_draft_id IN NUMBER
71 ,p_price_adjustment_id IN NUMBER
72 ,p_line_detail_index IN NUMBER
73 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
74 ,p_pricing_call_src IN VARCHAR2
75 --To fix price override not allowed error
76 ,p_allow_price_override_flag IN VARCHAR2
77 ,px_debug_upd_adj_tbl OUT NOCOPY NUMBER_TYPE
78 ,p_log_head IN VARCHAR2
79 );
80
81 PROCEDURE insert_adj
82 (p_draft_id IN NUMBER
83 ,p_order_header_id IN NUMBER
84 ,p_doc_sub_type IN VARCHAR2
85 ,p_log_head IN VARCHAR2
86 );
87
88 PROCEDURE update_adj_attribs
89 (p_draft_id IN NUMBER
90 ,p_order_header_id IN NUMBER
91 ,p_pricing_events IN VARCHAR2
92 ,p_log_head IN VARCHAR2
93 );
94
95 PROCEDURE insert_adj_attribs
96 (p_draft_id IN NUMBER
97 ,p_order_header_id IN NUMBER
98 ,p_log_head IN VARCHAR2
99 );
100
101 PROCEDURE insert_adj_rec
102 (p_adj_rec IN PO_PRICE_ADJUSTMENTS%ROWTYPE);
103
104 PROCEDURE insert_draft_adj_rec
105 (p_draft_id IN NUMBER
106 ,p_adj_rec IN PO_PRICE_ADJUSTMENTS_V%ROWTYPE);
107
108 --------------------------------------------------------------------------------
109 -- Procedure definitions
110 --------------------------------------------------------------------------------
111
112 --------------------------------------------------------------------------------
113 --Start of Comments
114 --Name: get_applied_modifier_code
115 --Pre-reqs:
116 -- None.
117 --Modifies:
118 -- None.
119 --Locks:
120 -- None.
121 --Function:
122 -- This procedure checks if any modifier got applied for the given PO Header ID and Line Id
123 -- modifier details
124 --Parameters:
125 --IN:
126 --p_po_header_id
127 -- Identifies the header that should be checked for applied modifiers
128 --p_po_line_id
129 -- Identifies the line that should be checked for applied modifiers
130 --OUT:
131 --x_modifier_code
132 -- Returns the type of modifiers that are applied for the line
133 --Testing:
134 --
135 -- Exceptions:
136 --
137 --End of Comments
138 -------------------------------------------------------------------------------
139 PROCEDURE get_applied_modifier_code
140 (
141 p_po_header_id IN NUMBER
142 , p_po_line_id IN NUMBER
143 , x_modifier_code OUT NOCOPY VARCHAR2
144 )
145 IS
146 l_man_mod_count NUMBER;
147 l_man_ovr_mod_count NUMBER;
148 l_auto_mod_count NUMBER;
149 l_auto_ovr_mod_count NUMBER;
150 l_modifier_code VARCHAR2(100);
151 BEGIN
152 --Check if manual modifiers are applied
153 SELECT COUNT(1)
154 INTO l_man_mod_count
155 FROM PO_PRICE_ADJUSTMENTS_V ADJV
156 WHERE ADJV.po_header_id = p_po_header_id
157 AND ADJV.po_line_id = p_po_line_id
158 AND ADJV.automatic_flag = 'N'
159 --AND ADJ.update_allowed = 'N'
160 AND NVL(ADJV.updated_flag,'N') = 'N';
161
162 --Check if manual overridden modifiers are applied
163 SELECT COUNT(1)
164 INTO l_man_ovr_mod_count
165 FROM PO_PRICE_ADJUSTMENTS_V ADJV
166 WHERE ADJV.po_header_id = p_po_header_id
167 AND ADJV.po_line_id = p_po_line_id
168 AND ADJV.automatic_flag = 'N'
169 AND ADJV.update_allowed = 'Y'
170 AND ADJV.updated_flag = 'Y';
171
172 --Check if automatic modifiers are applied
173 SELECT COUNT(1)
174 INTO l_auto_mod_count
175 FROM PO_PRICE_ADJUSTMENTS_V ADJV
176 WHERE ADJV.po_header_id = p_po_header_id
177 AND ADJV.po_line_id = p_po_line_id
178 AND ADJV.automatic_flag = 'Y'
179 --AND ADJ.update_allowed = 'N'
180 AND ADJV.updated_flag = 'N';
181
182 --Check if automatic overridden modifiers are applied
183 SELECT COUNT(1)
184 INTO l_auto_ovr_mod_count
185 FROM PO_PRICE_ADJUSTMENTS_V ADJV
186 WHERE ADJV.po_header_id = p_po_header_id
187 AND ADJV.po_line_id = p_po_line_id
188 AND ADJV.automatic_flag = 'Y'
189 AND ADJV.update_allowed = 'Y'
190 AND ADJV.updated_flag = 'Y';
191
192 --reset count
193 IF l_man_ovr_mod_count > 0 THEN
194 l_man_mod_count := 1;
195 END IF;
196
197 IF l_auto_ovr_mod_count > 0 THEN
198 l_auto_mod_count := 1;
199 END IF;
200
201 l_modifier_code := 'G';
202 IF l_auto_mod_count > 0 THEN
203 l_modifier_code := l_modifier_code||'_AUTOMATIC';
204 END IF;
205
206 IF l_man_mod_count > 0 THEN
207 l_modifier_code := l_modifier_code||'_MANUAL';
208 END IF;
209
210 IF (l_auto_ovr_mod_count > 0 OR l_man_ovr_mod_count > 0) THEN
211 l_modifier_code := l_modifier_code||'_OVR';
212 END IF;
213
214 l_modifier_code := l_modifier_code||'_MOD';
215
216 IF l_modifier_code = 'G_MANUAL_MOD' THEN
217 x_modifier_code := G_MANUAL_MOD;
218 ELSIF l_modifier_code = 'G_AUTOMATIC_MOD' THEN
219 x_modifier_code := G_AUTOMATIC_MOD;
220 ELSIF l_modifier_code = 'G_AUTOMATIC_MANUAL_MOD' THEN
221 x_modifier_code := G_AUTOMATIC_MANUAL_MOD;
222 ELSIF l_modifier_code = 'G_AUTOMATIC_OVR_MOD' THEN
223 x_modifier_code := G_AUTOMATIC_OVR_MOD;
224 ELSIF l_modifier_code = 'G_MANUAL_OVR_MOD' THEN
225 x_modifier_code := G_MANUAL_OVR_MOD;
226 ELSIF l_modifier_code = 'G_AUTOMATIC_MANUAL_OVR_MOD' THEN
227 x_modifier_code := G_AUTOMATIC_MANUAL_OVR_MOD;
228 ELSE
229 x_modifier_code := G_NO_MOD;
230 END IF;
231
232 END get_applied_modifier_code;
233
234
235 -------------------------------------------------------------------------------
236 --Start of Comments
237 --Pre-reqs: None.
238 --Modifies: None.
239 --Locks: None.
240 --Function:
241 -- Determines if modifiers are applied on the line
242 --Parameters:
243 --IN:
244 --p_po_header_id
245 -- Identifies the header that should be checked for applied modifiers
246 --p_po_line_id
247 -- Identifies the line that should be checked for applied modifiers
248 --OUT:
249 --x_mod_exist
250 -- Indicates whether manual or overridden modifiers exist
251 -- 'Y' - manual or overridden modifier applied on the line.
252 -- 'N' - no manual or overridden modifier applied on the line.
253 -- VARCHAR2(1)
254 --End of Comments
255 -------------------------------------------------------------------------------
256
257 PROCEDURE line_modifier_exist
258 (
259 p_po_header_id IN NUMBER
260 , p_po_line_id IN NUMBER
261 , x_line_modifier_exist OUT NOCOPY VARCHAR2
262 )
263 IS
264 d_mod CONSTANT VARCHAR2(100) := D_line_modifier_exist;
265 d_position NUMBER := 0;
266
267 l_line_modifiers_exist VARCHAR2(1);
268 l_modifier_code VARCHAR2(10);
269 BEGIN
270 IF PO_LOG.d_proc THEN
271 PO_LOG.proc_begin(d_mod);
272 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
273 PO_LOG.proc_begin(d_mod,'p_po_line_id',p_po_line_id);
274 END IF;
275
276 d_position := 100;
277 x_line_modifier_exist := 'N';
278
279 get_applied_modifier_code
280 (
281 p_po_header_id => p_po_header_id
282 , p_po_line_id => p_po_line_id
283 , x_modifier_code => l_modifier_code
284 );
285
286 d_position := 200;
287 IF PO_LOG.d_stmt THEN
288 PO_LOG.stmt(d_mod, d_position, 'l_modifier_code', l_modifier_code);
289 END IF;
290
291 IF (l_modifier_code = G_MANUAL_MOD
292 OR l_modifier_code = G_MANUAL_OVR_MOD
293 OR l_modifier_code = G_AUTOMATIC_MOD
294 OR l_modifier_code = G_AUTOMATIC_OVR_MOD
295 OR l_modifier_code = G_AUTOMATIC_MANUAL_MOD
296 OR l_modifier_code = G_AUTOMATIC_MANUAL_OVR_MOD) THEN
297 x_line_modifier_exist := 'Y';
298 ELSE
299 x_line_modifier_exist := 'N';
300 END IF;
301
302 d_position := 300;
303 IF PO_LOG.d_proc THEN
304 PO_LOG.proc_end(d_mod, 'x_line_modifier_exist', x_line_modifier_exist);
305 END IF;
306 END line_modifier_exist;
307
308
309 -------------------------------------------------------------------------------
310 --Start of Comments
311 --Pre-reqs: None.
312 --Modifies: None.
313 --Locks: None.
314 --Function:
315 -- Determines if manual or overridden modifiers are applied on the line
316 --Parameters:
317 --IN:
318 --p_po_header_id
319 -- Identifies the header that should be checked.
320 --p_po_line_id
321 -- Identifies the line that should be checked.
322 --OUT:
323 --x_man_ovr_mod_exist
324 -- Indicates whether manual or overridden modifiers exist
325 -- 'Y' - manual or overridden modifier applied on the line.
326 -- 'N' - no manual or overridden modifier applied on the line.
327 -- VARCHAR2(1)
328 --End of Comments
329 -------------------------------------------------------------------------------
330
331 PROCEDURE check_man_ovr_mod_exist
332 (
333 p_po_header_id IN NUMBER
334 , p_po_line_id IN NUMBER
335 , x_man_ovr_mod_exist OUT NOCOPY VARCHAR2
336 )
337 IS
338 d_mod CONSTANT VARCHAR2(100) := D_check_man_ovr_mod_exist;
339 d_position NUMBER := 0;
340
341 l_modifier_code VARCHAR2(10);
342 BEGIN
343 IF PO_LOG.d_proc THEN
344 PO_LOG.proc_begin(d_mod);
345 PO_LOG.proc_begin(d_mod,'p_po_header_id',p_po_header_id);
346 PO_LOG.proc_begin(d_mod,'p_po_line_id',p_po_line_id);
347 END IF;
348
349 d_position := 100;
350 x_man_ovr_mod_exist := 'N';
351
352 get_applied_modifier_code
353 (
354 p_po_header_id => p_po_header_id
355 , p_po_line_id => p_po_line_id
356 , x_modifier_code => l_modifier_code
357 );
358
359 d_position := 200;
360 IF PO_LOG.d_stmt THEN
361 PO_LOG.stmt(d_mod, d_position, 'l_modifier_code', l_modifier_code);
362 END IF;
363
364 IF (l_modifier_code = G_MANUAL_MOD
365 OR l_modifier_code = G_MANUAL_OVR_MOD
366 OR l_modifier_code = G_AUTOMATIC_MANUAL_MOD
367 OR l_modifier_code = G_AUTOMATIC_OVR_MOD
368 OR l_modifier_code = G_AUTOMATIC_MANUAL_OVR_MOD) THEN
369 x_man_ovr_mod_exist := 'Y';
370 ELSE
371 x_man_ovr_mod_exist := 'N';
372 END IF;
373
374 d_position := 300;
375 IF PO_LOG.d_proc THEN
376 PO_LOG.proc_end(d_mod, 'x_man_ovr_mod_exist', x_man_ovr_mod_exist);
377 END IF;
378 END check_man_ovr_mod_exist;
379
380
381 --------------------------------------------------------------------------------
382 --Start of Comments
383 --Name: popl_manual_overridden_adj
384 --Pre-reqs:
385 -- None.
386 --Modifies:
387 -- None.
388 --Locks:
389 -- None.
390 --Function:
391 -- This procedure populates the QP temp tables with manual and overridden
392 -- modifier details
393 --Parameters:
394 --IN:
395
396
397 --Testing:
398 --
399 -- Exceptions:
400 --Exceptions will be pushed into the stack and raised again.
401 --The Final exception block will catch it and populate the
402 -- the calling
403 --End of Comments
404 -------------------------------------------------------------------------------
405 PROCEDURE popl_manual_overridden_adj
406 (p_draft_id IN NUMBER
407 ,p_order_header_id IN NUMBER
408 ,p_order_line_id_tbl IN QP_PREQ_GRP.NUMBER_TYPE := G_EMPTY_NUMBER_TYPE
409 ,p_quantity_tbl IN QP_PREQ_GRP.NUMBER_TYPE := G_EMPTY_NUMBER_TYPE
410 ,x_return_status OUT NOCOPY VARCHAR2
411 )
412 IS
413 --
414 l_api_name CONSTANT varchar2(30) := 'popl_manual_overridden_adj';
415 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
416 l_progress VARCHAR2(3) := '000';
417 l_exception_msg FND_NEW_MESSAGES.message_text%TYPE;
418
419 i PLS_INTEGER;
420 j PLS_INTEGER;
421 k PLS_INTEGER;
422 m PLS_INTEGER; --used "m" instead of "l", as "l" looks like number "1"
423
424 --Line Details got from cursor Start
425 l_line_det_index PLS_INTEGER;
426 l_from_list_header_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
427 l_from_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
428 l_from_list_line_type_tbl QP_PREQ_GRP.VARCHAR_TYPE;
429 l_from_list_type_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
430 l_list_line_no_tbl QP_PREQ_GRP.VARCHAR_TYPE;
431
432 l_operand_calc_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
433 l_operand_value_tbl QP_PREQ_GRP.VARCHAR_TYPE;
434
435 l_updated_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
436 l_applied_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
437 l_override_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
438 l_automatic_flag_tbl QP_PREQ_GRP.VARCHAR_TYPE;
439
440 l_pricing_group_seq_tbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
441 l_price_break_type_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
442 l_modifier_level_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
443 l_change_reason_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
444 l_change_reason_text_tbl QP_PREQ_GRP.VARCHAR_TYPE;
445
446 l_price_adjustment_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
447 l_rltd_price_adj_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
448 l_relationship_type_code_tbl QP_PREQ_GRP.VARCHAR_TYPE;
449 l_rltd_list_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
450 --Line Details from cursor End
451
452
453 --Line Detail pl/sql tables Start
454 l_line_detail_index QP_PREQ_GRP.PLS_INTEGER_TYPE;
455 l_line_detail_type_code QP_PREQ_GRP.VARCHAR_TYPE;
456 l_price_break_type_code QP_PREQ_GRP.VARCHAR_TYPE;
457 l_list_price QP_PREQ_GRP.NUMBER_TYPE;
458 l_line_index QP_PREQ_GRP.PLS_INTEGER_TYPE;
459 l_created_from_list_header_id QP_PREQ_GRP.NUMBER_TYPE;
460 l_created_from_list_line_id QP_PREQ_GRP.NUMBER_TYPE;
461 l_created_from_list_line_type QP_PREQ_GRP.VARCHAR_TYPE;
462 l_created_from_list_type_code QP_PREQ_GRP.VARCHAR_TYPE;
463 l_created_from_sql QP_PREQ_GRP.VARCHAR_TYPE;
464 l_pricing_group_sequence QP_PREQ_GRP.PLS_INTEGER_TYPE;
465 l_pricing_phase_id QP_PREQ_GRP.PLS_INTEGER_TYPE;
466 l_operand_calculation_code QP_PREQ_GRP.VARCHAR_TYPE;
467 l_operand_value QP_PREQ_GRP.VARCHAR_TYPE;
468 l_substitution_type_code QP_PREQ_GRP.VARCHAR_TYPE;
469 l_substitution_value_from QP_PREQ_GRP.VARCHAR_TYPE;
470 l_substitution_value_to QP_PREQ_GRP.VARCHAR_TYPE;
471 l_ask_for_flag QP_PREQ_GRP.VARCHAR_TYPE;
472 l_price_formula_id QP_PREQ_GRP.NUMBER_TYPE;
473 l_pricing_status_code QP_PREQ_GRP.VARCHAR_TYPE;
474 l_pricing_status_text QP_PREQ_GRP.VARCHAR_TYPE;
475 l_product_precedence QP_PREQ_GRP.PLS_INTEGER_TYPE;
476 l_incompatablility_grp_code QP_PREQ_GRP.VARCHAR_TYPE;
477 l_processed_flag QP_PREQ_GRP.VARCHAR_TYPE;
478 l_applied_flag QP_PREQ_GRP.VARCHAR_TYPE;
479 l_automatic_flag QP_PREQ_GRP.VARCHAR_TYPE;
480 l_override_flag QP_PREQ_GRP.VARCHAR_TYPE;
481 l_primary_uom_flag QP_PREQ_GRP.VARCHAR_TYPE;
482 l_print_on_invoice_flag QP_PREQ_GRP.VARCHAR_TYPE;
483 l_modifier_level_code QP_PREQ_GRP.VARCHAR_TYPE;
484 l_benefit_qty QP_PREQ_GRP.NUMBER_TYPE;
485 l_benefit_uom_code QP_PREQ_GRP.VARCHAR_TYPE;
486 l_list_line_no QP_PREQ_GRP.VARCHAR_TYPE;
487 l_accrual_flag QP_PREQ_GRP.VARCHAR_TYPE;
488 l_accrual_conversion_rate QP_PREQ_GRP.NUMBER_TYPE;
489 l_estim_accrual_rate QP_PREQ_GRP.NUMBER_TYPE;
490 l_recurring_flag QP_PREQ_GRP.VARCHAR_TYPE;
491 l_selected_volume_attr QP_PREQ_GRP.VARCHAR_TYPE;
492 l_rounding_factor QP_PREQ_GRP.PLS_INTEGER_TYPE;
493 l_header_limit_exists QP_PREQ_GRP.VARCHAR_TYPE;
494 l_line_limit_exists QP_PREQ_GRP.VARCHAR_TYPE;
495 l_charge_type_code QP_PREQ_GRP.VARCHAR_TYPE;
496 l_charge_subtype_code QP_PREQ_GRP.VARCHAR_TYPE;
497 l_currency_detail_id QP_PREQ_GRP.NUMBER_TYPE;
498 l_currency_header_id QP_PREQ_GRP.NUMBER_TYPE;
499 l_selling_rounding_factor QP_PREQ_GRP.NUMBER_TYPE;
500 l_order_currency QP_PREQ_GRP.VARCHAR_TYPE;
501 l_pricing_effective_date QP_PREQ_GRP.DATE_TYPE;
502 l_base_currency_code QP_PREQ_GRP.VARCHAR_TYPE;
503 l_line_quantity QP_PREQ_GRP.NUMBER_TYPE;
504 l_updated_flag QP_PREQ_GRP.VARCHAR_TYPE;
505 l_calculation_code QP_PREQ_GRP.VARCHAR_TYPE;
506 l_change_reason_code QP_PREQ_GRP.VARCHAR_TYPE;
507 l_change_reason_text QP_PREQ_GRP.VARCHAR_TYPE;
508
509 l_price_adjustment_id QP_PREQ_GRP.NUMBER_TYPE;
510
511 l_accum_context QP_PREQ_GRP.VARCHAR_TYPE;
512 l_accum_attribute QP_PREQ_GRP.VARCHAR_TYPE;
513 l_accum_flag QP_PREQ_GRP.VARCHAR_TYPE;
514 l_break_uom_code QP_PREQ_GRP.VARCHAR_TYPE;
515 l_break_uom_context QP_PREQ_GRP.VARCHAR_TYPE;
516 l_break_uom_attribute QP_PREQ_GRP.VARCHAR_TYPE;
517 l_process_code QP_PREQ_GRP.VARCHAR_TYPE;
518 --Line Detail pl/sql tables End
519
520 l_line_detail_index_mapping QP_PREQ_GRP.PLS_INTEGER_TYPE;
521
522 --Related Line Detail pl/sql tables start
523 l_line_index_rtbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
524 l_line_detail_index_rtbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
525 l_relationship_type_code_rtbl QP_PREQ_GRP.VARCHAR_TYPE;
526
527 l_rltd_line_index_rtbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
528 l_rltd_line_detail_index_rtbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
529
530 l_list_line_id_rtbl QP_PREQ_GRP.NUMBER_TYPE;
531 l_rltd_list_line_id_rtbl QP_PREQ_GRP.NUMBER_TYPE;
532 l_pricing_status_text_rtbl QP_PREQ_GRP.VARCHAR_TYPE;
533 --Related Line Detail pl/sql tables end
534
535 --Line Attribute pl/sql tables Start
536 l_line_index_atbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
537 l_line_detail_index_atbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
538 l_attribute_level_atbl QP_PREQ_GRP.VARCHAR_TYPE;
539 l_attribute_type_atbl QP_PREQ_GRP.VARCHAR_TYPE;
540 l_list_header_id_atbl QP_PREQ_GRP.NUMBER_TYPE;
541 l_list_line_id_atbl QP_PREQ_GRP.NUMBER_TYPE;
542 l_context_atbl QP_PREQ_GRP.VARCHAR_TYPE;
543 l_attribute_atbl QP_PREQ_GRP.VARCHAR_TYPE;
544 l_value_from_atbl QP_PREQ_GRP.VARCHAR_TYPE;
545 l_setup_value_from_atbl QP_PREQ_GRP.VARCHAR_TYPE;
546 l_value_to_atbl QP_PREQ_GRP.VARCHAR_TYPE;
547 l_setup_value_to_atbl QP_PREQ_GRP.VARCHAR_TYPE;
548 l_grouping_number_atbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
549 l_no_qualifiers_in_grp_atbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
550 l_compar_oper_type_atbl QP_PREQ_GRP.VARCHAR_TYPE;
551 l_validated_flag_atbl QP_PREQ_GRP.VARCHAR_TYPE;
552 l_applied_flag_atbl QP_PREQ_GRP.VARCHAR_TYPE;
553 l_pricing_status_code_atbl QP_PREQ_GRP.VARCHAR_TYPE;
554 l_pricing_status_text_atbl QP_PREQ_GRP.VARCHAR_TYPE;
555 l_qualifier_precedence_atbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
556 l_datatype_atbl QP_PREQ_GRP.VARCHAR_TYPE;
557 l_pricing_attr_flag_atbl QP_PREQ_GRP.VARCHAR_TYPE;
558 l_qualifier_type_atbl QP_PREQ_GRP.VARCHAR_TYPE;
559 l_product_uom_code_atbl QP_PREQ_GRP.VARCHAR_TYPE;
560 l_excluder_flag_atbl QP_PREQ_GRP.VARCHAR_TYPE;
561 l_pricing_phase_id_atbl QP_PREQ_GRP.PLS_INTEGER_TYPE;
562 l_incomp_grp_code_atbl QP_PREQ_GRP.VARCHAR_TYPE;
563 l_line_detail_type_code_atbl QP_PREQ_GRP.VARCHAR_TYPE;
564 l_modifier_level_code_atbl QP_PREQ_GRP.VARCHAR_TYPE;
565 l_primary_uom_flag_atbl QP_PREQ_GRP.VARCHAR_TYPE;
566 --Line Attribute pl/sql tables End
567
568 l_min_price_adj_id NUMBER;
569 l_return_status_text VARCHAR2(2000);
570
571 --E_INVALID_PARAMS EXCEPTION;
572
573 CURSOR man_ovr_min_adj_cur(p_order_line_id PO_LINES_ALL.po_line_id%TYPE) IS
574 SELECT MIN(ADJV.price_adjustment_id) "MIN_PRICE_ADJ_ID"
575 FROM PO_PRICE_ADJUSTMENTS_V ADJV
576 WHERE ADJV.po_header_id = p_order_header_id --ADJV.draft_id = p_draft_id --sometimes draft_id may be passed as null
577 AND ADJV.po_line_id = p_order_line_id
578 AND NVL(ADJV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
579 AND (ADJV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
580 OR
581 (ADJV.automatic_flag = QP_PREQ_GRP.G_YES -- If modifier is automatic, changed and overridable
582 AND
583 ADJV.updated_flag = QP_PREQ_GRP.G_YES
584 AND
585 ADJV.update_allowed = QP_PREQ_GRP.G_YES
586 )
587 );
588
589
590 CURSOR man_ovr_adj_cur(p_order_line_id NUMBER) IS
591 SELECT ADJV.list_header_id "FROM_LIST_HEADER_ID"
592 ,ADJV.list_line_id "FROM_LIST_LINE_ID"
593 ,ADJV.list_line_type_code "FROM_LIST_LINE_TYPE_CODE"
594 ,ADJV.list_type_code "FROM_LIST_TYPE_CODE"
595 ,ADJV.list_line_no "LIST_LINE_NO"
596 --
597 ,ADJV.arithmetic_operator "OPERAND_CALCULATION_CODE"
598 ,ADJV.operand "OPERAND_VALUE"
599 --
600 ,ADJV.updated_flag "UPDATED_FLAG"
601 ,ADJV.applied_flag "APPLIED_FLAG"
602 ,ADJV.update_allowed "OVERRIDE_FLAG"
603 ,ADJV.automatic_flag "AUTOMATIC_FLAG"
604 --
605 ,ADJV.pricing_group_sequence "PRICING_GROUP_SEQUENCE"
606 ,ADJV.price_break_type_code "PRICE_BREAK_TYPE_CODE"
607 ,ADJV.modifier_level_code "MODIFIER_LEVEL_CODE"
608 ,ADJV.change_reason_code "CHANGE_REASON_CODE"
609 ,ADJV.change_reason_text "CHANGE_REASON_TEXT"
610 --
611 ,ADJV.price_adjustment_id "PRICE_ADJUSTMENT_ID" --Child price adjustment id
612 ,ADJV.parent_adjustment_id "RLTD_PRICE_ADJUSTMENT_ID" --Parent price adjustment id
613 ,ADJV.parent_list_line_type_code "RELATIONSHIP_TYPE_CODE" --Parent Child relationship type code
614 ,ADJV.parent_list_line_id "RLTD_LIST_LINE_ID" --Parent List Line Id
615 FROM PO_PRICE_ADJUSTMENTS_V ADJV
616 WHERE ADJV.po_header_id = p_order_header_id --ADJV.draft_id = p_draft_id --sometimes draft_id may be passed as null
617 AND ADJV.po_line_id = p_order_line_id
618 AND NVL(ADJV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
619 AND (ADJV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
620 OR
621 (ADJV.automatic_flag = QP_PREQ_GRP.G_YES -- If modifier is automatic, changed and overridable
622 AND
623 ADJV.updated_flag = QP_PREQ_GRP.G_YES
624 AND
625 ADJV.update_allowed = QP_PREQ_GRP.G_YES
626 )
627 )
628 ORDER BY ADJV.price_adjustment_id ASC;
629
630 CURSOR man_ovr_adj_attr_cur(p_order_line_id NUMBER, p_min_price_adj_id NUMBER) IS
631 SELECT (ATTRV.price_adjustment_id - p_min_price_adj_id) "LINE_DETAIL_INDEX"
632 --
633 ,DECODE(ATTRV.flex_title
634 ,'QP_ATTR_DEFNS_QUALIFIER', 'QUALIFIER'
635 ,'QP_ATTR_DEFNS_PRODUCT', 'PRODUCT'
636 ,'PRICING'
637 ) "ATTRIBUTE_TYPE"
638 ,ATTRV.pricing_context "CONTEXT"
639 ,ATTRV.pricing_attribute "ATTRIBUTE"
640 ,ATTRV.pricing_attr_value_from "VALUE_FROM"
641 ,ATTRV.pricing_attr_value_to "VALUE_TO"
642 ,ATTRV.comparison_operator "COMPARISON_OPERATOR_TYPE"
643 ,DECODE(ATTRV.flex_title
644 ,'QP_ATTR_DEFNS_QUALIFIER', 'Y', 'N') "VALIDATED_FLAG"
645 FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
646 WHERE ATTRV.po_header_id = p_order_header_id --ATTRV.draft_id = p_draft_id --sometimes draft_id may be passed as null
647 AND ATTRV.po_line_id = p_order_line_id
648 AND NVL(ATTRV.applied_flag,'Y') = 'Y' --To avoid applying manual modifiers selected and cancelled by the user
649 AND (ATTRV.automatic_flag = QP_PREQ_GRP.G_NO -- If modifier is not automatic. i.e., manual
650 OR
651 (ATTRV.automatic_flag = QP_PREQ_GRP.G_YES -- If modifier is automatic, changed and overridable
652 AND
653 ATTRV.updated_flag = QP_PREQ_GRP.G_YES
654 AND
655 ATTRV.update_allowed = QP_PREQ_GRP.G_YES
656 )
657 )
658 ORDER BY ATTRV.price_adjustment_id ASC;
659
660 BEGIN
661 SAVEPOINT POPULATE_QP_TABLES;
662 x_return_status := FND_API.G_RET_STS_SUCCESS;
663 l_progress := '000';
664
665 --Check if order_header_id or line_ids are passed
666 IF (p_order_header_id IS NULL) THEN
667 RETURN;
668 ELSIF (p_order_line_id_tbl IS NULL OR p_order_line_id_tbl.count <= 0) THEN
669 RETURN;
670 ELSIF (p_quantity_tbl IS NULL OR p_quantity_tbl.count <> p_order_line_id_tbl.count) THEN
671 --IF order lines are passed and the corresponding quantities are not passed, the quantity will be defaulted to '1' for the lines with missing quantity
672 NULL;
673 END IF;
674
675 l_progress := '020';
676 IF g_debug_stmt THEN
677 PO_DEBUG.debug_begin(l_log_head);
678 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_header_id',p_order_header_id);
679
680 FOR i IN p_order_line_id_tbl.FIRST..p_order_line_id_tbl.LAST
681 LOOP
682 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_line_id_tbl('||i||')',p_order_line_id_tbl(i));
683 IF (p_quantity_tbl.exists(i)) THEN
684 PO_DEBUG.debug_var(l_log_head,l_progress,'p_quantity_tbl('||i||')',p_quantity_tbl(i));
685 ELSE
686 PO_DEBUG.debug_var(l_log_head,l_progress,'missing p_quantity_tbl('||i||')',1);
687 END IF;
688 END LOOP;
689 END IF;
690
691 l_progress := '040';
692 IF g_debug_stmt THEN
693 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Before get manual and overridden price adjustments and associated context attributes');
694 END IF;
695
696 --For each order line id get the manual and overridden price adjustments and associated context attributes.
697 FOR i IN p_order_line_id_tbl.FIRST .. p_order_line_id_tbl.LAST
698 LOOP
699 l_progress := '060';
700 IF g_debug_stmt THEN
701 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get manual and overridden adjustments for order line id: '||p_order_line_id_tbl(i));
702 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Reset line detail and related line detail pl/sql tables');
703 END IF;
704
705 l_from_list_header_id_tbl.delete;
706 l_from_list_line_id_tbl.delete;
707 l_from_list_line_type_tbl.delete;
708 l_from_list_type_code_tbl.delete;
709 l_list_line_no_tbl.delete;
710
711 l_operand_calc_code_tbl.delete;
712 l_operand_value_tbl.delete;
713
714 l_updated_flag_tbl.delete;
715 l_applied_flag_tbl.delete;
716 l_override_flag_tbl.delete;
717 l_automatic_flag_tbl.delete;
718
719 l_pricing_group_seq_tbl.delete;
720 l_price_break_type_code_tbl.delete;
721 l_modifier_level_code_tbl.delete;
722 l_change_reason_code_tbl.delete;
723 l_change_reason_text_tbl.delete;
724
725 l_price_adjustment_id_tbl.delete;
726 l_rltd_price_adj_id_tbl.delete; --Parent price adjustment id
727 l_relationship_type_code_tbl.delete; --Parent Child relationship type code
728 l_rltd_list_line_id_tbl.delete; --Parent List Line Id
729
730 --Reset line detail pl/sql tables Start
731 l_line_detail_index.delete;
732
733 l_created_from_list_header_id.delete;
734 l_created_from_list_line_id.delete;
735 l_created_from_list_line_type.delete;
736 l_created_from_list_type_code.delete;
737 l_list_line_no.delete;
738
739 l_operand_calculation_code.delete;
740 l_operand_value.delete;
741
742 l_updated_flag.delete;
743 l_applied_flag.delete;
744 l_override_flag.delete;
745 l_automatic_flag.delete;
746
747 l_pricing_group_sequence.delete;
748 l_price_break_type_code.delete;
749 l_modifier_level_code.delete;
750 l_change_reason_code.delete;
751 l_change_reason_text.delete;
752
753 l_line_index.delete;
754 l_line_detail_type_code.delete;
755 l_line_quantity.delete;
756
757 l_pricing_status_code.delete;
758 l_pricing_status_text.delete;
759
760 l_list_price.delete;
761
762 l_created_from_sql.delete;
763 l_pricing_phase_id.delete;
764
765 l_substitution_type_code.delete;
766 l_substitution_value_from.delete;
767 l_substitution_value_to.delete;
768 l_ask_for_flag.delete;
769 l_price_formula_id.delete;
770
771 l_product_precedence.delete;
772 l_incompatablility_grp_code.delete;
773
774 l_primary_uom_flag.delete;
775 l_print_on_invoice_flag.delete;
776
777 l_benefit_qty.delete;
778 l_benefit_uom_code.delete;
779
780 l_accrual_flag.delete;
781 l_accrual_conversion_rate.delete;
782 l_estim_accrual_rate.delete;
783 l_recurring_flag.delete;
784 l_selected_volume_attr.delete;
785 l_rounding_factor.delete;
786 l_header_limit_exists.delete;
787 l_line_limit_exists.delete;
788 l_charge_type_code.delete;
789 l_charge_subtype_code.delete;
790 l_currency_detail_id.delete;
791 l_currency_header_id.delete;
792 l_selling_rounding_factor.delete;
793 l_order_currency.delete;
794 l_pricing_effective_date.delete;
795 l_base_currency_code.delete;
796 l_calculation_code.delete;
797
798 l_price_adjustment_id.delete;
799
800 l_accum_context.delete;
801 l_accum_attribute.delete;
802 l_accum_flag.delete;
803 l_break_uom_code.delete;
804 l_break_uom_context.delete;
805 l_break_uom_attribute.delete;
806 l_process_code.delete;
807 --Reset line detail pl/sql tables End
808
809 --pl/sql tables used for line mapping
810 l_line_detail_index_mapping.delete;
811
812
813 --Reset related lines pl/sql tables, Start
814 k := 0;
815
816 l_line_index_rtbl.delete;
817 l_line_detail_index_rtbl.delete;
818 l_relationship_type_code_rtbl.delete;
819
820
821 l_rltd_line_index_rtbl.delete;
822 l_rltd_line_detail_index_rtbl.delete;
823
824 l_list_line_id_rtbl.delete;
825 l_rltd_list_line_id_rtbl.delete;
826 l_pricing_status_text_rtbl.delete;
827 --Reset related lines pl/sql tables, End
828
829
830 --Reset line attribute pl/sql tables Start
831 l_line_index_atbl.delete;
832 l_line_detail_index_atbl.delete;
833 l_attribute_level_atbl.delete;
834 l_attribute_type_atbl.delete;
835 l_list_header_id_atbl.delete;
836 l_list_line_id_atbl.delete;
837 l_context_atbl.delete;
838 l_attribute_atbl.delete;
839 l_value_from_atbl.delete;
840 l_setup_value_from_atbl.delete;
841 l_value_to_atbl.delete;
842 l_setup_value_to_atbl.delete;
843 l_grouping_number_atbl.delete;
844 l_no_qualifiers_in_grp_atbl.delete;
845 l_compar_oper_type_atbl.delete;
846 l_validated_flag_atbl.delete;
847 l_applied_flag_atbl.delete;
848 l_pricing_status_code_atbl.delete;
849 l_pricing_status_text_atbl.delete;
850 l_qualifier_precedence_atbl.delete;
851 l_datatype_atbl.delete;
852 l_pricing_attr_flag_atbl.delete;
853 l_qualifier_type_atbl.delete;
854 l_product_uom_code_atbl.delete;
855 l_excluder_flag_atbl.delete;
856 l_pricing_phase_id_atbl.delete;
857 l_incomp_grp_code_atbl.delete;
858 l_line_detail_type_code_atbl.delete;
859 l_modifier_level_code_atbl.delete;
860 l_primary_uom_flag_atbl.delete;
861 --Reset line attribute pl/sql tables End
862
863
864 l_progress := '080';
865 IF g_debug_stmt THEN
866 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get Min Price Adjustment Id from the list of manual and overridden price adjustments');
867 END IF;
868 OPEN man_ovr_min_adj_cur(p_order_line_id_tbl(i));
869 FETCH man_ovr_min_adj_cur INTO l_min_price_adj_id;
870 CLOSE man_ovr_min_adj_cur;
871
872 l_progress := '090';
873 IF l_min_price_adj_id IS NULL THEN --No Adjustments found
874 PO_DEBUG.debug_stmt(l_log_head,l_progress,'No Adjustments found, continue with the next order line');
875 ELSE --To use it in the calculation of line detail index
876 l_min_price_adj_id := l_min_price_adj_id - 1;
877
878 l_progress := '100';
879 IF g_debug_stmt THEN
880 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get manual and overridden price adjustments from Price Adjustments tables');
881 END IF;
882
883 OPEN man_ovr_adj_cur(p_order_line_id_tbl(i));
884 FETCH man_ovr_adj_cur BULK COLLECT INTO
885 l_from_list_header_id_tbl,
886 l_from_list_line_id_tbl,
887 l_from_list_line_type_tbl,
888 l_from_list_type_code_tbl,
889 l_list_line_no_tbl,
890
891 l_operand_calc_code_tbl,
892 l_operand_value_tbl,
893
894 l_updated_flag_tbl,
895 l_applied_flag_tbl,
896 l_override_flag_tbl,
897 l_automatic_flag_tbl,
898
899 l_pricing_group_seq_tbl,
900 l_price_break_type_code_tbl,
901 l_modifier_level_code_tbl,
902 l_change_reason_code_tbl,
903 l_change_reason_text_tbl,
904
905 l_price_adjustment_id_tbl,
906 l_rltd_price_adj_id_tbl, --Parent price adjustment id
907 l_relationship_type_code_tbl, --Parent Child relationship type code
908 l_rltd_list_line_id_tbl; --Parent List Line Id
909
910
911 IF l_price_adjustment_id_tbl.count = 0 THEN
912 l_progress := '120';
913 IF g_debug_stmt THEN
914 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Continue with the next order line, when no manual or overridden modifiers are found');
915 END IF;
916 ELSE
917 l_progress := '140';
918 IF g_debug_stmt THEN
919 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Initialize parameters before calling Insert Line Details in QP_PREQ_GRP');
920 END IF;
921 m := 0;
922 FOR j IN l_price_adjustment_id_tbl.FIRST .. l_price_adjustment_id_tbl.LAST
923 LOOP
924 m := m + 1;
925 --Note: If the manual or automatic modifier is overridable and the user has overridden the rate from front end,
926 --the updated flag would have been set to G_YES.
927 --In case of manual modifiers the updated flag needs to be set to G_YES, to re apply the manual modifier to get the
928 --correct unit price
929
930 --Set the source line index
931 l_line_index(m) := i;
932
933 l_line_det_index := l_price_adjustment_id_tbl(j) - l_min_price_adj_id;
934 --Mapping received index (l_line_det_index_tbl(j)) with the given index (m)
935 l_line_detail_index_mapping(l_line_det_index) := m;
936
937 --Initialize pl/sql table returned from cursor
938 l_line_detail_index(m) := m;
939 l_created_from_list_header_id(m) := l_from_list_header_id_tbl(j);
940 l_created_from_list_line_id(m) := l_from_list_line_id_tbl(j);
941 l_created_from_list_line_type(m) := l_from_list_line_type_tbl(j);
942 l_created_from_list_type_code(m) := l_from_list_type_code_tbl(j);
943 l_list_line_no(m) := l_list_line_no_tbl(j);
944
945 l_operand_calculation_code(m) := l_operand_calc_code_tbl(j);
946 l_operand_value(m) := l_operand_value_tbl(j);
947
948 l_applied_flag(m) := l_applied_flag_tbl(j);
949 l_override_flag(m) := l_override_flag_tbl(j);
950 l_automatic_flag(m) := l_automatic_flag_tbl(j);
951
952 IF (l_automatic_flag_tbl(j) = QP_PREQ_GRP.G_NO) THEN
953 l_updated_flag(m) := QP_PREQ_GRP.G_YES;
954 l_applied_flag(m) := QP_PREQ_GRP.G_YES; --the value l_applied_flag_tbl(j) from the cursor Should always be 'Y', this line can be removed once confirmed
955 ELSE
956 l_updated_flag(m) := l_updated_flag_tbl(j); -- if automatic, it can be either Y or N
957 END IF;
958
959 l_pricing_group_sequence(m) := l_pricing_group_seq_tbl(j);
960 l_price_break_type_code(m) := l_price_break_type_code_tbl(j);
961 l_modifier_level_code(m) := l_modifier_level_code_tbl(j);
962 l_change_reason_code(m) := l_change_reason_code_tbl(j);
963 l_change_reason_text(m) := l_change_reason_text_tbl(j);
964
965 l_price_adjustment_id(m) := l_price_adjustment_id_tbl(j);
966 --Initialize pl/sql table returned from cursor
967
968 --Line Details Type Code cannot be null. Set it to 'NULL' string
969 l_line_detail_type_code(m) := 'NULL';
970
971 --Set the process code
972 l_process_code(m) := QP_PREQ_GRP.G_STATUS_NEW;
973 l_processed_flag(m) := NULL;
974
975 --Set line quantity
976 IF (p_quantity_tbl.exists(i)) THEN
977 l_line_quantity(m) := p_quantity_tbl(i);
978 ELSE
979 l_line_quantity(m) := 1;
980 END IF;
981
982 --Set the pricing status to unchanged for the pricing engine to consider the adjsutment
983 l_pricing_status_code(m) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
984 l_pricing_status_text(m) := NULL;
985
986 --Initiailize the other parameters as NULL
987 l_list_price(m) := NULL;
988
989 l_created_from_sql(m) := NULL;
990 l_pricing_phase_id(m) := NULL;
991
992 l_substitution_type_code(m) := NULL;
993 l_substitution_value_from(m) := NULL;
994 l_substitution_value_to(m) := NULL;
995 l_ask_for_flag(m) := NULL;
996 l_price_formula_id(m) := NULL;
997
998 l_product_precedence(m) := NULL;
999 l_incompatablility_grp_code(m) := NULL;
1000
1001 l_primary_uom_flag(m) := NULL;
1002 l_print_on_invoice_flag(m) := NULL;
1003
1004 l_benefit_qty(m) := NULL;
1005 l_benefit_uom_code(m) := NULL;
1006
1007 l_accrual_flag(m) := NULL;
1008 l_accrual_conversion_rate(m) := NULL;
1009 l_estim_accrual_rate(m) := NULL;
1010 l_recurring_flag(m) := NULL;
1011 l_selected_volume_attr(m) := NULL;
1012 l_rounding_factor(m) := NULL;
1013 l_header_limit_exists(m) := NULL;
1014 l_line_limit_exists(m) := NULL;
1015 l_charge_type_code(m) := NULL;
1016 l_charge_subtype_code(m) := NULL;
1017 l_currency_detail_id(m) := NULL;
1018 l_currency_header_id(m) := NULL;
1019 l_selling_rounding_factor(m) := NULL;
1020 l_order_currency(m) := NULL;
1021 l_pricing_effective_date(m) := NULL;
1022 l_base_currency_code(m) := NULL;
1023 l_calculation_code(m) := NULL;
1024
1025 l_accum_context(m) := NULL;
1026 l_accum_attribute(m) := NULL;
1027 l_accum_flag(m) := NULL;
1028 l_break_uom_code(m) := NULL;
1029 l_break_uom_context(m) := NULL;
1030 l_break_uom_attribute(m) := NULL;
1031
1032
1033 --set the related line information
1034 IF l_rltd_price_adj_id_tbl(j) IS NOT NULL THEN
1035 k := k + 1;
1036
1037 l_line_index_rtbl(k) := i;
1038 l_line_detail_index_rtbl(k) := l_rltd_price_adj_id_tbl(j) - l_min_price_adj_id;
1039
1040 IF (l_relationship_type_code_tbl(j) = 'PBH') THEN
1041 l_relationship_type_code_rtbl(k) := 'PBH_LINE';
1042 ELSE
1043 l_relationship_type_code_rtbl(k) := l_relationship_type_code_tbl(j);
1044 END IF;
1045
1046 l_rltd_line_index_rtbl(k) := i;
1047 l_rltd_line_detail_index_rtbl(k) := m;
1048
1049 l_list_line_id_rtbl(k) := l_rltd_list_line_id_tbl(j);
1050 l_rltd_list_line_id_rtbl(k) := l_from_list_line_id_tbl(j);
1051 l_pricing_status_text_rtbl(k) := NULL;
1052 END IF;
1053
1054 END LOOP;
1055
1056 IF l_line_detail_index_rtbl.count > 0 THEN
1057 FOR k IN l_line_detail_index_rtbl.FIRST .. l_line_detail_index_rtbl.LAST
1058 LOOP
1059 l_line_detail_index_rtbl(k) := l_line_detail_index_mapping(l_line_detail_index_rtbl(k)); --The Parent Line will always be found
1060 END LOOP;
1061 END IF;
1062
1063
1064 OPEN man_ovr_adj_attr_cur(p_order_line_id_tbl(i), l_min_price_adj_id);
1065 FETCH man_ovr_adj_attr_cur BULK COLLECT INTO
1066 l_line_detail_index_atbl,
1067 l_attribute_type_atbl,
1068 l_context_atbl,
1069 l_attribute_atbl,
1070 l_value_from_atbl,
1071 l_value_to_atbl,
1072 l_compar_oper_type_atbl,
1073 l_validated_flag_atbl;
1074
1075 l_progress := '160';
1076 IF g_debug_stmt THEN
1077 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Initialize parameters before calling Insert Line Attr Details in QP_PREQ_GRP');
1078 END IF;
1079 IF l_line_detail_index_atbl.count > 0 THEN
1080 FOR j IN l_line_detail_index_atbl.FIRST .. l_line_detail_index_atbl.LAST
1081 LOOP
1082 --Initialize Line Attribute pl/sql tables Start
1083 l_line_index_atbl(j) := i;
1084
1085 l_line_detail_index_atbl(j) := l_line_detail_index_mapping(l_line_detail_index_atbl(j)); --The Parent will always exist
1086
1087 l_attribute_level_atbl(j) := 'Line'; --Default to 'Line'
1088 --l_attribute_type_atbl(j) := NULL; --Initialized in fetch statement
1089 l_list_header_id_atbl(j) := NULL;
1090 l_list_line_id_atbl(j) := NULL;
1091
1092 --l_context_atbl(j) := NULL; --Initialized in fetch statement
1093 --l_attribute_atbl(j) := NULL; --Initialized in fetch statement
1094 --l_value_from_atbl(j) := NULL; --Initialized in fetch statement
1095 l_setup_value_from_atbl(j) := NULL;
1096 --l_value_to_atbl(j) := NULL; --Initialized in fetch statement
1097 l_setup_value_to_atbl(j) := NULL;
1098 l_grouping_number_atbl(j) := NULL;
1099 l_no_qualifiers_in_grp_atbl(j) := NULL;
1100 --l_compar_oper_type_atbl(j) := NULL; --Initialized in fetch statement
1101 --l_validated_flag_atbl(j) := NULL; --Initialized in fetch statement
1102 l_applied_flag_atbl(j) := NULL;
1103 l_pricing_status_code_atbl(j) := QP_PREQ_GRP.G_STATUS_NEW;
1104 l_pricing_status_text_atbl(j) := NULL;
1105 l_qualifier_precedence_atbl(j) := NULL;
1106 l_datatype_atbl(j) := NULL;
1107 l_pricing_attr_flag_atbl(j) := NULL;
1108 l_qualifier_type_atbl(j) := NULL;
1109 l_product_uom_code_atbl(j) := NULL;
1110 l_excluder_flag_atbl(j) := NULL;
1111 l_pricing_phase_id_atbl(j) := NULL;
1112 l_incomp_grp_code_atbl(j) := NULL;
1113 l_line_detail_type_code_atbl(j) := NULL;
1114 l_modifier_level_code_atbl(j) := NULL;
1115 l_primary_uom_flag_atbl(j) := NULL;
1116 --Initialize Line Attribute pl/sql tables End
1117 END LOOP;
1118 END IF;
1119
1120 l_progress := '170';
1121 IF g_debug_stmt THEN
1122 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling bulk insert procedure QP_PREQ_GRP.INSERT_LDETS2 to insert line details');
1123 END IF;
1124
1125 IF l_price_adjustment_id_tbl.count > 0 THEN
1126 QP_PREQ_GRP.INSERT_LDETS2
1127 (p_line_detail_index => l_line_detail_index
1128 ,p_line_detail_type_code => l_line_detail_type_code
1129 ,p_price_break_type_code => l_price_break_type_code
1130 ,p_list_price => l_list_price
1131 ,p_line_index => l_line_index
1132 ,p_created_from_list_header_id => l_created_from_list_header_id
1133 ,p_created_from_list_line_id => l_created_from_list_line_id
1134 ,p_created_from_list_line_type => l_created_from_list_line_type
1135 ,p_created_from_list_type_code => l_created_from_list_type_code
1136 ,p_created_from_sql => l_created_from_sql
1137 ,p_pricing_group_sequence => l_pricing_group_sequence
1138 ,p_pricing_phase_id => l_pricing_phase_id
1139 ,p_operand_calculation_code => l_operand_calculation_code
1140 ,p_operand_value => l_operand_value
1141 ,p_substitution_type_code => l_substitution_type_code
1142 ,p_substitution_value_from => l_substitution_value_from
1143 ,p_substitution_value_to => l_substitution_value_to
1144 ,p_ask_for_flag => l_ask_for_flag
1145 ,p_price_formula_id => l_price_formula_id
1146 ,p_pricing_status_code => l_pricing_status_code
1147 ,p_pricing_status_text => l_pricing_status_text
1148 ,p_product_precedence => l_product_precedence
1149 ,p_incompatablility_grp_code => l_incompatablility_grp_code
1150 ,p_processed_flag => l_processed_flag
1151 ,p_applied_flag => l_applied_flag
1152 ,p_automatic_flag => l_automatic_flag
1153 ,p_override_flag => l_override_flag
1154 ,p_primary_uom_flag => l_primary_uom_flag
1155 ,p_print_on_invoice_flag => l_print_on_invoice_flag
1156 ,p_modifier_level_code => l_modifier_level_code
1157 ,p_benefit_qty => l_benefit_qty
1158 ,p_benefit_uom_code => l_benefit_uom_code
1159 ,p_list_line_no => l_list_line_no
1160 ,p_accrual_flag => l_accrual_flag
1161 ,p_accrual_conversion_rate => l_accrual_conversion_rate
1162 ,p_estim_accrual_rate => l_estim_accrual_rate
1163 ,p_recurring_flag => l_recurring_flag
1164 ,p_selected_volume_attr => l_selected_volume_attr
1165 ,p_rounding_factor => l_rounding_factor
1166 ,p_header_limit_exists => l_header_limit_exists
1167 ,p_line_limit_exists => l_line_limit_exists
1168 ,p_charge_type_code => l_charge_type_code
1169 ,p_charge_subtype_code => l_charge_subtype_code
1170 ,p_currency_detail_id => l_currency_detail_id
1171 ,p_currency_header_id => l_currency_header_id
1172 ,p_selling_rounding_factor => l_selling_rounding_factor
1173 ,p_order_currency => l_order_currency
1174 ,p_pricing_effective_date => l_pricing_effective_date
1175 ,p_base_currency_code => l_base_currency_code
1176 ,p_line_quantity => l_line_quantity
1177 ,p_updated_flag => l_updated_flag
1178 ,p_calculation_code => l_calculation_code
1179 ,p_change_reason_code => l_change_reason_code
1180 ,p_change_reason_text => l_change_reason_text
1181 ,p_price_adjustment_id => l_price_adjustment_id
1182 ,p_accum_context => l_accum_context
1183 ,p_accum_attribute => l_accum_attribute
1184 ,p_accum_flag => l_accum_flag
1185 ,p_break_uom_code => l_break_uom_code
1186 ,p_break_uom_context => l_break_uom_context
1187 ,p_break_uom_attribute => l_break_uom_attribute
1188 ,p_process_code => l_process_code
1189 ,x_status_code => x_return_status
1190 ,x_status_text => l_return_status_text
1191 );
1192
1193 l_progress := '180';
1194 IF g_debug_stmt THEN
1195 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling INSERT_LDETS2');
1196 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1197 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status_text',l_return_status_text);
1198 END IF;
1199
1200 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1201 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1202 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1203 FND_MSG_PUB.Add;
1204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1205 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1206 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1207 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1208 FND_MSG_PUB.Add;
1209 RAISE FND_API.G_EXC_ERROR;
1210 END IF;
1211 END IF;
1212
1213
1214 l_progress := '190';
1215 IF g_debug_stmt THEN
1216 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling bulk insert procedure QP_PREQ_GRP.INSERT_RLTD_LINES2 to insert relationship between lines');
1217 END IF;
1218 IF l_line_detail_index_rtbl.count > 0 THEN
1219 QP_PREQ_GRP.INSERT_RLTD_LINES2
1220 (p_line_index => l_line_index_rtbl
1221 ,p_line_detail_index => l_line_detail_index_rtbl
1222 ,p_relationship_type_code => l_relationship_type_code_rtbl
1223 ,p_related_line_index => l_rltd_line_index_rtbl
1224 ,p_related_line_detail_index => l_rltd_line_detail_index_rtbl
1225 ,x_status_code => x_return_status
1226 ,x_status_text => l_return_status_text
1227 ,p_list_line_id => l_list_line_id_rtbl
1228 ,p_related_list_line_id => l_rltd_list_line_id_rtbl
1229 ,p_pricing_status_text => l_pricing_status_text_rtbl
1230 );
1231
1232 l_progress := '200';
1233 IF g_debug_stmt THEN
1234 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling INSERT_RLTD_LINES2');
1235 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1236 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status_text',l_return_status_text);
1237 END IF;
1238
1239 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1240 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1241 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1242 FND_MSG_PUB.Add;
1243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1244 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1245 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1246 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1247 FND_MSG_PUB.Add;
1248 RAISE FND_API.G_EXC_ERROR;
1249 END IF;
1250 END IF;
1251
1252
1253 l_progress := '210';
1254 IF g_debug_stmt THEN
1255 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Calling bulk insert procedure QP_PREQ_GRP.INSERT_LINE_ATTRS2 to insert line attribute details');
1256 END IF;
1257 IF l_line_detail_index_atbl.count > 0 THEN
1258 QP_PREQ_GRP.INSERT_LINE_ATTRS2
1259 (p_line_index_tbl => l_line_index_atbl
1260 ,p_line_detail_index_tbl => l_line_detail_index_atbl
1261 ,p_attribute_level_tbl => l_attribute_level_atbl
1262 ,p_attribute_type_tbl => l_attribute_type_atbl
1263 ,p_list_header_id_tbl => l_list_header_id_atbl
1264 ,p_list_line_id_tbl => l_list_line_id_atbl
1265 ,p_context_tbl => l_context_atbl
1266 ,p_attribute_tbl => l_attribute_atbl
1267 ,p_value_from_tbl => l_value_from_atbl
1268 ,p_setup_value_from_tbl => l_setup_value_from_atbl
1269 ,p_value_to_tbl => l_value_to_atbl
1270 ,p_setup_value_to_tbl => l_setup_value_to_atbl
1271 ,p_grouping_number_tbl => l_grouping_number_atbl
1272 ,p_no_qualifiers_in_grp_tbl => l_no_qualifiers_in_grp_atbl
1273 ,p_comparison_operator_type_tbl => l_compar_oper_type_atbl
1274 ,p_validated_flag_tbl => l_validated_flag_atbl
1275 ,p_applied_flag_tbl => l_applied_flag_atbl
1276 ,p_pricing_status_code_tbl => l_pricing_status_code_atbl
1277 ,p_pricing_status_text_tbl => l_pricing_status_text_atbl
1278 ,p_qualifier_precedence_tbl => l_qualifier_precedence_atbl
1279 ,p_datatype_tbl => l_datatype_atbl
1280 ,p_pricing_attr_flag_tbl => l_pricing_attr_flag_atbl
1281 ,p_qualifier_type_tbl => l_qualifier_type_atbl
1282 ,p_product_uom_code_tbl => l_product_uom_code_atbl
1283 ,p_excluder_flag_tbl => l_excluder_flag_atbl
1284 ,p_pricing_phase_id_tbl => l_pricing_phase_id_atbl
1285 ,p_incompatability_grp_code_tbl => l_incomp_grp_code_atbl
1286 ,p_line_detail_type_code_tbl => l_line_detail_type_code_atbl
1287 ,p_modifier_level_code_tbl => l_modifier_level_code_atbl
1288 ,p_primary_uom_flag_tbl => l_primary_uom_flag_atbl
1289 ,x_status_code => x_return_status
1290 ,x_status_text => l_return_status_text
1291 );
1292
1293 l_progress := '220';
1294 IF g_debug_stmt THEN
1295 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling INSERT_LINE_ATTRS2');
1296 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
1297 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status_text',l_return_status_text);
1298 END IF;
1299
1300 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1301 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1302 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1303 FND_MSG_PUB.Add;
1304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1305 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1306 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1307 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1308 FND_MSG_PUB.Add;
1309 RAISE FND_API.G_EXC_ERROR;
1310 END IF;
1311 END IF;
1312 END IF;
1313 CLOSE man_ovr_adj_attr_cur;
1314 CLOSE man_ovr_adj_cur;
1315 END IF;
1316 END LOOP;
1317 /*
1318 IF p_order_header_id = 81699 THEN
1319 DELETE FROM QP_PREQ_RLTD_LINES_TMP_TEST;
1320 DELETE FROM QP_PREQ_LINE_ATTRS_TMP_TEST;
1321 DELETE FROM QP_PREQ_LINES_TMP_TEST;
1322 DELETE FROM QP_PREQ_LDETS_TMP_TEST;
1323
1324 INSERT INTO QP_PREQ_RLTD_LINES_TMP_TEST (select * from QP_PREQ_RLTD_LINES_TMP);
1325 INSERT INTO QP_PREQ_LINE_ATTRS_TMP_TEST (select * from QP_PREQ_LINE_ATTRS_TMP);
1326 INSERT INTO QP_PREQ_LINES_TMP_TEST (select * from QP_PREQ_LINES_TMP);
1327 INSERT INTO QP_PREQ_LDETS_TMP_TEST (select * from QP_PREQ_LDETS_TMP);
1328 END IF;
1329 */
1330 --
1331 EXCEPTION
1332 WHEN FND_API.G_EXC_ERROR THEN
1333 x_return_status := FND_API.G_RET_STS_ERROR;
1334 IF g_debug_unexp THEN
1335 PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING POPL_MANUAL_OVERRIDDEN_ADJ WITH EXC ERROR with rollback');
1336 END IF;
1337 ROLLBACK TO SAVEPOINT POPULATE_QP_TABLES;
1338 RAISE FND_API.G_EXC_ERROR;
1339 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1341 IF g_debug_unexp THEN
1342 PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING POPL_MANUAL_OVERRIDDEN_ADJ WITH UNEXPECTED ERROR with rollback');
1343 END IF;
1344 ROLLBACK TO SAVEPOINT POPULATE_QP_TABLES;
1345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1346 WHEN OTHERS THEN
1347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1348 IF g_debug_unexp THEN
1349 PO_DEBUG.debug_stmt(l_log_head,l_progress,'UnExpected ERROR IN POPL_MANUAL_OVERRIDDEN_ADJ. SQLERRM at '||l_progress||': '||SQLERRM);
1350 END IF;
1351
1352 IF g_debug_unexp THEN
1353 PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING PO_PRICE_ADJUSTMENTS_PKG.POPL_MANUAL_OVERRIDDEN_ADJ with rollback');
1354 END IF;
1355 ROLLBACK TO SAVEPOINT POPULATE_QP_TABLES;
1356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1357 END popl_manual_overridden_adj;
1358
1359 --------------------------------------------------------------------------------
1360 --Start of Comments
1361 --Name: extract_price_adjustments
1362 --Pre-reqs:
1363 -- None.
1364 --Modifies:
1365 -- None.
1366 --Locks:
1367 -- None.
1368 --Function:
1369 -- This procedure extracts price adjustment details from the QP temp tables
1370 -- and populates the PO adjustment tables.
1371 --Parameters:
1372 --IN:
1373 --Testing:
1374 --
1375 --End of Comments
1376 -------------------------------------------------------------------------------
1377 PROCEDURE extract_price_adjustments
1378 (p_draft_id IN NUMBER
1379 ,p_order_header_id IN NUMBER
1380 ,p_order_line_id_tbl IN QP_PREQ_GRP.NUMBER_TYPE := G_EMPTY_NUMBER_TYPE
1381 ,p_pricing_events IN VARCHAR2
1382 ,p_calculate_flag IN VARCHAR2
1383 ,p_doc_sub_type IN VARCHAR2
1384 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1385 ,p_pricing_call_src IN VARCHAR2
1386 --To fix price override not allowed error
1387 ,p_allow_price_override_flag IN VARCHAR2
1388 ,x_return_status OUT NOCOPY VARCHAR2
1389 )
1390 IS
1391 --
1392 l_api_name CONSTANT varchar2(30) := 'extract_price_adjustments';
1393 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
1394 l_progress VARCHAR2(4) := '000';
1395 l_exception_msg FND_NEW_MESSAGES.message_text%TYPE;
1396
1397 i PLS_INTEGER;
1398
1399 l_debug_upd_line_adj_tbl NUMBER_TYPE;
1400
1401 CURSOR upd_line_det_cur IS
1402 SELECT ADJ.price_adjustment_id
1403 ,LDUP.line_detail_index
1404 FROM QP_LDETS_V LDUP
1405 ,QP_PREQ_LINES_TMP QLUP
1406 ,PO_PRICE_ADJUSTMENTS_DRAFT ADJ
1407 WHERE LDUP.process_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
1408 AND LDUP.list_line_id = ADJ.list_line_id
1409 AND ADJ.po_line_id = QLUP.line_id
1410 AND ADJ.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1411 AND QLUP.line_index = LDUP.line_index
1412 AND QLUP.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_UPDATED) --QP_PREQ_GRP.G_STATUS_NEW
1413 AND QLUP.process_status <> 'NOT_VALID'
1414 AND QLUP.line_type_code = 'LINE';
1415
1416 --
1417 BEGIN
1418 SAVEPOINT EXTRACT_PRICE_ADJUSTMENTS;
1419 x_return_status := FND_API.G_RET_STS_SUCCESS;
1420 l_progress := '000';
1421
1422 --Check if order_header_id or line_ids are passed
1423 IF (p_order_header_id IS NULL OR p_order_line_id_tbl IS NULL OR p_order_line_id_tbl.count <= 0) THEN
1424 RETURN;
1425 END IF;
1426
1427 l_progress := '010';
1428 IF g_debug_stmt THEN
1429 PO_DEBUG.debug_begin(l_log_head);
1430 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_header_id',p_order_header_id);
1431
1432 FOR i IN p_order_line_id_tbl.FIRST..p_order_line_id_tbl.LAST
1433 LOOP
1434 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_line_id_tbl('||i||')',p_order_line_id_tbl(i));
1435 END LOOP;
1436 PO_DEBUG.debug_var(l_log_head,l_progress,'p_pricing_events',p_pricing_events);
1437 PO_DEBUG.debug_var(l_log_head,l_progress,'p_calculate_flag',p_calculate_flag);
1438 END IF;
1439
1440
1441 l_progress := '020';
1442 IF g_debug_stmt THEN
1443 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Before extracting adjustments from QP temp tables');
1444 END IF;
1445
1446 --Sync PO PRICE ADJUSTMENTS table with PO PRICE ADJUSTMENTS DRAFT
1447 FOR i IN p_order_line_id_tbl.FIRST .. p_order_line_id_tbl.LAST
1448 LOOP
1449 PO_PRICE_ADJ_DRAFT_PKG.sync_draft_from_txn
1450 ( p_draft_id => p_draft_id
1451 , p_order_header_id => p_order_header_id
1452 , p_order_line_id => p_order_line_id_tbl(i)
1453 , p_delete_flag => NULL);
1454 END LOOP;
1455
1456 IF NVL(p_pricing_events, 'PO_BATCH') = 'PO_BATCH' THEN
1457 l_progress := '030';
1458 IF g_debug_stmt THEN
1459 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Extract adjustments from QP temp tables');
1460 END IF;
1461
1462 IF (p_calculate_flag <> QP_PREQ_GRP.G_CALCULATE_ONLY) THEN
1463 l_progress := '040';
1464 IF g_debug_stmt THEN
1465 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Delete outdated adjustments and dependant details');
1466 END IF;
1467 --Used progress code from 50 to 150
1468 delete_line_adjs
1469 (p_draft_id => p_draft_id
1470 ,p_order_header_id => p_order_header_id
1471 ,p_order_line_id_tbl => p_order_line_id_tbl
1472 ,p_pricing_events => p_pricing_events || ','
1473 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1474 ,p_pricing_call_src => p_pricing_call_src
1475 ,p_allow_price_override_flag => p_allow_price_override_flag
1476 ,p_log_head => l_log_head);
1477 END IF;
1478 END IF;
1479
1480 IF NVL(p_pricing_events, 'PO_BATCH') = 'PO_BATCH' THEN
1481 l_progress := '160';
1482 IF g_debug_stmt THEN
1483 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update PO Line Adjustment details');
1484 END IF;
1485 l_debug_upd_line_adj_tbl.delete;
1486 FOR upd_line_det IN upd_line_det_cur
1487 LOOP
1488 --Used progress code from 170 to 250
1489 update_adj
1490 (p_draft_id => p_draft_id
1491 ,p_price_adjustment_id => upd_line_det.price_adjustment_id
1492 ,p_line_detail_index => upd_line_det.line_detail_index
1493 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1494 ,p_pricing_call_src => p_pricing_call_src
1495 ,p_allow_price_override_flag => p_allow_price_override_flag
1496 ,px_debug_upd_adj_tbl => l_debug_upd_line_adj_tbl
1497 ,p_log_head => l_log_head);
1498 l_progress := '260';
1499 IF g_debug_stmt THEN
1500 PO_DEBUG.debug_stmt(l_log_head,l_progress,'UPDATED '|| SQL%ROWCOUNT ||' LINE LEVEL ADJUSTMENTS');
1501 END IF;
1502 END LOOP;
1503
1504 IF (p_calculate_flag <> QP_PREQ_GRP.G_CALCULATE_ONLY) THEN
1505 --Insert new Adjustments into PO ADJUSTMENTS table
1506 --Used Progress code from 270 to 350
1507 insert_adj
1508 (p_draft_id => p_draft_id
1509 ,p_order_header_id => p_order_header_id
1510 ,p_doc_sub_type => p_doc_sub_type
1511 ,p_log_head => l_log_head);
1512
1513 --Delete outdated attributes and Insert new attributes
1514 --Used Progress code from 370 to 450
1515 update_adj_attribs
1516 (p_draft_id => p_draft_id
1517 ,p_order_header_id => p_order_header_id
1518 ,p_pricing_events => p_pricing_events || ','
1519 ,p_log_head => l_log_head);
1520
1521
1522 END IF;
1523
1524 END IF;
1525
1526 --Merge PO PRICE ADJUSTMENTS DRAFT table with PO PRICE ADJUSTMENTS for retro calls
1527 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1528 IF (NVL(p_pricing_call_src, 'NULL') = 'RETRO' OR NVL(p_pricing_call_src, 'NULL') = 'AUTO') THEN
1529 --Merge Draft table with Base table
1530 PO_PRICE_ADJ_DRAFT_PKG.merge_changes( p_draft_id => p_draft_id );
1531 --Delete draft records
1532 PO_PRICE_ADJ_DRAFT_PKG.delete_rows
1533 (p_draft_id => p_draft_id
1534 ,p_price_adjustment_id => NULL);
1535 END IF;
1536 --
1537 EXCEPTION
1538 WHEN FND_API.G_EXC_ERROR THEN
1539 x_return_status := FND_API.G_RET_STS_ERROR;
1540 IF g_debug_unexp THEN
1541 PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING EXTRACT_PRICE_ADJUSTMENTS WITH EXC ERROR with rollback');
1542 END IF;
1543 ROLLBACK TO SAVEPOINT EXTRACT_PRICE_ADJUSTMENTS;
1544 RAISE FND_API.G_EXC_ERROR;
1545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1547 IF g_debug_unexp THEN
1548 PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING EXTRACT_PRICE_ADJUSTMENTS WITH UNEXPECTED ERROR with rollback');
1549 END IF;
1550 ROLLBACK TO SAVEPOINT EXTRACT_PRICE_ADJUSTMENTS;
1551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1552 WHEN OTHERS THEN
1553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1554 IF g_debug_unexp THEN
1555 PO_DEBUG.debug_stmt(l_log_head,l_progress,'UnExpected ERROR IN EXTRACT_PRICE_ADJUSTMENTS. SQLERRM at '||l_progress||': '||SQLERRM);
1556 END IF;
1557
1558 IF g_debug_unexp THEN
1559 PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING PO_PRICE_ADJUSTMENTS_PKG.EXTRACT_PRICE_ADJUSTMENTS with rollback');
1560 END IF;
1561 ROLLBACK TO SAVEPOINT EXTRACT_PRICE_ADJUSTMENTS;
1562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1563 END extract_price_adjustments;
1564
1565
1566 PROCEDURE delete_line_adjs
1567 (p_draft_id IN NUMBER
1568 ,p_order_header_id IN NUMBER
1569 ,p_order_line_id_tbl IN QP_PREQ_GRP.NUMBER_TYPE
1570 ,p_pricing_events IN VARCHAR2
1571 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1572 ,p_pricing_call_src IN VARCHAR2
1573 ,p_allow_price_override_flag IN VARCHAR2
1574 ,p_log_head IN VARCHAR2)
1575 IS
1576 --
1577 l_line_index NUMBER;
1578 l_line_id NUMBER;
1579 l_price_flag VARCHAR2(1);
1580 l_progress VARCHAR2(4) := '50';
1581
1582 i PLS_INTEGER;
1583 j PLS_INTEGER;
1584 l_adj_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
1585 BEGIN
1586 IF g_debug_stmt THEN
1587 PO_DEBUG.debug_stmt(p_log_head,l_progress,'Delete old line adjustments for lines with pricing_status_code UPDATED');
1588 END IF;
1589
1590 FOR j IN p_order_line_id_tbl.FIRST .. p_order_line_id_tbl.LAST
1591 LOOP
1592 l_progress := '60';
1593 IF g_debug_stmt THEN
1594 PO_DEBUG.debug_stmt(p_log_head,l_progress,'Check if PO Line Id: '||p_order_line_id_tbl(j)||' is UPDATED by pricing engine');
1595 END IF;
1596 BEGIN
1597 SELECT QLINE.line_id, QLINE.price_flag, QLINE.line_index
1598 INTO l_line_id, l_price_flag, l_line_index
1599 FROM QP_PREQ_LINES_TMP QLINE
1600 WHERE QLINE.line_id = p_order_line_id_tbl(j)
1601 AND QLINE.line_type_code = 'LINE'
1602 AND QLINE.price_flag IN ('Y')
1603 AND QLINE.process_status <> 'NOT_VALID'
1604 AND QLINE.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED);
1605 EXCEPTION
1606 WHEN NO_DATA_FOUND THEN
1607 l_line_id := NULL;
1608 l_price_flag := NULL;
1609 END;
1610
1611 IF l_line_id IS NULL THEN
1612 l_progress := '70';
1613 IF g_debug_stmt THEN
1614 PO_DEBUG.debug_stmt(p_log_head,l_progress,'The PO Line is not updated by pricing engine');
1615 END IF;
1616 ELSE
1617 --DELETE FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1618 UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1619 SET ADJD.delete_flag = 'Y'
1620 WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1621 AND ADJD.po_header_id = p_order_header_id
1622 AND ADJD.po_line_id = l_line_id
1623 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1624 AND DECODE(NVL(p_pricing_call_src,'NULL'), 'RETRO', 'N',
1625 DECODE(NVL(p_allow_price_override_flag,'Y'), 'Y', NVL(ADJD.updated_flag, 'N'), 'N')) = 'N' --to avoid deleting overridden-automatic and manual modifiers in normal mode
1626 --AND ADJD.pricing_phase_id IN (SELECT QPP.pricing_phase_id --this condition has to be checked for pass and fail scenarios, right now it fails for most of the cases
1627 -- FROM qp_event_phases QEP
1628 -- ,qp_pricing_phases QPP
1629 -- WHERE instr(p_pricing_events, QEP.pricing_event_code || ',') > 0
1630 -- AND QPP.pricing_phase_id = QEP.pricing_phase_id
1631 -- AND NVL(QPP.user_freeze_override_flag, QPP.freeze_override_flag)
1632 -- = decode(l_price_flag, 'Y', nvl(QPP.user_freeze_override_flag, QPP.freeze_override_flag), 'P', 'Y'))
1633 AND ADJD.list_line_id NOT IN (SELECT LD.list_line_id
1634 FROM qp_ldets_v LD
1635 WHERE LD.line_index = l_line_index
1636 AND LD.process_code IN (QP_PREQ_GRP.G_STATUS_UPDATED
1637 ,QP_PREQ_GRP.G_STATUS_UNCHANGED
1638 ,QP_PREQ_GRP.G_STATUS_NEW)
1639 AND (LD.applied_flag = 'Y'
1640 OR
1641 (nvl(ld.applied_flag, 'N') = 'N'
1642 AND
1643 nvl(ld.line_detail_type_code, 'x') = 'CHILD_DETAIL_LINE'
1644 )
1645 )
1646 )
1647 RETURNING ADJD.price_adjustment_id BULK COLLECT INTO l_adj_id_tbl;
1648
1649 l_progress := '80';
1650 IF g_debug_stmt THEN
1651 PO_DEBUG.debug_stmt(p_log_head,l_progress,'UPDATED '|| SQL%ROWCOUNT ||' LINE LEVEL ADJUSTMENTS WITH DELETE FLAG');
1652 END IF;
1653
1654 l_progress := '90';
1655 IF g_debug_stmt THEN
1656 PO_DEBUG.debug_stmt(p_log_head,l_progress,'Mark child adjustment records for deletion');
1657 END IF;
1658
1659 --Only child adjustments are marked for deletion, no need to mark the attributes for deletion. The attributes are deleted along with the adjustments
1660 IF l_adj_id_tbl.count > 0 THEN
1661 FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
1662 UPDATE PO_PRICE_ADJUSTMENTS_DRAFT
1663 SET delete_flag = 'Y'
1664 WHERE draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1665 AND parent_adjustment_id = l_adj_id_tbl(i);
1666
1667 l_progress := '100';
1668 IF g_debug_stmt THEN
1669 PO_DEBUG.debug_stmt(p_log_head,l_progress,'UPDATED '|| SQL%ROWCOUNT ||' CHILD LINES WITH DELETE FLAG');
1670 END IF;
1671 END IF;
1672 /*
1673 IF l_adj_id_tbl.count > 0 THEN
1674 FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
1675 DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT WHERE draft_id = p_draft_id AND price_adjustment_id = l_adj_id_tbl(i);
1676 l_progress := '100';
1677 IF g_debug_stmt THEN
1678 PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' ATTRIBS');
1679 END IF;
1680
1681 FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
1682 DELETE FROM PO_PRICE_ADJ_ASSOCS_DRAFT WHERE draft_id = p_draft_id AND price_adjustment_id = l_adj_id_tbl(i);
1683 l_progress := '110';
1684 IF g_debug_stmt THEN
1685 PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' ASSOCS');
1686 END IF;
1687
1688 FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
1689 DELETE FROM PO_PRICE_ADJ_ASSOCS_DRAFT WHERE draft_id = p_draft_id AND rltd_price_adj_id = l_adj_id_tbl(i);
1690 l_progress := '120';
1691 IF g_debug_stmt THEN
1692 PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' RLTD ASSOCS');
1693 END IF;
1694 END IF;
1695 */
1696 END IF;
1697 END LOOP;
1698 END delete_line_adjs;
1699
1700 PROCEDURE update_adj(p_draft_id IN NUMBER
1701 ,p_price_adjustment_id IN NUMBER
1702 ,p_line_detail_index IN NUMBER
1703 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1704 ,p_pricing_call_src IN VARCHAR2
1705 ,p_allow_price_override_flag IN VARCHAR2
1706 ,px_debug_upd_adj_tbl OUT NOCOPY NUMBER_TYPE
1707 ,p_log_head IN VARCHAR2)
1708 IS
1709 l_progress VARCHAR2(4) := '170';
1710 l_price_adjustment_id NUMBER;
1711
1712 BEGIN
1713
1714 IF g_debug_stmt THEN
1715 PO_DEBUG.debug_stmt(p_log_head,l_progress,'Update PO Line Adjustment details for');
1716 PO_DEBUG.debug_var(p_log_head,l_progress,'p_draft_id',p_draft_id);
1717 PO_DEBUG.debug_var(p_log_head,l_progress,'p_price_adjustment_id',p_price_adjustment_id);
1718 PO_DEBUG.debug_var(p_log_head,l_progress,'p_line_detail_index',p_line_detail_index);
1719 END IF;
1720
1721 BEGIN
1722 SELECT ADJ.price_adjustment_id
1723 INTO l_price_adjustment_id
1724 FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
1725 WHERE ADJ.draft_id = p_draft_id
1726 AND ADJ.price_adjustment_id = p_price_adjustment_id
1727 FOR UPDATE NOWAIT;
1728
1729 l_progress := '180';
1730 IF g_debug_stmt THEN
1731 PO_DEBUG.debug_stmt(p_log_head,l_progress,'Adjustment row successfully locked');
1732 END IF;
1733 EXCEPTION
1734 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1735 l_progress := '190';
1736 IF g_debug_stmt THEN
1737 PO_DEBUG.debug_stmt(p_log_head,l_progress,'in lock record exception, someone else working on the row');
1738 END IF;
1739 --FND_MESSAGE.SET_NAME('ONT', 'PO_LOCK_ROW_ALREADY_LOCKED');
1740 --PO_MSG_PUB.Add;
1741 RAISE FND_API.G_EXC_ERROR;
1742 WHEN NO_DATA_FOUND THEN
1743 l_progress := '200';
1744 IF g_debug_stmt THEN
1745 PO_DEBUG.debug_stmt(p_log_head,l_progress,'no_data_found, record lock exception');
1746 END IF;
1747 WHEN OTHERS THEN
1748 l_progress := '210';
1749 IF g_debug_stmt THEN
1750 PO_DEBUG.debug_stmt(p_log_head,l_progress,'record lock exception, others');
1751 END IF;
1752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1753 END;
1754
1755 UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1756 SET ( LAST_UPDATE_DATE
1757 , LAST_UPDATED_BY
1758 , LAST_UPDATE_LOGIN
1759 , operand
1760 , operand_per_pqty
1761 , adjusted_amount
1762 , adjusted_amount_per_pqty
1763 , arithmetic_operator
1764 , pricing_phase_id
1765 , pricing_group_sequence
1766 , automatic_flag
1767 , list_line_type_code
1768 , applied_flag
1769 , update_allowed
1770 --, updated_flag
1771 , charge_type_code
1772 , charge_subtype_code
1773 , range_break_quantity
1774 , accrual_conversion_rate
1775 , accrual_flag
1776 , list_line_no
1777 , print_on_invoice_flag
1778 , expiration_date
1779 , rebate_transaction_type_code
1780 , modifier_level_code
1781 , price_break_type_code
1782 , include_on_returns_flag
1783 , lock_control
1784 )
1785 =
1786 (SELECT
1787 SYSDATE -- LAST_UPDATE_DATE
1788 , FND_GLOBAL.user_id -- LAST_UPDATED_BY
1789 , FND_GLOBAL.login_id -- LAST_UPDATE_LOGIN
1790 , LDETS.order_qty_operand
1791 , LDETS.operand_value
1792 , LDETS.order_qty_adj_amt
1793 , LDETS.adjustment_amount
1794 , LDETS.operand_calculation_code
1795 , LDETS.pricing_phase_id
1796 , LDETS.pricing_group_sequence
1797 , LDETS.automatic_flag
1798 , LDETS.list_line_type_code
1799 , LDETS.applied_flag
1800 , LDETS.override_flag
1801 --, LDETS.updated_flag
1802 , LDETS.charge_type_code
1803 , LDETS.charge_subtype_code
1804 , LDETS.line_quantity --range_break_quantity
1805 , LDETS.accrual_conversion_rate
1806 , LDETS.accrual_flag
1807 , LDETS.list_line_no
1808 , LDETS.print_on_invoice_flag
1809 , LDETS.expiration_date
1810 , LDETS.rebate_transaction_type_code
1811 , LDETS.modifier_level_code
1812 , LDETS.price_break_type_code
1813 , LDETS.include_on_returns_flag
1814 , ADJD.lock_control + 1
1815 FROM QP_LDETS_v LDETS
1816 WHERE ldets.line_detail_index = p_line_detail_index
1817 )
1818 WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1819 AND ADJD.price_adjustment_id = p_price_adjustment_id
1820 RETURNING ADJD.list_line_id BULK COLLECT INTO px_debug_upd_adj_tbl;
1821
1822 --reset the columns used to identify the overridden modifier
1823 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
1824 IF NVL(p_pricing_call_src, 'NULL') = 'RETRO' OR NVL(p_allow_price_override_flag, 'Y') <> 'Y' THEN
1825 UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJD
1826 SET ADJD.updated_flag = 'N',
1827 ADJD.change_reason_code = null,
1828 ADJD.change_reason_text = null
1829 WHERE ADJD.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
1830 AND ADJD.price_adjustment_id = p_price_adjustment_id;
1831 END IF;
1832
1833 l_progress := '210';
1834 IF g_debug_stmt THEN
1835 PO_DEBUG.debug_stmt(p_log_head,l_progress,'exiting update_adj procedure');
1836 END IF;
1837 END update_adj;
1838
1839
1840 PROCEDURE insert_adj(p_draft_id IN NUMBER
1841 ,p_order_header_id IN NUMBER
1842 ,p_doc_sub_type IN VARCHAR2
1843 ,p_log_head IN VARCHAR2)
1844 IS
1845 --
1846 l_progress VARCHAR2(4) := '270';
1847 l_price_adjustment_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
1848 l_rltd_price_adj_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
1849 i PLS_INTEGER;
1850 BEGIN
1851
1852 IF g_debug_stmt THEN
1853 PO_DEBUG.debug_stmt(p_log_head,l_progress,'Insert Adjustments called with Order Header ID: '||p_order_header_id);
1854 END IF;
1855
1856 INSERT INTO PO_PRICE_ADJUSTMENTS_DRAFT
1857 (DRAFT_ID
1858 , PRICE_ADJUSTMENT_ID
1859 , ADJ_LINE_NUM
1860 , CREATION_DATE
1861 , CREATED_BY
1862 , LAST_UPDATE_DATE
1863 , LAST_UPDATED_BY
1864 , LAST_UPDATE_LOGIN
1865 , PROGRAM_APPLICATION_ID
1866 , PROGRAM_ID
1867 , PROGRAM_UPDATE_DATE
1868 , REQUEST_ID
1869 , PO_HEADER_ID
1870 , AUTOMATIC_FLAG
1871 , PO_LINE_ID
1872 , CONTEXT
1873 , ATTRIBUTE1
1874 , ATTRIBUTE2
1875 , ATTRIBUTE3
1876 , ATTRIBUTE4
1877 , ATTRIBUTE5
1878 , ATTRIBUTE6
1879 , ATTRIBUTE7
1880 , ATTRIBUTE8
1881 , ATTRIBUTE9
1882 , ATTRIBUTE10
1883 , ATTRIBUTE11
1884 , ATTRIBUTE12
1885 , ATTRIBUTE13
1886 , ATTRIBUTE14
1887 , ATTRIBUTE15
1888 , ORIG_SYS_DISCOUNT_REF
1889 , LIST_HEADER_ID
1890 , LIST_LINE_ID
1891 , LIST_LINE_TYPE_CODE
1892 , MODIFIED_FROM
1893 , MODIFIED_TO
1894 , UPDATED_FLAG
1895 , UPDATE_ALLOWED
1896 , APPLIED_FLAG
1897 , CHANGE_REASON_CODE
1898 , CHANGE_REASON_TEXT
1899 , operand
1900 , Arithmetic_operator
1901 , COST_ID
1902 , TAX_CODE
1903 , TAX_EXEMPT_FLAG
1904 , TAX_EXEMPT_NUMBER
1905 , TAX_EXEMPT_REASON_CODE
1906 , PARENT_ADJUSTMENT_ID
1907 , INVOICED_FLAG
1908 , ESTIMATED_FLAG
1909 , INC_IN_SALES_PERFORMANCE
1910 , ADJUSTED_AMOUNT
1911 , PRICING_PHASE_ID
1912 , CHARGE_TYPE_CODE
1913 , CHARGE_SUBTYPE_CODE
1914 , list_line_no
1915 , source_system_code
1916 , benefit_qty
1917 , benefit_uom_code
1918 , print_on_invoice_flag
1919 , expiration_date
1920 , rebate_transaction_type_code
1921 , rebate_transaction_reference
1922 , rebate_payment_system_code
1923 , redeemed_date
1924 , redeemed_flag
1925 , accrual_flag
1926 , range_break_quantity
1927 , accrual_conversion_rate
1928 , pricing_group_sequence
1929 , modifier_level_code
1930 , price_break_type_code
1931 , substitution_attribute
1932 , proration_type_code
1933 , CREDIT_OR_CHARGE_FLAG
1934 , INCLUDE_ON_RETURNS_FLAG
1935 , AC_CONTEXT
1936 , AC_ATTRIBUTE1
1937 , AC_ATTRIBUTE2
1938 , AC_ATTRIBUTE3
1939 , AC_ATTRIBUTE4
1940 , AC_ATTRIBUTE5
1941 , AC_ATTRIBUTE6
1942 , AC_ATTRIBUTE7
1943 , AC_ATTRIBUTE8
1944 , AC_ATTRIBUTE9
1945 , AC_ATTRIBUTE10
1946 , AC_ATTRIBUTE11
1947 , AC_ATTRIBUTE12
1948 , AC_ATTRIBUTE13
1949 , AC_ATTRIBUTE14
1950 , AC_ATTRIBUTE15
1951 , OPERAND_PER_PQTY
1952 , ADJUSTED_AMOUNT_PER_PQTY
1953 , LOCK_CONTROL
1954 )
1955 (SELECT
1956 p_draft_id --DRAFT_ID
1957 , po_price_adjustments_s.nextval -- PRICE_ADJUSTMENT_ID
1958 , LDETS.line_detail_index --ADJ_LINE_NUM
1959 , SYSDATE -- CREATION_DATE
1960 , FND_GLOBAL.user_id -- CREATED_BY
1961 , SYSDATE -- LAST_UPDATE_DATE
1962 , FND_GLOBAL.user_id -- LAST_UPDATED_BY
1963 , FND_GLOBAL.login_id -- LAST_UPDATE_LOGIN
1964 , NULL -- PROGRAM_APPLICATION_ID
1965 , NULL -- PROGRAM_ID
1966 , NULL -- PROGRAM_UPDATE_DATE
1967 , NULL -- REQUEST_ID
1968 , p_order_header_id -- HEADER_ID
1969 , LDETS.automatic_flag -- AUTOMATIC_FLAG
1970 , QLINE.line_id -- ORDER_LINE_ID
1971 , NULL -- CONTEXT
1972 , NULL -- ATTRIBUTE1
1973 , NULL -- ATTRIBUTE2
1974 , NULL -- ATTRIBUTE3
1975 , NULL -- ATTRIBUTE4
1976 , NULL -- ATTRIBUTE5
1977 , NULL -- ATTRIBUTE6
1978 , NULL -- ATTRIBUTE7
1979 , NULL -- ATTRIBUTE8
1980 , NULL -- ATTRIBUTE9
1981 , NULL -- ATTRIBUTE10
1982 , NULL -- ATTRIBUTE11
1983 , NULL -- ATTRIBUTE12
1984 , NULL -- ATTRIBUTE13
1985 , NULL -- ATTRIBUTE14
1986 , NULL -- ATTRIBUTE15
1987 , NULL -- ORIG_SYS_DISCOUNT_REF
1988 , LDETS.LIST_HEADER_ID -- LIST_HEADER_ID
1989 , LDETS.LIST_LINE_ID -- LIST_LINE_ID
1990 , LDETS.LIST_LINE_TYPE_CODE -- LIST_LINE_TYPE_CODE
1991 , NULL -- MODIFIED FROM
1992 , NULL -- MODIFIED_TO
1993 , LDETS.updated_flag -- UPDATED_FLAG
1994 , LDETS.override_flag -- UPDATE_ALLOWED
1995 , LDETS.applied_flag -- APPLIED_FLAG
1996 , NULL -- CHANGE_REASON_CODE
1997 , NULL -- CHANGE_REASON_TEXT
1998 , nvl(ldets.order_qty_operand, decode(ldets.operand_calculation_code
1999 ,'%', ldets.operand_value
2000 ,'LUMPSUM', ldets.operand_value
2001 ,ldets.operand_value * qline.priced_quantity / nvl(qline.line_quantity, 1)))
2002 --OPERAND
2003 , ldets.operand_calculation_code -- ARITHMETIC_OPERATOR
2004 , NULL -- COST_ID
2005 , NULL -- TAX_CODE
2006 , NULL -- TAX_EXEMPT_FLAG
2007 , NULL -- TAX_EXEMPT_NUMBER
2008 , NULL -- TAX_EXEMPT_REASON_CODE
2009 , NULL -- PARENT_ADJUSTMENT_ID
2010 , NULL -- INVOICED_FLAG
2011 , NULL -- ESTIMATED_FLAG
2012 , NULL -- INC_IN_SALES_PERFORMANCE
2013 , nvl(ldets.order_qty_adj_amt, ldets.adjustment_amount * nvl(qline.priced_quantity, 1) / nvl(qline.line_quantity, 1))
2014 -- ADJUSTED_AMOUNT
2015 , LDETS.pricing_phase_id -- PRICING_PHASE_ID
2016 , LDETS.charge_type_code -- CHARGE_TYPE_CODE
2017 , LDETS.charge_subtype_code -- CHARGE_SUBTYPE_CODE
2018 , LDETS.list_line_no -- LIST_LINE_NO
2019 , QH.source_system_code||' - '
2020 ||p_doc_sub_type -- SOURCE_SYSTEM_CODE
2021 , NULL -- LDETS.benefit_qty
2022 , NULL -- LDETS.benefit_uom_code
2023 , NULL -- PRINT_ON_INVOICE_FLAG
2024 , LDETS.expiration_date -- EXPIRATION_DATE
2025 , LDETS.rebate_transaction_type_code
2026 , NULL -- REBATE_TRANSACTION_REFERENCE
2027 , NULL -- REBATE_PAYMENT_SYSTEM_CODE
2028 , NULL -- REDEEMED_DATE
2029 , NULL -- REDEEMED_FLAG
2030 , LDETS.accrual_flag -- ACCRUAL_FLAG
2031 , LDETS.line_quantity -- RANGE_BREAK_QUANTITY
2032 , LDETS.accrual_conversion_rate -- ACCRUAL_CONVERSION_RATE
2033 , LDETS.pricing_group_sequence -- PRICING_GROUP_SEQUENCE
2034 , LDETS.modifier_level_code -- MODIFIER_LEVEL_CODE
2035 , LDETS.price_break_type_code -- PRICE_BREAK_TYPE_CODE
2036 , NULL -- LDETS.SUBSTITUTION_ATTRIBUTE
2037 , NULL -- LDETS.PRORATION_TYPE_CODE
2038 , NULL -- CREDIT_OR_CHARGE_FLAG
2039 , LDETS.include_on_returns_flag -- INCLUDE_ON_RETURNS_FLAG
2040 , NULL -- AC_CONTEXT
2041 , NULL -- AC_ATTRIBUTE1
2042 , NULL -- AC_ATTRIBUTE2
2043 , NULL -- AC_ATTRIBUTE3
2044 , NULL -- AC_ATTRIBUTE4
2045 , NULL -- AC_ATTRIBUTE5
2046 , NULL -- AC_ATTRIBUTE6
2047 , NULL -- AC_ATTRIBUTE7
2048 , NULL -- AC_ATTRIBUTE8
2049 , NULL -- AC_ATTRIBUTE9
2050 , NULL -- AC_ATTRIBUTE10
2051 , NULL -- AC_ATTRIBUTE11
2052 , NULL -- AC_ATTRIBUTE12
2053 , NULL -- AC_ATTRIBUTE13
2054 , NULL -- AC_ATTRIBUTE14
2055 , NULL -- AC_ATTRIBUTE15
2056 , LDETS.operand_value -- OPERAND_PER_PQTY
2057 , LDETS.adjustment_amount -- ADJUSTED_AMOUNT_PER_PQTY
2058 , 1 -- LOCK_CONTROL
2059 FROM QP_LDETS_v LDETS
2060 , QP_PREQ_LINES_TMP QLINE
2061 , QP_LIST_HEADERS_B QH
2062 WHERE LDETS.list_header_id = QH.list_header_id
2063 AND LDETS.process_code = QP_PREQ_GRP.G_STATUS_NEW
2064 AND QLINE.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED)
2065 AND QLINE.process_status <> 'NOT_VALID'
2066 AND LDETS.line_index = QLINE.line_index
2067 AND (nvl(LDETS.automatic_flag, 'N') = 'Y')
2068 AND LDETS.created_from_list_type_code NOT IN ('PRL', 'AGR')
2069 AND LDETS.list_line_type_code <> 'PLL'
2070 AND LDETS.list_line_id NOT IN (SELECT ADJ.list_line_id
2071 FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2072 WHERE ADJ.list_line_id = LDETS.list_line_id
2073 AND ADJ.po_line_id = QLINE.line_id)
2074 );
2075 l_progress := '280';
2076 IF g_debug_stmt THEN
2077 PO_DEBUG.debug_stmt(p_log_head,l_progress,'INSERTED '|| SQL%ROWCOUNT ||' ADJUSTMENTS');
2078 END IF;
2079
2080 --New Parent Adjustment Id Logic Start
2081 SELECT ADJ.price_adjustment_id
2082 , RADJ.price_adjustment_id
2083 BULK COLLECT INTO l_price_adjustment_id_tbl
2084 ,l_rltd_price_adj_id_tbl
2085 FROM QP_PREQ_RLTD_LINES_TMP RLTD
2086 , QP_PREQ_LINES_TMP QPL
2087 , PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2088 , PO_PRICE_ADJUSTMENTS_DRAFT RADJ
2089 WHERE QPL.LINE_INDEX = RLTD.LINE_INDEX
2090 AND QPL.LINE_ID = ADJ.PO_LINE_ID
2091 AND ADJ.draft_id = p_draft_id
2092 AND QPL.LINE_TYPE_CODE = 'LINE'
2093 AND QPL.PROCESS_STATUS <> 'NOT_VALID'
2094 AND RLTD.LIST_LINE_ID = ADJ.LIST_LINE_ID
2095 AND RLTD.RELATED_LINE_INDEX = QPL.LINE_INDEX
2096 AND RLTD.RELATED_LIST_LINE_ID = RADJ.LIST_LINE_ID
2097 AND ADJ.PO_LINE_ID = RADJ.PO_LINE_ID
2098 AND ADJ.draft_id = RADJ.draft_id
2099 AND RLTD.PRICING_STATUS_CODE = QP_PREQ_GRP.G_STATUS_NEW;
2100
2101 --Update adjustment table with parent record
2102 FORALL i IN l_rltd_price_adj_id_tbl.FIRST..l_rltd_price_adj_id_tbl.LAST
2103 UPDATE PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2104 SET ADJ.parent_adjustment_id = l_price_adjustment_id_tbl(i)
2105 WHERE ADJ.price_adjustment_id = l_rltd_price_adj_id_tbl(i);
2106 --New Parent Adjustment Id Logic end
2107
2108 EXCEPTION
2109 WHEN OTHERS THEN
2110 l_progress := '300';
2111 IF g_debug_stmt THEN
2112 PO_DEBUG.debug_stmt(p_log_head,l_progress,'ERROR in inserting adjustments and associations');
2113 END IF;
2114 RAISE FND_API.G_EXC_ERROR;
2115 END insert_adj;
2116
2117 PROCEDURE update_adj_attribs(p_draft_id IN NUMBER
2118 ,p_order_header_id IN NUMBER
2119 ,p_pricing_events IN VARCHAR2
2120 ,p_log_head IN VARCHAR2)
2121 IS
2122 --
2123 l_adj_id_tbl NUMBER_TYPE;
2124 l_line_detail_index_tbl NUMBER_TYPE;
2125 i PLS_INTEGER;
2126 l_progress VARCHAR2(4):= '370';
2127
2128 CURSOR refresh_attribs_cur IS
2129 SELECT ADJ.price_adjustment_id, ldets.line_detail_index
2130 FROM QP_PREQ_LINES_TMP QPL
2131 , PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2132 , QP_LDETS_V LDETS
2133 WHERE LDETS.list_line_id = ADJ.list_line_id
2134 AND ADJ.draft_id = p_draft_id --For now draft_id in this place can never be null, but this condition needs to be changed or removed if null value is allowed in draft_id
2135 AND LDETS.line_index = QPL.line_index
2136 AND ADJ.pricing_phase_id IN (SELECT QEP.pricing_phase_id
2137 FROM qp_event_phases QEP
2138 WHERE instr(p_pricing_events, QEP.pricing_event_code || ',') > 0)
2139 AND LDETS.process_code IN (QP_PREQ_GRP.G_STATUS_UNCHANGED, QP_PREQ_GRP.G_STATUS_UPDATED, QP_PREQ_GRP.G_STATUS_NEW)
2140 AND nvl(ADJ.updated_flag, 'N') = 'N'
2141 AND QPL.line_id = ADJ.po_line_id
2142 AND QPL.process_status <> 'NOT_VALID'
2143 AND QPL.line_type_code = 'LINE'
2144 AND QPL.pricing_status_code IN (QP_PREQ_GRP.G_STATUS_NEW, QP_PREQ_GRP.G_STATUS_UPDATED);
2145 --
2146 BEGIN
2147 --
2148 IF g_debug_stmt THEN
2149 PO_DEBUG.debug_stmt(p_log_head,l_progress,'Update Adjustment Attributes called with the below parameters');
2150 PO_DEBUG.debug_var(p_log_head,l_progress,'p_order_header_id',p_order_header_id);
2151 PO_DEBUG.debug_var(p_log_head,l_progress,'p_pricing_events',p_pricing_events);
2152 END IF;
2153 l_adj_id_tbl.delete;
2154 l_line_detail_index_tbl.delete;
2155
2156 OPEN refresh_attribs_cur;
2157 FETCH refresh_attribs_cur BULK COLLECT INTO l_adj_id_tbl, l_line_detail_index_tbl;
2158
2159 IF l_adj_id_tbl.count > 0 THEN
2160 FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
2161 DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT WHERE draft_id = p_draft_id AND price_adjustment_id = l_adj_id_tbl(i)
2162 AND ( pricing_context
2163 , pricing_attribute
2164 , pricing_attr_value_from
2165 , pricing_attr_value_to
2166 )
2167 NOT IN
2168 (SELECT QPLAT.context
2169 , QPLAT.attribute
2170 , QPLAT.setup_value_from
2171 , QPLAT.setup_value_to
2172 FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
2173 WHERE QPLAT.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2174 AND QPLAT.line_detail_index = l_line_detail_index_tbl(i)
2175 );
2176
2177 l_progress := '380';
2178 IF g_debug_stmt THEN
2179 PO_DEBUG.debug_stmt(p_log_head,l_progress,'DELETED '|| SQL%ROWCOUNT ||' ATTRIBUTES');
2180 END IF;
2181
2182 FORALL i IN l_adj_id_tbl.FIRST..l_adj_id_tbl.LAST
2183 INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2184 (DRAFT_ID
2185 , PRICE_ADJUSTMENT_ID
2186 , PRICING_CONTEXT
2187 , PRICING_ATTRIBUTE
2188 , CREATION_DATE
2189 , CREATED_BY
2190 , LAST_UPDATE_DATE
2191 , LAST_UPDATED_BY
2192 , LAST_UPDATE_LOGIN
2193 , PROGRAM_APPLICATION_ID
2194 , PROGRAM_ID
2195 , PROGRAM_UPDATE_DATE
2196 , REQUEST_ID
2197 , PRICING_ATTR_VALUE_FROM
2198 , PRICING_ATTR_VALUE_TO
2199 , COMPARISON_OPERATOR
2200 , FLEX_TITLE
2201 , PRICE_ADJ_ATTRIB_ID
2202 , LOCK_CONTROL
2203 )
2204 (SELECT
2205 p_draft_id
2206 , l_adj_id_tbl(i) --ADJ.PRICE_ADJUSTMENT_ID
2207 , QPLAT.context
2208 , QPLAT.attribute
2209 , SYSDATE
2210 , fnd_global.user_id
2211 , SYSDATE
2212 , fnd_global.user_id
2213 , fnd_global.login_id
2214 , NULL
2215 , NULL
2216 , NULL
2217 , NULL
2218 , QPLAT.setup_value_from --VALUE_FROM
2219 , QPLAT.setup_value_to --VALUE_TO
2220 , QPLAT.comparison_operator_type_code
2221 , decode(QPLAT.attribute_type,
2222 'QUALIFIER', 'QP_ATTR_DEFNS_QUALIFIER',
2223 'QP_ATTR_DEFNS_PRICING')
2224 , PO_PRICE_ADJ_ATTRIBS_S.nextval
2225 , 1
2226 FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
2227 WHERE QPLAT.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2228 AND QPLAT.LINE_DETAIL_INDEX = l_line_detail_index_tbl(i)
2229 AND ( QPLAT.context
2230 , QPLAT.attribute
2231 , QPLAT.setup_value_from
2232 , QPLAT.setup_value_to
2233 )
2234 NOT IN
2235 (SELECT pricing_context
2236 , pricing_attribute
2237 , pricing_attr_value_from
2238 , pricing_attr_value_to
2239 FROM PO_PRICE_ADJ_ATTRIBS_DRAFT
2240 WHERE PRICE_ADJUSTMENT_ID = l_adj_id_tbl(i))
2241 );
2242
2243 l_progress := '390';
2244 IF g_debug_stmt THEN
2245 PO_DEBUG.debug_stmt(p_log_head,l_progress,'INSERTED '|| SQL%ROWCOUNT ||' CHANGED ATTRIBS');
2246 END IF;
2247 END IF;
2248 CLOSE refresh_attribs_cur;
2249 END update_adj_attribs;
2250
2251
2252 PROCEDURE insert_adj_attribs(p_draft_id IN NUMBER
2253 ,p_order_header_id IN NUMBER
2254 ,p_log_head IN VARCHAR2)
2255 IS
2256 --
2257 l_progress VARCHAR2(4) := '450';
2258 BEGIN
2259
2260 IF g_debug_stmt THEN
2261 PO_DEBUG.debug_stmt(p_log_head,l_progress,'Insert Adjustment Attributes called with the below parameters');
2262 PO_DEBUG.debug_var(p_log_head,l_progress,'p_draft_id',p_draft_id);
2263 PO_DEBUG.debug_var(p_log_head,l_progress,'p_order_header_id',p_order_header_id);
2264 END IF;
2265
2266 INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2267 (DRAFT_ID
2268 , PRICE_ADJUSTMENT_ID
2269 , PRICING_CONTEXT
2270 , PRICING_ATTRIBUTE
2271 , CREATION_DATE
2272 , CREATED_BY
2273 , LAST_UPDATE_DATE
2274 , LAST_UPDATED_BY
2275 , LAST_UPDATE_LOGIN
2276 , PROGRAM_APPLICATION_ID
2277 , PROGRAM_ID
2278 , PROGRAM_UPDATE_DATE
2279 , REQUEST_ID
2280 , PRICING_ATTR_VALUE_FROM
2281 , PRICING_ATTR_VALUE_TO
2282 , COMPARISON_OPERATOR
2283 , FLEX_TITLE
2284 , PRICE_ADJ_ATTRIB_ID
2285 , LOCK_CONTROL
2286 )
2287 (SELECT p_draft_id
2288 , ADJ.price_adjustment_id
2289 , QPLAT.context
2290 , QPLAT.attribute
2291 , SYSDATE
2292 , fnd_global.user_id
2293 , SYSDATE
2294 , fnd_global.user_id
2295 , fnd_global.login_id
2296 , NULL
2297 , NULL
2298 , NULL
2299 , NULL
2300 , QPLAT.setup_value_from --VALUE_FROM
2301 , QPLAT.setup_value_to --VALUE_TO
2302 , QPLAT.comparison_operator_type_code
2303 , decode(QPLAT.attribute_type,
2304 'QUALIFIER', 'QP_ATTR_DEFNS_QUALIFIER',
2305 'QP_ATTR_DEFNS_PRICING')
2306 , PO_PRICE_ADJ_ATTRIBS_S.nextval
2307 , 1
2308 FROM QP_PREQ_LINE_ATTRS_TMP QPLAT
2309 , QP_LDETS_v LDETS
2310 , PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2311 , QP_PREQ_LINES_TMP QPLINE
2312 WHERE ADJ.po_header_id = p_order_header_id
2313 AND QPLAT.pricing_status_code = QP_PREQ_PUB.G_STATUS_NEW
2314 AND QPLAT.line_detail_index = LDETS.line_detail_index
2315 AND QPLAT.line_index = LDETS.line_index
2316 AND LDETS.list_line_id = ADJ.list_line_id
2317 AND LDETS.process_code = QP_PREQ_PUB.G_STATUS_NEW
2318 AND LDETS.line_index = QPLINE.line_index
2319 AND QPLINE.line_id = ADJ.po_line_id
2320 AND QPLINE.line_type_code = 'LINE'
2321 AND QPLINE.pricing_status_code IN (QP_PREQ_PUB.G_STATUS_UPDATED)
2322 AND QPLINE.process_status <> 'NOT_VALID'
2323 );
2324 l_progress := '460';
2325 IF g_debug_stmt THEN
2326 PO_DEBUG.debug_stmt(p_log_head,l_progress,'INSERTED '|| SQL%ROWCOUNT ||' ATTRIBS');
2327 END IF;
2328 END insert_adj_attribs;
2329
2330 PROCEDURE complete_manual_mod_lov_map
2331 (p_draft_id IN NUMBER
2332 ,p_doc_sub_type IN VARCHAR2
2333 ,x_return_status_text OUT NOCOPY VARCHAR2
2334 ,x_return_status OUT NOCOPY VARCHAR2
2335 )
2336 IS
2337 --
2338 d_mod CONSTANT VARCHAR2(100) := D_complete_manual_mod_lov_map;
2339 d_position NUMBER := 0;
2340
2341 l_price_adjustment_id_tbl NUMBER_TYPE;
2342 BEGIN
2343 --
2344 d_position := 1;
2345 SAVEPOINT COMPLETE_MANUAL_MOD_LOV_MAP;
2346
2347 IF (PO_LOG.d_proc) THEN
2348 PO_LOG.proc_begin(d_mod);
2349 PO_LOG.proc_begin(d_mod, 'p_draft_id', p_draft_id);
2350 PO_LOG.proc_begin(d_mod, 'p_doc_sub_type', p_doc_sub_type);
2351 END IF;
2352
2353 x_return_status := FND_API.G_RET_STS_SUCCESS;
2354
2355 d_position := 100;
2356 IF PO_LOG.d_stmt THEN
2357 PO_LOG.stmt(d_mod, d_position, 'Get newly added manual modifier lines');
2358 END IF;
2359
2360 SELECT price_adjustment_id
2361 BULK COLLECT INTO l_price_adjustment_id_tbl
2362 FROM po_price_adjustments_draft ADJD
2363 WHERE ADJD.draft_id = p_draft_id
2364 AND ADJD.applied_flag IS NULL --this check is needed to ensure if the manual modifier is newly added
2365 AND NVL(ADJD.automatic_flag, 'N') = 'N' --line is not automatic modifier
2366 AND NVL(ADJD.delete_flag, 'N') = 'N' --line is not marked for deletion
2367 AND NVL(ADJD.change_accepted_flag, 'Y') = 'Y'; --change is accepted for the line. Do we need this condition??
2368
2369 d_position := 120;
2370 IF PO_LOG.d_stmt THEN
2371 PO_LOG.stmt(d_mod, d_position, 'Populate PO_PRICE_ADJ_ATTRIBS table with pricing attribute details');
2372 END IF;
2373
2374 FORALL i IN l_price_adjustment_id_tbl.FIRST .. l_price_adjustment_id_tbl.LAST
2375 INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2376 (DRAFT_ID
2377 , PRICE_ADJUSTMENT_ID
2378 , PRICING_CONTEXT
2379 , PRICING_ATTRIBUTE
2380 , CREATION_DATE
2381 , CREATED_BY
2382 , LAST_UPDATE_DATE
2383 , LAST_UPDATED_BY
2384 , LAST_UPDATE_LOGIN
2385 , PROGRAM_APPLICATION_ID
2386 , PROGRAM_ID
2387 , PROGRAM_UPDATE_DATE
2388 , REQUEST_ID
2389 , PRICING_ATTR_VALUE_FROM
2390 , PRICING_ATTR_VALUE_TO
2391 , COMPARISON_OPERATOR
2392 , FLEX_TITLE
2393 , PRICE_ADJ_ATTRIB_ID
2394 , LOCK_CONTROL
2395 )
2396 (SELECT p_draft_id
2397 , ADJD.price_adjustment_id
2398 , PRA.pricing_attribute_context
2399 , PRA.pricing_attribute
2400 , SYSDATE
2401 , fnd_global.user_id
2402 , SYSDATE
2403 , fnd_global.user_id
2404 , fnd_global.login_id
2405 , NULL
2406 , NULL
2407 , NULL
2408 , NULL
2409 , PRA.pricing_attr_value_from --VALUE_FROM
2410 , PRA.pricing_attr_value_to --VALUE_TO
2411 , PRA.comparison_operator_code
2412 , 'QP_ATTR_DEFNS_PRICING' --FLEX_TITLE
2413 , PO_PRICE_ADJ_ATTRIBS_S.nextval
2414 , 1
2415 FROM QP_PRICING_ATTRIBUTES PRA
2416 ,PO_PRICE_ADJUSTMENTS_DRAFT ADJD
2417 WHERE ADJD.draft_id = p_draft_id
2418 AND ADJD.price_adjustment_id = l_price_adjustment_id_tbl(i)
2419 AND ADJD.list_header_id = PRA.list_header_id
2420 AND ADJD.list_line_id = PRA.list_line_id
2421 AND PRA.pricing_attribute_context IS NOT NULL --only pricing attributes are picked, also to avoid product attributes
2422 AND PRA.pricing_attr_value_from IS NOT NULL); --pricing attr with from value should not be null
2423
2424 d_position := 140;
2425 IF PO_LOG.d_stmt THEN
2426 PO_LOG.stmt(d_mod, d_position, 'INSERTED '|| SQL%ROWCOUNT ||' PRICING PRICE ADJ ATTRIBS');
2427 END IF;
2428
2429 d_position := 160;
2430 IF PO_LOG.d_stmt THEN
2431 PO_LOG.stmt(d_mod, d_position, 'Populate PO_PRICE_ADJ_ATTRIBS table with qualifier attribute details');
2432 END IF;
2433
2434 FORALL i IN l_price_adjustment_id_tbl.FIRST .. l_price_adjustment_id_tbl.LAST
2435 INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
2436 (DRAFT_ID
2437 , PRICE_ADJUSTMENT_ID
2438 , PRICING_CONTEXT
2439 , PRICING_ATTRIBUTE
2440 , CREATION_DATE
2441 , CREATED_BY
2442 , LAST_UPDATE_DATE
2443 , LAST_UPDATED_BY
2444 , LAST_UPDATE_LOGIN
2445 , PROGRAM_APPLICATION_ID
2446 , PROGRAM_ID
2447 , PROGRAM_UPDATE_DATE
2448 , REQUEST_ID
2449 , PRICING_ATTR_VALUE_FROM
2450 , PRICING_ATTR_VALUE_TO
2451 , COMPARISON_OPERATOR
2452 , FLEX_TITLE
2453 , PRICE_ADJ_ATTRIB_ID
2454 , LOCK_CONTROL
2455 )
2456 (SELECT p_draft_id
2457 , ADJD.price_adjustment_id
2458 , QUAL.qualifier_context
2459 , QUAL.qualifier_attribute
2460 , SYSDATE
2461 , fnd_global.user_id
2462 , SYSDATE
2463 , fnd_global.user_id
2464 , fnd_global.login_id
2465 , NULL
2466 , NULL
2467 , NULL
2468 , NULL
2469 , QUAL.qualifier_attr_value --VALUE_FROM
2470 , QUAL.qualifier_attr_value_to --VALUE_TO
2471 , QUAL.comparison_operator_code
2472 , 'QP_ATTR_DEFNS_QUALIFIER' --FLEX_TITLE
2473 , PO_PRICE_ADJ_ATTRIBS_S.nextval
2474 , 1
2475 FROM QP_QUALIFIERS QUAL
2476 ,PO_PRICE_ADJUSTMENTS_DRAFT ADJD
2477 WHERE ADJD.draft_id = p_draft_id
2478 AND ADJD.price_adjustment_id = l_price_adjustment_id_tbl(i)
2479 AND ADJD.list_header_id = QUAL.list_header_id
2480 AND ADJD.list_line_id = QUAL.list_line_id
2481 AND QUAL.qualifier_attr_value IS NOT NULL); --qualifier attr with from value should not be null
2482
2483 d_position := 180;
2484 IF PO_LOG.d_stmt THEN
2485 PO_LOG.stmt(d_mod, d_position, 'INSERTED '|| SQL%ROWCOUNT ||' QUALIFIER PRICE ADJ ATTRIBS');
2486 END IF;
2487
2488 d_position := 200;
2489 IF PO_LOG.d_proc THEN
2490 PO_LOG.proc_end(d_mod, 'x_return_status_text', x_return_status_text);
2491 PO_LOG.proc_end(d_mod, 'x_return_status', x_return_status);
2492 END IF;
2493
2494 EXCEPTION
2495 WHEN OTHERS THEN
2496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2497 x_return_status_text := 'UnExpected ERROR IN COMPLETE_MANUAL_MOD_LOV_MAP. SQLERRM at '||d_position||': '||SQLERRM;
2498 IF PO_LOG.d_exc THEN
2499 PO_LOG.exc(d_mod, d_position, x_return_status_text);
2500 END IF;
2501 ROLLBACK TO SAVEPOINT COMPLETE_MANUAL_MOD_LOV_MAP;
2502 END complete_manual_mod_lov_map;
2503
2504 PROCEDURE copy_line_adjustments
2505 ( p_src_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2506 , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS.po_header_id%TYPE
2507 , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2508 , p_mode IN VARCHAR2
2509 , x_return_status_text OUT NOCOPY VARCHAR2
2510 , x_return_status OUT NOCOPY VARCHAR2
2511 )
2512 IS
2513 --
2514 l_api_name CONSTANT varchar2(30) := 'copy_line_adjustments';
2515 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
2516 l_progress VARCHAR2(4) := '000';
2517 l_return_status_text VARCHAR2(2000);
2518 COPYDOC_ADJUSTMENT_FAILURE EXCEPTION;
2519
2520 l_po_price_adjustment_record PO_PRICE_ADJUSTMENTS%ROWTYPE;
2521 l_src_price_adjustment_id PO_PRICE_ADJUSTMENTS.price_adjustment_id%TYPE;
2522
2523 l_src_adj_count NUMBER;
2524 --l_src_asoc_count NUMBER;
2525 l_src_attr_count NUMBER;
2526
2527 l_dest_adj_count NUMBER;
2528 --l_dest_asoc_count NUMBER;
2529 l_dest_attr_count NUMBER;
2530
2531 l_adjustments_exist NUMBER;
2532 l_dml_count NUMBER;
2533
2534 l_auto_manual_flag VARCHAR2(1);
2535 l_override_allowed_flag VARCHAR2(1);
2536 l_overridden_flag VARCHAR2(1);
2537
2538 l_src_price_adjustment_id_tbl NUMBER_TYPE;
2539 l_dest_price_adjustment_id_tbl NUMBER_TYPE;
2540 i PLS_INTEGER;
2541
2542 --Used to pick only the parent adjustments
2543 CURSOR po_price_adjustments_cur(p_src_line_id PO_PRICE_ADJUSTMENTS.po_line_id%TYPE
2544 ,p_auto_manual_flag VARCHAR2
2545 ,p_override_allowed_flag VARCHAR2
2546 ,p_overridden_flag VARCHAR2) IS
2547 SELECT ADJ.*
2548 FROM PO_PRICE_ADJUSTMENTS ADJ
2549 WHERE ADJ.po_line_id = p_src_line_id
2550 AND (p_auto_manual_flag IS NULL OR ADJ.automatic_flag = p_auto_manual_flag)
2551 AND (p_override_allowed_flag IS NULL OR ADJ.update_allowed = p_override_allowed_flag)
2552 AND (p_overridden_flag IS NULL OR ADJ.updated_flag = p_overridden_flag);
2553
2554 BEGIN
2555 SAVEPOINT COPY_LINE_ADJUSTMENTS;
2556 x_return_status := FND_API.G_RET_STS_SUCCESS;
2557 l_progress := '010';
2558
2559 IF g_debug_stmt THEN
2560 PO_DEBUG.debug_begin(l_log_head);
2561 PO_DEBUG.debug_var(l_log_head,l_progress,'p_src_po_line_id',p_src_po_line_id);
2562 PO_DEBUG.debug_var(l_log_head,l_progress,'p_dest_po_header_id',p_dest_po_header_id);
2563 PO_DEBUG.debug_var(l_log_head,l_progress,'p_dest_po_line_id',p_dest_po_line_id);
2564 PO_DEBUG.debug_var(l_log_head,l_progress,'p_mode',p_mode);
2565 END IF;
2566
2567 IF (p_mode = G_COPY_MANUAL_MOD) THEN
2568 l_auto_manual_flag := 'N';
2569 l_override_allowed_flag := 'N';
2570 l_overridden_flag := 'N';
2571 ELSIF (p_mode = G_COPY_MANUAL_OVERRIDDEN_MOD) THEN
2572 l_auto_manual_flag := 'N';
2573 l_override_allowed_flag := 'Y';
2574 l_overridden_flag := 'Y';
2575 ELSIF (p_mode = G_COPY_AUTO_MOD) THEN
2576 l_auto_manual_flag := 'Y';
2577 l_override_allowed_flag := 'N';
2578 l_overridden_flag := 'N';
2579 ELSIF (p_mode = G_COPY_AUTO_OVERRIDDEN_MOD) THEN
2580 l_auto_manual_flag := 'Y';
2581 l_override_allowed_flag := 'Y';
2582 l_overridden_flag := 'Y';
2583 ELSIF (p_mode = G_COPY_OVERRIDDEN_MOD) THEN
2584 l_auto_manual_flag := NULL;
2585 l_override_allowed_flag := 'Y';
2586 l_overridden_flag := 'Y';
2587 ELSE --G_COPY_ALL_MOD
2588 l_auto_manual_flag := NULL;
2589 l_override_allowed_flag := NULL;
2590 l_overridden_flag := NULL;
2591 END IF;
2592
2593 l_progress := '020';
2594 --Check if the required parameters are passed
2595 IF (p_src_po_line_id IS NULL
2596 OR p_dest_po_header_id IS NULL OR p_dest_po_line_id IS NULL) THEN
2597 IF g_debug_stmt THEN
2598 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Incomplete parameters');
2599 END IF;
2600
2601 l_return_status_text := 'Incomplete parameters - '||
2602 'p_src_po_line_id: '||p_src_po_line_id||', '||
2603 'p_dest_po_header_id: '||p_dest_po_header_id||', '||
2604 'p_dest_po_line_id: '||p_dest_po_line_id;
2605 RAISE COPYDOC_ADJUSTMENT_FAILURE;
2606 END IF;
2607
2608 l_progress := '040';
2609 IF g_debug_stmt THEN
2610 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Check if adjustments already exist for the destination line id');
2611 END IF;
2612
2613 --Check if adjsutments already exist for the destination line id
2614 SELECT COUNT(1)
2615 INTO l_adjustments_exist
2616 FROM PO_PRICE_ADJUSTMENTS ADJ
2617 WHERE ADJ.po_line_id = p_dest_po_line_id
2618 AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
2619 AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
2620 AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag);
2621
2622 IF (l_adjustments_exist > 0) THEN
2623 l_return_status_text := 'Adjustments already exist for the destination Header Id: '||p_dest_po_header_id||' and Line Id: '||p_dest_po_line_id;
2624 IF g_debug_stmt THEN
2625 PO_DEBUG.debug_stmt(l_log_head,l_progress,l_return_status_text);
2626 END IF;
2627 RAISE COPYDOC_ADJUSTMENT_FAILURE;
2628 END IF;
2629
2630 l_progress := '050';
2631 IF g_debug_stmt THEN
2632 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get the current src record status');
2633 END IF;
2634
2635 SELECT COUNT(1)
2636 INTO l_src_adj_count
2637 FROM PO_PRICE_ADJUSTMENTS ADJ
2638 WHERE ADJ.po_line_id = p_src_po_line_id
2639 AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
2640 AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
2641 AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag);
2642
2643 SELECT COUNT(1)
2644 INTO l_src_attr_count
2645 FROM PO_PRICE_ADJ_ATTRIBS ATTR
2646 ,PO_PRICE_ADJUSTMENTS ADJ
2647 WHERE ATTR.price_adjustment_id = ADJ.price_adjustment_id
2648 AND ADJ.po_line_id = p_src_po_line_id
2649 AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
2650 AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
2651 AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag);
2652
2653 IF g_debug_stmt THEN
2654 PO_DEBUG.debug_var(l_log_head,l_progress,'l_src_adj_count',l_src_adj_count);
2655 --PO_DEBUG.debug_var(l_log_head,l_progress,'l_src_asoc_count',l_src_asoc_count);
2656 PO_DEBUG.debug_var(l_log_head,l_progress,'l_src_attr_count',l_src_attr_count);
2657 END IF;
2658
2659 l_progress := '070';
2660 IF g_debug_stmt THEN
2661 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Loop through and Copy Adjustments');
2662 END IF;
2663
2664 i := 0;
2665 OPEN po_price_adjustments_cur( p_src_po_line_id
2666 , l_auto_manual_flag
2667 , l_override_allowed_flag
2668 , l_overridden_flag);
2669 <<ADJUSTMENTS>>
2670 LOOP
2671 FETCH po_price_adjustments_cur INTO l_po_price_adjustment_record;
2672 EXIT ADJUSTMENTS WHEN po_price_adjustments_cur%NOTFOUND;
2673
2674 -- reset for new record
2675 l_po_price_adjustment_record.po_header_id := p_dest_po_header_id;
2676 l_po_price_adjustment_record.po_line_id := p_dest_po_line_id;
2677
2678 l_src_price_adjustment_id := l_po_price_adjustment_record.price_adjustment_id;
2679 SELECT po_price_adjustments_s.nextval
2680 INTO l_po_price_adjustment_record.price_adjustment_id
2681 FROM SYS.DUAL;
2682
2683 --reset Standard and other columns
2684 l_po_price_adjustment_record.created_by := fnd_global.user_id;
2685 l_po_price_adjustment_record.creation_date := SYSDATE;
2686 l_po_price_adjustment_record.last_updated_by := fnd_global.user_id;
2687 l_po_price_adjustment_record.last_update_date := SYSDATE;
2688 l_po_price_adjustment_record.last_update_login := fnd_global.login_id;
2689
2690 l_po_price_adjustment_record.program_application_id := NULL;
2691 l_po_price_adjustment_record.program_id := NULL;
2692 l_po_price_adjustment_record.program_update_date := NULL;
2693 l_po_price_adjustment_record.request_id := NULL;
2694
2695 l_progress := '080';
2696 IF g_debug_stmt THEN
2697 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call insert adjustment record');
2698 END IF;
2699
2700 insert_adj_rec(l_po_price_adjustment_record);
2701
2702 l_dml_count := SQL%ROWCOUNT;
2703 IF (l_dml_count = 0) THEN
2704 l_return_status_text := 'Insert adjustment record failed';
2705 IF g_debug_stmt THEN
2706 PO_DEBUG.debug_stmt(l_log_head,l_progress,l_return_status_text);
2707 END IF;
2708 RAISE COPYDOC_ADJUSTMENT_FAILURE;
2709 END IF;
2710 l_dest_adj_count := l_dest_adj_count + l_dml_count;
2711
2712 l_progress := '090';
2713 IF g_debug_stmt THEN
2714 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Insert attributes corresponding to the adjustment');
2715 END IF;
2716
2717 --copy attribute lines for the adjustment
2718 INSERT INTO PO_PRICE_ADJ_ATTRIBS
2719 (PRICE_ADJUSTMENT_ID
2720 , PRICING_CONTEXT
2721 , PRICING_ATTRIBUTE
2722 , CREATION_DATE
2723 , CREATED_BY
2724 , LAST_UPDATE_DATE
2725 , LAST_UPDATED_BY
2726 , LAST_UPDATE_LOGIN
2727 , PROGRAM_APPLICATION_ID
2728 , PROGRAM_ID
2729 , PROGRAM_UPDATE_DATE
2730 , REQUEST_ID
2731 , PRICING_ATTR_VALUE_FROM
2732 , PRICING_ATTR_VALUE_TO
2733 , COMPARISON_OPERATOR
2734 , FLEX_TITLE
2735 , PRICE_ADJ_ATTRIB_ID
2736 , LOCK_CONTROL
2737 )
2738 (SELECT
2739 l_po_price_adjustment_record.price_adjustment_id --newly copied price_adjustment_id
2740 , ATTR.pricing_context
2741 , ATTR.pricing_attribute
2742 , SYSDATE
2743 , fnd_global.user_id
2744 , SYSDATE
2745 , fnd_global.user_id
2746 , fnd_global.login_id
2747 , NULL
2748 , NULL
2749 , NULL
2750 , NULL
2751 , ATTR.pricing_attr_value_from
2752 , ATTR.pricing_attr_value_to
2753 , ATTR.comparison_operator
2754 , ATTR.FLEX_TITLE
2755 , PO_PRICE_ADJ_ATTRIBS_S.nextval
2756 , 1
2757 FROM PO_PRICE_ADJ_ATTRIBS ATTR
2758 WHERE ATTR.price_adjustment_id = l_src_price_adjustment_id);
2759 l_dest_attr_count := l_dest_attr_count + SQL%ROWCOUNT;
2760
2761 --Get the source and dest price adjustment id mapping, will be used later to update the parent adjustment ids
2762 i := i + 1;
2763 l_src_price_adjustment_id_tbl(i) := l_src_price_adjustment_id;
2764 l_dest_price_adjustment_id_tbl(i) := l_po_price_adjustment_record.price_adjustment_id;
2765 END LOOP ADJUSTMENTS;
2766 CLOSE po_price_adjustments_cur;
2767
2768 l_progress := '100';
2769 IF g_debug_stmt THEN
2770 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update parent adjustment ids');
2771 END IF;
2772
2773 FORALL i IN l_src_price_adjustment_id_tbl.FIRST .. l_src_price_adjustment_id_tbl.LAST
2774 UPDATE PO_PRICE_ADJUSTMENTS
2775 SET parent_adjustment_id = l_dest_price_adjustment_id_tbl(i)
2776 WHERE parent_adjustment_id = l_src_price_adjustment_id_tbl(i)
2777 AND po_header_id = p_dest_po_header_id
2778 AND po_line_id = p_dest_po_line_id
2779 AND parent_adjustment_id IS NOT NULL; --Only child lines are considered
2780
2781 l_progress := '120';
2782 IF g_debug_stmt THEN
2783 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Check if the entire price structure is copied');
2784 END IF;
2785
2786 IF (l_src_adj_count <> l_dest_adj_count OR l_src_attr_count <> l_dest_attr_count) THEN --OR l_src_asoc_count <> l_dest_asoc_count
2787 IF g_debug_stmt THEN
2788 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Copy Line Adjustment failed with incomplete price structure');
2789 PO_DEBUG.debug_stmt(l_log_head,l_progress,' Source Adjustment Count: '||l_src_adj_count||', Destination Adjustment Count: '||l_dest_adj_count);
2790 --PO_DEBUG.debug_stmt(l_log_head,l_progress,' Source Association Count: '||l_src_asoc_count||', Destination Association Count: '||l_dest_asoc_count);
2791 PO_DEBUG.debug_stmt(l_log_head,l_progress,' Source Attribute Count: '||l_src_attr_count||', Destination Attribute Count: '||l_dest_attr_count);
2792 END IF;
2793 END IF;
2794
2795 IF g_debug_stmt THEN
2796 PO_DEBUG.debug_end(l_log_head);
2797 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status_text',x_return_status_text);
2798 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
2799 END IF;
2800
2801 EXCEPTION
2802 WHEN COPYDOC_ADJUSTMENT_FAILURE THEN
2803 x_return_status := FND_API.G_RET_STS_ERROR;
2804 x_return_status_text := l_return_status_text;
2805
2806 IF g_debug_unexp THEN
2807 PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING COPY_LINE_ADJUSTMENTS with ERROR: '||l_return_status_text);
2808 END IF;
2809 ROLLBACK TO SAVEPOINT COPY_LINE_ADJUSTMENTS;
2810 WHEN OTHERS THEN
2811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2812 x_return_status_text := 'UnExpected ERROR IN COPY_LINE_ADJUSTMENTS. SQLERRM at '||l_progress||': '||SQLERRM;
2813
2814 IF g_debug_unexp THEN
2815 PO_DEBUG.debug_stmt(l_log_head,l_progress, x_return_status_text);
2816 END IF;
2817 ROLLBACK TO SAVEPOINT COPY_LINE_ADJUSTMENTS;
2818 END copy_line_adjustments;
2819
2820
2821 PROCEDURE copy_draft_line_adjustments
2822 ( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
2823 , p_src_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2824 , p_dest_po_header_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_header_id%TYPE
2825 , p_dest_po_line_id IN PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2826 , p_mode IN VARCHAR2
2827 , x_return_status_text OUT NOCOPY VARCHAR2
2828 , x_return_status OUT NOCOPY VARCHAR2
2829 )
2830 IS
2831 --
2832 l_api_name CONSTANT varchar2(30) := 'copy_draft_line_adjustments';
2833 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
2834 l_progress VARCHAR2(4) := '000';
2835 l_return_status_text VARCHAR2(2000);
2836 COPYDOC_ADJUSTMENT_FAILURE EXCEPTION;
2837
2838 l_po_price_adjustment_record PO_PRICE_ADJUSTMENTS_V%ROWTYPE;
2839 l_src_price_adjustment_id PO_PRICE_ADJUSTMENTS_DRAFT.price_adjustment_id%TYPE;
2840
2841 l_src_adj_count NUMBER;
2842 --l_src_asoc_count NUMBER;
2843 l_src_attr_count NUMBER;
2844
2845 l_dest_adj_count NUMBER;
2846 --l_dest_asoc_count NUMBER;
2847 l_dest_attr_count NUMBER;
2848
2849 l_adjustments_exist NUMBER;
2850 l_dml_count NUMBER;
2851
2852 l_auto_manual_flag VARCHAR2(1);
2853 l_override_allowed_flag VARCHAR2(1);
2854 l_overridden_flag VARCHAR2(1);
2855
2856 l_src_price_adjustment_id_tbl NUMBER_TYPE;
2857 l_dest_price_adjustment_id_tbl NUMBER_TYPE;
2858 i PLS_INTEGER;
2859
2860 --Used to pick only the parent adjustments
2861 CURSOR po_price_adjustments_cur(p_src_line_id PO_PRICE_ADJUSTMENTS_DRAFT.po_line_id%TYPE
2862 ,p_auto_manual_flag VARCHAR2
2863 ,p_override_allowed_flag VARCHAR2
2864 ,p_overridden_flag VARCHAR2) IS
2865 SELECT ADJV.*
2866 FROM PO_PRICE_ADJUSTMENTS_V ADJV
2867 WHERE ADJV.po_line_id = p_src_line_id --ADJV.draft_id = p_draft_id --the draft id may not have been initialized when copy event was triggered
2868 AND (p_auto_manual_flag IS NULL OR ADJV.automatic_flag = p_auto_manual_flag)
2869 AND (p_override_allowed_flag IS NULL OR ADJV.update_allowed = p_override_allowed_flag)
2870 AND (p_overridden_flag IS NULL OR ADJV.updated_flag = p_overridden_flag);
2871
2872 BEGIN
2873 SAVEPOINT COPY_DRAFT_LINE_ADJUSTMENTS;
2874 x_return_status := FND_API.G_RET_STS_SUCCESS;
2875 l_progress := '010';
2876
2877 IF g_debug_stmt THEN
2878 PO_DEBUG.debug_begin(l_log_head);
2879 PO_DEBUG.debug_var(l_log_head,l_progress,'p_draft_id',p_draft_id);
2880 PO_DEBUG.debug_var(l_log_head,l_progress,'p_src_po_line_id',p_src_po_line_id);
2881 PO_DEBUG.debug_var(l_log_head,l_progress,'p_dest_po_header_id',p_dest_po_header_id);
2882 PO_DEBUG.debug_var(l_log_head,l_progress,'p_dest_po_line_id',p_dest_po_line_id);
2883 PO_DEBUG.debug_var(l_log_head,l_progress,'p_mode',p_mode);
2884 END IF;
2885
2886 /*
2887 IF (p_mode <> G_COPY_ALL_MOD) THEN
2888 l_return_status_text := 'The only mode supported for now is COPY ALL ADJUSTMENTS';
2889 IF g_debug_stmt THEN
2890 PO_DEBUG.debug_stmt(l_log_head,l_progress,l_return_status_text);
2891 END IF;
2892 RAISE COPYDOC_ADJUSTMENT_FAILURE;
2893 END IF;
2894 */
2895 IF (p_mode = G_COPY_MANUAL_MOD) THEN
2896 l_auto_manual_flag := 'N';
2897 l_override_allowed_flag := 'N';
2898 l_overridden_flag := 'N';
2899 ELSIF (p_mode = G_COPY_MANUAL_OVERRIDDEN_MOD) THEN
2900 l_auto_manual_flag := 'N';
2901 l_override_allowed_flag := 'Y';
2902 l_overridden_flag := 'Y';
2903 ELSIF (p_mode = G_COPY_AUTO_MOD) THEN
2904 l_auto_manual_flag := 'Y';
2905 l_override_allowed_flag := 'N';
2906 l_overridden_flag := 'N';
2907 ELSIF (p_mode = G_COPY_AUTO_OVERRIDDEN_MOD) THEN
2908 l_auto_manual_flag := 'Y';
2909 l_override_allowed_flag := 'Y';
2910 l_overridden_flag := 'Y';
2911 ELSIF (p_mode = G_COPY_OVERRIDDEN_MOD) THEN
2912 l_auto_manual_flag := NULL;
2913 l_override_allowed_flag := 'Y';
2914 l_overridden_flag := 'Y';
2915 ELSE --G_COPY_ALL_MOD
2916 l_auto_manual_flag := NULL;
2917 l_override_allowed_flag := NULL;
2918 l_overridden_flag := NULL;
2919 END IF;
2920
2921 l_progress := '020';
2922 --Check if the required parameters are passed
2923 IF (p_draft_id IS NULL OR p_src_po_line_id IS NULL
2924 OR p_dest_po_header_id IS NULL OR p_dest_po_line_id IS NULL) THEN
2925 IF g_debug_stmt THEN
2926 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Incomplete parameters');
2927 END IF;
2928
2929 l_return_status_text := 'Incomplete parameters - '||
2930 'p_draft_id: '||p_draft_id||', '||
2931 'p_src_po_line_id: '||p_src_po_line_id||', '||
2932 'p_dest_po_header_id: '||p_dest_po_header_id||', '||
2933 'p_dest_po_line_id: '||p_dest_po_line_id;
2934 RAISE COPYDOC_ADJUSTMENT_FAILURE;
2935 END IF;
2936
2937 l_progress := '040';
2938 IF g_debug_stmt THEN
2939 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Check if adjustments already exist for the destination line id');
2940 END IF;
2941
2942 --Check if adjsutments already exist for the destination line id
2943 SELECT COUNT(1)
2944 INTO l_adjustments_exist
2945 FROM PO_PRICE_ADJUSTMENTS_V ADJV
2946 WHERE ADJV.draft_id = p_draft_id
2947 AND ADJV.po_line_id = p_dest_po_line_id
2948 AND (l_auto_manual_flag IS NULL OR ADJV.automatic_flag = l_auto_manual_flag)
2949 AND (l_override_allowed_flag IS NULL OR ADJV.update_allowed = l_override_allowed_flag)
2950 AND (l_overridden_flag IS NULL OR ADJV.updated_flag = l_overridden_flag);
2951
2952 IF (l_adjustments_exist > 0) THEN
2953 l_return_status_text := 'Adjustments already exist for the Draft Id: '||p_draft_id||', Destination Header Id: '||p_dest_po_header_id||' and Line Id: '||p_dest_po_line_id;
2954 IF g_debug_stmt THEN
2955 PO_DEBUG.debug_stmt(l_log_head,l_progress,l_return_status_text);
2956 END IF;
2957 RAISE COPYDOC_ADJUSTMENT_FAILURE;
2958 END IF;
2959
2960 l_progress := '050';
2961 IF g_debug_stmt THEN
2962 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get the current src record status');
2963 END IF;
2964
2965 SELECT COUNT(1)
2966 INTO l_src_adj_count
2967 FROM PO_PRICE_ADJUSTMENTS_V ADJV
2968 WHERE ADJV.po_line_id = p_src_po_line_id --ADJV.draft_id = p_draft_id --the draft id may not have been initialized when copy event was triggered
2969 AND (l_auto_manual_flag IS NULL OR ADJV.automatic_flag = l_auto_manual_flag)
2970 AND (l_override_allowed_flag IS NULL OR ADJV.update_allowed = l_override_allowed_flag)
2971 AND (l_overridden_flag IS NULL OR ADJV.updated_flag = l_overridden_flag);
2972
2973 /*
2974 SELECT COUNT(1)
2975 INTO l_src_asoc_count
2976 FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
2977 WHERE ASOC.draft_id = p_draft_id
2978 AND ASOC.line_id = p_src_po_line_id
2979 AND EXISTS (SELECT 1
2980 FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
2981 WHERE ADJ.draft_id = p_draft_id
2982 AND ADJ.line_id = p_src_po_line_id
2983 AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
2984 AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
2985 AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag));
2986 */
2987
2988 SELECT COUNT(1)
2989 INTO l_src_attr_count
2990 FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
2991 WHERE ATTRV.po_line_id = p_src_po_line_id --ADJV.draft_id = p_draft_id AND ATTRV.draft_id = ADJV.draft_id --the draft id may not have been initialized when copy event was triggered
2992 AND (l_auto_manual_flag IS NULL OR ATTRV.automatic_flag = l_auto_manual_flag)
2993 AND (l_override_allowed_flag IS NULL OR ATTRV.update_allowed = l_override_allowed_flag)
2994 AND (l_overridden_flag IS NULL OR ATTRV.updated_flag = l_overridden_flag);
2995
2996 IF g_debug_stmt THEN
2997 PO_DEBUG.debug_var(l_log_head,l_progress,'l_src_adj_count',l_src_adj_count);
2998 --PO_DEBUG.debug_var(l_log_head,l_progress,'l_src_asoc_count',l_src_asoc_count);
2999 PO_DEBUG.debug_var(l_log_head,l_progress,'l_src_attr_count',l_src_attr_count);
3000 END IF;
3001
3002 /*
3003 l_progress := '060';
3004 IF g_debug_stmt THEN
3005 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Copy Draft ssociation records first');
3006 END IF;
3007
3008 --Copy Association records with p_src_po_line_id
3009 INSERT INTO PO_PRICE_ADJ_ASSOCS_DRAFT
3010 (DRAFT_ID
3011 , PRICE_ADJUSTMENT_ID
3012 , CREATION_DATE
3013 , CREATED_BY
3014 , LAST_UPDATE_DATE
3015 , LAST_UPDATED_BY
3016 , LAST_UPDATE_LOGIN
3017 , PROGRAM_APPLICATION_ID
3018 , PROGRAM_ID
3019 , PROGRAM_UPDATE_DATE
3020 , REQUEST_ID
3021 , PRICE_ADJ_ASSOC_ID
3022 , LINE_ID
3023 , RLTD_PRICE_ADJ_ID
3024 , LOCK_CONTROL
3025 )
3026 (SELECT p_draft_id
3027 , ASOC.price_adjustment_id
3028 , SYSDATE --p_Line_Adj_Assoc_Rec.creation_date
3029 , fnd_global.user_id --p_Line_Adj_Assoc_Rec.CREATED_BY
3030 , SYSDATE --p_Line_Adj_Assoc_Rec.LAST_UPDATE_DATE
3031 , fnd_global.user_id --p_Line_Adj_Assoc_Rec.LAST_UPDATED_BY
3032 , fnd_global.login_id --p_Line_Adj_Assoc_Rec.LAST_UPDATE_LOGIN
3033 , NULL --p_Line_Adj_Assoc_Rec.PROGRAM_APPLICATION_ID
3034 , NULL --p_Line_Adj_Assoc_Rec.PROGRAM_ID
3035 , NULL --p_Line_Adj_Assoc_Rec.PROGRAM_UPDATE_DATE
3036 , NULL --p_Line_Adj_Assoc_Rec.REQUEST_ID
3037 , PO_PRICE_ADJ_ASSOCS_S.nextval
3038 , p_dest_po_line_id
3039 , ASOC.rltd_price_adj_id
3040 , 1
3041 FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
3042 WHERE ASOC.draft_id = p_draft_id
3043 AND ASOC.line_id = p_src_po_line_id
3044 AND EXISTS (SELECT 1
3045 FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
3046 WHERE ADJ.draft_id = p_draft_id
3047 AND ADJ.line_id = p_src_po_line_id
3048 AND (l_auto_manual_flag IS NULL OR ADJ.automatic_flag = l_auto_manual_flag)
3049 AND (l_override_allowed_flag IS NULL OR ADJ.update_allowed = l_override_allowed_flag)
3050 AND (l_overridden_flag IS NULL OR ADJ.updated_flag = l_overridden_flag)));
3051
3052 l_dest_asoc_count := SQL%ROWCOUNT;
3053 IF (l_src_asoc_count <> l_dest_asoc_count) THEN
3054 l_return_status_text := 'Copy association record failed';
3055 IF g_debug_stmt THEN
3056 PO_DEBUG.debug_stmt(l_log_head,l_progress,l_return_status_text);
3057 END IF;
3058 RAISE COPYDOC_ADJUSTMENT_FAILURE;
3059 END IF;
3060 */
3061
3062 l_progress := '070';
3063 IF g_debug_stmt THEN
3064 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Loop through and Copy Adjustments');
3065 END IF;
3066 i := 0;
3067 OPEN po_price_adjustments_cur( p_src_po_line_id
3068 , l_auto_manual_flag
3069 , l_override_allowed_flag
3070 , l_overridden_flag);
3071 <<ADJUSTMENTS>>
3072 LOOP
3073 FETCH po_price_adjustments_cur INTO l_po_price_adjustment_record;
3074 EXIT ADJUSTMENTS WHEN po_price_adjustments_cur%NOTFOUND;
3075
3076 -- reset for new record
3077 --l_po_price_adjustment_record.draft_id := p_draft_id;
3078 l_po_price_adjustment_record.po_header_id := p_dest_po_header_id;
3079 l_po_price_adjustment_record.po_line_id := p_dest_po_line_id;
3080
3081 l_src_price_adjustment_id := l_po_price_adjustment_record.price_adjustment_id;
3082 SELECT po_price_adjustments_s.nextval
3083 INTO l_po_price_adjustment_record.price_adjustment_id
3084 FROM SYS.DUAL;
3085
3086 --reset Standard columns
3087 l_po_price_adjustment_record.created_by := fnd_global.user_id;
3088 l_po_price_adjustment_record.creation_date := SYSDATE;
3089 l_po_price_adjustment_record.last_updated_by := fnd_global.user_id;
3090 l_po_price_adjustment_record.last_update_date := SYSDATE;
3091 l_po_price_adjustment_record.last_update_login := fnd_global.login_id;
3092
3093 l_po_price_adjustment_record.program_application_id := NULL;
3094 l_po_price_adjustment_record.program_id := NULL;
3095 l_po_price_adjustment_record.program_update_date := NULL;
3096 l_po_price_adjustment_record.request_id := NULL;
3097
3098
3099 l_progress := '080';
3100 IF g_debug_stmt THEN
3101 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call insert adjustment record');
3102 END IF;
3103
3104 insert_draft_adj_rec(p_draft_id, l_po_price_adjustment_record);
3105
3106 l_dml_count := SQL%ROWCOUNT;
3107 IF (l_dml_count = 0) THEN
3108 l_return_status_text := 'Insert adjustment record failed';
3109 IF g_debug_stmt THEN
3110 PO_DEBUG.debug_stmt(l_log_head,l_progress,l_return_status_text);
3111 END IF;
3112 RAISE COPYDOC_ADJUSTMENT_FAILURE;
3113 END IF;
3114 l_dest_adj_count := l_dest_adj_count + l_dml_count;
3115
3116 l_progress := '090';
3117 IF g_debug_stmt THEN
3118 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Insert attributes corresponding to the adjustment');
3119 END IF;
3120
3121 --copy attribute lines for the adjustment
3122 INSERT INTO PO_PRICE_ADJ_ATTRIBS_DRAFT
3123 (DRAFT_ID
3124 , PRICE_ADJUSTMENT_ID
3125 , PRICING_CONTEXT
3126 , PRICING_ATTRIBUTE
3127 , CREATION_DATE
3128 , CREATED_BY
3129 , LAST_UPDATE_DATE
3130 , LAST_UPDATED_BY
3131 , LAST_UPDATE_LOGIN
3132 , PROGRAM_APPLICATION_ID
3133 , PROGRAM_ID
3134 , PROGRAM_UPDATE_DATE
3135 , REQUEST_ID
3136 , PRICING_ATTR_VALUE_FROM
3137 , PRICING_ATTR_VALUE_TO
3138 , COMPARISON_OPERATOR
3139 , FLEX_TITLE
3140 , PRICE_ADJ_ATTRIB_ID
3141 , LOCK_CONTROL
3142 )
3143 (SELECT
3144 p_draft_id --ATTRV.draft_id --draft_id may not have been initialized when copy event was triggered
3145 , l_po_price_adjustment_record.price_adjustment_id --newly copied price_adjustment_id
3146 , ATTRV.pricing_context
3147 , ATTRV.pricing_attribute
3148 , SYSDATE
3149 , fnd_global.user_id
3150 , SYSDATE
3151 , fnd_global.user_id
3152 , fnd_global.login_id
3153 , NULL
3154 , NULL
3155 , NULL
3156 , NULL
3157 , ATTRV.pricing_attr_value_from
3158 , ATTRV.pricing_attr_value_to
3159 , ATTRV.comparison_operator
3160 , ATTRV.FLEX_TITLE
3161 , PO_PRICE_ADJ_ATTRIBS_S.nextval
3162 , 1
3163 FROM PO_PRICE_ADJ_ATTRIBS_V ATTRV
3164 WHERE ATTRV.price_adjustment_id = l_src_price_adjustment_id); --ATTRV.draft_id = p_draft_id --draft_id may not have been initialized when copy event was triggered
3165 l_dest_attr_count := l_dest_attr_count + SQL%ROWCOUNT;
3166
3167 --Get the source and dest price adjustment id mapping, will be used later to update the parent adjustment ids
3168 i := i + 1;
3169 l_src_price_adjustment_id_tbl(i) := l_src_price_adjustment_id;
3170 l_dest_price_adjustment_id_tbl(i) := l_po_price_adjustment_record.price_adjustment_id;
3171 END LOOP ADJUSTMENTS;
3172 CLOSE po_price_adjustments_cur;
3173
3174 l_progress := '100';
3175 IF g_debug_stmt THEN
3176 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Update parent adjustment ids');
3177 END IF;
3178
3179 FORALL i IN l_src_price_adjustment_id_tbl.FIRST .. l_src_price_adjustment_id_tbl.LAST
3180 UPDATE PO_PRICE_ADJUSTMENTS_DRAFT
3181 SET parent_adjustment_id = l_dest_price_adjustment_id_tbl(i)
3182 WHERE parent_adjustment_id = l_src_price_adjustment_id_tbl(i)
3183 AND draft_id = p_draft_id
3184 AND po_header_id = p_dest_po_header_id
3185 AND po_line_id = p_dest_po_line_id
3186 AND parent_adjustment_id IS NOT NULL; --Only child lines are considered
3187
3188 l_progress := '120';
3189 IF g_debug_stmt THEN
3190 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Check if the entire price structure is copied');
3191 END IF;
3192
3193 IF (l_src_adj_count <> l_dest_adj_count OR l_src_attr_count <> l_dest_attr_count) THEN --OR l_src_asoc_count <> l_dest_asoc_count
3194 IF g_debug_stmt THEN
3195 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Copy Draft Line Adjustment failed with incomplete price structure');
3196 PO_DEBUG.debug_stmt(l_log_head,l_progress,' Source Adjustment Count: '||l_src_adj_count||', Destination Adjustment Count: '||l_dest_adj_count);
3197 --PO_DEBUG.debug_stmt(l_log_head,l_progress,' Source Association Count: '||l_src_asoc_count||', Destination Association Count: '||l_dest_asoc_count);
3198 PO_DEBUG.debug_stmt(l_log_head,l_progress,' Source Attribute Count: '||l_src_attr_count||', Destination Attribute Count: '||l_dest_attr_count);
3199 END IF;
3200 END IF;
3201
3202 IF g_debug_stmt THEN
3203 PO_DEBUG.debug_end(l_log_head);
3204 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status_text',x_return_status_text);
3205 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
3206 END IF;
3207
3208 EXCEPTION
3209 WHEN COPYDOC_ADJUSTMENT_FAILURE THEN
3210 x_return_status := FND_API.G_RET_STS_ERROR;
3211 x_return_status_text := l_return_status_text;
3212
3213 IF g_debug_unexp THEN
3214 PO_DEBUG.debug_stmt(l_log_head,l_progress,'EXITING COPY_DRAFT_LINE_ADJUSTMENTS with ERROR: '||l_return_status_text);
3215 END IF;
3216 ROLLBACK TO SAVEPOINT COPY_DRAFT_LINE_ADJUSTMENTS;
3217 WHEN OTHERS THEN
3218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3219 x_return_status_text := 'UnExpected ERROR IN COPY_DRAFT_LINE_ADJUSTMENTS. SQLERRM at '||l_progress||': '||SQLERRM;
3220
3221 IF g_debug_unexp THEN
3222 PO_DEBUG.debug_stmt(l_log_head,l_progress, x_return_status_text);
3223 END IF;
3224 ROLLBACK TO SAVEPOINT COPY_DRAFT_LINE_ADJUSTMENTS;
3225 END copy_draft_line_adjustments;
3226
3227
3228
3229
3230 PROCEDURE insert_adj_rec(p_adj_rec IN PO_PRICE_ADJUSTMENTS%ROWTYPE)
3231 IS
3232 BEGIN
3233 INSERT INTO PO_PRICE_ADJUSTMENTS
3234 (PRICE_ADJUSTMENT_ID
3235 , CREATION_DATE
3236 , CREATED_BY
3237 , LAST_UPDATE_DATE
3238 , LAST_UPDATED_BY
3239 , LAST_UPDATE_LOGIN
3240 , PROGRAM_APPLICATION_ID
3241 , PROGRAM_ID
3242 , PROGRAM_UPDATE_DATE
3243 , REQUEST_ID
3244 , PO_HEADER_ID
3245 , AUTOMATIC_FLAG
3246 , PO_LINE_ID
3247 , ADJ_LINE_NUM
3248 , CONTEXT
3249 , ATTRIBUTE1
3250 , ATTRIBUTE2
3251 , ATTRIBUTE3
3252 , ATTRIBUTE4
3253 , ATTRIBUTE5
3254 , ATTRIBUTE6
3255 , ATTRIBUTE7
3256 , ATTRIBUTE8
3257 , ATTRIBUTE9
3258 , ATTRIBUTE10
3259 , ATTRIBUTE11
3260 , ATTRIBUTE12
3261 , ATTRIBUTE13
3262 , ATTRIBUTE14
3263 , ATTRIBUTE15
3264 , ORIG_SYS_DISCOUNT_REF
3265 , LIST_HEADER_ID
3266 , LIST_LINE_ID
3267 , LIST_LINE_TYPE_CODE
3268 , MODIFIED_FROM
3269 , MODIFIED_TO
3270 , UPDATED_FLAG
3271 , UPDATE_ALLOWED
3272 , APPLIED_FLAG
3273 , CHANGE_REASON_CODE
3274 , CHANGE_REASON_TEXT
3275 , OPERAND
3276 , ARITHMETIC_OPERATOR
3277 , COST_ID
3278 , TAX_CODE
3279 , TAX_EXEMPT_FLAG
3280 , TAX_EXEMPT_NUMBER
3281 , TAX_EXEMPT_REASON_CODE
3282 , PARENT_ADJUSTMENT_ID
3283 , INVOICED_FLAG
3284 , ESTIMATED_FLAG
3285 , INC_IN_SALES_PERFORMANCE
3286 , ADJUSTED_AMOUNT
3287 , PRICING_PHASE_ID
3288 , CHARGE_TYPE_CODE
3289 , CHARGE_SUBTYPE_CODE
3290 , LIST_LINE_NO
3291 , SOURCE_SYSTEM_CODE
3292 , BENEFIT_QTY
3293 , BENEFIT_UOM_CODE
3294 , PRINT_ON_INVOICE_FLAG
3295 , EXPIRATION_DATE
3296 , REBATE_TRANSACTION_TYPE_CODE
3297 , REBATE_TRANSACTION_REFERENCE
3298 , REBATE_PAYMENT_SYSTEM_CODE
3299 , REDEEMED_DATE
3300 , REDEEMED_FLAG
3301 , ACCRUAL_FLAG
3302 , RANGE_BREAK_QUANTITY
3303 , ACCRUAL_CONVERSION_RATE
3304 , PRICING_GROUP_SEQUENCE
3305 , MODIFIER_LEVEL_CODE
3306 , PRICE_BREAK_TYPE_CODE
3307 , SUBSTITUTION_ATTRIBUTE
3308 , PRORATION_TYPE_CODE
3309 , CREDIT_OR_CHARGE_FLAG
3310 , INCLUDE_ON_RETURNS_FLAG
3311 , AC_CONTEXT
3312 , AC_ATTRIBUTE1
3313 , AC_ATTRIBUTE2
3314 , AC_ATTRIBUTE3
3315 , AC_ATTRIBUTE4
3316 , AC_ATTRIBUTE5
3317 , AC_ATTRIBUTE6
3318 , AC_ATTRIBUTE7
3319 , AC_ATTRIBUTE8
3320 , AC_ATTRIBUTE9
3321 , AC_ATTRIBUTE10
3322 , AC_ATTRIBUTE11
3323 , AC_ATTRIBUTE12
3324 , AC_ATTRIBUTE13
3325 , AC_ATTRIBUTE14
3326 , AC_ATTRIBUTE15
3327 , OPERAND_PER_PQTY
3328 , ADJUSTED_AMOUNT_PER_PQTY
3329 , LOCK_CONTROL
3330 )
3331 (SELECT p_adj_rec.price_adjustment_id
3332 , p_adj_rec.creation_date
3333 , p_adj_rec.created_by
3334 , p_adj_rec.last_update_date
3335 , p_adj_rec.last_updated_by
3336 , p_adj_rec.last_update_login
3337 , p_adj_rec.program_application_id
3338 , p_adj_rec.program_id
3339 , p_adj_rec.program_update_date
3340 , p_adj_rec.request_id
3341 , p_adj_rec.po_header_id
3342 , p_adj_rec.automatic_flag
3343 , p_adj_rec.po_line_id
3344 , p_adj_rec.adj_line_num
3345 , p_adj_rec.context
3346 , p_adj_rec.attribute1
3347 , p_adj_rec.attribute2
3348 , p_adj_rec.attribute3
3349 , p_adj_rec.attribute4
3350 , p_adj_rec.attribute5
3351 , p_adj_rec.attribute6
3352 , p_adj_rec.attribute7
3353 , p_adj_rec.attribute8
3354 , p_adj_rec.attribute9
3355 , p_adj_rec.attribute10
3356 , p_adj_rec.attribute11
3357 , p_adj_rec.attribute12
3358 , p_adj_rec.attribute13
3359 , p_adj_rec.attribute14
3360 , p_adj_rec.attribute15
3361 , p_adj_rec.orig_sys_discount_ref
3362 , p_adj_rec.list_header_id
3363 , p_adj_rec.list_line_id
3364 , p_adj_rec.list_line_type_code
3365 , p_adj_rec.modified_from
3366 , p_adj_rec.modified_to
3367 , p_adj_rec.updated_flag
3368 , p_adj_rec.update_allowed
3369 , p_adj_rec.applied_flag
3370 , p_adj_rec.change_reason_code
3371 , p_adj_rec.change_reason_text
3372 , p_adj_rec.operand
3373 , p_adj_rec.arithmetic_operator
3374 , p_adj_rec.cost_id
3375 , p_adj_rec.tax_code
3376 , p_adj_rec.tax_exempt_flag
3377 , p_adj_rec.tax_exempt_number
3378 , p_adj_rec.tax_exempt_reason_code
3379 , p_adj_rec.parent_adjustment_id
3380 , p_adj_rec.invoiced_flag
3381 , p_adj_rec.estimated_flag
3382 , p_adj_rec.inc_in_sales_performance
3383 , p_adj_rec.adjusted_amount
3384 , p_adj_rec.pricing_phase_id
3385 , p_adj_rec.charge_type_code
3386 , p_adj_rec.charge_subtype_code
3387 , p_adj_rec.list_line_no
3388 , p_adj_rec.source_system_code
3389 , p_adj_rec.benefit_qty
3390 , p_adj_rec.benefit_uom_code
3391 , p_adj_rec.print_on_invoice_flag
3392 , p_adj_rec.expiration_date
3393 , p_adj_rec.rebate_transaction_type_code
3394 , p_adj_rec.rebate_transaction_reference
3395 , p_adj_rec.rebate_payment_system_code
3396 , p_adj_rec.redeemed_date
3397 , p_adj_rec.redeemed_flag
3398 , p_adj_rec.accrual_flag
3399 , p_adj_rec.range_break_quantity
3400 , p_adj_rec.accrual_conversion_rate
3401 , p_adj_rec.pricing_group_sequence
3402 , p_adj_rec.modifier_level_code
3403 , p_adj_rec.price_break_type_code
3404 , p_adj_rec.substitution_attribute
3405 , p_adj_rec.proration_type_code
3406 , p_adj_rec.credit_or_charge_flag
3407 , p_adj_rec.include_on_returns_flag
3408 , p_adj_rec.ac_context
3409 , p_adj_rec.ac_attribute1
3410 , p_adj_rec.ac_attribute2
3411 , p_adj_rec.ac_attribute3
3412 , p_adj_rec.ac_attribute4
3413 , p_adj_rec.ac_attribute5
3414 , p_adj_rec.ac_attribute6
3415 , p_adj_rec.ac_attribute7
3416 , p_adj_rec.ac_attribute8
3417 , p_adj_rec.ac_attribute9
3418 , p_adj_rec.ac_attribute10
3419 , p_adj_rec.ac_attribute11
3420 , p_adj_rec.ac_attribute12
3421 , p_adj_rec.ac_attribute13
3422 , p_adj_rec.ac_attribute14
3423 , p_adj_rec.ac_attribute15
3424 , p_adj_rec.operand_per_pqty
3425 , p_adj_rec.adjusted_amount_per_pqty
3426 , 1 -- LOCK_CONTROL
3427 FROM DUAL
3428 );
3429 --Exception will be caught by the calling procedure
3430 END insert_adj_rec;
3431
3432 PROCEDURE insert_draft_adj_rec(p_draft_id IN NUMBER
3433 ,p_adj_rec IN PO_PRICE_ADJUSTMENTS_V%ROWTYPE)
3434 IS
3435 BEGIN
3436 INSERT INTO PO_PRICE_ADJUSTMENTS_DRAFT
3437 (DRAFT_ID
3438 , CHANGE_ACCEPTED_FLAG
3439 , DELETE_FLAG
3440 , PRICE_ADJUSTMENT_ID
3441 , CREATION_DATE
3442 , CREATED_BY
3443 , LAST_UPDATE_DATE
3444 , LAST_UPDATED_BY
3445 , LAST_UPDATE_LOGIN
3446 , PROGRAM_APPLICATION_ID
3447 , PROGRAM_ID
3448 , PROGRAM_UPDATE_DATE
3449 , REQUEST_ID
3450 , PO_HEADER_ID
3451 , AUTOMATIC_FLAG
3452 , PO_LINE_ID
3453 , ADJ_LINE_NUM
3454 , CONTEXT
3455 , ATTRIBUTE1
3456 , ATTRIBUTE2
3457 , ATTRIBUTE3
3458 , ATTRIBUTE4
3459 , ATTRIBUTE5
3460 , ATTRIBUTE6
3461 , ATTRIBUTE7
3462 , ATTRIBUTE8
3463 , ATTRIBUTE9
3464 , ATTRIBUTE10
3465 , ATTRIBUTE11
3466 , ATTRIBUTE12
3467 , ATTRIBUTE13
3468 , ATTRIBUTE14
3469 , ATTRIBUTE15
3470 , ORIG_SYS_DISCOUNT_REF
3471 , LIST_HEADER_ID
3472 , LIST_LINE_ID
3473 , LIST_LINE_TYPE_CODE
3474 , MODIFIED_FROM
3475 , MODIFIED_TO
3476 , UPDATED_FLAG
3477 , UPDATE_ALLOWED
3478 , APPLIED_FLAG
3479 , CHANGE_REASON_CODE
3480 , CHANGE_REASON_TEXT
3481 , OPERAND
3482 , ARITHMETIC_OPERATOR
3483 , COST_ID
3484 , TAX_CODE
3485 , TAX_EXEMPT_FLAG
3486 , TAX_EXEMPT_NUMBER
3487 , TAX_EXEMPT_REASON_CODE
3488 , PARENT_ADJUSTMENT_ID
3489 , INVOICED_FLAG
3490 , ESTIMATED_FLAG
3491 , INC_IN_SALES_PERFORMANCE
3492 , ADJUSTED_AMOUNT
3493 , PRICING_PHASE_ID
3494 , CHARGE_TYPE_CODE
3495 , CHARGE_SUBTYPE_CODE
3496 , LIST_LINE_NO
3497 , SOURCE_SYSTEM_CODE
3498 , BENEFIT_QTY
3499 , BENEFIT_UOM_CODE
3500 , PRINT_ON_INVOICE_FLAG
3501 , EXPIRATION_DATE
3502 , REBATE_TRANSACTION_TYPE_CODE
3503 , REBATE_TRANSACTION_REFERENCE
3504 , REBATE_PAYMENT_SYSTEM_CODE
3505 , REDEEMED_DATE
3506 , REDEEMED_FLAG
3507 , ACCRUAL_FLAG
3508 , RANGE_BREAK_QUANTITY
3509 , ACCRUAL_CONVERSION_RATE
3510 , PRICING_GROUP_SEQUENCE
3511 , MODIFIER_LEVEL_CODE
3512 , PRICE_BREAK_TYPE_CODE
3513 , SUBSTITUTION_ATTRIBUTE
3514 , PRORATION_TYPE_CODE
3515 , CREDIT_OR_CHARGE_FLAG
3516 , INCLUDE_ON_RETURNS_FLAG
3517 , AC_CONTEXT
3518 , AC_ATTRIBUTE1
3519 , AC_ATTRIBUTE2
3520 , AC_ATTRIBUTE3
3521 , AC_ATTRIBUTE4
3522 , AC_ATTRIBUTE5
3523 , AC_ATTRIBUTE6
3524 , AC_ATTRIBUTE7
3525 , AC_ATTRIBUTE8
3526 , AC_ATTRIBUTE9
3527 , AC_ATTRIBUTE10
3528 , AC_ATTRIBUTE11
3529 , AC_ATTRIBUTE12
3530 , AC_ATTRIBUTE13
3531 , AC_ATTRIBUTE14
3532 , AC_ATTRIBUTE15
3533 , OPERAND_PER_PQTY
3534 , ADJUSTED_AMOUNT_PER_PQTY
3535 , LOCK_CONTROL
3536 )
3537 (SELECT p_draft_id
3538 , p_adj_rec.change_accepted_flag
3539 , p_adj_rec.delete_flag
3540 , p_adj_rec.price_adjustment_id
3541 , p_adj_rec.creation_date
3542 , p_adj_rec.created_by
3543 , p_adj_rec.last_update_date
3544 , p_adj_rec.last_updated_by
3545 , p_adj_rec.last_update_login
3546 , p_adj_rec.program_application_id
3547 , p_adj_rec.program_id
3548 , p_adj_rec.program_update_date
3549 , p_adj_rec.request_id
3550 , p_adj_rec.po_header_id
3551 , p_adj_rec.automatic_flag
3552 , p_adj_rec.po_line_id
3553 , p_adj_rec.adj_line_num
3554 , p_adj_rec.context
3555 , p_adj_rec.attribute1
3556 , p_adj_rec.attribute2
3557 , p_adj_rec.attribute3
3558 , p_adj_rec.attribute4
3559 , p_adj_rec.attribute5
3560 , p_adj_rec.attribute6
3561 , p_adj_rec.attribute7
3562 , p_adj_rec.attribute8
3563 , p_adj_rec.attribute9
3564 , p_adj_rec.attribute10
3565 , p_adj_rec.attribute11
3566 , p_adj_rec.attribute12
3567 , p_adj_rec.attribute13
3568 , p_adj_rec.attribute14
3569 , p_adj_rec.attribute15
3570 , p_adj_rec.orig_sys_discount_ref
3571 , p_adj_rec.list_header_id
3572 , p_adj_rec.list_line_id
3573 , p_adj_rec.list_line_type_code
3574 , p_adj_rec.modified_from
3575 , p_adj_rec.modified_to
3576 , p_adj_rec.updated_flag
3577 , p_adj_rec.update_allowed
3578 , p_adj_rec.applied_flag
3579 , p_adj_rec.change_reason_code
3580 , p_adj_rec.change_reason_text
3581 , p_adj_rec.operand
3582 , p_adj_rec.arithmetic_operator
3583 , p_adj_rec.cost_id
3584 , p_adj_rec.tax_code
3585 , p_adj_rec.tax_exempt_flag
3586 , p_adj_rec.tax_exempt_number
3587 , p_adj_rec.tax_exempt_reason_code
3588 , p_adj_rec.parent_adjustment_id
3589 , p_adj_rec.invoiced_flag
3590 , p_adj_rec.estimated_flag
3591 , p_adj_rec.inc_in_sales_performance
3592 , p_adj_rec.adjusted_amount
3593 , p_adj_rec.pricing_phase_id
3594 , p_adj_rec.charge_type_code
3595 , p_adj_rec.charge_subtype_code
3596 , p_adj_rec.list_line_no
3597 , p_adj_rec.source_system_code
3598 , p_adj_rec.benefit_qty
3599 , p_adj_rec.benefit_uom_code
3600 , p_adj_rec.print_on_invoice_flag
3601 , p_adj_rec.expiration_date
3602 , p_adj_rec.rebate_transaction_type_code
3603 , p_adj_rec.rebate_transaction_reference
3604 , p_adj_rec.rebate_payment_system_code
3605 , p_adj_rec.redeemed_date
3606 , p_adj_rec.redeemed_flag
3607 , p_adj_rec.accrual_flag
3608 , p_adj_rec.range_break_quantity
3609 , p_adj_rec.accrual_conversion_rate
3610 , p_adj_rec.pricing_group_sequence
3611 , p_adj_rec.modifier_level_code
3612 , p_adj_rec.price_break_type_code
3613 , p_adj_rec.substitution_attribute
3614 , p_adj_rec.proration_type_code
3615 , p_adj_rec.credit_or_charge_flag
3616 , p_adj_rec.include_on_returns_flag
3617 , p_adj_rec.ac_context
3618 , p_adj_rec.ac_attribute1
3619 , p_adj_rec.ac_attribute2
3620 , p_adj_rec.ac_attribute3
3621 , p_adj_rec.ac_attribute4
3622 , p_adj_rec.ac_attribute5
3623 , p_adj_rec.ac_attribute6
3624 , p_adj_rec.ac_attribute7
3625 , p_adj_rec.ac_attribute8
3626 , p_adj_rec.ac_attribute9
3627 , p_adj_rec.ac_attribute10
3628 , p_adj_rec.ac_attribute11
3629 , p_adj_rec.ac_attribute12
3630 , p_adj_rec.ac_attribute13
3631 , p_adj_rec.ac_attribute14
3632 , p_adj_rec.ac_attribute15
3633 , p_adj_rec.operand_per_pqty
3634 , p_adj_rec.adjusted_amount_per_pqty
3635 , 1 -- LOCK_CONTROL
3636 FROM DUAL
3637 );
3638 --Exception will be caught by the calling procedure
3639 END insert_draft_adj_rec;
3640
3641 -------------------------------------------------------------------------------
3642 -------------------------------------------------------------------------------
3643 --Start of Comments
3644 --Name: delete_price_adjustments
3645 --Pre-reqs:
3646 -- None.
3647 --Modifies:
3648 -- PO_PRICE_ADJUSTMENTS, PO_PRICE_ADJ_ASSOCS, PO_PRICE_ADJ_ATTRIBS
3649 --Locks:
3650 -- None.
3651 --Function:
3652 -- Deletes the price adjustments of a PO/PO Line or GBPA/GBPA Line
3653 --Parameters:
3654 --IN:
3655 --p_header_id
3656 -- Unique Header Id of PO or GBPA
3657 --p_line_id
3658 -- Unique ID of PO Line or GBPA Line
3659 --Testing:
3660 -- None.
3661 --End of Comments
3662 -------------------------------------------------------------------------------
3663 -------------------------------------------------------------------------------
3664 PROCEDURE delete_price_adjustments
3665 ( p_po_header_id IN PO_PRICE_ADJUSTMENTS.po_header_id%TYPE
3666 , p_po_line_id IN PO_PRICE_ADJUSTMENTS.po_line_id%TYPE DEFAULT NULL
3667 )
3668 IS
3669 l_price_adj_tbl NUMBER_TYPE;
3670 BEGIN
3671
3672 --Delete Price Adjustments
3673 DELETE FROM PO_PRICE_ADJUSTMENTS
3674 WHERE po_header_id = p_po_header_id
3675 AND (po_line_id = p_po_line_id OR p_po_line_id IS NULL)
3676 RETURNING
3677 price_adjustment_id
3678 BULK COLLECT INTO
3679 l_price_adj_tbl;
3680
3681 IF l_price_adj_tbl.count > 0 THEN
3682 /*
3683 --Delete Price Adjustment Associations
3684 FORALL i IN l_price_adj_tbl.FIRST..l_price_adj_tbl.LAST
3685 DELETE FROM PO_PRICE_ADJ_ASSOCS WHERE price_adjustment_id = l_price_adj_tbl(i);
3686 */
3687
3688 --Delete Price Adjustment Attributes
3689 FORALL i IN l_price_adj_tbl.FIRST..l_price_adj_tbl.LAST
3690 DELETE FROM PO_PRICE_ADJ_ATTRIBS WHERE price_adjustment_id = l_price_adj_tbl(i);
3691 END IF;
3692
3693 /*
3694 --Delete dependant fields first
3695 --Delete Price Adjustment Attributes
3696 DELETE FROM PO_PRICE_ADJ_ATTRIBS ATTR
3697 WHERE ATTR.price_adjustment_id IN (SELECT ADJ.price_adjustment_id
3698 FROM PO_PRICE_ADJUSTMENTS ADJ
3699 WHERE ADJ.header_id = p_header_id
3700 AND ADJ.line_id = p_line_id);
3701
3702 --Delete Price Adjustment Associations
3703 DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
3704 WHERE ASOC.line_id = p_line_id;
3705
3706 --Delete Price Adjustments
3707 DELETE FROM PO_PRICE_ADJUSTMENTS ADJ
3708 WHERE ADJ.header_id = p_header_id
3709 AND ADJ.line_id = p_line_id;
3710 */
3711
3712 EXCEPTION
3713 WHEN OTHERS THEN
3714 PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_price_adjustments','000',sqlcode);
3715 RAISE;
3716 END delete_price_adjustments;
3717
3718
3719 -------------------------------------------------------------------------------
3720 -------------------------------------------------------------------------------
3721 --Start of Comments
3722 --Name: delete_adjustment
3723 --Pre-reqs:
3724 -- None.
3725 --Modifies:
3726 -- PO_PRICE_ADJUSTMENTS, PO_PRICE_ADJ_ASSOCS, PO_PRICE_ADJ_ATTRIBS
3727 --Locks:
3728 -- None.
3729 --Function:
3730 -- Deletes the price adjustments of a PO Line or GBPA Line
3731 --Parameters:
3732 --IN:
3733 --p_price_adjustment_id
3734 -- Unique ID of Price Adjustment
3735 --Testing:
3736 -- None.
3737 --End of Comments
3738 -------------------------------------------------------------------------------
3739 -------------------------------------------------------------------------------
3740 PROCEDURE delete_adjustment
3741 ( p_price_adjustment_id IN PO_PRICE_ADJUSTMENTS.price_adjustment_id%TYPE )
3742 IS
3743 BEGIN
3744 --Delete dependant fields first
3745 --Delete Price Adjustment Attributes
3746 DELETE FROM PO_PRICE_ADJ_ATTRIBS ATTR
3747 WHERE ATTR.price_adjustment_id = p_price_adjustment_id;
3748 /*
3749 --Delete Price Adjustment Associations
3750 DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
3751 WHERE ASOC.price_adjustment_id = p_price_adjustment_id;
3752 */
3753 /*
3754 --Delete Related Price Adjustment Associations
3755 DELETE FROM PO_PRICE_ADJ_ASSOCS ASOC
3756 WHERE ASOC.rltd_price_adj_id = p_price_adjustment_id;
3757 */
3758 --Delete Price Adjustments
3759 DELETE FROM PO_PRICE_ADJUSTMENTS ADJ
3760 WHERE ADJ.price_adjustment_id = p_price_adjustment_id;
3761 EXCEPTION
3762 WHEN OTHERS THEN
3763 PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_adjustment','000',sqlcode);
3764 RAISE;
3765 END delete_adjustment;
3766
3767 /*
3768 -------------------------------------------------------------------------------
3769 -------------------------------------------------------------------------------
3770 --Start of Comments
3771 --Name: delete_adjustment_dependants
3772 --Pre-reqs:
3773 -- None.
3774 --Modifies:
3775 -- PO_PRICE_ADJ_ASSOCS, PO_PRICE_ADJ_ATTRIBS
3776 --Locks:
3777 -- None.
3778 --Function:
3779 -- Deletes the price adjustments dependants of a PO Line or GBPA Line
3780 --Parameters:
3781 --IN:
3782 --p_price_adjustment_id
3783 -- Unique ID of Price Adjustment
3784 --Testing:
3785 -- None.
3786 --End of Comments
3787 -------------------------------------------------------------------------------
3788 -------------------------------------------------------------------------------
3789 PROCEDURE delete_adjustment_dependants
3790 ( p_draft_id IN PO_PRICE_ADJUSTMENTS_DRAFT.draft_id%TYPE
3791 , p_price_adjustment_id IN PO_PRICE_ADJUSTMENTS_DRAFT.price_adjustment_id%TYPE )
3792 IS
3793 BEGIN
3794 --Delete dependant fields first
3795 --Delete Price Adjustment Attributes
3796 DELETE FROM PO_PRICE_ADJ_ATTRIBS_DRAFT ATTR
3797 WHERE ATTR.draft_id = p_draft_id
3798 AND ATTR.price_adjustment_id IN (SELECT p_price_adjustment_id FROM DUAL
3799 UNION
3800 SELECT ASOC.rltd_price_adj_id
3801 FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
3802 WHERE ASOC.price_adjustment_id = p_price_adjustment_id);
3803
3804 DELETE FROM PO_PRICE_ADJUSTMENTS_DRAFT ADJ
3805 WHERE ADJ.draft_id = p_draft_id
3806 AND ADJ.price_adjustment_id IN (SELECT ASOC.rltd_price_adj_id
3807 FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
3808 WHERE ASOC.price_adjustment_id = p_price_adjustment_id);
3809
3810 --Delete Price Adjustment Associations
3811 DELETE FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
3812 WHERE ASOC.draft_id = p_draft_id
3813 AND ASOC.price_adjustment_id = p_price_adjustment_id;
3814
3815 --Delete Related Price Adjustment Associations
3816 DELETE FROM PO_PRICE_ADJ_ASSOCS_DRAFT ASOC
3817 WHERE ASOC.draft_id = p_draft_id
3818 AND ASOC.rltd_price_adj_id = p_price_adjustment_id;
3819
3820 EXCEPTION
3821 WHEN OTHERS THEN
3822 PO_MESSAGE_S.sql_error('PO_PRICE_ADJUSTMENTS_PKG.delete_adjustment_dependants','000',sqlcode);
3823 RAISE;
3824 END delete_adjustment_dependants;
3825 */
3826
3827 END PO_PRICE_ADJUSTMENTS_PKG;