DataScope Examples

A/P: Expense Breakdown by Vendor

Query:

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"

Data Items:

• Amount

Columns:

• Year (Grouping: Year)
• Month (Grouping: Month)

Rows:

• Vendor
• Distribution G/L
• Description

Accounts Payable Open Invoice by Year/Month

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

Setup:

• DATA:

Balance

• COLUMN:

Year_Due (Group by Year)

Month_Due (Group by Month)

• ROW:
• Vend_ID
• Name

Accounts Receivable Open Invoice by Days Out

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

Sales Query with Drill Down

Main Query:

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

Drilldown Query:

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')   

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