ItemAvailability

Type	Table
ItemAvailability	gdata_item_availability

The gdata_item_availability table resides within the operational data section of the platform's database. It logs updates to item availability, capturing details about changes to menu items or modifiers and their availability statuses at specific locations. This table serves as an audit trail, reflecting when items become unavailable or available in the food aggregators' menus.

When an item is marked as unavailable, it will either be hidden or displayed as a disabled item on the platform's menus. The log helps ensure accuracy and transparency in item availability for customers across various channels.

Field Name	Data Type	Description
id	varchar(255)	Unique identifier for each item availability log entry.
partnerid	varchar(255)	Your account id
brandid	varchar(255)	Identifier for the brand linked to the item.
locationid	varchar(255)	Identifier for the location where the item availability change occurred.
itemavailabilitytype	varchar(255)	Type of item affected: RECIPE (menu item like "chicken burger") or MODIFIER (extra items or toppings like "cheese").
recipeid	varchar(255)	Identifier for the menu item (recipe) that has undergone an availability status change.
modifierid	varchar(255)	Identifier for the modifier (such as toppings or customizations) related to the availability change. Available only when it's a modifier item availability change.
itemstatus	varchar(300)	Current status of the item. Possible values include: - AVAILABLE: The item is now available. UNAVAILABLE_UNTIL_SPECIFIC_DATE: The item is unavailable until a specified date, indicated by the unavailabletill column. - UNAVAILABLE_UNTIL_FURTHER_NOTICE:** The item is unavailable indefinitely, until manually updated.
unavailabletill	timestamp	The date and time until which the item remains unavailable (applicable for UNAVAILABLE_UNTIL_SPECIFIC_DATE status). When a user selects "Unavailable Until Tomorrow" in the KDS or GrubOPS applications, the system updates the database by setting the unavailableTill column to the next day's timestamp, with the status recorded as "UNAVAILABLE_UNTIL_SPECIFIC_DATE."
actiontime	timestamp	The timestamp when the availability status change was occurred.
actiontriggeredby	varchar(300)	Information about the user or system that triggered the availability change.
actionsource	varchar(255)	Source application that triggered the availability change (e.g., MasterKDS, GrubOPS).
tags	varchar(255)	Optional tags associated with additional metadata.

Example

To extract details such as item names, modifier names, external IDs, or location and brand information from the item availability update log, join the log with the relevant dimension tables.

SELECT  

			gda.brandid AS "Brand ID", 
			gb.name AS "Brand Name", 
			gda.locationid AS "Location ID", 
			gl.name AS "Location Name", 
			gda.recipeid AS "Recipe ID", 
			gr.name AS "Recipe Name", 
			gr.externalid AS "Recipe External ID", 
			gda.modifierid AS "Modifier ID", 
			grm.name AS "Modifier Name", 
			grm.externalid AS "Modifier External ID", 
			gda.itemavailabilitytype AS "Item Availability Type", 
			gda.itemstatus AS "Item Status", 
			gda.unavailabletill AS "Unavailable Till", 
			gda.actiontime AS "Action Time", 
			gda.actiontriggeredby AS "Action Triggered By", 
			gda.actionsource AS "Action Source" 

FROM  
gdata_item_availability gda 
LEFT JOIN  
gdata_recipe gr ON gda.recipeid = gr.id 
LEFT JOIN  
gdata_recipe_modifier grm ON gda.modifierid = grm.modifiergroupid 
LEFT JOIN  
gdata_location gl ON gda.locationid = gl.id 
LEFT JOIN  
gdata_brand gb ON gda.brandid = gb.id 
WHERE  
gda.actiontime BETWEEN '2024-11-07 04:00:00.427' AND '2024-11-07 06:35:22.977';