VIEW: APPS.PA_PROJ_EXPEND_VIEW

Source

View Text - Preformatted

SELECT ei.expenditure_item_id , p.project_id , p.segment1 , p.name , (select meaning from pa_lookups WHERE lookup_code = ei.bill_hold_flag and lookup_type = 'BILLING HOLD') , ei.bill_hold_flag , 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) , per.person_id , e.incurred_by_organization_id , ei.override_to_organization_id , nvl(pa_expenditures_utils.GetOrgTlName(ei.override_to_organization_id), pa_expenditures_utils.GetOrgTlName(e.incurred_by_organization_id)) , nvl(ei.bill_job_id,asg1.job_id) , nvl(ei.bill_job_billing_title,job.name) , decode(ei.system_linkage_function, 'USG', ei.non_labor_resource||'-'||pa_expenditures_utils.GetOrgTlName(ei.organization_id), 'VI',null, 'PJ',null, 'WIP',null, 'INV',null, 'BTC',null, nvl(ei.bill_job_billing_title,job.name)) , ei.expenditure_type , et.expenditure_category , ei.system_linkage_function , t.task_id , t.task_number , t.task_name , ei.expenditure_item_date , ei.quantity , pa_utils4.get_unit_of_measure_m(EI.unit_of_measure , ei.expenditure_type) , ei.bill_rate , ei.bill_amount , ei.raw_cost , ei.burden_cost , nvl(ei.adjusted_revenue, ei.raw_revenue) , ei.accrued_revenue , ei.cost_distributed_flag , ei.revenue_distributed_flag , ei.billable_flag , ei.non_labor_resource , ei.organization_id , pa_expenditures_utils.GetOrgTlName(ei.organization_id) , cdl.system_reference2 , cdl.system_reference3 , asg1.primary_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_burdened_cost , ei.project_currency_code , ei.project_rate_date , ei.project_rate_type , ei.project_exchange_rate , ei.invproc_currency_code , ei.bill_trans_currency_code , ei.bill_trans_bill_amount , ei.document_header_id , ei.document_distribution_id , ei.document_payment_id , ei.document_line_number , ei.document_type , ei.document_distribution_type ,p.org_id , ei.event_num FROM pa_projects p, pa_tasks t, pa_expenditure_items_all ei, pa_expenditures_all e, pa_cost_dist_lines_all_bas cdl, pa_expenditure_types et, per_all_assignments_f asg1, per_all_people_f per, per_jobs job, po_vendors vend WHERE t.task_id = ei.task_id AND p.project_id = t.project_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 ei.expenditure_item_date between nvl(per.effective_start_date,ei.expenditure_item_date-1) and nvl(per.effective_end_date,ei.expenditure_item_date+1) AND ei.expenditure_item_id = cdl.expenditure_item_id (+) AND cdl.line_num (+) = 1 AND ei.vendor_id = vend.vendor_id (+) AND per.person_id = asg1.person_id (+) AND ei.expenditure_item_date between nvl(asg1.effective_start_date,ei.expenditure_item_date-1) and nvl(asg1.effective_end_date,ei.expenditure_item_date+1) AND asg1.job_id = job.job_id (+) AND asg1.primary_flag (+) ='Y' AND (asg1.assignment_type IN (DECODE(NVL(per.current_npw_flag,'N'),'N','E','C')) OR asg1.assignment_type IS null)

View Text - HTML Formatted

SELECT EI.EXPENDITURE_ITEM_ID
, P.PROJECT_ID
, P.SEGMENT1
, P.NAME
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_CODE = EI.BILL_HOLD_FLAG
AND LOOKUP_TYPE = 'BILLING HOLD')
, EI.BILL_HOLD_FLAG
, 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)
, PER.PERSON_ID
, E.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID
, NVL(PA_EXPENDITURES_UTILS.GETORGTLNAME(EI.OVERRIDE_TO_ORGANIZATION_ID)
, PA_EXPENDITURES_UTILS.GETORGTLNAME(E.INCURRED_BY_ORGANIZATION_ID))
, NVL(EI.BILL_JOB_ID
, ASG1.JOB_ID)
, NVL(EI.BILL_JOB_BILLING_TITLE
, JOB.NAME)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'USG'
, EI.NON_LABOR_RESOURCE||'-'||PA_EXPENDITURES_UTILS.GETORGTLNAME(EI.ORGANIZATION_ID)
, 'VI'
, NULL
, 'PJ'
, NULL
, 'WIP'
, NULL
, 'INV'
, NULL
, 'BTC'
, NULL
, NVL(EI.BILL_JOB_BILLING_TITLE
, JOB.NAME))
, EI.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, EI.SYSTEM_LINKAGE_FUNCTION
, T.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, EI.EXPENDITURE_ITEM_DATE
, EI.QUANTITY
, PA_UTILS4.GET_UNIT_OF_MEASURE_M(EI.UNIT_OF_MEASURE
, EI.EXPENDITURE_TYPE)
, EI.BILL_RATE
, EI.BILL_AMOUNT
, EI.RAW_COST
, EI.BURDEN_COST
, NVL(EI.ADJUSTED_REVENUE
, EI.RAW_REVENUE)
, EI.ACCRUED_REVENUE
, EI.COST_DISTRIBUTED_FLAG
, EI.REVENUE_DISTRIBUTED_FLAG
, EI.BILLABLE_FLAG
, EI.NON_LABOR_RESOURCE
, EI.ORGANIZATION_ID
, PA_EXPENDITURES_UTILS.GETORGTLNAME(EI.ORGANIZATION_ID)
, CDL.SYSTEM_REFERENCE2
, CDL.SYSTEM_REFERENCE3
, ASG1.PRIMARY_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_BURDENED_COST
, EI.PROJECT_CURRENCY_CODE
, EI.PROJECT_RATE_DATE
, EI.PROJECT_RATE_TYPE
, EI.PROJECT_EXCHANGE_RATE
, EI.INVPROC_CURRENCY_CODE
, EI.BILL_TRANS_CURRENCY_CODE
, EI.BILL_TRANS_BILL_AMOUNT
, EI.DOCUMENT_HEADER_ID
, EI.DOCUMENT_DISTRIBUTION_ID
, EI.DOCUMENT_PAYMENT_ID
, EI.DOCUMENT_LINE_NUMBER
, EI.DOCUMENT_TYPE
, EI.DOCUMENT_DISTRIBUTION_TYPE
, P.ORG_ID
, EI.EVENT_NUM
FROM PA_PROJECTS P
, PA_TASKS T
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL E
, PA_COST_DIST_LINES_ALL_BAS CDL
, PA_EXPENDITURE_TYPES ET
, PER_ALL_ASSIGNMENTS_F ASG1
, PER_ALL_PEOPLE_F PER
, PER_JOBS JOB
, PO_VENDORS VEND
WHERE T.TASK_ID = EI.TASK_ID
AND P.PROJECT_ID = T.PROJECT_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 EI.EXPENDITURE_ITEM_DATE BETWEEN NVL(PER.EFFECTIVE_START_DATE
, EI.EXPENDITURE_ITEM_DATE-1)
AND NVL(PER.EFFECTIVE_END_DATE
, EI.EXPENDITURE_ITEM_DATE+1)
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID (+)
AND CDL.LINE_NUM (+) = 1
AND EI.VENDOR_ID = VEND.VENDOR_ID (+)
AND PER.PERSON_ID = ASG1.PERSON_ID (+)
AND EI.EXPENDITURE_ITEM_DATE BETWEEN NVL(ASG1.EFFECTIVE_START_DATE
, EI.EXPENDITURE_ITEM_DATE-1)
AND NVL(ASG1.EFFECTIVE_END_DATE
, EI.EXPENDITURE_ITEM_DATE+1)
AND ASG1.JOB_ID = JOB.JOB_ID (+)
AND ASG1.PRIMARY_FLAG (+) ='Y'
AND (ASG1.ASSIGNMENT_TYPE IN (DECODE(NVL(PER.CURRENT_NPW_FLAG
, 'N')
, 'N'
, 'E'
, 'C')) OR ASG1.ASSIGNMENT_TYPE IS NULL)