[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM GHR_CPDF_TEMP
WHERE REPORT_TYPE = 'GHRCPDFO'
AND SESSION_ID = USERENV('sessionid');
SELECT
DISTINCT
0 LVL,
ORGANIZATION_ID_PARENT ID,
ORG_STRUCTURE_VERSION_ID
FROM
PER_ORG_STRUCTURE_ELEMENTS ELE
WHERE ELE.ORGANIZATION_ID_PARENT NOT IN (
SELECT
ORGANIZATION_ID_CHILD
FROM
PER_ORG_STRUCTURE_ELEMENTS ELE,
PER_ORG_STRUCTURE_VERSIONS VER
WHERE VER.ORGANIZATION_STRUCTURE_ID = P_ORGANIZATION_HIERARCHY
AND VER.ORG_STRUCTURE_VERSION_ID = ELE.ORG_STRUCTURE_VERSION_ID
AND VER.ORG_STRUCTURE_VERSION_ID = P_HIERARCHY_VERSION )
AND ELE.ORG_STRUCTURE_VERSION_ID = P_HIERARCHY_VERSION;
SELECT
DISTINCT
ORG.ORGANIZATION_ID ID,
SUBSTR(PPDK.SEGMENT4
,1
,2) AGENCY,
SUBSTR(PPEI.POEI_INFORMATION5
,1
,18) COMPONENT,
RPAD(NVL(OEI.ORG_INFORMATION11
,HRU.NAME)
,179
,' ') TITLE,
PPDK.SEGMENT4 AGENCY_SUBELEMENT
FROM
PER_ORGANIZATION_UNITS ORG,
HR_ORGANIZATION_INFORMATION OEI,
HR_ORGANIZATION_UNITS HRU,
PER_POSITION_EXTRA_INFO PPEI,
PER_POSITIONS PPOS,
PER_POSITION_DEFINITIONS PPDK
WHERE OEI.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PPOS.ORGANIZATION_ID = OEI.ORGANIZATION_ID
AND PPOS.POSITION_ID = PPEI.POSITION_ID
AND PPDK.POSITION_DEFINITION_ID = PPOS.POSITION_DEFINITION_ID
AND PPEI.INFORMATION_TYPE LIKE 'GHR_US_POS_GRP1'
AND OEI.ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_REPORTING_INFO'
AND OEI.ORGANIZATION_ID = HRU.ORGANIZATION_ID
AND PPDK.SEGMENT4 LIKE P_AGENCY_CODE || NVL(P_AGENCY_SUBELEMENT
,'%')
AND ORG.DATE_FROM <= P_REPORT_DATE
AND HRU.DATE_FROM <= P_REPORT_DATE
AND ( PPOS.DATE_END >= P_REPORT_DATE
OR PPOS.DATE_END IS NULL )
ORDER BY
2,
3;
SELECT
DISTINCT
SUBSTR(PPDK.SEGMENT4
,1
,2) AGENCY,
SUBSTR(PPEI.POEI_INFORMATION5
,1
,18) COMPONENT,
RPAD(NVL(OEI.ORG_INFORMATION11
,HRU.NAME)
,179
,' ') TITLE,
OEI.ORGANIZATION_ID ID,
HRU.ORGANIZATION_ID ORG_ID
FROM
HR_ORGANIZATION_INFORMATION OEI,
HR_ORGANIZATION_UNITS HRU,
PER_POSITION_EXTRA_INFO PPEI,
PER_POSITIONS PPOS,
PER_POSITION_DEFINITIONS PPDK
WHERE OEI.ORGANIZATION_ID = P_ID
AND PPOS.ORGANIZATION_ID = OEI.ORGANIZATION_ID
AND PPOS.POSITION_ID = PPEI.POSITION_ID
AND PPDK.POSITION_DEFINITION_ID = PPOS.POSITION_DEFINITION_ID
AND PPEI.INFORMATION_TYPE LIKE 'GHR_US_POS_GRP1'
AND OEI.ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_REPORTING_INFO'
AND OEI.ORGANIZATION_ID = HRU.ORGANIZATION_ID
AND PPDK.SEGMENT4 LIKE P_AGENCY_CODE || NVL(P_AGENCY_SUBELEMENT
,'%')
AND ( PPOS.DATE_END >= P_REPORT_DATE
OR PPOS.DATE_END IS NULL );
SELECT
DISTINCT
A.ORGANIZATION_ID_CHILD ID,
A.ORGANIZATION_ID_PARENT PARENT_ID,
SUBSTR(PPDK.SEGMENT4
,1
,2) AGENCY,
SUBSTR(PPEI.POEI_INFORMATION5
,1
,18) COMPONENT,
RPAD(NVL(OEI.ORG_INFORMATION11
,HRU.NAME)
,179
,' ') TITLE,
PPDK.SEGMENT4 AGENCY_SUBELEMENT
FROM
PER_ORG_STRUCTURE_ELEMENTS A,
PER_ORG_STRUCTURE_VERSIONS B,
HR_ORGANIZATION_INFORMATION OEI,
HR_ORGANIZATION_UNITS HRU,
PER_POSITION_EXTRA_INFO PPEI,
PER_POSITIONS PPOS,
PER_POSITION_DEFINITIONS PPDK
WHERE A.ORG_STRUCTURE_VERSION_ID = B.ORG_STRUCTURE_VERSION_ID
AND PPOS.ORGANIZATION_ID = OEI.ORGANIZATION_ID
AND OEI.ORGANIZATION_ID = HRU.ORGANIZATION_ID
AND PPDK.POSITION_DEFINITION_ID = PPOS.POSITION_DEFINITION_ID
AND PPOS.POSITION_ID = PPEI.POSITION_ID
AND PPEI.INFORMATION_TYPE LIKE 'GHR_US_POS_GRP1'
AND A.ORG_STRUCTURE_VERSION_ID = P_ORG_STR_VERSION_ID
AND A.ORGANIZATION_ID_PARENT = P_PARENT_ID
AND B.DATE_FROM <= P_REPORT_DATE
AND HRU.DATE_FROM <= P_REPORT_DATE
AND A.ORG_STRUCTURE_ELEMENT_ID IN (
SELECT
ORG_STRUCTURE_ELEMENT_ID
FROM
PER_ORG_STRUCTURE_ELEMENTS
START WITH ORGANIZATION_ID_PARENT = P_PARENT_ID
CONNECT BY PRIOR ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT )
AND OEI.ORGANIZATION_ID = A.ORGANIZATION_ID_CHILD
AND OEI.ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_REPORTING_INFO'
AND PPDK.SEGMENT4 LIKE P_AGENCY_CODE || NVL(P_AGENCY_SUBELEMENT
,'%')
AND ( PPOS.DATE_END >= P_REPORT_DATE
OR PPOS.DATE_END IS NULL )
ORDER BY
2,
3;
INSERT INTO GHR_CPDF_TEMP
(REPORT_TYPE
,SESSION_ID
,AWARD_AMOUNT
,BENEFIT_AMOUNT
,FROM_BASIC_PAY
,FROM_LOCALITY_ADJ
,AGENCY_CODE
,ORGANIZATIONAL_COMPONENT
,FROM_OCC_CODE
,FROM_NATIONAL_IDENTIFIER)
VALUES ('GHRCPDFO'
,USERENV('SESSIONID')
,R_CHILD.ID
,R_CHILD.PARENT_ID
,L_COUNT
,P_LEVEL
,R_CHILD.AGENCY
,R_CHILD.COMPONENT
,SUBSTR(R_CHILD.TITLE
,1
,150)
,SUBSTR(R_CHILD.TITLE
,151));
SELECT
VER.ORG_STRUCTURE_VERSION_ID,
STRUC.BUSINESS_GROUP_ID BUS_ID
INTO L_ORG_STR_VERSION_ID,L_BUS_GROUP_ID
FROM
PER_ORGANIZATION_STRUCTURES STRUC,
PER_ORG_STRUCTURE_VERSIONS VER
WHERE STRUC.ORGANIZATION_STRUCTURE_ID = P_ORGANIZATION_HIERARCHY
AND STRUC.ORGANIZATION_STRUCTURE_ID = VER.ORGANIZATION_STRUCTURE_ID
AND VER.ORG_STRUCTURE_VERSION_ID = P_HIERARCHY_VERSION
AND P_REPORT_DATE >= VER.DATE_FROM
AND P_REPORT_DATE <= DECODE(VER.DATE_TO
,NULL
,P_REPORT_DATE
,VER.DATE_TO);
INSERT INTO GHR_CPDF_TEMP
(REPORT_TYPE
,SESSION_ID
,AWARD_AMOUNT
,BENEFIT_AMOUNT
,FROM_BASIC_PAY
,FROM_LOCALITY_ADJ
,AGENCY_CODE
,ORGANIZATIONAL_COMPONENT
,FROM_OCC_CODE
,FROM_NATIONAL_IDENTIFIER)
VALUES ('GHRCPDFO'
,USERENV('SESSIONID')
,R_PARENT.ID
,NULL
,L_COUNT
,1
,R_DETAIL.AGENCY
,R_DETAIL.COMPONENT
,SUBSTR(R_DETAIL.TITLE
,1
,150)
,SUBSTR(R_DETAIL.TITLE
,151));
INSERT INTO GHR_CPDF_TEMP
(REPORT_TYPE
,SESSION_ID
,AWARD_AMOUNT
,BENEFIT_AMOUNT
,FROM_BASIC_PAY
,AGENCY_CODE
,ORGANIZATIONAL_COMPONENT
,FROM_OCC_CODE
,FROM_NATIONAL_IDENTIFIER)
VALUES ('GHRCPDFO'
,USERENV('SESSIONID')
,R_REGULAR.ID
,NULL
,NULL
,R_REGULAR.AGENCY
,R_REGULAR.COMPONENT
,SUBSTR(R_REGULAR.TITLE
,1
,150)
,SUBSTR(R_REGULAR.TITLE
,151));