[Home] [Help]
PACKAGE BODY: APPS.AHL_PRD_WORKORDER_PUB
Source
1 PACKAGE BODY AHL_PRD_WORKORDER_PUB AS
2 /* $Header: AHLPWOSB.pls 120.2.12020000.2 2012/12/10 16:36:25 prakkum ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_PRD_WORKORDER_PUB';
5
6 FUNCTION get_workorder_id(p_Workorder_Number IN VARCHAR2) RETURN NUMBER;
7
8 FUNCTION get_workorder_operation_id(p_Workorder_Id IN NUMBER,p_operation_sequence IN NUMBER) RETURN NUMBER;
9
10 FUNCTION get_qa_sql_str(p_plan_id IN NUMBER) RETURN VARCHAR2;
11
12 FUNCTION IS_VALID_RESULT_ATTRIBUTE(p_Char_Id IN NUMBER, p_QA_PLAN IN QA_PLAN_REC_TYPE) RETURN VARCHAR2;
13
14 PROCEDURE get_qa_plan_results
15 (
16 p_api_version IN NUMBER := 1.0,
17 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
18 p_commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 p_default IN VARCHAR2 := FND_API.G_FALSE,
21 p_module_type IN VARCHAR2,
22 p_Workorder_Id IN NUMBER,
23 p_Workorder_Number IN VARCHAR2,
24 p_WoOperationId IN NUMBER,
25 p_OperationSequence IN NUMBER,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2,
29 x_QaPlan OUT NOCOPY QA_PLAN_REC_TYPE,
30 x_QaResults OUT NOCOPY QA_RESULTS_REC_TYPE
31 ) IS
32
33 l_api_version CONSTANT NUMBER := 1.0;
34 l_api_name CONSTANT VARCHAR2(30) := 'get_qa_plan_results';
35 l_workorder_id NUMBER;
36 l_workorder_operation_id NUMBER;
37
38 CURSOR get_wo_plan_csr(p_Workorder_Id IN NUMBER)IS
39 Select plan_id,collection_id from ahl_search_workorders_v
40 WHERE workorder_id = p_Workorder_Id;
41
42 CURSOR get_op_plan_csr(p_WoOperationId IN NUMBER)IS
43 Select plan_id,collection_id from ahl_workorder_operations_v
44 WHERE workorder_operation_id = p_WoOperationId;
45
46
47
48 l_plan_id NUMBER;
49 l_collection_id NUMBER;
50 l_attribute_index NUMBER;
51
52 CURSOR get_plan_attributes_csr(p_plan_id IN NUMBER) IS
53 SELECT char_id , prompt_sequence
54 , prompt, enabled_flag , default_value
55 , default_value_id , result_column_name
56 , DECODE( NVL( sql_string_flag, 'N' ), 'N', DECODE( hardcoded_column, NULL, 'N', DECODE( QA_SS_LOV_API.get_lov_sql( plan_id, char_id, organization_id, null, null, null, null, null ), NULL, 'N', 'Y' ) ), sql_string_flag ) sql_string_flag
57 , values_exist_flag, displayed_flag
58 , char_name , datatype , display_length, hardcoded_column
59 , developer_name , mandatory_flag
60 FROM QA_PLAN_CHARS_V WHERE plan_id = p_plan_id ORDER BY prompt_sequence;
61
62 CURSOR get_plan_csr(p_plan_id IN NUMBER) IS
63 SELECT plan_id , organization_id, name
64 , description FROM QA_PLANS_V
65 WHERE plan_id = p_plan_id;
66
67 CURSOR get_op_default_values_csr(p_WoOperationId IN NUMBER)IS
68 SELECT WO.JOB_NUMBER,
69 AMH.TITLE MR_TITLE,
70 WOP.OPERATION_SEQUENCE_NUM,
71 WOP.STATUS_CODE,
72 WO.JOB_STATUS_CODE,
73 WO.WO_PART_NUMBER,
74 WO.ITEM_INSTANCE_NUMBER,
75 WO.LOT_NUMBER,
76 WO.SERIAL_NUMBER
77 FROM AHL_WORKORDERS_V WO, AHL_WORKORDER_OPERATIONS WOP,
78 AHL_MR_ROUTES AMR, AHL_MR_HEADERS_B AMH
79 WHERE WO.MR_ROUTE_ID=AMR.MR_ROUTE_ID (+)
80 AND AMR.MR_HEADER_ID=AMH.MR_HEADER_ID(+)
81 AND WO.WORKORDER_ID = WOP.WORKORDER_ID
82 AND WOP.workorder_operation_id = p_WoOperationId;
83
84 op_defaults get_op_default_values_csr%ROWTYPE;
85
86 CURSOR get_wo_default_values_csr(p_Workorder_Id IN NUMBER)IS
87 SELECT WO.JOB_NUMBER,
88 AMH.TITLE MR_TITLE,
89 WO.JOB_STATUS_CODE,
90 WO.WO_PART_NUMBER,
91 WO.ITEM_INSTANCE_NUMBER,
92 WO.LOT_NUMBER,
93 WO.SERIAL_NUMBER
94 FROM AHL_WORKORDERS_V WO,
95 AHL_MR_ROUTES AMR, AHL_MR_HEADERS_B AMH
96 WHERE WO.MR_ROUTE_ID=AMR.MR_ROUTE_ID (+)
97 AND AMR.MR_HEADER_ID=AMH.MR_HEADER_ID(+)
98 AND WO.WORKORDER_ID = p_Workorder_Id;
99
100 wo_defaults get_wo_default_values_csr%ROWTYPE;
101
102
103
104 l_bindvar_tbl AHL_OSP_UTIL_PKG.ahl_conditions_tbl;
105 -- dynamic cursor
106 l_cur AHL_OSP_UTIL_PKG.ahl_search_csr;
107 l_results_sql_str VARCHAR2(6000);
108 source_cursor INTEGER;
109 dummy INTEGER;
110 l_occurence NUMBER;
111 l_temp VARCHAR2(4000);
112 l_result_row_index NUMBER;
113 l_result_column_index NUMBER;
114
115
116 BEGIN
117 -- Initialize API return status to success
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119 x_QaPlan.QA_PLAN_ATR_TBL(0) := NULL;
120 x_QaResults.QA_RESULT_TBL(0).QA_PLAN_ATRVAL_TBL(0) := NULL;
121
122 -- Standard call to check for call compatibility
123 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END IF;
126
127 IF FND_API.To_Boolean(p_init_msg_list) THEN
128 FND_MSG_PUB.Initialize;
129 END IF;
130
131 -- Check Error Message stack.
132 IF(p_Workorder_Id IS NULL AND p_Workorder_Number IS NULL)THEN
133 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
134 FND_MSG_PUB.ADD;
135 RAISE FND_API.G_EXC_ERROR;
136 ELSIF (p_Workorder_Id IS NULL AND p_Workorder_Number IS NOT NULL)THEN
137 l_workorder_id := get_workorder_id(p_Workorder_Number);
138 ELSE
139 l_workorder_id := p_Workorder_Id;
140 END IF;
141
142 x_msg_count := FND_MSG_PUB.count_msg;
143 IF x_msg_count > 0 THEN
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146
147 IF(p_WoOperationId IS NULL AND p_OperationSequence IS NULL)THEN
148 l_workorder_operation_id := NULL;
149 ELSIF (p_WoOperationId IS NULL AND p_OperationSequence IS NOT NULL)THEN
150 l_workorder_operation_id := get_workorder_operation_id(l_workorder_id, p_OperationSequence);
151 ELSE
152 l_workorder_operation_id := p_WoOperationId;
153 END IF;
154 --l_workorder_operation_id := NULL;
155 x_msg_count := FND_MSG_PUB.count_msg;
156 IF x_msg_count > 0 THEN
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159
160 IF(l_workorder_operation_id IS NOT NULL)THEN
161 OPEN get_op_plan_csr(l_workorder_operation_id);
162 FETCH get_op_plan_csr INTO l_plan_id, l_collection_id;
163 CLOSE get_op_plan_csr;
164 OPEN get_op_default_values_csr(l_workorder_operation_id);
165 FETCH get_op_default_values_csr INTO op_defaults;
166 CLOSE get_op_default_values_csr;
167 ELSIF(l_workorder_id IS NOT NULL)THEN
168 OPEN get_wo_plan_csr(l_workorder_id);
169 FETCH get_wo_plan_csr INTO l_plan_id, l_collection_id;
170 CLOSE get_wo_plan_csr;
171 OPEN get_wo_default_values_csr(l_workorder_id);
172 FETCH get_wo_default_values_csr INTO wo_defaults;
173 CLOSE get_wo_default_values_csr;
174 END IF;
175 --fetch plan
176 IF(l_plan_id IS NOT NULL)THEN
177 OPEN get_plan_csr(l_plan_id);
178 FETCH get_plan_csr INTO x_QaPlan.Plan_Id,x_QaPlan.Organization_Id ,
179 x_QaPlan.Plan_Name, x_QaPlan.Plan_Description;
180 CLOSE get_plan_csr;
181 l_attribute_index :=0;
182 FOR qa_plan_attributes IN get_plan_attributes_csr(l_plan_id)LOOP
183 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id := qa_plan_attributes.char_id;
184 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Prompt_Sequence := qa_plan_attributes.prompt_sequence;
185 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Prompt := qa_plan_attributes.prompt;
186 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := qa_plan_attributes.default_value;
187 IF(qa_plan_attributes.displayed_flag = 1) THEN
188 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_Displayed := 'T';
189 ELSE
190 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_Displayed := 'F';
191 END IF;
192 IF(qa_plan_attributes.mandatory_flag = 1) THEN
193 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_Mandatory := 'T';
194 ELSE
195 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_Mandatory := 'F';
196 END IF;
197 IF(qa_plan_attributes.enabled_flag = 1) THEN
198 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_ReadOnly := 'F';
199 ELSE
200 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_ReadOnly := 'T';
201 END IF;
202 IF(qa_plan_attributes.sql_string_flag = 'Y'
203 AND x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_ReadOnly = 'F'
204 AND x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_Displayed = 'T')THEN
205 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_ListOfValue := 'T';
206 ELSE
207 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Is_ListOfValue := 'F';
208 END IF;
209 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Display_Length := qa_plan_attributes.display_length;
210 IF(qa_plan_attributes.datatype = 2)THEN
211 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DataType := 'integer';
212 ELSIF(qa_plan_attributes.datatype = 3)THEN
213 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DataType := 'date';
214 ELSIF(qa_plan_attributes.datatype = 6)THEN
215 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DataType := 'dateTime';
216 ELSE
217 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).DataType := 'string';
218 END IF;
219
220 IF(l_workorder_operation_id IS NOT NULL)THEN
221 IF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 165)THEN
222 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.JOB_NUMBER;
223 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 44)THEN
224 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.MR_TITLE;
225 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 199)THEN
226 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.OPERATION_SEQUENCE_NUM;
227 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 98)THEN
228 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.JOB_STATUS_CODE;
229 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 125)THEN
230 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.STATUS_CODE;
231 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 10)THEN
232 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.WO_PART_NUMBER;
233 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 30)THEN
234 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.ITEM_INSTANCE_NUMBER;
235 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 84)THEN
236 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.LOT_NUMBER;
237 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 147)THEN
238 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := op_defaults.SERIAL_NUMBER;
239 END IF;
240 ELSE
241 IF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 165)THEN
242 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := wo_defaults.JOB_NUMBER;
243 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 44)THEN
244 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := wo_defaults.MR_TITLE;
245 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 98)THEN
246 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := wo_defaults.JOB_STATUS_CODE;
247 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 10)THEN
248 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := wo_defaults.WO_PART_NUMBER;
249 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 30)THEN
250 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := wo_defaults.ITEM_INSTANCE_NUMBER;
251 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 84)THEN
252 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := wo_defaults.LOT_NUMBER;
253 ELSIF(x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Char_Id = 147)THEN
254 x_QaPlan.QA_PLAN_ATR_TBL(l_attribute_index).Default_Value := wo_defaults.SERIAL_NUMBER;
255 END IF;
256 END IF;
257 l_attribute_index := l_attribute_index+1;
258 END LOOP;
259 END IF;
260 --fetch results
261 IF(l_plan_id IS NOT NULL AND l_collection_id IS NOT NULL)THEN
262 x_QaResults.Plan_Id := l_plan_id;
263 x_QaResults.Collection_Id := l_collection_id;
264 l_results_sql_str := get_qa_sql_str(l_plan_id);
265 source_cursor := DBMS_sql.open_cursor;
266 DBMS_SQL.parse(source_cursor,l_results_sql_str,DBMS_SQL.native);
267 DBMS_SQL.BIND_VARIABLE(source_cursor, ':1', l_collection_id);
268 DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, l_occurence);
269
270 FOR i IN x_QaPlan.QA_PLAN_ATR_TBL.FIRST..x_QaPlan.QA_PLAN_ATR_TBL.LAST LOOP
271 DBMS_SQL.DEFINE_COLUMN(source_cursor, i+2, l_temp,4000);
272 END LOOP;
273 dummy := DBMS_SQL.EXECUTE(source_cursor);
274 l_result_row_index := 0;
275 LOOP
276 IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
277
278 DBMS_SQL.COLUMN_VALUE(source_cursor, 1, l_occurence);
279 x_QaResults.QA_RESULT_TBL(l_result_row_index).Occurence := l_occurence;
280
281 FOR i IN x_QaPlan.QA_PLAN_ATR_TBL.FIRST..x_QaPlan.QA_PLAN_ATR_TBL.LAST LOOP
282 DBMS_SQL.COLUMN_VALUE(source_cursor, i+2, l_temp);
283 x_QaResults.QA_RESULT_TBL(l_result_row_index).QA_PLAN_ATRVAL_TBL(i).Char_Id
284 := x_QaPlan.QA_PLAN_ATR_TBL(i).Char_Id;
285 x_QaResults.QA_RESULT_TBL(l_result_row_index).QA_PLAN_ATRVAL_TBL(i).Attribute_Value
286 := l_temp;
287
288 END LOOP;
289 ELSE
290 EXIT;
291 END IF;
292 l_result_row_index := l_result_row_index + 1;
293 END LOOP;
294 END IF;
295 -- Check Error Message stack.
296 x_msg_count := FND_MSG_PUB.count_msg;
297
298 EXCEPTION
299 WHEN FND_API.G_EXC_ERROR THEN
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
302 p_data => x_msg_data,
303 p_encoded => fnd_api.g_false);
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
307 p_data => x_msg_data,
308 p_encoded => fnd_api.g_false);
309 WHEN OTHERS THEN
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311
312 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
313 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
314 p_procedure_name => l_api_name,
315 p_error_text => SUBSTR(SQLERRM,1,500));
316 END IF;
317 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
318 p_data => x_msg_data,
319 p_encoded => fnd_api.g_false);
320 END get_qa_plan_results;
321
322 FUNCTION get_workorder_id(p_Workorder_Number IN VARCHAR2) RETURN NUMBER
323 IS
324
325 l_workorder_id NUMBER;
326
327 CURSOR get_workorder_id_csr(p_workorder_number IN VARCHAR2) IS
328 Select WORKORDER_ID FROM AHL_WORKORDERS
329 WHERE WORKORDER_NAME = p_workorder_number;
330
331 BEGIN
332 OPEN get_workorder_id_csr(p_Workorder_Number);
333 FETCH get_workorder_id_csr INTO l_workorder_id;
334 IF(get_workorder_id_csr%NOTFOUND)THEN
335 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
336 FND_MSG_PUB.ADD;
337 CLOSE get_workorder_id_csr;
338 RAISE FND_API.G_EXC_ERROR;
339 END IF;
340 CLOSE get_workorder_id_csr;
341 RETURN l_workorder_id;
342 END get_workorder_id;
343
344 FUNCTION get_workorder_operation_id(p_Workorder_Id IN NUMBER,p_operation_sequence IN NUMBER) RETURN NUMBER
345 IS
346
347 l_workorder_operation_id NUMBER;
348
349 CURSOR get_workorder_operation_id_csr(p_Workorder_Id IN NUMBER,p_operation_sequence IN NUMBER) IS
350 Select WORKORDER_OPERATION_ID FROM AHL_WORKORDER_OPERATIONS
351 WHERE WORKORDER_ID = p_Workorder_Id AND
352 OPERATION_SEQUENCE_NUM = p_operation_sequence;
353
354 BEGIN
355 OPEN get_workorder_operation_id_csr(p_Workorder_Id, p_operation_sequence);
356 FETCH get_workorder_operation_id_csr INTO l_workorder_operation_id;
357 IF(get_workorder_operation_id_csr%NOTFOUND)THEN
358 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_WO_NOT_FOUND');
359 FND_MSG_PUB.ADD;
360 CLOSE get_workorder_operation_id_csr;
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363 CLOSE get_workorder_operation_id_csr;
364 RETURN l_workorder_operation_id;
365 END get_workorder_operation_id;
366
367
368
369 FUNCTION get_qa_sql_str(p_plan_id IN NUMBER) RETURN VARCHAR2 IS
370
371 CURSOR get_plan_attributes_csr(p_plan_id IN NUMBER) IS
372 SELECT char_id , prompt_sequence
373 , prompt, enabled_flag , default_value
374 , default_value_id , result_column_name
375 , DECODE( NVL( sql_string_flag, 'N' ), 'N', DECODE( hardcoded_column, NULL, 'N', DECODE( QA_SS_LOV_API.get_lov_sql( plan_id, char_id, organization_id, null, null, null, null, null ), NULL, 'N', 'Y' ) ), sql_string_flag ) sql_string_flag
376 , values_exist_flag, displayed_flag
377 , char_name , datatype , display_length, hardcoded_column
378 , developer_name , mandatory_flag
379 FROM QA_PLAN_CHARS_V WHERE plan_id = p_plan_id ORDER BY prompt_sequence;
380
381 l_sql_string VARCHAR2(6000);
382 l_result_column VARCHAR2(240);
383 l_result_column_name VARCHAR2(240);
384
385
386 BEGIN
387 l_sql_string := 'SELECT RESULTS.OCCURRENCE ';
388 FOR qa_plan_attributes IN get_plan_attributes_csr(p_plan_id)LOOP
389 IF(qa_plan_attributes.char_id = 10)THEN -- ITEM NUMBER
390 l_result_column := 'CONCATENATED_SEGMENTS';
391 ELSIF (qa_plan_attributes.char_id = 15)THEN -- LOCATOR
392 l_result_column := 'CONCATENATED_SEGMENTS';
393 ELSE
394 IF ( qa_plan_attributes.hardcoded_column IS NOT NULL ) THEN
395 l_result_column := qa_plan_attributes.developer_name;
396 ELSE
397 l_result_column := qa_plan_attributes.result_column_name;
398 END IF;
399 END IF;
400
401 IF ( qa_plan_attributes.char_id = 10 )THEN
402 l_result_column_name := 'ITEM.' || l_result_column || ' ';
403 l_result_column_name := '(Select ' ||l_result_column_name || ' FROM MTL_SYSTEM_ITEMS_KFV ITEM '
404 || ' WHERE ITEM.inventory_item_id = RESULTS.'|| qa_plan_attributes.hardcoded_column
405 || ' AND ITEM.organization_id = RESULTS.organization_id ) ' || l_result_column;
406
407 ELSIF ( qa_plan_attributes.char_id = 15) THEN
408 l_result_column_name := 'LOCATOR.' || l_result_column || ' ';
409
410 l_result_column_name := '(Select ' || l_result_column_name ||' FROM MTL_ITEM_LOCATIONS_KFV LOCATOR '
411 || ' WHERE LOCATOR.inventory_location_id = RESULTS.' || qa_plan_attributes.hardcoded_column
412 + ' AND LOCATOR.organization_id = RESULTS.organization_id ) ' || l_result_column;
413
414 ELSE
415
416 l_result_column_name := 'to_char(RESULTS.' || l_result_column || ')';
417 END IF;
418 l_sql_string := l_sql_string || ' , ' || l_result_column_name;
419 END LOOP;
420
421 l_sql_string := l_sql_string || ' FROM QA_RESULTS_V RESULTS WHERE COLLECTION_ID = :1 ORDER BY RESULTS.OCCURRENCE DESC';
422 RETURN l_sql_string;
423 END get_qa_sql_str;
424
425
426
427 PROCEDURE process_wo_details
428 (
429 p_x_workorder_rec IN OUT NOCOPY WORKORDER_REC_TYPE,
430 p_curr_workorder_rec IN WORKORDER_REC_TYPE,
431 x_return_status OUT NOCOPY VARCHAR2,
432 x_msg_count OUT NOCOPY NUMBER,
433 x_msg_data OUT NOCOPY VARCHAR2
434 )IS
435
436 l_prd_workorder_rec AHL_PRD_WORKORDER_PVT.prd_workorder_rec;
437 l_prd_workoper_tbl AHL_PRD_WORKORDER_PVT.prd_workoper_tbl;
438 l_dummy_ctr NUMBER;
439
440 CURSOR get_status_csr(status VARCHAR)
441 IS
442 SELECT lookup_code
443 FROM fnd_Lookups
444 WHERE lookup_type = 'AHL_JOB_STATUS'
445 AND meaning = status;
446
447 CURSOR validate_status_csr(c_status_new VARCHAR, c_status_old VARCHAR)
448 IS
449 SELECT 1
450 FROM AHL_STATUS_ORDER_RULES
451 WHERE system_status_type = 'AHL_JOB_STATUS'
452 AND CURRENT_STATUS_CODE = c_status_old
453 AND NEXT_STATUS_CODE = c_status_new;
454
455 BEGIN
456 x_return_status := FND_API.G_RET_STS_SUCCESS;
457
458 IF p_x_workorder_rec.status_code IS NULL AND p_x_workorder_rec.status IS NOT NULL THEN
459 OPEN get_status_csr(p_x_workorder_rec.status);
460 FETCH get_status_csr INTO p_x_workorder_rec.status_code;
461 IF get_status_csr%NOTFOUND THEN
462 FND_MESSAGE.SET_NAME('AHL','AHL_PP_JOB_INV_STATUS_JSP');
463 FND_MSG_PUB.ADD;
464 CLOSE get_status_csr;
465 RAISE FND_API.G_EXC_ERROR;
466 ELSE
467 CLOSE get_status_csr;
468 END IF;
469 END IF;
470
471
472 IF p_x_workorder_rec.status_code IS NOT NULL AND p_x_workorder_rec.Status_Code <> '4' THEN
473 OPEN validate_status_csr(p_x_workorder_rec.status_code,p_curr_workorder_rec.status_code);
474 FETCH validate_status_csr INTO l_dummy_ctr;
475 IF validate_status_csr%NOTFOUND THEN
476 FND_MESSAGE.SET_NAME('AHL','AHL_UMP_INVALID_STTS_CHNG');
477 FND_MESSAGE.SET_TOKEN('FROM_STATUS',p_curr_workorder_rec.status_code);
478 FND_MESSAGE.SET_TOKEN('TO_STATUS', p_x_workorder_rec.status_code);
479 FND_MSG_PUB.ADD;
480 CLOSE validate_status_csr;
481 RAISE FND_API.G_EXC_ERROR;
482 ELSE
483 CLOSE validate_status_csr;
484 END IF;
485 END IF;
486
487
488 IF(p_x_workorder_rec.Status_Code IS NOT NULL AND p_x_workorder_rec.Status_Code = '3' AND p_curr_workorder_rec.Status_Code = '1')THEN
489 -- update work order
490 l_prd_workorder_rec.dml_operation := 'U';
491 l_prd_workorder_rec.BATCH_ID := p_x_workorder_rec.Batch_Id;
492 l_prd_workorder_rec.HEADER_ID := p_x_workorder_rec.Header_Id;
493 l_prd_workorder_rec.WORKORDER_ID := p_curr_workorder_rec.Workorder_Id;
494 l_prd_workorder_rec.WIP_ENTITY_ID := p_x_workorder_rec.Wip_Entity_Id;
495 l_prd_workorder_rec.OBJECT_VERSION_NUMBER := p_curr_workorder_rec.Object_Version_Number;
496 l_prd_workorder_rec.JOB_NUMBER := p_x_workorder_rec.Workorder_Number;
497 l_prd_workorder_rec.JOB_DESCRIPTION := p_x_workorder_rec.Description;
498 l_prd_workorder_rec.ORGANIZATION_ID := p_x_workorder_rec.Organization_Id;
499 l_prd_workorder_rec.ORGANIZATION_NAME := p_x_workorder_rec.Organization_Name;
500 l_prd_workorder_rec.ORGANIZATION_CODE := p_x_workorder_rec.Organization_Code;
501 l_prd_workorder_rec.DEPARTMENT_NAME := p_x_workorder_rec.Department_Name;
502 l_prd_workorder_rec.DEPARTMENT_ID := p_x_workorder_rec.Department_Id;
503 l_prd_workorder_rec.DEPARTMENT_CLASS_CODE := p_x_workorder_rec.Department_Class_Code;
504 l_prd_workorder_rec.STATUS_CODE := p_curr_workorder_rec.Status_Code;
505 l_prd_workorder_rec.STATUS_MEANING := NULL;
506 l_prd_workorder_rec.HOLD_REASON_CODE := FND_API.G_MISS_CHAR;
507 l_prd_workorder_rec.HOLD_REASON := FND_API.G_MISS_CHAR;
508 l_prd_workorder_rec.SCHEDULED_START_DATE := p_x_workorder_rec.Scheduled_Start_Date;
509 l_prd_workorder_rec.SCHEDULED_START_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'hh24');
510 l_prd_workorder_rec.SCHEDULED_START_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'mi');
511 l_prd_workorder_rec.SCHEDULED_END_DATE := p_x_workorder_rec.Scheduled_End_Date;
512 l_prd_workorder_rec.SCHEDULED_END_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'hh24');
513 l_prd_workorder_rec.SCHEDULED_END_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'mi');
514 l_prd_workorder_rec.ACTUAL_START_DATE := p_x_workorder_rec.Actual_Start_Date;
515 l_prd_workorder_rec.ACTUAL_START_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'hh24');
516 l_prd_workorder_rec.ACTUAL_START_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'mi');
517 l_prd_workorder_rec.ACTUAL_END_DATE := p_x_workorder_rec.Actual_End_Date;
518 l_prd_workorder_rec.ACTUAL_END_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'hh24');
519 l_prd_workorder_rec.ACTUAL_END_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'mi');
520 l_prd_workorder_rec.COMPLETION_SUBINVENTORY := p_x_workorder_rec.Completion_Subinventory;
521 l_prd_workorder_rec.COMPLETION_LOCATOR_ID := p_x_workorder_rec.Completion_Locator_Id;
522 l_prd_workorder_rec.COMPLETION_LOCATOR_NAME := p_x_workorder_rec.Completion_Locator_Name;
523 l_prd_workorder_rec.WIP_SUPPLY_TYPE := p_x_workorder_rec.Wip_Supply_Type;
524 l_prd_workorder_rec.WIP_SUPPLY_MEANING := p_x_workorder_rec.Wip_Supply_Meaning;
525 l_prd_workorder_rec.FIRM_PLANNED_FLAG := p_x_workorder_rec.Firm_Plan_Flag;
526 l_prd_workorder_rec.CONFIRM_FAILURE_FLAG := p_x_workorder_rec.Confirm_Failure_Flag;
527 l_prd_workorder_rec.CLASS_CODE := p_x_workorder_rec.Wip_Accounting_Class_Code;
528 l_prd_workorder_rec.JOB_PRIORITY := p_x_workorder_rec.Priority_Code;
529 l_prd_workorder_rec.JOB_PRIORITY_MEANING := p_x_workorder_rec.Priority;
530 l_prd_workorder_rec.SUB_INVENTORY := p_x_workorder_rec.Sub_Inventory;
531 l_prd_workorder_rec.LOCATOR_ID := p_x_workorder_rec.Locator_Id;
532 l_prd_workorder_rec.ATTRIBUTE_CATEGORY := p_x_workorder_rec.ATTRIBUTE_CATEGORY;
533 l_prd_workorder_rec.ATTRIBUTE1 := p_x_workorder_rec.ATTRIBUTE1;
534 l_prd_workorder_rec.ATTRIBUTE2 := p_x_workorder_rec.ATTRIBUTE2;
535 l_prd_workorder_rec.ATTRIBUTE3 := p_x_workorder_rec.ATTRIBUTE3;
536 l_prd_workorder_rec.ATTRIBUTE4 := p_x_workorder_rec.ATTRIBUTE4;
537 l_prd_workorder_rec.ATTRIBUTE5 := p_x_workorder_rec.ATTRIBUTE5;
538 l_prd_workorder_rec.ATTRIBUTE6 := p_x_workorder_rec.ATTRIBUTE6;
539 l_prd_workorder_rec.ATTRIBUTE7 := p_x_workorder_rec.ATTRIBUTE7;
540 l_prd_workorder_rec.ATTRIBUTE8 := p_x_workorder_rec.ATTRIBUTE8;
541 l_prd_workorder_rec.ATTRIBUTE9 := p_x_workorder_rec.ATTRIBUTE9;
542 l_prd_workorder_rec.ATTRIBUTE10 := p_x_workorder_rec.ATTRIBUTE10;
543 l_prd_workorder_rec.ATTRIBUTE11 := p_x_workorder_rec.ATTRIBUTE11;
544 l_prd_workorder_rec.ATTRIBUTE12 := p_x_workorder_rec.ATTRIBUTE12;
545 l_prd_workorder_rec.ATTRIBUTE13 := p_x_workorder_rec.ATTRIBUTE13;
546 l_prd_workorder_rec.ATTRIBUTE14 := p_x_workorder_rec.ATTRIBUTE14;
547 l_prd_workorder_rec.ATTRIBUTE15 := p_x_workorder_rec.ATTRIBUTE15;
548 l_prd_workorder_rec.LAST_UPDATE_DATE := p_x_workorder_rec.LAST_UPDATED_DATE;
549 l_prd_workorder_rec.LAST_UPDATED_BY := p_x_workorder_rec.LAST_UPDATED_BY;
550 l_prd_workorder_rec.CREATION_DATE := p_x_workorder_rec.CREATION_DATE;
551 l_prd_workorder_rec.CREATED_BY := p_x_workorder_rec.CREATED_BY;
552 l_prd_workorder_rec.LAST_UPDATE_LOGIN := p_x_workorder_rec.LAST_UPDATE_LOGIN;
553
554 l_prd_workorder_rec.INVENTORY_ITEM_ID := p_x_workorder_rec.INVENTORY_ITEM_ID;
555 l_prd_workorder_rec.ITEM_INSTANCE_ID := p_x_workorder_rec.ITEM_INSTANCE_ID;
556 l_prd_workorder_rec.PROPAGATE_FLAG := p_x_workorder_rec.PROPAGATE_FLAG;
557 l_prd_workorder_rec.SERVICE_ITEM_ID := p_x_workorder_rec.SERVICE_ITEM_ID;
558 l_prd_workorder_rec.SERVICE_ITEM_ORG_ID := p_x_workorder_rec.SERVICE_ITEM_ORG_ID;
559 l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION := p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION;
560 l_prd_workorder_rec.SERVICE_ITEM_NUMBER := p_x_workorder_rec.SERVICE_ITEM_NUMBER;
561 l_prd_workorder_rec.QUANTITY := p_x_workorder_rec.QUANTITY;
562 l_prd_workorder_rec.MRP_QUANTITY := p_x_workorder_rec.MRP_QUANTITY;
563 l_prd_workorder_rec.PARENT_ID := p_x_workorder_rec.PARENT_ID;
564 l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID := p_x_workorder_rec.TASK_MOTIVE_STATUS_ID;
565 l_prd_workorder_rec.ALLOW_EXPLOSION := p_x_workorder_rec.ALLOW_EXPLOSION;
566
567 AHL_PRD_WORKORDER_PVT.update_job
568 (
569 p_api_version => 1.0 ,
570 p_init_msg_list => FND_API.G_TRUE,
571 p_commit => FND_API.G_FALSE,
572 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
573 p_default => FND_API.G_TRUE,
574 p_module_type => 'API',
575 x_return_status => x_return_status,
576 x_msg_count => x_msg_count,
577 x_msg_data => x_msg_data,
578 p_wip_load_flag => 'Y',
579 p_x_prd_workorder_rec => l_prd_workorder_rec,
580 p_x_prd_workoper_tbl => l_prd_workoper_tbl
581 );
582 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
583 RAISE FND_API.G_EXC_ERROR;
584 END IF;
585
586 p_x_workorder_rec.Batch_Id := l_prd_workorder_rec.BATCH_ID;
587 p_x_workorder_rec.Header_Id := l_prd_workorder_rec.HEADER_ID;
588 p_x_workorder_rec.Workorder_Id := l_prd_workorder_rec.WORKORDER_ID;
589 p_x_workorder_rec.Wip_Entity_Id := l_prd_workorder_rec.WIP_ENTITY_ID;
590 p_x_workorder_rec.Object_Version_Number := l_prd_workorder_rec.OBJECT_VERSION_NUMBER;
591 p_x_workorder_rec.Workorder_Number := l_prd_workorder_rec.JOB_NUMBER;
592 p_x_workorder_rec.Description := l_prd_workorder_rec.JOB_DESCRIPTION;
593 p_x_workorder_rec.Organization_Id := l_prd_workorder_rec.ORGANIZATION_ID;
594 p_x_workorder_rec.Organization_Name := l_prd_workorder_rec.ORGANIZATION_NAME;
595 p_x_workorder_rec.Organization_Code := l_prd_workorder_rec.ORGANIZATION_CODE;
596 p_x_workorder_rec.Department_Name := l_prd_workorder_rec.DEPARTMENT_NAME;
597 p_x_workorder_rec.Department_Id := l_prd_workorder_rec.DEPARTMENT_ID;
598 p_x_workorder_rec.Department_Class_Code := l_prd_workorder_rec.DEPARTMENT_CLASS_CODE;
599 p_x_workorder_rec.Status_Code := l_prd_workorder_rec.STATUS_CODE ;
600 p_x_workorder_rec.Status := l_prd_workorder_rec.STATUS_MEANING;
601 p_x_workorder_rec.Hold_Reason_Code := l_prd_workorder_rec.HOLD_REASON_CODE;
602 p_x_workorder_rec.Hold_Reason := l_prd_workorder_rec.HOLD_REASON;
603 p_x_workorder_rec.Scheduled_Start_Date := l_prd_workorder_rec.SCHEDULED_START_DATE;
604 p_x_workorder_rec.Scheduled_End_Date := l_prd_workorder_rec.SCHEDULED_END_DATE;
605 p_x_workorder_rec.Actual_Start_Date := l_prd_workorder_rec.ACTUAL_START_DATE;
606 p_x_workorder_rec.Actual_End_Date := l_prd_workorder_rec.ACTUAL_END_DATE ;
607 p_x_workorder_rec.Completion_Subinventory := l_prd_workorder_rec.COMPLETION_SUBINVENTORY;
608 p_x_workorder_rec.Completion_Locator_Id := l_prd_workorder_rec.COMPLETION_LOCATOR_ID;
609 p_x_workorder_rec.Completion_Locator_Name := l_prd_workorder_rec.COMPLETION_LOCATOR_NAME;
610 p_x_workorder_rec.Wip_Supply_Type := l_prd_workorder_rec.WIP_SUPPLY_TYPE;
611 p_x_workorder_rec.Wip_Supply_Meaning := l_prd_workorder_rec.WIP_SUPPLY_MEANING;
612 p_x_workorder_rec.Firm_Plan_Flag := l_prd_workorder_rec.FIRM_PLANNED_FLAG;
613 p_x_workorder_rec.Confirm_Failure_Flag := l_prd_workorder_rec.CONFIRM_FAILURE_FLAG;
614 p_x_workorder_rec.Wip_Accounting_Class_Code := l_prd_workorder_rec.CLASS_CODE;
615 p_x_workorder_rec.Priority_Code := l_prd_workorder_rec.JOB_PRIORITY;
616 p_x_workorder_rec.Priority := l_prd_workorder_rec.JOB_PRIORITY_MEANING;
617 p_x_workorder_rec.Sub_Inventory := l_prd_workorder_rec.SUB_INVENTORY;
618 p_x_workorder_rec.Locator_Id := l_prd_workorder_rec.LOCATOR_ID;
619 p_x_workorder_rec.ATTRIBUTE_CATEGORY := l_prd_workorder_rec.ATTRIBUTE_CATEGORY;
620 p_x_workorder_rec.ATTRIBUTE1 := l_prd_workorder_rec.ATTRIBUTE1;
621 p_x_workorder_rec.ATTRIBUTE2 := l_prd_workorder_rec.ATTRIBUTE2;
622 p_x_workorder_rec.ATTRIBUTE3 := l_prd_workorder_rec.ATTRIBUTE3;
623 p_x_workorder_rec.ATTRIBUTE4 := l_prd_workorder_rec.ATTRIBUTE4;
624 p_x_workorder_rec.ATTRIBUTE5 := l_prd_workorder_rec.ATTRIBUTE5;
625 p_x_workorder_rec.ATTRIBUTE6 := l_prd_workorder_rec.ATTRIBUTE6;
626 p_x_workorder_rec.ATTRIBUTE7 := l_prd_workorder_rec.ATTRIBUTE7;
627 p_x_workorder_rec.ATTRIBUTE8 := l_prd_workorder_rec.ATTRIBUTE8;
628 p_x_workorder_rec.ATTRIBUTE9 := l_prd_workorder_rec.ATTRIBUTE9;
629 p_x_workorder_rec.ATTRIBUTE10 := l_prd_workorder_rec.ATTRIBUTE10;
630 p_x_workorder_rec.ATTRIBUTE11 := l_prd_workorder_rec.ATTRIBUTE11;
631 p_x_workorder_rec.ATTRIBUTE12 := l_prd_workorder_rec.ATTRIBUTE12;
632 p_x_workorder_rec.ATTRIBUTE13 := l_prd_workorder_rec.ATTRIBUTE13;
633 p_x_workorder_rec.ATTRIBUTE14 := l_prd_workorder_rec.ATTRIBUTE14;
634 p_x_workorder_rec.ATTRIBUTE15 := l_prd_workorder_rec.ATTRIBUTE15;
635 p_x_workorder_rec.LAST_UPDATED_DATE := l_prd_workorder_rec.LAST_UPDATE_DATE;
636 p_x_workorder_rec.LAST_UPDATED_BY := l_prd_workorder_rec.LAST_UPDATED_BY;
637 p_x_workorder_rec.CREATION_DATE := l_prd_workorder_rec.CREATION_DATE;
638 p_x_workorder_rec.CREATED_BY := l_prd_workorder_rec.CREATED_BY;
639 p_x_workorder_rec.LAST_UPDATE_LOGIN := l_prd_workorder_rec.LAST_UPDATE_LOGIN;
640
641 p_x_workorder_rec.INVENTORY_ITEM_ID := l_prd_workorder_rec.INVENTORY_ITEM_ID;
642 p_x_workorder_rec.ITEM_INSTANCE_ID := l_prd_workorder_rec.ITEM_INSTANCE_ID;
643 p_x_workorder_rec.PROPAGATE_FLAG := l_prd_workorder_rec.PROPAGATE_FLAG;
644 p_x_workorder_rec.SERVICE_ITEM_ID := l_prd_workorder_rec.SERVICE_ITEM_ID;
645 p_x_workorder_rec.SERVICE_ITEM_ORG_ID := l_prd_workorder_rec.SERVICE_ITEM_ORG_ID;
646 p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION := l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION;
647 p_x_workorder_rec.SERVICE_ITEM_NUMBER := l_prd_workorder_rec.SERVICE_ITEM_NUMBER;
648 p_x_workorder_rec.QUANTITY := l_prd_workorder_rec.QUANTITY;
649 p_x_workorder_rec.MRP_QUANTITY := l_prd_workorder_rec.MRP_QUANTITY;
650 p_x_workorder_rec.PARENT_ID := l_prd_workorder_rec.PARENT_ID;
651 p_x_workorder_rec.TASK_MOTIVE_STATUS_ID := l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID;
652 p_x_workorder_rec.ALLOW_EXPLOSION := l_prd_workorder_rec.ALLOW_EXPLOSION;
653 --release Job
654 AHL_PRD_WORKORDER_PVT.release_visit_jobs
655 (
656 p_api_version => 1.0 ,
657 p_init_msg_list => FND_API.G_TRUE,
658 p_commit => FND_API.G_FALSE,
659 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
660 p_default => FND_API.G_TRUE,
661 p_module_type => 'API',
662 x_return_status => x_return_status,
663 x_msg_count => x_msg_count,
664 x_msg_data => x_msg_data,
665 p_visit_id => NULL,
666 p_unit_effectivity_id => NULL,
667 p_workorder_id => p_curr_workorder_rec.Workorder_Id
668 );
669 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
670 RAISE FND_API.G_EXC_ERROR;
671 END IF;
672 ELSIF(p_x_workorder_rec.Status_Code IS NOT NULL AND p_x_workorder_rec.Status_Code = '7' AND p_curr_workorder_rec.Status_Code <> '7')THEN
673 -- update work order
674 l_prd_workorder_rec.dml_operation := 'U';
675 l_prd_workorder_rec.BATCH_ID := p_x_workorder_rec.Batch_Id;
676 l_prd_workorder_rec.HEADER_ID := p_x_workorder_rec.Header_Id;
677 l_prd_workorder_rec.WORKORDER_ID := p_curr_workorder_rec.Workorder_Id;
678 l_prd_workorder_rec.WIP_ENTITY_ID := p_x_workorder_rec.Wip_Entity_Id;
679 l_prd_workorder_rec.OBJECT_VERSION_NUMBER := p_curr_workorder_rec.Object_Version_Number;
680 l_prd_workorder_rec.JOB_NUMBER := p_x_workorder_rec.Workorder_Number;
681 l_prd_workorder_rec.JOB_DESCRIPTION := p_x_workorder_rec.Description;
682 l_prd_workorder_rec.ORGANIZATION_ID := p_x_workorder_rec.Organization_Id;
683 l_prd_workorder_rec.ORGANIZATION_NAME := p_x_workorder_rec.Organization_Name;
684 l_prd_workorder_rec.ORGANIZATION_CODE := p_x_workorder_rec.Organization_Code;
685 l_prd_workorder_rec.DEPARTMENT_NAME := p_x_workorder_rec.Department_Name;
686 l_prd_workorder_rec.DEPARTMENT_ID := p_x_workorder_rec.Department_Id;
687 l_prd_workorder_rec.DEPARTMENT_CLASS_CODE := p_x_workorder_rec.Department_Class_Code;
688 l_prd_workorder_rec.STATUS_CODE := p_curr_workorder_rec.Status_Code;
689 l_prd_workorder_rec.STATUS_MEANING := NULL;
690 l_prd_workorder_rec.HOLD_REASON_CODE := FND_API.G_MISS_CHAR;
691 l_prd_workorder_rec.HOLD_REASON := FND_API.G_MISS_CHAR;
692 l_prd_workorder_rec.SCHEDULED_START_DATE := p_x_workorder_rec.Scheduled_Start_Date;
693 l_prd_workorder_rec.SCHEDULED_START_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'hh24');
694 l_prd_workorder_rec.SCHEDULED_START_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'mi');
695 l_prd_workorder_rec.SCHEDULED_END_DATE := p_x_workorder_rec.Scheduled_End_Date;
696 l_prd_workorder_rec.SCHEDULED_END_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'hh24');
697 l_prd_workorder_rec.SCHEDULED_END_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'mi');
698 l_prd_workorder_rec.ACTUAL_START_DATE := p_x_workorder_rec.Actual_Start_Date;
699 l_prd_workorder_rec.ACTUAL_START_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'hh24');
700 l_prd_workorder_rec.ACTUAL_START_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'mi');
701 l_prd_workorder_rec.ACTUAL_END_DATE := p_x_workorder_rec.Actual_End_Date;
702 l_prd_workorder_rec.ACTUAL_END_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'hh24');
703 l_prd_workorder_rec.ACTUAL_END_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'mi');
704 l_prd_workorder_rec.COMPLETION_SUBINVENTORY := p_x_workorder_rec.Completion_Subinventory;
705 l_prd_workorder_rec.COMPLETION_LOCATOR_ID := p_x_workorder_rec.Completion_Locator_Id;
706 l_prd_workorder_rec.COMPLETION_LOCATOR_NAME := p_x_workorder_rec.Completion_Locator_Name;
707 l_prd_workorder_rec.WIP_SUPPLY_TYPE := p_x_workorder_rec.Wip_Supply_Type;
708 l_prd_workorder_rec.WIP_SUPPLY_MEANING := p_x_workorder_rec.Wip_Supply_Meaning;
709 l_prd_workorder_rec.FIRM_PLANNED_FLAG := p_x_workorder_rec.Firm_Plan_Flag;
710 l_prd_workorder_rec.CONFIRM_FAILURE_FLAG := p_x_workorder_rec.Confirm_Failure_Flag;
711 l_prd_workorder_rec.CLASS_CODE := p_x_workorder_rec.Wip_Accounting_Class_Code;
712 l_prd_workorder_rec.JOB_PRIORITY := p_x_workorder_rec.Priority_Code;
713 l_prd_workorder_rec.JOB_PRIORITY_MEANING := p_x_workorder_rec.Priority;
714 l_prd_workorder_rec.SUB_INVENTORY := p_x_workorder_rec.Sub_Inventory;
715 l_prd_workorder_rec.LOCATOR_ID := p_x_workorder_rec.Locator_Id;
716 l_prd_workorder_rec.ATTRIBUTE_CATEGORY := p_x_workorder_rec.ATTRIBUTE_CATEGORY;
717 l_prd_workorder_rec.ATTRIBUTE1 := p_x_workorder_rec.ATTRIBUTE1;
718 l_prd_workorder_rec.ATTRIBUTE2 := p_x_workorder_rec.ATTRIBUTE2;
719 l_prd_workorder_rec.ATTRIBUTE3 := p_x_workorder_rec.ATTRIBUTE3;
720 l_prd_workorder_rec.ATTRIBUTE4 := p_x_workorder_rec.ATTRIBUTE4;
721 l_prd_workorder_rec.ATTRIBUTE5 := p_x_workorder_rec.ATTRIBUTE5;
722 l_prd_workorder_rec.ATTRIBUTE6 := p_x_workorder_rec.ATTRIBUTE6;
723 l_prd_workorder_rec.ATTRIBUTE7 := p_x_workorder_rec.ATTRIBUTE7;
724 l_prd_workorder_rec.ATTRIBUTE8 := p_x_workorder_rec.ATTRIBUTE8;
725 l_prd_workorder_rec.ATTRIBUTE9 := p_x_workorder_rec.ATTRIBUTE9;
726 l_prd_workorder_rec.ATTRIBUTE10 := p_x_workorder_rec.ATTRIBUTE10;
727 l_prd_workorder_rec.ATTRIBUTE11 := p_x_workorder_rec.ATTRIBUTE11;
728 l_prd_workorder_rec.ATTRIBUTE12 := p_x_workorder_rec.ATTRIBUTE12;
729 l_prd_workorder_rec.ATTRIBUTE13 := p_x_workorder_rec.ATTRIBUTE13;
730 l_prd_workorder_rec.ATTRIBUTE14 := p_x_workorder_rec.ATTRIBUTE14;
731 l_prd_workorder_rec.ATTRIBUTE15 := p_x_workorder_rec.ATTRIBUTE15;
732 l_prd_workorder_rec.LAST_UPDATE_DATE := p_x_workorder_rec.LAST_UPDATED_DATE;
733 l_prd_workorder_rec.LAST_UPDATED_BY := p_x_workorder_rec.LAST_UPDATED_BY;
734 l_prd_workorder_rec.CREATION_DATE := p_x_workorder_rec.CREATION_DATE;
735 l_prd_workorder_rec.CREATED_BY := p_x_workorder_rec.CREATED_BY;
736 l_prd_workorder_rec.LAST_UPDATE_LOGIN := p_x_workorder_rec.LAST_UPDATE_LOGIN;
737
738 l_prd_workorder_rec.INVENTORY_ITEM_ID := p_x_workorder_rec.INVENTORY_ITEM_ID;
739 l_prd_workorder_rec.ITEM_INSTANCE_ID := p_x_workorder_rec.ITEM_INSTANCE_ID;
740 l_prd_workorder_rec.PROPAGATE_FLAG := p_x_workorder_rec.PROPAGATE_FLAG;
741 l_prd_workorder_rec.SERVICE_ITEM_ID := p_x_workorder_rec.SERVICE_ITEM_ID;
742 l_prd_workorder_rec.SERVICE_ITEM_ORG_ID := p_x_workorder_rec.SERVICE_ITEM_ORG_ID;
743 l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION := p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION;
744 l_prd_workorder_rec.SERVICE_ITEM_NUMBER := p_x_workorder_rec.SERVICE_ITEM_NUMBER;
745 l_prd_workorder_rec.QUANTITY := p_x_workorder_rec.QUANTITY;
746 l_prd_workorder_rec.MRP_QUANTITY := p_x_workorder_rec.MRP_QUANTITY;
747 l_prd_workorder_rec.PARENT_ID := p_x_workorder_rec.PARENT_ID;
748 l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID := p_x_workorder_rec.TASK_MOTIVE_STATUS_ID;
749 l_prd_workorder_rec.ALLOW_EXPLOSION := p_x_workorder_rec.ALLOW_EXPLOSION;
750
751 AHL_PRD_WORKORDER_PVT.update_job
752 (
753 p_api_version => 1.0 ,
754 p_init_msg_list => FND_API.G_TRUE,
755 p_commit => FND_API.G_FALSE,
756 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
757 p_default => FND_API.G_TRUE,
758 p_module_type => 'API',
759 x_return_status => x_return_status,
760 x_msg_count => x_msg_count,
761 x_msg_data => x_msg_data,
762 p_wip_load_flag => 'Y',
763 p_x_prd_workorder_rec => l_prd_workorder_rec,
764 p_x_prd_workoper_tbl => l_prd_workoper_tbl
765 );
766 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
767 RAISE FND_API.G_EXC_ERROR;
768 END IF;
769
770 p_x_workorder_rec.Batch_Id := l_prd_workorder_rec.BATCH_ID;
771 p_x_workorder_rec.Header_Id := l_prd_workorder_rec.HEADER_ID;
772 p_x_workorder_rec.Workorder_Id := l_prd_workorder_rec.WORKORDER_ID;
773 p_x_workorder_rec.Wip_Entity_Id := l_prd_workorder_rec.WIP_ENTITY_ID;
774 p_x_workorder_rec.Object_Version_Number := l_prd_workorder_rec.OBJECT_VERSION_NUMBER;
775 p_x_workorder_rec.Workorder_Number := l_prd_workorder_rec.JOB_NUMBER;
776 p_x_workorder_rec.Description := l_prd_workorder_rec.JOB_DESCRIPTION;
777 p_x_workorder_rec.Organization_Id := l_prd_workorder_rec.ORGANIZATION_ID;
778 p_x_workorder_rec.Organization_Name := l_prd_workorder_rec.ORGANIZATION_NAME;
779 p_x_workorder_rec.Organization_Code := l_prd_workorder_rec.ORGANIZATION_CODE;
780 p_x_workorder_rec.Department_Name := l_prd_workorder_rec.DEPARTMENT_NAME;
781 p_x_workorder_rec.Department_Id := l_prd_workorder_rec.DEPARTMENT_ID;
782 p_x_workorder_rec.Department_Class_Code := l_prd_workorder_rec.DEPARTMENT_CLASS_CODE;
783 p_x_workorder_rec.Status_Code := l_prd_workorder_rec.STATUS_CODE ;
784 p_x_workorder_rec.Status := l_prd_workorder_rec.STATUS_MEANING;
785 p_x_workorder_rec.Hold_Reason_Code := l_prd_workorder_rec.HOLD_REASON_CODE;
786 p_x_workorder_rec.Hold_Reason := l_prd_workorder_rec.HOLD_REASON;
787 p_x_workorder_rec.Scheduled_Start_Date := l_prd_workorder_rec.SCHEDULED_START_DATE;
788 p_x_workorder_rec.Scheduled_End_Date := l_prd_workorder_rec.SCHEDULED_END_DATE;
789 p_x_workorder_rec.Actual_Start_Date := l_prd_workorder_rec.ACTUAL_START_DATE;
790 p_x_workorder_rec.Actual_End_Date := l_prd_workorder_rec.ACTUAL_END_DATE ;
791 p_x_workorder_rec.Completion_Subinventory := l_prd_workorder_rec.COMPLETION_SUBINVENTORY;
792 p_x_workorder_rec.Completion_Locator_Id := l_prd_workorder_rec.COMPLETION_LOCATOR_ID;
793 p_x_workorder_rec.Completion_Locator_Name := l_prd_workorder_rec.COMPLETION_LOCATOR_NAME;
794 p_x_workorder_rec.Wip_Supply_Type := l_prd_workorder_rec.WIP_SUPPLY_TYPE;
795 p_x_workorder_rec.Wip_Supply_Meaning := l_prd_workorder_rec.WIP_SUPPLY_MEANING;
796 p_x_workorder_rec.Firm_Plan_Flag := l_prd_workorder_rec.FIRM_PLANNED_FLAG;
797 p_x_workorder_rec.Confirm_Failure_Flag := l_prd_workorder_rec.CONFIRM_FAILURE_FLAG;
798 p_x_workorder_rec.Wip_Accounting_Class_Code := l_prd_workorder_rec.CLASS_CODE;
799 p_x_workorder_rec.Priority_Code := l_prd_workorder_rec.JOB_PRIORITY;
800 p_x_workorder_rec.Priority := l_prd_workorder_rec.JOB_PRIORITY_MEANING;
801 p_x_workorder_rec.Sub_Inventory := l_prd_workorder_rec.SUB_INVENTORY;
802 p_x_workorder_rec.Locator_Id := l_prd_workorder_rec.LOCATOR_ID;
803 p_x_workorder_rec.ATTRIBUTE_CATEGORY := l_prd_workorder_rec.ATTRIBUTE_CATEGORY;
804 p_x_workorder_rec.ATTRIBUTE1 := l_prd_workorder_rec.ATTRIBUTE1;
805 p_x_workorder_rec.ATTRIBUTE2 := l_prd_workorder_rec.ATTRIBUTE2;
806 p_x_workorder_rec.ATTRIBUTE3 := l_prd_workorder_rec.ATTRIBUTE3;
807 p_x_workorder_rec.ATTRIBUTE4 := l_prd_workorder_rec.ATTRIBUTE4;
808 p_x_workorder_rec.ATTRIBUTE5 := l_prd_workorder_rec.ATTRIBUTE5;
809 p_x_workorder_rec.ATTRIBUTE6 := l_prd_workorder_rec.ATTRIBUTE6;
810 p_x_workorder_rec.ATTRIBUTE7 := l_prd_workorder_rec.ATTRIBUTE7;
811 p_x_workorder_rec.ATTRIBUTE8 := l_prd_workorder_rec.ATTRIBUTE8;
812 p_x_workorder_rec.ATTRIBUTE9 := l_prd_workorder_rec.ATTRIBUTE9;
813 p_x_workorder_rec.ATTRIBUTE10 := l_prd_workorder_rec.ATTRIBUTE10;
814 p_x_workorder_rec.ATTRIBUTE11 := l_prd_workorder_rec.ATTRIBUTE11;
815 p_x_workorder_rec.ATTRIBUTE12 := l_prd_workorder_rec.ATTRIBUTE12;
816 p_x_workorder_rec.ATTRIBUTE13 := l_prd_workorder_rec.ATTRIBUTE13;
817 p_x_workorder_rec.ATTRIBUTE14 := l_prd_workorder_rec.ATTRIBUTE14;
818 p_x_workorder_rec.ATTRIBUTE15 := l_prd_workorder_rec.ATTRIBUTE15;
819 p_x_workorder_rec.LAST_UPDATED_DATE := l_prd_workorder_rec.LAST_UPDATE_DATE;
820 p_x_workorder_rec.LAST_UPDATED_BY := l_prd_workorder_rec.LAST_UPDATED_BY;
821 p_x_workorder_rec.CREATION_DATE := l_prd_workorder_rec.CREATION_DATE;
822 p_x_workorder_rec.CREATED_BY := l_prd_workorder_rec.CREATED_BY;
823 p_x_workorder_rec.LAST_UPDATE_LOGIN := l_prd_workorder_rec.LAST_UPDATE_LOGIN;
824
825 p_x_workorder_rec.INVENTORY_ITEM_ID := l_prd_workorder_rec.INVENTORY_ITEM_ID;
826 p_x_workorder_rec.ITEM_INSTANCE_ID := l_prd_workorder_rec.ITEM_INSTANCE_ID;
827 p_x_workorder_rec.PROPAGATE_FLAG := l_prd_workorder_rec.PROPAGATE_FLAG;
828 p_x_workorder_rec.SERVICE_ITEM_ID := l_prd_workorder_rec.SERVICE_ITEM_ID;
829 p_x_workorder_rec.SERVICE_ITEM_ORG_ID := l_prd_workorder_rec.SERVICE_ITEM_ORG_ID;
830 p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION := l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION;
831 p_x_workorder_rec.SERVICE_ITEM_NUMBER := l_prd_workorder_rec.SERVICE_ITEM_NUMBER;
832 p_x_workorder_rec.QUANTITY := l_prd_workorder_rec.QUANTITY;
833 p_x_workorder_rec.MRP_QUANTITY := l_prd_workorder_rec.MRP_QUANTITY;
834 p_x_workorder_rec.PARENT_ID := l_prd_workorder_rec.PARENT_ID;
835 p_x_workorder_rec.TASK_MOTIVE_STATUS_ID := l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID;
836 p_x_workorder_rec.ALLOW_EXPLOSION := l_prd_workorder_rec.ALLOW_EXPLOSION;
837 --cancel job
838 AHL_PRD_WORKORDER_PVT.cancel_visit_jobs
839 (
840 p_api_version => 1.0 ,
841 p_init_msg_list => FND_API.G_TRUE,
842 p_commit => FND_API.G_FALSE,
843 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
844 p_default => FND_API.G_TRUE,
845 p_module_type => 'API',
846 x_return_status => x_return_status,
847 x_msg_count => x_msg_count,
848 x_msg_data => x_msg_data,
849 p_visit_id => NULL,
850 p_unit_effectivity_id => NULL,
851 p_workorder_id => p_curr_workorder_rec.Workorder_Id
852 );
853 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
854 RAISE FND_API.G_EXC_ERROR;
855 END IF;
856 ELSIF(p_x_workorder_rec.Status_Code IS NOT NULL AND p_x_workorder_rec.Status_Code = '4' AND p_curr_workorder_rec.Status_Code <> 4)THEN
857
858 -- update work order
859 l_prd_workorder_rec.dml_operation := 'U';
860 l_prd_workorder_rec.BATCH_ID := p_x_workorder_rec.Batch_Id;
861 l_prd_workorder_rec.HEADER_ID := p_x_workorder_rec.Header_Id;
862 l_prd_workorder_rec.WORKORDER_ID := p_curr_workorder_rec.Workorder_Id;
863 l_prd_workorder_rec.WIP_ENTITY_ID := p_x_workorder_rec.Wip_Entity_Id;
864 l_prd_workorder_rec.OBJECT_VERSION_NUMBER := p_curr_workorder_rec.Object_Version_Number;
865 l_prd_workorder_rec.JOB_NUMBER := p_x_workorder_rec.Workorder_Number;
866 l_prd_workorder_rec.JOB_DESCRIPTION := p_x_workorder_rec.Description;
867 l_prd_workorder_rec.ORGANIZATION_ID := p_x_workorder_rec.Organization_Id;
868 l_prd_workorder_rec.ORGANIZATION_NAME := p_x_workorder_rec.Organization_Name;
869 l_prd_workorder_rec.ORGANIZATION_CODE := p_x_workorder_rec.Organization_Code;
870 l_prd_workorder_rec.DEPARTMENT_NAME := p_x_workorder_rec.Department_Name;
871 l_prd_workorder_rec.DEPARTMENT_ID := p_x_workorder_rec.Department_Id;
872 l_prd_workorder_rec.DEPARTMENT_CLASS_CODE := p_x_workorder_rec.Department_Class_Code;
873 l_prd_workorder_rec.STATUS_CODE := p_curr_workorder_rec.Status_Code;
874 l_prd_workorder_rec.STATUS_MEANING := NULL;
875 l_prd_workorder_rec.HOLD_REASON_CODE := FND_API.G_MISS_CHAR;
876 l_prd_workorder_rec.HOLD_REASON := FND_API.G_MISS_CHAR;
877 l_prd_workorder_rec.SCHEDULED_START_DATE := p_x_workorder_rec.Scheduled_Start_Date;
878 l_prd_workorder_rec.SCHEDULED_START_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'hh24');
879 l_prd_workorder_rec.SCHEDULED_START_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'mi');
880 l_prd_workorder_rec.SCHEDULED_END_DATE := p_x_workorder_rec.Scheduled_End_Date;
881 l_prd_workorder_rec.SCHEDULED_END_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'hh24');
882 l_prd_workorder_rec.SCHEDULED_END_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'mi');
883 l_prd_workorder_rec.ACTUAL_START_DATE := p_x_workorder_rec.Actual_Start_Date;
884 l_prd_workorder_rec.ACTUAL_START_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'hh24');
885 l_prd_workorder_rec.ACTUAL_START_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'mi');
886 l_prd_workorder_rec.ACTUAL_END_DATE := p_x_workorder_rec.Actual_End_Date;
887 l_prd_workorder_rec.ACTUAL_END_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'hh24');
888 l_prd_workorder_rec.ACTUAL_END_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'mi');
889 l_prd_workorder_rec.COMPLETION_SUBINVENTORY := p_x_workorder_rec.Completion_Subinventory;
890 l_prd_workorder_rec.COMPLETION_LOCATOR_ID := p_x_workorder_rec.Completion_Locator_Id;
891 l_prd_workorder_rec.COMPLETION_LOCATOR_NAME := p_x_workorder_rec.Completion_Locator_Name;
892 l_prd_workorder_rec.WIP_SUPPLY_TYPE := p_x_workorder_rec.Wip_Supply_Type;
893 l_prd_workorder_rec.WIP_SUPPLY_MEANING := p_x_workorder_rec.Wip_Supply_Meaning;
894 l_prd_workorder_rec.FIRM_PLANNED_FLAG := p_x_workorder_rec.Firm_Plan_Flag;
895 l_prd_workorder_rec.CONFIRM_FAILURE_FLAG := p_x_workorder_rec.Confirm_Failure_Flag;
896 l_prd_workorder_rec.CLASS_CODE := p_x_workorder_rec.Wip_Accounting_Class_Code;
897 l_prd_workorder_rec.JOB_PRIORITY := p_x_workorder_rec.Priority_Code;
898 l_prd_workorder_rec.JOB_PRIORITY_MEANING := p_x_workorder_rec.Priority;
899 l_prd_workorder_rec.SUB_INVENTORY := p_x_workorder_rec.Sub_Inventory;
900 l_prd_workorder_rec.LOCATOR_ID := p_x_workorder_rec.Locator_Id;
901 l_prd_workorder_rec.ATTRIBUTE_CATEGORY := p_x_workorder_rec.ATTRIBUTE_CATEGORY;
902 l_prd_workorder_rec.ATTRIBUTE1 := p_x_workorder_rec.ATTRIBUTE1;
903 l_prd_workorder_rec.ATTRIBUTE2 := p_x_workorder_rec.ATTRIBUTE2;
904 l_prd_workorder_rec.ATTRIBUTE3 := p_x_workorder_rec.ATTRIBUTE3;
905 l_prd_workorder_rec.ATTRIBUTE4 := p_x_workorder_rec.ATTRIBUTE4;
906 l_prd_workorder_rec.ATTRIBUTE5 := p_x_workorder_rec.ATTRIBUTE5;
907 l_prd_workorder_rec.ATTRIBUTE6 := p_x_workorder_rec.ATTRIBUTE6;
908 l_prd_workorder_rec.ATTRIBUTE7 := p_x_workorder_rec.ATTRIBUTE7;
909 l_prd_workorder_rec.ATTRIBUTE8 := p_x_workorder_rec.ATTRIBUTE8;
910 l_prd_workorder_rec.ATTRIBUTE9 := p_x_workorder_rec.ATTRIBUTE9;
911 l_prd_workorder_rec.ATTRIBUTE10 := p_x_workorder_rec.ATTRIBUTE10;
912 l_prd_workorder_rec.ATTRIBUTE11 := p_x_workorder_rec.ATTRIBUTE11;
913 l_prd_workorder_rec.ATTRIBUTE12 := p_x_workorder_rec.ATTRIBUTE12;
914 l_prd_workorder_rec.ATTRIBUTE13 := p_x_workorder_rec.ATTRIBUTE13;
915 l_prd_workorder_rec.ATTRIBUTE14 := p_x_workorder_rec.ATTRIBUTE14;
916 l_prd_workorder_rec.ATTRIBUTE15 := p_x_workorder_rec.ATTRIBUTE15;
917 l_prd_workorder_rec.LAST_UPDATE_DATE := p_x_workorder_rec.LAST_UPDATED_DATE;
918 l_prd_workorder_rec.LAST_UPDATED_BY := p_x_workorder_rec.LAST_UPDATED_BY;
919 l_prd_workorder_rec.CREATION_DATE := p_x_workorder_rec.CREATION_DATE;
920 l_prd_workorder_rec.CREATED_BY := p_x_workorder_rec.CREATED_BY;
921 l_prd_workorder_rec.LAST_UPDATE_LOGIN := p_x_workorder_rec.LAST_UPDATE_LOGIN;
922
923 l_prd_workorder_rec.INVENTORY_ITEM_ID := p_x_workorder_rec.INVENTORY_ITEM_ID;
924 l_prd_workorder_rec.ITEM_INSTANCE_ID := p_x_workorder_rec.ITEM_INSTANCE_ID;
925 l_prd_workorder_rec.PROPAGATE_FLAG := p_x_workorder_rec.PROPAGATE_FLAG;
926 l_prd_workorder_rec.SERVICE_ITEM_ID := p_x_workorder_rec.SERVICE_ITEM_ID;
927 l_prd_workorder_rec.SERVICE_ITEM_ORG_ID := p_x_workorder_rec.SERVICE_ITEM_ORG_ID;
928 l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION := p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION;
929 l_prd_workorder_rec.SERVICE_ITEM_NUMBER := p_x_workorder_rec.SERVICE_ITEM_NUMBER;
930 l_prd_workorder_rec.QUANTITY := p_x_workorder_rec.QUANTITY;
931 l_prd_workorder_rec.MRP_QUANTITY := p_x_workorder_rec.MRP_QUANTITY;
932 l_prd_workorder_rec.PARENT_ID := p_x_workorder_rec.PARENT_ID;
933 l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID := p_x_workorder_rec.TASK_MOTIVE_STATUS_ID;
934 l_prd_workorder_rec.ALLOW_EXPLOSION := p_x_workorder_rec.ALLOW_EXPLOSION;
935
936 AHL_PRD_WORKORDER_PVT.update_job
937 (
938 p_api_version => 1.0 ,
939 p_init_msg_list => FND_API.G_TRUE,
940 p_commit => FND_API.G_FALSE,
941 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
942 p_default => FND_API.G_TRUE,
943 p_module_type => 'API',
944 x_return_status => x_return_status,
945 x_msg_count => x_msg_count,
946 x_msg_data => x_msg_data,
947 p_wip_load_flag => 'Y',
948 p_x_prd_workorder_rec => l_prd_workorder_rec,
949 p_x_prd_workoper_tbl => l_prd_workoper_tbl
950 );
951 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
952 RAISE FND_API.G_EXC_ERROR;
953 END IF;
954
955 p_x_workorder_rec.Batch_Id := l_prd_workorder_rec.BATCH_ID;
956 p_x_workorder_rec.Header_Id := l_prd_workorder_rec.HEADER_ID;
957 p_x_workorder_rec.Workorder_Id := l_prd_workorder_rec.WORKORDER_ID;
958 p_x_workorder_rec.Wip_Entity_Id := l_prd_workorder_rec.WIP_ENTITY_ID;
959 p_x_workorder_rec.Object_Version_Number := l_prd_workorder_rec.OBJECT_VERSION_NUMBER;
960 p_x_workorder_rec.Workorder_Number := l_prd_workorder_rec.JOB_NUMBER;
961 p_x_workorder_rec.Description := l_prd_workorder_rec.JOB_DESCRIPTION;
962 p_x_workorder_rec.Organization_Id := l_prd_workorder_rec.ORGANIZATION_ID;
963 p_x_workorder_rec.Organization_Name := l_prd_workorder_rec.ORGANIZATION_NAME;
964 p_x_workorder_rec.Organization_Code := l_prd_workorder_rec.ORGANIZATION_CODE;
965 p_x_workorder_rec.Department_Name := l_prd_workorder_rec.DEPARTMENT_NAME;
966 p_x_workorder_rec.Department_Id := l_prd_workorder_rec.DEPARTMENT_ID;
967 p_x_workorder_rec.Department_Class_Code := l_prd_workorder_rec.DEPARTMENT_CLASS_CODE;
968 p_x_workorder_rec.Status_Code := l_prd_workorder_rec.STATUS_CODE ;
969 p_x_workorder_rec.Status := l_prd_workorder_rec.STATUS_MEANING;
970 p_x_workorder_rec.Hold_Reason_Code := l_prd_workorder_rec.HOLD_REASON_CODE;
971 p_x_workorder_rec.Hold_Reason := l_prd_workorder_rec.HOLD_REASON;
972 p_x_workorder_rec.Scheduled_Start_Date := l_prd_workorder_rec.SCHEDULED_START_DATE;
973 p_x_workorder_rec.Scheduled_End_Date := l_prd_workorder_rec.SCHEDULED_END_DATE;
974 p_x_workorder_rec.Actual_Start_Date := l_prd_workorder_rec.ACTUAL_START_DATE;
975 p_x_workorder_rec.Actual_End_Date := l_prd_workorder_rec.ACTUAL_END_DATE ;
976 p_x_workorder_rec.Completion_Subinventory := l_prd_workorder_rec.COMPLETION_SUBINVENTORY;
977 p_x_workorder_rec.Completion_Locator_Id := l_prd_workorder_rec.COMPLETION_LOCATOR_ID;
978 p_x_workorder_rec.Completion_Locator_Name := l_prd_workorder_rec.COMPLETION_LOCATOR_NAME;
979 p_x_workorder_rec.Wip_Supply_Type := l_prd_workorder_rec.WIP_SUPPLY_TYPE;
980 p_x_workorder_rec.Wip_Supply_Meaning := l_prd_workorder_rec.WIP_SUPPLY_MEANING;
981 p_x_workorder_rec.Firm_Plan_Flag := l_prd_workorder_rec.FIRM_PLANNED_FLAG;
982 p_x_workorder_rec.Confirm_Failure_Flag := l_prd_workorder_rec.CONFIRM_FAILURE_FLAG;
983 p_x_workorder_rec.Wip_Accounting_Class_Code := l_prd_workorder_rec.CLASS_CODE;
984 p_x_workorder_rec.Priority_Code := l_prd_workorder_rec.JOB_PRIORITY;
985 p_x_workorder_rec.Priority := l_prd_workorder_rec.JOB_PRIORITY_MEANING;
986 p_x_workorder_rec.Sub_Inventory := l_prd_workorder_rec.SUB_INVENTORY;
987 p_x_workorder_rec.Locator_Id := l_prd_workorder_rec.LOCATOR_ID;
988 p_x_workorder_rec.ATTRIBUTE_CATEGORY := l_prd_workorder_rec.ATTRIBUTE_CATEGORY;
989 p_x_workorder_rec.ATTRIBUTE1 := l_prd_workorder_rec.ATTRIBUTE1;
990 p_x_workorder_rec.ATTRIBUTE2 := l_prd_workorder_rec.ATTRIBUTE2;
991 p_x_workorder_rec.ATTRIBUTE3 := l_prd_workorder_rec.ATTRIBUTE3;
992 p_x_workorder_rec.ATTRIBUTE4 := l_prd_workorder_rec.ATTRIBUTE4;
993 p_x_workorder_rec.ATTRIBUTE5 := l_prd_workorder_rec.ATTRIBUTE5;
994 p_x_workorder_rec.ATTRIBUTE6 := l_prd_workorder_rec.ATTRIBUTE6;
995 p_x_workorder_rec.ATTRIBUTE7 := l_prd_workorder_rec.ATTRIBUTE7;
996 p_x_workorder_rec.ATTRIBUTE8 := l_prd_workorder_rec.ATTRIBUTE8;
997 p_x_workorder_rec.ATTRIBUTE9 := l_prd_workorder_rec.ATTRIBUTE9;
998 p_x_workorder_rec.ATTRIBUTE10 := l_prd_workorder_rec.ATTRIBUTE10;
999 p_x_workorder_rec.ATTRIBUTE11 := l_prd_workorder_rec.ATTRIBUTE11;
1000 p_x_workorder_rec.ATTRIBUTE12 := l_prd_workorder_rec.ATTRIBUTE12;
1001 p_x_workorder_rec.ATTRIBUTE13 := l_prd_workorder_rec.ATTRIBUTE13;
1002 p_x_workorder_rec.ATTRIBUTE14 := l_prd_workorder_rec.ATTRIBUTE14;
1003 p_x_workorder_rec.ATTRIBUTE15 := l_prd_workorder_rec.ATTRIBUTE15;
1004 p_x_workorder_rec.LAST_UPDATED_DATE := l_prd_workorder_rec.LAST_UPDATE_DATE;
1005 p_x_workorder_rec.LAST_UPDATED_BY := l_prd_workorder_rec.LAST_UPDATED_BY;
1006 p_x_workorder_rec.CREATION_DATE := l_prd_workorder_rec.CREATION_DATE;
1007 p_x_workorder_rec.CREATED_BY := l_prd_workorder_rec.CREATED_BY;
1008 p_x_workorder_rec.LAST_UPDATE_LOGIN := l_prd_workorder_rec.LAST_UPDATE_LOGIN;
1009
1010 p_x_workorder_rec.INVENTORY_ITEM_ID := l_prd_workorder_rec.INVENTORY_ITEM_ID;
1011 p_x_workorder_rec.ITEM_INSTANCE_ID := l_prd_workorder_rec.ITEM_INSTANCE_ID;
1012 p_x_workorder_rec.PROPAGATE_FLAG := l_prd_workorder_rec.PROPAGATE_FLAG;
1013 p_x_workorder_rec.SERVICE_ITEM_ID := l_prd_workorder_rec.SERVICE_ITEM_ID;
1014 p_x_workorder_rec.SERVICE_ITEM_ORG_ID := l_prd_workorder_rec.SERVICE_ITEM_ORG_ID;
1015 p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION := l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION;
1016 p_x_workorder_rec.SERVICE_ITEM_NUMBER := l_prd_workorder_rec.SERVICE_ITEM_NUMBER;
1017 p_x_workorder_rec.QUANTITY := l_prd_workorder_rec.QUANTITY;
1018 p_x_workorder_rec.MRP_QUANTITY := l_prd_workorder_rec.MRP_QUANTITY;
1019 p_x_workorder_rec.PARENT_ID := l_prd_workorder_rec.PARENT_ID;
1020 p_x_workorder_rec.TASK_MOTIVE_STATUS_ID := l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID;
1021 p_x_workorder_rec.ALLOW_EXPLOSION := l_prd_workorder_rec.ALLOW_EXPLOSION;
1022 --
1023 -- complete job
1024 AHL_COMPLETIONS_PVT.complete_workorder
1025 (
1026 p_api_version => 1.0 ,
1027 p_init_msg_list => FND_API.G_TRUE,
1028 p_commit => FND_API.G_FALSE,
1029 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1030 p_default => FND_API.G_TRUE,
1031 p_module_type => 'API',
1032 x_return_status => x_return_status,
1033 x_msg_count => x_msg_count,
1034 x_msg_data => x_msg_data,
1035 p_workorder_id => p_x_workorder_rec.Workorder_Id,
1036 p_object_version_no => p_x_workorder_rec.Object_Version_Number
1037 );
1038 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1039 RAISE FND_API.G_EXC_ERROR;
1040 END IF;
1041 ELSE
1042 -- update work order
1043 l_prd_workorder_rec.dml_operation := 'U';
1044 l_prd_workorder_rec.BATCH_ID := p_x_workorder_rec.Batch_Id;
1045 l_prd_workorder_rec.HEADER_ID := p_x_workorder_rec.Header_Id;
1046 l_prd_workorder_rec.WORKORDER_ID := p_curr_workorder_rec.Workorder_Id;
1047 l_prd_workorder_rec.WIP_ENTITY_ID := p_x_workorder_rec.Wip_Entity_Id;
1048 l_prd_workorder_rec.OBJECT_VERSION_NUMBER := p_curr_workorder_rec.Object_Version_Number;
1049 l_prd_workorder_rec.JOB_NUMBER := p_x_workorder_rec.Workorder_Number;
1050 l_prd_workorder_rec.JOB_DESCRIPTION := p_x_workorder_rec.Description;
1051 l_prd_workorder_rec.ORGANIZATION_ID := p_x_workorder_rec.Organization_Id;
1052 l_prd_workorder_rec.ORGANIZATION_NAME := p_x_workorder_rec.Organization_Name;
1053 l_prd_workorder_rec.ORGANIZATION_CODE := p_x_workorder_rec.Organization_Code;
1054 l_prd_workorder_rec.DEPARTMENT_NAME := p_x_workorder_rec.Department_Name;
1055 l_prd_workorder_rec.DEPARTMENT_ID := p_x_workorder_rec.Department_Id;
1056 l_prd_workorder_rec.DEPARTMENT_CLASS_CODE := p_x_workorder_rec.Department_Class_Code;
1057 l_prd_workorder_rec.STATUS_CODE := p_x_workorder_rec.Status_Code;
1058 l_prd_workorder_rec.STATUS_MEANING := p_x_workorder_rec.Status;
1059 IF (p_x_workorder_rec.Status_Code IS NOT NULL AND p_x_workorder_rec.Status_Code IN ('6','19')) OR
1060 (p_x_workorder_rec.Status_Code IS NULL AND p_curr_workorder_rec.Status_Code IN ('6','19')) THEN
1061 l_prd_workorder_rec.HOLD_REASON_CODE := p_x_workorder_rec.HOLD_REASON_CODE;
1062 l_prd_workorder_rec.HOLD_REASON := p_x_workorder_rec.HOLD_REASON;
1063 ELSE
1064 l_prd_workorder_rec.HOLD_REASON_CODE := FND_API.G_MISS_CHAR;
1065 l_prd_workorder_rec.HOLD_REASON := FND_API.G_MISS_CHAR;
1066 END IF;
1067 l_prd_workorder_rec.SCHEDULED_START_DATE := p_x_workorder_rec.Scheduled_Start_Date;
1068 l_prd_workorder_rec.SCHEDULED_START_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'hh24');
1069 l_prd_workorder_rec.SCHEDULED_START_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'mi');
1070 l_prd_workorder_rec.SCHEDULED_END_DATE := p_x_workorder_rec.Scheduled_End_Date;
1071 l_prd_workorder_rec.SCHEDULED_END_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'hh24');
1072 l_prd_workorder_rec.SCHEDULED_END_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'mi');
1073 l_prd_workorder_rec.ACTUAL_START_DATE := p_x_workorder_rec.Actual_Start_Date;
1074 l_prd_workorder_rec.ACTUAL_START_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'hh24');
1075 l_prd_workorder_rec.ACTUAL_START_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'mi');
1076 l_prd_workorder_rec.ACTUAL_END_DATE := p_x_workorder_rec.Actual_End_Date;
1077 l_prd_workorder_rec.ACTUAL_END_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'hh24');
1078 l_prd_workorder_rec.ACTUAL_END_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'mi');
1079 l_prd_workorder_rec.COMPLETION_SUBINVENTORY := p_x_workorder_rec.Completion_Subinventory;
1080 l_prd_workorder_rec.COMPLETION_LOCATOR_ID := p_x_workorder_rec.Completion_Locator_Id;
1081 l_prd_workorder_rec.COMPLETION_LOCATOR_NAME := p_x_workorder_rec.Completion_Locator_Name;
1082 l_prd_workorder_rec.WIP_SUPPLY_TYPE := p_x_workorder_rec.Wip_Supply_Type;
1083 l_prd_workorder_rec.WIP_SUPPLY_MEANING := p_x_workorder_rec.Wip_Supply_Meaning;
1084 l_prd_workorder_rec.FIRM_PLANNED_FLAG := p_x_workorder_rec.Firm_Plan_Flag;
1085 l_prd_workorder_rec.CONFIRM_FAILURE_FLAG := p_x_workorder_rec.Confirm_Failure_Flag;
1086 l_prd_workorder_rec.CLASS_CODE := p_x_workorder_rec.Wip_Accounting_Class_Code;
1087 l_prd_workorder_rec.JOB_PRIORITY := p_x_workorder_rec.Priority_Code;
1088 l_prd_workorder_rec.JOB_PRIORITY_MEANING := p_x_workorder_rec.Priority;
1089 l_prd_workorder_rec.SUB_INVENTORY := p_x_workorder_rec.Sub_Inventory;
1090 l_prd_workorder_rec.LOCATOR_ID := p_x_workorder_rec.Locator_Id;
1091 l_prd_workorder_rec.ATTRIBUTE_CATEGORY := p_x_workorder_rec.ATTRIBUTE_CATEGORY;
1092 l_prd_workorder_rec.ATTRIBUTE1 := p_x_workorder_rec.ATTRIBUTE1;
1093 l_prd_workorder_rec.ATTRIBUTE2 := p_x_workorder_rec.ATTRIBUTE2;
1094 l_prd_workorder_rec.ATTRIBUTE3 := p_x_workorder_rec.ATTRIBUTE3;
1095 l_prd_workorder_rec.ATTRIBUTE4 := p_x_workorder_rec.ATTRIBUTE4;
1096 l_prd_workorder_rec.ATTRIBUTE5 := p_x_workorder_rec.ATTRIBUTE5;
1097 l_prd_workorder_rec.ATTRIBUTE6 := p_x_workorder_rec.ATTRIBUTE6;
1098 l_prd_workorder_rec.ATTRIBUTE7 := p_x_workorder_rec.ATTRIBUTE7;
1099 l_prd_workorder_rec.ATTRIBUTE8 := p_x_workorder_rec.ATTRIBUTE8;
1100 l_prd_workorder_rec.ATTRIBUTE9 := p_x_workorder_rec.ATTRIBUTE9;
1101 l_prd_workorder_rec.ATTRIBUTE10 := p_x_workorder_rec.ATTRIBUTE10;
1102 l_prd_workorder_rec.ATTRIBUTE11 := p_x_workorder_rec.ATTRIBUTE11;
1103 l_prd_workorder_rec.ATTRIBUTE12 := p_x_workorder_rec.ATTRIBUTE12;
1104 l_prd_workorder_rec.ATTRIBUTE13 := p_x_workorder_rec.ATTRIBUTE13;
1105 l_prd_workorder_rec.ATTRIBUTE14 := p_x_workorder_rec.ATTRIBUTE14;
1106 l_prd_workorder_rec.ATTRIBUTE15 := p_x_workorder_rec.ATTRIBUTE15;
1107 l_prd_workorder_rec.LAST_UPDATE_DATE := p_x_workorder_rec.LAST_UPDATED_DATE;
1108 l_prd_workorder_rec.LAST_UPDATED_BY := p_x_workorder_rec.LAST_UPDATED_BY;
1109 l_prd_workorder_rec.CREATION_DATE := p_x_workorder_rec.CREATION_DATE;
1110 l_prd_workorder_rec.CREATED_BY := p_x_workorder_rec.CREATED_BY;
1111 l_prd_workorder_rec.LAST_UPDATE_LOGIN := p_x_workorder_rec.LAST_UPDATE_LOGIN;
1112
1113 l_prd_workorder_rec.INVENTORY_ITEM_ID := p_x_workorder_rec.INVENTORY_ITEM_ID;
1114 l_prd_workorder_rec.ITEM_INSTANCE_ID := p_x_workorder_rec.ITEM_INSTANCE_ID;
1115 l_prd_workorder_rec.PROPAGATE_FLAG := p_x_workorder_rec.PROPAGATE_FLAG;
1116 l_prd_workorder_rec.SERVICE_ITEM_ID := p_x_workorder_rec.SERVICE_ITEM_ID;
1117 l_prd_workorder_rec.SERVICE_ITEM_ORG_ID := p_x_workorder_rec.SERVICE_ITEM_ORG_ID;
1118 l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION := p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION;
1119 l_prd_workorder_rec.SERVICE_ITEM_NUMBER := p_x_workorder_rec.SERVICE_ITEM_NUMBER;
1120 l_prd_workorder_rec.QUANTITY := p_x_workorder_rec.QUANTITY;
1121 l_prd_workorder_rec.MRP_QUANTITY := p_x_workorder_rec.MRP_QUANTITY;
1122 l_prd_workorder_rec.PARENT_ID := p_x_workorder_rec.PARENT_ID;
1123 l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID := p_x_workorder_rec.TASK_MOTIVE_STATUS_ID;
1124 l_prd_workorder_rec.ALLOW_EXPLOSION := p_x_workorder_rec.ALLOW_EXPLOSION;
1125
1126 AHL_PRD_WORKORDER_PVT.update_job
1127 (
1128 p_api_version => 1.0 ,
1129 p_init_msg_list => FND_API.G_TRUE,
1130 p_commit => FND_API.G_FALSE,
1131 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1132 p_default => FND_API.G_TRUE,
1133 p_module_type => 'API',
1134 x_return_status => x_return_status,
1135 x_msg_count => x_msg_count,
1136 x_msg_data => x_msg_data,
1137 p_wip_load_flag => 'Y',
1138 p_x_prd_workorder_rec => l_prd_workorder_rec,
1139 p_x_prd_workoper_tbl => l_prd_workoper_tbl
1140 );
1141 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1142 RAISE FND_API.G_EXC_ERROR;
1143 END IF;
1144
1145 p_x_workorder_rec.Batch_Id := l_prd_workorder_rec.BATCH_ID;
1146 p_x_workorder_rec.Header_Id := l_prd_workorder_rec.HEADER_ID;
1147 p_x_workorder_rec.Workorder_Id := l_prd_workorder_rec.WORKORDER_ID;
1148 p_x_workorder_rec.Wip_Entity_Id := l_prd_workorder_rec.WIP_ENTITY_ID;
1149 p_x_workorder_rec.Object_Version_Number := l_prd_workorder_rec.OBJECT_VERSION_NUMBER;
1150 p_x_workorder_rec.Workorder_Number := l_prd_workorder_rec.JOB_NUMBER;
1151 p_x_workorder_rec.Description := l_prd_workorder_rec.JOB_DESCRIPTION;
1152 p_x_workorder_rec.Organization_Id := l_prd_workorder_rec.ORGANIZATION_ID;
1153 p_x_workorder_rec.Organization_Name := l_prd_workorder_rec.ORGANIZATION_NAME;
1154 p_x_workorder_rec.Organization_Code := l_prd_workorder_rec.ORGANIZATION_CODE;
1155 p_x_workorder_rec.Department_Name := l_prd_workorder_rec.DEPARTMENT_NAME;
1156 p_x_workorder_rec.Department_Id := l_prd_workorder_rec.DEPARTMENT_ID;
1157 p_x_workorder_rec.Department_Class_Code := l_prd_workorder_rec.DEPARTMENT_CLASS_CODE;
1158 p_x_workorder_rec.Status_Code := l_prd_workorder_rec.STATUS_CODE ;
1159 p_x_workorder_rec.Status := l_prd_workorder_rec.STATUS_MEANING;
1160 p_x_workorder_rec.Hold_Reason_Code := l_prd_workorder_rec.HOLD_REASON_CODE;
1161 p_x_workorder_rec.Hold_Reason := l_prd_workorder_rec.HOLD_REASON;
1162 p_x_workorder_rec.Scheduled_Start_Date := l_prd_workorder_rec.SCHEDULED_START_DATE;
1163 p_x_workorder_rec.Scheduled_End_Date := l_prd_workorder_rec.SCHEDULED_END_DATE;
1164 p_x_workorder_rec.Actual_Start_Date := l_prd_workorder_rec.ACTUAL_START_DATE;
1165 p_x_workorder_rec.Actual_End_Date := l_prd_workorder_rec.ACTUAL_END_DATE ;
1166 p_x_workorder_rec.Completion_Subinventory := l_prd_workorder_rec.COMPLETION_SUBINVENTORY;
1167 p_x_workorder_rec.Completion_Locator_Id := l_prd_workorder_rec.COMPLETION_LOCATOR_ID;
1168 p_x_workorder_rec.Completion_Locator_Name := l_prd_workorder_rec.COMPLETION_LOCATOR_NAME;
1169 p_x_workorder_rec.Wip_Supply_Type := l_prd_workorder_rec.WIP_SUPPLY_TYPE;
1170 p_x_workorder_rec.Wip_Supply_Meaning := l_prd_workorder_rec.WIP_SUPPLY_MEANING;
1171 p_x_workorder_rec.Firm_Plan_Flag := l_prd_workorder_rec.FIRM_PLANNED_FLAG;
1172 p_x_workorder_rec.Confirm_Failure_Flag := l_prd_workorder_rec.CONFIRM_FAILURE_FLAG;
1173 p_x_workorder_rec.Wip_Accounting_Class_Code := l_prd_workorder_rec.CLASS_CODE;
1174 p_x_workorder_rec.Priority_Code := l_prd_workorder_rec.JOB_PRIORITY;
1175 p_x_workorder_rec.Priority := l_prd_workorder_rec.JOB_PRIORITY_MEANING;
1176 p_x_workorder_rec.Sub_Inventory := l_prd_workorder_rec.SUB_INVENTORY;
1177 p_x_workorder_rec.Locator_Id := l_prd_workorder_rec.LOCATOR_ID;
1178 p_x_workorder_rec.ATTRIBUTE_CATEGORY := l_prd_workorder_rec.ATTRIBUTE_CATEGORY;
1179 p_x_workorder_rec.ATTRIBUTE1 := l_prd_workorder_rec.ATTRIBUTE1;
1180 p_x_workorder_rec.ATTRIBUTE2 := l_prd_workorder_rec.ATTRIBUTE2;
1181 p_x_workorder_rec.ATTRIBUTE3 := l_prd_workorder_rec.ATTRIBUTE3;
1182 p_x_workorder_rec.ATTRIBUTE4 := l_prd_workorder_rec.ATTRIBUTE4;
1183 p_x_workorder_rec.ATTRIBUTE5 := l_prd_workorder_rec.ATTRIBUTE5;
1184 p_x_workorder_rec.ATTRIBUTE6 := l_prd_workorder_rec.ATTRIBUTE6;
1185 p_x_workorder_rec.ATTRIBUTE7 := l_prd_workorder_rec.ATTRIBUTE7;
1186 p_x_workorder_rec.ATTRIBUTE8 := l_prd_workorder_rec.ATTRIBUTE8;
1187 p_x_workorder_rec.ATTRIBUTE9 := l_prd_workorder_rec.ATTRIBUTE9;
1188 p_x_workorder_rec.ATTRIBUTE10 := l_prd_workorder_rec.ATTRIBUTE10;
1189 p_x_workorder_rec.ATTRIBUTE11 := l_prd_workorder_rec.ATTRIBUTE11;
1190 p_x_workorder_rec.ATTRIBUTE12 := l_prd_workorder_rec.ATTRIBUTE12;
1191 p_x_workorder_rec.ATTRIBUTE13 := l_prd_workorder_rec.ATTRIBUTE13;
1192 p_x_workorder_rec.ATTRIBUTE14 := l_prd_workorder_rec.ATTRIBUTE14;
1193 p_x_workorder_rec.ATTRIBUTE15 := l_prd_workorder_rec.ATTRIBUTE15;
1194 p_x_workorder_rec.LAST_UPDATED_DATE := l_prd_workorder_rec.LAST_UPDATE_DATE;
1195 p_x_workorder_rec.LAST_UPDATED_BY := l_prd_workorder_rec.LAST_UPDATED_BY;
1196 p_x_workorder_rec.CREATION_DATE := l_prd_workorder_rec.CREATION_DATE;
1197 p_x_workorder_rec.CREATED_BY := l_prd_workorder_rec.CREATED_BY;
1198 p_x_workorder_rec.LAST_UPDATE_LOGIN := l_prd_workorder_rec.LAST_UPDATE_LOGIN;
1199
1200 p_x_workorder_rec.INVENTORY_ITEM_ID := l_prd_workorder_rec.INVENTORY_ITEM_ID;
1201 p_x_workorder_rec.ITEM_INSTANCE_ID := l_prd_workorder_rec.ITEM_INSTANCE_ID;
1202 p_x_workorder_rec.PROPAGATE_FLAG := l_prd_workorder_rec.PROPAGATE_FLAG;
1203 p_x_workorder_rec.SERVICE_ITEM_ID := l_prd_workorder_rec.SERVICE_ITEM_ID;
1204 p_x_workorder_rec.SERVICE_ITEM_ORG_ID := l_prd_workorder_rec.SERVICE_ITEM_ORG_ID;
1205 p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION := l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION;
1206 p_x_workorder_rec.SERVICE_ITEM_NUMBER := l_prd_workorder_rec.SERVICE_ITEM_NUMBER;
1207 p_x_workorder_rec.QUANTITY := l_prd_workorder_rec.QUANTITY;
1208 p_x_workorder_rec.MRP_QUANTITY := l_prd_workorder_rec.MRP_QUANTITY;
1209 p_x_workorder_rec.PARENT_ID := l_prd_workorder_rec.PARENT_ID;
1210 p_x_workorder_rec.TASK_MOTIVE_STATUS_ID := l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID;
1211 p_x_workorder_rec.ALLOW_EXPLOSION := l_prd_workorder_rec.ALLOW_EXPLOSION;
1212
1213
1214 END IF;
1215
1216 END process_wo_details;
1217
1218 PROCEDURE process_op_quality
1219 (
1220 p_OP_DETAILS_REC IN OPERATION_REC_TYPE,
1221 p_x_OP_QaResults IN OUT NOCOPY QA_RESULTS_REC_TYPE,
1222 x_return_status OUT NOCOPY VARCHAR2,
1223 x_msg_count OUT NOCOPY NUMBER,
1224 x_msg_data OUT NOCOPY VARCHAR2
1225 ) IS
1226
1227 l_results_tbl AHL_QA_RESULTS_PVT.qa_results_tbl_type;
1228 l_hidden_results_tbl AHL_QA_RESULTS_PVT.qa_results_tbl_type;
1229 l_context_tbl AHL_QA_RESULTS_PVT.qa_context_tbl_type;
1230 l_occurrence_tbl AHL_QA_RESULTS_PVT.occurrence_tbl_type;
1231
1232 l_QA_PLAN QA_PLAN_REC_TYPE;
1233 l_QA_RESULTS QA_RESULTS_REC_TYPE;
1234 results_tbl_index integer;
1235 occurrence_tbl_index integer;
1236 l_rowElementCount integer;
1237
1238 BEGIN
1239 x_return_status := FND_API.G_RET_STS_SUCCESS;
1240 IF(p_x_OP_QaResults.QA_RESULT_TBL IS NULL OR p_x_OP_QaResults.QA_RESULT_TBL.COUNT < 1)THEN
1241 RETURN;
1242 END IF;
1243
1244 get_qa_plan_results
1245 (
1246 p_module_type => 'BPEL',
1247 p_Workorder_Id => p_OP_DETAILS_REC.Workorder_Id,
1248 p_Workorder_Number => NULL,
1249 p_WoOperationId => NULL,--p_OP_DETAILS_REC.Workorder_Operation_Id,
1250 p_OperationSequence => p_OP_DETAILS_REC.Operation_Sequence_Number,
1251 x_return_status => x_return_status,
1252 x_msg_count => x_msg_count,
1253 x_msg_data => x_msg_data,
1254 x_QaPlan => l_QA_PLAN,
1255 x_QaResults => l_QA_RESULTS
1256 );
1257 --AHL_DEBUG_PUB.debug( 'p_OP_DETAILS_REC.Workorder_Operation_Id : '||p_OP_DETAILS_REC.Workorder_Operation_Id);
1258 --AHL_DEBUG_PUB.debug( 'l_QA_PLAN.Plan_Id : '||l_QA_PLAN.Plan_Id);
1259
1260 l_context_tbl(1).Name := 'operation_id';
1261 l_context_tbl(1).Value := to_char(p_OP_DETAILS_REC.Workorder_Operation_Id);
1262
1263 l_context_tbl(2).Name := 'object_version_no';
1264 l_context_tbl(2).Value := to_char(p_OP_DETAILS_REC.Object_Version_Number);
1265
1266 results_tbl_index := 1;
1267 occurrence_tbl_index :=1;
1268 FOR i IN p_x_OP_QaResults.QA_RESULT_TBL.FIRST..p_x_OP_QaResults.QA_RESULT_TBL.LAST LOOP
1269 l_rowElementCount := 0;
1270 FOR j IN p_x_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL.FIRST..p_x_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL.LAST LOOP
1271 IF('T' = IS_VALID_RESULT_ATTRIBUTE(
1272 p_x_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).Char_Id,
1273 l_QA_PLAN))THEN
1274 l_results_tbl(results_tbl_index).char_id := p_x_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).Char_Id;
1275 l_results_tbl(results_tbl_index).result_value := p_x_OP_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).Attribute_Value;
1276 results_tbl_index := results_tbl_index+1;
1277 l_rowElementCount := l_rowElementCount+1;
1278 END IF;
1279 END LOOP;
1280 IF(l_rowElementCount > 0)THEN
1281 l_occurrence_tbl(occurrence_tbl_index).element_count := l_rowElementCount;
1282 occurrence_tbl_index := occurrence_tbl_index + 1;
1283 END IF;
1284 END LOOP;
1285 IF(results_tbl_index = 1)THEN
1286 RETURN; -- No attributes passed.
1287 END IF;
1288
1289 AHL_QA_RESULTS_PVT.submit_qa_results
1290 (
1291 p_api_version => 1.0,
1292 p_init_msg_list => FND_API.G_TRUE,
1293 p_commit => FND_API.G_FALSE,
1294 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1295 p_default => FND_API.G_FALSE,
1296 p_module_type => 'API',
1297 x_return_status => x_return_status,
1298 x_msg_count => x_msg_count,
1299 x_msg_data => x_msg_data,
1300 p_plan_id => l_QA_PLAN.Plan_Id,
1301 p_organization_id => l_QA_PLAN.Organization_Id,
1302 p_transaction_no => 2002,
1303 p_specification_id => NULL,
1304 p_results_tbl => l_results_tbl,
1305 p_hidden_results_tbl => l_hidden_results_tbl,
1306 p_context_tbl => l_context_tbl,
1307 p_result_commit_flag => 0,
1308 p_id_or_value => 'VALUE',
1309 p_x_collection_id => l_QA_RESULTS.Collection_Id,
1310 p_x_occurrence_tbl => l_occurrence_tbl
1311 );
1312 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1313 RAISE FND_API.G_EXC_ERROR;
1314 END IF;
1315 p_x_OP_QaResults.Collection_Id := l_QA_RESULTS.Collection_Id;
1316 p_x_OP_QaResults.Plan_Id := l_QA_PLAN.Plan_Id;
1317 END process_op_quality;
1318
1319 PROCEDURE process_op_details
1320 (
1321 p_workorder_rec IN WORKORDER_REC_TYPE,
1322 p_x_Operations IN OUT NOCOPY OPERATIONS_TBL_TYPE,
1323 x_return_status OUT NOCOPY VARCHAR2,
1324 x_msg_count OUT NOCOPY NUMBER,
1325 x_msg_data OUT NOCOPY VARCHAR2
1326 )IS
1327
1328
1329 CURSOR get_current_obj_ver_csr(p_WoOperationId IN NUMBER)IS
1330 SELECT object_version_number,status_code FROM AHL_WORKORDER_OPERATIONS
1331 WHERE WORKORDER_OPERATION_ID = p_WoOperationId;
1332
1333 l_prd_operation_tbl AHL_PRD_OPERATIONS_PVT.PRD_OPERATION_TBL;
1334 j INTEGER;
1335 l_out_Operations OPERATIONS_TBL_TYPE;
1336 l_complete_flag BOOLEAN;
1337 l_curr_status VARCHAR2(30);
1338
1339 CURSOR get_operation_status(c_operation_status VARCHAR2)
1340 IS
1341 SELECT lookup_code
1342 FROM FND_LOOKUP_VALUES_VL
1343 WHERE lookup_type='AHL_OPERATION_STATUS'
1344 AND UPPER(meaning)=UPPER(c_operation_status)
1345 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
1346 AND TRUNC(NVL(end_date_active,SYSDATE+1));
1347
1348 CURSOR get_route_id_validation_csr(c_wo_id NUMBER)
1349 IS
1350 SELECT 'x' FROM AHL_WORKORDERS AWOS
1351 WHERE AWOS.workorder_id = c_wo_id
1352 AND AWOS.route_id IS NULL;
1353 l_temp VARCHAR2(1);
1354
1355
1356
1357 BEGIN
1358 x_return_status := FND_API.G_RET_STS_SUCCESS;
1359 IF(p_x_Operations IS NULL OR p_x_Operations.COUNT < 1)THEN
1360 RETURN;
1361 END IF;
1362 j :=1;--keep this as 1 as thats how called package recognize things..
1363 FOR i IN p_x_Operations.FIRST..p_x_Operations.LAST LOOP
1364 IF(p_x_Operations(i).OPERATION_FLAG = 'U')THEN
1365 IF(p_x_Operations(i).Workorder_Operation_Id IS NULL)THEN
1366 p_x_Operations(i).Workorder_Operation_Id := get_workorder_operation_id(
1367 p_workorder_rec.Workorder_Id,
1368 p_x_Operations(i).Operation_Sequence_Number);
1369 END IF;
1370 process_op_quality
1371 (
1372 p_OP_DETAILS_REC => p_x_Operations(i),
1373 p_x_OP_QaResults => p_x_Operations(i).QAResults,
1374 x_return_status => x_return_status,
1375 x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data
1377 );
1378 --DBMS_OUTPUT.put_line('x_return_status :i: ' || x_return_status);
1379 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1380 RAISE FND_API.G_EXC_ERROR;
1381 END IF;
1382 -- Quality update bumps up the obj version number
1383 OPEN get_current_obj_ver_csr(p_x_Operations(i).Workorder_Operation_Id);
1384 FETCH get_current_obj_ver_csr INTO p_x_Operations(i).Object_Version_Number,l_curr_status;
1385 CLOSE get_current_obj_ver_csr;
1386 END IF;
1387 ----DBMS_OUTPUT.put_line('op :i:statusCode: ' || p_x_Operations(i).Status_Code);
1388 l_prd_operation_tbl(j).workorder_operation_id := p_x_Operations(i).Workorder_Operation_Id;
1389 l_prd_operation_tbl(j).operation_sequence_num := p_x_Operations(i).Operation_Sequence_Number;
1390 l_prd_operation_tbl(j).workorder_id := p_workorder_rec.Workorder_Id;
1391 l_prd_operation_tbl(j).organization_id := p_x_Operations(i).organization_id;
1392 l_prd_operation_tbl(j).department_id := p_x_Operations(i).Department_Id;
1393 l_prd_operation_tbl(j).department_name := p_x_Operations(i).Department_Name;
1394 l_prd_operation_tbl(j).object_version_number := p_x_Operations(i).Object_Version_Number;
1395 l_prd_operation_tbl(j).Operation_Id := p_x_Operations(i).Operation_Id;
1396 l_prd_operation_tbl(j).Operation_Code := p_x_Operations(i).Operation_Code;
1397 l_prd_operation_tbl(j).Operation_Type_Code := p_x_Operations(i).Operation_Type_Code;
1398 l_prd_operation_tbl(j).Operation_Type := p_x_Operations(i).Operation_Type;
1399
1400 l_prd_operation_tbl(j).dml_operation := p_x_Operations(i).OPERATION_FLAG;
1401 IF(p_x_Operations(i).OPERATION_FLAG = 'C')THEN
1402 OPEN get_route_id_validation_csr(p_workorder_rec.Workorder_Id);
1403 FETCH get_route_id_validation_csr INTO l_temp;
1404 IF(get_route_id_validation_csr%NOTFOUND)THEN
1405 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_OPER_INV_CR');
1406 FND_MESSAGE.SET_TOKEN('RECORD',l_prd_operation_tbl(j).operation_sequence_num,false);
1407 FND_MSG_PUB.ADD;
1408 CLOSE get_route_id_validation_csr;
1409 RAISE FND_API.G_EXC_ERROR;
1410 END IF;
1411 CLOSE get_route_id_validation_csr;
1412 END IF;
1413 l_prd_operation_tbl(j).OPERATION_DESCRIPTION := p_x_Operations(i).Description;
1414 l_prd_operation_tbl(j).status_code := l_curr_status;
1415 IF(p_x_Operations(i).status_code IS NULL
1416 AND p_x_Operations(i).status IS NOT NULL)THEN
1417 OPEN get_operation_status(p_x_Operations(i).status);
1418 FETCH get_operation_status INTO p_x_Operations(i).status_code;
1419 IF(get_operation_status%NOTFOUND)THEN
1420 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_UPDOP_INV_OP_STATUS');
1421 FND_MESSAGE.SET_TOKEN('OP_SEQ', p_x_Operations(i).Operation_Sequence_Number);
1422 FND_MSG_PUB.ADD;
1423 CLOSE get_operation_status;
1424 RAISE FND_API.G_EXC_ERROR;
1425 END IF;
1426 CLOSE get_operation_status;
1427 END IF;
1428 --l_prd_operation_tbl(j).status_meaning := p_x_Operations(i).Status;
1429 l_prd_operation_tbl(j).scheduled_start_date := p_x_Operations(i).Scheduled_Start_Date;
1430 l_prd_operation_tbl(j).SCHEDULED_START_HR := TO_CHAR(l_prd_operation_tbl(j).SCHEDULED_START_DATE, 'hh24');
1431 l_prd_operation_tbl(j).SCHEDULED_START_MI := TO_CHAR(l_prd_operation_tbl(j).SCHEDULED_START_DATE, 'mi');
1432 l_prd_operation_tbl(j).scheduled_end_date := p_x_Operations(i).Scheduled_End_Date;
1433 l_prd_operation_tbl(j).SCHEDULED_END_HR := TO_CHAR(l_prd_operation_tbl(j).SCHEDULED_END_DATE, 'hh24');
1434 l_prd_operation_tbl(j).SCHEDULED_END_MI := TO_CHAR(l_prd_operation_tbl(j).SCHEDULED_END_DATE, 'mi');
1435 l_prd_operation_tbl(j).actual_start_date := p_x_Operations(i).Actual_Start_Date;
1436 l_prd_operation_tbl(j).ACTUAL_START_HR := TO_CHAR(l_prd_operation_tbl(j).ACTUAL_START_DATE, 'hh24');
1437 l_prd_operation_tbl(j).ACTUAL_START_MI := TO_CHAR(l_prd_operation_tbl(j).ACTUAL_START_DATE, 'mi');
1438 l_prd_operation_tbl(j).actual_end_date := p_x_Operations(i).Actual_End_Date;
1439 l_prd_operation_tbl(j).ACTUAL_END_HR := TO_CHAR(l_prd_operation_tbl(j).ACTUAL_END_DATE, 'hh24');
1440 l_prd_operation_tbl(j).ACTUAL_END_MI := TO_CHAR(l_prd_operation_tbl(j).ACTUAL_END_DATE, 'mi');
1441 l_prd_operation_tbl(j).LAST_UPDATE_LOGIN := p_x_Operations(i).LAST_UPDATE_LOGIN;
1442 l_prd_operation_tbl(j).LAST_UPDATE_DATE := p_x_Operations(i).LAST_UPDATED_DATE;
1443 l_prd_operation_tbl(j).LAST_UPDATED_BY := p_x_Operations(i).LAST_UPDATED_BY;
1444 l_prd_operation_tbl(j).CREATION_DATE := p_x_Operations(i).CREATION_DATE;
1445 l_prd_operation_tbl(j).CREATED_BY := p_x_Operations(i).CREATED_BY;
1446 l_prd_operation_tbl(j).ATTRIBUTE_CATEGORY := p_x_Operations(i).ATTRIBUTE_CATEGORY;
1447 l_prd_operation_tbl(j).ATTRIBUTE1 := p_x_Operations(i).ATTRIBUTE1;
1448 l_prd_operation_tbl(j).ATTRIBUTE2 := p_x_Operations(i).ATTRIBUTE2;
1449 l_prd_operation_tbl(j).ATTRIBUTE3 := p_x_Operations(i).ATTRIBUTE3;
1450 l_prd_operation_tbl(j).ATTRIBUTE4 := p_x_Operations(i).ATTRIBUTE4;
1451 l_prd_operation_tbl(j).ATTRIBUTE5 := p_x_Operations(i).ATTRIBUTE5;
1452 l_prd_operation_tbl(j).ATTRIBUTE6 := p_x_Operations(i).ATTRIBUTE6;
1453 l_prd_operation_tbl(j).ATTRIBUTE7 := p_x_Operations(i).ATTRIBUTE7;
1454 l_prd_operation_tbl(j).ATTRIBUTE8 := p_x_Operations(i).ATTRIBUTE8;
1455 l_prd_operation_tbl(j).ATTRIBUTE9 := p_x_Operations(i).ATTRIBUTE9;
1456 l_prd_operation_tbl(j).ATTRIBUTE10 := p_x_Operations(i).ATTRIBUTE10;
1457 l_prd_operation_tbl(j).ATTRIBUTE11 := p_x_Operations(i).ATTRIBUTE11;
1458 l_prd_operation_tbl(j).ATTRIBUTE12 := p_x_Operations(i).ATTRIBUTE12;
1459 l_prd_operation_tbl(j).ATTRIBUTE13 := p_x_Operations(i).ATTRIBUTE13;
1460 l_prd_operation_tbl(j).ATTRIBUTE14 := p_x_Operations(i).ATTRIBUTE14;
1461 l_prd_operation_tbl(j).ATTRIBUTE15 := p_x_Operations(i).ATTRIBUTE15;
1462 j := j+1;
1463 END LOOP;
1464 IF(l_prd_operation_tbl IS NOT NULL AND l_prd_operation_tbl.COUNT > 0)THEN
1465 ----DBMS_OUTPUT.put_line('op :i:statusCode: processing operations : ' || l_prd_operation_tbl.COUNT);
1466 AHL_PRD_OPERATIONS_PVT.PROCESS_OPERATIONS
1467 (
1468 p_api_version => 1.0 ,
1469 p_init_msg_list => FND_API.G_TRUE,
1470 p_commit => FND_API.G_FALSE,
1471 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1472 p_default => FND_API.G_TRUE,
1473 p_module_type => 'API',
1474 p_wip_mass_load_flag => 'Y',
1475 x_return_status => x_return_status,
1476 x_msg_count => x_msg_count,
1477 x_msg_data => x_msg_data,
1478 p_x_prd_operation_tbl => l_prd_operation_tbl
1479 );
1480 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1481 RAISE FND_API.G_EXC_ERROR;
1482 END IF;
1483 j:= 1;
1484 FOR i IN l_prd_operation_tbl.FIRST..l_prd_operation_tbl.LAST LOOP
1485 l_out_Operations(j).Workorder_Operation_Id := l_prd_operation_tbl(i).workorder_operation_id;
1486 l_out_Operations(j).Operation_Sequence_Number := l_prd_operation_tbl(i).operation_sequence_num ;
1487 l_out_Operations(j).Workorder_Id := l_prd_operation_tbl(i).workorder_id;
1488 l_out_Operations(j).Organization_Id := l_prd_operation_tbl(i).Organization_Id;
1489 l_out_Operations(j).Department_Id := l_prd_operation_tbl(i).department_id;
1490 l_out_Operations(j).Department_Name := l_prd_operation_tbl(i).department_name;
1491 l_out_Operations(j).Object_Version_Number := l_prd_operation_tbl(i).object_version_number;
1492 l_out_Operations(j).Operation_Id := l_prd_operation_tbl(i).Operation_Id;
1493 l_out_Operations(j).Operation_Code := l_prd_operation_tbl(i).Operation_Code;
1494 l_out_Operations(j).Operation_Type_Code := l_prd_operation_tbl(i).Operation_Type_Code;
1495 l_out_Operations(j).Operation_Type := l_prd_operation_tbl(i).Operation_Type;
1496 l_out_Operations(j).Scheduled_Start_Date := l_prd_operation_tbl(i).scheduled_start_date;
1497 l_out_Operations(j).Scheduled_End_Date := l_prd_operation_tbl(i).scheduled_end_date;
1498 l_out_Operations(j).OPERATION_FLAG := l_prd_operation_tbl(i).dml_operation;
1499 l_out_Operations(j).Description := l_prd_operation_tbl(i).OPERATION_DESCRIPTION;
1500 l_out_Operations(j).Status_Code := l_prd_operation_tbl(i).status_code;
1501 l_out_Operations(j).Status := l_prd_operation_tbl(i).status_meaning;
1502 l_out_Operations(j).Actual_Start_Date := l_prd_operation_tbl(i).actual_start_date;
1503 l_out_Operations(j).Actual_End_Date := l_prd_operation_tbl(i).actual_end_date;
1504 l_out_Operations(j).LAST_UPDATE_LOGIN := l_prd_operation_tbl(i).LAST_UPDATE_LOGIN;
1505 l_out_Operations(j).LAST_UPDATED_DATE := l_prd_operation_tbl(i).LAST_UPDATE_DATE;
1506 l_out_Operations(j).LAST_UPDATED_BY := l_prd_operation_tbl(i).LAST_UPDATED_BY;
1507 l_out_Operations(j).CREATION_DATE := l_prd_operation_tbl(i).CREATION_DATE;
1508 l_out_Operations(j).CREATED_BY := l_prd_operation_tbl(i).CREATED_BY;
1509 l_out_Operations(j).ATTRIBUTE_CATEGORY := l_prd_operation_tbl(i).ATTRIBUTE_CATEGORY;
1510 l_out_Operations(j).ATTRIBUTE1 := l_prd_operation_tbl(i).ATTRIBUTE1;
1511 l_out_Operations(j).ATTRIBUTE2 := l_prd_operation_tbl(i).ATTRIBUTE2;
1512 l_out_Operations(j).ATTRIBUTE3 := l_prd_operation_tbl(i).ATTRIBUTE3;
1513 l_out_Operations(j).ATTRIBUTE4 := l_prd_operation_tbl(i).ATTRIBUTE4;
1514 l_out_Operations(j).ATTRIBUTE5 := l_prd_operation_tbl(i).ATTRIBUTE5;
1515 l_out_Operations(j).ATTRIBUTE6 := l_prd_operation_tbl(i).ATTRIBUTE6;
1516 l_out_Operations(j).ATTRIBUTE7 := l_prd_operation_tbl(i).ATTRIBUTE7;
1517 l_out_Operations(j).ATTRIBUTE8 := l_prd_operation_tbl(i).ATTRIBUTE8;
1518 l_out_Operations(j).ATTRIBUTE9 := l_prd_operation_tbl(i).ATTRIBUTE9;
1519 l_out_Operations(j).ATTRIBUTE10 := l_prd_operation_tbl(i).ATTRIBUTE10;
1520 l_out_Operations(j).ATTRIBUTE11 := l_prd_operation_tbl(i).ATTRIBUTE11;
1521 l_out_Operations(j).ATTRIBUTE12 := l_prd_operation_tbl(i).ATTRIBUTE12;
1522 l_out_Operations(j).ATTRIBUTE13 := l_prd_operation_tbl(i).ATTRIBUTE13;
1523 l_out_Operations(j).ATTRIBUTE14 := l_prd_operation_tbl(i).ATTRIBUTE14;
1524 l_out_Operations(j).ATTRIBUTE15 := l_prd_operation_tbl(i).ATTRIBUTE15;
1525 FOR k IN p_x_Operations.FIRST..p_x_Operations.LAST LOOP
1526 IF(l_out_Operations(j).Workorder_Operation_Id = p_x_Operations(k).Workorder_Operation_Id)THEN
1527 l_out_Operations(j).QAResults := p_x_Operations(k).QAResults;
1528 IF(p_x_Operations(k).status_code <> l_out_Operations(j).Status_Code AND
1529 p_x_Operations(k).status_code = '1') THEN
1530 OPEN get_current_obj_ver_csr(l_out_Operations(j).Workorder_Operation_Id);
1531 FETCH get_current_obj_ver_csr INTO l_out_Operations(j).Object_Version_Number,l_out_Operations(j).Status_Code;
1532 CLOSE get_current_obj_ver_csr;
1533 AHL_COMPLETIONS_PVT.complete_operation
1534 (
1535 p_api_version => 1.0,
1536 p_init_msg_list => FND_API.G_TRUE,
1537 p_commit => FND_API.G_FALSE,
1538 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1539 p_default => FND_API.G_FALSE,
1540 p_module_type => 'API',
1541 x_return_status => x_return_status,
1542 x_msg_count => x_msg_count,
1543 x_msg_data => x_msg_data,
1544 p_workorder_operation_id => l_out_Operations(j).Workorder_Operation_Id,
1545 p_object_version_no => l_out_Operations(j).Object_Version_Number
1546 );
1547 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1548 RAISE FND_API.G_EXC_ERROR;
1549 END IF;
1550 END IF;
1551 OPEN get_current_obj_ver_csr(l_out_Operations(j).Workorder_Operation_Id);
1552 FETCH get_current_obj_ver_csr INTO l_out_Operations(j).Object_Version_Number,l_out_Operations(j).Status_Code;
1553 CLOSE get_current_obj_ver_csr;
1554 EXIT;
1555 END IF;
1556 END LOOP;
1557 j := j+1;
1558 END LOOP;
1559 p_x_Operations := l_out_Operations;
1560 END IF;
1561 END process_op_details;
1562
1563 PROCEDURE process_mtl_requirements
1564 (
1565 p_workorder_rec IN WORKORDER_REC_TYPE,
1566 p_x_MaterialRequirements IN OUT NOCOPY MTL_REQMTS_TBL_TYPE,
1567 x_return_status OUT NOCOPY VARCHAR2,
1568 x_msg_count OUT NOCOPY NUMBER,
1569 x_msg_data OUT NOCOPY VARCHAR2
1570 ) IS
1571 l_req_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
1572 j INTEGER;
1573 BEGIN
1574 x_return_status := FND_API.G_RET_STS_SUCCESS;
1575 IF(p_x_MaterialRequirements IS NULL OR p_x_MaterialRequirements.COUNT < 1)THEN
1576 RETURN;
1577 END IF;
1578 j := 1;
1579 FOR i IN p_x_MaterialRequirements.FIRST..p_x_MaterialRequirements.LAST LOOP
1580 l_req_material_tbl(j).SCHEDULE_MATERIAL_ID := p_x_MaterialRequirements(i).Scheduled_Material_Id;
1581 l_req_material_tbl(j).OBJECT_VERSION_NUMBER := p_x_MaterialRequirements(i).Object_Version_Number;
1582 l_req_material_tbl(j).INVENTORY_ITEM_ID := p_x_MaterialRequirements(i).Inventory_Item_Id;
1583 l_req_material_tbl(j).ORGANIZATION_ID := p_workorder_rec.Organization_Id;
1584 l_req_material_tbl(j).SCHEDULED_DATE := p_x_MaterialRequirements(i).Scheduled_Date;
1585 l_req_material_tbl(j).CONCATENATED_SEGMENTS := p_x_MaterialRequirements(i).Item_Number;
1586 l_req_material_tbl(j).ITEM_DESCRIPTION := p_x_MaterialRequirements(i).Item_Description;
1587 l_req_material_tbl(j).REQUESTED_QUANTITY := p_x_MaterialRequirements(i).Required_Quantity;
1588 l_req_material_tbl(j).REQUESTED_DATE := p_x_MaterialRequirements(i).Required_Date;
1589 l_req_material_tbl(j).UOM_CODE := p_x_MaterialRequirements(i).Part_UOM_Code;
1590 l_req_material_tbl(j).UOM_MEANING := p_x_MaterialRequirements(i).Part_UOM;
1591 l_req_material_tbl(j).SCHEDULED_QUANTITY := p_x_MaterialRequirements(i).Scheduled_Quantity;
1592 l_req_material_tbl(j).JOB_NUMBER := p_workorder_rec.Workorder_Number;
1593 l_req_material_tbl(j).WORKORDER_ID := p_workorder_rec.Workorder_Id;
1594 l_req_material_tbl(j).WIP_ENTITY_ID := p_workorder_rec.Wip_Entity_Id;
1595 l_req_material_tbl(j).OPERATION_SEQUENCE := p_x_MaterialRequirements(i).Operation_Sequence_Number;
1596 l_req_material_tbl(j).LAST_UPDATED_DATE := p_x_MaterialRequirements(i).LAST_UPDATED_DATE;
1597 l_req_material_tbl(j).WORKORDER_OPERATION_ID := p_x_MaterialRequirements(i).Workorder_Operation_Id;
1598 l_req_material_tbl(j).ATTRIBUTE_CATEGORY := p_x_MaterialRequirements(i).ATTRIBUTE_CATEGORY;
1599 l_req_material_tbl(j).ATTRIBUTE1 := p_x_MaterialRequirements(i).ATTRIBUTE1;
1600 l_req_material_tbl(j).ATTRIBUTE2 := p_x_MaterialRequirements(i).ATTRIBUTE2;
1601 l_req_material_tbl(j).ATTRIBUTE3 := p_x_MaterialRequirements(i).ATTRIBUTE3;
1602 l_req_material_tbl(j).ATTRIBUTE4 := p_x_MaterialRequirements(i).ATTRIBUTE4;
1603 l_req_material_tbl(j).ATTRIBUTE5 := p_x_MaterialRequirements(i).ATTRIBUTE5;
1604 l_req_material_tbl(j).ATTRIBUTE6 := p_x_MaterialRequirements(i).ATTRIBUTE6;
1605 l_req_material_tbl(j).ATTRIBUTE7 := p_x_MaterialRequirements(i).ATTRIBUTE7;
1606 l_req_material_tbl(j).ATTRIBUTE8 := p_x_MaterialRequirements(i).ATTRIBUTE8;
1607 l_req_material_tbl(j).ATTRIBUTE9 := p_x_MaterialRequirements(i).ATTRIBUTE9;
1608 l_req_material_tbl(j).ATTRIBUTE10 := p_x_MaterialRequirements(i).ATTRIBUTE10;
1609 l_req_material_tbl(j).ATTRIBUTE11 := p_x_MaterialRequirements(i).ATTRIBUTE11;
1610 l_req_material_tbl(j).ATTRIBUTE12 := p_x_MaterialRequirements(i).ATTRIBUTE12;
1611 l_req_material_tbl(j).ATTRIBUTE13 := p_x_MaterialRequirements(i).ATTRIBUTE13;
1612 l_req_material_tbl(j).ATTRIBUTE14 := p_x_MaterialRequirements(i).ATTRIBUTE14;
1613 l_req_material_tbl(j).ATTRIBUTE15 := p_x_MaterialRequirements(i).ATTRIBUTE15;
1614 l_req_material_tbl(j).OPERATION_FLAG := p_x_MaterialRequirements(i).OPERATION_FLAG;
1615 --sukhwsin: SB Effectivity Code changes - starts
1616 l_req_material_tbl(j).MC_HEADER_ID := p_x_MaterialRequirements(i).MC_HEADER_ID;
1617 l_req_material_tbl(j).MC_NAME := p_x_MaterialRequirements(i).MC_NAME;
1618 l_req_material_tbl(j).MC_POSITION_KEY := p_x_MaterialRequirements(i).MC_POSITION_KEY;
1619 l_req_material_tbl(j).MC_POSITION := p_x_MaterialRequirements(i).MC_POSITION;
1620 l_req_material_tbl(j).ITEM_GROUP_ID := p_x_MaterialRequirements(i).ITEM_GROUP_ID;
1621 l_req_material_tbl(j).ITEM_GROUP_NAME := p_x_MaterialRequirements(i).ITEM_GROUP_NAME;
1622 --sukhwsin: SB Effectivity Code changes - ends
1623 j := j+1;
1624 END LOOP;
1625 AHL_PP_MATERIALS_PVT.Process_Material_Request (
1626 p_api_version => 1.0 ,
1627 p_init_msg_list => FND_API.G_TRUE,
1628 p_commit => FND_API.G_FALSE,
1629 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1630 p_module_type => 'API',
1631 p_x_req_material_tbl => l_req_material_tbl,
1632 x_return_status => x_return_status,
1633 x_msg_count => x_msg_count,
1634 x_msg_data => x_msg_data
1635 );
1636 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1637 RAISE FND_API.G_EXC_ERROR;
1638 END IF;
1639 j := 1;
1640 FOR i IN l_req_material_tbl.FIRST..l_req_material_tbl.LAST LOOP
1641 p_x_MaterialRequirements(j).Scheduled_Material_Id := l_req_material_tbl(i).SCHEDULE_MATERIAL_ID;
1642 p_x_MaterialRequirements(j).Object_Version_Number := l_req_material_tbl(i).OBJECT_VERSION_NUMBER;
1643 p_x_MaterialRequirements(j).Inventory_Item_Id := l_req_material_tbl(i).INVENTORY_ITEM_ID;
1644 p_x_MaterialRequirements(j).Scheduled_Date := l_req_material_tbl(i).SCHEDULED_DATE;
1645 p_x_MaterialRequirements(j).Item_Number := l_req_material_tbl(i).CONCATENATED_SEGMENTS;
1646 p_x_MaterialRequirements(j).Item_Description := l_req_material_tbl(i).ITEM_DESCRIPTION;
1647 p_x_MaterialRequirements(j).Required_Quantity := l_req_material_tbl(i).REQUESTED_QUANTITY;
1648 p_x_MaterialRequirements(j).Required_Date := l_req_material_tbl(i).REQUESTED_DATE;
1649 p_x_MaterialRequirements(j).Part_UOM_Code := l_req_material_tbl(i).UOM_CODE;
1650 p_x_MaterialRequirements(j).Part_UOM := l_req_material_tbl(i).UOM_MEANING;
1651 p_x_MaterialRequirements(j).Scheduled_Quantity := l_req_material_tbl(i).SCHEDULED_QUANTITY;
1652 p_x_MaterialRequirements(j).Workorder_Id := l_req_material_tbl(i).WORKORDER_ID;
1653 p_x_MaterialRequirements(j).Operation_Sequence_Number := l_req_material_tbl(i).OPERATION_SEQUENCE;
1654 p_x_MaterialRequirements(j).LAST_UPDATED_DATE := l_req_material_tbl(i).LAST_UPDATED_DATE;
1655 p_x_MaterialRequirements(j).Workorder_Operation_Id := l_req_material_tbl(i).WORKORDER_OPERATION_ID;
1656 p_x_MaterialRequirements(j).ATTRIBUTE_CATEGORY := l_req_material_tbl(i).ATTRIBUTE_CATEGORY;
1657 p_x_MaterialRequirements(j).ATTRIBUTE1 := l_req_material_tbl(i).ATTRIBUTE1;
1658 p_x_MaterialRequirements(j).ATTRIBUTE2 := l_req_material_tbl(i).ATTRIBUTE2;
1659 p_x_MaterialRequirements(j).ATTRIBUTE3 := l_req_material_tbl(i).ATTRIBUTE3;
1660 p_x_MaterialRequirements(j).ATTRIBUTE4 := l_req_material_tbl(i).ATTRIBUTE4;
1661 p_x_MaterialRequirements(j).ATTRIBUTE5 := l_req_material_tbl(i).ATTRIBUTE5;
1662 p_x_MaterialRequirements(j).ATTRIBUTE6 := l_req_material_tbl(i).ATTRIBUTE6;
1663 p_x_MaterialRequirements(j).ATTRIBUTE7 := l_req_material_tbl(i).ATTRIBUTE7;
1664 p_x_MaterialRequirements(j).ATTRIBUTE8 := l_req_material_tbl(i).ATTRIBUTE8;
1665 p_x_MaterialRequirements(j).ATTRIBUTE9 := l_req_material_tbl(i).ATTRIBUTE9;
1666 p_x_MaterialRequirements(j).ATTRIBUTE10 := l_req_material_tbl(i).ATTRIBUTE10;
1667 p_x_MaterialRequirements(j).ATTRIBUTE11 := l_req_material_tbl(i).ATTRIBUTE11;
1668 p_x_MaterialRequirements(j).ATTRIBUTE12 := l_req_material_tbl(i).ATTRIBUTE12;
1669 p_x_MaterialRequirements(j).ATTRIBUTE13 := l_req_material_tbl(i).ATTRIBUTE13;
1670 p_x_MaterialRequirements(j).ATTRIBUTE14 := l_req_material_tbl(i).ATTRIBUTE14;
1671 p_x_MaterialRequirements(j).ATTRIBUTE15 := l_req_material_tbl(i).ATTRIBUTE15;
1672 p_x_MaterialRequirements(j).OPERATION_FLAG := l_req_material_tbl(i).OPERATION_FLAG;
1673 --sukhwsin: SB Effectivity Code changes - starts
1674 p_x_MaterialRequirements(j).MC_HEADER_ID := l_req_material_tbl(i).MC_HEADER_ID;
1675 p_x_MaterialRequirements(j).MC_NAME := l_req_material_tbl(i).MC_NAME;
1676 p_x_MaterialRequirements(j).MC_POSITION_KEY := l_req_material_tbl(i).MC_POSITION_KEY;
1677 p_x_MaterialRequirements(j).MC_POSITION := l_req_material_tbl(i).MC_POSITION;
1678 p_x_MaterialRequirements(j).ITEM_GROUP_ID := l_req_material_tbl(i).ITEM_GROUP_ID;
1679 p_x_MaterialRequirements(j).ITEM_GROUP_NAME := l_req_material_tbl(i).ITEM_GROUP_NAME;
1680 --sukhwsin: SB Effectivity Code changes - ends
1681 j := j+1;
1682 END LOOP;
1683
1684
1685 END process_mtl_requirements;
1686
1687 PROCEDURE process_wo_quality
1688 (
1689 p_workorder_rec IN WORKORDER_REC_TYPE,
1690 p_x_WO_QaResults IN OUT NOCOPY QA_RESULTS_REC_TYPE,
1691 x_return_status OUT NOCOPY VARCHAR2,
1692 x_msg_count OUT NOCOPY NUMBER,
1693 x_msg_data OUT NOCOPY VARCHAR2
1694 ) IS
1695
1696 l_results_tbl AHL_QA_RESULTS_PVT.qa_results_tbl_type;
1697 l_hidden_results_tbl AHL_QA_RESULTS_PVT.qa_results_tbl_type;
1698 l_context_tbl AHL_QA_RESULTS_PVT.qa_context_tbl_type;
1699 l_occurrence_tbl AHL_QA_RESULTS_PVT.occurrence_tbl_type;
1700
1701 l_QA_PLAN QA_PLAN_REC_TYPE;
1702 l_QA_RESULTS QA_RESULTS_REC_TYPE;
1703 results_tbl_index integer;
1704 occurrence_tbl_index integer;
1705 l_rowElementCount integer;
1706
1707
1708 BEGIN
1709 x_return_status := FND_API.G_RET_STS_SUCCESS;
1710 IF(p_x_WO_QaResults.QA_RESULT_TBL IS NULL OR p_x_WO_QaResults.QA_RESULT_TBL.COUNT < 1)THEN
1711 RETURN;
1712 END IF;
1713
1714
1715 get_qa_plan_results
1716 (
1717 p_module_type => 'BPEL',
1718 p_Workorder_Id => p_workorder_rec.Workorder_Id,
1719 p_Workorder_Number => NULL,
1720 p_WoOperationId => NULL,
1721 p_OperationSequence => NULL,
1722 x_return_status => x_return_status,
1723 x_msg_count => x_msg_count,
1724 x_msg_data => x_msg_data,
1725 x_QaPlan => l_QA_PLAN,
1726 x_QaResults => l_QA_RESULTS
1727 );
1728
1729 l_context_tbl(1).Name := 'workorder_id';
1730 l_context_tbl(1).Value := to_char(p_workorder_rec.Workorder_Id);
1731
1732 l_context_tbl(2).Name := 'object_version_no';
1733 l_context_tbl(2).Value := to_char(p_workorder_rec.Object_Version_Number);
1734
1735 results_tbl_index := 1;
1736 occurrence_tbl_index :=1;
1737 FOR i IN p_x_WO_QaResults.QA_RESULT_TBL.FIRST..p_x_WO_QaResults.QA_RESULT_TBL.LAST LOOP
1738 l_rowElementCount := 0;
1739 FOR j IN p_x_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL.FIRST..p_x_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL.LAST LOOP
1740 IF('T' = IS_VALID_RESULT_ATTRIBUTE(
1741 p_x_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).Char_Id,
1742 l_QA_PLAN))THEN
1743 l_results_tbl(results_tbl_index).char_id := p_x_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).Char_Id;
1744 l_results_tbl(results_tbl_index).result_value := p_x_WO_QaResults.QA_RESULT_TBL(i).QA_PLAN_ATRVAL_TBL(j).Attribute_Value;
1745 results_tbl_index := results_tbl_index+1;
1746 l_rowElementCount := l_rowElementCount+1;
1747 END IF;
1748 END LOOP;
1749 IF(l_rowElementCount > 0)THEN
1750 l_occurrence_tbl(occurrence_tbl_index).element_count := l_rowElementCount;
1751 occurrence_tbl_index := occurrence_tbl_index + 1;
1752 END IF;
1753 END LOOP;
1754 IF(results_tbl_index = 1)THEN
1755 RETURN; -- No attributes passed.
1756 END IF;
1757
1758 AHL_QA_RESULTS_PVT.submit_qa_results
1759 (
1760 p_api_version => 1.0,
1761 p_init_msg_list => FND_API.G_TRUE,
1762 p_commit => FND_API.G_FALSE,
1763 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1764 p_default => FND_API.G_FALSE,
1765 p_module_type => 'API',
1766 x_return_status => x_return_status,
1767 x_msg_count => x_msg_count,
1768 x_msg_data => x_msg_data,
1769 p_plan_id => l_QA_PLAN.Plan_Id,
1770 p_organization_id => l_QA_PLAN.Organization_Id,
1771 p_transaction_no => 2001,
1772 p_specification_id => NULL,
1773 p_results_tbl => l_results_tbl,
1774 p_hidden_results_tbl => l_hidden_results_tbl,
1775 p_context_tbl => l_context_tbl,
1776 p_result_commit_flag => 0,
1777 p_id_or_value => 'VALUE',
1778 p_x_collection_id => l_QA_RESULTS.Collection_Id,
1779 p_x_occurrence_tbl => l_occurrence_tbl
1780 );
1781 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
1782 RAISE FND_API.G_EXC_ERROR;
1783 END IF;
1784 p_x_WO_QaResults.Collection_Id := l_QA_RESULTS.Collection_Id;
1785 p_x_WO_QaResults.Plan_Id := l_QA_PLAN.Plan_Id;
1786 END process_wo_quality;
1787
1788 PROCEDURE process_res_txns
1789 (
1790 p_workorder_rec IN OUT NOCOPY WORKORDER_REC_TYPE,
1791 p_x_ResourceTransactions IN OUT NOCOPY RES_TXNS_TBL_TYPE,
1792 x_return_status OUT NOCOPY VARCHAR2,
1793 x_msg_count OUT NOCOPY NUMBER,
1794 x_msg_data OUT NOCOPY VARCHAR2
1795 ) IS
1796 l_res_txns_tbl AHL_PRD_RESOURCE_TRANX_PVT.PRD_RESOURCE_TXNS_TBL;
1797 j INTEGER;
1798 l_dummy_ResourceTransactions RES_TXNS_TBL_TYPE;
1799 BEGIN
1800 x_return_status := FND_API.G_RET_STS_SUCCESS;
1801 IF(p_x_ResourceTransactions IS NULL OR p_x_ResourceTransactions.COUNT < 1)THEN
1802 RETURN;
1803 END IF;
1804 x_return_status := FND_API.G_RET_STS_SUCCESS;
1805 j :=1;
1806 FOR i IN p_x_ResourceTransactions.FIRST..p_x_ResourceTransactions.LAST LOOP
1807 l_res_txns_tbl(j).WORKORDER_ID := p_workorder_rec.Workorder_Id;
1808 l_res_txns_tbl(j).WORKORDER_OPERATION_ID := p_x_ResourceTransactions(i).Workorder_Operation_Id;
1809 l_res_txns_tbl(j).OPERATION_RESOURCE_ID := p_x_ResourceTransactions(i).OPERATION_RESOURCE_ID;
1810 l_res_txns_tbl(j).ORGANIZATION_ID := p_x_ResourceTransactions(i).ORGANIZATION_ID;
1811 l_res_txns_tbl(j).DEPARTMENT_CODE := p_x_ResourceTransactions(i).DEPARTMENT_CODE;
1812 l_res_txns_tbl(j).DEPARTMENT_ID := p_x_ResourceTransactions(i).DEPARTMENT_ID;
1813 l_res_txns_tbl(j).INSTANCE_ID := p_x_ResourceTransactions(i).INSTANCE_ID;
1814 l_res_txns_tbl(j).RESOURCE_TYPE_CODE := p_x_ResourceTransactions(i).RESOURCE_TYPE_CODE;
1815 l_res_txns_tbl(j).RESOURCE_TYPE_NAME := p_x_ResourceTransactions(i).RESOURCE_TYPE_NAME;
1816 l_res_txns_tbl(j).DEPARTMENT_NAME := p_x_ResourceTransactions(i).DEPARTMENT_NAME;
1817 l_res_txns_tbl(j).OPERATION_SEQUENCE_NUM := p_x_ResourceTransactions(i).OPERATION_SEQUENCE_NUM;
1818 l_res_txns_tbl(j).RESOURCE_SEQUENCE_NUM := p_x_ResourceTransactions(i).RESOURCE_SEQUENCE_NUM;
1819 l_res_txns_tbl(j).RESOURCE_ID := p_x_ResourceTransactions(i).Resource_Id;
1820 l_res_txns_tbl(j).RESOURCE_NAME := p_x_ResourceTransactions(i).Resource_NAME;
1821 l_res_txns_tbl(j).Qty := p_x_ResourceTransactions(i).Qty;
1822 l_res_txns_tbl(j).UOM_CODE := p_x_ResourceTransactions(i).UOM_Code;
1823 l_res_txns_tbl(j).UOM_MEANING := p_x_ResourceTransactions(i).UOM_MEANING;
1824 l_res_txns_tbl(j).TRANSACTION_DATE := p_x_ResourceTransactions(i).TRANSACTION_DATE ;
1825 l_res_txns_tbl(j).END_DATE := p_x_ResourceTransactions(i).END_DATE;
1826 l_res_txns_tbl(j).DML_OPERATION := p_x_ResourceTransactions(i).DML_OPERATION;
1827 l_res_txns_tbl(j).employee_name := p_x_ResourceTransactions(i).employee_name;
1828 l_res_txns_tbl(j).employee_num := p_x_ResourceTransactions(i).employee_num;
1829 l_res_txns_tbl(j).person_id := p_x_ResourceTransactions(i).person_id;
1830 l_res_txns_tbl(j).serial_number := p_x_ResourceTransactions(i).Serial_Number;
1831 l_res_txns_tbl(j).activity_id := p_x_ResourceTransactions(i).Activity_Id;
1832 l_res_txns_tbl(j).activity_meaning := p_x_ResourceTransactions(i).activity_meaning;
1833 l_res_txns_tbl(j).reason_id := p_x_ResourceTransactions(i).Reason_Id;
1834 l_res_txns_tbl(j).reason := p_x_ResourceTransactions(i).Reason;
1835 l_res_txns_tbl(j).reference := p_x_ResourceTransactions(i).Reference;
1836 j := j+1;
1837 END LOOP;
1838 AHL_PRD_RESOURCE_TRANX_PVT.process_resource_txns
1839 (
1840 p_api_version => 1.0 ,
1841 p_init_msg_list => FND_API.G_TRUE,
1842 p_commit => FND_API.G_FALSE,
1843 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1844 p_default => FND_API.G_TRUE,
1845 p_module_type => 'API',
1846 x_return_status => x_return_status,
1847 x_msg_count => x_msg_count,
1848 x_msg_data => x_msg_data,
1849 p_x_prd_resrc_txn_tbl => l_res_txns_tbl
1850 );
1851 j :=1;
1852 p_x_ResourceTransactions := l_dummy_ResourceTransactions;
1853 FOR i IN l_res_txns_tbl.FIRST..l_res_txns_tbl.LAST LOOP
1854 p_x_ResourceTransactions(j).Workorder_Id :=l_res_txns_tbl(i).WORKORDER_ID;
1855 p_x_ResourceTransactions(j).Workorder_Operation_Id := l_res_txns_tbl(i).WORKORDER_OPERATION_ID;
1856 p_x_ResourceTransactions(j).OPERATION_RESOURCE_ID := l_res_txns_tbl(i).OPERATION_RESOURCE_ID;
1857 p_x_ResourceTransactions(j).OPERATION_SEQUENCE_NUM := l_res_txns_tbl(i).OPERATION_SEQUENCE_NUM;
1858 p_x_ResourceTransactions(j).ORGANIZATION_ID := l_res_txns_tbl(i).ORGANIZATION_ID;
1859 p_x_ResourceTransactions(j).DEPARTMENT_CODE := l_res_txns_tbl(i).DEPARTMENT_CODE;
1860 p_x_ResourceTransactions(j).DEPARTMENT_ID := l_res_txns_tbl(i).DEPARTMENT_ID;
1861 p_x_ResourceTransactions(j).INSTANCE_ID := l_res_txns_tbl(i).INSTANCE_ID;
1862 p_x_ResourceTransactions(j).RESOURCE_TYPE_CODE := l_res_txns_tbl(i).RESOURCE_TYPE_CODE;
1863 p_x_ResourceTransactions(j).RESOURCE_TYPE_NAME := l_res_txns_tbl(i).RESOURCE_TYPE_NAME;
1864 p_x_ResourceTransactions(j).DEPARTMENT_NAME := l_res_txns_tbl(i).DEPARTMENT_NAME;
1865 p_x_ResourceTransactions(j).Resource_Id := l_res_txns_tbl(i).RESOURCE_ID;
1866 p_x_ResourceTransactions(j).RESOURCE_NAME := l_res_txns_tbl(i).RESOURCE_NAME;
1867 p_x_ResourceTransactions(j).Qty := l_res_txns_tbl(i).Qty;
1868 p_x_ResourceTransactions(j).UOM_Code := l_res_txns_tbl(i).UOM_CODE;
1869 p_x_ResourceTransactions(j).UOM_MEANING := l_res_txns_tbl(i).UOM_MEANING;
1870 p_x_ResourceTransactions(j).employee_num := l_res_txns_tbl(i).employee_num;
1871 p_x_ResourceTransactions(j).person_id := l_res_txns_tbl(i).person_id;
1872 p_x_ResourceTransactions(j).Serial_Number := l_res_txns_tbl(i).serial_number;
1873 p_x_ResourceTransactions(j).Activity_Id := l_res_txns_tbl(i).activity_id;
1874 p_x_ResourceTransactions(j).activity_meaning := l_res_txns_tbl(i).activity_meaning;
1875 p_x_ResourceTransactions(j).Reason_Id := l_res_txns_tbl(i).reason_id;
1876 p_x_ResourceTransactions(j).Reason := l_res_txns_tbl(i).reason;
1877 p_x_ResourceTransactions(j).Reference := l_res_txns_tbl(i).reference;
1878 j := j+1;
1879 END LOOP;
1880
1881 END process_res_txns;
1882
1883 PROCEDURE process_res_requirements
1884 (
1885 p_workorder_rec IN WORKORDER_REC_TYPE,
1886 p_x_ResourceRequirements IN OUT NOCOPY RES_REQUIREMENTS_TBL_TYPE,
1887 x_return_status OUT NOCOPY VARCHAR2,
1888 x_msg_count OUT NOCOPY NUMBER,
1889 x_msg_data OUT NOCOPY VARCHAR2
1890 )IS
1891 l_cre_res_reqmts AHL_PP_RESRC_REQUIRE_PVT.Resrc_Require_Tbl_Type;
1892 l_upd_res_reqmts AHL_PP_RESRC_REQUIRE_PVT.Resrc_Require_Tbl_Type;
1893 l_del_res_reqmts AHL_PP_RESRC_REQUIRE_PVT.Resrc_Require_Tbl_Type;
1894 j INTEGER;
1895 k INTEGER;
1896 l INTEGER;
1897
1898 l_dummy_ResourceRequirements RES_REQUIREMENTS_TBL_TYPE;
1899
1900 BEGIN
1901 x_return_status := FND_API.G_RET_STS_SUCCESS;
1902 IF(p_x_ResourceRequirements IS NULL OR p_x_ResourceRequirements.COUNT < 1)THEN
1903 RETURN;
1904 END IF;
1905 j :=1;
1906 k :=1;
1907 l :=1;
1908 FOR i IN p_x_ResourceRequirements.FIRST..p_x_ResourceRequirements.LAST LOOP
1909 IF(p_x_ResourceRequirements(i).OPERATION_FLAG = 'C')THEN
1910 l_cre_res_reqmts(j).Operation_Resource_Id := p_x_ResourceRequirements(i).Operation_Resource_Id;
1911 l_cre_res_reqmts(j).Object_Version_Number := p_x_ResourceRequirements(i).Object_Version_Number;
1912 l_cre_res_reqmts(j).RESOURCE_SEQ_NUMBER := p_x_ResourceRequirements(i).Resource_Sequence_Number;
1913 l_cre_res_reqmts(j).OPERATION_SEQ_NUMBER := p_x_ResourceRequirements(i).Operation_Sequence_Number;
1914 l_cre_res_reqmts(j).Workorder_Id := p_workorder_rec.Workorder_Id;
1915 l_cre_res_reqmts(j).JOB_NUMBER := p_workorder_rec.Workorder_Number;
1916 l_cre_res_reqmts(j).Wip_Entity_Id := p_workorder_rec.Wip_Entity_Id;
1917 l_cre_res_reqmts(j).Workorder_Operation_Id := p_x_ResourceRequirements(i).Workorder_Operation_Id;
1918 l_cre_res_reqmts(j).Organization_Id := p_workorder_rec.Organization_Id;
1919 l_cre_res_reqmts(j).Department_Id := p_x_ResourceRequirements(i).Department_Id;
1920 l_cre_res_reqmts(j).Department_Name := p_x_ResourceRequirements(i).Department_Name;
1921 l_cre_res_reqmts(j).RESOURCE_TYPE_NAME := p_x_ResourceRequirements(i).Resource_Type;
1922 l_cre_res_reqmts(j).Resource_Type_Code := p_x_ResourceRequirements(i).Resource_Type_Code;
1923 l_cre_res_reqmts(j).Resource_Id := p_x_ResourceRequirements(i).Resource_Id;
1924 l_cre_res_reqmts(j).RESOURCE_NAME := p_x_ResourceRequirements(i).Resource_Code;
1925 --l_cre_res_reqmts(j).Resource_Description := p_x_ResourceRequirements(i).Resource_Description;
1926 l_cre_res_reqmts(j).Oper_Start_Date := p_x_ResourceRequirements(i).Operation_Start_Date;
1927 l_cre_res_reqmts(j).Oper_End_Date := p_x_ResourceRequirements(i).Operation_End_Date;
1928 l_cre_res_reqmts(j).Duration := p_x_ResourceRequirements(i).Duration;
1929 l_cre_res_reqmts(j).Quantity := p_x_ResourceRequirements(i).Quantity;
1930 IF(l_cre_res_reqmts(j).Duration IS NOT NULL AND l_cre_res_reqmts(j).Quantity IS NOT NULL AND
1931 l_cre_res_reqmts(j).Duration <> FND_API.G_MISS_NUM AND l_cre_res_reqmts(j).Quantity <> FND_API.G_MISS_NUM)THEN
1932 l_cre_res_reqmts(j).Duration := l_cre_res_reqmts(j).Duration * l_cre_res_reqmts(j).Quantity;
1933 END IF;
1934 l_cre_res_reqmts(j).Set_Up := p_x_ResourceRequirements(i).Set_Up;
1935 l_cre_res_reqmts(j).UOM_Code := p_x_ResourceRequirements(i).UOM_Code;
1936 l_cre_res_reqmts(j).UOM_NAME := p_x_ResourceRequirements(i).UOM;
1937 l_cre_res_reqmts(j).Cost_Basis_Code := p_x_ResourceRequirements(i).Cost_Basis_Code;
1938 l_cre_res_reqmts(j).Cost_Basis_Name := p_x_ResourceRequirements(i).Cost_Basis_Name;
1939 l_cre_res_reqmts(j).Charge_Type_Code := p_x_ResourceRequirements(i).Charge_Type_Code;
1940 l_cre_res_reqmts(j).Charge_Type_Name := p_x_ResourceRequirements(i).Charge_Type_Name;
1941 l_cre_res_reqmts(j).Scheduled_Type_Code := p_x_ResourceRequirements(i).Scheduled_Type_Code;
1942 l_cre_res_reqmts(j).Scheduled_Type_Name := p_x_ResourceRequirements(i).Scheduled_Type_Name;
1943 l_cre_res_reqmts(j).Std_Rate_Flag_Code := p_x_ResourceRequirements(i).Std_Rate_Flag_Code;
1944 l_cre_res_reqmts(j).Std_Rate_Flag_Name := p_x_ResourceRequirements(i).Std_Rate_Flag_Name;
1945 l_cre_res_reqmts(j).Total_Required := p_x_ResourceRequirements(i).Total_Required;
1946 l_cre_res_reqmts(j).Applied_Num := p_x_ResourceRequirements(i).Applied_Number;
1947 l_cre_res_reqmts(j).Open_Num := p_x_ResourceRequirements(i).Open_Number;
1948 l_cre_res_reqmts(j).Req_Start_Date := p_x_ResourceRequirements(i).Requirement_Start_Date;
1949 l_cre_res_reqmts(j).Req_End_Date := p_x_ResourceRequirements(i).Requirement_End_Date;
1950 l_cre_res_reqmts(j).LAST_UPDATE_LOGIN := p_x_ResourceRequirements(i).LAST_UPDATE_LOGIN;
1951 l_cre_res_reqmts(j).LAST_UPDATED_DATE := p_x_ResourceRequirements(i).LAST_UPDATED_DATE;
1952 l_cre_res_reqmts(j).LAST_UDDATED_BY := p_x_ResourceRequirements(i).LAST_UPDATED_BY;
1953 l_cre_res_reqmts(j).CREATION_DATE := p_x_ResourceRequirements(i).CREATION_DATE;
1954 l_cre_res_reqmts(j).CREATED_BY := p_x_ResourceRequirements(i).CREATED_BY;
1955 l_cre_res_reqmts(j).ATTRIBUTE_CATEGORY := p_x_ResourceRequirements(i).ATTRIBUTE_CATEGORY;
1956 l_cre_res_reqmts(j).ATTRIBUTE1 := p_x_ResourceRequirements(i).ATTRIBUTE1;
1957 l_cre_res_reqmts(j).ATTRIBUTE2 := p_x_ResourceRequirements(i).ATTRIBUTE2;
1958 l_cre_res_reqmts(j).ATTRIBUTE3 := p_x_ResourceRequirements(i).ATTRIBUTE3;
1959 l_cre_res_reqmts(j).ATTRIBUTE4 := p_x_ResourceRequirements(i).ATTRIBUTE4;
1960 l_cre_res_reqmts(j).ATTRIBUTE5 := p_x_ResourceRequirements(i).ATTRIBUTE5;
1961 l_cre_res_reqmts(j).ATTRIBUTE6 := p_x_ResourceRequirements(i).ATTRIBUTE6;
1962 l_cre_res_reqmts(j).ATTRIBUTE7 := p_x_ResourceRequirements(i).ATTRIBUTE7;
1963 l_cre_res_reqmts(j).ATTRIBUTE8 := p_x_ResourceRequirements(i).ATTRIBUTE8;
1964 l_cre_res_reqmts(j).ATTRIBUTE9 := p_x_ResourceRequirements(i).ATTRIBUTE9;
1965 l_cre_res_reqmts(j).ATTRIBUTE10 := p_x_ResourceRequirements(i).ATTRIBUTE10;
1966 l_cre_res_reqmts(j).ATTRIBUTE11 := p_x_ResourceRequirements(i).ATTRIBUTE11;
1967 l_cre_res_reqmts(j).ATTRIBUTE12 := p_x_ResourceRequirements(i).ATTRIBUTE12;
1968 l_cre_res_reqmts(j).ATTRIBUTE13 := p_x_ResourceRequirements(i).ATTRIBUTE13;
1969 l_cre_res_reqmts(j).ATTRIBUTE14 := p_x_ResourceRequirements(i).ATTRIBUTE14;
1970 l_cre_res_reqmts(j).ATTRIBUTE15 := p_x_ResourceRequirements(i).ATTRIBUTE15;
1971 l_cre_res_reqmts(j).OPERATION_FLAG := p_x_ResourceRequirements(i).OPERATION_FLAG;
1972 j := j+1;
1973 ELSIF(p_x_ResourceRequirements(i).OPERATION_FLAG = 'U')THEN
1974 l_upd_res_reqmts(k).Operation_Resource_Id := p_x_ResourceRequirements(i).Operation_Resource_Id;
1975 l_upd_res_reqmts(k).Object_Version_Number := p_x_ResourceRequirements(i).Object_Version_Number;
1976 l_upd_res_reqmts(k).RESOURCE_SEQ_NUMBER := p_x_ResourceRequirements(i).Resource_Sequence_Number;
1977 l_upd_res_reqmts(k).OPERATION_SEQ_NUMBER := p_x_ResourceRequirements(i).Operation_Sequence_Number;
1978 l_upd_res_reqmts(k).Workorder_Id := p_workorder_rec.Workorder_Id;
1979 l_upd_res_reqmts(k).JOB_NUMBER := p_workorder_rec.Workorder_Number;
1980 l_upd_res_reqmts(k).Wip_Entity_Id := p_workorder_rec.Wip_Entity_Id;
1981 l_upd_res_reqmts(k).Workorder_Operation_Id := p_x_ResourceRequirements(i).Workorder_Operation_Id;
1982 l_upd_res_reqmts(k).Organization_Id := p_workorder_rec.Organization_Id;
1983 l_upd_res_reqmts(k).Department_Id := p_x_ResourceRequirements(i).Department_Id;
1984 l_upd_res_reqmts(k).Department_Name := p_x_ResourceRequirements(i).Department_Name;
1985 l_upd_res_reqmts(k).RESOURCE_TYPE_NAME := p_x_ResourceRequirements(i).Resource_Type;
1986 l_upd_res_reqmts(k).Resource_Type_Code := p_x_ResourceRequirements(i).Resource_Type_Code;
1987 l_upd_res_reqmts(k).Resource_Id := p_x_ResourceRequirements(i).Resource_Id;
1988 l_upd_res_reqmts(k).Resource_Name := p_x_ResourceRequirements(i).Resource_Code;
1989 --l_upd_res_reqmts(k).Resource_Description := p_x_ResourceRequirements(i).Resource_Description;
1990 l_upd_res_reqmts(k).Oper_Start_Date := p_x_ResourceRequirements(i).Operation_Start_Date;
1991 l_upd_res_reqmts(k).Oper_End_Date := p_x_ResourceRequirements(i).Operation_End_Date;
1992 l_upd_res_reqmts(k).Duration := p_x_ResourceRequirements(i).Duration;
1993 l_upd_res_reqmts(k).Quantity := p_x_ResourceRequirements(i).Quantity;
1994 IF(l_upd_res_reqmts(k).Duration IS NOT NULL AND l_upd_res_reqmts(k).Quantity IS NOT NULL AND
1995 l_upd_res_reqmts(k).Duration <> FND_API.G_MISS_NUM AND l_upd_res_reqmts(k).Quantity <> FND_API.G_MISS_NUM)THEN
1996 l_upd_res_reqmts(k).Duration := l_upd_res_reqmts(k).Duration * l_upd_res_reqmts(k).Quantity;
1997 END IF;
1998 l_upd_res_reqmts(k).Set_Up := p_x_ResourceRequirements(i).Set_Up;
1999 l_upd_res_reqmts(k).UOM_Code := p_x_ResourceRequirements(i).UOM_Code;
2000 l_upd_res_reqmts(k).UOM_NAME := p_x_ResourceRequirements(i).UOM;
2001 l_upd_res_reqmts(k).Cost_Basis_Code := p_x_ResourceRequirements(i).Cost_Basis_Code;
2002 l_upd_res_reqmts(k).Cost_Basis_Name := p_x_ResourceRequirements(i).Cost_Basis_Name;
2003 l_upd_res_reqmts(k).Charge_Type_Code := p_x_ResourceRequirements(i).Charge_Type_Code;
2004 l_upd_res_reqmts(k).Charge_Type_Name := p_x_ResourceRequirements(i).Charge_Type_Name;
2005 l_upd_res_reqmts(k).Scheduled_Type_Code := p_x_ResourceRequirements(i).Scheduled_Type_Code;
2006 l_upd_res_reqmts(k).Scheduled_Type_Name := p_x_ResourceRequirements(i).Scheduled_Type_Name;
2007 l_upd_res_reqmts(k).Std_Rate_Flag_Code := p_x_ResourceRequirements(i).Std_Rate_Flag_Code;
2008 l_upd_res_reqmts(k).Std_Rate_Flag_Name := p_x_ResourceRequirements(i).Std_Rate_Flag_Name;
2009 l_upd_res_reqmts(k).Total_Required := p_x_ResourceRequirements(i).Total_Required;
2010 l_upd_res_reqmts(k).Applied_Num := p_x_ResourceRequirements(i).Applied_Number;
2011 l_upd_res_reqmts(k).Open_Num := p_x_ResourceRequirements(i).Open_Number;
2012 l_upd_res_reqmts(k).Req_Start_Date := p_x_ResourceRequirements(i).Requirement_Start_Date;
2013 l_upd_res_reqmts(k).Req_End_Date := p_x_ResourceRequirements(i).Requirement_End_Date;
2014 l_upd_res_reqmts(k).LAST_UPDATE_LOGIN := p_x_ResourceRequirements(i).LAST_UPDATE_LOGIN;
2015 l_upd_res_reqmts(k).LAST_UPDATED_DATE := p_x_ResourceRequirements(i).LAST_UPDATED_DATE;
2016 l_upd_res_reqmts(k).LAST_UDDATED_BY := p_x_ResourceRequirements(i).LAST_UPDATED_BY;
2017 l_upd_res_reqmts(k).CREATION_DATE := p_x_ResourceRequirements(i).CREATION_DATE;
2018 l_upd_res_reqmts(k).CREATED_BY := p_x_ResourceRequirements(i).CREATED_BY;
2019 l_upd_res_reqmts(k).ATTRIBUTE_CATEGORY := p_x_ResourceRequirements(i).ATTRIBUTE_CATEGORY;
2020 l_upd_res_reqmts(k).ATTRIBUTE1 := p_x_ResourceRequirements(i).ATTRIBUTE1;
2021 l_upd_res_reqmts(k).ATTRIBUTE2 := p_x_ResourceRequirements(i).ATTRIBUTE2;
2022 l_upd_res_reqmts(k).ATTRIBUTE3 := p_x_ResourceRequirements(i).ATTRIBUTE3;
2023 l_upd_res_reqmts(k).ATTRIBUTE4 := p_x_ResourceRequirements(i).ATTRIBUTE4;
2024 l_upd_res_reqmts(k).ATTRIBUTE5 := p_x_ResourceRequirements(i).ATTRIBUTE5;
2025 l_upd_res_reqmts(k).ATTRIBUTE6 := p_x_ResourceRequirements(i).ATTRIBUTE6;
2026 l_upd_res_reqmts(k).ATTRIBUTE7 := p_x_ResourceRequirements(i).ATTRIBUTE7;
2027 l_upd_res_reqmts(k).ATTRIBUTE8 := p_x_ResourceRequirements(i).ATTRIBUTE8;
2028 l_upd_res_reqmts(k).ATTRIBUTE9 := p_x_ResourceRequirements(i).ATTRIBUTE9;
2029 l_upd_res_reqmts(k).ATTRIBUTE10 := p_x_ResourceRequirements(i).ATTRIBUTE10;
2030 l_upd_res_reqmts(k).ATTRIBUTE11 := p_x_ResourceRequirements(i).ATTRIBUTE11;
2031 l_upd_res_reqmts(k).ATTRIBUTE12 := p_x_ResourceRequirements(i).ATTRIBUTE12;
2032 l_upd_res_reqmts(k).ATTRIBUTE13 := p_x_ResourceRequirements(i).ATTRIBUTE13;
2033 l_upd_res_reqmts(k).ATTRIBUTE14 := p_x_ResourceRequirements(i).ATTRIBUTE14;
2034 l_upd_res_reqmts(k).ATTRIBUTE15 := p_x_ResourceRequirements(i).ATTRIBUTE15;
2035 l_upd_res_reqmts(k).OPERATION_FLAG := p_x_ResourceRequirements(i).OPERATION_FLAG;
2036 k := k+1;
2037 ELSIF(p_x_ResourceRequirements(i).OPERATION_FLAG = 'D')THEN
2038 l_del_res_reqmts(l).Operation_Resource_Id := p_x_ResourceRequirements(i).Operation_Resource_Id;
2039 l_del_res_reqmts(l).Object_Version_Number := p_x_ResourceRequirements(i).Object_Version_Number;
2040 l_del_res_reqmts(l).RESOURCE_SEQ_NUMBER := p_x_ResourceRequirements(i).Resource_Sequence_Number;
2041 l_del_res_reqmts(l).OPERATION_SEQ_NUMBER := p_x_ResourceRequirements(i).Operation_Sequence_Number;
2042 l_del_res_reqmts(l).Workorder_Id := p_workorder_rec.Workorder_Id;
2043 l_del_res_reqmts(l).JOB_NUMBER := p_workorder_rec.Workorder_Number;
2044 l_del_res_reqmts(l).Wip_Entity_Id := p_workorder_rec.Wip_Entity_Id;
2045 l_del_res_reqmts(l).Workorder_Operation_Id := p_x_ResourceRequirements(i).Workorder_Operation_Id;
2046 l_del_res_reqmts(l).Organization_Id := p_workorder_rec.Organization_Id;
2047 l_del_res_reqmts(l).Department_Id := p_x_ResourceRequirements(i).Department_Id;
2048 l_del_res_reqmts(l).Department_Name := p_x_ResourceRequirements(i).Department_Name;
2049 l_del_res_reqmts(l).RESOURCE_TYPE_NAME := p_x_ResourceRequirements(i).Resource_Type;
2050 l_del_res_reqmts(l).Resource_Type_Code := p_x_ResourceRequirements(i).Resource_Type_Code;
2051 l_del_res_reqmts(l).Resource_Id := p_x_ResourceRequirements(i).Resource_Id;
2052 l_del_res_reqmts(l).Resource_Name := p_x_ResourceRequirements(i).Resource_Code;
2053 l_del_res_reqmts(l).OPERATION_FLAG := p_x_ResourceRequirements(i).OPERATION_FLAG;
2054 l := l+1;
2055 END IF;
2056 END LOOP;
2057 j := 1;
2058
2059 p_x_ResourceRequirements := l_dummy_ResourceRequirements;--initialize
2060 IF(l_del_res_reqmts IS NOT NULL AND l_del_res_reqmts.COUNT > 0)THEN
2061 AHL_PP_RESRC_REQUIRE_PVT.process_resrc_require(
2062 p_api_version => 1.0,
2063 p_init_msg_list => FND_API.G_TRUE,
2064 p_commit => FND_API.G_FALSE,
2065 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2066 p_module_type => 'API',
2067 p_operation_flag => 'D',
2068 p_interface_flag => 'Y',
2069 p_x_resrc_require_tbl => l_del_res_reqmts,
2070 x_return_status => x_return_status,
2071 x_msg_count => x_msg_count,
2072 x_msg_data => x_msg_data);
2073 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2074 RAISE FND_API.G_EXC_ERROR;
2075 END IF;
2076 END IF;
2077 IF(l_upd_res_reqmts IS NOT NULL AND l_upd_res_reqmts.COUNT > 0)THEN
2078 AHL_PP_RESRC_REQUIRE_PVT.process_resrc_require(
2079 p_api_version => 1.0,
2080 p_init_msg_list => FND_API.G_TRUE,
2081 p_commit => FND_API.G_FALSE,
2082 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2083 p_module_type => 'API',
2084 p_operation_flag => 'U',
2085 p_interface_flag => 'Y',
2086 p_x_resrc_require_tbl => l_upd_res_reqmts,
2087 x_return_status => x_return_status,
2088 x_msg_count => x_msg_count,
2089 x_msg_data => x_msg_data);
2090 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2091 RAISE FND_API.G_EXC_ERROR;
2092 END IF;
2093 FOR i IN l_upd_res_reqmts.FIRST..l_upd_res_reqmts.LAST LOOP
2094 p_x_ResourceRequirements(j).Operation_Resource_Id := l_upd_res_reqmts(i).Operation_Resource_Id;
2095 p_x_ResourceRequirements(j).Object_Version_Number := l_upd_res_reqmts(i).Object_Version_Number;
2096 p_x_ResourceRequirements(j).Resource_Sequence_Number :=l_upd_res_reqmts(i).RESOURCE_SEQ_NUMBER;
2097 p_x_ResourceRequirements(j).Operation_Sequence_Number := l_upd_res_reqmts(i).OPERATION_SEQ_NUMBER;
2098 p_x_ResourceRequirements(j).Workorder_Id := l_upd_res_reqmts(i).Workorder_Id;
2099 p_x_ResourceRequirements(j).Workorder_Number := l_upd_res_reqmts(i).JOB_NUMBER;
2100 p_x_ResourceRequirements(j).Wip_Entity_Id := l_upd_res_reqmts(i).Wip_Entity_Id;
2101 p_x_ResourceRequirements(j).Workorder_Operation_Id := l_upd_res_reqmts(i).Workorder_Operation_Id;
2102 p_x_ResourceRequirements(j).Organization_Id := l_upd_res_reqmts(i).Organization_Id;
2103 p_x_ResourceRequirements(j).Department_Id := l_upd_res_reqmts(i).Department_Id;
2104 p_x_ResourceRequirements(j).Department_Name := l_upd_res_reqmts(i).Department_Name;
2105 p_x_ResourceRequirements(j).Resource_Type := l_upd_res_reqmts(i).RESOURCE_TYPE_NAME;
2106 p_x_ResourceRequirements(j).Resource_Type_Code := l_upd_res_reqmts(i).Resource_Type_Code;
2107 p_x_ResourceRequirements(j).Resource_Id := l_upd_res_reqmts(i).Resource_Id;
2108 p_x_ResourceRequirements(j).Resource_Code := l_upd_res_reqmts(i).Resource_Name;
2109 --p_x_ResourceRequirements(j).Resource_Description := l_upd_res_reqmts(i).Resource_Description;
2110 p_x_ResourceRequirements(j).Operation_Start_Date := l_upd_res_reqmts(i).Oper_Start_Date;
2111 p_x_ResourceRequirements(j).Operation_End_Date := l_upd_res_reqmts(i).Oper_End_Date;
2112 p_x_ResourceRequirements(j).Duration := l_upd_res_reqmts(i).Duration;
2113 p_x_ResourceRequirements(j).Quantity := l_upd_res_reqmts(i).Quantity;
2114 p_x_ResourceRequirements(j).Set_Up := l_upd_res_reqmts(i).Set_Up;
2115 p_x_ResourceRequirements(j).UOM_Code := l_upd_res_reqmts(i).UOM_Code;
2116 p_x_ResourceRequirements(j).UOM := l_upd_res_reqmts(i).UOM_NAME;
2117 p_x_ResourceRequirements(j).Cost_Basis_Code := l_upd_res_reqmts(i).Cost_Basis_Code;
2118 p_x_ResourceRequirements(j).Cost_Basis_Name := l_upd_res_reqmts(i).Cost_Basis_Name;
2119 p_x_ResourceRequirements(j).Charge_Type_Code := l_upd_res_reqmts(i).Charge_Type_Code;
2120 p_x_ResourceRequirements(j).Charge_Type_Name := l_upd_res_reqmts(i).Charge_Type_Name;
2121 p_x_ResourceRequirements(j).Scheduled_Type_Code := l_upd_res_reqmts(i).Scheduled_Type_Code;
2122 p_x_ResourceRequirements(j).Scheduled_Type_Name := l_upd_res_reqmts(i).Scheduled_Type_Name;
2123 p_x_ResourceRequirements(j).Std_Rate_Flag_Code := l_upd_res_reqmts(i).Std_Rate_Flag_Code;
2124 p_x_ResourceRequirements(j).Std_Rate_Flag_Name := l_upd_res_reqmts(i).Std_Rate_Flag_Name;
2125 p_x_ResourceRequirements(j).Total_Required := l_upd_res_reqmts(i).Total_Required;
2126 p_x_ResourceRequirements(j).Applied_Number := l_upd_res_reqmts(i).Applied_Num;
2127 p_x_ResourceRequirements(j).Open_Number := l_upd_res_reqmts(i).Open_Num;
2128 p_x_ResourceRequirements(j).Requirement_Start_Date := l_upd_res_reqmts(i).Req_Start_Date;
2129 p_x_ResourceRequirements(j).Requirement_End_Date := l_upd_res_reqmts(i).Req_End_Date;
2130 p_x_ResourceRequirements(j).LAST_UPDATE_LOGIN := l_upd_res_reqmts(i).LAST_UPDATE_LOGIN;
2131 p_x_ResourceRequirements(j).LAST_UPDATED_DATE := l_upd_res_reqmts(i).LAST_UPDATED_DATE;
2132 p_x_ResourceRequirements(j).LAST_UPDATED_BY := l_upd_res_reqmts(i).LAST_UDDATED_BY;
2133 p_x_ResourceRequirements(j).CREATION_DATE := l_upd_res_reqmts(i).CREATION_DATE;
2134 p_x_ResourceRequirements(j).CREATED_BY := l_upd_res_reqmts(i).CREATED_BY;
2135 p_x_ResourceRequirements(j).ATTRIBUTE_CATEGORY := l_upd_res_reqmts(i).ATTRIBUTE_CATEGORY;
2136 p_x_ResourceRequirements(j).ATTRIBUTE1 := l_upd_res_reqmts(i).ATTRIBUTE1;
2137 p_x_ResourceRequirements(j).ATTRIBUTE2 := l_upd_res_reqmts(i).ATTRIBUTE2;
2138 p_x_ResourceRequirements(j).ATTRIBUTE3 := l_upd_res_reqmts(i).ATTRIBUTE3;
2139 p_x_ResourceRequirements(j).ATTRIBUTE4 := l_upd_res_reqmts(i).ATTRIBUTE4;
2140 p_x_ResourceRequirements(j).ATTRIBUTE5 := l_upd_res_reqmts(i).ATTRIBUTE5;
2141 p_x_ResourceRequirements(j).ATTRIBUTE6 := l_upd_res_reqmts(i).ATTRIBUTE6;
2142 p_x_ResourceRequirements(j).ATTRIBUTE7 := l_upd_res_reqmts(i).ATTRIBUTE7;
2143 p_x_ResourceRequirements(j).ATTRIBUTE8 := l_upd_res_reqmts(i).ATTRIBUTE8;
2144 p_x_ResourceRequirements(j).ATTRIBUTE9 := l_upd_res_reqmts(i).ATTRIBUTE9;
2145 p_x_ResourceRequirements(j).ATTRIBUTE10 := l_upd_res_reqmts(i).ATTRIBUTE10;
2146 p_x_ResourceRequirements(j).ATTRIBUTE11 := l_upd_res_reqmts(i).ATTRIBUTE11;
2147 p_x_ResourceRequirements(j).ATTRIBUTE12 := l_upd_res_reqmts(i).ATTRIBUTE12;
2148 p_x_ResourceRequirements(j).ATTRIBUTE13 := l_upd_res_reqmts(i).ATTRIBUTE13;
2149 p_x_ResourceRequirements(j).ATTRIBUTE14 := l_upd_res_reqmts(i).ATTRIBUTE14;
2150 p_x_ResourceRequirements(j).ATTRIBUTE15 := l_upd_res_reqmts(i).ATTRIBUTE15;
2151 p_x_ResourceRequirements(j).OPERATION_FLAG := l_upd_res_reqmts(i).OPERATION_FLAG;
2152 j := j+1;
2153 END LOOP;
2154 END IF;
2155 IF(l_cre_res_reqmts IS NOT NULL AND l_cre_res_reqmts.COUNT >0)THEN
2156 AHL_PP_RESRC_REQUIRE_PVT.process_resrc_require(
2157 p_api_version => 1.0,
2158 p_init_msg_list => FND_API.G_TRUE,
2159 p_commit => FND_API.G_FALSE,
2160 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2161 p_module_type => 'API',
2162 p_operation_flag => 'C',
2163 p_interface_flag => 'Y',
2164 p_x_resrc_require_tbl => l_cre_res_reqmts,
2165 x_return_status => x_return_status,
2166 x_msg_count => x_msg_count,
2167 x_msg_data => x_msg_data);
2168 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2169 RAISE FND_API.G_EXC_ERROR;
2170 END IF;
2171 FOR i IN l_cre_res_reqmts.FIRST..l_cre_res_reqmts.LAST LOOP
2172 p_x_ResourceRequirements(j).Operation_Resource_Id := l_cre_res_reqmts(i).Operation_Resource_Id;
2173 p_x_ResourceRequirements(j).Object_Version_Number := l_cre_res_reqmts(i).Object_Version_Number;
2174 p_x_ResourceRequirements(j).Resource_Sequence_Number :=l_cre_res_reqmts(i).RESOURCE_SEQ_NUMBER;
2175 p_x_ResourceRequirements(j).Operation_Sequence_Number := l_cre_res_reqmts(i).OPERATION_SEQ_NUMBER;
2176 p_x_ResourceRequirements(j).Workorder_Id := l_cre_res_reqmts(i).Workorder_Id;
2177 p_x_ResourceRequirements(j).Workorder_Number := l_cre_res_reqmts(i).JOB_NUMBER;
2178 p_x_ResourceRequirements(j).Wip_Entity_Id := l_cre_res_reqmts(i).Wip_Entity_Id;
2179 p_x_ResourceRequirements(j).Workorder_Operation_Id := l_cre_res_reqmts(i).Workorder_Operation_Id;
2180 p_x_ResourceRequirements(j).Organization_Id := l_cre_res_reqmts(i).Organization_Id;
2181 p_x_ResourceRequirements(j).Department_Id := l_cre_res_reqmts(i).Department_Id;
2182 p_x_ResourceRequirements(j).Department_Name := l_cre_res_reqmts(i).Department_Name;
2183 p_x_ResourceRequirements(j).Resource_Type := l_cre_res_reqmts(i).RESOURCE_TYPE_NAME;
2184 p_x_ResourceRequirements(j).Resource_Type_Code := l_cre_res_reqmts(i).Resource_Type_Code;
2185 p_x_ResourceRequirements(j).Resource_Id := l_cre_res_reqmts(i).Resource_Id;
2186 p_x_ResourceRequirements(j).Resource_Code := l_cre_res_reqmts(i).Resource_Name;
2187 --p_x_ResourceRequirements(j).Resource_Description := l_cre_res_reqmts(i).Resource_Description;
2188 p_x_ResourceRequirements(j).Operation_Start_Date := l_cre_res_reqmts(i).Oper_Start_Date;
2189 p_x_ResourceRequirements(j).Operation_End_Date := l_cre_res_reqmts(i).Oper_End_Date;
2190 p_x_ResourceRequirements(j).Duration := l_cre_res_reqmts(i).Duration;
2191 p_x_ResourceRequirements(j).Quantity := l_cre_res_reqmts(i).Quantity;
2192 p_x_ResourceRequirements(j).Set_Up := l_cre_res_reqmts(i).Set_Up;
2193 p_x_ResourceRequirements(j).UOM_Code := l_cre_res_reqmts(i).UOM_Code;
2194 p_x_ResourceRequirements(j).UOM := l_cre_res_reqmts(i).UOM_NAME;
2195 p_x_ResourceRequirements(j).Cost_Basis_Code := l_cre_res_reqmts(i).Cost_Basis_Code;
2196 p_x_ResourceRequirements(j).Cost_Basis_Name := l_cre_res_reqmts(i).Cost_Basis_Name;
2197 p_x_ResourceRequirements(j).Charge_Type_Code := l_cre_res_reqmts(i).Charge_Type_Code;
2198 p_x_ResourceRequirements(j).Charge_Type_Name := l_cre_res_reqmts(i).Charge_Type_Name;
2199 p_x_ResourceRequirements(j).Scheduled_Type_Code := l_cre_res_reqmts(i).Scheduled_Type_Code;
2200 p_x_ResourceRequirements(j).Scheduled_Type_Name := l_cre_res_reqmts(i).Scheduled_Type_Name;
2201 p_x_ResourceRequirements(j).Std_Rate_Flag_Code := l_cre_res_reqmts(i).Std_Rate_Flag_Code;
2202 p_x_ResourceRequirements(j).Std_Rate_Flag_Name := l_cre_res_reqmts(i).Std_Rate_Flag_Name;
2203 p_x_ResourceRequirements(j).Total_Required := l_cre_res_reqmts(i).Total_Required;
2204 p_x_ResourceRequirements(j).Applied_Number := l_cre_res_reqmts(i).Applied_Num;
2205 p_x_ResourceRequirements(j).Open_Number := l_cre_res_reqmts(i).Open_Num;
2206 p_x_ResourceRequirements(j).Requirement_Start_Date := l_cre_res_reqmts(i).Req_Start_Date;
2207 p_x_ResourceRequirements(j).Requirement_End_Date := l_cre_res_reqmts(i).Req_End_Date;
2208 p_x_ResourceRequirements(j).LAST_UPDATE_LOGIN := l_cre_res_reqmts(i).LAST_UPDATE_LOGIN;
2209 p_x_ResourceRequirements(j).LAST_UPDATED_DATE := l_cre_res_reqmts(i).LAST_UPDATED_DATE;
2210 p_x_ResourceRequirements(j).LAST_UPDATED_BY := l_cre_res_reqmts(i).LAST_UDDATED_BY;
2211 p_x_ResourceRequirements(j).CREATION_DATE := l_cre_res_reqmts(i).CREATION_DATE;
2212 p_x_ResourceRequirements(j).CREATED_BY := l_cre_res_reqmts(i).CREATED_BY;
2213 p_x_ResourceRequirements(j).ATTRIBUTE_CATEGORY := l_cre_res_reqmts(i).ATTRIBUTE_CATEGORY;
2214 p_x_ResourceRequirements(j).ATTRIBUTE1 := l_cre_res_reqmts(i).ATTRIBUTE1;
2215 p_x_ResourceRequirements(j).ATTRIBUTE2 := l_cre_res_reqmts(i).ATTRIBUTE2;
2216 p_x_ResourceRequirements(j).ATTRIBUTE3 := l_cre_res_reqmts(i).ATTRIBUTE3;
2217 p_x_ResourceRequirements(j).ATTRIBUTE4 := l_cre_res_reqmts(i).ATTRIBUTE4;
2218 p_x_ResourceRequirements(j).ATTRIBUTE5 := l_cre_res_reqmts(i).ATTRIBUTE5;
2219 p_x_ResourceRequirements(j).ATTRIBUTE6 := l_cre_res_reqmts(i).ATTRIBUTE6;
2220 p_x_ResourceRequirements(j).ATTRIBUTE7 := l_cre_res_reqmts(i).ATTRIBUTE7;
2221 p_x_ResourceRequirements(j).ATTRIBUTE8 := l_cre_res_reqmts(i).ATTRIBUTE8;
2222 p_x_ResourceRequirements(j).ATTRIBUTE9 := l_cre_res_reqmts(i).ATTRIBUTE9;
2223 p_x_ResourceRequirements(j).ATTRIBUTE10 := l_cre_res_reqmts(i).ATTRIBUTE10;
2224 p_x_ResourceRequirements(j).ATTRIBUTE11 := l_cre_res_reqmts(i).ATTRIBUTE11;
2225 p_x_ResourceRequirements(j).ATTRIBUTE12 := l_cre_res_reqmts(i).ATTRIBUTE12;
2226 p_x_ResourceRequirements(j).ATTRIBUTE13 := l_cre_res_reqmts(i).ATTRIBUTE13;
2227 p_x_ResourceRequirements(j).ATTRIBUTE14 := l_cre_res_reqmts(i).ATTRIBUTE14;
2228 p_x_ResourceRequirements(j).ATTRIBUTE15 := l_cre_res_reqmts(i).ATTRIBUTE15;
2229 p_x_ResourceRequirements(j).OPERATION_FLAG := l_cre_res_reqmts(i).OPERATION_FLAG;
2230 j := j+1;
2231 END LOOP;
2232 END IF;
2233
2234 END process_res_requirements;
2235
2236 PROCEDURE process_res_assignments
2237 (
2238 p_workorder_rec IN WORKORDER_REC_TYPE,
2239 p_x_ResourceAssignments IN OUT NOCOPY RES_ASSIGNMENTS_TBL_TYPE,
2240 x_return_status OUT NOCOPY VARCHAR2,
2241 x_msg_count OUT NOCOPY NUMBER,
2242 x_msg_data OUT NOCOPY VARCHAR2
2243 )IS
2244 l_cre_res_assmts AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type;
2245 l_upd_res_assmts AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type;
2246 l_del_res_assmts AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type;
2247 j INTEGER;
2248 k INTEGER;
2249 l INTEGER;
2250
2251 l_dummy_ResourceAssignments RES_ASSIGNMENTS_TBL_TYPE;
2252
2253 BEGIN
2254 x_return_status := FND_API.G_RET_STS_SUCCESS;
2255 IF(p_x_ResourceAssignments IS NULL OR p_x_ResourceAssignments.COUNT < 1)THEN
2256 RETURN;
2257 END IF;
2258 j :=1;
2259 k :=1;
2260 l :=1;
2261 FOR i IN p_x_ResourceAssignments.FIRST..p_x_ResourceAssignments.LAST LOOP
2262 IF(p_x_ResourceAssignments(i).OPERATION_FLAG = 'C')THEN
2263 l_cre_res_assmts(j).ASSIGNMENT_ID := p_x_ResourceAssignments(i).Assignment_Id;
2264 l_cre_res_assmts(j).OBJECT_VERSION_NUMBER := p_x_ResourceAssignments(i).Object_Version_Number;
2265 l_cre_res_assmts(j).WORKORDER_ID := p_workorder_rec.Workorder_Id;
2266 l_cre_res_assmts(j).WORKORDER_OPERATION_ID := p_x_ResourceAssignments(i).Workorder_Operation_Id;
2267 l_cre_res_assmts(j).WIP_ENTITY_ID := p_workorder_rec.Wip_Entity_Id;
2268 l_cre_res_assmts(j).ORGANIZATION_ID := p_workorder_rec.Organization_Id;
2269 l_cre_res_assmts(j).OPERATION_SEQ_NUMBER := p_x_ResourceAssignments(i).Operation_Sequence_Number;
2270 l_cre_res_assmts(j).RESOURCE_SEQ_NUMBER := p_x_ResourceAssignments(i).Resource_Sequence_Number;
2271 l_cre_res_assmts(j).RESOURCE_TYPE_CODE := p_x_ResourceAssignments(i).Resource_Type_Code;
2272 l_cre_res_assmts(j).RESOURCE_TYPE_NAME := p_x_ResourceAssignments(i).Resource_Type;
2273 l_cre_res_assmts(j).OPER_RESOURCE_ID := p_x_ResourceAssignments(i).Operation_Resource_Id;
2274 l_cre_res_assmts(j).DEPARTMENT_ID := p_x_ResourceAssignments(i).Department_Id;
2275 l_cre_res_assmts(j).EMPLOYEE_ID := p_x_ResourceAssignments(i).Employee_Id;
2276 l_cre_res_assmts(j).EMPLOYEE_NUMBER := p_x_ResourceAssignments(i).Employee_Number;
2277 l_cre_res_assmts(j).EMPLOYEE_NAME := p_x_ResourceAssignments(i).Employee_Name;
2278 l_cre_res_assmts(j).INVENTORY_ITEM_ID := p_x_ResourceAssignments(i).Inventory_Item_Id;
2279 l_cre_res_assmts(j).ITEM_ORGANIZATION_ID := p_x_ResourceAssignments(i).Item_Organization_Id;
2280 l_cre_res_assmts(j).SERIAL_NUMBER := p_x_ResourceAssignments(i).Serial_Number;
2281 l_cre_res_assmts(j).INSTANCE_ID := p_x_ResourceAssignments(i).Instance_Id;
2282 l_cre_res_assmts(j).ASSIGN_START_DATE := p_x_ResourceAssignments(i).Assignment_Start_Date;
2283 l_cre_res_assmts(j).ASSIGN_END_DATE := p_x_ResourceAssignments(i).Assignment_End_Date;
2284 l_cre_res_assmts(j).LAST_UPDATE_LOGIN := p_x_ResourceAssignments(i).LAST_UPDATE_LOGIN;
2285 l_cre_res_assmts(j).LAST_UPDATED_DATE := p_x_ResourceAssignments(i).LAST_UPDATED_DATE;
2286 l_cre_res_assmts(j).LAST_UDDATED_BY := p_x_ResourceAssignments(i).LAST_UPDATED_BY;
2287 l_cre_res_assmts(j).CREATION_DATE := p_x_ResourceAssignments(i).CREATION_DATE;
2288 l_cre_res_assmts(j).CREATED_BY := p_x_ResourceAssignments(i).CREATED_BY;
2289 l_cre_res_assmts(j).ATTRIBUTE_CATEGORY := p_x_ResourceAssignments(i).ATTRIBUTE_CATEGORY;
2290 l_cre_res_assmts(j).ATTRIBUTE1 := p_x_ResourceAssignments(i).ATTRIBUTE1;
2291 l_cre_res_assmts(j).ATTRIBUTE2 := p_x_ResourceAssignments(i).ATTRIBUTE2;
2292 l_cre_res_assmts(j).ATTRIBUTE3 := p_x_ResourceAssignments(i).ATTRIBUTE3;
2293 l_cre_res_assmts(j).ATTRIBUTE4 := p_x_ResourceAssignments(i).ATTRIBUTE4;
2294 l_cre_res_assmts(j).ATTRIBUTE5 := p_x_ResourceAssignments(i).ATTRIBUTE5;
2295 l_cre_res_assmts(j).ATTRIBUTE6 := p_x_ResourceAssignments(i).ATTRIBUTE6;
2296 l_cre_res_assmts(j).ATTRIBUTE7 := p_x_ResourceAssignments(i).ATTRIBUTE7;
2297 l_cre_res_assmts(j).ATTRIBUTE8 := p_x_ResourceAssignments(i).ATTRIBUTE8;
2298 l_cre_res_assmts(j).ATTRIBUTE9 := p_x_ResourceAssignments(i).ATTRIBUTE9;
2299 l_cre_res_assmts(j).ATTRIBUTE10 := p_x_ResourceAssignments(i).ATTRIBUTE10;
2300 l_cre_res_assmts(j).ATTRIBUTE11 := p_x_ResourceAssignments(i).ATTRIBUTE11;
2301 l_cre_res_assmts(j).ATTRIBUTE12 := p_x_ResourceAssignments(i).ATTRIBUTE12;
2302 l_cre_res_assmts(j).ATTRIBUTE13 := p_x_ResourceAssignments(i).ATTRIBUTE13;
2303 l_cre_res_assmts(j).ATTRIBUTE14 := p_x_ResourceAssignments(i).ATTRIBUTE14;
2304 l_cre_res_assmts(j).ATTRIBUTE15 := p_x_ResourceAssignments(i).ATTRIBUTE15;
2305 l_cre_res_assmts(j).OPERATION_FLAG := p_x_ResourceAssignments(i).OPERATION_FLAG;
2306 j := j+1;
2307 ELSIF(p_x_ResourceAssignments(i).OPERATION_FLAG = 'U')THEN
2308 l_upd_res_assmts(k).ASSIGNMENT_ID := p_x_ResourceAssignments(i).Assignment_Id;
2309 l_upd_res_assmts(k).OBJECT_VERSION_NUMBER := p_x_ResourceAssignments(i).Object_Version_Number;
2310 l_upd_res_assmts(k).WORKORDER_ID := p_workorder_rec.Workorder_Id;
2311 l_upd_res_assmts(k).WORKORDER_OPERATION_ID := p_x_ResourceAssignments(i).Workorder_Operation_Id;
2312 l_upd_res_assmts(k).WIP_ENTITY_ID := p_workorder_rec.Wip_Entity_Id;
2313 l_upd_res_assmts(k).ORGANIZATION_ID := p_workorder_rec.Organization_Id;
2314 l_upd_res_assmts(k).OPERATION_SEQ_NUMBER := p_x_ResourceAssignments(i).Operation_Sequence_Number;
2315 l_upd_res_assmts(k).RESOURCE_SEQ_NUMBER := p_x_ResourceAssignments(i).Resource_Sequence_Number;
2316 l_upd_res_assmts(k).RESOURCE_TYPE_CODE := p_x_ResourceAssignments(i).Resource_Type_Code;
2317 l_upd_res_assmts(k).RESOURCE_TYPE_NAME := p_x_ResourceAssignments(i).Resource_Type;
2318 l_upd_res_assmts(k).OPER_RESOURCE_ID := p_x_ResourceAssignments(i).Operation_Resource_Id;
2319 l_upd_res_assmts(k).DEPARTMENT_ID := p_x_ResourceAssignments(i).Department_Id;
2320 l_upd_res_assmts(k).EMPLOYEE_ID := p_x_ResourceAssignments(i).Employee_Id;
2321 l_upd_res_assmts(k).EMPLOYEE_NUMBER := p_x_ResourceAssignments(i).Employee_Number;
2322 l_upd_res_assmts(k).EMPLOYEE_NAME := p_x_ResourceAssignments(i).Employee_Name;
2323 l_upd_res_assmts(k).INVENTORY_ITEM_ID := p_x_ResourceAssignments(i).Inventory_Item_Id;
2324 l_upd_res_assmts(k).ITEM_ORGANIZATION_ID := p_x_ResourceAssignments(i).Item_Organization_Id;
2325 l_upd_res_assmts(k).SERIAL_NUMBER := p_x_ResourceAssignments(i).Serial_Number;
2326 l_upd_res_assmts(k).INSTANCE_ID := p_x_ResourceAssignments(i).Instance_Id;
2327 l_upd_res_assmts(k).ASSIGN_START_DATE := p_x_ResourceAssignments(i).Assignment_Start_Date;
2328 l_upd_res_assmts(k).ASSIGN_END_DATE := p_x_ResourceAssignments(i).Assignment_End_Date;
2329 l_upd_res_assmts(k).LAST_UPDATE_LOGIN := p_x_ResourceAssignments(i).LAST_UPDATE_LOGIN;
2330 l_upd_res_assmts(k).LAST_UPDATED_DATE := p_x_ResourceAssignments(i).LAST_UPDATED_DATE;
2331 l_upd_res_assmts(k).LAST_UDDATED_BY := p_x_ResourceAssignments(i).LAST_UPDATED_BY;
2332 l_upd_res_assmts(k).CREATION_DATE := p_x_ResourceAssignments(i).CREATION_DATE;
2333 l_upd_res_assmts(k).CREATED_BY := p_x_ResourceAssignments(i).CREATED_BY;
2334 l_upd_res_assmts(k).ATTRIBUTE_CATEGORY := p_x_ResourceAssignments(i).ATTRIBUTE_CATEGORY;
2335 l_upd_res_assmts(k).ATTRIBUTE1 := p_x_ResourceAssignments(i).ATTRIBUTE1;
2336 l_upd_res_assmts(k).ATTRIBUTE2 := p_x_ResourceAssignments(i).ATTRIBUTE2;
2337 l_upd_res_assmts(k).ATTRIBUTE3 := p_x_ResourceAssignments(i).ATTRIBUTE3;
2338 l_upd_res_assmts(k).ATTRIBUTE4 := p_x_ResourceAssignments(i).ATTRIBUTE4;
2339 l_upd_res_assmts(k).ATTRIBUTE5 := p_x_ResourceAssignments(i).ATTRIBUTE5;
2340 l_upd_res_assmts(k).ATTRIBUTE6 := p_x_ResourceAssignments(i).ATTRIBUTE6;
2341 l_upd_res_assmts(k).ATTRIBUTE7 := p_x_ResourceAssignments(i).ATTRIBUTE7;
2342 l_upd_res_assmts(k).ATTRIBUTE8 := p_x_ResourceAssignments(i).ATTRIBUTE8;
2343 l_upd_res_assmts(k).ATTRIBUTE9 := p_x_ResourceAssignments(i).ATTRIBUTE9;
2344 l_upd_res_assmts(k).ATTRIBUTE10 := p_x_ResourceAssignments(i).ATTRIBUTE10;
2345 l_upd_res_assmts(k).ATTRIBUTE11 := p_x_ResourceAssignments(i).ATTRIBUTE11;
2346 l_upd_res_assmts(k).ATTRIBUTE12 := p_x_ResourceAssignments(i).ATTRIBUTE12;
2347 l_upd_res_assmts(k).ATTRIBUTE13 := p_x_ResourceAssignments(i).ATTRIBUTE13;
2348 l_upd_res_assmts(k).ATTRIBUTE14 := p_x_ResourceAssignments(i).ATTRIBUTE14;
2349 l_upd_res_assmts(k).ATTRIBUTE15 := p_x_ResourceAssignments(i).ATTRIBUTE15;
2350 l_upd_res_assmts(k).OPERATION_FLAG := p_x_ResourceAssignments(i).OPERATION_FLAG;
2351 k := k+1;
2352 ELSIF(p_x_ResourceAssignments(i).OPERATION_FLAG = 'D')THEN
2353 l_del_res_assmts(l).ASSIGNMENT_ID := p_x_ResourceAssignments(i).Assignment_Id;
2354 l_del_res_assmts(l).OBJECT_VERSION_NUMBER := p_x_ResourceAssignments(i).Object_Version_Number;
2355 l_del_res_assmts(l).WORKORDER_ID := p_workorder_rec.Workorder_Id;
2356 l_del_res_assmts(l).WORKORDER_OPERATION_ID := p_x_ResourceAssignments(i).Workorder_Operation_Id;
2357 l_del_res_assmts(l).WIP_ENTITY_ID := p_workorder_rec.Wip_Entity_Id;
2358 l_del_res_assmts(l).ORGANIZATION_ID := p_workorder_rec.Organization_Id;
2359 l_del_res_assmts(l).OPERATION_SEQ_NUMBER := p_x_ResourceAssignments(i).Operation_Sequence_Number;
2360 l_del_res_assmts(l).RESOURCE_SEQ_NUMBER := p_x_ResourceAssignments(i).Resource_Sequence_Number;
2361 l_del_res_assmts(l).RESOURCE_TYPE_CODE := p_x_ResourceAssignments(i).Resource_Type_Code;
2362 l_del_res_assmts(l).RESOURCE_TYPE_NAME := p_x_ResourceAssignments(i).Resource_Type;
2363 l_del_res_assmts(l).OPER_RESOURCE_ID := p_x_ResourceAssignments(i).Operation_Resource_Id;
2364 l_del_res_assmts(l).OPERATION_FLAG := p_x_ResourceAssignments(i).OPERATION_FLAG;
2365 l := l+1;
2366 END IF;
2367 END LOOP;
2368 j := 1;
2369 p_x_ResourceAssignments := l_dummy_ResourceAssignments;--initialize;
2370 IF(l_del_res_assmts IS NOT NULL AND l_del_res_assmts.COUNT >0)THEN
2371 AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign (
2372 p_api_version => 1.0 ,
2373 p_init_msg_list => FND_API.G_TRUE,
2374 p_commit => FND_API.G_FALSE,
2375 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2376 p_module_type => 'API',
2377 p_operation_flag => 'D',
2378 p_x_resrc_assign_tbl => l_del_res_assmts,
2379 x_return_status => x_return_status,
2380 x_msg_count => x_msg_count,
2381 x_msg_data => x_msg_data
2382 );
2383 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2384 RAISE FND_API.G_EXC_ERROR;
2385 END IF;
2386 END IF;
2387 IF(l_upd_res_assmts IS NOT NULL AND l_upd_res_assmts.COUNT >0)THEN
2388 AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign (
2389 p_api_version => 1.0 ,
2390 p_init_msg_list => FND_API.G_TRUE,
2391 p_commit => FND_API.G_FALSE,
2392 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2393 p_module_type => 'API',
2394 p_operation_flag => 'U',
2395 p_x_resrc_assign_tbl => l_upd_res_assmts,
2396 x_return_status => x_return_status,
2397 x_msg_count => x_msg_count,
2398 x_msg_data => x_msg_data
2399 );
2400 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2401 RAISE FND_API.G_EXC_ERROR;
2402 END IF;
2403 FOR i IN l_upd_res_assmts.FIRST..l_upd_res_assmts.LAST LOOP
2404 p_x_ResourceAssignments(j).Assignment_Id := l_upd_res_assmts(i).ASSIGNMENT_ID;
2405 p_x_ResourceAssignments(j).Object_Version_Number := l_upd_res_assmts(i).OBJECT_VERSION_NUMBER;
2406 p_x_ResourceAssignments(j).Workorder_Id := l_upd_res_assmts(i).WORKORDER_ID;
2407 p_x_ResourceAssignments(j).Workorder_Operation_Id := l_upd_res_assmts(i).WORKORDER_OPERATION_ID;
2408 p_x_ResourceAssignments(j).Wip_Entity_Id := l_upd_res_assmts(i).WIP_ENTITY_ID;
2409 p_x_ResourceAssignments(j).Organization_Id := l_upd_res_assmts(i).ORGANIZATION_ID;
2410 p_x_ResourceAssignments(j).Operation_Sequence_Number := l_upd_res_assmts(i).OPERATION_SEQ_NUMBER;
2411 p_x_ResourceAssignments(j).Resource_Sequence_Number := l_upd_res_assmts(i).RESOURCE_SEQ_NUMBER;
2412 p_x_ResourceAssignments(j).Resource_Type_Code := l_upd_res_assmts(i).RESOURCE_TYPE_CODE;
2413 p_x_ResourceAssignments(j).Resource_Type := l_upd_res_assmts(i).RESOURCE_TYPE_NAME;
2414 p_x_ResourceAssignments(j).Operation_Resource_Id := l_upd_res_assmts(i).OPER_RESOURCE_ID;
2415 p_x_ResourceAssignments(j).Department_Id := l_upd_res_assmts(i).DEPARTMENT_ID;
2416 p_x_ResourceAssignments(j).Employee_Id := l_upd_res_assmts(i).EMPLOYEE_ID;
2417 p_x_ResourceAssignments(j).Employee_Number := l_upd_res_assmts(i).EMPLOYEE_NUMBER;
2418 p_x_ResourceAssignments(j).Employee_Name := l_upd_res_assmts(i).EMPLOYEE_NAME;
2419 p_x_ResourceAssignments(j).Inventory_Item_Id := l_upd_res_assmts(i).INVENTORY_ITEM_ID;
2420 p_x_ResourceAssignments(j).Item_Organization_Id := l_upd_res_assmts(i).ITEM_ORGANIZATION_ID;
2421 p_x_ResourceAssignments(j).Serial_Number := l_upd_res_assmts(i).SERIAL_NUMBER;
2422 p_x_ResourceAssignments(j).Instance_Id := l_upd_res_assmts(i).INSTANCE_ID;
2423 p_x_ResourceAssignments(j).Assignment_Start_Date := l_upd_res_assmts(i).ASSIGN_START_DATE;
2424 p_x_ResourceAssignments(j).Assignment_End_Date := l_upd_res_assmts(i).ASSIGN_END_DATE;
2425 p_x_ResourceAssignments(j).LAST_UPDATE_LOGIN := l_upd_res_assmts(i).LAST_UPDATE_LOGIN;
2426 p_x_ResourceAssignments(j).LAST_UPDATED_DATE := l_upd_res_assmts(i).LAST_UPDATED_DATE;
2427 p_x_ResourceAssignments(j).LAST_UPDATED_BY := l_upd_res_assmts(i).LAST_UDDATED_BY;
2428 p_x_ResourceAssignments(j).CREATION_DATE := l_upd_res_assmts(i).CREATION_DATE;
2429 p_x_ResourceAssignments(j).CREATED_BY := l_upd_res_assmts(i).CREATED_BY;
2430 p_x_ResourceAssignments(j).ATTRIBUTE_CATEGORY := l_upd_res_assmts(i).ATTRIBUTE_CATEGORY;
2431 p_x_ResourceAssignments(j).ATTRIBUTE1 := l_upd_res_assmts(i).ATTRIBUTE1;
2432 p_x_ResourceAssignments(j).ATTRIBUTE2 := l_upd_res_assmts(i).ATTRIBUTE2;
2433 p_x_ResourceAssignments(j).ATTRIBUTE3 := l_upd_res_assmts(i).ATTRIBUTE3;
2434 p_x_ResourceAssignments(j).ATTRIBUTE4 := l_upd_res_assmts(i).ATTRIBUTE4;
2435 p_x_ResourceAssignments(j).ATTRIBUTE5 := l_upd_res_assmts(i).ATTRIBUTE5;
2436 p_x_ResourceAssignments(j).ATTRIBUTE6 := l_upd_res_assmts(i).ATTRIBUTE6;
2437 p_x_ResourceAssignments(j).ATTRIBUTE7 := l_upd_res_assmts(i).ATTRIBUTE7;
2438 p_x_ResourceAssignments(j).ATTRIBUTE8 := l_upd_res_assmts(i).ATTRIBUTE8;
2439 p_x_ResourceAssignments(j).ATTRIBUTE9 := l_upd_res_assmts(i).ATTRIBUTE9;
2440 p_x_ResourceAssignments(j).ATTRIBUTE10 := l_upd_res_assmts(i).ATTRIBUTE10;
2441 p_x_ResourceAssignments(j).ATTRIBUTE11 := l_upd_res_assmts(i).ATTRIBUTE11;
2442 p_x_ResourceAssignments(j).ATTRIBUTE12 := l_upd_res_assmts(i).ATTRIBUTE12;
2443 p_x_ResourceAssignments(j).ATTRIBUTE13 := l_upd_res_assmts(i).ATTRIBUTE13;
2444 p_x_ResourceAssignments(j).ATTRIBUTE14 := l_upd_res_assmts(i).ATTRIBUTE14;
2445 p_x_ResourceAssignments(j).ATTRIBUTE15 := l_upd_res_assmts(i).ATTRIBUTE15;
2446 p_x_ResourceAssignments(j).OPERATION_FLAG := l_upd_res_assmts(i).OPERATION_FLAG;
2447 j:= j+1;
2448 END LOOP;
2449 END IF;
2450 IF(l_cre_res_assmts IS NOT NULL AND l_cre_res_assmts.COUNT >0)THEN
2451 AHL_PP_RESRC_ASSIGN_PVT.Process_Resrc_Assign (
2452 p_api_version => 1.0 ,
2453 p_init_msg_list => FND_API.G_TRUE,
2454 p_commit => FND_API.G_FALSE,
2455 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2456 p_module_type => 'API',
2457 p_operation_flag => 'C',
2458 p_x_resrc_assign_tbl => l_cre_res_assmts,
2459 x_return_status => x_return_status,
2460 x_msg_count => x_msg_count,
2461 x_msg_data => x_msg_data
2462 );
2463 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2464 RAISE FND_API.G_EXC_ERROR;
2465 END IF;
2466 FOR i IN l_cre_res_assmts.FIRST..l_cre_res_assmts.LAST LOOP
2467 p_x_ResourceAssignments(j).Assignment_Id := l_cre_res_assmts(i).ASSIGNMENT_ID;
2468 p_x_ResourceAssignments(j).Object_Version_Number := l_cre_res_assmts(i).OBJECT_VERSION_NUMBER;
2469 p_x_ResourceAssignments(j).Workorder_Id := l_cre_res_assmts(i).WORKORDER_ID;
2470 p_x_ResourceAssignments(j).Workorder_Operation_Id := l_cre_res_assmts(i).WORKORDER_OPERATION_ID;
2471 p_x_ResourceAssignments(j).Wip_Entity_Id := l_cre_res_assmts(i).WIP_ENTITY_ID;
2472 p_x_ResourceAssignments(j).Organization_Id := l_cre_res_assmts(i).ORGANIZATION_ID;
2473 p_x_ResourceAssignments(j).Operation_Sequence_Number := l_cre_res_assmts(i).OPERATION_SEQ_NUMBER;
2474 p_x_ResourceAssignments(j).Resource_Sequence_Number := l_cre_res_assmts(i).RESOURCE_SEQ_NUMBER;
2475 p_x_ResourceAssignments(j).Resource_Type_Code := l_cre_res_assmts(i).RESOURCE_TYPE_CODE;
2476 p_x_ResourceAssignments(j).Resource_Type := l_cre_res_assmts(i).RESOURCE_TYPE_NAME;
2477 p_x_ResourceAssignments(j).Operation_Resource_Id := l_cre_res_assmts(i).OPER_RESOURCE_ID;
2478 p_x_ResourceAssignments(j).Department_Id := l_cre_res_assmts(i).DEPARTMENT_ID;
2479 p_x_ResourceAssignments(j).Employee_Id := l_cre_res_assmts(i).EMPLOYEE_ID;
2480 p_x_ResourceAssignments(j).Employee_Number := l_cre_res_assmts(i).EMPLOYEE_NUMBER;
2481 p_x_ResourceAssignments(j).Employee_Name := l_cre_res_assmts(i).EMPLOYEE_NAME;
2482 p_x_ResourceAssignments(j).Inventory_Item_Id := l_cre_res_assmts(i).INVENTORY_ITEM_ID;
2483 p_x_ResourceAssignments(j).Item_Organization_Id := l_cre_res_assmts(i).ITEM_ORGANIZATION_ID;
2484 p_x_ResourceAssignments(j).Serial_Number := l_cre_res_assmts(i).SERIAL_NUMBER;
2485 p_x_ResourceAssignments(j).Instance_Id := l_cre_res_assmts(i).INSTANCE_ID;
2486 p_x_ResourceAssignments(j).Assignment_Start_Date := l_cre_res_assmts(i).ASSIGN_START_DATE;
2487 p_x_ResourceAssignments(j).Assignment_End_Date := l_cre_res_assmts(i).ASSIGN_END_DATE;
2488 p_x_ResourceAssignments(j).LAST_UPDATE_LOGIN := l_cre_res_assmts(i).LAST_UPDATE_LOGIN;
2489 p_x_ResourceAssignments(j).LAST_UPDATED_DATE := l_cre_res_assmts(i).LAST_UPDATED_DATE;
2490 p_x_ResourceAssignments(j).LAST_UPDATED_BY := l_cre_res_assmts(i).LAST_UDDATED_BY;
2491 p_x_ResourceAssignments(j).CREATION_DATE := l_cre_res_assmts(i).CREATION_DATE;
2492 p_x_ResourceAssignments(j).CREATED_BY := l_cre_res_assmts(i).CREATED_BY;
2493 p_x_ResourceAssignments(j).ATTRIBUTE_CATEGORY := l_cre_res_assmts(i).ATTRIBUTE_CATEGORY;
2494 p_x_ResourceAssignments(j).ATTRIBUTE1 := l_cre_res_assmts(i).ATTRIBUTE1;
2495 p_x_ResourceAssignments(j).ATTRIBUTE2 := l_cre_res_assmts(i).ATTRIBUTE2;
2496 p_x_ResourceAssignments(j).ATTRIBUTE3 := l_cre_res_assmts(i).ATTRIBUTE3;
2497 p_x_ResourceAssignments(j).ATTRIBUTE4 := l_cre_res_assmts(i).ATTRIBUTE4;
2498 p_x_ResourceAssignments(j).ATTRIBUTE5 := l_cre_res_assmts(i).ATTRIBUTE5;
2499 p_x_ResourceAssignments(j).ATTRIBUTE6 := l_cre_res_assmts(i).ATTRIBUTE6;
2500 p_x_ResourceAssignments(j).ATTRIBUTE7 := l_cre_res_assmts(i).ATTRIBUTE7;
2501 p_x_ResourceAssignments(j).ATTRIBUTE8 := l_cre_res_assmts(i).ATTRIBUTE8;
2502 p_x_ResourceAssignments(j).ATTRIBUTE9 := l_cre_res_assmts(i).ATTRIBUTE9;
2503 p_x_ResourceAssignments(j).ATTRIBUTE10 := l_cre_res_assmts(i).ATTRIBUTE10;
2504 p_x_ResourceAssignments(j).ATTRIBUTE11 := l_cre_res_assmts(i).ATTRIBUTE11;
2505 p_x_ResourceAssignments(j).ATTRIBUTE12 := l_cre_res_assmts(i).ATTRIBUTE12;
2506 p_x_ResourceAssignments(j).ATTRIBUTE13 := l_cre_res_assmts(i).ATTRIBUTE13;
2507 p_x_ResourceAssignments(j).ATTRIBUTE14 := l_cre_res_assmts(i).ATTRIBUTE14;
2508 p_x_ResourceAssignments(j).ATTRIBUTE15 := l_cre_res_assmts(i).ATTRIBUTE15;
2509 p_x_ResourceAssignments(j).OPERATION_FLAG := l_cre_res_assmts(i).OPERATION_FLAG;
2510 j:= j+1;
2511 END LOOP;
2512 END IF;
2513 END process_res_assignments;
2514
2515 PROCEDURE process_Unclose_Workorder
2516 (
2517 p_api_version IN NUMBER := 1.0,
2518 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2519 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2520 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2521 p_default IN VARCHAR2 := FND_API.G_FALSE,
2522 p_module_type IN VARCHAR2 := NULL,
2523 x_return_status OUT NOCOPY VARCHAR2,
2524 x_msg_count OUT NOCOPY NUMBER,
2525 x_msg_data OUT NOCOPY VARCHAR2,
2526 p_x_workorder_rec IN OUT NOCOPY WORKORDER_REC_TYPE
2527 )
2528 IS
2529
2530 CURSOR get_status_csr(status VARCHAR)
2531 IS
2532 SELECT lookup_code
2533 FROM fnd_Lookups
2534 WHERE lookup_type = 'AHL_JOB_STATUS'
2535 AND meaning = status;
2536
2537 l_prd_workorder_rec AHL_PRD_WORKORDER_PVT.prd_workorder_rec;
2538
2539 BEGIN
2540
2541 x_return_status := FND_API.G_RET_STS_SUCCESS;
2542
2543 IF p_x_workorder_rec.status_code IS NULL AND p_x_workorder_rec.status IS NOT NULL THEN
2544 OPEN get_status_csr(p_x_workorder_rec.status);
2545 FETCH get_status_csr INTO p_x_workorder_rec.status_code;
2546 IF get_status_csr%NOTFOUND THEN
2547 FND_MESSAGE.SET_NAME('AHL','AHL_PP_JOB_INV_STATUS_JSP');
2548 FND_MSG_PUB.ADD;
2549 CLOSE get_status_csr;
2550 RAISE FND_API.G_EXC_ERROR;
2551 ELSE
2552 CLOSE get_status_csr;
2553 END IF;
2554 END IF;
2555
2556
2557 -- populate record
2558 l_prd_workorder_rec.dml_operation := 'U';
2559 l_prd_workorder_rec.BATCH_ID := p_x_workorder_rec.Batch_Id;
2560 l_prd_workorder_rec.HEADER_ID := p_x_workorder_rec.Header_Id;
2561 l_prd_workorder_rec.WORKORDER_ID := p_x_workorder_rec.Workorder_Id;
2562 l_prd_workorder_rec.WIP_ENTITY_ID := p_x_workorder_rec.Wip_Entity_Id;
2563 l_prd_workorder_rec.OBJECT_VERSION_NUMBER := p_x_workorder_rec.Object_Version_Number;
2564 l_prd_workorder_rec.JOB_NUMBER := p_x_workorder_rec.Workorder_Number;
2565 l_prd_workorder_rec.JOB_DESCRIPTION := p_x_workorder_rec.Description;
2566 l_prd_workorder_rec.ORGANIZATION_ID := p_x_workorder_rec.Organization_Id;
2567 l_prd_workorder_rec.ORGANIZATION_NAME := p_x_workorder_rec.Organization_Name;
2568 l_prd_workorder_rec.ORGANIZATION_CODE := p_x_workorder_rec.Organization_Code;
2569 l_prd_workorder_rec.DEPARTMENT_NAME := p_x_workorder_rec.Department_Name;
2570 l_prd_workorder_rec.DEPARTMENT_ID := p_x_workorder_rec.Department_Id;
2571 l_prd_workorder_rec.DEPARTMENT_CLASS_CODE := p_x_workorder_rec.Department_Class_Code;
2572 l_prd_workorder_rec.STATUS_CODE := p_x_workorder_rec.Status_Code;
2573 l_prd_workorder_rec.STATUS_MEANING := NULL;
2574 l_prd_workorder_rec.HOLD_REASON_CODE := FND_API.G_MISS_CHAR;
2575 l_prd_workorder_rec.HOLD_REASON := FND_API.G_MISS_CHAR;
2576 l_prd_workorder_rec.SCHEDULED_START_DATE := p_x_workorder_rec.Scheduled_Start_Date;
2577 l_prd_workorder_rec.SCHEDULED_START_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'hh24');
2578 l_prd_workorder_rec.SCHEDULED_START_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_START_DATE, 'mi');
2579 l_prd_workorder_rec.SCHEDULED_END_DATE := p_x_workorder_rec.Scheduled_End_Date;
2580 l_prd_workorder_rec.SCHEDULED_END_HR := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'hh24');
2581 l_prd_workorder_rec.SCHEDULED_END_MI := TO_CHAR(l_prd_workorder_rec.SCHEDULED_END_DATE, 'mi');
2582 l_prd_workorder_rec.ACTUAL_START_DATE := p_x_workorder_rec.Actual_Start_Date;
2583 l_prd_workorder_rec.ACTUAL_START_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'hh24');
2584 l_prd_workorder_rec.ACTUAL_START_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_START_DATE, 'mi');
2585 l_prd_workorder_rec.ACTUAL_END_DATE := p_x_workorder_rec.Actual_End_Date;
2586 l_prd_workorder_rec.ACTUAL_END_HR := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'hh24');
2587 l_prd_workorder_rec.ACTUAL_END_MI := TO_CHAR(l_prd_workorder_rec.ACTUAL_END_DATE, 'mi');
2588 l_prd_workorder_rec.COMPLETION_SUBINVENTORY := p_x_workorder_rec.Completion_Subinventory;
2589 l_prd_workorder_rec.COMPLETION_LOCATOR_ID := p_x_workorder_rec.Completion_Locator_Id;
2590 l_prd_workorder_rec.COMPLETION_LOCATOR_NAME := p_x_workorder_rec.Completion_Locator_Name;
2591 l_prd_workorder_rec.WIP_SUPPLY_TYPE := p_x_workorder_rec.Wip_Supply_Type;
2592 l_prd_workorder_rec.WIP_SUPPLY_MEANING := p_x_workorder_rec.Wip_Supply_Meaning;
2593 l_prd_workorder_rec.FIRM_PLANNED_FLAG := p_x_workorder_rec.Firm_Plan_Flag;
2594 l_prd_workorder_rec.CONFIRM_FAILURE_FLAG := p_x_workorder_rec.Confirm_Failure_Flag;
2595 l_prd_workorder_rec.CLASS_CODE := p_x_workorder_rec.Wip_Accounting_Class_Code;
2596 l_prd_workorder_rec.JOB_PRIORITY := p_x_workorder_rec.Priority_Code;
2597 l_prd_workorder_rec.JOB_PRIORITY_MEANING := p_x_workorder_rec.Priority;
2598 l_prd_workorder_rec.SUB_INVENTORY := p_x_workorder_rec.Sub_Inventory;
2599 l_prd_workorder_rec.LOCATOR_ID := p_x_workorder_rec.Locator_Id;
2600 l_prd_workorder_rec.ATTRIBUTE_CATEGORY := p_x_workorder_rec.ATTRIBUTE_CATEGORY;
2601 l_prd_workorder_rec.ATTRIBUTE1 := p_x_workorder_rec.ATTRIBUTE1;
2602 l_prd_workorder_rec.ATTRIBUTE2 := p_x_workorder_rec.ATTRIBUTE2;
2603 l_prd_workorder_rec.ATTRIBUTE3 := p_x_workorder_rec.ATTRIBUTE3;
2604 l_prd_workorder_rec.ATTRIBUTE4 := p_x_workorder_rec.ATTRIBUTE4;
2605 l_prd_workorder_rec.ATTRIBUTE5 := p_x_workorder_rec.ATTRIBUTE5;
2606 l_prd_workorder_rec.ATTRIBUTE6 := p_x_workorder_rec.ATTRIBUTE6;
2607 l_prd_workorder_rec.ATTRIBUTE7 := p_x_workorder_rec.ATTRIBUTE7;
2608 l_prd_workorder_rec.ATTRIBUTE8 := p_x_workorder_rec.ATTRIBUTE8;
2609 l_prd_workorder_rec.ATTRIBUTE9 := p_x_workorder_rec.ATTRIBUTE9;
2610 l_prd_workorder_rec.ATTRIBUTE10 := p_x_workorder_rec.ATTRIBUTE10;
2611 l_prd_workorder_rec.ATTRIBUTE11 := p_x_workorder_rec.ATTRIBUTE11;
2612 l_prd_workorder_rec.ATTRIBUTE12 := p_x_workorder_rec.ATTRIBUTE12;
2613 l_prd_workorder_rec.ATTRIBUTE13 := p_x_workorder_rec.ATTRIBUTE13;
2614 l_prd_workorder_rec.ATTRIBUTE14 := p_x_workorder_rec.ATTRIBUTE14;
2615 l_prd_workorder_rec.ATTRIBUTE15 := p_x_workorder_rec.ATTRIBUTE15;
2616 l_prd_workorder_rec.LAST_UPDATE_DATE := p_x_workorder_rec.LAST_UPDATED_DATE;
2617 l_prd_workorder_rec.LAST_UPDATED_BY := p_x_workorder_rec.LAST_UPDATED_BY;
2618 l_prd_workorder_rec.CREATION_DATE := p_x_workorder_rec.CREATION_DATE;
2619 l_prd_workorder_rec.CREATED_BY := p_x_workorder_rec.CREATED_BY;
2620 l_prd_workorder_rec.LAST_UPDATE_LOGIN := p_x_workorder_rec.LAST_UPDATE_LOGIN;
2621
2622 l_prd_workorder_rec.INVENTORY_ITEM_ID := p_x_workorder_rec.INVENTORY_ITEM_ID;
2623 l_prd_workorder_rec.ITEM_INSTANCE_ID := p_x_workorder_rec.ITEM_INSTANCE_ID;
2624 l_prd_workorder_rec.PROPAGATE_FLAG := p_x_workorder_rec.PROPAGATE_FLAG;
2625 l_prd_workorder_rec.SERVICE_ITEM_ID := p_x_workorder_rec.SERVICE_ITEM_ID;
2626 l_prd_workorder_rec.SERVICE_ITEM_ORG_ID := p_x_workorder_rec.SERVICE_ITEM_ORG_ID;
2627 l_prd_workorder_rec.SERVICE_ITEM_DESCRIPTION := p_x_workorder_rec.SERVICE_ITEM_DESCRIPTION;
2628 l_prd_workorder_rec.SERVICE_ITEM_NUMBER := p_x_workorder_rec.SERVICE_ITEM_NUMBER;
2629 l_prd_workorder_rec.QUANTITY := p_x_workorder_rec.QUANTITY;
2630 l_prd_workorder_rec.MRP_QUANTITY := p_x_workorder_rec.MRP_QUANTITY;
2631 l_prd_workorder_rec.PARENT_ID := p_x_workorder_rec.PARENT_ID;
2632 l_prd_workorder_rec.TASK_MOTIVE_STATUS_ID := p_x_workorder_rec.TASK_MOTIVE_STATUS_ID;
2633 l_prd_workorder_rec.ALLOW_EXPLOSION := p_x_workorder_rec.ALLOW_EXPLOSION;
2634
2635 AHL_PRD_WORKORDER_PVT.Unclose_Workorder(
2636 p_api_version => p_api_version,
2637 p_init_msg_list => p_init_msg_list,
2638 p_commit => p_commit,
2639 p_validation_level => p_validation_level,
2640 p_default => p_default,
2641 p_module_type => 'API',
2642 x_return_status => x_return_status,
2643 x_msg_count => x_msg_count,
2644 x_msg_data => x_msg_data,
2645 p_x_prd_workorder_rec => l_prd_workorder_rec
2646 );
2647
2648 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2649 RAISE FND_API.G_EXC_ERROR;
2650 END IF;
2651
2652 END process_Unclose_Workorder;
2653
2654 PROCEDURE process_workorder
2655 (
2656 p_api_version IN NUMBER := 1.0,
2657 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2658 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2659 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2660 p_default IN VARCHAR2 := FND_API.G_FALSE,
2661 p_module_type IN VARCHAR2,
2662 p_x_workorder_rec IN OUT NOCOPY WORKORDER_REC_TYPE,
2663 p_x_Operations IN OUT NOCOPY OPERATIONS_TBL_TYPE,
2664 p_x_MaterialRequirements IN OUT NOCOPY MTL_REQMTS_TBL_TYPE,
2665 p_x_WO_QaResults IN OUT NOCOPY QA_RESULTS_REC_TYPE,
2666 p_x_ResourceRequirements IN OUT NOCOPY RES_REQUIREMENTS_TBL_TYPE,
2667 p_x_ResourceAssignments IN OUT NOCOPY RES_ASSIGNMENTS_TBL_TYPE,
2668 p_x_ResourceTransactions IN OUT NOCOPY RES_TXNS_TBL_TYPE,
2669 x_return_status OUT NOCOPY VARCHAR2,
2670 x_msg_count OUT NOCOPY NUMBER,
2671 x_msg_data OUT NOCOPY VARCHAR2
2672 ) IS
2673 l_api_version CONSTANT NUMBER := 1.0;
2674 l_api_name CONSTANT VARCHAR2(30) := 'process_workorder';
2675 l_workorder_rec WORKORDER_REC_TYPE;
2676
2677 CURSOR get_workorder_csr(p_workorder_id IN NUMBER)IS
2678 SELECT WORKORDER_ID,WIP_ENTITY_ID,OBJECT_VERSION_NUMBER,JOB_NUMBER,ORGANIZATION_ID,
2679 DEPARTMENT_ID,JOB_STATUS_CODE,PLAN_ID,COLLECTION_ID
2680 FROM ahl_search_workorders_v
2681 WHERE WORKORDER_ID = p_workorder_id;
2682
2683 BEGIN
2684 -- Standard call to check for call compatibility
2685 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME ) THEN
2686 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2687 END IF;
2688
2689 SAVEPOINT PROCESS_WORKORDER;
2690
2691 IF FND_API.To_Boolean(p_init_msg_list) THEN
2692 FND_MSG_PUB.Initialize;
2693 END IF;
2694 -- Initialize API return status to success
2695 x_return_status := FND_API.G_RET_STS_SUCCESS;
2696 --AHL_DEBUG_PUB.debug( 'p_x_workorder_rec.Workorder_Id : '||p_x_workorder_rec.Workorder_Id);
2697 --AHL_DEBUG_PUB.debug( 'p_x_workorder_rec.Workorder_Number : '||p_x_workorder_rec.Workorder_Number);
2698
2699 IF(p_x_workorder_rec.Workorder_Id IS NULL)THEN
2700 p_x_workorder_rec.Workorder_Id := get_workorder_id(p_x_workorder_rec.Workorder_Number);
2701 END IF;
2702 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2703 RAISE FND_API.G_EXC_ERROR;
2704 END IF;
2705 OPEN get_workorder_csr(p_x_workorder_rec.Workorder_Id);
2706 FETCH get_workorder_csr INTO l_workorder_rec.Workorder_Id,
2707 l_workorder_rec.Wip_Entity_Id,
2708 l_workorder_rec.Object_Version_Number,
2709 l_workorder_rec.Workorder_Number,
2710 l_workorder_rec.Organization_Id,
2711 l_workorder_rec.Department_Id,
2712 l_workorder_rec.Status_Code,
2713 l_workorder_rec.Plan_Id,
2714 l_workorder_rec.Collection_Id;
2715 CLOSE get_workorder_csr;--No chance of a non-fetch
2716
2717 IF(p_x_workorder_rec.Object_Version_Number IS NULL OR
2718 p_x_workorder_rec.Object_Version_Number <> l_workorder_rec.Object_Version_Number)THEN
2719 --AHL_DEBUG_PUB.debug( 'Object Version Numbers are not same');
2720 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2721 FND_MSG_PUB.ADD;
2722 RAISE FND_API.G_EXC_ERROR;
2723 END IF;
2724
2725 --JKJain, ER 9283136 (Unclosing a WorkOrder)
2726 --AHL_DEBUG_PUB.debug( 'processing Unclosing of a WorkOrder');
2727
2728 IF(p_x_workorder_rec.UNCLOSE_WORKORDER_FLAG = 'Y') THEN
2729
2730 IF (l_workorder_rec.Status_Code <> '12') THEN
2731 FND_MESSAGE.SET_NAME('AHL','AHL_PP_UNCLOSE_ONLY_CLOSED');
2732 FND_MSG_PUB.ADD;
2733 RAISE FND_API.G_EXC_ERROR;
2734 END IF;
2735
2736 process_Unclose_Workorder(
2737 p_api_version => p_api_version,
2738 p_init_msg_list => p_init_msg_list,
2739 p_commit => p_commit,
2740 p_validation_level => p_validation_level,
2741 p_default => p_default,
2742 p_module_type => 'API',
2743 x_return_status => x_return_status,
2744 x_msg_count => x_msg_count,
2745 x_msg_data => x_msg_data,
2746 p_x_workorder_rec => p_x_workorder_rec
2747 );
2748 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2749 RAISE FND_API.G_EXC_ERROR;
2750 ELSE
2751 -- fresh fetch as object version number might change
2752 OPEN get_workorder_csr(p_x_workorder_rec.Workorder_Id);
2753 FETCH get_workorder_csr INTO l_workorder_rec.Workorder_Id,
2754 l_workorder_rec.Wip_Entity_Id,
2755 l_workorder_rec.Object_Version_Number,
2756 l_workorder_rec.Workorder_Number,
2757 l_workorder_rec.Organization_Id,
2758 l_workorder_rec.Department_Id,
2759 l_workorder_rec.Status_Code,
2760 l_workorder_rec.Plan_Id,
2761 l_workorder_rec.Collection_Id;
2762 CLOSE get_workorder_csr;--No chance of a non-fetch
2763 END IF;
2764 END IF;
2765
2766 --AHL_DEBUG_PUB.debug( 'processing resource requirement');
2767 process_res_requirements(
2768 p_workorder_rec => l_workorder_rec,
2769 p_x_ResourceRequirements => p_x_ResourceRequirements,
2770 x_return_status => x_return_status,
2771 x_msg_count => x_msg_count,
2772 x_msg_data => x_msg_data
2773 );
2774 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2775 RAISE FND_API.G_EXC_ERROR;
2776 END IF;
2777 --AHL_DEBUG_PUB.debug( 'Done processing resource requirement');
2778 process_res_assignments(
2779 p_workorder_rec => l_workorder_rec,
2780 p_x_ResourceAssignments => p_x_ResourceAssignments,
2781 x_return_status => x_return_status,
2782 x_msg_count => x_msg_count,
2783 x_msg_data => x_msg_data
2784 );
2785 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2786 RAISE FND_API.G_EXC_ERROR;
2787 END IF;
2788 --AHL_DEBUG_PUB.debug( 'Done processing res_assignments');
2789 process_res_txns(
2790 p_workorder_rec => l_workorder_rec,
2791 p_x_ResourceTransactions => p_x_ResourceTransactions,
2792 x_return_status => x_return_status,
2793 x_msg_count => x_msg_count,
2794 x_msg_data => x_msg_data
2795 );
2796 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2797 RAISE FND_API.G_EXC_ERROR;
2798 END IF;
2799 --AHL_DEBUG_PUB.debug( 'Done processing resource transactions');
2800
2801 process_mtl_requirements(
2802 p_workorder_rec => l_workorder_rec,
2803 p_x_MaterialRequirements => p_x_MaterialRequirements,
2804 x_return_status => x_return_status,
2805 x_msg_count => x_msg_count,
2806 x_msg_data => x_msg_data
2807 );
2808 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2809 RAISE FND_API.G_EXC_ERROR;
2810 END IF;
2811 --AHL_DEBUG_PUB.debug( 'Done processing mtl_requirements');
2812 process_op_details(
2813 p_workorder_rec => l_workorder_rec,
2814 p_x_Operations => p_x_Operations,
2815 x_return_status => x_return_status,
2816 x_msg_count => x_msg_count,
2817 x_msg_data => x_msg_data
2818 );
2819 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2820 RAISE FND_API.G_EXC_ERROR;
2821 END IF;
2822
2823 --AHL_DEBUG_PUB.debug( 'Done processing op_details');
2824
2825 process_wo_quality(
2826 p_workorder_rec => l_workorder_rec,
2827 p_x_WO_QaResults => p_x_WO_QaResults,
2828 x_return_status => x_return_status,
2829 x_msg_count => x_msg_count,
2830 x_msg_data => x_msg_data
2831 );
2832 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2833 RAISE FND_API.G_EXC_ERROR;
2834 ELSE
2835 -- fresh fetch as object version number might change
2836 OPEN get_workorder_csr(p_x_workorder_rec.Workorder_Id);
2837 FETCH get_workorder_csr INTO l_workorder_rec.Workorder_Id,
2838 l_workorder_rec.Wip_Entity_Id,
2839 l_workorder_rec.Object_Version_Number,
2840 l_workorder_rec.Workorder_Number,
2841 l_workorder_rec.Organization_Id,
2842 l_workorder_rec.Department_Id,
2843 l_workorder_rec.Status_Code,
2844 l_workorder_rec.Plan_Id,
2845 l_workorder_rec.Collection_Id;
2846 CLOSE get_workorder_csr;--No chance of a non-fetch
2847 END IF;
2848 --AHL_DEBUG_PUB.debug( 'processing WORKORDER details');
2849 process_wo_details(
2850 p_x_workorder_rec => p_x_workorder_rec,
2851 p_curr_workorder_rec => l_workorder_rec,
2852 x_return_status => x_return_status,
2853 x_msg_count => x_msg_count,
2854 x_msg_data => x_msg_data
2855 );
2856 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
2857 RAISE FND_API.G_EXC_ERROR;
2858 ELSE
2859 -- fresh fetch as object version number might change
2860 OPEN get_workorder_csr(p_x_workorder_rec.Workorder_Id);
2861 FETCH get_workorder_csr INTO p_x_workorder_rec.Workorder_Id,
2862 p_x_workorder_rec.Wip_Entity_Id,
2863 p_x_workorder_rec.Object_Version_Number,
2864 p_x_workorder_rec.Workorder_Number,
2865 p_x_workorder_rec.Organization_Id,
2866 p_x_workorder_rec.Department_Id,
2867 p_x_workorder_rec.Status_Code,
2868 p_x_workorder_rec.Plan_Id,
2869 p_x_workorder_rec.Collection_Id;
2870 CLOSE get_workorder_csr;--No chance of a non-fetch
2871 END IF;
2872 --AHL_DEBUG_PUB.debug( 'done processing WORKORDER details');
2873 IF FND_API.to_boolean(p_commit) THEN
2874 COMMIT;
2875 END IF;
2876 -- Standard call to get message count and if count is 1, get message
2877 FND_MSG_PUB.Count_And_Get
2878 ( p_count => x_msg_count,
2879 p_data => x_msg_data,
2880 p_encoded => fnd_api.g_false);
2881
2882 EXCEPTION
2883 WHEN FND_API.G_EXC_ERROR THEN
2884 ROLLBACK TO PROCESS_WORKORDER;
2885 x_return_status := FND_API.G_RET_STS_ERROR;
2886 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2887 p_data => x_msg_data,
2888 p_encoded => fnd_api.g_false);
2889 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2890 ROLLBACK TO PROCESS_WORKORDER;
2891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2892 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2893 p_data => x_msg_data,
2894 p_encoded => fnd_api.g_false);
2895 WHEN OTHERS THEN
2896 ROLLBACK TO PROCESS_WORKORDER;
2897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2898
2899 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2900 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2901 p_procedure_name => l_api_name,
2902 p_error_text => SUBSTR(SQLERRM,1,500));
2903 END IF;
2904 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2905 p_data => x_msg_data,
2906 p_encoded => fnd_api.g_false);
2907 END process_workorder;
2908
2909 FUNCTION is_valid_result_attribute(p_Char_Id IN NUMBER, p_QA_PLAN IN QA_PLAN_REC_TYPE) RETURN VARCHAR2
2910 IS
2911 l_addAttribute VARCHAR2(1);
2912 l_enabled_flag VARCHAR2(1);
2913
2914 BEGIN
2915 l_addAttribute := 'F';
2916 FOR i IN p_QA_PLAN.QA_PLAN_ATR_TBL.FIRST..p_QA_PLAN.QA_PLAN_ATR_TBL.LAST LOOP
2917 IF(p_Char_Id = p_QA_PLAN.QA_PLAN_ATR_TBL(i).Char_Id) THEN
2918 IF (p_QA_PLAN.QA_PLAN_ATR_TBL(i).Is_ReadOnly = 'F')THEN
2919 l_addAttribute := 'T';
2920 EXIT;
2921 END IF;
2922 END IF;
2923 END LOOP;
2924 RETURN l_addAttribute;
2925 END IS_VALID_RESULT_ATTRIBUTE;
2926
2927 END AHL_PRD_WORKORDER_PUB;