Common check on billrun

Check if there are no ongoing locks

SELECT TOP 1000 [transmissionId]
      ,[server]
      ,[CreateDateTime]
  FROM [v4].[dbo].[ZuoraUsagesTransmission]

SELECT TOP 1000 [hotelId]
      ,[server]
      ,[CreateDateTime]
  FROM [v4].[dbo].[ZuoraUsagesCalculationCronProcesses]

if any locks, better delete the locks first before continuing

Manually launching script to be sure everything is calculated for current month

From any server, launch the script ForceUsageCalculation on the first day of the month without editing.

If you launch the script on the 2nd of the month, edit the line

$today = new \DateTime('today', new \DateTimeZone($Hotel->TimeZone));

to launch the script:

php /var/www/bin/tools/Zuora/ForceCalculationUsages.php

if for any reason you want to launch only specific calculations on that script, you can overwrite the followin lines:

Force whitelabelId if needed

$whitelabels = array(array('whitelabelId' => 1));

To force hotels:

$hotels = \Db::getInstance()->Select('Select hotelId FROM [v4].[dbo].[hotelscache] WHERE databasesgroupId IN(15,36) and Removal = 0');

Once ForceUsageCalculation script ended, check the status of all usages upload

If a usage stay in Waiting for too long, it means there is a lock to remove in ZuoraUsagesTransmission

SELECT TOP 1000 [hotelId]
      ,[UOM]
      ,[StartDateTime]
      ,[EndDateTime]
      ,[Quantity]
      ,[SubscriptionNumber]
      ,[ChargeNumber]
      ,[BillingPeriod]
      ,[AccountId]
      ,[Status]
      ,[CreateDateTime]
      ,[PurgeDate]
      ,[PreviousRetryAttemptDateTime]
      ,[Description]
  FROM [v4].[dbo].[ZuoraUsages] where status != 'Done'

should any usages be stuck InProgress or Error, you need to manually check if the usages is in the Zuora account. make sure to set the status to Done if it is already in Zuora

then on next cron of upload it should be uploaded correctly

Checking usages in general

Note this is not needed, just for information

You can do a small comparaison with previous month

(First date : previous start 2 month ago, second date

DECLARE @StardDate DATETIMEOFFSET(7) = '2022-10-01 00:00:00 +02:00'
SELECT * FROM hotelscache HC
INNER JOIN v4.dbo.hotelslimitationsSummary hl
on hl.hotelId = hc.hotelId
WHERE hl.hasSubscriptionActive = 1 AND HC.hotelId IN(
	SELECT distinct [hotelId]
  
      
  FROM [v4].[dbo].[ZuoraUsages] where 
 [StartDateTime] = @StardDate AND
   hotelId NOT IN( SELECT [hotelId] FROM [v4].[dbo].[ZuoraUsages] where [UOM] IN ('{"value":"Payment Transaction"}', '{"value":"Meta With SB4"}', '{"value":"Meta Connection"}', '{"value":"PMS Connection"}', '{"value":"Booking Channels"}', '{"value":"Duplicate Channels"}') and startdatetime > @StardDate)
   and UOM NOT IN ('{"value":"Callback"}')
  group by hotelId
  )
  and hc.whitelabelId NOT IN(20)

Checking reservations amount usages

this is important

When all are done, you can quickly check the reservations CRS usages with this query:

Note that currently for Avenue6 we also calculate those, you need to manually go in the Zuora account and delete the usages because there is no conversion currently and the amount would be wrong.

For that go in the account itself and under usages, click on “pending process”

then you can delete the usage bottom right

You can list reservation usages : (make sure there are the same number of usages as previous month)

SELECT TOP 1000 [hotelId]
      ,[UOM]
      ,[StartDateTime]
      ,[EndDateTime]
      ,[Quantity]
      ,[SubscriptionNumber]
      ,[ChargeNumber]
      ,[BillingPeriod]
      ,[AccountId]
      ,[Status]
      ,[CreateDateTime]
      ,[PurgeDate]
      ,[PreviousRetryAttemptDateTime]
      ,[Description]
  FROM [v4].[dbo].[ZuoraUsages] where UOM LIKE '%Reservation Amount%'
  order by createdatetime desc

Checking usages for meta + payment transactions:

Startdate = calculation month

/****** Script for SelectTopNRows command from SSMS  ******/
DECLARE @StardDate DATETIMEOFFSET(7) = '2022-05-01 00:00:00 +02:00'
  
SELECT TOP 1000 [hotelId]
      ,[UOM]
      ,[StartDateTime]
      ,[EndDateTime]
      ,[Quantity]
      ,[SubscriptionNumber]
      ,[ChargeNumber]
      ,[BillingPeriod]
      ,[AccountId]
      ,[Status]
      ,[CreateDateTime]
      ,[PurgeDate]
      ,[PreviousRetryAttemptDateTime]
      ,[Description]
  FROM [v4].[dbo].[ZuoraUsages] 
  where uom like '%res%' and description like '%google%' and StartDateTime >= @StardDate
  order by hotelId asc

  SELECT count(1), [hotelId]
  FROM [v4].[dbo].[ZuoraUsages] 
  where uom like '%res%' and description like '%google%' and StartDateTime >= @StardDate
  group by hotelId

  SELECT TOP 1000 [hotelId]
      ,[UOM]
      ,[StartDateTime]
      ,[EndDateTime]
      ,[Quantity]
      ,[SubscriptionNumber]
      ,[ChargeNumber]
      ,[BillingPeriod]
      ,[AccountId]
      ,[Status]
      ,[CreateDateTime]
      ,[PurgeDate]
      ,[PreviousRetryAttemptDateTime]
      ,[Description]
  FROM [v4].[dbo].[ZuoraUsages] 
  where uom like '%res%' and description like '%trivago%' and StartDateTime >= @StardDate
 order by hotelId asc

  SELECT count(1), [hotelId]
  FROM [v4].[dbo].[ZuoraUsages] 
  where uom like '%res%' and description like '%trivago%' and StartDateTime >= @StardDate
  group by hotelId

  /****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 Z.[hotelId], HC.HotelName, HC.ZuoraTenantId
      ,[UOM]
      ,[StartDateTime]
      ,[EndDateTime]
      ,[Quantity]
      ,[SubscriptionNumber]
      ,[ChargeNumber]
      ,[BillingPeriod]
      ,[AccountId]
      ,[Status]
      ,Z.[CreateDateTime]
      ,[PurgeDate]
      ,[PreviousRetryAttemptDateTime]
      ,[Description]
  FROM [v4].[dbo].[ZuoraUsages]  Z
  INNER JOIN v4.dbo.hotelsCache HC
  on z.hotelId = HC.hotelId 
  where uom like '%payment%' and 
  [StartDateTime]  >= @StardDate
   order by createdatetime desc

Checking invoices callouts

SELECT TOP 1000 [obj_id_class]
      ,[obj_id]
      ,[Type]
      ,[Input]
      ,[Status]
      ,[CreateDateTime]
      ,[PurgeDate]
      ,[PreviousRetryAttemptDateTime]
   FROM [v4].[dbo].[ZuoraCallouts] where status !='Done' order by createdatetime desc

If you see some line that are InProgress or Error for some sime, it means the process failed.

To unblock:

First remove the local for the “obj_ids” returned in first query

delete FROM [v4].[dbo].[ZuoraCalloutsCronProcesses] WHERE obj_id IN ('', '', '')

Then update the following fields for the obj_ids:

UPDATE [v4].[dbo].[ZuoraCallouts] 
SET [Status] = 'Waiting', [PreviousRetryAttemptDateTime] = NULL 
WHERE obj_id IN('')

Hotel-Spider