SELECT A.PROJECT_STATUS,
A.OPERATING_UNIT_NAME,
A.AGREEMENT_TYPE,
A.PROJECT_ORGANIZATION_NAME,
A.PROJECT_NUMBER,
A.PROJECT_NAME,
A.START_DATE,
A.COMPLETION_DATE,
A.TASK_NUMBER,
A.TASK_NAME,
A.PROJECT_CURRENCY,
ROUND(A.BUDGETED_REV_PROJ_CURR) BUDGETED_REV_PROJ_CURR,
ROUND(A.ACTUAL_REV_ITD_PRJ_CURR) ACTUAL_REV_ITD_PRJ_CURR,
ROUND(A.BILLABLE_HOURS_ACTUAL_ITD) BILLABLE_HOURS_ACTUAL_ITD,
ROUND(A.TOTAL_HOURS_ACTUAL_ITD) TOTAL_HOURS_ACTUAL_ITD,
A.FUNC_CURRENCY,
ROUND(A.ACTUAL_REV_ITD_FUNC_CURR) ACTUAL_REV_ITD_FUNC_CURR,
(ROUND ( ( (A.ACTUAL_REV_ITD_PRJ_CURR / (CASE
WHEN A.BUDGETED_REV_PROJ_CURR = 0 THEN 0.1
ELSE A.BUDGETED_REV_PROJ_CURR
END)
) * 100)) || ' %') AS PERC_COMPLETE_PROJ_CURR,
(ROUND ( ( (A.ACTUAL_REV_ITD_FUNC_CURR
/ (CASE
WHEN (TRUNC ( (TRUNC (NVL(GDR.conversion_rate, 1), 4) * a.Budgeted_Rev_Proj_Curr), 2)) = 0
THEN
0.1
ELSE
(TRUNC ( (TRUNC (NVL(GDR.conversion_rate, 1), 4) * a.Budgeted_Rev_Proj_Curr), 2))
END)) * 100))
|| ' %') AS PERC_COMPLETE_FUNC_CURR
,A.Contract_Status
,A.contract_number
,A.line_number
FROM gl_daily_rates GDR,
(SELECT PPA.PROJECT_STATUS_CODE AS Project_Status,
FUNB.BU_NAME AS Operating_Unit_Name,
OTYPE.NAME AS Agreement_Type,
haou.NAME AS project_organization_name,
ppa.segment1 AS project_number,
PPA.NAME AS project_name,
TO_CHAR (ppa.start_date,
'DD-MON-YYYY',
'NLS_DATE_LANGUAGE = american')
AS start_date,
TO_CHAR (ppa.completion_date,
'DD-MON-YYYY',
'NLS_DATE_LANGUAGE = american')
AS completion_date
,PT.TASK_NUMBER
,PT.TASK_NAME
,OKCH.CURRENCY_CODE AS Project_currency,
okcl.line_amount AS Budgeted_Rev_Proj_Curr
,(SELECT NVL(SUM(NVL(PRD.project_curr_revenue_amt, 0)),0) project_curr_revenue_amt
FROM pjb_rev_distributions PRD
WHERE 1=1
AND PRD.contract_id = OKCH.id
AND PRD.contract_line_id = OKCL.id) Actual_Rev_ITD_Prj_Curr
,(SELECT NVL (SUM (projectcostdistributionpeo.quantity), 0)
FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
pjc_exp_items_all expenditureitempeo,
pjf_exp_types_vl pet
WHERE (projectcostdistributionpeo.expenditure_item_id =
expenditureitempeo.expenditure_item_id
AND expenditureitempeo.expenditure_type_id =
pet.expenditure_type_id
AND projectcostdistributionpeo.TASK_ID = NVL(LINK.proj_element_id, projectcostdistributionpeo.TASK_ID)
AND pet.expenditure_type_name IN
('Professional Labor',
'Contract Professional Labor')
AND projectcostdistributionpeo.BILLABLE_FLAG = 'Y')
AND expenditureitempeo.project_id = ppa.project_id)
AS Billable_Hours_Actual_ITD,
(SELECT NVL (SUM (projectcostdistributionpeo.quantity), 0)
FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
pjc_exp_items_all expenditureitempeo,
pjf_exp_types_vl pet
WHERE (projectcostdistributionpeo.expenditure_item_id =
expenditureitempeo.expenditure_item_id
AND expenditureitempeo.expenditure_type_id =
pet.expenditure_type_id
AND projectcostdistributionpeo.TASK_ID = NVL(LINK.proj_element_id, projectcostdistributionpeo.TASK_ID)
AND pet.expenditure_type_name IN
('Professional Labor',
'Contract Professional Labor'))
AND expenditureitempeo.project_id = ppa.project_id)
AS Total_Hours_Actual_ITD,
link.proj_element_id
,(SELECT LEDGER_CURRENCY_CODE
FROM pjb_rev_distributions
WHERE linked_project_id = ppa.project_id AND ROWNUM < 2)
AS Func_Currency
,(SELECT NVL(SUM(NVL(PRD.ledger_curr_revenue_amt, 0)), 0)
FROM pjb_rev_distributions PRD
WHERE 1=1
AND PRD.contract_id = OKCH.id
AND PRD.contract_line_id = OKCL.id) Actual_Rev_ITD_Func_curr
,OKCL.REVENUE_IMPACT_DATE
,OKCH.sts_code Contract_Status
,OKCH.contract_number
,PPA.project_id
,PPA.created_from_project_id
,OKCL.line_number
FROM okc_k_headers_all_b OKCH,
okc_k_lines_b OKCL,
okc_contract_types_tl OTYPE,
pjb_cntrct_proj_links LINK,
pjf_projects_all_vl PPA,
pjf_tasks_v PT,
fun_all_business_units_v FUNB,
hr_all_organization_units HAOU
WHERE 1 = 1
AND OKCH.id = OKCL.chr_id
AND OKCH.version_type = 'C'
AND OKCL.version_type = 'C'
AND OKCH.sts_code <> 'EXPIRED' --= 'ACTIVE'
AND OKCH.id = LINK.contract_id
AND OKCL.id = LINK.contract_line_id
AND LINK.version_type = 'C'
AND LINK.project_id = PPA.project_id
AND LINK.proj_element_id = PT.task_id (+)
AND PPA.carrying_out_organization_id = HAOU.organization_id
AND FUNB.BU_ID = OKCH.org_id
AND OTYPE.language = 'US'
AND OTYPE.contract_type_id = OKCH.contract_type_id
AND PPA.PROJECT_STATUS_CODE <> 'CLOSED' ) A
WHERE GDR.from_currency (+) = a.project_currency
AND GDR.to_currency (+) = a.Func_Currency
AND UPPER (GDR.conversion_type (+)) = 'CORPORATE'
AND TRUNC(GDR.conversion_date (+)) = TRUNC(a.revenue_impact_date)
ORDER BY A.project_number, A.contract_number, A.line_number, A.task_number
A.OPERATING_UNIT_NAME,
A.AGREEMENT_TYPE,
A.PROJECT_ORGANIZATION_NAME,
A.PROJECT_NUMBER,
A.PROJECT_NAME,
A.START_DATE,
A.COMPLETION_DATE,
A.TASK_NUMBER,
A.TASK_NAME,
A.PROJECT_CURRENCY,
ROUND(A.BUDGETED_REV_PROJ_CURR) BUDGETED_REV_PROJ_CURR,
ROUND(A.ACTUAL_REV_ITD_PRJ_CURR) ACTUAL_REV_ITD_PRJ_CURR,
ROUND(A.BILLABLE_HOURS_ACTUAL_ITD) BILLABLE_HOURS_ACTUAL_ITD,
ROUND(A.TOTAL_HOURS_ACTUAL_ITD) TOTAL_HOURS_ACTUAL_ITD,
A.FUNC_CURRENCY,
ROUND(A.ACTUAL_REV_ITD_FUNC_CURR) ACTUAL_REV_ITD_FUNC_CURR,
(ROUND ( ( (A.ACTUAL_REV_ITD_PRJ_CURR / (CASE
WHEN A.BUDGETED_REV_PROJ_CURR = 0 THEN 0.1
ELSE A.BUDGETED_REV_PROJ_CURR
END)
) * 100)) || ' %') AS PERC_COMPLETE_PROJ_CURR,
(ROUND ( ( (A.ACTUAL_REV_ITD_FUNC_CURR
/ (CASE
WHEN (TRUNC ( (TRUNC (NVL(GDR.conversion_rate, 1), 4) * a.Budgeted_Rev_Proj_Curr), 2)) = 0
THEN
0.1
ELSE
(TRUNC ( (TRUNC (NVL(GDR.conversion_rate, 1), 4) * a.Budgeted_Rev_Proj_Curr), 2))
END)) * 100))
|| ' %') AS PERC_COMPLETE_FUNC_CURR
,A.Contract_Status
,A.contract_number
,A.line_number
FROM gl_daily_rates GDR,
(SELECT PPA.PROJECT_STATUS_CODE AS Project_Status,
FUNB.BU_NAME AS Operating_Unit_Name,
OTYPE.NAME AS Agreement_Type,
haou.NAME AS project_organization_name,
ppa.segment1 AS project_number,
PPA.NAME AS project_name,
TO_CHAR (ppa.start_date,
'DD-MON-YYYY',
'NLS_DATE_LANGUAGE = american')
AS start_date,
TO_CHAR (ppa.completion_date,
'DD-MON-YYYY',
'NLS_DATE_LANGUAGE = american')
AS completion_date
,PT.TASK_NUMBER
,PT.TASK_NAME
,OKCH.CURRENCY_CODE AS Project_currency,
okcl.line_amount AS Budgeted_Rev_Proj_Curr
,(SELECT NVL(SUM(NVL(PRD.project_curr_revenue_amt, 0)),0) project_curr_revenue_amt
FROM pjb_rev_distributions PRD
WHERE 1=1
AND PRD.contract_id = OKCH.id
AND PRD.contract_line_id = OKCL.id) Actual_Rev_ITD_Prj_Curr
,(SELECT NVL (SUM (projectcostdistributionpeo.quantity), 0)
FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
pjc_exp_items_all expenditureitempeo,
pjf_exp_types_vl pet
WHERE (projectcostdistributionpeo.expenditure_item_id =
expenditureitempeo.expenditure_item_id
AND expenditureitempeo.expenditure_type_id =
pet.expenditure_type_id
AND projectcostdistributionpeo.TASK_ID = NVL(LINK.proj_element_id, projectcostdistributionpeo.TASK_ID)
AND pet.expenditure_type_name IN
('Professional Labor',
'Contract Professional Labor')
AND projectcostdistributionpeo.BILLABLE_FLAG = 'Y')
AND expenditureitempeo.project_id = ppa.project_id)
AS Billable_Hours_Actual_ITD,
(SELECT NVL (SUM (projectcostdistributionpeo.quantity), 0)
FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
pjc_exp_items_all expenditureitempeo,
pjf_exp_types_vl pet
WHERE (projectcostdistributionpeo.expenditure_item_id =
expenditureitempeo.expenditure_item_id
AND expenditureitempeo.expenditure_type_id =
pet.expenditure_type_id
AND projectcostdistributionpeo.TASK_ID = NVL(LINK.proj_element_id, projectcostdistributionpeo.TASK_ID)
AND pet.expenditure_type_name IN
('Professional Labor',
'Contract Professional Labor'))
AND expenditureitempeo.project_id = ppa.project_id)
AS Total_Hours_Actual_ITD,
link.proj_element_id
,(SELECT LEDGER_CURRENCY_CODE
FROM pjb_rev_distributions
WHERE linked_project_id = ppa.project_id AND ROWNUM < 2)
AS Func_Currency
,(SELECT NVL(SUM(NVL(PRD.ledger_curr_revenue_amt, 0)), 0)
FROM pjb_rev_distributions PRD
WHERE 1=1
AND PRD.contract_id = OKCH.id
AND PRD.contract_line_id = OKCL.id) Actual_Rev_ITD_Func_curr
,OKCL.REVENUE_IMPACT_DATE
,OKCH.sts_code Contract_Status
,OKCH.contract_number
,PPA.project_id
,PPA.created_from_project_id
,OKCL.line_number
FROM okc_k_headers_all_b OKCH,
okc_k_lines_b OKCL,
okc_contract_types_tl OTYPE,
pjb_cntrct_proj_links LINK,
pjf_projects_all_vl PPA,
pjf_tasks_v PT,
fun_all_business_units_v FUNB,
hr_all_organization_units HAOU
WHERE 1 = 1
AND OKCH.id = OKCL.chr_id
AND OKCH.version_type = 'C'
AND OKCL.version_type = 'C'
AND OKCH.sts_code <> 'EXPIRED' --= 'ACTIVE'
AND OKCH.id = LINK.contract_id
AND OKCL.id = LINK.contract_line_id
AND LINK.version_type = 'C'
AND LINK.project_id = PPA.project_id
AND LINK.proj_element_id = PT.task_id (+)
AND PPA.carrying_out_organization_id = HAOU.organization_id
AND FUNB.BU_ID = OKCH.org_id
AND OTYPE.language = 'US'
AND OTYPE.contract_type_id = OKCH.contract_type_id
AND PPA.PROJECT_STATUS_CODE <> 'CLOSED' ) A
WHERE GDR.from_currency (+) = a.project_currency
AND GDR.to_currency (+) = a.Func_Currency
AND UPPER (GDR.conversion_type (+)) = 'CORPORATE'
AND TRUNC(GDR.conversion_date (+)) = TRUNC(a.revenue_impact_date)
ORDER BY A.project_number, A.contract_number, A.line_number, A.task_number
Minecraft Benzeri Oyunlar
ReplyDeleteCall of Duty Benzeri Oyunlar
LOL Benzeri Oyunlar
Sims Benzeri Oyunlar
Summertime Saga Benzeri Oyunlar
S4DG5U