Introduction
This Post illustrates the steps required to get the General ledger analysis for PA Expenditure & Event in oracle Apps
Script for GL Account analysis for PA Expenditure & Event
SELECT ‘Expenditure’ revenue_type,
hca.cust_account_id customer_id,
hp.party_name customer_name,
v225332622.project_id project_id,
v225332622.project_number project_number,
okha.contract_number contract_number,
v225332622.project_name project_name,
haou.NAME project_organization,
hca.customer_class_code industry,
hp.state state, hp.city city,
hp.country country, gcc.segment1 company,
gcc.segment2 ACCOUNT,
(SELECT fv.description
FROM fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
WHERE fv.flex_value_set_id =
fvs.flex_value_set_id
AND fvs.flex_value_set_name = ‘Account’
AND fv.flex_value = gcc.segment2)
account_name,
gcc.segment3 department,
(SELECT fv.description
FROM fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
WHERE fv.flex_value_set_id =
fvs.flex_value_set_id
AND fvs.flex_value_set_name = ‘Department’
AND fv.flex_value = gcc.segment3)department_name,
gcc.segment4 region, gcc.segment5 FUNCTION,
SUM
(project_curr_revenue_amt
) project_curr_revenue_amt,
trns_currency_code bill_transaction_currency,
SUM
(trns_curr_revenue_amt
) bill_transaction_currency_rev,
contract_currency_code,
SUM
(NVL (cont_curr_revenue_amt, 0)
) cont_curr_revenue_amt_sum,
revenue_currency_code, ledger_currency_code,
SUM
(ledger_curr_revenue_amt
) ledger_curr_revenue_amt,
SUM
(v144634978.revenue_curr_amt
) revenue_curr_amt,
SUM
(DECODE
(‘USD’,
v144634978.ledger_currency_code, NVL
(v144634978.ledger_curr_revenue_amt,
0
),
( NVL
(v144634978.ledger_curr_revenue_amt,
0
)
* (SELECT DISTINCT r.avg_rate
FROM gl_translation_rates r,
gl_sets_of_books s
WHERE s.set_of_books_id =
r.set_of_books_id
AND r.to_currency_code =
‘USD’
AND s.currency_code =
v144634978.ledger_currency_code
AND UPPER (r.period_name) =
UPPER
(v144634978.gl_period
)
AND ROWNUM = 1)
)
)
) usd_revenue_amount,
v225332622.project_currency_code
project_currency_code,
SUM
(v144634978.project_curr_revenue_amt
) project_revenue_amount,
NVL
(pei.projfunc_currency_code,
v144634978.ledger_currency_code
) projfunc_currency_code,
SUM
(NVL (v144634978.ledger_curr_revenue_amt, 0)
) projfunc_revenue_amount,
v144634978.gl_period gl_period,
gsb.NAME gl_set_of_books_name,
NULL gl_line_dff_prj_num, NULL global_region,
(SELECT class_code
FROM pjf_project_classes prc,
pjf_class_codes_tl pct
WHERE prc.class_code_id = pct.class_code_id
AND prc.project_id = v225332622.project_id)region_code,
(SELECT NAME
FROM hr_operating_units
WHERE organization_id =
v225332622.org_id)business_unit,
(SELECT b.document_name
FROM pjf_txn_sources_tl a,
pjf_txn_document_tl b
WHERE a.transaction_source_id =
pei.transaction_source_id
AND b.document_id = pei.document_id) SOURCE,
(SELECT ho1.NAME
FROM pjf_projects_all_b pa,
hr_all_organization_units ho1
WHERE ho1.organization_id =
carrying_out_organization_id
AND pa.project_id = v225332622.project_id)project_organization1,
v225332622.project_name source_details,
pei.expenditure_item_id AS expenditure_item_id,
v144634978.transaction_project_id AS transaction_project_id,
gl.NAME ledger_name, xda.code_combination_id,
(SELECT hou1.NAME
FROM hr_all_organization_units hou1
WHERE okha.owning_org_id =hou1.organization_id)contract_org,
(SELECT DISTINCT r.avg_rate
FROM gl_translation_rates r,
gl_sets_of_books s
WHERE s.set_of_books_id =
r.set_of_books_id
AND r.to_currency_code = ‘USD’
AND s.currency_code =
v144634978.ledger_currency_code
AND UPPER (r.period_name) =
UPPER (v144634978.gl_period)
AND ROWNUM = 1) period_avg_rate,
(SELECT NAME
FROM per_jobs_f_vl pjb
WHERE pjb.job_id = pei.person_job_id
AND SYSDATE
BETWEEN NVL
(pjb.effective_start_date,
SYSDATE – 1
)
AND NVL (pjb.effective_end_date,
SYSDATE + 1
)
AND active_status = ‘A’) job_name
FROM
(sELECT revenuedistributionpeo.bill_transaction_type_code,
revenuedistributionpeo.rev_distribution_id,
revenuedistributionpeo.transaction_id,
revenuedistributionpeo.transaction_project_id,
revenuedistributionpeo.revenue_curr_amt,
revenuedistributionpeo.trns_curr_revenue_amt,
gl_period_name gl_period, contract_id,
major_version, project_curr_revenue_amt,
gl_date, reversed_flag,
line_num_reversed,
revenue_currency_code,
ledger_currency_code,
ledger_curr_revenue_amt,
contract_currency_code,
cont_curr_revenue_amt,
project_currency_code,
trns_currency_code,
(CASE
WHEN bill_transaction_type_code =
‘EI’
THEN transaction_id
END
) AS expenditure_item_id
FROM pjb_rev_distributions revenuedistributionpeo
WHERE 1 = 1) v144634978,
(SELECT projectbasepeo.project_id,
projectbasepeo.segment1
AS project_number,
ppt.NAME project_name,
projectbasepeo.org_id,
project_currency_code,
projectbasepeo.attribute10 region_code,
projectbasepeo.pm_product_code
pm_product_code
FROM pjf_projects_all_b projectbasepeo,
pjf_projects_all_tl ppt
WHERE projectbasepeo.project_id =
ppt.project_id) v225332622,
pjc_exp_items_all pei,
okc_k_headers_all_b okha,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units haou,
fun_names_business_units_v fnbu,
gl_ledgers gl,
gl_sets_of_books gsb,
gl_code_combinations gcc,
(SELECT xda2.event_id,
xda2.source_distribution_id_num_1,
xda2.rounding_class_code,
xda2.source_distribution_id_num_2,
xda2.ae_line_num, xda2.ae_header_id,
ref_ae_header_id, ref_ae_line_num,
SIGN
(NVL ( (-1)
* (xda2.unrounded_entered_dr),
xda2.unrounded_entered_cr
)
) xla_amt,
xal2.code_combination_id
FROM xla_distribution_links xda2,
xla_ae_lines xal2
WHERE 1 = 1
AND xda2.source_distribution_type =’Revenue – Expenditure Revenue’
AND xal2.ae_header_id = xda2.ae_header_id
AND xal2.ae_line_num = xda2.ae_line_num
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links xda1,
xla_ae_lines xla1
WHERE xda1.ref_temp_line_num =
xda2.temp_line_num
AND xda1.ref_ae_header_id =
xda2.ae_header_id
AND xda1.ae_line_num =
xla1.ae_line_num
AND xda1.ae_header_id =
xla1.ae_header_id
AND xla1.override_reason IS NOT NULL)) xda
WHERE v144634978.transaction_project_id =
v225332622.project_id
AND v225332622.project_id = pei.project_id
AND v144634978.expenditure_item_id =
pei.expenditure_item_id
AND v144634978.contract_id = okha.ID
AND v144634978.major_version = okha.major_version
AND okha.bill_to_acct_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND haou.organization_id = v225332622.org_id
AND fnbu.bu_id = v225332622.org_id
AND fnbu.primary_ledger_id = gl.ledger_id
AND gl.ledger_id = gsb.set_of_books_id
AND v144634978.rev_distribution_id =
xda.source_distribution_id_num_1
AND SIGN (xda.xla_amt) =SIGN (v144634978.revenue_curr_amt)
AND xda.code_combination_id =gcc.code_combination_id
AND v144634978.bill_transaction_type_code = ‘EI’
AND UPPER (v144634978.gl_period) IN (:p_gl_period)
GROUP BY hca.cust_account_id,
hp.party_name,
v225332622.project_id,
v225332622.project_number,
v225332622.project_name,
haou.NAME,
hca.customer_class_code,
hp.state,
hp.city,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
v144634978.gl_period,
gl.ledger_id,
v225332622.project_currency_code,
NVL (pei.projfunc_currency_code,
v144634978.ledger_currency_code
),
v144634978.gl_date,
gl.period_set_name,
v144634978.gl_period,
gsb.NAME,
region_code,
v225332622.org_id,
pei.expenditure_item_id,
v144634978.transaction_project_id,
xda.code_combination_id,
v225332622.pm_product_code,
pei.transaction_source_id,
pei.document_id,
hp.country,
okha.contract_number,
okha.owning_org_id,
gl.NAME,
v144634978.trns_currency_code,
v144634978.contract_currency_code,
v144634978.revenue_currency_code,
v144634978.ledger_currency_code,
pei.person_job_id
/*2. GL Account analysis for PA Events */
SELECT ‘Event’ revenue_type,
hca.cust_account_id customer_id,
hp.party_name customer_name,
ppa.project_id project_id,
ppa.segment1 project_number,
okh.contract_number contract_number,
ppa.NAME project_name,
hou.NAME project_organization,
hca.customer_class_code industry,
hp.state state, hp.city city,
hp.country country, gcc.segment1 company,
gcc.segment2 ACCOUNT,
(SELECT fv.description
FROM fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
WHERE fv.flex_value_set_id =
fvs.flex_value_set_id
AND fvs.flex_value_set_name = ‘Account’
AND fv.flex_value = gcc.segment2)
account_name,
gcc.segment3 department,
(SELECT fv.description
FROM fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
WHERE fv.flex_value_set_id =
fvs.flex_value_set_id
AND fvs.flex_value_set_name =’Department’
AND fv.flex_value = gcc.segment3)
department_name,
gcc.segment4 region, gcc.segment5 FUNCTION,
SUM
(prd.project_curr_revenue_amt
) project_curr_revenue_amt,
pe.bill_trns_currency_code
bill_transaction_currency,
SUM
(NVL (prd.trns_curr_revenue_amt, 0)
) bill_transaction_currency_rev,
pe.contract_curr_code contract_currency_code,
SUM
(NVL (prd.cont_curr_revenue_amt, 0)
) cont_curr_revenue_amt_sum,
pe.revenue_currency_code,
pe.ledger_currency_code,
SUM
(NVL (prd.ledger_curr_revenue_amt, 0)
) ledger_curr_revenue_amt,
SUM
(NVL (prd.revenue_curr_amt, 0)
) revenue_curr_amt,
SUM
(DECODE
(‘USD’,
pe.ledger_currency_code, prd.ledger_curr_revenue_amt,
prd.ledger_curr_revenue_amt
* NVL
((SELECT DISTINCT r.avg_rate
FROM gl_translation_rates r,
gl_sets_of_books s
WHERE s.set_of_books_id =
r.set_of_books_id
AND r.to_currency_code =
‘USD’
AND s.currency_code =
pe.ledger_currency_code
AND UPPER
(r.period_name) =
UPPER
(prd.gl_period_name
)),
1
)
)
) usd_revenue_amount,
pe.project_currency_code project_currency_code,
SUM(prd.project_curr_revenue_amt) project_revenue_amount,
ppa.projfunc_currency_code
projfunc_currency_code,
SUM(ledger_curr_revenue_amt) projfunc_revenue_amount,
prd.gl_period_name gl_period,
gsb.NAME gl_set_of_books_name,
NULL gl_line_dff_prj_num, NULL global_region,
(SELECT class_code
FROM pjf_project_classes prc,
pjf_class_codes_tl pct
WHERE prc.class_code_id = pct.class_code_id
AND prc.project_id = ppa.project_id)
region_code,
(SELECT NAME
FROM hr_operating_units
WHERE organization_id =
ppa.org_id)
business_unit,
‘PA’ SOURCE,
(SELECT ho1.NAME
FROM pjf_projects_all_b pa,
hr_all_organization_units ho1
WHERE ho1.organization_id =
carrying_out_organization_id
AND pa.project_id = ppa.project_id)
project_organization1,
ppa.segment1 source_details,
pe.event_id expenditure_item_id,
ppa.project_id transaction_project_id,
gl.NAME ledger_name,
xda.code_combination_id,
(SELECT hou1.NAME
FROM hr_all_organization_units hou1
WHERE okh.owning_org_id = hou1.organization_id)
contract_org,
(SELECT DISTINCT r.avg_rate
FROM gl_translation_rates r,
gl_sets_of_books s
WHERE s.set_of_books_id =
r.set_of_books_id
AND r.to_currency_code = ‘USD’
AND s.currency_code =
pe.ledger_currency_code
AND UPPER (r.period_name) =
UPPER (prd.gl_period_name))
period_avg_rate
FROM pjf_projects_all_vl ppa,
pjf_project_types_vl ppt,
pjb_rev_distributions prd,
pjb_billing_events pe,
okc_k_headers_all_b okh,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units hou,
fun_names_business_units_v fnbu,
(SELECT xda2.event_id,
xda2.source_distribution_id_num_1,
xda2.rounding_class_code,
xda2.source_distribution_id_num_2,
xda2.ae_line_num, xda2.ae_header_id,
ref_ae_header_id, ref_ae_line_num,
SIGN
(NVL ( (-1)
* (xda2.unrounded_entered_dr),
xda2.unrounded_entered_cr
)
) xla_amt,
xal2.code_combination_id
FROM xla_distribution_links xda2,
xla_ae_lines xal2
WHERE 1 = 1
AND xda2.source_distribution_type =
‘Revenue – Event Revenue’
AND xal2.ae_header_id = xda2.ae_header_id
AND xal2.ae_line_num = xda2.ae_line_num
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links xda1,
xla_ae_lines xla1
WHERE xda1.ref_temp_line_num =
xda2.temp_line_num
AND xda1.ref_ae_header_id =
xda2.ae_header_id
AND xda1.ae_line_num =
xla1.ae_line_num
AND xda1.ae_header_id =
xla1.ae_header_id
AND xla1.override_reason IS NOT NULL)) xda,
gl_code_combinations gcc,
gl_ledgers gl,
gl_sets_of_books gsb,
(SELECT pcl.project_id, pcl.contract_id
FROM pjb_cntrct_proj_links pcl
WHERE pcl.version_type = ‘C’
GROUP BY pcl.project_id, pcl.contract_id) cont_link
WHERE 1 = 1
AND ppa.project_type_id = ppt.project_type_id
AND bill_transaction_type_code = ‘EVT’
AND cont_link.project_id = ppa.project_id
AND prd.transaction_id = pe.event_id
AND pe.contract_id = cont_link.contract_id
AND okh.ID = pe.contract_id
AND okh.version_type = ‘C’
AND okh.bill_to_acct_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND hou.organization_id =ppa.org_id
AND fnbu.bu_id = ppa.org_id
AND fnbu.primary_ledger_id = gl.ledger_id
AND gl.ledger_id = gsb.set_of_books_id
AND xda.source_distribution_id_num_1 =prd.rev_distribution_id
AND SIGN (xda.xla_amt) = SIGN (prd.revenue_curr_amt)
AND gcc.code_combination_id =xda.code_combination_id
AND UPPER (prd.gl_period_name) IN (:p_gl_period)
GROUP BY hca.cust_account_id,
hp.party_name,
ppa.project_id,
ppa.segment1,
okh.contract_number,
okh.owning_org_id,
ppa.NAME,
hou.NAME,
hca.customer_class_code,
hp.state,
hp.city,
hp.country,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
pe.bill_trns_currency_code,
pe.contract_curr_code,
pe.revenue_currency_code,
pe.ledger_currency_code,
pe.project_currency_code,
ppa.projfunc_currency_code,
projfunc_currency_code,
prd.gl_date,
gl.period_set_name,
prd.gl_period_name,
gsb.NAME,
ppa.org_id,
pe.event_id,
ppa.project_id,
gl.NAME,
xda.code_combination_id
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
