PJC_XCC_ENC_EXTRACT_V

Details

• Schema: FUSION

• Object owner: PJC

• Object type: VIEW

Columns

Name

DATA_SET_ID SOURCE_HEADER_ID_1 SOURCE_HEADER_ID_2 TRANSACTION_NUMBER SOURCE_LINE_ID_1 SOURCE_LINE_ID_2 SOURCE_LINE_ID_3 SOURCE_LINE_ID_4 SOURCE_LINE_ID_5 SOURCE_LINE_ID_6 LINE_GROUP_CODE LINE_NUM LEDGER_ID BUSINESS_UNIT_ID DESTINATION_TYPE_CODE DOCUMENT_TYPE_CODE TRANSACTION_SOURCE_CODE TRANSACTION_SUBTYPE_CODE JE_SOURCE_CODE JE_CATEGORY_CODE BUDGET_DATE ACCOUNTING_DATE ENCUMBRANCE_TYPE_CODE ENTERED_CURRENCY UOM_CODE QUANTITY PRICE ENTERED_AMOUNT CONVERSION_TYPE_CODE CONVERSION_DATE LEDGER_AMOUNT LIQUIDATION_DATE LIQUIDATION_QUANTITY LIQUIDATION_AMOUNT LIQUIDATION_TRANS_TYPE_CODE LIQUIDATION_LINE_ID_1 LIQUIDATION_LINE_ID_2 LIQUIDATION_LINE_ID_3 LIQUIDATION_LINE_ID_4 LIQUIDATION_LINE_ID_5 LIQUIDATION_LINE_ID_6 STATISTICAL_AMOUNT BUDGET_CCID VENDOR_ID INVENTORY_ITEM_ID ORDER_TYPE_INFO CODE_COMBINATION_ID PJC_PROJECT_ID PJC_TASK_ID PJC_RESOURCE_ID PJC_CONTRACT_ID PJC_CONTRACT_LINE_ID PJC_FUNDING_ALLOCATION_ID PJC_BILLABLE_FLAG PJC_CAPITALIZABLE_FLAG PJC_EXPENDITURE_TYPE_ID PJC_EXPENDITURE_ITEM_DATE PJC_ORGANIZATION_ID PJC_WORK_TYPE_ID CONTROL_BUDGET_ID HEADER_GROUP_CODE HEADER_NUM PJC_FUNDING_SOURCE PJC_TOP_TASK_ID PJC_TOP_RESOURCE_ID

Query

SQL_Statement

SELECT cmt.data_set_id data_set_id, to_char(cmt.Commitment_Txn_Id) source_header_id_1, ' ' source_header_id_2, to_char(cmt.Commitment_Txn_Id) transaction_number, to_char(cmt.Commitment_Txn_Id) source_line_id_1, to_char(cmt.Commitment_Txn_Id) source_line_id_2, ' ' source_line_id_3, ' ' source_line_id_4, ' ' source_line_id_5, ' ' source_line_id_6, null line_group_code, null line_num, bu.org_information3 ledger_id, cmt.BU_ID business_unit_id, null destination_type_code, doc.document_code document_type_code, src.transaction_source transaction_source_code, null transaction_subtype_code, null je_source_code, null je_category_code, cmt.expenditure_item_date budget_date, NVL(cmt.GL_DATE ,cmt.expenditure_item_date) accounting_date, null encumbrance_type_code, cmt.denom_currency_code entered_currency, null uom_code, cmt.tot_cmt_quantity quantity, /*Quantity is from lines table seeing split rules*/ null price, DECODE(cmt.System_Linkage_Function, 'BTC', NVL(cmt.Denom_Brdnd_Cost,cmt.Denom_Raw_Cost), cmt.Denom_Raw_Cost) ENTERED_AMOUNT, /*Entered amount for BC is from lines table seeing split rules*/ prj.Currency_Conv_Rate_Type conversion_type_code, CASE WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N') AND prj.Currency_Conv_Date_Type_Code = 'T' ) THEN cmt.Expenditure_Item_Date WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N') AND prj.Currency_Conv_Date_Type_Code = 'A' ) THEN cmt.GL_DATE WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N') AND prj.Currency_Conv_Date_Type_Code = 'P' ) THEN cmt.pa_date /* adl.prvdr_pa_date */ WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N') AND prj.Currency_Conv_Date_Type_Code = 'F' ) THEN prj.Currency_Conv_Date ELSE NULL END conversion_date, DECODE(cmt.System_Linkage_Function, 'BTC', NVL(cmt.Acct_Brdnd_Cost,cmt.Denom_Brdnd_Cost), NVL(cmt.Acct_Raw_Cost,cmt.Denom_Raw_Cost)) ledger_amount, /*Entered acc raw cost amount for BC is from lines table seeing split rules*/ null liquidation_date, null liquidation_quantity, null liquidation_amount, null liquidation_trans_type_code, null liquidation_line_id_1, null liquidation_line_id_2, null liquidation_line_id_3, null liquidation_line_id_4, null liquidation_line_id_5, null liquidation_line_id_6, null statistical_amount, null budget_ccid, cmt.vendor_id vendor_id, cmt.inventory_item_id inventory_item_id, null order_type_info, null code_combination_id, cmt.project_id pjc_project_id, cmt.task_id pjc_task_id, cmt.PRBS_ELEMENT_ID pjc_resource_id, cmt.contract_id pjc_contract_id, cmt.contract_line_id pjc_contract_line_id, null pjc_funding_allocation_id, cmt.billable_flag pjc_billable_flag, cmt.capitalizable_flag pjc_capitalizable_flag, cmt.expenditure_type_id pjc_expenditure_type_id, cmt.expenditure_item_date pjc_expenditure_item_date, cmt.ORGANIZATION_ID pjc_organization_id, cmt.work_type_id pjc_work_type_id, null control_budget_id, cmt.PROCESS_GROUP_ID HEADER_GROUP_CODE, cmt.Commitment_Txn_Id header_num, cmt.reserved_attribute1 pjc_funding_source, task.denorm_top_element_id pjc_top_task_id, cmt.TOP_PRBS_ELEMENT_ID pjc_top_resource_id FROM pjc_commitment_txns cmt, hr_organization_information_x bu, pjf_projects_all_b prj, pjf_txn_document_b doc, pjf_txn_sources_b src, pjf_proj_elements_b task WHERE cmt.BU_ID = bu.organization_id AND prj.project_id = cmt.project_id AND task.project_id = prj.project_id AND bu.org_information_context = 'FUN_BUSINESS_UNIT' AND TRUNC(SYSDATE) BETWEEN bu.effective_start_date and bu.effective_end_date AND src.transaction_source_id = cmt.transaction_source_id AND doc.transaction_source_id = src.transaction_source_id AND doc.document_id = cmt.document_id AND task.proj_element_id = cmt.task_id AND cmt.budgetary_control_val_status = 'NOT_ATTEMPTED' AND cmt.TRANSACTION_STATUS_CODE = 'U' AND cmt.line_type = 'L'