[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
13-Mar-2009 skpatil 115.11 8267959 Removing addition of XML tags for Cobra values
25-Mar-2009 skpatil 115.12 8267963 Adding code to submit FND_REQUEST based on
release.
11-Mar-2010 vvijayku 115.13 9357061 Added code to split number into its integer and
decimal part. Also modified the XML tag generation
to incorporate the integer and decimal values.
24-Mar-2010 vvijayku 115.14 8772549 Added an exist clause to find the correct number of
employees who have wages in the quarter.
02-Apr-2010 vvijayku 115.15 8772549 Added code to Ignore the Information type classification
which has Pay Value not equal to 0.
08-Apr-2010 vvijayku 115.16 9556898 Moved the Splitting of the balance values into integer
and decimal parts out of the IF clause.
19-May-2010 vvijayku 115.17 9595127 Added condition in the function SPLIT_NUMBER_INTO_INT_DECIMAL
for returning the correctly formatted Integer part of the number
with the commas in the number.
9572713 Removed the ROUND function usage in the function
SPLIT_NUMBER_INTO_INT_DECIMAL, so as to return correct decimal
part of the actual number.
15-Jun-2010 vvijayku 115.19 9496602 Added new cursors c_count_qualified_first_paid and
c_count_qualified_paid. Also have modified the XML generation
code and have added new tags to be displayed as per the 941-PDF
2010 Q2 requirements.
17-Jun-2010 vvijayku 115.20 9496602 Modified the cursor c_run_balance_value to fetch the balance value
for SS ER W11 Taxable based on the qualified employees paid.
22-Jun-2010 vvijayku 115.21 9588208 Modified the c_count_asg_processed cursor to resolve the performance
issue mentioned in the bug 9588208
23-Jun-2010 vvijayku 115.22 9496602 Reverted back the changes that were introduced in the version 115.20.
29-Jun-2010 vvijayku 115.23 9921613 Added condition to handle Balance Adjustment as well for the lines 6a
and 6b in 941 report.
12-Aug-2010 vvijayku 115.24 9921467 Modified the query in the cursor c_count_asg_processed for improving
the performance of the 941 report.
07-Sep-2010 vvijayku 115.25 10091673 Updated the cursor c_count_qualified_first_paid with adjustments for
including the Quarter 3 reporting requirements for the field 6a.
11-Oct-2010 vvijayku 115.26 10097699 Included code to append 0 cents to the decimal value if not already
present.
08-Nov-2010 vvijayku 115.27 10269211 Updated the cursor c_count_qualified_first_paid with adjustments for
including the Quarter 4 reporting requirements for the field 6a.
11-Mar-2010 nkjaladi 115.28 11831970 Commenting out the code for deriving the hire act wages as
currently this is not required to report in 941 report.
15-Mar-2010 nkjaladi 115.29 11831970 Modified tax rate for SS wages and tips from .124 to .104
13-Jul-2012 ybudamal 115.30 14181799 Modified the query in the cursor c_count_asg_processed to fetch the
correct Employee Count.
19-Mar-2013 ybudamal 115.31 16211747 Modified the package for new Medicare reporting changes effective
2013.
************************************************************************/
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 COUNT(DISTINCT person_id) -- #Bug 14181799
FROM
(
SELECT /*+ LEADING(ptp) -- For Bug 4769835
INDEX (ptp PER_TIME_PERIODS_N50)
USE_NL(ptp ppa)
*/
paf.person_id person_id,
paa.assignment_id assignment_id, -- #Bug 14181799
pay_balance_pkg.get_value( cp_def_bal_id,
max(paa.assignment_action_id),
cp_tax_unit_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'TRUE') val
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'
group by paf.person_id,paa.assignment_id -- #Bug 14181799
)
WHERE val>0;
SELECT COUNT(DISTINCT pps.person_id)
FROM per_periods_of_service pps,
per_all_assignments_f paaf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_us_emp_fed_tax_rules_f ftr
WHERE pps.date_start BETWEEN TO_DATE('04-02-2010','DD-MM-YYYY') AND TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY')
AND paaf.person_id = pps.person_id
AND paa.tax_unit_id = cp_tax_unit_id
AND paa.assignment_id = paaf.assignment_id
AND ftr.assignment_id = paa.assignment_id
AND ftr.fed_information2 = 'Y'
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('H','M','B')
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN TRUNC(TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY'),'Q') AND TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY')
AND NOT EXISTS (SELECT 'Y'
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1
WHERE paa1.assignment_id = ftr.assignment_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.action_type in ('H','M','B')
AND ppa1.action_status = 'C'
AND ppa1.effective_date BETWEEN TO_DATE('19-03-2010','DD-MM-YYYY') AND TO_DATE('30-09-2010','DD-MM-YYYY'));
SELECT COUNT(DISTINCT pps.person_id)
FROM per_periods_of_service pps,
per_all_assignments_f paaf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_us_emp_fed_tax_rules_f ftr
WHERE pps.date_start BETWEEN TO_DATE('04-02-2010','DD-MM-YYYY') AND TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY')
AND paaf.person_id = pps.person_id
AND paa.tax_unit_id = cp_tax_unit_id
AND paa.assignment_id = paaf.assignment_id
AND ftr.assignment_id = paa.assignment_id
AND ftr.fed_information2 = 'Y'
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('H','M','B')
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN TRUNC(TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY'),'Q') AND TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY');
SELECT pbv.run_balance_status
FROM pay_balance_validation pbv
WHERE pbv.defined_balance_id = cp_defined_balance_id
AND pbv.business_group_id = cp_business_group_id
AND pbv.balance_load_date <= to_date ('01-01-2010','DD-MM-YYYY');
SELECT NVL(SUM(prb.balance_value),0)
FROM pay_run_balances prb,
pay_defined_balances pdb
where pdb.balance_type_id = cp_balance_type_id
and pdb.balance_dimension_id = cp_balance_dimension_id
and pdb.defined_balance_id = prb.defined_balance_id
and prb.tax_unit_id = cp_tax_unit_id
and prb.effective_date <= TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY')
and prb.effective_date >= TRUNC(TO_DATE(cp_qtr||'-'||cp_year,'DD-MM-YYYY'),'Q');
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 pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
WHERE pbt.balance_name = cp_balance_name
AND pbt.legislation_code = 'US'
AND pbd.database_item_suffix = cp_balance_dimension
AND pbd.legislation_code = 'US'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT pdb.defined_balance_id
INTO ln_def_bal_id
FROM pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
WHERE pbt.balance_name = 'Gross Earnings'
AND pbt.legislation_code = 'US'
AND pbd.database_item_suffix = '_ASG_QTD'
AND pbd.legislation_code = 'US'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
/*SELECT USERENV('SESSIONID')
INTO g_session_id
FROM DUAL;*/
/* 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;
select defined_balance_id, balance_type_id, balance_dimension_id
into ln_defined_balance_id, ln_balance_type_id, ln_balance_dimension_id
from pay_defined_balances
where balance_type_id = (select balance_type_id
from pay_balance_types
where balance_name = 'SS ER W11 Taxable'
and legislation_code = 'US')
and balance_dimension_id = (select balance_dimension_id
from pay_balance_dimensions
where database_item_suffix = '_ASG_GRE_RUN'
and legislation_code = 'US');
vXMLTable.DELETE;
select to_number(substr(PRODUCT_VERSION,1,2))
from FND_PRODUCT_INSTALLATIONS
where APPLICATION_ID = 800;