Search Invoices Report

From UG

(Difference between revisions)
Jump to: navigation, search
(→SOW 5)
(→SOW 9 Multi-select filter for EDI status in "Search Invoices" report)
 
(19 intermediate revisions not shown)
Line 57: Line 57:
There are number of options for output:
There are number of options for output:
-
'''Convert to Currency (dropdown)'''
+
==== Convert to Currency ====
-
: this defines in what additional currency to display invoice amount (extra column on the report)
+
 
 +
* dropdown
 +
 
 +
* this defines in what additional currency to display invoice amount (extra column on the report)
 +
 
 +
* conversion logic: [[Exchange Rates#v1]]
 +
 
 +
==== Show VAT Separately ====
 +
 
 +
: checkbox
-
'''Show VAT Separately (checkbox)'''
 
: if checked then additional column will be added with VAT amount (where appropriate)
: if checked then additional column will be added with VAT amount (where appropriate)
Line 374: Line 382:
Ability to include/exclude specific CC required
Ability to include/exclude specific CC required
-
=== SOW 5 ===
+
== SOW 5 Add filter option to include or exclude cc & option to multi select cc for Search SI & Multiple P&L reports ==
 +
 
'''0003472''': Add filter option to include or exclude cc & option to multi select cc for Search SI & Multiple P&L reports
'''0003472''': Add filter option to include or exclude cc & option to multi select cc for Search SI & Multiple P&L reports
Line 384: Line 393:
**Currently reports only allow user to select 1 cc at a time, this can be very tedious when filtering according to multiple charge codes.
**Currently reports only allow user to select 1 cc at a time, this can be very tedious when filtering according to multiple charge codes.
-
=== SOW 6 ===
+
== SOW 6 Change exchange rate logic on this Report ==
 +
 
 +
mantis: 0004196
 +
 
 +
spec: Currency exchange rate is based now on the [http://wiki.jaguarfreight.com/wiki/Sales_Invoice#Issuing_Date date of issuing] invoice - this is "new" currency exchange logic, ''per Kostya (from 2014-01-02)''.
 +
 
 +
== SOW 7 Add new output fields and filters ==
 +
 
 +
'''mantis''': 0004188
 +
 
 +
'''spec''':
 +
 
 +
'''Business Need:''' Main objective right now is to identify how quickly, or not, we invoice shipments (in relations to the arrival date) – and what the trends are per office/client/mode etc.
 +
 
 +
'''Changes''':
 +
 
 +
* Add "Client Companies" filter with "Exclude selected Client Companies" option
 +
* For all but PI types add drop down with options:
 +
** Invoices with Credit Notes
 +
** Invoices without Credit Notes
 +
** Credit Notes only
 +
 
 +
* Add columns to the output (for Sales Invoices option):
 +
** Date of Actual Arrival
 +
** Client Company
 +
** MOT
 +
** Issued By
 +
 
 +
== SOW 8 Reporting invoices for which no message is received in return ==
 +
 
 +
mantis: 4737
 +
 
 +
'''spec''' below
 +
 
 +
'''Requirement''':
 +
 
 +
* Dashboard: Invoices transmitted but not accepted
 +
* List invoices for which no accepted message is received 1 day after they are sent
 +
 
 +
'''Solution''':
 +
 
 +
* The System set EDI Status = ''"Transmitted, not received"'' for Invoices that were sent more than 1 day ago and haven't received any status from D2L.
 +
* New "Transmitted, not received" value added to the list of values in "EDI Status" filter of the report
 +
 
 +
== SOW 9 Multi-select filter for EDI status in "Search Invoices" report ==
 +
 
 +
mantis: 4687
 +
 
 +
'''spec''' below
 +
 
 +
'''Requirement''':
 +
 
 +
* Initially it was required to provide combined filtering of "Rejected and Failed" statuses in "Search Invoices" report.
 +
 
 +
'''Solution''':
 +
 
 +
* Implementation of "EDI status" filter was re-made to provide [http://wiki.jaguarfreight.com/wiki/Reports_Filters#Multiselect multi-selecting] of all possible statuses for the reporting

Current revision as of 14:01, 13 February 2015


Contents

[edit] Info

Scope:

This covers this one report: "Search Invoices".

[edit] Spec

[edit] Intro

You can search these types of invoices:

To specify what type of report to search select appropriate radio button (see Figure below).

Before running report you have to specify "user defined conditions" through report #Filters.

[edit] Hard coded or Hidden conditions

There are no Hard coded or Hidden conditions for this report which means that system will pull ALL invoices that satisfy conditions you set by filters.

[edit] Filters

Filters here are of two types (also see Figure below.):

[edit] Common filters

Invoice # contains (textbox)

system will report any invoice that contains defined string of characters.
For example if user types "abc" then system will report on the following types of invoice #s: "abc65434a"; "654abc987"; "8765abc"

Date (from datebox to datebox)

For Sales/Internal invoices: this is a condition for invoice's "Issuing date"
For Purchase invoices: this is a condition for invoice's "Posted date"

Original Amount (from ... to ...)

this is a condition for invoice's total amount (in its currency entered?)


[edit] Invoice specific filters

See invoice type specific sections below.

[edit] Output settings

There are number of options for output:

[edit] Convert to Currency

  • dropdown
  • this defines in what additional currency to display invoice amount (extra column on the report)

[edit] Show VAT Separately

checkbox
if checked then additional column will be added with VAT amount (where appropriate)

[edit] Purchase Invoices

[edit] Filters

Receiving Office:

all Jag offices or specific one office

Vendor:

Company that issued invoice as defined in Admin > Address Book > Vendors

See also #Common filters

[edit] HTML output

[edit] Columns

  • Receiving Office - office received and entered this invoice into CT2
  • Vendor - company issued invoice
  • Invoice # - as defined by vendor; hyperlink leads to edit inv page
  • Date - date invoice was entered into CT2
  • CT/M #s - mapping of this invoice amount to CT or M (multiple CT/M numbers could be listed if amount is split)
  • Currency - currency of the Inv
  • Total Amt - amount in currency of the Inv
  • USD Total Amt - Inv amount converted to USD
  • Status - status of the invoice. See more on PI status Incomplete_PI_Report#Output

[edit] Buttons (Actions)

  • Go back - back to Filters page
  • Edit - edit selected invoice - not sure why this is here ???
  • Remove - not sure why this is here ???
  • Download to Excel

[edit] Example

[edit] Spreadsheet

Spreadsheet version of this report has all the columns found on HTML except "Status" column.

[edit] Another Example of HTML/xls (with VAT and other filters)

  • spreadsheet

[edit] Sales Invoices

Example of xls output:

[edit] Internal Invoices

  • filters



  • HTML

  • xls

look same as HTML

[edit] SOW 0

[edit] Filters

  • Invoice Type > radio button
    • Purchase Invoices
    • Sales Invoices
    • Internal Invoices
  • Receiving Office > dropdown (office lists and All option) > for Purchase or Internal Invoices radio button selected
  • Issuing Office > dropdown (office lists and All option) > for Sales or Internal Invoices radio button selected
  • Vendor > dropdown list (vendor)> for Purchase Invoices radio selected
  • Bill To Party company > dropdown list > for Sales Invoices radio selected
  • Invoice # contains> text box
  • Date > from/to date calendar
  • Amount > from/to number fields

[edit] Button

  • Go back > go back to the previous page
  • Reset Form > clear all filter
  • Generate Result > HTML result(s) with CT#links and paging
    • Go back > go back to the previous page
    • Edit > edit record by selecting record that need to be edited
    • Remove > remove record by selecting record that need to be removed
    • Download to Excel > Excel report with CT# links

[edit] Output

  • 8 columns for Purchase Invoices:
    • Receiving office
    • Vendor
    • Invoice# with links
    • Date
    • CT/M#s
    • Currency
    • Amount
    • Status > show X with description for incomplete invoices
  • 7 columns for Sales Invoices:
    • Issuing office
    • Bill To Party Company
    • Invoice# with links
    • Date
    • CT/M#s
    • Currency
    • Amount
  • 7 columns for Internal Invoices:
    • Issuing office
    • Receiving office
    • Invoice# with links
    • Date
    • CT/M#s
    • Currency
    • Amount
  • Button
    • Go back
    • Edit
    • Remove
    • Download to Excel
  • With paging and sorting for all reports.

[edit] With download to xls

  • Excel format

Same as HTML (ZUL)

[edit] Tweak V.1.a

tw1) Add radio button for Credit Note Invoices to the forth choice of Invoice Type filter

Steps

  • If user selects Credit Note Invoices radio button
  • Filter dynamic changes"
    • Issuing Office > dropdown (office lists and All option) > Credit Note Invoices Invoices radio button selected
    • Bill To Party company > dropdown list > for Credit Note Invoices radio selected
    • Date > from/to date calendar
    • Amount > from/to number fields
  • User set filters and clicks "generate result" button
  • See result

tw2) HTML report 8 columns for Credit Note Invoices:

  • Issuing office
  • Bill To Party Company
  • Credit Note# with links
  • Issued by
  • Date
  • CT/M#s with links
  • Currency
  • Amount

tw3) Buttons on the HTML result page

  • Go back
  • Download to Excel

tw4) With paging and sorting for all reports.

tw5) Result shows for both approved and unapproved(pending) credit notes.

  • Credit Note Invoice consists of credit notes that issued against sale invoice and internal invoice; Result will show by category of type of invoice separating by tabs. See figure 1 below.
    • External Invoices
    • Internal Invoices
  • Each type of credit note invoice shows how many record on the tab.
  • When a tab is open, it should show 20 records per page.

Note: One tab is open at a time.

tw6)Make hyperlink for CT/M#s for purchase, sale, Internal and Credit Note invoices on HTML report and EXCEL report

NOTE: Tw1)-tw6) are updated by Tira 3:30 PM 17 Jan 2010


[edit] Figure 1.

File:CreditNoteinv.JPG

[edit] SOW 1

0003278: (Acc Report:Search Inv) Add date type - departure date or revenue recognition date (PI invoice only)

MO would like to add date type Departure date to the search invoice report.

  • This date is associated with the shipment (or Master) that is related to the invoice.

This is about adding this date as a filter and as a column on a report.

Label should read "Departure Date (Revenue Recognition Date)"

See mapping here: Revenue Recognition Date

This should be for PI invoice only (for now).

Output should indicate the date type on the heading of the column.

Note that currently "Date" on both filters page and output corresponds to "Issuing date". Please use this new label for this existing date.

Also, adding the option to select a date range or month and year.

[edit] Case: PI has several CTs or and masters associated

See #PI case: Extended layout

[edit] Examples

[edit] Purchase Invoice Screen

File:Purchase invoice added dates 2.JPG

[edit] What is revenue recognition date for invoice

If at least of the CTs associated with invoice satisfy dates criteria then include this invoice into report.

[edit] SOW 2

0003218: (Acc Report:Search Inv) Add MOT to filters and to output

[edit] MOT as filter

If this filter is not set, it should default to all modes of transport.

It should allow multi-select option.

This MOT is associated not with invoice but with CT or Master that are associated with specific inv on this report.

Therefore we would need 2 filters: one for Master and one for CT.

Pls note that these lists are different. Master MOT has only 3 options.

[edit] MOT on output

MOT should be included in the both html and excel output.

[edit] MOT for invoice types

[edit] PI case: Extended layout

Additional logic is required due to below: 

One invoice can have several cts or/and masters associated with this invoice. These cts can have 
different values for filters selected. For example in case of MOT filter - pls see for example PI 4536456 on dev server - 
this invoice is associated with two CTs - 359217 (air) and 359218 (ocean-lcl).

To address above layout of report should be changed.

System should look at each individual line in TOC and post into report line that satisfy selected filter criteria along with associated amount and additional columns: CC#, etc.

[edit] MOT filter case: PI associated with individual CTs and masters at one time 
Pls note that PI can be associated with individual cts and masters at one time - so pls explain 
how MOT filter should work in this case.

"Master MOT filter" would control TOC lines associated with Master and likewise for "CT MOT filter".

[edit] SI and II

One SI could be associated with multiple shipments (when they are grouped). But in this case MOT is the same so there should be no problem.

[edit] BR 3

Example provided by Karen:

Give me all sales invoice created July 1, 2011 - September 30, 2011 that are related to a departure after September 30, 2011 or before July 1, 2011

[edit] SOW 3

0003281: (Acc Report:Search Inv) Add filter option to satisfy both Issuing AND Departure dates criteria.

This is for Sales Invoices only.

Add 3rd option to Date type (radio button choice): "Issuing and Departure date".

Rename "Date type" label into "Date filter options".

In this case let user define both Issuing and Departure date.

Display results that satisfy both Issuing AND Departure dates criteria.

[edit] BR 4

Per Karen:

Reports should include and exclude specific charge codes

The same way that the option to run the report showing VAT charges separately, MO would like the option to run this report showing selected charge codes separately.

The charge codes that are displayed can be displayed as a charge code group.

[edit] SOW 4

0003280: (Acc Report:Search Inv) Charge Code Group / Charge Code Number

Add "Special View" options to report that will add "Charge Code Group" or "Charge Code Number" to output.

This is for all types of Invoices.

For each invoice provide breakdown of charges per "Charge Code Group" or "Charge Code Number".

Use standard we implemented for Profit Loss for Multiple CTs as a guideline for output style and for Filters page design.

Filters page will have "Special View" label with (radio buttons):

  • Off
  • Charge Code Group
  • Charge Code Number

Ability to include/exclude specific CC required

[edit] SOW 5 Add filter option to include or exclude cc & option to multi select cc for Search SI & Multiple P&L reports

0003472: Add filter option to include or exclude cc & option to multi select cc for Search SI & Multiple P&L reports

Requirement for previous task under SOW 4 "ability to include/exclude specific CC" was missed, therefore this task SOW 5 was created.

In addition, another requirement was received:

  • Ability to multi-select charge codes
    • Currently reports only allow user to select 1 cc at a time, this can be very tedious when filtering according to multiple charge codes.

[edit] SOW 6 Change exchange rate logic on this Report

mantis: 0004196

spec: Currency exchange rate is based now on the date of issuing invoice - this is "new" currency exchange logic, per Kostya (from 2014-01-02).

[edit] SOW 7 Add new output fields and filters

mantis: 0004188

spec:

Business Need: Main objective right now is to identify how quickly, or not, we invoice shipments (in relations to the arrival date) – and what the trends are per office/client/mode etc.

Changes:

  • Add "Client Companies" filter with "Exclude selected Client Companies" option
  • For all but PI types add drop down with options:
    • Invoices with Credit Notes
    • Invoices without Credit Notes
    • Credit Notes only
  • Add columns to the output (for Sales Invoices option):
    • Date of Actual Arrival
    • Client Company
    • MOT
    • Issued By

[edit] SOW 8 Reporting invoices for which no message is received in return

mantis: 4737

spec below

Requirement:

  • Dashboard: Invoices transmitted but not accepted
  • List invoices for which no accepted message is received 1 day after they are sent

Solution:

  • The System set EDI Status = "Transmitted, not received" for Invoices that were sent more than 1 day ago and haven't received any status from D2L.
  • New "Transmitted, not received" value added to the list of values in "EDI Status" filter of the report

[edit] SOW 9 Multi-select filter for EDI status in "Search Invoices" report

mantis: 4687

spec below

Requirement:

  • Initially it was required to provide combined filtering of "Rejected and Failed" statuses in "Search Invoices" report.

Solution:

  • Implementation of "EDI status" filter was re-made to provide multi-selecting of all possible statuses for the reporting
Retrieved from "https://wiki.jaguarfreight.com/Search_Invoices_Report"
Personal tools