DECLARE @countCreditRequests INT, @countCreditApprovals INT, @countCreditDenials INT,   @whistle_CmApproverEmail VARCHAR(MAX);

SELECT @countCreditRequests = COUNT(*)  
FROM qcMiniatQC_ProductCreditRequestDetails   
WHERE Dialog_RequestStatus = 'Submitted'    
   AND Status_AddTime > DATEADD(dd,-1,@WhenScheduled)   
   AND Status_AddTime <= @WhenScheduled;

SELECT @countCreditApprovals = COUNT(*)  
FROM qcMiniatQC_ProductCreditRequestDetails   
WHERE Dialog_RequestStatus = 'Approved'    
   AND Status_AddTime > DATEADD(dd,-1,@WhenScheduled)   
   AND Status_AddTime <= @WhenScheduled;

SELECT @countCreditDenials = COUNT(*)  
FROM qcMiniatQC_ProductCreditRequestDetails   
WHERE Dialog_RequestStatus = 'Denied'    
   AND Status_AddTime > DATEADD(dd,-1,@WhenScheduled)   
   AND Status_AddTime <= @WhenScheduled;

SELECT @whistle_CmApproverEmail = VariableValue 
FROM wmVariableValues 
WHERE VariableName = 'Whistle_CmApproverEmail';

-- here’s the query against which bounds would be evaluated 
-- if there are any -- one event executed per row returned 
-- (which in this case is only one)
SELECT    
   @countCreditRequests AS countCreditRequests,   
   @countCreditApprovals AS countCreditApprovals,    
   @countCreditDenials AS countCreditDenials,   
   @whistle_CmApproverEmail AS Recipients,   
DATEADD(dd,-1,@WhenScheduled) AS beginPeriod;

Between @beginPeriod and @whenScheduled there were: @countCreditRequests request(s) for credit; @countCreditApprovals request(s) approved; @countCreditDenials request(s) denied.

DECLARE @counter INT;  
SELECT @counter = wms_counter_idx 
FROM wms_counter_tbl 
WHERE wms_counter_type = 100;  
IF @@ROWCOUNT = 0  
BEGIN
   INSERT wms_counter_tbl (wms_counter_type, 
      wms_counter_idx)   
   VALUES (100, 0)  
END;  
BEGIN TRANSACTION;  
UPDATE wms_counter_tbl 
SET wms_counter_idx = wms_counter_idx + 1   
WHERE wms_counter_type = 100;  

-- this is the query whence we will get parameters for the 
-- SQL to be executed as part of the event
SELECT wms_counter_idx AS GroupId -- we will use @GroupId in the ContentTemplate
FROM wms_counter_tbl 
WHERE wms_counter_type = 100;  
COMMIT;

DECLARE @txGuid UNIQUEIDENTIFIER, @runGuid UNIQUEIDENTIFIER  
DECLARE @facility VARCHAR(50)  
DECLARE @queue VARCHAR(50)  
SET @facility = 'ME/00/HS/MC/qcdemo/WHISTLESERVER'  
SET @queue = 'Global/Main'      

-- inject shipping extract transaction into queue    
SET @txGuid = NEWID()  
SET @runGuid = NEWID()  
INSERT wmTransactionQueue   
(
  TxGuid, TransactionType, Label, SessionSerialNumber, Facility, LocalGenerationDate, ProcessOrchestrator,
  ProcessHandle, ProcessStart, ProcessStop, TrxQueue, OrderKey, [Status], ApplicationId, ApplicationVersion,
  Workstation, Payload, lock_count, EventId, WhenScheduled  
)  
VALUES  
(   
  @txGuid, 'ceTransactionGs1Transform', 'EIT/Shipping', 1000, @facility,
  GETDATE(), NULL, NULL, NULL, NULL, @queue, 1, 'Queued', 'SSMC', @@VERSION, 
  'ARE-PC',
  '<RequestEnvelope>
		<Guid>' + CONVERT(VARCHAR(100),@txGuid) + '</Guid>    
		<IsUow>False</IsUow>
		<RunGuid>' + CONVERT(VARCHAR(100),@runGuid) + '</RunGuid>
		<TransactionType>ceTransactionGs1Transform</TransactionType>
		<SchemaVersion>10</SchemaVersion>
		<Label>ETT/Shipping</Label>
		<SessionSerialNumber>1000</SessionSerialNumber>
		<Facility>' + @facility + '</Facility>
		<LocalGenerationDate>' +
			 CONVERT(VARCHAR(50),GETDATE()) + '
		</LocalGenerationDate>
		<Payload>
		<ceTransactionGs1Transform>
			<Name>Shipping</Name>
			<ExtractSql>
				SELECT * FROM wmVwChipotleShippingEvent V
				INNER JOIN wms_outint_tbl OI ON
				   OI.gl_cmp_key = V.gl_cmp_key AND
				   OI.in_whs_key = V.in_whs_key AND
				   OI.in_lot_key = V.CaseLot AND
				   OI.wms_conthdr_key = V.SSCC AND
				   OI.wms_contdtl_key = V.wms_contdtl_key AND
				   OI.so_hdr_key = V.SO AND
				   OI.wms_outdtl_key = V.wms_outdtl_key
				WHERE COALESCE(OI.extractStatus,0) = 0
			</ExtractSql>
			<GroupId>
				@GroupId -- recall this from the ActionArgs
			</GroupId>
			<SuccessSql>
				UPDATE wms_outint_tbl
				SET extractStatus = 1
				FROM wms_outint_tbl OI
				INNER JOIN wmVwChipotleShippingEvent S ON
				   OI.gl_cmp_key = S.gl_cmp_key AND
				   OI.in_whs_key = S.in_whs_key AND 
				   OI.so_hdr_key = S.SO AND
				   OI.wms_outdtl_key = S.wms_outdtl_key AND
				   OI.wms_conthdr_key = S.wms_conthdr_key AND
				   OI.wms_contdtl_key = S.wms_contdtl_key
				WHERE
				   COALESCE(OI.extractStatus,0) = 0
			</SuccessSql>     
			<Transformation>
				<Column>
					<Name>ShipFromLoc</Name>
					<ApplicationIdentifier>414</ApplicationIdentifier>
				</Column>    
				<Column>     
					<Name>ShipFromLocExt</Name>     
					<ApplicationIdentifier>254</ApplicationIdentifier>    
				</Column>    
				<Column>     
					<Name>ShipToLoc</Name>     
					<ApplicationIdentifier>414</ApplicationIdentifier>    
				</Column>    
				<Column>     
					<Name>ShipToLocExt</Name>     
					<ApplicationIdentifier>254</ApplicationIdentifier>    
				</Column>    
				<Column>     
					<Name>ShipDate</Name>           
					<Length>10</Length>     
					<FormatString>MM/dd/yyyy</FormatString>    
				</Column>    
				<Column>     
					<Name>CaseGTIN</Name>     
					<ApplicationIdentifier>1</ApplicationIdentifier>    
				</Column>    
				<Column>     
					<Name>CaseLot</Name>     
					<ApplicationIdentifier>10</ApplicationIdentifier>    
				</Column>    
				<Column>     
					<Name>CaseQuantity</Name>     
					<IsNumeric>True</IsNumeric>     
					<Length>8</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>False</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>CaseUOM</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>4</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>SSCC</Name>     
					<ApplicationIdentifier>0</ApplicationIdentifier>    
				</Column>    
				<Column>     
					<Name>SO</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>BOL</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>PO</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>Carrier</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>Broker</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>ShipVia</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>Driver</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>DriverLic</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>SealNumber</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>TruckLic</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>TrailerLic</Name>     
					<IsNumeric>False</IsNumeric>     
					<Length>30</Length>     
					<IsFixedLength>False</IsFixedLength>     
					<IsStoredAsText>True</IsStoredAsText>    
				</Column>    
				<Column>     
					<Name>LoadDate</Name>           
					<Length>10</Length>     
					<FormatString>MM/dd/yyyy</FormatString>    
				</Column>    
				<Column>     
					<Name>OutDate</Name>           
					<Length>10</Length>     
					<FormatString>MM/dd/yyyy</FormatString>    
				</Column>     
			</Transformation>      
		</ceTransactionGs1Transform>    
	</Payload>  
</RequestEnvelope>', 0, NULL, NULL)     

SELECT    
	'ftp.coolearth.com' AS Address,   
	'someuser' AS [User],   
	'mypassword' AS Password,   
	AfterTransformation AS Contents,   
	Name + 'Extract' + 
	CONVERT(VARCHAR(11), wmTransformations_id) + '.csv' AS 	RemoteFileName,
	'UPDATE wmTransformations SET Status = FTP 
WHERE 
	wmTransformations_id = ' + 
		CONVERT(VARCHAR(11), wmTransformations_id) AS 
			SuccessSqlToExecute  
FROM   
	wmTransformations T  
INNER JOIN  
(      
	SELECT GroupId, COUNT(*) AS Cnt 
	FROM wmTransformations       
	WHERE Status = 'New'      
	GROUP BY GroupId      
	HAVING COUNT(*) = 4  
) G ON G.GroupId = T.GroupId  
ORDER BY G.GroupId, wmTransformations_id