1 PACKAGE ARI_DB_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: ARIDBUTLS.pls 120.5.12010000.7 2010/04/14 12:26:29 avepati ship $ */
3
4 /*=======================================================================+
5 | Declare PUBLIC Data Types and Variables
6 +=======================================================================*/
7
8
9 /*=======================================================================+
10 | Declare PUBLIC Exceptions
11 +=======================================================================*/
12
13
14
15 /*========================================================================
16 | PUBLIC procedure oir_calc_aging_buckets
17 |
18 | DESCRIPTION
19 | This procedure performs aging calculations within the context
20 | of a customer, site, currency and an aging bucket style.
21 | ----------------------------------------
22 |
23 | PSEUDO CODE/LOGIC
24 |
25 | PARAMETERS
26 | p_customer_id Customer ID
27 | p_as_of_date As of when the calculations are performed.
28 | p_currency_code Currency Code
29 | p_credit_option Age/Not Age Credits
30 | p_invoice_type_low
31 | p_invoice_type_high
32 | p_ps_max_id
33 | p_app_max_id
34 | p_bucket_name Aging Bucket Defination to use.
35 | p_session_id Added for All Location Enhancement
36 |
37 | RETURNS
38 | p_outstanding_balance Account Balance
39 | p_bucket_titletop_0 Bucket i's Title
40 | p_bucket_titlebottom_0
41 | p_bucket_amount_0 Bucket i's Amount
42 | p_bucket_titletop_1
43 | p_bucket_titlebottom_1
44 | p_bucket_amount_1
45 | p_bucket_titletop_2
46 | p_bucket_titlebottom_2
47 | p_bucket_amount_2
48 | p_bucket_titletop_3
49 | p_bucket_titlebottom_3
50 | p_bucket_amount_3
51 | p_bucket_titletop_4
52 | p_bucket_titlebottom_4
53 | p_bucket_amount_4
54 | p_bucket_titletop_5
55 | p_bucket_titlebottom_5
56 | p_bucket_amount_5
57 | p_bucket_titletop_6
58 | p_bucket_titlebottom_6
59 | p_bucket_amount_6
60 | p_bucket_status_code0 Status Codes used in Acct. Details
61 | p_bucket_status_code1 Status Poplist
62 | p_bucket_status_code2
63 | p_bucket_status_code3
64 | p_bucket_status_code4
65 | p_bucket_status_code5
66 | p_bucket_status_code6
67 |
68 | KNOWN ISSUES
69 |
70 |
71 |
72 | NOTES
73 |
74 |
75 |
76 | MODIFICATION HISTORY
77 | Date Author Description of Changes
78 | 26-Oct-2002 J. Albowicz Created
79 |
80 *=======================================================================*/
81
82 PROCEDURE oir_calc_aging_buckets (
83 p_customer_id IN NUMBER,
84 p_as_of_date IN DATE,
85 p_currency_code IN VARCHAR2,
86 p_credit_option IN VARCHAR2,
87 p_invoice_type_low IN VARCHAR2,
88 p_invoice_type_high IN VARCHAR2,
89 p_ps_max_id IN NUMBER DEFAULT 0,
90 p_app_max_id IN NUMBER DEFAULT 0,
91 p_bucket_name IN VARCHAR2,
92 p_outstanding_balance IN OUT NOCOPY VARCHAR2,
93 p_bucket_titletop_0 OUT NOCOPY VARCHAR2,
94 p_bucket_titlebottom_0 OUT NOCOPY VARCHAR2,
95 p_bucket_amount_0 IN OUT NOCOPY VARCHAR2,
96 p_bucket_titletop_1 OUT NOCOPY VARCHAR2,
97 p_bucket_titlebottom_1 OUT NOCOPY VARCHAR2,
98 p_bucket_amount_1 IN OUT NOCOPY VARCHAR2,
99 p_bucket_titletop_2 OUT NOCOPY VARCHAR2,
100 p_bucket_titlebottom_2 OUT NOCOPY VARCHAR2,
101 p_bucket_amount_2 IN OUT NOCOPY VARCHAR2,
102 p_bucket_titletop_3 OUT NOCOPY VARCHAR2,
103 p_bucket_titlebottom_3 OUT NOCOPY VARCHAR2,
104 p_bucket_amount_3 IN OUT NOCOPY VARCHAR2,
105 p_bucket_titletop_4 OUT NOCOPY VARCHAR2,
106 p_bucket_titlebottom_4 OUT NOCOPY VARCHAR2,
107 p_bucket_amount_4 IN OUT NOCOPY VARCHAR2,
108 p_bucket_titletop_5 OUT NOCOPY VARCHAR2,
109 p_bucket_titlebottom_5 OUT NOCOPY VARCHAR2,
110 p_bucket_amount_5 IN OUT NOCOPY VARCHAR2,
111 p_bucket_titletop_6 OUT NOCOPY VARCHAR2,
112 p_bucket_titlebottom_6 OUT NOCOPY VARCHAR2,
113 p_bucket_amount_6 IN OUT NOCOPY VARCHAR2,
114 p_bucket_status_code0 OUT NOCOPY VARCHAR2,
115 p_bucket_status_code1 OUT NOCOPY VARCHAR2,
116 p_bucket_status_code2 OUT NOCOPY VARCHAR2,
117 p_bucket_status_code3 OUT NOCOPY VARCHAR2,
118 p_bucket_status_code4 OUT NOCOPY VARCHAR2,
119 p_bucket_status_code5 OUT NOCOPY VARCHAR2,
120 p_bucket_status_code6 OUT NOCOPY VARCHAR2,
121 p_session_id IN NUMBER
122 );
123
124
125 /*========================================================================
126 | PUBLIC procedure get_site_info
127 |
128 | DESCRIPTION
129 | Serves as a wrapper to arw_db_utilities. This wrapper was created
130 | to solve a performance issue encountered when there is a customer
131 | account with a large number of sites. Basically, the overhead of
132 | calling the arw_db_utilities individually for all rows of the result
133 | set was too much; the solution was to call the arw_db_utilities only
134 | for the portion of the result set that is displayed.
135 | ----------------------------------------
136 |
137 | PSEUDO CODE/LOGIC
138 |
139 | PARAMETERS
140 | p_customer_id Customer ID
141 | p_addr_id Address ID
142 | p_site_use Type of site (All, bill to, ship to, etc.)
143 |
144 | RETURNS
145 | p_contact_name Primary Contact for that (Account, Site) tuple
146 | p_contact_phone Phone number for Primary Contact
147 | p_site_uses Concatenated string of the site's uses.
148 | p_bill_to_site_use_id Returns 0 if the site is not a Bill To.
149 |
150 | KNOWN ISSUES
151 |
152 |
153 |
154 | NOTES
155 |
156 |
157 |
158 | MODIFICATION HISTORY
159 | Date Author Description of Changes
160 | 12-Jun-2003 J. Albowicz Created
161 |
162 *=======================================================================*/
163
164
165 PROCEDURE get_site_info(p_customer_id IN NUMBER,
166 p_addr_id IN NUMBER DEFAULT NULL,
167 p_site_use IN VARCHAR2 DEFAULT 'ALL',
168 p_contact_name OUT NOCOPY VARCHAR,
169 p_contact_phone OUT NOCOPY VARCHAR,
170 p_site_uses OUT NOCOPY VARCHAR,
171 p_bill_to_site_use_id OUT NOCOPY VARCHAR);
172
173
174 /*========================================================================
175 | PUBLIC procedure get_print_request_url
176 |
177 | DESCRIPTION
178 | This procedure is used to get the status of the print request and
179 | and also its URL.
180 |
181 | PSEUDO CODE/LOGIC
182 |
183 | PARAMETERS
184 | p_request_id The print request ID
185 | p_gwyuid The gateway user ID
186 | p_two_task The value of TWO_TASK
187 | p_user_id The user ID
188 |
189 | RETURNS
190 | p_output_url The output URL for the request
191 | p_status The status of the print request
192 |
193 | KNOWN ISSUES
194 |
195 |
196 |
197 | NOTES
198 |
199 |
200 |
201 | MODIFICATION HISTORY
202 | Date Author Description of Changes
203 | 08-Aug-2003 yashaskar Created
204 |
205 *=======================================================================*/
206
207 PROCEDURE get_print_request_url(
208 p_request_id IN NUMBER,
209 p_gwyuid IN VARCHAR2,
210 p_two_task IN VARCHAR2,
211 p_user_id IN NUMBER,
212 p_output_url OUT NOCOPY VARCHAR2,
213 p_status OUT NOCOPY VARCHAR2 );
214
215
216 /*========================================================================
217 | PUBLIC procedure oir_bpa_print_invoices
218 |
219 | DESCRIPTION
220 | This procedure is used to submit the print request to BPA and also
221 | inserts the record in ar_irec_print_requests table.
222 |
223 | PSEUDO CODE/LOGIC
224 |
225 | PARAMETERS
226 | p_id_list The ids to be submitted
227 | p_list_type List type
228 | p_description Description
229 | p_template_id Template id
230 | p_customer_id Customer id
231 | p_site_id Customer Site Use Id
232 |
233 | RETURNS
234 | x_req_id_list Request Id
235 |
236 | KNOWN ISSUES
237 |
238 |
239 |
240 | NOTES
241 |
242 |
243 |
244 | MODIFICATION HISTORY
245 | Date Author Description of Changes
246 | 06-May-2005 rsinthre Created
247 |
248 *=======================================================================*/
249 PROCEDURE oir_bpa_print_invoices(
250 p_id_list IN VARCHAR2,
251 x_req_id_list OUT NOCOPY VARCHAR2,
252 p_list_type IN VARCHAR2,
253 p_description IN VARCHAR2 ,
254 p_customer_id IN NUMBER,
255 p_customer_site_id IN NUMBER DEFAULT NULL,
256 p_user_name IN VARCHAR2
257 );
258
259 /*========================================================================
260 | PUBLIC procedure oir_invoice_print_selected_invoices
261 |
262 | DESCRIPTION
263 | This procedure submits cuncurrent request to print the
264 | selected invoices .The notification is sent to the user
265 | who has submited this request .
266 | ----------------------------------------
267 |
268 | PSEUDO CODE/LOGIC
269 |
270 | PARAMETERS
271 | p_resp_name Responsibility Name
272 | p_user_name User Name
273 | p_random_invoices_flag Randomly selected invoices or a range of invoices
274 | p_invoice_list_string Customer_trx_ids of all selected invoices
275 |
276 | RETURNS
277 | p_request_id Request ID
278 |
279 | KNOWN ISSUES
280 |
281 |
282 |
283 | NOTES
284 |
285 |
286 |
287 | MODIFICATION HISTORY
288 | Date Author Description of Changes
289 | 21-Jun-2009 avepati Created
290 |
291 *=======================================================================*/
292
293
294 PROCEDURE oir_print_selected_invoices(
295 p_resp_name IN VARCHAR2,
296 p_user_name IN VARCHAR2,
297 p_org_id IN NUMBER,
298 p_random_invoices_flag IN VARCHAR2,
299 p_invoice_list_string IN VARCHAR2,
300 p_customer_id IN VARCHAR2,
301 p_customer_site_id IN VARCHAR2,
302 p_request_id OUT NOCOPY NUMBER );
303
304 /*========================================================================
305 | PUBLIC procedure upload_ar_bank_branch_concur
306 |
307 | DESCRIPTION
308 | This procedure submits cuncurrent request to upload AR_BANK_DIRECTORY
309 | table data to HZ_PARTIES.
310 | --------------------------------------------------------------------
311 |
312 | PSEUDO CODE/LOGIC
313 |
314 | PARAMETERS
315 | NONE
316 |
317 | RETURNS
318 | ERRBUF Error Data
319 | RETCODE Return Status
320 |
321 | KNOWN ISSUES
322 |
323 | NOTES
324 |
325 | MODIFICATION HISTORY
326 | Date Author Description of Changes
327 | 24-sep-2009 avepati Created
328 |
329 *=======================================================================*/
330
331
332 PROCEDURE upload_ar_bank_branch_concur(ERRBUF OUT NOCOPY VARCHAR2,
333 RETCODE OUT NOCOPY VARCHAR2,
334 p_import_new_banks_only IN VARCHAR2);
335
336 /*========================================================================
337 | PUBLIC procedure PURGE_IREC_PRINT_REQUESTS
338 |
339 | DESCRIPTION
340 | This procedure submits cuncurrent request to purge AR_IREC_PRINT_REQUESTS
341 | table data matching the purge process of FND_CONCURRENT_REQUESTS table.
342 | --------------------------------------------------------------------
343 |
344 | PSEUDO CODE/LOGIC
345 |
346 | PARAMETERS
347 | NONE
348 |
349 | RETURNS
350 | ERRBUF Error Data
351 | RETCODE Return Status
352 |
353 | KNOWN ISSUES
354 |
355 | NOTES
356 |
357 | MODIFICATION HISTORY
358 | Date Author Description of Changes
359 | 02-Apr-2010 avepati Created
360 |
361 *=======================================================================*/
362
363
364 PROCEDURE PURGE_IREC_PRINT_REQUESTS(ERRBUF OUT NOCOPY VARCHAR2,
365 RETCODE OUT NOCOPY VARCHAR2,
366 p_creation_date in varchar2);
367
368 /*========================================================================
369 | PUBLIC procedure PURGE_IREC_PRINT_REQUESTS
370 |
371 | DESCRIPTION
372 | This procedure submits cuncurrent request to purge PURGE_IREC_USER_ACCT_SITES_ALL
373 | --------------------------------------------------------------------
374 |
375 | PSEUDO CODE/LOGIC
376 |
377 | PARAMETERS
378 | NONE
379 |
380 | RETURNS
381 | ERRBUF Error Data
382 | RETCODE Return Status
383 | p_creation_date purge date
384 |
385 | KNOWN ISSUES
386 |
387 | NOTES
388 |
389 | MODIFICATION HISTORY
390 | Date Author Description of Changes
391 | 14-Apr-2010 avepati Created
392 |
393 *=======================================================================*/
394
395
396 PROCEDURE PURGE_IREC_USER_ACCT_SITES_ALL(ERRBUF OUT NOCOPY VARCHAR2,
397 RETCODE OUT NOCOPY VARCHAR2,
398 p_creation_date in varchar2);
399
400 END ARI_DB_UTILITIES;