×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Order Entry Question

Order Entry Question

Order Entry Question

(OP)
I am new to Macola and have worked with SQL before. I have a task to provide my boss the number of orders input into Macola yesterday. I have it running in our Report Portal and providing the data daily. The issue is I need to add to this report the user that input the order into Macola and I have come to realize that requires another table. My challenge is the join and the join for both parts of the query. I am hoping I can get a little help here. Those of you who review my query and say.. wow.. thats not right.. you are most likely correct.. but it does work... so.. any ideas how to add the user name from the oehdraud_sql table in both instances of the query.
Thanks for the help..


Select sum(OrdCount), DATENAME(WEEKDAY,MacolaDate) AS DAYWEEK, Cast(MacolaDate as Date) Mdate From (

SELECT oh.entered_dt as MacolaDate, count(distinct oh.ord_no) as ordCount
FROM OEORDHDR_SQL oh with (nolock)
INNER JOIN OEORDLIN_SQL ol with (nolock) ON oh.ord_type = ol.ord_type AND oh.ord_no = ol.ord_no
WHERE oh.entered_dt >= @DtBegin and oh.entered_dt <= @DtEnd
and oh.ord_type = 'O'
AND ol.loc = 'MOD'
group by oh.entered_dt

UNION
--Posted invoices
SELECT oh.entered_dt as MacolaDate, count(distinct oh.ord_no) as ordCount
FROM OEHDRHST_SQL oh with (nolock)
INNER JOIN OELINHST_SQL ol with (nolock) ON oh.ord_type = ol.ord_type
WHERE oh.entered_dt >= @DtBegin and oh.entered_dt <= @DtEnd
and oh.ord_type = 'O'
AND ol.loc = 'MOD'
group by oh.entered_dt) as DT
Group by MacolaDate
order by MacolaDate

RE: Order Entry Question

You need to look at the OEHDRAUD_SQL and OELINAUD_SQL tables. These contain the user name. No need for a UNION query. There are 4 aud_action values: A-Add, B-Before Change, C-After Change and D-Delete. So using aud_dt rather than entered_dt, you can get the total net change to orders by adding the aud_action As and Cs and subtracting the aud_action Bs and Ds. This gets you an effective booking report - which Macola does not have - regardless if the items are invoiced or not.

I hope this helps.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

www.gainfocus.biz/exceladdin.html

RE: Order Entry Question

(OP)
dgillz thanks as you know you were spot on. I appreciate your help. I thought this tread would notify me when I got a post. I guess I have some adjustments to make.
Thanks,

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close