/* 1. GL Account analysis for PA Expenditure */
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
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
nice artical
ReplyDeletePower BI Training In Hyderabad
Power BI Training
Power BI Online Training
Power BI Training Online
Nice Blog We are providing technical support in Quickbooks Payroll Support Phone Number 1-800-986-4607.If you are facing any issue in Quickbooks dial our toll free number 1-800-986-4607.
ReplyDeleteThanks for providing this blog for us. Actually We are an Accounting solution company. Now a days maximum user working on the Accounting software and the availability of Quickbooks Support Phone Number can be easily solved. You have to dial 800-901-6679 for the urgent solution.
ReplyDeletehttps://tinyurl.com/y5e6s475
Facing any trouble while using Quickbooks? Contact Quickbooks Support Phone Number. As they provide immediate & effective solutions of the issues, you preoccupied with. Feel free to contact them, as they are available for you, round the clock. It doesn’t matter at what time, you come across the issue. Get in touch with them, by just dialing Quickbooks Support Phone Number 800-901-6679. Just Ask your queries & gain solutions.
ReplyDelete
ReplyDeletehttps://nexaccounting.weebly.com/
https://www.fyple.com/company/quickbooks-support-phone-number-ys96gwc/
https://helpingdotnet.blogspot.com/feeds/3454155590478389003/comments/default
https://www.postallads4free.com/financial_services-ad691861.html
We are provides a Quickbooks Support Phone Number Washington. Our support team constitutes of highly skilled & trained technicians who have years of experience in handling technical defects. It doesn’t matter how complex the issues would be. Get it resolved, from our Support team. As they are available for you, 24*7. Whenever you face any trouble, feel free to contact Quickbooks helpline 800-901-6679.
ReplyDeleteNice Blog, I saw Somany unknown topics in this Blog. Thanks For sharing,Keep it up.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Nice Blog ! Is there any Quickbooks issues creating hindrances in smooth functioning of your business? Do you need some help? If yes, Ring us at our Quickbooks Support Phone Number +1 (800)-986-4607.
ReplyDeleteFix QuicKBooks issues instantly by dialing our QuickBooks Update support Phone Number +1(855)-9O7-O4O6 .Here we have technical experts to deal with the issues effectively.
ReplyDeleteDial Quickbooks Support Phone Number 855-907-0406 that is available as toll-free. We will help to enjoy happy accounting.
ReplyDeleteView on Map: QuickBooks Customer Service
Thank you for sharing such a nice and interesting blog.
ReplyDeleteOracle Fusion HCM Online Training
our QuickBooks Customer Service Number 1-833-325-0220 and get your queries settled concurrently from our QB technicians that are always there to support you by 24*7. For More: https://tinyurl.com/ybzzmvhr
ReplyDelete