Acc Reports CTs With No Inv

From UG

(Difference between revisions)
Jump to: navigation, search
(m1894)
(CTs with a Loss)
 
(23 intermediate revisions not shown)
Line 1: Line 1:
-
[[Category:Acc]]
 
[[Category:AccReps]]
[[Category:AccReps]]
 +
 +
== Info ==
 +
 +
*http://ct.jaguarfreight.com/mantis/view.php?id=2303
 +
 +
See User Guide here: [[CTs With No Invoices]]
== Biz Req ==
== Biz Req ==
Line 6: Line 11:
This report helps to "catch" invoices that for some reson never being billed or never being mapped into any purchase amounts.
This report helps to "catch" invoices that for some reson never being billed or never being mapped into any purchase amounts.
-
== Hard Coded conditions ==  
+
== Summary ==  
-
CT should appear on this report if at least one condition is satisfied:
+
* '''CTs With No Invoices''' report is accounting report.
 +
* It is a list of shipments (CTs) which can be selected from DB by the [[#Hard Coded conditions]].
 +
* These sets of conditions correspond to their [[#Report Types]].
-
* CT is '''billed''' - see [[Billed CT Status]] OR
+
== Report Types ==
 +
* CTs with Unbilled Sales
 +
* CTs with a Loss
 +
* CTs without Purchase Invoices
 +
* CTs without Sales Invoices
 +
 
 +
== Hard Coded conditions ==
 +
 
 +
Todo (Tracie):
 +
 
 +
* review all conditions to make sure they are up to date
 +
** see http://ct.jaguarfreight.com/wiki/2157_rfd#.E2.80.9CCT_with_a_Loss.E2.80.9D and
 +
 
 +
=== CTs with Unbilled Sales ===
 +
CT should appear on this report if:
 +
* CT is '''NOT''' [[Billed CT Status |'''billed''']] – (If at least one Sales Invoice does not exist (No sales invoice was issued against CT))
 +
:AND
 +
* There is "PI" amount > 0 posted against CT
 +
 
 +
NOTES:
 +
:* If a CT record does not reflect a purchase invoice against it, it is not required to have a sales invoice against it. If no Purchase Invoice is issued against the CT record, the shipment should NOT show on the report.
 +
:* The system should not recognize internal invoices as a sales invoice for this report. If a CT records only has an internal invoice and no sales invoice the shipment SHOULD show on this report.
 +
 
 +
=== CTs with a Loss ===
 +
 
 +
CT should appear on this report if:
 +
 
 +
The total "purchase number" is equal to or greater then the total "sales number" for this CT.
 +
 
 +
* "purchase number" - purchase amount associated with this CT. In many cases it is a portion of purchase invoice proportional to weight or volume (see more here: [[Purchase_Invoices_CT2_component#Charge_Base]])
 +
 
 +
* "sales number" - sales amount associated with this CT
 +
** NOTE (!): Currently if CT is a part of a group then system will not properly allocate portion of this amount and report 0 amount (this will be fixed in the future).
 +
 
 +
<font color="red">We suspect that there is (or must be) another condition: "[[Revenue_Recognition_Date]] is in the past"</font>
 +
 
 +
=== CTs without Purchase Invoices ===
 +
CT should appear on this report if at least one condition is satisfied:
 +
* CT is '''billed''' - see [[Billed CT Status]]
 +
:OR
* 0 lines posted against CT in any PI in the system
* 0 lines posted against CT in any PI in the system
-
== Filters ==
+
=== CTs without Sales Invoices ===
-
* Invoice Type: PI / SI radio button
+
*No sales invoice was issued against CT
-
* CT Created on Date: from / to date calendar
+
 +
== Filters ==
 +
* '''Report Types''': group of radio buttons that correspond to the [[#Report Types |Report Types]]
 +
* '''CT Created on Date''': from / to date calendar
* NOTE: CT Created on Date: = GenTab.17. Notification Date:
* NOTE: CT Created on Date: = GenTab.17. Notification Date:
* NOTE: if both dates empty it means do not apply any date filter (no restriction on dates)
* NOTE: if both dates empty it means do not apply any date filter (no restriction on dates)
-
== Button ==
+
== Action Controls ==
-
* Go back > go back to the previous page
+
* '''Reset Form''' > clear all filter
-
* Reset Form > clear all filter
+
* '''Generate Result''' > HTML result(s) with CT#links and paging  
-
* Generate Result > HTML result(s) with CT#links and paging  
+
** '''Go back''' > go back to the previous page
-
** Go back > go back to the previous page
+
** '''Download to Excel''' > Excel report with CT# links
-
** Download to Excel > Excel report with CT# links
+
== Output ==
== Output ==
-
2 columns:
+
3 columns:
-
* CT (link)
+
* '''CT''' (link)
-
* Client
+
* '''Client Company'''
 +
* '''Jaguar Office'''
 +
** ''CTs with Unbilled Sales:'' The report should show the Jaguar Office that has entered to largest PI in an additional column on the report.
 +
** ''CTs with a Loss:'' The report should include the Jaguar Office reflecting the loss.
 +
*** If multiple offices show a loss or show purchase invoices without sales invoices – the office showing the largest loss should be the one included on the report.
 +
** ''CTs without Purchase/Sales Invoices'': Jaguar Office where was issued CT.
With paging.
With paging.
Line 41: Line 93:
Same as HTML (ZUL)
Same as HTML (ZUL)
-
== Figures ==
+
== Solution ver 1 ==
 +
=== CTs With No Invoices ===
 +
HTML output:
-
=== Figure 1 ===
+
[[File:CTs With No Invoices HTML.JPG]]
-
[[File:CTs with no inv.JPG]]
+
XLS output:
 +
 
 +
[[File:CTs With No Invoices XLS.JPG]]
 +
 
 +
=== CTs with a loss ===
 +
 
 +
[[File:CTs with a loss filters.JPG]]
 +
 
 +
[[File:CTs with a loss HTML.JPG]]
 +
 
 +
== Solution ver 2 ==
 +
 
 +
Create a dashboard version of this report (just for "CTs with a Loss option").
 +
 
 +
"CTs with a Loss option" should appear as option in Report scheduler.
 +
 
 +
Option to be DR or email.
 +
 
 +
Make "created from/to" required filter.
 +
 
 +
=== Level 1 ===
 +
 
 +
Counter: number of CTs that satisfy criteria (with hyperlink to next level)
 +
 
 +
=== Level 2 ===
 +
TBD in the next ver. None in this.
 +
 
 +
=== Level 3 ===
 +
HTML, with xls download option. Columns:
 +
 
 +
CT#
 +
Client company
 +
MaxPI
 +
USD profit/loss
 +
Date CT created
 +
Origin country
 +
Dest country
 +
MOT
 +
 
 +
== Ver 3 ==
 +
 
 +
Add Lev 2 as defined for all AR reports.
 +
 
 +
Option to "clear" CT from above list
== History ==
== History ==
Line 52: Line 149:
* Report conditions has been clarified. Verify that report satisfy [[#Hard Coded conditions]]. If not fix it.
* Report conditions has been clarified. Verify that report satisfy [[#Hard Coded conditions]]. If not fix it.
 +
* spec updated? Yes
 +
 +
=== 0002157: (Reps) (CTs With No Inv) Add "Unbilled Sales" report type. Rename some labels ===
 +
* tweak: [[0002157: (Reps) (CTs With No Inv) Add 2 report types: “CT with Unbilled Sales”, “CT with a Loss”]]
 +
* spec updated? No
 +
 +
=== 0003192: (Acc Report: CTs With No Inv) (CTs with a Loss option) Create a dashboard version of this report ===

Current revision as of 17:06, 18 October 2011


Contents

[edit] Info

See User Guide here: CTs With No Invoices

[edit] Biz Req

This report helps to "catch" invoices that for some reson never being billed or never being mapped into any purchase amounts.

[edit] Summary

  • CTs With No Invoices report is accounting report.
  • It is a list of shipments (CTs) which can be selected from DB by the #Hard Coded conditions.
  • These sets of conditions correspond to their #Report Types.

[edit] Report Types

  • CTs with Unbilled Sales
  • CTs with a Loss
  • CTs without Purchase Invoices
  • CTs without Sales Invoices

[edit] Hard Coded conditions

Todo (Tracie):

[edit] CTs with Unbilled Sales

CT should appear on this report if:

  • CT is NOT billed – (If at least one Sales Invoice does not exist (No sales invoice was issued against CT))
AND
  • There is "PI" amount > 0 posted against CT

NOTES:

  • If a CT record does not reflect a purchase invoice against it, it is not required to have a sales invoice against it. If no Purchase Invoice is issued against the CT record, the shipment should NOT show on the report.
  • The system should not recognize internal invoices as a sales invoice for this report. If a CT records only has an internal invoice and no sales invoice the shipment SHOULD show on this report.

[edit] CTs with a Loss

CT should appear on this report if:

The total "purchase number" is equal to or greater then the total "sales number" for this CT.
  • "sales number" - sales amount associated with this CT
    • NOTE (!): Currently if CT is a part of a group then system will not properly allocate portion of this amount and report 0 amount (this will be fixed in the future).

We suspect that there is (or must be) another condition: "Revenue_Recognition_Date is in the past"

[edit] CTs without Purchase Invoices

CT should appear on this report if at least one condition is satisfied:

OR
  • 0 lines posted against CT in any PI in the system

[edit] CTs without Sales Invoices

  • No sales invoice was issued against CT

[edit] Filters

  • Report Types: group of radio buttons that correspond to the Report Types
  • CT Created on Date: from / to date calendar
  • NOTE: CT Created on Date: = GenTab.17. Notification Date:
  • NOTE: if both dates empty it means do not apply any date filter (no restriction on dates)

[edit] Action Controls

  • Reset Form > clear all filter
  • Generate Result > HTML result(s) with CT#links and paging
    • Go back > go back to the previous page
    • Download to Excel > Excel report with CT# links

[edit] Output

3 columns:

  • CT (link)
  • Client Company
  • Jaguar Office
    • CTs with Unbilled Sales: The report should show the Jaguar Office that has entered to largest PI in an additional column on the report.
    • CTs with a Loss: The report should include the Jaguar Office reflecting the loss.
      • If multiple offices show a loss or show purchase invoices without sales invoices – the office showing the largest loss should be the one included on the report.
    • CTs without Purchase/Sales Invoices: Jaguar Office where was issued CT.

With paging.

With download to xls.

[edit] Excel format

Same as HTML (ZUL)

[edit] Solution ver 1

[edit] CTs With No Invoices

HTML output:

File:CTs With No Invoices HTML.JPG

XLS output:

File:CTs With No Invoices XLS.JPG

[edit] CTs with a loss

File:CTs with a loss filters.JPG

File:CTs with a loss HTML.JPG

[edit] Solution ver 2

Create a dashboard version of this report (just for "CTs with a Loss option").

"CTs with a Loss option" should appear as option in Report scheduler.

Option to be DR or email.

Make "created from/to" required filter.

[edit] Level 1

Counter: number of CTs that satisfy criteria (with hyperlink to next level)

[edit] Level 2

TBD in the next ver. None in this.

[edit] Level 3

HTML, with xls download option. Columns:

CT#
Client company
MaxPI
USD profit/loss
Date CT created
Origin country
Dest country
MOT

[edit] Ver 3

Add Lev 2 as defined for all AR reports.

Option to "clear" CT from above list

[edit] History

[edit] m1894

  • Report conditions has been clarified. Verify that report satisfy #Hard Coded conditions. If not fix it.
  • spec updated? Yes

[edit] 0002157: (Reps) (CTs With No Inv) Add "Unbilled Sales" report type. Rename some labels

[edit] 0003192: (Acc Report: CTs With No Inv) (CTs with a Loss option) Create a dashboard version of this report

Personal tools