[Home] [Help]
PACKAGE BODY: APPS.INV_TRANSACTION_FLOW_PUB
Source
1 PACKAGE BODY INV_TRANSACTION_FLOW_PUB AS
2 /* $Header: INVPICTB.pls 120.17 2007/12/17 11:59:50 ancgupta 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.17 2007/12/17 11:59:50 ancgupta 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
2889 CURSOR drop_ship_sources
2890 (l_poh_id NUMBER,
2891 l_pol_id NUMBER,
2892 l_poll_id NUMBER)
2893 IS
2894 SELECT header_id, line_id
2895 FROM
2896 oe_drop_ship_sources
2897 WHERE
2898 po_header_id = l_poh_id
2899 AND po_line_id = l_pol_id
2900 AND line_location_id = l_poll_id
2901 ORDER BY header_id,line_id;
2902 BEGIN
2903
2904 IF l_debug = 1 then
2905 print_debug('Entered Get_dropship_PO_txn_type', l_api_name);
2906 print_debug('Inputs p_rcv_transaction_id '||p_rcv_transaction_id||
2907 ' p_global_procurement_flag '||p_global_procurement_flag, l_api_name);
2908 END IF;
2909
2910 -- Standard call to check for call compatibility
2911 IF NOT FND_API.Compatible_API_Call(
2912 l_api_version_number
2913 , p_api_version
2914 , l_api_name
2915 , G_PKG_NAME)
2916 THEN
2917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2918 END IF;
2919
2920
2921 -- Initialize message list.
2922 IF fnd_api.to_boolean(p_init_msg_list) THEN
2923 fnd_msg_pub.initialize;
2924 END IF;
2925
2926
2927 x_return_status := 'S';
2928 x_msg_data := null;
2929 x_msg_count := 0;
2930 x_dropship_type_code := -99;
2931 x_transaction_type_id := -99;
2932 x_transaction_action_id := -99;
2933 x_transaction_source_type_id := -99;
2934 x_header_id := -99;
2935
2936 IF nvl(p_rcv_transaction_id,-1) <= 0 THEN
2937 IF l_debug = 1 then
2938 print_debug('incorrect p_rcv_transaction_id', l_api_name);
2939 END IF;
2940 fnd_message.set_name('INV', 'INV_NO_RCVTXNID');
2941 fnd_msg_pub.add;
2942 RAISE fnd_api.g_exc_error;
2943 END IF;
2944
2945 BEGIN
2946 SELECT rt.po_header_id, rt.po_line_id, rt.po_line_location_id,
2947 rt.transaction_type, rt.transaction_date, rsl.item_id
2948 INTO
2949 l_po_header_id, l_po_line_id, l_po_line_location_id,
2950 l_transaction_type, l_transaction_date, l_inventory_item_id
2951 FROM
2952 rcv_transactions rt,
2953 rcv_shipment_lines rsl
2954 WHERE
2955 transaction_id = p_rcv_transaction_id
2956 AND rt.shipment_line_id = rsl.shipment_line_id;
2957 EXCEPTION
2958 WHEN no_data_found THEN
2959 IF l_debug = 1 then
2960 print_debug('no record found in rcv_transcations', l_api_name);
2961 END IF;
2962 fnd_message.set_name('INV', 'INV_NO_RCVTXN');
2963 fnd_msg_pub.add;
2964 RAISE fnd_api.g_exc_error;
2965 END;
2966
2967 IF l_debug = 1 then
2968 print_debug(' l_po_header_id '||l_po_header_id||
2969 ' l_po_line_id '||l_po_line_id||
2970 ' l_po_line_location_id '||l_po_line_location_id||
2971 ' l_inventory_item_id '||l_inventory_item_id, l_api_name);
2972 print_debug('l_transaction_date '||l_transaction_date, l_api_name);
2973 print_debug('l_transaction_type '||l_transaction_type, l_api_name);
2974 END IF;
2975
2976 IF (l_po_header_id IS NULL) OR (l_po_line_id IS NULL) OR
2977 (l_po_line_location_id IS NULL) THEN
2978 IF l_debug = 1 then
2979 print_debug('Insufficient information in rcv_transactions', l_api_name);
2980 END IF;
2981 fnd_message.set_name('INV', 'INV_NO_RCVTXN_INFO');
2982 fnd_msg_pub.add;
2983 RAISE fnd_api.g_exc_error;
2984 END IF;
2985
2986
2987 IF l_transaction_type in ('RETURN TO RECEIVING',
2988 'RETURN TO CUSTOMER',
2989 'RETURN TO VENDOR') THEN
2990
2991 x_dropship_type_code := G_PHYSICAL_RECEIPT_FOR_NON_DS;
2992 x_transaction_type_id := INV_GLOBALS.G_TYPE_RETURN_TO_VENDOR;
2993 x_transaction_action_id := INV_GLOBALS.G_ACTION_ISSUE;
2994 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
2995
2996 ELSE
2997
2998 OPEN drop_ship_sources(l_po_header_id,l_po_line_id,l_po_line_location_id);
2999
3000 FETCH drop_ship_sources INTO l_header_id,l_line_id;
3001
3002 IF drop_ship_sources%notfound THEN
3003 IF l_debug = 1 then
3004 print_debug('No data found in oe_drop_ship_sources: not a drop ship', l_api_name);
3005 END IF;
3006 l_external_drop_ship := FALSE;
3007 ELSE
3008 l_external_drop_ship := TRUE;
3009 IF l_debug = 1 then
3010 print_debug('Drop ship', l_api_name);
3011 END IF;
3012 END IF;
3013
3014 IF drop_ship_sources%isopen THEN
3015 CLOSE drop_ship_sources;
3016 END IF;
3017
3018 IF l_debug = 1 then
3019 print_debug('l_header_id '||l_header_id||
3020 ' l_line_id '||l_line_id, l_api_name);
3021 END IF;
3022
3023 IF NOT l_external_drop_ship THEN
3024 x_dropship_type_code := G_PHYSICAL_RECEIPT_FOR_NON_DS;
3025 IF l_transaction_type = 'DELIVER' THEN
3026 x_transaction_type_id := INV_GLOBALS.G_TYPE_PO_RECEIPT;
3027 x_transaction_action_id := INV_GLOBALS.G_ACTION_RECEIPT;
3028 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3029 ELSIF l_transaction_type = 'CORRECT' THEN
3030 x_transaction_type_id := INV_GLOBALS.G_TYPE_PO_RCPT_ADJ;
3031 x_transaction_action_id := INV_GLOBALS.G_ACTION_DELIVERYADJ;
3032 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3033 END IF;
3034 ELSE
3035
3036 BEGIN
3037 SELECT org_id, ship_from_org_id
3038 INTO l_selling_ou, l_ship_from_org_id
3039 FROM oe_order_lines_all
3040 WHERE
3041 header_id = l_header_id AND
3042 line_id = l_line_id;
3043 EXCEPTION
3044 WHEN no_data_found THEN
3045 IF l_debug = 1 then
3046 print_debug('cannot find sales order line ', l_api_name);
3047 END IF;
3048 fnd_message.set_name('INV', 'INV_NO_SALES_ORDER_LINE');
3049 fnd_msg_pub.add;
3050 RAISE fnd_api.g_exc_error;
3051 END;
3052
3053 IF l_debug = 1 then
3054 print_debug('l_selling_ou '||l_selling_ou||
3055 ' l_ship_from_org_id '||l_ship_from_org_id, l_api_name);
3056 END IF;
3057
3058 BEGIN
3059 SELECT org_information3
3060 INTO l_ship_from_ou
3061 FROM HR_ORGANIZATION_INFORMATION HOI
3062 WHERE HOI.ORGANIZATION_ID= l_ship_from_org_id
3063 AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information';
3064 EXCEPTION
3065 WHEN no_data_found THEN
3066 l_ship_from_ou := NULL;
3067 IF l_debug = 1 then
3068 print_debug('cannot find ship from operating unit', l_api_name);
3069 END IF;
3070 END;
3071
3072 IF l_debug = 1 then
3073 print_debug('l_ship_from_ou '||l_ship_from_ou, l_api_name);
3074 END IF;
3075
3076 IF (l_selling_ou IS NULL) OR (l_ship_from_ou IS NULL) THEN
3077 fnd_message.set_name('INV', 'INV_NULL_SELLSHIP_OU');
3078 fnd_msg_pub.add;
3079 RAISE fnd_api.g_exc_error;
3080 END IF;
3081
3082 /******* Calling get transaction flow for the operating units*******/
3083
3084 l_qualifier_code_tbl.DELETE;
3085 l_qualifier_value_tbl.DELETE;
3086
3087 BEGIN
3088 SELECT category_id INTO l_qualifier_value_tbl(1)
3089 FROM mtl_item_categories
3090 WHERE
3091 inventory_item_id = l_inventory_item_id
3092 AND organization_id = l_ship_from_org_id
3093 AND category_set_id = 1;
3094
3095 l_qualifier_code_tbl(1) := 1;
3096
3097 IF l_debug = 1 then
3098 print_debug('category id'||l_qualifier_value_tbl(1), l_api_name);
3099 END IF;
3100
3101 EXCEPTION
3102 WHEN no_data_found THEN
3103 l_qualifier_value_tbl.DELETE;
3104 l_qualifier_value_tbl.DELETE;
3105
3106 when too_many_rows then
3107 fnd_message.set_name('INV', 'INV_TOO_MANY_CATEGORIES');
3108 fnd_msg_pub.add;
3109 RAISE fnd_api.g_exc_error;
3110 END;
3111
3112 INV_TRANSACTION_FLOW_PUB.CHECK_TRANSACTION_FLOW
3113 (p_api_version => 1.0
3114 ,p_start_operating_unit => l_ship_from_ou
3115 ,p_end_operating_unit => l_selling_ou
3116 ,p_flow_type => 1 --shipping
3117 ,p_organization_id => l_ship_from_org_id
3118 ,p_qualifier_code_tbl => l_qualifier_code_tbl
3119 ,p_qualifier_value_tbl => l_qualifier_value_tbl
3120 ,p_transaction_date => l_transaction_date
3121 ,x_return_status => l_return_status
3122 ,x_msg_count => l_msg_data
3123 ,x_msg_data => l_msg_count
3124 ,x_header_id => l_txn_flow_header_id
3125 ,x_new_accounting_flag => l_new_accounting_flag
3126 ,x_transaction_flow_exists => l_transaction_flow_exists
3127 );
3128
3129 IF l_debug = 1 THEN
3130 print_debug('check_transaction_flow Ret Status '|| l_return_status, l_api_name);
3131 print_debug('Ret Message '||l_msg_data, l_api_name);
3132 print_debug('l_transaction_flow_exists '||l_transaction_flow_exists, l_api_name);
3133 print_debug('l_txn_flow_header_id '||l_txn_flow_header_id, l_api_name);
3134 print_debug('l_new_accounting_flag '||l_new_accounting_flag, l_api_name);
3135 END IF;
3136
3137 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3138
3139 RAISE fnd_api.g_exc_unexpected_error;
3140
3141 ELSIF (l_transaction_flow_exists = g_transaction_flow_found)
3142 AND (l_new_accounting_flag in ('Y','y')) THEN
3143 --Drop ship logical
3144 x_dropship_type_code := G_LOGICAL_RECEIPT_FOR_DS;
3145 IF l_transaction_type = 'DELIVER' THEN
3146 x_header_id := l_txn_flow_header_id;
3147 x_transaction_type_id := INV_GLOBALS.G_TYPE_LOGL_PO_RECEIPT;
3148 x_transaction_action_id := INV_GLOBALS.G_ACTION_LOGICALRECEIPT;
3149 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3150 ELSIF l_transaction_type = 'CORRECT' THEN
3151 x_header_id := l_txn_flow_header_id;
3152 x_transaction_type_id := INV_GLOBALS.G_TYPE_LOGL_PO_RECEIPT_ADJ;
3153 x_transaction_action_id := INV_GLOBALS.G_ACTION_LOGICALDELADJ;
3154 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3155 END IF;
3156 ELSE
3157 --Drop ship Physical
3158 x_dropship_type_code := G_PHYSICAL_RECEIPT_FOR_DS;
3159 IF l_transaction_type = 'DELIVER' THEN
3160 x_transaction_type_id := INV_GLOBALS.G_TYPE_PO_RECEIPT;
3161 x_transaction_action_id := INV_GLOBALS.G_ACTION_RECEIPT;
3162 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3163 ELSIF l_transaction_type = 'CORRECT' THEN
3164 x_transaction_type_id := INV_GLOBALS.G_TYPE_PO_RCPT_ADJ;
3165 x_transaction_action_id := INV_GLOBALS.G_ACTION_DELIVERYADJ;
3166 x_transaction_source_type_id := INV_GLOBALS.G_SOURCETYPE_PURCHASEORDER;
3167 END IF;
3168 END IF;
3169
3170 END IF;--NOT l_external_drop_ship
3171 END IF;--else of IF l_transaction_type = 'RETURN TO RECEIVING'
3172
3173 IF l_debug = 1 THEN
3174 print_debug('Return values ', l_api_name);
3175 print_debug('x_dropship_type_code '||x_dropship_type_code, l_api_name);
3176 print_debug('x_header_id '||x_header_id, l_api_name);
3177 print_debug('x_transaction_type_id '||x_transaction_type_id||
3178 ' x_transaction_action_id '||x_transaction_action_id||
3179 ' x_transaction_source_type_id '||x_transaction_source_type_id, l_api_name);
3180 END IF;
3181 EXCEPTION
3182 WHEN FND_API.G_EXC_ERROR THEN
3183 x_return_status := FND_API.G_RET_STS_ERROR;
3184 x_dropship_type_code := -99;
3185 x_transaction_type_id := -99;
3186 x_transaction_action_id := -99;
3187 x_transaction_source_type_id := -99;
3188 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3189
3190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3192 x_dropship_type_code := -99;
3193 x_transaction_type_id := -99;
3194 x_transaction_action_id := -99;
3195 x_transaction_source_type_id := -99;
3196 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3197
3198 WHEN OTHERS THEN
3199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3200 x_dropship_type_code := -99;
3201 x_transaction_type_id := -99;
3202 x_transaction_action_id := -99;
3203 x_transaction_source_type_id := -99;
3204
3205 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3206 FND_MSG_PUB.Add_Exc_Msg
3207 ( G_PACKAGE_NAME, 'GET_DROPSHIP_PO_TXN_TYPE');
3208 end if;
3209 END GET_DROPSHIP_PO_TXN_TYPE;
3210
3211
3212 /** Get_Inventory_Org function
3213 * Private function to get the inventory org of a specific OU
3214 */
3215
3216 FUNCTION GET_INVENTORY_ORG(
3217 p_reference_id IN NUMBER
3218 , p_global_procurement_flag IN VARCHAR2
3219 , p_drop_ship_flag IN VARCHAR2
3220 , x_transaction_date OUT NOCOPY DATE
3221 , x_return_status OUT NOCOPY VARCHAR2
3222 , x_msg_data OUT NOCOPY VARCHAR2
3223 , x_msg_count OUT NOCOPY NUMBER
3224 ) RETURN NUMBER IS
3225 l_organization_id NUMBER;
3226 l_transaction_date DATE;
3227 l_progress NUMBER := 0;
3228 l_doc_type VARCHAR2(4);
3229 l_whse_code VARCHAR2(4);
3230 l_line_id NUMBER;
3231 BEGIN
3232 print_debug('Inside get_inventory_org', 'Get_Inventory_Org');
3233 print_debug('p_reference_id = ' || p_reference_id, 'Get_Inventory_org');
3234 print_Debug('p_global_procurement_flag = ' || p_global_procurement_flag, 'Get_Inventory_Org');
3235 print_Debug('p_drop_ship_flag = ' || p_drop_ship_flag, 'Get_Inventory_org');
3236
3237 /* OPM INVCONV umoogala
3238 * This code will not longer be needed for process mfg.orgs
3239 IF ( GML_PROCESS_FLAGS.process_orgn = 1 AND GML_PROCESS_FLAGS.opmitem_flag = 1 ) THEN
3240
3241 SELECT doc_type, line_id, whse_code
3242 INTO l_doc_type, l_line_id, l_whse_code
3243 FROM ic_tran_pnd
3244 WHERE trans_id = p_reference_id;
3245
3246 IF l_doc_type = 'OMSO' THEN
3247 SELECT WHS.mtl_organization_id, oeh.ordered_date
3248 INTO l_organization_id, l_transaction_date
3249 FROM ic_whse_mst WHS
3250 , oe_order_lines_all OEL
3251 , oe_order_headers_all OEH
3252 WHERE OEL.line_id = l_line_id
3253 AND oel.header_id = oeh.header_id
3254 AND WHS.whse_code = l_whse_code;
3255 ELSIF l_doc_type = 'PORC' THEN
3256 SELECT WHS.mtl_organization_id, oeh.ordered_date
3257 INTO l_organization_id, l_transaction_date
3258 FROM ic_whse_mst WHS
3259 , oe_order_lines_all OEL
3260 , oe_order_headers_all OEH
3261 , rcv_transactions RCT
3262 , po_requisition_headers_all poh
3263 , po_requisition_lines_all pol
3264 WHERE poh.requisition_header_id = pol.requisition_header_id
3265 AND pol.requisition_line_id = oel.orig_sys_document_Ref
3266 AND oel.order_source_id = 10
3267 AND oel.header_id = oeh.header_id
3268 AND RCT.transaction_id = l_line_id
3269 AND RCT.requisition_line_id = pol.requisition_line_id
3270 AND WHS.whse_code = l_whse_code;
3271 END IF;
3272 else
3273 end OPM INVCONV */
3274
3275 if( p_global_procurement_flag = 'N' )
3276 then
3277 -- this means this is not a global procurement
3278 -- we need to check if this is a drop ship with procuring flow
3279 print_debug('Inside p_global_procurement_flag = N', 'Get_Inventory_Org');
3280
3281 if( p_drop_ship_flag = 'N')
3282 then
3283 l_progress := 1;
3284 BEGIN
3285 select mmt.organization_id, transaction_date
3286 into l_organization_id, l_transaction_date
3287 From mtl_material_transactions mmt
3288 where mmt.transaction_id= p_reference_id;
3289 EXCEPTION
3290 WHEN NO_DATA_FOUND then
3291 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_TRANSACTIONS');
3292 FND_MESSAGE.SET_TOKEN('ID', p_reference_id);
3293 FND_MSG_PUB.ADD;
3294 raise FND_API.G_EXC_ERROR;
3295 END;
3296 else
3297 l_progress := 2;
3298 -- this is a true drop ship with procuring flow
3299 BEGIN
3300 select oel.ship_from_org_id, oeh.ordered_date
3301 into l_organization_id, l_transaction_date
3302 FROM oe_order_lines_all oel, oe_order_headers_all oeh
3303 where oel.line_id = p_reference_id
3304 AND oel.header_id = oeh.header_id;
3305 EXCEPTION
3306 WHEN NO_DATA_FOUND then
3307 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SALES_ORDER');
3308 FND_MESSAGE.SET_TOKEN('LINE', p_reference_id);
3309 FND_MSG_PUB.ADD;
3310 raise FND_API.G_EXC_ERROR;
3311 END;
3312 end if;
3313 else
3314 -- this is global procurement flow.
3315 l_progress := 3;
3316 BEGIN
3317 select rcv.organization_id, transaction_date
3318 into l_organization_id, l_transaction_date
3319 FROM rcv_transactions rcv
3320 WHERE rcv.transaction_id = p_reference_id;
3321 EXCEPTION
3322 WHEN NO_DATA_FOUND then
3323 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_RCV_TRANSACTION');
3324 FND_MESSAGE.SET_TOKEN('RCVID', p_reference_id);
3325 FND_MSG_PUB.ADD;
3326 raise FND_API.G_EXC_ERROR;
3327 END;
3328 end if;
3329 -- end if; OPM INVCONV
3330
3331 x_transaction_date := l_transaction_date;
3332 x_return_status := G_RET_STS_SUCCESS;
3333 fnd_msg_pub.count_and_get(
3334 p_encoded => fnd_api.g_false,
3335 p_count => x_msg_count,
3336 p_data => x_msg_data
3337 );
3338
3339 return l_organization_id;
3340 EXCEPTION
3341 WHEN FND_API.G_EXC_ERROR then
3342 x_return_status := G_RET_STS_ERROR;
3343 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3344 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3345
3346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3347 x_return_status := G_RET_STS_UNEXP_ERROR;
3348 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3349 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3350
3351 WHEN OTHERS then
3352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3353 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3354 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3355
3356 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3357 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, 'Get_Inventory_Org');
3358 end if;
3359
3360 print_debug('in When Others, l_progress = ' || l_progress, 'GET_INVENTORY_ORG');
3361
3362 END GET_INVENTORY_ORG;
3363
3364 /**
3365 * Helper function to get the functioanl currency code of an operating unit
3366 */
3367 Function get_functional_currency_code(
3368 p_org_id IN NUMBER,
3369 x_sets_of_book_id OUT NOCOPY NUMBER,
3370 x_return_status OUT NOCOPY VARCHAR2,
3371 x_msg_data OUT NOCOPY VARCHAR2,
3372 x_msg_count OUT NOCOPY NUMBER) return VARCHAR2
3373 IS
3374 l_functional_currency_code VARCHAR2(31);
3375 l_progress NUMBER := 0;
3376 l_set_of_book_id NUMBER;
3377 lreturn_status VARCHAR2(1);
3378 lmsg_data VARCHAR2(100);
3379 lsob_id NUMBER;
3380 lcoa_id NUMBER;
3381
3382 BEGIN
3383 l_functional_currency_code := '';
3384 x_return_status := G_RET_STS_SUCCESS;
3385 x_msg_data := null;
3386 x_msg_count := 0;
3387
3388 print_debug('Start Get_Functional_Currency_Code', 'Get_Functional_Currency_Code');
3389 print_debug('p_org_id ' || p_org_id, 'Get_Functional_Currency_Code');
3390
3391 -- print_Debug('Get the set of books', 'get_functional_currency_code');
3392 -- Modified the message text set of books to ledger for making the message compatible with LE uptake project
3393 print_Debug('Get the ledgers', 'get_functional_currency_code');
3394
3395 /* commented the selection of COA using LE - OU link which is obsoleted in R12
3396 and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
3397 Bug No - 4336479
3398 BEGIN
3399 l_progress := 1;
3400 SELECT to_number(LEI.org_information1)
3401 into l_set_of_book_id
3402 FROM HR_ORGANIZATION_INFORMATION LEI, HR_ORGANIZATION_UNITS OU,
3403 HR_ORGANIZATION_INFORMATION OUI
3404 WHERE OU.organization_id = p_org_id
3405 AND LEI.org_information_context = 'Legal Entity Accounting'
3406 AND to_char(LEI.organization_id) = OUI.org_information2
3407 AND OUI.org_information_context = 'Operating Unit Information'
3408 AND OUI.organization_id = OU.organization_id;
3409 EXCEPTION
3410 when no_data_found then
3411 -- print_Debug('cannot find the set of book of the ou ', 'Get_Functional_Currency_Code');
3412 -- Modified the message text set of books to ledger for making the message compatible with LE uptake project
3413 print_Debug('cannot find the ledger of the ou ', 'Get_Functional_Currency_Code');
3414 FND_MESSAGE.SET_NAME('INV', 'IC-INVALID BOOKS');
3415 FND_MESSAGE.SET_TOKEN('ID', p_org_id);
3416 FND_MSG_PUB.ADD;
3417 raise FND_API.G_EXC_ERROR;
3418 END;
3419 */
3420 BEGIN
3421 l_progress := 1;
3422 Inv_globals.get_ledger_info(
3423 x_return_status => lreturn_status,
3424 x_msg_data => lmsg_data ,
3425 p_context_type => 'Operating Unit Information',
3426 p_org_id => p_org_id,
3427 x_sob_id => lsob_id,
3428 x_coa_id => lcoa_id,
3429 p_account_info_context => 'SOB');
3430 IF NVL(lreturn_status , 'S') = 'E' THEN
3431 FND_MESSAGE.SET_NAME('INV', 'IC-INVALID BOOKS');
3432 FND_MESSAGE.SET_TOKEN('ID', p_org_id);
3433 FND_MSG_PUB.ADD;
3434 print_debug('Cannot find the ledger information for operating unit = '||p_org_id , 9);
3435 RAISE FND_API.G_EXC_ERROR;
3436 END IF;
3437 l_set_of_book_id := lsob_id;
3438 END;
3439
3440
3441
3442 l_progress := 2;
3443 BEGIN
3444 select currency_code
3445 into l_functional_currency_code
3446 FROM gl_sets_of_books
3447 WHERE set_of_books_id = l_set_of_book_id;
3448 EXCEPTION
3449 when NO_DATA_FOUND then
3450 print_debug('cannot find the functional currency code', 'get_functional_currency_code');
3451 FND_MESSAGE.SET_NAME('SQLGL', 'GL funct curr does not exist');
3452 FND_MSG_PUB.ADD;
3453 raise FND_API.G_EXC_ERROR;
3454 end;
3455
3456 print_debug('l_functional_currency_code is ' || l_functional_currency_code, 'get_functional_currency_code');
3457 x_sets_of_book_id := l_set_of_book_id;
3458 return l_functional_currency_code;
3459
3460 EXCEPTION
3461 WHEN FND_API.G_EXC_ERROR then
3462 x_return_status := G_RET_STS_ERROR;
3463 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3464 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3465 return null;
3466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3467 x_return_status := G_RET_STS_UNEXP_ERROR;
3468 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3469 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3470 return null;
3471
3472 WHEN OTHERS THEN
3473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3474 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3475 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3476
3477 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3478 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, 'get_functional_currency_code');
3479 end if;
3480
3481 print_debug('in When Others, l_progress = ' || l_progress, 'get_functional_currency_code');
3482 print_Debug(sqlerrm, 'get_functional_currency_code');
3483 return null;
3484
3485 END get_functional_currency_code;
3486
3487
3488 /* Package: INV_TRANSACTION_FLOWS_PUB
3489 * Function: convert_currency (
3490 * Description: This function is used to convert the transfer price
3491 * to the functional currency of a particular operating unit.
3492 *
3493 * Inputs:
3494 * 1. p_org_id - the operating unit to which functional currency the
3495 * transfer price will be converted.
3496 * 2. p_transfer_price - the amount to be converted.
3497 * 3. p_currency_code - the original currency code to be converted to functional
3498 * currency
3499 * 4. p_transaction_date - the date for which the conversion rate is used
3500 *
3501 * Output:
3502 * 1. x_functional_currency_code - the functional currency code of the p_org_id
3503 * 2. x_return_status - return status
3504 * 3. x_msg_data - the message on the message stack.
3505 * 4. x_msg_count - the number of message in the message stack
3506 */
3507
3508 FUNCTION CONVERT_CURRENCY (
3509 p_org_id IN NUMBER
3510 , p_transfer_price IN NUMBER
3511 , p_currency_code IN VARCHAR2
3512 , p_transaction_date IN DATE
3513 , x_functional_currency_code OUT NOCOPY VARCHAR2
3514 , x_return_status OUT NOCOPY VARCHAR2
3515 , x_msg_data OUT NOCOPY VARCHAR2
3516 , x_msg_count OUT NOCOPY NUMBER
3517 ) RETURN NUMBER
3518 IS
3519 l_functional_currency_code VARCHAR2(30);
3520 l_set_of_book_id NUMBER;
3521 l_fixed_rate VARCHAR2(4) := 'N';
3522 l_conversion_type VARCHAR2(31);
3523 l_conversion_rate NUMBER;
3524 l_transfer_price NUMBER;
3525 l_progress NUMBER;
3526 BEGIN
3527 x_functional_currency_code := '';
3528 x_return_status := G_RET_STS_SUCCESS;
3529 x_msg_data := null;
3530 x_msg_count := 0;
3531
3532 print_debug('Start Convert_Currency', 'Convert_Currency');
3533 print_debug('p_org_id p_transfer_price p_currency_code p_transaction_date', 'Convert_currency');
3534 print_Debug(p_org_id || ' ' || p_transfer_price || ' ' || p_currency_code || ' ' || p_transaction_date,
3535 'Convert_currency');
3536
3537 if( G_FROM_ORG_ID = p_org_id ) THEN
3538 l_functional_currency_code := G_FUNCTIONAL_CURRENCY_CODE;
3539 l_set_of_book_id := G_SETS_OF_BOOK_ID;
3540 elsif( G_FROM_ORG_ID = -1 OR G_FROM_ORG_ID <> p_org_id ) then
3541 G_FROM_ORG_ID := p_org_id;
3542 l_functional_currency_code := get_functional_currency_code(
3543 p_org_id, l_set_of_book_id, x_return_status, x_msg_data, x_msg_count);
3544 if( x_return_status <> G_RET_STS_SUCCESS ) then
3545 raise FND_API.G_EXC_ERROR;
3546 else
3547 G_FUNCTIONAL_CURRENCY_CODE := l_functional_currency_code;
3548 G_SETS_OF_BOOK_ID := l_set_of_book_id;
3549 end if;
3550 end if;
3551
3552 x_functional_currency_code := l_functional_currency_code;
3553 print_debug('l_functional_currency_code is ' || l_functional_currency_code, 'convert_currency');
3554 print_Debug('calling gl_currency_api.is_fix_rate', 'convert_currency');
3555
3556 if( l_functional_currency_code <> p_currency_code ) then
3557 print_Debug('calling gl_currency_api.is_fix_rate', 'convert_currency');
3558 l_fixed_rate := gl_currency_api.is_fixed_rate(
3559 p_currency_code, l_functional_currency_code, p_transaction_date);
3560
3561 print_debug('l_fixed_rate is ' || l_fixed_rate, 'convert_currency');
3562 l_conversion_type := fnd_profile.value('IC_CURRENCY_CONVERSION_TYPE');
3563 if( l_fixed_rate = 'Y' ) then
3564 l_conversion_type := 'EMU FIXED';
3565 end if;
3566
3567 print_Debug('l_conversion_type is ' || l_conversion_type, 'convert_currency');
3568 print_Debug('l_set_of_book_id is ' || l_set_of_book_id, 'convert_currency');
3569 print_Debug('p_currency_code is ' || p_currency_code, 'convert_currency');
3570 print_Debug('p_transfer_price is ' || p_transfer_price, 'convert_currency');
3571 print_Debug('p_transaction_date is ' || p_transaction_date, 'convert_currency');
3572
3573
3574 print_debug('calling gl_currency_api.converT_amount_sql ', 'convert_currency');
3575 l_transfer_price := gl_currency_api.convert_amount_sql(
3576 x_set_of_books_id => l_set_of_book_id
3577 , x_from_currency => p_currency_code
3578 , x_conversion_date => p_transaction_date
3579 , x_conversion_type => l_conversion_type
3580 , x_amount => p_transfer_price
3581 );
3582
3583
3584 if( l_transfer_price = -1 ) then
3585 print_debug('ic no conversion rate', 'convert_currency');
3586 FND_MESSAGE.SET_NAME('INV', 'IC-No conversion rate');
3587 FND_MESSAGE.SET_TOKEN('CONV_TYPE', l_conversion_type);
3588 FND_MESSAGE.SET_TOKEN('FROM_CURR', p_currency_code);
3589 FND_MESSAGE.SET_TOKEN('TO_CURR', l_functional_currency_code);
3590 FND_MSG_PUB.ADD;
3591 raise FND_API.G_EXC_ERROR;
3592 elsif( l_transfer_price = -2 ) then
3593 print_Debug('ic invalid currency', 'convert_currency');
3594 FND_MESSAGE.SET_NAME('INV', 'IC-Invalid_Currency');
3595 FND_MESSAGE.SET_TOKEN('FROM_CURR', p_currency_code);
3596 FND_MSG_PUB.ADD;
3597 raise FND_API.G_EXC_ERROR;
3598 end if;
3599 else
3600 l_transfer_price := p_transfer_price;
3601 end if;
3602
3603 print_debug('l_transfer_price is ' || l_transfer_price, 'convert_currency');
3604 x_return_status := G_RET_STS_SUCCESS;
3605 fnd_msg_pub.count_and_get (
3606 p_encoded => fnd_api.g_false,
3607 p_count => x_msg_count,
3608 p_data => x_msg_data
3609 );
3610
3611
3612 return l_transfer_price;
3613
3614 EXCEPTION
3615 WHEN FND_API.G_EXC_ERROR then
3616 x_return_status := G_RET_STS_ERROR;
3617 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3618 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3619 return -99;
3620 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3621 x_return_status := G_RET_STS_UNEXP_ERROR;
3622 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3623 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3624 return -99;
3625
3626 WHEN OTHERS THEN
3627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3628 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3629 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
3630
3631 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3632 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, 'Convert_Currency');
3633 end if;
3634
3635 print_debug('in When Others, l_progress = ' || l_progress, 'Convert_Currency');
3636 print_Debug(sqlerrm, 'Convert_Currency');
3637 return -99;
3638
3639 end convert_currency;
3640
3641 /*==========================================================================================================
3642 * Package: INV_TRANSACTION_FLOWS_PUB
3643 *
3644 * Procedure: GET_TRANSFER_PRICE_FOR_ITEM
3645 *
3646 * Description:
3647 * This API gets the transfer price in the transaction UOM using the following defaulting mechanism:
3648 * 1. list price at transaction UOM I established transfer price list
3649 * 2. Transaction cost of shipment transaction.
3650 * This API will be called by Oracle Inventory as well as Oracle CTO for CTO item
3651 *
3652 * Inputs:
3653 * - From_Org_ID - the start operating unit
3654 * - To_Org_Id - The End operating Unit
3655 * - Transaction_UOM - the transaction units of meassure
3656 * - Invenotry_Item_ID - the inventory item identifier
3657 * - Transaction ID - the logical transaction id
3658 * - price_list_id - the static price list id.
3659 *
3660 * Outputs:
3661 * - x_transfer_price - the unit transfer price of the item
3662 * - x_currency_code - the currency code of the transfer price
3663 * - x_return_Status - the return status
3664 * - x_msg_data - the error message
3665 * - x_msg_count - the message count
3666 *
3667 * History:
3668 * umoogala 21-Apr-2006 Bug 5171637/5138311: Process/Discrete Xfers Enh.
3669 * Removed parameter p_process_discrete_xfer_flag added as part of above fix, and replaced it
3670 * with p_order_line_id to make it clear. Fix for bug 5126431 caused ORA error. The get_transfer_price
3671 * API expects, transaction_id. But, different programs pass different value in transaction_id.
3672 * --
3673 * INV: Logical Txn procedure (INVTLTPBB.pls) puts order line_id as transaction_id with
3674 * global_procurement flag to N.
3675 * INV: InterCompany Invocing Program: sends mmt.transaction_id.
3676 * RCV: puts rcv_transaction_id as transaction_id with global_procurement flag to 'Y'
3677 * and drop_ship_flag to 'N'.
3678 * GMF: calls this API with order line_id as transaction_id
3679 * --
3680 *
3681 *============================================================================================================*/
3682 Procedure get_transfer_price_for_item
3683 (
3684 x_return_status OUT NOCOPY VARCHAR2
3685 , x_msg_data OUT NOCOPY VARCHAR2
3686 , x_msg_count OUT NOCOPY NUMBER
3687 , x_transfer_price OUT NOCOPY NUMBER
3688 , x_currency_code OUT NOCOPY VARCHAR2
3689 , p_api_version IN NUMBER
3690 , p_init_msg_list IN VARCHAR2
3691 , p_from_org_id IN NUMBER
3692 , p_to_org_id IN NUMBER
3693 , p_transaction_uom IN VARCHAR2
3694 , p_inventory_item_id IN NUMBER
3695 , p_transaction_id IN NUMBER
3696 , p_from_organization_id IN NUMBER DEFAULT null
3697 , p_price_list_id IN NUMBER
3698 , p_global_procurement_flag IN VARCHAR2
3699 , p_drop_ship_flag IN VARCHAR2 DEFAULT 'N'
3700 , p_cto_item_flag IN VARCHAR2 DEFAULT 'N'
3701 -- , p_process_discrete_xfer_flag IN VARCHAR2 DEFAULT 'N' -- Bug 4750256
3702 , p_order_line_id IN VARCHAR2 DEFAULT NULL
3703 -- Bug 5171637/5138311 umoogala:
3704 -- replaced above line with this one.
3705 ) IS
3706 l_invoice_currency_code VARCHAR2(30);
3707 l_return_Status VARCHAR2(1);
3708 l_msg_data VARCHAR2(255);
3709 l_msg_count NUMBER;
3710 l_debug NUMBER := nvl(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
3711 l_transfer_price NUMBER := 0;
3712 l_qp_profile NUMBER := nvl(fnd_profile.value('INV_USE_QP_FOR_INTERCOMPANY'), 2);
3713 l_transfer_price_code NUMBER := -1;
3714 l_organization_id NUMBER;
3715 l_functional_currency_code VARCHAR2(30);
3716 l_uom_rate NUMBER;
3717 l_primary_uom VARCHAR2(4);
3718 l_transaction_date DATE;
3719 l_fixed_rate VARCHAR2(1);
3720 l_progress NUMBER;
3721
3722 l_api_version_number CONSTANT NUMBER := 1.0;
3723 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRANSFER_PRICE_FOR_ITEM';
3724 l_flow_type NUMBER := G_SHIPPING_FLOW_TYPE;
3725 l_transaction_id NUMBER;
3726 l_order_line_id NUMBER;
3727 l_order_header_id NUMBER;
3728 l_inventory_item_id NUMBER;
3729 l_currency_code VARCHAR2(30);
3730 l_currency_org NUMBER;
3731 l_qp_price_flag BOOLEAN := false;
3732 l_set_of_book_id NUMBER;
3733 l_inv_currency_code NUMBER;
3734 l_count NUMBER := 0;
3735 l_item_description VARCHAR2(255);
3736 l_price_list_name VARCHAR2(255);
3737
3738 l_from_ou_name VARCHAR2(255);
3739 l_to_ou_name VARCHAR2(255);
3740 l_cto_item_flag VARCHAR2(1) := p_cto_item_flag;
3741 l_ato_line_id number; --bug 5126431
3742 BEGIN
3743 x_return_status := G_RET_STS_SUCCESS;
3744 x_msg_data := null;
3745 x_msg_count := 0;
3746 x_transfer_price := 0;
3747 x_currency_code := null;
3748
3749 -- Standard call to check for call compatibility
3750 IF NOT FND_API.Compatible_API_Call(
3751 l_api_version_number
3752 , p_api_version
3753 , l_api_name
3754 , G_PKG_NAME)
3755 THEN
3756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3757 END IF;
3758
3759 -- Initialize message list.
3760 IF fnd_api.to_boolean(p_init_msg_list) THEN
3761 fnd_msg_pub.initialize;
3762 END IF;
3763
3764 --
3765 -- Bug 5171637/5138311 umoogala: INVCONV
3766 -- Added following IF condition.
3767 -- We calling this API from Inventory Interface when Sales Order is
3768 -- ship confirmed. So, we do not have MMT transaction_id at this point.
3769 -- For this reason, we are sending order_line_id, which gets used by
3770 -- Advanced Pricing Engine.
3771 --
3772 IF p_order_line_id IS NOT NULL
3773 THEN
3774 l_order_line_id := p_order_line_id;
3775 ELSE
3776
3777 BEGIN --5126431
3778 IF l_cto_item_flag = 'Y' THEN
3779
3780 select l.ato_line_id,l.header_id
3781 into l_ato_line_id,l_order_header_id
3782 from mtl_material_transactions mmt
3783 , oe_order_lines_all l
3784 WHERE MMT.transaction_id = p_transaction_id
3785 AND l.line_id = mmt.trx_source_line_id;
3786
3787 SELECT line_id
3788 INTO l_order_line_id
3789 from oe_order_lines_all
3790 where header_id=l_order_header_id
3791 and ato_line_id=l_ato_line_id
3792 and inventory_item_id=p_inventory_item_id;
3793 ELSE
3794
3795 SELECT trx_source_line_id
3796 INTO l_order_line_id
3797 FROM mtl_material_transactions
3798 WHERE transaction_id = p_transaction_id;
3799
3800 END IF;
3801 EXCEPTION
3802 when no_data_found then
3803 l_order_header_id := NULL;
3804 l_order_line_id := NULL;
3805 l_ato_line_id := NULL;
3806 END;
3807 --End of 5126431
3808 END IF;
3809
3810 --
3811 -- Bug 5527437 umoogala
3812 --
3813 IF (p_order_line_id IS NULL AND p_transaction_id IS NULL AND p_cto_item_flag = 'Y')
3814 THEN
3815 l_order_line_id := G_ORDER_LINE_ID;
3816 END IF;
3817
3818
3819 print_debug('START GET_TRANSFER_PRICE_FOR_ITEM', 'GET_TRANSFER_PRICE_FOR_ITEM');
3820 print_debug('Input Parameter ' , 'GET_TRANSFER_PRICE_FOR_ITEM');
3821 print_debug('--------------- ' , 'GET_TRANSFER_PRICE_FOR_ITEM');
3822 print_debug(' p_api_version = ' || p_api_version, 'GET_TRANSFER_PRICE_FOR_ITEM');
3823 print_debug(' p_init_msg_list = ' || p_init_msg_list, 'GET_TRANSFER_PRICE_FOR_ITEM');
3824 print_debug(' p_from_org_id = ' || p_from_org_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3825 print_debug(' p_to_org_id = ' || p_to_org_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3826 print_debug(' p_transaction_uom = ' || p_transaction_uom, 'GET_TRANSFER_PRICE_FOR_ITEM');
3827 print_debug(' p_inventory_item_id = ' || p_inventory_item_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3828 print_debug(' p_transaction_id = ' || p_transaction_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3829 print_debug(' p_price_list_id = ' || p_price_list_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3830 print_debug(' p_global_procurement_flag = ' || p_global_procurement_flag, 'GET_TRANSFER_PRICE_FOR_ITEM');
3831 print_debug(' p_drop_ship_flag = ' || p_drop_ship_flag, 'GET_TRANSFER_PRICE_FOR_ITEM');
3832 print_debug(' p_from_organization_id = ' || p_from_organization_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3833 print_debug(' p_cto_item_flag = ' || p_cto_item_flag, 'GET_TRANSFER_PRICE_FOR_ITEM');
3834 -- print_debug(' p_process_discrete_xfer_flag = ' || p_process_discrete_xfer_flag, 'GET_TRANSFER_PRICE_FOR_ITEM'); /* INVCONV Bug 4750256 */
3835 print_debug(' l_order_header_id = ' || l_order_header_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3836 print_debug(' l_ato_line_id = ' || l_ato_line_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3837 print_debug(' l_order_line_id = ' || l_order_line_id, 'GET_TRANSFER_PRICE_FOR_ITEM');
3838 print_debug(' G_ORDER_LINE_ID = ' || G_ORDER_LINE_ID, 'GET_TRANSFER_PRICE_FOR_ITEM');
3839
3840
3841
3842 print_debug('Calling mtl_intercompany_invoices.get_transfer_price ',
3843 'Get_Transfer_Price_For_Item');
3844
3845 --5126431: Added new parameter I_order_line_id
3846
3847 l_transfer_price := MTL_INTERCOMPANY_INVOICES.get_transfer_price(
3848 I_transaction_id => p_transaction_id
3849 , I_price_list_id => p_price_list_id
3850 , I_sell_ou_id => p_to_org_id
3851 , I_ship_ou_id => p_from_org_id
3852 , O_currency_code => l_invoice_currency_code
3853 , x_return_status => l_return_status
3854 , x_msg_count => l_msg_count
3855 , x_msg_data => l_msg_data
3856 , I_order_line_id => l_order_line_id
3857 );
3858 print_debug('Return Status from External API is ' || l_return_Status, 'Get_transfer_price_for_item');
3859 print_debug('Message Data from External API is ' || l_msg_data, 'Get_transfer_price_for_item');
3860 print_debug('Message Count from External API is ' || l_msg_count, 'Get_transfer_price_for_item');
3861 print_debug('Transfer price = ' || l_transfer_price, 'Get_transfer_price_for_item');
3862 print_debug('Currency code = ' || l_invoice_currency_code, 'Get_transfer_price_for_item');
3863
3864 if( l_return_status <> G_RET_STS_SUCCESS ) then
3865 print_Debug('Error from mtl_intercompany_invoices.get_transfer_price', 'get_transfer_price_for_item');
3866 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_EXT_TRANSFER_PRICE');
3867 FND_MSG_PUB.ADD;
3868 raise FND_API.G_EXC_ERROR;
3869 end if;
3870
3871 if( nvl(l_transfer_price, -1) = -1 ) then
3872 -- This means that we get nothing from the external api
3873 -- try to get the transfer price using the QP Engine
3874
3875 if( p_from_organization_id is not null ) then
3876 l_organization_id := p_from_organization_id;
3877 l_transaction_date := sysdate;
3878 else
3879 print_debug('p_from_organization_id is null, need to call get_inventory_org', 'get_transfer_price_for_item');
3880 print_debug('Calling get_inventory_org', 'get_transfer_price_for_item');
3881 l_organization_id := get_inventory_org(
3882 p_reference_id => p_transaction_id
3883 , p_global_procurement_flag => p_global_procurement_flag
3884 , p_drop_ship_flag => p_drop_ship_flag
3885 , x_transaction_date => l_transaction_date
3886 , x_return_status => l_return_status
3887 , x_msg_data => l_msg_data
3888 , x_msg_count => l_msg_count
3889 );
3890 print_debug('l_organization_id = ' || l_organization_id, 'geT_transfer_price_for_item');
3891
3892 if( l_return_status <> G_RET_STS_SUCCESS ) then
3893 print_debug('Error from get_inventory_org', 'get_transfer_price_for_item');
3894 raise FND_API.G_EXC_ERROR;
3895 end if;
3896 end if;
3897
3898 print_debug('l_qp_profile = ' || l_qp_profile, 'get_transfer_price_for_item');
3899 print_debug('l_qp_status = ' || qp_util.get_qp_status, 'get_transfer_price_for_item');
3900
3901 if( QP_UTIL.get_qp_status <> 'I' OR l_qp_profile <> 1 ) then
3902 print_debug('QP is not install', 'Get_transfer_price_for_item');
3903 print_debug('QP PRofile set to NO', 'Get_Transfer_Price_For_Item');
3904 print_debug('Get the static price list in transaction_uom', 'get_transfer_price_for_item');
3905 BEGIN
3906 l_transfer_price_code := 1;
3907 /*Bug: 5054047 Modified the SQL*/
3908 select SPLL.operand, substr(SPL.currency_code, 1, 15)
3909 INTO l_transfer_price, l_invoice_currency_code
3910 FROM qp_list_headers_b spl, qp_list_lines SPLL, qp_pricing_attributes qpa
3911 WHERE SPL.list_header_id = p_price_list_id
3912 AND SPLL.list_header_id = SPL.list_header_id
3913 AND SPLL.list_line_id = qpa.list_line_id
3914 AND qpa.product_attribute_context = 'ITEM'
3915 AND qpa.product_attribute = 'PRICING_ATTRIBUTE1'
3916 AND qpa.product_attr_value = to_Char(p_inventory_item_id)
3917 AND qpa.product_uom_code = p_transaction_uom
3918 AND sysdate between nvl(SPLL.start_date_active, (sysdate-1)) AND
3919 nvl(SPLL.end_date_active+0.99999, (sysdate+1))
3920 AND qpa.qualification_ind = 4
3921 AND qpa.excluder_flag = 'N'
3922 AND qpa.pricing_phase_id=1
3923 AND rownum = 1;
3924
3925 print_debug('l_transfer_price = ' || l_transfer_price, 'get_transfer_price_for_item');
3926 print_debug('l_invoice_currency_code = ' || l_invoice_currency_code, 'get_transfeR_price_for_item');
3927
3928 EXCEPTION
3929 when no_data_found then
3930 print_debug('Get static price list in primary uom', 'get_transfeR_price_for_item');
3931 BEGIN
3932 l_transfer_price_code := 2;
3933 /*Bug: 5054047 Modified the SQL*/
3934 SELECT SPLL.operand, substr(SPL.currency_code, 1, 15), msi.primary_uom_code
3935 INTO l_transfer_price, l_invoice_currency_code, l_primary_uom
3936 FROM QP_LIST_HEADERS_B SPL, QP_LIST_LINES SPLL,
3937 QP_PRICING_ATTRIBUTES QPA, MTL_SYSTEM_ITEMS_B MSI
3938 WHERE MSI.organization_id = l_organization_id
3939 AND MSI.inventory_item_id = p_inventory_item_id
3940 AND SPL.list_header_id = p_price_list_id
3941 AND SPLL.list_header_id = SPL.list_header_id
3942 AND QPA.list_header_id = SPL.list_header_id
3943 AND SPLL.list_line_id = QPA.list_line_id
3944 AND QPA.product_attribute_context = 'ITEM'
3945 AND QPA.product_attribute = 'PRICING_ATTRIBUTE1'
3946 AND QPA.product_attr_value = to_char(MSI.inventory_item_id)
3947 AND QPA.product_uom_code = MSI.primary_uom_code
3948 AND sysdate between nvl(SPLL.start_date_active, (sysdate-1))
3949 AND nvl(SPLL.end_date_active + 0.99999, (sysdate+1))
3950 AND qpa.qualification_ind = 4
3951 AND qpa.excluder_flag = 'N'
3952 AND qpa.pricing_phase_id=1
3953 AND rownum = 1;
3954 EXCEPTION
3955 when no_data_found THEN
3956 print_debug('no price list found', 'get_transfer_price_for_item');
3957
3958 if( l_cto_item_flag = 'Y' ) then
3959 l_transfer_price := -99;
3960 else
3961 l_transfer_price := -99;
3962 l_return_status := G_RET_STS_ERROR;
3963 SELECT concatenated_segments, primary_uom_code
3964 INTO l_item_description, l_primary_uom
3965 FROM mtl_system_items_kfv
3966 WHERE organization_id = l_organization_id
3967 AND inventory_item_id = p_inventory_item_id;
3968
3969 SELECT name
3970 into l_price_list_name
3971 FROM QP_LIST_HEADERS
3972 WHERE list_header_id = p_price_list_id;
3973
3974 FND_MESSAGE.SET_NAME('QP', 'QP_PRC_NO_LIST_PRICE');
3975 FND_MESSAGE.SET_TOKEN('ITEM', l_item_description);
3976 FND_MESSAGE.SET_TOKEN('UNIT', l_primary_uom);
3977 FND_MESSAGE.SET_TOKEN('PRICE_LIST', l_price_list_name);
3978 FND_MSG_PUB.ADD;
3979 raise fnd_api.g_exc_error;
3980 end if;
3981 end;
3982 when others then
3983 print_debug('sqlerrm = ' || sqlerrm, 'get_transfer_price_for_item');
3984 l_transfer_price := 0;
3985 l_return_status := G_RET_STS_ERROR;
3986 FND_MESSAGE.SET_NAME('INV', 'IC-No Transfer Price');
3987 FND_MSG_PUB.ADD;
3988 raise fnd_api.g_exc_error;
3989 END;
3990 else
3991 l_qp_price_flag := TRUE;
3992 if( p_global_procurement_flag = 'Y' ) then
3993 l_flow_type := G_PROCURING_FLOW_TYPE;
3994 end if;
3995 print_Debug('after setting the l_flow_type ', 'Get_transfer_price_for_item');
3996
3997 --
3998 -- Setting order_line_id and transaction_id
3999 --
4000 IF (p_order_line_id IS NULL AND p_transaction_id IS NULL AND p_cto_item_flag = 'Y') THEN
4001 print_Debug('Value of l_order_line_id = '||l_order_line_id, 'Get_transfer_price_for_item');
4002 ELSIF p_order_line_id IS NOT NULL
4003 then
4004 --
4005 -- Bug 5171637/5138311 umoogala: INVCONV
4006 -- Added this IF block
4007 --
4008 l_order_line_id := p_order_line_id;
4009 l_transaction_id := null;
4010 else
4011 if( p_drop_ship_flag = 'Y' )
4012 then
4013 l_order_line_id := nvl(l_order_line_id, p_transaction_id);
4014 l_transaction_id := null;
4015 --
4016 -- Bug 5171637/5138311 umoogala: Commented following code and
4017 -- replaced with first IF condition above.
4018 --
4019 -- Bug 4750256, OPM INVCONV: Added this elsif block
4020 -- elsif( p_drop_ship_flag = 'N' AND p_process_discrete_xfer_flag = 'Y')
4021 -- then
4022 -- l_order_line_id := p_transaction_id;
4023 -- l_transaction_id := null;
4024 else
4025 print_debug(' p_drop_ship_flag is ' || p_drop_ship_flag, 'Get_transfer_price_for_item');
4026 if( p_cto_item_flag = 'N') then
4027 l_transaction_id := p_transaction_id;
4028 l_order_line_id := null;
4029 else
4030 BEGIN
4031 select 1
4032 INTO l_count
4033 From mtl_material_transactions
4034 WHERE transaction_id = p_transaction_id;
4035
4036 l_transaction_id := p_transaction_id;
4037 l_order_line_id := null;
4038 EXCEPTION
4039 WHEN no_data_found then
4040 l_order_line_id := p_transaction_id;
4041 l_transaction_id := null;
4042 END;
4043 end if;
4044 end if;
4045 end if;
4046
4047 print_debug('After setting the l_order_line_id ' || l_order_line_id || ' l_transaction_id ' || l_transaction_id,
4048 'Get_Transfer_price_for_item');
4049
4050 /* BEGIN
4051 print_debug('about to delete the qp temp table', 'Get_Transfer_price_for_item');
4052 select count(*)
4053 into l_count
4054 From qp_preq_lines_tmp;
4055
4056 if( l_count > 0 ) then
4057 Delete from qp_preq_lines_tmp_t;
4058 end if;
4059 EXCEPTION
4060 when others then
4061 fnd_message.set_name('INV', 'INV_INT_SQLCODE');
4062 fnd_msg_pub.add;
4063 raise FND_API.G_EXC_ERROR;
4064 end;*/
4065
4066 /* Added this for bug 3141793
4067 * The currency code in which the transfer price will be return is control by the
4068 * value of inv_currency_code column in the mtl_intercompany_parameters
4069 * The possible value of inv_currency_code are:
4070 * 1 - Currency of the SHip/From/Procuring OU
4071 * 2 - Currency of the Sell/To/Receiving OU
4072 * 3 - Order Currency (Sales Order/Purchase Order)
4073 * We will cache the OU in which the currency will return
4074 * And also will cache the currency code
4075 */
4076 if( p_global_procurement_flag = 'Y' ) then
4077 l_flow_type := 2;
4078 else
4079 l_flow_type := 1;
4080 end if;
4081
4082 /* Bug 4903269 moved the call in order to get the option of currency of sales order to work */
4083 BEGIN
4084 select nvl(inv_currency_code, 1)
4085 into l_inv_currency_code
4086 From mtl_intercompany_parameters
4087 where ship_organization_id = p_from_org_id
4088 and sell_organization_id = p_to_org_id
4089 and flow_type = l_flow_type;
4090 EXCEPTION
4091 when no_data_found then
4092 print_debug('No IC Relations exists between from OU and To OU', 'GET_TRANSFER_PRICE_FOR_ITEM');
4093 SELECT name
4094 INTO l_from_ou_name
4095 FROM hr_operating_units
4096 WHERE organization_id = p_from_org_id;
4097
4098
4099 SELECT name
4100 INTO l_to_ou_name
4101 FROM hr_operating_units
4102 WHERE organization_id = p_to_org_id;
4103
4104 FND_MESSAGE.SET_NAME('INV', 'IC-No INTERCO RELATION');
4105 FND_MESSAGE.SET_TOKEN('FROM_OU', l_from_ou_name);
4106 FND_MESSAGE.SET_TOKEN('TO_OU', l_to_ou_name);
4107 FND_MSG_PUB.ADD;
4108 raise FND_API.G_EXC_ERROR;
4109 END;
4110
4111
4112
4113 if( (G_FROM_ORG_ID = -1 AND G_TO_ORG_ID = -1 AND G_INV_CURR_ORG = -1)
4114 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
4115 -- This means that this API is called for the first time or the from OU and to OU
4116 -- is not the same as the last time when this API is called.
4117 -- Need to query the inv_currency_code from the database.
4118
4119 G_FROM_ORG_ID := p_from_org_id;
4120 G_TO_ORG_ID := p_to_org_id;
4121 G_FLOW_TYPE := l_flow_type;
4122
4123 if( l_inv_currency_code = 1 ) then
4124 l_currency_org := p_from_org_id;
4125 elsif( l_inv_currency_code = 2 ) then
4126 l_currency_org := p_to_org_id;
4127 else
4128 l_currency_org := 0;
4129 end if;
4130
4131 G_INV_CURR_ORG := l_currency_org;
4132
4133 G_FUNCTIONAL_CURRENCY_CODE := get_functional_currency_code(
4134 p_from_org_id, l_set_of_book_id, x_return_status, x_msg_data, x_msg_count);
4135
4136 G_SETS_OF_BOOK_ID := l_set_of_book_id;
4137
4138 if( l_currency_org > 0 ) then
4139 l_currency_code := get_functional_currency_code(
4140 l_currency_org, l_set_of_book_id, x_return_status, x_msg_data, x_msg_count);
4141
4142 if( x_return_status <> G_RET_STS_SUCCESS ) then
4143 raise FND_API.G_EXC_ERROR;
4144 end if;
4145 end if;
4146 G_INV_CURR_CODE := l_currency_code;
4147 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
4148 l_currency_org := G_INV_CURR_ORG;
4149 l_currency_code := G_INV_CURR_CODE;
4150 --null;
4151 END IF;
4152
4153 print_Debug('Calling mtl_qp_price.get_transfer_price', 'Get_transfer_price_for_item');
4154 l_transfer_price := MTL_QP_PRICE.get_transfer_price_ds(
4155 p_transaction_id => l_transaction_id
4156 , p_sell_ou_id => p_to_org_id
4157 , p_ship_ou_id => p_from_org_id
4158 , p_flow_type => l_flow_type
4159 , p_order_line_id => l_order_line_id
4160 , p_inventory_item_id => p_inventory_item_id
4161 , p_organization_id => l_organization_id
4162 , p_uom_code => p_transaction_uom
4163 , p_cto_item_flag => p_cto_item_flag
4164 , p_incr_code => l_inv_currency_code
4165 , p_incrcurrency => l_currency_code
4166 , x_currency_code => l_invoice_currency_code
4167 , x_tfrPriceCode => l_transfer_price_code
4168 , x_return_status => l_return_status
4169 , x_msg_count => l_msg_count
4170 , x_msg_data => l_msg_data
4171 );
4172
4173 print_Debug('l_transfer_price is ' || l_transfer_price, 'get_transfer_price_for_item');
4174 print_Debug('l_transfer_price_code = ' || l_transfer_price_code, 'get_transfer_price_for_item');
4175 print_Debug('l_invoice_currency_code = ' || l_invoice_currency_code, 'get_transfer_price_for_item');
4176 print_Debug('l_return_status = ' || l_return_status, 'get_transfer_price_for_item');
4177 print_Debug('l_msg_data = ' || l_msg_data, 'get_transfer_price_for_item');
4178 print_Debug('l_msg_count = ' || l_msg_count, 'get_transfer_price_for_item');
4179
4180 if( l_return_status <> G_RET_STS_SUCCESS ) THEN
4181 print_Debug('Error from mtl_qp_price', 'get_transfer_price_for_item');
4182 raise FND_API.G_EXC_ERROR;
4183 end if;
4184
4185 IF( l_transfer_price is NULL or l_transfer_price = -99 ) then
4186 SELECT description
4187 into l_item_description
4188 FROM mtl_system_items
4189 WHERE organization_id = l_organization_id
4190 AND inventory_item_id = p_inventory_item_id;
4191 END IF;
4192
4193 if( l_transfer_price = -99 ) THEN
4194 print_Debug('qp price is wrong', 'get_transfeR_price_for_item');
4195 FND_MESSAGE.SET_NAME('INV', 'INV_QP_PRICE_ERROR');
4196 FND_MESSAGE.SET_TOKEN('ITEM', l_item_description);
4197 FND_MSG_PUB.ADD;
4198 l_transfer_price := 0;
4199 end if;
4200
4201 if( l_transfer_price is null ) then
4202 print_Debug('qp price is wrong', 'get_transfeR_price_for_item');
4203 FND_MESSAGE.SET_NAME('INV', 'INV_QP_PRICE_ERROR');
4204 FND_MESSAGE.SET_TOKEN('ITEM', l_item_description);
4205 FND_MSG_PUB.ADD;
4206 l_transfer_price := 0;
4207 end if;
4208 end if; -- if( QP_UTIL.get_qp_status <> 'I' OR l_qp_profile <> 1 )
4209 end if; -- if( nvl(l_transfer_price, -1) = -1 )
4210
4211 l_progress := 2;
4212 if( l_debug = 1 ) then
4213 print_debug('Calling uom_conversion', 'get_transfer_price_for_item');
4214 end if;
4215
4216 print_debug('l_transfer_price_code = '||l_transfer_price_code, 'get_transfer_price_for_item');
4217 print_debug('l_organization_id = '||l_organization_id, 'get_transfer_price_for_item');
4218
4219 if( nvl(l_transfer_price,0) > 0 AND l_transfer_price_code = 2 ) then
4220
4221 IF l_primary_uom IS NULL THEN
4222 SELECT primary_uom_code
4223 INTO l_primary_uom
4224 FROM mtl_system_items
4225 WHERE organization_id = l_organization_id
4226 AND inventory_item_id = p_inventory_item_id;
4227 END IF;
4228
4229 -- do uom conversion
4230 print_debug('Calling uom_conversion', 'get_transfer_price_for_item');
4231 INV_CONVERT.inv_um_conversion(
4232 from_unit => p_transaction_uom
4233 , to_unit => l_primary_uom
4234 , item_id => p_inventory_item_id
4235 , uom_rate => l_uom_rate
4236 );
4237
4238 if( l_uom_rate = -99999 ) then
4239 print_debug('Error from Calling uom_conversion', 'get_transfer_price_for_item');
4240 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_UOM_CONV');
4241 FND_MESSAGE.SET_TOKEN('VALUE1', p_transaction_uom);
4242 FND_MESSAGE.SET_TOKEN('VALUE2', l_primary_uom);
4243 FND_MSG_PUB.ADD;
4244 raise FND_API.G_EXC_ERROR;
4245 end if;
4246 print_debug('l_uom_rate is ' || l_uom_rate, 'get_transfer_price_for_item');
4247
4248 l_transfer_price := l_uom_rate * l_transfer_price;
4249 end if;
4250
4251 if( l_debug = 1 ) then
4252 print_debug('Calling convert_to_functional_currency', 'get_transfer_price_for_item');
4253 end if;
4254
4255 /* Commented out the following currency conversion to From OU currency for Static Pricing*/
4256 /* Bug 4159025 */
4257 /* if( QP_UTIL.get_qp_status <> 'I' OR l_qp_profile <> 1 ) THEN
4258 l_transfer_price := convert_currency(
4259 p_org_id => p_from_org_id
4260 , p_transfer_price => l_transfer_price
4261 , p_currency_code => l_invoice_currency_code
4262 , p_transaction_date => l_transaction_date
4263 , x_functional_currency_code => l_functional_currency_code
4264 , x_return_status => l_return_status
4265 , x_msg_data => x_msg_data
4266 , x_msg_count => x_msg_count
4267 );
4268
4269 if( l_return_status <> G_RET_STS_SUCCESS ) then
4270 print_debug('Error from convert_currency', 'get_transfer_price_for_item');
4271 raise FND_API.G_EXC_ERROR;
4272 end if;
4273 else*/
4274
4275 l_functional_currency_code := l_invoice_currency_code;
4276
4277 print_Debug('l_transfer_price = ' || l_transfer_price, 'get_transfer_price_for_item');
4278 print_Debug('x_currency_code = ' || l_functional_currency_code, 'get_transfer_price_for_item');
4279
4280 /** THe currency code return is if it's not qp, return the functional currency of the shipping OU
4281 else if it is QP, return the currency code pass to qp **/
4282
4283 x_transfer_price := l_transfer_price;
4284 x_currency_code := l_functional_currency_code;
4285 x_return_status := l_return_status;
4286
4287 fnd_msg_pub.count_and_get(
4288 p_encoded => fnd_api.g_false,
4289 p_count => x_msg_count,
4290 p_data => x_msg_data
4291 );
4292 EXCEPTION
4293 WHEN FND_API.G_EXC_ERROR THEN
4294 x_return_status := G_RET_STS_ERROR;
4295 x_transfer_price := -99;
4296 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
4297 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4298
4299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4300 x_return_status := G_RET_STS_UNEXP_ERROR;
4301 x_transfer_price := -99;
4302 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
4303 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4304
4305 WHEN OTHERS then
4306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4307 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
4308 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4309
4310 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4311 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, l_api_name);
4312 end if;
4313
4314 print_debug('in When Others, l_progress = ' || l_progress, 'Get_Transfer_price');
4315
4316 END get_transfer_price_for_item;
4317
4318 /*==========================================================================================================
4319 * Procedure: GET_TRANSFER_PRICE
4320 *
4321 * Description:
4322 * This API is wrapper API to the Get_Transfer_Price API.
4323 * This API will be called by Oracle Inventory Create_logical_transaction API
4324 * as well as Oracle Costing.
4325 * This API will be called with transaction_uom as : PO UOM or SO UOM, whichever is applicable.
4326 * The API will return the transfer_price in the Transaction_UOM that was passed to it.
4327 * The currency of the price will be the currency set in the price list.
4328 * The calling program will take care of appropriate conversions of UOM and currency.
4329 *
4330 * Inputs:
4331 * - From_Org_ID - the start operating unit
4332 * - To_Org_Id - The End operating Unit
4333 * - Transaction UOM - the units of meassure
4334 * - Invenotry_Item_ID - the inventory item identifier
4335 * - Transaction ID - the inventory transaction ID
4336 * Outputs:
4337 * - x_transfer_price - The total price for the item. If there are no pricelist found, then return 0
4338 * - x_currency_code - the currency code of the transfer price
4339 * - x_return_status - the return status - S - success, E - Error, U - Unexpected Error
4340 * - x_msg_data - the error message
4341 * - x_msg_count - the number of messages in the message stack.
4342 *
4343 *==========================================================================================================*/
4344
4345 Procedure Get_Transfer_Price
4346 (
4347 x_return_status OUT NOCOPY VARCHAR2
4348 , x_msg_data OUT NOCOPY VARCHAR2
4349 , x_msg_count OUT NOCOPY NUMBER
4350 , x_transfer_price OUT NOCOPY NUMBER
4351 , x_currency_code OUT NOCOPY VARCHAR2
4352 , x_incr_transfer_price OUT NOCOPY NUMBER
4353 , x_incr_currency_code OUT NOCOPY VARCHAR2
4354 , p_api_version IN NUMBER
4355 , p_init_msg_list IN VARCHAR2
4356 , p_from_org_id IN NUMBER
4357 , p_to_org_id IN NUMBER
4358 , p_transaction_uom IN VARCHAR2
4359 , p_inventory_item_id IN NUMBER
4360 , p_transaction_id IN NUMBER
4361 , p_from_organization_id IN NUMBER DEFAULT NULL
4362 , p_global_procurement_flag IN VARCHAR2
4363 , p_drop_ship_flag IN VARCHAR2 DEFAULT 'N'
4364 -- , p_process_discrete_xfer_flag IN VARCHAR2 DEFAULT 'N' -- Bug 4750256
4365 , p_order_line_id IN VARCHAR2 DEFAULT NULL
4366 -- Bug 5171637/5138311 umoogala:
4367 -- replaced above line with this one.
4368 ) IS
4369
4370 l_base_item NUMBER := 0;
4371 l_organization_id NUMBER := 0;
4372 l_transaction_action_id NUMBER := 0;
4373 l_transaction_source_type_id NUMBER := 0;
4374 l_transaction_type_id NUMBER := 0;
4375 l_trx_source_line_id NUMBER := 0;
4376 l_exists NUMBER := 0;
4377 l_transfer_price NUMBER := 0;
4378 l_currency_code VARCHAR2(30);
4379 l_inventory_item_id NUMBER;
4380 l_functional_currency_code VARCHAR2(30);
4381 l_debug NUMBER := nvl(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
4382 l_return_status VARCHAR2(1);
4383 l_msg_data VARCHAR2(255);
4384 l_msg_count NUMBER;
4385 l_transaction_date DATE;
4386 l_price_list_id NUMBER;
4387 l_progress NUMBER;
4388 l_api_version_number CONSTANT NUMBER := 1.0;
4389 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRANSFER_PRICE';
4390 l_inv_transfer_price NUMBER;
4391 l_inv_currency_code VARCHAR2(30);
4392 l_flow_type NUMBER;
4393 l_from_ou_name VARCHAR2(240);
4394 l_to_ou_name VARCHAR2(240);
4395 l_location VARCHAR2(40);
4396 l_customer_number VARCHAR2(30);
4397 --For bug6460311.column length in table hz_parties is varchar2(360)
4398 --l_customer_name VARCHAR2(50);
4399 l_customer_name VARCHAR2(360);
4400 l_cto_item_flag VARCHAR2(1);
4401 l_trx_src_type_id NUMBER := NULL;
4402 l_inventory_item_id NUMBER := p_inventory_item_id;
4403 BEGIN
4404 x_return_status := G_RET_STS_SUCCESS;
4405 x_msg_data := null;
4406 x_msg_count := 0;
4407 x_transfer_price := 0;
4408 x_currency_code := null;
4409
4410 -- Standard call to check for call compatibility
4411 IF NOT FND_API.Compatible_API_Call(
4412 l_api_version_number
4413 , p_api_version
4414 , l_api_name
4415 , G_PKG_NAME)
4416 THEN
4417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4418 END IF;
4419
4420
4421 -- Initialize message list.
4422 IF fnd_api.to_boolean(p_init_msg_list) THEN
4423 fnd_msg_pub.initialize;
4424 END IF;
4425
4426
4427 print_debug('START GET_TRANSFER_PRICE', 'GET_TRANSFER_PRICE');
4428 print_Debug('Input Parameters', 'GET_TRANSFER_PRICE');
4429 print_Debug('----------------', 'GET_TRANSFER_PRICE');
4430 print_Debug('p_api_version is ' || p_api_version, 'GET_TRANSFER_PRICE');
4431 print_Debug('p_init_msg_list' || p_init_msg_list, 'GET_TRANSFER_PRICE');
4432 print_Debug('p_from_org_id is ' || p_from_org_id, 'GET_TRANSFER_PRICE');
4433 print_Debug('p_to_org_id is ' || p_to_org_id, 'GET_TRANSFER_PRICE');
4434 print_Debug('p_transaction_uom is ' || p_transaction_uom, 'GET_TRANSFER_PRICE');
4435 print_Debug('p_inventory_item_id is ' || p_inventory_item_id, 'GET_TRANSFER_PRICE');
4436 print_Debug('p_transaction_id is ' || p_transaction_id, 'GET_TRANSFER_PRICE');
4437 print_Debug('p_global_procurement_flag is ' || p_global_procurement_flag, 'GET_TRANSFER_PRICE');
4438 print_Debug('p_drop_ship_flag is ' || p_drop_ship_flag, 'GET_TRANSFER_PRICE');
4439 print_Debug('p_from_organization_id is ' || p_from_organization_id, 'GET_TRANSFER_PRICE');
4440 -- print_Debug('p_process_discrete_xfer_flag is ' || p_process_discrete_xfer_flag, 'GET_TRANSFER_PRICE'); /* INVCONV Bug 4750256 */
4441 print_Debug('p_order_line_id is ' || p_order_line_id, 'GET_TRANSFER_PRICE'); -- Bug 5171637/5138311 umoogala: INVCONV
4442
4443
4444 if( p_global_procurement_flag = 'Y' ) then
4445 l_flow_type := 2;
4446 else
4447 l_flow_type := 1;
4448 end if;
4449
4450 print_debug('Calling get_inventory_org ', 'GET_TRANSFER_PRICE');
4451 if( p_from_organization_id is not null ) then
4452 l_organization_id := p_from_organization_id;
4453 l_transaction_date := sysdate;
4454 else
4455
4456 l_organization_id := get_inventory_org(
4457 p_reference_id => p_transaction_id
4458 , p_global_procurement_flag => p_global_procurement_flag
4459 , p_drop_ship_flag => p_drop_ship_flag
4460 , x_transaction_date => l_transaction_date
4461 , x_return_status => l_return_status
4462 , x_msg_data => l_msg_data
4463 , x_msg_count => l_msg_count
4464 );
4465
4466 if( l_return_status <> G_RET_STS_SUCCESS ) then
4467 print_debug('Error from get_inventory_org', 'GET_TRANSFER_PRICE');
4468 raise FND_API.G_EXC_ERROR;
4469 end if;
4470 end if;
4471
4472 print_debug('Inventory org is ' || l_organization_id, 'GET_TRANSFER_PRICE');
4473
4474 l_progress := 1;
4475 print_debug('Check if the from OU is valid ', 'GET_TRANSFER_PRICE');
4476
4477 select count(organization_id)
4478 into l_exists
4479 FROM HR_ORGANIZATION_INFORMATION HOI
4480 WHERE HOI.ORG_INFORMATION3 = to_char(p_from_org_id)
4481 AND HOI.ORGANIZATION_ID= l_organization_id
4482 AND HOI.ORG_INFORMATION_CONTEXT = 'Accounting Information';
4483
4484 if( l_exists = 0 ) then
4485 print_debug('FROM OU is invalid', 'GET_TRANSFER_PRICE');
4486 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_FROM_OU');
4487 FND_MSG_PUB.ADD;
4488 raise FND_API.G_EXC_ERROR;
4489 -- throw error invalid from operating unit
4490 end if;
4491
4492 l_progress := 2;
4493 print_debug('Check if IC Relations exists between the From OU and TO OU', 'GET_TRANSFER_PRICE');
4494
4495 BEGIN
4496 select 1
4497 into l_exists
4498 FROM mtl_intercompany_parameters
4499 where sell_organization_id = p_to_org_id
4500 AND ship_organization_id = p_from_org_id
4501 and flow_type = l_flow_type;
4502 EXCEPTION
4503 WHEN no_data_found then
4504 print_debug('No IC Relations exists between from OU and To OU', 'GET_TRANSFER_PRICE');
4505 select name
4506 INTO l_from_ou_name
4507 FROM hr_operating_units
4508 where organization_id = p_from_org_id;
4509
4510 SELECT NAME
4511 INTO l_to_ou_name
4512 From HR_OPERATING_UNITS
4513 Where organization_id = p_to_org_id;
4514
4515 FND_MESSAGE.SET_NAME('INV', 'IC-No INTERCO Relation');
4516 FND_MESSAGE.SET_TOKEN('FROM_OU', l_from_ou_name);
4517 FND_MESSAGE.SET_TOKEN('TO_OU', l_to_ou_name);
4518 FND_MSG_PUB.ADD;
4519 raise FND_API.G_EXC_ERROR;
4520 -- throw error invalid no intercompany parameters defined
4521 END;
4522
4523 l_progress := 3;
4524 print_Debug('Get the base_item_id for the inventory_item_id ' || p_inventory_item_id, 'GET_TRANSFER_PRICE');
4525 BEGIN
4526 select nvl(base_item_id, 0)
4527 into l_base_item
4528 from mtl_system_items_b
4529 where inventory_item_id = p_inventory_item_id
4530 and organization_id = l_organization_id;
4531
4532 EXCEPTION
4533 when no_data_found then
4534 -- throw unexpected error;
4535
4536 print_debug('Cannot find item ' || p_inventory_item_id, 'GET_TRANSFER_PRICE');
4537 FND_MESSAGE.SET_NAME('INV', 'INV_IC_INVALID_ITEM_ORG');
4538 FND_MESSAGE.SET_TOKEN('ITEM', p_inventory_item_id);
4539 FND_MESSAGE.SET_TOKEN('ORG', l_organization_id);
4540 FND_MSG_PUB.ADD;
4541 raise FND_API.G_EXC_ERROR;
4542 END;
4543
4544 l_progress := 4;
4545 -- get price list id
4546 print_Debug('Get price list id ' , 'GET_TRANSFER_PRICE');
4547 BEGIN
4548 /* Modified query below : RA to HZ conversions
4549 Replaced occurances of RA views with HZ tables*/
4550 /* SELECT nvl(RSU.price_list_id, nvl(RC.price_list_id, -1)), RSU.location, RC.Customer_number, RC.Customer_name
4551 INTO l_price_List_Id, l_location, l_customer_number, l_customer_name
4552 FROM mtl_intercompany_parameters MIP
4553 , ra_site_uses_all RSU
4554 , ra_customers RC
4555 WHERE MIP.sell_organization_id = p_to_org_id
4556 AND MIP.ship_organization_id = p_from_org_id
4557 AND MIP.flow_type = l_flow_type
4558 AND RSU.site_use_id = MIP.customer_site_id
4559 AND RSU.org_id = MIP.ship_organization_id
4560 AND RC.customer_id = MIP.customer_id;
4561 */
4562
4563 SELECT NVL(rsu.price_list_id, NVL(rc.price_list_id, -1))
4564 , rsu.LOCATION
4565 , rc.customer_number
4566 , rc.customer_name
4567 INTO l_price_List_Id, l_location, l_customer_number, l_customer_name
4568 FROM mtl_intercompany_parameters mip
4569 , hz_cust_site_uses_all rsu
4570 , (SELECT cust_account_id customer_id
4571 , party.party_name customer_name
4572 , party.party_number customer_number
4573 , price_list_id
4574 FROM hz_parties party, hz_cust_accounts cust_acct
4575 WHERE cust_acct.party_id = party.party_id) rc
4576 WHERE mip.sell_organization_id = p_to_org_id
4577 AND mip.ship_organization_id = p_from_org_id
4578 AND mip.flow_type = l_flow_type
4579 AND rsu.site_use_id = mip.customer_site_id
4580 AND rsu.org_id = mip.ship_organization_id
4581 AND rc.customer_id = mip.customer_id;
4582
4583
4584
4585 if( l_price_list_id = -1 ) then
4586 FND_MESSAGE.SET_NAME('INV', 'IC-Price List Not Found');
4587 FND_MESSAGE.SET_TOKEN('LOC', l_location);
4588 FND_MESSAGE.SET_TOKEN('CUST_NUM', l_customer_number);
4589 FND_MESSAGE.SET_TOKEN('CUST_NAME', l_customer_name);
4590 FND_MSG_PUB.ADD;
4591 raise FND_API.G_EXC_ERROR;
4592 end if;
4593 EXCEPTION
4594 when no_data_found then
4595 print_debug('no price list found ', 'GET_TRANSFER_PRICE');
4596 SELECT name
4597 into l_from_ou_name
4598 FROM hr_operating_units
4599 WHERE organization_id = p_from_org_id;
4600
4601 FND_MESSAGE.SET_NAME('INV', 'IC-Invalid Customer');
4602 FND_MESSAGE.SET_TOKEN('OU', l_from_ou_name);
4603 FND_MSG_PUB.ADD;
4604 raise FND_API.G_EXC_ERROR;
4605 END;
4606
4607 -- call get_transfer_price_for_item for the base item
4608 if( l_base_item <> 0 ) then
4609 l_cto_item_flag := 'Y';
4610 else
4611 l_cto_item_flag := 'N';
4612 end if;
4613
4614 print_debug('Calling get_transfer_price_for_item', 'GET_TRANSFER_PRICE');
4615 get_transfer_price_for_item (
4616 x_return_Status => x_return_status
4617 , x_msg_data => x_msg_data
4618 , x_msg_count => x_msg_count
4619 , x_transfer_price => l_inv_transfer_price
4620 , x_currency_code => l_inv_currency_code
4621 , p_api_version => 1.0
4622 , p_init_msg_list => 'F'
4623 , p_from_org_id => p_from_org_id
4624 , p_to_org_id => p_to_org_id
4625 , p_transaction_uom => p_transaction_uom
4626 , p_inventory_item_id => p_inventory_item_id
4627 , p_transaction_id => p_transaction_id
4628 , p_from_organization_id => l_organization_id
4629 , p_price_list_id => l_price_list_id
4630 , p_global_procurement_flag => p_global_procurement_flag
4631 , p_drop_ship_flag => p_drop_ship_flag
4632 , p_cto_item_flag => l_cto_item_flag
4633 -- , p_process_discrete_xfer_flag => p_process_discrete_xfer_flag /* INVCONV Bug 4750256 */
4634 , p_order_line_id => p_order_line_id -- Bug 5171637/5138311 umoogala:
4635 -- replaced above line with this one.
4636 );
4637
4638 if( l_cto_item_flag = 'N' AND x_return_status <> G_RET_STS_SUCCESS ) then
4639 print_debug('Error from get_transfer_price_for_item', 'GET_TRANSFER_PRICE');
4640 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4641 end if;
4642
4643 /* Added following logic for ISO with confgiured items calling of the CTO roll-up price API
4644 only when the price is not associated with the configured item */
4645 BEGIN
4646 SELECT transaction_source_type_id into l_trx_src_type_id
4647 FROM mtl_material_transactions
4648 WHERE transaction_id = p_transaction_id;
4649 if ( l_trx_src_type_id = 8 ) AND ( l_base_item <> 0 ) AND ( nvl(l_inv_transfer_price,0) <> 0 )
4650 AND ( l_inv_currency_code is not null ) then
4651 l_transfer_price := l_inv_transfer_price;
4652 end if;
4653 EXCEPTION WHEN OTHERS THEN
4654 l_trx_src_type_id := NULL;
4655 END;
4656 /* End for ISO with confgiured items changes */
4657
4658 print_debug('l_transfer_price = ' || l_inv_transfer_price, 'GET_TRANSFER_PRICE');
4659 print_Debug('l_currency_code = ' || l_inv_currency_code, 'GET_TRANSFER_PRICE');
4660 print_debug('l_base_item = ' || l_base_item, 'GET_TRANSFER_PRICE');
4661
4662 -- Bug 4366773: In the IF condition modified the variable from l_transfer_price to l_inv_transfer_price
4663
4664 --
4665 -- Bug 5527437 umoogala:
4666 -- Added '= -99' condition to the following IF condition.
4667 --
4668 if( (l_inv_transfer_price IS NULL OR l_inv_transfer_price = -99) AND l_base_item <> 0 ) then
4669 -- call CTO API to get the transfer price
4670 --null;
4671 --
4672 -- Bug 5527437 umoogala: added following IF block
4673 --
4674 l_inv_transfer_price := 0;
4675 IF G_ORDER_LINE_ID = -1 OR G_ORDER_LINE_ID <> p_order_line_id
4676 THEN
4677 G_ORDER_LINE_ID := p_order_line_id;
4678 END IF;
4679
4680 print_debug('Calling CTO API to get the transfer price' , 'GET_TRANSFER_PRICE');
4681 print_debug('G_ORDER_LINE_ID'||G_ORDER_LINE_ID , 'GET_TRANSFER_PRICE');
4682 CTO_TRANSFER_PRICE_PK.CTO_TRANSFER_PRICE(
4683 p_config_item_id => p_inventory_item_id
4684 , p_selling_oper_unit => p_to_org_id
4685 , p_shipping_oper_unit => p_from_org_id
4686 , p_transaction_uom => p_transaction_uom
4687 , p_transaction_id => p_transaction_id
4688 , p_price_list_id => l_price_list_id
4689 , p_from_organization_id => l_organization_id
4690 , p_global_procurement_flag => p_global_procurement_flag
4691 , x_transfer_price => l_inv_transfer_price
4692 , x_currency_code => l_inv_currency_code
4693 , x_return_status => l_return_status
4694 , x_msg_count => l_msg_count
4695 , x_msg_data => l_msg_data
4696 );
4697
4698 if( l_return_status <> G_RET_STS_SUCCESS ) then
4699 print_debug('Error from CTO_Transfer_price', 'GET_TRANSFER_PRICE');
4700 raise FND_API.G_EXC_ERROR;
4701 end if;
4702 print_debug('l_transfer_price from CTO is ' || l_inv_transfer_price, 'GET_TRANSFER_PRICE');
4703 end if;
4704
4705 l_transfer_price := convert_currency(
4706 p_org_id => p_from_org_id
4707 , p_transfer_price => l_inv_transfer_price
4708 , p_currency_code => l_inv_currency_code
4709 , p_transaction_date => l_transaction_date
4710 , x_functional_currency_code => l_functional_currency_code
4711 , x_return_status => l_return_status
4712 , x_msg_data => x_msg_data
4713 , x_msg_count => x_msg_count
4714 );
4715
4716 if( l_return_status <> G_RET_STS_SUCCESS ) then
4717 print_debug('Error from convert_currency', 'get_transfer_price_for_item');
4718 raise FND_API.G_EXC_ERROR;
4719 end if;
4720 print_Debug('l_transfer_price = ' || l_transfer_price, 'get_transfer_price');
4721 print_Debug('x_currency_code = ' || l_functional_currency_code, 'get_transfer_price');
4722
4723 x_transfer_price := l_transfer_price;
4724 x_currency_code := l_functional_currency_code;
4725 x_incr_transfer_price := l_inv_transfer_price;
4726 x_incr_currency_code := l_inv_currency_code;
4727 x_return_status := G_RET_STS_SUCCESS;
4728 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4729 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4730 EXCEPTION
4731 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4732 x_return_status := G_RET_STS_UNEXP_ERROR;
4733 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4734 print_debug('In Exc_Unexpected_Error ' || l_progress, 'Get_Transfer_price');
4735 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4736
4737 WHEN FND_API.G_EXC_ERROR THEN
4738 x_return_status := G_RET_STS_ERROR;
4739 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4740 print_debug('In EXC_ERROR ' || l_progress, 'Get_Transfer_Price');
4741 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4742
4743
4744 WHEN OTHERS then
4745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4746 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
4747 x_msg_data := fnd_msg_pub.get(p_msg_index => x_msg_count, p_encoded => fnd_api.g_false);
4748
4749 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4750 FND_MSG_PUB.Add_Exc_Msg(G_PACKAGE_NAME, l_api_name);
4751 end if;
4752
4753 print_debug('in When Others, l_progress = ' || l_progress, 'Get_Transfer_price');
4754
4755 END get_transfer_price;
4756
4757 END INV_TRANSACTION_FLOW_PUB;