[Home] [Help]
PACKAGE BODY: APPS.PO_ADVANCED_PRICE_PVT
Source
1 PACKAGE BODY PO_ADVANCED_PRICE_PVT AS
2 /* $Header: POXQPRVB.pls 120.12 2010/10/07 22:50:10 rarajar ship $ */
3
4
5 -- Private package constants
6 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_ADVANCED_PRICE_PVT';
7 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
8
9 -- Debugging
10 g_debug_stmt BOOLEAN := PO_DEBUG.is_debug_stmt_on;
11 g_debug_unexp BOOLEAN := PO_DEBUG.is_debug_unexp_on;
12
13 --------------------------------------------------------------------------------
14 -- Forward procedure declarations
15 --------------------------------------------------------------------------------
16
17 PROCEDURE populate_header_record
18 (p_org_id IN NUMBER
19 , p_order_header_id IN NUMBER
20 , p_supplier_id IN NUMBER
21 , p_supplier_site_id IN NUMBER
22 , p_creation_date IN DATE
23 , p_order_type IN VARCHAR2
24 , p_ship_to_location_id IN NUMBER
25 , p_ship_to_org_id IN NUMBER
26 -- <FSC R12 START>
27 -- New Attributes for R12: Receving FSC support
28 , p_shipment_header_id IN NUMBER DEFAULT NULL
29 , p_hazard_class IN VARCHAR2 DEFAULT NULL
30 , p_hazard_code IN VARCHAR2 DEFAULT NULL
31 , p_shipped_date IN DATE DEFAULT NULL
32 , p_shipment_num IN VARCHAR2 DEFAULT NULL
33 , p_carrier_method IN VARCHAR2 DEFAULT NULL
34 , p_packaging_code IN VARCHAR2 DEFAULT NULL
35 , p_freight_carrier_code IN VARCHAR2 DEFAULT NULL
36 , p_freight_terms IN VARCHAR2 DEFAULT NULL
37 , p_currency_code IN VARCHAR2 DEFAULT NULL
38 , p_rate IN NUMBER DEFAULT NULL
39 , p_rate_type IN VARCHAR2 DEFAULT NULL
40 , p_source_org_id IN NUMBER DEFAULT NULL
41 , p_expected_receipt_date IN DATE DEFAULT NULL
42 -- <FSC R12 END>
43 );
44
45 PROCEDURE populate_line_record
46 (p_order_line_id IN NUMBER
47 , p_order_type IN VARCHAR2 DEFAULT NULL--Enhanced Pricing
48 , p_item_revision IN VARCHAR2 -- Bug 3330884
49 , p_item_id IN NUMBER
50 , p_category_id IN NUMBER
51 , p_supplier_item_num IN VARCHAR2
52 , p_agreement_type IN VARCHAR2
53 , p_agreement_id IN NUMBER
54 , p_agreement_line_id IN NUMBER DEFAULT NULL --<R12 GBPA Adv Pricing>
55 , p_supplier_id IN NUMBER
56 , p_supplier_site_id IN NUMBER
57 , p_ship_to_location_id IN NUMBER
58 , p_ship_to_org_id IN NUMBER
59 , p_rate IN NUMBER
60 , p_rate_type IN VARCHAR2
61 , p_currency_code IN VARCHAR2
62 , p_need_by_date IN DATE
63 -- <FSC R12 START>
64 -- New Attributes for R12: Receving FSC support
65 , p_shipment_line_id IN NUMBER DEFAULT NULL
66 , p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL
67 , p_to_organization_id IN NUMBER DEFAULT NULL
68 , p_unit_of_measure IN VARCHAR2 DEFAULT NULL
69 , p_source_document_code IN VARCHAR2 DEFAULT NULL
70 , p_unit_price IN NUMBER DEFAULT NULL -- will not be mapped to any QP attribute
71 , p_quantity IN NUMBER DEFAULT NULL -- will not be mapped to any QP attribute
72 -- <FSC R12 END>
73 );
74 --------------------------------------------------------------------------------
75 -- Procedure definitions
76 --------------------------------------------------------------------------------
77
78 --------------------------------------------------------------------------------
79 --Start of Comments
80 --Name: populate_header_record
81 --Pre-reqs:
82 -- None.
83 --Modifies:
84 -- None.
85 --Locks:
86 -- None.
87 --Function:
88 -- This procedure populates global variable G_HDR.
89 --Parameters:
90 --IN:
91 --p_org_id
92 -- Org ID.
93 --p_order_id
94 -- Order ID: REQUISITION Header ID or PO Header ID.
95 --p_supplier_id
96 -- Supplier ID.
97 --p_supplier_site_id
98 -- Supplier Site ID.
99 --p_creation_date
100 -- Creation date.
101 --p_order_type
102 -- Order type: REQUISITION or PO.
103 --p_ship_to_location_id
104 -- Ship to Location ID.
105 --p_ship_to_org_id
106 -- Ship to Org ID.
107 --p_shipment_header_id
108 -- shipment header id
109 --p_hazard_class
110 -- hazard class
111 --p_hazard_code
112 -- hazard code
113 --p_shipped_date
114 -- shipped date for goods
115 --p_shipment_num
116 -- shipment number
117 --p_carrier_method
118 -- carrier method
119 --p_packaging_code
120 -- packaging code
121 --p_freight_carrier_code
122 -- greight carrier code
123 --p_freight_terms
124 -- freight terms
125 --p_currency_code
126 -- currency code
127 --p_rate
128 -- currency conversion rate
129 --p_rate_type
130 -- rate type
131 --p_expected_receipt_date
132 -- expected receipt date
133 --Testing:
134 --
135 --End of Comments
136 -------------------------------------------------------------------------------
137 PROCEDURE populate_header_record
138 ( p_org_id IN NUMBER
139 , p_order_header_id IN NUMBER
140 , p_supplier_id IN NUMBER
141 , p_supplier_site_id IN NUMBER
142 , p_creation_date IN DATE
143 , p_order_type IN VARCHAR2
144 , p_ship_to_location_id IN NUMBER
145 , p_ship_to_org_id IN NUMBER
146 -- <FSC R12 START>
147 -- New Attributes for R12: Receving FSC support
148 , p_shipment_header_id IN NUMBER DEFAULT NULL
149 , p_hazard_class IN VARCHAR2 DEFAULT NULL
150 , p_hazard_code IN VARCHAR2 DEFAULT NULL
151 , p_shipped_date IN DATE DEFAULT NULL
152 , p_shipment_num IN VARCHAR2 DEFAULT NULL
153 , p_carrier_method IN VARCHAR2 DEFAULT NULL
154 , p_packaging_code IN VARCHAR2 DEFAULT NULL
155 , p_freight_carrier_code IN VARCHAR2 DEFAULT NULL
156 , p_freight_terms IN VARCHAR2 DEFAULT NULL
157 , p_currency_code IN VARCHAR2 DEFAULT NULL
158 , p_rate IN NUMBER DEFAULT NULL
159 , p_rate_type IN VARCHAR2 DEFAULT NULL
160 , p_source_org_id IN NUMBER DEFAULT NULL
161 , p_expected_receipt_date IN DATE DEFAULT NULL
162 -- <FSC R12 END>
163 )
164 IS
165 BEGIN
166 g_hdr.org_id := p_org_id;
167 g_hdr.p_order_header_id := p_order_header_id;
168 g_hdr.supplier_id := p_supplier_id;
169 g_hdr.supplier_site_id := p_supplier_site_id;
170 g_hdr.creation_date := p_creation_date;
171 g_hdr.order_type := p_order_type;
172 g_hdr.ship_to_location_id := p_ship_to_location_id;
173 g_hdr.ship_to_org_id := p_ship_to_org_id;
174 -- <FSC R12 START>
175 g_hdr.shipment_header_id := p_shipment_header_id;
176 g_hdr.hazard_class := p_hazard_class;
177 g_hdr.hazard_code := p_hazard_code;
178 g_hdr.shipped_date := p_shipped_date;
179 g_hdr.shipment_num := p_shipment_num;
180 g_hdr.carrier_method := p_carrier_method;
181 g_hdr.packaging_code := p_packaging_code;
185 g_hdr.rate := p_rate;
182 g_hdr.freight_carrier_code := p_freight_carrier_code;
183 g_hdr.freight_terms := p_freight_terms;
184 g_hdr.currency_code := p_currency_code;
186 g_hdr.rate_type := p_rate_type;
187 g_hdr.source_org_id := p_source_org_id;
188 g_hdr.expected_receipt_date := p_expected_receipt_date;
189 -- <FSC R12 END>
190 END populate_header_record;
191
192 --------------------------------------------------------------------------------
193 --Start of Comments
194 --Name: populate_line_record
195 --Pre-reqs:
196 -- None.
197 --Modifies:
198 -- None.
199 --Locks:
200 -- None.
201 --Function:
202 -- This procedure populates global variable G_LINE.
203 --Parameters:
204 --IN:
205 --p_order_line_id
206 -- Order Line ID: REQUISITION Line ID or PO Line ID.
207 --p_item_revision
208 -- Item Revision.
209 --p_item_id
210 -- Inventory Item ID.
211 --p_category_id
212 -- Category ID.
213 --p_agreement_type
214 -- The type of the source agreement. In 11.5.10, should only be CONTRACT.
215 --p_agreement_id
216 -- The header ID of the source agreement.
217 --p_supplier_id
218 -- Supplier ID.
219 --p_supplier_site_id
220 -- Supplier Site ID.
221 --p_ship_to_location_id
222 -- Ship to Location ID.
223 --p_ship_to_org_id
224 -- Ship to Org ID.
225 --p_rate
226 -- Conversion rate.
227 --p_rate_type
228 -- Conversion rate type.
229 --p_currency_code
230 -- Currency code.
231 --p_need_by_date
232 -- Need by date.
233 --p_shipment_line_id
234 -- Shipment line id
235 --p_primary_unit_of_measure
236 -- primary unit of measure
237 --p_to_organization_id
238 -- destination org id
239 --p_unit_of_measure
240 -- unit of measure
241 --p_source_document_code
242 -- source doc code
243 --p_unit_price
244 -- unit price
245 --p_quantity
246 -- quantity
247 --Testing:
248 --
249 --End of Comments
250 -------------------------------------------------------------------------------
251 PROCEDURE populate_line_record
252 (p_order_line_id IN NUMBER
253 , p_order_type IN VARCHAR2 DEFAULT NULL --Enhanced Pricing added to include order type condition at line level
254 , p_item_revision IN VARCHAR2 -- Bug 3330884
255 , p_item_id IN NUMBER
256 , p_category_id IN NUMBER
257 , p_supplier_item_num IN VARCHAR2
258 , p_agreement_type IN VARCHAR2
259 , p_agreement_id IN NUMBER
260 , p_agreement_line_id IN NUMBER DEFAULT NULL --<R12 GBPA Adv Pricing>
261 , p_supplier_id IN NUMBER
262 , p_supplier_site_id IN NUMBER
263 , p_ship_to_location_id IN NUMBER
264 , p_ship_to_org_id IN NUMBER
265 , p_rate IN NUMBER
266 , p_rate_type IN VARCHAR2
267 , p_currency_code IN VARCHAR2
268 , p_need_by_date IN DATE
269 --<FSC Start R12>
270 , p_shipment_line_id IN NUMBER DEFAULT NULL
271 , p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL
272 , p_to_organization_id IN NUMBER DEFAULT NULL
273 , p_unit_of_measure IN VARCHAR2 DEFAULT NULL
274 , p_source_document_code IN VARCHAR2 DEFAULT NULL
275 , p_unit_price IN NUMBER DEFAULT NULL -- will not be mapped to any QP attribute
276 , p_quantity IN NUMBER DEFAULT NULL -- will not be mapped to any QP attribute
277 --<FSC End R12>
278 )
279 IS
280 BEGIN
281 g_line.order_line_id := p_order_line_id;
282 g_line.order_type := p_order_type; --Enhanced Pricing
283 g_line.item_revision := p_item_revision;
284 g_line.item_id := p_item_id;
285 g_line.category_id := p_category_id;
286 g_line.supplier_item_num := p_supplier_item_num;
287 g_line.agreement_type := p_agreement_type;
288 g_line.agreement_id := p_agreement_id;
289 g_line.agreement_line_id := p_agreement_line_id; --<R12 GBPA Adv Pricing>
290 g_line.supplier_id := p_supplier_id;
291 g_line.supplier_site_id := p_supplier_site_id;
292 g_line.ship_to_location_id := p_ship_to_location_id;
293 g_line.ship_to_org_id := p_ship_to_org_id;
294 g_line.rate := p_rate;
295 g_line.rate_type := p_rate_type;
296 g_line.currency_code := p_currency_code;
297 g_line.need_by_date := p_need_by_date;
298 -- <FSC R12 START>
299 g_line.shipment_line_id := p_shipment_line_id;
300 g_line.primary_unit_of_measure := p_primary_unit_of_measure;
301 g_line.to_organization_id := p_to_organization_id;
302 g_line.unit_of_measure := p_unit_of_measure;
303 g_line.source_document_code := p_source_document_code;
304 g_line.unit_price := p_unit_price;
305 g_line.quantity := p_quantity;
306 -- <FSC R12 END>
307
308 END populate_line_record;
309
310 --------------------------------------------------------------------------------
311 --Start of Comments
312 --Name: get_advanced_price
313 --Pre-reqs:
314 -- None.
315 --Modifies:
316 -- None.
317 --Locks:
318 -- None.
319 --Function:
320 -- This procedure calls Advanced prcing API to get list price and adjustment.
321 --Parameters:
322 --IN:
323 --p_org_id
324 -- Org ID.
325 --p_supplier_id
326 -- Supplier ID.
327 --p_supplier_site_id
328 -- Supplier Site ID.
329 --p_rate
330 -- Conversion rate.
331 --p_rate_type
332 -- Conversion rate type.
333 --p_currency_code
334 -- Currency code.
335 --p_creation_date
336 -- Creation date.
337 --p_order_type
338 -- Order type: REQUISITION or PO.
339 --p_ship_to_location_id
340 -- Ship to Location ID.
341 --p_ship_to_org_id
342 -- Ship to Org ID.
343 --p_order_id
344 -- Order ID: REQUISITION Header ID or PO Header ID.
345 --p_order_line_id
346 -- Order Line ID: REQUISITION Line ID or PO Line ID.
347 --p_item_revision
348 -- Item Revision.
349 --p_item_id
350 -- Inventory Item ID.
351 --p_category_id
352 -- Category ID.
353 --p_supplier_item_num
354 -- Supplier Item Number
355 --p_agreement_type
356 -- The type of the source agreement. In 11.5.10, should only be CONTRACT.
357 --p_agreement_id
358 -- The header ID of the source agreement.
359 --p_price_date
360 -- Price date.
361 --p_quantity
362 -- Quantity.
363 --p_uom
364 -- Unit of Measure.
365 --p_unit_price
366 -- Unit Price.
367 --OUT:
368 --x_base_unit_price
369 -- Base Unit Price.
370 --x_unit_price
371 -- Adjusted Unit Price.
372 --x_return_status
373 -- FND_API.G_RET_STS_SUCCESS if API succeeds
374 -- FND_API.G_RET_STS_ERROR if API fails
375 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
376 --Testing:
377 --
378 --End of Comments
379 -------------------------------------------------------------------------------
380 PROCEDURE get_advanced_price
381 ( p_org_id IN NUMBER
382 , p_supplier_id IN NUMBER
383 , p_supplier_site_id IN NUMBER
384 , p_creation_date IN DATE
385 , p_order_type IN VARCHAR2
386 , p_ship_to_location_id IN NUMBER
387 , p_ship_to_org_id IN NUMBER
388 , p_order_header_id IN NUMBER
389 , p_order_line_id IN NUMBER
390 , p_item_revision IN VARCHAR2 -- Bug 3330884
391 , p_item_id IN NUMBER
392 , p_category_id IN NUMBER
393 , p_supplier_item_num IN VARCHAR2
394 , p_agreement_type IN VARCHAR2
395 , p_agreement_id IN NUMBER
396 , p_agreement_line_id IN NUMBER DEFAULT NULL --<R12 GBPA Adv Pricing>
397 , p_rate IN NUMBER
398 , p_rate_type IN VARCHAR2
399 , p_currency_code IN VARCHAR2
400 , p_need_by_date IN DATE
401 , p_quantity IN NUMBER
402 , p_uom IN VARCHAR2
403 , p_unit_price IN NUMBER
404 --<Enhanced Pricing Start>
405 , p_draft_id IN NUMBER DEFAULT NULL
406 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
407 , p_pricing_call_src IN VARCHAR2 DEFAULT NULL --parameter to identify calls from retro and auto creation
408 --<Enhanced Pricing End>
409 , x_base_unit_price OUT NOCOPY NUMBER
410 , x_unit_price OUT NOCOPY NUMBER
411 , x_return_status OUT NOCOPY VARCHAR2
412 )
413 IS
414 l_api_name CONSTANT VARCHAR2(30) := 'GET_ADVANCED_PRICE';
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 l_qp_license VARCHAR2(30) := NULL;
419 l_qp_license_product VARCHAR2(30) := NULL; /*Added for bug 8762015*/
420 l_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
421
422 --Enhanced Pricing Start:
423 l_order_line_id_tbl QP_PREQ_GRP.NUMBER_TYPE;
424 l_quantity_tbl QP_PREQ_GRP.NUMBER_TYPE;
425 l_pricing_events VARCHAR2(30) := 'PO_BATCH';
426 l_draft_id NUMBER;
427 l_retro_pricing VARCHAR2(1);
428 l_allow_price_override_flag PO_LINES_ALL.allow_price_override_flag%TYPE;
429 --Enhanced Pricing End:
430
431 l_line_id NUMBER := nvl(p_order_line_id, 1);
432 l_return_status_text VARCHAR2(2000);
433 l_control_rec QP_PREQ_GRP.control_record_type;
434 l_pass_line VARCHAR2(1);
435
436 l_line_index_tbl QP_PREQ_GRP.pls_integer_type;
437 l_line_type_code_tbl QP_PREQ_GRP.varchar_type;
438 l_pricinl_effective_date_tbl QP_PREQ_GRP.date_type ;
439 l_active_date_first_tbl QP_PREQ_GRP.date_type ;
440 l_active_date_first_type_tbl QP_PREQ_GRP.varchar_type;
441 l_active_date_second_tbl QP_PREQ_GRP.date_type ;
442 l_active_date_second_type_tbl QP_PREQ_GRP.varchar_type ;
443 l_line_unit_price_tbl QP_PREQ_GRP.number_type ;
444 l_line_quantity_tbl QP_PREQ_GRP.number_type ;
445 l_line_uom_code_tbl QP_PREQ_GRP.varchar_type;
446 l_request_type_code_tbl QP_PREQ_GRP.varchar_type;
447 l_priced_quantity_tbl QP_PREQ_GRP.number_type;
448 l_uom_quantity_tbl QP_PREQ_GRP.number_type;
449 l_priced_uom_code_tbl QP_PREQ_GRP.varchar_type;
450 l_currency_code_tbl QP_PREQ_GRP.varchar_type;
451 l_unit_price_tbl QP_PREQ_GRP.number_type;
452 l_percent_price_tbl QP_PREQ_GRP.number_type;
453 l_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
454 l_upd_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
455 l_processed_flag_tbl QP_PREQ_GRP.varchar_type;
456 l_price_flag_tbl QP_PREQ_GRP.varchar_type;
457 l_line_id_tbl QP_PREQ_GRP.number_type;
458 l_processing_order_tbl QP_PREQ_GRP.pls_integer_type;
459 l_rounding_factor_tbl QP_PREQ_GRP.pls_integer_type;
460 l_rounding_flag_tbl QP_PREQ_GRP.flag_type;
461 l_qualifiers_exist_flag_tbl QP_PREQ_GRP.varchar_type;
462 l_pricing_attrs_exist_flag_tbl QP_PREQ_GRP.varchar_type;
463 l_price_list_id_tbl QP_PREQ_GRP.number_type;
464 l_pl_validated_flag_tbl QP_PREQ_GRP.varchar_type;
465 l_price_request_code_tbl QP_PREQ_GRP.varchar_type;
469 l_pricing_status_text_tbl QP_PREQ_GRP.varchar_type;
466 l_usage_pricing_type_tbl QP_PREQ_GRP.varchar_type;
467 l_line_category_tbl QP_PREQ_GRP.varchar_type;
468 l_pricing_status_code_tbl QP_PREQ_GRP.varchar_type;
470 l_list_price_overide_flag_tbl QP_PREQ_GRP.varchar_type;
471
472 l_price_status_code QP_PREQ_LINES_TMP.pricing_status_code%TYPE;
473 l_price_status_text QP_PREQ_LINES_TMP.pricing_status_text%TYPE;
474
475 -- <Bug 3794940 START>
476 l_return_status VARCHAR2(1);
477 l_msg_count NUMBER;
478 l_msg_data VARCHAR2(2000);
479 -- <Bug 3794940 END>
480
481 BEGIN
482
483 -- Initialize OUT parameters
484 x_return_status := FND_API.G_RET_STS_SUCCESS;
485 x_base_unit_price := p_unit_price;
486 x_unit_price := p_unit_price;
487 l_draft_id := p_draft_id; --Enhanced Pricing
488
489 IF g_debug_stmt THEN
490 PO_DEBUG.debug_begin(l_log_head);
491 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_org_id', p_org_id);
492 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_supplier_id', p_supplier_id);
493 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_supplier_site_id', p_supplier_site_id);
494 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_creation_date', p_creation_date);
495 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_order_type', p_order_type);
496 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_ship_to_location_id', p_ship_to_location_id);
497 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_ship_to_org_id', p_ship_to_org_id);
498 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_order_header_id', p_order_header_id);
499 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_order_line_id', p_order_line_id);
500 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_item_revision', p_item_revision);
501 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_item_id', p_item_id);
502 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_category_id', p_category_id);
503 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_supplier_item_num', p_supplier_item_num);
504 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_agreement_type', p_agreement_type);
505 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_agreement_id', p_agreement_id);
506 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_agreement_line_id', p_agreement_line_id);
507 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_rate', p_rate);
508 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_rate_type', p_rate_type);
509 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_currency_code', p_currency_code);
510 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_need_by_date', p_need_by_date);
511 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_quantity', p_quantity);
512 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_uom', p_uom);
513 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_unit_price', p_unit_price);
514 --<Enhanced Pricing Start>
515 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_draft_id', p_draft_id);
516 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
517 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_pricing_call_src', p_pricing_call_src);
518 --<Enhanced Pricing End>
519 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Check Advanced Pricing License');
520 END IF;
521
522 FND_PROFILE.get('QP_LICENSED_FOR_PRODUCT', l_qp_license);
523 l_qp_license_product := FND_PROFILE.VALUE_SPECIFIC(NAME => 'QP_LICENSED_FOR_PRODUCT',application_id => 201); /*Added for bug 8762015*/
524 l_progress := '020';
525 IF g_debug_stmt THEN
526 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_qp_license', l_qp_license);
527 END IF;
528
529 --Bug 5555953: Remove the logic to nullify the output unitprice if the Adv Pricing API
530 --is not installed or licensed to PO;
531
532 /*Modified the code for bug 8762015*/
533 IF NOT ( ( Nvl(l_qp_license,'X') = 'PO') OR
534 ( Nvl (l_qp_license_product,'X') = 'PO' )
535 ) THEN
536 RETURN;
537 END IF;
538
539 l_progress := '040';
540 IF g_debug_stmt THEN
541 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Set Price Request ID');
542 END IF;
543
544 QP_PRICE_REQUEST_CONTEXT.set_request_id;
545
546 l_progress := '060';
547 IF g_debug_stmt THEN
548 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Populate Global Header Structure');
549 END IF;
550
551 populate_header_record(
552 p_org_id => p_org_id,
553 p_order_header_id => p_order_header_id,
554 p_supplier_id => p_supplier_id,
555 p_supplier_site_id => p_supplier_site_id,
556 p_creation_date => p_creation_date,
557 p_order_type => p_order_type,
558 p_ship_to_location_id => p_ship_to_location_id,
559 p_ship_to_org_id => p_ship_to_org_id);
560
561 l_progress := '080';
562 IF g_debug_stmt THEN
563 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Populate Global Line Structure');
564 END IF;
565
566 populate_line_record(
567 p_order_line_id => p_order_line_id,
568 p_order_type => p_order_type, --Enhanced Pricing
569 p_item_revision => p_item_revision,
570 p_item_id => p_item_id,
571 p_category_id => p_category_id,
572 p_supplier_item_num => p_supplier_item_num,
573 p_agreement_type => p_agreement_type,
574 p_agreement_id => p_agreement_id,
575 p_agreement_line_id => p_agreement_line_id, --<R12 GBPA Adv Pricing>
579 p_ship_to_org_id => p_ship_to_org_id,
576 p_supplier_id => p_supplier_id,
577 p_supplier_site_id => p_supplier_site_id,
578 p_ship_to_location_id => p_ship_to_location_id,
580 p_rate => p_rate,
581 p_rate_type => p_rate_type,
582 p_currency_code => p_currency_code,
583 p_need_by_date => p_need_by_date);
584
585
586 l_progress := '090';
587 IF g_debug_stmt THEN
588 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Set OE Debug');
589 OE_DEBUG_PUB.SetDebugLevel(10);
590 PO_DEBUG.debug_stmt(l_log_head, l_progress, 'Debug File Location:'||
591 OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
592 OE_DEBUG_PUB.Initialize;
593 OE_DEBUG_PUB.Debug_On;
594 END IF;
595
596 l_progress := '100';
597 IF g_debug_stmt THEN
598 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Build Attributes Mapping Contexts');
599 END IF;
600
601 QP_Attr_Mapping_PUB.Build_Contexts(
602 p_request_type_code => 'PO',
603 p_line_index => 1,
604 p_pricing_type_code => 'L',
605 p_check_line_flag => 'N',
606 p_pricing_event => 'PO_BATCH',
607 x_pass_line => l_pass_line);
608
609 l_progress := '110';
610 IF g_debug_stmt THEN
611 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Get UOM Code');
612 END IF;
613
614 BEGIN
615 -- Make sure we pass uom_code instead of unit_of_measure.
616 SELECT mum.uom_code
617 INTO l_uom_code
618 FROM mtl_units_of_measure mum
619 WHERE mum.unit_of_measure = p_uom;
620 EXCEPTION
621 WHEN OTHERS THEN
622 l_uom_code := p_uom;
623 END;
624
625 l_progress := '120';
626 IF g_debug_stmt THEN
627 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_uom_code', l_uom_code);
628 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Directly Insert into Temp table');
629 END IF;
630
631 l_request_type_code_tbl(1) := 'PO';
632 l_line_id_tbl(1) := l_line_id; -- order line id
633 l_line_index_tbl(1) := 1; -- Request Line Index
634 l_line_type_code_tbl(1) := 'LINE'; -- LINE or ORDER(Summary Line)
635 l_pricinl_effective_date_tbl(1) := p_need_by_date;-- Pricing as of effective date
636 l_active_date_first_tbl(1) := NULL; -- Can be Ordered Date or Ship Date
637 l_active_date_second_tbl(1) := NULL; -- Can be Ordered Date or Ship Date
638 l_active_date_first_type_tbl(1) := NULL; -- ORD/SHIP
639 l_active_date_second_type_tbl(1) := NULL; -- ORD/SHIP
640 l_line_unit_price_tbl(1) := p_unit_price;-- Unit Price
641 -- Bug 3315550, should pass 1 instead of NULL
642 l_line_quantity_tbl(1) := NVL(p_quantity, 1);-- Ordered Quantity
643 -- Bug 3564136, don't pass 0, pass 1 instead
644 IF (l_line_quantity_tbl(1) = 0) THEN
645 l_line_quantity_tbl(1) := 1;
646 END IF; /*IF (l_line_quantity_tbl(1) = 0)*/
647
648 l_line_uom_code_tbl(1) := l_uom_code; -- Ordered UOM Code
649 l_currency_code_tbl(1) := p_currency_code;-- Currency Code
650 l_price_flag_tbl(1) := 'Y'; -- Price Flag can have 'Y',
651 -- 'N'(No pricing),
652 -- 'P'(Phase)
653 l_usage_pricing_type_tbl(1) := QP_PREQ_GRP.g_regular_usage_type;
654 -- Bug 3564136, don't pass 0, pass 1 instead
655 -- Bug 3315550, should pass 1 instead of NULL
656 l_priced_quantity_tbl(1) := NVL(p_quantity, 1);
657 -- Bug 3564136, don't pass 0, pass 1 instead
658 IF (l_priced_quantity_tbl(1) = 0) THEN
659 l_priced_quantity_tbl(1) := 1;
660 END IF; /*IF (l_line_quantity_tbl(1) = 0)*/
661 l_priced_uom_code_tbl(1) := l_uom_code;
662 l_unit_price_tbl(1) := p_unit_price;
663 l_percent_price_tbl(1) := null;
664 l_uom_quantity_tbl(1) := null;
665 l_adjusted_unit_price_tbl(1) := null;
666 l_upd_adjusted_unit_price_tbl(1) := null;
667 l_processed_flag_tbl(1) := null;
668 l_processing_order_tbl(1) := null;
669 l_pricing_status_code_tbl(1) := QP_PREQ_GRP.g_status_unchanged;
670 l_pricing_status_text_tbl(1) := null;
671 l_rounding_flag_tbl(1) := null;
672 l_rounding_factor_tbl(1) := null;
673 l_qualifiers_exist_flag_tbl(1) := 'N';
674 l_pricing_attrs_exist_flag_tbl(1) := 'N';
675 l_price_list_id_tbl(1) := - 9999;
676 l_pl_validated_flag_tbl(1) := 'N';
677 l_price_request_code_tbl(1) := null;
678 l_line_category_tbl(1) := null;
679 l_list_price_overide_flag_tbl(1) := 'O'; -- Override price
680
681 l_progress := '140';
682 IF g_debug_stmt THEN
683 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Call INSERT_LINES2');
684 END IF;
685
686 QP_PREQ_GRP.INSERT_LINES2
687 (p_line_index => l_line_index_tbl,
688 p_line_type_code => l_line_type_code_tbl,
689 p_pricing_effective_date => l_pricinl_effective_date_tbl,
690 p_active_date_first => l_active_date_first_tbl,
691 p_active_date_first_type => l_active_date_first_type_tbl,
692 p_active_date_second => l_active_date_second_tbl,
693 p_active_date_second_type => l_active_date_second_type_tbl,
694 p_line_quantity => l_line_quantity_tbl,
695 p_line_uom_code => l_line_uom_code_tbl,
696 p_request_type_code => l_request_type_code_tbl,
697 p_priced_quantity => l_priced_quantity_tbl,
698 p_priced_uom_code => l_priced_uom_code_tbl,
702 p_uom_quantity => l_uom_quantity_tbl,
699 p_currency_code => l_currency_code_tbl,
700 p_unit_price => l_unit_price_tbl,
701 p_percent_price => l_percent_price_tbl,
703 p_adjusted_unit_price => l_adjusted_unit_price_tbl,
704 p_upd_adjusted_unit_price => l_upd_adjusted_unit_price_tbl,
705 p_processed_flag => l_processed_flag_tbl,
706 p_price_flag => l_price_flag_tbl,
707 p_line_id => l_line_id_tbl,
708 p_processing_order => l_processing_order_tbl,
709 p_pricing_status_code => l_pricing_status_code_tbl,
710 p_pricing_status_text => l_pricing_status_text_tbl,
711 p_rounding_flag => l_rounding_flag_tbl,
712 p_rounding_factor => l_rounding_factor_tbl,
713 p_qualifiers_exist_flag => l_qualifiers_exist_flag_tbl,
714 p_pricing_attrs_exist_flag => l_pricing_attrs_exist_flag_tbl,
715 p_price_list_id => l_price_list_id_tbl,
716 p_validated_flag => l_pl_validated_flag_tbl,
717 p_price_request_code => l_price_request_code_tbl,
718 p_usage_pricing_type => l_usage_pricing_type_tbl,
719 p_line_category => l_line_category_tbl,
720 p_line_unit_price => l_line_unit_price_tbl,
721 p_list_price_override_flag => l_list_price_overide_flag_tbl,
722 x_status_code => x_return_status,
723 x_status_text => l_return_status_text);
724
725 l_progress := '160';
726 IF g_debug_stmt THEN
727 PO_DEBUG.debug_stmt(l_log_head, l_progress,'After Calling INSERT_LINES2');
728 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_return_status', x_return_status);
729 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_return_status_text', l_return_status_text);
730 END IF;
731
732 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
733 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
734 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
735 FND_MSG_PUB.Add;
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
738 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
739 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
740 FND_MSG_PUB.Add;
741 RAISE FND_API.G_EXC_ERROR;
742 END IF;
743
744 -- Don't call QP_PREQ_GRP.INSERT_LINE_ATTRS2 since PO has no
745 -- ASK_FOR attributes
746
747 --Enhanced Pricing Start: Call to populate manual and overridden modifiers in QP temp tables
748 --Check if the agreement allows price override
749 IF p_agreement_type = 'BLANKET' AND
750 p_agreement_id IS NOT NULL AND
751 p_agreement_line_id IS NOT NULL THEN
752 SELECT POL.allow_price_override_flag
753 INTO l_allow_price_override_flag
754 FROM po_lines_all POL
755 WHERE POL.po_header_id = p_agreement_id
756 AND POL.po_line_id = p_agreement_line_id;
757 ELSE
758 l_allow_price_override_flag := 'Y';
759 END IF;
760
761 --Initialize In Parameters
762 l_order_line_id_tbl(1) := p_order_line_id;
763 l_quantity_tbl(1) := NVL(p_quantity, 1);
764 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
765 IF NVL(l_allow_price_override_flag, 'Y') = 'Y' AND
766 (p_pricing_call_src IS NULL OR p_pricing_call_src <> 'RETRO') AND
767 (l_draft_id IS NOT NULL OR p_pricing_call_src = 'AUTO') THEN
768 PO_PRICE_ADJUSTMENTS_PKG.popl_manual_overridden_adj
769 (p_draft_id => l_draft_id
770 ,p_order_header_id => p_order_header_id
771 ,p_order_line_id_tbl => l_order_line_id_tbl
772 ,p_quantity_tbl => l_quantity_tbl
773 ,x_return_status => x_return_status
774 );
775 END IF;
776 --Enhanced Pricing End
777
778 l_progress := '180';
779 IF g_debug_stmt THEN
780 PO_DEBUG.debug_stmt(l_log_head, l_progress,
781 'Populate Control Record for Pricing Request Call');
782 END IF;
783
784 l_control_rec.calculate_flag := 'Y';
785 l_control_rec.simulation_flag := 'N';
786 l_control_rec.pricing_event := 'PO_BATCH';
787 l_control_rec.temp_table_insert_flag := 'N';
788 l_control_rec.check_cust_view_flag := 'N';
789 l_control_rec.request_type_code := 'PO';
790 --now pricing take care of all the roundings.
791 l_control_rec.rounding_flag := 'Q';
792 --For multi_currency price list
793 l_control_rec.use_multi_currency := 'Y';
794 l_control_rec.user_conversion_rate := PO_ADVANCED_PRICE_PVT.g_line.rate;
795 l_control_rec.user_conversion_type := PO_ADVANCED_PRICE_PVT.g_line.rate_type;
796 l_control_rec.function_currency := PO_ADVANCED_PRICE_PVT.g_line.currency_code;
797 l_control_rec.get_freight_flag := 'N';
798
799 l_progress := '200';
800 IF g_debug_stmt THEN
801 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Call PRICE_REQUEST');
802 END IF;
803
804 QP_PREQ_PUB.PRICE_REQUEST(
805 p_control_rec => l_control_rec,
806 x_return_status => x_return_status,
807 x_return_status_Text => l_return_status_Text);
808
809 l_progress := '220';
810 IF g_debug_stmt THEN
811 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_return_status', x_return_status);
812 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_return_status_text', l_return_status_text);
813 END IF;
814
815 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
816 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
820 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
817 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
818 FND_MSG_PUB.Add;
819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
821 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
822 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
823 FND_MSG_PUB.Add;
824 RAISE FND_API.G_EXC_ERROR;
825 END IF;
826
827 --Enhanced Pricing Start:
828 --Bug:8598002 renamed p_retro_pricing to p_pricing_call_src, to distinguish calls from retro and auto creation
829 IF p_pricing_call_src IS NOT NULL AND (p_pricing_call_src = 'RETRO' OR p_pricing_call_src = 'AUTO') THEN
830 IF (l_draft_id IS NULL) THEN
831 l_draft_id := 1; --default the draft id, used when merging the changes
832 END IF;
833 END IF;
834
835 IF (l_draft_id IS NOT NULL OR p_pricing_call_src IS NOT NULL) THEN
836 PO_PRICE_ADJUSTMENTS_PKG.extract_price_adjustments
837 (p_draft_id => l_draft_id
838 ,p_order_header_id => p_order_header_id
839 ,p_order_line_id_tbl => l_order_line_id_tbl
840 ,p_pricing_events => l_pricing_events
841 ,p_calculate_flag => l_control_rec.calculate_flag
842 ,p_doc_sub_type => 'PO'
843 ,p_pricing_call_src => p_pricing_call_src
844 ,p_allow_price_override_flag => l_allow_price_override_flag
845 ,x_return_status => x_return_status
846 );
847 END IF;
848 --Exceptions raised by this procedure is handled in the exception section
849 --Enhanced Pricing End:
850
851 -- <Bug 3794940 START>
852 PO_CUSTOM_PRICE_PUB.audit_qp_price_adjustment(
853 p_api_version => 1.0
854 , p_order_type => p_order_type
855 , p_order_line_id => l_line_id
856 , p_line_index => 1
857 , x_return_status => l_return_status
858 , x_msg_count => l_msg_count
859 , x_msg_data => l_msg_data
860 );
861
862 l_progress := '230';
863 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
864 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_return_status', l_return_status);
865 PO_DEBUG.debug_unexp(l_log_head, l_progress,'audit_qp_price_adjustment errors out');
866 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
867 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_msg_data);
868 FND_MSG_PUB.Add;
869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
870 END IF;
871 -- <Bug 3794940 END>
872
873 l_progress := '240';
874 IF g_debug_stmt THEN
875 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Fetch QP pricing');
876 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_line_id', l_line_id);
877 PO_DEBUG.debug_table(l_log_head, l_progress, 'QP_PREQ_LINES_TMP_T', PO_DEBUG.g_all_rows, NULL, 'QP');
878 END IF;
879
880 /* Use API insted
881 -- SQL What: Fetch Price from Pricing Temp table
882 -- SQL Why: Return Advanced Pricing
883 SELECT line_unit_price,
884 adjusted_unit_price,
885 pricing_status_code,
886 pricing_status_text
887 INTO x_base_unit_price,
888 x_unit_price,
889 l_price_status_code,
890 l_price_status_text
891 FROM QP_PREQ_LINES_TMP
892 WHERE line_id = l_line_id
893 AND (processed_code IS NULL OR processed_code <> 'INVALID');
894 */
895
896 QP_PREQ_PUB.get_price_for_line(
897 p_line_index => 1
898 , p_line_id => l_line_id
899 , x_line_unit_price => x_base_unit_price
900 , x_adjusted_unit_price => x_unit_price
901 , x_return_status => x_return_status
902 , x_pricing_status_code => l_price_status_code
903 , x_pricing_status_text => l_price_status_text
904 );
905
906 l_progress := '260';
907 IF g_debug_stmt THEN
908 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_return_status', x_return_status);
909 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_base_unit_price', x_base_unit_price);
910 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_unit_price', x_unit_price);
911 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_price_status_code', l_price_status_code);
912 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_price_status_text', l_price_status_text);
913 END IF;
914
915 x_unit_price := NVL(x_unit_price, x_base_unit_price);
916
917 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
918 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
919 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_price_status_text);
920 x_return_status := FND_API.g_ret_sts_error;
921 END IF;
922
923 l_progress := '300';
924 IF g_debug_stmt THEN
925 PO_DEBUG.debug_end(l_log_head);
926 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_return_status',
927 x_return_status);
928 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_base_unit_price',
929 x_base_unit_price);
930 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_unit_price',
931 x_unit_price);
932 END IF;
933
934 EXCEPTION
935 WHEN FND_API.G_EXC_ERROR THEN
939 , p_encoded => 'F'
936 --raised expected error: assume raiser already pushed onto the stack
937 l_exception_msg := FND_MSG_PUB.get(
938 p_msg_index => FND_MSG_PUB.G_LAST
940 );
941 IF g_debug_unexp THEN
942 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_exception_msg',
943 l_exception_msg);
944 END IF;
945 x_return_status := FND_API.g_ret_sts_error;
946 -- Push the po_return_msg onto msg list and message stack
947 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
948 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
949
950 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
951 --raised unexpected error: assume raiser already pushed onto the stack
952 l_exception_msg := FND_MSG_PUB.get(
953 p_msg_index => FND_MSG_PUB.G_LAST
954 , p_encoded => 'F'
955 );
956 IF g_debug_unexp THEN
957 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_exception_msg',
958 l_exception_msg);
959 END IF;
960 x_return_status := FND_API.g_ret_sts_unexp_error;
961 -- Push the po_return_msg onto msg list and message stack
962 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
963 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
964
965 WHEN OTHERS THEN
966 IF g_debug_unexp THEN
967 PO_DEBUG.debug_exc(l_log_head, l_progress);
968 END IF;
969 --unexpected error from this procedure: get SQLERRM
970 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
971 l_exception_msg := FND_MESSAGE.get;
972 IF g_debug_unexp THEN
973 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_exception_msg',
974 l_exception_msg);
975 END IF;
976 x_return_status := FND_API.g_ret_sts_unexp_error;
977 -- Push the po_return_msg onto msg list and message stack
978 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
979 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
980 END get_advanced_price;
981
982
983 --------------------------------------------------------------------------------
984 --Start of Comments
985 --Name: is_valid_qp_line_type
986 --Pre-reqs:
987 -- None.
988 --Modifies:
989 -- None.
990 --Locks:
991 -- None.
992 --Function:
993 -- This procedure checks valid line type for advanced pricing call.
994 -- Any Line type with combination of Value Basis (Amount/Rate/Fixed Price)
995 -- and Purchase Basis (Temp Labor/Services) is invalid.
996 --Parameters:
997 --IN:
998 --p_line_type_id
999 -- Line Type ID.
1000 --RETURN:
1001 -- FALSE: Invalid Line type to call Advanced Pricing API
1002 -- TRUE: Valid Line type to call Advanced Pricing API
1003 --Testing:
1004 --
1005 --End of Comments
1006 -------------------------------------------------------------------------------
1007 FUNCTION is_valid_qp_line_type
1008 (p_line_type_id IN NUMBER
1009 ) RETURN BOOLEAN
1010 IS
1011 l_api_name CONSTANT VARCHAR2(30) := 'IS_VALID_QP_LINE_TYPE';
1012 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1013 l_progress VARCHAR2(3) := '000';
1014 l_purchase_basis PO_LINE_TYPES.purchase_basis%TYPE;
1015 -- Bug 3343261: should use value_basis instead of matching_basis
1016 -- l_matching_basis PO_LINE_TYPES.matching_basis%TYPE;
1017 l_value_basis PO_LINE_TYPES.order_type_lookup_code%TYPE;
1018 BEGIN
1019 IF g_debug_stmt THEN
1020 PO_DEBUG.debug_begin(l_log_head);
1021 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_line_type_id', p_line_type_id);
1022 END IF;
1023
1024 IF (p_line_type_id IS NULL) THEN
1025 RETURN TRUE;
1026 END IF;
1027
1028 l_progress := '010';
1029 SELECT purchase_basis,
1030 -- Bug 3343261: should use value_basis instead of matching_basis
1031 -- matching_basis,
1032 order_type_lookup_code
1033 INTO l_purchase_basis,
1034 -- Bug 3343261: should use value_basis instead of matching_basis
1035 -- l_matching_basis,
1036 l_value_basis
1037 FROM PO_LINE_TYPES
1038 WHERE line_type_id = p_line_type_id;
1039
1040 l_progress := '020';
1041 IF g_debug_stmt THEN
1042 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Retrieved line type');
1043 END IF;
1044
1045 IF (l_purchase_basis IN ('TEMP LABOR', 'SERVICES') AND
1046 -- Bug 3343261: should use value_basis instead of matching_basis
1047 l_value_basis IN ('AMOUNT', 'RATE', 'FIXED PRICE')) THEN
1048 RETURN FALSE;
1049 END IF;
1050
1051 l_progress := '030';
1052 IF g_debug_stmt THEN
1053 PO_DEBUG.debug_end(l_log_head);
1054 END IF;
1055
1056 RETURN TRUE;
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 RETURN FALSE;
1060 END is_valid_qp_line_type;
1061
1062 -- <FSC R12 START>
1063 --------------------------------------------------------------------------------
1064 --Start of Comments
1065 --Name: get_advanced_price
1066 --Pre-reqs:
1067 -- None.
1068 --Modifies:
1069 -- None.
1070 --Locks:
1071 -- None.
1072 --Procedure:
1073 -- This procedure calls out to QP for getting the adjusted price and
1077 --IN:
1074 -- freight and speacial charges. This takes in a document in the form of
1075 -- header record and a table of line records
1076 --Parameters:
1078 -- p_header_rec Header_Rec_Type This will keep the document header information
1079 -- p_line_rec_tbl Line_tbl_type This willl keep the table of the lines for the
1080 -- p_request_type Request type to be passed to QP.
1081 -- p_pricing_event pricing event set up in QP for the processing
1082 -- p_has_header_pricing True, when header line is also included in pricing
1083 -- p_return_price_flag True, when the caller wants the adjusted rice info
1084 -- also in the returned record
1085 -- p_return_freight_flag True, when the caller wants the API to return
1086 -- freight charge related info.
1087 --
1088 --OUT:
1089 -- x_return_status Return status for the QP call.
1090 -- x_price_tbl Table of resulted info from QP for each line
1091 --Testing:
1092 --
1093 --End of Comments
1094 -------------------------------------------------------------------------------
1095 PROCEDURE get_advanced_price(p_header_rec Header_rec_type,
1096 p_line_rec_tbl Line_Tbl_Type,
1097 p_request_type IN VARCHAR2,
1098 p_pricing_event IN VARCHAR2,
1099 p_has_header_pricing IN BOOLEAN,
1100 p_return_price_flag IN BOOLEAN,
1101 p_return_freight_flag IN BOOLEAN,
1102 x_price_tbl OUT NOCOPY Qp_Price_Result_Rec_Tbl_Type,
1103 x_return_status OUT NOCOPY VARCHAR2) IS
1104 l_api_name CONSTANT VARCHAR2(30) := 'GET_ADVANCED_PRICE';
1105 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1106 d_pos NUMBER;
1107 l_exception_msg FND_NEW_MESSAGES.message_text%TYPE;
1108 l_qp_license VARCHAR2(30) := NULL;
1109 l_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
1110 l_return_status_text VARCHAR2(2000);
1111 l_control_rec QP_PREQ_GRP.control_record_type;
1112 l_pass_line VARCHAR2(1);
1113 l_line_index_tbl QP_PREQ_GRP.pls_integer_type;
1114 l_line_type_code_tbl QP_PREQ_GRP.varchar_type;
1115 l_pricinl_effective_date_tbl QP_PREQ_GRP.date_type ;
1116 l_active_date_first_tbl QP_PREQ_GRP.date_type ;
1117 l_active_date_first_type_tbl QP_PREQ_GRP.varchar_type;
1118 l_active_date_second_tbl QP_PREQ_GRP.date_type ;
1119 l_active_date_second_type_tbl QP_PREQ_GRP.varchar_type ;
1120 l_line_unit_price_tbl QP_PREQ_GRP.number_type ;
1121 l_line_quantity_tbl QP_PREQ_GRP.number_type ;
1122 l_line_uom_code_tbl QP_PREQ_GRP.varchar_type;
1123 l_request_type_code_tbl QP_PREQ_GRP.varchar_type;
1124 l_priced_quantity_tbl QP_PREQ_GRP.number_type;
1125 l_uom_quantity_tbl QP_PREQ_GRP.number_type;
1126 l_priced_uom_code_tbl QP_PREQ_GRP.varchar_type;
1127 l_currency_code_tbl QP_PREQ_GRP.varchar_type;
1128 l_unit_price_tbl QP_PREQ_GRP.number_type;
1129 l_percent_price_tbl QP_PREQ_GRP.number_type;
1130 l_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
1131 l_upd_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
1132 l_processed_flag_tbl QP_PREQ_GRP.varchar_type;
1133 l_price_flag_tbl QP_PREQ_GRP.varchar_type;
1134 l_line_id_tbl QP_PREQ_GRP.number_type;
1135 l_processing_order_tbl QP_PREQ_GRP.pls_integer_type;
1136 l_rounding_factor_tbl QP_PREQ_GRP.pls_integer_type;
1137 l_rounding_flag_tbl QP_PREQ_GRP.flag_type;
1138 l_qualifiers_exist_flag_tbl QP_PREQ_GRP.varchar_type;
1139 l_pricing_attrs_exist_flag_tbl QP_PREQ_GRP.varchar_type;
1140 l_price_list_id_tbl QP_PREQ_GRP.number_type;
1141 l_pl_validated_flag_tbl QP_PREQ_GRP.varchar_type;
1142 l_price_request_code_tbl QP_PREQ_GRP.varchar_type;
1143 l_usage_pricing_type_tbl QP_PREQ_GRP.varchar_type;
1144 l_line_category_tbl QP_PREQ_GRP.varchar_type;
1145 l_pricing_status_code_tbl QP_PREQ_GRP.varchar_type;
1146 l_pricing_status_text_tbl QP_PREQ_GRP.varchar_type;
1147 l_list_price_overide_flag_tbl QP_PREQ_GRP.varchar_type;
1148 i PLS_INTEGER := 1;
1149 j PLS_INTEGER := 1;
1150 k PLS_INTEGER := 1;
1151 l_freight_charge_rec_tbl Freight_Charges_Rec_Tbl_Type;
1152 BEGIN
1153
1154 -- Initialize OUT parameters
1155 x_return_status := FND_API.G_RET_STS_SUCCESS;
1156 IF g_debug_stmt THEN
1157 PO_LOG.proc_begin(l_log_head);
1158 PO_LOG.proc_begin(l_log_head, 'p_header_rec.org_id', p_header_rec.org_id);
1159 PO_LOG.proc_begin(l_log_head, 'p_header_rec.p_order_header_id', p_header_rec.p_order_header_id);
1160 PO_LOG.proc_begin(l_log_head, 'p_header_rec.supplier_id', p_header_rec.supplier_id);
1161 PO_LOG.proc_begin(l_log_head, 'p_header_rec.supplier_site_id', p_header_rec.supplier_site_id);
1162 PO_LOG.proc_begin(l_log_head, 'p_header_rec.creation_date', p_header_rec.creation_date);
1163 PO_LOG.proc_begin(l_log_head, 'p_header_rec.order_type', p_header_rec.order_type);
1164 PO_LOG.proc_begin(l_log_head, 'p_header_rec.ship_to_location_id', p_header_rec.ship_to_location_id);
1165 PO_LOG.proc_begin(l_log_head, 'p_header_rec.ship_to_org_id', p_header_rec.ship_to_org_id);
1166 PO_LOG.proc_begin(l_log_head, 'p_header_rec.shipment_header_id', p_header_rec.shipment_header_id);
1167 PO_LOG.proc_begin(l_log_head, 'p_header_rec.hazard_class', p_header_rec.hazard_class);
1168 PO_LOG.proc_begin(l_log_head, 'p_header_rec.hazard_code', p_header_rec.hazard_code);
1169 PO_LOG.proc_begin(l_log_head, 'p_header_rec.shipped_date', p_header_rec.shipped_date);
1170 PO_LOG.proc_begin(l_log_head, 'p_header_rec.shipment_num', p_header_rec.shipment_num);
1171 PO_LOG.proc_begin(l_log_head, 'p_header_rec.carrier_method', p_header_rec.carrier_method);
1172 PO_LOG.proc_begin(l_log_head, 'p_header_rec.packaging_code', p_header_rec.packaging_code);
1173 PO_LOG.proc_begin(l_log_head, 'p_header_rec.freight_carrier_code', p_header_rec.freight_carrier_code);
1177 PO_LOG.proc_begin(l_log_head, 'p_header_rec.rate_type', p_header_rec.rate_type);
1174 PO_LOG.proc_begin(l_log_head, 'p_header_rec.freight_terms', p_header_rec.freight_terms);
1175 PO_LOG.proc_begin(l_log_head, 'p_header_rec.currency_code', p_header_rec.currency_code);
1176 PO_LOG.proc_begin(l_log_head, 'p_header_rec.rate', p_header_rec.rate);
1178
1179
1180 FOR i IN 1..p_line_rec_tbl.COUNT LOOP
1181 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').order_line_id', p_line_rec_tbl(i).order_line_id);
1182 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').order_type', p_line_rec_tbl(i).order_type); --Enhanced Pricing
1183 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').agreement_type', p_line_rec_tbl(i).agreement_type);
1184 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').agreement_id', p_line_rec_tbl(i).agreement_id);
1185 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').agreement_line_id', p_line_rec_tbl(i).agreement_line_id);
1186 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').supplier_id', p_line_rec_tbl(i).supplier_id);
1187 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').supplier_site_id', p_line_rec_tbl(i).supplier_site_id);
1188 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').ship_to_location_id', p_line_rec_tbl(i). ship_to_location_id);
1189 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').ship_to_org_id', p_line_rec_tbl(i).ship_to_org_id);
1190 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').supplier_item_num', p_line_rec_tbl(i).supplier_item_num);
1191 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').item_revision', p_line_rec_tbl(i).item_revision);
1192 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').item_id', p_line_rec_tbl(i).item_id);
1193 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').category_id', p_line_rec_tbl(i).category_id);
1194 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').rate', p_line_rec_tbl(i).rate);
1195 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').rate_type', p_line_rec_tbl(i).rate_type);
1196 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').currency_code', p_line_rec_tbl(i).currency_code);
1197 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').need_by_date', p_line_rec_tbl(i).need_by_date);
1198 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').shipment_line_id', p_line_rec_tbl(i).shipment_line_id);
1199 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').primary_unit_of_measure', p_line_rec_tbl(i).primary_unit_of_measure);
1200 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').to_organization_id', p_line_rec_tbl(i).to_organization_id);
1201 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').unit_of_measure', p_line_rec_tbl(i).unit_of_measure);
1202 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').source_document_code', p_line_rec_tbl(i).source_document_code);
1203 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').unit_price', p_line_rec_tbl(i).unit_price);
1204 PO_LOG.proc_begin(l_log_head, 'p_line_rec_tbl(' || i || ').quantity', p_line_rec_tbl(i).quantity);
1205 END LOOP;
1206
1207 PO_LOG.proc_begin(l_log_head, 'p_request_type', p_request_type);
1208 PO_LOG.proc_begin(l_log_head, 'p_pricing_event', p_pricing_event);
1209 PO_LOG.proc_begin(l_log_head, 'p_has_header_pricing', p_has_header_pricing);
1210 PO_LOG.proc_begin(l_log_head, 'p_return_price_flag', p_return_price_flag);
1211 PO_LOG.proc_begin(l_log_head, 'p_return_freight_flag', p_return_freight_flag);
1212 END IF;
1213
1214 x_price_tbl := Qp_Price_Result_Rec_Tbl_Type();
1215 PO_LOG.stmt(l_log_head, d_pos,'Check Advanced Pricing License');
1216 FND_PROFILE.get('QP_LICENSED_FOR_PRODUCT', l_qp_license);
1217 d_pos := 20;
1218 IF g_debug_stmt THEN
1219 PO_LOG.stmt(l_log_head, d_pos, 'l_qp_license', l_qp_license);
1220 END IF;
1221
1222 IF (l_qp_license IS NULL OR l_qp_license <> 'PO') THEN
1223 RETURN;
1224 END IF;
1225
1226 d_pos := 40;
1227 IF g_debug_stmt THEN
1228 PO_LOG.stmt(l_log_head, d_pos,'Set Price Request ID');
1229 END IF;
1230
1231 QP_PRICE_REQUEST_CONTEXT.set_request_id;
1232
1233 d_pos := 60;
1234 IF g_debug_stmt THEN
1235 PO_LOG.stmt(l_log_head, d_pos,'Populate Global Header Structure');
1236 END IF;
1237
1238 populate_header_record(
1239 p_org_id => p_header_rec.org_id,
1240 p_order_header_id => p_header_rec.p_order_header_id,
1241 p_supplier_id => p_header_rec.supplier_id,
1242 p_supplier_site_id => p_header_rec.supplier_site_id,
1243 p_creation_date => p_header_rec.creation_date,
1244 p_order_type => p_header_rec.order_type,
1245 p_ship_to_location_id => p_header_rec.ship_to_location_id,
1246 p_ship_to_org_id => p_header_rec.ship_to_org_id,
1247 -- New Attributes for R12: Receving FSC support
1248 p_shipment_header_id => p_header_rec.shipment_header_id,
1249 p_hazard_class => p_header_rec.hazard_class,
1250 p_hazard_code => p_header_rec.hazard_code,
1251 p_shipped_date => p_header_rec.shipped_date,
1252 p_shipment_num => p_header_rec.shipment_num,
1253 p_carrier_method => p_header_rec.carrier_method,
1254 p_packaging_code => p_header_rec.packaging_code,
1255 p_freight_carrier_code => p_header_rec.freight_carrier_code,
1256 p_freight_terms => p_header_rec.freight_terms,
1257 p_currency_code => p_header_rec.currency_code,
1258 p_rate => p_header_rec.rate,
1259 p_rate_type => p_header_rec.rate_type,
1263 d_pos := 80;
1260 p_source_org_id => p_header_rec.source_org_id,
1261 p_expected_receipt_date => p_header_rec.expected_receipt_date);
1262
1264 IF g_debug_stmt THEN
1265 PO_LOG.stmt(l_log_head, d_pos ,'Populate Global Line Structure');
1266 END IF;
1267
1268 i := 1;
1269
1270 d_pos := 90;
1271 IF g_debug_stmt THEN
1272 PO_LOG.stmt(l_log_head, d_pos,'Set OE Debug');
1273 OE_DEBUG_PUB.SetDebugLevel(10);
1274 PO_LOG.stmt(l_log_head, d_pos, 'Debug File Location: '|| OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
1275 OE_DEBUG_PUB.Initialize;
1276 OE_DEBUG_PUB.Debug_On;
1277 END IF;
1278
1279 d_pos := 100;
1280
1281 IF(p_has_header_pricing) THEN
1282 d_pos := 110;
1283 IF g_debug_stmt THEN
1284 PO_LOG.stmt(l_log_head, d_pos,'Build Attributes Mapping Contexts for header');
1285 END IF;
1286 QP_Attr_Mapping_PUB.Build_Contexts(p_request_type_code => p_request_type,
1287 p_line_index => i,
1288 p_pricing_type_code => 'H',
1289 p_check_line_flag => 'N',
1290 p_pricing_event => p_pricing_event,
1291 x_pass_line => l_pass_line);
1292 d_pos := 120;
1293
1294 l_request_type_code_tbl(i) := p_request_type;
1295 l_line_id_tbl(i) := nvl(p_header_rec.p_order_header_id, p_header_rec.shipment_header_id);-- header id
1296 l_line_index_tbl(i) := i; -- Request Line Index
1297 l_line_type_code_tbl(i) := 'ORDER'; -- LINE or ORDER(Summary Line)
1298 l_pricinl_effective_date_tbl(i) := SYSDATE;-- Pricing as of effective date
1299 l_active_date_first_tbl(i) := null; -- Can be Ordered Date or Ship Date
1300 l_active_date_second_tbl(i) := null; -- Can be Ordered Date or Ship Date
1301 l_active_date_first_type_tbl(i) := null; -- ORD/SHIP
1302 l_active_date_second_type_tbl(i) := null; -- ORD/SHIP
1303 l_line_unit_price_tbl(i) := null;-- Unit Price
1304 l_line_quantity_tbl(i) := null;-- Ordered Quantity
1305 l_line_uom_code_tbl(i) := null;-- Ordered UOM Code
1306 l_currency_code_tbl(i) := p_header_rec.currency_code;-- Currency Code
1307 l_price_flag_tbl(i) := 'Y'; -- Price Flag can have 'Y',
1308 -- 'N'(No pricing),
1309 -- 'P'(Phase)
1310 l_usage_pricing_type_tbl(i) := QP_PREQ_GRP.g_regular_usage_type;
1311 l_priced_quantity_tbl(i) := null;
1312 l_priced_uom_code_tbl(i) := null;
1313 l_unit_price_tbl(i) := null;
1314 l_percent_price_tbl(i) := null;
1315 l_uom_quantity_tbl(i) := null;
1316 l_adjusted_unit_price_tbl(i) := null;
1317 l_upd_adjusted_unit_price_tbl(i) := null;
1318 l_processed_flag_tbl(i) := null;
1319 l_processing_order_tbl(i) := null;
1320 l_pricing_status_code_tbl(i) := QP_PREQ_GRP.g_status_unchanged;
1321 l_pricing_status_text_tbl(i) := null;
1322 l_rounding_flag_tbl(i) := null;
1323 l_rounding_factor_tbl(i) := null;
1324 l_qualifiers_exist_flag_tbl(i) := 'N';
1325 l_pricing_attrs_exist_flag_tbl(i) := 'N';
1326 l_price_list_id_tbl(i) := - 9999;
1327 l_pl_validated_flag_tbl(i) := 'N';
1328 l_price_request_code_tbl(i) := null;
1329 l_line_category_tbl(i) := null;
1330 l_list_price_overide_flag_tbl(i) := 'O'; -- Override price
1331
1332 i := i + 1;
1333 d_pos := 130;
1334 END IF;
1335
1336
1337 FOR j IN 1..p_line_rec_tbl.COUNT LOOP
1338 populate_line_record(
1339 p_order_line_id => p_line_rec_tbl(j).order_line_id,
1340 p_order_type => p_line_rec_tbl(j).order_type, --Enhanced Pricing
1341 p_item_revision => p_line_rec_tbl(j).item_revision,
1342 p_item_id => p_line_rec_tbl(j).item_id,
1343 p_category_id => p_line_rec_tbl(j).category_id,
1344 p_supplier_item_num => p_line_rec_tbl(j).supplier_item_num,
1345 p_agreement_type => p_line_rec_tbl(j).agreement_type,
1346 p_agreement_id => p_line_rec_tbl(j).agreement_id,
1347 p_agreement_line_id => p_line_rec_tbl(j).agreement_line_id, --<R12 GBPA Adv Pricing>
1348 p_supplier_id => p_line_rec_tbl(j).supplier_id,
1349 p_supplier_site_id => p_line_rec_tbl(j).supplier_site_id,
1350 p_ship_to_location_id => p_line_rec_tbl(j).ship_to_location_id,
1351 p_ship_to_org_id => p_line_rec_tbl(j).ship_to_org_id,
1352 p_rate => p_line_rec_tbl(j).rate,
1353 p_rate_type => p_line_rec_tbl(j).rate_type,
1354 p_currency_code => p_line_rec_tbl(j).currency_code,
1355 p_need_by_date => p_line_rec_tbl(j).need_by_date,
1356 -- New Attributes for R12: Receving FSC support
1357 p_shipment_line_id => p_line_rec_tbl(j).shipment_line_id,
1358 p_primary_unit_of_measure => p_line_rec_tbl(j).primary_unit_of_measure,
1359 p_to_organization_id => p_line_rec_tbl(j).to_organization_id,
1360 p_unit_of_measure => p_line_rec_tbl(j).unit_of_measure,
1361 p_source_document_code => p_line_rec_tbl(j).source_document_code,
1365
1362 p_quantity => p_line_rec_tbl(j).quantity);
1363
1364 d_pos := 140;
1366 IF g_debug_stmt THEN
1367 --make the pricing debug ON
1368 PO_DEBUG.debug_stmt(l_log_head, d_pos,'Set OE Debug');
1369 OE_DEBUG_PUB.SetDebugLevel(10);
1370 PO_DEBUG.debug_stmt(l_log_head, d_pos, 'Debug File Location: '||
1371 OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
1372 OE_DEBUG_PUB.Initialize;
1373 OE_DEBUG_PUB.Debug_On;
1374 END IF;
1375
1376 d_pos := 150;
1377 IF g_debug_stmt THEN
1378 PO_LOG.stmt(l_log_head, d_pos,'Build Attributes Mapping Contexts for Line('|| j || ')');
1379 END IF;
1380
1381 QP_Attr_Mapping_PUB.Build_Contexts(
1382 p_request_type_code => p_request_type,
1383 p_line_index => i,
1384 p_pricing_type_code => 'L',
1385 p_check_line_flag => 'N',
1386 p_pricing_event => p_pricing_event,
1387 x_pass_line => l_pass_line);
1388
1389 d_pos := 160;
1390 IF g_debug_stmt THEN
1391 PO_LOG.stmt(l_log_head, d_pos,'Get UOM Code');
1392 END IF;
1393
1394 BEGIN
1395 -- Make sure we pass uom_code instead of unit_of_measure.
1396 SELECT mum.uom_code
1397 INTO l_uom_code
1398 FROM mtl_units_of_measure mum
1399 WHERE mum.unit_of_measure = p_line_rec_tbl(j).unit_of_measure;
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 l_uom_code := p_line_rec_tbl(j).unit_of_measure;
1403 END;
1404
1405 d_pos := 170;
1406 IF g_debug_stmt THEN
1407 PO_LOG.stmt(l_log_head, d_pos, 'l_uom_code', l_uom_code);
1408 PO_LOG.stmt(l_log_head, d_pos,'Directly Insert into Temp table');
1409 END IF;
1410
1411 l_request_type_code_tbl(i) := p_request_type;
1412 l_line_id_tbl(i) := nvl(p_line_rec_tbl(j).order_line_id, p_line_rec_tbl(j).shipment_line_id); -- order line id
1413 l_line_index_tbl(i) := i; -- Request Line Index
1414 l_line_type_code_tbl(i) := 'LINE'; -- LINE or ORDER(Summary Line)
1415 l_pricinl_effective_date_tbl(i) := p_line_rec_tbl(j).need_by_date;-- Pricing as of effective date
1416 l_active_date_first_tbl(i) := NULL; -- Can be Ordered Date or Ship Date
1417 l_active_date_second_tbl(i) := NULL; -- Can be Ordered Date or Ship Date
1418 l_active_date_first_type_tbl(i) := NULL; -- ORD/SHIP
1419 l_active_date_second_type_tbl(i) := NULL; -- ORD/SHIP
1420 l_line_unit_price_tbl(i) := p_line_rec_tbl(j).unit_price;-- Unit Price
1421 l_line_quantity_tbl(i) := NVL(p_line_rec_tbl(j).quantity, 1);-- Ordered Quantity
1422
1423 IF (l_line_quantity_tbl(i) = 0) THEN
1424 l_line_quantity_tbl(i) := 1;
1425 END IF;
1426
1427 l_line_uom_code_tbl(i) := l_uom_code; -- Ordered UOM Code
1428 l_currency_code_tbl(i) := p_line_rec_tbl(j).currency_code;-- Currency Code
1429 l_price_flag_tbl(i) := 'Y'; -- Price Flag can have 'Y',
1430 -- 'N'(No pricing),
1431 -- 'P'(Phase)
1432 l_usage_pricing_type_tbl(i) := QP_PREQ_GRP.g_regular_usage_type;
1433 l_priced_quantity_tbl(i) := NVL(p_line_rec_tbl(j).quantity, 1);
1434 IF (l_priced_quantity_tbl(i) = 0) THEN
1435 l_priced_quantity_tbl(i) := 1;
1436 END IF;
1437 l_priced_uom_code_tbl(i) := l_uom_code;
1438 l_unit_price_tbl(i) := p_line_rec_tbl(j).unit_price;
1439 l_percent_price_tbl(i) := null;
1440 l_uom_quantity_tbl(i) := null;
1441 l_adjusted_unit_price_tbl(i) := null;
1442 l_upd_adjusted_unit_price_tbl(i) := null;
1443 l_processed_flag_tbl(i) := null;
1444 l_processing_order_tbl(i) := null;
1445 l_pricing_status_code_tbl(i) := QP_PREQ_GRP.g_status_unchanged;
1446 l_pricing_status_text_tbl(i) := null;
1447 l_rounding_flag_tbl(i) := null;
1448 l_rounding_factor_tbl(i) := null;
1449 l_qualifiers_exist_flag_tbl(i) := 'N';
1450 l_pricing_attrs_exist_flag_tbl(i) := 'N';
1451 l_price_list_id_tbl(i) := - 9999;
1452 l_pl_validated_flag_tbl(i) := 'N';
1453 l_price_request_code_tbl(i) := null;
1454 l_line_category_tbl(i) := null;
1455 l_list_price_overide_flag_tbl(i) := 'O'; -- Override price
1456
1457 i := i + 1;
1458
1459 END LOOP;
1460
1461 d_pos := 180;
1462 IF g_debug_stmt THEN
1463 PO_LOG.stmt(l_log_head, d_pos,'Call INSERT_LINES2');
1464 END IF;
1465
1466 QP_PREQ_GRP.INSERT_LINES2
1467 (p_line_index => l_line_index_tbl,
1468 p_line_type_code => l_line_type_code_tbl,
1469 p_pricing_effective_date => l_pricinl_effective_date_tbl,
1470 p_active_date_first => l_active_date_first_tbl,
1471 p_active_date_first_type => l_active_date_first_type_tbl,
1472 p_active_date_second => l_active_date_second_tbl,
1473 p_active_date_second_type => l_active_date_second_type_tbl,
1474 p_line_quantity => l_line_quantity_tbl,
1475 p_line_uom_code => l_line_uom_code_tbl,
1476 p_request_type_code => l_request_type_code_tbl,
1477 p_priced_quantity => l_priced_quantity_tbl,
1478 p_priced_uom_code => l_priced_uom_code_tbl,
1479 p_currency_code => l_currency_code_tbl,
1480 p_unit_price => l_unit_price_tbl,
1481 p_percent_price => l_percent_price_tbl,
1485 p_processed_flag => l_processed_flag_tbl,
1482 p_uom_quantity => l_uom_quantity_tbl,
1483 p_adjusted_unit_price => l_adjusted_unit_price_tbl,
1484 p_upd_adjusted_unit_price => l_upd_adjusted_unit_price_tbl,
1486 p_price_flag => l_price_flag_tbl,
1487 p_line_id => l_line_id_tbl,
1488 p_processing_order => l_processing_order_tbl,
1489 p_pricing_status_code => l_pricing_status_code_tbl,
1490 p_pricing_status_text => l_pricing_status_text_tbl,
1491 p_rounding_flag => l_rounding_flag_tbl,
1492 p_rounding_factor => l_rounding_factor_tbl,
1493 p_qualifiers_exist_flag => l_qualifiers_exist_flag_tbl,
1494 p_pricing_attrs_exist_flag => l_pricing_attrs_exist_flag_tbl,
1495 p_price_list_id => l_price_list_id_tbl,
1496 p_validated_flag => l_pl_validated_flag_tbl,
1497 p_price_request_code => l_price_request_code_tbl,
1498 p_usage_pricing_type => l_usage_pricing_type_tbl,
1499 p_line_category => l_line_category_tbl,
1500 p_line_unit_price => l_line_unit_price_tbl,
1501 p_list_price_override_flag => l_list_price_overide_flag_tbl,
1502 x_status_code => x_return_status,
1503 x_status_text => l_return_status_text);
1504
1505 d_pos := 190;
1506 IF g_debug_stmt THEN
1507 PO_LOG.stmt(l_log_head, d_pos,'After Calling INSERT_LINES2');
1508 PO_LOG.stmt(l_log_head, d_pos, 'x_return_status', x_return_status);
1509 PO_LOG.stmt(l_log_head, d_pos, 'l_return_status_text', l_return_status_text);
1510 END IF;
1511
1512 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1513 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
1514 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
1515 FND_MSG_PUB.Add;
1516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1517 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1518 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
1519 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
1520 FND_MSG_PUB.Add;
1521 RAISE FND_API.G_EXC_ERROR;
1522 END IF;
1523
1524 -- Don't call QP_PREQ_GRP.INSERT_LINE_ATTRS2 since PO has no
1525 -- ASK_FOR attributes
1526
1527 d_pos := 200;
1528 IF g_debug_stmt THEN
1529 PO_LOG.stmt(l_log_head, d_pos,'Populate Control Record for Pricing Request Call');
1530 END IF;
1531
1532 l_control_rec.calculate_flag := 'Y';
1533 l_control_rec.simulation_flag := 'N';
1534 l_control_rec.pricing_event := p_pricing_event;
1535 l_control_rec.temp_table_insert_flag := 'N';
1536 l_control_rec.check_cust_view_flag := 'N';
1537 l_control_rec.request_type_code := p_request_type;
1538 --now pricing take care of all the roundings.
1539 l_control_rec.rounding_flag := 'Q';
1540 --For multi_currency price list
1541 l_control_rec.use_multi_currency := 'Y';
1542 l_control_rec.user_conversion_rate := p_header_rec.rate;
1543 l_control_rec.user_conversion_type := p_header_rec.rate_type;
1544 l_control_rec.function_currency := p_header_rec.currency_code;
1545 l_control_rec.get_freight_flag := 'N';
1546
1547 d_pos := 200;
1548 IF g_debug_stmt THEN
1549 PO_LOG.stmt(l_log_head, d_pos,'Call PRICE_REQUEST');
1550 END IF;
1551
1552 QP_PREQ_PUB.PRICE_REQUEST(
1553 p_control_rec => l_control_rec,
1554 x_return_status => x_return_status,
1555 x_return_status_Text => l_return_status_Text);
1556
1557 d_pos := 220;
1558 IF g_debug_stmt THEN
1559 PO_LOG.stmt(l_log_head, d_pos, 'x_return_status', x_return_status);
1560 PO_LOG.stmt(l_log_head, d_pos, 'l_return_status_text', l_return_status_text);
1561 END IF;
1562
1563 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1564 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
1565 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
1566 FND_MSG_PUB.Add;
1567 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1568 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1569 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
1570 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
1571 FND_MSG_PUB.Add;
1572 RAISE FND_API.G_EXC_ERROR;
1573 END IF;
1574
1575 /** No custom price hook for receiving.. need to
1576 incorporate it. whenever we are planning to use
1577 FSC for PO document
1578 PO_CUSTOM_PRICE_PUB.audit_qp_price_adjustment(
1579 p_api_version => 1.0
1580 , p_order_type => p_order_type
1581 , p_order_line_id => l_line_id
1582 , p_line_index => 1
1583 , x_return_status => l_return_status
1584 , x_msg_count => l_msg_count
1585 , x_msg_data => l_msg_data
1586 );
1587
1588 l_progress := '230';
1589 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1590 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status',l_return_status);
1591 PO_DEBUG.debug_unexp(l_log_head,l_progress,'audit_qp_price_adjustment errors out');
1592 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1593 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_msg_data);
1594 FND_MSG_PUB.Add;
1595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1596 END IF;
1597 */
1598
1599 d_pos := 240;
1600 IF g_debug_stmt THEN
1601 PO_LOG.stmt(l_log_head, d_pos,'Fetch QP pricing');
1602 PO_LOG.stmt(l_log_head, d_pos, 'p_header_rec.p_order_header_id', p_header_rec.p_order_header_id);
1606
1603 PO_LOG.stmt(l_log_head, d_pos, 'p_header_rec.shipment_header_id', p_header_rec.shipment_header_id);
1604 PO_LOG.stmt(l_log_head, d_pos, 'QP_PREQ_LINES_TMP_T', PO_LOG.c_all_rows);
1605 END IF;
1607 IF p_return_price_flag THEN
1608 --Access the QP views to retrieve the values for price
1609 d_pos := 250;
1610
1611 FOR j IN 1..i - 1 LOOP
1612 x_price_tbl.extend();
1613
1614 SELECT line_index,
1615 line_id,
1616 line_unit_price base_unit_price, -- base price
1617 order_uom_selling_price adjusted_price, -- adjusted_price
1618 pricing_status_code, --pricing status code
1619 pricing_status_text -- pricing status text
1620 INTO
1621 x_price_tbl(j).line_index,
1622 x_price_tbl(j).line_id,
1623 x_price_tbl(j).base_unit_price,
1624 x_price_tbl(j).adjusted_price,
1625 x_price_tbl(j).pricing_status_code,
1626 x_price_tbl(j).pricing_status_text
1627 FROM qp_preq_lines_tmp
1628 WHERE line_index = j;
1629
1630 d_pos := 260;
1631
1632 END LOOP;
1633 END IF;
1634
1635 IF p_return_freight_flag THEN
1636 d_pos := 270;
1637
1638 FOR j IN 1..i - 1 LOOP
1639 -- query to qp_ldets_v to retrieve the freight charge info.
1640 SELECT charge_type_code,
1641 order_qty_adj_amt freight_charge,
1642 pricing_status_code,
1643 pricing_status_text BULK COLLECT INTO l_freight_charge_rec_tbl
1644 FROM qp_ldets_v
1645 WHERE line_index = j
1646 AND list_line_type_code = 'FREIGHT_CHARGE'
1647 AND applied_flag = 'Y';
1648
1649 IF NOT p_return_price_flag THEN
1650 x_price_tbl.extend();
1651 END IF;
1652
1653 d_pos := 280;
1654
1655 x_price_tbl(j).line_index := l_line_index_tbl(j);
1656 x_price_tbl(j).base_unit_price := l_unit_price_tbl(j);
1657 x_price_tbl(j).freight_charge_rec_tbl := l_freight_charge_rec_tbl;
1658 x_price_tbl(j).line_id := l_line_id_tbl(j);
1659
1660 SELECT pricing_status_code, pricing_status_text INTO
1661 x_price_tbl(j).pricing_status_code,
1662 x_price_tbl(j).pricing_status_text
1663 FROM
1664 qp_preq_lines_tmp WHERE line_index = j;
1665 d_pos := 290;
1666 END LOOP;
1667 END IF;
1668
1669
1670 d_pos := 300;
1671 IF g_debug_stmt THEN
1672 PO_LOG.proc_end(l_log_head);
1673 PO_LOG.proc_end(l_log_head, 'x_return_status', x_return_status);
1674 FOR j IN 1..x_price_tbl.COUNT LOOP
1675 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').line_index', x_price_tbl(j).line_index);
1676 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').line_id', x_price_tbl(j).line_id);
1677 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').base_unit_price', x_price_tbl(j).base_unit_price);
1678 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').adjusted_price', x_price_tbl(j).adjusted_price);
1679 FOR k IN 1..x_price_tbl(j).freight_charge_rec_tbl.COUNT LOOP
1680 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').freight_charge_rec_tbl(' || k || ').charge_type_code'
1681 , x_price_tbl(j).freight_charge_rec_tbl(k).charge_type_code);
1682 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').freight_charge_rec_tbl(' || k || ').freight_charge'
1683 , x_price_tbl(j).freight_charge_rec_tbl(k).freight_charge);
1684 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').freight_charge_rec_tbl(' || k || ').pricing_status_code'
1685 , x_price_tbl(j).freight_charge_rec_tbl(k).pricing_status_code);
1686 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').freight_charge_rec_tbl(' || k || ').pricing_status_text'
1687 , x_price_tbl(j).freight_charge_rec_tbl(k).pricing_status_text);
1688 END LOOP;
1689 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').pricing_status_code', x_price_tbl(j).pricing_status_code);
1690 PO_LOG.proc_end(l_log_head, 'x_price_tbl(' || j || ').pricing_status_text', x_price_tbl(j).pricing_status_text);
1691 END LOOP;
1692 END IF;
1693 EXCEPTION
1694 WHEN FND_API.G_EXC_ERROR THEN
1695 --raised expected error: assume raiser already pushed onto the stack
1696 l_exception_msg := FND_MSG_PUB.get(
1697 p_msg_index => FND_MSG_PUB.G_LAST
1698 , p_encoded => 'F'
1699 );
1700 IF g_debug_unexp THEN
1701 PO_LOG.exc(l_log_head, d_pos, l_exception_msg);
1702 END IF;
1703 x_return_status := FND_API.g_ret_sts_error;
1704 -- Push the po_return_msg onto msg list and message stack
1705 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
1706 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
1707
1708 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1709 --raised unexpected error: assume raiser already pushed onto the stack
1710 l_exception_msg := FND_MSG_PUB.get(
1711 p_msg_index => FND_MSG_PUB.G_LAST
1712 , p_encoded => 'F'
1713 );
1714 IF g_debug_unexp THEN
1715 PO_LOG.exc(l_log_head, d_pos, l_exception_msg);
1716 END IF;
1717 x_return_status := FND_API.g_ret_sts_unexp_error;
1718 -- Push the po_return_msg onto msg list and message stack
1719 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
1720 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
1721
1722 WHEN OTHERS THEN
1723 IF g_debug_unexp THEN
1724 PO_LOG.exc(l_log_head, d_pos);
1725 END IF;
1726 --unexpected error from this procedure: get SQLERRM
1727 po_message_s.sql_error(g_pkg_name, l_api_name, d_pos, SQLCODE, SQLERRM);
1728 l_exception_msg := FND_MESSAGE.get;
1729 IF g_debug_unexp THEN
1730 PO_LOG.exc(l_log_head, d_pos, l_exception_msg);
1731 END IF;
1732 x_return_status := FND_API.g_ret_sts_unexp_error;
1733 -- Push the po_return_msg onto msg list and message stack
1734 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
1735 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
1736 END get_advanced_price;
1737 -- <FSC R12 END>
1738
1739 -- <Enhanced Pricing Start:>
1740 --------------------------------------------------------------------------------
1741 --Start of Comments
1742 --Name: call_pricing_manual_modifier
1743 --Pre-reqs:
1744 -- None.
1745 --Modifies:
1746 -- None.
1747 --Locks:
1748 -- None.
1749 --Function:
1750 -- This procedure calls Advanced prcing API to get the manual modifiers (adjustments)
1751 --Parameters:
1752 --IN:
1753 --p_org_id
1754 -- Org ID.
1755 --p_supplier_id
1756 -- Supplier ID.
1757 --p_supplier_site_id
1758 -- Supplier Site ID.
1759 --p_rate
1760 -- Conversion rate.
1761 --p_rate_type
1762 -- Conversion rate type.
1763 --p_currency_code
1764 -- Currency code.
1765 --p_creation_date
1766 -- Creation date.
1767 --p_order_type
1768 -- Order type: REQUISITION or PO.
1769 --p_ship_to_location_id
1770 -- Ship to Location ID.
1771 --p_ship_to_org_id
1772 -- Ship to Org ID.
1773 --p_order_id
1774 -- Order ID: REQUISITION Header ID or PO Header ID.
1775 --p_order_line_id
1776 -- Order Line ID: REQUISITION Line ID or PO Line ID.
1777 --p_item_revision
1778 -- Item Revision.
1779 --p_item_id
1780 -- Inventory Item ID.
1781 --p_category_id
1782 -- Category ID.
1783 --p_supplier_item_num
1784 -- Supplier Item Number
1785 --p_agreement_type
1786 -- The type of the source agreement. In 11.5.10, should only be CONTRACT.
1787 --p_agreement_id
1788 -- The header ID of the source agreement.
1789 --p_price_date
1790 -- Price date.
1791 --p_quantity
1792 -- Quantity.
1793 --p_uom
1794 -- Unit of Measure.
1795 --p_unit_price
1796 -- Unit Price.
1797 --OUT:
1798 --x_return_status
1799 -- FND_API.G_RET_STS_SUCCESS if API succeeds
1800 -- FND_API.G_RET_STS_ERROR if API fails
1801 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
1802 --Testing:
1803 --
1804 --End of Comments
1805 -------------------------------------------------------------------------------
1806 PROCEDURE call_pricing_manual_modifier
1807 ( p_org_id IN NUMBER
1808 , p_supplier_id IN NUMBER
1809 , p_supplier_site_id IN NUMBER
1810 , p_creation_date IN DATE
1811 , p_order_type IN VARCHAR2
1812 , p_ship_to_location_id IN NUMBER
1813 , p_ship_to_org_id IN NUMBER
1814 , p_order_header_id IN NUMBER
1815 , p_order_line_id IN NUMBER
1816 , p_item_revision IN VARCHAR2
1817 , p_item_id IN NUMBER
1818 , p_category_id IN NUMBER
1819 , p_supplier_item_num IN VARCHAR2
1820 , p_agreement_type IN VARCHAR2
1821 , p_agreement_id IN NUMBER
1822 , p_agreement_line_id IN NUMBER DEFAULT NULL --<R12 GBPA Adv Pricing>
1823 , p_rate IN NUMBER
1824 , p_rate_type IN VARCHAR2
1825 , p_currency_code IN VARCHAR2
1826 , p_need_by_date IN DATE
1827 , p_quantity IN NUMBER
1828 , p_uom IN VARCHAR2
1829 , p_unit_price IN NUMBER
1830 , x_return_status OUT NOCOPY VARCHAR2
1831 )
1832 IS
1833 l_api_name CONSTANT VARCHAR2(30) := 'CALL_PRICING_MANUAL_MODIFIER';
1834 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
1835 l_progress VARCHAR2(3) := '000';
1836 l_exception_msg FND_NEW_MESSAGES.message_text%TYPE;
1837 l_qp_license VARCHAR2(30) := NULL;
1838 l_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
1839
1840
1841 l_line_id NUMBER := nvl(p_order_line_id, 1);
1842 l_return_status_text VARCHAR2(2000);
1843 l_control_rec QP_PREQ_GRP.control_record_type;
1844 l_pass_line VARCHAR2(1);
1845
1846 l_line_index_tbl QP_PREQ_GRP.pls_integer_type;
1847 l_line_type_code_tbl QP_PREQ_GRP.varchar_type;
1848 l_pricing_effective_date_tbl QP_PREQ_GRP.date_type ;
1849 l_active_date_first_tbl QP_PREQ_GRP.date_type ;
1850 l_active_date_first_type_tbl QP_PREQ_GRP.varchar_type;
1851 l_active_date_second_tbl QP_PREQ_GRP.date_type ;
1852 l_active_date_second_type_tbl QP_PREQ_GRP.varchar_type ;
1853 l_line_unit_price_tbl QP_PREQ_GRP.number_type ;
1854 l_line_quantity_tbl QP_PREQ_GRP.number_type ;
1855 l_line_uom_code_tbl QP_PREQ_GRP.varchar_type;
1856 l_request_type_code_tbl QP_PREQ_GRP.varchar_type;
1860 l_currency_code_tbl QP_PREQ_GRP.varchar_type;
1857 l_priced_quantity_tbl QP_PREQ_GRP.number_type;
1858 l_uom_quantity_tbl QP_PREQ_GRP.number_type;
1859 l_priced_uom_code_tbl QP_PREQ_GRP.varchar_type;
1861 l_unit_price_tbl QP_PREQ_GRP.number_type;
1862 l_percent_price_tbl QP_PREQ_GRP.number_type;
1863 l_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
1864 l_upd_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
1865 l_processed_flag_tbl QP_PREQ_GRP.varchar_type;
1866 l_price_flag_tbl QP_PREQ_GRP.varchar_type;
1867 l_line_id_tbl QP_PREQ_GRP.number_type;
1868 l_processing_order_tbl QP_PREQ_GRP.pls_integer_type;
1869 l_rounding_factor_tbl QP_PREQ_GRP.pls_integer_type;
1870 l_rounding_flag_tbl QP_PREQ_GRP.flag_type;
1871 l_qualifiers_exist_flag_tbl QP_PREQ_GRP.varchar_type;
1872 l_pricing_attrs_exist_flag_tbl QP_PREQ_GRP.varchar_type;
1873 l_price_list_id_tbl QP_PREQ_GRP.number_type;
1874 l_pl_validated_flag_tbl QP_PREQ_GRP.varchar_type;
1875 l_price_request_code_tbl QP_PREQ_GRP.varchar_type;
1876 l_usage_pricing_type_tbl QP_PREQ_GRP.varchar_type;
1877 l_line_category_tbl QP_PREQ_GRP.varchar_type;
1878 l_pricing_status_code_tbl QP_PREQ_GRP.varchar_type;
1879 l_pricing_status_text_tbl QP_PREQ_GRP.varchar_type;
1880 l_list_price_overide_flag_tbl QP_PREQ_GRP.varchar_type;
1881
1882 l_price_status_code QP_PREQ_LINES_TMP.pricing_status_code%TYPE;
1883 l_price_status_text QP_PREQ_LINES_TMP.pricing_status_text%TYPE;
1884
1885 l_return_status VARCHAR2(1);
1886 l_msg_count NUMBER;
1887 l_msg_data VARCHAR2(2000);
1888
1889 BEGIN
1890
1891 --Initialize OUT parameters
1892 x_return_status := FND_API.G_RET_STS_SUCCESS;
1893
1894 IF g_debug_stmt THEN
1895 PO_DEBUG.debug_begin(l_log_head);
1896 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_org_id', p_org_id);
1897 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_supplier_id', p_supplier_id);
1898 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_supplier_site_id', p_supplier_site_id);
1899 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_creation_date', p_creation_date);
1900 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_order_type', p_order_type);
1901 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_ship_to_location_id', p_ship_to_location_id);
1902 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_ship_to_org_id', p_ship_to_org_id);
1903 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_order_header_id', p_order_header_id);
1904 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_order_line_id', p_order_line_id);
1905 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_item_revision', p_item_revision);
1906 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_item_id', p_item_id);
1907 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_category_id', p_category_id);
1908 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_supplier_item_num', p_supplier_item_num);
1909 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_agreement_type', p_agreement_type);
1910 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_agreement_id', p_agreement_id);
1911 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_agreement_line_id', p_agreement_line_id);
1912 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_rate', p_rate);
1913 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_rate_type', p_rate_type);
1914 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_currency_code', p_currency_code);
1915 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_need_by_date', p_need_by_date);
1916 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_quantity', p_quantity);
1917 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_uom', p_uom);
1918 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_unit_price', p_unit_price);
1919 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Check Advanced Pricing License');
1920 END IF;
1921
1922 FND_PROFILE.get('QP_LICENSED_FOR_PRODUCT', l_qp_license);
1923 l_progress := '020';
1924 IF g_debug_stmt THEN
1925 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_qp_license', l_qp_license);
1926 END IF;
1927
1928 IF (l_qp_license IS NULL OR l_qp_license <> 'PO') THEN
1929 RETURN;
1930 END IF;
1931
1932 l_progress := '040';
1933 IF g_debug_stmt THEN
1934 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Set Price Request ID');
1935 END IF;
1936
1937 QP_PRICE_REQUEST_CONTEXT.set_request_id;
1938
1939 l_progress := '060';
1940 IF g_debug_stmt THEN
1941 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Populate Global Header Structure');
1942 END IF;
1943
1944 populate_header_record(
1945 p_org_id => p_org_id,
1946 p_order_header_id => p_order_header_id,
1947 p_supplier_id => p_supplier_id,
1948 p_supplier_site_id => p_supplier_site_id,
1949 p_creation_date => p_creation_date,
1950 p_order_type => p_order_type,
1951 p_ship_to_location_id => p_ship_to_location_id,
1952 p_ship_to_org_id => p_ship_to_org_id);
1953
1954 l_progress := '080';
1955 IF g_debug_stmt THEN
1956 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Populate Global Line Structure');
1957 END IF;
1958
1959 populate_line_record(
1960 p_order_line_id => p_order_line_id,
1961 p_order_type => p_order_type, --Enhanced Pricing
1962 p_item_revision => p_item_revision,
1963 p_item_id => p_item_id,
1964 p_category_id => p_category_id,
1965 p_supplier_item_num => p_supplier_item_num,
1966 p_agreement_type => p_agreement_type,
1970 p_supplier_site_id => p_supplier_site_id,
1967 p_agreement_id => p_agreement_id,
1968 p_agreement_line_id => p_agreement_line_id, --<R12 GBPA Adv Pricing>
1969 p_supplier_id => p_supplier_id,
1971 p_ship_to_location_id => p_ship_to_location_id,
1972 p_ship_to_org_id => p_ship_to_org_id,
1973 p_rate => p_rate,
1974 p_rate_type => p_rate_type,
1975 p_currency_code => p_currency_code,
1976 p_need_by_date => p_need_by_date);
1977
1978
1979 l_progress := '090';
1980 IF g_debug_stmt THEN
1981 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Set OE Debug');
1982 OE_DEBUG_PUB.SetDebugLevel(10);
1983 PO_DEBUG.debug_stmt(l_log_head, l_progress, 'Debug File Location:'||
1984 OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
1985 OE_DEBUG_PUB.Initialize;
1986 OE_DEBUG_PUB.Debug_On;
1987 END IF;
1988
1989 l_progress := '100';
1990 IF g_debug_stmt THEN
1991 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Build Attributes Mapping Contexts');
1992 END IF;
1993
1994 QP_Attr_Mapping_PUB.Build_Contexts(
1995 p_request_type_code => 'PO',
1996 p_line_index => 1,
1997 p_pricing_type_code => 'L',
1998 p_check_line_flag => 'N',
1999 p_pricing_event => 'PO_BATCH',
2000 x_pass_line => l_pass_line);
2001
2002 l_progress := '110';
2003 IF g_debug_stmt THEN
2004 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Get UOM Code');
2005 END IF;
2006
2007 BEGIN
2008 -- Make sure we pass uom_code instead of unit_of_measure.
2009 SELECT mum.uom_code
2010 INTO l_uom_code
2011 FROM mtl_units_of_measure mum
2012 WHERE mum.unit_of_measure = p_uom;
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 l_uom_code := p_uom;
2016 END;
2017
2018 l_progress := '120';
2019 IF g_debug_stmt THEN
2020 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_uom_code', l_uom_code);
2021 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Directly Insert into Temp table');
2022 END IF;
2023
2024 l_request_type_code_tbl(1) := 'PO';
2025 l_line_id_tbl(1) := l_line_id; -- order line id
2026 l_line_index_tbl(1) := 1; -- Request Line Index
2027 l_line_type_code_tbl(1) := 'LINE'; -- LINE or ORDER(Summary Line)
2028 l_pricing_effective_date_tbl(1) := p_need_by_date;-- Pricing as of effective date
2029 l_active_date_first_tbl(1) := NULL; -- Can be Ordered Date or Ship Date
2030 l_active_date_second_tbl(1) := NULL; -- Can be Ordered Date or Ship Date
2031 l_active_date_first_type_tbl(1) := NULL; -- ORD/SHIP
2032 l_active_date_second_type_tbl(1) := NULL; -- ORD/SHIP
2033 l_line_unit_price_tbl(1) := p_unit_price;-- Unit Price
2034
2035 -- should pass 1 instead of NULL
2036 l_line_quantity_tbl(1) := NVL(p_quantity, 1);-- Ordered Quantity
2037 -- don't pass 0, pass 1 instead
2038 IF (l_line_quantity_tbl(1) = 0) THEN
2039 l_line_quantity_tbl(1) := 1;
2040 END IF;
2041
2042 l_line_uom_code_tbl(1) := l_uom_code; -- Ordered UOM Code
2043 l_currency_code_tbl(1) := p_currency_code;-- Currency Code
2044 l_price_flag_tbl(1) := 'Y'; -- Price Flag can have 'Y', 'N'(No pricing), 'P'(Phase)
2045 l_usage_pricing_type_tbl(1) := QP_PREQ_GRP.g_regular_usage_type;
2046
2047 -- should pass 1 instead of NULL
2048 l_priced_quantity_tbl(1) := NVL(p_quantity, 1);
2049 -- don't pass 0, pass 1 instead
2050 IF (l_priced_quantity_tbl(1) = 0) THEN
2051 l_priced_quantity_tbl(1) := 1;
2052 END IF;
2053
2057 l_uom_quantity_tbl(1) := NULL;
2054 l_priced_uom_code_tbl(1) := l_uom_code;
2055 l_unit_price_tbl(1) := p_unit_price;
2056 l_percent_price_tbl(1) := NULL;
2058 l_adjusted_unit_price_tbl(1) := NULL;
2059 l_upd_adjusted_unit_price_tbl(1) := NULL;
2060 l_processed_flag_tbl(1) := NULL;
2061 l_processing_order_tbl(1) := NULL;
2062 l_pricing_status_code_tbl(1) := QP_PREQ_GRP.g_status_unchanged;
2063 l_pricing_status_text_tbl(1) := NULL;
2064 l_rounding_flag_tbl(1) := NULL;
2065 l_rounding_factor_tbl(1) := NULL;
2066 l_qualifiers_exist_flag_tbl(1) := 'N';
2067 l_pricing_attrs_exist_flag_tbl(1) := 'N';
2068 l_price_list_id_tbl(1) := - 9999;
2069 l_pl_validated_flag_tbl(1) := 'N';
2070 l_price_request_code_tbl(1) := NULL;
2071 l_line_category_tbl(1) := NULL;
2072 l_list_price_overide_flag_tbl(1) := 'O'; -- Override price
2073
2074 l_progress := '140';
2075 IF g_debug_stmt THEN
2076 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Call INSERT_LINES2');
2077 END IF;
2078
2079 QP_PREQ_GRP.INSERT_LINES2
2080 (p_line_index => l_line_index_tbl,
2081 p_line_type_code => l_line_type_code_tbl,
2082 p_pricing_effective_date => l_pricing_effective_date_tbl,
2083 p_active_date_first => l_active_date_first_tbl,
2084 p_active_date_first_type => l_active_date_first_type_tbl,
2085 p_active_date_second => l_active_date_second_tbl,
2086 p_active_date_second_type => l_active_date_second_type_tbl,
2087 p_line_quantity => l_line_quantity_tbl,
2088 p_line_uom_code => l_line_uom_code_tbl,
2089 p_request_type_code => l_request_type_code_tbl,
2090 p_priced_quantity => l_priced_quantity_tbl,
2091 p_priced_uom_code => l_priced_uom_code_tbl,
2092 p_currency_code => l_currency_code_tbl,
2093 p_unit_price => l_unit_price_tbl,
2094 p_percent_price => l_percent_price_tbl,
2095 p_uom_quantity => l_uom_quantity_tbl,
2096 p_adjusted_unit_price => l_adjusted_unit_price_tbl,
2097 p_upd_adjusted_unit_price => l_upd_adjusted_unit_price_tbl,
2098 p_processed_flag => l_processed_flag_tbl,
2099 p_price_flag => l_price_flag_tbl,
2100 p_line_id => l_line_id_tbl,
2101 p_processing_order => l_processing_order_tbl,
2102 p_pricing_status_code => l_pricing_status_code_tbl,
2103 p_pricing_status_text => l_pricing_status_text_tbl,
2104 p_rounding_flag => l_rounding_flag_tbl,
2105 p_rounding_factor => l_rounding_factor_tbl,
2106 p_qualifiers_exist_flag => l_qualifiers_exist_flag_tbl,
2107 p_pricing_attrs_exist_flag => l_pricing_attrs_exist_flag_tbl,
2108 p_price_list_id => l_price_list_id_tbl,
2109 p_validated_flag => l_pl_validated_flag_tbl,
2110 p_price_request_code => l_price_request_code_tbl,
2111 p_usage_pricing_type => l_usage_pricing_type_tbl,
2112 p_line_category => l_line_category_tbl,
2113 p_line_unit_price => l_line_unit_price_tbl,
2114 p_list_price_override_flag => l_list_price_overide_flag_tbl,
2115 x_status_code => x_return_status,
2116 x_status_text => l_return_status_text);
2117
2118 l_progress := '160';
2119 IF g_debug_stmt THEN
2120 PO_DEBUG.debug_stmt(l_log_head, l_progress,'After Calling INSERT_LINES2');
2121 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_return_status', x_return_status);
2122 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_return_status_text', l_return_status_text);
2123 END IF;
2124
2125 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2126 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
2127 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
2128 FND_MSG_PUB.Add;
2129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2130 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2131 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
2132 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
2133 FND_MSG_PUB.Add;
2134 RAISE FND_API.G_EXC_ERROR;
2135 END IF;
2136
2137 -- Don't call QP_PREQ_GRP.INSERT_LINE_ATTRS2 since PO has no
2138 -- ASK_FOR attributes
2139
2140 l_progress := '180';
2141 IF g_debug_stmt THEN
2142 PO_DEBUG.debug_stmt(l_log_head, l_progress,
2143 'Populate Control Record for Pricing Request Call to fetch the manual modifiers');
2144 END IF;
2145
2146 l_control_rec.manual_adjustments_call_flag := qp_preq_grp.G_YES;
2147 l_control_rec.calculate_flag := 'Y';
2148 l_control_rec.simulation_flag := 'N';
2149 l_control_rec.pricing_event := 'PO_BATCH';
2150 l_control_rec.temp_table_insert_flag := 'N';
2151 l_control_rec.check_cust_view_flag := 'N';
2152 l_control_rec.request_type_code := 'PO';
2153 --now pricing take care of all the roundings.
2154 l_control_rec.rounding_flag := 'Q';
2155 --For multi_currency price list
2156 l_control_rec.use_multi_currency := 'Y';
2157 l_control_rec.user_conversion_rate := PO_ADVANCED_PRICE_PVT.g_line.rate;
2158 l_control_rec.user_conversion_type := PO_ADVANCED_PRICE_PVT.g_line.rate_type;
2159 l_control_rec.function_currency := PO_ADVANCED_PRICE_PVT.g_line.currency_code;
2160 l_control_rec.get_freight_flag := 'N';
2161
2162 l_progress := '200';
2163 IF g_debug_stmt THEN
2164 PO_DEBUG.debug_stmt(l_log_head, l_progress,'Call PRICE_REQUEST');
2165 END IF;
2166
2167 QP_PREQ_PUB.PRICE_REQUEST(
2168 p_control_rec => l_control_rec,
2169 x_return_status => x_return_status,
2170 x_return_status_Text => l_return_status_Text);
2171
2172 l_progress := '220';
2173 IF g_debug_stmt THEN
2174 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_return_status', x_return_status);
2175 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_return_status_text', l_return_status_text);
2176 END IF;
2177
2178 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2179 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
2180 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
2181 FND_MSG_PUB.Add;
2182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2183 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2184 FND_MESSAGE.SET_NAME('PO', 'PO_QP_PRICE_API_ERROR');
2185 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_return_status_text);
2186 FND_MSG_PUB.Add;
2187 RAISE FND_API.G_EXC_ERROR;
2188 END IF;
2189
2190 l_progress := '240';
2191 IF g_debug_stmt THEN
2192 PO_DEBUG.debug_stmt(l_log_head, l_progress,'The QP call is made successfully. The Manual modifiers can be fetched from QP temp tables');
2193 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_line_id', l_line_id);
2194 PO_DEBUG.debug_table(l_log_head, l_progress, 'QP_PREQ_LINES_TMP_T', PO_DEBUG.g_all_rows, NULL, 'QP');
2195 END IF;
2196
2197 l_progress := '300';
2198 IF g_debug_stmt THEN
2199 PO_DEBUG.debug_end(l_log_head);
2200 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_return_status', x_return_status);
2201 END IF;
2202
2203 EXCEPTION
2204 WHEN FND_API.G_EXC_ERROR THEN
2205 --raised expected error: assume raiser already pushed onto the stack
2206 l_exception_msg := FND_MSG_PUB.get(
2207 p_msg_index => FND_MSG_PUB.G_LAST
2208 , p_encoded => 'F'
2209 );
2210 IF g_debug_unexp THEN
2211 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_exception_msg',
2212 l_exception_msg);
2213 END IF;
2214 x_return_status := FND_API.g_ret_sts_error;
2215 -- Push the po_return_msg onto msg list and message stack
2216 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
2217 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
2218
2219 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2220 --raised unexpected error: assume raiser already pushed onto the stack
2221 l_exception_msg := FND_MSG_PUB.get(
2222 p_msg_index => FND_MSG_PUB.G_LAST
2223 , p_encoded => 'F'
2224 );
2225 IF g_debug_unexp THEN
2226 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_exception_msg',
2227 l_exception_msg);
2228 END IF;
2229 x_return_status := FND_API.g_ret_sts_unexp_error;
2230 -- Push the po_return_msg onto msg list and message stack
2231 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
2232 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
2233
2234 WHEN OTHERS THEN
2235 IF g_debug_unexp THEN
2236 PO_DEBUG.debug_exc(l_log_head, l_progress);
2237 END IF;
2238 --unexpected error from this procedure: get SQLERRM
2239 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
2240 l_exception_msg := FND_MESSAGE.get;
2241 IF g_debug_unexp THEN
2242 PO_DEBUG.debug_var(l_log_head, l_progress, 'l_exception_msg',
2243 l_exception_msg);
2244 END IF;
2245 x_return_status := FND_API.g_ret_sts_unexp_error;
2246 -- Push the po_return_msg onto msg list and message stack
2247 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
2248 FND_MESSAGE.SET_TOKEN('ERROR_TEXT', l_exception_msg);
2249 END call_pricing_manual_modifier;
2250 -- <Enhanced Pricing End>
2251
2252 END PO_ADVANCED_PRICE_PVT;