(TWN) Working with Point-of-Sale Data

This chapter provides an overview of point-of-sale (POS) data and discusses how to:

Enter and import POS data.
Move POS data from work tables to transaction tables.

Understanding POS Data

POS data is data that originates where a transaction takes place, such as a cash register terminal. You can import POS data from an external system, such as a cash register, into the JD Edwards EnterpriseOne system.

POS transactions often involve unidentified customers, such as an individual purchasing something in a retail location. Because you might have a high volume of transactions for unidentified customers, you can use a generic customer to record the sales in the JD Edwards EnterpriseOne system, and relate all of the GUI records to one general ledger transaction.

To work with POS data:

Use the GUI/VAT POS Review program (P75T101) to enter or import POS data to temporary tables.
Associate a general ledger transaction to the transactions.

You can associate a range of GUI numbers to one general ledger transaction.
Run the GUI/VAT POS Interface Inbound program (R75T101) to remove records from the temporary tables and create GUI records in the GUI/VAT Transaction Header table (F75T004) and GUI/VAT Transaction Detail table (F75T005).

Your GUI/VAT records for POS transactions can now be included in your period-ending reports.

Entering and Importing POS Data

This section provides an overview of the import process and discusses how to:

Import POS header and detail data.
Manually enter POS header data.
Manually enter POS detail data.

Understanding the Import Process

You can import POS data from an external system using a variety of methods, such as importing from a spreadsheet, using a custom table conversion program, using Microsoft Access, and so on. This guide describes how to import the data from a Microsoft Excel spreadsheet.

The JD Edwards EnterpriseOne system enables you to import header data for POS transactions, such as the declaration site, transaction type, format code, and so on. You must provide the transaction header information. You can enter the transaction header information by importing the data or by manually entering the data for each transaction. You can also import POS transaction detail data. The detail data includes information such as the item number, quantity sold, unit of measure, and so on. Providing the detail data is an optional task. However, Oracle recommends that you import the detail data to maintain data integrity between the F75T004 and F75T005 tables.

The system does not validate imported POS header or detail data. Therefore, after you import POS data from an external system, you must check the imported data to verify that it is correct and make changes, if necessary.

To enter or import POS data:

Use the GUI/VAT POS Review program to enter or import POS data and specify the general ledger account for the transactions.
1. Import POS data from a spreadsheet to the GUI/VAT Transaction Header POS (F75T004P) and GUI/VAT Transaction Detail POS (F75T005P) tables.
  
  Alternatively, you can enter the data manually.
  
  You can import transaction header data and transaction detail data.
2. Review the imported data and make changes.
  
  The system does not validate the data that you import, so you must review it and makes changes or additions as necessary.
3. Select the general ledger entry to associate with the transactions.
Run the GUI/VAT POS Interface Inbound program (R75T101) to remove records from the F75T004P and F75T005P tables and write the records to the F75T004 and F75T005 tables.

Considerations for Importing POS Header Data

To import POS header data from a spreadsheet, you must create a spreadsheet that contains columns for each of the data items. The columns must appear in the same order as the fields on the GUI/VAT Header POS Interface Revisions form, and you must provide a column in the spreadsheet for every field, even if you do not include data for the field. You must provide the header data for each POS transaction, either by importing the data or by manually entering the information.

Fields are:

Dclr Co (declaration company)
Dclr Site (declaration site)
Dclr YM (declaration year and month)
Trans. No. From (transaction number from)
Seq. No. (sequence number)
Auth Code (authorization code)
Trs Typ (transaction type)
Doc Typ (document type)
Trans Date (transaction date)
G/L Date (general ledger date)
Hppn YM (year and month in which the transaction occurred)
Fm Cd (format code)
TT (tax type)
Tx Ex (tax explanation code)
Tax Area
Tax Rate
Base Curr (base currency)
Taxable Amount
Tax
Gross Amount
Cur Code (currency code)
Exchange Rate
Foreign Taxable Amt (foreign taxable amount)
Foreign Tax
Foreign Gross Amt (foreign gross amount)
UI PF (GUI prefix)
Register No. From (register number from)
Segment No. From (segment number from)
GUI/VAT No. From (GUI/VAT number from)
GUI/VAT No. To (leave this field blank)
Trans. No. To (leave this field blank)
SN (summary note)
Doc. Count (document count)
Sal Itm (sales item type)
Zr Tx (zero tax rate item type)
DU (deduction usage code)
Proof Doc Name (proof document name)
Proof Doc Number (proof document number)
EM (exportation method)
ED Ty (export declaration type)
Exportation Doc. No. (exportation document number)
Export Date
Rev Typ (revenue type)
Spc Tax (special tax type)
Sold To Number
Invoice To Number
Ship To Number
Alpha Name
Prtnr UN (uniform number of business partner)
MT (merged transactions)
Doc Co (document company)
Doc Ty (document type)
Doc Number (document number)
Batch Number
Order Co (order company)
Order Ty (order type)
Order Number
Order Suf (order suffix)
Item Description
Quantity Ordered
CC 1 (category code 1)
CC 2 (category code 2)
CC 3 (category code 3)
CC 4 (category code 4)
CC 5 (category code 5)
CC 6 (category code 6)
Prt Cnt (printer count)
Creator ID (leave this field blank)
Sales Person
Cash Register
Citizenship ID
Credit Card Number
Payment Type
PS (prepayment status)
Prepay Balance
Dat Gen (data generation) (leave this field blank)
Remarks
User Code
User Date
User Amount
User Reference
User ID
Program ID
Work Stn ID (work station ID)
Date Updated
Time of Day

Considerations for Importing POS Detail Data

To import POS detail data from a spreadsheet, you must create a spreadsheet that contains columns for each of the data items. The columns must appear in the same order as the fields on the GUI/VAT Detail POS Interface Revisions form.

Fields are:

Ln ID (line ID)
Short Item No (short item number)
2nd Item Number
3rd Item Number
Description
UM (unit of measure)
Quantity Ordered
Taxable
Tx Ex (tax explanation code)
Tax Area
Base Curr (base currency)
Unit Price
Taxable Amount
Tax
Gross Amount
Cur Cod (currency code)
Exchange Rate
Foreign Unit Price
Foreign Taxable Amount
Foreign Tax
Foreign Gross Amt (foreign gross amount)
Doc Co (document company)
Doc Ty (document type)
Doc Number (document number)
Pay Itm (pay item)
Pay Ext (pay extension)
Order Co (order company)
Order Ty (order type)
Order Number
Order Suf (order suffix)
Line Number
TT (tax type)
User Code
User Date
User Amount
User Number
User Reference
User ID
Program ID
Work Stn ID (work station ID)
Date Updated
Time of Day

Prerequisites

Before you perform the tasks in this section:

Set up a customer to use for POS transactions.
Create the journal entry or invoice to which you will associate the POS transactions, and post the journal entry or invoice.
Create and populate the spreadsheets from which you will import data.

Forms Used to Enter or Import POS Data

Form Name	FormID	Navigation	Usage
Work With GUI/VAT Header POS Interface	W75T101A	GUI/VAT Advance & Technical Operations (G75T131), GUI/VAT POS Interface Review.	Review and select existing records.
GUI/VAT Header POS Interface Revisions	W75T101B	Click Add on the Work With GUI/VAT Header POS Interface form. Manually enter information for a new records, access the Import Assistant form, or access the Get GL Num form.	Add records, or modify existing records.
Import Assistant	none	On the GUI/VAT Header POS Interface Revisions form, select Import Grid Data from the Tools menu. On the GUI/VAT Detail POS Interface Revisions form, select Import Grid Data from the Tools menu.	Import transaction data from a spreadsheet.
Get GL Num	W75T101UH	On the GUI/VAT Header POS Interface Revisions form, select Get GL from the Form menu.	Select the journal entry transaction to which to associate the POS data.
Work With GUI/VAT Detail POS Interface	W75T101C	Select a record on the Work With GUI/VAT Header POS Interface form and select POS Detail from the Row menu.	Review and select existing records for transactions.
GUI/VAT Detail POS Interface Revision	W75T101D	Click Add on the Work With GUI/VAT Detail POS Interface form.	Add transaction detail to existing records such as item number, unit of measure, quantity, and so on.

Importing Transaction Header or Detail Data from a Spreadsheet

To import data from a spreadsheet:

Access the Import Assistant form.
Select the Import from Excel option.
Use the Browse button to navigate to the spreadsheet, and select the spreadsheet.
Complete the fields in the Define Cell Range area to specify the range of cells to import.
Specify the import location in the grid.
Click Apply.

The system populates the fields for the POS transactions.
Review and change the information, if necessary, and then click Continue in the menu bar.

The system returns you to the GUI/VAT Header POS Interface Revisions form or the GUI/VAT Detail POS Interface Revisions form and populates the fields for the transactions.

Manually Add POS Header Data

Access the GUI/VAT Header POS Interface Revisions form.

Dclr Co (declaration company)	Enter the company number of the company for which the transaction occurred.
Dclr Site (declaration site)	Enter a value that exists in the Declaration Site (75T/DS) UDC table to specify the declaration site for which you file the declaration.
Dclr YM (declaration year and month)	Enter the Taiwanese year and month for which you report the transaction.
Trans. No. From (transaction number from)	Enter the beginning number in the range of GUI/VAT transaction numbers.
Seq. No. (sequence number)	The system assigns a sequence number for returns and allowances.
Auth Code (authorization code)	Enter a value that exists in the Authorization Code (75T/AC) UDC table to limit the entry of transactions for the declaration site to users who are assigned the same code.
Trs Typ (transaction type)	Enter a value that exists in the GUI/VAT Transaction Type (75T/TT) UDC table to specify the type of transaction. Values are: FSV: Purchase Foreign Services IDG: Import Goods PUR: Purchase SAL: Sales
Doc Typ (document type)	Enter a value that exists in the GUI/VAT Document Type (75TDT) UDC table to specify the document type of the transaction. Values are: ALW: Returns & Allowances GUI: GUI IMP: Import Document INV: Commercial Invoice OTR: Other Document With VAT SCR: Sales Receipt VPD: VAT Payment Document
Trans Date (transaction date)	Enter the date that the transaction was entered into the system.
G/L Date (general ledger date)	Enter the date on which the transaction is posted.
Hppn YM (happen year and month)	Enter the Taiwanese calender year and month in which the transaction occurred.
Fm Cd (format code)	Enter a value that exists in the GUI/VAT Format Code (75T/FC) UDC table to specify the format code for the transaction. Values are: 21: Triplicate GUI / Computer GUI 22: Other Doc With Tax Amount 23: Triplicate Purchase Return / Allowance 24: Duplicate Purchase Return / Allowance 25: Triplicate Cash Register GUI 26: Summary / Computer. GUI With Tax 27: Summary Other Doc With Tax 28: VAT Payment Document 31: Triplicate / Computer GUI 32: Duplicate / Duplicate Cash Register GUI 33: Triplicate Sales Return / Allowance 34: Duplicate Sales Return / Allowance 35: Triplicate Cash Register GUI 36: GUI-Exempt 37: Special Tax: Sales 38: Special Tax: Return, Allowance 99: Sales Receipt
TT (transaction type)	Enter a value that exists in the GUI/VAT Transaction Type (75T/TT) UDC table to specify the type of transaction. Values are: FSV: Purchase Foreign Services IDG: Import Goods PUR: Purchase SAL: Sales
Tx Ex (tax explanation code)	Enter a value that exists in the Tax Explanation Codes (00/EX) UDC table to specify the tax explanation code for the transaction. The tax explanation code for VAT must be V.
Tax Area	Enter the code that identifies a tax or geographic area that has common tax rates and tax authorities. The system validates the code you enter against the Tax Areas table (F4008). The system uses the tax rate area in conjunction with the tax explanation code and tax rules to calculate tax and general ledger distribution amounts when you create an invoice or voucher.
Tax Rate 1	Enter a number that identifies the percentage of tax that should be assessed or paid to the corresponding tax authority, based on the tax area. Enter the percentage as a whole number and not as the decimal equivalent. For example, to specify 7 percent, enter 7, not .07.
Base Cur (base currency)	Enter a value that exists in the Currency Exchange Rates table (F0015) to specify the currency code of the company that originates the transaction.
Taxable Amount	Enter the amount on which the tax is calculated.
Tax	Enter the amount of the tax.
Gross Amount	Enter the total of the taxable amount and the tax.
Cur Cod (currency code)	Enter a value that exists in the Currency Exchange Rates table (F0015) to specify the currency code of the transaction.
Exchange Rate	Enter the value by which the foreign currency amount is multiplied to convert foreign currency to the equivalent domestic currency.
Foreign Taxable Amt (foreign taxable amount)	Enter the taxable amount in the foreign currency if the transaction occurred in a foreign currency.
Foreign Tax	Enter the tax amount in the foreign currency if the transaction occurred in a foreign currency.
Foreign Gross Amount	Enter the total of the taxable amount and the tax in the foreign currency if the transaction occurred in a foreign currency.
Register No. From (register number from)	Enter the beginning number of a range of receipt register numbers.
Segment No. From (segment number from)	Enter the beginning number of a range of segment numbers.
GUI/VAT No. From (GUI/VAT number from)	Enter the beginning number in a range of GUI/VAT numbers.
GUI/VAT No. To (GUI/VAT number to)	The system provides a value for this field.
Trans No. To (transaction number to)	The system provides a value for this field.
SN (summary note)	Enter a value that exists in the GUI/VAT Summary Note (75T/SN) UDC table to specify whether a record is a summary record. Values are: Blank: Not a summary record. A: Summary record
Doc Count (document count)	Enter the number of documents for the transaction.
Sal Item (sales item type)	Enter a value that exists in the GUI/VAT Sales Item Type (75T/ST) UDC table to specify the type of item sold. Values are: Blank: General sales item 1: Land 2: Other fixed asset
Zr Tx (zero tax rate)	Enter a value that exists in the GUI/VAT Zero Tax Rate Item Type (75T/TX) UDC table to specify the type of zero-tax-rate item. Values are: Blank: Not a zero tax-rate item. ZNC: Export not through customs. ZTC: Export through customs
DU (deduction usage)	Enter a value that exists in the Deduction Usage (75T/DD) UDC table to specify the deduction usage code. Values are: Blank: No deduction 1: Machine equipment 2: Raw materials 3: Part materials 4: Fuel 5: Semi-finished goods
Proof Doc Name (proof document name)	Enter a value that exists in the GUI/VAT Proof Document Name (75T/PD) UDC table to specify the name of the document that proves that an exported item did not go through customs. Examples are: BOE: Exchange Memo DFE: Declaration For Exportation GUI: Deduction Copy Of GUI
Proof Doc Number (proof document number)	Enter the number of the document that proves that an exported item did not go through customs.
EM (exportation method)	Enter a value that exists in the Exportation Method (75T/EX) UDC table to specify the exportation method. Values are: Blank: Not exported 1: Export method 1 2: Export method 2 3: Export method 3 4: Export method 4 5: Export method 5 6: Export method 6 7: Export method 7
ED Ty (export declaration type)	Enter a value that exists in the Ex. Declare Type (75T/EP) UDC table to specify the export declaration type. Values are: Blank: No export declaration type B1: Export declare type B1 B2: Export declare type B2 B8: Export declare type B8 B9: Export declare type B9 D1: Export declare type D1 D3: Export declare type D3 D4: Export declare type D4 D5: Export declare type D5 D9: Export declare type D9
Exportation Doc. No. (exportation document number)	Enter the document number of the declaration for exportation.
Export Date	Enter the date that the item was exported.
Rev Typ (revenue type)	Enter a value that exists in the GUI/VAT Revenue Type (75T/RT) to specify the type of revenue received. Examples are: R01: Sales revenue R02: Commission revenue
Spc Tax (special tax)	Enter a value that exists in the GUI/VAT Special Tax Type (75T/SP) UDC table to specify whether a special tax rate applies to the transaction. Values are: Blank: No special tax 1: Special food and beverage service, hostesses provided 2: Special food and beverage service, providing show programs 3: Banking, authorized business 4: Reinsurance 5: Banking, non-authorized business
Sold To Number	Enter the customer number. For POS transactions, this number is often a generic customer number.
Invoice To Number	Enter the customer number associated with the location to which the invoice is sent. For POS transactions, use the same value as you entered in the Sold To Number field or leave this field blank.
Ship To Number	Enter the customer number associated with the location to which you ship the goods sold. For POS transactions, use the same value as you entered in the Sold To Number field or leave this field blank.
Alpha Name	The system completes this field with the name of the customer.
Prtnr UN (business partner uniform number)	Enter the uniform number of the business partner. For POS transactions, leave this field blank.
MT (merged transactions)	Specify whether the GUI/VAT transaction was created from multiple invoices or sales orders. Values are: 0: Not merged 1: Merged
Doc Co (document company)	Enter the document company of the transaction.
Doc Type (document type)	Enter a value that exists in the Document Type (00/DT) UDC table to specify the company for which the general ledger entry exists.
Doc Number (document number)	Enter the document number of the original transaction, such as the journal entry created for the POS transactions.
Batch Number	Enter the batch number of the posted transaction.
Order Co (order company)	Enter the company number of the company that originated the transaction.
Order Ty (order type)	Enter the order type of the original transaction.
Order Number	Enter the number of the original voucher, invoice, journal entry or other document.
Order Suf (order suffix)	Enter the pay item number.
Item Description	Enter the item description.
Quantity Ordered	Enter the quantity.
CC 1 (category code 1)	Enter a value that exists in the GUI/VAT Cat. Code 1 - Segment (75T/C1) UDC table to associate additional information to the segment number.
CC 2 (category code 2)	Enter a value that exists in the GUI/VAT Cat. Code 2 - Segment (75T/C2) UDC table to associate additional information to the segment number.
CC 3 (category code 3)	Enter a value that exists in the GUI/VAT Cat. Code 3 - Segment (75T/C3) UDC table to associate additional information to the segment number.
CC 4 (category code 4)	Enter a value that exists in the GUI/VAT Cat. Code 4 - Customer (75T/C4) UDC table to associate additional information to the customer.
CC 5 (category code 5)	Enter a value that exists in the GUI/VAT Cat. Code 5 - Customer (75T/C5) UDC table to associate additional information to the customer.
CC 6 (category code 6)	Enter a value that exists in the GUI/VAT Cat. Code 6 - Customer (75T/C6) UDC table to associate additional information to the customer.
Prt Cnt (print count)	Enter the number of GUIs printed.
Sales Person	Enter the sales person ID number.
Cash Register	Enter the cash register ID number.
Citizenship ID	Enter the citizenship number of an individual.
Credit Card Number	Enter the credit card number used for the transaction.
Payment Type	Enter the type of payment, such as cash, check, or credit card.
PS (prepayment status)	Enter a value that exists in the GUI/VAT Prepayment Status (75T/PS) UDC table to specify if a transaction is a prepayment. Values are: Blank or 0: Not a prepayment 1: Prepayment, not delivered 2: Prepayment, item delivered
Prepay Balance	Enter the remaining balance to be paid.

Manually Add Transaction Detail Data

Access the GUI/VAT Detail POS Interface Revision form.

Ln ID (line ID)	Enter the transaction line ID or sequence line ID of the GUI/VAT transaction.
Short Item No (short item number)	The JD Edwards EnterpriseOne system enables you to create three different item numbers. Enter the short item number.
2nd Item Number	The JD Edwards EnterpriseOne system enables you to create three different item numbers. Enter the 2nd item number.
3rd Item Number	The JD Edwards EnterpriseOne system enables you to create three different item numbers. Enter the 3rd item number.
Description	Enter the description of the item.
UM (unit of measure)	Enter a value that exists in the Unit of Measure (00/UM) UDC table to specify the unit of measure for the item.
Quantity Ordered	Enter the quantity of the item.
Taxable	Enter a value that exists in the H00/TV UDC table to specify whether the item is taxable.
Tx Ex (tax explanation code)	Enter a value that exists in the Tax Explanation Codes (00/EX) UDC table to specify the reason the item is or is not taxable.
Tax Area	Enter a value that exists in the Tax Areas table (F4008) to specify the tax rate area to use for tax calculations.
Base Curr (base currency)	Enter a value that exists in the Currency Codes table (F0013) to specify the currency of the transaction.
Unit Price	Enter the list or base price to be charged for one unit of this item.
Taxable Amount	Enter the amount on which the tax is calculated.
Tax	Enter the amount of the tax.
Gross Amount	Enter the gross amount of the transaction.
Cur Cod (currency code)	Enter a value that exists in the Currency Codes table (F0013) to specify the currency of the transaction.
Exchange Rate	Enter the number (exchange rate) that a foreign currency amount is multiplied by to calculate a domestic currency amount.
Foreign Unit Price	Enter the unit price in the foreign currency.
Foreign Taxable Amount	Enter the taxable amount in the foreign currency amount.
Foreign Tax	Enter the tax amount in the foreign currency amount.
Doc Co (document company)	Enter the document company of the transaction.
Doc Type (document type)	Enter a value that exists in the Document Type (00/DT) UDC table to specify the company for which the general ledger entry exists.
Doc Number (document number)	Enter the document number of the original transaction, such as the journal entry created for the POS transactions.
Pay Item	Enter the sequence number of the line on the voucher.
Pay Ext (pay extension)	Enter the code to designate an adjusting entry to a pay item on a voucher or invoice.
Order Co (order company)	Enter the company number of the company that originated the transaction.
Order Ty (order type)	Enter the order type of the original transaction.
Order Number	Enter the number of the original voucher, invoice, journal entry or other document.
Order Suf (order suffix)	Enter the pay item number.
Line Number	Enter the number assigned to the line in the transaction record.
TT (tax type)	Enter a value that exists in the GUI/VAT Tax Type (75T/TX) UDC table to specify whether the GUI number is assigned to a taxable transaction. Values are: Blank: Transaction type is IGD or FSV 1: Taxable (General/Special) 2: Zero tax rate 3: Tax-exempt 4: Void or unused
User Code	The system does not use this field unless you provide custom programming.
User Date	The system does not use this field unless you provide custom programming.
User Amount	The system does not use this field unless you provide custom programming.
User Number	The system does not use this field unless you provide custom programming.
User Reference	The system does not use this field unless you provide custom programming. Note. In previous versions of the localization software for Taiwan, this field was used for the GUI number in the Customer Ledger table (F03B11).

Moving Point-of-Sale Data from Work Tables to Transaction Tables

This section provides an overview of how to move POS data to transaction tables and discusses how to run the GUI/VAT POS Interface Inbound program (R75T101).

Understanding How to Move POS Data to Transaction Tables

You use the GUI/VAT POS Interface Inbound program (R75T101) to convert point-of-sale data that has been imported into the F75T004P and F75T005P tables to the F75T004 and F75T005 tables. After each record is successfully inserted in tables F75T004 and F75T005, the system deletes that record from tables F75T004P and F75T005P.

The GUI/VAT POS Interface Inbound program produces a report that lists all of the records in tables F75T004P and F75T005P, and indicates whether the conversion was successful.

Note. The GUI/VAT POS Interface Inbound program does not have data selection or processing options. It converts all data in tables F75T004P and F75T005P.

After the POS data has been converted to tables F75T004 and F75T005, it is available for processing and reporting, just as if the transaction data had been entered directly into the system.

Running the GUI/VAT POS Interface Inbound Program (R75T101)

Select GUI/VAT Advanced & Technical Operations (G75T131), GUI/VAT POS Interface Inbound.