View: PA_RES_MAP_BTC_V

Product:	PA - Projects
Description:	This view selects raw BTC cdls to be resource mapped during the Funds Checking process.
Implementation/DBA Data:	APPS.PA_RES_MAP_BTC_V

View Text

SELECT CDL1.EXPENDITURE_ITEM_ID EXPENDITURE_ITEM_ID
, CDL1.PROJECT_ID PROJECT_ID
, CDL1.LINE_NUM LINE_NUM
, CDL1.ROWID CDL_ROWID
, CDL1.REQUEST_ID REQUEST_ID
, EI1.TASK_ID TASK_ID
, T.TOP_TASK_ID TOP_TASK_ID
, NVL(EI1.OVERRIDE_TO_ORGANIZATION_ID
, EXP.INCURRED_BY_ORGANIZATION_ID) ORGANIZATION_ID
, EI1.JOB_ID JOB_ID
, EI1.EXPENDITURE_TYPE EXPENDITURE_TYPE
, EXP.INCURRED_BY_PERSON_ID PERSON_ID
, EXPTYPE.EXPENDITURE_CATEGORY EXPENDITURE_CATEGORY
, EI1.SYSTEM_LINKAGE_FUNCTION SYSTEM_LINKAGE_FUNCTION
, GLP.START_DATE GL_START_DATE
, PA_FUNDS_CONTROL_UTILS.GET_ENCUM_TYPE_ID(NVL(CDL1.PROJECT_ID
, -99)
, 'STD' ) ENCUM_TYPE_ID
, (SELECT CDL3.SYSTEM_REFERENCE1
FROM PA_COST_DISTRIBUTION_LINES CDL3
WHERE EI1.BURDEN_SUM_DEST_RUN_ID = CDL3.BURDEN_SUM_SOURCE_RUN_ID
AND ROWNUM =1) VENDOR_ID
, (SELECT BV.BUDGET_VERSION_ID
FROM PA_BUDGET_VERSIONS BV
, PA_BUDGETARY_CONTROL_OPTIONS PBCT
WHERE BV.PROJECT_ID = EI1.PROJECT_ID
AND BV.CURRENT_FLAG = 'Y'
AND BV.BUDGET_STATUS_CODE = 'B'
AND PBCT.PROJECT_ID = BV.PROJECT_ID
AND PBCT.BDGT_CNTRL_FLAG = 'Y'
AND BV.BUDGET_TYPE_CODE = PBCT.BUDGET_TYPE_CODE
AND (PBCT.EXTERNAL_BUDGET_CODE = 'GL' OR PBCT.EXTERNAL_BUDGET_CODE IS NULL )) BUDGET_VERSION_ID
, (SELECT BV.RESOURCE_LIST_ID
FROM PA_BUDGET_VERSIONS BV
, PA_BUDGETARY_CONTROL_OPTIONS PBCT
WHERE BV.PROJECT_ID = EI1.PROJECT_ID
AND BV.CURRENT_FLAG = 'Y'
AND BV.BUDGET_STATUS_CODE = 'B'
AND PBCT.PROJECT_ID = BV.PROJECT_ID
AND PBCT.BDGT_CNTRL_FLAG = 'Y'
AND BV.BUDGET_TYPE_CODE = PBCT.BUDGET_TYPE_CODE
AND (PBCT.EXTERNAL_BUDGET_CODE = 'GL' OR PBCT.EXTERNAL_BUDGET_CODE IS NULL )) RESOURCE_LIST_ID
, (SELECT PBM.ENTRY_LEVEL_CODE
FROM PA_BUDGET_VERSIONS BV
, PA_BUDGET_ENTRY_METHODS PBM
, PA_BUDGETARY_CONTROL_OPTIONS PBCT
WHERE BV.PROJECT_ID = EI1.PROJECT_ID
AND BV.CURRENT_FLAG = 'Y'
AND BV.BUDGET_STATUS_CODE = 'B'
AND BV.BUDGET_ENTRY_METHOD_CODE = PBM.BUDGET_ENTRY_METHOD_CODE
AND PBCT.PROJECT_ID = BV.PROJECT_ID
AND PBCT.BDGT_CNTRL_FLAG = 'Y'
AND BV.BUDGET_TYPE_CODE = PBCT.BUDGET_TYPE_CODE
AND (PBCT.EXTERNAL_BUDGET_CODE = 'GL' OR PBCT.EXTERNAL_BUDGET_CODE IS NULL )) ENTRY_LEVEL_CODE
, CDL1.SYSTEM_REFERENCE1 SYSTEM_REFERENCE1
, DECODE(EI1.SYSTEM_LINKAGE_FUNCTION
, 'VI'
, EI1.DOCUMENT_HEADER_ID
, CDL1.SYSTEM_REFERENCE2) SYSTEM_REFERENCE2
, CDL1.SYSTEM_REFERENCE3 SYSTEM_REFERENCE3
, EI1.PO_LINE_ID PO_LINE_ID
, EI1.ADJUSTMENT_TYPE EI_ADJUSTMENT_TYPE
, PT.PROJECT_TYPE PROJECT_TYPE
, PT.BURDEN_AMT_DISPLAY_METHOD BURDEN_AMT_DISP_METHOD
, CDL1.DR_CODE_COMBINATION_ID
, IMP.ORG_ID
FROM PA_EXPENDITURE_ITEMS_ALL EI1
, PA_COST_DISTRIBUTION_LINES_ALL CDL1
, PA_PROJECTS_ALL PP
, PA_PROJECT_TYPES_ALL PT
, PA_EXPENDITURE_TYPES EXPTYPE
, PA_EXPENDITURES_ALL EXP
, PA_TASKS T
, GL_PERIODS GLP
, GL_SETS_OF_BOOKS GLSOB
, PA_IMPLEMENTATIONS IMP
WHERE EI1.COST_DIST_REJECTION_CODE IS NULL
AND EXPTYPE.EXPENDITURE_TYPE = EI1.EXPENDITURE_TYPE
AND EXP.EXPENDITURE_ID = EI1.EXPENDITURE_ID
AND CDL1.PA_DATE BETWEEN GLP.START_DATE
AND TRUNC(GLP.END_DATE)
AND GLSOB.PERIOD_SET_NAME = GLP.PERIOD_SET_NAME
AND GLSOB.ACCOUNTED_PERIOD_TYPE = GLP.PERIOD_TYPE
AND GLSOB.SET_OF_BOOKS_ID = IMP.SET_OF_BOOKS_ID
AND EI1.SYSTEM_LINKAGE_FUNCTION = 'BTC'
AND EI1.COST_DISTRIBUTED_FLAG = 'S'
AND EI1.EXPENDITURE_ITEM_ID = CDL1.EXPENDITURE_ITEM_ID
AND T.TASK_ID = EI1.TASK_ID
AND CDL1.LINE_TYPE = 'R'
AND EI1.PROJECT_ID = PP.PROJECT_ID
AND PP.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.ORG_ID = PP.ORG_ID
AND PA_FUNDS_CONTROL_UTILS.GET_BDGT_LINK(NVL( CDL1.PROJECT_ID
, -99)
, 'STD' ) = 'Y'
AND EXISTS ( SELECT NULL
FROM PA_COST_DISTRIBUTION_LINES CDL2
, PA_EXPENDITURE_ITEMS EI2
, PA_AUD_COST_DIST_LINES CDL_AUD
WHERE (EI2.EXPENDITURE_ITEM_ID = CDL2.EXPENDITURE_ITEM_ID(+)
AND EI1.BURDEN_SUM_DEST_RUN_ID = CDL2.BURDEN_SUM_SOURCE_RUN_ID(+) )
AND (EI2.EXPENDITURE_ITEM_ID = CDL_AUD.EXPENDITURE_ITEM_ID(+)
AND EI1.BURDEN_SUM_DEST_RUN_ID = CDL_AUD.BURDEN_SUM_SOURCE_RUN_ID(+))
AND EI2.EXPENDITURE_ITEM_ID = CDL2.EXPENDITURE_ITEM_ID
AND EI2.SYSTEM_LINKAGE_FUNCTION = NVL(EI1.SRC_SYSTEM_LINKAGE_FUNCTION
, EI2.SYSTEM_LINKAGE_FUNCTION)
AND ( EI2.SYSTEM_LINKAGE_FUNCTION = ('VI') OR (EI2.SYSTEM_LINKAGE_FUNCTION IN ('ST'
, 'OT')
AND EI2.PO_LINE_ID IS NOT NULL
AND EI1.PO_LINE_ID = EI2.PO_LINE_ID ) ) )
AND IMP.ORG_ID = EXP.ORG_ID

Columns