View: GMS_EXPEND_ITEMS_ADJUST_V

Product:	GMS - Grants Accounting
Description:
Implementation/DBA Data:	APPS.GMS_EXPEND_ITEMS_ADJUST_V

View Text

SELECT /*+INDEX(ADL GMS_ADL_UK)*/ EI.ROWID ROW_ID
, EI.EXPENDITURE_ITEM_ID
, EI.EXPENDITURE_ID
, ADL.ADL_LINE_NUM
, P.PROJECT_ID
, P.SEGMENT1 PROJECT_NUMBER
, P.NAME PROJECT_NAME
, PT.PROJECT_TYPE
, PT.PROJECT_TYPE_CLASS_CODE
, L8.MEANING PROJECT_CLASS
, EI.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, EI.EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE
, ET.EXPENDITURE_CATEGORY
, ET.REVENUE_CATEGORY_CODE
, X.INCURRED_BY_PERSON_ID
, EMP.FULL_NAME EMPLOYEE_NAME
, EMP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, EI.JOB_ID
, J.NAME JOB_NAME
, X.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID
, NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, X.INCURRED_BY_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_ID
, O1.NAME EXPENDITURE_ORGANIZATION_NAME
, VEND.VENDOR_ID
, VEND.SEGMENT1 VENDOR_NUMBER
, VEND.VENDOR_NAME
, NVL(EMP.FULL_NAME
, VEND.VENDOR_NAME) EMPLOYEE_VENDOR_NAME
, NVL(EMP.EMPLOYEE_NUMBER
, VEND.SEGMENT1) EMPLOYEE_VENDOR_NUMBER
, EI.NON_LABOR_RESOURCE
, EI.ORGANIZATION_ID NLR_ORGANIZATION_ID
, O2.NAME NLR_ORGANIZATION_NAME
, EI.SYSTEM_LINKAGE_FUNCTION
, SL.MEANING SYSTEM_LINKAGE_M
, C.EXPENDITURE_COMMENT
, EI.TRANSACTION_SOURCE
, EI.ORIG_TRANSACTION_REFERENCE
, X.EXPENDITURE_GROUP
, X.EXPENDITURE_STATUS_CODE
, L1.MEANING EXPENDITURE_STATUS_M
, X.EXPENDITURE_ENDING_DATE
, EI.QUANTITY * (ADL.DISTRIBUTION_VALUE/100)
, ADL.RAW_COST * (ADL.DISTRIBUTION_VALUE/100)
, ET.UNIT_OF_MEASURE
, L2.MEANING UNIT_OF_MEASURE_M
, ADL.RAW_COST
, EI.RAW_COST_RATE
, ADL.COST_DISTRIBUTED_FLAG
, EI.COST_DIST_REJECTION_CODE
, EI.LABOR_COST_MULTIPLIER_NAME
, ADL.RAW_COST * (ADL.DISTRIBUTION_VALUE/100) BURDENED_COST
, EI.BURDEN_COST_RATE BURDENED_COST_RATE
, EI.COST_BURDEN_DISTRIBUTED_FLAG
, EI.RECEIPT_CURRENCY_CODE
, ADL.RAW_COST * (ADL.DISTRIBUTION_VALUE/100)
, EI.RECEIPT_EXCHANGE_RATE
, EI.DENOM_CURRENCY_CODE
, ADL.RAW_COST * (ADL.DISTRIBUTION_VALUE/100)
, ADL.RAW_COST * (ADL.DISTRIBUTION_VALUE/100)
, EI.ACCT_EXCHANGE_ROUNDING_LIMIT
, EI.ACCT_CURRENCY_CODE
, EI.ACCT_RATE_TYPE
, EI.ACCT_RATE_DATE
, EI.ACCT_EXCHANGE_RATE
, ADL.RAW_COST * (ADL.DISTRIBUTION_VALUE/100)
, ADL.RAW_COST * (ADL.DISTRIBUTION_VALUE/100)
, EI.PROJECT_CURRENCY_CODE
, EI.PROJECT_RATE_TYPE
, EI.PROJECT_RATE_DATE
, EI.PROJECT_EXCHANGE_RATE
, EI.IND_COST_DIST_REJECTION_CODE
, EI.COST_IND_COMPILED_SET_ID
, ADL.IND_COMPILED_SET_ID
, DECODE (PT.PROJECT_TYPE_CLASS_CODE
, 'CAPITAL'
, EI.BILLABLE_FLAG
, NULL) CAPITALIZABLE_FLAG
, ADL.RAW_COST
, EI.ACCRUAL_RATE
, ADL.RAW_COST
, TO_NUMBER(NULL)
, ADL.RAW_COST
, EI.BILL_RATE
, EI.BILL_RATE_MULTIPLIER
, EI.ADJUSTED_REVENUE
, EI.ADJUSTED_RATE
, DECODE (PT.PROJECT_TYPE_CLASS_CODE
, 'CAPITAL'
, NULL
, EI.BILLABLE_FLAG) BILLABLE_FLAG
, ADL.REVENUE_DISTRIBUTED_FLAG
, ADL.BILL_HOLD_FLAG
, EI.REV_DIST_REJECTION_CODE
, EI.BILL_JOB_ID
, EI.BILL_JOB_BILLING_TITLE
, EI.BILL_EMPLOYEE_BILLING_TITLE
, EI.REV_IND_COMPILED_SET_ID
, EI.INV_IND_COMPILED_SET_ID
, EI.PROJECT_ID
, EI.EVENT_NUM
, EI.EVENT_TASK_ID
, EI.ADJUSTED_EXPENDITURE_ITEM_ID
, EI.NET_ZERO_ADJUSTMENT_FLAG
, EI.TRANSFERRED_FROM_EXP_ITEM_ID
, EI.CONVERTED_FLAG
, EI.SOURCE_EXPENDITURE_ITEM_ID
, EI.LAST_UPDATE_LOGIN
, EI.LAST_UPDATE_DATE
, EI.LAST_UPDATED_BY
, EI.CREATION_DATE
, EI.CREATED_BY
, EI.ATTRIBUTE_CATEGORY
, EI.ATTRIBUTE1
, EI.ATTRIBUTE2
, EI.ATTRIBUTE3
, EI.ATTRIBUTE4
, EI.ATTRIBUTE5
, EI.ATTRIBUTE6
, EI.ATTRIBUTE7
, EI.ATTRIBUTE8
, EI.ATTRIBUTE9
, EI.ATTRIBUTE10
, EI.ORG_ID
, CDL.SYSTEM_REFERENCE2
, P.ORG_ID
, TR.USER_TRANSACTION_SOURCE
, TR.ALLOW_ADJUSTMENTS_FLAG
, TR.COSTED_FLAG
, TR.COST_BURDENED_FLAG
, TR.GL_ACCOUNTED_FLAG
, TR.ALLOW_REVERSAL_FLAG
, TR.ALLOW_BURDEN_FLAG
, EI.BURDEN_SUM_DEST_RUN_ID
, CDL.BURDEN_SUM_REJECTION_CODE
, L9.MEANING
, P.PROJECT_STATUS_CODE
, CONV.USER_CONVERSION_TYPE
, CONV2.USER_CONVERSION_TYPE
, X.ORIG_EXP_TXN_REFERENCE1
, X.ORIG_USER_EXP_TXN_REFERENCE
, X.ORIG_EXP_TXN_REFERENCE2
, X.ORIG_EXP_TXN_REFERENCE3
, X.OVERRIDING_APPROVER_PERSON_ID
, X.WF_STATUS_CODE
, X.TRANSFER_STATUS_CODE
, X.USER_BATCH_NAME
FROM PO_VENDORS VEND
, PA_COST_DIST_LINES_ALL_BAS CDL
, FND_LOOKUP_VALUES L1
, FND_LOOKUP_VALUES L2
, FND_LOOKUP_VALUES L8
, FND_LOOKUP_VALUES L9
, PER_JOBS J
, HR_ALL_ORGANIZATION_UNITS_TL O1
, HR_ALL_ORGANIZATION_UNITS_TL O2
, PA_SYSTEM_LINKAGES SL
, PA_EXPENDITURE_COMMENTS C
, PER_PEOPLE_F EMP
, PA_PROJECT_TYPES_ALL PT
, PA_EXPENDITURES_ALL X
, PA_CONVERSION_TYPES_V CONV
, PA_CONVERSION_TYPES_V CONV2
, PA_EXPENDITURE_TYPES ET
, PA_TRANSACTION_SOURCES TR
, PA_TASKS T
, PA_PROJECTS P
, PA_EXPENDITURE_ITEMS_ALL EI
, GMS_AWARD_DISTRIBUTIONS ADL
WHERE ADL.AWARD_SET_ID>0
AND EI.EXPENDITURE_ITEM_ID=ADL.EXPENDITURE_ITEM_ID
AND EI.EXPENDITURE_ID = X.EXPENDITURE_ID
AND T.PROJECT_ID = P.PROJECT_ID
AND ADL.ADL_STATUS<>'I'
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND EI.TASK_ID = T.TASK_ID
AND EI.TRANSACTION_SOURCE = TR.TRANSACTION_SOURCE (+)
AND NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, X.INCURRED_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID
AND O1.LANGUAGE = USERENV('LANG')
AND EI.ORGANIZATION_ID = O2.ORGANIZATION_ID (+)
AND O2.LANGUAGE(+) = USERENV('LANG')
AND SL.FUNCTION = EI.SYSTEM_LINKAGE_FUNCTION
AND EI.JOB_ID = J.JOB_ID (+)
AND L1.LOOKUP_TYPE = 'EXPENDITURE STATUS'
AND L1.LOOKUP_CODE = X.EXPENDITURE_STATUS_CODE
AND L1.VIEW_APPLICATION_ID = 275
AND L1.LANGUAGE = USERENV('LANG')
AND L2.LOOKUP_TYPE = 'UNIT'
AND L2.LOOKUP_CODE = ET.UNIT_OF_MEASURE
AND L2.VIEW_APPLICATION_ID = 275
AND L2.LANGUAGE = USERENV('LANG')
AND L8.LOOKUP_TYPE = 'PROJECT TYPE CLASS'
AND L8.LOOKUP_CODE = PT.PROJECT_TYPE_CLASS_CODE
AND L8.VIEW_APPLICATION_ID = 275
AND L8.LANGUAGE = USERENV('LANG')
AND L9.LOOKUP_TYPE (+) = 'BURDEN SUM REJECTION CODE'
AND L9.LOOKUP_CODE (+) = CDL.BURDEN_SUM_REJECTION_CODE
AND L9.VIEW_APPLICATION_ID (+) = 275
AND L9.LANGUAGE (+) = USERENV('LANG')
AND EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND EI.EXPENDITURE_ITEM_ID = C.EXPENDITURE_ITEM_ID (+)
AND X.INCURRED_BY_PERSON_ID = EMP.PERSON_ID (+)
AND TRUNC( SYSDATE ) BETWEEN EMP.EFFECTIVE_START_DATE (+)
AND EMP.EFFECTIVE_END_DATE (+)
AND EMP.EMPLOYEE_NUMBER (+) IS NOT NULL
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID (+)
AND CDL.LINE_NUM (+) = 1
AND CDL.SYSTEM_REFERENCE1 = VEND.VENDOR_ID (+)
AND EI.ACCT_RATE_TYPE = CONV.CONVERSION_TYPE (+)
AND EI.PROJECT_RATE_TYPE = CONV2.CONVERSION_TYPE (+)

Columns