1 package body JA_CN_ITEMIZATION_INTERFACE AS
2 --$Header: JACNITIB.pls 120.2 2008/02/20 03:37:11 shyan noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNITIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used to import the legacy data user input in |
13 --| interface table. It will validate the journal lines user input |
14 --| and make the data enable to import to table ja_cn_journal_lines. |
15 --| After import these data, call CNAO post program. |
16 --| |
17 --| |
18 --| PROCEDURE LIST |
19 --| |
20 --| Import_Itemization_Data |
21 --| Set_flag_P |
22 --| Legal_consistent_Validation |
23 --| Company_Segment_Validation |
24 --| Balance_Validation |
25 --| Code_Combination_Validation |
26 --| Validation |
27 --| Generate_Journal_Num |
28 --| Generate_Code_Combination_View |
29 --| |
30 --| HISTORY |
31 --| 07/09/2007 yanbo liu Created |
32 --| 28/12/2007 xiao lv updated |
33 --| |
34 --+======================================================================*/
35 --==========================================================================
36 -- FUNCTION NAME:
37 -- Legal_consistent_Validation Public
38 --
39 -- DESCRIPTION:
40 -- check legal entity id
41 -- Legal entity id of journal lines must be consistent with legal entity id
42 -- defined in JA: CN Legal Entity. or else,set status as 'EL01'. The profile
43 -- legal entity id is the same as the paramter legal entity id.
44 --
45 -- PARAMETERS:
46 -- P_LEGAL_ENTITY_ID legal entity id
47 --
48 -- DESIGN REFERENCES:
49 -- None
50 --
51 -- CHANGE HISTORY:
52 -- 07/09/2007 yanbo liu created
53 -- 28/12/2007 xiao lv updated
54 --===========================================================================
55
56
57 PROCEDURE Legal_consistent_Validation( P_LEGAL_ENTITY_ID IN NUMBER) IS
58
59 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
60 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
61 l_proc_name VARCHAR2(100) :='Legal_consistent_Validation';
62
63 -- l_profile_legal NUMBER(15);
64
65 BEGIN
66
67 IF (l_proc_level >= l_dbg_level)
68 THEN
69 FND_LOG.String( l_proc_level
70 ,l_module_prefix||'.'||l_proc_name||'.begin'
71 ,'Enter procedure'
72 );
73
74 FND_LOG.String( l_proc_level
75 ,l_module_prefix||'.'||l_proc_name||'.parameters'
76 ,'Legal Entity ID '||P_LEGAL_ENTITY_ID
77 );
78
79
80 END IF; --(l_proc_level >= l_dbg_level)
81
82 /*
83 l_profile_legal:=Fnd_Profile.VALUE(NAME => 'JA_CN_LEGAL_ENTITY');
84
85 IF l_profile_legal IS NULL
86 THEN
87 --Raise error message for caller
88
89 Fnd_Message.Set_Name(Application => 'JA',
90 NAME => 'JA_CN_NO_LEGAL_ENTITY');
91 l_Error_Msg := Fnd_Message.Get;
92
93 --Output error message
94 Fnd_File.Put_Line(Fnd_File.Output, l_Error_Msg);
95 return;
96 END IF; -- FND_PROFILE.Value(NAME => 'JA_CN_LEGAL_ENTITY')IS NULL
97 */
98 --if the legal entity id of journal lines is not consistent with the legal entity id defined
99 --in JA: CN Legal Entity,set status as 'EL01'(this value is same as paramter legal_entity_id).
100 update ja_cn_item_interface
101 set status='EL01'
102 where legal_entity_id<>P_LEGAL_ENTITY_ID
103 and status='P';
104 -- commit;
105
106 EXCEPTION
107
108 WHEN OTHERS THEN
109 IF (l_proc_level >= l_dbg_level)
110 THEN
111 FND_LOG.String( l_proc_level
112 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
113 ,SQLCODE||':'||SQLERRM);
114 END IF; --(l_proc_level >= l_dbg_level)
115
116
117 END Legal_consistent_Validation;
118
119 --==========================================================================
120 -- FUNCTION NAME:
121 -- Legal_consistent_Validation Public
122 --
123 -- DESCRIPTION:
124 -- Do the check for user input. This program will check:
125 -- JE_CATERGORY
126 -- CURRENCY_CODE
127 -- THIRD_PARTY_NUMBER
128 -- PERSONNEL_NUMBER
129 -- PROJECT_NUMBER
130 -- PROJECT_SOURCE
131 -- THIRD_PARTY_TYPE
132 -- JOURNAL_CREATOR
133 -- JOURNAL_APPROVER
134 -- JOURNAL_POSTER
135 --
136 -- PARAMETERS:
137 -- P_LEGAL_ENTITY_ID legal entity id
138 --
139 -- DESIGN REFERENCES:
140 -- None
141 --
142 -- CHANGE HISTORY:
143 -- 07/09/2007 yanbo liu created
144 --===========================================================================
145
146
147 PROCEDURE Base_Validation IS
148
149 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
150 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
151 l_proc_name VARCHAR2(100) :='Legal_consistent_Validation';
152
153
154 l_JE_CATEGORY VARCHAR2(25);
155 l_CURRENCY_CODE VARCHAR2(15);
156 l_THIRD_PARTY_NUMBER VARCHAR2(300);
157 l_PERSONNEL_ID NUMBER;
158 l_PROJECT_NUMBER VARCHAR2(300);
159 l_PROJECT_SOURCE VARCHAR2(3);
160 l_THIRD_PARTY_TYPE VARCHAR2(1);
161 l_JOURNAL_CREATOR number;
162 l_JOURNAL_APPROVER number;
163 l_JOURNAL_POSTER number;
164 l_effective_date date;
165
166 l_count number;
167 l_error_flag varchar(1);
168 l_project_flag varchar(15);
169 l_history_coa varchar(25);
170
171 l_creator VARCHAR2(240);
172 l_approver VARCHAR2(240);
173 l_poster VARCHAR2(240);
174
175 cursor c_journals is
176 select
177 JE_CATEGORY ,
178 CURRENCY_CODE,
179 THIRD_PARTY_NUMBER,
180 PERSONNEL_ID,
181 PROJECT_NUMBER,
182 PROJECT_SOURCE,
183 THIRD_PARTY_TYPE,
184 JOURNAL_CREATOR_ID,
185 JOURNAL_APPROVER_ID,
186 JOURNAL_POSTER_ID,
187 DEFAULT_EFFECTIVE_DATE
188 from ja_cn_item_interface
189 where status = 'P'
190 for update;
191
192 BEGIN
193
194 IF (l_proc_level >= l_dbg_level)
195 THEN
196 FND_LOG.String( l_proc_level
197 ,l_module_prefix||'.'||l_proc_name||'.begin'
198 ,'Enter procedure'
199 );
200
201
202 END IF; --(l_proc_level >= l_dbg_level)
203
204 --fetch the journal line in interface table whose status is 'P';
205
206 open c_journals;
207 loop
208 fetch c_journals into
209 l_JE_CATEGORY,
210 l_CURRENCY_CODE,
211 l_THIRD_PARTY_NUMBER,
212 l_PERSONNEL_ID,
213 l_PROJECT_NUMBER,
214 l_PROJECT_SOURCE,
215 l_THIRD_PARTY_TYPE,
216 l_JOURNAL_CREATOR,
217 l_JOURNAL_APPROVER,
218 l_JOURNAL_POSTER,
219 l_effective_date;
220 exit when c_journals%notfound;
221
222 l_error_flag:='N';
223
224 --validate je_category.
225 select count(*)
226 into l_count
227 from gl_je_categories_tl
228 where user_je_category_name = l_JE_CATEGORY;
229 -- and LANGUAGE = userenv('LANG')
230 if l_count=0 then
231 update ja_cn_item_interface
232 set status='ECG1'
233 where current of c_journals;
234 l_error_flag :='Y';
235 end if;
236 --validate currency code.
237 if l_error_flag<>'Y' then
238 select count(*)
239 into l_count
240 from fnd_currencies
241 where currency_code = l_CURRENCY_CODE;
242 if l_count=0 then
243 update ja_cn_item_interface
244 set status='ECC1'
245 where current of c_journals;
246 l_error_flag :='Y';
247 end if;
248 end if;
249 --validate THIRD_PARTY_TYPE.
250 if l_error_flag<>'Y' and l_THIRD_PARTY_TYPE is not null then
251 select count(*)
252 into l_count
253 from FND_LOOKUP_VALUES
254 where LANGUAGE = userenv('LANG')
255 and lookup_code = l_THIRD_PARTY_TYPE
256 and lookup_type = 'JA_CN_THIRDPARTY_TYPE' ;
257 if l_count=0 then
258 update ja_cn_item_interface
259 set status='ETP1'
260 where current of c_journals;
261 l_error_flag :='Y';
262 end if;
263 end if;--l_error_flag<>'Y', for third party type
264 --if THIRD_PARTY_TYPE is null, but THIRD_PARTY_NUMBER is not null
265 --set error status as 'ETP2', third party number can't be validated.
266 if l_error_flag<>'Y' then
267 if l_THIRD_PARTY_TYPE is null and l_THIRD_PARTY_NUMBER is not null then
268 update ja_cn_item_interface
269 set status='ETP2'
270 where current of c_journals;
271 l_error_flag :='Y';
272 end if;
273 if l_THIRD_PARTY_TYPE = 'N' and l_THIRD_PARTY_NUMBER is not null then
274 update ja_cn_item_interface
275 set status='ETP5'
276 where current of c_journals;
277 l_error_flag :='Y';
278 end if;
279 end if;
280
281 --validate THIRD_PARTY_NUMBER.
282 if l_error_flag<>'Y' then
283 if l_THIRD_PARTY_TYPE = 'C' and l_THIRD_PARTY_NUMBER is not null then
284 select count(*)
285 into l_count
286 from Hz_Parties
287 where Party_Number =l_THIRD_PARTY_NUMBER;
288 if l_count=0 then
289 update ja_cn_item_interface
290 set status='ETP3'
291 where current of c_journals;
292 l_error_flag :='Y';
293 end if;
294 elsif l_THIRD_PARTY_TYPE = 'S' and l_THIRD_PARTY_NUMBER is not null then
295 select count(*)
296 into l_count
297 from ap_suppliers
298 where Segment1 =l_THIRD_PARTY_NUMBER;
299 if l_count=0 then
300 update ja_cn_item_interface
301 set status='ETP4'
302 where current of c_journals;
303 l_error_flag :='Y';
304 end if;
305 end if;--l_THIRD_PARTY_TYPE = 'C' and l_THIRD_PARTY_NUMBER is not null then
306 end if;--if l_error_flag<>'Y' , for third party number check
307 --check personal number
308 -------------------------------------------------------
309 if l_error_flag<>'Y' and l_PERSONNEL_ID is not null then
310 select count(*)
311 into l_count
312 from PER_ALL_PEOPLE_F
313 where PERSON_ID = l_PERSONNEL_ID
314 and effective_start_date<=l_effective_date
315 and effective_end_date>=l_effective_date;
316 if l_count=0 then
317 update ja_cn_item_interface
318 set status='EPR1'
319 where current of c_journals;
320 l_error_flag :='Y';
321 end if;
322 end if;
323 --check project source
324 if l_error_flag<>'Y' then
325 select count(*)
326 into l_count
327 from FND_LOOKUP_VALUES
328 where lookup_code = l_PROJECT_SOURCE
329 and lookup_type like 'JA_CN_PROJECT_SOURCE'
330 and LANGUAGE = userenv('LANG') ;
331 if l_count=0 then
332 update ja_cn_item_interface
333 set status='EPS1'
334 where current of c_journals;
335 l_error_flag :='Y';
336 end if;
337 end if; --l_error_flag<>'Y' ,for project source check
338 --check consistency
339 --when project source in interface table is PA and it's not consistent
340 --with the project flag of subsidiary account form.
341 if l_error_flag<>'Y' then
342 select nvl(project_source_flag,'-1'),nvl(history_coa_segment,'-1')
343 into l_project_flag,l_history_coa
344 from ja_cn_sub_acc_sources_all
345 where chart_of_accounts_id=l_coa;
346
347 if l_project_flag='-1' then
348 update ja_cn_item_interface
349 set status='EPS4'
350 where current of c_journals;
351 l_error_flag :='Y';
352 end if;
353
354 if l_error_flag <>'Y'and l_PROJECT_SOURCE='PA' and l_project_flag<>l_PROJECT_SOURCE then
355 update ja_cn_item_interface
356 set status='EPS2'
357 where current of c_journals;
358 l_error_flag :='Y';
359 end if;
360 --when project source in interface table is 'COA' and it's not consistent
361 --with the project flag of subsidiary account form. And also the history
362 --COA segment is null.
363 --l_project_flag is 'N',l_history_coa is null
364 --l_project_flag is 'N',l_history_coa is not null. this case can't happan. if happen, validated.
365 --l_project_flag is 'PA',l_history_coa is null
366 --l_project_flag is 'PA',l_history_coa is not null. this case can happen.
367 if l_error_flag <>'Y'and l_PROJECT_SOURCE='COA' and l_project_flag<>l_PROJECT_SOURCE and l_history_coa='-1'then
368 update ja_cn_item_interface
369 set status='EPS3'
370 where current of c_journals;
371 l_error_flag :='Y';
372 end if;
373 end if;-- l_error_flag<>'Y', for project number check
374 ----------------------------------------------------------
375 --check project number when project source is 'PA'
376 if l_error_flag<>'Y' then
377 if l_PROJECT_SOURCE='N' and l_PROJECT_NUMBER is not null then
378 update ja_cn_item_interface
379 set status='EPN3'
380 where current of c_journals;
381 l_error_flag :='Y';
382
383 end if;
384 if l_PROJECT_SOURCE='PA' and l_PROJECT_NUMBER is not null then
385 select count(*)
386 into l_count
387 from PA_PROJECTS_ALL
388 where SEGMENT1=l_PROJECT_NUMBER;
389 if l_count=0 then
390 update ja_cn_item_interface
391 set status='EPN1'
392 where current of c_journals;
393 l_error_flag :='Y';
394 end if;
395 end if; --if l_PROJECT_SOURCE='PA' and l_PROJECT_NUMBER is not null then
396 --check project number when project source is 'COA'
397 if l_PROJECT_SOURCE='COA' and l_PROJECT_NUMBER is not null then
398 select count(*)
399 into l_count
400 from FND_FLEX_VALUES ffv,
401 FND_ID_FLEX_SEGMENTS fifs
402 where ffv.flex_value_set_id=fifs.flex_value_set_id
403 and fifs.id_flex_code='GL#'
404 and fifs.id_flex_num=l_coa
405 and (fifs.application_column_name =(select coa_segment
406 from ja_cn_sub_acc_sources_all
407 where chart_of_accounts_id=l_coa
408 and coa_segment is not null)
409 or fifs.application_column_name =(select history_coa_segment
410 from ja_cn_sub_acc_sources_all
411 where chart_of_accounts_id=l_coa
412 and history_coa_segment is not null) )
413 AND flex_value = l_PROJECT_NUMBER ;
414 if l_count=0 then
415 update ja_cn_item_interface
416 set status='EPN2'
417 where current of c_journals;
418 l_error_flag :='Y';
419 end if; --l_count=0
420 end if; --l_PROJECT_SOURCE='COA' and l_PROJECT_NUMBER is not null
421 end if;--l_error_flag<>'Y', for project number;
422 -------------------------------------------
423 --check journal creator, if not null, change id to name
424 if l_error_flag<>'Y' and l_JOURNAL_CREATOR is not null then
425 begin
426 select Last_Name || First_Name Full_Name
427 into l_creator
428 from Per_All_People_f
429 where person_id = l_JOURNAL_CREATOR
430 AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
431 AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
432 /*
433 update ja_cn_item_interface
434 set journal_creator_id = l_creator
435 where journal_creator_id = l_JOURNAL_CREATOR; */
436 exception
437 when no_data_found then
438 update ja_cn_item_interface
439 set status='EJC1'
440 where current of c_journals;
441 l_error_flag :='Y';
442 end;
443 end if;
444 --check journal approver,if not null, change id to name
445 if l_error_flag<>'Y' and l_JOURNAL_APPROVER is not null then
446 begin
447 select Last_Name || First_Name Full_Name
448 into l_approver
449 from Per_All_People_f
450 where person_id =l_JOURNAL_APPROVER
451 AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
452 AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
453 /*
454 update ja_cn_item_interface
455 set journal_creator_id = l_approver
456 where journal_creator_id = l_JOURNAL_APPROVER; */
457 exception
458 when no_data_found then
459 update ja_cn_item_interface
460 set status='EJA1'
461 where current of c_journals;
462 l_error_flag :='Y';
463 end;
464 end if;
465 --check journal poster,if not null, change id to name
466 if l_error_flag<>'Y' and l_JOURNAL_POSTER is not null then
467 begin
468 select Last_Name || First_Name Full_Name
469 into l_poster
470 from Per_All_People_f
471 where person_id = l_JOURNAL_POSTER
472 AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
473 AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
474 /*
475 update ja_cn_item_interface
476 set journal_creator_id = l_poster
477 where journal_creator_id = l_JOURNAL_POSTER; */
478 exception
479 when no_data_found then
480 update ja_cn_item_interface
481 set status='EJP1'
482 where current of c_journals;
483 l_error_flag :='Y';
484 end;
485
486 end if;
487
488 end loop;
489 close c_journals;
490
491 -- select * from gl_je_categories_tl where user_je_category_name =
492
493
494 EXCEPTION
495
496 WHEN OTHERS THEN
497 IF (l_proc_level >= l_dbg_level)
498 THEN
499 FND_LOG.String( l_proc_level
500 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
501 ,SQLCODE||':'||SQLERRM);
502 END IF; --(l_proc_level >= l_dbg_level)
503
504
505 END Base_Validation;
506 --==========================================================================
507 -- FUNCTION NAME:
508 -- Legal_consistent_Validation Public
509 --
510 -- DESCRIPTION:
511 -- check legal entity id
512 -- If comany segment is not consistent with the legal and ledger
513 -- set status 'ECS1'
514 --
515 -- PARAMETERS:
516 -- P_LEGAL_ENTITY_ID legal entity id
517 -- P_LEDGER_ID ledger id
518 --
519 -- DESIGN REFERENCES:
520 -- None
521 --
522 -- CHANGE HISTORY:
523 -- 09/08/2007 yanbo liu created
524 --===========================================================================
525
526 PROCEDURE Company_Segment_Validation( P_LEGAL_ENTITY_ID IN NUMBER,
527 P_LEDGER_ID IN NUMBER
528 ) IS
529
530 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
531 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
532 l_proc_name VARCHAR2(100) :='Company_Segment_Validation';
533
534 l_sql varchar2(1000);
535
536 BEGIN
537
538 IF (l_proc_level >= l_dbg_level)
539 THEN
540 FND_LOG.String( l_proc_level
541 ,l_module_prefix||'.'||l_proc_name||'.begin'
542 ,'Enter procedure'
543 );
544
545 FND_LOG.String( l_proc_level
546 ,l_module_prefix||'.'||l_proc_name||'.parameters'
547 ,'Legal Entity ID '||P_LEGAL_ENTITY_ID
548 );
549 FND_LOG.String( l_proc_level
550 ,l_module_prefix||'.'||l_proc_name||'.parameters'
551 ,'Ledger ID '||P_LEDGER_ID
552 );
553
554
555 END IF; --(l_proc_level >= l_dbg_level)
556
557 --do prepare
558 --populate BSV for current legal entity and ledger
559 /*
560 l_Populate_Bsv_Flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(P_Ledger_Id,
561 P_Legal_Entity_Id);
562 IF l_Populate_Bsv_Flag = 'F' THEN
563 IF (l_Proc_Level >= l_Dbg_Level) THEN
564 Fnd_Log.STRING(l_Proc_Level,
565 l_Module_Prefix || '.' || l_Proc_Name,
566 'fail to populate BSV');
567 END IF; --(l_proc_level >= l_dbg_level)
568 END IF;
569 */
570 l_sql := 'UPDATE JA_CN_ITEM_INTERFACE
571 SET status=''ECS1''
572 WHERE status=''P''
573 AND ' || l_Company_Column_Name ||
574 ' NOT IN
575 (SELECT bsv.bal_seg_value
576 FROM ja_cn_ledger_le_bsv_gt bsv
577 WHERE Legal_Entity_Id = '|| P_LEGAL_ENTITY_ID ||
578 ' AND ledger_id = '|| P_LEDGER_ID||') ';
579
580 execute immediate l_sql;
581
582 EXCEPTION
583
584 WHEN OTHERS THEN
585 IF (l_proc_level >= l_dbg_level)
586 THEN
587 FND_LOG.String( l_proc_level
588 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
589 ,SQLCODE||':'||SQLERRM);
590 END IF; --(l_proc_level >= l_dbg_level)
591
592
593 END Company_Segment_Validation;
594
595 --==========================================================================
596 -- FUNCTION NAME:
597 -- Balance_Validation Public
598 --
599 -- DESCRIPTION:
600 -- If the DR sum and CR sum not balance in the same journal,same legal entity id
601 -- and the same company segment, set status 'EB01'.
602 --
603 -- PARAMETERS:
604 --
605 --
606 -- DESIGN REFERENCES:
607 -- None
608 --
609 -- CHANGE HISTORY:
610 -- 07/09/2007 yanbo liu created
611 --===========================================================================
612
613
614 PROCEDURE Balance_Validation(p_legal_entity_id IN NUMBER) IS
615
616 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
617 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
618 l_proc_name VARCHAR2(100) :='Balance_Validation';
619
620 l_dr number(15);
621 l_cr number(15);
622 l_account_dr number(15);
623 l_account_cr number(15);
624 l_journal_group number;
625 l_legal_entity_id number;
626 l_Company_segment varchar2(25);
627 l_sql varchar2(1000);
628 l_sql1 varchar2(1000);
629
630
631 TYPE BalanceCurTyp IS REF CURSOR;
632 c_bl_journal BalanceCurTyp;
633
634 BEGIN
635
636 IF (l_proc_level >= l_dbg_level)
637 THEN
638 FND_LOG.String( l_proc_level
639 ,l_module_prefix||'.'||l_proc_name||'.begin'
640 ,'Enter procedure'
641 );
642
643
644 END IF; --(l_proc_level >= l_dbg_level)
645
646 l_sql:='select journal_group,
647 legal_entity_id, '||
648 l_Company_Column_Name||
649 ',sum(ENTERED_DR),
650 sum(ENTERED_CR),
651 sum(ACCOUNTED_DR),
652 SUM(ACCOUNTED_cR)
653 from ja_cn_item_interface
654 where status=''P''
655 and '|| l_Company_Column_Name||' is not null
656 and legal_entity_id= '||p_legal_entity_id||
657 ' group by journal_group,
658 legal_entity_id,'
659 ||l_Company_Column_Name;
660
661 --update the status 'EB01' if not balance.
662 OPEN c_bl_journal FOR l_sql;
663 loop
664 fetch c_bl_journal into l_journal_group,
665 l_legal_entity_id,
666 l_Company_segment,
667 l_dr,
668 l_cr,
669 l_account_dr,
670 l_account_cr;
671 EXIT WHEN c_bl_journal%NOTFOUND;
672 if l_dr<>l_cr then
673 l_sql1:='update ja_cn_item_interface
674 set status=''EB01''
675 where status = ''P''
676 and journal_group ='|| l_journal_group||
677 ' and legal_entity_id ='|| l_legal_entity_id ||
678 ' and '|| l_Company_Column_Name||'='||l_Company_segment;
679 execute immediate l_sql1;
680 -- commit;
681 end if;
682 if l_account_dr<>l_account_cr then
683 l_sql1:='update ja_cn_item_interface
684 set status=''EB02''
685 where status = ''P''
686 and journal_group ='|| l_journal_group||
687 ' and legal_entity_id ='|| l_legal_entity_id ||
688 ' and '|| l_Company_Column_Name||'='||l_Company_segment;
689 execute immediate l_sql1;
690 -- commit;
691 end if;
692
693 end loop;
694 close c_bl_journal;
695
696
697 EXCEPTION
698
699 WHEN OTHERS THEN
700 IF (l_proc_level >= l_dbg_level)
701 THEN
702 FND_LOG.String( l_proc_level
703 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
704 ,SQLCODE||':'||SQLERRM);
705 END IF; --(l_proc_level >= l_dbg_level)
706
707
708 END Balance_Validation;
709
710 --==========================================================================
711 -- FUNCTION NAME:
712 -- Code_Combination_Validation Public
713 --
714 -- DESCRIPTION:
715 -- code combination id can't be null in table ja_cn_journal_lines.
716 -- user can input it in two ways, directly the code combination id
717 -- and the segment combination. So the correctness and the consistency
718 -- of the two ways should be validated.
719 --
720 -- PARAMETERS:
721 -- P_LEDGER_ID ledger id
722 --
723 -- DESIGN REFERENCES:
724 -- None
725 --
726 -- CHANGE HISTORY:
727 -- 07/09/2007 yanbo liu created
728 --===========================================================================
729
730 PROCEDURE Code_Combination_Validation( P_LEDGER_ID IN NUMBER) IS
731
732 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
733 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
734 l_proc_name VARCHAR2(100) :='Code_Combination_Validation';
735
736 v_code NUMBER;
737 v_errm VARCHAR2(64);
738
739 TYPE SEGMENT_TBL IS TABLE OF gl_code_combinations.segment1%type;
740 l_segments SEGMENT_TBL;
741 l_segment_index number;
742 l_segment_name gl_code_combinations.segment1%type;
743 l_count number(2);
744 i number(2);
745 l_sql VARCHAR2(1000);
746 TYPE SEGMENT_CONTEXT_TBL IS TABLE OF VARCHAR2(25);
747 l_segment_context SEGMENT_CONTEXT_TBL;
748 l_status varchar(25);
749 l_sql_segment1 varchar2(1000);
750 l_sql_segment2 varchar2(1000);
751 l_ccid number;
752 l_ccid1 number;
753 l_status varchar2(10);
754 l_ccid_count number;
755 l_Company_value varchar2(25);
756 l_Account_value varchar2(25);
757 l_Cost_CRT_value varchar2(25);
758 l_Rowid rowid;
759
760 TYPE InstSegCurTyp IS REF CURSOR;
761 c_Inst_segments InstSegCurTyp;
762
763 cursor c_ccid_check is
764 select code_combination_id
765 from ja_cn_item_interface
766 where (status = 'P1'
767 or status = 'P2')
768 and code_combination_id is not null
769 for update ;
770
771 cursor c_set_segment is
772 select code_combination_id,
773 rowid
774 from ja_cn_item_interface
775 where status = 'P1'
776 and code_combination_id is not null
777 for update;
778
779 cursor c_segments is
780 select segment1,
781 segment2,
782 segment3,
783 segment4,
784 segment5,
785 segment6,
786 segment7,
787 segment8,
788 segment9,
789 segment10,
790 segment11,
791 segment12,
792 segment13,
793 segment14,
794 segment15,
795 segment16,
796 segment17,
797 segment18,
798 segment19,
799 segment20,
800 segment21,
801 segment22,
802 segment23,
803 segment24,
804 segment25,
805 segment26,
806 segment27,
807 segment28,
808 segment29,
809 segment30,
810 code_combination_id
811 from ja_cn_item_interface
812 where status='P2'
813 for update;
814
815
816 -- and code_combination_id is null;
817 BEGIN
818
819 IF (l_proc_level >= l_dbg_level)
820 THEN
821 FND_LOG.String( l_proc_level
822 ,l_module_prefix||'.'||l_proc_name||'.begin'
823 ,'Enter procedure'
824 );
825
826 FND_LOG.String( l_proc_level
827 ,l_module_prefix||'.'||l_proc_name||'.parameters'
828 ,'Legal Entity ID '||P_LEDGER_ID
829 );
830
831
832 END IF; --(l_proc_level >= l_dbg_level)
833 --if all the segments are null, set status 'P1'
834 update ja_cn_item_interface
835 set status='P1'
836 where segment1 is null
837 and segment2 is null
838 and segment3 is null
839 and segment4 is null
840 and segment5 is null
841 and segment6 is null
842 and segment7 is null
843 and segment8 is null
844 and segment9 is null
845 and segment10 is null
846 and segment11 is null
847 and segment12 is null
848 and segment13 is null
849 and segment14 is null
850 and segment15 is null
851 and segment16 is null
852 and segment17 is null
853 and segment18 is null
854 and segment19 is null
855 and segment20 is null
856 and segment21 is null
857 and segment22 is null
858 and segment23 is null
859 and segment24 is null
860 and segment25 is null
861 and segment26 is null
862 and segment27 is null
863 and segment28 is null
864 and segment29 is null
865 and segment30 is null
866 and status='P';
867 --others lines whose status is 'P',set status 'P2'
868 update ja_cn_item_interface
869 set status='P2'
870 where status='P';
871
872 update ja_cn_item_interface
873 set status='EC01'
874 where status='P1'
875 and code_combination_id is null;
876
877 --for the journal whose status is 'P1' or 'P2', check the CCID column is right or not.
878 --if CCID in interface table is not defined in gl_code_combiantion table then set stats
879 --'EC02'
880 open c_ccid_check;
881 loop
882 fetch c_ccid_check into l_ccid;
883 exit when c_ccid_check%notfound;
884 select count(*)
885 into l_ccid_count
886 from gl_code_combinations
887 where code_combination_id=l_ccid
888 and chart_of_accounts_id=l_coa;
889 if l_ccid_count = 0 then
890 update ja_cn_item_interface
891 set status = 'EC02'
892 where current of c_ccid_check;
893 -- commit;
894 end if;
895 end loop;
896 close c_ccid_check;
897 --now if the status is 'P1', the ccid is right.
898 --get the segment value according to CCID.
899 open c_set_segment;
900 loop
901 fetch c_set_segment into l_ccid,l_rowid;
902 exit when c_set_segment%notfound;
903 l_sql:='select '|| l_Company_Column_Name ||', '
904 || l_Account_Column_Name ||', '
905 || l_Cost_CRT_Column_Name ||
906 ' from gl_code_combinations
907 where code_combination_id=' || l_ccid ||
908 ' and chart_of_accounts_id='|| l_coa;
909 open c_Inst_segments for l_sql;
910 loop
911 fetch c_Inst_segments into l_Company_value,l_Account_value,l_Cost_CRT_value;
912 exit when c_Inst_segments%notfound;
913 end loop;
914 close c_Inst_segments;
915
916 l_sql:='update ja_cn_item_interface set '
917 || l_Company_Column_Name ||' = ''' || l_Company_value ||''', '
918 || l_Account_Column_Name ||' = ''' || l_Account_value ||''', '
919 || l_Cost_CRT_Column_Name ||' = ''' || l_Cost_CRT_value ||'''
920 where rowid='''||l_rowid||'''';
921 execute immediate l_sql;
922 -- commit;
923 end loop;
924 close c_set_segment;
925
926 --check the segments combination is right or not.
927 --if it doesn't map a ccid in table ja_cn_item_interface
928 --set status 'EC03'
929 l_segment_context:=SEGMENT_CONTEXT_TBL();
930 l_segment_context.extend(30);
931 l_count:=0;
932 i:=1;
933 open c_segments;
934 loop
935 fetch c_segments into
936 l_segment_context(1),
937 l_segment_context(2),
938 l_segment_context(3),
939 l_segment_context(4),
940 l_segment_context(5),
941 l_segment_context(6),
942 l_segment_context(7),
943 l_segment_context(8),
944 l_segment_context(9),
945 l_segment_context(10),
946 l_segment_context(11),
947 l_segment_context(12),
948 l_segment_context(13),
949 l_segment_context(14),
950 l_segment_context(15),
951 l_segment_context(16),
952 l_segment_context(17),
953 l_segment_context(18),
954 l_segment_context(19),
955 l_segment_context(20),
956 l_segment_context(21),
957 l_segment_context(22),
958 l_segment_context(23),
959 l_segment_context(24),
960 l_segment_context(25),
961 l_segment_context(26),
962 l_segment_context(27),
963 l_segment_context(28),
964 l_segment_context(29),
965 l_segment_context(30),
966 l_ccid;
967 exit when c_segments%notfound;
968 begin
969 select code_combination_id
970 into l_ccid1
971 from gl_code_combinations
972 where nvl(segment1,-1)=nvl(l_segment_context(1),-1)
973 and nvl(segment2,-1)=nvl(l_segment_context(2),-1)
974 and nvl(segment3,-1)=nvl(l_segment_context(3),-1)
975 and nvl(segment4,-1)=nvl(l_segment_context(4),-1)
976 and nvl(segment5,-1)=nvl(l_segment_context(5),-1)
977 and nvl(segment6,-1)=nvl(l_segment_context(6),-1)
978 and nvl(segment7,-1)=nvl(l_segment_context(7),-1)
979 and nvl(segment8,-1)=nvl(l_segment_context(8),-1)
980 and nvl(segment9,-1)=nvl(l_segment_context(9),-1)
981 and nvl(segment10,-1)=nvl(l_segment_context(10),-1)
982 and nvl(segment11,-1)=nvl(l_segment_context(11),-1)
983 and nvl(segment12,-1)=nvl(l_segment_context(12),-1)
984 and nvl(segment13,-1)=nvl(l_segment_context(13),-1)
985 and nvl(segment14,-1)=nvl(l_segment_context(14),-1)
986 and nvl(segment15,-1)=nvl(l_segment_context(15),-1)
987 and nvl(segment16,-1)=nvl(l_segment_context(16),-1)
988 and nvl(segment17,-1)=nvl(l_segment_context(17),-1)
989 and nvl(segment18,-1)=nvl(l_segment_context(18),-1)
990 and nvl(segment19,-1)=nvl(l_segment_context(19),-1)
991 and nvl(segment20,-1)=nvl(l_segment_context(20),-1)
992 and nvl(segment21,-1)=nvl(l_segment_context(21),-1)
993 and nvl(segment12,-1)=nvl(l_segment_context(22),-1)
994 and nvl(segment23,-1)=nvl(l_segment_context(23),-1)
995 and nvl(segment24,-1)=nvl(l_segment_context(24),-1)
996 and nvl(segment25,-1)=nvl(l_segment_context(25),-1)
997 and nvl(segment26,-1)=nvl(l_segment_context(26),-1)
998 and nvl(segment27,-1)=nvl(l_segment_context(27),-1)
999 and nvl(segment28,-1)=nvl(l_segment_context(28),-1)
1000 and nvl(segment29,-1)=nvl(l_segment_context(29),-1)
1001 and nvl(segment30,-1)=nvl(l_segment_context(30),-1)
1002 and chart_of_accounts_id=l_coa;
1003 if l_ccid is null then
1004 update ja_cn_item_interface
1005 set Code_Combination_id=l_ccid1
1006 where current of c_segments;
1007 elsif l_ccid is not null then
1008 if l_ccid<>l_ccid1 then
1009 update ja_cn_item_interface
1010 set status='EC04'
1011 where current of c_segments;
1012 end if;
1013 end if;
1014 exception
1015 when no_data_found then
1016 update ja_cn_item_interface
1017 set status='EC03'
1018 where current of c_segments;
1019 end;
1020 end loop;
1021 close c_segments ;
1022
1023 update ja_cn_item_interface
1024 set status='P'
1025 where status in('P1','P2');
1026
1027 --now all the segments combination is right,
1028 --all the ccid is right if not null.
1029 --if ccid is null, then set ccid.
1030
1031 ---------------------------------------------------
1032
1033
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 v_code := SQLCODE;
1037 v_errm := SUBSTR(SQLERRM, 1 , 64);
1038
1039 IF (l_proc_level >= l_dbg_level)
1040 THEN
1041 FND_LOG.String( l_proc_level
1042 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1043 ,SQLCODE||':'||SQLERRM);
1044 END IF; --(l_proc_level >= l_dbg_level)
1045
1046
1047 END Code_Combination_Validation;
1048
1049 --==========================================================================
1050 -- FUNCTION NAME:
1051 -- Validation Public
1052 --
1053 -- DESCRIPTION:
1054 -- This procedure is used to validate the data in interface table, if the
1055 -- data is not reasonable, set the error status. if right, set the status
1056 -- as 'S'. This program will call several sub validation program.
1057 --
1058 -- PARAMETERS:
1059 -- P_LEDGER_ID ledger id
1060 -- P_LEGAL_ENTITY_ID legal entity id
1061 -- P_PERIOD_FROM period from
1062 -- P_PERIOD_TO period to
1063 --
1064 -- DESIGN REFERENCES:
1065 -- None
1066 --
1067 -- CHANGE HISTORY:
1068 -- 07/09/2007 yanbo liu created
1069 --===========================================================================
1070 PROCEDURE Validation( P_LEDGER_ID IN NUMBER,
1071 P_LEGAL_ENTITY_ID IN NUMBER,
1072 P_PERIOD_FROM IN VARCHAR2,
1073 P_PERIOD_TO IN VARCHAR2
1074 ) IS
1075
1076 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1077 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1078 l_proc_name VARCHAR2(100) :='Validation';
1079
1080
1081
1082 BEGIN
1083
1084 IF (l_proc_level >= l_dbg_level)
1085 THEN
1086 FND_LOG.String( l_proc_level
1087 ,l_module_prefix||'.'||l_proc_name||'.begin'
1088 ,'Enter procedure'
1089 );
1090 FND_LOG.String( l_proc_level
1091 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1092 ,'Ledger ID '||P_LEDGER_ID
1093 );
1094 FND_LOG.String( l_proc_level
1095 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1096 ,'Legal Entity ID '||P_LEGAL_ENTITY_ID
1097 );
1098 FND_LOG.String( l_proc_level
1099 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1100 ,'Period From '||P_PERIOD_FROM
1101 );
1102 FND_LOG.String( l_proc_level
1103 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1104 ,'Period To '||P_PERIOD_TO
1105 );
1106
1107 END IF; --(l_proc_level >= l_dbg_level)
1108
1109 --first update set status is null for all journals in interface table.
1110 update ja_cn_item_interface
1111 set status = null;
1112 -- commit;
1113
1114 --filter journals by parameter. set the status of journal which will be validated as p.
1115 update ja_cn_item_interface
1116 set status = 'P'
1117 where --legal_entity_id = P_LEGAL_ENTITY_ID and--this condition will be put the legal_consistent validation
1118 ledger_id = P_LEDGER_ID
1119 and period_name in(
1120 SELECT Gp.Period_Name
1121 FROM Gl_Periods Gp, Gl_Ledgers Led
1122 WHERE Led.Ledger_Id = p_Ledger_Id
1123 AND Led.Period_Set_Name = Gp.Period_Set_Name
1124 AND Led.Accounted_Period_Type = Gp.Period_Type
1125 AND Gp.Start_Date BETWEEN
1126 (SELECT Start_Date
1127 FROM Gl_Periods Gp
1128 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1129 AND Led.Accounted_Period_Type = Gp.Period_Type
1130 AND Gp.Period_Name = P_PERIOD_FROM )
1131 AND (SELECT Start_Date
1132 FROM Gl_Periods Gp
1133 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1134 AND Led.Accounted_Period_Type = Gp.Period_Type
1135 AND Gp.Period_Name = P_PERIOD_TO)
1136 );
1137 -- commit;
1138 -------------------------------------------------------------------
1139 --1, check legal entity id
1140 --Legal entity id of journal lines must be consistent with legal entity id
1141 --defined in JA: CN Legal Entity. or else,set status as 'L'.
1142 ---------------------------------------------------------------
1143
1144 Legal_consistent_Validation(P_LEGAL_ENTITY_ID);
1145 --------------------------------------------------------------------
1146 --do base validation
1147 Base_Validation();
1148
1149 ---------------------------------------------------------------------
1150 --2,check code_combination_id
1151 --The code combination id is option to input for user.
1152 --the segment combination is also option to input for user.
1153 -- if both of them are input, they must be consistent. if not,
1154 -- set status EC04
1155 --if one is input, but it can't be find in table gl_code_combinations
1156 --the status will be EC02 or EC03.
1157 --if only segment combination is input,set value for code combination
1158 --id to interface table. the status is 'p'
1159 --if only code combination id is input, set company segment, account
1160 --segment and cost center segment according to ccid. set status 'P'
1161 --if both of them isn't input, set status EC05.
1162 --before do the check described above, the segment count will be
1163 --checked first, if it is not consistent with segment count of
1164 --current ledger. set status EC01
1165 ----------------------------------------------------------------------
1166 Code_Combination_Validation(P_LEDGER_ID);
1167
1168 --check whether the company segment is consistency paramter legal and ledger
1169 Company_Segment_Validation( P_LEGAL_ENTITY_ID,
1170 P_LEDGER_ID );
1171 --check whether DR amount and CR amount equal or not
1172 --group by journal group, legal_entity_id , company segment.
1173
1174 Balance_Validation(P_LEGAL_ENTITY_ID);
1175
1176 --at last set the journal which is validated to 'S'
1177 update ja_cn_item_interface
1178 set status='S'
1179 where status='P';
1180 -- commit;
1181
1182 EXCEPTION
1183
1184 WHEN OTHERS THEN
1185 IF (l_proc_level >= l_dbg_level)
1186 THEN
1187 FND_LOG.String( l_proc_level
1188 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1189 ,SQLCODE||':'||SQLERRM);
1190 END IF; --(l_proc_level >= l_dbg_level)
1191
1192
1193 END Validation;
1194
1195 --==========================================================================
1196 -- FUNCTION NAME:
1197 -- Set_flag_P Public
1198 --
1199 -- DESCRIPTION:
1200 -- set a flag 'P' for journals in table gl_je_lines to identify them as
1201 -- processed journal according to the paramter input.
1202 --
1203 -- PARAMETERS:
1204 -- P_LEDGER_ID ledger id
1205 -- P_LEGAL_ENTITY_ID legal entity id
1206 -- P_PERIOD_FROM period from
1207 -- P_PERIOD_TO period to
1208 --
1209 -- DESIGN REFERENCES:
1210 -- None
1211 --
1212 -- CHANGE HISTORY:
1213 -- 07/09/2007 yanbo liu created
1214 --===========================================================================
1215 PROCEDURE Set_flag_P( P_LEDGER_ID IN NUMBER,
1216 P_LEGAL_ENTITY_ID IN NUMBER,
1217 P_PERIOD_FROM IN VARCHAR2,
1218 P_PERIOD_TO IN VARCHAR2
1219 ) IS
1220
1221 l_Populate_Journal_Sql VARCHAR2(4000);
1222 l_Start_Period VARCHAR2(15);
1223 l_End_Period VARCHAR2(15);
1224 l_ledger_id number;
1225 l_legal_entity_id number;
1226
1227 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1228 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1229 l_Proc_Name VARCHAR2(100) := 'Set_flag_p';
1230
1231 TYPE JECurTyp IS REF CURSOR;
1232 c_journal JECurTyp;
1233
1234 l_header_id number;
1235 l_line_num number;
1236
1237
1238
1239 BEGIN
1240
1241 IF (l_proc_level >= l_dbg_level)
1242 THEN
1243 FND_LOG.String( l_proc_level
1244 ,l_module_prefix||'.'||l_proc_name||'.begin'
1245 ,'Enter procedure'
1246 );
1247 FND_LOG.String( l_proc_level
1248 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1249 ,'Ledger ID '||P_LEDGER_ID
1250 );
1251 FND_LOG.String( l_proc_level
1252 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1253 ,'Legal Entity ID '||P_LEGAL_ENTITY_ID
1254 );
1255 FND_LOG.String( l_proc_level
1256 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1257 ,'Period From '||P_PERIOD_FROM
1258 );
1259 FND_LOG.String( l_proc_level
1260 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1261 ,'Period To '||P_PERIOD_TO
1262 );
1263
1264 END IF; --(l_proc_level >= l_dbg_level)
1265
1266 l_Start_Period := P_PERIOD_FROM ;
1267 l_End_Period := P_PERIOD_TO;
1268 l_ledger_id := p_ledger_id;
1269 l_legal_entity_id := P_legal_entity_id;
1270 --populate BSV for current legal entity and ledger
1271 /*
1272 l_Populate_Bsv_Flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(l_Ledger_Id,
1273 l_Legal_Entity_Id);
1274 IF l_Populate_Bsv_Flag = 'F' THEN
1275 IF (l_Proc_Level >= l_Dbg_Level) THEN
1276 Fnd_Log.STRING(l_Proc_Level,
1277 l_Module_Prefix || '.' || l_Proc_Name,
1278 'fail to populate BSV');
1279 END IF; --(l_proc_level >= l_dbg_level)
1280 END IF;
1281 */
1282
1283 --generate dynamic sql to find the journal which will be set flag p.
1284
1285 l_Populate_Journal_Sql :=
1286 'SELECT ' ||
1287 ' jeh.je_header_id' ||
1288 ' ,jel.je_line_num' ||
1289 -- ' ,jeh.period_name' ||
1290 -- ' ,jeh.je_category' ||
1291 -- ' ,jeh.je_source' ||
1292 -- ' ,bsv.legal_entity_id' || ',' ||
1293
1294 -- ',jeh.default_effective_date ' ||
1295 ' FROM gl_je_headers jeh' ||
1296 ' ,gl_je_lines jel' ||
1297 ' ,gl_code_combinations gcc' ||
1298 ' ,gl_periods gp' ||
1299 ' ,gl_ledgers led' ||
1300 ' ,ja_cn_ledger_le_bsv_gt bsv' ||
1301 ' WHERE jeh.je_header_id = jel.je_header_id' ||
1302 ' AND jeh.status = ''P''' ||
1303 ' AND jeh.period_name = gp.period_name' ||
1304 ' AND jel.code_combination_id = gcc.code_combination_id' ||
1305 ' AND jeh.LEDGER_ID = ' || l_Ledger_Id ||
1306 ' AND gcc.' || l_Company_Column_Name ||
1307 ' = bsv.BAL_SEG_VALUE' ||
1308 ' AND bsv.legal_entity_id = ' ||
1309 l_Legal_Entity_Id ||
1310 ' AND gp.start_date BETWEEN' ||
1311 ' (SELECT start_date' ||
1312 ' FROM gl_periods' ||
1313 ' WHERE period_name =''' ||
1314 l_Start_Period || '''' ||
1315 ' AND period_set_name = led.period_set_name)' ||
1316 ' AND (SELECT start_date' ||
1317 ' FROM gl_periods' ||
1318 ' WHERE period_name =''' ||
1319 l_End_Period || '''' ||
1320 ' AND period_set_name = led.period_set_name)' ||
1321 ' AND gp.period_set_name = led.period_set_name' ||
1322 ' AND gp.period_type = led.accounted_period_type' ||
1323 ' AND led.ledger_id = jeh.ledger_id' ||
1324 ' AND nvl(jel.global_attribute2' ||
1325 ' ,''U'') <> ''P''';
1326
1327 -- set all the journal found in gl_je_lines as processed journal lines.
1328 OPEN c_journal FOR l_Populate_Journal_Sql;
1329 loop
1330 fetch c_journal into l_header_id,l_line_num;
1331 EXIT WHEN c_journal%NOTFOUND;
1332 update gl_je_lines
1333 set global_attribute2='P'
1334 where je_header_id=l_header_id
1335 and je_line_num=l_line_num;
1336 end loop;
1337 close c_journal;
1338
1339
1340 EXCEPTION
1341
1342 WHEN OTHERS THEN
1343 IF (l_proc_level >= l_dbg_level)
1344 THEN
1345 FND_LOG.String( l_proc_level
1346 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1347 ,SQLCODE||':'||SQLERRM);
1348 END IF; --(l_proc_level >= l_dbg_level)
1349
1350
1351 END Set_flag_P;
1352
1353 --==========================================================================
1354 -- FUNCTION NAME:
1355 -- Generate_Journal_Num Public
1356 --
1357 -- DESCRIPTION:
1358 -- This procedure is used to generate journal number based on period
1359 -- legal entity level, ledger and je_header_id.
1360 --
1361 --
1362 -- PARAMETERS:
1363 -- p_period_Name period name
1364 -- p_ledger_id ledger id
1365 -- P_legal_entity_id legal entity id
1366 --
1367 -- DESIGN REFERENCES:
1368 -- None
1369 --
1370 -- CHANGE HISTORY:
1371 -- 07/09/2007 yanbo liu created
1372 --===========================================================================
1373
1374 PROCEDURE Generate_Journal_Num( p_period_Name IN VARCHAR2,
1375 p_ledger_id in number,
1376 P_legal_entity_id in number) IS
1377 l_Period_Name Gl_Periods.Period_Name%TYPE;
1378 l_Je_Header_Id NUMBER;
1379 l_Journal_Number NUMBER;
1380 l_Je_Appending_Id NUMBER;
1381
1382 l_ledger_id number;
1383 l_legal_entity_id number;
1384 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1385 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1386 l_Proc_Name VARCHAR2(100) := 'generate_journal_num';
1387
1388 v_code NUMBER;
1389 v_errm VARCHAR2(64);
1390
1391 --find unpost data in table ja_cn_journal_lines.
1392 CURSOR c_Journal IS
1393 SELECT distinct Je_Header_Id
1394 FROM ja_cn_journal_lines
1395 WHERE period_name=p_Period_Name
1396 AND status = 'U'
1397 order by Je_Header_Id;
1398
1399 -- for the l_Je_Header_Id, the journal number is created.
1400 CURSOR c_Journal_Appending IS
1401 SELECT DISTINCT Je_Header_Id, Journal_Number
1402 FROM Ja_Cn_Journal_Lines Jl
1403 WHERE Je_Header_Id = l_Je_Header_Id
1404 AND Journal_Number IS NOT NULL
1405 AND Company_Segment IN
1406 (SELECT bsv.bal_seg_value
1407 FROM ja_cn_ledger_le_bsv_gt bsv
1408 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1409 and ledger_id = l_ledger_id);
1410
1411 BEGIN
1412 --log for debug
1413 IF (l_Proc_Level >= l_Dbg_Level) THEN
1414 Fnd_Log.STRING(l_Proc_Level,
1415 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1416 'Enter procedure');
1417 END IF; --(l_proc_level >= l_dbg_level)
1418
1419
1420 l_Period_Name := p_Period_Name;
1421 l_legal_entity_id:=p_legal_entity_id;
1422 l_ledger_id:=p_ledger_id;
1423
1424 OPEN c_Journal;
1425 LOOP
1426 FETCH c_Journal INTO l_Je_Header_Id;
1427 EXIT WHEN c_Journal%NOTFOUND;
1428
1429 OPEN c_Journal_Appending;
1430 FETCH c_Journal_Appending
1431 INTO l_Je_Appending_Id, l_Journal_Number;
1432
1433 IF c_Journal_Appending%FOUND THEN
1434 CLOSE c_Journal_Appending;
1435 UPDATE Ja_Cn_Journal_Lines jop
1436 SET Journal_Number = l_Journal_Number
1437 WHERE Je_Header_Id = l_Je_Header_Id
1438 AND Journal_Number IS NULL
1439 AND Company_Segment IN
1440 (SELECT bsv.bal_seg_value
1441 FROM ja_cn_ledger_le_bsv_gt bsv
1442 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1443 and ledger_id = l_ledger_id);
1444 ELSE
1445
1446 CLOSE c_Journal_Appending;
1447 END IF; --c_journal_appending%FOUND
1448
1449 --I Think this will casue some problem, maybe update the journals updated above.
1450 l_Journal_Number := Ja_Cn_Update_Jl_Seq_Pkg.Fetch_Jl_Seq(p_Legal_Entity_Id => l_Legal_Entity_Id,
1451 p_ledger_id=>l_ledger_id,
1452 p_Period_Name => l_Period_Name);
1453
1454 IF Nvl(l_Journal_Number, 0) > 0 THEN
1455 UPDATE Ja_Cn_Journal_Lines
1456 SET Journal_Number = l_Journal_Number
1457 WHERE Je_Header_Id = l_Je_Header_Id
1458 AND Company_Segment IN
1459 (SELECT bsv.bal_seg_value
1460 FROM ja_cn_ledger_le_bsv_gt bsv
1461 WHERE Legal_Entity_Id = l_Legal_Entity_Id
1462 and ledger_id = l_ledger_id);
1463 END IF;
1464
1465 END LOOP;
1466 CLOSE c_Journal;
1467 -- commit;
1468 EXCEPTION
1469 WHEN OTHERS THEN
1470 v_code := SQLCODE;
1471 v_errm := SUBSTR(SQLERRM, 1 , 64);
1472 IF (l_Proc_Level >= l_Dbg_Level) THEN
1473 Fnd_Log.STRING(l_Proc_Level,
1474 l_Module_Prefix || '.' || l_Proc_Name ||
1475 '. Other_Exception ',
1476 SQLCODE || ':' || SQLERRM);
1477 END IF; --(l_proc_level >= l_dbg_level)
1478 RAISE;
1479 END Generate_Journal_Num;
1480
1481 --==========================================================================
1482 -- PROCEDURE NAME:
1483 -- generate_code_combination_view private
1484 --
1485 -- DESCRIPTION:
1486 -- This procedure is used to populate account segment, company segment
1487 -- cost center segment and project number if project option as 'COA'
1488 -- into view JA_CN_CODE_COMBINATION_V
1489 -- PARAMETERS:
1490 --
1491 --
1492 --
1493 -- DESIGN REFERENCES:
1494 -- None
1495 --
1496 -- CHANGE HISTORY:
1497 -- 02/21/2006 Qingjun Zhao Created
1498 -- 04/10/2006 Qingjun Zhao Deal with this situation which Cost
1499 -- segment is NULL in current Chart of
1500 -- account
1501 --===========================================================================
1502 PROCEDURE Generate_Code_Combination_View(p_ledger_id in number) IS
1503
1504 l_Create_View_Sql VARCHAR2(4000);
1505 l_Company_Column_Name VARCHAR2(30);
1506 l_Account_Column_Name VARCHAR2(30);
1507 l_Cost_Column_Name VARCHAR2(30);
1508 l_Project_Column_Name VARCHAR2(30);
1509 l_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1510 l_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1511 l_Proc_Name VARCHAR2(100) := 'generate_code_combination_view';
1512 l_Second_Track_Col_Name VARCHAR2(30);
1513 l_Other_Cols_Name VARCHAR2(200);
1514 l_ledger_id number;
1515 l_Project_Option Ja_Cn_Sub_Acc_Sources_All.Project_Source_Flag%type;
1516
1517 cursor c_Project_Option is
1518 SELECT Project_Source_Flag
1519 FROM Ja_Cn_Sub_Acc_Sources_All ja,gl_ledgers gl
1520 where ja.chart_of_accounts_id=gl.chart_of_accounts_id
1521 and gl.ledger_id=l_ledger_id;
1522
1523
1524 CURSOR c_Cost_Center IS
1525 SELECT Fsav.Application_Column_Name
1526 FROM Fnd_Id_Flex_Segments Fifs,
1527 Fnd_Segment_Attribute_Values Fsav,
1528 Gl_Ledgers Led
1529 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
1530 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
1531 AND Fsav.Segment_Attribute_Type = 'FA_COST_CTR'
1532 AND Fsav.Attribute_Value = 'Y'
1533 AND Fifs.Application_Id = 101
1534 and fsav.id_flex_code = fifs.id_flex_code
1535 and fsav.id_flex_code = 'GL#'
1536 AND Fifs.Application_Id = Fsav.Application_Id
1537 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
1538 AND Led.Ledger_Id = l_Ledger_Id;
1539
1540 --jogen
1541 CURSOR c_Segements IS
1542 SELECT Fsav.Application_Column_Name
1543 FROM Fnd_Id_Flex_Segments Fifs,
1544 Fnd_Segment_Attribute_Values Fsav,
1545 Gl_Ledgers Led
1546 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
1547 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
1548 AND Fsav.Segment_Attribute_Type = 'GL_GLOBAL'
1549 AND Fsav.Attribute_Value = 'Y'
1550 AND Fifs.Application_Id = 101
1551 and fifs.id_flex_code = fsav.id_flex_code
1552 and fifs.id_flex_code = 'GL#'
1553 AND Fifs.Application_Id = Fsav.Application_Id
1554 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
1555 AND Led.Ledger_Id = l_Ledger_Id;
1556
1557 --jogen
1558 BEGIN
1559
1560 --log for debug
1561 IF (l_Proc_Level >= l_Dbg_Level) THEN
1562 Fnd_Log.STRING(l_Proc_Level,
1563 l_Module_Prefix || '.' || l_Proc_Name || '.begin',
1564 'Enter procedure');
1565 END IF; --(l_proc_level >= l_dbg_level)
1566 l_ledger_id := p_ledger_id;
1567
1568 OPEN c_Project_Option;
1569 FETCH c_Project_Option
1570 INTO l_Project_Option;
1571
1572 --if "Project" isn't defined,then consider "Project"
1573 --as "Project Not considered"--'N'
1574 IF (c_Project_Option%NOTFOUND) THEN
1575 l_Project_Option := 'N';
1576 END IF; --(c_project_option%NOTFOUND)
1577 close c_Project_Option;
1578 --get application column name of company segment
1579 SELECT led.bal_seg_column_name
1580 INTO l_Company_Column_Name
1581 from gl_ledgers led
1582 where Led.Ledger_Id = l_Ledger_Id;
1583
1584 --get application column name of account segment
1585 SELECT Fsav.Application_Column_Name
1586 INTO l_Account_Column_Name
1587 FROM Fnd_Id_Flex_Segments Fifs,
1588 Fnd_Segment_Attribute_Values Fsav,
1589 Gl_Ledgers Led
1590 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
1591 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
1592 AND Fsav.Segment_Attribute_Type = 'GL_ACCOUNT'
1593 AND Fsav.Attribute_Value = 'Y'
1594 AND Fifs.Application_Id = 101
1595 and fsav.id_flex_code = fifs.id_flex_code
1596 and fsav.id_flex_code = 'GL#'
1597 AND Fifs.Application_Id = Fsav.Application_Id
1598 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
1599 AND Led.Ledger_Id = l_Ledger_Id;
1600 l_Create_View_Sql := 'select GCC.CODE_COMBINATION_ID,led.ledger_id,' ||
1601 'gcc.' || l_Company_Column_Name ||
1602 ' company_segment,';
1603 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
1604 l_Account_Column_Name || ' account_segment,';
1605
1606 --get application column name of cost center segment
1607 OPEN c_Cost_Center;
1608 FETCH c_Cost_Center
1609 INTO l_Cost_Column_Name;
1610
1611 IF c_Cost_Center%NOTFOUND THEN
1612 CLOSE c_Cost_Center;
1613 l_Create_View_Sql := l_Create_View_Sql ||
1614 ' to_char(null) cost_segment,';
1615 ELSE
1616 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
1617 l_Cost_Column_Name || ' cost_segment,';
1618 CLOSE c_Cost_Center;
1619 END IF; --c_cost_center%NOTFOUND
1620
1621 IF l_Project_Option = 'COA' THEN
1622 --get application column name of project segment
1623 SELECT Coa_Segment
1624 INTO l_Project_Column_Name
1625 FROM Ja_Cn_Sub_Acc_Sources_All ja,gl_ledgers gl
1626 WHERE ja.Chart_Of_Accounts_Id =gl.Chart_Of_Accounts_Id
1627 and gl.ledger_id=l_ledger_id;
1628 l_Create_View_Sql := l_Create_View_Sql || 'gcc.' ||
1629 l_Project_Column_Name || ' project_number,';
1630 ELSE
1631 l_Create_View_Sql := l_Create_View_Sql || 'to_char(null)' ||
1632 ' project_number,';
1633 END IF; --l_project_option = 'COA'
1634
1635 ---jogen
1636 BEGIN
1637 SELECT Fsav.Application_Column_Name
1638 INTO l_Second_Track_Col_Name
1639 FROM Fnd_Id_Flex_Segments Fifs,
1640 Fnd_Segment_Attribute_Values Fsav,
1641 Gl_Ledgers Led
1642 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
1643 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
1644 AND Fsav.Segment_Attribute_Type = 'GL_SECONDARY_TRACKING'
1645 AND Fsav.Attribute_Value = 'Y'
1646 AND Fifs.Application_Id = 101
1647 and fifs.id_flex_code = fsav.id_flex_code
1648 and fsav.id_flex_code = 'GL#'
1649 AND Fifs.Application_Id = Fsav.Application_Id
1650 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
1651 AND Led.Ledger_Id = l_Ledger_Id;
1652 EXCEPTION
1653 WHEN No_Data_Found THEN
1654 NULL;
1655 END;
1656
1657 IF l_Second_Track_Col_Name IS NULL THEN
1658 l_Second_Track_Col_Name := 'NULL';
1659 END IF;
1660
1661 FOR Rec_Segment IN c_Segements LOOP
1662 IF Rec_Segment.Application_Column_Name NOT IN
1663 (l_Company_Column_Name, l_Account_Column_Name, l_Cost_Column_Name,
1664 l_Second_Track_Col_Name) THEN
1665 l_Other_Cols_Name := l_Other_Cols_Name || '||''.''||' ||
1666 Rec_Segment.Application_Column_Name;
1667 END IF;
1668 END LOOP;
1669
1670 IF l_Other_Cols_Name IS NULL THEN
1671 l_Other_Cols_Name := 'NULL';
1672 ELSE
1673 l_Other_Cols_Name := Substr(l_Other_Cols_Name, 8);
1674 END IF;
1675
1676 l_Create_View_Sql := l_Create_View_Sql || l_Second_Track_Col_Name ||
1677 ' second_tracking_col,' || l_Other_Cols_Name ||
1678 ' other_columns,';
1679 --jogen
1680 l_Create_View_Sql := l_Create_View_Sql ||
1681 'to_number(null) project_id from gl_code_combinations gcc,' ||
1682 ' GL_LEDGERS led where led.chart_of_accounts_id ' ||
1683 ' = gcc.chart_of_accounts_id';
1684
1685 l_Create_View_Sql := 'create or replace view ja_cn_code_combination_v as ' ||
1686 l_Create_View_Sql;
1687
1688 --log for debug
1689 IF (l_Proc_Level >= l_Dbg_Level) THEN
1690 Fnd_Log.STRING(l_Proc_Level,
1691 l_Module_Prefix || '.' || l_Proc_Name,
1692 'l_create_view_sql:' || l_Create_View_Sql);
1693 END IF; --(l_proc_level >= l_dbg_level)
1694
1695 EXECUTE IMMEDIATE l_Create_View_Sql;
1696
1697 --log for dubug
1698 IF (l_Proc_Level >= l_Dbg_Level) THEN
1699 Fnd_Log.STRING(l_Proc_Level,
1700 l_Module_Prefix || '.' || l_Proc_Name || '.end',
1701 'Enter procedure');
1702 END IF; --(l_proc_level >= l_dbg_level)
1703
1704 EXCEPTION
1705 WHEN OTHERS THEN
1706 FND_FILE.put_line(FND_FILE.OUTPUT,SQLCODE || ':' || SQLERRM);
1707 --log for debug
1708 IF (l_Proc_Level >= l_Dbg_Level) THEN
1709 Fnd_Log.STRING(l_Proc_Level,
1710 l_Module_Prefix || '.' || l_Proc_Name ||
1711 '. Other_Exception ',
1712 SQLCODE || ':' || SQLERRM);
1713 END IF; --(l_proc_level >= l_dbg_level)
1714 RAISE;
1715 END Generate_Code_Combination_View;
1716
1717 --==========================================================================
1718 -- FUNCTION NAME:
1719 -- Import_Itemization_Data Public
1720 --
1721 -- DESCRIPTION:
1722 -- This procedure is the main program of itemization interface program.
1723 -- It will process the data in interface table and import them into
1724 -- table ja_cn_journal_lines. At last post journals to ja_cn_account_balances.
1725 --
1726 --
1727 -- PARAMETERS:
1728 -- P_LEDGER_ID ledger id
1729 -- P_LEGAL_ENTITY_ID legal entity id
1730 -- P_PERIOD_FROM period from
1731 -- P_PERIOD_TO period to
1732 --
1733 -- DESIGN REFERENCES:
1734 -- None
1735 --
1736 -- CHANGE HISTORY:
1737 -- 08/09/2007 yanbo liu created
1738 --===========================================================================
1739 procedure put_xml(P_LEDGER_ID IN NUMBER,
1740 P_LEGAL_ENTITY_ID IN NUMBER,
1741 P_PERIOD_FROM IN VARCHAR2,
1742 P_PERIOD_TO IN VARCHAR2
1743 )is
1744
1745 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1746 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1747 l_proc_name VARCHAR2(100) :='Put_xml';
1748
1749 L_XML_ITEM XMLTYPE;
1750 L_XML_REPORT XMLTYPE;
1751 L_XML_ROOT XMLTYPE:=null;
1752 L_XML_PARAMETER XMLTYPE;
1753 L_XML_LINE XMLTYPE;
1754
1755 L_LE_NAME HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
1756 L_LEDGER_NAME GL_LEDGERS.NAME%TYPE;
1757 L_JOURNAL_GROUP JA_CN_ITEM_INTERFACE.JOURNAL_GROUP%TYPE;
1758 L_JE_LINE_NUM JA_CN_ITEM_INTERFACE.JE_LINE_NUM%TYPE;
1759 L_STATUS_CODE JA_CN_ITEM_INTERFACE.STATUS%TYPE;
1760 L_DESCRIPTION FND_LOOKUP_VALUES.DESCRIPTION%TYPE;
1761
1762 l_period_from VARCHAR2(15);
1763
1764
1765 CURSOR C_ERROR_JOURNALS IS
1766 SELECT JOURNAL_GROUP,
1767 JE_LINE_NUM,
1768 STATUS,
1769 fnd.description
1770 FROM JA_CN_ITEM_INTERFACE JA,FND_LOOKUP_VALUES FND
1771 WHERE FND.meaning=JA.status
1772 AND FND.LANGUAGE = userenv('LANG')
1773 AND FND.lookup_type='JA_CN_ITEM_ERROR_CODE'
1774 AND LEDGER_ID = p_ledger_id
1775 AND STATUS IS NOT NULL
1776 AND period_name in(
1777 SELECT Gp.Period_Name
1778 FROM Gl_Periods Gp, Gl_Ledgers Led
1779 WHERE Led.Ledger_Id = p_Ledger_Id
1780 AND Led.Period_Set_Name = Gp.Period_Set_Name
1781 AND Led.Accounted_Period_Type = Gp.Period_Type
1782 AND Gp.Start_Date BETWEEN
1783 (SELECT Start_Date
1784 FROM Gl_Periods Gp
1785 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1786 AND Led.Accounted_Period_Type = Gp.Period_Type
1787 AND Gp.Period_Name = L_PERIOD_FROM )
1788 AND (SELECT Start_Date
1789 FROM Gl_Periods Gp
1790 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1791 AND Led.Accounted_Period_Type = Gp.Period_Type
1792 AND Gp.Period_Name = P_PERIOD_TO)
1793 )
1794 ORDER BY JOURNAL_GROUP,JA.JE_LINE_NUM;
1795 begin
1796 IF (l_proc_level >= l_dbg_level)
1797 THEN
1798 FND_LOG.String( l_proc_level
1799 ,l_module_prefix||'.'||l_proc_name||'.begin'
1800 ,'Enter procedure'
1801 );
1802 FND_LOG.String( l_proc_level
1803 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1804 ,'Ledger ID '||P_LEDGER_ID
1805 );
1806 FND_LOG.String( l_proc_level
1807 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1808 ,'Legal Entity ID '||P_LEGAL_ENTITY_ID
1809 );
1810 FND_LOG.String( l_proc_level
1811 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1812 ,'Period From '||P_PERIOD_FROM
1813 );
1814 FND_LOG.String( l_proc_level
1815 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1816 ,'Period To '||P_PERIOD_TO
1817 );
1818
1819 END IF; --(l_proc_level >= l_dbg_level)
1820
1821 l_period_from:=P_PERIOD_FROM;
1822 if P_PERIOD_FROM is null then
1823 --get the first period of current led
1824 SELECT Gp.Period_Name
1825 INTO l_period_from
1826 FROM Gl_Periods Gp, Gl_Ledgers Led
1827 WHERE Led.Ledger_Id = p_ledger_id
1828 AND Led.Period_Set_Name = Gp.Period_Set_Name
1829 AND Led.Accounted_Period_Type = Gp.Period_Type
1830 AND Gp.Start_Date IN
1831 (SELECT MIN(Start_Date)
1832 FROM Gl_Periods Gp
1833 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1834 AND Led.Accounted_Period_Type = Gp.Period_Type);
1835 end if;
1836
1837
1838 SELECT name
1839 INTO l_le_name
1840 FROM XLE_ENTITY_PROFILES
1841 WHERE legal_entity_id=p_legal_entity_id;
1842
1843 SELECT name
1844 INTO l_ledger_name
1845 FROM gl_ledgers
1846 WHERE ledger_id=p_ledger_id;
1847
1848 --write the parameter infomation into variable l_xml_parameter and last into l_xml_report
1849 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding="utf-8" ?>');
1850 l_xml_report := NULL;
1851 SELECT XMLELEMENT("P_LEDGER_NAME",l_ledger_name) INTO l_xml_item FROM dual;
1852 l_xml_parameter := l_xml_item;
1853 SELECT XMLELEMENT("P_LEGAL_NAME",l_le_name ) INTO l_xml_item FROM dual;
1854 SELECT XMLCONCAT(l_xml_parameter,l_xml_item) INTO l_xml_parameter FROM dual;
1855 SELECT XMLELEMENT("PERIOD_START",P_PERIOD_FROM) INTO l_xml_item FROM dual;
1856 SELECT XMLCONCAT(l_xml_parameter,l_xml_item) INTO l_xml_parameter FROM dual;
1857 SELECT XMLELEMENT("PERIOD_END",P_PERIOD_TO) INTO l_xml_item FROM dual;
1858 SELECT XMLCONCAT(l_xml_parameter,l_xml_item) INTO l_xml_parameter FROM dual;
1859 SELECT XMLCONCAT(l_xml_report,l_xml_parameter) INTO l_xml_report FROM dual;
1860
1861 OPEN C_ERROR_JOURNALS;
1862 LOOP
1863 FETCH C_ERROR_JOURNALS INTO L_JOURNAL_GROUP,L_JE_LINE_NUM,L_STATUS_CODE,L_DESCRIPTION;
1864 EXIT WHEN C_ERROR_JOURNALS%NOTFOUND;
1865 l_xml_line:=NULL;
1866 SELECT XMLELEMENT("JOURNAL_GROUP",L_JOURNAL_GROUP) INTO l_xml_item FROM dual;
1867 l_xml_line:=l_xml_item;
1868 SELECT XMLELEMENT("JE_LINE_NUM",L_JE_LINE_NUM) INTO l_xml_item FROM dual;
1869 SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
1870 SELECT XMLELEMENT("STATUS_CODE",L_STATUS_CODE) INTO l_xml_item FROM dual;
1871 SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
1872 SELECT XMLELEMENT("ERROR_MESSAGE",L_DESCRIPTION) INTO l_xml_item FROM dual;
1873 SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
1874 SELECT XMLELEMENT("ERROR_JOURNAL",l_xml_line) INTO l_xml_line FROM dual;
1875 SELECT XMLCONCAT(l_xml_report,l_xml_line) INTO l_xml_report FROM dual;
1876 END LOOP;
1877 CLOSE C_ERROR_JOURNALS;
1878
1879 SELECT XMLELEMENT( "REPORT",l_xml_report) INTO l_xml_root FROM dual;
1880 JA_CN_UTILITY.Output_Conc(l_xml_root.getclobval());
1881
1882
1883 EXCEPTION
1884
1885 WHEN OTHERS THEN
1886 IF (l_proc_level >= l_dbg_level)
1887 THEN
1888 FND_LOG.String( l_proc_level
1889 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
1890 ,SQLCODE||':'||SQLERRM);
1891 END IF; --(l_proc_level >= l_dbg_level)
1892 end put_xml;
1893
1894 --==========================================================================
1895 -- FUNCTION NAME:
1896 -- Import_Itemization_Data Public
1897 --
1898 -- DESCRIPTION:
1899 -- This procedure is the main program of itemization interface program.
1900 -- It will process the data in interface table and import them into
1901 -- table ja_cn_journal_lines. At last post journals to ja_cn_account_balances.
1902 --
1903 --
1904 -- PARAMETERS:
1905 -- P_LEDGER_ID ledger id
1906 -- P_LEGAL_ENTITY_ID legal entity id
1907 -- P_PERIOD_FROM period from
1908 -- P_PERIOD_TO period to
1909 --
1910 -- DESIGN REFERENCES:
1911 -- None
1912 --
1913 -- CHANGE HISTORY:
1914 -- 08/09/2007 yanbo liu created
1915 --===========================================================================
1916
1917 PROCEDURE Import_Itemization_Data(Errbuf OUT NOCOPY VARCHAR2,
1918 Retcode OUT NOCOPY VARCHAR2,
1919 P_LEGAL_ENTITY_ID IN NUMBER,
1920 P_LEDGER_ID IN NUMBER,
1921 P_PERIOD_FROM IN VARCHAR2,
1922 P_PERIOD_TO IN VARCHAR2
1923 ) IS
1924
1925 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1926 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1927 l_proc_name VARCHAR2(100) :='Import_Itemization_Data';
1928
1929 l_Populate_Bsv_Flag VARCHAR2(1);
1930
1931 v_code NUMBER;
1932 v_errm VARCHAR2(64);
1933
1934 l_Phase VARCHAR2(100);
1935 l_Status VARCHAR2(100);
1936 l_Dev_Phase VARCHAR2(100);
1937 l_Dev_Status VARCHAR2(100);
1938 l_Message VARCHAR2(100);
1939
1940 l_period_start VARCHAR2(15);
1941 l_period_end VARCHAR2(15);
1942 invalid_period_num number;
1943 l_Error_Msg VARCHAR2(2000);
1944
1945 l_period_from VARCHAR2(15);
1946 l_period_to VARCHAR2(15);
1947 l_ledger_id number;
1948 l_legal_entity_id number;
1949
1950 l_journal_group number(15);
1951 l_header_id number(15);
1952 l_sql varchar2(4000);
1953 l_Period_Name varchar(25);
1954
1955 l_Post_Con_Req_Id number(15):=0;
1956 l_Result boolean;
1957 l_Conc_Succ BOOLEAN;
1958 l_Post_Fail EXCEPTION;
1959 -- l_Phase_Code Fnd_Lookup_Values.Lookup_Code%TYPE;
1960 -- l_Status_Code Fnd_Lookup_Values.Lookup_Code%TYPE;
1961 l_third_party_number ja_cn_journal_lines.third_party_number%type;
1962 l_third_party_id ja_cn_journal_lines.third_party_id%type;
1963 l_third_party_type ja_cn_journal_lines.third_party_type%type;
1964 l_project_number ja_cn_journal_lines.project_number%type;
1965 l_project_id ja_cn_journal_lines.project_id%type;
1966 l_project_source ja_cn_journal_lines.project_source%type;
1967
1968 l_PERSONNEL_id ja_cn_journal_lines.personnel_id%type;
1969 l_PERSONNEL_NUMBER ja_cn_journal_lines.PERSONNEL_NUMBER%type;
1970
1971 l_creator ja_cn_journal_lines.journal_creator%type;
1972 l_creator_id ja_cn_journal_lines.journal_creator%type;
1973 l_APPROVER ja_cn_journal_lines.journal_APPROVER%type;
1974 l_APPROVER_id ja_cn_journal_lines.journal_creator%type;
1975 l_POSTER ja_cn_journal_lines.journal_POSTER%type;
1976 l_POSTER_id ja_cn_journal_lines.journal_creator%type;
1977
1978 L_EFFECTIVE_DATE DATE;
1979 L_START_DATE DATE;
1980 L_END_DATA DATE;
1981
1982
1983 cursor c_journal_group is
1984 select distinct je_header_id
1985 from ja_cn_journal_lines
1986 where status='U';
1987
1988 CURSOR c_Period_Name IS
1989 SELECT Gp.Period_Name
1990 FROM Gl_Periods Gp, Gl_Ledgers Led
1991 WHERE Led.Ledger_Id = l_Ledger_Id
1992 AND Led.Period_Set_Name = Gp.Period_Set_Name
1993 AND Led.Accounted_Period_Type = Gp.Period_Type
1994 AND Gp.Start_Date BETWEEN
1995 (SELECT Start_Date
1996 FROM Gl_Periods Gp
1997 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
1998 AND Led.Accounted_Period_Type = Gp.Period_Type
1999 AND Gp.Period_Name = l_period_from )
2000 AND (SELECT Start_Date
2001 FROM Gl_Periods Gp
2002 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
2003 AND Led.Accounted_Period_Type = Gp.Period_Type
2004 AND Gp.Period_Name = l_period_to)
2005 ORDER BY Gp.Start_Date;
2006
2007 cursor c_third_party is
2008 select third_party_number,third_party_type
2009 from ja_cn_journal_lines
2010 where status='U'
2011 and third_party_number is not null
2012 for update;
2013
2014 cursor c_project is
2015 select project_number,project_source
2016 from ja_cn_journal_lines
2017 where status='U'
2018 and project_number is not null
2019 for update;
2020
2021 cursor c_PERSONNEL is
2022 select PERSONNEL_ID, DEFAULT_EFFECTIVE_DATE
2023 from ja_cn_journal_lines
2024 where status='U'
2025 and PERSONNEL_ID is not null;
2026
2027 cursor c_creator is
2028 select journal_creator, DEFAULT_EFFECTIVE_DATE
2029 from ja_cn_journal_lines
2030 where status='U'
2031 and journal_creator is not null;
2032
2033 cursor c_approver is
2034 select journal_approver, DEFAULT_EFFECTIVE_DATE
2035 from ja_cn_journal_lines
2036 where status='U'
2037 and journal_approver is not null;
2038
2039 cursor c_poster is
2040 select journal_poster, DEFAULT_EFFECTIVE_DATE
2041 from ja_cn_journal_lines
2042 where status='U'
2043 and journal_poster is not null;
2044
2045 BEGIN
2046
2047 IF (l_proc_level >= l_dbg_level)
2048 THEN
2049 FND_LOG.String( l_proc_level
2050 ,l_module_prefix||'.'||l_proc_name||'.begin'
2051 ,'Enter procedure'
2052 );
2053 FND_LOG.String( l_proc_level
2054 ,l_module_prefix||'.'||l_proc_name||'.parameters'
2055 ,'Ledger ID '||P_LEDGER_ID
2056 );
2057 FND_LOG.String( l_proc_level
2058 ,l_module_prefix||'.'||l_proc_name||'.parameters'
2059 ,'Legal Entity ID '||P_LEGAL_ENTITY_ID
2060 );
2061 FND_LOG.String( l_proc_level
2062 ,l_module_prefix||'.'||l_proc_name||'.parameters'
2063 ,'Period From '||P_PERIOD_FROM
2064 );
2065 FND_LOG.String( l_proc_level
2066 ,l_module_prefix||'.'||l_proc_name||'.parameters'
2067 ,'Period To '||P_PERIOD_TO
2068 );
2069
2070 END IF; --(l_proc_level >= l_dbg_level)
2071
2072 l_period_from := P_PERIOD_FROM ;
2073 l_period_to := P_PERIOD_TO;
2074 l_ledger_id := p_ledger_id;
2075 l_legal_entity_id := P_legal_entity_id;
2076
2077 -- If parameter P_PERIOD_FROM is null then set it as the first period of current ledger.
2078 if P_PERIOD_FROM is null then
2079 --get the first period of current led
2080 SELECT Gp.Period_Name
2081 INTO l_period_from
2082 FROM Gl_Periods Gp, Gl_Ledgers Led
2083 WHERE Led.Ledger_Id = l_ledger_id
2084 AND Led.Period_Set_Name = Gp.Period_Set_Name
2085 AND Led.Accounted_Period_Type = Gp.Period_Type
2086 AND Gp.Start_Date IN
2087 (SELECT MIN(Start_Date)
2088 FROM Gl_Periods Gp
2089 WHERE Led.Period_Set_Name = Gp.Period_Set_Name
2090 AND Led.Accounted_Period_Type = Gp.Period_Type);
2091 end if;
2092
2093
2094
2095
2096
2097 --prepare for the program, set value for global variable.
2098 fnd_file.PUT_LINE(fnd_file.LOG,l_legal_entity_id);
2099 fnd_file.PUT_LINE(fnd_file.LOG,l_ledger_id );
2100 fnd_file.PUT_LINE(fnd_file.LOG,l_period_from);
2101 fnd_file.PUT_LINE(fnd_file.LOG,l_period_to);
2102
2103 --=======================================================================
2104 --the following piece of code is to check if the periods are all closed periods.
2105 l_period_start := P_PERIOD_FROM ;
2106 l_period_end := P_PERIOD_TO;
2107
2108 SELECT COUNT(*)
2109 INTO invalid_period_num
2110 FROM Gl_Period_Statuses GP
2111 WHERE GP.application_id = 101
2112 AND GP.ledger_id = p_ledger_id
2113 AND GP.start_date >=
2114 (SELECT START_DATE
2115 FROM Gl_Period_Statuses
2116 WHERE LEDGER_ID = l_ledger_id
2117 AND PERIOD_NAME = l_period_start
2118 AND APPLICATION_ID = 101)
2119 AND GP.end_date <=
2120 (SELECT END_DATE
2121 FROM Gl_Period_Statuses
2122 WHERE LEDGER_ID = l_ledger_id
2123 AND PERIOD_NAME = l_period_end
2124 AND APPLICATION_ID = 101)
2125 AND (GP.closing_status <> 'P'
2126 OR GP.closing_status <> 'C');
2127
2128 IF ( invalid_period_num > 0 )
2129 THEN
2130 --Raise error message
2131 Fnd_Message.Set_Name(Application => 'JA',
2132 NAME => 'JA_CN_INTERFACE_OPEN_PERIOD');
2133 l_Error_Msg := Fnd_Message.Get;
2134
2135 --Output error message
2136 Fnd_File.Put_Line(Fnd_File.LOG, l_Error_Msg);
2137 RETURN;
2138 END IF;
2139
2140 --=======================================================================
2141
2142
2143
2144 --get coa of parameter ledger id.
2145 SELECT chart_of_accounts_id
2146 INTO l_coa
2147 FROM gl_ledgers
2148 WHERE ledger_id = P_LEDGER_ID;
2149
2150 --get application column name of Company segment
2151 SELECT Fsav.Application_Column_Name
2152 INTO l_Company_Column_Name
2153 FROM Fnd_Id_Flex_Segments Fifs,
2154 Fnd_Segment_Attribute_Values Fsav,
2155 Gl_Ledgers Led
2156 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
2157 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
2158 AND Fsav.Segment_Attribute_Type = 'GL_BALANCING'
2159 AND Fsav.Attribute_Value = 'Y'
2160 AND Fifs.Application_Id = 101
2161 and fsav.id_flex_code = fifs.id_flex_code
2162 and fsav.id_flex_code = 'GL#'
2163 AND Fifs.Application_Id = Fsav.Application_Id
2164 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
2165 AND Led.Ledger_Id = l_ledger_id;
2166 --get application column name of account segment
2167 SELECT Fsav.Application_Column_Name
2168 INTO l_Account_Column_Name
2169 FROM Fnd_Id_Flex_Segments Fifs,
2170 Fnd_Segment_Attribute_Values Fsav,
2171 Gl_Ledgers Led
2172 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
2173 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
2174 AND Fsav.Segment_Attribute_Type = 'GL_ACCOUNT'
2175 AND Fsav.Attribute_Value = 'Y'
2176 AND Fifs.Application_Id = 101
2177 and fsav.id_flex_code = fifs.id_flex_code
2178 and fsav.id_flex_code = 'GL#'
2179 AND Fifs.Application_Id = Fsav.Application_Id
2180 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
2181 AND Led.Ledger_Id = l_ledger_id;
2182 --get application column name of cost center segment
2183 SELECT Fsav.Application_Column_Name
2184 into l_Cost_CRT_Column_Name
2185 FROM Fnd_Id_Flex_Segments Fifs,
2186 Fnd_Segment_Attribute_Values Fsav,
2187 Gl_Ledgers Led
2188 WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
2189 AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
2190 AND Fsav.Segment_Attribute_Type = 'FA_COST_CTR'
2191 AND Fsav.Attribute_Value = 'Y'
2192 AND Fifs.Application_Id = 101
2193 and fsav.id_flex_code = fifs.id_flex_code
2194 and fsav.id_flex_code = 'GL#'
2195 AND Fifs.Application_Id = Fsav.Application_Id
2196 AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
2197 AND Led.Ledger_Id = l_Ledger_Id;
2198
2199 l_Populate_Bsv_Flag := Ja_Cn_Utility.Populate_Ledger_Le_Bsv_Gt(P_Ledger_Id,
2200 P_Legal_Entity_Id);
2201 IF l_Populate_Bsv_Flag = 'F' THEN
2202 IF (l_Proc_Level >= l_Dbg_Level) THEN
2203 Fnd_Log.STRING(l_Proc_Level,
2204 l_Module_Prefix || '.' || l_Proc_Name,
2205 'fail to populate BSV');
2206 END IF; --(l_proc_level >= l_dbg_level)
2207 END IF;
2208
2209 --set a flag 'P' for journals in table gl_je_lines to identify them as processed journal.
2210
2211 set_flag_p( l_ledger_id ,
2212 l_legal_entity_id,
2213 l_period_from,
2214 l_period_to );
2215
2216
2217
2218 --validation the lines in interface table, if the lines is validated, the staus
2219 --is set to 'S' . if not the status code will be set in interface table.
2220 Validation(l_LEDGER_ID,
2221 l_LEGAL_ENTITY_ID,
2222 L_PERIOD_FROM,
2223 L_PERIOD_TO) ;
2224
2225 --after validation the data is prepared. for example the ccid, company segment
2226 --account segment is prepared. for the je_header_id column in table
2227 --ja_cn_journal_lines will be set journal group provisionally.
2228 --import the data which status is 'S' to table ja_cn_journal_lines.
2229
2230
2231 l_sql:='insert into ja_cn_journal_lines
2232 (je_header_id,
2233 SET_OF_BOOKS_ID,
2234 legal_entity_id,
2235 JOURNAL_NUMBER,
2236 JE_CATEGORY,
2237 DEFAULT_EFFECTIVE_DATE,
2238 PERIOD_NAME ,
2239 CURRENCY_CODE,
2240 CURRENCY_CONVERSION_RATE,
2241 JE_LINE_NUM ,
2242 DESCRIPTION ,
2243 COMPANY_SEGMENT ,
2244 CODE_COMBINATION_ID ,
2245 COST_CENTER,
2246 THIRD_PARTY_NUMBER ,
2247 PERSONNEL_ID,
2248 PROJECT_NUMBER ,
2249 ACCOUNT_SEGMENT ,
2250 ENTERED_DR,
2251 ENTERED_CR,
2252 ACCOUNTED_DR,
2253 ACCOUNTED_CR,
2254 STATUS,
2255 CREATED_BY,
2256 CREATION_DATE,
2257 LAST_UPDATED_BY,
2258 LAST_UPDATE_DATE,
2259 LAST_UPDATE_LOGIN,
2260 PROJECT_SOURCE,
2261 POPULATE_CODE ,
2262 THIRD_PARTY_TYPE ,
2263 JOURNAL_CREATOR ,
2264 JOURNAL_APPROVER,
2265 JOURNAL_POSTER,
2266 LEDGER_ID
2267 )
2268 select journal_group,
2269 ledger_id,
2270 LEGAL_ENTITY_ID,
2271 null,
2272 JE_CATEGORY,
2273 DEFAULT_EFFECTIVE_DATE,
2274 PERIOD_NAME ,
2275 CURRENCY_CODE,
2276 CURRENCY_CONVERSION_RATE,
2277 JE_LINE_NUM ,
2278 DESCRIPTION ,'||
2279 l_Company_Column_Name ||
2280 ',CODE_COMBINATION_ID,'||
2281 l_Cost_CRT_Column_Name ||
2282 ',THIRD_PARTY_NUMBER
2283 ,PERSONNEL_ID
2284 ,PROJECT_NUMBER,'||
2285 l_Account_Column_Name||
2286 ',ENTERED_DR,
2287 ENTERED_CR,
2288 ACCOUNTED_DR,
2289 ACCOUNTED_CR,
2290 ''U'',
2291 Fnd_Global.User_Id,
2292 SYSDATE,
2293 Fnd_Global.User_Id,
2294 SYSDATE,
2295 Fnd_Global.Login_Id,
2296 PROJECT_SOURCE,
2297 ''IMPORT'',
2298 THIRD_PARTY_TYPE ,
2299 JOURNAL_CREATOR_ID ,
2300 JOURNAL_APPROVER_ID,
2301 JOURNAL_POSTER_ID,
2302 LEDGER_ID
2303 from ja_cn_item_interface
2304 where status =''S''';
2305
2306 execute immediate l_sql;
2307 -- commit;
2308
2309 --after import, delete the journal which is successful
2310 -- delete from ja_cn_item_interface
2311 -- where status='S';
2312 -- commit;
2313 --get PERSONNEL_ID
2314 --according to PERSONNEL_ID,get personal number
2315 open c_PERSONNEL;
2316 loop
2317 fetch c_PERSONNEL into l_PERSONNEL_id,L_EFFECTIVE_DATE;
2318 exit when c_PERSONNEL%notfound;
2319 select employee_number--, Last_Name || First_Name Full_Name
2320 into l_PERSONNEL_NUMBER
2321 from Per_All_People_f
2322 where person_id = l_PERSONNEL_id
2323 AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
2324 AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
2325 update ja_cn_journal_lines
2326 set personnel_number = l_PERSONNEL_NUMBER
2327 where personnel_id = l_PERSONNEL_id ;
2328
2329 end loop;
2330 close c_PERSONNEL;
2331
2332 open c_creator;
2333 loop
2334 fetch c_creator into l_creator_id, L_EFFECTIVE_DATE;
2335 exit when c_creator%notfound;
2336 select Last_Name || First_Name Full_Name
2337 into l_creator
2338 from Per_All_People_f
2339 where person_id = to_number(l_creator_id)
2340 AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
2341 AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
2342
2343 update ja_cn_journal_lines
2344 set journal_creator = l_creator
2345 where journal_creator = l_creator_id;
2346
2347 end loop;
2348 close c_creator;
2349
2350 open c_approver;
2351 loop
2352 fetch c_approver into l_approver_id,L_EFFECTIVE_DATE;
2353 exit when c_approver%notfound;
2354 select Last_Name || First_Name Full_Name
2355 into l_approver
2356 from Per_All_People_f
2357 where person_id = to_number(l_approver_id )
2358 AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
2359 AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
2360
2361 update ja_cn_journal_lines
2362 set journal_approver = l_approver
2363 where journal_approver = l_APPROVER_id;
2364 end loop;
2365 close c_approver;
2366
2367 open c_poster;
2368 loop
2369 fetch c_poster into l_poster_id, L_EFFECTIVE_DATE;
2370 exit when c_poster%notfound;
2371 select Last_Name || First_Name Full_Name
2372 into l_poster
2373 from Per_All_People_f
2374 where person_id = to_number(l_poster_id)
2375 AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
2376 AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
2377
2378 update ja_cn_journal_lines
2379 set journal_poster = l_poster
2380 where journal_poster = l_poster_id;
2381 end loop;
2382 close c_poster;
2383
2384 --set je_header_id according to the journal group
2385 open c_journal_group;
2386 loop
2387 fetch c_journal_group into l_journal_group;
2388 exit when c_journal_group%notfound;
2389 SELECT ja_cn_item_interface_s.NEXTVAL into l_header_id FROM Dual;
2390 update ja_cn_journal_lines
2391 set je_header_id = l_header_id
2392 where status = 'U'
2393 and je_header_id = l_journal_group;
2394 end loop;
2395 close c_journal_group;
2396 -- commit;
2397 --get id according to number user input.
2398 --get third party id.
2399 open c_third_party;
2400 loop
2401 fetch c_third_party into l_third_party_number,l_third_party_type;
2402 exit when c_third_party%notfound;
2403 if l_third_party_type='C' then
2404 select party_id
2405 into l_third_party_id
2406 from Hz_Parties
2407 where Party_Number =l_THIRD_PARTY_NUMBER;
2408 elsif l_third_party_type='S' then
2409 select vendor_id
2410 into l_third_party_id
2411 from ap_suppliers
2412 where Segment1 =l_THIRD_PARTY_NUMBER;
2413 end if;
2414 update ja_cn_journal_lines
2415 set third_party_id=l_third_party_id
2416 where current of c_third_party;
2417 end loop;
2418 close c_third_party;
2419 --get project id
2420 open c_project;
2421 loop
2422 fetch c_project into l_project_number,l_project_source;
2423 exit when c_project%notfound;
2424 if l_project_source='PA' then
2425 select project_id
2426 into l_project_id
2427 from PA_PROJECTS_ALL
2428 where SEGMENT1=l_PROJECT_NUMBER;
2429 elsif l_project_source='COA'then
2430 select flex_value_id
2431 into l_project_id
2432 from FND_FLEX_VALUES ffv,
2433 FND_ID_FLEX_SEGMENTS fifs
2434 where ffv.flex_value_set_id=fifs.flex_value_set_id
2435 and fifs.id_flex_code='GL#'
2436 and fifs.id_flex_num=l_coa
2437 and (fifs.application_column_name =(select coa_segment
2438 from ja_cn_sub_acc_sources_all
2439 where chart_of_accounts_id=l_coa
2440 and coa_segment is not null)
2441 or fifs.application_column_name =(select history_coa_segment
2442 from ja_cn_sub_acc_sources_all
2443 where chart_of_accounts_id=l_coa
2444 and history_coa_segment is not null) )
2445 AND flex_value = l_PROJECT_NUMBER ;
2446 end if;
2447 update ja_cn_journal_lines
2448 set project_id=l_project_id
2449 where current of c_project;
2450 end loop;
2451 close c_project;
2452
2453 --generate journal number for each line in table ja_cn_journal_lines.
2454 OPEN c_Period_Name;
2455 LOOP
2456 FETCH c_Period_Name
2457 INTO l_Period_Name;
2458 EXIT WHEN c_Period_Name%NOTFOUND;
2459 -- generate journal number and journal line number
2460 Generate_Journal_Num(p_Period_Name => l_Period_Name,
2461 p_ledger_id =>p_ledger_id,
2462 p_legal_entity_id =>p_legal_entity_id);
2463
2464 END LOOP;
2465 close c_Period_Name;
2466
2467 -- l_Period_Name:='Dec-07';
2468 --prepare for the post program.
2469 Generate_Code_Combination_View(l_ledger_id);
2470 --call post program to post these journals itemized
2471 Ja_Cn_Post_Utility_Pkg.Post_Journal_Itemized(p_Period_Name =>l_Period_Name,
2472 p_ledger_Id => l_Ledger_Id,
2473 p_Legal_Entity_Id => l_Legal_Entity_Id);
2474
2475 commit;
2476
2477 put_xml(p_LEDGER_ID,
2478 p_LEGAL_ENTITY_ID,
2479 p_PERIOD_FROM,
2480 p_PERIOD_TO );
2481
2482
2483
2484 EXCEPTION
2485
2486 WHEN OTHERS THEN
2487 v_code := SQLCODE;
2488 v_errm := SUBSTR(SQLERRM, 1 , 64);
2489
2490 IF (l_proc_level >= l_dbg_level)
2491 THEN
2492 FND_LOG.String( l_proc_level
2493 ,l_module_prefix||'.'||l_proc_name||'.Other_Exception '
2494 ,SQLCODE||':'||SQLERRM);
2495 END IF; --(l_proc_level >= l_dbg_level)
2496
2497
2498 END Import_Itemization_Data;
2499
2500
2501 BEGIN
2502 NULL;
2503 -- Initialization
2504 -- <Statement>
2505 end JA_CN_ITEMIZATION_INTERFACE;
2506
2507
2508
2509
[Home] [Help]