[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
UPPER(PARAMETER_VALUE)
FROM
PAY_ACTION_PARAMETERS
WHERE PARAMETER_NAME = 'TRACE';
SELECT
NAME
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = CP_GRE;
SELECT
1
FROM
PAY_BAL_ATTRIBUTE_DEFINITIONS
WHERE ATTRIBUTE_NAME in ( 'PAY_CA_BPS_SEEDED_BALANCES' , 'PAY_CA_BPS_SUPP_EARNINGS' )
AND LEGISLATION_CODE = 'CA';
SELECT
TO_CHAR(TRUNC(FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10))
,'MM')
,'DD/MM'),
TO_CHAR(LAST_DAY(FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10)))
,'DD/MM'),
LAST_DAY(FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10))),
FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10)),
LAST_DAY(FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10)))
INTO
L_START_DATE
,L_END_DATE
,L_END_DATE2
,LD_REF_MON_ST_DATE
,LD_REF_MON_END_DATE
FROM
DUAL;
SELECT
PROVINCE_NAME
INTO
L_PROVINCE_NAME
FROM
PAY_CA_PROVINCES_V
WHERE PROVINCE_ABBREV = P_PROVINCE;
SELECT
LOCATION_CODE
INTO
L_LOCATION_CODE
FROM
HR_LOCATIONS
WHERE LOCATION_ID = P_LOCATION;
SELECT
ORG_INFORMATION5
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORGANIZATION_ID = CP_GRE
AND ORG_INFORMATION_CONTEXT = 'Canada Employer Identification';
SELECT
LOCATION_CODE
INTO
L_LOCATION_CODE
FROM
HR_LOCATIONS
WHERE LOCATION_ID = TO_NUMBER(PROV_OR_LOC);
SELECT
nvl(count(*),
0)
INTO
L_COUNT
FROM
( SELECT
PPF.PERSON_ID,
PP.PAYROLL_NAME,
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'HOURLY'
,'ANNUAL'
,'SALARIED'
,'MONTHLY'
,'SALARIED'
,'PERIOD'
,'SALARIED'
,'OTHER') PAY_BASIS,
PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE('Regular Earnings'
,'PTD'
,MAX(PAA.ASSIGNMENT_ACTION_ID)
,PAA.ASSIGNMENT_ID
,NULL
,PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
,PAA.TAX_UNIT_ID
,PPA.BUSINESS_GROUP_ID
,NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
,NULL)) REGULAR_GROSS
FROM
HR_LOCATIONS_ALL HL,
PER_ALL_PEOPLE_F PPF,
PER_PAY_BASES PPB,
PER_ALL_ASSIGNMENTS_F PAF,
PAY_PAYROLLS_F PP,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA,
PER_TIME_PERIODS PTP
WHERE PTP.TIME_PERIOD_ID in (
SELECT
MAX(PTP2.TIME_PERIOD_ID)
FROM
PER_TIME_PERIODS PTP2,
PAY_ALL_PAYROLLS_F PPF2
WHERE TO_CHAR(PTP2.END_DATE
,'YYYY/MM') = TO_CHAR(FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10))
,'YYYY/MM')
AND PPF2.PAYROLL_ID = PTP2.PAYROLL_ID
AND PPF2.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
GROUP BY
PPF2.PAYROLL_ID,
PTP2.PERIOD_TYPE )
AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PPA.EFFECTIVE_DATE between PTP.START_DATE
AND PTP.END_DATE
AND PPA.ACTION_TYPE in ( 'R' , 'Q' )
AND PPA.ACTION_STATUS = 'C'
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.BUSINESS_GROUP_ID + 0 = NVL(P_BUSINESS_GROUP_ID
,PPA.BUSINESS_GROUP_ID)
AND EXISTS (
SELECT
'X'
FROM
PAY_PAYROLL_ACTIONS PPA2,
PAY_RUN_TYPES_F PRT
WHERE PPA2.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND NVL(PPA2.RUN_TYPE_ID
,-1) = PRT.RUN_TYPE_ID
AND SUBSTR(PRT.SHORTNAME
,1
,1) in ( 'R' , 'T' ) )
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ID = PP.PAYROLL_ID
AND PPA.EFFECTIVE_DATE between PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PAA.TAX_UNIT_ID = NVL(GRE
,PAA.TAX_UNIT_ID)
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE between PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAF.PAY_BASIS_ID = ppb.pay_basis_id (+)
AND PAF.LOCATION_ID = HL.LOCATION_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PPA.EFFECTIVE_DATE between PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND HL.REGION_1 = NVL(CP_PROVINCE
,HL.REGION_1)
AND PAF.LOCATION_ID = NVL(TO_NUMBER(CP_LOCATION)
,PAF.LOCATION_ID)
GROUP BY
PPF.PERSON_ID,
PP.PAYROLL_NAME,
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'HOURLY'
,'ANNUAL'
,'SALARIED'
,'MONTHLY'
,'SALARIED'
,'PERIOD'
,'SALARIED'
,'OTHER'),
PAA.ASSIGNMENT_ID,
PAA.TAX_UNIT_ID,
PPA.BUSINESS_GROUP_ID ) BAL_TAB
WHERE BAL_TAB.PAYROLL_NAME = PP_PAYROLL_NAME
AND BAL_TAB.PAY_BASIS = PP_PAY_BASIS
AND BAL_TAB.REGULAR_GROSS > 0;
SELECT
nvl(count(*),
0)
INTO
L_COUNT
FROM
( SELECT
PPF.PERSON_ID,
PP.PAYROLL_NAME,
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'HOURLY'
,'ANNUAL'
,'SALARIED'
,'MONTHLY'
,'SALARIED'
,'PERIOD'
,'SALARIED'
,'OTHER') PAY_BASIS,
PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE('Regular Earnings'
,'PTD'
,MAX(PAA.ASSIGNMENT_ACTION_ID)
,PAA.ASSIGNMENT_ID
,NULL
,PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
,PAA.TAX_UNIT_ID
,PPA.BUSINESS_GROUP_ID
,NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
,NULL)) REGULAR_GROSS
FROM
HR_LOCATIONS_ALL HL,
PER_ALL_PEOPLE_F PPF,
PER_PAY_BASES PPB,
PER_ALL_ASSIGNMENTS_F PAF,
PAY_PAYROLLS_F PP,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA,
PER_TIME_PERIODS PTP
WHERE PTP.TIME_PERIOD_ID in (
SELECT
MAX(PTP2.TIME_PERIOD_ID)
FROM
PER_TIME_PERIODS PTP2,
PAY_ALL_PAYROLLS_F PPF2
WHERE TO_CHAR(PTP2.END_DATE
,'YYYY/MM') = TO_CHAR(FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10))
,'YYYY/MM')
AND PPF2.PAYROLL_ID = PTP2.PAYROLL_ID
AND PPF2.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
GROUP BY
PPF2.PAYROLL_ID,
PTP2.PERIOD_TYPE )
AND PPA.TIME_PERIOD_ID = PTP.TIME_PERIOD_ID
AND PPA.EFFECTIVE_DATE between PTP.START_DATE
AND PTP.END_DATE
AND PPA.ACTION_TYPE in ( 'R' , 'Q' )
AND PPA.ACTION_STATUS = 'C'
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.BUSINESS_GROUP_ID + 0 = NVL(P_BUSINESS_GROUP_ID
,PPA.BUSINESS_GROUP_ID)
AND EXISTS (
SELECT
'X'
FROM
PAY_PAYROLL_ACTIONS PPA2,
PAY_RUN_TYPES_F PRT
WHERE PPA2.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND NVL(PPA2.RUN_TYPE_ID
,-1) = PRT.RUN_TYPE_ID
AND SUBSTR(PRT.SHORTNAME
,1
,1) in ( 'R' , 'T' ) )
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ID = PP.PAYROLL_ID
AND PPA.EFFECTIVE_DATE between PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PAA.TAX_UNIT_ID = NVL(GRE
,PAA.TAX_UNIT_ID)
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE between PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PAF.LOCATION_ID = HL.LOCATION_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PPA.EFFECTIVE_DATE between PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND HL.REGION_1 = NVL(CP_PROVINCE
,HL.REGION_1)
AND PAF.LOCATION_ID = NVL(TO_NUMBER(CP_LOCATION)
,PAF.LOCATION_ID)
GROUP BY
PPF.PERSON_ID,
PP.PAYROLL_NAME,
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'HOURLY'
,'ANNUAL'
,'SALARIED'
,'MONTHLY'
,'SALARIED'
,'PERIOD'
,'SALARIED'
,'OTHER'),
PAA.ASSIGNMENT_ID,
PAA.TAX_UNIT_ID,
PPA.BUSINESS_GROUP_ID ) BAL_TAB
WHERE BAL_TAB.PAYROLL_NAME = PP_PAYROLL_NAME
AND BAL_TAB.PAY_BASIS = PP_PAY_BASIS
AND BAL_TAB.REGULAR_GROSS > 0;
SELECT
nvl(count(*),
0)
INTO
L_PARTTIME_COUNT
FROM
( SELECT
PPF.PERSON_ID,
PP.PAYROLL_NAME,
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'HOURLY'
,'ANNUAL'
,'SALARIED'
,'MONTHLY'
,'SALARIED'
,'PERIOD'
,'SALARIED'
,'OTHER') PAY_BASIS,
PTP.PERIOD_TYPE,
PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE(DECODE(PPB.PAY_BASIS
,'HOURLY'
,'Regular and Overtime Hours'
,'ANNUAL'
,'Regular Salary Hours'
,'MONTHLY'
,'Regular Salary Hours'
,'PERIOD'
,'Regular Salary Hours')
,'PTD'
,MAX(PAA.ASSIGNMENT_ACTION_ID)
,PAA.ASSIGNMENT_ID
,NULL
,PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
,PAA.TAX_UNIT_ID
,PPA.BUSINESS_GROUP_ID
,NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
,NULL)) REGULAR_HOURS,
PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE('Regular Earnings'
,'PTD'
,MAX(PAA.ASSIGNMENT_ACTION_ID)
,PAA.ASSIGNMENT_ID
,NULL
,PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
,PAA.TAX_UNIT_ID
,PPA.BUSINESS_GROUP_ID
,NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
,NULL)) REGULAR_GROSS,
ROUND(((PTP.END_DATE - PTP.START_DATE + 1) / 7)
,2) WEEKS
FROM
HR_LOCATIONS_ALL HL,
PER_ALL_PEOPLE_F PPF,
PER_PAY_BASES PPB,
PER_ALL_ASSIGNMENTS_F PAF,
PAY_PAYROLLS_F PP,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA,
PER_TIME_PERIODS PTP
WHERE PTP.TIME_PERIOD_ID in (
SELECT
MAX(PTP2.TIME_PERIOD_ID)
FROM
PER_TIME_PERIODS PTP2,
PAY_ALL_PAYROLLS_F PPF2
WHERE TO_CHAR(PTP2.END_DATE
,'YYYY/MM') = TO_CHAR(FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10))
,'YYYY/MM')
AND PPF2.PAYROLL_ID = PTP2.PAYROLL_ID
AND PPF2.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
GROUP BY
PPF2.PAYROLL_ID,
PTP2.PERIOD_TYPE )
AND PPA.EFFECTIVE_DATE between PTP.START_DATE
AND PTP.END_DATE
AND PPA.ACTION_TYPE in ( 'R' , 'Q' )
AND PPA.ACTION_STATUS = 'C'
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.BUSINESS_GROUP_ID + 0 = NVL(P_BUSINESS_GROUP_ID
,PPA.BUSINESS_GROUP_ID)
AND EXISTS (
SELECT
'X'
FROM
PAY_PAYROLL_ACTIONS PPA2,
PAY_RUN_TYPES_F PRT
WHERE PPA2.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND NVL(PPA2.RUN_TYPE_ID
,-1) = PRT.RUN_TYPE_ID
AND SUBSTR(PRT.SHORTNAME
,1
,1) in ( 'R' , 'T' ) )
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ID = PP.PAYROLL_ID
AND PPA.EFFECTIVE_DATE between PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PAA.TAX_UNIT_ID = NVL(GRE
,PAA.TAX_UNIT_ID)
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE between PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAF.PAY_BASIS_ID = ppb.pay_basis_id (+)
AND PAF.LOCATION_ID = HL.LOCATION_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PPA.EFFECTIVE_DATE between PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PAF.LOCATION_ID = NVL(TO_NUMBER(CP_LOCATION)
,PAF.LOCATION_ID)
AND HL.REGION_1 = NVL(CP_PROVINCE
,HL.REGION_1)
GROUP BY
PPF.PERSON_ID,
PP.PAYROLL_NAME,
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'HOURLY'
,'ANNUAL'
,'SALARIED'
,'MONTHLY'
,'SALARIED'
,'PERIOD'
,'SALARIED'
,'OTHER'),
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'Regular and Overtime Hours'
,'ANNUAL'
,'Regular Salary Hours'
,'MONTHLY'
,'Regular Salary Hours'
,'PERIOD'
,'Regular Salary Hours'),
PAA.ASSIGNMENT_ID,
PTP.PERIOD_TYPE,
PTP.END_DATE,
PTP.START_DATE,
PAA.TAX_UNIT_ID,
PPA.BUSINESS_GROUP_ID ) BAL_TAB
WHERE BAL_TAB.PAYROLL_NAME = PP_PAYROLL_NAME
AND BAL_TAB.PAY_BASIS = PP_PAY_BASIS
AND BAL_TAB.REGULAR_GROSS > 0
AND ( BAL_TAB.REGULAR_HOURS / BAL_TAB.WEEKS ) < 30;
SELECT
nvl(count(*),
0)
INTO
L_PARTTIME_COUNT
FROM
( SELECT
PPF.PERSON_ID,
PP.PAYROLL_NAME,
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'HOURLY'
,'ANNUAL'
,'SALARIED'
,'MONTHLY'
,'SALARIED'
,'PERIOD'
,'SALARIED'
,'OTHER') PAY_BASIS,
PTP.PERIOD_TYPE,
PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE(DECODE(PPB.PAY_BASIS
,'HOURLY'
,'Regular and Overtime Hours'
,'ANNUAL'
,'Regular Salary Hours'
,'MONTHLY'
,'Regular Salary Hours'
,'PERIOD'
,'Regular Salary Hours')
,'PTD'
,MAX(PAA.ASSIGNMENT_ACTION_ID)
,PAA.ASSIGNMENT_ID
,NULL
,PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
,PAA.TAX_UNIT_ID
,PPA.BUSINESS_GROUP_ID
,NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
,NULL)) REGULAR_HOURS,
PAY_CA_BALANCE_PKG.CALL_CA_BALANCE_GET_VALUE('Regular Earnings'
,'PTD'
,MAX(PAA.ASSIGNMENT_ACTION_ID)
,PAA.ASSIGNMENT_ID
,NULL
,PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('REPORT_LEVEL')
,PAA.TAX_UNIT_ID
,PPA.BUSINESS_GROUP_ID
,NVL(PAY_CA_BALANCE_VIEW_PKG.GET_SESSION_VAR('JURISDICTION_CODE')
,NULL)) REGULAR_GROSS,
ROUND(((PTP.END_DATE - PTP.START_DATE + 1) / 7)
,2) WEEKS
FROM
HR_LOCATIONS_ALL HL,
PER_ALL_PEOPLE_F PPF,
PER_PAY_BASES PPB,
PER_ALL_ASSIGNMENTS_F PAF,
PAY_PAYROLLS_F PP,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA,
PER_TIME_PERIODS PTP
WHERE PTP.TIME_PERIOD_ID in (
SELECT
MAX(PTP2.TIME_PERIOD_ID)
FROM
PER_TIME_PERIODS PTP2,
PAY_ALL_PAYROLLS_F PPF2
WHERE TO_CHAR(PTP2.END_DATE
,'YYYY/MM') = TO_CHAR(FND_DATE.CANONICAL_TO_DATE(SUBSTR(P_CANONICAL_REFERENCE_MONTH
,1
,10))
,'YYYY/MM')
AND PPF2.PAYROLL_ID = PTP2.PAYROLL_ID
AND PPF2.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
GROUP BY
PPF2.PAYROLL_ID,
PTP2.PERIOD_TYPE )
AND PPA.EFFECTIVE_DATE between PTP.START_DATE
AND PTP.END_DATE
AND PPA.ACTION_TYPE in ( 'R' , 'Q' )
AND PPA.ACTION_STATUS = 'C'
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PPA.BUSINESS_GROUP_ID + 0 = NVL(P_BUSINESS_GROUP_ID
,PPA.BUSINESS_GROUP_ID)
AND EXISTS (
SELECT
'X'
FROM
PAY_PAYROLL_ACTIONS PPA2,
PAY_RUN_TYPES_F PRT
WHERE PPA2.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND NVL(PPA2.RUN_TYPE_ID
,-1) = PRT.RUN_TYPE_ID
AND SUBSTR(PRT.SHORTNAME
,1
,1) in ( 'R' , 'T' ) )
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ID = PP.PAYROLL_ID
AND PPA.EFFECTIVE_DATE between PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PAA.TAX_UNIT_ID = NVL(GRE
,PAA.TAX_UNIT_ID)
AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE between PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PAF.LOCATION_ID = HL.LOCATION_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PPA.EFFECTIVE_DATE between PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PAF.LOCATION_ID = NVL(TO_NUMBER(CP_LOCATION)
,PAF.LOCATION_ID)
AND HL.REGION_1 = NVL(CP_PROVINCE
,HL.REGION_1)
GROUP BY
PPF.PERSON_ID,
PP.PAYROLL_NAME,
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'HOURLY'
,'ANNUAL'
,'SALARIED'
,'MONTHLY'
,'SALARIED'
,'PERIOD'
,'SALARIED'
,'OTHER'),
DECODE(PPB.PAY_BASIS
,'HOURLY'
,'Regular and Overtime Hours'
,'ANNUAL'
,'Regular Salary Hours'
,'MONTHLY'
,'Regular Salary Hours'
,'PERIOD'
,'Regular Salary Hours'),
PAA.ASSIGNMENT_ID,
PTP.PERIOD_TYPE,
PTP.END_DATE,
PTP.START_DATE,
PAA.TAX_UNIT_ID,
PPA.BUSINESS_GROUP_ID ) BAL_TAB
WHERE BAL_TAB.PAYROLL_NAME = PP_PAYROLL_NAME
AND BAL_TAB.PAY_BASIS = PP_PAY_BASIS
AND BAL_TAB.REGULAR_GROSS > 0
AND ( BAL_TAB.REGULAR_HOURS / BAL_TAB.WEEKS ) < 30;