return to PRS Technologies website
###############################################################################
#
# Module: dupe_cost2.4gl
# Author: Peter R. Schmidt
# Description: Find duplicate costs that have already been included in a proforma
# in Elite
#
# Search out proformas for costs that have been duplicated
# Create a report by matter billing attorney that billers can use
# to be on the lookout for duplicated costs.
#
# Note: This is not 100% reliable. If the exact same cost actually was
# was incurred for the same amount and same matter and same day, it
# looks just like a duplicate.
#
# Augument 1 only process batches >= arg 1
# Augument 2 only process batches <= arg 2
# Augument 3 only process proforma date >= arg 3
# Augument 4 only process proforma operator = arg 4
# Augument 5 optional: debug = 1
#
# Change Log
#
# Date Name Description.................
# 06/08/01 Peter R. Schmidt Start Program
#
###############################################################################
database son_db
###############################################################################
globals
define select_period char(4)
define seltxt char(1024)
define batch1 integer
define batch2 integer
define phdate1 date
define dq char(1)
define flag_debug integer
define cnt_dupe1 integer
define cnt_dupe2 integer
define cnt_dupe3 integer
define cnt_temp1 integer
define batch_bop char(8)
define FLAG_LAST_ROW integer
define p_batch1 record like batch.*
define p_cost1 record like cost.*
define p_cost2 record like cost.*
define p_costdesc1 record like costdesc.*
define p_costdesc2 record like costdesc.*
define p_costdesc record like costdesc.*
define p_prohead record like prohead.*
define p_prodetail record like prodetail.*
define p_matter record like matter.*
define p_timekeep record like timekeep.*
end globals
###############################################################################
main
define cnt_100 integer
define cnt_200 integer
define cnt_cursor7 integer
define cnt_cursor6 integer
# SET EXPLAIN ON
SET ISOLATION TO DIRTY READ
if num_args() < 1 or num_args() > 5 then
display "Usage: cost_dupe2.4ge [start batch] [ending batch] [pfa status date] [operator] [debug=1]"
exit program 1
end if
call startlog("dupe_cost2.log")
let dq = ASCII(34)
display ""
display "Check for duplicate costs in proformas"
display "Selecting..."
display ""
let cnt_100 = 0
let cnt_200 = 0
let FLAG_LAST_ROW = FALSE
--------------------------------------------------------------------------------
let batch1 = arg_val(1)
let batch2 = arg_val(2)
let phdate1 = arg_val(3)
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
--------------------------------------------------------------------------------
let seltxt = "select cost.* from cost,costdesc where ",
" cost.cmatter = ? ",
" and cost.cbatch >= ? ",
" and cost.cbatch <= ? ",
" and cost.cindex != ? ",
" and cost.cdisbdt = ? ",
" and cost.cquant = ? ",
" and cost.crate = ? ",
" and cost.camount = ? ",
" and cost.ctk = ? ",
" and cost.cledger = ? ",
" and cost.ccode = ? ",
" and cost.cauth = ? ",
" and cost.cloc = ? ",
" and cost.cindex = costdesc.cindex ",
" and costdesc.cdline = 1 ",
" and costdesc.cddesc = ?"
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
--------------------------------------------------------------------------------
let seltxt = "select * from prodetail,cost,batch,costdesc where ",
"prodetail.phindex = ? and ",
"pdtype = ",dq,"C",dq," and ",
"pddetail = cost.cindex and ",
"cost.cinvoice IS NULL and ",
"cost.cbatch = batch.bbatch "
if batch_bop IS NOT NULL then
let seltxt = seltxt clipped," and batch.bop = ",
dq,batch_bop clipped,dq
end if
let seltxt = seltxt clipped,
" and cost.cindex = costdesc.cindex ",
" and costdesc.cdline = 1",
" order by cost.cdisbdt"
call errorlog(seltxt)
prepare statmt7 from seltxt
declare cursor7 cursor for statmt7
--------------------------------------------------------------------------------
start report list_dupes to "dupe_cost2.out"
create temp table dupe_index1 (
cindex integer
) with no log
create index i_dupe_index1 on dupe_index1(cindex)
--------------------------------------------------------------------------------
let cnt_cursor6 = 0
declare cursor6 cursor for
select * from prohead, matter, timekeep
where
(phdate >= phdate1 or phstatus matches "C*") and
phmatter = matter.mmatter and
matter.mbillaty = timekeep.tkinit
order by timekeep.tkinit, phmatter, phindex
foreach cursor6 into p_prohead.*, p_matter.*, p_timekeep.*
let cnt_cursor6 = cnt_cursor6 + 1
if cnt_cursor6 = 1 then
display "Searching for duplicate costs..."
end if
let cnt_200 = cnt_200 + 1
if cnt_200 = 100 then
display "checking record ",cnt_cursor6 using "<<<,<<<,<<&",
", proforma ",p_prohead.phindex using "<<<<<<<<<<<<&",
" on matter ",p_prohead.phmatter
let cnt_200 = 0
end if
if flag_debug = 1 then
display "checking record ",cnt_cursor6 using "<<<,<<<,<<&",
", proforma ",p_prohead.phindex using "<<<<<<<<<<<<&",
" on matter ",p_prohead.phmatter
end if
let cnt_cursor7 = 0
let cnt_dupe2 = 0
let cnt_100 = 0
open cursor7 using p_prohead.phindex
foreach cursor7 into p_prodetail.*, p_cost1.*, p_batch1.*, p_costdesc1.*
let cnt_cursor7 = cnt_cursor7 + 1
let cnt_100 = cnt_100 + 1
if flag_debug = 1 then
display "Check cost index: ",p_cost1.cindex using "<<<<<<<<<&",
", date: ",p_cost1.cdisbdt using "mm/dd/yy",
", code: ",p_cost1.ccode clipped,
", amt: ",p_cost1.camount using "<<<<,<<<.&&",
" ",p_costdesc1.cddesc clipped
end if
if cnt_100 = 100 then
display "Checked ",cnt_cursor7 using "<<<,<<<,<<&"," costs in proforma: ",p_prohead.phindex using "<<<<<<<<<<<<&"," so far"
let cnt_100 = 0
end if
let cnt_dupe3 = 0
open cursor3 using # Does this cost have any dupes ?
p_cost1.cmatter,
batch1,
batch2,
p_cost1.cindex,
p_cost1.cdisbdt,
p_cost1.cquant,
p_cost1.crate,
p_cost1.camount,
p_cost1.ctk,
p_cost1.cledger,
p_cost1.ccode,
p_cost1.cauth,
p_cost1.cloc,
p_costdesc1.cddesc
foreach cursor3 into p_cost2.*
select count(*)
into cnt_temp1
from dupe_index1
where dupe_index1.cindex = p_cost2.cindex
if cnt_temp1 IS NULL then let cnt_temp1 = 0 end if
if cnt_temp1 = 0 then
let cnt_dupe1 = cnt_dupe1 + 1
let cnt_dupe2 = cnt_dupe2 + 1
let cnt_dupe3 = cnt_dupe3 + 1
if flag_debug = 1 then
display "Duplicate: Batch:",
p_cost1.cbatch using "<<<<<<<<&"," ",
p_cost1.ccode,
p_cost1.cdisbdt using "mm/dd/yy",
p_cost1.camount using "-<<<,<<<,<<<.&&"
end if
output to report list_dupes
(p_prohead.*,p_cost1.*,p_cost2.*,p_timekeep.*)
if cnt_dupe3 = 1 then
insert into dupe_index1 values (p_cost1.cindex)
end if
insert into dupe_index1 values (p_cost2.cindex)
end if
end foreach # << END OF DUPE COSTS
end foreach # << END OF ALL COSTS IN A PROFORMA
if cnt_dupe2 > 0 then
display cnt_dupe2 using "<<<,<<<,<<&"," dups found in proforma ",p_prohead.phindex using "<<<<<<<<<&"
end if
end foreach # << END OF ALL PROFORMAS
--------------------------------------------------------------------------------
finish report list_dupes
drop table dupe_index1
display ""
display cnt_dupe1 using "<<<,<<<,<<&"," dupes found"
end main
###############################################################################
report list_dupes (p_prohead,cost1,cost2,p_timekeep)
define p_prohead record like prohead.*
define cost1 record like cost.*
define cost2 record like cost.*
define p_timekeep record like timekeep.*
define cnt1 integer
define cnt2 integer
OUTPUT
TOP MARGIN 0
BOTTOM MARGIN 0
LEFT MARGIN 0
PAGE LENGTH 60
ORDER EXTERNAL BY p_timekeep.tkinit, p_prohead.phmatter
FORMAT
PAGE HEADER
if FLAG_LAST_ROW = TRUE then
print
column 1, "Final Totals",
column 30, "Duplicate Cost Report",
column 60, TODAY using "mm/dd/yy"
print column 60, TIME,
column 71, "Page: ",pageno using "<<<,<<<,<<&"
skip 1 line
print column 1, " Batch Batch Date Code Amount Description"
skip 1 line
skip 1 line
else
print
column 30, "Duplicate Cost Report",
column 60, TODAY using "mm/dd/yy",
column 71, "Page: ",pageno using "<<<,<<<,<<&"
print
column 1, "Proforma: ",
p_prohead.phindex using "<<<<<<<<<<<<<&",
column 30, "Bill Attorney: ",p_timekeep.tkinit clipped,
column 60, p_timekeep.tkfirst clipped, " ", p_timekeep.tklast
print
column 1, "Proforma date: ",p_prohead.phdate using "mm/dd/yy",
column 30, "Matter: ",p_prohead.phmatter clipped,
column 60, TIME,
column 71, "Status: ",p_prohead.phstatus
skip 1 line
print column 1, " Index Index Date Code Amount Description"
skip 1 line
end if
{
1 2 3 4 5 6
1234567890123456789012345678901234567890123456789012345678901234567890
index index Date Code Amount Description
BBBBBBBB BBBBBBBB mm/dd/yy CCODEEE ----,---.&& DDDDDDDDDDDDDDDDDDD
}
BEFORE GROUP OF p_prohead.phmatter
let cnt1 = 0
SKIP TO TOP OF PAGE
ON EVERY ROW
let cnt2 = 0
let cnt1 = cnt1 + 1
print
column 1, cost1.cindex using "#######&",
column 10, cost2.cindex using "#######&",
column 19, cost1.cdisbdt using "mm/dd/yy",
column 28, cost1.ccode,
column 36, cost1.camount using "----,---.&&";
open cursor5 using cost1.cindex
foreach cursor5 into p_costdesc.*
let cnt2 = cnt2 + 1
print column 49, p_costdesc.cddesc clipped
end foreach
if cnt2 = 0 then
skip 1 line
end if
AFTER GROUP OF p_prohead.phmatter
skip 1 line
print column 10,GROUP count(*) using "<<<,<<<,<<&", " dupes found",
column 36,GROUP sum(cost1.camount) using "----,---.&&"
ON LAST ROW
let flag_last_row = TRUE
SKIP TO TOP OF PAGE
skip 1 line
print "Final Totals"
skip 1 line
print "Starting proforma date: ",phdate1 using "mm/dd/yyyy"
print "or proforma status is not billed"
print ""
print "Starting cost batch: ",batch1 using "<<<<<<<<<&"
print "Ending cost batch: ",batch2 using "<<<<<<<<<&"
print "Cost batch operator: ",batch_bop
skip 1 line
print column 10,count(*) using "<<<,<<<,<<&", " dupes found",
column 36,sum(cost1.camount) using "----,---.&&"
END REPORT
################################################################################