[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ ordered */
PBG.NAME,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE),
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT1_VALUE
WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT2_VALUE
WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT3_VALUE
END BUDGET_VALUE
FROM (Select BUDGET_NAME,
BUSINESS_GROUP_ID,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID,
BUDGET_START_DATE,
BUDGET_END_DATE
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'', ''POSITION'')
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PER_BUSINESS_GROUPS_PERF PBG
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
'SELECT PBG.NAME,
FND_DATE.DATE_TO_DISPLAYDATE(PRDS.START_DATE),
FND_DATE.DATE_TO_DISPLAYDATE(PRDS.END_DATE),
NULL COL4,
NULL COL5
FROM
(
(SELECT ID,
CASE WHEN (LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)) IS NULL
AND DATE_FROM > :p_start_date
AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN :p_start_date
WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN :p_start_date
END START_DATE,
CASE WHEN (LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)) IS NULL
AND DATE_FROM > :p_start_date
AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN DATE_FROM -1
WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN DATE_FROM -1
END END_DATE
FROM
( SELECT PB.BUSINESS_GROUP_ID ID,
PB.BUDGET_START_DATE DATE_FROM,
PB.BUDGET_END_DATE DATE_TO
FROM PQH_BUDGETS PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD=''HEAD'' ) PST
WHERE PB.POSITION_CONTROL_FLAG = ''Y''
AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
ORDER BY DATE_FROM ))
UNION
(SELECT ID,
CASE WHEN DATE_TO+1 < NVL((LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)), :p_end_date)
THEN DATE_TO +1
WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN :p_start_date
END START_DATE,
CASE WHEN DATE_TO+1 < NVL((LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)), :p_end_date)
THEN NVL((LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)) - 1, :p_end_date)
WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN DATE_FROM -1
END END_DATE
FROM
( SELECT PB.BUSINESS_GROUP_ID ID,
PB.BUDGET_START_DATE DATE_FROM,
PB.BUDGET_END_DATE DATE_TO
FROM PQH_BUDGETS PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD=''HEAD'' ) PST
WHERE PB.POSITION_CONTROL_FLAG = ''Y''
AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
ORDER BY DATE_FROM ))
)PRDS,
PER_BUSINESS_GROUPS PBG
WHERE PRDS.START_DATE IS NOT NULL
AND PRDS.ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME';
SELECT PBG.NAME,
HR_GENERAL.DECODE_ORGANIZATION(ORG.ORGANIZATION_ID),
NULL COL3,
NULL COL4,
NULL COL5
FROM
(
SELECT ORGANIZATION_ID,
BUSINESS_GROUP_ID ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE DATE_FROM <= :p_end_date
AND NVL(DATE_TO,hr_general.end_of_time) >= :p_start_date
AND (ORGANIZATION_ID,BUSINESS_GROUP_ID) NOT IN
(
SELECT /*+ ordered*/
PBD.ORGANIZATION_ID,
PB.BUSINESS_GROUP_ID
FROM (Select BUSINESS_GROUP_ID,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'', ''POSITION'')
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
) )ORG,
PER_BUSINESS_GROUPS_PERF PBG
WHERE ORG.ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
SELECT PBG.NAME,
HR_GENERAL.DECODE_POSITION_LATEST_NAME(POSITION_ID),
NULL COL3,
NULL COL4,
NULL COL5
FROM
(
SELECT POSITION_ID,
BUSINESS_GROUP_ID ID
FROM HR_ALL_POSITIONS_F
WHERE EFFECTIVE_START_DATE <= :p_end_date
AND NVL(EFFECTIVE_END_DATE, hr_general.end_of_time) >= :p_start_date
AND (POSITION_ID, BUSINESS_GROUP_ID) NOT IN
(
SELECT /*+ ordered*/
PBD.POSITION_ID,
PB.BUSINESS_GROUP_ID ID
FROM (Select BUSINESS_GROUP_ID,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD = ''POSITION''
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
) )POS,
PER_BUSINESS_GROUPS_PERF PBG
WHERE POS.ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
SELECT /*+ ordered */
PBG.NAME,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE),
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID))||'' ''||
TO_CHAR(
CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT1_VALUE
WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT2_VALUE
WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBV.BUDGET_UNIT3_VALUE
END,
FND_CURRENCY.GET_FORMAT_MASK(NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID)),30)
) BUDGET_VALUE
FROM (Select BUDGET_NAME,
BUSINESS_GROUP_ID,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID,
BUDGET_START_DATE,
BUDGET_END_DATE,
CURRENCY_CODE
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'', ''POSITION'')
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PER_BUSINESS_GROUPS_PERF PBG
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND PBG.BUSINESS_GROUP_ID = PB.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
'SELECT PBG.NAME,
FND_DATE.DATE_TO_DISPLAYDATE(PRDS.EFFECTIVE_START_DATE),
FND_DATE.DATE_TO_DISPLAYDATE(PRDS.EFFECTIVE_END_DATE),
NULL COL4,
NULL COL5
FROM
(
(SELECT ID,
CASE WHEN (LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)) IS NULL
AND DATE_FROM > :p_start_date
AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN :p_start_date
WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN :p_start_date
END EFFECTIVE_START_DATE,
CASE WHEN (LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)) IS NULL
AND DATE_FROM > :p_start_date
AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN DATE_FROM -1
WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN DATE_FROM -1
END EFFECTIVE_END_DATE
FROM
( SELECT PB.BUSINESS_GROUP_ID ID,
PB.BUDGET_START_DATE DATE_FROM,
PB.BUDGET_END_DATE DATE_TO
FROM PQH_BUDGETS PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD=''MONEY'' ) PST
WHERE PB.POSITION_CONTROL_FLAG = ''Y''
AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
ORDER BY DATE_FROM ))
UNION
(SELECT ID,
CASE WHEN DATE_TO+1 < NVL((LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)), :p_end_date)
THEN DATE_TO +1
WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN :p_start_date
END EFFECTIVE_START_DATE,
CASE WHEN DATE_TO+1 < NVL((LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)), :p_end_date)
THEN NVL((LEAD(DATE_FROM, 1) OVER
(PARTITION BY ID ORDER BY DATE_FROM)) - 1, :p_end_date)
WHEN DATE_FROM > :p_start_date AND (row_number() over (PARTITION BY ID ORDER BY DATE_FROM)) =1
THEN DATE_FROM -1
END EFFECTIVE_END_DATE
FROM
( SELECT PB.BUSINESS_GROUP_ID ID,
PB.BUDGET_START_DATE DATE_FROM,
PB.BUDGET_END_DATE DATE_TO
FROM PQH_BUDGETS PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD=''MONEY'' ) PST
WHERE PB.POSITION_CONTROL_FLAG = ''Y''
AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
ORDER BY DATE_FROM ))
)PRDS,
PER_BUSINESS_GROUPS PBG
WHERE EFFECTIVE_START_DATE IS NOT NULL
AND PRDS.ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME';
SELECT PBG.NAME,
HR_GENERAL.DECODE_ORGANIZATION(ORG.ORGANIZATION_ID),
NULL COL3,
NULL COL4,
NULL COL5
FROM
(
SELECT ORGANIZATION_ID,
BUSINESS_GROUP_ID ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE DATE_FROM <= :p_end_date
AND NVL(DATE_TO,hr_general.end_of_time) >= :p_start_date
AND (ORGANIZATION_ID, BUSINESS_GROUP_ID) NOT IN
(
SELECT /*+ ordered*/
PBD.ORGANIZATION_ID,
PB.BUSINESS_GROUP_ID ID
FROM (Select BUSINESS_GROUP_ID,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
)) ORG,
PER_BUSINESS_GROUPS_PERF PBG
WHERE ORG.ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
SELECT PBG.NAME,
HR_GENERAL.DECODE_POSITION_LATEST_NAME(POS.POSITION_ID),
NULL COL3,
NULL COL4,
NULL COL5
FROM
(
SELECT POSITION_ID,
BUSINESS_GROUP_ID ID
FROM HR_ALL_POSITIONS_F
WHERE EFFECTIVE_START_DATE <= :p_end_date
AND NVL(EFFECTIVE_END_DATE, hr_general.end_of_time) >= :p_start_date
AND (POSITION_ID, BUSINESS_GROUP_ID) NOT IN
(
SELECT /*+ ordered*/
PBD.POSITION_ID,
PB.BUSINESS_GROUP_ID ID
FROM (Select BUSINESS_GROUP_ID,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD = ''POSITION''
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
) )POS,
PER_BUSINESS_GROUPS_PERF PBG
WHERE POS.ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
PDBS.DFLT_BUDGET_SET_NAME,
HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELE.BUDGET_ELEMENT_ID,:p_start_date),
NULL COL4,
NULL COL5
FROM PQH_BUDGETS PB,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD=''MONEY'' ) PST,
PQH_BUDGET_DETAILS DET,
PQH_BUDGET_PERIODS PRD,
PER_TIME_PERIODS PTPS,
PER_TIME_PERIODS PTPE,
PQH_BUDGET_SETS BSET,
PQH_BUDGET_ELEMENTS ELE,
PQH_BUDGET_FUND_SRCS SRC,
PQH_DFLT_BUDGET_SETS PDBS
WHERE PB.POSITION_CONTROL_FLAG = ''Y''
AND PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND DET.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
AND DET.BUDGET_DETAIL_ID = PRD.BUDGET_DETAIL_ID
AND PRD.BUDGET_PERIOD_ID = BSET.BUDGET_PERIOD_ID
AND PRD.START_TIME_PERIOD_ID = PTPS.TIME_PERIOD_ID
AND PRD.END_TIME_PERIOD_ID = PTPE.TIME_PERIOD_ID
AND BSET.BUDGET_SET_ID = ELE.BUDGET_SET_ID
AND ELE.BUDGET_ELEMENT_ID = SRC.BUDGET_ELEMENT_ID
AND SRC.COST_ALLOCATION_KEYFLEX_ID IS NULL
AND BSET.DFLT_BUDGET_SET_ID = PDBS.DFLT_BUDGET_SET_ID
AND PB.BUSINESS_GROUP_ID = PDBS.BUSINESS_GROUP_ID
';
SELECT BSET.BUDGET,
HRI_APL_DGNSTC_WBM.GET_BSET_NAME(BSET.BUDGET_SET_ID,BSET.BUSINESS_GROUP_ID),
SUM( CASE WHEN BSET.BUDGET_UNIT1_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
WHEN BSET.BUDGET_UNIT2_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
WHEN BSET.BUDGET_UNIT3_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
END ),
NULL COL4,
NULL COL5
FROM
( SELECT DISTINCT BSET.BUDGET_SET_ID,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'' BUDGET,
PB.BUSINESS_GROUP_ID,
PB.BUDGET_UNIT1_ID,
PB.BUDGET_UNIT2_ID,
PB.BUDGET_UNIT3_ID,
PST.SHARED_TYPE_ID
FROM PQH_BUDGETS PB,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD=''MONEY'' ) PST,
PQH_BUDGET_DETAILS DET,
PQH_BUDGET_PERIODS PRD,
PER_TIME_PERIODS PTPS,
PER_TIME_PERIODS PTPE,
PQH_BUDGET_SETS BSET
WHERE PB.POSITION_CONTROL_FLAG = ''Y''
AND PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND DET.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
AND DET.BUDGET_DETAIL_ID = PRD.BUDGET_DETAIL_ID
AND PRD.BUDGET_PERIOD_ID = BSET.BUDGET_PERIOD_ID
AND PRD.START_TIME_PERIOD_ID = PTPS.TIME_PERIOD_ID
AND PRD.END_TIME_PERIOD_ID = PTPE.TIME_PERIOD_ID ) BSET,
PQH_BUDGET_ELEMENTS ELE
WHERE BSET.BUDGET_SET_ID = ELE.BUDGET_SET_ID
GROUP BY BSET.BUDGET_SET_ID,
BSET.BUSINESS_GROUP_ID,
BSET.BUDGET
HAVING SUM(
CASE WHEN BSET.BUDGET_UNIT1_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
WHEN BSET.BUDGET_UNIT2_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
WHEN BSET.BUDGET_UNIT3_ID = BSET.SHARED_TYPE_ID THEN ELE.DISTRIBUTION_PERCENTAGE
END )
<> 100
';
SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
ELE.ELEMENT_NAME,
PIVF.NAME,
PBCE.SALARY_BASIS_FLAG,
PBCE.FORMULA_ID
FROM PQH_BDGT_CMMTMNT_ELMNTS PBCE,
PQH_BUDGETS PB,
PQH_BUDGET_VERSIONS PBV,
PAY_ELEMENT_TYPES_F ELE,
PAY_INPUT_VALUES_F PIVF
WHERE PBCE.BUDGET_ID = PB.BUDGET_ID
AND PB.BUDGET_ID = PBV.BUDGET_ID
AND ELE.ELEMENT_TYPE_ID = PBCE.ELEMENT_TYPE_ID
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND PIVF.INPUT_VALUE_ID = PBCE.ELEMENT_INPUT_VALUE_ID
AND :p_start_date BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE
AND :p_start_date BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
ORDER BY PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')''
';
SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
FND_DATE.DATE_TO_DISPLAYDATE(PEC.COMMITMENT_START_DATE),
FND_DATE.DATE_TO_DISPLAYDATE(PEC.COMMITMENT_END_DATE),
ELE.ELEMENT_NAME,
NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID))||'' ''||
TO_CHAR(
SUM(PEC.COMMITMENT_AMOUNT),
FND_CURRENCY.GET_FORMAT_MASK(NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID)),30)
) COMMITMENT_AMOUNT
FROM PQH_ELEMENT_COMMITMENTS PEC,
PQH_BUDGETS PB,
PQH_BUDGET_VERSIONS PBV,
PAY_ELEMENT_TYPES_F ELE
WHERE PEC.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
AND PB.BUDGET_ID = PBV.BUDGET_ID
AND ELE.ELEMENT_TYPE_ID = PEC.ELEMENT_TYPE_ID
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PEC.COMMITMENT_START_DATE <= :p_end_date
AND PEC.COMMITMENT_END_DATE >= :p_start_date
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND :p_start_date BETWEEN ele.EFFECTIVE_START_DATE AND ele.EFFECTIVE_END_DATE
GROUP BY PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
PB.CURRENCY_CODE,
PB.BUDGET_ID,
PEC.COMMITMENT_START_DATE,
PEC.COMMITMENT_END_DATE,
ELE.ELEMENT_NAME
ORDER BY PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')''
';
SELECT BUDGET_NAME,
FND_DATE.DATE_TO_DISPLAYDATE(DATE_FROM),
FND_DATE.DATE_TO_DISPLAYDATE(DATE_TO),
NULL COL4,
NULL COL5
FROM
(
SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'' BUDGET_NAME,
PBV.DATE_FROM,
PBV.DATE_TO
FROM PQH_BUDGETS PB,
PQH_BUDGET_VERSIONS PBV
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
MINUS
SELECT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'' BUDGET_NAME,
PBV.DATE_FROM,
PBV.DATE_TO
FROM PQH_ELEMENT_COMMITMENTS PEC,
PQH_BUDGETS PB,
PQH_BUDGET_VERSIONS PBV
WHERE PEC.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
AND PB.BUDGET_ID = PBV.BUDGET_ID
AND PEC.COMMITMENT_START_DATE <= :p_end_date
AND PEC.COMMITMENT_END_DATE >= :p_start_date
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
)
ORDER BY BUDGET_NAME
';
SELECT HCPV.VALUE,
FND_DATE.DATE_TO_DISPLAYDATE(PEC.COMMITMENT_START_DATE),
FND_DATE.DATE_TO_DISPLAYDATE(PEC.COMMITMENT_END_DATE),
ELE.ELEMENT_NAME,
PEC.COMMITMENT_AMOUNT
FROM PQH_ELEMENT_COMMITMENTS PEC,
per_all_assignments_f PAAF,
HRI_CL_PER_V HCPV,
PAY_ELEMENT_TYPES_F ELE
WHERE PEC.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF.PERSON_ID = HCPV.ID
AND ELE.ELEMENT_TYPE_ID = PEC.ELEMENT_TYPE_ID
AND PEC.COMMITMENT_START_DATE <= :p_end_date
AND PEC.COMMITMENT_END_DATE >= :p_start_date
AND :p_start_date BETWEEN ele.EFFECTIVE_START_DATE AND ele.EFFECTIVE_END_DATE
AND :p_start_date BETWEEN hcpv.EFFECTIVE_START_DATE AND hcpv.EFFECTIVE_END_DATE
AND :p_start_date BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
ORDER BY HCPV.VALUE
';
SELECT PRDS.VALUE,
ELE.ELEMENT_NAME,
FND_DATE.DATE_TO_DISPLAYDATE(PRDS.START_DATE),
FND_DATE.DATE_TO_DISPLAYDATE(PRDS.END_DATE),
NULL COL5
FROM
(
(SELECT VALUE,
ELEMENT_TYPE_ID,
CASE WHEN (LEAD(START_DATE, 1) OVER
(PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) IS NULL
AND START_DATE > :p_start_date
AND (row_number() over (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) =1
THEN :p_start_date
WHEN START_DATE > :p_start_date AND (ROW_NUMBER() OVER (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) = 1
THEN :p_start_date
END START_DATE,
CASE WHEN (LEAD(START_DATE, 1) OVER
(PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) IS NULL
AND START_DATE > :p_start_date
AND (row_number() over (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) =1
THEN START_DATE -1
WHEN START_DATE > :p_start_date AND (ROW_NUMBER() OVER (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) = 1
THEN START_DATE -1
END END_DATE
FROM
(
SELECT HCPV.VALUE,
PEC.ELEMENT_TYPE_ID,
PEC.COMMITMENT_START_DATE START_DATE,
PEC.COMMITMENT_END_DATE END_DATE
FROM PQH_ELEMENT_COMMITMENTS PEC,
per_all_assignments_f PAAF,
HRI_CL_PER_V HCPV
WHERE PEC.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF.PERSON_ID = HCPV.ID
AND PEC.COMMITMENT_START_DATE <= :p_end_date
AND PEC.COMMITMENT_END_DATE >= :p_start_date
AND :p_start_date BETWEEN hcpv.EFFECTIVE_START_DATE AND hcpv.EFFECTIVE_END_DATE
AND :p_start_date BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
ORDER BY PEC.COMMITMENT_START_DATE ))
UNION
(SELECT VALUE,
ELEMENT_TYPE_ID,
CASE WHEN END_DATE +1 < NVL((LEAD(START_DATE, 1) OVER
(PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)), :p_end_date)
THEN END_DATE +1
WHEN START_DATE > :p_start_date AND (ROW_NUMBER() OVER (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) = 1
THEN :p_start_date
END EFFECTIVE_START_DATE,
CASE WHEN END_DATE +1 < NVL((LEAD(START_DATE, 1) OVER
(PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)), :p_end_date)
THEN NVL((LEAD(START_DATE, 1) OVER
(PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) - 1, :p_end_date)
WHEN START_DATE > :p_start_date AND (ROW_NUMBER() OVER (PARTITION BY VALUE,ELEMENT_TYPE_ID ORDER BY START_DATE)) = 1
THEN START_DATE -1
END EFFECTIVE_END_DATE
FROM
(
SELECT HCPV.VALUE,
PEC.ELEMENT_TYPE_ID,
PEC.COMMITMENT_START_DATE START_DATE,
PEC.COMMITMENT_END_DATE END_DATE
FROM PQH_ELEMENT_COMMITMENTS PEC,
per_all_assignments_f PAAF,
HRI_CL_PER_V HCPV
WHERE PEC.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND PAAF.PERSON_ID = HCPV.ID
AND PEC.COMMITMENT_START_DATE <= :p_end_date
AND PEC.COMMITMENT_END_DATE >= :p_start_date
AND :p_start_date BETWEEN hcpv.EFFECTIVE_START_DATE AND hcpv.EFFECTIVE_END_DATE
AND :p_start_date BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
ORDER BY PEC.COMMITMENT_START_DATE )) ) PRDS,
PAY_ELEMENT_TYPES_F ELE
WHERE PRDS.START_DATE IS NOT NULL
AND ELE.ELEMENT_TYPE_ID = PRDS.ELEMENT_TYPE_ID
AND :p_start_date BETWEEN ele.EFFECTIVE_START_DATE AND ele.EFFECTIVE_END_DATE
ORDER BY PRDS.VALUE';
SELECT DISTINCT FULL_NAME,
NAME,
FND_DATE.DATE_TO_DISPLAYDATE(START_DATE),
FND_DATE.DATE_TO_DISPLAYDATE(END_DATE),
NULL COL5
FROM
(
SELECT PPF.FULL_NAME,
ORG_TL.NAME,
FND_DATE.CANONICAL_TO_DATE(ORG_INFO2.ORG_INFORMATION3) START_DATE,
FND_DATE.CANONICAL_TO_DATE(ORG_INFO2.ORG_INFORMATION4) END_DATE
FROM HR_ALL_ORGANIZATION_UNITS ORG,
HR_ALL_ORGANIZATION_UNITS_TL ORG_TL,
HR_ORGANIZATION_INFORMATION ORG_INFO2,
PER_ALL_PEOPLE_F PPF
WHERE ORG_INFO2.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG_INFO2.ORG_INFORMATION_CONTEXT = ''Organization Name Alias''
AND TO_NUMBER(ORG_INFO2.ORG_INFORMATION2) = PPF.PERSON_ID
AND ORG.ORGANIZATION_ID = ORG_TL.ORGANIZATION_ID
AND ORG_TL.LANGUAGE = USERENV(''LANG'')
AND EXISTS (SELECT NULL
FROM HR_ORG_INFO_TYPES_BY_CLASS OITBC,
HR_ORGANIZATION_INFORMATION ORG_INFO
WHERE ORG_INFO.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG_INFO.ORG_INFORMATION_CONTEXT = ''CLASS''
AND ORG_INFO.ORG_INFORMATION2 = ''Y''
AND OITBC.ORG_CLASSIFICATION = ORG_INFO.ORG_INFORMATION1
AND OITBC.ORG_INFORMATION_TYPE = ''Organization Name Alias'')
)
WHERE START_DATE <= :p_end_date
AND NVL(END_DATE,:p_start_date) >= :p_start_date
ORDER BY FULL_NAME
';
SELECT PBG.NAME,
HR_GENERAL.DECODE_ORGANIZATION(ORGS.ORGANIZATION_ID),
NULL COL3,
NULL COL4,
NULL COL5
FROM
(
SELECT /*+ ordered*/
PBD.ORGANIZATION_ID,
PB.BUSINESS_GROUP_ID ID
FROM (Select BUSINESS_GROUP_ID,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD IN (''MONEY'',''HEAD'')) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
MINUS
SELECT ORGANIZATION_ID,
BUSINESS_GROUP_ID ID
FROM
(
SELECT ORG.ORGANIZATION_ID,
ORG.BUSINESS_GROUP_ID,
FND_DATE.CANONICAL_TO_DATE(ORG_INFO2.ORG_INFORMATION3) START_DATE,
FND_DATE.CANONICAL_TO_DATE(ORG_INFO2.ORG_INFORMATION4) END_DATE
FROM HR_ALL_ORGANIZATION_UNITS ORG,
HR_ORGANIZATION_INFORMATION ORG_INFO2,
PER_ALL_PEOPLE_F PPF
WHERE ORG_INFO2.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG_INFO2.ORG_INFORMATION_CONTEXT = ''Organization Name Alias''
AND TO_NUMBER(ORG_INFO2.ORG_INFORMATION2) = PPF.PERSON_ID
AND EXISTS (SELECT NULL
FROM HR_ORG_INFO_TYPES_BY_CLASS OITBC,
HR_ORGANIZATION_INFORMATION ORG_INFO
WHERE ORG_INFO.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG_INFO.ORG_INFORMATION_CONTEXT = ''CLASS''
AND ORG_INFO.ORG_INFORMATION2 = ''Y''
AND OITBC.ORG_CLASSIFICATION = ORG_INFO.ORG_INFORMATION1
AND OITBC.ORG_INFORMATION_TYPE = ''Organization Name Alias'')
)
WHERE START_DATE <= :p_end_date
AND NVL(END_DATE,:p_start_date) >= :p_start_date
) ORGS,
PER_BUSINESS_GROUPS_PERF PBG
WHERE ORGS.ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
SELECT DISTINCT usr.user_name,
FND_DATE.DATE_TO_DISPLAYDATE(usr.start_date),
NULL COL3,
NULL COL4,
NULL COL5
FROM FND_USER usr,
WF_USER_ROLE_ASSIGNMENTS waur,
WF_LOCAL_ROLES wlr,
FND_RESPONSIBILITY resp
WHERE resp.responsibility_id = wlr.orig_system_id
AND resp.responsibility_key = ''HRI_DBI_WRKFC_BDGT_MANAGER''
AND wlr.orig_system = ''FND_RESP''
AND usr.user_name = waur.user_name
AND waur.role_name = wlr.name
AND sysdate BETWEEN usr.start_date AND NVL(usr.end_date,hr_general.end_of_time)
AND sysdate BETWEEN resp.start_date and NVL(resp.end_date, hr_general.end_of_time)
ORDER BY usr.user_name
';
SELECT DISTINCT usr.user_name,
FND_DATE.DATE_TO_DISPLAYDATE(usr.start_date),
null col3,
null col4,
null col5
FROM FND_USER usr,
WF_USER_ROLE_ASSIGNMENTS waur,
WF_LOCAL_ROLES wlr,
FND_RESPONSIBILITY resp
WHERE resp.responsibility_id = wlr.orig_system_id
AND resp.responsibility_key = ''HRI_DBI_WRKFC_BDGT_MANAGER''
AND wlr.orig_system = ''FND_RESP''
AND usr.user_name = waur.user_name
AND waur.role_name = wlr.name
AND usr.employee_id IS NULL
AND sysdate BETWEEN usr.start_date AND NVL(usr.end_date,hr_general.end_of_time)
AND sysdate BETWEEN resp.start_date and NVL(resp.end_date, hr_general.end_of_time)
ORDER BY usr.user_name
';
SELECT DISTINCT usr.user_name,
FND_DATE.DATE_TO_DISPLAYDATE(usr.start_date),
null col3,
null col4,
null col5
FROM FND_USER usr,
WF_USER_ROLE_ASSIGNMENTS waur,
WF_LOCAL_ROLES wlr,
FND_RESPONSIBILITY resp
WHERE resp.responsibility_id = wlr.orig_system_id
AND resp.responsibility_key = ''HRI_DBI_WRKFC_BDGT_MANAGER''
AND wlr.orig_system = ''FND_RESP''
AND usr.user_name = waur.user_name
AND waur.role_name = wlr.name
AND usr.employee_id IS NOT NULL
AND NOT EXISTS (SELECT null FROM hri_cs_suph sup WHERE sup.sup_person_id = usr.employee_id)
AND sysdate BETWEEN usr.start_date AND NVL(usr.end_date,hr_general.end_of_time)
AND sysdate BETWEEN resp.start_date and NVL(resp.end_date, hr_general.end_of_time)
ORDER BY usr.user_name
';
SELECT PBG.NAME,
PAPF.PAYROLL_NAME,
FND_DATE.DATE_TO_DISPLAYDATE(MAX(PPA.EFFECTIVE_DATE)),
NULL COL4,
NULL COL5
FROM
(
SELECT BUSINESS_GROUP_ID, PAYROLL_ID, EFFECTIVE_DATE
FROM PAY_PAYROLL_ACTIONS
WHERE ACTION_TYPE = ''R''
AND ACTION_STATUS = ''C''
) PPA,
PER_BUSINESS_GROUPS PBG,
PAY_ALL_PAYROLLS_F PAPF
WHERE PPA.BUSINESS_GROUP_ID =PBG.BUSINESS_GROUP_ID
AND PAPF.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
GROUP BY PBG.NAME,
PAPF.PAYROLL_NAME
';
SELECT /*+ ordered*/
PBG.NAME,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
HR_GENERAL.DECODE_ORGANIZATION(PBD.ORGANIZATION_ID),
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE) ||'' '' ||
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
SUM (
CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT1_VALUE
WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT2_VALUE
WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT3_VALUE
END )BUDGET_VALUE
FROM (Select BUSINESS_GROUP_ID,
BUDGET_NAME,
BUDGET_START_DATE,
BUDGET_END_DATE,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD,
PER_BUSINESS_GROUPS_PERF PBG
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
GROUP BY PBG.NAME,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
HR_GENERAL.DECODE_ORGANIZATION(PBD.ORGANIZATION_ID),
PB.BUDGET_START_DATE,
PB.BUDGET_END_DATE
ORDER BY PBG.NAME
';
SELECT /*+ ordered*/
PBG.NAME,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
HR_GENERAL.DECODE_POSITION_LATEST_NAME(PBD.POSITION_ID),
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE) ||'' '' ||
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT1_VALUE
WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT2_VALUE
WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT3_VALUE
END BUDGET_VALUE
FROM (Select BUSINESS_GROUP_ID,
BUDGET_NAME,
BUDGET_START_DATE,
BUDGET_END_DATE,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD = ''POSITION''
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''HEAD'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD,
PER_BUSINESS_GROUPS_PERF PBG
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
SELECT /*+ ordered*/
PBG.NAME,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
HR_GENERAL.DECODE_ORGANIZATION(PBD.ORGANIZATION_ID),
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE) ||'' '' ||
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID))||'' ''||
TO_CHAR(
SUM(
CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT1_VALUE
WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT2_VALUE
WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT3_VALUE
END),
FND_CURRENCY.GET_FORMAT_MASK(NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID)),30)
) BUDGET_VALUE
FROM (Select BUSINESS_GROUP_ID,
BUDGET_NAME,
CURRENCY_CODE,
BUDGET_START_DATE,
BUDGET_END_DATE,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD,
PER_BUSINESS_GROUPS_PERF PBG
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
GROUP BY PBG.NAME,
PB.CURRENCY_CODE,
PB.BUDGET_ID,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
HR_GENERAL.DECODE_ORGANIZATION(PBD.ORGANIZATION_ID),
PB.BUDGET_START_DATE,
PB.BUDGET_END_DATE
ORDER BY PBG.NAME
';
SELECT /*+ ordered*/
PBG.NAME,
PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'',
HR_GENERAL.DECODE_POSITION_LATEST_NAME(PBD.POSITION_ID),
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_START_DATE) ||'' '' ||
FND_DATE.DATE_TO_DISPLAYDATE(PB.BUDGET_END_DATE),
NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID))||'' ''||
TO_CHAR(
CASE WHEN PB.BUDGET_UNIT1_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT1_VALUE
WHEN PB.BUDGET_UNIT2_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT2_VALUE
WHEN PB.BUDGET_UNIT3_ID = PST.SHARED_TYPE_ID THEN PBD.BUDGET_UNIT3_VALUE
END,
FND_CURRENCY.GET_FORMAT_MASK(NVL(PB.CURRENCY_CODE, PQH_BUDGET.GET_CURRENCY_CD(PB.BUDGET_ID)),30)
) BUDGET_VALUE
FROM (Select BUSINESS_GROUP_ID,
BUDGET_NAME,
CURRENCY_CODE,
BUDGET_START_DATE,
BUDGET_END_DATE,
BUDGET_ID,
BUDGET_UNIT1_ID,
BUDGET_UNIT2_ID,
BUDGET_UNIT3_ID
from PQH_BUDGETS
WHERE POSITION_CONTROL_FLAG = ''Y''
AND BUDGETED_ENTITY_CD =''POSITION''
AND BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date )PB,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD =''MONEY'' ) PST,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD,
PER_BUSINESS_GROUPS_PERF PBG
WHERE PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND PB.BUSINESS_GROUP_ID = PBG.BUSINESS_GROUP_ID
ORDER BY PBG.NAME
';
SELECT DISTINCT PB.BUDGET_NAME ||'' (''|| PBV.VERSION_NUMBER ||'')'' ,
HRI_APL_DGNSTC_WBM.GET_BSET_NAME(BSET.BUDGET_SET_ID,PB.BUSINESS_GROUP_ID) ,
HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELE.ELEMENT_TYPE_ID,:p_start_date) ,
HRI_APL_DGNSTC_WBM.GET_FSC_NAME(SRC.COST_ALLOCATION_KEYFLEX_ID) ,
(src.distribution_percentage * ele.distribution_percentage / 100 )
FROM PQH_BUDGETS PB,
PQH_BUDGET_VERSIONS PBV,
PQH_BUDGET_DETAILS PBD,
(SELECT SHARED_TYPE_ID
FROM PER_SHARED_TYPES
WHERE LOOKUP_TYPE = ''BUDGET_MEASUREMENT_TYPE''
AND SYSTEM_TYPE_CD=''MONEY'' ) PST,
PQH_BUDGET_DETAILS DET,
PQH_BUDGET_PERIODS PRD,
PER_TIME_PERIODS PTPS,
PER_TIME_PERIODS PTPE,
PQH_BUDGET_SETS BSET,
PQH_BUDGET_ELEMENTS ELE,
PQH_BUDGET_FUND_SRCS SRC
WHERE PB.POSITION_CONTROL_FLAG = ''Y''
AND PB.BUDGET_ID = PBV.BUDGET_ID
AND PBV.BUDGET_VERSION_ID = PBD.BUDGET_VERSION_ID
AND PB.BUDGETED_ENTITY_CD IN (''ORGANIZATION'',''POSITION'')
AND PB.BUDGET_START_DATE <= :p_end_date
AND PB.BUDGET_END_DATE >= :p_start_date
AND PST.SHARED_TYPE_ID IN (PB.BUDGET_UNIT1_ID, PB.BUDGET_UNIT2_ID, PB.BUDGET_UNIT3_ID)
AND PBV.BUDGET_VERSION_ID =( SELECT MAX(BUDGET_VERSION_ID)
FROM PQH_BUDGET_VERSIONS
WHERE BUDGET_ID = PB.BUDGET_ID )
AND DET.BUDGET_VERSION_ID = PBV.BUDGET_VERSION_ID
AND DET.BUDGET_DETAIL_ID = PRD.BUDGET_DETAIL_ID
AND PRD.BUDGET_PERIOD_ID = BSET.BUDGET_PERIOD_ID
AND PRD.START_TIME_PERIOD_ID = PTPS.TIME_PERIOD_ID
AND PRD.END_TIME_PERIOD_ID = PTPE.TIME_PERIOD_ID
AND BSET.BUDGET_SET_ID = ELE.BUDGET_SET_ID
AND ELE.BUDGET_ELEMENT_ID = SRC.BUDGET_ELEMENT_ID
ORDER BY HRI_APL_DGNSTC_WBM.GET_BSET_NAME(BSET.BUDGET_SET_ID,PB.BUSINESS_GROUP_ID) ,
HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELE.ELEMENT_TYPE_ID,:p_start_date)
';
SELECT HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELEMENT_TYPE_ID,:p_start_date),
NULL COL2,
NULL COL3,
NULL COL4,
NULL COL5
FROM
(
SELECT ELEMENT_TYPE_ID
FROM HRI_MB_BDGTS_CT
WHERE BUDGET_START_DATE <= :p_end_date
AND BUDGET_END_DATE >= :p_start_date
AND BUDGET_MEASUREMENT_TYPE = ''MONEY''
MINUS
SELECT ELEMENT_TYPE_ID
FROM HRI_MB_ACTLS_CT
WHERE EFFECTIVE_DATE <= :p_end_date
AND EFFECTIVE_DATE >= :p_start_date
)
ORDER BY HRI_APL_DGNSTC_WBM.GET_ELE_NAME(ELEMENT_TYPE_ID,:p_start_date)
';
SELECT PBG.NAME,
PAPF.PAYROLL_NAME,
FND_DATE.DATE_TO_DISPLAYDATE(MAX(PPA.EFFECTIVE_DATE)),
NULL COL4,
NULL COL5
FROM
(
SELECT BUSINESS_GROUP_ID,
PAYROLL_ID,
EFFECTIVE_DATE
FROM PAY_PAYROLL_ACTIONS
WHERE ACTION_TYPE = ''C''
AND ACTION_STATUS = ''C''
) PPA,
PER_BUSINESS_GROUPS PBG,
PAY_ALL_PAYROLLS_F PAPF
WHERE PPA.BUSINESS_GROUP_ID =PBG.BUSINESS_GROUP_ID
AND PAPF.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
GROUP BY PBG.NAME,
PAPF.PAYROLL_NAME
';
SELECT PDBS.DFLT_BUDGET_SET_NAME INTO l_bset_name
FROM PQH_DFLT_BUDGET_SETS PDBS,
PQH_BUDGET_SETS PBS
WHERE PBS.BUDGET_SET_ID = p_bset_id
AND PDBS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PBS.DFLT_BUDGET_SET_ID = PDBS.DFLT_BUDGET_SET_ID;
SELECT ELEMENT_NAME INTO l_ele_name
FROM PAY_ELEMENT_TYPES_F_TL
WHERE ELEMENT_TYPE_ID = p_element_id
AND LANGUAGE = USERENV('LANG') ;
SELECT CONCATENATED_SEGMENTS INTO l_fsc_name
FROM PAY_COST_ALLOCATION_KEYFLEX
WHERE COST_ALLOCATION_KEYFLEX_ID = p_cost_allocation_keyflex_id;