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