[Home] [Help]
PACKAGE BODY: APPS.JA_CN_COA_EXP_PKG
Source
1 PACKAGE BODY JA_CN_COA_EXP_PKG AS
2 --$Header: JACNCAEB.pls 120.5.12000000.1 2007/08/13 14:09:09 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNCAEB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used for Chart of Accout Export, including |
13 --| Natural Account and 4 Subsidiary Account of "Project", |
14 --| "Third Party", "Cost Center" and "Personnel", in the CNAO Project.|
15 --| |
16 --| PROCEDURE LIST |
17 --| FUNCTION Is_Natural_Number PRIVATE |
18 --| PROCEDURE Get_Acc_Subs_View PRIVATE |
19 --| PROCEDURE Coa_NA_Export PRIVATE |
20 --| PROCEDURE Coa_PJ_Export PRIVATE |
21 --| PROCEDURE Coa_TP_Export PRIVATE |
22 --| PROCEDURE Coa_CC_Export PRIVATE |
23 --| PROCEDURE Coa_Person_Export PRIVATE |
24 --| PROCEDURE Coa_Export PUBLIC |
25 --| |
26 --| HISTORY |
27 --| 03/03/2006 Andrew Liu Created |
28 --+======================================================================*/
29
30 l_module_prefix VARCHAR2(100) :='JA_CN_COA_EXP_PKG';
31 JA_CN_NO_DATA_FOUND exception;
32 l_msg_no_data_found varchar2(2000); --'*****No data found*****';
33
34 --==========================================================================
35 -- FUNCTION NAME:
36 -- Is_Natural_Number private
37 --
38 -- DESCRIPTION:
39 -- This function checks the input string is a nutural number or not.
40 --
41 -- PARAMETERS:
42 -- In: P_NUM VARCHAR2 String of a number
43 -- RETURN:
44 -- NUMBER
45 -- It is a nutural number when returns 1, else not when returns 0
46 --
47 -- DESIGN REFERENCES:
48 -- None
49 --
50 -- CHANGE HISTORY:
51 -- 03/03/2006 Andrew Liu Created
52 --==========================================================================
53 FUNCTION Is_Natural_Number( P_NUM IN varchar2)
54 RETURN VARCHAR2 IS
55 l_number NUMBER;
56 BEGIN
57 l_number := TO_NUMBER(P_NUM);
58 IF instr(P_NUM, '.', 1, 1) > 0 --not a integer
59 OR instr(TO_CHAR(l_number), '.', 1, 1) > 0 --not a integer
60 OR l_number <1 --less than 1
61 THEN
62 RETURN 0;
63 END IF;
64
65 RETURN 1;
66 EXCEPTION
67 WHEN OTHERS THEN
68 RETURN 0;
69 End Is_Natural_Number;
70
71
72 --==========================================================================
73 -- PROCEDURE NAME:
74 -- Get_Acc_Subs_View private
75 --
76 -- DESCRIPTION:
77 -- This procedure gets account number, level, subsidiary account flag,
78 -- and item of project, third party, cost center and personnel, and
79 -- Balance Side of all accounts INTO view 'JA_CN_ACC_SUBS_V'.
80 --
81 -- PARAMETERS:
82 -- In: P_LEDGER_ID NUMBER ID of Ledger
83 -- In: P_COA_ID NUMBER chart of accounts ID
84 --
85 -- DESIGN REFERENCES:
86 -- None
87 --
88 -- CHANGE HISTORY:
89 -- 03/03/2006 Andrew Liu
90 -- 04/27/2007 Yucheng Sun
91 --==========================================================================
92 PROCEDURE Get_Acc_Subs_View( P_LEDGER_ID IN number
93 ,P_COA_ID IN NUMBER ) IS
94 l_ledger_id NUMBER := P_LEDGER_ID;
95 l_coa_id NUMBER := P_COA_ID;
96 l_sql_str varchar2(30000):='';
97 l_acc_level_context JA_CN_DFF_ASSIGNMENTS.CONTEXT_CODE%TYPE;
98 l_acc_sub_context JA_CN_DFF_ASSIGNMENTS.CONTEXT_CODE%TYPE;
99 l_acc_bal_context JA_CN_DFF_ASSIGNMENTS.CONTEXT_CODE%TYPE;
100 l_acc_level_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
101 l_sub_pj_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
102 l_sub_tp_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
103 l_sub_cc_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
104 l_sub_person_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
105 l_acc_bal_position JA_CN_DFF_ASSIGNMENTS.ATTRIBUTE_COLUMN%TYPE;
106
107 BEGIN
108 --Get positions of
109 -- account level, project, third party, cost center and personnel,and Balance Side
110 --Generally speaking, the context code of these 6 item are the same one ('subsidary').
111 SELECT nvl(DFF1.CONTEXT_CODE,'') acc_level_context
112 ,nvl(DFF2.CONTEXT_CODE,'') acc_sub_context
113 ,nvl(DFF6.CONTEXT_CODE,'') acc_bal_context
114 ,nvl(DFF1.ATTRIBUTE_COLUMN, '') acc_level_position
115 ,nvl(DFF2.ATTRIBUTE_COLUMN, '') sub_pj_position
116 ,nvl(DFF3.ATTRIBUTE_COLUMN, '') sub_tp_position
117 ,nvl(DFF4.ATTRIBUTE_COLUMN, '') sub_cc_position
118 ,nvl(DFF5.ATTRIBUTE_COLUMN, '') sub_person_position
119 ,nvl(DFF6.ATTRIBUTE_COLUMN, '') acc_bal_position
120 INTO l_acc_level_context
121 ,l_acc_sub_context
122 ,l_acc_bal_context
123 ,l_acc_level_position
124 ,l_sub_pj_position
125 ,l_sub_tp_position
126 ,l_sub_cc_position
127 ,l_sub_person_position
128 ,l_acc_bal_position
129 FROM JA_CN_DFF_ASSIGNMENTS DFF1
130 ,JA_CN_DFF_ASSIGNMENTS DFF2
131 ,JA_CN_DFF_ASSIGNMENTS DFF3
132 ,JA_CN_DFF_ASSIGNMENTS DFF4
133 ,JA_CN_DFF_ASSIGNMENTS DFF5
134 ,JA_CN_DFF_ASSIGNMENTS DFF6
135 WHERE DFF1.DFF_TITLE_CODE = 'ACLE' -- Account Level
136 AND DFF2.DFF_TITLE_CODE = 'SAPA' -- Project
137 AND DFF3.DFF_TITLE_CODE = 'SATP' -- Third party
138 AND DFF4.DFF_TITLE_CODE = 'SACC' -- Cost center
139 AND DFF5.DFF_TITLE_CODE = 'SAEE' -- Personnel
140 AND DFF6.DFF_TITLE_CODE = 'ACBS' -- Balance Side
141 -- Check whether the flexfields had been set for current COA_ID
142 AND DFF1.CHART_OF_ACCOUNTS_ID=l_coa_id
143 AND DFF2.CHART_OF_ACCOUNTS_ID=l_coa_id
144 AND DFF3.CHART_OF_ACCOUNTS_ID=l_coa_id
145 AND DFF4.CHART_OF_ACCOUNTS_ID=l_coa_id
146 AND DFF5.CHART_OF_ACCOUNTS_ID=l_coa_id
147 AND DFF6.CHART_OF_ACCOUNTS_ID=l_coa_id
148 ;
149
150 --Combine sql of view. The view will get account number, level, subsidiary account flag,
151 -- and item of project, third party, cost center and personnel and Balance Side
152 -- of all accounts.
153
154 -- add Global Data Elements supporting
155 -- while using Global Data Elements, the value of FND_FLEX_VALUES.VALUE_CATEGORY will be null
156 IF 'Global Data Elements'=nvl(l_acc_level_context,'') THEN
157 l_sql_str :=
158 'SELECT DISTINCT '
159 ||' FFV.FLEX_VALUE acc_number '
160 ||' ,nvl(FFV.' || l_acc_level_position ||', '''') '
161 ||' acc_level '
162 ||' ,DECODE( '
163 ||' nvl(FFV.' || l_sub_pj_position || ', ''N'') || '
164 ||' nvl(FFV.' || l_sub_tp_position || ', ''N'') || '
165 ||' nvl(FFV.' || l_sub_cc_position || ', ''N'') || '
166 ||' nvl(FFV.' || l_sub_person_position || ', ''N'') '
167 ||' , ''NNNN'', ''0'', ''1'') sub_flag '
168 ||' ,nvl( DECODE(nvl(FFV.' || l_sub_pj_position || ', ''N''),'
169 ||' ''Y'', ''Project/'', '''')|| '
170 ||' DECODE(nvl(FFV.' || l_sub_tp_position || ', ''N''),'
171 ||' ''S'', ''Supplier/'',''C'', ''Customer/'', '''')|| '
172 ||' DECODE(nvl(FFV.' || l_sub_cc_position || ', ''N''),'
173 ||' ''Y'', ''Cost Center/'', '''')|| '
174 ||' DECODE(nvl(FFV.' || l_sub_person_position || ', '
175 ||' ''N''), ''Y'', ''Personnel/'', ''''), '
176 ||' ''/'') sub_item '
177 ||' ,nvl(FFV.' || l_acc_bal_position ||', '''') '
178 ||' acc_bal '
179 ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
180 ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
181 ||' ,FND_FLEX_VALUE_SETS FFVS '
182 ||' ,FND_FLEX_VALUES FFV '
183 --||' ,GL_LEDGERS LEDGER '
184 ||' WHERE '
185 --Get all correct row of FFV
186 --||' LEDGER.ledger_id = ' || l_LEDGER_id
187 ||' FIFS.id_flex_num = '|| l_coa_id ||' '
188 ||' AND FIFS.id_flex_num = FSAV.id_flex_num '
189 ||' AND FIFS.application_id = 101 '
190 ||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME '
191 ||' AND FIFS.application_id = FSAV.application_id '
192 ||' AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT'' '
193 ||' AND FSAV.ATTRIBUTE_VALUE = ''Y'' '
194 ||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID '
195 ||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID '
196 --||' AND FFV.VALUE_CATEGORY IS NULL '
197 ||' ORDER BY FFV.FLEX_VALUE '
198 ;
199 ELSE
200 l_sql_str :=
201 'SELECT DISTINCT '
202 ||' FFV.FLEX_VALUE acc_number '
203 ||' ,DECODE(FFV.VALUE_CATEGORY,'''|| l_acc_level_context ||''','
204 ||' nvl(FFV.' || l_acc_level_position ||', ''''), '
205 ||' '''') acc_level '
206 ||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
207 ||' DECODE( '
208 ||' nvl(FFV.' || l_sub_pj_position || ', ''N'') || '
209 ||' nvl(FFV.' || l_sub_tp_position || ', ''N'') || '
210 ||' nvl(FFV.' || l_sub_cc_position || ', ''N'') || '
211 ||' nvl(FFV.' || l_sub_person_position || ', ''N'') '
212 ||' , ''NNNN'', ''0'', ''1''), '
213 ||' ''0'') sub_flag '
214 ||' ,DECODE(FFV.VALUE_CATEGORY,'''|| l_acc_sub_context ||''','
215 ||' nvl(DECODE(nvl(FFV.' || l_sub_pj_position || ', ''N''),'
216 ||' ''Y'', ''Project/'', '''')|| '
217 ||' DECODE(nvl(FFV.' || l_sub_tp_position || ', ''N''),'
218 ||' ''S'', ''Supplier/'',''C'', ''Customer/'', '''')|| '
219 ||' DECODE(nvl(FFV.' || l_sub_cc_position || ', ''N''),'
220 ||' ''Y'', ''Cost Center/'', '''')|| '
221 ||' DECODE(nvl(FFV.' || l_sub_person_position || ', '
222 ||' ''N''), ''Y'', ''Personnel/'', ''''), '
223 ||' ''/''), ''/'') sub_item '
224 ||' ,DECODE(FFV.VALUE_CATEGORY,'''|| l_acc_bal_context ||''','
225 ||' nvl(FFV.' || l_acc_bal_position ||', ''''), '
226 ||' '''') acc_bal '
227 ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
228 ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
229 ||' ,FND_FLEX_VALUE_SETS FFVS '
230 ||' ,FND_FLEX_VALUES FFV '
231 --||' ,GL_LEDGERS LEDGER '
232 ||' WHERE '
233 --Get all correct row of FFV
234 --||' LEDGER.ledger_id = ' || l_LEDGER_id
235 ||' FIFS.id_flex_num = '|| l_coa_id ||' '
236 ||' AND FIFS.id_flex_num = FSAV.id_flex_num '
237 ||' AND FIFS.application_id = 101 '
238 ||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME '
239 ||' AND FIFS.application_id = FSAV.application_id '
240 ||' AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT'' '
241 ||' AND FSAV.ATTRIBUTE_VALUE = ''Y'' '
242 ||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID '
243 ||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID '
244 ||' ORDER BY FFV.FLEX_VALUE '
245 ;
246 END IF;
247
248
249 l_sql_str := 'CREATE OR REPLACE VIEW JA_CN_ACC_SUBS_V AS ' ||
250 l_sql_str;
251
252 /* l_sql :=
253 'SELECT DISTINCT ' --For porject of 'N' or 'COA'
254 ||' FFV.FLEX_VALUE acc_number '
255 ||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_level_context||''','
256 ||' nvl(FFV.' || l_acc_level_position ||', ''''), '
257 ||' '''') acc_level '
258 ||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
259 ||' DECODE( '
260 ||' DECODE(nvl(SOB.GLOBAL_ATTRIBUTE1, ''N''), '
261 ||' ''N'', ''N'', '
262 ||' ''COA'', nvl(FFV.' || l_sub_pj_position
263 ||' , ''N'') ) || '
264 ||' nvl(FFV.' || l_sub_tp_position || ', ''N'') || '
265 ||' nvl(FFV.' || l_sub_cc_position || ', ''N'') || '
266 ||' nvl(FFV.' || l_sub_person_position || ', ''N'') '
267 ||' , ''NNNN'', ''0'', ''1''), '
268 ||' ''0'') sub_flag '
269 ||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
270 ||' nvl(DECODE(nvl(SOB.GLOBAL_ATTRIBUTE1, ''N''), '
271 ||' ''N'', '''', '--Leave Blank
272 ||' ''COA'', DECODE(nvl(FFV.' || l_sub_pj_position
273 ||' , ''N''), ''Y'', ''Project-COA/'', '''')'
274 ||' )|| '
275 ||' DECODE(nvl(FFV.' || l_sub_tp_position || ', ''N''),'
276 ||' ''Y'', ''Third Party/'', '''')|| '
277 ||' DECODE(nvl(FFV.' || l_sub_cc_position || ', ''N''),'
278 ||' ''Y'', ''Cost Center/'', '''')|| '
279 ||' DECODE(nvl(FFV.' || l_sub_person_position || ', '
280 ||' ''N''), ''Y'', ''Personnel/'', ''''), '
281 ||' ''/''), ''/'') sub_item '
282 ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
283 ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
284 ||' ,FND_FLEX_VALUE_SETS FFVS '
285 ||' ,FND_FLEX_VALUES FFV '
286 ||' ,GL_SETS_OF_BOOKS SOB '
287 ||' WHERE '
288 --Get all correct row of FFV
289 ||' SOB.set_of_books_id = ' || l_sob_id
290 ||' AND SOB.global_attribute_category = ''JA.CN.GLXSTBKS.BOOKS'' '
291 ||' AND SOB.chart_of_accounts_id = FIFS.id_flex_num '
292 ||' AND FIFS.id_flex_num = FSAV.id_flex_num '
293 ||' AND FIFS.application_id = 101 '
294 ||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME '
295
296 ||' AND FIFS.application_id = FSAV.application_id '
297 ||' AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT'' '
298 ||' AND FSAV.ATTRIBUTE_VALUE = ''Y'' '
299 ||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID '
300 ||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID '
301
302 \*--for all. --The context code may be null or others!
303 ||' AND (FFV.VALUE_CATEGORY is null OR '
304 ||' FFV.VALUE_CATEGORY = ''Subsidiary'') '*\
305
306 --for Subsidiary account item of project
307 ||' AND ( nvl(SOB.GLOBAL_ATTRIBUTE1, ''N'') = ''N'' '
308 ||' OR nvl(SOB.GLOBAL_ATTRIBUTE1, ''N'') = ''COA'' '
309 --AND DFF2.DFF_TITLE_CODE = ''SAPA''
310 ||' ) '
311 ;
312
313 l_sql := l_sql || ' UNION ';
314
315 l_sql := l_sql || --For porject of 'PA'(Project Module)
316 'SELECT DISTINCT '
317 ||' FFV.FLEX_VALUE acc_number '
318 ||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_level_context||''','
319 ||' nvl(FFV.' || l_acc_level_position ||', ''''), '
320 ||' '''') acc_level '
321 ||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
322 ||' DECODE( '
323 ||' DECODE(nvl(SOB.GLOBAL_ATTRIBUTE1, ''N''), '
324 ||' ''PA'', DECODE(nvl(BAL.PROJECT_NUMBER, ''''), '
325 ||' '''', ''N'', ''Y'') '
326 \*||' ''PA'', DECODE( '
327 ||' decode(nvl(BAL.SET_OF_BOOKS_ID, ''-1''), SOB.set_of_books_id, '
328 ||' decode(nvl(BAL.account_segment, ''@@''), FFV.FLEX_VALUE, '
329 ||' decode(nvl(BAL.project_source, ''@@''), ''PA'', '
330 ||' nvl(BAL.PROJECT_NUMBER, ''''), ''''), '
331 ||' ''''), ''''),'''', ''N'', ''Y'') '*\
332 ||' ) || '
333 ||' nvl(FFV.' || l_sub_tp_position || ', ''N'') || '
334 ||' nvl(FFV.' || l_sub_cc_position || ', ''N'') || '
335 ||' nvl(FFV.' || l_sub_person_position || ', ''N'') '
336 ||' , ''NNNN'', ''0'', ''1''), '
337 ||' ''0'') sub_flag '
338 ||' ,DECODE(FFV.VALUE_CATEGORY,'''||l_acc_sub_context||''','
339 ||' nvl(DECODE(nvl(SOB.GLOBAL_ATTRIBUTE1, ''N''), '
340 ||' ''PA'', DECODE(nvl(BAL.PROJECT_NUMBER, ''''), '
341 ||' '''', '''', ''Project-PM/'') '
342 \*||' ''PA'', DECODE( '
343 ||' decode(nvl(BAL.SET_OF_BOOKS_ID, ''-1''), SOB.set_of_books_id, '
344 ||' decode(nvl(BAL.account_segment, ''@@''), FFV.FLEX_VALUE, '
345 ||' decode(nvl(BAL.project_source, ''@@''), ''PA'', '
346 ||' nvl(BAL.PROJECT_NUMBER, ''''), ''''), '
347 ||' ''''), ''''),'''', '''', ''Project-PM/'') '*\
348 ||' )|| '
349 ||' DECODE(nvl(FFV.' || l_sub_tp_position || ', ''N''),'
350 ||' ''Y'', ''Third Party/'', '''')|| '
351 ||' DECODE(nvl(FFV.' || l_sub_cc_position || ', ''N''),'
352 ||' ''Y'', ''Cost Center/'', '''')|| '
353 ||' DECODE(nvl(FFV.' || l_sub_person_position || ', '
354 ||' ''N''), ''Y'', ''Personnel/'', ''''), '
355 ||' ''/''), ''/'') sub_item '
356 ||' FROM FND_ID_FLEX_SEGMENTS FIFS '
357 ||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV '
358 ||' ,FND_FLEX_VALUE_SETS FFVS '
359 ||' ,FND_FLEX_VALUES FFV '
360 ||' ,GL_SETS_OF_BOOKS SOB '
361 --Balane table used here only for project from Project Module
362 --||' ,JA_CN_ACCOUNT_BALANCES BAL '
363 ||' ,(SELECT * FROM JA_CN_ACCOUNT_BALANCES WHERE '
364 ||' project_source = ''PA'' AND set_of_books_id = '
365 || l_sob_id || ') BAL '
366 ||' WHERE '
367 --Get all correct row of FFV
368 ||' SOB.set_of_books_id = ' || l_sob_id
369 ||' AND SOB.global_attribute_category = ''JA.CN.GLXSTBKS.BOOKS'' '
370 ||' AND SOB.chart_of_accounts_id = FIFS.id_flex_num '
371 ||' AND FIFS.id_flex_num = FSAV.id_flex_num '
372 ||' AND FIFS.application_id = 101 '
373 ||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME '
374
375 ||' AND FIFS.application_id = FSAV.application_id '
376 ||' AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT'' '
377 ||' AND FSAV.ATTRIBUTE_VALUE = ''Y'' '
378 ||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID '
379 ||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID '
380
381 \*--for all. --The context code may be null or others!
382 ||' AND (FFV.VALUE_CATEGORY is null OR '
383 ||' FFV.VALUE_CATEGORY = ''Subsidiary'') '*\
384
385 --for Subsidiary account item of project
386 ||' AND nvl(SOB.GLOBAL_ATTRIBUTE1, ''N'') = ''PA'' '
387 ||' AND BAL.account_segment(+) = FFV.FLEX_VALUE '
388 ;
389
390 l_sql := 'CREATE OR REPLACE VIEW JA_CN_ACC_SUBS_V AS ' ||
391 l_sql;*/
392
393 --dbms_output.put_line(l_sql);
394 EXECUTE IMMEDIATE l_sql_str;
395
396 End Get_Acc_Subs_View;
397
398
399 --==========================================================================
400 -- PROCEDURE NAME:
401 -- Coa_NA_Export Private
402 --
403 -- DESCRIPTION:
404 -- This procedure expots Natural Accounts, if there are invalid
405 -- Natural Accounts then output an exceptions's report.
406 --
407 -- PARAMETERS:
408 -- Out: errbuf NOCOPY VARCHAR2
409 -- Out: retcode NOCOPY VARCHAR2
410 -- In: P_COA_ID NUMBER Chart of account ID
411 -- In: P_LEDGER_ID NUMBER ID of ledger
412 -- In: P_LE_ID NUMBER ID of Legal Entity
413 -- In: P_XML_TEMPLATE_LANGUAGE VARCHAR2 template language of exception report
414 -- In: P_XML_TEMPLATE_TERRITORY VARCHAR2 template territory of exception report
415 -- In: P_XML_OUTPUT_FORMAT VARCHAR2 output format of exception report
416 --
417 -- DESIGN REFERENCES:
418 -- None
419 --
420 -- CHANGE HISTORY:
421 -- 03/03/2006 Andrew Liu Created
422 -- 04/24/2007 Yucheng Sun Updated
423 --===========================================================================
424 PROCEDURE Coa_NA_Export( errbuf OUT NOCOPY VARCHAR2
425 ,retcode OUT NOCOPY VARCHAR2
426 ,P_COA_ID IN NUMBER
427 ,P_LEDGER_ID IN NUMBER
428 ,P_LE_ID IN NUMBER
429 ,P_XML_TEMPLATE_LANGUAGE IN VARCHAR2
430 ,P_XML_TEMPLATE_TERRITORY IN VARCHAR2
431 ,P_XML_OUTPUT_FORMAT IN VARCHAR2
432 ) IS
433 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
434 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
435 l_proc_name VARCHAR2(100) :='Coa_NA_Export';
436
437 l_coa_id NUMBER := P_COA_ID;
438 l_ledger_id NUMBER := P_LEDGER_ID;
439 l_le_id NUMBER := P_LE_ID;
440 JA_CN_INCOMPLETE_DFF_ASSIGN exception;
441 JA_CN_INVALID_ACCOUNT_STRU exception;
442 l_msg_incomplete_dff_assign varchar2(2000);
443 l_msg_invalid_account_stru varchar2(2000);
444 l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
445
446 l_na_curr_req_id NUMBER; --Request id of current request
447 l_xml_layout boolean;
448 l_template_language VARCHAR2(10) := P_XML_TEMPLATE_LANGUAGE;
449 l_template_territory VARCHAR2(10) := P_XML_TEMPLATE_TERRITORY;
450 l_output_format VARCHAR2(10) := P_XML_OUTPUT_FORMAT;
451 l_na_req_id NUMBER; --Request id for concurrent program 'Generating Natural Account Export Exception Report'
452 l_na_req_phase fnd_lookup_values.meaning%TYPE;
453 l_na_req_status fnd_lookup_values.meaning%TYPE;
454 l_na_req_dev_phase VARCHAR2(30);
455 l_na_req_dev_status VARCHAR2(30);
456 l_na_req_message VARCHAR2(100);
457
458 l_dff VARCHAR2(6);
459 l_sql varchar2(10000);
460 l_account_structures_kfv VARCHAR2(100) := 'ja_cn_account_structures_kfv';
461 l_na_acc_str VARCHAR2(2000);
462 l_na_acc_str_2 VARCHAR2(2000);
463 --l_ent_flag JA_CN_SYSTEM_PARAMETERS_ALL.ENT_FLAG%TYPE;
464 l_ent_acc_type VARCHAR2(100);
465 l_delimiter_label FND_ID_FLEX_STRUCTURES.Concatenated_Segment_Delimiter%TYPE;
466 l_acc_segment VARCHAR2(100);
467 TYPE t_acc_level_segments IS TABLE OF NUMBER;
468 l_acc_segments t_acc_level_segments;
469 l_acc_seg_serial NUMBER;
470 TYPE t_level_seg_lens IS TABLE OF NUMBER;
471 l_acc_seg_lens t_level_seg_lens;
472
473 l_na_number FND_FLEX_VALUES.FLEX_VALUE%TYPE;
474 l_na_name FND_FLEX_VALUES_TL.description%TYPE;
475 l_na_parent VARCHAR2(1);
476 l_na_level VARCHAR2(100);
477 l_na_sub_flag VARCHAR2(1);
478 l_na_sub_item VARCHAR2(100);
479 l_acc_type_code VARCHAR2(1);
480 l_acc_bal_code VARCHAR2(100);
481 l_na_mea GL_STAT_ACCOUNT_UOM.UNIT_OF_MEASURE%TYPE;
482
483 l_project_meaning VARCHAR2(100);
484 l_thirdparty_meaning VARCHAR2(100);
485 l_supplier_meaning VARCHAR2(100);
486 l_customer_meaning VARCHAR2(100);
487 l_costcenter_meaning VARCHAR2(100);
488 l_personnel_meaning VARCHAR2(100);
489
490 l_na_type VARCHAR2(50);
491 l_na_bal VARCHAR2(10);
492
493 l_length NUMBER;
494 l_expected_length NUMBER;
495
496 l_exceptions_count NUMBER; --count of invalid account rows
497 l_row_count NUMBER; --count of correct account rows
498
499
500
501 --Cursor to get DFF assignment status of Account Level, Project, Third Party,
502 -- Cost Center and Personnel, and Balance Side.
503 --Only a record of 'YYYYYY' expresses that all 6 DFFs have been set.
504 CURSOR c_dff IS
505 SELECT DECODE(nvl(DFF1.CONTEXT_CODE, ''), '', 'N',
506 DECODE(nvl(DFF1.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
507 || DECODE(nvl(DFF2.CONTEXT_CODE, ''), '', 'N',
508 DECODE(nvl(DFF2.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
509 || DECODE(nvl(DFF3.CONTEXT_CODE, ''), '', 'N',
510 DECODE(nvl(DFF3.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
511 || DECODE(nvl(DFF4.CONTEXT_CODE, ''), '', 'N',
512 DECODE(nvl(DFF4.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
513 || DECODE(nvl(DFF5.CONTEXT_CODE, ''), '', 'N',
514 DECODE(nvl(DFF5.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
515 || DECODE(nvl(DFF6.CONTEXT_CODE, ''), '', 'N',
516 DECODE(nvl(DFF6.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
517 dff_assign
518 FROM JA_CN_DFF_ASSIGNMENTS DFF1
519 ,JA_CN_DFF_ASSIGNMENTS DFF2
520 ,JA_CN_DFF_ASSIGNMENTS DFF3
521 ,JA_CN_DFF_ASSIGNMENTS DFF4
522 ,JA_CN_DFF_ASSIGNMENTS DFF5
523 ,JA_CN_DFF_ASSIGNMENTS DFF6
524 WHERE DFF1.DFF_TITLE_CODE = 'ACLE' -- Account Level
525 AND DFF2.DFF_TITLE_CODE = 'SAPA' -- Project
526 AND DFF3.DFF_TITLE_CODE = 'SATP' -- Third party
527 AND DFF4.DFF_TITLE_CODE = 'SACC' -- Cost center
528 AND DFF5.DFF_TITLE_CODE = 'SAEE' -- Personnel
529 AND DFF6.DFF_TITLE_CODE = 'ACBS' -- Balance Side
530 -- Check whether the flexfields had been set for current COA_ID
531 AND DFF1.CHART_OF_ACCOUNTS_ID=l_coa_id
532 AND DFF2.CHART_OF_ACCOUNTS_ID=l_coa_id
533 AND DFF3.CHART_OF_ACCOUNTS_ID=l_coa_id
534 AND DFF4.CHART_OF_ACCOUNTS_ID=l_coa_id
535 AND DFF5.CHART_OF_ACCOUNTS_ID=l_coa_id
536 AND DFF6.CHART_OF_ACCOUNTS_ID=l_coa_id
537 ;
538
539 --Cursor to get natural account's Number, Name, Parent flag, Type Code;
540 --AND account level, subsidiary account flag and item of project,
541 -- third party, cost center and personnel, and balance side
542 CURSOR c_na_info IS
543 SELECT DISTINCT
544 --FFV.FLEX_VALUE acc_number -- replace with sub.acc_number
545 sub.acc_number
546 ,nvl(FFVT.description, '') acc_name
547 ,DECODE(FFV.summary_flag, 'Y', 'Y', 'N') acc_parent
548 ,SUBSTR(TO_CHAR(FFV.COMPILED_VALUE_ATTRIBUTES) --such as 'Y Y L'
549 ,5,1) acc_type_code
550 ,nvl(sub.acc_level, '') acc_level
551 ,nvl(sub.sub_flag, '0') sub_flag
552 ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
553 nvl(sub.sub_item, '/'), 'Project', l_project_meaning
554 ), 'Third Party', l_thirdparty_meaning
555 ), 'Supplier', l_supplier_meaning
556 ), 'Customer', l_customer_meaning
557 ), 'Cost Center', l_costcenter_meaning
558 ), 'Personnel', l_personnel_meaning
559 ) sub_item
560 ,nvl(sub.acc_bal, '') acc_bal
561 FROM JA_CN_ACC_SUBS_V sub
562 ,FND_ID_FLEX_SEGMENTS FIFS
563 ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
564 ,FND_FLEX_VALUE_SETS FFVS
565 ,FND_FLEX_VALUES FFV
566 ,FND_FLEX_VALUES_TL FFVT
567 ,GL_LEDGERS ledger
568 WHERE --Get all correct row of FFV
569 ledger.ledger_id = l_ledger_id --using variable l_sob_id
570 AND ledger.chart_of_accounts_id = FIFS.id_flex_num
571 AND FIFS.id_flex_num = FSAV.id_flex_num
572 AND FIFS.application_id = 101
573 AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
574 AND FIFS.application_id = FSAV.application_id
575 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
576 AND FSAV.ATTRIBUTE_VALUE = 'Y'
577 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
578 AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
579 --AND nvl(FFV.ENABLED_FLAG, 'N') = 'Y' --Including disabled accounts
580 AND FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
581 AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
582 --For account level, subsidiary account flag and item
583 AND sub.acc_number(+) = FFV.FLEX_VALUE
584 order by sub.acc_number
585 ;
586
587 BEGIN
588 --1. Check whether any DFF assignment of Account Level, Project, Third Party,
589 -- Cost Center and Personnel, and Balance Side has been set or not.
590 --
591 OPEN c_dff;
592 FETCH c_dff INTO l_dff;
593 IF c_dff%NOTFOUND OR
594 l_dff <> 'YYYYYY'
595 THEN
596 RAISE JA_CN_INCOMPLETE_DFF_ASSIGN;
597 END IF;
598 CLOSE c_dff;
599
600 --Get meaning of Subsidiary Accounts
601 -- project meaning
602 SELECT FLV.meaning
603 INTO l_project_meaning
604 FROM FND_LOOKUP_VALUES FLV
605 WHERE FLV.lookup_code = 'PJ'
606 and FLV.lookup_type = 'JA_CN_SUBSIDIARY_GROUP'
607 and FLV.LANGUAGE = userenv('LANG')
608 ;
609 -- third party meaning
610 SELECT FLV.meaning
611 INTO l_thirdparty_meaning
612 FROM FND_LOOKUP_VALUES FLV
613 WHERE FLV.lookup_code = 'TP'
614 and FLV.lookup_type = 'JA_CN_SUBSIDIARY_GROUP'
615 and FLV.LANGUAGE = userenv('LANG')
616 ;
617 -- supplier meaning
618 SELECT FLV.meaning
619 INTO l_supplier_meaning
620 FROM FND_LOOKUP_VALUES FLV
621 WHERE FLV.lookup_code = 'S'
622 and FLV.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
623 and FLV.LANGUAGE = userenv('LANG')
624 ;
625 -- custmor meaning
626 SELECT FLV.meaning
627 INTO l_customer_meaning
628 FROM FND_LOOKUP_VALUES FLV
629 WHERE FLV.lookup_code = 'C'
630 and FLV.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
631 and FLV.LANGUAGE = userenv('LANG')
632 ;
633
634 -- cost center meaning
635 SELECT FLV.meaning
636 INTO l_costcenter_meaning
637 FROM FND_LOOKUP_VALUES FLV
638 WHERE FLV.lookup_code = 'CC'
639 and FLV.lookup_type = 'JA_CN_SUBSIDIARY_GROUP'
640 and FLV.LANGUAGE = userenv('LANG')
641 ;
642 -- person meaning
643 SELECT FLV.meaning
644 INTO l_personnel_meaning
645 FROM FND_LOOKUP_VALUES FLV
646 WHERE FLV.lookup_code = 'PERSON'
647 and FLV.lookup_type = 'JA_CN_SUBSIDIARY_GROUP'
648 and FLV.LANGUAGE = userenv('LANG')
649 ;
650 --get the coa ID from the DNS
651 -- chart of accounts ID from the parameter.
652 /* IF l_access_set_id >= 0 THEN
653 l_coa_id := ja_cn_utility.Get_Coa(p_Access_Set_Id => l_access_set_id);
654 END IF;*/
655
656
657
658
659 --2. Get account structure from system form and get its segments' length.
660 --Get account structure , and enterprise flag
661 /* SELECT nvl(SYS_PAR.ACCOUNT_STRUCTURE, '') acc_str
662 \*,nvl(SYS_PAR.ENT_FLAG, 'ENT') ent_flag*\
663 INTO l_na_acc_str
664 \*,l_ent_flag*\
665 FROM JA_CN_SYSTEM_PARAMETERS_ALL SYS_PAR
666 WHERE SYS_PAR.LEGAL_ENTITY_ID = P_LE_ID --using parameter P_LE_ID*/
667
668 --Using dynamitc sql to fetch account structure. The view
669 -- 'ja_cn_account_structures_kfv' doesn't exist when creating patch.
670 /* SELECT nvl(ACC_STR_V.concatenated_segments, '') acc_str
671 INTO l_na_acc_str
672 FROM JA_CN_SYSTEM_PARAMETERS_ALL SYS_PAR
673 ,ja_cn_account_structures_kfv ACC_STR_V
674 WHERE ACC_STR_V.account_structure_id = SYS_PAR.ACCOUNT_STRUCTURE_ID
675 AND SYS_PAR.LEGAL_ENTITY_ID = P_LE_ID --using parameter P_LE_ID
676 ;*/
677 l_sql :=
678 'SELECT '
679 ||' nvl(ACC_STR_V.concatenated_segments, '''') acc_str '
680 ||' FROM Ja_Cn_Sub_Acc_Sources_All SYS_PAR '
681 ||' ,' || l_account_structures_kfv || ' ACC_STR_V '
682 ||'WHERE ACC_STR_V.account_structure_id = SYS_PAR.ACCOUNTING_STRUCT_ID'
683 ||' AND SYS_PAR.CHART_OF_ACCOUNTS_ID = ' || l_coa_id --using parameter P_LE_ID
684 ;
685 EXECUTE IMMEDIATE l_sql into l_na_acc_str;
686
687 --Get delimiter label
688 BEGIN
689 SELECT distinct FIFStr.Concatenated_Segment_Delimiter
690 INTO l_delimiter_label
691 FROM FND_ID_FLEX_STRUCTURES FIFStr
692 WHERE FIFStr.APPLICATION_ID=7000
693 AND FIFStr.ID_FLEX_CODE='ACCT' --JA_CN_ACCOUNT_STRUCTURES
694 /* FROM GL_SETS_OF_BOOKS SOB
695 ,FND_ID_FLEX_STRUCTURES FIFStr
696 WHERE SOB.set_of_books_id = l_sob_id --using variable l_sob_id
697 AND FIFStr.APPLICATION_ID=7000
698 AND FIFStr.ID_FLEX_CODE='ACCT' --JA_CN_ACCOUNT_STRUCTURES
699 AND FIFStr.ID_FLEX_NUM = SOB.chart_of_accounts_id*/
700 ;
701 EXCEPTION
702 WHEN NO_DATA_FOUND THEN
703 l_delimiter_label := ',';
704 /*IF(l_proc_level >= l_dbg_level)
705 THEN
706 FND_LOG.string( l_proc_level
707 ,l_module_prefix||'.'||l_proc_name||'.NO_DATA_FOUND'
708 ,'The delimiter of account structure has not defined.'
709 );
710 END IF;
711 RAISE;*/
712 WHEN OTHERS THEN
713 l_delimiter_label := ',';
714 END;
715
716 --Get account segments' length
717 l_na_acc_str_2 := l_na_acc_str || l_delimiter_label; --l_na_acc_str has at least 1 segment.
718 l_acc_seg_serial := 1;
719 l_acc_segments := t_acc_level_segments();
720 l_acc_segments.EXTEND(15);
721 l_acc_seg_lens := t_level_seg_lens();
722 l_acc_seg_lens.EXTEND(15);
723 WHILE l_na_acc_str_2 is not null LOOP
724 l_acc_segment := trim(substr(l_na_acc_str_2, 1,
725 instr(l_na_acc_str_2, l_delimiter_label, 1)-1
726 ));
727 l_na_acc_str_2 := substr(l_na_acc_str_2,
728 instr(l_na_acc_str_2, l_delimiter_label, 1)+1,
729 length(l_na_acc_str_2)
730 );
731
732 --Do not check the segment is natural number or not here.
733 l_acc_segments(l_acc_seg_serial) := TO_NUMBER(l_acc_segment);
734 IF l_acc_seg_serial = 1
735 THEN
736 l_acc_seg_lens(l_acc_seg_serial) := l_acc_segments(l_acc_seg_serial);
737 ELSE
738 l_acc_seg_lens(l_acc_seg_serial) := l_acc_seg_lens(l_acc_seg_serial -1 ) +
739 l_acc_segments(l_acc_seg_serial);
740 END IF;
741 l_acc_seg_serial := l_acc_seg_serial + 1;
742 END LOOP; --WHILE l_na_acc_str_2 is not null LOOP
743 -- dbms_output.put_line(l_acc_seg_lens.count); --l_acc_seg_lens.count=15!!!
744 l_acc_seg_serial := l_acc_seg_serial - 1; --count of segments of account structure
745
746 --3. Generate the account level and subsidiary flag and items
747 Get_Acc_Subs_View(P_LEDGER_ID => l_ledger_id
748 ,P_COA_ID => l_coa_id);
749
750 /*-----for test----------------------------------------
751 FND_FILE.put_line(FND_FILE.output,'==Get_Acc_Subs_View:P_COA_ID=='||P_COA_ID ||'==P_LEDGER_ID='||P_LEDGER_ID);
752 -----for test----------------------------------------*/
753
754 --4. Go through all the natural accounts with a natural number marked in
755 -- "Level" field and lists all invalid accounts in invalid account table
756 -- JA_CN_COA_NA_EXCEPTION.
757 l_na_curr_req_id := FND_GLOBAL.CONC_REQUEST_ID; --id of current request
758
759 OPEN c_na_info;
760 LOOP -- Loop for all natural accounts
761 FETCH c_na_info INTO l_na_number
762 ,l_na_name
763 ,l_na_parent
764 ,l_acc_type_code
765 ,l_na_level
766 ,l_na_sub_flag
767 ,l_na_sub_item
768 ,l_acc_bal_code
769 ;
770 EXIT WHEN c_na_info%NOTFOUND;
771
772 --Only consider accounts with level, and the level should be a natural number and <16
773 IF JA_CN_UTILITY.Check_Account_Level(l_na_level)/*l_na_level is not null AND Is_Natural_Number(l_na_level) = 1 and l_na_level < 16*/
774 THEN
775 l_length := LENGTH(TO_CHAR(l_na_number)); --length of l_na_number
776
777 IF l_acc_seg_serial >= l_na_level THEN --l_na_acc_str has l_na_level segments
778 --l_expected_length is sum first l_na_level segements of l_na_acc_str
779 l_expected_length := l_acc_seg_lens(l_na_level);
780 ELSE
781 l_expected_length := -1;
782 END IF;
783
784 IF l_length <> l_expected_length
785 THEN
786 --Insert a row of account number, level, length, expected length,
787 -- account structure, and current request id into invalid account
788 -- table JA_CN_COA_NA_EXCEPTIONS
789 INSERT INTO JA_CN_COA_NA_EXCEPTIONS
790 ( ACCOUNT_SEGMENT
791 ,ACCOUNT_LEVEL
792 ,VALUE_LENGTH
793 ,EXPECTED_LENGTH
794 ,ACCOUNT_STRUCTURE
795 ,NA_REQUEST_ID
796 ,CREATED_BY
797 ,CREATION_DATE
798 ,LAST_UPDATED_BY
799 ,LAST_UPDATE_DATE
800 ,LAST_UPDATE_LOGIN
801 )
802 VALUES( l_na_number
803 ,l_na_level
804 ,l_length
805 ,l_expected_length
806 ,l_na_acc_str
807 ,l_na_curr_req_id
808 ,fnd_global.user_id
809 ,SYSDATE
810 ,fnd_global.user_id
811 ,SYSDATE
812 ,fnd_global.LOGIN_ID
813 );
814 END IF; --Value length
815 END IF; --Account Level should not null and be a natural number and <16
816 END LOOP;
817 CLOSE c_na_info;
818
819 -- 3. Checks if the invalid account table JA_CN_COA_NA_EXCEPTIONS has any row.
820 -- If YES then records error in output and submits a request to generate
821 -- exception report;
822 -- ELSE goes on to collect all natural accounts. <NOT all lowest level ones>
823 SELECT count(*)
824 INTO l_exceptions_count
825 FROM JA_CN_COA_NA_EXCEPTIONS
826 WHERE NA_REQUEST_ID = l_na_curr_req_id
827 ;
828
829 IF l_exceptions_count > 0 --JA_CN_COA_NA_EXCEPTIONS has row
830 THEN
831 l_xml_layout := FND_REQUEST.ADD_LAYOUT( template_appl_name => 'JA'
832 ,template_code => 'JACNNAER'
833 ,template_language => l_template_language --'zh' ('en')
834 ,template_territory => l_template_territory--'00' ('US')
835 ,output_format => l_output_format --'RTF'('PDF')
836 );
837 /*IF NOT(l_xml_layout) THEN --failded to add layout, report it.
838 RAISE JA_CN_ADD_LAYOUT_FAILED;
839 END IF;*/
840
841 --Submit the concurrent program 'Generating Natural Account Export Exception Report'
842 l_na_req_id := FND_REQUEST.Submit_Request( application=> 'JA'
843 ,program => 'JACNNAER'
844 ,argument1 => to_number(l_na_curr_req_id)
845 );
846 COMMIT;
847
848 /*------------for test---------------------------------
849 FND_FILE.put_line(FND_FILE.output,'===='||l_na_curr_req_id ||'==='||l_na_req_id||'=--'||l_exceptions_count||'-----------submit request');
850 ------------for test---------------------------------*/
851
852 --Waiting for the 'Generating Natural Account Export Exception Report' completed.
853 IF l_na_req_id <> 0
854 THEN
855 IF FND_CONCURRENT.Wait_For_Request( request_id => l_na_req_id
856 ,interval => 5
857 ,max_wait => 0
858 ,phase => l_na_req_phase
859 ,status => l_na_req_status
860 ,dev_phase => l_na_req_dev_phase
861 ,dev_status => l_na_req_dev_status
862 ,message => l_na_req_message
863 )
864 THEN
865 IF l_na_req_phase = 'Completed'
866 THEN
867 null;
868 END IF; --l_na_req_phase = 'Completed'
869 END IF; -- FND_CONCURRENT.Wait_For_Request ...
870 END IF; --l_na_req_id<>0
871
872 /* ------------for test---------------------------------
873 FND_FILE.put_line(FND_FILE.output,l_na_req_phase ||'-----------Wait_For_Request');
874 ------------for test---------------------------------*/
875
876 --DELETE rows with l_na_curr_req_id in TABLE JA_CN_COA_NA_EXCEPTIONS;
877 DELETE
878 FROM JA_CN_COA_NA_EXCEPTIONS
879 WHERE NA_REQUEST_ID = l_na_curr_req_id;
880 COMMIT;
881
882 --Report that there have invalid accounts
883 RAISE JA_CN_INVALID_ACCOUNT_STRU;
884
885 ELSE --The invalid account table has no row, so outputs all natural accounts <NOT all lowest level ones>
886 /*FND_FILE.put_line(FND_FILE.output,
887 RPAD('Number',10, ' ')
888 ||'| '|| RPAD('Name',40,' ')
889 ||'| '|| RPAD('Level',10,' ')
890 ||'| '|| RPAD('Subsidiary account flag',1,' ')
891 ||'| '|| RPAD('Subsidiary account item',50,' ')
892 ||'| '|| RPAD('Account type',20,' ')
893 ||'| '|| RPAD('Measurement',20,' ')
894 ||'| '|| RPAD('Balance side',10,' '));*/
895
896 l_row_count := 0;
897
898 /*IF l_ent_flag = 'ENT'
899 THEN
900 l_ent_acc_type := 'JA_CN_ENT_ACCOUNT_TYPE';
901 ELSIF l_ent_flag = 'PUB'
902 THEN
903 l_ent_acc_type := 'JA_CN_PS_ACCOUNT_TYPE';
904 END IF;*/
905 l_ent_acc_type := 'ACCOUNT_TYPE';
906
907 OPEN c_na_info;
908 LOOP -- Loop for all natural accounts
909 FETCH c_na_info INTO l_na_number
910 ,l_na_name
911 ,l_na_parent
912 ,l_acc_type_code
913 ,l_na_level
914 ,l_na_sub_flag
915 ,l_na_sub_item
916 ,l_acc_bal_code
917 ;
918 EXIT WHEN c_na_info%NOTFOUND;
919
920 --Only consider accounts with level, and the level should be a natural number and <16
921 IF JA_CN_UTILITY.Check_Account_Level(l_na_level)/*l_na_level is not null AND Is_Natural_Number(l_na_level) = 1 and l_na_level < 16*/
922 THEN
923 /*IF l_na_parent = 'N' THEN --Only export lowest level (not parent) accounts*/
924 l_row_count := l_row_count + 1; --This account will be outputed
925
926 --Get rid of '/' at the last position of subsidiary item
927 l_na_sub_item := substr(l_na_sub_item, 1, length(l_na_sub_item)-1);
928
929 --Get UOM
930 BEGIN
931 SELECT DISTINCT
932 nvl(UOM.UNIT_OF_MEASURE, '') acc_uom
933 INTO l_na_mea
934 FROM GL_LEDGERS LEDGER
935 ,GL_STAT_ACCOUNT_UOM UOM
936 WHERE LEDGER.ledger_id = l_ledger_id --using variable l_sob_id
937 AND UOM.CHART_OF_ACCOUNTS_ID = LEDGER.CHART_OF_ACCOUNTS_ID
938 AND UOM.ACCOUNT_SEGMENT_VALUE = l_na_number --using variable l_na_number
939 ;
940 EXCEPTION
941 WHEN NO_DATA_FOUND THEN
942 l_na_mea := '';
943 END;
944
945 --Get Account type and Balance side
946 IF l_acc_type_code is not null
947 THEN
948 BEGIN
949 SELECT nvl(FLV.meaning,'') acc_type
950 ,FLV1.meaning acc_bal_side
951 INTO l_na_type
952 ,l_na_bal
953 FROM FND_LOOKUP_VALUES FLV
954 ,FND_LOOKUP_VALUES FLV1
955 WHERE --Get meaning of account type
956 FLV.lookup_code = l_acc_type_code --using variable l_acc_type_code
957 AND FLV.lookup_type = l_ent_acc_type --'ACCOUNT_TYPE'
958 and FLV.LANGUAGE = userenv('LANG')
959 --The following 3 conditions should be remained
960 AND ( nvl('', FLV.territory_code) = FLV.territory_code
961 or FLV.territory_code is null )
962 AND FLV.VIEW_APPLICATION_ID = 0
963 AND FLV.SECURITY_GROUP_ID = 0
964 --Get meaning of balance side
965 AND FLV1.lookup_code = DECODE(
966 l_acc_type_code, --using variable l_acc_type_code
967 'A', DECODE(l_acc_bal_code, 'C', 'C', 'D'), --using variable l_acc_bal_code
968 'E', DECODE(l_acc_bal_code, 'C', 'C', 'D'),
969 'L', DECODE(l_acc_bal_code, 'D', 'D', 'C'),
970 'O', DECODE(l_acc_bal_code, 'D', 'D', 'C'),
971 'R', DECODE(l_acc_bal_code, 'D', 'D', 'C')
972 )
973 AND FLV1.lookup_type = 'JA_CN_DEBIT_CREDIT'--'DEBIT_CREDIT'
974 AND FLV1.LANGUAGE = userenv('LANG')
975 /*and ( nvl('', FLV1.territory_code) = FLV1.territory_code
976 or FLV1.territory_code is null )
977 and FLV1.VIEW_APPLICATION_ID = 3
978 and FLV1.SECURITY_GROUP_ID = 0*/
979 ;
980 EXCEPTION
981 WHEN NO_DATA_FOUND THEN
982 l_na_type := '';
983 l_na_bal := '';
984 END;
985 ELSE
986 l_na_type := '';
987 l_na_bal := '';
988 END IF; --l_acc_type_code is null or not
989
990 --Output a row of account in TXT file with columns account number,
991 -- name, level, sub flag, sub item, type, measurement, balance side
992 /*FND_FILE.put_line(FND_FILE.output,
993 RPAD(l_na_number,10, ' ')
994 ||'| '|| RPAD(nvl(l_na_name,' '),40,' ')
995 ||'| '|| RPAD(nvl(l_na_level,' '),10,' ')
996 ||'| '|| RPAD(nvl(l_na_sub_flag,' '),1,' ')
997 ||'| '|| RPAD(nvl(l_na_sub_item,' '),50,' ')
998 ||'| '|| RPAD(nvl(l_na_type,' '),20,' ')
999 ||'| '|| RPAD(nvl(l_na_mea,' '),20,' ')
1000 ||'| '|| RPAD(nvl(l_na_bal,' '),10,' '));*/
1001 FND_FILE.put_line(FND_FILE.output,
1002 '"' ||l_na_number || '"'
1003 ||l_seperator|| '"' ||l_na_name || '"'
1004 ||l_seperator|| l_na_level
1005 ||l_seperator|| '"' ||l_na_sub_flag || '"'
1006 ||l_seperator|| '"' ||l_na_sub_item || '"'
1007 ||l_seperator|| '"' ||l_na_type || '"'
1008 ||l_seperator|| '"' ||l_na_mea || '"'
1009 ||l_seperator|| '"' ||l_na_bal || '"'
1010 );
1011 END IF; --Account Level should not null and be a natural number and <16
1012 END LOOP;
1013 CLOSE c_na_info;
1014
1015 IF l_row_count = 0 --No account been outputed
1016 THEN
1017 raise JA_CN_NO_DATA_FOUND;
1018 END IF;
1019
1020 END IF; --The invalid account table has row or not
1021
1022 retcode := 0;
1023 errbuf := '';
1024 EXCEPTION
1025 WHEN JA_CN_NO_DATA_FOUND THEN
1026 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
1027 IF (l_proc_level >= l_dbg_level)
1028 THEN
1029 FND_LOG.String( l_proc_level
1030 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
1031 ,l_msg_no_data_found);
1032 END IF;
1033 retcode := 1;
1034 errbuf := l_msg_no_data_found;
1035
1036 WHEN JA_CN_INCOMPLETE_DFF_ASSIGN THEN
1037 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
1038 ,NAME => 'JA_CN_INCOMPLETE_DFF_ASSIGN'
1039 );
1040 l_msg_incomplete_dff_assign := FND_MESSAGE.Get;
1041
1042 FND_FILE.put_line(FND_FILE.output, l_msg_incomplete_dff_assign);
1043 IF (l_proc_level >= l_dbg_level)
1044 THEN
1045 FND_LOG.String( l_proc_level
1046 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_INCOMPLETE_DFF_ASSIGN '
1047 ,l_msg_incomplete_dff_assign);
1048 END IF;
1049 retcode := 1;
1050 errbuf := l_msg_incomplete_dff_assign;
1051
1052 WHEN JA_CN_INVALID_ACCOUNT_STRU THEN
1053 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
1054 ,NAME => 'JA_CN_INVALID_ACCOUNT_STRU'
1055 );
1056 FND_MESSAGE.SET_TOKEN('REQUEST_ID', TO_CHAR(l_na_req_id));
1057 l_msg_invalid_account_stru := FND_MESSAGE.Get;
1058
1059 FND_FILE.put_line(FND_FILE.output, l_msg_invalid_account_stru);
1060 IF (l_proc_level >= l_dbg_level)
1061 THEN
1062 FND_LOG.String( l_proc_level
1063 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_INVALID_ACCOUNT_STRU '
1064 ,l_msg_invalid_account_stru);
1065 END IF;
1066 retcode := 1;
1067 errbuf := l_msg_invalid_account_stru;
1068
1069 /* ------------for test---------------------------------
1070 FND_FILE.put_line(FND_FILE.output,l_msg_invalid_account_stru ||'-----------exception');
1071 ------------for test---------------------------------
1072 */
1073 WHEN OTHERS THEN
1074 IF (l_proc_level >= l_dbg_level)
1075 THEN
1076 FND_LOG.String( l_proc_level
1077 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1078 ,SQLCODE||':'||SQLERRM);
1079 END IF; --(l_proc_level >= l_dbg_level)
1080 RAISE;
1081 END Coa_NA_Export;
1082
1083 --==========================================================================
1084 -- PROCEDURE NAME:
1085 -- Coa_PJ_Export Private
1086 --
1087 -- DESCRIPTION:
1088 -- This procedure exporting the Project list as "subsidiary account"
1089 -- into format predefined flat file.
1090 --
1091 -- PARAMETERS:
1092 -- Out: errbuf NOCOPY VARCHAR2
1093 -- Out: retcode NOCOPY VARCHAR2
1094 -- In: P_COA_ID NUMBER Chart of account ID
1095 -- In: P_LEDGER_ID NUMBER ID of Set Of Book
1096 -- In: P_LE_ID NUMBER ID of Legal Entity
1097 --
1098 -- DESIGN REFERENCES:
1099 -- None
1100 --
1101 -- CHANGE HISTORY:
1102 -- 03/03/2006 Andrew Liu Created
1103 -- 04/24/2007 Yucheng Sun Updated:
1104 -- REPLACE TABLE:SOB WITH TABLE: LEDGER AND SOURCE
1105 --===========================================================================
1106 PROCEDURE Coa_PJ_Export( errbuf OUT NOCOPY VARCHAR2
1107 ,retcode OUT NOCOPY VARCHAR2
1108 ,P_COA_ID IN NUMBER
1109 ,P_LEDGER_ID IN NUMBER
1110 ,P_LE_ID IN NUMBER
1111 ) IS
1112 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1113 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1114 l_proc_name VARCHAR2(100) :='Coa_PJ_Export';
1115
1116 l_ledger_id NUMBER := P_LEDGER_ID;
1117 l_le_id NUMBER := P_LE_ID;
1118 l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
1119
1120 /*JA_CN_PROJ_NOT_CONSIDER exception;
1121 --JA_CN_NO_PROJ_DEFINED exception;
1122 l_msg_proj_not_consider varchar2(2000);
1123 --l_msg_no_proj_defined varchar2(2000);*/
1124
1125 l_row_count NUMBER; --count of rows
1126 --l_pj_source VARCHAR2(150);--VARCHAR2(3); --project data source set in GL GDF
1127 --l_pj_ps VARCHAR2(150);--VARCHAR2(2); --project segment in GL GDF set or not
1128
1129 l_pj_number JA_CN_ACCOUNT_BALANCES.project_number%TYPE;
1130 l_pj_name VARCHAR2(240);
1131
1132 /*--Cursor to get project data source, set in GDF, from table GL_SETS_OF_BOOKS
1133 CURSOR c_pj_setup IS
1134 SELECT nvl(GLOBAL_ATTRIBUTE1, 'N')
1135 ,nvl(GLOBAL_ATTRIBUTE3, '') --DECODE(nvl(GLOBAL_ATTRIBUTE3, ''), '', '', 'PS')
1136 FROM GL_SETS_OF_BOOKS
1137 WHERE set_of_books_id = l_sob_id
1138 AND global_attribute_category = 'JA.CN.GLXSTBKS.BOOKS';*/
1139
1140 --Cursor to get project_number from table JA_CN_ACCOUNT_BALANCES, and name from table
1141 -- PA_PROJECTS_ALL or FND_FLEX_VALUES_TL.
1142 --If two projects from PA and COA are with a same number and same name then
1143 -- only show them one time.
1144 CURSOR c_pj IS
1145 SELECT *
1146 FROM (
1147 --Get name for projects from project module
1148 SELECT DISTINCT
1149 BAL.project_number pj_number
1150 ,nvl(PPA.name, '') pj_name --name for project from PA
1151 FROM JA_CN_ACCOUNT_BALANCES BAL
1152 ,PA_PROJECTS_ALL PPA
1153 WHERE BAL.Ledger_Id= l_ledger_id --using variable l_sob_id
1154 AND BAL.account_segment IS NOT NULL
1155 AND nvl(BAL.project_source, 'N') = 'PA'
1156 and BAL.project_number IS NOT NULL
1157 --AND PPA.project_id = BAL.PROJECT_ID --PROJECT_ID is no use here, replaced it.
1158 AND PPA.SEGMENT1 = BAL.project_number
1159
1160 UNION
1161
1162 --Get name for projects from COA
1163 SELECT DISTINCT
1164 BAL.project_number pj_number
1165 ,nvl(FFVT.description, '') pj_name --name for project from COA
1166 FROM JA_CN_ACCOUNT_BALANCES BAL
1167 ,GL_LEDGERS LEDGER
1168 ,Ja_Cn_Sub_Acc_Sources_All SUBAS
1169 ,FND_ID_FLEX_SEGMENTS FIFS
1170 ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1171 ,FND_FLEX_VALUE_SETS FFVS
1172 ,FND_FLEX_VALUES_TL FFVT
1173 ,FND_FLEX_VALUES FFV
1174 WHERE BAL.Ledger_Id= l_ledger_id --using variable l_sob_id
1175 AND BAL.account_segment IS NOT NULL
1176 AND nvl(BAL.project_source, 'N') = 'COA'
1177 and BAL.project_number IS NOT NULL
1178 --Get project name. --PROJECT_ID is no use here, replaced it.
1179 AND FFV.FLEX_VALUE = BAL.project_number
1180 AND LEDGER.ledger_id = BAL.ledger_id
1181 AND LEDGER.chart_of_accounts_id = FIFS.id_flex_num
1182 AND FIFS.id_flex_num = FSAV.id_flex_num
1183 AND SUBAS.CHART_OF_ACCOUNTS_ID = LEDGER.CHART_OF_ACCOUNTS_ID -- ?? NOT SURE
1184 AND ( ( nvl(SUBAS.PROJECT_SOURCE_FLAG, 'N') = 'COA' --Currently it's from COA
1185 and SUBAS.COA_SEGMENT = FSAV.APPLICATION_COLUMN_NAME
1186 )
1187 OR --It's a old one
1188 ( (nvl(SUBAS.PROJECT_SOURCE_FLAG, 'N') = 'N' OR nvl(SUBAS.PROJECT_SOURCE_FLAG, 'N') = 'PA')
1189 and SUBAS.HISTORY_COA_SEGMENT = FSAV.APPLICATION_COLUMN_NAME
1190 )
1191 )
1192 AND FIFS.application_id = 101
1193 AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
1194 AND FIFS.application_id = FSAV.application_id
1195 AND FSAV.ATTRIBUTE_VALUE = 'Y'
1196 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
1197 AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
1198 AND FFVT.flex_value_id = FFV.flex_value_id
1199 AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
1200 ) tmp_pj_tbl
1201 -- add order by to keep the output item's seqence
1202 order by tmp_pj_tbl.pj_number
1203 ;
1204
1205 BEGIN
1206 --1. Check setups
1207 /*OPEN c_pj_setup;
1208 FETCH c_pj_setup INTO l_pj_source
1209 ,l_pj_ps
1210 ;
1211 CLOSE c_pj_setup;
1212 IF l_pj_source = 'N' THEN --'Project not considered'
1213 RAISE JA_CN_PROJ_NOT_CONSIDER;
1214 \*ELSIF l_pj_source = 'COA' THEN --'Chart of account'
1215 IF l_pj_ps <> 'PS' THEN --'Project segment'
1216 RAISE JA_CN_NO_PROJ_DEFINED;
1217 END IF;*\
1218 END IF;*/
1219
1220 /*FND_FILE.put_line(FND_FILE.output,
1221 RPAD('Number',10, ' ')
1222 ||'| '|| RPAD('Description',40,' '));*/
1223
1224 --2. Export all projects into the format predefined flat file
1225 l_row_count := 0;
1226 OPEN c_pj;
1227 LOOP -- Loop for all projects
1228 FETCH c_pj INTO l_pj_number
1229 ,l_pj_name
1230 ;
1231 EXIT WHEN c_pj%NOTFOUND;
1232 l_row_count := l_row_count+1;
1233 --Output a row of project in TXT file with columns project number, description
1234 /*FND_FILE.put_line(FND_FILE.output,
1235 RPAD(l_pj_number,10, ' ')
1236 ||'| '|| RPAD(l_pj_name,40,' '));*/
1237 FND_FILE.put_line(FND_FILE.output,
1238 '"' ||l_pj_number || '"'
1239 ||l_seperator|| '"' ||l_pj_name || '"'
1240 );
1241 END LOOP;
1242 CLOSE c_pj;
1243 IF l_row_count = 0 --No data found
1244 THEN
1245 RAISE JA_CN_NO_DATA_FOUND;
1246 END IF;
1247
1248 retcode := 0;
1249 errbuf := '';
1250 EXCEPTION
1251 WHEN JA_CN_NO_DATA_FOUND THEN
1252 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
1253 IF (l_proc_level >= l_dbg_level)
1254 THEN
1255 FND_LOG.String( l_proc_level
1256 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
1257 ,l_msg_no_data_found);
1258 END IF;
1259 retcode := 1;
1260 errbuf := l_msg_no_data_found;
1261 /*WHEN JA_CN_PROJ_NOT_CONSIDER THEN
1262 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
1263 ,NAME => 'JA_CN_PROJ_NOT_CONSIDER'
1264 );
1265 l_msg_proj_not_consider := FND_MESSAGE.Get;
1266
1267 FND_FILE.put_line(FND_FILE.output, l_msg_proj_not_consider);
1268 IF (l_proc_level >= l_dbg_level)
1269 THEN
1270 FND_LOG.String( l_proc_level
1271 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_PROJ_NOT_CONSIDER '
1272 ,l_msg_proj_not_consider);
1273 END IF;
1274 retcode := 1;
1275 errbuf := l_msg_proj_not_consider;*/
1276 /*WHEN JA_CN_NO_PROJ_DEFINED THEN
1277 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
1278 ,NAME => 'JA_CN_NO_PROJ_DEFINED'
1279 );
1280 l_msg_no_proj_defined := FND_MESSAGE.Get;
1281 IF (l_proc_level >= l_dbg_level)
1282 THEN
1283 FND_LOG.String( l_proc_level
1284 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_PROJ_DEFINED '
1285 ,l_msg_no_proj_defined);
1286 END IF;
1287 retcode := 1;
1288 errbuf := l_msg_no_proj_defined;*/
1289 WHEN OTHERS THEN
1290 IF (l_proc_level >= l_dbg_level)
1291 THEN
1292 FND_LOG.String( l_proc_level
1293 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1294 ,SQLCODE||':'||SQLERRM
1295 );
1296 END IF; --(l_proc_level >= l_dbg_level)
1297 RAISE;
1298 END Coa_PJ_Export;
1299
1300 --==========================================================================
1301 -- PROCEDURE NAME:
1302 -- Coa_TP_Export Private
1303 --
1304 -- DESCRIPTION:
1305 -- This procedure exporting the Third Party list as "subsidiary account"
1306 -- into format predefined flat file.
1307 --
1308 -- PARAMETERS:
1309 -- Out: errbuf NOCOPY VARCHAR2
1310 -- Out: retcode NOCOPY VARCHAR2
1311 -- In: P_COA_ID NUMBER Chart of account ID
1312 -- In: P_LEDGER_ID NUMBER ID of Set Of Book
1313 -- In: P_LE_ID NUMBER ID of Legal Entity
1314 --
1315 -- DESIGN REFERENCES:
1316 -- None
1317 --
1318 -- CHANGE HISTORY:
1319 -- 03/03/2006 Andrew Liu Created
1320 -- 04/24/2007 Yucheng Sun Updated
1321 --===========================================================================
1322 PROCEDURE Coa_TP_Export( errbuf OUT NOCOPY VARCHAR2
1323 ,retcode OUT NOCOPY VARCHAR2
1324 ,P_COA_ID IN NUMBER
1325 ,P_LEDGER_ID IN NUMBER
1326 ,P_LE_ID IN NUMBER
1327 ) IS
1328 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1329 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1330 l_proc_name VARCHAR2(100) :='Coa_TP_Export';
1331
1332 l_ledger_id NUMBER := P_LEDGER_ID; -- LEDGER ID
1333 l_le_id NUMBER := P_LE_ID; -- LEGAL ENTITY ID
1334 l_coa_id NUMBER := P_COA_ID; -- CHART OF ACCOUT ID
1335 l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
1336
1337 l_sup_meaning VARCHAR2(50);
1338 l_cust_meaning VARCHAR2(50);
1339
1340 l_row_count NUMBER; --count of rows
1341 l_tp_number VARCHAR2(100);
1342 l_tp_name VARCHAR2(360);
1343 l_tp_ctg_number VARCHAR2(60);
1344 l_tp_territory VARCHAR2(100);
1345 l_tp_phonenumber VARCHAR2(100);
1346 l_tp_address VARCHAR2(240);
1347 l_tp_credit_level VARCHAR2(60);
1348
1349 --For supplier
1350 l_vender_id PO_VENDORS.VENDOR_ID%TYPE;
1351 --For city and address of supplier
1352 TYPE t_sup_city_addr IS RECORD ( city PO_VENDOR_SITES_ALL.CITY%TYPE
1353 ,addr PO_VENDOR_SITES_ALL.ADDRESS_LINE1%TYPE
1354 );
1355 TYPE t_sup_city_addr_array IS TABLE OF t_sup_city_addr;
1356 l_all_sup_city_addr t_sup_city_addr_array;
1357 l_sup_city_addr t_sup_city_addr;
1358 --For phone number of supplier
1359 TYPE t_sup_phone_array IS TABLE OF VARCHAR2(100);
1360 l_all_sup_phone t_sup_phone_array;
1361
1362 --For Customer
1363 l_cust_account_id HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
1364 l_party_id HZ_CUST_ACCOUNTS.PARTY_ID%TYPE;
1365 --For city and address of Customer
1366 TYPE t_cust_city_addr IS RECORD ( city HZ_LOCATIONS.CITY%TYPE
1367 ,addr HZ_LOCATIONS.address1%TYPE
1368 );
1369 TYPE t_cust_city_addr_array IS TABLE OF t_cust_city_addr;
1370 l_all_cust_city_addr t_cust_city_addr_array;
1371 l_cust_city_addr t_cust_city_addr;
1372 --For phone number of Customer
1373 TYPE t_cust_phone IS RECORD ( priority VARCHAR2(10)
1374 ,phone VARCHAR2(100)
1375 );
1376 TYPE t_cust_phone_array IS TABLE OF t_cust_phone;
1377 l_all_cust_phone t_cust_phone_array;
1378 l_cust_phone t_cust_phone;
1379
1380 --Cursor to get basic info for suppliers included in table JA_CN_ACCOUNT_BALANCES
1381 --Except Territory, Phone number, and Address
1382 CURSOR c_tp_sup IS
1383 SELECT *
1384 FROM (
1385 SELECT DISTINCT
1386 PV.vendor_id vender_id
1387 /*,'S'||nvl(PV.SEGMENT1, '') sup_number*/
1388 --?? ,nvl(PV.SEGMENT1, '') sup_number --column vendor_number of view AP_VENDORS_V
1389 ,nvl(BAL.Third_Party_Number,'') sup_number -- temp solutin ????
1390 ,nvl(PV.VENDOR_NAME, '') sup_name
1391 /*,nvl(LC_TYPE.DISPLAYED_FIELD, '') sup_type --vendor_type_disp*/
1392 ,''
1393 FROM JA_CN_ACCOUNT_BALANCES BAL
1394 ,PO_VENDORS PV
1395 /*,PO_LOOKUP_CODES LC_TYPE*/
1396 WHERE BAL.Ledger_Id = l_ledger_id --using variable l_sob_id
1397 AND BAL.account_segment IS NOT NULL
1398 AND BAL.THIRD_PARTY_ID IS NOT NULL
1399 AND nvl(BAL.THIRD_PARTY_TYPE, 'X') = 'S'
1400 AND BAL.THIRD_PARTY_ID = PV.vendor_id
1401 ) tmp_sup_tbl
1402 -- add order by to keep the output item's seqence
1403 ORDER BY tmp_sup_tbl.sup_number
1404 /*-- Type
1405 AND LC_TYPE.LOOKUP_CODE(+) = PV.VENDOR_TYPE_LOOKUP_CODE
1406 and LC_TYPE.LOOKUP_TYPE(+) = 'VENDOR TYPE'*/
1407 ;
1408
1409 --Cursor to get basic info for customers of current SOB
1410 --Except Territory, Phone number, and Address
1411 CURSOR c_tp_cust IS
1412 SELECT *
1413 FROM (
1414 SELECT DISTINCT
1415 CUST.CUST_ACCOUNT_ID cust_account_id
1416 ,CUST.PARTY_ID party_id
1417 /*,'C'||nvl(CUST.ACCOUNT_NUMBER, '') cust_number*/
1418 --?? ,nvl(CUST_PARTY.Party_Number, '') cust_number -- take hz_parties.Party_Number to keep consistency with sla export
1419 ,nvl(BAL.Third_Party_Number,'') cust_number -- temp solutin ????
1420 ,nvl(CUST_PARTY.PARTY_NAME, '') cust_name
1421 /*,nvl(L_CLASS.MEANING, '') cust_class --CUSTOMER_CLASS_MEANING*/
1422 ,nvl(CP.CREDIT_RATING, '') cust_credit
1423 FROM JA_CN_ACCOUNT_BALANCES BAL
1424 ,HZ_CUST_ACCOUNTS CUST
1425 ,HZ_PARTIES CUST_PARTY
1426 /*,AR_LOOKUPS L_CLASS*/
1427 ,HZ_CUSTOMER_PROFILES CP
1428 WHERE BAL.Ledger_Id = l_ledger_id --using variable l_ledger_id
1429 AND BAL.LEGAL_ENTITY_ID=l_le_id --using variable l_le_id
1430 AND BAL.account_segment IS NOT NULL
1431 AND BAL.THIRD_PARTY_ID IS NOT NULL
1432 AND nvl(BAL.THIRD_PARTY_TYPE, 'X') = 'C'
1433 AND BAL.THIRD_PARTY_ID = CUST.CUST_ACCOUNT_ID
1434 AND CUST.PARTY_ID = CUST_PARTY.PARTY_ID
1435 /*-- Class
1436 AND CUST.CUSTOMER_CLASS_CODE = L_CLASS.LOOKUP_CODE(+)
1437 and L_CLASS.LOOKUP_TYPE(+) = 'CUSTOMER CLASS'*/
1438 -- Credit rating
1439 AND CP.CUST_ACCOUNT_ID(+) = CUST.CUST_ACCOUNT_ID
1440 and CP.site_use_id is null
1441 ) tmp_cst_tbl
1442 -- add order by to keep the output item's seqence
1443 order by tmp_cst_tbl.cust_number
1444 ;
1445
1446 BEGIN
1447 /*FND_FILE.put_line(FND_FILE.output,
1448 RPAD('Number',10, ' ')
1449 ||'| '|| RPAD('Name',40,' ')
1450 ||'| '|| RPAD('Category',20,' ')
1451 ||'| '|| RPAD('Territory',20,' ')
1452 ||'| '|| RPAD('Phone',20,' ')
1453 ||'| '|| RPAD('Address',40,' ')
1454 ||'| '|| RPAD('Credit Level',20,' '));*/
1455
1456 --Get meaning of Supplier and Customer
1457 SELECT nvl(FLV.meaning,'') sup_meaning
1458 ,nvl(FLV1.meaning,'') cust_meaning
1459 INTO l_sup_meaning
1460 ,l_cust_meaning
1461 FROM FND_LOOKUP_VALUES FLV
1462 ,FND_LOOKUP_VALUES FLV1
1463 WHERE FLV.lookup_code = 'S'
1464 AND FLV.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
1465 AND FLV.LANGUAGE = userenv('LANG')
1466 AND FLV1.lookup_code = 'C'
1467 AND FLV1.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
1468 AND FLV1.LANGUAGE = userenv('LANG')
1469 ;
1470
1471 l_row_count := 0;
1472
1473 --Export all third parties from Payable into the format predefined flat file
1474 l_tp_ctg_number:= l_sup_meaning;
1475 OPEN c_tp_sup;
1476 LOOP
1477 FETCH c_tp_sup INTO l_vender_id
1478 ,l_tp_number
1479 ,l_tp_name
1480 /*,l_tp_ctg_number */
1481 ,l_tp_credit_level
1482 ;
1483 EXIT WHEN c_tp_sup%NOTFOUND;
1484
1485 --Get Sup Territory/Address
1486 l_all_sup_city_addr := null;
1487 BEGIN
1488 SELECT DISTINCT
1489 nvl(PVSA.CITY, '') sup_city
1490 /* --JiaQian make it sure that get city from column 'city'
1491 nvl(PVSA.PROVINCE,
1492 nvl(PVSA.STATE, '')) sup_city
1493 */
1494 ,nvl(PVSA.ADDRESS_LINE1, '') sup_addr
1495 BULK COLLECT INTO l_all_sup_city_addr
1496 FROM PO_VENDOR_SITES_ALL PVSA
1497 ,HR_ORGANIZATION_INFORMATION HOI
1498 WHERE --Check "Primary Pay" Vendor site of OUs under current LE
1499 HOI.org_information_context = 'Operating Unit Information'
1500 AND HOI.Org_Information2 = l_le_id --using variable l_le_id
1501 AND HOI.Org_Information3 = l_ledger_id --using variable l_ledger_id
1502 AND PVSA.Org_id = HOI.ORGANIZATION_ID
1503 AND PVSA.vendor_id = l_vender_id --using variable l_vender_id
1504 and nvl(PVSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y'
1505 ;
1506 EXCEPTION
1507 WHEN NO_DATA_FOUND THEN
1508 l_all_sup_city_addr := null;
1509 END;
1510
1511 --Use city/addr only when there is just 1 record, otherwise leave blanks
1512 IF l_all_sup_city_addr.count = 1
1513 THEN
1514 l_sup_city_addr := l_all_sup_city_addr(1);
1515 l_tp_territory := l_sup_city_addr.city;
1516 l_tp_address := l_sup_city_addr.addr;
1517 ELSE
1518 l_tp_territory := '';
1519 l_tp_address := '';
1520 END IF; --sup city/addr
1521
1522 --Get Sup Phone
1523 l_all_sup_phone := null;
1524 BEGIN
1525 /*--The "Primary Pay" site contact number defined under Contacts tab of supplier Site define page.
1526 SELECT DISTINCT
1527 '0' || DECODE(nvl(PVC.AREA_CODE, ''), '', '', PVC.AREA_CODE || '-')
1528 || nvl(PVC.PHONE, '') sup_phone
1529 BULK COLLECT INTO l_all_sup_phone
1530 FROM PO_VENDOR_CONTACTS PVC
1531 ,PO_VENDOR_SITES_ALL PVSA
1532 ,HR_ORGANIZATION_INFORMATION HOI
1533 WHERE PVC.vendor_site_id = PVSA.vendor_site_id
1534 --Check "Primary Pay" Vendor site of OUs under current LE
1535 AND HOI.org_information_context = 'Operating Unit Information'
1536 AND HOI.Org_Information2 = l_le_id --using variable l_le_id
1537 AND HOI.Org_Information3 = l_sob_id --using variable l_sob_id
1538 AND PVSA.Org_id = HOI.ORGANIZATION_ID
1539 AND PVSA.vendor_id = l_vender_id --using variable l_vender_id
1540 and nvl(PVSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y'
1541 ;*/
1542
1543 --The "Primary Pay" site Communication Voice number defined under General tab of supplier Site define page.
1544 -- relationship : ASSA.vendor_id-->ASSA.vendor_site_id-->PVC.vendor_site_id-->PVC.PHONE
1545 SELECT DISTINCT
1546 nvl(PVC.AREA_CODE, '')
1547 || DECODE(NVL(PVC.AREA_CODE, ''),'','','-')
1548 || nvl(PVC.PHONE, '') sup_phone
1549 BULK COLLECT INTO l_all_sup_phone
1550 FROM PO_VENDOR_CONTACTS PVC
1551 ,AP_SUPPLIER_SITES_ALL ASSA
1552 WHERE PVC.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
1553 AND ASSA.VENDOR_ID=l_vender_id --using variable l_vender_id
1554 AND nvl(ASSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y';
1555
1556 EXCEPTION
1557 WHEN NO_DATA_FOUND THEN
1558 l_all_sup_phone := null;
1559 END;
1560
1561 --Use phone number only when there is just 1 record, otherwise leave blank
1562 IF l_all_sup_phone.count = 1
1563 THEN
1564 l_tp_phonenumber := l_all_sup_phone(1);
1565 ELSE
1566 l_tp_phonenumber := '';
1567 END IF; --sup phone
1568
1569 l_row_count := l_row_count+1;
1570 --Output a row of third party in TXT file with columns number, name,
1571 -- category, territory, phone number, address, credit_level
1572 /*FND_FILE.put_line(FND_FILE.output,
1573 RPAD(l_tp_number,10, ' ')
1574 ||'| '|| RPAD(nvl(l_tp_name, ' '),40,' ')
1575 ||'| '|| RPAD(nvl(l_tp_ctg_number, ' '),20,' ')
1576 ||'| '|| RPAD(nvl(l_tp_territory, ' '),20,' ')
1577 ||'| '|| RPAD(nvl(l_tp_phonenumber, ' '),20,' ')
1578 ||'| '|| RPAD(nvl(l_tp_address, ' '),40,' ')
1579 ||'| '|| l_tp_credit_level);*/
1580 FND_FILE.put_line(FND_FILE.output,
1581 '"' ||l_tp_number || '"'
1582 ||l_seperator|| '"' ||l_tp_name || '"'
1583 ||l_seperator|| '"' ||l_tp_ctg_number || '"'
1584 ||l_seperator|| '"' ||l_tp_territory || '"'
1585 ||l_seperator|| '"' ||l_tp_phonenumber || '"'
1586 ||l_seperator|| '"' ||l_tp_address || '"'
1587 ||l_seperator|| '"' ||l_tp_credit_level || '"'
1588 );
1589 END LOOP;
1590 CLOSE c_tp_sup;
1591
1592 --Export all third parties from Receivable into the format predefined flat file
1593 l_tp_ctg_number:= l_cust_meaning;
1594 OPEN c_tp_cust;
1595 LOOP
1596 FETCH c_tp_cust INTO l_cust_account_id
1597 ,l_party_id
1598 ,l_tp_number
1599 ,l_tp_name
1600 /*,l_tp_ctg_number*/
1601 ,l_tp_credit_level
1602 ;
1603 EXIT WHEN c_tp_cust%NOTFOUND;
1604
1605 --Get Customer Territory/Address
1606 l_all_cust_city_addr := null;
1607 BEGIN
1608 SELECT DISTINCT
1609 nvl(LOC.CITY, '') cust_city
1610 /* --JiaQian make it sure that get city from column 'city'
1611 nvl(LOC.PROVINCE,
1612 nvl(LOC.STATE, '')) sup_city
1613 */
1614 ,nvl(LOC.ADDRESS1, '') cust_addr
1615 BULK COLLECT INTO l_all_cust_city_addr
1616 FROM HZ_CUST_ACCT_SITES_ALL ADDR
1617 ,HZ_LOCATIONS LOC
1618 ,HZ_PARTY_SITES PARTY_SITE
1619 ,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
1620 ,HZ_CUST_SITE_USES_ALL SU
1621 ,HR_ORGANIZATION_INFORMATION HOI
1622 WHERE --ADDR.CUST_ACCOUNT_ID alias CUSTOMER_ID in AR_ADDRESSES_V
1623 ADDR.CUST_ACCOUNT_ID = l_cust_account_id --using variable l_cust_account_id
1624 and ADDR.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1625 and LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1626 and nvl(LOC.LANGUAGE, userenv('LANG')) = userenv('LANG')
1627 and LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1628 and NVL(ADDR.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
1629 --Check Customer site of OUs under current LE
1630 AND HOI.org_information_context = 'Operating Unit Information'
1631 AND HOI.Org_Information2 = l_le_id --using variable l_le_id
1632 AND HOI.Org_Information3 = l_ledger_id --using variable l_ledger_id
1633 AND ADDR.org_id = HOI.ORGANIZATION_ID
1634 -- Check "Primary Bill To"
1635 and SU.CUST_ACCT_SITE_ID= ADDR.CUST_ACCT_SITE_ID --alias address_id in HZ_SITE_USES_V
1636 and SU.SITE_USE_CODE = 'BILL_TO'
1637 and nvl(SU.PRIMARY_FLAG, 'N') = 'Y'
1638 ;
1639 EXCEPTION
1640 WHEN NO_DATA_FOUND THEN
1641 l_all_cust_city_addr := null;
1642 END;
1643
1644 --Use city/addr only when there is just 1 record, otherwise leave blanks
1645 IF l_all_cust_city_addr.count = 1
1646 THEN
1647 l_cust_city_addr := l_all_cust_city_addr(1);
1648 l_tp_territory := l_cust_city_addr.city;
1649 l_tp_address := l_cust_city_addr.addr;
1650 ELSE
1651 l_tp_territory := '';
1652 l_tp_address := '';
1653 END IF; --customer city/addr
1654
1655 --Get Customer Phone which are Active status and Telephone type,
1656 --Order by PRIMARY flag and Preferred flag.
1657 --1. The PRIMARY one and Preferred one can both only have 1 record for telcommunications(telphone,mobile,...).
1658 --2. The first one, or the one has set to be the PRIMARY one will be set as PRIMARY if
1659 -- there no PRIMARY one selected by user.
1660 l_all_cust_phone := null;
1661 BEGIN
1662 SELECT
1663 DECODE(HCP.PRIMARY_FLAG, 'Y', 'PRIMARY',
1664 DECODE(HCP.PRIMARY_BY_PURPOSE, 'Y', 'PREFERRED', 'NORMAL')
1665 ) cust_phone_priority
1666 ,NVL(HCP.PHONE_COUNTRY_CODE,'')
1667 || DECODE(NVL(HCP.PHONE_COUNTRY_CODE,''),'','','-')
1668 || NVL(HCP.PHONE_AREA_CODE,'')
1669 || DECODE(NVL(HCP.PHONE_AREA_CODE,''),'','','-')
1670 || HCP.PHONE_NUMBER cust_phone
1671 BULK COLLECT INTO l_all_cust_phone
1672 FROM HZ_CONTACT_POINTS HCP
1673 ,HZ_PARTY_SITES HPS
1674 WHERE HCP.OWNER_TABLE_ID(+)=HPS.PARTY_SITE_ID
1675 AND HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
1676 AND NVL(HCP.PRIMARY_FLAG,'')='Y'
1677 AND NVL(HCP.STATUS,'')='A' --only 'Active' one
1678 AND NVL(HCP.CONTACT_POINT_TYPE,'')='PHONE'
1679 AND NVL(HCP.PHONE_LINE_TYPE,'')='GEN' --only 'Telephone' type, just the code 'GEN'
1680 AND HPS.PARTY_ID = l_party_id --using variable l_cust_account_id
1681 ORDER BY HCP.primary_flag desc,
1682 HCP.primary_by_purpose desc
1683 ;
1684 EXCEPTION
1685 WHEN NO_DATA_FOUND THEN
1686 l_all_cust_phone := null;
1687 END;
1688
1689 --If multiple, the selection priority is base on "Primary", then "Preferred", then sequence.
1690 -- Return blank if not defined or multiple.
1691 IF l_all_cust_phone.count = 1
1692 THEN
1693 l_tp_phonenumber := l_all_cust_phone(1).phone;
1694 ELSIF l_all_cust_phone.count>1
1695 THEN
1696 l_cust_phone := l_all_cust_phone(1);
1697 IF l_cust_phone.priority = 'PRIMARY' OR l_cust_phone.priority = 'PREFERRED'
1698 THEN
1699 l_tp_phonenumber := l_cust_phone.phone;
1700 ELSE --surely two NORMAL ones and thus leave it blank
1701 l_tp_phonenumber := '';
1702 END IF;
1703 ELSE --not defined
1704 l_tp_phonenumber := '';
1705 END IF; --customer phone
1706
1707 l_row_count := l_row_count+1;
1708 --Output a row of l_tp_number, l_tp_name, l_tp_ctg_number,
1709 -- l_tp_territory, l_tp_phonenumber, l_tp_address, l_tp_credit_level in TXT file;
1710 /*FND_FILE.put_line(FND_FILE.output, l_tp_number ||'| '|| l_tp_name ||'| '|| l_tp_ctg_number
1711 ||'| '|| l_tp_territory ||'| '|| l_tp_phonenumber ||'| '|| l_tp_address
1712 ||'| '|| l_tp_credit_level);*/
1713 /*FND_FILE.put_line(FND_FILE.output,
1714 RPAD(l_tp_number,10, ' ')
1715 ||'| '|| RPAD(nvl(l_tp_name, ' '),40,' ')
1716 ||'| '|| RPAD(nvl(l_tp_ctg_number, ' '),20,' ')
1717 ||'| '|| RPAD(nvl(l_tp_territory, ' '),20,' ')
1718 ||'| '|| RPAD(nvl(l_tp_phonenumber, ' '),20,' ')
1719 ||'| '|| RPAD(nvl(l_tp_address, ' '),40,' ')
1720 ||'| '|| l_tp_credit_level);*/
1721 FND_FILE.put_line(FND_FILE.output,
1722 '"' ||l_tp_number || '"'
1723 ||l_seperator|| '"' ||l_tp_name || '"'
1724 ||l_seperator|| '"' ||l_tp_ctg_number || '"'
1725 ||l_seperator|| '"' ||l_tp_territory || '"'
1726 ||l_seperator|| '"' ||l_tp_phonenumber || '"'
1727 ||l_seperator|| '"' ||l_tp_address || '"'
1728 ||l_seperator|| '"' ||l_tp_credit_level || '"'
1729 );
1730 END LOOP;
1731 CLOSE c_tp_cust;
1732
1733 IF l_row_count = 0 --No data found
1734 THEN
1735 raise JA_CN_NO_DATA_FOUND;
1736 END IF;
1737
1738 retcode := 0;
1739 errbuf := '';
1740 EXCEPTION
1741 WHEN JA_CN_NO_DATA_FOUND THEN
1742 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
1743 IF (l_proc_level >= l_dbg_level)
1744 THEN
1745 FND_LOG.String( l_proc_level
1746 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
1747 ,l_msg_no_data_found);
1748 END IF;
1749 retcode := 1;
1750 errbuf := l_msg_no_data_found;
1751 WHEN OTHERS THEN
1752 IF (l_proc_level >= l_dbg_level)
1753 THEN
1754 FND_LOG.String( l_proc_level
1755 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1756 ,SQLCODE||':'||SQLERRM
1757 );
1758 END IF; --(l_proc_level >= l_dbg_level)
1759 RAISE;
1760
1761 END Coa_TP_Export;
1762
1763
1764
1765 --==========================================================================
1766 -- PROCEDURE NAME:
1767 -- Coa_CC_Export Private
1768 --
1769 -- DESCRIPTION:
1770 -- This procedure exporting the Cost Center list as "subsidiary account"
1771 -- into format predefined flat file.
1772 --
1773 -- PARAMETERS:
1774 -- Out: errbuf NOCOPY VARCHAR2
1775 -- Out: retcode NOCOPY VARCHAR2
1776 -- In: P_COA_ID NUMBER Chart of account ID
1777 -- In: P_LEDGER_ID NUMBER ID of Set Of Book
1778 -- In: P_LE_ID NUMBER ID of Legal Entity
1779 --
1780 -- DESIGN REFERENCES:
1781 -- None
1782 --
1783 -- CHANGE HISTORY:
1784 -- 03/03/2006 Andrew Liu Created
1785 -- 04/24/2007 Yucheng Sun Updated
1786 --===========================================================================
1787 PROCEDURE Coa_CC_Export( errbuf OUT NOCOPY VARCHAR2
1788 ,retcode OUT NOCOPY VARCHAR2
1789 ,P_COA_ID IN NUMBER
1790 ,P_LEDGER_ID IN NUMBER
1791 ,P_LE_ID IN NUMBER
1792 ) IS
1793 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1794 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1795 l_proc_name VARCHAR2(100) :='Coa_CC_Export';
1796
1797 l_ledger_id NUMBER := P_LEDGER_ID;
1798 l_le_id NUMBER := P_LE_ID;
1799 l_coa_id NUMBER := P_COA_ID;
1800 l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
1801
1802 l_row_count NUMBER; --count of rows
1803 l_cc_number JA_CN_ACCOUNT_BALANCES.cost_center%TYPE;
1804 l_cc_name FND_FLEX_VALUES_TL.description%TYPE;
1805
1806 --Cursor to get cost_center from table JA_CN_ACCOUNT_BALANCES and description from table
1807 -- FND_FLEX_VALUES_TL, as Department number and name.
1808 --Because the value set of cost center can be changed manually as natural account, it is
1809 -- no sense to store cost center id in table JA_CN_ACCOUNT_BALANCES. Thus the name should
1810 -- be gotten with the full flow.
1811 CURSOR c_cc IS
1812 SELECT *
1813 FROM (
1814 SELECT DISTINCT
1815 FFV.FLEX_VALUE cc_number
1816 ,nvl(FFVT.description, '') cc_name
1817 FROM JA_CN_ACCOUNT_BALANCES BAL
1818 ,FND_ID_FLEX_SEGMENTS FIFS
1819 ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1820 ,FND_FLEX_VALUE_SETS FFVS
1821 ,FND_FLEX_VALUES_TL FFVT
1822 ,FND_FLEX_VALUES FFV
1823 ,GL_LEDGERS LEDGER
1824 WHERE BAL.Ledger_Id = l_ledger_id --using variable l_sob_id
1825 AND BAL.account_segment IS NOT NULL
1826 and BAL.cost_center IS NOT NULL
1827 --for name. OR: FFVT.flex_value_meaning = BAL.cost_center
1828 AND FFV.FLEX_VALUE = BAL.cost_center
1829 AND LEDGER.Ledger_Id = l_ledger_id --using variable l_ledger_id
1830 AND LEDGER.chart_of_accounts_id = FIFS.id_flex_num
1831 AND FIFS.id_flex_num = FSAV.id_flex_num
1832 AND FIFS.application_id = 101
1833 AND FIFS.application_id = FSAV.application_id
1834 AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
1835 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'FA_COST_CTR'
1836 AND FSAV.ATTRIBUTE_VALUE = 'Y'
1837 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
1838 AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
1839 AND FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
1840 AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
1841 ) tmp_cc_tbl
1842 ORDER BY tmp_cc_tbl.cc_number
1843 ;
1844
1845 BEGIN
1846 /*FND_FILE.put_line(FND_FILE.output,
1847 RPAD('Department number',20, ' ')
1848 ||'| '|| RPAD('Department name',40,' '));*/
1849
1850 --Export all cost centers into the format predefined flat file
1851 l_row_count := 0;
1852 OPEN c_cc;
1853 LOOP
1854 FETCH c_cc INTO l_cc_number
1855 ,l_cc_name
1856 ;
1857 EXIT WHEN c_cc%NOTFOUND;
1858 l_row_count := l_row_count+1;
1859 --Output a row of cost center in TXT file with columns number, name
1860 /*FND_FILE.put_line(FND_FILE.output,
1861 RPAD(l_cc_number,20, ' ')
1862 ||'| '|| RPAD(l_cc_name,40,' '));*/
1863 FND_FILE.put_line(FND_FILE.output,
1864 '"' ||l_cc_number || '"'
1865 ||l_seperator|| '"' ||l_cc_name || '"'
1866 );
1867 END LOOP;
1868 CLOSE c_cc;
1869
1870 IF l_row_count = 0 --No data found
1871 THEN
1872 raise JA_CN_NO_DATA_FOUND;
1873 END IF;
1874
1875 retcode := 0;
1876 errbuf := '';
1877 EXCEPTION
1878 WHEN JA_CN_NO_DATA_FOUND THEN
1879 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
1880 IF (l_proc_level >= l_dbg_level)
1881 THEN
1882 FND_LOG.String( l_proc_level
1883 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
1884 ,l_msg_no_data_found);
1885 END IF;
1886 retcode := 1;
1887 errbuf := l_msg_no_data_found;
1888 WHEN OTHERS THEN
1889 IF (l_proc_level >= l_dbg_level)
1890 THEN
1891 FND_LOG.String( l_proc_level
1892 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1893 ,SQLCODE||':'||SQLERRM
1894 );
1895 END IF; --(l_proc_level >= l_dbg_level)
1896 RAISE;
1897
1898 END Coa_CC_Export;
1899
1900
1901 --==========================================================================
1902 -- PROCEDURE NAME:
1903 -- Coa_Person_Export Private
1904 --
1905 -- DESCRIPTION:
1906 -- This procedure exporting the Personnel list as "subsidiary account"
1907 -- into format predefined flat file.
1908 --
1909 -- PARAMETERS:
1910 -- Out: errbuf NOCOPY VARCHAR2
1911 -- Out: retcode NOCOPY VARCHAR2
1912 -- In: P_COA_ID NUMBER Chart of accounts ID
1913 -- In: P_LEDGER_ID NUMBER ID of Set Of Book
1914 -- In: P_LE_ID NUMBER ID of Legal Entity
1915 --
1916 -- DESIGN REFERENCES:
1917 -- None
1918 --
1919 -- CHANGE HISTORY:
1920 -- 03/03/2006 Andrew Liu Created
1921 -- 04/24/2007 Yucheng Sun Updated
1922 --===========================================================================
1923 PROCEDURE Coa_Person_Export( errbuf OUT NOCOPY VARCHAR2
1924 ,retcode OUT NOCOPY VARCHAR2
1925 ,P_COA_ID IN NUMBER
1926 ,P_LEDGER_ID IN NUMBER
1927 ,P_LE_ID IN NUMBER
1928 ) IS
1929 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1930 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1931 l_proc_name VARCHAR2(100) :='Coa_Person_Export';
1932
1933 l_ledger_id NUMBER := P_LEDGER_ID;
1934 l_le_id NUMBER := P_LE_ID;
1935 l_coa_id NUMBER := P_COA_ID;
1936 l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
1937
1938 l_row_count NUMBER; --count of rows
1939 l_person_number JA_CN_ACCOUNT_BALANCES.personnel_number%TYPE;
1940 l_person_name VARCHAR2(50);
1941
1942 --Cursor to get personnel_number from table JA_CN_ACCOUNT_BALANCES,
1943 -- and personnel name from table PER_ALL_PEOPLE_F
1944 CURSOR c_person IS
1945 SELECT *
1946 FROM (
1947 SELECT DISTINCT
1948 BAL.personnel_number person_number
1949 ,nvl(PER.last_name||PER.first_name, '') person_name
1950 FROM JA_CN_ACCOUNT_BALANCES BAL
1951 ,PER_ALL_PEOPLE_F PER
1952 WHERE BAL.Ledger_Id = l_ledger_id --using variable l_ledger_id
1953 AND BAL.account_segment IS NOT NULL
1954 and BAL.personnel_id IS NOT NULL
1955 AND PER.person_id = BAL.personnel_id
1956 ) tmp_psn_tbl
1957 ORDER BY tmp_psn_tbl.person_number
1958 ;
1959
1960 BEGIN
1961 /*FND_FILE.put_line(FND_FILE.output,
1962 RPAD('Number',10, ' ')
1963 ||'| '|| RPAD('Name',40,' '));*/
1964
1965 --Export all persons into the format predefined flat file
1966 l_row_count := 0;
1967 OPEN c_person;
1968 LOOP
1969 FETCH c_person INTO l_person_number
1970 ,l_person_name
1971 ;
1972 EXIT WHEN c_person%NOTFOUND;
1973 l_row_count := l_row_count+1;
1974 --Output a row of person in TXT file with columns number, name;
1975 /*FND_FILE.put_line(FND_FILE.output,
1976 RPAD(l_person_number,10, ' ')
1977 ||'| '|| l_person_name);*/
1978 FND_FILE.put_line(FND_FILE.output,
1979 '"' ||l_person_number || '"'
1980 ||l_seperator|| '"' ||l_person_name || '"'
1981 );
1982 END LOOP;
1983 CLOSE c_person;
1984
1985 IF l_row_count = 0 --No data found
1986 THEN
1987 raise JA_CN_NO_DATA_FOUND;
1988 END IF;
1989
1990 retcode := 0;
1991 errbuf := '';
1992 EXCEPTION
1993 WHEN JA_CN_NO_DATA_FOUND THEN
1994 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
1995 IF (l_proc_level >= l_dbg_level)
1996 THEN
1997 FND_LOG.String( l_proc_level
1998 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
1999 ,l_msg_no_data_found);
2000 END IF;
2001 retcode := 1;
2002 errbuf := l_msg_no_data_found;
2003 WHEN OTHERS THEN
2004 IF (l_proc_level >= l_dbg_level)
2005 THEN
2006 FND_LOG.String( l_proc_level
2007 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
2008 ,SQLCODE||':'||SQLERRM
2009 );
2010 END IF; --(l_proc_level >= l_dbg_level)
2011 RAISE;
2012
2013 END Coa_Person_Export;
2014
2015 --==========================================================================
2016 -- PROCEDURE NAME:
2017 -- Coa_Export Public
2018 --
2019 -- DESCRIPTION:
2020 -- This procedure calls COA Export programs according to
2021 -- the specified account type.
2022 --
2023 -- PARAMETERS:
2024 -- Out: errbuf NOCOPY VARCHAR2
2025 -- Out: retcode NOCOPY VARCHAR2
2026 -- In: P_COA_ID NUMBER chart of accounts ID
2027 -- In: P_LEDGER_ID NUMBER ID of LEDGER
2028 -- In: P_LE_ID NUMBER ID of Legal Entity
2029 -- In: P_ACCOUNT_TYPE VARCHAR2 Type of the account
2030 -- In: P_XML_TEMPLATE_LANGUAGE VARCHAR2 template language of NA exception report
2031 -- In: P_XML_TEMPLATE_TERRITORY VARCHAR2 template territory of NA exception report
2032 -- In: P_XML_OUTPUT_FORMAT VARCHAR2 output format of NA exception report
2033 --
2034 -- DESIGN REFERENCES:
2035 -- None
2036 --
2037 -- CHANGE HISTORY:
2038 -- 03/03/2006 Andrew Liu Created
2039 -- 04/24/2007 Yucheng Sun Updated
2040 --===========================================================================
2041 PROCEDURE Coa_Export( errbuf OUT NOCOPY VARCHAR2
2042 ,retcode OUT NOCOPY VARCHAR2
2043 ,P_COA_ID IN NUMBER
2044 ,P_LEDGER_ID IN NUMBER
2045 ,P_LE_ID IN NUMBER
2046 ,P_ACCOUNT_TYPE IN VARCHAR2
2047 ,P_XML_TEMPLATE_LANGUAGE IN VARCHAR2
2048 ,P_XML_TEMPLATE_TERRITORY IN VARCHAR2
2049 ,P_XML_OUTPUT_FORMAT IN VARCHAR2
2050 ) IS
2051
2052 l_account_type varchar2(30):=P_ACCOUNT_TYPE;
2053
2054 BEGIN
2055 /*
2056 Coa_Person_export( errbuf => errbuf
2057 ,retcode => retcode
2058 ,P_COA_ID => P_COA_ID
2059 ,P_LEDGER_ID => P_LEDGER_ID
2060 ,P_LE_ID => P_LE_ID
2061 );
2062 */
2063 IF P_ACCOUNT_TYPE = 'NA'
2064 THEN
2065 Coa_NA_Export( errbuf => errbuf
2066 ,retcode => retcode
2067 ,P_COA_ID => P_COA_ID
2068 ,P_LEDGER_ID => P_LEDGER_ID
2069 ,P_LE_ID => P_LE_ID
2070 ,P_XML_TEMPLATE_LANGUAGE => P_XML_TEMPLATE_LANGUAGE
2071 ,P_XML_TEMPLATE_TERRITORY => P_XML_TEMPLATE_TERRITORY
2072 ,P_XML_OUTPUT_FORMAT => P_XML_OUTPUT_FORMAT
2073 );
2074
2075 ELSIF P_ACCOUNT_TYPE = 'PJ'
2076 THEN
2077 Coa_PJ_export( errbuf => errbuf
2078 ,retcode => retcode
2079 ,P_COA_ID => P_COA_ID
2080 ,P_LEDGER_ID => P_LEDGER_ID
2081 ,P_LE_ID => P_LE_ID
2082 );
2083
2084 ELSIF P_ACCOUNT_TYPE = 'TP'
2085 THEN
2086 Coa_TP_export( errbuf => errbuf
2087 ,retcode => retcode
2088 ,P_COA_ID => P_COA_ID
2089 ,P_LEDGER_ID => P_LEDGER_ID
2090 ,P_LE_ID => P_LE_ID
2091 );
2092
2093 ELSIF P_ACCOUNT_TYPE = 'CC'
2094 THEN
2095 Coa_CC_export( errbuf => errbuf
2096 ,retcode => retcode
2097 ,P_COA_ID => P_COA_ID
2098 ,P_LEDGER_ID => P_LEDGER_ID
2099 ,P_LE_ID => P_LE_ID
2100 );
2101
2102 ELSIF P_ACCOUNT_TYPE = 'PERSON'
2103 THEN
2104 Coa_Person_export( errbuf => errbuf
2105 ,retcode => retcode
2106 ,P_COA_ID => P_COA_ID
2107 ,P_LEDGER_ID => P_LEDGER_ID
2108 ,P_LE_ID => P_LE_ID
2109 );
2110 END IF;
2111 END Coa_Export;
2112
2113 BEGIN
2114 -- Initialization
2115 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
2116 ,NAME => 'JA_CN_NO_DATA_FOUND'
2117 );
2118 l_msg_no_data_found := FND_MESSAGE.Get;
2119 END JA_CN_COA_EXP_PKG;
2120
2121
2122