Acc Reports CTs With No Inv
From UG
(→Biz Req) |
(→CTs with a Loss) |
||
(25 intermediate revisions not shown) | |||
Line 1: | Line 1: | ||
- | |||
[[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 == | ||
- | + | This report helps to "catch" invoices that for some reson never being billed or never being mapped into any purchase amounts. | |
- | * CT is '''billed''' - see [[Billed CT Status]] OR | + | == 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 | * 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) | ||
- | == | + | == Action Controls == |
- | * | + | * '''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 == | ||
- | + | 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 38: | Line 93: | ||
Same as HTML (ZUL) | Same as HTML (ZUL) | ||
- | == | + | == Solution ver 1 == |
+ | === CTs With No Invoices === | ||
+ | HTML output: | ||
- | + | [[File:CTs With No Invoices HTML.JPG]] | |
- | [[File:CTs with | + | 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 |
[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):
- review all conditions to make sure they are up to date
[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