[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.1 2006/03/10 10:39:43 jjalla noship $ */
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 IS
24 CURSOR C2 IS SELECT CSP_REQ_LINE_DETAILS_S1.nextval FROM sys.dual;
25 l_req_line_details CSP_REQ_LINE_DETAILS_PVT.Req_line_details_Rec_Type;
26 l_return_status varchar2(100);
27 l_msg_count NUMBER;
28 l_msg_data varchar2(1000);
29 l_api_name_full varchar2(50) := 'CSP_REQ_LINE_DETAILS_PKG.INSERT_ROW';
30
31
32
33 BEGIN
34 l_req_line_details.REQ_LINE_DETAIL_ID := px_REQ_LINE_DETAIL_ID ;
35 l_req_line_details.REQUIREMENT_LINE_ID := p_REQUIREMENT_LINE_ID ;
36 l_req_line_details.CREATED_BY := p_CREATED_BY;
37 l_req_line_details.CREATION_DATE := p_CREATION_DATE;
38 l_req_line_details.LAST_UPDATED_BY :=p_LAST_UPDATED_BY;
39 l_req_line_details.LAST_UPDATE_DATE :=p_LAST_UPDATE_DATE;
40 l_req_line_details.LAST_UPDATE_LOGIN :=p_LAST_UPDATE_LOGIN ;
41 l_req_line_details.SOURCE_TYPE :=p_SOURCE_TYPE;
42 l_req_line_details.SOURCE_ID :=p_SOURCE_ID;
43
44 csp_req_line_details_iuhk.Create_req_line_detail_Pre
45 ( x_return_status => l_return_status
46 ) ;
47 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
48 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
49 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
50 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
51 FND_MSG_PUB.Add;
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54
55
56
57 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
58 OPEN C2;
59 FETCH C2 INTO px_REQ_LINE_DETAIL_ID;
60 CLOSE C2;
61 End If;
62 l_req_line_details.REQ_LINE_DETAIL_ID := px_REQ_LINE_DETAIL_ID ;
63 user_hook_rec.REQ_LINE_DETAIL_ID := l_req_line_details.REQ_LINE_DETAIL_ID;
64
65 INSERT INTO CSP_REQ_LINE_DETAILS(
66 REQ_LINE_DETAIL_ID,
67 REQUIREMENT_LINE_ID,
68 CREATED_BY,
69 CREATION_DATE,
70 LAST_UPDATED_BY,
71 LAST_UPDATE_DATE,
72 LAST_UPDATE_LOGIN,
73 SOURCE_TYPE,
74 SOURCE_ID
75 )
76 VALUES (
77 px_REQ_LINE_DETAIL_ID,
78 decode( l_req_line_details.REQUIREMENT_LINE_ID, FND_API.G_MISS_NUM, NULL, l_req_line_details.REQUIREMENT_LINE_ID),
79 decode( l_req_line_details.CREATED_BY, FND_API.G_MISS_NUM, NULL, l_req_line_details.CREATED_BY),
80 decode( l_req_line_details.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), l_req_line_details.CREATION_DATE),
81 decode( l_req_line_details.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, l_req_line_details.LAST_UPDATED_BY),
82 decode( l_req_line_details.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), l_req_line_details.LAST_UPDATE_DATE),
83 decode( l_req_line_details.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, l_req_line_details.LAST_UPDATE_LOGIN),
84 decode( l_req_line_details.SOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, l_req_line_details.SOURCE_TYPE),
85 decode( l_req_line_details.SOURCE_ID, FND_API.G_MISS_NUM, NULL, l_req_line_details.SOURCE_ID)
86 );
87
88 user_hook_rec.REQUIREMENT_LINE_ID := l_req_line_details.REQUIREMENT_LINE_ID;
89 csp_req_line_details_iuhk.Create_req_line_detail_Post
90 ( x_return_status => l_return_status
91 ) ;
92 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
93 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
94 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_INT_USR_HK');
95 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
96 FND_MSG_PUB.Add;
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99
100 End Insert_Row;
101
102 PROCEDURE Update_Row(
103 px_REQ_LINE_DETAIL_ID IN OUT NOCOPY NUMBER,
104 p_REQUIREMENT_LINE_ID NUMBER,
105 p_CREATED_BY NUMBER,
106 p_CREATION_DATE DATE,
107 p_LAST_UPDATED_BY NUMBER,
108 p_LAST_UPDATE_DATE DATE,
109 p_LAST_UPDATE_LOGIN NUMBER,
110 p_SOURCE_TYPE VARCHAR2,
111 p_SOURCE_ID NUMBER)
112 IS
113 l_req_line_details CSP_REQ_line_details_PVT.Req_LINE_Details_Rec_Type;
114 l_api_name_full varchar2(50) := 'CSP_REQ_LINE_DETAILS_PKG.UPDATE_ROW';
115 l_return_status varchar2(100);
116 l_msg_count NUMBER;
117 l_msg_data varchar2(1000);
118 BEGIN
119 l_req_line_details.REQ_LINE_DETAIL_ID := px_REQ_LINE_DETAIL_ID ;
120 l_req_line_details.REQUIREMENT_LINE_ID := p_REQUIREMENT_LINE_ID ;
121 l_req_line_details.CREATED_BY := p_CREATED_BY;
122 l_req_line_details.CREATION_DATE := p_CREATION_DATE;
123 l_req_line_details.LAST_UPDATED_BY :=p_LAST_UPDATED_BY;
124 l_req_line_details.LAST_UPDATE_DATE :=p_LAST_UPDATE_DATE;
125 l_req_line_details.LAST_UPDATE_LOGIN :=p_LAST_UPDATE_LOGIN ;
126 l_req_line_details.SOURCE_TYPE :=p_SOURCE_TYPE;
127 l_req_line_details.SOURCE_ID :=p_SOURCE_ID;
128
129 user_hook_rec.REQ_LINE_DETAIL_ID := l_req_line_details.REQ_LINE_DETAIL_ID;
130 csp_req_line_details_iuhk.Update_req_line_detail_Pre
131 ( x_return_status => l_return_status
132 ) ;
133 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
134 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
135 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
136 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
137 FND_MSG_PUB.Add;
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END IF;
140
141
142 Update CSP_REQ_LINE_DETAILS
143 SET
144 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),
145 CREATED_BY = decode( l_req_line_details.CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, l_req_line_details.CREATED_BY),
146 CREATION_DATE = decode( l_req_line_details.CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, l_req_line_details.CREATION_DATE),
147 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),
148 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),
149 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),
150 SOURCE_TYPE = decode( l_req_line_details.SOURCE_TYPE, FND_API.G_MISS_CHAR, SOURCE_TYPE, l_req_line_details.SOURCE_TYPE),
151 SOURCE_ID = decode( l_req_line_details.SOURCE_ID, FND_API.G_MISS_NUM, SOURCE_ID, l_req_line_details.SOURCE_ID)
152 where REQ_LINE_DETAIL_ID = l_req_line_details.REQ_LINE_DETAIL_ID;
153
154 csp_req_line_details_iuhk.Update_req_line_detail_post
155 ( x_return_status => l_return_status
156 ) ;
157 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
158 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
159 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
160 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
161 FND_MSG_PUB.Add;
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
163 END IF;
164
165 If (SQL%NOTFOUND) then
166 RAISE NO_DATA_FOUND;
167 End If;
168
169 END Update_Row;
170
171 PROCEDURE Delete_Row(
172 px_REQ_LINE_DETAIL_ID NUMBER)
173 IS
174 l_return_status varchar2(100);
175 l_msg_count NUMBER;
176 l_msg_data varchar2(1000);
177 l_api_name_full varchar2(50) := 'CSP_REQ_LINE_DETAILS_PKG.DELETE_ROW';
178
179 BEGIN
180 user_hook_rec.REQ_LINE_DETAIL_ID := px_REQ_LINE_DETAIL_ID;
181 csp_req_line_details_iuhk.delete_req_line_detail_Pre
182 ( x_return_status => l_return_status
183 ) ;
184 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
185 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
186 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
187 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
188 FND_MSG_PUB.Add;
189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
190 END IF;
191 DELETE FROM CSP_REQ_LINE_DETAILS
192 WHERE REQ_LINE_DETAIL_ID = px_REQ_LINE_DETAIL_ID;
193 csp_req_line_details_iuhk.delete_req_line_detail_Post
194 ( x_return_status => l_return_status
195 ) ;
196 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
197 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
198 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_INT_CUST_USR_HK');
199 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
200 FND_MSG_PUB.Add;
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202 END IF;
203 If (SQL%NOTFOUND) then
204 RAISE NO_DATA_FOUND;
205 End If;
206 END Delete_Row;
207
208 PROCEDURE Lock_Row(
209 px_REQ_LINE_DETAIL_ID IN OUT NOCOPY NUMBER,
210 p_REQUIREMENT_LINE_ID NUMBER,
211 p_CREATED_BY NUMBER,
212 p_CREATION_DATE DATE,
213 p_LAST_UPDATED_BY NUMBER,
214 p_LAST_UPDATE_DATE DATE,
215 p_LAST_UPDATE_LOGIN NUMBER,
216 p_SOURCE_TYPE VARCHAR2,
217 p_SOURCE_ID NUMBER)
218 IS
219 CURSOR C IS
220 SELECT *
221 FROM CSP_REQ_LINE_DETAILS
222 WHERE REQ_LINE_DETAIL_ID = px_REQ_LINE_DETAIL_ID
223 FOR UPDATE of REQ_LINE_DETAIL_ID NOWAIT;
224 Recinfo C%ROWTYPE;
225 BEGIN
226 OPEN C;
227 FETCH C INTO Recinfo;
228 If (C%NOTFOUND) then
229 CLOSE C;
230 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
231 APP_EXCEPTION.RAISE_EXCEPTION;
232 End If;
233 CLOSE C;
234 if (
235 ( Recinfo.REQ_LINE_DETAIL_ID = px_REQ_LINE_DETAIL_ID)
236 AND ( ( Recinfo.REQUIREMENT_LINE_ID = p_REQUIREMENT_LINE_ID)
237 OR ( ( Recinfo.REQUIREMENT_LINE_ID IS NULL )
238 AND ( p_REQUIREMENT_LINE_ID IS NULL )))
239 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
240 OR ( ( Recinfo.CREATED_BY IS NULL )
241 AND ( p_CREATED_BY IS NULL )))
242 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
243 OR ( ( Recinfo.CREATION_DATE IS NULL )
244 AND ( p_CREATION_DATE IS NULL )))
245 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
246 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
247 AND ( p_LAST_UPDATED_BY IS NULL )))
248 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
249 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
250 AND ( p_LAST_UPDATE_DATE IS NULL )))
251 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
252 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
253 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
254 AND ( ( Recinfo.SOURCE_TYPE = p_SOURCE_TYPE)
255 OR ( ( Recinfo.SOURCE_TYPE IS NULL )
256 AND ( p_SOURCE_TYPE IS NULL )))
257 AND ( ( Recinfo.SOURCE_ID = p_SOURCE_ID)
258 OR ( ( Recinfo.SOURCE_ID IS NULL )
259 AND ( p_SOURCE_ID IS NULL )))
260 ) then
261 return;
262 else
263 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
264 APP_EXCEPTION.RAISE_EXCEPTION;
265 End If;
266 END Lock_Row;
267 End CSP_REQ_LINE_DETAILS_PKG;