1 Package body OPI_EDW_JOB_RSRC_PVT as
2 /* $Header: OPIMJRPB.pls 115.4 2003/03/28 02:09:51 ltong noship $ */
3
4 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
5 -- dbdrv: checkfile:~PROD:~PATH:~FILE
6
7
8 --------------------------------------------------------------------
9 -- FUNCTION GET_ACT_STRT_DATE
10 -- Returns the Actual start date of an operation
11 --------------------------------------------------------------------
12 FUNCTION GET_ACT_STRT_DATE(
13 p_organization_id NUMBER,
14 p_wip_entity_id NUMBER,
15 p_repetitive_schedule_id NUMBER,
16 p_operation_seq_num NUMBER
17 ) RETURN DATE IS
18 l_transaction_date DATE := to_date(NULL) ;
19 l_operation_seq_num NUMBER := 0;
20 l_strt_date DATE := to_date(NULL);
21 BEGIN
22
23
24 SELECT min(operation_seq_num)
25 INTO l_operation_seq_num
26 FROM WIP_OPERATIONS
27 WHERE organization_id = p_organization_id
28 AND wip_entity_id = p_wip_entity_id
29 AND nvl(repetitive_schedule_id ,-99)= nvl(p_repetitive_schedule_id,-99) ;
30
31 if p_repetitive_schedule_id is NOT NULL THEN
32
33 IF l_operation_seq_num = p_operation_seq_num THEN
34 /* First Operation */
35 SELECT min(wmt.transaction_date)
36 INTO l_strt_date
37 FROM WIP_MOVE_TRANSACTIONS wmt, WIP_MOVE_TXN_ALLOCATIONS wmta
38 WHERE wmt.organization_id = p_organization_id
39 AND wmt.wip_entity_id = p_wip_entity_id
40 AND wmt.transaction_id = wmta.transaction_id
41 AND wmta.repetitive_schedule_id = p_repetitive_schedule_id
42 AND wmt.fm_operation_seq_num = p_operation_seq_num ;
43 ELSE
44 SELECT min(wmt.transaction_date)
45 INTO l_strt_date
46 FROM WIP_MOVE_TRANSACTIONS wmt, WIP_MOVE_TXN_ALLOCATIONS wmta
47 WHERE wmt.organization_id = p_organization_id
48 AND wmt.wip_entity_id = p_wip_entity_id
49 AND wmt.transaction_id = wmta.transaction_id
50 AND wmta.repetitive_schedule_id = p_repetitive_schedule_id
51 AND wmt.to_operation_seq_num = p_operation_seq_num ;
52 END IF ;
53
54 ELSE /* p_repetitive_schedule_id is NULL */
55
56 IF l_operation_seq_num = p_operation_seq_num THEN
57 /* First Operation */
58 SELECT min(transaction_date)
59 INTO l_strt_date
60 FROM WIP_MOVE_TRANSACTIONS
61 WHERE organization_id = p_organization_id
62 AND wip_entity_id = p_wip_entity_id
63 AND fm_operation_seq_num = p_operation_seq_num ;
64 ELSE
65
66 SELECT min(transaction_date)
67 INTO l_strt_date
68 FROM WIP_MOVE_TRANSACTIONS
69 WHERE organization_id = p_organization_id
70 AND wip_entity_id = p_wip_entity_id
71 AND to_operation_seq_num = p_operation_seq_num ;
72
73 END IF ;
74
75 END IF ;
76
77 return l_strt_date ;
78
79 END GET_ACT_STRT_DATE ;
80
81 --------------------------------------------------------------------
82 -- FUNCTION GET_ACT_CMPL_DATE
83 -- Returns the Actual completion date of an operation
84 --------------------------------------------------------------------
85 FUNCTION GET_ACT_CMPL_DATE(
86 p_organization_id NUMBER,
87 p_wip_entity_id NUMBER,
88 p_repetitive_schedule_id NUMBER,
89 p_operation_seq_num NUMBER
90 ) RETURN DATE IS
91 l_transaction_date DATE := to_date(NULL) ;
92 l_operation_seq_num NUMBER := 0;
93 l_cmpl_date DATE := to_date(NULL);
94 BEGIN
95 SELECT max(operation_seq_num)
96 INTO l_operation_seq_num
97 FROM WIP_OPERATIONS
98 WHERE organization_id = p_organization_id
99 AND wip_entity_id = p_wip_entity_id
100 AND nvl(repetitive_schedule_id,-99) = nvl(p_repetitive_schedule_id,-99) ;
101
102
103 IF p_repetitive_schedule_id is NOT NULL THEN
104
105 IF l_operation_seq_num = p_operation_seq_num THEN
106 /* Last Operation */
107 SELECT max(wmt.transaction_date)
108 INTO l_cmpl_date
109 FROM WIP_MOVE_TRANSACTIONS wmt, WIP_MOVE_TXN_ALLOCATIONS wmta
110 WHERE wmt.organization_id = p_organization_id
111 AND wmt.wip_entity_id = p_wip_entity_id
112 AND wmt.transaction_id = wmta.transaction_id
113 AND wmta.repetitive_schedule_id = p_repetitive_schedule_id
114 AND wmt.to_operation_seq_num = p_operation_seq_num ;
115 ELSE
116 SELECT max(wmt.transaction_date)
117 INTO l_cmpl_date
118 FROM WIP_MOVE_TRANSACTIONS wmt, WIP_MOVE_TXN_ALLOCATIONS wmta
119 WHERE wmt.organization_id = p_organization_id
120 AND wmt.wip_entity_id = p_wip_entity_id
121 AND wmt.transaction_id = wmta.transaction_id
122 AND wmta.repetitive_schedule_id = p_repetitive_schedule_id
123 AND wmt.fm_operation_seq_num = p_operation_seq_num ;
124 END IF ;
125
126 /* fix for bug 2839182 */
127 IF l_cmpl_date IS NULL THEN
128 SELECT Decode( status_type,12, date_closed, last_update_date)
129 INTO l_cmpl_date
130 FROM wip_repetitive_schedules
131 WHERE status_type IN (4,5,7,12)
132 AND repetitive_schedule_id = p_repetitive_schedule_id;
133 END IF;
134
135 ELSE /* p_repetitive_schedule_id is NULL */
136
137 IF l_operation_seq_num = p_operation_seq_num THEN
138 /* Last Operation */
139 SELECT max(wmt.transaction_date)
140 INTO l_cmpl_date
141 FROM WIP_MOVE_TRANSACTIONS wmt
142 WHERE wmt.organization_id = p_organization_id
143 AND wmt.wip_entity_id = p_wip_entity_id
144 AND wmt.to_operation_seq_num = p_operation_seq_num ;
145 ELSE
146 SELECT max(wmt.transaction_date)
147 INTO l_cmpl_date
148 FROM WIP_MOVE_TRANSACTIONS wmt
149 WHERE wmt.organization_id = p_organization_id
150 AND wmt.wip_entity_id = p_wip_entity_id
151 AND wmt.fm_operation_seq_num = p_operation_seq_num ;
152 END IF ;
153
154 /* fix for bug 2839182 */
155 IF l_cmpl_date IS NULL THEN
156 SELECT Decode( status_type,12, date_closed,
157 4, date_completed,
158 5, date_completed,
159 last_update_date)
160 INTO l_cmpl_date
161 FROM wip_discrete_jobs
162 WHERE status_type IN (4,5,7,12)
163 AND wip_entity_id = p_wip_entity_id;
164 END IF;
165 END IF ;
166
167 return l_cmpl_date ;
168
169 END GET_ACT_CMPL_DATE ;
170
171 end OPI_EDW_JOB_RSRC_PVT ;
[Home] [Help]