1 PACKAGE XTR_COF_P AUTHID CURRENT_USER as
2 /* $Header: xtrcosts.pls 120.3 2005/06/29 06:12:35 badiredd ship $ */
3
4 /*
5 XTR_POSITION_HISTORY holds a daily record of balances and rates for all instuments
6 that were current at that time. Essentially it is a snapshot of xtr_mirror_dda_limit_row.
7 It is maintained automatically through calling package xtr_maintain_history from the relevant
8 table triggers on tables(xtr_Deals,xtr_Rollover_Transactions, xtr_Intergroup_Transfers
9 and xtr_Bank_Balances)
10
11 All backdating of deals (both updates and new records) are handled and records
12 in xtr_position_history are table trigger on xtr_position_history calls package
13 xtr_maintain_cost_of_funds to maintain XTR_COST_OF_FUNDS. This table holds summarized information
14 grouped by Company, Deal Type, Deal Subtype, Product Type, Portfolio, Cparty, Currency and
15 Currency Combination.
16
17 Note: For bugs 2855289 and 3062129 for both speed and accuracy global variables
18 have been added.
19 For IG there is G_IG_CURR_DEAL_NUMBER, G_IG_CURR_TRANSACTION_NUMBER and G_IG_CURR_MATURITY_DATE,
20 and for CA there is G_CA_DEAL_NUMBER, G_CA_CURR_TRANSACTION_NUMBER, and G_CA_CURR_MATURITY_DATE.
21 If the deal number and transaction number match the current deal being processed then the value
22 stored in the maturity date is used as the maturity date. This prevents the position table
23 from populating too far because these deal types do not have a column in their respective tables
24 to receive the maturity date. SET/GET_CURR_DEAL_ADDITIONAL_DETAILS are helper functions to set these values.
25 You can only hold one deal at a time per deal type, and once the deal information is used it is cleared.
26 */
27
28 G_IG_CURR_DEAL_NUMBER XTR_INTERGROUP_TRANSFERS.DEAL_NUMBER%TYPE;
29 G_IG_CURR_TRANSACTION_NUMBER XTR_INTERGROUP_TRANSFERS.TRANSACTION_NUMBER%TYPE;
30 G_IG_CURR_MATURITY_DATE DATE;
31
32 PROCEDURE SET_CURR_IG_DEAL_DETAILS(
33 P_DEAL_NUMBER IN XTR_INTERGROUP_TRANSFERS.DEAL_NUMBER%TYPE,
34 P_TRANSACTION_NUMBER IN XTR_INTERGROUP_TRANSFERS.TRANSACTION_NUMBER%TYPE,
35 P_MATURITY_DATE IN DATE);
36
37 PROCEDURE GET_CURR_IG_DEAL_DETAILS(
38 P_DEAL_NUMBER IN XTR_INTERGROUP_TRANSFERS.DEAL_NUMBER%TYPE,
39 P_TRANSACTION_NUMBER IN XTR_INTERGROUP_TRANSFERS.TRANSACTION_NUMBER%TYPE,
40 P_MATURITY_DATE OUT NOCOPY DATE);
41
42 PROCEDURE MAINTAIN_POSITION_HISTORY(
43 P_START_DATE IN DATE,
44 P_MATURITY_DATE IN DATE,
45 P_OTHER_DATE IN DATE,
46 P_DEAL_NUMBER IN NUMBER,
47 P_TRANSACTION_NUMBER IN NUMBER,
48 P_COMPANY_CODE IN VARCHAR2,
49 P_CURRENCY IN VARCHAR2,
50 P_DEAL_TYPE IN VARCHAR2,
51 P_DEAL_SUBTYPE IN VARCHAR2,
52 P_PRODUCT_TYPE IN VARCHAR2,
53 P_PORTFOLIO_CODE IN VARCHAR2,
54 P_CPARTY_CODE IN VARCHAR2,
55 P_CONTRA_CCY IN VARCHAR2,
56 P_CURRENCY_COMBINATION IN VARCHAR2,
57 P_ACCOUNT_NO IN VARCHAR2,
58 P_TRANSACTION_RATE IN NUMBER,
59 P_YEAR_CALC_TYPE IN VARCHAR2,
60 P_BASE_REF_AMOUNT IN NUMBER,
61 P_BASE_RATE IN NUMBER,
62 P_STATUS_CODE IN VARCHAR2,
63 P_INTEREST IN NUMBER,
64 P_MATURITY_AMOUNT IN NUMBER,
65 P_START_AMOUNT IN NUMBER,
66 P_CALC_BASIS IN VARCHAR2,
67 P_CALC_TYPE IN VARCHAR2,
68 P_ACTION IN VARCHAR2,
69 P_DAY_COUNT_TYPE IN VARCHAR2 DEFAULT NULL,
70 P_FIRST_TRANS_FLAG IN VARCHAR2 DEFAULT NULL
71 );
72
73 PROCEDURE SNAPSHOT_POSITION_HISTORY(
74 P_AS_AT_DATE IN DATE,
75 P_DEAL_NUMBER IN NUMBER,
76 P_TRANSACTION_NUMBER IN NUMBER,
77 P_COMPANY_CODE IN VARCHAR2,
78 P_CURRENCY IN VARCHAR2,
79 P_DEAL_TYPE IN VARCHAR2,
80 P_DEAL_SUBTYPE IN VARCHAR2,
81 P_PRODUCT_TYPE IN VARCHAR2,
82 P_PORTFOLIO_CODE IN VARCHAR2,
83 P_CPARTY_CODE IN VARCHAR2,
84 P_CONTRA_CCY IN VARCHAR2,
85 P_CURRENCY_COMBINATION IN VARCHAR2,
86 P_ACCOUNT_NO IN VARCHAR2,
87 P_TRANSACTION_RATE IN NUMBER,
88 P_YEAR_CALC_TYPE IN VARCHAR2,
89 P_BASE_REF_AMOUNT IN NUMBER,
90 P_BASE_RATE IN NUMBER,
91 P_STATUS_CODE IN VARCHAR2,
92 P_START_DATE IN DATE,
93 P_MATURITY_DATE IN DATE,
94 P_INTEREST IN NUMBER,
95 P_MATURITY_AMOUNT IN NUMBER,
96 P_START_AMOUNT IN NUMBER,
97 P_CALC_BASIS IN VARCHAR2,
98 P_CALC_TYPE IN VARCHAR2,
99 P_DAY_COUNT_TYPE IN VARCHAR2 DEFAULT NULL,
100 P_FIRST_TRANS_FLAG IN VARCHAR2 DEFAULT NULL
101 );
102
103 PROCEDURE MAINTAIN_COST_OF_FUND(
104 OLD_AS_AT_DATE IN date,
105 OLD_COMPANY_CODE IN VARCHAR2,
106 OLD_CURRENCY IN VARCHAR2,
107 OLD_DEAL_TYPE IN VARCHAR2,
108 OLD_DEAL_SUBTYPE IN VARCHAR2,
109 OLD_PRODUCT_TYPE IN VARCHAR2,
110 OLD_PORTFOLIO_CODE IN VARCHAR2,
111 OLD_CPARTY_CODE IN VARCHAR2,
112 OLD_CONTRA_CCY IN VARCHAR2,
113 OLD_CURRENCY_COMBINATION IN VARCHAR2,
114 OLD_ACCOUNT_NO IN VARCHAR2,
115 OLD_TRANSACTION_RATE IN NUMBER,
116 OLD_YEAR_CALC_TYPE IN VARCHAR2,
117 OLD_BASE_REF_AMOUNT IN NUMBER,
118 OLD_HCE_BASE_REF_AMOUNT IN NUMBER,
119 OLD_BASE_RATE IN NUMBER,
120 OLD_INTEREST IN NUMBER,
121 OLD_HCE_INTEREST IN NUMBER,
122 NEW_AS_AT_DATE IN date,
123 NEW_COMPANY_CODE IN VARCHAR2,
124 NEW_CURRENCY IN VARCHAR2,
125 NEW_DEAL_TYPE IN VARCHAR2,
126 NEW_DEAL_SUBTYPE IN VARCHAR2,
127 NEW_PRODUCT_TYPE IN VARCHAR2,
128 NEW_PORTFOLIO_CODE IN VARCHAR2,
129 NEW_CPARTY_CODE IN VARCHAR2,
130 NEW_CONTRA_CCY IN VARCHAR2,
131 NEW_CURRENCY_COMBINATION IN VARCHAR2,
132 NEW_ACCOUNT_NO IN VARCHAR2,
133 NEW_TRANSACTION_RATE IN NUMBER,
134 NEW_YEAR_CALC_TYPE IN VARCHAR2,
135 NEW_BASE_REF_AMOUNT IN NUMBER,
136 NEW_HCE_BASE_REF_AMOUNT IN NUMBER,
137 NEW_BASE_RATE IN NUMBER,
138 NEW_INTEREST IN NUMBER,
139 NEW_HCE_INTEREST IN NUMBER,
140 P_ACTION IN VARCHAR2);
141
142 PROCEDURE SNAPSHOT_COST_OF_FUNDS(errbuf OUT NOCOPY VARCHAR2,
143 retcode OUT NOCOPY NUMBER);
144
145 PROCEDURE CALCULATE_BOND_RATE(
146 p_deal_no IN NUMBER,
147 p_maturity_amt IN NUMBER,
148 p_consideration IN NUMBER,
149 p_coupon_rate IN NUMBER,
150 p_start_date IN DATE,
151 p_maturity_date IN DATE,
152 p_calc_type IN VARCHAR2,
153 p_daily_int OUT NOCOPY NUMBER,
154 p_yield_rate OUT NOCOPY NUMBER,
155 p_day_count_type IN VARCHAR2 DEFAULT NULL);
156
157 PROCEDURE UPLOAD_AVG_RATES_RESULTS(
158 p_batch_id IN NUMBER,
159 p_group_type IN VARCHAR2,
160 p_date_from IN DATE,
161 p_date_to IN DATE,
162 p_company_code IN VARCHAR2,
163 p_deal_type IN VARCHAR2,
164 p_currency IN VARCHAR2,
165 p_contra_ccy IN VARCHAR2,
166 p_cparty_code IN VARCHAR2,
167 p_product_type IN VARCHAR2,
168 p_portfolio_code IN VARCHAR2,
169 p_group_by_month IN VARCHAR2,
170 p_group_by_year IN VARCHAR2,
171 p_group_by_company IN VARCHAR2,
172 p_group_by_deal IN VARCHAR2,
173 p_group_by_currency IN VARCHAR2,
174 p_group_by_cparty IN VARCHAR2,
175 p_group_by_product IN VARCHAR2,
176 p_group_by_portfolio IN VARCHAR2);
177
178 end XTR_COF_P;
[Home] [Help]