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