[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT Jop.Je_Header_Id
FROM Ja_Cn_Journals_Of_Period Jop, Gl_Je_Sources_Tl Gjs
WHERE Jop.Request_Id = l_Request_Id
AND Gjs.Je_Source_Name = Jop.Je_Source
AND Gjs.Source_Lang = Userenv('LANG')
AND Gjs.LANGUAGE = Userenv('LANG')
AND Gjs.Journal_Approval_Flag = 'Y';
SELECT Jeb.Default_Period_Name, Jeb.NAME, Jeb.Approval_Status_Code
FROM Gl_Je_Headers Jeh, Gl_Je_Batches Jeb
WHERE Jeb.Je_Batch_Id = Jeh.Je_Batch_Id
AND Jeh.Je_Header_Id = l_Je_Header_Id;
SELECT d.Text_Value
FROM Wf_Items t, Wf_Item_Attribute_Values d
WHERE d.Item_Key = t.Item_Key
AND d.NAME = 'APPROVER_NAME'
AND t.User_Key = l_Batch_Name
AND d.Item_Type = 'GLBATCH'
AND t.Begin_Date IN
(SELECT MAX(It.Begin_Date)
FROM Wf_Items It,
Wf_Item_Attribute_Values T1,
Wf_Item_Attribute_Values t
WHERE It.User_Key = l_Batch_Name
AND It.Item_Key = t.Item_Key
AND T1.Item_Type = 'GLBATCH'
AND T1.Item_Key = t.Item_Key
AND t.Item_Type = 'GLBATCH'
AND t.NAME = 'BATCH_NAME'
AND t.Text_Value = l_Batch_Name
AND T1.NAME = 'PERIOD_NAME'
AND T1.Text_Value = l_Period_Name);
SELECT Last_Name || First_Name Full_Name
FROM Per_All_People_f
WHERE Person_Id =
(SELECT Employee_Id FROM Fnd_User WHERE User_Name = l_Approver);
UPDATE Ja_Cn_Journal_Lines_Req t
SET t.Journal_Approver = l_Approver_Name
WHERE t.Je_Header_Id = l_Je_Header_Id;
SELECT Flv.Lookup_Code
FROM Fnd_Lookup_Values Flv
WHERE Flv.LANGUAGE = Userenv('LANG')
AND Flv.Lookup_Type = p_Lookup_Type
AND Flv.Meaning = p_Lookup_Meaning
AND Flv.View_Application_Id = p_View_Application_Id
AND Flv.Security_Group_Id = p_Security_Group_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 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;
l_Populate_Journal_Sql := 'INSERT INTO ja_cn_journals_of_period' ||
'(je_header_id' || ',je_line_num' ||
',period_name' || ',je_category' ||
',je_source' || ',legal_entity_id' ||
',request_id,effective_date)' ||
'SELECT /*+index(jop,ja_cn_journals_of_period_n3)+*/ ' ||
' jeh.je_header_id' ||
' ,jel.je_line_num' ||
' ,jeh.period_name' ||
' ,jeh.je_category' ||
' ,jeh.je_source' ||
' ,bsv.legal_entity_id' || ',' ||
l_Request_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''';
SELECT led.bal_seg_column_name
from gl_ledgers led
where 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 = '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
WHERE Chart_Of_Accounts_Id = l_Chart_Of_Accounts_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 Gp.Period_Name
INTO p_Start_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 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 Gp.Period_Name
INTO p_End_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 IN
(SELECT MAX(Start_Date)
FROM Gl_Periods Gp
WHERE Led.Period_Set_Name = Gp.Period_Set_Name
AND Led.Accounted_Period_Type = Gp.Period_Type);
SELECT /*+ index(jel,gl_je_lines_u1)*/
Req.Je_Header_Id, Req.Je_Line_Num
FROM (SELECT SUM(Nvl(Req.Accounted_Dr, 0) - Nvl(Req.Accounted_Cr, 0)) Accounted_Amount,
SUM(Nvl(Req.Entered_Dr, 0) - Nvl(Req.Entered_Cr, 0)) Entered_Amount,
Req.Je_Header_Id,
Req.Je_Line_Num
FROM Ja_Cn_Journal_Lines_Req Req
WHERE Req.Request_Id = l_Request_Id
GROUP BY Req.Je_Header_Id, Req.Je_Line_Num) Req,
Gl_Je_Lines Jel
WHERE (Nvl(Jel.Accounted_Dr, 0) - Nvl(Jel.Accounted_Cr, 0) <>
Req.Accounted_Amount OR
Nvl(Jel.Entered_Dr, 0) - Nvl(Jel.Entered_Cr, 0) <>
Req.Entered_Amount)
AND Jel.Je_Line_Num = Req.Je_Line_Num
AND Jel.Je_Header_Id = Req.Je_Header_Id;
DELETE FROM Ja_Cn_Journal_Lines_Req
WHERE Je_Header_Id = l_Je_Header_Id
AND Je_Line_Num = l_Je_Line_Num;
INSERT INTO Ja_Cn_Journal_Lines_Req
(Je_Header_Id,
Ledger_Id,
Legal_Entity_Id,
Journal_Number,
Je_Category,
Default_Effective_Date,
Period_Name,
Currency_Code,
Currency_Conversion_Rate,
Je_Line_Num,
Line_Number,
Description,
Company_Segment,
Code_Combination_Id,
Cost_Center,
Third_Party_Id,
Third_Party_Number,
Personnel_Id,
Personnel_Number,
Project_Number,
Project_Source,
Account_Segment,
Entered_Dr,
Entered_Cr,
Accounted_Dr,
Accounted_Cr,
Status,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Populate_Code,
Request_Id,
Journal_Created_By,
Journal_Posted_By)
SELECT Jel.Je_Header_Id Je_Header_Id,
Jeh.Ledger_Id Ledger_Id,
Jop.Legal_Entity_Id Legal_Entity_Id,
To_Number(NULL) Journal_Number,
Jeh.Je_Category Je_Category,
Jeh.Default_Effective_Date Default_Effective_Date,
Jeh.Period_Name Period_Name,
Jeh.Currency_Code Currency_Code,
Jeh.Currency_Conversion_Rate Currency_Conversion_Rate,
Jel.Je_Line_Num Je_Line_Num,
To_Number(NULL) Line_Number,
Nvl(Jel.Description, Jeh.Description) Description,
Jcc.Company_Segment Company_Segment,
Jcc.Code_Combination_Id Code_Combination_Id,
Jcc.Cost_Segment Cost_Segment,
To_Number(NULL) Third_Party_Id,
To_Char(NULL) Third_Party_Number,
To_Number(NULL) Personnel_Id,
To_Char(NULL) Personnel_Number,
Decode(Nvl(l_Project_Option, 'N'),
'N',
To_Char(NULL),
'COA',
Jcc.Project_Number,
To_Char(NULL)) Project_Number,
Nvl(l_Project_Option, 'N') Project_Source,
Jcc.Account_Segment Account_Segment,
Jel.Entered_Dr,
Jel.Entered_Cr,
Jel.Accounted_Dr,
Jel.Accounted_Cr,
'U' Status,
Fnd_Global.User_Id Created_Gy,
SYSDATE Creation_Date,
Fnd_Global.User_Id Last_Updated_By,
SYSDATE Last_Update_Date,
Fnd_Global.Login_Id Last_Update_Login,
'NO ITEMIZATION',
l_Request_Id,
Jeh.Created_By,
Jeb.Posted_By --added by lyb, for bug for bug 6654734
-- Decode(Nvl(Jeh.Accrual_Rev_Status, 'N'),
-- 'R',
-- To_Number(NULL),
-- Jeh.Last_Updated_By)
FROM Gl_Je_Headers Jeh,
Gl_Je_Lines Jel,
Ja_Cn_Code_Combination_v Jcc,
Ja_Cn_Journals_Of_Period Jop,
Gl_Je_Batches Jeb--added by lyb, for bug 6654734
WHERE Jeh.Je_Header_Id = Jel.Je_Header_Id
AND Jcc.Ledger_Id = Jeh.Ledger_Id
AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
AND Jeh.Je_Header_Id = Jop.Je_Header_Id
AND Jel.Je_Line_Num = Jop.Je_Line_Num
AND Jop.Request_Id = l_Request_Id
AND Jel.Je_Line_Num NOT IN
(SELECT Je_Line_Num
FROM Ja_Cn_Journal_Lines_Req
WHERE Je_Header_Id = Jeh.Je_Header_Id)
AND Jeb.Je_Batch_Id=Jeh.Je_Batch_Id;--added by lyb, for bug 6654734
SELECT Je_Header_Id
FROM (SELECT DISTINCT Effective_Date, Je_Header_Id
FROM Ja_Cn_Journals_Of_Period
WHERE Request_Id = l_Request_Id
AND Period_Name = l_Period_Name)
ORDER BY Effective_Date ASC, Je_Header_Id ASC;
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);
l_Insertsql Dbms_Sql.Varchar2s;
SELECT Sas.Project_Source_Flag,
Sas.Project_Ac_Code,
Sas.Project_Ac_Detail_Code,
Sas.Ac_Grouping_Order--added for bug 6669665
FROM Ja_Cn_Sub_Acc_Sources_All Sas
WHERE Sas.Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;
l_Insertsql:='*/
if nvl(l_Project_Source_Flag, 'N') = 'PA' then
--get the detail code's group order in the analytical criterion
--deleted for bug 6669665
-- SELECT Dtl.Grouping_Order
-- INTO l_Grouping_Order
-- FROM Xla_Analytical_Dtls_b Dtl
-- WHERE Dtl.Analytical_Criterion_Code = l_Project_Ac_Code
-- AND Dtl.Analytical_Detail_Code = l_Project_Ac_Detail_Code;
l_sql:='INSERT INTO Ja_Cn_Journal_Lines_Req';
l_sql:=l_sql||' Last_Updated_By,';
l_sql:=l_sql||' Last_Update_Date,';
l_sql:=l_sql||' Last_Update_Login,';
l_sql:=l_sql||' SELECT /*+index(ael,xla_ae_lines_n4)+*/';
l_sql:=l_sql||' Fnd_Global.User_Id Last_Updated_By,';
l_sql:=l_sql||' SYSDATE Last_Update_Date,';
l_sql:=l_sql||' Fnd_Global.Login_Id Last_Update_Login,';
l_sql:=l_sql||' Jeh.Last_Updated_By)';*/
INSERT INTO Ja_Cn_Journal_Lines_Req
(Je_Header_Id,
Ledger_Id,
Legal_Entity_Id,
Journal_Number,
Je_Category,
Default_Effective_Date,
Period_Name,
Currency_Code,
Currency_Conversion_Rate,
Je_Line_Num,
Line_Number,
Description,
Company_Segment,
Code_Combination_Id,
Cost_Center,
Third_Party_Id,
Third_Party_Type,
Third_Party_Number,
Personnel_Id,
Personnel_Number,
Project_Number,
Project_Source,
Account_Segment,
Entered_Dr,
Entered_Cr,
Accounted_Dr,
Accounted_Cr,
Status,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Populate_Code,
Request_Id,
Journal_Created_By,
Journal_Posted_By)
SELECT /*+index(ael,xla_ae_lines_n4)+*/
Jel.Je_Header_Id Je_Header_Id
,Jeh.Ledger_Id Ledger_Id
,Jop.Legal_Entity_Id Legal_Entity_Id
,To_Number(NULL) Journal_Number
,Jeh.Je_Category Je_Category
,Jeh.Default_Effective_Date Default_Effective_Date
,Jeh.Period_Name Period_Name
,Jeh.Currency_Code Currency_Code
,Jeh.Currency_Conversion_Rate Currency_Conversion_Rate
,Jel.Je_Line_Num Je_Line_Num
,To_Number(NULL) Line_Number
,Nvl(Ael.Description,
Nvl(Jel.Description,
Jeh.Description)) Description
,Jcc.Company_Segment Company_Segment
,Jcc.Code_Combination_Id Code_Combination_Id
,Jcc.Cost_Segment Cost_Segment
,Decode(Pay_Group_Lookup_Code,
'EMPLOYEE',
To_Number(NULL),
Ael.Party_Id) Third_Party_Id
,Decode(Pay_Group_Lookup_Code,
'EMPLOYEE',
To_Char(NULL),
Ael.Party_Type_Code) Third_Party_Type
,Decode(Nvl(Ael.Party_Type_Code,
'D'),
'C',
Part.Party_Number,
'S',
Sup.Segment1,
To_Char(NULL)) Third_Party_Number
,Decode(Pay_Group_Lookup_Code,
'EMPLOYEE',
Sup.Employee_Id,
To_Number(NULL)) Personnel_Id
,To_Char(NULL) Personnel_Number
,Decode(Nvl(l_Project_Option,
'N'),
'N',
To_Char(NULL),
'COA',
Jcc.Project_Number,
To_Char(NULL)) Project_Number
,Nvl(l_Project_Option,
'N') Project_Source
,Jcc.Account_Segment Account_Segment
,Ael.Entered_Dr
,Ael.Entered_Cr
,Ael.Accounted_Dr
,Ael.Accounted_Cr
,'U' Status
,Fnd_Global.User_Id Created_Gy
,SYSDATE Creation_Date
,Fnd_Global.User_Id Last_Updated_By
,SYSDATE Last_Update_Date
,Fnd_Global.Login_Id Last_Update_Login
,'FSAH'
,l_Request_Id
,Jeh.Created_By
,jeb.posted_by
----deleted by lyb, for bug 6654734
-- ,Decode(Nvl(Jeh.Accrual_Rev_Status,
-- 'N'),
-- 'R',
-- To_Number(NULL),
-- Jeh.Last_Updated_By)
FROM Gl_Je_Lines Jel
,Gl_Je_Headers Jeh
,Gl_Je_Batches Jeb --added by lyb, for bug 6654734
,Xla_Ae_Lines Ael
,Xla_Ae_Headers Aeh
,Gl_Import_References Gir
,Ja_Cn_Code_Combination_v Jcc
,Ap_Suppliers Sup
,
-- per_all_people_f per,
Ja_Cn_Journals_Of_Period Jop
,Hz_Cust_Accounts Cust
,Hz_Parties Part
WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
AND Jeb.Je_Batch_Id=jeh.je_batch_id --added by lyb, for bug 6654734
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Sup.Vendor_Id(+) = Ael.Party_Id
-- and sup.pay_group_lookup_code='EMPLOYEE'
-- AND nvl(pv.employee_id, -1) = per.person_id(+)
AND Gir.Je_Line_Num = Jel.Je_Line_Num
AND Jop.Je_Header_Id = Jel.Je_Header_Id
AND Jop.Je_Line_Num = Jel.Je_Line_Num
AND Jcc.Ledger_Id = Jeh.Ledger_Id
AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
AND Jop.Request_Id = l_Request_Id
AND Cust.Cust_Account_Id(+) = Ael.Party_Id
AND Cust.Party_Id = Part.Party_Id(+);
SELECT Project_Source_Flag
FROM Ja_Cn_Sub_Acc_Sources_All
WHERE Chart_Of_Accounts_Id = l_Chart_Of_Accounts_Id;
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_Start_Period_Name)
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_End_Period_Name)
ORDER BY Gp.Start_Date;
SELECT Ja_Cn_Journal_Lines_Req_s.NEXTVAL INTO l_Request_Id FROM Dual;
UPDATE JA_CN_JOURNAL_LINES_REQ REQ
SET REQ.JOURNAL_CREATOR = (SELECT LAST_NAME || FIRST_NAME FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID =
(SELECT EMPLOYEE_ID
FROM FND_USER
WHERE USER_ID =
REQ.JOURNAL_CREATED_BY)
AND REQ.DEFAULT_EFFECTIVE_DATE BETWEEN
EFFECTIVE_START_DATE AND
EFFECTIVE_END_DATE)
WHERE REQ.REQUEST_ID = L_REQUEST_ID
AND REQ.JE_HEADER_ID > 0;
UPDATE JA_CN_JOURNAL_LINES_REQ REQ
SET REQ.JOURNAL_POSTER = (SELECT LAST_NAME || FIRST_NAME FULL_NAME
FROM PER_ALL_PEOPLE_F
WHERE PERSON_ID =
(SELECT EMPLOYEE_ID
FROM FND_USER
WHERE USER_ID =
REQ.JOURNAL_POSTED_BY)
AND REQ.DEFAULT_EFFECTIVE_DATE BETWEEN
EFFECTIVE_START_DATE AND
EFFECTIVE_END_DATE)
WHERE REQ.REQUEST_ID = L_REQUEST_ID
AND REQ.JE_HEADER_ID > 0;
UPDATE Gl_Je_Lines Jel
SET Jel.Global_Attribute2 = 'P'
WHERE Jel.Je_Line_Num IN
(SELECT Je_Line_Num
FROM Ja_Cn_Journals_Of_Period
WHERE Request_Id = l_Request_Id
AND Je_Header_Id = Jel.Je_Header_Id)
AND Jel.Je_Header_Id IN
(SELECT Je_Header_Id
FROM Ja_Cn_Journals_Of_Period
WHERE Request_Id = l_Request_Id);
INSERT INTO Ja_Cn_Journal_Lines
(Je_Header_Id,
Ledger_Id,
Legal_Entity_Id,
Journal_Number,
Je_Category,
Default_Effective_Date,
Period_Name,
Currency_Code,
Currency_Conversion_Rate,
Je_Line_Num,
Line_Number,
Description,
Company_Segment,
Code_Combination_Id,
Cost_Center,
Third_Party_Id,
Third_Party_Number,
Third_Party_Type,
Personnel_Id,
Personnel_Number,
Project_Number,
Project_Source,
Account_Segment,
Entered_Dr,
Entered_Cr,
Accounted_Dr,
Accounted_Cr,
Status,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Populate_Code,
Journal_Creator,
Journal_Approver,
Journal_Poster)
SELECT Je_Header_Id,
Ledger_Id,
Legal_Entity_Id,
Journal_Number,
Je_Category,
Default_Effective_Date,
Period_Name,
Currency_Code,
Currency_Conversion_Rate,
Je_Line_Num,
Line_Number,
Description,
Company_Segment,
Code_Combination_Id,
Cost_Center,
Third_Party_Id,
Third_Party_Number,
Third_Party_Type,
Personnel_Id,
Personnel_Number,
Project_Number,
Project_Source,
Account_Segment,
Entered_Dr,
Entered_Cr,
Accounted_Dr,
Accounted_Cr,
Status,
Created_By,
Creation_Date,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Populate_Code,
Journal_Creator,
Journal_Approver,
Journal_Poster
FROM Ja_Cn_Journal_Lines_Req
WHERE Request_Id = l_Request_Id
AND Je_Header_Id > 0;
/* DELETE FROM Ja_Cn_Journals_Of_Period
WHERE Request_Id = l_Request_Id
AND Je_Header_Id > 0;*/
DELETE FROM Ja_Cn_Journal_Lines_Req
WHERE Request_Id = l_Request_Id
AND Je_Header_Id > 0;
DELETE FROM Ja_Cn_Journals_Of_Period
WHERE Request_Id = l_Request_Id
AND Je_Header_Id > 0;
DELETE FROM Ja_Cn_Journal_Lines_Req
WHERE Request_Id = l_Request_Id
AND Je_Header_Id > 0;