4 type number_table_type is table of NUMBER index by binary_integer;
1 PACKAGE AP_UTILITIES_PKG AUTHID CURRENT_USER AS
2 /* $Header: aputilss.pls 120.28 2011/02/09 01:18:50 pgayen ship $ */
3
5 --
6
7 /*Bug 7172942 Natural Segment Caching */
8
9 TYPE g_natural_acct_seg_rec IS RECORD (natural_acct_seg GL_CODE_COMBINATIONS.SEGMENT1%TYPE);
10
11 TYPE g_natural_acct_seg_tab IS TABLE OF g_natural_acct_seg_rec INDEX BY VARCHAR2(100);
12
13 g_natural_acct_seg_t g_natural_acct_seg_tab;
14
15
16 /* Bug 5572876. Period Name Caching */
17 TYPE g_curr_period_name_rec IS RECORD (period_name gl_period_statuses.period_name%TYPE);
18
19 TYPE g_curr_period_name_tab IS TABLE OF g_curr_period_name_rec INDEX BY VARCHAR2(30);
20
21 g_curr_period_name_t g_curr_period_name_tab;
22
23 TYPE g_open_period_name_rec IS RECORD (period_name gl_period_statuses.period_name%TYPE,
24 start_date date);
25
29
26 TYPE g_open_period_name_tab IS TABLE OF g_open_period_name_rec INDEX BY VARCHAR2(30);
27
28 g_open_period_name_t g_open_period_name_tab;
30 /* Bug 5572876. Asset Book Caching */
31 TYPE g_asset_book_code_rec IS RECORD (asset_book_code fa_book_controls.book_type_code%TYPE);
32
33 TYPE g_asset_book_code_tab IS TABLE OF g_asset_book_code_rec INDEX BY BINARY_INTEGER;
34
35 g_asset_book_code_t g_asset_book_code_tab;
36
37 /* Bug 5572876. Curreny Related data Caching */
38 TYPE g_fnd_currency_rec IS RECORD
39 (currency_code FND_CURRENCIES.currency_code%TYPE,
40 minimum_accountable_unit FND_CURRENCIES.minimum_accountable_unit%TYPE,
41 precision FND_CURRENCIES.precision%TYPE);
42
43 TYPE g_fnd_currency_tab IS TABLE OF g_fnd_currency_rec INDEX BY VARCHAR2(15);
44
45 g_fnd_currency_code_t g_fnd_currency_tab;
46
47 /* Bug 8713737 Added r_invoice_attribute_rec for passing attribute columns
48 to PA APIs */
49
50 TYPE r_invoice_attribute_rec IS RECORD (
51 attribute_category ap_invoices_all.attribute_category%TYPE,
52 attribute1 ap_invoices_all.attribute1%TYPE,
53 attribute2 ap_invoices_all.attribute2%TYPE,
54 attribute3 ap_invoices_all.attribute3%TYPE,
55 attribute4 ap_invoices_all.attribute4%TYPE,
56 attribute5 ap_invoices_all.attribute5%TYPE,
57 attribute6 ap_invoices_all.attribute6%TYPE,
58 attribute7 ap_invoices_all.attribute7%TYPE,
59 attribute8 ap_invoices_all.attribute8%TYPE,
60 attribute9 ap_invoices_all.attribute9%TYPE,
61 attribute10 ap_invoices_all.attribute10%TYPE,
62 attribute11 ap_invoices_all.attribute11%TYPE,
63 attribute12 ap_invoices_all.attribute12%TYPE,
64 attribute13 ap_invoices_all.attribute13%TYPE,
65 attribute14 ap_invoices_all.attribute14%TYPE,
66 attribute15 ap_invoices_all.attribute15%TYPE,
67 line_attribute_category ap_invoice_lines_all.attribute_category%TYPE,
68 line_attribute1 ap_invoice_lines_all.attribute1%TYPE,
69 line_attribute2 ap_invoice_lines_all.attribute2%TYPE,
70 line_attribute3 ap_invoice_lines_all.attribute3%TYPE,
71 line_attribute4 ap_invoice_lines_all.attribute4%TYPE,
72 line_attribute5 ap_invoice_lines_all.attribute5%TYPE,
73 line_attribute6 ap_invoice_lines_all.attribute6%TYPE,
74 line_attribute7 ap_invoice_lines_all.attribute7%TYPE,
75 line_attribute8 ap_invoice_lines_all.attribute8%TYPE,
76 line_attribute9 ap_invoice_lines_all.attribute9%TYPE,
77 line_attribute10 ap_invoice_lines_all.attribute10%TYPE,
78 line_attribute11 ap_invoice_lines_all.attribute11%TYPE,
79 line_attribute12 ap_invoice_lines_all.attribute12%TYPE,
80 line_attribute13 ap_invoice_lines_all.attribute13%TYPE,
81 line_attribute14 ap_invoice_lines_all.attribute14%TYPE,
82 line_attribute15 ap_invoice_lines_all.attribute15%TYPE
83 );
84
85 /*Bug11720134*/
86 g_org_id NUMBER DEFAULT mo_global.get_current_org_id;
87
88 Function Ledger_Asset_Book (P_ledger_id IN Number) Return Varchar2;
89
90 function Ap_Get_Displayed_Field
91 (LookupType IN varchar2
92 ,LookupCode IN varchar2
93 ) return varchar2;
94 --
95 function Ap_Round_Currency
96 (P_Amount IN number
97 ,P_Currency_Code IN varchar2
98 ) return number;
99 --pragma restrict_references(Ap_Round_Currency, WNDS, WNPS, RNPS);
100
101 function Ap_Round_Tax
102 (P_Amount IN number
103 ,P_Currency_Code IN varchar2
104 ,P_Round_Rule IN varchar2
105 ,P_Calling_Sequence IN varchar2
106 ) return number;
107 function Ap_Round_Non_Rec_Tax
108 (P_Amount IN number
109 ,P_Currency_Code IN varchar2
110 ,P_Round_Rule IN varchar2
111 ,P_Calling_Sequence IN varchar2
112 ) return number;
113
114 function Ap_Round_Precision
115 (P_Amount IN number
116 ,P_Min_unit IN number
117 ,P_Precision IN number
118 ) return number;
119 FUNCTION net_invoice_amount(p_invoice_id IN NUMBER) RETURN NUMBER; -- Added by Bug:2022200
120
121 --PRAGMA RESTRICT_REFERENCES (Ap_Round_Precision, WNDS, WNPS, RNPS);
122
123 -- MOAC. Added org_id parameter
124 function get_current_gl_date (P_Date IN date,
125 P_Org_Id IN number default
126 mo_global.get_current_org_id) return varchar2;
127
128 function get_gl_period_name (P_Date IN date,
129 P_Org_Id IN number default
130 mo_global.get_current_org_id) return varchar2;
131
132
133 -- MOAC. Added org_id parameter
134 procedure get_open_gl_date
135 (P_Date IN date
136 ,P_Period_Name OUT NOCOPY varchar2
137 ,P_GL_Date OUT NOCOPY date
138 ,P_Org_Id IN number DEFAULT
139 mo_global.get_current_org_id);
140
141 procedure get_only_open_gl_date
142 (P_Date IN date
143 ,P_Period_Name OUT NOCOPY varchar2
147 -- PRAGMA RESTRICT_REFERENCES(get_only_open_gl_date, WNDS);
144 ,P_GL_Date OUT NOCOPY date
145 ,P_Org_Id IN number DEFAULT
146 mo_global.get_current_org_id);
148 -- PRAGMA RESTRICT_REFERENCES(get_open_gl_date, WNDS, RNPS, WNPS);
149 -- PRAGMA RESTRICT_REFERENCES(get_current_gl_date, WNDS, RNPS, WNPS);
150
151
152 FUNCTION get_exchange_rate(
153 p_from_currency_code IN varchar2,
154 p_to_currency_code IN varchar2,
155 p_exchange_rate_type IN varchar2,
156 p_exchange_date IN date,
157 p_calling_sequence IN varchar2) RETURN NUMBER;
158 --pragma restrict_references(Get_exchange_rate, WNDS, WNPS, RNPS);
159
160 PROCEDURE Set_Profile(p_profile_option IN vARCHAR2,
161 p_profile_value IN VARCHAR2);
162
163 PROCEDURE AP_Get_Message(p_err_txt OUT NOCOPY VARCHAR2);
164
165 --MO Access Control
166 FUNCTION Get_Window_Title RETURN VARCHAR2;
167
168 FUNCTION Get_Window_Session_Title RETURN VARCHAR2;
169 --pragma restrict_references(Get_Window_Session_Title, WNDS);
170
171 FUNCTION overlay_segments (
172 p_balancing_segment IN VARCHAR2,
173 p_cost_center_segment IN VARCHAR2,
174 p_account_segment IN VARCHAR2,
175 p_concatenated_segments IN VARCHAR2,
176 p_ccid IN OUT NOCOPY NUMBER,
177 p_set_of_books_id IN NUMBER,
178 p_overlay_mode IN VARCHAR2,
179 p_unbuilt_flex OUT NOCOPY VARCHAR2,
180 p_reason_unbuilt_flex OUT NOCOPY VARCHAR2,
181 p_resp_appl_id IN NUMBER,
182 p_resp_id IN NUMBER,
183 p_user_id IN NUMBER,
184 p_calling_sequence IN VARCHAR2,
185 p_ccid_to_segs IN VARCHAR2 Default Null,
186 p_accounting_date IN DATE DEFAULT SYSDATE) --7531219
187 RETURN BOOLEAN;
188
189 --following function added for BUG 1909374
190 FUNCTION overlay_segments_by_gldate (
191 p_balancing_segment IN VARCHAR2,
192 p_cost_center_segment IN VARCHAR2,
193 p_account_segment IN VARCHAR2,
194 p_concatenated_segments IN VARCHAR2,
195 p_ccid IN OUT NOCOPY NUMBER,
196 p_accounting_date IN DATE,
197 p_set_of_books_id IN NUMBER,
198 p_overlay_mode IN VARCHAR2,
199 p_unbuilt_flex OUT NOCOPY VARCHAR2,
200 p_reason_unbuilt_flex OUT NOCOPY VARCHAR2,
201 p_resp_appl_id IN NUMBER,
202 p_resp_id IN NUMBER,
203 p_user_id IN NUMBER,
204 p_calling_sequence IN VARCHAR2,
205 p_ccid_to_segs IN VARCHAR2 Default Null)
206 RETURN BOOLEAN;
207
208
209 FUNCTION check_partial(
210 p_concatenated_segments IN VARCHAR2,
211 p_partial_segments_flag OUT NOCOPY VARCHAR2,
212 p_set_of_books_id IN NUMBER,
213 p_error_message OUT NOCOPY VARCHAR2,
214 p_calling_sequence IN VARCHAR2)
215 RETURN BOOLEAN;
216
217 FUNCTION is_ccid_valid (
218 p_ccid IN NUMBER,
219 p_chart_of_accounts_id IN NUMBER,
220 p_date IN DATE,
221 p_calling_sequence IN VARCHAR2)
222 RETURN BOOLEAN;
223
224 --MO Access Control: Added the p_org_id parameter.
225 FUNCTION get_inventory_org(p_org_id number default mo_global.get_current_org_id) RETURN NUMBER;
226
227
228 PROCEDURE mc_flag_enabled ( p_sob_id IN NUMBER,
229 p_appl_id IN NUMBER,
230 p_org_id IN NUMBER,
231 p_fa_book_code IN VARCHAR2,
232 p_base_currency IN VARCHAR2,
233 p_mc_flag_enabled OUT NOCOPY VARCHAR2,
234 p_calling_sequence IN VARCHAR2);
235
236 function AP_Get_Sob_Order_Col(
237 P_Primary_SOB_ID IN number
238 ,P_Secondary_SOB_ID IN number
239 ,P_SOB_ID IN number
240 ,P_ORG_ID IN number
241 ,P_Calling_Sequence IN varchar2
242 ) return number;
243
244 function get_charge_account(
245 p_ccid IN number
246 , p_chart_of_accounts_id IN number
247 , p_calling_sequence IN varchar2
248 ) return varchar2;
249
250 function get_invoice_status(p_invoice_id IN NUMBER,
251 p_calling_sequence IN VARCHAR2
252 ) return varchar2;
253
254 PROCEDURE build_offset_account(P_base_ccid IN NUMBER
255 ,P_overlay_ccid IN NUMBER
256 ,P_accounting_date IN DATE
257 ,P_result_ccid OUT NOCOPY NUMBER
258 ,P_Reason_Unbuilt_Flex OUT NOCOPY VARCHAR2
259 ,P_calling_sequence IN VARCHAR2);
260
261 function get_auto_offsets_segments(
262 P_base_ccid IN NUMBER
263 )return varchar2;
264
265 FUNCTION get_auto_offsets_segments
266 (P_base_ccid IN NUMBER,
270 ) return varchar2;
267 P_flex_qualifier_name IN VARCHAR2,
268 P_flex_segment_num IN NUMBER,
269 P_chart_of_accts_id IN GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE
271
272 FUNCTION delete_invoice_from_interface(p_invoice_id_table IN number_table_type,
273 p_invoice_line_id_table IN number_table_type,
274 p_calling_sequence IN VARCHAR2
275 ) return boolean;
276
277
278 --Added function for exchange rate calculation project.
279 FUNCTION calculate_user_xrate(P_invoice_curr IN VARCHAR2,
280 P_base_curr IN VARCHAR2,
281 P_exchange_date IN DATE,
282 P_exchange_rate_type IN VARCHAR2
283 ) return VARCHAR2;
284
285 FUNCTION get_gl_batch_name(P_batch_id IN NUMBER,
286 P_GL_SL_link_id IN NUMBER,
287 P_ledger_id IN NUMBER) return VARCHAR2;
288
289 -- Bug 2249806
290 -- Code modified by MSWAMINA
291 -- Added a new stored function for the performance reasons
292 -- This will get the Lookup code and the lookup type
293 -- as the input arguments and will return the corresponding
294 -- Meaning as output.
295 -- This function is created inorder to avoid the reparsing
296 -- of these simple/common SQLs in our reports.
297
298 FUNCTION FND_Get_Displayed_Field
299 (LookupType IN VARCHAR2
300 ,LookupCode IN VARCHAR2
301 ) RETURN VARCHAR2;
302
303 -- Bug 2693900. Forward porting Bug 2610252.
304 -- Bug 5584997. Added the P_org_id
305 function get_reversal_gl_date (P_Date IN date, P_Org_Id IN Number) return date;
306
307 -- Bug 5584997. Added the P_org_id
308 function get_reversal_period (P_Date IN date, P_Org_Id IN Number) return Varchar2;
309
310 /* =======================================================================*/
311 /* New Function pa_flexbuild was created for in the scope of the Invoice */
312 /* Lines Project - Stage 1 */
313 /* =======================================================================*/
314
315 FUNCTION pa_flexbuild(
316 p_vendor_id IN NUMBER,
317 p_employee_id IN NUMBER,
318 p_set_of_books_id IN NUMBER,
319 p_chart_of_accounts_id IN NUMBER,
320 p_base_currency_code IN VARCHAR2,
321 p_Accounting_date IN DATE,
322 p_award_id IN NUMBER,
323 P_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
324 p_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
325 p_expenditure_type IN
326 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
327 p_expenditure_org_id IN
328 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
329 p_expenditure_item_date IN
330 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
331 p_invoice_attribute_rec IN AP_UTILITIES_PKG.r_invoice_attribute_rec, --bug 8713737
332 p_billable_flag IN VARCHAR2, --Bug6523162
333 p_employee_ccid IN NUMBER, --Bug5003249
334 p_web_parameter_id IN NUMBER, --Bug5003249
335 p_invoice_type_lookup_code IN VARCHAR2, --Bug5003249
336 p_default_last_updated_by IN NUMBER,
337 p_default_last_update_login IN NUMBER,
338 p_pa_default_dist_ccid OUT NOCOPY NUMBER,
339 p_pa_concatenated_segments OUT NOCOPY VARCHAR2,
340 p_debug_Info OUT NOCOPY VARCHAR2,
341 p_debug_Context OUT NOCOPY VARCHAR2,
342 p_calling_sequence IN VARCHAR2,
343 p_default_dist_ccid IN AP_INVOICE_LINES.DEFAULT_DIST_CCID%TYPE --bug 5386396
344 ) RETURN BOOLEAN;
345
346
347 PROCEDURE Get_Invoice_LE (
348 p_vendor_site_id IN NUMBER,
349 p_inv_liab_ccid IN NUMBER,
350 p_org_id IN NUMBER,
351 p_le_id OUT NOCOPY NUMBER);
352
353 FUNCTION Get_Check_LE (
354 p_bank_acct_use_id IN NUMBER) RETURN NUMBER;
355
356 PROCEDURE getInvoiceLEInfo (
357 p_vendor_site_id IN NUMBER,
358 p_inv_liab_ccid IN NUMBER,
359 p_org_id IN NUMBER,
360 p_le_id OUT NOCOPY NUMBER,
361 p_le_name OUT NOCOPY VARCHAR2,
362 p_le_registration_num OUT NOCOPY VARCHAR2,
363 p_le_address1 OUT NOCOPY VARCHAR2,
364 p_le_city OUT NOCOPY VARCHAR2,
365 p_le_postal_code OUT NOCOPY VARCHAR2,
366 p_le_country OUT NOCOPY VARCHAR2);
367
368 PROCEDURE Delete_AP_Profiles
369 (P_Profile_Option_Name IN VARCHAR2);
370
371 FUNCTION PERIOD_STATUS (p_gl_date IN DATE) -- 3881457
372 RETURN VARCHAR2;
373
374 /*This function will take in a clob and write it to the concurrent manager file.
375 It can be used in conjunction with dbms_xmlgen to generate xml output for a
376 concurrent request.*/
377 PROCEDURE clob_to_file
378 (p_xml_clob IN CLOB);
379
380 FUNCTION pa_period_status(
381 p_gl_date IN DATE,
382 p_org_id IN number default
383 mo_global.get_current_org_id) RETURN varchar2;
384
385 FUNCTION Get_PO_Reversed_Encumb_Amount(
386 P_Po_Distribution_Id IN NUMBER,
387 P_Start_gl_Date IN DATE,
388 P_End_gl_Date IN DATE,
389 P_Calling_Sequence IN VARCHAR2 DEFAULT NULL)
390
391 RETURN NUMBER;
392
393
394 --Function get_ccr_status, added for the R12 FSIO gap--
395 --Bug6053476
396 FUNCTION get_ccr_status(P_object_id IN NUMBER,
397 P_object_type IN VARCHAR2
398 ) return VARCHAR2;
399
400 --Function get_gl_natural_account added for Bug 6980939
401 FUNCTION get_gl_natural_account(
402 p_coa_id IN NUMBER,
403 p_ccid IN NUMBER,
404 P_calling_sequence IN VARCHAR2 DEFAULT NULL)
405 RETURN VARCHAR2;
406
407 -- bug 7531219
408 -- Function to validate balancing segment to the ledger
409 FUNCTION is_balancing_segment_valid (
410 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%type,
411 p_balancing_segment_value IN gl_ledger_segment_values.segment_value%type,
412 p_date IN DATE,
413 p_calling_sequence IN VARCHAR2)
414 RETURN BOOLEAN;
415
416 -- Added for bug 8408345.
417
418 PROCEDURE get_gl_date_and_period_1(
419 P_Date IN DATE,
420 P_Receipt_Date IN DATE DEFAULT NULL,
421 P_Period_Name OUT NOCOPY VARCHAR2,
422 P_GL_Date OUT NOCOPY DATE,
423 P_Batch_GL_Date IN DATE DEFAULT NULL,
424 P_Org_Id IN NUMBER DEFAULT
425 MO_GLOBAL.GET_CURRENT_ORG_ID);
426
427 function get_current_gl_date_no_cache (P_Date IN date,
428 P_Org_Id IN number default
429 mo_global.get_current_org_id) return varchar2;
430
431 procedure get_open_gl_date_no_cache
432 (P_Date IN date
433 ,P_Period_Name OUT NOCOPY varchar2
434 ,P_GL_Date OUT NOCOPY date
435 ,P_Org_Id IN number DEFAULT
436 mo_global.get_current_org_id);
437 -- End bug 8408345.
438
439 --Start 8691645
440
441 FUNCTION get_ccr_reg_status(p_vendor_site_id IN
442 AP_INVOICES.VENDOR_SITE_ID%TYPE)
443 return VARCHAR2;
444
445 --End 8691645
446
447 END AP_UTILITIES_PKG;
[Home] [Help]