[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 gcc.chart_of_accounts_id = l_coa_id --using variable P_COA_ID
AND ledger.chart_of_accounts_id = ledger.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 -- seeded data
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 ,'gl_account'
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 = TO_CHAR(l_account_num) --using variable l_account_num
AND cash_acc.chart_of_accounts_id = l_coa_id; --using variable P_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 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 = TO_CHAR(l_com_seg); --using variable l_com_seg
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
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*/
jel.GLOBAL_ATTRIBUTE6 cash_related_item
INTO l_tr_csi_check
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
SELECT leg.currency_code into l_gl_curr_code
FROM GL_LEDGERS leg
WHERE leg.ledger_id=P_LEDGER_ID;
SELECT cur.conversion_rate INTO P_AGIS_CURR_RATE
FROM GL_DAILY_RATES cur
WHERE cur.from_currency =P_AGIS_CURR_CODE
AND cur.to_currency=l_gl_curr_code
AND cur.conversion_type=P_AGIS_CURR_COV_TYPE
AND cur.conversion_date=P_GL_DATE;
SELECT rounding_rule
INTO l_round_flag
FROM ja_cn_system_parameters_all
WHERE legal_entity_id = P_LE_ID; --Using parameter P_LE_ID
SELECT PRECISION
INTO l_precision
FROM fnd_currencies
WHERE currency_code=l_currency_code;
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 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_name_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_name_to) --using parameter P_END_PERIOD
ORDER BY gp.start_date
;
SELECT jeh.je_header_id jnl_id
,jeh.je_category jnl_catg
,jeh.name jnl_name
,jeh.doc_sequence_value jnl_doc_seq_num
,jeh.currency_code curr_code
,jeh.currency_conversion_rate curr_cov_rate
,jeh.currency_conversion_type curr_cov_type
,jeh.currency_conversion_date curr_cov_date
,decode(jeh.global_attribute_category, 'JE.CN.GLXJEENT.HEADER',
nvl(jeh.global_attribute1, 'N'), 'N'
) inter_flag
,jeh.DEFAULT_EFFECTIVE_DATE effective_date
FROM GL_JE_HEADERS jeh
,gl_je_categories_tl jec
,JA_CN_DFF_ASSIGNMENTS DFF
WHERE jeh.ledger_id = l_ledger_id --using variable P_LEDGER_ID
AND jeh.period_name = l_period_name --using variable l_period_name
--check the Journal Category is Cash Related or not
AND jeh.je_category = jec.je_category_name --user_je_category_name
AND 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'
-- to locate cash flow item in dff_assignment
AND dff.Application_Id = 101
AND dff.chart_of_accounts_id = l_coa_id --using variable l_coa_id
--check the Journal's Status
AND jeh.status = 'P'
--fix bug 5665083, 2006-11-16, Andrew/ 4/03/2007, Altered by Yucheng.Sun :
-- should exclude AGIS, AP, AR in GL data collection.
AND jeh.je_source NOT IN ('Intercompany')--, 'Payables', 'Receivables'
-- exclude the source defined in in cash flow item mapping form (ja_cn_cfs_item_mapping_hdrs)
AND jeh.je_source NOT IN ( SELECT FAPP.APPLICATION_SHORT_NAME
FROM FND_APPLICATION FAPP, JA_CN_CFS_ITEM_MAPPING_HDRS JCCIMH
WHERE FAPP.APPLICATION_ID=JCCIMH.APPLICATION_ID
)
;
SELECT jel.je_line_num line_num
,nvl(jel.description,
jeh.description) line_desc
,jel.code_combination_id account_ccid
--Begin: Updated for CNAOV2 solution on 31-Mar-2010
/*,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*/
,jel.GLOBAL_ATTRIBUTE6 cash_related_item
,nvl(jel.ENTERED_DR, 0) -
nvl(jel.ENTERED_CR, 0) je_entered_amount
,nvl(jel.ACCOUNTED_DR, 0) -
nvl(jel.ACCOUNTED_CR, 0) je_accounted_amount
FROM GL_JE_LINES jel
,GL_JE_HEADERS jeh
--,ja_cn_dff_assignments dffa
WHERE jel.je_header_id = l_je_header_id --using variable l_je_header_id
AND jeh.je_header_id = jel.je_header_id;
SELECT trxh.trx_id trxh_ID
, trxh.trx_number trxh_num
, nvl(trxh.description,nvl(trxb.description,''))
trxh_decription
, trxh.initiator_id trxh_initiator_ID
, trxh.recipient_id trxh_recipient_ID
, trxh.to_le_id trxh_to_le_ID
, trxh.to_ledger_id trxh_to_ledger_ID
--, trxh.status trxh_status
, trxh.init_amount_cr trxh_init_amount_cr
, trxh.init_amount_dr trxh_init_amount_dr
, trxh.reci_amount_cr trxh_reci_amount_cr
, trxh.reci_amount_dr trxh_reci_amount_dr
, trxb.batch_id trxb_batch_ID
, trxb.batch_number trxb_batch_num
, trxb.gl_date trxb_gl_date
, trxb.currency_code trxb_curr_code
, trxb.from_le_id trxb_from_le_ID
, trxb.from_ledger_id trxb_from_ledger_ID
, nvl(trxb.exchange_rate_type,'') trxb_curr_cov_rate
, trxb.batch_date trxb_batch_date
/* , 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
--, JA_CN_DFF_ASSIGNMENTS dff
WHERE trxh.batch_id=trxb.batch_id
AND trxb.trx_type_id=trxtype.trx_type_id
--
--AND dff.DFF_TITLE_CODE='IITL'--'JOCA'
-- check the transaction status, care only complete trx.
-- and its journel had been post to GL
AND trxh.status = 'COMPLETE'
AND trxb.status = 'COMPLETE'
-- 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')
/* -- FOR TEST-----------------------------------------------------------
and trxh.batch_id in (83193,83194)
-- FOR TEST-----------------------------------------------------------
*/;
SELECT DISTINCT
trl.Line_Number line_num
,trl.line_id line_id
,trldist.dist_id distl_id
,trldist.party_id distl_party_id
,trldist.party_type_flag distl_party_flg
,trldist.dist_type_flag distl_dist_flg
,trldist.ccid distl_ccid
,trldist.amount_cr distl_amount_cr
,trldist.amount_dr distl_amount_dr
,trldist.dist_number distl_number
,codecmb.chart_of_accounts_id codecmb_coa_id
,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)
, '') distl_cash_related_item
FROM FUN_TRX_LINES trl
,FUN_DIST_LINES trldist
,GL_CODE_COMBINATIONS codecmb
,FND_SEGMENT_ATTRIBUTE_VALUES fsav
--,JA_CN_LEDGER_LE_BSV_GT tmpbsv
,JA_CN_DFF_ASSIGNMENTS dff
,xle_entity_profiles xep -- Added for fixing bug# 8744259 by Chaoqun on 30-Jul-2009
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_trxh_header_id --using variable l_trx_id
AND codecmb.chart_of_accounts_id=l_coa_id --using variable p_coa_id
AND dff.DFF_TITLE_CODE='IITL' --'JOCA'
--AND trldist.dist_type_flag='L' -- ?? not sure
/*AND tmpbsv.ledger_id = l_ledger_id
AND tmpbsv.legal_entity_id = l_le_id
AND DECODE(fsav.APPLICATION_COLUMN_NAME, --segment FSAV.APPLICATION_COLUMN_NAME of codecmb
'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
) = tmpbsv.bal_seg_value --select only company segment list in the ja_cn_ledger_le_bsv_gt table */
--locate to the right segment attribute value
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 FUN_TCA_PKG.GET_LE_ID(trldist.party_id)= xep.Party_Id -- Added for fixing bug# 8744259 by Chaoqun on 30-Jul-2009
AND xep.legal_entity_id = l_le_id -- Added for fixing bug# 8744259 by Chaoqun on 30-Jul-2009
;
DELETE
FROM JA_CN_LEDGER_LE_BSV_GT;
SELECT appl.application_short_name
INTO l_source_name
FROM fnd_application appl
WHERE appl.application_id= l_source_application_id;
SELECT ledger.currency_code
INTO l_je_func_curr_code
FROM GL_LEDGERS ledger
WHERE ledger.ledger_id=P_LEDGER_ID;
DELETE
FROM JA_CN_CFS_ACTIVITIES_ALL s
WHERE LEGAL_ENTITY_ID=P_LE_ID
--Start bug 12379032 by jianchao chi, add the ledger id condition
AND LEDGER_ID = P_LEDGER_ID
--End bug 12379032
-- Fix bug by arming delete start
-- AND SOURCE='GL'
-- Fix bug by arming delete end
-- Fix bug by arming add start
AND SOURCE=l_source_name
AND NVL(UPGRADE_FLAG, ' ')<>'P'
-- Fix bug by arming add end
AND PERIOD_NAME=l_period_Name;
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_APPLICATION_ID
,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
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,source
--,reference_number
,BALANCING_SEGMENT--Fix bug#7334017 add
)
VALUES( ja_cn_cfs_activities_s.nextval
,l_le_id
,P_LEDGER_ID -- ledger id
,null
,l_je_header_id
,l_je_jnl_name
,TO_CHAR(l_je_line_num)
,l_source_application_id
,'JOURNAL'
,l_je_jnl_doc_seq_num
,l_je_effective_date
,l_je_effective_date
,l_period_name
,l_je_func_curr_code -- currency code from gl_ledger
,l_je_line_func_amount
,l_je_curr_code
,l_je_line_amount
,l_je_curr_cov_rate
,l_je_curr_cov_type
,l_je_curr_cov_date
,l_je_line_desc
,l_cash_related_item --lines GDF
--,l_je_inter_flag -- not sure
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.LOGIN_ID
,l_source_name --'GL' -- for source,seeded data
--,l_je_jnl_name --sanme as TRX_NUMBER
,get_balancing_segment(l_ccid)--Fix bug#7334017 add
);
SELECT ledger.currency_code
INTO l_je_func_curr_code
FROM GL_LEDGERS ledger
WHERE ledger.ledger_id=P_LEDGER_ID;
DELETE
FROM JA_CN_CFS_ACTIVITIES_ALL
WHERE LEGAL_ENTITY_ID = l_le_id
--Start bug 12379032 by jianchao chi, add the ledger id condition
AND LEDGER_ID = P_LEDGER_ID
--End bug 12379032
AND PERIOD_NAME = l_period_name
--AND SOURCE = 'GIS' --fix bug 7488206 delete
AND SOURCE=l_source_name ;--fix bug 7488206 add
,l_trxb_entered_date -- ?? not sure, the batch date or the header last update date
--,l_trxh_cash_rel_item
;
Select gl.je_header_id, gl.je_line_num
into l_je_header_id, l_je_line_num
from GL_JE_LINES GL
,GL_JE_HEADERS GH
where GL.REFERENCE_2 = l_trxb_batch_id
and GL.REFERENCE_4 = l_trxl_id
and GL.REFERENCE_5 = l_distl_id
and GL.REFERENCE_1 = 'Intercompany Transaction'
AND GL.JE_HEADER_ID = GH.JE_HEADER_ID
AND GH.JE_SOURCE = 'Global Intercompany'
AND GH.LEDGER_ID = l_ledger_id;
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_APPLICATION_ID
,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 -- delete in R12
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,SOURCE
--,reference_number
,BALANCING_SEGMENT
,JE_HEADER_ID
,JE_LINE_NUM
)
VALUES( ja_cn_cfs_activities_s.nextval
,l_le_id
,P_LEDGER_ID -- ledger id
,NULL
,l_trxh_header_id -- ?? batch id
-- ,l_trxh_header_num -- ?? batch number: transaction header number fix bug 7488191 delete
,l_trxb_batch_num -- fix bug 7488191 add
,TO_CHAR(l_distl_number) -- ?? header number
,l_source_application_id -- application TD of AGIS
,'AGIS' -- seeded data
,NULL
,l_trxb_entered_date -- batches: batch_date
,l_trxb_gl_date -- batches: gl_date
,l_period_name -- parameters: period name
,l_je_func_curr_code -- function currency of current Ledger to FUNC_CURR_CODE
,l_tr_func_amount -- lines: acounted_dr-accounted_cr --?? not sure
,l_trxb_curr_code -- batches: currency code
,l_current_amount_dr-l_current_amount_cr -- lines: entered_dr-entered_cr --?? not sure
,l_trxh_curr_cov_rate -- GL_DAILY_RATES.CONVERSION_RATE
,l_trxb_curr_cov_type -- conversion_type of line's subsidary(s/r)
,l_trxb_gl_date -- batches: gl_date
,l_trxh_desc -- header: description
,l_distl_cash_rel_item -- the attribute* name
--,l_je_inter_flag -- delete in R12
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
,fnd_global.LOGIN_ID
,l_source_name --'GIS' -- for source,seeded data
--,l_je_jnl_name --sanme as TRX_NUMBER
,get_balancing_segment(l_distl_ccid)--Fix bug#7334017 add
,l_je_header_id
,l_je_line_num);
SELECT application_id
INTO l_source_id
FROM fnd_application
WHERE application_short_name = TO_CHAR(l_source);
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.SEGMENT_ATTRIBUTE_TYPE = 'GL_BALANCING'
AND FSAV.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
AND FSAV.ID_FLEX_CODE = 'GL#';--Fix bug#7334017 add
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';