--LHK Account Analysis report
SELECT
--period_name,
-- (select max(TO_CHAR(end_date,'MM-DD-YYYY')) period_name from gl_period_statuses where period_name=:p_period_from AND Application_Id=101) period_name,
(select TO_CHAR(end_date,'MM-DD-YYYY') period_name from gl_period_statuses where period_name=:p_period_from AND Application_Id=101 AND ledger_id =:p_ledger_name) period_name,
created_by,
posted_by,
source,
category,
to_char(posting_date, 'DD-MM-YYYY') posting_date,
-- , TO_CHAR(accounting_date,'MM/DD/RRRR') accounting_date
to_char(accounting_date, 'MM-DD-YYYY') accounting_date,
batch_name,
journal_name,
journal_line_num,
-- , REPLACE(REPLACE(journal_line_desc, CHR(10), ' '), CHR(13), ' ') journal_line_desc
replace(replace(journal_desc,CHR(10),' '),CHR(13),' ') journal_desc,
-- , journal_desc
replace(replace(journal_line_desc,CHR(10),' '),CHR(13),' ') journal_line_desc,
code_comb,
property,
region,
account,
Cost_Center,
inter_company,
project,
future1,
property_description,
region_description,
account_description,
Cost_Center_description,
IC_description,
project_description,
future1_description,
accounted_dr,
accounted_cr,
(nvl(accounted_dr, 0) - nvl(accounted_cr, 0)) Accounted_Net,
entered_dr,
entered_cr,
(nvl(entered_dr, 0) - nvl(entered_cr, 0)) Net_Entered,
accounted_dr gbp_local_currency_dr,
accounted_cr gbp_local_currency_cr,
trx_type,
currency_code,
curr_conve_rate,
curr_conve_type,
to_char(corr_conve_date, 'MM-DD-YYYY') corr_conve_date,
-- , TO_CHAR(Corr_Conve_Date,'MM/DD/RRRR') Corr_Conve_Date
custom_trx_type,
vendor_customer_name,
TO_CHAR(trx_date,'MM-DD-YYYY') trx_date,
trx_number,
check_receipt_number,
-- po_number,
beginning_balance,
ending_balance,
-- beginning_balance_acc,
-- ending_balance_acc,
-- employee_name,
-- expense_report_name,
-- expense_type,
-- concur_vendor
(SELECT
fnd_global.user_name
FROM Dual) user_name_1,
(SELECT
TO_CHAR(CAST((FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') AS DATE), 'MM-DD-YYYY HH:MI:SS AM') || ' PST' EXECUTION_TIME_PST
FROM Dual
) EXECUTION_TIME_PST_1,
(SELECT
TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS AM') REPORT_RUN_TIME
FROM Dual) REPORT_RUN_TIME_1
FROM
(
SELECT
gjh.period_name period_name,
gjh.created_by created_by,
dis_name.display_name posted_by,
(
SELECT
gjs.je_source_key
FROM
gl_je_sources gjs
WHERE
gjh.je_source = gjs.je_source_name
) source,
(
SELECT
gjc.je_category_key
FROM
gl_je_categories gjc
WHERE
gjh.je_category = gjc.je_category_name
) category,
gjh.posted_date posting_date,
gjh.default_effective_date accounting_date,
gjb.name batch_name,
gjh.name journal_name,
gjl.je_line_num journal_line_num,
replace(gjh.description,CHR(13),' ') journal_desc,
replace(gjl.description,CHR(13),' ') journal_line_desc,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7
code_comb,
gcc.segment1 Property
,gcc.segment2 Region
,gcc.segment3 Account
,gcc.segment4 Cost_Center
,gcc.segment5 Inter_Company
,gcc.segment6 Project
,gcc.segment7 Future1,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1) Property_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,2,gcc.segment2) Region_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) Account_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,4,gcc.segment4) Cost_Center_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,5,gcc.segment5) IC_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,6,gcc.segment6) Project_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,8,gcc.segment7) future1_description,
gjl.accounted_dr accounted_dr,
gjl.accounted_cr accounted_cr,
gjl.entered_dr entered_dr,
gjl.entered_cr entered_cr,
nvl(gjh.currency_code,gjl.currency_code) currency_code,
gjl.currency_conversion_rate curr_conve_rate,
gl_rate_type.user_conversion_type curr_conve_type,
gjl.currency_conversion_date corr_conve_date,
gjl.attribute1 vendor_customer_name,
gjh.default_effective_date trx_date,
TO_CHAR(NULL) trx_number,
(
SELECT
gjs.je_source_key
FROM
gl_je_sources gjs
WHERE
gjh.je_source = gjs.je_source_name
) trx_type,
NULL check_receipt_number,
gjl.attribute7 po_number,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance_acc,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance_acc,
gjl.attribute2 employee_name,
gjl.attribute6 expense_report_name,
gjl.attribute4 expense_type,
gjl.attribute8 concur_vendor,
'GL Transactions' custom_trx_type
FROM
gl_ledgers gl,
gl_je_categories gjc,
gl_je_headers gjh,
gl_je_batches gjb,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_period_statuses gp,
gl_daily_conversion_types gl_rate_type,
gl_period_statuses gps_from,
gl_period_statuses gps_to,
(
SELECT
gja1.user_id display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id <> ( 'IMPLSU' )
UNION
SELECT
'IMPLSU' display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id = ( 'IMPLSU' )
) dis_name
WHERE
1 = 1
AND gjl.currency_code != 'STAT'
AND gl.ledger_id = gjh.ledger_id
AND gjh.je_category = gjc.je_category_name
AND gjb.average_journal_flag = 'N'
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gl_rate_type.conversion_type = gjl.currency_conversion_type
AND gjl.status = 'P'
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = gl.chart_of_accounts_id
AND gjh.je_source NOT IN (
'Payables',
'Assets',
'Cash Management'
) -- NOT IN ('Assets','Payables','Receivables','Cash Management','Global Intercompany')
--,'Project Accounting','Global Intercompany','Balance Transfer')
AND gjh.period_name = gp.period_name
AND gp.adjustment_period_flag = 'N'
AND dis_name.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = dis_name.je_header_id
AND gl.ledger_id = gps_from.ledger_id
AND gl.ledger_id = gps_to.ledger_id
AND gp.ledger_id =:p_ledger_name
AND gps_from.ledger_id =:p_ledger_name
AND gps_to.ledger_id =:p_ledger_name
AND gp.application_id = 101
AND gps_from.application_id = 101
AND gps_to.application_id = 101
AND gp.start_date >= gps_from.start_date
AND gp.end_date <= gps_to.end_date
AND gps_from.period_name =:p_period_from
AND gps_to.period_name =:p_period_to
AND gjh.ledger_id =:p_ledger_name
-- and gjh.period_name= :P_Period
AND gcc.segment1 BETWEEN :P_FROM_PROPERTY AND :P_TO_PROPERTY
AND gcc.segment2 BETWEEN :P_FROM_REGION AND :P_TO_REGION
AND gcc.segment3 BETWEEN :P_FROM_ACCOUNT AND :P_TO_ACCOUNT
AND gcc.segment4 BETWEEN :P_FROM_CC AND :P_TO_CC
AND gcc.segment5 BETWEEN :P_FROM_INTER AND :P_TO_INTER
AND gcc.segment6 BETWEEN :P_FROM_PROJECT AND :P_TO_PROJECT
--
UNION ALL
--
SELECT
gjh.period_name period_name,
gjh.created_by created_by,
dis_name.display_name posted_by,
(
SELECT
gjs.je_source_key
FROM
gl_je_sources gjs
WHERE
gjh.je_source = gjs.je_source_name
) source,
(
SELECT
gjc.je_category_key
FROM
gl_je_categories gjc
WHERE
gjh.je_category = gjc.je_category_name
) category,
gjh.posted_date posting_date,
xal.accounting_date accounting_date,
gjb.name batch_name,
gjh.name journal_name,
gjl.je_line_num journal_line_num,
replace(gjh.description,CHR(13),' ') journal_desc,
aia.description journal_line_desc,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7
code_comb,
gcc.segment1 Property
,gcc.segment2 Region
,gcc.segment3 Account
,gcc.segment4 Cost_Center
,gcc.segment5 Inter_Company
,gcc.segment6 Project
,gcc.segment7 Future1,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1) Property_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,2,gcc.segment2) Region_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) Account_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,4,gcc.segment4) Cost_Center_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,5,gcc.segment5) IC_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,6,gcc.segment6) Project_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,8,gcc.segment7) future1_description,
xal.unrounded_accounted_dr accounted_dr,
xal.unrounded_accounted_cr accounted_cr,
xal.unrounded_entered_dr entered_dr,
xal.unrounded_entered_cr entered_cr,
nvl(gjh.currency_code,gjl.currency_code) currency_code,
DECODE(aia.invoice_currency_code,'USD',1,aia.exchange_rate) curr_conve_rate,
DECODE(aia.invoice_currency_code,'USD','User',aia.exchange_rate_type) curr_conve_type,
DECODE(aia.invoice_currency_code,'USD',aia.invoice_date,aia.exchange_date) corr_conve_date,
hp.party_name vendor_customer_name,
aia.invoice_date trx_date,
aia.invoice_num trx_number,
xet.name trx_type,
NULL check_receipt_number
-- ,DECODE(xal.ACCOUNTING_CLASS_CODE,'LIABILITY','',nvl(poh.segment1,'')) po_number
,
/* nvl( (
SELECT DISTINCT
poh.segment1
FROM
po_headers_all poh,po_distributions_all pod,ap_invoices_all aia1,ap_invoice_distributions_all aid,ap_invoice_lines_all ail
WHERE
pod.po_header_id = poh.po_header_id
AND aid.po_distribution_id = pod.po_distribution_id
AND aid.invoice_id = aia1.invoice_id
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aia1.invoice_id = aia.invoice_id
AND aid.reversal_flag IN(
'Y','N'
)
--AND rownum<2
),'') po_number, */
(select poh.segment1 from po_headers_all poh where poh.po_header_id = aia.po_header_id) po_number, -- Added by Rama 02042020
-- NULL po_number,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance_acc,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance_acc,
gjl.attribute2 employee_name,
gjl.attribute6 expense_report_name,
gjl.attribute4 expense_type,
gjl.attribute8 concur_vendor,
'AP Invoices' custom_trx_type
FROM
gl_ledgers gl,
gl_je_categories gjc,
gl_je_headers gjh,
gl_je_batches gjb,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_event_types_tl xet,
ap_invoices_all aia,
gl_period_statuses gp,
xla_transaction_entities xte,
hz_parties hp,
gl_period_statuses gps_from,
gl_period_statuses gps_to,
(
SELECT
gja1.user_id display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id <> ( 'IMPLSU' )
UNION
SELECT
'IMPLSU' display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id = ( 'IMPLSU' )
) dis_name
WHERE
1 = 1
AND gjl.currency_code != 'STAT'
AND gl.ledger_id = gjh.ledger_id
AND gjh.je_category = gjc.je_category_name
AND gjb.average_journal_flag = 'N'
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = gl.chart_of_accounts_id
AND gjh.je_source = 'Payables'
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND gjh.period_name = gp.period_name
AND gp.adjustment_period_flag = 'N'
AND gjc.je_category_name = 'Purchase Invoices'
AND xe.application_id = xte.application_id
AND xe.entity_id = xte.entity_id
AND xte.source_id_int_1 = aia.invoice_id
AND aia.party_id = hp.party_id
AND dis_name.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = dis_name.je_header_id
AND gl.ledger_id = gps_from.ledger_id
AND gl.ledger_id = gps_to.ledger_id
AND gp.ledger_id =:p_ledger_name
AND gps_from.ledger_id =:p_ledger_name
AND gps_to.ledger_id =:p_ledger_name
AND gp.application_id = 101
AND gps_from.application_id = 101
AND gps_to.application_id = 101
AND gp.start_date >= gps_from.start_date
AND gp.end_date <= gps_to.end_date
AND gps_from.period_name =:p_period_from
AND gps_to.period_name =:p_period_to
AND gjh.ledger_id =:p_ledger_name
-- and gjh.period_name= :P_Period
AND gcc.segment1 BETWEEN :P_FROM_PROPERTY AND :P_TO_PROPERTY
AND gcc.segment2 BETWEEN :P_FROM_REGION AND :P_TO_REGION
AND gcc.segment3 BETWEEN :P_FROM_ACCOUNT AND :P_TO_ACCOUNT
AND gcc.segment4 BETWEEN :P_FROM_CC AND :P_TO_CC
AND gcc.segment5 BETWEEN :P_FROM_INTER AND :P_TO_INTER
AND gcc.segment6 BETWEEN :P_FROM_PROJECT AND :P_TO_PROJECT
--
UNION ALL
--
SELECT
gjh.period_name period_name,
gjh.created_by created_by,
dis_name.display_name posted_by,
(
SELECT
gjs.je_source_key
FROM
gl_je_sources gjs
WHERE
gjh.je_source = gjs.je_source_name
) source,
(
SELECT
gjc.je_category_key
FROM
gl_je_categories gjc
WHERE
gjh.je_category = gjc.je_category_name
) category,
gjh.posted_date posting_date,
xal.accounting_date accounting_date,
gjb.name batch_name,
gjh.name journal_name,
gjl.je_line_num journal_line_num,
replace(gjh.description,CHR(13),' ') journal_desc,
aca.description journal_line_desc,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7
code_comb,
gcc.segment1 Property
,gcc.segment2 Region
,gcc.segment3 Account
,gcc.segment4 Cost_Center
,gcc.segment5 Inter_Company
,gcc.segment6 Project
,gcc.segment7 Future1,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1) Property_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,2,gcc.segment2) Region_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) Account_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,4,gcc.segment4) Cost_Center_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,5,gcc.segment5) IC_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,6,gcc.segment6) Project_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,8,gcc.segment7) future1_description,
xal.unrounded_accounted_dr accounted_dr,
xal.unrounded_accounted_cr accounted_cr,
xal.unrounded_entered_dr entered_dr,
xal.unrounded_entered_cr entered_cr,
nvl(gjh.currency_code,gjl.currency_code) currency_code,
DECODE(aca.currency_code,'USD',1,aca.exchange_rate) curr_conve_rate,
DECODE(aca.currency_code,'USD','User',aca.exchange_rate_type) curr_conve_type,
DECODE(aca.currency_code,'USD',aca.check_date,aca.exchange_date) corr_conve_date,
aca.vendor_name vendor_customer_name,
aca.check_date trx_date,
TO_CHAR(aca.check_number) trx_number,
xet.name trx_type,
TO_CHAR(aca.check_number) check_receipt_number,
NULL po_number,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance_acc,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance_acc,
gjl.attribute2 employee_name,
gjl.attribute6 expense_report_name,
gjl.attribute4 expense_type,
gjl.attribute8 concur_vendor,
'AP Payments' custom_trx_type
FROM
gl_ledgers gl,
gl_je_categories gjc,
gl_je_headers gjh,
gl_je_batches gjb,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
xla_events xe,
xla_event_types_tl xet,
ap_checks_all aca,
ap_lookup_codes alc,
gl_period_statuses gp,
xla_transaction_entities xte,
gl_period_statuses gps_from,
gl_period_statuses gps_to,
(
SELECT
gja1.user_id display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id <> ( 'IMPLSU' )
UNION
SELECT
'IMPLSU' display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id = ( 'IMPLSU' )
) dis_name
WHERE
1 = 1
AND gjl.currency_code != 'STAT'
AND gl.ledger_id = gjh.ledger_id
AND gjh.je_category = gjc.je_category_name
AND gjb.average_journal_flag = 'N'
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = gl.chart_of_accounts_id
AND gjh.je_source = 'Payables'
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND aca.status_lookup_code = alc.lookup_code
AND alc.lookup_type = 'CHECK STATE'
AND gjh.period_name = gp.period_name
AND gp.adjustment_period_flag = 'N'
AND xe.application_id = xte.application_id
AND xe.entity_id = xte.entity_id
AND xte.source_id_int_1 = aca.check_id
AND xte.entity_code = 'AP_PAYMENTS'
AND dis_name.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = dis_name.je_header_id
AND gl.ledger_id = gps_from.ledger_id
AND gl.ledger_id = gps_to.ledger_id
AND gp.ledger_id =:p_ledger_name
AND gps_from.ledger_id =:p_ledger_name
AND gps_to.ledger_id =:p_ledger_name
AND gp.application_id = 101
AND gps_from.application_id = 101
AND gps_to.application_id = 101
AND gp.start_date >= gps_from.start_date
AND gp.end_date <= gps_to.end_date
AND gps_from.period_name =:p_period_from
AND gps_to.period_name =:p_period_to
AND gjh.ledger_id =:p_ledger_name
-- and gjh.period_name= :P_Period
AND gcc.segment1 BETWEEN :P_FROM_PROPERTY AND :P_TO_PROPERTY
AND gcc.segment2 BETWEEN :P_FROM_REGION AND :P_TO_REGION
AND gcc.segment3 BETWEEN :P_FROM_ACCOUNT AND :P_TO_ACCOUNT
AND gcc.segment4 BETWEEN :P_FROM_CC AND :P_TO_CC
AND gcc.segment5 BETWEEN :P_FROM_INTER AND :P_TO_INTER
AND gcc.segment6 BETWEEN :P_FROM_PROJECT AND :P_TO_PROJECT
UNION ALL
SELECT
gjh.period_name period_name,
gjh.created_by created_by,
dis_name.display_name posted_by,
(
SELECT
gjs.je_source_key
FROM
gl_je_sources gjs
WHERE
gjh.je_source = gjs.je_source_name
) source,
(
SELECT
gjc.je_category_key
FROM
gl_je_categories gjc
WHERE
gjh.je_category = gjc.je_category_name
) category,
gjh.posted_date posting_date,
xal.accounting_date accounting_date,
gjb.name batch_name,
gjh.name journal_name,
gjl.je_line_num journal_line_num,
replace(gjh.description,CHR(13),' ') journal_desc,
gjl.description journal_line_desc,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7
code_comb,
gcc.segment1 Property
,gcc.segment2 Region
,gcc.segment3 Account
,gcc.segment4 Cost_Center
,gcc.segment5 Inter_Company
,gcc.segment6 Project
,gcc.segment7 Future1,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1) Property_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,2,gcc.segment2) Region_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) Account_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,4,gcc.segment4) Cost_Center_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,5,gcc.segment5) IC_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,6,gcc.segment6) Project_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,8,gcc.segment7) future1_description,
xal.unrounded_accounted_dr accounted_dr,
xal.unrounded_accounted_cr accounted_cr,
xal.unrounded_entered_dr entered_dr,
xal.unrounded_entered_cr entered_cr,
nvl(gjh.currency_code,gjl.currency_code) currency_code,
gjl.currency_conversion_rate curr_conve_rate,
gjl.currency_conversion_type curr_conve_type,
gjl.currency_conversion_date corr_conve_date,
NULL vendor_customer_name,
xal.accounting_date trx_date,
fab.asset_number trx_number,
xet.name trx_type,
NULL check_receipt_number,
NULL po_number,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance_acc,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance_acc,
gjl.attribute2 employee_name,
gjl.attribute6 expense_report_name,
gjl.attribute4 expense_type,
gjl.attribute8 concur_vendor,
'Depreciation' custom_trx_type
FROM
gl_ledgers gl,
gl_je_categories gjc,
gl_je_headers gjh,
gl_je_batches gjb,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_events xe,
xla_event_types_tl xet,
fa_deprn_summary fas,
fa_additions_b fab,
fa_distribution_history fdh,
gl_period_statuses gp,
gl_period_statuses gps_from,
gl_period_statuses gps_to,
(
SELECT
gja1.user_id display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id <> ( 'IMPLSU' )
UNION
SELECT
'IMPLSU' display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id = ( 'IMPLSU' )
) dis_name
WHERE
1 = 1
AND gjl.currency_code != 'STAT'
AND gl.ledger_id = gjh.ledger_id
AND gjh.je_category = gjc.je_category_name
AND gjb.average_journal_flag = 'N'
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = gl.chart_of_accounts_id
AND gjh.je_source = 'Assets'
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xdl.source_distribution_type = 'DEPRN'
AND fas.asset_id (+) = xdl.source_distribution_id_num_1
AND fas.period_counter (+) = xdl.source_distribution_id_num_2
AND fas.book_type_code (+) = xdl.source_distribution_id_char_4
AND fas.asset_id = fab.asset_id (+)
AND gjh.period_name = gp.period_name
AND gp.adjustment_period_flag = 'N'
AND fdh.asset_id = fab.asset_id
AND SYSDATE BETWEEN fdh.date_effective AND nvl(fdh.date_ineffective,SYSDATE)
AND dis_name.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = dis_name.je_header_id
AND gl.ledger_id = gps_from.ledger_id
AND gl.ledger_id = gps_to.ledger_id
AND gp.ledger_id =:p_ledger_name
AND gps_from.ledger_id =:p_ledger_name
AND gps_to.ledger_id =:p_ledger_name
AND gp.application_id = 101
AND gps_from.application_id = 101
AND gps_to.application_id = 101
AND gp.start_date >= gps_from.start_date
AND gp.end_date <= gps_to.end_date
AND gps_from.period_name =:p_period_from
AND gps_to.period_name =:p_period_to
AND gjh.ledger_id =:p_ledger_name
-- --AND gjh.period_name=:P_Period
AND gcc.segment1 BETWEEN :P_FROM_PROPERTY AND :P_TO_PROPERTY
AND gcc.segment2 BETWEEN :P_FROM_REGION AND :P_TO_REGION
AND gcc.segment3 BETWEEN :P_FROM_ACCOUNT AND :P_TO_ACCOUNT
AND gcc.segment4 BETWEEN :P_FROM_CC AND :P_TO_CC
AND gcc.segment5 BETWEEN :P_FROM_INTER AND :P_TO_INTER
AND gcc.segment6 BETWEEN :P_FROM_PROJECT AND :P_TO_PROJECT
UNION ALL
SELECT
gjh.period_name period_name,
gjh.created_by created_by,
dis_name.display_name posted_by,
(
SELECT
gjs.je_source_key
FROM
gl_je_sources gjs
WHERE
gjh.je_source = gjs.je_source_name
) source,
(
SELECT
gjc.je_category_key
FROM
gl_je_categories gjc
WHERE
gjh.je_category = gjc.je_category_name
) category,
gjh.posted_date posting_date,
xal.accounting_date accounting_date,
gjb.name batch_name,
gjh.name journal_name,
gjl.je_line_num journal_line_num,
replace(gjh.description,CHR(13),' ') journal_desc,
gjl.description journal_line_desc,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7
code_comb,
gcc.segment1 Property
,gcc.segment2 Region
,gcc.segment3 Account
,gcc.segment4 Cost_Center
,gcc.segment5 Inter_Company
,gcc.segment6 Project
,gcc.segment7 Future1,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1) Property_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,2,gcc.segment2) Region_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) Account_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,4,gcc.segment4) Cost_Center_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,5,gcc.segment5) IC_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,6,gcc.segment6) Project_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,8,gcc.segment7) future1_description,
xal.unrounded_accounted_dr accounted_dr,
xal.unrounded_accounted_cr accounted_cr,
xal.unrounded_entered_dr entered_dr,
xal.unrounded_entered_cr entered_cr,
nvl(gjh.currency_code,gjl.currency_code) currency_code,
gjl.currency_conversion_rate curr_conve_rate,
gjl.currency_conversion_type curr_conve_type,
gjl.currency_conversion_date corr_conve_date,
NULL vendor_customer_name,
fth.transaction_date_entered trx_date,
fab.asset_number trx_number,
xet.name trx_type,
NULL check_receipt_number,
NULL po_number,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance_acc,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance_acc,
gjl.attribute2 employee_name,
gjl.attribute6 expense_report_name,
gjl.attribute4 expense_type,
gjl.attribute8 concur_vendor,
'ASSETS' custom_trx_type
FROM
gl_ledgers gl,
gl_je_categories gjc,
gl_je_headers gjh,
gl_je_batches gjb,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
-- xla_distribution_links xdl,
xla_ae_headers xah,
xla_events xe,
xla_event_types_tl xet,
fa_transaction_headers fth,
fa_additions_b fab,
fa_distribution_history fdh,
gl_period_statuses gp,
gl_period_statuses gps_from,
gl_period_statuses gps_to,
(
SELECT
gja1.user_id display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id <> ( 'IMPLSU' )
UNION
SELECT
'IMPLSU' display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id = ( 'IMPLSU' )
) dis_name
WHERE
1 = 1
AND gjl.currency_code != 'STAT'
AND gl.ledger_id = gjh.ledger_id
AND gjh.je_category = gjc.je_category_name
AND gjb.average_journal_flag = 'N'
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = gl.chart_of_accounts_id
AND gjh.je_source = 'Assets'
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
-- AND xal.ae_header_id = xdl.ae_header_id
-- AND xal.ae_line_num = xdl.ae_line_num
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
-- AND xdl.source_distribution_type = 'TRX'
-- AND fth.transaction_header_id = xdl.source_distribution_id_num_1
AND xah.event_id = fth.event_id --Added by Rama to remove duplicate lines for retirement assets
AND fth.asset_id = fab.asset_id
AND gjh.period_name = gp.period_name
AND gp.adjustment_period_flag = 'N'
AND dis_name.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = dis_name.je_header_id
AND fdh.asset_id = fab.asset_id
AND SYSDATE BETWEEN fdh.date_effective AND nvl(fdh.date_ineffective,SYSDATE)
AND gl.ledger_id = gps_from.ledger_id
AND gl.ledger_id = gps_to.ledger_id
AND gp.ledger_id =:p_ledger_name
AND gps_from.ledger_id =:p_ledger_name
AND gps_to.ledger_id =:p_ledger_name
AND gp.application_id = 101
AND gps_from.application_id = 101
AND gps_to.application_id = 101
AND gp.start_date >= gps_from.start_date
AND gp.end_date <= gps_to.end_date
AND gps_from.period_name =:p_period_from
AND gps_to.period_name =:p_period_to
AND gjh.ledger_id =:p_ledger_name
--AND gjh.period_name=:P_Period
AND gcc.segment1 BETWEEN :P_FROM_PROPERTY AND :P_TO_PROPERTY
AND gcc.segment2 BETWEEN :P_FROM_REGION AND :P_TO_REGION
AND gcc.segment3 BETWEEN :P_FROM_ACCOUNT AND :P_TO_ACCOUNT
AND gcc.segment4 BETWEEN :P_FROM_CC AND :P_TO_CC
AND gcc.segment5 BETWEEN :P_FROM_INTER AND :P_TO_INTER
AND gcc.segment6 BETWEEN :P_FROM_PROJECT AND :P_TO_PROJECT
UNION ALL
SELECT
gjh.period_name period_name,
gjh.created_by created_by,
dis_name.display_name posted_by,
(
SELECT
gjs.je_source_key
FROM
gl_je_sources gjs
WHERE
gjh.je_source = gjs.je_source_name
) source,
(
SELECT
gjc.je_category_key
FROM
gl_je_categories gjc
WHERE
gjh.je_category = gjc.je_category_name
) category,
gjh.posted_date posting_date,
xal.accounting_date accounting_date,
gjb.name batch_name,
gjh.name journal_name,
gjl.je_line_num journal_line_num,
replace(gjh.description,CHR(13),' ') journal_desc,
gjl.description journal_line_desc,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7
code_comb,
gcc.segment1 Property
,gcc.segment2 Region
,gcc.segment3 Account
,gcc.segment4 Cost_Center
,gcc.segment5 Inter_Company
,gcc.segment6 Project
,gcc.segment7 Future1,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1) Property_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,2,gcc.segment2) Region_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) Account_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,4,gcc.segment4) Cost_Center_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,5,gcc.segment5) IC_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,6,gcc.segment6) Project_description,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,8,gcc.segment7) future1_description,
xal.unrounded_accounted_dr accounted_dr,
xal.unrounded_accounted_cr accounted_cr,
xal.unrounded_entered_dr entered_dr,
xal.unrounded_entered_cr entered_cr,
nvl(gjh.currency_code,gjl.currency_code) currency_code,
gjl.currency_conversion_rate curr_conve_rate,
gjl.currency_conversion_type curr_conve_type,
gjl.currency_conversion_date corr_conve_date,
NULL vendor_customer_name,
cet.transaction_date trx_date,
TO_CHAR(cet.transaction_id) trx_number,
xet.name trx_type,
NULL check_receipt_number,
NULL po_number,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance,
(
SELECT
gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr
FROM
gl_balances gb
WHERE
gb.code_combination_id = gcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) beginning_balance_acc,
(
SELECT
SUM(gb.begin_balance_dr - gb.begin_balance_cr + gb.period_net_dr - gb.period_net_cr)
FROM
gl_balances gb
WHERE
gb.code_combination_id IN (
SELECT DISTINCT
gcc1.code_combination_id
FROM
gl_code_combinations gcc1
WHERE
gcc1.segment2 = gcc.segment2
)
AND gb.period_name = gjh.period_name
AND gb.currency_code = 'USD'
) ending_balance_acc,
gjl.attribute2 employee_name,
gjl.attribute6 expense_report_name,
gjl.attribute4 expense_type,
gjl.attribute8 concur_vendor,
'CASH MANAGEMENT' custom_trx_type
FROM
gl_ledgers gl,
gl_je_categories gjc,
gl_je_headers gjh,
gl_je_batches gjb,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_events xe,
xla_event_types_tl xet,
ce_external_transactions cet,
fnd_lookup_values flv,
gl_period_statuses gp,
gl_period_statuses gps_from,
gl_period_statuses gps_to,
(
SELECT
gja1.user_id display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id <> ( 'IMPLSU' )
UNION
SELECT
'IMPLSU' display_name,
gjb1.je_batch_id,
gjh1.je_header_id
FROM
gl_ledgers gl1,
gl_je_batches gjb1,
gl_je_headers gjh1,
gl_je_lines gjl1,
gl_je_action_log gja1
WHERE
gjb1.je_batch_id = gja1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_batch_id = gjb1.je_batch_id
AND gjh1.je_header_id = gjl1.je_header_id
AND gl1.ledger_id = gjh1.ledger_id
AND gja1.action_code = 'POSTED'
AND gja1.user_id = ( 'IMPLSU' )
) dis_name
WHERE
1 = 1
AND gjl.currency_code != 'STAT'
AND gl.ledger_id = gjh.ledger_id
AND gjh.je_category = gjc.je_category_name
AND gjb.average_journal_flag = 'N'
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id = gl.chart_of_accounts_id
AND gjh.je_source = 'Cash Management'
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND xal.ae_header_id = xdl.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
AND xal.ae_header_id = xah.ae_header_id
AND xah.event_id = xe.event_id
AND xe.event_type_code = xet.event_type_code
AND xdl.source_distribution_type = 'CE_TRANS'
AND xdl.source_distribution_id_num_1 = cet.transaction_id
AND cet.transaction_type = flv.lookup_code (+)
AND flv.lookup_type (+) = 'CE_TRX_TYPE'
AND flv.language (+) = 'US'
AND gjh.period_name = gp.period_name
AND gp.adjustment_period_flag = 'N'
AND dis_name.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = dis_name.je_header_id
--AND gjh.period_name=:P_Period
AND gcc.segment1 BETWEEN :P_FROM_PROPERTY AND :P_TO_PROPERTY
AND gcc.segment2 BETWEEN :P_FROM_REGION AND :P_TO_REGION
AND gcc.segment3 BETWEEN :P_FROM_ACCOUNT AND :P_TO_ACCOUNT
AND gcc.segment4 BETWEEN :P_FROM_CC AND :P_TO_CC
AND gcc.segment5 BETWEEN :P_FROM_INTER AND :P_TO_INTER
AND gcc.segment6 BETWEEN :P_FROM_PROJECT AND :P_TO_PROJECT
AND gl.ledger_id = gps_from.ledger_id
AND gl.ledger_id = gps_to.ledger_id
AND gp.ledger_id =:p_ledger_name
AND gps_from.ledger_id =:p_ledger_name
AND gps_to.ledger_id =:p_ledger_name
AND gp.application_id = 101
AND gps_from.application_id = 101
AND gps_to.application_id = 101
AND gp.start_date >= gps_from.start_date
AND gp.end_date <= gps_to.end_date
AND gps_from.period_name =:p_period_from
AND gps_to.period_name =:p_period_to
AND gjh.ledger_id =:p_ledger_name
)
Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.
PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.
Following is the syntax structure for the PL/SQL code blocks
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
DECLARE
message varchar2(100):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
CREATE OR REPLACE FUNCTION
hello_user
(user_name IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN 'Hello ' || user_name;
END hello_user;
/
BEGIN
dbms_output.put_line(hello_user('Peter'));
END;
/
BEGIN
DBMS_OUTPUT.put_line (1/0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;