Back to Home

Skip BO Review (GSP Measurement)

Overview

For GSP Measurement, business requests that we have audits skip BO Review and move directly to QR Recheck

Steps

1. Run Script 1 to ensure there are audits to be moved directly to QR Recheck
2. Add Script 2 as a SAFE script to be run

Script 1

SELECT UniqueInstanceNo, BONo, InspectionTypeNo FROM [smart].[p11_IntakeForm] where PendReasonList = '-43-,' and stageno = 2

Script 2

declare @UniqueInstanceNo int, @BONo int, @InspectionTypeNo int, @PendReasonList varchar(10) = '-43-,'
declare MyCursor cursor for
SELECT UniqueInstanceNo, BONo, InspectionTypeNo FROM [smart].[p11_IntakeForm] where PendReasonList = @PendReasonList and stageno = 2
open MyCursor;
    fetch next from MyCursor into @UniqueInstanceNo, @BONo, @InspectionTypeNo
    while @@fetch_status = 0
    begin
        declare @Driver int, @RootCause int

        select top 1 @Driver = LookupNo from smart.p11_CodeList where LookupCategory= 'driver' and Option7 = @InspectionTypeNo
        select top 1 @RootCause = LookupNo from smart.p11_CodeList where LookupCategory= 'root cause' and Option7 = @InspectionTypeNo

        -- update pend history
        insert into smart.p11_PendHistory
       ([UniqueInstanceno], [DateEntered], [TransactionMode], [UserNo], [PendReasonList], [PendReasonNotes], [Deleted], [PendFrom], [PendID])
        select top 1 [UniqueInstanceno], getdate(), 'U', [UserNo], '', 'Unpended by System', 0, null, [PendID] from smart.p11_PendHistory where UniqueInstanceno = @UniqueInstanceNo and TransactionMode = 'P' and PendReasonList = @PendReasonList order by DateEntered desc

        update smart.p11_intakeform set PendReasonList = null, PendReasonNotes = 'Unpended by System', HoldStatus = 0, InventoryNo = 3, DatePend = getdate() where UniqueInstanceNo = @UniqueInstanceNo

        -- insert audit activity for bo response
        INSERT [smart].[p11_AuditActivity] ([UniqueAuditFieldNo], [ResponseNo], [UserNo], [DateEntered], [Comments], [Deleted], [StageNo], [SequenceNo], [DriverNo], [RootCauseNo1], [CurrentStatus], [RootCauseNo2], [RootCauseNo3])
        select [UniqueAuditFieldNo], 1, @BONo, getdate(), N'Auto Responded by System', 0, 4, 1, @Driver, @RootCause, 1, 0, 0 from smart.p11_AuditChecklist_FieldList where UniqueInstanceNo1 = @UniqueInstanceNo and deleted = 0 and BOResponseNo = 0 and AuditorResponseNo = 0

        -- update audit checklist fieldlist
        update smart.p11_AuditChecklist_FieldList set boresponseno = 1, lastcomments = '', DriverNo = @Driver, RootCauseNo1 = @RootCause, RootCauseNo2 = 0, RootCauseNo3 = 0 where UniqueInstanceNo1 = @UniqueInstanceNo and deleted = 0 and BOResponseNo = 0 and AuditorResponseNo = 0

        -- update intakeform
        update smart.p11_intakeform set datesubmitted = getdate(), inventoryno = 7,stageno = 3 where UniqueInstanceNo = @UniqueInstanceNo

        -- run update summary
        exec smart.p11_Update_Summary @UniqueInstanceNo

        fetch next from  MyCursor into  @UniqueInstanceNo, @BONo, @InspectionTypeNo
    end
close MyCursor;
deallocate MyCursor;