[Home] [Help]
PACKAGE BODY: APPS.INV_UI_TASK_LOVS
Source
1 PACKAGE BODY INV_UI_TASK_LOVS AS
2 /* $Header: INVUITAB.pls 120.3 2008/02/15 10:41:28 mporecha 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_UI_TASK_LOVS',
13 p_level => p_level
14 );
15 END IF;
16 END print_debug;
17
18 PROCEDURE GET_TASKS (
19 x_tasks OUT NOCOPY t_genref,
20 p_restrict_tasks IN VARCHAR,
21 p_project_id IN VARCHAR
22 )
23 IS
24 BEGIN
25 OPEN x_tasks FOR
26 SELECT t.task_id,
27 NVL ( t.task_number, ' ' ),
28 NVL ( t.task_name, ' ' )
29 FROM pjm_tasks_mtll_v t
30 WHERE t.task_number LIKE ( p_restrict_tasks )
31 AND t.project_id = p_project_id
32 ORDER BY 2;
33 END GET_TASKS;
34
35 PROCEDURE GET_MO_TASKS (
36 x_tasks OUT NOCOPY t_genref,
37 p_restrict_tasks IN VARCHAR2,
38 p_project_id IN NUMBER,
39 p_organization_id IN NUMBER,
40 p_mo_header_id IN NUMBER
41 )
42 IS
43 BEGIN
44 OPEN x_tasks FOR
45 SELECT t.task_id,
46 NVL ( t.task_number, ' ' ),
47 NVL ( t.task_name, ' ' )
48 FROM pjm_tasks_mtll_v t
49 WHERE t.task_number LIKE ( p_restrict_tasks )
50 AND t.project_id = p_project_id
51 AND t.task_id IN (
52 SELECT mtrl.task_id
53 FROM mtl_txn_request_lines mtrl
54 WHERE mtrl.organization_id = p_organization_id
55 AND mtrl.header_id = p_mo_header_id )
56 ORDER BY 2;
57 END GET_MO_TASKS;
58
59 PROCEDURE GET_CC_TASKS (
60 x_tasks OUT NOCOPY t_genref,
61 p_restrict_tasks IN VARCHAR2,
62 p_project_id IN NUMBER,
63 p_organization_id IN NUMBER,
64 p_cycle_count_id IN NUMBER,
65 p_unscheduled_flag IN NUMBER
66 )
67 IS
68 BEGIN
69 IF p_unscheduled_flag = 2 THEN
70 OPEN x_tasks FOR
71 SELECT DISTINCT t.task_id,
72 NVL ( t.task_number, ' ' ),
73 NVL ( t.task_name, ' ' )
74 FROM pjm_tasks_mtll_v t,
75 mtl_cycle_count_entries mcce,
76 mtl_item_locations mil
77 WHERE t.project_id = p_project_id
78 AND mcce.organization_id = mil.organization_id
79 AND mcce.subinventory = mil.subinventory_code
80 AND mcce.locator_id = mil.inventory_location_id
81 AND mcce.organization_id = p_organization_id
82 AND mcce.cycle_count_header_id = p_cycle_count_id
83 AND t.task_id = mil.task_id
84 AND t.task_number LIKE ( p_restrict_tasks )
85 AND t.project_id = mil.project_id
86 ORDER BY 2;
87 ELSE
88 OPEN x_tasks FOR
89 SELECT t.task_id,
90 NVL ( t.task_number, ' ' ),
91 NVL ( t.task_name, ' ' )
92 FROM pjm_tasks_mtll_v t
93 WHERE t.task_number LIKE ( p_restrict_tasks )
94 AND t.project_id = p_project_id
95 ORDER BY 2;
96 END IF;
97 END;
98
99 PROCEDURE GET_PHY_TASKS (
100 x_tasks OUT NOCOPY t_genref,
101 p_restrict_tasks IN VARCHAR2,
102 p_project_id IN NUMBER,
103 p_organization_id IN NUMBER,
104 p_dynamic_entry_flag IN NUMBER,
105 p_physical_inventory_id IN NUMBER
106 )
107 IS
108 BEGIN
109 IF ( p_dynamic_entry_flag = 2 ) THEN
110 OPEN x_tasks FOR
111 SELECT t.task_id,
112 NVL ( t.task_number, ' ' ),
113 NVL ( t.task_name, ' ' )
114 FROM pjm_tasks_mtll_v t,
115 mtl_physical_inventory_tags mpi,
116 mtl_item_locations mil
117 WHERE t.project_id = p_project_id
118 AND mil.inventory_location_id = mpi.locator_id
119 AND mil.organization_id = p_organization_id
120 AND mpi.physical_inventory_id = p_physical_inventory_id
121 AND t.task_number LIKE ( p_restrict_tasks )
122 AND mil.task_id = t.task_id
123 AND t.project_id = mil.project_id
124 ORDER BY 2;
125 ELSE
126 OPEN x_tasks FOR
127 SELECT t.task_id,
128 NVL ( t.task_number, ' ' ),
129 NVL ( t.task_name, ' ' )
130 FROM pjm_tasks_mtll_v t
131 WHERE t.task_number LIKE ( p_restrict_tasks )
132 AND t.project_id = p_project_id
133 ORDER BY 2;
134 END IF;
135 END;
136
137 PROCEDURE get_rcv_po_tasks (
138 X_TASKS OUT NOCOPY t_genref,
139 p_po_header_id IN NUMBER,
140 p_po_line_id IN NUMBER,
141 p_project_id IN NUMBER,
142 p_task_number IN VARCHAR2,
143 p_item_id IN NUMBER,
144 p_po_release_id IN NUMBER DEFAULT NULL
145 )
146 IS
147 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
148 BEGIN
149 IF (l_debug = 1) THEN
150 print_debug(' p_task_number:' || p_task_number);
151 print_debug(' p_po_header_id:'|| p_po_header_id);
152 print_debug(' p_po_line_id:'|| p_po_line_id);
153 print_debug(' p_item_id:'|| p_item_id);
154 print_debug(' p_po_release_id:'|| p_po_release_id);
155 END IF;
156
157 OPEN x_tasks FOR
158 SELECT DISTINCT t.task_id,
159 NVL ( t.task_number, ' ' ),
160 NVL ( t.task_name, ' ' )
161 FROM pjm_tasks_mtll_v t,
162 po_distributions_all pod,
163 po_line_locations_all poll
164 WHERE pod.task_id = t.task_id
165 AND pod.po_header_id = p_po_header_id
166 AND pod.po_line_id = NVL ( p_po_line_id, pod.po_line_id )
167 AND pod.project_id = p_project_id
168 AND t.project_id = p_project_id
169 AND ( p_item_id IS NULL
170 OR ( pod.po_line_id IN ( SELECT pol.po_line_id
171 FROM po_lines_all pol
172 WHERE pol.item_id = p_item_id ) )
173 )
174 AND ( p_po_release_id IS NULL
175 OR ( pod.po_release_id = p_po_release_id)
176 )
177 AND pod.line_location_id = poll.line_location_id
178 AND pod.po_line_id = poll.po_line_id
179 AND Nvl(pod.po_release_id,-999) = Nvl(poll.po_release_id,-999)
180 AND pod.po_header_id = poll.po_header_id
181 AND Nvl(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
182 AND poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
183 AND NVL(poll.APPROVED_FLAG,'N') = 'Y'
184 AND NVL(poll.CANCEL_FLAG, 'N') = 'N'
185 AND t.task_number LIKE ( p_task_number );
186 END;
187
188 /* Adding a new procedure as a part of fix for bug 6785303
189 * For deliver transactions tasks are not limited to open shipments
190 */
191
192 PROCEDURE get_rcv_po_deliver_tasks (
193 X_TASKS OUT NOCOPY t_genref,
194 p_po_header_id IN NUMBER,
195 p_po_line_id IN NUMBER,
196 p_project_id IN NUMBER,
197 p_task_number IN VARCHAR2,
198 p_item_id IN NUMBER,
199 p_po_release_id IN NUMBER DEFAULT NULL
200 )
201 IS
202 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
203 BEGIN
204 IF (l_debug = 1) THEN
205 print_debug(' p_task_number:' || p_task_number);
206 print_debug(' p_po_header_id:'|| p_po_header_id);
207 print_debug(' p_po_line_id:'|| p_po_line_id);
208 print_debug(' p_item_id:'|| p_item_id);
209 print_debug(' p_po_release_id:'|| p_po_release_id);
210 END IF;
211
212 OPEN x_tasks FOR
213 SELECT DISTINCT t.task_id,
214 NVL ( t.task_number, ' ' ),
215 NVL ( t.task_name, ' ' )
216 FROM pjm_tasks_mtll_v t,
217 po_distributions_all pod
218 WHERE pod.task_id = t.task_id
219 AND pod.po_header_id = p_po_header_id
220 AND pod.po_line_id = NVL ( p_po_line_id, pod.po_line_id )
221 AND pod.project_id = p_project_id
222 AND t.project_id = p_project_id
223 AND ( p_item_id IS NULL
224 OR ( pod.po_line_id IN ( SELECT pol.po_line_id
225 FROM po_lines_all pol
226 WHERE pol.item_id = p_item_id ) )
227 )
228 AND ( p_po_release_id IS NULL
229 OR ( pod.po_release_id = p_po_release_id)
230 )
231 AND t.task_number LIKE ( p_task_number );
232 END;
233
234 PROCEDURE get_rcv_req_tasks (
235 X_TASKS OUT NOCOPY t_genref,
236 p_req_header_id IN NUMBER,
237 p_project_id IN NUMBER,
238 p_task_number IN VARCHAR2,
239 p_item_id IN NUMBER,
240 p_lpn_id IN NUMBER
241 )
242 IS
243 BEGIN
244 OPEN x_tasks FOR
245 SELECT DISTINCT t.task_id,
246 NVL ( t.task_number, ' ' ),
247 NVL ( t.task_name, ' ' )
248 FROM pjm_tasks_mtll_v t,
249 po_requisition_lines_all prl,
250 po_req_distributions_all prd
251 WHERE prd.requisition_line_id = prl.requisition_line_id
252 AND prl.requisition_header_id = p_req_header_id
253 AND prd.project_id = p_project_id
254 AND t.project_id = p_project_id
255 AND prd.task_id = t.task_id
256 AND ( p_item_id IS NULL
257 OR ( prl.item_id = p_item_id ) )
258 AND (p_lpn_id IS NULL
259 OR (p_lpn_id IS NOT NULL
260 AND EXISTS(
261 SELECT wlc.parent_lpn_id
262 FROM wms_lpn_contents wlc
263 WHERE wlc.inventory_item_id = prl.item_id
264 AND wlc.parent_lpn_id IN(
265 SELECT lpn_id
266 FROM wms_license_plate_numbers
267 START WITH lpn_id = p_lpn_id
268 CONNECT BY parent_lpn_id = PRIOR lpn_id
269 )
270 )
271 )
272 )
273 AND t.task_number LIKE ( p_task_number );
274 END;
275
276 PROCEDURE get_rcv_rma_tasks (
277 X_TASKS OUT NOCOPY t_genref,
278 p_oe_header_id IN NUMBER,
279 p_project_id IN NUMBER,
280 p_task_number IN VARCHAR2,
281 p_item_id IN NUMBER
282 )
283 IS
284 BEGIN
285 OPEN x_tasks FOR
286 SELECT DISTINCT t.task_id,
287 NVL ( t.task_number, ' ' ),
288 NVL ( t.task_name, ' ' )
289 FROM pjm_tasks_mtll_v t,
290 oe_order_lines_all oel
291 WHERE oel.project_id = t.project_id
292 AND t.project_id = p_project_id
293 AND oel.task_id = t.task_id
294 AND oel.header_id = p_oe_header_id
295 AND ( p_item_id IS NULL
296 OR ( oel.inventory_item_id = p_item_id ) )
297 AND t.task_number LIKE ( p_task_number );
298 END;
299
300 PROCEDURE get_rcv_asn_tasks (
301 X_TASKS OUT NOCOPY t_genref,
302 p_po_header_id IN NUMBER,
303 p_shipment_id IN NUMBER,
304 p_project_id IN NUMBER,
305 p_task_number IN VARCHAR2,
306 p_lpn_id IN NUMBER DEFAULT NULL --ASN
307 )
308 IS
309 BEGIN
310 OPEN x_tasks FOR
311 SELECT DISTINCT t.task_id,
312 NVL ( t.task_number, ' ' ),
313 NVL ( t.task_name, ' ' )
314 FROM pjm_tasks_mtll_v t,
315 po_distributions_all pod,
316 rcv_shipment_lines rsl
317 WHERE rsl.shipment_header_id = p_shipment_id
318 AND rsl.po_header_id =
319 NVL ( p_po_header_id, rsl.po_header_id )
320 AND rsl.po_header_id = pod.po_header_id
321 AND rsl.po_line_id = pod.po_line_id(+)
322 AND rsl.po_line_location_id = pod.line_location_id(+)
323 AND t.project_id = p_project_id
324 AND pod.project_id = p_project_id
325 AND t.project_id = pod.project_id
326 AND t.task_id = pod.task_id
327 AND task_number LIKE ( p_task_number )
328 AND (p_lpn_id IS NULL
329 OR(p_lpn_id IS NOT NULL
330 AND EXISTS(
331 SELECT wlc.parent_lpn_id
332 FROM wms_lpn_contents wlc
333 WHERE wlc.inventory_item_id = rsl.item_id
334 AND wlc.parent_lpn_id IN(
335 SELECT lpn_id
336 FROM wms_license_plate_numbers
337 START WITH lpn_id = p_lpn_id
338 CONNECT BY parent_lpn_id = PRIOR lpn_id
339 )
340 )
341 )
342 )
343
344 /* AND rsl.item_id IN (
345 SELECT wlc.inventory_item_id
346 FROM wms_lpn_contents wlc,
347 po_lines_all pol
348 WHERE pol.item_id = wlc.inventory_item_id
349 AND parent_lpn_id =
350 NVL ( p_lpn_id, parent_lpn_id )
351 AND pod.po_line_id = pol.po_line_id )*/; --bug 2876336
352 END;
353
354 PROCEDURE GET_RCV_TASKS (
355 X_TASKS OUT NOCOPY t_genref,
356 p_document_type IN VARCHAR2,
357 p_po_header_id IN NUMBER,
358 p_po_line_id IN NUMBER,
359 p_oe_header_id IN NUMBER,
360 p_req_header_id IN NUMBER,
361 p_shipment_id IN NUMBER,
362 p_project_id IN NUMBER,
363 p_task_number IN VARCHAR2,
364 p_item_id IN NUMBER DEFAULT NULL,
365 p_lpn_id IN NUMBER DEFAULT NULL,
366 p_po_release_id IN NUMBER DEFAULT NULL,
367 p_is_deliver IN VARCHAR2 DEFAULT 'F' --bug 6785303
368 )
369 IS
370 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
371 BEGIN
372 IF p_document_type = 'PO' THEN
373 /* Adding IF-ELSE-END IF as part of fix for bug 6785303 */
374 IF p_is_deliver = 'T' THEN
375 print_debug('PO deliver type');
376 get_rcv_po_deliver_tasks ( X_TASKS,
377 p_po_header_id,
378 p_po_line_id,
379 p_project_id,
380 p_task_number,
381 p_item_id,
382 p_po_release_id
383 );
384 ELSE
385 print_debug('PO type');
386 get_rcv_po_tasks ( X_TASKS,
387 p_po_header_id,
388 p_po_line_id,
389 p_project_id,
390 p_task_number,
391 p_item_id,
392 p_po_release_id
393 );
394 END IF;
395 ELSIF p_document_type = 'REQ' THEN
396 get_rcv_req_tasks ( X_TASKS,
397 p_req_header_id,
398 p_project_id,
399 p_task_number,
400 p_item_id,
401 p_lpn_id
402 );
403 ELSIF p_document_type = 'RMA' THEN
404 get_rcv_rma_tasks ( X_TASKS,
405 p_oe_header_id,
406 p_project_id,
407 p_task_number,
408 p_item_id
409 );
410 ELSIF p_document_type = 'ASN' THEN
411 get_rcv_asn_tasks ( X_TASKS,
412 p_po_header_id,
413 p_shipment_id,
414 p_project_id,
415 p_task_number,
416 p_lpn_id
417 );
418 END IF;
419 END GET_RCV_TASKS;
420 END INV_UI_TASK_LOVS;