1 PACKAGE ARI_DB_UTILITIES AS
2 /* $Header: ARIDBUTLS.pls 120.5.12010000.2 2008/11/10 10:33:44 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 END ARI_DB_UTILITIES;