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