Wiki

New Case Case Status Kiln
Log In

Wiki

 
Classification  Renumber Scripts»HQ Abbr Renumber
  
Page Collections
Attributes
Hierarchy
  • RSS Feed

Last modified on 4/9/2013 1:40 PM by User.

Tags:

HQ Abbr Renumber

 Just find and replace the renumber_id (?) with the actual renumber_id in dbo_renumbers table 

One-to-One Relationship

   --1a.  LOG THE CURRENT STATE OF THE OLD LINE

  DECLARE V_GROUP_ID INT;
      BEGIN
       
       --Update assigned_to and title
      INSERT INTO DBO_class_transaction.transaction_group(release_date, status_id , assigned_to, title, transaction_type)
      values(sysdate, 3, 19, ' EXC-EXT Renumber (Old Parts)' ,9);
       
      SELECT max(group_id) INTO V_GROUP_ID  
      FROm DBO_class_transaction.transaction_group WHERE assigned_to = 19  AND transaction_type = 9;
 
      --Update renumber id
      INSERT INTO DBO_class_transaction.transaction_key (hq_abbr, part_number, group_id)
      SELECT distinct hq_abbr, part_number, V_GROUP_ID 
      FROM DBO_CLASSIFICATIONDATA.renumber_part rp, DBO_CLASSIFICATIONDATA.part_master pm 
      WHERE pm.hq_abbr = old_hq_abbr 
      AND pm.part_number = old_part_number
      AND renumber_id  IN  (?);
       
      DBO_class_transaction.create_complete_log(V_GROUP_ID);
       
      DELETE FROm DBO_class_transaction.transaction_key where group_id = V_GROUP_ID;
      DELETE FROm DBO_class_transaction.transaction_group where group_id = V_GROUP_ID;
       
      END;
      
  --1b.  CHECK PART VIEWER & LOGS
  
  --2a.  CHANGE THE ABBR OF THE PART NUMBERS
  
        --Update renumber id and new line abbr
        UPDATE DBO_CLASSIFICATIONDATA.part_master pm
        SET hq_abbr = (SELECT new_hq_abbr FROM DBO_CLASSIFICATIONDATA.renumber_part rp where renumber_id = ? and rp.old_hq_abbr=pm.hq_abbr and rp.old_part_number = pm.part_number )
        WHERE pm.HQ_ABBR = 'EXH'
 
  --2b.  CHECK RECORD COUNT AFTER UPDATE
 
        select COUNT(*) from DBO_CLASSIFICATIONDATA.part_master pm where pm.HQ_ABBR = 'EXT'
        new_hq_abbr='EXC'; --1566 PARTS
        new_hq_abbr='EXT'; --7145 PARTS
        
  --3a. LOG THE STATE OF THE NEW NUMBERS
  
        DECLARE V_GROUP_ID INT;
        BEGIN
         --Update assigned_to and title
        INSERT INTO DBO_class_transaction.transaction_group(release_date, status_id , assigned_to, title, transaction_type)
         values(sysdate, 3, 19, ' EXC-EXT Renumber (New Numbers)' ,9);
      
        SELECT max(group_id) INTO V_GROUP_ID  
        FROm DBO_class_transaction.transaction_group WHERE assigned_to = 19  AND transaction_type = 9;
         --Update renumber id
        INSERT INTO DBO_class_transaction.transaction_key (hq_abbr, part_number, group_id)
        SELECT distinct hq_abbr, part_number, V_GROUP_ID 
        FROM DBO_CLASSIFICATIONDATA.renumber_part rp, DBO_CLASSIFICATIONDATA.part_master pm 
        WHERE pm.hq_abbr = new_hq_abbr 
        AND pm.part_number = new_part_number
        AND renumber_id  IN  (?);
         
        DBO_class_transaction.create_complete_log(V_GROUP_ID);
         
        DELETE FROm DBO_class_transaction.transaction_key where group_id = V_GROUP_ID;
        DELETE FROm DBO_class_transaction.transaction_group where group_id = V_GROUP_ID;
         
        END;
 
  --3b.  CHECK PART VIEWER & LOGS
 
  --4a.  Create a delete bulletin that gets sent out for the old numbers.  This is not to delete anything on our side but to let the field know these numbers are gone.
       
--Query used for delete bulletin of old part numbers,  --BULLETIN: 13-D20(CASE 1861), 8711 PARTS
       a. SELECT old_hq_abbr, old_part_number FROM DBO_CLASSIFICATIONDATA.renumber_part where renumber_id = ?
    
        b. Modified Pup project in eclipse to comment out code below in com.napa.pup.bulletin. BulletinReleaser, preformWork method, so I can release bulletin without errors
       @Override
            public void preformWork(Bulletin bulletin) throws DisplayableException {
 
            /*Check for errors*/
            List<ReportQuery> errorQueries = new LinkedList<ReportQuery>();
            for(ReportQuery query:ReportManager.getQueries(bulletin)){
            if(query.isReleaseError()){
            errorQueries.add(query);
            }
            }
            //if(!ReportManager.runReport(bulletin.getBulletinId(), errorQueries).isEmpty()){
            // throw new DisplayableException("Bulletin "+bulletin+" has errors that prevent its release (Run validation report).");
            //}
           
        c.  Run Pup locally in eclipse, no need to commit changes
        d.  After Bulletin releases, check dbo_class_transaction tabkes 
 
SELECT * FROM DBO_CLASS_TRANSACTION.TRANSACTION_GROUP where ASSIGNED_TO=19 order by release_date desc 
              
e.  NO LOGS IN LOG_VIEWER BUT XML FILE CREATED 
     Example:  J:\transaction_mover\transfer_files\(43363) Bulletin 13-D20Case 1861.XML
            
 --5. Update DC Stocking
 --6. Afterwards
      a. Update DC stocking where needed
      b. Update recipient data where needed (Send email to Kim)
      c. Delete old product line and group codes if needed (?iF needed)
      d. Send out a class refresh through XML  the next week if needed