Product: | PA - Projects |
---|---|
Description: | Multi-org, single currency view |
Implementation/DBA Data: |
![]() |
SELECT MC.ROWID
, MC.EXPENDITURE_ITEM_ID
, MC.LINE_NUM
, MC.TRANSFER_STATUS_CODE
, PA.AMOUNT
, MC.QUANTITY
, MC.REQUEST_ID
, MC.PROGRAM_APPLICATION_ID
, MC.PROGRAM_ID
, MC.PROGRAM_UPDATE_DATE
, MC.TRANSFERRED_DATE
, MC.TRANSFER_REJECTION_REASON
, MC.BATCH_NAME
, PA.BURDENED_COST
, PA.CREATION_DATE
, PA.CREATED_BY
, PA.BILLABLE_FLAG
, PA.RESOURCE_ACCUMULATED_FLAG
, PA.FUNCTION_TRANSACTION_CODE
, PA.PA_DATE
, PA.RECVR_PA_DATE
, PA.DR_CODE_COMBINATION_ID
, PA.GL_DATE
, PA.RECVR_GL_DATE
, PA.ACCUMULATED_FLAG
, PA.REVERSED_FLAG
, PA.LINE_NUM_REVERSED
, PA.CR_CODE_COMBINATION_ID
, PA.IND_COMPILED_SET_ID
, PA.LINE_TYPE
, PA.SYSTEM_REFERENCE1
, PA.SYSTEM_REFERENCE2
, PA.SYSTEM_REFERENCE3
, PA.ORG_ID
, PA.CODE_COMBINATION_ID
, PA.BURDEN_SUM_SOURCE_RUN_ID
, PA.BURDEN_SUM_REJECTION_CODE
, PA.DENOM_CURRENCY_CODE
, PA.DENOM_RAW_COST
, PA.DENOM_BURDENED_COST
, MC.CURRENCY_CODE
, MC.CONVERSION_DATE
, MC.RATE_TYPE
, MC.EXCHANGE_RATE
, MC.AMOUNT
, MC.BURDENED_COST
, PA.PROJECT_CURRENCY_CODE
, PA.PROJECT_RATE_DATE
, PA.PROJECT_RATE_TYPE
, PA.PROJECT_EXCHANGE_RATE
, PA.PROJECT_ID
, PA.TASK_ID
, PA.PRC_GENERATED_FLAG
, PA.UTIL_SUMMARIZED_FLAG
, PA.PJI_SUMMARIZED_FLAG
, PA.LIQUIDATE_ENCUM_FLAG
, PA.ENCUMBRANCE_BATCH_NAME
, PA.ENCUMBRANCE_TYPE_ID
, PA.ENCUMBRANCE_AMOUNT
, PA.BUDGET_CCID
, PA.ENCUM_TRANSFER_REJ_REASON
, PA.GL_PERIOD_NAME
, PA.PA_PERIOD_NAME
, PA.RECVR_GL_PERIOD_NAME
, PA.RECVR_PA_PERIOD_NAME
, PA.PROJFUNC_CURRENCY_CODE
, PA.PROJFUNC_COST_RATE_TYPE
, PA.PROJFUNC_COST_RATE_DATE
, PA.PROJFUNC_COST_EXCHANGE_RATE
, PA.PROJECT_RAW_COST
, PA.PROJECT_BURDENED_COST
, PA.WORK_TYPE_ID
, PA.SYSTEM_REFERENCE4
, PA.ORG_LABOR_SCH_RULE_ID
, PA.COST_RATE_SCH_ID
, PA.DENOM_BURDENED_CHANGE
, PA.PROJECT_BURDENED_CHANGE
, PA.PROJFUNC_BURDENED_CHANGE
, PA.ACCT_BURDENED_CHANGE
, PA.PARENT_LINE_NUM
, PA.PREV_IND_COMPILED_SET_ID
, PA.SI_ASSETS_ADDITION_FLAG
, PA.ACCT_EVENT_ID
FROM PA_COST_DISTRIBUTION_LINES_ALL PA
, PA_MC_COST_DIST_LINES_ALL MC
WHERE MC.EXPENDITURE_ITEM_ID = PA.EXPENDITURE_ITEM_ID
AND MC.PRC_ASSIGNMENT_ID = -99
AND MC.LINE_NUM = PA.LINE_NUM
AND MC.SET_OF_BOOKS_ID = NVL( TO_NUMBER( SUBSTRB( USERENV('CLIENT_INFO')
, 45
, 10) )
, -99) UNION ALL SELECT PA.ROWID
, PA.EXPENDITURE_ITEM_ID
, PA.LINE_NUM
, PA.TRANSFER_STATUS_CODE
, PA.AMOUNT
, PA.QUANTITY
, PA.REQUEST_ID
, PA.PROGRAM_APPLICATION_ID
, PA.PROGRAM_ID
, PA.PROGRAM_UPDATE_DATE
, PA.TRANSFERRED_DATE
, PA.TRANSFER_REJECTION_REASON
, PA.BATCH_NAME
, PA.BURDENED_COST
, PA.CREATION_DATE
, PA.CREATED_BY
, PA.BILLABLE_FLAG
, PA.RESOURCE_ACCUMULATED_FLAG
, PA.FUNCTION_TRANSACTION_CODE
, PA.PA_DATE
, PA.RECVR_PA_DATE
, PA.DR_CODE_COMBINATION_ID
, PA.GL_DATE
, PA.RECVR_GL_DATE
, PA.ACCUMULATED_FLAG
, PA.REVERSED_FLAG
, PA.LINE_NUM_REVERSED
, PA.CR_CODE_COMBINATION_ID
, PA.IND_COMPILED_SET_ID
, PA.LINE_TYPE
, PA.SYSTEM_REFERENCE1
, PA.SYSTEM_REFERENCE2
, PA.SYSTEM_REFERENCE3
, PA.ORG_ID
, PA.CODE_COMBINATION_ID
, PA.BURDEN_SUM_SOURCE_RUN_ID
, PA.BURDEN_SUM_REJECTION_CODE
, PA.DENOM_CURRENCY_CODE
, PA.DENOM_RAW_COST
, PA.DENOM_BURDENED_COST
, PA.ACCT_CURRENCY_CODE
, PA.ACCT_RATE_DATE
, PA.ACCT_RATE_TYPE
, PA.ACCT_EXCHANGE_RATE
, PA.ACCT_RAW_COST
, PA.ACCT_BURDENED_COST
, PA.PROJECT_CURRENCY_CODE
, PA.PROJECT_RATE_DATE
, PA.PROJECT_RATE_TYPE
, PA.PROJECT_EXCHANGE_RATE
, PA.PROJECT_ID
, PA.TASK_ID
, PA.PRC_GENERATED_FLAG
, PA.UTIL_SUMMARIZED_FLAG
, PA.PJI_SUMMARIZED_FLAG
, PA.LIQUIDATE_ENCUM_FLAG
, PA.ENCUMBRANCE_BATCH_NAME
, PA.ENCUMBRANCE_TYPE_ID
, PA.ENCUMBRANCE_AMOUNT
, PA.BUDGET_CCID
, PA.ENCUM_TRANSFER_REJ_REASON
, PA.GL_PERIOD_NAME
, PA.PA_PERIOD_NAME
, PA.RECVR_GL_PERIOD_NAME
, PA.RECVR_PA_PERIOD_NAME
, PA.PROJFUNC_CURRENCY_CODE
, PA.PROJFUNC_COST_RATE_TYPE
, PA.PROJFUNC_COST_RATE_DATE
, PA.PROJFUNC_COST_EXCHANGE_RATE
, PA.PROJECT_RAW_COST
, PA.PROJECT_BURDENED_COST
, PA.WORK_TYPE_ID
, PA.SYSTEM_REFERENCE4
, PA.ORG_LABOR_SCH_RULE_ID
, PA.COST_RATE_SCH_ID
, PA.DENOM_BURDENED_CHANGE
, PA.PROJECT_BURDENED_CHANGE
, PA.PROJFUNC_BURDENED_CHANGE
, PA.ACCT_BURDENED_CHANGE
, PA.PARENT_LINE_NUM
, PA.PREV_IND_COMPILED_SET_ID
, PA.SI_ASSETS_ADDITION_FLAG
, PA.ACCT_EVENT_ID
FROM PA_COST_DISTRIBUTION_LINES_ALL PA
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_IMPLEMENTATIONS_ALL IMP1
, PA_IMPLEMENTATIONS_ALL IMP2
WHERE EI.ORG_ID <> EI.RECVR_ORG_ID
AND PA.ORG_ID <> NVL(MO_GLOBAL.GET_CURRENT_ORG_ID
, -99)
AND EI.EXPENDITURE_ITEM_ID = PA.EXPENDITURE_ITEM_ID
AND IMP1.ORG_ID = PA.ORG_ID
AND IMP2.ORG_ID = EI.RECVR_ORG_ID
AND IMP1.SET_OF_BOOKS_ID <> IMP2.SET_OF_BOOKS_ID