BPM query

postmanplod · Jan 22, 2009

Hello,

I need to show an informational warning box when the receiving stock via Job Receipt to Inventory. However, the warning should only appear when the total booked in i.e. sub total of Part Tran\Tran Qty by Job No is greater than the Job Head\Job Prod.

I have created a BPM rule based on the ReceiptsFromMfg.OnChangeTranQty method code. The pre-processing condition then states: number of rules in TEST query is more than 0.

Action: Show informational message.

The TEST query is based on the following syntax:

for last ttPartTran where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and ttPartTran.TranQty > jobhead.ProdQty no-lock

The above query works on a single level, however, I need the prompt to appear only when the sum of the ttPartTran.TranQty by the Job Number is greather than the Job Head.Job Prod - does anybody know the correct syntax?

Thanks,
Michael

postmanplod · Jan 23, 2009

I have tried the following to no avail:

for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead (TOTAL BY ttPartTran.JobNum) where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and ttPartTran.TranQty > jobhead.ProdQty no-lock

for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead (TOTAL BY JobHead.JobNum) where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and ttPartTran.TranQty > jobhead.ProdQty no-lock

for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead BREAK BY JobHed.job num where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and ttPartTran.TranQty > jobhead.ProdQty no-lock

for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead BREAK BY jobhead.JobNum where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and (Total by ttPartTran.TranQty) > jobhead.ProdQty no-lock

for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead BREAK BY jobhead.JobNum where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and ttPartTran.TranQty) > jobhead.ProdQty (Total) no-lock

for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead BREAK BY jobhead.JobNum where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and ttPartTran.TranQty) > jobhead.ProdQty (Total by jobhead.jobnJobNum) no-lock

for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead BREAK BY jobhead.JobNum where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and ttPartTran.TranQty (TOTAL BY ttPartTran.TranQty) > jobhead.ProdQty no-lock

for each ttPartTran BREAK BY ttPartTran.JobNum where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' and ttPartTran.TranType = 'MFG-STK' and ttPartTran.WareHouseCode = 'GHD') , each jobhead BREAK BY jobhead.JobNum where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum and (TOTAL BY ttPartTran.TranQty, ttPartTran.TranQty > jobhead.ProdQty) no-lock

for each ttPartTran no-lock , each JobHead BREAK BY JobHead.Jobnum where (ttPartTran.Company = JobHead.Company and ttPartTran.JobNum = JobHead.JobNum) and ttPartTran.TranQty > jobhead.ProdQty (TOTAL BY JobHead.JobNum) no-lock

for each ttPartTran no-lock , each JobHead BREAK BY JobHead.Jobnum where (ttPartTran.Company = JobHead.Company and ttPartTran.JobNum = JobHead.JobNum) and (TOTAL BY jobhead.jobnum) ttPartTran.TranQty > jobhead.ProdQty no-lock

postmanplod · Jan 28, 2009

I am going to attempt this via the 4GL action in BPM, based on the following code:

DEFINE VARIABLE Total Like ttPartTran.TranQty

for each ttPartTran where (ttPartTran.RowMod = 'A' or ttPartTran.RowMod = 'U' AND ttPartTran.Trantype = 'MFG-STK' AND ttPartTran.WareHouseCode = 'GHD') ,
each jobhead BREAK BY jobhead.JobNum no-lock where jobhead.Company = ttPartTran.Company and jobhead.JobNum = ttPartTran.JobNum:

SUB-TOTAL BY Total
IF Total > jobhead.ProdQty Then
MESSAGE "The received Qty is greater than the Total Job Qty"

Howevever, this is still not correct......does anybody have any suggestions?

postmanplod · Jan 28, 2009

Changed by code to the following but still no luck : -(

DEFINE VARIABLE Total Like ttPartTran.TranQty.

for each ttPartTran where (ttPartTran.RowMod = 'A'
or ttPartTran.RowMod = 'U'
AND ttPartTran.Trantype = 'MFG-STK'
AND ttPartTran.WareHouseCode = 'GHD') ,
each jobhead NO-LOCK
where jobhead.Company = ttPartTran.Company
and jobhead.JobNum = ttPartTran.JobNum
BREAK BY jobhead.JobNum:

ACCUMULATE ttPartTran.TranQty (SUB-TOTAL).

IF LAST-OF(jobhead.JobNum) THEN
ASSIGN Total = (ACCUM SUB-TOTAL ttPartTran.TranQty).

IF Total > jobhead.ProdQty Then
MESSAGE "The received Qty is greater than the Total Job Qty" .
END.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

BPM query

postmanplod

Programmer

postmanplod

Programmer

postmanplod

Programmer

postmanplod

Programmer

Similar threads

Part and Inventory Search

Sponsor