Kassenbuch_full@TENANT_NO@COMPANY_NO@DATE@TODATE
select * from currencies c join departments d on c.tenant_no = d.tenant_no AND c.currency_cd = d.currency_cd where d.tenant_no=@TENANT_NO and d.company_no=@COMPANY_NO and d.department_no =@DEPARTMENT_NO
select * from pos_drawers where tenant_no=@TENANT_NO and pos_cd=@POS_CD
select * from businessunits where tenant_no=@TENANT_NO and company_no=@COMPANY_NO and businessunit_no=@DEPARTMENT_NO and businessunit_no=@BUSINESSUNIT_NO
select
cast (sales_inv_ts as DATE) sales_inv_dt,
coalesce(i.item_revenue_acct_no,0) item_revenue_acct_no,
coalesce(sum(case when h.sales_inv_type=1 then p.position_gross_price else case when h.sales_inv_type=2 then -p.position_gross_price else 0 end end),0) r_revenue_acct_value,
coalesce(sum(case when h.sales_inv_type=1 then p.position_net_price else case when h.sales_inv_type=2 then -p.position_net_price else 0 end end),0) r_net_revenue_acct_value
from sales_inv_positions p
join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd = p.pos_cd and h.sales_inv_id = p.sales_inv_id
join items i
on i.item_cd = p.item_cd
AND i.tenant_no = h.tenant_no
AND i.company_no = h.company_no
AND i.department_no = h.department_no
where h.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and pos_cd=@POS_CD and cast (sales_inv_ts as DATE) >= cast (@DATE as DATE) and cast (sales_inv_ts as DATE) <= cast (@TODATE as DATE))
AND p.tenant_no=@TENANT_NO and p.pos_cd=@POS_CD
GROUP BY cast (sales_inv_ts as DATE), coalesce(i.item_revenue_acct_no,0)
select * from item_revenue_accounts where tenant_no=@TENANT_NO and company_no=@COMPANY_NO AND department_no=@DEPARTMENT_NO
select * from simple_accounts where tenant_no=@TENANT_NO and company_no=@COMPANY_NO AND department_no=@DEPARTMENT_NO
select
cast (pp.payment_ts as DATE) payment_dt,
coalesce(pip.simple_acct_cd,'') simple_acct_cd,
coalesce(sum(pip.position_gross_price),0) position_gross_price,
coalesce(sum(pip.position_net_price),0) position_net_price
from purchase_inv_positions pip
join purchase_invs h
on h.tenant_no = pip.tenant_no and h.pos_cd = pip.pos_cd and h.purchase_inv_id = pip.purchase_inv_id
join pos_payments pp
on h.purchase_inv_id = coalesce(pp.purchase_inv_id,-111)
and pp.tenant_no=h.tenant_no
and pp.pos_cd=h.pos_cd
and cast (pp.payment_ts as DATE) >= cast (@DATE as DATE) and cast (pp.payment_ts as DATE) <= cast (@TODATE as DATE)
WHERE h.tenant_no=@TENANT_NO
AND h.pos_cd=@POS_CD
GROUP BY cast (pp.payment_ts as DATE), coalesce(pip.simple_acct_cd,'')
select
drawer_no drawer_no,
coalesce(pp.contra_acct_cd,'-') contra_acct_cd,
pp.payment_direction payment_direction,
coalesce(sum(pp.payment),0) payment
from pos_payments pp
WHERE pp.tenant_no=@TENANT_NO
AND pp.pos_cd=@POS_CD
AND pp.sales_inv_id IS NULL
AND pp.purchase_inv_id IS NULL
AND cast (pp.payment_ts as DATE) >= cast (@DATE as DATE) and cast (pp.payment_ts as DATE) <= cast (@TODATE as DATE)
GROUP BY drawer_no, coalesce(pp.contra_acct_cd,'-'), pp.payment_direction
select
cast (pp.payment_ts as DATE) payment_dt,
coalesce(pp.contra_acct_cd,'-') contra_acct_cd,
pp.payment_direction payment_direction,
coalesce(sum(pp.payment),0) payment
from pos_payments pp
WHERE pp.tenant_no=@TENANT_NO
AND pp.pos_cd=@POS_CD
AND pp.sales_inv_id IS NULL
AND pp.purchase_inv_id IS NULL
AND cast (pp.payment_ts as DATE) >= cast (@DATE as DATE) and cast (pp.payment_ts as DATE) <= cast (@TODATE as DATE)
GROUP BY cast (pp.payment_ts as DATE), coalesce(pp.contra_acct_cd,'-'), pp.payment_direction