1 PACKAGE BODY CSTACPCS AS
2 /* $Header: CSTACPCB.pls 115.21 2003/06/20 15:51:44 rthng ship $ */
3
4 PROCEDURE summarize_value(
5 i_org_id IN NUMBER,
6 i_acct_period_id IN NUMBER,
7 i_last_period_id IN NUMBER,
8 i_user_id IN NUMBER,
9 i_prog_id IN NUMBER,
10 i_prog_appl_id IN NUMBER,
11 err_num OUT NOCOPY NUMBER,
12 err_code OUT NOCOPY VARCHAR2,
13 err_msg OUT NOCOPY VARCHAR2)
14
15 is
16 l_last_close_date DATE;
17 l_acct_close_date DATE;
18 l_stmt_num NUMBER;
19 l_intransit_exists NUMBER;
20 l_sum_period_qty NUMBER;
21 l_item_id NUMBER;
22 l_subinv_code VARCHAR2(10);
23 l_cost_group_id NUMBER;
24 l_wms_flg NUMBER;
25 l_msg_count NUMBER;
26 l_return_status VARCHAR2(11);
27 l_msg_data VARCHAR(2000);
28 l_default_cost_group_id NUMBER;
29 l_trans_id NUMBER;
30 l_last_period_id NUMBER;
31
32 process_error EXCEPTION;
33
34 CURSOR cur_mmt_qty(c_last_period_id NUMBER,c_acct_period_id NUMBER,c_org_id NUMBER) IS
35 SELECT
36 sum(mmt.primary_quantity),
37 MMT.COST_GROUP_ID,
38 MMT.INVENTORY_ITEM_ID,
39 MMT.SUBINVENTORY_CODE
40 FROM
41 MTL_PER_CLOSE_DTLS MPSD ,
42 MTL_MATERIAL_TRANSACTIONS MMT,
43 ORG_ACCT_PERIODS OAP
44 WHERE
45 MPSD.ACCT_PERIOD_ID = C_ACCT_PERIOD_ID AND
46 MPSD.ORGANIZATION_ID = C_ORG_ID AND
47 MMT.COST_GROUP_ID = MPSD.COST_GROUP_ID AND
48 MMT.ORGANIZATION_ID = MPSD.ORGANIZATION_ID AND
49 MMT.ORGANIZATION_ID = nvl(MMT.OWNING_ORGANIZATION_ID, MMT.ORGANIZATION_ID) AND
50 NVL(MMT.OWNING_TP_TYPE,2) = 2 AND
51 MMT.INVENTORY_ITEM_ID = MPSD.INVENTORY_ITEM_ID AND
52 MMT.SUBINVENTORY_CODE = MPSD.SECONDARY_INVENTORY AND
53 MMT.ORGANIZATION_ID = C_ORG_ID AND
54 MMT.SUBINVENTORY_CODE IS NOT NULL AND
55 MMT.COSTED_FLAG IS NULL AND
56 MMT.TRANSACTION_DATE >= OAP.PERIOD_START_DATE AND
57 MMT.TRANSACTION_DATE <= (trunc(OAP.SCHEDULE_CLOSE_DATE) + 0.99999)
58 AND
59 MMT.ACCT_PERIOD_ID > C_LAST_PERIOD_ID AND
60 MMT.ACCT_PERIOD_ID <= C_ACCT_PERIOD_ID AND
61 MMT.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID AND
62 OAP.ORGANIZATION_ID = C_ORG_ID
63 GROUP BY
64 MMT.COST_GROUP_ID,MMT.INVENTORY_ITEM_ID,MMT.SUBINVENTORY_CODE;
65
66 CURSOR cur_per_close_dtls(
67 c_org_id NUMBER,
68 c_acct_period_id NUMBER)
69 IS
70 SELECT DISTINCT
71 COST_GROUP_ID,
72 INVENTORY_ITEM_ID
73 FROM MTL_PER_CLOSE_DTLS
74 WHERE ORGANIZATION_ID = c_org_id
75 AND ACCT_PERIOD_ID = c_acct_period_id;
76
77 CURSOR cur_get_mcacd_id(
78 c_item_id IN NUMBER,
79 c_org_id IN NUMBER,
80 c_cg_id IN NUMBER,
81 c_last_period_id IN NUMBER,
82 c_acct_period_id IN NUMBER,
83 c_last_close_date IN DATE,
84 c_acct_close_date IN DATE)
85 IS
86 SELECT MCACD.TRANSACTION_ID
87 FROM MTL_CST_ACTUAL_COST_DETAILS MCACD,
88 CST_QUANTITY_LAYERS CQL,
89 MTL_MATERIAL_TRANSACTIONS MMT,
90 MTL_SECONDARY_INVENTORIES MSI
91 WHERE MCACD.INVENTORY_ITEM_ID = c_item_id
92 AND MCACD.ORGANIZATION_ID = c_org_id
93 AND MCACD.LAYER_ID = CQL.LAYER_ID
94 AND CQL.COST_GROUP_ID = c_cg_id
95 AND MCACD.TRANSACTION_ID = MMT.TRANSACTION_ID
96 AND MMT.TRANSACTION_ACTION_ID <> 30
97 AND (
98 ( MMT.ORGANIZATION_ID = c_org_id
99 AND MMT.ACCT_PERIOD_ID > c_last_period_id
100 AND MMT.ACCT_PERIOD_ID <= c_acct_period_id)
101 OR ( MMT.ORGANIZATION_ID <> c_org_id
102 AND MMT.TRANSACTION_DATE > NVL(c_last_close_date, MMT.TRANSACTION_DATE-1)
103 AND MMT.TRANSACTION_DATE <= trunc(c_acct_close_date)+0.99999))
104 AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME(+)
105 AND NVL(MSI.ORGANIZATION_ID,c_org_id) = c_org_id
106 AND NVL(MSI.ASSET_INVENTORY,1) = 1
107 ORDER
108 BY MCACD.TRANSACTION_COSTED_DATE DESC,
109 MCACD.TRANSACTION_ID DESC;
110 BEGIN
111
112 err_num:=0;
113
114 l_stmt_num:=5;
115
116 IF (wms_install.check_install(l_return_status, l_msg_count, l_msg_data, I_org_id))
117 THEN
118 l_wms_flg := 1;
119 ELSE
120 l_wms_flg := 0;
121 END IF;
122
123 IF (l_wms_flg = 1)
124 THEN
125 DELETE FROM MTL_PERIOD_CG_SUMMARY
126 WHERE
127 ORGANIZATION_ID = I_ORG_ID AND
128 ACCT_PERIOD_ID = I_ACCT_PERIOD_ID;
129 ELSE
130
131 DELETE FROM MTL_PERIOD_SUMMARY
132 WHERE
133 ORGANIZATION_ID = I_ORG_ID AND
134 ACCT_PERIOD_ID = I_ACCT_PERIOD_ID;
135
136 END IF;
137
138 l_stmt_num:=7;
139
140 DELETE FROM MTL_PER_CLOSE_DTLS
141 WHERE
142 ORGANIZATION_ID = I_ORG_ID AND
143 ACCT_PERIOD_ID = I_ACCT_PERIOD_ID;
144
145 /* added for bug 2769970 */
146 l_stmt_num :=8;
147
148 select default_cost_group_id
149 into l_default_cost_group_id
150 from mtl_parameters
151 where organization_id = i_org_id;
152
153
154 IF i_last_period_id IS NULL THEN
155 l_last_period_id := -1;
156 ELSE
157 l_last_period_id := i_last_period_id;
158 END IF;
159
160 l_stmt_num:=10;
161
162 /*Added For Bug 1478959 in 11.0 .... Porting the changes to 11.5 Bug 1521581*/
163 /*------------------------------------------+
164 | Pick up the schedule_close_date of the current
165 | period that was closed ...
166 |--------------------------------------*/
167
168 SELECT
169 SCHEDULE_CLOSE_DATE
170 INTO l_acct_close_date
171 FROM
172 ORG_ACCT_PERIODS
173 WHERE
174 ORGANIZATION_ID = I_ORG_ID AND
175 ACCT_PERIOD_ID = I_ACCT_PERIOD_ID;
176 /*End of Addition for 1478959 in 11.0 .... Porting the changes to 11.5 Bug 1521581*/
177
178 /*-----------------------------------------------------------+
179 | First, copy over from the prior period all the unique
180 | combinations of cost group, Item and subinventory.
181 |--------------------------------------------------------+*/
182
183 INSERT INTO MTL_PER_CLOSE_DTLS
184 (COST_GROUP_ID,
185 ACCT_PERIOD_ID,
186 SECONDARY_INVENTORY,
187 INVENTORY_ITEM_ID,
188 ORGANIZATION_ID,
189 PERIOD_END_QUANTITY,
190 period_end_unit_cost,
191 LAST_UPDATE_DATE,
192 LAST_UPDATED_BY,
193 CREATED_BY,
194 CREATION_DATE,
195 PROGRAM_ID,
196 PROGRAM_APPLICATION_ID,
197 LOGIN_ID)
198 select
199 COST_GROUP_ID,
200 i_acct_period_id,
201 SECONDARY_INVENTORY,
202 INVENTORY_ITEM_ID,
203 ORGANIZATION_ID,
204 NVL(PERIOD_END_QUANTITY,0),
205 NVL(period_end_unit_cost,0),
206 SYSDATE,
207 i_user_id,
208 i_user_id,
209 SYSDATE,
210 i_prog_id,
211 i_prog_appl_id,
212 i_user_id
213 FROM MTL_PER_CLOSE_DTLS OLD
214 WHERE
215 OLD.ACCT_PERIOD_ID = l_last_period_id AND
216 OLD.SECONDARY_INVENTORY IS NOT NULL AND
217 OLD.ORGANIZATION_ID = i_org_id
218 /*Added for 1478959 in 11.0 .... Porting the changes to 11.5 Bug 1521581*/
219 UNION ALL
220 select
221 l_default_cost_group_id, /*1, commented for bug 2769970*/
222 i_acct_period_id,
223 NULL,
224 SUP.ITEM_ID,
225 i_org_id,
226 SUM(DECODE(TO_ORGANIZATION_ID,I_ORG_ID,TO_ORG_PRIMARY_QUANTITY,
227 QUANTITY)),
228 0,
229 SYSDATE,
230 i_user_id,
231 i_user_id,
232 SYSDATE,
233 i_prog_id,
234 i_prog_appl_id,
235 i_user_id
236 FROM MTL_SUPPLY SUP,
237 RCV_SHIPMENT_HEADERS RSH
238 WHERE
239 SUP.ITEM_ID IN
240 (select inventory_item_id from mtl_per_close_dtls OLD
241 where
242 OLD.ACCT_PERIOD_ID = l_last_period_id AND
243 old.cost_group_id = l_default_cost_group_id AND --2769970
244 OLD.SECONDARY_INVENTORY IS NULL AND
245 OLD.ORGANIZATION_ID = i_org_id) AND
246 INTRANSIT_OWNING_ORG_ID = i_org_id AND
247 SUP.SUPPLY_TYPE_CODE IN ('SHIPMENT','RECEIVING') AND
248 (SUP.TO_ORGANIZATION_ID = i_org_id OR
249 SUP.FROM_ORGANIZATION_ID = I_ORG_ID) AND
250 SUP.SHIPMENT_HEADER_ID + 0 = RSH.SHIPMENT_HEADER_ID AND
251 EXISTS
252 (SELECT 'X'
253 FROM MTL_MATERIAL_TRANSACTIONS MMT
254 WHERE
255 MMT.INVENTORY_ITEM_ID = SUP.ITEM_ID AND
256 ((SUP.FROM_ORGANIZATION_ID = MMT.ORGANIZATION_ID AND
257 SUP.TO_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
258 AND
259 SUP.INTRANSIT_OWNING_ORG_ID = MMT.TRANSFER_ORGANIZATION_ID)
260 OR
261 (SUP.FROM_ORGANIZATION_ID = MMT.ORGANIZATION_ID AND
262 SUP.INTRANSIT_OWNING_ORG_ID = MMT.ORGANIZATION_ID AND
263 SUP.TO_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID))
264 AND
265 MMT.SHIPMENT_NUMBER = RSH.SHIPMENT_NUM AND
266 MMT.COSTED_FLAG IS NULL AND
267 MMT.TRANSACTION_DATE <= (trunc(nvl(L_ACCT_CLOSE_DATE,
268 mmt.transaction_date-1)) + 0.99999))
269 AND
270 SUP.INTRANSIT_OWNING_ORG_ID = I_ORG_ID
271 GROUP BY SUP.ITEM_ID;
272
273 /*Update the period end cost of previous period transactions..*/
274 UPDATE MTL_PER_CLOSE_DTLS MPSD
275 SET MPSD.PERIOD_END_UNIT_COST =
276 (SELECT OLD.PERIOD_END_UNIT_COST FROM
277 MTL_PER_CLOSE_DTLS OLD
278 WHERE OLD.ACCT_PERIOD_ID = l_last_period_id AND
279 OLD.INVENTORY_ITEM_ID = MPSD.INVENTORY_ITEM_ID AND
280 OLD.COST_GROUP_ID = l_default_cost_group_id AND --2769970
281 OLD.SECONDARY_INVENTORY IS NULL AND
282 OLD.ORGANIZATION_ID = I_ORG_ID)
283 WHERE MPSD.SECONDARY_INVENTORY IS NULL AND
284 MPSD.COST_GROUP_ID = l_default_cost_group_id AND --2769970
285 MPSD.ACCT_PERIOD_ID = I_ACCT_PERIOD_ID AND
286 MPSD.ORGANIZATION_ID = I_ORG_ID;
287
288 l_stmt_num:=20; /* last tuned in bug 2881225 */
289
290
291 /*----------------------------------------------------
292 | Insert into the details table, all new and unique
293 | combinations of Cost Grp/Item/Sub from the transactions
294 | table.
295 | We need to insert only data relevant to asset sub-
296 | inventories. So we need to join to mtl_secondary_
297 | inventories to check this.
298 |
299 | Open Issue:
300 | Go through the entire transaction set and see if we
301 | need to exclude any transactions here. By specifying
302 | subinventory_code is not null, we have ensured that
303 | scrap and avg cost update do not get considered.
304 |------------------------------------------------------+*/
305
306
307 INSERT INTO MTL_PER_CLOSE_DTLS
308 (COST_GROUP_ID,
309 ACCT_PERIOD_ID,
310 SECONDARY_INVENTORY,
311 INVENTORY_ITEM_ID,
312 ORGANIZATION_ID,
313 PERIOD_END_QUANTITY,
314 period_end_unit_cost,
315 LAST_UPDATE_DATE,
316 LAST_UPDATED_BY,
317 CREATED_BY,
318 CREATION_DATE,
319 PROGRAM_ID,
320 PROGRAM_APPLICATION_ID,
321 LOGIN_ID)
322 SELECT
323 NVL(COST_GROUP_ID,-9999),
324 I_ACCT_PERIOD_ID,
325 SUBINVENTORY_CODE,
326 INVENTORY_ITEM_ID,
327 I_ORG_ID,
328 0,
329 0,
330 SYSDATE,
331 i_user_id,
332 i_user_id,
333 SYSDATE,
334 i_prog_id,
335 i_prog_appl_id,
336 i_user_id
337 FROM
338 MTL_MATERIAL_TRANSACTIONS MMT,
339 MTL_SECONDARY_INVENTORIES SUB
340 WHERE
341 MMT.ORGANIZATION_ID = I_ORG_ID AND
342 MMT.ORGANIZATION_ID = nvl(MMT.OWNING_ORGANIZATION_ID, MMT.ORGANIZATION_ID) AND
343 NVL(MMT.OWNING_TP_TYPE,2) = 2 AND
344 MMT.ACCT_PERIOD_ID > l_LAST_PERIOD_ID AND
345 MMT.ACCT_PERIOD_ID <= I_ACCT_PERIOD_ID AND
346 MMT.SUBINVENTORY_CODE IS NOT NULL AND
347 MMT.COSTED_FLAG IS NULL AND
348 MMT.SUBINVENTORY_CODE = SUB.SECONDARY_INVENTORY_NAME
349 AND
350 MMT.ORGANIZATION_ID = SUB.ORGANIZATION_ID AND
351 SUB.ASSET_INVENTORY = 1
352 MINUS
353 SELECT
354 NVL(COST_GROUP_ID,-9999),
355 i_acct_period_id,
356 SECONDARY_INVENTORY,
357 INVENTORY_ITEM_ID,
358 i_org_id,
359 0,
360 0,
361 SYSDATE,
362 i_user_id,
363 i_user_id,
364 SYSDATE,
365 i_prog_id,
366 i_prog_appl_id,
367 i_user_id
368 FROM
369 MTL_PER_CLOSE_DTLS MPCD
370 WHERE
371 MPCD.organization_id = i_org_id AND
372 MPCD.acct_period_id = i_acct_period_id AND
373 MPCD.secondary_inventory IS NOT NULL;
374
375 l_stmt_num:=30;
376
377 /*-------------------------------------------------------++
378 | Update quantities from the transaction table, for all
379 | rows in MTL_PER_CLOSE_DTLS table, based on all
380 | the transactions that have occured during the period.
381 |-------------------------------------------------------+*/
382
383
384 l_sum_period_qty := 0;
385 l_cost_group_id := 0;
386 l_item_id := 0;
387 l_subinv_code := NULL;
388
389 open cur_mmt_qty(l_LAST_PERIOD_ID,I_ACCT_PERIOD_ID,I_ORG_ID);
390 LOOP
391 FETCH cur_mmt_qty INTO l_sum_period_qty,l_cost_group_id,l_item_id,l_subinv_code;
392
393 EXIT WHEN cur_mmt_qty%NOTFOUND;
394
395 UPDATE MTL_PER_CLOSE_DTLS MPSD
396 SET PERIOD_END_QUANTITY = NVL(l_sum_period_qty,0)+NVL(PERIOD_END_QUANTITY,0)
397 WHERE
398 MPSD.ACCT_PERIOD_ID = I_ACCT_PERIOD_ID
399 AND MPSD.ORGANIZATION_ID = I_ORG_ID
400 AND MPSD.COST_GROUP_ID = l_cost_group_id
401 AND MPSD.INVENTORY_ITEM_ID = l_item_id
402 AND MPSD.SECONDARY_INVENTORY = l_subinv_code;
403
404 END LOOP;
405 close cur_mmt_qty;
406
407
408
409 l_stmt_num:=32;
410
411 /*------------------------------------------+
412 | Pick up the schedule_close_date of the last
413 | period that was closed ...
414 |--------------------------------------*/
415
416 IF (i_last_period_id is NOT NULL AND i_last_period_id>0) THEN
417
418 SELECT
419 SCHEDULE_CLOSE_DATE
420 INTO l_last_close_date
421 FROM
422 ORG_ACCT_PERIODS
423 WHERE
424 ORGANIZATION_ID = I_ORG_ID AND
425 ACCT_PERIOD_ID = I_LAST_PERIOD_ID;
426
427 END IF;
428
429 l_stmt_num := 33;
430
431 l_stmt_num := 37;
432
433 /*-----------------------------------------------------
434 | Populate Org related Intransit information :
435 | This includes
436 | (i) Items 'in transit' that have been
437 | shipped to this Org from other orgs, obviously,
438 | the fob point being shipment in this case
439 | and
440 | (ii)Items 'in transit' that hav been shipped from
441 | this org to another org and the fob is
442 | receipt in this case.
443 | Note : We dont have to verify the fob because
444 | MTL_SUPPLY stores the intransit_owning_org_id.
445 | Note : We take a fresh snapshot of Intransit info
446 | for each run, therefore, sum from the beginning
447 | of time until now.
448 |-----------------------------------------------------*/
449
450 INSERT INTO MTL_PER_CLOSE_DTLS
451 (COST_GROUP_ID,
452 ACCT_PERIOD_ID,
453 SECONDARY_INVENTORY,
454 INVENTORY_ITEM_ID,
455 ORGANIZATION_ID,
456 PERIOD_END_QUANTITY,
457 period_end_unit_cost,
458 LAST_UPDATE_DATE,
459 LAST_UPDATED_BY,
460 CREATED_BY,
461 CREATION_DATE,
462 PROGRAM_ID,
463 PROGRAM_APPLICATION_ID,
464 LOGIN_ID)
465 SELECT
466 l_default_cost_group_id, /*1, commented for bug 2769970*/
467 I_ACCT_PERIOD_ID,
468 NULL,
469 SUP.ITEM_ID,
470 I_ORG_ID,
471 SUM(DECODE(TO_ORGANIZATION_ID,I_ORG_ID,TO_ORG_PRIMARY_QUANTITY,
472 QUANTITY)),
473 0,
474 SYSDATE,
475 i_user_id,
476 i_user_id,
477 SYSDATE,
478 i_prog_id,
479 i_prog_appl_id,
480 i_user_id
481 FROM MTL_SUPPLY SUP,
482 RCV_SHIPMENT_HEADERS RSH
483 WHERE
484 INTRANSIT_OWNING_ORG_ID = I_ORG_ID AND
485 SUP.SUPPLY_TYPE_CODE IN ('SHIPMENT','RECEIVING') AND
486 (SUP.TO_ORGANIZATION_ID = I_ORG_ID OR
487 SUP.FROM_ORGANIZATION_ID = I_ORG_ID) AND
488 RSH.SHIPMENT_HEADER_ID = SUP.SHIPMENT_HEADER_ID AND
489 EXISTS
490 (SELECT 'X'
491 FROM
492 MTL_MATERIAL_TRANSACTIONS MMT
493 WHERE
494 SUP.ITEM_ID = MMT.INVENTORY_ITEM_ID AND
495 ( --Added for Bug #1478959 in 11.0 .... Porting the changes to 11.5 Bug 1521581*/
496 (SUP.FROM_ORGANIZATION_ID = MMT.ORGANIZATION_ID AND
497 SUP.TO_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
498 AND
499 SUP.INTRANSIT_OWNING_ORG_ID = MMT.TRANSFER_ORGANIZATION_ID)
500 OR
501 (SUP.FROM_ORGANIZATION_ID = MMT.ORGANIZATION_ID AND
502 SUP.INTRANSIT_OWNING_ORG_ID = MMT.ORGANIZATION_ID AND
503 SUP.TO_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID)
504 ) --Added for Bug #1478959 in 11.0 .... Porting the changes to 11.5 Bug 1521581*/
505 AND
506 RSH.SHIPMENT_NUM = MMT.SHIPMENT_NUMBER AND
507 MMT.COSTED_FLAG IS NULL AND
508 MMT.TRANSACTION_DATE <= (trunc(L_ACCT_CLOSE_DATE) + 0.99999))
509 AND
510 SUP.INTRANSIT_OWNING_ORG_ID = I_ORG_ID AND
511 NOT EXISTS
512 (SELECT 'X'
513 FROM
514 MTL_PER_CLOSE_DTLS MPSD
515 WHERE
516 MPSD.INVENTORY_ITEM_ID = SUP.ITEM_ID AND
517 MPSD.ORGANIZATION_ID = I_ORG_ID AND
518 MPSD.COST_GROUP_ID = l_default_cost_group_id AND --2769970
519 MPSD.ACCT_PERIOD_ID = I_ACCT_PERIOD_ID AND
520 MPSD.SECONDARY_INVENTORY IS NULL)
521 GROUP BY SUP.ITEM_ID;
522
523
524
525
526 l_stmt_num:=40; /* last tuned in bug 2881225 */
527
528 /*---------------------------------------------------------
529 | Update the period_end_unit_cost for rows in MPSD. The logic
530 | for updating this is below:
531 | The value in any subinventory, of a given item, at period
532 | end is the quantity valued at the 'Avg cost of the item
533 | after the last transaction in that period'. The last
534 | transaction for an item in a period is identfied by the
535 | transaction that has the latest transaction_costed_date
536 | for that item in the period. The new_cost corresponding to
537 | this transaction represents the cost we need.
538 |
539 | Open issue: make sure that cost processor updates this
540 | column in MMT for every transaction and also make sure
541 | that no transaction needs to be excluded from this list.
542 |
543 | Inter-Org shipments
544 | -------------------
545 |
546 | When an inter-org intransit transaction is performed
547 | to an average costing organization, with fob = SHIP,
548 | the cost in the destination org gets averaged at the
549 | the time of shipment. Such a txn needs to be considered
550 | when looking for the max(txn_costed_date).
551 | In a project scenario, where multiple cost groups
552 | could exist, intransit is always belonging to the
553 | common cost group ==> when checking if a shipment
554 | transaction is the last txn, we check only if Cg = 1.
555 | Also, we should check for such a txn only if that
556 | txn has been costed on a date that falls within the
557 | current period in the org for which the period is
558 | being closed.
559 |
560 |-----------------------------------------------------*/
561
562 /*--------------------------------------------------------
563 | Txfr txn cost processing architecture is now going to
564 | involve creating 2 rows in MACD, onr for each Cost grp
565 | involved in the txn. The following considerations are
566 | pertinent:
567 | * For Direct Org txfrs, both sides of the txn will have
568 | corresponding rows in MACD.
569 | * For intransit shipments(fob=RCV), Intransit Receipts
570 | (fob=SHP), Sub txfrs --> The same txn will have 2
571 | sets of rows in MACD, one for each CG involved.
572 | Both these rows are for the same org however.
573 | In the case of the Intransit txns, there is only 1
574 | physical txn, however in the case of the sub txfr,
575 | there are 2 physical txn_id's in MMT. The txn with
576 | -ve qtty gets costed.
577 | * For Intransit shipments (fob=ship) and Intransit
578 | receipts (fob=rcv) --> There will be 1 physical
579 | txn in MMT, yet 2 rows in MACD. The 2 rows will
580 | be for 2 different orgs. Note that the cost workers
581 | of both orgs process this txn. ==> When the first
582 | worker finishes with the txn, it sets
583 | the shipment_costed flag = 'y' in MMT.
584 | ##For the shipment case, after the shipping org
585 | worker finishes the txn, the ship_cstd = 'y', and
586 | then the rcv org worker processes the txn,after
587 | which the costed_flag is set to NULL. However,
588 | since the txn in MMT is with org_id = SHip org,
589 | the period in the shipment org cannot be closed
590 | till the costed_flag = NULL. Also, the rcv org
591 | costs/books get affected only when the costed_flag
592 | is set to NULL ==> we dont have to bother with the'
593 | shipment_costed in this case.
594 | ##For the receipt case the physical txn is against
595 | the recv org. The shipping org worker first processes
596 | the txn and then sets shipment_costed='y'; at this
597 | point, it is possible to close the sending org
598 | period, and this txn would have affected the books
599 | for the SND org though the costed_flag = NULL and
600 | the rcv org books have not been affected.
601 | Txn Action_id
602 | --- ---------
603 | Direct Org 3
604 | Intransit ship 21
605 | Intransit Rcv 12
606 | Sub Txfr 2
607 |
608 | Fob_pt 1 ==> SHP
609 | Fob_pt 2 ==> RCV
610 |--------------------------------------------------------------+*/
611
612 OPEN cur_per_close_dtls(i_org_id,i_acct_period_id);
613 LOOP
614 FETCH cur_per_close_dtls INTO l_cost_group_id, l_item_id;
615 EXIT WHEN cur_per_close_dtls%NOTFOUND;
616
617 OPEN cur_get_mcacd_id(l_item_id,i_org_id,l_cost_group_id,l_last_period_id,i_acct_period_id,l_last_close_date,l_acct_close_date);
618 FETCH cur_get_mcacd_id INTO l_trans_id;
619 IF cur_get_mcacd_id%NOTFOUND THEN
620 l_trans_id := -1;
621 END IF;
622 CLOSE cur_get_mcacd_id;
623
624 IF l_trans_id >= 0 THEN
625
626 UPDATE mtl_per_close_dtls mpsd
627 SET
628 (period_end_unit_cost
629 ) =
630 (SELECT
631 nvl(sum(macd.new_cost),period_end_unit_cost)
632 FROM
633 mtl_cst_actual_cost_details macd,
634 cst_quantity_layers layer
635 WHERE
636 macd.inventory_item_id = mpsd.inventory_item_id AND
637 macd.organization_id = i_org_id AND
638 macd.layer_id = layer.layer_id AND
639 layer.cost_group_id = mpsd.cost_group_id AND
640 macd.transaction_id = l_trans_id
641 GROUP BY macd.transaction_id)
642 WHERE
643 mpsd.organization_id = i_org_id AND
644 mpsd.acct_period_id = i_acct_period_id AND
645 mpsd.cost_group_id = l_cost_group_id AND
646 mpsd.inventory_item_id = l_item_id;
647 END IF;
648
649 END LOOP;
650 CLOSE cur_per_close_dtls;
651
652 l_stmt_num:=50;
653
654 IF (l_wms_flg = 1)
655 THEN
656 INSERT INTO MTL_PERIOD_CG_SUMMARY
657 (ACCT_PERIOD_ID,
658 ORGANIZATION_ID,
659 INVENTORY_TYPE,
660 COST_GROUP_ID,
661 LAST_UPDATE_DATE,
662 LAST_UPDATED_BY,
663 CREATION_DATE,
664 CREATED_BY,
665 LAST_UPDATE_LOGIN,
666 INVENTORY_VALUE,
667 REQUEST_ID,
668 PROGRAM_APPLICATION_ID,
669 PROGRAM_ID,
670 PROGRAM_UPDATE_DATE)
671 SELECT
672 I_ACCT_PERIOD_ID,
673 I_ORG_ID,
674 1,
675 COST_GROUP_ID,
676 SYSDATE,
677 i_user_id,
678 SYSDATE,
679 i_user_id,
680 i_user_id,
681 SUM(NVL(PERIOD_END_QUANTITY,0)*NVL(period_end_unit_cost,0)),
682 NULL,
683 i_prog_id,
684 i_prog_appl_id,
685 SYSDATE
686 FROM
687 MTL_PER_CLOSE_DTLS
688 WHERE
689 ACCT_PERIOD_ID = I_ACCT_PERIOD_ID AND
690 ORGANIZATION_ID = I_ORG_ID
691 GROUP BY COST_GROUP_ID;
692
693 ELSE
694
695 INSERT INTO MTL_PERIOD_SUMMARY
696 (ACCT_PERIOD_ID,
697 ORGANIZATION_ID,
698 INVENTORY_TYPE,
699 SECONDARY_INVENTORY,
700 LAST_UPDATE_DATE,
701 LAST_UPDATED_BY,
702 CREATION_DATE,
703 CREATED_BY,
704 LAST_UPDATE_LOGIN,
705 INVENTORY_VALUE,
706 REQUEST_ID,
707 PROGRAM_APPLICATION_ID,
708 PROGRAM_ID,
709 PROGRAM_UPDATE_DATE)
710 SELECT
711 I_ACCT_PERIOD_ID,
712 I_ORG_ID,
713 1,
714 SECONDARY_INVENTORY,
715 SYSDATE,
716 i_user_id,
717 SYSDATE,
718 i_user_id,
719 i_user_id,
720 SUM(NVL(PERIOD_END_QUANTITY,0)*NVL(period_end_unit_cost,0)),
721 NULL,
722 i_prog_id,
723 i_prog_appl_id,
724 SYSDATE
725 FROM
726 MTL_PER_CLOSE_DTLS
727 WHERE
728 ACCT_PERIOD_ID = I_ACCT_PERIOD_ID AND
729 ORGANIZATION_ID = I_ORG_ID
730 GROUP BY SECONDARY_INVENTORY;
731
732 END IF;
733
734 l_stmt_num:=60;
735
736 commit;
737
738 EXCEPTION
739
740 WHEN OTHERS THEN
741 err_num := SQLCODE;
742 err_msg := 'CSTACPCS:' || to_char(l_stmt_num) || substr(SQLERRM,1,150);
743
744 rollback;
745
746 END summarize_value;
747
748 END CSTACPCS;
[Home] [Help]