[Home] [Help]
PACKAGE BODY: APPS.XTR_CASHPOOL_UTLS
Source
1 PACKAGE BODY XTR_CASHPOOL_UTLS as
2 /* $Header: xtrpoolb.pls 120.0.12010000.2 2008/08/06 10:43:50 srsampat ship $ */
3
4 /****************************************************************/
5 /* Insert record into XTR_CASHPOOL_ATTRIBUTES table for IAC */
6 /* deal attributes */
7 /****************************************************************/
8 PROCEDURE INSERT_IAC_CASHPOOL (p_cashpool_id NUMBER,
9 p_party_code VARCHAR2,
10 p_iac_portfolio VARCHAR2,
11 p_iac_product_type VARCHAR2)IS
12 BEGIN
13 If p_cashpool_id is NOT NULL then
14 INSERT INTO XTR_CASHPOOL_ATTRIBUTES
15 (CASHPOOL_ATTRIBUTE_ID,
16 CASHPOOL_ID,
17 IAC_PORTFOLIO,
18 IAC_PRODUCT_TYPE,
19 PORTFOLIO,
20 PRODUCT_TYPE,
21 ROUNDING_TYPE,
22 DAY_COUNT_TYPE,
23 PRICING_MODEL,
24 FUND_LIMIT_CODE,
25 INVEST_LIMIT_CODE,
26 PARTY_CODE,
27 PARTY_PORTFOLIO,
28 PARTY_PRODUCT_TYPE,
29 PARTY_PRICING_MODEL,
30 PARTY_FUND_LIMIT_CODE,
31 PARTY_INVEST_LIMIT_CODE,
32 CREATED_BY,
33 CREATION_DATE,
34 LAST_UPDATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATE_LOGIN)
37 VALUES
38 (xtr_cashpool_attributes_s.nextval,
39 p_cashpool_id,
40 p_iac_portfolio,
41 p_iac_product_type,
42 null,
43 null,
44 null,
45 null,
46 null,
47 null,
48 null,
49 null,
50 null,
51 null,
52 null,
53 null,
54 null,
55 nvl(fnd_global.user_id,-1),
56 sysdate,
57 nvl(fnd_global.user_id,-1),
58 sysdate,
59 nvl(fnd_global.user_id,-1));
60 End if;
61 END;
62
63 /****************************************************************/
64 /* Insert record into XTR_CASHPOOL_ATTRIBUTES table for IG */
65 /* deal attributes */
66 /****************************************************************/
67 PROCEDURE INSERT_IG_CASHPOOL (p_cashpool_id NUMBER,
68 p_portfolio VARCHAR2,
69 p_product_type VARCHAR2,
70 p_rounding_type VARCHAR2,
71 p_day_count_type VARCHAR2,
72 p_pricing_model VARCHAR2,
73 p_fund_limit VARCHAR2,
74 p_invest_limit VARCHAR2,
75 p_party_code VARCHAR2,
76 p_party_portfolio VARCHAR2,
77 p_party_product_type VARCHAR2,
78 p_party_pricing_model VARCHAR2,
79 p_party_fund_limit VARCHAR2,
80 p_party_invest_limit VARCHAR2)IS
81 BEGIN
82 If p_cashpool_id is NOT NULL then
83 INSERT INTO XTR_CASHPOOL_ATTRIBUTES
84 (CASHPOOL_ATTRIBUTE_ID,
85 CASHPOOL_ID,
86 IAC_PORTFOLIO,
87 IAC_PRODUCT_TYPE,
88 PORTFOLIO,
89 PRODUCT_TYPE,
90 ROUNDING_TYPE,
91 DAY_COUNT_TYPE,
92 PRICING_MODEL,
93 FUND_LIMIT_CODE,
94 INVEST_LIMIT_CODE,
95 PARTY_CODE,
96 PARTY_PORTFOLIO,
97 PARTY_PRODUCT_TYPE,
98 PARTY_PRICING_MODEL,
99 PARTY_FUND_LIMIT_CODE,
100 PARTY_INVEST_LIMIT_CODE,
101 CREATED_BY,
102 CREATION_DATE,
103 LAST_UPDATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATE_LOGIN)
106 VALUES
107 (xtr_cashpool_attributes_s.nextval,
108 p_cashpool_id,
109 null,
110 null,
111 p_portfolio,
112 p_product_type,
113 p_rounding_type,
114 p_day_count_type,
115 p_pricing_model,
116 p_fund_limit,
117 p_invest_limit,
118 p_party_code,
119 p_party_portfolio,
120 p_party_product_type,
121 p_party_pricing_model,
122 p_party_fund_limit,
123 p_party_invest_limit,
124 nvl(fnd_global.user_id,-1),
125 sysdate,
126 nvl(fnd_global.user_id,-1),
127 sysdate,
128 nvl(fnd_global.user_id,-1));
129 End if;
130
131 END;
132
133 /****************************************************************/
134 /* Update XTR_CASHPOOL_ATTRIBUTES table for IAC deal attributes */
135 /****************************************************************/
136
137 PROCEDURE UPDATE_IAC_CASHPOOL (p_cashpool_id NUMBER,
138 p_iac_portfolio VARCHAR2,
139 p_iac_product_type VARCHAR2)IS
140 BEGIN
141 Update XTR_CASHPOOL_ATTRIBUTES
142 set IAC_PORTFOLIO = p_iac_portfolio,
143 IAC_PRODUCT_TYPE = p_iac_product_type
144 Where cashpool_id = p_cashpool_id
145 and IAC_PORTFOLIO is NOT NULL;
146 END;
147
148 /****************************************************************/
149 /* Update XTR_CASHPOOL_ATTRIBUTES table for IG deal attributes */
150 /* for Company */
151 /****************************************************************/
152 PROCEDURE UPDATE_IG_CASHPOOL (p_cashpool_id NUMBER,
153 p_portfolio VARCHAR2,
154 p_product_type VARCHAR2,
155 p_rounding_type VARCHAR2,
156 p_day_count_type VARCHAR2,
157 p_pricing_model VARCHAR2,
158 p_fund_limit VARCHAR2,
159 p_invest_limit VARCHAR2,
160 p_party_code VARCHAR2,
161 p_party_portfolio VARCHAR2,
162 p_party_product_type VARCHAR2,
163 p_party_pricing_model VARCHAR2,
164 p_party_fund_limit VARCHAR2,
165 p_party_invest_limit VARCHAR2)IS
166 BEGIN
167 Update XTR_CASHPOOL_ATTRIBUTES
168 Set PORTFOLIO = p_portfolio,
169 PRODUCT_TYPE = p_product_type,
170 ROUNDING_TYPE = p_rounding_type,
171 DAY_COUNT_TYPE = p_day_count_type,
172 PRICING_MODEL = p_pricing_model,
173 FUND_LIMIT_CODE = p_fund_limit,
174 INVEST_LIMIT_CODE = p_invest_limit,
175 PARTY_PORTFOLIO = p_party_portfolio,
176 PARTY_PRODUCT_TYPE = p_party_product_type,
177 PARTY_PRICING_MODEL = p_party_pricing_model,
178 PARTY_FUND_LIMIT_CODE = p_party_fund_limit,
179 PARTY_INVEST_LIMIT_CODE = p_party_invest_limit,
180 LAST_UPDATED_BY = nvl(fnd_global.user_id,-1),
181 LAST_UPDATE_DATE = sysdate,
182 LAST_UPDATE_LOGIN = nvl(fnd_global.user_id,-1)
183 Where cashpool_id = p_cashpool_id
184 and party_code = p_party_code
185 and iac_portfolio is NULL;
186 END;
187
188 /****************************************************************/
189 /* Update XTR_CASHPOOL_ATTRIBUTES table for IG deal attributes */
190 /* for Intercompany */
191 /****************************************************************/
192 PROCEDURE UPDATE_IG_ROW_CASHPOOL (p_cashpool_id NUMBER,
193 p_fund_limit VARCHAR2,
194 p_invest_limit VARCHAR2,
195 p_party_code VARCHAR2,
196 p_party_portfolio VARCHAR2,
197 p_party_product_type VARCHAR2,
198 p_party_pricing_model VARCHAR2,
199 p_party_fund_limit VARCHAR2,
200 p_party_invest_limit VARCHAR2)IS
201 BEGIN
202 Update XTR_CASHPOOL_ATTRIBUTES
203 Set FUND_LIMIT_CODE = p_fund_limit,
204 INVEST_LIMIT_CODE = p_invest_limit,
205 PARTY_PORTFOLIO = p_party_portfolio,
206 PARTY_PRODUCT_TYPE = p_party_product_type,
207 PARTY_PRICING_MODEL = p_party_pricing_model,
208 PARTY_FUND_LIMIT_CODE = p_party_fund_limit,
209 PARTY_INVEST_LIMIT_CODE = p_party_invest_limit,
210 LAST_UPDATED_BY = nvl(fnd_global.user_id,-1),
211 LAST_UPDATE_DATE = sysdate,
212 LAST_UPDATE_LOGIN = nvl(fnd_global.user_id,-1)
213 Where cashpool_id = p_cashpool_id
214 and party_code = p_party_code;
215 END;
216
217 /****************************************************************/
218 /* delete record from XTR_CASHPOOL_ATTRIBUTES table when user */
219 /* remove sub-account from the cashpool */
220 /****************************************************************/
221 PROCEDURE DELETE_XTR_CASHPOOL (p_cashpool_id NUMBER,
222 p_party_code VARCHAR2) IS
223 BEGIN
224 Delete from XTR_CASHPOOL_ATTRIBUTES
225 Where CASHPOOL_ATTRIBUTE_ID = p_cashpool_id
226 and party_code = p_party_code;
227 END;
228
229
230 /****************************************************************/
231 /* Default cashpol Treasury deal attribute values while user */
232 /* create the physical cashpool. */
233 /****************************************************************/
234 PROCEDURE DEFAULT_IAC_IG_ATTRIBUTES (p_company_code NUMBER,
235 x_iac_portfolio OUT NOCOPY VARCHAR2,
236 x_iac_product_type OUT NOCOPY VARCHAR2,
237 x_portfolio OUT NOCOPY VARCHAR2,
238 x_product_type OUT NOCOPY VARCHAR2,
239 x_rounding_type OUT NOCOPY VARCHAR2,
240 x_day_count_type OUT NOCOPY VARCHAR2,
241 x_pricing_model OUT NOCOPY VARCHAR2) is
242
243 Cursor C_IAC_ATT is
244 select iac_portfolio, iac_product_type
245 from xtr_cashpool_attributes
246 where party_code = p_company_code
247 and iac_portfolio is NOT NULL
248 order by creation_date desc;
249
250 Cursor C_IG_ATT is
251 select portfolio, product_type,
252 rounding_type, day_count_type, pricing_model
253 from xtr_cashpool_attributes
254 where party_code = p_company_code
255 and portfolio is NOT NULL
256 order by creation_date desc;
257
258 Cursor C_DFT_PORTFOLIO is
259 select portfolio
260 from XTR_PORTFOLIOS
261 where company_code = p_company_code
262 and nvl(default_portfolio, 'N') = 'Y';
263
264
265 BEGIN
266 Open C_IAC_ATT;
267 Fetch C_IAC_ATT into x_iac_portfolio, x_iac_product_type;
268 if C_IAC_ATT%NOTFOUND then
269 Open C_DFT_PORTFOLIO;
270 Fetch C_DFT_PORTFOLIO into x_iac_portfolio;
271 Close C_DFT_PORTFOLIO;
272 End if;
273
274 Close C_IAC_ATT;
275
276 Open C_IG_ATT;
277 Fetch C_IG_ATT into x_portfolio, x_product_type,
278 x_rounding_type, x_day_count_type, x_pricing_model;
279 if C_IG_ATT%NOTFOUND then
280 Open C_DFT_PORTFOLIO;
281 Fetch C_DFT_PORTFOLIO into x_portfolio;
282 Close C_DFT_PORTFOLIO;
283 End if;
284
285 Close C_IG_ATT;
286
287
288
289
290
291
292 END;
293 END XTR_CASHPOOL_UTLS;