Created
October 25, 2019 17:29
-
-
Save calebwashburn/8969cc491c1c021c9d714c757e1df643 to your computer and use it in GitHub Desktop.
Stored_Procs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ; ^ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ; ^ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ; ^ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 ; ^ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment