# getcde6 
# is called for each relevant PERIOD
# its SQL is...

# for non - VRT
SELECT
    tCustomer.ShipTo,
    TN1.BatchID AS Sundry,
    TN1.RentAmt,
    TN1.CallAmt,
    TN1.OtherAmt,
    TN1.AdminAmt,
    (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt + TN1.AdminAmt) AS TotalAll,
    tBatchType.ID AS BatchTypeID,
    tBatchType.Description AS BatchTypeDesc,
    tBatchType.ReportGroupID,
    tReportGroup.GroupDesc AS ReportGroupDesc,
    tReportGroup.ReportOrder,
    tServiceType.Code,
    tServiceID.ServiceID AS ServiceName,
    tLocation.Description AS Location,
    tPerson.Person, 
    tCentre.ID, tCentre.Description AS Centre,
    tActivity.Description AS Activity,
    tElement.ElementCode AS Element,
    tSubledger.Description AS Subledger,
    tServiceID.PayType,
    tDepartment.ID, tDepartment.Description AS Department, 
    tServiceID.Description AS ServiceDesc,
    tServiceID.InstallationDate,
    tStatus.Status,
    tBatch.BillingPeriod,
    TN1.InvoiceDetailID,
    TN1.GroupID
FROM
 (SELECT
	${transactiontable}.batchID,
        ${transactiontable}.ServiceID,
	${transactiontable}.ServiceTypeID,
	${transactiontable}.CustomerID,
	${transactiontable}.StatusID,
	${transactiontable}.PersonID,
	${transactiontable}.LocationID,
	${transactiontable}.CustomerCentreID,
	${transactiontable}.CustomerActivityID,
	${transactiontable}.CustomerElementID,
	${transactiontable}.CustomerSubledgerID,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'RENT' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS RentAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'CALL' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS CallAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'OTHER' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS OtherAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'ADMIN' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS AdminAmt,
	${transactiontable}.InvoiceDetailID,
	${transactiontable}.GroupID
	FROM ${transactiontable}
	 INNER JOIN tBatch
		ON tBatch.ID = ${transactiontable}.BatchID
	 INNER JOIN ( tInvoiceDetail
			INNER JOIN tInvoice
			ON tInvoiceDetail.InvoiceID = tInvoice.ID
	 ) ON ${transactiontable}.InvoiceDetailID = tInvoiceDetail.ID
	 LEFT JOIN tTransactionType
		ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
	 LEFT JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
	WHERE tBatch.BillingPeriod = '$tbsbillingperiod' $BATCHIDBIT
	  AND ${transactiontable}.RevenueFlag = 1
	  AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')
	  AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD'))
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
	GROUP BY
		${transactiontable}.ServiceID,
		${transactiontable}.BatchID,
		${transactiontable}.ServiceTypeID,
		${transactiontable}.CustomerID,
		${transactiontable}.StatusID,
		${transactiontable}.PersonID,
		${transactiontable}.LocationID,
		${transactiontable}.CustomerCentreID,
		${transactiontable}.CustomerActivityID,
		${transactiontable}.CustomerElementID,
		${transactiontable}.CustomerSubledgerID,
		${transactiontable}.InvoiceDetailID,
		${transactiontable}.GroupID
 ) AS TN1
 LEFT JOIN tStatus
	ON TN1.StatusID = tStatus.ID
 LEFT JOIN tCustomer
	ON TN1.CustomerID = tCustomer.ID
 LEFT JOIN tPerson
	ON TN1.PersonID = tPerson.ID
 LEFT JOIN tLocation
	ON TN1.LocationID = tLocation.ID
 LEFT JOIN ( tCentre
	LEFT JOIN tDepartment
		ON tCentre.DepartmentID = tDepartment.ID
	) ON TN1.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
	ON TN1.CustomerActivityID = tActivity.ID
 LEFT JOIN tElement
	ON TN1.CustomerElementID = tElement.ID
 LEFT JOIN tSubledger
	ON TN1.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tServiceID
	ON TN1.ServiceID = tServiceID.ID
 LEFT JOIN tServiceType
	ON TN1.ServiceTypeID = tServiceType.ID
 LEFT JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON TN1.BatchID = tBatch.ID
WHERE
 ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo <> "VRT"
--#    and tBatchType.Active = 1
ORDER BY tCustomer.ShipTo, tServiceID.ServiceID, tServiceType.Code, Sundry

\go -m bcp
SQLCMD0


# for VRT
SELECT
    tCustomer.ShipTo,
    TN1.BatchID AS Sundry,
    TN1.RentAmt,
    TN1.CallAmt,
    TN1.OtherAmt,
    TN1.AdminAmt,
    (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt + TN1.AdminAmt) AS TotalAll,
    tBatchType.ID AS BatchTypeID,
    tBatchType.Description AS BatchTypeDesc,
    tBatchType.ReportGroupID,
    tReportGroup.GroupDesc AS ReportGroupDesc,
    tReportGroup.ReportOrder,
    tServiceType.Code,
    tServiceID.ServiceID AS ServiceName,
    tLocation.Description AS Location,
    tPerson.Person, 
    tCentre.ID, tCentre.Description AS Centre,
    tActivity.Description AS Activity,
    tElement.ElementCode AS Element,
    tSubledger.Description AS Subledger,
    tServiceID.PayType,
    tDepartment.ID, tDepartment.Description AS Department, 
    tServiceID.Description AS ServiceDesc,
    tServiceID.InstallationDate,
    tStatus.Status,
    tBatch.BillingPeriod,
    TN1.InvoiceDetailID,
    TN1.GroupID
FROM
 (SELECT
	${transactiontable}.batchID,
	${transactiontable}.ServiceID,
	${transactiontable}.ServiceTypeID,
	${transactiontable}.CustomerID,
	${transactiontable}.StatusID,
	${transactiontable}.PersonID,
	${transactiontable}.LocationID,
	${transactiontable}.CustomerCentreID,
	${transactiontable}.CustomerActivityID,
	${transactiontable}.CustomerElementID,
	${transactiontable}.CustomerSubledgerID,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'RENT' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS RentAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'CALL' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS CallAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'OTHER' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS OtherAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'ADMIN' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS AdminAmt,
	${transactiontable}.InvoiceDetailID,
	${transactiontable}.GroupID
	FROM ${transactiontable}
	 INNER JOIN tBatch
		ON tBatch.ID = ${transactiontable}.BatchID
	 LEFT JOIN ( tInvoiceDetail
		INNER JOIN tInvoice
			ON tInvoiceDetail.InvoiceID = tInvoice.ID
	 ) ON ${transactiontable}.InvoiceDetailID = tInvoiceDetail.ID
	 LEFT JOIN tTransactionType
		ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
	 LEFT JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
	WHERE tBatch.BillingPeriod = '$curtbsbillingperiod' $BATCHIDBIT
	  AND ${transactiontable}.RevenueFlag = 1
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
	GROUP BY
		${transactiontable}.ServiceID,
		${transactiontable}.BatchID,
		${transactiontable}.ServiceTypeID,
		${transactiontable}.CustomerID,
		${transactiontable}.StatusID,
		${transactiontable}.PersonID,
		${transactiontable}.LocationID,
		${transactiontable}.CustomerCentreID,
		${transactiontable}.CustomerActivityID,
		${transactiontable}.CustomerElementID,
		${transactiontable}.CustomerSubledgerID,
		${transactiontable}.InvoiceDetailID,
		${transactiontable}.GroupID
 ) AS TN1
 LEFT JOIN tStatus
	ON TN1.StatusID = tStatus.ID
 LEFT JOIN tCustomer
	ON TN1.CustomerID = tCustomer.ID
 LEFT JOIN tPerson
	ON TN1.PersonID = tPerson.ID
 LEFT JOIN tLocation
	ON TN1.LocationID = tLocation.ID
 LEFT JOIN ( tCentre LEFT JOIN tDepartment
		ON tCentre.DepartmentID = tDepartment.ID
	  ) ON TN1.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
	ON TN1.CustomerActivityID = tActivity.ID
 LEFT JOIN tElement
	ON TN1.CustomerElementID = tElement.ID
 LEFT JOIN tSubledger
	ON TN1.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tServiceID
	ON TN1.ServiceID = tServiceID.ID
 LEFT JOIN tServiceType
	ON TN1.ServiceTypeID = tServiceType.ID
 LEFT JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			 ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON TN1.BatchID = tBatch.ID
WHERE
 ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo = "VRT"
--#    and tBatchType.Active = 1
ORDER BY tCustomer.ShipTo, tServiceID.ServiceID, tServiceType.Code, Sundry