1 PACKAGE BODY inv_mo_lovs AS
2 /* $Header: INVMOLB.pls 120.5 2007/01/09 10:15:08 hjogleka noship $ */
3
4 --
5 -- Name: GET_MO_LOV_ALL
6 --
7 -- Input parameters:
8 -- p_Organization_Id which restricts LOV SQL to current org
9 -- p_mo_req_number which restricts LOV SQL to the user input text
10 -- p_molov_type Type of LOV being requested
11 --
12 -- Output parameters:
13 -- x_mo_num_lov returns LOV rows as reference cursor
14 --
15 -- Functions: This API returns MO number for a given org
16 -- which are in status APPROVED, PREAPPROVED, PART_APPROVED
17
18 /******************************************************************
19 The SELECT statement has been modified to get the line_id also
20 as part of tbe bug - 2169451
21
22 The select will return the LineNumber and LineId if there is only
23 one line for the MoveOrder. Otherwise it returns NULL.
24 ******************************************************************/
25 PROCEDURE get_mo_lov_all(x_mo_num_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_mo_req_number IN VARCHAR2) IS
26 BEGIN
27 OPEN x_mo_num_lov FOR
28 SELECT MAX(h.request_number)
29 , MAX(h.description)
30 , h.header_id
31 , MAX(h.move_order_type)
32 , DECODE(COUNT(l.line_number), 1, MAX(l.line_number), NULL)
33 , DECODE(COUNT(l.line_id), 1, MAX(l.line_id), NULL)
34 FROM mtl_txn_request_headers h, mtl_txn_request_lines l
35 WHERE h.organization_id = p_organization_id
36 AND h.request_number LIKE(p_mo_req_number)
37 AND h.header_status IN(3, 7, 8)
38 AND l.organization_id = h.organization_id
39 AND l.line_status IN(3, 7, 8)
40 AND NVL(l.quantity_delivered, 0) < l.quantity
41 AND l.header_id = h.header_id
42 AND EXISTS(
43 SELECT NULL
44 FROM mtl_system_items msi
45 WHERE msi.inventory_item_id = l.inventory_item_id
46 AND msi.organization_id = l.organization_id
47 AND msi.mtl_transactions_enabled_flag = 'Y'
48 AND msi.inventory_item_flag = 'Y'
49 AND msi.bom_item_type = 4)
50 GROUP BY h.header_id;
51 END get_mo_lov_all;
52
53 --
54 -- Name: GET_MO_LOV
55 --
56 -- Input parameters:
57 -- p_Organization_Id which restricts LOV SQL to current org
58 -- p_mo_req_number which restricts LOV SQL to the user input text
59 -- p_molov_type Type of LOV being requested
60 --
61 -- Output parameters:
62 -- x_mo_num_lov returns LOV rows as reference cursor
63 --
64 -- Functions: This API returns MoveOrders for a given org
65 -- which are in status APPROVED, PREAPPROVED, PART_APPROVED
66 -- for the specified MO_Type and Trx_Type
67 --
68
69 /*****************************************************************
70 The SELECT statement has been modified to get the line_id also
71 as part of tbe bug - 2169451
72
73 The select will return the LineNumber and LineId if there is only
74 one line for the MoveOrder. Otherwise it returns NULL.
75 *****************************************************************/
76 PROCEDURE get_mo_lov(
77 x_mo_num_lov OUT NOCOPY t_genref
78 , p_organization_id IN NUMBER
79 , p_mo_type IN NUMBER
80 , p_trx_type IN NUMBER
81 , p_mo_req_number IN VARCHAR2
82 ) IS
83 /*Bug Number:3066941*/
84 BEGIN
85 IF(p_trx_type =63 )THEN
86 OPEN x_mo_num_lov FOR
87 SELECT MAX(h.request_number)
88 , MAX(h.description)
89 , h.header_id
90 , MAX(h.move_order_type)
91 , DECODE(COUNT(l.line_number), 1, MAX(l.line_number), NULL)
92 , DECODE(COUNT(l.line_id), 1, MAX(l.line_id), NULL)
93 FROM mtl_txn_request_headers h, mtl_txn_request_lines l, mtl_transaction_types t
94 WHERE l.header_id = h.header_id
95 AND l.transaction_type_id = t.transaction_type_id
96 AND h.organization_id = p_organization_id
97 AND h.header_status IN(3, 7, 8)
98 AND h.move_order_type = p_mo_type
99 AND t.transaction_action_id=1
100 AND l.transaction_source_type_id=4
101 AND NVL(l.quantity_delivered, 0) < l.quantity
102 AND h.request_number LIKE(p_mo_req_number)
103 GROUP BY h.header_id;
104 ELSIF(p_trx_type =64 )THEN
105 OPEN x_mo_num_lov FOR
106 SELECT MAX(h.request_number)
107 , MAX(h.description)
108 , h.header_id
109 , MAX(h.move_order_type)
110 , DECODE(COUNT(l.line_number), 1, MAX(l.line_number), NULL)
111 , DECODE(COUNT(l.line_id), 1, MAX(l.line_id), NULL)
112 FROM mtl_txn_request_headers h, mtl_txn_request_lines l, mtl_transaction_types t
113 WHERE l.header_id = h.header_id
114 AND l.transaction_type_id = t.transaction_type_id
115 AND h.organization_id = p_organization_id
116 AND h.header_status IN(3, 7, 8)
117 AND h.move_order_type = p_mo_type
118 AND t.transaction_action_id=2
119 AND l.transaction_source_type_id=4
120 AND NVL(l.quantity_delivered, 0) < l.quantity
121 AND h.request_number LIKE(p_mo_req_number)
122 GROUP BY h.header_id;
123 ELSE
124 OPEN x_mo_num_lov FOR
125 SELECT MAX(h.request_number)
126 , MAX(h.description)
127 , h.header_id
128 , MAX(h.move_order_type)
129 , DECODE(COUNT(l.line_number), 1, MAX(l.line_number), NULL)
130 , DECODE(COUNT(l.line_id), 1, MAX(l.line_id), NULL)
131 FROM mtl_txn_request_headers h, mtl_txn_request_lines l
132 WHERE l.header_id = h.header_id
133 AND h.organization_id = p_organization_id
134 AND h.header_status IN(3, 7, 8)
135 AND h.move_order_type = p_mo_type
136 AND l.transaction_type_id = NVL(p_trx_type, l.transaction_type_id)
137 AND NVL(l.quantity_delivered, 0) < l.quantity
138 AND h.request_number LIKE(p_mo_req_number)
139 GROUP BY h.header_id;
140 END IF;
141 END get_mo_lov;
142
143 /*****************************************************************
144 The SELECT statement has been modified to get the line_id also
145 as part of tbe bug - 2169451
146
147 The select will return the LineNumber and LineId if there is only
148 one line for the MoveOrder. Otherwise it returns NULL.
149 *****************************************************************/
150 --Bug #3796571, filtering MO LOV on Sales Order Number
151 PROCEDURE get_pickwavemo_lov(x_pwmo_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_mo_req_number IN VARCHAR2, p_so_number IN VARCHAR2 := NULL) IS
152 BEGIN
153 --bug #3796571, forking the code for better performance when Sales Order number is not passed.
154 IF (p_so_number IS NULL) THEN
155 OPEN x_pwmo_lov FOR
156 SELECT MAX(h.request_number)
157 , MAX(h.description)
158 , h.header_id
159 , MAX(h.move_order_type)
160 , DECODE(COUNT(l.line_number), 1, MAX(l.line_number), NULL)
161 , DECODE(COUNT(l.line_id), 1, MAX(l.line_id), NULL)
162 FROM mtl_txn_request_headers h, mtl_txn_request_lines l
163 WHERE h.organization_id = p_organization_id
164 AND h.request_number LIKE (p_mo_req_number)
165 AND h.header_status IN(3, 7, 8)
166 AND move_order_type = 3
167 AND l.organization_id = h.organization_id
168 AND l.line_status IN(3, 7, 8)
169 AND NVL(l.quantity_delivered, 0) < l.quantity
170 AND l.header_id = h.header_id
171 AND EXISTS(
172 SELECT NULL
173 FROM mtl_system_items msi
174 WHERE msi.inventory_item_id = l.inventory_item_id
175 AND msi.organization_id = l.organization_id
176 AND msi.mtl_transactions_enabled_flag = 'Y'
177 AND msi.inventory_item_flag = 'Y'
178 AND msi.bom_item_type = 4)
179 GROUP BY h.header_id;
180 ELSE
181 OPEN x_pwmo_lov FOR
182 SELECT MAX(h.request_number)
183 , MAX(h.description)
184 , h.header_id
185 , MAX(h.move_order_type)
186 , DECODE(COUNT(l.line_number), 1, MAX(l.line_number), NULL)
187 , DECODE(COUNT(l.line_id), 1, MAX(l.line_id), NULL)
188 FROM mtl_txn_request_headers h, mtl_txn_request_lines l
189 , mtl_sales_orders mso
190 WHERE mso.sales_order_id = l.txn_source_id
191 AND (p_so_number IS NULL OR mso.segment1 = p_so_number)
192 AND h.organization_id = p_organization_id
193 AND h.request_number LIKE (p_mo_req_number)
194 AND h.header_status IN(3, 7, 8)
195 AND move_order_type = 3
196 AND l.organization_id = h.organization_id
197 AND l.line_status IN(3, 7, 8)
198 AND NVL(l.quantity_delivered, 0) < l.quantity
199 AND l.header_id = h.header_id
200 AND EXISTS(
201 SELECT NULL
202 FROM mtl_system_items msi
203 WHERE msi.inventory_item_id = l.inventory_item_id
204 AND msi.organization_id = l.organization_id
205 AND msi.mtl_transactions_enabled_flag = 'Y'
206 AND msi.inventory_item_flag = 'Y'
207 AND msi.bom_item_type = 4)
208 GROUP BY h.header_id;
209 END IF; --IF (p_so_number IS NULL)
210 END get_pickwavemo_lov;
211
212 /*****************************************************************
213 The SELECT statement has been modified to get the line_id also
214 as part of tbe bug - 2169451
215
216 The select will return the LineNumber and LineId if there is only
217 one line for the MoveOrder. Otherwise it returns NULL.
218 *****************************************************************/
219 PROCEDURE get_wipmo_lov(x_pwmo_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_mo_req_number IN VARCHAR2) IS
220 BEGIN
221 OPEN x_pwmo_lov FOR
222 SELECT MAX(h.request_number)
223 , MAX(h.description)
224 , h.header_id
225 , MAX(h.move_order_type)
226 , DECODE(COUNT(l.line_number), 1, MAX(l.line_number), NULL)
227 , DECODE(COUNT(l.line_id), 1, MAX(l.line_id), NULL)
228 FROM mtl_txn_request_headers h, mtl_txn_request_lines l
229 WHERE h.organization_id = p_organization_id
230 AND h.request_number LIKE(p_mo_req_number)
231 AND h.header_status IN(3, 7, 8)
232 AND move_order_type = 5
233 AND l.organization_id = h.organization_id
234 AND l.line_status IN(3, 7, 8)
235 AND NVL(l.quantity_delivered, 0) < l.quantity
236 AND l.header_id = h.header_id
237 AND EXISTS(
238 SELECT NULL
239 FROM mtl_system_items msi
240 WHERE msi.inventory_item_id = l.inventory_item_id
241 AND msi.organization_id = l.organization_id
242 AND msi.mtl_transactions_enabled_flag = 'Y'
243 AND msi.inventory_item_flag = 'Y'
244 AND msi.bom_item_type = 4)
245 GROUP BY h.header_id;
246 END get_wipmo_lov;
247
248 -- Name: GET_MOLINE_LOV
249 --
250 -- Input parameters:
251 -- p_Organization_Id which restricts LOV SQL to current org
252 -- p_mo_number which restricts LOV SQL to the user input text
253 -- p_line_number which restricts LOV SQL to specifid Line
254 --
255 -- Output parameters:
256 -- x_mo_line_lov returns LOV rows as reference cursor
257 --
258 -- Functions: This API returns MO Line Number for a given org and
259 -- MoveOrder headerId which are in status APPROVED, PREAPPROVED
260 -- and PART_APPROVED
261 --
262
263 PROCEDURE get_moline_lov(
264 x_mo_line_lov OUT NOCOPY t_genref
265 , p_organization_id IN NUMBER
266 , p_mo_header_id IN NUMBER
267 , p_line_number IN VARCHAR2
268 ) IS
269 BEGIN
270 OPEN x_mo_line_lov FOR
271 SELECT line_number, line_id, move_order_type
272 FROM mtl_txn_request_lines_v mtrl
273 WHERE organization_id = p_organization_id
274 AND line_status IN(3, 7, 8)
275 AND NVL(quantity_delivered, 0) < quantity
276 AND header_id = p_mo_header_id
277 AND line_number LIKE(p_line_number)
278 AND EXISTS(
279 SELECT NULL
280 FROM mtl_system_items msi
281 WHERE msi.inventory_item_id = mtrl.inventory_item_id
282 AND msi.organization_id = p_organization_id
283 AND msi.mtl_transactions_enabled_flag = 'Y'
284 AND msi.inventory_item_flag = 'Y'
285 AND msi.bom_item_type = 4);
286 END get_moline_lov;
287
288 PROCEDURE get_mo_kanban(x_mo_kanban OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_kb_number IN VARCHAR2) IS
289 BEGIN
290 OPEN x_mo_kanban FOR
291 SELECT k.kanban_card_number
292 , m.reference_id
293 , m.line_id
294 FROM mtl_txn_request_lines m, mtl_kanban_cards k
295 WHERE m.reference_id = k.kanban_card_id
296 AND m.organization_id = p_organization_id
297 AND m.reference_type_code = 1
298 AND m.line_status IN(3, 7, 8)
299 AND NVL(quantity_delivered, 0) < quantity
300 AND k.kanban_card_number LIKE(p_kb_number);
301 END get_mo_kanban;
302
303 PROCEDURE get_mo_sohdr(x_mo_sohdr OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_sohdr_id IN VARCHAR2) IS
304 BEGIN
305 OPEN x_mo_sohdr FOR
306 SELECT UNIQUE wdd.source_header_number
307 FROM wsh_delivery_details wdd
308 WHERE wdd.organization_id = p_organization_id
309 AND wdd.released_status = 'S'
310 AND wdd.source_header_number LIKE(p_sohdr_id);
311 END;
312
313 --Bug #3796571, filtering Delivery LOV on Sales Order Number, Move Order, and Pickslip number.
314 PROCEDURE get_delivery_num(x_delivery OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_deliv_num IN VARCHAR2, p_so_number IN VARCHAR2 := NULL, p_mo_req_num IN VARCHAR2 := NULL, p_pickslip_number IN VARCHAR2 := NULL) IS
315 BEGIN
316 --bug 4951734, rewriting query with better performance.
317 /*
318 SELECT wnd.NAME, wnd.delivery_id
319 FROM wsh_new_deliveries wnd, wsh_delivery_details wdd, wsh_delivery_assignments_v wda, mtl_txn_request_lines ml
320 WHERE wda.delivery_id = wnd.delivery_id
321 AND wda.delivery_detail_id = wdd.delivery_detail_id
322 AND wdd.move_order_line_id = ml.line_id
323 AND wdd.organization_id = p_organization_id
324 AND ml.quantity > NVL(ml.quantity_delivered, 0)
325 AND wnd.NAME LIKE(p_deliv_num || '%');
326 */
327
328 --bug #3796571, forking the code for better performance when filtering parameters are not passed.
329 IF (p_so_number IS NULL AND p_mo_req_num IS NULL AND p_pickslip_number IS NULL) THEN
330 OPEN x_delivery FOR
331 SELECT UNIQUE wnd.NAME
332 , wnd.delivery_id
333 FROM wsh_new_deliveries_ob_grp_v wnd
334 , wsh_delivery_details_ob_grp_v wdd
335 , wsh_delivery_assignments wda
336 , mtl_txn_request_lines ml
337 WHERE wda.delivery_id = wnd.delivery_id
338 AND wda.delivery_detail_id = wdd.delivery_detail_id
339 AND wdd.released_status = 'S'
340 AND wdd.organization_id = p_organization_id
341 AND wdd.move_order_line_id = ml.line_id
342 AND ml.organization_id = p_organization_id
343 AND ml.inventory_item_id = wdd.inventory_item_id
344 AND ml.line_status = 7
345 AND ml.transaction_source_type_id IN (2, 8)
346 AND wnd.NAME LIKE (p_deliv_num) ;
347 ELSIF (p_pickslip_number IS NULL) THEN
348 OPEN x_delivery FOR
349 SELECT UNIQUE wnd.NAME
350 , wnd.delivery_id
351 FROM wsh_new_deliveries_ob_grp_v wnd
352 , wsh_delivery_details_ob_grp_v wdd
353 , wsh_delivery_assignments wda
354 , mtl_txn_request_lines ml
355 , mtl_txn_request_headers mh
356 WHERE wda.delivery_id = wnd.delivery_id
357 AND wda.delivery_detail_id = wdd.delivery_detail_id
358 AND wdd.released_status = 'S'
359 AND wdd.organization_id = p_organization_id
360 AND wdd.move_order_line_id = ml.line_id
361 AND ml.organization_id = p_organization_id
362 AND ml.inventory_item_id = wdd.inventory_item_id
363 AND ml.line_status = 7
364 AND ml.transaction_source_type_id IN (2, 8)
365 AND ml.header_id = mh.header_id
366 AND (p_so_number IS NULL OR wdd.source_header_number = p_so_number)
367 AND (p_mo_req_num IS NULL OR mh.request_number = p_mo_req_num)
368 AND wnd.NAME LIKE (p_deliv_num) ;
369 ELSE
370 OPEN x_delivery FOR
371 SELECT UNIQUE wnd.NAME
372 , wnd.delivery_id
373 FROM wsh_new_deliveries_ob_grp_v wnd
374 , wsh_delivery_details_ob_grp_v wdd
375 , wsh_delivery_assignments wda
376 , mtl_txn_request_lines ml
377 , mtl_txn_request_headers mh
378 , mtl_material_transactions_temp mmtt
379 WHERE wda.delivery_id = wnd.delivery_id
380 AND wda.delivery_detail_id = wdd.delivery_detail_id
381 AND wdd.released_status = 'S'
382 AND wdd.organization_id = p_organization_id
383 AND wdd.move_order_line_id = ml.line_id
384 AND ml.organization_id = p_organization_id
385 AND ml.inventory_item_id = wdd.inventory_item_id
386 AND ml.line_status = 7
387 AND ml.transaction_source_type_id IN (2, 8)
388 AND ml.header_id = mh.header_id
389 AND ml.organization_id = mmtt.organization_id
390 AND ml.line_id = mmtt.move_order_line_id
391 AND mh.header_id = mmtt.move_order_header_id
392 AND (p_so_number IS NULL OR wdd.source_header_number = p_so_number)
393 AND (p_mo_req_num IS NULL OR mh.request_number = p_mo_req_num)
394 AND (p_pickslip_number IS NULL OR mmtt.pick_slip_number = p_pickslip_number)
395 AND wnd.NAME LIKE (p_deliv_num) ;
396 END IF;
397 END;
398
399 --Bug #3796571, filtering Pickslip LOV on Sales Order Number, Move Order
400 PROCEDURE get_pickslip_num(x_pickslip OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_pickslip_num IN VARCHAR2, p_so_number IN VARCHAR2 := NULL, p_mo_req_num IN VARCHAR2 := NULL) IS
401 BEGIN
402 --bug #3796571, forking the code for better performance when filtering parameters are not passed.
403 IF (p_so_number IS NULL AND p_mo_req_num IS NULL) THEN
404 OPEN x_pickslip FOR
405 SELECT UNIQUE pick_slip_number
406 FROM mtl_material_transactions_temp mmtt
407 WHERE mmtt.organization_id = p_organization_id
408 AND mmtt.pick_slip_number LIKE (p_pickslip_num);
409 ELSE
410 OPEN x_pickslip FOR
411 SELECT UNIQUE pick_slip_number
412 FROM mtl_material_transactions_temp mmtt
413 , mtl_sales_orders mso
414 , mtl_txn_request_headers mh
415 WHERE mmtt.organization_id = p_organization_id
416 AND mmtt.move_order_header_id = mh.header_id
417 AND mso.sales_order_id = mmtt.transaction_source_id
418 AND (p_so_number IS NULL OR mso.segment1 = p_so_number)
419 AND (p_mo_req_num IS NULL OR mh.request_number = p_mo_req_num)
420 AND mmtt.pick_slip_number LIKE (p_pickslip_num);
421 END IF;
422 END;
423
424 PROCEDURE get_missing_qty_action_lov(x_miss_qty_action OUT NOCOPY t_genref, p_miss_qty_action VARCHAR2) AS
425 BEGIN
426 OPEN x_miss_qty_action FOR
427 SELECT meaning, lookup_code
428 FROM mfg_lookups
429 WHERE lookup_type = 'INV_MISSING_QTY_ACTIONS'
430 AND meaning LIKE p_miss_qty_action
431 ORDER BY lookup_code;
432 END;
433
434 END inv_mo_lovs;
[Home] [Help]