WikiAdmin: 1 revision imported

2023-07-14T12:58:48Z

1 revision imported

← Older revision	Revision as of 12:58, 14 July 2023
(No difference)

Intra>User: 1 revision imported

2017-01-10T00:01:30Z

1 revision imported

New page

==A/P: Expense Breakdown by Vendor==
Query:
<PRE>
select vend_class "Class",v.name "Vendor Name",vend_id "Vend ID",invoice_date "Invoice Date",invoice_no "Invoice No.",paid_amount "Amount Paid",disc_taken "Discount Taken",description "Description",C.Description "Class Name",invoice_date "Year",invoice_date "Month",invoice_date "Quarter", exp_gl "Distribution G/L",dist_amt "Amount",a.description "Account Name",
a.segment_1 "Acct",a.segment_2 "Plant",vend_id||': '||v.name "Vendor",void_flag "Void Flag",source_flag "Source"
from aptinvhd i
inner join aptvend v on v.vend_id=i.vend_id
inner join aptinvdt d on d.session_no=i.session_no and d.trans_no=i.trans_no
inner join aptvecls c on c.class_no=v.vend_class
left outer join gltacct a on a.gl_account=d.exp_gl
where invoice_date between :"Begin Date" and :"End Date"
</PRE>

[[Image:Ds ap exp by ven.png]]
Data Items:
*Amount

Columns:
*Year (Grouping: Year)
*Month (Grouping: Month)

Rows:
*Vendor
*Distribution G/L
*Description

==Accounts Payable Open Invoice by Year/Month==
<PRE>
select v.vend_class class,v.vend_id,v.name, i.invoice_no,invoice_date,due_date,
due_date Month_Due,due_date Year_Due,gross_amount "Inv AMT",(paid_amount+disc_taken) paid,gross_amount-(paid_amount+disc_taken) balance,to_pay_amount,
description
from aptinvhd i
inner join aptvend v on v.vend_id = i.vend_id
where gross_amount-paid_amount-disc_taken<>0
order by vend_class,i.due_date</PRE>

Setup:
*DATA:
:Balance
*COLUMN:
::Year_Due (Group by Year)
::Month_Due (Group by Month)
*ROW:
*Vend_ID
*Name

[[Image:Ds ap open yr mo.png]]

==Accounts Receivable Open Invoice by Days Out==
<PRE>
select i.*,c.name, i.invoice_date mo, i.invoice_date YR,
DEBIT_AMOUNT-CREDIT_AMOUNT BALANCE,c.active_flag,
cast ('today' as date)-Invoice_date DaysOut
from exv_aRinvoices i
inner join artcust c on c.cust_no=i.cust_no
WHERE DEBIT_AMOUNT-CREDIT_AMOUNT<>0
</PRE>

[[Image:Ds ar open days out.png]]

==Sales Query with Drill Down==

'''Main Query:'''
<PRE>
SELECT h.cust_no "Cust No", c.name "Name",h.job_po_loc "Job",
p.product_class||': '||pc.description "Product Class",
d.product_code "Product Code",p.description_1 "Product Name",p.base_product "Product Base",
h.cust_class "Customer Class",
sum(d.qty_sold) "Quantity", sum(d.ext_price) "Sales", sum(d.ext_cost) "Cost", sum(d.ext_disc) "Disc Offrd",
sum(d.ext_price) "Avg Price", sum(d.ext_price) "Change",
h.invoice_date "Month", h.invoice_date "Quarter",Invoice_Date "Year" ,
h.plant_no "Plant", h.tax_authority "Tax Auth",
h.salesperson "Sales Person",d.unit_of_measure "UM"
FROM artslshd h
INNER JOIN artslsdt d ON (h.session_no = d.session_no) and (h.trans_no = d.trans_no)
INNER JOIN artcust c on (c.cust_no = h.cust_no)
inner join artprod p on p.product_code=d.product_code
inner join artprcls pc on (pc.class_no=p.product_class)
WHERE (d.comment_flag = 'N')
and h.invoice_date between :beg_date and :end_date
group by
h.cust_no, c.name,h.job_po_loc,
p.product_class,pc.description,
d.product_code,p.description_1,
h.cust_class,
h.invoice_date,h.plant_no , h.tax_authority, h.salesperson ,d.unit_of_measure,p.base_product
</PRE>

'''Drilldown Query:'''
<PRE>
SELECT h.cust_no "Cust No", c.name "Name",h.job_po_loc "Job",
p.product_class||': '||pc.description "Product Class",
d.product_code "Product Code",p.description_1 "Product Name",p.base_product "Product Base",
h.cust_class "Customer Class",
d.qty_sold "Quantity", d.ext_price "Sales", d.ext_cost "Cost", d.ext_disc "Disc Offrd",
h.invoice_date "Month",
h.plant_no "Plant", h.tax_authority "Tax Auth",
h.salesperson "Sales Person",d.unit_of_measure "UM"
FROM artslshd h
INNER JOIN artslsdt d ON (h.session_no = d.session_no) and (h.trans_no = d.trans_no)
INNER JOIN artcust c on (c.cust_no = h.cust_no)
inner join artprod p on p.product_code=d.product_code
inner join artprcls pc on (pc.class_no=p.product_class)
WHERE
h.invoice_date between :beg_date and :end_date and
[WHERE Cust No=h.cust_no]
[WHERE Job=h.job_po_loc]
[WHERE Product Class=p.product_class||': '||pc.description]
[WHERE Product Code=p.product_code]
[WHERE Customer Class=h.cust_class]
[WHERE Month=extract(month from h.invoice_date)]
[WHERE Quarter=(2+extract(month from h.invoice_date))/3]
[WHERE Year=extract(year from h.invoice_date)]
[WHERE Plant=h.plant_no]
[WHERE Tax Auth=h.tax_authority]
[WHERE Sales Person=h.salesperson]
[WHERE UM=d.unit_of_measure]
[WHERE Product Base=p.base_product]
and (d.comment_flag = 'N')
</PRE>

This query is very flexible. It can be stripped down for efficiency and simplicity.

Recomended Data Items:
*Sales
*Qty
*Avg Price (Summary Type: Weighted Average/Qty)

Recomended Columns:
*Year (Grouping: Year)
*Quarter (Grouping: Year)
*Month (Grouping: Month)

'''See Also: [[DataScope G/L Finanacial Statements]]'''

DataScope Examples - Revision history

WikiAdmin: 1 revision imported

Intra>User: 1 revision imported