VIEW: APPS.PA_TRANSACTION_INTERFACE_V

Source

View Text - Preformatted

SELECT TI.ROWID ,TI.TRANSACTION_SOURCE ,TI.BATCH_NAME ,TI.EXPENDITURE_ENDING_DATE ,TI.EMPLOYEE_NUMBER ,TI.ORGANIZATION_NAME ,TI.EXPENDITURE_ITEM_DATE ,TI.PROJECT_NUMBER ,TI.TASK_NUMBER ,TI.EXPENDITURE_TYPE ,TI.NON_LABOR_RESOURCE ,TI.NON_LABOR_RESOURCE_ORG_NAME ,TI.QUANTITY ,TI.RAW_COST ,TI.EXPENDITURE_COMMENT ,TI.TRANSACTION_STATUS_CODE ,TI.TRANSACTION_REJECTION_CODE ,TI.EXPENDITURE_ID ,TI.ORIG_TRANSACTION_REFERENCE ,TI.ATTRIBUTE_CATEGORY ,TI.ATTRIBUTE1 ,TI.ATTRIBUTE2 ,TI.ATTRIBUTE3 ,TI.ATTRIBUTE4 ,TI.ATTRIBUTE5 ,TI.ATTRIBUTE6 ,TI.ATTRIBUTE7 ,TI.ATTRIBUTE8 ,TI.ATTRIBUTE9 ,TI.ATTRIBUTE10 ,TI.RAW_COST_RATE ,TI.INTERFACE_ID ,TI.UNMATCHED_NEGATIVE_TXN_FLAG ,TI.EXPENDITURE_ITEM_ID ,TI.ORG_ID ,TI.DR_CODE_COMBINATION_ID ,TI.CR_CODE_COMBINATION_ID ,TI.CDL_SYSTEM_REFERENCE1 ,TI.CDL_SYSTEM_REFERENCE2 ,TI.CDL_SYSTEM_REFERENCE3 ,TI.GL_DATE ,TI.BURDENED_COST ,TI.BURDENED_COST_RATE ,TI.SYSTEM_LINKAGE ,TI.USER_TRANSACTION_SOURCE ,TI.TXN_INTERFACE_ID ,L.MEANING ,TI.LAST_UPDATED_BY ,TI.LAST_UPDATE_DATE ,TI.CREATED_BY ,TI.CREATION_DATE ,TI.receipt_currency_amount ,TI.receipt_currency_code ,TI.receipt_exchange_rate ,TI.denom_currency_code ,TI.denom_raw_cost ,TI.denom_burdened_cost ,TI.acct_rate_date ,TI.acct_rate_type ,TI.acct_exchange_rate ,TI.acct_raw_cost ,TI.acct_burdened_cost ,TI.acct_exchange_rounding_limit ,TI.project_currency_code ,TI.project_rate_date ,TI.project_rate_type ,TI.project_exchange_rate ,TI.ORIG_EXP_TXN_REFERENCE1 ,TI.ORIG_USER_EXP_TXN_REFERENCE ,TI.VENDOR_NUMBER ,TI.ORIG_EXP_TXN_REFERENCE2 ,TI.ORIG_EXP_TXN_REFERENCE3 ,TI.OVERRIDE_TO_ORGANIZATION_NAME ,TI.REVERSED_ORIG_TXN_REFERENCE ,TI.BILLABLE_FLAG ,TI.PERSON_BUSINESS_GROUP_NAME ,TI.PROJFUNC_CURRENCY_CODE ,TI.PROJFUNC_COST_RATE_TYPE ,TI.PROJFUNC_COST_RATE_DATE ,TI.PROJFUNC_COST_EXCHANGE_RATE ,TI.PROJECT_RAW_COST ,TI.PROJECT_BURDENED_COST ,TI.ASSIGNMENT_NAME ,TI.WORK_TYPE_NAME ,TI.cdl_system_reference4 ,TI.accrual_flag ,TI.PROJECT_ID ,TI.TASK_ID ,TI.PERSON_ID ,TI.ORGANIZATION_ID ,TI.NON_LABOR_RESOURCE_ORG_ID ,TI.VENDOR_ID ,TI.OVERRIDE_TO_ORGANIZATION_ID ,TI.ASSIGNMENT_ID ,TI.WORK_TYPE_ID ,TI.PERSON_BUSINESS_GROUP_ID ,TI.PO_NUMBER ,TI.PO_HEADER_ID ,TI.PO_LINE_NUM ,TI.PO_LINE_ID ,TI.PERSON_TYPE ,TI.PO_PRICE_TYPE ,TI.INVENTORY_ITEM_ID ,TI.WIP_RESOURCE_ID ,TI.UNIT_OF_MEASURE ,TI.cdl_system_reference5 ,TI.adjusted_expenditure_item_id ,TI.adjusted_txn_interface_id ,TI.fc_document_type ,TI.document_type ,TI.document_distribution_type ,TI.si_assets_addition_flag ,TI.sc_xfer_code From pa_transaction_interface TI, PA_LOOKUPS L WHERE transaction_status_code IN ( 'R' , 'P', 'PO', 'PR') and l.lookup_type in ( 'TRANSACTION REJECTION REASON', 'FC_RESULT_CODE','COST DIST REJECTION CODE', 'INVOICE_CURRENCY', 'TRANSACTION USER REJ REASON') and l.lookup_code = TI.transaction_rejection_code UNION SELECT TII.ROWID ,TII.TRANSACTION_SOURCE ,TII.BATCH_NAME ,TII.EXPENDITURE_ENDING_DATE ,TII.EMPLOYEE_NUMBER ,TII.ORGANIZATION_NAME ,TII.EXPENDITURE_ITEM_DATE ,TII.PROJECT_NUMBER ,TII.TASK_NUMBER ,TII.EXPENDITURE_TYPE ,TII.NON_LABOR_RESOURCE ,TII.NON_LABOR_RESOURCE_ORG_NAME ,TII.QUANTITY ,TII.RAW_COST ,TII.EXPENDITURE_COMMENT ,TII.TRANSACTION_STATUS_CODE ,TII.TRANSACTION_REJECTION_CODE ,TII.EXPENDITURE_ID ,TII.ORIG_TRANSACTION_REFERENCE ,TII.ATTRIBUTE_CATEGORY ,TII.ATTRIBUTE1 ,TII.ATTRIBUTE2 ,TII.ATTRIBUTE3 ,TII.ATTRIBUTE4 ,TII.ATTRIBUTE5 ,TII.ATTRIBUTE6 ,TII.ATTRIBUTE7 ,TII.ATTRIBUTE8 ,TII.ATTRIBUTE9 ,TII.ATTRIBUTE10 ,TII.RAW_COST_RATE ,TII.INTERFACE_ID ,TII.UNMATCHED_NEGATIVE_TXN_FLAG ,TII.EXPENDITURE_ITEM_ID ,TII.ORG_ID ,TII.DR_CODE_COMBINATION_ID ,TII.CR_CODE_COMBINATION_ID ,TII.CDL_SYSTEM_REFERENCE1 ,TII.CDL_SYSTEM_REFERENCE2 ,TII.CDL_SYSTEM_REFERENCE3 ,TII.GL_DATE ,TII.BURDENED_COST ,TII.BURDENED_COST_RATE ,TII.SYSTEM_LINKAGE ,TII.USER_TRANSACTION_SOURCE ,TII.TXN_INTERFACE_ID ,NULL ,TII.LAST_UPDATED_BY ,TII.LAST_UPDATE_DATE ,TII.CREATED_BY ,TII.CREATION_DATE ,TII.receipt_currency_amount ,TII.receipt_currency_code ,TII.receipt_exchange_rate ,TII.denom_currency_code ,TII.denom_raw_cost ,TII.denom_burdened_cost ,TII.acct_rate_date ,TII.acct_rate_type ,TII.acct_exchange_rate ,TII.acct_raw_cost ,TII.acct_burdened_cost ,TII.acct_exchange_rounding_limit ,TII.project_currency_code ,TII.project_rate_date ,TII.project_rate_type ,TII.project_exchange_rate ,TII.ORIG_EXP_TXN_REFERENCE1 ,TII.ORIG_USER_EXP_TXN_REFERENCE ,TII.VENDOR_NUMBER ,TII.ORIG_EXP_TXN_REFERENCE2 ,TII.ORIG_EXP_TXN_REFERENCE3 ,TII.OVERRIDE_TO_ORGANIZATION_NAME ,TII.REVERSED_ORIG_TXN_REFERENCE ,TII.BILLABLE_FLAG ,TII.PERSON_BUSINESS_GROUP_NAME ,TII.PROJFUNC_CURRENCY_CODE ,TII.PROJFUNC_COST_RATE_TYPE ,TII.PROJFUNC_COST_RATE_DATE ,TII.PROJFUNC_COST_EXCHANGE_RATE ,TII.PROJECT_RAW_COST ,TII.PROJECT_BURDENED_COST ,TII.ASSIGNMENT_NAME ,TII.WORK_TYPE_NAME ,TII.cdl_system_reference4 ,TII.accrual_flag ,TII.PROJECT_ID ,TII.TASK_ID ,TII.PERSON_ID ,TII.ORGANIZATION_ID ,TII.NON_LABOR_RESOURCE_ORG_ID ,TII.VENDOR_ID ,TII.OVERRIDE_TO_ORGANIZATION_ID ,TII.ASSIGNMENT_ID ,TII.WORK_TYPE_ID ,TII.PERSON_BUSINESS_GROUP_ID ,TII.PO_NUMBER ,TII.PO_HEADER_ID ,TII.PO_LINE_NUM ,TII.PO_LINE_ID ,TII.PERSON_TYPE ,TII.PO_PRICE_TYPE ,TII.INVENTORY_ITEM_ID ,TII.WIP_RESOURCE_ID ,TII.UNIT_OF_MEASURE ,TII.cdl_system_reference5 ,TII.adjusted_expenditure_item_id ,TII.adjusted_txn_interface_id ,TII.fc_document_type ,TII.document_type ,TII.document_distribution_type ,TII.si_assets_addition_flag ,TII.sc_xfer_code FROM pa_transaction_interface TII WHERE TII.transaction_rejection_code IS NULL AND tii.transaction_status_code IN ( 'R' , 'P', 'PO', 'PR') UNION SELECT TI.ROWID ,TI.TRANSACTION_SOURCE ,TI.BATCH_NAME ,TI.EXPENDITURE_ENDING_DATE ,TI.EMPLOYEE_NUMBER ,TI.ORGANIZATION_NAME ,TI.EXPENDITURE_ITEM_DATE ,TI.PROJECT_NUMBER ,TI.TASK_NUMBER ,TI.EXPENDITURE_TYPE ,TI.NON_LABOR_RESOURCE ,TI.NON_LABOR_RESOURCE_ORG_NAME ,TI.QUANTITY ,TI.RAW_COST ,TI.EXPENDITURE_COMMENT ,TI.TRANSACTION_STATUS_CODE ,TI.TRANSACTION_REJECTION_CODE ,TI.EXPENDITURE_ID ,TI.ORIG_TRANSACTION_REFERENCE ,TI.ATTRIBUTE_CATEGORY ,TI.ATTRIBUTE1 ,TI.ATTRIBUTE2 ,TI.ATTRIBUTE3 ,TI.ATTRIBUTE4 ,TI.ATTRIBUTE5 ,TI.ATTRIBUTE6 ,TI.ATTRIBUTE7 ,TI.ATTRIBUTE8 ,TI.ATTRIBUTE9 ,TI.ATTRIBUTE10 ,TI.RAW_COST_RATE ,TI.INTERFACE_ID ,TI.UNMATCHED_NEGATIVE_TXN_FLAG ,TI.EXPENDITURE_ITEM_ID ,TI.ORG_ID ,TI.DR_CODE_COMBINATION_ID ,TI.CR_CODE_COMBINATION_ID ,TI.CDL_SYSTEM_REFERENCE1 ,TI.CDL_SYSTEM_REFERENCE2 ,TI.CDL_SYSTEM_REFERENCE3 ,TI.GL_DATE ,TI.BURDENED_COST ,TI.BURDENED_COST_RATE ,TI.SYSTEM_LINKAGE ,TI.USER_TRANSACTION_SOURCE ,TI.TXN_INTERFACE_ID ,FNM.MESSAGE_TEXT ,TI.LAST_UPDATED_BY ,TI.LAST_UPDATE_DATE ,TI.CREATED_BY ,TI.CREATION_DATE ,TI.receipt_currency_amount ,TI.receipt_currency_code ,TI.receipt_exchange_rate ,TI.denom_currency_code ,TI.denom_raw_cost ,TI.denom_burdened_cost ,TI.acct_rate_date ,TI.acct_rate_type ,TI.acct_exchange_rate ,TI.acct_raw_cost ,TI.acct_burdened_cost ,TI.acct_exchange_rounding_limit ,TI.project_currency_code ,TI.project_rate_date ,TI.project_rate_type ,TI.project_exchange_rate ,TI.ORIG_EXP_TXN_REFERENCE1 ,TI.ORIG_USER_EXP_TXN_REFERENCE ,TI.VENDOR_NUMBER ,TI.ORIG_EXP_TXN_REFERENCE2 ,TI.ORIG_EXP_TXN_REFERENCE3 ,TI.OVERRIDE_TO_ORGANIZATION_NAME ,TI.REVERSED_ORIG_TXN_REFERENCE ,TI.BILLABLE_FLAG ,TI.PERSON_BUSINESS_GROUP_NAME ,TI.PROJFUNC_CURRENCY_CODE ,TI.PROJFUNC_COST_RATE_TYPE ,TI.PROJFUNC_COST_RATE_DATE ,TI.PROJFUNC_COST_EXCHANGE_RATE ,TI.PROJECT_RAW_COST ,TI.PROJECT_BURDENED_COST ,TI.ASSIGNMENT_NAME ,TI.WORK_TYPE_NAME ,TI.cdl_system_reference4 ,TI.accrual_flag ,TI.PROJECT_ID ,TI.TASK_ID ,TI.PERSON_ID ,TI.ORGANIZATION_ID ,TI.NON_LABOR_RESOURCE_ORG_ID ,TI.VENDOR_ID ,TI.OVERRIDE_TO_ORGANIZATION_ID ,TI.ASSIGNMENT_ID ,TI.WORK_TYPE_ID ,TI.PERSON_BUSINESS_GROUP_ID ,TI.PO_NUMBER ,TI.PO_HEADER_ID ,TI.PO_LINE_NUM ,TI.PO_LINE_ID ,TI.PERSON_TYPE ,TI.PO_PRICE_TYPE ,TI.INVENTORY_ITEM_ID ,TI.WIP_RESOURCE_ID ,TI.UNIT_OF_MEASURE ,TI.cdl_system_reference5 ,TI.adjusted_expenditure_item_id ,TI.adjusted_txn_interface_id ,TI.fc_document_type ,TI.document_type ,TI.document_distribution_type ,TI.si_assets_addition_flag ,TI.sc_xfer_code FROM pa_transaction_interface TI, FND_NEW_MESSAGES FNM WHERE ti.transaction_status_code IN ( 'R' , 'P', 'PO', 'PR') and FNM.message_name = TI.transaction_rejection_code and NOT EXISTS ( SELECT meaning FROM PA_LOOKUPS L2 where L2.lookup_code=TI.transaction_rejection_code and l2.lookup_type in ( 'TRANSACTION REJECTION REASON', 'FC_RESULT_CODE','COST DIST REJECTION CODE', 'INVOICE_CURRENCY', 'TRANSACTION USER REJ REASON') ) AND fnm.application_id = fnd_global.resp_appl_id() AND fnm.application_id in (fnd_global.resp_appl_id(),275) AND fnm.language_code = userenv('lang')

View Text - HTML Formatted

SELECT TI.ROWID
, TI.TRANSACTION_SOURCE
, TI.BATCH_NAME
, TI.EXPENDITURE_ENDING_DATE
, TI.EMPLOYEE_NUMBER
, TI.ORGANIZATION_NAME
, TI.EXPENDITURE_ITEM_DATE
, TI.PROJECT_NUMBER
, TI.TASK_NUMBER
, TI.EXPENDITURE_TYPE
, TI.NON_LABOR_RESOURCE
, TI.NON_LABOR_RESOURCE_ORG_NAME
, TI.QUANTITY
, TI.RAW_COST
, TI.EXPENDITURE_COMMENT
, TI.TRANSACTION_STATUS_CODE
, TI.TRANSACTION_REJECTION_CODE
, TI.EXPENDITURE_ID
, TI.ORIG_TRANSACTION_REFERENCE
, TI.ATTRIBUTE_CATEGORY
, TI.ATTRIBUTE1
, TI.ATTRIBUTE2
, TI.ATTRIBUTE3
, TI.ATTRIBUTE4
, TI.ATTRIBUTE5
, TI.ATTRIBUTE6
, TI.ATTRIBUTE7
, TI.ATTRIBUTE8
, TI.ATTRIBUTE9
, TI.ATTRIBUTE10
, TI.RAW_COST_RATE
, TI.INTERFACE_ID
, TI.UNMATCHED_NEGATIVE_TXN_FLAG
, TI.EXPENDITURE_ITEM_ID
, TI.ORG_ID
, TI.DR_CODE_COMBINATION_ID
, TI.CR_CODE_COMBINATION_ID
, TI.CDL_SYSTEM_REFERENCE1
, TI.CDL_SYSTEM_REFERENCE2
, TI.CDL_SYSTEM_REFERENCE3
, TI.GL_DATE
, TI.BURDENED_COST
, TI.BURDENED_COST_RATE
, TI.SYSTEM_LINKAGE
, TI.USER_TRANSACTION_SOURCE
, TI.TXN_INTERFACE_ID
, L.MEANING
, TI.LAST_UPDATED_BY
, TI.LAST_UPDATE_DATE
, TI.CREATED_BY
, TI.CREATION_DATE
, TI.RECEIPT_CURRENCY_AMOUNT
, TI.RECEIPT_CURRENCY_CODE
, TI.RECEIPT_EXCHANGE_RATE
, TI.DENOM_CURRENCY_CODE
, TI.DENOM_RAW_COST
, TI.DENOM_BURDENED_COST
, TI.ACCT_RATE_DATE
, TI.ACCT_RATE_TYPE
, TI.ACCT_EXCHANGE_RATE
, TI.ACCT_RAW_COST
, TI.ACCT_BURDENED_COST
, TI.ACCT_EXCHANGE_ROUNDING_LIMIT
, TI.PROJECT_CURRENCY_CODE
, TI.PROJECT_RATE_DATE
, TI.PROJECT_RATE_TYPE
, TI.PROJECT_EXCHANGE_RATE
, TI.ORIG_EXP_TXN_REFERENCE1
, TI.ORIG_USER_EXP_TXN_REFERENCE
, TI.VENDOR_NUMBER
, TI.ORIG_EXP_TXN_REFERENCE2
, TI.ORIG_EXP_TXN_REFERENCE3
, TI.OVERRIDE_TO_ORGANIZATION_NAME
, TI.REVERSED_ORIG_TXN_REFERENCE
, TI.BILLABLE_FLAG
, TI.PERSON_BUSINESS_GROUP_NAME
, TI.PROJFUNC_CURRENCY_CODE
, TI.PROJFUNC_COST_RATE_TYPE
, TI.PROJFUNC_COST_RATE_DATE
, TI.PROJFUNC_COST_EXCHANGE_RATE
, TI.PROJECT_RAW_COST
, TI.PROJECT_BURDENED_COST
, TI.ASSIGNMENT_NAME
, TI.WORK_TYPE_NAME
, TI.CDL_SYSTEM_REFERENCE4
, TI.ACCRUAL_FLAG
, TI.PROJECT_ID
, TI.TASK_ID
, TI.PERSON_ID
, TI.ORGANIZATION_ID
, TI.NON_LABOR_RESOURCE_ORG_ID
, TI.VENDOR_ID
, TI.OVERRIDE_TO_ORGANIZATION_ID
, TI.ASSIGNMENT_ID
, TI.WORK_TYPE_ID
, TI.PERSON_BUSINESS_GROUP_ID
, TI.PO_NUMBER
, TI.PO_HEADER_ID
, TI.PO_LINE_NUM
, TI.PO_LINE_ID
, TI.PERSON_TYPE
, TI.PO_PRICE_TYPE
, TI.INVENTORY_ITEM_ID
, TI.WIP_RESOURCE_ID
, TI.UNIT_OF_MEASURE
, TI.CDL_SYSTEM_REFERENCE5
, TI.ADJUSTED_EXPENDITURE_ITEM_ID
, TI.ADJUSTED_TXN_INTERFACE_ID
, TI.FC_DOCUMENT_TYPE
, TI.DOCUMENT_TYPE
, TI.DOCUMENT_DISTRIBUTION_TYPE
, TI.SI_ASSETS_ADDITION_FLAG
, TI.SC_XFER_CODE
FROM PA_TRANSACTION_INTERFACE TI
, PA_LOOKUPS L
WHERE TRANSACTION_STATUS_CODE IN ( 'R'
, 'P'
, 'PO'
, 'PR')
AND L.LOOKUP_TYPE IN ( 'TRANSACTION REJECTION REASON'
, 'FC_RESULT_CODE'
, 'COST DIST REJECTION CODE'
, 'INVOICE_CURRENCY'
, 'TRANSACTION USER REJ REASON')
AND L.LOOKUP_CODE = TI.TRANSACTION_REJECTION_CODE UNION SELECT TII.ROWID
, TII.TRANSACTION_SOURCE
, TII.BATCH_NAME
, TII.EXPENDITURE_ENDING_DATE
, TII.EMPLOYEE_NUMBER
, TII.ORGANIZATION_NAME
, TII.EXPENDITURE_ITEM_DATE
, TII.PROJECT_NUMBER
, TII.TASK_NUMBER
, TII.EXPENDITURE_TYPE
, TII.NON_LABOR_RESOURCE
, TII.NON_LABOR_RESOURCE_ORG_NAME
, TII.QUANTITY
, TII.RAW_COST
, TII.EXPENDITURE_COMMENT
, TII.TRANSACTION_STATUS_CODE
, TII.TRANSACTION_REJECTION_CODE
, TII.EXPENDITURE_ID
, TII.ORIG_TRANSACTION_REFERENCE
, TII.ATTRIBUTE_CATEGORY
, TII.ATTRIBUTE1
, TII.ATTRIBUTE2
, TII.ATTRIBUTE3
, TII.ATTRIBUTE4
, TII.ATTRIBUTE5
, TII.ATTRIBUTE6
, TII.ATTRIBUTE7
, TII.ATTRIBUTE8
, TII.ATTRIBUTE9
, TII.ATTRIBUTE10
, TII.RAW_COST_RATE
, TII.INTERFACE_ID
, TII.UNMATCHED_NEGATIVE_TXN_FLAG
, TII.EXPENDITURE_ITEM_ID
, TII.ORG_ID
, TII.DR_CODE_COMBINATION_ID
, TII.CR_CODE_COMBINATION_ID
, TII.CDL_SYSTEM_REFERENCE1
, TII.CDL_SYSTEM_REFERENCE2
, TII.CDL_SYSTEM_REFERENCE3
, TII.GL_DATE
, TII.BURDENED_COST
, TII.BURDENED_COST_RATE
, TII.SYSTEM_LINKAGE
, TII.USER_TRANSACTION_SOURCE
, TII.TXN_INTERFACE_ID
, NULL
, TII.LAST_UPDATED_BY
, TII.LAST_UPDATE_DATE
, TII.CREATED_BY
, TII.CREATION_DATE
, TII.RECEIPT_CURRENCY_AMOUNT
, TII.RECEIPT_CURRENCY_CODE
, TII.RECEIPT_EXCHANGE_RATE
, TII.DENOM_CURRENCY_CODE
, TII.DENOM_RAW_COST
, TII.DENOM_BURDENED_COST
, TII.ACCT_RATE_DATE
, TII.ACCT_RATE_TYPE
, TII.ACCT_EXCHANGE_RATE
, TII.ACCT_RAW_COST
, TII.ACCT_BURDENED_COST
, TII.ACCT_EXCHANGE_ROUNDING_LIMIT
, TII.PROJECT_CURRENCY_CODE
, TII.PROJECT_RATE_DATE
, TII.PROJECT_RATE_TYPE
, TII.PROJECT_EXCHANGE_RATE
, TII.ORIG_EXP_TXN_REFERENCE1
, TII.ORIG_USER_EXP_TXN_REFERENCE
, TII.VENDOR_NUMBER
, TII.ORIG_EXP_TXN_REFERENCE2
, TII.ORIG_EXP_TXN_REFERENCE3
, TII.OVERRIDE_TO_ORGANIZATION_NAME
, TII.REVERSED_ORIG_TXN_REFERENCE
, TII.BILLABLE_FLAG
, TII.PERSON_BUSINESS_GROUP_NAME
, TII.PROJFUNC_CURRENCY_CODE
, TII.PROJFUNC_COST_RATE_TYPE
, TII.PROJFUNC_COST_RATE_DATE
, TII.PROJFUNC_COST_EXCHANGE_RATE
, TII.PROJECT_RAW_COST
, TII.PROJECT_BURDENED_COST
, TII.ASSIGNMENT_NAME
, TII.WORK_TYPE_NAME
, TII.CDL_SYSTEM_REFERENCE4
, TII.ACCRUAL_FLAG
, TII.PROJECT_ID
, TII.TASK_ID
, TII.PERSON_ID
, TII.ORGANIZATION_ID
, TII.NON_LABOR_RESOURCE_ORG_ID
, TII.VENDOR_ID
, TII.OVERRIDE_TO_ORGANIZATION_ID
, TII.ASSIGNMENT_ID
, TII.WORK_TYPE_ID
, TII.PERSON_BUSINESS_GROUP_ID
, TII.PO_NUMBER
, TII.PO_HEADER_ID
, TII.PO_LINE_NUM
, TII.PO_LINE_ID
, TII.PERSON_TYPE
, TII.PO_PRICE_TYPE
, TII.INVENTORY_ITEM_ID
, TII.WIP_RESOURCE_ID
, TII.UNIT_OF_MEASURE
, TII.CDL_SYSTEM_REFERENCE5
, TII.ADJUSTED_EXPENDITURE_ITEM_ID
, TII.ADJUSTED_TXN_INTERFACE_ID
, TII.FC_DOCUMENT_TYPE
, TII.DOCUMENT_TYPE
, TII.DOCUMENT_DISTRIBUTION_TYPE
, TII.SI_ASSETS_ADDITION_FLAG
, TII.SC_XFER_CODE
FROM PA_TRANSACTION_INTERFACE TII
WHERE TII.TRANSACTION_REJECTION_CODE IS NULL
AND TII.TRANSACTION_STATUS_CODE IN ( 'R'
, 'P'
, 'PO'
, 'PR') UNION SELECT TI.ROWID
, TI.TRANSACTION_SOURCE
, TI.BATCH_NAME
, TI.EXPENDITURE_ENDING_DATE
, TI.EMPLOYEE_NUMBER
, TI.ORGANIZATION_NAME
, TI.EXPENDITURE_ITEM_DATE
, TI.PROJECT_NUMBER
, TI.TASK_NUMBER
, TI.EXPENDITURE_TYPE
, TI.NON_LABOR_RESOURCE
, TI.NON_LABOR_RESOURCE_ORG_NAME
, TI.QUANTITY
, TI.RAW_COST
, TI.EXPENDITURE_COMMENT
, TI.TRANSACTION_STATUS_CODE
, TI.TRANSACTION_REJECTION_CODE
, TI.EXPENDITURE_ID
, TI.ORIG_TRANSACTION_REFERENCE
, TI.ATTRIBUTE_CATEGORY
, TI.ATTRIBUTE1
, TI.ATTRIBUTE2
, TI.ATTRIBUTE3
, TI.ATTRIBUTE4
, TI.ATTRIBUTE5
, TI.ATTRIBUTE6
, TI.ATTRIBUTE7
, TI.ATTRIBUTE8
, TI.ATTRIBUTE9
, TI.ATTRIBUTE10
, TI.RAW_COST_RATE
, TI.INTERFACE_ID
, TI.UNMATCHED_NEGATIVE_TXN_FLAG
, TI.EXPENDITURE_ITEM_ID
, TI.ORG_ID
, TI.DR_CODE_COMBINATION_ID
, TI.CR_CODE_COMBINATION_ID
, TI.CDL_SYSTEM_REFERENCE1
, TI.CDL_SYSTEM_REFERENCE2
, TI.CDL_SYSTEM_REFERENCE3
, TI.GL_DATE
, TI.BURDENED_COST
, TI.BURDENED_COST_RATE
, TI.SYSTEM_LINKAGE
, TI.USER_TRANSACTION_SOURCE
, TI.TXN_INTERFACE_ID
, FNM.MESSAGE_TEXT
, TI.LAST_UPDATED_BY
, TI.LAST_UPDATE_DATE
, TI.CREATED_BY
, TI.CREATION_DATE
, TI.RECEIPT_CURRENCY_AMOUNT
, TI.RECEIPT_CURRENCY_CODE
, TI.RECEIPT_EXCHANGE_RATE
, TI.DENOM_CURRENCY_CODE
, TI.DENOM_RAW_COST
, TI.DENOM_BURDENED_COST
, TI.ACCT_RATE_DATE
, TI.ACCT_RATE_TYPE
, TI.ACCT_EXCHANGE_RATE
, TI.ACCT_RAW_COST
, TI.ACCT_BURDENED_COST
, TI.ACCT_EXCHANGE_ROUNDING_LIMIT
, TI.PROJECT_CURRENCY_CODE
, TI.PROJECT_RATE_DATE
, TI.PROJECT_RATE_TYPE
, TI.PROJECT_EXCHANGE_RATE
, TI.ORIG_EXP_TXN_REFERENCE1
, TI.ORIG_USER_EXP_TXN_REFERENCE
, TI.VENDOR_NUMBER
, TI.ORIG_EXP_TXN_REFERENCE2
, TI.ORIG_EXP_TXN_REFERENCE3
, TI.OVERRIDE_TO_ORGANIZATION_NAME
, TI.REVERSED_ORIG_TXN_REFERENCE
, TI.BILLABLE_FLAG
, TI.PERSON_BUSINESS_GROUP_NAME
, TI.PROJFUNC_CURRENCY_CODE
, TI.PROJFUNC_COST_RATE_TYPE
, TI.PROJFUNC_COST_RATE_DATE
, TI.PROJFUNC_COST_EXCHANGE_RATE
, TI.PROJECT_RAW_COST
, TI.PROJECT_BURDENED_COST
, TI.ASSIGNMENT_NAME
, TI.WORK_TYPE_NAME
, TI.CDL_SYSTEM_REFERENCE4
, TI.ACCRUAL_FLAG
, TI.PROJECT_ID
, TI.TASK_ID
, TI.PERSON_ID
, TI.ORGANIZATION_ID
, TI.NON_LABOR_RESOURCE_ORG_ID
, TI.VENDOR_ID
, TI.OVERRIDE_TO_ORGANIZATION_ID
, TI.ASSIGNMENT_ID
, TI.WORK_TYPE_ID
, TI.PERSON_BUSINESS_GROUP_ID
, TI.PO_NUMBER
, TI.PO_HEADER_ID
, TI.PO_LINE_NUM
, TI.PO_LINE_ID
, TI.PERSON_TYPE
, TI.PO_PRICE_TYPE
, TI.INVENTORY_ITEM_ID
, TI.WIP_RESOURCE_ID
, TI.UNIT_OF_MEASURE
, TI.CDL_SYSTEM_REFERENCE5
, TI.ADJUSTED_EXPENDITURE_ITEM_ID
, TI.ADJUSTED_TXN_INTERFACE_ID
, TI.FC_DOCUMENT_TYPE
, TI.DOCUMENT_TYPE
, TI.DOCUMENT_DISTRIBUTION_TYPE
, TI.SI_ASSETS_ADDITION_FLAG
, TI.SC_XFER_CODE
FROM PA_TRANSACTION_INTERFACE TI
, FND_NEW_MESSAGES FNM
WHERE TI.TRANSACTION_STATUS_CODE IN ( 'R'
, 'P'
, 'PO'
, 'PR')
AND FNM.MESSAGE_NAME = TI.TRANSACTION_REJECTION_CODE
AND NOT EXISTS ( SELECT MEANING
FROM PA_LOOKUPS L2
WHERE L2.LOOKUP_CODE=TI.TRANSACTION_REJECTION_CODE
AND L2.LOOKUP_TYPE IN ( 'TRANSACTION REJECTION REASON'
, 'FC_RESULT_CODE'
, 'COST DIST REJECTION CODE'
, 'INVOICE_CURRENCY'
, 'TRANSACTION USER REJ REASON') )
AND FNM.APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID()
AND FNM.APPLICATION_ID IN (FND_GLOBAL.RESP_APPL_ID()
, 275)
AND FNM.LANGUAGE_CODE = USERENV('LANG')