[Home] [Help]
PACKAGE BODY: APPS.INV_LOGICAL_TRANSACTIONS_PUB
Source
1 PACKAGE BODY INV_LOGICAL_TRANSACTIONS_PUB AS
2 /* $Header: INVLTPBB.pls 120.35.12020000.2 2012/07/09 08:09:02 asugandh ship $ */
3
4 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5
6 PROCEDURE print_debug(
7 p_err_msg IN VARCHAR2,
8 p_level IN NUMBER := 9
9 ) IS
10 BEGIN
11 INV_LOG_UTIL.Trace(p_message => p_err_msg,
12 p_module => 'INV_LOGICAL_TRANSACTIONS_PUB',
13 p_level => p_level);
14 END print_debug;
15
16
17 PROCEDURE GET_ACCT_PERIOD(
18 x_return_status OUT NOCOPY VARCHAR2
19 , x_msg_count OUT NOCOPY NUMBER
20 , x_msg_data OUT NOCOPY VARCHAR2
21 , x_acct_period_id OUT NOCOPY NUMBER
22 , p_organization_id IN NUMBER
23 , p_transaction_date IN DATE
24 )
25 IS
26 BEGIN
27 IF (l_debug = 1) THEN
28 print_debug('Enter get_acct_period', 9);
29 print_debug('p_organization_id = ' || p_organization_id, 9);
30 END IF;
31
32 /* Commented for bug 9691823 to have same logic same as that of BaseTransaction
33
34 SELECT acct_period_id
35 INTO x_acct_period_id
36 FROM org_acct_periods
37 WHERE period_close_date IS NULL
38 AND organization_id = p_organization_id
39 AND TRUNC(schedule_close_date) >=
40 TRUNC(NVL(p_transaction_date,sysdate))
41 AND TRUNC(PERIOD_START_DATE) <=
42 TRUNC(NVL(p_transaction_date,sysdate));
43 */
44
45 /* Added following statement for bug 9691823 */
46 SELECT acct_period_id
47 INTO x_acct_period_id
48 FROM org_acct_periods
49 WHERE period_close_date IS NULL
50 AND organization_id = p_organization_id
51 AND (schedule_close_date + 1) >
52 inv_le_timezone_pub.get_le_day_for_inv_org(nvl(p_transaction_date,SYSDATE),
53 p_organization_id)
54 AND period_start_date <=
55 inv_le_timezone_pub.get_le_day_for_inv_org(nvl(p_transaction_date,SYSDATE),
56 p_organization_id) ;
57
58 IF (l_debug = 1) THEN
59 print_debug('x_acct_period_id = ' || x_acct_period_id, 9);
60 END IF;
61
62 x_return_status := G_RET_STS_SUCCESS;
63 EXCEPTION
64 WHEN no_data_found THEN
65 x_return_status := G_RET_STS_ERROR;
66 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
67 IF (l_debug = 1) THEN
68 print_debug('GET_ACCT_PERIOD: no_data_found error', 9);
69 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
70 END IF;
71 WHEN OTHERS THEN
72 x_return_status := G_RET_STS_UNEXP_ERROR;
73
74 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
75 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_acct_period');
76 END IF;
77 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
78 IF (l_debug = 1) THEN
79 print_debug('GET_ACCT_PERIOD: others error', 9);
80 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
81 END IF;
82 END GET_ACCT_PERIOD;
83
84
85
86 PROCEDURE GET_COGS_ACCT_FOR_LOGICAL_SO(
87 x_return_status OUT NOCOPY VARCHAR2
88 , x_msg_count OUT NOCOPY NUMBER
89 , x_msg_data OUT NOCOPY VARCHAR2
90 , x_cogs_acct_id OUT NOCOPY NUMBER
91 , p_inventory_item_id IN NUMBER
92 , p_order_line_id IN NUMBER
93 , p_ic_to_inv_organization_id IN NUMBER DEFAULT NULL -- Bug: 4607049. Added parameter to get the Selling Inventory organization to pass to INV_WORKFLOW.
94 )
95 IS
96 l_dist_acct_id NUMBER := null;
97 l_order_header_id NUMBER := null;
98 l_order_type_id NUMBER := null;
99 l_customer_id NUMBER := null;
100 l_selling_org_id NUMBER := null;
101 l_flex_seg VARCHAR2(2000) := null;
102 l_error_msg VARCHAR2(2000);
103 l_success BOOLEAN := TRUE;
104 l_sob_id NUMBER := NULL;
105 l_coa_id NUMBER := NULL;
106 lreturn_status VARCHAR2(1);
107 lmsg_data VARCHAR2(100);
108 BEGIN
109 IF (l_debug = 1) THEN
110 print_debug('Enter get_cogs_acct_for_logical_so', 9);
111 print_debug('p_inventory_item_id = ' || p_inventory_item_id, 9);
112 print_debug('p_order_line_id = ' || p_order_line_id, 9);
113 print_debug('p_ic_to_inv_organization_id = ' || p_ic_to_inv_organization_id, 9);
114 END IF;
115
116 BEGIN
117 SELECT oel.header_id,
118 oel.org_id,
119 oel.sold_to_org_id,
120 oeh.order_type_id
121 INTO l_order_header_id,
122 l_selling_org_id,
123 l_customer_id,
124 l_order_type_id
125 FROM oe_order_headers_all oeh,
126 oe_order_lines_all oel
127 WHERE oel.line_id = p_order_line_id
128 AND oel.header_id = oeh.header_id;
129
130 IF (l_debug = 1) THEN
131 print_debug('header_id = ' || l_order_header_id, 9);
132 print_debug('selling_org_id = ' || l_selling_org_id, 9);
133 print_debug('customer_id = ' || l_customer_id, 9);
134 print_debug('order_type_id = ' || l_order_type_id, 9);
135 END IF;
136 EXCEPTION
137 WHEN no_data_found THEN
138 IF (l_debug = 1) THEN
139 print_debug('Cannot find so order line or OU for order line = '
140 || p_order_line_id, 9);
141 END IF;
142 RAISE FND_API.G_EXC_ERROR;
143 END;
144
145 /* commented the selection of COA using LE - OU link which is obsoleted in R12
146 and replaced the code with selection of COAs using the API - INV_GLOBALS.GET_LEDGER_INFO
147 Bug No - 4336479
148 begin
149 SELECT to_number(LEI.org_information1)
150 INTO l_sob_id
151 FROM hr_organization_information LEI
152 , hr_organization_information OUI
153 , hr_organization_units OU
154 , hr_organization_units LE
155 WHERE OU.organization_id = l_selling_org_id
156 AND LEI.org_information_context = 'Legal Entity Accounting'
157 AND to_char(LEI.organization_id) = OUI.org_information2
158 AND OUI.org_information_context = 'Operating Unit Information'
159 AND OUI.organization_id = OU.organization_id
160 AND LE.organization_id = LEI.organization_id;
161 EXCEPTION
162 WHEN no_data_found THEN
163 IF (l_debug = 1) THEN
164 -- print_debug('Cannot find set of books id for the selling OU = '
165 -- || l_selling_org_id, 9);
166 -- Modified the message text set of books to ledger for making
167 --the message compatible with LE uptake project
168 print_debug('Cannot find ledger id for the selling OU = '
169 || l_selling_org_id, 9);
170 END IF;
171 RAISE FND_API.G_EXC_ERROR;
172 END;
173
174 BEGIN
175 SELECT chart_of_accounts_id
176 INTO l_coa_id
177 FROM gl_sets_of_books
178 WHERE set_of_books_id = l_sob_id;
179 EXCEPTION
180 WHEN no_data_found THEN
181 IF (l_debug = 1) THEN
182 -- print_debug('Cannot find chart of accounts id for the SOB ID = '
183 -- || l_sob_id, 9);
184 -- Modified the message text set of books to ledger for making the message compatible with LE uptake project
185 print_debug('Cannot find chart of accounts id for the Ledger ID = '
186 || l_sob_id, 9);
187 END IF;
188 RAISE FND_API.G_EXC_ERROR;
189 END;
190 */
191
192 BEGIN
193 Inv_globals.get_ledger_info(
194 x_return_status => lreturn_status,
195 x_msg_data => lmsg_data ,
196 p_context_type => 'Operating Unit Information',
197 p_org_id => l_selling_org_id,
198 x_sob_id => l_sob_id,
199 x_coa_id => l_coa_id,
200 p_account_info_context => 'BOTH');
201 IF NVL(lreturn_status , 'S') = 'E' THEN
202 print_debug('Cannot find Ledger Information for Operating Unit = '||l_selling_org_id , 9);
203 RAISE FND_API.G_EXC_ERROR;
204 END IF;
205 END;
206
207 IF (l_debug = 1) THEN
208 print_debug('Calling INV_WORKFLOW.CALL_GENERATE_COGS', 9);
209 END IF;
210
211 l_success := INV_WORKFLOW.call_generate_cogs
212 (
213 c_fb_flex_num => l_coa_id,
214 c_IC_CUSTOMER_ID => l_customer_id
215 , c_ic_iTEM_ID => p_inventory_item_id
216 , c_IC_ORDER_HEADER_ID => l_order_header_id
217 , c_IC_ORDER_LINE_ID => p_order_line_id
218 , c_IC_ORDER_TYPE_ID => l_order_type_id
219 , c_IC_SELL_OPER_UNIT => l_selling_org_id
220 , c_V_CCID => x_cogs_acct_id
221 , c_FB_FLEX_SEG => l_flex_seg
222 , c_FB_ERROR_MSG => l_error_msg
223 , c_IC_TO_INV_ORGANIZATION_ID => p_ic_to_inv_organization_id); -- Bug: 4607049.
224
225 IF (l_success) THEN
226 IF (l_debug = 1) THEN
227 print_debug('l_success is TRUE', 9);
228 print_debug('x_cogs_acct_id = ' || x_cogs_acct_id, 9);
229 END IF;
230 ELSE
231 IF (l_debug = 1) THEN
232 print_debug('l_success = FALSE', 9);
233 print_debug('error msg : ' || l_error_msg, 9);
234 END IF;
235 RAISE FND_API.G_EXC_ERROR;
236 END IF;
237
238 x_return_status := G_RET_STS_SUCCESS;
239 EXCEPTION
240 WHEN FND_API.G_EXC_ERROR THEN
241 x_return_status := G_RET_STS_ERROR;
242 IF (l_debug = 1) THEN
243 print_debug('GET_COGS_FOR_LOGICAL_SO: Expected Error', 9);
244 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
245 END IF;
246 WHEN OTHERS THEN
247 x_return_status := G_RET_STS_UNEXP_ERROR;
248 IF (l_debug = 1) THEN
249 print_debug('GET_COGS_FOR_LOGICAL_SO: Others Error', 9);
250 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
251 END IF;
252 END GET_COGS_ACCT_FOR_LOGICAL_SO;
253
254 PROCEDURE GET_DEFAULT_COSTGROUP(
255 x_return_status OUT NOCOPY VARCHAR2
256 , x_msg_count OUT NOCOPY NUMBER
257 , x_msg_data OUT NOCOPY VARCHAR2
258 , x_cost_group_id OUT NOCOPY NUMBER
259 , p_organization_id IN NUMBER
260 )
261 IS
262 BEGIN
263 IF (l_debug = 1) THEN
264 print_debug('Enter get_default_costgroup', 9);
265 print_debug('p_organization_id = ' || p_organization_id, 9);
266 END IF;
267
268 SELECT default_cost_group_id
269 INTO x_cost_group_id
270 FROM mtl_parameters
271 WHERE organization_id = p_organization_id;
272
273 IF (l_debug = 1) THEN
274 print_debug('x_cost_group_id = ' || x_cost_group_id, 9);
275 END IF;
276
277 x_return_status := G_RET_STS_SUCCESS;
278 EXCEPTION
279 WHEN no_data_found THEN
280 x_return_status := G_RET_STS_ERROR;
281
282 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
283 IF (l_debug = 1) THEN
284 print_debug('GET_DEFAULT_COSTGROUP: No Data Found Error', 9);
285 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
286 END IF;
287 WHEN OTHERS THEN
288 x_return_status := G_RET_STS_UNEXP_ERROR;
289
290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
291 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_acct_period');
292 END IF;
293 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
294 IF (l_debug = 1) THEN
295 print_debug('GET_DEFAULT_COSTGROUP: Others Error', 9);
296 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
297 END IF;
298 END GET_DEFAULT_COSTGROUP;
299
300
301 PROCEDURE GET_PROJECT_COSTGROUP(
302 x_return_status OUT NOCOPY VARCHAR2
303 , x_msg_count OUT NOCOPY NUMBER
304 , x_msg_data OUT NOCOPY VARCHAR2
305 , x_cost_group_id OUT NOCOPY NUMBER
306 , p_project_id IN NUMBER
307 , p_organization_id IN NUMBER
308 )
309 IS
310 BEGIN
311 IF (l_debug = 1) THEN
312 print_debug('Enter get_project_costgroup', 9);
313 print_debug('p_project_id = ' || p_project_id, 9);
314 print_debug('p_organization_id = ' || p_organization_id, 9);
315 END IF;
316
317 SELECT costing_group_id
318 INTO x_cost_group_id
319 FROM mrp_project_parameters
320 WHERE project_id = p_project_id
321 and organization_id = p_organization_id;
322
323 IF (l_debug = 1) THEN
324 print_debug('x_cost_group_id = ' || x_cost_group_id, 9);
325 END IF;
326
327 x_return_status := G_RET_STS_SUCCESS;
328 EXCEPTION
329 WHEN no_data_found THEN
330 x_return_status := G_RET_STS_ERROR;
331 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
332 IF (l_debug = 1) THEN
333 print_debug('GET_PROJECT_COSTGROUP: No Data Found Error', 9);
334 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
335 END IF;
336 WHEN OTHERS THEN
337 x_return_status := G_RET_STS_UNEXP_ERROR;
338
339 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
340 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'get_acct_period');
341 END IF;
342 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
343 IF (l_debug = 1) THEN
344 print_debug('GET_PROJECT_COSTGROUP: Others Error', 9);
345 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
346 END IF;
347 END GET_PROJECT_COSTGROUP;
348
349
350 ------------------------------------------------------------------------------
351
352 PROCEDURE create_exp_req_rcpt_trx(x_return_status OUT nocopy VARCHAR2,
353 x_msg_data OUT nocopy VARCHAR2,
354 p_transaction_id IN NUMBER,
355 p_transaction_temp_id IN NUMBER)
356 IS
357 l_return_status varchar2(1);
358 l_msg_data VARCHAR2(240);
359 l_msg_count NUMBER;
360 l_account_period_id NUMBER;
361 l_organization_id NUMBER;
362 l_transaction_date DATE;
363 l_cost_group_id NUMBER;
364 l_project_id NUMBER;
365 l_requisition_line_id NUMBER;
366 l_expenditure_type VARCHAR2(240);
367 l_distribution_account_id NUMBER;
368 --changes for backport3990277
369 l_trx_src_id NUMBER;
370 x_ret_status VARCHAR2(5);
371 prj_ref_enabled NUMBER;
372 l_pm_cost_collected VARCHAR2(3);
373 l_expenditure_org NUMBER;
374 l_task_id NUMBER;
375
376 --
377 -- OPM INVCONV: umoogala 11-Jul-2006
378 -- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
379 --
380 l_process_discrete_xfer VARCHAR2(1);
381 l_transfer_price NUMBER;
382 l_curr_rate NUMBER;
383 l_precision NUMBER;
384 l_ext_precision NUMBER;
385 l_min_unit NUMBER;
386
387 l_parentorg_process_org VARCHAR2(1);
388 l_parentorg_currency fnd_currencies.currency_code%TYPE;
389
390 l_logicalorg_process_org VARCHAR2(1);
391 l_logicalorg_ou_id NUMBER;
392 l_logicalorg_currency fnd_currencies.currency_code%TYPE;
393
394 -- End Bug 5349860
395
396 BEGIN
397
398
399 --
400 -- OPM INVCONV: umoogala 11-Jul-2006
401 -- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
402 -- Getting process_enabled_flag's for orgs.
403 --
404 SELECT
405 mmtt.transfer_organization, mmtt.transaction_date,
406 mmtt.requisition_line_id,
407 parentorg.process_enabled_flag, logicalorg.process_enabled_flag,
408 codx.operating_unit, cod.currency_code,
409 mmtt.transfer_price
410 INTO
411 l_organization_id,l_transaction_date,
412 l_requisition_line_id,
413 l_parentorg_process_org, l_logicalorg_process_org,
414 l_logicalorg_ou_id, l_parentorg_currency,
415 l_transfer_price
416 FROM mtl_material_transactions_temp mmtt,
417 mtl_parameters parentorg, mtl_parameters logicalorg,
418 cst_organization_definitions cod, cst_organization_definitions codx
419 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
420 AND parentorg.organization_id = mmtt.organization_id
421 AND logicalorg.organization_id = mmtt.transfer_organization
422 AND cod.organization_id = mmtt.organization_id
423 AND codx.organization_id = mmtt.transfer_organization
424 ;
425
426 --
427 -- OPM INVCONV: umoogala 11-Jul-2006
428 -- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
429 --
430 IF l_parentorg_process_org <> l_logicalorg_process_org
431 THEN
432 l_process_discrete_xfer := 'Y';
433 ELSE
434 l_process_discrete_xfer := 'N';
435 END IF;
436
437 --changes for backport3990277
438 --check if Project ref enabled
439 SELECT NVL(project_reference_enabled,2)
440 INTO prj_ref_enabled
441 FROM mtl_parameters
442 WHERE organization_id = l_organization_id ;
443
444 SELECT REQUISITION_HEADER_ID
445 INTO l_trx_src_id
446 FROM PO_REQUISITION_LINES_ALL
447 WHERE requisition_line_id =l_requisition_line_id;
448
449 --bug 3357867 get the distribution account id from po api
450 -- get the expenditure type and org from po_distributions.
451 SELECT expenditure_type,expenditure_organization_id
452 INTO l_expenditure_type,l_expenditure_org
453 FROM po_req_distributions_all
454 WHERE requisition_line_id = l_requisition_line_id;
455
456 l_distribution_account_id:= PO_REQ_DIST_SV1.get_dist_account(l_requisition_line_id);
457
458 inv_project.Get_project_info_from_Req(
459 x_ret_status,
460 l_project_id,
461 l_task_id,
462 l_requisition_line_id);
463
464 IF (x_ret_status <> g_ret_sts_success) THEN
465 print_debug('Failed to get project id and task id');
466 x_return_status := x_ret_status;
467 RETURN;
468 END IF;
469
470
471 IF (l_project_id IS NOT NULL)THEN
472 l_pm_cost_collected :='N';
473 END IF;
474 --changes for backport3990277
475
476 GET_ACCT_PERIOD(
477 x_return_status =>l_return_status,
478 x_msg_count =>l_msg_count,
479 x_msg_data =>l_msg_data,
480 x_acct_period_id =>l_account_period_id,
481 p_organization_id =>l_organization_id,
482 p_transaction_date => l_transaction_date);
483
484 IF (l_return_status <> g_ret_sts_success) THEN
485 print_debug('Failed to get acct period id for org:'||l_organization_id ||' message '||l_msg_data);
486 x_return_status := l_return_status;
487 x_msg_data := l_msg_data;
488 RETURN;
489 END IF;
490
491 IF (l_project_id IS NULL OR prj_ref_enabled=2) THEN
492 get_default_costgroup(
493 x_return_status => l_return_status
494 , x_msg_count => l_msg_count
495 , x_msg_data => l_msg_data
496 , x_cost_group_id => l_cost_group_id
497 , p_organization_id => l_organization_id);
498 ELSE
499 get_project_costgroup(
500 x_return_status => l_return_status
501 , x_msg_count => l_msg_count
502 , x_msg_data => l_msg_data
503 , x_cost_group_id => l_cost_group_id
504 , p_project_id => l_project_id
505 , p_organization_id => l_organization_id);
506 END IF;
507
508 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
509 IF (l_debug = 1) THEN
510 print_debug('get_default_costgroup returns error', 9);
511 print_debug('l_msg_data = ' || l_msg_data, 9);
512 END IF;
513 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_COST_GROUP');
514 FND_MSG_PUB.ADD;
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518
519 /*Bug# 5027170. The column 'logical_transaction' is included in the following
520 INSERT statement because the record inserted corresponds to a logical
521 transaction*/
522
523 --
524 -- OPM INVCONV: umoogala 11-Jul-2006
525 -- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
526 -- Convert transfer price from shipping currency to receiving currency.
527 --
528 IF l_process_discrete_xfer = 'Y'
529 THEN
530 print_debug(': Now doing currency conversion from Currency: ' ||
531 l_parentorg_currency || ' to functional currency, if necessary');
532
533 l_curr_rate := INV_TRANSACTION_FLOW_PUB.convert_currency (
534 p_org_id => l_logicalorg_ou_id
535 , p_transfer_price => 1
536 , p_currency_code => l_parentorg_currency
537 , p_transaction_date => l_transaction_date
538 , x_functional_currency_code => l_logicalorg_currency
539 , x_return_status => l_return_status
540 , x_msg_data => l_msg_data
541 , x_msg_count => l_msg_count
542 );
543
544
545 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
546 IF (l_debug = 1) THEN
547 print_debug('INV_TRANSACTION_FLOW_PUB.convert_currency returns error', 9);
548 print_debug('l_msg_data = ' || l_msg_data, 9);
549 END IF;
550 x_return_status := l_return_status;
551 x_msg_data := l_msg_data;
552 RETURN;
553 END IF;
554 fnd_currency.get_info (currency_code => l_logicalorg_currency,
555 precision => l_precision,
556 ext_precision => l_ext_precision,
557 min_acct_unit => l_min_unit);
558
559 l_transfer_price := round(l_curr_rate * l_transfer_price, l_ext_precision);
560 END IF;
561
562 INSERT INTO mtl_material_transactions
563 (TRANSACTION_ID,
564 ORGANIZATION_ID,
565 INVENTORY_ITEM_ID,
566 REVISION,
567 SUBINVENTORY_CODE,
568 LOCATOR_ID,
569 TRANSACTION_TYPE_ID,
570 TRANSACTION_ACTION_ID,
571 TRANSACTION_SOURCE_TYPE_ID,
572 TRANSACTION_SOURCE_ID,
573 TRANSACTION_SOURCE_NAME,
574 TRANSACTION_QUANTITY,
575 TRANSACTION_UOM,
576 PRIMARY_QUANTITY,
577 TRANSACTION_DATE,
578 ACCT_PERIOD_ID,
579 DISTRIBUTION_ACCOUNT_ID,
580 COSTED_FLAG,
581 ACTUAL_COST,
582 INVOICED_FLAG,
583 TRANSACTION_COST,
584 CURRENCY_CODE,
585 CURRENCY_CONVERSION_RATE,
586 CURRENCY_CONVERSION_TYPE,
587 CURRENCY_CONVERSION_DATE,
588 PM_COST_COLLECTED,
589 TRX_SOURCE_LINE_ID,
590 SOURCE_CODE,
591 SOURCE_LINE_ID,
592 TRANSFER_ORGANIZATION_ID,
593 TRANSFER_SUBINVENTORY,
594 TRANSFER_LOCATOR_ID,
595 COST_GROUP_ID,
596 TRANSFER_COST_GROUP_ID,
597 PROJECT_ID,
598 TASK_ID,
599 TO_PROJECT_ID,
600 TO_TASK_ID,
601 SHIP_TO_LOCATION_ID,
602 TRANSACTION_MODE,
603 TRANSACTION_BATCH_ID,
604 TRANSACTION_BATCH_SEQ,
605 LPN_ID,
606 parent_transaction_id,
607 last_update_date,
608 last_updated_by,
609 creation_date,
610 created_by,
611 transaction_set_id,
612 expenditure_type,
613 PA_EXPENDITURE_ORG_ID,
614 logical_transaction,
615 --
616 -- OPM INVCONV: umoogala 11-Jul-2006
617 -- Added transfer_price, and opm_costed_flag columns
618 --
619 transfer_price,
620 opm_costed_flag,
621 SHIPMENT_NUMBER) /* Bug 6411640.Shipment Number was not inserted for
622 Logical receipt */
623 SELECT
624 mmt.transfer_transaction_id,
625 mmt.TRANSFER_ORGANIZATION_ID,
626 mmt.INVENTORY_ITEM_ID,
627 mmt.REVISION,
628 mmt.TRANSFER_SUBINVENTORY,
629 mmt.TRANSFER_LOCATOR_ID,
630 g_type_logl_exp_req_receipt,
631 G_ACTION_LOGICALEXPREQRECEIPT,
632 g_sourcetype_intreq,
633 l_trx_src_id,
634 null,
635 Abs(mmt.transaction_quantity),
636 mmt.TRANSACTION_UOM,
637 Abs(mmt.primary_quantity),
638 mmt.TRANSACTION_DATE,
639 l_account_period_id,
640 l_distribution_account_id,
641 --
642 -- OPM INVCONV: umoogala 11-Jul-2006
643 -- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
644 -- If this logical txn org is process org, then set costed_flag to NULL.
645 --
646 decode(l_logicalorg_process_org, 'Y', NULL, 'N'), /* OPMCONV ANTHIYAG Bug#5510484 06-Sep-2006 */
647 mmt.ACTUAL_COST,
648 mmt.INVOICED_FLAG,
649 mmt.TRANSACTION_COST,
650 mmt.CURRENCY_CODE,
651 mmt.CURRENCY_CONVERSION_RATE,
652 mmt.CURRENCY_CONVERSION_TYPE,
653 mmt.CURRENCY_CONVERSION_DATE,
654 l_pm_cost_collected,--added pm_cost_collected flag
655 l_requisition_line_id,
656 mmt.SOURCE_CODE,
657 mmt.SOURCE_LINE_ID,
658 mmt.ORGANIZATION_ID,
659 mmt.SUBINVENTORY_CODE,
660 mmt.LOCATOR_ID,
661 l_cost_group_id,
662 mmt.TRANSFER_COST_GROUP_ID,
663 l_project_id,
664 l_task_id,
665 mmt.TO_PROJECT_ID,
666 mmt.TO_TASK_ID,
667 mmt.SHIP_TO_LOCATION_ID,
668 mmt.TRANSACTION_MODE,
669 mmt.TRANSACTION_BATCH_ID,
670 mmt.TRANSACTION_BATCH_SEQ,
671 mmt.LPN_ID,
672 mmt.transaction_id,
673 mmt.last_update_date,
674 mmt.last_updated_by,
675 mmt.creation_date,
676 mmt.created_by,
677 mmt.transaction_set_id,
678 l_expenditure_type,
679 l_expenditure_org,
680 1,
681 --
682 -- OPM INVCONV: umoogala 11-Jul-2006
683 -- Bug 5349860: Process/Discrete Internal Order Xfer to Exp Destination
684 -- Added transfer_price and opm_costed_flag
685 --
686 l_transfer_price,
687 DECODE(l_logicalorg_process_org, 'Y', 'N', NULL), -- opm_costed_flag
688 MMT.SHIPMENT_NUMBER -- Bug 6411640
689 FROM
690 mtl_material_transactions mmt,
691 fnd_currencies curr -- Bug 5349860: OPM INVCONV: umoogala 11-Jul-2006
692 WHERE mmt.transaction_id = p_transaction_id
693 AND curr.currency_code(+) = mmt.currency_code;
694
695 x_return_status := g_ret_sts_success;
696 IF (l_debug = 1) THEN
697 print_debug('create_exp_req_rcpt_trx: AFter mmt insert', 9);
698 END IF;
699
700 EXCEPTION
701 WHEN no_data_found THEN
702 x_return_status := G_RET_STS_ERROR;
703 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => x_msg_data);
704 IF (l_debug = 1) THEN
705 print_debug('create_exp_req_rcpt_trx: no_data_found error', 9);
706 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
707 END IF;
708 WHEN OTHERS THEN
709 x_return_status := G_RET_STS_UNEXP_ERROR;
710
711 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
712 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'create_logical_exp_req_rcpt_trx');
713 END IF;
714 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => x_msg_data);
715 IF (l_debug = 1) THEN
716 print_debug('create_exp_req_rcpt_trx: others error', 9);
717 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
718 END IF;
719 END create_exp_req_rcpt_trx;
720
721 -----------------------------------------------------------------------------
722
723
724
725 /*==========================================================================*
726 | Procedure : CREATE_LOGICAL_TRX_WRAPPER |
727 | |
728 | Description : This API is a wrapper that would be called from TM to |
729 | create logical transactions. This API has the input |
730 | parameter of transaction id of the inserted SO issue MMT |
731 | record, check if the selling OU is not the same as the |
732 | shipping OU, the transaction flow exists and new |
733 | transaction flow is checked, then it creates a record of |
734 | mtl_trx_rec_type and table of mtl_trx_tbl_type and then |
735 | calls the create_logical_transactions. This API is mainly |
736 | called from the INV java TM. |
737 | |
738 | Input Parameters : |
739 | p_api_version_number - API version number |
740 | p_init_msg_lst - Whether initialize the error message list or not|
741 | Should be fnd_api.g_false or fnd_api.g_true |
742 | p_transaction_id - transaction id of the inserted SO issue MMT |
743 | record. |
744 | p_transaction_temp_id - mmtt transaction temp id, only will be passed |
745 | from the inventory transaction manager for internal order intransit |
746 | issue transactions, where the destination type is EXPENSE |
747 | Output Parameters : |
748 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
749 | fnd_api.g_ret_sts_exc_error, if an expected |
750 | error occurred |
751 | fnd_api.g_ret_sts_unexp_error, if an unexpected |
752 | eror occurred |
753 | x_msg_count - Number of error message in the error message |
754 | list |
755 | x_msg_data - If the number of error message in the error |
756 | message list is one, the error message is in |
757 | this output parameter |
758 *==========================================================================*/
759 PROCEDURE create_logical_trx_wrapper(
760 x_return_status OUT NOCOPY VARCHAR2
761 , x_msg_count OUT NOCOPY NUMBER
762 , x_msg_data OUT NOCOPY VARCHAR2
763 , p_api_version_number IN NUMBER := 1.0
764 , p_init_msg_lst IN VARCHAR2 := G_FALSE
765 , p_transaction_id IN NUMBER
766 , p_transaction_temp_id IN NUMBER := NULL
767 )
768 IS
769 l_api_version_number CONSTANT NUMBER := 1.0;
770 l_in_api_version_number NUMBER := NVL(p_api_version_number, 1.0);
771 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_LOGICAL_TRX_WRAPPER';
772 l_init_msg_lst VARCHAR2(1) := NVL(p_init_msg_lst, G_FALSE);
773 l_progress NUMBER;
774 l_mtl_trx_tbl INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_tbl_type;
775 l_qualifier_code_tbl INV_TRANSACTION_FLOW_PUB.number_tbl;
776 l_qualifier_value_tbl INV_TRANSACTION_FLOW_PUB.number_tbl;
777 l_selling_OU NUMBER;
778 l_shipping_OU NUMBER;
779 l_ship_from_org_id NUMBER;
780 l_return_status VARCHAR2(1);
781 l_msg_count NUMBER;
782 l_msg_data VARCHAR2(2000);
783 l_header_id NUMBER;
784 l_transaction_date DATE;
785 l_new_accounting_flag VARCHAR2(1) := 'N';
786 l_transaction_flow_exists VARCHAR2(1) := INV_TRANSACTION_FLOW_PUB.G_TRANSACTION_FLOW_NOT_FOUND;
787 l_organization_id NUMBER;
788 l_item_id NUMBER;
789 l_transaction_source_type_id NUMBER;
790 l_transaction_action_id NUMBER;
791 l_logical_trx_type_code NUMBER;
792 l_defer_logical_trx NUMBER;
793 l_defer_logical_trx_flag NUMBER;
794 BEGIN
795 IF (l_debug = 1) THEN
796 print_debug('Enter create_logical_trx_wrapper', 9);
797 print_debug('p_api_version_number = ' || p_api_version_number, 9);
798 print_debug('l_in_api_version_number = ' || l_in_api_version_number, 9);
799 print_debug('p_init_msg_lst = ' || p_init_msg_lst, 9);
800 print_debug('l_init_msg_lst = ' || l_init_msg_lst, 9);
801 print_debug('p_transaction_id = ' || p_transaction_id, 9);
802 print_debug('p_transaction_temp_id = ' || p_transaction_temp_id, 9);
803 END IF;
804
805 -- Standard call to check for call compatibility
806 IF NOT fnd_api.compatible_api_call(l_api_version_number,
807 l_in_api_version_number, l_api_name, g_pkg_name) THEN
808 RAISE fnd_api.g_exc_unexpected_error;
809 END IF;
810
811 -- Initialize message list.
812 IF fnd_api.to_boolean(l_init_msg_lst) THEN
813 fnd_msg_pub.initialize;
814 END IF;
815
816
817 -- Determine if it's sales order shipment transaction or Logical PO receipt transaction
818 -- If it's logical PO receipt transaction, then receiving should already check the
819 -- transaction flow exists and get the transaction flow header_id, therefore we don't
820 -- need to check transaction flow for logical PO receipt
821 SELECT transaction_source_type_id, transaction_action_id
822 INTO l_transaction_source_type_id, l_transaction_action_id
823 FROM mtl_material_transactions
824 WHERE transaction_id = p_transaction_id;
825
826 IF (l_debug = 1) THEN
827 print_debug('transaction_source_type_id = ' || l_transaction_source_type_id, 9);
828 print_debug('transaction_action_id = ' || l_transaction_action_id, 9);
829 END IF;
830
831 --Internal Order Intransit Issue Transaction. Need to create a
832 --costing record if the destination is EXPENSE
833 IF ((l_transaction_source_type_id = g_sourcetype_intorder)
834 AND (l_transaction_action_id = g_action_issue)
835 AND (p_transaction_temp_id IS NOT NULL))THEN
836
837 create_exp_req_rcpt_trx(x_return_status =>l_return_status,
838 x_msg_data => l_msg_data,
839 p_transaction_id => p_transaction_id,
840 p_transaction_temp_id => p_transaction_temp_id);
841 IF (l_debug = 1) THEN
842 print_debug('AFter calling create_exp_req_rcpt_trx', 9);
843 END IF;
844
845 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
846 IF (l_debug = 1) THEN
847 print_debug('create_exp_req_rcpt_trx returns error', 9);
848 END IF;
849 RAISE FND_API.G_EXC_ERROR;
850 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
851 IF (l_debug = 1) THEN
852 print_debug('create_exp_req_rcpt_trx returns unexpected error', 9);
853 END IF;
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855 END IF;
856 IF (l_debug = 1) THEN
857 print_debug('create_exp_req_rcpt_trx returns success', 9);
858 END IF;
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860
861 ELSE
862
863 IF ((l_transaction_source_type_id = G_SOURCETYPE_SALESORDER and
864 l_transaction_action_id = G_ACTION_ISSUE) or
865 (l_transaction_source_type_id = G_SOURCETYPE_RMA and
866 (l_transaction_action_id = G_ACTION_RECEIPT
867 OR l_transaction_action_id = G_ACTION_ISSUE))) THEN
868
869 IF (l_debug = 1) THEN
870 print_debug('This is sales order issue/ RMA', 9);
871 END IF;
872
873 -- Get the selling OU and shipping OU of the sales order issue transaction
874 BEGIN
875 l_progress := 10;
876 IF (l_debug = 1) THEN
877 print_debug('Getting the selling OU and shipping OU of the SO issue', 9);
878 END IF;
879
880 SELECT oola.org_id,
881 to_number(hoi.org_information3),
882 oola.ship_from_org_id,
883 mmt.organization_id,
884 mmt.inventory_item_id,
885 mmt.transaction_date
886 INTO l_selling_OU,
887 l_shipping_OU,
888 l_ship_from_org_id,
889 l_organization_id,
890 l_item_id,
891 l_transaction_date
892 FROM hr_organization_information hoi,
893 oe_order_lines_all oola,
894 mtl_material_transactions mmt
895 WHERE mmt.transaction_id = p_transaction_id
896 AND mmt.trx_source_line_id = oola.line_id
897 AND oola.ship_from_org_id = hoi.organization_id
898 AND hoi.org_information_context = 'Accounting Information';
899
900 l_progress := 20;
901
902 IF (l_debug = 1) THEN
903 print_debug('create_logical_trx_wrapper: Selling OU = ' || l_selling_OU
904 || ' Shipping OU = ' || l_shipping_OU
905 || ' ship_from_org_id = ' || l_ship_from_org_id, 9);
906 END IF;
907 EXCEPTION
908 WHEN no_data_found THEN
909 IF (l_debug = 1) THEN
910 print_debug('Cannot find the selling and shipping OU of the sales order', 9);
911 END IF;
912 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OU');
913 FND_MSG_PUB.ADD;
914 RAISE FND_API.G_EXC_ERROR;
915 END;
916
917 -- check if the selling OU is not the same as the shipping OU, the
918 -- transaction flow exists and new transaction flow is checked, then
919 -- creates table of mtl_trx_tbl_type
920 -- and then calls the create_logical_transactions.
921 IF (l_selling_OU <> l_shipping_OU) THEN
922 l_progress := 30;
923 IF (l_debug = 1) THEN
924 print_debug('selling OU <> shipping OU', 9);
925 print_debug('Getting category_id', 9);
926 END IF;
927
928 -- get if there is any Category id of the item with the category set id = 1(Inventory)
929 BEGIN
930 SELECT category_id
931 INTO l_qualifier_value_tbl(1)
932 FROM mtl_item_categories
933 WHERE category_set_id = 1
934 AND organization_id = l_organization_id
935 AND inventory_item_id = l_item_id;
936
937 IF (l_qualifier_value_tbl(1) IS NOT NULL) THEN
938 IF (l_debug = 1) THEN
939 print_debug('l_qualifier_value_tbl(1) = ' || l_qualifier_value_tbl(1), 9);
940 END IF;
941 l_qualifier_code_tbl(1) := 1;
942 END IF;
943 EXCEPTION
944 WHEN no_data_found THEN
945 IF (l_debug = 1) THEN
946 print_debug('no category_id is found for the item id = ' || l_item_id, 9);
947 END IF;
948 END;
949
950 IF (l_debug = 1) THEN
951 print_debug('Calling INV_TRANSACTION_FLOW_PUB.check_transaction_flow', 9);
952 print_debug('l_shipping_OU = ' || l_shipping_OU, 9);
953 print_debug('l_selling_OU = ' || l_selling_OU, 9);
954 print_debug('flow_type = ' || G_SHIPPING, 9);
955 print_debug('organization_id = ' || l_ship_from_org_id, 9);
956 IF (l_qualifier_code_tbl.COUNT > 0) THEN
957 print_debug('l_qualifier_code_tbl(1) = ' || l_qualifier_code_tbl(1), 9);
958 END IF;
959
960 IF (l_qualifier_code_tbl.COUNT > 0) THEN
961 print_debug('l_qualifier_value_tbl(1) = ' || l_qualifier_value_tbl(1), 9);
962 END IF;
963 END IF;
964
965 INV_TRANSACTION_FLOW_PUB.check_transaction_flow(
966 p_api_version => 1.0
967 , p_init_msg_list => fnd_api.g_false
968 , p_start_operating_unit => l_shipping_OU
969 , p_end_operating_unit => l_selling_OU
970 , p_flow_type => G_SHIPPING
971 , p_organization_id => l_ship_from_org_id
972 , p_qualifier_code_tbl => l_qualifier_code_tbl
973 , p_qualifier_value_tbl => l_qualifier_value_tbl
974 , p_transaction_date => l_transaction_date
975 , x_return_status => l_return_status
976 , x_msg_count => l_msg_count
977 , x_msg_data => l_msg_data
978 , x_header_id => l_header_id
979 , x_new_accounting_flag => l_new_accounting_flag
980 , x_transaction_flow_exists => l_transaction_flow_exists);
981
982 l_progress := 40;
983 IF (l_debug = 1) THEN
984 print_debug('Output from the API: l_header_id = ' || l_header_id, 9);
985 print_debug('l_new_accounting_flag = ' || l_new_accounting_flag, 9);
986 print_debug('l_transaction_flow_exists = ' || l_transaction_flow_exists, 9);
987 END IF;
988
989 IF (l_return_status = G_RET_STS_ERROR) THEN
990 IF (l_debug = 1) THEN
991 print_debug('Check trx flow returns error: ' || l_msg_data, 9);
992 END IF;
993 RAISE FND_API.G_EXC_ERROR;
994 ELSIF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
995 IF (l_debug = 1) THEN
996 print_debug('Check trx flow returns unexpected error: ' || l_msg_data, 9);
997 END IF;
998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
999 END IF;
1000
1001 IF (l_debug = 1) THEN
1002 print_debug('Check trx flow returns success', 9);
1003 END IF;
1004
1005 l_logical_trx_type_code := G_LOGTRXCODE_RMASOISSUE;
1006 l_defer_logical_trx := G_DEFER_LOGICAL_TRX_ORG_LEVEL;
1007 ELSE -- selling OU = shipping OU, no logical transactions
1008 IF (l_debug = 1) THEN
1009 print_debug('l_selling_OU = l_shipping_OU, do not create logical transactions', 9);
1010 END IF;
1011 --deferred cogs change
1012 --since selling ou is the same as shipping ou
1013 -- update the physical sales order with so_issue_acct_type as
1014 -- 2 deferred cogs
1015 BEGIN
1016 UPDATE mtl_material_transactions
1017 SET so_issue_account_type =2
1018 WHERE transaction_id = p_transaction_id;
1019 EXCEPTION
1020 WHEN no_data_found THEN
1021 IF (l_debug = 1) THEN
1022 print_debug('No MMT found while updating so_issue_acct_type' || p_transaction_id, 9);
1023 x_return_status:= FND_API.g_ret_sts_error;
1024 END IF;
1025 END;
1026 x_return_status := FND_API.G_RET_STS_SUCCESS;
1027 return;
1028 END IF; -- end of (l_selling_OU <> l_shipping_OU)
1029 l_progress := 50;
1030 --Bug 5103108: Added this condition so that other transaction
1031 -- source types doesnt get into the default logic
1032 ELSIF (l_transaction_source_type_id =
1033 inv_globals.G_SOURCETYPE_PURCHASEORDER AND
1034 (l_transaction_action_id = G_ACTION_LOGICALRECEIPT OR
1035 l_transaction_action_id = G_ACTION_LOGICALDELADJ)) THEN
1036 -- it's a Logical PO receipt
1037 IF (l_debug = 1) THEN
1038 print_debug('This is logical PO receipt', 9);
1039 END IF;
1040
1041 l_logical_trx_type_code := G_LOGTRXCODE_DSDELIVER;
1042 l_transaction_flow_exists := INV_TRANSACTION_FLOW_PUB.G_TRANSACTION_FLOW_FOUND;
1043 l_new_accounting_flag := 'Y';
1044 l_defer_logical_trx := G_NOT_DEFER_LOGICAL_TRX;
1045 END IF; -- end of transaction source type is sales order issue/RMA
1046
1047 IF (l_transaction_flow_exists = INV_TRANSACTION_FLOW_PUB.G_TRANSACTION_FLOW_FOUND
1048 AND l_new_accounting_flag = 'Y') THEN
1049 l_progress := 60;
1050 BEGIN
1051
1052 --The check for the installation of Costing J and OM J will
1053 -- be done here if the new accoutning flag is set to Yes.
1054 --
1055
1056 -- check the patchset level of Costing and OM, returns error
1057 -- if they are not in patchset J
1058 IF (l_debug = 1) THEN
1059 print_debug('Costing Current Release Level = ' || CST_VersionCtrl_GRP.Get_Current_Release_Level, 9);
1060 print_debug('Costing J Release Level = ' || CST_Release_GRP.G_J_Release_Level, 9);
1061 print_debug('OM Current Release Level = ' || OE_CODE_CONTROL.Get_Code_Release_Level, 9);
1062 END IF;
1063
1064 --do as before
1065 IF (CST_VersionCtrl_GRP.Get_Current_Release_Level < CST_Release_GRP.G_J_Release_Level) THEN
1066 IF (l_debug = 1) THEN
1067 print_debug('Costing Release Level < Costing J Release Level', 9);
1068 END IF;
1069 FND_MESSAGE.SET_NAME('INV', 'INV_CST_JRELEASE');
1070 FND_MSG_PUB.ADD;
1071 RAISE FND_API.G_EXC_ERROR;
1072 ELSIF (OE_CODE_CONTROL.Get_Code_Release_Level < '110510') THEN
1073 IF (l_debug = 1) THEN
1074 print_debug('OM Release Level < 110510', 9);
1075 END IF;
1076 FND_MESSAGE.SET_NAME('INV', 'INV_OM_JRELEASE');
1077 FND_MSG_PUB.ADD;
1078 RAISE FND_API.G_EXC_ERROR;
1079 END IF;
1080
1081 IF (l_debug = 1) THEN
1082 print_debug('l_transaction_flow_exists=1 AND l_new_accounting_flag=Y', 9);
1083 print_debug('before construct the transaction pl/sql table', 9);
1084 END IF;
1085 -- construct the pl/sql table
1086 SELECT TRANSACTION_ID,
1087 ORGANIZATION_ID,
1088 INVENTORY_ITEM_ID,
1089 REVISION,
1090 SUBINVENTORY_CODE,
1091 LOCATOR_ID,
1092 TRANSACTION_TYPE_ID,
1093 TRANSACTION_ACTION_ID,
1094 TRANSACTION_SOURCE_TYPE_ID,
1095 TRANSACTION_SOURCE_ID,
1096 TRANSACTION_SOURCE_NAME,
1097 TRANSACTION_QUANTITY,
1098 TRANSACTION_UOM,
1099 PRIMARY_QUANTITY,
1100 TRANSACTION_DATE,
1101 ACCT_PERIOD_ID,
1102 DISTRIBUTION_ACCOUNT_ID,
1103 COSTED_FLAG,
1104 ACTUAL_COST,
1105 INVOICED_FLAG,
1106 TRANSACTION_COST,
1107 CURRENCY_CODE,
1108 CURRENCY_CONVERSION_RATE,
1109 CURRENCY_CONVERSION_TYPE,
1110 CURRENCY_CONVERSION_DATE,
1111 PM_COST_COLLECTED,
1112 TRX_SOURCE_LINE_ID,
1113 SOURCE_CODE,
1114 RCV_TRANSACTION_ID,
1115 SOURCE_LINE_ID,
1116 TRANSFER_ORGANIZATION_ID,
1117 TRANSFER_SUBINVENTORY,
1118 TRANSFER_LOCATOR_ID,
1119 COST_GROUP_ID,
1120 TRANSFER_COST_GROUP_ID,
1121 PROJECT_ID,
1122 TASK_ID,
1123 TO_PROJECT_ID,
1124 TO_TASK_ID,
1125 SHIP_TO_LOCATION_ID,
1126 TRANSACTION_MODE,
1127 TRANSACTION_BATCH_ID,
1128 TRANSACTION_BATCH_SEQ,
1129 TRX_FLOW_HEADER_ID,
1130 INTERCOMPANY_COST,
1131 INTERCOMPANY_CURRENCY_CODE,
1132 INTERCOMPANY_PRICING_OPTION,
1133 LPN_ID,
1134 PARENT_TRANSACTION_ID,
1135 LOGICAL_TRANSACTIONS_CREATED
1136 INTO l_mtl_trx_tbl(1).TRANSACTION_ID,
1137 l_mtl_trx_tbl(1).ORGANIZATION_ID,
1138 l_mtl_trx_tbl(1).INVENTORY_ITEM_ID,
1139 l_mtl_trx_tbl(1).REVISION,
1140 l_mtl_trx_tbl(1).SUBINVENTORY_CODE,
1141 l_mtl_trx_tbl(1).LOCATOR_ID,
1142 l_mtl_trx_tbl(1).TRANSACTION_TYPE_ID,
1143 l_mtl_trx_tbl(1).TRANSACTION_ACTION_ID,
1144 l_mtl_trx_tbl(1).TRANSACTION_SOURCE_TYPE_ID,
1145 l_mtl_trx_tbl(1).TRANSACTION_SOURCE_ID,
1146 l_mtl_trx_tbl(1).TRANSACTION_SOURCE_NAME,
1147 l_mtl_trx_tbl(1).TRANSACTION_QUANTITY,
1148 l_mtl_trx_tbl(1).TRANSACTION_UOM,
1149 l_mtl_trx_tbl(1).PRIMARY_QUANTITY,
1150 l_mtl_trx_tbl(1).TRANSACTION_DATE,
1151 l_mtl_trx_tbl(1).ACCT_PERIOD_ID,
1152 l_mtl_trx_tbl(1).DISTRIBUTION_ACCOUNT_ID,
1153 l_mtl_trx_tbl(1).COSTED_FLAG,
1154 l_mtl_trx_tbl(1).ACTUAL_COST,
1155 l_mtl_trx_tbl(1).INVOICED_FLAG,
1156 l_mtl_trx_tbl(1).TRANSACTION_COST,
1157 l_mtl_trx_tbl(1).CURRENCY_CODE,
1158 l_mtl_trx_tbl(1).CURRENCY_CONVERSION_RATE,
1159 l_mtl_trx_tbl(1).CURRENCY_CONVERSION_TYPE,
1160 l_mtl_trx_tbl(1).CURRENCY_CONVERSION_DATE,
1161 l_mtl_trx_tbl(1).PM_COST_COLLECTED,
1162 l_mtl_trx_tbl(1).TRX_SOURCE_LINE_ID,
1163 l_mtl_trx_tbl(1).SOURCE_CODE,
1164 l_mtl_trx_tbl(1).RCV_TRANSACTION_ID,
1165 l_mtl_trx_tbl(1).SOURCE_LINE_ID,
1166 l_mtl_trx_tbl(1).TRANSFER_ORGANIZATION_ID,
1167 l_mtl_trx_tbl(1).TRANSFER_SUBINVENTORY,
1168 l_mtl_trx_tbl(1).TRANSFER_LOCATOR_ID,
1169 l_mtl_trx_tbl(1).COST_GROUP_ID,
1170 l_mtl_trx_tbl(1).TRANSFER_COST_GROUP_ID,
1171 l_mtl_trx_tbl(1).PROJECT_ID,
1172 l_mtl_trx_tbl(1).TASK_ID,
1173 l_mtl_trx_tbl(1).TO_PROJECT_ID,
1174 l_mtl_trx_tbl(1).TO_TASK_ID,
1175 l_mtl_trx_tbl(1).SHIP_TO_LOCATION_ID,
1176 l_mtl_trx_tbl(1).TRANSACTION_MODE,
1177 l_mtl_trx_tbl(1).TRANSACTION_BATCH_ID,
1178 l_mtl_trx_tbl(1).TRANSACTION_BATCH_SEQ,
1179 l_mtl_trx_tbl(1).TRX_FLOW_HEADER_ID,
1180 l_mtl_trx_tbl(1).INTERCOMPANY_COST,
1181 l_mtl_trx_tbl(1).INTERCOMPANY_CURRENCY_CODE,
1182 l_mtl_trx_tbl(1).INTERCOMPANY_PRICING_OPTION,
1183 l_mtl_trx_tbl(1).LPN_ID,
1184 l_mtl_trx_tbl(1).parent_transaction_id,
1185 l_defer_logical_trx_flag
1186 FROM mtl_material_transactions
1187 WHERE transaction_id = p_transaction_id;
1188
1189 IF (l_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE) THEN
1190 l_mtl_trx_tbl(1).trx_flow_header_id := l_header_id;
1191 END IF;
1192
1193 l_progress := 70;
1194 IF (l_debug = 1) THEN
1195 print_debug('after construct the pl/sql table with transaction_id = '
1196 || p_transaction_id, 9);
1197 END IF;
1198 EXCEPTION
1199 WHEN no_data_found THEN
1200 IF (l_debug = 1) THEN
1201 print_debug('Error when creating logical trx table', 9);
1202 END IF;
1203 FND_MESSAGE.SET_NAME('INV', 'INV_LOG_TRX_REC_ERROR');
1204 FND_MSG_PUB.ADD;
1205 RAISE FND_API.G_EXC_ERROR ;
1206 END;
1207
1208 --bug fix - the defer flag should be set only if it is being
1209 -- called by the concurrent program which will set the value of
1210 -- the flag to N before calling the wrapper. Other cases,
1211 -- it will get either the org. default or for PO will be set to No.
1212 IF (l_defer_logical_trx_flag = 1) THEN
1213 l_defer_logical_trx := G_NOT_DEFER_LOGICAL_TRX;
1214 END IF;
1215
1216
1217 l_progress := 80;
1218 IF (l_debug = 1) THEN
1219 print_debug('Before calling create_logical_transactions', 9);
1220 END IF;
1221
1222 create_logical_transactions(
1223 x_return_status => l_return_status
1224 , x_msg_count => l_msg_count
1225 , x_msg_data => l_msg_data
1226 , p_api_version_number => 1.0
1227 , p_init_msg_lst => G_FALSE
1228 , p_mtl_trx_tbl => l_mtl_trx_tbl
1229 , p_validation_flag => G_TRUE
1230 , p_trx_flow_header_id => l_mtl_trx_tbl(1).trx_flow_header_id
1231 , p_defer_logical_transactions => l_defer_logical_trx
1232 , p_logical_trx_type_code => l_logical_trx_type_code
1233 , p_exploded_flag => G_NOT_EXPLODED);
1234
1235 l_progress := 90;
1236 IF (l_debug = 1) THEN
1237 print_debug('AFter calling create_logical_transactions', 9);
1238 END IF;
1239
1240 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1241 IF (l_debug = 1) THEN
1242 print_debug('create_logical_transactions returns error', 9);
1243 END IF;
1244 RAISE FND_API.G_EXC_ERROR;
1245 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1246 IF (l_debug = 1) THEN
1247 print_debug('create_logical_transactions returns unexpected error', 9);
1248 END IF;
1249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1250 END IF;
1251 IF (l_debug = 1) THEN
1252 print_debug('create_logical_transactions returns success', 9);
1253 END IF;
1254 --deferred cogs change
1255 --when old acct than stamp cogs for multiple OU's.
1256 UPDATE mtl_material_transactions
1257 SET so_issue_account_type = 2--defcogs
1258 WHERE transaction_id = p_transaction_id;
1259 IF (SQL%ROWCOUNT = 0) THEN
1260 IF (l_debug = 1) THEN
1261 print_debug('No MMT record is found for defcogsupdate with trx id:'
1262 || p_transaction_id ,9);
1263 END IF;
1264 FND_MESSAGE.SET_NAME('INV', 'INV_MMT_NOT_FOUND');
1265 FND_MSG_PUB.ADD;
1266 RAISE FND_API.G_EXC_ERROR;
1267 END IF;
1268 ELSIF (l_transaction_flow_exists = INV_TRANSACTION_FLOW_PUB.G_TRANSACTION_FLOW_FOUND
1269 AND l_new_accounting_flag = 'N') THEN
1270 IF (l_debug = 1) THEN
1271 print_debug('updating MMT record trx_id ' || p_transaction_id ||
1272 ' with trx_flow_header_id = ' || l_header_id, 9);
1273 END IF;
1274 --deferred cogs change
1275 --when old acct than stamp cogs for multiple OU's.
1276 UPDATE mtl_material_transactions
1277 SET trx_flow_header_id = l_header_id,
1278 so_issue_account_type = 1--cogs
1279 WHERE transaction_id = p_transaction_id;
1280 IF (SQL%ROWCOUNT = 0) THEN
1281 IF (l_debug = 1) THEN
1282 print_debug('No MMT record is found for update with trx id:'
1283 || p_transaction_id ,9);
1284 END IF;
1285 FND_MESSAGE.SET_NAME('INV', 'INV_MMT_NOT_FOUND');
1286 FND_MSG_PUB.ADD;
1287 RAISE FND_API.G_EXC_ERROR;
1288 END IF;
1289
1290 END IF;--end of (l_transaction_flow_exists = 1 AND l_new_accounting_flag = 'Y')
1291 x_return_status := FND_API.G_RET_STS_SUCCESS;
1292 END IF;--End condition for 'if' internal order int shipment trx.
1293 EXCEPTION
1294 WHEN FND_API.G_EXC_ERROR THEN
1295 IF (l_debug = 1) THEN
1296 print_debug('create_logical_trx_wrapper error exception, l_progress = '
1297 || l_progress, 9);
1298 END IF;
1299 x_return_status := FND_API.G_RET_STS_ERROR;
1300 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1301
1302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1303 IF (l_debug = 1) THEN
1304 print_debug('create_logical_trx_wrapper unexpected error exception,l_progress = ' || l_progress, 9);
1305 END IF;
1306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1307 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1308
1309 WHEN OTHERS THEN
1310 IF (l_debug = 1) THEN
1311 print_debug('create_logical_trx_wrapper other exception, l_progress = ' || l_progress || ' ' || substr(sqlerrm, 1, 100), 9);
1312 END IF;
1313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1314 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1315
1316 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1317 FND_MSG_PUB.Add_Exc_Msg
1318 (G_PKG_NAME, 'INV_LOGICAL_TRANSACTIONS_PUB');
1319 END IF;
1320 END create_logical_trx_wrapper;
1321
1322 /*==================================================================================*
1323 | Procedure : CREATE_LOGICAL_TRANSACTIONS |
1324 | |
1325 | Description : The create_logical_transactions API will be a public API that will |
1326 | be called from within Oracle Inventory and other modules that would|
1327 | like to insert records into mtl_material_transactions table as part|
1328 | part of a logical shipment or a logical receipt transaction or a |
1329 | retroactive price change transaction. |
1330 | The following transactions may trigger such as insert: |
1331 | 1. Sales order issue transaction tied to a transaction flow |
1332 | spanning across multiple operating units. |
1333 | 2. Global procurement transaction tied to a transaction flow |
1334 | across multiple operating units. |
1335 | 3. Drop ship transaction from a supplier/vendor to a customer |
1336 | spanning across multiple operating units and tied to a |
1337 | transaction flow. The drop shipments can also be a combination |
1338 | of the global procurement and a shipment flow depending on the |
1339 | receiving operating unit. |
1340 | 4. Retroactive price update that has a consumption advice already |
1341 | created. |
1342 | 5. In-transit receipt transaction with an expense destination. |
1343 | 6. All return transactions such as return to vendor, RMAs or PO |
1344 | corrections spanning multiple operating units. |
1345 | |
1346 | Input Parameters: |
1347 | p_api_version_number - API version number |
1348 | p_init_msg_lst - Whether initialize the error message list or not |
1349 | Should be fnd_api.g_false or fnd_api.g_true |
1350 | p_mtl_trx_tbl - An array of mtl_trx_rec_type records, the definition |
1351 | is in the INV_LOGICAL_TRANSACTION_GLOBAL package. |
1352 | p_validation_flag - To indicate whether the call to this API is a trusted|
1353 | call or not. Depending on this flag, we will decide |
1354 | whether to validate the parameters passed. |
1355 | Default will be 'TRUE' |
1356 | p_trx_flow_header_id - The header id of the transaction flow that is being |
1357 | used. This parameter would be null for retroactive |
1358 | price update transactions. |
1359 | p_defer_logical_transactions - The flag indicates whether to defer the |
1360 | creation of logical transactions or not. The |
1361 | following are the values: |
1362 | 1 - YES. This would indicate that the creation of |
1363 | logical transactions would be deferred. |
1364 | 2 - No. This would indicate that the creation of |
1365 | logical transactions would not be deferred. |
1366 | 3 - Use the flag set at the Org level. mtl_parameters|
1367 | will hold the default value for a specific |
1368 | organization. |
1369 | Default would be set to 3 - use the flag set at |
1370 | the organization level. |
1371 | p_logical_trx_type_code - Indentify the type of transaction being processed. |
1372 | The following are the values: |
1373 | 1 - Indicates a Drop Ship transaction. |
1374 | 2 - Indicates sales order shipment spanning multiple |
1375 | operating units/RMA return transaction flow |
1376 | across multiple nodes. |
1377 | 3 - Indicates Global Procurement/Return to Vendor |
1378 | 4 - Retroactive Price Update. |
1379 | Null - Transactions that does not belong to any of |
1380 | the type mentioned above. |
1381 | |
1382 | p_exploded_flag - This will indicate whether the table of records that |
1383 | is being passed to this API has already been exploded|
1384 | or not. Exploded means that all the logical |
1385 | transactions for all the intermediate nodes have been|
1386 | created and this API would just perform a bulk insert|
1387 | into MMT. Otherwise, this API has to create all the |
1388 | logical transactions. Default value will be 2 (No). |
1389 | The following are the values this can take: |
1390 | 1 - YES. This would indicate that the calling API has|
1391 | already exploded all the nodes and all this API |
1392 | has to do is to insert the logical transactions |
1393 | into MMT. |
1394 | 2 - No. This would indicate that the calling API has |
1395 | not done the creation of the logical transactions|
1396 | and this API would have to explode the |
1397 | intermediate nodes. |
1398 | Output Parameters: |
1399 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
1400 | fnd_api.g_ret_sts_exc_error, if an expected error |
1401 | occurred |
1402 | fnd_api.g_ret_sts_unexp_error, if an unexpected error |
1403 | occurred |
1404 | x_msg_count - Number of error message in the error message list |
1405 | x_msg_data - If the number of error message in the error message |
1406 | message list is one, the error message is in |
1407 | this output parameter |
1408 *==================================================================================*/
1409 PROCEDURE create_logical_transactions(
1410 x_return_status OUT NOCOPY VARCHAR2
1411 , x_msg_count OUT NOCOPY NUMBER
1412 , x_msg_data OUT NOCOPY VARCHAR2
1413 , p_api_version_number IN NUMBER := 1.0
1414 , p_init_msg_lst IN VARCHAR2 := G_FALSE
1415 , p_mtl_trx_tbl IN inv_logical_transaction_global.mtl_trx_tbl_type
1416 , p_validation_flag IN VARCHAR2 := G_TRUE
1417 , p_trx_flow_header_id IN NUMBER
1418 , p_defer_logical_transactions IN NUMBER := G_DEFER_LOGICAL_TRX_ORG_LEVEL
1419 , p_logical_trx_type_code IN NUMBER := NULL
1420 , p_exploded_flag IN NUMBER := G_NOT_EXPLODED
1421 )
1422 IS
1423 l_api_version_number CONSTANT NUMBER := 1.0;
1424 l_in_api_version_number NUMBER := NVL(p_api_version_number, 1.0);
1425 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_LOGICAL_TRANSACTIONS';
1426 l_init_msg_lst VARCHAR2(1) := NVL(p_init_msg_lst, G_FALSE);
1427 l_progress NUMBER;
1428
1429 l_mtl_trx_tbl INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_tbl_type;
1430 l_mtl_trx_tbl_temp INV_LOGICAL_TRANSACTION_GLOBAL.mtl_trx_tbl_type;
1431 l_defer_logical_trx NUMBER := p_defer_logical_transactions;
1432 l_project_id NUMBER;
1433 l_user_id NUMBER := fnd_global.user_id;
1434 l_row_id VARCHAR2(10) := NULL;
1435 l_transaction_batch_id NUMBER;
1436 l_parent_transaction_id NUMBER;
1437 l_start_org_id NUMBER;
1438 l_rec_start_org_id NUMBER;
1439 l_transaction_uom VARCHAR2(3);
1440 l_selling_OU NUMBER;
1441 l_shipping_OU NUMBER;
1442 l_ship_from_org_id NUMBER;
1443 l_new_accounting_flag VARCHAR2(1) := 'N';
1444 l_index NUMBER;
1445 l_trx_flow_tbl INV_TRANSACTION_FLOW_PUB.g_transaction_flow_tbl_type;
1446 l_transfer_price_tbl mtl_transfer_price_tbl_type;
1447 l_qualifier_code_tbl INV_TRANSACTION_FLOW_PUB.number_tbl;
1448 l_qualifier_value_tbl INV_TRANSACTION_FLOW_PUB.number_tbl;
1449 l_trx_id NUMBER;
1450 l_is_return NUMBER := 0; -- values: 0 - No, 1 - Yes
1451 l_dsreceive BOOLEAN;
1452 l_drop_ship_flag VARCHAR2(1) := 'N';
1453 l_inv_asset_flag VARCHAR2(1);
1454 l_lot_control_code NUMBER;
1455 l_serial_control_code NUMBER;
1456 l_order_source VARCHAR2(40) := fnd_profile.value('ONT_SOURCE_CODE');
1457 l_oe_header_id NUMBER;
1458 l_oe_order_type_id NUMBER;
1459 l_oe_order_type_name VARCHAR2(80);
1460 l_ic_to_inv_organization_id NUMBER;
1461 l_return_status VARCHAR2(1);
1462 l_msg_count NUMBER;
1463 l_msg_data VARCHAR2(2000);
1464 --Bug#8620411
1465 l_uom_rate NUMBER := 1;
1466 l_primary_uom VARCHAR2(4);
1467 -- Bug 4411804: Removing direct updates to WLPN
1468 l_lpn WMS_CONTAINER_PUB.lpn;
1469 --
1470 -- Bug: - umoogala 13-Feb-2006
1471 -- No bug is logged. But fixing as part of bug 5008080.
1472 -- Do NOT insert into MTL_CST_TXN_COST_DETAILS table for
1473 -- process mfg organizations.
1474 --
1475 l_prev_organization_id BINARY_INTEGER := NULL;
1476 l_process_enabled_flag VARCHAR2(1) := NULL;
1477 BEGIN
1478 SAVEPOINT create_logical_transactions;
1479
1480 IF (l_debug = 1) THEN
1481 print_debug('Enter create_logical_transactions', 9);
1482 print_debug('p_api_version_number = ' || p_api_version_number, 9);
1483 print_debug('l_in_api_version_number = ' || l_in_api_version_number, 9);
1484 print_debug('p_init_msg_lst = ' || p_init_msg_lst, 9);
1485 print_debug('l_init_msg_lst = ' || l_init_msg_lst, 9);
1486 print_debug('p_validation_flag = ' || p_validation_flag, 9);
1487 print_debug('p_trx_flow_header_id = ' || p_trx_flow_header_id, 9);
1488 print_debug('p_defer_logical_transactions = ' || p_defer_logical_transactions, 9);
1489 print_debug('p_logical_trx_type_code = ' || p_logical_trx_type_code, 9);
1490 print_debug('p_exploded_flag = ' || p_exploded_flag, 9);
1491 END IF;
1492
1493 -- Standard call to check for call compatibility
1494 IF NOT fnd_api.compatible_api_call(l_api_version_number,
1495 l_in_api_version_number, l_api_name, g_pkg_name) THEN
1496 RAISE fnd_api.g_exc_unexpected_error;
1497 END IF;
1498
1499 -- Initialize message list.
1500 IF fnd_api.to_boolean(l_init_msg_lst) THEN
1501 fnd_msg_pub.initialize;
1502 END IF;
1503
1504
1505 l_progress := 10;
1506 -- If the defer accounting trx is 3 (at org level)
1507 -- get the defer logical transactions flag from mtl_parameters
1508 IF (l_defer_logical_trx = G_DEFER_LOGICAL_TRX_ORG_LEVEL) THEN
1509 BEGIN
1510 IF (l_debug = 1) THEN
1511 print_debug('l_defer_logical_trx = 3', 9);
1512 print_debug('get defer_logical_transactions from org ' ||
1513 p_mtl_trx_tbl(1).organization_id, 9);
1514 END IF;
1515 l_progress := 20;
1516 SELECT defer_logical_transactions
1517 INTO l_defer_logical_trx
1518 FROM mtl_parameters
1519 WHERE organization_id = p_mtl_trx_tbl(1).organization_id;
1520
1521 l_progress := 30;
1522 IF (l_debug = 1) THEN
1523 print_debug('defer_logical_transactions is: ' || l_defer_logical_trx, 9);
1524 END IF;
1525 EXCEPTION
1526 WHEN no_data_found THEN
1527 IF (l_debug = 1) THEN
1528 print_debug('Cannot get defer logical trx flag from mtl_parameters', 9);
1529 END IF;
1530 FND_MESSAGE.SET_NAME('INV', 'INV_DEFER_LOGICAL_ERR');
1531 FND_MSG_PUB.ADD;
1532 RAISE FND_API.G_EXC_ERROR;
1533 END;
1534 END IF;
1535
1536 IF (l_debug = 1) THEN
1537 print_debug('The p_logical_trx_type_code is : ' || p_logical_trx_type_code, 9);
1538 END IF;
1539
1540 l_mtl_trx_tbl := p_mtl_trx_tbl;
1541
1542 -- ***** Starts populating logical transaction records of the pl/sql table *****
1543 IF (p_logical_trx_type_code = G_LOGTRXCODE_RETROPRICEUPD) THEN
1544 l_progress := 40;
1545 -- Retroactive price update
1546 IF (l_debug = 1) THEN
1547 print_debug('Trx type is Retroactive price update', 9);
1548 print_debug('Start constructing pl/sql table for retroactive price update', 9);
1549 END IF;
1550
1551 -- Populate account period id to the record if it's null
1552 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
1553 IF (l_mtl_trx_tbl(i).acct_period_id IS NULL) THEN
1554 l_progress := 50;
1555 get_acct_period(x_return_status => l_return_status
1556 , x_msg_count => l_msg_count
1557 , x_msg_data => l_msg_data
1558 , x_acct_period_id => l_mtl_trx_tbl(i).acct_period_id
1559 , p_organization_id => l_mtl_trx_tbl(i).organization_id
1560 , p_transaction_date => l_mtl_trx_tbl(i).transaction_date);
1561 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
1562 IF (l_debug = 1) THEN
1563 print_debug('get_acct_period returns error with org id = '
1564 || l_mtl_trx_tbl(i).organization_id, 9);
1565 print_debug('x_msg_data = ' || x_msg_data, 9);
1566 END IF;
1567 FND_MESSAGE.SET_NAME('INV', 'INV_PERIOD_RETRIEVAL_ERROR');
1568 FND_MSG_PUB.ADD;
1569 RAISE FND_API.G_EXC_ERROR;
1570 END IF;
1571 END IF;
1572
1573 -- get the project if the locator is populated and tied to project
1574 IF (l_mtl_trx_tbl(i).project_id IS NULL AND l_mtl_trx_tbl(i).locator_id > 0) THEN
1575 l_progress := 60;
1576 BEGIN
1577 IF (l_debug = 1) THEN
1578 print_debug('getting the project id and task id', 9);
1579 END IF;
1580 SELECT project_id, task_id
1581 INTO l_mtl_trx_tbl(i).project_id,
1582 l_mtl_trx_tbl(i).task_id
1583 FROM mtl_item_locations
1584 WHERE organization_id = l_mtl_trx_tbl(i).organization_id
1585 and inventory_location_id = l_mtl_trx_tbl(i).locator_id;
1586 IF (l_debug = 1) THEN
1587 print_debug('The project id = ' || l_mtl_trx_tbl(i).project_id ||
1588 ' and the task id = ' || l_mtl_trx_tbl(i).task_id, 9);
1589 END IF;
1590 EXCEPTION
1591 WHEN no_data_found THEN
1592 l_mtl_trx_tbl(i).project_id := NULL;
1593 l_mtl_trx_tbl(i).task_id := NULL;
1594 IF (l_debug = 1) THEN
1595 print_debug('No project id is found', 9);
1596 END IF;
1597 END;
1598 END IF;
1599
1600 -- Get default cost group if no project id, else get cost group of the project
1601 IF (l_mtl_trx_tbl(i).project_id IS NULL) THEN
1602 l_progress := 70;
1603 get_default_costgroup(
1604 x_return_status => l_return_status
1605 , x_msg_count => l_msg_count
1606 , x_msg_data => l_msg_data
1607 , x_cost_group_id => l_mtl_trx_tbl(i).cost_group_id
1608 , p_organization_id => l_mtl_trx_tbl(i).organization_id);
1609 ELSE
1610 l_progress := 80;
1611 get_project_costgroup(
1612 x_return_status => l_return_status
1613 , x_msg_count => l_msg_count
1614 , x_msg_data => l_msg_data
1615 , x_cost_group_id => l_mtl_trx_tbl(i).cost_group_id
1616 , p_project_id => l_mtl_trx_tbl(i).project_id
1617 , p_organization_id => l_mtl_trx_tbl(i).organization_id);
1618 END IF;
1619
1620 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
1621 IF (l_debug = 1) THEN
1622 print_debug('get_default_costgroup returns error', 9);
1623 print_debug('l_msg_data = ' || l_msg_data, 9);
1624 END IF;
1625 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_COST_GROUP');
1626 FND_MSG_PUB.ADD;
1627 RAISE FND_API.G_EXC_ERROR;
1628 END IF;
1629
1630 l_mtl_trx_tbl(i).costed_flag := 'N';
1631 l_mtl_trx_tbl(i).invoiced_flag := null;
1632
1633 -- populate the transaction id to the record
1634 SELECT mtl_material_transactions_s.nextval
1635 INTO l_mtl_trx_tbl(i).transaction_id
1636 FROM DUAL;
1637
1638 l_mtl_trx_tbl(i).transaction_batch_id := l_mtl_trx_tbl(1).transaction_id;
1639 l_mtl_trx_tbl(i).transaction_batch_seq := i;
1640
1641 END LOOP;
1642
1643 IF (l_debug = 1) THEN
1644 print_debug('End of constructing pl/sql table for retroactive price update', 9);
1645 END IF;
1646 elsif (p_logical_trx_type_code = G_LOGTRXCODE_GLOBPROCRTV OR
1647 p_logical_trx_type_code = G_LOGTRXCODE_DSRECEIPT) THEN
1648 -- global procurement/Return to vendor, or True dropship with logical PO receipt
1649 l_progress := 90;
1650 IF (l_debug = 1) THEN
1651 print_debug('Trx Type: Global procurement/Return to vendor or DS receipt', 9);
1652 print_debug('Start constructing pl/sql table for GLOB PROC/RTV or DS recipet', 9);
1653 END IF;
1654
1655 -- Return error if the input table is not exploded
1656 IF (p_exploded_flag = G_NOT_EXPLODED) THEN
1657 IF (l_debug = 1) THEN
1658 print_debug('Records are not exploded', 9);
1659 END IF;
1660 FND_MESSAGE.SET_NAME('INV', 'INV_REC_NOT_EXPLODED');
1661 FND_MSG_PUB.ADD;
1662 RAISE FND_API.G_EXC_ERROR;
1663 END IF;
1664
1665 IF (l_debug = 1) THEN
1666 print_debug('Get the start OU from the transaction flow header', 9);
1667 END IF;
1668
1669 -- get the start OU from the transaction flow header
1670 -- this is used for later when we get the cost_group_id
1671 l_progress := 100;
1672 SELECT start_org_id
1673 INTO l_start_org_id
1674 FROM mtl_transaction_flow_headers
1675 WHERE header_id = p_trx_flow_header_id;
1676
1677 l_progress := 110;
1678 IF (l_debug = 1) THEN
1679 print_debug('The start OU from the transaction flow header = ' || l_start_org_id, 9);
1680 END IF;
1681
1682 IF (l_defer_logical_trx = G_NOT_DEFER_LOGICAL_TRX) THEN -- not defer
1683 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
1684 l_progress := 120;
1685 -- populate transaction id
1686 SELECT mtl_material_transactions_s.nextval
1687 INTO l_mtl_trx_tbl(i).transaction_id
1688 FROM dual;
1689
1690 l_progress := 130;
1691
1692 -- If the record is parent transaction, populate transaction id to the
1693 -- parent transaction id and transaction batch id
1694 IF (l_mtl_trx_tbl(i).parent_transaction_flag = 1) THEN
1695 l_mtl_trx_tbl(i).parent_transaction_id := l_mtl_trx_tbl(i).transaction_id;
1696 l_mtl_trx_tbl(i).transaction_batch_id := l_mtl_trx_tbl(i).transaction_id;
1697 l_parent_transaction_id := l_mtl_trx_tbl(i).parent_transaction_id;
1698 l_transaction_batch_id := l_mtl_trx_tbl(i).transaction_batch_id;
1699 END IF;
1700
1701 -- Populate account period id to the record if it's null
1702 IF (l_mtl_trx_tbl(i).acct_period_id IS NULL) THEN
1703 l_progress := 140;
1704 get_acct_period(x_return_status => l_return_status
1705 , x_msg_count => l_msg_count
1706 , x_msg_data => l_msg_data
1707 , x_acct_period_id => l_mtl_trx_tbl(i).acct_period_id
1708 , p_organization_id => l_mtl_trx_tbl(i).organization_id
1709 , p_transaction_date => l_mtl_trx_tbl(i).transaction_date);
1710 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
1711 IF (l_debug = 1) THEN
1712 print_debug('get_acct_period returns error with org id = '
1713 || l_mtl_trx_tbl(1).organization_id, 9);
1714 print_debug('x_msg_data = ' || x_msg_data, 9);
1715 END IF;
1716 FND_MESSAGE.SET_NAME('INV', 'INV_PERIOD_RETRIEVAL_ERROR');
1717 FND_MSG_PUB.ADD;
1718 RAISE FND_API.G_EXC_ERROR;
1719 END IF;
1720 END IF;
1721
1722 -- Populate cost group id to the record
1723 -- If it's the first node and project id is populated, get the cost
1724 -- group of project and task, else if it's the first node and no project
1725 -- tied to it or if it's the intermediate nodes with project or not, get
1726 -- the default cost group of the organization level
1727 l_progress := 150;
1728 IF (l_mtl_trx_tbl(i).cost_group_id IS NULL) THEN
1729 SELECT to_number(org_information3)
1730 INTO l_rec_start_org_id
1731 FROM hr_organization_information
1732 WHERE organization_id = l_mtl_trx_tbl(i).organization_id
1733 AND org_information_context = 'Accounting Information';
1734
1735 -- For the first node, check if there's project tied to the locator
1736 IF (l_start_org_id = l_rec_start_org_id) THEN --It's the first node
1737 -- get the project if the locator is populated and tied to project
1738 IF (l_mtl_trx_tbl(i).project_id IS NULL AND l_mtl_trx_tbl(i).locator_id>0) THEN
1739 BEGIN
1740 l_progress := 160;
1741 SELECT project_id, task_id
1742 INTO l_mtl_trx_tbl(i).project_id,
1743 l_mtl_trx_tbl(i).task_id
1744 FROM mtl_item_locations
1745 WHERE organization_id = l_mtl_trx_tbl(1).organization_id
1746 and inventory_location_id = l_mtl_trx_tbl(1).locator_id;
1747 EXCEPTION
1748 WHEN no_data_found THEN
1749 l_mtl_trx_tbl(i).project_id := NULL;
1750 IF (l_debug = 1) THEN
1751 print_debug('No project id is found', 9);
1752 END IF;
1753 END;
1754 END IF;
1755 END IF;
1756
1757 IF ((l_start_org_id = l_rec_start_org_id AND l_mtl_trx_tbl(i).project_id is null)
1758 OR (l_start_org_id <> l_rec_start_org_id)) THEN
1759 l_progress := 170;
1760 get_default_costgroup(
1761 x_return_status => l_return_status
1762 , x_msg_count => l_msg_count
1763 , x_msg_data => l_msg_data
1764 , x_cost_group_id => l_mtl_trx_tbl(i).cost_group_id
1765 , p_organization_id => l_mtl_trx_tbl(i).organization_id);
1766 ELSE
1767 l_progress := 180;
1768 get_project_costgroup(
1769 x_return_status => l_return_status
1770 , x_msg_count => l_msg_count
1771 , x_msg_data => l_msg_data
1772 , x_cost_group_id => l_mtl_trx_tbl(i).cost_group_id
1773 , p_project_id => l_mtl_trx_tbl(i).project_id
1774 , p_organization_id => l_mtl_trx_tbl(i).organization_id);
1775 END IF;
1776
1777 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
1778 IF (l_debug = 1) THEN
1779 print_debug('get_default_costgroup returns error', 9);
1780 print_debug('l_msg_data = ' || l_msg_data, 9);
1781 END IF;
1782 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_COST_GROUP');
1783 FND_MSG_PUB.ADD;
1784 RAISE FND_API.G_EXC_ERROR;
1785 END IF;
1786 END IF;
1787
1788 IF (l_mtl_trx_tbl(i).transfer_cost_group_id is null) THEN
1789 IF (l_mtl_trx_tbl(i).transaction_action_id IN (G_ACTION_LOGICALICSALES,
1790 G_ACTION_LOGICALICRECEIPT, G_ACTION_LOGICALICSALESRETURN,
1791 G_ACTION_LOGICALICRCPTRETURN)) THEN
1792 get_default_costgroup(
1793 x_return_status => l_return_status
1794 , x_msg_count => l_msg_count
1795 , x_msg_data => l_msg_data
1796 , x_cost_group_id => l_mtl_trx_tbl(i).transfer_cost_group_id
1797 , p_organization_id => l_mtl_trx_tbl(i).transfer_organization_id);
1798
1799 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
1800 IF (l_debug = 1) THEN
1801 print_debug('get_default_costgroup returns error', 9);
1802 print_debug('l_msg_data = ' || l_msg_data, 9);
1803 END IF;
1804 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_COST_GROUP');
1805 FND_MSG_PUB.ADD;
1806 RAISE FND_API.G_EXC_ERROR;
1807 END IF;
1808 END IF;
1809 END IF;
1810 END LOOP;
1811
1812 -- populate the parent_transaction_id, transaction_batch_id and
1813 -- transaction_batch_seq to every records of the table
1814 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
1815 l_mtl_trx_tbl(i).parent_transaction_id := l_parent_transaction_id;
1816 l_mtl_trx_tbl(i).transaction_batch_id := l_transaction_batch_id;
1817 l_mtl_trx_tbl(i).transaction_batch_seq := i;
1818 END LOOP;
1819
1820 IF (l_debug = 1) THEN
1821 print_debug('End constructing pl/sql table for global proc/Return to vendor', 9);
1822 END IF;
1823 ELSE -- else of if (l_defer_logical_trx = G_NOT_DEFER_LOGICAL_TRX)
1824 IF (l_debug = 1) THEN
1825 print_debug('Cannot defer creating logical trx for global proc/return to vendor', 9);
1826 END IF;
1827
1828 x_return_status := FND_API.G_RET_STS_ERROR;
1829 FND_MESSAGE.SET_NAME('INV', 'INV_CANNOT_DEFER_LOGICAL_TRX');
1830 FND_MSG_PUB.ADD;
1831 RAISE FND_API.G_EXC_ERROR;
1832 END IF; -- end of if (l_defer_logical_trx = G_NOT_DEFER_LOGICAL_TRX)
1833 -- true dropship(shipping flow)/SO issue across OUs/RMA return
1834 ELSIF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER
1835 or p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE) THEN
1836 -- IF true drop ship(shipping flow) and Yes defer flag, return error
1837 IF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER
1838 and l_defer_logical_trx = G_DEFER_LOGICAL_TRX) THEN
1839 IF (l_debug = 1) THEN
1840 print_debug('Cannot defer creating logical trx for true dropship', 9);
1841 END IF;
1842
1843 x_return_status := FND_API.G_RET_STS_ERROR;
1844 FND_MESSAGE.SET_NAME('INV', 'INV_CANNOT_DEFER_LOGICAL_TRX');
1845 FND_MSG_PUB.ADD;
1846 RAISE FND_API.G_EXC_ERROR;
1847 ELSIF (p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE
1848 and l_defer_logical_trx = G_DEFER_LOGICAL_TRX) THEN
1849 -- If sales order issue/RMA and yes defer flag, update the MMT record
1850
1851 IF (l_debug = 1) THEN
1852 print_debug('Defer creating logical trx for SO issue/RMA', 9);
1853 print_debug('Update MMT with transaction_id = ' || p_mtl_trx_tbl(1).transaction_id, 9);
1854 print_debug('Update MMT with header_id = ' || p_trx_flow_header_id, 9);
1855 END IF;
1856 -- Bug:3426281. Have to update the MMT record with the header id
1857 l_progress := 190;
1858 UPDATE mtl_material_transactions
1859 SET logical_transactions_created = 2,
1860 invoiced_flag = NULL,
1861 trx_flow_header_id = p_trx_flow_header_id
1862 WHERE transaction_id = p_mtl_trx_tbl(1).transaction_id;
1863 IF (SQL%ROWCOUNT = 0) THEN
1864 IF (l_debug = 1) THEN
1865 print_debug('No MMT record is found to update with logical_transactions_created=N', 9);
1866 END IF;
1867 FND_MESSAGE.SET_NAME('INV', 'INV_MMT_NOT_FOUND');
1868 FND_MSG_PUB.ADD;
1869 RAISE FND_API.G_EXC_ERROR;
1870 END IF;
1871 return;
1872 END IF;
1873
1874 -- set the return flag to check if the transaction is RMA later
1875 IF (l_mtl_trx_tbl(1).transaction_source_type_id = G_SOURCETYPE_RMA and
1876 (l_mtl_trx_tbl(1).transaction_action_id = G_ACTION_RECEIPT
1877 OR l_mtl_trx_tbl(1).transaction_action_id = G_ACTION_ISSUE)) THEN
1878 l_is_return := 1;
1879 END IF;
1880
1881 -- get the shipping OU and selling OU and ship from org id for get_transaction_flow api
1882 l_progress := 200;
1883 BEGIN
1884 SELECT start_org_id,
1885 end_org_id,
1886 new_accounting_flag
1887 into l_selling_OU,
1888 l_shipping_OU,
1889 l_new_accounting_flag
1890 FROM mtl_transaction_flow_headers
1891 WHERE header_id = p_trx_flow_header_id;
1892
1893 l_progress := 210;
1894 IF (l_debug = 1) THEN
1895 print_debug('Selling OU: ' || l_selling_OU || ' Shipping OU: ' || l_shipping_OU, 9);
1896 END IF;
1897 EXCEPTION
1898 WHEN no_data_found THEN
1899 IF (l_debug = 1) THEN
1900 print_debug('Transaction flow not defined for header_id = ' || p_trx_flow_header_id, 9);
1901 END IF;
1902 FND_MESSAGE.SET_NAME('INV', 'INV_NO_TRX_FLOW');
1903 FND_MSG_PUB.ADD;
1904 RAISE FND_API.G_EXC_ERROR;
1905 END;
1906
1907 -- For dropship deliver, get the sales order id and populate to transaction_source_id
1908 -- Also get the lot_control_code and serial_control_code of the item.
1909 IF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER) THEN
1910 IF (l_debug = 1) THEN
1911 print_debug('DSDELIVER: rcv_transaction_id = ' || l_mtl_trx_tbl(1).rcv_transaction_id, 9);
1912 END IF;
1913
1914 BEGIN
1915 l_progress := 220;
1916 SELECT MAX(odss.line_id), odss.header_id
1917 INTO l_mtl_trx_tbl(1).trx_source_line_id, l_oe_header_id
1918 FROM oe_drop_ship_sources odss, rcv_transactions rt
1919 WHERE rt.transaction_id = l_mtl_trx_tbl(1).rcv_transaction_id
1920 AND rt.po_line_location_id = odss.line_location_id
1921 GROUP BY odss.header_id;
1922
1923 IF (l_debug = 1) THEN
1924 print_debug('DSDELIVER: trx_source_line_id = ' || l_mtl_trx_tbl(1).trx_source_line_id, 9);
1925 print_debug('DSDELIVER: l_oe_header_id = ' || l_oe_header_id, 9);
1926 END IF;
1927
1928 l_progress := 230;
1929 SELECT oeh.order_type_id, oet.name
1930 INTO l_oe_order_type_id, l_oe_order_type_name
1931 FROM oe_order_headers_all oeh, oe_transaction_types_tl oet
1932 WHERE oeh.header_id = l_oe_header_id
1933 AND oeh.order_type_id = oet.transaction_type_id
1934 AND oet.language = (Select language_code from fnd_languages where installed_flag = 'B');
1935
1936 IF (l_debug = 1) THEN
1937 print_debug('DSDELIVER: l_oe_order_type_id = ' || l_oe_order_type_id, 9);
1938 print_debug('DSDELIVER: l_oe_order_type_name = ' || l_oe_order_type_name, 9);
1939 END IF;
1940
1941 /* bug 4155079, added to_char to oeh.order_number */
1942
1943 l_progress := 240;
1944 SELECT mso.sales_order_id
1945 INTO l_mtl_trx_tbl(1).transaction_source_id
1946 FROM oe_order_headers_all oeh, mtl_sales_orders mso
1947 WHERE to_char(oeh.order_number) = mso.segment1
1948 AND mso.segment2 = l_oe_order_type_name
1949 AND mso.segment3 = l_order_source
1950 AND oeh.header_id = l_oe_header_id;
1951
1952 IF (l_debug = 1) THEN
1953 print_debug('DSDELIVER: transaction_source_id = ' || l_mtl_trx_tbl(1).transaction_source_id, 9);
1954 END IF;
1955 /******
1956 SELECT MAX(odss.line_id),
1957 mso.sales_order_id
1958 INTO l_mtl_trx_tbl(1).trx_source_line_id,
1959 l_mtl_trx_tbl(1).transaction_source_id
1960 FROM oe_drop_ship_sources odss,
1961 rcv_transactions rt,
1962 mtl_sales_orders mso,
1963 oe_order_headers_all ooha
1964 WHERE rt.transaction_id = l_mtl_trx_tbl(1).rcv_transaction_id
1965 AND odss.line_location_id = rt.po_line_location_id
1966 AND odss.header_id = ooha.header_id
1967 AND ooha.order_number = mso.segment1
1968 GROUP BY mso.sales_order_id;
1969 ******/
1970 /*****
1971 SELECT mso.sales_order_id
1972 INTO l_mtl_trx_tbl(1).transaction_source_id
1973 FROM mtl_sales_orders mso,
1974 oe_order_headers_all ooha,
1975 oe_order_lines_all oola
1976 WHERE oola.line_id = l_mtl_trx_tbl(1).trx_source_line_id
1977 AND oola.header_id = ooha.header_id
1978 AND ooha.order_number = mso.segment1;
1979 *****/
1980
1981 EXCEPTION
1982 WHEN no_data_found THEN
1983 IF (l_debug = 1) THEN
1984 print_debug('no sales order found for line id = ' || l_mtl_trx_tbl(1).trx_source_line_id, 9);
1985 END IF;
1986 FND_MESSAGE.SET_NAME('INV', 'INV_NO_SO');
1987 FND_MSG_PUB.ADD;
1988 RAISE FND_API.G_EXC_ERROR;
1989
1990 WHEN others THEN
1991 IF (l_debug = 1) THEN
1992 print_debug('when others ' || sqlerrm, 9);
1993 print_Debug('l_progress = ' || l_progress, 9);
1994 END IF;
1995 FND_MESSAGE.SET_NAME('INV', 'INV_NO_SO');
1996 FND_MSG_PUB.ADD;
1997 RAISE FND_API.G_EXC_ERROR;
1998 END;
1999
2000 IF (l_debug = 1) THEN
2001 print_debug('sales order id = ' ||
2002 l_mtl_trx_tbl(1).transaction_source_id, 9);
2003 print_debug('line id = ' ||
2004 l_mtl_trx_tbl(1).trx_source_line_id, 9);
2005 END IF;
2006
2007 SELECT lot_control_code, serial_number_control_code
2008 INTO l_lot_control_code, l_serial_control_code
2009 FROM mtl_system_items
2010 WHERE organization_id = l_mtl_trx_tbl(1).organization_id
2011 AND inventory_item_id = l_mtl_trx_tbl(1).inventory_item_id;
2012
2013 IF (l_debug = 1) THEN
2014 print_debug('l_lot_control_code = ' || l_lot_control_code, 9);
2015 print_debug('l_serial_control_code = ' || l_serial_control_code, 9);
2016 END IF;
2017 END IF;
2018
2019 -- for true dropship of shipping flow, if there is only single OU, which means
2020 -- shipping OU = selling OU, and there is transaction flow define and the new
2021 -- accounting flag is Y, we still have to create a logical sales order issue
2022 -- rather than a physical shipment
2023 -- else for multiple OUs, create a table of logical transaction records
2024 IF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER
2025 AND l_selling_OU = l_shipping_OU AND l_new_accounting_flag = 'Y') THEN
2026 IF (l_debug = 1) THEN
2027 print_debug('Drop shipment deliver and selling OU is the same as shipping OU', 9);
2028 print_debug('Transaction flow exists and new accounting flag=Y', 9);
2029 print_debug('Constructing the pl/sql table for DS deliver single OU', 9);
2030 END IF;
2031
2032 -- update the input(first) record of the pl/sql table
2033 -- get the project if the locator is populated and tied to project
2034 /*
2035 IF (l_mtl_trx_tbl(1).project_id IS NULL AND l_mtl_trx_tbl(1).locator_id > 0) THEN
2036 l_progress := 220;
2037 BEGIN
2038 SELECT project_id, task_id
2039 INTO l_mtl_trx_tbl(1).project_id,
2040 l_mtl_trx_tbl(1).task_id
2041 FROM mtl_item_locations
2042 WHERE organization_id = l_mtl_trx_tbl(1).organization_id
2043 and inventory_location_id = l_mtl_trx_tbl(1).locator_id;
2044 l_progress := 230;
2045 EXCEPTION
2046 WHEN no_data_found THEN
2047 l_mtl_trx_tbl(1).project_id := NULL;
2048 IF (l_debug = 1) THEN
2049 print_debug('No project id is found', 9);
2050 END IF;
2051 END;
2052 END IF;
2053
2054 -- Get the cost group: default cost group if no project id,
2055 -- else cost group of the project
2056 IF (l_mtl_trx_tbl(1).project_id IS NULL) THEN
2057 l_progress := 240;
2058 get_default_costgroup(
2059 x_return_status => l_return_status
2060 , x_msg_count => l_msg_count
2061 , x_msg_data => l_msg_data
2062 , x_cost_group_id => l_mtl_trx_tbl(1).cost_group_id
2063 , p_organization_id => l_mtl_trx_tbl(1).organization_id);
2064
2065 ELSE
2066 l_progress := 250;
2067 get_project_costgroup(
2068 x_return_status => l_return_status
2069 , x_msg_count => l_msg_count
2070 , x_msg_data => l_msg_data
2071 , x_cost_group_id => l_mtl_trx_tbl(1).cost_group_id
2072 , p_project_id => l_mtl_trx_tbl(1).project_id
2073 , p_organization_id => l_mtl_trx_tbl(1).organization_id);
2074 END IF;
2075
2076 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
2077 IF (l_debug = 1) THEN
2078 print_debug('get_default_costgroup returns error', 9);
2079 print_debug('l_msg_data = ' || l_msg_data, 9);
2080 END IF;
2081 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_COST_GROUP');
2082 FND_MSG_PUB.ADD;
2083 RAISE FND_API.G_EXC_ERROR;
2084 END IF;
2085
2086 IF (l_mtl_trx_tbl(1).acct_period_id IS NULL) THEN
2087 l_progress := 260;
2088 get_acct_period(x_return_status => l_return_status
2089 , x_msg_count => l_msg_count
2090 , x_msg_data => l_msg_data
2091 , x_acct_period_id => l_mtl_trx_tbl(1).acct_period_id
2092 , p_organization_id => l_mtl_trx_tbl(1).organization_id
2093 , p_transaction_date => l_mtl_trx_tbl(1).transaction_date);
2094 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
2095 IF (l_debug = 1) THEN
2096 print_debug('get_acct_period returns error with org id = '
2097 || l_mtl_trx_tbl(1).organization_id, 9);
2098 print_debug('x_msg_data = ' || x_msg_data, 9);
2099 END IF;
2100 FND_MESSAGE.SET_NAME('INV', 'INV_PERIOD_RETRIEVAL_ERROR');
2101 FND_MSG_PUB.ADD;
2102 RAISE FND_API.G_EXC_ERROR;
2103 END IF;
2104 END IF;
2105
2106 l_mtl_trx_tbl(1).costed_flag := 'N';
2107 l_mtl_trx_tbl(1).invoiced_flag := null;
2108 l_mtl_trx_tbl(1).parent_transaction_flag := 1;
2109
2110 l_progress := 270;
2111 SELECT mtl_material_transactions_s.nextval
2112 INTO l_mtl_trx_tbl(1).transaction_id
2113 FROM dual;
2114
2115 l_mtl_trx_tbl(1).transaction_batch_id := l_mtl_trx_tbl(1).transaction_id;
2116 l_mtl_trx_tbl(1).transaction_batch_seq := 1;
2117 -- end of updating the input(first) record of the pl/sql table
2118
2119 -- construct a logical sales order issue record of to_org_id
2120 l_index := l_mtl_trx_tbl.COUNT + 1;
2121 IF (l_debug = 1) THEN
2122 print_debug('l_index = ' || l_index, 9);
2123 END IF;
2124
2125 l_mtl_trx_tbl(l_index) := l_mtl_trx_tbl(1);
2126 */
2127 l_mtl_trx_tbl(1).transaction_type_id := G_TYPE_LOGL_SALES_ORDER_ISSUE;
2128 l_mtl_trx_tbl(1).transaction_action_id := G_ACTION_LOGICALISSUE;
2129 l_mtl_trx_tbl(1).transaction_source_type_id := G_SOURCETYPE_SALESORDER;
2130 l_mtl_trx_tbl(1).transaction_date := NVL(p_mtl_trx_tbl(1).transaction_date,sysdate);
2131 l_mtl_trx_tbl(1).transaction_quantity := -1*p_mtl_trx_tbl(1).transaction_quantity;
2132 l_mtl_trx_tbl(1).primary_quantity := -1*p_mtl_trx_tbl(1).primary_quantity;
2133 l_mtl_trx_tbl(1).pm_cost_collected := null;
2134 IF (l_mtl_trx_tbl(1).project_id is null) THEN
2135 l_mtl_trx_tbl(1).cost_group_id := p_mtl_trx_tbl(1).cost_group_id;
2136 ELSE
2137 l_progress := 280;
2138 get_default_costgroup(
2139 x_return_status => l_return_status
2140 , x_msg_count => l_msg_count
2141 , x_msg_data => l_msg_data
2142 , x_cost_group_id => l_mtl_trx_tbl(1).cost_group_id
2143 , p_organization_id => l_mtl_trx_tbl(1).organization_id);
2144
2145 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
2146 IF (l_debug = 1) THEN
2147 print_debug('get_default_costgroup returns error', 9);
2148 print_debug('l_msg_data = ' || l_msg_data, 9);
2149 END IF;
2150 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_COST_GROUP');
2151 FND_MSG_PUB.ADD;
2152 RAISE FND_API.G_EXC_ERROR;
2153 END IF;
2154 END IF;
2155
2156 l_mtl_trx_tbl(1).currency_code := null;
2157 l_mtl_trx_tbl(1).parent_transaction_flag := 2;
2158
2159 l_progress := 290;
2160
2161 -- Bug: 4607049.
2162 -- Added SQL to get the value of TO_ORGANIZATION_ID from the IC Transaction Flow Lines.
2163 -- Pass this TO_ORGANIZATION_ID to get_cogs_acct_for_logical_so()
2164 BEGIN
2165 SELECT to_organization_id
2166 INTO l_ic_to_inv_organization_id
2167 FROM mtl_transaction_flow_lines
2168 WHERE header_id = l_mtl_trx_tbl(1).trx_flow_header_id;
2169 EXCEPTION
2170 WHEN NO_DATA_FOUND THEN
2171 NULL;
2172 END;
2173
2174 get_cogs_acct_for_logical_so(
2175 x_return_status => l_return_status
2176 , x_msg_count => l_msg_count
2177 , x_msg_data => l_msg_data
2178 , x_cogs_acct_id => l_mtl_trx_tbl(1).distribution_account_id
2179 , p_inventory_item_id => l_mtl_trx_tbl(1).inventory_item_id
2180 , p_order_line_id => l_mtl_trx_tbl(1).trx_source_line_id
2181 , p_ic_to_inv_organization_id => l_ic_to_inv_organization_id);
2182 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
2183 IF (l_debug = 1) THEN
2184 print_debug('get_cogs_acct_for_logical_so returns error', 9);
2185 print_debug('l_msg_data = ' || l_msg_data, 9);
2186 END IF;
2187 FND_MESSAGE.SET_NAME('INV', 'INV_NO_COGS_FOR_LOG_SO');
2188 FND_MSG_PUB.ADD;
2189 RAISE FND_API.G_EXC_ERROR;
2190 END IF;
2191
2192 l_progress := 300;
2193 SELECT mtl_material_transactions_s.nextval
2194 INTO l_mtl_trx_tbl(1).transaction_id
2195 FROM dual;
2196
2197 l_mtl_trx_tbl(1).parent_transaction_id := p_mtl_trx_tbl(1).transaction_id;
2198 l_mtl_trx_tbl(1).transaction_batch_id := p_mtl_trx_tbl(1).transaction_id;
2199 l_mtl_trx_tbl(1).transaction_batch_seq := 1;
2200
2201 print_debug('End of constructing the pl/sql table for DS deliver single OU', 9);
2202 -- end of DSDELIVER and single OU
2203 ELSE -- (p_logical_trx_type_code=G_LOGTRXCODE_DSDELIVER
2204 -- and shipping_ou<>selling_ou) or SO issue/RMA return
2205 l_progress := 310;
2206 IF (l_debug = 1) THEN
2207 print_debug('Drop shipment deliver across multiple OUS or SO issue/RMA', 9);
2208 print_debug('Calling INV_TRANSACTION_FLOW_PUB.get_transaction_flow', 9);
2209 END IF;
2210
2211 l_progress := 320;
2212
2213 INV_TRANSACTION_FLOW_PUB.get_transaction_flow(
2214 x_return_status => l_return_status
2215 , x_msg_data => l_msg_data
2216 , x_msg_count => l_msg_count
2217 , x_transaction_flows_tbl => l_trx_flow_tbl
2218 , p_api_version => 1.0
2219 , p_init_msg_list => fnd_api.g_false
2220 , p_header_id => p_trx_flow_header_id
2221 , p_get_default_cost_group => 'Y');
2222
2223 IF (l_debug = 1) THEN
2224 print_debug('get_transaction_flow returns status = ' || l_return_status, 9);
2225 print_debug('Transaction flow counts = ' || l_trx_flow_tbl.COUNT, 9);
2226 END IF;
2227
2228 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2229 IF (l_debug = 1) THEN
2230 print_debug('get_transaction_flow returns error: ' || l_msg_data, 9);
2231 END IF;
2232 RAISE FND_API.G_EXC_ERROR;
2233 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2234 IF (l_debug = 1) THEN
2235 print_debug('get_transaction_flow returns unexpected error: ' || l_msg_data, 9);
2236 END IF;
2237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2238 END IF;
2239
2240 -- If there is transaction flow, then get the the transfer price
2241 -- and construct a pl/sql transfer price table
2242 IF (l_trx_flow_tbl.COUNT > 0) THEN
2243 -- For true dropship (shipping flow), since there is no mmt record inserted
2244 -- before calling the get transfer price, should pass the so line id
2245 -- and 'D' to p_global_procurement_flag to get_transfer_price api
2246 -- else for SO shipment, pass the inserted mmt transaction_id of the
2247 -- physical SO issue record and 'N' to the p_global_procurement_flag
2248 IF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER) THEN
2249 l_trx_id := l_mtl_trx_tbl(1).trx_source_line_id;
2250 l_drop_ship_flag := 'Y';
2251 ELSE
2252 l_trx_id := l_mtl_trx_tbl(1).transaction_id;
2253 l_drop_ship_flag := 'N';
2254 END IF;
2255
2256 l_trx_flow_tbl(1).from_organization_id := l_mtl_trx_tbl(1).organization_id;
2257
2258 IF (l_debug = 1) THEN
2259 print_debug('Calling INV_TRANSACTION_FLOW_PUB.get_transfer_price', 9);
2260 END IF;
2261
2262 -- start construct transfer price pl/sql table
2263 FOR i in 1..l_trx_flow_tbl.COUNT LOOP
2264 IF (l_debug = 1) THEN
2265 print_debug('index of l_trx_flow_tbl = ' || i, 9);
2266 print_debug('from_org_id = ' || l_trx_flow_tbl(i).from_org_id, 9);
2267 print_debug('to_org_id = ' || l_trx_flow_tbl(i).to_org_id, 9);
2268 print_debug('from_organization_id = ' || l_trx_flow_tbl(i).from_organization_id, 9);
2269 print_debug('transaction_uom = ' || l_mtl_trx_tbl(1).transaction_uom, 9);
2270 print_debug('inventory_item_id = ' || l_mtl_trx_tbl(1).inventory_item_id, 9);
2271 print_debug('l_trx_id = ' || l_trx_id, 9);
2272 print_debug('l_drop_ship_flag = ' || l_drop_ship_flag, 9);
2273 END IF;
2274
2275 l_transfer_price_tbl(i).from_org_id := l_trx_flow_tbl(i).from_org_id;
2276 l_transfer_price_tbl(i).to_org_id := l_trx_flow_tbl(i).to_org_id;
2277 l_progress := 330;
2278 INV_TRANSACTION_FLOW_PUB.get_transfer_price(
2279 x_return_status => l_return_status
2280 , x_msg_data => l_msg_data
2281 , x_msg_count => l_msg_count
2282 , x_transfer_price => l_transfer_price_tbl(i).transfer_price
2283 , x_currency_code => l_transfer_price_tbl(i).functional_currency_code
2284 , x_incr_transfer_price => l_transfer_price_tbl(i).incr_transfer_price
2285 , x_incr_currency_code => l_transfer_price_tbl(i).incr_currency_code
2286 , p_api_version => 1.0
2287 , p_init_msg_list => fnd_api.g_false
2288 , p_from_org_id => l_transfer_price_tbl(i).from_org_id
2289 , p_to_org_id => l_transfer_price_tbl(i).to_org_id
2290 , p_transaction_uom => l_mtl_trx_tbl(1).transaction_uom
2291 , p_inventory_item_id => l_mtl_trx_tbl(1).inventory_item_id
2292 , p_transaction_id => l_trx_id
2293 , p_from_organization_id => l_trx_flow_tbl(i).from_organization_id
2294 , p_global_procurement_flag => 'N'
2295 , p_drop_ship_flag => l_drop_ship_flag);
2296
2297 IF (l_debug = 1) THEN
2298 print_debug('get_transfer_price returns status = ' || l_return_status, 9);
2299 END IF;
2300
2301 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2302 IF (l_debug = 1) THEN
2303 print_debug('get_transfer_price returns error: ' || l_msg_data, 9);
2304 END IF;
2305 RAISE FND_API.G_EXC_ERROR;
2306 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2307 IF (l_debug = 1) THEN
2308 print_debug('get_transfer_price returns unexpected error: ' || l_msg_data, 9);
2309 END IF;
2310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2311 END IF;
2312 END LOOP;
2313
2314 IF (l_debug = 1) THEN
2315 print_debug('Transfer price pl/sql table count = ' || l_transfer_price_tbl.COUNT, 9);
2316 FOR i in 1..l_transfer_price_tbl.COUNT LOOP
2317 print_debug('from_org_id=' || l_transfer_price_tbl(i).from_org_id ||
2318 ' to_org_id=' || l_transfer_price_tbl(i).to_org_id ||
2319 ' transfer_price=' || l_transfer_price_tbl(i).transfer_price ||
2320 ' func_curr_code=' || l_transfer_price_tbl(i).functional_currency_code ||
2321 ' incr_transfer_price=' || l_transfer_price_tbl(i).incr_transfer_price ||
2322 ' incr_currency_code=' || l_transfer_price_tbl(i).incr_currency_code, 9);
2323 END LOOP;
2324 END IF;
2325 END IF;
2326
2327 -- check if the item is inventory asset item or expense item, to decide
2328 -- which account should be used to populate the distribution account id
2329 -- for intercompany receipt record
2330 -- If it is inventory asset item, then use the inventory accrual account id
2331 -- else if it is expense item, use the expense accrual account id
2332 -- inventory_asset_flag = Y or N
2333 BEGIN
2334 l_progress := 340;
2335 SELECT inventory_asset_flag
2336 INTO l_inv_asset_flag
2337 FROM mtl_system_items
2338 WHERE organization_id = l_mtl_trx_tbl(1).organization_id
2339 AND inventory_item_id = l_mtl_trx_tbl(1).inventory_item_id;
2340 EXCEPTION
2341 WHEN no_data_found THEN
2342 IF (l_debug = 1) THEN
2343 print_debug('No item is found for item id: '
2344 || l_mtl_trx_tbl(1).inventory_item_id, 9);
2345 END IF;
2346 FND_MESSAGE.SET_NAME('INV', 'INV_NO_ITEM_FOUND');
2347 FND_MSG_PUB.ADD;
2348 RAISE FND_API.G_EXC_ERROR;
2349 END;
2350
2351 -- loop for each record of transaction flow table and construct the logical
2352 -- transaction table
2353 l_index := l_mtl_trx_tbl.COUNT;
2354 FOR i in 1..l_trx_flow_tbl.COUNT LOOP
2355 l_index := l_index + 1;
2356
2357 IF (l_trx_flow_tbl(i).start_org_id = l_trx_flow_tbl(i).from_org_id) THEN
2358 -- this is first node to intermediate node
2359 -- create a logical I/C sales issue of from_org_id and
2360 -- a logical I/C receipt of to_org_id
2361 -- OR a logical I/C sales return into from_org_id for RMA and
2362 -- a logical I/C receipt return of the to_org_id
2363
2364 -- start to construct the logical I/C sales issue of from_org_id
2365 -- or logical I/C sales return into from_org_id
2366 IF (l_debug = 1) THEN
2367 print_debug('First node', 9);
2368 print_debug('Construct the logical I/C sales issue/return of from_org_id', 9);
2369 print_debug('l_index for l_mtl_trx_tbl = ' || l_index, 9);
2370 END IF;
2371
2372 l_mtl_trx_tbl(l_index).organization_id := l_mtl_trx_tbl(1).organization_id;
2373 l_mtl_trx_tbl(l_index).transfer_organization_id := l_trx_flow_tbl(i).to_organization_id;
2374 l_mtl_trx_tbl(l_index).subinventory_code := l_mtl_trx_tbl(1).subinventory_code;
2375 l_mtl_trx_tbl(l_index).locator_id := l_mtl_trx_tbl(1).locator_id;
2376 l_mtl_trx_tbl(l_index).project_id := l_mtl_trx_tbl(1).project_id;
2377 l_mtl_trx_tbl(l_index).task_id := l_mtl_trx_tbl(1).task_id;
2378 l_mtl_trx_tbl(l_index).transfer_cost_group_id := l_trx_flow_tbl(i).to_org_cost_group_id;
2379 l_mtl_trx_tbl(l_index).intercompany_cost := l_transfer_price_tbl(i).incr_transfer_price;
2380 l_mtl_trx_tbl(l_index).intercompany_currency_code := l_transfer_price_tbl(i).incr_currency_code;
2381 l_mtl_trx_tbl(l_index).currency_code := l_transfer_price_tbl(i).functional_currency_code;
2382 l_mtl_trx_tbl(l_index).invoiced_flag := 'N';
2383 l_mtl_trx_tbl(l_index).pm_cost_collected := null;
2384 l_mtl_trx_tbl(l_index).transaction_date := NVL(l_mtl_trx_tbl(1).transaction_date,sysdate);
2385 l_mtl_trx_tbl(l_index).acct_period_id := l_mtl_trx_tbl(l_index-1).acct_period_id;
2386 l_mtl_trx_tbl(l_index).distribution_account_id :=
2387 l_trx_flow_tbl(i).INTERCOMPANY_COGS_ACCOUNT_ID;
2388 l_mtl_trx_tbl(l_index).trx_source_line_id :=
2389 l_mtl_trx_tbl(1).trx_source_line_id;
2390 l_mtl_trx_tbl(l_index).transaction_source_id :=
2391 l_mtl_trx_tbl(1).transaction_source_id;
2392 IF (l_debug = 1) THEN
2393 print_debug('******transaction_source_id: ******' ||
2394 l_mtl_trx_tbl(l_index).transaction_source_id);
2395 print_debug('******index: ******' ||l_index);
2396
2397 END IF;
2398
2399 IF (p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE) THEN
2400 -- SO issue/RMA
2401 l_mtl_trx_tbl(l_index).transaction_quantity := l_mtl_trx_tbl(1).transaction_quantity;
2402 l_mtl_trx_tbl(l_index).primary_quantity := l_mtl_trx_tbl(1).primary_quantity;
2403 l_mtl_trx_tbl(l_index).transaction_cost := null;
2404 l_mtl_trx_tbl(l_index).cost_group_id := l_mtl_trx_tbl(1).cost_group_id;
2405 l_mtl_trx_tbl(l_index).parent_transaction_id := l_mtl_trx_tbl(1).transaction_id;
2406 l_mtl_trx_tbl(l_index).transaction_batch_id := l_mtl_trx_tbl(1).transaction_id;
2407
2408 IF (l_is_return = 1) THEN
2409 l_mtl_trx_tbl(l_index).pm_cost_collected := null;
2410 END IF;
2411 ELSIF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER) THEN
2412 -- true trop ship
2413 l_mtl_trx_tbl(l_index).transaction_quantity := -1*l_mtl_trx_tbl(1).transaction_quantity;
2414 l_mtl_trx_tbl(l_index).primary_quantity := -1*l_mtl_trx_tbl(1).primary_quantity;
2415 l_mtl_trx_tbl(l_index).transaction_cost := l_mtl_trx_tbl(1).transaction_cost;
2416 l_mtl_trx_tbl(l_index).cost_group_id := l_mtl_trx_tbl(1).cost_group_id;
2417 END IF;
2418
2419 IF (l_is_return = 0) THEN -- construct logical I/C sales issue
2420 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_INVENTORY;
2421 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_IC_SALES_ISSUE;
2422 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALICSALES;
2423 ELSE -- it's RMA, construct logical I/C sales return
2424 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_INVENTORY;
2425 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_IC_SALES_RETURN;
2426 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALICSALESRETURN;
2427 END IF;
2428 -- end of construct the logical I/C sales issue of from_org_id
2429 -- or logical I/C sales return into from_rog_id
2430
2431 -- start to construct the logical I/C receipt of the to_org_id
2432 l_index := l_index + 1;
2433 IF (l_debug = 1) THEN
2434 print_debug('First node', 9);
2435 print_debug('Construct the logical I/C receipt/sales return of from_org_id', 9);
2436 print_debug('l_index for l_mtl_trx_tbl = ' || l_index, 9);
2437 END IF;
2438
2439 l_mtl_trx_tbl(l_index).organization_id := l_trx_flow_tbl(i).to_organization_id;
2440 l_mtl_trx_tbl(l_index).transfer_organization_id := l_trx_flow_tbl(i).from_organization_id;
2441 l_mtl_trx_tbl(l_index).transaction_date := NVL(l_mtl_trx_tbl(1).transaction_date,sysdate);
2442 l_progress := 350;
2443 get_acct_period(
2444 x_return_status => l_return_status
2445 , x_msg_count => l_msg_count
2446 , x_msg_data => l_msg_data
2447 , x_acct_period_id => l_mtl_trx_tbl(l_index).acct_period_id
2448 , p_organization_id => l_mtl_trx_tbl(l_index).organization_id
2449 , p_transaction_date => l_mtl_trx_tbl(l_index).transaction_date);
2450 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
2451 IF (l_debug = 1) THEN
2452 print_debug('get_acct_period returns error with org id = '
2453 || l_mtl_trx_tbl(1).organization_id, 9);
2454 print_debug('x_msg_data = ' || x_msg_data, 9);
2455 END IF;
2456 FND_MESSAGE.SET_NAME('INV', 'INV_PERIOD_RETRIEVAL_ERROR');
2457 FND_MSG_PUB.ADD;
2458 RAISE FND_API.G_EXC_ERROR;
2459 END IF;
2460
2461 IF (l_inv_asset_flag = 'Y') THEN
2462 l_mtl_trx_tbl(l_index).distribution_account_id := l_trx_flow_tbl(i).INVENTORY_ACCRUAL_ACCOUNT_ID;
2463 ELSE
2464 l_mtl_trx_tbl(l_index).distribution_account_id := l_trx_flow_tbl(i).EXPENSE_ACCRUAL_ACCOUNT_ID;
2465 END IF;
2466
2467 IF (l_debug = 1) THEN
2468 print_debug('Calling INV_TRANSACTION_FLOW_PUB.convert_currency, input params:', 9);
2469 print_debug('1. p_org_id = ' || l_transfer_price_tbl(i).to_org_id, 9);
2470 print_debug('2. p_transfer_price = ' || l_mtl_trx_tbl(l_index-1).intercompany_cost, 9);
2471 print_debug('3. p_currency_code = ' || l_mtl_trx_tbl(l_index-1).currency_code, 9);
2472 END IF;
2473
2474 /*Start of Bug: 5554106. */
2475 BEGIN
2476 SELECT primary_uom_code
2477 INTO l_primary_uom
2478 FROM mtl_system_items
2479 WHERE organization_id = l_trx_flow_tbl(i).to_organization_id
2480 AND inventory_item_id = l_mtl_trx_tbl(1).inventory_item_id;
2481 print_debug('Primary UOM is: '||l_primary_uom, 9);
2482 EXCEPTION
2483 WHEN NO_DATA_FOUND THEN
2484 print_debug('Item not found for item id: '|| l_mtl_trx_tbl(1).inventory_item_id, 9);
2485 FND_MESSAGE.SET_NAME('INV', 'INV_NO_ITEM_FOUND');
2486 FND_MSG_PUB.ADD;
2487 RAISE FND_API.G_EXC_ERROR;
2488 END;
2489
2490 print_debug('Transaction UOM is: '||p_mtl_trx_tbl(1).transaction_uom, 9);
2491
2492
2493 IF p_mtl_trx_tbl(1).transaction_uom <> l_primary_uom THEN
2494 INV_CONVERT.inv_um_conversion(
2495 from_unit => l_primary_uom
2496 , to_unit => p_mtl_trx_tbl(1).transaction_uom
2497 , item_id => l_mtl_trx_tbl(1).inventory_item_id
2498 , uom_rate => l_uom_rate
2499 );
2500
2501 -- Converting the transaction quantity to primary UOMs quantity when transaction UOM is not same as Primary UOM.
2502 print_debug('Transaction UOM is NOT same as Primary UOM.', 9);
2503 print_debug('Calling inv_convert.inv_um_convert....', 9);
2504 l_mtl_trx_tbl(l_index).primary_quantity := -1*inv_convert.inv_um_convert(l_mtl_trx_tbl(1).inventory_item_id,6
2505 ,l_mtl_trx_tbl(l_index-1).transaction_quantity
2506 ,p_mtl_trx_tbl(1).transaction_uom
2507 ,l_primary_uom
2508 ,'','');
2509 ELSE
2510 print_debug('Transaction UOM is same as Primary UOM.', 9);
2511 l_mtl_trx_tbl(l_index).primary_quantity := -1*l_mtl_trx_tbl(l_index-1).primary_quantity;
2512 END IF;
2513 print_debug('Primary Quantity: '||l_mtl_trx_tbl(l_index).primary_quantity, 9);
2514 /*End of bug: 5554106. */
2515
2516 print_debug('l_uom_rate: '||l_uom_rate, 9);
2517 l_progress := 360;
2518 /* Modified the Transaction_cost calculation using intercompany transfer price and currency code for bug 12693744 */
2519 /* l_mtl_trx_tbl(l_index).transaction_cost := l_uom_rate * INV_TRANSACTION_FLOW_PUB.convert_currency(
2520 p_org_id => l_transfer_price_tbl(i).to_org_id
2521 , p_transfer_price => l_transfer_price_tbl(i).transfer_price
2522 , p_currency_code => l_mtl_trx_tbl(l_index-1).currency_code
2523 , p_transaction_date => l_mtl_trx_tbl(l_index).transaction_date
2524 , p_logical_txn => 'Y' /* bug 6696446
2525 , x_functional_currency_code => l_mtl_trx_tbl(l_index).currency_code
2526 , x_return_status => l_return_status
2527 , x_msg_data => l_msg_data
2528 , x_msg_count => l_msg_count);*/
2529
2530 l_mtl_trx_tbl(l_index).transaction_cost := l_uom_rate * INV_TRANSACTION_FLOW_PUB.convert_currency(
2531 p_org_id => l_transfer_price_tbl(i).to_org_id
2532 , p_transfer_price => l_transfer_price_tbl(i).incr_transfer_price
2533 , p_currency_code => l_transfer_price_tbl(i).incr_currency_code
2534 , p_transaction_date => l_mtl_trx_tbl(l_index).transaction_date
2535 , p_logical_txn => 'Y' /* bug 6696446 */
2536 , x_functional_currency_code => l_mtl_trx_tbl(l_index).currency_code
2537 , x_return_status => l_return_status
2538 , x_msg_data => l_msg_data
2539 , x_msg_count => l_msg_count);
2540
2541
2542 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2543 IF (l_debug = 1) THEN
2544 print_debug('l_return_status = ' || l_return_status, 9);
2545 print_debug('l_msg_data = ' || l_msg_data, 9);
2546 RAISE FND_API.G_EXC_ERROR;
2547 END IF;
2548 END IF;
2549 -- Commented following for bug 6696446
2550 -- l_mtl_trx_tbl(l_index).transaction_cost := round(l_mtl_trx_tbl(l_index).transaction_cost,2);
2551 print_debug('transaction_cost: '||l_mtl_trx_tbl(l_index).transaction_cost, 9);
2552
2553 l_mtl_trx_tbl(l_index).intercompany_cost := l_mtl_trx_tbl(l_index-1).intercompany_cost;
2554 l_mtl_trx_tbl(l_index).intercompany_currency_code := l_mtl_trx_tbl(l_index-1).intercompany_currency_code;
2555 l_mtl_trx_tbl(l_index).invoiced_flag := 'N';
2556 l_mtl_trx_tbl(l_index).pm_cost_collected := null;
2557 l_mtl_trx_tbl(l_index).cost_group_id := l_trx_flow_tbl(i).to_org_cost_group_id;
2558 l_mtl_trx_tbl(l_index).transfer_cost_group_id := l_mtl_trx_tbl(l_index-1).cost_group_id;
2559
2560 l_mtl_trx_tbl(l_index).transaction_quantity := -1*l_mtl_trx_tbl(l_index-1).transaction_quantity;
2561
2562
2563 IF (p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE) THEN
2564 -- SO issue/RMA
2565 l_mtl_trx_tbl(l_index).parent_transaction_id := l_mtl_trx_tbl(1).transaction_id;
2566 l_mtl_trx_tbl(l_index).transaction_batch_id := l_mtl_trx_tbl(1).transaction_id;
2567 END IF;
2568
2569 IF (l_is_return = 0) THEN -- construct logical I/C receipt of to_org_id
2570 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_INVENTORY;
2571 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_IC_SHIP_RECEIPT;
2572 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALICRECEIPT;
2573 ELSE -- it's RMA, construct logical I/C receipt return
2574 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_INVENTORY;
2575 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_IC_RECEIPT_RETURN;
2576 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALICRCPTRETURN;
2577 END IF;
2578 -- END of construct the logical I/C receipt or logical I/C receipt
2579 -- return of to_org_id
2580
2581 ELSE -- (l_trx_flow_tbl(i).start_org_id <> l_trx_flow_tbl(i).from_org_id) THEN
2582 -- this is intermediate node to intermediate node
2583 -- OR intermediate node to last node
2584 -- create a logical I/C sales issue of the from_org_id and a
2585 -- logical I/C receipt of the to_org_id,
2586 -- if the to_org_id is the last node, also create a logical sales
2587 -- order issue from to_org_id
2588 -- OR for RMA, create a logical I/C sales return of from_org_id
2589 -- and a logical I/C receipt return of the to_org_id.
2590 -- if the to_org_id is the last node, also create a logical
2591 -- RMA receipt into to_org_id
2592
2593 -- start to construct the logical I/C sales issue of the from_org_id
2594 -- or logical I/C sales return into from_org_id
2595 IF (l_debug = 1) THEN
2596 print_debug('Intermediate node', 9);
2597 print_debug('Construct the logical I/C sales issue/return of from_org_id', 9);
2598 print_debug('l_index for l_mtl_trx_tbl = ' || l_index, 9);
2599 END IF;
2600
2601 l_mtl_trx_tbl(l_index).organization_id := l_trx_flow_tbl(i).from_organization_id;
2602 l_mtl_trx_tbl(l_index).transfer_organization_id := l_trx_flow_tbl(i).to_organization_id;
2603 l_mtl_trx_tbl(l_index).acct_period_id := l_mtl_trx_tbl(l_index-1).acct_period_id;
2604 l_mtl_trx_tbl(l_index).distribution_account_id := l_trx_flow_tbl(i).INTERCOMPANY_COGS_ACCOUNT_ID;
2605 l_mtl_trx_tbl(l_index).currency_code := l_transfer_price_tbl(i).functional_currency_code;
2606 l_mtl_trx_tbl(l_index).transaction_cost := l_mtl_trx_tbl(l_index-1).transaction_cost;
2607 l_mtl_trx_tbl(l_index).intercompany_cost := l_transfer_price_tbl(i).incr_transfer_price;
2608 l_mtl_trx_tbl(l_index).intercompany_currency_code := l_transfer_price_tbl(i).incr_currency_code;
2609 l_mtl_trx_tbl(l_index).invoiced_flag := 'N';
2610 l_mtl_trx_tbl(l_index).pm_cost_collected := null;
2611 l_mtl_trx_tbl(l_index).cost_group_id := l_trx_flow_tbl(i).from_org_cost_group_id;
2612 l_mtl_trx_tbl(l_index).transfer_cost_group_id := l_trx_flow_tbl(i).to_org_cost_group_id;
2613 l_mtl_trx_tbl(l_index).transaction_quantity := -1*l_mtl_trx_tbl(l_index-1).transaction_quantity;
2614 l_mtl_trx_tbl(l_index).primary_quantity := -1*l_mtl_trx_tbl(l_index-1).primary_quantity;
2615 l_mtl_trx_tbl(l_index).transaction_date := NVL(l_mtl_trx_tbl(1).transaction_date,sysdate);
2616
2617 IF (p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE) THEN
2618 l_mtl_trx_tbl(l_index).parent_transaction_id := l_mtl_trx_tbl(1).transaction_id;
2619 l_mtl_trx_tbl(l_index).transaction_batch_id := l_mtl_trx_tbl(1).transaction_id;
2620 END IF;
2621
2622 IF (l_is_return = 0) THEN -- construct logical I/C sales issue of the from_org_id
2623 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_INVENTORY;
2624 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_IC_SALES_ISSUE;
2625 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALICSALES;
2626 ELSE -- it's RMA, construct logical I/C sales return
2627 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_TYPE_LOGL_IC_RECEIPT_RETURN;
2628 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_IC_SALES_RETURN;
2629 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALICSALESRETURN;
2630 END IF;
2631 -- end of construct the logical I/C sales issue of the from_org_id
2632 -- or logical I/C sales return into from_org_id
2633
2634 -- start to construct the logical I/C receipt of the to_org_id
2635 -- or logical I/C receipt return from the to_org_id
2636 l_index := l_index + 1;
2637 IF (l_debug = 1) THEN
2638 print_debug('Intermediate node', 9);
2639 print_debug('Construct the logical I/C receipt/receipt return of from_org_id', 9);
2640 print_debug('l_index for l_mtl_trx_tbl = ' || l_index, 9);
2641 END IF;
2642
2643 l_mtl_trx_tbl(l_index).organization_id := l_trx_flow_tbl(i).to_organization_id;
2644 l_mtl_trx_tbl(l_index).transfer_organization_id := l_trx_flow_tbl(i).from_organization_id;
2645 l_mtl_trx_tbl(l_index).transaction_date := NVL(l_mtl_trx_tbl(1).transaction_date,sysdate);
2646 l_progress := 370;
2647 get_acct_period(
2648 x_return_status => l_return_status
2649 , x_msg_count => l_msg_count
2650 , x_msg_data => l_msg_data
2651 , x_acct_period_id => l_mtl_trx_tbl(l_index).acct_period_id
2652 , p_organization_id => l_mtl_trx_tbl(l_index).organization_id
2653 , p_transaction_date => l_mtl_trx_tbl(l_index).transaction_date);
2654 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
2655 IF (l_debug = 1) THEN
2656 print_debug('get_acct_period returns error with org id = '
2657 || l_mtl_trx_tbl(1).organization_id, 9);
2658 print_debug('x_msg_data = ' || x_msg_data, 9);
2659 END IF;
2660 FND_MESSAGE.SET_NAME('INV', 'INV_PERIOD_RETRIEVAL_ERROR');
2661 FND_MSG_PUB.ADD;
2662 RAISE FND_API.G_EXC_ERROR;
2663 END IF;
2664
2665 IF (l_inv_asset_flag = 'Y') THEN
2666 l_mtl_trx_tbl(l_index).distribution_account_id := l_trx_flow_tbl(i).INVENTORY_ACCRUAL_ACCOUNT_ID;
2667 ELSE
2668 l_mtl_trx_tbl(l_index).distribution_account_id := l_trx_flow_tbl(i).EXPENSE_ACCRUAL_ACCOUNT_ID;
2669 END IF;
2670
2671
2672
2673 /*Start of Bug: 5554106. */
2674 print_debug('Checking Transaction UOM and Primary UOM.', 9);
2675 BEGIN
2676 SELECT primary_uom_code
2677 INTO l_primary_uom
2678 FROM mtl_system_items
2679 WHERE organization_id = l_trx_flow_tbl(i).to_organization_id
2680 AND inventory_item_id = l_mtl_trx_tbl(1).inventory_item_id;
2681 print_debug('Primary UOM for '||l_trx_flow_tbl(i).to_organization_id||' is: '||l_primary_uom, 9);
2682 EXCEPTION
2683 WHEN NO_DATA_FOUND THEN
2684 print_debug('Item not found for item id: '|| l_mtl_trx_tbl(1).inventory_item_id, 9);
2685 FND_MESSAGE.SET_NAME('INV', 'INV_NO_ITEM_FOUND');
2686 FND_MSG_PUB.ADD;
2687 RAISE FND_API.G_EXC_ERROR;
2688 END;
2689 l_uom_rate := 1;
2690
2691 print_debug('Transaction UOM is: '||p_mtl_trx_tbl(1).transaction_uom, 9);
2692
2693 IF p_mtl_trx_tbl(1).transaction_uom <> l_primary_uom THEN
2694 INV_CONVERT.inv_um_conversion(
2695 from_unit => l_primary_uom
2696 , to_unit => p_mtl_trx_tbl(1).transaction_uom
2697 , item_id => l_mtl_trx_tbl(1).inventory_item_id
2698 , uom_rate => l_uom_rate
2699 );
2700
2701 -- Converting the transaction quantity to primary UOMs quantity when transaction UOM is not same as Primary UOM.
2702 print_debug('Transaction UOM is NOT same as Primary UOM.', 9);
2703 print_debug('Calling inv_convert.inv_um_convert....', 9);
2704 l_mtl_trx_tbl(l_index).primary_quantity := -1*inv_convert.inv_um_convert(l_mtl_trx_tbl(1).inventory_item_id,6
2705 ,l_mtl_trx_tbl(l_index-1).transaction_quantity
2706 ,p_mtl_trx_tbl(1).transaction_uom
2707 ,l_primary_uom
2708 ,'','');
2709 ELSE
2710 print_debug('Transaction UOM is same as Primary UOM.', 9);
2711 l_mtl_trx_tbl(l_index).primary_quantity := -1*l_mtl_trx_tbl(l_index-1).primary_quantity;
2712 END IF;
2713 print_debug('Primary Quantity: '||l_mtl_trx_tbl(l_index).primary_quantity, 9);
2714 /*End of bug: 5554106. */
2715
2716 print_debug('l_uom_rate: '||l_uom_rate, 9);
2717
2718 l_progress := 380;
2719 /* Modified the Transaction_cost calculation using intercompany transfer price and currency code for bug 12693744 */
2720 /* l_mtl_trx_tbl(l_index).transaction_cost := l_uom_rate * INV_TRANSACTION_FLOW_PUB.convert_currency(
2721 p_org_id => l_transfer_price_tbl(i).to_org_id
2722 , p_transfer_price => l_transfer_price_tbl(i).transfer_price
2723 , p_currency_code => l_mtl_trx_tbl(l_index-1).currency_code
2724 , p_transaction_date => l_mtl_trx_tbl(l_index).transaction_date
2725 , p_logical_txn => 'Y' bug 6696446
2726 , x_functional_currency_code => l_mtl_trx_tbl(l_index).currency_code
2727 , x_return_status => l_return_status
2728 , x_msg_data => l_msg_data
2729 , x_msg_count => l_msg_count); */
2730
2731 l_mtl_trx_tbl(l_index).transaction_cost := l_uom_rate * INV_TRANSACTION_FLOW_PUB.convert_currency(
2732 p_org_id => l_transfer_price_tbl(i).to_org_id
2733 , p_transfer_price => l_transfer_price_tbl(i).incr_transfer_price
2734 , p_currency_code => l_transfer_price_tbl(i).incr_currency_code
2735 , p_transaction_date => l_mtl_trx_tbl(l_index).transaction_date
2736 , p_logical_txn => 'Y' /* bug 6696446 */
2737 , x_functional_currency_code => l_mtl_trx_tbl(l_index).currency_code
2738 , x_return_status => l_return_status
2739 , x_msg_data => l_msg_data
2740 , x_msg_count => l_msg_count);
2741
2742 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2743 IF (l_debug = 1) THEN
2744 print_debug('l_return_status = ' || l_return_status, 9);
2745 print_debug('l_msg_data = ' || l_msg_data, 9);
2746 RAISE FND_API.G_EXC_ERROR;
2747 END IF;
2748 END IF;
2749
2750 -- commented following for bug 6696446
2751 -- l_mtl_trx_tbl(l_index).transaction_cost := round(l_mtl_trx_tbl(l_index).transaction_cost,2);
2752
2753 print_debug('transaction_cost: '||l_mtl_trx_tbl(l_index).transaction_cost, 9);
2754
2755 l_mtl_trx_tbl(l_index).intercompany_cost := l_mtl_trx_tbl(l_index-1).intercompany_cost;
2756 l_mtl_trx_tbl(l_index).intercompany_currency_code := l_mtl_trx_tbl(l_index-1).intercompany_currency_code;
2757 l_mtl_trx_tbl(l_index).invoiced_flag := 'N';
2758 l_mtl_trx_tbl(l_index).pm_cost_collected := null;
2759 l_mtl_trx_tbl(l_index).cost_group_id := l_trx_flow_tbl(i).to_org_cost_group_id;
2760 l_mtl_trx_tbl(l_index).transfer_cost_group_id := l_trx_flow_tbl(i).from_org_cost_group_id;
2761 l_mtl_trx_tbl(l_index).transaction_quantity := -1*l_mtl_trx_tbl(l_index-1).transaction_quantity;
2762
2763 IF (p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE) THEN
2764 -- SO issue/RMA
2765 l_mtl_trx_tbl(l_index).parent_transaction_id := l_mtl_trx_tbl(1).transaction_id;
2766 l_mtl_trx_tbl(l_index).transaction_batch_id := l_mtl_trx_tbl(1).transaction_id;
2767 END IF;
2768
2769 IF (l_is_return = 0) THEN -- construct logical I/C receipt of from_org_id
2770 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_INVENTORY;
2771 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_IC_SHIP_RECEIPT;
2772 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALICRECEIPT;
2773 ELSE -- it's RMA, construct logical I/C receipt return
2774 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_INVENTORY;
2775 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_IC_RECEIPT_RETURN;
2776 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALICRCPTRETURN;
2777 END IF;
2778 -- END of construct the logical I/C receipt or logical I/C receipt
2779 -- return of to_org_id
2780 END IF; -- end of IF (l_trx_flow_tbl(i).start_org_id = l_trx_flow_tbl(i).from_org_id)
2781
2782 -- if it's the end node, also construct the logical sales order issue
2783 -- of to_org_id or logical RMA receipt of to_org_id
2784 IF (l_trx_flow_tbl(i).end_org_id = l_trx_flow_tbl(i).to_org_id) THEN
2785 l_index := l_index + 1;
2786 IF (l_debug = 1) THEN
2787 print_debug('End node', 9);
2788 print_debug('Construct the logical sales issue/RMA receipt of from_org_id', 9);
2789 print_debug('l_index for l_mtl_trx_tbl = ' || l_index, 9);
2790 END IF;
2791
2792 l_mtl_trx_tbl(l_index).organization_id := l_trx_flow_tbl(i).to_organization_id;
2793 l_mtl_trx_tbl(l_index).transfer_organization_id := null;
2794 l_mtl_trx_tbl(l_index).acct_period_id := l_mtl_trx_tbl(l_index-1).acct_period_id;
2795
2796 l_progress := 390;
2797
2798 get_cogs_acct_for_logical_so(
2799 x_return_status => l_return_status
2800 , x_msg_count => l_msg_count
2801 , x_msg_data => l_msg_data
2802 , x_cogs_acct_id => l_mtl_trx_tbl(l_index).distribution_account_id
2803 , p_inventory_item_id => l_mtl_trx_tbl(1).inventory_item_id
2804 , p_order_line_id => l_mtl_trx_tbl(1).trx_source_line_id
2805 , p_ic_to_inv_organization_id => l_mtl_trx_tbl(l_index).organization_id); -- Bug: 4607049.
2806 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
2807 IF (l_debug = 1) THEN
2808 print_debug('get_cogs_acct_for_logical_so returns error', 9);
2809 print_debug('l_msg_data = ' || l_msg_data, 9);
2810 END IF;
2811 FND_MESSAGE.SET_NAME('INV', 'INV_NO_COGS_FOR_LOG_SO');
2812 FND_MSG_PUB.ADD;
2813 RAISE FND_API.G_EXC_ERROR;
2814 END IF;
2815
2816 l_mtl_trx_tbl(l_index).currency_code := null;
2817 l_mtl_trx_tbl(l_index).transaction_cost := l_mtl_trx_tbl(l_index-1).transaction_cost;
2818 l_mtl_trx_tbl(l_index).intercompany_cost := null;
2819 l_mtl_trx_tbl(l_index).intercompany_currency_code := null;
2820 l_mtl_trx_tbl(l_index).invoiced_flag := null;
2821 l_mtl_trx_tbl(l_index).pm_cost_collected := null;
2822 l_mtl_trx_tbl(l_index).cost_group_id := l_mtl_trx_tbl(l_index-1).cost_group_id;
2823 l_mtl_trx_tbl(l_index).transaction_quantity := -1*l_mtl_trx_tbl(l_index-1).transaction_quantity;
2824 l_mtl_trx_tbl(l_index).primary_quantity := -1*l_mtl_trx_tbl(l_index-1).primary_quantity;
2825 l_mtl_trx_tbl(l_index).transaction_date := NVL(l_mtl_trx_tbl(1).transaction_date,sysdate);
2826
2827 IF (p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE) THEN
2828 l_mtl_trx_tbl(l_index).parent_transaction_id := l_mtl_trx_tbl(1).transaction_id;
2829 l_mtl_trx_tbl(l_index).transaction_batch_id := l_mtl_trx_tbl(1).transaction_id;
2830 END IF;
2831
2832 IF (l_is_return = 0) THEN
2833 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_SALESORDER;
2834 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_SALES_ORDER_ISSUE;
2835 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALISSUE;
2836 ELSE -- it's RMA, construct logical RMA receipt
2837 l_mtl_trx_tbl(l_index).transaction_source_type_id := G_SOURCETYPE_RMA;
2838 l_mtl_trx_tbl(l_index).transaction_type_id := G_TYPE_LOGL_RMA_RECEIPT;
2839 l_mtl_trx_tbl(l_index).transaction_action_id := G_ACTION_LOGICALRECEIPT;
2840 END IF;
2841 END IF;
2842 -- end of constrct the logical sales order issue or the logical RMA
2843 -- receipt of the to_org_id
2844 END LOOP;
2845
2846 IF (p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE
2847 OR p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER) THEN
2848 -- For sales order issue or RMA, we don't want to insert the first record
2849 -- which is physical record for RMA or SO issue or
2850 -- logical record for dropship deliver already inserted into MMT.
2851 IF (l_is_return = 0) THEN
2852 l_index := 0;
2853 FOR i in 2..l_mtl_trx_tbl.COUNT LOOP
2854 l_index := l_index + 1;
2855 l_mtl_trx_tbl_temp(l_index) := l_mtl_trx_tbl(i);
2856 END LOOP;
2857 l_mtl_trx_tbl := l_mtl_trx_tbl_temp;
2858 ELSE
2859 -- IF the transaction is return transaction (RMA), reserve the order
2860 -- of the records in the pl/sql table
2861 l_index := 0;
2862 FOR i in REVERSE 2..l_mtl_trx_tbl.COUNT LOOP
2863 l_index := l_index + 1;
2864 l_mtl_trx_tbl_temp(l_index) := l_mtl_trx_tbl(i);
2865 END LOOP;
2866 l_mtl_trx_tbl := l_mtl_trx_tbl_temp;
2867 END IF;
2868
2869 END IF;
2870
2871 -- populate the transaction id of the records
2872 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
2873 -- populate transaction id
2874 l_progress := 400;
2875 SELECT mtl_material_transactions_s.nextval
2876 INTO l_mtl_trx_tbl(i).transaction_id
2877 FROM dual;
2878
2879 -- populate the columns which has same value for all types of records
2880 l_mtl_trx_tbl(i).inventory_item_id := p_mtl_trx_tbl(1).inventory_item_id;
2881 l_mtl_trx_tbl(i).revision := p_mtl_trx_tbl(1).revision;
2882 l_mtl_trx_tbl(i).transaction_uom := p_mtl_trx_tbl(1).transaction_uom;
2883 l_mtl_trx_tbl(i).source_line_id := p_mtl_trx_tbl(1).source_line_id;
2884 l_mtl_trx_tbl(i).rcv_transaction_id := p_mtl_trx_tbl(1).rcv_transaction_id;
2885 l_mtl_trx_tbl(i).trx_flow_header_id := p_mtl_trx_tbl(1).trx_flow_header_id;
2886 l_mtl_trx_tbl(i).lpn_id := p_mtl_trx_tbl(1).lpn_id;
2887 l_mtl_trx_tbl(i).costed_flag := 'N';
2888 l_mtl_trx_tbl(i).transaction_source_name := p_mtl_trx_tbl(1).transaction_source_name;
2889
2890 IF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER) THEN
2891 l_mtl_trx_tbl(i).transaction_batch_id := p_mtl_trx_tbl(1).transaction_id;
2892 l_mtl_trx_tbl(i).parent_transaction_id := p_mtl_trx_tbl(1).transaction_id;
2893 l_mtl_trx_tbl(i).transaction_source_id := l_mtl_trx_tbl(1).transaction_source_id;
2894 l_mtl_trx_tbl(i).trx_source_line_id := l_mtl_trx_tbl(1).trx_source_line_id;
2895
2896 IF (l_debug = 1) THEN
2897 print_debug('******transaction_source_id: ******' ||
2898 l_mtl_trx_tbl(i).transaction_source_id);
2899 print_debug('******i: ******' ||i);
2900 END IF;
2901 ELSE
2902 l_mtl_trx_tbl(i).transaction_source_id := p_mtl_trx_tbl(1).transaction_source_id;
2903 l_mtl_trx_tbl(i).trx_source_line_id := p_mtl_trx_tbl(1).trx_source_line_id;
2904 END IF;
2905
2906 l_mtl_trx_tbl(i).transaction_batch_seq := i;
2907 END LOOP;
2908 END IF; --end of (p_logical_trx_type_code=2 and shipping_ou<>selling_ou) or SO issue/RMA
2909 END IF; -- end of checking p_logical_trx_type_code
2910 -- ****** end of populating the logical transaction records of the pl/sql table *******
2911
2912 IF (l_debug = 1) THEN
2913 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
2914 print_debug('***** l_mtl_trx_tbl record ' || i, 9);
2915 print_debug('transaction_id: ' || l_mtl_trx_tbl(i).transaction_id, 9);
2916 print_debug('transaction_batch_id: ' || l_mtl_trx_tbl(i).transaction_batch_id, 9);
2917 print_debug('transaction_batch_seq: ' || l_mtl_trx_tbl(i).transaction_batch_seq, 9);
2918 print_debug('parent_transaction_id: ' || l_mtl_trx_tbl(i).parent_transaction_id, 9);
2919 print_debug('parent_transaction_flag: ' || l_mtl_trx_tbl(i).parent_transaction_flag, 9);
2920 print_debug('organization_id: ' || l_mtl_trx_tbl(i).organization_id, 9);
2921 print_debug('transfer_organization_id: ' || l_mtl_trx_tbl(i).transfer_organization_id, 9);
2922 print_debug('inventory_item_id: ' || l_mtl_trx_tbl(i).inventory_item_id, 9);
2923 print_debug('revision:' || l_mtl_trx_tbl(i).revision, 9);
2924 print_debug('transaction_type_id: ' || l_mtl_trx_tbl(i).transaction_type_id, 9);
2925 print_debug('transaction_action_id: ' || l_mtl_trx_tbl(i).transaction_action_id, 9);
2926 print_debug('transaction_source_type_id: ' || l_mtl_trx_tbl(i).transaction_source_type_id, 9);
2927 print_debug('transaction_source_id: ' || l_mtl_trx_tbl(i).transaction_source_id, 9);
2928 print_debug('transaction_source_name: ' || l_mtl_trx_tbl(i).transaction_source_name, 9);
2929 print_debug('transaction_quantity: ' || l_mtl_trx_tbl(i).transaction_quantity, 9);
2930 print_debug('transaction_uom: ' || l_mtl_trx_tbl(i).transaction_uom, 9);
2931 print_debug('primary_quantity: ' || l_mtl_trx_tbl(i).primary_quantity, 9);
2932 print_debug('transaction_cost: ' || l_mtl_trx_tbl(i).transaction_cost, 9);
2933 print_debug('intercompany_cost: ' || l_mtl_trx_tbl(i).intercompany_cost, 9);
2934 print_debug('cost_group_id: ' || l_mtl_trx_tbl(i).cost_group_id, 9);
2935 print_debug('transfer_cost_group_id: ' || l_mtl_trx_tbl(i).transfer_cost_group_id, 9);
2936 print_debug('trx_flow_header_id: ' || l_mtl_trx_tbl(i).trx_flow_header_id, 9);
2937 print_debug('invoiced_flag: ' || l_mtl_trx_tbl(i).invoiced_flag, 9);
2938 print_debug('pm_cost_collected: ' || l_mtl_trx_tbl(i).pm_cost_collected, 9);
2939 print_debug('acct_period_id: ' || l_mtl_trx_tbl(i).acct_period_id, 9);
2940 print_debug('distribution_account_id: ' || l_mtl_trx_tbl(i).distribution_account_id, 9);
2941 print_debug('transaction_source_id: ' || l_mtl_trx_tbl(i).transaction_source_id, 9);
2942 print_debug('trx_source_line_id: ' || l_mtl_trx_tbl(i).trx_source_line_id, 9);
2943 print_debug('source_line_id: ' || l_mtl_trx_tbl(i).source_line_id, 9);
2944 print_debug('rcv_transaction_id: ' || l_mtl_trx_tbl(i).rcv_transaction_id, 9);
2945 print_debug('lpn_id: ' || l_mtl_trx_tbl(i).lpn_id, 9);
2946 END LOOP;
2947 END IF;
2948
2949 -- For global procurement or drop shipment receipt, if the transaction type is populated
2950 -- but the transaction_action_id and transaction_source_type_id is null, populate
2951 -- the transaction_action_id and transaction_source_type_id
2952 IF (p_logical_trx_type_code = G_LOGTRXCODE_GLOBPROCRTV OR
2953 p_logical_trx_type_code = G_LOGTRXCODE_DSRECEIPT) THEN
2954 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
2955 IF (l_mtl_trx_tbl(i).transaction_action_id is null OR
2956 l_mtl_trx_tbl(i).transaction_source_type_id is null) THEN
2957 IF (l_mtl_trx_tbl(i).transaction_type_id is not null) THEN
2958 BEGIN
2959 l_progress := 410;
2960 SELECT transaction_action_id, transaction_source_type_id
2961 INTO l_mtl_trx_tbl(i).transaction_action_id, l_mtl_trx_tbl(i).transaction_source_type_id
2962 FROM mtl_transaction_types
2963 WHERE transaction_type_id = l_mtl_trx_tbl(i).transaction_type_id
2964 AND nvl(disable_date, sysdate+1) > sysdate;
2965 EXCEPTION
2966 WHEN no_data_found THEN
2967 IF (l_debug = 1) THEN
2968 print_debug('Transaction type not found', 9);
2969 END IF;
2970 FND_MESSAGE.SET_NAME('INV', 'INV_TRX_TYPE_ERROR');
2971 FND_MSG_PUB.ADD;
2972 RAISE FND_API.G_EXC_ERROR;
2973 END;
2974 ELSE
2975 IF (l_debug = 1) THEN
2976 print_debug('Trx action id, trx source type id or trx type is invalid', 9);
2977 END IF;
2978 FND_MESSAGE.SET_NAME('INV', 'INV_TRX_TYPE_ERROR');
2979 FND_MSG_PUB.ADD;
2980 RAISE FND_API.G_EXC_ERROR;
2981 END IF;
2982 END IF;
2983 END LOOP;
2984 END IF;
2985
2986
2987 IF (p_validation_flag = G_TRUE) THEN
2988 -- validate the transaction record
2989 IF (l_debug = 1) THEN
2990 print_debug('Calling INV_LOGICAL_TRANSACTIONS_PVT.validate_input_parameters', 9);
2991 END IF;
2992
2993 l_progress := 420;
2994 INV_LOGICAL_TRANSACTIONS_PVT.validate_input_parameters(
2995 x_return_status => l_return_status
2996 , x_msg_count => l_msg_count
2997 , x_msg_data => l_msg_data
2998 , p_mtl_trx_tbl => l_mtl_trx_tbl
2999 , p_validation_level => p_validation_flag
3000 , p_logical_trx_type_code => p_logical_trx_type_code);
3001
3002 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3003 IF (l_debug = 1) THEN
3004 print_debug('Validate_input_parameters returns error: ' || l_msg_data, 9);
3005 END IF;
3006 RAISE FND_API.G_EXC_ERROR;
3007 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3008 IF (l_debug = 1) THEN
3009 print_debug('Validate_input_parameters returns unexpected error: ' || l_msg_data, 9);
3010 END IF;
3011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3012 END IF;
3013 END IF;
3014
3015 -- validate_input_parameters returns success, call inv_mmt_insert to insert the
3016 -- record into mmt
3017 IF (l_debug = 1) THEN
3018 print_debug('Calling INV_LOGICAL_TRANSACTIONS_PVT.inv_mmt_insert', 9);
3019 END IF;
3020
3021 l_progress := 430;
3022 INV_LOGICAL_TRANSACTIONS_PVT.inv_mmt_insert(
3023 x_return_status => l_return_status
3024 , x_msg_count => l_msg_count
3025 , x_msg_data => l_msg_data
3026 , p_api_version_number => 1.0
3027 , p_init_msg_lst => fnd_api.g_false
3028 , p_mtl_trx_tbl => l_mtl_trx_tbl
3029 , p_logical_trx_type_code => p_logical_trx_type_code);
3030
3031 IF (l_debug = 1) THEN
3032 print_debug('After calling inv_mmt_insert, return status = ' || l_return_status, 9);
3033 END IF;
3034
3035 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3036 IF (l_debug = 1) THEN
3037 print_debug('inv_mmt_insert returns error: ' || l_msg_data, 9);
3038 END IF;
3039 RAISE FND_API.G_EXC_ERROR;
3040 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3041 IF (l_debug = 1) THEN
3042 print_debug('inv_mmt_insert returns unexpected error: ' || l_msg_data, 9);
3043 END IF;
3044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3045 END IF;
3046
3047 -- For dropship deliver, if the item is lot or serial controlled, we need to
3048 -- insert into mtl_transaction_lot_numbers or mtl_unit_transactions
3049 -- for the first logical intercompany sales issue record
3050 IF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER) THEN
3051 IF (l_lot_control_code = 2 or l_serial_control_code in (2, 5, 6)) THEN
3052 INV_LOGICAL_TRANSACTIONS_PVT.inv_lot_serial_insert
3053 (x_return_status => l_return_status,
3054 x_msg_count => l_msg_count,
3055 x_msg_data => l_msg_data,
3056 p_api_version_number => 1.0,
3057 p_init_msg_lst => fnd_api.g_false,
3058 p_parent_transaction_id => l_mtl_trx_tbl(1).parent_transaction_id,
3059 p_transaction_id => l_mtl_trx_tbl(1).transaction_id,
3060 p_lot_control_code => l_lot_control_code,
3061 p_serial_control_code => l_serial_control_code,
3062 p_organization_id => l_mtl_trx_tbl(1).organization_id,
3063 p_inventory_item_id => l_mtl_trx_tbl(1).inventory_item_id,
3064 p_primary_quantity => l_mtl_trx_tbl(1).primary_quantity,
3065 p_trx_source_type_id => l_mtl_trx_tbl(1).transaction_source_type_id,
3066 p_revision => l_mtl_trx_tbl(1).revision);
3067
3068 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3069 IF (l_debug = 1) THEN
3070 print_debug('inv_lot_serial_insert returns error: ' || l_msg_data, 9);
3071 END IF;
3072 RAISE FND_API.G_EXC_ERROR;
3073 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3074 IF (l_debug = 1) THEN
3075 print_debug('inv_lot_serial_insert returns unexpected error: ' || l_msg_data, 9);
3076 END IF;
3077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3078 END IF;
3079 END IF;
3080
3081 IF (l_mtl_trx_tbl(1).lpn_id IS NOT NULL) THEN
3082 --Bug 4411804: Removing direct updates to WLPN table.
3083 /*******
3084 UPDATE wms_license_plate_numbers
3085 SET lpn_context = 4
3086 WHERE organization_id = l_mtl_trx_tbl(1).organization_id
3087 AND lpn_id = l_mtl_trx_tbl(1).lpn_id;
3088
3089 IF (SQL%ROWCOUNT = 0) THEN
3090 IF (l_debug = 1) THEN
3091 print_debug('No wms_license_plate_number record is found for update with lpn_id'
3092 || l_mtl_trx_tbl(1).lpn_id ,9);
3093 END IF;
3094 FND_MESSAGE.SET_NAME('INV', 'INV_LPN_UPDATE_FAILURE');
3095 FND_MSG_PUB.ADD;
3096 RAISE FND_API.G_EXC_ERROR;
3097 END IF;
3098 ******/
3099 --Calling wms_container_pvt.Modify_LPN API to update the
3100 -- context.
3101 l_lpn.organization_id := l_mtl_trx_tbl(1).organization_id;
3102 l_lpn.lpn_id := l_mtl_trx_tbl(1).lpn_id;
3103 l_lpn.lpn_context := 4;
3104 wms_container_pvt.Modify_LPN
3105 (
3106 p_api_version => 1.0
3107 , p_init_msg_list => fnd_api.g_false
3108 , p_commit => fnd_api.g_false
3109 , p_validation_level => fnd_api.g_valid_level_full
3110 , x_return_status => l_return_status
3111 , x_msg_count => l_msg_count
3112 , x_msg_data => l_msg_data
3113 , p_lpn => l_lpn
3114 , p_caller => 'INV_LOGTXN'
3115 );
3116
3117 IF (l_return_status <> G_RET_STS_SUCCESS) THEN
3118 IF (l_debug = 1) THEN
3119 print_debug('Error from modify LPN API', 9);
3120 print_debug('l_msg_data = ' || l_msg_data, 9);
3121 END IF;
3122 FND_MESSAGE.SET_NAME('INV', 'INV_LPN_UPDATE_FAILURE');
3123 FND_MSG_PUB.ADD;
3124 RAISE FND_API.G_EXC_ERROR;
3125 END IF;
3126 END IF;
3127 END IF;
3128
3129 -- inv_mmt_insert returns success
3130 -- Loop through the records of pl/sql table, if transaction cost is not null,
3131 -- call costing API to insert into MTL_CST_TXN_COST_DETAILS
3132 IF (l_debug = 1) THEN
3133 print_debug('Calling MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row', 9);
3134 END IF;
3135
3136 IF (p_logical_trx_type_code = G_LOGTRXCODE_RETROPRICEUPD) THEN
3137 l_progress := 440;
3138 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
3139 --
3140 -- Bug: - umoogala 13-Feb-2006
3141 -- No bug is logged. But fixing as part of bug 5008080.
3142 -- Do NOT insert into MTL_CST_TXN_COST_DETAILS table for
3143 -- process mfg organizations.
3144 --
3145 IF l_prev_organization_id IS NULL OR
3146 p_mtl_trx_tbl(i).organization_id <> l_prev_organization_id
3147 THEN
3148 l_prev_organization_id := p_mtl_trx_tbl(i).organization_id;
3149
3150 SELECT NVL(process_enabled_flag, 'N')
3151 INTO l_process_enabled_flag
3152 FROM mtl_parameters
3153 WHERE organization_id = p_mtl_trx_tbl(i).organization_id;
3154 END IF;
3155
3156 IF (l_debug = 1) THEN
3157 print_debug('X_Transaction_Id = ' || l_mtl_trx_tbl(i).transaction_id, 9);
3158 print_debug('X_Organization_Id = ' || l_mtl_trx_tbl(i).organization_id, 9);
3159 print_debug('X_Last_Updated_By = ' || l_user_id, 9);
3160 print_debug('X_Inventory_Item_Id = ' || l_mtl_trx_tbl(i).inventory_item_id, 9);
3161 print_debug('X_Transaction_Cost = ' || l_mtl_trx_tbl(i).old_po_price, 9);
3162 print_debug('old po price = ' || l_mtl_trx_tbl(i).old_po_price, 9);
3163 print_debug('new po price = ' || l_mtl_trx_tbl(i).new_po_price, 9);
3164 print_debug('process_enabled_flag = ' || l_process_enabled_flag, 9);
3165 END IF;
3166
3167 IF l_process_enabled_flag = 'N'
3168 THEN
3169
3170 MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row(
3171 X_Rowid => l_row_id
3172 , X_Transaction_Id => l_mtl_trx_tbl(i).transaction_id
3173 , X_Organization_Id => l_mtl_trx_tbl(i).organization_id
3174 , X_Cost_Element_Id => 1
3175 , X_Level_Type => 1
3176 , X_Last_Update_Date => NVL(l_mtl_trx_tbl(i).transaction_date,sysdate)
3177 , X_Last_Updated_By => l_user_id
3178 , X_Creation_Date => NVL(l_mtl_trx_tbl(i).transaction_date,sysdate)
3179 , X_Created_By => l_user_id
3180 , X_Inventory_Item_Id => l_mtl_trx_tbl(i).inventory_item_id
3181 , X_Transaction_Cost => l_mtl_trx_tbl(i).old_po_price
3182 , X_Value_Change => l_mtl_trx_tbl(i).old_po_price-l_mtl_trx_tbl(i).new_po_price);
3183 IF (l_row_id is null or l_row_id < 0) THEN
3184 IF (l_debug = 1) THEN
3185 print_debug('MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row returns error', 9);
3186 print_debug('l_row_id = ' || l_row_id, 9);
3187 END IF;
3188 FND_MESSAGE.SET_NAME('INV', 'INV_INSERT_COST_ERR');
3189 FND_MSG_PUB.ADD;
3190 RAISE FND_API.G_EXC_ERROR;
3191 END IF;
3192 ELSE
3193 IF (l_debug = 1)
3194 THEN
3195 print_debug('Note: This is Process Enabled Org, so no rows being inserted into MTL_CST_TXN_COST_DETAILS', 9);
3196 END IF;
3197 END IF;
3198 END LOOP;
3199 ELSE
3200 l_progress := 450;
3201 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
3202 IF (l_mtl_trx_tbl(i).transaction_cost IS NOT NULL) THEN
3203 IF (l_debug = 1) THEN
3204 print_debug('X_Transaction_Id = ' || l_mtl_trx_tbl(i).transaction_id, 9);
3205 print_debug('X_Organization_Id = ' || l_mtl_trx_tbl(i).organization_id, 9);
3206 print_debug('X_Last_Updated_By = ' || l_user_id, 9);
3207 print_debug('X_Inventory_Item_Id = ' || l_mtl_trx_tbl(i).inventory_item_id, 9);
3208 print_debug('X_Transaction_Cost = ' || l_mtl_trx_tbl(i).transaction_cost, 9);
3209 END IF;
3210
3211 /*
3212 Commented following UOM conversion logic for bug 13241396, because l_mtl_trx_tbl(i).transaction_cost is always in primary UOM
3213
3214 -- Bug 8620411 - Code changes start
3215 SELECT primary_uom_code
3216 INTO l_primary_uom
3217 FROM mtl_system_items
3218 WHERE inventory_item_id = l_mtl_trx_tbl(i).inventory_item_id
3219 AND organization_id =l_mtl_trx_tbl(i).organization_id;
3220
3221
3222 IF ( (l_mtl_trx_tbl(i).transaction_uom <> l_primary_uom) AND p_exploded_flag = G_NOT_EXPLODED) THEN
3223 print_debug('transaction_uom diff than primary UOM, Calling uom_conversion', 9);
3224
3225 INV_CONVERT.inv_um_conversion(
3226 from_unit => l_mtl_trx_tbl(i).transaction_uom
3227 , to_unit => l_primary_uom
3228 , item_id => l_mtl_trx_tbl(i).inventory_item_id
3229 , uom_rate => l_uom_rate
3230 );
3231
3232 IF ( l_uom_rate = -99999 ) THEN
3233 print_debug('Error from calling uom_conversion', 9);
3234 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_UOM_CONV');
3235 FND_MESSAGE.SET_TOKEN('VALUE1', l_mtl_trx_tbl(i).transaction_uom);
3236 FND_MESSAGE.SET_TOKEN('VALUE2', l_primary_uom);
3237 FND_MSG_PUB.ADD;
3238 RAISE FND_API.G_EXC_ERROR;
3239 END IF;
3240
3241 print_debug('l_uom_rate = ' || l_uom_rate, 9);
3242
3243 l_mtl_trx_tbl(i).transaction_cost := l_mtl_trx_tbl(i).transaction_cost / l_uom_rate;
3244 END IF;
3245
3246 -- Bug 8620411 - Code changes end
3247
3248 */
3249
3250 print_debug('X_Transaction_Cost in primary uom = ' || l_mtl_trx_tbl(i).transaction_cost, 9);
3251
3252 MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row(
3253 X_Rowid => l_row_id
3254 , X_Transaction_Id => l_mtl_trx_tbl(i).transaction_id
3255 , X_Organization_Id => l_mtl_trx_tbl(i).organization_id
3256 , X_Cost_Element_Id => 1
3257 , X_Level_Type => 1
3258 , X_Last_Update_Date => NVL(l_mtl_trx_tbl(i).transaction_date,sysdate)
3259 , X_Last_Updated_By => l_user_id
3260 , X_Creation_Date => NVL(l_mtl_trx_tbl(i).transaction_date,sysdate)
3261 , X_Created_By => l_user_id
3262 , X_Inventory_Item_Id => l_mtl_trx_tbl(i).inventory_item_id
3263 , X_Transaction_Cost => l_mtl_trx_tbl(i).transaction_cost
3264 , X_Value_Change => null);
3265 IF (l_row_id is null or l_row_id < 0) THEN
3266 IF (l_debug = 1) THEN
3267 print_debug('MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row returns error', 9);
3268 print_debug('l_row_id = ' || l_row_id, 9);
3269 END IF;
3270 FND_MESSAGE.SET_NAME('INV', 'INV_INSERT_COST_ERR');
3271 FND_MSG_PUB.ADD;
3272 RAISE FND_API.G_EXC_ERROR;
3273 ELSE
3274 IF (l_debug = 1) THEN
3275 print_debug('MTL_CST_TXN_COST_DETAILS_PKG.Insert_Row returns success', 9);
3276 END IF;
3277 END IF;
3278 END IF;
3279 END LOOP;
3280 END IF;
3281
3282 -- if it's dropship deliver, then call the om API to update
3283 -- the shipped qty for the sales order with p_mode=1
3284 IF (p_logical_trx_type_code = G_LOGTRXCODE_DSDELIVER) THEN
3285 IF (l_debug = 1) THEN
3286 print_debug('Calling OE_DS_PVT.DropShipReceive', 9);
3287 print_debug('p_rcv_transaction_id = ' || p_mtl_trx_tbl(1).rcv_transaction_id, 9);
3288 END IF;
3289 l_progress := 460;
3290 l_dsreceive := OE_DS_PVT.DropShipReceive(
3291 p_rcv_transaction_id => p_mtl_trx_tbl(1).rcv_transaction_id
3292 , p_application_short_name => 'INV'
3293 , p_mode => 1);
3294 IF (l_debug = 1) THEN
3295 print_debug('After calling OE_DS_PVT.DropShipReceive', 9);
3296 END IF;
3297
3298 IF (l_dsreceive = FALSE) THEN
3299 IF (l_debug = 1) THEN
3300 print_debug('OE_DS_PVT.DropShipReceive returns false', 9);
3301 END IF;
3302 FND_MESSAGE.SET_NAME('INV', 'INV_DS_UPDATE_ERROR');
3303 FND_MSG_PUB.ADD;
3304 RAISE FND_API.G_EXC_ERROR;
3305 ELSE
3306 IF (l_debug = 1) THEN
3307 print_debug('OE_DS_PVT.DropShipReceive returns true', 9);
3308 END IF;
3309 END IF;
3310
3311 --Update the DSdelievr transaction with the parent transaction id
3312 -- after we call the om API
3313
3314 IF (l_debug = 1) THEN
3315 print_debug('update MMT of trx_id = ' || p_mtl_trx_tbl(1).transaction_id
3316 || ' with parent trx id and trx batch id = '
3317 || p_mtl_trx_tbl(1).transaction_id, 9);
3318 END IF;
3319 UPDATE mtl_material_transactions
3320 SET parent_transaction_id = p_mtl_trx_tbl(1).transaction_id,
3321 transaction_batch_id = p_mtl_trx_tbl(1).transaction_id,
3322 logical_transactions_created = 1,
3323 logical_transaction = 1,
3324 invoiced_flag = NULL,
3325 trx_source_line_id = l_mtl_trx_tbl(1).trx_source_line_id,
3326 pm_cost_collected = 'N'
3327 WHERE transaction_id = p_mtl_trx_tbl(1).transaction_id;
3328
3329 IF (SQL%ROWCOUNT = 0) THEN
3330 IF (l_debug = 1) THEN
3331 print_debug('No MMT record is found for update with trx id:'
3332 || p_mtl_trx_tbl(1).transaction_id ,9);
3333 END IF;
3334 FND_MESSAGE.SET_NAME('INV', 'INV_MMT_NOT_FOUND');
3335 FND_MSG_PUB.ADD;
3336 RAISE FND_API.G_EXC_ERROR;
3337 END IF;
3338
3339 -- Call INV_TXNSTUB_PUB.postTransaction only for logical PO receipt record
3340 -- of true dropship
3341 IF (l_debug = 1) THEN
3342 print_debug('Before Calling INV_TXNSTUB_PUB.postTransaction', 9);
3343 print_debug('transaction_id = ' || p_mtl_trx_tbl(1).transaction_id, 9);
3344 END IF;
3345
3346 INV_TXNSTUB_PUB.postTransaction
3347 ( p_header_id => null
3348 ,p_transaction_id => p_mtl_trx_tbl(1).transaction_id
3349 ,x_return_status => l_return_status);
3350
3351 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3352 IF (l_debug = 1) THEN
3353 print_debug('INV_TXNSTUB_PUB.postTransaction returns error: ' || l_msg_data, 9);
3354 END IF;
3355 RAISE FND_API.G_EXC_ERROR;
3356 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3357 IF (l_debug = 1) THEN
3358 print_debug('INV_TXNSTUB_PUB.postTransaction returns unexpected error: ' || l_msg_data, 9);
3359 END IF;
3360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3361 ELSE
3362 IF (l_debug = 1) THEN
3363 print_debug('INV_TXNSTUB_PUB returns success', 9);
3364 END IF;
3365 END IF;
3366
3367 END IF;
3368
3369 -- If it's sales order issue or RMA,
3370 -- update the MMT transaction with the parent_transaction_id,
3371 -- transaction_batch_id, logical_transactions_created
3372 -- logical_transaction (1 - logical trx, 2 - physical trx) and invoiced_flag
3373 IF (p_logical_trx_type_code = G_LOGTRXCODE_RMASOISSUE) THEN
3374 l_progress := 470;
3375 IF (l_debug = 1) THEN
3376 print_debug('update MMT of trx_id = ' || p_mtl_trx_tbl(1).transaction_id
3377 || ' with parent trx id and trx batch id = '
3378 || p_mtl_trx_tbl(1).transaction_id, 9);
3379 END IF;
3380 UPDATE mtl_material_transactions
3381 SET parent_transaction_id = p_mtl_trx_tbl(1).transaction_id,
3382 transaction_batch_id = p_mtl_trx_tbl(1).transaction_id,
3383 trx_flow_header_id = p_trx_flow_header_id,
3384 logical_transactions_created = 1,
3385 logical_transaction = 2,
3386 invoiced_flag = null
3387 WHERE transaction_id = p_mtl_trx_tbl(1).transaction_id;
3388 IF (SQL%ROWCOUNT = 0) THEN
3389 IF (l_debug = 1) THEN
3390 print_debug('No MMT record is found for update with trx id:'
3391 || p_mtl_trx_tbl(1).transaction_id ,9);
3392 END IF;
3393 FND_MESSAGE.SET_NAME('INV', 'INV_MMT_NOT_FOUND');
3394 FND_MSG_PUB.ADD;
3395 RAISE FND_API.G_EXC_ERROR;
3396 END IF;
3397 END IF;
3398
3399
3400 -- if it's retroactive price update, Call price_update_insert API
3401 -- to insert into mtl_consumption_transactions
3402 IF (p_logical_trx_type_code = G_LOGTRXCODE_RETROPRICEUPD) THEN
3403 FOR i in 1..l_mtl_trx_tbl.COUNT LOOP
3404 IF (l_debug = 1) THEN
3405 print_debug('Calling INV_CONSUMPTION_TXN_PVT.price_update_insert', 9);
3406 print_debug('p_transaction_id = ' || l_mtl_trx_tbl(i).transaction_id, 9);
3407 print_debug('p_consumption_po_header_id = ' || l_mtl_trx_tbl(i).consumption_po_header_id, 9);
3408 print_debug('p_consumption_release_id = ' || l_mtl_trx_tbl(i).consumption_release_id, 9);
3409 print_debug('p_transaction_quantity = ' || l_mtl_trx_tbl(i).transaction_quantity, 9);
3410 END IF;
3411
3412 l_progress := 480;
3413 INV_CONSUMPTION_TXN_PVT.price_update_insert(
3414 p_transaction_id => l_mtl_trx_tbl(i).transaction_id
3415 , p_consumption_po_header_id => l_mtl_trx_tbl(i).consumption_po_header_id
3416 , p_consumption_release_id => l_mtl_trx_tbl(i).consumption_release_id
3417 , p_transaction_quantity => l_mtl_trx_tbl(i).transaction_quantity
3418 , p_po_distribution_id => l_mtl_trx_tbl(i).PO_DISTRIBUTION_ID
3419 , x_msg_count => l_msg_count
3420 , x_msg_data => l_msg_data
3421 , x_return_status => l_return_status);
3422
3423 IF (l_debug = 1) THEN
3424 print_debug('After calling price_update_insert, l_return_status = ' || l_return_status, 9);
3425 END IF;
3426
3427 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3428 IF (l_debug = 1) THEN
3429 print_debug('price_update_insert returns error: ' || l_msg_data, 9);
3430 END IF;
3431 RAISE FND_API.G_EXC_ERROR;
3432 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3433 IF (l_debug = 1) THEN
3434 print_debug('price_update_insert returns unexpected error: ' || l_msg_data, 9);
3435 END IF;
3436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3437 ELSE
3438 IF (l_debug = 1) THEN
3439 print_debug('price_update_insert returns success', 9);
3440 END IF;
3441 END IF;
3442 END LOOP;
3443 END IF;
3444
3445 x_return_status := G_RET_STS_SUCCESS;
3446 EXCEPTION
3447 WHEN FND_API.G_EXC_ERROR THEN
3448 x_return_status := FND_API.G_RET_STS_ERROR;
3449 ROLLBACK TO create_logical_transactions;
3450
3451 IF (l_debug = 1) THEN
3452 print_debug('create_logical_transactions: Expected Error, l_progress = ' || l_progress, 9);
3453 print_debug('SQL Error: ' || Sqlerrm(SQLCODE),9);
3454 print_debug('Return Status :' || x_return_status, 9);
3455 END IF;
3456
3457 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3458
3459
3460 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3462 ROLLBACK TO create_logical_transactions;
3463
3464 IF (l_debug = 1) THEN
3465 print_debug('create_logical_transactions: Unexpected Error, l_progress = ' || l_progress, 9);
3466 print_debug('SQL Error: ' || Sqlerrm(SQLCODE),9);
3467 print_debug('Return Status :' || x_return_status, 9);
3468 END IF;
3469
3470 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3471
3472 WHEN OTHERS THEN
3473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3474 ROLLBACK TO create_logical_transactions;
3475
3476 IF (l_debug = 1) THEN
3477 print_debug('create_logical_transactions: Other Error, l_progress = ' || l_progress, 9);
3478 print_debug('SQL Error: ' || Sqlerrm(SQLCODE),9);
3479 print_debug('Return Status :' || x_return_status, 9);
3480 END IF;
3481
3482 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3483
3484 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3485 FND_MSG_PUB.Add_Exc_Msg
3486 (G_PKG_NAME, 'INV_LOGICAL_TRANSACTIONS_PUB');
3487 END IF;
3488 END create_logical_transactions;
3489
3490 PROCEDURE create_deferred_log_txns_cp
3491 (errbuf OUT NOCOPY VARCHAR2,
3492 retcode OUT NOCOPY NUMBER,
3493 p_api_version IN NUMBER,
3494 p_start_date IN VARCHAR2,
3495 p_end_date IN VARCHAR2
3496 )
3497 IS
3498 l_ret BOOLEAN;
3499 l_return_status VARCHAR2(1);
3500 l_msg_data VARCHAR2(2000);
3501 l_msg_count NUMBER;
3502 l_failed NUMBER := 0;
3503 l_success NUMBER := 0;
3504 l_message VARCHAR2(255);
3505
3506 --Bug: 3632208. Removed the NVL around the start date and end date
3507 -- so that the index on transaction_date will be used. The start and
3508 -- end dates are mandatory. So, no need for the NVL.
3509 CURSOR deferred_transactions IS
3510 SELECT transaction_id FROM
3511 mtl_material_transactions
3512 WHERE
3513 logical_transactions_created = 2
3514 AND transaction_date BETWEEN
3515 fnd_date.canonical_to_date(p_start_date)
3516 AND fnd_date.canonical_to_date(p_end_date);
3517
3518 BEGIN
3519 -- Bug Number 3307070
3520 IF(
3521 (inv_control.get_current_release_level < INV_Release.Get_J_RELEASE_LEVEL)
3522 OR (CST_VersionCtrl_GRP.GET_CURRENT_RELEASE_LEVEL < CST_Release_GRP.GET_J_RELEASE_LEVEL )
3523 OR (OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL < '110510' )
3524 )
3525 THEN
3526 print_debug('This function is not availiable without patchset J of INV,OM,and COSTING');
3527 retcode := 2;
3528 fnd_message.set_name('INV', 'INV_CREATE_DEF_NOT_AVAILABLE');
3529 l_message := fnd_message.get;
3530 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
3531
3532 ELSE
3533
3534 FOR l_txn_rec IN deferred_transactions LOOP
3535
3536 l_return_status := g_ret_sts_success;
3537 l_msg_data := NULL;
3538 l_msg_count := 0;
3539
3540 SAVEPOINT create_log_txn;
3541
3542 BEGIN
3543
3544 UPDATE mtl_material_transactions
3545 SET logical_transactions_created = 1
3546 WHERE
3547 transaction_id = l_txn_rec.transaction_id;
3548
3549 inv_logical_transactions_pub.create_logical_trx_wrapper
3550 ( x_return_status => l_return_status
3551 , x_msg_count => l_msg_count
3552 , x_msg_data => l_msg_data
3553 , p_api_version_number => p_api_version
3554 , p_init_msg_lst => fnd_api.g_true
3555 , p_transaction_id => l_txn_rec.transaction_id
3556 );
3557 EXCEPTION
3558 WHEN OTHERS THEN
3559 l_return_status := G_RET_STS_UNEXP_ERROR;
3560 END;
3561
3562 IF l_return_status = g_ret_sts_success THEN
3563 l_success := l_success + 1;
3564 print_debug('successfully processed txn id:'||l_txn_rec.transaction_id);
3565 COMMIT;
3566 ELSE
3567 l_failed := l_failed + 1;
3568 print_debug('Falied to process txn id:'||l_txn_rec.transaction_id ||
3569 ' message '||l_msg_data);
3570 ROLLBACK TO create_log_txn;
3571 END IF;
3572 END LOOP;
3573
3574 print_debug(l_success||' successful '||l_failed||' failed.');
3575
3576 IF l_failed > 0 THEN
3577 retcode := 3;
3578 fnd_message.set_name('INV', 'INV_CREATE_LOG_TXNS_WARN');
3579 fnd_message.set_token('FAIL_COUNT',''||l_failed);
3580 l_message := fnd_message.get;
3581 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
3582
3583 ELSE
3584 retcode := 1;
3585 fnd_message.set_name('INV', 'INV_CREATE_LOG_TXNS_SUCCESS');
3586 l_message := fnd_message.get;
3587 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',l_message);
3588 END IF;
3589 END IF;
3590 EXCEPTION
3591 WHEN OTHERS THEN
3592 retcode := 2;
3593 fnd_message.set_name('INV', 'INV_CREATE_LOG_TXNS_ERR');
3594 l_message := fnd_message.get;
3595 l_ret := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_message);
3596
3597 END CREATE_DEFERRED_LOG_TXNS_CP;
3598
3599
3600
3601 /*==========================================================================*
3602 | Procedure : CHECK_ACCOUNTING_PERIOD_CLOSE
3603 |
3604 |
3605 |
3606 | Description : This API will deletermine if a current accounting
3607 | period that is being closed has any transactions in
3608 | mtl_material_transactions table that has any deferred
3609 | transactions that have not been costed,and if it
3610 | belongs to a transaction flow where the orgs operatinh
3611 | unit is one of the intermediate nodes and so, will
3612 | prevent the user from cosing the accounting period.
3613 |
3614 |
3615 |
3616 | Input Parameters :
3617 |
3618 | p_api_version_number - API version number
3619 |
3620 | p_init_msg_lst - Whether initialize the error message list
3621 | or not
3622 | Should be fnd_api.g_false or fnd_api.g_true
3623 |
3624 | p_organization_id - Organziation Id of the org that is being
3625 | closed .
3626 |
3627 | p_org_id - operating unit of the org that is being closed.
3628 |
3629 | p_period_start_date - Start date of the accounting period of the
3630 | organization that is being closed.
3631 |
3632 | p_period_end_date - End date of the accounting period of the
3633 | organization that is being closed.
3634 |
3635 | Output Parameters :
3636 |
3637 | x_return_status - fnd_api.g_ret_sts_success, if succeeded
3638 |
3639 | fnd_api.g_ret_sts_exc_error, if an expected
3640 |
3641 | error occurred
3642 |
3643 | fnd_api.g_ret_sts_unexp_error, if an
3644 | unexpected
3645 | eror occurred
3646 |
3647 | x_msg_count - Number of error message in the error message
3648 |
3649 | list
3650 |
3651 | x_msg_data - If the number of error message in the error
3652 |
3653 | message list is one, the error message is in
3654 |
3655 | this output parameter
3656 | x_period_close - This is a boolean which will decide whether
3657 | the accounting period can be closed or not
3658 *========================================================================*/
3659
3660 PROCEDURE check_accounting_period_close
3661 (x_return_status OUT NOCOPY VARCHAR2
3662 , x_msg_count OUT NOCOPY NUMBER
3663 , x_msg_data OUT NOCOPY VARCHAR2
3664 , x_period_close OUT nocopy VARCHAR2
3665 , p_api_version_number IN NUMBER := 1.0
3666 , p_init_msg_lst IN VARCHAR2 := G_FALSE
3667 , p_organization_id IN NUMBER
3668 , p_org_id IN NUMBER
3669 , p_period_start_date IN DATE
3670 , p_period_end_date IN DATE
3671 )
3672 IS
3673
3674 l_count NUMBER := 0;
3675 l_transaction_date DATE;
3676 l_api_version_number CONSTANT NUMBER := 1.0;
3677 l_in_api_version_number NUMBER := NVL(p_api_version_number, 1.0);
3678 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_ACCOUNTING_PERIOD_CLOSE';
3679 l_init_msg_lst VARCHAR2(1) := NVL(p_init_msg_lst, G_FALSE);
3680
3681 CURSOR deferred_mmt_records IS
3682 SELECT trx_flow_header_id, transaction_date FROM
3683 mtl_material_transactions mmt WHERE costed_flag = 'N' AND
3684 logical_transactions_created = 2;
3685
3686 BEGIN
3687
3688 x_period_close := 'Y';
3689 x_return_status := G_RET_STS_SUCCESS;
3690
3691 IF (l_debug = 1) THEN
3692 print_debug('Organization Id :' || p_organization_id, 9);
3693 print_debug('OU Id :' || p_org_id, 9);
3694 print_debug('Period Start Date= ' || p_period_start_date, 9);
3695 print_debug('Period End Date = ' || p_period_end_date, 9);
3696 END IF;
3697
3698 IF (p_organization_id IS NULL OR
3699 p_period_start_date IS NULL OR p_period_end_date IS NULL) then
3700 IF (l_debug = 1) THEN
3701 print_debug('Invalid input parameters', 9);
3702 END IF;
3703 RAISE FND_API.G_EXC_ERROR;
3704 END IF;
3705
3706 IF (l_debug = 1) THEN
3707 print_debug('Before calling compatible API', 9);
3708 END IF;
3709
3710
3711 -- Standard call to check for call compatibility
3712 IF NOT fnd_api.compatible_api_call
3713 (l_api_version_number, l_in_api_version_number, l_api_name, g_pkg_name) THEN
3714 RAISE fnd_api.g_exc_unexpected_error;
3715 END IF;
3716
3717 IF (l_debug = 1) THEN
3718 print_debug('Before calling init API', 9);
3719 END IF;
3720
3721 -- Initialize message list.
3722 IF fnd_api.to_boolean(l_init_msg_lst) THEN
3723 fnd_msg_pub.initialize;
3724 END IF;
3725
3726 IF (l_debug = 1) THEN
3727 print_debug('After calling init API', 9);
3728 END IF;
3729
3730 FOR deferred_trxs IN deferred_mmt_records LOOP
3731
3732 IF (l_debug = 1) THEN
3733 print_debug('Inside the loop', 9);
3734 END IF;
3735
3736 IF deferred_mmt_records%ROWCOUNT = 0 THEN
3737
3738 IF (l_debug = 1) THEN
3739 print_debug('No records with deferred flag',9);
3740 x_period_close := 'Y';
3741 x_return_status := G_RET_STS_SUCCESS;
3742 RETURN;
3743 END IF;
3744 END IF;
3745
3746 IF (l_debug = 1) THEN
3747 print_debug('Inside the loop', 9);
3748 END IF;
3749
3750 BEGIN
3751 l_transaction_date :=
3752 INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(deferred_trxs.transaction_date,p_organization_id);
3753
3754 EXCEPTION
3755 WHEN others THEN
3756 IF (l_debug = 1) THEN
3757 print_debug('Timezone API returned error: ' || l_transaction_date, 9);
3758 RAISE fnd_api.g_exc_unexpected_error;
3759 END IF;
3760
3761 END;
3762
3763 IF (l_debug = 1) THEN
3764 print_debug('Transaction date' || l_transaction_date, 9);
3765 END IF;
3766
3767 IF (l_debug = 1) THEN
3768 print_debug('Trx Flow Header Id'||
3769 deferred_trxs.trx_flow_header_id , 9);
3770 print_debug('Transaction_date'||
3771 deferred_trxs.transaction_date, 9);
3772 print_debug('Transaction_date with LE timezone'|| l_transaction_date, 9);
3773 END IF;
3774
3775 BEGIN
3776 SELECT COUNT(1) into l_count
3777 FROM mtl_transaction_flow_headers mtfh,
3778 mtl_transaction_flow_lines mtfl
3779 WHERE (l_transaction_date BETWEEN p_period_start_date AND
3780 p_period_end_date) AND
3781 mtfh.header_id = deferred_trxs.trx_flow_header_id AND
3782 mtfh.new_accounting_flag = 'Y' AND
3783 mtfh.header_id = mtfl.header_id AND
3784 (mtfl.from_organization_id = p_organization_id OR
3785 mtfl.to_organization_id = p_organization_id );
3786 EXCEPTION
3787 WHEN no_data_found THEN
3788
3789 IF (l_debug = 1) THEN
3790 print_debug('Check Account Period Close: Cannot find any lines', 9);
3791 x_period_close := 'Y';
3792 x_return_status := G_RET_STS_SUCCESS;
3793 END IF;
3794
3795 END;
3796
3797 IF l_count > 0 THEN
3798 x_period_close := 'N';
3799 print_debug('Check Account Period Close: Count > 0', 9);
3800 x_return_status := G_RET_STS_SUCCESS;
3801 RETURN;
3802 ELSIF l_count = 0 THEN
3803 x_period_close := 'Y';
3804 print_debug('Check Account Period Close: Count = 0', 9);
3805 x_return_status := G_RET_STS_SUCCESS;
3806 ELSE
3807 x_period_close := 'N';
3808 x_return_status := G_RET_STS_UNEXP_ERROR;
3809 print_debug('Check Account Period Close: Count incorrect', 9);
3810 RAISE fnd_api.g_exc_unexpected_error;
3811 END IF;
3812
3813 END LOOP;
3814
3815 IF (l_debug = 1) THEN
3816 print_debug('After the loop', 9);
3817 print_debug('Period Close' || x_period_close, 9);
3818 print_debug('Return Status' || x_return_status , 9);
3819 END IF;
3820
3821
3822 /******** Rather than doing it for 1 record at a time, we can do it a
3823 little differently. Instead of converting the transaction_date to
3824 the legal entities timezone, we can add the difference between
3825 the transaction_date in the server time and the legal entities
3826 time to the period start and the period end date.
3827
3828 Say for example,
3829 Period start date is 'Jun 01 2003, 00:00:00
3830 Period end date is 'Jun 30 2003, 23:59:59
3831 Transaction Date is 'Jun 30 2003, 10:50:00
3832
3833 Say if the Transaction date if coverted to the Legal Entity's
3834 time zone is Jun 31 2003, 00:50:00 (Trx_date + 14).
3835 To achieve the same result, we can subtract the period start
3836 date and the period end date by the same factor, which in this
3837 case is 14 hours.
3838
3839 So,
3840 Jun 01 2003, 00:00:00 > Jun 30 2003, 10:50:00 >= Jun 30 2003,
3841 23:59:59
3842 When the transaction_date is converted to legal entitys
3843 timezone, it will beccome,
3844 Jun 01 2003, 00:00:00 > Jun 31 2003, 00:50:00 >= Jun 30 2003,
3845
3846 23:59:59
3847
3848 It is the same as,
3849
3850 May 31 2003, 10:00:00 > Jun 30 2003, 10:50:00 >=
3851 Jun 30 2003, 09:59:59
3852
3853 l_period_start_date :=
3854 INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(p_period_start_date,p_organization_id);
3855 l_period_end_date :=
3856 INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(p_period_end_date,p_organization_id);
3857
3858 BEGIN
3859 SELECT COUNT(1) into l_count
3860 FROM
3861 mtl_material_transactions mmt,
3862 mtl_transaction_flow_headers mtfh,
3863 mtl_transaction_flow_lines mtfl
3864 WHERE (mmt.transaction_date BETWEEN
3865 l_period_start_date AND l_period_end_date) AND
3866 mmt.logical_transactions_created = 2 AND
3867 mmt.trx_flow_header_id = mtfh.header_id AND
3868 mtfh.new_accounting_flag = 'Y' AND
3869 mtfh.header_id = mtfl.header_id AND
3870 (mtfl.from_organization_id = p_organziation_id OR
3871 mtfl.to_organization_id = p_organziation_id );
3872 EXCEPTION
3873 WHEN no_data_found THEN
3874 IF (l_debug = 1) THEN
3875 print_debug('Check Account Period Close: Cannot find any lines', 9);
3876 x_period_close := 'Y';
3877 x_return_status := G_RET_STS_SUCCESS;
3878 END IF;
3879
3880 END;
3881
3882 IF l_count > 0 THEN
3883 x_period_close := 'N';
3884 print_debug('Check Account Period Close: Count > 0', 9);
3885 x_return_status := G_RET_STS_SUCCESS;
3886 ELSIF l_count = 0 THEN
3887 x_period_close := 'Y';
3888 print_debug('Check Account Period Close: Count = 0', 9);
3889 x_return_status := G_RET_STS_SUCCESS;
3890 ELSE
3891 x_period_close := 'N';
3892 x_return_status := G_RET_STS_UNEXP_ERROR;
3893 print_debug('Check Account Period Close: Count incorrect', 9);
3894 RAISE fnd_api.g_exc_unexpected_error;
3895 END IF;
3896 *******/
3897
3898 EXCEPTION
3899 WHEN FND_API.G_EXC_ERROR THEN
3900 x_return_status := FND_API.G_RET_STS_ERROR;
3901
3902 IF (l_debug = 1) THEN
3903 print_debug('Check Account Period Close: Expected Error', 9);
3904 print_debug('SQL Error: ' || Sqlerrm(SQLCODE),9);
3905 print_debug('Return Status :' || x_return_status, 9);
3906 END IF;
3907
3908 FND_MSG_PUB.count_and_get
3909 (p_count => x_msg_count, p_data => x_msg_data);
3910
3911 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3913
3914 IF (l_debug = 1) THEN
3915 print_debug('Check Account Period Close: Unexpected Error', 9);
3916 print_debug('SQL Error: ' || Sqlerrm(SQLCODE),9);
3917 print_debug('Return Status :' || x_return_status, 9);
3918 END IF;
3919
3920 FND_MSG_PUB.count_and_get
3921 (p_count => x_msg_count, p_data => x_msg_data);
3922
3923 WHEN OTHERS THEN
3924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3925
3926 IF (l_debug = 1) THEN
3927 print_debug('Check Account Period Close: Other Error', 9);
3928 print_debug('SQL Error: ' || Sqlerrm(SQLCODE),9);
3929 print_debug('Return Status :' || x_return_status, 9);
3930 END IF;
3931
3932 FND_MSG_PUB.count_and_get
3933 (p_count => x_msg_count, p_data => x_msg_data);
3934
3935 IF FND_MSG_PUB.check_msg_level
3936 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3937 FND_MSG_PUB.Add_Exc_Msg
3938 (G_PKG_NAME, 'INV_LOGICAL_TRANSACTIONS_PUB');
3939 END IF;
3940
3941 END CHECK_ACCOUNTING_PERIOD_CLOSE;
3942
3943 --deferred cogs NEW api for cogs recognition
3944 PROCEDURE create_cogs_recognition ( x_return_status OUT nocopy NUMBER,
3945 x_error_code OUT nocopy VARCHAR2,
3946 x_error_message OUT nocopy VARCHAR2)
3947 IS
3948 l_count NUMBER :=0;
3949
3950 BEGIN
3951 x_return_status :=0;
3952 --Validate the inventory item id.
3953 SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp crtt
3954 WHERE INVENTORY_ITEM_ID IS NOT NULL
3955 AND NOT EXISTS (
3956 SELECT NULL
3957 FROM MTL_SYSTEM_ITEMS MSI
3958 WHERE MSI.INVENTORY_ITEM_ID = crtt.INVENTORY_ITEM_ID
3959 AND MSI.ORGANIZATION_ID = crtt.ORGANIZATION_ID
3960 AND MSI.INVENTORY_ITEM_FLAG = 'Y');
3961
3962 IF l_count <> 0 THEN
3963 IF (l_debug = 1) THEN
3964 print_debug('CREATE_COGS_RECOGNITION:Validating specified item 1' || l_count || 'failed', 9);
3965 END IF;
3966 fnd_message.set_name('INV', 'inv_int_itmcode');
3967 x_error_code := fnd_message.get;
3968 fnd_message.set_name('INV', 'inv_int_itmexp');
3969 x_error_message := fnd_message.get;
3970 x_return_status := -1;
3971 RETURN;
3972 END IF;
3973
3974 SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp
3975
3976 WHERE INVENTORY_ITEM_ID IS NULL;
3977
3978 IF l_count <> 0 THEN
3979 IF (l_debug = 1) THEN
3980 print_debug('CREATE_COGS_RECOGNITION:Validating specified item 1' || l_count || 'failed', 9);
3981 END IF;
3982 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
3983 x_error_code := fnd_message.get;
3984 fnd_message.set_name('INV', 'inv_int_itmexp');
3985 x_error_message := fnd_message.get;
3986 x_return_status := -1;
3987 RETURN;
3988 END IF;
3989 --validate subinventory code.
3990
3991
3992 SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp crtt
3993
3994 WHERE TRANSACTION_ACTION_ID NOT IN (24, 30)
3995 AND subinventory_code IS NOT null
3996 AND NOT EXISTS (
3997 SELECT NULL
3998 FROM MTL_SECONDARY_INVENTORIES MSI
3999 WHERE MSI.ORGANIZATION_ID = crtt.ORGANIZATION_ID
4000 AND MSI.SECONDARY_INVENTORY_NAME = crtt.SUBINVENTORY_CODE
4001 AND NVL(MSI.DISABLE_DATE,SYSDATE+1) > Sysdate);
4002
4003 IF l_count <> 0 THEN
4004 IF (l_debug = 1) THEN
4005 print_debug('CREATE_COGS_RECOGNITION"Validating specified item 1' || l_count || 'failed', 9);
4006 END IF;
4007 fnd_message.set_name('INV','inv_int_subcode' );
4008 x_error_code := fnd_message.get;
4009 fnd_message.set_name('INV', 'INV_INT_SUBEXP');
4010 x_error_message := fnd_message.get;
4011 x_return_status := -1;
4012 RETURN;
4013 END IF;
4014
4015 --Validating restricted subinventories
4016
4017
4018
4019 SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp crtt
4020
4021 where SUBINVENTORY_CODE IS NOT NULL
4022 AND NOT EXISTS (
4023 SELECT NULL
4024 FROM MTL_ITEM_SUB_INVENTORIES MIS,
4025 MTL_SYSTEM_ITEMS MSI
4026 WHERE MSI.ORGANIZATION_ID = crtt.ORGANIZATION_ID
4027 AND MSI.INVENTORY_ITEM_ID = crtt.INVENTORY_ITEM_ID
4028 AND MSI.RESTRICT_SUBINVENTORIES_CODE = 1
4029 AND MIS.ORGANIZATION_ID = crtt.ORGANIZATION_ID
4030 AND MIS.INVENTORY_ITEM_ID = crtt.INVENTORY_ITEM_ID
4031 AND MIS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
4032 AND MIS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
4033 AND MIS.SECONDARY_INVENTORY = crtt.SUBINVENTORY_CODE
4034 UNION
4035 SELECT NULL
4036 FROM MTL_SYSTEM_ITEMS ITM
4037 WHERE ITM.ORGANIZATION_ID = crtt.ORGANIZATION_ID
4038 AND ITM.INVENTORY_ITEM_ID = crtt.INVENTORY_ITEM_ID
4039 AND ITM.RESTRICT_SUBINVENTORIES_CODE = 2);
4040 IF l_count <> 0 THEN
4041 IF (l_debug = 1) THEN
4042 print_debug('CREATE_COGS_RECOGNITION:Validating specified item 3' || l_count || 'failed', 9);
4043 END IF;
4044 fnd_message.set_name('INV','INV_INT_SUBCODE');
4045 x_error_code := fnd_message.get;
4046 fnd_message.set_name('INV','inv_int_subcode');
4047 x_error_message := fnd_message.get;
4048 x_return_status := -1;
4049 RETURN;
4050 END IF;
4051
4052 SELECT COUNT(1) INTO l_count FROM mtl_cogs_recognition_temp crtt
4053
4054 where transaction_type_id <> 10008
4055 OR transaction_action_id <> 36
4056 OR transaction_source_type_id <>2;
4057
4058 IF l_count <> 0 THEN
4059 IF (l_debug = 1) THEN
4060 print_debug('CREATE_COGS_RECOGNITION:Validating specified item 4' || l_count || 'failed', 9);
4061 END IF;
4062 --fnd_message.set_name('INV','INV_INT_SUBCODE');
4063 x_error_code := 'Invalid Transaction Source-Action-TYPE combination';
4064 --fnd_message.set_name('INV','inv_int_subcode');
4065 x_error_message := 'Invalid Transaction Source-Action-TYPE combination';
4066 x_return_status := -1;
4067 RETURN;
4068 END IF;
4069
4070 --bulk insert into mmt.
4071
4072
4073 INSERT INTO mtl_material_transactions
4074 (TRANSACTION_ID,
4075 ORGANIZATION_ID,
4076 INVENTORY_ITEM_ID,
4077 REVISION,
4078 SUBINVENTORY_CODE,
4079 LOCATOR_ID,
4080 TRANSACTION_TYPE_ID,
4081 TRANSACTION_ACTION_ID,
4082 TRANSACTION_SOURCE_TYPE_ID,
4083 TRANSACTION_SOURCE_ID,
4084 TRANSACTION_SOURCE_NAME,
4085 TRANSACTION_QUANTITY,
4086 TRANSACTION_UOM,
4087 PRIMARY_QUANTITY,
4088 TRANSACTION_DATE,
4089 ACCT_PERIOD_ID,
4090 DISTRIBUTION_ACCOUNT_ID,
4091 COSTED_FLAG,
4092 ACTUAL_COST,
4093 INVOICED_FLAG,
4094 TRANSACTION_COST,
4095 CURRENCY_CODE,
4096 CURRENCY_CONVERSION_RATE,
4097 CURRENCY_CONVERSION_TYPE,
4098 CURRENCY_CONVERSION_DATE,
4099 PM_COST_COLLECTED,
4100 TRX_SOURCE_LINE_ID,
4101 SOURCE_CODE,
4102 SOURCE_LINE_ID,
4103 TRANSFER_ORGANIZATION_ID,
4104 TRANSFER_SUBINVENTORY,
4105 TRANSFER_LOCATOR_ID,
4106 COST_GROUP_ID,
4107 TRANSFER_COST_GROUP_ID,
4108 PROJECT_ID,
4109 TASK_ID,
4110 TO_PROJECT_ID,
4111 TO_TASK_ID,
4112 SHIP_TO_LOCATION_ID,
4113 TRANSACTION_MODE,
4114 TRANSACTION_BATCH_ID,
4115 TRANSACTION_BATCH_SEQ,
4116 LPN_ID,
4117 parent_transaction_id,
4118 last_update_date,
4119 last_updated_by,
4120 creation_date,
4121 created_by,
4122 transaction_set_id,
4123 expenditure_type,
4124 pa_expenditure_org_id,
4125 opm_costed_flag,
4126 cogs_recognition_percent,
4127 so_issue_account_type,
4128 logical_transaction)
4129 SELECT
4130 crtt.transaction_id,
4131 crtt.ORGANIZATION_ID,
4132 crtt.INVENTORY_ITEM_ID,
4133 crtt.REVISION,
4134 crtt.SUBINVENTORY_CODE,
4135 crtt.LOCATOR_ID,
4136 crtt.transaction_type_id,
4137 crtt.transaction_action_id,
4138 crtt.transaction_source_type_id,
4139 crtt.transaction_source_id,
4140 crtt.transaction_source_name,
4141 crtt.transaction_quantity,
4142 crtt.TRANSACTION_UOM,
4143 crtt.primary_quantity,
4144 crtt.TRANSACTION_DATE,
4145 crtt.acct_period_id,
4146 crtt.distribution_account_id,
4147 crtt.COSTED_FLAG,
4148 crtt.ACTUAL_COST,
4149 crtt.INVOICED_FLAG,
4150 crtt.TRANSACTION_COST,
4151 crtt.CURRENCY_CODE,
4152 crtt.CURRENCY_CONVERSION_RATE,
4153 crtt.CURRENCY_CONVERSION_TYPE,
4154 crtt.CURRENCY_CONVERSION_DATE,
4155 crtt.pm_cost_collected,--added pm_cost_collected flag
4156 crtt.trx_source_line_id,
4157 crtt.SOURCE_CODE,
4158 crtt.SOURCE_LINE_ID,
4159 crtt.transfer_ORGANIZATION_id,
4160 crtt.transfer_SUBINVENTORY,
4161 crtt.transfer_LOCATOR_id,
4162 crtt.cost_group_id,
4163 crtt.TRANSFER_COST_GROUP_ID,
4164 crtt.project_id,
4165 crtt.task_id,
4166 crtt.TO_PROJECT_ID,
4167 crtt.TO_TASK_ID,
4168 crtt.SHIP_TO_LOCATION_ID,
4169 crtt.TRANSACTION_MODE,
4170 crtt.TRANSACTION_BATCH_ID,
4171 crtt.TRANSACTION_BATCH_SEQ,
4172 crtt.LPN_ID,
4173 crtt.transaction_id,
4174 crtt.last_update_date,
4175 crtt.last_updated_by,
4176 crtt.creation_date,
4177 crtt.created_by,
4178 crtt.transaction_set_id,
4179 crtt.expenditure_type,
4180 crtt.pa_expenditure_org_id,
4181 crtt.opm_costed_flag,
4182 crtt.cogs_recognition_percent,
4183 crtt.so_issue_account_type ,
4184 crtt.logical_transaction
4185 FROM mtl_cogs_recognition_temp crtt
4186 ;
4187 x_return_status := 0;
4188 RETURN;
4189 EXCEPTION
4190 WHEN OTHERS THEN
4191 IF (l_debug = 1) THEN
4192 print_debug('CREATE_COGS_RECOGNITION:Error in insert', 9);
4193 END IF;
4194 x_error_code := 'Error in insert';
4195 x_error_message := 'Error in INSERT';
4196 x_return_status := -1;
4197 RETURN ;
4198 END create_cogs_recognition;
4199
4200 /**
4201 * OPM INVCONV rseshadr/umoogala 15-Feb-2005
4202 * added procedure for creating logical trx
4203 * for any process/discrete transfers with in-transit.
4204 * No logical txns will be created for direct transfers
4205 **/
4206 PROCEDURE create_opm_disc_logical_trx (
4207 x_return_status OUT NOCOPY VARCHAR2
4208 , x_msg_count OUT NOCOPY NUMBER
4209 , x_msg_data OUT NOCOPY VARCHAR2
4210
4211 , p_api_version_number IN NUMBER := 1.0
4212 , p_init_msg_lst IN VARCHAR2 := G_FALSE
4213 , p_transaction_id IN NUMBER
4214 , p_transaction_temp_id IN NUMBER
4215 )
4216 IS
4217 l_api_version_number CONSTANT NUMBER := 1.0;
4218 l_in_api_version_number NUMBER := NVL(p_api_version_number, 1.0);
4219 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_OPM_DISC_LOGICAL_TRX';
4220 l_init_msg_lst VARCHAR2(1) := NVL(p_init_msg_lst, G_FALSE);
4221
4222 l_return_status VARCHAR2(1);
4223 l_msg_data VARCHAR2(240);
4224 l_msg_count BINARY_INTEGER;
4225 l_account_period_id BINARY_INTEGER;
4226 l_organization_id BINARY_INTEGER;
4227 l_xfer_organization_id BINARY_INTEGER;
4228 l_transaction_date DATE;
4229 l_cost_group_id BINARY_INTEGER;
4230 l_xfer_cost_group_id BINARY_INTEGER;
4231 l_requisition_line_id BINARY_INTEGER;
4232 l_expenditure_type VARCHAR2(240);
4233 l_distribution_account_id BINARY_INTEGER;
4234 l_curr_conversion_rate NUMBER;
4235 l_pri_uom_code mtl_material_transactions.transaction_uom%TYPE;
4236 l_sec_uom_code mtl_material_transactions.secondary_uom_code%TYPE;
4237 l_sec_qty mtl_material_transactions.secondary_transaction_quantity%TYPE;
4238 l_transfer_price mtl_material_transactions.transfer_price%TYPE;
4239 l_transportation_cost mtl_material_transactions.transportation_cost%TYPE;
4240 l_xfer_transaction_id mtl_material_transactions.transfer_transaction_id%TYPE;
4241 l_snd_txn_qty mtl_material_transactions.transaction_quantity%TYPE;
4242 l_snd_txn_uom mtl_material_transactions.transaction_uom%TYPE;
4243 l_snd_pri_qty mtl_material_transactions.primary_quantity%TYPE;
4244 l_snd_pri_uom mtl_system_items_b.primary_uom_code%TYPE;
4245 l_snd_subinv mtl_material_transactions.subinventory_code%TYPE;
4246 l_pri_qty mtl_material_transactions.primary_quantity%TYPE;
4247 l_pri_uom mtl_system_items_b.primary_uom_code%TYPE;
4248 l_owner_pri_uom mtl_system_items_b.primary_uom_code%TYPE;
4249 l_pri_uom_rate NUMBER;
4250 l_snd_sec_qty mtl_material_transactions.secondary_transaction_quantity%TYPE;
4251 l_snd_sec_uom mtl_system_items_b.secondary_uom_code%TYPE;
4252 l_sec_uom mtl_system_items_b.secondary_uom_code%TYPE;
4253 l_tracking_quantity_ind mtl_system_items_b.tracking_quantity_ind%TYPE;
4254 l_secondary_default_ind mtl_system_items_b.secondary_default_ind%TYPE;
4255 l_transaction_uom mtl_material_transactions.transaction_uom%TYPE;
4256 l_item_id mtl_material_transactions.inventory_item_id%TYPE;
4257
4258 l_currency_code mtl_material_transactions.currency_code%TYPE;
4259 l_owner_currency_code mtl_material_transactions.currency_code%TYPE;
4260 -- l_snd_ou_id BINARY_INTEGER;
4261 -- l_snd_sob_id BINARY_INTEGER;
4262 l_ou_id BINARY_INTEGER;
4263 l_sob_id BINARY_INTEGER;
4264
4265 l_owner_org_id BINARY_INTEGER;
4266 l_owner_pri_qty NUMBER;
4267 l_owner_ou_id BINARY_INTEGER;
4268 l_owner_sob_id BINARY_INTEGER;
4269
4270 l_procedure_name VARCHAR2(64) := 'create_opm_disc_logical_trx';
4271 l_skip_qty_conv VARCHAR2(1) := 'N';
4272 l_costed_flag VARCHAR2(1) := NULL;
4273 l_opm_costed_flag VARCHAR2(1) := NULL;
4274
4275 l_transaction_source_type_id BINARY_INTEGER;
4276 l_transaction_action_id BINARY_INTEGER;
4277 l_parent_transaction_id BINARY_INTEGER;
4278 l_transaction_id BINARY_INTEGER;
4279 l_transaction_qty NUMBER;
4280 l_qty_ratio NUMBER;
4281
4282 l_logical_trx_id BINARY_INTEGER;
4283 l_logical_trx_type_id BINARY_INTEGER;
4284 l_logical_trx_action_id BINARY_INTEGER;
4285 l_logical_trx_src_type_id BINARY_INTEGER;
4286
4287
4288 l_fobpoint BINARY_INTEGER;
4289 l_pd_txfr_ind BINARY_INTEGER;
4290
4291 l_stmt_num BINARY_INTEGER;
4292 l_are_qties_valid BINARY_INTEGER;
4293
4294 e_p_d_xfer_na EXCEPTION;
4295 e_uom_conversion_error EXCEPTION;
4296 e_currency_conversion_error EXCEPTION;
4297
4298 -- Bug 5018698: Following columns have been added.
4299 l_transaction_source_id mtl_material_transactions.transaction_source_id%TYPE;
4300 l_transaction_source_name mtl_material_transactions.transaction_source_name%TYPE;
4301 l_trx_source_line_id mtl_material_transactions.trx_source_line_id%TYPE;
4302 l_source_code mtl_material_transactions.source_code%TYPE;
4303 l_source_line_id mtl_material_transactions.source_line_id%TYPE;
4304
4305 l_trx_source_delivery_id mtl_material_transactions.trx_source_delivery_id%TYPE;
4306 l_picking_line_id mtl_material_transactions.picking_line_id%TYPE;
4307 l_pick_slip_number mtl_material_transactions.pick_slip_number%TYPE;
4308 l_pick_strategy_id mtl_material_transactions.pick_strategy_id%TYPE;
4309 l_pick_rule_id mtl_material_transactions.pick_rule_id%TYPE;
4310 l_pick_slip_date mtl_material_transactions.pick_slip_date%TYPE;
4311 l_so_issue_account_type mtl_material_transactions.so_issue_account_type%TYPE;
4312 l_ship_to_location_id mtl_material_transactions.ship_to_location_id%TYPE;
4313
4314 l_invoiced_flag mtl_material_transactions.invoiced_flag%TYPE;
4315
4316 l_snd_currency_code mtl_material_transactions.currency_code%TYPE;
4317 l_currency_conversion_rate mtl_material_transactions.currency_conversion_rate%TYPE;
4318 l_currency_conversion_type mtl_material_transactions.currency_conversion_type%TYPE;
4319 l_currency_conversion_date mtl_material_transactions.currency_conversion_date%TYPE;
4320
4321 l_intercompany_currency_code mtl_material_transactions.intercompany_currency_code%TYPE;
4322 l_intercompany_cost mtl_material_transactions.intercompany_cost%TYPE;
4323 l_intercompany_pricing_option mtl_material_transactions.intercompany_pricing_option%TYPE;
4324
4325 -- End Bug 5018698
4326
4327 l_snd_trp_cost mtl_material_transactions.transportation_cost%TYPE;
4328
4329 CURSOR c_process_flag(p_organization_id BINARY_INTEGER)
4330 IS
4331 SELECT NVL(mp.process_enabled_flag,'N')
4332 FROM mtl_parameters mp
4333 WHERE mp.organization_id = p_organization_id;
4334
4335
4336 CURSOR c_from_to_ou(p_organizaiton_id BINARY_INTEGER, p_xfer_organization_id BINARY_INTEGER)
4337 IS
4338 SELECT org.operating_unit, org.set_of_books_id,
4339 xorg.operating_unit, xorg.set_of_books_id
4340 FROM org_organization_definitions org, org_organization_definitions xorg
4341 WHERE org.organization_id = p_organizaiton_id
4342 AND xorg.organization_id = p_xfer_organization_id
4343 ;
4344
4345 BEGIN
4346
4347 IF (l_debug = 1)
4348 THEN
4349 print_debug(l_procedure_name, 9);
4350 print_debug('p_api_version_number = ' || p_api_version_number, 9);
4351 print_debug('l_in_api_version_number = ' || l_in_api_version_number, 9);
4352 print_debug('p_init_msg_lst = ' || p_init_msg_lst, 9);
4353 print_debug('l_init_msg_lst = ' || l_init_msg_lst, 9);
4354 print_debug('p_transaction_id = ' || p_transaction_id, 9);
4355 print_debug('p_transaction_temp_id = ' || p_transaction_temp_id, 9);
4356 END IF;
4357
4358 -- Standard call to check for call compatibility
4359 IF NOT fnd_api.compatible_api_call(l_api_version_number,
4360 l_in_api_version_number, l_api_name, g_pkg_name)
4361 THEN
4362 RAISE fnd_api.g_exc_unexpected_error;
4363 END IF;
4364
4365 -- Initialize message list.
4366 IF fnd_api.to_boolean(l_init_msg_lst)
4367 THEN
4368 fnd_msg_pub.initialize;
4369 END IF;
4370
4371
4372 l_stmt_num := 10;
4373
4374 SELECT mmt.inventory_item_id,
4375 mmt.organization_id, mmt.transfer_organization_id, mmt.transaction_date,
4376 mmt.transaction_source_type_id, mmt.transaction_action_id, mmt.fob_point,
4377 mmt.cost_group_id, mmt.transfer_cost_group_id,
4378 mmt.transaction_quantity, mmt.transaction_uom,
4379 mmt.transfer_price, mmt.transportation_cost,
4380 mmt.transfer_transaction_id, msi.primary_uom_code
4381 INTO l_item_id,
4382 l_organization_id, l_xfer_organization_id, l_transaction_date,
4383 l_transaction_source_type_id, l_transaction_action_id, l_fobpoint,
4384 l_xfer_cost_group_id, l_cost_group_id, -- yes, we've to flip CGs
4385 l_transaction_qty, l_transaction_uom,
4386 l_transfer_price, l_transportation_cost,
4387 l_xfer_transaction_id, l_pri_uom
4388 FROM mtl_material_transactions mmt, mtl_system_items_b msi
4389 WHERE mmt.transaction_id = p_transaction_id
4390 AND mmt.inventory_item_id = msi.inventory_item_id
4391 AND mmt.organization_id = msi.organization_id
4392 ;
4393
4394 l_stmt_num := 20;
4395 SELECT MOD(SUM(DECODE(process_enabled_flag, 'Y', 1, 2)), 2)
4396 INTO l_pd_txfr_ind
4397 FROM mtl_parameters mp
4398 WHERE mp.organization_id = l_organization_id
4399 OR mp.organization_id = l_xfer_organization_id;
4400
4401 IF l_pd_txfr_ind <> 1
4402 THEN
4403 RAISE e_p_d_xfer_na;
4404 END IF;
4405
4406
4407 IF (l_debug = 1) THEN
4408 print_debug(l_procedure_name || 'transaction_source_type_id = ' || l_transaction_source_type_id ||
4409 ' transaction_action_id = ' || l_transaction_action_id ||
4410 ' FOB Point = ' || l_fobpoint, 9);
4411 print_debug(l_procedure_name || 'org/xferOrg: ' || l_organization_id || '/' ||
4412 l_xfer_organization_id || ' transfer price: ' || l_transfer_price, 9);
4413 END IF;
4414
4415 /**
4416 * rseshadr -
4417 * Determine if a logical trx needs to be created
4418 * if so, also determine who owns it
4419 * ownership of the logical trx is based on the fob point
4420 * fobpoint: 1 (shipping)
4421 * Receiver is owner of intransit and hence owns the logical trx
4422 * The logical trx is created along with the shipping trx
4423 * fobpoint: 2 (receiving)
4424 * Shipper is owner of intransit and owns the logical trx
4425 * The logical trx is created along with the receiving trx
4426 */
4427
4428 l_stmt_num := 30;
4429 IF( (l_fobpoint = G_FOB_RECEIVING AND l_transaction_action_id = G_ACTION_INTRANSITSHIPMENT) OR
4430 (l_fobpoint = G_FOB_SHIPPING AND l_transaction_action_id = G_ACTION_INTRANSITRECEIPT) )
4431 THEN
4432 /** No logical trx is required, return */
4433 x_return_status := 'S';
4434 x_msg_data := 'No Logical Transaction is required';
4435 print_debug(l_procedure_name || ': No Logical Transaction is required', 9);
4436 RETURN;
4437 END IF;
4438
4439 print_debug(l_procedure_name || ': setting logical txns', 9);
4440
4441 <<ASSIGN_LOGICAL_TXN_TYPES>>
4442 CASE
4443 WHEN (l_fobpoint = 1 AND l_transaction_action_id = G_ACTION_INTRANSITSHIPMENT)
4444 THEN
4445 l_owner_org_id := l_xfer_organization_id;
4446 l_logical_trx_action_id := G_ACTION_LOGICALINTRECEIPT;
4447
4448 CASE l_transaction_source_type_id
4449
4450 WHEN G_SOURCETYPE_INVENTORY
4451 THEN
4452 --
4453 -- FOB = Shipping, source = inventory, Action = Intransit Shipment
4454 -- set trx type to 'Logical Intransit Receipt (59)'
4455 --
4456 l_logical_trx_src_type_id := G_SOURCETYPE_INVENTORY;
4457 l_logical_trx_type_id := G_TYPE_LOGL_INTORG_INTRECEIPT;
4458
4459 WHEN G_SOURCETYPE_INTORDER
4460 THEN
4461 --
4462 -- FOB = Shipping, source = Int. Order, Action = Intransit Shipment
4463 -- set trx type to 'Logical Intransit Shipment (65)'
4464 --
4465 l_logical_trx_src_type_id := G_SOURCETYPE_INTREQ;
4466 l_logical_trx_type_id := G_TYPE_LOGL_INTREQ_INTRECEIPT;
4467
4468 ELSE NULL;
4469
4470 END CASE;
4471
4472 WHEN ( l_fobpoint = 2 AND l_transaction_action_id = G_ACTION_INTRANSITRECEIPT )
4473 THEN
4474
4475 l_owner_org_id := l_xfer_organization_id;
4476 l_logical_trx_action_id := G_ACTION_LOGICALINTSHIPMENT;
4477
4478 CASE l_transaction_source_type_id
4479
4480 WHEN G_SOURCETYPE_INVENTORY
4481 THEN
4482 --
4483 -- FOB = Receipt, source = inventory, Action = Intransit Receipt
4484 -- set trx type to 'Logical Intransit Receipt (59)'
4485 --
4486 l_logical_trx_src_type_id := G_SOURCETYPE_INVENTORY;
4487 l_logical_trx_type_id := G_TYPE_LOGL_INTORG_INTSHIPMENT;
4488
4489 WHEN G_SOURCETYPE_INTREQ
4490 THEN
4491 --
4492 -- FOB = Shipping, source = Int. Req, Action = Intransit Shipment
4493 -- set trx type to 'Logical Intransit Receipt (76)'
4494 --
4495 l_logical_trx_src_type_id := G_SOURCETYPE_INTORDER;
4496 l_logical_trx_type_id := G_TYPE_LOGL_INTORD_INTSHIPMENT;
4497
4498 END CASE;
4499
4500 ELSE
4501 IF (l_debug = 1)
4502 THEN
4503 print_debug(l_procedure_name || 'before raise no_data_found. Invalid trx passed', 9);
4504 END IF;
4505 RAISE no_data_found;
4506
4507 END CASE ASSIGN_LOGICAL_TXN_TYPES;
4508
4509 IF (l_debug = 1) THEN
4510 print_debug(l_procedure_name || ': Logical Txn is, Source: ' || l_logical_trx_src_type_id ||
4511 ' Action: ' || l_logical_trx_action_id ||
4512 ' Type: ' || l_logical_trx_type_id);
4513 END IF;
4514
4515 --
4516 -- Since the logical trx will be created against *Receiving* orgs, we have
4517 -- to do some conversions if necessary.
4518 -- Get Accounting Period
4519 -- Transaction Qty to primary and secondary UOMs.
4520 -- Transfer Price should be converted to Receiving Orgs currency
4521 --
4522
4523 IF (l_debug = 1) THEN
4524 print_debug(l_procedure_name || ': getting accounting period for org: ' || l_owner_org_id ||
4525 ' on txn date: ' || l_transaction_date);
4526 END IF;
4527
4528 /** Get the account period Id of the owner org */
4529 l_stmt_num := 40;
4530 GET_ACCT_PERIOD(
4531 x_return_status => l_return_status,
4532 x_msg_count => l_msg_count,
4533 x_msg_data => l_msg_data,
4534 x_acct_period_id => l_account_period_id,
4535 p_organization_id => l_owner_org_id,
4536 p_transaction_date => l_transaction_date
4537 );
4538
4539 IF (l_return_status <> g_ret_sts_success)
4540 THEN
4541 print_debug(l_procedure_name || ': Failed to get acct period id for org:'||
4542 l_organization_id ||' message '||l_msg_data);
4543 x_return_status := l_return_status;
4544 x_msg_data := l_msg_data;
4545 RETURN;
4546 END IF;
4547
4548 /********************************************************************
4549 * convert the Transaction Qty to the owner_Org's Primary UOM and
4550 * if necessary, secondary UOM.
4551 * Items might have different primary UOM in different orgs (correct?).
4552 * So, first converty the trx qty from trx uom to logical transactions
4553 * owner_Org's primary UOM.
4554 * Then converty the primary to secondary, if necessary.
4555 *********************************************************************/
4556
4557 l_stmt_num := 90;
4558 IF l_fobpoint = G_FOB_RECEIVING
4559 THEN
4560 --
4561 -- Get the details from shipping txn since this logical txn is for
4562 -- shipping org
4563 --
4564 IF (l_debug = 1) THEN
4565 print_debug(l_procedure_name || ': FOB Receipt. So, populating transfer_price from shipping txn');
4566 END IF;
4567
4568 l_stmt_num := 60;
4569 SELECT mmt.transfer_price,
4570 mmt.transaction_quantity, mmt.transaction_uom,
4571 mmt.primary_quantity, msi.primary_uom_code,
4572 mmt.secondary_transaction_quantity, mmt.secondary_uom_code,
4573 mmt.subinventory_code,
4574 -- Bug 5018698: Following columns have been added.
4575 mmt.transaction_source_id, mmt.transaction_source_name, mmt.trx_source_line_id,
4576 mmt.source_code, mmt.source_line_id,
4577 mmt.trx_source_delivery_id, mmt.picking_line_id, mmt.pick_slip_number,
4578 mmt.pick_strategy_id, mmt.pick_rule_id, mmt.pick_slip_date,
4579 mmt.so_issue_account_type, mmt.invoiced_flag,
4580 mmt.currency_code, mmt.currency_conversion_rate,
4581 mmt.currency_conversion_type, mmt.currency_conversion_date,
4582 mmt.intercompany_currency_code, mmt.intercompany_cost,
4583 mmt.intercompany_pricing_option,
4584 mmt.ship_to_location_id, mmt.transportation_cost
4585 INTO l_transfer_price,
4586 l_snd_txn_qty, l_snd_txn_uom,
4587 l_snd_pri_qty, l_snd_pri_uom,
4588 l_snd_sec_qty, l_snd_sec_uom,
4589 l_snd_subinv,
4590 -- Bug 5018698: Following columns have been added.
4591 l_transaction_source_id, l_transaction_source_name, l_trx_source_line_id,
4592 l_source_code, l_source_line_id,
4593 l_trx_source_delivery_id, l_picking_line_id, l_pick_slip_number,
4594 l_pick_strategy_id, l_pick_rule_id, l_pick_slip_date,
4595 l_so_issue_account_type, l_invoiced_flag,
4596 l_snd_currency_code, l_currency_conversion_rate,
4597 l_currency_conversion_type, l_currency_conversion_date,
4598 l_intercompany_currency_code, l_intercompany_cost,
4599 l_intercompany_pricing_option,
4600 l_ship_to_location_id, l_snd_trp_cost
4601 FROM mtl_material_transactions mmt, mtl_system_items_b msi
4602 WHERE mmt.transaction_id = l_xfer_transaction_id
4603 AND mmt.inventory_item_id = msi.inventory_item_id
4604 AND mmt.organization_id = msi.organization_id
4605 ;
4606
4607 --
4608 -- when creating this logical txn, shipping txn already exists.
4609 -- If total quantity is received in the same *txn* UOM as sending txn UOM,
4610 -- then we can directly populate all the qty fields without doing any UOM
4611 -- conversions. Doing just to improve performance!!!
4612 --
4613 l_stmt_num := 100;
4614 IF l_snd_txn_uom <> l_transaction_uom
4615 -- shipping qty and recv. txn uom are same
4616 THEN
4617 -- Do not skip qty conversions
4618 l_skip_qty_conv := 'N';
4619 ELSE
4620 l_skip_qty_conv := 'Y';
4621
4622 l_qty_ratio := l_transaction_qty/l_snd_txn_qty;
4623 l_pri_qty := l_snd_pri_qty * l_qty_ratio;
4624 l_owner_pri_uom := l_snd_pri_uom;
4625 l_sec_qty := l_snd_sec_qty * l_qty_ratio;
4626 l_sec_uom := l_snd_sec_uom;
4627 l_transportation_cost := abs(l_qty_ratio * l_snd_trp_cost); -- Bug 5332813
4628
4629 IF (l_debug = 1)
4630 THEN
4631 IF (l_qty_ratio = 1)
4632 THEN
4633 print_debug(l_procedure_name || ': Fully received');
4634 ELSE
4635 print_debug(l_procedure_name || ': Partially received');
4636 END IF;
4637 END IF; -- l_debug =1
4638
4639 END IF; -- IF l_snd_txn_uom <> l_transaction_uom
4640
4641 ELSIF l_fobpoint = G_FOB_SHIPPING
4642 THEN
4643
4644 --
4645 -- Bug 5018698: This elsif block of code is added.
4646 -- For FOB Ship, we are creating Internal Req/Logical Intransit Receipt (7/15) txn.
4647 --
4648 -- Inventory Material Txns form gets requision# as soon as it sees Internal Req. txn.
4649 -- Earlier, we were setting source_code and txn source id from the shipping line.
4650 -- The values were 'ORDER ENTRY' and mtl_sales_order.sales_order_id respectively.
4651 -- Because of this, form is unable to find the req and throwing No Data Found error and
4652 -- unable to query and logical intransit receipts at all.
4653 --
4654 -- Now we are setting the source_code to 'RCV' and getting the requisition_header_id
4655 -- from oe_order_lines_all table, so that form can query the txn.
4656 -- Txn_Source_Line_Id column should get updated when actual receipt of goods is made.
4657 --
4658 l_source_code := 'RCV';
4659
4660 l_stmt_num := 601;
4661 IF l_transaction_source_type_id in (7,8)
4662 THEN
4663 SELECT
4664 ol.source_document_id -- Requisition_header_id
4665 -- requisition_line_id = ol.source_document_line_id
4666 INTO
4667 l_transaction_source_id
4668 FROM mtl_material_transactions mmt, oe_order_lines_all ol
4669 WHERE mmt.transaction_id = p_transaction_id
4670 AND ol.line_id = mmt.trx_source_line_id
4671 ;
4672 ELSE
4673 l_transaction_source_id := NULL;
4674 END IF;
4675
4676 IF (l_debug = 1) THEN
4677 print_debug(l_procedure_name || ': SourceDoc/ReqHdrId: ' || l_source_code ||'/'||l_transaction_source_id);
4678 END IF; -- IF l_fobpoint = G_FOB_RECEIVING
4679
4680 END IF; -- IF l_fobpoint = G_FOB_RECEIVING
4681
4682
4683 l_stmt_num := 110;
4684 IF l_skip_qty_conv = 'N' /* Always 'N' for FOB Shipping */
4685 THEN
4686
4687 IF (l_debug = 1) THEN
4688 print_debug(l_procedure_name || ': getting primary and secondary uom flags.');
4689 END IF;
4690
4691 l_stmt_num := 120;
4692 SELECT primary_uom_code, tracking_quantity_ind, secondary_default_ind, secondary_uom_code
4693 INTO l_owner_pri_uom, l_tracking_quantity_ind, l_secondary_default_ind, l_sec_uom
4694 FROM mtl_system_items_b
4695 WHERE organization_id = l_owner_org_id
4696 AND inventory_item_id = l_item_id;
4697
4698 IF (l_transaction_uom <> l_owner_pri_uom)
4699 THEN
4700
4701 IF (l_debug = 1) THEN
4702 print_debug(l_procedure_name || ': calling INV_CONVERT.inv_um_convert (' || l_stmt_num || '): '
4703 || 'converting from txnUOM: ' || l_transaction_uom || ' to primaryUOM: ' || l_owner_pri_uom, 9);
4704 END IF;
4705
4706 l_stmt_num := 130;
4707 l_pri_qty := INV_CONVERT.INV_UM_CONVERT
4708 ( item_id => l_item_id
4709 , lot_number => NULL
4710 , organization_id => l_owner_org_id
4711 , precision => 5
4712 , from_quantity => l_transaction_qty
4713 , from_unit => l_transaction_uom
4714 , to_unit => l_owner_pri_uom
4715 , from_name => NULL
4716 , to_name => NULL);
4717
4718 IF (l_pri_qty = -99999)
4719 THEN
4720 -- log message
4721 RAISE e_uom_conversion_error;
4722 END IF;
4723
4724 ELSE
4725 l_pri_qty := l_transaction_qty;
4726 END IF;
4727
4728 IF (l_tracking_quantity_ind <> 'P')
4729 AND (l_secondary_default_ind IN ('F', 'D'))
4730 AND (l_owner_pri_uom <> l_sec_uom)
4731 THEN
4732
4733 IF (l_debug = 1) THEN
4734 print_debug(l_procedure_name || ': calling INV_CONVERT.inv_um_convert (' || l_stmt_num || '): '
4735 || 'converting from primaryUOM: ' || l_owner_pri_uom|| ' to secUOM: ' || l_sec_uom, 9);
4736 END IF;
4737
4738 l_stmt_num := 140;
4739 l_sec_qty := INV_CONVERT.INV_UM_CONVERT
4740 ( item_id => l_item_id
4741 , lot_number => NULL
4742 , organization_id => l_owner_org_id
4743 , precision => 5
4744 , from_quantity => l_transaction_qty
4745 , from_unit => l_owner_pri_uom
4746 , to_unit => l_sec_uom
4747 , from_name => NULL
4748 , to_name => NULL);
4749
4750 IF (l_sec_qty = -99999)
4751 THEN
4752 -- log message
4753 RAISE e_uom_conversion_error;
4754 END IF;
4755
4756 IF (l_debug = 1) THEN
4757 print_debug(l_procedure_name || ': calling INV_CONVERT.WITHIN_DEVIATION', 9);
4758 END IF;
4759
4760 -- Validate the quantitioes within deviation :
4761 l_stmt_num := 150;
4762 l_are_qties_valid := INV_CONVERT.within_deviation(
4763 p_organization_id => l_owner_org_id
4764 , p_inventory_item_id => l_item_id
4765 , p_lot_number => NULL
4766 , p_precision => 5
4767 , p_quantity => ABS(l_pri_qty)
4768 , p_uom_code1 => l_owner_pri_uom
4769 , p_quantity2 => ABS(l_sec_qty)
4770 , p_uom_code2 => l_sec_uom)
4771 ;
4772
4773 IF (l_are_qties_valid = 0)
4774 THEN
4775 -- dbms_output.put_line('INV_CONVERT.within_deviation (ERROR) '|| l_error_exp );
4776 IF (l_debug = 1) THEN
4777 print_debug(l_procedure_name || ': INV_CONVERT.within_deviation (ERROR)', 9);
4778 print_debug(' l_pri_qty: ' || l_pri_qty || ' l_owner_pri_uom: ' || l_owner_pri_uom, 9);
4779 print_debug(' l_sec_qty: ' || l_sec_qty || ' l_sec_uom: ' || l_sec_uom, 9);
4780 print_debug(' l_item_id: ' || l_item_id || ' l_owner_org_id: ' || l_owner_org_id, 9);
4781 END IF;
4782 RAISE FND_API.G_EXC_ERROR;
4783 END IF;
4784
4785 -- dbms_output.put_line('INV_CONVERT.within_deviation (PASS) ');
4786 IF (l_debug = 1) THEN
4787 inv_log_util.trace(l_procedure_name || ': INV_CONVERT.within_deviation (PASS)' , 9);
4788 END IF;
4789
4790 ELSE
4791 l_sec_qty := NULL;
4792 l_sec_uom := NULL;
4793 END IF;
4794 END IF;
4795 --
4796 -- End of qty conversions
4797 --
4798
4799 /********************************************************************
4800 ** Transfer_Price and Transportation Cost conversion
4801 ** For FOB Shipping: Shipping Orgs currency to Receiving Orgs currency
4802 ** For FOB Receiving: we can directly pickup from shipping txn.
4803 ********************************************************************/
4804
4805 --
4806 -- Get from and to OUs. If they are different then only do currency conversion.
4807 -- Here l_owner_ou_id is the OU Id of logical txn owner organizaiton.
4808 -- l_organization_id: sending org for fob ship,
4809 -- receiving org for fob receipt.
4810 --
4811 l_stmt_num := 61;
4812 OPEN c_from_to_ou (l_organization_id, l_owner_org_id);
4813 FETCH c_from_to_ou INTO l_ou_id, l_sob_id, l_owner_ou_id, l_owner_sob_id;
4814 CLOSE c_from_to_ou;
4815
4816 --
4817 -- Get base currency of sending org (fob ship) or receiving org (fob receipt) org
4818 --
4819 SELECT currency_code
4820 INTO l_currency_code
4821 FROM gl_sets_of_books
4822 WHERE set_of_books_id = l_sob_id;
4823
4824
4825 IF (l_debug = 1) THEN
4826 print_debug(l_procedure_name || ': doing transfer_price currency conversion, if necessary. txn/OwnerSOBId: ' ||
4827 l_sob_id||'/'||l_owner_sob_id);
4828 END IF;
4829 --
4830 -- Following call converts transfer_price from Receiving OU currency (l_currency_code)
4831 -- to funcational currency of Receiving OU (p_owner_ou_id).
4832 --
4833 IF l_sob_id <> l_owner_sob_id
4834 THEN
4835
4836 IF (l_debug = 1) THEN
4837 print_debug(l_procedure_name || ': SOBs are different. currSOB/ownerSOB: ' ||
4838 l_sob_id ||'/'|| l_owner_sob_id || '. ownerOUId: ' || l_owner_ou_id);
4839 END IF;
4840
4841 l_curr_conversion_rate := INV_TRANSACTION_FLOW_PUB.convert_currency(
4842 p_org_id => l_owner_ou_id
4843 , p_transfer_price => 1
4844 , p_currency_code => l_currency_code
4845 , p_transaction_date => l_transaction_date
4846 , p_logical_txn => 'Y' --bug#13965450
4847 , x_functional_currency_code => l_owner_currency_code
4848 , x_return_status => x_return_status
4849 , x_msg_data => x_msg_data
4850 , x_msg_count => x_msg_count
4851 );
4852
4853 IF (l_debug = 1) THEN
4854 print_debug(l_procedure_name || ': currConvRate from curr '|| l_currency_code ||
4855 ' to ' || l_owner_currency_code || ' is: ' || l_curr_conversion_rate);
4856 END IF;
4857
4858 IF ( x_return_status <> G_RET_STS_SUCCESS )
4859 THEN
4860 print_debug(l_procedure_name || ': Error from INV_TRANSACTION_FLOW_PUB.convert_currency: ' ||
4861 x_msg_data, 9);
4862 IF x_return_status = FND_API.G_RET_STS_ERROR
4863 THEN
4864 RAISE FND_API.G_EXC_ERROR;
4865 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4866 THEN
4867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4868 END IF;
4869 END IF;
4870
4871 -- Bug 5326088: Default to 1 otherwise it is NULL and hence transfer price is NULL.
4872 ELSE
4873 l_curr_conversion_rate := 1;
4874 END IF;
4875
4876 l_stmt_num := 50;
4877 IF l_fobpoint = G_FOB_RECEIVING
4878 THEN
4879 --
4880 -- Since the logical trx being created against *shipping* orgs, we can
4881 -- get the transfer price from shipping transaction.
4882 -- We already got it above with stmt number 60.
4883 --
4884 -- But, we still need to convert transportation cost. We cannot directly
4885 -- pickup transportation cost from shipping transaction, since qty can
4886 -- be received mutliple times i.e., partially. Transportation cost on
4887 -- receiving transaction is already prorated. So, we just need to convert
4888 -- it from receiving currency to shipping currency.
4889 --
4890 -- Bug 5332813: using conversion rate from shipping txn. Then dividing the
4891 -- transportation_cost on the receiving txn (in rcv currency) with conversion rate
4892 -- to derive in shipping orgs currency. Just working backwards to avoid decimal dust.
4893 --
4894 IF l_skip_qty_conv = 'N' AND nvl(l_currency_conversion_rate, 0) <> 0
4895 THEN
4896 l_transportation_cost := l_transportation_cost / l_currency_conversion_rate;
4897 END IF;
4898
4899 ELSE
4900
4901 --
4902 -- In case of FOB Shipment, logical transaction will be created for
4903 -- receiving org. So, convert transfer_price from sending org currency
4904 -- to receiving orgs currency, if they are different.
4905 -- Conversion is only needed when transfers across OUs.
4906 --
4907 IF (l_debug = 1) THEN
4908 print_debug(l_procedure_name || ': FOB Shipment. So, doing transfer_price UOM (not Currency) conversion, if necessary. Transfer Price at this point is ' || l_transfer_price);
4909 END IF;
4910
4911 --
4912 -- First do uom conversion and then currency conversion for transfer price.
4913 --
4914
4915 IF l_pri_uom <> l_owner_pri_uom
4916 THEN
4917
4918 IF (l_debug = 1) THEN
4919 print_debug(l_procedure_name ||
4920 ': First, doing UOM conv. from/to: ' || l_pri_uom ||'/'|| l_owner_pri_uom ||
4921 ' tp/trp: ' || l_transfer_price ||'/'|| l_transportation_cost);
4922 END IF;
4923
4924 INV_CONVERT.inv_um_conversion(
4925 from_unit => l_pri_uom
4926 , to_unit => l_owner_pri_uom
4927 , item_id => l_item_id
4928 , uom_rate => l_pri_uom_rate
4929 );
4930
4931 IF (l_pri_uom_rate = -99999)
4932 THEN
4933 -- UOM conversion error
4934 RAISE e_uom_conversion_error;
4935 END IF;
4936
4937 ELSE
4938 l_pri_uom_rate := 1;
4939 END IF;
4940
4941
4942 --- l_transfer_price := l_pri_uom_rate * l_transfer_price; /* Bug#5471868 ANTHIYAG 17-Aug-2006 */
4943 l_transfer_price := l_transfer_price / nvl(l_pri_uom_rate, 1); /* Bug#5471868 ANTHIYAG 17-Aug-2006 */
4944 l_transfer_price := l_curr_conversion_rate * l_transfer_price;
4945 l_transportation_cost := l_curr_conversion_rate * l_transportation_cost;
4946
4947 IF (l_debug = 1) THEN
4948 print_debug(l_procedure_name || ': After doing UOM conv. uom conv rate: ' ||
4949 l_pri_uom_rate || ' currConvRate: ' || l_curr_conversion_rate);
4950 END IF;
4951
4952 END IF;
4953
4954 print_Debug(l_procedure_name || ': transfer price/trpCost = ' || l_transfer_price ||
4955 '/' || l_transportation_cost ||
4956 ' ' || nvl(l_owner_currency_code, l_currency_code), 9);
4957
4958
4959 /********************************************************************
4960 ** Determine Costed_Flag and OPM_costed_flag column values.
4961 ********************************************************************/
4962 l_stmt_num := 160;
4963 SELECT DECODE(NVL(process_enabled_flag, 'N'), 'N', 'N', NULL),
4964 DECODE(NVL(process_enabled_flag, 'N'), 'Y', 'N', NULL)
4965 INTO l_costed_flag, l_opm_costed_flag
4966 FROM mtl_parameters
4967 WHERE organization_id = l_owner_org_id;
4968
4969
4970 IF (l_debug = 1) THEN
4971 print_debug(l_procedure_name || ': All set to insert logical txn into MMT');
4972 END IF;
4973 /** we have all values, insert into MMT */
4974
4975 l_stmt_num := 170;
4976 INSERT INTO mtl_material_transactions
4977 (TRANSACTION_ID,
4978 ORGANIZATION_ID,
4979 INVENTORY_ITEM_ID,
4980 REVISION,
4981 SUBINVENTORY_CODE,
4982 LOCATOR_ID,
4983 TRANSACTION_TYPE_ID,
4984 TRANSACTION_ACTION_ID,
4985 TRANSACTION_SOURCE_TYPE_ID,
4986 TRANSACTION_SOURCE_ID,
4987 TRANSACTION_SOURCE_NAME,
4988 TRANSACTION_QUANTITY,
4989 TRANSACTION_UOM,
4990 TRANSACTION_DATE,
4991 ACCT_PERIOD_ID,
4992 DISTRIBUTION_ACCOUNT_ID,
4993 COSTED_FLAG,
4994 OPM_COSTED_FLAG,
4995 ACTUAL_COST,
4996 INVOICED_FLAG,
4997 TRANSACTION_COST,
4998 CURRENCY_CODE,
4999 CURRENCY_CONVERSION_RATE,
5000 CURRENCY_CONVERSION_TYPE,
5001 CURRENCY_CONVERSION_DATE,
5002 PM_COST_COLLECTED,
5003 TRX_SOURCE_LINE_ID,
5004 SOURCE_CODE,
5005 SOURCE_LINE_ID,
5006 TRANSFER_TRANSACTION_ID,
5007 TRANSFER_ORGANIZATION_ID,
5008 TRANSFER_SUBINVENTORY,
5009 TRANSFER_LOCATOR_ID,
5010 COST_GROUP_ID,
5011 TRANSFER_COST_GROUP_ID,
5012 SHIP_TO_LOCATION_ID,
5013 TRANSACTION_MODE,
5014 TRANSACTION_BATCH_ID,
5015 TRANSACTION_BATCH_SEQ,
5016 LPN_ID,
5017 PARENT_TRANSACTION_ID,
5018 LAST_UPDATE_DATE,
5019 LAST_UPDATED_BY,
5020 CREATION_DATE,
5021 CREATED_BY,
5022 TRANSACTION_SET_ID,
5023 EXPENDITURE_TYPE,
5024 TRANSFER_PRICE,
5025 LOGICAL_TRANSACTION,
5026 LOGICAL_TRANSACTIONS_CREATED,
5027 FOB_POINT,
5028 TRANSPORTATION_COST,
5029 TRANSPORTATION_DIST_ACCOUNT,
5030 SHIPMENT_NUMBER,
5031 TRANSACTION_REFERENCE,
5032 QUANTITY_ADJUSTED,
5033 TRANSFER_ORGANIZATION_TYPE,
5034 ORGANIZATION_TYPE,
5035 OWNING_ORGANIZATION_ID,
5036 PLANNING_ORGANIZATION_ID,
5037 XFR_PLANNING_ORGANIZATION_ID,
5038 PRIMARY_QUANTITY,
5039 SECONDARY_UOM_CODE,
5040 SECONDARY_TRANSACTION_QUANTITY,
5041 RCV_TRANSACTION_ID,
5042 OWNING_TP_TYPE,
5043 XFR_OWNING_ORGANIZATION_ID,
5044 TRANSFER_OWNING_TP_TYPE,
5045 PLANNING_TP_TYPE,
5046 TRANSFER_PLANNING_TP_TYPE,
5047 -- Bug 5018698: Following columns have been added.
5048 INTERCOMPANY_COST,
5049 INTERCOMPANY_PRICING_OPTION,
5050 INTERCOMPANY_CURRENCY_CODE,
5051 TRX_SOURCE_DELIVERY_ID,
5052 PICKING_LINE_ID,
5053 PICK_SLIP_NUMBER,
5054 PICK_STRATEGY_ID,
5055 PICK_RULE_ID,
5056 PICK_SLIP_DATE,
5057 INTRANSIT_ACCOUNT, -- Bug 5018698
5058 SO_ISSUE_ACCOUNT_TYPE
5059 )
5060 SELECT
5061 mtl_material_transactions_s.nextval, -- transaction_id
5062 l_owner_org_id, -- organization_id
5063 mmt.inventory_item_id,
5064 mmt.revision,
5065 decode(mmt.fob_point, G_FOB_RECEIVING, l_snd_subinv,
5066 mmt.transfer_subinventory), -- subinv_code of owner org i.e., shipping org subinv
5067 mmt.transfer_locator_id, -- locator_id of owner org
5068 l_logical_trx_type_id, -- transaction_type_id
5069 l_logical_trx_action_id, -- transaction_action_id
5070 -- Bug 4898549: replaced following line with local variable.
5071 -- mmt.transaction_source_type_id, -- transaction_source_id
5072 l_logical_trx_src_type_id,
5073 l_transaction_source_id,
5074 l_transaction_source_name,
5075 abs(mmt.transaction_quantity), -- transaction_quantity
5076 mmt.transaction_uom, -- transaction_uom
5077 mmt.transaction_date, -- transaction_date
5078 l_account_period_id,
5079 null, -- distribution_account_id null for now
5080 l_costed_flag, -- costed_flag
5081 l_opm_costed_flag, -- opm_costed_flag
5082 mmt.actual_cost,
5083 decode(mmt.fob_point, G_FOB_RECEIVING, l_invoiced_flag,
5084 mmt.invoiced_flag),
5085 mmt.transaction_cost,
5086 l_owner_currency_code,
5087 l_currency_conversion_rate,
5088 l_currency_conversion_type,
5089 l_currency_conversion_date,
5090 mmt.pm_cost_collected,
5091 decode(mmt.fob_point, G_FOB_RECEIVING, l_trx_source_line_id, mmt.trx_source_line_id),
5092 l_source_code,
5093 decode(mmt.fob_point, G_FOB_RECEIVING, l_source_line_id, mmt.source_line_id),
5094 mmt.transaction_id, -- transfer_transaction_id
5095 mmt.organization_id, -- transfer_organization_id
5096 mmt.subinventory_code, -- transfer_subinventory
5097 mmt.locator_id, -- transfer_locator_id
5098 l_cost_group_id, -- cost_group_id
5099 l_xfer_cost_group_id,
5100 l_ship_to_location_id,
5101 mmt.transaction_mode,
5102 mmt.transaction_batch_id,
5103 mmt.transaction_batch_seq,
5104 mmt.lpn_id,
5105 mmt.transaction_id, -- parent_transaction_id
5106 mmt.last_update_date,
5107 mmt.last_updated_by,
5108 mmt.creation_date,
5109 mmt.created_by,
5110 mmt.transaction_set_id, -- should we set this here?
5111 l_expenditure_type,
5112 l_transfer_price,
5113 1, -- logical_transaction it is!
5114 null, -- logical_transactions_created set it to null
5115 l_fobpoint,
5116 l_transportation_cost,
5117 mmt.transportation_dist_account,
5118 mmt.shipment_number,
5119 mmt.transaction_reference,
5120 mmt.quantity_adjusted, -- in Recv Orgs UOM for FOB Shipping
5121 mmt.organization_type, -- transfer_organization_type.
5122 mmt.transfer_organization_type, -- organization_type. xxx how to get this???
5123 l_owner_org_id, -- owning_organization_id
5124 mmt.xfr_planning_organization_id,
5125 mmt.planning_organization_id,
5126 abs(l_pri_qty),
5127 l_sec_uom,
5128 l_sec_qty,
5129 mmt.rcv_transaction_id,
5130 mmt.transfer_owning_tp_type,
5131 mmt.owning_organization_id,
5132 mmt.owning_tp_type,
5133 mmt.transfer_planning_tp_type,
5134 mmt.planning_tp_type,
5135 -- Bug 5018698: Following columns have been added.
5136 l_intercompany_cost,
5137 l_intercompany_pricing_option,
5138 l_intercompany_currency_code,
5139 l_trx_source_delivery_id,
5140 l_picking_line_id,
5141 l_pick_slip_number,
5142 l_pick_strategy_id,
5143 l_pick_rule_id,
5144 l_pick_slip_date,
5145 mmt.intransit_account, -- Bug 5018698
5146 l_so_issue_account_type
5147 FROM mtl_material_transactions mmt
5148 WHERE mmt.transaction_id = p_transaction_id;
5149
5150 x_return_status := g_ret_sts_success;
5151
5152 IF (l_debug = 1)
5153 THEN
5154 print_debug(l_procedure_name || ': After mmt insert', 9);
5155 END IF;
5156
5157 /** rseshadr TBD -
5158 * should we use the project_id from the current trx? or
5159 * do we get it from the other side of the trx, if available?
5160 * or find it ourselves based on owner_org_id and trx type?
5161 * For I Phase p/d transfers from or to a Project Org is disabled
5162 *
5163 * umoogala: since CG is already stamped on the shipping row, we can
5164 * use that. Did that in the query against mmt.
5165 *
5166 IF (true)
5167 THEN
5168 INV_LOGICAL_TRANSACTIONS_PUB.get_default_costgroup(
5169 x_return_status => l_return_status
5170 , x_msg_count => l_msg_count
5171 , x_msg_data => l_msg_data
5172 , x_cost_group_id => l_cost_group_id
5173 , p_organization_id => l_owner_org_id);
5174 ELSE
5175 INV_LOGICAL_TRANSACTIONS_PUB.get_project_costgroup(
5176 x_return_status => l_return_status
5177 , x_msg_count => l_msg_count
5178 , x_msg_data => l_msg_data
5179 , x_cost_group_id => l_cost_group_id
5180 , p_project_id => l_project_id
5181 , p_organization_id => l_owner_org_id);
5182 END IF;
5183
5184 IF (l_return_status <> G_RET_STS_SUCCESS)
5185 THEN
5186 IF (l_debug = 1)
5187 THEN
5188 print_debug(l_procedure_name || 'get_default_costgroup returns error', 9);
5189 print_debug(l_procedure_name || 'l_msg_data = ' || l_msg_data, 9);
5190 END IF;
5191 FND_MESSAGE.SET_NAME('INV', 'INV_ERROR_GET_COST_GROUP');
5192 FND_MSG_PUB.ADD;
5193 RAISE FND_API.G_EXC_ERROR;
5194 END IF;
5195 */
5196 EXCEPTION
5197 WHEN no_data_found
5198 THEN
5199 x_return_status := G_RET_STS_ERROR;
5200 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => x_msg_data);
5201
5202 IF (l_debug = 1)
5203 THEN
5204 print_debug(l_procedure_name || ' (' || l_stmt_num ||'): no_data_found error', 9);
5205 print_debug(l_procedure_name || 'SQL Error ' || '(' || l_stmt_num || '): ' || Sqlerrm(SQLCODE), 9);
5206 END IF;
5207
5208 WHEN OTHERS
5209 THEN
5210 x_return_status := G_RET_STS_UNEXP_ERROR;
5211
5212 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5213 THEN
5214 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_procedure_name);
5215 END IF;
5216
5217 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count, p_data => x_msg_data);
5218 IF (l_debug = 1)
5219 THEN
5220 print_debug(l_procedure_name || ' (' || l_stmt_num ||'): others error', 9);
5221 print_debug(l_procedure_name || 'SQL Error ' || '(' || l_stmt_num || '): ' || Sqlerrm(SQLCODE), 9);
5222 END IF;
5223
5224 END create_opm_disc_logical_trx;
5225
5226
5227 END INV_LOGICAL_TRANSACTIONS_PUB;