GTMIS is written in Object Pascal and constructed using design tools from the Delphi product. Built-in controls allow GTMIS to process jobs unattended, with prerequisite and post-processing job flow to ensure data integrity. A sequence of commands initiates various stored procedures that verify, extract, and load data into both system tables and user tables.
The data processed includes:
·
Money Market transactions
·
Foreign Exchange transactions
·
Global Risk System
transactions
·
Internal and External Swap
transactions
Many of the data tables that GTMIS updates are in turn used to refresh other programs and to prepare them for the next series of program methods. The command line methods within each stored procedure are run in specific time slots, in an interactive sequence designed for the speed and efficiency of the program interfaces. Most of these updates and methods are gathered and processed overnight during the GTMIS Batch Production Process, resulting in new data processed and exported into Excel spreadsheets that feed the GTMIS database programs.
Initially, GTMIS constructs the user’s request in any one of several Delphi formulas that operate at two method levels. The first level is the SQL formula for retrieving basic data from GAP-related deals, and may include one or more conditional requirements. A condition usually begins with if, and alters the basic formula to accommodate certain criteria, such as a particular datetime, numerical figure or user-defined specification. When a condition is met, one or more datafields and table functions will be added to or suppressed from the basic query.
Where SQL command strings are located in single quotation marks, double quotations refer to the Delphi commands that SQL interprets. Double quotations are used to set apart from the basic formula any combination of user-defined specifications, Delphi coded terms, and user-friendly aliases that rename SQL datafields. A user-defined specification refers to selections in a datafield, such as the user’s choice of portfolios, currencies and reference numbers.
After the end user selects the categories for data and submits a request through any of the GTMIS database programs, the query is translated into one of a variety of formulas written in Delphi-coded language. This formula is run through a filtering process that converts the codes into Select Statements designed for the SQL Server to read. SQL compiles a dataset from any combination of tables and columns, corresponding to the user’s request. Next, this fresh dataset is sent through a reverse filtering process that ends when the information is displayed on the user’s screen.
Initially, the background process constructs the user’s request in a Delphi script that operates at two levels. The first level is the basic formula for retrieving general data from any of GTMIS database programs, tables and columns. The second level is determined by the following factors:
·
User-defined specifications.
·
Conditional Requests.
·
Special Situations for new
and unique data.
User-defined specifications are invoked when the end user selects specific categories for retrieval of data. A user-defined specification may be a selected datafield, such Portfolio Type, or a selection within a datafield, such as a particular portfolio ID, currency or reference number. When the user prompts GTMIS to display the Sales Database Program, the settings default to those applied during its previous access. The user may select any combination of specific categories, such as:
Þ
Traders, Portfolios, and Deals
Þ
Types of Traders, Portfolios, and Deals
Þ
Date times, Exchange Rates, and Simulation Runs.
Most requests include user-defined specifications written in Delphi and imbedded in double quotes in the basic formula for retrieving data. In the second level of the GTMIS background process, they enable the end user to customize the dataset.
Some requests in the GTMIS background process include one or more conditions that alter the basic Delphi formula. Conditions are stipulations that instruct SQL to add or to suppress certain datafields in the basic formula. Located at the beginning of a line or string, a condition is set apart from the basic formula with one of two words beginning a command line or string:
1.
“CASE,” referring to one in a variety of possible user-defined
specifications
2.
“IF,” referring to whether or not certain criteria are being met,
such as index rates, date times, deal status, as well as a user specification.
Conditions are invoked in tandem both with the user-defined specifications and with the nature of data to be retrieved. Whether the user selects “ALL” or a specific portfolio ID number may determine how a condition is met. In the second level of the GTMIS background process, conditions instruct the SQL Server to compile the data corresponding to the end user’s specific needs, and to consolidate it accordingly.
Some data requests need GTMIS to alter its basic Delphi formulas to fit any combination of special situations, including the following:
Þ
New or recent trades.
Þ
Sudden shifts or fluctuations in exchange rates and profit
margins.
Þ
Specific exchange rates, index numbers, date times,
simulation runs, and deal status.
Þ
Data fields that are offset when combined.
In the second level of the GTMIS background process, the codes for such situations instruct the SQL Server to adjust its data request to correspond to various circumstances.
Consider the following example of a SQL Statement taken from the OBS P/L program:
function
TObsPL.GetDataset(bOfficial: boolean): TDataSet;
var
date1: TDateTime;
Q:
TQuery;
sp:
TStoredProc;
begin
if bOfficial then { retrieve the data from
the historical table }
begin
Q := TQuery.Create(MainForm);
Q.DatabaseName := APP_DBNAME_GDSTREAS;
RunDate := CalForm.CalGrid.CalendarDate;
date1 := RunDate;
OldDate := LastGoodBusinessDay(date1);
Q.SQL.Add('select * from obs_pl where aodate = "' + DateToStr(RunDate) +
'"');
Q.Open;
result := Q;
end
else { retrieve the data by running the stored procedure }
begin
sp := TStoredProc.Create(MainForm);
sp.DatabaseName := APP_DBNAME_GDS;
sp.StoredProcName := APP_DBSP_FXMTMREP;
RunDate := Date;
date1 := RunDate;
OldDate := LastGoodBusinessDay(date1);
sp.ParamBindMode := pbByNumber;
sp.Params.CreateParam(ftDateTime,
'@eod_date', ptInput);
sp.Params.CreateParam(ftInteger,
'@runtype', ptInput);
sp.Params.Items[0].AsDateTime := RunDate;
sp.Params.Items[1].AsInteger := 0;
sp.Open;
result := sp;
end;
end;
When the user requests from the OBS_PL table a list of deals with a specific asodate, the query is sent to SQL in Delphi coding.
Q.SQL.Add('select * from obs_pl where aodate = "' + DateToStr(RunDate) + '"'
Next, the query passes through a filtering process that converts the query into a command that SQL can run. The appropriate RunDate string is applied accordingly, for example:
'select * from obs_pl where aodate = "' + ‘11/25/96’ + '"'
SQL then converts the query into a third command, one designed to compile the requested data from its database:
select *
from obs_pl
where aodate = “11/25/96”
When converted to SQL, the statement’s single quotation marks are omitted. This SQL statement will create a table with 500 rows affected, under the following column headings, beginning with Pfolio_name:
|
¨
pfolio_name |
¨
tran_num |
|
¨
reference |
¨
payrec |
|
¨
fixfloat |
¨
yield_basis_name |
|
¨
instruments_name |
¨
currency_name |
|
¨
proj_index_tenor |
¨
trade_date |
|
¨
start_date |
¨
end_date |
|
¨
notional |
¨
rate |
|
¨
vfc_amount |
¨
npv_amount |
|
¨
aodate |
¨
curnotnl |
|
¨
float_spread |
¨
counterparty |
After the data is compiled, SQL sends it through a reverse filtering process that converts the SQL codes into Delphi. Once converted to a language that Delphi can read, the SQL data is then converted into another copde that GTMIS can display to the end user.
The Gds2gtmis.bat batch file is the primary means by which GTMIS refreshes its data tables, and consists of 8 stored procedures. The first 6 are GDS-generated and are used to download the new GDS data to temporary tables where GTMIS has access. Because GTMIS cannot extract the GDS files directly, the first 6 procedures download the new data to temporary files where GTMIS has access. The final 2 procedures of the batch run consist of GTMIS extracting the new data, then parsing distributing it to specified OBS P/L tables.
Following are 2 GTMIS-generated stored procedures:
1.
gds_treas..fxGetDailySpotRates
2.
gds_treas..OBS_PL_Month2Day
This procedure consists of deleting the current DailySpotRates, creating a new version of the table, with fresh CCY data from the Rates table in the FXProd directory. This process is a preparatory stage for the batch run’s final procedure, gds_treas..OBS_PL_Month2Day, which does the downloading of new GDS-generated data.
Following are the contents of gds_treas..fxGetDailySpotRates:
create procedure fxGetDailySpotRates(@date datetime)
as
delete DailySpotRates
insert into DailySpotRates
select CCY, (SRATE_ASK +
SRATE_BID) / 2 'SPOT_RATE'
from fxprod..RATES
where fxprod..RATES.ASOFDATE
= @date AND
fxprod..RATES.SDATE = (SELECT
MIN(SDATE) FROM fxprod..RATES WHERE ASOFDATE = @date)
Most of the GDS Reports are based on successful EOD (End of Date) runs. The user enters a UserID and its password, and often a date parameter. To generate the GDS Reports, GTMIS runs an executable program off two files:
1.
gdsrep.exe
2.
gdsutl.dll
GTMIS extracts the GDS data from a variety of tables, primarily from pfolio_result_data which captures the particular EOD run. The result generates the following seven reports:
¨
Accrual.bat
¨
Blotter.bat
¨
Cash.bat
¨
Fra.bat
¨
Hcash.bat
¨
M2M.bat
¨
Pending.bat
The SQL name for this file is accrual.sql. For this report, the following criteria must be met.
Þ
Input includes UserID, password and date
Þ
Run Type
¨
19 = LTD
¨
20 = Daily
¨
51 = MTD
The SQL name for this file is tran2.sql. For this report, the input includes a UserID and password. Parameter is supplied in the ini file of 3 3, where 3 = validate state.
The SQL name for this file is cash2.sql. For this report, the following criteria must be met.
Þ
Input includes UserID, password and date
Þ
Run Type
¨
10 = Historical Cash
¨
11 = Current Cash
The SQL name for this file is fra2.sql. For this report, the following criteria must be met.
Þ
Input includes UserID, password and date
Þ
Run Type
¨
15 = NPV
The SQL name for this file is hcash2.sql. In this report, historical cash captures data from ab_tran and ab_tran_event. This is due to the fact the matured deals that get archived to ab_tran_event with a pymt_type = 36(historic cash).
For this report,
Þ
Input includes UserID, password, and date
The SQL name for this file is mtm2.sql. For this report, the following criteria must be met.
Þ
Input includes UserID, password and date
Þ
Run type
¨
0 = VFC
¨
15 = NPV
The SQL name for this file is tran2.sql.
o
For this report,
Þ
input includes a UserID and password.
o
Parameter is supplied in,
Þ
the ini file of 1 2 (1 and 2)
¨
where 1 = Pending
¨
and 2 = New
This section concerns the background process used to construct SQL Select Statements. After the end user selects the categories for data and submits this request through any of the GTMIS database programs, GTMIS translates the query into one of a variety of formulas written in Delphi-coded language. This formula is run through a filtering process that converts the codes into a Select Statement designed for the SQL Server to read. SQL compiles a dataset from any combination of tables and columns, corresponding to the user’s request. Next, this fresh dataset is sent through a reverse filtering process that ends when the information is displayed on the user’s screen.
The following formulas are used to build SQL Select Statements in OBS P/L.
Function: TObsPL.GetDataset(bOfficial:
boolean): TDataSet
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if
bOfficial then
Q.SQL.Add('select * from obs_pl where aodate =
"' + DateToStr(RunDate) + '"');
Q.Open;
result := Q;
end
else { retrieve the data by running the stored procedure }
begin
sp := TStoredProc.Create(MainForm);
sp.DatabaseName := APP_DBNAME_GDS;
sp.StoredProcName := APP_DBSP_FXMTMREP;
RunDate := Date;
date1 := RunDate;
OldDate := LastGoodBusinessDay(date1);
sp.ParamBindMode := pbByNumber;
sp.Params.CreateParam(ftDateTime,
'@eod_date', ptInput);
sp.Params.CreateParam(ftInteger,
'@runtype', ptInput);
sp.Params.Items[0].AsDateTime := RunDate;
sp.Params.Items[1].AsInteger := 0;
sp.Open;
result := sp;
end;
When the user requests from the OBS_PL table a list of deals with a specific asodate, the query is sent to SQL in Delphi coding.
Q.SQL.Add('select * from obs_pl where aodate = "' + DateToStr(RunDate) + '"'
Next, the query passes through a filtering process that converts the query into a command that SQL can run. The appropriate RunDate string is applied accordingly, for example:
'select * from obs_pl where aodate = "' + ‘11/25/96’ + '"'
SQL then converts the query into a third command, one designed to compile the requested data from its database:
select * from obs_pl where aodate = “11/25/96”
When converted to SQL, the statement’s single quotation marks are ommitted. This SQL statement will create a table with 500 rows affected, under the following column headings, beginning with Pfolio_name:
|
¨
pfolio_name |
¨
tran_num |
|
¨
reference |
¨
payrec |
|
¨
fixfloat |
¨
yield_basis_name |
|
¨
instruments_name |
¨
currency_name |
|
¨
proj_index_tenor |
¨
trade_date |
|
¨
start_date |
¨
end_date |
|
¨
notional |
¨
rate |
|
¨
vfc_amount |
¨
npv_amount |
|
¨
aodate |
¨
curnotnl |
|
¨
float_spread |
¨
counterparty |
When the requested data is sent to the user, the reverse filtering process interprets the codes. The new SQL statement converts to a code that Delphi can read, and this code converts to another code that will carry out the display of data for the user.
Function: TObsPL.GetSecondaryDataset
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: case
iIndex of 0 or 1
Q.SQL.Add('select * from m2d_npv4 ');
Q.SQL.Add('where aodate >= "' +
DateToStr(mDate) + '" ');
Q.SQL.Add('and aodate <= "' +
DateToStr(RunDate) + '" ');
case iIndex of
0: Q.SQL.Add('and pfolio_name like
"%' + OBSPL_STR_SEEKSWAP + '%" ');
1: Q.SQL.Add('and pfolio_name like
"%' + OBSPL_STR_SEEKFRA + '%" ');
end;
Q.SQL.Add('order by pfolio_name, aodate');
Q.Open;
Here, when the user requests a list of deals from the m2d_npv4 table that span two specific asodates, GTMIS will send the query in the following Delphi coding.
Q.SQL.Add('select * from m2d_npv4 ');
Q.SQL.Add('where aodate >= "' + DateToStr(mDate) + '" ');
Q.SQL.Add('and aodate <= "' + DateToStr(RunDate) + '" ');
Next, the query passes through a SQL filtering process that converts it into a SQL command with the conditional asodate strings, for example:
'select * from m2d_npv4 '
'where aodate >= "' + 11/25/96 + '" '
'and aodate <= "' + 11/25/96 + '" '
SQL then converts the query into a third command, one designed to compile the requested information from its database:
select *
from obs_pl
where aodate = “11/25/97”
select *
from m2d_npv4
where aodate >= "11/25/96” and aodate <= "11/25/96”
Keeping with the above example, the requested data will be arranged under the following column headers, beginning with Pfolio_name:
¨
pfolio_name
¨
npv_total
¨
ex_rate
¨
ccy_adj
¨
usd_adj
¨
mat_cash
¨
val_hcash
¨
val_cash
¨
ytd_npv
¨
aodate
When the requested data is sent to the user, the same filtering process converts the SQL codes back to Delphi, so that GTMIS can display the datatable on the user’screen.
Procedure:
TObsPL.CreateAuxTables
DatabaseName: APP_DBNAME_GDS
GTMIS Source File: Fxutil.pas
Conditions: if not
MainForm.OfficialBtn.Down then
qryExRatesRule.SQL.Add('select SWIFT_CODE, QUOTED_PER_DOLLAR
from CCY_INFO');
qryExRatesRule.Open;
{
// create lookup table for historical cash
spMatHCash :=
TStoredProc.Create(MainForm);
spMatHCash.DatabaseName := APP_DBNAME_GDS;
spMatHCash.StoredProcName :=
APP_DBSP_FXHCASHREP_DET;
spMatHCash.ParamBindMode := pbByNumber;
spMatHCash.Params.CreateParam(ftDateTime,
'@eod_date', ptInput);
spMatHCash.Params.Items[0].AsDateTime :=
OldDate;
spMatHCash.Open;
// create lookup table for historical cash
spValCash := TStoredProc.Create(MainForm);
spValCash.DatabaseName := APP_DBNAME_GDS;
spValCash.StoredProcName :=
APP_DBSP_FXCASHREP_DET;
spValCash.ParamBindMode := pbByNumber;
spValCash.Params.CreateParam(ftDateTime,
'@eod_date', ptInput);
spValCash.Params.Items[0].AsDateTime :=
OldDate;
spValCash.Open;
The following SQL Statement results:
select SWIFT_CODE, QUOTED_PER_DOLLAR
from CCY_INFO
The two columns SWIFT_CODE and QUOTED_PER_DOLLAR will display all the rows affected.
Function: TObsPL.GetExchangeRate
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if result
= 0 or 1 then
qryExchangeRate.SQL.Add('select spot_rate from DailySpotRates
');
qryExchangeRate.SQL.Add('where ccy = "'
+ VarToStr(vCCY) + '" ');
qryExchangeRate.Open;
result
:= qryExchangeRate.Fields[0].Value;
if result = 0 then
result := 1;
if qryExRatesRule.Locate('SWIFT_CODE', vCCY,
[loCaseInsensitive]) then
if qryExRatesRule.Fields[1].Value = 1 then
result := 1 /
qryExchangeRate.Fields[0].Value;
With AUD as the user-specified currency, the following SQL Statement results:
select spot_rate
from DailySpotRates
where ccy = "AUD"
Procedure:
TObsPL.GetPrevNpvBase
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: None
qryPrevNpv.SQL.Add('select npv_total from m2d_npv4 ');
qryPrevNpv.SQL.Add('where pfolio_name =
"' + sPortfolioID + '" ');
qryPrevNpv.SQL.Add('and aodate = "' +
DateToStr(NpvDate) + '" ');
qryPrevNpv.Open;
select npv_total
from m2d_npv4
where pfolio_name = "CORER" and aodate = "11/15/96"
Procedure:
TObsPL.GetPrevNpvUsd
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: None
qryPrevNpv.SQL.Add('select ytd_npv from m2d_npv4 ');
qryPrevNpv.SQL.Add('where pfolio_name =
"' + sPortfolioID + '" ');
qryPrevNpv.SQL.Add('and aodate = "' + DateToStr(NpvDate)
+ '" ');
qryPrevNpv.Open;
With the user-specified choices of NYDH1 for portfolio and 11/11/96 for AsOfDate, the following SQL statement results:
select ytd_npv
from m2d_npv4
where pfolio_name = "NYDH1" and aodate = "11/11/96"
Procedure:
TObsPL.GetPrevNpvOfficial
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if
vNpvBaseDiff = null then
if vNpvUsdDiff = null then
Q.SQL.Add('select npv_total, ytd_npv from m2d_npv4 ');
Q.SQL.Add('where pfolio_name = "' +
sPortfolioID + '" ');
Q.SQL.Add('and aodate = "' +
DateToStr(LastGoodBusinessDay(BDate)) + '" ');
Q.Open;
vNpvBaseDiff
:= Q.Fields[0].Value;
if vNpvBaseDiff = null then
vNpvBaseDiff := 0;
vNpvUsdDiff
:= Q.Fields[1].Value;
if vNpvUsdDiff = null then
vNpvUsdDiff := 0;
The following SQL statement converts the above, using the examples NYDH1 for portfolio and 11/11/96 for the AsOfDate:
select npv_total, ytd_npv
from m2d_npv4
where pfolio_name = "NYJR1” and aodate = "11/15/96"
Procedure:
TObsPL.GetMonthToDayOfficial
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: None
qrySecondary.SQL.Add('select max(aodate) from m2d_npv4 ');
qrySecondary.SQL.Add('where datepart(month,
aodate) = ');
qrySecondary.SQL.Add('datepart(month,
"' + DateToStr(BDate) + '") - 1');
qrySecondary.Open;
sOldestDate :=
DateToStr(qrySecondary.Fields[0].AsDateTime);
qrySecondary.Close;
qrySecondary.Free;
qryMonth2Day := TQuery.Create(MainForm);
qryMonth2Day.DatabaseName :=
APP_DBNAME_GDSTREAS;
qryMonth2Day.SQL.Add('select npv_total, ytd_npv from m2d_npv4 ');
qryMonth2Day.SQL.Add('where pfolio_name =
"' + sPortfolioID + '" ');
qryMonth2Day.SQL.Add('and aodate <=
"' + DateToStr(BDate) + '" ');
qryMonth2Day.SQL.Add('and aodate >=
"' + sOldestDate + '" ');
qryMonth2Day.SQL.Add('order by pfolio_name,
aodate');
qryMonth2Day.Open;
qryMonth2Day.First;
repeat
vValue1 := qryMonth2Day.Fields[1].Value;
vValue2 := qryMonth2Day.Fields[0].Value;
qryMonth2Day.Next;
vValue1 := qryMonth2Day.Fields[1].Value -
vValue1;
vValue2 := qryMonth2Day.Fields[0].Value -
vValue2;
vNpvUsdMtd := vNpvUsdMtd + vValue1;
vNpvBaseMtd := vNpvBaseMtd + vValue2;
until qryMonth2Day.EOF;
qryMonth2Day.Close;
This SQL statement converts the two select statements above, using the example 11/15/96 for AsOfDate in the first select, and the examples NYAI1 for portfolio and 11/15/96 and 2/7/97 for the AsOfDates in the second select:
select max(aodate)
from m2d_npv4
where datepart(month, aodate) = datepart(month, "11/15/96") - 1
select npv_total, ytd_npv
from m2d_npv4
where pfolio_name = "NYAI1" and aodate <= "2/7/97" and aodate >= "11/15/96"
order by pfolio_name, aodate
When the query is executed, SQL responds as follows:
---------------------------
(null)
(1 row(s) affected)
npv_total ytd_npv
------------------------
------------------------
(0 row(s) affected)
Procedure:
TObsPL.GetMonthToDay
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: None
qrySecondary.SQL.Add('select max(aodate) from m2d_npv4 ');
qrySecondary.SQL.Add('where datepart(month,
aodate) = ');
qrySecondary.SQL.Add('datepart(month,
"' + DateToStr(BDate) + '") - 1');
qrySecondary.Open;
sOldestDate :=
DateToStr(qrySecondary.Fields[0].AsDateTime);
qrySecondary.Close;
select max(aodate)
from m2d_npv4
where datepart(month, aodate) = datepart(month, "11/15/96") - 1
Procedure:
TObsPL.GetMonthToDay
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: None
qryMonth2Day.SQL.Add('select npv_total, ytd_npv from m2d_npv4
');
qryMonth2Day.SQL.Add('where pfolio_name =
"' + sPortfolioID + '" ');
qryMonth2Day.SQL.Add('and aodate <=
"' + DateToStr(BDate) + '" ');
qryMonth2Day.SQL.Add('and aodate >=
"' + sOldestDate + '" ');
qryMonth2Day.SQL.Add('order by pfolio_name,
aodate');
qryMonth2Day.Open;
qryMonth2Day.First;
repeat
vValue1 := qryMonth2Day.Fields[1].Value;
vValue2 := qryMonth2Day.Fields[0].Value;
qryMonth2Day.Next;
vValue1 := qryMonth2Day.Fields[1].Value -
vValue1;
vValue2 := qryMonth2Day.Fields[0].Value -
vValue2;
vNpvUsdMtd := vNpvUsdMtd + vValue1;
vNpvBaseMtd := vNpvBaseMtd + vValue2;
until qryMonth2Day.EOF;
vNpvBaseMtd := (vNpvBaseToday - qryMonth2Day.Fields[0].Value)
+ vNpvBaseMtd;
vNpvUsdMtd
:= (vNpvUsdToday - qryMonth2Day.Fields[1].Value) + vNpvUsdMtd;
qryMonth2Day.Close;
qryMonth2Day.Free;
end;
select npv_total, ytd_npv
from m2d_npv4
where pfolio_name = "CORER" and aodate <= "2/7/97" and aodate >= "11/15/96"
order by pfolio_name, aodate
Procedure:
TObsPL.GetM2DUsd
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: None
qryOldestDate.SQL.Add('select max(aodate) from m2d_npv4 ');
qryOldestDate.SQL.Add('where datepart(month,
aodate) = ');
qryOldestDate.SQL.Add('datepart(month,
"' + DateToStr(BDate) + '") - 1');
qryOldestDate.Open;
sOldestDate :=
DateToStr(qryOldestDate.Fields[0].AsDateTime);
qryOldestDate.Close;
qryOldestDate.Free;
qryMonth2Day := TQuery.Create(MainForm);
qryMonth2Day.DatabaseName :=
APP_DBNAME_GDSTREAS;
qryMonth2Day.SQL.Add('select ytd_npv from m2d_npv4 ');
qryMonth2Day.SQL.Add('where pfolio_name =
"' + sPortfolioID + '" ');
qryMonth2Day.SQL.Add('and aodate <=
"' + DateToStr(BDate) + '" ');
qryMonth2Day.SQL.Add('and aodate >=
"' + sOldestDate + '" ');
qryMonth2Day.SQL.Add('order by pfolio_name,
aodate');
qryMonth2Day.Open;
qryMonth2Day.First;
repeat
vValue := qryMonth2Day.Fields[0].Value;
qryMonth2Day.Next;
vValue := qryMonth2Day.Fields[0].Value -
vValue;
vNpvBaseMtd := vNpvBaseMtd + vValue;
until qryMonth2Day.EOF;
vNpvBaseMtd := (vNpvBaseToday -
qryMonth2Day.Fields[0].Value) + vNpvBaseMtd;
qryMonth2Day.Close;
qryMonth2Day.Free;
end;
select max(aodate)
from m2d_npv4
where datepart(month, aodate) = datepart(month, "11/15/96") - 1
select ytd_npv
from m2d_npv4
where pfolio_name = "CORER" and aodate <= "11/15/96" and aodate >= "8/10/96"
order by pfolio_name, aodate
---------------------------
(null)
(1 row(s) affected)
ytd_npv
------------------------
(0 row(s) affected)
Function: TObsPL.GetPortfolioDataset
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if
sParam[3] = ')' then
sParam := Copy(sParam, 1, 2);
if MainForm.OfficialBtn.Down then
Q.SQL.Add('select reference, instruments_name
"type", ');
Q.SQL.Add('start_date "start
date", end_date "end date", ');
Q.SQL.Add('npv_amount "npv
amount", curnotnl "cur notnl", ');
Q.SQL.Add('counterparty "counter
party" ');
Q.SQL.Add('from obs_pl where aodate =
"' + DateToStr(RunDate) + '" ');
Q.SQL.Add('and npv_amount <> 0 ');
Q.SQL.Add('and pfolio_name like "%' + sParam + '" ');
Q.SQL.Add('and (instruments_name =
"IRS" or instruments_name = "FRA")');
Q.Open;
result := Q;
end
else
begin
sp := TStoredProc.Create(MainForm);
sp.DatabaseName := APP_DBNAME_GDS;
sp.StoredProcName :=
APP_DBSP_FXMTMREP_DET;
sp.ParamBindMode := pbByNumber;
sp.Params.CreateParam(ftDateTime,
'@eod_date', ptInput);
sp.Params.CreateParam(ftString,
'@portfolio', ptInput);
sp.Params.Items[0].AsDateTime := RunDate;
sp.Params.Items[1].Text := sParam;
sp.Open;
result := sp;
end;
select reference, instruments_name "type", start_date "start date", end_date "end date", npv_amount "npv amount", curnotnl "cur notnl", counterparty "counter party"
from obs_pl
where aodate = "11/10/96" and npv_amount <> 0 and pfolio_name like "FOBM1" and (instruments_name = "IRS" or instruments_name = "FRA")
The result of this query will spread data fields under the following column headers:
1.
reference
2.
type
3.
start date
4.
end date
5.
npv amount
6.
cur notnl
7.
counter party
Function:
TObsPL.GetMatHCash
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if
spMatHCash.Locate
if result = null then
result := 0;
qryMatHCash.SQL.Add('select historical_cash from hcash ');
qryMatHCash.SQL.Add('where pfolio_name =
"' + sPortfolioID + '" ');
qryMatHCash.Open;
result := qryMatHCash.Fields[0].Value;
if result = null then
result := 0;
qryMatHCash.Close;
qryMatHCash.Free;
end;
select historical_cash
from hcash
where pfolio_name = "CORER"
Function: TObsPL.GetValCash
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if
spValCash.Locate
if result = null then
qryValCash.SQL.Add('select historical_cash from cash ');
qryValCash.SQL.Add('where pfolio_name =
"' + sPortfolioID + '" ');
qryValCash.SQL.Add('and aodate = "' +
DateToStr(OldDate) + '" ');
qryValCash.Open;
result := qryValCash.Fields[0].Value;
if result = null then
result := 0;
qryValCash.Close;
qryValCash.Free;
end;
select historical_cash
from cash
where pfolio_name = “NYEC1” and aodate = “11/15/96”
Function: TObsPL.GetCurCash
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if
spValCash.Locate
if result = null then
qryCash.SQL.Add('select cash from cash ');
qryCash.SQL.Add('where pfolio_name = "'
+ sPortfolioID + '" ');
qryCash.SQL.Add('and aodate = "' +
DateToStr(OldDate) + '" ');
qryCash.Open;
result := qryCash.Fields[0].Value;
if result = null then
result := 0;
qryCash.Close;
qryCash.Free;
end;
select cash
from cash
where pfolio_name = “CDLG2” and aodate = “11/25/96”
Function: TObsPL.GetAdjustment
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if result
= null then
Q.SQL.Add('select adjustment from adjustments
where ');
Q.SQL.Add('book_num = ' + sBook + ' and type
= ' + IntToStr(nAdjType) +' and ');
Q.SQL.Add('ccy = "' + cCCY + '"');
Q.Open;
result := Q.Fields[0].Value;
if result = null then
result := 0;
Q.Close;
Q.Free;
end;
select adjustment
from adjustments
where book_num = 55 and type = 1 and ccy = "DEM"
Function: TObsPL.GetNpvTotalPrev
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: Fxutil.pas
Conditions: if result
= null then
qryNpvTotalPrev.SQL.Add('select ytd_npv from m2d_npv4 ');
qryNpvTotalPrev.SQL.Add('where pfolio_name =
"' + sPortfolioID + '" ');
qryNpvTotalPrev.SQL.Add('and aodate =
"' + DateToStr(BDate) + '" ');
qryNpvTotalPrev.Open;
result := qryNpvTotalPrev.Fields[0].Value;
qryNpvTotalPrev.Close;
qryNpvTotalPrev.Free;
end;
function
TObsPL.DescribeDataset: String;
begin
result := 'Type: ' //+
GetObsTypeName(FType);
end;
select ytd_npv
from m2d_npv4
where pfolio_name = "CORER" and aodate = "11/15/96"
Procedure:
TmainForm. RatesBtnClick(Sender: TObject)
DatabaseName: APP_DBNAME_GDSTREAS
GTMIS Source File: mfunit.pas
Conditions: if
MainForm.OfficialBtn.Down
Select ccy, spot_rate
from DailySpotRates