[Home] [Help]
1: PACKAGE BODY WMS_RULE_5 AS
2:
3: PROCEDURE open_curs
4: (
5: p_cursor IN OUT NOCOPY WMS_RULE_PVT.cv_pick_type,
6: p_organization_id IN NUMBER,
7: p_inventory_item_id IN NUMBER,
8: p_transaction_type_id IN NUMBER,
9: p_revision IN VARCHAR2,
101: ,sub.reservable_type
102: ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
103: ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
104: ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
105: ,WMS_Rule_PVT.GetConversionRate(
106: nvl(loc.pick_uom_code, sub.pick_uom_code)
107: ,msn.current_organization_id
108: ,msn.inventory_item_id) conversion_rate
109: ,msn.lpn_id lpn_id
145: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
146: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
147: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
148: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
149: and Wms_Rule_Pvt.Match_Planning_Group(base.ORGANIZATION_ID,base.locator_id, g_project_id, mptdtv.project_id, mptdtv.task_id,g_transaction_type_id,g_inventory_item_id,base.project_id,base.task_id) = 1
150: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
151: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
152: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
153: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
152: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
153: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
154: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
155: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
156: and ( mlna.EXPIRATION_DATE is NULL OR mlna.EXPIRATION_DATE > sysdate OR wms_rule_pvt.g_allow_expired_lot_txn = 'Y' ) order by decode(base.project_id,g_project_id,1,NULL,2,3) asc,base.CONVERSION_RATE desc
157: ,mln.EXPIRATION_DATE asc
158: ,base.DATE_RECEIVED asc
159: ,mln.LOT_NUMBER
160: ,base.SERIAL_NUMBER asc
196: ,sub.reservable_type
197: ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
198: ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
199: ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
200: ,WMS_Rule_PVT.GetConversionRate(
201: nvl(loc.pick_uom_code, sub.pick_uom_code)
202: ,msn.current_organization_id
203: ,msn.inventory_item_id) conversion_rate
204: ,msn.lpn_id lpn_id
243: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
244: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
245: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
246: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
247: and Wms_Rule_Pvt.Match_Planning_Group(base.ORGANIZATION_ID,base.locator_id, g_project_id, mptdtv.project_id, mptdtv.task_id,g_transaction_type_id,g_inventory_item_id,base.project_id,base.task_id) = 1
248: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
249: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
250: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
251: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
250: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
251: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
252: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
253: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
254: and ( mlna.EXPIRATION_DATE is NULL OR mlna.EXPIRATION_DATE > sysdate OR wms_rule_pvt.g_allow_expired_lot_txn = 'Y' ) group by base.ORGANIZATION_ID
255: ,base.INVENTORY_ITEM_ID
256: ,base.REVISION
257: ,base.LOT_NUMBER
258: ,base.LOT_EXPIRATION_DATE
310: ,sub.reservable_type
311: ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
312: ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
313: ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
314: ,WMS_Rule_PVT.GetConversionRate(
315: nvl(loc.pick_uom_code, sub.pick_uom_code)
316: ,msn.current_organization_id
317: ,msn.inventory_item_id) conversion_rate
318: ,msn.lpn_id lpn_id
353: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
354: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
355: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
356: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
357: and Wms_Rule_Pvt.Match_Planning_Group(base.ORGANIZATION_ID,base.locator_id, g_project_id, mptdtv.project_id, mptdtv.task_id,g_transaction_type_id,g_inventory_item_id,base.project_id,base.task_id) = 1
358: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
359: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
360: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
361: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
360: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
361: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
362: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
363: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
364: and ( mlna.EXPIRATION_DATE is NULL OR mlna.EXPIRATION_DATE > sysdate OR wms_rule_pvt.g_allow_expired_lot_txn = 'Y' ) group by base.ORGANIZATION_ID
365: ,base.INVENTORY_ITEM_ID
366: ,base.REVISION
367: ,base.LOT_NUMBER
368: ,base.LOT_EXPIRATION_DATE
424: ,x.reservable_type reservable_type
425: ,x.locreservable locreservable
426: ,x.lotreservable lotreservable
427: ,NVL(loc.pick_uom_code,sub.pick_uom_code) uom_code
428: ,WMS_Rule_PVT.GetConversionRate(
429: NVL(loc.pick_uom_code, sub.pick_uom_code)
430: ,x.organization_id
431: ,x.inventory_item_id) conversion_rate
432: ,NULL locator_inventory_item_id
515: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
516: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
517: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
518: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
519: and Wms_Rule_Pvt.Match_Planning_Group(base.ORGANIZATION_ID,base.locator_id, g_project_id, mptdtv.project_id, mptdtv.task_id,g_transaction_type_id,g_inventory_item_id,base.project_id,base.task_id) = 1
520: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
521: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
522: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
523: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
522: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
523: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
524: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
525: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
526: and ( mlna.EXPIRATION_DATE is NULL OR mlna.EXPIRATION_DATE > sysdate OR wms_rule_pvt.g_allow_expired_lot_txn = 'Y' ) group by base.ORGANIZATION_ID
527: ,base.INVENTORY_ITEM_ID
528: ,base.REVISION
529: ,base.LOT_NUMBER
530: ,base.LOT_EXPIRATION_DATE
551:
552: END open_curs;
553:
554: PROCEDURE fetch_one_row(
555: p_cursor IN WMS_RULE_PVT.cv_pick_type,
556: x_revision OUT NOCOPY VARCHAR2,
557: x_lot_number OUT NOCOPY VARCHAR2,
558: x_lot_expiration_date OUT NOCOPY DATE,
559: x_subinventory_code OUT NOCOPY VARCHAR2,
599:
600:
601: END fetch_one_row;
602:
603: PROCEDURE close_curs( p_cursor IN WMS_RULE_PVT.cv_pick_type) IS
604: BEGIN
605: if (p_cursor%ISOPEN) THEN
606: CLOSE p_cursor;
607: END IF;
608: END close_curs;
609:
610: -- LG convergence new procedure for the new manual picking select screen
611: PROCEDURE fetch_available_rows(
612: p_cursor IN WMS_RULE_PVT.cv_pick_type,
613: x_return_status OUT NOCOPY NUMBER) IS
614:
615: /* Fix for Bug#8360804 . Added temp variable of type available_inventory_tbl */
616: