Acc Reports CTs With No Inv

From UG

(Difference between revisions)
Jump to: navigation, search
(→Excel format)
(→CTs with a Loss)
 
(27 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 ==
-
We need to find all CTs with 0 invoices created (sales inv) or 0 lines posted against CT in any PI in the system.
 
-
== Filters ==
+
This report helps to "catch" invoices that for some reson never being billed or never being mapped into any purchase amounts.
-
* Invoice Type: PI / SI radio button
+
-
* CT Created on Date: from / to date calendar
+
 +
== 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]].
 +
 +
== 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
 +
 +
=== CTs without Sales Invoices ===
 +
*No sales invoice was issued against CT
 +
 +
== 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 34: Line 93:
Same as HTML (ZUL)
Same as HTML (ZUL)
-
== Figures ==
+
== Solution ver 1 ==
 +
=== CTs With No Invoices ===
 +
HTML output:
 +
 
 +
[[File:CTs With No Invoices HTML.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 ==
 +
 
 +
=== m1894 ===
 +
 
 +
* Report conditions has been clarified. Verify that report satisfy [[#Hard Coded conditions]]. If not fix it.
 +
* spec updated? Yes
-
=== Figure 1 ===
+
=== 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
-
[[File:CTs with no inv.JPG]]
+
=== 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

Retrieved from "https://wiki.jaguarfreight.com/Acc_Reports_CTs_With_No_Inv"
Personal tools