[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(COUNT(DISTINCT action_information1),0,'1',
COUNT(DISTINCT action_information1))
FROM pay_action_information pai
WHERE action_information_category = 'IN_24Q_CHALLAN'
AND action_context_type = 'PA'
AND action_information3 = p_gre_org_id
AND action_information2 = p_assess_period
AND pai.action_context_id= p_max_action_id
AND fnd_date.canonical_to_date(pai.action_information5)<=fnd_date.CHARDATE_TO_DATE(SYSDATE);
SELECT COUNT(*)
FROM pay_action_information pai
WHERE action_information_category = 'IN_24Q_DEDUCTEE'
AND action_context_type = 'AAP'
AND action_information3 = p_gre_org_id
AND EXISTS (SELECT 1 FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_max_action_id
AND paa.assignment_action_id = pai.action_context_id)
AND pai.action_information1 = p_challan
AND pay_in_24q_er_returns.get_format_value(NVL(pai.action_information5,'0')) <> '0.00'
-- AND fnd_date.canonical_to_date(pai.action_information4)<=fnd_date.CHARDATE_TO_DATE(SYSDATE)
ORDER BY action_information1, action_information2 ASC;
SELECT SUM (TDS)
, SUM (SUR)
, SUM (EC)
, SUM (INTR)
, SUM (OTH)
FROM ( SELECT DISTINCT pai.action_information1
, NVL(pai.action_information6,0) TDS
, NVL(pai.action_information7,0) SUR
, NVL(pai.action_information8,0) EC
, NVL(pai.action_information9,0) INTR
, NVL(pai.action_information10,0) OTH
FROM pay_action_information pai
WHERE action_information_category = 'IN_24Q_CHALLAN'
AND action_context_type = 'PA'
AND action_information3 = p_gre_org_id
AND action_information2 = p_assess_period
AND pai.action_context_id= p_max_action_id
AND fnd_date.canonical_to_date(pai.action_information5)<=fnd_date.CHARDATE_TO_DATE(SYSDATE));
SELECT SUM(NVL(pai.action_information9,0)) -----total tax paid
,SUM(NVL(pai.action_information6,0))
,SUM(NVL(pai.action_information7,0))
,SUM(NVL(pai.action_information8,0))
FROM pay_action_information pai
WHERE action_information_category ='IN_24Q_DEDUCTEE'
AND action_context_type = 'AAP'
AND action_information3 =p_gre_org_id
AND action_information1=p_challan_number
AND EXISTS ( SELECT 1
FROM pay_assignment_actions paa
WHERE paa.payroll_action_id = p_max_action_id
AND paa.assignment_action_id = pai.action_context_id)
-- AND fnd_date.canonical_to_date(pai.action_information4)<=fnd_date.CHARDATE_TO_DATE(SYSDATE)
ORDER BY action_information1, action_information2 ASC;
SELECT COUNT(*)
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_VIA'
AND action_context_type = 'AAP'
AND action_context_id = p_action_context_id
AND source_id =p_source_id;
SELECT COUNT(*)
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_PERSON'
AND action_context_type = 'AAP'
AND action_information2 = p_assess_period
AND action_information3 = p_gre_org_id
AND action_context_id IN (SELECT MAX(pai.action_context_id)
FROM pay_action_information pai
,pay_assignment_actions paa
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_24Q_PERSON'
AND pai.action_context_type = 'AAP'
AND pai.action_information1 = asg.person_id
AND pai.assignment_id = asg.assignment_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND pai.action_information2 = p_assess_period
AND pai.action_information3 = p_gre_org_id
AND pai.source_id = paa.assignment_action_id
GROUP BY pai.assignment_id,pai.action_information1,pai.action_information9
);
SELECT COUNT(*)
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_PERSON'
AND action_context_type = 'AAP'
AND SUBSTR(action_information2,1,9) = p_assess_year
AND action_information3 = p_gre_org_id
AND assignment_id = p_assignment_id
AND action_context_id IN ( SELECT MAX(action_context_id)
FROM pay_action_information pai
,pay_assignment_actions paa
,per_assignments_f asg
WHERE action_information_category = 'IN_24Q_PERSON'
AND action_context_type = 'AAP'
AND pai.action_information1 = asg.person_id
AND SUBSTR(pai.action_information2,1,9) = p_assess_year
AND pai.action_information3 = p_gre_org_id
AND asg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND pai.source_id = paa.assignment_action_id
AND pai.assignment_id = asg.assignment_id
GROUP BY pai.assignment_id,action_information1,action_information9
);
SELECT NVL(action_information2,0)
,NVL(action_information3,0)
FROM pay_action_information
WHERE action_information_category = p_category
AND action_information1 = p_component_name
AND action_context_id = p_context_id
AND source_id = p_source_id;
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_INCOME_TAX_DF'
AND organization_id = p_gre_org_id;
SELECT NVL(SUM(action_information2),0)
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_SALARY'
AND action_context_type = 'AAP'
AND action_information1 = p_balance
AND action_context_id = p_action_context_id
AND source_id = p_source_id;
SELECT MAX(pai.action_context_id) action_cont_id
,source_id sour_id
FROM pay_action_information pai
,pay_assignment_actions paa
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_24Q_PERSON'
AND pai.action_information3 = p_gre_org_id
AND pai.action_information2 = p_assess_period
AND pai.action_information1 = asg.person_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND asg.assignment_id = pai.assignment_id
AND pai.action_context_type = 'AAP'
AND pai.source_id = paa.assignment_action_id
GROUP BY pai.action_information1,pai.action_information9,source_id;
SELECT address_line_1,
address_line_2,
address_line_3,
loc_information14,
loc_information15,
NVL(hr_general.decode_lookup('IN_STATE_CODES',loc_information16),'^'),
NVL(postal_code,'^'),
NVL(LOC_INFORMATION17,'^'),
NVL(TELEPHONE_NUMBER_1,'^')
FROM hr_locations
WHERE location_id = p_location_id;
SELECT SUBSTR (l_phone,INSTR(l_phone,'-',1,1)+1,INSTR(l_phone,'-',1,2)-INSTR (l_phone,'-',1,1)-1) STD
,SUBSTR (l_phone,INSTR(l_phone,'-',1,2)+1) TELPH
INTO l_std,l_telph
FROM dual;
SELECT sex
,TO_CHAR(MONTHS_BETWEEN(SYSDATE, date_of_birth)) l_age
FROM per_all_people_f
WHERE person_id = p_person_id
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;