[Home] [Help]
PACKAGE BODY: APPS.CSP_REQUIREMENT_HEADERS_PKG
Source
1 PACKAGE BODY CSP_REQUIREMENT_HEADERS_PKG as
2 /* $Header: csptrqhb.pls 120.2.12020000.2 2012/07/18 08:04:57 kgambhir ship $ */
3 -- Start of Comments
4 -- Package name : CSP_REQUIREMENT_HEADERS_PKG
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSP_REQUIREMENT_HEADERS_PKG';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csptrqhb.pls';
13
14 PROCEDURE Insert_Row(
15 px_REQUIREMENT_HEADER_ID IN OUT NOCOPY 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_OPEN_REQUIREMENT VARCHAR2,
22 p_SHIP_TO_LOCATION_ID NUMBER,
23 p_TASK_ID NUMBER,
24 p_TASK_ASSIGNMENT_ID NUMBER,
25 p_SHIPPING_METHOD_CODE VARCHAR2,
26 p_NEED_BY_DATE DATE,
27 p_DESTINATION_ORGANIZATION_ID NUMBER,
28 p_PARTS_DEFINED VARCHAR2,
29 p_ATTRIBUTE_CATEGORY VARCHAR2,
30 p_ATTRIBUTE1 VARCHAR2,
31 p_ATTRIBUTE2 VARCHAR2,
32 p_ATTRIBUTE3 VARCHAR2,
33 p_ATTRIBUTE4 VARCHAR2,
34 p_ATTRIBUTE5 VARCHAR2,
35 p_ATTRIBUTE6 VARCHAR2,
36 p_ATTRIBUTE7 VARCHAR2,
37 p_ATTRIBUTE8 VARCHAR2,
38 p_ATTRIBUTE9 VARCHAR2,
39 p_ATTRIBUTE10 VARCHAR2,
40 p_ATTRIBUTE11 VARCHAR2,
41 p_ATTRIBUTE12 VARCHAR2,
42 p_ATTRIBUTE13 VARCHAR2,
43 p_ATTRIBUTE14 VARCHAR2,
44 p_ATTRIBUTE15 VARCHAR2,
45 p_ORDER_TYPE_ID NUMBER,
46 p_ADDRESS_TYPE VARCHAR2,
47 p_RESOURCE_ID NUMBER,
48 p_RESOURCE_TYPE VARCHAR2,
49 p_TIMEZONE_ID NUMBER,
50 p_DESTINATION_SUBINVENTORY varchar2,
51 p_SHIP_TO_CONTACT_ID NUMBER,
52 p_DML_MODE VARCHAR2)
53
54 IS
55 CURSOR C2 IS SELECT CSP_REQUIREMENT_HEADERS_S1.nextval FROM sys.dual;
56 l_requirement_header CSP_REQUIREMENT_HEADERS_PVT.Requirement_header_Rec_Type;
57 l_return_status varchar2(100);
58 l_msg_count NUMBER;
59 l_msg_data varchar2(1000);
60 l_api_name_full varchar2(50) := 'CSP_REQUIREMENT_HEADERS_PKG.INSERT_ROW';
61 l_dml_mode varchar2(10) :=p_DML_MODE;
62 BEGIN
63 l_requirement_header.REQUIREMENT_HEADER_ID := px_REQUIREMENT_HEADER_ID;
64 l_requirement_header.CREATED_BY := p_CREATED_BY;
65 l_requirement_header.CREATION_DATE := p_CREATION_DATE;
66 l_requirement_header.LAST_UPDATED_BY := p_LAST_UPDATED_BY ;
67 l_requirement_header.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE ;
68 l_requirement_header.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN ;
69 l_requirement_header.OPEN_REQUIREMENT := p_OPEN_REQUIREMENT ;
70 l_requirement_header.SHIP_TO_LOCATION_ID := p_SHIP_TO_LOCATION_ID ;
71 l_requirement_header.TASK_ID := p_TASK_ID ;
72 l_requirement_header.TASK_ASSIGNMENT_ID := p_TASK_ASSIGNMENT_ID ;
73 l_requirement_header.SHIPPING_METHOD_CODE := p_SHIPPING_METHOD_CODE;
74 l_requirement_header.NEED_BY_DATE := p_NEED_BY_DATE ;
75 l_requirement_header.DESTINATION_ORGANIZATION_ID := p_DESTINATION_ORGANIZATION_ID;
76 l_requirement_header.PARTS_DEFINED := p_PARTS_DEFINED;
77 l_requirement_header.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
78 l_requirement_header.ATTRIBUTE1 := p_ATTRIBUTE1;
79 l_requirement_header.ATTRIBUTE2 := p_ATTRIBUTE2;
80 l_requirement_header.ATTRIBUTE3 := p_ATTRIBUTE3;
81 l_requirement_header.ATTRIBUTE4 := p_ATTRIBUTE4;
82 l_requirement_header.ATTRIBUTE5 := p_ATTRIBUTE5;
83 l_requirement_header.ATTRIBUTE6 := p_ATTRIBUTE6;
84 l_requirement_header.ATTRIBUTE7 := p_ATTRIBUTE7;
85 l_requirement_header.ATTRIBUTE8 := p_ATTRIBUTE8;
86 l_requirement_header.ATTRIBUTE9 := p_ATTRIBUTE9;
87 l_requirement_header.ATTRIBUTE10 := p_ATTRIBUTE10;
88 l_requirement_header.ATTRIBUTE11 := p_ATTRIBUTE11;
89 l_requirement_header.ATTRIBUTE12 := p_ATTRIBUTE12;
90 l_requirement_header.ATTRIBUTE13 := p_ATTRIBUTE13;
91 l_requirement_header.ATTRIBUTE14 := p_ATTRIBUTE14;
92 l_requirement_header.ATTRIBUTE15 := p_ATTRIBUTE15;
93 l_requirement_header.ORDER_TYPE_ID := p_ORDER_TYPE_ID;
94 l_requirement_header.ADDRESS_TYPE := p_ADDRESS_TYPE;
95 l_requirement_header.RESOURCE_ID := p_RESOURCE_ID ;
96 l_requirement_header.RESOURCE_TYPE := p_RESOURCE_TYPE;
97 l_requirement_header.TIMEZONE_ID := p_TIMEZONE_ID;
98 l_requirement_header.DESTINATION_SUBINVENTORY := p_DESTINATION_SUBINVENTORY;
99 l_requirement_header.SHIP_TO_CONTACT_ID := p_SHIP_TO_CONTACT_ID;
100 --Check for DML Mode
101 IF l_dml_mode is null then
102 l_dml_mode := 'BOTH';
103 END IF;
104 --Pre hook
105 IF l_dml_mode <> 'POST' THEN
106 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
107 'Insert_Row',
108 'B', 'C') THEN
109
110 csp_requirement_headers_cuhk.Create_requirement_header_Pre
111 ( px_requirement_header => l_requirement_header,
112 x_return_status => l_return_status,
113 x_msg_count => l_msg_count,
114 x_msg_data => l_msg_data
115 ) ;
116 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
117 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
118 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_CUST_USR_HK');
119 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
120 FND_MSG_PUB.Add;
121 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
122 END IF;
123 END IF;
124
125
126 -- Pre call to the Vertical Type User Hook
127 --
128 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
129 'Insert_Row',
130 'B', 'V') THEN
131 csp_requirement_headers_vuhk.Create_requirement_header_Pre
132 ( px_requirement_header => l_requirement_header,
133 x_return_status => l_return_status,
134 x_msg_count => l_msg_count,
135 x_msg_data => l_msg_data
136 ) ;
137
138 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
139 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
140 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_VERT_USR_HK');
141 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
142 FND_MSG_PUB.Add;
143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144 END IF;
145 END IF;
146
147 csp_requirement_headers_iuhk.Create_requirement_header_Pre
148 ( x_return_status => l_return_status
149 ) ;
150 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
151 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
152 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_INT_USR_HK');
153 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
154 FND_MSG_PUB.Add;
155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156 END IF;
157 If (l_requirement_header.REQUIREMENT_HEADER_ID IS NULL) OR (l_requirement_header.REQUIREMENT_HEADER_ID = FND_API.G_MISS_NUM) then
158 OPEN C2;
159 FETCH C2 INTO px_REQUIREMENT_HEADER_ID;
160 CLOSE C2;
161 End If;
162 END IF;
163 l_requirement_header.REQUIREMENT_HEADER_ID := px_REQUIREMENT_HEADER_ID;
164 user_hooks_rec.REQUIREMENT_HEADER_ID := l_requirement_header.REQUIREMENT_HEADER_ID;
165 IF l_dml_mode = 'BOTH' THEN
166 INSERT INTO CSP_REQUIREMENT_HEADERS(
167 REQUIREMENT_HEADER_ID,
168 CREATED_BY,
169 CREATION_DATE,
170 LAST_UPDATED_BY,
171 LAST_UPDATE_DATE,
172 LAST_UPDATE_LOGIN,
173 OPEN_REQUIREMENT,
174 SHIP_TO_LOCATION_ID,
175 TASK_ID,
176 TASK_ASSIGNMENT_ID,
177 SHIPPING_METHOD_CODE,
178 NEED_BY_DATE,
179 DESTINATION_ORGANIZATION_ID,
180 PARTS_DEFINED,
181 ATTRIBUTE_CATEGORY,
182 ATTRIBUTE1,
183 ATTRIBUTE2,
184 ATTRIBUTE3,
185 ATTRIBUTE4,
186 ATTRIBUTE5,
187 ATTRIBUTE6,
188 ATTRIBUTE7,
189 ATTRIBUTE8,
190 ATTRIBUTE9,
191 ATTRIBUTE10,
192 ATTRIBUTE11,
193 ATTRIBUTE12,
194 ATTRIBUTE13,
195 ATTRIBUTE14,
196 ATTRIBUTE15,
197 ORDER_TYPE_ID,
198 ADDRESS_TYPE,
199 RESOURCE_ID,
200 RESOURCE_TYPE,
201 --TIMEZONE_ID,
202 DESTINATION_SUBINVENTORY,
203 SHIP_TO_CONTACT_ID
204 ) VALUES (
205 px_REQUIREMENT_HEADER_ID,
206 decode( l_requirement_header.CREATED_BY, FND_API.G_MISS_NUM, NULL, l_requirement_header.CREATED_BY),
207 decode( l_requirement_header.CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), l_requirement_header.CREATION_DATE),
208 decode( l_requirement_header.LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, l_requirement_header.LAST_UPDATED_BY),
209 decode( l_requirement_header.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), l_requirement_header.LAST_UPDATE_DATE),
210 decode( l_requirement_header.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, l_requirement_header.LAST_UPDATE_LOGIN),
211 decode( l_requirement_header.OPEN_REQUIREMENT, FND_API.G_MISS_CHAR, NULL, l_requirement_header.OPEN_REQUIREMENT),
212 decode( l_requirement_header.SHIP_TO_LOCATION_ID, FND_API.G_MISS_NUM, NULL, l_requirement_header.SHIP_TO_LOCATION_ID),
213 decode( l_requirement_header.TASK_ID, FND_API.G_MISS_NUM, NULL, l_requirement_header.TASK_ID),
214 decode( l_requirement_header.TASK_ASSIGNMENT_ID, FND_API.G_MISS_NUM, NULL, l_requirement_header.TASK_ASSIGNMENT_ID),
215 decode( l_requirement_header.SHIPPING_METHOD_CODE, FND_API.G_MISS_CHAR, NULL, l_requirement_header.SHIPPING_METHOD_CODE),
216 decode( l_requirement_header.NEED_BY_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), l_requirement_header.NEED_BY_DATE),
217 decode( l_requirement_header.DESTINATION_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, l_requirement_header.DESTINATION_ORGANIZATION_ID),
218 decode( l_requirement_header.PARTS_DEFINED, FND_API.G_MISS_CHAR, NULL, l_requirement_header.PARTS_DEFINED),
219 decode( l_requirement_header.ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE_CATEGORY),
220 decode( l_requirement_header.ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE1),
221 decode( l_requirement_header.ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE2),
222 decode( l_requirement_header.ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE3),
223 decode( l_requirement_header.ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE4),
224 decode( l_requirement_header.ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE5),
225 decode( l_requirement_header.ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE6),
226 decode( l_requirement_header.ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE7),
227 decode( l_requirement_header.ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE8),
228 decode( l_requirement_header.ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE9),
229 decode( l_requirement_header.ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE10),
230 decode( l_requirement_header.ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE11),
231 decode( l_requirement_header.ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE12),
232 decode( l_requirement_header.ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
233 decode( l_requirement_header.ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE14),
234 decode( l_requirement_header.ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ATTRIBUTE15),
235 decode( l_requirement_header.ORDER_TYPE_ID, FND_API.G_MISS_NUM, NULL, l_requirement_header.ORDER_TYPE_ID),
236 decode( l_requirement_header.ADDRESS_TYPE, FND_API.G_MISS_CHAR, NULL, l_requirement_header.ADDRESS_TYPE),
237 decode( l_requirement_header.RESOURCE_ID, FND_API.G_MISS_NUM, NULL, l_requirement_header.RESOURCE_ID),
241 decode( l_requirement_header.SHIP_TO_CONTACT_ID, FND_API.G_MISS_NUM, NULL, l_requirement_header.SHIP_TO_CONTACT_ID)
238 decode( l_requirement_header.RESOURCE_TYPE, FND_API.G_MISS_CHAR, NULL, l_requirement_header.RESOURCE_TYPE),
239 --decode( l_requirement_header.TIMEZONE_ID, FND_API.G_MISS_NUM, NULL, l_requirement_header.TIMEZONE_ID),
240 decode( l_requirement_header.DESTINATION_SUBINVENTORY, FND_API.G_MISS_CHAR, NULL, l_requirement_header.DESTINATION_SUBINVENTORY),
242 );
243 END IF;
244
245 --Post hook
246 IF l_dml_mode <> 'PRE' THEN
247 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
248 'Insert_Row',
249 'A', 'C') THEN
250
251 csp_requirement_headers_cuhk.Create_requirement_header_post
252 ( px_requirement_header => l_requirement_header,
253 x_return_status => l_return_status,
254 x_msg_count => l_msg_count,
255 x_msg_data => l_msg_data
256 ) ;
257
258
259 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
260 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Post Customer User Hook');
261 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_CUST_USR_HK');
262 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
263 FND_MSG_PUB.Add;
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266 END IF;
267
268
269 -- Post call to the Vertical Type User Hook
270 --
271 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
272 'Insert_Row',
273 'A', 'V') THEN
274
275 csp_requirement_headers_vuhk.Create_requirement_header_post
276 ( px_requirement_header => l_requirement_header,
277 x_return_status => l_return_status,
278 x_msg_count => l_msg_count,
279 x_msg_data => l_msg_data
280 ) ;
281 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
282 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Post Vertical User Hook');
283 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_VERT_USR_HK');
284 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
285 FND_MSG_PUB.Add;
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END IF;
288 END IF;
289 csp_requirement_headers_iuhk.Create_requirement_header_post
290 ( x_return_status => l_return_status
291 ) ;
292 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
293 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
294 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_INT_USR_HK');
295 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
296 FND_MSG_PUB.Add;
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299 END IF;
300 End Insert_Row;
301
302 PROCEDURE Update_Row(
303 p_REQUIREMENT_HEADER_ID NUMBER,
304 p_CREATED_BY NUMBER,
305 p_CREATION_DATE DATE,
306 p_LAST_UPDATED_BY NUMBER,
307 p_LAST_UPDATE_DATE DATE,
308 p_LAST_UPDATE_LOGIN NUMBER,
309 p_OPEN_REQUIREMENT VARCHAR2,
310 p_SHIP_TO_LOCATION_ID NUMBER,
311 p_TASK_ID NUMBER,
312 p_TASK_ASSIGNMENT_ID NUMBER,
313 p_SHIPPING_METHOD_CODE VARCHAR2,
314 p_NEED_BY_DATE DATE,
315 p_DESTINATION_ORGANIZATION_ID NUMBER,
316 p_PARTS_DEFINED VARCHAR2,
317 p_ATTRIBUTE_CATEGORY VARCHAR2,
318 p_ATTRIBUTE1 VARCHAR2,
319 p_ATTRIBUTE2 VARCHAR2,
320 p_ATTRIBUTE3 VARCHAR2,
321 p_ATTRIBUTE4 VARCHAR2,
322 p_ATTRIBUTE5 VARCHAR2,
323 p_ATTRIBUTE6 VARCHAR2,
324 p_ATTRIBUTE7 VARCHAR2,
325 p_ATTRIBUTE8 VARCHAR2,
326 p_ATTRIBUTE9 VARCHAR2,
327 p_ATTRIBUTE10 VARCHAR2,
328 p_ATTRIBUTE11 VARCHAR2,
329 p_ATTRIBUTE12 VARCHAR2,
330 p_ATTRIBUTE13 VARCHAR2,
331 p_ATTRIBUTE14 VARCHAR2,
332 p_ATTRIBUTE15 VARCHAR2,
333 p_ORDER_TYPE_ID NUMBER,
334 p_ADDRESS_TYPE VARCHAR2,
335 p_resource_id NUMBER,
336 p_resource_type VARCHAR2,
337 p_TIMEZONE_ID NUMBER,
338 P_DESTINATION_SUBINVENTORY VARCHAR2,
339 p_SHIP_TO_CONTACT_ID NUMBER,
340 p_DML_MODE VARCHAR2)
341
342 IS
343 l_requirement_header CSP_REQUIREMENT_HEADERS_PVT.Requirement_header_Rec_Type;
344 l_return_status varchar2(100);
345 l_msg_count NUMBER;
346 l_msg_data varchar2(1000);
347 l_api_name_full varchar2(50) := 'CSP_REQUIREMENT_HEADERS_PKG.UPDATE_ROW';
348 l_dml_mode varchar2(10) := p_dml_mode;
349 BEGIN
350 l_requirement_header.REQUIREMENT_HEADER_ID := p_REQUIREMENT_HEADER_ID;
351 l_requirement_header.CREATED_BY := p_CREATED_BY;
352 l_requirement_header.CREATION_DATE := p_CREATION_DATE;
353 l_requirement_header.LAST_UPDATED_BY := p_LAST_UPDATED_BY ;
354 l_requirement_header.LAST_UPDATE_DATE := p_LAST_UPDATE_DATE ;
355 l_requirement_header.LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN ;
356 l_requirement_header.OPEN_REQUIREMENT := p_OPEN_REQUIREMENT ;
357 l_requirement_header.SHIP_TO_LOCATION_ID := p_SHIP_TO_LOCATION_ID ;
358 l_requirement_header.TASK_ID := p_TASK_ID ;
359 l_requirement_header.TASK_ASSIGNMENT_ID := p_TASK_ASSIGNMENT_ID ;
363 l_requirement_header.PARTS_DEFINED := p_PARTS_DEFINED;
360 l_requirement_header.SHIPPING_METHOD_CODE := p_SHIPPING_METHOD_CODE;
361 l_requirement_header.NEED_BY_DATE := p_NEED_BY_DATE ;
362 l_requirement_header.DESTINATION_ORGANIZATION_ID := p_DESTINATION_ORGANIZATION_ID;
364 l_requirement_header.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
365 l_requirement_header.ATTRIBUTE1 := p_ATTRIBUTE1;
366 l_requirement_header.ATTRIBUTE2 := p_ATTRIBUTE2;
367 l_requirement_header.ATTRIBUTE3 := p_ATTRIBUTE3;
368 l_requirement_header.ATTRIBUTE4 := p_ATTRIBUTE4;
369 l_requirement_header.ATTRIBUTE5 := p_ATTRIBUTE5;
370 l_requirement_header.ATTRIBUTE6 := p_ATTRIBUTE6;
371 l_requirement_header.ATTRIBUTE7 := p_ATTRIBUTE7;
372 l_requirement_header.ATTRIBUTE8 := p_ATTRIBUTE8;
373 l_requirement_header.ATTRIBUTE9 := p_ATTRIBUTE9;
374 l_requirement_header.ATTRIBUTE10 := p_ATTRIBUTE10;
375 l_requirement_header.ATTRIBUTE11 := p_ATTRIBUTE11;
376 l_requirement_header.ATTRIBUTE12 := p_ATTRIBUTE12;
377 l_requirement_header.ATTRIBUTE13 := p_ATTRIBUTE13;
378 l_requirement_header.ATTRIBUTE14 := p_ATTRIBUTE14;
379 l_requirement_header.ATTRIBUTE15 := p_ATTRIBUTE15;
380 l_requirement_header.ORDER_TYPE_ID := p_ORDER_TYPE_ID;
381 l_requirement_header.ADDRESS_TYPE := p_ADDRESS_TYPE;
382 l_requirement_header.RESOURCE_ID := p_RESOURCE_ID ;
383 l_requirement_header.RESOURCE_TYPE := p_RESOURCE_TYPE;
384 l_requirement_header.TIMEZONE_ID := p_TIMEZONE_ID;
385 l_requirement_header.DESTINATION_SUBINVENTORY := p_DESTINATION_SUBINVENTORY;
386 l_requirement_header.SHIP_TO_CONTACT_ID := p_SHIP_TO_CONTACT_ID;
387
388 IF l_dml_mode is null THEN
389 l_dml_mode := 'BOTH';
390 END IF;
391
392 IF L_dml_mode <> 'POST' THEN
393 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
394 'Update_Row',
395 'B', 'C') THEN
396
397 csp_requirement_headers_cuhk.update_requirement_header_Pre
398 ( px_requirement_header => l_requirement_header,
399 x_return_status => l_return_status,
400 x_msg_count => l_msg_count,
401 x_msg_data => l_msg_data
402 ) ;
403 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
404 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
405 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_CUST_USR_HK');
406 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
407 FND_MSG_PUB.Add;
408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
409 END IF;
410 END IF;
411
412
413 -- Pre call to the Vertical Type User Hook
414 --
415 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
416 'Update_Row',
417 'B', 'V') THEN
418 csp_requirement_headers_vuhk.update_requirement_header_Pre
419 ( px_requirement_header => l_requirement_header,
420 x_return_status => l_return_status,
421 x_msg_count => l_msg_count,
422 x_msg_data => l_msg_data
423 ) ;
424
425 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
426 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
427 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_VERT_USR_HK');
428 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
429 FND_MSG_PUB.Add;
430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
431 END IF;
432 END IF;
433 user_hooks_rec.REQUIREMENT_HEADER_ID := l_requirement_header.REQUIREMENT_HEADER_ID;
434 csp_requirement_headers_iuhk.update_requirement_header_Pre
435 ( x_return_status => l_return_status
436 ) ;
437 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
438 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
439 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_INT_USR_HK');
440 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
441 FND_MSG_PUB.Add;
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444 END IF;
445
446 IF l_dml_mode = 'BOTH' THEN
447 Update CSP_REQUIREMENT_HEADERS
448 SET
449 CREATED_BY = decode( l_requirement_header.CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, l_requirement_header.CREATED_BY),
450 CREATION_DATE = decode( l_requirement_header.CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, l_requirement_header.CREATION_DATE),
451 LAST_UPDATED_BY = decode( l_requirement_header.LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, l_requirement_header.LAST_UPDATED_BY),
452 LAST_UPDATE_DATE = decode( l_requirement_header.LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, l_requirement_header.LAST_UPDATE_DATE),
453 LAST_UPDATE_LOGIN = decode( l_requirement_header.LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, l_requirement_header.LAST_UPDATE_LOGIN),
454 OPEN_REQUIREMENT = decode( l_requirement_header.OPEN_REQUIREMENT, FND_API.G_MISS_CHAR, OPEN_REQUIREMENT, l_requirement_header.OPEN_REQUIREMENT),
458 SHIPPING_METHOD_CODE = decode( l_requirement_header.SHIPPING_METHOD_CODE, FND_API.G_MISS_CHAR, SHIPPING_METHOD_CODE, l_requirement_header.SHIPPING_METHOD_CODE),
455 SHIP_TO_LOCATION_ID = decode( l_requirement_header.SHIP_TO_LOCATION_ID, FND_API.G_MISS_NUM, SHIP_TO_LOCATION_ID, l_requirement_header.SHIP_TO_LOCATION_ID),
456 TASK_ID = decode( l_requirement_header.TASK_ID, FND_API.G_MISS_NUM, TASK_ID, l_requirement_header.TASK_ID),
457 TASK_ASSIGNMENT_ID = decode( l_requirement_header.TASK_ASSIGNMENT_ID, FND_API.G_MISS_NUM, TASK_ASSIGNMENT_ID, l_requirement_header.TASK_ASSIGNMENT_ID),
459 NEED_BY_DATE = decode( l_requirement_header.NEED_BY_DATE, FND_API.G_MISS_DATE, NEED_BY_DATE, l_requirement_header.NEED_BY_DATE),
460 DESTINATION_ORGANIZATION_ID = decode( l_requirement_header.DESTINATION_ORGANIZATION_ID, FND_API.G_MISS_NUM, DESTINATION_ORGANIZATION_ID, l_requirement_header.DESTINATION_ORGANIZATION_ID),
461 PARTS_DEFINED = decode( l_requirement_header.PARTS_DEFINED, FND_API.G_MISS_CHAR, PARTS_DEFINED, l_requirement_header.PARTS_DEFINED),
462 ATTRIBUTE_CATEGORY = decode( l_requirement_header.ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, ATTRIBUTE_CATEGORY, l_requirement_header.ATTRIBUTE_CATEGORY),
463 ATTRIBUTE1 = decode( l_requirement_header.ATTRIBUTE1, FND_API.G_MISS_CHAR, ATTRIBUTE1, l_requirement_header.ATTRIBUTE1),
464 ATTRIBUTE2 = decode( l_requirement_header.ATTRIBUTE2, FND_API.G_MISS_CHAR, ATTRIBUTE2, l_requirement_header.ATTRIBUTE2),
465 ATTRIBUTE3 = decode( l_requirement_header.ATTRIBUTE3, FND_API.G_MISS_CHAR, ATTRIBUTE3, l_requirement_header.ATTRIBUTE3),
466 ATTRIBUTE4 = decode( l_requirement_header.ATTRIBUTE4, FND_API.G_MISS_CHAR, ATTRIBUTE4, l_requirement_header.ATTRIBUTE4),
467 ATTRIBUTE5 = decode( l_requirement_header.ATTRIBUTE5, FND_API.G_MISS_CHAR, ATTRIBUTE5, l_requirement_header.ATTRIBUTE5),
468 ATTRIBUTE6 = decode( l_requirement_header.ATTRIBUTE6, FND_API.G_MISS_CHAR, ATTRIBUTE6, l_requirement_header.ATTRIBUTE6),
469 ATTRIBUTE7 = decode( l_requirement_header.ATTRIBUTE7, FND_API.G_MISS_CHAR, ATTRIBUTE7, l_requirement_header.ATTRIBUTE7),
470 ATTRIBUTE8 = decode( l_requirement_header.ATTRIBUTE8, FND_API.G_MISS_CHAR, ATTRIBUTE8, l_requirement_header.ATTRIBUTE8),
471 ATTRIBUTE9 = decode( l_requirement_header.ATTRIBUTE9, FND_API.G_MISS_CHAR, ATTRIBUTE9, l_requirement_header.ATTRIBUTE9),
472 ATTRIBUTE10 = decode( l_requirement_header.ATTRIBUTE10, FND_API.G_MISS_CHAR, ATTRIBUTE10, l_requirement_header.ATTRIBUTE10),
473 ATTRIBUTE11 = decode( l_requirement_header.ATTRIBUTE11, FND_API.G_MISS_CHAR, ATTRIBUTE11, l_requirement_header.ATTRIBUTE11),
474 ATTRIBUTE12 = decode( l_requirement_header.ATTRIBUTE12, FND_API.G_MISS_CHAR, ATTRIBUTE12, l_requirement_header.ATTRIBUTE12),
475 ATTRIBUTE13 = decode( l_requirement_header.ATTRIBUTE13, FND_API.G_MISS_CHAR, ATTRIBUTE13, l_requirement_header.ATTRIBUTE13),
476 ATTRIBUTE14 = decode( l_requirement_header.ATTRIBUTE14, FND_API.G_MISS_CHAR, ATTRIBUTE14, l_requirement_header.ATTRIBUTE14),
477 ATTRIBUTE15 = decode( l_requirement_header.ATTRIBUTE15, FND_API.G_MISS_CHAR, ATTRIBUTE15, l_requirement_header.ATTRIBUTE15),
478 ORDER_TYPE_ID = decode( l_requirement_header.ORDER_TYPE_ID, FND_API.G_MISS_NUM, ORDER_TYPE_ID, l_requirement_header.ORDER_TYPE_ID),
479 ADDRESS_TYPE = decode( l_requirement_header.ADDRESS_TYPE, FND_API.G_MISS_CHAR, ADDRESS_TYPE, l_requirement_header.ADDRESS_TYPE),
480 RESOURCE_ID = decode( l_requirement_header.RESOURCE_ID, FND_API.G_MISS_NUM, RESOURCE_ID, l_requirement_header.RESOURCE_ID),
481 RESOURCE_TYPE = decode( l_requirement_header.RESOURCE_TYPE, FND_API.G_MISS_CHAR,RESOURCE_TYPE, l_requirement_header.RESOURCE_TYPE),
482 --TIMEZONE_ID = decode( l_requirement_header.TIMEZONE_ID, FND_API.G_MISS_NUM, TIMEZONE_ID, l_requirement_header.TIMEZONE_ID),
483 DESTINATION_SUBINVENTORY = decode( l_requirement_header.DESTINATION_SUBINVENTORY, FND_API.G_MISS_CHAR, DESTINATION_SUBINVENTORY, l_requirement_header.DESTINATION_SUBINVENTORY),
484 SHIP_TO_CONTACT_ID = decode( l_requirement_header.SHIP_TO_CONTACT_ID, FND_API.G_MISS_NUM, SHIP_TO_CONTACT_ID, l_requirement_header.SHIP_TO_CONTACT_ID)
485 where REQUIREMENT_HEADER_ID = l_requirement_header.REQUIREMENT_HEADER_ID;
486
487 If (SQL%NOTFOUND) then
488 RAISE NO_DATA_FOUND;
489 End If;
490 END IF;
491 --Post hook
492 IF l_dml_mode <> 'PRE' THEN
493 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
494 'Update_Row',
495 'A', 'C') THEN
496
497 csp_requirement_headers_cuhk.update_requirement_header_post
498 ( px_requirement_header => l_requirement_header,
499 x_return_status => l_return_status,
500 x_msg_count => l_msg_count,
501 x_msg_data => l_msg_data
502 ) ;
503 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
504 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
505 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_CUST_USR_HK');
506 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
507 FND_MSG_PUB.Add;
508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
509 END IF;
510 END IF;
511
512
513 -- Pre call to the Vertical Type User Hook
514 --
515 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
516 'Update_Row',
517 'A', 'V') THEN
518 csp_requirement_headers_vuhk.update_requirement_header_post
519 ( px_requirement_header => l_requirement_header,
520 x_return_status => l_return_status,
521 x_msg_count => l_msg_count,
522 x_msg_data => l_msg_data
523 ) ;
524
525 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
529 FND_MSG_PUB.Add;
526 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
527 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_VERT_USR_HK');
528 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
531 END IF;
532 END IF;
533 csp_requirement_headers_iuhk.update_requirement_header_post
534 ( x_return_status => l_return_status
535 ) ;
536 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
537 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
538 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_INT_USR_HK');
539 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
540 FND_MSG_PUB.Add;
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END IF;
543 END IF;
544 END Update_Row;
545
546 PROCEDURE Delete_Row(
547 p_REQUIREMENT_HEADER_ID NUMBER,
548 p_DML_MODE VARCHAR2)
549 IS
550 l_return_status varchar2(100);
551 l_msg_count NUMBER;
552 l_msg_data varchar2(1000);
553 l_api_name_full varchar2(50) := 'CSP_REQUIREMENT_HEADERS_PKG.DELETE_ROW';
554 l_dml_mode varchar2(10) := p_DML_MODE;
555 BEGIN
556 IF l_dml_mode is null THEN
557 l_dml_mode := 'BOTH';
558 END IF;
559
560 IF l_dml_mode <> 'POST' THEN
561 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
562 'Delete_Row',
563 'B', 'C') THEN
564
565 csp_requirement_headers_cuhk.Delete_requirement_header_pre
566 (
567 p_header_id => p_REQUIREMENT_HEADER_ID,
568 x_return_status => l_return_status,
569 x_msg_count => l_msg_count,
570 x_msg_data => l_msg_data
571 ) ;
572 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
573 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
574 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_CUST_USR_HK');
575 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
576 FND_MSG_PUB.Add;
577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
578 END IF;
579 END IF;
580
581
582 -- Pre call to the Vertical Type User Hook
583 --
584 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
585 'Delete_Row',
586 'B', 'V') THEN
587 csp_requirement_headers_vuhk.Delete_requirement_header_pre
588 (
589 p_header_id => p_REQUIREMENT_HEADER_ID,
590 x_return_status => l_return_status,
591 x_msg_count => l_msg_count,
592 x_msg_data => l_msg_data
593 ) ;
594
595 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
596 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
597 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_VERT_USR_HK');
598 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
599 FND_MSG_PUB.Add;
600 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
601 END IF;
602 END IF;
603 user_hooks_rec.REQUIREMENT_HEADER_ID := p_REQUIREMENT_HEADER_ID;
604 csp_requirement_headers_iuhk.delete_requirement_header_Pre
605 ( x_return_status => l_return_status
606 ) ;
607 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
608 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
609 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_PRE_INT_USR_HK');
610 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
611 FND_MSG_PUB.Add;
612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614 END IF;
615
616 IF l_dml_mode = 'BOTH' THEN
617 DELETE FROM CSP_REQUIREMENT_HEADERS
618 WHERE REQUIREMENT_HEADER_ID = p_REQUIREMENT_HEADER_ID;
619 If (SQL%NOTFOUND) then
620 RAISE NO_DATA_FOUND;
621 End If;
622 END IF;
623
624 IF l_dml_mode <> 'PRE' THEN
625 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
626 'Delete_Row',
627 'A', 'C') THEN
628
629 csp_requirement_headers_cuhk.Delete_requirement_header_post
630 (
631 p_header_id => p_REQUIREMENT_HEADER_ID,
632 x_return_status => l_return_status,
633 x_msg_count => l_msg_count,
634 x_msg_data => l_msg_data
635 ) ;
636 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
637 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Customer User Hook');
638 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_CUST_USR_HK');
639 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
640 FND_MSG_PUB.Add;
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 END IF;
643 END IF;
644
645
646 -- Pre call to the Vertical Type User Hook
647 --
648 IF jtf_usr_hks.Ok_To_Execute('CSP_REQUIREMENT_HEADERS_PKG',
649 'Delete_Row',
650 'A', 'V') THEN
651 csp_requirement_headers_vuhk.Delete_requirement_header_post
652 (
653 p_header_id => p_REQUIREMENT_HEADER_ID,
654 x_return_status => l_return_status,
655 x_msg_count => l_msg_count,
659 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
656 x_msg_data => l_msg_data
657 ) ;
658
660 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
661 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_VERT_USR_HK');
662 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
663 FND_MSG_PUB.Add;
664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
665 END IF;
666 END IF;
667 csp_requirement_headers_iuhk.delete_requirement_header_post
668 ( x_return_status => l_return_status
669 ) ;
670 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
671 --DBMS_OUTPUT.PUT_LINE('Returned Error Status from the Pre Vertical User Hook');
672 FND_MESSAGE.Set_Name('CSP', 'CSP_ERR_POST_INT_USR_HK');
673 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
674 FND_MSG_PUB.Add;
675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
676 END IF;
677 END IF;
678 END Delete_Row;
679
680 PROCEDURE Lock_Row(
681 p_REQUIREMENT_HEADER_ID NUMBER,
682 p_CREATED_BY NUMBER,
683 p_CREATION_DATE DATE,
684 p_LAST_UPDATED_BY NUMBER,
685 p_LAST_UPDATE_DATE DATE,
686 p_LAST_UPDATE_LOGIN NUMBER,
687 p_OPEN_REQUIREMENT VARCHAR2,
688 p_SHIP_TO_LOCATION_ID NUMBER,
689 p_TASK_ID NUMBER,
690 p_TASK_ASSIGNMENT_ID NUMBER,
691 p_SHIPPING_METHOD_CODE VARCHAR2,
692 p_NEED_BY_DATE DATE,
693 p_DESTINATION_ORGANIZATION_ID NUMBER,
694 p_PARTS_DEFINED VARCHAR2,
695 p_ATTRIBUTE_CATEGORY VARCHAR2,
696 p_ATTRIBUTE1 VARCHAR2,
697 p_ATTRIBUTE2 VARCHAR2,
698 p_ATTRIBUTE3 VARCHAR2,
699 p_ATTRIBUTE4 VARCHAR2,
700 p_ATTRIBUTE5 VARCHAR2,
701 p_ATTRIBUTE6 VARCHAR2,
702 p_ATTRIBUTE7 VARCHAR2,
703 p_ATTRIBUTE8 VARCHAR2,
704 p_ATTRIBUTE9 VARCHAR2,
705 p_ATTRIBUTE10 VARCHAR2,
706 p_ATTRIBUTE11 VARCHAR2,
707 p_ATTRIBUTE12 VARCHAR2,
708 p_ATTRIBUTE13 VARCHAR2,
709 p_ATTRIBUTE14 VARCHAR2,
710 p_ATTRIBUTE15 VARCHAR2,
711 p_ORDER_TYPE_ID NUMBER,
712 p_ADDRESS_TYPE VARCHAR2,
713 p_resource_id NUMBER,
714 p_resource_type VARCHAR2,
715 p_TIMEZONE_ID NUMBER,
716 P_DESTINATION_SUBINVENTORY VARCHAR2,
717 p_SHIP_TO_CONTACT_ID NUMBER)
718
719 IS
720 CURSOR C IS
721 SELECT *
722 FROM CSP_REQUIREMENT_HEADERS
723 WHERE REQUIREMENT_HEADER_ID = p_REQUIREMENT_HEADER_ID
724 FOR UPDATE of REQUIREMENT_HEADER_ID NOWAIT;
725 Recinfo C%ROWTYPE;
726 BEGIN
727 OPEN C;
728 FETCH C INTO Recinfo;
729 If (C%NOTFOUND) then
730 CLOSE C;
731 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
732 APP_EXCEPTION.RAISE_EXCEPTION;
733 End If;
734 CLOSE C;
735 if (
736 ( Recinfo.REQUIREMENT_HEADER_ID = p_REQUIREMENT_HEADER_ID)
737 AND ( ( Recinfo.CREATED_BY = p_CREATED_BY)
738 OR ( ( Recinfo.CREATED_BY IS NULL )
739 AND ( p_CREATED_BY IS NULL )))
740 AND ( ( Recinfo.CREATION_DATE = p_CREATION_DATE)
741 OR ( ( Recinfo.CREATION_DATE IS NULL )
742 AND ( p_CREATION_DATE IS NULL )))
743 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
744 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
745 AND ( p_LAST_UPDATED_BY IS NULL )))
746 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
747 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
748 AND ( p_LAST_UPDATE_DATE IS NULL )))
749 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
750 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
751 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
752 AND ( ( Recinfo.OPEN_REQUIREMENT = p_OPEN_REQUIREMENT)
753 OR ( ( Recinfo.OPEN_REQUIREMENT IS NULL )
754 AND ( p_OPEN_REQUIREMENT IS NULL )))
755 AND ( ( Recinfo.SHIP_TO_LOCATION_ID = p_SHIP_TO_LOCATION_ID)
756 OR ( ( Recinfo.SHIP_TO_LOCATION_ID IS NULL )
757 AND ( p_SHIP_TO_LOCATION_ID IS NULL )))
758 AND ( ( Recinfo.TASK_ID = p_TASK_ID)
759 OR ( ( Recinfo.TASK_ID IS NULL )
760 AND ( p_TASK_ID IS NULL )))
761 AND ( ( Recinfo.TASK_ASSIGNMENT_ID = p_TASK_ASSIGNMENT_ID)
762 OR ( ( Recinfo.TASK_ASSIGNMENT_ID IS NULL )
763 AND ( p_TASK_ASSIGNMENT_ID IS NULL )))
764 AND ( ( Recinfo.SHIPPING_METHOD_CODE = p_SHIPPING_METHOD_CODE)
765 OR ( ( Recinfo.SHIPPING_METHOD_CODE IS NULL )
766 AND ( p_SHIPPING_METHOD_CODE IS NULL )))
767 AND ( ( Recinfo.NEED_BY_DATE = p_NEED_BY_DATE)
768 OR ( ( Recinfo.NEED_BY_DATE IS NULL )
769 AND ( p_NEED_BY_DATE IS NULL )))
770 AND ( ( Recinfo.DESTINATION_ORGANIZATION_ID = p_DESTINATION_ORGANIZATION_ID)
771 OR ( ( Recinfo.DESTINATION_ORGANIZATION_ID IS NULL )
772 AND ( p_DESTINATION_ORGANIZATION_ID IS NULL )))
773 AND ( ( Recinfo.PARTS_DEFINED = p_PARTS_DEFINED)
774 OR ( ( Recinfo.PARTS_DEFINED IS NULL )
775 AND ( p_PARTS_DEFINED IS NULL )))
776 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = p_ATTRIBUTE_CATEGORY)
777 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
778 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
779 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
783 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
780 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
781 AND ( p_ATTRIBUTE1 IS NULL )))
782 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
784 AND ( p_ATTRIBUTE2 IS NULL )))
785 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
786 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
787 AND ( p_ATTRIBUTE3 IS NULL )))
788 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
789 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
790 AND ( p_ATTRIBUTE4 IS NULL )))
791 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
792 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
793 AND ( p_ATTRIBUTE5 IS NULL )))
794 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
795 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
796 AND ( p_ATTRIBUTE6 IS NULL )))
797 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
798 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
799 AND ( p_ATTRIBUTE7 IS NULL )))
800 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
801 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
802 AND ( p_ATTRIBUTE8 IS NULL )))
803 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
804 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
805 AND ( p_ATTRIBUTE9 IS NULL )))
806 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
807 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
808 AND ( p_ATTRIBUTE10 IS NULL )))
809 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
810 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
811 AND ( p_ATTRIBUTE11 IS NULL )))
812 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
813 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
814 AND ( p_ATTRIBUTE12 IS NULL )))
815 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
816 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
817 AND ( p_ATTRIBUTE13 IS NULL )))
818 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
819 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
820 AND ( p_ATTRIBUTE14 IS NULL )))
821 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
822 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
823 AND ( p_ATTRIBUTE15 IS NULL )))
824 AND ( ( Recinfo.ORDER_TYPE_ID = p_ORDER_TYPE_ID)
825 OR ( ( Recinfo.ORDER_TYPE_ID IS NULL )
826 AND ( p_ORDER_TYPE_ID IS NULL )))
827 AND ( ( Recinfo.ADDRESS_TYPE = p_ADDRESS_TYPE)
828 OR ( ( Recinfo.ADDRESS_TYPE IS NULL )
829 AND ( p_ADDRESS_TYPE IS NULL )))
830 AND ( ( Recinfo.RESOURCE_ID = p_RESOURCE_ID)
831 OR ( ( Recinfo.RESOURCE_ID IS NULL )
832 AND ( p_RESOURCE_ID IS NULL )))
833 AND ( ( Recinfo.RESOURCE_TYPE = p_RESOURCE_TYPE)
834 OR ( ( Recinfo.RESOURCE_TYPE IS NULL )
835 AND ( p_RESOURCE_TYPE IS NULL )))
836 /*AND ( ( Recinfo.TIMEZONE_ID = p_TIMEZONE_ID)
837 OR ( ( Recinfo.TIMEZONE_ID IS NULL )
838 AND ( p_TIMEZONE_ID IS NULL )))*/
839 AND ( ( Recinfo.DESTINATION_SUBINVENTORY = p_DESTINATION_SUBINVENTORY)
840 OR ( ( Recinfo.DESTINATION_SUBINVENTORY IS NULL )
841 AND ( p_DESTINATION_SUBINVENTORY IS NULL )))
842 AND ( ( Recinfo.SHIP_TO_CONTACT_ID = p_SHIP_TO_CONTACT_ID)
843 OR ( ( Recinfo.SHIP_TO_CONTACT_ID IS NULL )
844 AND ( p_SHIP_TO_CONTACT_ID IS NULL )))
845 ) then
846 return;
847 else
848 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
849 APP_EXCEPTION.RAISE_EXCEPTION;
850 End If;
851 END Lock_Row;
852
853 End CSP_REQUIREMENT_HEADERS_PKG;