# gettbstransactionhierarchy # uses the beloow SQL to get a list of PERIOD's to run # gettbscust # # SELECT DISTINCT Period # FROM tInvoice, tInvoiceDetail # where tInvoiceDetail.InvoiceID = tInvoice.ID # and tInvoice.InvoiceDate > # dateadd($IOFFSETDAYS,'$EYYYYMMDD') # and tInvoice.InvoiceDate <= # dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD')) # $EXCLUDEINVBIT # $EXCLUDEBATCHIDBIT # ORDER BY Period # gettrnscusthier does this SQL for each PERIOD # non - VRT SELECT distinct ${transactiontable}.ServiceID AS ServiceIDID, tServiceID.ServiceID, tCentre.ID AS CentreID, tCentre.Description AS CentreDesc, tDepartment.ID As DeptID, tDepartment.Description AS DeptDesc, tGroup.ID AS GrpID, tGroup.Description AS GrpDesc, tCustomer.ID AS CustID, tCustomer.ShipTo, tCustomer.FullName, tCustomer.VNumber , 1, 1, 1, 1, 1 FROM ${transactiontable} LEFT JOIN tStatus ON ${transactiontable}.StatusID = tStatus.ID LEFT JOIN tServiceID ON ${transactiontable}.ServiceID = tServiceID.ID LEFT JOIN ( tCentre LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID ) ON ${transactiontable}.CustomerCentreID = tCentre.ID LEFT JOIN tCustomer ON ${transactiontable}.CustomerID = tCustomer.ID LEFT JOIN tGroup ON ${transactiontable}.GroupID = tGroup.ID INNER JOIN tBatch ON tBatch.ID = ${transactiontable}.BatchID INNER JOIN ( tInvoiceDetail INNER JOIN tInvoice ON tInvoiceDetail.InvoiceID = tInvoice.ID ) ) ON ${transactiontable}.InvoiceDetailID = tInvoiceDetail.ID WHERE tBatch.BillingPeriod = '$tbsbillingperiod' $BATCHIDBIT and ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' ) and tCustomer.ShipTo <> "VRT" AND ${transactiontable}.RevenueFlag = 1 AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD') AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')) $EXCLUDEINVBIT # for VRT SELECT distinct ${transactiontable}.ServiceID AS ServiceIDID, tServiceID.ServiceID, tCentre.ID AS CentreID, tCentre.Description AS CentreDesc, tDepartment.ID As DeptID, tDepartment.Description AS DeptDesc, tGroup.ID AS GrpID, tGroup.Description AS GrpDesc, tCustomer.ID AS CustID, tCustomer.ShipTo, tCustomer.FullName, tCustomer.VNumber , 1, 1, 1, 1, 1 FROM ${transactiontable} LEFT JOIN tStatus ON ${transactiontable}.StatusID = tStatus.ID LEFT JOIN tServiceID ON ${transactiontable}.ServiceID = tServiceID.ID LEFT JOIN ( tCentre LEFT JOIN tDepartment ON tCentre.DepartmentID = tDepartment.ID ) ON ${transactiontable}.CustomerCentreID = tCentre.ID LEFT JOIN tCustomer ON ${transactiontable}.CustomerID = tCustomer.ID LEFT JOIN tGroup ON ${transactiontable}.GroupID = tGroup.ID LEFT JOIN tBatch ON ${transactiontable}.BatchID = tBatch.ID LEFT JOIN ( tInvoiceDetail INNER JOIN tInvoice ON tInvoiceDetail.InvoiceID = tInvoice.ID ) ON ${transactiontable}.InvoiceDetailID = tInvoiceDetail.ID WHERE tBatch.BillingPeriod = '$curtbsbillingperiod' $BATCHIDBIT and ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' ) and tCustomer.ShipTo = "VRT" AND ${transactiontable}.RevenueFlag = 1 $EXCLUDEINVBIT $EXCLUDEBATCHIDBIT