[Home] [Help]
PACKAGE BODY: APPS.GMF_GET_TRANSFER_PRICE_PUB
Source
1 PACKAGE BODY GMF_get_transfer_price_PUB AS
2 /* $Header: GMFGXFRB.pls 120.13 2006/11/21 22:26:06 umoogala noship $ */
3
4 G_PACKAGE_NAME VARCHAR2(50) := 'GMF_get_transfer_price_PUB';
5 --===================================================================
6 --
7 -- Following G_ global variables will be used for Advanced Pricing
8 --
9 --===================================================================
10 G_LINE_INDEX_TBL QP_PREQ_GRP.pls_integer_type;
11 G_LINE_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
12 G_PRICING_EFFECTIVE_DATE_TBL QP_PREQ_GRP.DATE_TYPE;
13 G_ACTIVE_DATE_FIRST_TBL QP_PREQ_GRP.DATE_TYPE;
14 G_ACTIVE_DATE_FIRST_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
15 G_ACTIVE_DATE_SECOND_TBL QP_PREQ_GRP.DATE_TYPE;
16 G_ACTIVE_DATE_SECOND_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
17 G_LINE_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
18 G_LINE_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
19 G_REQUEST_TYPE_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
20 G_PRICED_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
21 G_UOM_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
22 G_PRICED_UOM_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
23 G_CURRENCY_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
24 G_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
25 G_PERCENT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
26 G_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
27 G_UPD_ADJUSTED_UNIT_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
28 G_PROCESSED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
29 G_PRICE_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
30 G_LINE_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
31 G_PROCESSING_ORDER_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
32 G_ROUNDING_FACTOR_TBL QP_PREQ_GRP.PLS_INTEGER_TYPE;
33 G_ROUNDING_FLAG_TBL QP_PREQ_GRP.FLAG_TYPE;
34 G_QUALIFIERS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
35 G_PRICING_ATTRS_EXIST_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
36 G_PRICE_LIST_ID_TBL QP_PREQ_GRP.NUMBER_TYPE;
37 G_PL_VALIDATED_FLAG_TBL QP_PREQ_GRP.VARCHAR_TYPE;
38 G_PRICE_REQUEST_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
39 G_USAGE_PRICING_TYPE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
40 G_PRICING_STATUS_CODE_TBL QP_PREQ_GRP.VARCHAR_TYPE;
41 G_PRICING_STATUS_TEXT_TBL QP_PREQ_GRP.VARCHAR_TYPE;
42 G_LINE_CATEGORY_TBL QP_PREQ_GRP.VARCHAR_TYPE;
43
44 G_UNIT_SELLING_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
45 G_UNIT_LIST_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
46 G_UNIT_SELL_PRICE_PER_PQTY_TBL QP_PREQ_GRP.NUMBER_TYPE;
47 G_UNIT_LIST_PRICE_PER_PQTY_TBL QP_PREQ_GRP.NUMBER_TYPE;
48 G_PRICING_QUANTITY_TBL QP_PREQ_GRP.NUMBER_TYPE;
49 G_UNIT_LIST_PERCENT_TBL QP_PREQ_GRP.NUMBER_TYPE;
50 G_UNIT_PERCENT_BASE_PRICE_TBL QP_PREQ_GRP.NUMBER_TYPE;
51 G_UNIT_SELLING_PERCENT_TBL QP_PREQ_GRP.NUMBER_TYPE;
52
53 -- Package body level globals to store input values
54 g_transfer_type VARCHAR2(6); /* INTCOM or INTORG */
55
56 g_from_ou BINARY_INTEGER;
57 g_to_ou BINARY_INTEGER;
58
59 g_from_organization_id NUMBER;
60 g_from_org_currency VARCHAR2(31);
61 g_to_organization_id BINARY_INTEGER;
62
63 g_inventory_item_id NUMBER;
64 g_transaction_qty mtl_material_transactions.transaction_quantity%TYPE;
65 g_transaction_uom mtl_material_transactions.transaction_uom%TYPE;
66 g_primary_uom mtl_system_items_b.primary_uom_code%TYPE;
67
68 g_transaction_id BINARY_INTEGER; -- will be NULL
69 g_order_line_id BINARY_INTEGER; -- will be order_line_id for Internal Orders
70
71 g_global_procurement_flag VARCHAR2(1);
72 g_drop_ship_flag VARCHAR2(1);
73
74 g_xfer_source VARCHAR2(6);
75
76 G_XFER_PRICE_IN_TXN_UOM CONSTANT BINARY_INTEGER := 1;
77 G_XFER_PRICE_IN_PRI_UOM CONSTANT BINARY_INTEGER := 2;
78
79 l_debug BINARY_INTEGER;
80
81 PROCEDURE print_debug(p_message in VARCHAR2) IS
82 BEGIN
83 IF (l_debug = 1) THEN
84 inv_log_util.trace(p_message, '', 4);
85 END IF;
86 END print_debug;
87
88 --===================================================================
89 --
90 -- Start of comments
91 -- API name : get_transfer_price
92 -- Type : Public
93 -- Pre-reqs : None
94 -- Version : Current version 1.0
95 -- Initial version 1.0
96 -- PURPOSE : Get Transfer Price
97 -- Parameters :
98 -- p_transaction_id : Order Line Id of Sales Order
99 -- p_transfer_type : Valid values are INTCOM and INTORG.
100 -- INTCOM: Transfer using Internal Orders across OUs
101 -- with InterCompany Invoicing Enabled.
102 -- (Set when Internal Order Shipment Line is
103 -- being created. Package INV_TXN_MANAGER_GRP)
104 -- INTORG: All other interorg transfers.
105 -- (set during Internal Req creation and during
106 -- Inv. Interorg Transfer)
107 --
108 -- p_transfer_source : INTORG: Inventory InterOrg Xfer
109 -- INTORD: Internal Order
110 -- INTREQ: Internal Req
111 --
112 -- x_transfer_price OUT NOCOPY NUMBER /* In Txn UOM */
113 -- x_currency_code OUT NOCOPY VARCHAR2
114 -- x_transfer_price_priuom OUT NOCOPY NUMBER /* In Item Primary UOM */
115 --
116 -- HISTORY
117 --
118 -- Bug#5461545 Anand Thiyagarajan 17-Aug-2006
119 -- Modified Code to correct the way the transfer price in transaction UOM
120 -- and transfer price in Primary UOM are correctly calculated.
121 -- End of comments
122 --===================================================================
123
124 Procedure get_transfer_price
125 ( p_api_version IN NUMBER
126 , p_init_msg_list IN VARCHAR2
127
128 , p_inventory_item_id IN NUMBER
129 , p_transaction_qty IN NUMBER
130 , p_transaction_uom IN VARCHAR2
131
132 , p_transaction_id IN NUMBER /* Order Line Id for now */
133 , p_global_procurement_flag IN VARCHAR2
134 , p_drop_ship_flag IN VARCHAR2
135
136 , p_from_organization_id IN NUMBER
137 , p_from_ou IN NUMBER /* from OU */
138 , p_to_organization_id IN NUMBER
139 , p_to_ou IN NUMBER /* to OU */
140
141 , p_transfer_type IN VARCHAR2
142 , p_transfer_source IN VARCHAR2 /* INTORG, INTORD, INTREQ */
143
144 , x_return_status OUT NOCOPY VARCHAR2
145 , x_msg_data OUT NOCOPY VARCHAR2
146 , x_msg_count OUT NOCOPY NUMBER
147
148 , x_transfer_price OUT NOCOPY NUMBER /* In Txn UOM */
149 , x_transfer_price_priuom OUT NOCOPY NUMBER /* In Item Primary UOM */
150 , x_currency_code OUT NOCOPY VARCHAR2
151 , x_incr_transfer_price OUT NOCOPY NUMBER
152 , x_incr_currency_code OUT NOCOPY VARCHAR2
153 )
154 IS
155
156 l_transfer_price NUMBER;
157 l_transfer_price_code NUMBER;
158
159 l_item_description VARCHAR2(255);
160 l_uom_rate NUMBER;
161
162 l_use_adv_pricing VARCHAR2(4);
163 l_pricelist_currency VARCHAR2(30);
164 l_functional_currency_code VARCHAR2(30);
165
166 l_return_status VARCHAR2(1);
167 l_user_hook_status NUMBER;
168
169 l_api_name VARCHAR2(80);
170
171 l_from_uom VARCHAR2(40);
172 l_to_uom VARCHAR2(40);
173
174 l_curr_rate NUMBER;
175 l_precision NUMBER;
176 l_ext_precision NUMBER;
177 l_min_unit NUMBER;
178
179 e_user_hook_error EXCEPTION;
180 e_uom_conversion_error EXCEPTION;
181 e_currency_conversion_error EXCEPTION;
182 e_adv_pricing_profile_error EXCEPTION;
183 e_transfer_price_null_error EXCEPTION;
184 e_ignore_error EXCEPTION; -- Bug 5136335
185
186 l_process_enabled_flag_from VARCHAR2(1);
187 l_process_enabled_flag_to VARCHAR2(1);
188 l_exists NUMBER(1);
189
190 BEGIN
191
192 l_api_name := 'GMF_get_transfer_price_PUB.get_transfer_price';
193 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
194 x_return_status := FND_API.G_RET_STS_SUCCESS;
195
196
197 --===================================================================
198 --
199 -- set global variables to use across procedures
200 -- INTCOM: Internal order across OUs with intercompany invoicing.
201 -- INTORG: All other transfers.
202 --
203 --===================================================================
204 g_transfer_type := p_transfer_type; /* INTCOM or INTORG */
205
206 --===================================================================
207 --
208 -- Following parameter will have 3 values: INTORG, INTORD, and REQ
209 -- Will be used to determine which transfer_price routines to call for
210 -- advanced pricing.
211 --
212 -- For INTORG and REQ, THIS package will be used.
213 -- For INTORD we will use mtl_qp_price.get_transfer_price.
214 --
215 --===================================================================
216 g_xfer_source := p_transfer_source; /* INTORG, INTORD, REQ */
217
218 g_from_organization_id := p_from_organization_id;
219 g_from_ou := p_from_ou;
220 g_to_organization_id := p_to_organization_id;
221 g_to_ou := p_to_ou;
222
223 g_inventory_item_id := p_inventory_item_id;
224 g_transaction_qty := p_transaction_qty;
225 g_transaction_uom := p_transaction_uom;
226
227 g_transaction_id := p_transaction_id;
228 g_global_procurement_flag := p_global_procurement_flag;
229 g_drop_ship_flag := p_drop_ship_flag;
230
231 --===================================================================
232 --
233 -- Validating Inputs
234 -- shall we do this for Orgs and OUs???
235 --
236 --===================================================================
237 IF (g_from_ou is null OR g_to_ou IS NULL) OR
238 (g_from_organization_id is NULL OR g_to_organization_id IS NULL)
239 THEN
240 print_debug('Invalid parameters to transfer price API: From/To OUs or From/To Orgs should be passed');
241 RAISE FND_API.G_EXC_ERROR;
242 END IF;
243
244 print_debug(' ' || l_api_name || ': Get price for item: ' || g_inventory_item_id ||
245 ' org: ' || g_from_organization_id || ' Dest. Org.: ' || g_to_organization_id ||
246 ' transaction qty: ' || g_transaction_qty || ' '|| g_transaction_uom);
247
248 --===================================================================
249 --
250 -- Get items primary uom
251 --
252 --===================================================================
253 SELECT primary_uom_code
254 INTO g_primary_uom
255 FROM mtl_system_items_b
256 WHERE inventory_item_id = g_inventory_item_id
257 AND organization_id = g_from_organization_id;
258
259 --===================================================================
260 --
261 -- Get sending org's base currency
262 --
263 -- jboppana bug 4906497:
264 -- Fixed following sql for performance issue
265 --===================================================================
266 SELECT currency_code
267 INTO g_from_org_currency
268 FROM hr_organization_information org, gl_ledgers gll
269 WHERE org.organization_id = g_from_organization_id
270 AND gll.ledger_id = org.org_information1
271 AND org.org_information_context = 'Accounting Information';
272
273 print_debug(l_api_name || ': p_transfer_type: ' || p_transfer_type);
274
275
276 --===================================================================
277 --
278 -- For inter-company xfers, call existing INV transfer price API
279 --
280 --===================================================================
281 IF (p_transfer_type = 'INTCOM')
282 THEN
283 INV_TRANSACTION_FLOW_PUB.get_transfer_price(
284 x_return_status => x_return_status
285 , x_msg_data => x_msg_data
286 , x_msg_count => x_msg_count
287
288 , x_transfer_price => l_transfer_price
289 , x_currency_code => l_pricelist_currency
290 , x_incr_transfer_price => x_incr_transfer_price
291 , x_incr_currency_code => x_incr_currency_code
292
293 , p_api_version => 1.0
294 , p_init_msg_list => fnd_api.g_false
295
296 , p_from_org_id => p_from_ou
300 , p_transaction_id => NULL
297 , p_to_org_id => p_to_ou
298 , p_transaction_uom => p_transaction_uom
299 , p_inventory_item_id => p_inventory_item_id
301 , p_global_procurement_flag => 'N'
302 , p_drop_ship_flag => p_drop_ship_flag
303 , p_from_organization_id => p_from_organization_id
304 , p_order_line_id => p_transaction_id
305 -- , p_process_discrete_xfer_flag => 'Y' Bug 5171637: replaced with above line.
306 );
307
308 print_debug(l_api_name || ': return status from INV_TRANSACTION_FLOW_PUB.get_transfer_price: ' || x_return_status);
309 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
310 THEN
311
312 --
313 -- Bug 5136335
314 -- For Discrete Intercompany transfers, if no intercompany setup
315 -- is done, then ignore the error. Look at the bug for more details.
316 -- If intercompany setup is done, then raise error.
317 --
318
319 --
320 -- Get process mfg org flag for from and to orgs
321 --
322 SELECT mp_from.process_enabled_flag, mp_to.process_enabled_flag
323 INTO l_process_enabled_flag_from, l_process_enabled_flag_to
324 FROM mtl_parameters mp_from, mtl_parameters mp_to
325 WHERE mp_from.organization_id = g_from_organization_id
326 AND mp_to.organization_id = g_to_organization_id;
327
328 IF l_process_enabled_flag_from = 'N'
329 AND l_process_enabled_flag_to = 'N'
330 THEN
331
332 BEGIN
333 --
334 -- Bug 5675254: following query was using orgnIds instead of OU Ids.
335 -- Also added flow_type to the query
336 --
337 SELECT 1
338 INTO l_exists
339 FROM mtl_intercompany_parameters
340 WHERE sell_organization_id = g_to_ou
341 AND ship_organization_id = g_from_ou
342 AND flow_type = 1
343 ;
344 print_debug('Discrete Xfer. IC Relations do exists between from OU and To OU. Error is being raised and txn will not be shipconfirmed.');
345 EXCEPTION
346 WHEN no_data_found
347 THEN
348 print_debug('Discrete Xfer. No IC Relations exists between from OU and To OU. No error is being raised.');
349 x_transfer_price := NULL;
350 x_transfer_price_priuom := NULL;
351 RAISE e_ignore_error;
352 END;
353
354 END IF;
355 --
356 -- End -- Bug 5136335
357 --
358
359 x_msg_data := FND_MESSAGE.get;
360 IF (l_debug = 1) THEN
361 print_debug('INV_TRANSACTION_FLOW_PUB.get_transfer_price: Error = '|| x_msg_data );
362 END IF;
363 RAISE FND_API.G_EXC_ERROR;
364
365 ELSE
366
367 IF l_transfer_price IS NULL
368 THEN
369 print_debug('INV_TRANSACTION_FLOW_PUB.get_transfer_price: Transfer Price is NULL: '|| x_msg_data );
370 RAISE e_transfer_price_null_error;
371 END IF;
372
373 END IF;
374
375 l_transfer_price_code := G_XFER_PRICE_IN_TXN_UOM; -- since above api returns in Txn uom
376 print_debug(l_api_name || ': Transfer Price: ' || l_transfer_price ||
377 ' PriceCode: ' || l_transfer_price_code);
378
379 ELSE
380
381 --===================================================================
382 -- Call user hook - always
383 --===================================================================
384 print_debug(l_api_name || ': calling user hook');
385 GMF_get_xfer_price_hook_PUB.Get_xfer_price_user_hook (
386 p_api_version => 1.0
387 , p_init_msg_list => fnd_api.g_false
388
389 , p_transaction_uom => p_transaction_uom
390 , p_inventory_item_id => p_inventory_item_id
391 , p_transaction_id => p_transaction_id
392
393 , p_from_organization_id => p_from_organization_id
394 , p_to_organization_id => p_to_organization_id
395
396 , p_from_ou => p_from_ou
397 , p_to_ou => p_to_ou
398
399 , x_return_status => l_user_hook_status
400 , x_msg_data => x_msg_data
401 , x_msg_count => x_msg_count
402
403 , x_transfer_price => x_transfer_price
404 , x_transfer_price_priuom=> x_transfer_price_priuom /* In Item Primary UOM */
405 , x_currency_code => x_currency_code
406 );
407
408 --===================================================================
409 --
410 -- x_return_status = -1 is default i.e., user hook
411 -- not implemented.
412 -- Any other value means, user hook implemented and
413 -- we need to honor whatever the outcome
414 --
415 --===================================================================
416 IF l_user_hook_status = -2 -- User Hook error
417 THEN
418 print_debug(l_api_name || ': user hook returned error: ' || x_msg_data);
419 RAISE e_user_hook_error;
420
421 ELSIF l_user_hook_status = 0
422 THEN
423 -- Got the transfer price. return from here.
424 print_debug(l_api_name || ': user hook returned transfer price in txn uom: ' || x_transfer_price ||
425 ' and in item primary uom: ' || x_transfer_price_priuom);
426 print_debug(l_api_name || ': End');
427 RETURN;
428 END IF;
429 print_debug(l_api_name || ': user hook NOT implemented');
430
431
435 -- Get the new profile to see whether we can use Adv. Pricing or not.
432 --===================================================================
433 -- If we are here means user hook is not implemented.
434 --
436 -- This is the new profile added for inter-org transfer across
437 -- process/discrete xfers only.
438 --===================================================================
439
440 l_use_adv_pricing := fnd_profile.value('INV_USE_QP_FOR_INTERORG');
441
442 print_debug(l_api_name || ': Adv. Pricing profile: ' || l_use_adv_pricing);
443
444
445 IF (l_use_adv_pricing IS NULL OR l_use_adv_pricing = 2)
446 -- Profile set to No
447 THEN
448 --===================================================================
449 --
450 -- Basic pricing. So, get pricelist from interorg parameters
451 --
452 --===================================================================
453
454 print_debug(l_api_name || ': Calling Basic Pricing...');
455
456 GMF_get_transfer_price_PUB.get_xfer_price_basic (
457 x_transfer_price => l_transfer_price
458 , x_transfer_price_code => l_transfer_price_code
459 , x_pricelist_currency => l_pricelist_currency
460 , x_return_status => x_return_status
461 , x_msg_data => x_msg_data
462 , x_msg_count => x_msg_count
463 );
464
465 print_debug(l_api_name || ': Basic Pricing status: ' || x_return_status);
466 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
467 THEN
468
469 x_msg_data := FND_MESSAGE.get;
470 IF (l_debug = 1) THEN
471 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_basic: Error = '|| x_msg_data );
472 END IF;
473 RAISE FND_API.G_EXC_ERROR;
474
475 ELSE
476
477 IF l_transfer_price IS NULL
478 THEN
479 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_basic: Transfer Price is NULL: '|| x_msg_data );
480 RAISE e_transfer_price_null_error;
481 END IF;
482
483 END IF;
484
485 -- l_pricelist_currency := g_from_org_currency;
486 print_debug(l_api_name || ': Transfer Price: ' || l_transfer_price ||
487 ' PriceCode: ' || l_transfer_price_code);
488
489
490 ELSIF l_use_adv_pricing = 1
491 THEN
492 --===================================================================
493 --
494 -- Advance pricing
495 --
496 --===================================================================
497
498 print_debug(l_api_name || ': Calling Advanced Pricing...');
499 GMF_get_transfer_price_PUB.get_xfer_price_qp (
503 , x_return_status => x_return_status
500 x_transfer_price => l_transfer_price
501 , x_currency_code => l_pricelist_currency
502 , x_transfer_price_code => l_transfer_price_code
504 , x_msg_data => x_msg_data
505 , x_msg_count => x_msg_count
506 );
507
508 print_debug(l_api_name || ': After Advanced Pricing. status: ' || x_return_status);
509
510 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
511 THEN
512
513 IF (l_debug = 1) THEN
514 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Error = '|| x_msg_data );
515 END IF;
516 RAISE FND_API.G_EXC_ERROR;
517
518 ELSE
519
520 IF l_transfer_price IS NULL
521 THEN
522 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Transfer Price is NULL: '|| x_msg_data );
523 RAISE e_transfer_price_null_error;
524 END IF;
525
526 END IF;
527
528 -- l_pricelist_currency := g_from_org_currency;
529 print_debug(l_api_name || ': Transfer Price: ' || l_transfer_price ||
530 ' PriceCode: ' || l_transfer_price_code);
531
532 END IF;
533
534 END IF; -- IF (p_transfer_type = 'INTCOM')
535
536
537 --===================================================================
538 --
539 -- l_transfer_price_code = 2: xfer price in item's primary uom
540 -- Convert to transaction_uom
541 --
542 --===================================================================
543 IF (NVL(l_transfer_price,0) > 0
544 AND g_primary_uom = g_transaction_uom)
545 THEN
546
547 x_transfer_price := l_transfer_price;
548 x_transfer_price_priuom := l_transfer_price;
549
550 ELSIF (NVL(l_transfer_price,0) > 0
551 AND g_primary_uom <> g_transaction_uom)
552 THEN
553
554 print_debug(l_api_name || ': Converting Transfer Price from Primary UOM: ' || g_primary_uom ||
555 ' to Transaction UOM: ' || g_transaction_uom);
556
557 IF l_transfer_price_code = G_XFER_PRICE_IN_PRI_UOM
558 THEN
559 l_from_uom := g_primary_uom;
560 l_to_uom := g_transaction_uom;
561 ELSE
562 l_from_uom := g_transaction_uom;
563 l_to_uom := g_primary_uom;
564 END IF;
565
566 -- do uom conversion
567 INV_CONVERT.inv_um_conversion(
568 from_unit => l_from_uom
569 , to_unit => l_to_uom
570 , item_id => g_inventory_item_id
571 , uom_rate => l_uom_rate
572 );
573
574 IF (l_uom_rate = -99999) THEN
575 --
576 -- UOM conversion error
577 --
578 RAISE e_uom_conversion_error;
579
580 END IF;
581
582 IF l_transfer_price_code = G_XFER_PRICE_IN_PRI_UOM
583 THEN
584 --- x_transfer_price := l_uom_rate * l_transfer_price; /* ANTHIYAG Bug#5461545 17-Aug-2006 */
585 x_transfer_price := l_transfer_price / nvl(l_uom_rate,1); /* ANTHIYAG Bug#5461545 17-Aug-2006 */
586
587 x_transfer_price_priuom := l_transfer_price;
588 ELSE
589 x_transfer_price := l_transfer_price;
590 --- x_transfer_price_priuom := l_uom_rate * l_transfer_price; /* ANTHIYAG Bug#5461545 17-Aug-2006 */
591 x_transfer_price_priuom := l_transfer_price / nvl(l_uom_rate,1); /* ANTHIYAG Bug#5461545 17-Aug-2006 */
592 END IF;
593 -- l_transfer_price := l_uom_rate * l_transfer_price;
594
595 print_debug(l_api_name || ': After UOM conversion Transfer Price in ' ||
596 'Txn/Pri UOM is: ' || x_transfer_price ||'/'|| x_transfer_price_priuom);
597
598 END IF;
599
600 /* For InterCompany xfer, INV API has already converted the transfer price
601 * to base currency.
602 */
603 IF (p_transfer_type <> 'INTCOM')
604 AND (NVL(l_transfer_price,0) > 0)
605 THEN
606 print_debug(l_api_name || ': Now doing currency conversion from priceList Currency: ' ||
607 l_pricelist_currency || ' to functional currency, if necessary');
608
609 l_curr_rate := INV_TRANSACTION_FLOW_PUB.convert_currency (
610 p_org_id => g_from_ou
611 , p_transfer_price => 1
612 , p_currency_code => l_pricelist_currency
613 , p_transaction_date => sysdate
614 , x_functional_currency_code => l_functional_currency_code
615 , x_return_status => x_return_status
616 , x_msg_data => x_msg_data
617 , x_msg_count => x_msg_count
618 );
619
620 fnd_currency.get_info (currency_code => l_functional_currency_code,
621 precision => l_precision,
622 ext_precision => l_ext_precision,
623 min_acct_unit => l_min_unit);
624
625 x_transfer_price := round(l_curr_rate * x_transfer_price, l_ext_precision);
626 x_transfer_price_priuom := round(l_curr_rate * x_transfer_price_priuom, l_ext_precision);
627
628 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS )
629 THEN
630 --
631 -- currency conversion error
632 --
633 RAISE e_currency_conversion_error;
634 END IF;
635 END IF;
636
637 print_debug(l_api_name || ': Final Transfer Price in Txn/Pri UOM: ' || x_transfer_price ||'/'|| x_transfer_price_priuom);
641 -- x_transfer_price := l_transfer_price;
638 print_debug(l_api_name || ': Functional Currency Price (may be null if currConv is not called): ' || l_functional_currency_code);
639 print_debug(l_api_name || ': all done! exiting...');
640
642 x_currency_code := l_functional_currency_code;
643
644
645 EXCEPTION
646 WHEN e_uom_conversion_error
647 THEN
648 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_UOM_CONV');
649 FND_MESSAGE.SET_TOKEN('VALUE1', p_transaction_uom);
650 FND_MESSAGE.SET_TOKEN('VALUE2', g_primary_uom);
651 FND_MSG_PUB.ADD;
652
653 x_return_status := FND_API.G_RET_STS_ERROR;
654
655 WHEN e_currency_conversion_error
656 THEN
657 print_debug('GMF_get_transfer_price_PUB.get_transfer_price: currency conversion error');
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
660 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
661
662
663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
664 THEN
665 print_debug('Exc_Unexpected_Error in GMF_get_transfer_price_PUB.get_transfer_price');
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
667 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
668 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
669
670 WHEN FND_API.G_EXC_ERROR
671 THEN
672 print_debug('EXC_ERROR in GMF_get_transfer_price_PUB.get_transfer_price: ' || x_msg_data);
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 IF x_msg_data IS NULL
675 THEN
676 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
677 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
678 END IF;
679
680 WHEN e_user_hook_error
681 THEN
682 print_debug('user hook returned Error: ' || x_msg_data);
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 IF x_msg_data IS NULL
685 THEN
686 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
687 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
688 END IF;
689
690
691 WHEN e_transfer_price_null_error
692 THEN
693 print_debug('transfer price is null: ' || x_msg_data);
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 IF x_msg_data IS NULL
696 THEN
697 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
698 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
699 END IF;
700
701 WHEN e_ignore_error
702 THEN
703 x_return_status := FND_API.G_RET_STS_SUCCESS;
704
705
706 WHEN OTHERS THEN
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
708 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
709 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
710
711 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
712 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, l_api_name);
713 end if;
714 print_debug('When Other in GMF_get_transfer_price_PUB.get_transfer_price (sqlerrm): ' || sqlerrm);
715 print_debug('When Other in GMF_get_transfer_price_PUB.get_transfer_price (backtrace): ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
716
717 END get_transfer_price;
718
719 --
720 -- Start of comments
721 -- API name : get_xfer_price_basic
722 -- Type : Public
723 -- Pre-reqs : None
724 -- Parameters :
725 -- Version : Current version 1.0
726 -- Initial version 1.0
727 -- PURPOSE: Pseudo code
728 -- Begin
729 -- if (transfer type = 'INTCOM' then
730 -- call transfer_price api for inter company
731 -- i.e., INV_TRANSACTION_FLOW_PUB
732 -- return;
733 -- end if;
734 --
735 -- call the user hook
736 -- -- either to get prior period cost as transfer price (supplied)
737 -- -- or transfer price from user code, by commenting above code
738 --
739 -- if hook is successful then
740 -- return transfer price
741 -- end if;
742 --
743 -- get profile value for advance pricing for inter org transfers
744 -- -- profile: INV_USE_QP_FOR_INTERORG
745 --
746 -- if (advance pricing enabled ) then
747 -- call price request engine call
748 -- return transfer_price;
749 -- end if;
750 --
751 -- get price from static price list
752 -- -- defined in shipping networks for this item.
753 --
754 -- return transfer price;
755 --
756 -- end;
757 -- End of comments
758
759 Procedure get_xfer_price_basic (
760 x_transfer_price OUT NOCOPY NUMBER
761 , x_transfer_price_code OUT NOCOPY NUMBER
762 , x_pricelist_currency OUT NOCOPY VARCHAR2
763 , x_return_status OUT NOCOPY VARCHAR2
764 , x_msg_count OUT NOCOPY NUMBER
765 , x_msg_data OUT NOCOPY VARCHAR2
766 )
767 IS
768
769 l_transfer_price NUMBER;
770 l_transfer_price_code NUMBER;
771
772 l_pricelist_id NUMBER;
773 l_pricelist_name VARCHAR2(255);
774 l_pricelist_currency VARCHAR2(30);
775
776 l_item_description VARCHAR2(2000);
777 l_primary_uom VARCHAR2(4);
778
779 l_uom_rate NUMBER;
780
784 e_item_not_on_pricelist exception;
781 l_api_name VARCHAR2(50);
782
783 e_price_list_not_found exception;
785
786 BEGIN
787
788 l_api_name := 'GMF_get_transfer_price_PUB.get_xfer_price_basic';
789
790 print_debug(' ' || l_api_name || ': Basic pricing. Get price for item: ' || g_inventory_item_id ||
791 ' org: ' || g_from_organization_id);
792 --
793 -- get price list id
794 --
795 BEGIN
796 SELECT NVL(mip.pricelist_id,-1)
797 INTO l_pricelist_Id
798 FROM mtl_interorg_parameters mip
799 WHERE mip.from_organization_id = g_from_organization_id
800 AND mip.to_organization_id = g_to_organization_id;
801
802 IF ( l_pricelist_id = -1)
803 THEN
804 RAISE e_price_list_not_found;
805 END IF;
806
807 EXCEPTION
808 WHEN NO_DATA_FOUND
809 THEN
810 RAISE e_price_list_not_found;
811 END;
812
813 print_debug(' ' || l_api_name || ': Price List Id is ' || l_pricelist_id);
814
815 BEGIN
816 --
817 -- Get static price list in transaction uom
818 --
819 print_debug(' ' || l_api_name || ': Getting price using transaction uom: ' || g_transaction_uom);
820
821 l_transfer_price_code := G_XFER_PRICE_IN_TXN_UOM; -- (1)
822
823 SELECT spll.operand, substr(spl.currency_code, 1, 15)
824 INTO l_transfer_price, l_pricelist_currency
825 FROM qp_list_headers_b spl, qp_list_lines spll, qp_pricing_attributes qpa
826 WHERE spl.list_header_id = l_pricelist_id
827 AND spll.list_header_id = spl.list_header_id
828 AND spll.list_line_id = qpa.list_line_id
829 AND qpa.product_attribute_context = 'ITEM'
830 AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
831 AND qpa.product_attr_value = to_Char(g_inventory_item_id)
832 AND qpa.product_uom_code = g_transaction_uom
833 AND sysdate BETWEEN NVL(spll.start_date_active, (sysdate-1)) AND
834 NVL(spll.end_date_active+0.99999, (sysdate+1))
835 AND rownum = 1
836 ;
837
838 print_debug(' ' || l_api_name || ': List Price: ' || l_transfer_price);
839
840 EXCEPTION
841 WHEN NO_DATA_FOUND
842 THEN
843 --
844 -- Get static price list in primary uom
845 --
846 BEGIN
847 print_debug(' ' || l_api_name || ': Getting price using primary uom: ' || g_primary_uom);
848
849 l_transfer_price_code := G_XFER_PRICE_IN_PRI_UOM; -- (2)
850
851 SELECT spll.operand, substr(spl.currency_code, 1, 15), msi.primary_uom_code
852 INTO l_transfer_price, l_pricelist_currency, l_primary_uom
853 FROM qp_list_headers_b spl, qp_list_lines spll,
854 qp_pricing_attributes qpa, mtl_system_items_b msi
855 WHERE msi.organization_id = g_from_organization_id
856 AND msi.inventory_item_id = g_inventory_item_id
857 AND spl.list_header_id = l_pricelist_id
858 AND spll.list_header_id = spl.list_header_id
859 AND qpa.list_header_id = spl.list_header_id
860 AND spll.list_line_id = qpa.list_line_id
861 AND qpa.product_attribute_context = 'ITEM'
862 AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
863 AND qpa.product_attr_value = to_char(msi.inventory_item_id)
864 AND qpa.product_uom_code = msi.primary_uom_code
865 AND sysdate BETWEEN NVL(spll.start_date_active, (sysdate-1)) AND
866 NVL(spll.end_date_active + 0.99999, (sysdate+1))
867 AND rownum = 1
868 ;
869
870 print_debug(' ' || l_api_name || ': List Price: ' || l_transfer_price);
871
872 EXCEPTION
873 WHEN NO_DATA_FOUND
874 THEN
875 --
876 -- Item not on pricelist
877 --
878 print_debug(' ' || l_api_name || ': item not on price list');
879 RAISE e_item_not_on_pricelist;
880 END;
881 END;
882
883
884 x_transfer_price := l_transfer_price;
885 x_transfer_price_code := l_transfer_price_code;
886 x_pricelist_currency := l_pricelist_currency;
887 x_return_status := FND_API.G_RET_STS_SUCCESS;
888
889 print_debug(' ' || l_api_name || ': PriceList Currency: ' || l_pricelist_currency);
890 print_debug(' ' || l_api_name || ': exiting...');
891
892 EXCEPTION
893 WHEN e_price_list_not_found
894 THEN
895 print_debug(' ' || l_api_name || ': PriceList not found!');
896 -- l_transfer_price := -99;
897 x_return_status := FND_API.G_RET_STS_ERROR;
898
899 FND_MESSAGE.SET_NAME('GMF', 'IC-Price List Not Found');
900 FND_MESSAGE.SET_TOKEN('FROM_ORG', g_from_organization_id);
901 FND_MESSAGE.SET_TOKEN('TO_ORG', g_to_organization_id);
902 FND_MSG_PUB.ADD;
903 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
904 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
905 RAISE FND_API.G_EXC_ERROR;
906
907 WHEN e_item_not_on_pricelist
908 THEN
909 print_debug(' ' || l_api_name || ': Item is not on price list!');
910 -- l_transfer_price := -99;
911 x_return_status := FND_API.G_RET_STS_ERROR;
912
913 SELECT concatenated_segments
914 INTO l_item_description
915 FROM mtl_system_items_kfv
916 WHERE organization_id = g_from_organization_id
920 INTO l_pricelist_name
917 AND inventory_item_id = g_inventory_item_id;
918
919 SELECT name
921 FROM QP_LIST_HEADERS
922 WHERE list_header_id = l_pricelist_id;
923
924 FND_MESSAGE.SET_NAME('QP', 'QP_PRC_NO_LIST_PRICE');
925 FND_MESSAGE.SET_TOKEN('ITEM', l_item_description);
926 FND_MESSAGE.SET_TOKEN('UNIT', g_primary_uom);
927 FND_MESSAGE.SET_TOKEN('PRICE_LIST', l_pricelist_name);
928 FND_MSG_PUB.ADD;
929 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
930 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
931 RAISE FND_API.G_EXC_ERROR;
932
933 WHEN OTHERS then
934 print_debug(' ' || l_api_name || ': in When Others (sqlerrm): ' || substr(sqlerrm, 1, 200));
935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
936 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
937 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
938 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
939 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, l_api_name);
940 end if;
941 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942
943 /*
944 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
946 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
947 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
948
949 WHEN FND_API.G_EXC_ERROR THEN
950 x_return_status := FND_API.G_RET_STS_ERROR;
951 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
952 print_debug('In EXC_ERROR ' || l_progress, 'Get_Transfer_Price');
953 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
954 */
955 END get_xfer_price_basic;
956
957 --
958 -- Start of comments
959 -- API name : get_xfer_price_qp
960 -- Type : Public
961 -- Pre-reqs : None
962 -- Parameters :
963 -- Version : Current version 1.0
964 -- Initial version 1.0
965 -- PURPOSE: Pseudo code
966 -- Begin
967 -- if (transfer type = 'INTCOM' then
968 -- call transfer_price api for inter company
969 -- i.e., INV_TRANSACTION_FLOW_PUB
970 -- return;
971 -- end if;
972 --
973 -- call the user hook
974 -- -- either to get prior period cost as transfer price (supplied)
975 -- -- or transfer price from user code, by commenting above code
976 --
977 -- if hook is successful then
978 -- return transfer price
979 -- end if;
980 --
981 -- get profile value for advance pricing for inter org transfers
982 -- -- profile: INV_USE_QP_FOR_INTERORG
983 --
984 -- if (advance pricing enabled ) then
985 -- call price request engine call
986 -- return transfer_price;
987 -- end if;
988 --
989 -- get price from static price list
990 -- -- defined in shipping networks for this item.
991 --
992 -- return transfer price;
993 --
994 -- end;
995 -- End of comments
996
997 Procedure get_xfer_price_qp (
998 x_transfer_price OUT NOCOPY NUMBER
999 , x_currency_code OUT NOCOPY VARCHAR2
1000 , x_transfer_price_code OUT NOCOPY NUMBER
1001 , x_return_status OUT NOCOPY VARCHAR2
1002 , x_msg_data OUT NOCOPY VARCHAR2
1003 , x_msg_count OUT NOCOPY NUMBER
1004 )
1005 IS
1006
1007 -- INTORD for Internal Orders
1008 -- INTORG for Inventory InterOrg Transfers
1009
1010 l_header_id NUMBER;
1011 l_line_id NUMBER;
1012 l_inventory_item_id NUMBER;
1013 l_organization_id NUMBER;
1014 l_transaction_uom VARCHAR2(3);
1015 l_Control_Rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
1016 l_pricing_event VARCHAR2(30); -- DEFAULT 'ICBATCH';
1017 l_request_type_code VARCHAR2(30); -- DEFAULT 'INTORG'; -- 'INVXFR';
1018 l_line_index NUMBER := 0;
1019 l_return_status_Text VARCHAR2(2000);
1020 l_version VARCHAR2(240);
1021 l_dir VARCHAR2(2000);
1022 l_tfrPrice NUMBER;
1023 l_uom_rate NUMBER;
1024 l_doc_type VARCHAR2(4); /* OPM Bug 2865040 */
1025
1026 l_order_line_id NUMBER;
1027 l_base_item_id NUMBER;
1028 l_transaction_source_type_id NUMBER;
1029 l_transaction_action_id NUMBER;
1030
1031 begin
1032
1033 IF (l_debug = 1) THEN
1034 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: xfr source: ' || g_xfer_source);
1035 END IF;
1036
1037 -- QP: Pricing Transaction Entity => Inter Organization Transfers for Internal Orders
1038 -- QP: Source System Code => Oracle Inventory
1039 fnd_profile.put('QP_PRICING_TRANSACTION_ENTITY', 'INTCOM');
1040 fnd_profile.put('QP_SOURCE_SYSTEM_CODE', 'INV');
1041
1042 l_request_type_code := 'INTORG';
1043 l_pricing_event := 'ICBATCH';
1044
1045 --
1046 -- First doing for Internal Orders
1047 -- For internal Order we will be reusing INV team API.
1048 -- For that to happen we need to send Request Type code.
1049 --
1050 IF g_xfer_source = 'INTORD'
1051 THEN
1052
1053 -- l_request_type_code := 'INVIOT';
1054
1058 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Calling MTL_QP_PRICE.get_transfer_price' ||
1055 INV_IC_ORDER_PUB.G_LINE.from_organization_id := g_from_organization_id;
1056 INV_IC_ORDER_PUB.G_LINE.to_organization_id := g_to_organization_id;
1057
1059 ' with From Orgn: ' || g_from_organization_id ||
1060 ' To Orgn: ' || g_to_organization_id ||
1061 ' for order line id: ' || g_transaction_id ||
1062 ' itemID: ' || g_inventory_item_id);
1063
1064 x_transfer_price := MTL_QP_PRICE.get_transfer_price(
1065 p_transaction_id => NULL,
1066 p_sell_ou_id => g_from_ou,
1067 p_ship_ou_id => g_to_ou,
1068 p_order_line_id => g_transaction_id,
1069 p_inventory_item_id => g_inventory_item_id,
1070 p_organization_id => g_from_organization_id,
1071 p_uom_code => g_transaction_uom,
1072 p_cto_item_flag => 'N',
1073 p_incr_code => 1,
1074 p_incrCurrency => g_from_org_currency,
1075 p_request_type_code => l_request_type_code,
1076 p_pricing_event => l_pricing_event,
1077 x_currency_code => x_currency_code,
1078 x_tfrPriceCode => x_transfer_price_code,
1079 x_return_status => x_return_status,
1080 x_msg_count => x_msg_count,
1081 x_msg_data => x_msg_data
1082 );
1083
1084 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1085 IF (l_debug = 1) THEN
1086 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: MTL_QP_PRICE.get_transfer_price error ' );
1087 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Error ='|| x_msg_data );
1088 END IF;
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF;
1091
1092 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: ' ||
1093 'Transfer Price from MTL_QP_PRICE.get_transfer_price: ' || x_transfer_price);
1094
1095 ELSIF g_xfer_source in ('INTORG', 'INTREQ')
1096 THEN
1097 --
1098 -- set QP profile to force Inter-Org context.
1099 -- For intercompany, this profile will be honored, but
1100 -- not for inter-org transfers
1101 --
1102
1103 -- l_request_type_code := 'INVXFR';
1104
1105 IF (l_debug = 1) THEN
1106 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp:: Selecting Line Identifier...');
1107 END IF;
1108
1109 -- l_organization_id := g_from_organization_id;
1110 l_transaction_uom := g_transaction_uom;
1111 -- l_inventory_item_id := g_inventory_item_id;
1112 -- l_line_id := NULL;
1113 -- l_transaction_source_type_id := 13;
1114 -- l_transaction_action_id := 21;
1115
1116 /*
1117 G_Hdr_Initialize;
1118
1119 -- Header
1120 copy_Header_to_request( p_header_rec => INV_IC_ORDER_PUB.g_txn_hdr
1121 , p_Request_Type_Code => l_request_type_code
1122 , px_line_index => l_line_index );
1123
1124 IF (l_debug = 1) THEN
1125 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Build Context for header...');
1126 END IF;
1127
1128 QP_Attr_Mapping_PUB.Build_Contexts(
1129 p_request_type_code => l_request_type_code
1130 , p_pricing_type_code => 'H'
1131 , p_line_index => INV_IC_ORDER_PUB.g_txn_hdr.dummy );
1132 */
1133
1134 IF (l_debug = 1) THEN
1135 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: calling G_Line_Initialize...');
1136 END IF;
1137
1138 G_Line_Initialize;
1139 QP_price_request_context.set_request_id;
1140
1141 IF (l_debug = 1) THEN
1142 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: calling copy_Line_to_request...');
1143 END IF;
1144
1145 --
1146 -- Copy line to request
1147 --
1148 copy_Line_to_request( p_Line_rec => INV_IC_ORDER_PUB.g_line
1149 , p_pricing_events => l_pricing_event
1150 , p_request_type_code => l_request_type_code
1151 , px_line_index => l_line_index );
1152
1153
1154 --
1155 -- Build Context
1156 --
1157 IF (l_debug = 1) THEN
1158 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Build Context for line...');
1159 END IF;
1160
1161 QP_Attr_Mapping_PUB.Build_Contexts(
1162 p_request_type_code => l_request_type_code
1163 , p_pricing_type_code => 'L'
1164 , p_line_index => g_inventory_item_id + g_from_organization_id + g_to_organization_id);
1165
1166
1167 --
1168 -- Populate temp table
1169 --
1170 IF l_line_index > 0 THEN
1171 IF (l_debug = 1) THEN
1172 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Populating Lines temp table...');
1173 END IF;
1174 Populate_Temp_Table(x_return_status);
1175 END IF;
1176
1177 --
1178 -- Initializing control record
1179 --
1180 IF (l_debug = 1) THEN
1181 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Initializing control record...');
1182 END IF;
1183
1184 l_control_rec.pricing_event := l_pricing_event;
1188 l_control_rec.rounding_flag := 'Y';
1185 l_control_rec.calculate_flag := qp_preq_grp.G_SEARCH_N_CALCULATE;
1186 l_control_rec.temp_table_insert_flag := 'N';
1187 l_control_rec.request_type_code := l_request_type_code;
1189 l_control_rec.USE_MULTI_CURRENCY := 'Y';
1190
1191 --
1192 -- All set. Call Adv. Pricing Engine.
1193 --
1194 IF (l_debug = 1) THEN
1195 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Calling QP:Price Request routine ...');
1196 END IF;
1197
1198 QP_PREQ_PUB.PRICE_REQUEST( p_control_rec => l_control_rec
1199 , x_return_status => x_return_status
1200 , x_return_status_Text => l_return_status_Text);
1201
1202 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1203 IF (l_debug = 1) THEN
1204 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: QP_PREQ_PUB.PRICE_REQUEST error ' );
1205 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: x_return_status_text='|| l_return_status_text );
1206 END IF;
1207 fnd_message.set_name('INV', 'INV_UNHANDLED_ERR');
1208 fnd_message.set_token('ENTITY1', 'QP_PREQ_PUB.PRICE_REQUEST');
1209 fnd_message.set_token('ENTITY2', substr(l_return_status_text, 1, 150) );
1210 RAISE FND_API.G_EXC_ERROR;
1211 END IF;
1212
1213 IF (l_debug = 1) THEN
1214 Print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: Populating QP results ...');
1215 END IF;
1216
1217 --
1218 -- Populating results
1219 --
1220 Populate_Results (l_line_index, x_return_status, x_msg_data);
1221
1222 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1223 THEN
1224 RAISE FND_API.G_EXC_ERROR;
1225 END IF;
1226
1227 x_currency_code := G_CURRENCY_CODE_TBL(l_line_index);
1228 x_transfer_price := G_UNIT_SELLING_PRICE_TBL(l_line_index);
1229
1230 IF G_PRICED_UOM_CODE_TBL(l_line_index) = l_transaction_uom
1231 THEN
1232 x_transfer_price_code := 1;
1233 ELSIF G_PRICED_UOM_CODE_TBL(l_line_index) = g_primary_uom
1234 THEN
1235 x_transfer_price_code := 2;
1236 ELSE
1237 x_transfer_price_code := 1;
1238 END IF;
1239
1240
1241 IF (l_debug = 1)
1242 THEN
1243 Print_debug('Transfer Price='|| to_char(x_transfer_price));
1244 Print_debug('UOM='|| G_PRICED_UOM_CODE_TBL(l_line_index));
1245 END IF;
1246
1247 END IF; -- IF g_xfer_source = 'INTORG'
1248
1249
1250 EXCEPTION
1251 WHEN NO_DATA_FOUND
1252 THEN
1253 IF (l_debug = 1)
1254 THEN
1255 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: EXCEP NO_DATA_FOUND ' );
1256 END IF;
1257 fnd_message.set_name('INV', 'INV_NO_DATA_EXISTS');
1258
1259 WHEN FND_API.G_EXC_ERROR
1260 THEN
1261 IF (l_debug = 1)
1262 THEN
1263 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: EXCEP G_EXC_ERROR ' );
1264 END IF;
1265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1266 THEN
1267 IF (l_debug = 1)
1268 THEN
1269 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: EXCEP G_EXC_UNEXPECTED_ERROR ' );
1270 END IF;
1271 WHEN OTHERS
1272 THEN
1273 IF (l_debug = 1)
1274 THEN
1275 print_debug('GMF_get_transfer_price_PUB.get_xfer_price_qp: EXCEP UNEXP OTHERS - ' || sqlerrm);
1276 END IF;
1277 END get_xfer_price_qp;
1278
1279 --
1280 -- Not being used currently
1281 --
1282 PROCEDURE G_Hdr_Initialize
1283 IS
1284 BEGIN
1285
1286 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
1287
1288 -- Header population
1289 IF (l_debug = 1) THEN
1290 print_debug('GMF_get_transfer_price_PUB.G_Hdr_Initialize: Populating G_HDR...');
1291 END IF;
1292
1293 --
1294 -- For InterOrg Transfers header is not supported.
1295 -- For Adv Pricing setup purpose we've to create one. So, we created
1296 -- a dummy header
1297 --
1298 -- INV_IC_ORDER_PUB.G_TXN_HDR.dummy := 1;
1299
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1303 IF (l_debug = 1) THEN
1304 print_debug('GMF_get_transfer_price_PUB.G_Hdr_Initialize: EXCEP UNEXP OTHERS - ' || sqlerrm);
1305 END IF;
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1307
1308 END G_Hdr_Initialize;
1309
1310
1311 PROCEDURE G_Line_Initialize
1312 IS
1313 BEGIN
1314
1315 -- Line population
1316 IF (l_debug = 1) THEN
1317 print_debug('GMF_get_transfer_price_PUB.G_Line_Initialize: Populating G_LINE...');
1318 END IF;
1319
1320 INV_IC_ORDER_PUB.G_LINE.from_organization_id := G_from_organization_id;
1321 INV_IC_ORDER_PUB.G_LINE.to_organization_id := G_to_organization_id;
1322
1323 INV_IC_ORDER_PUB.G_LINE.from_ou := G_from_ou;
1324 INV_IC_ORDER_PUB.G_LINE.to_ou := G_to_ou;
1325
1326 INV_IC_ORDER_PUB.G_LINE.inventory_item_id := G_inventory_item_id;
1327 INV_IC_ORDER_PUB.G_LINE.ordered_quantity := G_transaction_qty;
1328 INV_IC_ORDER_PUB.G_LINE.order_quantity_uom := G_transaction_uom;
1329 INV_IC_ORDER_PUB.G_LINE.primary_uom := G_primary_uom;
1330
1331 INV_IC_ORDER_PUB.G_LINE.calculate_price_flag := 'Y';
1332
1333 IF (l_debug = 1) THEN
1334 print_debug('GMF_get_transfer_price_PUB.G_Line_Initialize: item/qty/uom: ' ||
1338
1335 INV_IC_ORDER_PUB.G_LINE.inventory_item_id ||'/'|| INV_IC_ORDER_PUB.G_LINE.ordered_quantity ||'/'||
1336 INV_IC_ORDER_PUB.G_LINE.order_quantity_uom);
1337 END IF;
1339 EXCEPTION
1340
1341 WHEN NO_DATA_FOUND THEN
1342 -- x_return_status := FND_API.G_RET_STS_ERROR;
1343 IF (l_debug = 1) THEN
1344 print_debug('GMF_get_transfer_price_PUB.G_Line_Initialize: EXCEP NO_DATA_FOUND ' );
1345 END IF;
1346 RAISE NO_DATA_FOUND;
1347 WHEN OTHERS THEN
1348 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1349 IF (l_debug = 1) THEN
1350 print_debug('GMF_get_transfer_price_PUB.G_Line_Initialize: EXCEP UNEXP OTHERS - ' || sqlerrm);
1351 END IF;
1352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1353
1354 END G_Line_Initialize;
1355
1356
1357 --
1358 -- Not being used currently
1359 --
1360 PROCEDURE copy_Header_to_request( p_header_rec INV_IC_ORDER_PUB.Header_Rec_Type
1361 , p_Request_Type_Code VARCHAR2
1362 , px_line_index IN OUT NOCOPY NUMBER )
1363 IS
1364 BEGIN
1365
1366 px_line_index := px_line_index+1;
1367
1368 G_REQUEST_TYPE_CODE_TBL(px_line_index) := p_Request_Type_Code;
1369 G_PRICE_REQUEST_CODE_TBL(px_line_index) := p_Request_Type_Code;
1370 -- G_LINE_INDEX_tbl(px_line_index) := p_Header_rec.dummy;
1371 G_LINE_TYPE_CODE_TBL(px_line_index) := 'ORDER';
1372
1373 -- Hold the header_id in line_id for 'HEADER' Records
1374 -- G_LINE_ID_TBL(px_line_index) := p_Header_rec.dummy;
1375
1376 G_PRICING_EFFECTIVE_DATE_TBL(px_line_index) := trunc(SYSDATE);
1377
1378 G_CURRENCY_CODE_TBL(px_line_index) := NULL;
1379 G_PROCESSED_FLAG_TBL(px_line_index) := QP_PREQ_GRP.G_NOT_PROCESSED;
1380 G_PRICING_STATUS_CODE_tbl(px_line_index) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
1381 G_USAGE_PRICING_TYPE_TBL(px_line_index) := 'REGULAR';
1382
1383 G_PRICE_FLAG_TBL(px_line_index) := 'Y';
1384 G_QUALIFIERS_EXIST_FLAG_TBL(px_line_index) :='N';
1385 G_PRICING_ATTRS_EXIST_FLAG_TBL(px_line_index) :='N';
1386 G_ROUNDING_FLAG_TBL(px_line_index) := 'Y';
1387
1388 G_ACTIVE_DATE_FIRST_TYPE_TBL(px_line_index) := NULL;
1389 G_ACTIVE_DATE_FIRST_TBL(px_line_index) := NULL;
1390 G_ACTIVE_DATE_SECOND_TBL(px_line_index) := NULL;
1391 G_ACTIVE_DATE_SECOND_TYPE_TBL(px_line_index) := NULL;
1392
1393 G_ROUNDING_FACTOR_TBL(px_line_index) := NULL;
1394 G_PROCESSING_ORDER_TBL(px_line_index) := NULL;
1395 G_PRICING_STATUS_TEXT_tbl(px_line_index) := NULL;
1396 G_PRICE_LIST_ID_TBL(px_line_index) := NULL;
1397 G_PL_VALIDATED_FLAG_TBL(px_line_index) := 'N';
1398 G_UPD_ADJUSTED_UNIT_PRICE_TBL(px_line_index) := NULL;
1399 G_LINE_QUANTITY_TBL(px_line_index) := NULL;
1400 G_LINE_UOM_CODE_TBL(px_line_index) := NULL;
1401 G_PRICED_QUANTITY_TBL(px_line_index) := NULL;
1402 G_UOM_QUANTITY_TBL(px_line_index) := NULL;
1403 G_PRICED_UOM_CODE_TBL(px_line_index) := NULL;
1404 G_UNIT_PRICE_TBL(px_line_index) := NULL;
1405 G_PERCENT_PRICE_TBL(px_line_index) := NULL;
1406 G_ADJUSTED_UNIT_PRICE_TBL(px_line_index) := NULL;
1407
1408 G_LINE_CATEGORY_TBL(px_line_index) := NULL;
1409
1410 END copy_Header_to_request;
1411
1412
1413 PROCEDURE copy_Line_to_request ( p_Line_rec IN INV_IC_ORDER_PUB.Line_Rec_Type
1414 , p_pricing_events IN VARCHAR2
1415 , p_request_type_code IN VARCHAR2
1416 , px_line_index IN OUT NOCOPY NUMBER )
1417 IS
1418 l_uom_rate NUMBER;
1419 BEGIN
1420
1421 px_line_index := px_line_index+1;
1422
1423 G_REQUEST_TYPE_CODE_TBL(px_line_index) := p_Request_Type_Code;
1424 G_PRICE_REQUEST_CODE_TBL(px_line_index) := p_Request_Type_Code;
1425
1426 G_LINE_ID_TBL(px_line_index) := px_line_index;
1427 G_LINE_INDEX_tbl(px_line_index) := G_inventory_item_id + G_from_organization_id + G_to_organization_id;
1428 G_LINE_TYPE_CODE_TBL(px_line_index) := 'LINE';
1429
1430 G_LINE_QUANTITY_TBL(px_line_index) := g_transaction_qty;
1431 G_LINE_UOM_CODE_TBL(px_line_index) := g_transaction_uom;
1432 G_CURRENCY_CODE_TBL(px_line_index) := g_from_org_currency;
1433
1434 G_PRICED_QUANTITY_TBL(px_line_index) := g_transaction_qty;
1435 G_PRICED_UOM_CODE_TBL(px_line_index) := g_transaction_uom;
1436
1437 G_PROCESSED_FLAG_TBL(px_line_index) := QP_PREQ_GRP.G_NOT_PROCESSED;
1438 G_PRICING_STATUS_CODE_TBL(px_line_index) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
1439
1440 G_PRICE_FLAG_TBL(px_line_index) := 'Y';
1441 G_ROUNDING_FLAG_TBL(px_line_index) := 'Y';
1442 G_QUALIFIERS_EXIST_FLAG_TBL(px_line_index) := 'N';
1443 G_PRICING_ATTRS_EXIST_FLAG_TBL(px_line_index) := 'N';
1444 G_PL_VALIDATED_FLAG_TBL(px_line_index) := 'N';
1445 G_USAGE_PRICING_TYPE_TBL(px_line_index) := 'REGULAR';
1446 G_PRICING_EFFECTIVE_DATE_TBL(px_line_index) := trunc(SYSDATE);
1447
1448 --
1449 -- All the following are not applicable for us
1450 --
1451 G_PERCENT_PRICE_TBL(px_line_index) := NULL;
1452
1453 G_ACTIVE_DATE_FIRST_TYPE_TBL(px_line_index) := NULL;
1454 G_ACTIVE_DATE_FIRST_TBL(px_line_index) := NULL;
1455 G_ACTIVE_DATE_SECOND_TBL(px_line_index) := NULL;
1456 G_ACTIVE_DATE_SECOND_TYPE_TBL(px_line_index) := NULL;
1457
1458 G_ROUNDING_FACTOR_TBL(px_line_index) := NULL;
1459 G_PROCESSING_ORDER_TBL(px_line_index) := NULL;
1460 G_PRICING_STATUS_TEXT_tbl(px_line_index) := NULL;
1461 G_UPD_ADJUSTED_UNIT_PRICE_TBL(px_line_index) := NULL;
1462
1463 G_PRICE_LIST_ID_TBL(px_line_index) := NULL;
1464 G_UOM_QUANTITY_TBL(px_line_index) := NULL;
1468
1465 G_UNIT_PRICE_TBL(px_line_index) := NULL;
1466 G_ADJUSTED_UNIT_PRICE_TBL(px_line_index) := NULL;
1467 G_LINE_CATEGORY_TBL(px_line_index) := NULL;
1469 END copy_Line_to_request;
1470
1471
1472 PROCEDURE Populate_Temp_Table ( x_return_status OUT NOCOPY VARCHAR2 )
1473 IS
1474 l_return_status VARCHAR2(1);
1475 l_return_status_Text VARCHAR2(2000) ;
1476 i number :=0;
1477 BEGIN
1478
1479 x_return_status := FND_API.G_RET_STS_SUCCESS;
1480 l_return_status := FND_API.G_RET_STS_SUCCESS;
1481
1482 print_debug('G_LINE_INDEX_TBL.count: ' || G_LINE_INDEX_TBL.count);
1483 FOR i IN G_LINE_INDEX_TBL.FIRST..G_LINE_INDEX_TBL.LAST
1484 LOOP
1485 IF (l_debug = 1) THEN
1486 print_debug('i = ' || i);
1487 print_debug(G_LINE_TYPE_CODE_TBL(i));
1488 print_debug('-----------------------------------------------');
1489 print_debug('line_index => '||to_char(G_LINE_INDEX_TBL(i)));
1490 print_debug('pricing_effective_date => '||to_char(G_PRICING_EFFECTIVE_DATE_TBL(i)));
1491 print_debug('active_date_first => '||to_char(G_ACTIVE_DATE_FIRST_TBL(i)));
1492 print_debug('active_date_first_type => '||G_ACTIVE_DATE_FIRST_TYPE_TBL(i));
1493 print_debug('active_date_second => '||to_char(G_ACTIVE_DATE_SECOND_TBL(i)));
1494 print_debug('active_date_second_type => '||G_ACTIVE_DATE_SECOND_TYPE_TBL(i));
1495 print_debug('line_quantity => '||to_char(G_LINE_QUANTITY_TBL(i)));
1496 print_debug('line_uom_code => '||G_LINE_UOM_CODE_TBL(i));
1497 print_debug('request_type_code => '||G_REQUEST_TYPE_CODE_TBL(i));
1498 print_debug('PRICED_QUANTITY => '||to_char(G_PRICED_QUANTITY_TBL(i)));
1499 print_debug('PRICED_UOM_CODE => '||G_PRICED_UOM_CODE_TBL(i));
1500 print_debug('CURRENCY_CODE => '||G_CURRENCY_CODE_TBL(i));
1501 -- print_debug('UNIT_PRICE => '||to_char(G_UNIT_PRICE_TBL(i)));
1502 -- print_debug('PERCENT_PRICE => '||to_char(G_PERCENT_PRICE_TBL(i)));
1503 print_debug('UOM_QUANTITY => '||to_char(G_UOM_QUANTITY_TBL(i)));
1504 -- print_debug('ADJUSTED_UNIT_PRICE => '||to_char(G_ADJUSTED_UNIT_PRICE_TBL(i)));
1505 -- print_debug('UPD_ADJUSTED_UNIT_PRICE => '||to_char(G_UPD_ADJUSTED_UNIT_PRICE_TBL(i)));
1506 print_debug('PROCESSED_FLAG => '||G_PROCESSED_FLAG_TBL(i));
1507 print_debug('price_flag => '||G_PRICE_FLAG_TBL(i));
1508 print_debug('LINE_ID => '||to_char(G_LINE_ID_TBL(i)));
1509 -- print_debug('PROCESSING_ORDER => '||to_char(G_PROCESSING_ORDER_TBL(i)));
1510 print_debug('pricing_status_code => '||substr(G_PRICING_STATUS_CODE_TBL(i), 1, 5));
1511 print_debug('PRICING_STATUS_TEXT => '||G_PRICING_STATUS_TEXT_TBL(i));
1512 print_debug('ROUNDING_FLAG => '||G_ROUNDING_FLAG_TBL(i));
1513 -- print_debug('ROUNDING_FACTOR => '||to_char(G_ROUNDING_FACTOR_TBL(i)));
1514 print_debug('QUALIFIERS_EXIST_FLAG => '||G_QUALIFIERS_EXIST_FLAG_TBL(i));
1515 print_debug('PRICING_ATTRS_EXIST_FLAG => '||G_PRICING_ATTRS_EXIST_FLAG_TBL(i));
1516 print_debug('PRICE_LIST_ID => '||to_char(G_PRICE_LIST_ID_TBL(i)));
1517 print_debug('VALIDATED_FLAG => '||G_PL_VALIDATED_FLAG_TBL(i));
1518 print_debug('PRICE_REQUEST_CODE => '||G_PRICE_REQUEST_CODE_TBL(i));
1519 print_debug('USAGE_PRICING_TYPE => '||G_USAGE_PRICING_TYPE_TBL(i));
1520 print_debug('LINE_CATEGORY => '||G_LINE_CATEGORY_TBL(i));
1521 END IF;
1522 END LOOP;
1523
1524 IF (l_debug = 1) THEN
1525 print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: Calling QP:Bulk insert routine...' );
1526 END IF;
1527
1528 QP_PREQ_GRP.INSERT_LINES2 (
1529 p_LINE_INDEX => G_LINE_INDEX_TBL,
1530 p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
1531 p_PRICING_EFFECTIVE_DATE => G_PRICING_EFFECTIVE_DATE_TBL,
1532 p_ACTIVE_DATE_FIRST => G_ACTIVE_DATE_FIRST_TBL,
1533 p_ACTIVE_DATE_FIRST_TYPE => G_ACTIVE_DATE_FIRST_TYPE_TBL,
1534 p_ACTIVE_DATE_SECOND => G_ACTIVE_DATE_SECOND_TBL,
1535 p_ACTIVE_DATE_SECOND_TYPE => G_ACTIVE_DATE_SECOND_TYPE_TBL,
1536 p_LINE_QUANTITY => G_LINE_QUANTITY_TBL,
1537 p_LINE_UOM_CODE => G_LINE_UOM_CODE_TBL,
1538 p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
1539 p_PRICED_QUANTITY => G_PRICED_QUANTITY_TBL,
1540 p_PRICED_UOM_CODE => G_PRICED_UOM_CODE_TBL,
1541 p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
1542 p_UNIT_PRICE => G_UNIT_PRICE_TBL,
1543 p_PERCENT_PRICE => G_PERCENT_PRICE_TBL,
1544 p_UOM_QUANTITY => G_UOM_QUANTITY_TBL,
1545 p_ADJUSTED_UNIT_PRICE => G_ADJUSTED_UNIT_PRICE_TBL,
1546 p_UPD_ADJUSTED_UNIT_PRICE => G_UPD_ADJUSTED_UNIT_PRICE_TBL,
1547 p_PROCESSED_FLAG => G_PROCESSED_FLAG_TBL,
1548 p_PRICE_FLAG => G_PRICE_FLAG_TBL,
1549 p_LINE_ID => G_LINE_ID_TBL,
1550 p_PROCESSING_ORDER => G_PROCESSING_ORDER_TBL,
1551 p_PRICING_STATUS_CODE => G_PRICING_STATUS_CODE_TBL,
1552 p_PRICING_STATUS_TEXT => G_PRICING_STATUS_TEXT_TBL,
1553 p_ROUNDING_FLAG => G_ROUNDING_FLAG_TBL,
1554 p_ROUNDING_FACTOR => G_ROUNDING_FACTOR_TBL,
1555 p_QUALIFIERS_EXIST_FLAG => G_QUALIFIERS_EXIST_FLAG_TBL,
1556 p_PRICING_ATTRS_EXIST_FLAG => G_PRICING_ATTRS_EXIST_FLAG_TBL,
1557 p_PRICE_LIST_ID => G_PRICE_LIST_ID_TBL,
1561 p_LINE_CATEGORY => G_LINE_CATEGORY_TBL,
1558 p_VALIDATED_FLAG => G_PL_VALIDATED_FLAG_TBL,
1559 p_PRICE_REQUEST_CODE => G_PRICE_REQUEST_CODE_TBL,
1560 p_USAGE_PRICING_TYPE => G_USAGE_PRICING_TYPE_TBL,
1562 x_status_code => l_return_status,
1563 x_status_text => l_return_status_text );
1564
1565 print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: after QP:Bulk insert routine...' );
1566
1567 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1568 IF (l_debug = 1) THEN
1569 print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: QP_PREQ_GRP.INSERT_LINES2 error ' );
1570 print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: x_return_status_text='|| l_return_status_text );
1571 END IF;
1572 x_return_status := l_return_status;
1573 fnd_message.set_name('INV', 'INV_UNHANDLED_ERR');
1574 fnd_message.set_token('ENTITY1', 'QP_PREQ_GRP.INSERT_LINES2');
1575 fnd_message.set_token('ENTITY2', substr(l_return_status_text, 1, 150) );
1576 RAISE FND_API.G_EXC_ERROR;
1577 END IF;
1578
1579 SELECT count(*)
1580 INTO i
1581 FROM qp_preq_lines_tmp;
1582
1583 IF (l_debug = 1) THEN
1584 print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: No. of records inserted in QP_PREQ_LINES_TMP=' || to_char(i));
1585 END IF;
1586
1587 EXCEPTION
1588 WHEN FND_API.G_EXC_ERROR THEN
1589 RAISE FND_API.G_EXC_ERROR;
1590 WHEN OTHERS THEN
1591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1592 IF (l_debug = 1) THEN
1593 print_debug('GMF_get_transfer_price_PUB.Populate_Temp_Table: EXCEP UNEXP OTHERS - ' || sqlerrm);
1594 END IF;
1595
1596 END Populate_Temp_Table;
1597
1598
1599 PROCEDURE Populate_Results(
1600 p_line_index IN NUMBER
1601 , x_return_status OUT NOCOPY VARCHAR2
1602 , x_msg_data OUT NOCOPY VARCHAR2
1603 )
1604 IS
1605 i number :=0;
1606 BEGIN
1607
1608 x_return_status := FND_API.G_RET_STS_SUCCESS;
1609
1610 FOR i IN G_LINE_INDEX_TBL.FIRST..G_LINE_INDEX_TBL.LAST
1611 LOOP
1612 BEGIN
1613 SELECT lines.ADJUSTED_UNIT_PRICE
1614 , lines.UNIT_PRICE
1615 , lines.ADJUSTED_UNIT_PRICE
1616 , lines.UNIT_PRICE
1617 , lines.priced_quantity
1618 , lines.priced_uom_code
1619 , lines.price_list_header_id
1620 , nvl(lines.percent_price, NULL)
1621 , nvl(lines.parent_price, NULL)
1622 , decode(lines.parent_price, NULL, 0, 0, 0, lines.adjusted_unit_price/lines.parent_price)
1623 , lines.currency_code
1624 , lines.pricing_status_code
1625 , lines.pricing_status_text
1626 INTO G_UNIT_SELLING_PRICE_TBL(i)
1627 , G_UNIT_LIST_PRICE_TBL(i)
1628 , G_UNIT_SELL_PRICE_PER_PQTY_TBL(i)
1629 , G_UNIT_LIST_PRICE_PER_PQTY_TBL(i)
1630 , G_PRICING_QUANTITY_TBL(i)
1631 , G_PRICED_UOM_CODE_TBL(i)
1632 , G_PRICE_LIST_ID_TBL(i)
1633 , G_UNIT_LIST_PERCENT_TBL(i)
1634 , G_UNIT_PERCENT_BASE_PRICE_TBL(i)
1635 , G_UNIT_SELLING_PERCENT_TBL(i)
1636 , G_CURRENCY_CODE_TBL(i)
1637 , G_PRICING_STATUS_CODE_TBL(i)
1638 , G_PRICING_STATUS_TEXT_TBL(i)
1639 FROM qp_preq_lines_tmp lines
1640 WHERE lines.line_id=G_LINE_ID_TBL(i);
1641
1642 EXCEPTION
1643 WHEN NO_DATA_FOUND THEN
1644 IF G_LINE_TYPE_CODE_TBL(i) = 'LINE' THEN
1645 x_return_status := FND_API.G_RET_STS_ERROR;
1646 IF (l_debug = 1) THEN
1647 print_debug('GMF_get_transfer_price_PUB.Populate_Results: UNIT PRICE NOT POPULATED');
1648 END IF;
1649 ELSE
1650 IF (l_debug = 1) THEN
1651 print_debug('GMF_get_transfer_price_PUB.Populate_Results: ' || G_LINE_TYPE_CODE_TBL(i) || ' NO_DATA_FOUND');
1652 END IF;
1653 END IF;
1654
1655 WHEN OTHERS THEN
1656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1657 IF (l_debug = 1) THEN
1658 print_debug('GMF_get_transfer_price_PUB.Populate_Results: ' || sqlerrm );
1659 END IF;
1660 RAISE;
1661 END;
1662
1663 IF G_LINE_TYPE_CODE_TBL(i) = 'LINE' THEN
1664 IF G_PRICING_STATUS_CODE_TBL(i) = QP_PREQ_GRP.G_STATUS_UPDATED THEN
1665 IF (l_debug = 1) THEN
1666 print_debug('GMF_get_transfer_price_PUB.Populate_Results: Unit_Price=' || G_UNIT_SELLING_PRICE_TBL(i));
1667 END IF;
1668 ELSE
1669 x_return_status := FND_API.G_RET_STS_ERROR;
1670 x_msg_data := G_PRICING_STATUS_TEXT_TBL(i);
1671
1672 IF (l_debug = 1) THEN
1673 print_debug('GMF_get_transfer_price_PUB.Populate_Results: Status_Code=' || G_PRICING_STATUS_CODE_TBL(i) ||
1674 ' Status_Text=' || G_PRICING_STATUS_TEXT_TBL(i));
1675 END IF;
1676 END IF;
1677 END IF;
1678
1679 END LOOP;
1680
1681 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1682 RAISE FND_API.G_EXC_ERROR;
1683 END IF;
1684
1685 FOR i IN G_LINE_INDEX_TBL.FIRST..G_LINE_INDEX_TBL.LAST
1686 LOOP
1687 IF (l_debug = 1) THEN
1688 print_debug(G_LINE_TYPE_CODE_TBL(i));
1689 print_debug('-----------------------------------------------');
1690 print_debug('PRICING_STATUS_CODE => ' || G_UNIT_SELLING_PERCENT_TBL(i));
1691 print_debug('UNIT_SELLING_PRICE => ' || to_char(G_UNIT_SELLING_PRICE_TBL(i)));
1692 print_debug('UNIT_LIST_PRICE => ' || to_char(G_UNIT_LIST_PRICE_TBL(i)));
1693 print_debug('UNIT_SELL_PRICE_PER_PQTY => ' || to_char(G_UNIT_SELL_PRICE_PER_PQTY_TBL(i)));
1694 print_debug('UNIT_LIST_PRICE_PER_PQTY => ' || to_char(G_UNIT_LIST_PRICE_PER_PQTY_TBL(i)));
1695 print_debug('PRICING_QUANTITY => ' || to_char(G_PRICING_QUANTITY_TBL(i)));
1696 print_debug('PRICING_QUANTITY_UOM => ' || G_PRICED_UOM_CODE_TBL(i));
1697 print_debug('PRICE_LIST_ID => ' || to_char(G_PRICE_LIST_ID_TBL(i)));
1698 print_debug('UNIT_LIST_PERCENT => ' || to_char(G_UNIT_LIST_PERCENT_TBL(i)));
1699 print_debug('UNIT_PERCENT_BASE_PRICE => ' || to_char(G_UNIT_PERCENT_BASE_PRICE_TBL(i)));
1700 print_debug('UNIT_SELLING_PERCENT => ' || to_char(G_UNIT_SELLING_PERCENT_TBL(i)));
1701 print_debug('CURRENCY_CODE => ' || G_CURRENCY_CODE_TBL(i));
1702 END IF;
1703 END LOOP;
1704
1705 EXCEPTION
1706 WHEN FND_API.G_EXC_ERROR
1707 THEN
1708 print_debug('EXC_ERROR in GMF_get_transfer_price_PUB.Populate_Results');
1709 x_return_status := FND_API.G_RET_STS_ERROR;
1710 RAISE FND_API.G_EXC_ERROR;
1711
1712 WHEN OTHERS
1713 THEN
1714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1715 IF (l_debug = 1) THEN
1716 print_debug('GMF_get_transfer_price_PUB.Populate_Results: EXCEP UNEXP OTHERS - ' || sqlerrm);
1717 END IF;
1718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1719 END Populate_Results;
1720
1721 END GMF_get_transfer_price_PUB;