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_attribute7' || --updated for CNAOV2 solution by chaoqun on 13-May-2010
' ,''U'') <> ''P''' ||
' AND jeh.ACTUAL_FLAG <> ''B''' || --Added for fixing bug#10316599 01-December-2010
' AND jeh.ACTUAL_FLAG <> ''E'''; --Added for fixing bug#8670470 by Chaoqun on 09-JUL-2009
SELECT NULL CONTEXT_CODE,
FLV.LOOKUP_CODE SUBSIDIARY_SEGMENT_CODE
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'JA_CN_SUB_ACC_SEGMENT'
AND FLV.LANGUAGE = USERENV('LANG')
AND NOT EXISTS(
SELECT * FROM JA_CN_SUB_ACC_MAPPING SAM,
GL_LEDGERS LED
WHERE SAM.SOURCES_CODE = 'COA'
AND SAM.CHART_OF_ACCOUNTS_ID = LED.CHART_OF_ACCOUNTS_ID
AND LED.LEDGER_ID = l_Ledger_Id
AND SAM.SUBSIDIARY_SEGMENT_CODE = FLV.LOOKUP_CODE)
UNION
SELECT SAM.CONTEXT_CODE,
SAM.SUBSIDIARY_SEGMENT_CODE
FROM JA_CN_SUB_ACC_MAPPING SAM,
GL_LEDGERS LED
WHERE SAM.SOURCES_CODE = 'COA'
AND SAM.CHART_OF_ACCOUNTS_ID = LED.CHART_OF_ACCOUNTS_ID
AND LED.LEDGER_ID = l_Ledger_Id;
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;
l_Insert_Header_Sql VARCHAR2(4000) := '';
l_Insert_Values_Sql VARCHAR2(4000) := '';
SELECT SAM.SUBSIDIARY_SEGMENT_CODE,
SAM.SOURCES_CODE,
SAM.CONTEXT_CODE
FROM JA_CN_SUB_ACC_MAPPING SAM
WHERE SAM.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_Id;
Fnd_Log.STRING(l_Proc_Level, --Updated by Chaoqun for CNAOV2
l_Module_Prefix || '.' || l_Proc_Name || '.prameter',
'p_chart_of_accounts_id:' || p_chart_of_accounts_id);
l_Insert_Header_Sql := l_Insert_Header_Sql || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
l_Insert_Values_Sql := l_Insert_Values_Sql || 'jcc.' ||
l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
l_Insert_Values_Sql := l_Insert_Values_Sql ||
'To_Char(NULL) ' || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
' (Select nvl(DECODE(DFFA.ATTRIBUTE_COLUMN, ''ATTRIBUTE1'',TRLHDR.attribute1, ''ATTRIBUTE2'',TRLHDR.attribute2,'||
' ''ATTRIBUTE3'',TRLHDR.attribute3, ''ATTRIBUTE4'',TRLHDR.attribute4, ''ATTRIBUTE5'',TRLHDR.attribute5,'||
' ''ATTRIBUTE6'',TRLHDR.attribute6, ''ATTRIBUTE7'',TRLHDR.attribute7, ''ATTRIBUTE8'',TRLHDR.attribute8,'||
' ''ATTRIBUTE9'',TRLHDR.attribute9, ''ATTRIBUTE10'',TRLHDR.attribute10, ''ATTRIBUTE11'',TRLHDR.attribute11,'||
' ''ATTRIBUTE12'',TRLHDR.attribute12, ''ATTRIBUTE13'',TRLHDR.attribute13, ''ATTRIBUTE14'',TRLHDR.attribute14,'||
' ''ATTRIBUTE15'',TRLHDR.attribute15)'||
' , '''')'||
' FROM GL_IMPORT_REFERENCES GIR,'||
' FUN_TRX_HEADERS TRLHDR,'||
' JA_CN_DFF_ASSIGNMENTS DFFA'||
' WHERE GIR.REFERENCE_1 = ''Intercompany Transaction'''||
' AND GIR.JE_HEADER_ID = JEH.JE_HEADER_ID'||
' AND GIR.JE_LINE_NUM = JEL.JE_LINE_NUM'||
' AND GIR.REFERENCE_3 = TO_CHAR(TRLHDR.TRX_ID)'||
' AND DFFA.DFF_TITLE_CODE = ''IITH'''||
' AND DFFA.APPLICATION_ID = 435 AND DFFA.chart_of_accounts_id = '||p_chart_of_accounts_id||'),'||
' (SELECT decode(jeh.context, dffa.context_code,'||
' decode(dffa.attribute_column, ''ATTRIBUTE1'',jeh.attribute1, ''ATTRIBUTE2'',jeh.attribute2,'||
' ''ATTRIBUTE3'',jeh.attribute3, ''ATTRIBUTE4'',jeh.attribute4, ''ATTRIBUTE5'',jeh.attribute5,'||
' ''ATTRIBUTE6'',jeh.attribute6, ''ATTRIBUTE7'',jeh.attribute7, ''ATTRIBUTE8'',jeh.attribute8,'||
' ''ATTRIBUTE9'',jeh.attribute9, ''ATTRIBUTE10'',jeh.attribute10))'||
' FROM ja_cn_dff_assignments dffa'||
' WHERE dffa.Application_Id = 101'||
' AND dffa.chart_of_accounts_id = '||p_chart_of_accounts_id||
' AND dffa.dff_title_code=''GLJO'')'||
' ) Settlement_Method_Number ';
l_sql:='INSERT INTO Ja_Cn_Journal_Lines_Req';
l_sql:=l_sql||l_Insert_Header_Sql;
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 DISTINCT Jel.Je_Header_Id Je_Header_Id,';
l_sql:=l_sql||l_Insert_Values_Sql;
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||' (SELECT 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;
l_Insert_Header_Sql VARCHAR2(4000) := '';
l_Insert_Values_Sql VARCHAR2(4000) := '';
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;*/
SELECT 'PA' PROJECT_SOURCE_FLAG,
SAM.CONTEXT_CODE PROJECT_AC_CODE,
XADB.GROUPING_ORDER AC_GROUPING_ORDER
FROM JA_CN_SUB_ACC_MAPPING SAM,
XLA_ANALYTICAL_DTLS_B XADB
WHERE XADB.ANALYTICAL_CRITERION_CODE = SAM.CONTEXT_CODE
AND SAM.SOURCES_CODE = 'PROJECT MODULE'
AND SAM.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_Id;
SELECT SAM.SUBSIDIARY_SEGMENT_CODE,
SAM.SOURCES_CODE,
SAM.CONTEXT_CODE
FROM JA_CN_SUB_ACC_MAPPING SAM
WHERE SAM.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_Id;
SELECT XADB.GROUPING_ORDER
INTO l_SM_Grouping_Order
FROM XLA_ANALYTICAL_DTLS_B XADB
WHERE XADB.ANALYTICAL_CRITERION_CODE = 'SETTLEMENT METHOD';
' (select acs.ac'||l_SM_Grouping_Order||
' from xla_ae_line_acs acs'||
' where Acs.Ae_Header_Id = Ael.Ae_Header_Id'||
' and Acs.Ae_Line_Num = Ael.Ae_Line_Num'||
' and Acs.analytical_criterion_code = ''JA_CN_SM'') ,'||
' ''Receivables'','||
' (select acs.ac'||l_SM_Grouping_Order||
' from xla_ae_line_acs acs'||
' where Acs.Ae_Header_Id = Ael.Ae_Header_Id'||
' and Acs.Ae_Line_Num = Ael.Ae_Line_Num'||
' and Acs.analytical_criterion_code = ''JA_CN_SM''),'||
' NULL) Settlement_Method_Number ';
l_Insert_Header_Sql := l_Insert_Header_Sql || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
l_Insert_Values_Sql := l_Insert_Values_Sql || 'jcc.' ||
l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
l_Insert_Values_Sql := l_Insert_Values_Sql ||
'Decode(Nvl(Ael.Party_Type_Code,''D''), ''C'', ' ||
'Part.Party_Number, To_Char(NULL)) ' || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
l_Insert_Values_Sql := l_Insert_Values_Sql ||
'Decode(vendor_type_lookup_code, ''EMPLOYEE'', to_char(null), '||
'Decode(Nvl(Ael.Party_Type_Code,''D''), ''S'', ' ||
'Sup.Segment1, To_Char(NULL))) ' || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
ELSIF l_Sub_Segment.CONTEXT_CODE = 'EMPLOYEE_SUPPLIER' THEN --Updated for fixing bug 9962344
l_Insert_Values_Sql := l_Insert_Values_Sql ||
'Decode(vendor_type_lookup_code, ''EMPLOYEE'', '||
'TO_CHAR((SELECT EMPLOYEE_NUMBER FROM PER_PEOPLE_F WHERE PERSON_ID = sup.employee_id)), to_char(null)) '||
l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
/* l_Insert_Values_Sql := l_Insert_Values_Sql || 'To_Char(NULL) ' ||
l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;*/
l_Insert_Values_Sql := l_Insert_Values_Sql || 'acs.ac' ||
l_Grouping_Order || ' ' ||
l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ' ;
l_Insertsql:='*/
if nvl(l_Project_Source_Flag, 'N') = 'PA' then
l_Project_Query_Table_Sql := 'xla_ae_line_acs acs,';
l_sql:='INSERT INTO Ja_Cn_Journal_Lines_Req';
l_sql:=l_sql||l_Insert_Header_Sql; -- Added by Chaoqun for CNAOV2
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 DISTINCT /*+index(ael,xla_ae_lines_n4)+*/';
l_sql:=l_sql||' decode(vendor_type_lookup_code,'; --Updated for fixing bug 8970684
l_sql:=l_sql||' decode(vendor_type_lookup_code,'; --Updated for fixing bug 8970684
l_sql:=l_sql||' decode(vendor_type_lookup_code,'; --Updated for fixing bug 8970684
l_sql:=l_sql||l_Insert_Values_Sql; -- Added by Chaoqun for CNAOV2
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,||
l_Insert_Header_Sql ||
--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(+);
l_Insert_Header_Sql VARCHAR2(4000) := '';
l_Insert_Values_Sql VARCHAR2(4000) := '';
SELECT SAM.SUBSIDIARY_SEGMENT_CODE,
SAM.SOURCES_CODE,
SAM.CONTEXT_CODE
FROM JA_CN_SUB_ACC_MAPPING SAM
WHERE SAM.CHART_OF_ACCOUNTS_ID = l_Chart_Of_Accounts_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;
Unitemize_Journal_Lines(p_chart_of_accounts_id => l_chart_of_accounts_id, --Updated by Chaoqun for CNAOV2
p_Request_Id => l_Request_Id/*,
p_Project_Option => l_Project_Option*/);
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_Attribute7 = 'P' --Updated for CNAOV2 solution by chaoqun on 13-May-2010
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);
l_Insert_Header_Sql := l_Insert_Header_Sql || l_Sub_Segment.SUBSIDIARY_SEGMENT_CODE || ', ';
l_Insert_Values_Sql := l_Insert_Header_Sql;
/* 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;*/
l_sql:='INSERT INTO Ja_Cn_Journal_Lines';
l_sql:=l_sql||l_Insert_Header_Sql;
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 Je_Header_Id,';
l_sql:=l_sql||l_Insert_Values_Sql;
l_sql:=l_sql||' Last_Updated_By,';
l_sql:=l_sql||' Last_Update_Date,';
l_sql:=l_sql||' Last_Update_Login,';
/* 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;