[Home] [Help]
PACKAGE BODY: APPS.CSP_REQ_LINE_DETAILS_PVT
Source
1 PACKAGE BODY CSP_REQ_LINE_DETAILS_PVT as
2 /* $Header: cspvrldb.pls 120.0 2005/05/24 18:11:55 appldev noship $ */
3 -- Start of Comments
4 -- Package name : CSP_Req_Line_details_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_Req_Line_Details_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvrldb.pls';
12
13
14 -- Hint: Primary key needs to be returned.
15 PROCEDURE Create_req_line_Details(
16 P_Api_Version_Number IN NUMBER,
17 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
18 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 P_Req_Line_Details_Tbl IN Req_Line_Details_Tbl_Type := G_MISS_Req_Line_Details_Tbl,
21 x_Req_Line_Details_tbl OUT NOCOPY Req_Line_Details_Tbl_Type,
22 X_Return_Status OUT NOCOPY VARCHAR2,
23 X_Msg_Count OUT NOCOPY NUMBER,
24 X_Msg_Data OUT NOCOPY VARCHAR2
25 )
26
27 IS
28 l_api_name CONSTANT VARCHAR2(30) := 'Create_req_line_details';
29 l_api_version_number CONSTANT NUMBER := 1.0;
30 l_return_status_full VARCHAR2(1);
31 l_access_flag VARCHAR2(1);
32
33 l_req_line_Details_Rec Req_Line_Details_Rec_Type;
34 l_req_line_details_id NUMBER;
35 BEGIN
36 -- Standard Start of API savepoint
37 SAVEPOINT CREATE_Req_Line_Details_PUB;
38
39 -- Standard call to check for call compatibility.
40 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
41 p_api_version_number,
42 l_api_name,
43 G_PKG_NAME)
44 THEN
45 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46 END IF;
47
48
49 -- Initialize message list if p_init_msg_list is set to TRUE.
50 IF FND_API.to_Boolean( p_init_msg_list )
51 THEN
52 FND_MSG_PUB.initialize;
53 END IF;
54
55
56 -- Debug Message
57 -- JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
58
59 -- Initialize API return status to SUCCESS
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61
62 -- Invoke table handler(CSP_REQUIREMENT_LINES_PKG.Insert_Row)
63 FOR I IN 1..P_Req_Line_Details_Tbl.COUNT LOOP
64
65 l_req_line_details_rec := P_Req_Line_Details_Tbl(I);
66
67 CSP_req_line_details_PKG.Insert_Row(
68 px_REQ_LINE_DETAIL_ID => l_req_line_details_rec.req_line_detail_id,
69 p_CREATED_BY => FND_GLOBAL.USER_ID,
70 p_CREATION_DATE => SYSDATE,
71 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
72 p_LAST_UPDATE_DATE => SYSDATE,
73 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
74 p_REQUIREMENT_LINE_ID => l_Req_Line_details_rec.REQUIREMENT_LINE_ID,
75 p_SOURCE_TYPE => l_Req_Line_details_rec.SOURCE_TYPE,
76 p_SOURCE_ID => l_Req_Line_details_rec.source_id
77 );
78
79 -- Hint: Primary key should be returned.
80 x_REQ_Line_Details_Tbl(I) := l_req_line_details_rec;
81
82 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85 END LOOP;
86 --
87 -- End of API body
88 --
89
90 -- Standard check for p_commit
91 /* IF FND_API.to_Boolean( p_commit )
92 THEN
93 COMMIT WORK;
94 END IF;
95 */
96
97 -- Debug Message
98 -- JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
99
100
101 -- Standard call to get message count and if count is 1, get message info.
102 /* FND_MSG_PUB.Count_And_Get
103 ( p_count => x_msg_count,
104 p_data => x_msg_data
105 ); */
106
107 EXCEPTION
108 WHEN FND_API.G_EXC_ERROR THEN
109 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
110 P_API_NAME => L_API_NAME
111 ,P_PKG_NAME => G_PKG_NAME
112 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
113 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
114 ,X_MSG_COUNT => X_MSG_COUNT
115 ,X_MSG_DATA => X_MSG_DATA
116 ,X_RETURN_STATUS => X_RETURN_STATUS);
117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
118 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
119 P_API_NAME => L_API_NAME
120 ,P_PKG_NAME => G_PKG_NAME
121 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
122 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
123 ,X_MSG_COUNT => X_MSG_COUNT
124 ,X_MSG_DATA => X_MSG_DATA
125 ,X_RETURN_STATUS => X_RETURN_STATUS);
126 WHEN OTHERS THEN
127 Rollback to CREATE_Req_line_details_PUB;
128 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
129 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
130 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
131 FND_MSG_PUB.ADD;
132 fnd_msg_pub.count_and_get
133 ( p_count => x_msg_count
134 , p_data => x_msg_data);
135 x_return_status := FND_API.G_RET_STS_ERROR;
136 End Create_req_line_details;
137
138
139 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
140 PROCEDURE Update_req_line_details(
141 P_Api_Version_Number IN NUMBER,
142 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
143 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
144 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
145 P_Req_Line_Details_Tbl IN Req_Line_Details_Tbl_Type,
146 X_Return_Status OUT NOCOPY VARCHAR2,
147 X_Msg_Count OUT NOCOPY NUMBER,
148 X_Msg_Data OUT NOCOPY VARCHAR2
149 )
150
151 IS
152 l_api_name CONSTANT VARCHAR2(30) := 'Update_req_line_details';
153 l_api_version_number CONSTANT NUMBER := 1.0;
154 -- Local Variables
155 l_Req_Line_Details_rec CSP_req_line_Details_PVT.Req_Line_Details_Rec_Type;
156 --l_tar_Requirement_Line_rec CSP_requirement_lines_PVT.Requirement_Line_Rec_Type := P_Requirement_Line_Rec;
157 l_rowid ROWID;
158 BEGIN
159 -- Standard Start of API savepoint
160 SAVEPOINT UPDATE_Req_Line_Details_PUB;
161
162 -- Standard call to check for call compatibility.
163 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
164 p_api_version_number,
165 l_api_name,
166 G_PKG_NAME)
167 THEN
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170
171
172 -- Initialize message list if p_init_msg_list is set to TRUE.
173 IF FND_API.to_Boolean( p_init_msg_list )
174 THEN
175 FND_MSG_PUB.initialize;
176 END IF;
177
178
179 -- Debug Message
180 --JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
181
182
183 -- Initialize API return status to SUCCESS
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185
186
187 -- Invoke table handler(CSP_REQUIREMENT_LINES_PKG.Update_Row)
188 FOR I IN 1..P_Req_Line_Details_Tbl.COUNT LOOP
189 l_req_line_details_rec := p_req_line_details_tbl(I);
190
191 CSP_REQ_LINE_DETAILS_PKG.Update_Row(
192 px_REQ_LINE_DETAIL_ID => l_req_line_details_rec.REQ_LINE_DETAIL_ID,
193 p_REQUIREMENT_LINE_ID => l_req_line_details_rec.REQUIREMENT_LINE_ID,
194 p_CREATED_BY => FND_API.G_MISS_NUM,
195 p_CREATION_DATE => FND_API.G_MISS_DATE,
196 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
197 p_LAST_UPDATE_DATE => SYSDATE,
198 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
199 p_SOURCE_TYPE => l_req_line_details_rec.SOURCE_TYPE,
200 p_SOURCE_ID => l_req_line_details_rec.SOURCE_ID
201 );
202
203 END LOOP;
204 --
205 -- End of API body.
206 --
207
208 -- Standard check for p_commit
209 IF FND_API.to_Boolean( p_commit )
210 THEN
211 COMMIT WORK;
212 END IF;
213
214
215 EXCEPTION
216 WHEN FND_API.G_EXC_ERROR THEN
217 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
218 P_API_NAME => L_API_NAME
219 ,P_PKG_NAME => G_PKG_NAME
220 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
221 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
222 ,X_MSG_COUNT => X_MSG_COUNT
223 ,X_MSG_DATA => X_MSG_DATA
224 ,X_RETURN_STATUS => X_RETURN_STATUS);
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
227 P_API_NAME => L_API_NAME
228 ,P_PKG_NAME => G_PKG_NAME
229 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
230 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
231 ,X_MSG_COUNT => X_MSG_COUNT
232 ,X_MSG_DATA => X_MSG_DATA
233 ,X_RETURN_STATUS => X_RETURN_STATUS);
234 WHEN OTHERS THEN
235 Rollback to UPDATE_Req_Line_Details_PUB;
236 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
237 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
238 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
239 FND_MSG_PUB.ADD;
240 fnd_msg_pub.count_and_get
241 ( p_count => x_msg_count
242 , p_data => x_msg_data);
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 End Update_req_line_details;
245
246
247 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
248 -- The Master delete procedure may not be needed depends on different business requirements.
249 PROCEDURE Delete_req_line_details(
250 P_Api_Version_Number IN NUMBER,
251 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
252 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
253 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
254 p_req_line_details_tbl IN Req_Line_Details_Tbl_Type,
255 X_Return_Status OUT NOCOPY VARCHAR2,
256 X_Msg_Count OUT NOCOPY NUMBER,
257 X_Msg_Data OUT NOCOPY VARCHAR2
258 )
259
260 IS
261 l_api_name CONSTANT VARCHAR2(30) := 'Delete_req_line_details';
262 l_api_version_number CONSTANT NUMBER := 1.0;
263 l_req_line_details_rec Req_Line_Details_Rec_type;
264 BEGIN
265 -- Standard Start of API savepoint
266 SAVEPOINT DELETE_Req_Line_Details_PUB;
267
268 -- Standard call to check for call compatibility.
269 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
270 p_api_version_number,
271 l_api_name,
272 G_PKG_NAME)
273 THEN
274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
275 END IF;
276
277
278 -- Initialize message list if p_init_msg_list is set to TRUE.
279 IF FND_API.to_Boolean( p_init_msg_list )
280 THEN
281 FND_MSG_PUB.initialize;
282 END IF;
283
284
285 -- Debug Message
286 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
287
288
289 -- Initialize API return status to SUCCESS
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 --
293 -- Api body
294 --
295 -- Debug Message
296 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling delete table handler');
297
298 -- Invoke table handler(CSP_REQUIREMENT_LINES_PKG.Delete_Row)
299 FOR I IN 1..P_Req_Line_Details_Tbl.COUNT LOOP
300 l_req_line_details_rec := p_req_Line_Details_Tbl(I);
301
302 CSP_REQ_LINE_DETAILS_PKG.Delete_Row(
303 px_REQ_LINE_DETAIL_ID => l_req_line_details_rec.REQ_LINE_DETAIL_ID);
304
305 END LOOP;
306 --
307 -- End of API body
308 --
309
310 -- Standard check for p_commit
311 IF FND_API.to_Boolean( p_commit )
312 THEN
313 COMMIT WORK;
314 END IF;
315
316
317 -- Debug Message
318 JTF_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
319
320
321 -- Standard call to get message count and if count is 1, get message info.
322 FND_MSG_PUB.Count_And_Get
323 ( p_count => x_msg_count,
324 p_data => x_msg_data
325 );
326
327 EXCEPTION
328 WHEN FND_API.G_EXC_ERROR THEN
329 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
330 P_API_NAME => L_API_NAME
331 ,P_PKG_NAME => G_PKG_NAME
332 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
333 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
334 ,X_MSG_COUNT => X_MSG_COUNT
335 ,X_MSG_DATA => X_MSG_DATA
336 ,X_RETURN_STATUS => X_RETURN_STATUS);
337 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
338 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
339 P_API_NAME => L_API_NAME
340 ,P_PKG_NAME => G_PKG_NAME
341 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
342 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
343 ,X_MSG_COUNT => X_MSG_COUNT
344 ,X_MSG_DATA => X_MSG_DATA
345 ,X_RETURN_STATUS => X_RETURN_STATUS);
346 WHEN OTHERS THEN
347 Rollback to DELETE_Req_Line_Details_PUB;
348 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
349 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
350 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
351 FND_MSG_PUB.ADD;
352 fnd_msg_pub.count_and_get
353 ( p_count => x_msg_count
354 , p_data => x_msg_data);
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 End Delete_req_line_details;
357
358 End CSP_Req_Line_Details_PVT;
359