1 PACKAGE BODY CSP_ORDERHEADERS_PVT AS
2 /* $Header: cspvtmhb.pls 115.14 2003/05/02 17:17:21 phegde ship $ */
3 -- Start of Comments
4 -- Package name : CSP_ORDERHEADERS_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE : CSP - api_version_number 1.0
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_ORDERHEADERS_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvtmhb.pls';
13
14 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
16
17 -- Hint: Primary key needs to be returned.
18 PROCEDURE Create_orderheaders(
19 P_Api_Version_Number IN NUMBER,
20 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
21 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
22 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
23 P_MOH_Rec IN MOH_Rec_Type := G_MISS_MOH_REC,
24 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
25 X_HEADER_ID OUT NOCOPY NUMBER,
26 X_Return_Status OUT NOCOPY VARCHAR2,
27 X_Msg_Count OUT NOCOPY NUMBER,
28 X_Msg_Data OUT NOCOPY VARCHAR2
29 )
30
31 IS
32 l_api_name CONSTANT VARCHAR2(30) := 'Create_orderheaders';
33 l_api_version_number CONSTANT NUMBER := 1.0;
34 l_return_status_full VARCHAR2(1);
35 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
36 BEGIN
37 -- Standard Start of API savepoint
38 SAVEPOINT CREATE_ORDERHEADERS_PVT;
39
40 -- Standard call to check for call compatibility.
41 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
42 p_api_version_number,
43 l_api_name,
44 G_PKG_NAME)
45 THEN
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 END IF;
48
49
50 -- Initialize message list if p_init_msg_list is set to TRUE.
51 IF FND_API.to_Boolean( p_init_msg_list )
52 THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55
56
57 -- Debug Message
58 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
59
60
61 -- Initialize API return status to SUCCESS
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63
64 --
65 -- API body
66 --
67
68 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
69 /*
70 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
71 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
72 THEN
73 AS_CALLOUT_PKG.Create_orderheaders_BC(
74 p_api_version_number => 2.0,
75 p_init_msg_list => FND_API.G_FALSE,
76 p_commit => FND_API.G_FALSE,
77 p_validation_level => p_validation_level,
78 P_MOH_Rec => P_MOH_Rec,
79 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
80 x_return_status => x_return_status,
81 x_msg_count => x_msg_count,
82 x_msg_data => x_msg_data);
83 END IF;
84 */
85
86 -- ******************************************************************
87 -- Validate Environment
88 -- ******************************************************************
89 IF FND_GLOBAL.User_Id IS NULL
90 THEN
91 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
92 THEN
93 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
94 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
95 FND_MSG_PUB.ADD;
96 END IF;
97 RAISE FND_API.G_EXC_ERROR;
98 END IF;
99
100
101 /*
102 The following codes of geting the current user associtated with a saleforce_id is commented out.
103 by CSP. 06-DEC-99.
104
105 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
106 p_api_version_number => 2.0
107 ,p_salesforce_id => NULL
108 ,x_return_status => x_return_status
109 ,x_msg_count => x_msg_count
110 ,x_msg_data => x_msg_data
111 ,x_sales_member_rec => l_identity_sales_member_rec);
112
113 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
114 x_error := 'In as_sales';
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117 */
118
119 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
120 THEN
121 -- Debug message
122 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Validate_orderheaders');
123
124 -- Invoke validation procedures
125 Validate_orderheaders(
126 p_init_msg_list => FND_API.G_FALSE,
127 p_validation_level => p_validation_level,
128 p_validation_mode => JTF_PLSQL_API.G_CREATE,
129 P_MOH_Rec => P_MOH_Rec,
130 x_return_status => x_return_status,
131 x_msg_count => x_msg_count,
132 x_msg_data => x_msg_data
133 );
134 END IF;
135
136 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
137 RAISE FND_API.G_EXC_ERROR;
138 END IF;
139
140 -- Hint: Add corresponding Master-Detail business logic here if necessary.
141
142 -- Debug Message
143 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Calling create table handler');
144
145
146 -- Invoke table handler(CSP_MOVEORDER_HEADERS_PKG.Insert_Row)
147 CSP_MOVEORDER_HEADERS_PKG.Insert_Row(
148 p_HEADER_ID => p_MOH_rec.HEADER_ID,
149 p_CREATED_BY => p_MOH_rec.created_by,
150 p_CREATION_DATE => p_MOH_rec.creation_date,
151 p_LAST_UPDATED_BY => p_MOH_rec.last_updated_by,
152 p_LAST_UPDATE_DATE => p_MOH_rec.last_update_date,
153 p_LAST_UPDATE_LOGIN => p_MOH_rec.last_update_login,
154 p_CARRIER => p_MOH_rec.CARRIER,
155 p_SHIPMENT_METHOD => p_MOH_rec.SHIPMENT_METHOD,
156 p_AUTORECEIPT_FLAG => p_MOH_rec.AUTORECEIPT_FLAG,
157 p_ATTRIBUTE_CATEGORY => p_MOH_rec.ATTRIBUTE_CATEGORY,
158 p_ATTRIBUTE1 => p_MOH_rec.ATTRIBUTE1,
159 p_ATTRIBUTE2 => p_MOH_rec.ATTRIBUTE2,
160 p_ATTRIBUTE3 => p_MOH_rec.ATTRIBUTE3,
161 p_ATTRIBUTE4 => p_MOH_rec.ATTRIBUTE4,
162 p_ATTRIBUTE5 => p_MOH_rec.ATTRIBUTE5,
163 p_ATTRIBUTE6 => p_MOH_rec.ATTRIBUTE6,
164 p_ATTRIBUTE7 => p_MOH_rec.ATTRIBUTE7,
165 p_ATTRIBUTE8 => p_MOH_rec.ATTRIBUTE8,
166 p_ATTRIBUTE9 => p_MOH_rec.ATTRIBUTE9,
167 p_ATTRIBUTE10 => p_MOH_rec.ATTRIBUTE10,
168 p_ATTRIBUTE11 => p_MOH_rec.ATTRIBUTE11,
169 p_ATTRIBUTE12 => p_MOH_rec.ATTRIBUTE12,
170 p_ATTRIBUTE13 => p_MOH_rec.ATTRIBUTE13,
171 p_ATTRIBUTE14 => p_MOH_rec.ATTRIBUTE14,
172 p_ATTRIBUTE15 => p_MOH_rec.ATTRIBUTE15,
173 P_LOCATION_ID => P_MOH_REC.LOCATION_ID,
174 p_PARTY_SITE_ID => P_MOH_REC.PARTY_SITE_ID
175 );
176 -- Hint: Primary key should be returned.
177 -- x_HEADER_ID := px_HEADER_ID;
178
179 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
180 RAISE FND_API.G_EXC_ERROR;
181 END IF;
182
183 --
184 -- End of API body
185 --
186
187 -- Standard check for p_commit
188 IF FND_API.to_Boolean( p_commit )
189 THEN
190 COMMIT WORK;
191 END IF;
192
193
194 -- Debug Message
195 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
196
197
198 -- Standard call to get message count and if count is 1, get message info.
199 FND_MSG_PUB.Count_And_Get
200 ( p_count => x_msg_count,
201 p_data => x_msg_data
202 );
203
204 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
205 /*
206 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
207 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
208 THEN
209 AS_CALLOUT_PKG.Create_orderheaders_AC(
210 p_api_version_number => 2.0,
211 p_init_msg_list => FND_API.G_FALSE,
212 p_commit => FND_API.G_FALSE,
213 p_validation_level => p_validation_level,
214 P_MOH_Rec => P_MOH_Rec,
215 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
216 x_return_status => x_return_status,
217 x_msg_count => x_msg_count,
218 x_msg_data => x_msg_data);
219 END IF;
220 */
221 EXCEPTION
222 WHEN FND_API.G_EXC_ERROR THEN
223 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
224 P_API_NAME => L_API_NAME
225 ,P_PKG_NAME => G_PKG_NAME
226 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
227 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
228 ,X_MSG_COUNT => X_MSG_COUNT
229 ,X_MSG_DATA => X_MSG_DATA
230 ,X_RETURN_STATUS => X_RETURN_STATUS);
231
232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
233 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
234 P_API_NAME => L_API_NAME
235 ,P_PKG_NAME => G_PKG_NAME
236 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
237 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
238 ,X_MSG_COUNT => X_MSG_COUNT
239 ,X_MSG_DATA => X_MSG_DATA
240 ,X_RETURN_STATUS => X_RETURN_STATUS);
241
242 WHEN OTHERS THEN
243 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
244 P_API_NAME => L_API_NAME
245 ,P_PKG_NAME => G_PKG_NAME
246 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
247 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
248 ,X_MSG_COUNT => X_MSG_COUNT
249 ,X_MSG_DATA => X_MSG_DATA
250 ,X_RETURN_STATUS => X_RETURN_STATUS);
251 End Create_orderheaders;
252
253
254 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
255 PROCEDURE Update_orderheaders(
256 P_Api_Version_Number IN NUMBER,
257 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
258 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
259 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
260 P_Identity_Salesforce_Id IN NUMBER := NULL,
261 P_MOH_Rec IN MOH_Rec_Type,
262 X_Return_Status OUT NOCOPY VARCHAR2,
263 X_Msg_Count OUT NOCOPY NUMBER,
264 X_Msg_Data OUT NOCOPY VARCHAR2
265 )
266
267 IS
268
269 /*
270 Cursor C_Get_orderheaders(HEADER_ID Number) IS
271 Select rowid,
272 HEADER_ID,
273 CREATED_BY,
274 CREATION_DATE,
275 LAST_UPDATED_BY,
276 LAST_UPDATE_DATE,
277 LAST_UPDATE_LOGIN,
278 LOCATION_ID,
279 CARRIER,
280 SHIPMENT_METHOD,
281 AUTORECEIPT_FLAG,
282 ATTRIBUTE_CATEGORY,
283 ATTRIBUTE1,
284 ATTRIBUTE2,
285 ATTRIBUTE3,
286 ATTRIBUTE4,
287 ATTRIBUTE5,
288 ATTRIBUTE6,
289 ATTRIBUTE7,
290 ATTRIBUTE8,
291 ATTRIBUTE9,
292 ATTRIBUTE10,
293 ATTRIBUTE11,
294 ATTRIBUTE12,
295 ATTRIBUTE13,
296 ATTRIBUTE14,
297 ATTRIBUTE15,
298 ADDRESS1,
299 ADDRESS2,
300 ADDRESS3,
301 ADDRESS4,
302 CITY,
303 POSTAL_CODE,
304 STATE,
305 PROVINCE,
306 COUNTRY
307 From CSP_MOVEORDER_HEADERS
308 -- Hint: Developer need to provide Where clause
309 For Update NOWAIT;
310 */
311 l_api_name CONSTANT VARCHAR2(30) := 'Update_orderheaders';
312 l_api_version_number CONSTANT NUMBER := 1.0;
313 -- Local Variables
314 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
315 l_ref_MOH_rec CSP_orderheaders_PVT.MOH_Rec_Type;
316 l_tar_MOH_rec CSP_orderheaders_PVT.MOH_Rec_Type := P_MOH_Rec;
317 l_rowid ROWID;
318 BEGIN
319 -- Standard Start of API savepoint
320 SAVEPOINT UPDATE_ORDERHEADERS_PVT;
321
322 -- Standard call to check for call compatibility.
323 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
324 p_api_version_number,
325 l_api_name,
326 G_PKG_NAME)
327 THEN
328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
329 END IF;
330
331
332 -- Initialize message list if p_init_msg_list is set to TRUE.
333 IF FND_API.to_Boolean( p_init_msg_list )
334 THEN
335 FND_MSG_PUB.initialize;
336 END IF;
337
338
339 -- Debug Message
340 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
341
342
343 -- Initialize API return status to SUCCESS
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345
346 --
347 -- Api body
348 --
349 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
350 /*
351 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
352 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
353 THEN
354 AS_CALLOUT_PKG.Update_orderheaders_BU(
355 p_api_version_number => 2.0,
356 p_init_msg_list => FND_API.G_FALSE,
357 p_commit => FND_API.G_FALSE,
358 p_validation_level => p_validation_level,
359 p_identity_salesforce_id => p_identity_salesforce_id,
360 P_MOH_Rec => P_MOH_Rec,
361 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
362 x_return_status => x_return_status,
363 x_msg_count => x_msg_count,
364 x_msg_data => x_msg_data);
365 END IF;
366 */
367
368
369 /* Comment out the Get_CurrentUser by CSP. 06-DEC-99.
370
371 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
372 p_api_version_number => 2.0
373 ,p_salesforce_id => p_identity_salesforce_id
374 ,x_return_status => x_return_status
375 ,x_msg_count => x_msg_count
376 ,x_msg_data => x_msg_data
377 ,x_sales_member_rec => l_identity_sales_member_rec);
378
379 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
380 RAISE FND_API.G_EXC_ERROR;
381 END IF;
382 */
383 -- Debug Message
384 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: - Open Cursor to Select');
385
386 /*
387 Open C_Get_orderheaders( l_tar_MOH_rec.HEADER_ID);
388
389 Fetch C_Get_orderheaders into
390 l_rowid,
391 l_ref_MOH_rec.HEADER_ID,
392 l_ref_MOH_rec.CREATED_BY,
393 l_ref_MOH_rec.CREATION_DATE,
394 l_ref_MOH_rec.LAST_UPDATED_BY,
395 l_ref_MOH_rec.LAST_UPDATE_DATE,
396 l_ref_MOH_rec.LAST_UPDATE_LOGIN,
397 l_ref_MOH_rec.LOCATION_ID,
398 l_ref_MOH_rec.CARRIER,
399 l_ref_MOH_rec.SHIPMENT_METHOD,
400 l_ref_MOH_rec.AUTORECEIPT_FLAG,
401 l_ref_MOH_rec.ATTRIBUTE_CATEGORY,
402 l_ref_MOH_rec.ATTRIBUTE1,
403 l_ref_MOH_rec.ATTRIBUTE2,
404 l_ref_MOH_rec.ATTRIBUTE3,
405 l_ref_MOH_rec.ATTRIBUTE4,
406 l_ref_MOH_rec.ATTRIBUTE5,
407 l_ref_MOH_rec.ATTRIBUTE6,
408 l_ref_MOH_rec.ATTRIBUTE7,
409 l_ref_MOH_rec.ATTRIBUTE8,
410 l_ref_MOH_rec.ATTRIBUTE9,
411 l_ref_MOH_rec.ATTRIBUTE10,
412 l_ref_MOH_rec.ATTRIBUTE11,
413 l_ref_MOH_rec.ATTRIBUTE12,
414 l_ref_MOH_rec.ATTRIBUTE13,
415 l_ref_MOH_rec.ATTRIBUTE14,
416 l_ref_MOH_rec.ATTRIBUTE15,
417 l_ref_MOH_rec.ADDRESS1,
418 l_ref_MOH_rec.ADDRESS2,
419 l_ref_MOH_rec.ADDRESS3,
420 l_ref_MOH_rec.ADDRESS4,
421 l_ref_MOH_rec.CITY,
422 l_ref_MOH_rec.POSTAL_CODE,
423 l_ref_MOH_rec.STATE,
424 l_ref_MOH_rec.PROVINCE,
425 l_ref_MOH_rec.COUNTRY;
426
427 If ( C_Get_orderheaders%NOTFOUND) Then
428 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
429 THEN
430 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_UPDATE_TARGET');
431 FND_MESSAGE.Set_Token ('INFO', 'orderheaders', FALSE);
432 FND_MSG_PUB.Add;
433 END IF;
434 raise FND_API.G_EXC_ERROR;
435 END IF;
436 -- Debug Message
437 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: - Close Cursor');
438 Close C_Get_orderheaders;
439 */
440
441 /* uncommented out and will
442 If (l_tar_MOH_rec.last_update_date is NULL or
443 l_tar_MOH_rec.last_update_date = FND_API.G_MISS_Date ) Then
444 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
445 THEN
446 FND_MESSAGE.Set_Name('CSP', 'API_MISSING_ID');
447 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
448 FND_MSG_PUB.ADD;
449 END IF;
450 raise FND_API.G_EXC_ERROR;
451 End if;
452 -- Check Whether record has been changed by someone else
453 If (l_tar_MOH_rec.last_update_date <> l_ref_MOH_rec.last_update_date) Then
454 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
455 THEN
456 FND_MESSAGE.Set_Name('CSP', 'API_RECORD_CHANGED');
457 FND_MESSAGE.Set_Token('INFO', 'orderheaders', FALSE);
458 FND_MSG_PUB.ADD;
459 END IF;
460 raise FND_API.G_EXC_ERROR;
461 End if;
462 */
463 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
464 THEN
465 -- Debug message
466 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Validate_orderheaders');
467
468 -- Invoke validation procedures
469 Validate_orderheaders(
470 p_init_msg_list => FND_API.G_FALSE,
471 p_validation_level => p_validation_level,
472 p_validation_mode => JTF_PLSQL_API.G_UPDATE,
473 P_MOH_Rec => P_MOH_Rec,
474 x_return_status => x_return_status,
475 x_msg_count => x_msg_count,
476 x_msg_data => x_msg_data);
477 END IF;
478
479 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482
483 -- Hint: Add corresponding Master-Detail business logic here if necessary.
484
485 -- Debug Message
486 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Calling update table handler');
487
488 -- Invoke table handler(CSP_MOVEORDER_HEADERS_PKG.Update_Row)
489 CSP_MOVEORDER_HEADERS_PKG.Update_Row(
490 p_HEADER_ID => p_MOH_rec.HEADER_ID,
491 p_CREATED_BY => p_MOH_rec.created_by,
492 p_CREATION_DATE => p_MOH_rec.creation_date,
493 p_LAST_UPDATED_BY => p_MOH_rec.LAST_UPDATED_BY,
494 p_LAST_UPDATE_DATE => SYSDATE,
495 p_LAST_UPDATE_LOGIN => p_MOH_rec.LAST_UPDATE_LOGIN,
496 p_CARRIER => p_MOH_rec.CARRIER,
497 p_SHIPMENT_METHOD => p_MOH_rec.SHIPMENT_METHOD,
498 p_AUTORECEIPT_FLAG => p_MOH_rec.AUTORECEIPT_FLAG,
499 p_ATTRIBUTE_CATEGORY => p_MOH_rec.ATTRIBUTE_CATEGORY,
500 p_ATTRIBUTE1 => p_MOH_rec.ATTRIBUTE1,
501 p_ATTRIBUTE2 => p_MOH_rec.ATTRIBUTE2,
502 p_ATTRIBUTE3 => p_MOH_rec.ATTRIBUTE3,
503 p_ATTRIBUTE4 => p_MOH_rec.ATTRIBUTE4,
504 p_ATTRIBUTE5 => p_MOH_rec.ATTRIBUTE5,
505 p_ATTRIBUTE6 => p_MOH_rec.ATTRIBUTE6,
506 p_ATTRIBUTE7 => p_MOH_rec.ATTRIBUTE7,
507 p_ATTRIBUTE8 => p_MOH_rec.ATTRIBUTE8,
508 p_ATTRIBUTE9 => p_MOH_rec.ATTRIBUTE9,
509 p_ATTRIBUTE10 => p_MOH_rec.ATTRIBUTE10,
510 p_ATTRIBUTE11 => p_MOH_rec.ATTRIBUTE11,
511 p_ATTRIBUTE12 => p_MOH_rec.ATTRIBUTE12,
512 p_ATTRIBUTE13 => p_MOH_rec.ATTRIBUTE13,
513 p_ATTRIBUTE14 => p_MOH_rec.ATTRIBUTE14,
514 p_ATTRIBUTE15 => p_MOH_rec.ATTRIBUTE15,
515 P_LOCATION_ID => P_MOH_REC.LOCATION_ID,
516 p_PARTY_SITE_ID => P_MOH_REC.PARTY_SITE_ID
517 /*
518 p_ADDRESS1 => p_MOH_rec.ADDRESS1,
519 p_ADDRESS2 => p_MOH_rec.ADDRESS2,
520 p_ADDRESS3 => p_MOH_rec.ADDRESS3,
521 p_ADDRESS4 => p_MOH_rec.ADDRESS4,
522 p_CITY => p_MOH_rec.CITY,
523 p_POSTAL_CODE => p_MOH_rec.POSTAL_CODE,
524 p_STATE => p_MOH_rec.STATE,
525 p_PROVINCE => p_MOH_rec.PROVINCE,
526 p_COUNTRY => p_MOH_rec.COUNTRY */
527 );
528 --
529 -- End of API body.
530 --
531
532 -- Standard check for p_commit
533 IF FND_API.to_Boolean( p_commit )
534 THEN
535 COMMIT WORK;
536 END IF;
537
538
539 -- Debug Message
540 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
541
542
543 -- Standard call to get message count and if count is 1, get message info.
544 FND_MSG_PUB.Count_And_Get
545 ( p_count => x_msg_count,
546 p_data => x_msg_data
547 );
548
549 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
550 /*
551 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
552 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
553 THEN
554 AS_CALLOUT_PKG.Update_orderheaders_AU(
555 p_api_version_number => 2.0,
556 p_init_msg_list => FND_API.G_FALSE,
557 p_commit => FND_API.G_FALSE,
558 p_validation_level => p_validation_level,
559 p_identity_salesforce_id => p_identity_salesforce_id,
560 P_MOH_Rec => P_MOH_Rec,
561 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
562 x_return_status => x_return_status,
563 x_msg_count => x_msg_count,
564 x_msg_data => x_msg_data);
565 END IF;
566 */
567 EXCEPTION
568 WHEN FND_API.G_EXC_ERROR THEN
569 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
570 P_API_NAME => L_API_NAME
571 ,P_PKG_NAME => G_PKG_NAME
572 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
573 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
574 ,X_MSG_COUNT => X_MSG_COUNT
575 ,X_MSG_DATA => X_MSG_DATA
576 ,X_RETURN_STATUS => X_RETURN_STATUS);
577
578 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
579 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
580 P_API_NAME => L_API_NAME
581 ,P_PKG_NAME => G_PKG_NAME
582 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
583 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
584 ,X_MSG_COUNT => X_MSG_COUNT
585 ,X_MSG_DATA => X_MSG_DATA
586 ,X_RETURN_STATUS => X_RETURN_STATUS);
587
588 WHEN OTHERS THEN
589 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
590 P_API_NAME => L_API_NAME
591 ,P_PKG_NAME => G_PKG_NAME
592 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
593 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
594 ,X_MSG_COUNT => X_MSG_COUNT
595 ,X_MSG_DATA => X_MSG_DATA
596 ,X_RETURN_STATUS => X_RETURN_STATUS);
597 End Update_orderheaders;
598
599
600 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
601 -- The Master delete procedure may not be needed depends on different business requirements.
602 PROCEDURE Delete_orderheaders(
603 P_Api_Version_Number IN NUMBER,
604 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
605 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
606 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
607 P_identity_salesforce_id IN NUMBER := NULL,
608 P_MOH_Rec IN MOH_Rec_Type,
609 X_Return_Status OUT NOCOPY VARCHAR2,
610 X_Msg_Count OUT NOCOPY NUMBER,
611 X_Msg_Data OUT NOCOPY VARCHAR2
612 )
613
614 IS
615 l_api_name CONSTANT VARCHAR2(30) := 'Delete_orderheaders';
616 l_api_version_number CONSTANT NUMBER := 1.0;
617 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
618 BEGIN
619 -- Standard Start of API savepoint
620 SAVEPOINT DELETE_ORDERHEADERS_PVT;
621
622 -- Standard call to check for call compatibility.
623 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
624 p_api_version_number,
625 l_api_name,
626 G_PKG_NAME)
627 THEN
628 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
629 END IF;
630
631
632 -- Initialize message list if p_init_msg_list is set to TRUE.
633 IF FND_API.to_Boolean( p_init_msg_list )
634 THEN
635 FND_MSG_PUB.initialize;
636 END IF;
637
638
639 -- Debug Message
640 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
641
642
643 -- Initialize API return status to SUCCESS
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645
646 --
647 -- Api body
648 --
649 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
650 /*
651 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
652 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
653 THEN
654 AS_CALLOUT_PKG.Delete_orderheaders_BD(
655 p_api_version_number => 2.0,
656 p_init_msg_list => FND_API.G_FALSE,
657 p_commit => FND_API.G_FALSE,
658 p_validation_level => p_validation_level,
659 p_identity_salesforce_id => p_identity_salesforce_id,
660 P_MOH_Rec => P_MOH_Rec,
661 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
662 x_return_status => x_return_status,
663 x_msg_count => x_msg_count,
664 x_msg_data => x_msg_data);
665 END IF;
666 */
667
668 /* Comment out by CSP. 06-DEC-1999.
669
670 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
671 p_api_version_number => 2.0
672 ,p_salesforce_id => p_identity_salesforce_id
673 ,x_return_status => x_return_status
674 ,x_msg_count => x_msg_count
675 ,x_msg_data => x_msg_data
676 ,x_sales_member_rec => l_identity_sales_member_rec);
677
678 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681 */
682 -- Debug Message
683 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Calling delete table handler');
684
685 -- Invoke table handler(CSP_MOVEORDER_HEADERS_PKG.Delete_Row)
686 CSP_MOVEORDER_HEADERS_PKG.Delete_Row(
687 p_HEADER_ID => p_MOH_rec.HEADER_ID);
688 --
689 -- End of API body
690 --
691
692 -- Standard check for p_commit
693 IF FND_API.to_Boolean( p_commit )
694 THEN
695 COMMIT WORK;
696 END IF;
697
698
699 -- Debug Message
700 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
701
702
703 -- Standard call to get message count and if count is 1, get message info.
704 FND_MSG_PUB.Count_And_Get
705 ( p_count => x_msg_count,
706 p_data => x_msg_data
707 );
708
709 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
710 /*
711 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
712 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
713 THEN
714 AS_CALLOUT_PKG.Delete_orderheaders_AD(
715 p_api_version_number => 2.0,
716 p_init_msg_list => FND_API.G_FALSE,
717 p_commit => FND_API.G_FALSE,
718 p_validation_level => p_validation_level,
719 p_identity_salesforce_id => p_identity_salesforce_id,
720 P_MOH_Rec => P_MOH_Rec,
721 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
722 x_return_status => x_return_status,
723 x_msg_count => x_msg_count,
724 x_msg_data => x_msg_data);
725 END IF;
726 */
727 EXCEPTION
728 WHEN FND_API.G_EXC_ERROR THEN
729 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
730 P_API_NAME => L_API_NAME
731 ,P_PKG_NAME => G_PKG_NAME
732 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
733 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
734 ,X_MSG_COUNT => X_MSG_COUNT
735 ,X_MSG_DATA => X_MSG_DATA
736 ,X_RETURN_STATUS => X_RETURN_STATUS);
737
738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
739 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
740 P_API_NAME => L_API_NAME
741 ,P_PKG_NAME => G_PKG_NAME
742 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
743 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
744 ,X_MSG_COUNT => X_MSG_COUNT
745 ,X_MSG_DATA => X_MSG_DATA
746 ,X_RETURN_STATUS => X_RETURN_STATUS);
747
748 WHEN OTHERS THEN
749 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
750 P_API_NAME => L_API_NAME
751 ,P_PKG_NAME => G_PKG_NAME
752 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
753 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
754 ,X_MSG_COUNT => X_MSG_COUNT
755 ,X_MSG_DATA => X_MSG_DATA
756 ,X_RETURN_STATUS => X_RETURN_STATUS);
757 End Delete_orderheaders;
758
759
760 -- This procudure defines the columns for the Dynamic SQL.
761 PROCEDURE Define_Columns(
762 P_MOH_Rec IN CSP_ORDERHEADERS_PUB.MOH_Rec_Type,
763 p_cur_get_MOH IN NUMBER
764 )
765 IS
766 BEGIN
767 -- Debug Message
768 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Define Columns Begins');
769
770 -- define all columns for CSP_MOVEORDER_HEADERS_V view
771 dbms_sql.define_column(p_cur_get_MOH, 1, P_MOH_Rec.HEADER_ID);
772 dbms_sql.define_column(p_cur_get_MOH, 2, P_MOH_Rec.CARRIER, 50);
773 dbms_sql.define_column(p_cur_get_MOH, 3, P_MOH_Rec.SHIPMENT_METHOD, 50);
774 dbms_sql.define_column(p_cur_get_MOH, 4, P_MOH_Rec.AUTORECEIPT_FLAG, 10);
775 dbms_sql.define_column(p_cur_get_MOH, 5, P_MOH_Rec.ATTRIBUTE_CATEGORY, 30);
776 dbms_sql.define_column(p_cur_get_MOH, 6, P_MOH_Rec.LOCATION_ID);
777 /*
778 dbms_sql.define_column(p_cur_get_MOH, 7, P_MOH_Rec.ADDRESS1, 240);
779 dbms_sql.define_column(p_cur_get_MOH, 8, P_MOH_Rec.ADDRESS2, 240);
780 dbms_sql.define_column(p_cur_get_MOH, 9, P_MOH_Rec.ADDRESS3, 240);
781 dbms_sql.define_column(p_cur_get_MOH, 10, P_MOH_Rec.ADDRESS4, 240);
782 dbms_sql.define_column(p_cur_get_MOH, 11, P_MOH_Rec.CITY, 60);
783 dbms_sql.define_column(p_cur_get_MOH, 12, P_MOH_Rec.POSTAL_CODE, 60);
784 dbms_sql.define_column(p_cur_get_MOH, 13, P_MOH_Rec.STATE, 60);
785 dbms_sql.define_column(p_cur_get_MOH, 14, P_MOH_Rec.PROVINCE, 60);
786 dbms_sql.define_column(p_cur_get_MOH, 15, P_MOH_Rec.COUNTRY, 60); */
787
788 -- Debug Message
789 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Define Columns Ends');
790 END Define_Columns;
791
792 -- This procudure gets column values by the Dynamic SQL.
793 PROCEDURE Get_Column_Values(
794 p_cur_get_MOH IN NUMBER,
795 X_MOH_Rec OUT NOCOPY CSP_ORDERHEADERS_PUB.MOH_Rec_Type
796 )
797 IS
798 BEGIN
799 -- Debug Message
800 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Get Column Values Begins');
801
802 -- get all column values for CSP_MOVEORDER_HEADERS_V table
803 dbms_sql.column_value(p_cur_get_MOH, 1, X_MOH_Rec.HEADER_ID);
804 dbms_sql.column_value(p_cur_get_MOH, 2, X_MOH_Rec.CARRIER);
805 dbms_sql.column_value(p_cur_get_MOH, 3, X_MOH_Rec.SHIPMENT_METHOD);
806 dbms_sql.column_value(p_cur_get_MOH, 4, X_MOH_Rec.AUTORECEIPT_FLAG);
807 dbms_sql.column_value(p_cur_get_MOH, 5, X_MOH_Rec.ATTRIBUTE_CATEGORY);
808 dbms_sql.column_value(p_cur_get_MOH, 6, X_MOH_Rec.LOCATION_ID);
809 /*
810 dbms_sql.column_value(p_cur_get_MOH, 7, X_MOH_Rec.ADDRESS1);
811 dbms_sql.column_value(p_cur_get_MOH, 8, X_MOH_Rec.ADDRESS2);
812 dbms_sql.column_value(p_cur_get_MOH, 9, X_MOH_Rec.ADDRESS3);
813 dbms_sql.column_value(p_cur_get_MOH, 10, X_MOH_Rec.ADDRESS4);
814 dbms_sql.column_value(p_cur_get_MOH, 11, X_MOH_Rec.CITY);
815 dbms_sql.column_value(p_cur_get_MOH, 12, X_MOH_Rec.POSTAL_CODE);
816 dbms_sql.column_value(p_cur_get_MOH, 13, X_MOH_Rec.STATE);
817 dbms_sql.column_value(p_cur_get_MOH, 14, X_MOH_Rec.PROVINCE);
818 dbms_sql.column_value(p_cur_get_MOH, 15, X_MOH_Rec.COUNTRY); */
819
820 -- Debug Message
821 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Get Column Values Ends');
822 END Get_Column_Values;
823
824 PROCEDURE Gen_MOH_order_cl(
825 p_order_by_rec IN CSP_ORDERHEADERS_PUB.MOH_sort_rec_type,
826 x_order_by_cl OUT NOCOPY VARCHAR2,
827 x_return_status OUT NOCOPY VARCHAR2,
828 x_msg_count OUT NOCOPY NUMBER,
829 x_msg_data OUT NOCOPY VARCHAR2
830 )
831 IS
832 l_order_by_cl VARCHAR2(1000) := NULL;
833 l_util_order_by_tbl JTF_PLSQL_API.Util_order_by_tbl_type;
834 BEGIN
835 -- Debug Message
836 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Order by Begins');
837
838 -- Hint: Developer should add more statements according to CSP_sort_rec_type
839 -- Ex:
840 -- l_util_order_by_tbl(1).col_choice := p_order_by_rec.customer_name;
841 -- l_util_order_by_tbl(1).col_name := 'Customer_Name';
842
843 -- Debug Message
844 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Invoke JTF_PLSQL_API.Translate_OrderBy');
845
846 JTF_PLSQL_API.Translate_OrderBy(
847 p_api_version_number => 1.0
848 ,p_init_msg_list => FND_API.G_FALSE
849 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
850 ,p_order_by_tbl => l_util_order_by_tbl
851 ,x_order_by_clause => l_order_by_cl
852 ,x_return_status => x_return_status
853 ,x_msg_count => x_msg_count
854 ,x_msg_data => x_msg_data);
855
856 IF(l_order_by_cl IS NOT NULL) THEN
857 x_order_by_cl := 'order by' || l_order_by_cl;
858 ELSE
859 x_order_by_cl := NULL;
860 END IF;
861
862 -- Debug Message
863 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Order by Ends');
864 END Gen_MOH_order_cl;
865
866 -- This procedure bind the variables for the Dynamic SQL
867 PROCEDURE Bind(
868 P_MOH_Rec IN CSP_ORDERHEADERS_PUB.MOH_Rec_Type,
869 -- Hint: Add more binding variables here
870 p_cur_get_MOH IN NUMBER
871 )
872 IS
873 BEGIN
874 -- Bind variables
875 -- Only those that are not NULL
876 -- Debug Message
877 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Bind Variables Begins');
878
879 -- The following example applies to all columns,
880 -- developers can copy and paste them.
881 IF( (P_MOH_Rec.HEADER_ID IS NOT NULL) AND (P_MOH_Rec.HEADER_ID <> FND_API.G_MISS_NUM) )
882 THEN
883 DBMS_SQL.BIND_VARIABLE(p_cur_get_MOH, ':p_HEADER_ID', P_MOH_Rec.HEADER_ID);
884 END IF;
885
886 -- Debug Message
887 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Bind Variables Ends');
888 END Bind;
889
890 PROCEDURE Gen_Select(
891 x_select_cl OUT NOCOPY VARCHAR2
892 )
893 IS
894 BEGIN
895 -- Debug Message
896 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Select Begins');
897
898 x_select_cl := 'Select ' ||
899 'CSP_MOVEORDER_HEADERS_V.HEADER_ID,' ||
900 'CSP_MOVEORDER_HEADERS_V.CREATED_BY,' ||
901 'CSP_MOVEORDER_HEADERS_V.CREATION_DATE,' ||
902 'CSP_MOVEORDER_HEADERS_V.LAST_UPDATED_BY,' ||
903 'CSP_MOVEORDER_HEADERS_V.LAST_UPDATE_DATE,' ||
904 'CSP_MOVEORDER_HEADERS_V.LAST_UPDATE_LOGIN,' ||
905 'CSP_MOVEORDER_HEADERS_V.LOCATION_ID,' ||
906 'CSP_MOVEORDER_HEADERS_V.CARRIER,' ||
907 'CSP_MOVEORDER_HEADERS_V.SHIPMENT_METHOD,' ||
908 'CSP_MOVEORDER_HEADERS_V.AUTORECEIPT_FLAG,' ||
909 'CSP_MOVEORDER_HEADERS_V.ATTRIBUTE_CATEGORY,' ||
910 /* 'CSP_MOVEORDER_HEADERS_V.ADDRESS1,' ||
911 'CSP_MOVEORDER_HEADERS_V.ADDRESS2,' ||
912 'CSP_MOVEORDER_HEADERS_V.ADDRESS3,' ||
913 'CSP_MOVEORDER_HEADERS_V.ADDRESS4,' ||
914 'CSP_MOVEORDER_HEADERS_V.CITY,' ||
915 'CSP_MOVEORDER_HEADERS_V.POSTAL_CODE,' ||
916 'CSP_MOVEORDER_HEADERS_V.STATE,' ||
917 'CSP_MOVEORDER_HEADERS_V.PROVINCE,' ||
918 'CSP_MOVEORDER_HEADERS_V.COUNTRY,' || */
919 'from CSP_MOVEORDER_HEADERS_V';
920 -- Debug Message
921 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Select Ends');
922
923 END Gen_Select;
924
925 PROCEDURE Gen_MOH_Where(
926 P_MOH_Rec IN CSP_ORDERHEADERS_PUB.MOH_Rec_Type,
927 x_MOH_where OUT NOCOPY VARCHAR2
928 )
929 IS
930 -- cursors to check if wildcard values '%' and '_' have been passed
931 -- as item values
932 CURSOR c_chk_str1(p_rec_item VARCHAR2) IS
933 SELECT INSTR(p_rec_item, '%', 1, 1)
934 FROM DUAL;
935 CURSOR c_chk_str2(p_rec_item VARCHAR2) IS
936 SELECT INSTR(p_rec_item, '_', 1, 1)
937 FROM DUAL;
938
939 -- return values from cursors
940 str_csr1 NUMBER;
941 str_csr2 NUMBER;
942 l_operator VARCHAR2(10);
943 BEGIN
944 -- Debug Message
945 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Where Begins');
946
947 -- There are three examples for each kind of datatype:
948 -- NUMBER, DATE, VARCHAR2.
949 -- Developer can copy and paste the following codes for your own record.
950
951 -- example for NUMBER datatype
952 IF( (P_MOH_Rec.HEADER_ID IS NOT NULL) AND (P_MOH_Rec.HEADER_ID <> FND_API.G_MISS_NUM) )
953 THEN
954 IF(x_MOH_where IS NULL) THEN
955 x_MOH_where := 'Where';
956 ELSE
957 x_MOH_where := x_MOH_where || ' AND ';
958 END IF;
959 x_MOH_where := x_MOH_where || 'P_MOH_Rec.HEADER_ID = :p_HEADER_ID';
960 END IF;
961
962 -- example for DATE datatype
963 IF( (P_MOH_Rec.CREATION_DATE IS NOT NULL) AND (P_MOH_Rec.CREATION_DATE <> FND_API.G_MISS_DATE) )
964 THEN
965 -- check if item value contains '%' wildcard
966 OPEN c_chk_str1(P_MOH_Rec.CREATION_DATE);
967 FETCH c_chk_str1 INTO str_csr1;
968 CLOSE c_chk_str1;
969
970 IF(str_csr1 <> 0) THEN
971 l_operator := ' LIKE ';
972 ELSE
973 l_operator := ' = ';
974 END IF;
975
976 -- check if item value contains '_' wildcard
977 OPEN c_chk_str2(P_MOH_Rec.CREATION_DATE);
978 FETCH c_chk_str2 INTO str_csr2;
979 CLOSE c_chk_str2;
980
981 IF(str_csr2 <> 0) THEN
982 l_operator := ' LIKE ';
983 ELSE
984 l_operator := ' = ';
985 END IF;
986
987 IF(x_MOH_where IS NULL) THEN
988 x_MOH_where := 'Where ';
989 ELSE
990 x_MOH_where := x_MOH_where || ' AND ';
991 END IF;
992 x_MOH_where := x_MOH_where || 'P_MOH_Rec.CREATION_DATE ' || l_operator || ' :p_CREATION_DATE';
993 END IF;
994
995 -- example for VARCHAR2 datatype
996 IF( (P_MOH_Rec.CARRIER IS NOT NULL) AND (P_MOH_Rec.CARRIER <> FND_API.G_MISS_CHAR) )
997 THEN
998 -- check if item value contains '%' wildcard
999 OPEN c_chk_str1(P_MOH_Rec.CARRIER);
1000 FETCH c_chk_str1 INTO str_csr1;
1001 CLOSE c_chk_str1;
1002
1003 IF(str_csr1 <> 0) THEN
1004 l_operator := ' LIKE ';
1005 ELSE
1006 l_operator := ' = ';
1007 END IF;
1008
1009 -- check if item value contains '_' wildcard
1010 OPEN c_chk_str2(P_MOH_Rec.CARRIER);
1011 FETCH c_chk_str2 INTO str_csr2;
1012 CLOSE c_chk_str2;
1013
1014 IF(str_csr2 <> 0) THEN
1015 l_operator := ' LIKE ';
1016 ELSE
1017 l_operator := ' = ';
1018 END IF;
1019
1020 IF(x_MOH_where IS NULL) THEN
1021 x_MOH_where := 'Where ';
1022 ELSE
1023 x_MOH_where := x_MOH_where || ' AND ';
1024 END IF;
1025 x_MOH_where := x_MOH_where || 'P_MOH_Rec.CARRIER ' || l_operator || ' :p_CARRIER';
1026 END IF;
1027
1028 -- Add more IF statements for each column below
1029
1030 -- Debug Message
1031 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: Generate Where Ends');
1032
1033 END Gen_MOH_Where;
1034
1035 -- Item-level validation procedures
1036 PROCEDURE Validate_HEADER_ID (
1037 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1038 P_Validation_mode IN VARCHAR2,
1039 P_HEADER_ID IN NUMBER,
1040 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1041 X_Return_Status OUT NOCOPY VARCHAR2,
1042 X_Msg_Count OUT NOCOPY NUMBER,
1043 X_Msg_Data OUT NOCOPY VARCHAR2
1044 )
1045 IS
1046 l_check_header_id number;
1047 BEGIN
1048
1049 -- Initialize message list if p_init_msg_list is set to TRUE.
1050 IF FND_API.to_Boolean( p_init_msg_list )
1051 THEN
1052 FND_MSG_PUB.initialize;
1053 END IF;
1054
1055
1056 -- Initialize API return status to SUCCESS
1057 x_return_status := FND_API.G_RET_STS_SUCCESS;
1058
1059 -- validate NOT NULL column
1060 IF(p_HEADER_ID is NULL)
1061 THEN
1062 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private orderheaders API: -Violate NOT NULL constraint(HEADER_ID)');
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 END IF;
1065
1066 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1067 THEN
1068 NULL;
1069 -- Hint: Validate data
1070 -- IF p_HEADER_ID is not NULL and p_HEADER_ID <> G_MISS_CHAR
1071 -- verify if data is valid
1072 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1073
1074 -- Check whether the p_header_id exists in the mtl_txn_request_headers. If not, raise an exception.
1075 /* 04/05/00: the following block is moved to csp_to_form_moheaders.validate_and_write.
1076 begin
1077 select header_id into l_check_header_id
1078 from mtl_txn_request_headers
1079 where header_id = p_header_id;
1080
1081 exception
1082 when no_data_found then
1083 x_msg_count := x_msg_count + 1;
1084 x_msg_data := x_msg_data || 'Invalid Header ID.';
1085 x_return_status := FND_API.G_RET_STS_ERROR;
1086 when others then
1087 x_msg_count := x_msg_count + 1;
1088 x_msg_data := x_msg_data || 'Unexpected errors found while validating the Header ID. Please check the Header ID.';
1089 x_return_status := FND_API.G_RET_STS_ERROR;
1090 end;
1091 */
1092 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1093 THEN
1094 -- Hint: Validate data
1095 -- IF p_HEADER_ID <> G_MISS_CHAR
1096 -- verify if data is valid
1097 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1098 begin
1099 select header_id into l_check_header_id
1100 from mtl_txn_request_headers
1101 where header_id = p_header_id;
1102
1103 exception
1104 when no_data_found then
1105 x_msg_count := x_msg_count + 1;
1106 x_msg_data := x_msg_data || 'Invalid Header ID.';
1107 x_return_status := FND_API.G_RET_STS_ERROR;
1108 when others then
1109 x_msg_count := x_msg_count + 1;
1110 x_msg_data := x_msg_data || 'Unexpected errors found while validating the Header ID. Please check the Header ID.';
1111 x_return_status := FND_API.G_RET_STS_ERROR;
1112 end;
1113 END IF;
1114
1115 -- Standard call to get message count and if count is 1, get message info.
1116 FND_MSG_PUB.Count_And_Get
1117 ( p_count => x_msg_count,
1118 p_data => x_msg_data
1119 );
1120
1121 END Validate_HEADER_ID;
1122
1123
1124 PROCEDURE Validate_LOCATION_ID (
1125 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1126 P_Validation_mode IN VARCHAR2,
1127 P_LOCATION_ID IN NUMBER,
1128 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1129 X_Return_Status OUT NOCOPY VARCHAR2,
1130 X_Msg_Count OUT NOCOPY NUMBER,
1131 X_Msg_Data OUT NOCOPY VARCHAR2
1132 )
1133 IS
1134 BEGIN
1135
1136 -- Initialize message list if p_init_msg_list is set to TRUE.
1137 IF FND_API.to_Boolean( p_init_msg_list )
1138 THEN
1139 FND_MSG_PUB.initialize;
1140 END IF;
1141
1142
1143 -- Initialize API return status to SUCCESS
1144 x_return_status := FND_API.G_RET_STS_SUCCESS;
1145
1146 -- validate NOT NULL column
1147 IF(p_LOCATION_ID is NULL)
1148 THEN
1149 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'CSP', 'Private orderheaders API: -Violate NOT NULL constraint(LOCATION_ID)');
1150 x_return_status := FND_API.G_RET_STS_ERROR;
1151 END IF;
1152
1153 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1154 THEN
1155 -- Hint: Validate data
1156 -- IF p_LOCATION_ID is not NULL and p_LOCATION_ID <> G_MISS_CHAR
1157 -- verify if data is valid
1158 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1159 NULL;
1160 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1161 THEN
1162 -- Hint: Validate data
1163 -- IF p_LOCATION_ID <> G_MISS_CHAR
1164 -- verify if data is valid
1165 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1166 NULL;
1167 END IF;
1168
1169 -- Standard call to get message count and if count is 1, get message info.
1170 FND_MSG_PUB.Count_And_Get
1171 ( p_count => x_msg_count,
1172 p_data => x_msg_data
1173 );
1174
1175 END Validate_LOCATION_ID;
1176
1177
1178 PROCEDURE Validate_CARRIER (
1179 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1180 P_Validation_mode IN VARCHAR2,
1181 P_CARRIER IN VARCHAR2,
1182 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1183 X_Return_Status OUT NOCOPY VARCHAR2,
1184 X_Msg_Count OUT NOCOPY NUMBER,
1185 X_Msg_Data OUT NOCOPY VARCHAR2
1186 )
1187 IS
1188 BEGIN
1189
1190 -- Initialize message list if p_init_msg_list is set to TRUE.
1191 IF FND_API.to_Boolean( p_init_msg_list )
1192 THEN
1193 FND_MSG_PUB.initialize;
1194 END IF;
1195
1196
1197 -- Initialize API return status to SUCCESS
1198 x_return_status := FND_API.G_RET_STS_SUCCESS;
1199
1200 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1201 THEN
1202 -- Hint: Validate data
1203 -- IF p_CARRIER is not NULL and p_CARRIER <> G_MISS_CHAR
1204 -- verify if data is valid
1205 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1206 NULL;
1207 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1208 THEN
1209 -- Hint: Validate data
1210 -- IF p_CARRIER <> G_MISS_CHAR
1211 -- verify if data is valid
1212 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1213 NULL;
1214 END IF;
1215
1216 -- Standard call to get message count and if count is 1, get message info.
1217 FND_MSG_PUB.Count_And_Get
1218 ( p_count => x_msg_count,
1219 p_data => x_msg_data
1220 );
1221
1222 END Validate_CARRIER;
1223
1224
1225 PROCEDURE Validate_SHIPMENT_METHOD (
1226 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1227 P_Validation_mode IN VARCHAR2,
1228 P_SHIPMENT_METHOD IN VARCHAR2,
1229 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1230 X_Return_Status OUT NOCOPY VARCHAR2,
1231 X_Msg_Count OUT NOCOPY NUMBER,
1232 X_Msg_Data OUT NOCOPY VARCHAR2
1233 )
1234 IS
1235 BEGIN
1236
1237 -- Initialize message list if p_init_msg_list is set to TRUE.
1238 IF FND_API.to_Boolean( p_init_msg_list )
1239 THEN
1240 FND_MSG_PUB.initialize;
1241 END IF;
1242
1243
1244 -- Initialize API return status to SUCCESS
1245 x_return_status := FND_API.G_RET_STS_SUCCESS;
1246
1247 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1248 THEN
1249 -- Hint: Validate data
1250 -- IF p_SHIPMENT_METHOD is not NULL and p_SHIPMENT_METHOD <> G_MISS_CHAR
1251 -- verify if data is valid
1252 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1253 NULL;
1254 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1255 THEN
1256 -- Hint: Validate data
1257 -- IF p_SHIPMENT_METHOD <> G_MISS_CHAR
1258 -- verify if data is valid
1259 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1260 NULL;
1261 END IF;
1262
1263 -- Standard call to get message count and if count is 1, get message info.
1264 FND_MSG_PUB.Count_And_Get
1265 ( p_count => x_msg_count,
1266 p_data => x_msg_data
1267 );
1268
1269 END Validate_SHIPMENT_METHOD;
1270
1271
1272 PROCEDURE Validate_AUTORECEIPT_FLAG (
1273 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1274 P_Validation_mode IN VARCHAR2,
1275 P_AUTORECEIPT_FLAG IN VARCHAR2,
1276 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1277 X_Return_Status OUT NOCOPY VARCHAR2,
1278 X_Msg_Count OUT NOCOPY NUMBER,
1279 X_Msg_Data OUT NOCOPY VARCHAR2
1280 )
1281 IS
1282 BEGIN
1283
1284 -- Initialize message list if p_init_msg_list is set to TRUE.
1285 IF FND_API.to_Boolean( p_init_msg_list )
1286 THEN
1287 FND_MSG_PUB.initialize;
1288 END IF;
1289
1290
1291 -- Initialize API return status to SUCCESS
1292 x_return_status := FND_API.G_RET_STS_SUCCESS;
1293
1294 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1295 THEN
1296 -- Hint: Validate data
1297 -- IF p_AUTORECEIPT_FLAG is not NULL and p_AUTORECEIPT_FLAG <> G_MISS_CHAR
1298 -- verify if data is valid
1299 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1300 NULL;
1301 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1302 THEN
1303 -- Hint: Validate data
1304 -- IF p_AUTORECEIPT_FLAG <> G_MISS_CHAR
1305 -- verify if data is valid
1306 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1307 NULL;
1308 END IF;
1309
1310 -- Standard call to get message count and if count is 1, get message info.
1311 FND_MSG_PUB.Count_And_Get
1312 ( p_count => x_msg_count,
1313 p_data => x_msg_data
1314 );
1315
1316 END Validate_AUTORECEIPT_FLAG;
1317
1318
1319 /* PROCEDURE Validate_ADDRESS1 (
1320 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1321 P_Validation_mode IN VARCHAR2,
1322 P_ADDRESS1 IN VARCHAR2,
1323 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1324 X_Return_Status OUT NOCOPY VARCHAR2,
1325 X_Msg_Count OUT NOCOPY NUMBER,
1326 X_Msg_Data OUT NOCOPY VARCHAR2
1327 )
1328 IS
1329 BEGIN
1330
1331 -- Initialize message list if p_init_msg_list is set to TRUE.
1332 IF FND_API.to_Boolean( p_init_msg_list )
1333 THEN
1334 FND_MSG_PUB.initialize;
1335 END IF;
1336
1337
1338 -- Initialize API return status to SUCCESS
1339 x_return_status := FND_API.G_RET_STS_SUCCESS;
1340
1341 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1342 THEN
1343 -- Hint: Validate data
1344 -- IF p_ADDRESS1 is not NULL and p_ADDRESS1 <> G_MISS_CHAR
1345 -- verify if data is valid
1346 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1347 NULL;
1348 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1349 THEN
1350 -- Hint: Validate data
1351 -- IF p_ADDRESS1 <> G_MISS_CHAR
1352 -- verify if data is valid
1353 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1354 NULL;
1355 END IF;
1356
1357 -- Standard call to get message count and if count is 1, get message info.
1358 FND_MSG_PUB.Count_And_Get
1359 ( p_count => x_msg_count,
1360 p_data => x_msg_data
1361 );
1362
1363 END Validate_ADDRESS1;
1364
1365
1366 PROCEDURE Validate_ADDRESS2 (
1367 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1368 P_Validation_mode IN VARCHAR2,
1369 P_ADDRESS2 IN VARCHAR2,
1370 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1371 X_Return_Status OUT NOCOPY VARCHAR2,
1372 X_Msg_Count OUT NOCOPY NUMBER,
1373 X_Msg_Data OUT NOCOPY VARCHAR2
1374 )
1375 IS
1376 BEGIN
1377
1378 -- Initialize message list if p_init_msg_list is set to TRUE.
1379 IF FND_API.to_Boolean( p_init_msg_list )
1380 THEN
1381 FND_MSG_PUB.initialize;
1382 END IF;
1383
1384
1385 -- Initialize API return status to SUCCESS
1386 x_return_status := FND_API.G_RET_STS_SUCCESS;
1387
1388 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1389 THEN
1390 -- Hint: Validate data
1391 -- IF p_ADDRESS2 is not NULL and p_ADDRESS2 <> G_MISS_CHAR
1392 -- verify if data is valid
1393 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1394 NULL;
1395 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1396 THEN
1397 -- Hint: Validate data
1398 -- IF p_ADDRESS2 <> G_MISS_CHAR
1399 -- verify if data is valid
1400 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1401 NULL;
1402 END IF;
1403
1404 -- Standard call to get message count and if count is 1, get message info.
1405 FND_MSG_PUB.Count_And_Get
1406 ( p_count => x_msg_count,
1407 p_data => x_msg_data
1408 );
1409
1410 END Validate_ADDRESS2;
1411
1412
1413 PROCEDURE Validate_ADDRESS3 (
1414 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1415 P_Validation_mode IN VARCHAR2,
1416 P_ADDRESS3 IN VARCHAR2,
1417 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1418 X_Return_Status OUT NOCOPY VARCHAR2,
1419 X_Msg_Count OUT NOCOPY NUMBER,
1420 X_Msg_Data OUT NOCOPY VARCHAR2
1421 )
1422 IS
1423 BEGIN
1424
1425 -- Initialize message list if p_init_msg_list is set to TRUE.
1426 IF FND_API.to_Boolean( p_init_msg_list )
1427 THEN
1428 FND_MSG_PUB.initialize;
1429 END IF;
1430
1431
1432 -- Initialize API return status to SUCCESS
1433 x_return_status := FND_API.G_RET_STS_SUCCESS;
1434
1435 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1436 THEN
1437 -- Hint: Validate data
1438 -- IF p_ADDRESS3 is not NULL and p_ADDRESS3 <> G_MISS_CHAR
1439 -- verify if data is valid
1440 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1441 NULL;
1442 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1443 THEN
1444 -- Hint: Validate data
1445 -- IF p_ADDRESS3 <> G_MISS_CHAR
1446 -- verify if data is valid
1447 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1448 NULL;
1449 END IF;
1450
1451 -- Standard call to get message count and if count is 1, get message info.
1452 FND_MSG_PUB.Count_And_Get
1453 ( p_count => x_msg_count,
1454 p_data => x_msg_data
1455 );
1456
1457 END Validate_ADDRESS3;
1458
1459
1460 PROCEDURE Validate_ADDRESS4 (
1461 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1462 P_Validation_mode IN VARCHAR2,
1463 P_ADDRESS4 IN VARCHAR2,
1464 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1465 X_Return_Status OUT NOCOPY VARCHAR2,
1466 X_Msg_Count OUT NOCOPY NUMBER,
1467 X_Msg_Data OUT NOCOPY VARCHAR2
1468 )
1469 IS
1470 BEGIN
1471
1472 -- Initialize message list if p_init_msg_list is set to TRUE.
1473 IF FND_API.to_Boolean( p_init_msg_list )
1474 THEN
1475 FND_MSG_PUB.initialize;
1476 END IF;
1477
1478
1479 -- Initialize API return status to SUCCESS
1480 x_return_status := FND_API.G_RET_STS_SUCCESS;
1481
1482 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1483 THEN
1484 -- Hint: Validate data
1485 -- IF p_ADDRESS4 is not NULL and p_ADDRESS4 <> G_MISS_CHAR
1486 -- verify if data is valid
1487 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1488 NULL;
1489 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1490 THEN
1491 -- Hint: Validate data
1492 -- IF p_ADDRESS4 <> G_MISS_CHAR
1493 -- verify if data is valid
1494 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1495 NULL;
1496 END IF;
1497
1498 -- Standard call to get message count and if count is 1, get message info.
1499 FND_MSG_PUB.Count_And_Get
1500 ( p_count => x_msg_count,
1501 p_data => x_msg_data
1502 );
1503
1504 END Validate_ADDRESS4;
1505
1506
1507 PROCEDURE Validate_CITY (
1508 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1509 P_Validation_mode IN VARCHAR2,
1510 P_CITY IN VARCHAR2,
1511 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1512 X_Return_Status OUT NOCOPY VARCHAR2,
1513 X_Msg_Count OUT NOCOPY NUMBER,
1514 X_Msg_Data OUT NOCOPY VARCHAR2
1515 )
1516 IS
1517 BEGIN
1518
1519 -- Initialize message list if p_init_msg_list is set to TRUE.
1520 IF FND_API.to_Boolean( p_init_msg_list )
1521 THEN
1522 FND_MSG_PUB.initialize;
1523 END IF;
1524
1525
1526 -- Initialize API return status to SUCCESS
1527 x_return_status := FND_API.G_RET_STS_SUCCESS;
1528
1529 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1530 THEN
1531 -- Hint: Validate data
1532 -- IF p_CITY is not NULL and p_CITY <> G_MISS_CHAR
1533 -- verify if data is valid
1534 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1535 NULL;
1536 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1537 THEN
1538 -- Hint: Validate data
1539 -- IF p_CITY <> G_MISS_CHAR
1540 -- verify if data is valid
1541 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1542 NULL;
1543 END IF;
1544
1545 -- Standard call to get message count and if count is 1, get message info.
1546 FND_MSG_PUB.Count_And_Get
1547 ( p_count => x_msg_count,
1548 p_data => x_msg_data
1549 );
1550
1551 END Validate_CITY;
1552
1553
1554 PROCEDURE Validate_POSTAL_CODE (
1555 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1556 P_Validation_mode IN VARCHAR2,
1557 P_POSTAL_CODE IN VARCHAR2,
1558 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1559 X_Return_Status OUT NOCOPY VARCHAR2,
1560 X_Msg_Count OUT NOCOPY NUMBER,
1561 X_Msg_Data OUT NOCOPY VARCHAR2
1562 )
1563 IS
1564 BEGIN
1565
1566 -- Initialize message list if p_init_msg_list is set to TRUE.
1567 IF FND_API.to_Boolean( p_init_msg_list )
1568 THEN
1569 FND_MSG_PUB.initialize;
1570 END IF;
1571
1572
1573 -- Initialize API return status to SUCCESS
1574 x_return_status := FND_API.G_RET_STS_SUCCESS;
1575
1576 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1577 THEN
1578 -- Hint: Validate data
1579 -- IF p_POSTAL_CODE is not NULL and p_POSTAL_CODE <> G_MISS_CHAR
1580 -- verify if data is valid
1581 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1582 NULL;
1583 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1584 THEN
1585 -- Hint: Validate data
1586 -- IF p_POSTAL_CODE <> G_MISS_CHAR
1587 -- verify if data is valid
1588 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1589 NULL;
1590 END IF;
1591
1592 -- Standard call to get message count and if count is 1, get message info.
1593 FND_MSG_PUB.Count_And_Get
1594 ( p_count => x_msg_count,
1595 p_data => x_msg_data
1596 );
1597
1598 END Validate_POSTAL_CODE;
1599
1600
1601 PROCEDURE Validate_STATE (
1602 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1603 P_Validation_mode IN VARCHAR2,
1604 P_STATE IN VARCHAR2,
1605 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1606 X_Return_Status OUT NOCOPY VARCHAR2,
1607 X_Msg_Count OUT NOCOPY NUMBER,
1608 X_Msg_Data OUT NOCOPY VARCHAR2
1609 )
1610 IS
1611 BEGIN
1612
1613 -- Initialize message list if p_init_msg_list is set to TRUE.
1614 IF FND_API.to_Boolean( p_init_msg_list )
1615 THEN
1616 FND_MSG_PUB.initialize;
1617 END IF;
1618
1619
1620 -- Initialize API return status to SUCCESS
1621 x_return_status := FND_API.G_RET_STS_SUCCESS;
1622
1623 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1624 THEN
1625 -- Hint: Validate data
1626 -- IF p_STATE is not NULL and p_STATE <> G_MISS_CHAR
1627 -- verify if data is valid
1628 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1629 NULL;
1630 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1631 THEN
1632 -- Hint: Validate data
1633 -- IF p_STATE <> G_MISS_CHAR
1634 -- verify if data is valid
1635 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1636 NULL;
1637 END IF;
1638
1639 -- Standard call to get message count and if count is 1, get message info.
1640 FND_MSG_PUB.Count_And_Get
1641 ( p_count => x_msg_count,
1642 p_data => x_msg_data
1643 );
1644
1645 END Validate_STATE;
1646
1647
1648 PROCEDURE Validate_PROVINCE (
1649 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1650 P_Validation_mode IN VARCHAR2,
1651 P_PROVINCE IN VARCHAR2,
1652 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1653 X_Return_Status OUT NOCOPY VARCHAR2,
1654 X_Msg_Count OUT NOCOPY NUMBER,
1655 X_Msg_Data OUT NOCOPY VARCHAR2
1656 )
1657 IS
1658 BEGIN
1659
1660 -- Initialize message list if p_init_msg_list is set to TRUE.
1661 IF FND_API.to_Boolean( p_init_msg_list )
1662 THEN
1663 FND_MSG_PUB.initialize;
1664 END IF;
1665
1666
1667 -- Initialize API return status to SUCCESS
1668 x_return_status := FND_API.G_RET_STS_SUCCESS;
1669
1670 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1671 THEN
1672 -- Hint: Validate data
1673 -- IF p_PROVINCE is not NULL and p_PROVINCE <> G_MISS_CHAR
1674 -- verify if data is valid
1675 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1676 NULL;
1677 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1678 THEN
1679 -- Hint: Validate data
1680 -- IF p_PROVINCE <> G_MISS_CHAR
1681 -- verify if data is valid
1682 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1683 NULL;
1684 END IF;
1685
1686 -- Standard call to get message count and if count is 1, get message info.
1687 FND_MSG_PUB.Count_And_Get
1688 ( p_count => x_msg_count,
1689 p_data => x_msg_data
1690 );
1691
1692 END Validate_PROVINCE;
1693
1694
1695 PROCEDURE Validate_COUNTRY (
1696 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1697 P_Validation_mode IN VARCHAR2,
1698 P_COUNTRY IN VARCHAR2,
1699 -- Hint: You may add 'X_Item_Property_Rec OUT NOCOPY JTF_PLSQL_API.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1700 X_Return_Status OUT NOCOPY VARCHAR2,
1701 X_Msg_Count OUT NOCOPY NUMBER,
1702 X_Msg_Data OUT NOCOPY VARCHAR2
1703 )
1704 IS
1705 BEGIN
1706
1707 -- Initialize message list if p_init_msg_list is set to TRUE.
1708 IF FND_API.to_Boolean( p_init_msg_list )
1709 THEN
1710 FND_MSG_PUB.initialize;
1711 END IF;
1712
1713
1714 -- Initialize API return status to SUCCESS
1715 x_return_status := FND_API.G_RET_STS_SUCCESS;
1716
1717 IF(p_validation_mode = JTF_PLSQL_API.G_CREATE)
1718 THEN
1719 -- Hint: Validate data
1720 -- IF p_COUNTRY is not NULL and p_COUNTRY <> G_MISS_CHAR
1721 -- verify if data is valid
1722 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1723 NULL;
1724 ELSIF(p_validation_mode = JTF_PLSQL_API.G_UPDATE)
1725 THEN
1726 -- Hint: Validate data
1727 -- IF p_COUNTRY <> G_MISS_CHAR
1728 -- verify if data is valid
1729 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1730 NULL;
1731 END IF;
1732
1733 -- Standard call to get message count and if count is 1, get message info.
1734 FND_MSG_PUB.Count_And_Get
1735 ( p_count => x_msg_count,
1736 p_data => x_msg_data
1737 );
1738
1739 END Validate_COUNTRY;
1740 */
1741
1742 -- Hint: inter-field level validation can be added here.
1743 -- Hint: If p_validation_mode = JTF_PLSQL_API.G_VALIDATE_UPDATE, we should use cursor
1744 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1745 -- stored in database table.
1746 PROCEDURE Validate_MOH_rec(
1747 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1748 P_Validation_mode IN VARCHAR2,
1749 P_MOH_Rec IN MOH_Rec_Type,
1750 X_Return_Status OUT NOCOPY VARCHAR2,
1751 X_Msg_Count OUT NOCOPY NUMBER,
1752 X_Msg_Data OUT NOCOPY VARCHAR2
1753 )
1754 IS
1755 BEGIN
1756
1757 -- Initialize message list if p_init_msg_list is set to TRUE.
1758 IF FND_API.to_Boolean( p_init_msg_list )
1759 THEN
1760 FND_MSG_PUB.initialize;
1761 END IF;
1762
1763
1764 -- Initialize API return status to SUCCESS
1765 x_return_status := FND_API.G_RET_STS_SUCCESS;
1766
1767 -- Hint: Validate data
1768 -- If data not valid
1769 -- THEN
1770 -- x_return_status := FND_API.G_RET_STS_ERROR;
1771
1772 -- Debug Message
1773 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'API_INVALID_RECORD');
1774
1775 -- Standard call to get message count and if count is 1, get message info.
1776 FND_MSG_PUB.Count_And_Get
1777 ( p_count => x_msg_count,
1778 p_data => x_msg_data
1779 );
1780
1781 END Validate_MOH_Rec;
1782
1783 PROCEDURE Validate_orderheaders(
1784 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1785 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1786 P_Validation_mode IN VARCHAR2,
1787 P_MOH_Rec IN MOH_Rec_Type,
1788 X_Return_Status OUT NOCOPY VARCHAR2,
1789 X_Msg_Count OUT NOCOPY NUMBER,
1790 X_Msg_Data OUT NOCOPY VARCHAR2
1791 )
1792 IS
1793 l_api_name CONSTANT VARCHAR2(30) := 'Validate_orderheaders';
1794 BEGIN
1795
1796 -- Debug Message
1797 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'start');
1798
1799
1800 -- Initialize API return status to SUCCESS
1801 x_return_status := FND_API.G_RET_STS_SUCCESS;
1802
1803 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM) THEN
1804 -- Hint: We provide validation procedure for every column. Developer should delete
1805 -- unnecessary validation procedures.
1806 Validate_HEADER_ID(
1807 p_init_msg_list => FND_API.G_FALSE,
1808 p_validation_mode => p_validation_mode,
1809 p_HEADER_ID => P_MOH_Rec.HEADER_ID,
1810 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1811 x_return_status => x_return_status,
1812 x_msg_count => x_msg_count,
1813 x_msg_data => x_msg_data);
1814 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1815 raise FND_API.G_EXC_ERROR;
1816 END IF;
1817
1818 Validate_CARRIER(
1819 p_init_msg_list => FND_API.G_FALSE,
1820 p_validation_mode => p_validation_mode,
1821 p_CARRIER => P_MOH_Rec.CARRIER,
1822 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1823 x_return_status => x_return_status,
1824 x_msg_count => x_msg_count,
1825 x_msg_data => x_msg_data);
1826 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1827 raise FND_API.G_EXC_ERROR;
1828 END IF;
1829
1830 Validate_SHIPMENT_METHOD(
1831 p_init_msg_list => FND_API.G_FALSE,
1832 p_validation_mode => p_validation_mode,
1833 p_SHIPMENT_METHOD => P_MOH_Rec.SHIPMENT_METHOD,
1834 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1835 x_return_status => x_return_status,
1836 x_msg_count => x_msg_count,
1837 x_msg_data => x_msg_data);
1838 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1839 raise FND_API.G_EXC_ERROR;
1840 END IF;
1841
1842 Validate_AUTORECEIPT_FLAG(
1843 p_init_msg_list => FND_API.G_FALSE,
1844 p_validation_mode => p_validation_mode,
1845 p_AUTORECEIPT_FLAG => P_MOH_Rec.AUTORECEIPT_FLAG,
1846 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1847 x_return_status => x_return_status,
1848 x_msg_count => x_msg_count,
1849 x_msg_data => x_msg_data);
1850 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1851 raise FND_API.G_EXC_ERROR;
1852 END IF;
1853
1854 /* Validate_ADDRESS1(
1855 p_init_msg_list => FND_API.G_FALSE,
1856 p_validation_mode => p_validation_mode,
1857 p_ADDRESS1 => P_MOH_Rec.ADDRESS1,
1858 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1859 x_return_status => x_return_status,
1860 x_msg_count => x_msg_count,
1861 x_msg_data => x_msg_data);
1862 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1863 raise FND_API.G_EXC_ERROR;
1864 END IF;
1865
1866 Validate_ADDRESS2(
1867 p_init_msg_list => FND_API.G_FALSE,
1868 p_validation_mode => p_validation_mode,
1869 p_ADDRESS2 => P_MOH_Rec.ADDRESS2,
1870 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1871 x_return_status => x_return_status,
1872 x_msg_count => x_msg_count,
1873 x_msg_data => x_msg_data);
1874 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1875 raise FND_API.G_EXC_ERROR;
1876 END IF;
1877
1878 Validate_ADDRESS3(
1879 p_init_msg_list => FND_API.G_FALSE,
1880 p_validation_mode => p_validation_mode,
1881 p_ADDRESS3 => P_MOH_Rec.ADDRESS3,
1882 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1883 x_return_status => x_return_status,
1884 x_msg_count => x_msg_count,
1885 x_msg_data => x_msg_data);
1886 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1887 raise FND_API.G_EXC_ERROR;
1888 END IF;
1889
1890 Validate_ADDRESS4(
1891 p_init_msg_list => FND_API.G_FALSE,
1892 p_validation_mode => p_validation_mode,
1893 p_ADDRESS4 => P_MOH_Rec.ADDRESS4,
1894 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1895 x_return_status => x_return_status,
1896 x_msg_count => x_msg_count,
1897 x_msg_data => x_msg_data);
1898 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1899 raise FND_API.G_EXC_ERROR;
1900 END IF;
1901
1902 Validate_CITY(
1903 p_init_msg_list => FND_API.G_FALSE,
1904 p_validation_mode => p_validation_mode,
1905 p_CITY => P_MOH_Rec.CITY,
1906 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1907 x_return_status => x_return_status,
1908 x_msg_count => x_msg_count,
1909 x_msg_data => x_msg_data);
1910 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1911 raise FND_API.G_EXC_ERROR;
1912 END IF;
1913
1914 Validate_POSTAL_CODE(
1915 p_init_msg_list => FND_API.G_FALSE,
1916 p_validation_mode => p_validation_mode,
1917 p_POSTAL_CODE => P_MOH_Rec.POSTAL_CODE,
1918 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1919 x_return_status => x_return_status,
1920 x_msg_count => x_msg_count,
1921 x_msg_data => x_msg_data);
1922 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1923 raise FND_API.G_EXC_ERROR;
1924 END IF;
1925
1926 Validate_STATE(
1927 p_init_msg_list => FND_API.G_FALSE,
1928 p_validation_mode => p_validation_mode,
1929 p_STATE => P_MOH_Rec.STATE,
1930 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1931 x_return_status => x_return_status,
1932 x_msg_count => x_msg_count,
1933 x_msg_data => x_msg_data);
1934 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1935 raise FND_API.G_EXC_ERROR;
1936 END IF;
1937
1938 Validate_PROVINCE(
1939 p_init_msg_list => FND_API.G_FALSE,
1940 p_validation_mode => p_validation_mode,
1941 p_PROVINCE => P_MOH_Rec.PROVINCE,
1942 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1943 x_return_status => x_return_status,
1944 x_msg_count => x_msg_count,
1945 x_msg_data => x_msg_data);
1946 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1947 raise FND_API.G_EXC_ERROR;
1948 END IF;
1949
1950 Validate_COUNTRY(
1951 p_init_msg_list => FND_API.G_FALSE,
1952 p_validation_mode => p_validation_mode,
1953 p_COUNTRY => P_MOH_Rec.COUNTRY,
1954 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1955 x_return_status => x_return_status,
1956 x_msg_count => x_msg_count,
1957 x_msg_data => x_msg_data);
1958 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1959 raise FND_API.G_EXC_ERROR;
1960 END IF;
1961 */
1962 END IF;
1963
1964 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_RECORD) THEN
1965 -- Hint: Inter-field level validation can be added here
1966 -- invoke record level validation procedures
1967 Validate_MOH_Rec(
1968 p_init_msg_list => FND_API.G_FALSE,
1969 p_validation_mode => p_validation_mode,
1970 P_MOH_Rec => P_MOH_Rec,
1971 x_return_status => x_return_status,
1972 x_msg_count => x_msg_count,
1973 x_msg_data => x_msg_data);
1974
1975 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1976 raise FND_API.G_EXC_ERROR;
1977 END IF;
1978 END IF;
1979
1980 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_RECORD) THEN
1981 -- invoke inter-record level validation procedures
1982 NULL;
1983 END IF;
1984
1985 IF (p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_INTER_ENTITY) THEN
1986 -- invoke inter-entity level validation procedures
1987 NULL;
1988 END IF;
1989
1990
1991 -- Debug Message
1992 JTF_PLSQL_API.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'CSP', 'Private API: ' || l_api_name || 'end');
1993
1994 END Validate_orderheaders;
1995
1996 End CSP_ORDERHEADERS_PVT;
[Home] [Help]