[Home] [Help]
PACKAGE BODY: APPS.CST_DIAGNOSTICS_PKG
Source
1 PACKAGE BODY CST_DIAGNOSTICS_PKG AS
2 /* $Header: CSTDIAGB.pls 120.0.12000000.3 2007/09/25 00:07:47 anjha noship $ */
3
4 /*---------------------------------------------------------------------------
5 | FUNCTION : TEMP_PO_TAX
6 | DESCRIPTION : Calculates po tax
7 ----------------------------------------------------------------------------*/
8 FUNCTION TEMP_PO_TAX(i_txn_id in number)
9 RETURN NUMBER IS
10
11 l_tax number;
12 BEGIN
13 SELECT nvl((SUM( nvl(pod.nonrecoverable_tax,0))
14 /SUM(pod.quantity_ordered)),0)
15 INTO l_tax
16 FROM po_distributions_all pod,
17 rcv_transactions rt9
18 WHERE RT9.TRANSACTION_ID = i_txn_id
19 AND (
20 ( RT9.PO_DISTRIBUTION_ID IS NOT NULL
21 AND RT9.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID )
22 OR
23 ( RT9.PO_DISTRIBUTION_ID IS NULL
24 AND RT9.PO_LINE_LOCATION_ID = POD.LINE_LOCATION_ID)
25 );
26
27 RETURN l_tax;
28 END temp_po_tax;
29
30 /*---------------------------------------------------------------------------
31 | PROCEDURE : TEMP_PO_RATE
32 | DESCRIPTION : Calculates po_rate.
33 ----------------------------------------------------------------------------*/
34
35 FUNCTION TEMP_PO_RATE(i_txn_id in number)
36 RETURN NUMBER IS
37
38 l_rate number;
39
40 BEGIN
41 SELECT
42 SUM(pod.quantity_ordered*nvl(pod.rate,1))/SUM(pod.quantity_ordered)
43 INTO l_rate
44 FROM po_distributions_all pod,
45 rcv_transactions rct,
46 po_headers_all poh
47 WHERE rct.transaction_id = i_txn_id
48 AND pod.po_header_id = poh.po_header_id
49 AND (
50 (RCT.PO_DISTRIBUTION_ID IS NOT NULL
51 AND RCT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID)
52 OR
53 (RCT.PO_DISTRIBUTION_ID IS NULL
54 AND RCT.PO_LINE_LOCATION_ID = POD.LINE_LOCATION_ID)
55 );
56
57 RETURN l_rate;
58 END temp_po_rate;
59
60 /*---------------------------------------------------------------------------
61 | PROCEDURE : Check_Orphaned
62 | DESCRIPTION : Checks the orphaned transactions for a WIP flow schedule
63 | Completion transaction.
64 ----------------------------------------------------------------------------*/
65 PROCEDURE CHECK_ORPHANED
66 (TXN_ID IN NUMBER,
67 L_ORG_ID IN NUMBER) IS
68 L_C_TXN_ID NUMBER:=NULL;
69 L_COSTED_FLAG VARCHAR(2);
70
71 BEGIN
72 SELECT COMPLETION_TRANSACTION_ID
73 INTO L_C_TXN_ID
74 FROM MTL_MATERIAL_TRANSACTIONS
75 WHERE TRANSACTION_ID =TXN_ID
76 AND ORGANIZATION_ID =L_ORG_ID;
77
78 IF L_C_TXN_ID IS NULL
79 THEN
80 INSERT INTO CST_DIAG_TXN_ERRORS (TRANSACTION_ID,
81 ERROR_MESSAGE,RESOLUTION)
82 VALUES(TXN_ID,'COMPLETION TRANSACTION_ID NOT STAMPED.',NULL);
83 ELSE
84 SELECT COSTED_FLAG
85 INTO L_COSTED_FLAG
86 FROM MTL_MATERIAL_TRANSACTIONS
87 WHERE COMPLETION_TRANSACTION_ID = L_C_TXN_ID
88 AND TRANSACTION_SOURCE_TYPE_ID = 5
89 AND TRANSACTION_ACTION_ID NOT IN (1, 27, 33, 34);
90
91 IF ( L_COSTED_FLAG IS NULL) THEN
92 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
93 ERROR_MESSAGE,RESOLUTION)
94 VALUES(TXN_ID,'COMPLETION COSTED-ORPHANED TRANSACTION',NULL);
95 END IF;
96 END IF;
97 EXCEPTION
98 WHEN NO_DATA_FOUND THEN
99 INSERT INTO CST_DIAG_TXN_ERRORS (TRANSACTION_ID,
100 ERROR_MESSAGE,RESOLUTION)
101 VALUES(TXN_ID,'INCORRECT COMPLETION_TRANSACTION_ID',NULL);
102 END;
103
104
105 /*---------------------------------------------------------------------------
106 | PROCEDURE : Get_Stuck_Txn_Info
107 | DESCRIPTION : Checks for the bottle neck transactions for Actual costing
108 | Organizations.
109 ----------------------------------------------------------------------------*/
110 PROCEDURE Get_Stuck_Txn_Info AS
111 CURSOR ALL_ACTUAL_COSTING_ORGS IS
112 SELECT MP.ORGANIZATION_ID
113 FROM MTL_PARAMETERS MP
114 WHERE MP.PRIMARY_COST_METHOD IN (2,5,6);
115 L_MIN_DATE DATE;
116 L_MIN_TXN NUMBER;
117 L_TXN_DATE DATE;
118 L_TXN_ORG NUMBER;
119 L_TXN_TXFR_ORG NUMBER;
120 L_TXN_ACTION_ID NUMBER;
121 L_TXN_SOURCE_TYPE_ID NUMBER;
122 L_TXN_COST NUMBER;
123 L_TXN_SHIPMENT_COSTED VARCHAR2(1);
124 L_TXN_MOVE_TXN_ID NUMBER;
125 L_TXN_COMP_TXN_ID NUMBER;
126 L_TXN_COSTED_FLAG VARCHAR2(1);
127 L_TXN_TXFR_TXN_ID NUMBER;
128 L_LOGICAL_TXN_CRTD NUMBER;
129 BEGIN
130 delete from CST_DIAG_ERRORED_TXNS;
131
132 FOR ORG_REC IN ALL_ACTUAL_COSTING_ORGS LOOP
133 SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
134 TRUNC(NVL(MIN(TRANSACTION_DATE),(SYSDATE+1)))
135 INTO L_MIN_DATE
136 FROM mtl_material_transactions MMT
137 WHERE
138 nvl(parent_transaction_id, transaction_id) = transaction_id
139 AND costed_flag in ('N', 'E')
140 AND
141 (
142 (
143 organization_id = ORG_REC.ORGANIZATION_ID
144 AND
145 (
146 costed_flag = 'E'
147 OR
148 (
149 nvl(logical_transactions_created, 1) = 2
150 OR
151 EXISTS (
152 SELECT null
153 FROM mtl_material_transactions_temp MMTP
154 WHERE MMT.transaction_action_id IN (30, 31)
155 AND MMT.organization_id = MMTP.organization_id
156 AND (MMT.COMPLETION_TRANSACTION_ID = MMTP.COMPLETION_TRANSACTION_ID
157 -- Added for bug 4256685
158 OR MMT.MOVE_TRANSACTION_ID = MMTP.move_transaction_id)
159 UNION ALL
160 SELECT null
161 FROM wip_move_txn_interface WMTI
162 WHERE MMT.transaction_action_id IN (30, 31)
163 AND WMTI.TRANSACTION_ID = MMT.MOVE_TRANSACTION_ID
164 )
165 )
166 OR
167 (
168 costed_flag = 'N'
169 AND
170 (
171 EXISTS (
172 SELECT null
173 FROM mtl_parameters
174 WHERE organization_id = MMT.transfer_organization_id
175 AND primary_cost_method IN (2,5,6)
176 )
177 AND
178 (
179 (
180 transaction_action_id = 3
181 AND primary_quantity > 0
182 AND transaction_cost IS NULL
183 )
184 OR
185 (
186 exists (select null
187 from mtl_interorg_parameters MIP
188 where transaction_action_id = 12
189 AND MIP.to_organization_id = MMT.organization_id
190 AND MIP.from_organization_id = MMT.transfer_organization_id
191 AND NVL(MMT.fob_point,MIP.fob_point) = 2
192 AND shipment_costed IS NULL
193 )
194 )
195 )
196 )
197 )
198 )
199 )
200 OR
201 (
202 transfer_organization_id = ORG_REC.ORGANIZATION_ID
203 AND costed_flag = 'N'
204 AND
205 (
206 exists (select null
207 from mtl_interorg_parameters MIP
208 where transaction_action_id = 21
209 AND MIP.to_organization_id = MMT.transfer_organization_id
210 AND MIP.from_organization_id = MMT.organization_id
211 AND NVL(MMT.fob_point,MIP.fob_point) = 1
212 AND shipment_costed IS NULL
213 AND EXISTS (
214 SELECT null
215 FROM mtl_parameters
216 WHERE organization_id = MMT.organization_id
217 AND primary_cost_method IN (2,5,6)
218 )
219 )
220 OR
221 (
222 EXISTS (
223 SELECT null
224 FROM mtl_parameters
225 WHERE organization_id = MMT.organization_id
226 AND primary_cost_method = 1
227 AND cost_cutoff_date is not null
228 AND MMT.transaction_date >= cost_cutoff_date
229 )
230 AND
231 (
232 transaction_action_id = 3
233 AND primary_quantity < 0
234 )
235 )
236 )
237 )
238 OR
239 (
240 costed_flag = 'N'
241 AND
242 exists (select null
243 from mtl_interorg_parameters MIP
244 where
245 (
246 transaction_action_id = 21
247 AND MIP.to_organization_id = MMT.transfer_organization_id
248 AND MIP.from_organization_id = MMT.organization_id
249 AND NVL(MMT.fob_point,MIP.fob_point) = 1
250 )
251 OR
252 (
253 transaction_action_id = 12
254 AND MIP.to_organization_id = MMT.organization_id
255 AND MIP.from_organization_id = MMT.transfer_organization_id
256 AND NVL(MMT.fob_point,MIP.fob_point) = 2
257 )
258 )
259 AND
260 EXISTS (
261 SELECT null
262 FROM mtl_parameters
263 WHERE
264 primary_cost_method = 1
265 AND cost_cutoff_date is not null
266 AND MMT.transaction_date >= cost_cutoff_date
267 AND
268 (
269 (
270 MMT.organization_id = ORG_REC.ORGANIZATION_ID
271 AND organization_id = MMT.transfer_organization_id
272 )
273 OR
274 (
275 MMT.transfer_organization_id = ORG_REC.ORGANIZATION_ID
276 ANd organization_id = MMT.organization_id
277 )
278 )
279 )
280 )
281 );
282
283
284 SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
285 NVL(MIN(TRANSACTION_ID),-1)
286 INTO L_MIN_TXN
287 FROM mtl_material_transactions MMT
288 WHERE
289 transaction_date < (L_MIN_DATE+1)
290 AND transaction_date >= L_MIN_DATE
291 AND nvl(parent_transaction_id, transaction_id) = transaction_id
292 AND costed_flag in ('N', 'E')
293 AND
294 (
295 (
296 organization_id = ORG_REC.ORGANIZATION_ID
297 AND
298 (
299 costed_flag = 'E'
300 OR
301 (
302 nvl(logical_transactions_created, 1) = 2
303 OR
304 EXISTS (
305 SELECT null
306 FROM mtl_material_transactions_temp MMTP
307 WHERE MMT.transaction_action_id IN (30, 31)
308 AND MMT.organization_id = MMTP.organization_id
309 AND (MMT.COMPLETION_TRANSACTION_ID = MMTP.COMPLETION_TRANSACTION_ID
310 -- Added for bug 4256685
311 OR MMT.MOVE_TRANSACTION_ID = MMTP.move_transaction_id)
312 UNION ALL
313 SELECT null
314 FROM wip_move_txn_interface WMTI
315 WHERE MMT.transaction_action_id IN (30, 31)
316 AND WMTI.TRANSACTION_ID = MMT.MOVE_TRANSACTION_ID
317 )
318 )
319 OR
320 (
321 costed_flag = 'N'
322 AND
323 (
324 EXISTS (
325 SELECT null
326 FROM mtl_parameters
327 WHERE organization_id = MMT.transfer_organization_id
328 AND primary_cost_method IN (2,5,6)
329 )
330 AND
331 (
332 (
333 transaction_action_id = 3
334 AND primary_quantity > 0
335 AND transaction_cost IS NULL
336 )
337 OR
338 (
339 exists (
340 select null
341 from mtl_interorg_parameters MIP
342 where transaction_action_id = 12
343 AND MIP.to_organization_id = MMT.organization_id
344 AND MIP.from_organization_id = MMT.transfer_organization_id
345 AND NVL(MMT.fob_point,MIP.fob_point) = 2
346 AND shipment_costed IS NULL
347 )
348 )
349 )
350 )
351 )
352 )
353 )
354 OR
355 (
356 transfer_organization_id = ORG_REC.ORGANIZATION_ID
357 AND costed_flag = 'N'
358 AND
359 (
360 exists (
361 select null
362 from mtl_interorg_parameters MIP
363 where transaction_action_id = 21
364 AND MIP.to_organization_id = MMT.transfer_organization_id
365 AND MIP.from_organization_id = MMT.organization_id
366 AND NVL(MMT.fob_point,MIP.fob_point) = 1
367 AND shipment_costed IS NULL
368 AND EXISTS (
369 SELECT null
370 FROM mtl_parameters
371 WHERE organization_id = MMT.organization_id
372 AND primary_cost_method IN (2,5,6)
373 )
374 )
375 OR
376 (
377 EXISTS (
378 SELECT null
379 FROM mtl_parameters
380 WHERE organization_id = MMT.organization_id
381 AND primary_cost_method = 1
382 AND cost_cutoff_date is not null
383 AND MMT.transaction_date >= cost_cutoff_date
384 )
385 AND
386 (
387 transaction_action_id = 3
388 AND primary_quantity < 0
389 )
390 )
391 )
392 )
393 OR
394 (
395 costed_flag = 'N'
396 AND
397 exists (
398 select null
399 from mtl_interorg_parameters MIP
400 where
401 (
402 transaction_action_id = 21
403 AND MIP.to_organization_id = MMT.transfer_organization_id
404 AND MIP.from_organization_id = MMT.organization_id
405 AND NVL(MMT.fob_point,MIP.fob_point) = 1
406 )
407 OR
408 (
409 transaction_action_id = 12
410 AND MIP.to_organization_id = MMT.organization_id
411 AND MIP.from_organization_id = MMT.transfer_organization_id
412 AND NVL(MMT.fob_point,MIP.fob_point) = 2
413 )
414 )
415 AND
416 EXISTS (
417 SELECT null
418 FROM mtl_parameters
419 WHERE
420 primary_cost_method = 1
421 AND cost_cutoff_date is not null
422 AND MMT.transaction_date >= cost_cutoff_date
423 AND
424 (
425 (
426 MMT.organization_id = ORG_REC.ORGANIZATION_ID
427 AND organization_id = MMT.transfer_organization_id
428 )
429 OR
430 (
431 MMT.transfer_organization_id = ORG_REC.ORGANIZATION_ID
432 ANd organization_id = MMT.organization_id
433 )
434 )
435 )
436 )
437 );
438
439 IF ( L_MIN_TXN <>-1) THEN
440 SELECT TRANSACTION_DATE,
441 ORGANIZATION_ID,
442 TRANSFER_ORGANIZATION_ID,
443 TRANSFER_TRANSACTION_ID,
444 TRANSACTION_ACTION_ID,
445 TRANSACTION_SOURCE_TYPE_ID,
446 TRANSACTION_COST,
447 SHIPMENT_COSTED,
448 MOVE_TRANSACTION_ID,
449 COMPLETION_TRANSACTION_ID,
450 COSTED_FLAG,
451 nvl(logical_transactions_created, 1)
452 INTO
453 L_TXN_DATE,
454 L_TXN_ORG,
455 L_TXN_TXFR_ORG,
456 L_TXN_TXFR_TXN_ID,
457 L_TXN_ACTION_ID,
458 L_TXN_SOURCE_TYPE_ID,
459 L_TXN_COST,
460 L_TXN_SHIPMENT_COSTED,
461 L_TXN_MOVE_TXN_ID,
462 L_TXN_COMP_TXN_ID,
463 L_TXN_COSTED_FLAG,
464 L_LOGICAL_TXN_CRTD
465 FROM MTL_MATERIAL_TRANSACTIONS
466 WHERE TRANSACTION_ID = L_MIN_TXN;
467
468
472 BOTTLE_NECK_TXN_ID,
469
470 INSERT INTO CST_DIAG_ERRORED_TXNS
471 ( ORGANIZATION_ID,
473 TRANSACTION_DATE,
474 TRANSACTION_ORGANIZATION_ID,
475 TRANSFER_ORGANIZATION_ID,
476 TRANSFER_TRANSACTION_ID,
477 TRANSACTION_ACTION_ID,
478 TRANSACTION_SOURCE_TYPE_ID,
479 TRANSACTION_COST,
480 SHIPMENT_COSTED,
481 MOVE_TRANSACTION_ID,
482 COMPLETION_TRANSACTION_ID,
483 COSTED_FLAG,
484 WAITING_ORGANIZATION_ID,
485 LOGICAL_TXN_CREATED
486 )
487 VALUES ( ORG_REC.ORGANIZATION_ID,
488 L_MIN_TXN,
489 L_TXN_DATE,
490 L_TXN_ORG,
491 L_TXN_TXFR_ORG,
492 L_TXN_TXFR_TXN_ID,
493 L_TXN_ACTION_ID,
494 L_TXN_SOURCE_TYPE_ID,
495 L_TXN_COST,
496 L_TXN_SHIPMENT_COSTED,
497 L_TXN_MOVE_TXN_ID,
498 L_TXN_COMP_TXN_ID,
499 L_TXN_COSTED_FLAG,
500 DECODE(L_TXN_ACTION_ID,
501 21,L_TXN_ORG,
502 3,L_TXN_TXFR_ORG,
503 12,L_TXN_TXFR_ORG,
504 NULL),
505 L_LOGICAL_TXN_CRTD
506 );
507 ELSE
508
509 INSERT INTO CST_DIAG_ERRORED_TXNS
510 ( ORGANIZATION_ID,
511 BOTTLE_NECK_TXN_ID,
512 TRANSACTION_DATE,
513 TRANSACTION_ORGANIZATION_ID,
514 TRANSFER_ORGANIZATION_ID,
515 TRANSFER_TRANSACTION_ID,
516 TRANSACTION_ACTION_ID,
517 TRANSACTION_SOURCE_TYPE_ID,
518 TRANSACTION_COST,
519 SHIPMENT_COSTED,
520 MOVE_TRANSACTION_ID,
521 COMPLETION_TRANSACTION_ID,
522 COSTED_FLAG,
523 WAITING_ORGANIZATION_ID,
524 LOGICAL_TXN_CREATED
525 )
526 VALUES ( ORG_REC.ORGANIZATION_ID,
527 NULL,
528 NULL,
529 NULL,
530 NULL,
531 NULL,
532 NULL,
533 NULL,
534 NULL,
535 NULL,
536 NULL,
537 NULL,
538 NULL,
539 NULL,
540 NULL
541 );
542
543 END IF;
544
545
546 END LOOP;
547 END Get_Stuck_Txn_Info;
548
549 /*---------------------------------------------------------------------------
550 | FUNCTION : Cost_Cutoff_Date
551 | DESCRIPTION : Checks for the Cost Cut-Off date for the organizations
552 | for customers on and above release 11.5.7.
553 ---------------------------------------------------------------------------*/
554
555 FUNCTION COST_CUTOFF_DATE(P_ORG_ID IN NUMBER) RETURN DATE IS
556 P_COST_CUTOFF_DATE DATE;
557 L_RELEASE VARCHAR2(10);
558 BEGIN
559
560 SELECT RELEASE_NAME
561 INTO L_RELEASE
562 FROM FND_PRODUCT_GROUPS;
563
564 IF (L_RELEASE IN ('11.5.7','11.5.8','11.5.9','11.5.10'))
565 THEN
566 SELECT NVL(COST_CUTOFF_DATE,SYSDATE+1)
567 INTO P_COST_CUTOFF_DATE
568 FROM MTL_PARAMETERS
569 WHERE ORGANIZATION_ID = P_ORG_ID;
570 ELSE
571 P_COST_CUTOFF_DATE :=SYSDATE+1;
572 END IF;
573
574 RETURN(P_COST_CUTOFF_DATE);
575 END COST_CUTOFF_DATE;
576
577 /*---------------------------------------------------------------------------
578 | PROCEDURE : Check_Transactions_MMT
579 | DESCRIPTION : Spools the transactions of MMT and checks for
580 | the reason why costing is stuck for the transactions.
581 ---------------------------------------------------------------------------*/
582
583 PROCEDURE Check_Transactions_MMT
584 ( ORGANIZATION_ID NUMBER)
585 AS
586 CURSOR C_UNCOSTED_TRANSACTIONS(ORG_ID NUMBER) IS
587 SELECT TRANSACTION_ID,
588 INVENTORY_ITEM_ID,
589 TRANSACTION_DATE,
590 TRANSACTION_GROUP_ID,
591 TRANSACTION_ACTION_ID,
592 TRANSACTION_SOURCE_TYPE_ID,
593 COMPLETION_TRANSACTION_ID,
594 FLOW_SCHEDULE,
595 ORGANIZATION_ID,
596 TRANSFER_ORGANIZATION_ID,
597 TRANSFER_TRANSACTION_ID,
598 COSTED_FLAG
599 FROM MTL_MATERIAL_TRANSACTIONS
600 WHERE ORGANIZATION_ID =ORG_ID
601 AND COSTED_FLAG ='N';
602
603 L_MIN_REQUEST_ID NUMBER :=NULL;
604 COST_MANAGER_INACTIVE EXCEPTION;
605 L_ORGANIZATION_ID NUMBER;
606 L_PRIMARY_COST_METHOD NUMBER;
607 L_WSM_FLAG VARCHAR2(1);
608 L_COST_CUTOFF_DATE DATE;
609 L_RELEASE VARCHAR2(20);
610 L_TXN_ACTION VARCHAR2(200);
611 L_COSTED_FLAG VARCHAR2(1);
612 L_MIN_TXN_DTE DATE;
613 L_LOT_FLAG NUMBER;
614 NOTE1 VARCHAR2(300);
615 L_TRANSACTION_GROUP_ID NUMBER;
616 L_BOTTLE_NECK_TXN_ID NUMBER;
617 L_TXN_DATE DATE;
618 L_TXN_ORG NUMBER;
619 L_TXN_TXFR_ORG NUMBER;
620 L_TXN_TXFR_TXN_ID NUMBER;
621 L_TXN_ACTION_ID NUMBER;
622 L_TXN_SOURCE_TYPE_ID NUMBER;
623 L_TXN_COST NUMBER;
624 L_TXN_SHIPMENT_COSTED NUMBER;
625 L_TXN_MOVE_TXN_ID NUMBER;
626 L_TXN_COMP_TXN_ID NUMBER;
627 L_TXN_COSTED_FLAG VARCHAR(2);
628 L_WAITING_ORG NUMBER;
629 L_MMTT_COUNT NUMBER;
630 L_TRANSFER_COST NUMBER:=0;
631 L_SHIPMENT_COSTED NUMBER:=0;
632 L_TRF_ORG_CST_METHOD NUMBER:=NULL;
633 L_WMTI_COUNT NUMBER;
634 L_LOGICAL_TXN_CRTD NUMBER;
635
636 BEGIN
637 DELETE FROM CST_DIAG_TXN_ERRORS;
638
639 SELECT MIN(FCR.REQUEST_ID)
640 INTO L_MIN_REQUEST_ID
641 FROM FND_CONCURRENT_REQUESTS FCR
645 WHERE concurrent_program_name ='CMCTCM')
642 WHERE FCR.CONCURRENT_PROGRAM_ID IN
643 (SELECT CONCURRENT_PROGRAM_ID
644 FROM fnd_concurrent_programs
646 AND FCR.PROGRAM_APPLICATION_ID = 702
647 AND FCR.PHASE_CODE <> 'C';
648
649 ----DBMS_OUTPUT.PUT_LINE('CHECKING COST MANAGER ACTIVE');
650 IF ( NVL(L_MIN_REQUEST_ID,0) = 0 )
651 THEN
652 RAISE COST_MANAGER_INACTIVE;
653 END IF;
654
655 L_ORGANIZATION_ID:=ORGANIZATION_ID;
656
657 SELECT PRIMARY_COST_METHOD,
658 NVL(WSM_ENABLED_FLAG,'N')
659 INTO L_PRIMARY_COST_METHOD,
660 L_WSM_FLAG
661 FROM MTL_PARAMETERS
662 WHERE ORGANIZATION_ID = L_ORGANIZATION_ID;
663
664 --DBMS_OUTPUT.PUT_LINE('CHECKING COSTING METHOD: = '|| L_PRIMARY_COST_METHOD || 'WMS FLAG = '|| L_WSM_FLAG );
665
666 L_COST_CUTOFF_DATE :=COST_CUTOFF_DATE(L_ORGANIZATION_ID);
667
668 --DBMS_OUTPUT.PUT_LINE('COST CUT-OFF DATE : = '|| L_COST_CUTOFF_DATE);
669
670 --DBMS_OUTPUT.PUT_LINE('DELETE TEMP TABLE!!!');
671
672 --DELETE FROM CST_DIAG_TXN_ERRORS;
673
674
675 IF (L_PRIMARY_COST_METHOD =1)
676 THEN
677
678 --DBMS_OUTPUT.PUT_LINE('STANDARD COSTING NON WSM ENABLED ORGANIZATION');
679
680 FOR ITEM_REC IN C_UNCOSTED_TRANSACTIONS(L_ORGANIZATION_ID) LOOP
681
682 /* get transfer_org costing method only if TRANSFER_ORGANIZATION_ID is not null */
683 IF (ITEM_REC.TRANSFER_ORGANIZATION_ID is not null) THEN
684 SELECT PRIMARY_COST_METHOD
685 INTO L_TRF_ORG_CST_METHOD
686 FROM MTL_PARAMETERS
687 WHERE ORGANIZATION_ID = ITEM_REC.TRANSFER_ORGANIZATION_ID;
688 END IF;
689
690 --DBMS_OUTPUT.PUT_LINE('TXN DATE :='||ITEM_REC.TRANSACTION_DATE);
691
692 IF( ITEM_REC.TRANSACTION_GROUP_ID IS NOT NULL AND ITEM_REC.COSTED_FLAG ='N') THEN
693 --DBMS_OUTPUT.PUT_LINE('TRANSACTION_GROUP_ID IS NOT NULL FOR TRANSACTION WITH COSTED FLAG N');
694 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
695 ERROR_MESSAGE,RESOLUTION)
696 VALUES(ITEM_REC.TRANSACTION_ID,'TRANSACTION_GROUP_ID NOT NULL',NULL);
697
698 ELSIF ( ITEM_REC.TRANSACTION_DATE >L_COST_CUTOFF_DATE) THEN
699
700 --DBMS_OUTPUT.PUT_LINE('COST CUTT OFF DATE CHECK');
701 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
702 ERROR_MESSAGE,RESOLUTION)
703 VALUES(ITEM_REC.TRANSACTION_ID,'TXN DATE IS LATER THEN COST CUTOFF DATE FOR ORG',NULL);
704 ELSIF /* A */
705 ( ITEM_REC.TRANSACTION_ACTION_ID IN (1, 27, 33, 34)
706 AND ITEM_REC.TRANSACTION_SOURCE_TYPE_ID =5
707 AND ITEM_REC.FLOW_SCHEDULE ='Y' ) THEN
708 --DBMS_OUTPUT.PUT_LINE('ORPHAN CHECK');
709 CHECK_ORPHANED(ITEM_REC.TRANSACTION_ID,L_ORGANIZATION_ID);
710 ELSIF /* B */
711 ( ITEM_REC.TRANSACTION_ACTION_ID NOT IN (2,28,3,55,5)
712 AND ITEM_REC.TRANSFER_TRANSACTION_ID IS NOT NULL ) THEN
713 --DBMS_OUTPUT.PUT_LINE('TRANSFER TRANSACTION_ID CHECK');
714 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
715 ERROR_MESSAGE,RESOLUTION)
716 VALUES(ITEM_REC.TRANSACTION_ID,'INCORRECT TRANSFER_TRANSACTION_ID',NULL);
717 ELSIF /* C */
718 ( ITEM_REC.TRANSACTION_ACTION_ID IN (3,21,12) AND NVL(L_TRF_ORG_CST_METHOD,1) <>1 ) THEN
719 --DBMS_OUTPUT.PUT_LINE('RUN CHECKS FOR TRANSFER ORGANIZATION_ID '|| ITEM_REC.TRANSFER_ORGANIZATION_ID);
720 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
721 ERROR_MESSAGE,RESOLUTION)
722 VALUES(NULL,'RUN CHECKS FOR TRANSFER ORGANIZATION_ID '|| ITEM_REC.TRANSFER_ORGANIZATION_ID,NULL);
723
724 ELSIF /* F Only if WSM is enabled */
725 (L_WSM_FLAG ='Y' AND ITEM_REC.TRANSACTION_SOURCE_TYPE_ID =5) THEN
726
727 SELECT MIN(TXN_DT)
728 INTO L_MIN_TXN_DTE
729 FROM
730 ( SELECT MIN(TRANSACTION_DATE) TXN_DT
731 FROM WIP_COST_TXN_INTERFACE WCTI
732 WHERE WCTI.ORGANIZATION_ID = L_ORGANIZATION_ID
733 AND WCTI.ENTITY_TYPE = 5
734 AND WCTI.PROCESS_STATUS = 3
735 UNION
736 SELECT MIN(TRANSACTION_DATE)
737 FROM MTL_MATERIAL_TRANSACTIONS MMT
738 WHERE MMT.TRANSACTION_SOURCE_TYPE_ID = 5
739 AND MMT.ORGANIZATION_ID = L_ORGANIZATION_ID
740 AND MMT.COSTED_FLAG = 'E'
741 AND EXISTS (SELECT 1
742 FROM WIP_ENTITIES WE
743 WHERE WE.WIP_ENTITY_ID = MMT.TRANSACTION_SOURCE_ID
744 AND WE.ORGANIZATION_ID = MMT.ORGANIZATION_ID
745 AND WE.ENTITY_TYPE = 5));
746 SELECT 1
747 INTO L_LOT_FLAG
748 FROM MTL_MATERIAL_TRANSACTIONS MMT,
749 WIP_ENTITIES WE
750 WHERE MMT.TRANSACTION_ID =ITEM_REC.TRANSACTION_ID
751 AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
752 AND WE.WIP_ENTITY_ID = MMT.TRANSACTION_SOURCE_ID
753 AND WE.ORGANIZATION_ID = MMT.ORGANIZATION_ID
754 AND WE.ENTITY_TYPE = 5;
755
756 IF ( L_LOT_FLAG =1 AND ITEM_REC.TRANSACTION_DATE < L_MIN_TXN_DTE ) THEN
757 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
758 ERROR_MESSAGE,RESOLUTION)
759 VALUES(ITEM_REC.TRANSACTION_ID,'ERRORED RECORDS IN MMT/WCTI FOR LOT BASED JOB',NULL);
760 END IF;
761
762 ELSIF( C_UNCOSTED_TRANSACTIONS%ROWCOUNT >0) THEN
763 --DBMS_OUTPUT.PUT_LINE('REPORT COSTING BUG');
764 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
765 ERROR_MESSAGE,RESOLUTION)
766 VALUES(ITEM_REC.TRANSACTION_ID,'REPORT A BUG WITH COSTING DEVELOPMENT',NULL);
767 ELSE
768 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
769 ERROR_MESSAGE,RESOLUTION)
770 VALUES(NULL,'NO UNCOSTED TRANSACTIONS FOUND!!',NULL);
771 END IF;
772 END LOOP;
773
774 END IF;
775
776
777 IF (L_PRIMARY_COST_METHOD <>1) /* ACTUAL COSTING ORGANIZATIONS*/
778 THEN
782 SELECT BOTTLE_NECK_TXN_ID,
779 --DBMS_OUTPUT.PUT_LINE('ACTUAL COSTING ORGANIZATIONS');
780 Get_Stuck_Txn_Info();
781
783 TRANSACTION_DATE,
784 TRANSACTION_ORGANIZATION_ID,
785 TRANSFER_ORGANIZATION_ID,
786 TRANSFER_TRANSACTION_ID,
787 TRANSACTION_ACTION_ID,
788 TRANSACTION_SOURCE_TYPE_ID,
789 TRANSACTION_COST,
790 SHIPMENT_COSTED,
791 MOVE_TRANSACTION_ID,
792 COMPLETION_TRANSACTION_ID,
793 COSTED_FLAG,
794 WAITING_ORGANIZATION_ID,
795 LOGICAL_TXN_CREATED
796 INTO
797 L_BOTTLE_NECK_TXN_ID,
798 L_TXN_DATE ,
799 L_TXN_ORG ,
800 L_TXN_TXFR_ORG ,
801 L_TXN_TXFR_TXN_ID ,
802 L_TXN_ACTION_ID ,
803 L_TXN_SOURCE_TYPE_ID,
804 L_TXN_COST ,
805 L_TXN_SHIPMENT_COSTED,
806 L_TXN_MOVE_TXN_ID ,
807 L_TXN_COMP_TXN_ID ,
808 L_TXN_COSTED_FLAG ,
809 L_WAITING_ORG ,
810 L_LOGICAL_TXN_CRTD
811 FROM CST_DIAG_ERRORED_TXNS
812 WHERE ORGANIZATION_ID =L_ORGANIZATION_ID;
813
814 --DBMS_OUTPUT.PUT_LINE('TXN DATE :='||L_TXN_DATE);
815
816 IF (L_TXN_DATE >L_COST_CUTOFF_DATE)
817 THEN
818 --DBMS_OUTPUT.PUT_LINE('COST CUTT OFF DATE CHECK');
819 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
820 ERROR_MESSAGE,RESOLUTION)
821 VALUES(L_BOTTLE_NECK_TXN_ID,'TXN DATE IS LATER THEN COST CUTOFF DATE FOR ORG',NULL);
822 ELSIF (L_LOGICAL_TXN_CRTD = 2) THEN
823 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
824 ERROR_MESSAGE,RESOLUTION)
825 VALUES(L_BOTTLE_NECK_TXN_ID,'LOGICAL TRANSACTIONS FOR THIS TRANSACTION HAVE NOT BEEN CREATED',NULL);
826
827 ELSIF /* A - CHECK MMTT FOR BACK FLUSH TRANSACTIONS*/
828 ( L_TXN_SOURCE_TYPE_ID =5 ) THEN
829
830 SELECT COUNT(MMTT.COMPLETION_TRANSACTION_ID)
831 INTO L_MMTT_COUNT
832 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT,
833 CST_DIAG_ERRORED_TXNS SC
834 WHERE SC.ORGANIZATION_ID = L_ORGANIZATION_ID
835 AND SC.TRANSACTION_SOURCE_TYPE_ID = 5
836 AND MMTT.ORGANIZATION_ID = SC.ORGANIZATION_ID
837 AND (MMTT.COMPLETION_TRANSACTION_ID = SC.COMPLETION_TRANSACTION_ID
838 OR MMTT.MOVE_TRANSACTION_ID = SC.MOVE_TRANSACTION_ID);
839
840 IF (L_MMTT_COUNT > 0) THEN
841 --DBMS_OUTPUT.PUT_LINE('TRANSACTIONS STUCK IN MMTT');
842 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
843 ERROR_MESSAGE,RESOLUTION)
844 VALUES(L_BOTTLE_NECK_TXN_ID,L_MMTT_COUNT||' TRANSACTIONS ARE STUCK IN MMTT, CLEAR THEM BEFORE PROCEDDING',NULL);
845 END IF;
846
847 SELECT COUNT(1)
848 INTO L_WMTI_COUNT
849 FROM WIP_MOVE_TXN_INTERFACE WMTI
850 WHERE WMTI.TRANSACTION_ID = L_TXN_MOVE_TXN_ID ;
851 IF (L_WMTI_COUNT > 0) THEN
852
853 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
854 ERROR_MESSAGE,RESOLUTION)
855 VALUES(L_BOTTLE_NECK_TXN_ID,L_WMTI_COUNT||' TRANSACTIONS ARE STUCK IN WMTI, CLEAR THEM BEFORE PROCEDDING',NULL);
856 END IF;
857
858 ELSIF /* B - CHECK TRANSFER COST */
859 ( L_TXN_ACTION_ID =3 ) THEN
860 SELECT 1
861 INTO L_TRANSFER_COST
862 FROM CST_DIAG_ERRORED_TXNS SC
863 WHERE SC.BOTTLE_NECK_TXN_ID IS NOT NULL
864 AND SC.TRANSACTION_ACTION_ID = 3
865 AND SC.TRANSACTION_COST IS NULL
866 AND EXISTS ( SELECT 'X'
867 FROM MTL_MATERIAL_TRANSACTIONS MMT
868 WHERE MMT.TRANSACTION_ID = SC.TRANSFER_TRANSACTION_ID
869 AND MMT.COSTED_FLAG IS NULL
870 )
871 AND SC.ORGANIZATION_ID = L_ORGANIZATION_ID;
872
873 IF (L_TRANSFER_COST IS NOT NULL )
874 THEN
875 --DBMS_OUTPUT.PUT_LINE('TRANSFER COST IS NULL FOR DIRECT INTER_ORG TRANSFERS');
876 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
877 ERROR_MESSAGE,RESOLUTION)
878 VALUES(L_BOTTLE_NECK_TXN_ID,'TRANSFER COST IS NULL FOR DIRECT INTER_ORG TRANSFERS',NULL);
879 END IF;
880 ELSIF /* C - INTER ORGS */
881 ( L_TXN_ACTION_ID IN (12,22) ) THEN
882
883 SELECT 1
884 INTO L_SHIPMENT_COSTED
885 FROM CST_DIAG_ERRORED_TXNS SC
886 WHERE SC.TRANSACTION_ACTION_ID IN (12,21)
887 AND SC.SHIPMENT_COSTED IS NULL
888 AND EXISTS ( SELECT 'X'
889 FROM MTL_CST_TXN_COST_DETAILS MCTCD
890 WHERE MCTCD.TRANSACTION_ID = SC.BOTTLE_NECK_TXN_ID
891 AND MCTCD.ORGANIZATION_ID = SC.ORGANIZATION_ID
892 )
893 AND SC.BOTTLE_NECK_TXN_ID IS NOT NULL
894 AND SC.ORGANIZATION_ID = L_ORGANIZATION_ID;
895
896 IF (L_SHIPMENT_COSTED IS NOT NULL )
897 THEN
898 --DBMS_OUTPUT.PUT_LINE('THE SHIPMENT COSTED SHOULD BE Y');
899 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
900 ERROR_MESSAGE,RESOLUTION)
901 VALUES(L_BOTTLE_NECK_TXN_ID,'THE SHIPMENT COSTED SHOULD BE Y',NULL);
902 END IF;
903
904 ELSIF /* D - ERRORED TRANSACTION */
905 ( L_TXN_COSTED_FLAG ='E' ) THEN
906 --DBMS_OUTPUT.PUT_LINE('THE TRANSACTION ERRORED');
907 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
908 ERROR_MESSAGE,RESOLUTION)
909 VALUES(L_BOTTLE_NECK_TXN_ID,'TRANSACTION ERRORED NEEDS TO BE RESOLVED',NULL);
910 ELSIF ( L_TXN_ACTION_ID IN (3,21,12)) THEN
911 --DBMS_OUTPUT.PUT_LINE('RUN CHECKS FOR TRANSFER ORGANIZATION_ID '|| L_TXN_TXFR_ORG);
912 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
913 ERROR_MESSAGE,RESOLUTION)
914 VALUES(NULL,'RUN CHECKS FOR TRANSFER ORGANIZATION_ID '|| L_TXN_TXFR_TXN_ID,NULL);
915 ELSIF(L_BOTTLE_NECK_TXN_ID is NOT NULL) THEN
916 --DBMS_OUTPUT.PUT_LINE('REPORT COSTING BUG');
917 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
918 ERROR_MESSAGE,RESOLUTION)
919 VALUES(L_BOTTLE_NECK_TXN_ID,'REPORT A BUG WITH COSTING DEVELOPMENT',NULL);
920 ELSE
921 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
922 ERROR_MESSAGE,RESOLUTION)
923 VALUES(NULL,'NO UNCOSTED TRANSACTIONS FOUND!!',NULL);
924
925 END IF;
926 END IF;
927
928 EXCEPTION
929 WHEN COST_MANAGER_INACTIVE THEN
930 INSERT INTO CST_DIAG_TXN_ERRORS(TRANSACTION_ID,
931 ERROR_MESSAGE,RESOLUTION)
932 VALUES(NULL,'COST MANAGER IS INACTIVE','LAUNCH THE COST MANAGER TO GET THE TRANSACTIONS COSTED');
933 END Check_Transactions_MMT;
934
935 END CST_DIAGNOSTICS_PKG;