[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
02-DEC-2005 rdhingra 115.5 4769835 Performance changes. Updated Cursor
c_count_asg_processed in procedure
get_941_balances
06-APR-2006 pragupta 115.8 5117504 introduced tags YEAR1 and YEAR2.
30-OCT-2006 alikhar 115.9 5479800 Added procedure pay_us_941_report_wrapper
************************************************************************/
g_CP_STATUS VARCHAR2(20);
SELECT hou.name, -- GRE Name
hoi_ein.org_information1, -- EIN
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
hrl.region_2,
hrl.postal_code
FROM hr_organization_units hou,
hr_organization_information hoi_bg,
hr_organization_information hoi_ein ,
hr_locations hrl
WHERE hou.organization_id = cp_tax_unit_id
AND hoi_bg.organization_id = hou.organization_id
and hoi_bg.org_information_context = 'CLASS'
AND hoi_bg.org_information1 = 'HR_LEGAL'
AND hoi_ein.organization_id(+) = hou.organization_id
AND nvl(hoi_ein.org_information_context(+),'Employer Identification') = 'Employer Identification'
AND hrl.location_id = hou.location_id;
SELECT /*+ LEADING(ptp) -- For Bug 4769835
INDEX (ptp PER_TIME_PERIODS_N50)
USE_NL(ptp ppa)
*/
COUNT(DISTINCT paf.person_id)
FROM per_time_periods ptp,
per_assignments_f paf,
per_assignments_f paf1,
pay_assignment_actions paa, pay_payroll_actions ppa
WHERE ptp.end_date >= TO_DATE('01'||TO_CHAR
(TRUNC(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'), 'Q'),'MM') ||
TO_CHAR(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'YYYY'),'DD-MM-YYYY')
AND ptp.start_date <= TO_DATE('12'||TO_CHAR
(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'MM') ||
TO_CHAR(to_date(cp_qtr||'-'||cp_year, 'DD-MM-YYYY'),'YYYY'),'DD-MM-YYYY')
AND ppa.effective_date >= ptp.start_date -- For Bug 4769835
AND ppa.effective_date <= ptp.end_date -- For Bug 4769835
AND ppa.payroll_id = ptp.payroll_id -- For Bug 4769835
AND ppa.time_period_id = ptp.time_period_id
AND ppa.action_type IN ('R', 'Q')
AND ppa.payroll_action_id = paa.payroll_action_id
AND paf1.assignment_id = paa.assignment_id
AND paf.person_id = paf1.person_id
AND paa.run_type_id IS NULL
AND paa.tax_unit_id = cp_tax_unit_id
AND paa.action_status = 'C';
SELECT ORG_INFORMATION1
FROM HR_ORGANIZATION_INFORMATION_V
WHERE org_information_context = 'State Tax Rules'
AND ORGANIZATION_ID = cp_tax_unit_id;
SELECT d_tax_otd_value,
d_wage_otd_value,
d_tax_type
FROM pay_us_federal_tax_bal_gre_v
WHERE d_balance_set_name = '941_QTD'
AND d_tax_unit_id = cp_tax_unit_id;
/* SELECT count(DISTINCT(ORG_INFORMATION1))
INTO ln_count_state
FROM HR_ORGANIZATION_INFORMATION_V
WHERE org_information_context = 'State Tax Rules'
AND ORGANIZATION_ID = p_tax_unit_id;
vXMLTable.DELETE;