[Home] [Help]
PACKAGE BODY: APPS.CSP_TO_FORM_MOLINES
Source
1 PACKAGE BODY CSP_TO_FORM_MOLINES AS
2 /* $Header: cspgtmlb.pls 115.14 2002/11/26 06:51:27 hhaugeru ship $ */
3 -- Start of Comments
4 -- Package name : CSP_TO_FORM_MOLINES
5 -- Purpose : A wrapper to prepare data to call the CSP_ORDERLINES_PVT.Create_orderlines.
6 -- History
7 -- 18-Nov-1999: klou
8 -- 03-Dev-1999: Modified because of change of schema by Vernon Lou.
9 -- Removed fields: p_address, p_service_request_number, p_mtl15_line_id, p_total_shipped.
10 -- Added fields: p_incident_id
11 --
12 -- NOTE :
13 -- End of Comments
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_TO_FORM_MOLINES';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtmlb.pls';
16 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
17 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
18
19 PROCEDURE Validate_and_Write(
20 P_Api_Version_Number IN NUMBER,
21 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
22 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
23 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
24 p_action_code IN NUMBER,
25 P_line_id IN NUMBER := FND_API.G_MISS_NUM,
26 p_CREATED_BY IN NUMBER := FND_API.G_MISS_NUM,
27 p_CREATION_DATE IN DATE := FND_API.G_MISS_DATE,
28 p_LAST_UPDATED_BY IN NUMBER := FND_API.G_MISS_NUM,
29 p_LAST_UPDATE_DATE IN DATE := FND_API.G_MISS_DATE,
30 p_LAST_UPDATED_LOGIN IN NUMBER := FND_API.G_MISS_NUM,
31 p_HEADER_ID IN NUMBER := FND_API.G_MISS_NUM,
32 p_CUSTOMER_PO IN VARCHAR2 := FND_API.G_MISS_CHAR,
33 p_INCIDENT_ID IN NUMBER := FND_API.G_MISS_NUM,
34 p_TASK_ID IN NUMBER := FND_API.G_MISS_NUM,
35 p_TASK_ASSIGNMENT_ID IN NUMBER := FND_API.G_MISS_NUM,
36 p_COMMENTS IN VARCHAR2 := FND_API.G_MISS_CHAR,
37 p_ATTRIBUTE_CATEGORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
38 p_ATTRIBUTE1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
39 p_ATTRIBUTE2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
40 p_ATTRIBUTE3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
41 p_ATTRIBUTE4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
42 p_ATTRIBUTE5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
43 p_ATTRIBUTE6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
44 p_ATTRIBUTE7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
45 p_ATTRIBUTE8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
46 p_ATTRIBUTE9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
47 p_ATTRIBUTE10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
48 p_ATTRIBUTE11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
49 p_ATTRIBUTE12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
50 p_ATTRIBUTE13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
51 p_ATTRIBUTE14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
52 p_ATTRIBUTE15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
53 X_Return_Status OUT NOCOPY VARCHAR2,
54 X_Msg_Count OUT NOCOPY NUMBER,
55 X_Msg_Data OUT NOCOPY VARCHAR2
56 )
57
58 IS
59 l_mol_rec CSP_ORDERLINES_PVT.MOL_Rec_Type;
60 l_return_line_id NUMBER;
61 l_api_version_number CONSTANT NUMBER := 1.0;
62 l_api_name CONSTANT VARCHAR2(50) := 'Validate_And_Write';
63 l_pkg_api_name CONSTANT VARCHAR2(80) := G_PKG_NAME ||'.'||l_api_name;
64 l_msg_data VARCHAR2(300);
65 l_check_existence NUMBER := 0;
66 l_return_status VARCHAR2(1);
67 l_msg_count NUMBER := 0;
68 l_commit VARCHAR2(1) := FND_API.G_FALSE;
69 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
70 l_task_id NUMBER := p_task_id;
71 l_task_assignment_id NUMBER := p_task_assignment_id;
72 EXCP_USER_DEFINED EXCEPTION;
73
74 l_creation_date DATE := p_creation_date;
75 l_last_update_date DATE := p_last_update_date;
76 l_created_by NUMBER := p_created_by;
77 l_last_update_login NUMBER := p_last_updated_login;
78 l_last_updated_by NUMBER := p_last_updated_by;
79 Cursor l_Get_Creation_Date_Csr Is
80 Select creation_date
81 From csp_moveorder_lines
82 Where line_id = p_LINE_ID;
83 Cursor l_Get_Line_ID_Csr Is
84 Select line_id
85 From csp_moveorder_lines
86 Where line_id = p_LINE_ID;
87
88 BEGIN
89 savepoint Validate_And_Write_PUB;
90
91 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
92 -- initialize message list
93 FND_MSG_PUB.initialize;
94 END IF;
95
96 -- Standard call to check for call compatibility.
97 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
98 p_api_version_number,
99 l_api_name,
100 G_PKG_NAME)
101 THEN
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103 END IF;
104
105 --validating p_action_code
106 IF nvl(p_action_code, fnd_api.g_miss_num) NOT IN (0, 1, 2) THEN
107 fnd_message.set_name ('INV', 'INV-INVALID ACTION');
108 fnd_message.set_token ('ROUTINE', l_pkg_api_name, FALSE);
109 fnd_msg_pub.add;
110 RAISE EXCP_USER_DEFINED;
111 END IF;
112
113 IF p_action_code = 0 THEN
114 -- check p_header_id and p_line_id. if null return an error.
115 IF nvl(p_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
116 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
117 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_header_id', FALSE);
118 FND_MSG_PUB.ADD;
119 RAISE EXCP_USER_DEFINED;
120 ELSE
121 -- validate where the header_id already exists.
122 BEGIN
123 SELECT header_id INTO l_check_existence
124 FROM mtl_txn_request_headers --csp_moveorder_headers
125 WHERE header_id = p_header_id;
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_MOVEORDER');
129 FND_MESSAGE.SET_TOKEN('HEADER_ID', to_char(p_header_id), FALSE);
130 FND_MSG_PUB.ADD;
131 RAISE EXCP_USER_DEFINED;
132 WHEN OTHERS THEN
133 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
134 fnd_message.set_token('ERR_FIELD', 'p_header_id', FALSE);
135 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
136 fnd_message.set_token('TABLE', 'MTL_TXN_REQUEST_HEADERS', FALSE);
137 FND_MSG_PUB.ADD;
138 RAISE EXCP_USER_DEFINED;
139 END;
140
141 END IF;
142
143 IF nvl(p_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
144 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
145 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_line_id', FALSE);
146 FND_MSG_PUB.ADD;
147 RAISE EXCP_USER_DEFINED;
148 ELSE
149 -- First check whether the line id already exists.
150 OPEN l_Get_Line_ID_Csr;
151 FETCH l_Get_Line_ID_Csr INTO l_check_existence;
152 IF l_Get_Line_ID_Csr%NOTFOUND THEN
153 BEGIN
154 SELECT line_id INTO l_check_existence
155 FROM mtl_txn_request_lines
156 WHERE line_id = p_line_id
157 AND header_id = p_header_id;
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 FND_MESSAGE.SET_NAME('CSP', 'CSP_MOLINE_NO_EXIST');
161 FND_MESSAGE.SET_TOKEN('LINE_ID', to_char(p_line_id), FALSE);
162 FND_MSG_PUB.ADD;
163 RAISE EXCP_USER_DEFINED;
164 WHEN OTHERS THEN
165 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
166 fnd_message.set_token('ERR_FIELD', 'p_line_id', FALSE);
167 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
168 fnd_message.set_token('TABLE', 'MTL_TXN_REQUEST_LINES', FALSE);
169 FND_MSG_PUB.ADD;
170 RAISE EXCP_USER_DEFINED;
171 END;
172 ELSE
173 fnd_message.set_name ('CSP', 'CSP_DUPLICATE_RECORD');
174 fnd_msg_pub.add;
175 RAISE EXCP_USER_DEFINED;
176 END IF;
177 CLOSE l_Get_Line_ID_Csr;
178 END IF;
179
180 IF nvl(p_task_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
181 -- Validate the task id
182 BEGIN
183 SELECT task_id INTO l_check_existence
184 FROM jtf_tasks_vl
185 WHERE task_id = p_task_id;
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188 FND_MESSAGE.SET_NAME('JTF', 'JTF_TASK_INVALID_TASK_ID');
189 FND_MESSAGE.SET_TOKEN('P_TASK_ID', to_char(p_task_id), FALSE);
190 FND_MSG_PUB.ADD;
191 RAISE EXCP_USER_DEFINED;
192 WHEN OTHERS THEN
193 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
194 fnd_message.set_token('ERR_FIELD', 'p_task_id', FALSE);
195 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
196 fnd_message.set_token('TABLE', 'JTF_TASKS_TL', FALSE);
197 FND_MSG_PUB.ADD;
198 RAISE EXCP_USER_DEFINED;
199 END;
200 END IF;
201
202 IF nvl(p_task_assignment_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
203 IF nvl(p_task_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
204 -- validate the task_assignment_id against the task_id.
205 BEGIN
206 SELECT task_assignment_id INTO l_check_existence
207 FROM jtf_task_assignments
208 WHERE task_assignment_id = p_task_assignment_id
209 AND task_id = p_task_id;
210 EXCEPTION
211 WHEN NO_DATA_FOUND THEN
212 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_TASK_ASSIGNMENT');
213 FND_MESSAGE.SET_TOKEN('ASSIGNMENT_ID', to_char(p_task_assignment_id), FALSE);
214 FND_MESSAGE.SET_TOKEN('TASK_ID', to_char(p_task_id), FALSE);
215 FND_MSG_PUB.ADD;
216 RAISE EXCP_USER_DEFINED;
217 WHEN OTHERS THEN
218 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
219 fnd_message.set_token('ERR_FIELD', 'p_task_assignment_id', FALSE);
220 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
221 fnd_message.set_token('TABLE', 'JTF_TASK_ASSIGNMENTS', FALSE);
222 FND_MSG_PUB.ADD;
223 RAISE EXCP_USER_DEFINED;
224 END;
225 ELSE
226 BEGIN
227 SELECT task_assignment_id INTO l_check_existence
228 FROM jtf_task_assignments
229 WHERE task_assignment_id = p_task_assignment_id;
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 FND_MESSAGE.SET_NAME('JTF', 'JTF_TASK_INV_TK_ASS');
233 FND_MESSAGE.SET_TOKEN('P_TASK_ASSIGNMENT_ID', to_char(p_task_assignment_id), FALSE);
234 FND_MSG_PUB.ADD;
235 RAISE EXCP_USER_DEFINED;
236 WHEN OTHERS THEN
237 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
238 fnd_message.set_token('ERR_FIELD', 'p_task_assignment_id', FALSE);
239 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
240 fnd_message.set_token('TABLE', 'JTF_TASK_ASSIGNMENTS', FALSE);
241 FND_MSG_PUB.ADD;
242 RAISE EXCP_USER_DEFINED;
243 END;
244 END IF;
245 END IF;
246
247 IF nvl(p_incident_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
248 -- validate the incident id
249 BEGIN
250 SELECT incident_id INTO l_check_existence
251 FROM cs_incidents_all
252 WHERE incident_id = p_incident_id;
253 EXCEPTION
254 WHEN NO_DATA_FOUND THEN
255 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_INCIDENT_ID');
256 FND_MESSAGE.SET_TOKEN('INCIDENT_ID', to_char(p_incident_id), FALSE);
257 FND_MSG_PUB.ADD;
258 RAISE EXCP_USER_DEFINED;
259 WHEN OTHERS THEN
260 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
261 fnd_message.set_token('ERR_FIELD', 'p_incident_id', FALSE);
262 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
263 fnd_message.set_token('TABLE', 'CS_INCIDENTS_ALL', FALSE);
264 FND_MSG_PUB.ADD;
265 RAISE EXCP_USER_DEFINED;
266 END;
267 END IF;
268
269 -- check creation_date and last_update_date
270 IF nvl(l_creation_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
271 l_creation_date := sysdate;
272 END IF;
273
274 IF nvl(l_last_update_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
275 l_last_update_date := sysdate;
276 END IF;
277
278 IF nvl(l_created_by, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
279 l_created_by := g_user_id;
280 END IF;
281
282 IF nvl(l_last_update_login, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
283 l_last_update_login := g_login_id;
284 END IF;
285
286 IF nvl(l_last_updated_by, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
287 l_last_updated_by := g_user_id;
288 END IF;
289
290 ELSIF p_action_code = 1 THEN
291 IF nvl(p_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
292 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
293 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_line_id', FALSE);
294 FND_MSG_PUB.ADD;
295 RAISE EXCP_USER_DEFINED;
296 ELSE
297 BEGIN
298 SELECT line_id INTO l_check_existence
299 FROM csp_moveorder_lines
300 WHERE line_id = p_line_id;
301 EXCEPTION
302 WHEN NO_DATA_FOUND THEN
303 FND_MESSAGE.SET_NAME('CSP', 'CSP_MOLINE_NO_EXIST');
304 FND_MESSAGE.SET_TOKEN('LINE_ID', to_char(p_line_id), FALSE);
305 FND_MSG_PUB.ADD;
306 RAISE EXCP_USER_DEFINED;
307 WHEN OTHERS THEN
308 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
309 fnd_message.set_token('ERR_FIELD', 'p_line_id', FALSE);
310 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
311 fnd_message.set_token('TABLE', 'CSP_MOVERDER_LINES', FALSE);
312 FND_MSG_PUB.ADD;
313 RAISE EXCP_USER_DEFINED;
314 END;
315 END IF;
316
317 -- check p_header_id and p_line_id. if null return an error.
318 IF nvl(p_header_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
319 -- validate where the header_id already exists.
320 BEGIN
321 SELECT header_id INTO l_check_existence
322 FROM csp_moveorder_headers
323 WHERE header_id = p_header_id;
324 EXCEPTION
325 WHEN NO_DATA_FOUND THEN
326 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_MOVEORDER');
327 FND_MESSAGE.SET_TOKEN('HEADER_ID', to_char(p_header_id), FALSE);
328 FND_MSG_PUB.ADD;
329 RAISE EXCP_USER_DEFINED;
330 WHEN OTHERS THEN
331 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
332 fnd_message.set_token('ERR_FIELD', 'p_header_id', FALSE);
333 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
334 fnd_message.set_token('TABLE', 'CSP_MOVEORDER_LINES', FALSE);
335 FND_MSG_PUB.ADD;
336 RAISE EXCP_USER_DEFINED;
337 END;
338 END IF;
339
340 IF nvl(l_task_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
341 -- Validate the task id
342 BEGIN
343 SELECT task_id INTO l_check_existence
344 FROM jtf_tasks_vl
345 WHERE task_id = l_task_id;
346 EXCEPTION
347 WHEN NO_DATA_FOUND THEN
348 FND_MESSAGE.SET_NAME('JTF', 'JTF_TASK_INVALID_TASK_ID');
349 FND_MESSAGE.SET_TOKEN('P_TASK_ID', to_char(p_task_id), FALSE);
350 FND_MSG_PUB.ADD;
351 RAISE EXCP_USER_DEFINED;
352 WHEN OTHERS THEN
353 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
354 fnd_message.set_token('ERR_FIELD', 'p_task_id', FALSE);
355 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
356 fnd_message.set_token('TABLE', 'JTF_TASKS_TL', FALSE);
357 FND_MSG_PUB.ADD;
358 RAISE EXCP_USER_DEFINED;
359 END;
360
361 IF nvl(p_task_assignment_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
362 SELECT nvl(task_assignment_id, fnd_api.g_miss_num) INTO l_task_assignment_id
363 FROM csp_moveorder_lines
364 WHERE line_id = p_line_id;
365
366 IF nvl(l_task_assignment_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
367 BEGIN
368 SELECT task_assignment_id INTO l_check_existence
369 FROM jtf_task_assignments
370 WHERE task_assignment_id = l_task_assignment_id
371 AND task_id = l_task_id;
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_TASK_ASSIGNMENT');
375 FND_MESSAGE.SET_TOKEN('ASSIGNMENT_ID', to_char(l_task_assignment_id), FALSE);
376 FND_MESSAGE.SET_TOKEN('TASK_ID', to_char(l_task_id), FALSE);
377 FND_MSG_PUB.ADD;
378 RAISE EXCP_USER_DEFINED;
379 WHEN OTHERS THEN
380 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
381 fnd_message.set_token('ERR_FIELD', 'p_task_assignment_id', FALSE);
382 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
383 fnd_message.set_token('TABLE', 'JTF_TASK_ASSIGNMENTS', FALSE);
384 FND_MSG_PUB.ADD;
385 RAISE EXCP_USER_DEFINED;
386 END;
387 END IF;
388 END IF;
389 END IF;
390
391 IF nvl(p_task_assignment_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
392 IF nvl(p_task_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
393 -- find out the task_id in the existing moveorder_line record.
394 SELECT nvl(task_id, fnd_api.g_miss_num) INTO l_task_id
395 FROM csp_moveorder_lines
396 WHERE line_id = p_line_id;
397 END IF;
398
399 IF nvl(l_task_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
400 -- validate the task_assignment_id against the task_id.
401 BEGIN
402 SELECT task_assignment_id INTO l_check_existence
403 FROM jtf_task_assignments
404 WHERE task_assignment_id = p_task_assignment_id
405 AND task_id = l_task_id;
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_TASK_ASSIGNMENT');
409 FND_MESSAGE.SET_TOKEN('ASSIGNMENT_ID', to_char(p_task_assignment_id), FALSE);
410 FND_MESSAGE.SET_TOKEN('TASK_ID', to_char(l_task_id), FALSE);
411 FND_MSG_PUB.ADD;
412 RAISE EXCP_USER_DEFINED;
413 WHEN OTHERS THEN
414 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
415 fnd_message.set_token('ERR_FIELD', 'p_task_assignment_id', FALSE);
416 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
417 fnd_message.set_token('TABLE', 'JTF_TASK_ASSIGNMENTS', FALSE);
418 FND_MSG_PUB.ADD;
419 RAISE EXCP_USER_DEFINED;
420 END;
421 ELSE
422 BEGIN
423 SELECT task_assignment_id INTO l_check_existence
424 FROM jtf_task_assignments
425 WHERE task_assignment_id = p_task_assignment_id;
426 EXCEPTION
427 WHEN NO_DATA_FOUND THEN
428 FND_MESSAGE.SET_NAME('JTF', 'JTF_TASK_INV_TK_ASS');
429 FND_MESSAGE.SET_TOKEN('P_TASK_ASSIGNMENT_ID', to_char(p_task_assignment_id), FALSE);
430 FND_MSG_PUB.ADD;
431 RAISE EXCP_USER_DEFINED;
432 WHEN OTHERS THEN
433 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
434 fnd_message.set_token('ERR_FIELD', 'p_task_assignment_id', FALSE);
435 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
436 fnd_message.set_token('TABLE', 'JTF_TASK_ASSIGNMENTS', FALSE);
437 FND_MSG_PUB.ADD;
438 RAISE EXCP_USER_DEFINED;
439 END;
440 END IF;
441 END IF;
442
443 IF nvl(p_incident_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
444 -- validate the incident id
445 BEGIN
446 SELECT incident_id INTO l_check_existence
447 FROM cs_incidents_all
448 WHERE incident_id = p_incident_id;
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_INCIDENT_ID');
452 FND_MESSAGE.SET_TOKEN('INCIDENT_ID', to_char(p_incident_id), FALSE);
453 FND_MSG_PUB.ADD;
454 RAISE EXCP_USER_DEFINED;
455 WHEN OTHERS THEN
456 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
457 fnd_message.set_token('ERR_FIELD', 'p_incident_id', FALSE);
458 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
459 fnd_message.set_token('TABLE', 'CS_INCIDENTS_ALL', FALSE);
460 FND_MSG_PUB.ADD;
461 RAISE EXCP_USER_DEFINED;
462 END;
463 END IF;
464
465 -- validate the creation_date
466 IF nvl(l_creation_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
467 Open l_Get_Creation_Date_Csr;
468 Fetch l_Get_Creation_Date_Csr into l_creation_date;
469 If l_Get_Creation_Date_Csr%NOTFOUND Then
470 Close l_Get_Creation_Date_Csr;
471 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
472 fnd_message.set_token('ERR_FIELD', 'p_cretaion_date', FALSE);
473 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
474 fnd_message.set_token('TABLE', 'CSP_MOVEORDER_LINES', FALSE);
475 FND_MSG_PUB.ADD;
476 RAISE EXCP_USER_DEFINED;
477 End if;
478 Close l_Get_Creation_Date_Csr;
479 End if;
480
481 IF nvl(l_last_update_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
482 l_last_update_date := sysdate;
483 END IF;
484 ELSE -- p_action_code = 2
485 IF nvl(p_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
486 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
487 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_line_id', FALSE);
488 FND_MSG_PUB.ADD;
489 RAISE EXCP_USER_DEFINED;
490 ELSE
491 BEGIN
492 SELECT line_id INTO l_check_existence
493 FROM csp_moveorder_lines
494 WHERE line_id = p_line_id;
495 EXCEPTION
496 WHEN NO_DATA_FOUND THEN
497 FND_MESSAGE.SET_NAME('CSP', 'CSP_MOLINE_NO_EXIST');
498 FND_MESSAGE.SET_TOKEN('LINE_ID', to_char(p_line_id), FALSE);
499 FND_MSG_PUB.ADD;
500 RAISE EXCP_USER_DEFINED;
501 WHEN OTHERS THEN
502 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
503 fnd_message.set_token('ERR_FIELD', 'p_line_id', FALSE);
504 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
505 fnd_message.set_token('TABLE', 'CSP_MOVERDER_LINES', FALSE);
506 FND_MSG_PUB.ADD;
507 RAISE EXCP_USER_DEFINED;
508 END;
509 END IF;
510 END IF;
511
512 -- construct the l_mol_rec for calling the CSP_ORDRELINES_PVT.Create_orderlines
513 l_mol_rec.LINE_ID := P_line_id;
514 l_mol_rec.CREATED_BY := nvl(l_CREATED_BY, fnd_api.g_miss_num);
515 l_mol_rec.CREATION_DATE := l_CREATION_DATE;
516 l_mol_rec.LAST_UPDATED_BY := nvl(l_LAST_UPDATED_BY, fnd_api.g_miss_num);
517 l_mol_rec.LAST_UPDATE_DATE := l_LAST_UPDATE_DATE;
518 l_mol_rec.LAST_UPDATED_LOGIN := l_LAST_UPDATE_LOGIN;
519 l_mol_rec.HEADER_ID := nvl(p_HEADER_ID, fnd_api.g_miss_num);
520 l_mol_rec.CUSTOMER_PO := p_CUSTOMER_PO;
521 l_mol_rec.INCIDENT_ID := p_INCIDENT_ID;
522 l_mol_rec.TASK_ID := p_TASK_ID;
523 l_mol_rec.TASK_ASSIGNMENT_ID := p_TASK_ASSIGNMENT_ID;
524 l_mol_rec.COMMENTS := p_COMMENTS;
525 l_mol_rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
526 l_mol_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
527 l_mol_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
528 l_mol_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
529 l_mol_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
530 l_mol_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
531 l_mol_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
532 l_mol_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
533 l_mol_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
534 l_mol_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
535 l_mol_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
536 l_mol_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
537 l_mol_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
538 l_mol_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
539 l_mol_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
540 l_mol_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
541
542
543 if p_action_code = 0 then -- call the create_orderlines procedure
544 CSP_ORDERLINES_PVT.Create_orderlines(
545 P_Api_Version_Number => P_api_version_number,
546 P_Init_Msg_List => P_Init_Msg_List,
547 P_Commit => l_Commit,
548 p_validation_level => p_validation_level,
549 P_MOL_Rec => l_mol_rec,
550 X_LINE_ID => l_return_line_id,
551 X_Return_Status => X_return_status,
552 X_Msg_Count => X_Msg_Count,
553 X_Msg_Data => X_Msg_Data);
554
555 elsif p_action_code = 1 then -- call the update procedure
556
557 CSP_ORDERLINES_PVT.Update_orderlines(
558 P_Api_Version_Number => P_api_version_number,
559 P_Init_Msg_List => P_Init_Msg_List,
560 P_Commit => l_Commit,
561 p_validation_level => p_validation_level,
562 P_Identity_Salesforce_Id => NULL,
563 P_MOL_Rec => l_mol_rec,
564 X_Return_Status => X_return_status,
565 X_Msg_Count => X_Msg_Count,
566 X_Msg_Data => X_Msg_Data);
567
568 else -- call the delete procedure
569 CSP_ORDERLINES_PVT.Delete_orderlines(
570 P_Api_Version_Number => P_api_version_number,
571 P_Init_Msg_List => P_Init_Msg_List,
572 P_Commit => l_Commit,
573 p_validation_level => p_validation_level,
574 P_Identity_Salesforce_Id => NULL,
575 P_MOL_Rec => l_mol_rec,
576 X_Return_Status => X_return_status,
577 X_Msg_Count => X_Msg_Count,
578 X_Msg_Data => X_Msg_Data);
579 end if;
580
581 IF l_return_status <> fnd_api.g_ret_sts_success THEN
582 RAISE FND_API.G_EXC_ERROR;
583 END IF;
584
585 IF fnd_api.to_boolean(p_commit) THEN
586 commit work;
587 END IF;
588
589 EXCEPTION
590 WHEN EXCP_USER_DEFINED THEN
591 Rollback to Validate_And_Write_PUB;
592 x_return_status := FND_API.G_RET_STS_ERROR;
593 fnd_msg_pub.count_and_get
594 ( p_count => x_msg_count
595 , p_data => x_msg_data);
596 --for debugging purpose
597 --x_msg_data := l_msg_data;
598 WHEN FND_API.G_EXC_ERROR THEN
599 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
600 P_API_NAME => L_API_NAME
601 ,P_PKG_NAME => G_PKG_NAME
602 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
603 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
604 ,X_MSG_COUNT => X_MSG_COUNT
605 ,X_MSG_DATA => X_MSG_DATA
606 ,X_RETURN_STATUS => X_RETURN_STATUS);
607 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
608 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
609 P_API_NAME => L_API_NAME
610 ,P_PKG_NAME => G_PKG_NAME
611 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
612 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
613 ,X_MSG_COUNT => X_MSG_COUNT
614 ,X_MSG_DATA => X_MSG_DATA
615 ,X_RETURN_STATUS => X_RETURN_STATUS);
616 WHEN OTHERS THEN
617 Rollback to Validate_And_Write_PUB;
618 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
619 fnd_message.set_token ('ROUTINE', l_pkg_api_name, FALSE);
620 fnd_message.set_token ('SQLERRM', sqlerrm, FALSE);
621 fnd_msg_pub.add;
622 fnd_msg_pub.count_and_get
623 ( p_count => x_msg_count
624 , p_data => x_msg_data);
625 x_return_status := fnd_api.g_ret_sts_error;
626
627 END Validate_and_Write;
628
629 END CSP_TO_FORM_MOLINES;