[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select msei.secondary_inventory_name subinventory_code --changed
,mil.inventory_location_id locator_id
,msei.organization_id organization_id --changed
,mil.project_id project_id
,mil.task_id task_id
,g_inventory_item_id inventory_item_id
,mil.location_current_units location_current_units
,mil.inventory_item_id locator_inventory_item_id
,mil.empty_flag empty_flag
,mil.mixed_items_flag mixed_items_flag
,mil.LAST_UPDATE_DATE
,mil.LAST_UPDATED_BY
,mil.CREATION_DATE
,mil.CREATED_BY
,mil.LAST_UPDATE_LOGIN
,mil.DESCRIPTION
,mil.DESCRIPTIVE_TEXT
,mil.DISABLE_DATE
,mil.INVENTORY_LOCATION_TYPE
,mil.PICKING_ORDER
,mil.PHYSICAL_LOCATION_CODE
,mil.LOCATION_MAXIMUM_UNITS
,mil.LOCATION_WEIGHT_UOM_CODE
,mil.MAX_WEIGHT
,mil.VOLUME_UOM_CODE
,mil.MAX_CUBIC_AREA
,mil.X_COORDINATE
,mil.Y_COORDINATE
,mil.Z_COORDINATE
,mil.INVENTORY_ACCOUNT_ID
,mil.SEGMENT1
,mil.SEGMENT2
,mil.SEGMENT3
,mil.SEGMENT4
,mil.SEGMENT5
,mil.SEGMENT6
,mil.SEGMENT7
,mil.SEGMENT8
,mil.SEGMENT9
,mil.SEGMENT10
,mil.SEGMENT11
,mil.SEGMENT12
,mil.SEGMENT13
,mil.SEGMENT14
,mil.SEGMENT15
,mil.SEGMENT16
,mil.SEGMENT17
,mil.SEGMENT18
,mil.SEGMENT19
,mil.SEGMENT20
,mil.SUMMARY_FLAG
,mil.ENABLED_FLAG
,mil.START_DATE_ACTIVE
,mil.END_DATE_ACTIVE
,mil.ATTRIBUTE_CATEGORY
,mil.ATTRIBUTE1
,mil.ATTRIBUTE2
,mil.ATTRIBUTE3
,mil.ATTRIBUTE4
,mil.ATTRIBUTE5
,mil.ATTRIBUTE6
,mil.ATTRIBUTE7
,mil.ATTRIBUTE8
,mil.ATTRIBUTE9
,mil.ATTRIBUTE10
,mil.ATTRIBUTE11
,mil.ATTRIBUTE12
,mil.ATTRIBUTE13
,mil.ATTRIBUTE14
,mil.ATTRIBUTE15
,mil.REQUEST_ID
,mil.PROGRAM_APPLICATION_ID
,mil.PROGRAM_ID
,mil.PROGRAM_UPDATE_DATE
,mil.PHYSICAL_LOCATION_ID
,mil.PICK_UOM_CODE
,mil.DIMENSION_UOM_CODE
,mil.LENGTH
,mil.WIDTH
,mil.HEIGHT
,mil.LOCATOR_STATUS
,mil.STATUS_ID
,mil.CURRENT_CUBIC_AREA
,mil.AVAILABLE_CUBIC_AREA
,mil.CURRENT_WEIGHT
,mil.AVAILABLE_WEIGHT
,mil.LOCATION_AVAILABLE_UNITS
,mil.SUGGESTED_CUBIC_AREA
,mil.SUGGESTED_WEIGHT
,mil.LOCATION_SUGGESTED_UNITS
,mil.rowid
from MTL_ITEM_LOCATIONS mil
,MTL_SECONDARY_INVENTORIES msei
where mil.organization_id(+) = msei.organization_id
and mil.subinventory_code(+) = msei.secondary_inventory_name
and NVL(msei.disable_date, sysdate+1) > sysdate
and NVL(mil.disable_date, sysdate+1) > sysdate
and mil.ORGANIZATION_ID = g_organization_id
'; --8467209.added org_id above
select msei.secondary_inventory_name subinventory_code -- changed
,mil.inventory_location_id locator_id
,msei.organization_id organization_id -- changed
,mil.project_id project_id
,mil.task_id task_id
,g_inventory_item_id inventory_item_id
,mil.location_current_units location_current_units
,mil.inventory_item_id locator_inventory_item_id
,mil.empty_flag empty_flag
,mil.mixed_items_flag mixed_items_flag
,mil.LAST_UPDATE_DATE
,mil.LAST_UPDATED_BY
,mil.CREATION_DATE
,mil.CREATED_BY
,mil.LAST_UPDATE_LOGIN
,mil.DESCRIPTION
,mil.DESCRIPTIVE_TEXT
,mil.DISABLE_DATE
,mil.INVENTORY_LOCATION_TYPE
,mil.PICKING_ORDER
,mil.PHYSICAL_LOCATION_CODE
,mil.LOCATION_MAXIMUM_UNITS
,mil.LOCATION_WEIGHT_UOM_CODE
,mil.MAX_WEIGHT
,mil.VOLUME_UOM_CODE
,mil.MAX_CUBIC_AREA
,mil.X_COORDINATE
,mil.Y_COORDINATE
,mil.Z_COORDINATE
,mil.INVENTORY_ACCOUNT_ID
,mil.SEGMENT1
,mil.SEGMENT2
,mil.SEGMENT3
,mil.SEGMENT4
,mil.SEGMENT5
,mil.SEGMENT6
,mil.SEGMENT7
,mil.SEGMENT8
,mil.SEGMENT9
,mil.SEGMENT10
,mil.SEGMENT11
,mil.SEGMENT12
,mil.SEGMENT13
,mil.SEGMENT14
,mil.SEGMENT15
,mil.SEGMENT16
,mil.SEGMENT17
,mil.SEGMENT18
,mil.SEGMENT19
,mil.SEGMENT20
,mil.SUMMARY_FLAG
,mil.ENABLED_FLAG
,mil.START_DATE_ACTIVE
,mil.END_DATE_ACTIVE
,mil.ATTRIBUTE_CATEGORY
,mil.ATTRIBUTE1
,mil.ATTRIBUTE2
,mil.ATTRIBUTE3
,mil.ATTRIBUTE4
,mil.ATTRIBUTE5
,mil.ATTRIBUTE6
,mil.ATTRIBUTE7
,mil.ATTRIBUTE8
,mil.ATTRIBUTE9
,mil.ATTRIBUTE10
,mil.ATTRIBUTE11
,mil.ATTRIBUTE12
,mil.ATTRIBUTE13
,mil.ATTRIBUTE14
,mil.ATTRIBUTE15
,mil.REQUEST_ID
,mil.PROGRAM_APPLICATION_ID
,mil.PROGRAM_ID
,mil.PROGRAM_UPDATE_DATE
,mil.PHYSICAL_LOCATION_ID
,mil.PICK_UOM_CODE
,mil.DIMENSION_UOM_CODE
,mil.LENGTH
,mil.WIDTH
,mil.HEIGHT
,mil.LOCATOR_STATUS
,mil.STATUS_ID
,mil.CURRENT_CUBIC_AREA
,mil.AVAILABLE_CUBIC_AREA
,mil.CURRENT_WEIGHT
,mil.AVAILABLE_WEIGHT
,mil.LOCATION_AVAILABLE_UNITS
,mil.SUGGESTED_CUBIC_AREA
,mil.SUGGESTED_WEIGHT
,mil.LOCATION_SUGGESTED_UNITS
,mil.rowid
from MTL_ITEM_LOCATIONS mil
,MTL_SECONDARY_INVENTORIES msei
,MTL_ITEM_SUB_INVENTORIES misi
where mil.organization_id(+) = msei.organization_id
and mil.organization_id=g_organization_id ---bug8425620 8665549
and mil.subinventory_code(+) = msei.secondary_inventory_name
and NVL(msei.disable_date, sysdate+1) > sysdate
and NVL(mil.disable_date, sysdate+1) > sysdate
and mil.organization_id = misi.organization_id
and mil.subinventory_code = misi.secondary_inventory
and misi.inventory_item_id = g_inventory_item_id
';
select mil.subinventory_code subinventory_code
,mil.inventory_location_id locator_id
,mil.organization_id organization_id
,mil.project_id project_id
,mil.task_id task_id
,g_inventory_item_id inventory_item_id
,mil.location_current_units location_current_units
,mil.inventory_item_id locator_inventory_item_id
,mil.empty_flag empty_flag
,mil.mixed_items_flag mixed_items_flag
,mil.LAST_UPDATE_DATE
,mil.LAST_UPDATED_BY
,mil.CREATION_DATE
,mil.CREATED_BY
,mil.LAST_UPDATE_LOGIN
,mil.DESCRIPTION
,mil.DESCRIPTIVE_TEXT
,mil.DISABLE_DATE
,mil.INVENTORY_LOCATION_TYPE
,mil.PICKING_ORDER
,mil.PHYSICAL_LOCATION_CODE
,mil.LOCATION_MAXIMUM_UNITS
,mil.LOCATION_WEIGHT_UOM_CODE
,mil.MAX_WEIGHT
,mil.VOLUME_UOM_CODE
,mil.MAX_CUBIC_AREA
,mil.X_COORDINATE
,mil.Y_COORDINATE
,mil.Z_COORDINATE
,mil.INVENTORY_ACCOUNT_ID
,mil.SEGMENT1
,mil.SEGMENT2
,mil.SEGMENT3
,mil.SEGMENT4
,mil.SEGMENT5
,mil.SEGMENT6
,mil.SEGMENT7
,mil.SEGMENT8
,mil.SEGMENT9
,mil.SEGMENT10
,mil.SEGMENT11
,mil.SEGMENT12
,mil.SEGMENT13
,mil.SEGMENT14
,mil.SEGMENT15
,mil.SEGMENT16
,mil.SEGMENT17
,mil.SEGMENT18
,mil.SEGMENT19
,mil.SEGMENT20
,mil.SUMMARY_FLAG
,mil.ENABLED_FLAG
,mil.START_DATE_ACTIVE
,mil.END_DATE_ACTIVE
,mil.ATTRIBUTE_CATEGORY
,mil.ATTRIBUTE1
,mil.ATTRIBUTE2
,mil.ATTRIBUTE3
,mil.ATTRIBUTE4
,mil.ATTRIBUTE5
,mil.ATTRIBUTE6
,mil.ATTRIBUTE7
,mil.ATTRIBUTE8
,mil.ATTRIBUTE9
,mil.ATTRIBUTE10
,mil.ATTRIBUTE11
,mil.ATTRIBUTE12
,mil.ATTRIBUTE13
,mil.ATTRIBUTE14
,mil.ATTRIBUTE15
,mil.REQUEST_ID
,mil.PROGRAM_APPLICATION_ID
,mil.PROGRAM_ID
,mil.PROGRAM_UPDATE_DATE
,mil.PHYSICAL_LOCATION_ID
,mil.PICK_UOM_CODE
,mil.DIMENSION_UOM_CODE
,mil.LENGTH
,mil.WIDTH
,mil.HEIGHT
,mil.LOCATOR_STATUS
,mil.STATUS_ID
,mil.CURRENT_CUBIC_AREA
,mil.AVAILABLE_CUBIC_AREA
,mil.CURRENT_WEIGHT
,mil.AVAILABLE_WEIGHT
,mil.LOCATION_AVAILABLE_UNITS
,mil.SUGGESTED_CUBIC_AREA
,mil.SUGGESTED_WEIGHT
,mil.LOCATION_SUGGESTED_UNITS
,mil.rowid
from MTL_ITEM_LOCATIONS mil
,MTL_SECONDARY_INVENTORIES msei
,MTL_ITEM_SUB_INVENTORIES misi
,MTL_SECONDARY_LOCATORS msl
where mil.organization_id = msei.organization_id
and mil.organization_id=g_organization_id ---bug8425620 8665549
and mil.subinventory_code = msei.secondary_inventory_name
and NVL(msei.disable_date, sysdate+1) > sysdate
and NVL(mil.disable_date, sysdate+1) > sysdate
and mil.organization_id = misi.organization_id
and mil.subinventory_code = misi.secondary_inventory
and misi.inventory_item_id = g_inventory_item_id
and mil.organization_id = msl.organization_id
and mil.inventory_location_id = msl.secondary_locator
and msl.inventory_item_Id = g_inventory_item_id
';
select x.organization_id
,x.inventory_item_id
,x.subinventory_code
,x.locator_id
from (
-- subs not restricted and locator controlled
select msi.ORGANIZATION_ID ORGANIZATION_ID
,msi.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
,msei.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE
,mil.INVENTORY_LOCATION_ID LOCATOR_ID
,mil.PROJECT_ID PROJECT_ID
,mil.TASK_ID TASK_ID
from MTL_ITEM_LOCATIONS mil
,MTL_SECONDARY_INVENTORIES msei
,MTL_PARAMETERS mp
,MTL_SYSTEM_ITEMS msi
where nvl(msi.RESTRICT_SUBINVENTORIES_CODE,2) = 2
and nvl(msi.RESTRICT_LOCATORS_CODE,2) = 2
and mp.ORGANIZATION_ID = msi.ORGANIZATION_ID
and msei.ORGANIZATION_ID = msi.ORGANIZATION_ID
and nvl(msei.DISABLE_DATE,sysdate+1) > sysdate
and decode(mp.STOCK_LOCATOR_CONTROL_CODE,
4,decode(msei.LOCATOR_TYPE,
5,nvl(msi.LOCATION_CONTROL_CODE,1),
nvl(msei.LOCATOR_TYPE,1)),
mp.STOCK_LOCATOR_CONTROL_CODE) > 1
and mil.INVENTORY_LOCATION_ID > 0 -- force U1 to be used
and mil.ORGANIZATION_ID = msei.ORGANIZATION_ID
and mil.SUBINVENTORY_CODE = msei.SECONDARY_INVENTORY_NAME
and nvl(mil.DISABLE_DATE,sysdate+1) > sysdate
union all
-- subs restricted and locator controlled
select msi.ORGANIZATION_ID
,msi.INVENTORY_ITEM_ID
,misi.SECONDARY_INVENTORY
,mil.INVENTORY_LOCATION_ID
,mil.PROJECT_ID
,mil.TASK_ID
from MTL_ITEM_LOCATIONS mil
,MTL_SECONDARY_INVENTORIES msei
,MTL_ITEM_SUB_INVENTORIES misi
,MTL_PARAMETERS mp
,MTL_SYSTEM_ITEMS msi
where nvl(msi.RESTRICT_SUBINVENTORIES_CODE,2) = 1
and nvl(msi.RESTRICT_LOCATORS_CODE,2) = 2
and mp.ORGANIZATION_ID = msi.ORGANIZATION_ID
and misi.ORGANIZATION_ID = msi.ORGANIZATION_ID
and misi.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
and msei.ORGANIZATION_ID = misi.ORGANIZATION_ID
and msei.SECONDARY_INVENTORY_NAME = misi.SECONDARY_INVENTORY
and nvl(msei.DISABLE_DATE,sysdate+1) > sysdate
and decode(mp.STOCK_LOCATOR_CONTROL_CODE,
4,decode(msei.LOCATOR_TYPE,
5,nvl(msi.LOCATION_CONTROL_CODE,1),
nvl(msei.LOCATOR_TYPE,1)),
mp.STOCK_LOCATOR_CONTROL_CODE) > 1
and mil.INVENTORY_LOCATION_ID > 0 -- force U1 to be used
and mil.ORGANIZATION_ID = misi.ORGANIZATION_ID
and mil.SUBINVENTORY_CODE = misi.SECONDARY_INVENTORY
and nvl(mil.DISABLE_DATE,sysdate+1) > sysdate
union all
-- locators restricted
select msi.ORGANIZATION_ID
,msi.INVENTORY_ITEM_ID
,misi.SECONDARY_INVENTORY
,msl.SECONDARY_LOCATOR
,mil.PROJECT_ID
,mil.TASK_ID
from MTL_ITEM_LOCATIONS mil
,MTL_SECONDARY_LOCATORS msl
,MTL_SECONDARY_INVENTORIES msei
,MTL_ITEM_SUB_INVENTORIES misi
,MTL_PARAMETERS mp
,MTL_SYSTEM_ITEMS msi
where nvl(msi.RESTRICT_SUBINVENTORIES_CODE,2) = 1
and nvl(msi.RESTRICT_LOCATORS_CODE,2) = 1
and mp.ORGANIZATION_ID = msi.ORGANIZATION_ID
and misi.ORGANIZATION_ID = msi.ORGANIZATION_ID
and misi.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
and msei.ORGANIZATION_ID = misi.ORGANIZATION_ID
and msei.SECONDARY_INVENTORY_NAME = misi.SECONDARY_INVENTORY
and nvl(msei.DISABLE_DATE,sysdate+1) > sysdate
and msl.ORGANIZATION_ID = misi.ORGANIZATION_ID
and msl.INVENTORY_ITEM_ID = misi.INVENTORY_ITEM_ID
and msl.SUBINVENTORY_CODE = misi.SECONDARY_INVENTORY
and mil.ORGANIZATION_ID = msl.ORGANIZATION_ID
and mil.INVENTORY_LOCATION_ID = msl.SECONDARY_LOCATOR
and nvl(mil.DISABLE_DATE,sysdate+1) > sysdate
) x
group by x.organization_id
, x.inventory_item_id
, x.subinventory_code
, x.locator_id
';
select msn.current_organization_id organization_id
,msn.inventory_item_id
,msn.revision
,msn.lot_number
,lot.expiration_date lot_expiration_date
,msn.current_subinventory_code subinventory_code
,msn.current_locator_id locator_id
,msn.cost_group_id
,msn.status_id --added status_id
,msn.serial_number
,msn.initialization_date date_received
,1 primary_quantity
,null secondary_quantity -- new
,lot.grade_code grade_code -- new
,sub.reservable_type
,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
,WMS_Rule_PVT.GetConversionRate(
nvl(loc.pick_uom_code, sub.pick_uom_code)
,msn.current_organization_id
,msn.inventory_item_id) conversion_rate
,msn.lpn_id lpn_id
,loc.project_id project_id
,loc.task_id task_id
,NULL locator_inventory_item_id
,NULL empty_flag
,NULL location_current_units
from mtl_serial_numbers msn
,mtl_secondary_inventories sub
,mtl_item_locations loc
,mtl_lot_numbers lot
where msn.current_status = 3
and decode(g_unit_number, ''-9999'', ''a'', ''-7777'', nvl(msn.end_item_unit_number, ''-7777''), msn.end_item_unit_number) =
decode(g_unit_number, ''-9999'', ''a'', g_unit_number)
and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
--and (g_detail_serial IN ( 1,2)
and ( g_detail_any_serial = 2 or (g_detail_any_serial = 1
and g_from_serial_number <= msn.serial_number
and lengthb(g_from_serial_number) = lengthb(msn.serial_number)
and g_to_serial_number >= msn.serial_number
and lengthb(g_to_serial_number) = lengthb(msn.serial_number))
or ( g_from_serial_number is null or g_to_serial_number is null)
)
and sub.organization_id = msn.current_organization_id
and sub.secondary_inventory_name = msn.current_subinventory_code
and loc.organization_id (+)= msn.current_organization_id
and loc.inventory_location_id (+)= msn.current_locator_id
and lot.organization_id (+)= msn.current_organization_id
and lot.inventory_Item_id (+)= msn.inventory_item_id
and lot.lot_number (+)= msn.lot_number
';
select msn.current_organization_id organization_id
,msn.inventory_item_id
,msn.revision
,msn.lot_number
,lot.expiration_date lot_expiration_date
,msn.current_subinventory_code subinventory_code
,msn.current_locator_id locator_id
,msn.cost_group_id
,msn.status_id --added status_id
,msn.serial_number
,msn.initialization_date date_received
,1 primary_quantity
,null secondary_quantity -- new
,lot.grade_code grade_code -- new
,sub.reservable_type
,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
,WMS_Rule_PVT.GetConversionRate(
nvl(loc.pick_uom_code, sub.pick_uom_code)
,msn.current_organization_id
,msn.inventory_item_id) conversion_rate
,msn.lpn_id lpn_id
,loc.project_id project_id
,loc.task_id task_id
,NULL locator_inventory_item_id
,NULL empty_flag
,NULL location_current_units
from mtl_serial_numbers msn
,mtl_secondary_inventories sub
,mtl_item_locations loc
,mtl_lot_numbers lot
where msn.current_status = 3
and decode(g_unit_number, ''-9999'', ''a'', ''-7777'', nvl(msn.end_item_unit_number, ''-7777''), msn.end_item_unit_number) =
decode(g_unit_number, ''-9999'', ''a'', g_unit_number)
and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
and (g_detail_serial = 4
OR(g_detail_any_serial = 1
OR (g_from_serial_number <= msn.serial_number
AND lengthb(g_from_serial_number) = lengthb(msn.serial_number)
AND g_to_serial_number >= msn.serial_number
AND lengthb(g_to_serial_number) = lengthb(msn.serial_number)
)))
and sub.organization_id = msn.current_organization_id
and sub.secondary_inventory_name = msn.current_subinventory_code
and loc.organization_id (+)= msn.current_organization_id
and loc.inventory_location_id (+)= msn.current_locator_id
and lot.organization_id (+)= msn.current_organization_id
and lot.inventory_Item_id (+)= msn.inventory_item_id
and lot.lot_number (+)= msn.lot_number
';
select msn.current_organization_id organization_id
,msn.inventory_item_id
,msn.revision
,msn.lot_number
,lot.expiration_date lot_expiration_date
,msn.current_subinventory_code subinventory_code
,msn.current_locator_id locator_id
,msn.cost_group_id
,msn.status_id --added status_id
,msn.serial_number
,msn.initialization_date date_received
,1 primary_quantity
,null secondary_quantity -- new
,lot.grade_code grade_code -- new
,sub.reservable_type
,nvl(loc.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,nvl(loc.pick_uom_code, sub.pick_uom_code) uom_code
,WMS_Rule_PVT.GetConversionRate(
nvl(loc.pick_uom_code, sub.pick_uom_code)
,msn.current_organization_id
,msn.inventory_item_id) conversion_rate
,msn.lpn_id lpn_id
,loc.project_id project_id
,loc.task_id task_id
,NULL locator_inventory_item_id
,NULL empty_flag
,NULL location_current_units
from mtl_serial_numbers msn
,mtl_secondary_inventories sub
,mtl_item_locations loc
,mtl_lot_numbers lot
where msn.current_status = 3
and decode(g_unit_number, ''-9999'', ''a'', ''-7777'', nvl(msn.end_item_unit_number, ''-7777''), msn.end_item_unit_number) =
decode(g_unit_number, ''-9999'', ''a'', g_unit_number)
and (msn.group_mark_id IS NULL or msn.group_mark_id = -1)
and (g_detail_serial = 3
OR(g_detail_any_serial = 1
OR (g_from_serial_number <= msn.serial_number
AND lengthb(g_from_serial_number) = lengthb(msn.serial_number)
AND g_to_serial_number >= msn.serial_number
AND lengthb(g_to_serial_number) = lengthb(msn.serial_number)
)))
and sub.organization_id = msn.current_organization_id
and sub.secondary_inventory_name = msn.current_subinventory_code
and loc.organization_id (+)= msn.current_organization_id
and loc.inventory_location_id (+)= msn.current_locator_id
and lot.organization_id (+)= msn.current_organization_id
and lot.inventory_Item_id (+)= msn.inventory_item_id
and lot.lot_number (+)= msn.lot_number
and inv_detail_util_pvt.is_serial_trx_allowed(
g_transaction_type_id
,msn.current_organization_id
,msn.inventory_item_id
,msn.status_id) = ''Y'' ';
select x.organization_id
,x.inventory_item_id
,x.revision
,x.lot_number
,lot.expiration_date lot_expiration_date
,x.subinventory_code
,sub.reservable_type
,nvl(x.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,x.locator_id
,x.cost_group_id
,x.status_id --added status_id
,x.date_received date_received
,x.primary_quantity primary_quantity
,x.secondary_quantity secondary_quantity -- new
,lot.grade_code grade_code -- new
,x.lpn_id lpn_id
,x.project_id project_id
,x.task_id task_id
from
(SELECT
moq.organization_id
,moq.inventory_item_id
,moq.revision
,moq.lot_number
,moq.subinventory_code
,moq.locator_id
,moq.cost_group_id
,moq.status_id --added status_id
,mils.reservable_type -- Bug 6719290
,min(NVL(moq.orig_date_received,
moq.date_received)) date_received
,sum(moq.primary_transaction_quantity) primary_quantity
,sum(moq.secondary_transaction_quantity) secondary_quantity -- new
,moq.lpn_id lpn_id
,mils.project_id project_id
,mils.task_id task_id
FROM
mtl_onhand_quantities_detail moq
, mtl_item_locations mils
WHERE
moq.organization_id = g_organization_id
AND moq.inventory_item_id = g_inventory_item_id
AND moq.organization_id = mils.organization_id
AND moq.subinventory_code = mils.subinventory_code
AND moq.locator_id = mils.inventory_location_id
AND moq.lpn_id IS NOT NULL
AND NOT EXISTS(
select lpn_id
from wms_license_plate_numbers wlpn1
where wlpn1.parent_lpn_id = moq.lpn_id)
AND
1 = (select count(distinct(moq1.inventory_item_id))
from mtl_onhand_quantities_detail moq1
where moq1.organization_id = moq.organization_id
and moq1.subinventory_code = moq.subinventory_code
and moq1.locator_id = moq.locator_id
and moq1.lpn_id = moq.lpn_id)
GROUP BY
moq.organization_id, moq.inventory_item_id
--,moq.date_received --bug 6648984
,NVL(moq.orig_date_received, moq.date_received) --13518247
,moq.revision, moq.lot_number
,moq.subinventory_code, moq.locator_id --added status_id
,moq.cost_group_id,moq.status_id,mils.reservable_type,moq.lpn_id -- Bug 6719290
,mils.project_id, mils.task_id
HAVING
sum(moq.primary_transaction_quantity) > 0 -- high volume project 8546026
) x
,mtl_secondary_inventories sub
,mtl_lot_numbers lot
where
-- x.primary_quantity > 0 and -- high volume project 8546026
x.organization_id = sub.organization_id
and x.subinventory_code = sub.secondary_inventory_name
and x.organization_id = lot.organization_id (+)
and x.inventory_item_id = lot.inventory_item_id (+)
and x.lot_number = lot.lot_number (+)
';
select x.organization_id
,x.inventory_item_id
,x.revision
,x.lot_number
,lot.expiration_date lot_expiration_date
,x.subinventory_code
,sub.reservable_type
,nvl(x.reservable_type,1) locreservable -- Bug 6719290
,nvl(lot.reservable_type,1) lotreservable -- Bug 6719290
,x.locator_id
,x.cost_group_id
,x.status_id --added status_id
,x.date_received date_received
,x.primary_quantity primary_quantity
,x.secondary_quantity secondary_quantity -- new
,lot.grade_code grade_code -- new
,x.lpn_id lpn_id
,x.project_id project_id
,x.task_id task_id
from
(SELECT
moq.organization_id
,moq.inventory_item_id
,moq.revision
,moq.lot_number
,moq.subinventory_code
,moq.locator_id
,moq.cost_group_id
,moq.status_id --added status_id
,mils.reservable_type -- Bug 6719290
,min(NVL(moq.orig_date_received,
moq.date_received)) date_received
,sum(moq.primary_transaction_quantity) primary_quantity
,sum(moq.secondary_transaction_quantity) secondary_quantity -- new
,moq.lpn_id lpn_id
,decode(mils.project_id, mils.project_id, moq.project_id) project_id
,decode(mils.task_id, mils.task_id, moq.task_id) task_id
FROM
mtl_onhand_quantities_detail moq,mtl_item_locations mils
WHERE
moq.organization_id = g_organization_id
AND moq.inventory_item_id = g_inventory_item_id
AND moq.organization_id = mils.organization_id (+)
AND moq.subinventory_code = mils.subinventory_code (+)
AND moq.locator_id = mils.inventory_location_id (+)
GROUP BY
moq.organization_id, moq.inventory_item_id
--,moq.date_received --bug 6648984
,NVL(moq.orig_date_received, moq.date_received) --13518247
,moq.revision, moq.lot_number
,moq.subinventory_code, moq.locator_id --added status_id
,moq.cost_group_id,moq.status_id, mils.reservable_type, moq.lpn_id -- Bug 6719290
,decode(mils.project_id, mils.project_id, moq.project_id)
,decode(mils.task_id, mils.task_id, moq.task_id)
HAVING
sum(moq.primary_transaction_quantity) > 0 -- high volume project 8546026
) x
,mtl_secondary_inventories sub
,mtl_lot_numbers lot
where
-- x.primary_quantity > 0 and -- high volume project 8546026
x.organization_id = sub.organization_id
and x.subinventory_code = sub.secondary_inventory_name
and x.organization_id = lot.organization_id (+)
and x.inventory_item_id = lot.inventory_item_id (+)
and x.lot_number = lot.lot_number (+)
';
g_base_select LONG;
g_rule_select LONG;
g_rule_select_serial LONG;
g_rule_select_serial_new LONG; -- Added for Bug 13448337
SELECT COUNT(*)
INTO l_lpn_used
FROM mtl_reservations mr
WHERE mr.organization_id = p_organization_id
AND mr.demand_source_type_id = 9 -- Cycle Count Reservation
AND mr.lpn_id = p_lpn_id;
SELECT count( DISTINCT p.object_id)
INTO l_serial_object_used
FROM wms_selection_criteria_txn wsc,
wms_strategies_b wsb,
wms_strategy_members wsm,
wms_rules_b wrb,
wms_restrictions r,
wms_sort_criteria s,
wms_rule_consistencies c,
wms_parameters_b p
WHERE wsc.rule_type_code = 2
AND wsc.enabled_flag = 1
AND wsc.return_type_code = 'S'
AND wsc.from_organization_id = p_organization_id
AND wsc.return_type_id = wsb.strategy_id
AND wsb.strategy_id = wsm.strategy_id
AND wsm.rule_id = wrb.rule_id
AND (
( wrb.rule_id = r.rule_id AND ((r.parameter_id = p.parameter_id ) OR (r.operand_parameter_id = p.parameter_id)))
OR (wrb.rule_id = s.rule_id AND s.parameter_id = p.parameter_id) OR (wrb.rule_id = c.rule_id AND c.parameter_id = p.parameter_id) )
AND wrb.organization_id IN (p_organization_id, -1)
AND p.object_id = 26; --- 26 is Serial object
g_base_select := NULL;
g_rule_select := NULL;
g_rule_select_serial := NULL;
g_rule_select_serial_new := NULL; -- Added for Bug 13448337
UPDATE wms_rule_list_package
SET package_name_count = 3;
SELECT conversion_rate
, uom_class
FROM mtl_uom_conversions
WHERE uom_code = p_uom_code
AND inventory_item_id IN (p_inventory_item_id, 0)
AND NVL(disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE)
ORDER BY inventory_item_id DESC;
SELECT uom_class
FROM mtl_units_of_measure muom, mtl_system_items msi
WHERE msi.organization_id = p_organization_id
AND msi.inventory_item_id = p_inventory_item_id
AND muom.uom_code = msi.primary_uom_code;
SELECT conversion_rate
FROM mtl_uom_class_conversions
WHERE from_uom_class = l_from_class
AND to_uom_class = l_to_class
AND inventory_item_id IN (p_inventory_item_id, 0)
AND NVL(disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE)
ORDER BY inventory_item_id DESC;
SELECT wdo1.table_alias
, wdo2.table_name
, wdo2.table_alias
FROM wms_db_objects wdo1, wms_db_objects wdo2
WHERE wdo1.db_object_id = 1
AND wdo2.db_object_id = 2;
g_base_select := l_type_dependent_alias
|| '.REVISION'
|| g_line_feed
|| ','
|| l_type_dependent_alias
|| '.LOT_NUMBER'
|| g_line_feed
|| ','
|| l_type_dependent_alias
|| '.LOT_EXPIRATION_DATE'
|| g_line_feed
|| ','
|| g_base_table_alias
|| '.SUBINVENTORY_CODE'
|| g_line_feed
|| ','
|| g_base_table_alias
|| '.LOCATOR_ID'
|| g_line_feed;
g_base_select := g_base_table_alias
|| '.SUBINVENTORY_CODE'
|| g_line_feed || ','
|| g_base_table_alias
|| '.LOCATOR_ID'
|| g_line_feed;
g_base_select := g_base_select
|| ','
|| g_base_table_alias
|| '.PROJECT_ID'
|| g_line_feed;
g_base_select := g_base_select
|| ','
|| g_base_table_alias
|| '.TASK_ID'
|| g_line_feed;
g_base_select := g_base_select
|| ','
|| g_base_table_alias
|| '.COST_GROUP_ID'
|| g_line_feed;
g_base_select := g_base_select
|| ','
|| g_base_table_alias
|| '.UOM_CODE'
|| g_line_feed;
g_base_select := g_base_select
|| ','
|| g_base_table_alias
|| '.LPN_ID'
|| g_line_feed;
g_base_select := g_base_select
|| ',decode(g_lpn_id, -9999, NULL, g_lpn_id) LPN_ID'
|| g_line_feed;
* g_base_select := g_base_select ||',NULL cost_group_id'
* || g_line_feed;
* g_base_select := g_base_select ||',NULL uom_code'
* || g_line_feed;
|| 'SELECT x.organization_id organization_id '
|| g_line_feed
|| ' ,x.inventory_item_id inventory_item_id '
|| g_line_feed
|| ' ,x.revision revision '
|| g_line_feed
|| ' ,x.lot_number lot_number '
|| g_line_feed
|| ' ,x.lot_expiration_date lot_expiration_date '
|| g_line_feed
|| ' ,x.subinventory_code subinventory_code '
|| g_line_feed
|| ' ,x.locator_id locator_id '
|| g_line_feed
|| ' ,x.cost_group_id cost_group_id '
|| g_line_feed
|| ' ,x.status_id status_id ' --added status_id
|| g_line_feed
|| ' ,NULL serial_number '
|| g_line_feed
|| ' ,x.lpn_id lpn_id '
|| g_line_feed
|| ' ,x.project_id project_id '
|| g_line_feed
|| ' ,x.task_id task_id '
|| g_line_feed
|| ' ,x.date_received date_received '
|| g_line_feed
|| ' ,x.primary_quantity primary_quantity '
|| g_line_feed
|| ' ,x.secondary_quantity secondary_quantity ' -- new
|| g_line_feed -- new
|| ' ,x.grade_code grade_code ' -- new
|| g_line_feed -- new
|| ' ,x.reservable_type reservable_type '
|| g_line_feed
|| ' ,x.locreservable locreservable ' -- Bug 6719290 Start
|| g_line_feed
|| ' ,x.lotreservable lotreservable '
|| g_line_feed -- Bug 6719290 End
|| ' ,NVL(loc.pick_uom_code,sub.pick_uom_code) uom_code'
|| g_line_feed
|| ' ,WMS_Rule_PVT.GetConversionRate( '
|| g_line_feed
|| ' NVL(loc.pick_uom_code, sub.pick_uom_code) '
|| g_line_feed
|| ' ,x.organization_id '
|| g_line_feed
|| ' ,x.inventory_item_id) conversion_rate '
|| g_line_feed
|| ' ,NULL locator_inventory_item_id '
|| g_line_feed
|| ' ,NULL empty_flag '
|| g_line_feed
|| ' ,NULL location_current_units '
|| g_line_feed
|| 'FROM ('
|| g_pick_base
--extra line feed? || g_line_feed
|| ' ) x '
|| g_line_feed
|| ' ,mtl_secondary_inventories sub '
|| g_line_feed
|| ' ,mtl_item_locations loc '
|| g_line_feed
|| 'WHERE x.organization_id = loc.organization_id (+) '
|| g_line_feed
|| ' AND x.locator_id = loc.inventory_location_id (+)'
|| g_line_feed
|| ' AND sub.organization_id = x.organization_id '
|| g_line_feed
|| ' AND sub.secondary_inventory_name = x.subinventory_code '
|| g_line_feed; /*
select lpn_id
from wms_license_plate_numbers wlpn1
where wlpn1.parent_lpn_id = msn.lpn_id)
and
1 = (select count(distinct(moq1.inventory_item_id))
from mtl_onhand_quantities_detail moq1
where moq1.organization_id = msn.current_organization_id
and moq1.subinventory_code = msn.current_subinventory_code
and moq1.locator_id = msn.current_locator_id
and moq1.lpn_id = msn.lpn_id)
';
select lpn_id
from wms_license_plate_numbers wlpn1
where wlpn1.parent_lpn_id = msn.lpn_id)
and
1 = (select count(distinct(moq1.inventory_item_id))
from mtl_onhand_quantities_detail moq1
where moq1.organization_id = msn.current_organization_id
and moq1.subinventory_code = msn.current_subinventory_code
and moq1.locator_id = msn.current_locator_id
and moq1.lpn_id = msn.lpn_id)
';
select lpn_id
from wms_license_plate_numbers wlpn1
where wlpn1.parent_lpn_id = msn.lpn_id)
and
1 = (select count(distinct(moq1.inventory_item_id))
from mtl_onhand_quantities_detail moq1
where moq1.organization_id = msn.current_organization_id
and moq1.subinventory_code = msn.current_subinventory_code
and moq1.locator_id = msn.current_locator_id
and moq1.lpn_id = msn.lpn_id)
';
SELECT wdo.db_object_id
, wdo.table_name
, wdo.table_alias
, wdo.context_dependent_flag
FROM wms_db_objects wdo
, wms_parameters_b wpb
, (SELECT wsc.parameter_id
FROM wms_sort_criteria wsc
WHERE wsc.rule_id = p_rule_id
UNION
SELECT wr.parameter_id
FROM wms_restrictions wr
WHERE wr.rule_id = p_rule_id
UNION
SELECT wr.operand_parameter_id
FROM wms_restrictions wr
WHERE wr.rule_id = p_rule_id
AND wr.operand_type_code = 4
UNION
SELECT wrc.parameter_id
FROM wms_rule_consistencies wrc
WHERE wrc.rule_id = p_rule_id) x
WHERE wpb.parameter_id = x.parameter_id
AND wpb.db_object_ref_type_code = 1
AND wdo.db_object_id = wpb.db_object_id
AND wdo.db_object_id NOT IN (1, 2)
UNION
-- 2. all parents of single referenced DB objects
SELECT wdo.db_object_id
, wdo.table_name
, wdo.table_alias
, wdo.context_dependent_flag
FROM wms_db_objects wdo
, (SELECT wdop.parent_db_object_id
FROM wms_db_objects_parents wdop
WHERE wdop.type_code = p_type_code
CONNECT BY wdop.db_object_id = PRIOR wdop.parent_db_object_id
START WITH wdop.db_object_id IN (SELECT wdod.db_object_id
FROM wms_db_objects wdod
, wms_parameters_b wpbd
, (SELECT wscd.parameter_id
FROM wms_sort_criteria wscd
WHERE wscd.rule_id = p_rule_id
UNION
SELECT wrd.parameter_id
FROM wms_restrictions wrd
WHERE wrd.rule_id = p_rule_id
UNION
SELECT wrd.operand_parameter_id
FROM wms_restrictions wrd
WHERE wrd.rule_id = p_rule_id
AND wrd.operand_type_code = 4
UNION
SELECT wrcd.parameter_id
FROM wms_rule_consistencies wrcd
WHERE wrcd.rule_id = p_rule_id) xd
WHERE wpbd.parameter_id = xd.parameter_id
AND wpbd.db_object_ref_type_code = 1
AND wdod.db_object_id = wpbd.db_object_id)) x
WHERE wdo.db_object_id = x.parent_db_object_id
AND wdo.db_object_id NOT IN (1, 2)
-- 3. all multi referenced DB objects
UNION
SELECT wdo.db_object_id
, wdo.table_name
, NVL(wdorm.table_alias, wdo.table_alias)
, wdo.context_dependent_flag
FROM wms_db_objects wdo
, wms_db_obj_ref_members wdorm
, wms_db_object_references wdor
, wms_parameters_b wpb
, (SELECT wsc.parameter_id
FROM wms_sort_criteria wsc
WHERE wsc.rule_id = p_rule_id
UNION
SELECT wr.parameter_id
FROM wms_restrictions wr
WHERE wr.rule_id = p_rule_id
UNION
SELECT wr.operand_parameter_id
FROM wms_restrictions wr
WHERE wr.rule_id = p_rule_id
AND wr.operand_type_code = 4
UNION
SELECT wrc.parameter_id
FROM wms_rule_consistencies wrc
WHERE wrc.rule_id = p_rule_id) x
WHERE wpb.parameter_id = x.parameter_id
AND wpb.db_object_ref_type_code = 2
AND wdor.db_object_reference_id = wpb.db_object_reference_id
AND wdorm.db_object_reference_id = wdor.db_object_reference_id
AND wdo.db_object_id = wdorm.db_object_id
AND wdo.db_object_id NOT IN (1, 2)
UNION
-- 4. all parents of multi referenced DB objects
SELECT wdo.db_object_id
, wdo.table_name
, wdo.table_alias
, wdo.context_dependent_flag
FROM wms_db_objects wdo
, (SELECT wdop.parent_db_object_id
FROM wms_db_objects_parents wdop
WHERE wdop.type_code = p_type_code
CONNECT BY wdop.db_object_id = PRIOR wdop.parent_db_object_id
START WITH wdop.db_object_id IN (SELECT wdoi.db_object_id
FROM wms_db_objects wdoi
, wms_db_obj_ref_members wdormi
, wms_db_object_references wdori
, wms_parameters_b wpbi
, (SELECT wsc.parameter_id
FROM wms_sort_criteria wsc
WHERE wsc.rule_id = p_rule_id
UNION
SELECT wr.parameter_id
FROM wms_restrictions wr
WHERE wr.rule_id = p_rule_id
UNION
SELECT wr.operand_parameter_id
FROM wms_restrictions wr
WHERE wr.rule_id = p_rule_id
AND wr.operand_type_code = 4
UNION
SELECT wrc.parameter_id
FROM wms_rule_consistencies wrc
WHERE wrc.rule_id = p_rule_id) xi
WHERE wpbi.parameter_id = xi.parameter_id
AND wpbi.db_object_ref_type_code = 2
AND wdori.db_object_reference_id = wpbi.db_object_reference_id
AND wdormi.db_object_reference_id = wdori.db_object_reference_id
AND wdoi.db_object_id = wdormi.db_object_id)) x
WHERE wdo.db_object_id = x.parent_db_object_id
AND wdo.db_object_id NOT IN (1, 2)
ORDER BY 1;
SELECT wpb.parameter_type_code
, wpb.column_name
, wpb.expression
, wpb.data_type_code
, wpbp.parameter_type_code
, wpbp.column_name
, wpbp.expression
, wpbp.data_type_code
, wdop.table_alias -- alias n.a. for multi object based parameters
FROM wms_db_objects wdop, wms_parameters_b wpbp, wms_parameters_b wpb, wms_db_object_joins wdoj
WHERE wdoj.db_object_id = l_db_object_id
AND wdoj.type_code = p_type_code
AND wpb.parameter_id = wdoj.parameter_id
AND wpbp.parameter_id = wdoj.parent_parameter_id
AND wdop.db_object_id(+) = wpbp.db_object_id;
SELECT wpb.parameter_type_code
, wpb.column_name
, wpb.expression
, wpb.data_type_code
, wdo.table_alias -- alias n.a. for multi object based parameters
FROM wms_db_objects wdo, wms_parameters_b wpb, wms_rules_b wrb
WHERE wrb.rule_id = p_rule_id
AND wpb.parameter_id = wrb.qty_function_parameter_id
AND wdo.db_object_id(+) = wpb.db_object_id;
SELECT wpbl.parameter_type_code
, wpbl.column_name
, wpbl.expression
, wpbl.data_type_code
, wdol.table_alias -- alias n.a. for multi object based parameters
, DECODE(
wr.operator_code
, 1, '>'
, 2, '<'
, 3, '='
, 4, '<>'
, 5, '>='
, 6, '<='
, 7, 'IN'
, 8, 'NOT IN'
, 9, 'LIKE'
, 10, 'NOT LIKE'
, 11, 'IS NULL'
, 12, 'IS NOT NULL'
, NULL
)
, wr.operand_type_code
, wr.operand_constant_number
, wr.operand_constant_character
, wr.operand_constant_date
, wr.operand_expression
, wr.operand_flex_value_set_id
, DECODE(wr.logical_operator_code, 1, 'and', 2, 'or', NULL)
, wr.bracket_open
, wr.bracket_close
, wpbr.parameter_type_code
, wpbr.column_name
, wpbr.expression
, DECODE(
wr.operand_type_code
, 4, wpbr.data_type_code
, 5, wpbl.data_type_code
, 6, DECODE(ffvs.format_type, 'N', 1, 'C', 2, 3)
, 7, NULL
, wr.operand_type_code
)
, wdor.table_alias -- alias n.a. for multi object based parameters
, ffvs.validation_type -- only 'independent' and 'table' are supported
, ffvt.id_column_name
, ffvt.value_column_name
, ffvt.application_table_name
, ffvt.additional_where_clause
, wr.rule_id
, wr.sequence_number
FROM fnd_flex_validation_tables ffvt
, fnd_flex_value_sets ffvs
, wms_db_objects wdor
, wms_parameters_b wpbr
, wms_db_objects wdol
, wms_parameters_b wpbl
, wms_restrictions wr
WHERE wr.rule_id = p_rule_id
AND wpbl.parameter_id = wr.parameter_id
AND wdol.db_object_id(+) = wpbl.db_object_id
AND wpbr.parameter_id(+) = wr.operand_parameter_id
AND wdor.db_object_id(+) = wpbr.db_object_id
AND ffvs.flex_value_set_id(+) = wr.operand_flex_value_set_id
AND ffvt.flex_value_set_id(+) = wr.operand_flex_value_set_id
ORDER BY wr.rule_id, wr.sequence_number -- order is important
;
SELECT wpb.parameter_type_code
, wpb.column_name
, wpb.expression
, wpb.data_type_code
, wdo.table_alias -- alias n.a. for multi object based parameters
, wms_parameter_pvt.getflexdatatypecode(
wpb.data_type_code
, wpb.db_object_ref_type_code
, wpb.parameter_type_code
, wpb.flexfield_usage_code
, wpb.flexfield_application_id
, wpb.flexfield_name
, wpb.column_name
)
, DECODE(wsc.order_code, 1, 'asc', 2, 'desc', NULL)
, wsc.rule_id
, wsc.sequence_number
, wpb.object_id -- Added for Bug 13448337
, wsc.parameter_id -- Added for Bug 13448337
FROM wms_db_objects wdo, wms_parameters_b wpb, wms_sort_criteria wsc
WHERE wsc.rule_id = p_rule_id
AND wpb.parameter_id = wsc.parameter_id
AND wdo.db_object_id(+) = wpb.db_object_id
ORDER BY wsc.rule_id, wsc.sequence_number;
SELECT wpb.parameter_type_code
, wpb.column_name
, wpb.expression
, wdo.table_alias
FROM wms_rule_consistencies wrc, wms_parameters_b wpb, wms_db_objects wdo
WHERE wrc.rule_id = p_rule_id
AND wpb.parameter_id = wrc.parameter_id
AND wdo.db_object_id(+) = wpb.db_object_id;
g_rule_select := 'NULL serial_number
,nvl(base.primary_quantity,0)
,nvl(base.secondary_quantity,0) -- new
,base.grade_code, -- new
,NULL consist_string,
,NULL order_by_string';
g_rule_select := 'NULL serial_number
,nvl(WMS_Parameter_PVT.GetAvailableUnitCapacity
( mptdtv.TO_ORGANIZATION_ID
,base.SUBINVENTORY_CODE
,base.LOCATOR_ID
,mil.LOCATION_MAXIMUM_UNITS
)
,0)
,NULL consist_string
,NULL order_by_string';
g_rule_select := g_rule_select || ',' || g_base_table_alias || '.SERIAL_NUMBER' || g_line_feed;
g_rule_select := g_rule_select || ',NULL SERIAL_NUMBER' || g_line_feed;
g_rule_select_serial := g_rule_select_serial || ',NULL SERIAL_NUMBER' || g_line_feed;
g_rule_select := g_rule_select || ',nvl(' || l_table_alias || '.' || l_column_name || ',0)' || g_line_feed;
g_rule_select_serial := g_rule_select_serial || ',sum(nvl(' || l_table_alias || '.' || l_column_name || ',0))' || g_line_feed;
g_rule_select := g_rule_select
|| ',nvl(' || l_expression || ',0)'
|| g_line_feed;
g_rule_select_serial := g_rule_select_serial
|| ',sum(nvl(' || l_expression || ',0))'
|| g_line_feed;
g_rule_select := g_rule_select
|| ',base.primary_quantity ' || g_line_feed
|| ',base.secondary_quantity ' || g_line_feed -- new
|| ',base.grade_code ' || g_line_feed; -- new
g_rule_select_serial := g_rule_select_serial
|| ',sum(base.primary_quantity) ' || g_line_feed
|| ',sum(base.secondary_quantity) ' || g_line_feed -- new
|| ',base.grade_code ' || g_line_feed; -- new
|| '( select '
|| l_flex_column_name
|| ' from '
|| l_application_table_name
|| ' '
|| l_additional_where_clause
|| ')'
|| l_bracket_close
|| g_line_feed;
|| '( select FLEX_VALUE from '
|| 'FND_FLEX_VALUES_VL where FLEX_VALUE_SET_ID = '
|| l_operand_flex_value_set_id
|| ' and ENABLED_FLAG = ''Y'' and sysdate between nvl('
|| 'START_DATE_ACTIVE,sysdate-1) and nvl('
|| 'END_DATE_ACTIVE,sysdate+1) )'
|| l_bracket_close
|| g_line_feed;
g_rule_select := g_rule_select
|| ','
|| l_consist_string
|| ' consist_string'
|| g_line_feed;
g_rule_select_serial := g_rule_select_serial
|| ','
|| l_consist_string
|| ' consist_string'
|| g_line_feed;
g_rule_select := g_rule_select || ',NULL consist_string' || g_line_feed;
g_rule_select_serial := g_rule_select_serial || ',NULL consist_string' || g_line_feed;
g_rule_select_serial_new := g_rule_select_serial; -- Added for Bug 13448337
g_rule_select := g_rule_select
|| ','
|| l_order_by_string
|| ' order_by_string'
|| g_line_feed;
g_rule_select_serial := g_rule_select_serial
|| ','
|| l_order_by_string
|| ' order_by_string'
|| g_line_feed;
g_rule_select_serial_new := g_rule_select_serial_new
|| ','
|| l_order_by_string1
|| ' order_by_string'
|| g_line_feed;
g_rule_select_serial_new := g_rule_select_serial;
g_rule_select := g_rule_select || ',NULL order_by_string' || g_line_feed;
g_rule_select_serial := g_rule_select_serial || ',NULL order_by_string' || g_line_feed;
g_rule_select_serial_new := g_rule_select_serial_new || ',NULL order_by_string' || g_line_feed;
g_rule_select := g_rule_select || ',NULL consist_string' || g_line_feed;
g_rule_select_serial := g_rule_select_serial || ',NULL consist_string' || g_line_feed;
g_rule_select_serial_new := g_rule_select_serial_new || ',NULL consist_string' || g_line_feed; -- Added for Bug 13448337
g_rule_select := g_rule_select || ',NULL order_by_string' || g_line_feed;
g_rule_select_serial := g_rule_select_serial || ',NULL order_by_string' || g_line_feed;
g_rule_select_serial_new := g_rule_select_serial_new || ',NULL order_by_string' || g_line_feed; -- Added for Bug 13448337
|| ' ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) '
|| ' AND exists(select 1 from mtl_material_statuses where status_id = '
|| g_base_table_alias
|| '.STATUS_ID'
|| ' AND RESERVABLE_TYPE = 1)) OR '
|| '((NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) '
|| ' or '
|| g_base_table_alias
|| '.STATUS_ID IS NULL)'
|| ' and '
|| 'decode(g_subinventory_code, ''-9999'', '
|| g_base_table_alias
|| '.RESERVABLE_TYPE, 1)'
|| ' = 1))'
|| g_line_feed;
|| ' ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) '
|| ' AND exists(select 1 from mtl_material_statuses where status_id = '
|| g_base_table_alias
|| '.STATUS_ID'
|| ' AND RESERVABLE_TYPE = 1)) OR '
|| '((NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) '
|| 'or '
|| g_base_table_alias
|| '.STATUS_ID IS NULL)'
|| ' and '
|| 'decode(g_locator_id, -9999, '
|| g_base_table_alias
|| '.locreservable, 1)'
|| ' = 1))'
|| g_line_feed;
|| ' ((exists (select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not null ) '
|| ' AND exists(select 1 from mtl_material_statuses where status_id = '
|| g_base_table_alias
|| '.STATUS_ID'
|| ' AND RESERVABLE_TYPE = 1)) OR '
|| '((NOT exists(select 1 from mtl_parameters where organization_id = g_organization_id and default_status_id is not NULL) '
|| 'or '
|| g_base_table_alias
|| '.STATUS_ID IS NULL)'
|| ' and '
|| 'decode(g_lot_number, ''-9999'', '
|| 'decode (INV_Pick_Release_PVT.g_pick_nonrsv_lots,1,1,' --8719074
|| g_base_table_alias
|| '.lotreservable), 1)'
|| ' = 1))'
|| g_line_feed;
PROCEDURE rollback_capacity_update(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
) IS
l_return_status VARCHAR2(1);
l_api_name VARCHAR2(30) := 'rollback_capacity_update';
SELECT to_locator_id
, SUM(primary_quantity) quantity
FROM wms_transactions_temp
WHERE type_code = 1
AND line_type_code = 2
GROUP BY to_locator_id;
log_procedure(l_api_name, 'start', 'Start rollback_capacity_update');
log_procedure(l_api_name, 'end', 'End rollback_capacity_update');
END rollback_capacity_update;
PROCEDURE validate_and_insert(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_record_id IN NUMBER
, p_needed_quantity IN NUMBER
, p_use_pick_uom IN BOOLEAN
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_to_subinventory_code IN VARCHAR2
, p_to_locator_id IN NUMBER
, p_to_cost_group_id IN NUMBER
, p_primary_uom IN VARCHAR2
, p_transaction_uom IN VARCHAR2
, p_transaction_temp_id IN NUMBER
, p_type_code IN NUMBER
, p_rule_id IN NUMBER
, p_reservation_id IN NUMBER
, p_tree_id IN NUMBER
, p_debug_on IN BOOLEAN
, p_needed_sec_quantity IN NUMBER
, p_secondary_uom IN VARCHAR2
, p_grade_code IN VARCHAR2
, p_orig_needed_quantity IN NUMBER -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty IN NUMBER -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode IN NUMBER -- Added for 14699845 (Flexible lot allocation)
, x_inserted_record OUT NOCOPY BOOLEAN
, x_allocated_quantity OUT NOCOPY NUMBER
, x_remaining_quantity OUT NOCOPY NUMBER
, x_sec_allocated_quantity OUT NOCOPY NUMBER
, x_sec_remaining_quantity OUT NOCOPY NUMBER
) IS
l_api_name VARCHAR2(30) := 'validate_and_insert';
SELECT 1 FROM dual
WHERE EXISTS
( SELECT 1
FROM mtl_lot_uom_class_conversions
WHERE lot_number = g_locs(p_record_id).lot_number
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND (disable_date IS NULL or disable_date > sysdate)
);
SELECT reservation_id
-- Added for bug 8665496
, lot_number
, reservation_quantity
, primary_reservation_quantity
, detailed_quantity
, reservation_uom_code
, secondary_reservation_quantity
, secondary_detailed_quantity
FROM mtl_reservations
WHERE reservation_id = p_reservation_id;
SELECT uom_class
FROM mtl_units_of_measure_vl
WHERE uom_code = p_uom;
log_statement(l_api_name, 'validate and insert', 'Entered with parameters: ' ||
' p_record_id: ' || p_record_id ||
', p_needed_quantity: ' || p_needed_quantity ||
', p_organization_id: ' || p_organization_id ||
', p_inventory_item_id: ' || p_inventory_item_id ||
', p_to_subinventory_code: ' || p_to_subinventory_code ||
', p_to_locator_id: ' || p_to_locator_id ||
', p_to_cost_group_id: ' || p_to_cost_group_id ||
', p_primary_uom: ' || p_primary_uom ||
', p_transaction_uom: ' || p_transaction_uom ||
', p_transaction_temp_id: ' || p_transaction_temp_id ||
', p_type_code: ' || p_type_code ||
', p_rule_id: ' || p_rule_id ||
', p_reservation_id: ' || p_reservation_id ||
', p_tree_id: ' || p_tree_id ||
', p_needed_sec_quantity: ' || p_needed_sec_quantity ||
', p_secondary_uom: ' || p_secondary_uom ||
', l_orig_needed_quantity: ' || l_orig_needed_quantity ||
', l_orig_needed_sec_qty: ' || l_orig_needed_sec_qty ||
', p_grade_code: ' || p_grade_code ||
', p_simulation_mode: ' || p_simulation_mode
);
log_statement(l_api_name, 'validate and insert', 'l_allocate_lot_flag: '|| l_allocate_lot_flag);
log_statement(l_api_name, 'validate and insert', 'l_indivisible_flag: '|| l_indivisible_flag);
log_statement(l_api_name, 'validate and insert', 'g_locs(p_record_id).lpn_id : '|| g_locs(p_record_id).lpn_id);
log_statement(l_api_name, 'validate and insert', g_locs(p_record_id).lpn_id || ' exists in g_lpns ');
log_statement(l_api_name, 'validate and insert', 'g_lpns(g_locs(p_record_id).lpn_id).quantity: '
|| g_lpns(g_locs(p_record_id).lpn_id).quantity);
log_statement(l_api_name, 'validate and insert', 'g_lpns(g_locs(p_record_id).lpn_id).total_quantity: '
|| g_lpns(g_locs(p_record_id).lpn_id).total_quantity);
log_statement(l_api_name, 'validate and insert', 'g_lpns(g_locs(p_record_id).lpn_id).secondary_quantity: '
|| g_lpns(g_locs(p_record_id).lpn_id).secondary_quantity);
log_statement(l_api_name, 'validate and insert', 'g_lpns(g_locs(p_record_id).lpn_id).secondary_total_quantity: '
|| g_lpns(g_locs(p_record_id).lpn_id).secondary_total_quantity);
log_statement(l_api_name, 'validate and insert', 'g_locs(p_record_id).lpn_id IS NULL. ');
log_statement(l_api_name, 'validate and insert', 'l_entire_lpn_alloc: '|| l_entire_lpn_alloc);
SELECT lot_number
INTO l_reserved_lot
FROM mtl_reservations
WHERE reservation_id = p_reservation_id;
log_statement(l_api_name, 'validate and insert', ' l_reserved_lot '||l_reserved_lot);
SELECT lot_number
INTO l_mo_lot
FROM mtl_txn_request_lines
WHERE line_id = p_transaction_temp_id;
log_statement(l_api_name, 'validate and insert', ' l_mo_lot '||l_mo_lot);
log_statement(l_api_name, 'validate and insert', 'Either the RSV or MO has lot, so allocating lot.');
log_statement(l_api_name,'validate and insert'
,'Secondary quantity from UOM conversion: ' || l_lot_sallocation_quantity);
log_error(l_api_name,'validate and insert','UOM conversion error');
log_statement(l_api_name,'validate and insert'
,'Allocating integer secondary, and no secondary remaining');
x_inserted_record := FALSE;
log_statement(l_api_name,'validate and insert'
,'Primary quantity from UOM conversion: ' || l_allocation_quantity);
log_statement(l_api_name, 'validate and insert', 'Fetch the l_top_model_line_id using txn_source_line_id: '
|| inv_cache.mol_rec.txn_source_line_id);
log_statement(l_api_name, 'validate and insert', 'Fetched l_top_model_line_id: ' || l_top_model_line_id);
log_statement(l_api_name, 'validate and insert', 'Final l_indivisible_flag: ' || l_indivisible_flag);
log_statement(l_api_name, 'validate and insert', 'New l_att: ' || l_att);
log_statement(l_api_name, 'validate and insert', 'New l_satt: ' || l_satt);
log_statement(l_api_name,'validate and insert'
,'Secondary quantity from UOM conversion (att < requested): ' || l_sallocation_quantity);
log_statement(l_api_name,'validate and insert'
,'Allocating integer secondary, and no secondary remaining (att < requested)');
x_inserted_record := FALSE;
log_statement(l_api_name,'validate and insert'
,'Primary quantity from UOM conversion (att < requested): ' || l_allocation_quantity);
log_error(l_api_name,'validate and insert','UOM conversion error');
SELECT SUM(primary_quantity), SUM(secondary_quantity)
INTO l_already_allocated_pri_qty, l_already_allocated_sec_qty
FROM wms_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND type_code = 2
AND line_type_code = 2 ;
x_inserted_record := FALSE;
x_inserted_record := FALSE;
x_inserted_record := FALSE;
x_inserted_record := FALSE;
x_inserted_record := FALSE;
log_statement(l_api_name, 'update_tree', 'Updating qty tree');
inv_quantity_tree_pvt.update_quantities(
p_api_version_number => g_qty_tree_api_version
, p_init_msg_lst => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_tree_id => p_tree_id
, p_revision => g_locs(p_record_id).revision
, p_lot_number => g_locs(p_record_id).lot_number
, p_subinventory_code => g_locs(p_record_id).subinventory_code
, p_locator_id => g_locs(p_record_id).locator_id
, p_primary_quantity => l_allocation_quantity
, p_secondary_quantity => l_sallocation_quantity -- INVCONV
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- INVCONV
, x_srqoh => l_srqoh -- INVCONV
, x_sqr => l_sqr -- INVCONV
, x_sqs => l_sqs -- INVCONV
, x_satt => l_satt -- INVCONV
, x_satr => l_satr -- INVCONV
, p_transfer_subinventory_code => p_to_subinventory_code
, p_cost_group_id => g_locs(p_record_id).cost_group_id
, p_lpn_id => g_locs(p_record_id).lpn_id
);
log_statement(l_api_name, 'uerr_update_qty', 'Unexpected error in inv_quantity_tree_pvt.update_quantities');
log_statement(l_api_name, 'err_update_qty', 'Error in inv_quantity_tree_pvt.update_quantities');
log_statement(l_api_name, 'insert_wtt_rec', 'Inserting wtt recs. Trx Qty: ' || l_possible_trx_qty);
log_statement(l_api_name, 'insert_wtt_rec', 'Inserting wtt recs. Sec Trx Qty: ' || l_sec_possible_trx_qty);
INSERT INTO wms_transactions_temp
( pp_transaction_temp_id
, transaction_temp_id
, type_code
, line_type_code
, transaction_quantity
, primary_quantity
, secondary_quantity
, grade_code
, revision
, lot_number
, lot_expiration_date
, from_subinventory_code
, from_locator_id
, rule_id
, reservation_id
, serial_number
, to_subinventory_code
, to_locator_id
, from_cost_group_id
, to_cost_group_id
, lpn_id
)
VALUES ( wms_transactions_temp_s.NEXTVAL
, p_transaction_temp_id
, p_type_code
, 2 -- line type code is output
, l_possible_trx_qty
, l_allocation_quantity
, l_sallocation_quantity
, g_locs(p_record_id).grade_code
, g_locs(p_record_id).revision
, g_locs(p_record_id).lot_number
, g_locs(p_record_id).lot_expiration_date
, g_locs(p_record_id).subinventory_code
, g_locs(p_record_id).locator_id
, p_rule_id
, p_reservation_id
, g_locs(p_record_id).serial_number
, p_to_subinventory_code
, l_to_locator_id
, g_locs(p_record_id).cost_group_id
, p_to_cost_group_id
, g_locs(p_record_id).lpn_id
);
SELECT pp_transaction_temp_id
INTO l_transaction_temp_id
FROM wms_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND type_code = p_type_code
AND line_type_code = 2
AND NVL(grade_code, '-9999') = NVL(g_locs(p_record_id).grade_code, '-9999')
AND NVL(revision, '-99') = NVL(g_locs(p_record_id).revision, '-99')
AND lot_number IS NULL
AND lot_expiration_date IS NULL
AND NVL(from_subinventory_code, '@@@@') = NVL(g_locs(p_record_id).subinventory_code, '@@@@')
AND NVL(from_locator_id, '-9999') = NVL(g_locs(p_record_id).locator_id, '-9999')
AND rule_id = p_rule_id
AND NVL(reservation_id, '-9999') = NVL(p_reservation_id, '-9999')
AND serial_number IS NULL
AND NVL(to_subinventory_code, '@@@@') = NVL(p_to_subinventory_code, '@@@@')
AND NVL(to_locator_id, '-9999') = NVL(l_to_locator_id, '-9999')
AND NVL(from_cost_group_id, '-9999') = NVL(g_locs(p_record_id).cost_group_id, '-9999')
AND NVL(to_cost_group_id, '-9999') = NVL(p_to_cost_group_id, '-9999')
AND NVL(lpn_id, '-9999') = NVL(g_locs(p_record_id).lpn_id, '-9999')
AND ROWNUM < 2;
UPDATE wms_transactions_temp
SET transaction_quantity = NVL(transaction_quantity, 0) + NVL(l_possible_trx_qty, 0),
primary_quantity = NVL(primary_quantity, 0) + NVL(l_allocation_quantity, 0),
secondary_quantity = NVL(secondary_quantity, 0) + NVL(l_sallocation_quantity, 0)
WHERE pp_transaction_temp_id = l_transaction_temp_id;
INSERT INTO wms_transactions_temp
( pp_transaction_temp_id
, transaction_temp_id
, type_code
, line_type_code
, transaction_quantity
, primary_quantity
, secondary_quantity
, grade_code
, revision
, lot_number
, lot_expiration_date
, from_subinventory_code
, from_locator_id
, rule_id
, reservation_id
, serial_number
, to_subinventory_code
, to_locator_id
, from_cost_group_id
, to_cost_group_id
, lpn_id
)
VALUES ( wms_transactions_temp_s.NEXTVAL
, p_transaction_temp_id
, p_type_code
, 2 -- line type code is output
, l_possible_trx_qty
, l_allocation_quantity
, l_sallocation_quantity
, g_locs(p_record_id).grade_code
, g_locs(p_record_id).revision
, NULL
, NULL
, g_locs(p_record_id).subinventory_code
, g_locs(p_record_id).locator_id
, p_rule_id
, p_reservation_id
, NULL --g_locs(p_record_id).serial_number
, p_to_subinventory_code
, l_to_locator_id
, g_locs(p_record_id).cost_group_id
, p_to_cost_group_id
, g_locs(p_record_id).lpn_id
);
log_statement(l_api_name, 'finish_insert_wtt', 'Finished inserting wtt recs.');
x_inserted_record := TRUE;
log_procedure(l_api_name, 'end', 'End Validate_and_Insert');
END validate_and_insert;
PROCEDURE validate_and_insert(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_record_id IN NUMBER
, p_needed_quantity IN NUMBER
, p_use_pick_uom IN BOOLEAN
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_to_subinventory_code IN VARCHAR2
, p_to_locator_id IN NUMBER
, p_to_cost_group_id IN NUMBER
, p_primary_uom IN VARCHAR2
, p_transaction_uom IN VARCHAR2
, p_transaction_temp_id IN NUMBER
, p_type_code IN NUMBER
, p_rule_id IN NUMBER
, p_reservation_id IN NUMBER
, p_tree_id IN NUMBER
, p_debug_on IN BOOLEAN
, p_needed_sec_quantity IN NUMBER
, p_secondary_uom IN VARCHAR2
, p_grade_code IN VARCHAR2
, x_inserted_record OUT NOCOPY BOOLEAN
, x_allocated_quantity OUT NOCOPY NUMBER
, x_remaining_quantity OUT NOCOPY NUMBER
, x_sec_allocated_quantity OUT NOCOPY NUMBER
, x_sec_remaining_quantity OUT NOCOPY NUMBER
) IS
BEGIN
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => p_record_id
, p_needed_quantity => p_needed_quantity
, p_use_pick_uom => p_use_pick_uom
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => p_to_subinventory_code
, p_to_locator_id => p_to_locator_id
, p_to_cost_group_id => p_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => p_rule_id
, p_reservation_id => p_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => p_debug_on
, p_needed_sec_quantity => p_needed_sec_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, p_orig_needed_quantity => p_needed_quantity
, p_orig_needed_sec_qty => p_needed_sec_quantity
, p_simulation_mode => wms_engine_pvt.g_no_simulation
, x_inserted_record => x_inserted_record
, x_allocated_quantity => x_allocated_quantity
, x_remaining_quantity => x_remaining_quantity
, x_sec_allocated_quantity => x_sec_allocated_quantity
, x_sec_remaining_quantity => x_sec_remaining_quantity );
END validate_and_insert;
SELECT SUM(moq.primary_transaction_quantity)
INTO l_moq_qty
FROM mtl_onhand_quantities_detail moq
WHERE moq.organization_id = p_organization_id
AND moq.inventory_item_id = p_inventory_item_id
AND nvl(moq.revision,'@@') = nvl(p_revision,'@@')
AND moq.subinventory_code = p_subinventory_code
AND decode(p_lot_number,null,'@@',moq.lot_number) = nvl(p_lot_number,'@@')
AND moq.locator_id = p_locator_id
AND moq.lpn_id = p_lpn_id;
SELECT SUM(Decode(mmtt.transaction_status, 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(p_revision,'@@')
AND mmtt.subinventory_code = p_subinventory_code
AND mmtt.locator_id = p_locator_id
AND NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) = p_lpn_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
AND mmtt.transaction_action_id NOT IN (5,6,24,30);
SELECT SUM(Abs(mmtt.primary_quantity))
INTO l_mmtt_qty_dest
FROM mtl_material_transactions_temp mmtt
WHERE decode(mmtt.transaction_action_id,3,
mmtt.transfer_organization,mmtt.organization_id) = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(p_revision,'@@')
AND mmtt.transfer_subinventory = p_subinventory_code
AND mmtt.transfer_to_location = p_locator_id
AND NVL(mmtt.content_lpn_id,mmtt.transfer_lpn_id) = p_lpn_id
AND mmtt.posting_flag = 'Y'
AND Nvl(mmtt.transaction_status,0) <> 2
AND mmtt.transaction_action_id in (2,28,3)
AND mmtt.wip_supply_type IS NULL;
SELECT SUM(Decode(mmtt.transaction_status, 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(p_revision,'@@')
AND mmtt.subinventory_code = p_subinventory_code
AND mmtt.locator_id = p_locator_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
AND mmtt.transaction_action_id NOT IN (5,6,24,30);
SELECT SUM(moq.primary_transaction_quantity)
INTO l_moq_qty
FROM mtl_onhand_quantities_detail moq
WHERE moq.organization_id = p_organization_id
AND moq.inventory_item_id = p_inventory_item_id
AND nvl(moq.revision,'@@') = nvl(p_revision,'@@')
AND moq.subinventory_code = p_subinventory_code
AND decode(p_lot_number,null,'@@',moq.lot_number) = nvl(p_lot_number,'@@')
AND moq.locator_id = p_locator_id;
SELECT SUM(Decode(mmtt.transaction_status, 2, 1,
Decode(mmtt.transaction_action_id,1,-1,2,-1,28,-1,3,-1,
Sign(mmtt.primary_quantity)))
* round(Abs(mmtt.primary_quantity),5))
INTO l_mmtt_qty_src
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND nvl(mmtt.revision,'@@') = nvl(p_revision,'@@')
AND mmtt.subinventory_code = p_subinventory_code
AND mmtt.locator_id = p_locator_id
AND mmtt.posting_flag = 'Y'
AND mmtt.subinventory_code IS NOT NULL
AND (Nvl(mmtt.transaction_status,0) <> 2 OR
Nvl(mmtt.transaction_status,0) = 2 AND
mmtt.transaction_action_id IN (1,2,28,3,21,29,32,34)
)
AND mmtt.transaction_action_id NOT IN (5,6,24,30);
PROCEDURE ValidNinsert(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_record_id IN NUMBER
, p_needed_quantity IN NUMBER
, p_use_pick_uom IN BOOLEAN
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_to_subinventory_code IN VARCHAR2
, p_to_locator_id IN NUMBER
, p_to_cost_group_id IN NUMBER
, p_primary_uom IN VARCHAR2
, p_transaction_uom IN VARCHAR2
, p_transaction_temp_id IN NUMBER
, p_type_code IN NUMBER
, p_rule_id IN NUMBER
, p_reservation_id IN NUMBER
, p_tree_id IN NUMBER
, p_debug_on IN BOOLEAN
, x_inserted_record OUT NOCOPY BOOLEAN
, x_allocated_quantity OUT NOCOPY NUMBER
, x_remaining_quantity OUT NOCOPY NUMBER
) IS
l_api_name VARCHAR2(30) := 'validate_N_insert';
log_procedure(l_api_name, 'start', 'Start Validate_N_Insert');
x_inserted_record := FALSE;
x_inserted_record := FALSE;
x_inserted_record := FALSE;
x_inserted_record := FALSE;
log_statement(l_api_name, 'insert_wtt_rec', 'Inserting wtt recs. Trx Qty: ' || l_possible_trx_qty);
INSERT INTO wms_transactions_temp
(
pp_transaction_temp_id
, transaction_temp_id
, type_code
, line_type_code
, transaction_quantity
, primary_quantity
, revision
, lot_number
, lot_expiration_date
, from_subinventory_code
, from_locator_id
, rule_id
, reservation_id
, serial_number
, to_subinventory_code
, to_locator_id
, from_cost_group_id
, to_cost_group_id
, lpn_id
)
VALUES (
wms_transactions_temp_s.NEXTVAL
, p_transaction_temp_id
, p_type_code
, 2 -- line type code is output
, l_possible_trx_qty
, l_allocation_quantity
, g_locs(p_record_id).revision
, g_locs(p_record_id).lot_number
, g_locs(p_record_id).lot_expiration_date
, g_locs(p_record_id).subinventory_code
, g_locs(p_record_id).locator_id
, p_rule_id
, p_reservation_id
, g_locs(p_record_id).serial_number
, p_to_subinventory_code
, p_to_locator_id
, g_locs(p_record_id).cost_group_id
, p_to_cost_group_id
, g_locs(p_record_id).lpn_id
);
log_statement(l_api_name, 'finish_insert_wtt', 'Finished inserting wtt recs.');
x_inserted_record := TRUE;
log_procedure(l_api_name, 'end', 'End ValidNinsert');
END ValidNinsert;
log_statement(l_api_name, 'first_loc', 'First rec to update: ' || l_current_loc);
DELETE FROM wms_transactions_temp
WHERE line_type_code = 2
AND type_code = p_type_code;
log_statement(l_api_name, 'finish_delete_sugs', 'Finished deleting suggestions and restored quantity tree');
l_inserted_record BOOLEAN;
l_insert NUMBER := 0; --bug# 8270806
l_insert := 0; --bug# 8270806,reseting the value to l_insert
log_statement(l_api_name, 'val_insert', 'Calling Validate and Insert');
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_uom_loc_id
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => p_to_subinventory_code
, p_to_locator_id => p_to_locator_id
, p_to_cost_group_id => p_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => p_rule_id
, p_reservation_id => p_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => p_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_needed_quantity_wo_tol -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_needed_sec_qty_wo_tol -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
l_insert := 1; --bug# 8270806
log_statement(l_api_name, 'val_insert', 'Calling Validate and Insert');
AND l_insert = 0 AND p_lot_divisible_flag = 'N'
)
OR ((NVL(p_lot_divisible_flag, 'Y') = 'Y') OR NVL(l_lot_control_code,1) = 1) -- added for bug7261861
THEN --{
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_loc_id
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => p_use_pick_uom
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => p_to_subinventory_code
, p_to_locator_id => p_to_locator_id
, p_to_cost_group_id => p_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => p_rule_id
, p_reservation_id => p_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => p_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_needed_quantity_wo_tol -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_needed_sec_qty_wo_tol -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
PROCEDURE insert_consist_record(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_record_id IN NUMBER
, p_needed_quantity IN NUMBER
, p_use_pick_uom IN BOOLEAN
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_to_subinventory_code IN VARCHAR2
, p_to_locator_id IN NUMBER
, p_to_cost_group_id IN NUMBER
, p_primary_uom IN VARCHAR2
, p_transaction_uom IN VARCHAR2
, p_transaction_temp_id IN NUMBER
, p_type_code IN NUMBER
, p_rule_id IN NUMBER
, p_reservation_id IN NUMBER
, p_tree_id IN NUMBER
, p_debug_on IN BOOLEAN
, p_order_by_rank IN NUMBER
, p_needed_sec_quantity IN NUMBER
, p_secondary_uom IN VARCHAR2
, p_grade_code IN VARCHAR2
, p_orig_needed_quantity IN NUMBER -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty IN NUMBER -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode IN NUMBER -- Added for 14699845 (Flexible lot allocation)
, x_finished OUT NOCOPY BOOLEAN
, x_remaining_quantity OUT NOCOPY NUMBER
, x_remaining_sec_qty OUT NOCOPY NUMBER
) IS
l_api_name VARCHAR2(30) := 'insert_consist_record';
log_procedure(l_api_name, 'start', 'Start Insert Consist Record');
log_procedure(l_api_name, 'end', 'End Insert_Consist_Record');
END insert_consist_record;
/*SELECT *
INTO l_locator
FROM mtl_item_locations
WHERE inventory_location_id = p_locator_id; Commented bug3237702*/
SELECT *
INTO g_locator
FROM MTL_ITEM_LOCATIONS
WHERE inventory_location_id = p_locator_id;
select *
into l_org
from mtl_parameters
where organization_id = l_locator.organization_id;
/* select *
into l_sub
from mtl_secondary_inventories
where secondary_inventory_name = l_locator.subinventory_code
and organization_id = l_locator.organization_id;
l_capacity_updated BOOLEAN;
l_inserted_record BOOLEAN;
l_custom_select_serials INV_DETAIL_UTIL_PVT.g_serial_row_table_rec;
SELECT allocation_mode_id
, qty_function_parameter_id
FROM wms_rules_b
WHERE rule_id = l_rule_id;
SELECT consistency_id
FROM wms_rule_consistencies
WHERE rule_id = l_rule_id;
SELECT SUM(primary_transaction_quantity)
, NVL(SUM(secondary_transaction_quantity),0)
FROM mtl_onhand_quantities_detail
WHERE lpn_id = v_current_row.lpn_id;
SELECT serial_number FROM wms_transactions_temp
WHERE rule_id = l_rule_id;
g_trace_recs.DELETE;
log_statement(l_api_name,'detailed', 'Reservation is fully detailed, validate and insert');
SELECT status_id
INTO l_serial_status_id
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number = l_serial_number;
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => 1
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_sec_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
g_locs.DELETE;
g_consists.DELETE;
g_lpns.DELETE;
AND (NVL(l_custom_serial_index,-1) < NVL(l_custom_select_serials.serial_number.LAST,-1))
THEN
-- next record should be next serial from custom API
l_custom_serial_index := l_custom_serial_index + 1;
v_current_row.serial_number := l_custom_select_serials.serial_number(l_custom_serial_index);
SELECT source_header_type_id, source_header_id, source_line_id
INTO l_source_type_id, l_source_header_id, l_source_line_id
FROM wsh_delivery_details
WHERE move_order_line_id = p_transaction_temp_id
AND rownum = 1;
, x_serial_numbers => l_custom_select_serials );
l_custom_serial_index := l_custom_select_serials.serial_number.FIRST;
v_current_row.serial_number := l_custom_select_serials.serial_number(l_custom_serial_index);
SELECT status_id
INTO l_serial_status_id
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number = v_current_row.serial_number;
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_lpn_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_sec_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
IF l_inserted_record = FALSE
OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'P' AND l_allocated_quantity < l_expected_quantity)
OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' AND l_sec_allocated_quantity < l_sec_expected_quantity)
THEN
IF l_debug = 1 THEN
log_statement(l_api_name, 'insert_failed', 'Record failed to allocation. Rolling back and '
|| 'invalidating LPN');
DELETE FROM wms_transactions_temp
WHERE line_type_code = 2
AND type_code = p_type_code;
log_statement(l_api_name, 'finish_delete_sugs',
'Finished deleting suggestions and restored quantity tree');
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_lpn_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_sec_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
IF l_inserted_record = FALSE
OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'P' AND l_allocated_quantity < l_expected_quantity)
OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S' AND l_sec_allocated_quantity < l_sec_expected_quantity)
THEN --{
IF l_debug = 1 THEN
log_statement(l_api_name, 'insert_failed', 'Record failed to allocation. Rolling back and '
|| 'invalidating LPN');
DELETE FROM wms_transactions_temp
WHERE line_type_code = 2
AND type_code = p_type_code;
log_statement(l_api_name, 'finish_delete_sugs', 'Finished deleting suggestions and restored quantity tree');
log_statement(l_api_name, 'val_insert', 'Calling validate_and_insert');
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_lpn_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_sec_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
IF l_inserted_record = FALSE
OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'P'
AND l_allocated_quantity < g_locs(l_cur_lpn_rec).quantity)
OR (NVL(wms_engine_pvt.g_fulfillment_base,'P') = 'S'
AND l_sec_allocated_quantity < g_locs(l_cur_lpn_rec).secondary_quantity)
THEN --{
IF l_debug = 1 THEN
log_statement(l_api_name, 'insert_fail', 'Record failed to insert.' || 'Invalidating LPN');
DELETE FROM wms_transactions_temp
WHERE line_type_code = 2
AND type_code = p_type_code;
log_statement(l_api_name, 'finish_delete_sugs', 'Finished deleting suggestions and restored quantity tree');
AND (NVL(l_custom_serial_index,-1) < NVL(l_custom_select_serials.serial_number.LAST,-1))
THEN
-- next record should be next serial from custom API
l_custom_serial_index := l_custom_serial_index + 1;
v_current_row.serial_number := l_custom_select_serials.serial_number(l_custom_serial_index);
SELECT source_header_type_id, source_header_id, source_line_id
INTO l_source_type_id, l_source_header_id, l_source_line_id
FROM wsh_delivery_details
WHERE move_order_line_id = p_transaction_temp_id
AND rownum = 1;
, x_serial_numbers => l_custom_select_serials );
l_custom_serial_index := l_custom_select_serials.serial_number.FIRST;
v_current_row.serial_number := l_custom_select_serials.serial_number(l_custom_serial_index);
SELECT status_id
INTO l_serial_status_id
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number = v_current_row.serial_number;
log_statement(l_api_name, 'insert_consist', 'Calling Insert_Consist_Record');
insert_consist_record(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_uom_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_order_by_rank => l_order_by_rank
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_finished => l_finished
, x_remaining_quantity => l_remaining_quantity
, x_remaining_sec_qty => l_sec_remaining_quantity
, p_orig_needed_quantity => l_orig_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_orig_needed_sec_qty -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_insert_consist', 'Unexpected error in insert_consist_record');
log_statement(l_api_name, 'err_insert_consist', 'Error in insert_consist_record');
log_statement(l_api_name, 'validate_insert', 'Calling Validate_and_Insert');
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_uom_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_orig_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_orig_needed_sec_qty -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
IF l_inserted_record THEN
l_needed_quantity := l_needed_quantity - l_allocated_quantity;
log_statement(l_api_name, 'insert_consist', 'Calling insert consist record from pick UOM list');
insert_consist_record(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_uom_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_order_by_rank => l_order_by_rank
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_finished => l_finished
, x_remaining_quantity => l_remaining_quantity
, x_remaining_sec_qty => l_sec_remaining_quantity
, p_orig_needed_quantity => l_orig_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_orig_needed_sec_qty -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_insert_consist', 'Unexpected error in insert_consist_record');
log_statement(l_api_name, 'err_insert_consist', 'Error in insert_consist_record');
log_statement(l_api_name, 'validate_insert', 'Calling Validate_and_insert for pick uom list');
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_uom_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_orig_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_orig_needed_sec_qty -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
IF l_inserted_record THEN
l_needed_quantity := l_needed_quantity - l_allocated_quantity;
log_statement(l_api_name, 'success_validate', 'Successfully inserted record. New needed quantity: '
|| l_needed_quantity);
log_statement(l_api_name, 'success_validate', 'Successfully inserted record. New needed sec quantity: '
|| l_sec_needed_quantity);
log_statement(l_api_name, 'success_validate', 'Successfully inserted record. New orig needed quantity: '
|| l_orig_needed_quantity);
log_statement(l_api_name, 'success_validate', 'Successfully inserted record. New orig needed sec qty: '
|| l_orig_needed_sec_qty);
log_statement(l_api_name, 'insert_consist', 'Calling Insert_Consist_Record');
insert_consist_record(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => g_locs_index
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => l_use_pick_uom
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_order_by_rank => l_order_by_rank
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_finished => l_finished
, x_remaining_quantity => l_remaining_quantity
, x_remaining_sec_qty => l_sec_remaining_quantity
, p_orig_needed_quantity => l_orig_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_orig_needed_sec_qty -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_insert_consist', 'Unexpected error in insert_consist_record');
log_statement(l_api_name, 'err_insert_consist', 'Error in insert_consist_record');
log_statement(l_api_name, 'validate_insert', 'Calling Validate_and_insert');
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => g_locs_index
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => l_use_pick_uom
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => l_rule_id
, p_reservation_id => l_reservation_id
, p_tree_id => p_tree_id
, p_debug_on => l_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
, p_orig_needed_quantity => l_orig_needed_quantity -- Added for 14699845 (Flexible lot allocation)
, p_orig_needed_sec_qty => l_orig_needed_sec_qty -- Added for 14699845 (Flexible lot allocation)
, p_simulation_mode => p_simulation_mode -- Added for 14699845 (Flexible lot allocation)
);
log_statement(l_api_name, 'uerr_validate_insert', 'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
IF l_inserted_record THEN
l_needed_quantity := l_needed_quantity - l_allocated_quantity;
log_statement(l_api_name, 'inserted_rec', 'Inserted record. New needed quantity: '
|| l_needed_quantity);
log_statement(l_api_name, 'inserted_rec', 'Inserted record. New needed sec quantity: '
|| l_sec_needed_quantity);
log_statement(l_api_name, 'inserted_rec', 'Inserted record. New orig needed quantity: ' || l_orig_needed_quantity);
log_statement(l_api_name, 'inserted_rec', 'Inserted record. New orig needed sec qty: ' || l_orig_needed_sec_qty);
l_capacity_updated := FALSE;
log_statement(l_api_name, 'update_capacity', 'Updating suggested capacity');
inv_loc_wms_utils.update_loc_suggested_capacity(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_organization_id => p_organization_id
, p_inventory_location_id => l_olocator_id
, p_inventory_item_id => p_inventory_item_id
, p_primary_uom_flag => 'Y'
, p_transaction_uom_code => NULL
, p_quantity => l_possible_quantity
);
log_statement(l_api_name, 'uerr_update_capacity', 'Unexpected error in update_loc_suggested_capacity');
log_statement(l_api_name, 'err_update_capacity', 'Error in update_loc_suggested_capacity');
l_capacity_updated := TRUE;
log_statement(l_api_name, 'insert_put_wtt_recs', 'Before insert values of l_possible_trx_qty'|| l_possible_trx_qty);
log_statement(l_api_name, 'insert_put_wtt_recs', 'Before insert values of l_possible_quantity'|| l_possible_quantity);
log_statement(l_api_name, 'insert_put_wtt_recs', 'Inserting putaway records in wtt');
INSERT INTO wms_transactions_temp
(
pp_transaction_temp_id
, transaction_temp_id
, type_code
, line_type_code
, transaction_quantity
, primary_quantity
, secondary_quantity
, grade_code
, revision
, lot_number
, lot_expiration_date
, from_subinventory_code
, from_locator_id
, rule_id
, reservation_id
, to_subinventory_code
, to_locator_id
, from_cost_group_id
, to_cost_group_id
, lpn_id
)
VALUES (
wms_transactions_temp_s.NEXTVAL
, p_transaction_temp_id
, p_type_code
, 2 -- line type code is output
, l_possible_trx_qty
, l_possible_quantity
, l_sec_possible_quantity
, l_grade_code
, l_revision
, l_lot_number
, l_lot_expiration_date
, l_from_subinventory_code
, l_from_locator_id
, l_rule_id
, l_reservation_id
, l_osubinventory_code
, l_olocator_id
, l_from_cost_group_id
, l_to_cost_group_id
, l_input_lpn_id
);
l_capacity_updated := FALSE;
inv_log_util.trace('Serial not found', 'delete_serial_numbers', 9);
inv_log_util.trace('Org: ' || p_organization_id, 'delete_serial_numbers', 9);
inv_log_util.trace('Item: ' || p_inventory_item_id, 'delete_serial_numbers', 9);
inv_log_util.trace('Serial: ' || l_serial_number, 'delete_serial_numbers', 9);
|| inv_detail_util_pvt.g_serial_tbl_ptr, 'delete_serial_numbers', 9);
inv_detail_util_pvt.g_output_serial_rows.DELETE(inv_detail_util_pvt.g_serial_tbl_ptr);
|| inv_detail_util_pvt.g_serial_tbl_ptr, 'delete_serial_numbers', 9);
inv_detail_util_pvt.g_output_serial_rows.DELETE;
inv_log_util.trace('Array cleared', 'delete_serial_numbers', 9);
EXIT; -- if a serial has been deleted, needn't check other serials present in g_output_serial_rows
rollback_capacity_update(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
);
log_statement(l_api_name, 'uerr_rollback_cap', 'Unexpected error in rollback_capacity_update');
log_statement(l_api_name, 'err_rollback_cap', 'Error in rollback_capacity_update');
IF l_capacity_updated THEN
IF l_debug = 1 THEN
log_statement(l_api_name, 'revert_capacity', 'Calling revert_loc_suggested_capacity');
l_capacity_updated := FALSE;
wms_search_order_globals_pvt.insert_trace_lines(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_header_id => wms_engine_pvt.g_trace_header_id
, p_rule_id => l_rule_id
, p_pre_suggestions => g_trace_recs
);
UPDATE wms_transactions_temp
SET primary_quantity = l_needed_quantity
, secondary_quantity = l_sec_needed_quantity
WHERE pp_transaction_temp_id = l_pp_transaction_temp_id;
wms_re_common_pvt.updateinputline(l_needed_quantity,l_sec_needed_quantity);
DELETE FROM wms_transactions_temp
WHERE pp_transaction_temp_id = l_pp_transaction_temp_id;
wms_re_common_pvt.deleteinputline;
wms_search_order_globals_pvt.insert_trace_lines(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_header_id => wms_engine_pvt.g_trace_header_id
, p_rule_id => l_rule_id
, p_pre_suggestions => g_trace_recs
);
rollback_capacity_update(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
);
IF l_capacity_updated THEN
inv_loc_wms_utils.revert_loc_suggested_capacity(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_organization_id
, p_inventory_location_id => l_olocator_id
, p_inventory_item_id => p_inventory_item_id
, p_primary_uom_flag => 'Y'
, p_transaction_uom_code => NULL
, p_quantity => l_possible_quantity
);
l_capacity_updated := FALSE;
rollback_capacity_update(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
);
IF l_capacity_updated THEN
inv_loc_wms_utils.revert_loc_suggested_capacity(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_organization_id
, p_inventory_location_id => l_olocator_id
, p_inventory_item_id => p_inventory_item_id
, p_primary_uom_flag => 'Y'
, p_transaction_uom_code => NULL
, p_quantity => l_possible_quantity
);
l_capacity_updated := FALSE;
rollback_capacity_update(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
);
IF l_capacity_updated THEN
inv_loc_wms_utils.revert_loc_suggested_capacity(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_organization_id
, p_inventory_location_id => l_olocator_id
, p_inventory_item_id => p_inventory_item_id
, p_primary_uom_flag => 'Y'
, p_transaction_uom_code => NULL
, p_quantity => l_possible_quantity
);
l_capacity_updated := FALSE;
SELECT subinventory_code
FROM mtl_item_sub_defaults
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND default_type = 3; -- default transfer order sub
SELECT status_id
FROM mtl_item_locations
WHERE inventory_location_id = l_putaway_loc
AND organization_id = l_organization_id ;
SELECT locator_id
FROM mtl_item_loc_defaults mtld,
mtl_item_locations mil
WHERE mtld.locator_id = mil.inventory_location_id
AND mtld.organization_id = mil.organization_id
AND mtld.inventory_item_id = l_inventory_item_id
AND mtld.organization_id = l_organization_id
AND mtld.subinventory_code = l_putaway_sub
AND mtld.default_type = 3
AND nvl(mil.disable_date,sysdate + 1) > sysdate;
SELECT lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = l_osubinventory_code;
g_trace_recs.DELETE;
select locator_type into l_locator_type
from mtl_secondary_inventories
where organization_id = p_organization_id
and SECONDARY_INVENTORY_NAME = l_to_subinventory_code;
log_statement(l_api_name, 'insert_put_wtt_recs',
'Inserting putaway records in wtt');
INSERT
INTO WMS_TRANSACTIONS_TEMP
( PP_TRANSACTION_TEMP_ID
,TRANSACTION_TEMP_ID
,TYPE_CODE
,LINE_TYPE_CODE
,TRANSACTION_QUANTITY
,PRIMARY_QUANTITY
,SECONDARY_QUANTITY
,GRADE_CODE
,REVISION
,LOT_NUMBER
,LOT_EXPIRATION_DATE
,FROM_SUBINVENTORY_CODE
,FROM_LOCATOR_ID
,RULE_ID
,RESERVATION_ID
,TO_SUBINVENTORY_CODE
,TO_LOCATOR_ID
,FROM_COST_GROUP_ID
,TO_COST_GROUP_ID
,LPN_ID
) VALUES
( wms_transactions_temp_s.NEXTVAL
,p_transaction_temp_id
,1 -- p_type_code
,2 -- line type code is output
,l_possible_trx_qty
,l_possible_quantity
,l_sec_possible_quantity
,l_grade_code
,l_revision
,l_lot_number
,l_lot_expiration_date
,l_from_subinventory_code
,l_from_locator_id
,NULL -- l_rule_id
,l_reservation_id
,l_osubinventory_code
,l_olocator_id
,l_from_cost_group_id
,l_to_cost_group_id
,l_input_lpn_id
);
DELETE
FROM WMS_TRANSACTIONS_TEMP
WHERE pp_transaction_temp_id = l_pp_transaction_temp_id;
wms_re_common_pvt.DeleteInputLine;
wms_search_order_globals_pvt.insert_trace_lines(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_header_id => wms_engine_pvt.g_trace_header_id
,p_rule_id => null
,p_pre_suggestions => g_trace_recs
);
SELECT rule_id
, organization_id
, type_code
, NAME
, description
, qty_function_parameter_id
, enabled_flag
, user_defined_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM wms_rules_vl
WHERE rule_id = p_rule_id;
g_build_package_tbl.DELETE;
SELECT status
FROM user_objects
WHERE object_name = UPPER(p_package_name)
AND object_type='PACKAGE'
AND status <> 'VALID'
AND rownum = 1;
SELECT status
FROM user_objects
WHERE object_name = UPPER(p_package_name)
AND object_type = 'PACKAGE BODY'
AND status <> 'VALID'
AND rownum = 1;
,insert_newlines => 'FALSE'
,comp_error => l_comp_error
);
SELECT type_code
, allocation_mode_id
FROM wms_rules_b mpr
WHERE mpr.rule_id = p_rule_id;
g_stmt_serial := 'select '
|| g_base_select
|| g_rule_select_serial_new -- Added for Bug 13448337
|| ' from '
|| g_rule_from
|| g_base_from_serial
|| ' where '
|| g_input_where
|| g_rule_where
|| ' group by '
|| g_base_group_by
|| g_rule_group_by_new; -- Added for Bug 13448337
g_stmt_serial_validate := 'select '
|| g_base_select
|| g_rule_select_serial_new -- Added for Bug 13448337
|| ' from '
|| g_rule_from
|| g_base_from_serial_v
|| ' where '
|| g_input_where
|| g_rule_where
|| ' group by '
|| g_base_group_by
|| g_rule_group_by_new; -- Added for Bug 13448337
g_stmt_serial_detail := 'select '
|| g_base_select
|| g_rule_select
|| ' from '
|| g_rule_from
|| g_base_from_serial
|| ' where '
|| g_input_where
|| g_rule_where;
g_stmt_serial_detail_new := 'select '
|| g_base_select
|| g_rule_select
|| ' from '
|| g_rule_from
|| g_base_from_serial_detail
|| ' where '
|| g_input_where
|| g_rule_where;
g_stmt_serial := 'select 1,1,sysdate,1,1,1,1,1,1,1,1,1 from dual';
g_stmt_serial_validate := 'select 1,1,sysdate,1,1,1,1,1,1,1,1,1 from dual';
g_stmt_serial_detail := 'select 1,1,sysdate,1,1,1,1,1,1,1,1,1 from dual';
g_stmt_serial_detail_new := 'select 1,1,sysdate,1,1,1,1,1,1,1,1,1 from dual';
g_stmt := 'select count(*) '
|| ' from '
|| NVL(g_rule_from, 'WMS_LABEL_REQUESTS wlr')
|| ' where wlr.label_request_id = p_label_request_id '
|| g_rule_where;
g_stmt := 'select count(*) '
|| ' from '
|| NVL(g_rule_from, 'MTL_MATERIAL_TRANSACTIONS_TEMP mmtt')
|| ' where mmtt.transaction_temp_id = p_pp_transaction_temp_id '
|| g_rule_where;
g_stmt := 'select count(*) '
|| ' from '
|| NVL(g_rule_from, 'MTL_MATERIAL_TRANSACTIONS_TEMP mmtt')
|| ' where (MMTT.PARENT_LINE_ID = p_pp_transaction_temp_id or MMTT.TRANSACTION_TEMP_ID = p_pp_transaction_temp_id) ' -- Bug Fix 5560849, 8546026(High vol project)
|| g_rule_where;
g_stmt := 'select count(*) '
|| ' from '
|| NVL(g_rule_from, 'WMS_COST_GROUPS_INPUT_V wcgiv')
|| ' where wcgiv.line_id = g_line_id '
|| g_rule_where;
'select ' || g_base_select || ' from ' || g_rule_from || g_base_from || ' where ' || g_input_where || g_rule_where;
'select ' || g_base_select || g_rule_select || ' from ' || g_rule_from || g_base_from || ' where ' || g_input_where || g_rule_where;
g_stmt := 'select '
|| g_base_select
|| g_rule_select_serial
|| ' from '
|| g_rule_from
|| g_base_from
|| ' where '
|| g_input_where
|| g_rule_where
|| 'group by '
|| g_base_group_by
|| g_rule_group_by;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
WHERE mmtt.operation_plan_id IS NULL
AND mmtt.move_order_line_id = mol.line_id
AND mol.header_id = p_move_order_header_id;
SELECT 1 INTO l_count
FROM dual
WHERE EXISTS (SELECT 1 FROM wms_rules_b rules
WHERE rules.type_code = 7
AND rules.enabled_flag = 'Y'
AND (organization_id = -1
OR organization_id IN (SELECT mmtt.organization_id
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol
WHERE mmtt.operation_plan_id IS NULL /*bug9128227*/
AND mmtt.move_order_line_id = mol.line_id
AND mol.header_id = p_move_order_header_id)));
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.operation_plan_id = (SELECT NVL(default_pick_op_plan_id, 1)
FROM mtl_parameters mp
WHERE mp.organization_id = mmtt.organization_id)
WHERE mmtt.operation_plan_id IS NULL
AND mmtt.move_order_line_id IN (SELECT line_id
FROM mtl_txn_request_lines mol
WHERE mol.header_id = p_move_order_header_id)
AND mmtt.transaction_source_type_id in (2, 8);
SELECT rules.rule_id
, mmtt.organization_id
, mmtt.wms_task_type
, rules.type_hdr_id
FROM wms_rules_b rules, wms_op_plans_b wop, mtl_material_transactions_temp mmtt
WHERE rules.type_code = 7
AND rules.enabled_flag = 'Y'
AND rules.type_hdr_id = wop.operation_plan_id
AND wop.system_task_type = NVL(mmtt.wms_task_type, wop.system_task_type)
--AND mmtt.transaction_source_type_id <> 5 -- exclude wip issue tasks
AND mmtt.transaction_source_type_id IN (2, 8) --restrict to sales order and internal order mmtts only
AND mmtt.transaction_temp_id = p_task_id
AND rules.organization_id IN (mmtt.organization_id, -1)
AND NVL(wop.organization_id, mmtt.organization_id) = mmtt.organization_id
AND wop.enabled_flag = 'Y'
ORDER BY rules.rule_weight DESC, rules.creation_date;
SELECT NVL(default_pick_op_plan_id, 1)
FROM mtl_parameters
WHERE organization_id = l_organization_id;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.operation_plan_id = l_operation_plan_id
WHERE mmtt.transaction_temp_id = p_task_id;
select organization_id
into l_organization_id
from mtl_material_transactions_temp
where transaction_temp_id = p_task_id
AND transaction_source_type_id IN (2, 8); -- bug fix 3361560
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.operation_plan_id = l_operation_plan_id
WHERE mmtt.transaction_temp_id = p_task_id;
SELECT mmtt.transaction_temp_id
,mmtt.organization_id -- Added new
,mmtt.wms_task_type -- Added new
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
WHERE mmtt.standard_operation_id IS NULL
AND mmtt.move_order_line_id = mol.line_id
AND mol.header_id = p_move_order_header_id;
SELECT 1 INTO l_count
FROM dual
WHERE EXISTS (SELECT 1 FROM wms_rules_b rules
WHERE rules.type_code = 3
AND rules.enabled_flag = 'Y'
AND (organization_id = -1
OR organization_id IN (SELECT mmtt.organization_id
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol
WHERE mmtt.standard_operation_id IS NULL
AND mmtt.move_order_line_id = mol.line_id
AND mol.header_id = p_move_order_header_id)));
UPDATE mtl_material_transactions_temp mmtt
SET standard_operation_id =
(SELECT DECODE(mmtt.wms_task_type, 1, default_pick_task_type_id,
2, default_putaway_task_type_id,
3, default_cc_task_type_id,
4, default_repl_task_type_id,
5, default_moxfer_task_type_id,
6, default_moissue_task_type_id,
NULL)
FROM mtl_parameters mp WHERE mp.organization_id = mmtt.organization_id)
WHERE mmtt.standard_operation_id IS NULL
AND mmtt.move_order_line_id IN (SELECT line_id
FROM mtl_txn_request_lines mol
WHERE mol.header_id = p_move_order_header_id);
SELECT rules.rule_id
, rules.type_hdr_id /* Added this Column */
, mmtt.organization_id
, mmtt.wms_task_type
FROM wms_rules_b rules, bom_standard_operations bso , mtl_material_transactions_temp mmtt
WHERE rules.type_code = 3
AND rules.enabled_flag = 'Y'
AND rules.type_hdr_id = bso.standard_operation_id
AND bso.wms_task_type = NVL(mmtt.wms_task_type, bso.wms_task_type)
AND mmtt.transaction_temp_id = p_task_id
AND rules.organization_id IN (mmtt.organization_id, -1)
AND bso.organization_id = mmtt.organization_id
ORDER BY rules.rule_weight DESC, rules.creation_date;
SELECT rules.rule_id , rules.type_hdr_id
FROM wms_rules_b rules, bom_standard_operations bso
WHERE rules.type_code = 3
AND rules.enabled_flag = 'Y'
AND rules.type_hdr_id = bso.standard_operation_id
AND bso.wms_task_type = NVL(p_wms_task_type, bso.wms_task_type)
AND rules.organization_id IN (p_organization_id, -1)
AND bso.organization_id = p_organization_id
ORDER BY rules.rule_weight DESC, rules.creation_date; */
SELECT default_pick_task_type_id
,default_cc_task_type_id
,default_putaway_task_type_id
,default_repl_task_type_id
FROM mtl_parameters
WHERE organization_id = l_organization_id;
SELECT default_pick_task_type_id
, default_cc_task_type_id
, default_putaway_task_type_id
, default_repl_task_type_id
, default_moxfer_task_type_id
, default_moissue_task_type_id
, default_pick_op_plan_id
FROM mtl_parameters
WHERE organization_id = l_organization_id;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.standard_operation_id = l_type_hdr_id
WHERE mmtt.transaction_temp_id = p_task_id;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.standard_operation_id = (SELECT type_hdr_id
FROM wms_rules_b
WHERE rule_id = l_rule_id)
WHERE mmtt.transaction_temp_id = p_task_id
*/
EXIT; -- task assigned, jump out of the rule loop
select organization_id, wms_task_type
into l_organization_id, l_wms_task_type
from mtl_material_transactions_temp
where transaction_temp_id = p_task_id;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.standard_operation_id = l_task_type_id
WHERE mmtt.transaction_temp_id = p_task_id;
UPDATE wms_rules_b
SET rule_weight = (SELECT 100 * COUNT(parameter_id)
FROM (SELECT DISTINCT rules.rule_id
, par.parameter_id
FROM wms_rules_b rules, wms_restrictions rest, wms_parameters_b par
WHERE rules.rule_id = p_rule_id
AND rules.rule_id = rest.rule_id(+)
AND rest.parameter_id = par.parameter_id(+)
AND (NVL(par.use_for_tt_assn_flag, 'Y') = 'Y'
OR NVL(par.use_for_label_rest_flag, 'Y') = 'Y'
)))
WHERE rule_id = p_rule_id
AND rule_weight IS NULL;
SELECT rules.rule_id
, rules.rule_weight
, rules.type_hdr_id
, wl.label_format_name
, wl.document_id
FROM wms_rules_b rules, wms_label_formats wl, wms_label_requests wlr
WHERE rules.type_code = 4
AND rules.type_hdr_id = wl.label_format_id
AND wl.label_format_id = NVL(wlr.label_format_id, wl.label_format_id)
AND NVL(format_disable_date, SYSDATE + 1) > SYSDATE --Bug #3452076
AND wlr.document_id = wl.document_id
AND wlr.label_request_id = p_label_request_id
AND rules.enabled_flag = 'Y'
AND (rules.organization_id = wlr.organization_id
OR rules.organization_id = -1
) -- Common to All Org.
ORDER BY rules.rule_weight DESC, rules.creation_date;
SELECT label_format_id
, label_format_name
FROM wms_label_formats
WHERE document_id = l_document_id
AND default_format_flag IN ('Y', 'y');
SELECT *
FROM wms_label_requests
WHERE label_request_id = p_label_request_id;
SELECT count(rules.rule_id ) into ll_ctr
FROM wms_rules_b rules, wms_label_formats wl, wms_label_requests wlr
WHERE rules.type_code = 4
AND rules.type_hdr_id = wl.label_format_id
AND wl.label_format_id = NVL(wlr.label_format_id, wl.label_format_id)
AND NVL(format_disable_date, SYSDATE + 1) > SYSDATE --Bug # 3452076
AND wlr.document_id = wl.document_id
AND wlr.label_request_id = p_label_request_id
AND rules.enabled_flag = 'Y'
AND (rules.organization_id = wlr.organization_id
OR rules.organization_id = -1
) ;
UPDATE wms_label_requests wlr
SET wlr.label_format_id = l_label_format_id
, wlr.rule_id = l_rule_id
, wlr.strategy_id = l_strategy_id
, wlr.rule_weight = l_rule_weight
WHERE wlr.label_request_id = p_label_request_id;
TRACE('Rule Match.Update Label Request with format ID :'|| l_label_format_id || ' ' || l_label_format_name);
UPDATE wms_label_requests wlr
SET wlr.label_format_id = l_label_format_id
WHERE wlr.label_request_id = p_label_request_id;
TRACE('Update Label Request with label ID :'|| l_label_format_id || '.(' || SQL%ROWCOUNT || ')');
SELECT rule_id
FROM wms_rules_b
WHERE enabled_flag = 'Y'
ORDER BY rule_id;
l_inserted_record BOOLEAN;
SELECT STATUS_ID FROM MTL_SERIAL_NUMBERS
WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
AND LPN_ID = l_lpn_id;
g_locs.DELETE;
log_statement(l_api_name, 'QuickPick', 'Calling validate_and_insert');
log_statement(l_api_name, 'QuickPick', 'Insert an Output record into WTT if status allowed');
log_statement(l_api_name, 'Calling validate_and_insert', '');
validate_and_insert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => 0
, p_reservation_id => l_reservation_id
, p_tree_id => l_tree_id
, p_debug_on => l_debug_on
, p_needed_sec_quantity => l_sec_needed_quantity
, p_secondary_uom => p_secondary_uom
, p_grade_code => p_grade_code
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
, x_sec_allocated_quantity => l_sec_allocated_quantity
, x_sec_remaining_quantity => l_sec_remaining_quantity
);
ELSIF p_tree_id is NULL THEN -- Call the new local procedure validate_and_insert_noqtytree() for bug #4006426
IF l_debug = 1 THEN
log_statement(l_api_name, 'Calling validateNinsert', '');
ValidNinsert(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_record_id => l_cur_rec
, p_needed_quantity => l_needed_quantity
, p_use_pick_uom => FALSE
, p_organization_id => p_organization_id
, p_inventory_item_id => p_inventory_item_id
, p_to_subinventory_code => l_to_subinventory_code
, p_to_locator_id => l_to_locator_id
, p_to_cost_group_id => l_to_cost_group_id
, p_primary_uom => p_primary_uom
, p_transaction_uom => p_transaction_uom
, p_transaction_temp_id => p_transaction_temp_id
, p_type_code => p_type_code
, p_rule_id => 0
, p_reservation_id => l_reservation_id
, p_tree_id => l_tree_id
, p_debug_on => l_debug_on
, x_inserted_record => l_inserted_record
, x_allocated_quantity => l_allocated_quantity
, x_remaining_quantity => l_remaining_quantity
);
log_statement(l_api_name, 'uerr_validate_insert',
'Unexpected error in validate_and_insert');
log_statement(l_api_name, 'err_validate_insert', 'Error in validate_and_insert');
IF l_inserted_record = FALSE OR ABS(l_allocated_quantity - l_needed_quantity) > 0.00005
THEN -- made by when testing 13591755
fnd_message.set_name('WMS', 'WMS_LPN_UNAVAILABLE'); --- to be Added to Mesg Dict
log_statement(l_api_name, 'insert_failed', 'Record failed to allocation. Rolling back and ' || 'invalidating LPN');
log_statement(l_api_name, 'insert_failed', 'Rolling back');
l_capacity_updated BOOLEAN;
l_inserted_record BOOLEAN;
l_sec_inserted_record BOOLEAN; -- new
SELECT allocation_mode_id
, qty_function_parameter_id
FROM wms_rules_b
WHERE rule_id = l_rule_id;
SELECT consistency_id
FROM wms_rule_consistencies
WHERE rule_id = l_rule_id;
SELECT SUM(primary_transaction_quantity)
FROM mtl_onhand_quantities_detail
WHERE lpn_id = v_current_row.lpn_id;
/* Lgao, Bug 5141737 select available will not check this flag, not used */
/*IF p_partial_success_allowed_flag IS NULL
OR p_partial_success_allowed_flag = fnd_api.g_miss_char THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
fnd_message.set_name('WMS', 'WMS_PARTIAL_SUCC_FLAG_MISS');
g_trace_recs.DELETE;
l_update BOOLEAN := FALSE;
l_update := TRUE;
IF(l_update) THEN
fnd_profile.put('MFG_ORGANIZATION_ID',l_organization_id);