1 PACKAGE OKL_ACCOUNT_DIST_PVT AS
2 /* $Header: OKLRTDTS.pls 120.8.12010000.2 2008/08/14 12:08:33 racheruv ship $ */
3
4 SUBTYPE ctxt_val_tbl_type IS OKL_EXECUTE_FORMULA_PUB.ctxt_val_tbl_type;
5 SUBTYPE tabv_rec_type IS OKL_TRNS_ACC_DSTRS_PUB.tabv_rec_type;
6 SUBTYPE tabv_tbl_type IS OKL_TRNS_ACC_DSTRS_PUB.tabv_tbl_type;
7
8 SUBTYPE avlv_tbl_type IS OKL_TMPT_SET_PUB.avlv_tbl_type;
9 SUBTYPE avlv_rec_type IS OKL_TMPT_SET_PUB.avlv_rec_type;
10 SUBTYPE aetv_rec_type IS OKL_ACCT_EVENT_PUB.aetv_rec_type;
11 SUBTYPE acc_gen_primary_key IS Okl_Account_Generator_Pub.primary_key_tbl;
12
13 -- bug 4157521
14
15 SUBTYPE acc_gen_wf_sources_rec IS Okl_Account_Generator_Pub.acc_gen_wf_sources_rec;
16
17 -- Added by Santonyr on 26-Jul-2004 for the bug 3772490
18
19 SUBTYPE asev_rec_type IS Okl_Acct_Sources_Pvt.asev_rec_type;
20 SUBTYPE asev_tbl_type IS Okl_Acct_Sources_Pvt.asev_tbl_type;
21
22
23 TYPE TMPL_IDENTIFY_REC_TYPE IS RECORD
24 (PRODUCT_ID NUMBER := OKL_API.G_MISS_NUM,
25 TRANSACTION_TYPE_ID NUMBER := OKL_API.G_MISS_NUM,
26 STREAM_TYPE_ID NUMBER := OKL_API.G_MISS_NUM,
27 ADVANCE_ARREARS OKL_AE_TEMPLATES.ADVANCE_ARREARS%TYPE := OKL_API.G_MISS_CHAR,
28 FACTORING_SYND_FLAG OKL_AE_TEMPLATES.FACTORING_SYND_FLAG%TYPE := OKL_API.G_MISS_CHAR,
29 SYNDICATION_CODE OKL_AE_TEMPLATES.SYT_CODE%TYPE := OKL_API.G_MISS_CHAR,
30 FACTORING_CODE OKL_AE_TEMPLATES.FAC_CODE%TYPE := OKL_API.G_MISS_CHAR,
31
32 -- Santonyr. Added to implement Securitization Chnages
33 INVESTOR_CODE OKL_AE_TEMPLATES.INV_CODE%TYPE := OKL_API.G_MISS_CHAR,
34 MEMO_YN OKL_AE_TEMPLATES.MEMO_YN%TYPE := OKL_API.G_MISS_CHAR,
35 PRIOR_YEAR_YN OKL_AE_TEMPLATES.PRIOR_YEAR_YN%TYPE := OKL_API.G_MISS_CHAR,
36
37 -- 15-Oct-04 santonyr -- Added a new flag to template identification record
38 -- for revenue recognition program Bug 3948354.
39 REV_REC_FLAG VARCHAR2(1) := 'N'
40
41 );
42
43 TYPE DIST_INFO_REC_TYPE IS RECORD
44 (SOURCE_ID NUMBER := OKL_API.G_MISS_NUM,
45 SOURCE_TABLE OKL_TRNS_ACC_DSTRS.SOURCE_TABLE%TYPE := OKL_API.G_MISS_CHAR,
46 ACCOUNTING_DATE OKL_TRNS_ACC_DSTRS.GL_DATE%TYPE := OKL_API.G_MISS_DATE,
47 GL_REVERSAL_FLAG OKL_TRNS_ACC_DSTRS.GL_REVERSAL_FLAG%TYPE := OKL_API.G_MISS_CHAR,
48 POST_TO_GL OKL_TRNS_ACC_DSTRS.POST_TO_GL%TYPE := OKL_API.G_MISS_CHAR,
49 AMOUNT NUMBER := OKL_API.G_MISS_NUM,
50 CURRENCY_CODE OKL_TRNS_ACC_DSTRS.CURRENCY_CODE%TYPE := OKL_API.G_MISS_CHAR,
51 CURRENCY_CONVERSION_TYPE OKL_TRNS_ACC_DSTRS.CURRENCY_CONVERSION_TYPE%TYPE
52 := OKL_API.G_MISS_CHAR,
53 CURRENCY_CONVERSION_DATE OKL_TRNS_ACC_DSTRS.CURRENCY_CONVERSION_DATE%TYPE
54 := OKL_API.G_MISS_DATE,
55
56 -- Santonyr Added on 18-Nov-2002
57 -- Multi Currency Changes
58
59 CURRENCY_CONVERSION_RATE NUMBER := OKL_API.G_MISS_NUM,
60
61 CONTRACT_ID NUMBER := OKL_API.G_MISS_NUM,
62 CONTRACT_LINE_ID NUMBER := OKL_API.G_MISS_NUM);
63
64 TYPE AMOUNT_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
65
66 --Added by gboomina for Accruals Performance
67 --Bug 4662173 - Start of Changes
68 TYPE DIST_INFO_TBL_TYPE IS TABLE OF DIST_INFO_REC_TYPE INDEX BY BINARY_INTEGER;
69
70 TYPE avlb_rec_type IS RECORD (
71 id NUMBER := OKC_API.G_MISS_NUM,
72 object_version_number NUMBER := OKC_API.G_MISS_NUM,
73 try_id NUMBER := OKC_API.G_MISS_NUM,
74 aes_id NUMBER := OKC_API.G_MISS_NUM,
75 sty_id NUMBER := OKC_API.G_MISS_NUM,
76 fma_id NUMBER := OKC_API.G_MISS_NUM,
77 set_of_books_id NUMBER := OKC_API.G_MISS_NUM,
78 fac_code OKL_AE_TEMPLATES.FAC_CODE%TYPE := OKC_API.G_MISS_CHAR,
79 syt_code OKL_AE_TEMPLATES.SYT_CODE%TYPE := OKC_API.G_MISS_CHAR,
80 post_to_gl OKL_AE_TEMPLATES.POST_TO_GL%TYPE := OKC_API.G_MISS_CHAR,
81 advance_arrears OKL_AE_TEMPLATES.ADVANCE_ARREARS%TYPE := OKC_API.G_MISS_CHAR,
82 memo_yn OKL_AE_TEMPLATES.MEMO_YN%TYPE := OKC_API.G_MISS_CHAR,
83 prior_year_yn OKL_AE_TEMPLATES.PRIOR_YEAR_YN%TYPE := OKC_API.G_MISS_CHAR,
84 name OKL_AE_TEMPLATES.NAME%TYPE := OKC_API.G_MISS_CHAR,
85 description OKL_AE_TEMPLATES.DESCRIPTION%TYPE := OKC_API.G_MISS_CHAR,
86 version OKL_AE_TEMPLATES.VERSION%TYPE := OKC_API.G_MISS_CHAR,
87 factoring_synd_flag OKL_AE_TEMPLATES.FACTORING_SYND_FLAG%TYPE := OKC_API.G_MISS_CHAR,
88 start_date OKL_AE_TEMPLATES.START_DATE%TYPE := OKC_API.G_MISS_DATE,
89 end_date OKL_AE_TEMPLATES.END_DATE%TYPE := OKC_API.G_MISS_DATE,
90 accrual_yn OKL_AE_TEMPLATES.ACCRUAL_YN%TYPE := OKC_API.G_MISS_CHAR,
91 attribute_category OKL_AE_TEMPLATES.ATTRIBUTE_CATEGORY%TYPE := OKC_API.G_MISS_CHAR,
92 attribute1 OKL_AE_TEMPLATES.ATTRIBUTE1%TYPE := OKC_API.G_MISS_CHAR,
93 attribute2 OKL_AE_TEMPLATES.ATTRIBUTE2%TYPE := OKC_API.G_MISS_CHAR,
94 attribute3 OKL_AE_TEMPLATES.ATTRIBUTE3%TYPE := OKC_API.G_MISS_CHAR,
95 attribute4 OKL_AE_TEMPLATES.ATTRIBUTE4%TYPE := OKC_API.G_MISS_CHAR,
96 attribute5 OKL_AE_TEMPLATES.ATTRIBUTE5%TYPE := OKC_API.G_MISS_CHAR,
97 attribute6 OKL_AE_TEMPLATES.ATTRIBUTE6%TYPE := OKC_API.G_MISS_CHAR,
98 attribute7 OKL_AE_TEMPLATES.ATTRIBUTE7%TYPE := OKC_API.G_MISS_CHAR,
99 attribute8 OKL_AE_TEMPLATES.ATTRIBUTE8%TYPE := OKC_API.G_MISS_CHAR,
100 attribute9 OKL_AE_TEMPLATES.ATTRIBUTE9%TYPE := OKC_API.G_MISS_CHAR,
101 attribute10 OKL_AE_TEMPLATES.ATTRIBUTE10%TYPE := OKC_API.G_MISS_CHAR,
102 attribute11 OKL_AE_TEMPLATES.ATTRIBUTE11%TYPE := OKC_API.G_MISS_CHAR,
103 attribute12 OKL_AE_TEMPLATES.ATTRIBUTE12%TYPE := OKC_API.G_MISS_CHAR,
104 attribute13 OKL_AE_TEMPLATES.ATTRIBUTE13%TYPE := OKC_API.G_MISS_CHAR,
105 attribute14 OKL_AE_TEMPLATES.ATTRIBUTE14%TYPE := OKC_API.G_MISS_CHAR,
106 attribute15 OKL_AE_TEMPLATES.ATTRIBUTE15%TYPE := OKC_API.G_MISS_CHAR,
107 org_id NUMBER := OKC_API.G_MISS_NUM,
108 created_by NUMBER := OKC_API.G_MISS_NUM,
109 creation_date OKL_AE_TEMPLATES.CREATION_DATE%TYPE := OKC_API.G_MISS_DATE,
110 last_updated_by NUMBER := OKC_API.G_MISS_NUM,
111 last_update_date OKL_AE_TEMPLATES.LAST_UPDATE_DATE%TYPE := OKC_API.G_MISS_DATE,
112 last_update_login NUMBER := OKC_API.G_MISS_NUM,
113
114 -- Added by HKPATEL for securitization changes
115 inv_code OKL_AE_TEMPLATES.INV_CODE%TYPE := OKC_API.G_MISS_CHAR,
116 parent_index_number NUMBER := OKC_API.G_MISS_NUM);
117
118 g_miss_avlv_rec avlv_rec_type;
119
120 TYPE avlb_tbl_type IS TABLE OF avlb_rec_type
121 INDEX BY BINARY_INTEGER;
122
123 TYPE amt_rec_type IS RECORD(
124 amount NUMBER := OKC_API.G_MISS_NUM,
125 parent_index_number NUMBER := OKC_API.G_MISS_NUM);
126
127 TYPE amt_tbl_type IS TABLE OF amt_rec_type
128 INDEX BY BINARY_INTEGER;
129
130 -- Table type which holds the account generator source related information
131 TYPE acc_gen_rec IS RECORD
132 (source_table okl_ag_source_maps.source%TYPE,
133 primary_key_column VARCHAR2(100),
134 source_id NUMBER
135 );
136
137 TYPE acc_gen_tbl IS TABLE OF acc_gen_rec INDEX BY BINARY_INTEGER;
138 --Bug 4662173 - End of Changes
139
140 --Added by kthiruva on 12-Feb-2007 for SLA Uptake
141 --Bug 5707866 - Start of Changes
142 TYPE tmpl_identify_tbl_type IS TABLE OF tmpl_identify_rec_type INDEX BY BINARY_INTEGER;
143
144 --Record and table type to store the ctxt_val_Tbl per transaction line_id
145 TYPE ctxt_rec_type IS RECORD(ctxt_val_tbl ctxt_val_tbl_type
146 ,source_id NUMBER );
147
148 TYPE ctxt_tbl_type IS TABLE OF ctxt_rec_type INDEX BY BINARY_INTEGER;
149
150 --Record and table type to store the account generator table info per transaction line
151 TYPE acc_gen_rec_type IS RECORD(acc_gen_key_tbl acc_gen_primary_key
152 ,source_id NUMBER);
153
154 TYPE acc_gen_tbl_type IS TABLE OF acc_gen_rec_type INDEX BY BINARY_INTEGER;
155
156 --Record and table type to store the template info per transaction line
157 TYPE avlv_out_rec_type IS RECORD(template_tbl AVLV_TBL_TYPE,
158 source_id NUMBER);
159
160 TYPE avlv_out_tbl_type IS TABLE OF avlv_out_rec_type INDEX BY BINARY_INTEGER;
161
162 --Record and table type to store the distribution amoounts per transaction line
163 TYPE amount_out_rec_type IS RECORD(amount_tbl AMOUNT_TBL_TYPE,
164 source_id NUMBER);
165
166 TYPE amount_out_tbl_type IS TABLE OF amount_out_rec_type INDEX BY BINARY_INTEGER;
167
168 TYPE tclv_rec_type IS RECORD(source_id NUMBER,
169 source_table VARCHAR2(30));
170
171 TYPE tclv_tbl_type IS TABLE OF tclv_rec_type INDEX BY BINARY_INTEGER;
172 --Bug 5707866 - End of Changes
173
174
175 PROCEDURE CREATE_ACCOUNTING_DIST(p_api_version IN NUMBER,
176 p_init_msg_list IN VARCHAR2,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_msg_count OUT NOCOPY NUMBER,
179 x_msg_data OUT NOCOPY VARCHAR2,
180 p_tmpl_identify_rec IN TMPL_IDENTIFY_REC_TYPE,
181 p_dist_info_rec IN DIST_INFO_REC_TYPE,
182 p_ctxt_val_tbl IN CTXT_VAL_TBL_TYPE,
183 p_acc_gen_primary_key_tbl IN acc_gen_primary_key,
184 x_template_tbl OUT NOCOPY AVLV_TBL_TYPE,
185 x_amount_tbl OUT NOCOPY AMOUNT_TBL_TYPE);
186
187 PROCEDURE CREATE_ACCOUNTING_DIST(p_api_version IN NUMBER,
188 p_init_msg_list IN VARCHAR2,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_msg_count OUT NOCOPY NUMBER,
191 x_msg_data OUT NOCOPY VARCHAR2,
192 p_tmpl_identify_rec IN TMPL_IDENTIFY_REC_TYPE,
193 p_dist_info_rec IN DIST_INFO_REC_TYPE,
194 p_ctxt_val_tbl IN CTXT_VAL_TBL_TYPE,
195 p_acc_gen_primary_key_tbl IN acc_gen_primary_key,
196 x_template_tbl OUT NOCOPY AVLV_TBL_TYPE,
197 x_amount_tbl OUT NOCOPY AMOUNT_TBL_TYPE,
198 x_gl_date OUT NOCOPY DATE);
199
200 --Added by gboomina on 14-Oct-2005 for improving Accruals Performance
201 --Bug 4662173 - Start of Changes
202 PROCEDURE CREATE_ACCOUNTING_DIST(p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2,
207 p_tmpl_identify_rec IN TMPL_IDENTIFY_REC_TYPE,
208 p_dist_info_tbl IN DIST_INFO_TBL_TYPE,
209 p_ctxt_val_tbl IN CTXT_VAL_TBL_TYPE,
210 p_acc_gen_primary_key_tbl IN acc_gen_tbl,
211 x_template_tbl OUT NOCOPY AVLB_TBL_TYPE,
212 x_amount_tbl OUT NOCOPY AMT_TBL_TYPE);
213 --Bug 4662173 - End of Changes
214
215 --Added by kthiruva on 14-Feb-2007 for SLA Uptake
216 --Bug 5707866 - Start of Changes
217 --New Accounting Engine signature which accepts a table of distributions and template_identify_rec
218 --This call is made once for a transaction header
219 PROCEDURE CREATE_ACCOUNTING_DIST (p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2,
224 p_tmpl_identify_tbl IN TMPL_IDENTIFY_TBL_TYPE,
225 p_dist_info_tbl IN DIST_INFO_TBL_TYPE,
226 p_ctxt_val_tbl IN CTXT_TBL_TYPE,
227 p_acc_gen_primary_key_tbl IN ACC_GEN_TBL_TYPE,
228 x_template_tbl OUT NOCOPY AVLV_OUT_TBL_TYPE,
229 x_amount_tbl OUT NOCOPY AMOUNT_OUT_TBL_TYPE,
230 p_trx_header_id IN NUMBER,
231 p_trx_header_table IN VARCHAR2 DEFAULT NULL);
232 --Bug 5707866 - End of Changes
233
234
235 PROCEDURE GET_TEMPLATE_INFO(p_api_version IN NUMBER,
236 p_init_msg_list IN VARCHAR2,
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2,
240 p_tmpl_identify_rec IN TMPL_IDENTIFY_REC_TYPE,
241 x_template_tbl OUT NOCOPY AVLV_TBL_TYPE,
242 p_validity_date IN DATE DEFAULT SYSDATE);
243
244
245 PROCEDURE UPDATE_POST_TO_GL(p_api_version IN NUMBER,
246 p_init_msg_list IN VARCHAR2,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 p_source_id IN NUMBER,
251 p_source_table IN VARCHAR2);
252
253
254 PROCEDURE DELETE_ACCT_ENTRIES(p_api_version IN NUMBER,
255 p_init_msg_list IN VARCHAR2,
256 x_return_status OUT NOCOPY VARCHAR2,
257 x_msg_count OUT NOCOPY NUMBER,
258 x_msg_data OUT NOCOPY VARCHAR2,
259 p_source_id IN NUMBER,
260 p_source_table IN VARCHAR2);
261
262
263 PROCEDURE REVERSE_ENTRIES(p_api_version IN NUMBER,
264 p_init_msg_list IN VARCHAR2,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268 p_source_id IN NUMBER,
269 p_source_table IN VARCHAR2,
270 p_acct_date IN DATE);
271
272
273 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_ACCOUNT_DIST_PVT' ;
274 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
275 g_sysdate DATE := TRUNC(SYSDATE);
276 G_gl_date DATE;
277
278
279
280 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
281 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
282 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLerrm';
283 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLcode';
284 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
285 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
286
287
288 -- Added by Santonyr on 26-Jul-2004 for the bug 3772490
289
290 TYPE template_amount_rec_type IS RECORD
291 (TEMPLATE_ID NUMBER := Okl_Api.G_MISS_NUM,
292 AMOUNT NUMBER := Okl_Api.G_MISS_NUM,
293 FORMULA_USED VARCHAR2(3) := Okl_Api.G_MISS_CHAR,
294 STREAM_TYPE_ID NUMBER := Okl_Api.G_MISS_NUM);
295
296 TYPE template_amount_tbl_type IS TABLE OF template_amount_rec_type INDEX BY BINARY_INTEGER;
297
298 --Added by gboomina on 14-Oct-2005 for improving Accruals Performance
299 --Bug 4662173 - Start of Changes
300 TYPE tmp_bulk_amount_rec_type IS RECORD
301 (TEMPLATE_ID NUMBER := Okl_Api.G_MISS_NUM,
302 AMOUNT NUMBER := Okl_Api.G_MISS_NUM,
303 FORMULA_USED VARCHAR2(3) := Okl_Api.G_MISS_CHAR,
304 STREAM_TYPE_ID NUMBER := Okl_Api.G_MISS_NUM,
305 PARENT_INDEX_NUMBER NUMBER := Okl_Api.G_MISS_NUM);
306
307 TYPE tmp_bulk_amount_tbl_type IS TABLE OF tmp_bulk_amount_rec_type INDEX BY BINARY_INTEGER;
308 --Bug 4662173 - End of Changes
309
310
311 PROCEDURE POPULATE_ACCT_SOURCES(p_api_version IN NUMBER,
312 p_init_msg_list IN VARCHAR2,
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_count OUT NOCOPY NUMBER,
315 x_msg_data OUT NOCOPY VARCHAR2,
316 p_tmpl_identify_rec IN TMPL_IDENTIFY_REC_TYPE,
317 p_dist_info_rec IN DIST_INFO_REC_TYPE,
318 p_acc_gen_primary_key_tbl IN acc_gen_primary_key,
319 p_template_amount_tbl IN template_amount_tbl_type);
320
321
322 --Added by gboomina for Accruals Performance Improvement
323 --Bug 4662173 - Start of Changes
324 PROCEDURE POPULATE_ACCT_SOURCES(p_api_version IN NUMBER,
325 p_init_msg_list IN VARCHAR2,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 p_tmpl_identify_rec IN TMPL_IDENTIFY_REC_TYPE,
330 p_dist_info_tbl IN DIST_INFO_TBL_TYPE,
331 p_acc_gen_primary_key_tbl IN acc_gen_tbl,
332 p_template_amount_tbl IN tmp_bulk_amount_tbl_type);
333 --Bug 4662173 - End of Changes
334
335 -- MG uptake
336 TYPE ledger_rec_type IS RECORD
337 (ledger_id gl_ledgers.ledger_id%TYPE,
338 rep_code gl_ledgers.short_name%TYPE,
339 rep_type VARCHAR2(20)
340 );
341 TYPE ledger_tbl_type IS TABLE OF ledger_rec_type INDEX BY PLS_INTEGER;
342
343 g_representation_type VARCHAR2(20);
344 g_ledger_id NUMBER;
345
346 END OKL_ACCOUNT_DIST_PVT;