ORACLE MASTERMINDS: REVENUE TRANSFER REPORT (FUSION)

Saturday 22 September 2018

REVENUE TRANSFER REPORT (FUSION)

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,
project_organization global_region, region_code, SOURCE,
business_unit, ledger_name,
project_organization 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 XXX'
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 XXX'
--'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 XXX'
--'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 XXX'
--'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 XXX'
--'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 XXX'
--'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 = 'XXX 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 XXX'
--'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

Saturday 22 September 2018

REVENUE TRANSFER REPORT (FUSION)

No comments:

Post a Comment