[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update ja_cn_item_interface
set status='EL01'
where legal_entity_id<>P_LEGAL_ENTITY_ID
and status='P';
select
JE_CATEGORY ,
CURRENCY_CODE,
THIRD_PARTY_NUMBER,
PERSONNEL_ID,
PROJECT_NUMBER,
PROJECT_SOURCE,
THIRD_PARTY_TYPE,
JOURNAL_CREATOR_ID,
JOURNAL_APPROVER_ID,
JOURNAL_POSTER_ID,
DEFAULT_EFFECTIVE_DATE
from ja_cn_item_interface
where status = 'P'
for update;
select count(*)
into l_count
from gl_je_categories_tl
where user_je_category_name = l_JE_CATEGORY;
update ja_cn_item_interface
set status='ECG1'
where current of c_journals;
select count(*)
into l_count
from fnd_currencies
where currency_code = l_CURRENCY_CODE;
update ja_cn_item_interface
set status='ECC1'
where current of c_journals;
select count(*)
into l_count
from FND_LOOKUP_VALUES
where LANGUAGE = userenv('LANG')
and lookup_code = l_THIRD_PARTY_TYPE
and lookup_type = 'JA_CN_THIRDPARTY_TYPE' ;
update ja_cn_item_interface
set status='ETP1'
where current of c_journals;
update ja_cn_item_interface
set status='ETP2'
where current of c_journals;
update ja_cn_item_interface
set status='ETP5'
where current of c_journals;
select count(*)
into l_count
from Hz_Parties
where Party_Number =l_THIRD_PARTY_NUMBER;
update ja_cn_item_interface
set status='ETP3'
where current of c_journals;
select count(*)
into l_count
from ap_suppliers
where Segment1 =l_THIRD_PARTY_NUMBER;
update ja_cn_item_interface
set status='ETP4'
where current of c_journals;
select count(*)
into l_count
from PER_ALL_PEOPLE_F
where PERSON_ID = l_PERSONNEL_ID
and effective_start_date<=l_effective_date
and effective_end_date>=l_effective_date;
update ja_cn_item_interface
set status='EPR1'
where current of c_journals;
select count(*)
into l_count
from FND_LOOKUP_VALUES
where lookup_code = l_PROJECT_SOURCE
and lookup_type like 'JA_CN_PROJECT_SOURCE'
and LANGUAGE = userenv('LANG') ;
update ja_cn_item_interface
set status='EPS1'
where current of c_journals;
select nvl(project_source_flag,'-1'),nvl(history_coa_segment,'-1')
into l_project_flag,l_history_coa
from ja_cn_sub_acc_sources_all
where chart_of_accounts_id=l_coa;
update ja_cn_item_interface
set status='EPS4'
where current of c_journals;
update ja_cn_item_interface
set status='EPS2'
where current of c_journals;
update ja_cn_item_interface
set status='EPS3'
where current of c_journals;
update ja_cn_item_interface
set status='EPN3'
where current of c_journals;
select count(*)
into l_count
from PA_PROJECTS_ALL
where SEGMENT1=l_PROJECT_NUMBER;
update ja_cn_item_interface
set status='EPN1'
where current of c_journals;
select count(*)
into l_count
from FND_FLEX_VALUES ffv,
FND_ID_FLEX_SEGMENTS fifs
where ffv.flex_value_set_id=fifs.flex_value_set_id
and fifs.id_flex_code='GL#'
and fifs.id_flex_num=l_coa
and (fifs.application_column_name =(select coa_segment
from ja_cn_sub_acc_sources_all
where chart_of_accounts_id=l_coa
and coa_segment is not null)
or fifs.application_column_name =(select history_coa_segment
from ja_cn_sub_acc_sources_all
where chart_of_accounts_id=l_coa
and history_coa_segment is not null) )
AND flex_value = l_PROJECT_NUMBER ;
update ja_cn_item_interface
set status='EPN2'
where current of c_journals;
select Last_Name || First_Name Full_Name
into l_creator
from Per_All_People_f
where person_id = l_JOURNAL_CREATOR
AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
update ja_cn_item_interface
set journal_creator_id = l_creator
where journal_creator_id = l_JOURNAL_CREATOR; */
update ja_cn_item_interface
set status='EJC1'
where current of c_journals;
select Last_Name || First_Name Full_Name
into l_approver
from Per_All_People_f
where person_id =l_JOURNAL_APPROVER
AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
update ja_cn_item_interface
set journal_creator_id = l_approver
where journal_creator_id = l_JOURNAL_APPROVER; */
update ja_cn_item_interface
set status='EJA1'
where current of c_journals;
select Last_Name || First_Name Full_Name
into l_poster
from Per_All_People_f
where person_id = l_JOURNAL_POSTER
AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
update ja_cn_item_interface
set journal_creator_id = l_poster
where journal_creator_id = l_JOURNAL_POSTER; */
update ja_cn_item_interface
set status='EJP1'
where current of c_journals;
l_sql := 'UPDATE JA_CN_ITEM_INTERFACE
SET status=''ECS1''
WHERE status=''P''
AND ' || l_Company_Column_Name ||
' NOT IN
(SELECT bsv.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt bsv
WHERE Legal_Entity_Id = '|| P_LEGAL_ENTITY_ID ||
' AND ledger_id = '|| P_LEDGER_ID||') ';
l_sql:='select journal_group,
legal_entity_id, '||
l_Company_Column_Name||
',sum(ENTERED_DR),
sum(ENTERED_CR),
sum(ACCOUNTED_DR),
SUM(ACCOUNTED_cR)
from ja_cn_item_interface
where status=''P''
and '|| l_Company_Column_Name||' is not null
and legal_entity_id= '||p_legal_entity_id||
' group by journal_group,
legal_entity_id,'
||l_Company_Column_Name;
l_sql1:='update ja_cn_item_interface
set status=''EB01''
where status = ''P''
and journal_group ='|| l_journal_group||
' and legal_entity_id ='|| l_legal_entity_id ||
' and '|| l_Company_Column_Name||'='||l_Company_segment;
l_sql1:='update ja_cn_item_interface
set status=''EB02''
where status = ''P''
and journal_group ='|| l_journal_group||
' and legal_entity_id ='|| l_legal_entity_id ||
' and '|| l_Company_Column_Name||'='||l_Company_segment;
select code_combination_id
from ja_cn_item_interface
where (status = 'P1'
or status = 'P2')
and code_combination_id is not null
for update ;
select code_combination_id,
rowid
from ja_cn_item_interface
where status = 'P1'
and code_combination_id is not null
for update;
select segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
code_combination_id
from ja_cn_item_interface
where status='P2'
for update;
update ja_cn_item_interface
set status='P1'
where segment1 is null
and segment2 is null
and segment3 is null
and segment4 is null
and segment5 is null
and segment6 is null
and segment7 is null
and segment8 is null
and segment9 is null
and segment10 is null
and segment11 is null
and segment12 is null
and segment13 is null
and segment14 is null
and segment15 is null
and segment16 is null
and segment17 is null
and segment18 is null
and segment19 is null
and segment20 is null
and segment21 is null
and segment22 is null
and segment23 is null
and segment24 is null
and segment25 is null
and segment26 is null
and segment27 is null
and segment28 is null
and segment29 is null
and segment30 is null
and status='P';
update ja_cn_item_interface
set status='P2'
where status='P';
update ja_cn_item_interface
set status='EC01'
where status='P1'
and code_combination_id is null;
select count(*)
into l_ccid_count
from gl_code_combinations
where code_combination_id=l_ccid
and chart_of_accounts_id=l_coa;
update ja_cn_item_interface
set status = 'EC02'
where current of c_ccid_check;
l_sql:='select '|| l_Company_Column_Name ||', '
|| l_Account_Column_Name ||', '
|| l_Cost_CRT_Column_Name ||
' from gl_code_combinations
where code_combination_id=' || l_ccid ||
' and chart_of_accounts_id='|| l_coa;
l_sql:='update ja_cn_item_interface set '
|| l_Company_Column_Name ||' = ''' || l_Company_value ||''', '
|| l_Account_Column_Name ||' = ''' || l_Account_value ||''', '
|| l_Cost_CRT_Column_Name ||' = ''' || l_Cost_CRT_value ||'''
where rowid='''||l_rowid||'''';
select code_combination_id
into l_ccid1
from gl_code_combinations
where nvl(segment1,-1)=nvl(l_segment_context(1),-1)
and nvl(segment2,-1)=nvl(l_segment_context(2),-1)
and nvl(segment3,-1)=nvl(l_segment_context(3),-1)
and nvl(segment4,-1)=nvl(l_segment_context(4),-1)
and nvl(segment5,-1)=nvl(l_segment_context(5),-1)
and nvl(segment6,-1)=nvl(l_segment_context(6),-1)
and nvl(segment7,-1)=nvl(l_segment_context(7),-1)
and nvl(segment8,-1)=nvl(l_segment_context(8),-1)
and nvl(segment9,-1)=nvl(l_segment_context(9),-1)
and nvl(segment10,-1)=nvl(l_segment_context(10),-1)
and nvl(segment11,-1)=nvl(l_segment_context(11),-1)
and nvl(segment12,-1)=nvl(l_segment_context(12),-1)
and nvl(segment13,-1)=nvl(l_segment_context(13),-1)
and nvl(segment14,-1)=nvl(l_segment_context(14),-1)
and nvl(segment15,-1)=nvl(l_segment_context(15),-1)
and nvl(segment16,-1)=nvl(l_segment_context(16),-1)
and nvl(segment17,-1)=nvl(l_segment_context(17),-1)
and nvl(segment18,-1)=nvl(l_segment_context(18),-1)
and nvl(segment19,-1)=nvl(l_segment_context(19),-1)
and nvl(segment20,-1)=nvl(l_segment_context(20),-1)
and nvl(segment21,-1)=nvl(l_segment_context(21),-1)
and nvl(segment12,-1)=nvl(l_segment_context(22),-1)
and nvl(segment23,-1)=nvl(l_segment_context(23),-1)
and nvl(segment24,-1)=nvl(l_segment_context(24),-1)
and nvl(segment25,-1)=nvl(l_segment_context(25),-1)
and nvl(segment26,-1)=nvl(l_segment_context(26),-1)
and nvl(segment27,-1)=nvl(l_segment_context(27),-1)
and nvl(segment28,-1)=nvl(l_segment_context(28),-1)
and nvl(segment29,-1)=nvl(l_segment_context(29),-1)
and nvl(segment30,-1)=nvl(l_segment_context(30),-1)
and chart_of_accounts_id=l_coa;
update ja_cn_item_interface
set Code_Combination_id=l_ccid1
where current of c_segments;
update ja_cn_item_interface
set status='EC04'
where current of c_segments;
update ja_cn_item_interface
set status='EC03'
where current of c_segments;
update ja_cn_item_interface
set status='P'
where status in('P1','P2');
update ja_cn_item_interface
set status = null;
update ja_cn_item_interface
set status = 'P'
where --legal_entity_id = P_LEGAL_ENTITY_ID and--this condition will be put the legal_consistent validation
ledger_id = P_LEDGER_ID
and period_name in(
SELECT Gp.Period_Name
FROM Gl_Periods Gp, Gl_Ledgers Led
WHERE Led.Ledger_Id = p_Ledger_Id
AND Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Start_Date BETWEEN
(SELECT Start_Date
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Period_Name = P_PERIOD_FROM )
AND (SELECT Start_Date
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Period_Name = P_PERIOD_TO)
);
update ja_cn_item_interface
set status='S'
where status='P';
'SELECT ' ||
' jeh.je_header_id' ||
' ,jel.je_line_num' ||
-- ' ,jeh.period_name' ||
-- ' ,jeh.je_category' ||
-- ' ,jeh.je_source' ||
-- ' ,bsv.legal_entity_id' || ',' ||
-- ',jeh.default_effective_date ' ||
' FROM gl_je_headers jeh' ||
' ,gl_je_lines jel' ||
' ,gl_code_combinations gcc' ||
' ,gl_periods gp' ||
' ,gl_ledgers led' ||
' ,ja_cn_ledger_le_bsv_gt bsv' ||
' WHERE jeh.je_header_id = jel.je_header_id' ||
' AND jeh.status = ''P''' ||
' AND jeh.period_name = gp.period_name' ||
' AND jel.code_combination_id = gcc.code_combination_id' ||
' AND jeh.LEDGER_ID = ' || l_Ledger_Id ||
' AND gcc.' || l_Company_Column_Name ||
' = bsv.BAL_SEG_VALUE' ||
' AND bsv.legal_entity_id = ' ||
l_Legal_Entity_Id ||
' AND gp.start_date BETWEEN' ||
' (SELECT start_date' ||
' FROM gl_periods' ||
' WHERE period_name =''' ||
l_Start_Period || '''' ||
' AND period_set_name = led.period_set_name)' ||
' AND (SELECT start_date' ||
' FROM gl_periods' ||
' WHERE period_name =''' ||
l_End_Period || '''' ||
' AND period_set_name = led.period_set_name)' ||
' AND gp.period_set_name = led.period_set_name' ||
' AND gp.period_type = led.accounted_period_type' ||
' AND led.ledger_id = jeh.ledger_id' ||
' AND nvl(jel.global_attribute2' ||
' ,''U'') <> ''P''';
update gl_je_lines
set global_attribute2='P'
where je_header_id=l_header_id
and je_line_num=l_line_num;
SELECT distinct Je_Header_Id
FROM ja_cn_journal_lines
WHERE period_name=p_Period_Name
AND status = 'U'
order by Je_Header_Id;
SELECT DISTINCT Je_Header_Id, Journal_Number
FROM Ja_Cn_Journal_Lines Jl
WHERE Je_Header_Id = l_Je_Header_Id
AND Journal_Number IS NOT NULL
AND Company_Segment IN
(SELECT bsv.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt bsv
WHERE Legal_Entity_Id = l_Legal_Entity_Id
and ledger_id = l_ledger_id);
UPDATE Ja_Cn_Journal_Lines jop
SET Journal_Number = l_Journal_Number
WHERE Je_Header_Id = l_Je_Header_Id
AND Journal_Number IS NULL
AND Company_Segment IN
(SELECT bsv.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt bsv
WHERE Legal_Entity_Id = l_Legal_Entity_Id
and ledger_id = l_ledger_id);
l_Journal_Number := Ja_Cn_Update_Jl_Seq_Pkg.Fetch_Jl_Seq(p_Legal_Entity_Id => l_Legal_Entity_Id,
p_ledger_id=>l_ledger_id,
p_Period_Name => l_Period_Name);
UPDATE Ja_Cn_Journal_Lines
SET Journal_Number = l_Journal_Number
WHERE Je_Header_Id = l_Je_Header_Id
AND Company_Segment IN
(SELECT bsv.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt bsv
WHERE Legal_Entity_Id = l_Legal_Entity_Id
and ledger_id = l_ledger_id);
SELECT Project_Source_Flag
FROM Ja_Cn_Sub_Acc_Sources_All ja,gl_ledgers gl
where ja.chart_of_accounts_id=gl.chart_of_accounts_id
and gl.ledger_id=l_ledger_id;
SELECT Fsav.Application_Column_Name
FROM Fnd_Id_Flex_Segments Fifs,
Fnd_Segment_Attribute_Values Fsav,
Gl_Ledgers Led
WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
AND Fsav.Segment_Attribute_Type = 'FA_COST_CTR'
AND Fsav.Attribute_Value = 'Y'
AND Fifs.Application_Id = 101
and fsav.id_flex_code = fifs.id_flex_code
and fsav.id_flex_code = 'GL#'
AND Fifs.Application_Id = Fsav.Application_Id
AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
AND Led.Ledger_Id = l_Ledger_Id;
SELECT Fsav.Application_Column_Name
FROM Fnd_Id_Flex_Segments Fifs,
Fnd_Segment_Attribute_Values Fsav,
Gl_Ledgers Led
WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
AND Fsav.Segment_Attribute_Type = 'GL_GLOBAL'
AND Fsav.Attribute_Value = 'Y'
AND Fifs.Application_Id = 101
and fifs.id_flex_code = fsav.id_flex_code
and fifs.id_flex_code = 'GL#'
AND Fifs.Application_Id = Fsav.Application_Id
AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
AND Led.Ledger_Id = l_Ledger_Id;
SELECT led.bal_seg_column_name
INTO l_Company_Column_Name
from gl_ledgers led
where Led.Ledger_Id = l_Ledger_Id;
SELECT Fsav.Application_Column_Name
INTO l_Account_Column_Name
FROM Fnd_Id_Flex_Segments Fifs,
Fnd_Segment_Attribute_Values Fsav,
Gl_Ledgers Led
WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
AND Fsav.Segment_Attribute_Type = 'GL_ACCOUNT'
AND Fsav.Attribute_Value = 'Y'
AND Fifs.Application_Id = 101
and fsav.id_flex_code = fifs.id_flex_code
and fsav.id_flex_code = 'GL#'
AND Fifs.Application_Id = Fsav.Application_Id
AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
AND Led.Ledger_Id = l_Ledger_Id;
l_Create_View_Sql := 'select GCC.CODE_COMBINATION_ID,led.ledger_id,' ||
'gcc.' || l_Company_Column_Name ||
' company_segment,';
SELECT Coa_Segment
INTO l_Project_Column_Name
FROM Ja_Cn_Sub_Acc_Sources_All ja,gl_ledgers gl
WHERE ja.Chart_Of_Accounts_Id =gl.Chart_Of_Accounts_Id
and gl.ledger_id=l_ledger_id;
SELECT Fsav.Application_Column_Name
INTO l_Second_Track_Col_Name
FROM Fnd_Id_Flex_Segments Fifs,
Fnd_Segment_Attribute_Values Fsav,
Gl_Ledgers Led
WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
AND Fsav.Segment_Attribute_Type = 'GL_SECONDARY_TRACKING'
AND Fsav.Attribute_Value = 'Y'
AND Fifs.Application_Id = 101
and fifs.id_flex_code = fsav.id_flex_code
and fsav.id_flex_code = 'GL#'
AND Fifs.Application_Id = Fsav.Application_Id
AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
AND Led.Ledger_Id = l_Ledger_Id;
SELECT JOURNAL_GROUP,
JE_LINE_NUM,
STATUS,
fnd.description
FROM JA_CN_ITEM_INTERFACE JA,FND_LOOKUP_VALUES FND
WHERE FND.meaning=JA.status
AND FND.LANGUAGE = userenv('LANG')
AND FND.lookup_type='JA_CN_ITEM_ERROR_CODE'
AND LEDGER_ID = p_ledger_id
AND STATUS IS NOT NULL
AND period_name in(
SELECT Gp.Period_Name
FROM Gl_Periods Gp, Gl_Ledgers Led
WHERE Led.Ledger_Id = p_Ledger_Id
AND Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Start_Date BETWEEN
(SELECT Start_Date
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Period_Name = L_PERIOD_FROM )
AND (SELECT Start_Date
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Period_Name = P_PERIOD_TO)
)
ORDER BY JOURNAL_GROUP,JA.JE_LINE_NUM;
SELECT Gp.Period_Name
INTO l_period_from
FROM Gl_Periods Gp, Gl_Ledgers Led
WHERE Led.Ledger_Id = p_ledger_id
AND Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Start_Date IN
(SELECT MIN(Start_Date)
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type);
SELECT name
INTO l_le_name
FROM XLE_ENTITY_PROFILES
WHERE legal_entity_id=p_legal_entity_id;
SELECT name
INTO l_ledger_name
FROM gl_ledgers
WHERE ledger_id=p_ledger_id;
SELECT XMLELEMENT("P_LEDGER_NAME",l_ledger_name) INTO l_xml_item FROM dual;
SELECT XMLELEMENT("P_LEGAL_NAME",l_le_name ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT(l_xml_parameter,l_xml_item) INTO l_xml_parameter FROM dual;
SELECT XMLELEMENT("PERIOD_START",P_PERIOD_FROM) INTO l_xml_item FROM dual;
SELECT XMLCONCAT(l_xml_parameter,l_xml_item) INTO l_xml_parameter FROM dual;
SELECT XMLELEMENT("PERIOD_END",P_PERIOD_TO) INTO l_xml_item FROM dual;
SELECT XMLCONCAT(l_xml_parameter,l_xml_item) INTO l_xml_parameter FROM dual;
SELECT XMLCONCAT(l_xml_report,l_xml_parameter) INTO l_xml_report FROM dual;
SELECT XMLELEMENT("JOURNAL_GROUP",L_JOURNAL_GROUP) INTO l_xml_item FROM dual;
SELECT XMLELEMENT("JE_LINE_NUM",L_JE_LINE_NUM) INTO l_xml_item FROM dual;
SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
SELECT XMLELEMENT("STATUS_CODE",L_STATUS_CODE) INTO l_xml_item FROM dual;
SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
SELECT XMLELEMENT("ERROR_MESSAGE",L_DESCRIPTION) INTO l_xml_item FROM dual;
SELECT XMLCONCAT(l_xml_line,l_xml_item) INTO l_xml_line FROM dual;
SELECT XMLELEMENT("ERROR_JOURNAL",l_xml_line) INTO l_xml_line FROM dual;
SELECT XMLCONCAT(l_xml_report,l_xml_line) INTO l_xml_report FROM dual;
SELECT XMLELEMENT( "REPORT",l_xml_report) INTO l_xml_root FROM dual;
select distinct je_header_id
from ja_cn_journal_lines
where status='U';
SELECT Gp.Period_Name
FROM Gl_Periods Gp, Gl_Ledgers Led
WHERE Led.Ledger_Id = l_Ledger_Id
AND Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Start_Date BETWEEN
(SELECT Start_Date
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Period_Name = l_period_from )
AND (SELECT Start_Date
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Period_Name = l_period_to)
ORDER BY Gp.Start_Date;
select third_party_number,third_party_type
from ja_cn_journal_lines
where status='U'
and third_party_number is not null
for update;
select project_number,project_source
from ja_cn_journal_lines
where status='U'
and project_number is not null
for update;
select PERSONNEL_ID, DEFAULT_EFFECTIVE_DATE
from ja_cn_journal_lines
where status='U'
and PERSONNEL_ID is not null;
select journal_creator, DEFAULT_EFFECTIVE_DATE
from ja_cn_journal_lines
where status='U'
and journal_creator is not null;
select journal_approver, DEFAULT_EFFECTIVE_DATE
from ja_cn_journal_lines
where status='U'
and journal_approver is not null;
select journal_poster, DEFAULT_EFFECTIVE_DATE
from ja_cn_journal_lines
where status='U'
and journal_poster is not null;
SELECT Gp.Period_Name
INTO l_period_from
FROM Gl_Periods Gp, Gl_Ledgers Led
WHERE Led.Ledger_Id = l_ledger_id
AND Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type
AND Gp.Start_Date IN
(SELECT MIN(Start_Date)
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type);
SELECT COUNT(*)
INTO invalid_period_num
FROM Gl_Period_Statuses GP
WHERE GP.application_id = 101
AND GP.ledger_id = p_ledger_id
AND GP.start_date >=
(SELECT START_DATE
FROM Gl_Period_Statuses
WHERE LEDGER_ID = l_ledger_id
AND PERIOD_NAME = l_period_start
AND APPLICATION_ID = 101)
AND GP.end_date <=
(SELECT END_DATE
FROM Gl_Period_Statuses
WHERE LEDGER_ID = l_ledger_id
AND PERIOD_NAME = l_period_end
AND APPLICATION_ID = 101)
AND (GP.closing_status <> 'P'
OR GP.closing_status <> 'C');
SELECT chart_of_accounts_id
INTO l_coa
FROM gl_ledgers
WHERE ledger_id = P_LEDGER_ID;
SELECT Fsav.Application_Column_Name
INTO l_Company_Column_Name
FROM Fnd_Id_Flex_Segments Fifs,
Fnd_Segment_Attribute_Values Fsav,
Gl_Ledgers Led
WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
AND Fsav.Segment_Attribute_Type = 'GL_BALANCING'
AND Fsav.Attribute_Value = 'Y'
AND Fifs.Application_Id = 101
and fsav.id_flex_code = fifs.id_flex_code
and fsav.id_flex_code = 'GL#'
AND Fifs.Application_Id = Fsav.Application_Id
AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
AND Led.Ledger_Id = l_ledger_id;
SELECT Fsav.Application_Column_Name
INTO l_Account_Column_Name
FROM Fnd_Id_Flex_Segments Fifs,
Fnd_Segment_Attribute_Values Fsav,
Gl_Ledgers Led
WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
AND Fsav.Segment_Attribute_Type = 'GL_ACCOUNT'
AND Fsav.Attribute_Value = 'Y'
AND Fifs.Application_Id = 101
and fsav.id_flex_code = fifs.id_flex_code
and fsav.id_flex_code = 'GL#'
AND Fifs.Application_Id = Fsav.Application_Id
AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
AND Led.Ledger_Id = l_ledger_id;
SELECT Fsav.Application_Column_Name
into l_Cost_CRT_Column_Name
FROM Fnd_Id_Flex_Segments Fifs,
Fnd_Segment_Attribute_Values Fsav,
Gl_Ledgers Led
WHERE Fifs.Id_Flex_Num = Fsav.Id_Flex_Num
AND Fifs.Application_Column_Name = Fsav.Application_Column_Name
AND Fsav.Segment_Attribute_Type = 'FA_COST_CTR'
AND Fsav.Attribute_Value = 'Y'
AND Fifs.Application_Id = 101
and fsav.id_flex_code = fifs.id_flex_code
and fsav.id_flex_code = 'GL#'
AND Fifs.Application_Id = Fsav.Application_Id
AND Led.Chart_Of_Accounts_Id = Fifs.Id_Flex_Num
AND Led.Ledger_Id = l_Ledger_Id;
l_sql:='insert into ja_cn_journal_lines
(je_header_id,
SET_OF_BOOKS_ID,
legal_entity_id,
JOURNAL_NUMBER,
JE_CATEGORY,
DEFAULT_EFFECTIVE_DATE,
PERIOD_NAME ,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
JE_LINE_NUM ,
DESCRIPTION ,
COMPANY_SEGMENT ,
CODE_COMBINATION_ID ,
COST_CENTER,
THIRD_PARTY_NUMBER ,
PERSONNEL_ID,
PROJECT_NUMBER ,
ACCOUNT_SEGMENT ,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
STATUS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROJECT_SOURCE,
POPULATE_CODE ,
THIRD_PARTY_TYPE ,
JOURNAL_CREATOR ,
JOURNAL_APPROVER,
JOURNAL_POSTER,
LEDGER_ID
)
select journal_group,
ledger_id,
LEGAL_ENTITY_ID,
null,
JE_CATEGORY,
DEFAULT_EFFECTIVE_DATE,
PERIOD_NAME ,
CURRENCY_CODE,
CURRENCY_CONVERSION_RATE,
JE_LINE_NUM ,
DESCRIPTION ,'||
l_Company_Column_Name ||
',CODE_COMBINATION_ID,'||
l_Cost_CRT_Column_Name ||
',THIRD_PARTY_NUMBER
,PERSONNEL_ID
,PROJECT_NUMBER,'||
l_Account_Column_Name||
',ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
''U'',
Fnd_Global.User_Id,
SYSDATE,
Fnd_Global.User_Id,
SYSDATE,
Fnd_Global.Login_Id,
PROJECT_SOURCE,
''IMPORT'',
THIRD_PARTY_TYPE ,
JOURNAL_CREATOR_ID ,
JOURNAL_APPROVER_ID,
JOURNAL_POSTER_ID,
LEDGER_ID
from ja_cn_item_interface
where status =''S''';
select employee_number--, Last_Name || First_Name Full_Name
into l_PERSONNEL_NUMBER
from Per_All_People_f
where person_id = l_PERSONNEL_id
AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
update ja_cn_journal_lines
set personnel_number = l_PERSONNEL_NUMBER
where personnel_id = l_PERSONNEL_id ;
select Last_Name || First_Name Full_Name
into l_creator
from Per_All_People_f
where person_id = to_number(l_creator_id)
AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
update ja_cn_journal_lines
set journal_creator = l_creator
where journal_creator = l_creator_id;
select Last_Name || First_Name Full_Name
into l_approver
from Per_All_People_f
where person_id = to_number(l_approver_id )
AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
update ja_cn_journal_lines
set journal_approver = l_approver
where journal_approver = l_APPROVER_id;
select Last_Name || First_Name Full_Name
into l_poster
from Per_All_People_f
where person_id = to_number(l_poster_id)
AND EFFECTIVE_START_DATE<=L_EFFECTIVE_DATE
AND EFFECTIVE_END_DATE>=L_EFFECTIVE_DATE;
update ja_cn_journal_lines
set journal_poster = l_poster
where journal_poster = l_poster_id;
SELECT ja_cn_item_interface_s.NEXTVAL into l_header_id FROM Dual;
update ja_cn_journal_lines
set je_header_id = l_header_id
where status = 'U'
and je_header_id = l_journal_group;
select party_id
into l_third_party_id
from Hz_Parties
where Party_Number =l_THIRD_PARTY_NUMBER;
select vendor_id
into l_third_party_id
from ap_suppliers
where Segment1 =l_THIRD_PARTY_NUMBER;
update ja_cn_journal_lines
set third_party_id=l_third_party_id
where current of c_third_party;
select project_id
into l_project_id
from PA_PROJECTS_ALL
where SEGMENT1=l_PROJECT_NUMBER;
select flex_value_id
into l_project_id
from FND_FLEX_VALUES ffv,
FND_ID_FLEX_SEGMENTS fifs
where ffv.flex_value_set_id=fifs.flex_value_set_id
and fifs.id_flex_code='GL#'
and fifs.id_flex_num=l_coa
and (fifs.application_column_name =(select coa_segment
from ja_cn_sub_acc_sources_all
where chart_of_accounts_id=l_coa
and coa_segment is not null)
or fifs.application_column_name =(select history_coa_segment
from ja_cn_sub_acc_sources_all
where chart_of_accounts_id=l_coa
and history_coa_segment is not null) )
AND flex_value = l_PROJECT_NUMBER ;
update ja_cn_journal_lines
set project_id=l_project_id
where current of c_project;