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