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