return to PRS Technologies website
<![CDATA[
###############################################################################
#
# Module: dupe_cost1.4gl
# Author: Peter R. Schmidt
# Description: Find and isolate duplicate costs
#
# Augument 1 optional: batch period to process
# Augument 2 optional: only process batches >= arg 2
# Augument 3 optional: only process batches <= arg 3
# Augument 4 optional: batch operator
# Augument 5 optional: debug = Y
#
# Change Log
#
# Date Name Description.................
# 05/31/01 Peter R. Schmidt Start Program
# 07/19/01 Peter R. Schmidt Improvements
#
###############################################################################
database son_db
###############################################################################
globals
define select_period char(4)
define seltxt char(1024)
define batch1 integer
define batch2 integer
define batch_bop char(8)
define dq char(1)
define flag_debug integer
define report_name char(50)
define p_batch1 record like batch.*
define p_cost2 record like cost.*
define p_cost3 record like cost.*
define p_cost4 record like cost.*
define p_costdesc record like costdesc.*
define a_desc1 array[100] of char(48)
define a_desc2 array[100] of char(48)
define a_max integer
define x integer
define y integer
define z integer
end globals
###############################################################################
main
define flag_dupe_cost1 integer
define flag_dupe_batch1 integer
define cnt_100 integer
define cnt_cursor2 integer
define cnt_save1 integer
define cnt_save2 integer
define cnt_dupe1 integer
define cnt_dupe2 integer
define cnt_temp1 integer
SET ISOLATION TO DIRTY READ
if num_args() < 1 or num_args() > 5 then
display "Usage: cost_dupe1.4ge [period] [minimum batch] [maximum batch] [bop] [debug=1]"
exit program 1
end if
call startlog("dupe_cost1.log")
let select_period = arg_val(1)
if num_args() >= 2 then
let batch1 = arg_val(2)
end if
if num_args() >= 3 then
let batch2 = arg_val(3)
end if
if num_args() >= 4 then
let batch_bop = arg_val(4)
if batch_bop = "-" then
let batch_bop = NULL
end if
end if
if num_args() >= 5 then
let flag_debug = arg_val(5)
end if
if batch1 IS NULL then let batch1 = 0 end if
if batch2 IS NULL then let batch2 = 0 end if
if flag_debug IS NULL then let flag_debug = 0 end if
display ""
display "Check for duplicate costs for period: ",select_period
if batch1 > 0 then
display "Only process batches >= ",batch1 using "<<<<<<<&"
end if
if batch2 > 0 then
display "Only process batches <= ",batch2 using "<<<<<<<&"
end if
display ""
let cnt_save1 = 0
let cnt_save2 = 0
let cnt_dupe1 = 0
let cnt_dupe2 = 0
let a_max = 100
--------------------------------------------------------------------------------
create temp table dupe_index1 (
cindex integer
) with no log
create index i_dupe_index1 on dupe_index1(cindex)
create temp table not_dupe_index2 (
cindex integer
) with no log
--------------------------------------------------------------------------------
let dq = ASCII(34)
let seltxt = "select * from batch where btype = 'C' "
if select_period IS NOT NULL and select_period <> "-" then
let seltxt = seltxt clipped," and bper = ",
dq,select_period,dq
end if
if batch1 > 0 then
let seltxt = seltxt clipped," and bbatch >= ",
dq,batch1 using "<<<<<<<<<&",dq
end if
if batch2 > 0 then
let seltxt = seltxt clipped," and bbatch <= ",
dq,batch2 using "<<<<<<<<<&",dq
end if
if batch_bop IS NOT NULL then
let seltxt = seltxt clipped," and bop = ",
dq,batch_bop clipped,dq
end if
let seltxt = seltxt clipped," order by bbatch desc"
call errorlog(seltxt)
prepare statmt1 from seltxt
declare cursor1 cursor for statmt1
--------------------------------------------------------------------------------
let seltxt = "select * from cost where ",
"cbatch < ? ",
"and cmatter = ? "
## if batch1 > 0 then
## let seltxt = seltxt clipped," and cbatch >= ",
## dq,batch1 using "<<<<<<<<<&",dq," "
## end if
let seltxt = seltxt clipped,
" and cdisbdt = ? ",
" and cquant = ? ",
" and crate = ? ",
" and camount = ? ",
" and ctk = ? ",
" and cledger = ? ",
" and ccode = ? ",
" and cauth = ? ",
" and cloc = ? ",
" and cindex <> ?"
prepare statmt3 from seltxt
declare cursor3 cursor for statmt3
--------------------------------------------------------------------------------
let seltxt = "select * from costdesc where cindex = ? order by cdline"
prepare statmt5 from seltxt
declare cursor5 cursor for statmt5
--------------------------------------------------------------------------------
start report list_dupes to "dupe_cost1.out"
--------------------------------------------------------------------------------
open cursor1
foreach cursor1 into p_batch1.* # get one row per batch
display "Checking batch ",p_batch1.bbatch using "<<<<<<<&"
let cnt_100 = 0
let cnt_cursor2 = 0
let cnt_dupe1 = 0
let flag_dupe_batch1 = FALSE
drop table not_dupe_index2
create temp table not_dupe_index2 (
cindex integer
) with no log
create temp table dupe_index2 (
cindex integer
) with no log
declare cursor2 cursor for
select * from cost
where cbatch = p_batch1.bbatch
order by cindex,cdisbdt,cmatter
foreach cursor2 into p_cost2.* # get all the costs in the batch
let flag_dupe_cost1 = FALSE
if flag_debug > 0 then
let cnt_cursor2 = cnt_cursor2 + 1
let cnt_100 = cnt_100 + 1
if cnt_100 = 500 then
display "Processed ",cnt_cursor2 using "<<<,<<<,<<&"," costs ",""
let cnt_100 = 0
end if
end if
open cursor3 using # Does this cost have any dupes ?
p_cost2.cbatch,
p_cost2.cmatter,
p_cost2.cdisbdt,
p_cost2.cquant,
p_cost2.crate,
p_cost2.camount,
p_cost2.ctk,
p_cost2.cledger,
p_cost2.ccode,
p_cost2.cauth,
p_cost2.cloc,
p_cost2.cindex
foreach cursor3 into p_cost3.*
-- looks very similar - is the narrative the same ?
let x = 0
let y = 0
open cursor5 using p_cost2.cindex
foreach cursor5 into p_costdesc.*
let x = x + 1
if x > a_max then exit foreach end if
let a_desc1[x] = p_costdesc.cddesc
end foreach
open cursor5 using p_cost3.cindex
foreach cursor5 into p_costdesc.*
let y = y + 1
if y > a_max then exit foreach end if
let a_desc2[y] = p_costdesc.cddesc
end foreach
if x > 0 then # if narrative exists...
# If different number of line of narrative,
# then not a dupe
if x <> y then exit foreach end if
for z = 1 to x
# If narrative not the same - not a dupe
if a_desc1[z] <> a_desc2[z] then
exit foreach
end if
end for
end if
-- Ok - looks like a dupe
-- Has this cost previously been recorded as a dupe ?
select count(*)
into cnt_temp1
from dupe_index2
where dupe_index2.cindex = p_cost3.cindex
if cnt_temp1 IS NULL then let cnt_temp1 = 0 end if
if cnt_temp1 = 0 then # not previously recorded as a dupe
let flag_dupe_cost1 = TRUE
let flag_dupe_batch1 = TRUE
let cnt_dupe1 = cnt_dupe1 + 1
let cnt_dupe2 = cnt_dupe2 + 1
output to report list_dupes
(p_cost2.*,p_cost3.*)
-- Record the index # of all dupes
insert into dupe_index2 values
(p_cost3.cindex)
# Once I decided this is a dupe, there is no reason
# to check further because I only want to find out
# if the 'current' cost needs to be put into the
# 'good' pile or the 'bad/dupe' pile.
# It doesn't matter how many times it got duplicated
# as far as this cost is concerned
EXIT FOREACH # only match up 1 dupe
end if
end foreach
# IF NOT A DUPE - SAVE THE INDEX NUMBER
if flag_dupe_cost1 = FALSE then
insert into not_dupe_index2 values (p_cost2.cindex)
end if
end foreach # << END OF ALL COSTS FOR A BATCH
close cursor2
if flag_debug > 0 then
display cnt_cursor2 using "<<<,<<<,<<&"," costs in batch"
end if
if flag_dupe_batch1 = TRUE then # DID THIS BATCH HAVE ANY DUPE COSTS ?
let cnt_save1 = 0
-- copy index of dupes found into 'permanent' table
insert into dupe_index1
select unique * from dupe_index2
update statistics for table dupe_index1
display cnt_dupe1 using "<<<,<<<,<<&",
" Dupes found - saving non dupes for batch: ",
p_batch1.bbatch using "<<<<<<<&"
let report_name = "save_",
p_batch1.bbatch using "<<<<<<<&",
".txt"
start report save_non_dupes to report_name
declare cursor4 cursor for
select cost.*
from cost, not_dupe_index2
where cost.cindex = not_dupe_index2.cindex
foreach cursor4 into p_cost4.*
output to report save_non_dupes(p_cost4.*)
let cnt_save1 = cnt_save1 + 1
let cnt_save2 = cnt_save2 + 1
end foreach
close cursor4
finish report save_non_dupes
display cnt_save1 using "<<<,<<<,<<&"," non-dupes saved"
end if
drop table dupe_index2
end foreach
close cursor1
finish report list_dupes
drop table dupe_index1
display ""
display cnt_dupe2 using "<<<,<<<,<<&"," dupes found"
display cnt_save2 using "<<<,<<<,<<&"," non-dupes saved"
end main
###############################################################################
report save_non_dupes (cost3)
define cost3 record like cost.*
OUTPUT
TOP MARGIN 0
BOTTOM MARGIN 0
LEFT MARGIN 0
PAGE LENGTH 1
FORMAT
ON EVERY ROW
print "DO"
print cost3.cdisbdt using "mmddyy"
print cost3.cmatter
print cost3.cquant using "<<<<<<<&"
print cost3.crate using "<<<<<<<.&&"
print cost3.camount using "<<<<<<<<<.&&"
print cost3.ccode
print cost3.ctk
print cost3.cauth
print cost3.cloc
open cursor5 using cost3.cindex
foreach cursor5 into p_costdesc.*
print p_costdesc.cddesc clipped
end foreach
skip 1 line
END REPORT
###############################################################################
report list_dupes (cost1,cost2)
define cost1 record like cost.*
define cost2 record like cost.*
OUTPUT
TOP MARGIN 0
BOTTOM MARGIN 0
LEFT MARGIN 0
PAGE LENGTH 60
ORDER EXTERNAL BY cost1.cbatch
FORMAT
PAGE HEADER
print
column 1, "Duplicate Cost Report",
column 50, TODAY
print
column 50, TIME
skip 1 line
{
1 2 3 4 5 6
1234567890123456789012345678901234567890123456789012345678901234567890
BBBBBBBB mm/dd/yy BBBBBBBB mm/dd/yy mm/dd/yy CCODEEE MMMMMMMMMMMMMMM AAAAAAAAAAAAAAA
BBBBBBBB BBBBBBBB mm/dd/yy CCODEEE MMMMMMMMMMMMMMM AAAAAAAAAAAAAAA
}
ON EVERY ROW
print
column 1, cost1.cbatch using "#######&",
column 10, cost2.cbatch using "#######&",
column 19, cost1.cdisbdt using "mm/dd/yy",
column 28, cost1.ccode,
column 36, cost1.cmatter,
column 52, cost1.camount using "----,---,---.&&"
AFTER GROUP OF cost1.cbatch
SKIP 1 LINE
ON LAST ROW
print count(*) using "<<<,<<<,<<&", " dupes found"
print sum(cost1.camount) using "-<<<,<<<,<<<.&&", " dollars worked"
END REPORT
###############################################################################
]]>