VIEW: APPS.PA_STATUS_EI_DRIVE_V

Source

View Text - Preformatted

SELECT /*+ leading(PSEI) use_nl(EI) */ PSEI.EXPENDITURE_ITEM_ID , PSEI.PA_START_DATE , PA_STATUS.GETPROJID , PA_STATUS.GETTASKID , PSEI.PA_PERIOD , PA_STATUS.GETRSRCMEMBERID , PSEI.QUANTITY , DECODE(EI.system_linkage_function, 'ST', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.raw_cost, NULL), 'OT', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.raw_cost, NULL), PSEI.raw_cost) , DECODE(EI.system_linkage_function, 'ST', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.burdened_cost, NULL), 'OT', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.burdened_cost, NULL), PSEI.burdened_cost) , PSEI.BILLABLE_QUANTITY , DECODE(EI.system_linkage_function, 'ST', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.billable_raw_cost, NULL), 'OT', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.billable_raw_cost, NULL), PSEI.billable_raw_cost) , DECODE(EI.system_linkage_function, 'ST', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.billable_burdened_cost, NULL), 'OT', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.billable_burdened_cost, NULL), PSEI.billable_burdened_cost) , PSEI.REVENUE , 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 , EI.JOB_ID , X.INCURRED_BY_ORGANIZATION_ID , EI.OVERRIDE_TO_ORGANIZATION_ID , NVL(EI.OVERRIDE_TO_ORGANIZATION_ID, X.INCURRED_BY_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_ID , OTL.NAME EXPENDITURE_ORGANIZATION_NAME , EI.NON_LABOR_RESOURCE , EI.ORGANIZATION_ID NLR_ORGANIZATION_ID , EI.SYSTEM_LINKAGE_FUNCTION , SL.MEANING SYSTEM_LINKAGE_M , EI.TRANSACTION_SOURCE , EI.ORIG_TRANSACTION_REFERENCE , X.EXPENDITURE_GROUP , NVL( EI.UNIT_OF_MEASURE, ET.UNIT_OF_MEASURE ) UNIT_OF_MEASURE , L2.MEANING UNIT_OF_MEASURE_M , EI.receipt_currency_code , EI.receipt_currency_amount , EI.receipt_exchange_rate , EI.denom_currency_code , DECODE(EI.system_linkage_function, 'ST', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.denom_raw_cost, NULL), 'OT', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.denom_raw_cost, NULL), PSEI.denom_raw_cost) , DECODE(EI.system_linkage_function, 'ST', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.denom_burdened_cost, NULL), 'OT', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.denom_burdened_cost, NULL), PSEI.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(PA_STATUS.GETPROJID), 'Y', PSEI.acct_raw_cost, NULL), 'OT', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.acct_raw_cost, NULL), PSEI.acct_raw_cost) , DECODE(EI.system_linkage_function, 'ST', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.acct_burdened_cost, NULL), 'OT', DECODE(pa_security.view_labor_costs(PA_STATUS.GETPROJID), 'Y', PSEI.acct_burdened_cost, NULL), PSEI.acct_burdened_cost) , EI.project_currency_code , EI.project_rate_type , EI.project_rate_date , EI.project_exchange_rate , EI.ORG_ID , P.ORG_ID FROM PA_LOOKUPS L1, PA_LOOKUPS L2, HR_ALL_ORGANIZATION_UNITS_TL OTL, PA_SYSTEM_LINKAGES SL, PA_EXPENDITURES_ALL X, PA_EXPENDITURE_TYPES ET, PA_EXPENDITURE_ITEMS_ALL EI, PA_TASKS T, PA_PROJECTS P, PA_STATUS_EI_BASE_V PSEI WHERE EI.TASK_ID = T.TASK_ID AND T.PROJECT_ID = P.PROJECT_ID AND EI.EXPENDITURE_ID = X.EXPENDITURE_ID AND NVL(EI.OVERRIDE_TO_ORGANIZATION_ID,X.INCURRED_BY_ORGANIZATION_ID) = OTL.ORGANIZATION_ID AND DECODE(OTL.ORGANIZATION_ID,NULL,'1',OTL.LANGUAGE) = DECODE(OTL.ORGANIZATION_ID,NULL,'1',USERENV('LANG')) AND SL.FUNCTION = EI.SYSTEM_LINKAGE_FUNCTION AND L1.LOOKUP_TYPE = 'EXPENDITURE STATUS' AND L1.LOOKUP_CODE = X.EXPENDITURE_STATUS_CODE AND L2.LOOKUP_TYPE = 'UNIT' AND L2.LOOKUP_CODE = NVL( EI.UNIT_OF_MEASURE, ET.UNIT_OF_MEASURE) AND EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE AND EI.EXPENDITURE_ITEM_ID = PSEI.EXPENDITURE_ITEM_ID AND P.PROJECT_ID = PA_STATUS.GETPROJID AND T.TASK_ID = PA_STATUS.GETTASKID

View Text - HTML Formatted

SELECT /*+ LEADING(PSEI) USE_NL(EI) */ PSEI.EXPENDITURE_ITEM_ID
, PSEI.PA_START_DATE
, PA_STATUS.GETPROJID
, PA_STATUS.GETTASKID
, PSEI.PA_PERIOD
, PA_STATUS.GETRSRCMEMBERID
, PSEI.QUANTITY
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.RAW_COST
, NULL)
, PSEI.RAW_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.BURDENED_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.BURDENED_COST
, NULL)
, PSEI.BURDENED_COST)
, PSEI.BILLABLE_QUANTITY
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.BILLABLE_RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.BILLABLE_RAW_COST
, NULL)
, PSEI.BILLABLE_RAW_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.BILLABLE_BURDENED_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.BILLABLE_BURDENED_COST
, NULL)
, PSEI.BILLABLE_BURDENED_COST)
, PSEI.REVENUE
, 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
, EI.JOB_ID
, X.INCURRED_BY_ORGANIZATION_ID
, EI.OVERRIDE_TO_ORGANIZATION_ID
, NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, X.INCURRED_BY_ORGANIZATION_ID) EXPENDITURE_ORGANIZATION_ID
, OTL.NAME EXPENDITURE_ORGANIZATION_NAME
, EI.NON_LABOR_RESOURCE
, EI.ORGANIZATION_ID NLR_ORGANIZATION_ID
, EI.SYSTEM_LINKAGE_FUNCTION
, SL.MEANING SYSTEM_LINKAGE_M
, EI.TRANSACTION_SOURCE
, EI.ORIG_TRANSACTION_REFERENCE
, X.EXPENDITURE_GROUP
, NVL( EI.UNIT_OF_MEASURE
, ET.UNIT_OF_MEASURE ) UNIT_OF_MEASURE
, L2.MEANING UNIT_OF_MEASURE_M
, EI.RECEIPT_CURRENCY_CODE
, EI.RECEIPT_CURRENCY_AMOUNT
, EI.RECEIPT_EXCHANGE_RATE
, EI.DENOM_CURRENCY_CODE
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.DENOM_RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.DENOM_RAW_COST
, NULL)
, PSEI.DENOM_RAW_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.DENOM_BURDENED_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.DENOM_BURDENED_COST
, NULL)
, PSEI.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(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.ACCT_RAW_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.ACCT_RAW_COST
, NULL)
, PSEI.ACCT_RAW_COST)
, DECODE(EI.SYSTEM_LINKAGE_FUNCTION
, 'ST'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.ACCT_BURDENED_COST
, NULL)
, 'OT'
, DECODE(PA_SECURITY.VIEW_LABOR_COSTS(PA_STATUS.GETPROJID)
, 'Y'
, PSEI.ACCT_BURDENED_COST
, NULL)
, PSEI.ACCT_BURDENED_COST)
, EI.PROJECT_CURRENCY_CODE
, EI.PROJECT_RATE_TYPE
, EI.PROJECT_RATE_DATE
, EI.PROJECT_EXCHANGE_RATE
, EI.ORG_ID
, P.ORG_ID
FROM PA_LOOKUPS L1
, PA_LOOKUPS L2
, HR_ALL_ORGANIZATION_UNITS_TL OTL
, PA_SYSTEM_LINKAGES SL
, PA_EXPENDITURES_ALL X
, PA_EXPENDITURE_TYPES ET
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_TASKS T
, PA_PROJECTS P
, PA_STATUS_EI_BASE_V PSEI
WHERE EI.TASK_ID = T.TASK_ID
AND T.PROJECT_ID = P.PROJECT_ID
AND EI.EXPENDITURE_ID = X.EXPENDITURE_ID
AND NVL(EI.OVERRIDE_TO_ORGANIZATION_ID
, X.INCURRED_BY_ORGANIZATION_ID) = OTL.ORGANIZATION_ID
AND DECODE(OTL.ORGANIZATION_ID
, NULL
, '1'
, OTL.LANGUAGE) = DECODE(OTL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND SL.FUNCTION = EI.SYSTEM_LINKAGE_FUNCTION
AND L1.LOOKUP_TYPE = 'EXPENDITURE STATUS'
AND L1.LOOKUP_CODE = X.EXPENDITURE_STATUS_CODE
AND L2.LOOKUP_TYPE = 'UNIT'
AND L2.LOOKUP_CODE = NVL( EI.UNIT_OF_MEASURE
, ET.UNIT_OF_MEASURE)
AND EI.EXPENDITURE_TYPE = ET.EXPENDITURE_TYPE
AND EI.EXPENDITURE_ITEM_ID = PSEI.EXPENDITURE_ITEM_ID
AND P.PROJECT_ID = PA_STATUS.GETPROJID
AND T.TASK_ID = PA_STATUS.GETTASKID