[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.12010000.2 2009/05/25 08:27:47 shyan ship $
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 -- 05/19/2009 Chaoqun Wu Updated for fixing bug#8420682
1322 --===========================================================================
1323 PROCEDURE Coa_TP_Export( errbuf OUT NOCOPY VARCHAR2
1324 ,retcode OUT NOCOPY VARCHAR2
1325 ,P_COA_ID IN NUMBER
1326 ,P_LEDGER_ID IN NUMBER
1327 ,P_LE_ID IN NUMBER
1328 ) IS
1329 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1330 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1331 l_proc_name VARCHAR2(100) :='Coa_TP_Export';
1332
1333 l_ledger_id NUMBER := P_LEDGER_ID; -- LEDGER ID
1334 l_le_id NUMBER := P_LE_ID; -- LEGAL ENTITY ID
1335 l_coa_id NUMBER := P_COA_ID; -- CHART OF ACCOUT ID
1336 l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
1337
1338 l_sup_meaning VARCHAR2(50);
1339 l_cust_meaning VARCHAR2(50);
1340
1341 l_row_count NUMBER; --count of rows
1342 l_tp_number VARCHAR2(100);
1343 l_tp_name VARCHAR2(360);
1344 l_tp_ctg_number VARCHAR2(60);
1345 l_tp_territory VARCHAR2(100);
1346 l_tp_phonenumber VARCHAR2(100);
1347 l_tp_address VARCHAR2(240);
1348 l_tp_credit_level VARCHAR2(60);
1349
1350 --For supplier
1351 l_vender_id PO_VENDORS.VENDOR_ID%TYPE;
1352 --For city and address of supplier
1353 TYPE t_sup_city_addr IS RECORD ( city PO_VENDOR_SITES_ALL.CITY%TYPE
1354 ,addr PO_VENDOR_SITES_ALL.ADDRESS_LINE1%TYPE
1355 );
1356 TYPE t_sup_city_addr_array IS TABLE OF t_sup_city_addr;
1357 l_all_sup_city_addr t_sup_city_addr_array;
1358 l_sup_city_addr t_sup_city_addr;
1359 --For phone number of supplier
1360 TYPE t_sup_phone_array IS TABLE OF VARCHAR2(100);
1361 l_all_sup_phone t_sup_phone_array;
1362
1363 --For Customer
1364 l_cust_account_id HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;
1365 l_party_id HZ_CUST_ACCOUNTS.PARTY_ID%TYPE;
1366 --For city and address of Customer
1367 TYPE t_cust_city_addr IS RECORD ( city HZ_LOCATIONS.CITY%TYPE
1368 ,addr HZ_LOCATIONS.address1%TYPE
1369 );
1370 TYPE t_cust_city_addr_array IS TABLE OF t_cust_city_addr;
1371 l_all_cust_city_addr t_cust_city_addr_array;
1372 l_cust_city_addr t_cust_city_addr;
1373 --For phone number of Customer
1374 TYPE t_cust_phone IS RECORD ( priority VARCHAR2(10)
1375 ,phone VARCHAR2(100)
1376 );
1377 TYPE t_cust_phone_array IS TABLE OF t_cust_phone;
1378 l_all_cust_phone t_cust_phone_array;
1379 l_cust_phone t_cust_phone;
1380
1381 --Cursor to get basic info for suppliers included in table JA_CN_ACCOUNT_BALANCES
1382 --Except Territory, Phone number, and Address
1383 CURSOR c_tp_sup IS
1384 SELECT *
1385 FROM (
1386 SELECT DISTINCT
1387 PV.vendor_id vender_id
1388 /*,'S'||nvl(PV.SEGMENT1, '') sup_number*/
1389 --?? ,nvl(PV.SEGMENT1, '') sup_number --column vendor_number of view AP_VENDORS_V
1390 ,nvl(BAL.Third_Party_Number,'') sup_number -- temp solutin ????
1391 ,nvl(PV.VENDOR_NAME, '') sup_name
1392 /*,nvl(LC_TYPE.DISPLAYED_FIELD, '') sup_type --vendor_type_disp*/
1393 ,''
1394 FROM JA_CN_ACCOUNT_BALANCES BAL
1395 ,PO_VENDORS PV
1396 /*,PO_LOOKUP_CODES LC_TYPE*/
1397 WHERE BAL.Ledger_Id = l_ledger_id --using variable l_sob_id
1398 AND BAL.account_segment IS NOT NULL
1399 AND BAL.THIRD_PARTY_ID IS NOT NULL
1400 AND nvl(BAL.THIRD_PARTY_TYPE, 'X') = 'S'
1401 AND BAL.THIRD_PARTY_ID = PV.vendor_id
1402 ) tmp_sup_tbl
1403 -- add order by to keep the output item's seqence
1404 ORDER BY tmp_sup_tbl.sup_number
1405 /*-- Type
1406 AND LC_TYPE.LOOKUP_CODE(+) = PV.VENDOR_TYPE_LOOKUP_CODE
1407 and LC_TYPE.LOOKUP_TYPE(+) = 'VENDOR TYPE'*/
1408 ;
1409
1410 --Cursor to get basic info for customers of current SOB
1411 --Except Territory, Phone number, and Address
1412 CURSOR c_tp_cust IS
1413 SELECT *
1414 FROM (
1415 SELECT DISTINCT
1416 CUST.CUST_ACCOUNT_ID cust_account_id
1417 ,CUST.PARTY_ID party_id
1418 /*,'C'||nvl(CUST.ACCOUNT_NUMBER, '') cust_number*/
1419 --?? ,nvl(CUST_PARTY.Party_Number, '') cust_number -- take hz_parties.Party_Number to keep consistency with sla export
1420 --,nvl(BAL.Third_Party_Number,'') cust_number -- temp solutin ???? --Deleted by Chaoqun for fixing bug#8420682 on 19-May-2009
1421 ,nvl(CUST_PARTY.PARTY_NUMBER,'') cust_number --Updated by Chaoqun for fixing bug#8420682 on 19-May-2009
1422 ,nvl(CUST_PARTY.PARTY_NAME, '') cust_name
1423 /*,nvl(L_CLASS.MEANING, '') cust_class --CUSTOMER_CLASS_MEANING*/
1424 ,nvl(CP.CREDIT_RATING, '') cust_credit
1425 FROM JA_CN_ACCOUNT_BALANCES BAL
1426 ,HZ_CUST_ACCOUNTS CUST
1427 ,HZ_PARTIES CUST_PARTY
1428 /*,AR_LOOKUPS L_CLASS*/
1429 ,HZ_CUSTOMER_PROFILES CP
1430 WHERE BAL.Ledger_Id = l_ledger_id --using variable l_ledger_id
1431 AND BAL.LEGAL_ENTITY_ID=l_le_id --using variable l_le_id
1432 AND BAL.account_segment IS NOT NULL
1433 AND BAL.THIRD_PARTY_ID IS NOT NULL
1434 AND nvl(BAL.THIRD_PARTY_TYPE, 'X') = 'C'
1435 AND BAL.THIRD_PARTY_ID = CUST.CUST_ACCOUNT_ID
1436 AND CUST.PARTY_ID = CUST_PARTY.PARTY_ID
1437 /*-- Class
1438 AND CUST.CUSTOMER_CLASS_CODE = L_CLASS.LOOKUP_CODE(+)
1439 and L_CLASS.LOOKUP_TYPE(+) = 'CUSTOMER CLASS'*/
1440 -- Credit rating
1441 AND CP.CUST_ACCOUNT_ID(+) = CUST.CUST_ACCOUNT_ID
1442 and CP.site_use_id is null
1443 ) tmp_cst_tbl
1444 -- add order by to keep the output item's seqence
1445 order by tmp_cst_tbl.cust_number
1446 ;
1447
1448 BEGIN
1449 /*FND_FILE.put_line(FND_FILE.output,
1450 RPAD('Number',10, ' ')
1451 ||'| '|| RPAD('Name',40,' ')
1452 ||'| '|| RPAD('Category',20,' ')
1453 ||'| '|| RPAD('Territory',20,' ')
1454 ||'| '|| RPAD('Phone',20,' ')
1455 ||'| '|| RPAD('Address',40,' ')
1456 ||'| '|| RPAD('Credit Level',20,' '));*/
1457
1458 --Get meaning of Supplier and Customer
1459 SELECT nvl(FLV.meaning,'') sup_meaning
1460 ,nvl(FLV1.meaning,'') cust_meaning
1461 INTO l_sup_meaning
1462 ,l_cust_meaning
1463 FROM FND_LOOKUP_VALUES FLV
1464 ,FND_LOOKUP_VALUES FLV1
1465 WHERE FLV.lookup_code = 'S'
1466 AND FLV.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
1467 AND FLV.LANGUAGE = userenv('LANG')
1468 AND FLV1.lookup_code = 'C'
1469 AND FLV1.lookup_type = 'JA_CN_THIRDPARTY_TYPE'
1470 AND FLV1.LANGUAGE = userenv('LANG')
1471 ;
1472
1473 l_row_count := 0;
1474
1475 --Export all third parties from Payable into the format predefined flat file
1476 l_tp_ctg_number:= l_sup_meaning;
1477 OPEN c_tp_sup;
1478 LOOP
1479 FETCH c_tp_sup INTO l_vender_id
1480 ,l_tp_number
1481 ,l_tp_name
1482 /*,l_tp_ctg_number */
1483 ,l_tp_credit_level
1484 ;
1485 EXIT WHEN c_tp_sup%NOTFOUND;
1486
1487 --Get Sup Territory/Address
1488 l_all_sup_city_addr := null;
1489 BEGIN
1490 SELECT DISTINCT
1491 nvl(PVSA.CITY, '') sup_city
1492 /* --JiaQian make it sure that get city from column 'city'
1493 nvl(PVSA.PROVINCE,
1494 nvl(PVSA.STATE, '')) sup_city
1495 */
1496 ,nvl(PVSA.ADDRESS_LINE1, '') sup_addr
1497 BULK COLLECT INTO l_all_sup_city_addr
1498 FROM PO_VENDOR_SITES_ALL PVSA
1499 ,HR_ORGANIZATION_INFORMATION HOI
1500 WHERE --Check "Primary Pay" Vendor site of OUs under current LE
1501 HOI.org_information_context = 'Operating Unit Information'
1502 AND HOI.Org_Information2 = l_le_id --using variable l_le_id
1503 AND HOI.Org_Information3 = l_ledger_id --using variable l_ledger_id
1504 AND PVSA.Org_id = HOI.ORGANIZATION_ID
1505 AND PVSA.vendor_id = l_vender_id --using variable l_vender_id
1506 and nvl(PVSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y'
1507 ;
1508 EXCEPTION
1509 WHEN NO_DATA_FOUND THEN
1510 l_all_sup_city_addr := null;
1511 END;
1512
1513 --Use city/addr only when there is just 1 record, otherwise leave blanks
1514 IF l_all_sup_city_addr.count = 1
1515 THEN
1516 l_sup_city_addr := l_all_sup_city_addr(1);
1517 l_tp_territory := l_sup_city_addr.city;
1518 l_tp_address := l_sup_city_addr.addr;
1519 ELSE
1520 l_tp_territory := '';
1521 l_tp_address := '';
1522 END IF; --sup city/addr
1523
1524 --Get Sup Phone
1525 l_all_sup_phone := null;
1526 BEGIN
1527 /*--The "Primary Pay" site contact number defined under Contacts tab of supplier Site define page.
1528 SELECT DISTINCT
1529 '0' || DECODE(nvl(PVC.AREA_CODE, ''), '', '', PVC.AREA_CODE || '-')
1530 || nvl(PVC.PHONE, '') sup_phone
1531 BULK COLLECT INTO l_all_sup_phone
1532 FROM PO_VENDOR_CONTACTS PVC
1533 ,PO_VENDOR_SITES_ALL PVSA
1534 ,HR_ORGANIZATION_INFORMATION HOI
1535 WHERE PVC.vendor_site_id = PVSA.vendor_site_id
1536 --Check "Primary Pay" Vendor site of OUs under current LE
1537 AND HOI.org_information_context = 'Operating Unit Information'
1538 AND HOI.Org_Information2 = l_le_id --using variable l_le_id
1539 AND HOI.Org_Information3 = l_sob_id --using variable l_sob_id
1540 AND PVSA.Org_id = HOI.ORGANIZATION_ID
1541 AND PVSA.vendor_id = l_vender_id --using variable l_vender_id
1542 and nvl(PVSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y'
1543 ;*/
1544
1545 --The "Primary Pay" site Communication Voice number defined under General tab of supplier Site define page.
1546 -- relationship : ASSA.vendor_id-->ASSA.vendor_site_id-->PVC.vendor_site_id-->PVC.PHONE
1547 SELECT DISTINCT
1548 nvl(PVC.AREA_CODE, '')
1549 || DECODE(NVL(PVC.AREA_CODE, ''),'','','-')
1550 || nvl(PVC.PHONE, '') sup_phone
1551 BULK COLLECT INTO l_all_sup_phone
1552 FROM PO_VENDOR_CONTACTS PVC
1553 ,AP_SUPPLIER_SITES_ALL ASSA
1554 WHERE PVC.VENDOR_SITE_ID=ASSA.VENDOR_SITE_ID
1555 AND ASSA.VENDOR_ID=l_vender_id --using variable l_vender_id
1556 AND nvl(ASSA.PRIMARY_PAY_SITE_FLAG, 'N') = 'Y';
1557
1558 EXCEPTION
1559 WHEN NO_DATA_FOUND THEN
1560 l_all_sup_phone := null;
1561 END;
1562
1563 --Use phone number only when there is just 1 record, otherwise leave blank
1564 IF l_all_sup_phone.count = 1
1565 THEN
1566 l_tp_phonenumber := l_all_sup_phone(1);
1567 ELSE
1568 l_tp_phonenumber := '';
1569 END IF; --sup phone
1570
1571 l_row_count := l_row_count+1;
1572 --Output a row of third party in TXT file with columns number, name,
1573 -- category, territory, phone number, address, credit_level
1574 /*FND_FILE.put_line(FND_FILE.output,
1575 RPAD(l_tp_number,10, ' ')
1576 ||'| '|| RPAD(nvl(l_tp_name, ' '),40,' ')
1577 ||'| '|| RPAD(nvl(l_tp_ctg_number, ' '),20,' ')
1578 ||'| '|| RPAD(nvl(l_tp_territory, ' '),20,' ')
1579 ||'| '|| RPAD(nvl(l_tp_phonenumber, ' '),20,' ')
1580 ||'| '|| RPAD(nvl(l_tp_address, ' '),40,' ')
1581 ||'| '|| l_tp_credit_level);*/
1582 FND_FILE.put_line(FND_FILE.output,
1583 '"' ||l_tp_number || '"'
1584 ||l_seperator|| '"' ||l_tp_name || '"'
1585 ||l_seperator|| '"' ||l_tp_ctg_number || '"'
1586 ||l_seperator|| '"' ||l_tp_territory || '"'
1587 ||l_seperator|| '"' ||l_tp_phonenumber || '"'
1588 ||l_seperator|| '"' ||l_tp_address || '"'
1589 ||l_seperator|| '"' ||l_tp_credit_level || '"'
1590 );
1591 END LOOP;
1592 CLOSE c_tp_sup;
1593
1594 --Export all third parties from Receivable into the format predefined flat file
1595 l_tp_ctg_number:= l_cust_meaning;
1596 OPEN c_tp_cust;
1597 LOOP
1598 FETCH c_tp_cust INTO l_cust_account_id
1599 ,l_party_id
1600 ,l_tp_number
1601 ,l_tp_name
1602 /*,l_tp_ctg_number*/
1603 ,l_tp_credit_level
1604 ;
1605 EXIT WHEN c_tp_cust%NOTFOUND;
1606
1607 --Get Customer Territory/Address
1608 l_all_cust_city_addr := null;
1609 BEGIN
1610 SELECT DISTINCT
1611 nvl(LOC.CITY, '') cust_city
1612 /* --JiaQian make it sure that get city from column 'city'
1613 nvl(LOC.PROVINCE,
1614 nvl(LOC.STATE, '')) sup_city
1615 */
1616 ,nvl(LOC.ADDRESS1, '') cust_addr
1617 BULK COLLECT INTO l_all_cust_city_addr
1618 FROM HZ_CUST_ACCT_SITES_ALL ADDR
1619 ,HZ_LOCATIONS LOC
1620 ,HZ_PARTY_SITES PARTY_SITE
1621 ,HZ_LOC_ASSIGNMENTS LOC_ASSIGN
1622 ,HZ_CUST_SITE_USES_ALL SU
1623 ,HR_ORGANIZATION_INFORMATION HOI
1624 WHERE --ADDR.CUST_ACCOUNT_ID alias CUSTOMER_ID in AR_ADDRESSES_V
1625 ADDR.CUST_ACCOUNT_ID = l_cust_account_id --using variable l_cust_account_id
1626 and ADDR.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1627 and LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1628 and nvl(LOC.LANGUAGE, userenv('LANG')) = userenv('LANG')
1629 and LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
1630 and NVL(ADDR.ORG_ID,-99) = NVL(LOC_ASSIGN.ORG_ID,-99)
1631 --Check Customer site of OUs under current LE
1632 AND HOI.org_information_context = 'Operating Unit Information'
1633 AND HOI.Org_Information2 = l_le_id --using variable l_le_id
1634 AND HOI.Org_Information3 = l_ledger_id --using variable l_ledger_id
1635 AND ADDR.org_id = HOI.ORGANIZATION_ID
1636 -- Check "Primary Bill To"
1637 and SU.CUST_ACCT_SITE_ID= ADDR.CUST_ACCT_SITE_ID --alias address_id in HZ_SITE_USES_V
1638 and SU.SITE_USE_CODE = 'BILL_TO'
1639 and nvl(SU.PRIMARY_FLAG, 'N') = 'Y'
1640 ;
1641 EXCEPTION
1642 WHEN NO_DATA_FOUND THEN
1643 l_all_cust_city_addr := null;
1644 END;
1645
1646 --Use city/addr only when there is just 1 record, otherwise leave blanks
1647 IF l_all_cust_city_addr.count = 1
1648 THEN
1649 l_cust_city_addr := l_all_cust_city_addr(1);
1650 l_tp_territory := l_cust_city_addr.city;
1651 l_tp_address := l_cust_city_addr.addr;
1652 ELSE
1653 l_tp_territory := '';
1654 l_tp_address := '';
1655 END IF; --customer city/addr
1656
1657 --Get Customer Phone which are Active status and Telephone type,
1658 --Order by PRIMARY flag and Preferred flag.
1659 --1. The PRIMARY one and Preferred one can both only have 1 record for telcommunications(telphone,mobile,...).
1660 --2. The first one, or the one has set to be the PRIMARY one will be set as PRIMARY if
1661 -- there no PRIMARY one selected by user.
1662 l_all_cust_phone := null;
1663 BEGIN
1664 SELECT
1665 DECODE(HCP.PRIMARY_FLAG, 'Y', 'PRIMARY',
1666 DECODE(HCP.PRIMARY_BY_PURPOSE, 'Y', 'PREFERRED', 'NORMAL')
1667 ) cust_phone_priority
1668 ,NVL(HCP.PHONE_COUNTRY_CODE,'')
1669 || DECODE(NVL(HCP.PHONE_COUNTRY_CODE,''),'','','-')
1670 || NVL(HCP.PHONE_AREA_CODE,'')
1671 || DECODE(NVL(HCP.PHONE_AREA_CODE,''),'','','-')
1672 || HCP.PHONE_NUMBER cust_phone
1673 BULK COLLECT INTO l_all_cust_phone
1674 FROM HZ_CONTACT_POINTS HCP
1675 ,HZ_PARTY_SITES HPS
1676 WHERE HCP.OWNER_TABLE_ID(+)=HPS.PARTY_SITE_ID
1677 AND HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
1678 AND NVL(HCP.PRIMARY_FLAG,'')='Y'
1679 AND NVL(HCP.STATUS,'')='A' --only 'Active' one
1680 AND NVL(HCP.CONTACT_POINT_TYPE,'')='PHONE'
1681 AND NVL(HCP.PHONE_LINE_TYPE,'')='GEN' --only 'Telephone' type, just the code 'GEN'
1682 AND HPS.PARTY_ID = l_party_id --using variable l_cust_account_id
1683 ORDER BY HCP.primary_flag desc,
1684 HCP.primary_by_purpose desc
1685 ;
1686 EXCEPTION
1687 WHEN NO_DATA_FOUND THEN
1688 l_all_cust_phone := null;
1689 END;
1690
1691 --If multiple, the selection priority is base on "Primary", then "Preferred", then sequence.
1692 -- Return blank if not defined or multiple.
1693 IF l_all_cust_phone.count = 1
1694 THEN
1695 l_tp_phonenumber := l_all_cust_phone(1).phone;
1696 ELSIF l_all_cust_phone.count>1
1697 THEN
1698 l_cust_phone := l_all_cust_phone(1);
1699 IF l_cust_phone.priority = 'PRIMARY' OR l_cust_phone.priority = 'PREFERRED'
1700 THEN
1701 l_tp_phonenumber := l_cust_phone.phone;
1702 ELSE --surely two NORMAL ones and thus leave it blank
1703 l_tp_phonenumber := '';
1704 END IF;
1705 ELSE --not defined
1706 l_tp_phonenumber := '';
1707 END IF; --customer phone
1708
1709 l_row_count := l_row_count+1;
1710 --Output a row of l_tp_number, l_tp_name, l_tp_ctg_number,
1711 -- l_tp_territory, l_tp_phonenumber, l_tp_address, l_tp_credit_level in TXT file;
1712 /*FND_FILE.put_line(FND_FILE.output, l_tp_number ||'| '|| l_tp_name ||'| '|| l_tp_ctg_number
1713 ||'| '|| l_tp_territory ||'| '|| l_tp_phonenumber ||'| '|| l_tp_address
1714 ||'| '|| l_tp_credit_level);*/
1715 /*FND_FILE.put_line(FND_FILE.output,
1716 RPAD(l_tp_number,10, ' ')
1717 ||'| '|| RPAD(nvl(l_tp_name, ' '),40,' ')
1718 ||'| '|| RPAD(nvl(l_tp_ctg_number, ' '),20,' ')
1719 ||'| '|| RPAD(nvl(l_tp_territory, ' '),20,' ')
1720 ||'| '|| RPAD(nvl(l_tp_phonenumber, ' '),20,' ')
1721 ||'| '|| RPAD(nvl(l_tp_address, ' '),40,' ')
1722 ||'| '|| l_tp_credit_level);*/
1723 FND_FILE.put_line(FND_FILE.output,
1724 '"' ||l_tp_number || '"'
1725 ||l_seperator|| '"' ||l_tp_name || '"'
1726 ||l_seperator|| '"' ||l_tp_ctg_number || '"'
1727 ||l_seperator|| '"' ||l_tp_territory || '"'
1728 ||l_seperator|| '"' ||l_tp_phonenumber || '"'
1729 ||l_seperator|| '"' ||l_tp_address || '"'
1730 ||l_seperator|| '"' ||l_tp_credit_level || '"'
1731 );
1732 END LOOP;
1733 CLOSE c_tp_cust;
1734
1735 IF l_row_count = 0 --No data found
1736 THEN
1737 raise JA_CN_NO_DATA_FOUND;
1738 END IF;
1739
1740 retcode := 0;
1741 errbuf := '';
1742 EXCEPTION
1743 WHEN JA_CN_NO_DATA_FOUND THEN
1744 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
1745 IF (l_proc_level >= l_dbg_level)
1746 THEN
1747 FND_LOG.String( l_proc_level
1748 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
1749 ,l_msg_no_data_found);
1750 END IF;
1751 retcode := 1;
1752 errbuf := l_msg_no_data_found;
1753 WHEN OTHERS THEN
1754 IF (l_proc_level >= l_dbg_level)
1755 THEN
1756 FND_LOG.String( l_proc_level
1757 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1758 ,SQLCODE||':'||SQLERRM
1759 );
1760 END IF; --(l_proc_level >= l_dbg_level)
1761 RAISE;
1762
1763 END Coa_TP_Export;
1764
1765
1766
1767 --==========================================================================
1768 -- PROCEDURE NAME:
1769 -- Coa_CC_Export Private
1770 --
1771 -- DESCRIPTION:
1772 -- This procedure exporting the Cost Center list as "subsidiary account"
1773 -- into format predefined flat file.
1774 --
1775 -- PARAMETERS:
1776 -- Out: errbuf NOCOPY VARCHAR2
1777 -- Out: retcode NOCOPY VARCHAR2
1778 -- In: P_COA_ID NUMBER Chart of account ID
1779 -- In: P_LEDGER_ID NUMBER ID of Set Of Book
1780 -- In: P_LE_ID NUMBER ID of Legal Entity
1781 --
1782 -- DESIGN REFERENCES:
1783 -- None
1784 --
1785 -- CHANGE HISTORY:
1786 -- 03/03/2006 Andrew Liu Created
1787 -- 04/24/2007 Yucheng Sun Updated
1788 --===========================================================================
1789 PROCEDURE Coa_CC_Export( errbuf OUT NOCOPY VARCHAR2
1790 ,retcode OUT NOCOPY VARCHAR2
1791 ,P_COA_ID IN NUMBER
1792 ,P_LEDGER_ID IN NUMBER
1793 ,P_LE_ID IN NUMBER
1794 ) IS
1795 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1796 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1797 l_proc_name VARCHAR2(100) :='Coa_CC_Export';
1798
1799 l_ledger_id NUMBER := P_LEDGER_ID;
1800 l_le_id NUMBER := P_LE_ID;
1801 l_coa_id NUMBER := P_COA_ID;
1802 l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
1803
1804 l_row_count NUMBER; --count of rows
1805 l_cc_number JA_CN_ACCOUNT_BALANCES.cost_center%TYPE;
1806 l_cc_name FND_FLEX_VALUES_TL.description%TYPE;
1807
1808 --Cursor to get cost_center from table JA_CN_ACCOUNT_BALANCES and description from table
1809 -- FND_FLEX_VALUES_TL, as Department number and name.
1810 --Because the value set of cost center can be changed manually as natural account, it is
1811 -- no sense to store cost center id in table JA_CN_ACCOUNT_BALANCES. Thus the name should
1812 -- be gotten with the full flow.
1813 CURSOR c_cc IS
1814 SELECT *
1815 FROM (
1816 SELECT DISTINCT
1817 FFV.FLEX_VALUE cc_number
1818 ,nvl(FFVT.description, '') cc_name
1819 FROM JA_CN_ACCOUNT_BALANCES BAL
1820 ,FND_ID_FLEX_SEGMENTS FIFS
1821 ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
1822 ,FND_FLEX_VALUE_SETS FFVS
1823 ,FND_FLEX_VALUES_TL FFVT
1824 ,FND_FLEX_VALUES FFV
1825 ,GL_LEDGERS LEDGER
1826 WHERE BAL.Ledger_Id = l_ledger_id --using variable l_sob_id
1827 AND BAL.account_segment IS NOT NULL
1828 and BAL.cost_center IS NOT NULL
1829 --for name. OR: FFVT.flex_value_meaning = BAL.cost_center
1830 AND FFV.FLEX_VALUE = BAL.cost_center
1831 AND LEDGER.Ledger_Id = l_ledger_id --using variable l_ledger_id
1832 AND LEDGER.chart_of_accounts_id = FIFS.id_flex_num
1833 AND FIFS.id_flex_num = FSAV.id_flex_num
1834 AND FIFS.application_id = 101
1835 AND FIFS.application_id = FSAV.application_id
1836 AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
1837 AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'FA_COST_CTR'
1838 AND FSAV.ATTRIBUTE_VALUE = 'Y'
1839 AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
1840 AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
1841 AND FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
1842 AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
1843 ) tmp_cc_tbl
1844 ORDER BY tmp_cc_tbl.cc_number
1845 ;
1846
1847 BEGIN
1848 /*FND_FILE.put_line(FND_FILE.output,
1849 RPAD('Department number',20, ' ')
1850 ||'| '|| RPAD('Department name',40,' '));*/
1851
1852 --Export all cost centers into the format predefined flat file
1853 l_row_count := 0;
1854 OPEN c_cc;
1855 LOOP
1856 FETCH c_cc INTO l_cc_number
1857 ,l_cc_name
1858 ;
1859 EXIT WHEN c_cc%NOTFOUND;
1860 l_row_count := l_row_count+1;
1861 --Output a row of cost center in TXT file with columns number, name
1862 /*FND_FILE.put_line(FND_FILE.output,
1863 RPAD(l_cc_number,20, ' ')
1864 ||'| '|| RPAD(l_cc_name,40,' '));*/
1865 FND_FILE.put_line(FND_FILE.output,
1866 '"' ||l_cc_number || '"'
1867 ||l_seperator|| '"' ||l_cc_name || '"'
1868 );
1869 END LOOP;
1870 CLOSE c_cc;
1871
1872 IF l_row_count = 0 --No data found
1873 THEN
1874 raise JA_CN_NO_DATA_FOUND;
1875 END IF;
1876
1877 retcode := 0;
1878 errbuf := '';
1879 EXCEPTION
1880 WHEN JA_CN_NO_DATA_FOUND THEN
1881 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
1882 IF (l_proc_level >= l_dbg_level)
1883 THEN
1884 FND_LOG.String( l_proc_level
1885 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
1886 ,l_msg_no_data_found);
1887 END IF;
1888 retcode := 1;
1889 errbuf := l_msg_no_data_found;
1890 WHEN OTHERS THEN
1891 IF (l_proc_level >= l_dbg_level)
1892 THEN
1893 FND_LOG.String( l_proc_level
1894 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1895 ,SQLCODE||':'||SQLERRM
1896 );
1897 END IF; --(l_proc_level >= l_dbg_level)
1898 RAISE;
1899
1900 END Coa_CC_Export;
1901
1902
1903 --==========================================================================
1904 -- PROCEDURE NAME:
1905 -- Coa_Person_Export Private
1906 --
1907 -- DESCRIPTION:
1908 -- This procedure exporting the Personnel list as "subsidiary account"
1909 -- into format predefined flat file.
1910 --
1911 -- PARAMETERS:
1912 -- Out: errbuf NOCOPY VARCHAR2
1913 -- Out: retcode NOCOPY VARCHAR2
1914 -- In: P_COA_ID NUMBER Chart of accounts ID
1915 -- In: P_LEDGER_ID NUMBER ID of Set Of Book
1916 -- In: P_LE_ID NUMBER ID of Legal Entity
1917 --
1918 -- DESIGN REFERENCES:
1919 -- None
1920 --
1921 -- CHANGE HISTORY:
1922 -- 03/03/2006 Andrew Liu Created
1923 -- 04/24/2007 Yucheng Sun Updated
1924 --===========================================================================
1925 PROCEDURE Coa_Person_Export( errbuf OUT NOCOPY VARCHAR2
1926 ,retcode OUT NOCOPY VARCHAR2
1927 ,P_COA_ID IN NUMBER
1928 ,P_LEDGER_ID IN NUMBER
1929 ,P_LE_ID IN NUMBER
1930 ) IS
1931 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1932 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1933 l_proc_name VARCHAR2(100) :='Coa_Person_Export';
1934
1935 l_ledger_id NUMBER := P_LEDGER_ID;
1936 l_le_id NUMBER := P_LE_ID;
1937 l_coa_id NUMBER := P_COA_ID;
1938 l_seperator varchar2(1) := FND_GLOBAL.Local_Chr(9); --' ';
1939
1940 l_row_count NUMBER; --count of rows
1941 l_person_number JA_CN_ACCOUNT_BALANCES.personnel_number%TYPE;
1942 l_person_name VARCHAR2(50);
1943
1944 --Cursor to get personnel_number from table JA_CN_ACCOUNT_BALANCES,
1945 -- and personnel name from table PER_ALL_PEOPLE_F
1946 CURSOR c_person IS
1947 SELECT *
1948 FROM (
1949 SELECT DISTINCT
1950 BAL.personnel_number person_number
1951 ,nvl(PER.last_name||PER.first_name, '') person_name
1952 FROM JA_CN_ACCOUNT_BALANCES BAL
1953 ,PER_ALL_PEOPLE_F PER
1954 WHERE BAL.Ledger_Id = l_ledger_id --using variable l_ledger_id
1955 AND BAL.account_segment IS NOT NULL
1956 and BAL.personnel_id IS NOT NULL
1957 AND PER.person_id = BAL.personnel_id
1958 ) tmp_psn_tbl
1959 ORDER BY tmp_psn_tbl.person_number
1960 ;
1961
1962 BEGIN
1963 /*FND_FILE.put_line(FND_FILE.output,
1964 RPAD('Number',10, ' ')
1965 ||'| '|| RPAD('Name',40,' '));*/
1966
1967 --Export all persons into the format predefined flat file
1968 l_row_count := 0;
1969 OPEN c_person;
1970 LOOP
1971 FETCH c_person INTO l_person_number
1972 ,l_person_name
1973 ;
1974 EXIT WHEN c_person%NOTFOUND;
1975 l_row_count := l_row_count+1;
1976 --Output a row of person in TXT file with columns number, name;
1977 /*FND_FILE.put_line(FND_FILE.output,
1978 RPAD(l_person_number,10, ' ')
1979 ||'| '|| l_person_name);*/
1980 FND_FILE.put_line(FND_FILE.output,
1981 '"' ||l_person_number || '"'
1982 ||l_seperator|| '"' ||l_person_name || '"'
1983 );
1984 END LOOP;
1985 CLOSE c_person;
1986
1987 IF l_row_count = 0 --No data found
1988 THEN
1989 raise JA_CN_NO_DATA_FOUND;
1990 END IF;
1991
1992 retcode := 0;
1993 errbuf := '';
1994 EXCEPTION
1995 WHEN JA_CN_NO_DATA_FOUND THEN
1996 --FND_FILE.put_line(FND_FILE.output, l_msg_no_data_found);
1997 IF (l_proc_level >= l_dbg_level)
1998 THEN
1999 FND_LOG.String( l_proc_level
2000 ,l_module_prefix||'.'||l_proc_name||'.JA_CN_NO_DATA_FOUND '
2001 ,l_msg_no_data_found);
2002 END IF;
2003 retcode := 1;
2004 errbuf := l_msg_no_data_found;
2005 WHEN OTHERS THEN
2006 IF (l_proc_level >= l_dbg_level)
2007 THEN
2008 FND_LOG.String( l_proc_level
2009 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
2010 ,SQLCODE||':'||SQLERRM
2011 );
2012 END IF; --(l_proc_level >= l_dbg_level)
2013 RAISE;
2014
2015 END Coa_Person_Export;
2016
2017 --==========================================================================
2018 -- PROCEDURE NAME:
2019 -- Coa_Export Public
2020 --
2021 -- DESCRIPTION:
2022 -- This procedure calls COA Export programs according to
2023 -- the specified account type.
2024 --
2025 -- PARAMETERS:
2026 -- Out: errbuf NOCOPY VARCHAR2
2027 -- Out: retcode NOCOPY VARCHAR2
2028 -- In: P_COA_ID NUMBER chart of accounts ID
2029 -- In: P_LEDGER_ID NUMBER ID of LEDGER
2030 -- In: P_LE_ID NUMBER ID of Legal Entity
2031 -- In: P_ACCOUNT_TYPE VARCHAR2 Type of the account
2032 -- In: P_XML_TEMPLATE_LANGUAGE VARCHAR2 template language of NA exception report
2033 -- In: P_XML_TEMPLATE_TERRITORY VARCHAR2 template territory of NA exception report
2034 -- In: P_XML_OUTPUT_FORMAT VARCHAR2 output format of NA exception report
2035 --
2036 -- DESIGN REFERENCES:
2037 -- None
2038 --
2039 -- CHANGE HISTORY:
2040 -- 03/03/2006 Andrew Liu Created
2041 -- 04/24/2007 Yucheng Sun Updated
2042 --===========================================================================
2043 PROCEDURE Coa_Export( errbuf OUT NOCOPY VARCHAR2
2044 ,retcode OUT NOCOPY VARCHAR2
2045 ,P_COA_ID IN NUMBER
2046 ,P_LEDGER_ID IN NUMBER
2047 ,P_LE_ID IN NUMBER
2048 ,P_ACCOUNT_TYPE IN VARCHAR2
2049 ,P_XML_TEMPLATE_LANGUAGE IN VARCHAR2
2050 ,P_XML_TEMPLATE_TERRITORY IN VARCHAR2
2051 ,P_XML_OUTPUT_FORMAT IN VARCHAR2
2052 ) IS
2053
2054 l_account_type varchar2(30):=P_ACCOUNT_TYPE;
2055
2056 BEGIN
2057 /*
2058 Coa_Person_export( errbuf => errbuf
2059 ,retcode => retcode
2060 ,P_COA_ID => P_COA_ID
2061 ,P_LEDGER_ID => P_LEDGER_ID
2062 ,P_LE_ID => P_LE_ID
2063 );
2064 */
2065 IF P_ACCOUNT_TYPE = 'NA'
2066 THEN
2067 Coa_NA_Export( errbuf => errbuf
2068 ,retcode => retcode
2069 ,P_COA_ID => P_COA_ID
2070 ,P_LEDGER_ID => P_LEDGER_ID
2071 ,P_LE_ID => P_LE_ID
2072 ,P_XML_TEMPLATE_LANGUAGE => P_XML_TEMPLATE_LANGUAGE
2073 ,P_XML_TEMPLATE_TERRITORY => P_XML_TEMPLATE_TERRITORY
2074 ,P_XML_OUTPUT_FORMAT => P_XML_OUTPUT_FORMAT
2075 );
2076
2077 ELSIF P_ACCOUNT_TYPE = 'PJ'
2078 THEN
2079 Coa_PJ_export( errbuf => errbuf
2080 ,retcode => retcode
2081 ,P_COA_ID => P_COA_ID
2082 ,P_LEDGER_ID => P_LEDGER_ID
2083 ,P_LE_ID => P_LE_ID
2084 );
2085
2086 ELSIF P_ACCOUNT_TYPE = 'TP'
2087 THEN
2088 Coa_TP_export( errbuf => errbuf
2089 ,retcode => retcode
2090 ,P_COA_ID => P_COA_ID
2091 ,P_LEDGER_ID => P_LEDGER_ID
2092 ,P_LE_ID => P_LE_ID
2093 );
2094
2095 ELSIF P_ACCOUNT_TYPE = 'CC'
2096 THEN
2097 Coa_CC_export( errbuf => errbuf
2098 ,retcode => retcode
2099 ,P_COA_ID => P_COA_ID
2100 ,P_LEDGER_ID => P_LEDGER_ID
2101 ,P_LE_ID => P_LE_ID
2102 );
2103
2104 ELSIF P_ACCOUNT_TYPE = 'PERSON'
2105 THEN
2106 Coa_Person_export( errbuf => errbuf
2107 ,retcode => retcode
2108 ,P_COA_ID => P_COA_ID
2109 ,P_LEDGER_ID => P_LEDGER_ID
2110 ,P_LE_ID => P_LE_ID
2111 );
2112 END IF;
2113 END Coa_Export;
2114
2115 BEGIN
2116 -- Initialization
2117 FND_MESSAGE.Set_Name( APPLICATION => 'JA'
2118 ,NAME => 'JA_CN_NO_DATA_FOUND'
2119 );
2120 l_msg_no_data_found := FND_MESSAGE.Get;
2121 END JA_CN_COA_EXP_PKG;
2122
2123
2124