[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT decode(p_type_code, 1, mpsmttv.to_organization_id,
mpsmttv.from_organization_id) organization_id
,mpsmttv.inventory_item_id
,mpsmttv.transaction_source_type_id
,mpsmttv.txn_source_id
,mpsmttv.txn_source_line_id
,mpsmttv.txn_source_line_detail
,mpsmttv.txn_source_name
,mpsmttv.transaction_type_id
,mpsmttv.transaction_uom
,msi.primary_uom_code
,msi.secondary_uom_code
from mtl_system_items msi
,wms_strategy_mat_txn_tmp_v mpsmttv
where msi.organization_id =
decode(p_type_code, 1, mpsmttv.to_organization_id,
mpsmttv.from_organization_id)
and msi.inventory_item_id = mpsmttv.inventory_item_id
and mpsmttv.line_id = p_transaction_temp_id
and mpsmttv.type_code = p_type_code; */
select txn_source_id ,
txn_source_line_id,
txn_source_name,
txn_source_line_detail
from wms_txn_context_temp wtct
where line_id = p_transaction_temp_id;
select mptt.PP_TRANSACTION_TEMP_ID
,mptt.REVISION
,mptt.LOT_NUMBER
,mptt.LOT_EXPIRATION_DATE
,mptt.FROM_SUBINVENTORY_CODE
,mptt.FROM_LOCATOR_ID
,mptt.FROM_COST_GROUP_ID
,mptt.TO_SUBINVENTORY_CODE
,mptt.TO_LOCATOR_ID
,mptt.TO_COST_GROUP_ID
,mptt.primary_quantity
,mptt.secondary_quantity
,mptt.grade_code
,mptt.reservation_id
,mptt.serial_number ---- [ Added code - ,mptt.serial_number ]
,mptt.lpn_id
from WMS_TRANSACTIONS_TEMP mptt
where mptt.TRANSACTION_TEMP_ID = p_transaction_temp_id
and mptt.TYPE_CODE = p_type_code
and mptt.LINE_TYPE_CODE = 1
order by mptt.pp_transaction_temp_id
;
SELECT wsm.rule_id
,wsm.partial_success_allowed_flag
,NVL(wsb.over_allocation_mode, 1) ,wsb.tolerance_value
FROM wms_strategy_members wsm
,wms_strategies_b wsb
,wms_rules_b wrb
WHERE wsm.strategy_id = p_strategy_id
AND wsb.strategy_id = p_strategy_id
AND wrb.rule_id = wsm.rule_id
AND wrb.enabled_flag = 'Y'
AND wms_datecheck_pvt.date_valid (wsb.organization_id,
wsm.date_type_code,
wsm.date_type_from,
wsm.date_type_to,
wsm.effective_from,
wsm.effective_to) = 'Y'
ORDER BY wsm.sequence_number;
select nvl(msi.REVISION_QTY_CONTROL_CODE,1)
,nvl(msi.LOT_CONTROL_CODE,1)
,nvl(msi.SERIAL_NUMBER_CONTROL_CODE,1)
from MTL_SYSTEM_ITEMS msi
where ORGANIZATION_ID = p_organization_id
and INVENTORY_ITEM_ID = p_inventory_item_id
;
l_select long := null;
select decode(p_type_code, 1, mpsmttv.TO_ORGANIZATION_ID,
mpsmttv.FROM_ORGANIZATION_ID) organization_id
from WMS_STRATEGY_MAT_TXN_TMP_V mpsmttv
where mpsmttv.LINE_ID = p_transaction_temp_id
and mpsmttv.TYPE_CODE = p_type_code;
select organization_id
from wms_cost_groups_input_v wcgiv
where wcgiv.line_id = p_transaction_temp_id;
select mpo.OBJECT_ID
,mpo.STRAT_ASGMT_DB_OBJECT_ID
from WMS_OBJECTS_B mpo
,WMS_ORG_HIERARCHY_OBJS mpoho
where mpoho.ORGANIZATION_ID = l_organization_id
and mpoho.TYPE_CODE = p_type_code
and mpo.OBJECT_ID = mpoho.OBJECT_ID
and mpo.STRAT_ASGMT_DB_OBJECT_ID is not null
and mpo.STRAT_ASGMT_LOV_SQL is not null
order by mpoho.SEARCH_ORDER;
select mpdo.DB_OBJECT_ID
,mpdo.TABLE_NAME
,mpdo.TABLE_ALIAS
from WMS_DB_OBJECTS mpdo
where mpdo.db_object_id IN
(SELECT mpdo.db_object_id
FROM wms_db_objects mpdo
WHERE mpdo.db_object_id = l_strat_asgmt_db_object_id
UNION
SELECT mpdop.parent_db_object_id
FROM wms_db_objects_parents mpdop
WHERE mpdop.type_code = l_search_type_code
Connect by mpdop.DB_OBJECT_ID = prior mpdop.PARENT_DB_OBJECT_ID
Start with mpdop.DB_OBJECT_ID = l_strat_asgmt_db_object_id AND
mpdop.type_code = l_search_type_code );
select mpp.PARAMETER_TYPE_CODE
,mpp.COLUMN_NAME
,mpdop1.TABLE_ALIAS --- added for CG
,mpp.EXPRESSION
,mpp.DATA_TYPE_CODE
,mppp.PARAMETER_TYPE_CODE
,mppp.COLUMN_NAME
,mppp.EXPRESSION
,mppp.DATA_TYPE_CODE
,mpdop.TABLE_ALIAS -- alias n.a. for multi object based parameters
from WMS_DB_OBJECTS mpdop
,WMS_DB_OBJECTS mpdop1 -- added for CG
,WMS_PARAMETERS_B mppp
,WMS_PARAMETERS_B mpp
,WMS_DB_OBJECT_JOINS mpdoj
where mpdoj.DB_OBJECT_ID = l_db_object_id
and mpdoj.type_code = l_search_type_code
and mpp.PARAMETER_ID = mpdoj.PARAMETER_ID
and mppp.PARAMETER_ID = mpdoj.PARENT_PARAMETER_ID
and mpdop1.DB_OBJECT_ID = mpp.DB_OBJECT_ID --- Added for CG
and mpdop.DB_OBJECT_ID (+) = mppp.DB_OBJECT_ID;
l_select := 'select '||l_table_alias||'.STRATEGY_ID'|| '
,' || l_table_alias || '.PK1_VALUE ' || '
,' || l_table_alias || '.PK2_VALUE ' || '
,' || l_table_alias || '.PK3_VALUE ' || '
,' || l_table_alias || '.PK4_VALUE ' || '
,' || l_table_alias || '.PK5_VALUE ' || '
';
||' and (select wsbxyz.enabled_flag from wms_strategies_b wsbxyz where '
||l_table_alias||'.strategy_id = wsbxyz.strategy_id) = ''Y'' ';
l_stmt := l_select || l_from || l_where || l_order_by;
SELECT NVL(SUM(transaction_quantity), 0)
, NVL(SUM(secondary_transaction_quantity), 0)
INTO l_other_alloc
, l_other_sec_alloc
FROM mtl_material_transactions_temp
WHERE move_order_line_id <> p_transaction_temp_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND transaction_action_id = 28
AND trx_source_line_id = p_trx_source_line_id;
SELECT source_line_set_id,source_header_id
into l_source_line_set_id,l_source_header_id
FROM wsh_delivery_details
WHERE source_line_id = p_trx_source_line_id
AND source_code = 'OE'
AND container_flag = 'N'
AND released_status <> 'D'
AND rownum = 1;
SELECT NVL(SUM(transaction_quantity), 0)
, NVL(SUM(secondary_transaction_quantity), 0)
INTO l_alloc_qty
, l_alloc_sec_qty
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.transaction_action_id = 28
AND mmtt.trx_source_line_id IN
(SELECT wdd.source_line_id FROM wsh_delivery_details wdd
WHERE wdd.source_line_set_id =l_source_line_set_id
AND wdd.source_line_id <> p_trx_source_line_id
AND wdd.source_code = 'OE'
AND wdd.container_flag = 'N'
AND wdd.source_header_id = l_source_header_id) ;
SELECT SUM(wdd.requested_quantity)
, NVL(SUM(wdd.requested_quantity2),0)
INTO l_requested_qty
, l_requested_sec_qty
FROM wsh_delivery_details wdd
WHERE wdd.source_line_set_id =l_source_line_set_id
AND wdd.released_status NOT IN ( 'C','Y','D')
AND wdd.source_line_id <> p_trx_source_line_id
AND wdd.source_code = 'OE'
AND wdd.container_flag = 'N'
AND wdd.source_header_id =l_source_header_id
AND EXISTS (SELECT 1 FROM mtl_material_transactions_temp
WHERE wdd.source_line_id = trx_source_line_id) ;
SELECT NVL(SUM(transaction_quantity), 0)
, NVL(SUM(secondary_transaction_quantity), 0)
INTO l_cur_mo_alloc
, l_cur_mo_sec_alloc
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_transaction_temp_id;
SELECT 'Y'
FROM WMS_TRANSACTIONS_TEMP
WHERE TYPE_CODE = p_type_code
AND LINE_TYPE_CODE = 2;
select NVL(WMS_ENABLED_FLAG, 'N') INTO l_wms_enabled_flag
from mtl_parameters
where ORGANIZATION_ID = l_organization_id;
SELECT 'Y'
INTO WMS_RULE_PVT.g_over_allocation
FROM mtl_txn_request_lines mtrl
WHERE mtrl.txn_source_line_id = l_trx_source_line_id
AND mtrl.transaction_source_type_id IN (2,8)
AND mtrl.LINE_ID <> p_transaction_temp_id
AND mtrl.secondary_quantity_detailed IS NOT NULL
AND mtrl.inventory_item_id = l_inventory_item_id
AND mtrl.organization_id = l_organization_id
AND mtrl.line_status in (3,7) --11711464
HAVING SUM(mtrl.secondary_quantity - NVL(mtrl.secondary_quantity_delivered, 0))
< (SELECT SUM(NVL(mmtt.secondary_transaction_quantity, 0))
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.trx_source_line_id = l_trx_source_line_id
AND mmtt.move_order_line_id <> p_transaction_temp_id
AND mmtt.inventory_item_id = l_inventory_item_id
AND mmtt.organization_id = l_organization_id
AND mmtt.transaction_action_id = 28);
SELECT 'Y'
INTO WMS_RULE_PVT.g_over_allocation
FROM mtl_txn_request_lines mtrl
WHERE mtrl.txn_source_line_id = l_trx_source_line_id
AND mtrl.transaction_source_type_id IN (2,8)
AND mtrl.LINE_ID <> p_transaction_temp_id
AND mtrl.quantity_detailed IS NOT NULL
AND mtrl.inventory_item_id = l_inventory_item_id
AND mtrl.organization_id = l_organization_id
AND mtrl.line_status in (3,7) --11711464
HAVING SUM(mtrl.quantity - NVL(mtrl.quantity_delivered, 0))
< (SELECT SUM(NVL(mmtt.transaction_quantity, 0))
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.trx_source_line_id = l_trx_source_line_id
AND mmtt.move_order_line_id <> p_transaction_temp_id
AND mmtt.inventory_item_id = l_inventory_item_id
AND mmtt.organization_id = l_organization_id
AND mmtt.transaction_action_id = 28);
wms_parameter_pvt.g_locator_item_quantity.DELETE;
wms_parameter_pvt.g_bulkCollect_Locator.DELETE;
wms_parameter_pvt.g_bulkCollect_quantity.DELETE;