1 PACKAGE AP_UTILITIES_PKG AS
2 /* $Header: aputilss.pls 120.19.12010000.2 2008/08/08 03:55:56 sparames ship $ */
3
4 type number_table_type is table of NUMBER index by binary_integer;
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
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;
29
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 Function Ledger_Asset_Book (P_ledger_id IN Number) Return Varchar2;
48
49 function Ap_Get_Displayed_Field
50 (LookupType IN varchar2
51 ,LookupCode IN varchar2
52 ) return varchar2;
53 --
54 function Ap_Round_Currency
55 (P_Amount IN number
56 ,P_Currency_Code IN varchar2
57 ) return number;
58 --pragma restrict_references(Ap_Round_Currency, WNDS, WNPS, RNPS);
59
60 function Ap_Round_Tax
61 (P_Amount IN number
62 ,P_Currency_Code IN varchar2
63 ,P_Round_Rule IN varchar2
64 ,P_Calling_Sequence IN varchar2
65 ) return number;
66 function Ap_Round_Non_Rec_Tax
67 (P_Amount IN number
68 ,P_Currency_Code IN varchar2
69 ,P_Round_Rule IN varchar2
70 ,P_Calling_Sequence IN varchar2
71 ) return number;
72
73 function Ap_Round_Precision
74 (P_Amount IN number
75 ,P_Min_unit IN number
76 ,P_Precision IN number
77 ) return number;
78 FUNCTION net_invoice_amount(p_invoice_id IN NUMBER) RETURN NUMBER; -- Added by Bug:2022200
79
80 --PRAGMA RESTRICT_REFERENCES (Ap_Round_Precision, WNDS, WNPS, RNPS);
81
82 -- MOAC. Added org_id parameter
83 function get_current_gl_date (P_Date IN date,
84 P_Org_Id IN number default
85 mo_global.get_current_org_id) return varchar2;
86
87 function get_gl_period_name (P_Date IN date,
88 P_Org_Id IN number default
89 mo_global.get_current_org_id) return varchar2;
90
91
92 -- MOAC. Added org_id parameter
93 procedure get_open_gl_date
94 (P_Date IN date
95 ,P_Period_Name OUT NOCOPY varchar2
96 ,P_GL_Date OUT NOCOPY date
97 ,P_Org_Id IN number DEFAULT
98 mo_global.get_current_org_id);
99
100 procedure get_only_open_gl_date
101 (P_Date IN date
102 ,P_Period_Name OUT NOCOPY varchar2
103 ,P_GL_Date OUT NOCOPY date
104 ,P_Org_Id IN number DEFAULT
105 mo_global.get_current_org_id);
106 -- PRAGMA RESTRICT_REFERENCES(get_only_open_gl_date, WNDS);
107 -- PRAGMA RESTRICT_REFERENCES(get_open_gl_date, WNDS, RNPS, WNPS);
108 -- PRAGMA RESTRICT_REFERENCES(get_current_gl_date, WNDS, RNPS, WNPS);
109
110
111 FUNCTION get_exchange_rate(
112 p_from_currency_code IN varchar2,
113 p_to_currency_code IN varchar2,
114 p_exchange_rate_type IN varchar2,
115 p_exchange_date IN date,
116 p_calling_sequence IN varchar2) RETURN NUMBER;
117 --pragma restrict_references(Get_exchange_rate, WNDS, WNPS, RNPS);
118
119 PROCEDURE Set_Profile(p_profile_option IN vARCHAR2,
120 p_profile_value IN VARCHAR2);
121
122 PROCEDURE AP_Get_Message(p_err_txt OUT NOCOPY VARCHAR2);
123
124 --MO Access Control
125 FUNCTION Get_Window_Title RETURN VARCHAR2;
126
127 FUNCTION Get_Window_Session_Title RETURN VARCHAR2;
128 --pragma restrict_references(Get_Window_Session_Title, WNDS);
129
130 FUNCTION overlay_segments (
131 p_balancing_segment IN VARCHAR2,
132 p_cost_center_segment IN VARCHAR2,
133 p_account_segment IN VARCHAR2,
134 p_concatenated_segments IN VARCHAR2,
135 p_ccid IN OUT NOCOPY NUMBER,
136 p_set_of_books_id IN NUMBER,
137 p_overlay_mode IN VARCHAR2,
138 p_unbuilt_flex OUT NOCOPY VARCHAR2,
139 p_reason_unbuilt_flex OUT NOCOPY VARCHAR2,
140 p_resp_appl_id IN NUMBER,
141 p_resp_id IN NUMBER,
142 p_user_id IN NUMBER,
143 p_calling_sequence IN VARCHAR2,
144 p_ccid_to_segs IN VARCHAR2 Default Null)
145 RETURN BOOLEAN;
146
147 --following function added for BUG 1909374
148 FUNCTION overlay_segments_by_gldate (
149 p_balancing_segment IN VARCHAR2,
150 p_cost_center_segment IN VARCHAR2,
151 p_account_segment IN VARCHAR2,
152 p_concatenated_segments IN VARCHAR2,
153 p_ccid IN OUT NOCOPY NUMBER,
154 p_accounting_date IN DATE,
155 p_set_of_books_id IN NUMBER,
156 p_overlay_mode IN VARCHAR2,
157 p_unbuilt_flex OUT NOCOPY VARCHAR2,
158 p_reason_unbuilt_flex OUT NOCOPY VARCHAR2,
159 p_resp_appl_id IN NUMBER,
160 p_resp_id IN NUMBER,
161 p_user_id IN NUMBER,
162 p_calling_sequence IN VARCHAR2,
163 p_ccid_to_segs IN VARCHAR2 Default Null)
164 RETURN BOOLEAN;
165
166
167 FUNCTION check_partial(
168 p_concatenated_segments IN VARCHAR2,
169 p_partial_segments_flag OUT NOCOPY VARCHAR2,
170 p_set_of_books_id IN NUMBER,
171 p_error_message OUT NOCOPY VARCHAR2,
172 p_calling_sequence IN VARCHAR2)
173 RETURN BOOLEAN;
174
175 FUNCTION is_ccid_valid (
176 p_ccid IN NUMBER,
177 p_chart_of_accounts_id IN NUMBER,
178 p_date IN DATE,
179 p_calling_sequence IN VARCHAR2)
180 RETURN BOOLEAN;
181
182 --MO Access Control: Added the p_org_id parameter.
183 FUNCTION get_inventory_org(p_org_id number default mo_global.get_current_org_id) RETURN NUMBER;
184
185
186 PROCEDURE mc_flag_enabled ( p_sob_id IN NUMBER,
187 p_appl_id IN NUMBER,
188 p_org_id IN NUMBER,
189 p_fa_book_code IN VARCHAR2,
190 p_base_currency IN VARCHAR2,
191 p_mc_flag_enabled OUT NOCOPY VARCHAR2,
192 p_calling_sequence IN VARCHAR2);
193
194 function AP_Get_Sob_Order_Col(
195 P_Primary_SOB_ID IN number
196 ,P_Secondary_SOB_ID IN number
197 ,P_SOB_ID IN number
198 ,P_ORG_ID IN number
199 ,P_Calling_Sequence IN varchar2
200 ) return number;
201
202 function get_charge_account(
203 p_ccid IN number
204 , p_chart_of_accounts_id IN number
205 , p_calling_sequence IN varchar2
206 ) return varchar2;
207
208 function get_invoice_status(p_invoice_id IN NUMBER,
209 p_calling_sequence IN VARCHAR2
210 ) return varchar2;
211
212 PROCEDURE build_offset_account(P_base_ccid IN NUMBER
213 ,P_overlay_ccid IN NUMBER
214 ,P_accounting_date IN DATE
215 ,P_result_ccid OUT NOCOPY NUMBER
216 ,P_Reason_Unbuilt_Flex OUT NOCOPY VARCHAR2
217 ,P_calling_sequence IN VARCHAR2);
218
219 function get_auto_offsets_segments(
220 P_base_ccid IN NUMBER
221 )return varchar2;
222
223 FUNCTION get_auto_offsets_segments
224 (P_base_ccid IN NUMBER,
225 P_flex_qualifier_name IN VARCHAR2,
226 P_flex_segment_num IN NUMBER,
227 P_chart_of_accts_id IN GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE
228 ) return varchar2;
229
230 FUNCTION delete_invoice_from_interface(p_invoice_id_table IN number_table_type,
231 p_invoice_line_id_table IN number_table_type,
232 p_calling_sequence IN VARCHAR2
233 ) return boolean;
234
235
236 --Added function for exchange rate calculation project.
237 FUNCTION calculate_user_xrate(P_invoice_curr IN VARCHAR2,
238 P_base_curr IN VARCHAR2,
239 P_exchange_date IN DATE,
240 P_exchange_rate_type IN VARCHAR2
241 ) return VARCHAR2;
242
243 FUNCTION get_gl_batch_name(P_batch_id IN NUMBER,
244 P_GL_SL_link_id IN NUMBER,
245 P_ledger_id IN NUMBER) return VARCHAR2;
246
247 -- Bug 2249806
248 -- Code modified by MSWAMINA
249 -- Added a new stored function for the performance reasons
250 -- This will get the Lookup code and the lookup type
251 -- as the input arguments and will return the corresponding
252 -- Meaning as output.
253 -- This function is created inorder to avoid the reparsing
254 -- of these simple/common SQLs in our reports.
255
256 FUNCTION FND_Get_Displayed_Field
257 (LookupType IN VARCHAR2
258 ,LookupCode IN VARCHAR2
259 ) RETURN VARCHAR2;
260
261 -- Bug 2693900. Forward porting Bug 2610252.
262 -- Bug 5584997. Added the P_org_id
263 function get_reversal_gl_date (P_Date IN date, P_Org_Id IN Number) return date;
264
265 -- Bug 5584997. Added the P_org_id
266 function get_reversal_period (P_Date IN date, P_Org_Id IN Number) return Varchar2;
267
268 /* =======================================================================*/
269 /* New Function pa_flexbuild was created for in the scope of the Invoice */
270 /* Lines Project - Stage 1 */
271 /* =======================================================================*/
272
273 FUNCTION pa_flexbuild(
274 p_vendor_id IN NUMBER,
275 p_employee_id IN NUMBER,
276 p_set_of_books_id IN NUMBER,
277 p_chart_of_accounts_id IN NUMBER,
278 p_base_currency_code IN VARCHAR2,
279 p_Accounting_date IN DATE,
280 p_award_id IN NUMBER,
281 P_project_id IN AP_INVOICE_DISTRIBUTIONS.PROJECT_ID%TYPE,
282 p_task_id IN AP_INVOICE_DISTRIBUTIONS.TASK_ID%TYPE,
283 p_expenditure_type IN
284 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_TYPE%TYPE,
285 p_expenditure_org_id IN
286 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ORGANIZATION_ID%TYPE,
287 p_expenditure_item_date IN
288 AP_INVOICE_DISTRIBUTIONS.EXPENDITURE_ITEM_DATE%TYPE,
289 p_employee_ccid IN NUMBER, --Bug5003249
290 p_web_parameter_id IN NUMBER, --Bug5003249
291 p_invoice_type_lookup_code IN VARCHAR2, --Bug5003249
292 p_default_last_updated_by IN NUMBER,
293 p_default_last_update_login IN NUMBER,
294 p_pa_default_dist_ccid OUT NOCOPY NUMBER,
295 p_pa_concatenated_segments OUT NOCOPY VARCHAR2,
296 p_debug_Info OUT NOCOPY VARCHAR2,
297 p_debug_Context OUT NOCOPY VARCHAR2,
298 p_calling_sequence IN VARCHAR2,
299 p_default_dist_ccid IN AP_INVOICE_LINES.DEFAULT_DIST_CCID%TYPE --bug 5386396
300 ) RETURN BOOLEAN;
301
302
303 PROCEDURE Get_Invoice_LE (
304 p_vendor_site_id IN NUMBER,
305 p_inv_liab_ccid IN NUMBER,
306 p_org_id IN NUMBER,
307 p_le_id OUT NOCOPY NUMBER);
308
309 FUNCTION Get_Check_LE (
310 p_bank_acct_use_id IN NUMBER) RETURN NUMBER;
311
312 PROCEDURE getInvoiceLEInfo (
313 p_vendor_site_id IN NUMBER,
314 p_inv_liab_ccid IN NUMBER,
315 p_org_id IN NUMBER,
316 p_le_id OUT NOCOPY NUMBER,
317 p_le_name OUT NOCOPY VARCHAR2,
318 p_le_registration_num OUT NOCOPY VARCHAR2,
319 p_le_address1 OUT NOCOPY VARCHAR2,
320 p_le_city OUT NOCOPY VARCHAR2,
321 p_le_postal_code OUT NOCOPY VARCHAR2,
322 p_le_country OUT NOCOPY VARCHAR2);
323
324 PROCEDURE Delete_AP_Profiles
325 (P_Profile_Option_Name IN VARCHAR2);
326
327 FUNCTION PERIOD_STATUS (p_gl_date IN DATE) -- 3881457
328 RETURN VARCHAR2;
329
330 /*This function will take in a clob and write it to the concurrent manager file.
331 It can be used in conjunction with dbms_xmlgen to generate xml output for a
332 concurrent request.*/
333 PROCEDURE clob_to_file
334 (p_xml_clob IN CLOB);
335
336 FUNCTION pa_period_status(
337 p_gl_date IN DATE,
338 p_org_id IN number default
339 mo_global.get_current_org_id) RETURN varchar2;
340
341 FUNCTION Get_PO_Reversed_Encumb_Amount(
342 P_Po_Distribution_Id IN NUMBER,
343 P_Start_gl_Date IN DATE,
344 P_End_gl_Date IN DATE,
345 P_Calling_Sequence IN VARCHAR2 DEFAULT NULL)
346
347 RETURN NUMBER;
348
349
350 --Function get_ccr_status, added for the R12 FSIO gap--
351 --Bug6053476
352 FUNCTION get_ccr_status(P_object_id IN NUMBER,
353 P_object_type IN VARCHAR2
354 ) return VARCHAR2;
355
356 --Function get_gl_natural_account added for Bug 6980939
357 FUNCTION get_gl_natural_account(
358 p_coa_id IN NUMBER,
359 p_ccid IN NUMBER,
360 P_calling_sequence IN VARCHAR2 DEFAULT NULL)
361 RETURN VARCHAR2;
362
363 END AP_UTILITIES_PKG;