VIEW: APPS.PA_PROJ_COST_EXPEND_VIEW

Source

View Text - Preformatted

SELECT ei.expenditure_item_id , t.project_id , per.full_name , nvl(PER.EMPLOYEE_NUMBER, PER.NPW_NUMBER) , e.incurred_by_person_id , vend.vendor_id , vend.segment1 , vend.vendor_name , nvl(per.full_name, vend.vendor_name) , nvl(nvl(per.employee_number, per.npw_number), vend.segment1) , e.incurred_by_organization_id , orgtl1.name , ei.cost_job_id , job.name , decode(ei.system_linkage_function,'USG', ei.non_labor_resource||'-'||orgtl2.name, 'VI',null, 'PJ',null, 'WIP',null, 'INV',null, 'BTC',null, job.name) , ei.expenditure_type , t.task_id , t.task_number , ei.expenditure_item_date , ei.quantity , lk.meaning , ei.raw_cost , ei.burden_cost , ei.cost_distributed_flag , ei.non_labor_resource , ei.organization_id , orgtl2.name FROM pa_expenditure_items_all ei, pa_cost_distribution_lines_all cdl, pa_expenditures_all e, pa_expenditure_types et, pa_lookups lk, pa_tasks t, po_vendors vend, per_jobs job, hr_org_units_no_join org1, hr_all_organization_units_tl orgtl1, hr_org_units_no_join org2, hr_all_organization_units_tl orgtl2, per_people_f per WHERE t.task_id = ei.task_id AND ei.expenditure_id = e.expenditure_id AND ei.expenditure_type = et.expenditure_type AND e.incurred_by_person_id = per.person_id (+) AND trunc(sysdate) between per.effective_start_date (+) and per.effective_end_date (+) AND ei.expenditure_item_id = cdl.expenditure_item_id (+) AND cdl.line_num (+) = 1 AND cdl.system_reference1 = vend.vendor_id (+) AND e.incurred_by_organization_id = org1.organization_id(+) AND org1.organization_id = orgtl1.organization_id(+) AND decode(orgtl1.organization_id,null,'1',orgtl1.language) = decode(orgtl1.organization_id,null,'1',USERENV('LANG')) AND ei.organization_id = org2.organization_id (+) AND org2.organization_id = orgtl2.organization_id(+) AND decode(orgtl2.organization_id,null,'1',orgtl2.language) = decode(orgtl2.organization_id,null,'1',USERENV('LANG')) AND NVL( EI.UNIT_OF_MEASURE, et.unit_of_measure) = lk.lookup_code AND lk.lookup_type = 'UNIT' AND ei.job_id = job.job_id (+)

View Text - HTML Formatted

SELECT EI.EXPENDITURE_ITEM_ID
, T.PROJECT_ID
, PER.FULL_NAME
, NVL(PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER)
, E.INCURRED_BY_PERSON_ID
, VEND.VENDOR_ID
, VEND.SEGMENT1
, VEND.VENDOR_NAME
, NVL(PER.FULL_NAME
, VEND.VENDOR_NAME)
, NVL(NVL(PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER)
, VEND.SEGMENT1)
, E.INCURRED_BY_ORGANIZATION_ID
, ORGTL1.NAME
, EI.COST_JOB_ID
, JOB.NAME
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'USG'
, EI.NON_LABOR_RESOURCE||'-'||ORGTL2.NAME
, 'VI'
, NULL
, 'PJ'
, NULL
, 'WIP'
, NULL
, 'INV'
, NULL
, 'BTC'
, NULL
, JOB.NAME)
, EI.EXPENDITURE_TYPE
, T.TASK_ID
, T.TASK_NUMBER
, EI.EXPENDITURE_ITEM_DATE
, EI.QUANTITY
, LK.MEANING
, EI.RAW_COST
, EI.BURDEN_COST
, EI.COST_DISTRIBUTED_FLAG
, EI.NON_LABOR_RESOURCE
, EI.ORGANIZATION_ID
, ORGTL2.NAME
FROM PA_EXPENDITURE_ITEMS_ALL EI
, PA_COST_DISTRIBUTION_LINES_ALL CDL
, PA_EXPENDITURES_ALL E
, PA_EXPENDITURE_TYPES ET
, PA_LOOKUPS LK
, PA_TASKS T
, PO_VENDORS VEND
, PER_JOBS JOB
, HR_ORG_UNITS_NO_JOIN ORG1
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL1
, HR_ORG_UNITS_NO_JOIN ORG2
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL2
, PER_PEOPLE_F PER
WHERE T.TASK_ID = EI.TASK_ID
AND EI.EXPENDITURE_ID = E.EXPENDITURE_ID
AND EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND E.INCURRED_BY_PERSON_ID = PER.PERSON_ID (+)
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE (+)
AND PER.EFFECTIVE_END_DATE (+)
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID (+)
AND CDL.LINE_NUM (+) = 1
AND CDL.SYSTEM_REFERENCE1 = VEND.VENDOR_ID (+)
AND E.INCURRED_BY_ORGANIZATION_ID = ORG1.ORGANIZATION_ID(+)
AND ORG1.ORGANIZATION_ID = ORGTL1.ORGANIZATION_ID(+)
AND DECODE(ORGTL1.ORGANIZATION_ID
, NULL
, '1'
, ORGTL1.LANGUAGE) = DECODE(ORGTL1.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND EI.ORGANIZATION_ID = ORG2.ORGANIZATION_ID (+)
AND ORG2.ORGANIZATION_ID = ORGTL2.ORGANIZATION_ID(+)
AND DECODE(ORGTL2.ORGANIZATION_ID
, NULL
, '1'
, ORGTL2.LANGUAGE) = DECODE(ORGTL2.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND NVL( EI.UNIT_OF_MEASURE
, ET.UNIT_OF_MEASURE) = LK.LOOKUP_CODE
AND LK.LOOKUP_TYPE = 'UNIT'
AND EI.JOB_ID = JOB.JOB_ID (+)