Update PGVP vendor ID data in dev, test, perf, and beta environments

[bryanramirez1]

Beta users (RT 75886) running into vendorid issues as it relates to PGVP vendor ids being outdated in the database. Need to merge updated data in all environments to fix issue.

[djw4erg]

Process

1. Use "ProtocolGasVendor.GenerateMergeSelects.sql" to produce the selects statements of current Protocol Gas Vendors.
   • Run the script on CAMDBSP.
   • Remove the "union all" on the last select produced before using below as the select core for other queries.
2. Copy "ProtocolGasVendor.NeededDelete.Template.sql" and update the copy with the results from Step 1.
   • Run the updated copied script on the target EASEY database.
   • Investigate any rows returned by the script before preceding. Manual updates may be needed.
3. Copy "ProtocolGasVendor.Difference.Template.sql" and update the copy with the results from Step 1.
   • Run the updated copied script on the target EASEY database.
   • The results should have one row with a SOURCE_CD of "NEW" for any new vendor.
   • The results should have two rows with SOURCE_CD values of "NEW" and "OLD" for any vendor with changes.
   • The results should have on row with a SOURCE_CD of "OLD" for any vendor deleted from CAMDBSP since the last update.
     • This should match the results from Step 2.
   • Data either in the CAMDBSP or EASEY version of PROTOCOL_GAS_VENDOR that are not produced by this query have the same values in the two databases.
4. Copy "ProtocolGasVendor.Update.Template.sql" and update the copy with the results from Step 1.
   • Run the updated copied script on the target EASEY database.
   • This script will update the "EASEY" database to match "CAMDBSP".
   • A reran Step 3 should eliminate additions and updates.

Note: The updated scripts for Steps 2, 3 and 4 can be reused on multiple EASEY databases.

Scripts

ProtocolGasVendor.GenerateMergeSelects.sql

select  pgv.*,
        ( 
            '            '
            || 'select '
            || '''' || pgv.Vendor_Id || ''' as Vendor_Id, ' 
            || '''' || pgv.Vendor_Name || ''' as Vendor_Name, '
            || decode(  pgv.Activation_Date, null, 'NULL', 'to_date( ''' || to_char( pgv.Activation_Date, 'yyyy-mm-dd') || ''', ''yyyy-mm-dd'' )' ) || ' as Activation_Date, '
            || decode(  pgv.Deactivation_Date, null, 'NULL', 'to_date( ''' || to_char( pgv.Deactivation_Date, 'yyyy-mm-dd') || ''', ''yyyy-mm-dd'' )' ) || ' as Deactivation_Date, '
            || pgv.Active_Ind || ' as Active_Ind, '
            || decode(  pgv.Userid, null, 'NULL', '''' || pgv.Userid || '''' ) || ' as Userid, '
            || decode(  pgv.Add_Date, null, 'NULL', 'to_timestamp( ''' || to_char( pgv.Add_Date, 'yyyy-mm-dd hh24:mi:ss') || ''', ''yyyy-mm-dd hh24:mi:ss'' )::timestamp without time zone' ) || ' as Add_Date, '
            || decode(  pgv.Update_Date, null, 'NULL', 'to_timestamp( ''' || to_char( pgv.Update_Date, 'yyyy-mm-dd hh24:mi:ss') || ''', ''yyyy-mm-dd hh24:mi:ss'' )::timestamp without time zone' ) || ' as Update_Date '
            || 'union all'
        ) as Sql_Statement
  from  PROTOCOL_GAS_VENDOR pgv
 order
    by  pgv.Add_Date,
        pgv.Vendor_Id

ProtocolGasVendor.NeededDelete.Template.sql

select  *
  from  camdecmps.PROTOCOL_GAS_VENDOR pgv
 where  not exists
        (
            select  1
              from  (
                        /* Replace with the select core. */
                    ) exs
             where  exs.Vendor_Id = pgv.Vendor_Id
        )

ProtocolGasVendor.Difference.Template.sql

with
    old as
    (
        select  pgv.Vendor_Id,
                pgv.Vendor_Name,
                pgv.Activation_Date,
                pgv.Deactivation_Date,
                pgv.Active_Ind,
                pgv.Userid,
                pgv.Add_Date,
                pgv.Update_Date
          from  camdecmps.PROTOCOL_GAS_VENDOR pgv
    ),
    new as
    (
        /* Replace with the select core. */
    )    
select  dif.Vendor_Id,
        dif.Source_Cd,
        dif.Vendor_Name,
        dif.Activation_Date,
        dif.Deactivation_Date,
        dif.Active_Ind,
        dif.Userid,
        dif.Add_Date,
        dif.Update_Date
  from  (
            select  dat.Vendor_Id,
                    'NEW' as Source_Cd,
                    dat.Vendor_Name,
                    dat.Activation_Date,
                    dat.Deactivation_Date,
                    dat.Active_Ind,
                    dat.Userid,
                    dat.Add_Date,
                    dat.Update_Date
              from   ( select * from new except select * from old ) dat
            union
            select  dat.Vendor_Id,
                    'OLD' as Source_Cd,
                    dat.Vendor_Name,
                    dat.Activation_Date,
                    dat.Deactivation_Date,
                    dat.Active_Ind,
                    dat.Userid,
                    dat.Add_Date,
                    dat.Update_Date
              from  ( select * from old except select * from new ) dat
        ) dif
 order
    by  dif.Vendor_Id,
        dif.Source_Cd

ProtocolGasVendor.Update.Template.sql

 merge
  into  camdecmps.PROTOCOL_GAS_VENDOR snk
 using  (
            /* Replace with the select core. */
        ) src
    on  ( snk.Vendor_Id = src.Vendor_Id )
  when  matched then
            update
               set  snk.Vendor_Name         = src.Vendor_Name,
                    snk.Activation_Date     = src.Activation_Date,
                    snk.Deactivation_Date   = src.Deactivation_Date,
                    snk.Active_Ind          = src.Active_Ind,
                    snk.Userid              = src.Userid,
                    snk.Add_Date            = src.Add_Date,
                    snk.Update_Date         = src.Update_Date
  when  not matched then
            insert  (
                        Vendor_Id,
                        Vendor_Name,
                        Activation_Date,
                        Deactivation_Date,
                        Active_Ind,
                        Userid,
                        Add_Date,
                        Update_Date
                    )
            values  (
                        src.Vendor_Id,
                        src.Vendor_Name,
                        src.Activation_Date,
                        src.Deactivation_Date,
                        src.Active_Ind,
                        src.Userid,
                        src.Add_Date,
                        src.Update_Date
                    );

commit;