[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT /*+ NO_MERGE Leading(gl1,fia2,fia1,gl2) */
distinct le1.legal_entity_id TRANSACTING_LE_ID
,le1.NAME TRANSACTING_LE
,gl1.ledger_id TRANSACTING_LEDGER_ID
,gl1.name TRANSACTING_LEDGER
,fia1.to_le_id TRADING_PARTNER_LE_ID
,le2.NAME TRADING_PARTNER_LE
,gl2.ledger_id TRADING_PARTNER_LEDGER_ID
,gl2.name TRADING_PARTNER_LEDGER
,fia1.ccid CCID
,fia1.type ACCT_TYPE
,gl1.accounted_period_type ACCOUNTED_PERIOD_TYPE
,gl1.period_set_name PERIOD_SET_NAME
FROM fun_inter_accounts_v fia1,
fun_inter_accounts_v fia2,
gl_ledgers gl1,
xle_entity_profiles le1,
gl_ledgers gl2,
xle_entity_profiles le2
WHERE fia1.ledger_id = gl1.ledger_id
AND fia1.from_le_id = le1.legal_entity_id
AND fia1.to_le_id = fia2.from_le_id
AND fia1.from_le_id = fia2.to_le_id
AND fia2.ledger_id = gl2.ledger_id
AND fia2.from_le_id = le2.legal_entity_id
AND gl1.ledger_category_code = ''PRIMARY''
AND gl2.ledger_category_code = ''PRIMARY''';
SELECT distinct le1.legal_entity_id TRANSACTING_LE_ID
,le1.NAME TRANSACTING_LE
,gl1.ledger_id TRANSACTING_LEDGER_ID
,gl1.name TRANSACTING_LEDGER
,fia1.to_le_id TRADING_PARTNER_LE_ID
,le2.NAME TRADING_PARTNER_LE
,gl2.ledger_id TRADING_PARTNER_LEDGER_ID
,gl2.name TRADING_PARTNER_LEDGER
,glb1.currency_code TRANSACTION_CURRENCY
,glb1.period_name TRANSACTING_PERIOD_NAME
,glp2.period_name TRADING_PERIOD_NAME
FROM fun_inter_accounts_v fia1,
gl_balances glb1,
gl_ledgers gl1,
xle_entity_profiles le1,
gl_periods glp1,
fun_inter_accounts_v fia2,
gl_ledgers gl2,
xle_entity_profiles le2,
gl_periods glp2
WHERE fia1.ledger_id = glb1.ledger_id
AND fia1.ccid = glb1.code_combination_id
AND fia1.type = ''R''
AND fia1.ledger_id = gl1.ledger_id
AND fia1.from_le_id = le1.legal_entity_id
AND fia2.type = ''P''
AND fia1.to_le_id = fia2.from_le_id
AND fia1.from_le_id = fia2.to_le_id
AND glb1.actual_flag = ''A''
AND fia2.ledger_id = gl2.ledger_id
AND fia2.from_le_id = le2.legal_entity_id
AND gl1.period_set_name = glp1.period_set_name
AND glp1.period_type = gl1.accounted_period_type
AND glp1.period_name = glb1.period_name
AND gl2.period_set_name = glp2.period_set_name
AND glp2.period_type = gl2.accounted_period_type
AND gl1.ledger_category_code = ''PRIMARY''
AND gl2.ledger_category_code = ''PRIMARY''
AND (glb1.translated_flag = ''R'' OR glb1.translated_flag is NULL)
';
SELECT TRANSACTING_LE_ID
,TRANSACTING_LE
,TRANSACTING_LEDGER_ID
,TRANSACTING_LEDGER
,TRADING_PARTNER_LE_ID
,TRADING_PARTNER_LE
,TRADING_PARTNER_LEDGER_ID
,TRADING_PARTNER_LEDGER
,TRANSACTION_CURRENCY
,TRANSACTING_PERIOD_NAME
,TRADING_PERIOD_NAME
,LTRIM(TO_CHAR(fun_recon_rpt_pkg.get_balance(''R'', ''BEGIN_BALANCE_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY),''999999999999999999999.999999999999'')) AR_BEGIN_BALANCE_DR
,LTRIM(TO_CHAR(fun_recon_rpt_pkg.get_balance(''R'', ''BEGIN_BALANCE_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY),''999999999999999999999.999999999999'')) AR_BEGIN_BALANCE_CR
,LTRIM(TO_CHAR(fun_recon_rpt_pkg.get_balance(''P'', ''BEGIN_BALANCE_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY),''999999999999999999999.999999999999'')) AP_BEGIN_BALANCE_DR
,LTRIM(TO_CHAR(fun_recon_rpt_pkg.get_balance(''P'', ''BEGIN_BALANCE_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY),''999999999999999999999.999999999999'')) AP_BEGIN_BALANCE_CR
,LTRIM(TO_CHAR(fun_recon_rpt_pkg.get_balance(''R'', ''PERIOD_NET_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY),''999999999999999999999.999999999999'')) AR_PERIOD_NET_DR
,LTRIM(TO_CHAR(fun_recon_rpt_pkg.get_balance(''R'', ''PERIOD_NET_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY),''999999999999999999999.999999999999'')) AR_PERIOD_NET_CR
,LTRIM(TO_CHAR(fun_recon_rpt_pkg.get_balance(''P'', ''PERIOD_NET_DR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY),''999999999999999999999.999999999999'')) AP_PERIOD_NET_DR
,LTRIM(TO_CHAR(fun_recon_rpt_pkg.get_balance(''P'', ''PERIOD_NET_CR'', TRANSACTING_LEDGER_ID, TRANSACTING_LE_ID, TRANSACTING_PERIOD_NAME
,TRADING_PARTNER_LEDGER_ID, TRADING_PARTNER_LE_ID, TRADING_PERIOD_NAME, TRANSACTION_CURRENCY),''999999999999999999999.999999999999'')) AP_PERIOD_NET_CR
$additional_currency_columns$
FROM
(
$sub_query$
) ';
'SELECT /*+ Leading(fun_act,ael,aeh) use_nl(ael,aeh) cardinality(fun_act,1) opt_param(''_optimizer_cost_based_transformation'',''off'' ) */
fun_act.TRANSACTING_LE_ID TRANS_LE_ID
,fun_act.TRANSACTING_LE TRANS_LE
,fun_act.TRADING_PARTNER_LE_ID TRAD_LE_ID
,fun_act.TRADING_PARTNER_LE TRAD_LE
,fun_act.TRANSACTING_LEDGER TRANSACTING_LEDGER
,fun_act.TRANSACTING_LEDGER_ID LEDGER_ID
,''SLA'' SOURCE
,fun_act.ACCT_TYPE ACCOUNT_TYPE
,aeh.accounting_date GL_DATE
,aeh.creation_date CREATION_DATE
,aeh.last_update_date LAST_UPDATE_DATE
,aeh.gl_transfer_date GL_TRANSFER_DATE
,aeh.reference_date REFERENCE_DATE
,aeh.completed_date COMPLETED_DATE
,ent.transaction_number TRANSACTION_NUMBER
,xle.transaction_date TRANSACTION_DATE
,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
,aeh.application_id APPLICATION_ID
,fap.application_name APPLICATION_NAME
,aeh.ae_header_id HEADER_ID
,aeh.description HEADER_DESCRIPTION
,xlk1.meaning FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,ae_line_num SLA_LINE_NUMBER
,xle.event_id EVENT_ID
,xle.event_date EVENT_DATE
,xle.event_number EVENT_NUMBER
,xet.event_class_code EVENT_CLASS_CODE
,xect.NAME EVENT_CLASS_NAME
,aeh.event_type_code EVENT_TYPE_CODE
,xett.NAME EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,gjb.posted_date POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.je_source JE_SOURCE_CODE
,gjh.je_header_id JE_HEADER_ID
,gjl.je_line_num GL_LINE_NUMBER
,ael.displayed_line_number LINE_NUMBER
,ael.accounting_class_code ACCOUNTING_CLASS_CODE
,xlk2.meaning ACCOUNTING_CLASS_NAME
,ael.description LINE_DESCRIPTION
,ael.currency_code ENTERED_CURRENCY
,LTRIM(TO_CHAR(ael.currency_conversion_rate,''999999999999999999999.999999999999'')) CONVERSION_RATE
,ael.currency_conversion_date CONVERSION_RATE_DATE
,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,LTRIM(TO_CHAR(ael.entered_dr,''999999999999999999999.999999999999'')) ENTERED_DR
,LTRIM(TO_CHAR(ael.entered_cr,''999999999999999999999.999999999999'')) ENTERED_CR
,LTRIM(TO_CHAR(ael.unrounded_accounted_dr,''999999999999999999999.999999999999'')) UNROUNDED_ACCOUNTED_DR
,LTRIM(TO_CHAR(ael.unrounded_accounted_cr,''999999999999999999999.999999999999'')) UNROUNDED_ACCOUNTED_CR
,LTRIM(TO_CHAR(ael.accounted_dr,''999999999999999999999.999999999999'')) ACCOUNTED_DR
,LTRIM(TO_CHAR(ael.accounted_cr ,''999999999999999999999.999999999999'')) ACCOUNTED_CR
,LTRIM(TO_CHAR(ael.statistical_amount ,''999999999999999999999.999999999999'')) STATISTICAL_AMOUNT
,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
,ael.attribute_category ATTRIBUTE_CATEGORY
,ael.attribute1 ATTRIBUTE1
,ael.attribute2 ATTRIBUTE2
,ael.attribute3 ATTRIBUTE3
,ael.attribute4 ATTRIBUTE4
,ael.attribute5 ATTRIBUTE5
,ael.attribute6 ATTRIBUTE6
,ael.attribute7 ATTRIBUTE7
,ael.attribute8 ATTRIBUTE8
,ael.attribute9 ATTRIBUTE9
,ael.attribute10 ATTRIBUTE10
,ael.attribute11 ATTRIBUTE11
,ael.attribute12 ATTRIBUTE12
,ael.attribute13 ATTRIBUTE13
,ael.attribute14 ATTRIBUTE14
,ael.attribute15 ATTRIBUTE15
,ael.code_combination_id CODE_COMBINATION_ID
,fun_trx_entry_util.get_concatenated_account(ael.code_combination_id ) ACCOUNT
,ael.ae_header_id||''-''||ael.ae_line_num EXPAND_ID
,nvl(xsrc.application_id, -1) DRILLDOWN_APP_ID
FROM xla_ae_headers aeh
,xla_ae_lines ael
,xla_lookups xlk1
,xla_lookups xlk2
,xla_events xle
,xla_event_types_b xet
,xla_event_classes_tl xect
,xla_event_types_tl xett
,xla_transaction_entities ent
,fnd_application_tl fap
,gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_daily_conversion_types gdct
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_periods glp
,xla_subledgers xsrc
,gl_ledgers gl
, ($get_accounts_query$) fun_act
WHERE aeh.accounting_entry_status_code = ''F''
AND aeh.gl_transfer_status_code = ''Y''
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND xsrc.application_id = aeh.application_id
AND xlk1.lookup_type(+) = ''XLA_FUNDS_STATUS''
AND xlk1.lookup_code(+) = aeh.funds_status_code
AND xlk2.lookup_type = ''XLA_ACCOUNTING_CLASS''
AND xlk2.lookup_code = ael.accounting_class_code
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND xect.application_id = xet.application_id
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.LANGUAGE = USERENV(''LANG'')
AND xett.application_id = xet.application_id
AND xett.entity_code = xet.entity_code
AND xett.event_class_code = xet.event_class_code
AND xett.event_type_code = xet.event_type_code
AND xett.LANGUAGE = USERENV(''LANG'')
AND xle.application_id = aeh.application_id
AND ent.entity_id = xle.entity_id
AND ent.application_id = xle.application_id
AND fap.application_id = aeh.application_id
AND fap.LANGUAGE = USERENV(''LANG'')
AND gdct.conversion_type(+) = ael.currency_conversion_type
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = ''P''
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV(''LANG'')
AND gjst.je_source_name = gjh.je_source
AND gjst.LANGUAGE = USERENV(''LANG'')
AND aeh.balance_type_code = ''A''
AND ael.code_combination_id = fun_act.ccid
AND aeh.ledger_id = fun_act.TRANSACTING_LEDGER_ID
AND glp.period_set_name = fun_act.period_set_name
AND glp.period_type = fun_act.accounted_period_type
AND gjl.ledger_id = gjh.ledger_id
AND glp.period_name = aeh.period_name
AND glp.period_name = gjh.period_name||''''
AND xsrc.je_source_name (+) = gjh.je_source
AND gjh.ledger_id = gl.ledger_id
AND aeh.ledger_id =ael.ledger_id
AND ael.accounting_date BETWEEN glp.start_date AND glp.end_date
and ael.accounting_date =aeh.accounting_date
AND gl.ledger_category_code = ''PRIMARY''';
'SELECT /*+ leading(fia, ael) cardinality(fia,1) opt_param(''_optimizer_cost_based_transformation'',''off'' ) */
glv.legal_entity_id TRANS_LE_ID
,glv.LEGAL_ENTITY_NAME TRANS_LE
,fia.to_le_id TRAD_LE_ID
,fun_recon_rpt_pkg.get_legal_entity(fia.to_le_Id) TRAD_LE
,glv.ledger_name TRANSACTING_LEDGER
,glv.ledger_id LEDGER_ID
,''SLA'' SOURCE
,fia.type ACCOUNT_TYPE
,aeh.accounting_date GL_DATE
,aeh.creation_date CREATION_DATE
,aeh.last_update_date LAST_UPDATE_DATE
,aeh.gl_transfer_date GL_TRANSFER_DATE
,aeh.reference_date REFERENCE_DATE
,aeh.completed_date COMPLETED_DATE
,ent.transaction_number TRANSACTION_NUMBER
,xle.transaction_date TRANSACTION_DATE
,aeh.application_id APPLICATION_ID
,fap.application_name APPLICATION_NAME
,aeh.ae_header_id HEADER_ID
,aeh.description HEADER_DESCRIPTION
,xlk1.meaning FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,ae_line_num SLA_LINE_NUMBER
,xle.event_id EVENT_ID
,xle.event_date EVENT_DATE
,xle.event_number EVENT_NUMBER
,xet.event_class_code EVENT_CLASS_CODE
,xect.NAME EVENT_CLASS_NAME
,aeh.event_type_code EVENT_TYPE_CODE
,xett.NAME EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,gjb.posted_date POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.je_source JE_SOURCE_CODE
,gjh.je_header_id JE_HEADER_ID
,gjl.je_line_num GL_LINE_NUMBER
,ael.displayed_line_number LINE_NUMBER
,ael.accounting_class_code ACCOUNTING_CLASS_CODE
,xlk2.meaning ACCOUNTING_CLASS_NAME
,ael.description LINE_DESCRIPTION
,ael.currency_code ENTERED_CURRENCY
,LTRIM(TO_CHAR(ael.currency_conversion_rate,''999999999999999999999.999999999999'')) CONVERSION_RATE
,ael.currency_conversion_date CONVERSION_RATE_DATE
,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,LTRIM(TO_CHAR(ael.entered_dr,''999999999999999999999.999999999999'')) ENTERED_DR
,LTRIM(TO_CHAR(ael.entered_cr,''999999999999999999999.999999999999'')) ENTERED_CR
,LTRIM(TO_CHAR(ael.unrounded_accounted_dr,''999999999999999999999.999999999999'')) UNROUNDED_ACCOUNTED_DR
,LTRIM(TO_CHAR(ael.unrounded_accounted_cr,''999999999999999999999.999999999999'')) UNROUNDED_ACCOUNTED_CR
,LTRIM(TO_CHAR(ael.accounted_dr,''999999999999999999999.999999999999'')) ACCOUNTED_DR
,LTRIM(TO_CHAR(ael.accounted_cr ,''999999999999999999999.999999999999'')) ACCOUNTED_CR
,LTRIM(TO_CHAR(ael.statistical_amount ,''999999999999999999999.999999999999'')) STATISTICAL_AMOUNT
,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
,ael.attribute_category ATTRIBUTE_CATEGORY
,ael.attribute1 ATTRIBUTE1
,ael.attribute2 ATTRIBUTE2
,ael.attribute3 ATTRIBUTE3
,ael.attribute4 ATTRIBUTE4
,ael.attribute5 ATTRIBUTE5
,ael.attribute6 ATTRIBUTE6
,ael.attribute7 ATTRIBUTE7
,ael.attribute8 ATTRIBUTE8
,ael.attribute9 ATTRIBUTE9
,ael.attribute10 ATTRIBUTE10
,ael.attribute11 ATTRIBUTE11
,ael.attribute12 ATTRIBUTE12
,ael.attribute13 ATTRIBUTE13
,ael.attribute14 ATTRIBUTE14
,ael.attribute15 ATTRIBUTE15
,ael.code_combination_id CODE_COMBINATION_ID
,fun_trx_entry_util.get_concatenated_account(ael.code_combination_id ) ACCOUNT
,ael.ae_header_id||''-''||ael.ae_line_num EXPAND_ID
,nvl(xsrc.application_id, -1) DRILLDOWN_APP_ID
FROM xla_ae_headers aeh
,xla_ae_lines ael
,xla_lookups xlk1
,xla_lookups xlk2
,xla_events xle
,xla_event_types_b xet
,xla_event_classes_tl xect
,xla_event_types_tl xett
,xla_transaction_entities ent
,fnd_application_tl fap
,gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_daily_conversion_types gdct
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledger_le_v glv
,fun_inter_accounts_v fia
,gl_periods glp
,xla_subledgers xsrc
,gl_ledgers gl
WHERE aeh.accounting_entry_status_code = ''F''
AND aeh.gl_transfer_status_code = ''Y''
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND xsrc.application_id = aeh.application_id
AND xlk1.lookup_type(+) = ''XLA_FUNDS_STATUS''
AND xlk1.lookup_code(+) = aeh.funds_status_code
AND xlk2.lookup_type = ''XLA_ACCOUNTING_CLASS''
AND xlk2.lookup_code = ael.accounting_class_code
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND xect.application_id = xet.application_id
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.LANGUAGE = USERENV(''LANG'')
AND xett.application_id = xet.application_id
AND xett.entity_code = xet.entity_code
AND xett.event_class_code = xet.event_class_code
AND xett.event_type_code = xet.event_type_code
AND xett.LANGUAGE = USERENV(''LANG'')
AND ent.entity_id = xle.entity_id
AND ent.application_id = xle.application_id
AND xle.application_id = aeh.application_id
AND fap.application_id = aeh.application_id
AND fap.LANGUAGE = USERENV(''LANG'')
AND gdct.conversion_type(+) = ael.currency_conversion_type
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = ''P''
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV(''LANG'')
AND gjst.je_source_name = gjh.je_source
AND gjst.LANGUAGE = USERENV(''LANG'')
AND aeh.ledger_id = fia.ledger_id
AND aeh.balance_type_code = ''A''
AND ael.code_combination_id = fia.ccid
AND fia.ledger_id = glv.ledger_id
AND fia.from_le_id = glv.legal_entity_id
AND glv.period_set_name = glp.period_set_name
AND glv.accounted_period_type = glp.period_type
AND glp.period_name = aeh.period_name
AND glp.period_name = gjh.period_name ||''''
AND xsrc.je_source_name (+) = gjh.je_source
AND glv.ledger_category_code = ''PRIMARY''
AND gjh.ledger_id = gl.ledger_id
and aeh.ledger_id =ael.ledger_id
AND gl.ledger_category_code = ''PRIMARY''';
'SELECT fun_act.TRANSACTING_LE_ID SRC_TRANS_LE_ID
,fun_act.TRANSACTING_LE SRC_TRANS_LE
,fun_act.TRADING_PARTNER_LE_ID SRC_TRAD_LE_ID
,fun_act.TRADING_PARTNER_LE SRC_TRAD_LE
,fun_act.TRANSACTING_LEDGER SRC_TRANS_LEDGER
,fun_act.TRANSACTING_LEDGER_ID SRC_TRANS_LEDGER_ID
,gjct.user_je_category_name JOURNAL_CATEGORY
,gjst.user_je_source_name JOURNAL_SOURCE
,gjh.currency_code TRX_CURR
,gjl.period_name PERIOD_NAME
,LTRIM(TO_CHAR(NVL(sum(decode(fun_act.acct_type,''R'',(Nvl(gjl.entered_dr,0)), 0)),0),''999999999999999999999.999999999999'')) AR_ENTERED_DR
,LTRIM(TO_CHAR(NVL(sum(decode(fun_act.acct_type,''R'',(Nvl(gjl.entered_cr,0)), 0)),0),''999999999999999999999.999999999999'')) AR_ENTERED_CR
,LTRIM(TO_CHAR(NVL(sum(decode(fun_act.acct_type,''R'',(Nvl(gjl.accounted_dr,0)), 0)),0),''999999999999999999999.999999999999'')) AR_ACCOUNTED_DR
,LTRIM(TO_CHAR(NVL(sum(decode(fun_act.acct_type,''R'',(Nvl(gjl.accounted_cr,0)), 0)),0),''999999999999999999999.999999999999'')) AR_ACCOUNTED_CR
,LTRIM(TO_CHAR(NVL(sum(decode(fun_act.acct_type,''P'',(Nvl(gjl.entered_dr,0)), 0)),0),''999999999999999999999.999999999999'')) AP_ENTERED_DR
,LTRIM(TO_CHAR(NVL(sum(decode(fun_act.acct_type,''P'',(Nvl(gjl.entered_cr,0)), 0)),0),''999999999999999999999.999999999999'')) AP_ENTERED_CR
,LTRIM(TO_CHAR(NVL(sum(decode(fun_act.acct_type,''P'',(Nvl(gjl.accounted_dr,0)), 0)),0),''999999999999999999999.999999999999'')) AP_ACCOUNTED_DR
,LTRIM(TO_CHAR(NVL(sum(decode(fun_act.acct_type,''P'',(Nvl(gjl.accounted_cr,0)), 0)),0),''999999999999999999999.999999999999'')) AP_ACCOUNTED_CR
FROM gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_periods glp
, ($get_accounts_query$) fun_act
WHERE gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = ''P''
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV(''LANG'')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV(''LANG'')
AND gjh.actual_flag = ''A''
AND gjl.code_combination_id = fun_act.ccid
AND gjl.ledger_id = fun_act.TRANSACTING_LEDGER_ID
AND glp.period_set_name = fun_act.period_set_name
AND glp.period_type = fun_act.accounted_period_type
AND glp.period_name = gjl.period_name
AND gjl.ledger_id = gjh.ledger_id ';
' SELECT SRC_TRANS_LE_ID
,SRC_TRANS_LE
,SRC_TRAD_LE_ID
,SRC_TRAD_LE
,SRC_TRANS_LEDGER
,JOURNAL_CATEGORY
,JOURNAL_SOURCE
,TRX_CURR
,PERIOD_NAME
,LTRIM(TO_CHAR(NVL(sum(decode(TYPE,''R'',(Nvl(ENTERED_DR,0)), 0)),0),''999999999999999999999.999999999999'')) AR_ENTERED_DR
,LTRIM(TO_CHAR(NVL(sum(decode(TYPE,''R'',(Nvl(ENTERED_CR,0)), 0)),0),''999999999999999999999.999999999999'')) AR_ENTERED_CR
,LTRIM(TO_CHAR(NVL(sum(decode(TYPE,''R'',(Nvl(ACCOUNTED_DR,0)), 0)),0),''999999999999999999999.999999999999'')) AR_ACCOUNTED_DR
,LTRIM(TO_CHAR(NVL(sum(decode(TYPE,''R'',(Nvl(ACCOUNTED_CR,0)), 0)),0),''999999999999999999999.999999999999'')) AR_ACCOUNTED_CR
,LTRIM(TO_CHAR(NVL(sum(decode(TYPE,''P'',(Nvl(ENTERED_DR,0)), 0)),0),''999999999999999999999.999999999999'')) AP_ENTERED_DR
,LTRIM(TO_CHAR(NVL(sum(decode(TYPE,''P'',(Nvl(ENTERED_CR,0)), 0)),0),''999999999999999999999.999999999999'')) AP_ENTERED_CR
,LTRIM(TO_CHAR(NVL(sum(decode(TYPE,''P'',(Nvl(ACCOUNTED_DR,0)), 0)),0),''999999999999999999999.999999999999'')) AP_ACCOUNTED_DR
,LTRIM(TO_CHAR(NVL(sum(decode(TYPE,''P'',(Nvl(ACCOUNTED_CR,0)), 0)),0),''999999999999999999999.999999999999'')) AP_ACCOUNTED_CR
FROM (
SELECT glv.legal_entity_id SRC_TRANS_LE_ID
,glv.LEGAL_ENTITY_NAME SRC_TRANS_LE
,fia.to_le_id SRC_TRAD_LE_ID
,fun_recon_rpt_pkg.get_legal_entity(fia.to_le_Id) SRC_TRAD_LE
,fia.ledger_id SRC_TRANS_LEDGER_ID
,glv.ledger_name SRC_TRANS_LEDGER
,gjct.user_je_category_name JOURNAL_CATEGORY
,gjst.user_je_source_name JOURNAL_SOURCE
,gjh.currency_code TRX_CURR
,gjl.period_name PERIOD_NAME
,gjl.entered_dr ENTERED_DR
,gjl.entered_cr ENTERED_CR
,gjl.accounted_dr ACCOUNTED_DR
,gjl.accounted_cr ACCOUNTED_CR
,fia.type TYPE
,glp.start_date START_DATE
,glp.end_date END_DATE
FROM gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledger_le_v glv
,fun_inter_accounts_v fia
,gl_periods glp
WHERE gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = ''P''
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV(''LANG'')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV(''LANG'')
AND gjl.ledger_id = fia.ledger_id
AND gjh.actual_flag = ''A''
AND gjl.code_combination_id = fia.ccid
AND fia.ledger_id = glv.ledger_id
AND fia.from_le_id = glv.legal_entity_id
AND glv.period_set_name = glp.period_set_name
AND glv.accounted_period_type = glp.period_type
AND glp.period_name = gjl.period_name
AND (
gjh.parent_je_header_id IS NOT NULL
OR
gjh.je_source NOT IN (SELECT DISTINCT je_source_name
FROM xla_subledgers
WHERE je_source_name <> ''Global Intercompany'')
)
UNION
SELECT glv.legal_entity_id SRC_TRANS_LE_ID
,glv.LEGAL_ENTITY_NAME SRC_TRANS_LE
,fia.to_le_id SRC_TRAD_LE_ID
,fun_recon_rpt_pkg.get_legal_entity(fia.to_le_Id) SRC_TRAD_LE
,fia.ledger_id SRC_TRANS_LEDGER_ID
,glv.ledger_name SRC_TRANS_LEDGER
,gjct.user_je_category_name JOURNAL_CATEGORY
,gjst.user_je_source_name JOURNAL_SOURCE
,gjh.currency_code TRX_CURR
,gjl.period_name PERIOD_NAME
,gjl.entered_dr ENTERED_DR
,gjl.entered_cr ENTERED_CR
,gjl.accounted_dr ACCOUNTED_DR
,gjl.accounted_cr ACCOUNTED_CR
,fia.type TYPE
,glp.start_date START_DATE
,glp.end_date END_DATE
FROM gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledger_le_v glv
,fun_inter_accounts_v fia
,gl_periods glp
,xla_ae_headers aeh
,xla_ae_lines ael
,xla_events xle
,xla_event_types_b xet
,xla_transaction_entities ent
,gl_import_references gir
WHERE gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = ''P''
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV(''LANG'')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV(''LANG'')
AND gjl.ledger_id = fia.ledger_id
AND gjh.actual_flag = ''A''
AND gjl.code_combination_id = fia.ccid
AND fia.ledger_id = glv.ledger_id
AND fia.from_le_id = glv.legal_entity_id
AND glv.period_set_name = glp.period_set_name
AND glv.accounted_period_type = glp.period_type
AND glp.period_name = gjl.period_name
AND aeh.accounting_entry_status_code = ''F''
AND aeh.gl_transfer_status_code = ''Y''
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND ent.entity_id = xle.entity_id
AND ent.application_id = xle.application_id
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
$where_clause1$
)
WHERE 1 = 1 ';
'SELECT fun_act.TRANSACTING_LE_ID TRANS_LE_ID
,fun_act.TRANSACTING_LE TRANS_LE
,fun_act.TRADING_PARTNER_LE_ID TRAD_LE_ID
,fun_act.TRADING_PARTNER_LE TRAD_LE
,fun_act.TRANSACTING_LEDGER TRANSACTING_LEDGER
,fun_act.TRANSACTING_LEDGER_ID LEDGER_ID
,''GL'' SOURCE
,fun_act.acct_type ACCOUNT_TYPE
,gjh.default_effective_date GL_DATE
,fdu.user_name CREATED_BY
,gjh.creation_date CREATION_DATE
,gjh.last_update_date LAST_UPDATE_DATE
,gjh.reference_date REFERENCE_DATE
,gjh.je_header_id HEADER_ID
,gjh.description HEADER_DESCRIPTION
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,gjb.NAME GL_BATCH_NAME
,gjb.posted_date POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjl.je_line_num GL_LINE_NUMBER
,gjl.description LINE_DESCRIPTION
,gjh.currency_code ENTERED_CURRENCY
,LTRIM(TO_CHAR(gjh.currency_conversion_rate ,''999999999999999999999.999999999999'')) CONVERSION_RATE
,gjh.currency_conversion_date CONVERSION_RATE_DATE
,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,LTRIM(TO_CHAR(gjl.entered_dr,''999999999999999999999.999999999999'')) ENTERED_DR
,LTRIM(TO_CHAR(gjl.entered_cr,''999999999999999999999.999999999999'')) ENTERED_CR
,LTRIM(TO_CHAR(gjl.accounted_dr,''999999999999999999999.999999999999'')) ACCOUNTED_DR
,LTRIM(TO_CHAR(gjl.accounted_cr,''999999999999999999999.999999999999'')) ACCOUNTED_CR
,gjl.code_combination_id CODE_COMBINATION_ID
,gjl.period_name PERIOD_NAME
,fun_trx_entry_util.get_concatenated_account(gjl.code_combination_id ) ACCOUNT
,LTRIM(TO_CHAR(gjl.stat_amount,''999999999999999999999.999999999999'')) STATISTICAL_AMOUNT
,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gjl.context ATTRIBUTE_CATEGORY
,gjl.attribute1 ATTRIBUTE1
,gjl.attribute2 ATTRIBUTE2
,gjl.attribute3 ATTRIBUTE3
,gjl.attribute4 ATTRIBUTE4
,gjl.attribute5 ATTRIBUTE5
,gjl.attribute6 ATTRIBUTE6
,gjl.attribute7 ATTRIBUTE7
,gjl.attribute8 ATTRIBUTE8
,gjl.attribute9 ATTRIBUTE9
,gjl.attribute10 ATTRIBUTE10
,gjl.attribute11 ATTRIBUTE11
,gjl.attribute12 ATTRIBUTE12
,gjl.attribute13 ATTRIBUTE13
,gjl.attribute14 ATTRIBUTE14
,gjl.attribute15 ATTRIBUTE15
,gjl.attribute16 ATTRIBUTE16
,gjl.attribute17 ATTRIBUTE17
,gjl.attribute18 ATTRIBUTE18
,gjl.attribute19 ATTRIBUTE19
,gjl.attribute20 ATTRIBUTE20
,gjl.je_header_id||''-''||gjl.je_line_num EXPAND_ID
FROM fnd_user fdu
,gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_daily_conversion_types gdct
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_periods glp
, ($get_accounts_query$) fun_act
WHERE gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = ''P''
AND fdu.user_id = gjb.created_by
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV(''LANG'')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV(''LANG'')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
AND (gjh.parent_je_header_id IS NOT NULL
OR
gjh.je_source NOT IN (SELECT DISTINCT je_source_name
FROM xla_subledgers
WHERE je_source_name <> ''Global Intercompany'')
)
AND gjl.ledger_id = gjh.ledger_id
AND gjh.actual_flag = ''A''
AND gjl.code_combination_id = fun_act.ccid
AND gjl.ledger_id = fun_act.TRANSACTING_LEDGER_ID
AND glp.period_set_name = fun_act.period_set_name
AND glp.period_type = fun_act.accounted_period_type
AND glp.period_name = gjl.period_name ';
'SELECT glv.legal_entity_id TRANS_LE_ID
,glv.LEGAL_ENTITY_NAME TRANS_LE
,fia.to_le_id TRAD_LE_ID
,fun_recon_rpt_pkg.get_legal_entity(fia.to_le_Id) TRAD_LE
,glv.ledger_name TRANSACTING_LEDGER
,glv.ledger_id LEDGER_ID
,''GL'' SOURCE
,fia.type ACCOUNT_TYPE
,gjh.default_effective_date GL_DATE
,fdu.user_name CREATED_BY
,gjh.creation_date CREATION_DATE
,gjh.last_update_date LAST_UPDATE_DATE
,gjh.reference_date REFERENCE_DATE
,gjh.je_header_id HEADER_ID
,gjh.description HEADER_DESCRIPTION
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,gjb.NAME GL_BATCH_NAME
,gjb.posted_date POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjl.je_line_num GL_LINE_NUMBER
,gjl.description LINE_DESCRIPTION
,gjh.currency_code ENTERED_CURRENCY
,LTRIM(TO_CHAR(gjh.currency_conversion_rate,''999999999999999999999.999999999999'')) CONVERSION_RATE
,gjh.currency_conversion_date CONVERSION_RATE_DATE
,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,LTRIM(TO_CHAR(gjl.entered_dr,''999999999999999999999.999999999999'')) ENTERED_DR
,LTRIM(TO_CHAR(gjl.entered_cr,''999999999999999999999.999999999999'')) ENTERED_CR
,LTRIM(TO_CHAR(gjl.accounted_dr,''999999999999999999999.999999999999'')) ACCOUNTED_DR
,LTRIM(TO_CHAR(gjl.accounted_cr,''999999999999999999999.999999999999'')) ACCOUNTED_CR
,gjl.code_combination_id CODE_COMBINATION_ID
,gjl.period_name PERIOD_NAME
,fun_trx_entry_util.get_concatenated_account(gjl.code_combination_id ) ACCOUNT
,LTRIM(TO_CHAR(gjl.stat_amount,''999999999999999999999.999999999999'')) STATISTICAL_AMOUNT
,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gjl.context ATTRIBUTE_CATEGORY
,gjl.attribute1 ATTRIBUTE1
,gjl.attribute2 ATTRIBUTE2
,gjl.attribute3 ATTRIBUTE3
,gjl.attribute4 ATTRIBUTE4
,gjl.attribute5 ATTRIBUTE5
,gjl.attribute6 ATTRIBUTE6
,gjl.attribute7 ATTRIBUTE7
,gjl.attribute8 ATTRIBUTE8
,gjl.attribute9 ATTRIBUTE9
,gjl.attribute10 ATTRIBUTE10
,gjl.attribute11 ATTRIBUTE11
,gjl.attribute12 ATTRIBUTE12
,gjl.attribute13 ATTRIBUTE13
,gjl.attribute14 ATTRIBUTE14
,gjl.attribute15 ATTRIBUTE15
,gjl.attribute16 ATTRIBUTE16
,gjl.attribute17 ATTRIBUTE17
,gjl.attribute18 ATTRIBUTE18
,gjl.attribute19 ATTRIBUTE19
,gjl.attribute20 ATTRIBUTE20
,gjl.je_header_id||''-''||gjl.je_line_num EXPAND_ID
FROM fnd_user fdu
,gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_daily_conversion_types gdct
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledger_le_v glv
,fun_inter_accounts_v fia
,gl_periods glp
WHERE gjh.je_header_id = gjl.je_header_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = ''P''
AND fdu.user_id = gjb.created_by
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV(''LANG'')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV(''LANG'')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
AND (gjh.parent_je_header_id IS NOT NULL
OR
gjh.je_source NOT IN (SELECT DISTINCT je_source_name
FROM xla_subledgers
WHERE je_source_name <> ''Global Intercompany'')
)
AND gjl.ledger_id = fia.ledger_id
AND gjh.actual_flag = ''A''
AND gjl.code_combination_id = fia.ccid
AND fia.ledger_id = glv.ledger_id
AND fia.from_le_id = glv.legal_entity_id
AND glv.period_set_name = glp.period_set_name
AND glv.accounted_period_type = glp.period_type
AND glp.period_name = gjl.period_name ';
l_select_str VARCHAR2(4000);
l_insert_query VARCHAR2(4000);
SELECT USERENV('LANG')
INTO l_lang
FROM dual;
l_array_sql.DELETE;
l_param_query := 'Select '
||''''||p_trans_ledger_id ||''''||' p_trans_ledger_id,'
||''''||p_trans_legal_entity_id ||''''||' p_TRANS_LEGAL_ENTITY_ID,'
||''''||p_trans_gl_period ||''''||' p_trans_gl_period,'
||''''||p_tp_ledger_id ||''''||' p_tp_ledger_id,'
||''''||p_tp_legal_entity_id ||''''||' p_TP_LEGAL_ENTITY_ID,'
||''''||p_currency ||''''||' p_CURRENCY,'
||''''||p_tp_gl_period ||''''||' p_tp_gl_period,'
||''''||p_rate_type ||''''||' p_rate_type,'
||''''||to_char(fnd_date.canonical_to_date(p_rate_date)) ||''''||' p_rate_date'
||' FROM DUAL ';
SELECT glp.start_date,
glp.end_date
FROM gl_periods glp,
gl_ledgers gl
WHERE glp.period_set_name = gl.period_set_name
AND glp.period_type = gl.accounted_period_type
AND glp.period_name = p_period_name
AND gl.ledger_id = p_ledger_id;
l_sum_unmatched_query := l_sum_unmatched_query || ' AND SRC_TRAD_LE_ID IN (SELECT tole.legal_entity_id
FROM gl_ledger_le_v tole
WHERE tole.ledger_id = '||p_tp_ledger_id ||')';
l_gl_unmatched_query := l_gl_unmatched_query || ' AND fia.to_le_id IN (SELECT tole.legal_entity_id
FROM gl_ledger_le_v tole
WHERE tole.ledger_id = '||p_tp_ledger_id ||')';
l_sla_unmatched_query := l_sla_unmatched_query || ' AND fia.to_le_id IN (SELECT tole.legal_entity_id
FROM gl_ledger_le_v tole
WHERE tole.ledger_id = '||p_tp_ledger_id ||')';
l_sum_unmatched_query_rev := l_sum_unmatched_query_rev || ' AND SRC_TRAD_LE_ID IN (SELECT tole.legal_entity_id
FROM gl_ledger_le_v tole
WHERE tole.ledger_id = '||p_trans_ledger_id ||')';
l_gl_unmatched_query_rev := l_gl_unmatched_query_rev || ' AND fia.to_le_id IN (SELECT tole.legal_entity_id
FROM gl_ledger_le_v tole
WHERE tole.ledger_id = '||p_trans_ledger_id ||')';
l_sla_unmatched_query_rev := l_sla_unmatched_query_rev || ' AND fia.to_le_id IN (SELECT tole.legal_entity_id
FROM gl_ledger_le_v tole
WHERE tole.ledger_id = '||p_trans_ledger_id ||')';
select distinct name
into v_le_name
from xle_entity_profiles
where legal_entity_id = p_le_id
and rownum=1;
select CURRENCY_CODE
INTO l_currency
from gl_ledgers
where ledger_id = p_trans_ledger_id;
SELECT sum(glb.begin_balance_dr),
sum(glb.begin_balance_cr),
sum(glb.period_net_dr),
sum(glb.period_net_cr)
INTO l_begin_balance_dr, l_begin_balance_cr, l_period_net_dr, l_period_net_cr
FROM gl_balances glb
WHERE glb.period_name = p_trans_gl_period
AND glb.ledger_id = p_trans_ledger_id
AND glb.actual_flag = 'A'
AND glb.currency_code = p_currency
AND glb.translated_flag = 'R'
AND glb.code_combination_id IN (SELECT DISTINCT fia.ccid
FROM fun_inter_accounts_v fia
WHERE fia.ledger_id = p_trans_ledger_id
AND fia.from_le_id = p_trans_le_id
AND fia.to_le_id = p_trad_le_id
AND fia.type = 'R' );
SELECT sum(glb.begin_balance_dr_beq),
sum(glb.begin_balance_cr_beq),
sum(glb.period_net_dr_beq),
sum(glb.period_net_cr_beq)
INTO l_begin_balance_dr, l_begin_balance_cr, l_period_net_dr, l_period_net_cr
FROM gl_balances glb
WHERE glb.period_name = p_trans_gl_period
AND glb.ledger_id = p_trans_ledger_id
AND glb.actual_flag = 'A'
AND glb.currency_code = p_currency
AND glb.translated_flag is NULL
AND glb.code_combination_id IN (SELECT DISTINCT fia.ccid
FROM fun_inter_accounts_v fia
WHERE fia.ledger_id = p_trans_ledger_id
AND fia.from_le_id = p_trans_le_id
AND fia.to_le_id = p_trad_le_id
AND fia.type = 'R' );
select CURRENCY_CODE
INTO l_currency
from gl_ledgers
where ledger_id = p_trad_ledger_id;
SELECT sum(glb.begin_balance_dr), sum(glb.begin_balance_cr), sum(glb.period_net_dr), sum(glb.period_net_cr)
INTO l_begin_balance_dr, l_begin_balance_cr, l_period_net_dr, l_period_net_cr
FROM gl_balances glb
WHERE glb.period_name = p_trad_gl_period
AND glb.ledger_id = p_trad_ledger_id
AND glb.actual_flag = 'A'
AND glb.currency_code = p_currency
AND glb.translated_flag = 'R'
AND glb.code_combination_id IN (SELECT DISTINCT fia.ccid
FROM fun_inter_accounts_v fia
WHERE fia.ledger_id = p_trad_ledger_id
AND fia.from_le_id = p_trad_le_id
AND fia.to_le_id = p_trans_le_id
AND fia.type = 'P' );
SELECT sum(glb.begin_balance_dr_beq), sum(glb.begin_balance_cr_beq), sum(glb.period_net_dr_beq), sum(glb.period_net_cr_beq)
INTO l_begin_balance_dr, l_begin_balance_cr, l_period_net_dr, l_period_net_cr
FROM gl_balances glb
WHERE glb.period_name = p_trad_gl_period
AND glb.ledger_id = p_trad_ledger_id
AND glb.actual_flag = 'A'
AND glb.currency_code = p_currency
AND glb.translated_flag is NULL
AND glb.code_combination_id IN (SELECT DISTINCT fia.ccid
FROM fun_inter_accounts_v fia
WHERE fia.ledger_id = p_trad_ledger_id
AND fia.from_le_id = p_trad_le_id
AND fia.to_le_id = p_trans_le_id
AND fia.type = 'P' );
select invoice_num
into l_invoice_num
from ap_invoices_all
where invoice_id = l_ap_invoice_id;
select invoice_id
into l_ap_invoice_id
from ap_invoices_all
where invoice_num = l_ar_invoice_id;
SELECT glp.end_date, glp.adjustment_period_flag
INTO l_trans_gl_prd_end_date, l_trans_adj_period_flag
FROM gl_periods glp,
gl_ledgers gl
WHERE glp.period_set_name = gl.period_set_name
AND glp.period_type = gl.accounted_period_type
AND glp.period_name = l_trans_gl_period
AND gl.ledger_id = l_trans_ledger_id;
SELECT glp.period_name
INTO l_trad_gl_period
FROM gl_periods glp,
gl_ledgers gl
WHERE glp.period_set_name = gl.period_set_name
AND glp.period_type = gl.accounted_period_type
AND gl.ledger_id = l_trad_ledger_id
AND l_trans_gl_prd_end_date between glp.start_date and glp.end_date
AND glp.adjustment_period_flag = l_trans_adj_period_flag;
SELECT
sum(nvl(ael.entered_cr, 0) - nvl(ael.entered_dr, 0))
INTO l_payables_net_cr
FROM xla_ae_headers aeh
,xla_ae_lines ael
,xla_events xle
,xla_event_types_b xet
,xla_transaction_entities ent
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledger_le_v glv
,fun_inter_accounts_v fia
,gl_periods glp
WHERE aeh.accounting_entry_status_code = 'F'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND ent.entity_id = xle.entity_id
AND ent.application_id = xle.application_id
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = 'P'
AND aeh.ledger_id = fia.ledger_id
AND aeh.balance_type_code = 'A'
AND ael.code_combination_id = fia.ccid
AND fia.ledger_id = glv.ledger_id
AND fia.from_le_id = glv.legal_entity_id
AND glv.period_set_name = glp.period_set_name
AND glv.accounted_period_type = glp.period_type
AND glp.period_name = aeh.period_name
AND ent.application_id = 200
AND ent.entity_code = 'AP_INVOICES'
AND ent.source_id_int_1 = l_ap_invoice_id
AND fia.type = 'P'
AND fia.from_le_id = l_trans_le_id
AND fia.ledger_id = l_trans_ledger_id
AND glp.period_name = l_trans_gl_period
AND fia.to_le_id = l_trad_le_id;
SELECT
sum(nvl(ael.entered_dr, 0) - nvl(ael.entered_cr, 0))
INTO l_receivables_net_dr
FROM xla_ae_headers aeh
,xla_ae_lines ael
,xla_events xle
,xla_event_types_b xet
,xla_transaction_entities ent
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledger_le_v glv
,fun_inter_accounts_v fia
,gl_periods glp
WHERE aeh.accounting_entry_status_code = 'F'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND ent.entity_id = xle.entity_id
AND ent.application_id = xle.application_id
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = 'P'
AND aeh.ledger_id = fia.ledger_id
AND aeh.balance_type_code = 'A'
AND ael.code_combination_id = fia.ccid
AND fia.ledger_id = glv.ledger_id
AND fia.from_le_id = glv.legal_entity_id
AND glv.period_set_name = glp.period_set_name
AND glv.accounted_period_type = glp.period_type
AND glp.period_name = aeh.period_name
AND ent.application_id = 222
AND ent.entity_code = 'BILLS_RECEIVABLE'
AND ent.source_id_int_1 = l_ar_invoice_id
AND fia.type = 'R'
AND fia.from_le_id = l_trad_le_id
AND fia.ledger_id = nvl(l_trad_ledger_id, fia.ledger_id)
AND glp.period_name = l_trad_gl_period
AND fia.to_le_id = l_trans_le_id;
SELECT
sum(nvl(ael.entered_cr, 0) - nvl(ael.entered_dr, 0))
INTO l_payables_net_cr
FROM xla_ae_headers aeh
,xla_ae_lines ael
,xla_events xle
,xla_event_types_b xet
,xla_transaction_entities ent
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledger_le_v glv
,fun_inter_accounts_v fia
,gl_periods glp
WHERE aeh.accounting_entry_status_code = 'F'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND ent.entity_id = xle.entity_id
AND ent.application_id = xle.application_id
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = 'P'
AND aeh.ledger_id = fia.ledger_id
AND aeh.balance_type_code = 'A'
AND ael.code_combination_id = fia.ccid
AND fia.ledger_id = glv.ledger_id
AND fia.from_le_id = glv.legal_entity_id
AND glv.period_set_name = glp.period_set_name
AND glv.accounted_period_type = glp.period_type
AND glp.period_name = aeh.period_name
AND ent.application_id = 200
AND ent.entity_code = 'AP_INVOICES'
AND ent.source_id_int_1 = l_ap_invoice_id
AND fia.type = 'P'
AND fia.from_le_id = l_trad_le_id
AND fia.ledger_id = nvl(l_trad_ledger_id, fia.ledger_id)
AND glp.period_name = l_trad_gl_period;
SELECT
sum(nvl(ael.entered_dr, 0) - nvl(ael.entered_cr, 0))
INTO l_receivables_net_dr
FROM xla_ae_headers aeh
,xla_ae_lines ael
,xla_events xle
,xla_event_types_b xet
,xla_transaction_entities ent
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,gl_ledger_le_v glv
,fun_inter_accounts_v fia
,gl_periods glp
WHERE aeh.accounting_entry_status_code = 'F'
AND aeh.gl_transfer_status_code = 'Y'
AND ael.ae_header_id = aeh.ae_header_id
AND ael.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND ent.entity_id = xle.entity_id
AND ent.application_id = xle.application_id
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = 'P'
AND aeh.ledger_id = fia.ledger_id
AND aeh.balance_type_code = 'A'
AND ael.code_combination_id = fia.ccid
AND fia.ledger_id = glv.ledger_id
AND fia.from_le_id = glv.legal_entity_id
AND glv.period_set_name = glp.period_set_name
AND glv.accounted_period_type = glp.period_type
AND glp.period_name = aeh.period_name
AND ent.application_id = 222
AND ent.entity_code = 'BILLS_RECEIVABLE'
AND ent.source_id_int_1 = l_ar_invoice_id
AND fia.type = 'R'
AND fia.from_le_id = l_trans_le_id
AND fia.ledger_id = l_trans_ledger_id
AND glp.period_name = l_trans_gl_period;