Purpose of the report is to display project hours and hard-limit amount with interactive mode.
with XXANSR_PA_EVENTS_cte as (SELECT evt.ROWID as "ROW_ID",
evt.event_id as "EVENT_ID",
evt.project_id as "PROJECT_ID",
prj.segment1 as "PROJECT_NUMBER",
prjt.name as "PROJECT_NAME",
prjt.description as "DESCRIPTION_MIR",
evt.task_id as "TASK_ID",
tsk.task_number as "TASK_NUMBER",
tsk.task_name as "TASK_NAME",
evt.event_num as "EVENT_NUM",
--evt.event_type_code as "EVENT_TYPE",
typl.event_type_name as "EVENT_TYPE",
--DPL typ.event_type_classification as "EVENT_TYPE_CLASSIFICATION",
evt.completion_date as "EVENT_DATE",
evt.event_desc as "DESCRIPTION",
prj.org_id as "OPERATING_UNIT",
prj.carrying_out_organization_id as "PROJECT_ORG_ID",
(SELECT name FROM hr_organization_units WHERE organization_id = prj.carrying_out_organization_id) as "PROJECT_OWNING_ORG",
evt.organization_id as "ORGANIZATION_ID",
hou.name as "ORGANIZATION_NAME",
ctl.hard_limit_amount,
--DPL evt.bill_amount as "BILL_AMOUNT",
evt.revenue_amt as "REVENUE_AMOUNT",
evt.bill_hold_flag as "BILL_HOLD_FLAG",
--DPL evt.revenue_distributed_flag as "REVENUE_DISTRIBUTED_FLAG",
--DPL prj.project_level_funding_flag as "PROJECT_LEVEL_FUNDING_FLAG",
evt.attribute_category as "ATTRIBUTE_CATEGORY",
evt.attribute1 as "ATTRIBUTE1",
evt.attribute2 as "ATTRIBUTE2",
evt.attribute3 as "ATTRIBUTE3",
evt.attribute4 as "ATTRIBUTE4",
evt.attribute5 as "ATTRIBUTE5",
evt.attribute6 as "ATTRIBUTE6",
evt.attribute7 as "ATTRIBUTE7",
evt.attribute8 as "ATTRIBUTE8",
evt.attribute9 as "ATTRIBUTE9",
evt.attribute10 as "ATTRIBUTE10",
--DPL evt.inventory_org_id,
--DPL evt.inventory_item_id as "INVENTORY_ITEM_ID",
evt.quantity_billed as "QUANTITY_BILLED",
evt.uom_code as "UOM_CODE",
evt.unit_price as "UNIT_PRICE",
evt.reference1 as "REFERENCE1",
evt.reference2 as "REFERENCE2",
evt.reference3 as "REFERENCE3",
evt.reference4 as "REFERENCE4",
evt.reference5 as "REFERENCE5",
evt.reference6 as "REFERENCE6",
evt.reference7 as "REFERENCE7",
evt.reference8 as "REFERENCE8",
evt.reference9 as "REFERENCE9",
evt.reference10 as "REFERENCE10",
evt.last_update_date as "LAST_UPDATE_DATE",
evt.last_updated_by as "LAST_UPDATED_BY",
evt.creation_date as "CREATION_DATE",
evt.created_by as "CREATED_BY",
evt.last_update_login as "LAST_UPDATE_LOGIN",
evt.bill_trns_currency_code as "BILL_TRANS_CURRENCY_CODE",
evt.bill_trns_amount as "BILL_TRANS_AMOUNT",
evt.contract_curr_amt as "BILL_TRANS_REV_AMOUNT",
evt.contract_curr_code as "CONTRACT_CURR_CODE",
evt.contract_curr_rate_type as "CONTRACT_CUUR_RATE_TYPE",
--DPL c.user_conversion_type as "PROJECT_RATE_TYPE_NAME",
evt.contract_curr_exchg_rate as "CONTRACT_CURR_EXCHG_RATE",
evt.contract_curr_exchg_date as "CONTRACT_CURR_EXCHG_DATE",
--DPL evt.project_bill_amount as "PROJFUNC_BILL_AMOUNT",
--DPL evt.project_inv_exchange_rate as "PROJECT_INV_EXCHANGE_RATE",
--DPL evt.project_inv_rate_date as "PROJECT_INV_RATE_DATE",
evt.project_revenue_amt as "PROJECT_REVENUE_AMT",
--DPL evt.project_rev_exchange_rate as "PROJECT_REV_EXCHANGE_RATE",
--DPL evt.project_rev_rate_date as "PROJECT_REV_RATE_DATE",
--DPL evt.projfunc_rev_rate_date,
--DPL evt.projfunc_rate_type as "PROJFUNC_RATE_TYPE",
--DPL c1.user_conversion_type as "PROJFUNC_RATE_TYPE_NAME",
--DPL evt.projfunc_rate_date as "PROJFUNC_RATE_DATE",
--DPL evt.projfunc_exchange_rate as "PROJFUNC_EXCHANGE_RATE",
--DPL evt.projfunc_bill_amount,
--DPL evt.projfunc_inv_exchange_rate,
--DPL evt.projfunc_inv_rate_date,
--DPL evt.projfunc_revenue_amount,
--DPL evt.projfunc_rev_exchange_rate as "PROJFUNC_REV_EXCHANGE_RATE",
--DPL evt.projfunc_rev_rate_date as "PROJFUNC_REV_RATE_DATE",
--DPL evt.funding_rate_type as "FUNDING_RATE_TYPE",
--DPL c2.user_conversion_type as "FUNDING_RATE_TYPE_NAME",
--DPL evt.funding_rate_date as "FUNDING_RATE_DATE",
--DPL evt.funding_exchange_rate as "FUNDING_EXCHANGE_RATE",
evt.invoice_currency_code as "INVOICE_CURRENCY_CODE",
evt.invoice_curr_rate_type as "INVOICE_CURR_RATE_TYPE",
evt.invoice_curr_exchg_date as "INVOICE_CURR_EXCHG_DATE",
evt.invoice_curr_exchg_rate as "INVOICE_CURR_EXCHG_RATE",
--DPL evt.revproc_currency_code as "REVPROC_CURRENCY_CODE",
--DPL evt.revproc_rate_type as "REVPROC_RATE_TYPE",
--DPL evt.revproc_rate_date as "REVPROC_RATE_DATE",
--DPL evt.revproc_exchange_rate as "REVPROC_EXCHANGE_RATE",
--DPL evt.inv_gen_rejection_code as "INV_GEN_REJECTION_CODE",
--DPL pl1.meaning as "INV_GEN_REJECTION_REASON",
--DPL pl.meaning as "REV_GEN_REJECTION_REASON",
prj.project_currency_code, -- as "PROJ_LEVEL_CURR_CODE",
--DPL prj.project_bil_rate_date_code as "PROJ_LEVEL_RT_DT_COD",
--DPL prj.project_bil_rate_type as "PROJ_LEVEL_RT_TYP",
--DPL prj.project_bil_rate_date as "PROJ_LEVEL_RT_DT",
--DPL prj.project_bil_exchange_rate as "PROJ_LEVEL_EXCH_RT",
--DPL prj.projfunc_currency_code as "PROJ_LEVEL_FUNC_CURR_COD",
--DPL prj.projfunc_bil_rate_date_code as "PROJ_LEVEL_FUNC_RT_DT_COD",
--DPL prj.projfunc_bil_rate_type as "PROJ_LEVEL_FUNC_RT_TYP",
--DPL prj.projfunc_bil_rate_date as "PROJ_LEVEL_FUNC_RT_DT",
--DPL prj.projfunc_bil_exchange_rate as "PROJ_LEVEL_FUNC_EXCH_RT",
--DPL prj.funding_rate_date_code as "PROJ_LEVEL_FUND_RT_DT_COD",
--DPL prj.funding_rate_type as "PROJ_LEVEL_FUND_RT_TYP",
--DPL prj.funding_rate_date as "PROJ_LEVEL_FUND_RT_DT",
--DPL prj.funding_exchange_rate as "PROJ_LEVEL_FUND_EXCH_RT",
--DPL prj.revproc_currency_code as "PROJ_LEVEL_REVPRO_CURR_COD",
--DPL prj.invproc_currency_type as "PROJ_LEVEL_INVPRO_CURR_TYP",
--DPL prj.multi_currency_billing_flag as "MULTI_CURRENCY_BILLING_FLAG",
prj.project_status_code as "PROJECT_STATUS_CODE",
--DPL evt.adjusting_revenue_flag as "ADJUSTING_REVENUE_FLAG",
--DPL evt.zero_revenue_amount_flag as "ZERO_REVENUE_AMOUNT_FLAG",
evt.non_updateable_flag as "NON_UPDATEABLE_FLAG",
evt.audit_cost_plan_type_id as "AUDIT_COST_PLAN_TYPE_ID",
evt.audit_rev_plan_type_id as "AUDIT_REV_PLAN_TYPE_ID",
evt.revenue_hold_flag as "REVENUE_HOLD_FLAG",
NULL as "REP_PROJFUNC_CURRENCY_CODE",
NULL as "REP_PROJFUNC_RATE_TYPE",
NULL as "REP_PROJFUNC_RATE_TYPE_NAME",
TO_DATE(NULL) as "REP_PROJFUNC_RATE_DATE",
TO_NUMBER(NULL) as "REP_PROJFUNC_EXCHANGE_RATE",
TO_DATE(NULL) as "REP_PROJFUNC_REV_RATE_DATE",
TO_NUMBER(NULL) as "REP_PROJFUNC_REV_EXCHANGE_RATE",
TO_NUMBER(NULL) as "REP_PROJFUNC_REVENUE_AMOUNT",
TO_DATE(NULL) as "REP_PROJFUNC_INV_RATE_DATE",
TO_NUMBER(NULL) as "REP_PROJFUNC_INV_EXCHANGE_RATE",
TO_NUMBER(NULL) as "REP_PROJFUNC_BILL_AMOUNT",
--DPL evt.billed_flag as "BILLED_FLAG"
prj.pm_product_code as "PM_PRODUCT_CODE",
okh.contract_number "CONTRACT_NUMBER",
okl.line_number "CONTRACT_LINE_NUMBER",
flv.meaning "REVENUE_STATUS",
evt.revenue_exception_flag "REVENUE_EXCEPTION",
Round(evt.revenue_recognzd_percentage,2) "REVENUE_PERCENTAGE"
--DPL prj.pm_event_reference as "PM_EVENT_REFERENCE"
--DPL pl2.meaning as "PM_PRODUCT_CODE_DISP"
FROM PJF_PROJECTS_ALL_B prj
inner join PJF_PROJECTS_ALL_TL prjt
on prjt.project_id = prj.project_id
inner join OKC_K_HEADERS_ALL_B okh
on okh.contract_number = prj.segment1
inner join OKC_K_LINES_B okl
on okh.major_version=okl.major_version
AND okh.id=okl.dnz_chr_id
inner join PJB_BILLING_CONTROLS ctl
on ctl.contract_id = okh.id
inner join PJB_BILLING_EVENTS evt
on evt.contract_id = okh.id
and evt.major_version = okh.major_version
and evt.project_id=prj.project_id
and ctl.contract_line_id=evt.contract_line_id
inner join PJF_TASKS_V tsk
on tsk.project_id = prj.project_id
and tsk.task_id=evt.task_id
inner join PJF_EVENT_TYPES_B typ
on typ.event_type_id = evt.event_type_id
inner join PJF_EVENT_TYPES_TL typl
on typ.event_type_id = typl.event_type_id
inner join hr_all_organization_units_tl hou
on hou.organization_id = evt.organization_id
inner join fnd_lookup_values_tl flv
on flv.lookup_type = 'PJB_EVT_REVENUE_RECOGNZD'
AND flv.lookup_code=evt.revenue_recognzd_flag
where evt.task_id IS NOT NULL
and hou.LANGUAGE = USERENV('LANG')
UNION ALL
SELECT evt.rowid,
evt.event_id,
evt.project_id,
prj.segment1,
prjt.name,
prjt.description,
evt.task_id,
NULL,
NULL,
evt.event_num,
evt.event_type_code,
--DPL et.event_type_classification,
evt.completion_date,
evt.event_desc,
prj.org_id,
prj.carrying_out_organization_id,
(SELECT name FROM hr_organization_units hou WHERE hou.organization_id = prj.carrying_out_organization_id) as project_owning_org,
evt.organization_id,
hou.name,
ctl.hard_limit_amount,
--DPL evt.bill_amount,
evt.revenue_amt,
evt.bill_hold_flag,
--DPL evt.revenue_distributed_flag,
--DPL prj.project_level_funding_flag,
evt.attribute_category,
evt.attribute1,
evt.attribute2,
evt.attribute3,
evt.attribute4,
evt.attribute5,
evt.attribute6,
evt.attribute7,
evt.attribute8,
evt.attribute9,
evt.attribute10,
--DPL evt.inventory_org_id,
--DPL evt.inventory_item_id,
evt.quantity_billed,
evt.uom_code,
evt.unit_price,
evt.reference1,
evt.reference2,
evt.reference3,
evt.reference4,
evt.reference5,
evt.reference6,
evt.reference7,
evt.reference8,
evt.reference9,
evt.reference10,
evt.last_update_date,
evt.last_updated_by,
evt.creation_date,
evt.created_by,
evt.last_update_login,
evt.bill_trns_currency_code,
evt.bill_trns_amount,
evt.contract_curr_amt as "BILL_TRANS_REV_AMOUNT",
evt.contract_curr_code,
evt.contract_curr_rate_type,
--DPL c.user_conversion_type project_rate_type_name,
evt.contract_curr_exchg_rate,
evt.contract_curr_exchg_date,
--DPL evt.project_bill_amount,
--DPL evt.project_inv_exchange_rate,
--DPL evt.project_inv_rate_date,
evt.project_revenue_amt,
--DPL evt.project_rev_exchange_rate,
--DPL evt.project_rev_rate_date,
--DPL evt.projfunc_currency_code,
--DPL evt.projfunc_rate_type,
--DPL c1.user_conversion_type projfunc_rate_type_name,
--DPL evt.projfunc_rate_date,
--DPL evt.projfunc_exchange_rate,
--DPL evt.projfunc_bill_amount,
--DPL evt.projfunc_inv_exchange_rate,
--DPL evt.projfunc_inv_rate_date,
--DPL evt.projfunc_revenue_amount,
--DPL evt.projfunc_rev_exchange_rate,
--DPL evt.projfunc_rev_rate_date,
--DPL evt.funding_rate_type,
--DPL c2.user_conversion_type funding_rate_type_name,
--DPL evt.funding_rate_date,
--DPL evt.funding_exchange_rate,
evt.invoice_currency_code,
evt.invoice_curr_rate_type,
evt.invoice_curr_exchg_date ,
evt.invoice_curr_exchg_rate,
--DPL evt.revproc_currency_code,
--DPL evt.revproc_rate_type,
--DPL evt.revproc_rate_date,
--DPL evt.revproc_exchange_rate,
--DPL evt.inv_gen_rejection_code,
--DPL pl1.meaning,
--DPL pl.meaning,
prj.project_currency_code,
--DPL prj.project_bil_rate_date_code,
--DPL prj.project_bil_rate_type,
--DPL prj.project_bil_rate_date,
--DPL prj.project_bil_exchange_rate,
--DPL prj.projfunc_currency_code,
--DPL prj.projfunc_bil_rate_date_code,
--DPL prj.projfunc_bil_rate_type,
--DPL prj.projfunc_bil_rate_date,
--DPL prj.projfunc_bil_exchange_rate,
--DPL prj.funding_rate_date_code,
--DPL prj.funding_rate_type,
--DPL prj.funding_rate_date,
--DPL prj.funding_exchange_rate,
--DPL prj.revproc_currency_code,
--DPL prj.invproc_currency_type,
--DPL prj.multi_currency_billing_flag,
prj.project_status_code,
--DPL evt.adjusting_revenue_flag,
--DPL evt.zero_revenue_amount_flag,
evt.non_updateable_flag,
evt.audit_cost_plan_type_id,
evt.audit_rev_plan_type_id,
evt.revenue_hold_flag,
NULL,
NULL,
NULL,
TO_DATE (NULL),
TO_NUMBER (NULL),
TO_DATE (NULL),
TO_NUMBER (NULL),
TO_NUMBER (NULL),
TO_DATE (NULL),
TO_NUMBER (NULL),
TO_NUMBER (NULL),
--DPL evt.billed_flag,
prj.pm_product_code,
okh.contract_number "CONTRACT_NUMBER",
okl.line_number "CONTRACT_LINE_NUMBER",
flv.meaning "REVENUE_STATUS",
evt.revenue_exception_flag "REVENUE_EXCEPTION",
Round(evt.revenue_recognzd_percentage,2) "REVENUE_PERCENTAGE"
--DPL evt.pm_event_reference,
--DPL pl2.meaning
FROM PJF_PROJECTS_ALL_B prj
inner join PJF_PROJECTS_ALL_TL prjt
on prjt.project_id = prj.project_id
inner join OKC_K_HEADERS_ALL_B okh
on okh.contract_number = prj.segment1
inner join OKC_K_LINES_B okl
on okh.major_version=okl.major_version
AND okh.id=okl.dnz_chr_id
inner join PJB_BILLING_CONTROLS ctl
on ctl.contract_id = okh.id
inner join PJB_BILLING_EVENTS evt
on evt.contract_id = okh.id
and evt.major_version = okh.major_version
and evt.project_id=prj.project_id
and ctl.contract_line_id=evt.contract_line_id
inner join PJF_TASKS_V tsk
on tsk.project_id = prj.project_id
and tsk.task_id=evt.task_id
inner join PJF_EVENT_TYPES_B typ
on typ.event_type_id = evt.event_type_id
inner join hr_all_organization_units_tl hou
on hou.organization_id = evt.organization_id
inner join fnd_lookup_values_tl flv
on flv.lookup_type = 'PJB_EVT_REVENUE_RECOGNZD'
AND flv.lookup_code=evt.revenue_recognzd_flag
where evt.task_id IS NULL
and hou.LANGUAGE = USERENV('LANG'))
SELECT
o259902.project_number,
o259902.project_name,
o259902.task_number,
o259902.task_name,
o259902.event_type,
o259902.event_date,
o259902.organization_name,
o259902.bill_trans_amount,
o259902.bill_trans_rev_amount,
o259902.project_currency_code,
o259902.hard_limit_amount,
o259902.contract_number,
o259902.contract_line_number,
o259902.revenue_status,
o259902.revenue_exception,
o259902.revenue_percentage,
o259902.event_num,
o259902.description,
o259902.project_revenue_amt,
(o259902.bill_trans_amount-o259902.bill_trans_rev_amount) Tot
FROM
apps.xxansr_pa_events_cte o259902
WHERE
o259902.project_status_code = 'ACTIVE'
AND o259902.hard_limit_amount is not null
--TODO AND o259902.description = 'Hard Limit'
AND o259902.bill_trans_rev_amount != '0'
Group by o259902.project_number,
o259902.project_name,
o259902.task_number,
o259902.task_name,
o259902.event_type,
o259902.event_date,
o259902.organization_name,
o259902.bill_trans_rev_amount,
o259902.project_currency_code,
o259902.hard_limit_amount,
o259902.contract_number,
o259902.contract_line_number,
o259902.revenue_status,
o259902.revenue_exception,
o259902.revenue_percentage,
o259902.event_num,
o259902.description,
o259902.project_revenue_amt,
o259902.bill_trans_amount
No comments:
Post a Comment