[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION UpdateGLCashlines(pn_chart_account_id NUMBER, -- Change from Procedure, by Jianchao Chi for bug 11691910
pn_legal_entitliy_id NUMBER,
pn_ledger_id NUMBER,
P_LEDGER_NAME VARCHAR2,
P_LEGAL_ENTITY VARCHAR2,
pv_period_name VARCHAR2) RETURN NUMBER IS
-- Add return value by Jianchao Chi for bug 11691910
lv_flag VARCHAR2(10);
lv_procedure_name VARCHAR2(40) := 'UpdateCashlines';
lv_cur_cash_lines_sql VARCHAR2(4000) := 'SELECT gjl.je_header_id, gjl.je_line_num
FROM gl_je_lines gjl, gl_je_headers gjh, ja_cn_dff_assignments jcda
WHERE gjl.ledger_id = ' ||
pn_ledger_id || '
AND gjh.ledger_id = gjl.ledger_id
AND gjh.period_name = ''' ||
pv_period_name || '''
AND gjh.period_name = gjl.period_name
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE JA_CN_VOUCHER_NUM_PKG.get_balancing_segment(gjl.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = ' ||
pn_ledger_id || '
AND jclllbg.Legal_Entity_Id = ' ||
pn_legal_entitliy_id || ')
AND gjh.je_header_id = gjl.je_header_id
AND gjl.context = jcda.context_code
AND jcda.chart_of_accounts_id = ' ||
pn_chart_account_id || '
AND jcda.descriptive_flexfield_name = ''GL_JE_LINES''
AND jcda.dff_title_code = ''GLLI''
AND gjh.je_category IN
( SELECT gjc.je_category_name
FROM gl_je_categories gjc, ja_cn_dff_assignments jcda
WHERE jcda.chart_of_accounts_id = ' ||
pn_chart_account_id || '
AND gjc.$cash_related_attribute$ = ''Y''
AND jcda.descriptive_flexfield_name = ''GL_JE_CATEGORIES''
AND jcda.dff_title_code = ''JOCA''
AND gjc.CONTEXT = jcda.context_code)
AND gjl.Global_Attribute6 is null';
lv_update_cash_lines_sql VARCHAR2(4000) := 'UPDATE gl_je_lines gjl
SET gjl.Global_Attribute6 = gjl.$cashitem_column$
WHERE gjl.je_header_id = :1
AND gjl.je_line_num = :2';
SELECT jcda.attribute_column
INTO lv_cashrelated_column
FROM ja_cn_dff_assignments jcda
WHERE jcda.chart_of_accounts_id = pn_chart_account_id
AND jcda.descriptive_flexfield_name = 'GL_JE_CATEGORIES'
AND jcda.dff_title_code = 'JOCA';
SELECT jcda.attribute_column, b.enabled_flag
INTO lv_cashitem_column, lv_enabled
FROM fnd_descr_flex_column_usages b, ja_cn_dff_assignments jcda
WHERE (b.APPLICATION_ID = 101)
AND (b.descriptive_flexfield_name =
JCDA.descriptive_flexfield_name)
AND jcda.chart_of_accounts_id = pn_chart_account_id
AND jcda.descriptive_flexfield_name = 'GL_JE_LINES'
AND jcda.dff_title_code = 'GLLI'
AND b.descriptive_flex_context_code = jcda.context_code;
lv_update_cash_lines_sql := REPLACE(lv_update_cash_lines_sql,
'$cashitem_column$',
lv_cashitem_column);
EXECUTE IMMEDIATE lv_update_cash_lines_sql
USING ln_je_header_id, ln_je_line_num;
END UpdateGLCashlines;
PROCEDURE UpdateSLAHeaderLine(pn_legal_entitliy_id NUMBER,
pn_ledger_id NUMBER,
P_LEDGER_NAME VARCHAR2,
P_LEGAL_ENTITY VARCHAR2,
pv_period_name VARCHAR2) IS
lv_flag VARCHAR2(10);
lv_procedure_name VARCHAR2(40) := 'UpdateSLAHeaderLine';
UPDATE ja_cn_cfs_activities_all jcca
SET jcca.je_header_id =
(SELECT DISTINCT jcjl.je_header_id
FROM JA_CN_JOURNAL_LINES jcjl,
Gl_Je_Headers Jeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir
WHERE Jeh.Je_Header_Id = jcjl.je_header_id
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Gir.Je_Line_Num = jcjl.Je_Line_Num
AND Gir.Je_Header_Id = jcjl.Je_Header_Id
AND jcca.trx_id = ael.ae_header_id
AND jcca.trx_line_id = ael.ae_line_num
AND jcjl.legal_entity_id = pn_legal_entitliy_id
AND jcjl.ledger_id = pn_ledger_id
AND jcjl.period_name = pv_period_name),
jcca.je_line_num =
(SELECT DISTINCT jcjl.Je_Line_Num
FROM JA_CN_JOURNAL_LINES jcjl,
Gl_Je_Headers Jeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir
WHERE Jeh.Je_Header_Id = jcjl.je_header_id
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Gir.Je_Line_Num = jcjl.Je_Line_Num
AND Gir.Je_Header_Id = jcjl.Je_Header_Id
AND jcca.trx_id = ael.ae_header_id
AND jcca.trx_line_id = ael.ae_line_num
AND jcjl.legal_entity_id = pn_legal_entitliy_id
AND jcjl.ledger_id = pn_ledger_id
AND jcjl.period_name = pv_period_name)
WHERE jcca.transaction_type = 'SLA'
AND (jcca.je_header_id IS NULL OR jcca.je_line_num IS NULL)
AND legal_entity_id = pn_legal_entitliy_id
AND ledger_id = pn_ledger_id;
END UpdateSLAHeaderLine;
PROCEDURE UpdateGLItemizationlines(pn_legal_entitliy_id NUMBER,
pn_ledger_id NUMBER,
pv_period_name VARCHAR2) IS
lv_flag VARCHAR2(10);
lv_procedure_name VARCHAR2(40) := 'UpdateGLItemizationlines';
UPDATE gl_je_lines gjl
SET gjl.Global_Attribute7 = gjl.Global_Attribute2,
gjl.Global_Attribute2 = NULL
WHERE gjl.ledger_id = pn_ledger_id
AND gjl.period_name = pv_period_name
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE JA_CN_VOUCHER_NUM_PKG.get_balancing_segment(gjl.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entitliy_id)
AND gjl.Global_Attribute7 IS NULL
AND gjl.Global_Attribute2 IS NOT NULL;
END UpdateGLItemizationlines;
/*PROCEDURE UpdateItemizationlines(pn_chart_account_id NUMBER,
pn_legal_entitliy_id NUMBER,
pn_ledger_id NUMBER,
pv_period_name VARCHAR2,
--pb_msg_tag BOOLEAN, --Add a parameter by Jianchao Chi for bug 11683977
lb_need_Update BOOLEAN,
lv_prject_seg VARCHAR2) IS
ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
lv_procedure_name VARCHAR2(40) := 'UpdateItemizationlines';
lv_update_sql VARCHAR2(1000);
\* lv_update_template_sql VARCHAR2(1000) := 'UPDATE ja_cn_journal_lines jcjl
SET $to_column$ = $from_column$
WHERE jcjl.je_header_id = :1
AND jcjl.je_line_num = :2
AND jcjl.rowid = :3'; -- Chongwu fix bug 12373841*\
lv_update_template_sql VARCHAR2(1000) := 'UPDATE ja_cn_journal_lines jcjl
SET $to_column$ = $from_column$
WHERE jcjl.rowid = :1'; -- Chongwu fix bug 12373841
SELECT jcjl.rowid, -- Chongwu fix bug 12373841
je_header_id,
je_line_num,
third_party_number,
third_party_type,
cost_center,
ppf.employee_number, --choli changed for bug 11774438
project_number
FROM ja_cn_journal_lines jcjl, per_people_f ppf --choli changed for bug 11774438
WHERE jcjl.legal_entity_id = pn_legal_entitliy_id
AND jcjl.ledger_id = pn_ledger_id
AND jcjl.journal_number IS NOT NULL
AND jcjl.period_name = pv_period_name
AND ppf.person_id(+) = jcjl.personnel_id; --choli added for bug 11774438
lv_bal_update_sql VARCHAR2(1000);
\* lv_bal_update_tpl_sql VARCHAR2(1000) := 'UPDATE ja_cn_account_balances jcab
SET $to_column$ = $from_column$
WHERE jcab.ledger_id = :1
AND jcab.legal_entity_id = :2
AND jcab.company_segment = :3
AND jcab.period_name = :4
AND jcab.currency_code = :5
AND nvl(jcab.cost_center,0) = nvl(:6,0)
AND nvl(jcab.project_number,0) = nvl(:7,0)
AND nvl(jcab.project_source,0) = nvl(:8,0)
AND nvl(jcab.account_segment,0) = nvl(:9,0)
AND nvl(jcab.personnel_id,0) = nvl(:10,0)
AND nvl(jcab.third_party_type,0) = nvl(:11,0)
AND nvl(jcab.third_party_id,0) = nvl(:12,0)';*\
lv_bal_update_tpl_sql VARCHAR2(1000) := 'UPDATE ja_cn_account_balances jcab
SET $to_column$ = $from_column$
WHERE jcab.rowid = :1';
SELECT jcab.rowid,
ledger_id,
legal_entity_id,
company_segment,
period_name,
currency_code,
cost_center,
project_number,
project_source,
account_segment,
personnel_id,
ppf.employee_number, --choli changed for bug 11774438
third_party_type,
third_party_id,
third_party_number
FROM ja_cn_account_balances jcab, per_people_f ppf --choli changed for bug 11774438
WHERE jcab.legal_entity_id = pn_legal_entitliy_id
AND jcab.ledger_id = pn_ledger_id
AND jcab.period_name = pv_period_name
AND ppf.person_id(+) = jcab.personnel_id; --choli added for bug 11774438
SELECT sam.subsidiary_segment_code,
sam.sources_code,
sam.context_code
FROM ja_cn_sub_acc_mapping sam
WHERE (chart_of_accounts_id = pn_chart_account_id)
AND context_code in
('CUSTOMER',
'SUPPLIER',
'EMPLOYEE_SUPPLIER',
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE segment_attribute_type = 'FA_COST_CTR'
AND attribute_value = 'Y'
AND (id_flex_num = pn_chart_account_id)
AND (id_flex_code = 'GL#')
AND (application_id = 101)));
SELECT application_column_name
INTO lv_costcenter_column
FROM fnd_segment_attribute_values
WHERE segment_attribute_type = 'FA_COST_CTR'
AND attribute_value = 'Y'
AND (id_flex_num = pn_chart_account_id)
AND (id_flex_code = 'GL#')
AND (application_id = 101);
\*SELECT count(*)--Comment by Jianchao Chi for bug 11683977
INTO ln_Subsidiary_number
FROM ja_cn_sub_acc_mapping sam
WHERE (chart_of_accounts_id = pn_chart_account_id)
and context_code in
('CUSTOMER', 'SUPPLIER', 'EMPLOYEE_SUPPLIER',
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE segment_attribute_type = 'FA_COST_CTR'
AND attribute_value = 'Y'
AND (id_flex_num = pn_chart_account_id)
AND (id_flex_code = 'GL#')
AND (application_id = 101)));
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_customer_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
-- choli change the follow line to use third_party_number instead of third_party_id
-- to fix bug 11664438
'''' || v_line.third_party_number || '''');
EXECUTE IMMEDIATE lv_update_sql
USING v_line.rowid;
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_supplier_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
-- choli change the follow line to use third_party_number instead of third_party_id
-- to fix bug 11664438
'''' || v_line.third_party_number || '''');
EXECUTE IMMEDIATE lv_update_sql
USING v_line.rowid;
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_employee_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
'''' || v_line.employee_number || '''');
EXECUTE IMMEDIATE lv_update_sql
USING v_line.rowid;
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_costcenter_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
'''' || v_line.cost_center || '''');
EXECUTE IMMEDIATE lv_update_sql
USING v_line.rowid;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_customer_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
-- choli change the follow line to use third_party_number instead of third_party_id
-- to fix bug 11664438
'''' || v_line.third_party_number || '''');
lv_bal_update_sql := lv_bal_update_sql || ' AND ' ||
lv_customer_seg || ' IS NULL ';
EXECUTE IMMEDIATE lv_bal_update_sql
USING v_line.rowid;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_supplier_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
-- choli change the follow line to use third_party_number instead of third_party_id
-- to fix bug 11664438
'''' || v_line.third_party_number || '''');
lv_bal_update_sql := lv_bal_update_sql || ' AND ' ||
lv_supplier_seg || ' IS NULL ';
EXECUTE IMMEDIATE lv_bal_update_sql
USING v_line.rowid;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_employee_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
'''' || v_line.employee_number || '''');
lv_bal_update_sql := lv_bal_update_sql || ' AND ' ||
lv_employee_seg || ' IS NULL ';
EXECUTE IMMEDIATE lv_bal_update_sql
USING v_line.rowid;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_costcenter_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
'''' || v_line.cost_center || '''');
lv_bal_update_sql := lv_bal_update_sql || ' AND ' ||
lv_costcenter_seg || ' IS NULL ';
EXECUTE IMMEDIATE lv_bal_update_sql
USING v_line.rowid;
UPDATE JA_CN_JOURNAL_LINES jc
SET currency_conversion_type =
(SELECT DISTINCT NVL(Ael.Currency_Conversion_Type,
Jeh.Currency_Conversion_Type) currency_conversion_type
FROM Gl_Je_Headers Jeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir,
JA_CN_JOURNAL_LINES jcjl
WHERE Jeh.Je_Header_Id = jcjl.je_header_id
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id(+)
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table(+)
AND Gir.Je_Line_Num(+) = jcjl.Je_Line_Num
AND Gir.Je_Header_Id(+) = jcjl.Je_Header_Id
AND jc.Je_Header_Id = jcjl.je_header_id
--Add by Jianchao Chi for bug 10634017
AND jc.Je_Line_Num = jcjl.Je_Line_Num)
WHERE jc.Ledger_Id = pn_ledger_id
AND jc.Legal_Entity_Id = pn_legal_entitliy_id;
IF (lb_need_Update) THEN
FOR v_line IN cur_itemiz_lines LOOP
-- Construct the update sql for Itemazation data migration
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_prject_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
'''' || v_line.project_number || '''');
EXECUTE IMMEDIATE lv_update_sql
USING v_line.rowid;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_prject_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
'''' || v_line.project_number || '''');
lv_bal_update_sql := lv_bal_update_sql || ' AND ' || lv_prject_seg ||
' IS NULL ';
EXECUTE IMMEDIATE lv_bal_update_sql
USING v_line.rowid;
END IF; --(lb_need_Update) THEN
END UpdateItemizationlines;*/
lb_need_update BOOLEAN := TRUE;
SELECT DISTINCT period_name
FROM ja_cn_journal_lines
WHERE ledger_id = P_LEDGER_ID
AND legal_entity_id = P_LEGAL_ENTITY_ID
AND journal_number IS NOT NULL;
SELECT count(*) -- Moved from UpdateItemizationlines function by Jianchao Chi for bug 11683977
INTO ln_Subsidiary_number
FROM ja_cn_sub_acc_mapping sam
WHERE (chart_of_accounts_id = P_COA_ID)
and context_code in
('CUSTOMER',
'SUPPLIER',
'EMPLOYEE_SUPPLIER',
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE segment_attribute_type = 'FA_COST_CTR'
AND attribute_value = 'Y'
AND (id_flex_num = P_COA_ID)
AND (id_flex_code = 'GL#')
AND (application_id = 101)));
UpdateGLCashlines(P_COA_ID,
P_LEGAL_ENTITY_ID,
P_LEDGER_ID,
P_LEDGER_NAME,
P_LEGAL_ENTITY,
v_line.period_name);
UpdateGLItemizationlines(P_LEGAL_ENTITY_ID,
P_LEDGER_ID,
v_line.period_name);
UpdateItemizationlines(P_COA_ID,
P_LEGAL_ENTITY_ID,
P_LEDGER_ID,
v_line.period_name,
--lb_Msg_Tag,
lb_need_update);
\* UpdateBalances(P_COA_ID
,P_LEGAL_ENTITY_ID
,P_LEDGER_ID); *\
/* SELECT DISTINCT period_name
FROM ja_cn_journal_lines
WHERE ledger_id = P_LEDGER_ID
AND legal_entity_id = P_LEGAL_ENTITY_ID
AND journal_number IS NOT NULL;*/
SELECT DISTINCT period_name
FROM gl_je_lines
WHERE ledger_id = P_LEDGER_ID;
UpdateGLCashlines(P_COA_ID,
P_LEGAL_ENTITY_ID,
P_LEDGER_ID,
P_LEDGER_NAME,
P_LEGAL_ENTITY,
v_line.period_name);
UpdateSLAHeaderLine(P_LEGAL_ENTITY_ID,
P_LEDGER_ID,
P_LEDGER_NAME,
P_LEGAL_ENTITY,
v_line.period_name);
lb_need_update BOOLEAN := TRUE;
/* SELECT DISTINCT period_name
FROM ja_cn_journal_lines
WHERE ledger_id = P_LEDGER_ID
AND legal_entity_id = P_LEGAL_ENTITY_ID
AND journal_number IS NOT NULL;*/
SELECT DISTINCT period_name
FROM gl_je_lines
WHERE ledger_id = P_LEDGER_ID;
SELECT count(*) -- Moved from UpdateItemizationlines function by Jianchao Chi for bug 11683977
INTO ln_Subsidiary_number
FROM ja_cn_sub_acc_mapping sam
WHERE (chart_of_accounts_id = P_COA_ID)
and context_code in
('CUSTOMER',
'SUPPLIER',
'EMPLOYEE_SUPPLIER',
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE segment_attribute_type = 'FA_COST_CTR'
AND attribute_value = 'Y'
AND (id_flex_num = P_COA_ID)
AND (id_flex_code = 'GL#')
AND (application_id = 101)));
lb_need_Update := FALSE;
SELECT project_source_flag --, project_ac_code, coa_segment
INTO lv_project_sourrce_flag --, lv_project_ac_code, lv_coa_segment
FROM ja_cn_sub_acc_sources_all
WHERE chart_of_accounts_id = P_COA_ID;
SELECT sam.subsidiary_segment_code
INTO lv_prject_project_seg
FROM ja_cn_sub_acc_mapping sam
WHERE (sam.chart_of_accounts_id = P_COA_ID)
AND sam.context_code = 'PROJECT_NUMBER'
AND sam.sources_code = 'PROJECT MODULE';
SELECT sam.subsidiary_segment_code
INTO lv_prject_coa_seg
FROM ja_cn_sub_acc_mapping sam
WHERE sam.sources_code = 'COA'
AND sam.chart_of_accounts_id = P_COA_ID
AND sam.context_code IN
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE attribute_value = 'Y'
AND (id_flex_num = P_COA_ID)
AND (id_flex_code = 'GL#')
AND (application_id = 101)
AND application_column_name NOT IN
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE attribute_value = 'Y'
AND (id_flex_num = P_COA_ID)
AND (id_flex_code = 'GL#')
AND (application_id = 101)
and segment_attribute_type IN
('FA_COST_CTR', 'GL_BALANCING', 'GL_ACCOUNT')));
lb_need_Update := FALSE;
lb_need_Update := FALSE;
IF (lb_need_Update) THEN
-- Chongwu update for Project solution changes by anita FDD 0.6
IF (lv_project_sourrce_flag = 'PA') THEN
IF (lv_prject_coa_seg IS NULL AND lv_prject_project_seg IS NULL) THEN
Fnd_Message.Set_Name(Application => 'JA',
NAME => 'JA_CN_MISSING_SUBSIDIARY_SETUP');
lb_need_Update := FALSE;
lb_need_Update := FALSE;
lb_need_Update := FALSE;
lb_need_Update := FALSE;
lb_need_Update := FALSE;
END IF; --(lb_need_Update) THEN
UpdateGLItemizationlines(P_LEGAL_ENTITY_ID,
P_LEDGER_ID,
v_line.period_name);
UpdateItemizationlines(P_COA_ID,
P_LEGAL_ENTITY_ID,
P_LEDGER_ID,
v_line.period_name,
--lb_Msg_Tag,
lb_need_update,
lv_prject_seg);
lb_need_update := FALSE;
IF(lb_need_update) THEN
-----
Fnd_Message.Set_Name(Application => 'JA',
NAME => 'JA_CN_DATA_MIGRATION_RESULT_D');
PROCEDURE UpdateItemizationlines(pn_chart_account_id NUMBER,
pn_legal_entitliy_id NUMBER,
pn_ledger_id NUMBER,
pv_period_name VARCHAR2,
--pb_msg_tag BOOLEAN, --Add a parameter by Jianchao Chi for bug 11683977
lb_need_Update BOOLEAN,
lv_prject_seg VARCHAR2) IS
ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
lv_procedure_name VARCHAR2(40) := 'UpdateItemizationlines';
lv_update_sql VARCHAR2(1000);
lv_update_template_sql VARCHAR2(1000) := 'UPDATE ja_cn_journal_lines jcjl
SET jcjl.$to_column$ = $from_column$
WHERE jcjl.legal_entity_id = ' || pn_legal_entitliy_id ||'
AND jcjl.ledger_id = ' || pn_ledger_id ||'
AND jcjl.journal_number IS NOT NULL
AND jcjl.period_name = ''' || pv_period_name ||'''
AND jcjl.$to_column$ IS NULL';
lv_bal_update_sql VARCHAR2(1000);
lv_bal_update_tpl_sql VARCHAR2(1000) := 'UPDATE ja_cn_account_balances jcab
SET jcab.$to_column$ = $from_column$
WHERE jcab.legal_entity_id = ' || pn_legal_entitliy_id ||'
AND jcab.ledger_id = ' || pn_ledger_id ||'
AND jcab.period_name = ''' || pv_period_name || '''
AND jcab.$to_column$ IS NULL';
SELECT sam.subsidiary_segment_code,
sam.sources_code,
sam.context_code
FROM ja_cn_sub_acc_mapping sam
WHERE (chart_of_accounts_id = pn_chart_account_id)
AND context_code in
('CUSTOMER',
'SUPPLIER',
'EMPLOYEE_SUPPLIER',
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE segment_attribute_type = 'FA_COST_CTR'
AND attribute_value = 'Y'
AND (id_flex_num = pn_chart_account_id)
AND (id_flex_code = 'GL#')
AND (application_id = 101)));
SELECT application_column_name
INTO lv_costcenter_column
FROM fnd_segment_attribute_values
WHERE segment_attribute_type = 'FA_COST_CTR'
AND attribute_value = 'Y'
AND (id_flex_num = pn_chart_account_id)
AND (id_flex_code = 'GL#')
AND (application_id = 101);
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_customer_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
'jcjl.third_party_number');
lv_update_sql := lv_update_sql || ' AND jcjl.third_party_type = ''C'' ';
EXECUTE IMMEDIATE lv_update_sql;
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_supplier_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
'jcjl.third_party_number');
lv_update_sql := lv_update_sql || ' AND jcjl.third_party_type = ''S'' ';
EXECUTE IMMEDIATE lv_update_sql;
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_employee_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
'(select distinct ppf.employee_number from per_people_f ppf where ppf.person_id(+) = jcjl.personnel_id)');
EXECUTE IMMEDIATE lv_update_sql;
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_costcenter_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
'jcjl.cost_center');
EXECUTE IMMEDIATE lv_update_sql;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_customer_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
'jcab.third_party_number');
lv_bal_update_sql := lv_bal_update_sql || ' AND jcab.third_party_type = ''C'' ';
EXECUTE IMMEDIATE lv_bal_update_sql;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_supplier_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
'jcab.third_party_number');
lv_bal_update_sql := lv_bal_update_sql || ' AND jcab.third_party_type = ''S'' ';
EXECUTE IMMEDIATE lv_bal_update_sql;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_employee_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
'(select distinct ppf.employee_number from per_people_f ppf where ppf.person_id(+) = jcab.personnel_id)');
EXECUTE IMMEDIATE lv_bal_update_sql;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_costcenter_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
'jcab.cost_center');
EXECUTE IMMEDIATE lv_bal_update_sql;
UPDATE JA_CN_JOURNAL_LINES jc
SET currency_conversion_type =
(SELECT DISTINCT NVL(Ael.Currency_Conversion_Type,
Jeh.Currency_Conversion_Type) currency_conversion_type
FROM Gl_Je_Headers Jeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir,
JA_CN_JOURNAL_LINES jcjl
WHERE Jeh.Je_Header_Id = jcjl.je_header_id
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id(+)
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table(+)
AND Gir.Je_Line_Num(+) = jcjl.Je_Line_Num
AND Gir.Je_Header_Id(+) = jcjl.Je_Header_Id
AND jc.Je_Header_Id = jcjl.je_header_id
--Add by Jianchao Chi for bug 10634017
AND jc.Je_Line_Num = jcjl.Je_Line_Num)
WHERE jc.Ledger_Id = pn_ledger_id
AND jc.Legal_Entity_Id = pn_legal_entitliy_id
AND jc.period_name = pv_period_name
AND jc.currency_conversion_type IS NULL;
IF (lb_need_Update) THEN
-- Update project
lv_update_sql := REPLACE(lv_update_template_sql,
'$to_column$',
lv_prject_seg);
lv_update_sql := REPLACE(lv_update_sql,
'$from_column$',
'project_number');
EXECUTE IMMEDIATE lv_update_sql;
lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
'$to_column$',
lv_prject_seg);
lv_bal_update_sql := REPLACE(lv_bal_update_sql,
'$from_column$',
'jcab.project_number');
EXECUTE IMMEDIATE lv_bal_update_sql;
END IF; --(lb_need_Update) THEN
END UpdateItemizationlines;