[Home] [Help]
PACKAGE BODY: APPS.CSFW_REQ_PUB
Source
1 PACKAGE BODY csfw_req_pub AS
2 /*$Header: csfwreqb.pls 120.2 2006/02/14 05:30:05 htank noship $*/
3
4 PROCEDURE CREATE_REQUIREMENT_HEADER
5 (
6 p_task_id IN NUMBER
7 , p_location_id IN NUMBER
8 , p_ATTRIBUTE1 IN VARCHAR2
9 , p_ATTRIBUTE2 IN VARCHAR2
10 , p_ATTRIBUTE3 IN VARCHAR2
11 , p_ATTRIBUTE4 IN VARCHAR2
12 , p_ATTRIBUTE5 IN VARCHAR2
13 , p_ATTRIBUTE6 IN VARCHAR2
14 , p_ATTRIBUTE7 IN VARCHAR2
15 , p_ATTRIBUTE8 IN VARCHAR2
16 , p_ATTRIBUTE9 IN VARCHAR2
17 , p_ATTRIBUTE10 IN VARCHAR2
18 , p_ATTRIBUTE11 IN VARCHAR2
19 , p_ATTRIBUTE12 IN VARCHAR2
20 , p_ATTRIBUTE13 IN VARCHAR2
21 , p_ATTRIBUTE14 IN VARCHAR2
22 , p_ATTRIBUTE15 IN VARCHAR2
23 , p_ATTRIBUTE_CATEGORY IN VARCHAR2
24 , x_header_id OUT NOCOPY NUMBER
25 , x_error_id OUT NOCOPY NUMBER
26 , x_error OUT NOCOPY VARCHAR2
27 )
28 IS
29 l_header_id number;
30 l_req_hdr_rec CSP_Requirement_headers_PVT.REQUIREMENT_HEADER_Rec_Type;
31 l_Return_Status VARCHAR2(1);
32 l_Msg_Count NUMBER;
33 l_Msg_Data VARCHAR2(2000);
34 l_msg_index_out NUMBER;
35 l_data VARCHAR2(255);
36
37 cursor c_next_header is select CSP_REQUIREMENT_HEADERS_S1.nextval from dual;
38
39 BEGIN
40 open c_next_header;
41 fetch c_next_header into l_header_id ;
42 close c_next_header;
43
44 l_req_hdr_rec.REQUIREMENT_HEADER_ID := l_header_id;
45 l_req_hdr_rec.OPEN_REQUIREMENT := 'Y';
46 l_req_hdr_rec.TASK_ID := p_task_id;
47 l_req_hdr_rec.PARTS_DEFINED := 'Y';
48
49 IF (p_location_id <> 0) THEN
50 l_req_hdr_rec.SHIP_TO_LOCATION_ID := p_location_id;
51 l_req_hdr_rec.ADDRESS_TYPE := 'C';--Customer
52 ELSE
53 l_req_hdr_rec.ADDRESS_TYPE := 'R'; -- Resource bug # 5023696
54 END IF;
55
56 -- bug # 5023696
57 l_req_hdr_rec.ORDER_TYPE_ID := 1430;
58
59 -- set other parameters
60 IF p_ATTRIBUTE1 IS NULL OR p_ATTRIBUTE1 <> '#%*%#'
61 THEN
62 l_req_hdr_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
63 END IF;
64
65 IF p_ATTRIBUTE2 IS NULL OR p_ATTRIBUTE2 <> '#%*%#'
66 THEN
67 l_req_hdr_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
68 END IF;
69
70 IF p_ATTRIBUTE3 IS NULL OR p_ATTRIBUTE3 <> '#%*%#'
71 THEN
72 l_req_hdr_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
73 END IF;
74
75 IF p_ATTRIBUTE4 IS NULL OR p_ATTRIBUTE4 <> '#%*%#'
76 THEN
77 l_req_hdr_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
78 END IF;
79
80 IF p_ATTRIBUTE5 IS NULL OR p_ATTRIBUTE5 <> '#%*%#'
81 THEN
82 l_req_hdr_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
83 END IF;
84
85 IF p_ATTRIBUTE6 IS NULL OR p_ATTRIBUTE6 <> '#%*%#'
86 THEN
87 l_req_hdr_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
88 END IF;
89
90 IF p_ATTRIBUTE7 IS NULL OR p_ATTRIBUTE7 <> '#%*%#'
91 THEN
92 l_req_hdr_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
93 END IF;
94
95 IF p_ATTRIBUTE8 IS NULL OR p_ATTRIBUTE8 <> '#%*%#'
96 THEN
97 l_req_hdr_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
98 END IF;
99
100 IF p_ATTRIBUTE9 IS NULL OR p_ATTRIBUTE9 <> '#%*%#'
101 THEN
102 l_req_hdr_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
103 END IF;
104
105 IF p_ATTRIBUTE10 IS NULL OR p_ATTRIBUTE10 <> '#%*%#'
106 THEN
107 l_req_hdr_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
108 END IF;
109
110 IF p_ATTRIBUTE11 IS NULL OR p_ATTRIBUTE11 <> '#%*%#'
111 THEN
112 l_req_hdr_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
113 END IF;
114
115 IF p_ATTRIBUTE12 IS NULL OR p_ATTRIBUTE12 <> '#%*%#'
116 THEN
117 l_req_hdr_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
118 END IF;
119
120 IF p_ATTRIBUTE13 IS NULL OR p_ATTRIBUTE13 <> '#%*%#'
121 THEN
122 l_req_hdr_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
123 END IF;
124
125 IF p_ATTRIBUTE14 IS NULL OR p_ATTRIBUTE14 <> '#%*%#'
126 THEN
127 l_req_hdr_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
128 END IF;
129
130 IF p_ATTRIBUTE15 IS NULL OR p_ATTRIBUTE15 <> '#%*%#'
131 THEN
132 l_req_hdr_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
133 END IF;
134
135 IF p_ATTRIBUTE_CATEGORY IS NULL OR p_ATTRIBUTE_CATEGORY <> '#%*%#'
136 THEN
137 l_req_hdr_rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
138 END IF;
139
140 CSP_Requirement_headers_PVT.Create_requirement_headers(
141 P_Api_Version_Number => 1.0 ,
142 P_Init_Msg_List => FND_API.G_FALSE,
143 P_Commit => FND_API.G_TRUE,
144 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
145 P_REQUIREMENT_HEADER_Rec => l_req_hdr_rec,
146 X_REQUIREMENT_HEADER_ID => x_header_id ,
147 X_Return_Status => l_Return_Status,
148 X_Msg_Count => l_Msg_Count ,
149 X_Msg_Data => l_Msg_Data
150 );
151
152 IF l_Return_Status = FND_API.G_RET_STS_SUCCESS
153 THEN
154 /* API-call was successfull */
155 x_error_id := 0;
156 x_error := FND_API.G_RET_STS_SUCCESS;
157 commit work;
158 ELSE
159 FOR l_counter IN 1 .. l_msg_count
160 LOOP
161 fnd_msg_pub.get
162 ( p_msg_index => l_counter
163 , p_encoded => FND_API.G_FALSE
164 , p_data => l_Msg_Data
165 , p_msg_index_out => l_msg_index_out
166 );
167 --dbms_output.put_line( 'Message: '||l_data );
168 END LOOP ;
169 x_error_id := 1;
170 x_error := l_data;
171 END IF;
172 EXCEPTION
173 WHEN OTHERS
174 THEN
175 x_error_id := -1;
176 x_error := SQLERRM;
177
178 END CREATE_REQUIREMENT_HEADER;
179
180
181
182 PROCEDURE DELETE_REQUIREMENT_HEADER
183 (
184 p_header_id IN NUMBER
185 , x_error_id OUT NOCOPY NUMBER
186 , x_error OUT NOCOPY VARCHAR2
187 )
188 IS
189 l_req_hdr_rec CSP_Requirement_headers_PVT.REQUIREMENT_HEADER_Rec_Type;
190 l_Return_Status VARCHAR2(1);
191 l_Msg_Count NUMBER;
192 l_Msg_Data VARCHAR2(2000);
193 l_msg_index_out NUMBER;
194 l_data VARCHAR2(255);
195 BEGIN
196
197 l_req_hdr_rec.REQUIREMENT_HEADER_ID := p_header_id;
198
199 CSP_Requirement_headers_PVT.Delete_requirement_headers(
200 P_Api_Version_Number => 1.0,
201 P_Init_Msg_List => FND_API.G_FALSE,
202 P_Commit => FND_API.G_TRUE,
203 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
204 P_REQUIREMENT_HEADER_Rec => l_req_hdr_rec,
205 X_Return_Status => l_Return_Status,
206 X_Msg_Count => l_Msg_Count,
207 X_Msg_Data => l_Msg_Data
208 );
209
210 IF l_Return_Status = FND_API.G_RET_STS_SUCCESS
211 THEN
212 /* API-call was successfull */
213 x_error_id := 0;
214 x_error := FND_API.G_RET_STS_SUCCESS;
215 commit work;
216 ELSE
217 FOR l_counter IN 1 .. l_msg_count
218 LOOP
219 fnd_msg_pub.get
220 ( p_msg_index => l_counter
221 , p_encoded => FND_API.G_FALSE
222 , p_data => l_Msg_Data
223 , p_msg_index_out => l_msg_index_out
224 );
225 --dbms_output.put_line( 'Message: '||l_data );
226 END LOOP ;
227 x_error_id := 1;
228 x_error := l_data;
229 END IF;
230 EXCEPTION
231 WHEN OTHERS
232 THEN
233 x_error_id := -1;
234 x_error := SQLERRM;
235
236 END DELETE_REQUIREMENT_HEADER;
237
238
239 PROCEDURE CREATE_REQUIREMENT_LINE
240 (
241 p_header_id IN NUMBER
242 , p_inv_item_id IN NUMBER
243 , p_UOM IN VARCHAR2
244 , p_quantity IN NUMBER
245 , p_revision IN VARCHAR2
246 , p_ATTRIBUTE1 IN VARCHAR2
247 , p_ATTRIBUTE2 IN VARCHAR2
248 , p_ATTRIBUTE3 IN VARCHAR2
249 , p_ATTRIBUTE4 IN VARCHAR2
250 , p_ATTRIBUTE5 IN VARCHAR2
251 , p_ATTRIBUTE6 IN VARCHAR2
252 , p_ATTRIBUTE7 IN VARCHAR2
253 , p_ATTRIBUTE8 IN VARCHAR2
254 , p_ATTRIBUTE9 IN VARCHAR2
255 , p_ATTRIBUTE10 IN VARCHAR2
256 , p_ATTRIBUTE11 IN VARCHAR2
257 , p_ATTRIBUTE12 IN VARCHAR2
258 , p_ATTRIBUTE13 IN VARCHAR2
259 , p_ATTRIBUTE14 IN VARCHAR2
260 , p_ATTRIBUTE15 IN VARCHAR2
261 , p_ATTRIBUTE_CATEGORY IN VARCHAR2
262 , x_error_id OUT NOCOPY NUMBER
263 , x_error OUT NOCOPY VARCHAR2
264 , x_line_id OUT NOCOPY NUMBER
265 )
266 IS
267 l_line_id number;
268 l_req_line_rec CSP_Requirement_Lines_PVT.Requirement_Line_Rec_Type;
269 l_req_table CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
270 l_req_table_tmp CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
271 l_Return_Status VARCHAR2(1);
272 l_Msg_Count NUMBER;
273 l_Msg_Data VARCHAR2(2000);
274 l_msg_index_out NUMBER;
275 l_data VARCHAR2(255);
276
277 cursor c_next_line is select CSP_REQUIREMENT_LINES_S1.nextval from dual;
278
279 BEGIN
280
281 x_line_id := 0;
282 open c_next_line;
283 fetch c_next_line into l_line_id;
284 close c_next_line;
285
286 l_req_line_rec.REQUIREMENT_LINE_ID := l_line_id ;
287 l_req_line_rec.REQUIREMENT_HEADER_ID := p_header_id;
288 l_req_line_rec.INVENTORY_ITEM_ID := p_inv_item_id;
289 l_req_line_rec.UOM_CODE := p_UOM;
290 l_req_line_rec.REQUIRED_QUANTITY := p_quantity;
291
292 --dbms_output.put_line('1...');
293
294 IF p_revision <> '$$#@' then
295 l_req_line_rec.REVISION := p_revision;
296 -- dbms_output.put_line('2...');
297 END IF;
298
299 -- set other parameters
300 IF p_ATTRIBUTE1 IS NULL OR p_ATTRIBUTE1 <> '#%*%#'
301 THEN
302 l_req_line_rec.ATTRIBUTE1 := p_ATTRIBUTE1;
303 END IF;
304
305 IF p_ATTRIBUTE2 IS NULL OR p_ATTRIBUTE2 <> '#%*%#'
306 THEN
307 l_req_line_rec.ATTRIBUTE2 := p_ATTRIBUTE2;
308 END IF;
309
310 IF p_ATTRIBUTE3 IS NULL OR p_ATTRIBUTE3 <> '#%*%#'
311 THEN
312 l_req_line_rec.ATTRIBUTE3 := p_ATTRIBUTE3;
313 END IF;
314
315 IF p_ATTRIBUTE4 IS NULL OR p_ATTRIBUTE4 <> '#%*%#'
316 THEN
317 l_req_line_rec.ATTRIBUTE4 := p_ATTRIBUTE4;
318 END IF;
319
320 IF p_ATTRIBUTE5 IS NULL OR p_ATTRIBUTE5 <> '#%*%#'
321 THEN
322 l_req_line_rec.ATTRIBUTE5 := p_ATTRIBUTE5;
323 END IF;
324
325 IF p_ATTRIBUTE6 IS NULL OR p_ATTRIBUTE6 <> '#%*%#'
326 THEN
327 l_req_line_rec.ATTRIBUTE6 := p_ATTRIBUTE6;
328 END IF;
329
330 IF p_ATTRIBUTE7 IS NULL OR p_ATTRIBUTE7 <> '#%*%#'
331 THEN
332 l_req_line_rec.ATTRIBUTE7 := p_ATTRIBUTE7;
333 END IF;
334
335 IF p_ATTRIBUTE8 IS NULL OR p_ATTRIBUTE8 <> '#%*%#'
336 THEN
337 l_req_line_rec.ATTRIBUTE8 := p_ATTRIBUTE8;
338 END IF;
339
340 IF p_ATTRIBUTE9 IS NULL OR p_ATTRIBUTE9 <> '#%*%#'
341 THEN
342 l_req_line_rec.ATTRIBUTE9 := p_ATTRIBUTE9;
343 END IF;
344
345 IF p_ATTRIBUTE10 IS NULL OR p_ATTRIBUTE10 <> '#%*%#'
346 THEN
347 l_req_line_rec.ATTRIBUTE10 := p_ATTRIBUTE10;
348 END IF;
349
350 IF p_ATTRIBUTE11 IS NULL OR p_ATTRIBUTE11 <> '#%*%#'
351 THEN
352 l_req_line_rec.ATTRIBUTE11 := p_ATTRIBUTE11;
353 END IF;
354
355 IF p_ATTRIBUTE12 IS NULL OR p_ATTRIBUTE12 <> '#%*%#'
356 THEN
357 l_req_line_rec.ATTRIBUTE12 := p_ATTRIBUTE12;
358 END IF;
359
360 IF p_ATTRIBUTE13 IS NULL OR p_ATTRIBUTE13 <> '#%*%#'
361 THEN
362 l_req_line_rec.ATTRIBUTE13 := p_ATTRIBUTE13;
363 END IF;
364
365 IF p_ATTRIBUTE14 IS NULL OR p_ATTRIBUTE14 <> '#%*%#'
366 THEN
367 l_req_line_rec.ATTRIBUTE14 := p_ATTRIBUTE14;
368 END IF;
369
370 IF p_ATTRIBUTE15 IS NULL OR p_ATTRIBUTE15 <> '#%*%#'
371 THEN
372 l_req_line_rec.ATTRIBUTE15 := p_ATTRIBUTE15;
373 END IF;
374
375 IF p_ATTRIBUTE_CATEGORY IS NULL OR p_ATTRIBUTE_CATEGORY <> '#%*%#'
376 THEN
377 l_req_line_rec.ATTRIBUTE_CATEGORY := p_ATTRIBUTE_CATEGORY;
378 END IF;
379
380 l_req_table(1) := l_req_line_rec;
381 -- CALL API
382
383 CSP_Requirement_Lines_PVT.Create_requirement_lines(
384 P_Api_Version_Number => 1.0,
385 P_Init_Msg_List => FND_API.G_FALSE,
386 P_Commit => FND_API.G_TRUE,
387 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
388 P_Requirement_Line_Tbl => l_req_table,
389 x_Requirement_Line_tbl => l_req_table_tmp,
390 X_Return_Status => l_Return_Status,
391 X_Msg_Count => l_Msg_Count,
392 X_Msg_Data => l_Msg_Data
393 );
397 x_error_id := 0;
394 IF l_Return_Status = FND_API.G_RET_STS_SUCCESS
395 THEN
396 /* API-call was successfull */
398 x_error := FND_API.G_RET_STS_SUCCESS;
399 l_req_line_rec := l_req_table_tmp(1);
400 x_line_id := l_req_line_rec.REQUIREMENT_LINE_ID;
401 commit work;
402 ELSE
403 FOR l_counter IN 1 .. l_msg_count
404 LOOP
408 , p_data => l_data
405 fnd_msg_pub.get
406 ( p_msg_index => l_counter
407 , p_encoded => FND_API.G_FALSE
409 , p_msg_index_out => l_msg_index_out
410 );
411 -- dbms_output.put_line( 'Message: '||l_data );
412 END LOOP ;
413 x_error_id := 1;
414 x_error := l_data;
415 END IF;
416 EXCEPTION
417 WHEN OTHERS
418 THEN
419 x_error_id := -1;
420 x_error := SQLERRM;
421 END CREATE_REQUIREMENT_LINE;
422
423
424 PROCEDURE DELETE_REQUIREMENT_LINE
425 (
426 p_LINE_ID IN NUMBER
427 , x_error_id OUT NOCOPY NUMBER
428 , x_error OUT NOCOPY VARCHAR2
429 )
430 IS
431 l_req_line_rec CSP_Requirement_Lines_PVT.Requirement_Line_Rec_Type;
432 l_req_table CSP_Requirement_Lines_PVT.Requirement_Line_Tbl_Type;
433 l_Return_Status VARCHAR2(1);
434 l_Msg_Count NUMBER;
435 l_Msg_Data VARCHAR2(2000);
436 l_msg_index_out NUMBER;
437 l_data VARCHAR2(255);
438
439 BEGIN
440 /*
441 l_req_line_rec.REQUIREMENT_LINE_ID := p_LINE_ID ;
442
443 l_req_table(1) := l_req_line_rec;
444
445 -- CALL API
446
447 --dbms_output.put_line( 'Calling API');
448 CSP_Requirement_Lines_PVT.Delete_requirement_lines(
449 P_Api_Version_Number => 1.0,
450 P_Init_Msg_List => FND_API.G_FALSE,
451 P_Commit => FND_API.G_TRUE,
452 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
453 P_Requirement_Line_Tbl => l_req_table,
454 X_Return_Status => l_Return_Status,
455 X_Msg_Count => l_Msg_Count,
456 X_Msg_Data => l_data
457 );
458
459
460 IF l_Return_Status = FND_API.G_RET_STS_SUCCESS
461 THEN
462 x_error_id := 0;
463 x_error := FND_API.G_RET_STS_SUCCESS;
464 ELSE
465 FOR l_counter IN 1 .. l_msg_count
466 LOOP
467 fnd_msg_pub.get
468 ( p_msg_index => l_counter
469 , p_encoded => FND_API.G_FALSE
470 , p_data => l_data
471 , p_msg_index_out => l_msg_index_out
472 );
473 --dbms_output.put_line( 'Message: '||l_data);
474 END LOOP ;
475 x_error_id := 1;
476 x_error := l_data;
477 END IF;
478 */
479
480 delete CSP_requirement_lines where REQUIREMENT_LINE_ID = p_LINE_ID;
481 commit work;
482 x_error_id := 0;
483 x_error := '';
484
485 EXCEPTION
486 WHEN OTHERS
487 THEN
488 x_error_id := -1;
489 x_error := SQLERRM;
490
491 END DELETE_REQUIREMENT_LINE;
492
493
494
495
496
497 END csfw_req_pub;