Jet GP Views
Overview
In Dynamics GP, you may have noticed that sometimes, even with friendly names installed, it can be challenging to figure out the meanings for the values in some status fields and type fields - since they are often just lists of integers in the database.
It can also be challenging to figure out how certain tables are related to each other - for creating certain types of reports such as sales, purchasing, or general ledger reports. To solve both of these problems, insightsoftware has created Jet Views for some common report writing areas.
A Jet View combines multiple tables, bringing together important information for a particular area. Jet Views look like other tables in your database, and you can write functions and reports using them, just like you would a table. The Jet function wizard, Report Wizard, and Table Builder allow you to filter for Jet Views.
The following is a brief description of the contents of each view.
- The Customer Views include basic information about the customer including name, number, address, phone number, and balance. Customers sales and returns are available as well and can be filtered by year and period.
- The General Ledger Views give basic general ledger account information including account name, description, account index, segments, and categories. The accounts balance, budget, credits, and debits are also available and can be filtered by year and period.
- The Project Accounting Views hold the forecasted profit, amount, cost, and quantity as well as the posted profit, amount, cost, and quantity. These can be filtered by cost category, project, contract, or customer.
- The Purchasing Views hold purchase invoice information. It combines both purchase line information and purchase header information. Historical and open purchase invoice information is available.
- The Sales Views hold sales invoice information. It combines both sales line and sales header information. Historical and open sales invoices are available.
- The Vendor Views hold basic information about the vendor including, name, number, address, and phone number. Vendors billed and paid amount is also available.
| View Name | Description | Tables & Fields |
|---|---|---|
| Jet Customer Master | There is one row for each customer in the GP company being viewed. This view also includes address information, salesperson information and customer balance. This view is good for customer informational reporting such as lists, labels, etc. and is a good jumping off point for other reports organized by customer. | RM00101RM00102 {Address 1, Address 2, Address 3, City, State, Zip Code, Country, Country Code, Phone 1, Phone 2, Phone 3, Fax, Location Code, UPS Zone} RM00301 {Salesperson First Name, Salesperson Last Name, Commission Code, Commission Percentage} RM00103 {Customer Balance} |
| Jet Customer Open Transactions | There is one row for each entry in the customer open transactions file in the GP company being viewed. This view is good for customer transaction reporting such as aging and open transactions lists. | RM20101 – Customer Transactions (only non-void transactions) RM40401 {Customer Document Type (Link = Document Type), Document Type } RM00101 {Customer Name, Customer Class, Customer Status (Active/Inactive), Customer Territory, Customer Salesperson, Customer Contact, Customer Phone 1, Customer Credit Limit Amount, Customer Payment Terms, Customer Balance Type (Open/Bal Fwd)} RM00103 {Customer Balance} |
| Jet Customer Open Application | There is one row for each entry in the applied transactions file in the GP company being viewed. This view allows you to see the transactions that are applied to a transaction that you already have. An example of this can be found in the sample A/R aging report which uses this view linked into the Jet Customer Open Transactions view. | RM20201RM40401 {Customer Document Type ( Link = Applied to Doc Type ), Applied To Doc Type } |
| Jet Customer Period Balance | There is one row for each Customer Record and Fiscal Period in the GP company being viewed. This view allows you to see balance for a particular customer for a particular period in the GP company being viewed. | RM20101 {Customer Number, Period Balance} SY40100 {Year Filter, Period ID, Period Start, and Period End} |
| Jet Date | There is one row for each Day, Week, Month, Quarter, and Year between 1/1/1900 and 12/31/2100. This view allows you to see dates of different types(date, week, month, quarter, year). It also allows for the easy creation of date filters for reports | JET_DATE {Period Start, Period End, Period Type, Period No, Period Name} |
| Jet Fiscal Period | There is one row for each fiscal period for each Series in the GP company being viewed. This view allows you to see the start and end date of each period for the fiscal year, the number of periods in the fiscal year, the period name and whether or not the fiscal period is closed for the series. | SY40101 { Fiscal Year ( Link = Fiscal Year ), Start Date, End Date, Number of Periods, Is Historical Year } |
| Jet GL Account | There is one row for each GL Account in the GP company being viewed. This view allows you to see the account number, it’s category, it’s description, the individual segments for the account, and their descriptions, the posting type, and whether or not it’s active. | GL00100 {Account Description, Posting Type, Active} GL00105 {Account Index ( Link = Account Index ), Account Number, Segments} GL00102 {Category Number( Link = Category Number ), Category Description} |
| Jet GL Transactions | There is one row for each GL Transaction in the GP company being viewed. This view allows you to see the transaction date, description, account, credit amount, debit amount, transaction amount, period, document date, vendor, account category, and account description for each transaction. | GL20000 {Transaction Description, Transaction Date, Account Index ( Link = Account Index ), Credit Amount, Transaction Amount, Debit Amount, Period ID, Document Date, Vendor Name} GL30000 {Transaction Description, Transaction Date, Account Index ( Link = Account Index ), Credit Amount, Transaction Amount, Debit Amount, Period ID, Document Date, Vendor Name} GL00105 {Account Number, Segments} GL00102 {Category Number ( Link = Category Number ), Category Description} GL00100 {Account Description, Posting Type} |
| Jet Item All Locations | There is one row for each Item in the GP company being viewed. The quantities reflect the total of all locations. This view allows you to see the Item Number, Description, Type, Cost, Weight, Class, Class Description, Valuation Method, Unit of Measure, Price Level, Price Group, Price Method, Country of Origin, Quantity on Hand, Quantity Committed, Quantity Sold, Quantity Returned, Quantity On Order, and Quantity Back Ordered. | IV00102 {Item Number, Item Description, Item Type, Standard Cost, Current Cost, Shipping Weight, Drop Ship Index, Class Code, Lot Type, Valuation Method, Unit of Measure Schedule, Warranty Days, Price Level, Item Code, Price Group, Price Method, Purchasing Unit Of Measure, and Selling Unit Of Measure} IV00102 {Quantity Committed, Quantity Sold, Quantity Returned, Quantity On Order, and Quantity Back Ordered} IV40800 {Pricel Level Description} IV40900 {Price Group Description} IV40400 {Class Description} IV40201 {Unit Of Measure Schedule Desc, Base Unit of Measure} |
| Jet Item By Location | There is one row for each Item in the GP company being viewed for each location. This view allows you to see the Item Number, Description, Type, Cost, Weight, Class, Class Description, Valuation Method, Unit of Measure, Price Level, Price Group, Price Method, Country of Origin, Quantity on Hand, Quantity Committed, Quantity Sold, Quantity Returned, Quantity On Order, and Quantity Back Ordered. | IV00102 {Item Number, Item Description, Item Type, Standard Cost, Current Cost, Shipping Weight, Drop Ship Index, Class Code, Lot Type, Valuation Method, Unit of Measure Schedule, Warranty Days, Price Level, Item Code, Price Group, Price Method, Purchasing Unit Of Measure, and Selling Unit Of Measure} IV00102 – (Link = Item Number) {Quantity Committed, Quantity Sold, Quantity Returned, Quantity On Order, and Quantity Back Ordered.} IV40800 – (Link = Price Level) {Pricel Level Description} IV40900 – (Link = Price Group) {Price Group Description} IV40400 – (Link = Class Code) {Class Description} IV40201 – (Link = Unit of Measure Schedule) {Unit Of Measure Schedule Desc, Base Unit of Measure} |
| Jet Payroll Transactions | There is one row for each payroll transaction in the GP company being viewed. This view allows you to see the check number, date, department, payroll code, document type, amount, employee name, and employee number for each payroll transaction. | UPR30300 {Check Date, Check Number, Department, Payroll Code, Transaction Beginning Date, Transaction Ending Date, Pay Rate, Transaction Amount, Pay Advance, State, Local Tax, Workers Comp, Shift Code, Days Worked, Taxable Wages, Weeks Worked, and Employee Class} UPR00100 – ( Link =Employee Number ) {Employee Number, Employee Last Name, Employee First Name, Employee Middle Name, Social Security Number, Birth Date, Gender, Job Title, Location ID, Vacation Available, Sick Time Available, and Marital Status} |
| Jet Project Accounting | There is one row for each Customer, Contract, Project, and Cost Category in the GP company being viewed. This view allows you to see the Customer number, Contract, Project, forecasted profit, forecasted quantity, forecasted cost, actual revenue, actual costs, actual quantities, and actual billings for each Cost Category. | PA01301 {Cost Category ID, Forecast Quantity, Forecast Total Cost, Forecast Profit, and Forecast Profit Amount} PA33302 – ( Link = Project Number ) {Posted Revenue, Posted Cost, Posted Quantity, and Posted Billings} PA01201 – ( Link = Cost Category ) {Project Number} PA01111 – ( Link = Customer Number ) {Customer Number, Contract Number} |
| Jet Purchase Detail | There is one row for each purchase line in the GP company being viewed. This view allows you to see the unit cost, extended cost, item number, item description, document date, PO status, PO number, vendor number and quantity for each PO line. Both posted and open PO’s are available. | POP30100 {PO Number, PO Status, PO Type, Confirm With, Document Date, Promised Date, Promised Ship Date, Required Date, Requisition Date, Shipping Method, Vendor Number, Vendor Name, Minimum Order, Vendor Address Code, Vendor Company ID, Primary bill-to Address Code, Primary Ship-to Address Code, Vendor Company Name, Vendor Contact, Address 1, Address 2, Address 3, City, State, Zip Code, Country Code, Country, Phone 1, Phone 2, Phone 3, Fax, Payment Terms ID, Discount Date, Due Date, Created Date, Buyer ID, and POP Contract Number} POP10100 {PO Number, PO Status, PO Type, Confirm With, Document Date, Promised Date, Promised Ship Date, Required Date, Requisition Date, Shipping Method, Vendor Number, Vendor Name, Minimum Order, Vendor Address Code, Vendor Company ID, Primary bill-to Address Code, Primary Ship-to Address Code, Vendor Company Name, Vendor Contact, Address 1, Address 2, Address 3, City, State, Zip Code, Country Code, Country, Phone 1, Phone 2, Phone 3, Fax, Payment Terms ID, Discount Date, Due Date, Created Date, Buyer ID, and POP Contract Number} POP30110 – ( Link = PO Number ) {PO Line Status, Line Item Number, Line Item Description, Line Quantity Ordered, Vendor Line Item Number, Vendor Line Item Description, Location Code, Unit of Measure, Unit Cost, Extended Cost, Item Required Date, Item Requested Date, Release By Date, Released Date, Line Number, and Release} POP30110 – ( Link = PO Number ) {PO Line Status, Line Item Number, Line Item Description, Line Quantity Ordered, Vendor Line Item Number, Vendor Line Item Description, Location Code, Unit of Measure, Unit Cost, Extended Cost, Item Required Date, Item Requested Date, Release By Date, Released Date, Line Number, and Release} POP10110 – ( Link = PO Number ) {PO Line Status, Line Item Number, Line Item Description, Line Quantity Ordered, Vendor Line Item Number, Vendor Line Item Description, Location Code, Unit of Measure, Unit Cost, Extended Cost, Item Required Date, Item Requested Date, Release By Date, Released Date, Line Number, and Release} |
| Jet Purchase Header | There is one row for each PO in the GP company being viewed. This view allows you to see the PO document information including PO document totals. It shows document date, shipping method, subtotal, freight amount, miscellaneous charges, tax amount, vendor number, vendor name, and discount amounts. Both posted and open PO’s are available. | POP30100 {PO Number, PO Status, PO Type, Confirm With, Document Date, Promised Date, Promised Ship Date, Required Date, Requisition Date, Shipping Method, Remaining Subtotal, Subtotal, Freight Amount, Misc Charges Amount, Tax Amount, Vendor Number, Vendor Name, Minimum Order, Vendor Address Code, Vendor Company ID, Primary Bill-to Address Code, Primary Ship-to Address Code, Vendor Company Name, Vendor Contact, Address 1, Address 2, Address 3, City, State, Zip Code, Country Code, Country, Phone 1, Phone 2, Phone 3, Fax, Payment Terms ID, Discount Dollar Amt, Discount Percent Amt, Discount Amt Available, Discount Date, Due Date, Created Date, Buyer ID, Freight Tax Amt, Misc Tax Amount, and POP Contract Number} POP10100 {PO Number, PO Status, PO Type, Confirm With, Document Date, Promised Date, Promised Ship Date, Required Date, Requisition Date, Shipping Method, Remaining Subtotal, Subtotal, Freight Amount, Misc Charges Amount, Tax Amount, Vendor Number, Vendor Name, Minimum Order, Vendor Address Code, Vendor Company ID, Primary Bill-to Address Code, Primary Ship-to Address Code, Vendor Company Name, Vendor Contact, Address 1, Address 2, Address 3, City, State, Zip Code, Country Code, Country, Phone 1, Phone 2, Phone 3, Fax, Payment Terms ID, Discount Dollar Amt, Discount Percent Amt, Discount Amt Available, Discount Date, Due Date, Created Date, Buyer ID, Freight Tax Amt, Misc Tax Amount, and POP Contract Number} |
| Jet Sales Detail | There is one row for each sales line in the GP company being viewed. This view allows you to see the line item numbers and descriptions, unit cost, unit price, extended price, remaining price, and quantities for each sales line. Both the posted and open sales lines are included. | SOP30200 {Document Type, Document Number, Posting Date, Document Date, Order Date, Invoice Date, Document Back Order Date, Document Fulfillment Date, Document Actual Ship Date, Due Date, Location Code, Customer Number, Customer Name, Customer PO Number, Contact Person, Ship to Name, Address 1, Address 2, Address 3, City, State, Zip Code, Country Code, Country Name, Phone 1, Phone 2, Phone 3, Fax, Shipment Method, Document Status, Void Status, Currency ID, Salesperson ID, Sales Territory, UPS Zone, and Document Payment Amount Received} SOP10100 {Document Type, Document Number, Posting Date, Document Date, Order Date, Invoice Date, Document Back Order Date, Document Fulfillment Date, Document Actual Ship Date, Due Date, Location Code, Customer Number, Customer Name, Customer PO Number, Contact Person, Ship to Name, Address 1, Address 2, Address 3, City, State, Zip Code, Country Code, Country Name, Phone 1, Phone 2, Phone 3, Fax, Shipment Method, Document Status, Void Status, Currency ID, Salesperson ID, Sales Territory, UPS Zone, and Document Payment Amount Received} SOP30300 – ( Link = Document Number ) {Line Item Number, Line Item Description, Unit of Measure, Unit Cost, Unit Price, Unit Extended Price, Unit Remaining Price, Unit Quantity, Unit Qty Damaged, Unit Qty Returned, Unit Qty on Hand, Unit Qty Ordered, Unit Qty Received, and Unit Qty on Invoice} SOP10200 – ( Link = Document Number ) {Line Item Number, Line Item Description, Unit of Measure, Unit Cost, Unit Price, Unit Extended Price, Unit Remaining Price, Unit Quantity, Unit Qty Damaged, Unit Qty Returned, Unit Qty on Hand, Unit Qty Ordered, Unit Qty Received, and Unit Qty on Invoice} |
| Jet Sales Header | There is one row for each sales order in the GP company being viewed. This view allows you to see the customer, document amount, document status, and document date for sales documents. Both the posted and open sales documents are included. | SOP30200 {Document Type, Document Type Description, Document Number, Posting Date, Document Date, Order Date, Invoice Date, Document Back Order Date, Document Fulfillment Date, Document Actual Ship Date, Due Date, Location Code, Customer Number, Customer Name, Customer PO Number, Contact Person, Ship to Name, Fax, Shipment Method, Document Status, Void Status, Currency ID, Salesperson ID, Sales Territory, UPS Zone, and Document Payment Amount Received, Commission Amount, Discount Available Amount, Document Amount, and Subtotal} SOP10100 {Document Type, Document Type Description, Document Number, Posting Date, Document Date, Order Date, Invoice Date, Document Back Order Date, Document Fulfillment Date, Document Actual Ship Date, Due Date, Location Code, Customer Number, Customer Name, Customer PO Number, Contact Person, Ship to Name, Address 1, Address 2, Address 3, City, State, Zip Code, Country Code, Country Name, Phone 1, Phone 2, Phone 3, Fax, Shipment Method, Document Status, Void Status, Currency ID, Salesperson ID, Sales Territory, UPS Zone, and Document Payment Amount Received, Commission Amount, Discount Available Amount, Document Amount, and Subtotal} |
| Jet Vendor Open Application | There is one row for each apply-from document in the GP company being viewed. This view allows you to see the amount applied, remaining amount, the vendor, the document type, the document number, and the due date for each open payable document. | PM10200 {Applied To Doc Number, Voucher Number, Applied Amount, Applied From Doc Number, Applied From Doc Date, and Vendor Number} PM40102 – ( Link = Applied To Doc Type ) {Applied To Doc Type and Applied From Doc Type} PM30300 {Applied To Doc Number, Voucher Number, Applied Amount, Applied From Doc Number, Applied From Doc Date, and Vendor Number} |
| Jet Vendor Open Transactions | There is one row for each open payable document and the amount applied in the GP company being viewed. This view allows you to see the amount applied, remaining amount, the vendor, the document type, the document number, and the due date for each open payable document. | PM20000 {Document Number, Document Date, Due Date, Discount Date, Amount, Remaining Amount, Vendor Balance, Discount %, Discount Available, Discount Taken, Purchases Amount, Freight Amount, Misc Amount, Tax Amount, Trade Discount Amount, Write Off Amount, PO Number, Shipping Method, Currency ID, Payment Type, Apply Withholding, Batch Number, Batch Source, and Voucher Number} PM40102 – ( Link = Document Type ) {Document Type Type} PM00200 – ( Link = Vendor Number ) {Vendor Number, Vendor Name, and Vendor Class} |
| Jet Vendor Period Balance | There is one row for each period in the fiscal calendar of the GP company being viewed. This view allows you to see the balance for a vendor as of a particular period. | PM20000 {Vendor Number, Period Balance} SY40100 – ( Link = Period End, Period Start, Year Filter ) {Year Filter, Period ID, Period Start, and Period End} |
| Jet Vendor Master | There is one row for each vendor and each period in the fiscal year in the GP company being viewed. This view allows you to see information about each individual vendor. Such as: Vendor Number, Vendor Name, Address, Class, Contact, Phone, Fax, Shipping Method, Payment Terms, Amount Billed, and Amount Paid. | PM00100 – ( Link = Vendor Class ID ) {Class Description, and Vendor Class ID} PM00200 {Vendor Number, Vendor Name, Primary Address Code, Purchase Address Code, Ship From Address Code, Remit To Address Code, Class Description, Contact, Address 1, Address 2, Address 3, City State, Zip Code, Country, Phone 1, Phone 2, Phone 3, Fax, UPS Zone, Shipping Method, Status, Currency ID, Minimum Order, Payment Terms ID, Min Payment Percent, Min Payment Amount, Max Invoice Amount, Comment 1, Comment 2, Credit Limit Amount, Discount Grace Period, Due Date Grace Period, and Country Code} PM00202 – ( Link = Vendor Number ) {Period Filter, Year Filter, Amount Billed, Amount Paid, and Number of Invoices} |