1 PACKAGE Ja_Cn_Utility AUTHID CURRENT_USER AS
2 --$Header: JACNCUYS.pls 120.0.12010000.2 2008/10/28 06:57:48 shyan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNCUYS.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This package is to provide share procedures for CNAO programs
14 --|
15 --| PROCEDURE LIST
16 --|
17 --| FUNCTION Check_Profile
18 --| FUNCTION Get_Chart_Of_Account_ID
19 --| FUNCTION Get_Lookup_Meaning
20 --| FUNCTION Check_Nat_Number
21 --| PROCEDURE Change_Output_Filename
22 --| PROCEDURE Submit_Charset_Conversion
23 --| FUNCTION Get_Lookup_Code
24 --|
25 --| HISTORY
26 --| 02-Mar-2006 Donghai Wang Created
27 --| 09-Mar-2006 Joseph Wang added the function Get_Chart_Of_Account_ID
28 --| 21-Mar-2006 Joseph Wang replace the function Get_Chart_Of_Account_ID by
29 --| procedure Get_SOB_And_COA to make it return both Set Of Book
30 --| and Chart Of Account ID
31 --| 31-Mar-2006 Joseph Wang added functions Get_SOB and Get_COA
32 --| 11-Apr-2006 Jackey Li added functions Get_Lookup_Meaning and Check_Nat_Number
33 --| 27-Apr-2006 Andrew Liu added Procedure Output_Conc
34 --| 18-May-2006 Andrew Liu added function Check_Account_Level
35 --| 20-Jun-2006 Shujuan Yan added the new procedure Change_Output_Filename,
36 --| Get_Lookup_Code and Submit_Charset_Conversion
37 --| 04-July-2006 Joseph Wang added the function Check_Accounting_Period_Range
38 --| 1-Sep-2008 Chaoqun Wu added the function Get_Balancing_Segment_Value
39 --|
40 --+======================================================================*/
41
42 --==========================================================================
43 -- FUNCTION NAME:
44 --
45 -- Check_Profile Public
46 --
47 -- DESCRIPTION:
48 --
49 -- This function is used to check if all required profiles has been properly set
50 -- for current responsibility. If No, the function will return FALSE to caller and .
51 -- raise error message. Those required profiles include ' JG: Product', which should
52 -- be set to 'Asia/Pacific Localizations','JG: Territory', which should be set
53 -- to 'China' and 'JA: CNAO Legal Entity', which should be NOT NULL
54 --
55 --
56 -- PARAMETERS:
57 -- In:
58 --
59 --
60 -- DESIGN REFERENCES:
61 --
62 --
63 -- CHANGE HISTORY:
64 --
65 -- 02-Mar-2006 Donghai Wang Created
66 --
67 --===========================================================================
68
69 FUNCTION Check_Profile RETURN BOOLEAN;
70 --==========================================================================
71 -- PROCEDURE NAME:
72 --
73 -- Get_SOB_And_COA Public
74 --
75 -- DESCRIPTION:
76 --
77 -- This procedure is used to get chart of account id and set of book id
78 -- by legal entity, if no data found or exception occurs, x_flag will be
79 -- returned with -1
80 --
81 --
82 --
83 -- PARAMETERS:
84 -- In: p_legal_entity_id Legal entity ID
85 -- Out: x_sob_id Set of book ID
86 -- Out: x_coa_id Chart of account ID
87 -- Out: x_flag Return flag
88 --
89 -- RETURN:
90 -- Flag, -1 for abnormal cases.
91 --
92 -- DESIGN REFERENCES:
93 --
94 --
95 -- CHANGE HISTORY:
96 --
97 -- 09-Mar-2006 Joseph Wang Created
98 --
99 --===========================================================================
100 PROCEDURE Get_Sob_And_Coa(p_Legal_Entity_Id NUMBER,
101 x_Sob_Id OUT NOCOPY NUMBER,
102 x_Coa_Id OUT NOCOPY NUMBER,
103 x_Flag OUT NOCOPY NUMBER);
104
105 --==========================================================================
106 -- FUNCTION NAME:
107 --
108 -- Get_SOB Public
109 --
110 -- DESCRIPTION:
111 --
112 -- This function is used to get set of book id within SQL statements
113 -- by legal entity. Actually it invokes the procedure Get_SOB_And_COA
114 -- to get return value. If no data found or exception occurs, -9 will be
115 -- returned.
116 --
117 --
118 -- PARAMETERS:
119 -- In: p_legal_entity_id Legal entity ID
120 --
121 -- RETURN:
122 -- Set of book ID, -9 for abnormal cases.
123 --
124 -- DESIGN REFERENCES:
125 --
126 --
127 -- CHANGE HISTORY:
128 --
129 -- 31-Mar-2006 Joseph Wang Created
130 --
131 --===========================================================================
132 FUNCTION Get_Sob(p_Legal_Entity_Id NUMBER) RETURN NUMBER;
133 --==========================================================================
134 -- FUNCTION NAME:
135 --
136 -- Get_COA Public
137 --
138 -- DESCRIPTION:
139 --
140 -- This function is used to get chart of account id within SQL statements
141 -- by legal entity. Actually it invokes the procedure Get_SOB_And_COA
142 -- to get return value. If no data found or exception occurs, -9 will be
143 -- returned.
144 --
145 --
146 -- PARAMETERS:
147 -- In: p_legal_entity_id Legal entity ID
148 --
149 -- RETURN:
150 -- Chart of account ID, -9 for abnormal cases.
151 --
152 -- DESIGN REFERENCES:
153 --
154 --
155 -- CHANGE HISTORY:
156 --
157 -- 31-Mar-2006 Joseph Wang Created
158 --
159 -- CHANGED:
160 --Get_Coa function has been updated by lyb in 20-Mar-2007, because the logic is update in R12.
161 --We should get the coa id by access id.
162 --===========================================================================
163 -- FUNCTION Get_Coa(p_Legal_Entity_Id NUMBER) RETURN NUMBER;
164 FUNCTION Get_Coa(p_Access_Set_Id NUMBER) RETURN NUMBER;
165 --==========================================================================
166 -- FUNCTION NAME:
167 --
168 -- Get_Lookup_Meaning Public
169 --
170 -- DESCRIPTION:
171 --
172 -- This function is used to get lookup meaning under one lookup code
173 -- according to lookup type.
174 --
175 --
176 -- PARAMETERS:
177 -- In: p_lookup_code lookup code
178 --
179 -- RETURN:
180 -- Lookup_meaning Varchar2
181 --
182 -- DESIGN REFERENCES:
183 --
184 --
185 -- CHANGE HISTORY:
186 --
187 -- 24-Mar-2006 Jackey Li Created
188 --
189 --===========================================================================
190 FUNCTION Get_Lookup_Meaning(p_Lookup_Code IN VARCHAR2) RETURN VARCHAR2;
191
192 --==========================================================================
193 -- FUNCTION NAME:
194 --
195 -- Check_Nat_Number Public
196 --
197 -- DESCRIPTION:
198 --
199 -- This function is used to check if the given string is a natual number.
200 --
201 --
202 -- PARAMETERS:
203 -- In: p_subject the string need to check
204 --
205 -- RETURN:
206 -- BOOLEAN
207 --
208 -- DESIGN REFERENCES:
209 --
210 --
211 -- CHANGE HISTORY:
212 -- 11-Apr-2006 Jackey Li Created
213 --
214 --===========================================================================
215 FUNCTION Check_Nat_Number(p_Subject IN VARCHAR2) RETURN BOOLEAN;
216
217 --==========================================================================
218 -- PROCEDURE NAME:
219 --
220 -- Output_Conc Public
221 --
222 -- DESCRIPTION:
223 --
224 -- This procedure write data to concurrent output file
225 -- the data can be longer than 4000
226 --
227 -- PARAMETERS:
228 -- In: p_clob the content which need output to concurrent output
229 --
230 --
231 -- DESIGN REFERENCES:
232 --
233 --
234 -- CHANGE HISTORY:
235 --
236 -- 30-APR-2005: qugen.hu Created.
237 -- 27-APR-2005: Andrew.liu imported.
238 --
239 --===========================================================================
240 PROCEDURE Output_Conc(p_Clob IN CLOB);
241
242 --==========================================================================
243 -- FUNCTION NAME:
244 --
245 -- Check_Account_Level Public
246 --
247 -- DESCRIPTION:
248 --
249 -- This procedure check the account level of an account. If the account
250 -- level is not null, and is a natural number and less than 16 than return
251 -- TRUE, else FALSE.
252 --
253 -- PARAMETERS:
254 -- In: P_LEVEL the account level
255 --
256 --
257 -- DESIGN REFERENCES:
258 --
259 --
260 -- CHANGE HISTORY:
261 --
262 -- 18-MAY-2005: Andrew.liu Created.
263 --
264 --===========================================================================
265 FUNCTION Check_Account_Level(p_Level IN VARCHAR2) RETURN BOOLEAN;
266 --==========================================================================
267 -- FUNCTION NAME:
268 -- Get_Lookup_Code Public
269 --
270 -- DESCRIPTION:
271 -- This function is used to get lookup code of lookup meaning,
272 -- PARAMETERS:
273 -- In: p_lookup_meaning lookup meaning
274 -- p_lookup_type lookup code
275 -- p_view_application_id view application, DEFAULT 0
276 -- p_security_group_id security group
277 --
278 --
279 -- DESIGN REFERENCES:
280 -- None
281 --
282 -- CHANGE HISTORY:
283 -- 06/03/2006 Shujuan Yan Created
284 --==========================================================================
285 FUNCTION Get_Lookup_Code(p_Lookup_Meaning IN VARCHAR2,
286 p_Lookup_Type IN VARCHAR2,
287 p_View_Application_Id IN NUMBER DEFAULT 0,
288 p_Security_Group_Id IN NUMBER DEFAULT 0)
289 RETURN VARCHAR2;
290 --==========================================================================
291 -- PROCEDURE NAME:
292 -- Submit_xml_publiser Public
293 --
294 -- DESCRIPTION:
295 -- This function is used to submit charset conversion concurrent.
296 -- PARAMETERS:
297 -- In: p_xml_request_id xml publisher concurrent request id
298 -- p_source_charset source charset
299 -- p_destination_charset destination charset
300 -- p_source_separator source separator
301 -- Out: x_charset_request_id charset conversion request id
302 -- x_result_flag result flag
303 --
304 --
305 -- DESIGN REFERENCES:
306 -- None
307 --
308 -- CHANGE HISTORY:
309 -- 06/03/2006 Shujuan Yan Created
310 --==========================================================================
311 PROCEDURE Submit_Charset_Conversion(p_Xml_Request_Id IN NUMBER,
312 p_Source_Charset IN VARCHAR2,
313 p_Destination_Charset IN VARCHAR2,
314 p_Source_Separator IN VARCHAR2,
315 x_Charset_Request_Id OUT NOCOPY NUMBER,
316 x_Result_Flag OUT NOCOPY VARCHAR2);
317 --==========================================================================
318 -- PROCEDURE NAME:
319 -- Submit_xml_publiser Public
320 --
321 -- DESCRIPTION:
322 -- This function is used to submit the concurrent program of change output file name
323 -- PARAMETERS:
324 -- In: p_xml_request_id xml publisher concurrent request id
325 -- p_destination_charset destination charset
326 -- p_destination_filename destination filename
327 -- Out: x_charset_request_id charset conversion request id
328 -- x_result_flag result flag
329 --
330 --
331 -- DESIGN REFERENCES:
332 -- None
333 --
334 -- CHANGE HISTORY:
335 -- 06/03/2006 Shujuan Yan Created
336 --==========================================================================
337 PROCEDURE Change_Output_Filename(p_Xml_Request_Id IN NUMBER,
338 p_Destination_Charset IN VARCHAR2,
339 p_Destination_Filename IN VARCHAR2,
340 x_Filename_Request_Id OUT NOCOPY NUMBER,
341 x_Result_Flag OUT NOCOPY VARCHAR2);
342
343 --==========================================================================
344 -- FUNCTION NAME:
345 --
346 -- Check_Cash_Related_Account Public
347 --
348 -- DESCRIPTION:
349 --
350 -- This function is used to check if the gl code combination passed in is -- Cash Related.
351 --
352 -- PARAMETERS:
353 -- In: p_set_of_bks_id Identifier of GL set of book
354 -- p_acc_flex GL code combination
355 --
356 --
357 -- DESIGN REFERENCES:
358 --
359 --
360 -- CHANGE HISTORY:
361 --
362 -- 30-MAY-2005: Donghai Wang Created
363 --
364 --===========================================================================
365 FUNCTION Check_Cash_Related_Account(p_Set_Of_Bks_Id IN NUMBER,
366 p_Acc_Flex IN VARCHAR2)
367 RETURN BOOLEAN;
368
369 --==========================================================================
370 -- FUNCTION NAME:
371 --
372 -- Check_Accounting_Period_Range Public
373 --
374 -- DESCRIPTION:
375 --
376 -- This function is used to check whether all the periods' status within
377 -- the range are 'C' or 'P'
378 --
379 --
380 -- PARAMETERS:
381 -- In: p_legal_entity_id Legal entity ID
382 -- In: p_start_period_name Start period name
383 -- In: p_end_period_name End period name
384 --
385 -- RETURN:
386 -- True for success, otherwise False
387 --
388 -- DESIGN REFERENCES:
389 --
390 --
391 -- CHANGE HISTORY:
392 --
393 -- 04-July-2006 Joseph Wang Created
394 --
395 --===========================================================================
396 FUNCTION Check_Accounting_Period_Range(p_Start_Period_Name IN VARCHAR2,
397 p_End_Period_Name IN VARCHAR2,
398 p_Legal_Entity_Id NUMBER,
399 p_ledger_id IN NUMBER)--added by lyb
400 RETURN BOOLEAN;
401
402 FUNCTION Fetch_Account_Structure(p_Le_Id IN NUMBER) RETURN VARCHAR2;
403
404 --==========================================================================
405 -- PROCEDURE NAME:
406 --
407 -- Populate_Ledger_Le_Bsv_Gt Public
408 --
409 -- DESCRIPTION:
410 --
411 -- This procedure is used to populate the balance segment of currenct
412 -- legal entity and ledger into temporary table ja_cn_ledger_le_bsv_gt
413 --
414 --
415 -- PARAMETERS:
416 -- In: p_legal_entity_id Legal entity ID
417 -- In: p_ledger_id Ledger ID
418 --
419 --
420 -- DESIGN REFERENCES:
421 --
422 --
423 -- CHANGE HISTORY:
424 --
425 -- 12-Mar-07 Qingjun Zhao Created
426 --
427 FUNCTION Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id IN NUMBER,
428 p_Legal_Entity_Id IN NUMBER)
429 RETURN VARCHAR2;
430
431 --==========================================================================
432 -- FUNCTION NAME:
433 --
434 -- Get_Balancing_Segment_Value Public
435 --
436 -- DESCRIPTION:
437 --
438 -- This function is used to get balancing segment value for the specified key flexfield segments.
439 --
440 --
441 -- PARAMETERS:
442 -- In: p_coa_id Chart of account ID
443 -- In: p_concatenated_segments Concatenated segments
444 --
445 -- RETURN:
446 -- Balancing segment value, NULL for abnormal cases.
447 --
448 -- DESIGN REFERENCES:
449 --
450 --
451 -- CHANGE HISTORY:
452 --
453 -- 29-Aug-2008 Chaoqun Wu Created
454 --
455 --===========================================================================
456 FUNCTION Get_Balancing_Segment_Value(
457 p_coa_id IN NUMBER,
458 p_concatenated_segments IN VARCHAR2)
459 RETURN VARCHAR2;
460
461 END Ja_Cn_Utility;
462
[Home] [Help]