VIEW: APPS.PA_LABOR_EXPENDITURES_VIEW

Source

View Text - Preformatted

SELECT p.project_id , p.segment1 , p.name , p.project_type , t.task_id , t.task_number , t.task_name , per.person_id , NVL(per.employee_number , per.npw_number ) employee_number , per.full_name , job.job_id , job.name , org.organization_id , org.name , e.expenditure_ending_date , e.orig_user_exp_txn_reference , ei.expenditure_item_date , ei.expenditure_type , decode(ei.system_linkage_function,'ST' ,nvl(ei.quantity,0) ,0) , decode(ei.system_linkage_function,'OT' ,nvl(ei.quantity,0),0) , decode(ei.billable_flag,'Y',nvl(ei.quantity,0),0) , decode(ei.billable_flag,'N',nvl(ei.quantity,0),0) , ei.raw_cost , ei.burden_cost , nvl(ei.adjusted_revenue,ei.raw_revenue) , ei.accrued_revenue , ei.billable_flag , ei.denom_currency_code , ei.denom_raw_cost , ei.denom_burdened_cost , ei.acct_currency_code , ei.acct_rate_date , ei.acct_rate_type , ei.acct_exchange_rate , ei.acct_raw_cost , ei.acct_burdened_cost , ei.project_currency_code , ei.project_rate_date , ei.project_rate_type , ei.project_exchange_rate , e.transfer_status_code , e.wf_status_code , e.orig_exp_txn_reference1 , e.orig_exp_txn_reference2 , e.orig_exp_txn_reference3 , e.user_batch_name , e.overriding_approver_person_id FROM pa_projects_all p, pa_tasks t, pa_expenditures_all e, pa_expenditure_items_all ei, per_people_f per, per_jobs job, hr_organization_units org WHERE p.project_id = t.project_id AND t.task_id = ei.task_id AND ei.expenditure_id = e.expenditure_id AND e.incurred_by_person_id = per.person_id AND trunc(sysdate) between per.effective_start_date AND per.effective_end_date AND (per.employee_number is not null or per.npw_number is not null) AND ei.system_linkage_function = 'ST' AND ei.job_id = job.job_id (+) AND e.incurred_by_organization_id = org.organization_id

View Text - HTML Formatted

SELECT P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, P.PROJECT_TYPE
, T.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, PER.PERSON_ID
, NVL(PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER ) EMPLOYEE_NUMBER
, PER.FULL_NAME
, JOB.JOB_ID
, JOB.NAME
, ORG.ORGANIZATION_ID
, ORG.NAME
, E.EXPENDITURE_ENDING_DATE
, E.ORIG_USER_EXP_TXN_REFERENCE
, EI.EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, NVL(EI.QUANTITY
, 0)
, 0)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'OT'
, NVL(EI.QUANTITY
, 0)
, 0)
, DECODE(EI.BILLABLE_FLAG
, 'Y'
, NVL(EI.QUANTITY
, 0)
, 0)
, DECODE(EI.BILLABLE_FLAG
, 'N'
, NVL(EI.QUANTITY
, 0)
, 0)
, EI.RAW_COST
, EI.BURDEN_COST
, NVL(EI.ADJUSTED_REVENUE
, EI.RAW_REVENUE)
, EI.ACCRUED_REVENUE
, EI.BILLABLE_FLAG
, EI.DENOM_CURRENCY_CODE
, EI.DENOM_RAW_COST
, EI.DENOM_BURDENED_COST
, EI.ACCT_CURRENCY_CODE
, EI.ACCT_RATE_DATE
, EI.ACCT_RATE_TYPE
, EI.ACCT_EXCHANGE_RATE
, EI.ACCT_RAW_COST
, EI.ACCT_BURDENED_COST
, EI.PROJECT_CURRENCY_CODE
, EI.PROJECT_RATE_DATE
, EI.PROJECT_RATE_TYPE
, EI.PROJECT_EXCHANGE_RATE
, E.TRANSFER_STATUS_CODE
, E.WF_STATUS_CODE
, E.ORIG_EXP_TXN_REFERENCE1
, E.ORIG_EXP_TXN_REFERENCE2
, E.ORIG_EXP_TXN_REFERENCE3
, E.USER_BATCH_NAME
, E.OVERRIDING_APPROVER_PERSON_ID
FROM PA_PROJECTS_ALL P
, PA_TASKS T
, PA_EXPENDITURES_ALL E
, PA_EXPENDITURE_ITEMS_ALL EI
, PER_PEOPLE_F PER
, PER_JOBS JOB
, HR_ORGANIZATION_UNITS ORG
WHERE P.PROJECT_ID = T.PROJECT_ID
AND T.TASK_ID = EI.TASK_ID
AND EI.EXPENDITURE_ID = E.EXPENDITURE_ID
AND E.INCURRED_BY_PERSON_ID = PER.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND EI.SYSTEM_LINKAGE_FUNCTION = 'ST'
AND EI.JOB_ID = JOB.JOB_ID (+)
AND E.INCURRED_BY_ORGANIZATION_ID = ORG.ORGANIZATION_ID