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_GROUP_CODE HEADER_NUM PJC_FUNDING_SOURCE

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

Query

SQL_Statement
SELECT gt.patc_reserved_num_13 data_set_id, gt.source_id_1 source_header_id_1, ' ' source_header_id_2, gt.transaction_number transaction_number, gt.source_id_1 source_line_id_1, ' ' 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, gt.org_id business_unit_id, null destination_type_code, gt.document_code document_type_code, gt.transaction_source_code transaction_source_code, null transaction_subtype_code, null je_source_code, null je_category_code, NVL(gt.budget_date,gt.expenditure_item_date) budget_date, gt.gl_date accounting_date, null encumbrance_type_code, gt.denom_currency_code entered_currency, null uom_code, gt.quantity quantity, null price, DECODE(gt.System_Linkage, 'BTC', gt.Denom_Burdened_Cost, gt.Denom_Raw_Cost) ENTERED_AMOUNT, 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 gt.Expenditure_Item_Date WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N') AND prj.Currency_Conv_Date_Type_Code = 'A' ) THEN gt.GL_Date WHEN ((src.transaction_source = 'PROJECTS' OR doc.predefined_flag = 'N') AND prj.Currency_Conv_Date_Type_Code = 'P' ) THEN gt.Patc_Reserved_Date_4 /* 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(gt.System_Linkage, 'BTC', gt.Acct_Burdened_Cost, gt.Acct_Raw_Cost) ledger_amount, 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, gt.vendor_id vendor_id, gt.inventory_item_id inventory_item_id, null order_type_info, null code_combination_id, gt.project_id pjc_project_id, gt.task_id pjc_task_id, gt.patc_reserved_num_11 pjc_resource_id, gt.contract_id pjc_contract_id, null pjc_contract_line_id, null pjc_funding_allocation_id, gt.billable_flag pjc_billable_flag, gt.capitalizable_flag pjc_capitalizable_flag, gt.expenditure_type_id pjc_expenditure_type_id, gt.expenditure_item_date pjc_expenditure_item_date, gt.organization_id pjc_organization_id, gt.work_type_id pjc_work_type_id, null control_budget_id, gt.patc_reserved_num_12 header_group_code, null header_num, gt.reserved_attribute1 pjc_funding_source FROM pjc_txn_xface_gt gt, hr_organization_information_x bu, pjf_projects_all_b prj, pjf_txn_document_b doc, pjf_txn_sources_b src WHERE gt.org_id = bu.organization_id AND gt.transaction_status_code = 'P' AND prj.project_id = gt.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 = gt.transaction_source_id AND doc.transaction_source_id = src.transaction_source_id AND doc.document_id = gt.document_id AND gt.patc_reserved_char_11 = 'NOT_ATTEMPTED'

SQL_Statement

SELECT gt.patc_reserved_num_13 data_set_id,

gt.source_id_1 source_header_id_1,

' ' source_header_id_2,

gt.transaction_number transaction_number,

gt.source_id_1 source_line_id_1,

' ' 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,

gt.org_id business_unit_id,

null destination_type_code,

gt.document_code document_type_code,

gt.transaction_source_code transaction_source_code,

null transaction_subtype_code,

null je_source_code,

null je_category_code,

NVL(gt.budget_date,gt.expenditure_item_date) budget_date,

gt.gl_date accounting_date,

null encumbrance_type_code,

gt.denom_currency_code entered_currency,

null uom_code,

gt.quantity quantity,

null price,

DECODE(gt.System_Linkage, 'BTC', gt.Denom_Burdened_Cost, gt.Denom_Raw_Cost) ENTERED_AMOUNT,

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 gt.Expenditure_Item_Date

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

AND prj.Currency_Conv_Date_Type_Code = 'A' ) THEN gt.GL_Date

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

AND prj.Currency_Conv_Date_Type_Code = 'P' ) THEN gt.Patc_Reserved_Date_4 /* 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(gt.System_Linkage, 'BTC', gt.Acct_Burdened_Cost, gt.Acct_Raw_Cost) ledger_amount,

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,

gt.vendor_id vendor_id,

gt.inventory_item_id inventory_item_id,

null order_type_info,

null code_combination_id,

gt.project_id pjc_project_id,

gt.task_id pjc_task_id,

gt.patc_reserved_num_11 pjc_resource_id,

gt.contract_id pjc_contract_id,

null pjc_contract_line_id,

null pjc_funding_allocation_id,

gt.billable_flag pjc_billable_flag,

gt.capitalizable_flag pjc_capitalizable_flag,

gt.expenditure_type_id pjc_expenditure_type_id,

gt.expenditure_item_date pjc_expenditure_item_date,

gt.organization_id pjc_organization_id,

gt.work_type_id pjc_work_type_id,

null control_budget_id,

gt.patc_reserved_num_12 header_group_code,

null header_num,

gt.reserved_attribute1 pjc_funding_source

FROM pjc_txn_xface_gt gt,

hr_organization_information_x bu,

pjf_projects_all_b prj,

pjf_txn_document_b doc,

pjf_txn_sources_b src

WHERE gt.org_id = bu.organization_id

AND gt.transaction_status_code = 'P'

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

AND doc.transaction_source_id = src.transaction_source_id

AND doc.document_id = gt.document_id

AND gt.patc_reserved_char_11 = 'NOT_ATTEMPTED'