[Home] [Help]
PACKAGE BODY: APPS.CSP_TO_FORM_MOHEADERS
Source
1 PACKAGE BODY CSP_TO_FORM_MOHEADERS AS
2 /*$Header: cspgtmhb.pls 115.21 2002/11/26 06:53:45 hhaugeru ship $*/
3 -- Start of Comments
4 -- Package name : CSP_TO_FORM_MOMEAHDERS_B
5 -- Purpose : Takes all parameters from the FORM and construct those parameters into a record for calling
6 -- the prviate API in the CSP_MOVEORDER_HEADERS_PVT package.
7 -- History : 11/17/1999, Created by Vernon Lou
8 -- NOTE :
9 -- End of Comments
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_TO_FORM_MOHEADERS';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspgtmhb.pls';
13 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
14 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
15
16 PROCEDURE Validate_And_Write (
17 P_Api_Version_Number IN NUMBER,
18 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
19 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
20 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
21 p_action_code IN NUMBER, /* 0 = insert, 1 = update, 2 = delete */
22 p_header_id IN NUMBER := FND_API.G_MISS_NUM,
23 p_created_by IN NUMBER := FND_API.G_MISS_NUM,
24 p_creation_date IN DATE := FND_API.G_MISS_DATE,
25 p_last_updated_by IN NUMBER := FND_API.G_MISS_NUM,
26 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
27 p_last_update_login IN NUMBER := FND_API.G_MISS_NUM,
28 p_carrier IN VARCHAR2 := FND_API.G_MISS_CHAR,
29 p_shipment_method IN VARCHAR2 := FND_API.G_MISS_CHAR,
30 p_autoreceipt_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
31 p_attribute_category IN VARCHAR2 := FND_API.G_MISS_CHAR,
32 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
33 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
34 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
35 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
36 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
37 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
38 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
39 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
40 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
41 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
42 p_attribute11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
43 p_attribute12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
44 p_attribute13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
45 p_attribute14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
46 p_attribute15 IN VARCHAR2 := FND_API.G_MISS_CHAR,
47 p_location_id IN NUMBER := FND_API.G_MISS_NUM,
48 p_party_site_id IN NUMBER,
49 X_Return_Status OUT NOCOPY VARCHAR2,
50 X_Msg_Count OUT NOCOPY NUMBER,
51 X_Msg_Data OUT NOCOPY VARCHAR2
52 )
53 IS
54 l_moheader_rec CSP_ORDERHEADERS_PVT.MOH_Rec_Type;
55 l_header_id NUMBER := p_header_id;
56
57 l_api_version_number CONSTANT NUMBER := 1.0;
58 l_api_name CONSTANT VARCHAR2(50) := 'Validate_And_Write';
59 l_msg_data VARCHAR2(300);
60 l_check_existence NUMBER := 0;
61 l_return_status VARCHAR2(1);
62 l_msg_count NUMBER := 0;
63 l_commit VARCHAR2(1) := FND_API.G_FALSE;
64 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
65 l_pkg_api_name CONSTANT VARCHAR2(80) := G_PKG_NAME ||'.'||l_api_name;
66 l_carrier VARCHAR2(25);
67 EXCP_USER_DEFINED EXCEPTION;
68
69 l_creation_date DATE := p_creation_date;
70 l_last_update_date DATE := p_last_update_date;
71 l_created_by NUMBER := p_created_by;
72 l_last_update_login NUMBER := p_last_update_login;
73 l_last_updated_by NUMBER := p_last_updated_by;
74 Cursor l_Get_Creation_Date_Csr Is
75 Select creation_date
76 From csp_moveorder_headers
77 Where header_id = p_header_id;
78 Cursor l_Get_Header_ID_Csr IS
79 Select header_id
80 From csp_moveorder_headers
81 Where header_id = p_header_id;
82
83 BEGIN
84 savepoint Validate_And_Write_PUB;
85
86 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
87 -- initialize message list
88 FND_MSG_PUB.initialize;
89 END IF;
90
91 -- Standard call to check for call compatibility.
92 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
93 p_api_version_number,
94 l_api_name,
95 G_PKG_NAME)
96 THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99
100
101 -- check p_action_code
102 IF p_action_code not in (0, 1, 2) THEN
103 fnd_message.set_name('INV', 'INV-INVALID ACTION');
104 fnd_message.set_token('ROUTINE', l_api_name, FALSE);
105 fnd_msg_pub.add;
106 RAISE EXCP_USER_DEFINED;
107 END IF;
108
109 IF p_action_code = 0 THEN
110 -- For inserting, we need to validate the header_id and the carrier.
111 IF nvl(p_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
112 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
113 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_header_id', FALSE);
114 FND_MSG_PUB.ADD;
115 RAISE EXCP_USER_DEFINED;
116 ELSE
117 -- First we need to validate whether the given header_id already exists in the csp_moveorder_headers.
118 OPEN l_Get_Header_ID_Csr;
119 FETCH l_Get_Header_ID_Csr INTO l_check_existence;
120 IF l_Get_Header_ID_Csr%NOTFOUND THEN
121 -- Now, validate whether the given header_id exists in the mtl_txn_request_headers table.
122 BEGIN
123 SELECT header_id INTO l_check_existence
124 FROM mtl_txn_request_headers
125 WHERE header_id = p_header_id;
126
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_MOVEORDER');
130 FND_MESSAGE.SET_TOKEN('HEADER_ID', to_char(p_header_id), FALSE);
131 FND_MSG_PUB.ADD;
132 RAISE EXCP_USER_DEFINED;
133 WHEN OTHERS THEN
134 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
135 fnd_message.set_token('ERR_FIELD', 'p_header_id', FALSE);
136 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
137 fnd_message.set_token('TABLE', 'MTL_TXN_REQUEST_HEADERS', FALSE);
138 FND_MSG_PUB.ADD;
139 RAISE EXCP_USER_DEFINED;
140
141 END;
142 ELSE
143 fnd_message.set_name ('CSP', 'CSP_DUPLICATE_RECORD');
144 fnd_msg_pub.add;
145 RAISE EXCP_USER_DEFINED;
146 END IF;
147 CLOSE l_Get_Header_ID_Csr;
148
149 END IF;
150
151 IF nvl(p_carrier, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
152 -- Validate whether the p_carrier exists.
153 BEGIN
154 SELECT distinct freight_code INTO l_carrier
155 FROM org_freight_tl
156 WHERE freight_code = p_carrier
157 AND organization_id = (SELECT organization_id FROM mtl_txn_request_headers
158 WHERE header_id = p_header_id);
159 EXCEPTION
160 WHEN NO_DATA_FOUND THEN
161 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_CARRIER');
162 FND_MESSAGE.SET_TOKEN('CARRIER_CODE', p_carrier, FALSE);
163 FND_MSG_PUB.ADD;
164 RAISE EXCP_USER_DEFINED;
165 WHEN OTHERS THEN
166 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
167 fnd_message.set_token('ERR_FIELD', 'p_carrier', FALSE);
168 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
169 fnd_message.set_token('TABLE', 'ORG_FREIGHT_TL', FALSE);
170 FND_MSG_PUB.ADD;
171 RAISE EXCP_USER_DEFINED;
172 END;
173 END IF;
174
175 -- check creation_date and last_update_date
176 IF nvl(l_creation_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
177 l_creation_date := sysdate;
178 END IF;
179
180 IF nvl(l_last_update_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
181 l_last_update_date := sysdate;
182 END IF;
183
184 IF nvl(l_created_by, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
185 l_created_by := g_user_id;
186 END IF;
187
188 IF nvl(l_last_update_login, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
189 l_last_update_login := g_login_id;
190 END IF;
191
192 IF nvl(l_last_updated_by, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
193 l_last_updated_by := g_user_id;
194 END IF;
195
196 ELSIF p_action_code = 1 THEN
197 IF nvl(p_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
198 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
199 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_header_id', FALSE);
200 FND_MSG_PUB.ADD;
201 RAISE EXCP_USER_DEFINED;
202 ELSE
203 -- Validate whether the given header_id exists in the mtl_txn_request_headers table.
204 BEGIN
205 SELECT header_id INTO l_check_existence
206 FROM csp_moveorder_headers
207 WHERE header_id = p_header_id;
208
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_MOVEORDER');
212 FND_MESSAGE.SET_TOKEN('HEADER_ID', to_char(p_header_id), FALSE);
213 FND_MSG_PUB.ADD;
214 RAISE EXCP_USER_DEFINED;
215 WHEN OTHERS THEN
216 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
217 fnd_message.set_token('ERR_FIELD', 'p_header_id', FALSE);
218 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
219 fnd_message.set_token('TABLE', 'CSP_MOVEORDER_HEADERS', FALSE);
220 FND_MSG_PUB.ADD;
221 RAISE EXCP_USER_DEFINED;
222
223 END;
224 END IF;
225
226 IF nvl(p_carrier, fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
227 -- Validate whether the p_carrier exists.
228 BEGIN
229 SELECT distinct freight_code INTO l_carrier
230 FROM org_freight_tl
231 WHERE freight_code = p_carrier
232 AND organization_id = (SELECT organization_id FROM mtl_txn_request_headers
233 WHERE header_id = p_header_id);
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_CARRIER');
237 FND_MESSAGE.SET_TOKEN('CARRIER_CODE', p_carrier, FALSE);
238 FND_MSG_PUB.ADD;
239 RAISE EXCP_USER_DEFINED;
240 WHEN OTHERS THEN
241 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
242 fnd_message.set_token('ERR_FIELD', 'p_carrier', FALSE);
243 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
244 fnd_message.set_token('TABLE', 'ORG_FREIGHT_TL', FALSE);
245 FND_MSG_PUB.ADD;
246 RAISE EXCP_USER_DEFINED;
247 END;
248 END IF;
249
250 -- validate the creation_date
251 IF nvl(l_creation_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
252 Open l_Get_Creation_Date_Csr;
253 Fetch l_Get_Creation_Date_Csr into l_creation_date;
254 If l_Get_Creation_Date_Csr%NOTFOUND Then
255 Close l_Get_Creation_Date_Csr;
256 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
257 fnd_message.set_token('ERR_FIELD', 'p_cretaion_date', FALSE);
258 fnd_message.set_token('ROUTINE', G_PKG_NAME||'.'||l_api_name, FALSE);
259 fnd_message.set_token('TABLE', 'CSP_MOVEORDER_HEADERS', FALSE);
260 FND_MSG_PUB.ADD;
261 RAISE EXCP_USER_DEFINED;
262 End if;
263 Close l_Get_Creation_Date_Csr;
264 END IF;
265
266 IF nvl(l_last_update_date, fnd_api.g_miss_date) = fnd_api.g_miss_date THEN
267 l_last_update_date := sysdate;
268 END IF;
269 ELSE -- p_action_code = 2
270 IF nvl(p_header_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
271 FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
272 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_header_id', FALSE);
273 FND_MSG_PUB.ADD;
274 RAISE EXCP_USER_DEFINED;
275 ELSE
276 -- Validate whether the given header_id exists in the mtl_txn_request_headers table.
277 BEGIN
278 SELECT header_id INTO l_check_existence
279 FROM csp_moveorder_headers
280 WHERE header_id = p_header_id;
281
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 FND_MESSAGE.SET_NAME('CSP', 'CSP_INVALID_MOVEORDER');
285 FND_MESSAGE.SET_TOKEN('HEADER_ID', to_char(p_header_id), FALSE);
286 FND_MSG_PUB.ADD;
287 RAISE EXCP_USER_DEFINED;
288 WHEN OTHERS THEN
289 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_ERRORS');
290 fnd_message.set_token('ERR_FIELD', 'p_header_id', FALSE);
291 fnd_message.set_token('ROUTINE', l_pkg_api_name, FALSE);
292 fnd_message.set_token('TABLE', 'CSP_MOVEORDER_HEADERS', FALSE);
293 FND_MSG_PUB.ADD;
294 RAISE EXCP_USER_DEFINED;
295
296 END;
297 END IF;
298 END IF;
299
300
301 -- construct the move_over_headers record
302 l_moheader_rec.header_id := p_header_id;
303 l_moheader_rec.created_by := nvl(l_created_by, fnd_api.g_miss_num);
304 l_moheader_rec.creation_date := nvl(l_creation_date, fnd_api.g_miss_date);
305 l_moheader_rec.last_updated_by := nvl(l_last_updated_by, fnd_api.g_miss_num);
306 l_moheader_rec.last_update_date := nvl(l_last_update_date, fnd_api.g_miss_date);
307 l_moheader_rec.last_update_login := l_last_update_login;
308 l_moheader_rec.carrier := p_carrier;
309 l_moheader_rec.shipment_method := p_shipment_method;
310 l_moheader_rec.autoreceipt_flag := nvl(p_autoreceipt_flag, fnd_api.g_miss_char);
311 l_moheader_rec.attribute_category := p_attribute_category;
312 l_moheader_rec.attribute1 := p_attribute1;
313 l_moheader_rec.attribute2 := p_attribute2;
314 l_moheader_rec.attribute3 := p_attribute3;
315 l_moheader_rec.attribute4 := p_attribute4;
316 l_moheader_rec.attribute5 := p_attribute5;
317 l_moheader_rec.attribute6 := p_attribute6;
318 l_moheader_rec.attribute7 := p_attribute7;
319 l_moheader_rec.attribute8 := p_attribute8;
320 l_moheader_rec.attribute9 := p_attribute9;
321 l_moheader_rec.attribute10 := p_attribute10;
322 l_moheader_rec.attribute11 := p_attribute11;
323 l_moheader_rec.attribute12 := p_attribute12;
324 l_moheader_rec.attribute13 := p_attribute13;
325 l_moheader_rec.attribute14 := p_attribute14;
326 l_moheader_rec.attribute15 := p_attribute15;
327 l_moheader_rec.location_id := p_location_id;
328 l_moheader_rec.party_site_id := p_party_site_id;
329
330 if p_action_code = 0 then
331 -- call the private insert (create) procedure
332 CSP_ORDERHEADERS_PVT.Create_orderheaders(
333 P_Api_Version_Number => p_api_version_number,
334 P_Init_Msg_List => p_init_msg_list,
335 P_Commit => l_commit,
336 p_validation_level => l_validation_level,
337 P_MOH_Rec => l_moheader_rec,
338 X_HEADER_ID => l_header_id,
339 X_Return_Status => l_return_status,
340 X_Msg_Count => l_msg_count,
341 X_Msg_Data => l_msg_data
342 );
343
344 elsif p_action_code = 1 then
345 -- call the private update procedure
346 CSP_ORDERHEADERS_PVT.Update_orderheaders(
347 P_Api_Version_Number => p_api_version_number,
348 P_Init_Msg_List => p_init_msg_list,
349 P_Commit => l_commit,
350 p_validation_level => l_validation_level,
351 P_Identity_Salesforce_Id => null,
352 P_MOH_Rec => l_moheader_rec,
353 X_Return_Status => l_return_status,
354 X_Msg_Count => l_msg_count,
355 X_Msg_Data => l_msg_data);
356
357 else
358 -- call the private delete procedure
359 CSP_ORDERHEADERS_PVT.Delete_orderheaders(
360 P_Api_Version_Number => p_api_version_number,
361 P_Init_Msg_List => p_init_msg_list,
362 P_Commit => l_commit,
363 p_validation_level => l_validation_level,
364 P_Identity_Salesforce_Id => null,
365 P_MOH_Rec => l_moheader_rec,
366 X_Return_Status => l_return_status,
367 X_Msg_Count => l_msg_count,
368 X_Msg_Data => l_msg_data);
369 end if;
370
371 IF l_return_status <> fnd_api.g_ret_sts_success THEN
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374
375 IF fnd_api.to_boolean(p_commit) THEN
376 commit work;
377 END IF;
378
379 EXCEPTION
380 WHEN EXCP_USER_DEFINED THEN
381 Rollback to Validate_And_Write_PUB;
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 fnd_msg_pub.count_and_get
384 ( p_count => x_msg_count
385 , p_data => x_msg_data);
386 --for debugging purpose
387 --x_msg_data := l_msg_data;
388 WHEN FND_API.G_EXC_ERROR THEN
389 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
390 P_API_NAME => L_API_NAME
391 ,P_PKG_NAME => G_PKG_NAME
392 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
393 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
394 ,X_MSG_COUNT => X_MSG_COUNT
395 ,X_MSG_DATA => X_MSG_DATA
396 ,X_RETURN_STATUS => X_RETURN_STATUS);
397 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
398 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
399 P_API_NAME => L_API_NAME
400 ,P_PKG_NAME => G_PKG_NAME
401 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
402 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
403 ,X_MSG_COUNT => X_MSG_COUNT
404 ,X_MSG_DATA => X_MSG_DATA
405 ,X_RETURN_STATUS => X_RETURN_STATUS);
406 WHEN OTHERS THEN
407 Rollback to Validate_And_Write_PUB;
408 fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
409 fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
410 fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
411 fnd_msg_pub.add;
412 fnd_msg_pub.count_and_get
413 ( p_count => x_msg_count
414 , p_data => x_msg_data);
415 x_return_status := fnd_api.g_ret_sts_error;
416
417 END Validate_And_Write;
418
419 END CSP_TO_FORM_MOHEADERS;