A data export configuration was created in Simphony using the standard template named “FiscalStandardExp”, which includes the following subject areas:

• FCR Invoice Control (v1)
• FCR Invoice Data (v1)
• FCR Total Data (v1)
• Location By Reference (v1)
• Guest Check GST (v1)

This configuration is scheduled to run daily across all hotels.

However, we have identified missing data in the export files:

• 2025/01/15 – the cgsts table contains no data
• 2025/04/05 – the cgsts table contains data as expected

Below is the SQL query currently used in the Query Engine:

SELECT

  'CGST' AS Record_Type,

  COALESCE(GCG.checkNum, 0) AS Guest_Check_Number,

  COALESCE(GCG.revenueCenterName, ' ') AS Revenue_Center_Name,

  COALESCE(GCG.revenueCenterNum, 0) AS Revenue_Center_Number,

  COALESCE(GCG.revenueCenterMasterName, ' ') AS Revenue_Center_Master_Name,

  COALESCE(GCG.revenueCenterMasterNum, 0) AS Revenue_Center_Master_Number,

  COALESCE(GCG.orderTypeName, ' ') AS Order_Type_Name,

  COALESCE(GCG.orderTypeNum, 0) AS Order_Type_Number,

  COALESCE(GCG.orderTypeMasterName, ' ') AS Order_Type_Master_Name,

  COALESCE(GCG.orderTypeMasterNum, 0) AS Order_Type_Master_Number,

  COALESCE(GCG.taxName, ' ') AS Tax_Name,

  COALESCE(GCG.taxNum, 0) AS Tax_Number,

  COALESCE(GCG.taxMasterName, ' ') AS Tax_Master_Name,

  COALESCE(GCG.taxMasterNum, 0) AS Tax_Master_Number,

  COALESCE(GCG.checkEmployeeFirstName, ' ') AS Check_Employee_First_Name,

  COALESCE(GCG.checkEmployeeLastName, ' ') AS Check_Employee_Last_Name,

  COALESCE(GCG.checkEmployeeNum, 0) AS Check_Employee_Number,

  COALESCE(GCG.taxRate, 0) AS GST_Tax_Rate,

  COALESCE(GCG.taxAmount, 0) AS Tax_Amount,

  COALESCE(GCG.taxExemptAmount, 0) AS Tax_Exempt_Amount,

  COALESCE(GCG.openFixedPeriod, 0) AS Open_Fixed_Period,

  COALESCE(GCG.grossSalesAfterDiscount, 0) AS Gross_Sales_After_Discount,

  COALESCE(GCG.salesNetGst, 0) AS Sales_Net_GST,

  CASE

    WHEN GCG.openBusinessDate IS NULL THEN ' '

    ELSE TO_CHAR(TO_TIMESTAMP(GCG.openBusinessDate, 'yyyy-MM-dd'), 'yyyyMMddHHmmss')

  END AS Open_Business_Date,

  COALESCE(GCG.orderChannelNum, 0) AS Order_Channel_Number,

  COALESCE(GCG.orderChannelName, ' ') AS Order_Channel_Name,

  COALESCE(GCG.orderChannelMasterNum, 0) AS Order_Channel_Master_Number,

  COALESCE(GCG.orderChannelMasterName, ' ') AS Order_Channel_Master_Name

FROM guestCheckGST34() GCG;

We suspect that the missing data in the XML export may be due to data retention policies.

Our question is: What changes should we make to the SELECT statement to ensure that, in cases where no data is available in the current table, the query retrieves data from the historical table instead?

Are these assumptions and considerations valid?