1 PACKAGE BODY INV_UI_PROJECT_LOVS AS
2 /* $Header: INVUIPRB.pls 120.5.12010000.2 2008/09/30 11:12:30 kkesavar ship $ */
3 PROCEDURE print_debug (
4 p_err_msg VARCHAR2,
5 p_level NUMBER DEFAULT 4
6 )
7 IS
8 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
9 BEGIN
10 IF ( l_debug = 1 ) THEN
11 inv_mobile_helper_functions.tracelog ( p_err_msg => p_err_msg,
12 p_module => 'inv_PROJECT_LOVS',
13 p_level => p_level
14 );
15 END IF;
16 END print_debug;
17
18 /* Adding new procedure as a part of fix for bug6785303 */
19 PROCEDURE GET_RCV_PO_DELIVER_PROJECTS (
20 X_PROJECTS OUT NOCOPY /* file.sql.39 change */ t_genref,
21 p_po_header_id IN NUMBER,
22 p_po_line_id IN NUMBER,
23 p_project_number IN VARCHAR2,
24 p_item_id IN NUMBER DEFAULT NULL,
25 p_po_release_id IN NUMBER DEFAULT NULL--BUG 4201013
26 )
27 IS
28 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
29 l_no_proj_str VARCHAR2(2000);--BUG 4599723
30 BEGIN
31 l_no_proj_str := FND_MESSAGE.GET_STRING ( 'INV', 'INV_NO_PROJECT' );
32 IF (l_debug = 1) THEN
33 print_debug(' l_no_proj_str:' || l_no_proj_str);
34 print_debug(' p_project_number:' || p_project_number);
35 print_debug(' p_po_header_id:'|| p_po_header_id);
36 print_debug(' p_po_line_id:'|| p_po_line_id);
37 print_debug(' p_item_id:'|| p_item_id);
38 print_debug(' p_po_release_id:'|| p_po_release_id);
39 END IF;
40
41 IF (l_no_proj_str IS NOT NULL AND l_no_proj_str NOT LIKE p_project_number) THEN
42 OPEN x_projects FOR
43 SELECT DISTINCT p.project_id,
44 p.project_number,
45 p.project_name
46 FROM po_distributions_all pod,
47 pjm_projects_mtll_v p
48 WHERE pod.project_id = p.project_id
49 AND pod.po_header_id = p_po_header_id
50 AND pod.po_line_id = NVL ( p_po_line_id, pod.po_line_id )
51 AND Nvl(pod.po_release_id,-999) = Nvl(p_po_release_id, Nvl(pod.po_release_id, -999))
52 AND ( p_item_id IS NULL
53 OR pod.po_line_id IN (
54 SELECT pol.po_line_id
55 FROM po_lines_all pol
56 WHERE pol.item_id = p_item_id
57 AND pol.po_header_id = p_po_header_id
58 )
59 )
60 AND p.project_number LIKE ( p_project_number )
61 AND pod.project_id IS NOT NULL ;
62 ELSE
63 OPEN x_projects FOR
64 SELECT DISTINCT p.project_id,
65 p.project_number,
66 p.project_name
67 FROM po_distributions_all pod,
68 pjm_projects_mtll_v p
69 WHERE pod.project_id = p.project_id
70 AND pod.po_header_id = p_po_header_id
71 AND pod.po_line_id = NVL ( p_po_line_id, pod.po_line_id )
72 AND Nvl(pod.po_release_id,-999) = Nvl(p_po_release_id, Nvl(pod.po_release_id, -999))
73 AND ( p_item_id IS NULL
74 OR pod.po_line_id IN (
75 SELECT pol.po_line_id
76 FROM po_lines_all pol
77 WHERE pol.item_id = p_item_id
78 AND pol.po_header_id = p_po_header_id
79 )
80 )
81 AND p.project_number LIKE ( p_project_number )
82 AND pod.project_id IS NOT NULL
83 UNION ALL
84 SELECT DISTINCT -9999 project_id,
85 l_no_proj_str project_number,
86 l_no_proj_str project_name
87 FROM po_distributions_all pod
88 WHERE pod.project_id is NULL
89 AND pod.po_header_id = p_po_header_id
90 AND pod.po_line_id = NVL (p_po_line_id ,POD.PO_LINE_ID )
91 AND Nvl(pod.po_release_id,-999) = Nvl(p_po_release_id, Nvl(pod.po_release_id, -999))
92 AND (p_item_id IS NULL OR pod.po_line_id IN ( SELECT pol.po_line_id
93 FROM po_lines_all pol
94 WHERE pol.item_id = p_item_id
95 AND pol.po_header_id=p_po_header_id)
96 )
97 AND l_no_proj_str LIKE ( p_project_number ) ;
98 END IF;
99
100 END;
101
102 PROCEDURE GET_RCV_PO_PROJECTS (
103 X_PROJECTS OUT NOCOPY /* file.sql.39 change */ t_genref,
104 p_po_header_id IN NUMBER,
105 p_po_line_id IN NUMBER,
106 p_project_number IN VARCHAR2,
107 p_item_id IN NUMBER DEFAULT NULL,
108 p_po_release_id IN NUMBER DEFAULT NULL--BUG 4201013
109 )
110 IS
111 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
112 l_no_proj_str VARCHAR2(2000);--BUG 4599723
113 BEGIN
114
115 l_no_proj_str := FND_MESSAGE.GET_STRING ( 'INV', 'INV_NO_PROJECT' );
116 IF (l_debug = 1) THEN
117 print_debug(' l_no_proj_str:' || l_no_proj_str);
118 print_debug(' p_project_number:' || p_project_number);
119 print_debug(' p_po_header_id:'|| p_po_header_id);
120 print_debug(' p_po_line_id:'|| p_po_line_id);
121 print_debug(' p_item_id:'|| p_item_id);
122 print_debug(' p_po_release_id:'|| p_po_release_id);
123 END IF;
124
125 --BUG 4599723
126 --For performance reasons, remove the outer join on
127 --pjm_projects_mtll_v; instead, use a union to retrieve
128 --the no projects row. Also, if user did not enter a string
129 --like the no proj string, don't do the union
130 IF (l_no_proj_str IS NOT NULL AND l_no_proj_str NOT LIKE p_project_number) THEN
131 OPEN x_projects FOR
132 SELECT DISTINCT p.project_id,
133 p.project_number,
134 p.project_name
135 FROM po_distributions_all pod,
136 pjm_projects_mtll_v p,
137 po_line_locations_all poll
138 WHERE pod.project_id = p.project_id
139 AND pod.po_header_id = p_po_header_id
140 AND pod.po_line_id = NVL ( p_po_line_id, pod.po_line_id )
141 AND Nvl(pod.po_release_id,-999) = Nvl(p_po_release_id, Nvl(pod.po_release_id, -999))
142 AND ( p_item_id IS NULL
143 OR pod.po_line_id IN (
144 SELECT pol.po_line_id
145 FROM po_lines pol
146 WHERE pol.item_id = p_item_id
147 AND pol.po_header_id = p_po_header_id
148 )
149 )
150 AND pod.line_location_id = poll.line_location_id
151 AND pod.po_line_id = poll.po_line_id
152 AND Nvl(pod.po_release_id,-999) = Nvl(poll.po_release_id,-999)
153 AND pod.po_header_id = poll.po_header_id
154 AND Nvl(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
155 AND poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
156 AND NVL(poll.APPROVED_FLAG,'N') = 'Y'
157 AND NVL(poll.CANCEL_FLAG, 'N') = 'N'
158 AND p.project_number LIKE ( p_project_number )
159 AND pod.project_id IS NOT NULL ;
160 ELSE
161 OPEN x_projects FOR
162 SELECT DISTINCT p.project_id,
163 p.project_number,
164 p.project_name
165 FROM po_distributions_all pod,
166 pjm_projects_mtll_v p,
167 po_line_locations_all poll
168 WHERE pod.project_id = p.project_id
169 AND pod.po_header_id = p_po_header_id
170 AND pod.po_line_id = NVL ( p_po_line_id, pod.po_line_id )
171 AND Nvl(pod.po_release_id,-999) = Nvl(p_po_release_id, Nvl(pod.po_release_id, -999))
172 AND ( p_item_id IS NULL
173 OR pod.po_line_id IN (
174 SELECT pol.po_line_id
175 FROM po_lines_all pol /* bug 6785303 po_lines -> po_lines_all */
176 WHERE pol.item_id = p_item_id
177 AND pol.po_header_id = p_po_header_id
178 )
179 )
180 AND pod.line_location_id = poll.line_location_id
181 AND pod.po_line_id = poll.po_line_id
182 AND Nvl(pod.po_release_id,-999) = Nvl(poll.po_release_id,-999)
183 AND pod.po_header_id = poll.po_header_id
184 AND Nvl(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
185 AND poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
186 AND NVL(poll.APPROVED_FLAG,'N') = 'Y'
187 AND NVL(poll.CANCEL_FLAG, 'N') = 'N'
188 AND p.project_number LIKE ( p_project_number )
189 AND pod.project_id IS NOT NULL
190 UNION ALL
191 SELECT DISTINCT -9999 project_id,
192 l_no_proj_str project_number,
193 l_no_proj_str project_name
194 FROM po_distributions_all pod,
195 po_line_locations_all poll
196 WHERE pod.project_id is NULL
197 AND pod.po_header_id = p_po_header_id
198 AND pod.po_line_id = NVL (p_po_line_id ,POD.PO_LINE_ID )
199 AND Nvl(pod.po_release_id,-999) = Nvl(p_po_release_id, Nvl(pod.po_release_id, -999))
200 AND (p_item_id IS NULL OR pod.po_line_id IN ( SELECT pol.po_line_id
201 FROM po_lines pol
202 WHERE pol.item_id = p_item_id
203 AND pol.po_header_id=p_po_header_id)
204 )
205 AND pod.line_location_id = poll.line_location_id
206 AND pod.po_line_id = poll.po_line_id
207 AND Nvl(pod.po_release_id,-999) = Nvl(poll.po_release_id,-999)
208 AND pod.po_header_id = poll.po_header_id
209 AND Nvl(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
210 AND poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
211 AND NVL(poll.APPROVED_FLAG,'N') = 'Y'
212 AND NVL(poll.CANCEL_FLAG, 'N') = 'N'
213 AND l_no_proj_str LIKE ( p_project_number ) ;
214 END IF;
215 END;
216
217 PROCEDURE GET_RCV_ASN_PROJECTS (
218 X_PROJECTS OUT NOCOPY /* file.sql.39 change */ t_genref,
219 p_shipment_header_id IN NUMBER,
220 p_project_number IN VARCHAR2,
221 p_po_header_id IN NUMBER,
222 p_lpn_id IN NUMBER DEFAULT NULL
223 )
224 IS
225 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
226 l_no_proj_str VARCHAR2(2000);
227 BEGIN
228 l_no_proj_str := FND_MESSAGE.GET_STRING ( 'INV', 'INV_NO_PROJECT' );
229 IF (l_debug = 1) THEN
230 print_debug(' l_no_proj_str:' || l_no_proj_str);
231 END IF;
232
233 -- print_debug('FOR ASN ' || p_shipment_header_id || ' for lpn ' || p_lpn_id || ' Prj: ' || nvl(p_project_number,'@@@@') || ' PoHeader: ' || nvl(p_po_header_id,-9999));
234
235 --BUG 4599723
236 --For performance reasons, remove the outer join on
237 --pjm_projects_mtll_v; instead, use a union to retrieve
238 --the no projects row. Also, if user did not enter a string
239 --like the no proj string, don't do the union
240 IF (l_no_proj_str IS NOT NULL AND l_no_proj_str NOT LIKE p_project_number) THEN
241 OPEN x_projects FOR
242 SELECT DISTINCT p.project_id,
243 p.project_number,
244 p.project_name
245 FROM po_distributions_all pod,
246 pjm_projects_mtll_v p,
247 rcv_shipment_lines rsl
248 WHERE pod.project_id IS NOT NULL
249 AND pod.po_header_id = rsl.po_header_id
250 AND pod.project_id = p.project_id
251 AND pod.po_header_id = NVL ( p_po_header_id, pod.po_header_id )
252 AND p.project_number LIKE ( p_project_number )
253 AND rsl.shipment_header_id = p_shipment_header_id
254 AND ( (p_lpn_id IS NULL) OR
255 (p_lpn_id IS NOT NULL AND EXISTS
256 ( SELECT wlc.inventory_item_id
257 FROM wms_lpn_contents wlc
258 WHERE wlc.inventory_item_id = rsl.item_id
259 AND wlc.parent_lpn_id IN
260 ( SELECT lpn_id
261 FROM wms_license_plate_numbers
262 START WITH lpn_id = p_lpn_id
263 CONNECT BY parent_lpn_id = PRIOR lpn_id
264 )
265 )
266 )
267 );
268 ELSE
269 OPEN x_projects FOR
270 SELECT DISTINCT p.project_id,
271 p.project_number,
272 p.project_name
273 FROM po_distributions_all pod,
274 pjm_projects_mtll_v p,
275 rcv_shipment_lines rsl
276 WHERE pod.project_id IS NOT NULL
277 AND pod.po_header_id = rsl.po_header_id
278 AND pod.project_id = p.project_id
279 AND pod.po_header_id = NVL ( p_po_header_id, pod.po_header_id )
280 AND p.project_number LIKE ( p_project_number )
281 AND rsl.shipment_header_id = p_shipment_header_id
282 AND ( (p_lpn_id IS NULL) OR
283 (p_lpn_id IS NOT NULL AND EXISTS
284 ( SELECT wlc.inventory_item_id
285 FROM wms_lpn_contents wlc
286 WHERE wlc.inventory_item_id = rsl.item_id
287 AND wlc.parent_lpn_id IN
288 ( SELECT lpn_id
289 FROM wms_license_plate_numbers
290 START WITH lpn_id = p_lpn_id
291 CONNECT BY parent_lpn_id = PRIOR lpn_id
292 )
293 )
294 )
295 )
296 UNION ALL
297 SELECT DISTINCT -9999 project_id,
298 l_no_proj_str project_number,
299 l_no_proj_str project_name
300 FROM po_distributions_all pod,
301 rcv_shipment_lines rsl
302 WHERE pod.project_id IS NULL
303 AND pod.po_header_id = rsl.po_header_id
304 AND pod.po_header_id = NVL ( p_po_header_id, pod.po_header_id )
305 AND l_no_proj_str LIKE ( p_project_number )
306 AND rsl.shipment_header_id = p_shipment_header_id
307 AND ( (p_lpn_id IS NULL) OR
308 (p_lpn_id IS NOT NULL AND EXISTS
309 ( SELECT wlc.inventory_item_id
310 FROM wms_lpn_contents wlc
311 WHERE wlc.inventory_item_id = rsl.item_id
312 AND wlc.parent_lpn_id IN
313 ( SELECT lpn_id
314 FROM wms_license_plate_numbers
315 START WITH lpn_id = p_lpn_id
316 CONNECT BY parent_lpn_id = PRIOR lpn_id
317 )
318 )
319 )
320 );
321 END IF;
322 END;
323
324 PROCEDURE GET_RCV_REQ_PROJECTS (
325 X_PROJECTS OUT NOCOPY /* file.sql.39 change */ t_genref,
326 p_req_header_id IN NUMBER,
327 p_project_number IN VARCHAR2,
328 p_item_id IN NUMBER DEFAULT NULL,
329 p_lpn_id IN NUMBER DEFAULT NULL
330 )
331 IS
332 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
333 l_no_proj_str VARCHAR2(2000);
334 BEGIN
335 print_debug ( 'Receiving for req projects : for req ' || p_req_header_id
336 );
337 l_no_proj_str := FND_MESSAGE.GET_STRING ( 'INV', 'INV_NO_PROJECT' );
338 IF (l_debug = 1) THEN
339 print_debug(' l_no_proj_str:' || l_no_proj_str);
340 END IF;
341
342 --BUG 4599723
343 --For performance reasons, remove the outer join on
344 --pjm_projects_mtll_v; instead, use a union to retrieve
345 --the no projects row. Also, if user did not enter a string
346 --like the no proj string, don't do the union
347 IF (l_no_proj_str IS NOT NULL AND l_no_proj_str NOT LIKE p_project_number) THEN
348 OPEN x_projects FOR
349 SELECT DISTINCT p.project_id,
350 p.project_number,
351 p.project_name
352 FROM po_req_distributions_all prd,
353 pjm_projects_mtll_v p,
354 po_requisition_lines_all prl
355 WHERE prd.project_id IS NOT NULL
356 AND prd.project_id = p.project_id
357 AND prd.requisition_line_id = prl.requisition_line_id
358 AND prl.requisition_header_id = p_req_header_id
359 AND p.project_number LIKE p_project_number
360 AND ( p_item_id IS NULL
361 OR prl.item_id = p_item_id )
362 AND ( (p_lpn_id IS NULL) OR
363 (p_lpn_id IS NOT NULL AND EXISTS
364 ( SELECT wlc.inventory_item_id
365 FROM wms_lpn_contents wlc
366 WHERE wlc.inventory_item_id = prl.item_id
367 AND wlc.parent_lpn_id IN
368 ( SELECT lpn_id
369 FROM wms_license_plate_numbers
370 START WITH lpn_id = p_lpn_id
371 CONNECT BY parent_lpn_id = PRIOR lpn_id
372 )
373 )
374 )
375 );
376 ELSE
377 OPEN x_projects FOR
378 SELECT DISTINCT p.project_id,
379 p.project_number,
380 p.project_name
381 FROM po_req_distributions_all prd,
382 pjm_projects_mtll_v p,
383 po_requisition_lines_all prl
384 WHERE prd.project_id IS NOT NULL
385 AND prd.project_id = p.project_id
386 AND prd.requisition_line_id = prl.requisition_line_id
387 AND prl.requisition_header_id = p_req_header_id
388 AND p.project_number LIKE p_project_number
389 AND ( p_item_id IS NULL
390 OR prl.item_id = p_item_id )
391 AND ( (p_lpn_id IS NULL) OR
392 (p_lpn_id IS NOT NULL AND EXISTS
393 ( SELECT wlc.inventory_item_id
394 FROM wms_lpn_contents wlc
395 WHERE wlc.inventory_item_id = prl.item_id
396 AND wlc.parent_lpn_id IN
397 ( SELECT lpn_id
398 FROM wms_license_plate_numbers
399 START WITH lpn_id = p_lpn_id
400 CONNECT BY parent_lpn_id = PRIOR lpn_id
401 )
402 )
403 )
404 )
405 UNION ALL
406 SELECT DISTINCT -9999 project_id,
407 l_no_proj_str project_number,
408 l_no_proj_str project_name
409 FROM po_req_distributions_all prd,
410 po_requisition_lines_all prl
411 WHERE prd.project_id IS NULL
412 AND prd.requisition_line_id = prl.requisition_line_id
413 AND prl.requisition_header_id = p_req_header_id
414 AND l_no_proj_str LIKE ( p_project_number )
415 AND ( p_item_id IS NULL
416 OR prl.item_id = p_item_id )
417 AND ( (p_lpn_id IS NULL) OR
418 (p_lpn_id IS NOT NULL AND EXISTS
419 ( SELECT wlc.inventory_item_id
420 FROM wms_lpn_contents wlc
421 WHERE wlc.inventory_item_id = prl.item_id
422 AND wlc.parent_lpn_id IN
423 ( SELECT lpn_id
424 FROM wms_license_plate_numbers
425 START WITH lpn_id = p_lpn_id
426 CONNECT BY parent_lpn_id = PRIOR lpn_id
427 )
428 )
429 )
430 );
431 END IF;
432 END;
433
434 PROCEDURE GET_RCV_RMA_PROJECTS (
435 X_PROJECTS OUT NOCOPY /* file.sql.39 change */ t_genref,
436 p_order_header_id IN NUMBER,
437 p_project_number IN VARCHAR2,
438 p_item_id IN NUMBER DEFAULT NULL
439 )
440 IS
441 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
442 l_no_proj_str VARCHAR2(2000);
443 BEGIN
444 l_no_proj_str := FND_MESSAGE.GET_STRING ( 'INV', 'INV_NO_PROJECT' );
445 IF (l_debug = 1) THEN
446 print_debug(' l_no_proj_str:' || l_no_proj_str);
447 END IF;
448 --BUG 4599723
449 --For performance reasons, remove the outer join on
450 --pjm_projects_mtll_v; instead, use a union to retrieve
451 --the no projects row. Also, if user did not enter a string
452 --like the no proj string, don't do the union
453 IF (l_no_proj_str IS NOT NULL AND l_no_proj_str NOT LIKE p_project_number) THEN
454 OPEN x_projects FOR
455 SELECT DISTINCT p.project_id,
456 p.project_number,
457 p.project_name
458 FROM oe_order_lines_all oel,
459 pjm_projects_mtll_v p
460 WHERE oel.project_id IS NOT NULL
461 AND oel.project_id = p.project_id
462 AND oel.header_id = p_order_header_id
463 AND ( p_item_id IS NULL
464 OR ( oel.inventory_item_id = p_item_id ) )
465 AND p.project_number LIKE ( p_project_number );
466 ELSE
467 OPEN x_projects FOR
468 SELECT DISTINCT p.project_id,
469 p.project_number,
470 p.project_name
471 FROM oe_order_lines_all oel,
472 pjm_projects_mtll_v p
473 WHERE oel.project_id IS NOT NULL
474 AND oel.project_id = p.project_id
475 AND oel.header_id = p_order_header_id
476 AND ( p_item_id IS NULL
477 OR ( oel.inventory_item_id = p_item_id ) )
478 AND p.project_number LIKE ( p_project_number )
479 UNION ALL
480 SELECT DISTINCT -9999 project_id,
481 l_no_proj_str project_number,
482 l_no_proj_str project_name
483 FROM oe_order_lines_all oel
484 WHERE oel.project_id IS NULL
485 AND oel.header_id = p_order_header_id
486 AND ( p_item_id IS NULL
487 OR ( oel.inventory_item_id = p_item_id ) )
488 AND l_no_proj_str LIKE ( p_project_number );
489 END IF;
490 END;
491
492 PROCEDURE GET_PROJECTS (
493 x_projects OUT NOCOPY /* file.sql.39 change */ t_genref,
494 p_restrict_projects IN VARCHAR
495 )
496 IS
497 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
498 BEGIN
499 OPEN x_projects FOR
500 SELECT p.project_id,
501 NVL ( p.project_number, ' ' ),
502 NVL ( p.project_name, ' ' )
503 FROM pjm_projects_mtll_v p
504 WHERE p.project_number LIKE ( p_restrict_projects )
505 ORDER BY 2;
506 END GET_PROJECTS;
507
508 PROCEDURE GET_RCV_PROJECTS (
509 X_PROJECTS OUT NOCOPY /* file.sql.39 change */ t_genref,
510 document_type IN VARCHAR2,
511 p_po_header_id IN NUMBER,
512 p_po_line_id IN NUMBER,
513 p_order_header_id IN NUMBER,
514 p_req_header_id IN NUMBER,
515 p_shipment_header_id IN NUMBER,
516 p_project_number IN VARCHAR2,
517 p_item_id IN NUMBER DEFAULT NULL,
518 p_lpn_id IN NUMBER DEFAULT NULL, --ASN
519 p_po_release_id IN NUMBER DEFAULT NULL, --BUG 4201013
520 p_is_deliver IN VARCHAR2 DEFAULT 'F' --Bug 6785303
521 )
522 IS
523 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
524 BEGIN
525 /* Bug 6785303 if p_is_deliver then call GET_RCV_PO_DELIVER_PROJECTS */
526 IF DOCUMENT_TYPE = 'PO' THEN
527 IF p_is_deliver = 'T' THEN
528 print_debug ( 'PO DELIVER type' );
529 GET_RCV_PO_DELIVER_PROJECTS ( X_PROJECTS,
530 p_po_header_id,
531 p_po_line_id,
532 p_project_number,
533 p_item_id,
534 p_po_release_id
535 );
536 ELSE
537 print_debug ( 'PO type' );
538 GET_RCV_PO_PROJECTS ( X_PROJECTS,
539 p_po_header_id,
540 p_po_line_id,
541 p_project_number,
542 p_item_id,
543 p_po_release_id
544 );
545 END IF;
546 ELSIF DOCUMENT_TYPE = 'REQ' THEN
547 print_debug ( 'req type' );
548 GET_RCV_REQ_PROJECTS ( X_PROJECTS,
549 p_req_header_id,
550 p_project_number,
551 p_item_id,
552 p_lpn_id
553 );
554 /*Added as part of bug - 5928199*/
555 ELSIF DOCUMENT_TYPE = 'INTSHIP' THEN
556 print_debug ( 'INTSHIP type' );
557 GET_RCV_REQ_PROJECTS ( X_PROJECTS,
558 p_req_header_id,
559 p_project_number,
560 p_item_id,
561 p_lpn_id
562 );
563 /*End of modifications for bug - 5928199*/
564 ELSIF DOCUMENT_TYPE = 'RMA' THEN
565 GET_RCV_RMA_PROJECTS ( X_PROJECTS,
566 p_order_header_id,
567 p_project_number,
568 p_item_id
569 );
570 ELSIF DOCUMENT_TYPE = 'ASN' THEN
571 GET_RCV_ASN_PROJECTS ( X_PROJECTS,
572 p_shipment_header_id,
573 p_project_number,
574 p_po_header_id,
575 p_lpn_id
576 );
577 END IF;
578 END;
579
580 PROCEDURE GET_CC_PROJECTS (
581 x_projects OUT NOCOPY /* file.sql.39 change */ t_genref,
582 p_organization_id IN NUMBER,
583 p_cycle_count_id IN NUMBER,
584 p_unscheduled_flag IN NUMBER,
585 p_project_number IN VARCHAR2
586 )
587 IS
588 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
589 BEGIN
590
591 -- bug 4662395 set the profile mfg_organization_id so
592 -- the call to PJM_PROJECTS_MTLL_V will return data.
593
594 FND_PROFILE.put('MFG_ORGANIZATION_ID',p_organization_id);
595
596 IF p_unscheduled_flag = 2 THEN
597 IF ( l_debug = 1 ) THEN
598 inv_log_util.TRACE ( 'Unsceduled flag = 2 '
599 || p_cycle_count_id
600 || ' : '
601 || p_organization_id,
602 4
603 );
604 END IF;
605
606 OPEN x_projects FOR
607 SELECT DISTINCT p.project_id,
608 NVL ( p.project_number, ' ' ),
609 NVL ( p.project_name, ' ' )
610 FROM pjm_projects_mtll_v p,
611 mtl_cycle_count_entries mcce,
612 mtl_item_locations mil
613 WHERE mil.segment19 = p.project_id
614 AND mcce.locator_id = mil.inventory_location_id
615 AND mcce.organization_id = mil.organization_id
616 AND mcce.cycle_count_header_id = p_cycle_count_id
617 AND p.project_number LIKE ( p_project_number )
618 ORDER BY 2;
619 ELSIF p_unscheduled_flag = 1 THEN
620 IF ( l_debug = 1 ) THEN
621 inv_log_util.TRACE ( 'Unsceduled flag = 1 '
622 || p_cycle_count_id
623 || ' : '
624 || p_organization_id,
625 4
626 );
627 END IF;
628
629 OPEN x_projects FOR
630 SELECT p.project_id,
631 NVL ( p.project_number, ' ' ),
632 NVL ( p.project_name, ' ' )
633 FROM pjm_projects_mtll_v p
634 WHERE p.project_number LIKE ( p_project_number )
635 ORDER BY 2;
636 END IF;
637 END;
638
639 PROCEDURE GET_PHY_PROJECTS (
640 x_projects OUT NOCOPY /* file.sql.39 change */ t_genref,
641 p_organization_id IN NUMBER,
642 p_dynamic_entry_flag IN NUMBER,
643 p_physical_inventory_id IN NUMBER,
644 p_project_number IN VARCHAR2
645 )
646 IS
647 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
648 BEGIN
649
650 -- bug 4662395 set the profile mfg_organization_id so
651 -- the call to PJM_PROJECTS_MTLL_V will return data.
652
653 FND_PROFILE.put('MFG_ORGANIZATION_ID',p_organization_id);
654
655 IF ( p_dynamic_entry_flag = 2 ) THEN
656 OPEN x_projects FOR
657 SELECT DISTINCT p.project_id,
658 NVL ( p.project_number, ' ' ),
659 NVL ( p.project_name, ' ' )
660 FROM pjm_projects_mtll_v p,
661 mtl_physical_inventory_tags mpi,
662 mtl_item_locations mil
663 WHERE mil.project_id = p.project_id
664 AND mil.inventory_location_id = mpi.locator_id
665 AND mil.organization_id = p_organization_id
666 AND mpi.physical_inventory_id =
667 p_physical_inventory_id
668 AND p.project_number LIKE ( p_project_number )
669 ORDER BY 2;
670 ELSE -- dynamic tags allowed
671 OPEN x_projects FOR
672 SELECT p.project_id,
673 NVL ( p.project_number, ' ' ),
674 NVL ( p.project_name, ' ' )
675 FROM pjm_projects_mtll_v p
676 WHERE p.project_number LIKE ( p_project_number )
677 ORDER BY 2;
678 END IF;
679 END;
680
681 PROCEDURE GET_MO_PROJECTS (
682 x_projects OUT NOCOPY /* file.sql.39 change */ t_genref,
683 p_restrict_projects IN VARCHAR2,
684 p_organization_id IN NUMBER,
685 p_mo_header_id IN NUMBER
686 )
687 IS
688 l_debug NUMBER := NVL ( FND_PROFILE.VALUE ( 'INV_DEBUG_TRACE' ), 0 );
689 BEGIN
690
691 -- bug 4662395 set the profile mfg_organization_id so
692 -- the call to PJM_PROJECTS_MTLL_V will return data.
693
694 FND_PROFILE.put('MFG_ORGANIZATION_ID',p_organization_id);
695
696 OPEN x_projects FOR
697 SELECT p.project_id,
698 NVL ( p.project_number, ' ' ),
699 NVL ( p.project_name, ' ' )
700 FROM pjm_projects_mtll_v p
701 WHERE p.project_number LIKE ( p_restrict_projects )
702 AND p.project_id IN (
703 SELECT mtrl.project_id
704 FROM mtl_txn_request_lines mtrl
705 WHERE mtrl.organization_id = p_organization_id
706 AND mtrl.header_id = p_mo_header_id )
707 ORDER BY 2;
708 END GET_MO_PROJECTS;
709 END INV_UI_PROJECT_LOVS;
[Home] [Help]