return to PRS Technologies website


dupe_cost2.4gl
############################################################################### # # 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 ################################################################################