Acc Reports CTs With No Inv

From UG

Revision as of 06:11, 11 April 2010 (edit) Alex (Talk | contribs) (→Figures) ← Older edit		Current revision as of 17:06, 18 October 2011 (edit) (undo) Alex (Talk | contribs) (→CTs with a Loss)
(26 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:
-	=== 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 ==
	=== m1894 ===		=== m1894 ===
		+
		+	* 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 1 Info 2 Biz Req 3 Summary 4 Report Types 5 Hard Coded conditions 5.1 CTs with Unbilled Sales 5.2 CTs with a Loss 5.3 CTs without Purchase Invoices 5.4 CTs without Sales Invoices 6 Filters 7 Action Controls 8 Output 9 Excel format 10 Solution ver 1 10.1 CTs With No Invoices 10.2 CTs with a loss 11 Solution ver 2 11.1 Level 1 11.2 Level 2 11.3 Level 3 12 Ver 3 13 History 13.1 m1894 13.2 0002157: (Reps) (CTs With No Inv) Add "Unbilled Sales" report type. Rename some labels 13.3 0003192: (Acc Report: CTs With No Inv) (CTs with a Loss option) Create a dashboard version of this report

[edit] Info

• http://ct.jaguarfreight.com/mantis/view.php?id=2303

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):

• 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

[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.

• "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).

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:

• CT is billed - see Billed CT Status

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:

XLS output:

[edit] CTs with a loss

[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

• tweak: 0002157: (Reps) (CTs With No Inv) Add 2 report types: “CT with Unbilled Sales”, “CT with a Loss”
• spec updated? No

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