O2OFramework/sql/DTReadOutMapping_payload_o2o.sql

0001 /*
0002  *  DTReadOutMapping_payload_o2o()
0003  *
0004  *  DTReadOutMapping transform/transfer
0005  *  Parameters:  last_id:  The lower bounding IOV_VALUE_ID for objects to transfer
0006  *
0007  *  Note:  Uses sequence on ORCON for IOV_VALUE_ID
0008  *         Transfers 1 object per call
0009  */
0010
0011 /* A sequence for the DTReadoutMapping object IOV_VALUE_ID */
0012 DROP SEQUENCE dtread_id_sq;
0013 CREATE SEQUENCE dtread_id_sq
0014 START WITH 1
0015 INCREMENT BY 1
0016 ;
0017
0018 CREATE OR REPLACE PROCEDURE DTReadOutMapping_payload_o2o (
0019   last_id IN NUMBER
0020 )
0021 AS
0022
0023 BEGIN
0024 INSERT INTO dtreadoutmapping
0025             (iov_value_id,cell_map_version,rob_map_version,time)
0026      VALUES ( dtread_id_sq.NextVal, 'CMSSW_CELL','CMSSW_ROS', NULL )
0027 ;
0028
0029 INSERT INTO dtreadoutconnection
0030             (wheel,station,sector,superlayer,layer,cell,
0031              ddu,ros,rob,tdc,channel,
0032              connection_id,iov_value_id)
0033 SELECT dt_wheel_numbering.object_number,
0034        dt_chamber_numbering.station_number,
0035        dt_chamber_numbering.sector_number,
0036        dt_superlayer_numbering.object_number,
0037        dt_layer_numbering.object_number,
0038        dt_cell_numbering.object_number,
0039        dt_ddu.read_out_number,
0040        dt_ros.read_out_number,
0041        dt_ros_channel.read_out_number,
0042        dt_tdc.read_out_number,
0043        dt_tdc_channel.read_out_number,
0044        rownum, dtread_id_sq.CurrVal
0045   FROM dt_wheel_numbering@omds       dt_wheel_numbering,
0046        dt_chamber_numbering@omds     dt_chamber_numbering,
0047        dt_superlayer_numbering@omds  dt_superlayer_numbering,
0048        dt_layer_numbering@omds       dt_layer_numbering,
0049        dt_cell_numbering@omds        dt_cell_numbering,
0050        dt_wheel@omds                 dt_wheel,
0051        dt_sector@omds                dt_sector,
0052        dt_chamber@omds               dt_chamber,
0053        dt_superlayer@omds            dt_superlayer,
0054        dt_layer@omds                 dt_layer,
0055        dt_cell@omds                  dt_cell,
0056        dt_cell_readout_map@omds      dt_cell_readout_map,
0057        dt_tdc_channel@omds           dt_tdc_channel,
0058        dt_tdc@omds                   dt_tdc,
0059        dt_rob@omds                   dt_rob,
0060        dt_rob_ros_connection@omds    dt_rob_ros_connection,
0061        dt_ros_channel@omds           dt_ros_channel,
0062        dt_ros@omds                   dt_ros,
0063        dt_ddu@omds                   dt_ddu,
0064        (SELECT numbering_id FROM dt_numbering_master@omds
0065          WHERE numbering_version='CMSSW_NUMBERING') dt_num,
0066        (SELECT connection_id FROM dt_connection_master@omds
0067          WHERE connection_type='DT_CELL_READOUT_CONNECTION'
0068            AND mapping_version='CMSSW_CELL') rob_con,
0069        (SELECT connection_id FROM dt_connection_master@omds
0070          WHERE connection_type='DT_ROB_ROS_CONNECTION'
0071            AND mapping_version='CMSSW_ROS') ros_con,
0072         dual /* No idea why this is needed, but it is (sequence?) */
0073   WHERE dt_cell_readout_map.cell_id=dt_cell.cell_id
0074     AND dt_cell.layer_id=dt_layer.layer_id
0075     AND dt_layer.sl_id=dt_superlayer.sl_id
0076     AND dt_superlayer.chamber_id=dt_chamber.chamber_id
0077     AND dt_chamber.sector_id=dt_sector.sector_id
0078     AND dt_sector.wheel_id=dt_wheel.wheel_id
0079     AND dt_wheel_numbering.wheel_id=dt_wheel.wheel_id
0080     AND dt_chamber_numbering.chamber_id=dt_chamber.chamber_id
0081     AND dt_superlayer_numbering.sl_id=dt_superlayer.sl_id
0082     AND dt_layer_numbering.layer_id=dt_layer.layer_id
0083     AND dt_cell_numbering.cell_id=dt_cell.cell_id
0084     AND dt_cell_readout_map.tdc_channel_id=dt_tdc_channel.tdc_channel_id
0085     AND dt_tdc_channel.tdc_id=dt_tdc.tdc_id
0086     AND dt_tdc.rob_id=dt_rob.rob_id
0087     AND dt_rob.rob_id=dt_rob_ros_connection.rob_id
0088     AND dt_ros_channel.ros_channel_id=dt_rob_ros_connection.ros_channel_id
0089     AND dt_ros_channel.ros_id=dt_ros.ros_id
0090     AND dt_ros.ddu_id=dt_ddu.ddu_id
0091     AND dt_cell_readout_map.connection_id=rob_con.connection_id
0092     AND dt_rob_ros_connection.connection_id=ros_con.connection_id
0093     AND dt_wheel_numbering.numbering_id=dt_num.numbering_id
0094     AND dt_chamber_numbering.numbering_id=dt_num.numbering_id
0095     AND dt_superlayer_numbering.numbering_id=dt_num.numbering_id
0096     AND dt_layer_numbering.numbering_id=dt_num.numbering_id
0097     AND dt_cell_numbering.numbering_id=dt_num.numbering_id
0098 ;
0099
0100 END;
0101 /
0102 show errors;