Stored_Procs

SPAlterListEstimateByCreatedOn.sql

SET TERM ^ ;

ALTER PROCEDURE LIST_ESTIMATE_BY_CREATED_ON (
    DATE1 Date,
    DATE2 Date )
RETURNS (
    "Estimate ID" Varchar(40) CHARACTER SET NONE,
    "Customer" Varchar(72) CHARACTER SET NONE,
    "Written On" Date,
    RO_ID Integer,
    "Follow-Up (Phone)" Char(1) CHARACTER SET NONE,
    "Follow-Up (Letter)" Char(1) CHARACTER SET NONE,
    "Color" Varchar(20) CHARACTER SET NONE,
    "Year" Char(4) CHARACTER SET NONE,
    "Make" Varchar(30) CHARACTER SET NONE,
    "Model" Varchar(50) CHARACTER SET NONE,
    "Notes" Char(1) CHARACTER SET NONE,
    "Written by" Varchar(35) CHARACTER SET NONE,
    "Amount" Decimal(15,2),
    "Insurance" Varchar(35) CHARACTER SET NONE,
    ID Integer )
AS
declare variable OWNR_CO_NM VARCHAR(35);
BEGIN
  FOR
   select
    (EST_HEADER.est_system || '-' || EST_HEADER.estfile_id),
    EST_HEADER.ownr_ln || ', ' || EST_HEADER.ownr_fn,
    cast(est_header.created_on as date),
    EST_HEADER.RO_ID,
    EST_HEADER.follow_up_ind,
    EST_HEADER.follow_up_letter_sent_ind,
    EST_VEHICLE.v_color as "Color",
    cast(EST_VEHICLE.v_model_yr as char(4)),
    EST_VEHICLE.v_makedesc,
    EST_VEHICLE.v_model,
    EST_HEADER.notes_ind,
    EST_HEADER.estimator_name,
    EST_HEADER.g_ttl_amt,
    EST_INSURANCE.ins_co_nm,
    EST_HEADER.ID,
    EST_HEADER.ownr_co_nm
   from EST_HEADER
    INNER JOIN EST_VEHICLE ON (EST_HEADER.ID = EST_VEHICLE.EST_HEADER_ID)
    LEFT OUTER JOIN EST_INSURANCE ON (EST_HEADER.ID = EST_INSURANCE.EST_HEADER_ID)
    where (
           cast(est_header.created_on as date) between :DATE1 and :DATE2
          )
    order by est_header.created_on, upper(EST_HEADER.ownr_ln), upper(EST_HEADER.ownr_fn)
    INTO :"Estimate ID",
         :"Customer",
         :"Written On",
         :RO_ID,
         :"Follow-Up (Phone)",
         :"Follow-Up (Letter)",
         :"Color",
         :"Year",
         :"Make",
         :"Model",
         :"Notes",
         :"Written by",
         :"Amount",
         :"Insurance",
         :ID,
         :OWNR_CO_NM
  DO
  BEGIN
    if ("Customer" = ',') then
     "Customer" = OWNR_CO_NM;

    SUSPEND;
  END
END^
SET TERM ; ^

SPAlterListEstimateDays.sql

SET TERM ^ ;
ALTER PROCEDURE LIST_ESTIMATE_DAYS (
    INP_DAYS Integer )
RETURNS (
    "Estimate ID" Varchar(40),
    "Customer" Varchar(72),
    "Written On" Date,
    RO_ID Integer,
    "Follow-Up (Phone)" Char(1),
    "Follow-Up (Letter)" Char(1),
    "Color" Varchar(20),
    "Year" Char(4),
    "Make" Varchar(30),
    "Model" Varchar(50),
    "Notes" Char(1),
    "Written by" Varchar(35),
    "Amount" Decimal(15,2),
    "Insurance" Varchar(35),
    ID Integer )
AS
declare variable ownr_co_nm varchar(35);
BEGIN
  FOR
   select
    (EST_HEADER.est_system || '-' || EST_HEADER.estfile_id),
    EST_HEADER.ownr_ln || ', ' || EST_HEADER.ownr_fn,
    cast(est_header.created_on as date),
    EST_HEADER.RO_ID,
    EST_HEADER.follow_up_ind,
    EST_HEADER.follow_up_letter_sent_ind,
    EST_VEHICLE.v_color as "Color",
    cast(EST_VEHICLE.v_model_yr as char(4)),
    EST_VEHICLE.v_makedesc,
    EST_VEHICLE.v_model,
    EST_HEADER.notes_ind,
    EST_HEADER.estimator_name,
    EST_HEADER.g_ttl_amt,
    EST_INSURANCE.ins_co_nm,
    EST_HEADER.ID,
    est_header.ownr_co_nm
   from EST_HEADER
    INNER JOIN EST_VEHICLE ON (EST_HEADER.ID = EST_VEHICLE.EST_HEADER_ID)
    LEFT OUTER JOIN EST_INSURANCE ON (EST_HEADER.ID = EST_INSURANCE.EST_HEADER_ID)
    where (
           (cast(est_header.created_on as date) between (current_date - :inp_days) and current_date)
          )
    order by upper(EST_HEADER.ownr_ln), upper(EST_HEADER.ownr_fn)
    INTO :"Estimate ID",
         :"Customer",
         :"Written On",
         :RO_ID,
         :"Follow-Up (Phone)",
         :"Follow-Up (Letter)",
         :"Color",
         :"Year",
         :"Make",
         :"Model",
         :"Notes",
         :"Written by",
         :"Amount",
         :"Insurance",
         :ID,
         :ownr_co_nm
  DO
  BEGIN
    if ("Customer" = ',') then
     "Customer" = OWNR_CO_NM;

    SUSPEND;
  END
END^
SET TERM ; ^

SPAlterListEstimateDaysNotSold.sql

SET TERM ^ ;
ALTER PROCEDURE LIST_ESTIMATE_DAYS_NOT_SOLD (
    INP_DAYS Smallint )
RETURNS (
    "Estimate ID" Varchar(40),
    "Customer" Varchar(72),
    "Written On" Date,
    RO_ID Integer,
    "Follow-Up (Phone)" Char(1),
    "Follow-Up (Letter)" Char(1),
    "Color" Varchar(20),
    "Year" Char(4),
    "Make" Varchar(30),
    "Model" Varchar(50),
    "Notes" Char(1),
    "Written by" Varchar(35),
    "Amount" Decimal(15,2),
    "Insurance" Varchar(35),
    ID Integer )
AS
declare variable ownr_co_nm varchar(35);
BEGIN
  FOR
   select
    (EST_HEADER.est_system || '-' || EST_HEADER.estfile_id),
    EST_HEADER.ownr_ln || ', ' || EST_HEADER.ownr_fn,
    cast(est_header.created_on as date),
    EST_HEADER.RO_ID,
    EST_HEADER.follow_up_ind,
    EST_HEADER.follow_up_letter_sent_ind,
    EST_VEHICLE.v_color as "Color",
    cast(EST_VEHICLE.v_model_yr as char(4)),
    EST_VEHICLE.v_makedesc,
    EST_VEHICLE.v_model,
    EST_HEADER.notes_ind,
    EST_HEADER.estimator_name,
    EST_HEADER.g_ttl_amt,
    EST_INSURANCE.ins_co_nm,
    EST_HEADER.ID,
    est_header.ownr_co_nm
   from EST_HEADER
    INNER JOIN EST_VEHICLE ON (EST_HEADER.ID = EST_VEHICLE.EST_HEADER_ID)
    LEFT OUTER JOIN EST_INSURANCE ON (EST_HEADER.ID = EST_INSURANCE.EST_HEADER_ID)
    where (
           (cast(est_header.created_on as date) between  (current_date - :inp_days) and current_date) AND
           (EST_HEADER.RO_ID IS NULL)
          )
    order by upper(EST_HEADER.ownr_ln),  upper(EST_HEADER.ownr_fn)
    INTO :"Estimate ID",
         :"Customer",
         :"Written On",
         :RO_ID,
         :"Follow-Up (Phone)",
         :"Follow-Up (Letter)",
         :"Color",
         :"Year",
         :"Make",
         :"Model",
         :"Notes",
         :"Written by",
         :"Amount",
         :"Insurance",
         :ID,
         :ownr_co_nm
  DO
  BEGIN
    if ("Customer" = ',') then
     "Customer" = OWNR_CO_NM;

    SUSPEND;
  END
END^
SET TERM ; ^

SPAlterListEstimateROCreate.sql

SET TERM ^ ;
ALTER PROCEDURE LIST_ESTIMATE_RO_CREATE
RETURNS (
    "Customer" Varchar(72),
    "City" Varchar(20),
    "Color" Varchar(20),
    "Year" Char(4),
    "Make" Varchar(30),
    "Model" Varchar(50),
    "Written by" Varchar(35),
    "Estimate ID" Varchar(40),
    ID Integer )
AS
declare variable ownr_co_nm varchar(35);
BEGIN
  FOR
    select
    EST_HEADER.ownr_ln || ', ' || EST_HEADER.ownr_fn as "Customer",
    EST_HEADER.ownr_city as "City",
    EST_VEHICLE.v_color as "Color",
    cast(EST_VEHICLE.v_model_yr as char(4)) as "Year",
    EST_VEHICLE.v_makedesc as "Make",
    EST_VEHICLE.v_model as "Model",
    EST_HEADER.estimator_name as "Written by",
    (EST_HEADER.est_system || '-' || EST_HEADER.estfile_id) as "Estimate ID",
    EST_HEADER.id as "ID",
    est_header.ownr_co_nm
   from EST_HEADER, EST_VEHICLE
    where EST_HEADER.id = EST_VEHICLE.est_header_id and
      cast(est_header.created_on as date) between current_date - 3 and current_date
    order by upper(EST_HEADER.ownr_ln), upper(EST_HEADER.ownr_fn)
    INTO :"Customer",
         :"City",
         :"Color",
         :"Year",
         :"Make",
         :"Model",
         :"Written by",
         :"Estimate ID",
         :ID,
         :ownr_co_nm
  DO
  BEGIN
   if ("Customer" = ',') then
     "Customer" = OWNR_CO_NM;
    SUSPEND;
  END
END^
SET TERM ; ^