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