The Profit and Loss Report shows actual amounts for profit and loss accounts. It contains one row for each location, legal entity, GL account, transaction date, approval status, and beginning balance flag.
Column reference
| Column | Type | Description |
|---|---|---|
| tenant_id | NUMBER(38,0) | R365 internal tenant identifier |
| operational_year | NUMBER(4,0) | Fiscal year from the 4-4-5 calendar |
| operational_period | NUMBER(2,0) | Fiscal period number (1-12) from the 4-4-5 calendar. Blank for the extra week at the end of a 53-week year, which has no period number. |
| operational_quarter | NUMBER(9,0) | Fiscal quarter (1-4) |
| operational_week | NUMBER(17,0) | Fiscal week number (1-53) within the operational year from the 4-4-5 calendar |
| transaction_date | DATE | GL posting date of the transaction |
| location_id | VARCHAR | Location identifier for this posting |
| legal_entity_id | VARCHAR | Legal entity resolved from the posting location |
| approval_status | NUMBER(38,0) | Approval status of the underlying transaction: 1 is unapproved and 2 is approved. The report includes both. Filter this column to 2 to see approved transactions only. |
| is_beginning_balance | BOOLEAN | True when the transaction is a beginning balance entry. The report includes these entries. Filter this column to exclude them. |
| gl_account_id | VARCHAR | GL account identifier |
| account_number | VARCHAR | GL account number |
| account_name | VARCHAR | GL account name |
| gl_type_name | VARCHAR | GL type name (such as Food, Beverage, or Labor) |
| gl_type_code | NUMBER(38,0) | Numeric GL type code |
| typical_balance_is_credit | BOOLEAN | True if the account's typical balance is credit (revenue). False for debit-normal accounts (expense and cost of goods sold). Some tenants set Sales GL types (GL type code 400) as debit-normal. The amount column still treats these as credit minus debit. |
| is_statistical_account | BOOLEAN | True if this is a statistical (non-financial) account |
| operational_reporting_category | VARCHAR | Full operational reporting category label, such as 'Food - Cost of Sales'. |
| operational_reporting_category_number | NUMBER(38,0) | Raw special_category code from gl_account |
| parent_gl_account_id | VARCHAR | Direct parent GL account identifier |
| parent1_gl_account_id | VARCHAR | Level-1 parent GL account identifier in the account hierarchy |
| parent_account_name | VARCHAR | Level-1 parent GL account name |
| parent_account_number | VARCHAR | Level-1 parent GL account number |
| operational_reporting_category_1 | VARCHAR | First segment of operational_reporting_category (before the dash separator) |
| operational_reporting_category_2 | VARCHAR | Second segment of operational_reporting_category (after the dash separator) |
| debit_amount | NUMBER(38,2) | Sum of debit amounts for this account, location, and transaction date |
| credit_amount | NUMBER(38,2) | Sum of credit amounts for this account, location, and transaction date |
| amount | NUMBER(38,2) | Sign-adjusted net amount. For credit-normal accounts, the amount is credit minus debit, so a positive value is an increase in the account (a credit for revenue). For debit-normal accounts, the amount is debit minus credit, so a positive value is an increase in the account (a debit for expense and cost of goods sold). Sales accounts (GL type code 400) are the exception: the amount is always credit minus debit, even when the account is debit-normal, so a debit gives a negative amount. |