[Home] [Help]
1: PACKAGE BODY WMS_RULE_1 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,mln.EXPIRATION_DATE asc
157: ,base.DATE_RECEIVED asc
158: ,base.SERIAL_NUMBER asc,base.CONVERSION_RATE desc
159: ;
160: Elsif (g_serial_control = 1) AND (g_detail_serial = 3) THEN
194: ,sub.reservable_type
195: ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
196: ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
197: ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
198: ,WMS_Rule_PVT.GetConversionRate(
199: nvl(loc.pick_uom_code, sub.pick_uom_code)
200: ,msn.current_organization_id
201: ,msn.inventory_item_id) conversion_rate
202: ,msn.lpn_id lpn_id
241: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
242: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
243: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
244: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
245: 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
246: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
247: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
248: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
249: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
248: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
249: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
250: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
251: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
252: 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
253: ,base.INVENTORY_ITEM_ID
254: ,base.REVISION
255: ,base.LOT_NUMBER
256: ,base.LOT_EXPIRATION_DATE
304: ,sub.reservable_type
305: ,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
306: ,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
307: ,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
308: ,WMS_Rule_PVT.GetConversionRate(
309: nvl(loc.pick_uom_code, sub.pick_uom_code)
310: ,msn.current_organization_id
311: ,msn.inventory_item_id) conversion_rate
312: ,msn.lpn_id lpn_id
347: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
348: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
349: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
350: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
351: 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
352: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
353: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
354: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
355: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
354: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
355: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
356: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
357: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
358: 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
359: ,base.INVENTORY_ITEM_ID
360: ,base.REVISION
361: ,base.LOT_NUMBER
362: ,base.LOT_EXPIRATION_DATE
414: ,x.reservable_type reservable_type
415: ,x.locreservable locreservable
416: ,x.lotreservable lotreservable
417: ,NVL(loc.pick_uom_code,sub.pick_uom_code) uom_code
418: ,WMS_Rule_PVT.GetConversionRate(
419: NVL(loc.pick_uom_code, sub.pick_uom_code)
420: ,x.organization_id
421: ,x.inventory_item_id) conversion_rate
422: ,NULL locator_inventory_item_id
505: and decode(g_lot_number, '-9999', 'a', base.LOT_NUMBER) = decode(g_lot_number, '-9999', 'a', g_lot_number)
506: and decode(g_lpn_id, -9999, 1, base.lpn_id) = decode(g_lpn_id, -9999, 1, g_lpn_id)
507: and decode(g_cost_group_id, -9999, 1, base.cost_group_id) = decode(g_cost_group_id, -9999, 1, g_cost_group_id)
508: and mptdtv.PP_TRANSACTION_TEMP_ID = g_pp_transaction_temp_id
509: 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
510: and mln.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
511: and mln.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
512: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
513: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
512: and mln.LOT_NUMBER (+) = base.LOT_NUMBER
513: and mlna.ORGANIZATION_ID (+) = base.ORGANIZATION_ID
514: and mlna.INVENTORY_ITEM_ID (+) = base.INVENTORY_ITEM_ID
515: and mlna.LOT_NUMBER (+) = base.LOT_NUMBER
516: 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
517: ,base.INVENTORY_ITEM_ID
518: ,base.REVISION
519: ,base.LOT_NUMBER
520: ,base.LOT_EXPIRATION_DATE
537:
538: END open_curs;
539:
540: PROCEDURE fetch_one_row(
541: p_cursor IN WMS_RULE_PVT.cv_pick_type,
542: x_revision OUT NOCOPY VARCHAR2,
543: x_lot_number OUT NOCOPY VARCHAR2,
544: x_lot_expiration_date OUT NOCOPY DATE,
545: x_subinventory_code OUT NOCOPY VARCHAR2,
585:
586:
587: END fetch_one_row;
588:
589: PROCEDURE close_curs( p_cursor IN WMS_RULE_PVT.cv_pick_type) IS
590: BEGIN
591: if (p_cursor%ISOPEN) THEN
592: CLOSE p_cursor;
593: END IF;
594: END close_curs;
595:
596: -- LG convergence new procedure for the new manual picking select screen
597: PROCEDURE fetch_available_rows(
598: p_cursor IN WMS_RULE_PVT.cv_pick_type,
599: x_return_status OUT NOCOPY NUMBER) IS
600:
601: /* Fix for Bug#8360804 . Added temp variable of type available_inventory_tbl */
602: