I have a customized maintain session that stores Supplier QMS metadata for various industries (Medical, Aerospace, Automotive, General). Business rules are applied against the data in this session and some data in another session which tracks Supplier Corrective Action Responses (SCARs) to determine a supplier status (Approved, Not Approved, Conditionally Approved, Executive Override). The main table that my session uses is tiobg999. The status fields belong to the main table (tiobg999.scsa, tiobg999.scsb, tiobg999.scsc, tiobg999.scsd), but are able to be modified by the user. Their value is determined by the application of the business rules against the data (coding within my session). My problem…. If I change data in my session or in the SCAR session which would then (through the application of the business rules) change the status of the supplier, the new status value displays on form, but does not change in the database. I’m not sure why this is. Below is the function which applies the business rules and returns the appropriate status. Any assistance would be greatly appreciated!

tiobg999.scsa = determine.medical.supplier.control.status()

|******************************************************************************
| This function will apply business rules to determine the status of a
| medical supplier.
|******************************************************************************
function domain tisupstat determine.medical.supplier.control.status()
{
domain tisupstat f.scst

f.scst = tisupstat.notapprove

|******************************************************************************
| Application of Business Rules to determine the approval status for Medical Suppliers

| Determine if Medical Industry is selected
if tiobg999.inda = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if there is a non-expired executive override
if tiobg999.exoa = tgyenox.yes and tiobg999.exda >= ws.today then
f.scst = tisupstat.override
return(f.scst)
endif

| Determine if the Supplier Add/Change/Delete form is completed and on-file
if tiobg999.sarc = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if there is a non-expired ISO 9001 certificate
if tiobg999.isoa = tgyenox.no or tiobg999.expa < ws.today then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if any commodities have been assigned to determine if supplier is Critical or Major
if ws.comc = "None" then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if the supplier provides commidities which are classified as Critical.
if ws.sccl = ticomclass.critical then
| Critical commodities require Medical Validation.
if tiobg999.mval = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
else
| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
| If there are accepted SCARs, then the supplier is conditionally approved.
f.scst = tisupstat.conditional
return(f.scst)
endif
endif
else
| Determine if the supplier provides commidities which are classified as Major.
if ws.sccl = ticomclass.major then
| See if any commodities are Testing types
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.test = tgyenox.yes
as set with 1 rows
selectdo
| Major Suppliers that we use for Testing must have
| (non-expired) A2LA Certification.
if tiobg999.atla = tgyenox.no or
(tiobg999.atla = tgyenox.yes and tiobg999.adla < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
f.scst = tisupstat.notapprove
endselect

| See if any commodities are Calibration types
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.catt = tgyenox.yes
as set with 1 rows
selectdo
| Major Suppliers that we use for Calibration must have
| (non-expired) ISO 17025 Certification.
if tiobg999.isoe = tgyenox.no or
(tiobg999.isoe = tgyenox.yes and tiobg999.expe < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
f.scst = tisupstat.notapprove
endselect
endif

| Major Medical suppliers must have either ISO 13485 or a Site Audit
if (tiobg999.isob = tgyenox.yes and tiobg999.expb >= ws.today) or
(tiobg999.moss = tgyenox.yes and tiobg999.mssd >= ws.today) then
f.scst = tisupstat.approve
else
| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
| If there are accepted SCARs, then the supplier is conditionally approved.
f.scst = tisupstat.conditional
return(f.scst)
endif
endif
endif

return(f.scst)
}

Assuming that tiobg999 is the maintable of the session, then call
tiobg999.scsa = determine.medical.supplier.control.status()
should also be placed in the before.rewrite section to update the table.

Alternatively, use update.occ() after the call.

If it is not the maintable, you have program the update completely.

Thank you!

That is working, but only when I'm saving a record. I originally was attempting to perform that function call under:

field.all:
when.field.changes:
function call



Not sure why that isn't working. I've tried so many different things, I may need to scale back and start looking at it fresh.

The 4gl engine will set a flag that the database needs to do a save whenever you have modified a field on the form. When you don't modify a field, but change a table value in the code, you need to call update.occ. to set the flag manually.

What this means is that you should be able to put your function call in a when.field.changes: subsection and it will work if you change a field value to something new.

Do you want to update the value any other time?

First... thanks to shah_bs and toolswizard for your assistance.


Still having some issue with getting the data to save when the field changes. It is odd because it appears that the tiobg999.scsa (and scsb, scsc, scsd) values in the session's buffer changes to the appropriate approval code because on the session itself, the values change and I can see it as a click different selections, but it doesn't get changed in the database until I force a save or save-exit.


Here is the entire code:

|******************************************************************************
|* tiosd9199 00100 0 VRC B40C c4 tst4
|* Maintain Supplier Data
|* erice
|* 03.31.2011
|******************************************************************************
|* Script Type: 4
|******************************************************************************


|****************************** DECLARATION SECTION ***************************
declaration:

| Tables:

table ttcccm300 | SCAR Table
table ttccom020 | Supplier Master
table ttiobg997 | Commodity Code by Supplier
table ttiobg998 | Commodity Codes
table ttiobg999 | QMS Supplier Data

| Selection Criteria:

extern domain tcsuno ws.suno | Supplier Number

| Working Storage Variables:

extern domain tisupstat return.status
extern domain tcmcs.long return.value
extern domain tcmcs.long user.executive.override
extern domain tcmcs.str10 ws.comc | Commodity Status
extern domain tcmcs.long ws.ecof | Executive Conditional Override Flag (0 = No Authorization,
| 1 = Authorization)
extern domain ticomclass ws.sccl | Supplier Status
extern domain tcmcs.long ws.stus.open | Count of Open SCARs
extern domain tcmcs.long ws.stus.closed | Count of Closed SCARs
extern domain tcmcs.long ws.stus.disputed | Count of Disputed SCARs
extern domain tcmcs.long ws.supplier.status
extern domain tcdate ws.today

| Libraries Used

#pragma used dll otiobgd10000001 | Check Special User Permissions
#pragma used dll otiobgd10000003 | Determine Commodity Status

|****************************** PROGRAM SECTION ***************************
before.program:
user.executive.override = check.special.user.permissions(prog.name$, logname$)

import("tccom020.suno", ws.suno)
query.extension = "tiobg999.suno = '" & ws.suno & "'"

ws.today = date.num()

| Retrieve value for ws.sccl
return.value = check.supplier.commodity.status(tiobg999.suno)

|****************************** TABLE I/O SECTION ***************************
main.table.io:
before.rewrite:
update.supplier.status()

after.delete:
check.key.supplier()

| Removes any commodities which are assigned to the deleted supplier.
select tiobg997.*
from tiobg997
for update
where tiobg997._index1 = {:tiobg999.suno}
selectdo
db.delete(ttiobg997, db.retry)
commit.transaction()
endselect

|****************************** FORM SECTION ***************************

form.all:
init.form:
get.screen.defaults()
update.supplier.commodity.status()
refresh.scar.data()

|****************************** CHOICE SECTION ***************************
choice.user.0: | Maintain Commodities
on.choice:
export("ws.suno", ws.suno)
zoom.to$("tiosd9198m000",Z.Session,"","",0)
update.supplier.status()

|****************************** FIELD SECTION ***************************
field.all:
when.field.changes:
update.supplier.status()

field.tiobg999.acqi:
when.field.changes:
if tiobg999.acqi = tgyenox.yes then
tiobg999.cqid = date.num() + 365
else
tiobg999.cqid = 0
endif
display("tiobg999.cqid")

field.tiobg999.aess:
when.field.changes:
if tiobg999.aess = tgyenox.no then
tiobg999.esss = 0
display("tiobg999.esss")
endif

field.tiobg999.aoss:
when.field.changes:
if tiobg999.aoss = tgyenox.no then
tiobg999.assd = 0
display("tiobg999.assd")
endif

field.tiobg999.atla:
when.field.changes:
if tiobg999.atla = tgyenox.yes then
tiobg999.adla = date.num()
else
tiobg999.adla = 0
endif
display("tiobg999.adla")

field.tiobg999.auss:
when.field.changes:
if tiobg999.auss = tgyenox.no then
tiobg999.usss = 0
display("tiobg999.usss")
endif

field.tiobg999.exda:
before.input:
if user.executive.override = 0 then
message("You have no authorization for Executive Override.")
attr.input = 0
else
attr.input = 1
endif

field.tiobg999.exdb:
before.input:
if user.executive.override = 0 then
message("You have no authorization for Executive Override.")
attr.input = 0
else
attr.input = 1
endif

field.tiobg999.exdc:
before.input:
if user.executive.override = 0 then
message("You have no authorization for Executive Override.")
attr.input = 0
else
attr.input = 1
endif

field.tiobg999.exdd:
before.input:
if user.executive.override = 0 then
message("You have no authorization for Executive Override.")
attr.input = 0
else
attr.input = 1
endif

field.tiobg999.exoa:
before.input:
if user.executive.override = 0 then
message("You have no authorization for Executive Override.")
attr.input = 0
else
attr.input = 1
endif

when.field.changes:
if tiobg999.exoa = tgyenox.no then
tiobg999.exra = 0
tiobg999.exda = 0
display.all()
else
tiobg999.exda = date.num() + 90
endif

field.tiobg999.exob:
before.input:
if user.executive.override = 0 then
message("You have no authorization for Executive Override.")
attr.input = 0
else
attr.input = 1
endif

when.field.changes:
if tiobg999.exob = tgyenox.no then
tiobg999.exrb = 0
tiobg999.exdb = 0
display.all()
else
tiobg999.exdb = date.num() + 90
endif

field.tiobg999.exoc:
before.input:
if user.executive.override = 0 then
message("You have no authorization for Executive Override.")
attr.input = 0
else
attr.input = 1
endif

when.field.changes:
if tiobg999.exoc = tgyenox.no then
tiobg999.exrc = 0
tiobg999.exdc = 0
display.all()
else
tiobg999.exdc = date.num() + 90
endif

field.tiobg999.exod:
before.input:
if user.executive.override = 0 then
message("You have no authorization for Executive Override.")
attr.input = 0
else
attr.input = 1
endif

when.field.changes:
if tiobg999.exod = tgyenox.no then
tiobg999.exrd = 0
tiobg999.exdd = 0
display.all()
else
tiobg999.exdd = date.num() + 90
endif

field.tiobg999.exra:
before.input:
if user.executive.override = 0 then
attr.input = 0
message("You have no authorization for Executive Override.")
else
attr.input = 1
endif

after.input:
if tiobg999.exra = 0 then
message("You must specify a reason code for an executive override.")
to.field(tiobg999.exoa)
endif

field.tiobg999.exrb:
before.input:
if user.executive.override = 0 then
attr.input = 0
message("You have no authorization for Executive Override.")
else
attr.input = 1
endif

after.input:
if tiobg999.exrb = 0 then
message("You must specify a reason code for an executive override.")
to.field(tiobg999.exob)
endif

field.tiobg999.exrc:
before.input:
if user.executive.override = 0 then
attr.input = 0
message("You have no authorization for Executive Override.")
else
attr.input = 1
endif

after.input:
if tiobg999.exrc = 0 then
message("You must specify a reason code for an executive override.")
to.field(tiobg999.exoc)
endif

field.tiobg999.exrd:
before.input:
if user.executive.override = 0 then
attr.input = 0
message("You have no authorization for Executive Override.")
else
attr.input = 1
endif

after.input:
if tiobg999.exrd = 0 then
message("You must specify a reason code for an executive override.")
to.field(tiobg999.exod)
endif

field.tiobg999.inda:
after.input:
if tiobg999.inda = tgyenox.no then
tiobg999.isob = tgyenox.no
tiobg999.expb = 0
tiobg999.mval = tgyenox.no
tiobg999.moss = tgyenox.no
tiobg999.mssd = 0
tiobg999.osss = 0
tiobg999.exoa = tgyenox.no
tiobg999.exra = 0
tiobg999.exda = 0
display.all()
endif

field.tiobg999.indb:
after.input:
if tiobg999.indc = tgyenox.no then
tiobg999.isoc = tgyenox.no
tiobg999.expc = 0
tiobg999.nadc = tgyenox.no
tiobg999.ncpd = 0
tiobg999.aoss = tgyenox.no
tiobg999.assd = 0
tiobg999.aess = tgyenox.no
tiobg999.esss = 0
tiobg999.exob = tgyenox.no
tiobg999.exrb = 0
tiobg999.exdb = 0
display.all()
endif

field.tiobg999.indc:
after.input:
if tiobg999.indc = tgyenox.no then
tiobg999.isod = tgyenox.no
tiobg999.expd = 0
tiobg999.acqi = tgyenox.no
tiobg999.cqid = 0
tiobg999.auss = tgyenox.no
tiobg999.usss = 0
tiobg999.exoc = tgyenox.no
tiobg999.exrc = 0
tiobg999.exdc = 0
display.all()
endif

field.tiobg999.indd:
after.input:
if tiobg999.indc = tgyenox.no then
tiobg999.gess = tgyenox.no
tiobg999.gsss = 0
tiobg999.exod = tgyenox.no
tiobg999.exrd = 0
tiobg999.exdd = 0
display.all()
endif

field.tiobg999.isoa:
when.field.changes:
if tiobg999.isoa = tgyenox.yes then
tiobg999.expa = date.num()
else
tiobg999.expa = 0
endif
display("tiobg999.expa")

field.tiobg999.isob:
when.field.changes:
if tiobg999.isob = tgyenox.yes then
tiobg999.expb = date.num()
else
tiobg999.expb = 0
endif
display("tiobg999.expb")

field.tiobg999.isoc:
when.field.changes:
if tiobg999.isoc = tgyenox.yes then
tiobg999.expc = date.num()
else
tiobg999.expc = 0
endif
display("tiobg999.expc")

field.tiobg999.isod:
when.field.changes:
if tiobg999.isod = tgyenox.yes then
tiobg999.expd = date.num()
else
tiobg999.expd = 0
endif
display("tiobg999.expd")

field.tiobg999.isoe:
when.field.changes:
if tiobg999.isoe = tgyenox.yes then
tiobg999.expe = date.num()
else
tiobg999.expe = 0
endif
display("tiobg999.expe")

field.tiobg999.moss:
when.field.changes:
if tiobg999.moss = tgyenox.yes then
tiobg999.mssd = date.num()
else
tiobg999.mssd = 0
tiobg999.osss = 0
display("tiobg999.osss")
endif
display("tiobg999.mssd")

field.tiobg999.nadc:
when.field.changes:
if tiobg999.nadc = tgyenox.yes then
tiobg999.ncpd = date.num()
else
tiobg999.ncpd = 0
endif
display("tiobg999.ncpd")

field.tiobg999.osss:
when.field.changes:
if tiobg999.osss > 100 or tiobg999.osss < 0 then
message("On Site Survey Score must be between 0 and 100.")
tiobg999.osss = 0
display("tiobg999.osss")
endif

field.tiobg999.suno:
before.input:
tiobg999.suno = ws.suno

after.input:
check.key.supplier()

field.ws.comc:
after.zoom:
update.supplier.status()

|****************************** FUNCTION SECTION ***************************

functions:

|******************************************************************************
| This function toggles as to whether the supplier is a key supplier or not.
|******************************************************************************
function check.key.supplier()
{
domain tgyenox f.aslc

select tccom020.aslc:f.aslc
from tccom020
where tccom020._index1 = {:tiobg999.suno}
selectdo
if f.aslc = tgyenox.no then
f.aslc = tgyenox.yes
else
f.aslc = tgyenox.no
endif
endselect

select tccom020.aslc
from tccom020 for update
where tccom020._index1 = {:tiobg999.suno}
selectdo
tccom020.aslc = f.aslc
db.update(ttccom020, db.retry)
commit.transaction()
endselect
}


|******************************************************************************
| This function will execute four separate functions which will apply business
| rules to determine supplier status and then display the updated results.
|******************************************************************************
function update.supplier.status()
{
update.supplier.commodity.status()
refresh.scar.data()
tiobg999.scsa = determine.medical.supplier.control.status()
tiobg999.scsb = determine.aero.supplier.control.status()
tiobg999.scsc = determine.auto.supplier.control.status()
tiobg999.scsd = determine.general.supplier.control.status()
update.occ()
display.all()
}


|******************************************************************************
| This function updates the assigns the display value based on the supplier
| commodity.
|******************************************************************************
function update.supplier.commodity.status()
{
return.value = check.supplier.commodity.status(tiobg999.suno)
if return.value = 0 then
ws.comc = "None"
else
if ws.sccl = ticomclass.major then
ws.comc = "Major"
endif
if ws.sccl = ticomclass.critical then
ws.comc = "Critical"
endif
endif
display("ws.comc")
}


|******************************************************************************
| This function toggles as to whether the supplier is a key supplier or not.
|******************************************************************************
function refresh.scar.data()
{
extern domain tcmcs.long f.stus.count
extern domain tcstus f.stus

ws.stus.open = 0
ws.stus.closed = 0
ws.stus.disputed = 0

select tcccm300.stus:f.stus,
count(tcccm300.stus):f.stus.count
from tcccm300
where tcccm300.suno = :tiobg999.suno
group by tcccm300.stus
selectdo
on case(etol(f.stus))
case(1): | Open
ws.stus.open = f.stus.count
break
case(2): | Closed
ws.stus.closed = f.stus.count
break
case(3): | Disputed
ws.stus.disputed = f.stus.count
break
endcase
endselect
display("ws.stus.open")
display("ws.stus.closed")
display("ws.stus.disputed")
}


|******************************************************************************
| This function will apply business rules to determine the status of a
| medical supplier.
|******************************************************************************
function domain tisupstat determine.medical.supplier.control.status()
{
domain tisupstat f.scst

f.scst = tisupstat.notapprove

|******************************************************************************
| Application of Business Rules to determine the approval status for Medical Suppliers

| Determine if Medical Industry is selected
if tiobg999.inda = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if there is a non-expired executive override
if tiobg999.exoa = tgyenox.yes and tiobg999.exda >= ws.today then
f.scst = tisupstat.override
return(f.scst)
endif

| Determine if the Supplier Add/Change/Delete form is completed and on-file
if tiobg999.sarc = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if there is a non-expired ISO 9001 certificate
if tiobg999.isoa = tgyenox.no or tiobg999.expa < ws.today then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if any commodities have been assigned to determine if supplier is Critical or Major
if ws.comc = "None" then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if the supplier provides commidities which are classified as Critical.
if ws.sccl = ticomclass.critical then
| Critical commodities require Medical Validation.
if tiobg999.mval = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
else
| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
| If there are accepted SCARs, then the supplier is conditionally approved.
f.scst = tisupstat.conditional
return(f.scst)
endif
endif
else
| Determine if the supplier provides commidities which are classified as Major.
if ws.sccl = ticomclass.major then
| See if any commodities are Testing types
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.test = tgyenox.yes
as set with 1 rows
selectdo
| Major Suppliers that we use for Testing must have
| (non-expired) A2LA Certification.
if tiobg999.atla = tgyenox.no or
(tiobg999.atla = tgyenox.yes and tiobg999.adla < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
f.scst = tisupstat.notapprove
endselect

| See if any commodities are Calibration types
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.catt = tgyenox.yes
as set with 1 rows
selectdo
| Major Suppliers that we use for Calibration must have
| (non-expired) ISO 17025 Certification.
if tiobg999.isoe = tgyenox.no or
(tiobg999.isoe = tgyenox.yes and tiobg999.expe < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
f.scst = tisupstat.notapprove
endselect
endif

| Major Medical suppliers must have either ISO 13485 or a Site Audit
if (tiobg999.isob = tgyenox.yes and tiobg999.expb >= ws.today) or
(tiobg999.moss = tgyenox.yes and tiobg999.mssd >= ws.today) then
f.scst = tisupstat.approve
else
| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
| If there are accepted SCARs, then the supplier is conditionally approved.
f.scst = tisupstat.conditional
return(f.scst)
endif
endif
endif

return(f.scst)
}


|******************************************************************************
| This function will apply business rules to determine the status of an
| Aerospace supplier.
|******************************************************************************
function domain tisupstat determine.aero.supplier.control.status()
{
domain tisupstat f.scst

f.scst = tisupstat.notapprove

|******************************************************************************
| Application of Business Rules to determine the approval status for Aerospace Suppliers

| Determine if Aerospace Industry is selected
if tiobg999.indb = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if there is a non-expired executive override
if tiobg999.exob = tgyenox.yes and tiobg999.exdb >= ws.today then
f.scst = tisupstat.override
return(f.scst)
endif

| Determine if the Supplier Add/Change/Delete form is completed and on-file
if tiobg999.sarc = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if any commodities have been assigned to determine if supplier is Critical or Major
if ws.comc = "None" then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if the supplier provides commidities which are classified as Critical.
if ws.sccl = ticomclass.critical then
| Critical commodities require Medical Validation.
if tiobg999.nadc = tgyenox.no or tiobg999.ncpd < ws.today then
f.scst = tisupstat.notapprove
return(f.scst)
else
| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
f.scst = tisupstat.conditional
return(f.scst)
endif
endif
else
| Determine if the supplier provides commidities which are classified as Major.
if ws.sccl = ticomclass.major then
| See if any commodities are Testing types
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.test = tgyenox.yes
as set with 1 rows
selectdo
| Major Suppliers that we use for Testing must have
| (non-expired) A2LA Certification.
if tiobg999.atla = tgyenox.no or
(tiobg999.atla = tgyenox.yes and tiobg999.adla < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
return.value = 0
endselect

| See if any commodities are Calibration types
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.catt = tgyenox.yes
as set with 1 rows
selectdo
| Major Suppliers that we use for Calibration must have
| (non-expired) ISO 17025 Certification.
if tiobg999.isoe = tgyenox.no or
(tiobg999.isoe = tgyenox.yes and tiobg999.expe < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
return.value = 0
endselect
endif

| Major Aerospace suppliers must have either ISO 9001, AS 9100, Site Audit, or a Self-Survey
if (tiobg999.isoa = tgyenox.yes and tiobg999.expa >= ws.today) or
(tiobg999.isoc = tgyenox.yes and tiobg999.expc >= ws.today) or
(tiobg999.aoss = tgyenox.yes and tiobg999.assd >= ws.today) or
(tiobg999.aess = tgyenox.yes) then
f.scst = tisupstat.approve
else
| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
f.scst = tisupstat.conditional
return(f.scst)
endif
endif
endif

return(f.scst)
}


|******************************************************************************
| This function will apply business rules to determine the status of an
| Automotive supplier.
|******************************************************************************
function domain tisupstat determine.auto.supplier.control.status()
{
domain tisupstat f.scst

f.scst = tisupstat.notapprove

|******************************************************************************
| Application of Business Rules to determine the approval status for Automotive Suppliers

| Determine if Automotive Industry is selected
if tiobg999.indc = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if there is a non-expired executive override
if tiobg999.exoc = tgyenox.yes and tiobg999.exdc >= ws.today then
f.scst = tisupstat.override
return(f.scst)
endif

| Determine if the Supplier Add/Change/Delete form is completed and on-file
if tiobg999.sarc = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if there is a non-expired ISO 9001 certificate
if tiobg999.isoa = tgyenox.no or tiobg999.expa < ws.today then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if any commodities have been assigned to determine if supplier is Critical or Major
if ws.comc = "None" then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if the supplier provides commidities which are classified as Critical.
if ws.sccl = ticomclass.critical then
| Critical commodities require CQI Accreditation.
if tiobg999.acqi = tgyenox.no or tiobg999.cqid < ws.today then
f.scst = tisupstat.notapprove
return(f.scst)
else
| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
f.scst = tisupstat.conditional
return(f.scst)
endif
endif
else
| Determine if the supplier provides commidities which are classified as Major.
if ws.sccl = ticomclass.major then
| See if any commodities are Testing types
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.test = tgyenox.yes
as set with 1 rows
selectdo
| Major Suppliers that we use for Testing must have
| (non-expired) A2LA Certification.
if tiobg999.atla = tgyenox.no or
(tiobg999.atla = tgyenox.yes and tiobg999.adla < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
return.value = 0
endselect

| See if any commodities are Calibration types
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.catt = tgyenox.yes
as set with 1 rows
selectdo
| Major Suppliers that we use for Calibration must have
| (non-expired) ISO 17025 Certification.
if tiobg999.isoe = tgyenox.no or
(tiobg999.isoe = tgyenox.yes and tiobg999.expe < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
return.value = 0
endselect
endif

| Major Medical suppliers must have either TS 16949 or a Self Survey
if (tiobg999.isod = tgyenox.yes and tiobg999.expd >= ws.today) or
(tiobg999.auss = tgyenox.yes) then
f.scst = tisupstat.approve
else
| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
f.scst = tisupstat.conditional
return(f.scst)
endif
endif
endif
return(f.scst)
}


|******************************************************************************
| This function will apply business rules to determine the status of an
| General supplier.
|******************************************************************************
function domain tisupstat determine.general.supplier.control.status()
{
domain tisupstat f.scst

f.scst = tisupstat.notapprove

|******************************************************************************
| Application of Business Rules to determine the approval status for General Suppliers

| Determine if General Industry is selected
if tiobg999.indd = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if there is a non-expired executive override
if tiobg999.exod = tgyenox.yes and tiobg999.exdd >= ws.today then
f.scst = tisupstat.override
return(f.scst)
endif

| Determine if the Supplier Add/Change/Delete form is completed and on-file
if tiobg999.sarc = tgyenox.no then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Determine if any commodities have been assigned to determine if supplier is Critical or Major
if ws.comc = "None" then
f.scst = tisupstat.notapprove
return(f.scst)
endif

| Check to see if any commodities are for Testing
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.test = tgyenox.yes
as set with 1 rows
selectdo
| General Suppliers that we use for Testing must have
| (non-expired) A2LA Certification.
if tiobg999.atla = tgyenox.no or
(tiobg999.atla = tgyenox.yes and tiobg999.adla < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
return.value = 0
endselect

| Check to see if any commodities are for Calibration
select tiobg998.test
from tiobg997, tiobg998
where tiobg997.comc = tiobg998.comc
and tiobg997._index1 = {:tiobg999.suno}
and tiobg998.catt = tgyenox.yes
as set with 1 rows
selectdo
| General Suppliers that we use for Calibration must have
| (non-expired) ISO 17025 Certification.
if tiobg999.isoe = tgyenox.no or
(tiobg999.isoe = tgyenox.yes and tiobg999.expe < ws.today) then
f.scst = tisupstat.notapprove
return(f.scst)
endif
selectempty
return.value = 0
endselect

| If there are non-accepted SCARs, then the supplier is disapproved.
if ws.stus.disputed <> 0 then
f.scst = tisupstat.notapprove
return(f.scst)
endif
|If the Supplier no SCARs they are approved.
if ws.stus.open = 0 then
f.scst = tisupstat.approve
return(f.scst)
else
f.scst = tisupstat.conditional
return(f.scst)
endif

return(f.scst)
}

|****************************** END OF SCRIPT ***************************

Sorry Eric - but did I miss something? I am sure you know this, but I mean in any maintain session no changes are saved until a "save" or "save and exit" is executed. You can change all the fields you want, but until a save or commit is executed they do not make it into the table. Anytime I want to update a table in a session like this I usually execute(update.db) or in somecases a quick select..update..commit. Is there any reason you can't just execute(update.db)? I feel like I am missing something on this thread.

Thanks Mark... I had tried that a few times but kept getting recursion errors, but then (smack me in the head with a big stick), I never commented out the before.rewrite calls to the functions which was causing the issue.

This is one of those projects where I've received the final revision of the user specifications 18 times now and I think that with all the changes I was going crazy.

Thanks to all of you that responded. I did learn some things about the update.occ() as well.