Sample PLSQL program that calls an API to set a flag

Your administrator can create flags, each with multiple states, to assign to records to track anything you want.

To use flags to help in the review process of individual subject data records, write a validation check to examine records and apply a particular flag state depending on the data in the record. for example:

• Write a validation check to ascertain that all required fields have a value and set a flag called Record Complete to On if they do.

• Write a validation check that compares the timestamp of the last data update for each unlocked record to the execution timestamp for all validation checks run for that study and sets the Validation Checks flag for each record with the appropriate state: Validation Incomplete, Validation Complete, or, if the record is locked, Record Locked.

The following sample program reads flags on source table data, and if the flag is 'Complete' for a row, it inserts that row into the target table, and on the target table it assigns the 'Complete' flag to all rows.

CREATE OR REPLACE PACKAGE VITALS_PKG AS
  PROCEDURE loadVitals;
END VITALS_PKG ;
/
 
CREATE OR REPLACE PACKAGE BODY VITALS_PKG AS
  PROCEDURE loadVitals IS 
  x_return_status VARCHAR2(1);
  x_msg_count     NUMBER;
  x_msg_data      VARCHAR2(1000);
  oFlagName dme_flag_name_type;
  vFlagState varchar2(100);
  BEGIN
    -- get the flag
    dme_pub_flag_name.getFlagName(
              p_api_version => 1.0
            , p_init_msg_list => CDR_PUB_DEF_CONSTANTS.G_FALSE
            , p_commit => CDR_PUB_DEF_CONSTANTS.G_TRUE
            , p_validation_level => CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
            , x_return_status => x_return_status
            , x_msg_count => x_msg_count
            , x_msg_data => x_msg_data
            , pi_company_id => cdr_pub_def_constants.current_company_id
            , pi_flag_namestr => 'Completeness'
            , pio_dme_flag_name => oFlagName
    );
    for row in (select 
      STUDYID,
      SITEID,
      SUBJID,
      VISITNUM,
      INITIALS,
      BIRTHDT,
      HEIGHT,
      HEIGHTU,
      WEIGHT,
      WEIGHTU,
      CDR$SKEY
     from vitals
    ) loop
      dme_pub_flag_data.getFlag(
          p_api_version => 1.0
        , p_init_msg_list => CDR_PUB_DEF_CONSTANTS.G_FALSE
        , p_commit => CDR_PUB_DEF_CONSTANTS.G_TRUE
        , p_validation_level => CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
        , x_return_status => x_return_status
        , x_msg_count => x_msg_count
        , x_msg_data => x_msg_data
        , pi_company_id => cdr_pub_def_constants.current_company_id
        , pi_tab_obj_id => cdr_pub_df_mapping.GET_TAB_INST_ID('VITALS')
        , pi_skey_value => row.cdr$skey
        , pi_flag_id => oFlagName.flag_id
        , po_flag_state => vFlagState
      );
      
      if vFlagState = 'Complete' then
        insert into vitals_tgt (
          STUDYID,
          SITEID,
          SUBJID,
          VISITNUM,
          INITIALS,
          BIRTHDT,
          HEIGHT,
          HEIGHTU,
          WEIGHT,
          WEIGHTU,
          SOURCE_KEY
         ) 
         values (row.studyid, row.siteid, row.subjid, row.visitnum, row.initials, row.birthdt, row.height, row.heightu, row.weight, row.weightu, row.cdr$skey);
      end if;
   end loop;
   for row in (select * from vitals_tgt) loop
      dme_pub_flag_data.setFlag(
          p_api_version => 1.0
        , p_init_msg_list => CDR_PUB_DEF_CONSTANTS.G_FALSE
        , p_commit => CDR_PUB_DEF_CONSTANTS.G_TRUE
        , p_validation_level => CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL
        , x_return_status => x_return_status
        , x_msg_count => x_msg_count
        , x_msg_data => x_msg_data
        , pi_company_id => cdr_pub_def_constants.current_company_id
        , pi_tab_obj_id => cdr_pub_df_mapping.GET_TAB_INST_ID('VITALS_TGT')
        , pi_skey_value => row.cdr$skey
        , pi_flag_id => oFlagName.flag_id
        , pi_flag_state => 'Complete'
      );
   end loop;
  END loadVitals;
END VITALS_PKG;
/