VIEW: APPS.PA_PROJ_TRANSFERS_VIEW

Source

View Text - Preformatted

SELECT p1.project_id, p1.segment1, p1.name, t1.task_id, t1.task_number, t1.task_name, ei.expenditure_item_id, p2.project_id, p2.segment1, p2.name, t2.task_id, t2.task_name, t2.task_number, ei.transferred_from_exp_item_id, e.incurred_by_person_id, nvl(PER.EMPLOYEE_NUMBER, PER.NPW_NUMBER), per.full_name, e.incurred_by_organization_id, orgtl.name, ei.expenditure_type, ei.expenditure_item_date, ei.quantity, lk.meaning, ei.raw_cost, ei.burden_cost, nvl(ei.adjusted_revenue,ei.raw_revenue), ei.accrued_revenue , 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 , p1.org_id FROM pa_projects p1, pa_tasks t1, pa_expenditure_items_all ei, pa_expenditures_all e, pa_expenditure_types et, pa_lookups lk, pa_expenditure_items_all ei2, pa_tasks t2, pa_projects_all p2, hr_org_units_no_join org, hr_all_organization_units_tl orgtl, per_people_f per WHERE p1.project_id = t1.project_id AND t1.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 e.incurred_by_organization_id = org.organization_id (+) AND org.organization_id = orgtl.organization_id(+) AND decode(orgtl.organization_id,null,'1',orgtl.language) = decode(orgtl.organization_id,null,'1',USERENV('LANG')) and ei.transferred_from_exp_item_id = ei2.expenditure_item_id AND ei2.task_id = t2.task_id AND t2.project_id = p2.project_id AND nvl(ei.unit_of_measure, et.unit_of_measure) = lk.lookup_code AND lk.lookup_type = 'UNIT'

View Text - HTML Formatted

SELECT P1.PROJECT_ID
, P1.SEGMENT1
, P1.NAME
, T1.TASK_ID
, T1.TASK_NUMBER
, T1.TASK_NAME
, EI.EXPENDITURE_ITEM_ID
, P2.PROJECT_ID
, P2.SEGMENT1
, P2.NAME
, T2.TASK_ID
, T2.TASK_NAME
, T2.TASK_NUMBER
, EI.TRANSFERRED_FROM_EXP_ITEM_ID
, E.INCURRED_BY_PERSON_ID
, NVL(PER.EMPLOYEE_NUMBER
, PER.NPW_NUMBER)
, PER.FULL_NAME
, E.INCURRED_BY_ORGANIZATION_ID
, ORGTL.NAME
, EI.EXPENDITURE_TYPE
, EI.EXPENDITURE_ITEM_DATE
, EI.QUANTITY
, LK.MEANING
, EI.RAW_COST
, EI.BURDEN_COST
, NVL(EI.ADJUSTED_REVENUE
, EI.RAW_REVENUE)
, EI.ACCRUED_REVENUE
, 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
, P1.ORG_ID
FROM PA_PROJECTS P1
, PA_TASKS T1
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL E
, PA_EXPENDITURE_TYPES ET
, PA_LOOKUPS LK
, PA_EXPENDITURE_ITEMS_ALL EI2
, PA_TASKS T2
, PA_PROJECTS_ALL P2
, HR_ORG_UNITS_NO_JOIN ORG
, HR_ALL_ORGANIZATION_UNITS_TL ORGTL
, PER_PEOPLE_F PER
WHERE P1.PROJECT_ID = T1.PROJECT_ID
AND T1.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 E.INCURRED_BY_ORGANIZATION_ID = ORG.ORGANIZATION_ID (+)
AND ORG.ORGANIZATION_ID = ORGTL.ORGANIZATION_ID(+)
AND DECODE(ORGTL.ORGANIZATION_ID
, NULL
, '1'
, ORGTL.LANGUAGE) = DECODE(ORGTL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND EI.TRANSFERRED_FROM_EXP_ITEM_ID = EI2.EXPENDITURE_ITEM_ID
AND EI2.TASK_ID = T2.TASK_ID
AND T2.PROJECT_ID = P2.PROJECT_ID
AND NVL(EI.UNIT_OF_MEASURE
, ET.UNIT_OF_MEASURE) = LK.LOOKUP_CODE
AND LK.LOOKUP_TYPE = 'UNIT'