I will start by pointing you to a good article Andreas Hofmann from Microsoft has written. It steps you through what you need to extend a transactional table in the Retail Channel and bring that back to HQ via the P Job.
Now to summerise the issue I faced recently (being a retail rookie).
Following the blog post I created a custom Int64 field on the RetailTransactionSalesTrans. However, when I ran the P job it failed with this error.
“Failed to convert parameter value from a String to a Int64”
I did some investigation by trying to find out what it is actually doing. Essentially the job will do an outer join to your custom extension table. Even though your custom field is 0 by default. You won’t be creating an extension record for every single transaction record. The p job will do an outer join between RetailTransactionSalesTrans to your custom CustomRetailTransSalesTrans, you will notice some blanks in your file that is coming to HQ.
See figure below what the file looks like.
Remember also, that the sync happens by downloading and uploading flat files. That is why you have it trying to convert from string to int64. Hence, the error.
You can see the files by going to the Upload sessions and downloading the zip file.
As a colleague told me today. The advice is, use a string and treat it as a string from Channel to HQ.