The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fnd_profile.value('AMW_FIN_IMPORT_FROM_FSG') into m_fsg_or_not from dual;
select fnd_profile.value('AMW_FIN_IMPORT_FROM_FSG') into m_fsg_or_not from dual;
M_LAST_UPDATE_DATE DATE;
M_LAST_UPDATED_BY NUMBER;
M_LAST_UPDATE_LOGIN NUMBER;
'SELECT
FINANCIAL_STATEMENT_ID
from ' || fnd_profile.value('AMW_STMNT_SOURCE_VIEW')
|| ' where FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id =' || to_char(P_RUN_ID) || ')';
'SELECT
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
PARENT_FINANCIAL_ITEM_ID,
SEQUENCE_NUMBER
from ' || fnd_profile.value('AMW_FINITEM_SOURCE_VIEW')
|| ' where FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id ='|| to_char(P_RUN_ID) || ')';
'SELECT
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
NATURAL_ACCOUNT_ID
from ' || fnd_profile.value('AMW_FIN_ITEM_ACC_RELATIONS_VIEW');
'SELECT
FINANCIAL_STATEMENT_ID,
NAME ,
LANGUAGE ,
SOURCE_LANGUAGE
from ' || fnd_profile.value('AMW_STMNT_SOURCE_TL_VIEW');
'SELECT
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
NAME ,
LANGUAGE ,
SOURCE_LANGUAGE
from ' || fnd_profile.value('AMW_FINITEM_SOURCE_TL_VIEW');
SELECT
distinct keyacc.ACCOUNT_GROUP_ID
--, keyacc.NATURAL_ACCOUNT_ID
--, NATURAL_ACCOUNT_VALUE
from
AMW_FIN_KEY_ACCOUNTS_B keyacc
WHERE keyacc.End_Date is null
And keyacc.NATURAL_ACCOUNT_ID = P_NATURAL_ACCOUNT_ID
and END_DATE is null
and ACCOUNT_GROUP_ID =
(select max(keyacc2.ACCOUNT_GROUP_ID) from AMW_FIN_KEY_ACCOUNTS_B keyacc2 where keyacc2.End_Date is null) ;
select AMW_FIN_STMNT_S.nextval into M_STATEMENT_GROUP_ID from dual;
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_STMNT_ROW (
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
X_END_DATE => NULL,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_STMNT_ROW_TL(
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
X_NAME => M_NAME,
X_LANGUAGE => M_LANGUAGE,
X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE ,
-- X_OBJECT_TYPE ,
X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID ,
X_OBJECT_VERSION_NUMBER => Null,
X_ORIG_SYSTEM_REFERENCE => Null ,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ROW(
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
X_PARENT_FINANCIAL_ITEM_ID => M_PARENT_FINANCIAL_ITEM_ID ,
X_SEQUENCE_NUMBER => M_SEQUENCE_NUMBER ,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ROW_TL(
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
X_NAME => M_NAME,
X_LANGUAGE => M_LANGUAGE,
X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE ,
X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID ,
X_OBJECT_VERSION_NUMBER => Null,
X_ORIG_SYSTEM_REFERENCE => Null ,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id);
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR FINANCIAL_ITEM_ID =' || M_FINANCIAL_ITEM_ID );
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR ACCOUNT_GROUP_ID=' || M_ACCOUNT_GROUP_ID);
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR NATURAL_ACCOUNT_ID=' || M_NATURAL_ACCOUNT_ID);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ACC_MAP(
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID ,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
X_NATURAL_ACCOUNT_ID =>M_NATURAL_ACCOUNT_ID ,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
M_LAST_UPDATE_DATE DATE;
M_LAST_UPDATED_BY NUMBER;
M_LAST_UPDATE_LOGIN NUMBER;
select
FINANCIAL_STATEMENT_ID,
NAME
from
AMW_STATEMENTS_V
where FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id = P_RUN_ID);
select
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
NAME,
trim(DESCRIPTION) DESCRIPTION,
DISPLAY_FLAG,
PARENT_FINANCIAL_ITEM_ID
from
AMW_FINANCIAL_ITEMS_V
where FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id = P_RUN_ID);
select
LANGUAGE_CODE
from
FND_LANGUAGES
where INSTALLED_FLAG in ('I', 'B');
select AMW_FIN_STMNT_S.nextval into M_STATEMENT_GROUP_ID from dual;
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_STMNT_ROW (
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
X_END_DATE => NULL,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_STMNT_ROW_TL(
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID,
X_NAME => M_NAME,
X_LANGUAGE => M_LANGUAGE,
X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE,
-- X_OBJECT_TYPE ,
X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID,
X_OBJECT_VERSION_NUMBER => Null,
X_ORIG_SYSTEM_REFERENCE => Null,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ROW(
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
X_PARENT_FINANCIAL_ITEM_ID => M_PARENT_FINANCIAL_ITEM_ID,
X_SEQUENCE_NUMBER => M_FINANCIAL_ITEM_ID ,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ROW_TL(
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID,
X_NAME => M_NAME,
X_LANGUAGE => M_LANGUAGE,
X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE,
X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID,
X_OBJECT_VERSION_NUMBER => Null,
X_ORIG_SYSTEM_REFERENCE => Null,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id);
M_LAST_UPDATE_DATE DATE;
M_LAST_UPDATED_BY NUMBER;
M_LAST_UPDATE_LOGIN NUMBER;
SELECT
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID ,
SET_OF_BOOKS_ID ,
SEGMENT1_LOW ,
SEGMENT1_HIGH ,
SEGMENT1_TYPE ,
SEGMENT2_LOW ,
SEGMENT2_HIGH ,
SEGMENT2_TYPE ,
SEGMENT3_LOW ,
SEGMENT3_HIGH ,
SEGMENT3_TYPE ,
SEGMENT4_LOW ,
SEGMENT4_HIGH ,
SEGMENT4_TYPE ,
SEGMENT5_LOW ,
SEGMENT5_HIGH ,
SEGMENT5_TYPE ,
SEGMENT6_LOW ,
SEGMENT6_HIGH ,
SEGMENT6_TYPE ,
SEGMENT7_LOW ,
SEGMENT7_HIGH ,
SEGMENT7_TYPE ,
SEGMENT8_LOW ,
SEGMENT8_HIGH ,
SEGMENT8_TYPE ,
SEGMENT9_LOW ,
SEGMENT9_HIGH ,
SEGMENT9_TYPE ,
SEGMENT10_LOW ,
SEGMENT10_HIGH ,
SEGMENT10_TYPE ,
SEGMENT11_LOW ,
SEGMENT11_HIGH ,
SEGMENT11_TYPE ,
SEGMENT12_LOW ,
SEGMENT12_HIGH ,
SEGMENT12_TYPE ,
SEGMENT13_LOW ,
SEGMENT13_HIGH ,
SEGMENT13_TYPE ,
SEGMENT14_LOW ,
SEGMENT14_HIGH ,
SEGMENT14_TYPE ,
SEGMENT15_LOW ,
SEGMENT15_HIGH ,
SEGMENT15_TYPE ,
SEGMENT16_LOW ,
SEGMENT16_HIGH ,
SEGMENT16_TYPE ,
SEGMENT17_LOW ,
SEGMENT17_HIGH ,
SEGMENT17_TYPE ,
SEGMENT18_LOW ,
SEGMENT18_HIGH ,
SEGMENT18_TYPE ,
SEGMENT19_LOW ,
SEGMENT19_HIGH ,
SEGMENT19_TYPE ,
SEGMENT20_LOW ,
SEGMENT20_HIGH ,
SEGMENT20_TYPE ,
SEGMENT21_LOW ,
SEGMENT21_HIGH ,
SEGMENT21_TYPE ,
SEGMENT22_LOW ,
SEGMENT22_HIGH ,
SEGMENT22_TYPE ,
SEGMENT23_LOW ,
SEGMENT23_HIGH ,
SEGMENT23_TYPE ,
SEGMENT24_LOW ,
SEGMENT24_HIGH ,
SEGMENT24_TYPE ,
SEGMENT25_LOW ,
SEGMENT25_HIGH ,
SEGMENT25_TYPE ,
SEGMENT26_LOW ,
SEGMENT26_HIGH ,
SEGMENT26_TYPE ,
SEGMENT27_LOW ,
SEGMENT27_HIGH ,
SEGMENT27_TYPE ,
SEGMENT28_LOW ,
SEGMENT28_HIGH ,
SEGMENT28_TYPE ,
SEGMENT29_LOW ,
SEGMENT29_HIGH ,
SEGMENT29_TYPE ,
SEGMENT30_LOW ,
SEGMENT30_HIGH ,
SEGMENT30_TYPE
FROM
AMW_FIN_ITEMS_ACCOUNT_RANGE_V
where FINANCIAL_STATEMENT_ID
in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id = P_RUN_ID);
Select
distinct v.APPLICATION_COLUMN_NAME ,
vs.flex_value_set_name, vs.description, vs.flex_value_set_id
from
fnd_flex_value_sets vs, fnd_segment_attribute_Values v, fnd_id_flex_segments s
where
s.application_id=101 and s.id_flex_Code = 'GL#'
and s.enabled_flag='Y'
and v.application_id=s.application_id
and v.id_flex_code=s.id_flex_code
and v.id_flex_num=s.id_flex_num
and v.application_column_name=s.application_column_name
and v.segment_attribute_type='GL_ACCOUNT'
and v.attribute_value='Y'
and s.flex_value_set_id=vs.flex_value_set_id
and v.id_flex_num= P_CHART_OF_ACCOUNTS_ID;
Select
CHART_OF_ACCOUNTS_ID
from
GL_SETS_OF_BOOKS_V
Where
SET_OF_BOOKS_ID= P_SET_OF_BOOKS_ID ;
SELECT
distinct ACCOUNT_GROUP_ID , NATURAL_ACCOUNT_ID, NATURAL_ACCOUNT_VALUE
from
AMW_FIN_KEY_ACCOUNTS_B
WHERE End_Date is null
And NATURAL_ACCOUNT_VALUE >= M_Low_Value
And NATURAL_ACCOUNT_VALUE <= M_High_Value
and END_DATE is null;
select fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET') into default_value_set_id from dual ;
select fnd_profile.value('GL_SET_OF_BKS_ID') into M_DEFAULT_SETOFBOOKS_ID from dual ;
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR STATEMENT_GROUP_ID =' || M_STATEMENT_GROUP_ID);
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR FINANCIAL_STATEMENT_ID =' || M_FINANCIAL_STATEMENT_ID );
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR FINANCIAL_ITEM_ID =' || M_FINANCIAL_ITEM_ID );
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR ACCOUNT_GROUP_ID=' || M_ACCOUNT_GROUP_ID);
fnd_file.put_line(fnd_file.LOG, 'CALLING INSERT FINITEM_ACC_MAP FOR NATURAL_ACCOUNT_ID=' || M_NATURAL_ACCOUNT_ID);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_FINITEM_ACC_MAP(
X_STATEMENT_GROUP_ID => M_STATEMENT_GROUP_ID,
X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID ,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID ,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID ,
X_NATURAL_ACCOUNT_ID =>M_NATURAL_ACCOUNT_ID ,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
update AMW_FIN_STMNT_B set end_date = sysdate
where STATEMENT_GROUP_ID <> P_STATEMENT_GROUP_ID and
FINANCIAL_STATEMENT_ID in (select FINANCIAL_STATEMENT_ID from AMW_FIN_STMNT_SELECTION where run_id = P_RUNID);
delete AMW_FIN_STMNT_TL tl where exists(select base.STATEMENT_GROUP_ID, base.FINANCIAL_STATEMENT_ID
from AMW_FIN_STMNT_B base where base.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and base.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and base.end_date is not null
) and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
'FIN_STMT');
delete AMW_FIN_STMNT_ITEMS_TL tl where exists(select base.STATEMENT_GROUP_ID, base.FINANCIAL_STATEMENT_ID
from AMW_FIN_STMNT_B base where base.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and base.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and base.end_date is not null
) and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
'FIN_STMT');
delete AMW_FIN_STMNT_ITEMS_B tl where exists(select base.STATEMENT_GROUP_ID, base.FINANCIAL_STATEMENT_ID
from AMW_FIN_STMNT_B base where base.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and base.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and base.end_date is not null
) and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
'FIN_STMT');
delete AMW_FIN_ITEMS_KEY_ACC tl where exists(select base.STATEMENT_GROUP_ID, base.FINANCIAL_STATEMENT_ID
from AMW_FIN_STMNT_B base where base.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and base.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and base.end_date is not null
) and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
'FIN_STMT');
delete AMW_FIN_STMNT_B tl where tl.end_date is not null
and not exists (select cert.STATEMENT_GROUP_ID, cert.FINANCIAL_STATEMENT_ID
from amw_certification_b cert where cert.STATEMENT_GROUP_ID= tl.STATEMENT_GROUP_ID
and cert.FINANCIAL_STATEMENT_ID = tl.FINANCIAL_STATEMENT_ID and cert.OBJECT_TYPE =
'FIN_STMT');
M_LAST_UPDATE_DATE DATE;
M_LAST_UPDATED_BY NUMBER;
M_LAST_UPDATE_LOGIN NUMBER;
SELECT
FLEX_VALUE_ID, FLEX_VALUE, DESCRIPTION
from
fnd_flex_values_vl
-- 4872820 18-Oct-2006 Start-1
--WHERE flex_value_set_id= fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET');
select
FLEX_VALUE_SET_ID ,
PARENT_FLEX_VALUE, FLEX_VALUE as Child_Flex_Value
from
FND_FLEX_VALUE_CHILDREN_V
Where
FLEX_VALUE_SET_ID = m_acc_value_set_id
and
PARENT_FLEX_VALUE =m_flex_value;
SELECT
FLEX_VALUE_ID, FLEX_VALUE, DESCRIPTION
from
fnd_flex_values_vl where
FLEX_VALUE = m_child_flex_value
-- 4872820 18-Oct-2006 Start-2
-- and FLEX_VALUE_SET_ID = fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET');
select AMW_FIN_KEY_ACCOUNTS_S.nextval into M_ACCOUNT_GROUP_ID from dual;
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW(
X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID,
X_NATURAL_ACCOUNT_ID => M_NATURAL_ACCOUNT_ID,
X_NATURAL_ACCOUNT_VALUE => M_NATURAL_ACCOUNT_VALUE,
X_END_DATE => NULL,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_PARENT_NATURAL_ACCOUNT_ID => NULL );
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW(
X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID,
X_NATURAL_ACCOUNT_ID => C_NATURAL_ACCOUNT_ID,
X_NATURAL_ACCOUNT_VALUE => C_NATURAL_ACCOUNT_VALUE ,
X_END_DATE => NULL,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_PARENT_NATURAL_ACCOUNT_ID => M_PARENT_NATURAL_ACCOUNT_ID);
update AMW_FIN_KEY_ACCOUNTS_B set end_date = sysdate
where ACCOUNT_GROUP_ID<>M_ACCOUNT_GROUP_ID;
Select
FLEX_VALUE_ID,
LANGUAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
SOURCE_LANG,
FLEX_VALUE_MEANING
-- SECURITY_GROUP_ID commented as prd environments did not have this field
from
FND_FLEX_VALUES_TL
where
FLEX_VALUE_ID= m_flex_value_id;
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW_TL(
X_ACCOUNT_GROUP_ID => P_ACCOUNT_GROUP_ID,
X_NATURAL_ACCOUNT_ID => P_NATURAL_ACCOUNT_ID,
X_NAME => M_NAME,
X_LANGUAGE => M_LANGUAGE,
X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE ,
-- X_OBJECT_TYPE ,
X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID ,
X_OBJECT_VERSION_NUMBER => Null,
X_ORIG_SYSTEM_REFERENCE => Null ,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id);
M_LAST_UPDATE_DATE DATE;
M_LAST_UPDATED_BY NUMBER;
M_LAST_UPDATE_LOGIN NUMBER;
'SELECT
NATURAL_ACCOUNT_ID,
NATURAL_ACCOUNT_VALUE,
PARENT_NATURAL_ACCOUNT_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER
from ' || fnd_profile.value('AMW_ACCOUNT_SOURCE_VIEW');
'SELECT
NATURAL_ACCOUNT_ID,
NATURAL_ACCOUNT_VALUE,
PARENT_NATURAL_ACCOUNT_ID
from ' || fnd_profile.value('AMW_ACCOUNT_SOURCE_VIEW') ;
SELECT
*
from
DUAL;
'SELECT
NATURAL_ACCOUNT_ID,
NAME ,
LANGUAGE ,
SOURCE_LANGUAGE
from ' || fnd_profile.value('AMW_ACCOUNT_NAMES_VIEW');
select AMW_FIN_KEY_ACCOUNTS_S.nextval into M_ACCOUNT_GROUP_ID from dual;
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW(
X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID,
X_NATURAL_ACCOUNT_ID => M_NATURAL_ACCOUNT_ID,
X_NATURAL_ACCOUNT_VALUE => M_NATURAL_ACCOUNT_VALUE,
X_END_DATE => NULL,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id ,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_PARENT_NATURAL_ACCOUNT_ID => M_PARENT_NATURAL_ACCOUNT_ID);
AMW_IMPORT_STMNTS_ACCS_PKG.INSERT_ROW_TL(
X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID,
X_NATURAL_ACCOUNT_ID => M_NATURAL_ACCOUNT_ID ,
X_NAME => M_NAME,
X_LANGUAGE => M_LANGUAGE,
X_SOURCE_LANGUAGE => M_SOURCE_LANGUAGE ,
-- X_OBJECT_TYPE ,
X_SECURITY_GROUP_ID => M_SECURITY_GROUP_ID ,
X_OBJECT_VERSION_NUMBER => Null,
X_ORIG_SYSTEM_REFERENCE => Null ,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id ,
X_LAST_UPDATE_LOGIN => g_login_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => g_user_id);
update AMW_FIN_KEY_ACCOUNTS_B set end_date = sysdate
where ACCOUNT_GROUP_ID<>M_ACCOUNT_GROUP_ID;
procedure INSERT_ROW (
X_ACCOUNT_GROUP_ID in out NOCOPY NUMBER,
X_NATURAL_ACCOUNT_ID in NUMBER,
X_NATURAL_ACCOUNT_VALUE in VARCHAR2,
X_END_DATE in DATE,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_CREATION_DATE IN DATE,
X_CREATED_BY in NUMBER,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_PARENT_NATURAL_ACCOUNT_ID in NUMBER) is
-- cursor C is select NATURAL_ACCOUNT_ID from AMW_FIN_KEY_ACCOUNTS_B
-- where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID;
select
NATURAL_ACCOUNT_ID into var_NATURAL_ACCOUNT_ID
from AMW_FIN_KEY_ACCOUNTS_B
where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID AND
NATURAL_ACCOUNT_ID = X_NATURAL_ACCOUNT_ID AND
NVL(PARENT_NATURAL_ACCOUNT_ID,-1)= NVL(X_PARENT_NATURAL_ACCOUNT_ID,-1);
insert into AMW_FIN_KEY_ACCOUNTS_B (
ACCOUNT_GROUP_ID,
NATURAL_ACCOUNT_ID,
NATURAL_ACCOUNT_VALUE,
END_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PARENT_NATURAL_ACCOUNT_ID
) values (
X_ACCOUNT_GROUP_ID ,
X_NATURAL_ACCOUNT_ID ,
X_NATURAL_ACCOUNT_VALUE,
X_END_DATE ,
X_LAST_UPDATE_DATE ,
X_LAST_UPDATED_BY ,
X_LAST_UPDATE_LOGIN ,
X_CREATION_DATE ,
X_CREATED_BY ,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_PARENT_NATURAL_ACCOUNT_ID
);
end INSERT_ROW;
procedure INSERT_ROW_TL (
X_ACCOUNT_GROUP_ID in out NOCOPY NUMBER,
X_NATURAL_ACCOUNT_ID in NUMBER,
X_NAME in VARCHAR2,
X_LANGUAGE in VARCHAR2,
X_SOURCE_LANGUAGE in VARCHAR2,
-- X_OBJECT_TYPE VARCHAR2,
X_SECURITY_GROUP_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_CREATION_DATE DATE,
X_CREATED_BY in NUMBER
) is
-- cursor C is select NATURAL_ACCOUNT_ID from AMW_FIN_KEY_ACCOUNTS_TL
-- where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID ;
select
NATURAL_ACCOUNT_ID into var_NATURAL_ACCOUNT_ID
from AMW_FIN_KEY_ACCOUNTS_TL
where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID AND
NATURAL_ACCOUNT_ID = X_NATURAL_ACCOUNT_ID AND
LANGUAGE = X_LANGUAGE;
insert into AMW_FIN_KEY_ACCOUNTS_TL (
ACCOUNT_GROUP_ID,
NATURAL_ACCOUNT_ID,
NAME,
LANGUAGE,
SOURCE_LANGUAGE,
--OBJECT_TYPE
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
SOURCE_LANG
-- ORIG_SYSTEM_REFERENCE
) values (
X_ACCOUNT_GROUP_ID,
X_NATURAL_ACCOUNT_ID,
X_NAME,
X_LANGUAGE,
X_SOURCE_LANGUAGE,
--X_OBJECT_TYPE,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_SECURITY_GROUP_ID,
X_OBJECT_VERSION_NUMBER,
X_SOURCE_LANGUAGE
--X_ORIG_SYSTEM_REFERENCE
-- userenv('LANG')
);
end INSERT_ROW_TL;
procedure INSERT_STMNT_ROW (
X_STATEMENT_GROUP_ID in NUMBER,
X_FINANCIAL_STATEMENT_ID in NUMBER,
X_END_DATE in DATE,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_SECURITY_GROUP_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER)
is
var_STATEMENT_ID number ;
select
FINANCIAL_STATEMENT_ID into var_STATEMENT_ID
from
AMW_FIN_STMNT_B
where
STATEMENT_GROUP_ID = X_STATEMENT_GROUP_ID and
FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID;
insert into AMW_FIN_STMNT_B(
STATEMENT_GROUP_ID,
FINANCIAL_STATEMENT_ID,
END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER)
values
(
X_STATEMENT_GROUP_ID ,
X_FINANCIAL_STATEMENT_ID ,
X_END_DATE ,
X_LAST_UPDATE_DATE ,
X_LAST_UPDATED_BY ,
X_LAST_UPDATE_LOGIN ,
X_CREATION_DATE ,
X_CREATED_BY ,
X_ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 ,
X_ATTRIBUTE2 ,
X_ATTRIBUTE3 ,
X_ATTRIBUTE4 ,
X_ATTRIBUTE5 ,
X_ATTRIBUTE6 ,
X_ATTRIBUTE7 ,
X_ATTRIBUTE8 ,
X_ATTRIBUTE9 ,
X_ATTRIBUTE10 ,
X_ATTRIBUTE11 ,
X_ATTRIBUTE12 ,
X_ATTRIBUTE13 ,
X_ATTRIBUTE14 ,
X_ATTRIBUTE15 ,
X_SECURITY_GROUP_ID ,
X_OBJECT_VERSION_NUMBER );
END INSERT_STMNT_ROW;
procedure INSERT_STMNT_ROW_TL (
X_STATEMENT_GROUP_ID in NUMBER,
X_FINANCIAL_STATEMENT_ID in NUMBER,
X_NAME in VARCHAR2,
X_LANGUAGE in VARCHAR2,
X_SOURCE_LANGUAGE in VARCHAR2,
X_SECURITY_GROUP_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_CREATION_DATE DATE,
X_CREATED_BY in NUMBER
) is
--cursor C is select NATURAL_ACCOUNT_ID from AMW_FIN_KEY_ACCOUNTS_TL
-- where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID;
select
FINANCIAL_STATEMENT_ID into var_STATEMENT_ID
from
AMW_FIN_STMNT_tl where
STATEMENT_GROUP_ID = X_STATEMENT_GROUP_ID and
FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID and
LANGUAGE =X_LANGUAGE ;
insert into AMW_FIN_STMNT_tl(
STATEMENT_GROUP_ID,
FINANCIAL_STATEMENT_ID,
NAME,
LANGUAGE,
SOURCE_LANGUAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
SOURCE_LANG
) values (
X_STATEMENT_GROUP_ID ,
X_FINANCIAL_STATEMENT_ID ,
X_NAME,
X_LANGUAGE,
X_SOURCE_LANGUAGE,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_SECURITY_GROUP_ID,
X_OBJECT_VERSION_NUMBER,
X_SOURCE_LANGUAGE
--X_ORIG_SYSTEM_REFERENCE
-- userenv('LANG')
);
end INSERT_STMNT_ROW_TL;
procedure INSERT_FINITEM_ROW (
X_STATEMENT_GROUP_ID in NUMBER,
X_FINANCIAL_STATEMENT_ID in NUMBER,
X_FINANCIAL_ITEM_ID IN NUMBER,
X_PARENT_FINANCIAL_ITEM_ID IN NUMBER,
X_SEQUENCE_NUMBER in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_SECURITY_GROUP_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER)
is
var_ITEM_ID number;
select
FINANCIAL_ITEM_ID INTO var_ITEM_ID
from
AMW_FIN_STMNT_ITEMS_B
where
STATEMENT_GROUP_ID = X_STATEMENT_GROUP_ID and
FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID and
FINANCIAL_ITEM_ID = X_FINANCIAL_ITEM_ID and
nvl(PARENT_FINANCIAL_ITEM_ID,-1) = nvl(X_PARENT_FINANCIAL_ITEM_ID,-1);
insert into AMW_FIN_STMNT_ITEMS_B(
STATEMENT_GROUP_ID,
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
PARENT_FINANCIAL_ITEM_ID,
SEQUENCE_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER)
values
(
X_STATEMENT_GROUP_ID ,
X_FINANCIAL_STATEMENT_ID ,
X_FINANCIAL_ITEM_ID ,
X_PARENT_FINANCIAL_ITEM_ID ,
X_SEQUENCE_NUMBER ,
X_LAST_UPDATE_DATE ,
X_LAST_UPDATED_BY ,
X_LAST_UPDATE_LOGIN ,
X_CREATION_DATE ,
X_CREATED_BY ,
X_ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 ,
X_ATTRIBUTE2 ,
X_ATTRIBUTE3 ,
X_ATTRIBUTE4 ,
X_ATTRIBUTE5 ,
X_ATTRIBUTE6 ,
X_ATTRIBUTE7 ,
X_ATTRIBUTE8 ,
X_ATTRIBUTE9 ,
X_ATTRIBUTE10 ,
X_ATTRIBUTE11 ,
X_ATTRIBUTE12 ,
X_ATTRIBUTE13 ,
X_ATTRIBUTE14 ,
X_ATTRIBUTE15 ,
X_SECURITY_GROUP_ID ,
X_OBJECT_VERSION_NUMBER );
END INSERT_FINITEM_ROW ;
procedure INSERT_FINITEM_ROW_TL (
X_STATEMENT_GROUP_ID in NUMBER,
X_FINANCIAL_STATEMENT_ID in NUMBER,
X_FINANCIAL_ITEM_ID IN NUMBER,
X_NAME in VARCHAR2,
X_LANGUAGE in VARCHAR2,
X_SOURCE_LANGUAGE in VARCHAR2,
X_SECURITY_GROUP_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_ORIG_SYSTEM_REFERENCE in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_CREATION_DATE DATE,
X_CREATED_BY in NUMBER
) is
--cursor C is select NATURAL_ACCOUNT_ID from AMW_FIN_KEY_ACCOUNTS_TL
-- where ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID;
select FINANCIAL_ITEM_ID INTO var_ITEM_ID
from
AMW_FIN_STMNT_ITEMS_tl
where
STATEMENT_GROUP_ID = X_STATEMENT_GROUP_ID and
FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID and
FINANCIAL_ITEM_ID = X_FINANCIAL_ITEM_ID and
LANGUAGE=X_LANGUAGE ;
insert into AMW_FIN_STMNT_ITEMS_TL(
STATEMENT_GROUP_ID,
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
NAME,
LANGUAGE,
SOURCE_LANGUAGE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
SOURCE_LANG
) values (
X_STATEMENT_GROUP_ID ,
X_FINANCIAL_STATEMENT_ID ,
X_FINANCIAL_ITEM_ID ,
X_NAME,
X_LANGUAGE,
X_SOURCE_LANGUAGE,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_SECURITY_GROUP_ID,
X_OBJECT_VERSION_NUMBER,
X_SOURCE_LANGUAGE
--X_ORIG_SYSTEM_REFERENCE
-- userenv('LANG')
);
end INSERT_FINITEM_ROW_TL ;
procedure INSERT_FINITEM_ACC_MAP (
X_STATEMENT_GROUP_ID in NUMBER,
X_ACCOUNT_GROUP_ID in NUMBER,
X_FINANCIAL_STATEMENT_ID in NUMBER,
X_FINANCIAL_ITEM_ID IN NUMBER,
X_NATURAL_ACCOUNT_ID in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_SECURITY_GROUP_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER)
is
begin
DECLARE
itmacc_count NUMBER :=0;
select count(1) into itmacc_count
from
AMW_FIN_ITEMS_KEY_ACC
where
STATEMENT_GROUP_ID =X_STATEMENT_GROUP_ID and
ACCOUNT_GROUP_ID = X_ACCOUNT_GROUP_ID and
FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID and
FINANCIAL_ITEM_ID = X_FINANCIAL_ITEM_ID and
NATURAL_ACCOUNT_ID = X_NATURAL_ACCOUNT_ID ;
insert into AMW_FIN_ITEMS_KEY_ACC(
STATEMENT_GROUP_ID,
ACCOUNT_GROUP_ID ,
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
NATURAL_ACCOUNT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER)
values
(
X_STATEMENT_GROUP_ID ,
X_ACCOUNT_GROUP_ID ,
X_FINANCIAL_STATEMENT_ID ,
X_FINANCIAL_ITEM_ID ,
X_NATURAL_ACCOUNT_ID ,
X_LAST_UPDATE_DATE ,
X_LAST_UPDATED_BY ,
X_LAST_UPDATE_LOGIN ,
X_CREATION_DATE ,
X_CREATED_BY ,
X_ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 ,
X_ATTRIBUTE2 ,
X_ATTRIBUTE3 ,
X_ATTRIBUTE4 ,
X_ATTRIBUTE5 ,
X_ATTRIBUTE6 ,
X_ATTRIBUTE7 ,
X_ATTRIBUTE8 ,
X_ATTRIBUTE9 ,
X_ATTRIBUTE10 ,
X_ATTRIBUTE11 ,
X_ATTRIBUTE12 ,
X_ATTRIBUTE13 ,
X_ATTRIBUTE14 ,
X_ATTRIBUTE15 ,
X_SECURITY_GROUP_ID ,
X_OBJECT_VERSION_NUMBER );
END INSERT_FINITEM_ACC_MAP;
select fnd_profile.value('AMW_ACCOUNT_SOURCE_VIEW') into M_AMW_ACCOUNT_SOURCE_VIEW from dual;
select fnd_profile.value('AMW_ACCOUNT_NAMES_VIEW' ) into M_AMW_ACCOUNT_NAMES_VIEW from dual;
select fnd_profile.value('AMW_STMNT_SOURCE_VIEW') into M_AMW_STMNT_SOURCE_VIEW from dual;
select fnd_profile.value('AMW_FINITEM_SOURCE_VIEW' ) into M_AMW_FINITEM_SOURCE_VIEW from dual;
select fnd_profile.value('AMW_FIN_ITEM_ACC_RELATIONS_VIEW' ) into M_AMW_FIN_ITEM_ACC_MAP_VIEW from dual;
select fnd_profile.value('AMW_STMNT_SOURCE_TL_VIEW' ) into M_AMW_STMNT_SOURCE_TL_VIEW from dual;
select fnd_profile.value('AMW_FINITEM_SOURCE_TL_VIEW' ) into M_AMW_FINITEM_SOURCE_TL_VIEW from dual;
select count(account_group_id) into M_acct_count from amw_fin_key_Accounts_b where END_DATE is null;
select fnd_profile.value('AMW_NATRL_ACCT_VALUE_SET') into acc_value_set_id from dual ;
select distinct natural_account_id acct_id, account_group_id group_id
from amw_fin_key_accounts_b
where account_group_id = x_group_id ;
select p_acct_id parent_id,
acct.natural_account_id child_id,
acct.account_group_id group_id
from AMW_FIN_KEY_ACCOUNTS_B acct
start with account_group_id = p_group_id
and parent_natural_account_id = p_acct_id
connect by prior natural_account_id = parent_natural_account_id
and account_group_id = p_group_id;
insert into amw_fin_key_acct_flat
( parent_natural_account_id,
child_natural_account_id,
account_group_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER
)
values
( nested_rec.parent_id,
nested_rec.child_id,
nested_rec.group_id,
g_user_id,
sysdate,
g_user_id,
sysdate,
g_login_id,
null,
null
);
select distinct items.financial_item_id item_id,
items.statement_group_id group_id,
items.financial_statement_id stmt_id
from amw_fin_stmnt_items_b items
where items.statement_group_id = x_group_id;
select p_item_id parent_id,
items.financial_item_id child_id,
items.statement_group_id group_id,
items.financial_statement_id stmt_id
from amw_fin_stmnt_items_b items
start with items.statement_group_id = p_group_id
and items.parent_financial_item_id = p_item_id
and items.financial_statement_id = p_stmt_id
connect by prior items.financial_item_id = items.parent_financial_item_id
and items.statement_group_id = p_group_id
and items.financial_statement_id = p_stmt_id;
insert into amw_fin_item_flat
( parent_financial_item_id,
child_financial_item_id,
statement_group_id,
financial_statement_id,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER
)
values
( nested_rec.parent_id,
nested_rec.child_id,
nested_rec.group_id,
nested_rec.stmt_id,
g_user_id, sysdate, g_user_id, sysdate, g_login_id, null, null
);