[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT FLV.meaning source
INTO l_source
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.lookup_code = P_SOURCE --using parameter P_SOURCE: 'GLJE'/'INTR'
AND FLV.lookup_type = 'JA_CN_CASHFLOW_SRC_TYPE'
AND FLV.LANGUAGE = userenv('LANG')
;
SELECT FFV.Flex_Value ffv_flex_value
,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)
seg_fsav_gcc
FROM GL_CODE_COMBINATIONS gcc
,GL_LEDGERS ledger
,FND_ID_FLEX_SEGMENTS FIFS
,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
,FND_FLEX_VALUE_SETS FFVS
,FND_FLEX_VALUES FFV
WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
AND ledger.chart_of_accounts_id = gcc.chart_of_accounts_id
AND ledger.ledger_id = l_ledger_id --using variable l_sob_id
AND FIFS.id_flex_num = gcc.chart_of_accounts_id
AND FIFS.id_flex_num = FSAV.id_flex_num
AND FIFS.application_id = 101
AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FIFS.application_id = FSAV.application_id
AND FSAV.SEGMENT_ATTRIBUTE_TYPE = l_seg_type --using variable l_seg_type
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
;
SELECT count(*) row_count
INTO l_account_check
FROM JA_CN_CASH_ACCOUNTS_ALL cash_acc
WHERE cash_acc.ACCOUNT_SEGMENT_VALUE = l_account_num --using variable l_account_num
AND cash_acc.chart_of_accounts_id = l_coa_id --using variable l_coa_id
;
SELECT count(*) row_count
INTO l_com_seg_check
FROM JA_CN_LEDGER_LE_BSV_GT tmpbsv
WHERE tmpbsv.LEGAL_ENTITY_ID = l_le_id --using variable l_le_id
AND tmpbsv.ledger_id = l_ledger_id --using variable l_ledger_id
AND tmpbsv.bal_seg_value = l_com_seg; --using variable l_com_seg
SELECT decode(jel.context, dffa.context_code,
decode(dffa.attribute_column, 'ATTRIBUTE1',jel.attribute1, 'ATTRIBUTE2',jel.attribute2,
'ATTRIBUTE3',jel.attribute3, 'ATTRIBUTE4',jel.attribute4, 'ATTRIBUTE5',jel.attribute5,
'ATTRIBUTE6',jel.attribute6, 'ATTRIBUTE7',jel.attribute7, 'ATTRIBUTE8',jel.attribute8,
'ATTRIBUTE9',jel.attribute9, 'ATTRIBUTE10',jel.attribute10, 'ATTRIBUTE11',jel.attribute11,
'ATTRIBUTE12',jel.attribute12, 'ATTRIBUTE13',jel.attribute13, 'ATTRIBUTE14',jel.attribute14,
'ATTRIBUTE15',jel.attribute15)
) cash_related_item
INTO l_cash_flow_item_from_GL
FROM gl_je_lines jel
, fun_trx_headers trxh
, fun_trx_lines trxl
, fun_dist_lines distl
,ja_cn_dff_assignments dffa
WHERE distl.dist_id = l_jt_id -- transaction header id
AND distl.line_id=trxl.line_id
AND trxh.trx_id = trxl.trx_id
AND jel.reference_2 = TO_CHAR(trxh.batch_id)
AND jel.reference_3 = TO_CHAR(trxh.trx_id)
AND jel.reference_4 = TO_CHAR(trxl.line_id)
AND jel.reference_5 = TO_CHAR(distl.dist_id)
AND jel.ledger_id=l_ledger_id -- care only current ledgers'
--AND jel.status='P' -- care only post journels from trxes --?? NOT SURE
-- to locate cash flow item in dff_assignment
AND dffa.Application_Id = 101
AND dffa.chart_of_accounts_id = l_coa_id --using variable l_coa_id
AND dffa.dff_title_code='GLLI';
SELECT FIFStr.Concatenated_Segment_Delimiter
INTO l_delimiter_label
FROM GL_CODE_COMBINATIONS gcc
,FND_ID_FLEX_STRUCTURES FIFStr
WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
AND FIFStr.APPLICATION_ID=101
AND FIFStr.ID_FLEX_CODE='GL#'
AND FIFStr.ID_FLEX_NUM = gcc.chart_of_accounts_id
;
SELECT FIFS.SEGMENT_NUM
,FIFS.Segment_Name
,FIFS.APPLICATION_COLUMN_NAME
,FIFS.FLEX_VALUE_SET_ID
BULK COLLECT INTO l_all_segemnts
FROM GL_CODE_COMBINATIONS gcc
,FND_ID_FLEX_SEGMENTS FIFS
WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
AND FIFS.APPLICATION_ID=101
AND FIFS.ID_FLEX_CODE='GL#' -- using standard flex code, without it the output will be reduplicate
AND FIFS.ID_FLEX_NUM = gcc.chart_of_accounts_id
ORDER BY FIFS.SEGMENT_NUM
;
'SELECT DISTINCT '
||' FIFS.SEGMENT_NUM seg_num' --the output will order by it!
||' ,FIFS.SEGMENT_NAME seg_name'
||' ,FFV.Flex_Value flex_value'
||' ,FFVT.Description flex_desc'
||' BULK COLLECT INTO :1'
||' FROM GL_CODE_COMBINATIONS gcc'
||' ,GL_SETS_OF_BOOKS sob'
||' ,FND_ID_FLEX_SEGMENTS FIFS'
||' ,FND_SEGMENT_ATTRIBUTE_VALUES FSAV'
||' ,FND_FLEX_VALUE_SETS FFVS'
||' ,FND_FLEX_VALUES FFV'
||' ,FND_FLEX_VALUES_TL FFVT'
||' WHERE gcc.code_combination_id = :2' --using variable l_cc_id
||' AND sob.chart_of_accounts_id = gcc.chart_of_accounts_id'
||' AND sob.set_of_books_id = :3' --using variable l_sob_id
||' AND FIFS.id_flex_num = gcc.chart_of_accounts_id'
||' AND FIFS.id_flex_num = FSAV.id_flex_num'
||' AND FIFS.application_id = 101'
||' AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME'
--AND FSAV.SEGMENT_ATTRIBUTE_TYPE = l_seg_type --Just not check the type!
||' AND FIFS.application_id = FSAV.application_id'
||' AND FSAV.ATTRIBUTE_VALUE = ''Y'''
||' AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID'
||' AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID'
||' AND GCC.' || FSAV.APPLICATION_COLUMN_NAME || ' = FFV.Flex_Value'
||' and FFVT.flex_value_id = FFV.flex_value_id'
||' and nvl(FFVT.LANGUAGE, userenv(''LANG'')) = userenv(''LANG'')';
SELECT DISTINCT
FIFS.SEGMENT_NUM seg_num --the output will order by it!
,FIFS.SEGMENT_NAME seg_name
,FFV.Flex_Value flex_value
,FFVT.Description flex_desc
BULK COLLECT INTO l_all_acc_seg
FROM GL_CODE_COMBINATIONS gcc
,GL_LEDGERS ledger
,FND_ID_FLEX_SEGMENTS FIFS
,FND_SEGMENT_ATTRIBUTE_VALUES FSAV
,FND_FLEX_VALUE_SETS FFVS
,FND_FLEX_VALUES FFV
,FND_FLEX_VALUES_TL FFVT
WHERE gcc.code_combination_id = l_cc_id --using variable l_cc_id
AND ledger.chart_of_accounts_id = gcc.chart_of_accounts_id
AND ledger.ledger_id = l_ledger_id --using variable l_ledger_id
AND FIFS.id_flex_num = gcc.chart_of_accounts_id
AND FIFS.id_flex_num = FSAV.id_flex_num
AND FIFS.application_id = 101
AND FIFS.ID_FLEX_CODE='GL#' -- using standard flex code, without it the output will be reduplicate
AND FIFS.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
--AND FSAV.SEGMENT_ATTRIBUTE_TYPE = l_seg_type --Just not check the type!
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FIFS.application_id = FSAV.application_id
AND FFVS.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
AND 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) = FFV.Flex_Value
AND FFVT.flex_value_id = FFV.flex_value_id
AND nvl(FFVT.LANGUAGE, userenv('LANG')) = userenv('LANG')
;
SELECT gp.period_name, gp.period_year, gp.period_num
FROM gl_periods gp, GL_LEDGERS ledger
WHERE ledger.ledger_id = l_ledger_id --using variable P_LEDGER_ID
AND ledger.period_set_name = gp.PERIOD_SET_NAME
AND ledger.accounted_period_type = gp.period_type
AND gp.start_date between
(SELECT start_date
FROM GL_PERIODS GP
WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.period_name = l_period_from) --using parameter P_START_PERIOD
and (SELECT start_date
FROM GL_PERIODS GP
WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND GP.period_name = l_period_to) --using parameter P_END_PERIOD
ORDER BY gp.start_date
;
SELECT jec.je_category_name catg_name
--,jec.user_je_category_name catg_user_name
FROM gl_je_categories_tl jec
,JA_CN_DFF_ASSIGNMENTS DFF
WHERE DFF.DFF_TITLE_CODE = 'JOCA'
AND jec.context = DFF.CONTEXT_CODE
AND jec.language = userenv('LANG')
AND nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',jec.attribute1, 'ATTRIBUTE2',jec.attribute2,
'ATTRIBUTE3',jec.attribute3, 'ATTRIBUTE4',jec.attribute4, 'ATTRIBUTE5',jec.attribute5),
'N') = 'Y'
AND DFF.Chart_Of_Accounts_Id = l_coa_id --Added for fixing bug#7475903
;
SELECT jes.je_source_name src_name
--,jes.user_je_source_name src_user_name
FROM gl_je_sources_tl jes
WHERE jes.language = userenv('LANG')
;
SELECT DISTINCT
jeh.je_header_id jnl_id
,jeb.name batch
,jeh.name jnl_name
,jeh.je_source jnl_source
,jeh.je_category jnl_catg
,jeh.default_effective_date jnl_eff_date
,jeh.description jnl_des
,src_t.user_je_source_name jnl_usr_source
,catg_t.user_je_category_name jnl_usr_catg
,jeh.status jeh_status
FROM GL_JE_BATCHES jeb
,GL_JE_HEADERS jeh
,gl_je_sources_tl src_t
,gl_je_categories_tl catg_t
WHERE jeh.ledger_id = l_ledger_id --using variable l_sob_id
AND jeb.je_batch_id + 0 = jeh.je_batch_id + 0
AND jeb.je_batch_id > 0
AND jeh.period_name = l_period_name --using variable l_period_name
AND jeh.je_category = l_jnl_catg --using variable l_jnl_catg
AND jeh.je_source = l_jnl_src --using variable l_jnl_src
-- Select certain transactions settle for the certain P_STATUS :
-- While 'null' return all the status,while not return transactions with status of 'P_status'
AND (jeh.status = NVL(P_STATUS,'') or P_STATUS IS NULL)
AND src_t.je_source_name = jeh.je_source
AND src_t.language = userenv('LANG') -- ?? l_language
AND catg_t.je_category_name = jeh.je_category
AND catg_t.language = userenv('LANG') -- ?? l_language
;
SELECT DISTINCT --Added for fixing bug#7475903
jel.je_line_num line_num
,jel.description line_desc
,jel.code_combination_id account_ccid
,decode(jel.context, dffa.context_code,
decode(dffa.attribute_column, 'ATTRIBUTE1',jel.attribute1, 'ATTRIBUTE2',jel.attribute2,
'ATTRIBUTE3',jel.attribute3, 'ATTRIBUTE4',jel.attribute4, 'ATTRIBUTE5',jel.attribute5,
'ATTRIBUTE6',jel.attribute6, 'ATTRIBUTE7',jel.attribute7, 'ATTRIBUTE8',jel.attribute8,
'ATTRIBUTE9',jel.attribute9, 'ATTRIBUTE10',jel.attribute10, 'ATTRIBUTE11',jel.attribute11,
'ATTRIBUTE12',jel.attribute12, 'ATTRIBUTE13',jel.attribute13, 'ATTRIBUTE14',jel.attribute14,
'ATTRIBUTE15',jel.attribute15)
) cash_related_item
FROM GL_JE_LINES jel
,ja_cn_dff_assignments dffa
,GL_CODE_COMBINATIONS codecmb --Added for CNAO Enhancement
,FND_SEGMENT_ATTRIBUTE_VALUES fsav
WHERE jel.je_header_id = l_je_header_id --using variable l_je_header_id
-- to locate cash flow item in dff_assignment
AND dffa.Application_Id = 101
AND dffa.chart_of_accounts_id = l_coa_id --using variable l_coa_id
AND dffa.dff_title_code='GLLI'
--Added for CNAO Enhancement begin
AND codecmb.chart_of_accounts_id=dffa.chart_of_accounts_id
AND codecmb.code_combination_id = jel.code_combination_id
AND fsav.application_id = 101
AND fsav.id_flex_num = l_coa_id
AND fsav.attribute_value = 'Y'
AND fsav.segment_attribute_type = 'GL_BALANCING'
AND (P_COM_SEG is null
OR P_COM_SEG =
DECODE(FSAV.APPLICATION_COLUMN_NAME,
'SEGMENT1',codecmb.SEGMENT1, 'SEGMENT2',codecmb.SEGMENT2, 'SEGMENT3',codecmb.SEGMENT3,
'SEGMENT4',codecmb.SEGMENT4, 'SEGMENT5',codecmb.SEGMENT5, 'SEGMENT6',codecmb.SEGMENT6,
'SEGMENT7',codecmb.SEGMENT7, 'SEGMENT8',codecmb.SEGMENT8, 'SEGMENT9',codecmb.SEGMENT9,
'SEGMENT10',codecmb.SEGMENT10, 'SEGMENT11',codecmb.SEGMENT11, 'SEGMENT12',codecmb.SEGMENT12,
'SEGMENT13',codecmb.SEGMENT13, 'SEGMENT14',codecmb.SEGMENT14, 'SEGMENT15',codecmb.SEGMENT15,
'SEGMENT16',codecmb.SEGMENT16, 'SEGMENT17',codecmb.SEGMENT17, 'SEGMENT18',codecmb.SEGMENT18,
'SEGMENT19',codecmb.SEGMENT19, 'SEGMENT20',codecmb.SEGMENT20, 'SEGMENT21',codecmb.SEGMENT21,
'SEGMENT22',codecmb.SEGMENT22, 'SEGMENT23',codecmb.SEGMENT23, 'SEGMENT24',codecmb.SEGMENT24,
'SEGMENT25',codecmb.SEGMENT25, 'SEGMENT26',codecmb.SEGMENT26, 'SEGMENT27',codecmb.SEGMENT27,
'SEGMENT28',codecmb.SEGMENT28, 'SEGMENT29',codecmb.SEGMENT29, 'SEGMENT30',codecmb.SEGMENT30
)
)
ORDER BY jel.je_line_num ASC;
DELETE
FROM JA_CN_LEDGER_LE_BSV_GT
;
SELECT XMLELEMENT( "P_START_PERIOD",P_START_PERIOD ) INTO l_xml_item FROM dual;
SELECT XMLELEMENT( "P_END_PERIOD",P_END_PERIOD ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "P_COMPANY_SEGMENT",P_COM_SEG ) INTO l_xml_item FROM dual; -- not sure
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "P_SOURCE",P_SOURCE ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "P_JOURNAL_CTG",P_JOURNAL_CTG ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "P_STATUS",P_STATUS ) INTO l_xml_item FROM dual; --Fix bug# 7481841 added
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT ledger.name
INTO l_ledger_name
FROM GL_LEDGERS ledger
WHERE ledger.ledger_id = l_ledger_id
;
SELECT XMLELEMENT( "LEDGER_NAME",l_ledger_name ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT HAOTL.name -- hao.name
INTO l_le_name
FROM HR_ALL_ORGANIZATION_UNITS HAO
,HR_ALL_ORGANIZATION_UNITS_TL HAOTL
WHERE HAO.ORGANIZATION_ID = l_le_id
AND HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
AND HAOTL.LANGUAGE = USERENV('LANG')
;*/
SELECT XEP.name
INTO l_le_name
FROM XLE_ENTITY_PROFILES XEP
WHERE XEP.LEGAL_ENTITY_ID = l_le_id;
SELECT XMLELEMENT( "LE_NAME",l_le_name ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT DECODE(nvl(DFF.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
INTO l_dff_check
FROM JA_CN_DFF_ASSIGNMENTS DFF
WHERE DFF.DFF_TITLE_CODE = 'JOCA'
AND DFF.CHART_OF_ACCOUNTS_ID=l_coa_id
;
SELECT jes.je_source_name
INTO l_jnl_src
FROM gl_je_sources_tl jes
WHERE jes.user_je_source_name = l_source -- ?? not sure
AND jes.language = l_language; -- ?? userenv('LANG');
SELECT distinct jec.je_category_name catg_name
INTO l_jnl_catg
FROM gl_je_categories_tl jec
,JA_CN_DFF_ASSIGNMENTS DFF
WHERE DFF.DFF_TITLE_CODE = 'JOCA'
AND jec.context = DFF.CONTEXT_CODE
AND jec.language = l_language
AND jec.user_je_category_name = l_journal_ctg
AND nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',jec.attribute1,
'ATTRIBUTE2',jec.attribute2, 'ATTRIBUTE3',jec.attribute3,
'ATTRIBUTE4',jec.attribute4, 'ATTRIBUTE5',jec.attribute5),
'N') = 'Y'
;
/* -- Select certain ledger settle for the certain P_STATUS :
l_check_flg := 'FALSE'; --flag to determin whether the check will continue
SELECT XMLELEMENT( "BATCH",l_je_batch_name ) INTO l_xml_item FROM dual;
SELECT XMLELEMENT( "JOURNAL_NAME",l_je_name ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
SELECT XMLELEMENT( "SOURCE",l_je_usr_source ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
SELECT XMLELEMENT( "JOURNAL_CTG",l_je_usr_catg ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
SELECT XMLELEMENT( "JOURNAL_EFF_DATE",l_je_eff_date ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
SELECT XMLELEMENT( "DESCRIPTION",l_je_desc ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
SELECT XMLELEMENT( "LINE_NUMBER",l_je_line_num ) INTO l_xml_item FROM dual;
SELECT XMLELEMENT( "ACCOUNT",l_account ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
SELECT XMLELEMENT( "ACCOUNT_DESC",l_account_desc ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
SELECT XMLELEMENT( "CASH_FLOW_ITEM",l_cash_related_item ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
SELECT XMLELEMENT( "EXC_REASON",l_line_err_msg ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
SELECT XMLELEMENT( "LINE",l_xml_line_items ) INTO l_xml_line FROM dual;
SELECT XMLCONCAT( l_xml_jnl_items,l_xml_line ) INTO l_xml_jnl_items FROM dual;
SELECT XMLELEMENT( "INVALID_LINES_4_JNL",l_invalid_line_4_jnl ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_jnl_items,l_xml_item) INTO l_xml_jnl_items FROM dual;
SELECT XMLELEMENT( "JOURNAL",l_xml_jnl_items ) INTO l_xml_journal FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_journal ) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "TOTAL_COUNT",TO_CHAR(l_jnl_count) ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "TOTAL_INVALID_JNL",TO_CHAR(l_invalid_jnl_count) ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "TOTAL_INVALID_LINES",TO_CHAR(l_invalid_lines) ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "GL_INVALID_JOURNALS",l_xml_all ) INTO l_xml_root FROM dual;
SELECT XMLELEMENT( "TOTAL_COUNT",0 ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "DFF_EXCEPTION",l_msg_incomplete_dff_assign ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "GL_INVALID_JOURNALS",l_xml_all ) INTO l_xml_root FROM dual;
SELECT XMLELEMENT( "TOTAL_COUNT",0 ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "DFF_EXCEPTION",'Other_Exception' ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "GL_INVALID_JOURNALS",l_xml_all ) INTO l_xml_root FROM dual;
SELECT gp.period_name
, gp.period_year
, gp.period_num
, gp.start_date
, gp.end_date
FROM gl_periods gp, GL_LEDGERS ledger
WHERE ledger.ledger_id = l_ledger_id --using variable l_ledger_id
AND ledger.period_set_name = GP.PERIOD_SET_NAME
AND ledger.accounted_period_type = gp.period_type
AND gp.start_date between
(SELECT start_date
FROM GL_PERIODS GP
WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND gp.period_name = l_period_from) --using parameter P_START_PERIOD
and (SELECT start_date
FROM GL_PERIODS GP
WHERE ledger.PERIOD_SET_NAME = GP.PERIOD_SET_NAME
AND ledger.ACCOUNTED_PERIOD_TYPE = GP.PERIOD_TYPE
AND gp.period_name = l_period_to) --using parameter P_END_PERIOD
ORDER BY gp.start_date
;
SELECT bsv.*
FROM JA_CN_LEDGER_LE_BSV_GT bsv
WHERE bsv.ledger_id = P_LEDGER_ID
AND bsv.legal_entity_id = P_LE_ID
AND bsv.chart_of_accounts_id = P_COA_ID
;
SELECT trxh.trx_id trxh_id
,trxh.trx_number trxh_number
,trxtype.trx_type_name trxtype_name
,party_init.party_name trxh_send_name
,party_reci.party_name trxh_recv_name
,trxb.gl_date trxb_gl_date
,trxh.description trxb_desc
,trxb.from_ledger_id trxb_send_ledger_id
,trxh.to_ledger_id trxh_recv_ledger_id
,trxb.from_le_id trxb_send_le_id
,trxh.to_le_id trxh_recv_le_id
,trxb.batch_number trxb_number
/* ,nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',trxh.attribute1, 'ATTRIBUTE2',trxh.attribute2,
'ATTRIBUTE3',trxh.attribute3, 'ATTRIBUTE4',trxh.attribute4, 'ATTRIBUTE5',trxh.attribute5,
'ATTRIBUTE6',trxh.attribute6, 'ATTRIBUTE7',trxh.attribute7, 'ATTRIBUTE8',trxh.attribute8,
'ATTRIBUTE9',trxh.attribute9, 'ATTRIBUTE10',trxh.attribute10, 'ATTRIBUTE11',trxh.attribute11,
'ATTRIBUTE12',trxh.attribute12, 'ATTRIBUTE13',trxh.attribute13, 'ATTRIBUTE14',trxh.attribute14,
'ATTRIBUTE15',trxh.attribute15)
, '') trxh_cash_related_item*/
FROM FUN_TRX_HEADERS trxh
,FUN_TRX_BATCHES trxb
,FUN_TRX_TYPES_TL trxtype
,HZ_PARTIES party_init
,HZ_PARTIES party_reci
--,JA_CN_DFF_ASSIGNMENTS dff
WHERE trxh.batch_id=trxb.batch_id
AND trxb.trx_type_id=trxtype.trx_type_id
AND party_init.party_id=trxh.initiator_id
AND party_reci.Party_Id=trxh.recipient_id
-- AND dff.DFF_TITLE_CODE='IITL'--'JOCA'
--AND trxh.status = 'COMPLETE'
--AND trxb.status = 'COMPLETE'
-- Select certain transactions settle for the certain P_STATUS :
-- While 'null' return all the status,while not return transactions with status of 'P_status'
AND (trxh.status = NVL(P_STATUS,'') or P_STATUS IS NULL)
-- determine the trx type is transfered to GL
AND trxh.invoice_flag='N'
-- add period limite , have to limite the gl_date in the period.
AND trxb.gl_date between l_period_start_date and l_perioD_end_date
AND trxtype.language=userenv('LANG');
SELECT DISTINCT
trl.Line_Number line_num
,trl.line_id line_id
,trldist.dist_id distl_id
,trldist.dist_number distl_num
,trldist.party_id distl_party_id
,trldist.party_type_flag distl_party_flg
,trldist.dist_type_flag distl_dist_flg
,trldist.ccid distl_ccid
,codecmb.chart_of_accounts_id codecmb_coa_id
--segment FSAV.APPLICATION_COLUMN_NAME of codecmb
,DECODE(FSAV.APPLICATION_COLUMN_NAME,
'SEGMENT1',codecmb.SEGMENT1, 'SEGMENT2',codecmb.SEGMENT2, 'SEGMENT3',codecmb.SEGMENT3,
'SEGMENT4',codecmb.SEGMENT4, 'SEGMENT5',codecmb.SEGMENT5, 'SEGMENT6',codecmb.SEGMENT6,
'SEGMENT7',codecmb.SEGMENT7, 'SEGMENT8',codecmb.SEGMENT8, 'SEGMENT9',codecmb.SEGMENT9,
'SEGMENT10',codecmb.SEGMENT10, 'SEGMENT11',codecmb.SEGMENT11, 'SEGMENT12',codecmb.SEGMENT12,
'SEGMENT13',codecmb.SEGMENT13, 'SEGMENT14',codecmb.SEGMENT14, 'SEGMENT15',codecmb.SEGMENT15,
'SEGMENT16',codecmb.SEGMENT16, 'SEGMENT17',codecmb.SEGMENT17, 'SEGMENT18',codecmb.SEGMENT18,
'SEGMENT19',codecmb.SEGMENT19, 'SEGMENT20',codecmb.SEGMENT20, 'SEGMENT21',codecmb.SEGMENT21,
'SEGMENT22',codecmb.SEGMENT22, 'SEGMENT23',codecmb.SEGMENT23, 'SEGMENT24',codecmb.SEGMENT24,
'SEGMENT25',codecmb.SEGMENT25, 'SEGMENT26',codecmb.SEGMENT26, 'SEGMENT27',codecmb.SEGMENT27,
'SEGMENT28',codecmb.SEGMENT28, 'SEGMENT29',codecmb.SEGMENT29, 'SEGMENT30',codecmb.SEGMENT30
) fsav_com_seg
-- get cash flow item
,nvl(DECODE(DFF.ATTRIBUTE_COLUMN, 'ATTRIBUTE1',trldist.attribute1, 'ATTRIBUTE2',trldist.attribute2,
'ATTRIBUTE3',trldist.attribute3, 'ATTRIBUTE4',trldist.attribute4, 'ATTRIBUTE5',trldist.attribute5,
'ATTRIBUTE6',trldist.attribute6, 'ATTRIBUTE7',trldist.attribute7, 'ATTRIBUTE8',trldist.attribute8,
'ATTRIBUTE9',trldist.attribute9, 'ATTRIBUTE10',trldist.attribute10, 'ATTRIBUTE11',trldist.attribute11,
'ATTRIBUTE12',trldist.attribute12, 'ATTRIBUTE13',trldist.attribute13, 'ATTRIBUTE14',trldist.attribute14,
'ATTRIBUTE15',trldist.attribute15)
, '') trxh_cash_related_item
FROM FUN_TRX_LINES trl
,FUN_DIST_LINES trldist
,GL_CODE_COMBINATIONS codecmb
,FND_SEGMENT_ATTRIBUTE_VALUES fsav
,JA_CN_DFF_ASSIGNMENTS dff
WHERE trl.line_id=trldist.line_id
AND trl.trx_id=trldist.trx_id
AND trldist.ccid=codecmb.code_combination_id
AND trl.trx_id=l_trx_id --using variable l_trx_id
AND codecmb.chart_of_accounts_id=P_COA_ID --using variable p_coa_id
AND trldist.dist_type_flag='L' --select ones only transfered to GL
--locate to the right segment attribute value
AND fsav.application_id = 101
AND fsav.id_flex_num = P_COA_ID
AND fsav.attribute_value = 'Y'
AND fsav.segment_attribute_type = 'GL_BALANCING'
AND dff.DFF_TITLE_CODE='IITL'--'JOCA'
--Added for CNAO Enhancement begin
AND (l_com_seg is null
OR l_com_seg =
DECODE(FSAV.APPLICATION_COLUMN_NAME,
'SEGMENT1',codecmb.SEGMENT1, 'SEGMENT2',codecmb.SEGMENT2, 'SEGMENT3',codecmb.SEGMENT3,
'SEGMENT4',codecmb.SEGMENT4, 'SEGMENT5',codecmb.SEGMENT5, 'SEGMENT6',codecmb.SEGMENT6,
'SEGMENT7',codecmb.SEGMENT7, 'SEGMENT8',codecmb.SEGMENT8, 'SEGMENT9',codecmb.SEGMENT9,
'SEGMENT10',codecmb.SEGMENT10, 'SEGMENT11',codecmb.SEGMENT11, 'SEGMENT12',codecmb.SEGMENT12,
'SEGMENT13',codecmb.SEGMENT13, 'SEGMENT14',codecmb.SEGMENT14, 'SEGMENT15',codecmb.SEGMENT15,
'SEGMENT16',codecmb.SEGMENT16, 'SEGMENT17',codecmb.SEGMENT17, 'SEGMENT18',codecmb.SEGMENT18,
'SEGMENT19',codecmb.SEGMENT19, 'SEGMENT20',codecmb.SEGMENT20, 'SEGMENT21',codecmb.SEGMENT21,
'SEGMENT22',codecmb.SEGMENT22, 'SEGMENT23',codecmb.SEGMENT23, 'SEGMENT24',codecmb.SEGMENT24,
'SEGMENT25',codecmb.SEGMENT25, 'SEGMENT26',codecmb.SEGMENT26, 'SEGMENT27',codecmb.SEGMENT27,
'SEGMENT28',codecmb.SEGMENT28, 'SEGMENT29',codecmb.SEGMENT29, 'SEGMENT30',codecmb.SEGMENT30
)
)
--Added for CNAO Enhancement end
-- Fix bug#7487439 added begin
AND EXISTS
(
SELECT * FROM FUN_TRX_HEADERS trxh
,FUN_TRX_BATCHES trxb
WHERE trxh.batch_id=trxb.batch_id
AND trxh.invoice_flag='N'
AND trxh.trx_id=l_trx_id --using variable l_trx_id
AND
(
( trxh.to_le_id = l_le_id --using variable l_le_id
AND trxh.to_ledger_id = l_ledger_id --using variable l_le_id
AND trldist.party_type_flag='R'
AND trldist.dist_type_flag ='L'
)
OR
( trxb.from_le_id = l_le_id --using variable l_le_id
AND trxb.from_ledger_id = l_ledger_id --using variable l_le_id
AND trldist.party_type_flag='I'
AND trldist.dist_type_flag ='L'
)
)
)
-- Fix bug#7487439 added end
;
DELETE
FROM JA_CN_LEDGER_LE_BSV_GT
;
SELECT XMLELEMENT( "P_START_PERIOD",l_period_from ) INTO l_xml_item FROM dual;
SELECT XMLELEMENT( "P_END_PERIOD",l_period_to ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "P_COMPANY_SEGMENT",l_com_seg) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "P_STATUS",P_STATUS) INTO l_xml_item FROM dual; --Fix bug# 7481841
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT ledger.name
INTO l_ledger_name
FROM GL_LEDGERS ledger
WHERE ledger.ledger_id = l_ledger_id
;
SELECT XMLELEMENT( "LEDGER_NAME",l_ledger_name ) INTO l_xml_item FROM dual; --Fix bug#7481545
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
/* SELECT HAOTL.name
INTO l_le_name
FROM HR_ALL_ORGANIZATION_UNITS HAO
,HR_ALL_ORGANIZATION_UNITS_TL HAOTL
WHERE HAO.ORGANIZATION_ID = l_le_id
AND HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
AND HAOTL.LANGUAGE = USERENV('LANG')
;*/
SELECT XEP.name --Updated to fix the issue that no legal entity name was found based on current legal entity id
INTO l_le_name
FROM XLE_ENTITY_PROFILES XEP
WHERE XEP.LEGAL_ENTITY_ID = l_le_id
;
SELECT XMLELEMENT( "LE_NAME",l_le_name ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT distinct DECODE(nvl(DFF.CONTEXT_CODE, ''), '', 'N',
DECODE(nvl(DFF.ATTRIBUTE_COLUMN, ''), '', 'N', 'Y'))
INTO l_dff_check
FROM JA_CN_DFF_ASSIGNMENTS DFF
WHERE DFF.DFF_TITLE_CODE = 'IITL'
AND DFF.chart_of_accounts_id=l_coa_id
;
SELECT count(*)
INTO l_trx_sender_c
FROM JA_CN_LEDGER_LE_BSV_GT tmp_bsv
WHERE tmp_bsv.ledger_id = l_trx_send_ledger_id -- ?? not sure
AND tmp_bsv.legal_entity_id = l_trx_send_le_id -- ?? not sure
AND tmp_bsv.bal_seg_value = l_codecmb_com_seg
;
SELECT count(*)
INTO l_trx_receiver_c
FROM JA_CN_LEDGER_LE_BSV_GT tmp_bsv
WHERE tmp_bsv.ledger_id = l_trx_recv_ledger_id -- ?? not sure
AND tmp_bsv.legal_entity_id = l_trx_recv_le_id -- ?? not sure
AND tmp_bsv.bal_seg_value = l_codecmb_com_seg
;
SELECT XMLELEMENT( "TR_NUMBER",l_trxb_num||'/'||l_trx_num ) INTO l_xml_item FROM dual;
SELECT XMLELEMENT( "TR_TYPE",l_trx_type ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
SELECT XMLELEMENT( "SENDER",l_trx_send_name ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
SELECT XMLELEMENT( "RECEIVER",l_trx_recv_name ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
SELECT XMLELEMENT( "GL_DATE",l_trx_gl_date ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
SELECT XMLELEMENT( "LINE_NUMBER",l_trxl_num ) INTO l_xml_item FROM dual; -- using trx line number to markup recorde with error
SELECT XMLELEMENT( "ACCOUNT",l_account ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
SELECT XMLELEMENT( "ACCOUNT_DESC",l_account_desc ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
SELECT XMLELEMENT( "CASH_FLOW_ITEM",l_cash_related_item ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
SELECT XMLELEMENT( "EXC_REASON",l_line_err_msg ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_line_items,l_xml_item) INTO l_xml_line_items FROM dual;
SELECT XMLELEMENT( "LINE",l_xml_line_items ) INTO l_xml_line FROM dual;
SELECT XMLCONCAT( l_xml_tr_items,l_xml_line ) INTO l_xml_tr_items FROM dual;
SELECT XMLELEMENT( "INVALID_LINES_4_TR",l_invalid_line_4_tr ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_tr_items,l_xml_item) INTO l_xml_tr_items FROM dual;
SELECT XMLELEMENT( "TRANSACTION",l_xml_tr_items ) INTO l_xml_tr FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_tr ) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "TOTAL_COUNT",TO_CHAR(l_tr_count) ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "TOTAL_INVALID_TR",TO_CHAR(l_invalid_tr_count) ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "TOTAL_INVALID_LINES",TO_CHAR(l_invalid_lines) ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "GIS_INVALID_TRANSACTIONS",l_xml_all ) INTO l_xml_root FROM dual;
SELECT XMLELEMENT( "TOTAL_COUNT",0 ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "DFF_EXCEPTION",l_msg_incomplete_dff_assign ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "GIS_INVALID_TRANSACTIONS",l_xml_all ) INTO l_xml_root FROM dual;
SELECT XMLELEMENT( "TOTAL_COUNT",0 ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "DFF_EXCEPTION",'Other_Exception' ) INTO l_xml_item FROM dual;
SELECT XMLCONCAT( l_xml_all,l_xml_item) INTO l_xml_all FROM dual;
SELECT XMLELEMENT( "GIS_INVALID_TRANSACTIONS",l_xml_all ) INTO l_xml_root FROM dual;