CST_WO_OP_DEPENDENCIES_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
CST_WORK_ORDER_ID FROM_CST_WO_OPERATION_ID TO_CST_WO_OPERATION_ID TRANSFER_PERCENT DEPENDENCY_TYPE FROM_WO_OPERATION_ID TO_WO_OPERATION_ID WORK_ORDER_ID FROM_OPERATION_SEQ_NUMBER TO_OPERATION_SEQ_NUMBER WORK_METHOD_CODE

Query

SQL_Statement

SELECT cst_work_order_id, from_cst_wo_operation_id, to_cst_wo_operation_id, act_transfer_percent transfer_percent, dependency_type, from_wo_operation_id, to_wo_operation_id, work_order_id, from_operation_seq_number, to_operation_seq_number, work_method_code FROM ( SELECT t.cst_work_order_id, t.from_cst_wo_operation_id, t.to_cst_wo_operation_id, t.transfer_percent, t.dependency_type, t.from_wo_operation_id, t.to_wo_operation_id, t.work_order_id, t.from_operation_seq_number, t.to_operation_seq_number, t.work_method_code, CASE WHEN t.work_method_code = 'PROCESS_MANUFACTURING' THEN t.transfer_percent ELSE ( CASE WHEN t.to_wo_operation_id IS NULL THEN NULL WHEN t.rk = t.cnt_of_op AND t.cnt_of_op > 1 THEN 1 - round(1 / t.cnt_of_op, 20) * ( t.cnt_of_op - 1 ) WHEN t.cnt_of_op > 1 THEN round(1 / t.cnt_of_op, 20) ELSE 1 END ) * 100 END act_transfer_percent FROM ( SELECT cwo.cst_work_order_id, CASE WHEN from_op.cst_work_order_operation_id IS NOT NULL THEN from_op.cst_work_order_operation_id WHEN op_dep.from_wo_operation_id IS NOT NULL THEN - 1 ELSE NULL END AS from_cst_wo_operation_id, CASE WHEN to_op.cst_work_order_operation_id IS NOT NULL THEN to_op.cst_work_order_operation_id WHEN op_dep.to_wo_operation_id IS NOT NULL THEN - 1 ELSE NULL END AS to_cst_wo_operation_id, op_dep.transfer_percent, CASE WHEN wm.work_method_code = 'DISCRETE_MANUFACTURING' THEN COUNT(1) OVER(PARTITION BY cwo.cst_work_order_id, op_dep.work_order_id, op_dep.from_wo_operation_id) ELSE 0 END cnt_of_op, DENSE_RANK() OVER(PARTITION BY cwo.cst_work_order_id, op_dep.work_order_id, op_dep.from_wo_operation_id ORDER BY op_dep.to_wo_operation_id ) rk, op_dep.dependency_type, op_dep.from_wo_operation_id, op_dep.to_wo_operation_id, op_dep.work_order_id, from_op.operation_seq_number from_operation_seq_number, to_op.operation_seq_number to_operation_seq_number, wm.work_method_code FROM wie_wo_op_dependencies op_dep, wis_work_methods_b wm, cst_work_orders cwo, cst_work_order_operations from_op, cst_work_order_operations to_op WHERE cwo.external_system_reference || '' = 'FUSION' AND cwo.enable_parallel_ops_flag = 'Y' AND cwo.work_method_id = wm.work_method_id AND TO_NUMBER(cwo.external_system_ref_id) = op_dep.work_order_id AND cwo.cst_work_order_id = from_op.cst_work_order_id (+) AND cwo.cst_work_order_id = to_op.cst_work_order_id (+) AND op_dep.from_wo_operation_id = from_op.work_order_operation_id (+) AND op_dep.dependency_type = 'ORA_FINISH_TO_START' AND op_dep.to_wo_operation_id = to_op.work_order_operation_id (+) ) t )