1 PACKAGE CST_ReceiptAccrualPerEnd_PVT AUTHID CURRENT_USER AS
2 /* $Header: CSTVRAPS.pls 120.5.12010000.2 2008/08/08 14:27:09 mpuranik ship $ */
3
4 -----------------------------------------------------------------------------
5 -- Record type for System_setup information
6 -----------------------------------------------------------------------------
7 /* Bug6987381 : Added Org_id */
8 TYPE cst_sys_setup_rec_type IS RECORD (
9 set_of_books_id NUMBER(15),
10 chart_of_accounts_id NUMBER(15),
11 functional_currency_code VARCHAR2(15),
12 purch_encumbrance_flag VARCHAR2(1),
13 period_name VARCHAR2(15),
14 accrual_effect_date DATE,
15 accrual_cutoff_date DATE,
16 period_end_date DATE,
17 transaction_date DATE,
18 user_je_source_name VARCHAR2(25),
19 user_je_category_name VARCHAR2(25),
20 org_id NUMBER(15)
21 );
22
23 -----------------------------------------------------------------------------
24 -- Record type for period end accrual information
25 -----------------------------------------------------------------------------
26 TYPE cst_accrual_info_rec_type IS RECORD (
27 rcv_acc_event_id NUMBER,
28 actual_flag VARCHAR2(1),
29 currency_code VARCHAR2(15),
30 code_combination_id NUMBER,
31 entered_dr NUMBER,
32 entered_cr NUMBER,
33 accounted_dr NUMBER,
34 accounted_cr NUMBER,
35 currency_conversion_date DATE,
36 user_currency_conversion_type VARCHAR2(30),
37 currency_conversion_rate NUMBER,
38 po_header_id NUMBER,
39 distribution_id NUMBER,
40 po_number VARCHAR2(25),
41 source_doc_quantity NUMBER,
42 entered_rec_tax NUMBER,
43 entered_nr_tax NUMBER,
44 accounted_rec_tax NUMBER,
45 accounted_nr_tax NUMBER,
46 accrual_method_flag VARCHAR2(1),
47 accounting_line_type VARCHAR2(25),
48 parent_rcv_acc_event_id NUMBER
49 );
50
51 -----------------------------------------------------------------------------
52 -- Table types for RCV_RECEIVING_SUB_LEDGER
53 -----------------------------------------------------------------------------
54 TYPE rcv_acc_event_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
55 TYPE pnt_rcv_acc_event_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
56 TYPE actual_flag_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
57 TYPE currency_code_tbl_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
58 TYPE code_combination_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
59 TYPE entered_dr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
60 TYPE entered_cr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
61 TYPE accounted_dr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
62 TYPE accounted_cr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
63 TYPE curr_conversion_date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
64 TYPE user_curr_conversion_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
65 TYPE curr_conversion_rate_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
66 TYPE po_header_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
67 TYPE shipment_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
68 TYPE distribution_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
69 TYPE po_number_tbl_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
70 TYPE source_doc_quantity_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
71 TYPE entered_rec_tax_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
72 TYPE entered_nr_tax_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
73 TYPE accounted_rec_tax_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
74 TYPE accounted_nr_tax_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
75 TYPE accrual_method_flag_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
76 TYPE accounting_line_type_tbl_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
77
78 -----------------------------------------------------------------------------
79 -- Table types for RCV_ACCOUNTING_EVENTS
80 -----------------------------------------------------------------------------
81 TYPE rae_event_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
82 TYPE rae_event_type_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
83 TYPE rae_inv_org_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
84 TYPE rae_po_number_tbl_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
85 TYPE rae_distribution_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
86 TYPE rae_qty_received_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
87 TYPE rae_qty_invoiced_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
88 TYPE rae_unit_pice_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
89 TYPE rae_txn_qty_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
90 TYPE rae_txn_amount_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
91 TYPE rae_currency_code_tbl_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
92 TYPE rae_cur_conv_type_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
93 TYPE rae_cur_conv_rate_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
94 TYPE rae_cur_conv_date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
95
96 TYPE accrual_index_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
97 TYPE encum_index_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
98 /*Bug6987381*/
99 TYPE rae_pnt_event_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
100
101 -----------------------------------------------------------------------------
102 -- PL/SQL tables for RCV_RECEIVING_SUB_LEDGER
103 -----------------------------------------------------------------------------
104 g_rcv_acc_event_id_tbl RCV_ACC_EVENT_ID_TBL_TYPE;
105 g_pnt_rcv_acc_event_id_tbl PNT_RCV_ACC_EVENT_ID_TBL_TYPE;
106 g_actual_flag_tbl ACTUAL_FLAG_TBL_TYPE;
107 g_currency_code_tbl CURRENCY_CODE_TBL_TYPE;
108 g_code_combination_id_tbl CODE_COMBINATION_ID_TBL_TYPE;
109 g_entered_dr_tbl ENTERED_DR_TBL_TYPE;
110 g_entered_cr_tbl ENTERED_CR_TBL_TYPE;
111 g_accounted_dr_tbl ACCOUNTED_DR_TBL_TYPE;
112 g_accounted_cr_tbl ACCOUNTED_CR_TBL_TYPE;
113 g_curr_conversion_date_tbl CURR_CONVERSION_DATE_TBL_TYPE;
114 g_user_curr_conversion_tbl USER_CURR_CONVERSION_TBL_TYPE;
115 g_curr_conversion_rate_tbl CURR_CONVERSION_RATE_TBL_TYPE;
116 g_po_header_id_tbl PO_HEADER_ID_TBL_TYPE;
117 g_shipment_id_tbl SHIPMENT_ID_TBL_TYPE;
118 g_distribution_id_tbl DISTRIBUTION_ID_TBL_TYPE;
119 g_po_number_tbl PO_NUMBER_TBL_TYPE;
120 g_source_doc_quantity_tbl SOURCE_DOC_QUANTITY_TBL_TYPE;
121 g_entered_rec_tax_tbl ENTERED_REC_TAX_TBL_TYPE;
122 g_entered_nr_tax_tbl ENTERED_NR_TAX_TBL_TYPE;
123 g_accounted_rec_tax_tbl ACCOUNTED_REC_TAX_TBL_TYPE;
124 g_accounted_nr_tax_tbl ACCOUNTED_NR_TAX_TBL_TYPE;
125 g_accrual_method_flag_tbl ACCRUAL_METHOD_FLAG_TBL_TYPE;
126 g_accounting_line_type_tbl ACCOUNTING_LINE_TYPE_TBL_TYPE;
127
128 -----------------------------------------------------------------------------
129 -- List of distribution_id, for which Accrued flag has to be set to 'Y'
130 -- The table will be populated by CST_PerEndAccruals_PVT.Create_PerEndAccruals
131 -----------------------------------------------------------------------------
132 g_accrued_dist_id_tbl DISTRIBUTION_ID_TBL_TYPE;
133
134 -----------------------------------------------------------------------------
135 -- PL/SQL tables for RCV_ACCOUNTING_EVENTS
136 -----------------------------------------------------------------------------
137 g_rae_event_id_tbl RAE_EVENT_ID_TBL_TYPE;
138 g_rae_event_type_id_tbl RAE_EVENT_TYPE_ID_TBL_TYPE;
139 g_rae_distribution_id_tbl RAE_DISTRIBUTION_ID_TBL_TYPE;
140 g_rae_inv_org_id_tbl RAE_INV_ORG_ID_TBL_TYPE;
141 g_rae_po_number_tbl RAE_PO_NUMBER_TBL_TYPE;
142 g_rae_qty_received_tbl RAE_QTY_RECEIVED_TBL_TYPE;
143 g_rae_qty_invoiced_tbl RAE_QTY_INVOICED_TBL_TYPE;
144 g_rae_unit_pice_tbl RAE_UNIT_PICE_TBL_TYPE;
145 g_rae_txn_qty_tbl RAE_TXN_QTY_TBL_TYPE;
146 g_rae_txn_amount_tbl RAE_TXN_AMOUNT_TBL_TYPE;
147 g_rae_currency_code_tbl RAE_CURRENCY_CODE_TBL_TYPE;
148 g_rae_cur_conv_type_tbl RAE_CUR_CONV_TYPE_TBL_TYPE;
149 g_rae_cur_conv_rate_tbl RAE_CUR_CONV_RATE_TBL_TYPE;
150 g_rae_cur_conv_date_tbl RAE_CUR_CONV_DATE_TBL_TYPE;
151 /*Bug6987381*/
152 g_rae_pnt_event_id_tbl RAE_PNT_EVENT_ID_TBL_TYPE;
153 -----------------------------------------------------------------------------
154 -- PL/SQL tables, which works as index tables for accounting_event_id PL/SQL table
155 -----------------------------------------------------------------------------
156 g_accrual_index_tbl ACCRUAL_INDEX_TBL_TYPE;
157 g_encum_index_tbl ENCUM_INDEX_TBL_TYPE;
158
159 -----------------------------------------------------------------------------
160 -- Global counter, stores no. of rows in PL/SQL tables for
161 -- RCV_RECEIVING_SUB_LEDGER
162 -----------------------------------------------------------------------------
163 g_counter NUMBER;
164
165
166 -----------------------------------------------------------------------------
167 -- Start of comments
168 -- API name : Start_Process
169 -- Type : Private
170 -- Function : Starting point for Receipt Accruals - Period End
171 -- Concurrent Program.
172 -- Pre-reqs : None.
173 -- Parameters :
174 -- IN :
175 -- p_min_accrual_amount NUMBER Required
176 -- p_vendor_id NUMBER Required
177 -- p_struct_num NUMBER Required
178 -- p_category_id NUMBER Required
179 -- p_period_name VARCHAR2 Required
180 --
181 -- OUT :
182 -- errbuf VARCHAR2
183 -- retcode NUMBER
184 --
185 -- Version : Current version 1.0
186 --
187 -- End of comments
188 -----------------------------------------------------------------------------
189 PROCEDURE Start_Process
190 (
191 errbuf OUT NOCOPY VARCHAR2,
192 retcode OUT NOCOPY NUMBER,
193
194 p_min_accrual_amount IN NUMBER,
195 p_vendor_id IN NUMBER,
196 p_struct_num IN NUMBER,
197 p_category_id IN NUMBER,
198 p_period_name IN VARCHAR2
199 );
200
201 -----------------------------------------------------------------------------
202 -- Start of comments
203 -- API name : Seed_RcvAccountingEvents
204 -- Type : Private
205 -- Function : The procedure created events in RCV_ACCOUNTING_EVENTS
206 -- table.
207 -- The procedure generates data for RAE and creates PL/SQL
208 -- table, which will be used for bulk inserting the data
209 -- in RCV_ACCOUNTING_EVENTS.
210 --
211 -- Pre-reqs : None.
212 -- Parameters :
213 -- IN :
214 -- p_api_version NUMBER Required
215 -- p_init_msg_list VARCHAR2 Required
216 -- p_commit VARCHAR2 Required
217 -- p_validation_level NUMBER Required
218 -- p_sys_setup_rec CST_SYS_SETUP_REC_TYPE Required
219 --
220 -- OUT :
221 -- x_return_status VARCHAR2
222 -- x_msg_count NUMBER
223 -- x_msg_data VARCHAR2
224 --
225 -- Version : Current version 1.0
226 --
227 -- End of comments
228 -----------------------------------------------------------------------------
229 PROCEDURE Seed_RcvAccountingEvents
230 (
231 p_api_version IN NUMBER,
232 p_init_msg_list IN VARCHAR2,
233 p_commit IN VARCHAR2,
234 p_validation_level IN NUMBER,
235
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239
240 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
241 );
242
243 -----------------------------------------------------------------------------
244 -- Start of comments
245 -- API name : Create_AccrualAccount
246 -- Type : Private
247 -- Function : The procedure fetches data from temp table
248 -- CST_PER_END_ACCRUALS_TEMP, and populates PL/SQL tables
249 -- with the corresponding accrual info for the tables
250 -- RCV_RECEIVING_SUB_LEDGER
251 --
252 -- Pre-reqs : None.
253 -- Parameters :
254 -- IN :
255 -- p_api_version NUMBER Required
256 -- p_init_msg_list VARCHAR2 Required
257 -- p_commit VARCHAR2 Required
258 -- p_validation_level NUMBER Required
259 -- p_sys_setup_rec CST_SYS_SETUP_REC_TYPE Required
260 --
261 -- OUT :
262 -- x_return_status VARCHAR2
263 -- x_msg_count NUMBER
264 -- x_msg_data VARCHAR2
265 --
266 -- Version : Current version 1.0
267 --
268 -- End of comments
269 -----------------------------------------------------------------------------
270 PROCEDURE Create_AccrualAccount
271 (
272 p_api_version IN NUMBER,
273 p_init_msg_list IN VARCHAR2,
274 p_commit IN VARCHAR2,
275 p_validation_level IN NUMBER,
276
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_msg_count OUT NOCOPY NUMBER,
279 x_msg_data OUT NOCOPY VARCHAR2,
280
281 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
282 );
283
284 -----------------------------------------------------------------------------
285 -- Start of comments
286 -- API name : Insert_Account
287 -- Type : Private
288 -- Function : The procedure adds a new row to the PL/SQL tables for
289 -- each accrual_info_rec record.
290 --
291 -- Pre-reqs : None.
292 -- Parameters :
293 -- IN :
294 -- p_api_version NUMBER Required
295 -- p_init_msg_list VARCHAR2 Required
296 -- p_commit VARCHAR2 Required
297 -- p_validation_level NUMBER Required
298 -- p_accrual_info_rec CST_ACCRUAL_INFO_REC_TYPE Required
299 -- p_sys_setup_rec CST_SYS_SETUP_REC_TYPE Required
300 --
301 -- OUT :
302 -- x_return_status VARCHAR2
303 -- x_msg_count NUMBER
304 -- x_msg_data VARCHAR2
305 --
306 -- Version : Current version 1.0
307 --
308 -- End of comments
309 -----------------------------------------------------------------------------
310 PROCEDURE Insert_Account
311 (
312 p_api_version IN NUMBER,
313 p_init_msg_list IN VARCHAR2,
314 p_commit IN VARCHAR2,
315 p_validation_level IN NUMBER,
316
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2,
320
321 p_accrual_info_rec IN CST_ACCRUAL_INFO_REC_TYPE,
322 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
323 );
324
325 -----------------------------------------------------------------------------
326 -- Start of comments
327 -- API name : Insert_AccrualSubLedger
328 -- Type : Private
329 -- Function : Insert accounting entries in RCV_RECEIVING_SUB_LEDGER
330 -- Pre-reqs : None.
331 -- Parameters :
332 -- IN :
333 -- p_api_version NUMBER Required
334 -- p_init_msg_list VARCHAR2 Required
335 -- p_commit VARCHAR2 Required
336 -- p_validation_level NUMBER Required
337 -- p_sys_setup_rec CST_SYS_SETUP_REC_TYPE Required
338 --
339 -- OUT :
340 -- x_return_status VARCHAR2
341 -- x_msg_count NUMBER
342 -- x_msg_data VARCHAR2
343 --
344 -- Version : Current version 1.0
345 --
346 -- End of comments
347 -----------------------------------------------------------------------------
348 PROCEDURE Insert_AccrualSubLedger
349 (
350 p_api_version IN NUMBER,
351 p_init_msg_list IN VARCHAR2,
352 p_commit IN VARCHAR2,
353 p_validation_level IN NUMBER,
354
355 x_return_status OUT NOCOPY VARCHAR2,
356 x_msg_count OUT NOCOPY NUMBER,
357 x_msg_data OUT NOCOPY VARCHAR2,
358
359 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
360 );
361
362 -----------------------------------------------------------------------------
363 -- Start of comments
364 -- API name : Get_SystemSetup
365 -- Type : Private
366 -- Function : Get system set-up information e.g. set_of_books,
367 -- functional_currency, chart_of_accounts,
368 -- purchase_encumbrance_flag etc
369 --
370 -- Pre-reqs : None.
371 -- Parameters :
372 -- IN :
373 -- p_api_version NUMBER Required
374 -- p_init_msg_list VARCHAR2 Required
375 -- p_validation_level NUMBER Required
376 -- p_period_name VARCHAR2 Required
377 --
378 -- OUT :
379 -- x_return_status VARCHAR2
380 -- x_msg_count NUMBER
381 -- x_msg_data VARCHAR2
382 -- x_sys_setup_rec CST_SYS_SETUP_REC_TYPE
383 --
384 -- Version : Current version 1.0
385 --
386 -- End of comments
387 -----------------------------------------------------------------------------
388 PROCEDURE Get_SystemSetup
389 (
390 p_api_version IN NUMBER,
391 p_init_msg_list IN VARCHAR2,
392 p_validation_level IN NUMBER,
393
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2,
397
398 p_period_name IN VARCHAR2,
399 x_sys_setup_rec OUT NOCOPY CST_SYS_SETUP_REC_TYPE
400 );
401
402 END CST_ReceiptAccrualPerEnd_PVT;
[Home] [Help]