[Home] [Help]
1: PACKAGE BODY WMS_RULE_6 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: ,base.SERIAL_NUMBER asc
160: ;
195: ,sub.reservable_type
196: ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
197: ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
198: ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
199: ,WMS_Rule_PVT.GetConversionRate(
200: nvl(loc.pick_uom_code, sub.pick_uom_code)
201: ,msn.current_organization_id
202: ,msn.inventory_item_id) conversion_rate
203: ,msn.lpn_id lpn_id
242: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
243: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
244: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
245: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
246: 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
247: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
248: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
249: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
250: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
249: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
250: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
251: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
252: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
253: 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
254: ,base.INVENTORY_ITEM_ID
255: ,base.REVISION
256: ,base.LOT_NUMBER
257: ,base.LOT_EXPIRATION_DATE
306: ,sub.reservable_type
307: ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
308: ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
309: ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
310: ,WMS_Rule_PVT.GetConversionRate(
311: nvl(loc.pick_uom_code, sub.pick_uom_code)
312: ,msn.current_organization_id
313: ,msn.inventory_item_id) conversion_rate
314: ,msn.lpn_id lpn_id
349: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
350: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
351: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
352: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
353: 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
354: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
355: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
356: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
357: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
356: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
357: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
358: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
359: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
360: 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
361: ,base.INVENTORY_ITEM_ID
362: ,base.REVISION
363: ,base.LOT_NUMBER
364: ,base.LOT_EXPIRATION_DATE
417: ,x.reservable_type reservable_type
418: ,x.locreservable locreservable
419: ,x.lotreservable lotreservable
420: ,NVL(loc.pick_uom_code,sub.pick_uom_code) uom_code
421: ,WMS_Rule_PVT.GetConversionRate(
422: NVL(loc.pick_uom_code, sub.pick_uom_code)
423: ,x.organization_id
424: ,x.inventory_item_id) conversion_rate
425: ,NULL locator_inventory_item_id
508: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
509: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
510: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
511: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
512: 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
513: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
514: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
515: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
516: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
515: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
516: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
517: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
518: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
519: 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
520: ,base.INVENTORY_ITEM_ID
521: ,base.REVISION
522: ,base.LOT_NUMBER
523: ,base.LOT_EXPIRATION_DATE
541:
542: END open_curs;
543:
544: PROCEDURE fetch_one_row(
545: p_cursor IN WMS_RULE_PVT.cv_pick_type,
546: x_revision OUT NOCOPY VARCHAR2,
547: x_lot_number OUT NOCOPY VARCHAR2,
548: x_lot_expiration_date OUT NOCOPY DATE,
549: x_subinventory_code OUT NOCOPY VARCHAR2,
589:
590:
591: END fetch_one_row;
592:
593: PROCEDURE close_curs( p_cursor IN WMS_RULE_PVT.cv_pick_type) IS
594: BEGIN
595: if (p_cursor%ISOPEN) THEN
596: CLOSE p_cursor;
597: END IF;
598: END close_curs;
599:
600: -- LG convergence new procedure for the new manual picking select screen
601: PROCEDURE fetch_available_rows(
602: p_cursor IN WMS_RULE_PVT.cv_pick_type,
603: x_return_status OUT NOCOPY NUMBER) IS
604:
605: /* Fix for Bug#8360804 . Added temp variable of type available_inventory_tbl */
606: