CMR_XLA_PROJECT_SOURCES_REF

Details

Schema: FUSION
Object owner: CMR
Object type: VIEW

Columns

Name
PROJECT_ID TASK_ID EXPENDITURE_TYPE_ID EXPENDITURE_ITEM_DATE EXPENDITURE_ORGANIZATION_ID WORK_TYPE_ID WORK_TYPE_NAME CONTRACT_ID CONTRACT_LINE_ID FUNDING_ALLOCATION_ID BILLABLE_FLAG CAPITALIZABLE_FLAG CONTEXT_CATEGORY USER_DEF_ATTRIBUTE1 USER_DEF_ATTRIBUTE2 USER_DEF_ATTRIBUTE3 USER_DEF_ATTRIBUTE4 USER_DEF_ATTRIBUTE5 USER_DEF_ATTRIBUTE6 USER_DEF_ATTRIBUTE7 USER_DEF_ATTRIBUTE8 USER_DEF_ATTRIBUTE9 USER_DEF_ATTRIBUTE10 RESERVED_ATTRIBUTE1 RESERVED_ATTRIBUTE2 RESERVED_ATTRIBUTE3 RESERVED_ATTRIBUTE4 RESERVED_ATTRIBUTE5 RESERVED_ATTRIBUTE6 RESERVED_ATTRIBUTE7 RESERVED_ATTRIBUTE8 RESERVED_ATTRIBUTE9 RESERVED_ATTRIBUTE10 CMR_PO_DISTRIBUTION_ID CMR_RCV_TRANSACTION_ID CST_TRANSFER_ORDER_DIST_ID PROJECT_NUMBER PROJECT_NAME TASK_NUMBER TASK_NAME EXPENDITURE_TYPE_NAME

Name

PROJECT_ID

TASK_ID

EXPENDITURE_TYPE_ID

EXPENDITURE_ITEM_DATE

EXPENDITURE_ORGANIZATION_ID

WORK_TYPE_ID

WORK_TYPE_NAME

CONTRACT_ID

CONTRACT_LINE_ID

FUNDING_ALLOCATION_ID

BILLABLE_FLAG

CAPITALIZABLE_FLAG

CONTEXT_CATEGORY

USER_DEF_ATTRIBUTE1

USER_DEF_ATTRIBUTE2

USER_DEF_ATTRIBUTE3

USER_DEF_ATTRIBUTE4

USER_DEF_ATTRIBUTE5

USER_DEF_ATTRIBUTE6

USER_DEF_ATTRIBUTE7

USER_DEF_ATTRIBUTE8

USER_DEF_ATTRIBUTE9

USER_DEF_ATTRIBUTE10

RESERVED_ATTRIBUTE1

RESERVED_ATTRIBUTE2

RESERVED_ATTRIBUTE3

RESERVED_ATTRIBUTE4

RESERVED_ATTRIBUTE5

RESERVED_ATTRIBUTE6

RESERVED_ATTRIBUTE7

RESERVED_ATTRIBUTE8

RESERVED_ATTRIBUTE9

RESERVED_ATTRIBUTE10

CMR_PO_DISTRIBUTION_ID

CMR_RCV_TRANSACTION_ID

CST_TRANSFER_ORDER_DIST_ID

PROJECT_NUMBER

PROJECT_NAME

TASK_NUMBER

TASK_NAME

EXPENDITURE_TYPE_NAME

Query

SQL_Statement
SELECT crt.pjc_project_id project_id, crt.pjc_task_id task_id, crt.pjc_expenditure_type_id expenditure_type_id, crt.pjc_expenditure_item_date expenditure_item_date, crt.pjc_organization_id expenditure_organization_id, crt.pjc_work_type_id work_type_id, crt.pjc_work_type_id work_type_name, crt.pjc_contract_id contract_id, crt.pjc_contract_line_id contract_line_id, crt.pjc_funding_allocation_id funding_allocation_id, crt.pjc_billable_flag billable_flag, crt.pjc_capitalizable_flag capitalizable_flag, crt.pjc_context_category context_category, crt.pjc_user_def_attribute1 user_def_attribute1, crt.pjc_user_def_attribute2 user_def_attribute2, crt.pjc_user_def_attribute3 user_def_attribute3, crt.pjc_user_def_attribute4 user_def_attribute4, crt.pjc_user_def_attribute5 user_def_attribute5, crt.pjc_user_def_attribute6 user_def_attribute6, crt.pjc_user_def_attribute7 user_def_attribute7, crt.pjc_user_def_attribute8 user_def_attribute8, crt.pjc_user_def_attribute9 user_def_attribute9, crt.pjc_user_def_attribute10 user_def_attribute10, crt.pjc_reserved_attribute1 reserved_attribute1, crt.pjc_reserved_attribute2 reserved_attribute2, crt.pjc_reserved_attribute3 reserved_attribute3, crt.pjc_reserved_attribute4 reserved_attribute4, crt.pjc_reserved_attribute5 reserved_attribute5, crt.pjc_reserved_attribute6 reserved_attribute6, crt.pjc_reserved_attribute7 reserved_attribute7, crt.pjc_reserved_attribute8 reserved_attribute8, crt.pjc_reserved_attribute9 reserved_attribute9, crt.pjc_reserved_attribute10 reserved_attribute10, ct.cmr_po_distribution_id cmr_po_distribution_id, ct.cmr_rcv_transaction_id cmr_rcv_transaction_id, CAST(NULL AS NUMBER(18)) cst_transfer_order_dist_id, ppa.segment1 project_number, ppa.name project_name, pt.task_number task_number, pt.task_name task_name, pet.expenditure_type_name expenditure_type_name FROM cmr_rcv_transactions crt, cmr_transactions ct, pjf_tasks_v pt, pjf_projects_all_vl ppa, pjf_exp_types_vl pet WHERE crt.pjc_project_id = ppa.project_id AND crt.pjc_task_id = pt.task_id AND crt.pjc_project_id IS NOT NULL AND crt.pjc_expenditure_type_id = pet.expenditure_type_id AND ct.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id UNION ALL SELECT crt.pjc_project_id project_id, crt.pjc_task_id task_id, crt.pjc_expenditure_type_id expenditure_type_id, crt.pjc_expenditure_item_date expenditure_item_date, crt.pjc_organization_id expenditure_organization_id, crt.pjc_work_type_id work_type_id, crt.pjc_work_type_id work_type_name, crt.pjc_contract_id contract_id, crt.pjc_contract_line_id contract_line_id, crt.pjc_funding_allocation_id funding_allocation_id, crt.pjc_billable_flag billable_flag, crt.pjc_capitalizable_flag capitalizable_flag, crt.pjc_context_category context_category, crt.pjc_user_def_attribute1 user_def_attribute1, crt.pjc_user_def_attribute2 user_def_attribute2, crt.pjc_user_def_attribute3 user_def_attribute3, crt.pjc_user_def_attribute4 user_def_attribute4, crt.pjc_user_def_attribute5 user_def_attribute5, crt.pjc_user_def_attribute6 user_def_attribute6, crt.pjc_user_def_attribute7 user_def_attribute7, crt.pjc_user_def_attribute8 user_def_attribute8, crt.pjc_user_def_attribute9 user_def_attribute9, crt.pjc_user_def_attribute10 user_def_attribute10, crt.pjc_reserved_attribute1 reserved_attribute1, crt.pjc_reserved_attribute2 reserved_attribute2, crt.pjc_reserved_attribute3 reserved_attribute3, crt.pjc_reserved_attribute4 reserved_attribute4, crt.pjc_reserved_attribute5 reserved_attribute5, crt.pjc_reserved_attribute6 reserved_attribute6, crt.pjc_reserved_attribute7 reserved_attribute7, crt.pjc_reserved_attribute8 reserved_attribute8, crt.pjc_reserved_attribute9 reserved_attribute9, crt.pjc_reserved_attribute10 reserved_attribute10, crt.cmr_po_distribution_id cmr_po_distribution_id, CAST(NULL AS NUMBER(18)) cmr_rcv_transaction_id, CAST(NULL AS NUMBER(18)) cst_transfer_order_dist_id, ppa.segment1 project_number, ppa.name project_name, pt.task_number task_number, pt.task_name task_name, pet.expenditure_type_name expenditure_type_name FROM cmr_rcv_transactions crt, pjf_tasks_v pt, pjf_projects_all_vl ppa, pjf_exp_types_vl pet WHERE crt.cmr_rcv_transaction_id = ( SELECT MAX(crt_in.cmr_rcv_transaction_id) FROM cmr_rcv_transactions crt_in WHERE crt_in.cmr_po_distribution_id = crt.cmr_po_distribution_id ) AND crt.pjc_project_id = ppa.project_id AND crt.pjc_task_id = pt.task_id AND crt.pjc_project_id IS NOT NULL AND crt.pjc_expenditure_type_id = pet.expenditure_type_id UNION ALL SELECT ctod.pjc_project_id project_id, CAST(ctod.pjc_task_id AS VARCHAR2(18) ) task_id, ctod.pjc_expenditure_type_id expenditure_type_id, ctod.pjc_expenditure_item_date expenditure_item_date, ctod.pjc_organization_id expenditure_organization_id, ctod.pjc_work_type_id work_type_id, ctod.pjc_work_type_id work_type_name, ctod.pjc_contract_id contract_id, ctod.pjc_contract_line_id contract_line_id, ctod.pjc_funding_allocation_id funding_allocation_id, ctod.pjc_billable_flag billable_flag, ctod.pjc_capitalizable_flag capitalizable_flag, ctod.pjc_context_category context_category, ctod.pjc_user_def_attribute1 user_def_attribute1, ctod.pjc_user_def_attribute2 user_def_attribute2, ctod.pjc_user_def_attribute3 user_def_attribute3, ctod.pjc_user_def_attribute4 user_def_attribute4, ctod.pjc_user_def_attribute5 user_def_attribute5, ctod.pjc_user_def_attribute6 user_def_attribute6, ctod.pjc_user_def_attribute7 user_def_attribute7, ctod.pjc_user_def_attribute8 user_def_attribute8, ctod.pjc_user_def_attribute9 user_def_attribute9, ctod.pjc_user_def_attribute10 user_def_attribute10, ctod.pjc_reserved_attribute1 reserved_attribute1, ctod.pjc_reserved_attribute2 reserved_attribute2, ctod.pjc_reserved_attribute3 reserved_attribute3, ctod.pjc_reserved_attribute4 reserved_attribute4, ctod.pjc_reserved_attribute5 reserved_attribute5, ctod.pjc_reserved_attribute6 reserved_attribute6, ctod.pjc_reserved_attribute7 reserved_attribute7, ctod.pjc_reserved_attribute8 reserved_attribute8, ctod.pjc_reserved_attribute9 reserved_attribute9, ctod.pjc_reserved_attribute10 reserved_attribute10, CAST(NULL AS NUMBER(18)) cmr_po_distribution_id, CAST(NULL AS NUMBER(18)) cmr_rcv_transaction_id, ctod.cst_transfer_order_dist_id, ppa.segment1 project_number, ppa.name project_name, pt.task_number task_number, pt.task_name task_name, pet.expenditure_type_name expenditure_type_name FROM cst_transfer_order_dists ctod, pjf_tasks_v pt, pjf_projects_all_vl ppa, pjf_exp_types_vl pet WHERE ctod.pjc_project_id = ppa.project_id AND ctod.pjc_task_id = pt.task_id AND ctod.pjc_project_id IS NOT NULL AND ctod.pjc_expenditure_type_id = pet.expenditure_type_id

SQL_Statement

SELECT

crt.pjc_project_id project_id,

crt.pjc_task_id task_id,

crt.pjc_expenditure_type_id expenditure_type_id,

crt.pjc_expenditure_item_date expenditure_item_date,

crt.pjc_organization_id expenditure_organization_id,

crt.pjc_work_type_id work_type_id,

crt.pjc_work_type_id work_type_name,

crt.pjc_contract_id contract_id,

crt.pjc_contract_line_id contract_line_id,

crt.pjc_funding_allocation_id funding_allocation_id,

crt.pjc_billable_flag billable_flag,

crt.pjc_capitalizable_flag capitalizable_flag,

crt.pjc_context_category context_category,

crt.pjc_user_def_attribute1 user_def_attribute1,

crt.pjc_user_def_attribute2 user_def_attribute2,

crt.pjc_user_def_attribute3 user_def_attribute3,

crt.pjc_user_def_attribute4 user_def_attribute4,

crt.pjc_user_def_attribute5 user_def_attribute5,

crt.pjc_user_def_attribute6 user_def_attribute6,

crt.pjc_user_def_attribute7 user_def_attribute7,

crt.pjc_user_def_attribute8 user_def_attribute8,

crt.pjc_user_def_attribute9 user_def_attribute9,

crt.pjc_user_def_attribute10 user_def_attribute10,

crt.pjc_reserved_attribute1 reserved_attribute1,

crt.pjc_reserved_attribute2 reserved_attribute2,

crt.pjc_reserved_attribute3 reserved_attribute3,

crt.pjc_reserved_attribute4 reserved_attribute4,

crt.pjc_reserved_attribute5 reserved_attribute5,

crt.pjc_reserved_attribute6 reserved_attribute6,

crt.pjc_reserved_attribute7 reserved_attribute7,

crt.pjc_reserved_attribute8 reserved_attribute8,

crt.pjc_reserved_attribute9 reserved_attribute9,

crt.pjc_reserved_attribute10 reserved_attribute10,

ct.cmr_po_distribution_id cmr_po_distribution_id,

ct.cmr_rcv_transaction_id cmr_rcv_transaction_id,

CAST(NULL AS NUMBER(18)) cst_transfer_order_dist_id,

ppa.segment1 project_number,

ppa.name project_name,

pt.task_number task_number,

pt.task_name task_name,

pet.expenditure_type_name expenditure_type_name

FROM

cmr_rcv_transactions crt,

cmr_transactions ct,

pjf_tasks_v pt,

pjf_projects_all_vl ppa,

pjf_exp_types_vl pet

WHERE

crt.pjc_project_id = ppa.project_id

AND crt.pjc_task_id = pt.task_id

AND crt.pjc_project_id IS NOT NULL

AND crt.pjc_expenditure_type_id = pet.expenditure_type_id

AND ct.cmr_rcv_transaction_id = crt.cmr_rcv_transaction_id

UNION ALL

SELECT

crt.pjc_project_id project_id,

crt.pjc_task_id task_id,

crt.pjc_expenditure_type_id expenditure_type_id,

crt.pjc_expenditure_item_date expenditure_item_date,

crt.pjc_organization_id expenditure_organization_id,

crt.pjc_work_type_id work_type_id,

crt.pjc_work_type_id work_type_name,

crt.pjc_contract_id contract_id,

crt.pjc_contract_line_id contract_line_id,

crt.pjc_funding_allocation_id funding_allocation_id,

crt.pjc_billable_flag billable_flag,

crt.pjc_capitalizable_flag capitalizable_flag,

crt.pjc_context_category context_category,

crt.pjc_user_def_attribute1 user_def_attribute1,

crt.pjc_user_def_attribute2 user_def_attribute2,

crt.pjc_user_def_attribute3 user_def_attribute3,

crt.pjc_user_def_attribute4 user_def_attribute4,

crt.pjc_user_def_attribute5 user_def_attribute5,

crt.pjc_user_def_attribute6 user_def_attribute6,

crt.pjc_user_def_attribute7 user_def_attribute7,

crt.pjc_user_def_attribute8 user_def_attribute8,

crt.pjc_user_def_attribute9 user_def_attribute9,

crt.pjc_user_def_attribute10 user_def_attribute10,

crt.pjc_reserved_attribute1 reserved_attribute1,

crt.pjc_reserved_attribute2 reserved_attribute2,

crt.pjc_reserved_attribute3 reserved_attribute3,

crt.pjc_reserved_attribute4 reserved_attribute4,

crt.pjc_reserved_attribute5 reserved_attribute5,

crt.pjc_reserved_attribute6 reserved_attribute6,

crt.pjc_reserved_attribute7 reserved_attribute7,

crt.pjc_reserved_attribute8 reserved_attribute8,

crt.pjc_reserved_attribute9 reserved_attribute9,

crt.pjc_reserved_attribute10 reserved_attribute10,

crt.cmr_po_distribution_id cmr_po_distribution_id,

CAST(NULL AS NUMBER(18)) cmr_rcv_transaction_id,

CAST(NULL AS NUMBER(18)) cst_transfer_order_dist_id,

ppa.segment1 project_number,

ppa.name project_name,

pt.task_number task_number,

pt.task_name task_name,

pet.expenditure_type_name expenditure_type_name

FROM

cmr_rcv_transactions crt,

pjf_tasks_v pt,

pjf_projects_all_vl ppa,

pjf_exp_types_vl pet

WHERE

crt.cmr_rcv_transaction_id =

(

SELECT

MAX(crt_in.cmr_rcv_transaction_id)

FROM

cmr_rcv_transactions crt_in

WHERE

crt_in.cmr_po_distribution_id = crt.cmr_po_distribution_id

)

AND crt.pjc_project_id = ppa.project_id

AND crt.pjc_task_id = pt.task_id

AND crt.pjc_project_id IS NOT NULL

AND crt.pjc_expenditure_type_id = pet.expenditure_type_id

UNION ALL

SELECT

ctod.pjc_project_id project_id,

CAST(ctod.pjc_task_id AS VARCHAR2(18) ) task_id,

ctod.pjc_expenditure_type_id expenditure_type_id,

ctod.pjc_expenditure_item_date expenditure_item_date,

ctod.pjc_organization_id expenditure_organization_id,

ctod.pjc_work_type_id work_type_id,

ctod.pjc_work_type_id work_type_name,

ctod.pjc_contract_id contract_id,

ctod.pjc_contract_line_id contract_line_id,

ctod.pjc_funding_allocation_id funding_allocation_id,

ctod.pjc_billable_flag billable_flag,

ctod.pjc_capitalizable_flag capitalizable_flag,

ctod.pjc_context_category context_category,

ctod.pjc_user_def_attribute1 user_def_attribute1,

ctod.pjc_user_def_attribute2 user_def_attribute2,

ctod.pjc_user_def_attribute3 user_def_attribute3,

ctod.pjc_user_def_attribute4 user_def_attribute4,

ctod.pjc_user_def_attribute5 user_def_attribute5,

ctod.pjc_user_def_attribute6 user_def_attribute6,

ctod.pjc_user_def_attribute7 user_def_attribute7,

ctod.pjc_user_def_attribute8 user_def_attribute8,

ctod.pjc_user_def_attribute9 user_def_attribute9,

ctod.pjc_user_def_attribute10 user_def_attribute10,

ctod.pjc_reserved_attribute1 reserved_attribute1,

ctod.pjc_reserved_attribute2 reserved_attribute2,

ctod.pjc_reserved_attribute3 reserved_attribute3,

ctod.pjc_reserved_attribute4 reserved_attribute4,

ctod.pjc_reserved_attribute5 reserved_attribute5,

ctod.pjc_reserved_attribute6 reserved_attribute6,

ctod.pjc_reserved_attribute7 reserved_attribute7,

ctod.pjc_reserved_attribute8 reserved_attribute8,

ctod.pjc_reserved_attribute9 reserved_attribute9,

ctod.pjc_reserved_attribute10 reserved_attribute10,

CAST(NULL AS NUMBER(18)) cmr_po_distribution_id,

CAST(NULL AS NUMBER(18)) cmr_rcv_transaction_id,

ctod.cst_transfer_order_dist_id,

ppa.segment1 project_number,

ppa.name project_name,

pt.task_number task_number,

pt.task_name task_name,

pet.expenditure_type_name expenditure_type_name

FROM

cst_transfer_order_dists ctod,

pjf_tasks_v pt,

pjf_projects_all_vl ppa,

pjf_exp_types_vl pet

WHERE

ctod.pjc_project_id = ppa.project_id

AND ctod.pjc_task_id = pt.task_id

AND ctod.pjc_project_id IS NOT NULL

AND ctod.pjc_expenditure_type_id = pet.expenditure_type_id