[Home] [Help]
PACKAGE BODY: APPS.INV_TRANSACTION_FLOW_PUB
Source
1 PACKAGE BODY INV_TRANSACTION_FLOW_PUB AS
2 /* $Header: INVPICTB.pls 120.26.12020000.3 2013/01/21 03:03:43 pdong ship $ */
3
4 /** These two global variable are used to cache the start OU and get the functional currency of
5 The start OU
6 **/
7 G_FUNCTIONAL_CURRENCY_CODE VARCHAR2(31);
8 G_SETS_OF_BOOK_ID NUMBER := -1;
9 G_INV_CURR_ORG NUMBER := -1;
10 G_INV_CURR_CODE VARCHAR2(31);
11 G_FROM_ORG_ID NUMBER := -1;
12 G_TO_ORG_ID NUMBER := -1;
13 G_FLOW_TYPE NUMBER := -1;
14 g_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
15 g_version_printed BOOLEAN := FALSE;
16
17 G_PKG_NAME CONSTANT VARCHAR2(50) := 'INV_TRANSACTION_FLOW_PUB';
18
19 G_ORDER_LINE_ID NUMBER := -1; /* Bug 5527437: umoogala */
20
21 Procedure print_debug(p_message IN VARCHAR2,
22 p_module IN VARCHAR2)
23 IS
24 BEGIN
25 IF (g_debug=1) THEN
26 IF NOT g_version_printed THEN
27 INV_TRX_UTIL_PUB.TRACE('$Header: INVPICTB.pls 120.26.12020000.3 2013/01/21 03:03:43 pdong ship $',G_PKG_NAME, 9);
28 g_version_printed := TRUE;
29 END IF;
30 inv_log_util.trace(p_message, p_module);
31 END IF;
32 --dbms_output.put_line(p_module || ' ' || p_message);
33 end;
34
35 Procedure print_debug(p_message IN VARCHAR2)
36 IS
37 BEGIN
38 print_debug(p_message, 'INV_TRANSACTION_FLOW_PUB');
39 --dbms_output.put_line(p_module || ' ' || p_message);
40 end;
41
42
43
44 /*===================================================================================================
45 * Procedure: GET_TRANSACTION_FLOW()
46 *
47 * Description:
48 * This API is used to get a valid Inter-company Transaction Flow for a pair of Start Operating Unit
49 * and End Operating Unit, which is active on the transaction date for either Global Procurement Flow
50 * or Drop Ship flow.
51 * This API will be a public API and will be called by
52 * 1. the "Create Logical Transaction" API within Oracle Inventory,
53 * 2. by Receiving during the time of delivery for True Drop Ship flows
54 * 3. by Oracle Costing while creating the Receiving Accounting Event records for
55 * Global Procurement flows
56 *
57 * Usage:
58 * To get a valid Inter-company Transaction Flow for a pair of Start Operating Unit and
59 * End Operating Unit, which is active on the transaction date for either Global Procurement Flow
60 * or Drop Ship flow.
61 *
62 * Inputs:
63 * This API will receive the following input parameters:
64 * 1. Start OU: The start Operating Unit for which the Global Procurement or Drop Ship occurred.
65 * This is a required parameter.
66 * 2. End OU: The End Operating Unit for which the Global Procurement of Drop Ship occurred.
67 * This is a required parameter
68 * 3. Flow Type: To indicate what is the flow type, either Global Procurement or Drop Ship
69 * 4. Array of Qualifier Codes: The qualifier code, for this release, it will be "1" - Category.
70 * This is an optional parameter. Default value for this parameter is NULL.
71 * 5. Array of Qualifier Value IDs: The value of the qualifier.
72 * For this release, it will be the category_id of the item. This is an optional parameter.
73 * The default value of this parameter will be NULL.
74 * 6. Transaction Date: The date when the transaction is going to happen.
75 * 7. API version - the version of the API
76 * 8. Get default cost group - Flag to get the default cost group
77 *
78 * Outputs:
79 * This API will return a table of records of all the nodes in between the
80 * Start Operating Unit and End Operating Unit, the pricing options,
81 * and the Inter-Company Relations information.
82 *===================================================================================================*/
83 PROCEDURE GET_TRANSACTION_FLOW
84 (
85 x_return_status OUT NOCOPY VARCHAR2
86 , x_msg_data OUT NOCOPY VARCHAR2
87 , x_msg_count OUT NOCOPY NUMBER
88 , x_transaction_flows_tbl OUT NOCOPY g_transaction_flow_tbl_type
89 , p_api_version IN NUMBER
90 , p_init_msg_list IN VARCHAR2
91 , p_start_operating_unit IN NUMBER
92 , p_end_operating_unit IN NUMBER
93 , p_flow_type IN NUMBER
94 , p_organization_id IN NUMBER
95 , p_qualifier_code_tbl IN NUMBER_TBL
96 , p_qualifier_value_tbl IN NUMBER_TBL
97 , p_transaction_date IN DATE
98 , p_get_default_cost_group IN VARCHAR2)
99 IS
100 CURSOR txn_flow_hdrs(l_start_org_id NUMBER, l_end_org_id NUMBER, l_flow_type NUMBER, l_txn_date DATE)
101 IS
102 SELECT
103 t_hdr.header_id,
104 t_hdr.start_org_id,
105 t_hdr.end_org_id,
106 t_hdr.organization_id,
107 t_hdr.start_date,
108 t_hdr.end_date,
109 t_hdr.asset_item_pricing_option,
110 t_hdr.expense_item_pricing_option,
111 t_hdr.new_accounting_flag,
112 t_hdr.qualifier_code,
113 t_hdr.qualifier_value_id
114 FROM
115 mtl_transaction_flow_headers t_hdr
116 WHERE
117 t_hdr.start_org_id = l_start_org_id
118 and t_hdr.end_org_id = l_end_org_id
119 and l_txn_date between t_hdr.start_date and nvl(t_hdr.end_date,l_txn_date+1)
120 and t_hdr.flow_type = l_flow_type
121 ORDER BY
122 t_hdr.organization_id,t_hdr.qualifier_code;
123
124 CURSOR txn_flow_lines(l_header_id NUMBER,l_flow_type NUMBER)
125 IS
126 SELECT
127 t_line.line_number,
128 t_line.from_org_id,
129 t_line.from_organization_id,
130 t_line.to_org_id,
131 t_line.to_organization_id,
132 --
133 icp.customer_id,
134 icp.address_id,
135 icp.customer_site_id,
136 icp.cust_trx_type_id,
137 icp.vendor_id,
138 icp.vendor_site_id,
139 icp.freight_code_combination_id,
140 icp.inventory_accrual_account_id,
141 icp.expense_accrual_account_id,
142 icp.intercompany_cogs_account_id
143 FROM
144 mtl_transaction_flow_lines t_line,
145 mtl_intercompany_parameters icp
146 WHERE
147 t_line.header_id = l_header_id
148 and icp.ship_organization_id=t_line.from_org_id
149 and icp.sell_organization_id=t_line.to_org_id
150 and icp.flow_type = l_flow_type
151 ORDER BY t_line.line_number;
152
153 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
154 l_transaction_flows_tbl g_transaction_flow_tbl_type;
155 rcount NUMBER := 0;
156 l_match BOOLEAN;
157 l_found BOOLEAN;
158 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRANSACTION_FLOW';
159 l_api_version_number CONSTANT NUMBER := 1.0;
160 l_from_ou_name VARCHAR2(240);
161 l_to_ou_name VARCHAR2(240);
162 BEGIN
163
164 x_return_status := g_ret_sts_success;
165 x_msg_data := null;
166 x_msg_count := 0;
167
168 -- Standard call to check for call compatibility
169 IF NOT FND_API.Compatible_API_Call(
170 l_api_version_number
171 , p_api_version
172 , l_api_name
173 , G_PKG_NAME)
174 THEN
175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
176 END IF;
177
178
179 -- Initialize message list.
180 IF fnd_api.to_boolean(p_init_msg_list) THEN
181 fnd_msg_pub.initialize;
182 END IF;
183
184
185 IF l_debug = 1 then
186 print_debug('Entered get_transaction_flow', l_api_name);
187 print_debug('p_start_operating_unit '||p_start_operating_unit, l_api_name);
188 print_debug('p_end_operating_unit '||p_end_operating_unit, l_api_name);
189 print_debug('p_flow_type '|| p_flow_type, l_api_name);
190 print_debug('p_organization_id '||p_organization_id, l_api_name);
191 print_debug('p_transaction_date '||To_char(p_transaction_date,'DD-MON-YYYY HH24:MI:SS'), l_api_name);
192 print_debug('p_get_default_cost_group '||p_get_default_cost_group, l_api_name);
193
194 IF p_qualifier_code_tbl.COUNT > 0
195 AND p_qualifier_value_tbl.COUNT > 0
196 AND p_qualifier_code_tbl.COUNT = p_qualifier_value_tbl.COUNT THEN
197
198 FOR i IN 1..p_qualifier_code_tbl.COUNT LOOP
199 print_debug('p_qualifier_code_tbl('||i||'):'||p_qualifier_code_tbl(i), l_api_name);
200 print_debug('p_qualifier_value_tbl('||i||'):'||p_qualifier_value_tbl(i), l_api_name);
201 END LOOP;
202 END IF;--IF p_qualifier_code_tbl.COUNT > 0....
203 END IF;
204
205
206
207 FOR l_txn_flow_hdrs IN txn_flow_hdrs(p_start_operating_unit, p_end_operating_unit, p_flow_type, p_transaction_date) LOOP
208
209 l_match := TRUE;
210
211 IF l_debug = 1 THEN
212 print_debug(' Verifying match for txn flow hdr '||l_txn_flow_hdrs.header_id, l_api_name);
213 END IF;
214
215 /************************************************
216 l_txn_flow_hdrs.organization_id |p_organization_id |match
217 NOT NULL NOT NULL only when l_txn_flow_hdrs.organization_id=p_organization_id
218 NOT NULL NULL Not a Match
219 NULL NOT NULL Match
220 NULL NULL Match
221 ************************************************/
222
223 IF Nvl(l_txn_flow_hdrs.organization_id,Nvl(p_organization_id,-9999)) = NVL(p_organization_id,-9999) THEN
224 l_match := TRUE;
225 ELSE
226 l_match := FALSE;
227 END IF;-- IF l_txn_flow_hdrs.organization_id = p_organization_id
228
229 IF l_debug = 1 THEN
230 print_debug('p_organization_id '||p_organization_id||' compare to '||
231 l_txn_flow_hdrs.organization_id, l_api_name);
232 IF l_match THEN
233 print_debug('l_match TRUE', l_api_name);
234 ELSE
235 print_debug('l_match FALSE', l_api_name);
236 END IF;--IF l_match THEN
237 END IF;-- IF l_debug = 1 THEN
238
239 IF l_match THEN
240 IF (p_qualifier_code_tbl.COUNT > 0)
241 AND (p_qualifier_value_tbl.COUNT > 0)
242 AND (p_qualifier_code_tbl.COUNT = p_qualifier_value_tbl.COUNT) THEN
243
244 IF l_debug = 1 THEN
245 print_debug('p_qualifier_code_tbl(1) '||p_qualifier_code_tbl(1)||' compare to '||
246 l_txn_flow_hdrs.qualifier_code, l_api_name);
247 end if;
248
249 IF Nvl(l_txn_flow_hdrs.qualifier_code, Nvl(p_qualifier_code_tbl(1),-9999)) = Nvl(p_qualifier_code_tbl(1),-9999)
250 AND Nvl(l_txn_flow_hdrs.qualifier_value_id,Nvl(p_qualifier_value_tbl(1),-9999)) = Nvl(p_qualifier_value_tbl(1),-9999) THEN
251 l_match := TRUE;
252 ELSE
253 l_match := FALSE;
254 END IF;
255 ELSIF l_txn_flow_hdrs.qualifier_code IS NULL
256 AND l_txn_flow_hdrs.qualifier_value_id IS NULL THEN
257 l_match := TRUE;
258 ELSE
259 l_match := FALSE;
260 END IF;
261
262 IF l_debug = 1 THEN
263 IF l_match THEN
264 print_debug('l_match TRUE', l_api_name);
265 ELSE
266 print_debug('l_match FALSE', l_api_name);
267 END IF;--IF l_match THEN
268 END IF;-- IF l_debug = 1 THEN
269
270 END IF;--IF l_match THEN
271
272 IF l_match THEN
273
274 rcount := rcount + 1;
275
276 l_transaction_flows_tbl(rcount).HEADER_ID := l_txn_flow_hdrs.header_id;
277 l_transaction_flows_tbl(rcount).START_ORG_ID := l_txn_flow_hdrs.start_org_id;
278 l_transaction_flows_tbl(rcount).END_ORG_ID := l_txn_flow_hdrs.end_org_id;
279 l_transaction_flows_tbl(rcount).ORGANIZATION_ID := l_txn_flow_hdrs.organization_id;
280 l_transaction_flows_tbl(rcount).ASSET_ITEM_PRICING_OPTION := l_txn_flow_hdrs.asset_item_pricing_option;
281 l_transaction_flows_tbl(rcount).EXPENSE_ITEM_PRICING_OPTION := l_txn_flow_hdrs.expense_item_pricing_option;
282 l_transaction_flows_tbl(rcount).START_DATE := l_txn_flow_hdrs.start_date;
283 l_transaction_flows_tbl(rcount).END_DATE := l_txn_flow_hdrs.end_date;
284 l_transaction_flows_tbl(rcount).NEW_ACCOUNTING_FLAG := l_txn_flow_hdrs.new_accounting_flag;
285
286 FOR l_txn_flow_lines IN txn_flow_lines(l_txn_flow_hdrs.header_id,p_flow_type) LOOP
287
288 l_transaction_flows_tbl(rcount).HEADER_ID := l_txn_flow_hdrs.header_id;
289 l_transaction_flows_tbl(rcount).START_ORG_ID := l_txn_flow_hdrs.start_org_id;
290 l_transaction_flows_tbl(rcount).END_ORG_ID := l_txn_flow_hdrs.end_org_id;
291 l_transaction_flows_tbl(rcount).ORGANIZATION_ID := l_txn_flow_hdrs.organization_id;
292 l_transaction_flows_tbl(rcount).ASSET_ITEM_PRICING_OPTION := l_txn_flow_hdrs.asset_item_pricing_option;
293 l_transaction_flows_tbl(rcount).EXPENSE_ITEM_PRICING_OPTION := l_txn_flow_hdrs.expense_item_pricing_option;
294 l_transaction_flows_tbl(rcount).START_DATE := l_txn_flow_hdrs.start_date;
295 l_transaction_flows_tbl(rcount).END_DATE := l_txn_flow_hdrs.end_date;
296 l_transaction_flows_tbl(rcount).NEW_ACCOUNTING_FLAG := l_txn_flow_hdrs.new_accounting_flag;
297
298 --Line attributes
299 l_transaction_flows_tbl(rcount).LINE_NUMBER := l_txn_flow_lines.line_number;
300 l_transaction_flows_tbl(rcount).FROM_ORG_ID := l_txn_flow_lines.from_org_id;
301 l_transaction_flows_tbl(rcount).FROM_ORGANIZATION_ID := l_txn_flow_lines.from_organization_id;
302 l_transaction_flows_tbl(rcount).TO_ORG_ID := l_txn_flow_lines.to_org_id;
303 l_transaction_flows_tbl(rcount).TO_ORGANIZATION_ID := l_txn_flow_lines.to_organization_id;
304 --I/C attributes
305 l_transaction_flows_tbl(rcount).CUSTOMER_ID := l_txn_flow_lines.customer_id;
306 l_transaction_flows_tbl(rcount).ADDRESS_ID := l_txn_flow_lines.address_id;
307 l_transaction_flows_tbl(rcount).CUSTOMER_SITE_ID := l_txn_flow_lines.customer_site_id;
308 l_transaction_flows_tbl(rcount).CUST_TRX_TYPE_ID := l_txn_flow_lines.cust_trx_type_id;
309 l_transaction_flows_tbl(rcount).VENDOR_ID := l_txn_flow_lines.vendor_id;
310 l_transaction_flows_tbl(rcount).VENDOR_SITE_ID := l_txn_flow_lines.vendor_site_id;
311 l_transaction_flows_tbl(rcount).FREIGHT_CODE_COMBINATION_ID := l_txn_flow_lines.freight_code_combination_id;
312 l_transaction_flows_tbl(rcount).INVENTORY_ACCRUAL_ACCOUNT_ID := l_txn_flow_lines.inventory_accrual_account_id;
313 l_transaction_flows_tbl(rcount).EXPENSE_ACCRUAL_ACCOUNT_ID := l_txn_flow_lines.expense_accrual_account_id;
314 l_transaction_flows_tbl(rcount).INTERCOMPANY_COGS_ACCOUNT_ID := l_txn_flow_lines.intercompany_cogs_account_id;
315
316 if p_get_default_cost_group in ('Y','y') THEN
317
318 IF l_txn_flow_lines.from_organization_id IS NOT NULL THEN
319 BEGIN
320 SELECT default_cost_group_id
321 INTO l_transaction_flows_tbl(rcount).From_ORG_COST_GROUP_ID
322 FROM mtl_parameters mp
323 WHERE
324 organization_id = l_txn_flow_lines.from_organization_id;
325 EXCEPTION
326 WHEN no_data_found THEN
327 RAISE fnd_api.g_exc_error;
328 END;
329 END IF;--if l_txn_flows.from_organization_id IS ...
330
331 IF l_txn_flow_lines.to_organization_id IS NOT NULL THEN
332 BEGIN
333 SELECT default_cost_group_id
334 INTO l_transaction_flows_tbl(rcount).to_ORG_COST_GROUP_ID
335 FROM mtl_parameters mp
336 WHERE
337 organization_id = l_txn_flow_lines.to_organization_id;
338 EXCEPTION
339 WHEN no_data_found THEN
340 RAISE fnd_api.g_exc_error;
341 END;
342 END IF;--IF l_txn_flows.to_organization_id
343
344 END IF;--if p_get_default_cost_group in ('Y','y') THEN
345
346 rcount := rcount + 1;
347
348 END LOOP;-- FOR l_txn_flow_lines IN txn_flow..
349
350 IF l_debug = 1 THEN
351 print_debug(' returning Header_id '||l_txn_flow_hdrs.header_id, l_api_name);
352 END IF;
353 --Exiting the loop as we found the matching transaction flow
354 EXIT;
355
356 END IF; -- if l_match
357
358 END LOOP;--l_txn_flow_hdrs IN txn_flows...
359
360 IF rcount > 0 THEN
361 x_transaction_flows_tbl := l_transaction_flows_tbl;
362 ELSE
363 x_return_status := g_ret_sts_warning;
364 BEGIN
365 select name
366 into l_from_ou_name
367 FROM hr_organization_units
368 WHERE organization_id = p_start_operating_unit;
369
370 EXCEPTION
371 when no_data_found then
372 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_START_ORG');
373 FND_MSG_PUB.ADD;
374 raise fnd_api.g_exc_error;
375 end;
376
377 BEGIN
378 select name
379 into l_to_ou_name
380 FROM hr_organization_units
381 WHERE organization_id = p_end_operating_unit;
382 EXCEPTION
383 when no_data_found then
384 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_END_ORG');
385 FND_MSG_PUB.ADD;
386 raise fnd_api.g_exc_error;
387 end;
388
389 fnd_message.set_name('INV', 'INV_NO_IC_TXN_FLOW');
390 FND_MESSAGE.SET_TOKEN('FROM_OU', l_from_ou_name);
391 FND_MESSAGE.SET_TOKEN('TO_OU', l_to_ou_name);
392 fnd_msg_pub.add;
393 IF l_debug = 1 THEN
394 print_debug(' No matching transaction flows found ', l_api_name);
395 END IF;
396 x_transaction_flows_tbl.DELETE;
397 END IF;
398
399 EXCEPTION
400 WHEN FND_API.G_EXC_ERROR THEN
401 x_return_status := FND_API.G_RET_STS_ERROR;
402 x_transaction_flows_tbl.delete;
403 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
404
405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
407 x_transaction_flows_tbl.delete;
408 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
409
410 WHEN OTHERS THEN
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
412 x_transaction_flows_tbl.delete;
413 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
414
415 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
416 FND_MSG_PUB.Add_Exc_Msg
417 ( G_PACKAGE_NAME, 'GET_TRANSACTION_FLOW');
418 end if;
419 END GET_TRANSACTION_FLOW;
420
421
422 /*===================================================================================================
423 * Procedure: GET_TRANSACTION_FLOW()
424 *
425 * Description:
426 * This API is used to get a valid Inter-company Transaction Flow for a
427 * given transaction flow header
428 * This API will be a public API and will be called by
429 * 1. the "Create Logical Transaction" API within Oracle Inventory,
430 *
431 * Usage:
432 * To get a valid Inter-company Transaction Flow for a given transaction flow header.
433 * Inputs:
434 * This API will receive the following input parameters:
435 * 1. API version - the version of the API
436 * 2. Header Id - Transaction Flow Header id
437 * 3. Get default cost group - if passed 'Y' , populates the from org
438 * cost group and to org cost group on the return transaction flows table
439 * Outputs:
440 * This API will return a table of records of type g_transaction_flow_tbl_type
441 * x_return_status - this API will return 'S' if it is successfull and a transaction flow record
442 * is found.
443 * - This API will return 'W' if it is successfull but no transaction flow record is
444 * found
445 * - This API will return 'U' or 'E' if something errors out.
446 *===================================================================================================*/
447 procedure get_transaction_flow(
448 x_return_status OUT NOCOPY VARCHAR2
449 , x_msg_data OUT NOCOPY VARCHAR2
450 , x_msg_count OUT NOCOPY NUMBER
451 , x_transaction_flows_tbl OUT NOCOPY g_transaction_flow_tbl_type
452 , p_api_version IN NUMBER
453 , p_init_msg_list IN VARCHAR2 default G_FALSE
454 , p_header_id IN NUMBER
455 , p_get_default_cost_group IN VARCHAR2) IS
456
457
458 CURSOR txn_flows(l_header_id NUMBER)
459 IS
460 SELECT
461 t_hdr.header_id,
462 t_hdr.start_org_id,
463 t_hdr.end_org_id,
464 t_hdr.organization_id,
465 t_hdr.start_date,
466 t_hdr.end_date,
467 t_hdr.asset_item_pricing_option,
468 t_hdr.expense_item_pricing_option,
469 t_hdr.new_accounting_flag,
470 t_hdr.qualifier_code,
471 t_hdr.qualifier_value_id,
472 --
473 t_line.line_number,
474 t_line.from_org_id,
475 t_line.from_organization_id,
476 t_line.to_org_id,
477 t_line.to_organization_id,
478 --
479 icp.customer_id,
480 icp.address_id,
481 icp.customer_site_id,
482 icp.cust_trx_type_id,
483 icp.vendor_id,
484 icp.vendor_site_id,
485 icp.freight_code_combination_id,
486 icp.inventory_accrual_account_id,
487 icp.expense_accrual_account_id,
488 icp.intercompany_cogs_account_id
489 FROM
490 mtl_transaction_flow_headers t_hdr,
491 mtl_transaction_flow_lines t_line,
492 mtl_intercompany_parameters icp
493 WHERE
494 t_hdr.header_id = l_header_id
495 AND t_hdr.header_id = t_line.header_id (+)
496 and icp.ship_organization_id=t_line.from_org_id
497 and icp.sell_organization_id=t_line.to_org_id
498 AND icp.flow_type = t_hdr.flow_type
499 ORDER BY t_line.line_number;
500
501
502 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
503 l_transaction_flows_tbl g_transaction_flow_tbl_type;
504 rcount NUMBER := 0;
505 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRANSACTION_FLOW';
506 l_api_version_number CONSTANT NUMBER := 1.0;
507 l_from_ou_name VARCHAR2(240);
508 l_to_ou_name vARCHAR2(240);
509 BEGIN
510 x_return_status := g_ret_sts_success;
511 x_msg_data := null;
512 x_msg_count := 0;
513
514 -- Standard call to check for call compatibility
515 IF NOT FND_API.compatible_api_call
516 (l_api_version_number
517 , p_api_version
518 , l_api_name
519 , G_PKG_NAME)
520 THEN
521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
522 END IF;
523
524
525 -- Initialize message list.
526 IF fnd_api.to_boolean(p_init_msg_list) THEN
527 fnd_msg_pub.initialize;
528 END IF;
529
530
531 IF l_debug = 1 then
532 print_debug('Entered get_transaction_flow', l_api_name);
533 print_debug('p_header_id '||p_header_id, l_api_name);
534 print_debug('p_get_default_cost_group '||p_get_default_cost_group, l_api_name);
535 END IF;--IF l_debug = 1 then
536
537 FOR l_txn_flows IN txn_flows(p_header_id) LOOP
538
539 rcount := rcount + 1;
540
541 l_transaction_flows_tbl(rcount).HEADER_ID := l_txn_flows.header_id;
542 l_transaction_flows_tbl(rcount).START_ORG_ID := l_txn_flows.start_org_id;
543 l_transaction_flows_tbl(rcount).END_ORG_ID := l_txn_flows.end_org_id;
544 l_transaction_flows_tbl(rcount).ORGANIZATION_ID := l_txn_flows.organization_id;
545 l_transaction_flows_tbl(rcount).ASSET_ITEM_PRICING_OPTION := l_txn_flows.asset_item_pricing_option;
546 l_transaction_flows_tbl(rcount).EXPENSE_ITEM_PRICING_OPTION := l_txn_flows.expense_item_pricing_option;
547 l_transaction_flows_tbl(rcount).START_DATE := l_txn_flows.start_date;
548 l_transaction_flows_tbl(rcount).END_DATE := l_txn_flows.end_date;
549 l_transaction_flows_tbl(rcount).NEW_ACCOUNTING_FLAG := l_txn_flows.new_accounting_flag;
550 --Line attributes
551 l_transaction_flows_tbl(rcount).LINE_NUMBER := l_txn_flows.line_number;
552 l_transaction_flows_tbl(rcount).FROM_ORG_ID := l_txn_flows.from_org_id;
553 l_transaction_flows_tbl(rcount).FROM_ORGANIZATION_ID := l_txn_flows.from_organization_id;
554 l_transaction_flows_tbl(rcount).TO_ORG_ID := l_txn_flows.to_org_id;
555 l_transaction_flows_tbl(rcount).TO_ORGANIZATION_ID := l_txn_flows.to_organization_id;
556 --I/C attributes
557 l_transaction_flows_tbl(rcount).CUSTOMER_ID := l_txn_flows.customer_id;
558 l_transaction_flows_tbl(rcount).ADDRESS_ID := l_txn_flows.address_id;
559 l_transaction_flows_tbl(rcount).CUSTOMER_SITE_ID := l_txn_flows.customer_site_id;
560 l_transaction_flows_tbl(rcount).CUST_TRX_TYPE_ID := l_txn_flows.cust_trx_type_id;
561 l_transaction_flows_tbl(rcount).VENDOR_ID := l_txn_flows.vendor_id;
562 l_transaction_flows_tbl(rcount).VENDOR_SITE_ID := l_txn_flows.vendor_site_id;
563 l_transaction_flows_tbl(rcount).FREIGHT_CODE_COMBINATION_ID := l_txn_flows.freight_code_combination_id;
564 l_transaction_flows_tbl(rcount).INVENTORY_ACCRUAL_ACCOUNT_ID := l_txn_flows.inventory_accrual_account_id;
565 l_transaction_flows_tbl(rcount).EXPENSE_ACCRUAL_ACCOUNT_ID := l_txn_flows.expense_accrual_account_id;
566 l_transaction_flows_tbl(rcount).INTERCOMPANY_COGS_ACCOUNT_ID := l_txn_flows.intercompany_cogs_account_id;
567
568 if p_get_default_cost_group in ('Y','y') THEN
569
570 IF l_txn_flows.from_organization_id IS NOT NULL THEN
571 BEGIN
572 SELECT default_cost_group_id
573 INTO l_transaction_flows_tbl(rcount).From_ORG_COST_GROUP_ID
574 FROM mtl_parameters mp
575 WHERE
576 organization_id = l_txn_flows.from_organization_id;
577 EXCEPTION
578 WHEN no_data_found THEN
579 RAISE fnd_api.g_exc_error;
580 END;
581 END IF;--if l_txn_flows.from_organization_id IS ...
582
583 IF l_txn_flows.to_organization_id IS NOT NULL THEN
584 BEGIN
585 SELECT default_cost_group_id
586 INTO l_transaction_flows_tbl(rcount).to_ORG_COST_GROUP_ID
587 FROM mtl_parameters mp
588 WHERE
589 organization_id = l_txn_flows.to_organization_id;
590 EXCEPTION
591 WHEN no_data_found THEN
592 RAISE fnd_api.g_exc_error;
593 END;
594 END IF;--IF l_txn_flows.to_organization_id
595
596 END IF;--if p_get_default_cost_group in ('Y','y') THEN
597
598
599
600 END LOOP;--l_txn_flows IN txn_flows...
601
602 IF rcount > 0 THEN
603 x_transaction_flows_tbl := l_transaction_flows_tbl;
604 ELSE
605
606 fnd_message.set_name('INV', 'INV_NO_IC_TXN_FLOW_ID');
607 FND_MESSAGE.SET_TOKEN('ID', p_headeR_id);
608
609 fnd_msg_pub.add;
610 IF l_debug = 1 THEN
611 print_debug(' No transaction flows found ', l_api_name);
612 END IF;
613 RAISE FND_API.g_exc_error;
614 END IF;
615
616 EXCEPTION
617 WHEN FND_API.G_EXC_ERROR THEN
618 x_return_status := FND_API.G_RET_STS_ERROR;
619 x_transaction_flows_tbl.delete;
620 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
621
622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
624 x_transaction_flows_tbl.delete;
625 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
626
627 WHEN OTHERS THEN
628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
629 x_transaction_flows_tbl.delete;
630 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
631
632 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
633 FND_MSG_PUB.Add_Exc_Msg
634 ( G_PACKAGE_NAME, 'GET_TRANSACTION_FLOW');
635 end if;
636 END GET_TRANSACTION_FLOW;
637
638
639 /*======================================================================================================
640 * Procedure: CHECK_TRANSACTION_FLOW()
641 * Description:
642 * This API will be a public API and will be called by PO while user creates the PO Document.
643 * This API will return true if a Inter-company Transaction Flow exists between two operating units
644 * for user specified date and qualifier.
645 *
646 * Usage:
647 * This API will return true if a Inter-company Transaction Flow exists between two operating units
648 * for user specified date and qualifier.
649 *
650 * Inputs:
651 * This API will receive the following input parameters:
652 * 1. Start OU: The start Operating Unit for which the Global Procurement or Drop Ship occurred.
653 * This is a required parameter.
654 * 2. End OU: The End Operating Unit for which the Global Procurement of Drop Ship occurred.
655 * This is a required parameter
656 * 3. Flow Type: To indicate what is the flow type, either Global Procurement or Drop Ship
657 * 4. Array of Qualifier Codes: The qualifier code, for this release, it will be "1" - Category.
658 * This is an optional parameter. Default value for this parameter is NULL.
659 * 5. Array of Qualifier Value IDs: The value of the qualifier.
660 * For this release, it will be the category_id of the item. This is an optional parameter.
661 * The default value of this parameter will be NULL.
662 * 6. Transaction Date: The date when the transaction will happen.
663 * 7. API version: the version of the API.
664 *
665 * Outputs:
666 * This API will return true if a Inter-company Transaction Flow exists between two operating units
667 * for user specified date and qualifier, otherwise, it will return false.
668 * The API will also return the header_id for the Inter-company Transaction Flow,
669 * and the new_accounting_flag to indicate whether Inter-company Transaction Flow is used or not.
670 *======================================================================================================*/
671 PROCEDURE CHECK_TRANSACTION_FLOW(
672 p_api_version IN NUMBER
673 , p_init_msg_list IN VARCHAR2
674 , p_start_operating_unit IN NUMBER
675 , p_end_operating_unit IN NUMBER
676 , p_flow_type IN NUMBER
677 , p_organization_id IN NUMBER
678 , p_qualifier_code_tbl IN NUMBER_TBL
679 , p_qualifier_value_tbl IN NUMBER_TBL
680 , p_transaction_date IN DATE
681 , x_return_status OUT NOCOPY VARCHAR2
682 , x_msg_count OUT NOCOPY NUMBER
683 , x_msg_data OUT NOCOPY VARCHAR2
684 , x_header_id OUT NOCOPY NUMBER
685 , x_new_accounting_flag OUT NOCOPY VARCHAR2
686 , x_transaction_flow_exists OUT NOCOPY VARCHAR2 )
687 IS
688 CURSOR txn_flow_hdrs(l_start_org_id NUMBER,
689 l_end_org_id NUMBER,
690 l_flow_type NUMBER,
691 l_txn_date DATE)
692 IS
693 SELECT
694 t_hdr.HEADER_ID,
695 t_hdr.organization_id,
696 t_hdr.new_accounting_flag,
697 t_hdr.Qualifier_Code,
698 t_hdr.Qualifier_Value_Id
699 FROM
700 mtl_transaction_flow_headers t_hdr
701 WHERE
702 t_hdr.start_org_id = l_start_org_id
703 and t_hdr.end_org_id = l_end_org_id
704 and l_txn_date between t_hdr.start_date and nvl(t_hdr.end_date,l_txn_date+1)
705 and t_hdr.flow_type = l_flow_type
706 ORDER BY
707 t_hdr.organization_id, t_hdr.qualifier_code;
708
709 l_match BOOLEAN;
710 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
711 l_api_version_number CONSTANT NUMBER := 1.0;
712 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_TRANSACTION_FLOW';
713 l_from_ou_name VARCHAR2(240);
714 l_to_ou_name VARCHAR2(240);
715 BEGIN
716 x_return_status := g_ret_sts_success;
717 x_msg_data := null;
718 x_msg_count := 0;
719 l_match := FALSE;
720
721 -- Standard call to check for call compatibility
722 IF NOT FND_API.Compatible_API_Call(
723 l_api_version_number
724 , p_api_version
725 , l_api_name
726 , G_PKG_NAME)
727 THEN
728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
729 END IF;
730
731
732 -- Initialize message list.
733 IF fnd_api.to_boolean(p_init_msg_list) THEN
734 fnd_msg_pub.initialize;
735 END IF;
736
737
738 IF l_debug = 1 then
739 print_debug('Entered check_transaction_flow', l_api_name);
740 print_debug('p_start_operating_unit '||p_start_operating_unit, l_api_name);
741 print_debug('p_end_operating_unit '||p_end_operating_unit, l_api_name);
742 print_debug('p_flow_type '|| p_flow_type, l_api_name);
743 print_debug('p_organization_id '||p_organization_id, l_api_name);
744 print_debug('p_transaction_date '||To_char(p_transaction_date,'DD-MON-YYYY HH24:MI:SS'), l_api_name);
745
746 IF p_qualifier_code_tbl.COUNT > 0
747 AND p_qualifier_value_tbl.COUNT > 0
748 AND p_qualifier_code_tbl.COUNT = p_qualifier_value_tbl.COUNT THEN
749
750 FOR i IN 1..p_qualifier_code_tbl.COUNT LOOP
751 print_debug('p_qualifier_code_tbl('||i||'):'||p_qualifier_code_tbl(i), l_api_name);
752 print_debug('p_qualifier_value_tbl('||i||'):'||p_qualifier_value_tbl(i), l_api_name);
753 END LOOP;
754 END IF;--IF p_qualifier_code_tbl.COUNT > 0....
755 END IF;
756
757
758 FOR l_txn_flow_hdrs IN txn_flow_hdrs(p_start_operating_unit, p_end_operating_unit, p_flow_type,p_transaction_date) LOOP
759
760 l_match := TRUE;
761 IF l_debug = 1 THEN
762 print_debug('verifying match txn flow hdr:'||l_txn_flow_hdrs.header_id, l_api_name);
763 END IF;
764
765 /**************************************************************************************************************
766 Matching Logic
767
768 l_txn_flow_hdrs.organization_id |p_organization_id |match
769 NOT NULL NOT NULL only when l_txn_flow_hdrs.organization_id=p_organization_id
770 NOT NULL NULL Not a Match
771 NULL NOT NULL Match
772 NULL NULL Match
773 ****************************************************************************************************************/
774
775 IF Nvl(l_txn_flow_hdrs.organization_id,Nvl(p_organization_id,-9999)) = NVL(p_organization_id,-9999) THEN
776 l_match := TRUE;
777 ELSE
778 l_match := FALSE;
779 END IF;-- IF l_txn_flow_hdrs.organization_id = p_organization_id
780
781 IF l_debug = 1 THEN
782 print_debug('p_organization_id '||p_organization_id||' compare to '||
783 l_txn_flow_hdrs.organization_id, l_api_name);
784 IF l_match THEN
785 print_debug('l_match TRUE', l_api_name);
786 ELSE
787 print_debug('l_match FALSE', l_api_name);
788 END IF;--IF l_match THEN
789 END IF;-- IF l_debug = 1 THEN
790
791 IF l_match THEN
792
793 IF (p_qualifier_code_tbl.COUNT > 0)
794 AND (p_qualifier_value_tbl.COUNT > 0)
795 AND (p_qualifier_code_tbl.COUNT = p_qualifier_value_tbl.COUNT) THEN
796
797 IF l_debug = 1 THEN
798 print_debug('p_qualifier_code_tbl(1) '||p_qualifier_code_tbl(1)||' compare to '||
799 l_txn_flow_hdrs.qualifier_code, l_api_name);
800 end if;
801
802 IF Nvl(l_txn_flow_hdrs.qualifier_code, Nvl(p_qualifier_code_tbl(1),-9999)) = Nvl(p_qualifier_code_tbl(1),-9999)
803 AND Nvl(l_txn_flow_hdrs.qualifier_value_id,Nvl(p_qualifier_value_tbl(1),-9999)) = Nvl(p_qualifier_value_tbl(1),-9999) THEN
804 l_match := TRUE;
805 ELSE
806 l_match := FALSE;
807 END IF;
808 ELSIF l_txn_flow_hdrs.qualifier_code IS NULL
809 AND l_txn_flow_hdrs.qualifier_value_id IS NULL THEN
810 l_match := TRUE;
811 ELSE
812 l_match := FALSE;
813 END IF;
814
815 IF l_debug = 1 THEN
816 IF l_match THEN
817 print_debug('l_match TRUE', l_api_name);
818 ELSE
819 print_debug('l_match FALSE', l_api_name);
820 END IF;--IF l_match THEN
821 END IF;-- IF l_debug = 1 THEN
822
823 END IF;----IF l_match THEN
824
825 IF l_match THEN
826 x_header_id := l_txn_flow_hdrs.header_id;
827 x_new_accounting_flag := l_txn_flow_hdrs.new_accounting_flag;
828 x_transaction_flow_exists := g_transaction_flow_found;
829 IF l_debug = 1 THEN
830 print_debug('match txn flow hdr:'||l_txn_flow_hdrs.header_id, l_api_name);
831 print_debug('new_accounting_flag:'||l_txn_flow_hdrs.new_accounting_flag, l_api_name);
832 END IF;
833 EXIT;
834 ELSE
835 IF l_debug = 1 THEN
836 print_debug('not match txn flow hdr:'||l_txn_flow_hdrs.header_id, l_api_name);
837 END IF;
838 END IF;
839 END LOOP;
840
841 IF NOT l_match THEN
842 BEGIN
843 select name
844 into l_from_ou_name
845 FROM hr_organization_units
846 WHERE organization_id = p_start_operating_unit;
847
848 EXCEPTION
849 when no_data_found then
850 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_START_ORG');
851 FND_MSG_PUB.ADD;
852 raise fnd_api.g_exc_error;
853 end;
854
855 BEGIN
856 select name
857 into l_to_ou_name
858 FROM hr_organization_units
859 WHERE organization_id = p_end_operating_unit;
860 EXCEPTION
861 when no_data_found then
862 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_END_ORG');
863 FND_MSG_PUB.ADD;
864 raise fnd_api.g_exc_error;
865 end;
866
867 fnd_message.set_name('INV', 'INV_NO_IC_TXN_FLOW');
868 FND_MESSAGE.SET_TOKEN('FROM_OU', l_from_ou_name);
869 FND_MESSAGE.SET_TOKEN('TO_OU', l_to_ou_name);
870 fnd_msg_pub.add;
871
872 IF l_debug = 1 THEN
873 print_debug(' No matching transaction flows found ', l_api_name);
874 END IF;
875 x_header_id := NULL;
876 x_new_accounting_flag := NULL;
877 x_transaction_flow_exists := G_TRANSACTION_FLOW_NOT_FOUND;
878 END IF;
879
880 EXCEPTION
881 WHEN FND_API.G_EXC_ERROR THEN
882 x_return_status := FND_API.G_RET_STS_ERROR;
883 x_transaction_flow_exists := G_TRANSACTION_FLOW_NOT_FOUND;
884 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
885
886 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
887 x_return_status := FND_API.G_RET_STS_ERROR;
888 x_transaction_flow_exists := G_TRANSACTION_FLOW_NOT_FOUND;
889 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
890
891 WHEN OTHERS THEN
892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
893 x_transaction_flow_exists := G_TRANSACTION_FLOW_NOT_FOUND;
894 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
895
896 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
897 FND_MSG_PUB.Add_Exc_Msg
898 ( G_PACKAGE_NAME, 'CHECK_TRANSACTION_FLOW');
899 end if;
900 END CHECK_TRANSACTION_FLOW;
901
902 /*=======================================================================================================
903 * Procedure: Insert_Row()
904 * This API is a private API to insert new transaction flow for a start operating unit and end operating unit.
905 * This API will be called by the Transaction Flow Setup Form on the ON-INSERT trigger of the block.
906 * Inputs:
907 *
908 * 1. Start OU: The start Operating Unit for which the Global Procurement or Drop Ship occurred.
909 * This is a required parameter.
910 * 2. End OU: The End Operating Unit for which the Global Procurement of Drop Ship occurred.
911 * This is a required parameter
912 * 3. Flow Type: To indicate what is the flow type, either Global Procurement or Drop Ship
913 * 4. Qualifier Code: The qualifier code, for this release, it will be "1" - Category.
914 * This is an optional parameter. Default value for this parameter is NULL.
915 * 5. Qualifier Value ID: The value of the qualifier.
916 * For this release, it will be the category_id of the item. The default value of this parameter will be NULL.
917 * 6. Start Date: The date when the Inter-company Transaction Flow become active.
918 * The default value is SYSDATE. This is required parameter
919 * 7. End Date: The date when the when Inter-company Transaction Flow become inactive.
920 * 8. Asset Item Pricing Option: The pricing option for asset item for global procurement flow.
921 * 9. Expense Item Pricing option: the pricing option for expense item
922 * 10. new accounting flag : flag to indicate new accounting will be use
923 * 11. line_number_tbl - list of sequence of the line nodes
924 * 12. from_ou_tbl - list of from operating unit of the line nodes
925 * 13. to_ou_tbl - list of to_operating unit of the line nodes
926 *
927 * Outputs:
928 * 1. header_id
929 * 2. line_number
930 *
931 *=======================================================================================================*/
932 PROCEDURE create_transaction_flow
933 (
934 x_return_status OUT NOCOPY VARCHAR2
935 , x_msg_data OUT NOCOPY VARCHAR2
936 , x_msg_count OUT NOCOPY NUMBER
937 , x_header_id OUT NOCOPY NUMBER
938 , x_line_number_tbl OUT NOCOPY NUMBER_TBL
939 , p_api_version IN NUMBER
940 , p_init_msg_list IN VARCHAR2
941 , p_validation_level IN NUMBER
942 , p_start_org_id IN NUMBER
943 , p_end_org_id IN NUMBER
944 , p_flow_type IN NUMBER
945 , p_organization_id IN NUMBER
946 , p_qualifier_code IN NUMBER
947 , p_qualifier_value_id IN NUMBER
948 , p_asset_item_pricing_option IN NUMBER
949 , p_expense_item_pricing_option IN NUMBER
950 , p_new_accounting_flag IN VARCHAR2
951 , p_start_date IN DATE
952 , p_end_date IN DATE
953 , P_Attribute_Category IN VARCHAR2
954 , P_Attribute1 IN VARCHAR2
955 , P_Attribute2 IN VARCHAR2
956 , P_Attribute3 IN VARCHAR2
957 , P_Attribute4 IN VARCHAR2
958 , P_Attribute5 IN VARCHAR2
959 , P_Attribute6 IN VARCHAR2
960 , P_Attribute7 IN VARCHAR2
961 , P_Attribute8 IN VARCHAR2
962 , P_Attribute9 IN VARCHAR2
963 , P_Attribute10 IN VARCHAR2
964 , P_Attribute11 IN VARCHAR2
965 , P_Attribute12 IN VARCHAR2
966 , P_Attribute13 IN VARCHAR2
967 , P_Attribute14 IN VARCHAR2
968 , P_Attribute15 IN VARCHAR2
969 , p_line_number_tbl IN NUMBER_TBL
970 , p_from_org_id_tbl IN NUMBER_TBL
971 , p_from_organization_id_tbl IN NUMBER_TBL
972 , p_to_org_id_tbl IN NUMBER_TBL
973 , p_to_organization_id_tbl IN NUMBER_TBL
974 , P_LINE_Attribute_Category_tbl IN VARCHAR2_tbl
975 , P_LINE_Attribute1_tbl IN VARCHAR2_tbl
976 , P_LINE_Attribute2_tbl IN VARCHAR2_tbl
977 , P_LINE_Attribute3_tbl IN VARCHAR2_tbl
978 , P_LINE_Attribute4_tbl IN VARCHAR2_tbl
979 , P_LINE_Attribute5_tbl IN VARCHAR2_tbl
980 , P_LINE_Attribute6_tbl IN VARCHAR2_tbl
981 , P_LINE_Attribute7_tbl IN VARCHAR2_tbl
982 , P_LINE_Attribute8_tbl IN VARCHAR2_tbl
983 , P_LINE_Attribute9_tbl IN VARCHAR2_tbl
984 , P_LINE_Attribute10_tbl IN VARCHAR2_tbl
985 , P_LINE_Attribute11_tbl IN VARCHAR2_tbl
986 , P_LINE_Attribute12_tbl IN VARCHAR2_tbl
987 , P_LINE_Attribute13_tbl IN VARCHAR2_tbl
988 , P_LINE_Attribute14_tbl IN VARCHAR2_tbl
989 , P_LINE_Attribute15_tbl IN VARCHAR2_tbl
990 , P_Ship_Organization_Id_tbl IN NUMBER_tbl
991 , P_Sell_Organization_Id_tbl IN NUMBER_tbl
992 , P_Vendor_Id_tbl IN NUMBER_tbl
993 , P_Vendor_Site_Id_tbl IN NUMBER_tbl
994 , P_Customer_Id_tbl IN NUMBER_tbl
995 , P_Address_Id_tbl IN NUMBER_tbl
996 , P_Customer_Site_Id_tbl IN NUMBER_tbl
997 , P_Cust_Trx_Type_Id_tbl IN NUMBER_tbl
998 , P_IC_Attribute_Category_tbl IN VARCHAR2_tbl
999 , P_IC_Attribute1_tbl IN VARCHAR2_tbl
1000 , P_IC_Attribute2_tbl IN VARCHAR2_tbl
1001 , P_IC_Attribute3_tbl IN VARCHAR2_tbl
1002 , P_IC_Attribute4_tbl IN VARCHAR2_tbl
1003 , P_IC_Attribute5_tbl IN VARCHAR2_tbl
1004 , P_IC_Attribute6_tbl IN VARCHAR2_tbl
1005 , P_IC_Attribute7_tbl IN VARCHAR2_tbl
1006 , P_IC_Attribute8_tbl IN VARCHAR2_tbl
1007 , P_IC_Attribute9_tbl IN VARCHAR2_tbl
1008 , P_IC_Attribute10_tbl IN VARCHAR2_tbl
1009 , P_IC_Attribute11_tbl IN VARCHAR2_tbl
1010 , P_IC_Attribute12_tbl IN VARCHAR2_tbl
1011 , P_IC_Attribute13_tbl IN VARCHAR2_tbl
1012 , P_IC_Attribute14_tbl IN VARCHAR2_tbl
1013 , P_IC_Attribute15_tbl IN VARCHAR2_tbl
1014 , P_Revalue_Average_Flag_tbl IN VARCHAR2_tbl
1015 , P_Freight_Code_Comb_Id_tbl IN NUMBER_tbl
1016 , p_inv_currency_code_tbl IN NUMBER_tbl
1017 , P_IC_COGS_Acct_Id_tbl IN NUMBER_tbl
1018 , P_Inv_Accrual_Acct_Id_tbl IN NUMBER_tbl
1019 , P_Exp_Accrual_Acct_Id_tbl IN NUMBER_tbl
1020 ) IS
1021
1022 l_lines_tab INV_TRANSACTION_FLOW_PVT.trx_flow_lines_tab;
1023 l_line_number_tbl NUMBER_TBL;
1024 l_header_id NUMBER := NULL;
1025 l_line_number NUMBER := NULL;
1026 l_ref_date DATE := Sysdate;
1027 l_return_status VARCHAR2(1) := NULL;
1028 l_msg_data VARCHAR2(2000):= NULL;
1029 l_msg_count NUMBER := NULL;
1030 l_ic_rowid VARCHAR2(2000);
1031 l_valid VARCHAR2(1) := NULL;
1032 l_api_version_number CONSTANT NUMBER := 1.0;
1033 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRANSACTION_FLOW';
1034 BEGIN
1035
1036 x_return_status := G_RET_STS_SUCCESS;
1037 x_msg_data := null;
1038 x_msg_count := 0;
1039
1040 -- Standard call to check for call compatibility
1041 IF NOT FND_API.Compatible_API_Call(
1042 l_api_version_number
1043 , p_api_version
1044 , l_api_name
1045 , G_PKG_NAME)
1046 THEN
1047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1048 END IF;
1049
1050
1051 -- Initialize message list.
1052 IF fnd_api.to_boolean(p_init_msg_list) THEN
1053 fnd_msg_pub.initialize;
1054 END IF;
1055
1056 SELECT mtl_transaction_flow_headers_s.NEXTVAL
1057 INTO l_header_id FROM dual;
1058
1059 l_line_number := 0;
1060
1061 SAVEPOINT CREATE_TRANSACTION_FLOW;
1062
1063 FOR i IN 1..p_line_number_tbl.COUNT LOOP
1064
1065 l_line_number := l_line_number + 1;
1066 l_line_number_tbl(i) := l_line_number;
1067
1068 l_lines_tab(i).HEADER_ID := l_header_id;
1069 l_lines_tab(i).LINE_NUMBER := l_line_number;
1070 l_lines_tab(i).FROM_ORG_ID := p_from_org_id_tbl(i);
1071 l_lines_tab(i).FROM_ORGANIZATION_ID := p_from_organization_id_tbl(i);
1072 l_lines_tab(i).TO_ORG_ID := p_to_org_id_tbl(i) ;
1073 l_lines_tab(i).TO_ORGANIZATION_ID := p_to_organization_id_tbl(i);
1074 l_lines_tab(i).ATTRIBUTE_CATEGORY := p_line_attribute_category_tbl(i);
1075 l_lines_tab(i).ATTRIBUTE1 := p_line_attribute1_tbl(i);
1076 l_lines_tab(i).ATTRIBUTE2 := p_line_attribute2_tbl(i);
1077 l_lines_tab(i).ATTRIBUTE3 := p_line_attribute3_tbl(i);
1078 l_lines_tab(i).ATTRIBUTE4 := p_line_attribute4_tbl(i);
1079 l_lines_tab(i).ATTRIBUTE5 := p_line_attribute5_tbl(i);
1080 l_lines_tab(i).ATTRIBUTE6 := p_line_attribute6_tbl(i);
1081 l_lines_tab(i).ATTRIBUTE7 := p_line_attribute7_tbl(i);
1082 l_lines_tab(i).ATTRIBUTE8 := p_line_attribute8_tbl(i);
1083 l_lines_tab(i).ATTRIBUTE9 := p_line_attribute9_tbl(i);
1084 l_lines_tab(i).ATTRIBUTE10 := p_line_attribute10_tbl(i);
1085 l_lines_tab(i).ATTRIBUTE11 := p_line_attribute11_tbl(i);
1086 l_lines_tab(i).ATTRIBUTE12 := p_line_attribute12_tbl(i);
1087 l_lines_tab(i).ATTRIBUTE13 := p_line_attribute13_tbl(i);
1088 l_lines_tab(i).ATTRIBUTE14 := p_line_attribute14_tbl(i);
1089 l_lines_tab(i).ATTRIBUTE15 := p_line_attribute15_tbl(i);
1090
1091 DECLARE
1092 vDummy VARCHAR2(30);
1093 BEGIN
1094
1095 SELECT 'ic_relation_exists' INTO vDummy
1096 FROM MTL_INTERCOMPANY_PARAMETERS
1097 WHERE ship_organization_id = p_from_org_id_tbl(i)
1098 AND sell_organization_id = p_to_org_id_tbl(i)
1099 AND flow_type = p_flow_type;
1100
1101 IF (g_debug=1) THEN
1102 print_debug('IC RELATION ALREADY EXISTS HENCE SKIP THIS RECORD, Ship Org=' || p_ship_organization_id_tbl(i) || ', Sell Org=' || p_Sell_Organization_Id_tbl(i));
1103 END IF;
1104
1105 EXCEPTION
1106 WHEN NO_DATA_FOUND THEN
1107
1108 IF (g_debug=1) THEN
1109 print_debug('NO IC RELATION EXISTS HENCE GO AHEAD AND CREATE, Ship Org=' || p_ship_organization_id_tbl(i) || ', Sell Org=' || p_Sell_Organization_Id_tbl(i));
1110 END IF;
1111
1112 inv_transaction_flow_pub.validate_ic_relation_rec
1113 (x_return_status => l_return_status,
1114 x_msg_data => l_msg_data,
1115 x_msg_count => l_msg_count,
1116 x_valid => l_valid,
1117 p_api_version => p_api_version,
1118 p_init_msg_list => G_FALSE,
1119 p_ship_organization_id => p_from_org_id_tbl(i),
1120 p_sell_organization_id => p_to_org_id_tbl(i),
1121 p_vendor_id => p_vendor_id_tbl(i),
1122 p_vendor_site_id => p_vendor_site_id_tbl(i),
1123 p_customer_id => p_customer_id_tbl(i),
1124 p_address_id => p_address_id_tbl(i),
1125 p_customer_site_id => p_customer_site_id_tbl(i),
1126 p_cust_trx_type_id => p_cust_trx_type_id_tbl(i),
1127 p_attribute_category => p_ic_attribute_category_tbl(i),
1128 p_attribute1 => p_ic_attribute1_tbl(i),
1129 p_attribute2 => p_ic_attribute2_tbl(i),
1130 p_attribute3 => p_ic_attribute3_tbl(i),
1131 p_attribute4 => p_ic_attribute4_tbl(i),
1132 p_attribute5 => p_ic_attribute5_tbl(i),
1133 p_attribute6 => p_ic_attribute6_tbl(i),
1134 p_attribute7 => p_ic_attribute7_tbl(i),
1135 p_attribute8 => p_ic_attribute8_tbl(i),
1136 p_attribute9 => p_ic_attribute9_tbl(i),
1137 p_attribute10 => p_ic_attribute10_tbl(i),
1138 p_attribute11 => p_ic_attribute11_tbl(i),
1139 p_attribute12 => p_ic_attribute12_tbl(i),
1140 p_attribute13 => p_ic_attribute13_tbl(i),
1141 p_attribute14 => p_ic_attribute14_tbl(i),
1142 p_attribute15 => p_ic_attribute15_tbl(i),
1143 p_revalue_average_flag => p_revalue_average_flag_tbl(i),
1144 p_freight_code_combination_id => p_freight_code_comb_id_tbl(i),
1145 p_inv_currency_code => p_inv_currency_code_tbl(i),
1146 p_flow_type => p_flow_type,
1147 p_intercompany_cogs_account_id => p_IC_COGS_Acct_Id_tbl(i),
1148 p_inventory_accrual_account_id => p_Inv_Accrual_Acct_Id_tbl(i) ,
1149 p_expense_accrual_account_id => p_Exp_Accrual_Acct_Id_tbl(i)
1150 );
1151
1152 IF (g_debug=1) THEN
1153 print_debug('After validation of IC RELATION , Ship Org=' || p_ship_organization_id_tbl(i) || ', Sell Org=' || p_Sell_Organization_Id_tbl(i) || ', Return Status=' || l_return_status);
1154 END IF;
1155
1156 IF l_return_status = g_ret_sts_error THEN
1157 RAISE fnd_api.g_exc_error;
1158 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1159 RAISE fnd_api.g_exc_unexpected_error;
1160
1161 ELSIF l_valid = g_true THEN
1162
1163 IF (g_debug=1) THEN
1164 print_debug('Before MTL_IC_PARAMETERS_PKG.INSERT_ROW call...');
1165 END IF;
1166
1167 MTL_IC_PARAMETERS_PKG.INSERT_ROW
1168 (X_Rowid => l_ic_rowid,
1169 X_Ship_Organization_Id => p_from_org_id_tbl(i),
1170 X_Sell_Organization_Id => p_to_org_id_tbl(i),
1171 X_Last_Update_Date => Sysdate,
1172 X_Last_Updated_By => FND_GLOBAL.user_id,
1173 X_Creation_Date => Sysdate,
1174 X_Created_By => FND_GLOBAL.user_id,
1175 X_Last_Update_Login => fnd_global.login_id,
1176 X_Vendor_Id => p_vendor_id_tbl(i),
1177 X_Vendor_Site_Id => p_vendor_site_id_tbl(i),
1178 X_Customer_Id => p_customer_id_tbl(i),
1179 X_Address_Id => p_address_id_tbl(i),
1180 X_Customer_Site_Id => p_customer_site_id_tbl(i),
1181 X_Cust_Trx_Type_Id => p_cust_trx_type_id_tbl(i),
1182 X_Attribute_Category => p_ic_attribute_category_tbl(i),
1183 X_Attribute1 => p_ic_attribute1_tbl(i),
1184 X_Attribute2 => p_ic_attribute2_tbl(i),
1185 X_Attribute3 => p_ic_attribute3_tbl(i),
1186 X_Attribute4 => p_ic_attribute4_tbl(i),
1187 X_Attribute5 => p_ic_attribute5_tbl(i),
1188 X_Attribute6 => p_ic_attribute6_tbl(i),
1189 X_Attribute7 => p_ic_attribute7_tbl(i),
1190 X_Attribute8 => p_ic_attribute8_tbl(i),
1191 X_Attribute9 => p_ic_attribute9_tbl(i),
1192 X_Attribute10 => p_ic_attribute10_tbl(i),
1193 X_Attribute11 => p_ic_attribute11_tbl(i),
1194 X_Attribute12 => p_ic_attribute12_tbl(i),
1195 X_Attribute13 => p_ic_attribute13_tbl(i),
1196 X_Attribute14 => p_ic_attribute14_tbl(i),
1197 X_Attribute15 => p_ic_attribute15_tbl(i),
1198 X_Revalue_Average_Flag => p_revalue_average_flag_tbl(i),
1199 X_Freight_Code_Combination_Id => p_freight_code_comb_id_tbl(i),
1200 X_Inv_Currency_Code => p_inv_currency_code_tbl(i),
1201 X_Flow_Type => p_flow_type,
1202 X_Intercompany_COGS_Account_Id => p_IC_COGS_Acct_Id_tbl(i) ,
1203 X_Inventory_Accrual_Account_Id => p_Inv_Accrual_Acct_Id_tbl(i) ,
1204 X_Expense_Accrual_Account_Id => p_Exp_Accrual_Acct_Id_tbl(i));
1205
1206 IF (g_debug=1) THEN
1207 print_debug('After MTL_IC_PARAMETERS_PKG.INSERT_ROW call...');
1208 END IF;
1209
1210 END IF;
1211 END;
1212 END LOOP;
1213
1214 l_return_status := NULL;
1215 l_msg_count := NULL;
1216 l_msg_data := NULL;
1217
1218 IF (g_debug=1) THEN
1219 print_debug('Before inv_transaction_flow_pvt.create_ic_transaction_flow call...');
1220 END IF;
1221
1222 inv_transaction_flow_pvt.create_ic_transaction_flow
1223 (x_return_status => l_return_status,
1224 x_msg_count => l_msg_count,
1225 x_msg_data => l_msg_data,
1226 p_header_id => l_header_id,
1227 p_commit => false,
1228 p_validation_level => fnd_api.g_valid_level_full,
1229 p_start_org_id => p_start_org_id,
1230 p_end_org_id => p_end_org_id,
1231 p_flow_type => p_flow_type,
1232 p_organization_id => p_organization_id,
1233 p_qualifier_code => p_qualifier_code,
1234 p_qualifier_value_id => p_qualifier_value_id,
1235 p_asset_item_pricing_option => p_asset_item_pricing_option,
1236 p_expense_item_pricing_option => p_expense_item_pricing_option,
1237 p_start_date => p_start_date,
1238 p_end_date => p_end_date,
1239 p_new_accounting_flag => p_new_accounting_flag,
1240 p_attribute_category => p_attribute_category,
1241 p_attribute1 => p_attribute1,
1242 p_attribute2 => p_attribute2,
1243 p_attribute3 => p_attribute3,
1244 p_attribute4 => p_attribute4,
1245 p_attribute5 => p_attribute5,
1246 p_attribute6 => p_attribute6,
1247 p_attribute7 => p_attribute7,
1248 p_attribute8 => p_attribute8,
1249 p_attribute9 => p_attribute9,
1250 p_attribute10 => p_attribute10,
1251 p_attribute11 => p_attribute11,
1252 p_attribute12 => p_attribute12,
1253 p_attribute13 => p_attribute13,
1254 p_attribute14 => p_attribute14,
1255 p_attribute15 => p_attribute15,
1256 p_ref_date => l_ref_date,
1257 p_lines_tab => l_lines_tab
1258 );
1259
1260 IF (g_debug=1) THEN
1261 print_debug('After inv_transaction_flow_pvt.create_ic_transaction_flow call... Return Status=' || l_return_status);
1262 END IF;
1263
1264 IF l_return_status = fnd_api.g_ret_sts_success THEN
1265 x_header_id := l_header_id;
1266 x_line_number_tbl := l_line_number_tbl;
1267 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1268 RAISE fnd_api.g_exc_error;
1269 ELSE
1270 RAISE fnd_api.g_exc_unexpected_error;
1271 END IF;
1272
1273 EXCEPTION
1274 WHEN FND_API.G_EXC_ERROR THEN
1275 ROLLBACK TO create_transaction_flow;
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277 x_header_id := NULL;
1278 x_line_number_tbl.DELETE;
1279 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1280
1281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1282 ROLLBACK TO create_transaction_flow;
1283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1284 x_header_id := NULL;
1285 x_line_number_tbl.DELETE;
1286 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1287 WHEN OTHERS THEN
1288 ROLLBACK TO create_transaction_flow;
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1290 x_header_id := NULL;
1291 x_line_number_tbl.DELETE;
1292
1293 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1294 FND_MSG_PUB.Add_Exc_Msg
1295 ( G_PACKAGE_NAME, 'CREATE_TRANSACTION_FLOW');
1296 end if;
1297
1298 END CREATE_TRANSACTION_FLOW;
1299
1300 /*========================================================================================================
1301 * Procedure: Update_Transaction_Flow()
1302 *
1303 * Description:
1304 * This API is used to update the transaction flow. Once a transaction flow is created, user can only
1305 * update the start date and the end date and desc flex field attributes of headers and lines and the Ic relation
1306 * defined.
1307 *
1308 *
1309 *
1310 *========================================================================================================*/
1311
1312 PROCEDURE update_transaction_flow
1313 (
1314 x_return_status OUT NOCOPY VARCHAR2
1315 , x_msg_data OUT NOCOPY VARCHAR2
1316 , x_msg_count OUT NOCOPY NUMBER
1317 , p_api_version IN NUMBER
1318 , p_init_msg_list IN VARCHAR2
1319 , p_validation_level IN NUMBER
1320 , p_header_id IN NUMBER
1321 , p_flow_type IN NUMBER
1322 , p_start_date IN DATE
1323 , p_end_date IN DATE
1324 , P_Attribute_Category IN VARCHAR2
1325 , P_Attribute1 IN VARCHAR2
1326 , P_Attribute2 IN VARCHAR2
1327 , P_Attribute3 IN VARCHAR2
1328 , P_Attribute4 IN VARCHAR2
1329 , P_Attribute5 IN VARCHAR2
1330 , P_Attribute6 IN VARCHAR2
1331 , P_Attribute7 IN VARCHAR2
1332 , P_Attribute8 IN VARCHAR2
1333 , P_Attribute9 IN VARCHAR2
1334 , P_Attribute10 IN VARCHAR2
1335 , P_Attribute11 IN VARCHAR2
1336 , P_Attribute12 IN VARCHAR2
1337 , P_Attribute13 IN VARCHAR2
1338 , P_Attribute14 IN VARCHAR2
1339 , P_Attribute15 IN VARCHAR2
1340 , p_line_number_tbl IN NUMBER_TBL
1341 , P_LINE_Attribute_Category_tbl IN VARCHAR2_tbl
1342 , P_LINE_Attribute1_tbl IN VARCHAR2_tbl
1343 , P_LINE_Attribute2_tbl IN VARCHAR2_tbl
1344 , P_LINE_Attribute3_tbl IN VARCHAR2_tbl
1345 , P_LINE_Attribute4_tbl IN VARCHAR2_tbl
1346 , P_LINE_Attribute5_tbl IN VARCHAR2_tbl
1347 , P_LINE_Attribute6_tbl IN VARCHAR2_tbl
1348 , P_LINE_Attribute7_tbl IN VARCHAR2_tbl
1349 , P_LINE_Attribute8_tbl IN VARCHAR2_tbl
1350 , P_LINE_Attribute9_tbl IN VARCHAR2_tbl
1351 , P_LINE_Attribute10_tbl IN VARCHAR2_tbl
1352 , P_LINE_Attribute11_tbl IN VARCHAR2_tbl
1353 , P_LINE_Attribute12_tbl IN VARCHAR2_tbl
1354 , P_LINE_Attribute13_tbl IN VARCHAR2_tbl
1355 , P_LINE_Attribute14_tbl IN VARCHAR2_tbl
1356 , P_LINE_Attribute15_tbl IN VARCHAR2_tbl
1357 , P_Ship_Organization_Id_tbl IN NUMBER_tbl
1358 , P_Sell_Organization_Id_tbl IN NUMBER_tbl
1359 , P_Vendor_Id_tbl IN NUMBER_tbl
1360 , P_Vendor_Site_Id_tbl IN NUMBER_tbl
1361 , P_Customer_Id_tbl IN NUMBER_tbl
1362 , P_Address_Id_tbl IN NUMBER_tbl
1363 , P_Customer_Site_Id_tbl IN NUMBER_tbl
1364 , P_Cust_Trx_Type_Id_tbl IN NUMBER_tbl
1365 , P_IC_Attribute_Category_tbl IN VARCHAR2_tbl
1366 , P_IC_Attribute1_tbl IN VARCHAR2_tbl
1367 , P_IC_Attribute2_tbl IN VARCHAR2_tbl
1368 , P_IC_Attribute3_tbl IN VARCHAR2_tbl
1369 , P_IC_Attribute4_tbl IN VARCHAR2_tbl
1370 , P_IC_Attribute5_tbl IN VARCHAR2_tbl
1371 , P_IC_Attribute6_tbl IN VARCHAR2_tbl
1372 , P_IC_Attribute7_tbl IN VARCHAR2_tbl
1373 , P_IC_Attribute8_tbl IN VARCHAR2_tbl
1374 , P_IC_Attribute9_tbl IN VARCHAR2_tbl
1375 , P_IC_Attribute10_tbl IN VARCHAR2_tbl
1376 , P_IC_Attribute11_tbl IN VARCHAR2_tbl
1377 , P_IC_Attribute12_tbl IN VARCHAR2_tbl
1378 , P_IC_Attribute13_tbl IN VARCHAR2_tbl
1379 , P_IC_Attribute14_tbl IN VARCHAR2_tbl
1380 , P_IC_Attribute15_tbl IN VARCHAR2_tbl
1381 , P_Revalue_Average_Flag_tbl IN VARCHAR2_tbl
1382 , P_Freight_Code_Comb_Id_tbl IN NUMBER_tbl
1383 , p_inv_currency_code_tbl IN NUMBER_tbl
1384 , P_IC_COGS_Acct_Id_tbl IN NUMBER_tbl
1385 , P_Inv_Accrual_Acct_Id_tbl IN NUMBER_tbl
1386 , P_Exp_Accrual_Acct_Id_tbl IN NUMBER_tbl
1387 ) IS
1388
1389 l_return_status VARCHAR2(1) := NULL;
1390 l_msg_data VARCHAR2(2000):= NULL;
1391 l_msg_count NUMBER := NULL;
1392 l_api_version_number CONSTANT NUMBER := 1.0;
1393 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRANSACTION_FLOW';
1394 BEGIN
1395 x_return_status := G_RET_STS_SUCCESS;
1396 x_msg_data := null;
1397 x_msg_count := 0;
1398
1399 SAVEPOINT UPDATE_TRANSACTION_FLOW;
1400
1401 -- Standard call to check for call compatibility
1402 IF NOT FND_API.Compatible_API_Call(
1403 l_api_version_number
1404 , p_api_version
1405 , l_api_name
1406 , G_PKG_NAME)
1407 THEN
1408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1409 END IF;
1410
1411
1412 -- Initialize message list.
1413 IF fnd_api.to_boolean(p_init_msg_list) THEN
1414 fnd_msg_pub.initialize;
1415 END IF;
1416
1417 IF (g_debug=1) THEN
1418 print_debug('Before inv_transaction_flow_pub.update_transaction_flow_header call...');
1419 END IF;
1420
1421 inv_transaction_flow_pub.update_transaction_flow_header
1422 (x_return_status => l_return_status,
1423 x_msg_count => l_msg_count,
1424 x_msg_data => l_msg_data,
1425 p_api_version => p_api_version,
1426 p_init_msg_list => G_FALSE,
1427 p_header_id => p_header_id,
1428 p_start_date => p_start_date,
1429 p_end_date => p_end_date,
1430 p_attribute_category => p_attribute_category,
1431 p_attribute1 => p_attribute1,
1432 p_attribute2 => p_attribute2,
1433 p_attribute3 => p_attribute3,
1434 p_attribute4 => p_attribute4,
1435 p_attribute5 => p_attribute5,
1436 p_attribute6 => p_attribute6,
1437 p_attribute7 => p_attribute7,
1438 p_attribute8 => p_attribute8,
1439 p_attribute9 => p_attribute9,
1440 p_attribute10 => p_attribute10,
1441 p_attribute11 => p_attribute11,
1442 p_attribute12 => p_attribute12,
1443 p_attribute13 => p_attribute13,
1444 p_attribute14 => p_attribute14,
1445 p_attribute15 => p_attribute15
1446 );
1447
1448 IF (g_debug=1) THEN
1449 print_debug('After inv_transaction_flow_pub.update_transaction_flow_header call... Return Status=' || l_return_status);
1450 END IF;
1451
1452 IF x_return_status = fnd_api.g_ret_sts_error THEN
1453 RAISE fnd_api.g_exc_error;
1454 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1455 RAISE fnd_api.g_exc_unexpected_error;
1456 END IF;
1457
1458 l_return_status:=NULL;
1459 l_msg_data:=NULL;
1460 l_msg_count:=NULL;
1461
1462 FOR i IN 1..p_line_number_tbl.COUNT LOOP
1463
1464 IF (g_debug=1) THEN
1465 print_debug('Before inv_transaction_flow_pub.update_transaction_flow_line call...');
1466 END IF;
1467
1468 inv_transaction_flow_pub.update_transaction_flow_line
1469 ( x_return_status => l_return_status
1470 , x_msg_data => l_msg_data
1471 , x_msg_count => l_msg_count
1472 , p_api_version => p_api_version
1473 , p_init_msg_list => G_FALSE
1474 , p_header_id => p_header_id
1475 , p_line_number => p_line_number_tbl(i)
1476 , p_attribute_category => p_line_attribute_category_tbl(i)
1477 , p_attribute1 => p_line_attribute1_tbl(i)
1478 , p_attribute2 => p_line_attribute2_tbl(i)
1479 , p_attribute3 => p_line_attribute3_tbl(i)
1480 , p_attribute4 => p_line_attribute4_tbl(i)
1481 , p_attribute5 => p_line_attribute5_tbl(i)
1482 , p_attribute6 => p_line_attribute6_tbl(i)
1483 , p_attribute7 => p_line_attribute7_tbl(i)
1484 , p_attribute8 => p_line_attribute8_tbl(i)
1485 , p_attribute9 => p_line_attribute9_tbl(i)
1486 , p_attribute10 => p_line_attribute10_tbl(i)
1487 , p_attribute11 => p_line_attribute11_tbl(i)
1488 , p_attribute12 => p_line_attribute12_tbl(i)
1489 , p_attribute13 => p_line_attribute13_tbl(i)
1490 , p_attribute14 => p_line_attribute14_tbl(i)
1491 , p_attribute15 => p_line_attribute15_tbl(i)
1492 );
1493
1494 IF (g_debug=1) THEN
1495 print_debug('After inv_transaction_flow_pub.update_transaction_flow_line call... Return Status=' || l_return_status);
1496 END IF;
1497
1498 IF l_return_status = g_ret_sts_error THEN
1499 RAISE fnd_api.g_exc_error;
1500 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1501 RAISE fnd_api.g_exc_unexpected_error;
1502 END IF;
1503 l_return_status:=NULL;
1504 l_msg_data:=NULL;
1505 l_msg_count:=NULL;
1506
1507 IF (g_debug=1) THEN
1508 print_debug('Before inv_transaction_flow_pub.update_ic_relation call...');
1509 END IF;
1510
1511 inv_transaction_flow_pub.update_ic_relation
1512 (x_return_status => l_return_status,
1513 x_msg_data => l_msg_data,
1514 x_msg_count => l_msg_count,
1515 p_api_version => p_api_version,
1516 p_init_msg_list => G_FALSE,
1517 p_Ship_Organization_Id => p_ship_organization_id_tbl(i),
1518 p_Sell_Organization_Id => p_Sell_Organization_Id_tbl(i),
1519 p_Vendor_Id => p_vendor_id_tbl(i),
1520 p_Vendor_Site_Id => p_vendor_site_id_tbl(i),
1521 p_Customer_Id => p_customer_id_tbl(i) ,
1522 p_Address_Id => p_address_id_tbl(i) ,
1523 p_Customer_Site_Id => p_customer_site_id_tbl(i) ,
1524 p_Cust_Trx_Type_Id => p_cust_trx_type_id_tbl(i) ,
1525 p_Attribute_Category => p_ic_attribute_category_tbl(i) ,
1526 p_Attribute1 => p_ic_attribute1_tbl(i) ,
1527 p_Attribute2 => p_ic_attribute2_tbl(i) ,
1528 p_Attribute3 => p_ic_attribute3_tbl(i) ,
1529 p_Attribute4 => p_ic_attribute4_tbl(i) ,
1530 p_Attribute5 => p_ic_attribute5_tbl(i) ,
1531 p_Attribute6 => p_ic_attribute6_tbl(i) ,
1532 p_Attribute7 => p_ic_attribute7_tbl(i) ,
1533 p_Attribute8 => p_ic_attribute8_tbl(i) ,
1534 p_Attribute9 => p_ic_attribute9_tbl(i) ,
1535 p_Attribute10 => p_ic_attribute10_tbl(i) ,
1536 p_Attribute11 => p_ic_attribute11_tbl(i) ,
1537 p_Attribute12 => p_ic_attribute12_tbl(i) ,
1538 p_Attribute13 => p_ic_attribute13_tbl(i) ,
1539 p_Attribute14 => p_ic_attribute14_tbl(i) ,
1540 p_Attribute15 => p_ic_attribute15_tbl(i) ,
1541 p_Revalue_Average_Flag => p_revalue_average_flag_tbl(i) ,
1542 p_Freight_Code_Combination_Id =>p_freight_code_comb_id_tbl(i),
1543 p_inv_currency_code => p_inv_currency_code_tbl(i),
1544 p_Flow_Type =>p_flow_type,
1545 p_Intercompany_COGS_Account_Id =>p_IC_COGS_Acct_Id_tbl(i),
1546 p_Inventory_Accrual_Account_Id => p_Inv_Accrual_Acct_Id_tbl(i),
1547 p_Expense_Accrual_Account_Id => p_Exp_Accrual_Acct_Id_tbl(i)
1548
1549 );
1550
1551 IF (g_debug=1) THEN
1552 print_debug('After inv_transaction_flow_pub.update_ic_relation call...Return Status=' || l_return_status);
1553 END IF;
1554
1555 IF l_return_status = g_ret_sts_error THEN
1556 RAISE fnd_api.g_exc_error;
1557 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1558 RAISE fnd_api.g_exc_unexpected_error;
1559 END IF;
1560 END LOOP;
1561
1562
1563
1564 EXCEPTION
1565 WHEN FND_API.G_EXC_ERROR THEN
1566 ROLLBACK TO UPDATE_TRANSACTION_FLOW;
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1569
1570 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1571 ROLLBACK TO UPDATE_TRANSACTION_FLOW;
1572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1573 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1574 WHEN OTHERS THEN
1575 ROLLBACK TO UPDATE_TRANSACTION_FLOW;
1576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1577 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1578 FND_MSG_PUB.Add_Exc_Msg
1579 ( G_PACKAGE_NAME, 'CREATE_TRANSACTION_FLOW');
1580 end if;
1581
1582 END UPDATE_TRANSACTION_FLOW;
1583
1584 /*========================================================================================================*/
1585
1586
1587 PROCEDURE update_transaction_flow_header
1588 (X_return_status OUT NOCOPY VARCHAR2
1589 , x_msg_data OUT NOCOPY VARCHAR2
1590 , x_msg_count OUT NOCOPY NUMBER
1591 , p_api_version IN NUMBER
1592 , p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
1593 , p_header_id IN NUMBER
1594 , p_end_date IN DATE
1595 , p_start_date IN DATE
1596 , P_Attribute_Category IN VARCHAR2
1597 , P_Attribute1 IN VARCHAR2
1598 , P_Attribute2 IN VARCHAR2
1599 , P_Attribute3 IN VARCHAR2
1600 , P_Attribute4 IN VARCHAR2
1601 , P_Attribute5 IN VARCHAR2
1602 , P_Attribute6 IN VARCHAR2
1603 , P_Attribute7 IN VARCHAR2
1604 , P_Attribute8 IN VARCHAR2
1605 , P_Attribute9 IN VARCHAR2
1606 , P_Attribute10 IN VARCHAR2
1607 , P_Attribute11 IN VARCHAR2
1608 , P_Attribute12 IN VARCHAR2
1609 , P_Attribute13 IN VARCHAR2
1610 , P_Attribute14 IN VARCHAR2
1611 , P_Attribute15 IN VARCHAR2)
1612 IS
1613
1614
1615 l_ref_date DATE := Sysdate;
1616 l_return_status VARCHAR2(1) := NULL;
1617 l_msg_data VARCHAR2(2000):= NULL;
1618 l_msg_count NUMBER := NULL;
1619 l_lines_tab INV_TRANSACTION_FLOW_PVT.trx_flow_lines_tab;
1620 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1621 l_api_version_number CONSTANT NUMBER := 1.0;
1622 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRANSACTION_FLOW_HEADER';
1623 BEGIN
1624
1625 x_return_status := G_RET_STS_SUCCESS;
1626 x_msg_data := null;
1627 x_msg_count := 0;
1628
1629 -- Standard call to check for call compatibility
1630 IF NOT FND_API.compatible_api_call
1631 (l_api_version_number
1632 , p_api_version
1633 , l_api_name
1634 , G_PKG_NAME)
1635 THEN
1636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1637 END IF;
1638
1639 -- Initialize message list.
1640 IF fnd_api.to_boolean(p_init_msg_list) THEN
1641 fnd_msg_pub.initialize;
1642 END IF;
1643
1644
1645 SAVEPOINT UPDATE_TRANSACTION_FLOW_HEADER;
1646
1647 l_lines_tab.DELETE;
1648
1649 inv_transaction_flow_pvt.update_ic_txn_flow_hdr
1650 (x_return_status => l_return_status,
1651 x_msg_count => l_msg_count,
1652 x_msg_data => l_msg_data,
1653 p_header_id => p_header_id,
1654 p_commit => false,
1655 p_start_date => p_start_date,
1656 p_end_date => p_end_date,
1657 p_ref_date => l_ref_date,
1658 p_attribute_category => p_attribute_category,
1659 p_attribute1 => p_attribute1,
1660 p_attribute2 => p_attribute2,
1661 p_attribute3 => p_attribute3,
1662 p_attribute4 => p_attribute4,
1663 p_attribute5 => p_attribute5,
1664 p_attribute6 => p_attribute6,
1665 p_attribute7 => p_attribute7,
1666 p_attribute8 => p_attribute8,
1667 p_attribute9 => p_attribute9,
1668 p_attribute10 => p_attribute10,
1669 p_attribute11 => p_attribute11,
1670 p_attribute12 => p_attribute12,
1671 p_attribute13 => p_attribute13,
1672 p_attribute14 => p_attribute14,
1673 p_attribute15 => p_attribute15
1674 );
1675
1676 IF l_debug = 1 THEN
1677 print_debug('l_return_status from'||
1678 'inv_transaction_flow_pvt.update_ic_txn_flow_hdr'||
1679 x_return_status,l_api_name);
1680 END IF;
1681
1682 IF l_return_status = g_ret_sts_success THEN
1683 --Successful
1684 x_return_status := l_return_status;
1685 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1686 IF l_debug = 1 THEN
1687 print_debug('l_msg_data from'||
1688 'inv_transaction_flow_pvt.update_ic_txn_flow_hdr'||
1689 l_msg_data,l_api_name);
1690 END IF;
1691
1692 RAISE fnd_api.g_exc_error;
1693 ELSE
1694 IF l_debug = 1 THEN
1695 print_debug('l_msg_data from'||
1696 'inv_transaction_flow_pvt.update_ic_txn_flow_hdr'||
1697 l_msg_data,l_api_name);
1698 END IF;
1699 RAISE fnd_api.g_exc_unexpected_error;
1700 END IF;
1701
1702 EXCEPTION
1703 WHEN FND_API.G_EXC_ERROR THEN
1704 ROLLBACK TO UPDATE_TRANSACTION_FLOW_HEADER;
1705 x_return_status := FND_API.G_RET_STS_ERROR;
1706 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1707
1708 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1709 ROLLBACK TO UPDATE_TRANSACTION_FLOW_HEADER;
1710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1711 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1712 WHEN OTHERS THEN
1713 ROLLBACK TO UPDATE_TRANSACTION_FLOW_HEADER;
1714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1715
1716 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1717 FND_MSG_PUB.Add_Exc_Msg
1718 ( G_PACKAGE_NAME, 'UPDATE_TRANSACTION_FLOW_HEADER');
1719 end if;
1720 END Update_Transaction_Flow_Header;
1721
1722
1723
1724 PROCEDURE update_transaction_flow_line
1725 (x_return_status OUT NOCOPY VARCHAR2
1726 , x_msg_data OUT NOCOPY VARCHAR2
1727 , x_msg_count OUT NOCOPY VARCHAR2
1728 , p_api_version IN NUMBER
1729 , p_init_msg_list IN VARCHAR2 DEFAULT g_false
1730 , p_header_id IN NUMBER
1731 , p_line_number IN NUMBER
1732 , p_attribute_category IN VARCHAR2
1733 , p_attribute1 IN VARCHAR2
1734 , p_attribute2 IN VARCHAR2
1735 , p_attribute3 IN VARCHAR2
1736 , p_attribute4 IN VARCHAR2
1737 , p_attribute5 IN VARCHAR2
1738 , p_attribute6 IN VARCHAR2
1739 , p_attribute7 IN VARCHAR2
1740 , p_attribute8 IN VARCHAR2
1741 , p_attribute9 IN VARCHAR2
1742 , p_attribute10 IN VARCHAR2
1743 , p_attribute11 IN VARCHAR2
1744 , p_attribute12 IN VARCHAR2
1745 , p_attribute13 IN VARCHAR2
1746 , p_attribute14 IN VARCHAR2
1747 , p_attribute15 IN VARCHAR2
1748 )IS
1749
1750
1751 l_return_status VARCHAR2(1) := NULL;
1752 l_msg_data VARCHAR2(2000):= NULL;
1753 l_msg_count NUMBER := NULL;
1754 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1755 l_api_version_number CONSTANT NUMBER := 1.0;
1756 l_api_name CONSTANT VARCHAR2(30) := 'Update_Transaction_flow_line';
1757 BEGIN
1758
1759 x_return_status := G_RET_STS_SUCCESS;
1760 x_msg_data := null;
1761 x_msg_count := 0;
1762
1763 -- Standard call to check for call compatibility
1764 IF NOT FND_API.compatible_api_call
1765 (l_api_version_number
1766 , p_api_version
1767 , l_api_name
1768 , G_PKG_NAME)
1769 THEN
1770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1771 END IF;
1772
1773
1774 -- Initialize message list.
1775 IF fnd_api.to_boolean(p_init_msg_list) THEN
1776 fnd_msg_pub.initialize;
1777 END IF;
1778
1779
1780 SAVEPOINT UPDATE_TRANSACTION_FLOW_LINE;
1781
1782
1783
1784 inv_transaction_flow_pvt.update_ic_txn_flow_line
1785 (x_return_status => l_return_status,
1786 x_msg_count => l_msg_count,
1787 x_msg_data => l_msg_data,
1788 p_commit => FALSE,
1789 p_header_id => p_header_id,
1790 p_line_number => p_line_number,
1791 p_attribute_category => p_attribute_category,
1792 p_attribute1 => p_attribute1,
1793 p_attribute2 => p_attribute2,
1794 p_attribute3 => p_attribute3,
1795 p_attribute4 => p_attribute4,
1796 p_attribute5 => p_attribute5,
1797 p_attribute6 => p_attribute6,
1798 p_attribute7 => p_attribute7,
1799 p_attribute8 => p_attribute8,
1800 p_attribute9 => p_attribute9,
1801 p_attribute10 => p_attribute10,
1802 p_attribute11 => p_attribute11,
1803 p_attribute12 => p_attribute12,
1804 p_attribute13 => p_attribute13,
1805 p_attribute14 => p_attribute14,
1806 p_attribute15 => p_attribute15
1807 );
1808
1809 IF l_debug = 1 THEN
1810 print_debug('l_return_status from'||
1811 'inv_transaction_flow_pvt.update_ic_txn_flow_line'||
1812 x_return_status,l_api_name);
1813 END IF;
1814
1815 IF l_return_status = g_ret_sts_success THEN
1816 --Successful
1817 x_return_status := l_return_status;
1818 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1819 IF l_debug = 1 THEN
1820 print_debug('l_msg_data from'||
1821 'inv_transaction_flow_pvt.update_ic_txn_flow_line'||
1822 l_msg_data,l_api_name);
1823 END IF;
1824
1825 RAISE fnd_api.g_exc_error;
1826 ELSE
1827 IF l_debug = 1 THEN
1828 print_debug('l_msg_data from'||
1829 'inv_transaction_flow_pvt.update_ic_txn_flow_line'||
1830 l_msg_data,l_api_name);
1831 END IF;
1832 RAISE fnd_api.g_exc_unexpected_error;
1833 END IF;
1834
1835 EXCEPTION
1836 WHEN FND_API.G_EXC_ERROR THEN
1837 ROLLBACK TO update_transaction_flow_line;
1838 x_return_status := FND_API.G_RET_STS_ERROR;
1839 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1840
1841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1842 ROLLBACK TO update_transaction_flow_line;
1843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1844 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1845 WHEN OTHERS THEN
1846 ROLLBACK TO update_transaction_flow_line;
1847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1848
1849 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1850 FND_MSG_PUB.Add_Exc_Msg
1851 ( G_PACKAGE_NAME, 'UPDATE_TRANSACTION_FLOW');
1852 end if;
1853 END Update_Transaction_Flow_line;
1854
1855 PROCEDURE update_ic_relation
1856 (x_return_status OUT NOCOPY VARCHAR2,
1857 x_msg_data OUT NOCOPY VARCHAR2,
1858 x_msg_count OUT NOCOPY VARCHAR2,
1859 p_api_version IN NUMBER,
1860 p_init_msg_list IN VARCHAR2 DEFAULT g_false,
1861 p_Ship_Organization_Id IN NUMBER,
1862 p_Sell_Organization_Id IN NUMBER,
1863 p_Vendor_Id IN NUMBER,
1864 p_Vendor_Site_Id IN NUMBER,
1865 p_Customer_Id IN NUMBER,
1866 p_Address_Id IN NUMBER,
1867 p_Customer_Site_Id IN NUMBER,
1868 p_Cust_Trx_Type_Id IN NUMBER,
1869 p_Attribute_Category IN VARCHAR2,
1870 p_Attribute1 IN VARCHAR2,
1871 p_Attribute2 IN VARCHAR2,
1872 p_Attribute3 IN VARCHAR2,
1873 p_Attribute4 IN VARCHAR2,
1874 p_Attribute5 IN VARCHAR2,
1875 p_Attribute6 IN VARCHAR2,
1876 p_Attribute7 IN VARCHAR2,
1877 p_Attribute8 IN VARCHAR2,
1878 p_Attribute9 IN VARCHAR2,
1879 p_Attribute10 IN VARCHAR2,
1880 p_Attribute11 IN VARCHAR2,
1881 p_Attribute12 IN VARCHAR2,
1882 p_Attribute13 IN VARCHAR2,
1883 p_Attribute14 IN VARCHAR2,
1884 p_Attribute15 IN VARCHAR2,
1885 p_Revalue_Average_Flag IN VARCHAR2,
1886 p_Freight_Code_Combination_Id IN NUMBER,
1887 p_inv_currency_code IN NUMBER,
1888 p_Flow_Type IN NUMBER,
1889 p_Intercompany_COGS_Account_Id IN NUMBER,
1890 p_Inventory_Accrual_Account_Id IN NUMBER,
1891 p_Expense_Accrual_Account_Id IN NUMBER
1892 )IS
1893
1894 CURSOR ic_information(l_ship_organization_id NUMBER,
1895 l_sell_organization_id NUMBER,
1896 l_flow_type NUMBER)
1897 IS
1898 SELECT
1899 rowid,
1900 ship_organization_id,
1901 sell_organization_id,
1902 last_update_date,
1903 last_updated_by,
1904 creation_date,
1905 created_by,
1906 last_update_login,
1907 customer_id,
1908 address_id,
1909 customer_site_id,
1910 cust_trx_type_id,
1911 vendor_id,
1912 vendor_site_id,
1913 revalue_average_flag,
1914 attribute_category,
1915 attribute1,
1916 attribute2,
1917 attribute3,
1918 attribute4,
1919 attribute5,
1920 attribute6,
1921 attribute7,
1922 attribute8,
1923 attribute9,
1924 attribute10,
1925 attribute11,
1926 attribute12,
1927 attribute13,
1928 attribute14,
1929 attribute15,
1930 freight_code_combination_id,
1931 inv_currency_code,
1932 flow_type,
1933 intercompany_cogs_account_id,
1934 inventory_accrual_account_id,
1935 expense_accrual_account_id
1936 FROM
1937 mtl_intercompany_parameters
1938 WHERE
1939 ship_organization_id = l_ship_organization_id
1940 AND sell_organization_id = l_sell_organization_id
1941 AND flow_type = l_flow_type;
1942
1943 l_return_status VARCHAR2(1) := NULL;
1944 l_msg_data VARCHAR2(2000):= NULL;
1945 l_msg_count NUMBER := NULL;
1946
1947 l_valid VARCHAR2(1) := NULL;
1948
1949 l_rowid ROWID;
1950 l_ship_organization_id NUMBER := p_ship_organization_id;
1951 l_sell_organization_id NUMBER := p_sell_organization_id;
1952 l_customer_id NUMBER := p_customer_id;
1953 l_address_id NUMBER := p_address_id;
1954 l_customer_site_id NUMBER := p_customer_site_id;
1955 l_cust_trx_type_id NUMBER := p_cust_trx_type_id;
1956 l_vendor_id NUMBER := p_vendor_id;
1957 l_vendor_site_id NUMBER := p_vendor_site_id;
1958 l_freight_code_combination_id NUMBER := p_freight_code_combination_id;
1959 l_intercompany_cogs_account_id NUMBER := p_intercompany_cogs_account_id;
1960 l_inventory_accrual_account_id NUMBER := p_inventory_accrual_account_id;
1961 l_expense_accrual_account_id NUMBER := p_expense_accrual_account_id;
1962 l_chart_of_accounts_id NUMBER := NULL;
1963 l_ship_chart_of_accounts_id NUMBER := NULL;
1964
1965 l_api_version_number CONSTANT NUMBER := 1.0;
1966 l_api_name CONSTANT VARCHAR2(30) := 'Update_ic_relation';
1967 BEGIN
1968
1969 x_return_status := G_RET_STS_SUCCESS;
1970 x_msg_data := null;
1971 x_msg_count := 0;
1972
1973 -- Standard call to check for call compatibility
1974 IF NOT FND_API.compatible_api_call
1975 (l_api_version_number
1976 , p_api_version
1977 , l_api_name
1978 , G_PKG_NAME)
1979 THEN
1980 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1981 END IF;
1982
1983
1984 -- Initialize message list.
1985 IF fnd_api.to_boolean(p_init_msg_list) THEN
1986 fnd_msg_pub.initialize;
1987 END IF;
1988
1989 SAVEPOINT update_ic_relation;
1990
1991 inv_transaction_flow_pub.validate_ic_relation_rec
1992 (x_return_status => l_return_status,
1993 x_msg_data => l_msg_data,
1994 x_msg_count => l_msg_count,
1995 x_valid => l_valid,
1996 p_api_version => p_api_version,
1997 p_init_msg_list => G_FALSE,
1998 p_ship_organization_id => p_ship_organization_id,
1999 p_sell_organization_id => p_sell_organization_id,
2000 p_vendor_id => p_vendor_id,
2001 p_vendor_site_id => p_vendor_site_id,
2002 p_customer_id => p_customer_id,
2003 p_address_id => p_address_id,
2004 p_customer_site_id => p_customer_site_id,
2005 p_cust_trx_type_id => p_cust_trx_type_id,
2006 p_attribute_category => p_attribute_category,
2007 p_attribute1 => p_attribute1,
2008 p_attribute2 => p_attribute2,
2009 p_attribute3 => p_attribute3,
2010 p_attribute4 => p_attribute4,
2011 p_attribute5 => p_attribute5,
2012 p_attribute6 => p_attribute6,
2013 p_attribute7 => p_attribute7,
2014 p_attribute8 => p_attribute8,
2015 p_attribute9 => p_attribute9,
2016 p_attribute10 => p_attribute10,
2017 p_attribute11 => p_attribute11,
2018 p_attribute12 => p_attribute12,
2019 p_attribute13 => p_attribute13,
2020 p_attribute14 => p_attribute14,
2021 p_attribute15 => p_attribute15,
2022 p_revalue_average_flag => p_revalue_average_flag,
2023 p_freight_code_combination_id => p_freight_code_combination_id,
2024 p_inv_currency_code => p_inv_currency_code,
2025 p_flow_type => p_flow_type,
2026 p_intercompany_cogs_account_id => p_intercompany_cogs_account_id,
2027 p_inventory_accrual_account_id => p_inventory_accrual_account_id,
2028 p_expense_accrual_account_id => p_expense_accrual_account_id
2029 );
2030
2031 IF l_return_status = g_ret_sts_error THEN
2032 RAISE fnd_api.g_exc_error;
2033 ELSIF l_return_status = g_ret_sts_unexp_error THEN
2034 RAISE fnd_api.g_exc_unexpected_error;
2035 ELSIF l_valid = g_true THEN
2036
2037 FOR ic_information_rec IN ic_information(p_ship_organization_id,p_sell_organization_id,p_flow_type) LOOP
2038
2039 l_rowid := ic_information_rec.ROWID;
2040
2041 IF p_customer_id = g_miss_num THEN
2042 l_customer_id := ic_information_rec.customer_id;
2043 END IF;
2044
2045 IF p_address_id = g_miss_num THEN
2046 l_address_id := ic_information_rec.address_id;
2047 END IF;
2048
2049 IF p_customer_site_id = g_miss_num THEN
2050 l_customer_site_id := ic_information_rec.customer_site_id;
2051 END IF;
2052
2053 IF p_address_id = g_miss_num THEN
2054 l_address_id := ic_information_rec.address_id;
2055 END IF;
2056
2057 IF p_customer_site_id = g_miss_num THEN
2058 l_customer_site_id := ic_information_rec.customer_site_id;
2059 END IF;
2060
2061 IF p_cust_trx_type_id = g_miss_num THEN
2062 l_cust_trx_type_id := ic_information_rec.cust_trx_type_id;
2063 END IF;
2064
2065 IF p_vendor_id = g_miss_num THEN
2066 l_vendor_id := ic_information_rec.vendor_id;
2067 END IF;
2068
2069 IF p_vendor_site_id = g_miss_num THEN
2070 l_vendor_site_id := ic_information_rec.vendor_site_id;
2071 END IF;
2072
2073 IF P_FREIGHT_CODE_COMBINATION_ID = g_miss_num THEN
2074 l_freight_code_combination_id := ic_information_rec.freight_code_combination_id;
2075 END IF;
2076
2077 IF P_intercompany_cogs_account_id = g_miss_num THEN
2078 l_intercompany_cogs_account_id := ic_information_rec.intercompany_cogs_account_id;
2079 END IF;
2080
2081 IF P_inventory_accrual_account_id = g_miss_num THEN
2082 l_inventory_accrual_account_id := ic_information_rec.inventory_accrual_account_id;
2083 END IF;
2084
2085 IF P_expense_accrual_account_id = g_miss_num THEN
2086 l_expense_accrual_account_id := ic_information_rec.expense_accrual_account_id;
2087 END IF;
2088
2089 mtl_ic_parameters_pkg.update_row
2090 (x_rowid=> l_rowid,
2091 x_ship_organization_id=> p_ship_organization_id,
2092 x_sell_organization_id=> p_sell_organization_id,
2093 x_last_update_date=>sysdate,
2094 x_last_updated_by=> fnd_global.user_id,
2095 x_last_update_login=> fnd_global.login_id,
2096 x_vendor_id=> l_vendor_id,
2097 x_vendor_site_id=> l_vendor_site_id,
2098 x_customer_id=> l_customer_id,
2099 x_address_id=> l_address_id,
2100 x_customer_site_id=> l_customer_site_id,
2101 x_cust_trx_type_id=> l_cust_trx_type_id,
2102 x_attribute_category=> p_attribute_category,
2103 x_attribute1=> p_attribute1,
2104 x_attribute2=> p_attribute2,
2105 x_attribute3=> p_attribute3,
2106 x_attribute4=> p_attribute4,
2107 x_attribute5=> p_attribute5,
2108 x_attribute6=> p_attribute6,
2109 x_attribute7=> p_attribute7,
2110 x_attribute8=> p_attribute8,
2111 x_attribute9=> p_attribute9,
2112 x_attribute10=> p_attribute10,
2113 x_attribute11=> p_attribute11,
2114 x_attribute12=> p_attribute12,
2115 x_attribute13=> p_attribute13,
2116 x_attribute14=> p_attribute14,
2117 x_attribute15=> p_attribute15,
2118 x_revalue_average_flag=> p_revalue_average_flag,
2119 x_freight_code_combination_id=> l_freight_code_combination_id,
2120 x_inv_currency_code => p_inv_currency_Code,
2121 x_flow_type=> p_flow_type,
2122 x_intercompany_cogs_account_id=> l_intercompany_cogs_account_id,
2123 x_inventory_accrual_account_id=> l_inventory_accrual_account_id,
2124 x_expense_accrual_account_id=> l_expense_accrual_account_id
2125 );
2126 END LOOP;
2127 END IF;
2128
2129
2130 EXCEPTION
2131 WHEN FND_API.G_EXC_ERROR THEN
2132 ROLLBACK TO update_ic_relation;
2133 x_return_status := FND_API.G_RET_STS_ERROR;
2134 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2135
2136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2137 ROLLBACK TO update_ic_relation;
2138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2139 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2140 WHEN OTHERS THEN
2141 ROLLBACK TO update_ic_relation;
2142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2143
2144 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2145 FND_MSG_PUB.Add_Exc_Msg
2146 ( G_PACKAGE_NAME, 'UPDATE_TRANSACTION_FLOW');
2147 end if;
2148 END Update_IC_RELATION;
2149
2150
2151
2152 PROCEDURE validate_ic_relation_rec
2153 (x_return_status OUT NOCOPY VARCHAR2,
2154 x_msg_data OUT NOCOPY VARCHAR2,
2155 x_msg_count OUT NOCOPY VARCHAR2,
2156 x_valid OUT NOCOPY VARCHAR2,
2157 p_api_version IN NUMBER,
2158 p_init_msg_list IN VARCHAR2 DEFAULT g_false,
2159 p_Ship_Organization_Id IN NUMBER,
2160 p_Sell_Organization_Id IN NUMBER,
2161 p_Vendor_Id IN NUMBER,
2162 p_Vendor_Site_Id IN NUMBER,
2163 p_Customer_Id IN NUMBER,
2164 p_Address_Id IN NUMBER,
2165 p_Customer_Site_Id IN NUMBER,
2166 p_Cust_Trx_Type_Id IN NUMBER,
2167 p_Attribute_Category IN VARCHAR2,
2168 p_Attribute1 IN VARCHAR2,
2169 p_Attribute2 IN VARCHAR2,
2170 p_Attribute3 IN VARCHAR2,
2171 p_Attribute4 IN VARCHAR2,
2172 p_Attribute5 IN VARCHAR2,
2173 p_Attribute6 IN VARCHAR2,
2174 p_Attribute7 IN VARCHAR2,
2175 p_Attribute8 IN VARCHAR2,
2176 p_Attribute9 IN VARCHAR2,
2177 p_Attribute10 IN VARCHAR2,
2178 p_Attribute11 IN VARCHAR2,
2179 p_Attribute12 IN VARCHAR2,
2180 p_Attribute13 IN VARCHAR2,
2181 p_Attribute14 IN VARCHAR2,
2182 p_Attribute15 IN VARCHAR2,
2183 p_Revalue_Average_Flag IN VARCHAR2,
2184 p_Freight_Code_Combination_Id IN NUMBER,
2185 p_inv_currency_code IN NUMBER,
2186 p_Flow_Type IN NUMBER,
2187 p_Intercompany_COGS_Account_Id IN NUMBER,
2188 p_Inventory_Accrual_Account_Id IN NUMBER,
2189 p_Expense_Accrual_Account_Id IN NUMBER
2190 )IS
2191
2192
2193 l_address_id NUMBER := NULL;
2194 l_customer_site_id NUMBER := NULL;
2195 l_chart_of_accounts_id NUMBER := NULL;
2196 l_ship_chart_of_accounts_id NUMBER := NULL;
2197
2198 l_valid VARCHAR2(1) := NULL;
2199 l_valid_ccid BOOLEAN;
2200 l_api_version_number CONSTANT NUMBER := 1.0;
2201 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_IC_RELATION';
2202
2203 lreturn_status VARCHAR2(1);
2204 lmsg_data VARCHAR2(100);
2205 lsob_id NUMBER;
2206 lcoa_id NUMBER;
2207
2208 BEGIN
2209
2210 x_return_status := G_RET_STS_SUCCESS;
2211 x_valid := g_true;
2212 x_msg_data := null;
2213 x_msg_count := 0;
2214
2215 -- Standard call to check for call compatibility
2216 IF NOT FND_API.compatible_api_call
2217 (l_api_version_number
2218 , p_api_version
2219 , l_api_name
2220 , G_PKG_NAME)
2221 THEN
2222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2223 END IF;
2224
2225
2226 -- Initialize message list.
2227 IF fnd_api.to_boolean(p_init_msg_list) THEN
2228 fnd_msg_pub.initialize;
2229 END IF;
2230
2231 /* NOT NULL CHECK - ALL THE NOT NULL ERROR MESSAGES WOULD BE GROUPED AND
2232 RAISE AT ONCE */
2233 DECLARE
2234 hasNotNullError BOOLEAN := false;
2235 BEGIN -- BEGIN NOT NULL CHECK
2236
2237 IF p_ship_organization_id IS NULL THEN
2238 FND_MESSAGE.SET_NAME('INV','INV_INPUT_NULL');
2239 FND_MESSAGE.SET_TOKEN('COLUMN','SHIP_ORGANIZATION_ID');
2240 FND_MSG_PUB.ADD;
2241 hasNotNullError := true;
2242 END IF;
2243
2244 IF p_sell_organization_id IS NULL THEN
2245 FND_MESSAGE.SET_NAME('INV','INV_INPUT_NULL');
2246 FND_MESSAGE.SET_TOKEN('COLUMN','SELL_ORGANIZATION_ID');
2247 FND_MSG_PUB.ADD;
2248 hasNotNullError := true;
2249 END IF;
2250
2251 IF p_flow_type IS NULL THEN
2252 FND_MESSAGE.SET_NAME('INV','INV_INPUT_NULL');
2253 FND_MESSAGE.SET_TOKEN('COLUMN','FLOW_TYPE');
2254 FND_MSG_PUB.ADD;
2255 hasNotNullError := true;
2256 END IF;
2257
2258 IF p_customer_id IS NULL THEN
2259
2260 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2261 FND_MESSAGE.SET_TOKEN('COLUMN','CUSTOMER_ID');
2262 FND_MSG_PUB.ADD;
2263 hasNotNullError := true;
2264 END IF;
2265
2266 IF p_address_id IS NULL THEN
2267
2268 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2269 FND_MESSAGE.SET_TOKEN('COLUMN','ADDRESS_ID');
2270 FND_MSG_PUB.ADD;
2271 hasNotNullError := true;
2272
2273 END IF;
2274
2275 IF p_customer_site_id IS NULL THEN
2276
2277 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2278 FND_MESSAGE.SET_TOKEN('COLUMN','CUSTOMER_SITE_ID');
2279 FND_MSG_PUB.ADD;
2280 hasNotNullError := true;
2281
2282 END IF;
2283
2284 IF p_cust_trx_type_id IS NULL THEN
2285
2286 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2287 FND_MESSAGE.SET_TOKEN('COLUMN','CUST_TRX_TYPE_ID');
2288 FND_MSG_PUB.ADD;
2289 hasNotNullError := true;
2290
2291 END IF;
2292
2293 IF p_vendor_id IS NULL THEN
2294
2295 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2296 FND_MESSAGE.SET_TOKEN('COLUMN','VENDOR_ID');
2297 FND_MSG_PUB.ADD;
2298 hasNotNullError := true;
2299
2300 END IF;
2301
2302 IF p_vendor_site_id IS NULL THEN
2303
2304 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2305 FND_MESSAGE.SET_TOKEN('COLUMN','VENDOR_SITE_ID');
2306 FND_MSG_PUB.ADD;
2307 hasNotNullError := true;
2308
2309 END IF;
2310 /* Bug# 3314606
2311 IF p_freight_code_combination_id IS NULL THEN
2312 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2313 FND_MESSAGE.SET_TOKEN('COLUMN','FREIGHT_CODE_COMBINATION_ID');
2314 FND_MSG_PUB.ADD;
2315 hasNotNullError := true;
2316 END IF;
2317 */
2318 DECLARE
2319 nDummy NUMBER;
2320 BEGIN
2321
2322 SELECT 1 INTO nDummy FROM DUAL
2323 WHERE EXISTS (SELECT 1 FROM MTL_TRANSACTION_FLOW_HEADERS
2324 WHERE START_ORG_ID = p_ship_organization_id
2325 AND END_ORG_ID = p_ship_organization_id
2326 AND (NEW_ACCOUNTING_FLAG = 'Y' OR FLOW_TYPE <> 1));
2327
2328 IF p_intercompany_cogs_account_id IS NULL THEN
2329 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2330 FND_MESSAGE.SET_TOKEN('COLUMN','INTERCOMPANY_COGS_ACCOUNT_ID');
2331 FND_MSG_PUB.ADD;
2332 hasNotNullError := true;
2333 END IF;
2334
2335 IF p_inventory_accrual_account_id IS NULL THEN
2336 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2337 FND_MESSAGE.SET_TOKEN('COLUMN','INVENTORY_ACCRUAL_ACCOUNT_ID');
2338 FND_MSG_PUB.ADD;
2339 hasNotNullError := true;
2340 END IF;
2341
2342 IF p_expense_accrual_account_id IS NULL THEN
2343 FND_MESSAGE.SET_NAME('INV','INV_COLUMN_NOT_NULL');
2344 FND_MESSAGE.SET_TOKEN('COLUMN','EXPENSE_ACCRUAL_ACCOUNT_ID');
2345 FND_MSG_PUB.ADD;
2346 hasNotNullError := true;
2347 END IF;
2348 EXCEPTION
2349 WHEN NO_DATA_FOUND THEN
2350 NULL;
2351 END;
2352
2353 IF (hasNotNullError) THEN
2354 RAISE FND_API.G_EXC_ERROR;
2355 END IF;
2356
2357 END; -- END NOT NULL CHECK
2358
2359 /* END NOT NULL CHECK */
2360
2361 --Validating Customer_id
2362 IF p_customer_id = g_miss_num THEN
2363 --Do nothing
2364 NULL;
2365 ELSE
2366 l_valid := 'N';
2367 BEGIN
2368 SELECT 'Y' INTO l_valid FROM dual
2369 WHERE
2370 exists(SELECT CUST_ACCOUNT_ID
2371 FROM HZ_CUST_ACCOUNTS
2372 WHERE CUST_ACCOUNT_ID = p_customer_id);
2373 EXCEPTION
2374 WHEN no_data_found THEN
2375 l_valid := 'N';
2376 END;
2377
2378 IF l_valid = 'N' THEN
2379 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2380 FND_MESSAGE.SET_TOKEN('COLUMN','CUSTOMER_ID');
2381 RAISE FND_API.G_EXC_ERROR;
2382 END IF;
2383 END IF;
2384
2385 --Validating Address_id
2386
2387 IF p_address_id = g_miss_num THEN
2388 --Do nothing
2389 NULL;
2390 ELSE
2391 l_valid := 'N';
2392 BEGIN
2393 /* Modified query below : RA to HZ conversions
2394 Replaced occurances of RA views with HZ tables*/
2395 /*
2396 SELECT 'Y' INTO l_valid FROM dual
2397 WHERE
2398 exists
2399 (select rsu.address_id
2400 from ra_addresses_all ra , ra_site_uses_all rsu
2401 where nvl(rsu.status,'A') = 'A'
2402 and rsu.site_use_code = 'BILL_TO'
2403 and ra.address_id = rsu.address_id and nvl(ra.status,'A') = 'A'
2404 and ra.customer_id = Decode(p_customer_id,g_miss_num,ra.customer_id,p_customer_id)
2405 and ra.org_id = p_ship_organization_id
2406 AND rsu.address_id = p_address_id
2407 AND rsu.site_use_id = Decode(p_customer_site_id,g_miss_num,rsu.site_use_id,p_customer_site_id));
2408 */
2409
2410 SELECT 'Y' INTO l_valid
2411 FROM DUAL
2412 WHERE EXISTS(
2413 SELECT rsu.cust_acct_site_id
2414 FROM (SELECT loc_id address_id
2415 , acct_site.status
2416 , cust_account_id customer_id
2417 , acct_site.org_id
2418 FROM hz_party_sites party_site, hz_loc_assignments loc_assign, hz_locations loc, hz_cust_acct_sites_all acct_site
2419 WHERE acct_site.party_site_id = party_site.party_site_id
2420 AND loc.location_id = party_site.location_id
2421 AND loc.location_id = loc_assign.location_id
2422 AND NVL(acct_site.org_id, -99) = NVL(loc_assign.org_id, -99)) ra
2423 , hz_cust_site_uses_all rsu
2424 WHERE NVL(rsu.status, 'A') = 'A'
2425 AND rsu.site_use_code = 'BILL_TO'
2426 AND ra.address_id = rsu.cust_acct_site_id
2427 AND NVL(ra.status, 'A') = 'A'
2428 AND ra.customer_id = DECODE(p_customer_id, g_miss_num, ra.customer_id, p_customer_id)
2429 AND ra.org_id = p_ship_organization_id
2430 AND rsu.cust_acct_site_id = p_address_id
2431 AND rsu.site_use_id = DECODE(p_customer_site_id, g_miss_num, rsu.site_use_id, p_customer_site_id));
2432
2433
2434 EXCEPTION
2435 WHEN no_data_found THEN
2436 l_valid := 'N';
2437 END;
2438
2439 IF l_valid = 'N' THEN
2440 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2441 FND_MESSAGE.SET_TOKEN('COLUMN','ADDRESS_ID');
2442 RAISE FND_API.G_EXC_ERROR;
2443 END IF;
2444 END IF;
2445
2446 --Validating customer_site_id
2447
2448 IF p_customer_site_id = g_miss_num THEN
2449 --Do nothing
2450 NULL;
2451 ELSE
2452 l_valid := 'N';
2453 BEGIN
2454 /* Modified query below : RA to HZ conversions
2455 Replaced occurances of RA views with HZ tables*/
2456 /*
2457 SELECT 'Y' INTO l_valid FROM dual
2458 WHERE
2459 exists
2460 (select rsu.site_use_id
2461 from ra_addresses_all ra , ra_site_uses_all rsu
2462 where nvl(rsu.status,'A') = 'A'
2463 and rsu.site_use_code = 'BILL_TO'
2464 and ra.address_id = rsu.address_id and nvl(ra.status,'A') = 'A'
2465 and ra.customer_id = Decode(p_customer_id,g_miss_num,ra.customer_id,p_customer_id)
2466 and ra.org_id = p_ship_organization_id
2467 AND rsu.address_id = Decode(p_address_id,g_miss_num,rsu.address_id,p_address_id)
2468 AND rsu.site_use_id = p_customer_site_id);
2469 */
2470
2471 SELECT 'Y' INTO l_valid
2472 FROM DUAL
2473 WHERE EXISTS(
2474 SELECT rsu.site_use_id
2475 FROM (SELECT loc_id address_id
2476 , acct_site.status
2477 , cust_account_id customer_id
2478 , acct_site.org_id
2479 FROM hz_party_sites party_site, hz_loc_assignments loc_assign, hz_locations loc, hz_cust_acct_sites_all acct_site
2480 WHERE acct_site.party_site_id = party_site.party_site_id
2481 AND loc.location_id = party_site.location_id
2482 AND loc.location_id = loc_assign.location_id
2483 AND NVL(acct_site.org_id, -99) = NVL(loc_assign.org_id, -99)) ra
2484 , hz_cust_site_uses_all rsu
2485 WHERE NVL(rsu.status, 'A') = 'A'
2486 AND rsu.site_use_code = 'BILL_TO'
2487 AND ra.address_id = rsu.cust_acct_site_id
2488 AND NVL(ra.status, 'A') = 'A'
2489 AND ra.customer_id = DECODE(p_customer_id, g_miss_num, ra.customer_id, p_customer_id)
2490 AND ra.org_id = p_ship_organization_id
2491 AND rsu.cust_acct_site_id = DECODE(p_address_id, g_miss_num, rsu.cust_acct_site_id, p_address_id)
2492 AND rsu.site_use_id = p_customer_site_id);
2493
2494 EXCEPTION
2495 WHEN no_data_found THEN
2496 l_valid := 'N';
2497 END;
2498
2499 IF l_valid = 'N' THEN
2500 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2501 FND_MESSAGE.SET_TOKEN('COLUMN','CUSTOMER_SITE_ID');
2502 RAISE FND_API.G_EXC_ERROR;
2503 END IF;
2504 END IF;
2505
2506 --Validating cust_trx_type_id
2507
2508 IF p_cust_trx_type_id = g_miss_num THEN
2509 --Do nothing
2510 NULL;
2511
2512 ELSE
2513 l_valid := 'N';
2514 BEGIN
2515 SELECT 'Y' INTO l_valid FROM dual
2516 WHERE
2517 exists
2518 (select cust_trx_type_id
2519 from ra_cust_trx_types_all
2520 where
2521 sysdate between nvl(start_date, sysdate-1)
2522 and nvl(end_date, sysdate+1)
2523 and org_id = p_ship_organization_id
2524 AND cust_trx_type_id = p_cust_trx_type_id);
2525 EXCEPTION
2526 WHEN no_data_found THEN
2527 l_valid := 'N';
2528 END;
2529
2530 IF l_valid = 'N' THEN
2531 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2532 FND_MESSAGE.SET_TOKEN('COLUMN','CUST_TRX_TYPE_ID');
2533 RAISE FND_API.G_EXC_ERROR;
2534 END IF;
2535 END IF;
2536
2537 --Validating vendor_id
2538
2539 IF p_vendor_id = g_miss_num THEN
2540 --Do nothing
2541 NULL;
2542
2543 ELSE
2544 l_valid := 'N';
2545 BEGIN
2546 SELECT 'Y' INTO l_valid FROM dual
2547 WHERE
2548 exists
2549 (select pov.vendor_id from
2550 po_vendors pov,
2551 FND_LOOKUPS FL
2552 WHERE
2553 NVL(POV.HOLD_FLAG,'N') = FL.LOOKUP_CODE
2554 AND FL.LOOKUP_TYPE = 'YES_NO'
2555 AND POV.ENABLED_FLAG = 'Y'
2556 AND SYSDATE BETWEEN NVL(POV.START_DATE_ACTIVE, SYSDATE-1)
2557 AND NVL(POV.END_DATE_ACTIVE+1, SYSDATE+1)
2558 and pov.vendor_id = p_vendor_id);
2559 EXCEPTION
2560 WHEN no_data_found THEN
2561 l_valid := 'N';
2562 END;
2563
2564 IF l_valid = 'N' THEN
2565 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2566 FND_MESSAGE.SET_TOKEN('COLUMN','VENDOR_ID');
2567 RAISE FND_API.G_EXC_ERROR;
2568 END IF;
2569 END IF;
2570
2571 --Validating vendor_site_id
2572 IF p_vendor_site_id = g_miss_num THEN
2573 --Do nothing
2574 NULL;
2575
2576 ELSE
2577 l_valid := 'N';
2578 BEGIN
2579 SELECT 'Y' INTO l_valid FROM dual
2580 WHERE
2581 exists
2582 (select vendor_site_id
2583 from po_vendor_sites_all
2584 where pay_site_flag = 'Y'
2585 and vendor_id = p_vendor_id
2586 and org_id = p_sell_organization_id
2587 AND vendor_site_id = p_vendor_site_id);
2588 EXCEPTION
2589 WHEN no_data_found THEN
2590 l_valid := 'N';
2591 END;
2592
2593 IF l_valid = 'N' THEN
2594 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2595 FND_MESSAGE.SET_TOKEN('COLUMN','VENDOR_SITE_ID');
2596 RAISE FND_API.G_EXC_ERROR;
2597 END IF;
2598 END IF;
2599
2600 --Getting chart_of_accounts_id
2601
2602 /* commented the selection of COA using LE - OU link which is obsoleted in R12
2603 and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
2604 Bug No - 4336479
2605 BEGIN
2606 SELECT
2607 gsob.chart_of_accounts_id
2608 into
2609 l_ship_chart_of_accounts_id
2610 from
2611 gl_sets_of_books gsob,
2612 hr_organization_information hoi,
2613 hr_organization_information hoi1
2614 where
2615 hoi1.organization_id = p_ship_organization_id
2616 and hoi1.org_information_context = 'Operating Unit Information'
2617 and hoi.organization_id = to_number(hoi1.org_information2)
2618 and hoi.org_information_context = 'Legal Entity Accounting'
2619 and gsob.set_of_books_id = to_number(hoi.org_information1);
2620 EXCEPTION
2621 WHEN no_data_found THEN
2622 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2623 FND_MESSAGE.SET_TOKEN('COLUMN','SHIP_CHART_OF_ACCOUNTS_ID');
2624 RAISE FND_API.G_EXC_ERROR;
2625 END;
2626 */
2627
2628 BEGIN
2629 Inv_globals.get_ledger_info(
2630 x_return_status => lreturn_status,
2631 x_msg_data => lmsg_data ,
2632 p_context_type => 'Operating Unit Information',
2633 p_org_id => p_ship_organization_id,
2634 x_sob_id => lsob_id,
2635 x_coa_id => lcoa_id,
2636 p_account_info_context => 'COA');
2637 IF NVL(lreturn_status , 'S') = 'E' THEN
2638 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2639 FND_MESSAGE.SET_TOKEN('COLUMN','SHIP_CHART_OF_ACCOUNTS_ID');
2640 RAISE FND_API.G_EXC_ERROR;
2641 END IF;
2642 l_ship_chart_of_accounts_id := lcoa_id;
2643 END;
2644
2645
2646 -- sell side
2647 /* commented the selection of COA using LE - OU link which is obsoleted in R12
2648 and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
2649 Bug No - 4336479
2650 BEGIN
2651 select
2652 gsob.chart_of_accounts_id
2653 into
2654 l_chart_of_accounts_id
2655 from
2656 gl_sets_of_books gsob,
2657 hr_organization_information hoi,
2658 hr_organization_information hoi1
2659 where hoi1.organization_id = p_sell_organization_id
2660 and hoi1.org_information_context = 'Operating Unit Information'
2661 and hoi.organization_id = to_number(hoi1.org_information2)
2662 and hoi.org_information_context = 'Legal Entity Accounting'
2663 and gsob.set_of_books_id = to_number(hoi.org_information1);
2664 EXCEPTION
2665 WHEN no_data_found THEN
2666 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2667 FND_MESSAGE.SET_TOKEN('COLUMN','CHART_OF_ACCOUNTS_ID');
2668 RAISE FND_API.G_EXC_ERROR;
2669 END;
2670 */
2671
2672 BEGIN
2673 Inv_globals.get_ledger_info(
2674 x_return_status => lreturn_status,
2675 x_msg_data => lmsg_data ,
2676 p_context_type => 'Operating Unit Information',
2677 p_org_id => p_sell_organization_id,
2678 x_sob_id => lsob_id,
2679 x_coa_id => lcoa_id,
2680 p_account_info_context => 'COA');
2681 IF NVL(lreturn_status , 'S') = 'E' THEN
2682 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2683 FND_MESSAGE.SET_TOKEN('COLUMN','CHART_OF_ACCOUNTS_ID');
2684 RAISE FND_API.G_EXC_ERROR;
2685 END IF;
2686 l_chart_of_accounts_id:= lcoa_id;
2687 END;
2688
2689
2690 --Validating freight_code_combination_id
2691
2692 IF P_FREIGHT_CODE_COMBINATION_ID IS NULL OR P_FREIGHT_CODE_COMBINATION_ID = g_miss_num THEN
2693 --Do nothing
2694 NULL;
2695 ELSE
2696 l_valid_ccid :=
2697 fnd_flex_keyval.validate_ccid
2698 (appl_short_name => 'SQLGL',
2699 key_flex_code => 'GL#',
2700 structure_number => l_chart_of_accounts_id,
2701 combination_id => p_freight_code_combination_id,
2702 vrule => '\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN\\0GL_GLOBAL\\nDETAIL_POSTING_ALLOWED\\nE\\nAPPL=INV;NAME=INV_VRULE_POSTING\\nN');
2703
2704 IF NOT l_valid_ccid THEN
2705
2706 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2707 FND_MESSAGE.SET_TOKEN('COLUMN','FREIGHT_CODE_COMBINATION_ID');
2708 RAISE FND_API.G_EXC_ERROR;
2709 END IF;
2710 END IF;
2711
2712 --Validating intercompany_cogs_account_id
2713
2714 IF P_intercompany_cogs_account_id IS NULL OR P_intercompany_cogs_account_id = g_miss_num THEN
2715 --Do nothing
2716 NULL;
2717 ELSE
2718 l_valid_ccid :=
2719 fnd_flex_keyval.validate_ccid
2720 (appl_short_name => 'SQLGL',
2721 key_flex_code => 'GL#',
2722 structure_number => l_ship_chart_of_accounts_id,
2723 combination_id => p_intercompany_cogs_account_id,
2724 vrule => '\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN\\0GL_GLOBAL\\nDETAIL_POSTING_ALLOWED\\nE\\nAPPL=INV;NAME=INV_VRULE_POSTING\\nN');
2725
2726 IF NOT l_valid_ccid THEN
2727 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2728 FND_MESSAGE.SET_TOKEN('COLUMN','INTERCOMPANY_COGS_ACCOUNT_ID');
2729 RAISE FND_API.G_EXC_ERROR;
2730 END IF;
2731 END IF;
2732
2733 --Validating inventory_accrual_account_id
2734
2735 IF P_inventory_accrual_account_id IS NULL OR P_inventory_accrual_account_id = g_miss_num THEN
2736 --Do nothing
2737 NULL;
2738 ELSE
2739 l_valid_ccid :=
2740 fnd_flex_keyval.validate_ccid
2741 (appl_short_name => 'SQLGL',
2742 key_flex_code => 'GL#',
2743 structure_number => l_chart_of_accounts_id,
2744 combination_id => p_inventory_accrual_account_id,
2745 vrule => '\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN\\0GL_GLOBAL\\nDETAIL_POSTING_ALLOWED\\nE\\nAPPL=INV;NAME=INV_VRULE_POSTING\\nN');
2746
2747 IF NOT l_valid_ccid THEN
2748 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2749 FND_MESSAGE.SET_TOKEN('COLUMN','INVENTORY_ACCRUAL_ACCOUNT_ID');
2750 RAISE FND_API.G_EXC_ERROR;
2751 END IF;
2752 END IF;
2753
2754 --Validating expense_accrual_account_id
2755
2756 IF P_expense_accrual_account_id IS NULL OR P_expense_accrual_account_id = g_miss_num THEN
2757 --Do nothing
2758 NULL;
2759 ELSE
2760 l_valid_ccid :=
2761 fnd_flex_keyval.validate_ccid
2762 (appl_short_name => 'SQLGL',
2763 key_flex_code => 'GL#',
2764 structure_number => l_chart_of_accounts_id,
2765 combination_id => p_expense_accrual_account_id,
2766 vrule => '\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN\\0GL_GLOBAL\\nDETAIL_POSTING_ALLOWED\\nE\\nAPPL=INV;NAME=INV_VRULE_POSTING\\nN');
2767
2768 IF NOT l_valid_ccid THEN
2769 FND_MESSAGE.SET_NAME('INV','INV_INVALID_COLUMN');
2770 FND_MESSAGE.SET_TOKEN('COLUMN','EXPENSE_ACCRUAL_ACCOUNT_ID');
2771 RAISE FND_API.G_EXC_ERROR;
2772 END IF;
2773 END IF;
2774
2775 IF p_attribute_category = g_miss_num THEN
2776 --Do nothing
2777 NULL;
2778 ELSIF NOT inv_transaction_flow_pvt.Validate_Dff( P_FLEX_NAME => 'MTL_INTERCOMPANY_PARAMETERS',
2779 P_ATTRIBUTE1 => p_attribute1,
2780 P_ATTRIBUTE2 => p_attribute2,
2781 P_ATTRIBUTE3 => p_attribute3,
2782 P_ATTRIBUTE4 => p_attribute4,
2783 P_ATTRIBUTE5 => p_attribute5,
2784 P_ATTRIBUTE6 => p_attribute6,
2785 P_ATTRIBUTE7 => p_attribute7,
2786 P_ATTRIBUTE8 => p_attribute8,
2787 P_ATTRIBUTE9 => p_attribute9,
2788 P_ATTRIBUTE10 => p_attribute10,
2789 P_ATTRIBUTE11 => p_attribute11,
2790 P_ATTRIBUTE12 => p_attribute12,
2791 P_ATTRIBUTE13 => p_attribute13,
2792 P_ATTRIBUTE14 => p_attribute14,
2793 P_ATTRIBUTE15 => p_attribute15,
2794 P_ATTRIBUTE_CATEGORY => p_attribute_category
2795 ) THEN
2796 RAISE FND_API.G_EXC_ERROR;
2797 END IF;
2798
2799 EXCEPTION
2800 WHEN FND_API.G_EXC_ERROR THEN
2801 x_return_status := FND_API.G_RET_STS_ERROR;
2802 x_valid := g_false;
2803 FND_MSG_PUB.ADD;
2804 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2805
2806 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2808 x_valid := g_false;
2809 FND_MSG_PUB.ADD;
2810 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2811 WHEN OTHERS THEN
2812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2813 x_valid := g_false;
2814 FND_MSG_PUB.ADD;
2815 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2816 FND_MSG_PUB.Add_Exc_Msg
2817 ( G_PACKAGE_NAME, 'validate_IC_RELATION_rec');
2818 end if;
2819 END validate_IC_RELATION_rec;
2820
2821
2822
2823 /*==========================================================================================================
2824 * Package: INV_TRANSACTION_FLOWS_PUB
2825 *
2826 * Procedure: GET_DROPSHIP_PO_TXN_TYPE
2827 *
2828 * Description:
2829 * This API gets the drop ship transaction type code for a drop ship or global procurement flow.
2830 * This API will be called by Oracle Receiving as well as Oracle Costing
2831 *
2832 * Inputs:
2833 * - p_po_line_location_id - the Purchase Order LIne Location
2834 * - p_global_procurement_flag - a flag to indicate whether the flow is global procurement flow
2835 *
2836 * Outputs:
2837 * - x_ds_type_code - the drop ship transaction type code. The possible value for this are:
2838 * 1 - Drop Ship flow and logical
2839 * 2 - Drop Ship Flow and physical
2840 * 3 - Not a Drop Ship Flow and Physical
2841 * - x_header_id - Transaction Flow Header Identifier.A value is
2842 * returned only when x_ds_type_code is returned as 1
2843 *
2844 * - x_return_Status - the return status
2845 * - x_msg_data - the error message
2846 * - x_msg_count - the message count
2847 *============================================================================================================*/
2848 PROCEDURE GET_DROPSHIP_PO_TXN_TYPE
2849 (
2850 p_api_version IN NUMBER
2851 , p_init_msg_list IN VARCHAR2
2852 , p_rcv_transaction_id IN NUMBER
2853 , p_global_procurement_flag IN VARCHAR2
2854 , x_return_Status OUT NOCOPY VARCHAR2
2855 , x_msg_data OUT NOCOPY VARCHAR2
2856 , x_msg_count OUT NOCOPY NUMBER
2857 , x_transaction_type_id OUT NOCOPY NUMBER
2858 , x_transaction_action_id OUT NOCOPY NUMBER
2859 , x_transaction_Source_type_id OUT NOCOPY NUMBER
2860 , x_dropship_type_Code OUT NOCOPY NUMBER
2861 , x_header_id OUT NOCOPY NUMBER
2862 ) IS
2863
2864 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2865 l_inventory_item_id NUMBER;
2866 l_po_header_id NUMBER := NULL;
2867 l_po_line_id NUMBER := NULL;
2868 l_po_line_location_id NUMBER := NULL;
2869 l_transaction_type VARCHAR2(80) := NULL;
2870 l_header_id NUMBER := NULL;
2871 l_line_id NUMBER := NULL;
2872 l_external_drop_ship BOOLEAN := TRUE;
2873 l_selling_org_id NUMBER := NULL;
2874 l_ship_from_org_id NUMBER := NULL;
2875 l_selling_ou NUMBER := NULL;
2876 l_ship_from_ou NUMBER := NULL;
2877 l_qualifier_code_tbl number_tbl;
2878 l_qualifier_value_tbl number_tbl;
2879 l_transaction_flow_exists VARCHAR2(1);
2880 l_txn_flow_header_id NUMBER;
2881 l_new_accounting_flag VARCHAR2(1);
2882 l_return_status VARCHAR2(1);
2883 l_msg_data VARCHAR2(2000);
2884 l_msg_count NUMBER;
2885 l_transaction_date DATE;
2886 l_api_version_number CONSTANT NUMBER := 1.0;
2887 l_api_name CONSTANT VARCHAR2(30) := 'GET_DROPSHIP_PO_TXN_TYPE';
2888 l_rcv_parent_txn_id NUMBER;--bug#16080592 added
2889
2890 CURSOR drop_ship_sources
2891 (l_poh_id NUMBER,
2892 l_pol_id NUMBER,
2893 l_poll_id NUMBER)
2894 IS
2895 SELECT header_id, line_id
2896 FROM
2897 oe_drop_ship_sources
2898 WHERE
2899 po_header_id = l_poh_id
2900 AND po_line_id = l_pol_id
2901 AND line_location_id = l_poll_id
2902 ORDER BY header_id,line_id;
2903 BEGIN
2904
2905 IF l_debug = 1 then
2906 print_debug('Entered Get_dropship_PO_txn_type', l_api_name);
2907 print_debug('Inputs p_rcv_transaction_id '||p_rcv_transaction_id||
2908 ' p_global_procurement_flag '||p_global_procurement_flag, l_api_name);
2909 END IF;
2910
2911 -- Standard call to check for call compatibility
2912 IF NOT FND_API.Compatible_API_Call(
2913 l_api_version_number
2914 , p_api_version
2915 , l_api_name
2916 , G_PKG_NAME)
2917 THEN
2918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2919 END IF;
2920
2921
2922 -- Initialize message list.
2923 IF fnd_api.to_boolean(p_init_msg_list) THEN
2924 fnd_msg_pub.initialize;
2925 END IF;
2926
2927
2928 x_return_status := 'S';
2929 x_msg_data := null;
2930 x_msg_count := 0;
2931 x_dropship_type_code := -99;
2932 x_transaction_type_id := -99;
2933 x_transaction_action_id := -99;
2934 x_transaction_source_type_id := -99;
2935 x_header_id := -99;
2936
2937 IF nvl(p_rcv_transaction_id,-1) <= 0 THEN
2938 IF l_debug = 1 then
2939 print_debug('incorrect p_rcv_transaction_id', l_api_name);
2940 END IF;
2941 fnd_message.set_name('INV', 'INV_NO_RCVTXNID');
2942 fnd_msg_pub.add;
2943 RAISE fnd_api.g_exc_error;
2944 END IF;
2945
2946 BEGIN
2947 SELECT rt.po_header_id, rt.po_line_id, rt.po_line_location_id,
2948 rt.transaction_type, rt.transaction_date, rsl.item_id, rt.parent_transaction_id --bug#16080592
2949 INTO
2950 l_po_header_id, l_po_line_id, l_po_line_location_id,
2951 l_transaction_type, l_transaction_date, l_inventory_item_id,l_rcv_parent_txn_id
2952 FROM
2953 rcv_transactions rt,
2954 rcv_shipment_lines rsl
2955 WHERE
2956 transaction_id = p_rcv_transaction_id
2957 AND rt.shipment_line_id = rsl.shipment_line_id;
2958 EXCEPTION
2959 WHEN no_data_found THEN
2960 IF l_debug = 1 then
2961 print_debug('no record found in rcv_transcations', l_api_name);
2962 END IF;
2963 fnd_message.set_name('INV', 'INV_NO_RCVTXN');
2964 fnd_msg_pub.add;
2965 RAISE fnd_api.g_exc_error;
2966 END;
2967
2968 IF l_debug = 1 then
2969 print_debug(' l_po_header_id '||l_po_header_id||
2970 ' l_po_line_id '||l_po_line_id||
2971 ' l_po_line_location_id '||l_po_line_location_id||
2972 ' l_inventory_item_id '||l_inventory_item_id, l_api_name);
2973 print_debug('l_transaction_date '||l_transaction_date, l_api_name);
2974 print_debug('l_transaction_type '||l_transaction_type, l_api_name);
2975 END IF;
2976
2977 IF (l_po_header_id IS NULL) OR (l_po_line_id IS NULL) OR
2978 (l_po_line_location_id IS NULL) THEN
2979 IF l_debug = 1 then
2980 print_debug('Insufficient information in rcv_transactions', l_api_name);
2981 END IF;
2982 fnd_message.set_name('INV', 'INV_NO_RCVTXN_INFO');
2983 fnd_msg_pub.add;
2984 RAISE fnd_api.g_exc_error;
2985 END IF;
2986
2987
2988 IF l_transaction_type in ('RETURN TO RECEIVING',
2989 'RETURN TO CUSTOMER',
2990 'RETURN TO VENDOR') THEN
2991 /*
2992 bug#16080592,the RETURN transactions should have the same drop_ship_code
2993 as its original parent transaction*/
2994 begin
2995 select dropship_type_code
2996 into x_dropship_type_code
2997 from rcv_transactions
2998 where transaction_id = l_rcv_parent_txn_id;
2999 exception
3000 when NO_DATA_FOUND then
3001 x_dropship_type_code := G_PHYSICAL_RECEIPT_FOR_NON_DS;
3002 end;
3003
3004 x_transaction_type_id := INV_GLOBALS.G_TYPE_RETURN_TO_VENDOR;
3005 x_transaction_action_id := INV_GLOBALS.G_ACTION_ISSUE;
3006 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3007
3008 ELSE
3009
3010 OPEN drop_ship_sources(l_po_header_id,l_po_line_id,l_po_line_location_id);
3011
3012 FETCH drop_ship_sources INTO l_header_id,l_line_id;
3013
3014 IF drop_ship_sources%notfound THEN
3015 IF l_debug = 1 then
3016 print_debug('No data found in oe_drop_ship_sources: not a drop ship', l_api_name);
3017 END IF;
3018 l_external_drop_ship := FALSE;
3019 ELSE
3020 l_external_drop_ship := TRUE;
3021 IF l_debug = 1 then
3022 print_debug('Drop ship', l_api_name);
3023 END IF;
3024 END IF;
3025
3026 IF drop_ship_sources%isopen THEN
3027 CLOSE drop_ship_sources;
3028 END IF;
3029
3030 IF l_debug = 1 then
3031 print_debug('l_header_id '||l_header_id||
3032 ' l_line_id '||l_line_id, l_api_name);
3033 END IF;
3034
3035 IF NOT l_external_drop_ship THEN
3036 x_dropship_type_code := G_PHYSICAL_RECEIPT_FOR_NON_DS;
3037 IF l_transaction_type = 'DELIVER' THEN
3038 x_transaction_type_id := INV_GLOBALS.G_TYPE_PO_RECEIPT;
3039 x_transaction_action_id := INV_GLOBALS.G_ACTION_RECEIPT;
3040 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3041 ELSIF l_transaction_type = 'CORRECT' THEN
3042 x_transaction_type_id := INV_GLOBALS.G_TYPE_PO_RCPT_ADJ;
3043 x_transaction_action_id := INV_GLOBALS.G_ACTION_DELIVERYADJ;
3044 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3045 END IF;
3046 ELSE
3047
3048 BEGIN
3049 SELECT org_id, ship_from_org_id
3050 INTO l_selling_ou, l_ship_from_org_id
3051 FROM oe_order_lines_all
3052 WHERE
3053 header_id = l_header_id AND
3054 line_id = l_line_id;
3055 EXCEPTION
3056 WHEN no_data_found THEN
3057 IF l_debug = 1 then
3058 print_debug('cannot find sales order line ', l_api_name);
3059 END IF;
3060 fnd_message.set_name('INV', 'INV_NO_SALES_ORDER_LINE');
3061 fnd_msg_pub.add;
3062 RAISE fnd_api.g_exc_error;
3063 END;
3064
3065 IF l_debug = 1 then
3066 print_debug('l_selling_ou '||l_selling_ou||
3067 ' l_ship_from_org_id '||l_ship_from_org_id, l_api_name);
3068 END IF;
3069
3070 BEGIN
3071 SELECT org_information3
3072 INTO l_ship_from_ou
3073 FROM HR_ORGANIZATION_INFORMATION HOI
3074 WHERE HOI.ORGANIZATION_ID= l_ship_from_org_id
3075 AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information';
3076 EXCEPTION
3077 WHEN no_data_found THEN
3078 l_ship_from_ou := NULL;
3079 IF l_debug = 1 then
3080 print_debug('cannot find ship from operating unit', l_api_name);
3081 END IF;
3082 END;
3083
3084 IF l_debug = 1 then
3085 print_debug('l_ship_from_ou '||l_ship_from_ou, l_api_name);
3086 END IF;
3087
3088 IF (l_selling_ou IS NULL) OR (l_ship_from_ou IS NULL) THEN
3089 fnd_message.set_name('INV', 'INV_NULL_SELLSHIP_OU');
3090 fnd_msg_pub.add;
3091 RAISE fnd_api.g_exc_error;
3092 END IF;
3093
3094 /******* Calling get transaction flow for the operating units*******/
3095
3096 l_qualifier_code_tbl.DELETE;
3097 l_qualifier_value_tbl.DELETE;
3098
3099 BEGIN
3100 SELECT category_id INTO l_qualifier_value_tbl(1)
3101 FROM mtl_item_categories
3102 WHERE
3103 inventory_item_id = l_inventory_item_id
3104 AND organization_id = l_ship_from_org_id
3105 AND category_set_id = 1;
3106
3107 l_qualifier_code_tbl(1) := 1;
3108
3109 IF l_debug = 1 then
3110 print_debug('category id'||l_qualifier_value_tbl(1), l_api_name);
3111 END IF;
3112
3113 EXCEPTION
3114 WHEN no_data_found THEN
3115 l_qualifier_value_tbl.DELETE;
3116 l_qualifier_value_tbl.DELETE;
3117
3118 when too_many_rows then
3119 fnd_message.set_name('INV', 'INV_TOO_MANY_CATEGORIES');
3120 fnd_msg_pub.add;
3121 RAISE fnd_api.g_exc_error;
3122 END;
3123
3124 INV_TRANSACTION_FLOW_PUB.CHECK_TRANSACTION_FLOW
3125 (p_api_version => 1.0
3126 ,p_start_operating_unit => l_ship_from_ou
3127 ,p_end_operating_unit => l_selling_ou
3128 ,p_flow_type => 1 --shipping
3129 ,p_organization_id => l_ship_from_org_id
3130 ,p_qualifier_code_tbl => l_qualifier_code_tbl
3131 ,p_qualifier_value_tbl => l_qualifier_value_tbl
3132 ,p_transaction_date => l_transaction_date
3133 ,x_return_status => l_return_status
3134 ,x_msg_count => l_msg_data
3135 ,x_msg_data => l_msg_count
3136 ,x_header_id => l_txn_flow_header_id
3137 ,x_new_accounting_flag => l_new_accounting_flag
3138 ,x_transaction_flow_exists => l_transaction_flow_exists
3139 );
3140
3141 IF l_debug = 1 THEN
3142 print_debug('check_transaction_flow Ret Status '|| l_return_status, l_api_name);
3143 print_debug('Ret Message '||l_msg_data, l_api_name);
3144 print_debug('l_transaction_flow_exists '||l_transaction_flow_exists, l_api_name);
3145 print_debug('l_txn_flow_header_id '||l_txn_flow_header_id, l_api_name);
3146 print_debug('l_new_accounting_flag '||l_new_accounting_flag, l_api_name);
3147 END IF;
3148
3149 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3150
3151 RAISE fnd_api.g_exc_unexpected_error;
3152
3153 ELSIF (l_transaction_flow_exists = g_transaction_flow_found)
3154 AND (l_new_accounting_flag in ('Y','y')) THEN
3155 --Drop ship logical
3156 x_dropship_type_code := G_LOGICAL_RECEIPT_FOR_DS;
3157 IF l_transaction_type = 'DELIVER' THEN
3158 x_header_id := l_txn_flow_header_id;
3159 x_transaction_type_id := INV_GLOBALS.G_TYPE_LOGL_PO_RECEIPT;
3160 x_transaction_action_id := INV_GLOBALS.G_ACTION_LOGICALRECEIPT;
3161 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3162 ELSIF l_transaction_type = 'CORRECT' THEN
3163 x_header_id := l_txn_flow_header_id;
3164 x_transaction_type_id := INV_GLOBALS.G_TYPE_LOGL_PO_RECEIPT_ADJ;
3165 x_transaction_action_id := INV_GLOBALS.G_ACTION_LOGICALDELADJ;
3166 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3167 END IF;
3168 ELSE
3169 --Drop ship Physical
3170 x_dropship_type_code := G_PHYSICAL_RECEIPT_FOR_DS;
3171 IF l_transaction_type = 'DELIVER' THEN
3172 x_transaction_type_id := INV_GLOBALS.G_TYPE_PO_RECEIPT;
3173 x_transaction_action_id := INV_GLOBALS.G_ACTION_RECEIPT;
3174 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3175 ELSIF l_transaction_type = 'CORRECT' THEN
3176 x_transaction_type_id := INV_GLOBALS.G_TYPE_PO_RCPT_ADJ;
3177 x_transaction_action_id := INV_GLOBALS.G_ACTION_DELIVERYADJ;
3178 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3179 END IF;
3180 END IF;
3181
3182 END IF;--NOT l_external_drop_ship
3183 END IF;--else of IF l_transaction_type = 'RETURN TO RECEIVING'
3184
3185 IF l_debug = 1 THEN
3186 print_debug('Return values ', l_api_name);
3187 print_debug('x_dropship_type_code '||x_dropship_type_code, l_api_name);
3188 print_debug('x_header_id '||x_header_id, l_api_name);
3189 print_debug('x_transaction_type_id '||x_transaction_type_id||
3190 ' x_transaction_action_id '||x_transaction_action_id||
3191 ' x_transaction_source_type_id '||x_transaction_source_type_id, l_api_name);
3192 END IF;
3193 EXCEPTION
3194 WHEN FND_API.G_EXC_ERROR THEN
3195 x_return_status := FND_API.G_RET_STS_ERROR;
3196 x_dropship_type_code := -99;
3197 x_transaction_type_id := -99;
3198 x_transaction_action_id := -99;
3199 x_transaction_source_type_id := -99;
3200 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3201
3202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3204 x_dropship_type_code := -99;
3205 x_transaction_type_id := -99;
3206 x_transaction_action_id := -99;
3207 x_transaction_source_type_id := -99;
3208 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3209
3210 WHEN OTHERS THEN
3211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3212 x_dropship_type_code := -99;
3213 x_transaction_type_id := -99;
3214 x_transaction_action_id := -99;
3215 x_transaction_source_type_id := -99;
3216
3217 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3218 FND_MSG_PUB.Add_Exc_Msg
3219 ( G_PACKAGE_NAME, 'GET_DROPSHIP_PO_TXN_TYPE');
3220 end if;
3221 END GET_DROPSHIP_PO_TXN_TYPE;
3222
3223
3224 /** Get_Inventory_Org function
3225 * Private function to get the inventory org of a specific OU
3226 */
3227
3228 FUNCTION GET_INVENTORY_ORG(
3229 p_reference_id IN NUMBER
3230 , p_global_procurement_flag IN VARCHAR2
3231 , p_drop_ship_flag IN VARCHAR2
3232 , x_transaction_date OUT NOCOPY DATE
3233 , x_return_status OUT NOCOPY VARCHAR2
3234 , x_msg_data OUT NOCOPY VARCHAR2
3235 , x_msg_count OUT NOCOPY NUMBER
3236 ) RETURN NUMBER IS
3237 l_organization_id NUMBER;
3238 l_transaction_date DATE;
3239 l_progress NUMBER := 0;
3240 l_doc_type VARCHAR2(4);
3241 l_whse_code VARCHAR2(4);
3242 l_line_id NUMBER;
3243 BEGIN
3244 print_debug('Inside get_inventory_org', 'Get_Inventory_Org');
3245 print_debug('p_reference_id = ' || p_reference_id, 'Get_Inventory_org');
3246 print_Debug('p_global_procurement_flag = ' || p_global_procurement_flag, 'Get_Inventory_Org');
3247 print_Debug('p_drop_ship_flag = ' || p_drop_ship_flag, 'Get_Inventory_org');
3248
3249 /* OPM INVCONV umoogala
3250 * This code will not longer be needed for process mfg.orgs
3251 IF ( GML_PROCESS_FLAGS.process_orgn = 1 AND GML_PROCESS_FLAGS.opmitem_flag = 1 ) THEN
3252
3253 SELECT doc_type, line_id, whse_code
3254 INTO l_doc_type, l_line_id, l_whse_code
3255 FROM ic_tran_pnd
3256 WHERE trans_id = p_reference_id;
3257
3258 IF l_doc_type = 'OMSO' THEN
3259 SELECT WHS.mtl_organization_id, oeh.ordered_date
3260 INTO l_organization_id, l_transaction_date
3261 FROM ic_whse_mst WHS
3262 , oe_order_lines_all OEL
3263 , oe_order_headers_all OEH
3264 WHERE OEL.line_id = l_line_id
3265 AND oel.header_id = oeh.header_id
3266 AND WHS.whse_code = l_whse_code;
3267 ELSIF l_doc_type = 'PORC' THEN
3268 SELECT WHS.mtl_organization_id, oeh.ordered_date
3269 INTO l_organization_id, l_transaction_date
3270 FROM ic_whse_mst WHS
3271 , oe_order_lines_all OEL
3272 , oe_order_headers_all OEH
3273 , rcv_transactions RCT
3274 , po_requisition_headers_all poh
3275 , po_requisition_lines_all pol
3276 WHERE poh.requisition_header_id = pol.requisition_header_id
3277 AND pol.requisition_line_id = oel.orig_sys_document_Ref
3278 AND oel.order_source_id = 10
3279 AND oel.header_id = oeh.header_id
3280 AND RCT.transaction_id = l_line_id
3281 AND RCT.requisition_line_id = pol.requisition_line_id
3282 AND WHS.whse_code = l_whse_code;
3283 END IF;
3284 else
3285 end OPM INVCONV */
3286
3287 if( p_global_procurement_flag = 'N' )
3288 then
3289 -- this means this is not a global procurement
3290 -- we need to check if this is a drop ship with procuring flow
3291 print_debug('Inside p_global_procurement_flag = N', 'Get_Inventory_Org');
3292
3293 if( p_drop_ship_flag = 'N')
3294 then
3295 l_progress := 1;
3296 BEGIN
3297 select mmt.organization_id, transaction_date
3298 into l_organization_id, l_transaction_date
3299 From mtl_material_transactions mmt
3300 where mmt.transaction_id= p_reference_id;
3301 EXCEPTION
3302 WHEN NO_DATA_FOUND then
3303 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_TRANSACTIONS');
3304 FND_MESSAGE.SET_TOKEN('ID', p_reference_id);
3305 FND_MSG_PUB.ADD;
3306 raise FND_API.G_EXC_ERROR;
3307 END;
3308 else
3309 l_progress := 2;
3310 -- this is a true drop ship with procuring flow
3311 BEGIN
3312 select oel.ship_from_org_id, oeh.ordered_date
3313 into l_organization_id, l_transaction_date
3314 FROM oe_order_lines_all oel, oe_order_headers_all oeh
3315 where oel.line_id = p_reference_id
3316 AND oel.header_id = oeh.header_id;
3317 EXCEPTION
3318 WHEN NO_DATA_FOUND then
3319 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SALES_ORDER');
3320 FND_MESSAGE.SET_TOKEN('LINE', p_reference_id);
3321 FND_MSG_PUB.ADD;
3322 raise FND_API.G_EXC_ERROR;
3323 END;
3324 end if;
3325 else
3326 -- this is global procurement flow.
3327 l_progress := 3;
3328 BEGIN
3329 select rcv.organization_id, transaction_date
3330 into l_organization_id, l_transaction_date
3331 FROM rcv_transactions rcv
3332 WHERE rcv.transaction_id = p_reference_id;
3333 EXCEPTION
3334 WHEN NO_DATA_FOUND then
3335 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_RCV_TRANSACTION');
3336 FND_MESSAGE.SET_TOKEN('RCVID', p_reference_id);
3337 FND_MSG_PUB.ADD;
3338 raise FND_API.G_EXC_ERROR;
3339 END;
3340 end if;
3341 -- end if; OPM INVCONV
3342
3343 x_transaction_date := l_transaction_date;
3344 x_return_status := G_RET_STS_SUCCESS;
3345 fnd_msg_pub.count_and_get(
3346 p_encoded => fnd_api.g_false,
3347 p_count => x_msg_count,
3348 p_data => x_msg_data
3349 );
3350
3351 return l_organization_id;
3352 EXCEPTION
3353 WHEN FND_API.G_EXC_ERROR then
3354 x_return_status := G_RET_STS_ERROR;
3355 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3356 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3357
3358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3359 x_return_status := G_RET_STS_UNEXP_ERROR;
3360 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3361 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3362
3363 WHEN OTHERS then
3364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3365 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3366 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3367
3368 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3369 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, 'Get_Inventory_Org');
3370 end if;
3371
3372 print_debug('in When Others, l_progress = ' || l_progress, 'GET_INVENTORY_ORG');
3373
3374 END GET_INVENTORY_ORG;
3375
3376 /**
3377 * Helper function to get the functioanl currency code of an operating unit
3378 */
3379 Function get_functional_currency_code(
3380 p_org_id IN NUMBER,
3381 x_sets_of_book_id OUT NOCOPY NUMBER,
3382 x_return_status OUT NOCOPY VARCHAR2,
3383 x_msg_data OUT NOCOPY VARCHAR2,
3384 x_msg_count OUT NOCOPY NUMBER) return VARCHAR2
3385 IS
3386 l_functional_currency_code VARCHAR2(31);
3387 l_progress NUMBER := 0;
3388 l_set_of_book_id NUMBER;
3389 lreturn_status VARCHAR2(1);
3390 lmsg_data VARCHAR2(100);
3391 lsob_id NUMBER;
3392 lcoa_id NUMBER;
3393
3394 BEGIN
3395 l_functional_currency_code := '';
3396 x_return_status := G_RET_STS_SUCCESS;
3397 x_msg_data := null;
3398 x_msg_count := 0;
3399
3400 print_debug('Start Get_Functional_Currency_Code', 'Get_Functional_Currency_Code');
3401 print_debug('p_org_id ' || p_org_id, 'Get_Functional_Currency_Code');
3402
3403 -- print_Debug('Get the set of books', 'get_functional_currency_code');
3404 -- Modified the message text set of books to ledger for making the message compatible with LE uptake project
3405 print_Debug('Get the ledgers', 'get_functional_currency_code');
3406
3407 /* commented the selection of COA using LE - OU link which is obsoleted in R12
3408 and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
3409 Bug No - 4336479
3410 BEGIN
3411 l_progress := 1;
3412 SELECT to_number(LEI.org_information1)
3413 into l_set_of_book_id
3414 FROM HR_ORGANIZATION_INFORMATION LEI, HR_ORGANIZATION_UNITS OU,
3415 HR_ORGANIZATION_INFORMATION OUI
3416 WHERE OU.organization_id = p_org_id
3417 AND LEI.org_information_context = 'Legal Entity Accounting'
3418 AND to_char(LEI.organization_id) = OUI.org_information2
3419 AND OUI.org_information_context = 'Operating Unit Information'
3420 AND OUI.organization_id = OU.organization_id;
3421 EXCEPTION
3422 when no_data_found then
3423 -- print_Debug('cannot find the set of book of the ou ', 'Get_Functional_Currency_Code');
3424 -- Modified the message text set of books to ledger for making the message compatible with LE uptake project
3425 print_Debug('cannot find the ledger of the ou ', 'Get_Functional_Currency_Code');
3426 FND_MESSAGE.SET_NAME('INV', 'IC-INVALID BOOKS');
3427 FND_MESSAGE.SET_TOKEN('ID', p_org_id);
3428 FND_MSG_PUB.ADD;
3429 raise FND_API.G_EXC_ERROR;
3430 END;
3431 */
3432 BEGIN
3433 l_progress := 1;
3434 Inv_globals.get_ledger_info(
3435 x_return_status => lreturn_status,
3436 x_msg_data => lmsg_data ,
3437 p_context_type => 'Operating Unit Information',
3438 p_org_id => p_org_id,
3439 x_sob_id => lsob_id,
3440 x_coa_id => lcoa_id,
3441 p_account_info_context => 'SOB');
3442 IF NVL(lreturn_status , 'S') = 'E' THEN
3443 FND_MESSAGE.SET_NAME('INV', 'IC-INVALID BOOKS');
3444 FND_MESSAGE.SET_TOKEN('ID', p_org_id);
3445 FND_MSG_PUB.ADD;
3446 print_debug('Cannot find the ledger information for operating unit = '||p_org_id , 9);
3447 RAISE FND_API.G_EXC_ERROR;
3448 END IF;
3449 l_set_of_book_id := lsob_id;
3450 END;
3451
3452
3453
3454 l_progress := 2;
3455 BEGIN
3456 select currency_code
3457 into l_functional_currency_code
3458 FROM gl_sets_of_books
3459 WHERE set_of_books_id = l_set_of_book_id;
3460 EXCEPTION
3461 when NO_DATA_FOUND then
3462 print_debug('cannot find the functional currency code', 'get_functional_currency_code');
3463 FND_MESSAGE.SET_NAME('SQLGL', 'GL funct curr does not exist');
3464 FND_MSG_PUB.ADD;
3465 raise FND_API.G_EXC_ERROR;
3466 end;
3467
3468 print_debug('l_functional_currency_code is ' || l_functional_currency_code, 'get_functional_currency_code');
3469 x_sets_of_book_id := l_set_of_book_id;
3470 return l_functional_currency_code;
3471
3472 EXCEPTION
3473 WHEN FND_API.G_EXC_ERROR then
3474 x_return_status := G_RET_STS_ERROR;
3475 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3476 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3477 return null;
3478 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3479 x_return_status := G_RET_STS_UNEXP_ERROR;
3480 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3481 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3482 return null;
3483
3484 WHEN OTHERS THEN
3485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3486 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3487 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3488
3489 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3490 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, 'get_functional_currency_code');
3491 end if;
3492
3493 print_debug('in When Others, l_progress = ' || l_progress, 'get_functional_currency_code');
3494 print_Debug(sqlerrm, 'get_functional_currency_code');
3495 return null;
3496
3497 END get_functional_currency_code;
3498
3499
3500 /* Package: INV_TRANSACTION_FLOWS_PUB
3501 * Function: convert_currency (
3502 * Description: This function is used to convert the transfer price
3503 * to the functional currency of a particular operating unit.
3504 *
3505 * Inputs:
3506 * 1. p_org_id - the operating unit to which functional currency the
3507 * transfer price will be converted.
3508 * 2. p_transfer_price - the amount to be converted.
3509 * 3. p_currency_code - the original currency code to be converted to functional
3510 * currency
3511 * 4. p_transaction_date - the date for which the conversion rate is used
3512 *
3513 * Output:
3514 * 1. x_functional_currency_code - the functional currency code of the p_org_id
3515 * 2. x_return_status - return status
3516 * 3. x_msg_data - the message on the message stack.
3517 * 4. x_msg_count - the number of message in the message stack
3518 */
3519
3520 FUNCTION CONVERT_CURRENCY (
3521 p_org_id IN NUMBER
3522 , p_transfer_price IN NUMBER
3523 , p_currency_code IN VARCHAR2
3524 , p_transaction_date IN DATE
3525 , p_logical_txn IN VARCHAR2 DEFAULT 'N' /* bug 6696446 */
3526 , x_functional_currency_code OUT NOCOPY VARCHAR2
3527 , x_return_status OUT NOCOPY VARCHAR2
3528 , x_msg_data OUT NOCOPY VARCHAR2
3529 , x_msg_count OUT NOCOPY NUMBER
3530 ) RETURN NUMBER
3531 IS
3532 l_functional_currency_code VARCHAR2(30);
3533 l_set_of_book_id NUMBER;
3534 l_fixed_rate VARCHAR2(4) := 'N';
3535 l_conversion_type VARCHAR2(31);
3536 l_conversion_rate NUMBER;
3537 l_transfer_price NUMBER;
3538 l_progress NUMBER;
3539 BEGIN
3540 x_functional_currency_code := '';
3541 x_return_status := G_RET_STS_SUCCESS;
3542 x_msg_data := null;
3543 x_msg_count := 0;
3544
3545 print_debug('Start Convert_Currency', 'Convert_Currency');
3546 print_debug('p_org_id p_transfer_price p_currency_code p_transaction_date', 'Convert_currency');
3547 print_Debug(p_org_id || ' ' || p_transfer_price || ' ' || p_currency_code || ' ' || p_transaction_date,
3548 'Convert_currency');
3549
3550 if( G_FROM_ORG_ID = p_org_id ) THEN
3551 l_functional_currency_code := G_FUNCTIONAL_CURRENCY_CODE;
3552 l_set_of_book_id := G_SETS_OF_BOOK_ID;
3553 elsif( G_FROM_ORG_ID = -1 OR G_FROM_ORG_ID <> p_org_id ) then
3554 G_FROM_ORG_ID := p_org_id;
3555 l_functional_currency_code := get_functional_currency_code(
3556 p_org_id, l_set_of_book_id, x_return_status, x_msg_data, x_msg_count);
3557 if( x_return_status <> G_RET_STS_SUCCESS ) then
3558 raise FND_API.G_EXC_ERROR;
3559 else
3560 G_FUNCTIONAL_CURRENCY_CODE := l_functional_currency_code;
3561 G_SETS_OF_BOOK_ID := l_set_of_book_id;
3562 end if;
3563 end if;
3564
3565 x_functional_currency_code := l_functional_currency_code;
3566 print_debug('l_functional_currency_code is ' || l_functional_currency_code, 'convert_currency');
3567 print_Debug('calling gl_currency_api.is_fix_rate', 'convert_currency');
3568
3569 if( l_functional_currency_code <> p_currency_code ) then
3570 print_Debug('calling gl_currency_api.is_fix_rate', 'convert_currency');
3571 l_fixed_rate := gl_currency_api.is_fixed_rate(
3572 p_currency_code, l_functional_currency_code, p_transaction_date);
3573
3574 print_debug('l_fixed_rate is ' || l_fixed_rate, 'convert_currency');
3575 l_conversion_type := fnd_profile.value('IC_CURRENCY_CONVERSION_TYPE');
3576 if( l_fixed_rate = 'Y' ) then
3577 l_conversion_type := 'EMU FIXED';
3578 end if;
3579
3580 print_Debug('l_conversion_type is ' || l_conversion_type, 'convert_currency');
3581 print_Debug('l_set_of_book_id is ' || l_set_of_book_id, 'convert_currency');
3582 print_Debug('p_currency_code is ' || p_currency_code, 'convert_currency');
3583 print_Debug('p_transfer_price is ' || p_transfer_price, 'convert_currency');
3584 print_Debug('p_transaction_date is ' || p_transaction_date, 'convert_currency');
3585
3586 -- Added following for bug 6696446
3587 print_Debug('p_logical_txn is ' || p_logical_txn, 'convert_currency');
3588
3589 IF p_logical_txn = 'Y' THEN /* Added if condition for bug 6696446 */
3590
3591 print_debug('calling gl_currency_api.get_rate_sql ', 'convert_currency');
3592 l_conversion_rate := gl_currency_api.get_rate_sql(
3593 x_set_of_books_id => l_set_of_book_id
3594 , x_from_currency => p_currency_code
3595 , x_conversion_date => p_transaction_date
3596 , x_conversion_type => l_conversion_type
3597 );
3598 print_debug('l_conversion_rate is '||l_conversion_rate, 'convert_currency');
3599
3600 IF l_conversion_rate = -1 THEN
3601 l_transfer_price := -1;
3602 ELSIF l_conversion_rate = -2 THEN
3603 l_transfer_price := -2;
3604 ELSE
3605 print_debug('Before conversion p_transfer_price : '||p_transfer_price, 'convert_currency');
3606 print_debug('l_conversion_rate : '||l_conversion_rate, 'convert_currency');
3607
3608 l_transfer_price := p_transfer_price * l_conversion_rate;
3609 print_debug('After conversion l_transfer_price : '|| l_transfer_price, 'convert_currency');
3610 END IF;
3611
3612 ELSIF p_logical_txn = 'N' THEN
3613
3614 print_debug('calling gl_currency_api.converT_amount_sql ', 'convert_currency');
3615 l_transfer_price := gl_currency_api.convert_amount_sql(
3616 x_set_of_books_id => l_set_of_book_id
3617 , x_from_currency => p_currency_code
3618 , x_conversion_date => p_transaction_date
3619 , x_conversion_type => l_conversion_type
3620 , x_amount => p_transfer_price
3621 );
3622
3623 END IF; /* p_logical_txn = 'Y' */
3624
3625 if( l_transfer_price = -1 ) then
3626 print_debug('ic no conversion rate', 'convert_currency');
3627 FND_MESSAGE.SET_NAME('INV', 'IC-No conversion rate');
3628 FND_MESSAGE.SET_TOKEN('CONV_TYPE', l_conversion_type);
3629 FND_MESSAGE.SET_TOKEN('FROM_CURR', p_currency_code);
3630 FND_MESSAGE.SET_TOKEN('TO_CURR', l_functional_currency_code);
3631 FND_MSG_PUB.ADD;
3632 raise FND_API.G_EXC_ERROR;
3633 elsif( l_transfer_price = -2 ) then
3634 print_Debug('ic invalid currency', 'convert_currency');
3635 FND_MESSAGE.SET_NAME('INV', 'IC-Invalid_Currency');
3636 FND_MESSAGE.SET_TOKEN('FROM_CURR', p_currency_code);
3637 FND_MSG_PUB.ADD;
3638 raise FND_API.G_EXC_ERROR;
3639 end if;
3640 else
3641 l_transfer_price := p_transfer_price;
3642 end if;
3643
3644 print_debug('l_transfer_price is ' || l_transfer_price, 'convert_currency');
3645 x_return_status := G_RET_STS_SUCCESS;
3646 fnd_msg_pub.count_and_get (
3647 p_encoded => fnd_api.g_false,
3648 p_count => x_msg_count,
3649 p_data => x_msg_data
3650 );
3651
3652
3653 return l_transfer_price;
3654
3655 EXCEPTION
3656 WHEN FND_API.G_EXC_ERROR then
3657 x_return_status := G_RET_STS_ERROR;
3658 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3659 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3660 return -99;
3661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3662 x_return_status := G_RET_STS_UNEXP_ERROR;
3663 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3664 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3665 return -99;
3666
3667 WHEN OTHERS THEN
3668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3669 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3670 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3671
3672 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3673 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, 'Convert_Currency');
3674 end if;
3675
3676 print_debug('in When Others, l_progress = ' || l_progress, 'Convert_Currency');
3677 print_Debug(sqlerrm, 'Convert_Currency');
3678 return -99;
3679
3680 end convert_currency;
3681
3682 /*==========================================================================================================
3683 * Package: INV_TRANSACTION_FLOWS_PUB
3684 *
3685 * Procedure: GET_TRANSFER_PRICE_FOR_ITEM
3686 *
3687 * Description:
3688 * This API gets the transfer price in the transaction UOM using the following defaulting mechanism:
3689 * 1. list price at transaction UOM I established transfer price list
3690 * 2. Transaction cost of shipment transaction.
3691 * This API will be called by Oracle Inventory as well as Oracle CTO for CTO item
3692 *
3693 * Inputs:
3694 * - From_Org_ID - the start operating unit
3695 * - To_Org_Id - The End operating Unit
3696 * - Transaction_UOM - the transaction units of meassure
3697 * - Invenotry_Item_ID - the inventory item identifier
3698 * - Transaction ID - the logical transaction id
3699 * - price_list_id - the static price list id.
3700 *
3701 * Outputs:
3702 * - x_transfer_price - the unit transfer price of the item
3703 * - x_currency_code - the currency code of the transfer price
3704 * - x_return_Status - the return status
3705 * - x_msg_data - the error message
3706 * - x_msg_count - the message count
3707 *
3708 * History:
3709 * umoogala 21-Apr-2006 Bug 5171637/5138311: Process/Discrete Xfers Enh.
3710 * Removed parameter p_process_discrete_xfer_flag added as part of above fix, and replaced it
3711 * with p_order_line_id to make it clear. Fix for bug 5126431 caused ORA error. The get_transfer_price
3712 * API expects, transaction_id. But, different programs pass different value in transaction_id.
3713 * --
3714 * INV: Logical Txn procedure (INVTLTPBB.pls) puts order line_id as transaction_id with
3715 * global_procurement flag to N.
3716 * INV: InterCompany Invocing Program: sends mmt.transaction_id.
3717 * RCV: puts rcv_transaction_id as transaction_id with global_procurement flag to 'Y'
3718 * and drop_ship_flag to 'N'.
3719 * GMF: calls this API with order line_id as transaction_id
3720 * --
3721 *
3722 *============================================================================================================*/
3723 Procedure get_transfer_price_for_item
3724 (
3725 x_return_status OUT NOCOPY VARCHAR2
3726 , x_msg_data OUT NOCOPY VARCHAR2
3727 , x_msg_count OUT NOCOPY NUMBER
3728 , x_transfer_price OUT NOCOPY NUMBER
3729 , x_currency_code OUT NOCOPY VARCHAR2
3730 , p_api_version IN NUMBER
3731 , p_init_msg_list IN VARCHAR2
3732 , p_from_org_id IN NUMBER
3733 , p_to_org_id IN NUMBER
3734 , p_transaction_uom IN VARCHAR2
3735 , p_inventory_item_id IN NUMBER
3736 , p_transaction_id IN NUMBER
3737 , p_from_organization_id IN NUMBER DEFAULT null
3738 , p_price_list_id IN NUMBER
3739 , p_global_procurement_flag IN VARCHAR2
3740 , p_drop_ship_flag IN VARCHAR2 DEFAULT 'N'
3741 , p_cto_item_flag IN VARCHAR2 DEFAULT 'N'
3742 -- , p_process_discrete_xfer_flag IN VARCHAR2 DEFAULT 'N' -- Bug 4750256
3743 , p_order_line_id IN VARCHAR2 DEFAULT NULL
3744 -- Bug 5171637/5138311 umoogala:
3745 -- replaced above line with this one.
3746 ) IS
3747 l_invoice_currency_code VARCHAR2(30);
3748 l_return_Status VARCHAR2(1);
3749 l_msg_data VARCHAR2(255);
3750 l_msg_count NUMBER;
3751 l_debug NUMBER := nvl(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
3752 l_transfer_price NUMBER := 0;
3753 l_qp_profile NUMBER := nvl(fnd_profile.value('INV_USE_QP_FOR_INTERCOMPANY'), 2);
3754 l_transfer_price_code NUMBER := -1;
3755 l_organization_id NUMBER;
3756 l_functional_currency_code VARCHAR2(30);
3757 l_uom_rate NUMBER;
3758 l_primary_uom VARCHAR2(4);
3759 l_transaction_date DATE;
3760 l_fixed_rate VARCHAR2(1);
3761 l_progress NUMBER;
3762
3763 l_api_version_number CONSTANT NUMBER := 1.0;
3764 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRANSFER_PRICE_FOR_ITEM';
3765 l_flow_type NUMBER := G_SHIPPING_FLOW_TYPE;
3766 l_transaction_id NUMBER;
3767 l_order_line_id NUMBER;
3768 l_order_header_id NUMBER;
3769 l_inventory_item_id NUMBER;
3770 l_currency_code VARCHAR2(30);
3771 l_currency_org NUMBER;
3772 l_qp_price_flag BOOLEAN := false;
3773 l_set_of_book_id NUMBER;
3774 l_inv_currency_code NUMBER;
3775 l_count NUMBER := 0;
3776 l_item_description VARCHAR2(255);
3777 l_price_list_name VARCHAR2(255);
3778
3779 l_from_ou_name VARCHAR2(255);
3780 l_to_ou_name VARCHAR2(255);
3781 l_cto_item_flag VARCHAR2(1) := p_cto_item_flag;
3782 l_ato_line_id number; --bug 5126431
3783 l_trf_price_date date; --bug 6700919
3784 BEGIN
3785 x_return_status := G_RET_STS_SUCCESS;
3786 x_msg_data := null;
3787 x_msg_count := 0;
3788 x_transfer_price := 0;
3789 x_currency_code := null;
3790
3791 -- Standard call to check for call compatibility
3792 IF NOT FND_API.Compatible_API_Call(
3793 l_api_version_number
3794 , p_api_version
3795 , l_api_name
3796 , G_PKG_NAME)
3797 THEN
3798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3799 END IF;
3800
3801 -- Initialize message list.
3802 IF fnd_api.to_boolean(p_init_msg_list) THEN
3803 fnd_msg_pub.initialize;
3804 END IF;
3805
3806 --
3807 -- Bug 5171637/5138311 umoogala: INVCONV
3808 -- Added following IF condition.
3809 -- We calling this API from Inventory Interface when Sales Order is
3810 -- ship confirmed. So, we do not have MMT transaction_id at this point.
3811 -- For this reason, we are sending order_line_id, which gets used by
3812 -- Advanced Pricing Engine.
3813 --
3814 IF p_order_line_id IS NOT NULL
3815 THEN
3816 l_order_line_id := p_order_line_id;
3817 ELSE
3818
3819 --- Bug 9458617. Added the p_global_procurement_flag check.
3820 -- Bug 9851507 Added p_drop_ship_flag check.
3821 IF (nvl(p_global_procurement_flag,'N') = 'N' and p_drop_ship_flag = 'N') THEN
3822
3823 BEGIN --5126431
3824 IF l_cto_item_flag = 'Y' THEN
3825
3826 select l.ato_line_id,l.header_id
3827 into l_ato_line_id,l_order_header_id
3828 from mtl_material_transactions mmt
3829 , oe_order_lines_all l
3830 WHERE MMT.transaction_id = p_transaction_id
3831 AND l.line_id = mmt.trx_source_line_id;
3832
3833 SELECT line_id
3834 INTO l_order_line_id
3835 from oe_order_lines_all
3836 where header_id=l_order_header_id
3837 and ato_line_id=l_ato_line_id
3838 and inventory_item_id=p_inventory_item_id;
3839 ELSE
3840
3841 SELECT trx_source_line_id
3842 INTO l_order_line_id
3843 FROM mtl_material_transactions
3844 WHERE transaction_id = p_transaction_id;
3845
3846 END IF;
3847 EXCEPTION
3848 when no_data_found then
3849 l_order_header_id := NULL;
3850 l_order_line_id := NULL;
3851 l_ato_line_id := NULL;
3852 END;
3853 --End of 5126431
3854 END IF;
3855 END IF;
3856
3857 --
3858 -- Bug 5527437 umoogala
3859 --
3860 IF (p_order_line_id IS NULL AND p_transaction_id IS NULL AND p_cto_item_flag = 'Y')
3861 THEN
3862 l_order_line_id := G_ORDER_LINE_ID;
3863 END IF;
3864
3865
3866 print_debug('START GET_TRANSFER_PRICE_FOR_ITEM', 'GET_TRANSFER_PRICE_FOR_ITEM');
3867 print_debug('Input Parameter ' , 'GET_TRANSFER_PRICE_FOR_ITEM');
3868 print_debug('--------------- ' , 'GET_TRANSFER_PRICE_FOR_ITEM');
3869 print_debug(' p_api_version = ' || p_api_version, 'GET_TRANSFER_PRICE_FOR_ITEM');
3870 print_debug(' p_init_msg_list = ' || p_init_msg_list, 'GET_TRANSFER_PRICE_FOR_ITEM');
3871 print_debug(' p_from_org_id = ' || p_from_org_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3872 print_debug(' p_to_org_id = ' || p_to_org_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3873 print_debug(' p_transaction_uom = ' || p_transaction_uom, 'GET_TRANSFER_PRICE_FOR_ITEM');
3874 print_debug(' p_inventory_item_id = ' || p_inventory_item_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3875 print_debug(' p_transaction_id = ' || p_transaction_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3876 print_debug(' p_price_list_id = ' || p_price_list_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3877 print_debug(' p_global_procurement_flag = ' || p_global_procurement_flag, 'GET_TRANSFER_PRICE_FOR_ITEM');
3878 print_debug(' p_drop_ship_flag = ' || p_drop_ship_flag, 'GET_TRANSFER_PRICE_FOR_ITEM');
3879 print_debug(' p_from_organization_id = ' || p_from_organization_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3880 print_debug(' p_cto_item_flag = ' || p_cto_item_flag, 'GET_TRANSFER_PRICE_FOR_ITEM');
3881 -- print_debug(' p_process_discrete_xfer_flag = ' || p_process_discrete_xfer_flag, 'GET_TRANSFER_PRICE_FOR_ITEM'); /* INVCONV Bug 4750256 */
3882 print_debug(' l_order_header_id = ' || l_order_header_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3883 print_debug(' l_ato_line_id = ' || l_ato_line_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3884 print_debug(' l_order_line_id = ' || l_order_line_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3885 print_debug(' G_ORDER_LINE_ID = ' || G_ORDER_LINE_ID, 'GET_TRANSFER_PRICE_FOR_ITEM');
3886
3887
3888
3889 print_debug('Calling mtl_intercompany_invoices.get_transfer_price ',
3890 'Get_Transfer_Price_For_Item');
3891
3892 -- start of bug9851507
3893 if( nvl(p_global_procurement_flag,'N') = 'N' and p_drop_ship_flag = 'Y') then
3894 l_order_line_id := nvl(l_order_line_id, p_transaction_id);
3895 end if;
3896 --- End of bug9851507
3897
3898 --5126431: Added new parameter I_order_line_id
3899
3900 l_transfer_price := MTL_INTERCOMPANY_INVOICES.get_transfer_price(
3901 I_transaction_id => p_transaction_id
3902 , I_price_list_id => p_price_list_id
3903 , I_sell_ou_id => p_to_org_id
3904 , I_ship_ou_id => p_from_org_id
3905 , O_currency_code => l_invoice_currency_code
3906 , x_return_status => l_return_status
3907 , x_msg_count => l_msg_count
3908 , x_msg_data => l_msg_data
3909 , I_order_line_id => l_order_line_id
3910 );
3911 print_debug('Return Status from External API is ' || l_return_Status, 'Get_transfer_price_for_item');
3912 print_debug('Message Data from External API is ' || l_msg_data, 'Get_transfer_price_for_item');
3913 print_debug('Message Count from External API is ' || l_msg_count, 'Get_transfer_price_for_item');
3914 print_debug('Transfer price = ' || l_transfer_price, 'Get_transfer_price_for_item');
3915 print_debug('Currency code = ' || l_invoice_currency_code, 'Get_transfer_price_for_item');
3916
3917 if( l_return_status <> G_RET_STS_SUCCESS ) then
3918 print_Debug('Error from mtl_intercompany_invoices.get_transfer_price', 'get_transfer_price_for_item');
3919 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_EXT_TRANSFER_PRICE');
3920 FND_MSG_PUB.ADD;
3921 raise FND_API.G_EXC_ERROR;
3922 end if;
3923
3924 if( nvl(l_transfer_price, -1) = -1 ) then
3925 -- This means that we get nothing from the external api
3926 -- try to get the transfer price using the QP Engine
3927
3928 if( p_from_organization_id is not null ) then
3929 l_organization_id := p_from_organization_id;
3930 l_transaction_date := sysdate;
3931 else
3932 print_debug('p_from_organization_id is null, need to call get_inventory_org', 'get_transfer_price_for_item');
3933 print_debug('Calling get_inventory_org', 'get_transfer_price_for_item');
3934 l_organization_id := get_inventory_org(
3935 p_reference_id => p_transaction_id
3936 , p_global_procurement_flag => p_global_procurement_flag
3937 , p_drop_ship_flag => p_drop_ship_flag
3938 , x_transaction_date => l_transaction_date
3939 , x_return_status => l_return_status
3940 , x_msg_data => l_msg_data
3941 , x_msg_count => l_msg_count
3942 );
3943 print_debug('l_organization_id = ' || l_organization_id, 'geT_transfer_price_for_item');
3944
3945 if( l_return_status <> G_RET_STS_SUCCESS ) then
3946 print_debug('Error from get_inventory_org', 'get_transfer_price_for_item');
3947 raise FND_API.G_EXC_ERROR;
3948 end if;
3949 end if;
3950
3951 print_debug('l_qp_profile = ' || l_qp_profile, 'get_transfer_price_for_item');
3952 print_debug('l_qp_status = ' || qp_util.get_qp_status, 'get_transfer_price_for_item');
3953
3954 if( QP_UTIL.get_qp_status <> 'I' OR l_qp_profile <> 1 ) then
3955 print_debug('QP is not install', 'Get_transfer_price_for_item');
3956 print_debug('QP PRofile set to NO', 'Get_Transfer_Price_For_Item');
3957 print_debug('Get the static price list in transaction_uom', 'get_transfer_price_for_item');
3958 /* bug 6700919 Calling get_transfer_price_date to retreive the date
3959 by which price list price will be queried*/
3960
3961 print_debug('Calling get_transfer_price_date', 'Get_Transfer_Price_For_Item');
3962 l_trf_price_date := get_transfer_price_date(
3963 p_call => 'I'
3964 ,p_order_line_id => l_order_line_id
3965 ,p_global_procurement_flag => p_global_procurement_flag
3966 ,p_transaction_id => p_transaction_id
3967 ,p_drop_ship_flag => p_drop_ship_flag
3968 ,x_return_status => l_return_status
3969 ,x_msg_data => l_msg_data
3970 ,x_msg_count => l_msg_count
3971 );
3972
3973 print_debug('l_trf_price_date ='||l_trf_price_date, 'Get_Transfer_Price_For_Item');
3974 if( l_return_status <> G_RET_STS_SUCCESS ) then
3975 print_debug('Error from get_transfer_price_date', 'get_transfer_price_for_item');
3976 raise FND_API.G_EXC_ERROR;
3977 end if;
3978
3979 BEGIN
3980 l_transfer_price_code := 1;
3981 /*Bug: 5054047 Modified the SQL*/
3982
3983 /*Bug: 13566324 Modified the SQL*/
3984
3985 select SPLL.operand, substr(SPL.currency_code, 1, 15)
3986 INTO l_transfer_price, l_invoice_currency_code
3987 FROM qp_list_headers_b spl, qp_list_lines SPLL, qp_pricing_attributes qpa
3988 WHERE SPL.list_header_id = p_price_list_id
3989 AND SPLL.list_header_id = SPL.list_header_id
3990 AND SPLL.list_line_id = qpa.list_line_id
3991 AND qpa.product_attribute_context = 'ITEM'
3992 AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
3993 AND qpa.product_attr_value = to_Char(p_inventory_item_id)
3994 AND qpa.product_uom_code = p_transaction_uom
3995 AND l_trf_price_date between nvl(SPLL.start_date_active, (l_trf_price_date-1)) AND
3996 nvl(SPLL.end_date_active+0.99999, (l_trf_price_date+1)) --bug 6700919 changed sysdate to l_trf_price_date
3997 --AND qpa.qualification_ind = 4
3998 AND qpa.pricing_attribute_context is null
3999 AND qpa.excluder_flag = 'N'
4000 AND qpa.pricing_phase_id=1
4001 AND rownum = 1;
4002
4003 print_debug('l_transfer_price = ' || l_transfer_price, 'get_transfer_price_for_item');
4004 print_debug('l_invoice_currency_code = ' || l_invoice_currency_code, 'get_transfeR_price_for_item');
4005
4006 EXCEPTION
4007 when no_data_found then
4008 print_debug('Get static price list in primary uom', 'get_transfeR_price_for_item');
4009 BEGIN
4010 l_transfer_price_code := 2;
4011 /*Bug: 5054047 Modified the SQL*/
4012
4013 /*Bug: 13566324 Modified the SQL*/
4014
4015 SELECT SPLL.operand, substr(SPL.currency_code, 1, 15), msi.primary_uom_code
4016 INTO l_transfer_price, l_invoice_currency_code, l_primary_uom
4017 FROM QP_LIST_HEADERS_B SPL, QP_LIST_LINES SPLL,
4018 QP_PRICING_ATTRIBUTES QPA, MTL_SYSTEM_ITEMS_B MSI
4019 WHERE MSI.organization_id = l_organization_id
4020 AND MSI.inventory_item_id = p_inventory_item_id
4021 AND SPL.list_header_id = p_price_list_id
4022 AND SPLL.list_header_id = SPL.list_header_id
4023 AND QPA.list_header_id = SPL.list_header_id
4024 AND SPLL.list_line_id = QPA.list_line_id
4025 AND QPA.product_attribute_context = 'ITEM'
4026 AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
4027 AND QPA.product_attr_value = to_char(MSI.inventory_item_id)
4028 AND QPA.product_uom_code = MSI.primary_uom_code
4029 AND l_trf_price_date between nvl(SPLL.start_date_active, (l_trf_price_date-1))
4030 AND nvl(SPLL.end_date_active + 0.99999, (l_trf_price_date+1)) --bug 6700919 changed sysdate to l_trf_price_date
4031 --AND qpa.qualification_ind = 4
4032 AND qpa.pricing_attribute_context is null
4033 AND qpa.excluder_flag = 'N'
4034 AND qpa.pricing_phase_id=1
4035 AND rownum = 1;
4036 EXCEPTION
4037 when no_data_found THEN
4038 print_debug('no price list found', 'get_transfer_price_for_item');
4039
4040 if( l_cto_item_flag = 'Y' ) then
4041 l_transfer_price := -99;
4042 else
4043 l_transfer_price := -99;
4044 l_return_status := G_RET_STS_ERROR;
4045 SELECT concatenated_segments, primary_uom_code
4046 INTO l_item_description, l_primary_uom
4047 FROM mtl_system_items_kfv
4048 WHERE organization_id = l_organization_id
4049 AND inventory_item_id = p_inventory_item_id;
4050
4051 SELECT name
4052 into l_price_list_name
4053 FROM QP_LIST_HEADERS
4054 WHERE list_header_id = p_price_list_id;
4055
4056 FND_MESSAGE.SET_NAME('QP', 'QP_PRC_NO_LIST_PRICE');
4057 FND_MESSAGE.SET_TOKEN('ITEM', l_item_description);
4058 FND_MESSAGE.SET_TOKEN('UNIT', l_primary_uom);
4059 FND_MESSAGE.SET_TOKEN('PRICE_LIST', l_price_list_name);
4060 FND_MSG_PUB.ADD;
4061 raise fnd_api.g_exc_error;
4062 end if;
4063 end;
4064 when others then
4065 print_debug('sqlerrm = ' || sqlerrm, 'get_transfer_price_for_item');
4066 l_transfer_price := 0;
4067 l_return_status := G_RET_STS_ERROR;
4068 FND_MESSAGE.SET_NAME('INV', 'IC-No Transfer Price');
4069 FND_MSG_PUB.ADD;
4070 raise fnd_api.g_exc_error;
4071 END;
4072 else
4073 l_qp_price_flag := TRUE;
4074 if( p_global_procurement_flag = 'Y' ) then
4075 l_flow_type := G_PROCURING_FLOW_TYPE;
4076 end if;
4077 print_Debug('after setting the l_flow_type ', 'Get_transfer_price_for_item');
4078
4079 --
4080 -- Setting order_line_id and transaction_id
4081 --
4082 IF (p_order_line_id IS NULL AND p_transaction_id IS NULL AND p_cto_item_flag = 'Y') THEN
4083 print_Debug('Value of l_order_line_id = '||l_order_line_id, 'Get_transfer_price_for_item');
4084 ELSIF p_order_line_id IS NOT NULL
4085 then
4086 --
4087 -- Bug 5171637/5138311 umoogala: INVCONV
4088 -- Added this IF block
4089 --
4090 l_order_line_id := p_order_line_id;
4091 l_transaction_id := null;
4092 else
4093 if( p_drop_ship_flag = 'Y' )
4094 then
4095 l_order_line_id := nvl(l_order_line_id, p_transaction_id);
4096 l_transaction_id := null;
4097 --
4098 -- Bug 5171637/5138311 umoogala: Commented following code and
4099 -- replaced with first IF condition above.
4100 --
4101 -- Bug 4750256, OPM INVCONV: Added this elsif block
4102 -- elsif( p_drop_ship_flag = 'N' AND p_process_discrete_xfer_flag = 'Y')
4103 -- then
4104 -- l_order_line_id := p_transaction_id;
4105 -- l_transaction_id := null;
4106 else
4107 print_debug(' p_drop_ship_flag is ' || p_drop_ship_flag, 'Get_transfer_price_for_item');
4108 if( p_cto_item_flag = 'N') then
4109 l_transaction_id := p_transaction_id;
4110 l_order_line_id := null;
4111 else
4112 BEGIN
4113 select 1
4114 INTO l_count
4115 From mtl_material_transactions
4116 WHERE transaction_id = p_transaction_id;
4117
4118 l_transaction_id := p_transaction_id;
4119 l_order_line_id := null;
4120 EXCEPTION
4121 WHEN no_data_found then
4122 l_order_line_id := p_transaction_id;
4123 l_transaction_id := null;
4124 END;
4125 end if;
4126 end if;
4127 end if;
4128
4129 print_debug('After setting the l_order_line_id ' || l_order_line_id || ' l_transaction_id ' || l_transaction_id,
4130 'Get_Transfer_price_for_item');
4131
4132 /* BEGIN
4133 print_debug('about to delete the qp temp table', 'Get_Transfer_price_for_item');
4134 select count(*)
4135 into l_count
4136 From qp_preq_lines_tmp;
4137
4138 if( l_count > 0 ) then
4139 Delete from qp_preq_lines_tmp_t;
4140 end if;
4141 EXCEPTION
4142 when others then
4143 fnd_message.set_name('INV', 'INV_INT_SQLCODE');
4144 fnd_msg_pub.add;
4145 raise FND_API.G_EXC_ERROR;
4146 end;*/
4147
4148 /* Added this for bug 3141793
4149 * The currency code in which the transfer price will be return is control by the
4150 * value of inv_currency_code column in the mtl_intercompany_parameters
4151 * The possible value of inv_currency_code are:
4152 * 1 - Currency of the SHip/From/Procuring OU
4153 * 2 - Currency of the Sell/To/Receiving OU
4154 * 3 - Order Currency (Sales Order/Purchase Order)
4155 * We will cache the OU in which the currency will return
4156 * And also will cache the currency code
4157 */
4158 if( p_global_procurement_flag = 'Y' ) then
4159 l_flow_type := 2;
4160 else
4161 l_flow_type := 1;
4162 end if;
4163
4164 /* Bug 4903269 moved the call in order to get the option of currency of sales order to work */
4165 BEGIN
4166 select nvl(inv_currency_code, 1)
4167 into l_inv_currency_code
4168 From mtl_intercompany_parameters
4169 where ship_organization_id = p_from_org_id
4170 and sell_organization_id = p_to_org_id
4171 and flow_type = l_flow_type;
4172 EXCEPTION
4173 when no_data_found then
4174 print_debug('No IC Relations exists between from OU and To OU', 'GET_TRANSFER_PRICE_FOR_ITEM');
4175 SELECT name
4176 INTO l_from_ou_name
4177 FROM hr_operating_units
4178 WHERE organization_id = p_from_org_id;
4179
4180
4181 SELECT name
4182 INTO l_to_ou_name
4183 FROM hr_operating_units
4184 WHERE organization_id = p_to_org_id;
4185
4186 FND_MESSAGE.SET_NAME('INV', 'IC-No INTERCO RELATION');
4187 FND_MESSAGE.SET_TOKEN('FROM_OU', l_from_ou_name);
4188 FND_MESSAGE.SET_TOKEN('TO_OU', l_to_ou_name);
4189 FND_MSG_PUB.ADD;
4190 raise FND_API.G_EXC_ERROR;
4191 END;
4192
4193
4194
4195 if( (G_FROM_ORG_ID = -1 AND G_TO_ORG_ID = -1 AND G_INV_CURR_ORG = -1)
4196 OR (G_FROM_ORG_ID <> p_from_org_id ) OR (G_TO_ORG_ID <> p_to_org_id ) OR (G_FLOW_TYPE <>l_flow_type)) THEN
4197 -- This means that this API is called for the first time or the from OU and to OU
4198 -- is not the same as the last time when this API is called.
4199 -- Need to query the inv_currency_code from the database.
4200
4201 G_FROM_ORG_ID := p_from_org_id;
4202 G_TO_ORG_ID := p_to_org_id;
4203 G_FLOW_TYPE := l_flow_type;
4204
4205 if( l_inv_currency_code = 1 ) then
4206 l_currency_org := p_from_org_id;
4207 elsif( l_inv_currency_code = 2 ) then
4208 l_currency_org := p_to_org_id;
4209 else
4210 l_currency_org := 0;
4211 end if;
4212
4213 G_INV_CURR_ORG := l_currency_org;
4214
4215 G_FUNCTIONAL_CURRENCY_CODE := get_functional_currency_code(
4216 p_from_org_id, l_set_of_book_id, x_return_status, x_msg_data, x_msg_count);
4217
4218 G_SETS_OF_BOOK_ID := l_set_of_book_id;
4219
4220 if( l_currency_org > 0 ) then
4221 l_currency_code := get_functional_currency_code(
4222 l_currency_org, l_set_of_book_id, x_return_status, x_msg_data, x_msg_count);
4223
4224 if( x_return_status <> G_RET_STS_SUCCESS ) then
4225 raise FND_API.G_EXC_ERROR;
4226 end if;
4227 end if;
4228 G_INV_CURR_CODE := l_currency_code;
4229 ELSIF( (G_FROM_ORG_ID = p_from_org_id ) AND (G_TO_ORG_ID = p_to_org_id ) AND (G_FLOW_TYPE = l_flow_type) ) THEN
4230 l_currency_org := G_INV_CURR_ORG;
4231 l_currency_code := G_INV_CURR_CODE;
4232 --null;
4233 END IF;
4234
4235 print_Debug('Calling mtl_qp_price.get_transfer_price', 'Get_transfer_price_for_item');
4236 l_transfer_price := MTL_QP_PRICE.get_transfer_price_ds(
4237 p_transaction_id => l_transaction_id
4238 , p_sell_ou_id => p_to_org_id
4239 , p_ship_ou_id => p_from_org_id
4240 , p_flow_type => l_flow_type
4241 , p_order_line_id => l_order_line_id
4242 , p_inventory_item_id => p_inventory_item_id
4243 , p_organization_id => l_organization_id
4244 , p_uom_code => p_transaction_uom
4245 , p_cto_item_flag => p_cto_item_flag
4246 , p_incr_code => l_inv_currency_code
4247 , p_incrcurrency => l_currency_code
4248 , x_currency_code => l_invoice_currency_code
4249 , x_tfrPriceCode => l_transfer_price_code
4250 , x_return_status => l_return_status
4251 , x_msg_count => l_msg_count
4252 , x_msg_data => l_msg_data
4253 );
4254
4255 print_Debug('l_transfer_price is ' || l_transfer_price, 'get_transfer_price_for_item');
4256 print_Debug('l_transfer_price_code = ' || l_transfer_price_code, 'get_transfer_price_for_item');
4257 print_Debug('l_invoice_currency_code = ' || l_invoice_currency_code, 'get_transfer_price_for_item');
4258 print_Debug('l_return_status = ' || l_return_status, 'get_transfer_price_for_item');
4259 print_Debug('l_msg_data = ' || l_msg_data, 'get_transfer_price_for_item');
4260 print_Debug('l_msg_count = ' || l_msg_count, 'get_transfer_price_for_item');
4261
4262 if( l_return_status <> G_RET_STS_SUCCESS ) THEN
4263 print_Debug('Error from mtl_qp_price', 'get_transfer_price_for_item');
4264 raise FND_API.G_EXC_ERROR;
4265 end if;
4266
4267 --Fixed for bug#9049184
4268 --primary uom is not derived in this part. When tranfer price code is
4269 --returned as 2 then we call to INV_CONVERT.inv_um_conversion is made
4270 --after this else part. But since the primary uom is not derived in this
4271 --part hence it is passed as null and this API fails with error
4272 --UOM conversion not defined.
4273 --below code is aded to select the primary UOM.
4274 if( nvl(l_transfer_price,0) > 0 AND l_transfer_price_code = 2 AND l_primary_uom IS NULL
4275 AND p_inventory_item_id IS NOT NULL ) THEN
4276 print_Debug('l_primary_uom is Null ' || l_primary_uom, 'get_transfer_price_for_item');
4277
4278 SELECT primary_uom_code
4279 into l_primary_uom
4280 FROM mtl_system_items
4281 WHERE organization_id = l_organization_id
4282 AND inventory_item_id = p_inventory_item_id;
4283
4284 print_Debug('l_primary_uom is ' || l_primary_uom, 'get_transfer_price_for_item');
4285 END IF;
4286
4287
4288
4289 IF( l_transfer_price is NULL or l_transfer_price = -99 ) then
4290 SELECT description
4291 into l_item_description
4292 FROM mtl_system_items
4293 WHERE organization_id = l_organization_id
4294 AND inventory_item_id = p_inventory_item_id;
4295 END IF;
4296
4297 if( l_transfer_price = -99 ) THEN
4298 print_Debug('qp price is wrong', 'get_transfeR_price_for_item');
4299 FND_MESSAGE.SET_NAME('INV', 'INV_QP_PRICE_ERROR');
4300 FND_MESSAGE.SET_TOKEN('ITEM', l_item_description);
4301 FND_MSG_PUB.ADD;
4302 l_transfer_price := 0;
4303 end if;
4304
4305 if( l_transfer_price is null ) then
4306 print_Debug('qp price is wrong', 'get_transfeR_price_for_item');
4307 FND_MESSAGE.SET_NAME('INV', 'INV_QP_PRICE_ERROR');
4308 FND_MESSAGE.SET_TOKEN('ITEM', l_item_description);
4309 FND_MSG_PUB.ADD;
4310 l_transfer_price := 0;
4311 end if;
4312 end if; -- if( QP_UTIL.get_qp_status <> 'I' OR l_qp_profile <> 1 )
4313 end if; -- if( nvl(l_transfer_price, -1) = -1 )
4314
4315 l_progress := 2;
4316 if( l_debug = 1 ) then
4317 print_debug('Calling uom_conversion', 'get_transfer_price_for_item');
4318 end if;
4319
4320 print_debug('l_transfer_price_code = '||l_transfer_price_code, 'get_transfer_price_for_item');
4321 print_debug('l_organization_id = '||l_organization_id, 'get_transfer_price_for_item');
4322
4323 if( nvl(l_transfer_price,0) > 0 AND l_transfer_price_code = 2 ) then
4324
4325 IF l_primary_uom IS NULL THEN
4326 SELECT primary_uom_code
4327 INTO l_primary_uom
4328 FROM mtl_system_items
4329 WHERE organization_id = l_organization_id
4330 AND inventory_item_id = p_inventory_item_id;
4331 END IF;
4332
4333 -- do uom conversion
4334 print_debug('Calling uom_conversion', 'get_transfer_price_for_item');
4335 INV_CONVERT.inv_um_conversion(
4336 from_unit => p_transaction_uom
4337 , to_unit => l_primary_uom
4338 , item_id => p_inventory_item_id
4339 , uom_rate => l_uom_rate
4340 );
4341
4342 if( l_uom_rate = -99999 ) then
4343 print_debug('Error from Calling uom_conversion', 'get_transfer_price_for_item');
4344 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_UOM_CONV');
4345 FND_MESSAGE.SET_TOKEN('VALUE1', p_transaction_uom);
4346 FND_MESSAGE.SET_TOKEN('VALUE2', l_primary_uom);
4347 FND_MSG_PUB.ADD;
4348 raise FND_API.G_EXC_ERROR;
4349 end if;
4350 print_debug('l_uom_rate is ' || l_uom_rate, 'get_transfer_price_for_item');
4351
4352 l_transfer_price := l_uom_rate * l_transfer_price;
4353 end if;
4354
4355 if( l_debug = 1 ) then
4356 print_debug('Calling convert_to_functional_currency', 'get_transfer_price_for_item');
4357 end if;
4358
4359 /* Commented out the following currency conversion to From OU currency for Static Pricing*/
4360 /* Bug 4159025 */
4361 /* if( QP_UTIL.get_qp_status <> 'I' OR l_qp_profile <> 1 ) THEN
4362 l_transfer_price := convert_currency(
4363 p_org_id => p_from_org_id
4364 , p_transfer_price => l_transfer_price
4365 , p_currency_code => l_invoice_currency_code
4366 , p_transaction_date => l_transaction_date
4367 , x_functional_currency_code => l_functional_currency_code
4368 , x_return_status => l_return_status
4369 , x_msg_data => x_msg_data
4370 , x_msg_count => x_msg_count
4371 );
4372
4373 if( l_return_status <> G_RET_STS_SUCCESS ) then
4374 print_debug('Error from convert_currency', 'get_transfer_price_for_item');
4375 raise FND_API.G_EXC_ERROR;
4376 end if;
4377 else*/
4378
4379 l_functional_currency_code := l_invoice_currency_code;
4380
4381 print_Debug('l_transfer_price = ' || l_transfer_price, 'get_transfer_price_for_item');
4382 print_Debug('x_currency_code = ' || l_functional_currency_code, 'get_transfer_price_for_item');
4383
4384 /** THe currency code return is if it's not qp, return the functional currency of the shipping OU
4385 else if it is QP, return the currency code pass to qp **/
4386
4387 x_transfer_price := l_transfer_price;
4388 x_currency_code := l_functional_currency_code;
4389 x_return_status := l_return_status;
4390
4391 fnd_msg_pub.count_and_get(
4392 p_encoded => fnd_api.g_false,
4393 p_count => x_msg_count,
4394 p_data => x_msg_data
4395 );
4396 EXCEPTION
4397 WHEN FND_API.G_EXC_ERROR THEN
4398 x_return_status := G_RET_STS_ERROR;
4399 x_transfer_price := -99;
4400 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
4401 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4402
4403 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4404 x_return_status := G_RET_STS_UNEXP_ERROR;
4405 x_transfer_price := -99;
4406 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
4407 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4408
4409 WHEN OTHERS then
4410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4411 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
4412 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4413
4414 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4415 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, l_api_name);
4416 end if;
4417
4418 print_debug('in When Others, l_progress = ' || l_progress, 'Get_Transfer_price');
4419
4420 END get_transfer_price_for_item;
4421
4422 /*==========================================================================================================
4423 * Procedure: GET_TRANSFER_PRICE
4424 *
4425 * Description:
4426 * This API is wrapper API to the Get_Transfer_Price API.
4427 * This API will be called by Oracle Inventory Create_logical_transaction API
4428 * as well as Oracle Costing.
4429 * This API will be called with transaction_uom as : PO UOM or SO UOM, whichever is applicable.
4430 * The API will return the transfer_price in the Transaction_UOM that was passed to it.
4431 * The currency of the price will be the currency set in the price list.
4432 * The calling program will take care of appropriate conversions of UOM and currency.
4433 *
4434 * Inputs:
4435 * - From_Org_ID - the start operating unit
4436 * - To_Org_Id - The End operating Unit
4437 * - Transaction UOM - the units of meassure
4438 * - Invenotry_Item_ID - the inventory item identifier
4439 * - Transaction ID - the inventory transaction ID
4440 * Outputs:
4441 * - x_transfer_price - The total price for the item. If there are no pricelist found, then return 0
4442 * - x_currency_code - the currency code of the transfer price
4443 * - x_return_status - the return status - S - success, E - Error, U - Unexpected Error
4444 * - x_msg_data - the error message
4445 * - x_msg_count - the number of messages in the message stack.
4446 *
4447 *==========================================================================================================*/
4448
4449 Procedure Get_Transfer_Price
4450 (
4451 x_return_status OUT NOCOPY VARCHAR2
4452 , x_msg_data OUT NOCOPY VARCHAR2
4453 , x_msg_count OUT NOCOPY NUMBER
4454 , x_transfer_price OUT NOCOPY NUMBER
4455 , x_currency_code OUT NOCOPY VARCHAR2
4456 , x_incr_transfer_price OUT NOCOPY NUMBER
4457 , x_incr_currency_code OUT NOCOPY VARCHAR2
4458 , p_api_version IN NUMBER
4459 , p_init_msg_list IN VARCHAR2
4460 , p_from_org_id IN NUMBER
4461 , p_to_org_id IN NUMBER
4462 , p_transaction_uom IN VARCHAR2
4463 , p_inventory_item_id IN NUMBER
4464 , p_transaction_id IN NUMBER
4465 , p_from_organization_id IN NUMBER DEFAULT NULL
4466 , p_global_procurement_flag IN VARCHAR2
4467 , p_drop_ship_flag IN VARCHAR2 DEFAULT 'N'
4468 -- , p_process_discrete_xfer_flag IN VARCHAR2 DEFAULT 'N' -- Bug 4750256
4469 , p_order_line_id IN VARCHAR2 DEFAULT NULL
4470 -- Bug 5171637/5138311 umoogala:
4471 -- replaced above line with this one.
4472 , p_txn_date IN DATE DEFAULT NULL /* added for bug 8282784 */
4473 ) IS
4474
4475 l_base_item NUMBER := 0;
4476 l_organization_id NUMBER := 0;
4477 l_transaction_action_id NUMBER := 0;
4478 l_transaction_source_type_id NUMBER := 0;
4479 l_transaction_type_id NUMBER := 0;
4480 l_trx_source_line_id NUMBER := 0;
4481 l_exists NUMBER := 0;
4482 l_transfer_price NUMBER := 0;
4483 l_currency_code VARCHAR2(30);
4484 l_inventory_item_id NUMBER;
4485 l_functional_currency_code VARCHAR2(30);
4486 l_debug NUMBER := nvl(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
4487 l_return_status VARCHAR2(1);
4488 l_msg_data VARCHAR2(255);
4489 l_msg_count NUMBER;
4490 l_transaction_date DATE;
4491 l_price_list_id NUMBER;
4492 l_progress NUMBER;
4493 l_api_version_number CONSTANT NUMBER := 1.0;
4494 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRANSFER_PRICE';
4495 l_inv_transfer_price NUMBER;
4496 l_inv_currency_code VARCHAR2(30);
4497 l_flow_type NUMBER;
4498 l_from_ou_name VARCHAR2(240);
4499 l_to_ou_name VARCHAR2(240);
4500 l_location VARCHAR2(40);
4501 l_customer_number VARCHAR2(30);
4502 --For bug6460311.column length in table hz_parties is varchar2(360)
4503 --l_customer_name VARCHAR2(50);
4504 l_customer_name VARCHAR2(360);
4505 l_cto_item_flag VARCHAR2(1);
4506 l_trx_src_type_id NUMBER := NULL;
4507 l_inventory_item_id NUMBER := p_inventory_item_id;
4508 BEGIN
4509 x_return_status := G_RET_STS_SUCCESS;
4510 x_msg_data := null;
4511 x_msg_count := 0;
4512 x_transfer_price := 0;
4513 x_currency_code := null;
4514
4515 -- Standard call to check for call compatibility
4516 IF NOT FND_API.Compatible_API_Call(
4517 l_api_version_number
4518 , p_api_version
4519 , l_api_name
4520 , G_PKG_NAME)
4521 THEN
4522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4523 END IF;
4524
4525
4526 -- Initialize message list.
4527 IF fnd_api.to_boolean(p_init_msg_list) THEN
4528 fnd_msg_pub.initialize;
4529 END IF;
4530
4531
4532 print_debug('START GET_TRANSFER_PRICE', 'GET_TRANSFER_PRICE');
4533 print_Debug('Input Parameters', 'GET_TRANSFER_PRICE');
4534 print_Debug('----------------', 'GET_TRANSFER_PRICE');
4535 print_Debug('p_api_version is ' || p_api_version, 'GET_TRANSFER_PRICE');
4536 print_Debug('p_init_msg_list' || p_init_msg_list, 'GET_TRANSFER_PRICE');
4537 print_Debug('p_from_org_id is ' || p_from_org_id, 'GET_TRANSFER_PRICE');
4538 print_Debug('p_to_org_id is ' || p_to_org_id, 'GET_TRANSFER_PRICE');
4539 print_Debug('p_transaction_uom is ' || p_transaction_uom, 'GET_TRANSFER_PRICE');
4540 print_Debug('p_inventory_item_id is ' || p_inventory_item_id, 'GET_TRANSFER_PRICE');
4541 print_Debug('p_transaction_id is ' || p_transaction_id, 'GET_TRANSFER_PRICE');
4542 print_Debug('p_global_procurement_flag is ' || p_global_procurement_flag, 'GET_TRANSFER_PRICE');
4543 print_Debug('p_drop_ship_flag is ' || p_drop_ship_flag, 'GET_TRANSFER_PRICE');
4544 print_Debug('p_from_organization_id is ' || p_from_organization_id, 'GET_TRANSFER_PRICE');
4545 -- print_Debug('p_process_discrete_xfer_flag is ' || p_process_discrete_xfer_flag, 'GET_TRANSFER_PRICE'); /* INVCONV Bug 4750256 */
4546 print_Debug('p_order_line_id is ' || p_order_line_id, 'GET_TRANSFER_PRICE'); -- Bug 5171637/5138311 umoogala: INVCONV
4547
4548
4549 if( p_global_procurement_flag = 'Y' ) then
4550 l_flow_type := 2;
4551 else
4552 l_flow_type := 1;
4553 end if;
4554
4555 print_debug('Calling get_inventory_org ', 'GET_TRANSFER_PRICE');
4556 /* Added if condition for bug 8282784
4557 OPM passes shipment date to p_txn_date, conversion rate (hence used in
4558 function convert_currency() below) should be the one applicable on
4559 shipment date , not as per sysdate.
4560 */
4561 if ( p_txn_date is not null and p_from_organization_id is not null ) then
4562 l_transaction_date := p_txn_date;
4563 l_organization_id := p_from_organization_id;
4564 /* End of changes for bug 8282784 */
4565
4566 elsif( p_from_organization_id is not null ) then
4567 l_organization_id := p_from_organization_id;
4568 /* The Start of code changes for bug 9181836 */
4569 -- l_transaction_date := sysdate;
4570 if( p_global_procurement_flag = 'N' )
4571 then
4572 print_debug('Inside p_global_procurement_flag = N', 'GET_TRANSFER_PRICE');
4573
4574 if( p_drop_ship_flag = 'N')
4575 then
4576 BEGIN
4577 select transaction_date
4578 into l_transaction_date
4579 From mtl_material_transactions mmt
4580 where mmt.transaction_id= p_transaction_id;
4581 EXCEPTION
4582 WHEN NO_DATA_FOUND then
4583 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_TRANSACTIONS');
4584 FND_MESSAGE.SET_TOKEN('ID', p_transaction_id);
4585 FND_MSG_PUB.ADD;
4586 raise FND_API.G_EXC_ERROR;
4587 END;
4588 else
4589 print_debug('Inside p_drop_ship_flag = Y', 'GET_TRANSFER_PRICE');
4590 BEGIN
4591 select oeh.ordered_date
4592 into l_transaction_date
4593 FROM oe_order_lines_all oel, oe_order_headers_all oeh
4594 where oel.line_id = p_transaction_id
4595 AND oel.header_id = oeh.header_id;
4596 EXCEPTION
4597 WHEN NO_DATA_FOUND then
4598 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SALES_ORDER');
4599 FND_MESSAGE.SET_TOKEN('LINE', p_transaction_id);
4600 FND_MSG_PUB.ADD;
4601 raise FND_API.G_EXC_ERROR;
4602 END;
4603 end if;
4604 else
4605 print_debug('Inside p_global_procurement_flag = Y', 'GET_TRANSFER_PRICE');
4606 BEGIN
4607 select transaction_date
4608 into l_transaction_date
4609 FROM rcv_transactions rcv
4610 WHERE rcv.transaction_id = p_transaction_id;
4611 EXCEPTION
4612 WHEN NO_DATA_FOUND then
4613 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_RCV_TRANSACTION');
4614 FND_MESSAGE.SET_TOKEN('RCVID', p_transaction_id);
4615 FND_MSG_PUB.ADD;
4616 raise FND_API.G_EXC_ERROR;
4617 END;
4618 end if;
4619
4620 print_debug('l_transaction_date ='||l_transaction_date, 'GET_TRANSFER_PRICE');
4621
4622 /* End of code changes for bug 9181836 */
4623 else
4624
4625 l_organization_id := get_inventory_org(
4626 p_reference_id => p_transaction_id
4627 , p_global_procurement_flag => p_global_procurement_flag
4628 , p_drop_ship_flag => p_drop_ship_flag
4629 , x_transaction_date => l_transaction_date
4630 , x_return_status => l_return_status
4631 , x_msg_data => l_msg_data
4632 , x_msg_count => l_msg_count
4633 );
4634
4635 if( l_return_status <> G_RET_STS_SUCCESS ) then
4636 print_debug('Error from get_inventory_org', 'GET_TRANSFER_PRICE');
4637 raise FND_API.G_EXC_ERROR;
4638 end if;
4639 end if;
4640
4641 print_debug('Inventory org is ' || l_organization_id, 'GET_TRANSFER_PRICE');
4642
4643 l_progress := 1;
4644 print_debug('Check if the from OU is valid ', 'GET_TRANSFER_PRICE');
4645
4646 select count(organization_id)
4647 into l_exists
4648 FROM HR_ORGANIZATION_INFORMATION HOI
4649 WHERE HOI.ORG_INFORMATION3 = to_char(p_from_org_id)
4650 AND HOI.ORGANIZATION_ID= l_organization_id
4651 AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information';
4652
4653 if( l_exists = 0 ) then
4654 print_debug('FROM OU is invalid', 'GET_TRANSFER_PRICE');
4655 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_FROM_OU');
4656 FND_MSG_PUB.ADD;
4657 raise FND_API.G_EXC_ERROR;
4658 -- throw error invalid from operating unit
4659 end if;
4660
4661 l_progress := 2;
4662 print_debug('Check if IC Relations exists between the From OU and TO OU', 'GET_TRANSFER_PRICE');
4663
4664 BEGIN
4665 select 1
4666 into l_exists
4667 FROM mtl_intercompany_parameters
4668 where sell_organization_id = p_to_org_id
4669 AND ship_organization_id = p_from_org_id
4670 and flow_type = l_flow_type;
4671 EXCEPTION
4672 WHEN no_data_found then
4673 print_debug('No IC Relations exists between from OU and To OU', 'GET_TRANSFER_PRICE');
4674 select name
4675 INTO l_from_ou_name
4676 FROM hr_operating_units
4677 where organization_id = p_from_org_id;
4678
4679 SELECT NAME
4680 INTO l_to_ou_name
4681 From HR_OPERATING_UNITS
4682 Where organization_id = p_to_org_id;
4683
4684 FND_MESSAGE.SET_NAME('INV', 'IC-No INTERCO Relation');
4685 FND_MESSAGE.SET_TOKEN('FROM_OU', l_from_ou_name);
4686 FND_MESSAGE.SET_TOKEN('TO_OU', l_to_ou_name);
4687 FND_MSG_PUB.ADD;
4688 raise FND_API.G_EXC_ERROR;
4689 -- throw error invalid no intercompany parameters defined
4690 END;
4691
4692 l_progress := 3;
4693 print_Debug('Get the base_item_id for the inventory_item_id ' || p_inventory_item_id, 'GET_TRANSFER_PRICE');
4694 BEGIN
4695 select nvl(base_item_id, 0)
4696 into l_base_item
4697 from mtl_system_items_b
4698 where inventory_item_id = p_inventory_item_id
4699 and organization_id = l_organization_id;
4700
4701 EXCEPTION
4702 when no_data_found then
4703 -- throw unexpected error;
4704
4705 print_debug('Cannot find item ' || p_inventory_item_id, 'GET_TRANSFER_PRICE');
4706 FND_MESSAGE.SET_NAME('INV', 'INV_IC_INVALID_ITEM_ORG');
4707 FND_MESSAGE.SET_TOKEN('ITEM', p_inventory_item_id);
4708 FND_MESSAGE.SET_TOKEN('ORG', l_organization_id);
4709 FND_MSG_PUB.ADD;
4710 raise FND_API.G_EXC_ERROR;
4711 END;
4712
4713 l_progress := 4;
4714 -- get price list id
4715 print_Debug('Get price list id ' , 'GET_TRANSFER_PRICE');
4716 BEGIN
4717 /* Modified query below : RA to HZ conversions
4718 Replaced occurances of RA views with HZ tables*/
4719 /* SELECT nvl(RSU.price_list_id, nvl(RC.price_list_id, -1)), RSU.location, RC.Customer_number, RC.Customer_name
4720 INTO l_price_List_Id, l_location, l_customer_number, l_customer_name
4721 FROM mtl_intercompany_parameters MIP
4722 , ra_site_uses_all RSU
4723 , ra_customers RC
4724 WHERE MIP.sell_organization_id = p_to_org_id
4725 AND MIP.ship_organization_id = p_from_org_id
4726 AND MIP.flow_type = l_flow_type
4727 AND RSU.site_use_id = MIP.customer_site_id
4728 AND RSU.org_id = MIP.ship_organization_id
4729 AND RC.customer_id = MIP.customer_id;
4730 */
4731
4732 SELECT NVL(rsu.price_list_id, NVL(rc.price_list_id, -1))
4733 , rsu.LOCATION
4734 , rc.customer_number
4735 , rc.customer_name
4736 INTO l_price_List_Id, l_location, l_customer_number, l_customer_name
4737 FROM mtl_intercompany_parameters mip
4738 , hz_cust_site_uses_all rsu
4739 , (SELECT cust_account_id customer_id
4740 , party.party_name customer_name
4741 , party.party_number customer_number
4742 , price_list_id
4743 FROM hz_parties party, hz_cust_accounts cust_acct
4744 WHERE cust_acct.party_id = party.party_id) rc
4745 WHERE mip.sell_organization_id = p_to_org_id
4746 AND mip.ship_organization_id = p_from_org_id
4747 AND mip.flow_type = l_flow_type
4748 AND rsu.site_use_id = mip.customer_site_id
4749 AND rsu.org_id = mip.ship_organization_id
4750 AND rc.customer_id = mip.customer_id;
4751
4752
4753
4754 if( l_price_list_id = -1 ) then
4755 FND_MESSAGE.SET_NAME('INV', 'IC-Price List Not Found');
4756 FND_MESSAGE.SET_TOKEN('LOC', l_location);
4757 FND_MESSAGE.SET_TOKEN('CUST_NUM', l_customer_number);
4758 FND_MESSAGE.SET_TOKEN('CUST_NAME', l_customer_name);
4759 FND_MSG_PUB.ADD;
4760 raise FND_API.G_EXC_ERROR;
4761 end if;
4762 EXCEPTION
4763 when no_data_found then
4764 print_debug('no price list found ', 'GET_TRANSFER_PRICE');
4765 SELECT name
4766 into l_from_ou_name
4767 FROM hr_operating_units
4768 WHERE organization_id = p_from_org_id;
4769
4770 FND_MESSAGE.SET_NAME('INV', 'IC-Invalid Customer');
4771 FND_MESSAGE.SET_TOKEN('OU', l_from_ou_name);
4772 FND_MSG_PUB.ADD;
4773 raise FND_API.G_EXC_ERROR;
4774 END;
4775
4776 -- call get_transfer_price_for_item for the base item
4777 if( l_base_item <> 0 ) then
4778 l_cto_item_flag := 'Y';
4779 else
4780 l_cto_item_flag := 'N';
4781 end if;
4782
4783 print_debug('Calling get_transfer_price_for_item', 'GET_TRANSFER_PRICE');
4784 get_transfer_price_for_item (
4785 x_return_Status => x_return_status
4786 , x_msg_data => x_msg_data
4787 , x_msg_count => x_msg_count
4788 , x_transfer_price => l_inv_transfer_price
4789 , x_currency_code => l_inv_currency_code
4790 , p_api_version => 1.0
4791 , p_init_msg_list => 'F'
4792 , p_from_org_id => p_from_org_id
4793 , p_to_org_id => p_to_org_id
4794 , p_transaction_uom => p_transaction_uom
4795 , p_inventory_item_id => p_inventory_item_id
4796 , p_transaction_id => p_transaction_id
4797 , p_from_organization_id => l_organization_id
4798 , p_price_list_id => l_price_list_id
4799 , p_global_procurement_flag => p_global_procurement_flag
4800 , p_drop_ship_flag => p_drop_ship_flag
4801 , p_cto_item_flag => l_cto_item_flag
4802 -- , p_process_discrete_xfer_flag => p_process_discrete_xfer_flag /* INVCONV Bug 4750256 */
4803 , p_order_line_id => p_order_line_id -- Bug 5171637/5138311 umoogala:
4804 -- replaced above line with this one.
4805 );
4806
4807 if( l_cto_item_flag = 'N' AND x_return_status <> G_RET_STS_SUCCESS ) then
4808 print_debug('Error from get_transfer_price_for_item', 'GET_TRANSFER_PRICE');
4809 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4810 end if;
4811
4812 /* Added following logic for ISO with confgiured items calling of the CTO roll-up price API
4813 only when the price is not associated with the configured item */
4814 BEGIN
4815 SELECT transaction_source_type_id into l_trx_src_type_id
4816 FROM mtl_material_transactions
4817 WHERE transaction_id = p_transaction_id;
4818 if ( l_trx_src_type_id = 8 ) AND ( l_base_item <> 0 ) AND ( nvl(l_inv_transfer_price,0) <> 0 )
4819 AND ( l_inv_currency_code is not null ) then
4820 l_transfer_price := l_inv_transfer_price;
4821 end if;
4822 EXCEPTION WHEN OTHERS THEN
4823 l_trx_src_type_id := NULL;
4824 END;
4825 /* End for ISO with confgiured items changes */
4826
4827 print_debug('l_transfer_price = ' || l_inv_transfer_price, 'GET_TRANSFER_PRICE');
4828 print_Debug('l_currency_code = ' || l_inv_currency_code, 'GET_TRANSFER_PRICE');
4829 print_debug('l_base_item = ' || l_base_item, 'GET_TRANSFER_PRICE');
4830
4831 -- Bug 4366773: In the IF condition modified the variable from l_transfer_price to l_inv_transfer_price
4832
4833 --
4834 -- Bug 5527437 umoogala:
4835 -- Added '= -99' condition to the following IF condition.
4836 --
4837 if( (l_inv_transfer_price IS NULL OR l_inv_transfer_price = -99) AND l_base_item <> 0 ) then
4838 -- call CTO API to get the transfer price
4839 --null;
4840 --
4841 -- Bug 5527437 umoogala: added following IF block
4842 --
4843 l_inv_transfer_price := 0;
4844 IF G_ORDER_LINE_ID = -1 OR G_ORDER_LINE_ID <> p_order_line_id
4845 THEN
4846 G_ORDER_LINE_ID := p_order_line_id;
4847 END IF;
4848
4849 print_debug('Calling CTO API to get the transfer price' , 'GET_TRANSFER_PRICE');
4850 print_debug('G_ORDER_LINE_ID'||G_ORDER_LINE_ID , 'GET_TRANSFER_PRICE');
4851 CTO_TRANSFER_PRICE_PK.CTO_TRANSFER_PRICE(
4852 p_config_item_id => p_inventory_item_id
4853 , p_selling_oper_unit => p_to_org_id
4854 , p_shipping_oper_unit => p_from_org_id
4855 , p_transaction_uom => p_transaction_uom
4856 , p_transaction_id => p_transaction_id
4857 , p_price_list_id => l_price_list_id
4858 , p_from_organization_id => l_organization_id
4859 , p_global_procurement_flag => p_global_procurement_flag
4860 , x_transfer_price => l_inv_transfer_price
4861 , x_currency_code => l_inv_currency_code
4862 , x_return_status => l_return_status
4863 , x_msg_count => l_msg_count
4864 , x_msg_data => l_msg_data
4865 );
4866
4867 if( l_return_status <> G_RET_STS_SUCCESS ) then
4868 print_debug('Error from CTO_Transfer_price', 'GET_TRANSFER_PRICE');
4869 raise FND_API.G_EXC_ERROR;
4870 end if;
4871 print_debug('l_transfer_price from CTO is ' || l_inv_transfer_price, 'GET_TRANSFER_PRICE');
4872 end if;
4873
4874 l_transfer_price := convert_currency(
4875 p_org_id => p_from_org_id
4876 , p_transfer_price => l_inv_transfer_price
4877 , p_currency_code => l_inv_currency_code
4878 , p_transaction_date => l_transaction_date
4879 , p_logical_txn => 'Y' /* bug 6696446 */
4880 , x_functional_currency_code => l_functional_currency_code
4881 , x_return_status => l_return_status
4882 , x_msg_data => x_msg_data
4883 , x_msg_count => x_msg_count
4884 );
4885
4886 if( l_return_status <> G_RET_STS_SUCCESS ) then
4887 print_debug('Error from convert_currency', 'get_transfer_price_for_item');
4888 raise FND_API.G_EXC_ERROR;
4889 end if;
4890 print_Debug('l_transfer_price = ' || l_transfer_price, 'get_transfer_price');
4891 print_Debug('x_currency_code = ' || l_functional_currency_code, 'get_transfer_price');
4892
4893 x_transfer_price := l_transfer_price;
4894 x_currency_code := l_functional_currency_code;
4895 x_incr_transfer_price := l_inv_transfer_price;
4896 x_incr_currency_code := l_inv_currency_code;
4897 x_return_status := G_RET_STS_SUCCESS;
4898 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4899 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4900 EXCEPTION
4901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4902 x_return_status := G_RET_STS_UNEXP_ERROR;
4903 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4904 print_debug('In Exc_Unexpected_Error ' || l_progress, 'Get_Transfer_price');
4905 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4906
4907 WHEN FND_API.G_EXC_ERROR THEN
4908 x_return_status := G_RET_STS_ERROR;
4909 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4910 print_debug('In EXC_ERROR ' || l_progress, 'Get_Transfer_Price');
4911 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4912
4913
4914 WHEN OTHERS then
4915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4916 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
4917 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4918
4919 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4920 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, l_api_name);
4921 end if;
4922
4923 print_debug('in When Others, l_progress = ' || l_progress, 'Get_Transfer_price');
4924
4925 END get_transfer_price;
4926
4927 /*==========================================================================================================
4928 * Function: GET_TRANSFER_PRICE_DATE();
4929 *
4930 * Description: This function is to get the date by which the transfer price for the item will be queried from Transfer Price List
4931 * This function retrieve the date according to value of profile "INV: Intercompany Transfer Price Date".
4932 * i) Profile set to 'ORDER DATE'
4933 * a) Shipping flow- function returns order line pricing date
4934 * b) Procurement flow- function returns Purchase Order Approved date.
4935 * ii) Profile set to 'CURRENT DATE', function returns sysdate
4936 *
4937 * Input Parameters:
4938 * 1. p_call - Determines from where this function is called
4939 * I - Called from internal procedure or function of INV_TRANSACTION_FLOW_PUB
4940 * E - Called from any external procedure or function
4941 * 2. p_order_line_id - SO line id for Shipping flow and PO line id for purchasing flow
4942 * 3. p_global_procurement_flag
4943 * 4. p_transaction_id - This is not required when called from external procedure/function
4944 * 5. p_drop_ship_flag - default is N
4945 *
4946 * Output Parameter:
4947 * 1. x_return_status - return status
4948 * 2. x_msg_data - error message
4949 * 3. x_msg_count - number of message in the message stack
4950 *
4951 * It returns a date value
4952 * Note - Function is added as a part of changes done in bug#6700919
4953 *==========================================================================================================*/
4954 FUNCTION get_transfer_price_date(
4955 p_call IN VARCHAR2
4956 , p_order_line_id IN NUMBER
4957 , p_global_procurement_flag IN VARCHAR2
4958 , p_transaction_id IN NUMBER DEFAULT NULL
4959 , p_drop_ship_flag IN VARCHAR2 DEFAULT 'N'
4960 , x_return_status OUT NOCOPY VARCHAR2
4961 , x_msg_data OUT NOCOPY VARCHAR2
4962 , x_msg_count OUT NOCOPY NUMBER
4963 ) RETURN DATE
4964 IS
4965 l_trf_date_profile NUMBER:= nvl(fnd_profile.value('INV_INTERCOMPANY_TRANSFER_PRICE_DATE'), 1);
4966 l_trf_price_date DATE;
4967 l_doc_type VARCHAR2(4);
4968 l_whse_code VARCHAR2(4);
4969 l_line_id NUMBER;
4970 BEGIN
4971 print_debug('Inside get_transfer_price_date', 'GET_TRANSFER_PRICE_DATE');
4972 print_Debug('p_call = ' || p_call, 'get_transfer_price_date');
4973 print_Debug('p_order_line_id = ' || p_order_line_id, 'GET_TRANSFER_PRICE_DATE');
4974 print_Debug('p_global_procurement_flag = ' || p_global_procurement_flag, 'GET_TRANSFER_PRICE_DATE');
4975 print_Debug('p_transaction_id = ' || p_transaction_id, 'GET_TRANSFER_PRICE_DATE');
4976 print_Debug('p_drop_ship_flag = ' || p_drop_ship_flag, 'GET_TRANSFER_PRICE_DATE');
4977
4978
4979 if l_trf_date_profile <>1 then -- for profile value =ORDER DATE
4980 print_debug('INV: Intercompany Transfer Price Date -ORDER DATE', 'GET_TRANSFER_PRICE_DATE');
4981 if (p_call <> 'I') then
4982 if( p_global_procurement_flag = 'N' ) then
4983 print_debug('Inside p_global_procurement_flag = N', 'GET_TRANSFER_PRICE_DATE');
4984 begin
4985 select oel.pricing_date
4986 into l_trf_price_date
4987 from oe_order_lines_all oel
4988 where oel.line_id = p_order_line_id;
4989 exception
4990 when no_data_found then
4991 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SALES_ORDER');
4992 FND_MESSAGE.SET_TOKEN('LINE', p_order_line_id);
4993 FND_MSG_PUB.ADD;
4994 raise FND_API.G_EXC_ERROR;
4995 end;
4996 else
4997 -- this is global procurement flow.
4998 print_debug('Inside p_global_procurement_flag =Y', 'GET_TRANSFER_PRICE_DATE');
4999 begin
5000 select poh.approved_date
5001 into l_trf_price_date
5002 from po_headers_all poh, po_lines_all pol
5003 where poh.po_header_id = pol.po_header_id
5004 and pol.po_line_id = p_order_line_id;
5005 exception
5006 when no_data_found then
5007 FND_MESSAGE.SET_NAME('INV', 'INV_INT_PO');
5008 FND_MSG_PUB.ADD;
5009 raise FND_API.G_EXC_ERROR;
5010 end;
5011 end if; --p_global_procurement_flag = 'N'
5012 else
5013 if ( GML_PROCESS_FLAGS.process_orgn = 1 AND GML_PROCESS_FLAGS.opmitem_flag = 1 ) then
5014 --OPM Flow
5015 print_debug('Inside OPM Flow', 'GET_TRANSFER_PRICE_DATE');
5016 select doc_type, line_id, whse_code
5017 into l_doc_type, l_line_id, l_whse_code
5018 from ic_tran_pnd
5019 where trans_id = p_transaction_id;
5020 if l_doc_type = 'OMSO' then
5021 begin
5022 select oel.pricing_date
5023 into l_trf_price_date
5024 from ic_whse_mst WHS
5025 , oe_order_lines_all OEL
5026 where OEL.line_id = l_line_id
5027 and WHS.whse_code = l_whse_code;
5028 exception
5029 when no_data_found then
5030 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SALES_ORDER');
5031 FND_MESSAGE.SET_TOKEN('ID', l_line_id);
5032 FND_MSG_PUB.ADD;
5033 raise FND_API.G_EXC_ERROR;
5034 end;
5035 elsif l_doc_type = 'PORC' THEN
5036 begin
5037 select oel.pricing_date
5038 into l_trf_price_date
5039 from ic_whse_mst WHS
5040 , oe_order_lines_all OEL
5041 , rcv_transactions RCT
5042 , po_requisition_lines_all pol
5043 where pol.requisition_line_id = oel.orig_sys_document_Ref
5044 and oel.order_source_id = 10
5045 and RCT.transaction_id = l_line_id
5046 and RCT.requisition_line_id = pol.requisition_line_id
5047 and WHS.whse_code = l_whse_code;
5048 exception
5049 when no_data_found then
5050 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_RCV_TRANSACTION');
5051 FND_MESSAGE.SET_TOKEN('RCVID', l_line_id);
5052 FND_MSG_PUB.ADD;
5053 raise FND_API.G_EXC_ERROR;
5054 end;
5055 end if;
5056 else
5057 if( p_global_procurement_flag = 'N' ) then
5058 -- this means this is not a global procurement
5059 -- we need to check if this is a drop ship with procuring flow
5060 print_debug('Inside p_global_procurement_flag = N', 'GET_TRANSFER_PRICE_DATE');
5061 if( p_drop_ship_flag = 'N') then
5062 begin
5063 select oel.pricing_date
5064 into l_trf_price_date
5065 from oe_order_lines_all oel
5066 where oel.line_id = p_order_line_id;
5067 exception
5068 when no_data_found then
5069 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SALES_ORDER');
5070 FND_MESSAGE.SET_TOKEN('ID', p_order_line_id);
5071 FND_MSG_PUB.ADD;
5072 raise FND_API.G_EXC_ERROR;
5073 end;
5074 else
5075 -- this is a true drop ship with procuring flow
5076 BEGIN
5077 select oel.pricing_date
5078 INTO l_trf_price_date
5079 FROM oe_order_lines_all oel
5080 where oel.line_id = p_transaction_id;
5081 EXCEPTION
5082 WHEN NO_DATA_FOUND then
5083 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SALES_ORDER');
5084 FND_MESSAGE.SET_TOKEN('LINE', p_transaction_id);
5085 FND_MSG_PUB.ADD;
5086 raise FND_API.G_EXC_ERROR;
5087 END;
5088 end if; --if( p_drop_ship_flag = 'N')
5089 else
5090 -- this is global procurement flow.
5091 print_debug('Inside p_global_procurement_flag =Y', 'GET_TRANSFER_PRICE_DATE');
5092 begin
5093 SELECT poh.approved_date
5094 into l_trf_price_date
5095 FROM rcv_transactions rcv, po_headers_all poh
5096 WHERE rcv.transaction_id = p_transaction_id
5097 AND rcv.po_header_id = poh.po_header_id;
5098 exception
5099 when no_data_found then
5100 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_RCV_TRANSACTION');
5101 FND_MESSAGE.SET_TOKEN('RCVID', p_transaction_id);
5102 FND_MSG_PUB.ADD;
5103 raise FND_API.G_EXC_ERROR;
5104 end;
5105 end if; --if( p_global_procurement_flag = 'N' )
5106 end if; --if ( GML_PROCESS_FLAGS.process_orgn = 1
5107 end if; -- p_call <> 'I'
5108 else -- for profile value =CURRENT DATE
5109 print_debug('INV: Intercompany Transfer Price Date -CURRENT DATE', 'GET_TRANSFER_PRICE_DATE');
5110 l_trf_price_date := sysdate;
5111 end if; --if l_trf_date_profile <>1
5112
5113 if l_trf_price_date is null then
5114 print_debug('Error: l_trf_price_date is null' , 'GET_TRANSFER_PRICE_DATE');
5115 raise FND_API.G_EXC_ERROR;
5116 end if;
5117
5118 x_return_status := G_RET_STS_SUCCESS;
5119 /* Added trunc() function for bug 8796195 */
5120 return trunc(l_trf_price_date);
5121
5122 EXCEPTION
5123 WHEN FND_API.G_EXC_ERROR then
5124 x_return_status := G_RET_STS_ERROR;
5125 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
5126 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
5127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5128 x_return_status := G_RET_STS_UNEXP_ERROR;
5129 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
5130 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
5131 WHEN OTHERS then
5132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5133 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
5134 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
5135
5136 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5137 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, 'GET_TRANSFER_PRICE_DATE');
5138 end if;
5139 END get_transfer_price_date;
5140
5141 END INV_TRANSACTION_FLOW_PUB;