BPM with Outer Joins?

I originally had it set up as just a for each but epicor requires an open query to use outer-joins. i also need the outer joins because not every line item received is bought towards a job/has a project, jobnum, asmbl, etc. 


the original code is below and works great until a part is purchased to inventory instead of a job. 

for EACH ttRcvDtl No-Lock, 

each podetail no-lock where (podetail.Company = cur-comp and podetail.PONUM = ttrcvdtl.PONum and podetail.POLine = ttrcvdtl.POLine),

 each PORel no-lock  where (porel.company = cur-comp and porel.PONum = podetail.PONUM and porel.POLine = podetail.POLine),  

each Project no-lock where (Project.Company = cur-comp and Project.projectid = porel.ProjectID), each jobasmbl  no-lock where JobAsmbl.Company = cur-comp and jobasmbl.JobNum = ttrcvdtl.JobNum and jobasmbl.AssemblySeq = ttrcvdtl.AssemblySeq, 

each plantwhse no-lock where plantwhse.partnum = ttrcvdtl.partnum and plantwhse.Company = cur-comp, each poheader no-lock where poheader.ponum = ttrcvdtl.PONum and Poheader.Company = cur-comp,

each puragent no-lock where puragent.BuyerID = poheader.BuyerID.


Assign statements and all that good stuff. 


Anyone ever mess with a bpm to do bin assigning? i'm running into an issue where i need outer joins in my query but my bpm is acting up. i ended up modifying my code a little to include a define statement and an open query statement but something isn't quite right. Any ideas? 

Code is below. It's a little rough because i've tried a lot of different methods.

Thanks, 


DEFINE QUERY a FOR ttrcvdtl,podetail,porel,project,jobasmbl,plantwhse,poheader,puragent.


OPEN QUERY a

for EACH ttRcvDtl Exclusive-Lock, 

each podetail no-lock where (podetail.Company = cur-comp and podetail.PONUM = ttrcvdtl.PONum and podetail.POLine = ttrcvdtl.POLine),

 each PORel no-lock  where (porel.company = cur-comp and porel.PONum = podetail.PONUM and porel.POLine = podetail.POLine),  

each Project no-lock outer-join where (Project.Company = cur-comp and Project.projectid = porel.ProjectID), each jobasmbl  no-lock where JobAsmbl.Company = cur-comp and jobasmbl.JobNum = ttrcvdtl.JobNum and jobasmbl.AssemblySeq = ttrcvdtl.AssemblySeq, 

each plantwhse no-lock where plantwhse.partnum = ttrcvdtl.partnum and plantwhse.Company = cur-comp, each poheader no-lock where poheader.ponum = ttrcvdtl.PONum and Poheader.Company = cur-comp,each puragent no-lock where puragent.BuyerID = poheader.BuyerID.


GET FIRST a.


DO:


Assign ttRcvDtl.ShortChar01 = Project.ProjectID

ttRcvDtl.Shortchar02 = Project.Description. 


if plantwhse.PrimBin <> ""

then assign ttrcvdtl.BinNum = "Y". /* this allows us to bin assign parts that we are certain will always go to one location.*/

else if puragent.checkbox01 = True

then assign ttrcvdtl.BinNum = "SERVICE".

else if jobasmbl.checkbox05 = true 

then assign ttRcvdtl.BinNum = "E".

else if (ttrcvdtl.ShortChar03 = "Shop for Field (shiploose)" or ttrcvdtl.ShortChar03 = "Shop/Fld")

then assign ttrcvdtl.BinNum = "SL".

else if podetail.ClassID = "TOOL"

then assign ttrcvdtl.BinNum = "T".

else if podetail.ClassID = "SOFT"

then assign ttrcvdtl.BinNum = "IT".

else if podetail.ClassID = "CMPT"

then assign ttrcvdtl.binnum = "IT".

else if Project.BinNum = "" 

then assign ttRcvDtl.BinNum = "R".

else assign ttRcvDtl.BinNum = Project.BinNum.


pause.


GET NEXT a.


end.


You joins for plantwhse and poheader you may want to put the Company join first.

If your using a progress database this will help it locate the proper database index.

 

Patrick

 

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Tuesday, May 17, 2016 07:23
To: vantage@yahoogroups.com
Subject: [Vantage] BPM with Outer Joins?

 

 

Anyone ever mess with a bpm to do bin assigning? i'm running into an issue where i need outer joins in my query but my bpm is acting up. i ended up modifying my code a little to include a define statement and an open query statement but something isn't quite right. Any ideas? 

 

Code is below. It's a little rough because i've tried a lot of different methods.

 

Thanks, 

 

 

DEFINE QUERY a FOR ttrcvdtl,podetail,porel,project,jobasmbl,plantwhse,poheader,puragent.

 

OPEN QUERY a

for EACH ttRcvDtl Exclusive-Lock, 

each podetail no-lock where (podetail.Company = cur-comp and podetail.PONUM = ttrcvdtl.PONum and podetail.POLine = ttrcvdtl.POLine),

 each PORel no-lock  where (porel.company = cur-comp and porel.PONum = podetail.PONUM and porel.POLine = podetail.POLine),  

each Project no-lock outer-join where (Project.Co mpany = cur-comp and Project.projectid = porel.ProjectID), each jobasmbl  no-lock where JobAsmbl.Company = cur-comp and jobasmbl.JobNum = ttrcvdtl.JobNum and jobasmbl.AssemblySeq = ttrcvdtl.AssemblySeq, 

each plantwhse no-lock where plantwhse.partnum = ttrcvdtl.partnum and plantwhse.Company = cur-comp, each poheader no-lock where poheader.ponum = ttrcvdtl.PONum and Poheader.Company = cur-comp,each puragent no-lock where puragent.BuyerID = poheader.BuyerID.

 

GET FIRST a.

 

DO:

 

Assign ttRcvDtl.ShortChar01 = Project.ProjectID

ttRcvDtl.Shortchar02 = Project.Description. 

 

if plantwhse.PrimBin <> ""

            then assign ttrcvdtl.BinNum = "Y". /* this allows us to bin assign parts that we are certain will always go to one location.*/

else if puragent.checkbox01 = True

            then assign ttrcvdtl.BinNum = "SERVICE".

else if jobasmbl.checkbox05 = true 

            then assign ttRcvdtl.BinNum = "E".

else if (ttrcvdtl.ShortChar03 = "Shop for Field (shiploose)" or ttrcvdtl.ShortChar03 = "Shop/Fld")

then assign ttrcvdtl.BinNum = "SL".

else if podetail.ClassID = "TOOL"

            then assign ttrcvdtl.BinNum = "T".

else if podetail.ClassID = "SOFT"

            then assign ttrcvdtl.BinNum = "IT".

else if podetail.ClassID = "CMPT"

            then assign ttrcvdtl.binnum = "IT".

else if Project.BinNum = "" 

            then assign ttRcvDtl.BinNum = "R".

else assign ttRcvDtl.BinNum = Project.BinNum.

 

pause.

 

GET NEXT a.

 

end.

 

This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.
This e-mail and any attachments may contain confidential and privileged information. If you are not the intended and/or named recipient or recipients, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient or recipients is unauthorized and may be illegal. Any views or opinions expressed in this email are those of the author and do not necessarily represent those of the Specialty Screw Corporation. Warning: Although precautions have been taken to make sure no viruses are present in this email, Specialty Screw Corporation cannot accept responsibility for any loss or damage that may arise from the use of this email or attachments.   ­­  
 What results are you getting that aren't exactly right?

To reduce the complexity I would change over to a for each instead of a query.  No real advantage in this situation, just extra typing.

For the joins use the company field on the table instead of cur-comp.  Shouldn't change results but may improve response.  No need for exclusive-lock on a temporary table.

for EACH ttRcvDtl, 

each podetail no-lock where (podetail.Company = ttrcvdtl.Company and podetail.PONUM = ttrcvdtl.PONum and podetail.POLine = ttrcvdtl.POLine),...:



/* do stuff  */


end.

 

Also might start small and add one join at a time using message statements to show the results.  Message will put the results in the log file.



Jim Kinneman
Encompass Solutions, Inc