PJC_XCC_ADL_EXTRACT

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_NUM PJC_FUNDING_SOURCE PJC_TOP_TASK_ID PJC_TOP_RESOURCE_ID

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_NUM

PJC_FUNDING_SOURCE

PJC_TOP_TASK_ID

PJC_TOP_RESOURCE_ID

Query

SQL_Statement
SELECT awrdlines.data_set_id data_set_id, to_char(awrdlines.AWARD_DISTRIBUTION_ID) source_header_id_1, ' ' source_header_id_2, awddist.transaction_number transaction_number, to_char(awddist.AWARD_DISTRIBUTION_ID) source_line_id_1, to_char(awrdlines.AWARD_DISTRIBUTION_LINE_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, bc_line_num_sequence line_num, bu.org_information3 ledger_id, awddist.BUSINESS_UNIT_ID business_unit_id, null destination_type_code, awddist.document_code document_type_code, awddist.transaction_source_code transaction_source_code, null transaction_subtype_code, null je_source_code, null je_category_code, NVL(awddist.budget_date,awddist.expenditure_item_date) budget_date, NVL(awddist.PRVDR_GL_DATE ,awddist.expenditure_item_date) accounting_date, null encumbrance_type_code, awddist.denom_currency_code entered_currency, null uom_code, awrdlines.quantity quantity, /Quantity is from lines table seeing split rules/ null price, DECODE(awddist.System_Linkage, 'BTC', NVL(awrdlines.Denom_Burdened_Cost,awrdlines.Denom_Raw_Cost), awrdlines.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 awddist.Expenditure_Item_Date WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N') AND prj.Currency_Conv_Date_Type_Code = 'A' ) THEN awddist.PRVDR_GL_DATE WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N') AND prj.Currency_Conv_Date_Type_Code = 'P' ) THEN awddist.prvdr_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(awddist.System_Linkage, 'BTC', NVL(awrdlines.Acct_Burdened_Cost,awrdlines.Denom_Burdened_Cost), NVL(awrdlines.Acct_Raw_Cost,awrdlines.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, awddist.vendor_id vendor_id, awddist.inventory_item_id inventory_item_id, null order_type_info, null code_combination_id, awddist.project_id pjc_project_id, awddist.task_id pjc_task_id, awrdlines.PRBS_ELEMENT_ID pjc_resource_id, awrdlines.contract_id pjc_contract_id, null pjc_contract_line_id, null pjc_funding_allocation_id, awddist.billable_flag pjc_billable_flag, awddist.capitalizable_flag pjc_capitalizable_flag, awddist.expenditure_type_id pjc_expenditure_type_id, awddist.expenditure_item_date pjc_expenditure_item_date, awddist.EXPENDITURE_ORGANIZATION_ID pjc_organization_id, awddist.work_type_id pjc_work_type_id, null control_budget_id, null header_num, awrdlines.FUNDING_SOURCE_ID pjc_funding_source, task.denorm_top_element_id pjc_top_task_id, awrdlines.top_resource_id pjc_top_resource_id FROM pjc_award_distribution_lines awrdlines, pjc_award_distributions awddist, 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 awddist.BUSINESS_UNIT_ID = bu.organization_id and awddist.AWARD_DISTRIBUTION_ID = awrdlines.AWARD_DISTRIBUTION_ID AND prj.project_id = awddist.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 = awddist.transaction_source_id AND doc.transaction_source_id = src.transaction_source_id AND doc.document_id = awddist.document_id AND task.proj_element_id = awddist.task_id AND awrdlines.budgetary_control_val_status = 'NOT_ATTEMPTED'

SQL_Statement

SELECT awrdlines.data_set_id data_set_id,

to_char(awrdlines.AWARD_DISTRIBUTION_ID) source_header_id_1,

' ' source_header_id_2,

awddist.transaction_number transaction_number,

to_char(awddist.AWARD_DISTRIBUTION_ID) source_line_id_1,

to_char(awrdlines.AWARD_DISTRIBUTION_LINE_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,

bc_line_num_sequence line_num,

bu.org_information3 ledger_id,

awddist.BUSINESS_UNIT_ID business_unit_id,

null destination_type_code,

awddist.document_code document_type_code,

awddist.transaction_source_code transaction_source_code,

null transaction_subtype_code,

null je_source_code,

null je_category_code,

NVL(awddist.budget_date,awddist.expenditure_item_date) budget_date,

NVL(awddist.PRVDR_GL_DATE ,awddist.expenditure_item_date) accounting_date,

null encumbrance_type_code,

awddist.denom_currency_code entered_currency,

null uom_code,

awrdlines.quantity quantity, /*Quantity is from lines table seeing split rules*/

null price,

DECODE(awddist.System_Linkage, 'BTC', NVL(awrdlines.Denom_Burdened_Cost,awrdlines.Denom_Raw_Cost), awrdlines.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 awddist.Expenditure_Item_Date

WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N')

AND prj.Currency_Conv_Date_Type_Code = 'A' ) THEN awddist.PRVDR_GL_DATE

WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N')

AND prj.Currency_Conv_Date_Type_Code = 'P' ) THEN awddist.prvdr_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(awddist.System_Linkage, 'BTC', NVL(awrdlines.Acct_Burdened_Cost,awrdlines.Denom_Burdened_Cost), NVL(awrdlines.Acct_Raw_Cost,awrdlines.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,

awddist.vendor_id vendor_id,

awddist.inventory_item_id inventory_item_id,

null order_type_info,

null code_combination_id,

awddist.project_id pjc_project_id,

awddist.task_id pjc_task_id,

awrdlines.PRBS_ELEMENT_ID pjc_resource_id,

awrdlines.contract_id pjc_contract_id,

null pjc_contract_line_id,

null pjc_funding_allocation_id,

awddist.billable_flag pjc_billable_flag,

awddist.capitalizable_flag pjc_capitalizable_flag,

awddist.expenditure_type_id pjc_expenditure_type_id,

awddist.expenditure_item_date pjc_expenditure_item_date,

awddist.EXPENDITURE_ORGANIZATION_ID pjc_organization_id,

awddist.work_type_id pjc_work_type_id,

null control_budget_id,

null header_num,

awrdlines.FUNDING_SOURCE_ID pjc_funding_source,

task.denorm_top_element_id pjc_top_task_id,

awrdlines.top_resource_id pjc_top_resource_id

FROM pjc_award_distribution_lines awrdlines,

pjc_award_distributions awddist,

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

awddist.BUSINESS_UNIT_ID = bu.organization_id

and awddist.AWARD_DISTRIBUTION_ID = awrdlines.AWARD_DISTRIBUTION_ID

AND prj.project_id = awddist.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 = awddist.transaction_source_id

AND doc.transaction_source_id = src.transaction_source_id

AND doc.document_id = awddist.document_id

AND task.proj_element_id = awddist.task_id

AND awrdlines.budgetary_control_val_status = 'NOT_ATTEMPTED'