[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

[sqr-users] Problem Using a Break My Detail Lines Start at Row number 2 instead of row number 1 of my result set.

Hi group, I am new in sqr world I have the following logic but my detail
lines Start at Row number 2 instead of row number 1 of my result set

#define lv_total_headings_line 12
Begin-Setup
 ask database 'Database?'
 Ask BILL_DATE 'Enter Billing Date of Report: format mm/dd/yyyy '
 Use '{database}'

declare-variable
text $lv_company_name 
text $lv_system
text $lv
text $lv_hour
text $lv_date
text $lv_page_desc_number
text $lv_report_name
text $lv_report_title
date $lv_start_date
end-declare

Declare-Layout  ENDLP
Orientation = Landscape
Left-Margin   = 0.0
Max-Lines = 60
Max-Columns = 132
End-Declare

declare-printer lp
type=lp
end-declare
 
declare-report ENDRPT
layout=ENDLP
printer-type=lp
END-DECLARE

begin-sql ON-ERROR=STOP
!Create Final Report Layout Table
create table tempdb..cont_final_rpt
(structure_id smallint null,
scenario_id smallint null,
bdry_id int null,
route_name char(30),
bdry_seq smallint null,
metro_or_isla char(30),
region char(30),
district char(30),
district_short char(6),
cont_id           int         NOT NULL,
cont_name         char(50)    NOT NULL,
cont_hphone       char(13),
cont_mphone       char(13), 
cont_invoice_amt        money     NOT NULL,
cont_bil_amendment_num  smallint  NOT NULL,
cont_bil_contract_num int       NOT NULL)
end-sql
End-Setup

BEGIN-PROCEDURE PRT_ERROR
DISPLAY $SQL-ERROR
STOP
END-PROCEDURE !PRT_ERROR



Begin-Program
        Do Main
        DO final_rpt
        Do No_More
End-program

Begin-Procedure Main 
!Set Variables To Use With Common El Nuevo Dia Headings
let $lv_company_name = 'EL NUEVO DIA'
let $lv_system = 'CIRC2000' 
let $lv_sqr_name =  'PP_DAILY_PPAID'
let $lv_report_title = 'NUMERICO CLIENTES DIARIOS POR DIAS DE SERVICIO'
!Do prompt_user
Begin-sql
insert tempdb..cont_final_rpt
(structure_id,
scenario_id,
bdry_id,
route_name,
bdry_seq,
metro_or_isla,
region,
district,
district_short,
cont_id,
cont_name,
cont_hphone,
cont_mphone,     
cont_invoice_amt,
cont_bil_amendment_num,
cont_bil_contract_num)
select structure_id=0,
 
scenario_id=0,bdry_id=0,route_name='',bdry_seq=0,metro_or_isla='',reion='',d
istrict='',district_short='', 
    contract.contractor_id, 
    cont_name=rtrim(last_nm) + ',' + rtrim(first_nm), 
    con_hphone='',cont_mphone='',
    contractor_trxn.amt,
    contractor_trxn.amendment_num,
    contractor_trxn.contract_num
FROM 
    contractor_trxn, 
    contract, 
    ind_contractor, 
    amendment
WHERE
    contractor_trxn.contract_num = contract.contract_num
  AND 
    contract.contractor_id = ind_contractor.contractor_id
  AND 
    contractor_trxn.contract_num = amendment.contract_num
  AND 
    contractor_trxn.amendment_num = amendment.amendment_num
  AND 
    trxn_type_cd = 'CBILL' 
AND 
    trxn_dt >= '10/27/2002' AND 
    trxn_dt <= '10/27/2002' 
!AND 
!    ind_contractor.CONTRACTOR_ID=15746 !for test
end-sql
    Do Update_Hphone
    Do Update_Mphone
    Do Update_Zone_District
end-procedure

!   Update Contractor Home Phone
begin-procedure Update_Hphone
begin-sql
update tempdb..cont_final_rpt
set cont_hphone= '(' || substring(cp.area_code,1,3) || ')' || 
    substring(cp.phone_num,1,3) || '-' ||   substring(cp.phone_num,4,4 )  
from contractor_phone cp, tempdb..cont_final_rpt c
where c.cont_id = cp.contractor_id 
    and cp.phone_type_cd = 'HOME'
end-sql
end-procedure

!   Update Contractror Mobile Phone
begin-procedure Update_Mphone
begin-sql
update tempdb..cont_final_rpt
set cont_mphone= '(' || substring(cp.area_code,1,3) || ')' || 
    substring(cp.phone_num,1,3) || '-' ||   substring(cp.phone_num,4,4 )  
from contractor_phone cp, tempdb..cont_final_rpt c
where c.cont_id = cp.contractor_id 
    and cp.phone_type_cd = 'MOBILE' 
end-sql
end-procedure

begin-procedure Update_Zone_District
begin-sql
!   Update bdry_id From amendment Table
update tempdb..cont_final_rpt
set  a.structure_id=b.structure_id,
    a.bdry_id=b.bdry_id
from  tempdb..cont_final_rpt a, amendment b
where a.cont_bil_contract_num = b.contract_num 
    and a.cont_bil_amendment_num = b.amendment_num  

!   Update Distribution Structure
update tempdb..cont_final_rpt
set a.scenario_id=b.scenario_id,
    a.route_name=b.route_name,
    a.bdry_seq=b.bdry_seq,
    a.metro_or_isla=b.metro_or_isla,
    a.region=b.region,
    a.district=b.district,
    a.district_short=b.district_short
from  tempdb..cont_final_rpt a, vw_bdry_levels b 
where a.bdry_id = b.bdry_id
end-sql
end-procedure


begin-procedure final_rpt
begin-select
cont_name &c_name () on-break print=never PROCEDURE=cont_sub_total
save=$old_cont
region &region 
district_short &ds  
cont_id  &cont_id
cont_invoice_amt &c_inv_amt
cont_hphone &cont_hphone
cont_mphone &cont_mphone
cont_bil_amendment_num &cban 
cont_bil_contract_num  &cbcn 
    let #cont_tot_inv =  #cont_tot_inv + &c_inv_amt
from tempdb..cont_final_rpt
WHERE region = 'AREA 24' !FOR TEST
Order By cont_name
end-select
End-Procedure     

#IF {sqr-platform} = 'UNIX' 
#include
'/apps/usr/circ/bin/endsqr/end_include/end_headings/lp_headings.txt'
#ELSE
#include 'F:\Departamentos\Sistemas de
Informacion\Desarrollo\Circ2000\apps\usr\circ\bin\endsqr\end_include\end_hea
dings\lp_headings.txt'
#end-if

begin-procedure cont_sub_total
    print &cont_id (1,1) edit 099999999na
    print &c_inv_amt (,12) edit 99,999.99   !For Test
!   Imprimir Total Para El Contractor
    print #cont_tot_inv (,24) edit 99,999.99
!   Total Agrupado para el contrator guardarlo para proximo nivel
    let #lv_dist_total = #lv_dist_total + #cont_tot_inv
!   Clean up Contractor Sub Total Field
    let #cont_tot_inv = 0
!    Print '_' (,24,5) fill
!    Print '_' (,30,5) fill
    Print '_' (,36,5) fill
    Print '_' (,42,5) fill
    Print '_' (,48,7) fill
    Print '#' (,56)
    Print '_' (,57,7) fill 
    Print '#' (,64)
    Print '_' (,65,7) fill
    Print '_' (,73,7) fill 
    Print '_' (,81,6) fill 
    Print '_' (,88,6) fill 
    Print '_' (,95,7) fill 
    Print '_' (,103,6) fill 
    Print '_' (,110,6) fill 
    Print '_' (,117,7) fill 
    Print '_' (,125,7) fill 
    print &c_name (+1,3)  
    print 'Home Phone: ' (+1,3) 
    print &cont_hphone   (,14)
    Print '$' (,56)
    Print '_' (,57,7) fill 
    Print '$' (,64)
    Print '_' (,65,7) fill
    print 'Mobile Phone: ' (+1,3) 
    print &cont_mphone   (,16)   
!For Debug Purposes
    print 'Region Es: ' (,30) 
    print &region (,41)
    print 'Distrito Es: ' (,63)
    print &ds (,76)
    print &cban (,95)
    print &cbcn (,105)
    PRINT '-' (+1,1,132) fill 
    Next-Listing NEED=4
end-procedure

begin-procedure Page_Headings
 print 'Zone........: ' (6,1)
 print &region (,16)
 print 'District ...: ' (+2,1)
 print &ds (,16)
 Print 'CONTRACTOR' (+2,1)  
 Print 'INVOICED' (,15)   
 Print 'BALANCE' (,48)  
 Print 'CRED.' (,58)   
 Print 'CRED.' (,65) 
 Print 'PEND.' (,73)   
 Print 'PAGO' (,89)
 Print 'PLAN' (,111)  
 Print 'PAGO' (,111)  
 Print 'BALANCE' (,116) 
 Print 'BALANCE ' (,124) 
 Print 'NAME' (+1,3)  
 Print 'AMOUNT' (,16)   
 Print 'MIER' (,24)   
 Print 'JUEV' (,30)   
 Print 'VIER' (,36)   
 Print 'SAB' (,42)  
 Print 'PEND.' (,49) 
 Print 'DIARIO' (,57)   
 Print 'DOMINGO' (,65)    
 Print 'ACLARAR' (,73)   
 Print 'PBM' (,83)   
 Print 'SUSC.' (,89)   
 Print 'FIANZA' (,95)   
 Print 'QUERELLA' (,102)      
 Print 'MIERC. ' (,117)   
 Print 'FINAL' (,125) 
PRINT '-' (+1,1,132) fill 
End-Procedure

Begin-Procedure No_More
        NEW-PAGE
        Print 'No More Data For This Report'  (+2,15) Bold
End-Procedure



Thanks in advance cloperena.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users