[freeside-users] Query of invoice line items for service broadband

[Gilbert Rebeiro]

Hi Jeff,

Thanks for the query.
This only gives me the one-time charges not the recurring package that 
is billed on a monthly basis.
Any idea how to get the line items from the invoice that are generated 
by the packages that are billed on a recurring basis?

Thanks,
Gilbert.


On 09/06/2012 4:29 PM, Jeff Finucane wrote:
> On Sat, Jun 09, 2012 at 12:26:17PM -0400, Gilbert Rebeiro<gilbert at dido.ca>  wrote:
> +----------
> | I can see the string on a sample invoice but I don't know the proper
> | query to get the historical line items.
> |
> | as an example
> | this is custnum 305
> | invoice num 16559
> | The line item shows
> | 2675     Cable Hi-Speed  (2009/02/01 - 2009/03/01)    $19.12
> |     - Videotron Cable: SN: 101740526 - VLJMBYYY - customer name
> |     - customer address
> |
> | So I need a query that shows all invoice numbers and invoice dates
> | that were issued with VLJMBYYY in the description.
> +----------
>
> Try
>
> SELECT DISTINCT cust_bill.invnum,cust_bill._date FROM cust_bill JOIN cust_bill_pkg USING(invnum) JOIN cust_bill_pkg_detail USING(billpkgnum) WHERE detail LIKE '%VLJMBYYY%';
>