[Home] [Help]
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;
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
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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:
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;
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
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
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;
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
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;
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
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;
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
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,
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 ||
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 ||
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:
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:
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
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
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:
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;
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||'''';
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;
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
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;
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;
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,
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.
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(
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:
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:
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);
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:
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
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;
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
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;