4
1 PACKAGE ARPT_SQL_FUNC_UTIL AUTHID CURRENT_USER AS
2 /* $Header: ARTUSSFS.pls 120.14.12020000.3 2012/07/10 00:55:37 dgaurab ship $ */
3
5
6 FUNCTION get_cb_invoice( p_customer_trx_id IN number,
7 p_class IN varchar2)
8 RETURN VARCHAR2;
9
10
11
12 FUNCTION get_dispute_amount( p_customer_trx_id IN number,
13 p_class IN varchar2,
14 p_open_receivable_flag IN varchar2)
15 RETURN NUMBER;
16
17
18
19 FUNCTION get_dispute_date( p_customer_trx_id IN number,
20 p_class IN varchar2,
21 p_open_receivable_flag IN
22 varchar2)
23 RETURN DATE;
24
25
26
27 FUNCTION get_max_dispute_date( p_customer_trx_id IN number,
28 p_class IN varchar2,
29 p_open_receivable_flag IN varchar2)
30 RETURN DATE;
31
32
33
34 FUNCTION get_revenue_recog_run_flag( p_customer_trx_id IN number,
35 p_invoicing_rule_id IN number)
36 RETURN VARCHAR2;
37
38 FUNCTION get_nonmodal_row_exist_flag( p_customer_trx_id IN number,
39 p_invoicing_rule_id IN number)
40 RETURN VARCHAR2;
41
42
43 FUNCTION get_posted_flag( p_customer_trx_id IN number,
44 p_post_to_gl_flag IN varchar2,
45 p_complete_flag IN varchar2,
46 p_class IN varchar2 DEFAULT NULL) RETURN VARCHAR2;
47
48
49 FUNCTION get_selected_for_payment_flag( p_customer_trx_id IN number,
50 p_open_receivables_flag IN varchar2,
51 p_complete_flag IN varchar2)
52 RETURN VARCHAR2;
53
54
55 FUNCTION get_activity_flag( p_customer_trx_id IN number,
56 p_open_receivables_flag IN varchar2,
57 p_complete_flag IN varchar2,
58 p_class IN varchar2,
59 p_initial_customer_trx_id IN number,
60 p_previous_customer_trx_id IN number
61 )
62 RETURN VARCHAR2;
63
64
65
66 FUNCTION Get_Reference( p_trx_rowid IN ROWID)
67 RETURN varchar2;
68
72
69 FUNCTION Get_Line_Reference( p_line_trx_rowid IN ROWID)
70 RETURN varchar2;
71
73 PROCEDURE Set_Reference_Column(p_reference_column IN varchar2);
74
75 FUNCTION Get_First_Due_Date( p_term_id IN number,
76 p_trx_date IN date)
77 RETURN DATE;
78
79
80 FUNCTION Get_First_Real_Due_Date( p_customer_trx_id IN number,
81 p_term_id IN number,
82 p_trx_date IN date)
83 RETURN DATE;
84
85
86 FUNCTION Get_Number_Of_Due_Dates( p_term_id IN number)
87 RETURN NUMBER;
88
89
90 FUNCTION get_period_name( p_gl_date IN DATE )
91 RETURN VARCHAR2;
92
93
94 FUNCTION get_territory( p_address_id IN NUMBER )
95 RETURN VARCHAR2;
96
97
98 FUNCTION get_territory_rowid( p_address_id IN NUMBER )
99 RETURN ROWID;
100
101
102
103 FUNCTION get_commitments_exist_flag(
104 p_bill_to_customer_id IN number,
105 p_invoice_currency_code IN varchar2,
106 p_previous_customer_trx_id IN number,
107 p_trx_date IN date,
108 p_ct_prev_initial_cust_trx_id IN number
109 DEFAULT NULL,
110 p_code_combination_id_gain IN number
111 DEFAULT NULL,
112 p_base_currency IN varchar2
113 DEFAULT NULL)
114 RETURN varchar2;
115
116 FUNCTION get_agreements_exist_flag(
117 p_bill_to_customer_id IN number,
118 p_trx_date IN date )
119 RETURN varchar2;
120
121 FUNCTION get_override_terms(
122 p_customer_id IN number,
123 p_site_use_id IN NUMBER ) RETURN varchar2;
124
125
126
127 FUNCTION get_bs_name_for_cb_invoice ( p_class IN varchar2,
128 p_customer_trx_id number
129 ) RETURN VARCHAR2;
130
131
132 FUNCTION get_dunning_date_last (p_payment_schedule_id
133 IN ar_correspondence_pay_sched.payment_schedule_id%type)
134 RETURN DATE;
135
136 TYPE t_ar_lookups_table IS TABLE OF VARCHAR2(80)
137 INDEX BY BINARY_INTEGER;
138
139 pg_ar_lookups_rec t_ar_lookups_table;
140
141 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
142 p_lookup_code IN VARCHAR2)
143 RETURN VARCHAR2;
144
145 --Add for bug 12955526
146 FUNCTION get_fndlookup_meaning (p_lookup_type IN VARCHAR2,
147 p_lookup_code IN VARCHAR2)
148 RETURN VARCHAR2;
149
150 TYPE srep_rec_type IS RECORD
151 (salesrep_name ra_salesreps.name%TYPE,
152 salesrep_number ra_salesreps.salesrep_number%TYPE);
153
154 TYPE t_salesrep_table IS TABLE OF srep_rec_type
155 INDEX BY BINARY_INTEGER;
156
157 pg_salesrep_rec t_salesrep_table;
158
159 FUNCTION get_salesrep_name_number (p_salesrep_id IN NUMBER,
160 p_name_number IN VARCHAR2,
161 --begin anuj
162 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
163 p_org_id IN NUMBER DEFAULT NULL)
164 /* Multi-Org Access Control Changes for SSA;end;anukumar;11/01/2002*/
165 --end anuj
166
167 RETURN VARCHAR2;
168
169 /* Bug 2544852 : increase size of territory_short_name from 60 to 80 */
170
171 TYPE address_rec_type IS RECORD
172 (add1 VARCHAR2(240),
173 add2 VARCHAR2(240),
174 add3 VARCHAR2(240),
175 add4 VARCHAR2(240),
176 city VARCHAR2(60),
177 state VARCHAR2(60),
178 province VARCHAR2(60),
179 territory_short_name VARCHAR2(80),
180 postal_code VARCHAR2(60),
181 country VARCHAR2(60),
182 status VARCHAR2(1));
183
184 TYPE t_address_table IS TABLE OF address_rec_type
185 INDEX BY BINARY_INTEGER;
186
187 pg_address_rec t_address_table;
188
189 FUNCTION get_address_details (p_address_id IN NUMBER,
190 p_detail_type IN VARCHAR2)
191 RETURN VARCHAR2;
192 TYPE phone_rec_type IS RECORD
193 (phone_number hz_contact_points.phone_number%TYPE,
194 area_code hz_contact_points.phone_area_code%TYPE,
195 extension hz_contact_points.phone_extension%TYPE);
196
197 TYPE t_phone_table IS TABLE OF phone_rec_type
198 INDEX BY BINARY_INTEGER;
199
200 pg_phone_rec t_phone_table;
201
202 FUNCTION get_phone_details (p_phone_id IN NUMBER,
203 p_detail_type IN VARCHAR2)
204 RETURN VARCHAR2;
205
206 /* Bug fix 3655704 */
207 FUNCTION is_max_rowid (p_rowid IN ROWID)
208 RETURN VARCHAR2;
209
210
211 TYPE term_rec_type IS RECORD
212 (name ra_terms.name%TYPE,
213 calc_disc_on_lines_flag ra_terms.calc_discount_on_lines_flag%TYPE,
214 partial_discount_flag ra_terms.partial_discount_flag%TYPE);
215
216 TYPE t_term_table IS TABLE OF term_rec_type
217 INDEX BY BINARY_INTEGER;
218
219 pg_term_rec t_term_table;
220
224
221 FUNCTION get_term_details (p_term_id IN NUMBER,
222 p_detail_type IN VARCHAR2)
223 RETURN VARCHAR2;
225 TYPE agreement_rec_type IS RECORD
226 (name so_agreements.name%type,
227 start_date_active so_agreements.start_date_active%type,
228 end_date_active so_agreements.end_date_active%type,
229 is_valid_date VARCHAR2(10));
230
231 TYPE t_agreement_table IS TABLE OF agreement_rec_type
232 INDEX BY BINARY_INTEGER;
233
234 pg_agreement_rec t_agreement_table;
235
236 FUNCTION is_agreement_date_valid(p_trx_date IN DATE,
237 p_agreement_id IN NUMBER)
238 RETURN VARCHAR2;
239
240 FUNCTION get_agreement_name(p_agreement_id IN NUMBER)
241 RETURN VARCHAR2;
242
243 TYPE trx_type_rec_type IS RECORD
244 (name ra_cust_trx_types.name%type,
245 type ra_cust_trx_types.type%type,
246 subseq_trx_type_id ra_cust_trx_types.subsequent_trx_type_id%type,
247 allow_overapplication_flag ra_cust_trx_types.allow_overapplication_flag%type,
248 natural_application_only_flag ra_cust_trx_types.natural_application_only_flag%type,
249 creation_sign ra_cust_trx_types.creation_sign%type,
250 post_to_gl ra_cust_trx_types.post_to_gl%type);
251
252 TYPE t_trx_type_table IS TABLE OF trx_type_rec_type
253 INDEX BY BINARY_INTEGER;
254
255 pg_trx_type_rec t_trx_type_table;
256
257
258 /* Bug fix 5462362 */
259
260 FUNCTION get_trx_type_details(p_trx_type_id IN NUMBER,
261 p_detail_type IN VARCHAR2,
262 p_org_id IN NUMBER default NULL)
263 RETURN VARCHAR2;
264
265 FUNCTION check_iclaim_installed
266 RETURN VARCHAR2;
267
268 FUNCTION get_orig_gl_date(p_customer_trx_id IN NUMBER)
269 RETURN DATE;
270
271 FUNCTION get_sum_of_trx_lines(p_customer_trx_id IN NUMBER,
272 p_line_type IN VARCHAR2)
273 RETURN NUMBER;
274
275 FUNCTION get_balance_due_as_of_date(p_applied_payment_schedule_id in number,
276 p_as_of_date in date,
277 p_class in varchar2)
278 RETURN NUMBER;
279
280 FUNCTION bucket_function(p_buck_line_typ varchar2,
281 p_amt_in_disp NUMBER,
282 p_amt_adj_pen NUMBER,
283 p_days_from NUMBER,
284 p_days_to NUMBER,
285 p_due_date DATE,
286 p_bucket_category VARCHAR2,
287 p_as_of DATE)
288
289 RETURN number;
290
291 pragma restrict_references(get_balance_due_as_of_date, WNDS, WNPS);
292
293 /* bug 2362943 : added new functions :
294 get_bill_id, get_stmt_cycle, get_send_stmt */
295 FUNCTION get_bill_id(p_site_use_id IN NUMBER)
296 RETURN NUMBER;
297
298 FUNCTION get_stmt_cycle(p_site_use_id IN NUMBER)
299 RETURN NUMBER;
300
301 FUNCTION get_send_stmt(p_site_use_id IN NUMBER)
302 RETURN VARCHAR2;
303
304 FUNCTION get_cred_bal(p_site_use_id IN NUMBER)
305 RETURN VARCHAR2;
306
307 FUNCTION get_claim_amount(p_claim_id IN NUMBER)
308 RETURN NUMBER;
309
310 /*Bug3820605 */
311 TYPE get_name_type IS
312 TABLE OF VARCHAR2(100)
313 INDEX BY BINARY_INTEGER;
314
315 TYPE get_id_type IS
316 TABLE OF VARCHAR2(100)
317 INDEX BY BINARY_INTEGER;
318
319 pg_get_hash_name_cache get_name_type;
320 pg_get_line_name_cache get_name_type;
321 pg_get_hash_id_cache get_id_type;
322 pg_get_line_id_cache get_id_type;
323
324 tab_size NUMBER := 0;
325
326 FUNCTION get_org_trx_type_details(p_trx_type_id IN NUMBER,
327 p_org_id IN NUMBER)
328 RETURN VARCHAR2 ;
329
330 -- Bug 4221745
331 FUNCTION get_rec_trx_type(p_rec_trx_id IN NUMBER,
332 p_detail_type IN VARCHAR2 DEFAULT 'TYPE')
333 RETURN VARCHAR2;
334
335 FUNCTION check_BOE_paymeth(p_receipt_method_id IN NUMBER)
336 RETURN VARCHAR2;
337
338 /* Bug 4761373 : Transferred from ARTATULS.pls
339 New function Get_currency_code has been added for the bug 3043128 */
340
341 Function GET_CURRENCY_CODE(p_application_type in varchar2,
342 p_status in varchar2,
343 p_ard_source_type in varchar2,
344 p_cr_currency_code in varchar2,
345 p_inv_currency_code in varchar2)
346 RETURN VARCHAR2;
347
348 END ARPT_SQL_FUNC_UTIL;