[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT period_name
INTO l_period_name
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND start_date <= p_gl_date
AND End_Date >= p_gl_date
AND period_type = p_period_type
AND adjustment_period_flag = 'N';
SELECT
DECODE(FSAV.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of gcc
'SEGMENT1',GCC.SEGMENT1, 'SEGMENT2',GCC.SEGMENT2, 'SEGMENT3',GCC.SEGMENT3,
'SEGMENT4',GCC.SEGMENT4, 'SEGMENT5',GCC.SEGMENT5, 'SEGMENT6',GCC.SEGMENT6,
'SEGMENT7',GCC.SEGMENT7, 'SEGMENT8',GCC.SEGMENT8, 'SEGMENT9',GCC.SEGMENT9,
'SEGMENT10',GCC.SEGMENT10, 'SEGMENT11',GCC.SEGMENT11, 'SEGMENT12',GCC.SEGMENT12,
'SEGMENT13',GCC.SEGMENT13, 'SEGMENT14',GCC.SEGMENT14, 'SEGMENT15',GCC.SEGMENT15,
'SEGMENT16',GCC.SEGMENT16, 'SEGMENT17',GCC.SEGMENT17, 'SEGMENT18',GCC.SEGMENT18,
'SEGMENT19',GCC.SEGMENT19, 'SEGMENT20',GCC.SEGMENT20, 'SEGMENT21',GCC.SEGMENT21,
'SEGMENT22',GCC.SEGMENT22, 'SEGMENT23',GCC.SEGMENT23, 'SEGMENT24',GCC.SEGMENT24,
'SEGMENT25',GCC.SEGMENT25, 'SEGMENT26',GCC.SEGMENT26, 'SEGMENT27',GCC.SEGMENT27,
'SEGMENT28',GCC.SEGMENT28, 'SEGMENT29',GCC.SEGMENT29, 'SEGMENT30',GCC.SEGMENT30)
INTO L_BALANCING_SEGMENT
FROM GL_CODE_COMBINATIONS GCC,
FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE GCC.CODE_COMBINATION_ID = P_CC_ID
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.APPLICATION_ID = 101
AND FSAV.ID_FLEX_CODE = 'GL#'--Fix bug#7334017 add
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID;
PROCEDURE insert_SLA_data(P_COA_ID IN NUMBER,
P_LEDGER_ID IN NUMBER,
P_LE_ID IN NUMBER,
p_period_set_name IN VARCHAR2,
p_application_id IN NUMBER,
p_ae_header_id IN NUMBER,
p_ae_line_num IN VARCHAR2,
p_transaction_date IN DATE,
p_period_type IN VARCHAR2,
p_func_currency_code IN VARCHAR2,
p_currency_code IN VARCHAR2,
p_currency_conversion_rate IN NUMBER,
p_currency_conversion_type IN VARCHAR2,
p_currency_conversion_date IN DATE,
p_detailed_cfs_item IN VARCHAR2,
p_EVENT_CLASS_CODE IN VARCHAR2,
--p_application_id IN NUMBER,
p_ANALYTICAL_CRITERION_CODE IN VARCHAR2,
p_ac_value IN VARCHAR2,
p_cash_date IN DATE,
p_accounting_class IN VARCHAR2,
p_cash_amount_cr IN NUMBER,
p_cash_amount_dr IN NUMBER,
p_accounted_dr IN NUMBER,
p_entered_dr IN NUMBER,
p_accounted_cr IN NUMBER,
p_entered_cr IN NUMBER,
p_ccid In VARCHAR2) AS--Fix bug#7334017 add
L_GL_date ja_cn_cfs_activities_all.gl_date%type;
SELECT application_short_name
INTO l_application_source
FROM fnd_application
WHERE application_id = p_application_id;
SELECT Ffvt.DESCRIPTION
INTO l_detailed_item_desc
FROM Fnd_Flex_Values_Tl Ffvt,
fnd_flex_values Ffv,
ja_cn_cash_valuesets_all Cra
WHERE Cra.Chart_Of_Accounts_Id = p_coa_id
AND Ffv.Flex_Value_Set_Id = Cra.Flex_Value_Set_Id
AND Ffv.Flex_Value_Id = Ffvt.Flex_Value_Id
AND ffvt.flex_value_meaning = p_detailed_cfs_item
AND ffvt.LANGUAGE = userenv('LANG');
INSERT INTO ja_cn_cfs_activities_all(CFS_ACTIVITY_ID,
LEGAL_ENTITY_ID,
LEDGER_ID,
ORG_ID,
TRX_ID,
TRX_NUMBER,
TRX_LINE_ID,
SOURCE,
TRANSACTION_TYPE,
DOCUMENT_SEQUENCE_NUMBER,
TRANSACTION_DATE,
GL_DATE,
PERIOD_NAME,
FUNC_CURR_CODE,
FUNC_AMOUNT,
ORIGINAL_CURR_CODE,
ORIGINAL_AMOUNT,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
DESCRIPTION,
DETAILED_CFS_ITEM,
INTERCOMPANY_FLAG,
REFERENCE_NUMBER,
THIRD_PARTY_NAME,
THIRD_PARTY_NUMBER,
EVENT_CLASS_CODE,
SOURCE_APPLICATION_ID,
ANALYTICAL_CRITERION_CODE,
SOURCE_VALUE,
CASH_ITEM_DESC ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
balancing_segment)--Fix bug#7334017 add
VALUES(
ja_cn_cfs_activities_s.NEXTVAL
,p_le_id
,p_ledger_id
,NULL
,p_ae_header_id
,NULL
,p_ae_line_num
,l_application_source--p_source
,'SLA'
,NULL
,p_transaction_date
,l_gl_date
,l_period_name
,p_func_currency_code
,l_func_amount
,p_currency_code
,l_orig_amount
,p_currency_conversion_rate
,p_currency_conversion_type
,p_currency_conversion_date
,''
,p_detailed_cfs_item
,''
,p_ae_header_id
,''
,NULL
,p_EVENT_CLASS_CODE
,p_application_id
,p_ANALYTICAL_CRITERION_CODE
,p_ac_value
,l_detailed_item_desc
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.LOGIN_ID
,get_balancing_segment(p_ccid));--Fix bug#7334017 add
'SELECT al.ae_header_id,
al.ae_line_num,
al.accounting_date,
al.accounted_dr,
al.accounted_cr,
al.entered_dr,
al.entered_cr,
al.currency_code,
al.currency_conversion_rate,
al.currency_conversion_type,
al.currency_conversion_date,
al.application_id,
ala.Analytical_Criterion_Code,
ala.analytical_criterion_type_code,
ala.amb_context_code,
ala.ac1,
al.code_combination_id
FROM xla_ae_lines al
,xla_ae_headers ah
,xla_ae_line_acs ala
,fnd_segment_attribute_values fsav
,gl_code_combinations gcc
,ja_cn_ledger_le_bsv_gt glsv
WHERE ah.gl_transfer_status_code = ''Y''
AND ah.event_type_code NOT IN (''PAYMENT CANCELLED'',''RECP_REVERSE'')
AND al.ae_header_id = ah.ae_header_id
--AND al.analytical_balance_flag IS NOT NULL
AND ala.ae_header_id IN (SELECT ae_header_id
FROM xla_ae_line_acs
WHERE analytical_criterion_code = ''CHECK_ID''
AND amb_context_code = ''DEFAULT''
AND analytical_criterion_type_code = ''S''
AND ac1= :l_transaction_num)
AND al.ae_header_id = ala.ae_header_id
AND al.ae_line_num = ala.ae_line_num
AND al.ledger_id = :p_ledger_id
AND ala.Analytical_Criterion_Code <> ''CHECK_ID''
AND al.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = :p_coa_id
AND fsav.application_id = 101
AND fsav.id_flex_num = gcc.chart_of_accounts_id
AND fsav.attribute_value = ''Y''
AND fsav.segment_attribute_type = ''GL_BALANCING''
AND FSAV.ID_FLEX_CODE = ''GL#''
AND glsv.ledger_id = :p_ledger_id
AND glsv.legal_entity_id = :p_le_id
@source_sql
AND DECODE(FSAV.APPLICATION_COLUMN_NAME,
''SEGMENT1'',GCC.SEGMENT1, ''SEGMENT2'',GCC.SEGMENT2, ''SEGMENT3'',GCC.SEGMENT3,
''SEGMENT4'',GCC.SEGMENT4, ''SEGMENT5'',GCC.SEGMENT5, ''SEGMENT6'',GCC.SEGMENT6,
''SEGMENT7'',GCC.SEGMENT7, ''SEGMENT8'',GCC.SEGMENT8, ''SEGMENT9'',GCC.SEGMENT9,
''SEGMENT10'',GCC.SEGMENT10, ''SEGMENT11'',GCC.SEGMENT11, ''SEGMENT12'',GCC.SEGMENT12,
''SEGMENT13'',GCC.SEGMENT13, ''SEGMENT14'',GCC.SEGMENT14, ''SEGMENT15'',GCC.SEGMENT15,
''SEGMENT16'',GCC.SEGMENT16, ''SEGMENT17'',GCC.SEGMENT17, ''SEGMENT18'',GCC.SEGMENT18,
''SEGMENT19'',GCC.SEGMENT19, ''SEGMENT20'',GCC.SEGMENT20, ''SEGMENT21'',GCC.SEGMENT21,
''SEGMENT22'',GCC.SEGMENT22, ''SEGMENT23'',GCC.SEGMENT23, ''SEGMENT24'',GCC.SEGMENT24,
''SEGMENT25'',GCC.SEGMENT25, ''SEGMENT26'',GCC.SEGMENT26, ''SEGMENT27'',GCC.SEGMENT27,
''SEGMENT28'',GCC.SEGMENT28, ''SEGMENT29'',GCC.SEGMENT29, ''SEGMENT30'',GCC.SEGMENT30) = glsv.bal_seg_value';
'SELECT al.accounting_date
,al.accounting_class_code
,al.accounted_cr
,al.accounted_dr
,ala.ac1
FROM xla_ae_lines al
,xla_ae_headers ah
,xla_ae_line_acs ala
,fnd_segment_attribute_values fsav
,fnd_segment_attribute_values fsav1
,gl_code_combinations gcc
,gl_code_combinations gcc1
,ja_cn_ledger_le_bsv_gt glsv
,ja_cn_cash_accounts_all jca
WHERE al.accounting_date >= :l_date_from
AND al.accounting_date < :l_date_to + 1
AND ah.gl_transfer_status_code = ''Y''
AND al.ae_header_id = ah.ae_header_id
and al.ae_header_id = ala.ae_header_id
AND al.ae_line_num = ala.ae_line_num
--AND al.analytical_balance_flag IS NOT NULL
AND ala.analytical_criterion_code = ''CHECK_ID''
AND ala.analytical_criterion_type_code = ''S''
AND ala.amb_context_code = ''DEFAULT''
--AND ala.ac1 = :l_transaction_num
AND al.ledger_id = :p_ledger_id
AND (al.accounting_class_code =''CASH''
OR (al.accounting_class_code IN (SELECT class_code
FROM ja_cn_accounting_classes_all
WHERE chart_of_accounts_id = :p_coa_id )))
AND al.code_combination_id = gcc1.code_combination_id
AND gcc1.chart_of_accounts_id = :p_coa_id
AND fsav1.application_id = 101
AND fsav1.id_flex_num = :p_coa_id
AND fsav1.attribute_value = ''Y''
AND fsav1.segment_attribute_type = ''GL_BALANCING''
AND FSAV1.ID_FLEX_CODE = ''GL#''
AND glsv.ledger_id = :p_ledger_id
AND glsv.legal_entity_id = :p_le_id
AND DECODE(FSAV1.APPLICATION_COLUMN_NAME,
''SEGMENT1'',GCC1.SEGMENT1, ''SEGMENT2'',GCC1.SEGMENT2, ''SEGMENT3'',GCC1.SEGMENT3,
''SEGMENT4'',GCC1.SEGMENT4, ''SEGMENT5'',GCC1.SEGMENT5, ''SEGMENT6'',GCC1.SEGMENT6,
''SEGMENT7'',GCC1.SEGMENT7, ''SEGMENT8'',GCC1.SEGMENT8, ''SEGMENT9'',GCC1.SEGMENT9,
''SEGMENT10'',GCC1.SEGMENT10, ''SEGMENT11'',GCC1.SEGMENT11, ''SEGMENT12'',GCC1.SEGMENT12,
''SEGMENT13'',GCC1.SEGMENT13, ''SEGMENT14'',GCC1.SEGMENT14, ''SEGMENT15'',GCC1.SEGMENT15,
''SEGMENT16'',GCC1.SEGMENT16, ''SEGMENT17'',GCC1.SEGMENT17, ''SEGMENT18'',GCC1.SEGMENT18,
''SEGMENT19'',GCC1.SEGMENT19, ''SEGMENT20'',GCC1.SEGMENT20, ''SEGMENT21'',GCC1.SEGMENT21,
''SEGMENT22'',GCC1.SEGMENT22, ''SEGMENT23'',GCC1.SEGMENT23, ''SEGMENT24'',GCC1.SEGMENT24,
''SEGMENT25'',GCC1.SEGMENT25, ''SEGMENT26'',GCC1.SEGMENT26, ''SEGMENT27'',GCC1.SEGMENT27,
''SEGMENT28'',GCC1.SEGMENT28, ''SEGMENT29'',GCC1.SEGMENT29, ''SEGMENT30'',GCC1.SEGMENT30) = glsv.bal_seg_value
AND al.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = :p_coa_id
AND FSAV.ATTRIBUTE_VALUE = ''Y''
AND FSAV.APPLICATION_ID = 101
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = ''GL_ACCOUNT''
AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
AND FSAV.ID_FLEX_CODE = ''GL#''
AND JCA.chart_of_accounts_id = :p_coa_id
@source_sql
AND DECODE(FSAV.APPLICATION_COLUMN_NAME,
''SEGMENT1'',GCC.SEGMENT1, ''SEGMENT2'',GCC.SEGMENT2, ''SEGMENT3'',GCC.SEGMENT3,
''SEGMENT4'',GCC.SEGMENT4, ''SEGMENT5'',GCC.SEGMENT5, ''SEGMENT6'',GCC.SEGMENT6,
''SEGMENT7'',GCC.SEGMENT7, ''SEGMENT8'',GCC.SEGMENT8, ''SEGMENT9'',GCC.SEGMENT9,
''SEGMENT10'',GCC.SEGMENT10, ''SEGMENT11'',GCC.SEGMENT11, ''SEGMENT12'',GCC.SEGMENT12,
''SEGMENT13'',GCC.SEGMENT13, ''SEGMENT14'',GCC.SEGMENT14, ''SEGMENT15'',GCC.SEGMENT15,
''SEGMENT16'',GCC.SEGMENT16, ''SEGMENT17'',GCC.SEGMENT17, ''SEGMENT18'',GCC.SEGMENT18,
''SEGMENT19'',GCC.SEGMENT19, ''SEGMENT20'',GCC.SEGMENT20, ''SEGMENT21'',GCC.SEGMENT21,
''SEGMENT22'',GCC.SEGMENT22, ''SEGMENT23'',GCC.SEGMENT23, ''SEGMENT24'',GCC.SEGMENT24,
''SEGMENT25'',GCC.SEGMENT25, ''SEGMENT26'',GCC.SEGMENT26, ''SEGMENT27'',GCC.SEGMENT27,
''SEGMENT28'',GCC.SEGMENT28, ''SEGMENT29'',GCC.SEGMENT29, ''SEGMENT30'',GCC.SEGMENT30) IN JCA.ACCOUNT_SEGMENT_VALUE';
SELECT currency_code, accounted_period_type
INTO l_func_currency_code, l_period_type
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
SELECT period_year * 1000 + period_num, start_date
INTO l_period_num_from, l_date_from
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_name = P_GL_PERIOD_FROM
AND period_type = l_period_type;
SELECT period_year * 1000 + period_num, end_date
INTO l_period_num_to, l_date_to
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_name = P_GL_PERIOD_to
AND period_type = l_period_type;
DELETE ja_cn_cfs_activities_all ca
WHERE ca.legal_entity_id = P_LE_ID
AND ledger_id = p_ledger_id
AND ca.SOURCE_application_id NOT IN (101, 435)
AND ca.period_name IN
(SELECT period_name
FROM Gl_Periods
WHERE period_set_name = p_period_set_name
AND period_year * 1000 + period_num BETWEEN
l_period_num_from AND l_period_num_to);
SELECT application_id
INTO l_source_id
FROM fnd_application
WHERE application_short_name = p_source;
DELETE ja_cn_cfs_activities_all ca
WHERE ca.legal_entity_id = P_LE_ID
AND ledger_id = p_ledger_id
AND ca.SOURCE_application_id = l_source_id
AND ca.period_name IN
(SELECT period_name
FROM Gl_Periods
WHERE period_set_name = p_period_set_name
AND period_year * 1000 + period_num BETWEEN
l_period_num_from AND l_period_num_to);
Select xte.SECURITY_ID_INT_1
Into l_line_org_id
FROM XLA_TRANSACTION_ENTITIES xte, xla_ae_headers xah
Where xte.entity_id = xah.entity_id
And xte.application_id = xah.application_id
And xah.ae_header_id = l_ae_header_id
AND xah.application_id = l_application_id;
Select jccl.detailed_cfs_item, jccl.org_id, jcch.event_class_code
Into l_detailed_cfs_item, L_mapping_org_id, l_event_class_code
From ja_cn_cfs_item_mapping_hdrs jcch,
ja_cn_cfs_item_mapping_lines jccl
Where jcch.Chart_of_Accounts_id = p_coa_id
And jcch.analytical_criterion_code = l_analytical_criterion_code
AND jcch.analytical_criterion_type_code = l_ac_type_code
AND jcch.amb_context_code = l_amb_context_code
And jcch.mapping_header_id = jccl.mapping_header_id
And jccl.ac_value = l_ac_value
AND nvl(jccl.effective_start_date, l_transaction_date) <=l_transaction_date
AND nvl(jccl.effective_end_date,l_transaction_date) >=l_transaction_date
And jccl.org_id = l_line_org_id;
Select jccl.detailed_cfs_item,jcch.event_class_code
Into l_detailed_cfs_item,l_event_class_code
From ja_cn_cfs_item_mapping_hdrs jcch,
ja_cn_cfs_item_mapping_lines jccl
Where jcch.Chart_of_Accounts_id = p_coa_id
And jcch.analytical_criterion_code = l_analytical_criterion_code
AND jcch.analytical_criterion_type_code = l_ac_type_code
AND jcch.amb_context_code = l_amb_context_code
And jcch.mapping_header_id = jccl.mapping_header_id
And jccl.ac_value = l_ac_value
AND nvl(jccl.effective_start_date,l_transaction_date) <= l_transaction_date
AND nvl(jccl.effective_end_date, l_transaction_date) >= l_transaction_date
And jccl.org_id is NULL;
insert_SLA_data(P_COA_ID,
P_LEDGER_ID,
P_LE_ID,
p_period_set_name,
l_application_id,
l_ae_header_id,
l_ae_line_num,
l_transaction_date,
l_period_type,
l_func_currency_code,
l_currency_code,
l_currency_conversion_rate,
l_currency_conversion_type,
l_currency_conversion_date,
l_detailed_cfs_item,
l_EVENT_CLASS_CODE,
--l_application_id,
l_ANALYTICAL_CRITERION_CODE,
l_ac_value,
l_cash_date,
l_accounting_class,
l_cash_amount_cr,
l_cash_amount_dr,
l_accounted_dr,
l_entered_dr,
l_accounted_cr,
l_entered_cr,
l_ccid);--Fix bug#7334017 add
insert_SLA_data(P_COA_ID,
P_LEDGER_ID,
P_LE_ID,
p_period_set_name,
l_application_id,
l_ae_header_id,
l_ae_line_num,
l_transaction_date,
l_period_type,
l_func_currency_code,
l_currency_code,
l_currency_conversion_rate,
l_currency_conversion_type,
l_currency_conversion_date,
l_detailed_cfs_item,
l_EVENT_CLASS_CODE,
--l_application_id,
l_ANALYTICAL_CRITERION_CODE,
l_ac_value,
l_cash_date,
l_accounting_class,
l_cash_amount_cr,
l_cash_amount_dr,
l_accounted_dr,
l_entered_dr,
l_accounted_cr,
l_entered_cr ,
l_ccid);--Fix bug#7334017 add