[Home] [Help]
PACKAGE BODY: APPS.EAM_OP_COMP_UTILITY_PVT
Source
1 PACKAGE BODY EAM_OP_COMP_UTILITY_PVT AS
2 /* $Header: EAMVOCUB.pls 120.2 2006/06/16 14:57:00 gbadoni noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVOCUB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_OP_COMP_UTILITY_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 14-FEB-2005 mmaduska Initial Creation
21 ***************************************************************************/
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_OP_COMP_UTILITY_PVT';
23
24 PROCEDURE Perform_Writes
25 (
26 p_eam_op_comp_rec IN EAM_PROCESS_WO_PUB.eam_op_comp_rec_type
27 , x_return_status OUT NOCOPY VARCHAR2
28 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
29 )
30 IS
31 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
32 BEGIN
33
34 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Entered Package EAM_OP_COMP_UTILITY_PVT.Perform_Writes procedure..'); END IF;
35
36 IF p_eam_op_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_COMPLETE OR
37 p_eam_op_comp_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UNCOMPLETE
38 THEN
39 Insert_Row
40 ( p_eam_op_comp_rec => p_eam_op_comp_rec
41 , x_mesg_token_Tbl => x_mesg_token_tbl
42 , x_return_Status => l_return_status
43 );
44 END IF;
45
46 x_return_status := l_return_status;
47 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Exiting Package EAM_OP_COMP_UTILITY_PVT.Perform_Writes procedure ...'); END IF;
48
49 END Perform_Writes;
50
51 PROCEDURE insert_row
52 (
53 p_eam_op_comp_rec IN EAM_PROCESS_WO_PUB.eam_op_comp_rec_type
54 , x_return_status OUT NOCOPY VARCHAR2
55 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
56 )
57 IS
58 l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
59 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
60 l_status_id NUMBER;
61 l_maintenance_object_id NUMBER := NULL ;
62 l_maintenance_object_type NUMBER := NULL ;
63 l_asset_group_id NUMBER;
64 l_asset_number VARCHAR2(30);
65
66 BEGIN
67
68 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Entered Package EAM_OP_COMP_UTILITY_PVT.insert_row procedure..'); END IF;
69
70 INSERT INTO EAM_OP_COMPLETION_TXNS( /* Insert statement from EAMOCMPB.pls */
71 transaction_id,
72 transaction_date,
73 transaction_type,
74 wip_entity_id,
75 organization_id,
76 operation_seq_num,
77 acct_period_id,
78 qa_collection_id,
79 reference,
80 reconciliation_code,
81 department_id,
82 actual_start_date,
83 actual_end_date,
84 actual_duration,
85 vendor_id,
86 vendor_site_id,
87 vendor_contact_id,
88 reason_id,
89 transaction_reference,
90 created_by,
91 creation_date,
92 last_updated_by,
93 last_update_date,
94 last_update_login,
95 attribute_category,
96 attribute1,
97 attribute2,
98 attribute3,
99 attribute4,
100 attribute5,
101 attribute6,
102 attribute7,
103 attribute8,
104 attribute9,
105 attribute10,
106 attribute11,
107 attribute12,
108 attribute13,
109 attribute14,
110 attribute15
111
112 ) VALUES (
113 p_eam_op_comp_rec.transaction_id,
114 p_eam_op_comp_rec.transaction_date,
115 decode(p_eam_op_comp_rec.transaction_type,EAM_PROCESS_WO_PVT.G_OPR_COMPLETE,1,EAM_PROCESS_WO_PVT.G_OPR_UNCOMPLETE,2),
116 p_eam_op_comp_rec.wip_entity_id,
117 p_eam_op_comp_rec.organization_id,
118 p_eam_op_comp_rec.operation_seq_num,
119 NULL, -- acct_period_id
120 p_eam_op_comp_rec.qa_collection_id,
121 NULL, -- reference
122 p_eam_op_comp_rec.reconciliation_code,
123 p_eam_op_comp_rec.department_id,
124 p_eam_op_comp_rec.actual_start_date,
125 p_eam_op_comp_rec.actual_end_date,
126 p_eam_op_comp_rec.actual_duration,
127 p_eam_op_comp_rec.vendor_id,
128 p_eam_op_comp_rec.vendor_site_id,
129 p_eam_op_comp_rec.vendor_contact_id,
130 p_eam_op_comp_rec.reason_id,
131 p_eam_op_comp_rec.reference, -- transaction_reference
132 FND_GLOBAL.user_id,
133 sysdate,
134 FND_GLOBAL.user_id,
135 sysdate,
136 FND_GLOBAL.login_id,
137 p_eam_op_comp_rec.attribute_category,
138 p_eam_op_comp_rec.attribute1,
139 p_eam_op_comp_rec.attribute2,
140 p_eam_op_comp_rec.attribute3,
141 p_eam_op_comp_rec.attribute4,
142 p_eam_op_comp_rec.attribute5,
143 p_eam_op_comp_rec.attribute6,
144 p_eam_op_comp_rec.attribute7,
145 p_eam_op_comp_rec.attribute8,
146 p_eam_op_comp_rec.attribute9,
147 p_eam_op_comp_rec.attribute10,
148 p_eam_op_comp_rec.attribute11,
149 p_eam_op_comp_rec.attribute12,
150 p_eam_op_comp_rec.attribute13,
151 p_eam_op_comp_rec.attribute14,
152 p_eam_op_comp_rec.attribute15
153 );
154
155 --
156 -- SHUTDOWN History
157 IF (p_eam_op_comp_rec.shutdown_start_date IS NOT NULL) OR
158 (p_eam_op_comp_rec.shutdown_end_date IS NOT NULL) THEN
159
160 SELECT eam_asset_status_history_s.nextval
161 INTO l_status_id
162 FROM dual;
163
164 --bug 3572376: pass maintenance object type and id
165 BEGIN
166
167 SELECT nvl(wdj.rebuild_item_id, wdj.asset_number),
168 nvl(wdj.rebuild_serial_number, wdj.asset_group_id),
169 wdj.maintenance_object_type,
170 wdj.maintenance_object_id
171 INTO l_asset_group_id,
172 l_asset_number,
173 l_maintenance_object_type,
174 l_maintenance_object_id
175 FROM wip_discrete_jobs wdj
176 WHERE wdj.wip_entity_id = p_eam_op_comp_rec.wip_entity_id ;
177
178 EXCEPTION
179 WHEN NO_DATA_FOUND THEN
180 l_maintenance_object_type := NULL;
181 l_maintenance_object_id := NULL;
182 END;
183
184 INSERT INTO EAM_ASSET_STATUS_HISTORY(
185 asset_status_id,
186 asset_group_id,
187 asset_number,
188 organization_id,
189 start_date,
190 end_date,
191 wip_entity_id,
192 operation_seq_num,
193 created_by,
194 creation_date,
195 last_updated_by,
196 last_update_date,
197 last_update_login,
198 attribute_category,
199 attribute1,
200 attribute2,
201 attribute3,
202 attribute4,
203 attribute5,
204 attribute6,
205 attribute7,
206 attribute8,
207 attribute9,
208 attribute10,
209 attribute11,
210 attribute12,
211 attribute13,
212 attribute14,
213 attribute15
214 ,maintenance_object_type
215 ,maintenance_object_id
216 ,enable_flag -- enhancemnet bug 3852846
217
218 ) VALUES (
219 l_status_id,
220 l_asset_group_id,
221 l_asset_number,
222 p_eam_op_comp_rec.organization_id,
223 p_eam_op_comp_rec.shutdown_start_date,
224 p_eam_op_comp_rec.shutdown_end_date,
225 p_eam_op_comp_rec.wip_entity_id,
226 p_eam_op_comp_rec.operation_seq_num,
227 FND_GLOBAL.user_id,
228 SYSDATE,
229 FND_GLOBAL.user_id,
230 SYSDATE,
231 FND_GLOBAL.login_id,
232 p_eam_op_comp_rec.attribute_category,
233 p_eam_op_comp_rec.attribute1,
234 p_eam_op_comp_rec.attribute2,
235 p_eam_op_comp_rec.attribute3,
236 p_eam_op_comp_rec.attribute4,
237 p_eam_op_comp_rec.attribute5,
238 p_eam_op_comp_rec.attribute6,
239 p_eam_op_comp_rec.attribute7,
240 p_eam_op_comp_rec.attribute8,
241 p_eam_op_comp_rec.attribute9,
242 p_eam_op_comp_rec.attribute10,
243 p_eam_op_comp_rec.attribute11,
244 p_eam_op_comp_rec.attribute12,
245 p_eam_op_comp_rec.attribute13,
246 p_eam_op_comp_rec.attribute14,
247 p_eam_op_comp_rec.attribute15
248 ,l_maintenance_object_type
249 ,l_maintenance_object_id
250 ,'Y' -- Enhancemnet Bug 3852846
251 );
252
253 END IF; -- history insert
254 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Calling EAM_OP_COMP_UTILITY_PVT.update_row from EAM_OP_COMP_UTILITY_PVT.insert_row procedure'); END IF;
255
256 update_row
257 ( p_eam_op_comp_rec => p_eam_op_comp_rec
258 , x_mesg_token_Tbl => l_mesg_token_tbl
259 , x_return_Status => l_return_status
260 );
261 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Returned from EAM_OP_COMP_UTILITY_PVT.update_row to EAM_OP_COMP_UTILITY_PVT.insert_row procedure'); END IF;
262
263 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Exiting Package EAM_OP_COMP_UTILITY_PVT.insert_row procedure with status:'||l_return_status); END IF;
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Exception happened Package EAM_OP_COMP_UTILITY_PVT.insert_row procedure..' || SQLERRM); END IF;
268 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
269 ( p_message_name => NULL
270 , p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
271 , x_mesg_token_Tbl => l_Mesg_Token_tbl
272 );
273 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_ERROR;
274 x_mesg_token_Tbl := l_Mesg_Token_tbl;
275
276 END insert_row;
277
278 PROCEDURE update_row
279 (
280 p_eam_op_comp_rec IN EAM_PROCESS_WO_PUB.eam_op_comp_rec_type
281 , x_return_status OUT NOCOPY VARCHAR2
282 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
283 )
284 IS
285 l_shutdown_type VARCHAR2(30) :=NULL;
286 BEGIN
287
288 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Entered Package EAM_OP_COMP_UTILITY_PVT.update_row procedure..'); END IF;
289
290 SELECT wo.shutdown_type INTO l_shutdown_type
291 FROM wip_operations wo
292 WHERE wo.wip_entity_id = p_eam_op_comp_rec.wip_entity_id
293 AND wo.operation_seq_num = p_eam_op_comp_rec.operation_seq_num;
294
295 -- Enhancemnet Bug 3852846
296 IF NVL(to_number(l_shutdown_type),1) = 2 THEN
297 UPDATE eam_asset_status_history
298 SET enable_flag = 'N'
299 , last_update_date = SYSDATE
300 , last_updated_by = FND_GLOBAL.user_id
301 , last_update_login = FND_GLOBAL.login_id
302 WHERE organization_id = p_eam_op_comp_rec.organization_id
303 AND wip_entity_id = p_eam_op_comp_rec.wip_entity_id
304 AND operation_seq_num = p_eam_op_comp_rec.operation_seq_num
305 AND enable_flag = 'Y' OR enable_flag IS NULL;
306 END IF;
307
308 UPDATE wip_operations
309 SET
310 operation_completed = decode(p_eam_op_comp_rec.transaction_type,EAM_PROCESS_WO_PVT.G_OPR_COMPLETE,'Y',EAM_PROCESS_WO_PVT.G_OPR_UNCOMPLETE,'N'),
311 quantity_completed = 1,
312 last_updated_by = FND_GLOBAL.user_id,
313 last_update_date = sysdate,
314 last_update_login = FND_GLOBAL.login_id
315 WHERE
316 wip_entity_id = p_eam_op_comp_rec.wip_entity_id
317 AND operation_seq_num = p_eam_op_comp_rec.operation_seq_num;
318 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Exiting Package EAM_OP_COMP_UTILITY_PVT.update_row procedure'); END IF;
319
320 END update_row;
321
322
323 END EAM_OP_COMP_UTILITY_PVT;