1 PACKAGE arp_util AS
2 /*$Header: ARCUTILS.pls 120.8 2006/06/29 17:42:39 shveeram ship $*/
3
4 -- Update server-patch level here :
5
6 PG_AR_SERVER_PATCH_LEVEL VARCHAR2(30) ; -- 1759719
7
8
9 -----------------------------------------------------------------------------
10 -- Debugging functions
11 -----------------------------------------------------------------------------
12 PROCEDURE enable_debug;
13 PROCEDURE enable_debug( buffer_size NUMBER );
14 PROCEDURE disable_debug;
15 PROCEDURE debug( line IN VARCHAR2 ) ;
16 PROCEDURE debug( str VARCHAR2, print_level NUMBER );
17 PROCEDURE print_fcn_label( p_label VARCHAR2 );
18 PROCEDURE print_fcn_label2( p_label VARCHAR2 );
19
20
21 -----------------------------------------------------------------------------
22 -- Amount functions
23 -----------------------------------------------------------------------------
24 FUNCTION functional_amount(amount IN NUMBER,
25 currency_code IN VARCHAR2,
26 exchange_rate IN NUMBER,
27 precision IN NUMBER,
28 min_acc_unit IN NUMBER) RETURN NUMBER ;
29
30
31 --Added function for Bug 501260
32 FUNCTION func_amount(amount IN NUMBER,
33 currency_code IN VARCHAR2,
34 exchange_rate IN NUMBER,
35 precision IN NUMBER,
36 min_acc_unit IN NUMBER) RETURN NUMBER ;
37
38
39 FUNCTION calc_dynamic_amount(
40 p_amount IN NUMBER,
41 p_exchange_rate IN NUMBER,
42 p_currency_code IN fnd_currencies.currency_code%TYPE )
43 RETURN NUMBER;
44
45 FUNCTION CurrRound( p_amount IN NUMBER,
46 p_currency_code IN VARCHAR2) RETURN NUMBER;
47
48
49 -- This function is the PL/SQL equivalent of aracc() in PRO*C.
50 -- It determines the accounted amounts (detail and master) for
51 -- a master and detail amount.
52
53 PROCEDURE calc_acctd_amount(
54 p_currency IN VARCHAR2,
55 p_precision IN NUMBER,
56 p_mau IN NUMBER,
57 p_rate IN NUMBER,
58 p_type IN VARCHAR2,
59 p_master_from IN NUMBER,
60 p_acctd_master_from IN OUT NOCOPY NUMBER,
61 p_detail IN NUMBER,
62 p_master_to IN OUT NOCOPY NUMBER,
63 p_acctd_master_to IN OUT NOCOPY NUMBER,
64 p_acctd_detail IN OUT NOCOPY NUMBER );
65
66 ---Added function for Bug 501260
67 PROCEDURE calc_accounted_amount(
68 p_currency IN VARCHAR2,
69 p_precision IN NUMBER,
70 p_mau IN NUMBER,
71 p_rate IN NUMBER,
72 p_type IN VARCHAR2,
73 p_master_from IN NUMBER,
74 p_acctd_master_from IN OUT NOCOPY NUMBER,
75 p_detail IN NUMBER,
76 p_master_to IN OUT NOCOPY NUMBER,
77 p_acctd_master_to IN OUT NOCOPY NUMBER,
78 p_acctd_detail IN OUT NOCOPY NUMBER );
79
80 /* ==================================================================================
81 | PROCEDURE Set_Buckets
82 |
83 | DESCRIPTION
84 | Sets accounted amount base for tax, charges, freight, line
85 | from amount buckets of the Receivable application or adjustment.
86 | We do not store accounted amounts for individual buckets in the
87 | payment schedule or on application or adjustment. Hence the accounted
88 | amounts are derived by this routine in order, Tax, Charges, Line and
89 | Freight by using the foreign currency amounts and multiplying with the
90 | exchange rate to get the base or functional currency accounted amounts
91 | with the rounding correction going to the last non zero amount
92 | bucket in that order. This is the standard that has been established and
93 | the same algorithm must be used to retain consistency. The usage came
94 | into being during the Tax accounting for Discounts and Adjustments,
95 | however in future projects this will be required. This could not be
96 | derived as an effect on payment schedule becuause the payment schedules
97 | are update before or after activity by various modules. In addition
98 | depending on the bucket which is first choosen to be calculated the
99 | rounding correction is different and goes to the last bucket. The
100 | approach by this routine is the most desirable way to do things.
101 |
102 | SCOPE - PUBLIC
103 |
104 | PARAMETERS
105 | p_header_acctd_amt IN Header accounted amount to reconcile
106 | p_base_currency IN Base or functional currency
107 | p_exchange_rate IN Exchange rate
108 | p_base_precision IN Base precision
109 | p_base_min_acc_unit IN Minimum accountable unit
110 | p_tax_amt IN Tax amount in currency of Transaction
111 | p_charges_amt IN Charges amount in currency of Transaction
112 | p_freight_amt IN Freight amount in currency of Transaction
113 | p_line_amt IN Line amount in currency of Transaction
114 | p_tax_acctd_amt IN OUT NOCOPY Tax accounted amount in functional currency
115 | p_charges_acctd_amt IN OUT NOCOPY Charges accounted amount in functional currency
116 | p_freight_acctd_amt IN OUT NOCOPY Freight accounted amount in functional currency
117 | p_line_acctd_amt IN OUT NOCOPY Line accounted amount in functional currency
118 |
119 | Notes
120 | Introduced for 11.5 Tax accounting - used by ARALLOCB.pls and ARTWRAPB.pls
121 *===================================================================================*/
122 PROCEDURE Set_Buckets(
123 p_header_acctd_amt IN NUMBER ,
124 p_base_currency IN fnd_currencies.currency_code%TYPE,
125 p_exchange_rate IN NUMBER ,
126 p_base_precision IN NUMBER ,
127 p_base_min_acc_unit IN NUMBER ,
128 p_tax_amt IN NUMBER ,
129 p_charges_amt IN NUMBER ,
130 p_line_amt IN NUMBER ,
131 p_freight_amt IN NUMBER ,
132 p_tax_acctd_amt IN OUT NOCOPY NUMBER ,
133 p_charges_acctd_amt IN OUT NOCOPY NUMBER ,
134 p_line_acctd_amt IN OUT NOCOPY NUMBER ,
135 p_freight_acctd_amt IN OUT NOCOPY NUMBER );
136
137 -- This function is only used to test calc_acctd_amount:
138
139 PROCEDURE calc_acctd_amount_test;
140
141 -----------------------------------------------------------------------------
142 -- Date functions
143 -----------------------------------------------------------------------------
144 --
145 -- This is a just a stub to call the validate_and_default_gl_date in
146 -- ARP_STANDARD package
147 --
148 FUNCTION validate_and_default_gl_date(
149 gl_date in date,
150 trx_date in date,
151 validation_date1 in date,
152 validation_date2 in date,
153 validation_date3 in date,
154 default_date1 in date,
155 default_date2 in date,
156 default_date3 in date,
157 p_allow_not_open_flag in varchar2,
158 p_invoicing_rule_id in varchar2,
159 p_set_of_books_id in number,
160 p_application_id in number,
161 default_gl_date out NOCOPY date,
162 defaulting_rule_used out NOCOPY varchar2,
163 error_message out NOCOPY varchar2
164 ) RETURN BOOLEAN;
165
166 --
167 -- overloaded function to return period name
168 --
169 FUNCTION validate_and_default_gl_date(
170 gl_date in date,
171 trx_date in date,
172 validation_date1 in date,
173 validation_date2 in date,
174 validation_date3 in date,
175 default_date1 in date,
176 default_date2 in date,
177 default_date3 in date,
178 p_allow_not_open_flag in varchar2,
179 p_invoicing_rule_id in varchar2,
180 p_set_of_books_id in number,
181 p_application_id in number,
182 default_gl_date out NOCOPY date,
183 defaulting_rule_used out NOCOPY varchar2,
184 error_message out NOCOPY varchar2,
185 p_period_name out NOCOPY varchar2
186 ) RETURN BOOLEAN;
187
188 --
189 FUNCTION is_gl_date_valid( p_gl_date IN DATE ) RETURN BOOLEAN;
190 --
191 FUNCTION is_gl_date_valid( p_gl_date IN DATE,
192 p_allow_not_open_flag IN VARCHAR ) RETURN BOOLEAN;
193 --
194 PROCEDURE validate_gl_date( p_gl_date IN DATE,
195 p_module_name IN VARCHAR2,
196 p_module_version IN VARCHAR2 );
197 --
198 -- overloaded function to return period name
199 --
200 PROCEDURE validate_gl_date( p_gl_date IN DATE,
201 p_module_name IN VARCHAR2,
202 p_module_version IN VARCHAR2,
203 p_period_name OUT NOCOPY varchar2 );
204
205 -----------------------------------------------------------------------------
206 -- Misc functions
207 -----------------------------------------------------------------------------
208 PROCEDURE close_cursor( p_cursor_handle IN OUT NOCOPY NUMBER );
209
210
211 -----------------------------------------------------------------------------
212 -- Function to support server-side patch-level identification
213 -----------------------------------------------------------------------------
214 FUNCTION ar_server_patch_level RETURN VARCHAR2;
215
216 TYPE attribute_rec_type IS RECORD(
217 attribute_category VARCHAR2(30) DEFAULT NULL,
218 attribute1 VARCHAR2(150) DEFAULT NULL,
219 attribute2 VARCHAR2(150) DEFAULT NULL,
220 attribute3 VARCHAR2(150) DEFAULT NULL,
221 attribute4 VARCHAR2(150) DEFAULT NULL,
222 attribute5 VARCHAR2(150) DEFAULT NULL,
223 attribute6 VARCHAR2(150) DEFAULT NULL,
224 attribute7 VARCHAR2(150) DEFAULT NULL,
225 attribute8 VARCHAR2(150) DEFAULT NULL,
226 attribute9 VARCHAR2(150) DEFAULT NULL,
227 attribute10 VARCHAR2(150) DEFAULT NULL,
228 attribute11 VARCHAR2(150) DEFAULT NULL,
229 attribute12 VARCHAR2(150) DEFAULT NULL,
230 attribute13 VARCHAR2(150) DEFAULT NULL,
231 attribute14 VARCHAR2(150) DEFAULT NULL,
232 attribute15 VARCHAR2(150) DEFAULT NULL);
233
234 PROCEDURE Validate_Desc_Flexfield(
235 p_desc_flex_rec IN OUT NOCOPY arp_util.attribute_rec_type,
236 p_desc_flex_name IN VARCHAR2,
237 p_return_status IN OUT NOCOPY varchar2);
238
239 --This function will get the ID when you pass the corresponding number/or name
240 -- for an entity.The following entitiy can be passed to get the corresponding ID
241 --CUSTOMER_NUMBER,CUSTOMER_NAME,RECEIPT_METHOD_NAME,CUST_BANK_ACCOUNT_NUMBER
242 --CUST_BANK_ACCOUNT_NAME,REMIT_BANK_ACCOUNT_NUMBER,REMIT_BANK_ACCOUNT_NAME,
243 --CURRENCY_NAME,
244
245 FUNCTION Get_Id(
246 p_entity IN VARCHAR2,
247 p_value IN VARCHAR2,
248 p_return_status OUT NOCOPY VARCHAR2
249 ) RETURN VARCHAR2;
250
251
252 -- This function returns the sum of the ra_interface_lines.promised_commitment_
253 -- amount for a given transaction. Since the interface data is transient,
254 -- this function is really only useful during AutoInvoice processing.
255
256 FUNCTION Get_Promised_Amount(
257 p_customer_trx_id IN NUMBER,
258 p_alloc_tax_freight IN VARCHAR2
259 ) RETURN NUMBER;
260 --This procedure will substitute the balancing segment
261
262 PROCEDURE Substitute_Ccid(p_coa_id IN gl_sets_of_books.chart_of_accounts_id%TYPE ,
263 p_original_ccid IN ar_system_parameters.code_combination_id_gain%TYPE,
264 p_subs_ccid IN ar_system_parameters.code_combination_id_gain%TYPE,
265 p_actual_ccid OUT NOCOPY ar_system_parameters.code_combination_id_gain%TYPE);
266
267 --
268 -- Following procedure is for executing dynamic sql from forms
269 --
270 PROCEDURE Dynamic_Select (p_query IN VARCHAR2,
271 p_result OUT NOCOPY VARCHAR2);
272
273 --
274 -- kmahajan - 4th Aug 2003 - New utility function to retrieve the Start and
275 -- End dates of the time-period to be considered for Sales Group LOVs
276 --
277
278 PROCEDURE Get_Txn_Start_End_Dates (
279 p_customer_trx_id IN NUMBER,
280 p_start_date OUT NOCOPY DATE,
281 p_end_date OUT NOCOPY DATE
282 );
283
284 --
285 -- kmahajan - 25th Aug 2003 - New utility functions that serve as wrappers
286 -- for the JTF function to return a Default Sales Group given a Sales Rep
287 -- and effective date
288 --
289
290 FUNCTION Get_Default_SalesGroup (
291 p_salesrep_id IN NUMBER,
292 p_org_id IN NUMBER,
293 p_date IN DATE
294 ) RETURN NUMBER;
295
296 FUNCTION Get_Default_SalesGroup (
297 p_salesrep_id IN NUMBER,
298 p_customer_trx_id IN NUMBER
299 ) RETURN NUMBER;
300
301 /* Bug fix 4942083:
302 The accounting reports will be run for a GL date range. If within this date range, there
303 is a period which is not Closed or Close Pending, this function will return TRUE. Else
304 this function will return FALSE */
305
306 FUNCTION Open_Period_Exists(
307 p_reporting_level IN VARCHAR2,
308 p_reporting_entity_id IN NUMBER,
309 p_gl_date_from IN DATE,
310 p_gl_date_to IN DATE
311 ) RETURN BOOLEAN ;
312
313 FUNCTION Open_Period_Exists(
314 p_reporting_level IN VARCHAR2,
315 p_reporting_entity_id IN NUMBER,
316 p_in_as_of_date_low IN DATE
317 ) RETURN BOOLEAN ;
318
319 END arp_util;