Are you looking qry_invoice ?

edited January 2015 in Missing Table Or Query
Run this query on MySQL :
-------------------------

create view qry_invoice_retur as
select `invoice`.`your_order__` AS `your_order__`,sum(`invoice`.`amount`) AS `retur`
from `invoice` where (`invoice`.`invoice_type` = _utf8'R')
group by `invoice`.`invoice_number`;

create view qry_invoice_cedit as
select `crdb_memo`.`docnumber` AS `docnumber`,sum(`crdb_memo`.`amount`) AS `cr_amount`
from `crdb_memo`
where (`crdb_memo`.`transtype` = _utf8'SO-CREDIT MEMO')
group by `crdb_memo`.`docnumber`;

create view qry_invoice_debit as
select `crdb_memo`.`docnumber` AS `docnumber`,sum(`crdb_memo`.`amount`) AS `db_amount`
from `crdb_memo`
where (`crdb_memo`.`transtype` = _utf8'SO-DEBIT MEMO')
group by `crdb_memo`.`docnumber`;

create view qry_invoice_payment as
select `payments`.`invoice_number` AS `invoice_number`,sum(`payments`.`amount_paid`) AS `payment` from `payments` group by `payments`.`invoice_number`;

create view qry_invoice as
select `i`.`invoice_date` AS `invoice_date`,`i`.`invoice_number` AS `invoice_number`,`i`.`sold_to_customer` AS `sold_to_customer`,`c`.`company` AS `company`,
`i`.`due_date` AS `due_date`,`i`.`payment_terms` AS `payment_terms`,`i`.`salesman` AS `salesman`,`i`.`amount` AS `amount`,`i`.`sales_order_number` AS `sales_order_number`,
`p`.`payment` AS `payment`,`r`.`retur` AS `retur`,`cr`.`cr_amount` AS `cr_amount`,
`d`.`db_amount` AS `db_amount`
from (((((`invoice` `i`
left join `customers` `c` on((`c`.`customer_number` = `i`.`sold_to_customer`))) left join `qry_invoice_payment` `p` on((`p`.`invoice_number` = `i`.`invoice_number`)))
left join `qry_invoice_retur` `r` on((`r`.`your_order__` = `i`.`invoice_number`)))
left join `qry_invoice_credit` `cr` on((`cr`.`docnumber` = `i`.`invoice_number`)))
left join `qry_invoice_debit` `d` on((`d`.`docnumber` = `i`.`invoice_number`)))
where (`i`.`invoice_type` = _utf8'I');



Sign In or Register to comment.