[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
23-NOV-2005 rdhingra 115.6 4742356 Updated value of l_countlimit
02-DEC-2005 rdhingra 115.7 4771769 Added CDATA to take care of special char
from different names. Reverted changes of
sql to take care of special char from full_name
19-JAN-2006 rdhingra 115.8 4960092 Modified cursor c_unpay_details in
procedure write_unpay_details to show the
details of unpaid payments at the lowest sort
level
07-Apr-2006 rdhingra 115.9 5148084 Removed xml PI(processing instruction) from
the procedure write_header.
Removed Procedure FETCH_RTF_BLOB
19-Feb-2007 saurgupt 115.10 5862861 Modified procedures WRITE_DETAIL_RECORDS and
WRITE_UNPAY_DETAILS. Added the condition to check
the length of xmlstring to avoid overflow error.
*/
g_proc_name VARCHAR2(240);
SELECT tax_unit_id,
attribute2,
to_number(attribute3),
to_date(attribute4,'MM/DD/YYYY'),
to_date(attribute5,'MM/DD/YYYY')
FROM pay_us_rpt_totals
WHERE organization_id = to_number(p_ppa_finder)
AND attribute1 = 'GTN';
SELECT distinct substr(payroll_name,1,80),
substr('Payroll : '||payroll_name,1,80)
INTO l_payroll_name,
l_print_set_payroll_name
FROM pay_payrolls_f
WHERE payroll_id = l_payroll_id
AND effective_start_date <= l_end_date
AND effective_end_date >= l_end_date;
SELECT consolidation_set_name,
substr('Consolidation Set : '||consolidation_set_name,1,80)
INTO l_consolidation_set_name,
l_print_set_payroll_name
FROM pay_consolidation_sets
WHERE consolidation_set_id = l_consolidation_set_id;
SELECT consolidation_set_name,
substr('Consolidation Set : '||consolidation_set_name,1,80)
INTO l_consolidation_set_name,
l_print_set_payroll_name
FROM pay_consolidation_sets
WHERE consolidation_set_id = l_consolidation_set_id;
SELECT substr(name,1,80)
INTO l_gre_name
FROM hr_organization_units
WHERE organization_id = l_gre_id;
vXMLTable.DELETE;
/*Removed the xml PI(processing instruction) as the core package inserts it*/
vXMLTable(vCtr).xmlstring := '';
SELECT DECODE (LOWER (cp_sort1),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
'GRE Name : ' || gre_name
) sort1_name,
DECODE (LOWER (cp_sort2),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
) sort2_name,
DECODE (LOWER (cp_sort3),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
)sort3_name,
attribute12 emp_name,
TO_NUMBER (attribute2) class_seq1, TO_NUMBER (attribute3) sub_class1,
attribute4 classification1, attribute5 element_name1,
SUM (value2) run_val1, to_number(SUM (value3)) run_hours1, COUNT (*) tot_count1,
to_char(business_group_id) person_id
FROM pay_us_rpt_totals
WHERE cp_sort3 IS NOT NULL AND tax_unit_id = cp_pact_id
AND attribute1 <> 'GTN'
AND cp_asg_flag = 'Y'
GROUP BY DECODE (LOWER (cp_sort1),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
'GRE Name : ' || gre_name
),
DECODE (LOWER (cp_sort2),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
),
DECODE (LOWER (cp_sort3),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
),
to_char(business_group_id),
attribute12,
TO_NUMBER (attribute2),
TO_NUMBER (attribute3),
attribute4,
attribute5
HAVING ( DECODE (SIGN (SUM (value2)),
1, SUM (value2),
-1, -1 * SUM (value2),
0
) > 0
OR DECODE (SIGN (SUM (value3)),
1, SUM (value3),
-1, -1 * SUM (value3),
0
) > 0
)
UNION
SELECT DECODE (LOWER (cp_sort1),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
'GRE Name : ' || gre_name
) sort1_name,
DECODE (LOWER (cp_sort2),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
) sort2_name,
DECODE (LOWER (cp_sort3),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
) sort3_name,
to_char(NULL),
TO_NUMBER (attribute2) class_seq1, TO_NUMBER (attribute3) sub_class1,
attribute4 classification1, attribute5 element_name1,
SUM (value2) run_val1, SUM (value3) run_hours1, COUNT (*) tot_count1,
to_char(NULL) person_id
FROM pay_us_rpt_totals
WHERE cp_sort3 IS NOT NULL AND tax_unit_id = cp_pact_id
AND attribute1 <> 'GTN'
GROUP BY DECODE (LOWER (cp_sort1),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
'GRE Name : ' || gre_name
),
DECODE (LOWER (cp_sort2),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
),
DECODE (LOWER (cp_sort3),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
),
to_char(NULL),
TO_NUMBER (attribute2),
TO_NUMBER (attribute3),
attribute4,
attribute5
HAVING ( DECODE (SIGN (SUM (value2)),
1, SUM (value2),
-1, -1 * SUM (value2),
0
) > 0
OR DECODE (SIGN (SUM (value3)),
1, SUM (value3),
-1, -1 * SUM (value3),
0
) > 0
)
UNION
SELECT DECODE (LOWER (cp_sort1),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
'GRE Name : ' || gre_name
) sort1_name,
DECODE (LOWER (cp_sort2),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
) sort2_name,
to_char(NULL) sort3_name,to_char(NULL) emp_name, TO_NUMBER (attribute2) class_seq1,
TO_NUMBER (attribute3) sub_class1, attribute4 classification1,
attribute5 element_name1, SUM (value2) run_val1,
SUM (value3) run_hours1, COUNT (*) tot_count1,
to_char(NULL) person_id
FROM pay_us_rpt_totals
WHERE cp_sort2 IS NOT NULL AND tax_unit_id = cp_pact_id
AND attribute1 <> 'GTN'
GROUP BY DECODE (LOWER (cp_sort1),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
'GRE Name : ' || gre_name
),
DECODE (LOWER (cp_sort2),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
to_char(NULL)
),
to_char(NULL),
to_char(NULL),
TO_NUMBER (attribute2),
TO_NUMBER (attribute3),
attribute4,
attribute5
HAVING ( DECODE (SIGN (SUM (value2)),
1, SUM (value2),
-1, -1 * SUM (value2),
0
) > 0
OR DECODE (SIGN (SUM (value3)),
1, SUM (value3),
-1, -1 * SUM (value3),
0
) > 0
)
UNION
SELECT DECODE (LOWER (cp_sort1),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
'GRE Name : ' || gre_name
) sort1_name,
to_char(NULL) sort2_name, to_char(NULL) sort3_name,to_char(NULL) emp_name, TO_NUMBER (attribute2) class_seq1,
TO_NUMBER (attribute3) sub_class1, attribute4 classification1,
attribute5 element_name1, SUM (value2) run_val1,
SUM (value3) run_hours1, COUNT (*) tot_count1,
to_char(NULL) person_id
FROM pay_us_rpt_totals
WHERE tax_unit_id = cp_pact_id AND attribute1 <> 'GTN'
GROUP BY DECODE (LOWER (cp_sort1),
'loc', 'Location Name : ' || location_name,
'gre', 'GRE Name : ' || gre_name,
'org', 'Organization Name : ' || organization_name,
'GRE Name : ' || gre_name
),
to_char(NULL),
to_char(NULL),
to_char(NULL),
TO_NUMBER (attribute2),
TO_NUMBER (attribute3),
attribute4,
attribute5
HAVING ( DECODE (SIGN (SUM (value2)),
1, SUM (value2),
-1, -1 * SUM (value2),
0
) > 0
OR DECODE (SIGN (SUM (value3)),
1, SUM (value3),
-1, -1 * SUM (value3),
0
) > 0
)
ORDER BY 1, 2, 3, 4, 5,6;
vXMLTable.DELETE;
/*to be deleted*/
--start
/*
if length(vXMLTable(vCtr).xmlstring) >= 1 then
hr_utility.trace (' length of vXMLTable(vCtr).xmlstring : ' || length(vXMLTable(vCtr).xmlstring) );
SELECT TO_NUMBER (attribute2) CLASS, TO_NUMBER (attribute3) sub_class,
attribute4 classification_r, attribute5 element_name_r,
SUM (value2) run_val_r, SUM (value3) run_hours_r, COUNT(*) run_tot_r
FROM pay_us_rpt_totals
WHERE tax_unit_id = cp_pact_id AND attribute1 <> 'GTN'
GROUP BY TO_NUMBER (attribute2),
TO_NUMBER (attribute3),
attribute4,
attribute5
HAVING ( DECODE (SIGN (SUM (value2)),
1, SUM (value2),
-1, -1 * SUM (value2),
0
) > 0
OR DECODE (SIGN (SUM (value3)),
1, SUM (value3),
-1, -1 * SUM (value3),
0
) > 0
)
ORDER BY 1,2,3,4;
SELECT to_number(attribute3) unpaid_sub_class1,
decode(lower(cp_sort1),
'loc','Location Name : '||location_name,
'gre','GRE Name : '||gre_name ,
'org','Organization Name : '||organization_name,
'GRE Name : '||gre_name) unpaid_sort1_name,
decode(lower(cp_sort2),
'loc','Location Name : '||location_name,
'gre','GRE Name : '||gre_name ,
'org','Organization Name : '||organization_name,
null) unpaid_sort2_name,
decode(lower(cp_sort3),
'loc','Location Name : '||location_name,
'gre','GRE Name : '||gre_name ,
'org','Organization Name : '||organization_name,
null) unpaid_sort3_name,
to_number(attribute2) unpaid_class_seq1,
attribute4 unpaid_classification1,
attribute6 full_name,
attribute7 asg_no,
attribute8 pymt_method_name,
attribute9 account_type,
attribute10 account_number,
attribute11 routing_number,
organization_id aaid,
location_id pre_pay_id
FROM pay_us_rpt_totals
WHERE tax_unit_id = cp_pact_id
AND attribute2 = '10'
AND attribute3 in ('1','2') --Unprocessed/Unpaid Payments
AND organization_id is not null
AND attribute1 = 'MESG-LINE'
ORDER BY 1,2,3,4,5,6,7;
vXMLTable.DELETE;
DELETE FROM pay_us_rpt_totals
WHERE tax_unit_id = l_pact_id;*/