SET @PersonalNo = '104938091'
SET @PrescriptionNo = 17564
SET @Status = 0
SELECT G1PrescriptionId, PersonalNo, InsertDate, PrescriptionNo, * FROM Prescription WHERE (PersonalNo = @PersonalNo) AND (PrescriptionNo = @PrescriptionNo) ORDER BY InsertDate DESC
SET @G1PrescriptionId = ( SELECT G1PrescriptionId FROM Prescription WHERE (PersonalNo = @PersonalNo) AND (PrescriptionNo = @PrescriptionNo) )
SELECT G1PrescriptionId, * FROM Prescription WHERE G1PrescriptionId = @G1PrescriptionId
SET @G1PrescriptionId = (SELECT G1PrescriptionId FROM Prescription WHERE G1PrescriptionId = @G1PrescriptionId AND G1PrescriptionId IS NOT NULL AND Status = @Status)
SELECT G1PrescriptionId, SendResultStatus, ResultId, SendReportStatus, ReportId, * FROM G1Prescription WHERE G1PrescriptionId = @G1PrescriptionId
SELECT AckCode, [Text], * FROM G1PrescResult WHERE G1PrescriptionId = @G1PrescriptionId
SELECT AckCode, [Text], XmlError, MessageId, * FROM G1PrescReport WHERE G1PrescriptionId = @G1PrescriptionId
SELECT DocType, AckCode, [Text], XmlError, MessageId, * FROM G1PrescCancel WHERE G1PrescriptionId = @G1PrescriptionId
SET @ResultId = (SELECT TOP 1 ResultId FROM G1PrescResult WHERE G1PrescriptionId = @G1PrescriptionId AND AckCode = 'Accepted' AND [Text] = '' ORDER BY DeliveryDate DESC)
SET @ReportId = (SELECT TOP 1 ReportId FROM G1PrescReport WHERE G1PrescriptionId = @G1PrescriptionId AND AckCode = 'Accepted' AND [Text] = '' AND XmlError = '' ORDER BY ReportDate DESC)
BEGIN TRANSACTION
UPDATE G1Prescription SET SendResultStatus = 1, ResultId = '@ResultId' WHERE G1PrescriptionId = @G1PrescriptionId
UPDATE G1Prescription SET SendReportStatus = 1, ReportId = '@ReportId' WHERE G1PrescriptionId = @G1PrescriptionId
SELECT G1PrescriptionId, SendResultStatus, ResultId, SendReportStatus, ReportId, * FROM G1Prescription WHERE G1PrescriptionId = @G1PrescriptionId
ROLLBACK
--COMMIT