[Home] [Help]
PACKAGE BODY: APPS.CSP_REQ_LINE_DETAILS_PKG
Source
1 PACKAGE BODY CSP_REQ_LINE_DETAILS_PKG as
2 /* $Header: csptrldb.pls 120.2.12020000.2 2013/01/18 07:06:58 htank ship $ */
3 -- Start of Comments
4 -- Package name : CSP_REQUIREMENT_LINES_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_REQ_LINE_DETAILS_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptrldb.pls';
12
13 PROCEDURE Insert_Row(
14 px_REQ_LINE_DETAIL_ID IN OUT NOCOPY NUMBER,
15 p_REQUIREMENT_LINE_ID NUMBER,
16 p_CREATED_BY NUMBER,
17 p_CREATION_DATE DATE,
18 p_LAST_UPDATED_BY NUMBER,
19 p_LAST_UPDATE_DATE DATE,
20 p_LAST_UPDATE_LOGIN NUMBER,
21 p_SOURCE_TYPE VARCHAR2,
22 p_SOURCE_ID NUMBER,
23 p_DML_MODE VARCHAR2)
24 IS
25 CURSOR C2 IS SELECT CSP_REQ_LINE_DETAILS_S1.nextval FROM sys.dual;
26 l_req_line_details CSP_REQ_LINE_DETAILS_PVT.Req_line_details_Rec_Type;
27 l_return_status varchar2(100);
28 l_msg_count NUMBER;
29 l_msg_data varchar2(1000);
30 l_api_name_full varchar2(50) := 'CSP_REQ_LINE_DETAILS_PKG.INSERT_ROW';
31 l_dml_mode varchar2(10) := p_dml_mode;
32
33 l_module_name VARCHAR2(100) := 'csp.plsql.csp_req_line_details_pkg.insert_row';
34
35 BEGIN
36
37 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
38 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
39 l_module_name,
40 'BEGIN...');
41 end if;
42
43 l_req_line_details.REQ_LINE_DETAIL_ID := px_REQ_LINE_DETAIL_ID ;
44 l_req_line_details.REQUIREMENT_LINE_ID := p_REQUIREMENT_LINE_ID ;
45 l_req_line_details.CREATED_BY := p_CREATED_BY;
46 l_req_line_details.CREATION_DATE := p_CREATION_DATE;
47 l_req_line_details.LAST_UPDATED_BY :=p_LAST_UPDATED_BY;
48 l_req_line_details.LAST_UPDATE_DATE :=p_LAST_UPDATE_DATE;
49 l_req_line_details.LAST_UPDATE_LOGIN :=p_LAST_UPDATE_LOGIN ;
50 l_req_line_details.SOURCE_TYPE :=p_SOURCE_TYPE;
51 l_req_line_details.SOURCE_ID :=p_SOURCE_ID;
52
53 IF l_dml_mode is null THEN
54 l_dml_mode :='BOTH';
55 END IF;
56
57 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
58 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
59 l_module_name,
60 'l_dml_mode = ' || l_dml_mode);
61 end if;
62
63 IF l_dml_mode <> 'POST' THEN
64 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
65 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
66 l_module_name,
67 'before calling csp_req_line_details_iuhk.Create_req_line_detail_Pre...');
68 end if;
69
70 csp_req_line_details_iuhk.Create_req_line_detail_Pre
71 ( x_return_status => l_return_status
72 ) ;
73 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
74 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
75 l_module_name,
76 'after calling csp_req_line_details_iuhk.Create_req_line_detail_Pre. l_return_status = ' || l_return_status);
77 end if;
78 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
79 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
80 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
81 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
82 FND_MSG_PUB.Add;
83 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END IF;
85
86 If (l_req_line_details.REQ_LINE_DETAIL_ID IS NULL) OR (l_req_line_details.REQ_LINE_DETAIL_ID = FND_API.G_MISS_NUM) then
87 OPEN C2;
88 FETCH C2 INTO px_REQ_LINE_DETAIL_ID;
89 CLOSE C2;
90 End If;
91 END IF;
92 l_req_line_details.REQ_LINE_DETAIL_ID := px_REQ_LINE_DETAIL_ID ;
93 user_hook_rec.REQ_LINE_DETAIL_ID := l_req_line_details.REQ_LINE_DETAIL_ID;
94
95 IF l_dml_mode = 'BOTH' THEN
96
97 INSERT INTO CSP_REQ_LINE_DETAILS(
98 REQ_LINE_DETAIL_ID,
99 REQUIREMENT_LINE_ID,
100 CREATED_BY,
101 CREATION_DATE,
102 LAST_UPDATED_BY,
103 LAST_UPDATE_DATE,
104 LAST_UPDATE_LOGIN,
105 SOURCE_TYPE,
106 SOURCE_ID
107 )
108 VALUES (
109 px_REQ_LINE_DETAIL_ID,
110 decode( l_req_line_details.REQUIREMENT_LINE_ID, FND_API.G_MISS_NUM, NULL, l_req_line_details.REQUIREMENT_LINE_ID),
111 decode( l_req_line_details.CREATED_BY, FND_API.G_MISS_NUM, NULL, l_req_line_details.CREATED_BY),
112 decode( l_req_line_details.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), l_req_line_details.CREATION_DATE),
113 decode( l_req_line_details.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, l_req_line_details.LAST_UPDATED_BY),
114 decode( l_req_line_details.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), l_req_line_details.LAST_UPDATE_DATE),
115 decode( l_req_line_details.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, l_req_line_details.LAST_UPDATE_LOGIN),
116 decode( l_req_line_details.SOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, l_req_line_details.SOURCE_TYPE),
117 decode( l_req_line_details.SOURCE_ID, FND_API.G_MISS_NUM, NULL, l_req_line_details.SOURCE_ID)
118 );
119 END IF;
120
121 IF l_dml_mode <> 'PRE' THEN
122 user_hook_rec.REQUIREMENT_LINE_ID := l_req_line_details.REQUIREMENT_LINE_ID;
123
124 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
125 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
126 l_module_name,
127 'before calling csp_req_line_details_iuhk.Create_req_line_detail_Post...');
128 end if;
129
130 csp_req_line_details_iuhk.Create_req_line_detail_Post
131 ( x_return_status => l_return_status
132 ) ;
133
134 if(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
135 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
136 l_module_name,
137 'after calling csp_req_line_details_iuhk.Create_req_line_detail_Post. l_return_status = ' || l_return_status);
138 end if;
139
140 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
141 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
142 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_INT_USR_HK');
143 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
144 FND_MSG_PUB.Add;
145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
146 END IF;
147 END IF;
148 End Insert_Row;
149
150 PROCEDURE Update_Row(
151 px_REQ_LINE_DETAIL_ID IN OUT NOCOPY NUMBER,
152 p_REQUIREMENT_LINE_ID NUMBER,
153 p_CREATED_BY NUMBER,
154 p_CREATION_DATE DATE,
155 p_LAST_UPDATED_BY NUMBER,
156 p_LAST_UPDATE_DATE DATE,
157 p_LAST_UPDATE_LOGIN NUMBER,
158 p_SOURCE_TYPE VARCHAR2,
159 p_SOURCE_ID NUMBER,
160 p_DML_MODE VARCHAR2)
161 IS
162 l_req_line_details CSP_REQ_line_details_PVT.Req_LINE_Details_Rec_Type;
163 l_api_name_full varchar2(50) := 'CSP_REQ_LINE_DETAILS_PKG.UPDATE_ROW';
164 l_return_status varchar2(100);
165 l_msg_count NUMBER;
166 l_msg_data varchar2(1000);
167 l_dml_mode varchar2(10) := p_dml_mode;
168 BEGIN
169 l_req_line_details.REQ_LINE_DETAIL_ID := px_REQ_LINE_DETAIL_ID ;
170 l_req_line_details.REQUIREMENT_LINE_ID := p_REQUIREMENT_LINE_ID ;
171 l_req_line_details.CREATED_BY := p_CREATED_BY;
172 l_req_line_details.CREATION_DATE := p_CREATION_DATE;
173 l_req_line_details.LAST_UPDATED_BY :=p_LAST_UPDATED_BY;
174 l_req_line_details.LAST_UPDATE_DATE :=p_LAST_UPDATE_DATE;
175 l_req_line_details.LAST_UPDATE_LOGIN :=p_LAST_UPDATE_LOGIN ;
176 l_req_line_details.SOURCE_TYPE :=p_SOURCE_TYPE;
177 l_req_line_details.SOURCE_ID :=p_SOURCE_ID;
178 IF l_dml_mode is null THEN
179 l_dml_mode :='BOTH';
180 END IF;
181
182 IF l_dml_mode <> 'POST' THEN
183
184 user_hook_rec.REQ_LINE_DETAIL_ID := l_req_line_details.REQ_LINE_DETAIL_ID;
185 csp_req_line_details_iuhk.Update_req_line_detail_Pre
186 ( x_return_status => l_return_status
187 ) ;
188 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
189 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
190 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
191 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
192 FND_MSG_PUB.Add;
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194 END IF;
195 END IF;
196
197 IF l_dml_mode = 'BOTH' THEN
198
199 Update CSP_REQ_LINE_DETAILS
200 SET
201 REQUIREMENT_LINE_ID = decode( l_req_line_details.REQUIREMENT_LINE_ID, FND_API.G_MISS_NUM, REQUIREMENT_LINE_ID, l_req_line_details.REQUIREMENT_LINE_ID),
202 CREATED_BY = decode( l_req_line_details.CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, l_req_line_details.CREATED_BY),
203 CREATION_DATE = decode( l_req_line_details.CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, l_req_line_details.CREATION_DATE),
204 LAST_UPDATED_BY = decode( l_req_line_details.LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, l_req_line_details.LAST_UPDATED_BY),
205 LAST_UPDATE_DATE = decode( l_req_line_details.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, l_req_line_details.LAST_UPDATE_DATE),
206 LAST_UPDATE_LOGIN = decode( l_req_line_details.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, l_req_line_details.LAST_UPDATE_LOGIN),
207 SOURCE_TYPE = decode( l_req_line_details.SOURCE_TYPE, FND_API.G_MISS_CHAR, SOURCE_TYPE, l_req_line_details.SOURCE_TYPE),
208 SOURCE_ID = decode( l_req_line_details.SOURCE_ID, FND_API.G_MISS_NUM, SOURCE_ID, l_req_line_details.SOURCE_ID)
209 where REQ_LINE_DETAIL_ID = l_req_line_details.REQ_LINE_DETAIL_ID;
210 END IF;
211
212 IF l_dml_mode <> 'PRE' THEN
213 csp_req_line_details_iuhk.Update_req_line_detail_post
214 ( x_return_status => l_return_status
215 ) ;
216 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
217 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
218 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
219 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
220 FND_MSG_PUB.Add;
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
222 END IF;
223
224 If (SQL%NOTFOUND) then
225 RAISE NO_DATA_FOUND;
226 End If;
227 END IF;
228 END Update_Row;
229
230 PROCEDURE Delete_Row(
231 px_REQ_LINE_DETAIL_ID NUMBER,
232 p_DML_MODE VARCHAR2)
233 IS
234 l_return_status varchar2(100);
235 l_msg_count NUMBER;
236 l_msg_data varchar2(1000);
237 l_api_name_full varchar2(50) := 'CSP_REQ_LINE_DETAILS_PKG.DELETE_ROW';
238 l_dml_mode varchar2(10) := p_dml_mode;
239 BEGIN
240 IF l_dml_mode is null THEN
241 l_dml_mode :='BOTH';
242 END IF;
243
244 IF l_dml_mode <> 'POST' THEN
245 user_hook_rec.REQ_LINE_DETAIL_ID := px_REQ_LINE_DETAIL_ID;
246 csp_req_line_details_iuhk.delete_req_line_detail_Pre
247 ( x_return_status => l_return_status
248 ) ;
249 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
250 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
251 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
252 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
253 FND_MSG_PUB.Add;
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256 END IF;
257
258 IF l_dml_mode = 'BOTH' THEN
259 DELETE FROM CSP_REQ_LINE_DETAILS
260 WHERE REQ_LINE_DETAIL_ID = px_REQ_LINE_DETAIL_ID;
261 END IF;
262
263 IF l_dml_mode <> 'PRE' THEN
264 csp_req_line_details_iuhk.delete_req_line_detail_Post
265 ( x_return_status => l_return_status
266 ) ;
267 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
268 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
269 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
270 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
271 FND_MSG_PUB.Add;
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274 If (SQL%NOTFOUND) then
275 RAISE NO_DATA_FOUND;
276 End If;
277 END IF;
278 END Delete_Row;
279
280 PROCEDURE Lock_Row(
281 px_REQ_LINE_DETAIL_ID IN OUT NOCOPY NUMBER,
282 p_REQUIREMENT_LINE_ID NUMBER,
283 p_CREATED_BY NUMBER,
284 p_CREATION_DATE DATE,
285 p_LAST_UPDATED_BY NUMBER,
286 p_LAST_UPDATE_DATE DATE,
287 p_LAST_UPDATE_LOGIN NUMBER,
288 p_SOURCE_TYPE VARCHAR2,
289 p_SOURCE_ID NUMBER)
290 IS
291 CURSOR C IS
292 SELECT *
293 FROM CSP_REQ_LINE_DETAILS
294 WHERE REQ_LINE_DETAIL_ID = px_REQ_LINE_DETAIL_ID
295 FOR UPDATE of REQ_LINE_DETAIL_ID NOWAIT;
296 Recinfo C%ROWTYPE;
297 BEGIN
298 OPEN C;
299 FETCH C INTO Recinfo;
300 If (C%NOTFOUND) then
301 CLOSE C;
302 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
303 APP_EXCEPTION.RAISE_EXCEPTION;
304 End If;
305 CLOSE C;
306 if (
307 ( Recinfo.REQ_LINE_DETAIL_ID = px_REQ_LINE_DETAIL_ID)
308 AND ( ( Recinfo.REQUIREMENT_LINE_ID = p_REQUIREMENT_LINE_ID)
309 OR ( ( Recinfo.REQUIREMENT_LINE_ID IS NULL )
310 AND ( p_REQUIREMENT_LINE_ID IS NULL )))
311 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
312 OR ( ( Recinfo.CREATED_BY IS NULL )
313 AND ( p_CREATED_BY IS NULL )))
314 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
315 OR ( ( Recinfo.CREATION_DATE IS NULL )
316 AND ( p_CREATION_DATE IS NULL )))
317 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
318 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
319 AND ( p_LAST_UPDATED_BY IS NULL )))
320 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
321 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
322 AND ( p_LAST_UPDATE_DATE IS NULL )))
323 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
324 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
325 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
326 AND ( ( Recinfo.SOURCE_TYPE = p_SOURCE_TYPE)
327 OR ( ( Recinfo.SOURCE_TYPE IS NULL )
328 AND ( p_SOURCE_TYPE IS NULL )))
329 AND ( ( Recinfo.SOURCE_ID = p_SOURCE_ID)
330 OR ( ( Recinfo.SOURCE_ID IS NULL )
331 AND ( p_SOURCE_ID IS NULL )))
332 ) then
333 return;
334 else
335 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
336 APP_EXCEPTION.RAISE_EXCEPTION;
337 End If;
338 END Lock_Row;
339 End CSP_REQ_LINE_DETAILS_PKG;