[Home] [Help]
PACKAGE BODY: APPS.PO_ADVANCED_PRICE_PVT
Source
1 PACKAGE BODY PO_ADVANCED_PRICE_PVT AS
2 /* $Header: POXQPRVB.pls 120.8 2006/09/25 19:28:21 pxiao noship $ */
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_item_revision IN VARCHAR2 -- Bug 3330884
48 , p_item_id IN NUMBER
49 , p_category_id IN NUMBER
50 , p_supplier_item_num IN VARCHAR2
51 , p_agreement_type IN VARCHAR2
52 , p_agreement_id IN NUMBER
53 , p_agreement_line_id IN NUMBER DEFAULT NULL --<R12 GBPA Adv Pricing>
54 , p_supplier_id IN NUMBER
55 , p_supplier_site_id IN NUMBER
56 , p_ship_to_location_id IN NUMBER
57 , p_ship_to_org_id IN NUMBER
58 , p_rate IN NUMBER
59 , p_rate_type IN VARCHAR2
60 , p_currency_code IN VARCHAR2
61 , p_need_by_date IN DATE
62 -- <FSC R12 START>
63 -- New Attributes for R12: Receving FSC support
64 , p_shipment_line_id IN NUMBER DEFAULT NULL
65 , p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL
66 , p_to_organization_id IN NUMBER DEFAULT NULL
67 , p_unit_of_measure IN VARCHAR2 DEFAULT NULL
68 , p_source_document_code IN VARCHAR2 DEFAULT NULL
69 , p_unit_price IN NUMBER DEFAULT NULL -- will not be mapped to any QP attribute
70 , p_quantity IN NUMBER DEFAULT NULL -- will not be mapped to any QP attribute
71 -- <FSC R12 END>
72 );
73 --------------------------------------------------------------------------------
74 -- Procedure definitions
75 --------------------------------------------------------------------------------
76
77 --------------------------------------------------------------------------------
78 --Start of Comments
79 --Name: populate_header_record
80 --Pre-reqs:
81 -- None.
82 --Modifies:
83 -- None.
84 --Locks:
85 -- None.
86 --Function:
87 -- This procedure populates global variable G_HDR.
88 --Parameters:
89 --IN:
90 --p_org_id
91 -- Org ID.
92 --p_order_id
93 -- Order ID: REQUISITION Header ID or PO Header ID.
94 --p_supplier_id
95 -- Supplier ID.
96 --p_supplier_site_id
97 -- Supplier Site ID.
98 --p_creation_date
99 -- Creation date.
100 --p_order_type
101 -- Order type: REQUISITION or PO.
102 --p_ship_to_location_id
103 -- Ship to Location ID.
104 --p_ship_to_org_id
105 -- Ship to Org ID.
106 --p_shipment_header_id
107 -- shipment header id
108 --p_hazard_class
109 -- hazard class
110 --p_hazard_code
111 -- hazard code
112 --p_shipped_date
113 -- shipped date for goods
114 --p_shipment_num
115 -- shipment number
116 --p_carrier_method
117 -- carrier method
118 --p_packaging_code
119 -- packaging code
120 --p_freight_carrier_code
121 -- greight carrier code
122 --p_freight_terms
123 -- freight terms
124 --p_currency_code
125 -- currency code
126 --p_rate
127 -- currency conversion rate
128 --p_rate_type
129 -- rate type
130 --p_expected_receipt_date
131 -- expected receipt date
132 --Testing:
133 --
134 --End of Comments
135 -------------------------------------------------------------------------------
136 PROCEDURE populate_header_record
137 ( p_org_id IN NUMBER
138 , p_order_header_id IN NUMBER
139 , p_supplier_id IN NUMBER
140 , p_supplier_site_id IN NUMBER
141 , p_creation_date IN DATE
142 , p_order_type IN VARCHAR2
143 , p_ship_to_location_id IN NUMBER
144 , p_ship_to_org_id IN NUMBER
145 -- <FSC R12 START>
146 -- New Attributes for R12: Receving FSC support
147 , p_shipment_header_id IN NUMBER DEFAULT NULL
148 , p_hazard_class IN VARCHAR2 DEFAULT NULL
149 , p_hazard_code IN VARCHAR2 DEFAULT NULL
150 , p_shipped_date IN DATE DEFAULT NULL
151 , p_shipment_num IN VARCHAR2 DEFAULT NULL
152 , p_carrier_method IN VARCHAR2 DEFAULT NULL
153 , p_packaging_code IN VARCHAR2 DEFAULT NULL
154 , p_freight_carrier_code IN VARCHAR2 DEFAULT NULL
155 , p_freight_terms IN VARCHAR2 DEFAULT NULL
156 , p_currency_code IN VARCHAR2 DEFAULT NULL
157 , p_rate IN NUMBER DEFAULT NULL
158 , p_rate_type IN VARCHAR2 DEFAULT NULL
159 , p_source_org_id IN NUMBER DEFAULT NULL
160 , p_expected_receipt_date IN DATE DEFAULT NULL
161 -- <FSC R12 END>
162 )
163 IS
164 BEGIN
165 g_hdr.org_id := p_org_id;
166 g_hdr.p_order_header_id := p_order_header_id;
167 g_hdr.supplier_id := p_supplier_id;
168 g_hdr.supplier_site_id := p_supplier_site_id;
169 g_hdr.creation_date := p_creation_date;
170 g_hdr.order_type := p_order_type;
171 g_hdr.ship_to_location_id := p_ship_to_location_id;
172 g_hdr.ship_to_org_id := p_ship_to_org_id;
173 -- <FSC R12 START>
174 g_hdr.shipment_header_id := p_shipment_header_id;
175 g_hdr.hazard_class := p_hazard_class;
176 g_hdr.hazard_code := p_hazard_code;
177 g_hdr.shipped_date := p_shipped_date;
178 g_hdr.shipment_num := p_shipment_num;
179 g_hdr.carrier_method := p_carrier_method;
180 g_hdr.packaging_code := p_packaging_code;
181 g_hdr.freight_carrier_code := p_freight_carrier_code;
182 g_hdr.freight_terms := p_freight_terms;
183 g_hdr.currency_code := p_currency_code;
184 g_hdr.rate := p_rate;
185 g_hdr.rate_type := p_rate_type;
186 g_hdr.source_org_id := p_source_org_id;
187 g_hdr.expected_receipt_date := p_expected_receipt_date;
188 -- <FSC R12 END>
189 END populate_header_record;
190
191 --------------------------------------------------------------------------------
192 --Start of Comments
193 --Name: populate_line_record
194 --Pre-reqs:
195 -- None.
196 --Modifies:
197 -- None.
198 --Locks:
199 -- None.
200 --Function:
201 -- This procedure populates global variable G_LINE.
202 --Parameters:
203 --IN:
204 --p_order_line_id
205 -- Order Line ID: REQUISITION Line ID or PO Line ID.
206 --p_item_revision
207 -- Item Revision.
208 --p_item_id
209 -- Inventory Item ID.
210 --p_category_id
211 -- Category ID.
212 --p_agreement_type
213 -- The type of the source agreement. In 11.5.10, should only be CONTRACT.
214 --p_agreement_id
215 -- The header ID of the source agreement.
216 --p_supplier_id
217 -- Supplier ID.
218 --p_supplier_site_id
219 -- Supplier Site ID.
220 --p_ship_to_location_id
221 -- Ship to Location ID.
222 --p_ship_to_org_id
223 -- Ship to Org ID.
224 --p_rate
225 -- Conversion rate.
226 --p_rate_type
227 -- Conversion rate type.
228 --p_currency_code
229 -- Currency code.
230 --p_need_by_date
231 -- Need by date.
232 --p_shipment_line_id
233 -- Shipment line id
234 --p_primary_unit_of_measure
235 -- primary unit of measure
236 --p_to_organization_id
237 -- destination org id
238 --p_unit_of_measure
239 -- unit of measure
240 --p_source_document_code
241 -- source doc code
242 --p_unit_price
243 -- unit price
244 --p_quantity
245 -- quantity
246 --Testing:
247 --
248 --End of Comments
249 -------------------------------------------------------------------------------
250 PROCEDURE populate_line_record
251 ( p_order_line_id IN NUMBER
252 , p_item_revision IN VARCHAR2 -- Bug 3330884
253 , p_item_id IN NUMBER
254 , p_category_id IN NUMBER
255 , p_supplier_item_num IN VARCHAR2
256 , p_agreement_type IN VARCHAR2
257 , p_agreement_id IN NUMBER
258 , p_agreement_line_id IN NUMBER DEFAULT NULL --<R12 GBPA Adv Pricing>
259 , p_supplier_id IN NUMBER
260 , p_supplier_site_id IN NUMBER
261 , p_ship_to_location_id IN NUMBER
262 , p_ship_to_org_id IN NUMBER
263 , p_rate IN NUMBER
264 , p_rate_type IN VARCHAR2
265 , p_currency_code IN VARCHAR2
266 , p_need_by_date IN DATE
267 --<FSC Start R12>
268 , p_shipment_line_id IN NUMBER DEFAULT NULL
269 , p_primary_unit_of_measure IN VARCHAR2 DEFAULT NULL
270 , p_to_organization_id IN NUMBER DEFAULT NULL
271 , p_unit_of_measure IN VARCHAR2 DEFAULT NULL
272 , p_source_document_code IN VARCHAR2 DEFAULT NULL
273 , p_unit_price IN NUMBER DEFAULT NULL -- will not be mapped to any QP attribute
274 , p_quantity IN NUMBER DEFAULT NULL -- will not be mapped to any QP attribute
275 --<FSC End R12>
276 )
277 IS
278 BEGIN
279 g_line.order_line_id := p_order_line_id;
280 g_line.item_revision := p_item_revision;
281 g_line.item_id := p_item_id;
282 g_line.category_id := p_category_id;
283 g_line.supplier_item_num := p_supplier_item_num;
284 g_line.agreement_type := p_agreement_type;
285 g_line.agreement_id := p_agreement_id;
286 g_line.agreement_line_id := p_agreement_line_id; --<R12 GBPA Adv Pricing>
287 g_line.supplier_id := p_supplier_id;
288 g_line.supplier_site_id := p_supplier_site_id;
289 g_line.ship_to_location_id := p_ship_to_location_id;
290 g_line.ship_to_org_id := p_ship_to_org_id;
291 g_line.rate := p_rate;
292 g_line.rate_type := p_rate_type;
293 g_line.currency_code := p_currency_code;
294 g_line.need_by_date := p_need_by_date;
295 -- <FSC R12 START>
296 g_line.shipment_line_id := p_shipment_line_id;
297 g_line.primary_unit_of_measure:= p_primary_unit_of_measure;
298 g_line.to_organization_id := p_to_organization_id;
299 g_line.unit_of_measure := p_unit_of_measure;
300 g_line.source_document_code := p_source_document_code;
301 g_line.unit_price := p_unit_price;
302 g_line.quantity := p_quantity;
303 -- <FSC R12 END>
304
305 END populate_line_record;
306
307 --------------------------------------------------------------------------------
308 --Start of Comments
309 --Name: get_advanced_price
310 --Pre-reqs:
311 -- None.
312 --Modifies:
313 -- None.
314 --Locks:
315 -- None.
316 --Function:
317 -- This procedure calls Advanced prcing API to get list price and adjustment.
318 --Parameters:
319 --IN:
320 --p_org_id
321 -- Org ID.
322 --p_supplier_id
323 -- Supplier ID.
324 --p_supplier_site_id
325 -- Supplier Site ID.
326 --p_rate
327 -- Conversion rate.
328 --p_rate_type
329 -- Conversion rate type.
330 --p_currency_code
331 -- Currency code.
332 --p_creation_date
333 -- Creation date.
334 --p_order_type
335 -- Order type: REQUISITION or PO.
336 --p_ship_to_location_id
337 -- Ship to Location ID.
338 --p_ship_to_org_id
339 -- Ship to Org ID.
340 --p_order_id
341 -- Order ID: REQUISITION Header ID or PO Header ID.
342 --p_order_line_id
343 -- Order Line ID: REQUISITION Line ID or PO Line ID.
344 --p_item_revision
345 -- Item Revision.
346 --p_item_id
347 -- Inventory Item ID.
348 --p_category_id
349 -- Category ID.
350 --p_supplier_item_num
351 -- Supplier Item Number
352 --p_agreement_type
353 -- The type of the source agreement. In 11.5.10, should only be CONTRACT.
354 --p_agreement_id
355 -- The header ID of the source agreement.
356 --p_price_date
357 -- Price date.
358 --p_quantity
359 -- Quantity.
360 --p_uom
361 -- Unit of Measure.
362 --p_unit_price
363 -- Unit Price.
364 --OUT:
365 --x_base_unit_price
366 -- Base Unit Price.
367 --x_unit_price
368 -- Adjusted Unit Price.
369 --x_return_status
370 -- FND_API.G_RET_STS_SUCCESS if API succeeds
371 -- FND_API.G_RET_STS_ERROR if API fails
372 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
373 --Testing:
374 --
375 --End of Comments
376 -------------------------------------------------------------------------------
377 PROCEDURE get_advanced_price
378 ( p_org_id IN NUMBER
379 , p_supplier_id IN NUMBER
380 , p_supplier_site_id IN NUMBER
381 , p_creation_date IN DATE
382 , p_order_type IN VARCHAR2
383 , p_ship_to_location_id IN NUMBER
384 , p_ship_to_org_id IN NUMBER
385 , p_order_header_id IN NUMBER
386 , p_order_line_id IN NUMBER
387 , p_item_revision IN VARCHAR2 -- Bug 3330884
388 , p_item_id IN NUMBER
389 , p_category_id IN NUMBER
390 , p_supplier_item_num IN VARCHAR2
391 , p_agreement_type IN VARCHAR2
392 , p_agreement_id IN NUMBER
393 , p_agreement_line_id IN NUMBER DEFAULT NULL --<R12 GBPA Adv Pricing>
394 , p_rate IN NUMBER
395 , p_rate_type IN VARCHAR2
396 , p_currency_code IN VARCHAR2
397 , p_need_by_date IN DATE
398 , p_quantity IN NUMBER
399 , p_uom IN VARCHAR2
400 , p_unit_price IN NUMBER
401 , x_base_unit_price OUT NOCOPY NUMBER
402 , x_unit_price OUT NOCOPY NUMBER
403 , x_return_status OUT NOCOPY VARCHAR2
404 )
405 IS
406 l_api_name CONSTANT varchar2(30) := 'GET_ADVANCED_PRICE';
407 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
408 l_progress VARCHAR2(3) := '000';
409 l_exception_msg FND_NEW_MESSAGES.message_text%TYPE;
410 l_qp_license VARCHAR2(30) := NULL;
411 l_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
412
413 l_line_id NUMBER := nvl(p_order_line_id, 1);
414 l_return_status_text VARCHAR2(2000);
415 l_control_rec QP_PREQ_GRP.control_record_type;
416 l_pass_line VARCHAR2(1);
417
418 l_line_index_tbl QP_PREQ_GRP.pls_integer_type;
419 l_line_type_code_tbl QP_PREQ_GRP.varchar_type;
420 l_pricinl_effective_date_tbl QP_PREQ_GRP.date_type ;
421 l_active_date_first_tbl QP_PREQ_GRP.date_type ;
422 l_active_date_first_type_tbl QP_PREQ_GRP.varchar_type;
423 l_active_date_second_tbl QP_PREQ_GRP.date_type ;
424 l_active_date_second_type_tbl QP_PREQ_GRP.varchar_type ;
425 l_line_unit_price_tbl QP_PREQ_GRP.number_type ;
426 l_line_quantity_tbl QP_PREQ_GRP.number_type ;
427 l_line_uom_code_tbl QP_PREQ_GRP.varchar_type;
428 l_request_type_code_tbl QP_PREQ_GRP.varchar_type;
429 l_priced_quantity_tbl QP_PREQ_GRP.number_type;
430 l_uom_quantity_tbl QP_PREQ_GRP.number_type;
431 l_priced_uom_code_tbl QP_PREQ_GRP.varchar_type;
432 l_currency_code_tbl QP_PREQ_GRP.varchar_type;
433 l_unit_price_tbl QP_PREQ_GRP.number_type;
434 l_percent_price_tbl QP_PREQ_GRP.number_type;
435 l_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
436 l_upd_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
437 l_processed_flag_tbl QP_PREQ_GRP.varchar_type;
438 l_price_flag_tbl QP_PREQ_GRP.varchar_type;
439 l_line_id_tbl QP_PREQ_GRP.number_type;
440 l_processing_order_tbl QP_PREQ_GRP.pls_integer_type;
441 l_rounding_factor_tbl QP_PREQ_GRP.pls_integer_type;
442 l_rounding_flag_tbl QP_PREQ_GRP.flag_type;
443 l_qualifiers_exist_flag_tbl QP_PREQ_GRP.varchar_type;
444 l_pricing_attrs_exist_flag_tbl QP_PREQ_GRP.varchar_type;
445 l_price_list_id_tbl QP_PREQ_GRP.number_type;
446 l_pl_validated_flag_tbl QP_PREQ_GRP.varchar_type;
447 l_price_request_code_tbl QP_PREQ_GRP.varchar_type;
448 l_usage_pricing_type_tbl QP_PREQ_GRP.varchar_type;
449 l_line_category_tbl QP_PREQ_GRP.varchar_type;
450 l_pricing_status_code_tbl QP_PREQ_GRP.varchar_type;
451 l_pricing_status_text_tbl QP_PREQ_GRP.varchar_type;
452 l_list_price_overide_flag_tbl QP_PREQ_GRP.varchar_type;
453
454 l_price_status_code QP_PREQ_LINES_TMP.pricing_status_code%TYPE;
455 l_price_status_text QP_PREQ_LINES_TMP.pricing_status_text%TYPE;
456
457 -- <Bug 3794940 START>
458 l_return_status VARCHAR2(1);
459 l_msg_count NUMBER;
460 l_msg_data VARCHAR2(2000);
461 -- <Bug 3794940 END>
462
463 BEGIN
464
465 -- Initialize OUT parameters
466 x_return_status := FND_API.G_RET_STS_SUCCESS;
467 x_base_unit_price := p_unit_price;
468 x_unit_price := p_unit_price;
469
470 IF g_debug_stmt THEN
471 PO_DEBUG.debug_begin(l_log_head);
472 PO_DEBUG.debug_var(l_log_head,l_progress,'p_org_id',p_org_id);
473 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_id',p_supplier_id);
474 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_site_id',p_supplier_site_id);
475 PO_DEBUG.debug_var(l_log_head,l_progress,'p_creation_date',p_creation_date);
476 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_type',p_order_type);
477 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ship_to_location_id',p_ship_to_location_id);
478 PO_DEBUG.debug_var(l_log_head,l_progress,'p_ship_to_org_id',p_ship_to_org_id);
479 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_header_id',p_order_header_id);
480 PO_DEBUG.debug_var(l_log_head,l_progress,'p_order_line_id',p_order_line_id);
481 PO_DEBUG.debug_var(l_log_head,l_progress,'p_item_revision',p_item_revision);
482 PO_DEBUG.debug_var(l_log_head,l_progress,'p_item_id',p_item_id);
483 PO_DEBUG.debug_var(l_log_head,l_progress,'p_category_id',p_category_id);
484 PO_DEBUG.debug_var(l_log_head,l_progress,'p_supplier_item_num',p_supplier_item_num);
485 PO_DEBUG.debug_var(l_log_head,l_progress,'p_agreement_type',p_agreement_type);
486 PO_DEBUG.debug_var(l_log_head,l_progress,'p_agreement_id',p_agreement_id);
487 PO_DEBUG.debug_var(l_log_head,l_progress,'p_agreement_line_id',p_agreement_line_id);
488 PO_DEBUG.debug_var(l_log_head,l_progress,'p_rate',p_rate);
489 PO_DEBUG.debug_var(l_log_head,l_progress,'p_rate_type',p_rate_type);
490 PO_DEBUG.debug_var(l_log_head,l_progress,'p_currency_code',p_currency_code);
491 PO_DEBUG.debug_var(l_log_head,l_progress,'p_need_by_date',p_need_by_date);
492 PO_DEBUG.debug_var(l_log_head,l_progress,'p_quantity',p_quantity);
493 PO_DEBUG.debug_var(l_log_head,l_progress,'p_uom',p_uom);
494 PO_DEBUG.debug_var(l_log_head,l_progress,'p_unit_price',p_unit_price);
495 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Check Advanced Pricing License');
496 END IF;
497
498 FND_PROFILE.get('QP_LICENSED_FOR_PRODUCT',l_qp_license);
499 l_progress := '020';
500 IF g_debug_stmt THEN
501 PO_DEBUG.debug_var(l_log_head,l_progress,'l_qp_license',l_qp_license);
502 END IF;
503
504 --Bug 5555953: Remove the logic to nullify the output unitprice if the Adv Pricing API
505 --is not installed or licensed to PO;
506 IF (l_qp_license IS NULL OR l_qp_license <> 'PO') THEN
507 RETURN;
508 END IF;
509
510 l_progress := '040';
511 IF g_debug_stmt THEN
512 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Set Price Request ID');
513 END IF;
514
515 QP_PRICE_REQUEST_CONTEXT.set_request_id;
516
517 l_progress := '060';
518 IF g_debug_stmt THEN
519 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Populate Global Header Structure');
520 END IF;
521
522 populate_header_record(
523 p_org_id => p_org_id,
524 p_order_header_id => p_order_header_id,
525 p_supplier_id => p_supplier_id,
526 p_supplier_site_id => p_supplier_site_id,
527 p_creation_date => p_creation_date,
528 p_order_type => p_order_type,
529 p_ship_to_location_id => p_ship_to_location_id,
530 p_ship_to_org_id => p_ship_to_org_id);
531
532 l_progress := '080';
533 IF g_debug_stmt THEN
534 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Populate Global Line Structure');
535 END IF;
536
537 populate_line_record(
538 p_order_line_id => p_order_line_id,
539 p_item_revision => p_item_revision,
540 p_item_id => p_item_id,
541 p_category_id => p_category_id,
542 p_supplier_item_num => p_supplier_item_num,
543 p_agreement_type => p_agreement_type,
544 p_agreement_id => p_agreement_id,
545 p_agreement_line_id => p_agreement_line_id, --<R12 GBPA Adv Pricing>
546 p_supplier_id => p_supplier_id,
547 p_supplier_site_id => p_supplier_site_id,
548 p_ship_to_location_id => p_ship_to_location_id,
549 p_ship_to_org_id => p_ship_to_org_id,
550 p_rate => p_rate,
551 p_rate_type => p_rate_type,
552 p_currency_code => p_currency_code,
553 p_need_by_date => p_need_by_date);
554
555
556 l_progress := '090';
557 IF g_debug_stmt THEN
558 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Set OE Debug');
559 OE_DEBUG_PUB.SetDebugLevel(10);
560 PO_DEBUG.debug_stmt(l_log_head,l_progress, 'Debug File Location:'||
561 OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
562 OE_DEBUG_PUB.Initialize;
563 OE_DEBUG_PUB.Debug_On;
564 END IF;
565
566 l_progress := '100';
567 IF g_debug_stmt THEN
568 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Build Attributes Mapping Contexts');
569 END IF;
570
571 QP_Attr_Mapping_PUB.Build_Contexts(
572 p_request_type_code => 'PO',
573 p_line_index => 1,
574 p_pricing_type_code => 'L',
575 p_check_line_flag => 'N',
576 p_pricing_event => 'PO_BATCH',
577 x_pass_line => l_pass_line);
578
579 l_progress := '110';
580 IF g_debug_stmt THEN
581 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Get UOM Code');
582 END IF;
583
584 BEGIN
585 -- Make sure we pass uom_code instead of unit_of_measure.
586 SELECT mum.uom_code
587 INTO l_uom_code
588 FROM mtl_units_of_measure mum
589 WHERE mum.unit_of_measure = p_uom;
590 EXCEPTION
591 WHEN OTHERS THEN
592 l_uom_code := p_uom;
593 END;
594
595 l_progress := '120';
596 IF g_debug_stmt THEN
597 PO_DEBUG.debug_var(l_log_head,l_progress,'l_uom_code',l_uom_code);
598 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Directly Insert into Temp table');
599 END IF;
600
601 l_request_type_code_tbl(1) := 'PO';
602 l_line_id_tbl(1) := l_line_id; -- order line id
603 l_line_index_tbl(1) := 1; -- Request Line Index
604 l_line_type_code_tbl(1) := 'LINE'; -- LINE or ORDER(Summary Line)
605 l_pricinl_effective_date_tbl(1) := p_need_by_date;-- Pricing as of effective date
606 l_active_date_first_tbl(1) := NULL; -- Can be Ordered Date or Ship Date
607 l_active_date_second_tbl(1) := NULL; -- Can be Ordered Date or Ship Date
608 l_active_date_first_type_tbl(1) := NULL; -- ORD/SHIP
609 l_active_date_second_type_tbl(1) := NULL; -- ORD/SHIP
610 l_line_unit_price_tbl(1) := p_unit_price;-- Unit Price
611 -- Bug 3315550, should pass 1 instead of NULL
612 l_line_quantity_tbl(1) := NVL(p_quantity, 1);-- Ordered Quantity
613 -- Bug 3564136, don't pass 0, pass 1 instead
614 IF (l_line_quantity_tbl(1) = 0) THEN
615 l_line_quantity_tbl(1) := 1;
616 END IF; /*IF (l_line_quantity_tbl(1) = 0)*/
617
618 l_line_uom_code_tbl(1) := l_uom_code; -- Ordered UOM Code
619 l_currency_code_tbl(1) := p_currency_code;-- Currency Code
620 l_price_flag_tbl(1) := 'Y'; -- Price Flag can have 'Y',
621 -- 'N'(No pricing),
622 -- 'P'(Phase)
623 l_usage_pricing_type_tbl(1) := QP_PREQ_GRP.g_regular_usage_type;
624 -- Bug 3564136, don't pass 0, pass 1 instead
625 -- Bug 3315550, should pass 1 instead of NULL
626 l_priced_quantity_tbl(1) := NVL(p_quantity, 1);
627 -- Bug 3564136, don't pass 0, pass 1 instead
628 IF (l_priced_quantity_tbl(1) = 0) THEN
629 l_priced_quantity_tbl(1) := 1;
630 END IF; /*IF (l_line_quantity_tbl(1) = 0)*/
631 l_priced_uom_code_tbl(1) := l_uom_code;
632 l_unit_price_tbl(1) := p_unit_price;
633 l_percent_price_tbl(1) := null;
634 l_uom_quantity_tbl(1) := null;
635 l_adjusted_unit_price_tbl(1) := null;
636 l_upd_adjusted_unit_price_tbl(1) := null;
637 l_processed_flag_tbl(1) := null;
638 l_processing_order_tbl(1) := null;
639 l_pricing_status_code_tbl(1) := QP_PREQ_GRP.g_status_unchanged;
640 l_pricing_status_text_tbl(1) := null;
641 l_rounding_flag_tbl(1) := null;
642 l_rounding_factor_tbl(1) := null;
643 l_qualifiers_exist_flag_tbl(1) := 'N';
644 l_pricing_attrs_exist_flag_tbl(1) := 'N';
645 l_price_list_id_tbl(1) := -9999;
646 l_pl_validated_flag_tbl(1) := 'N';
647 l_price_request_code_tbl(1) := null;
648 l_line_category_tbl(1) := null;
649 l_list_price_overide_flag_tbl(1) := 'O'; -- Override price
650
651 l_progress := '140';
652 IF g_debug_stmt THEN
653 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call INSERT_LINES2');
654 END IF;
655
656 QP_PREQ_GRP.INSERT_LINES2
657 (p_line_index => l_line_index_tbl,
658 p_line_type_code => l_line_type_code_tbl,
659 p_pricing_effective_date => l_pricinl_effective_date_tbl,
660 p_active_date_first => l_active_date_first_tbl,
661 p_active_date_first_type => l_active_date_first_type_tbl,
662 p_active_date_second => l_active_date_second_tbl,
663 p_active_date_second_type => l_active_date_second_type_tbl,
664 p_line_quantity => l_line_quantity_tbl,
665 p_line_uom_code => l_line_uom_code_tbl,
666 p_request_type_code => l_request_type_code_tbl,
667 p_priced_quantity => l_priced_quantity_tbl,
668 p_priced_uom_code => l_priced_uom_code_tbl,
669 p_currency_code => l_currency_code_tbl,
670 p_unit_price => l_unit_price_tbl,
671 p_percent_price => l_percent_price_tbl,
672 p_uom_quantity => l_uom_quantity_tbl,
673 p_adjusted_unit_price => l_adjusted_unit_price_tbl,
674 p_upd_adjusted_unit_price => l_upd_adjusted_unit_price_tbl,
675 p_processed_flag => l_processed_flag_tbl,
676 p_price_flag => l_price_flag_tbl,
677 p_line_id => l_line_id_tbl,
678 p_processing_order => l_processing_order_tbl,
679 p_pricing_status_code => l_pricing_status_code_tbl,
680 p_pricing_status_text => l_pricing_status_text_tbl,
681 p_rounding_flag => l_rounding_flag_tbl,
682 p_rounding_factor => l_rounding_factor_tbl,
683 p_qualifiers_exist_flag => l_qualifiers_exist_flag_tbl,
684 p_pricing_attrs_exist_flag => l_pricing_attrs_exist_flag_tbl,
685 p_price_list_id => l_price_list_id_tbl,
686 p_validated_flag => l_pl_validated_flag_tbl,
687 p_price_request_code => l_price_request_code_tbl,
688 p_usage_pricing_type => l_usage_pricing_type_tbl,
689 p_line_category => l_line_category_tbl,
690 p_line_unit_price => l_line_unit_price_tbl,
691 p_list_price_override_flag => l_list_price_overide_flag_tbl,
692 x_status_code => x_return_status,
693 x_status_text => l_return_status_text);
694
695 l_progress := '160';
696 IF g_debug_stmt THEN
697 PO_DEBUG.debug_stmt(l_log_head,l_progress,'After Calling INSERT_LINES2');
698 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
699 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status_text',l_return_status_text);
700 END IF;
701
702 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
703 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
704 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
705 FND_MSG_PUB.Add;
706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
707 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
708 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
709 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
710 FND_MSG_PUB.Add;
711 RAISE FND_API.G_EXC_ERROR;
712 END IF;
713
714 -- Don't call QP_PREQ_GRP.INSERT_LINE_ATTRS2 since PO has no
715 -- ASK_FOR attributes
716
717 l_progress := '180';
718 IF g_debug_stmt THEN
719 PO_DEBUG.debug_stmt(l_log_head,l_progress,
720 'Populate Control Record for Pricing Request Call');
721 END IF;
722
723 l_control_rec.calculate_flag := 'Y';
724 l_control_rec.simulation_flag := 'N';
725 l_control_rec.pricing_event := 'PO_BATCH';
726 l_control_rec.temp_table_insert_flag := 'N';
727 l_control_rec.check_cust_view_flag := 'N';
728 l_control_rec.request_type_code := 'PO';
729 --now pricing take care of all the roundings.
730 l_control_rec.rounding_flag := 'Q';
731 --For multi_currency price list
732 l_control_rec.use_multi_currency :='Y';
733 l_control_rec.user_conversion_rate := PO_ADVANCED_PRICE_PVT.g_line.rate;
734 l_control_rec.user_conversion_type := PO_ADVANCED_PRICE_PVT.g_line.rate_type;
735 l_control_rec.function_currency := PO_ADVANCED_PRICE_PVT.g_line.currency_code;
736 l_control_rec.get_freight_flag := 'N';
737
738 l_progress := '200';
739 IF g_debug_stmt THEN
740 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Call PRICE_REQUEST');
741 END IF;
742
743 QP_PREQ_PUB.PRICE_REQUEST(
744 p_control_rec => l_control_rec,
745 x_return_status => x_return_status,
746 x_return_status_Text => l_return_status_Text);
747
748 l_progress := '220';
749 IF g_debug_stmt THEN
750 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
751 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status_text',l_return_status_text);
752 END IF;
753
754 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
755 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
756 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
757 FND_MSG_PUB.Add;
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
760 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
761 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
762 FND_MSG_PUB.Add;
763 RAISE FND_API.G_EXC_ERROR;
764 END IF;
765
766 -- <Bug 3794940 START>
767 PO_CUSTOM_PRICE_PUB.audit_qp_price_adjustment(
768 p_api_version => 1.0
769 , p_order_type => p_order_type
770 , p_order_line_id => l_line_id
771 , p_line_index => 1
772 , x_return_status => l_return_status
773 , x_msg_count => l_msg_count
774 , x_msg_data => l_msg_data
775 );
776
777 l_progress := '230';
778 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
779 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status',l_return_status);
780 PO_DEBUG.debug_unexp(l_log_head,l_progress,'audit_qp_price_adjustment errors out');
781 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
782 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_msg_data);
783 FND_MSG_PUB.Add;
784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
785 END IF;
786 -- <Bug 3794940 END>
787
788 l_progress := '240';
789 IF g_debug_stmt THEN
790 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Fetch QP pricing');
791 PO_DEBUG.debug_var(l_log_head,l_progress,'l_line_id',l_line_id);
792 PO_DEBUG.debug_table(l_log_head,l_progress,'QP_PREQ_LINES_TMP_T',PO_DEBUG.g_all_rows,NULL,'QP');
793 END IF;
794
795 /* Use API insted
796 -- SQL What: Fetch Price from Pricing Temp table
797 -- SQL Why: Return Advanced Pricing
798 SELECT line_unit_price,
799 adjusted_unit_price,
800 pricing_status_code,
801 pricing_status_text
802 INTO x_base_unit_price,
803 x_unit_price,
804 l_price_status_code,
805 l_price_status_text
806 FROM QP_PREQ_LINES_TMP
807 WHERE line_id = l_line_id
808 AND (processed_code IS NULL OR processed_code <> 'INVALID');
809 */
810
811 QP_PREQ_PUB.get_price_for_line(
812 p_line_index => 1
813 , p_line_id => l_line_id
814 , x_line_unit_price => x_base_unit_price
815 , x_adjusted_unit_price => x_unit_price
816 , x_return_status => x_return_status
817 , x_pricing_status_code => l_price_status_code
818 , x_pricing_status_text => l_price_status_text
819 );
820
821 l_progress := '260';
822 IF g_debug_stmt THEN
823 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',x_return_status);
824 PO_DEBUG.debug_var(l_log_head,l_progress,'x_base_unit_price',x_base_unit_price);
825 PO_DEBUG.debug_var(l_log_head,l_progress,'x_unit_price',x_unit_price);
826 PO_DEBUG.debug_var(l_log_head,l_progress,'l_price_status_code',l_price_status_code);
827 PO_DEBUG.debug_var(l_log_head,l_progress,'l_price_status_text',l_price_status_text);
828 END IF;
829
830 x_unit_price := NVL(x_unit_price, x_base_unit_price);
831
832 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
833 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
834 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_price_status_text);
835 x_return_status := FND_API.g_ret_sts_error;
836 END IF;
837
838 l_progress := '300';
839 IF g_debug_stmt THEN
840 PO_DEBUG.debug_end(l_log_head);
841 PO_DEBUG.debug_var(l_log_head,l_progress,'x_return_status',
842 x_return_status);
843 PO_DEBUG.debug_var(l_log_head,l_progress,'x_base_unit_price',
844 x_base_unit_price);
845 PO_DEBUG.debug_var(l_log_head,l_progress,'x_unit_price',
846 x_unit_price);
847 END IF;
848
849 EXCEPTION
850 WHEN FND_API.G_EXC_ERROR THEN
851 --raised expected error: assume raiser already pushed onto the stack
852 l_exception_msg := FND_MSG_PUB.get(
853 p_msg_index => FND_MSG_PUB.G_LAST
854 , p_encoded => 'F'
855 );
856 IF g_debug_unexp THEN
857 PO_DEBUG.debug_var(l_log_head,l_progress,'l_exception_msg',
858 l_exception_msg);
859 END IF;
860 x_return_status := FND_API.g_ret_sts_error;
861 -- Push the po_return_msg onto msg list and message stack
862 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
863 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_exception_msg);
864
865 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
866 --raised unexpected error: assume raiser already pushed onto the stack
867 l_exception_msg := FND_MSG_PUB.get(
868 p_msg_index => FND_MSG_PUB.G_LAST
869 , p_encoded => 'F'
870 );
871 IF g_debug_unexp THEN
872 PO_DEBUG.debug_var(l_log_head,l_progress,'l_exception_msg',
873 l_exception_msg);
874 END IF;
875 x_return_status := FND_API.g_ret_sts_unexp_error;
876 -- Push the po_return_msg onto msg list and message stack
877 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
878 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_exception_msg);
879
880 WHEN OTHERS THEN
881 IF g_debug_unexp THEN
882 PO_DEBUG.debug_exc(l_log_head,l_progress);
883 END IF;
884 --unexpected error from this procedure: get SQLERRM
885 po_message_s.sql_error(g_pkg_name, l_api_name, l_progress, SQLCODE, SQLERRM);
886 l_exception_msg := FND_MESSAGE.get;
887 IF g_debug_unexp THEN
888 PO_DEBUG.debug_var(l_log_head,l_progress,'l_exception_msg',
889 l_exception_msg);
890 END IF;
891 x_return_status := FND_API.g_ret_sts_unexp_error;
892 -- Push the po_return_msg onto msg list and message stack
893 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
894 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_exception_msg);
895 END get_advanced_price;
896
897
898 --------------------------------------------------------------------------------
899 --Start of Comments
900 --Name: is_valid_qp_line_type
901 --Pre-reqs:
902 -- None.
903 --Modifies:
904 -- None.
905 --Locks:
906 -- None.
907 --Function:
908 -- This procedure checks valid line type for advanced pricing call.
909 -- Any Line type with combination of Value Basis (Amount/Rate/Fixed Price)
910 -- and Purchase Basis (Temp Labor/Services) is invalid.
911 --Parameters:
912 --IN:
913 --p_line_type_id
914 -- Line Type ID.
915 --RETURN:
916 -- FALSE: Invalid Line type to call Advanced Pricing API
917 -- TRUE: Valid Line type to call Advanced Pricing API
918 --Testing:
919 --
920 --End of Comments
921 -------------------------------------------------------------------------------
922 FUNCTION is_valid_qp_line_type
923 ( p_line_type_id IN NUMBER
924 ) RETURN BOOLEAN
925 IS
926 l_api_name CONSTANT varchar2(30) := 'IS_VALID_QP_LINE_TYPE';
927 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
928 l_progress VARCHAR2(3) := '000';
929 l_purchase_basis PO_LINE_TYPES.purchase_basis%TYPE;
930 -- Bug 3343261: should use value_basis instead of matching_basis
931 -- l_matching_basis PO_LINE_TYPES.matching_basis%TYPE;
932 l_value_basis PO_LINE_TYPES.order_type_lookup_code%TYPE;
933 BEGIN
934 IF g_debug_stmt THEN
935 PO_DEBUG.debug_begin(l_log_head);
936 PO_DEBUG.debug_var(l_log_head,l_progress,'p_line_type_id',p_line_type_id);
937 END IF;
938
939 IF (p_line_type_id IS NULL) THEN
940 RETURN TRUE;
941 END IF;
942
943 l_progress := '010';
944 SELECT purchase_basis,
945 -- Bug 3343261: should use value_basis instead of matching_basis
946 -- matching_basis,
947 order_type_lookup_code
948 INTO l_purchase_basis,
949 -- Bug 3343261: should use value_basis instead of matching_basis
950 -- l_matching_basis,
951 l_value_basis
952 FROM PO_LINE_TYPES
953 WHERE line_type_id = p_line_type_id;
954
955 l_progress := '020';
956 IF g_debug_stmt THEN
957 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Retrieved line type');
958 END IF;
959
960 IF (l_purchase_basis IN ('TEMP LABOR', 'SERVICES') AND
961 -- Bug 3343261: should use value_basis instead of matching_basis
962 l_value_basis in ('AMOUNT', 'RATE', 'FIXED PRICE')) THEN
963 RETURN FALSE;
964 END IF;
965
966 l_progress := '030';
967 IF g_debug_stmt THEN
968 PO_DEBUG.debug_end(l_log_head);
969 END IF;
970
971 RETURN TRUE;
972 EXCEPTION
973 WHEN OTHERS THEN
974 RETURN FALSE;
975 END is_valid_qp_line_type;
976
977 -- <FSC R12 START>
978 --------------------------------------------------------------------------------
979 --Start of Comments
980 --Name: get_advanced_price
981 --Pre-reqs:
982 -- None.
983 --Modifies:
984 -- None.
985 --Locks:
986 -- None.
987 --Procedure:
988 -- This procedure calls out to QP for getting the adjusted price and
989 -- freight and speacial charges. This takes in a document in the form of
990 -- header record and a table of line records
991 --Parameters:
992 --IN:
993 -- p_header_rec Header_Rec_Type This will keep the document header information
994 -- p_line_rec_tbl Line_tbl_type This willl keep the table of the lines for the
995 -- p_request_type Request type to be passed to QP.
996 -- p_pricing_event pricing event set up in QP for the processing
997 -- p_has_header_pricing True, when header line is also included in pricing
998 -- p_return_price_flag True, when the caller wants the adjusted rice info
999 -- also in the returned record
1000 -- p_return_freight_flag True, when the caller wants the API to return
1001 -- freight charge related info.
1002 --
1003 --OUT:
1004 -- x_return_status Return status for the QP call.
1005 -- x_price_tbl Table of resulted info from QP for each line
1006 --Testing:
1007 --
1008 --End of Comments
1009 -------------------------------------------------------------------------------
1010 Procedure get_advanced_price(p_header_rec Header_rec_type,
1011 p_line_rec_tbl Line_Tbl_Type,
1012 p_request_type IN VARCHAR2,
1013 p_pricing_event IN VARCHAR2,
1014 p_has_header_pricing IN boolean,
1015 p_return_price_flag IN boolean,
1016 p_return_freight_flag IN boolean,
1017 x_price_tbl OUT NOCOPY Qp_Price_Result_Rec_Tbl_Type,
1018 x_return_status OUT NOCOPY VARCHAR2) IS
1019 l_api_name CONSTANT varchar2(30) := 'GET_ADVANCED_PRICE';
1020 l_log_head CONSTANT varchar2(100) := g_log_head || l_api_name;
1021 d_pos NUMBER;
1022 l_exception_msg FND_NEW_MESSAGES.message_text%TYPE;
1023 l_qp_license VARCHAR2(30) := NULL;
1024 l_uom_code MTL_UNITS_OF_MEASURE.uom_code%TYPE;
1025 l_return_status_text VARCHAR2(2000);
1026 l_control_rec QP_PREQ_GRP.control_record_type;
1027 l_pass_line VARCHAR2(1);
1028 l_line_index_tbl QP_PREQ_GRP.pls_integer_type;
1029 l_line_type_code_tbl QP_PREQ_GRP.varchar_type;
1030 l_pricinl_effective_date_tbl QP_PREQ_GRP.date_type ;
1031 l_active_date_first_tbl QP_PREQ_GRP.date_type ;
1032 l_active_date_first_type_tbl QP_PREQ_GRP.varchar_type;
1033 l_active_date_second_tbl QP_PREQ_GRP.date_type ;
1034 l_active_date_second_type_tbl QP_PREQ_GRP.varchar_type ;
1035 l_line_unit_price_tbl QP_PREQ_GRP.number_type ;
1036 l_line_quantity_tbl QP_PREQ_GRP.number_type ;
1037 l_line_uom_code_tbl QP_PREQ_GRP.varchar_type;
1038 l_request_type_code_tbl QP_PREQ_GRP.varchar_type;
1039 l_priced_quantity_tbl QP_PREQ_GRP.number_type;
1040 l_uom_quantity_tbl QP_PREQ_GRP.number_type;
1041 l_priced_uom_code_tbl QP_PREQ_GRP.varchar_type;
1042 l_currency_code_tbl QP_PREQ_GRP.varchar_type;
1043 l_unit_price_tbl QP_PREQ_GRP.number_type;
1044 l_percent_price_tbl QP_PREQ_GRP.number_type;
1045 l_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
1046 l_upd_adjusted_unit_price_tbl QP_PREQ_GRP.number_type;
1047 l_processed_flag_tbl QP_PREQ_GRP.varchar_type;
1048 l_price_flag_tbl QP_PREQ_GRP.varchar_type;
1049 l_line_id_tbl QP_PREQ_GRP.number_type;
1050 l_processing_order_tbl QP_PREQ_GRP.pls_integer_type;
1051 l_rounding_factor_tbl QP_PREQ_GRP.pls_integer_type;
1052 l_rounding_flag_tbl QP_PREQ_GRP.flag_type;
1053 l_qualifiers_exist_flag_tbl QP_PREQ_GRP.varchar_type;
1054 l_pricing_attrs_exist_flag_tbl QP_PREQ_GRP.varchar_type;
1055 l_price_list_id_tbl QP_PREQ_GRP.number_type;
1056 l_pl_validated_flag_tbl QP_PREQ_GRP.varchar_type;
1057 l_price_request_code_tbl QP_PREQ_GRP.varchar_type;
1058 l_usage_pricing_type_tbl QP_PREQ_GRP.varchar_type;
1059 l_line_category_tbl QP_PREQ_GRP.varchar_type;
1060 l_pricing_status_code_tbl QP_PREQ_GRP.varchar_type;
1061 l_pricing_status_text_tbl QP_PREQ_GRP.varchar_type;
1062 l_list_price_overide_flag_tbl QP_PREQ_GRP.varchar_type;
1063 i PLS_INTEGER :=1;
1064 j PLS_INTEGER :=1;
1065 k PLS_INTEGER :=1;
1066 l_freight_charge_rec_tbl Freight_Charges_Rec_Tbl_Type;
1067 BEGIN
1068
1069 -- Initialize OUT parameters
1070 x_return_status := FND_API.G_RET_STS_SUCCESS;
1071 IF g_debug_stmt THEN
1072 PO_LOG.proc_begin(l_log_head);
1073 PO_LOG.proc_begin(l_log_head,'p_header_rec.org_id',p_header_rec.org_id);
1074 PO_LOG.proc_begin(l_log_head,'p_header_rec.p_order_header_id',p_header_rec.p_order_header_id);
1075 PO_LOG.proc_begin(l_log_head,'p_header_rec.supplier_id',p_header_rec.supplier_id);
1076 PO_LOG.proc_begin(l_log_head,'p_header_rec.supplier_site_id',p_header_rec.supplier_site_id);
1077 PO_LOG.proc_begin(l_log_head,'p_header_rec.creation_date',p_header_rec.creation_date);
1078 PO_LOG.proc_begin(l_log_head,'p_header_rec.order_type',p_header_rec.order_type);
1079 PO_LOG.proc_begin(l_log_head,'p_header_rec.ship_to_location_id',p_header_rec.ship_to_location_id);
1080 PO_LOG.proc_begin(l_log_head,'p_header_rec.ship_to_org_id',p_header_rec.ship_to_org_id);
1081 PO_LOG.proc_begin(l_log_head,'p_header_rec.shipment_header_id',p_header_rec.shipment_header_id);
1082 PO_LOG.proc_begin(l_log_head,'p_header_rec.hazard_class',p_header_rec.hazard_class);
1083 PO_LOG.proc_begin(l_log_head,'p_header_rec.hazard_code',p_header_rec.hazard_code);
1084 PO_LOG.proc_begin(l_log_head,'p_header_rec.shipped_date',p_header_rec.shipped_date);
1085 PO_LOG.proc_begin(l_log_head,'p_header_rec.shipment_num',p_header_rec.shipment_num);
1086 PO_LOG.proc_begin(l_log_head,'p_header_rec.carrier_method',p_header_rec.carrier_method);
1087 PO_LOG.proc_begin(l_log_head,'p_header_rec.packaging_code',p_header_rec.packaging_code);
1088 PO_LOG.proc_begin(l_log_head,'p_header_rec.freight_carrier_code',p_header_rec.freight_carrier_code);
1089 PO_LOG.proc_begin(l_log_head,'p_header_rec.freight_terms',p_header_rec.freight_terms);
1090 PO_LOG.proc_begin(l_log_head,'p_header_rec.currency_code',p_header_rec.currency_code);
1091 PO_LOG.proc_begin(l_log_head,'p_header_rec.rate',p_header_rec.rate);
1092 PO_LOG.proc_begin(l_log_head,'p_header_rec.rate_type',p_header_rec.rate_type);
1093
1094
1095 for i IN 1..p_line_rec_tbl.COUNT LOOP
1096 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').order_line_id',p_line_rec_tbl(i).order_line_id);
1097 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').agreement_type',p_line_rec_tbl(i).agreement_type);
1098 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').agreement_id',p_line_rec_tbl(i).agreement_id);
1099 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').agreement_line_id',p_line_rec_tbl(i).agreement_line_id);
1100 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').supplier_id',p_line_rec_tbl(i).supplier_id);
1101 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').supplier_site_id',p_line_rec_tbl(i).supplier_site_id);
1102 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);
1103 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);
1104 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').supplier_item_num',p_line_rec_tbl(i).supplier_item_num);
1105 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').item_revision',p_line_rec_tbl(i).item_revision);
1106 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').item_id',p_line_rec_tbl(i).item_id);
1107 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').category_id',p_line_rec_tbl(i).category_id);
1108 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').rate',p_line_rec_tbl(i).rate);
1109 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').rate_type',p_line_rec_tbl(i).rate_type);
1110 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').currency_code',p_line_rec_tbl(i).currency_code);
1111 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').need_by_date',p_line_rec_tbl(i).need_by_date);
1112 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').shipment_line_id',p_line_rec_tbl(i).shipment_line_id);
1113 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);
1114 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').to_organization_id',p_line_rec_tbl(i).to_organization_id);
1115 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').unit_of_measure',p_line_rec_tbl(i).unit_of_measure);
1116 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').source_document_code',p_line_rec_tbl(i).source_document_code);
1117 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').unit_price',p_line_rec_tbl(i).unit_price);
1118 PO_LOG.proc_begin(l_log_head,'p_line_rec_tbl('||i||').quantity',p_line_rec_tbl(i).quantity);
1119 end LOOP;
1120
1121 PO_LOG.proc_begin(l_log_head,'p_request_type',p_request_type);
1122 PO_LOG.proc_begin(l_log_head,'p_pricing_event',p_pricing_event);
1123 PO_LOG.proc_begin(l_log_head,'p_has_header_pricing',p_has_header_pricing);
1124 PO_LOG.proc_begin(l_log_head,'p_return_price_flag',p_return_price_flag);
1125 PO_LOG.proc_begin(l_log_head,'p_return_freight_flag',p_return_freight_flag);
1126 END IF;
1127
1128 x_price_tbl := Qp_Price_Result_Rec_Tbl_Type();
1129 PO_LOG.stmt(l_log_head,d_pos,'Check Advanced Pricing License');
1130 FND_PROFILE.get('QP_LICENSED_FOR_PRODUCT',l_qp_license);
1131 d_pos := 20;
1132 IF g_debug_stmt THEN
1133 PO_LOG.stmt(l_log_head,d_pos,'l_qp_license',l_qp_license);
1134 END IF;
1135
1136 IF (l_qp_license IS NULL OR l_qp_license <> 'PO') THEN
1137 RETURN;
1138 END IF;
1139
1140 d_pos := 40;
1141 IF g_debug_stmt THEN
1142 PO_LOG.stmt(l_log_head,d_pos,'Set Price Request ID');
1143 END IF;
1144
1145 QP_PRICE_REQUEST_CONTEXT.set_request_id;
1146
1147 d_pos := 60;
1148 IF g_debug_stmt THEN
1149 PO_LOG.stmt(l_log_head,d_pos,'Populate Global Header Structure');
1150 END IF;
1151
1152 populate_header_record(
1153 p_org_id => p_header_rec.org_id,
1154 p_order_header_id => p_header_rec.p_order_header_id,
1155 p_supplier_id => p_header_rec.supplier_id,
1156 p_supplier_site_id => p_header_rec.supplier_site_id,
1157 p_creation_date => p_header_rec.creation_date,
1158 p_order_type => p_header_rec.order_type,
1159 p_ship_to_location_id => p_header_rec.ship_to_location_id,
1160 p_ship_to_org_id => p_header_rec.ship_to_org_id,
1161 -- New Attributes for R12: Receving FSC support
1162 p_shipment_header_id => p_header_rec.shipment_header_id,
1163 p_hazard_class => p_header_rec.hazard_class,
1164 p_hazard_code => p_header_rec.hazard_code,
1165 p_shipped_date => p_header_rec.shipped_date,
1166 p_shipment_num => p_header_rec.shipment_num,
1167 p_carrier_method => p_header_rec.carrier_method,
1168 p_packaging_code => p_header_rec.packaging_code,
1169 p_freight_carrier_code => p_header_rec.freight_carrier_code,
1170 p_freight_terms => p_header_rec.freight_terms,
1171 p_currency_code => p_header_rec.currency_code,
1172 p_rate => p_header_rec.rate,
1173 p_rate_type => p_header_rec.rate_type,
1174 p_source_org_id => p_header_rec.source_org_id,
1175 p_expected_receipt_date => p_header_rec.expected_receipt_date);
1176
1177 d_pos := 80;
1178 IF g_debug_stmt THEN
1179 PO_LOG.stmt(l_log_head,d_pos ,'Populate Global Line Structure');
1180 END IF;
1181
1182 i := 1;
1183
1184 d_pos := 90;
1185 IF g_debug_stmt THEN
1186 PO_LOG.stmt(l_log_head,d_pos,'Set OE Debug');
1187 OE_DEBUG_PUB.SetDebugLevel(10);
1188 PO_LOG.stmt(l_log_head,d_pos, 'Debug File Location: '||OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
1189 OE_DEBUG_PUB.Initialize;
1190 OE_DEBUG_PUB.Debug_On;
1191 END IF;
1192
1193 d_pos := 100;
1194
1195 if(p_has_header_pricing) then
1196 d_pos:= 110;
1197 IF g_debug_stmt THEN
1198 PO_LOG.stmt(l_log_head,d_pos,'Build Attributes Mapping Contexts for header');
1199 END IF;
1200 QP_Attr_Mapping_PUB.Build_Contexts(p_request_type_code => p_request_type,
1201 p_line_index => i,
1202 p_pricing_type_code => 'H',
1203 p_check_line_flag => 'N',
1204 p_pricing_event => p_pricing_event,
1205 x_pass_line => l_pass_line);
1206 d_pos := 120;
1207
1208 l_request_type_code_tbl(i) := p_request_type;
1209 l_line_id_tbl(i) := nvl(p_header_rec.p_order_header_id,p_header_rec.shipment_header_id);-- header id
1210 l_line_index_tbl(i) := i; -- Request Line Index
1211 l_line_type_code_tbl(i) := 'ORDER'; -- LINE or ORDER(Summary Line)
1212 l_pricinl_effective_date_tbl(i) := sysdate;-- Pricing as of effective date
1213 l_active_date_first_tbl(i) := NULL; -- Can be Ordered Date or Ship Date
1214 l_active_date_second_tbl(i) := NULL; -- Can be Ordered Date or Ship Date
1215 l_active_date_first_type_tbl(i) := NULL; -- ORD/SHIP
1216 l_active_date_second_type_tbl(i) := NULL; -- ORD/SHIP
1217 l_line_unit_price_tbl(i) := NULL;-- Unit Price
1218 l_line_quantity_tbl(i) := NULL;-- Ordered Quantity
1219 l_line_uom_code_tbl(i) := NULL;-- Ordered UOM Code
1220 l_currency_code_tbl(i) := p_header_rec.currency_code;-- Currency Code
1221 l_price_flag_tbl(i) := 'Y'; -- Price Flag can have 'Y',
1222 -- 'N'(No pricing),
1223 -- 'P'(Phase)
1224 l_usage_pricing_type_tbl(i) := QP_PREQ_GRP.g_regular_usage_type;
1225 l_priced_quantity_tbl(i) := NULL;
1226 l_priced_uom_code_tbl(i) := null;
1227 l_unit_price_tbl(i) := null;
1228 l_percent_price_tbl(i) := null;
1229 l_uom_quantity_tbl(i) := null;
1230 l_adjusted_unit_price_tbl(i) := null;
1231 l_upd_adjusted_unit_price_tbl(i) := null;
1232 l_processed_flag_tbl(i) := null;
1233 l_processing_order_tbl(i) := null;
1234 l_pricing_status_code_tbl(i) := QP_PREQ_GRP.g_status_unchanged;
1235 l_pricing_status_text_tbl(i) := null;
1236 l_rounding_flag_tbl(i) := null;
1237 l_rounding_factor_tbl(i) := null;
1238 l_qualifiers_exist_flag_tbl(i) := 'N';
1239 l_pricing_attrs_exist_flag_tbl(i):= 'N';
1240 l_price_list_id_tbl(i) := -9999;
1241 l_pl_validated_flag_tbl(i) := 'N';
1242 l_price_request_code_tbl(i) := null;
1243 l_line_category_tbl(i) := null;
1244 l_list_price_overide_flag_tbl(i) := 'O'; -- Override price
1245
1246 i := i+1;
1247 d_pos := 130;
1248 end if;
1249
1250
1251 for j IN 1..p_line_rec_tbl.COUNT LOOP
1252 populate_line_record(
1253 p_order_line_id => p_line_rec_tbl(j).order_line_id,
1254 p_item_revision => p_line_rec_tbl(j).item_revision,
1255 p_item_id => p_line_rec_tbl(j).item_id,
1256 p_category_id => p_line_rec_tbl(j).category_id,
1257 p_supplier_item_num => p_line_rec_tbl(j).supplier_item_num,
1258 p_agreement_type => p_line_rec_tbl(j).agreement_type,
1259 p_agreement_id => p_line_rec_tbl(j).agreement_id,
1260 p_agreement_line_id => p_line_rec_tbl(j).agreement_line_id, --<R12 GBPA Adv Pricing>
1261 p_supplier_id => p_line_rec_tbl(j).supplier_id,
1262 p_supplier_site_id => p_line_rec_tbl(j).supplier_site_id,
1263 p_ship_to_location_id => p_line_rec_tbl(j).ship_to_location_id,
1264 p_ship_to_org_id => p_line_rec_tbl(j).ship_to_org_id,
1265 p_rate => p_line_rec_tbl(j).rate,
1266 p_rate_type => p_line_rec_tbl(j).rate_type,
1267 p_currency_code => p_line_rec_tbl(j).currency_code,
1268 p_need_by_date => p_line_rec_tbl(j).need_by_date,
1269 -- New Attributes for R12: Receving FSC support
1270 p_shipment_line_id => p_line_rec_tbl(j).shipment_line_id,
1271 p_primary_unit_of_measure => p_line_rec_tbl(j).primary_unit_of_measure,
1272 p_to_organization_id => p_line_rec_tbl(j).to_organization_id,
1273 p_unit_of_measure => p_line_rec_tbl(j).unit_of_measure,
1274 p_source_document_code=> p_line_rec_tbl(j).source_document_code,
1275 p_quantity => p_line_rec_tbl(j).quantity);
1276
1277 d_pos := 140;
1278
1279 IF g_debug_stmt THEN
1280 --make the pricing debug ON
1281 PO_DEBUG.debug_stmt(l_log_head,d_pos,'Set OE Debug');
1282 OE_DEBUG_PUB.SetDebugLevel(10);
1283 PO_DEBUG.debug_stmt(l_log_head,d_pos, 'Debug File Location: '||
1284 OE_DEBUG_PUB.Set_Debug_Mode('FILE'));
1285 OE_DEBUG_PUB.Initialize;
1286 OE_DEBUG_PUB.Debug_On;
1287 END IF;
1288
1289 d_pos := 150;
1290 IF g_debug_stmt THEN
1291 PO_LOG.stmt(l_log_head,d_pos,'Build Attributes Mapping Contexts for Line('||j||')');
1292 END IF;
1293
1294 QP_Attr_Mapping_PUB.Build_Contexts(
1295 p_request_type_code => p_request_type,
1296 p_line_index => i,
1297 p_pricing_type_code => 'L',
1298 p_check_line_flag => 'N',
1299 p_pricing_event => p_pricing_event,
1300 x_pass_line => l_pass_line);
1301
1302 d_pos := 160;
1303 IF g_debug_stmt THEN
1304 PO_LOG.stmt(l_log_head,d_pos,'Get UOM Code');
1305 END IF;
1306
1307 BEGIN
1308 -- Make sure we pass uom_code instead of unit_of_measure.
1309 SELECT mum.uom_code
1310 INTO l_uom_code
1311 FROM mtl_units_of_measure mum
1312 WHERE mum.unit_of_measure = p_line_rec_tbl(j).unit_of_measure;
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 l_uom_code := p_line_rec_tbl(j).unit_of_measure;
1316 END;
1317
1318 d_pos := 170;
1319 IF g_debug_stmt THEN
1320 PO_LOG.stmt(l_log_head,d_pos,'l_uom_code',l_uom_code);
1321 PO_LOG.stmt(l_log_head,d_pos,'Directly Insert into Temp table');
1322 END IF;
1323
1324 l_request_type_code_tbl(i) := p_request_type;
1325 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
1326 l_line_index_tbl(i) := i; -- Request Line Index
1327 l_line_type_code_tbl(i) := 'LINE'; -- LINE or ORDER(Summary Line)
1328 l_pricinl_effective_date_tbl(i) := p_line_rec_tbl(j).need_by_date;-- Pricing as of effective date
1329 l_active_date_first_tbl(i) := NULL; -- Can be Ordered Date or Ship Date
1330 l_active_date_second_tbl(i) := NULL; -- Can be Ordered Date or Ship Date
1331 l_active_date_first_type_tbl(i) := NULL; -- ORD/SHIP
1332 l_active_date_second_type_tbl(i):= NULL; -- ORD/SHIP
1333 l_line_unit_price_tbl(i) := p_line_rec_tbl(j).unit_price;-- Unit Price
1334 l_line_quantity_tbl(i) := NVL(p_line_rec_tbl(j).quantity, 1);-- Ordered Quantity
1335
1336 IF (l_line_quantity_tbl(i) = 0) THEN
1337 l_line_quantity_tbl(i) := 1;
1338 END IF;
1339
1340 l_line_uom_code_tbl(i) := l_uom_code; -- Ordered UOM Code
1341 l_currency_code_tbl(i) := p_line_rec_tbl(j).currency_code;-- Currency Code
1342 l_price_flag_tbl(i) := 'Y'; -- Price Flag can have 'Y',
1343 -- 'N'(No pricing),
1344 -- 'P'(Phase)
1345 l_usage_pricing_type_tbl(i) := QP_PREQ_GRP.g_regular_usage_type;
1346 l_priced_quantity_tbl(i) := NVL(p_line_rec_tbl(j).quantity, 1);
1347 IF (l_priced_quantity_tbl(i) = 0) THEN
1348 l_priced_quantity_tbl(i) := 1;
1349 END IF;
1350 l_priced_uom_code_tbl(i) := l_uom_code;
1351 l_unit_price_tbl(i) := p_line_rec_tbl(j).unit_price;
1352 l_percent_price_tbl(i) := null;
1353 l_uom_quantity_tbl(i) := null;
1354 l_adjusted_unit_price_tbl(i) := null;
1355 l_upd_adjusted_unit_price_tbl(i):= null;
1356 l_processed_flag_tbl(i) := null;
1357 l_processing_order_tbl(i) := null;
1358 l_pricing_status_code_tbl(i) := QP_PREQ_GRP.g_status_unchanged;
1359 l_pricing_status_text_tbl(i) := null;
1360 l_rounding_flag_tbl(i) := null;
1361 l_rounding_factor_tbl(i) := null;
1362 l_qualifiers_exist_flag_tbl(i) := 'N';
1363 l_pricing_attrs_exist_flag_tbl(i):= 'N';
1364 l_price_list_id_tbl(i) := -9999;
1365 l_pl_validated_flag_tbl(i) := 'N';
1366 l_price_request_code_tbl(i) := null;
1367 l_line_category_tbl(i) := null;
1368 l_list_price_overide_flag_tbl(i):= 'O'; -- Override price
1369
1370 i := i+1;
1371
1372 end LOOP;
1373
1374 d_pos:= 180;
1375 IF g_debug_stmt THEN
1376 PO_LOG.stmt(l_log_head,d_pos,'Call INSERT_LINES2');
1377 END IF;
1378
1379 QP_PREQ_GRP.INSERT_LINES2
1380 (p_line_index => l_line_index_tbl,
1381 p_line_type_code => l_line_type_code_tbl,
1382 p_pricing_effective_date => l_pricinl_effective_date_tbl,
1383 p_active_date_first => l_active_date_first_tbl,
1384 p_active_date_first_type => l_active_date_first_type_tbl,
1385 p_active_date_second => l_active_date_second_tbl,
1386 p_active_date_second_type => l_active_date_second_type_tbl,
1387 p_line_quantity => l_line_quantity_tbl,
1388 p_line_uom_code => l_line_uom_code_tbl,
1389 p_request_type_code => l_request_type_code_tbl,
1390 p_priced_quantity => l_priced_quantity_tbl,
1391 p_priced_uom_code => l_priced_uom_code_tbl,
1392 p_currency_code => l_currency_code_tbl,
1393 p_unit_price => l_unit_price_tbl,
1394 p_percent_price => l_percent_price_tbl,
1395 p_uom_quantity => l_uom_quantity_tbl,
1396 p_adjusted_unit_price => l_adjusted_unit_price_tbl,
1397 p_upd_adjusted_unit_price => l_upd_adjusted_unit_price_tbl,
1398 p_processed_flag => l_processed_flag_tbl,
1399 p_price_flag => l_price_flag_tbl,
1400 p_line_id => l_line_id_tbl,
1401 p_processing_order => l_processing_order_tbl,
1402 p_pricing_status_code => l_pricing_status_code_tbl,
1403 p_pricing_status_text => l_pricing_status_text_tbl,
1404 p_rounding_flag => l_rounding_flag_tbl,
1405 p_rounding_factor => l_rounding_factor_tbl,
1406 p_qualifiers_exist_flag => l_qualifiers_exist_flag_tbl,
1407 p_pricing_attrs_exist_flag => l_pricing_attrs_exist_flag_tbl,
1408 p_price_list_id => l_price_list_id_tbl,
1409 p_validated_flag => l_pl_validated_flag_tbl,
1410 p_price_request_code => l_price_request_code_tbl,
1411 p_usage_pricing_type => l_usage_pricing_type_tbl,
1412 p_line_category => l_line_category_tbl,
1413 p_line_unit_price => l_line_unit_price_tbl,
1414 p_list_price_override_flag => l_list_price_overide_flag_tbl,
1415 x_status_code => x_return_status,
1416 x_status_text => l_return_status_text);
1417
1418 d_pos:= 190;
1419 IF g_debug_stmt THEN
1420 PO_LOG.stmt(l_log_head,d_pos,'After Calling INSERT_LINES2');
1421 PO_LOG.stmt(l_log_head,d_pos,'x_return_status',x_return_status);
1422 PO_LOG.stmt(l_log_head,d_pos,'l_return_status_text',l_return_status_text);
1423 END IF;
1424
1425 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1426 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1427 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1428 FND_MSG_PUB.Add;
1429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1430 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1431 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1432 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1433 FND_MSG_PUB.Add;
1434 RAISE FND_API.G_EXC_ERROR;
1435 END IF;
1436
1437 -- Don't call QP_PREQ_GRP.INSERT_LINE_ATTRS2 since PO has no
1438 -- ASK_FOR attributes
1439
1440 d_pos:= 200;
1441 IF g_debug_stmt THEN
1442 PO_LOG.stmt(l_log_head,d_pos,'Populate Control Record for Pricing Request Call');
1443 END IF;
1444
1445 l_control_rec.calculate_flag := 'Y';
1446 l_control_rec.simulation_flag := 'N';
1447 l_control_rec.pricing_event := p_pricing_event;
1448 l_control_rec.temp_table_insert_flag := 'N';
1449 l_control_rec.check_cust_view_flag := 'N';
1450 l_control_rec.request_type_code := p_request_type;
1451 --now pricing take care of all the roundings.
1452 l_control_rec.rounding_flag := 'Q';
1453 --For multi_currency price list
1454 l_control_rec.use_multi_currency :='Y';
1455 l_control_rec.user_conversion_rate := p_header_rec.rate;
1456 l_control_rec.user_conversion_type := p_header_rec.rate_type;
1457 l_control_rec.function_currency := p_header_rec.currency_code;
1458 l_control_rec.get_freight_flag := 'N';
1459
1460 d_pos := 200;
1461 IF g_debug_stmt THEN
1462 PO_LOG.stmt(l_log_head,d_pos,'Call PRICE_REQUEST');
1463 END IF;
1464
1465 QP_PREQ_PUB.PRICE_REQUEST(
1466 p_control_rec => l_control_rec,
1467 x_return_status => x_return_status,
1468 x_return_status_Text => l_return_status_Text);
1469
1470 d_pos:= 220;
1471 IF g_debug_stmt THEN
1472 PO_LOG.stmt(l_log_head,d_pos,'x_return_status',x_return_status);
1473 PO_LOG.stmt(l_log_head,d_pos,'l_return_status_text',l_return_status_text);
1474 END IF;
1475
1476 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1477 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1478 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1479 FND_MSG_PUB.Add;
1480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1481 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1482 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1483 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_return_status_text);
1484 FND_MSG_PUB.Add;
1485 RAISE FND_API.G_EXC_ERROR;
1486 END IF;
1487
1488 /** No custom price hook for receiving.. need to
1489 incorporate it. whenever we are planning to use
1490 FSC for PO document
1491 PO_CUSTOM_PRICE_PUB.audit_qp_price_adjustment(
1492 p_api_version => 1.0
1493 , p_order_type => p_order_type
1494 , p_order_line_id => l_line_id
1495 , p_line_index => 1
1496 , x_return_status => l_return_status
1497 , x_msg_count => l_msg_count
1498 , x_msg_data => l_msg_data
1499 );
1500
1501 l_progress := '230';
1502 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1503 PO_DEBUG.debug_var(l_log_head,l_progress,'l_return_status',l_return_status);
1504 PO_DEBUG.debug_unexp(l_log_head,l_progress,'audit_qp_price_adjustment errors out');
1505 FND_MESSAGE.SET_NAME('PO','PO_QP_PRICE_API_ERROR');
1506 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_msg_data);
1507 FND_MSG_PUB.Add;
1508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1509 END IF;
1510 */
1511
1512 d_pos := 240;
1513 IF g_debug_stmt THEN
1514 PO_LOG.stmt(l_log_head,d_pos,'Fetch QP pricing');
1515 PO_LOG.stmt(l_log_head,d_pos,'p_header_rec.p_order_header_id',p_header_rec.p_order_header_id);
1516 PO_LOG.stmt(l_log_head,d_pos,'p_header_rec.shipment_header_id',p_header_rec.shipment_header_id);
1517 PO_LOG.stmt(l_log_head,d_pos,'QP_PREQ_LINES_TMP_T',PO_LOG.c_all_rows);
1518 END IF;
1519
1520 if p_return_price_flag then
1521 --Access the QP views to retrieve the values for price
1522 d_pos := 250;
1523
1524 FOR j IN 1..i-1 LOOP
1525 x_price_tbl.extend();
1526
1527 select line_index,
1528 line_id,
1529 line_unit_price base_unit_price, -- base price
1530 order_uom_selling_price adjusted_price, -- adjusted_price
1531 pricing_status_code, --pricing status code
1532 pricing_status_text -- pricing status text
1533 into
1534 x_price_tbl(j).line_index,
1535 x_price_tbl(j).line_id,
1536 x_price_tbl(j).base_unit_price,
1537 x_price_tbl(j).adjusted_price,
1538 x_price_tbl(j).pricing_status_code,
1539 x_price_tbl(j).pricing_status_text
1540 from qp_preq_lines_tmp
1541 where line_index = j;
1542
1543 d_pos := 260;
1544
1545 END LOOP;
1546 end if;
1547
1548 if p_return_freight_flag then
1549 d_pos := 270;
1550
1551 FOR j IN 1..i-1 LOOP
1552 -- query to qp_ldets_v to retrieve the freight charge info.
1553 select charge_type_code,
1554 order_qty_adj_amt freight_charge,
1555 pricing_status_code,
1556 pricing_status_text BULK COLLECT into l_freight_charge_rec_tbl
1557 from qp_ldets_v
1558 where line_index = j
1559 and list_line_type_code='FREIGHT_CHARGE'
1560 and applied_flag = 'Y';
1561
1562 if not p_return_price_flag then
1563 x_price_tbl.extend();
1564 end if;
1565
1566 d_pos := 280;
1567
1568 x_price_tbl(j).line_index := l_line_index_tbl(j);
1569 x_price_tbl(j).base_unit_price := l_unit_price_tbl(j);
1570 x_price_tbl(j).freight_charge_rec_tbl := l_freight_charge_rec_tbl;
1571 x_price_tbl(j).line_id := l_line_id_tbl(j);
1572
1573 select pricing_status_code, pricing_status_text into
1574 x_price_tbl(j).pricing_status_code,
1575 x_price_tbl(j).pricing_status_text
1576 from
1577 qp_preq_lines_tmp where line_index =j;
1578 d_pos := 290;
1579 END LOOP;
1580 End if;
1581
1582
1583 d_pos:= 300;
1584 IF g_debug_stmt THEN
1585 PO_LOG.proc_end(l_log_head);
1586 PO_LOG.proc_end(l_log_head,'x_return_status',x_return_status);
1587 FOR j IN 1..x_price_tbl.COUNT LOOP
1588 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').line_index',x_price_tbl(j).line_index);
1589 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').line_id',x_price_tbl(j).line_id);
1590 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').base_unit_price',x_price_tbl(j).base_unit_price);
1591 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').adjusted_price',x_price_tbl(j).adjusted_price);
1592 FOR k IN 1..x_price_tbl(j).freight_charge_rec_tbl.COUNT LOOP
1593 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').freight_charge_rec_tbl('||k||').charge_type_code'
1594 ,x_price_tbl(j).freight_charge_rec_tbl(k).charge_type_code);
1595 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').freight_charge_rec_tbl('||k||').freight_charge'
1596 ,x_price_tbl(j).freight_charge_rec_tbl(k).freight_charge);
1597 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').freight_charge_rec_tbl('||k||').pricing_status_code'
1598 ,x_price_tbl(j).freight_charge_rec_tbl(k).pricing_status_code);
1599 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').freight_charge_rec_tbl('||k||').pricing_status_text'
1600 ,x_price_tbl(j).freight_charge_rec_tbl(k).pricing_status_text);
1601 end LOOP;
1602 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').pricing_status_code',x_price_tbl(j).pricing_status_code);
1603 PO_LOG.proc_end(l_log_head,'x_price_tbl('||j||').pricing_status_text',x_price_tbl(j).pricing_status_text);
1604 end LOOP;
1605 END IF;
1606 EXCEPTION
1607 WHEN FND_API.G_EXC_ERROR THEN
1608 --raised expected error: assume raiser already pushed onto the stack
1609 l_exception_msg := FND_MSG_PUB.get(
1610 p_msg_index => FND_MSG_PUB.G_LAST
1611 , p_encoded => 'F'
1612 );
1613 IF g_debug_unexp THEN
1614 PO_LOG.exc(l_log_head,d_pos,l_exception_msg);
1615 END IF;
1616 x_return_status := FND_API.g_ret_sts_error;
1617 -- Push the po_return_msg onto msg list and message stack
1618 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
1619 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_exception_msg);
1620
1621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1622 --raised unexpected error: assume raiser already pushed onto the stack
1623 l_exception_msg := FND_MSG_PUB.get(
1624 p_msg_index => FND_MSG_PUB.G_LAST
1625 , p_encoded => 'F'
1626 );
1627 IF g_debug_unexp THEN
1628 PO_LOG.exc(l_log_head,d_pos,l_exception_msg);
1629 END IF;
1630 x_return_status := FND_API.g_ret_sts_unexp_error;
1631 -- Push the po_return_msg onto msg list and message stack
1632 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
1633 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_exception_msg);
1634
1635 WHEN OTHERS THEN
1636 IF g_debug_unexp THEN
1637 PO_LOG.exc(l_log_head,d_pos);
1638 END IF;
1639 --unexpected error from this procedure: get SQLERRM
1640 po_message_s.sql_error(g_pkg_name, l_api_name, d_pos, SQLCODE, SQLERRM);
1641 l_exception_msg := FND_MESSAGE.get;
1642 IF g_debug_unexp THEN
1643 PO_LOG.exc(l_log_head,d_pos,l_exception_msg);
1644 END IF;
1645 x_return_status := FND_API.g_ret_sts_unexp_error;
1646 -- Push the po_return_msg onto msg list and message stack
1647 FND_MESSAGE.set_name('PO', 'PO_QP_PRICE_API_ERROR');
1648 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_exception_msg);
1649 END get_advanced_price;
1650 -- <FSC R12 END>
1651
1652 END PO_ADVANCED_PRICE_PVT;