[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tag
FROM fnd_lookup_values
WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
INSTR(USERENV('LANGUAGE'), '.') + 1)
AND language = 'US';
SELECT PASG.PERSON_ID, PASG.ASSIGNMENT_ID, MAX(PAA.ASSIGNMENT_ACTION_ID) AS ASSIGNMENT_ACTION_ID,
PPA.PAYROLL_ID, MAX(PPA.DATE_EARNED) AS EFFECTIVE_DATE, PAP.PER_INFORMATION3
FROM PER_ALL_PEOPLE_F PAP
,PER_ALL_ASSIGNMENTS_F ASG
,PAY_PAYROLL_ACTIONS PPA
,PAY_ASSIGNMENT_ACTIONS PAA
,PAY_RUN_RESULTS PRR
,PAY_ELEMENT_TYPES_F PET
,PER_ALL_ASSIGNMENTS_F PASG
WHERE PAP.PERSON_ID = ASG.PERSON_ID
AND ASG.PAYROLL_ID = PPA.PAYROLL_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PET.LEGISLATION_CODE = 'DK'
AND PPA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PAA.TAX_UNIT_ID = P_LEGAL_EMPLOYER_ID
AND PET.ELEMENT_NAME IN ('Employee ATP', 'Employer ATP')
AND PPA.DATE_EARNED BETWEEN QUARTER_START AND QUARTER_END
AND QUARTER_END BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
AND PASG.ASSIGNMENT_STATUS_TYPE_ID = 1
AND PAP.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PAP.EFFECTIVE_START_DATE <= QUARTER_END
AND PAP.EFFECTIVE_END_DATE >= QUARTER_START
AND PASG.EFFECTIVE_START_DATE <= QUARTER_END
AND PASG.EFFECTIVE_END_DATE >= QUARTER_START
AND ASG.EFFECTIVE_START_DATE <= QUARTER_END
AND ASG.EFFECTIVE_END_DATE >= QUARTER_START
GROUP BY PASG.PERSON_ID, PASG.ASSIGNMENT_ID, PPA.PAYROLL_ID, PAP.PER_INFORMATION3
ORDER BY PASG.PERSON_ID, PASG.ASSIGNMENT_ID;*/
SELECT
PERSON_ID ,
ASSIGNMENT_ID ,
ASSIGNMENT_ACTION_ID,
PAYROLL_ID,
EFFECTIVE_DATE,
PER_INFORMATION3,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
LEAD (ASSIGNMENT_ID,1) OVER (ORDER BY PERSON_ID, ASSIGNMENT_ID,EFFECTIVE_DATE,EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE) AS LEAD_ASSIGNMENT_ID,
LEAD (EFFECTIVE_DATE,1) OVER (ORDER BY PERSON_ID, ASSIGNMENT_ID,EFFECTIVE_DATE,EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE) AS LEAD_EFFECTIVE_DATE
FROM (
SELECT ASG.PERSON_ID, ASG.ASSIGNMENT_ID, PAA.ASSIGNMENT_ACTION_ID ASSIGNMENT_ACTION_ID,
PPA.PAYROLL_ID, PPA.DATE_EARNED EFFECTIVE_DATE, PAP.PER_INFORMATION3,
PAP.EFFECTIVE_START_DATE,
PAP.EFFECTIVE_END_DATE
FROM PER_ALL_PEOPLE_F PAP
,PER_ALL_ASSIGNMENTS_F ASG
,PAY_PAYROLL_ACTIONS PPA
,PAY_ASSIGNMENT_ACTIONS PAA
,PAY_RUN_RESULTS PRR
,PAY_ELEMENT_TYPES_F PET
WHERE PAP.PERSON_ID = ASG.PERSON_ID
AND ASG.PAYROLL_ID = PPA.PAYROLL_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('R','Q') -- Payroll Run or Quickpay Run
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PET.LEGISLATION_CODE = 'DK'
AND PPA.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PAA.TAX_UNIT_ID = P_LEGAL_EMPLOYER_ID
AND PET.ELEMENT_NAME IN ('Employee ATP', 'Employer ATP')
AND PPA.DATE_EARNED BETWEEN QUARTER_START AND QUARTER_END
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = 1
AND PAP.CURRENT_EMPLOYEE_FLAG = 'Y'
/* Date check for the date tracked tables*/
AND TO_CHAR(PPA.DATE_EARNED,'MM/YYYY') BETWEEN to_char(PAP.EFFECTIVE_START_DATE,'MM/YYYY') AND TO_CHAR(PAP.EFFECTIVE_END_DATE,'MM/YYYY')
AND TO_CHAR (PPA.DATE_EARNED,'MM/YYYY') BETWEEN to_char(ASG.EFFECTIVE_START_DATE,'MM/YYYY') AND TO_CHAR(ASG.EFFECTIVE_END_DATE,'MM/YYYY')
AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
GROUP BY ASG.PERSON_ID, ASG.ASSIGNMENT_ID, PAA.ASSIGNMENT_ACTION_ID,
PPA.PAYROLL_ID, PPA.DATE_EARNED, PAP.PER_INFORMATION3,
PAP.EFFECTIVE_START_DATE,PAP.EFFECTIVE_END_DATE
ORDER BY ASG.PERSON_ID, ASG.ASSIGNMENT_ID,PPA.DATE_EARNED,PAP.EFFECTIVE_START_DATE,
PAP.EFFECTIVE_END_DATE);
SELECT DEFINED_BALANCE_ID
FROM PAY_BALANCE_TYPES PBT
, PAY_DEFINED_BALANCES PDB
, PAY_BALANCE_DIMENSIONS PBD
WHERE PDB.BALANCE_TYPE_ID = PBT.BALANCE_TYPE_ID
AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
AND PBT.BALANCE_NAME = BAL_NAME
AND PBD.DATABASE_ITEM_SUFFIX = DIM_NAME;
SELECT TRIM(GLOBAL_VALUE)
FROM FF_GLOBALS_F GLB
WHERE QUARTER_END BETWEEN GLB.EFFECTIVE_START_DATE AND GLB.EFFECTIVE_END_DATE
AND GLB.GLOBAL_NAME = GLB_NAME
AND GLB.LEGISLATION_CODE = 'DK';
g_xml_element_table.DELETE;
SELECT TRUNC(EFFECTIVE_DATE)
INTO P_EFFECTIVE_DATE
FROM FND_SESSIONS
WHERE SESSION_ID=USERENV('SESSIONID');
/* SQLSTR := 'SELECT '''|| TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_total_atp,2)),0) ,'999G999G990D99' ) || ''' as "TotalATP",'''
|| TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_emp_count,2)),0) ,'999G999G990D99' ) || ''' as "FullTimeEmpCount",'''
|| l_ded_1 ||''' as "Deduction1",'''
|| l_ded_50 ||''' as "Deduction50",'''
|| l_ded_trainee ||''' as "DeductionTrainee",'''
|| TO_CHAR(NVL(FND_NUMBER.canonical_to_number(round(l_tot_emp_aer,2)),0) ,'999G999G990D99' ) ||''' as "TotalAER",'''
|| TO_CHAR(NVL(FND_NUMBER.canonical_to_number(l_tot_qtr_aer),0) ,'999G999G990D99' ) ||''' as "TotalAERQuarter" from dual';