> For the complete documentation index, see [llms.txt](https://transfer-app.gitbook.io/transfer-app-docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://transfer-app.gitbook.io/transfer-app-docs/user-guide/markdown/journal-adjustment.md).

# Journal Adjustment

The journal adjustment transfer is a more advanced example of uploading an excel workbook and grabbing the data from a sheet by name from the workbook and then loading that data into the journal adjustment fact table.

Use Case:  When accountants or auditors come in with their magic wands (spreadsheets), they might decree, "This sale was actually a return," or "That discount wasn't supposed to be that generous." Time for some manual adjustments!

Navigate to the Admin -> Transfers page, and select "example - Journal Adjustment" from the drop down menu to view the entire configuration for the upload.

The following features are configured for this transfer:

* Excel file for source
  * Grab the sheet that matches the name "journal\_adjustments" from the workbook
  * Limit the file selection to .xlsx files
* Allow downloads of the uploaded files
* Upload history shows files uploaded from all users

Where the features are more advanced:

* Use a custom sql insert statement to load the table
* Use Snowflake functions to populate target fields
* Use environment variables to add the username and upload\_log\_id to the target table
* Change the order of the columns from the source to match the layout of the table
* Skip the first line of the staged file with sql window function
* Add the current time to the target table lastupdated field

```
source data layout

OriginalTransactionID,AdjustmentType,AdjustmentAmount,AdjustmentDate,Comments
1001,Return,-50.00,2022-01-15,"Corrected sale to return"
1002,Discount,-10.00,2022-02-20,"Adjusted discount amount"
1003,Correction,25.00,2022-03-05,"Corrected transaction amount"
1004,Return,-30.00,2022-04-10,"Reversed incorrect sale"
```

```sql
table columns:

AdjustmentGUID VARCHAR(60) PRIMARY KEY,
OriginalTransactionID INT,
AdjustmentDate DATE,
AdjustmentAmount DECIMAL(10, 2),
AdjustmentType VARCHAR(50),
Comments VARCHAR(255),
upload_log_id VARCHAR(255),
username VARCHAR(255),
LastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
```

The following custom sql is run and makes use of the replacement variables available in the transfer app.

```
insert into {tableName} 
     (AdjustmentGUID,OriginalTransactionID,AdjustmentDate,AdjustmentAmount, AdjustmentType,
        Comments,upload_log_id,username,LastUpdated)

select uuid_string() as AdjustmentGUID
     , $1 as OriginalTransactionID
     , $4 as AdjustmentDate
     , $3 as AdjustmentAmount
     , $2 as AdjustmentType
     , $5 as Comments
     , '{logId}' as upload_log_id
     , '{username}' as username
     , current_timestamp() as LastUpdated
  FROM (
               SELECT $1,$2,$3,$4,$5, ROW_NUMBER() OVER (ORDER BY NULL) AS row_num
                 FROM @{stageFilePath}
        )
 QUALIFY row_num > 1
 ;
```

**Replacement Variables for Custom SQL:**

* ${stageFilePath}  - the location of the file uploaded
* ${tableName}  - db.schema.table defined for the transfer
* ${logId}  - the log id for the upload
* ${username}  - the snowflake username

The attached file can be used to test the upload.

{% file src="/files/1fSatStHb17KoGzCVJOu" %}