Purpose of this report is to derive all projects expenditure and event details with cost information.
SELECT revenue_type, TO_CHAR (customer_id) customer_id, customer_name,
TO_CHAR (project_id) project_id, project_number, contract_number,
project_name, project_organization, industry, state, city, country,
company, ACCOUNT, –account_type,
account_name, department, department_name, region,
FUNCTION,
ROUND (SUM (project_curr_revenue_amt), 2) project_curr_revenue_amt,
bill_transaction_currency,
ROUND
(SUM (bill_transaction_currency_rev),
2
) bill_transaction_currency_rev,
contract_currency_code,
ROUND (SUM (cont_curr_revenue_amt_sum), 2) cont_curr_revenue_amt_sum,
revenue_currency_code, ledger_currency_code,
ROUND (SUM (ledger_curr_revenue_amt), 2) ledger_curr_revenue_amt,
ROUND (SUM (revenue_curr_amt), 2) revenue_curr_amt,
ROUND (SUM (usd_revenue_amount), 2) usd_revenue_amount,
project_currency_code,
ROUND (SUM (project_revenue_amount), 2) project_revenue_amount,
projfunc_currency_code,
ROUND (SUM (projfunc_revenue_amount), 2) projfunc_revenue_amount,
planning_week, gl_period, gl_set_of_books_name, gl_line_dff_prj_num,
–system_person_type,
DECODE (project_organization,
‘THG US’, ‘NA’,
‘THG Germany’, ‘EU’,
‘THG Netherlands’, ‘EU’,
‘THG Switzerland’, ‘EU’,
‘THG UK’, ‘EU’,
‘THG France’, ‘EU’,
‘THG Italy’, ‘EU’,
‘THG Singapore’, ‘EU’,
‘THG Spain’, ‘EU’,
‘THG Australia’, ‘APAC’,
‘THG India’, ‘IN’,
‘THG Hungary’, ‘EU’,
‘THG Uruguay’, ‘NA’,
‘THG Canada’, ‘NA’,
‘Missing Org Id’
) global_region,
region_code, SOURCE, business_unit, ledger_name,
DECODE
(project_organization,
‘SAP AMS’, ‘SAP Application Managed Services’,
‘SAP Finance’, ‘SAP Application Services’,
‘SAP DS’, ‘SAP Application Services’,
‘SAP Business Intelligence’, ‘SAP Application Services’,
‘SAP Technology’, ‘SAP Application Services’,
‘SAP Supply Chain’, ‘SAP Application Services’,
‘SAP Management’, ‘SAP Application Services’,
‘SAP Reseller’, ‘SAP Reseller Software Services’,
‘EZ Commerce-India’, ‘EZ Commerce India-Bridge’,
‘EZ Commerce-India-DS’, ‘EZ Commerce India-Bridge’,
‘Business Application Support’, ‘Business Application Support Services’,
‘Hackett Institute’, ‘Hackett Institute Services’,
‘Hackett Institute DS’, ‘Hackett Institute Services’,
‘Transformation Workforce’, ‘Workforce Applications Services’,
‘Hackett DS’, ‘Hackett Admin’,
‘Hackett Performance Exchange’, ‘BM-HPE’,
‘Hackett Performance Exchange DS’, ‘BM-HPE’,
‘Technolab AMS’, ‘Technolab AMS Services’,
‘Technolab AMS DS’, ‘Technolab AMS Services’,
‘Strategic Technical Account Managers’, ‘Technolab AMS Services’,
‘REL’, ‘REL Services’,
‘REL DS’, ‘REL Services’,
‘Aecus’, ‘Aecus Limited Services’,
‘Aecus DS’, ‘Aecus Limited Services’,
‘Risk Management’, ‘Risk Management Services’,
‘Finance’, ‘Functional Services’,
‘Human Resources’, ‘Functional Services’,
‘Information Technology’, ‘Functional Services’,
‘Executive’, ‘Functional Services’,
‘Office Administration’, ‘Functional Services’,
‘Legal’, ‘Functional Services’,
‘Product Development’, ‘Functional Services’,
‘Corporate’, ‘Functional Services’,
‘REL Elimination’, ‘Functional Services’,
‘Transformation Finance’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘THG Switzerland’, ‘EU’,
‘THG Germany’, ‘EU’,
‘THG UK’, ‘EU’,
‘THG Netherlands’, ‘EU’,
‘THG France’, ‘EU’,
‘THG Spain’, ‘EU’,
‘Missing Org Id’
),
‘NA’, ‘Strategy & Business Transformation’,
‘EU’, ‘Transformation Europe’,
‘Transformation Services’
),
‘Transformation HR’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘THG Switzerland’, ‘EU’,
‘THG Germany’, ‘EU’,
‘THG UK’, ‘EU’,
‘THG Netherlands’, ‘EU’,
‘THG France’, ‘EU’,
‘THG Spain’, ‘EU’,
‘Missing Org Id’
),
‘NA’, ‘Strategy & Business Transformation’,
‘EU’, ‘Transformation Europe’,
‘Transformation Services’
),
‘Transformation IT’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘THG Switzerland’, ‘EU’,
‘THG Germany’, ‘EU’,
‘THG UK’, ‘EU’,
‘THG Netherlands’, ‘EU’,
‘THG France’, ‘EU’,
‘THG Spain’, ‘EU’,
‘Missing Org Id’
),
‘NA’, ‘Strategy & Business Transformation’,
‘EU’, ‘Transformation Europe’,
‘Transformation Services’
),
‘Transformation DS’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘THG Switzerland’, ‘EU’,
‘THG Germany’, ‘EU’,
‘THG UK’, ‘EU’,
‘THG Netherlands’, ‘EU’,
‘THG France’, ‘EU’,
‘THG Spain’, ‘EU’,
‘Missing Org Id’
),
‘NA’, ‘Strategy & Business Transformation’,
‘EU’, ‘Transformation Europe’,
‘Transformation Services’
),
‘ADP’, ‘ADP Services’,
‘Benchmark’, ‘BM-HPE’, — Replacing’Benchmark Services’,
‘Benchmark DS’, ‘BM-HPE’, — Replacing ‘Benchmark Services’,
‘Advisory’, ‘Advisory Services’,
‘Advisory-DS’, ‘Advisory Services’,
‘S&O Operations’, ‘Strategy & Business Transformation’,
‘S&O Procurement’, ‘Strategy & Business Transformation’,
‘S&O IT’, ‘Strategy & Business Transformation’,
‘S&O Enterprise Pool’, ‘Strategy & Business Transformation’,
‘S&O DS’, ‘Strategy & Business Transformation’,
‘EEA Transformation’, DECODE (DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘THG Switzerland’, ‘EU’,
‘THG Germany’, ‘EU’,
‘THG UK’, ‘EU’,
‘THG Netherlands’, ‘EU’,
‘THG France’, ‘EU’,
‘THG Spain’, ‘EU’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EU’, ‘Transformation Europe’,
‘EEA Transformation Services’
),
‘GBS Europe’, ‘Transformation Europe’,
‘EPM Europe’, ‘Transformation Europe’,
‘Procurement Europe’, ‘Transformation Europe’,
‘EEA Jibe ERP’, ‘EEA Solutions’,
‘EEA Jibe HCM’, ‘EEA Solutions’,
‘EEA Jibe EPM Analytics’, ‘EEA Solutions’,
‘EEA Jibe DS’, ‘EEA Solutions’,
‘EEA Jibe Near Shore Applications’, ‘EEA Solutions’,
‘EEA Jibe NA Tech’, ‘EEA Solutions’,
‘EEA Jibe Offshore’, ‘EEA Solutions’,
‘EEA Jibe Financials’, ‘EEA Solutions’,
‘EEA Jibe AMS’, ‘EEA Solutions’,
‘EEA Business Intelligence/Advanced Analytics’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EEA Oracle Services’
),
‘EEA Oracle Consolidation’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EEA Oracle Services’
),
‘EEA Analytic Infrastructure’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EEA Oracle Services’
),
‘EEA Oracle Planning & Analytics’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EEA Oracle Services’
),
‘EEA Strategic’, DECODE (DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EEA Oracle Services’
),
‘EEA DS’, DECODE (DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EEA Oracle Services’
),
‘EEA Near Shore Applications’, DECODE
(DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EEA Oracle Services’
),
‘EEA Oracle Cloud ERP’, DECODE (DECODE (project_organization,
‘THG Canada’, ‘NA’,
‘THG US’, ‘NA’,
‘THG Uruguay’, ‘NA’,
‘Missing Org Id’
),
‘NA’, ‘EEA Solutions’,
‘EEA Oracle Services’
),
‘Not Defined’
) reporting_organization
FROM ((SELECT revenue_type, customer_id, customer_name, project_id,
project_number, contract_number, project_name,
project_organization, industry, state, city, country,
company, ACCOUNT, account_name, department,
department_name, region, FUNCTION,
ROUND
(SUM (project_curr_revenue_amt),
2
) project_curr_revenue_amt,
bill_transaction_currency,
ROUND
(SUM (bill_transaction_currency_rev),
2
) bill_transaction_currency_rev,
contract_currency_code,
ROUND
(SUM (cont_curr_revenue_amt_sum),
2
) cont_curr_revenue_amt_sum,
revenue_currency_code, ledger_currency_code,
ROUND
(SUM (ledger_curr_revenue_amt),
2
) ledger_curr_revenue_amt,
ROUND (SUM (revenue_curr_amt), 2) revenue_curr_amt,
ROUND (SUM (usd_revenue_amount), 2) usd_revenue_amount,
project_currency_code,
ROUND
(SUM (project_revenue_amount),
2
) project_revenue_amount,
projfunc_currency_code,
ROUND
(SUM (projfunc_revenue_amount),
2
) projfunc_revenue_amount,
planning_week, gl_period, gl_set_of_books_name,
gl_line_dff_prj_num, global_region, region_code,
business_unit, SOURCE, ledger_name, code_combination_id
FROM (SELECT ‘Expenditure’ revenue_type,
CAST
(hca.cust_account_id AS CHARACTER (30)
) customer_id,
hp.party_name customer_name,
CAST
(v225332622.project_id AS CHARACTER (30)
) 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,
–gcc.account_type,
(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 THG’
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 THG’
–‘ANSR_GL_ACCOUNT’
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.revenue_currency_code, NVL(v144634978.revenue_curr_amt,0),
(NVL(v144634978.revenue_curr_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.set_of_books_id=gsb.set_of_books_id
AND s.currency_code= v144634978.revenue_currency_code
AND r.period_name =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,
pcdl.projfunc_currency_code
projfunc_currency_code,
pcdl.projfunc_raw_cost projfunc_revenue_amount,
(SELECT ‘Q’
|| quarter_num
|| ‘-WK’
|| LPAD
(TO_CHAR
(DECODE
(period_num,
53, 14,
DECODE (MOD (period_num,
13
),
0, 13,
MOD (period_num,
13
)
)
)
),
2,
‘0’
)
FROM gl_periods gp
WHERE 1 = 1
AND v144634978.gl_date BETWEEN start_date
AND end_date
AND adjustment_period_flag = ‘N’
AND gp.period_set_name = gl.period_set_name)
planning_week,
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 =
v457612106.transaction_source_id
AND b.document_id = v457612106.document_id)
SOURCE,
v457612106.expenditure_item_id
AS expenditure_item_id,
v144634978.transaction_project_id
AS transaction_project_id,
–v144634978.rev_distribution_id AS rev_distribution_id,
/*(Select meaning
From fnd_lookup_values_tl
where lookup_type=’PJF_PERSON_TYPE’
and lookup_code=v457612106.person_type) system_person_type,*/
gl.NAME ledger_name, xal.code_combination_id
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,
(SELECT expenditureitempeo.expenditure_item_id,
expenditureitempeo.document_id,
expenditureitempeo.project_id,
expenditureitempeo.transaction_source_id,
expenditureitempeo.person_type
person_type,
expenditureitempeo.original_dist_id
FROM pjc_exp_items_all expenditureitempeo) v457612106,
pjc_cost_dist_lines_all pcdl,
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,
xla_distribution_links xda,
xla_ae_lines xal
WHERE v144634978.transaction_project_id =
v225332622.project_id
AND v225332622.project_id = v457612106.project_id
AND v144634978.expenditure_item_id = v457612106.expenditure_item_id(+)
AND (((v144634978.transaction_project_id > 0)))
AND pcdl.reversed_flag IS NULL
AND pcdl.line_num_reversed IS NULL
AND v144634978.reversed_flag IS NULL
AND v144634978.line_num_reversed IS NULL
AND pcdl.expenditure_item_id =
v457612106.expenditure_item_id
AND pcdl.transfer_status_code IN (‘N’)
AND v144634978.contract_id = okha.ID
AND v144634978.major_version = okha.major_version
AND okha.bill_to_acct_id = hca.cust_account_id(+)
–AND v144634978.bill_transaction_type_code =’EI’
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 xda.source_distribution_id_num_1 =
v144634978.rev_distribution_id
–AND PCDL.EXPENDITURE_ITEM_ID=XDA.SOURCE_DISTRIBUTION_ID_NUM_1(+)
AND xda.ae_line_num(+) = 2
AND xal.ae_header_id(+) = xda.ae_header_id
AND xal.ae_line_num(+) = xda.ae_line_num
AND gcc.code_combination_id =
–xal.code_combination_id
NVL
(xal.code_combination_id,
(CASE
WHEN pcdl.acct_source_code = ‘AP_INV’
THEN (SELECT apd.dist_code_combination_id
FROM ap_invoice_distributions_all apd
WHERE apd.invoice_distribution_id =
v457612106.original_dist_id)
ELSE NVL (pcdl.raw_cost_dr_ccid,
pcdl.raw_cost_cr_ccid
)
END
)
)
AND xda.source_distribution_type =
‘Revenue – Expenditure Revenue’
AND xda.unrounded_accounted_cr IS NOT NULL
AND xal.ae_header_id = xda.ae_header_id
AND xal.ae_line_num = xda.ae_line_num
–AND gcc.code_combination_id =xal.code_combination_id
AND gcc.segment1 <> ‘AA’
AND revenue_curr_amt IS NOT NULL
/*AND ( gcc.segment2 BETWEEN ‘410000’ AND ‘549999’
AND gcc.segment2 NOT BETWEEN ‘500000’ AND ‘528509’
AND gcc.segment2 NOT BETWEEN ‘528511’ AND ‘529999’
)*/
AND v144634978.bill_transaction_type_code = ‘EI’
–AND v225332622.project_number=’TEST-GFY-DE’
–AND v457612106.expenditure_item_id=207232
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.account_type,
gcc.segment3,
gcc.segment4,
gcc.segment5,
v144634978.trns_curr_revenue_amt,
v144634978.gl_period,
gl.ledger_id,
v225332622.project_currency_code,
v144634978.project_curr_revenue_amt,
pcdl.projfunc_currency_code,
pcdl.projfunc_raw_cost,
v144634978.gl_date,
gl.period_set_name,
v144634978.gl_period,
gsb.NAME,
region_code,
v225332622.org_id,
v144634978.revenue_curr_amt,
v457612106.expenditure_item_id,
v144634978.transaction_project_id,
xal.code_combination_id,
v225332622.pm_product_code,
v457612106.transaction_source_id,
v457612106.document_id,
hp.country,
okha.contract_number,
gl.NAME,
v144634978.trns_currency_code,
v144634978.contract_currency_code,
v144634978.revenue_currency_code,
v144634978.ledger_currency_code)
WHERE transaction_project_id IS NOT NULL
AND expenditure_item_id IS NOT NULL
GROUP BY revenue_type,
customer_id,
customer_name,
project_id,
project_number,
project_name,
project_organization,
industry,
state,
city,
country,
company,
ACCOUNT,
–account_type,
account_name,
department,
department_name,
region,
FUNCTION,
project_currency_code,
projfunc_currency_code,
planning_week,
gl_period,
gl_set_of_books_name,
gl_line_dff_prj_num,
global_region,
region_code,
business_unit,
SOURCE,
code_combination_id,
contract_number,
–system_person_type
ledger_name,
bill_transaction_currency,
contract_currency_code,
revenue_currency_code,
ledger_currency_code,
project_currency_code,
projfunc_currency_code)
UNION ALL
(SELECT revenue_type, customer_id, customer_name, project_id,
project_number, contract_number, project_name,
project_organization, industry, state, city, country,
company, ACCOUNT, account_name, department,
department_name, region, FUNCTION,
ROUND
(SUM (project_curr_revenue_amt),
2
) project_curr_revenue_amt,
bill_transaction_currency,
ROUND
(SUM (bill_transaction_currency_rev),
2
) bill_transaction_currency_rev,
contract_currency_code,
ROUND
(SUM (cont_curr_revenue_amt_sum),
2
) cont_curr_revenue_amt_sum,
revenue_currency_code, ledger_currency_code,
ROUND
(SUM (ledger_curr_revenue_amt),
2
) ledger_curr_revenue_amt,
ROUND (SUM (revenue_curr_amt), 2) revenue_curr_amt,
ROUND (SUM (usd_revenue_amount), 2) usd_revenue_amount,
project_currency_code,
ROUND
(SUM (project_revenue_amount),
2
) project_revenue_amount,
projfunc_currency_code,
ROUND
(SUM (projfunc_revenue_amount),
2
) projfunc_revenue_amount,
planning_week, gl_period, gl_set_of_books_name,
gl_line_dff_prj_num, global_region, region_code,
business_unit, SOURCE, ledger_name, code_combination_id
FROM (SELECT ‘Event’ revenue_type,
CAST
(hca.cust_account_id AS CHARACTER (30)
) customer_id,
hp.party_name customer_name,
CAST
(ppa.project_id AS CHARACTER (30))
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 THG’
–‘ANSR_GL_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 THG’
–‘ANSR_GL_ACCOUNT’
AND fv.flex_value = gcc.segment3)
department_name,
gcc.segment4 region, gcc.segment5 FUNCTION,
SUM
(pe.project_revenue_amt
) project_curr_revenue_amt,
pe.bill_trns_currency_code
bill_transaction_currency,
SUM
(NVL (pe.bill_trns_amount, 0)
) bill_transaction_currency_rev,
pe.contract_curr_code contract_currency_code,
SUM
(NVL (pe.contract_curr_amt, 0)
) cont_curr_revenue_amt_sum,
pe.revenue_currency_code,
pe.ledger_currency_code,
SUM
(NVL (pe.ledger_revenue_amt, 0)
) ledger_curr_revenue_amt,
SUM (NVL (pe.revenue_amt, 0)) revenue_curr_amt,
SUM
(DECODE
(‘USD’,
pe.revenue_currency_code, pe.revenue_amt,
pe.ledger_currency_code, pe.ledger_revenue_amt,
pe.contract_curr_code, pe.contract_curr_amt,
pe.project_currency_code, pe.project_revenue_amt,
pe.revenue_amt –pcrdl.amount
* NVL
((SELECT gdr.conversion_rate
FROM gl_daily_rates gdr
WHERE gdr.conversion_type =
‘Corporate’
AND gdr.to_currency = ‘USD’
AND gdr.from_currency =
pe.revenue_currency_code
AND gdr.conversion_date =
prd.gl_date),
1
)
)
) usd_revenue_amount,
pe.project_currency_code project_currency_code,
SUM
(pe.project_revenue_amt
) project_revenue_amount,
ppa.projfunc_currency_code
projfunc_currency_code,
NULL projfunc_revenue_amount,
(SELECT ‘Q’
|| quarter_num
|| ‘-WK’
|| LPAD
(TO_CHAR
(DECODE
(period_num,
53, 14,
DECODE (MOD (period_num,
13
),
0, 13,
MOD (period_num,
13
)
)
)
),
2,
‘0’
)
FROM gl_periods gp
WHERE 1 = 1
AND prd.gl_date BETWEEN start_date AND end_date
AND adjustment_period_flag = ‘N’
AND gp.period_set_name = gl.period_set_name)
planning_week,
prd.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 = ppa.project_id)
region_code,
(SELECT NAME
FROM hr_operating_units
WHERE organization_id =
ppa.org_id)
business_unit,
‘PA’ SOURCE,
–prd.revenue_curr_amt revenue_curr_amt,
pe.event_id expenditure_item_id,
prd.transaction_project_id
transaction_project_id,
gl.NAME ledger_name,
–pe.event_desc,
xal.code_combination_id
FROM okc_k_headers_all_b okh,
— OKC_K_LINES_B okl,
pjf_projects_all_vl ppa,
pjf_project_types_vl ppt,
hr_all_organization_units hou,
hr_all_organization_units hou1,
pjb_billing_events pe,
pjf_tasks_v tsk,
pjf_event_types_tl etyp,
hz_cust_accounts hca,
hz_parties hp,
((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,
(CASE
WHEN bill_transaction_type_code =
‘EVT’
THEN transaction_id
END
) AS event_id
FROM pjb_rev_distributions revenuedistributionpeo
WHERE 1 = 1)) prd,
gl_code_combinations gcc,
gl_ledgers gl,
gl_sets_of_books gsb,
fun_names_business_units_v fnbu,
xla_distribution_links xda,
xla_ae_lines xal,
pjc_exp_items_all pei
WHERE 1 = 1
AND ppa.project_type_id = ppt.project_type_id
AND hou.organization_id =
ppa.carrying_out_organization_id
AND hou1.organization_id = pe.organization_id
AND pe.project_id = ppa.project_id
AND tsk.project_id = ppa.project_id
AND pe.task_id = tsk.task_id
AND pe.event_type_id = etyp.event_type_id
AND pe.event_id = prd.event_id(+)
AND okh.bill_to_acct_id = hca.cust_account_id(+)
AND NVL (prd.bill_transaction_type_code, ‘EVT’) =
‘EVT’
AND prd.reversed_flag IS NULL
AND prd.line_num_reversed IS NULL
AND hca.party_id = hp.party_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 pe.event_id=prd.event_id
AND okh.ID = pe.contract_id
AND NVL (xda.source_distribution_id_num_1(+),
-99999) =
NVL (prd.rev_distribution_id,
-99999)
AND xal.ae_header_id = xda.ae_header_id
–AND xda.ae_line_num(+) <> 2
AND xal.ae_line_num(+) = xda.ae_line_num
AND gcc.code_combination_id =
xal.code_combination_id
–AND xda.unrounded_accounted_cr IS NOT NULL
AND xal.ae_header_id = xda.ae_header_id
AND xal.ae_line_num = xda.ae_line_num
–AND gcc.code_combination_id =xal.code_combination_id
AND gcc.segment1 <> ‘AA’
AND gcc.segment3 <>
‘0000’
–Need to Check this condition
AND ppa.project_id = pei.project_id(+)
AND prd.transaction_id(+) = pei.expenditure_item_id
–AND ppa.segment1 in ‘10002’–(‘TEST-GFY-DE’) — 300000013862871
–AND okh.contract_number = ‘10002’
GROUP BY hca.cust_account_id,
hp.party_name,
ppa.project_id,
ppa.segment1,
okh.contract_number,
ppa.NAME,
hou.NAME,
hca.customer_class_code,
hp.state,
hp.city,
hp.country,
gcc.segment1,
gcc.segment2,
–gcc.account_type,
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,
gl.period_set_name,
prd.gl_period,
gsb.NAME,
prd.gl_date,
ppa.org_id,
–pei.person_type,
prd.revenue_curr_amt,
pe.event_id,
pe.event_desc,
prd.transaction_project_id,
xal.code_combination_id,
prd.rev_distribution_id,
pei.transaction_source_id,
gl.NAME,
–pei.document_id,
xda.source_distribution_id_num_1
ORDER BY okh.contract_number, pe.event_id)
GROUP BY revenue_type,
customer_id,
customer_name,
project_id,
project_number,
project_name,
project_organization,
industry,
state,
city,
country,
company,
ACCOUNT,
–account_type,
account_name,
department,
department_name,
region,
FUNCTION,
project_currency_code,
projfunc_currency_code,
planning_week,
gl_period,
gl_set_of_books_name,
gl_line_dff_prj_num,
global_region,
region_code,
business_unit,
SOURCE,
code_combination_id,
contract_number,
–system_person_type
ledger_name,
bill_transaction_currency,
contract_currency_code,
revenue_currency_code,
ledger_currency_code,
project_currency_code,
projfunc_currency_code)
Union All
SELECT ‘JE Adjustments’ revenue_type,
CAST (0 AS CHARACTER (30)) customer_id,
‘JE Adjustments’ customer_name,
CAST (0 AS CHARACTER (30)) project_id, ‘0’ project_number,
‘0’ contract_number,
–js.user_je_source_name
‘0’ project_name,
–jh.description || ‘:’ || jl.description project_name,
fv.description project_organization,
jh.external_reference industry, NULL state, NULL city,
NULL country, cc.segment1 company, cc.segment2 ACCOUNT,
–cc.account_type,
(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 THG’
–‘ANSR_GL_ACCOUNT’
AND fv.flex_value = cc.segment2) account_name,
cc.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 THG’
–‘ANSR_GL_ACCOUNT’
AND fv.flex_value = cc.segment3) department_name,
cc.segment4 region, cc.segment5 FUNCTION,
SUM (( NVL (( NVL (jl.entered_dr, 0)
– NVL (jl.entered_cr, 0)
),
0
)
* -1
)
) project_curr_revenue_amt,
jl.currency_code bill_transaction_currency,
NULL bill_transaction_currency_rev,
Null contract_currency_code,
Null cont_curr_revenue_amt_sum,
Null revenue_currency_code,
gl.currency_code ledger_currency_code,
NULL ledger_curr_revenue_amt,
SUM ( NVL (NVL (jl.accounted_dr, jl.entered_dr), 0)
– (NVL (NVL (jl.accounted_cr, jl.entered_cr), 0)
)
) revenue_curr_amt,
— Convert based on SOB Currency – Note: Accounted# are null when SOB Currency
— is the same as Journal Currency
SUM
( ( ( NVL (NVL (jl.accounted_dr, jl.entered_dr), 0)
– (NVL (NVL (jl.accounted_cr, jl.entered_cr), 0)
)
)
* NVL ((SELECT gper.avg_rate
FROM gl_lookups lk,
gl_translation_rates gper
WHERE lk.lookup_type =
‘TRANSLATION_BAL_TYPE’
AND lk.lookup_code = gper.actual_flag
AND gper.period_name = jl.period_name
AND gper.set_of_books_id =
gsb.set_of_books_id
–AND gper.functional_currency=gsb.currency_code
AND gper.to_currency_code = ‘USD’
GROUP BY gper.avg_rate,
gper.period_name,
gper.to_currency_code),
1
)
–xxansr_utils_pkg.get_period_avg_rate
— (gsb.currency_code,
— jl.period_name,
— ‘USD’
— )
)
* 1
) usd_revenue_amount,
jl.currency_code project_currency_code,
Null project_revenue_amount,
gsb.currency_code projfunc_currency_code,
SUM
( ( NVL (NVL (jl.accounted_dr, jl.entered_dr), 0)
– (NVL (NVL (jl.accounted_cr, jl.entered_cr), 0))
)
* -1
) projfunc_revenue_amount,
(SELECT ‘Q’
|| quarter_num
|| ‘-WK’
|| LPAD
(TO_CHAR (DECODE (period_num,
53, 14,
DECODE (MOD (period_num,
13
),
0, 13,
MOD (period_num,
13
)
)
)
),
2,
‘0’
)
FROM gl_periods
WHERE 1 = 1
AND period_set_name = ‘THG 4/4/5’
–period_type = ‘4_4_57130283831’
–’22’ — PA Periods
AND TRUNC (jl.effective_date) BETWEEN start_date
AND end_date
AND adjustment_period_flag = ‘N’) planning_week,
–‘JAN-05’ gl_period,
jl.period_name gl_period, gsb.NAME gl_set_of_books_name,
jl.attribute1 gl_line_dff_prj_num, NULL global_region,
‘ ‘ region_code, gsb.NAME business_unit,
js.je_source_name source,
–NULL expenditure_item_id,
–NULL transaction_project_id,
gl.NAME ledger_name,
cc.code_combination_id
FROM gl_je_headers jh,
gl_je_lines jl,
gl_code_combinations cc,
fnd_flex_values_vl fv,
fnd_flex_value_sets fvs,
gl_je_sources js,
gl_sets_of_books gsb,
gl_ledgers gl
WHERE jl.je_header_id = jh.je_header_id
–AND jh.je_header_id = 7327
AND jh.ledger_id = gsb.set_of_books_id
AND gl.ledger_id = gsb.set_of_books_id
AND NVL (jh.status, ‘X’) = ‘P’
AND jh.je_source = js.je_source_name
AND fv.flex_value_set_id = fvs.flex_value_set_id
AND fvs.flex_value_set_name = ‘Department THG’
–‘ANSR_GL_DEPARTMENT’
AND NVL (jh.je_category, ‘x’) != ‘1’
AND fv.flex_value = cc.segment3
AND jl.code_combination_id = cc.code_combination_id
AND cc.account_type IN (‘R’, ‘E’)
AND EXISTS (
SELECT ‘X’
FROM gl_code_combinations
WHERE 1 = 1
AND account_type IN (‘R’, ‘E’)
— AND ( segment2 BETWEEN ‘410500’ AND ‘433099’
— OR segment2 BETWEEN ‘532000’ AND ‘547099’
— )
AND segment1 != ‘AA’
AND code_combination_id = jl.code_combination_id)
AND ( cc.segment2 BETWEEN ‘410000’ AND ‘549999’
AND cc.segment2 NOT BETWEEN ‘500000’ AND ‘528509’
AND cc.segment2 NOT BETWEEN ‘528511’ AND ‘529999’
)
GROUP BY js.user_je_source_name,
jh.description,
jl.description,
fv.description,
jh.external_reference,
cc.segment1,
cc.segment2,
cc.account_type,
cc.segment3,
cc.segment4,
cc.segment5,
jl.period_name,
gsb.set_of_books_id,
jl.currency_code,
gsb.currency_code,
jl.effective_date,
jl.period_name,
gsb.name,
jl.attribute1,
cc.segment1,
js.je_source_name,
cc.code_combination_id,
jl.period_name,
gl.currency_code,
gl.name)
WHERE gl_period IN (:p_gl_period)
— AND ROWNUM <= 75001
/*AND ( ACCOUNT BETWEEN ‘410000’ AND ‘549999’
AND ACCOUNT NOT BETWEEN ‘500000’ AND ‘528509’
AND ACCOUNT NOT BETWEEN ‘528511’ AND ‘529999’
)*/
GROUP BY revenue_type,
customer_id,
customer_name,
project_id,
project_number,
contract_number,
project_name,
project_organization,
industry,
state,
city,
country,
company,
ACCOUNT,
account_name,
department,
department_name,
region,
FUNCTION,
bill_transaction_currency,
contract_currency_code,
revenue_currency_code,
ledger_currency_code,
project_currency_code,
projfunc_currency_code,
planning_week,
gl_period,
gl_set_of_books_name,
gl_line_dff_prj_num,
region_code,
SOURCE,
business_unit,
ledger_name
