VIEW: APPS.GMS_EXPEND_ITEMS_ADJUST_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)