[Home] [Help]
PACKAGE BODY: APPS.INV_TURNS
Source
1 PACKAGE BODY INV_TURNS as
2 /* $Header: INVTRNIB.pls 120.2 2006/06/09 09:34:26 srayadur noship $ */
3
4
5 PROCEDURE get_1st_onhand (p_organization_id IN NUMBER,
6 p_acct_period_id IN NUMBER,
7 p_inventory_item_id IN NUMBER,
8 p_prev_onhand OUT NOCOPY NUMBER,
9 p_return_status OUT NOCOPY NUMBER)
10 IS
11 l_period_start_date DATE;
12 l_schedule_close_date DATE;
13 BEGIN
14 SELECT period_start_date,
15 schedule_close_date
16 INTO l_period_start_date,
17 l_schedule_close_date
18 FROM ORG_ACCT_PERIODS
19 WHERE acct_period_id = p_acct_period_id;
20
21 SELECT ROUND(SUM(NVL(MTA.BASE_TRANSACTION_VALUE, 0)),2) txn_val
22 INTO p_prev_onhand
23 FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_TRANSACTION_ACCOUNTS MTA
24 WHERE (MTA.accounting_line_type = 1 OR MTA.accounting_line_type =
25 DECODE(MMT.transaction_action_id, 2, 99, 3, 99, 1))
26 AND SIGN(MTA.primary_quantity) =
27 DECODE(MMT.transaction_action_id, 2,
28 SIGN(MMT.primary_quantity), SIGN(MTA.primary_quantity))
29 AND MTA.organization_id + 0 = p_organization_id
30 AND MTA.transaction_id = DECODE(MMT.transaction_action_id,2,
31 DECODE(SIGN(MMT.primary_quantity), -1, MMT.transaction_id,
32 MMT.transfer_transaction_id),
33 3, DECODE(SIGN(MMT.primary_quantity), -1, MMT.transaction_id,
34 MMT.transfer_transaction_id), MMT.transaction_id)
35 AND MMT.transaction_date <= l_schedule_close_date + 1
36 AND MMT.transaction_date >= l_period_start_date
37 AND MMT.transaction_type_id <> 25
38 AND MMT.organization_id = p_organization_id
39 AND MMT.transaction_id = MTA.transaction_id
40 AND MMT.inventory_item_id = MTA.inventory_item_id
41 AND MMT.inventory_item_id = p_inventory_item_id
42 -- CSHEU ADDED TWO LINES HERE
43 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
44 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
45 ;
46
47 -- AND MMT.transaction_date <= l_schedule_close_date + 1 : adding 1 in this statement
48 -- to avoid the use of trunc for transaction_date
49
50 EXCEPTION
51 WHEN OTHERS
52 THEN
53 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
54 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
55 THEN
56 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'GET_1ST_ONHAND');
57 END IF;
58 END get_1st_onhand;
59
60 PROCEDURE get_max_period (p_organization_id IN NUMBER,
61 p_inventory_item_id IN NUMBER,
62 p_acct_period_id IN NUMBER,
63 p_onhand IN NUMBER,
64 p_wip IN NUMBER,
65 p_intransit IN NUMBER,
66 p_prev_onhand OUT NOCOPY NUMBER,
67 p_prev_wip OUT NOCOPY NUMBER,
68 p_prev_intransit OUT NOCOPY NUMBER,
69 p_return_status OUT NOCOPY VARCHAR2 )
70 IS
71 BEGIN
72 SELECT onhand,
73 wip,
74 intransit
75 INTO p_prev_onhand,
76 p_prev_wip,
77 p_prev_intransit
78 FROM mtl_bis_inv_by_period
79 WHERE organization_id = p_organization_id
80 AND inventory_item_id = p_inventory_item_id
81 AND acct_period_id =
82 (SELECT max(acct_period_id)
83 FROM mtl_bis_inv_by_period
84 WHERE organization_id = p_organization_id
85 AND inventory_item_id = p_inventory_item_id
86 AND acct_period_id < p_acct_period_id);
87
88 p_return_status := FND_API.G_RET_STS_SUCCESS;
89
90 EXCEPTION
91 WHEN NO_DATA_FOUND
92 THEN
93 get_1st_onhand(p_organization_id,
97 p_return_status);
94 p_acct_period_id,
95 p_inventory_item_id,
96 p_prev_onhand,
98
99 p_prev_onhand := nvl(p_onhand - p_prev_onhand,0);
100 p_prev_wip := 0;
101 p_prev_intransit := 0;
102 p_return_status := FND_API.G_RET_STS_SUCCESS;
103 WHEN OTHERS
104 THEN
105 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
106 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
107 THEN
108 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'P_GET_MAX_PERIOD');
109 END IF;
110 END get_max_period;
111
112 PROCEDURE update_bop_values(p_organization_id IN NUMBER,
113 p_inventory_item_id IN NUMBER,
114 p_acct_period_id IN NUMBER,
115 p_prev_onhand IN NUMBER,
116 p_prev_wip IN NUMBER,
117 p_prev_intransit IN NUMBER,
118 p_return_status OUT NOCOPY VARCHAR2 )
119 IS
120 BEGIN
121 UPDATE mtl_bis_inv_by_period
122 SET bop_onhand = nvl(p_prev_onhand,0),
123 bop_wip = nvl(p_prev_wip,0) ,
124 bop_intransit = nvl(p_prev_intransit,0)
125 WHERE organization_id = p_organization_id
126 AND inventory_item_id = p_inventory_item_id
127 AND acct_period_id = p_acct_period_id;
128
129 IF SQL%NOTFOUND
130 THEN
131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
132 END IF;
133
134 p_return_status := FND_API.G_RET_STS_SUCCESS;
135 EXCEPTION
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
137 THEN
138 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
139 WHEN OTHERS
140 THEN
141 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
143 THEN
144 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'P_UPDATE_BOP_VALUES');
145 END IF;
146
147 END update_bop_values;
148
149 PROCEDURE set_prev_values (p_onhand IN NUMBER,
150 p_wip IN NUMBER,
151 p_intransit IN NUMBER,
152 p_prev_onhand OUT NOCOPY NUMBER,
153 p_prev_wip OUT NOCOPY NUMBER,
154 p_prev_intransit OUT NOCOPY NUMBER)
155 IS
156 BEGIN
157 p_prev_onhand := p_onhand;
158 p_prev_wip := p_wip;
159 p_prev_intransit := p_intransit;
160 END set_prev_values;
161
162 PROCEDURE calc_closed_bop_values
163 (p_organization_id IN NUMERIC,
164 p_acct_period_id IN NUMERIC,
165 p_return_status OUT NOCOPY VARCHAR2)
166 IS
167 l_prev_organization_id NUMBER := 0;
168 l_prev_inventory_item_id NUMBER := 0;
169 l_prev_acct_period_id NUMBER := 0;
170 l_prev_onhand NUMBER := NULL;
171 l_prev_wip NUMBER := NULL;
172 l_prev_intransit NUMBER := NULL;
173 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
174
175
176 CURSOR get_closed_period_details
177 IS
178 SELECT mbibp.organization_id,
179 mbibp.acct_period_id,
180 mbibp.inventory_item_id,
181 mbibp.bop_onhand,
182 mbibp.onhand,
183 mbibp.bop_wip,
184 mbibp.wip,
185 mbibp.bop_intransit,
186 mbibp.intransit
187 FROM mtl_bis_inv_by_period mbibp
188 WHERE mbibp.acct_period_id = p_acct_period_id
189 AND mbibp.organization_id = p_organization_id
190 ORDER BY
191 mbibp.organization_id,
192 mbibp.inventory_item_id,
193 mbibp.acct_period_id;
194
195 BEGIN
196 FOR closed_period_details IN get_closed_period_details
197 LOOP
198 IF (l_prev_organization_id <> closed_period_details.organization_id)
199 THEN
200 IF (l_prev_organization_id <> 0) THEN
201 COMMIT;
202 END IF;
203 l_prev_organization_id := closed_period_details.organization_id;
204 l_prev_inventory_item_id := closed_period_details.inventory_item_id;
205 l_prev_acct_period_id := closed_period_details.acct_period_id;
206 get_max_period (closed_period_details.organization_id,
207 closed_period_details.inventory_item_id,
208 closed_period_details.acct_period_id,
209 closed_period_details.onhand,
210 closed_period_details.wip,
211 closed_period_details.intransit,
212 l_prev_onhand,
213 l_prev_wip,
214 l_prev_intransit,
215 l_return_status);
216 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 END IF;
219
220 update_bop_values(closed_period_details.organization_id,
221 closed_period_details.inventory_item_id,
222 closed_period_details.acct_period_id,
223 l_prev_onhand,
224 l_prev_wip,
228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 l_prev_intransit,
226 l_return_status);
227 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
229 END IF;
230
231 set_prev_values (closed_period_details.onhand,
232 closed_period_details.wip,
233 closed_period_details.intransit,
234 l_prev_onhand,
235 l_prev_wip,
236 l_prev_intransit);
237 ELSE --(l_prev_organization_id = closed_period_details.organization_id)
238 IF (l_prev_inventory_item_id <>
239 closed_period_details.inventory_item_id)
240 THEN
241 l_prev_inventory_item_id := closed_period_details.inventory_item_id;
242 l_prev_acct_period_id := closed_period_details.acct_period_id;
243 get_max_period (closed_period_details.organization_id,
244 closed_period_details.inventory_item_id,
245 closed_period_details.acct_period_id,
246 closed_period_details.onhand,
247 closed_period_details.wip,
248 closed_period_details.intransit,
249 l_prev_onhand,
250 l_prev_wip,
251 l_prev_intransit,
252 l_return_status);
253 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256 update_bop_values(closed_period_details.organization_id,
257 closed_period_details.inventory_item_id,
258 closed_period_details.acct_period_id,
259 l_prev_onhand,
260 l_prev_wip,
261 l_prev_intransit,
262 l_return_status);
263 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266 set_prev_values (closed_period_details.onhand,
267 closed_period_details.wip,
268 closed_period_details.intransit,
269 l_prev_onhand,
270 l_prev_wip,
271 l_prev_intransit);
272 ELSE --(l_prev_inventory_item_id = closed_period_details.inventory_item_id)
273
274 IF (l_prev_acct_period_id <> closed_period_details.acct_period_id)
275 THEN
276 l_prev_acct_period_id := closed_period_details.acct_period_id;
277 update_bop_values(closed_period_details.organization_id,
278 closed_period_details.inventory_item_id,
279 closed_period_details.acct_period_id,
280 l_prev_onhand,
281 l_prev_wip,
282 l_prev_intransit,
283 l_return_status);
284 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287 set_prev_values (closed_period_details.onhand,
288 closed_period_details.wip,
289 closed_period_details.intransit,
290 l_prev_onhand,
291 l_prev_wip,
292 l_prev_intransit);
293 ELSE --(l_prev_acct_period_id = closed_period_details.acct_period_id)
294
295 -- Condition not possible ---
296 -- Each new record in the cursor should have a unique combination of the
297 -- foll.: Organization_id, inventory_item_id, acct_period_id
298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
299 END IF;
300 END IF;
301 END IF;
302 END LOOP;
303 COMMIT;
304 p_return_status := l_return_status;
305 EXCEPTION
306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
307 THEN
308 ROLLBACK;
309 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 WHEN OTHERS THEN
311 ROLLBACK;
312 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
314 THEN
315 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'CALC_CLOSED_BOP_VALUES');
316 END IF;
317 END calc_closed_bop_values ;
318
319 -- Should be called just from summarize_value of CSTACPCS
320 -- just before inserting records to mtl_period_summary
321 -- Calculates onhand value for every inventory_item for
322 -- the given org_id and period_id
323
324 PROCEDURE CLOSED_TB (
325 p_organization_id IN NUMBER,
326 p_period_id IN NUMBER,
327 err_msg OUT NOCOPY VARCHAR2 )
328 IS
329 l_user_id NUMBER;
330 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
331
332 BEGIN
333 FND_MSG_PUB.initialize;
334
335 l_user_id := FND_GLOBAL.USER_ID;
336
337 SAVEPOINT CLOSED_TB;
338
339 DELETE FROM MTL_BIS_INV_BY_PERIOD
340 WHERE organization_id = p_organization_id
341 AND acct_period_id = p_period_id;
342
343 INSERT INTO MTL_BIS_INV_BY_PERIOD
347 program_id, program_update_date)
344 (organization_id, acct_period_id, inventory_item_id, onhand, wip,
345 intransit, cogs, last_update_date, last_updated_by, creation_date,
346 created_by, last_update_login, request_id, program_application_id,
348 SELECT p_organization_id, p_period_id, inventory_item_id,
349 ROUND(SUM(NVL((period_end_quantity * period_end_unit_cost),0)),2),
350 NULL, 0, NULL, SYSDATE, l_user_id , SYSDATE, l_user_id, l_user_id,
351 NULL, NULL, NULL, NULL
352 FROM CST_PER_CLOSE_DTLS_V
353 --FROM MTL_PER_CLOSE_DTLS
354 WHERE acct_period_id = p_period_id
355 AND organization_id = p_organization_id
356 GROUP BY organization_id, acct_period_id, inventory_item_id;
357
358 CLOSED_WIP(p_organization_id, p_period_id, l_return_status);
359
360 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
366 END IF;
367
368 CLOSED_COGS(p_organization_id, p_period_id, l_return_status);
369
370 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
371 RAISE FND_API.G_EXC_ERROR;
372 END IF;
373
374 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
376 END IF;
377
378 err_msg := l_return_status;
379
380 COMMIT;
381
382 CALC_CLOSED_BOP_VALUES (p_organization_id, p_period_id, l_return_status) ;
383
384 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
386 END IF;
387
388 err_msg := l_return_status;
389
390 EXCEPTION
391 WHEN FND_API.G_EXC_ERROR THEN
392 ROLLBACK ;
393 err_msg := FND_API.G_RET_STS_ERROR;
394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
395 ROLLBACK;
396 err_msg := FND_API.G_RET_STS_UNEXP_ERROR;
397 WHEN OTHERS THEN
398 err_msg := FND_API.G_RET_STS_UNEXP_ERROR;
399 ROLLBACK;
400 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
401 THEN
402 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'CLOSED_ONHAND_TB');
403 END IF;
404
405 END CLOSED_TB;
406
407 -- Should be called from the inctpc
408
409 PROCEDURE CLOSED_SC (
410 p_organization_id IN NUMBER,
411 p_period_id IN NUMBER,
412 p_last_period_id IN NUMBER,
413 err_msg OUT NOCOPY VARCHAR2
414 ) is
415 l_user_id NUMBER;
416 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
417 -- Changes for bug2856158
418 l_period_start_date DATE;
419 l_schedule_close_date DATE;
420 -- Define a table type parameters
421 Type item_id IS TABLE OF MTL_BIS_INV_BY_PERIOD.inventory_item_id%TYPE;
422 Type item_onhand IS TABLE OF MTL_BIS_INV_BY_PERIOD.onhand%TYPE;
423 -- Local varaible declaration
424 l_item_id item_id;
425 l_item_onhand item_onhand;
426 i NUMBER;
427
428 BEGIN
429 FND_MSG_PUB.initialize;
430 l_user_id := FND_GLOBAL.USER_ID;
431
432 SAVEPOINT CLOSED_SC;
433
434 DELETE FROM MTL_BIS_INV_BY_PERIOD
435 WHERE organization_id = p_organization_id
436 AND acct_period_id = p_period_id;
437 -- Begin changes 2856158
438 SELECT period_start_date,schedule_close_date + 1 - (1/(24*3600))
439 INTO l_period_start_date,l_schedule_close_date
440 FROM ORG_ACCT_PERIODS
441 WHERE organization_id = p_organization_id
442 AND acct_period_id = p_period_id;
443
444 /* INSERT INTO MTL_BIS_INV_BY_PERIOD
445 ( organization_id, acct_period_id, inventory_item_id,
446 onhand, wip, intransit, cogs, last_update_date,
447 last_updated_by, creation_date, created_by,
448 last_update_login, request_id, program_application_id,
449 program_id, program_update_date
450 )
451 SELECT p_organization_id, p_period_id, MTA.inventory_item_id,
452 SUM(NVL(MTA.base_transaction_value,0)) + NVL(MBI.onhand,0),
453 NULL, 0, NULL, SYSDATE, l_user_id, SYSDATE, l_user_id,
454 NULL,NULL,NULL,NULL,NULL
455 FROM MTL_TRANSACTION_ACCOUNTS MTA, MTL_MATERIAL_TRANSACTIONS MMT,
456 ORG_ACCT_PERIODS OAP, MTL_BIS_INV_BY_PERIOD MBI
457 WHERE (MTA.accounting_line_type = 1
458 OR MTA.accounting_line_type =
459 decode(MMT.transaction_action_id, 2, 99, 3, 99, 1))
460 AND sign(MTA.primary_quantity) =
461 decode(MMT.transaction_action_id, 2, sign(MMT.primary_quantity),
462 sign(MTA.primary_quantity))
463 AND MTA.organization_id + 0 = p_organization_id
464 AND MTA.transaction_id = decode(MMT.transaction_action_id, 2,
465 decode(sign(MMT.primary_quantity), -1, MMT.transaction_id,
466 MMT.transfer_transaction_id),3,
467 decode(sign(MMT.primary_quantity), -1, MMT.transaction_id,
468 MMT.transfer_transaction_id),MMT.transaction_id)
469 AND MTA.transaction_date >= OAP.period_start_date
470 AND MTA.transaction_date <= OAP.schedule_close_date + 1 - (1/(24*3600))
471 AND MMT.transaction_date >= OAP.period_start_date
472 AND MMT.transaction_date <= OAP.schedule_close_date
473 + 1 - (1/(24*3600))
474 AND MMT.transaction_type_id <> 25
475 AND MBI.organization_id(+) = p_organization_id
476 AND MBI.acct_period_id(+) = p_last_period_id
477 AND MBI.inventory_item_id(+) = MMT.inventory_item_id
478 AND MMT.inventory_item_id = MTA.inventory_item_id
479 AND MMT.organization_id = p_organization_id
480 AND OAP.organization_id = p_organization_id
481 AND OAP.acct_period_id = p_period_id
485 GROUP BY MTA.organization_id, OAP.acct_period_id, MTA.inventory_item_id,
482 -- CSHEU ADDED TWO LINES HERE
483 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
484 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
486 MBI.onhand; */
487
488 -- performance bug 4951748, sql 14818635
489 -- index hint is added
490
491 SELECT /*+ leading(mmt) index(mmt mtl_material_transactions_n5) */ MTA.inventory_item_id,
492 SUM(NVL(MTA.base_transaction_value,0)) +
493 INV_TURNS.get_mbi_onhand(mta.organization_id,mta.inventory_item_id,p_last_period_id)
494 BULK COLLECT INTO l_item_id,l_item_onhand
495 FROM MTL_TRANSACTION_ACCOUNTS MTA, MTL_MATERIAL_TRANSACTIONS MMT
496 WHERE (MTA.accounting_line_type = 1
497 OR MTA.accounting_line_type =
498 decode(MMT.transaction_action_id, 2, 99, 3, 99, 1))
499 AND sign(MTA.primary_quantity) =
500 decode(MMT.transaction_action_id, 2, sign(MMT.primary_quantity),
501 sign(MTA.primary_quantity))
502 AND MTA.organization_id + 0 = p_organization_id
503 AND MTA.transaction_id = decode(MMT.transaction_action_id, 2,
504 decode(sign(MMT.primary_quantity), -1, MMT.transaction_id,
505 MMT.transfer_transaction_id),3,
506 decode(sign(MMT.primary_quantity), -1, MMT.transaction_id,
507 MMT.transfer_transaction_id),MMT.transaction_id)
508 AND MTA.transaction_date >= l_period_start_date
509 AND MTA.transaction_date <= l_schedule_close_date
510 AND MMT.transaction_date >= l_period_start_date
511 AND MMT.transaction_date <= l_schedule_close_date
512 AND MMT.transaction_type_id <> 25
513 AND MMT.inventory_item_id = MTA.inventory_item_id
514 AND MMT.organization_id = mta.organization_id
515 AND MMT.organization_id = p_organization_id
516 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
517 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
518 GROUP BY MTA.organization_id, MTA.inventory_item_id;
519 IF NVL(l_item_id.FIRST, -1) > 0 THEN /*bug 3180663 - Check l_item_id.FIRST for null*/
520 FORALL i IN l_item_id.FIRST .. l_item_id.LAST
521 INSERT INTO MTL_BIS_INV_BY_PERIOD
522 ( organization_id, acct_period_id, inventory_item_id,
523 onhand, wip, intransit, cogs, last_update_date,
524 last_updated_by, creation_date, created_by,
525 last_update_login, request_id, program_application_id,
526 program_id, program_update_date )
527 values (p_organization_id, p_period_id, l_item_id(i),
528 l_item_onhand(i),NULL, 0, NULL, SYSDATE, l_user_id, SYSDATE,
529 l_user_id,NULL,NULL,NULL,NULL,NULL );
530 END IF; /* bug 3180663*/
531 -- End changes 2856158
532
533 INSERT INTO MTL_BIS_INV_BY_PERIOD
534 ( organization_id, acct_period_id, inventory_item_id,
535 onhand, wip, intransit, cogs, last_update_date,
536 last_updated_by, creation_date, created_by,
537 last_update_login, request_id, program_application_id,
538 program_id, program_update_date
539 )
540 /* Bug 2747076 : modified the below sql as it was giving performance issues
541 in period close program */
542
543 SELECT p_organization_id, p_period_id, O_MBI.inventory_item_id,
544 O_MBI.onhand,
545 NULL, 0, NULL, SYSDATE,
546 l_user_id, SYSDATE, l_user_id,
547 NULL,NULL,NULL,NULL,NULL
548 FROM MTL_BIS_INV_BY_PERIOD O_MBI
549 WHERE O_MBI.organization_id = p_organization_id
550 AND O_MBI.acct_period_id = p_last_period_id
551 AND NOT EXISTS
552 (SELECT 1
553 FROM MTL_BIS_INV_BY_PERIOD I_MBI
554 WHERE I_MBI.organization_id = p_organization_id
555 AND I_MBI.acct_period_id = p_period_id
556 AND I_MBI.inventory_item_id = O_MBI.inventory_item_id);
557
558 /* Replaced these conditions for bug 2747076 :
559
560 AND O_MBI.inventory_item_id NOT IN
561 (SELECT I_MBI.inventory_item_id
562 FROM MTL_BIS_INV_BY_PERIOD I_MBI
563 WHERE I_MBI.organization_id = p_organization_id
564 AND I_MBI.acct_period_id = p_period_id);
565 */
566 CLOSED_WIP(p_organization_id, p_period_id, l_return_status);
567
568 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
569 RAISE FND_API.G_EXC_ERROR;
570 END IF;
571
572 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
574 END IF;
575
576 CLOSED_COGS(p_organization_id, p_period_id, l_return_status);
577
578 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
579 RAISE FND_API.G_EXC_ERROR;
580 END IF;
581
582 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
584 END IF;
585
586 err_msg := l_return_status;
587
588 COMMIT;
589
590 CALC_CLOSED_BOP_VALUES (p_organization_id, p_period_id, l_return_status) ;
591
592 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596 err_msg := l_return_status;
597
601 err_msg := FND_API.G_RET_STS_ERROR;
598 EXCEPTION
599 WHEN FND_API.G_EXC_ERROR THEN
600 ROLLBACK ;
602 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
603 ROLLBACK;
604 err_msg := FND_API.G_RET_STS_UNEXP_ERROR;
605 WHEN OTHERS THEN
606 err_msg := FND_API.G_RET_STS_UNEXP_ERROR;
607 ROLLBACK;
608 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
609 THEN
610 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'CLOSED_ONHAND_SC');
611 END IF;
612
613 END CLOSED_SC;
614
615 PROCEDURE CLOSED_WIP (
616 p_organization_id IN NUMBER,
617 p_period_id IN NUMBER,
618 err_msg OUT NOCOPY VARCHAR2
619 ) is
620 l_user_id NUMBER;
621 l_last_period_id NUMBER;
622 l_last_wip NUMBER;
623 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
624
625 CURSOR GET_CLOSED_WIPS IS
626 select SUM((NVL(WPB.tl_resource_in,0) +
627 NVL(WPB.tl_overhead_in,0) + NVL(WPB.tl_outside_processing_in,0) +
628 NVL(WPB.pl_material_in,0) + NVL(WPB.pl_resource_in,0) +
629 NVL(WPB.pl_overhead_in,0) + NVL(WPB.pl_outside_processing_in,0) +
630 NVL(WPB.pl_material_overhead_in,0))
631 -
632 (NVL(WPB.tl_resource_out,0) + NVL(WPB.tl_overhead_out,0) +
633 NVL(WPB.tl_outside_processing_out,0) + NVL(WPB.pl_material_out,0) +
634 NVL(WPB.pl_material_overhead_out,0) + NVL(WPB.pl_resource_out,0) +
635 NVL(WPB.pl_overhead_out,0) + NVL(WPB.pl_outside_processing_out,0) +
636 NVL(WPB.tl_material_overhead_out,0)+NVL(WPB.tl_material_out,0))
637 -
638 (NVL(WPB.tl_material_var,0) + NVL(WPB.tl_resource_var,0) +
639 NVL(WPB.tl_overhead_var,0) + NVL(WPB.tl_outside_processing_var,0) +
640 NVL(WPB.pl_material_var,0) + NVL(WPB.pl_resource_var,0) +
641 NVL(WPB.pl_overhead_var,0) + NVL(WPB.pl_outside_processing_var,0) +
642 NVL(WPB.tl_material_overhead_var,0) +
643 NVL(WPB.pl_material_overhead_var,0))) balance,
644 WPB.organization_id org_id, WPB.acct_period_id per_id,
645 WE.primary_item_id item_id
646 FROM WIP_PERIOD_BALANCES WPB, WIP_ENTITIES WE
647 WHERE WPB.organization_id = p_organization_id
648 AND WPB.acct_period_id = p_period_id
649 AND WPB.wip_entity_id = WE.wip_entity_id
650 GROUP BY WPB.organization_id, WPB.acct_period_id, WE.primary_item_id;
651 BEGIN
652
653 l_user_id := FND_GLOBAL.USER_ID;
654 l_last_period_id := NULL;
655
656 SELECT MAX(acct_period_id)
657 INTO l_last_period_id
658 FROM org_acct_periods
659 WHERE organization_id = p_organization_id
660 AND acct_period_id < p_period_id;
661
662 FOR GET_CLOSED_WIPS_REC IN GET_CLOSED_WIPS LOOP
663 IF GET_CLOSED_WIPS_REC.item_id is NOT NULL then /*bug 2531269*/
664 l_last_wip := NULL;
665
666 SELECT MAX(acct_period_id)
667 INTO l_last_period_id
668 FROM org_acct_periods
669 WHERE organization_id = GET_CLOSED_WIPS_REC.org_id
670 AND acct_period_id < GET_CLOSED_WIPS_REC.per_id;
671
672 IF l_last_period_id IS NOT NULL THEN
673 BEGIN
674 SELECT NVL(wip,0)
675 INTO l_last_wip
676 FROM MTL_BIS_INV_BY_PERIOD
677 WHERE organization_id = GET_CLOSED_WIPS_REC.org_id
678 AND inventory_item_id = GET_CLOSED_WIPS_REC.item_id
679 AND acct_period_id = l_last_period_id;
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 l_last_wip := 0;
683 END;
684
685 ELSE
686 l_last_wip := 0;
687 END IF;
688
689 UPDATE MTL_BIS_INV_BY_PERIOD
690 SET wip = GET_CLOSED_WIPS_REC.balance + l_last_wip
691 WHERE organization_id = GET_CLOSED_WIPS_REC.org_id
692 AND acct_period_id = GET_CLOSED_WIPS_REC.per_id
693 AND inventory_item_id = GET_CLOSED_WIPS_REC.item_id;
694
695 IF SQL%NOTFOUND THEN
696 INSERT INTO MTL_BIS_INV_BY_PERIOD
697 (organization_id, acct_period_id, inventory_item_id, onhand,
698 wip, intransit, cogs, last_update_date, last_updated_by,
699 creation_date, created_by, last_update_login, request_id,
700 program_application_id, program_id, program_update_date)
701 VALUES (GET_CLOSED_WIPS_REC.org_id, GET_CLOSED_WIPS_REC.per_id,
702 GET_CLOSED_WIPS_REC.item_id, NULL,
703 GET_CLOSED_WIPS_REC.balance + l_last_wip, 0, NULL,
704 SYSDATE, l_user_id, SYSDATE, l_user_id, l_user_id,
705 NULL,NULL,NULL,NULL);
706 END IF;
707 END IF;
708 END LOOP;
709 err_msg := l_return_status;
710 EXCEPTION
711 WHEN OTHERS THEN
712 err_msg := FND_API.G_RET_STS_UNEXP_ERROR;
713 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
714 THEN
715 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'CLOSED_WIP');
716 END IF;
717
718 END CLOSED_WIP;
719
720 PROCEDURE CLOSED_COGS(
721 p_organization_id IN NUMBER,
722 p_period_id IN NUMBER,
723 err_msg OUT NOCOPY VARCHAR2
724 ) is
725 l_user_id NUMBER;
726 l_period_start_date DATE;
727 l_period_close_date DATE;
728
729 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
730
731 CURSOR CLOSED_COGS IS
732 Select item_id, SUM(cogs) cogs
733 from(
734 SELECT mta.inventory_item_id item_id,
735 ROUND(SUM(NVL(mta.base_transaction_value,0)),2) cogs
736 FROM mtl_transaction_accounts mta,
737 mtl_material_transactions mmt
738 WHERE mta.transaction_source_type_id = 2
739 AND mta.organization_id = p_organization_id
740 AND mmt.transaction_id = mta.transaction_id
741 AND mmt.transaction_action_id = 1 -- Issues from stores
745 -- CSHEU ADDED TWO LINES HERE
742 AND mta.base_transaction_value > 0
743 AND mta.transaction_date BETWEEN TRUNC(l_period_start_date)
744 AND (TRUNC(l_period_close_date)+1)
746 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
747 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
748 --
749 GROUP BY mta.inventory_item_id
750 UNION
751 SELECT MTA.inventory_item_id item_id,
752 ROUND(SUM(NVL(MTA.base_transaction_value,0)),2) cogs
753 FROM mtl_transaction_accounts MTA,
754 mtl_material_transactions MMT1,
755 mtl_material_transactions MMT2
756 WHERE MMT2.transaction_source_type_id = 2
757 AND MTA.organization_id = p_organization_id
758 AND MMT1.transaction_id = MTA.transaction_id
759 AND MMT1.PARENT_TRANSACTION_ID = MMT2.transaction_id
760 AND MMT1.organization_id=MMT2.organization_id
761 AND MMT1.logical_transaction = 1
762 AND MMT2.transaction_action_id = 1 -- Issues from stores
763 AND MTA.base_transaction_value > 0
764 AND MTA.transaction_date BETWEEN TRUNC(l_period_start_date) AND (TRUNC(l_period_close_date)+1)
765 AND MMT2.organization_id = NVL(MMT2.owning_organization_id,MMT2.organization_id)
766 AND NVL(MMT2.OWNING_TP_TYPE,2) = 2
767 GROUP BY MTA.inventory_item_id
768
769 )
770 group by item_id;
771 /* mr : mar 22nd 2000
772 SELECT ROUND(SUM(NVL(base_transaction_value,0)),2) cogs,
773 inventory_item_id item_id
774 FROM MTL_TRANSACTION_ACCOUNTS
775 WHERE transaction_source_type_id = 2
776 AND organization_id = p_organization_id
777 AND base_transaction_value > 0
778 AND TRUNC(transaction_date) between
779 trunc(l_period_start_date) AND trunc(l_period_close_date)
780 GROUP BY inventory_item_id; */
781 BEGIN
782 l_user_id := FND_GLOBAL.USER_ID;
783
784 SELECT period_start_date,schedule_close_date
785 INTO l_period_start_date, l_period_close_date
786 FROM org_acct_periods
787 WHERE organization_id = p_organization_id
788 AND acct_period_id = p_period_id;
789
790 FOR CLOSED_COGS_REC IN CLOSED_COGS LOOP
791 UPDATE MTL_BIS_INV_BY_PERIOD
792 SET cogs = CLOSED_COGS_REC.cogs
793 WHERE organization_id = p_organization_id
794 AND acct_period_id = p_period_id
795 AND inventory_item_id = CLOSED_COGS_REC.item_id;
796
797 IF SQL%NOTFOUND THEN
798 INSERT INTO MTL_BIS_INV_BY_PERIOD
799 (organization_id, acct_period_id, inventory_item_id, onhand,
800 wip, intransit, cogs, last_update_date, last_updated_by,
801 creation_date, created_by, last_update_login, request_id,
802 program_application_id, program_id, program_update_date)
803 VALUES (p_organization_id, p_period_id,
804 CLOSED_COGS_REC.item_id, NULL,
805 NULL, 0, CLOSED_COGS_REC.cogs,
806 SYSDATE, l_user_id, SYSDATE, l_user_id, l_user_id,
807 NULL,NULL,NULL,NULL);
808
809 END IF;
810
811 END LOOP;
812
813 err_msg := l_return_status;
814
815 EXCEPTION
816 WHEN OTHERS THEN
817 err_msg := FND_API.G_RET_STS_UNEXP_ERROR;
818 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
819 THEN
820 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'CLOSED_COGS');
821 END IF;
822
823 END CLOSED_COGS;
824
825 PROCEDURE calc_open_bop_values (p_return_status OUT NOCOPY VARCHAR2)
826 IS
827 l_prev_organization_id NUMBER := 0;
828 l_prev_inventory_item_id NUMBER := 0;
829 l_prev_acct_period_id NUMBER := 0;
830 l_prev_onhand NUMBER := NULL;
831 l_prev_wip NUMBER := NULL;
832 l_prev_intransit NUMBER := NULL;
833 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
834
835 CURSOR get_open_period_details
836 IS
837 SELECT mbibp.organization_id,
838 mbibp.acct_period_id,
839 mbibp.inventory_item_id,
840 mbibp.bop_onhand,
841 mbibp.onhand,
842 mbibp.bop_wip,
843 mbibp.wip,
844 mbibp.bop_intransit,
845 mbibp.intransit
846 FROM org_acct_periods oap,
847 mtl_bis_inv_by_period mbibp
848 WHERE oap.open_flag = 'Y'
849 AND mbibp.acct_period_id = oap.acct_period_id
850 AND mbibp.organization_id = oap.organization_id
851 ORDER BY
852 mbibp.organization_id,
853 mbibp.inventory_item_id,
854 mbibp.acct_period_id;
855
856 BEGIN
857 FOR open_period_details IN get_open_period_details
858 LOOP
859 IF (l_prev_organization_id <> open_period_details.organization_id)
860 THEN
861 IF (l_prev_organization_id <> 0) THEN
862 COMMIT;
863 END IF;
864 l_prev_organization_id := open_period_details.organization_id;
865 l_prev_inventory_item_id := open_period_details.inventory_item_id;
866 l_prev_acct_period_id := open_period_details.acct_period_id;
867 get_max_period (open_period_details.organization_id,
868 open_period_details.inventory_item_id,
869 open_period_details.acct_period_id,
870 open_period_details.onhand,
871 open_period_details.wip,
872 open_period_details.intransit,
876 l_return_status);
873 l_prev_onhand,
874 l_prev_wip,
875 l_prev_intransit,
877 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
879 END IF;
880
881 update_bop_values(open_period_details.organization_id,
882 open_period_details.inventory_item_id,
883 open_period_details.acct_period_id,
884 l_prev_onhand,
885 l_prev_wip,
886 l_prev_intransit,
887 l_return_status);
888 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
890 END IF;
891
892 set_prev_values (open_period_details.onhand,
893 open_period_details.wip,
894 open_period_details.intransit,
895 l_prev_onhand,
896 l_prev_wip,
897 l_prev_intransit);
898 ELSE --(l_prev_organization_id = open_period_details.organization_id)
899 IF (l_prev_inventory_item_id <>
900 open_period_details.inventory_item_id)
901 THEN
902 l_prev_inventory_item_id := open_period_details.inventory_item_id;
903 l_prev_acct_period_id := open_period_details.acct_period_id;
904 get_max_period (open_period_details.organization_id,
905 open_period_details.inventory_item_id,
906 open_period_details.acct_period_id,
907 open_period_details.onhand,
908 open_period_details.wip,
909 open_period_details.intransit,
910 l_prev_onhand,
911 l_prev_wip,
912 l_prev_intransit,
913 l_return_status);
914 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916 END IF;
917 update_bop_values(open_period_details.organization_id,
918 open_period_details.inventory_item_id,
919 open_period_details.acct_period_id,
920 l_prev_onhand,
921 l_prev_wip,
922 l_prev_intransit,
923 l_return_status);
924 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
926 END IF;
927 set_prev_values (open_period_details.onhand,
928 open_period_details.wip,
929 open_period_details.intransit,
930 l_prev_onhand,
931 l_prev_wip,
932 l_prev_intransit);
933 ELSE --(l_prev_inventory_item_id = open_period_details.inventory_item_id)
934
935 IF (l_prev_acct_period_id <> open_period_details.acct_period_id)
936 THEN
937 l_prev_acct_period_id := open_period_details.acct_period_id;
938 update_bop_values(open_period_details.organization_id,
939 open_period_details.inventory_item_id,
940 open_period_details.acct_period_id,
941 l_prev_onhand,
942 l_prev_wip,
943 l_prev_intransit,
944 l_return_status);
945 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
947 END IF;
948 set_prev_values (open_period_details.onhand,
949 open_period_details.wip,
950 open_period_details.intransit,
951 l_prev_onhand,
952 l_prev_wip,
953 l_prev_intransit);
954 ELSE --(l_prev_acct_period_id = open_period_details.acct_period_id)
955
956 -- Condition not possible ---
957 -- Each new record in the cursor should have a unique combination of the
958 -- foll.: Organization_id, inventory_item_id, acct_period_id
959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
960 END IF;
961 END IF;
962 END IF;
963 END LOOP;
964 COMMIT;
965 p_return_status := l_return_status;
966 EXCEPTION
967 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
968 THEN
969 ROLLBACK;
970 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
971 WHEN OTHERS THEN
972 ROLLBACK;
973 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
974 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
975 THEN
976 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'CALC_OPEN_BOP_VALUES');
977 END IF;
978 END calc_open_bop_values ;
979
980 /* Process all the closed periods that have not been processed
981 and for which there is no data in MTL_BIS_INV_BY_PERIOD table .
982 This will be a case at the customers who are implementing
983 this procedure for the first time).
987
984 R12 changes: Restricting process orgs in closed_periods cursor itself as
985 this wouldn't be passed further.
986 */
988 PROCEDURE process_closed_periods
989 (p_return_status OUT NOCOPY VARCHAR2) IS
990 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
991 l_last_period_id org_acct_periods.acct_period_id%TYPE := 0;
992
993 /* performance bug 4951748, sql 14818892
994 sql is rewritten to combine two selects from org_acct_periods
995
996 CURSOR closed_periods IS
997 SELECT oap.organization_id,
998 oap.acct_period_id,
999 oap.period_start_date,
1000 oap.schedule_close_date,
1001 mp.primary_cost_method
1002 FROM org_acct_periods oap,
1003 mtl_parameters mp
1004 WHERE oap.organization_id = mp.organization_id
1005 AND ( oap.organization_id, oap.acct_period_id)
1006 IN
1007 (SELECT oap.organization_id, oap.acct_period_id
1008 FROM org_acct_periods oap
1009 WHERE oap.open_flag = 'N'
1010 AND oap.schedule_close_date <= INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,oap.organization_id)
1011 MINUS
1012 SELECT organization_id, acct_period_id
1013 FROM mtl_bis_inv_by_period)
1014 ORDER BY
1015 oap.organization_id,
1016 oap.schedule_close_date asc;
1017 */
1018
1019 CURSOR closed_periods IS
1020 SELECT oap.organization_id,
1021 oap.acct_period_id,
1022 oap.period_start_date,
1023 oap.schedule_close_date,
1024 mp.primary_cost_method
1025 FROM org_acct_periods oap,
1026 mtl_parameters mp
1027 WHERE oap.organization_id = mp.organization_id
1028 AND oap.open_flag = 'N'
1029 AND oap.schedule_close_date <= INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,oap.organization_id)
1030 AND mp.process_enabled_flag <> 'Y' -- Added for R12 uptake. Ignore all data in process orgs.
1031 AND EXISTS -- Changed from NOT EXISTS to EXISTS for Bug 5099039
1032 (SELECT mbibp.acct_period_id, mbibp.organization_id
1033 FROM mtl_bis_inv_by_period mbibp
1034 WHERE mbibp.acct_period_id = oap.acct_period_id
1035 AND mbibp.organization_id = oap.organization_id
1036 AND trunc(mbibp.last_update_date) <= oap.period_close_date ) -- Added this filter to collect data for all orgs
1037 -- that were closed later than prior collection date. This would ensure that any transactions after last collection
1038 -- and prior to closing of that period are collected appropriately.
1039 ORDER BY
1040 oap.organization_id,
1041 oap.schedule_close_date asc;
1042
1043 BEGIN
1044 FOR closed_periods_rec IN closed_periods
1045 LOOP
1046 IF(closed_periods_rec.primary_cost_method IN (2,5,6))
1047 THEN
1048 inv_turns.closed_tb (closed_periods_rec.organization_id,
1049 closed_periods_rec.acct_period_id,
1050 l_return_status);
1051 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1053 END IF;
1054 ELSE
1055 SELECT nvl(MAX(acct_period_id),0)
1056 INTO l_last_period_id
1057 FROM mtl_bis_inv_by_period
1058 WHERE organization_id = closed_periods_rec.organization_id
1059 AND acct_period_id < closed_periods_rec.acct_period_id ;
1060
1061 inv_turns.closed_sc (closed_periods_rec.organization_id,
1062 closed_periods_rec.acct_period_id,
1063 l_last_period_id,
1064 l_return_status);
1065 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1067 END IF;
1068 END IF;
1069 END LOOP;
1070 COMMIT;
1071
1072 p_return_status := l_return_status;
1073 END process_closed_periods ;
1074
1075 /*
1076 R12 changes: Restricting process orgs in open_pers cursor itself as
1077 they wouldn't be passed to the successive cursors using these orgs , acct_periods.
1078 */
1079
1080 PROCEDURE CREATE_OPEN_PERIODS (
1081 ERRBUF OUT NOCOPY VARCHAR2,
1082 RETCODE OUT NOCOPY NUMBER
1083 ) is
1084 l_user_id NUMBER;
1085 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1086 l_organization_id NUMBER := 0;
1087 l_prel_org NUMBER := 0;
1088 l_period_id NUMBER;
1089 l_period_start_date DATE;
1090 l_schedule_close_date DATE;
1091 l_last_period_id NUMBER;
1092 l_txn_value NUMBER;
1093 l_last_start_date DATE;
1094 l_last_close_date DATE;
1095 l_conc_status BOOLEAN;
1096
1097 CURSOR OPEN_PERS IS
1098 SELECT oap.organization_id, oap.acct_period_id, oap.period_start_date,
1099 oap.schedule_close_date
1100 FROM ORG_ACCT_PERIODS oap, mtl_parameters mp
1101 WHERE oap.open_flag = 'Y'
1102 AND oap.organization_id = mp.organization_id
1103 AND mp.process_enabled_flag <> 'Y' -- Added for R12 uptake. Ignore all data in process orgs.
1104 AND INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,oap.organization_id) >= period_start_date
1105 ORDER BY oap.organization_id, oap.schedule_close_date desc;
1106 -- Commented on mar 10th 2000. We shoould process the current period
1110
1107 -- too. (AND schedule_close_date <= sysdate )
1108 -- mar 20th 2000 AND ( trunc(sysdate) BETWEEN period_start_date
1109 -- mar 20th 2000 AND schedule_close_date)
1111 CURSOR OPEN_ONHAND IS
1112 SELECT cql.inventory_item_id,
1113 ROUND(SUM(NVL((cql.layer_quantity * cql.item_cost),0)),2) onhand
1114 FROM cst_quantity_layers cql,
1115 mtl_parameters mp
1116 WHERE cql.organization_id = l_organization_id
1117 AND cql.organization_id = mp.organization_id
1118 AND mp.primary_cost_method IN (2,5,6)
1119 GROUP BY cql.inventory_item_id
1120 UNION ALL
1121 SELECT moq.inventory_item_id,
1122 ROUND(SUM(NVL((moq.transaction_quantity * moq.item_cost),0)),2) onhand
1123 FROM mtl_onhand_qty_cost_v moq,
1124 mtl_parameters mp
1125 WHERE moq.organization_id = l_organization_id
1126 AND moq.organization_id = mp.organization_id
1127 AND mp.primary_cost_method = 1
1128 GROUP BY inventory_item_id;
1129 /* Commented on Oct 25th as the transaction value is not calculated right
1130 SELECT inventory_item_id,
1131 ROUND(SUM(NVL((transaction_quantity * item_cost),0)),2) onhand
1132 FROM mtl_onhand_qty_cost_v
1133 WHERE organization_id = l_organization_id
1134 GROUP BY inventory_item_id; */
1135
1136 CURSOR OPEN_TRNS IS
1137 SELECT l_organization_id, l_period_id,
1138 CIVV.inventory_item_id inv_item_id,
1139 ROUND(SUM(NVL(DECODE(CIVV.intransit_owning_org_id,
1140 CIVV.from_organization_id, CIVV.quantity,
1141 CIVV.to_organization_id,
1142 CIVV.to_org_primary_quantity) * CST.item_cost, 0)),2) it_sum
1143 FROM MTL_PARAMETERS MP1,
1144 MTL_PARAMETERS MP2,
1145 CST_CG_ITEM_COSTS_VIEW CST,
1146 CST_INTRANSIT_VALUE_VIEW CIVV
1147 WHERE CIVV.intransit_owning_org_id = l_organization_id
1148 AND MP1.organization_id = CIVV.from_organization_id
1149 AND MP2.organization_id = CIVV.to_organization_id
1150 AND CST.organization_id = DECODE(CIVV.intransit_owning_org_id,
1151 CIVV.from_organization_id, MP1.cost_organization_id,
1152 CIVV.to_organization_id, MP2.cost_organization_id)
1153 AND CIVV.inventory_item_id = CST.inventory_item_id
1154 AND CIVV.shipped_date <= l_schedule_close_date
1155 AND CST.cost_group_id = 1
1156 GROUP BY CIVV.intransit_owning_org_id,
1157 CIVV.inventory_item_id;
1158
1159 CURSOR OPEN_COGS IS
1160 select inventory_item_id, SUM(cogs_val) cogs_val
1161 from(
1162 SELECT mta.inventory_item_id inventory_item_id,
1163 ROUND(SUM(NVL(mta.base_transaction_value,0)),2) cogs_val
1164 FROM mtl_transaction_accounts mta,
1165 mtl_material_transactions mmt
1166 WHERE mta.transaction_source_type_id = 2
1167 AND mta.organization_id = l_organization_id
1168 AND mmt.transaction_id = mta.transaction_id
1169 AND mmt.transaction_action_id = 1 -- Issues from stores
1170 AND mta.base_transaction_value > 0
1171 AND mta.transaction_date BETWEEN TRUNC(l_period_start_date)
1172 AND (TRUNC(l_schedule_close_date)+1)
1173
1174 -- CSHEU ADDED TWO LINES HERE
1175 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
1176 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
1177 -- CSHEU
1178 GROUP BY mta.inventory_item_id
1179 UNION
1180 SELECT MTA.inventory_item_id inventory_item_id,
1181 ROUND(SUM(NVL(MTA.base_transaction_value,0)),2) cogs_val
1182 FROM mtl_transaction_accounts MTA,
1183 mtl_material_transactions MMT1,
1184 mtl_material_transactions MMT2
1185 WHERE MMT2.transaction_source_type_id = 2
1186 AND MTA.organization_id = l_organization_id
1187 AND MMT1.transaction_id = MTA.transaction_id
1188 AND MMT1.PARENT_TRANSACTION_ID = MMT2.transaction_id
1189 AND MMT1.organization_id=MMT2.organization_id
1190 AND MMT1.logical_transaction = 1
1191 AND MMT2.transaction_action_id = 1 -- Issues from stores
1192 AND MTA.base_transaction_value > 0
1193 AND MTA.transaction_date BETWEEN TRUNC(l_period_start_date) AND (TRUNC(l_schedule_close_date)+1)
1194 AND MMT2.organization_id = NVL(MMT2.owning_organization_id,MMT2.organization_id)
1195 AND NVL(MMT2.OWNING_TP_TYPE,2) = 2
1196 GROUP BY MTA.inventory_item_id
1197 ) group by inventory_item_id;
1198 BEGIN
1199 l_user_id := FND_GLOBAL.USER_ID;
1200
1201 FOR OPEN_PERS_REC IN OPEN_PERS LOOP
1202
1203 l_organization_id := OPEN_PERS_REC.organization_id;
1204 l_period_id := OPEN_PERS_REC.acct_period_id;
1205 l_period_start_date := OPEN_PERS_REC.period_start_date;
1206 l_schedule_close_date := OPEN_PERS_REC.schedule_close_date;
1207
1208 DELETE FROM MTL_BIS_INV_BY_PERIOD
1209 WHERE organization_id = l_organization_id
1210 AND acct_period_id = l_period_id;
1211
1212 IF l_prel_org <> l_organization_id THEN
1213 IF l_prel_org <> 0 THEN
1214 COMMIT;
1215 END IF;
1216 FOR OPEN_ONHAND_REC IN OPEN_ONHAND LOOP
1217 UPDATE MTL_BIS_INV_BY_PERIOD
1218 SET onhand = OPEN_ONHAND_REC.onhand
1219 WHERE organization_id = l_organization_id
1220 AND acct_period_id = l_period_id
1221 AND inventory_item_id = OPEN_ONHAND_REC.inventory_item_id;
1222
1223 IF SQL%NOTFOUND THEN
1224 INSERT INTO MTL_BIS_INV_BY_PERIOD
1228 program_application_id, program_id, program_update_date)
1225 (organization_id, acct_period_id, inventory_item_id, onhand,
1226 wip, intransit, cogs, last_update_date, last_updated_by,
1227 creation_date, created_by, last_update_login, request_id,
1229 VALUES (l_organization_id, l_period_id,
1230 OPEN_ONHAND_REC.inventory_item_id,
1231 OPEN_ONHAND_REC.onhand, 0, NULL,
1232 NULL, SYSDATE, l_user_id, SYSDATE, l_user_id, l_user_id,
1233 NULL,NULL,NULL,NULL);
1234 END IF;
1235
1236 END LOOP;
1237
1238 FOR OPEN_TRNS_REC IN OPEN_TRNS LOOP
1239
1240 UPDATE MTL_BIS_INV_BY_PERIOD
1241 SET intransit = OPEN_TRNS_REC.it_sum
1242 WHERE organization_id = l_organization_id
1243 AND acct_period_id = l_period_id
1244 AND inventory_item_id = OPEN_TRNS_REC.inv_item_id;
1245
1246 IF SQL%NOTFOUND THEN
1247
1248 INSERT INTO MTL_BIS_INV_BY_PERIOD
1249 (organization_id, acct_period_id, inventory_item_id, onhand,
1250 wip, intransit, cogs, last_update_date, last_updated_by,
1251 creation_date, created_by, last_update_login, request_id,
1252 program_application_id, program_id, program_update_date)
1253 VALUES (l_organization_id, l_period_id, OPEN_TRNS_REC.inv_item_id,
1254 NULL, 0, OPEN_TRNS_REC.it_sum,
1255 NULL, SYSDATE, l_user_id, SYSDATE, l_user_id, l_user_id,
1256 NULL,NULL,NULL,NULL);
1257
1258 END IF;
1259
1260 END LOOP;
1261
1262 FOR OPEN_COGS_REC IN OPEN_COGS LOOP
1263
1264 UPDATE MTL_BIS_INV_BY_PERIOD
1265 SET cogs = OPEN_COGS_REC.cogs_val
1266 WHERE organization_id = l_organization_id
1267 AND acct_period_id = l_period_id
1268 AND inventory_item_id = OPEN_COGS_REC.inventory_item_id;
1269
1270 IF SQL%NOTFOUND THEN
1271 INSERT INTO MTL_BIS_INV_BY_PERIOD
1272 (organization_id, acct_period_id, inventory_item_id, onhand,
1273 wip, intransit, cogs, last_update_date, last_updated_by,
1274 creation_date, created_by, last_update_login, request_id,
1275 program_application_id, program_id, program_update_date)
1276 VALUES (l_organization_id, l_period_id, OPEN_COGS_REC.inventory_item_id,
1277 NULL , 0, NULL,
1278 OPEN_COGS_REC.cogs_val, SYSDATE, l_user_id, SYSDATE,
1279 l_user_id, l_user_id, NULL,NULL,NULL,NULL);
1280
1281 END IF;
1282
1283 END LOOP;
1284
1285 ELSE
1286 FIND_TXN_VALUES(l_return_status, l_organization_id, l_period_id,
1287 l_last_period_id, l_last_start_date, l_last_close_date);
1288
1289 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1291 END IF;
1292
1293 FOR OPEN_TRNS_REC IN OPEN_TRNS LOOP
1294
1295 UPDATE MTL_BIS_INV_BY_PERIOD
1296 SET intransit = OPEN_TRNS_REC.it_sum
1297 WHERE organization_id = l_organization_id
1298 AND acct_period_id = l_period_id
1299 AND inventory_item_id = OPEN_TRNS_REC.inv_item_id;
1300
1301 IF SQL%NOTFOUND THEN
1302
1303 INSERT INTO MTL_BIS_INV_BY_PERIOD
1304 (organization_id, acct_period_id, inventory_item_id, onhand,
1305 wip, intransit, cogs, last_update_date, last_updated_by,
1306 creation_date, created_by, last_update_login, request_id,
1307 program_application_id, program_id, program_update_date)
1308 VALUES (l_organization_id, l_period_id, OPEN_TRNS_REC.inv_item_id,
1309 NULL, 0, OPEN_TRNS_REC.it_sum,
1310 NULL, SYSDATE, l_user_id, SYSDATE, l_user_id, l_user_id,
1311 NULL,NULL,NULL,NULL);
1312
1313 END IF;
1314
1315 END LOOP;
1316
1317 FOR OPEN_COGS_REC IN OPEN_COGS LOOP
1318
1319 UPDATE MTL_BIS_INV_BY_PERIOD
1320 SET cogs = OPEN_COGS_REC.cogs_val
1321 WHERE organization_id = l_organization_id
1322 AND acct_period_id = l_period_id
1323 AND inventory_item_id = OPEN_COGS_REC.inventory_item_id;
1324
1325 IF SQL%NOTFOUND THEN
1326 INSERT INTO MTL_BIS_INV_BY_PERIOD
1327 (organization_id, acct_period_id, inventory_item_id, onhand,
1328 wip, intransit, cogs, last_update_date, last_updated_by,
1329 creation_date, created_by, last_update_login, request_id,
1330 program_application_id, program_id, program_update_date)
1331 VALUES (l_organization_id, l_period_id, OPEN_COGS_REC.inventory_item_id,
1332 NULL , 0, NULL,
1333 OPEN_COGS_REC.cogs_val, SYSDATE, l_user_id, SYSDATE,
1334 l_user_id, l_user_id, NULL,NULL,NULL,NULL);
1335
1336 END IF;
1337
1338 END LOOP;
1339
1340 END IF;
1341
1342 l_prel_org := l_organization_id;
1343 l_last_period_id := l_period_id;
1344 l_last_start_date := l_period_start_date;
1345 l_last_close_date := l_schedule_close_date;
1346
1347 END LOOP;
1348
1349 COMMIT;
1350
1351 process_closed_periods (l_return_status);
1352 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1354 END IF;
1355
1356 calc_open_bop_values (l_return_status);
1357 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1359 END IF;
1360
1361 RETCODE := 1;
1362 l_conc_status := fnd_concurrent.set_completion_status('NORMAL','NORMAL') ;
1363
1364 EXCEPTION
1365 WHEN FND_API.G_EXC_ERROR THEN
1366 l_conc_status := fnd_concurrent.set_completion_status('ERROR' ,'ERROR');
1367 RETCODE := 2;
1368
1372
1369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1370 l_conc_status := fnd_concurrent.set_completion_status('ERROR' ,'ERROR');
1371 RETCODE := 2;
1373 WHEN OTHERS THEN
1374 l_conc_status := fnd_concurrent.set_completion_status('ERROR' ,'ERROR');
1375 RETCODE := 2;
1376
1377 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1378 THEN
1379 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'CREATE_OPEN_PERIODS');
1380 END IF;
1381 END CREATE_OPEN_PERIODS;
1382
1383 PROCEDURE FIND_TXN_VALUES (
1384 err_msg OUT NOCOPY varchar2,
1385 p_organization_id IN NUMBER,
1386 p_new_period IN NUMBER,
1387 p_period_id IN NUMBER,
1388 p_period_start_date DATE,
1389 p_schedule_close_date DATE)
1390 IS
1391 l_user_id NUMBER;
1392 l_onhand NUMBER;
1393
1394 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1395
1396 -- performance bug 4951748, sql 14819227
1397 -- index hint is added
1398
1399 CURSOR F_TXN IS
1400 SELECT /*+ index(mmt mtl_material_transactions_n5) */ MTA.inventory_item_id,
1401 ROUND(SUM(NVL(MTA.BASE_TRANSACTION_VALUE, 0)),2) txn_val
1402 FROM MTL_MATERIAL_TRANSACTIONS MMT, MTL_TRANSACTION_ACCOUNTS MTA
1403 WHERE (MTA.accounting_line_type = 1 OR MTA.accounting_line_type =
1404 DECODE(MMT.transaction_action_id, 2, 99, 3, 99, 1))
1405 AND SIGN(MTA.primary_quantity) =
1406 DECODE(MMT.transaction_action_id, 2,
1407 SIGN(MMT.primary_quantity), SIGN(MTA.primary_quantity))
1408 AND MTA.organization_id + 0 = p_organization_id
1409 AND MTA.transaction_id = DECODE(MMT.transaction_action_id,2,
1410 DECODE(SIGN(MMT.primary_quantity), -1, MMT.transaction_id,
1411 MMT.transfer_transaction_id),
1412 3, DECODE(SIGN(MMT.primary_quantity), -1, MMT.transaction_id,
1413 MMT.transfer_transaction_id), MMT.transaction_id)
1414 -- AND MMT.transaction_date <= p_schedule_close_date + 1 -- adding 1 so that we do not have to use trunc for transaction_date
1415 -- Above statment has created bug 3405311
1416 -- As p_schedule_close_date are in truncated form in database . no need to truncate again
1417 AND MMT.transaction_date <= p_schedule_close_date +.99999 -- addition .99999 should be done fixed for bug 3405311
1418 AND MMT.transaction_date >= p_period_start_date
1419 AND MMT.transaction_type_id <> 25
1420 AND MMT.organization_id = p_organization_id
1421 AND MMT.transaction_id = MTA.transaction_id
1422 AND MMT.inventory_item_id = MTA.inventory_item_id
1423
1424 -- CSHEU ADDED TWO LINES HERE
1425 AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
1426 AND NVL(MMT.OWNING_TP_TYPE,2) = 2
1427 -- CSHEU
1428 GROUP BY MTA.inventory_item_id;
1429 -- AND MMT.transaction_date <= p_schedule_close_date + 1 : adding 1 in this statement
1430 -- to avoid the use of trunc for transaction_date
1431
1432 BEGIN
1433 FND_MSG_PUB.initialize;
1434
1435 l_user_id := FND_GLOBAL.USER_ID;
1436
1437 INSERT INTO MTL_BIS_INV_BY_PERIOD
1438 (organization_id, acct_period_id, inventory_item_id, onhand,
1439 wip, intransit, cogs, last_update_date, last_updated_by,
1440 creation_date, created_by, last_update_login, request_id,
1441 program_application_id, program_id, program_update_date)
1442 SELECT organization_id, p_new_period, inventory_item_id, onhand,
1443 0, 0, NULL, SYSDATE, l_user_id, SYSDATE, l_user_id, l_user_id,
1444 NULL,NULL,NULL,NULL
1445 FROM MTL_BIS_INV_BY_PERIOD
1446 WHERE organization_id = p_organization_id
1447 AND acct_period_id = p_period_id
1448 AND onhand IS NOT NULL;
1449
1450 FOR F_TXN_REC IN F_TXN LOOP
1451
1452 UPDATE MTL_BIS_INV_BY_PERIOD
1453 SET onhand = onhand - F_TXN_REC.txn_val,
1454 intransit = 0
1455 WHERE organization_id = p_organization_id
1456 AND acct_period_id = p_new_period
1457 AND inventory_item_id = F_TXN_REC.inventory_item_id;
1458
1459 IF SQL%NOTFOUND THEN
1460
1461 INSERT INTO MTL_BIS_INV_BY_PERIOD
1462 (organization_id, acct_period_id, inventory_item_id, onhand,
1463 wip, intransit, cogs, last_update_date, last_updated_by,
1464 creation_date, created_by, last_update_login, request_id,
1465 program_application_id, program_id, program_update_date)
1466 VALUES (p_organization_id, p_new_period,
1467 F_TXN_REC.inventory_item_id,
1468 (-1) * F_TXN_REC.txn_val,
1469 0, 0, NULL, SYSDATE, l_user_id,
1470 SYSDATE, l_user_id, l_user_id, NULL,NULL,NULL,NULL);
1471 END IF;
1472
1473 END LOOP;
1474
1475 err_msg := l_return_status;
1476
1477 EXCEPTION
1478 WHEN OTHERS THEN
1479 err_msg := FND_API.G_RET_STS_UNEXP_ERROR;
1480 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1481 THEN
1482 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name, 'FIND_TXN_VALUES');
1483 END IF;
1484
1485 END FIND_TXN_VALUES;
1486 --Begin changes 2856158
1487 FUNCTION GET_MBI_ONHAND(x_organization_id NUMBER,
1488 x_inventory_item_id NUMBER,
1489 x_last_period_id NUMBER) return NUMBER IS
1490 l_onhand_qty number := 0.0;
1491 begin
1492 begin
1493 select onhand into l_onhand_qty
1494 from mtl_bis_inv_by_period
1495 where organization_id = x_organization_id
1496 and inventory_item_id = x_inventory_item_id
1497 and acct_period_id = x_last_period_id;
1498 exception WHEN NO_DATA_FOUND then
1499 l_onhand_qty := 0.0;
1500 end;
1501 return l_onhand_qty;
1502 end GET_MBI_ONHAND;
1503 -- End changes 2856158
1504 END INV_TURNS;