>I'm wondering if there is a way to identify, within a file index, what
>type of file operation has occurred.
Hello, Jeff.
Listed below is a program that uses the index like a trigger. It can only
detect WRITE and DELETE statements though. I used this approach with PI and
PI/open as they never supported triggers (unless one considered the AKUFS a
trigger).
I have not tried this approach on UniVerse so I can not speak to its
performance, however, I suspect it will work reasonably well. If you do give
it a try, please let me know your results.
Ask me questions about what it is doing if things are unclear. I published
an article in Infocus and an NPUG proceedings that describes the algorithm
in detail but I do not have that in electronic form. :-(
Best regards,
Gyle
**********************************************************
SUBROUTINE BUILD.POD.INDEX(result)
* This program projects a nested Purchase Order into a normalized form
suitable for reporting purposes.
$INCLUDE PO.EQUATE.INS.IBAS
$INCLUDE POD.EQUATE.INS.IBAS
DIM temporary.pod.record(2000)
DIM purchase.order.record(100)
EQUATE TRUE TO 1
EQUATE FALSE TO 0
EQUATE ADD TO 1
EQUATE DELETING TO 2
EQUATE UPDATE.FIRST.CALL TO 3
EQUATE UPDATE.SECOND.CALL TO 4
EQUATE NEEDLESS.WRITE.PERFORMED TO 5
EQUATE BUILDING.INDEX TO 6
COMMON /PO.POD/ Po.File.Variable,
Pod.File.Variable,
Last.Updated.Id,
Last.Mode,
Last.Added.Id,
Build.Time
result = ""
IF NOT(FILEINFO(Po.File.Variable, 0)) THEN
OPEN "", "PO" TO Po.File.Variable ELSE
PRINT
PRINT "Unable to open the PO file! (BUILD.POD.INDEX)"
RETURN
END
Last.Updated.Id = ""
Last.Added.Id = ""
Last.Mode = ""
Build.Time = 0
END
IF NOT(FILEINFO(Pod.File.Variable, 0)) THEN
OPEN "", "POD" TO Pod.File.Variable ELSE
PRINT
PRINT "Unable to open the POD file! (BUILD.POD.INDEX)"
RETURN
END
Last.Updated.Id = ""
Last.Added.Id = ""
Last.Mode = ""
Build.Time = 0
END
IF @SENTENCE[1, 11] = "BUILD.INDEX" THEN
Last.Mode = BUILDING.INDEX
END ELSE
Build.Time = 0
READ temporary.record FROM Po.File.Variable, @ID THEN
IF temporary.record = @RECORD THEN
BEGIN CASE
CASE @ID = Last.Updated.Id
Last.Mode = UPDATE.SECOND.CALL
Last.Added.Id = ""
CASE @ID = Last.Added.Id
Last.Mode = NEEDLESS.WRITE.PERFORMED
Last.Added.Id = ""
CASE TRUE
Last.Mode = ADD
Last.Added.Id = @ID
END CASE
Last.Updated.Id = ""
END ELSE
Last.Mode = UPDATE.FIRST.CALL
Last.Added.Id = ""
Last.Updated.Id = @ID
END
END ELSE
Last.Mode = DELETING
Last.Updated.Id = ""
Last.Added.Id = ""
temporary.record = ""
END
END
display.error.messages = TRUE
ON Last.Mode GOSUB add:,
delete:,
update:,
update.phase.2:,
needless.write.performed:,
build:
RETURN
add:
GOSUB build.pod.records:
IF result # "ERROR" THEN
GOSUB write.pod.records:
END
RETURN
delete:
display.error.messages = FALSE
GOSUB build.pod.records:
GOSUB delete.pod.records:
RETURN
update:
display.error.messages = FALSE
GOSUB build.pod.records:
IF result # "ERROR" THEN
GOSUB write.pod.records:
END
RETURN
update.phase.2:
GOSUB delete.pod.records:
GOSUB build.pod.records:
IF result # "ERROR" THEN
GOSUB write.pod.records:
END
RETURN
needless.write.performed:
display.error.messages = FALSE
GOSUB build.pod.records:
RETURN
build:
IF Build.Time = 0 THEN
PRINT "Clearing the POD file. (BUILD.POD.INDEX)"
CLEARFILE Pod.File.Variable
Build.Time = 1
END
GOSUB build.pod.records:
IF result # "ERROR" THEN
GOSUB write.pod.records:
END
RETURN
build.pod.records:
MATPARSE purchase.order.record FROM @RECORD, @FM
detail.record.counter = 0
errors = ""
result = ""
number.of.backordered.line.items = 0
number.of.excess.receipt.line.items = 0
customer.id = purchase.order.record(PO.VENDOR.NUMBER)
IF customer.id = "" THEN
customer.id = purchase.order.record(6)
END
customer.po = purchase.order.record(PO.PURCHASE.ORDER.NUMBER)
current.order.status = purchase.order.record(PO.STATUS)
hold = purchase.order.record(14)
purchase.order.type = purchase.order.record(27)
work.order.sequense = purchase.order.record(63)
* Process each line item
IF LEN(purchase.order.record(PO.LINE.ITEM)) THEN
line.item.position = 0
LOOP
REMOVE line.item FROM purchase.order.record(PO.LINE.ITEM)
SETTING delimiter
line.item.position += 1
part.number = purchase.order.record(PO.LINE.ITEM.PART.NUMBER)<1,
line.item.position>
part.description = purchase.order.record(PO.PART.DESCRIPTION)<1,
line.item.position>
quantity.ordered = purchase.order.record(PO.ORDER.QUANTITY)<1,
line.item.position>
unit.price = purchase.order.record(PO.UNIT.PRICE)<1,
line.item.position>
vendor.description =
purchase.order.record(PO.VENDOR.DESCRIPTION)<1, line.item.position>
product.manager = purchase.order.record(PO.PROD.MANAGER)<1,
line.item.position>
condition = purchase.order.record(PO.CONDITION)<1,
line.item.position>
production.notes = purchase.order.record(PO.PRODUCTION.NOTES)<1,
line.item.position>
brokerage.po = purchase.order.record(PO.BROKERAGE.PO)<1,
line.item.position>
manufacturer.code =
purchase.order.record(PO.MANUFACTURER.CODE)<1, line.item.position>
trans.code = purchase.order.record(PO.TRANS.CODE)<1,
line.item.position>
print.flag = purchase.order.record(71)<1, line.item.position>
license.code = purchase.order.record(PO.LICENSE.CODE)<1,
line.item.position>
line.item.hold = purchase.order.record(PO.ITEM.HOLD)<1,
line.item.position>
ncon = purchase.order.record(PO.NCON)<1, line.item.position>
dpac = purchase.order.record(PO.DPAC)<1, line.item.position>
revision.level = purchase.order.record(PO.REVISION)<1,
line.item.position>
back.order.quantity =
purchase.order.record(PO.BACK.ORDER.QUANTITY)<1, line.item.position>
bums = purchase.order.record(PO.BUMS)<1, line.item.position>
lsta = purchase.order.record(PO.LSTA)<1, line.item.position>
change.notes = purchase.order.record(PO.CHANGE.NOTES)<1,
line.item.position>
line.notes = purchase.order.record(PO.LINE.NOTES)<1,
line.item.position>
receipts.field = purchase.order.record(39)<1,
line.item.position>
scheduled.receipts.field = purchase.order.record(34)<1,
line.item.position>
target.dates.field = purchase.order.record(70)<1,
line.item.position>
*---- Check integrity of key data
line.errors = ""
IF NOT(NUM(quantity.ordered)) THEN
line.errors<-1> = " The order quantity is invalid."
END
IF NOT(NUM(back.order.quantity)) THEN
line.errors<-1> = " The back order quantity is invalid."
END
IF NOT(NUM(unit.price)) THEN
line.errors<-1> = " The unit price is invalid."
END
IF LEN(line.errors) THEN
errors<-1> = "Line item number : " : line.item : " is
incorrect!"
errors<-1> = line.errors
END ELSE
*---- Build skeleton detail record
po.detail.record = ""
po.detail.record<POD.LINE.ITEM.NUMBER> = line.item
po.detail.record<POD.PART.NUMBER> = part.number
po.detail.record<POD.PART.DESCRIPTION> = part.description
po.detail.record<POD.BUMS> = bums
po.detail.record<POD.DPAC> = dpac
po.detail.record<POD.PO.TYPE> = purchase.order.type
po.detail.record<POD.PMGR> = product.manager
po.detail.record<POD.VEND.DESCRIPTION> = vendor.description
po.detail.record<POD.UNIT.PRICE> = unit.price
po.detail.record<POD.LI.HOLD> = line.item.hold
po.detail.record<POD.REVISION> = revision.level
po.detail.record<POD.WO.SEQ.NUMBER> = work.order.sequense
po.detail.record<POD.DATE.CREATED> = DATE()
po.detail.record<25> = condition
po.detail.record<26> = line.notes
po.detail.record<27> = change.notes
*---- Calculate the status of this line item
GOSUB calculate.line.item.status:
*---- Build all necessary detail records
IF there.are.backorders THEN
GOSUB build.scheduled.receipt.records:
END
IF there.are.receipts THEN
GOSUB build.receipt.records:
END
IF there.are.excess.receipts THEN
GOSUB build.excess.receipt.records:
END
IF total.line.item.scheduled.receipt.quantity = 0 THEN
GOSUB add.cancel.line:
END
END
WHILE delimiter DO REPEAT
IF LEN(errors) THEN
result = "ERRORS"
IF display.error.messages THEN
IF Last.Mode = BUILDING.INDEX THEN
PRINT
END
PRINT "PO# : " : @ID : " has incorrect data. The problems
are listed below." : @SYS.BELL
LOOP
PRINT REMOVE(errors, delimiter)
WHILE delimiter DO REPEAT
PRINT "No further processing of this order is possible until
this problem"
PRINT "is corrected."
END
END ELSE
BEGIN CASE
CASE current.order.status = "T"
result = ""
CASE (number.of.backordered.line.items > 0) OR
(number.of.excess.receipt.line.items > 0)
IF current.order.status # "B" THEN
result = "B"
END
CASE TRUE
IF current.order.status # "C" THEN
result = "C"
END
END CASE
END
END
RETURN
add.cancel.line:
scheduled.receipts.field = scheduled.receipts.field
LOOP
REMOVE scheduled.receipts FROM scheduled.receipts.field SETTING
minor.delimiter
REMOVE original.dates FROM target.dates.field SETTING
original.delimiter
scheduled.receipt.date = FIELD(scheduled.receipts, "*", 1)
scheduled.receipt.quantity = FIELD(scheduled.receipts, "*", 2)
original.date = FIELD(original.dates, "*",1)
po.detail.record<POD.SHIP.DATE> = scheduled.receipt.date
po.detail.record<POD.QUANTITY> = scheduled.receipt.quantity
po.detail.record<POD.CUSTOMER.ID> = customer.id
po.detail.record<POD.PAYDATE> = ""
po.detail.record<POD.DETAIL.TYPE> = "C"
po.detail.record<28> = original.date
detail.record.counter += 1
temporary.pod.record(detail.record.counter) = po.detail.record
UNTIL minor.delimiter = 0 REPEAT
RETURN
calculate.line.item.status:
total.line.item.received.quantity = 0
IF LEN(receipts.field) THEN
receipts.field = receipts.field
there.are.receipts = TRUE
LOOP
REMOVE receipts FROM receipts.field SETTING minor.delimiter
receipts.quantity = FIELD(receipts, "*", 2)
total.line.item.received.quantity += receipts.quantity
WHILE minor.delimiter DO REPEAT
END ELSE
there.are.receipts = FALSE
END
total.line.item.scheduled.receipt.quantity = 0
IF LEN(scheduled.receipts.field) THEN
scheduled.receipts.field = scheduled.receipts.field
LOOP
REMOVE scheduled.receipt FROM scheduled.receipts.field SETTING
minor.delimiter
scheduled.receipt.quantity = FIELD(scheduled.receipt, "*", 2)
total.line.item.scheduled.receipt.quantity +=
scheduled.receipt.quantity
WHILE minor.delimiter DO REPEAT
END
total.line.item.backorder.quantity =
total.line.item.scheduled.receipt.quantity -
total.line.item.received.quantity
there.are.backorders = FALSE
there.are.excess.receipts = FALSE
BEGIN CASE
CASE total.line.item.backorder.quantity = 0
CASE total.line.item.backorder.quantity > 0
there.are.backorders = TRUE
number.of.backordered.line.items += 1
CASE total.line.item.backorder.quantity < 0
there.are.excess.receipts = TRUE
number.of.excess.receipt.line.items += 1
END CASE
RETURN
build.receipt.records:
target.dates.quantities = purchase.order.record(70)<1,
line.item.position>
save.original.date = ""
GOSUB get.original.schedule.date:
receipts.total = 0
receipts.field = receipts.field
LOOP
REMOVE receipts FROM receipts.field SETTING minor.delimiter
receipts.date = FIELD(receipts, "*", 1)
receipts.quantity = FIELD(receipts, "*", 2)
receipts.number = FIELD(receipts, "*", 3)
receipts.payment.flag = FIELD(receipts, "*", 4)
po.detail.record<POD.SHIP.DATE> = receipts.date
po.detail.record<POD.QUANTITY> = receipts.quantity
po.detail.record<POD.CUSTOMER.ID> = customer.id : "*" :
receipts.number
po.detail.record<POD.PAYDATE> = receipts.payment.flag
po.detail.record<POD.DETAIL.TYPE> = "S"
receipts.total += receipts.quantity
IF receipts.total GT original.quantity THEN
receipts.total = 0
GOSUB get.original.schedule.date:
END
po.detail.record<28> = original.date
detail.record.counter += 1
temporary.pod.record(detail.record.counter) = po.detail.record
WHILE minor.delimiter DO REPEAT
date.of.last.receipt = receipts.date
RETURN
get.original.schedule.date:
REMOVE original.date.quantity FROM target.dates.quantities SETTING
original.delimiter
original.date = FIELD(original.date.quantity, "*",1)
original.quantity = FIELD(original.date.quantity, "*",2)
IF original.date = "" THEN
original.date = save.original.date
END ELSE
save.original.date = original.date
END
RETURN
build.scheduled.receipt.records:
scheduled.receipts.field = scheduled.receipts.field
total.scheduled.receipts = 0
total.receipts = total.line.item.received.quantity ;* start with
all real receipts
LOOP
REMOVE scheduled.receipts FROM scheduled.receipts.field SETTING
minor.delimiter
REMOVE original.dates FROM target.dates.field SETTING
original.delimiter
scheduled.receipt.date = FIELD(scheduled.receipts, "*", 1)
scheduled.receipt.quantity = FIELD(scheduled.receipts, "*", 2)
original.date = FIELD(original.dates, "*",1)
total.scheduled.receipts += scheduled.receipt.quantity
IF total.scheduled.receipts > total.receipts THEN
backorder.quantity = total.scheduled.receipts - total.receipts
IF backorder.quantity > total.line.item.backorder.quantity THEN
backorder.quantity = total.line.item.backorder.quantity
END
total.receipts += backorder.quantity ;* add in scheduled
receipts
po.detail.record<POD.SHIP.DATE> = scheduled.receipt.date
po.detail.record<POD.QUANTITY> = backorder.quantity
po.detail.record<POD.CUSTOMER.ID> = customer.id
po.detail.record<POD.PAYDATE> = ""
po.detail.record<POD.DETAIL.TYPE> = "B"
po.detail.record<28> = original.date
detail.record.counter += 1
temporary.pod.record(detail.record.counter) = po.detail.record
END
WHILE minor.delimiter DO REPEAT
RETURN
build.excess.receipt.records:
po.detail.record<POD.SHIP.DATE> = date.of.last.receipt
po.detail.record<POD.QUANTITY> =
ABS(total.line.item.backorder.quantity)
po.detail.record<POD.PAYDATE> = ""
po.detail.record<POD.DETAIL.TYPE> = "O"
po.detail.record<28> = original.date
detail.record.counter += 1
temporary.pod.record(detail.record.counter) = po.detail.record
RETURN
write.pod.records:
FOR I = 1 TO detail.record.counter
WRITE temporary.pod.record(I) TO Pod.File.Variable, I : '*' : @ID
NEXT I
RETURN
delete.pod.records:
pod.sequence.number = 0
end.of.pods = FALSE
LOOP
pod.sequence.number += 1
READ dummy FROM Pod.File.Variable, pod.sequence.number : "*" : @ID
ELSE
end.of.pods = TRUE
END
UNTIL end.of.pods DO
DELETE Pod.File.Variable, pod.sequence.number : "*" : @ID
REPEAT
dummy = ""
RETURN
END
-------
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
