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