[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_param_tbl.delete;
select 1
into l_excluded
from bom_std_op_resp_exclusions bsore
where standard_operation_id = p_standard_op_id
and responsibility_id = p_responsibility_id;
select NVL(move_in_option, 0), -- default: optional
NVL(move_to_next_op_option, 0) -- default: optional
into l_move_in_option,
l_move_next_option
from wsm_parameters wp
where organization_id = p_org_id;
select NVL(use_org_settings, 1), -- default: use org settings
NVL(queue_mandatory_flag, 0), -- default: no
NVL(run_mandatory_flag, 0), -- default: no
NVL(to_move_mandatory_flag, 0) -- default: no
into l_use_org_settings,
l_queue_mandatory,
l_run_mandatory,
l_to_move_mandatory
from bom_standard_operations bso
where standard_operation_id = p_standard_op_id;
* 2^6 = 64 Update Assembly
* 2^7 = 128 Update Routing
* 2^8 = 256 Update Lot Name
* 2^9 = 512 Update Quantity
* 2^10 = 1024 Transact Materials
* 2^11 = 2048 Jump To Operation
* 2^12 = 4096 Undo Move
* 2^15 = 32768 Change component during backflush
* 2^16 = 65536 Move In
* 2^17 = 131072 Move Out
* 2^18 = 262144 Move To Next Op
*/
procedure wsm_transaction_allowed(
p_transaction_type in number,
p_responsibility_id in number,
p_wip_entity_id in number,
p_org_id in number,
p_job_op_seq_num in number,
p_standard_op_id in number,
p_intraop_step in number,
p_status_type in number,
x_allowed out nocopy number,
x_error_msg_name out nocopy varchar2
) is
l_excluded number;
l_param_tbl.delete;
select nvl(internal_copy_type,0)
into l_internal_copy_type
from wsm_lot_based_jobs
where wip_entity_id = p_wip_entity_id;
select allow_backward_move_flag
into l_org_allow_undo
from wsm_parameters wp
where organization_id = p_org_id;
select max(transaction_id)
into l_txn_id
from wip_move_transactions
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and wsm_undo_txn_id IS NULL;
select source_code
into l_undo_source_code
from wip_move_transactions
where transaction_id = l_txn_id;
select max(operation_seq_num)
into l_max_op_seq_num
from wip_operations
where wip_entity_id = p_wip_entity_id;
select max(transaction_date)
into l_max_move_txn_date
from wip_move_transactions
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and wsm_undo_txn_id IS NULL;
select max(wsmt.transaction_date)
into l_max_wlt_txn_date
FROM wsm_split_merge_transactions wsmt,
wsm_sm_starting_jobs wssj
WHERE wsmt.transaction_id = wssj.transaction_id
AND wssj.wip_entity_id = p_wip_entity_id;
select charge_jump_from_queue
into l_charge_jump_from_queue
from wsm_parameters
where organization_id = p_org_id;
select 1
into l_excluded
from bom_std_op_resp_exclusions bsore
where standard_operation_id = p_standard_op_id
and responsibility_id = p_responsibility_id;
select bitand(code_mask, p_transaction_type)
into l_txn_allowed
from wsm_responsibility_settings wrs
where responsibility_id = p_responsibility_id;
SELECT current_rtg_op_seq_num
INTO l_routing_op_seq_num
FROM wsm_lot_based_jobs
WHERE wip_entity_id = p_wip_entity_id;
SELECT DECODE(SUM(DECODE(WVIS.STEP_LOOKUP_TYPE,2,1,0)),0,2,1),
DECODE(SUM(DECODE(WVIS.STEP_LOOKUP_TYPE,3,
DECODE(WVIS.RECORD_CREATOR,'USER',1,0),0)),0,2,1)
INTO l_wip_run_enabled_flag, l_wip_to_move_enabled_flag
--FROM WIP_PARAMETERS_V
FROM WIP_VALID_INTRAOPERATION_STEPS WVIS
WHERE organization_id = p_org_id;
SELECT nvl(move_in_option, 0), nvl(move_to_next_op_option, 0) --bugfix 5336838 changed from default 2 to 0
INTO l_wsm_move_in, l_wsm_move_to_next_op
FROM WSM_PARAMETERS
WHERE organization_id = p_org_id;
-- Note: we do not really need a check on bos vs. bso because bso changes are updated to bos immediately.
-- however leaving this as-is as we may support routing-level changes in the future; move_txn_allowed would need to be changed.
SELECT nvl(BOS.use_org_settings, 1), nvl(BOS.run_mandatory_flag, 0), nvl(BOS.to_move_mandatory_flag, 0)
INTO l_op_use_org_settings, l_op_run_mandatory_flag, l_op_to_move_mandatory_flag
FROM BOM_OPERATION_SEQUENCES BOS, WIP_OPERATIONS WO
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_job_op_seq_num
AND BOS.operation_sequence_id = WO.operation_sequence_id;
SELECT nvl(BSO.use_org_settings, 1), nvl(BSO.run_mandatory_flag, 0), nvl(BSO.to_move_mandatory_flag, 0)
INTO l_op_use_org_settings, l_op_run_mandatory_flag, l_op_to_move_mandatory_flag
FROM BOM_STANDARD_OPERATIONS BSO, WIP_OPERATIONS WO
WHERE WO.wip_entity_id = p_wip_entity_id
AND WO.operation_seq_num = p_job_op_seq_num
AND BSO.standard_operation_id = WO.standard_operation_id
AND BSO.organization_id = WO.organization_id;
SELECT 0
INTO l_excluded
FROM wsm_copy_op_networks
WHERE wip_entity_id = p_wip_entity_id
AND (from_op_seq_num = l_routing_op_seq_num
OR to_op_seq_num = l_routing_op_seq_num);
l_param_tbl.delete;
select we.wip_entity_name job_name,
wo.operation_seq_num job_op_seq_num,
wo.quantity_in_queue
+ wo.quantity_running
+ wo.quantity_waiting_to_move assembly_quantity,
wdj.status_type status_type,
case when wo.quantity_in_queue>0 then 1
when wo.quantity_running>0 then 2
when wo.quantity_waiting_to_move>0 then 3
else null end intraop_step_code
into l_job_name,
l_job_op_seq_num,
l_quantity,
l_status_type,
l_intraop_step
from wip_discrete_jobs WDJ,
wip_entities WE,
wip_operations WO
where WE.entity_type in (5, 8)
and WDJ.wip_entity_id = we.wip_entity_id
and WDJ.organization_id = we.organization_id
and WDJ.status_type in (3, 6)
and WO.wip_entity_id = WDJ.wip_entity_id
and WO.organization_id = WDJ.organization_id
and wo.quantity_in_queue
+ wo.quantity_running
+ wo.quantity_waiting_to_move > 0
and WDJ.WIP_ENTITY_ID = p_wip_entity_id;
select
we.wip_entity_name,
wdj.wip_entity_id,
wo.operation_seq_num,
wdj.status_type,
wlbj.current_rtg_op_seq_num
into l_job_name,
l_wip_entity_id,
l_op_seq_num,
l_status_type,
l_rtg_op_seq_num
from wip_discrete_jobs WDJ,
wip_entities WE,
wip_operations WO,
wip_operation_resources WOR,
wsm_lot_based_jobs WLBJ
where WE.entity_type in (5, 8)
and WDJ.wip_entity_id = we.wip_entity_id
and WDJ.organization_id = we.organization_id
and WDJ.status_type in (3, 6)
and WO.wip_entity_id = WDJ.wip_entity_id
and WO.organization_id = WDJ.organization_id
and WO.operation_seq_num = WOR.operation_seq_num
and WO.wip_entity_id = WOR.wip_entity_id
and WO.organization_id = WOR.organization_id
and WO.quantity_in_queue
+ WO.quantity_running
+ WO.quantity_waiting_to_move <> 0
and not exists (
select BDRI.instance_id
from BOM_DEPT_RES_INSTANCES BDRI
where BDRI.department_id = WO.department_id
and BDRI.resource_id = WOR.resource_id
and rownum = 1
)
and WDJ.organization_id = p_organization_id
and WO.department_id = p_department_id
and WOR.resource_id = p_resource_id
and WE.wip_entity_id = WLBJ.wip_entity_id
and WE.organization_id = WLBJ.organization_id;
select
we.wip_entity_name,
wdj.wip_entity_id,
wo.operation_seq_num,
wdj.status_type,
wlbj.current_rtg_op_seq_num
into l_job_name,
l_wip_entity_id,
l_op_seq_num,
l_status_type,
l_rtg_op_seq_num
from wip_discrete_jobs WDJ,
wip_entities WE,
wip_operations WO,
wip_operation_resources WOR,
wip_op_resource_instances WORI,
wsm_lot_based_jobs WLBJ
where WE.entity_type in (5, 8)
and WDJ.wip_entity_id = we.wip_entity_id
and WDJ.organization_id = we.organization_id
and WDJ.status_type in (3, 6)
and WO.wip_entity_id = WDJ.wip_entity_id
and WO.organization_id = WDJ.organization_id
and WO.operation_seq_num = WOR.operation_seq_num
and WO.wip_entity_id = WOR.wip_entity_id
and WO.organization_id = WOR.organization_id
and WOR.wip_entity_id = WORI.wip_entity_id
and WOR.operation_seq_num = WORI.operation_seq_num
and WOR.resource_seq_num = WORI.resource_seq_num
and WO.quantity_in_queue
+ WO.quantity_running
+ WO.quantity_waiting_to_move <> 0
and WDJ.organization_id = p_organization_id
and WO.department_id = p_department_id
and WOR.resource_id = p_resource_id
and WORI.instance_id = p_instance_id
and WORI.serial_number = p_serial_number
and WE.wip_entity_id = WLBJ.wip_entity_id
and WE.organization_id = WLBJ.organization_id;
* 2^6 = 64 Update Assembly
* 2^7 = 128 Update Routing
* 2^11 = 2048 Jump To Operation
* 2^12 = 4096 Undo Move
*/
function check_po_req_exists(
p_txn_type in number,
p_wip_entity_id in number
) return number is
l_charge_jump_from_queue number;
l_param_tbl.delete;
select organization_id,
current_job_op_seq_num
into l_org_id,
l_job_op_seq_num
from wsm_lot_based_jobs
where wip_entity_id = p_wip_entity_id;
SELECT nvl(charge_jump_from_queue,2)
INTO l_charge_jump_from_queue
FROM wsm_parameters
WHERE organization_id = l_org_id;
l_param_tbl.delete;
select SHARE_FROM_DEPT_ID
into l_share_from_dept
from BOM_DEPARTMENT_RESOURCES
where department_id = p_department_id
and resource_id = p_resource_id
and SHARE_FROM_DEPT_ID IS NOT NULL;