[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT db.defined_balance_id, pbt.balance_name
FROM pay_balance_types pbt,
pay_defined_balances db,
pay_balance_dimensions bd
WHERE pbt.balance_name IN ('RA GRA PLAN BY INST',
'RA GRA PLAN REDUCT',
'RA PLAN DEDUCT',
'RA ADDL DEDUCT',
'RA ADDL REDUCT',
'SRA GSRA REDUCT')
AND bd.dimension_name = 'Assignment Default Run'
AND pbt.balance_type_id = db.balance_type_id
AND bd.balance_dimension_id = db.balance_dimension_id
ORDER BY pbt.balance_name;
SELECT DISTINCT
attribute5
,attribute12
,value9
FROM pay_us_rpt_totals
WHERE tax_unit_id = p_payroll_action_id
AND attribute14 = '999'
AND attribute15 = 'NEGATIVE BALANCE'
AND attribute1 <> 'TIAA-CREF';
SELECT attribute5
,SUM(value2) value2
,SUM(value3) value3
,SUM(value4) value4
,SUM(value5) value5
,SUM(value6) value6
,SUM(value7) value7
FROM pay_us_rpt_totals
WHERE tax_unit_id = c_payroll_action_id
AND attribute1 <> 'TIAA-CREF'
AND attribute5 = c_assignment_id
AND value9 = c_payroll_id
-- AND NVL(attribute12,'X') = NVL(c_payment_mode,'X')
GROUP BY attribute5
HAVING SUM(value2) < 0 OR
SUM(value3) < 0 OR
SUM(value4) < 0 OR
SUM(value5) < 0 OR
SUM(value6) < 0 OR
SUM(value7) < 0;
UPDATE pay_us_rpt_totals
SET attribute14 = NULL
,attribute15 = NULL
WHERE tax_unit_id = p_payroll_action_id
AND attribute5 = rpt_rec.attribute5
AND attribute15 = 'NEGATIVE BALANCE'
AND value9 = rpt_rec.value9;
PROCEDURE insert_rpt_data (p_assignment_id IN NUMBER
,p_assignment_action_id IN NUMBER
,p_dimension_name IN VARCHAR2
,p_effective_date IN DATE
,p_ppa_finder IN VARCHAR2) IS
l_insert_valid BOOLEAN := FALSE;
l_proc_name VARCHAR2(150) := g_proc_name ||'insert_rpt_data';
l_insert_valid := TRUE;
IF l_insert_valid THEN
hr_utility.set_location('..Valid for Assignment : '||p_assignment_id, 20);
INSERT INTO pay_us_rpt_totals
(tax_unit_id,
gre_name,
organization_name,
location_name,
attribute1,
value1,
attribute2,
attribute3,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
value2,
value3,
value4,
value5,
value6,
value7,
organization_id, value8,value9 )
VALUES
(l_payroll_action_id, --tax_unit_id
ins_val_t(i).gre_name, --gre_name
ins_val_t(i).org_name, --org_name
l_location_code, --location_code
'BALANCE', --'BALANCE'
l_payroll_action_id, --value1
'', --attribute2
p_dimension_name, --attribute3
p_assignment_id, --attribute5
ins_val_t(i).last_name, --attribute6
ins_val_t(i).first_name, --attribute7
TO_CHAR(ins_val_t(i).dob,'DD-MON-YYYY'), --attribute8
ins_val_t(i).national_id, --attribute9
ins_val_t(i).asg_ppg_code, --attribute10
ins_val_t(i).org_ppg, --attribute11
ins_val_t(i).pay_mode, --attribute12
ins_val_t(i).middle_name, --attribute13
ins_val_t(i).err_num, --attribute14
ins_val_t(i).err_msg, --attribute15
TO_CHAR(ins_val_t(i).input_start_date,'DD-MON-YYYY'), --attribute16
TO_CHAR(ins_val_t(i).input_date,'DD-MON-YYYY'), --attribute17
TO_CHAR(l_effective_date,'DD-MON-YYYY'), --attribute18
ins_val_t(i).ppg_billing, --attribute19
ins_val_t(i).balance_name1, --attribute21
ins_val_t(i).balance_name2, --attribute22
ins_val_t(i).balance_name3, --attribute23
ins_val_t(i).balance_name4, --attribute24
ins_val_t(i).balance_name5, --attribute25
ins_val_t(i).balance_name6, --attribute26
ins_val_t(i).balance_value1, --value2
ins_val_t(i).balance_value2, --value3
ins_val_t(i).balance_value3, --value4
ins_val_t(i).balance_value4, --value5
ins_val_t(i).balance_value5, --value6
ins_val_t(i).balance_value6, --value7
ins_val_t(i).assignment_action_id, --organization_id
l_chunk_no, --value8
ins_val_t(i).payroll_id ); --value9
hr_utility.set_location('..Inserted for assignment :'||p_assignment_id, 25);
END IF; -- IF l_insert_valid Then
hr_utility.set_location('..After Inserting into pay_us_rpt_totals ', 70);
ins_val_t.DELETE(i);
hr_utility.set_location('..Error in Insert_Rpt_Data :' ||SQLERRM,150);
END insert_rpt_data;
l_update_flag BOOLEAN;
l_insert_valid BOOLEAN;
l_update_flag := FALSE;
l_update_flag := TRUE;
l_update_flag := TRUE;
l_update_flag := TRUE;
l_update_flag := TRUE;
l_update_flag := TRUE;
l_update_flag := TRUE;
IF NOT l_update_flag THEN
hr_utility.set_location('..New balance for the same assignment id :'||l_balance_name, 25);
l_update_flag := TRUE;
IF l_update_flag THEN
ins_val_t(i).asg_ppg_code := l_asg_ppg_code;
IF NOT l_update_flag THEN
i := p_assignment_id;
SELECT prl.prl_information7
FROM pay_payrolls_f prl
WHERE prl.payroll_id = p_payroll_id
AND prl.prl_information_category = 'US'
AND l_effective_date BETWEEN prl.effective_start_date
AND prl.effective_end_date;
SELECT prl.prl_information4
,prl.prl_information7
FROM pay_payrolls_f prl
WHERE prl.payroll_id = p_payroll_id
AND prl.prl_information_category = 'US'
AND l_effective_date BETWEEN prl.effective_start_date
AND prl.effective_end_date;
SELECT paei.aei_information1,
pasg.payroll_id
FROM per_assignment_extra_info paei ,
per_assignments_f pasg
WHERE pasg.assignment_id = p_assignment
AND pasg.assignment_id = paei.assignment_id(+)
AND paei.information_type(+) = 'PQP_US_TIAA_CREF_CODES'
AND l_effective_date BETWEEN pasg.effective_start_date
AND pasg.effective_end_date;
SELECT ppv.last_name,
ppv.first_name,
ppv.middle_names,
ppv.date_of_birth,
ppv.national_identifier
FROM per_all_people_f ppv,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment
AND paf.person_id = ppv.person_id
AND l_actual_date BETWEEN ppv.effective_start_date
AND ppv.effective_end_date
AND l_actual_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT MAX(effective_end_date)
FROM per_assignments_f paf
WHERE paf.assignment_id = p_assignment
AND paf.business_group_id =l_business_group_id;
SELECT org_information1
FROM hr_organization_information
WHERE org_information_context = 'PQP_US_TIAA_CREF_CODES'
AND organization_id = p_tax_unit_id;
SELECT DISTINCT
paa.assignment_id assignment_id,
ppa_gen.start_date start_date,
ppa_gen.effective_date end_date,
ppa_gen.business_group_id business_group_id,
ppa_gen.payroll_action_id payroll_action_id,
ppa.effective_date effective_date,
ppa.action_type action_type,
paa.tax_unit_id tax_unit_id,
hou.name gre_name,
paf.organization_id organization_id,
hox.name organization_name,
paf.location_id location_id,
hrl.location_code location_code,
paa.assignment_action_id assignment_action_id,
ppa.payroll_id pay_payroll_id
FROM hr_locations_all hrl,
hr_organization_units hox,
hr_organization_units hou,
per_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_assignment_actions paa_gen,
pay_payroll_actions ppa_gen
WHERE
ppa_gen.payroll_action_id = l_pactid
AND paa_gen.payroll_action_id = ppa_gen.payroll_action_id
AND paa_gen.chunk_number = l_chnkno
AND pai.locking_action_id = paa_gen.assignment_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND paa.action_status = 'C'
AND paa.tax_unit_id = NVL(t_gre_id,
paa.tax_unit_id)
AND ppa.consolidation_set_id = NVL(t_consolidation_set_id,
ppa.consolidation_set_id)
AND ppa.payroll_id = NVL(t_payroll_id,
ppa.payroll_id)
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','V','Q','B')
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN ppa_gen.start_date
AND ppa_gen.effective_date
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_id = paa.assignment_id
AND paf.business_group_id = ppa_gen.business_group_id
AND hrl.location_id = paf.location_id
AND hox.organization_id = paf.organization_id
AND hou.organization_id = paa.tax_unit_id
ORDER BY paa.assignment_id,ppa.payroll_id, paa.assignment_action_id;
SELECT ppa.legislative_parameters,
ppa.business_group_id,
ppa.start_date,
ppa.effective_date,
pay_paygtn_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
pay_paygtn_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
pay_paygtn_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
ppa.payroll_action_id
INTO l_leg_param,
l_business_group_id,
l_leg_start_date,
l_leg_end_date,
t_consolidation_set_id,
t_payroll_id,
t_gre_id,
t_payroll_action_id
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
INSERT INTO pay_us_rpt_totals
( tax_unit_id, attribute1, organization_id,
attribute2, attribute3, attribute4,
attribute5
)
VALUES
(pactid, 'TIAA-CREF', ppa_finder,
l_leg_param, l_business_group_id, TO_CHAR(l_leg_start_date,'MM/DD/YYYY'),
TO_CHAR(l_leg_end_date,'MM/DD/YYYY')
);
hr_utility.set_location('..Calling INSERT_RPT_DATA within loop ', 55);
insert_rpt_data (p_assignment_id => l_prev_assignment_id
,p_assignment_action_id => l_prev_assignment_action_id
,p_dimension_name => p_dimension_name
,p_effective_date => l_prev_end_date
,p_ppa_finder => ppa_finder);
hr_utility.set_location('..Calling INSERT_RPT_DATA outside loop ', 60);
insert_rpt_data (p_assignment_id => l_prev_assignment_id
,p_assignment_action_id => l_prev_assignment_action_id
,p_dimension_name => p_dimension_name
,p_effective_date => l_prev_end_date
,p_ppa_finder => ppa_finder);
ins_val_t.DELETE;