1 PACKAGE BODY INV_TRANSACTION_LOVS AS
2 /* $Header: INVMWALB.pls 120.1 2005/06/17 10:08:15 appldev $ */
3
4 PROCEDURE GET_TXN_REASONS(x_txnreasonLOV OUT NOCOPY /* file.sql.39 change */ t_genref) IS
5 BEGIN
6 OPEN x_txnreasonLOV FOR
7 SELECT reason_id,reason_name,description
8 FROM MTL_TRANSACTION_REASONS
9 WHERE NVL(DISABLE_DATE,SYSDATE) >= SYSDATE;
10
11 END GET_TXN_REASONS;
12
13 PROCEDURE GET_CARRIER(x_getcarrierLOV OUT NOCOPY /* file.sql.39 change */ t_genref,
14 p_FromOrganization_Id IN NUMBER,
15 p_ToOrganization_Id IN NUMBER,
16 p_carrier IN VARCHAR2)
17 IS
18 BEGIN
19 OPEN x_getcarrierLOV FOR
20 select freight_code, description, distribution_account
21 from
22 org_enabled_freight_val_v
23 where organization_id = (SELECT decode(FOB_POINT,1,TO_ORGANIZATION_ID,2, FROM_ORGANIZATION_ID) from mtl_interorg_parameters where TO_ORGANIZATION_ID = p_FromOrganization_Id and from_organization_id =p_ToOrganization_Id )
24 AND freight_code LIKE p_carrier
25 order by freight_code;
26 END GET_CARRIER;
27
28
29
30 PROCEDURE GET_TXN_TYPES(x_motxntypeLOV OUT NOCOPY /* file.sql.39 change */ t_genref,
31 p_transaction_source_type_id IN NUMBER) IS
32 begin
33 open x_motxntypeLOV FOR
34 select transaction_type_id, transaction_type_name, description, transaction_action_id
35 from mtl_transaction_types
36 where transaction_source_type_id = p_transaction_Source_type_id;
37 END GET_TXN_TYPES;
38
39
40 PROCEDURE GET_TXN_TYPES(x_txntypeLOV OUT NOCOPY /* file.sql.39 change */ t_genref,
41 p_Transaction_Action_Id IN NUMBER,
42 p_Transaction_Source_Type_Id IN NUMBER,
43 p_Transaction_Type_Name IN VARCHAR2) IS
44 BEGIN
45 OPEN x_txntypeLOV FOR
46 select transaction_type_id,transaction_type_name,description
47 from mtl_transaction_types
48 where transaction_action_id = p_Transaction_Action_Id and
49 transaction_source_type_id = p_Transaction_Source_Type_Id and
50 transaction_type_name like p_Transaction_Type_Name;
51
52 END GET_TXN_TYPES;
53
54 -- this procedure gets the accounts for a given organization and puts them in a
55 -- reference cursor.
56
57
58 PROCEDURE GET_ACCOUNT_ALIAS(x_Accounts_Info OUT NOCOPY /* file.sql.39 change */ t_genref,
59 p_Organization_Id IN NUMBER,
60 p_Description IN VARCHAR2) IS
61 BEGIN
62 OPEN x_Accounts_Info FOR
63 select distribution_account,disposition_id,description from mtl_generic_dispositions
64 where organization_id = p_Organization_Id and
65 description like p_Description;
66
67 END GET_ACCOUNT_ALIAS;
68
69
70 PROCEDURE GET_ACCOUNTS(x_Accounts OUT NOCOPY /* file.sql.39 change */ t_genref,
71 p_Organization_Id IN NUMBER,
72 p_Concatenated_Segments IN VARCHAR2) IS
73 BEGIN
74 OPEN x_Accounts FOR
75 SELECT a.code_combination_id,a.concatenated_segments FROM gl_code_combinations_kfv a,
76 org_organization_definitions b
77 WHERE b.organization_id = p_Organization_Id AND
78 a.chart_of_accounts_id = b.chart_of_accounts_id AND
79 a.concatenated_segments like p_Concatenated_Segments
80 and a.enabled_flag = 'Y' and nvl(a.start_date_active,sysdate-1)<=sysdate and
81 nvl(a.end_date_active,sysdate+1)>sysdate;
82 END GET_ACCOUNTS;
83
84 procedure GET_ITEMS(x_items OUT NOCOPY /* file.sql.39 change */ t_genref,
85 p_organization_id IN NUMBER,
86 p_concatenated_segments IN VARCHAR2) IS
87 BEGIN
88 open x_items for
89 select concatenated_segments, inventory_item_id, description,
90 revision_qty_control_code, lot_control_code, location_control_code,
91 serial_number_control_code, restrict_subinventories_code,
92 restrict_locators_code
93 from mtl_system_items_kfv
94 where organization_id = p_organization_id
95 and mtl_transactions_enabled_flag = 'Y'
96 and concatenated_segments like p_concatenated_segments;
97 END;
98
99 --This procedure gets all the transactable items in an organization and returns
100 --them in a ref cursor back to the mobile server
101
102 PROCEDURE GET_TRANSACTABLE_ITEMS(x_Items OUT NOCOPY /* file.sql.39 change */ t_genref,
103 p_Organization_Id IN NUMBER,
104 p_Concatenated_Segments IN VARCHAR2,
105 p_Transaction_Action_Id IN NUMBER,
106 p_To_Organization_Id IN NUMBER DEFAULT
107 NULL)
108 IS
109 BEGIN
110 IF p_Transaction_Action_Id = 3 THEN
111 OPEN x_Items FOR
112 select concatenated_segments,inventory_item_id,description,
113 revision_qty_control_code,lot_control_code,location_control_code,
114 serial_number_control_code,restrict_subinventories_code,
115 restrict_locators_code ,
116 Nvl(shelf_life_code, 1),
117 Nvl(shelf_life_days,0),
118 Nvl(effectivity_control,1)
119 from mtl_system_items_kfv
120 where organization_id = p_Organization_Id
121 and mtl_transactions_enabled_flag = 'Y'
122 and inventory_item_id IN
123 (SELECT inventory_item_id
124 from mtl_system_items
125 where organization_id = p_To_Organization_Id
126 and mtl_transactions_enabled_flag = 'Y')
127 and concatenated_segments like p_Concatenated_Segments;
128 ELSE
129 OPEN x_Items FOR
130 select concatenated_segments,inventory_item_id,description,
131 revision_qty_control_code,lot_control_code,
132 serial_number_control_code,restrict_subinventories_code,restrict_locators_code,
133 location_control_code,Nvl(shelf_life_code, 1),Nvl(shelf_life_days,0), Nvl(effectivity_control,1)
134 from mtl_system_items_kfv
135 where organization_id = p_Organization_Id
136 and mtl_transactions_enabled_flag = 'Y'
137 and concatenated_segments like p_Concatenated_Segments;
138 END IF;
139 END GET_TRANSACTABLE_ITEMS;
140
141 PROCEDURE GET_VALID_LOCATORS(x_Locators OUT NOCOPY /* file.sql.39 change */ t_genref,
142 p_Organization_Id IN NUMBER,
143 p_Subinventory_Code IN VARCHAR2,
144 p_Restrict_Locators_Code IN NUMBER,
145 p_Inventory_Item_Id IN NUMBER,
146 p_Concatenated_Segments IN VARCHAR2)
147
148 IS
149 l_locator_id NUMBER;
150 BEGIN
151 IF p_Restrict_Locators_Code = 1 THEN --Locators restricted to predefined list
152 OPEN x_Locators FOR
153 select a.inventory_location_id, a.concatenated_segments,a.description
154 FROM mtl_item_locations_kfv a,mtl_secondary_locators b
155 WHERE b.organization_id = p_Organization_Id and
156 b.inventory_item_id = p_Inventory_Item_Id and
157 b.subinventory_code = p_Subinventory_Code and
158 a.inventory_location_id = b.secondary_locator and
159 a.concatenated_segments like (p_concatenated_segments);
160 ELSE --Locators not restricted
161 OPEN x_Locators FOR
162 select inventory_location_id,concatenated_segments,description
163 FROM mtl_item_locations_kfv
164 WHERE organization_id = p_Organization_Id and
165 subinventory_code = p_Subinventory_Code and
166 concatenated_segments like (p_concatenated_segments);
167 END IF;
168 END GET_VALID_LOCATORS;
169
170 PROCEDURE GET_VALID_TO_LOCS(x_Locators OUT NOCOPY /* file.sql.39 change */ t_genref,
171 p_Transaction_Action_Id IN NUMBER,
172 p_To_Organization_Id IN NUMBER,
173 p_Organization_Id IN NUMBER,
174 p_Subinventory_Code IN VARCHAR2,
175 p_Restrict_Locators_Code IN NUMBER,
176 p_Inventory_Item_Id IN NUMBER,
177 p_Concatenated_Segments IN VARCHAR2)
178 IS
179 l_org NUMBER;
180 l_Restrict_Locators_Code NUMBER;
181 BEGIN
182 IF p_Transaction_Action_Id = 3 THEN
183 l_org := p_To_Organization_Id;
184 select restrict_locators_code into l_Restrict_Locators_Code
185 from mtl_system_items
186 where inventory_item_id = p_Inventory_Item_Id and organization_id = l_org;
187 ELSE
188 l_org := p_Organization_Id;
189 l_Restrict_Locators_Code := p_Restrict_Locators_Code;
190 END IF;
191
192 GET_VALID_LOCATORS(x_Locators,
193 l_org,
194 p_Subinventory_Code,
195 l_Restrict_Locators_Code,
196 p_Inventory_Item_Id,
197 p_Concatenated_Segments);
198 END GET_VALID_TO_LOCS;
199
200 PROCEDURE GET_VALID_SUBS(x_Zones OUT NOCOPY /* file.sql.39 change */ t_genref,
201 p_organization_id IN NUMBER,
202 p_subinventory_code IN VARCHAR2) IS
203 BEGIN
204 open x_Zones for
205 SELECT secondary_inventory_name, description, locator_type, asset_inventory
206 FROM mtl_secondary_inventories
207 WHERE organization_id = p_organization_id
208 AND secondary_inventory_name like p_subinventory_code;
209 END GET_VALID_SUBS;
210
211 PROCEDURE GET_FROM_SUBS(x_Zones OUT NOCOPY /* file.sql.39 change */ t_genref,
212 p_organization_id IN NUMBER) IS
213 BEGIN
214 open x_Zones for
215 SELECT secondary_inventory_name,description,asset_inventory
216 FROM mtl_secondary_inventories
217 WHERE organization_id = p_Organization_Id;
218 END GET_FROM_SUBS;
219
220 PROCEDURE GET_FROM_SUBS(x_Zones OUT NOCOPY /* file.sql.39 change */ t_genref,
221 p_Organization_Id IN NUMBER,
222 p_Inventory_Item_Id IN NUMBER,
223 p_Restrict_Subinventories_Code IN NUMBER,
224 p_Secondary_Inventory_Name IN VARCHAR2,
225 p_Transaction_Action_Id IN NUMBER)
226 IS
227 l_expense_to_asset VARCHAR2(1);
228 BEGIN
229 FND_PROFILE.GET('INV:EXPENSE_TO_ASSET_TRANSFER',l_expense_to_asset);
230
231 IF( NVL(l_expense_to_asset,'2') = '1') THEN
232 IF (p_Transaction_Action_Id <> 2 and p_Transaction_Action_Id <>3) THEN
233 IF p_Restrict_Subinventories_Code = 1 THEN
234 OPEN x_Zones FOR
235 SELECT secondary_inventory_name,description,asset_inventory
236 FROM mtl_item_sub_trk_val_v
237 WHERE organization_id = p_Organization_Id AND
238 inventory_item_id = p_Inventory_Item_Id AND
239 secondary_inventory_name like p_Secondary_Inventory_Name;
240 ELSE
241
242 OPEN x_Zones FOR
243 SELECT secondary_inventory_name,description,asset_inventory
244 FROM mtl_subinventories_trk_val_v
245 WHERE organization_Id = p_Organization_Id AND
246 secondary_inventory_name like p_Secondary_Inventory_Name;
247 END IF;
248 ELSE
249 IF p_Restrict_Subinventories_Code = 1 THEN
250 OPEN x_Zones FOR
251 SELECT secondary_inventory_name,description,asset_inventory
252 FROM mtl_item_sub_trk_val_v
253 WHERE organization_id = p_Organization_Id AND
254 inventory_item_id = p_Inventory_Item_Id AND
255 secondary_inventory_name like p_Secondary_Inventory_Name;
256 ELSE
257 OPEN x_Zones FOR
258 SELECT secondary_inventory_name,description,asset_inventory
259 FROM mtl_subinventories_trk_val_v
260 WHERE organization_Id = p_Organization_Id AND
261 secondary_inventory_name like p_Secondary_Inventory_Name;
262 END IF;
263 END IF;
264 ELSE
265 IF (p_Transaction_Action_Id <> 2 and p_Transaction_Action_Id <>3) THEN
266 IF p_Restrict_Subinventories_Code = 1 THEN
267 OPEN x_Zones FOR
268 SELECT secondary_inventory_name,description,asset_inventory
269 FROM mtl_item_sub_val_v
270 WHERE organization_id = p_Organization_Id AND
271 inventory_item_id = p_Inventory_Item_Id AND
272 secondary_inventory_name like p_Secondary_Inventory_Name;
273 ELSE
274
275 OPEN x_Zones FOR
276 SELECT secondary_inventory_name,description,asset_inventory
277 FROM mtl_subinventories_trk_val_v
278 WHERE organization_Id = p_Organization_Id AND
279 secondary_inventory_name like p_Secondary_Inventory_Name;
280 END IF;
281 ELSE
282 IF p_Restrict_Subinventories_Code = 1 THEN
283 OPEN x_Zones FOR
284 SELECT secondary_inventory_name,description,asset_inventory
285 FROM mtl_item_sub_trk_val_v
286 WHERE organization_id = p_Organization_Id AND
287 inventory_item_id = p_Inventory_Item_Id AND
288 secondary_inventory_name like p_Secondary_Inventory_Name;
289 ELSE
290 OPEN x_Zones FOR
291 SELECT secondary_inventory_name,description,asset_inventory
292 FROM mtl_subinventories_trk_val_v
293 WHERE organization_Id = p_Organization_Id AND
294 secondary_inventory_name like p_Secondary_Inventory_Name;
295 END IF;
296 END IF;
297 END IF;
298 END GET_FROM_SUBS;
299
300
301 PROCEDURE GET_TO_SUB(x_Zones OUT NOCOPY /* file.sql.39 change */ t_genref,
302 p_organization_id IN NUMBER,
303 p_secondary_inventory_name IN VARCHAR2) IS
304 BEGIN
305 open x_Zones for
306 sELECT secondary_inventory_name,description,asset_inventory
307 FROM mtl_secondary_inventories
308 WHERE organization_id = p_Organization_Id
309 AND secondary_inventory_name <> p_secondary_inventory_name;
310 END GET_TO_SUB;
311
312 PROCEDURE GET_TO_SUB(x_to_sub OUT NOCOPY /* file.sql.39 change */ t_genref,
313 p_Organization_Id IN NUMBER,
314 p_Inventory_Item_Id IN NUMBER,
315 p_from_Secondary_Name IN VARCHAR2,
316 p_Restrict_Subinventories_Code IN NUMBER,
317 p_Secondary_Inventory_Name IN VARCHAR2,
318 p_From_Sub_Asset_Inventory IN VARCHAR2,
319 p_Transaction_Action_Id IN NUMBER,
320 p_To_Organization_Id IN NUMBER,
321 p_Serial_Number_Control_Code IN NUMBER)
322 --p_Serial IN VARCHAR2)
323 IS
324 l_expense_to_asset VARCHAR2(1);
325 l_Inventory_Asset_Flag VARCHAR2(1);
326 l_org NUMBER;
327 l_Restrict_Subinventories_Code NUMBER;
328 l_From_Sub VARCHAR2(10);
329 l_From_Sub_Asset_Inventory VARCHAR2(1);
330 BEGIN
331 IF p_Transaction_Action_Id = 3 THEN
332 l_org := p_To_Organization_Id;
333 select restrict_subinventories_code
334 into l_Restrict_Subinventories_Code
335 from mtl_system_items
336 where organization_id = l_org
337 and inventory_item_id = p_Inventory_Item_Id;
338 ELSE
339 l_org := p_Organization_Id;
340 l_Restrict_Subinventories_Code := p_Restrict_Subinventories_Code;
341 END IF;
342
343 IF p_Serial_Number_Control_Code not in (1, 6) THEN
344 l_from_sub:=p_from_secondary_name;
345 l_from_sub_asset_inventory:=p_from_sub_asset_inventory;
346 --select current_subinventory_code
347 --into l_From_Sub
348 --from mtl_serial_numbers
349 --where current_organization_id = l_org
350 --and serial_number = p_Serial
351 --and inventory_item_id = p_Inventory_Item_Id;
352
353 --select asset_inventory
354 --into l_From_Sub_Asset_Inventory
355 --from mtl_secondary_inventories
356 --where organization_id = l_org
357 --and secondary_inventory_name = l_From_Sub;
358 ELSE
359 l_From_Sub_Asset_Inventory := p_From_Sub_Asset_Inventory;
360 END IF;
361
362 SELECT Inventory_Asset_Flag
363 INTO l_Inventory_Asset_Flag
364 FROM mtl_system_items
365 WHERE inventory_item_id = p_Inventory_Item_Id
366 AND organization_id = l_org;
367
368 FND_PROFILE.GET('INV:EXPENSE_TO_ASSET_TRANSFER',l_expense_to_asset);
369 IF (nvl(l_expense_to_asset,'2') = '1') THEN
370 IF l_Restrict_Subinventories_Code = 1 THEN
371 OPEN x_to_sub FOR
372 SELECT secondary_inventory_name,description
373 FROM MTL_ITEM_SUB_VAL_V
374 WHERE organization_id = l_org
375 AND inventory_item_id = p_Inventory_Item_Id
376 AND secondary_inventory_name like p_Secondary_Inventory_Name;
377 ELSE
378 OPEN x_to_sub FOR
379 SELECT secondary_inventory_name,description
380 FROM MTL_SUBINVENTORIES_VAL_V
381 WHERE ORGANIZATION_ID = l_org
382 AND SECONDARY_INVENTORY_NAME like p_Secondary_Inventory_Name;
383 END IF;
384 ELSE
385 IF l_Restrict_Subinventories_Code = 1 THEN
386 IF l_Inventory_Asset_Flag = 'Y' THEN
387 IF l_From_Sub_Asset_Inventory = 1 THEN
388 OPEN x_to_sub FOR
389 SELECT secondary_inventory_name,description
390 FROM MTL_ITEM_SUB_VAL_V
391 WHERE ORGANIZATION_ID = l_org
392 AND INVENTORY_ITEM_ID = p_Inventory_Item_Id
393 AND SECONDARY_INVENTORY_NAME like p_Secondary_Inventory_Name;
394 ELSE
395 OPEN x_to_sub FOR
396 SELECT secondary_inventory_name,description
397 FROM MTL_ITEM_SUB_EXP_VAL_V
398 WHERE ORGANIZATION_ID = l_org
399 AND INVENTORY_ITEM_ID = p_Inventory_Item_Id
400 AND SECONDARY_INVENTORY_NAME like p_Secondary_Inventory_Name;
401 END IF;
402 ELSE
403 OPEN x_to_sub FOR
404 SELECT secondary_inventory_name,description
405 FROM MTL_ITEM_SUB_VAL_V
406 WHERE ORGANIZATION_ID = l_org
407 AND INVENTORY_ITEM_ID = p_Inventory_Item_Id
408 AND SECONDARY_INVENTORY_NAME like p_Secondary_Inventory_Name;
409 END IF;
410 ELSE
411 IF l_Inventory_Asset_Flag = 'Y' THEN
412 IF l_From_Sub_Asset_Inventory = 1 THEN
413 OPEN x_to_sub FOR
414 SELECT secondary_inventory_name,description
415 FROM MTL_SUBINVENTORIES_VAL_V
416 WHERE ORGANIZATION_ID = l_org
417 AND SECONDARY_INVENTORY_NAME like p_Secondary_Inventory_Name;
418 ELSE
419 OPEN x_to_sub FOR
420 SELECT secondary_inventory_name,description
421 FROM MTL_SUB_EXP_VAL_V
422 WHERE ORGANIZATION_ID = l_org
423 AND SECONDARY_INVENTORY_NAME like p_Secondary_Inventory_Name;
424 END IF;
425 ELSE
426 OPEN x_to_sub FOR
427 SELECT secondary_inventory_name,description
428 FROM MTL_SUBINVENTORIES_VAL_V
429 WHERE ORGANIZATION_ID = l_org
430 AND SECONDARY_INVENTORY_NAME like p_Secondary_Inventory_Name;
431 END IF;
432 END IF;
433 END IF;
434 END GET_TO_SUB;
435
436
437
438 PROCEDURE GET_ORG(x_org OUT NOCOPY /* file.sql.39 change */ t_genref,
439 p_responsibility_id IN NUMBER,
440 p_resp_application_id IN NUMBER) IS
441 BEGIN
442 open x_org FOR
443 select organization_code, organization_name, organization_id
444 from org_access_view
445 where responsibility_id = p_responsibility_id
446 and p_resp_application_id = p_resp_application_id
447 order by organization_code;
448
449 END;
450
451 PROCEDURE GET_TO_ORG(x_Organizations OUT NOCOPY /* file.sql.39 change */ t_genref,
452 p_From_Organization_Id IN NUMBER) IS
453
454 BEGIN
455 OPEN x_Organizations FOR
456 SELECT
457 a.to_organization_id,b.organization_code,c.name, a.intransit_type
458 FROM mtl_interorg_parameters a, mtl_parameters b,hr_all_organization_units c
459 WHERE a.from_organization_id = p_From_Organization_Id AND
460 a.to_organization_id = b.organization_id
461 AND a.to_organization_id = c.organization_id
462 order by 2;
463
464
465
466 END GET_TO_ORG;
467
468 PROCEDURE GET_VALID_UOMS(x_UOMS OUT NOCOPY /* file.sql.39 change */ t_genref,
469 p_Organization_Id IN NUMBER,
470 p_Inventory_Item_Id IN NUMBER,
471 p_UOM_Code IN VARCHAR2) IS
472 BEGIN
473
474 OPEN x_UOMS FOR
475 SELECT uom_Code, unit_of_measure, description FROM mtl_item_uoms_view
476 WHERE organization_id = p_Organization_Id AND
477 inventory_item_id = p_Inventory_Item_Id AND
478 uom_Code like p_UOM_Code;
479
480 END GET_VALID_UOMS;
481
482 PROCEDURE GET_VALID_LOTS(x_Lots OUT NOCOPY /* file.sql.39 change */ t_genref,
483 p_Organization_Id IN NUMBER,
484 p_Inventory_Item_Id IN NUMBER,
485 p_Lot IN VARCHAR2) IS
486 BEGIN
487 OPEN x_Lots FOR
488 SELECT Lot_Number,to_char(expiration_date,'MM-DD-YYYY')
489 FROM mtl_lot_numbers
490 WHERE organization_id = p_Organization_Id AND
491 inventory_item_id = p_Inventory_Item_Id AND
492 lot_number like (p_lot);
493
494 END GET_VALID_LOTS;
495
496 PROCEDURE GET_VALID_LOTS(x_Lots OUT NOCOPY /* file.sql.39 change */ t_genref,
497 p_Organization_Id IN NUMBER,
498 p_Inventory_Item_Id IN NUMBER,
499 p_subcode IN VARCHAR2,
500 p_revision IN VARCHAR2,
501 p_locatorid IN NUMBER,
502 p_Lot IN VARCHAR2) IS
503 BEGIN
504 OPEN x_Lots FOR
505 SELECT b.Lot_Number,to_char(b.expiration_date,'MM-DD-YYYY')
506 FROM mtl_onhand_quantities_detail a, mtl_lot_numbers b
507 -- Bug 2687570, use MOQD instead of MOQ because consigned stock is not visible in MOQ
508 WHERE a.organization_id = p_Organization_Id AND
509 a.inventory_item_id = p_Inventory_Item_Id AND
510 a.subinventory_code = p_subcode AND
511 Nvl(a.revision, '##') = Nvl(p_revision,'##') AND
512 Nvl(a.locator_id, '-1') = Nvl(p_locatorid ,'-1')AND
513 a.lot_number = b.lot_number AND
514 a.lot_number like (p_lot);
515
516 END GET_VALID_LOTS;
517
518
519
520
521
522
523
524
525
526 PROCEDURE GET_VALID_REVS(x_Revs OUT NOCOPY /* file.sql.39 change */ t_genref,
527 p_Organization_Id IN NUMBER,
528 p_Inventory_Item_Id IN NUMBER,
529 p_Revision IN VARCHAR2) IS
530 BEGIN
531 OPEN x_Revs FOR
532 SELECT revision, effectivity_date, Nvl(description,'')
533 FROM mtl_item_revisions
534 WHERE organization_Id = p_Organization_Id AND
535 inventory_item_id = p_Inventory_Item_Id AND
536 revision like (p_revision);
537
538 END GET_VALID_REVS;
539
540
541 PROCEDURE GET_VALID_SERIAL_REC_2(x_RSerials IN OUT NOCOPY /* file.sql.39 change */ t_genref,
542 p_Current_Organization_Id IN NUMBER,
543 p_Inventory_Item_Id IN NUMBER,
544 p_Serial_Number IN VARCHAR2) IS
545 BEGIN
546 OPEN x_RSerials FOR
547 select serial_number,current_subinventory_code,current_locator_id, lot_number
548 from mtl_serial_numbers
549 where Current_Organization_Id = p_Current_Organization_Id
550 AND inventory_item_id = p_Inventory_Item_Id and current_status = 1
551 and serial_number like p_Serial_Number;
552
553 END GET_VALID_SERIAL_REC_2;
554
555 PROCEDURE GET_VALID_SERIAL_REC_5(x_RSerials IN OUT NOCOPY /* file.sql.39 change */ t_genref,
556 p_Current_Organization_Id IN NUMBER,
557 p_Inventory_Item_Id IN NUMBER,
558 p_Current_Subinventory_Code IN VARCHAR2,
559 p_Current_Locator_Id IN NUMBER,
560 p_Lot_Number IN VARCHAR2,
561 p_Serial_Number IN VARCHAR2) IS
562 BEGIN
563
564 OPEN x_RSerials FOR
565 select serial_number,current_subinventory_code, current_locator_id,lot_number
566 from mtl_serial_numbers
567 where Current_Organization_Id = p_Current_Organization_Id
568 and inventory_item_id = p_Inventory_Item_Id and current_status = 4
569 and serial_number like p_serial_number;
570
571 IF x_RSerials IS NULL and p_Serial_Number IS NOT NULL THEN
572 OPEN x_RSerials FOR
573 select p_Serial_Number,p_Current_Subinventory_Code,p_Current_Locator_Id,
574 p_Lot_Number
575 from DUAL;
576 END IF;
577
578 END GET_VALID_SERIAL_REC_5;
579
580
581 --During an issue, if it is the first serial number then
582 --we can accept any serial that resides in stores
583 --however, after the first serial has been scanned we must
584 --make sure that all subsequent serials are from the same
585 --locator and same sub.
586
587
588 PROCEDURE GET_VALID_SERIAL_ISSUE(x_RSerials OUT NOCOPY /* file.sql.39 change */ t_genref,
589 p_Current_Organization_Id IN NUMBER,
590 p_Current_Subinventory_Code IN VARCHAR2,
591 p_Current_Locator_Id IN NUMBER,
592 p_Current_Lot_Number IN VARCHAR2,
593 p_Inventory_Item_Id IN NUMBER,
594 p_Serial_Number IN VARCHAR2)
595 IS
596 BEGIN
597
598 IF p_Current_Subinventory_Code IS NULL THEN
599 OPEN x_RSerials FOR
600 SELECT
601 a.serial_number,a.current_Subinventory_code,Nvl(a.current_locator_id,-1),
602 a.lot_number, a.revision ,b.expiration_date
603 from mtl_serial_numbers a, mtl_lot_numbers b
604 where a.Current_organization_Id = p_Current_Organization_Id
605 and a.inventory_item_id = p_Inventory_Item_Id
606 and a.current_status = 3
607 AND a.lot_number = b.lot_number
608 and a.serial_number like p_serial_number;
609
610 ELSE
611 OPEN x_RSerials FOR
612 select a.serial_number,
613 a.current_subinventory_code,
614 Nvl(a.current_locator_id,-1),
615 a.lot_number, a.revision,
616 b.expiration_date
617 from mtl_serial_numbers a , mtl_lot_numbers b
618 where a.Current_organization_Id = p_Current_Organization_Id
619 and a.inventory_item_id = p_Inventory_Item_Id
620 and a.current_status = 3
621 and a.serial_number like p_serial_number
622 and a.current_subinventory_code = p_current_subinventory_code
623 and Nvl(a.current_locator_id,-1) = Decode(p_current_locator_id,'-1',Nvl(a.current_locator_id,-1),p_current_locator_id)
624 AND a.lot_number = b.lot_number;
625
626 END IF;
627
628 END GET_VALID_SERIAL_ISSUE;
629
630 PROCEDURE GET_VALID_SERIALS(x_RSerials OUT NOCOPY /* file.sql.39 change */ t_genref,
631 p_Serial_Number_Control_Code IN NUMBER,
632 p_Inventory_Item_Id IN NUMBER,
633 p_Current_Organization_Id IN NUMBER,
634 p_Current_Subinventory_Code IN VARCHAR2,
635 p_Current_Locator_Id IN NUMBER,
636 p_Lot_Number IN VARCHAR2,
637 p_Transaction_Action_Id IN NUMBER,
638 p_Serial_Number IN VARCHAR2) IS
639
640 BEGIN
641 --Predefined and Receipt
642 IF p_Transaction_Action_Id = 27 and p_Serial_Number_Control_Code = 2 THEN
643 GET_VALID_SERIAL_REC_2(x_RSerials,
644 p_Current_Organization_Id,
645 p_Inventory_Item_Id,
646 p_Serial_Number);
647 ELSIf p_Transaction_Action_Id = 27 and p_Serial_Number_Control_Code = 5 THEN
648 --Dynamic serial entry upon receipt and Receipt
649 GET_VALID_SERIAL_REC_5(x_RSerials,
650 p_Current_Organization_Id,
651 p_Inventory_Item_Id,
652 p_Current_Subinventory_Code,
653 p_Current_Locator_Id,
654 p_Lot_Number,
655 p_Serial_Number);
656 ELSE
657 --Issue or transfer transaction
658 GET_VALID_SERIAL_ISSUE(x_RSerials,
659 p_Current_Organization_Id,
660 p_Current_Subinventory_Code,
661 p_Current_Locator_Id,
662 p_Lot_Number,
663 p_Inventory_Item_Id,
664 p_Serial_Number);
665
666 END IF;
667 END GET_VALID_SERIALS;
668 END INV_TRANSACTION_LOVS;
[Home] [Help]