View: PA_EXPEND_ITEMS_ADJUST2_V

Product:	PA - Projects
Description:	11i Only
Implementation/DBA Data:	APPS.PA_EXPEND_ITEMS_ADJUST2_V

View Text

SELECT EI.ROWID ROW_ID
, 'X'
, EI.EXPENDITURE_ITEM_ID
, EI.EXPENDITURE_ID
, P.PROJECT_ID
, P.SEGMENT1 PROJECT_NUMBER
, P.NAME PROJECT_NAME
, PT.PROJECT_TYPE
, PT.PROJECT_TYPE_CLASS_CODE
, EI.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, EI.EXPENDITURE_ITEM_DATE
, EI.EXPENDITURE_TYPE
, (SELECT EXPENDITURE_CATEGORY
FROM PA_EXPENDITURE_TYPES
WHERE EXPENDITURE_TYPE = EI.EXPENDITURE_TYPE) EXPENDITURE_CATEGORY
, (SELECT REVENUE_CATEGORY_CODE
FROM PA_EXPENDITURE_TYPES
WHERE EXPENDITURE_TYPE = EI.EXPENDITURE_TYPE) REVENUE_CATEGORY_CODE
, X.INCURRED_BY_PERSON_ID
, (SELECT P.FULL_NAME
FROM PER_ALL_PEOPLE_F P
WHERE P.PERSON_ID = X.INCURRED_BY_PERSON_ID
AND EI.EXPENDITURE_ITEM_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE) EMPLOYEE_NAME
, (SELECT DECODE(P.CURRENT_EMPLOYEE_FLAG
, 'Y'
, P.EMPLOYEE_NUMBER
, NVL(P.NPW_NUMBER
, P.EMPLOYEE_NUMBER))
FROM PER_ALL_PEOPLE_F P
WHERE P.PERSON_ID = X.INCURRED_BY_PERSON_ID
AND EI.EXPENDITURE_ITEM_DATE BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE) 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
, EI.NON_LABOR_RESOURCE
, EI.ORGANIZATION_ID NLR_ORGANIZATION_ID
, DECODE(EI.ORGANIZATION_ID
, NULL
, NULL
, PA_EXPENDITURES_UTILS.GET_ORG_NAME(EI.ORGANIZATION_ID
, 'NLR')) NLR_ORGANIZATION_NAME
, EI.SYSTEM_LINKAGE_FUNCTION
, (SELECT EXPENDITURE_COMMENT
FROM PA_EXPENDITURE_COMMENTS
WHERE EXPENDITURE_ITEM_ID =EI.EXPENDITURE_ITEM_ID)
, EI.TRANSACTION_SOURCE
, EI.ORIG_TRANSACTION_REFERENCE
, X.EXPENDITURE_GROUP
, X.EXPENDITURE_STATUS_CODE
, X.EXPENDITURE_ENDING_DATE
, EI.QUANTITY
, EI.BURDEN_COST
, DECODE (EI.UNIT_OF_MEASURE
, NULL
, PA_UTILS4.GET_UNIT_OF_MEASURE(EI.EXPENDITURE_TYPE )
, EI.UNIT_OF_MEASURE) UNIT_OF_MEASURE
, PA_UTILS4.GET_UNIT_OF_MEASURE_M(EI.UNIT_OF_MEASURE
, EI.EXPENDITURE_TYPE ) UNIT_OF_MEASURE_M
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.RAW_COST
, NULL)
, EI.RAW_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.RAW_COST_RATE
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.RAW_COST_RATE
, NULL)
, EI.RAW_COST_RATE)
, EI.COST_DISTRIBUTED_FLAG
, EI.COST_DIST_REJECTION_CODE
, EI.LABOR_COST_MULTIPLIER_NAME
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.BURDEN_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.BURDEN_COST
, NULL)
, EI.BURDEN_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.BURDEN_COST_RATE
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.BURDEN_COST_RATE
, NULL)
, EI.BURDEN_COST_RATE)
, EI.RECEIPT_CURRENCY_CODE
, EI.RECEIPT_EXCHANGE_RATE
, EI.RECEIPT_CURRENCY_AMOUNT
, EI.DENOM_CURRENCY_CODE
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.DENOM_RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.DENOM_RAW_COST
, NULL)
, EI.DENOM_RAW_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.DENOM_BURDENED_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.DENOM_BURDENED_COST
, NULL)
, EI.DENOM_BURDENED_COST)
, EI.ACCT_EXCHANGE_ROUNDING_LIMIT
, EI.ACCT_CURRENCY_CODE
, EI.ACCT_RATE_TYPE
, EI.ACCT_RATE_DATE
, EI.ACCT_EXCHANGE_RATE
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.ACCT_RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.ACCT_RAW_COST
, NULL)
, EI.ACCT_RAW_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.ACCT_BURDENED_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.ACCT_BURDENED_COST
, NULL)
, EI.ACCT_BURDENED_COST)
, EI.PROJECT_CURRENCY_CODE
, EI.PROJECT_RATE_TYPE
, EI.PROJECT_RATE_DATE
, EI.PROJECT_EXCHANGE_RATE
, EI.COST_BURDEN_DISTRIBUTED_FLAG
, EI.IND_COST_DIST_REJECTION_CODE
, EI.COST_IND_COMPILED_SET_ID
, DECODE (PT.PROJECT_TYPE_CLASS_CODE
, 'CAPITAL'
, EI.BILLABLE_FLAG
, NULL) CAPITALIZABLE_FLAG
, EI.ACCRUED_REVENUE
, EI.ACCRUAL_RATE
, EI.RAW_REVENUE
, EI.FORECAST_REVENUE
, EI.BILL_AMOUNT
, 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
, DECODE (PT.PROJECT_TYPE_CLASS_CODE
, 'CAPITAL'
, DECODE(T.RETIREMENT_COST_FLAG
, 'N'
, EI.REVENUE_DISTRIBUTED_FLAG
, NULL)
, NULL) GROUPED_CIP_FLAG
, DECODE (PT.PROJECT_TYPE_CLASS_CODE
, 'CAPITAL'
, NULL
, EI.REVENUE_DISTRIBUTED_FLAG) REVENUE_DISTRIBUTED_FLAG
, EI.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.EVENT_NUM
, EI.EVENT_TASK_ID
, EI.PROJECT_ID
, EI.ADJUSTED_EXPENDITURE_ITEM_ID
, NVL(EI.NET_ZERO_ADJUSTMENT_FLAG
, 'N')
, EI.TRANSFERRED_FROM_EXP_ITEM_ID
, DECODE(TRANSFERRED_FROM_EXP_ITEM_ID
, NULL
, DECODE(PA_UTILS2.ISSOURCEPURGED(EXPENDITURE_ITEM_ID)
, 'Y'
, 'Y'
, 'N')
, 'Y')
, 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
, EI.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
, P.PROJECT_STATUS_CODE
, (SELECT NVL(USER_CONVERSION_TYPE
, EI.ACCT_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.ACCT_RATE_TYPE)
, (SELECT NVL(USER_CONVERSION_TYPE
, EI.PROJECT_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.PROJECT_RATE_TYPE )
, X.ORIG_EXP_TXN_REFERENCE1
, X.ORIG_USER_EXP_TXN_REFERENCE
, EI.VENDOR_ID
, 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
, EI.CC_PRVDR_ORGANIZATION_ID
, EI.CC_RECVR_ORGANIZATION_ID
, DECODE(EI.CC_PRVDR_ORGANIZATION_ID
, NULL
, NULL
, PA_EXPENDITURES_UTILS.GET_ORG_NAME_WOSEC(EI.CC_PRVDR_ORGANIZATION_ID)) CC_PRVDR_ORGANIZATION_NAME
, DECODE(EI.CC_RECVR_ORGANIZATION_ID
, NULL
, NULL
, PA_EXPENDITURES_UTILS.GET_ORG_NAME_WOSEC(EI.CC_RECVR_ORGANIZATION_ID)) CC_RECVR_ORGANIZATION_NAME
, EI.ORG_ID
, EI.RECVR_ORG_ID
, PA_EXPENDITURES_UTILS.GET_ORG_NAME_WOSEC(EI.ORG_ID) PRVDR_ORG_NAME
, DECODE(EI.RECVR_ORG_ID
, NULL
, NULL
, PA_EXPENDITURES_UTILS.GET_ORG_NAME_WOSEC(EI.RECVR_ORG_ID)) RECVR_ORG_NAME
, EI.CC_CROSS_CHARGE_CODE
, EI.CC_CROSS_CHARGE_TYPE
, EI.CC_REJECTION_CODE
, EI.CC_BL_DISTRIBUTED_CODE
, EI.CC_IC_PROCESSED_CODE
, EI.DENOM_TP_CURRENCY_CODE
, EI.DENOM_TRANSFER_PRICE
, EI.ACCT_TP_RATE_TYPE
, (SELECT NVL(USER_CONVERSION_TYPE
, EI.ACCT_TP_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.ACCT_TP_RATE_TYPE)
, EI.ACCT_TP_RATE_DATE
, EI.ACCT_TP_EXCHANGE_RATE
, EI.ACCT_TRANSFER_PRICE
, EI.PROJACCT_TRANSFER_PRICE
, PA_EXPENDITURES_UTILS.GETJOBNAME(EI.BILL_JOB_ID) BILL_JOB_NAME
, EI.COST_JOB_ID
, PA_EXPENDITURES_UTILS.GETJOBNAME(EI.COST_JOB_ID) COST_JOB_NAME
, EI.TP_JOB_ID
, PA_EXPENDITURES_UTILS.GETJOBNAME(EI.TP_JOB_ID) TP_JOB_NAME
, EI.PROV_PROJ_BILL_JOB_ID
, PA_EXPENDITURES_UTILS.GETJOBNAME(EI.PROV_PROJ_BILL_JOB_ID) PROV_PROJ_BILL_JOB_NAME
, EI.BILL_TRANS_FORECAST_CURR_CODE
, EI.BILL_TRANS_FORECAST_REVENUE
, EI.BILL_TRANS_CURRENCY_CODE
, EI.BILL_TRANS_RAW_REVENUE
, EI.BILL_TRANS_BILL_AMOUNT
, EI.PROJECT_RAW_REVENUE
, EI.PROJECT_BILL_AMOUNT
, EI.PROJFUNC_CURRENCY_CODE
, EI.PROJFUNC_RAW_REVENUE
, EI.PROJFUNC_BILL_AMOUNT
, EI.INVPROC_CURRENCY_CODE
, EI.PROJFUNC_COST_RATE_TYPE
, EI.PROJFUNC_COST_RATE_DATE
, EI.PROJFUNC_COST_EXCHANGE_RATE
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.PROJECT_RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.PROJECT_RAW_COST
, NULL)
, EI.PROJECT_RAW_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.PROJECT_BURDENED_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 'Y'
, EI.PROJECT_BURDENED_COST
, NULL)
, EI.PROJECT_BURDENED_COST)
, EI.ASSIGNMENT_ID
, EI.WORK_TYPE_ID
, EI.PROJECT_TP_RATE_DATE
, EI.PROJECT_TP_RATE_TYPE
, (SELECT NVL(USER_CONVERSION_TYPE
, EI.PROJECT_TP_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.PROJECT_TP_RATE_TYPE )
, EI.PROJECT_TP_EXCHANGE_RATE
, EI.PROJECT_TRANSFER_PRICE
, PA_UTILS4.GET_WORK_TYPE_NAME(EI.WORK_TYPE_ID)
, PA_UTILS4.GET_ASSIGNMENT_NAME(EI.ASSIGNMENT_ID)
, EI.TP_AMT_TYPE_CODE
, EI.PROJFUNC_TP_RATE_DATE
, EI.PROJFUNC_TP_RATE_TYPE
, (SELECT NVL(USER_CONVERSION_TYPE
, EI.PROJFUNC_TP_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.PROJFUNC_TP_RATE_TYPE)
, EI.PROJFUNC_TP_EXCHANGE_RATE
, EI.PROJFUNC_TRANSFER_PRICE
, (SELECT NVL(USER_CONVERSION_TYPE
, EI.PROJFUNC_COST_RATE_TYPE)
FROM PA_CONVERSION_TYPES_V
WHERE CONVERSION_TYPE = EI.PROJFUNC_COST_RATE_TYPE )
, EI.PRVDR_ACCRUAL_DATE
, EI.RECVR_ACCRUAL_DATE
, T.RETIREMENT_COST_FLAG
, DECODE(EI.CAPITAL_EVENT_ID
, -1
, EI.CAPITAL_EVENT_ID
, NULL
, EI.CAPITAL_EVENT_ID
, (SELECT CAPITAL_EVENT_NUMBER
FROM PA_CAPITAL_EVENTS
WHERE CAPITAL_EVENT_ID = EI.CAPITAL_EVENT_ID) ) CAPITAL_EVENT_NUMBER
, DECODE(PT.PROJECT_TYPE_CLASS_CODE
, 'CAPITAL'
, DECODE(T.RETIREMENT_COST_FLAG
, 'Y'
, EI.REVENUE_DISTRIBUTED_FLAG
, NULL)
, NULL) GROUPED_RWIP_FLAG
, TO_DATE(PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME(EI.EXPENDITURE_ITEM_ID
, 'PA_DATE')
, 'DD-MON-RRRR') LATEST_PA_DATE
, TO_DATE(PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME(EI.EXPENDITURE_ITEM_ID
, 'GL_DATE')
, 'DD-MON-RRRR') LATEST_GL_DATE
, TO_DATE(PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME(EI.EXPENDITURE_ITEM_ID
, 'RECVR_PA_DATE')
, 'DD-MON-RRRR') LATEST_RECVR_PA_DATE
, TO_DATE(PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME(EI.EXPENDITURE_ITEM_ID
, 'RECVR_GL_DATE')
, 'DD-MON-RRRR') LATEST_RECVR_GL_DATE
, SUBSTR(PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME(EI.EXPENDITURE_ITEM_ID
, 'PA_PERIOD_NAME')
, 1
, 15) LATEST_PA_PERIOD_NAME
, SUBSTR(PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME(EI.EXPENDITURE_ITEM_ID
, 'GL_PERIOD_NAME')
, 1
, 15) LATEST_GL_PERIOD_NAME
, SUBSTR(PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME(EI.EXPENDITURE_ITEM_ID
, 'RECVR_PA_PERIOD_NAME')
, 1
, 15) LATEST_RECVR_PA_PERIOD_NAME
, SUBSTR(PA_EXPENDITURES_UTILS.GET_LATEST_DATE_PERIOD_NAME(EI.EXPENDITURE_ITEM_ID
, 'RECVR_GL_PERIOD_NAME')
, 1
, 15) LATEST_RECVR_GL_PERIOD_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, X.PERSON_TYPE
, EI.PO_LINE_ID
, EI.PO_PRICE_TYPE
, (SELECT H.SEGMENT1
FROM PO_HEADERS_ALL H
, PO_LINES_ALL L
WHERE L.PO_HEADER_ID = H.PO_HEADER_ID
AND L.PO_LINE_ID = EI.PO_LINE_ID) PO_NUMBER
, (SELECT L.LINE_NUM
FROM PO_LINES_ALL L
WHERE L.PO_LINE_ID = EI.PO_LINE_ID) PO_LINE_NUMBER
, (SELECT F.MEANING
FROM FND_LOOKUPS F
WHERE F.LOOKUP_TYPE = 'PRICE DIFFERENTIALS'
AND F.LOOKUP_CODE = EI.PO_PRICE_TYPE) PO_PRICE_TYPE_M
, (SELECT F.MEANING
FROM PA_LOOKUPS F
WHERE F.LOOKUP_TYPE = 'PA_PERSON_TYPE'
AND F.LOOKUP_CODE = X.PERSON_TYPE) PA_PERSON_TYPE_M
, WIP_RESOURCE_ID
, DECODE (EI.WIP_RESOURCE_ID
, NULL
, NULL
, PA_UTILS4.GET_WIP_RESOURCE_CODE(EI.WIP_RESOURCE_ID) ) WIP_RESOURCE
, INVENTORY_ITEM_ID
, DECODE (EI.INVENTORY_ITEM_ID
, NULL
, NULL
, PA_UTILS4.GET_INVENTORY_ITEM(EI.INVENTORY_ITEM_ID)) INVENTORY_ITEM
, EI.DOCUMENT_HEADER_ID
, EI.DOCUMENT_DISTRIBUTION_ID
, EI.DOCUMENT_LINE_NUMBER
, EI.DOCUMENT_PAYMENT_ID
, EI.DOCUMENT_TYPE
, EI.DOCUMENT_DISTRIBUTION_TYPE
, DECODE(PT.PROJECT_TYPE_CLASS_CODE
, 'CAPITAL'
, DECODE(T.BILLABLE_FLAG
, 'N'
, DECODE(T.RETIREMENT_COST_FLAG
, 'N'
, 'Y'
, 'N')
, 'N')
, 'N')
, -999
, -999
, EI.LOCATION_ID
, (SELECT LOCATION_CODE
FROM HR_LOCATIONS
WHERE LOCATION_ID = EI.LOCATION_ID) LOCATION_CODE
, EI.PAY_ELEMENT_TYPE_ID
, (SELECT ELEMENT_NAME
FROM PAY_ELEMENT_TYPES_F_TL
WHERE ELEMENT_TYPE_ID = EI.PAY_ELEMENT_TYPE_ID
AND LANGUAGE=USERENV('LANG')) PAY_ELEMENT_NAME
, EI.RATE_SOURCE_CODE
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'PA_RATE_SOURCE'
AND LOOKUP_CODE = EI.RATE_SOURCE_CODE ) RATE_SOURCE_M
, EI.COSTING_METHOD
, (SELECT MEANING
FROM PA_LOOKUPS
WHERE LOOKUP_TYPE = 'PA_LABOR_COSTING_METHOD'
AND LOOKUP_CODE = EI.COSTING_METHOD ) COSTING_METHOD_M
, EI.PAYROLL_ACCRUAL_FLAG
, P_AUD.INTERFACE_RUN_ID COSTED_PAYROLL_SET_ID
, P_AUD.PAYROLL_NAME
, P_AUD.PAY_PERIOD_START_DATE
, P_AUD.PAY_PERIOD_END_DATE
FROM PA_PROJECTS_ALL P
, PA_TASKS T
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_EXPENDITURES_ALL X
, PA_PROJECT_TYPES_ALL PT
, PA_TRANSACTION_SOURCES TR
, HR_ALL_ORGANIZATION_UNITS_TL O1
, PER_JOBS J
, ( SELECT AUD.INTERFACE_RUN_ID
, PAY.PAYROLL_NAME
, TRUNC(PAY_PERIOD_START_DATE) PAY_PERIOD_START_DATE
, TRUNC(PAY_PERIOD_END_DATE) PAY_PERIOD_END_DATE
FROM PA_PAY_AUDIT_ALL AUD
, PAY_PAYROLLS_F PAY
WHERE AUD.PAYROLL_ID = PAY.PAYROLL_ID
AND AUD.INT_EXT_INDICATOR = 'INT' UNION SELECT AUD.INTERFACE_RUN_ID
, PAY.PAYROLL_NAME
, TRUNC(PAY_PERIOD_START_DATE) PAY_PERIOD_START_DATE
, TRUNC(PAY_PERIOD_END_DATE) PAY_PERIOD_END_DATE
FROM PA_PAY_AUDIT_ALL AUD
, PA_PAY_EXTERNAL_PAYROLL PAY
WHERE AUD.PAYROLL_ID = PAY.PAYROLL_ID
AND AUD.INT_EXT_INDICATOR = 'EXT') P_AUD
WHERE T.PROJECT_ID = P.PROJECT_ID
AND EI.PROJECT_ID = P.PROJECT_ID
AND P.PROJECT_TYPE = PT.PROJECT_TYPE
AND P.ORG_ID = PT.ORG_ID
AND EI.TASK_ID = T.TASK_ID
AND EI.EXPENDITURE_ID = X.EXPENDITURE_ID
AND NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, X.INCURRED_BY_ORGANIZATION_ID) = O1.ORGANIZATION_ID
AND EI.JOB_ID = J.JOB_ID (+)
AND EI.INTERFACE_RUN_ID = P_AUD.INTERFACE_RUN_ID(+)
AND EI.TRANSACTION_SOURCE = TR.TRANSACTION_SOURCE (+)
AND O1.LANGUAGE = USERENV('LANG')
AND EXISTS ( SELECT 1
FROM PA_IMPLEMENTATIONS IMP
WHERE ( EI.ORG_ID = IMP.ORG_ID OR NVL(PA_EXPENDITURE_INQUIRY.GET_MODE
, 'X') <> 'CROSS-PROJECT' ))

Columns