[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
21-oct-2002 tclewis 115.7 removed the "for Update... " in the action_creation
code.
04-Feb-2004 schauhan 115.8 Modified the query for the cursors c_actions and
c_parameters to reduce the cost of the cursor query
c_actions.Bug 3364759
04-May-2004 irgonzal 115.9 Bug fix 3270485. Added logic to range_cursor
procedure to ensure the "header information" gets
inserted into pay_us_rpt_totals.
01-Oct-2004 saurgupt 115.10 Bug 3679305: Modified the procedure range_cursor. Changed the sqlstr
to improve performance.
05-Oct-2004 saurgupt 115.11 Bug 3679305: Modified the procedure range_cursor. Changed the sqlstr.
Added the new variable l_payroll_test to improve performance.
09-Dec-2004 sgajula 115.12 Added action_create_bra,archive_init,archive_code,archive_deinit
for implementing BRA.
15-MAR-2005 sdhole 115.13 Bug No.4237962, Removed ppa_run.action_status = 'C'
from range_cursor, action_creation and action_create_bra.
26-Aug-2005 sackumar 115.14 Bug No.4344971, Introduced a new condition in sql present in
action_create_bra and action_create functions to remove
the Merge Cartesian join .
30-Aug-2005 sackumar 115.15 Introduced Index Hint in get_futa_def_bal_id cursor in
ARCHIVE_INIT procedure.
12-SEP-2005 pragupta 115.16 Bug 453407: Added an extra condition in the p_er_liab_where
variable to avoid duplication of rows in the procedure
load_er_liab of pay_gtnlod_pkg.
15-SEP-2005 meshah 115.17 removed the index hint from the range cursor.
bug 4591091.
07-APR-2006 rdhingra 115.18 Bug 5148084: Added procedure create_gtn_xml_data
Modified ARCHIVE_DEINIT to submit XML Report Publisher
24-APR-2006 rdhingra 115.19 Bug 5148084: Modified Cursor get_application_detais
to reflect changes of parameters to XML Report Publisher
concurrent program
30-Aug-2006 kvsankar 115.20 Bug 5478638 : Passed Application ID instead of
Application Name to the concurrent program
"XML Report Publisher"
16-Oct-2006 jdevasah 114.21 Bug 4942114 : changed the parameters to
pay_gtnlod_pkg.load_data procedure in ARCHIVE_CODE.
Commented assignment statements in ARCHVIE_INIT.
Created global variables which are input paramenters
to pay_gtnlod_pkg.load_data procedure.
21-jan-2007 asgugupt 114.22 Bug 6365474 : changed the parameters to
fnd_request.submit_request in ARCHIVE_DEINIT.
06-Mar-2008 skameswa 115.25 Bug 6799553 : Modified the procedure ARCHIVE_DEINIT to include
a new cursor get_printer_details and a call to
fnd_request.set_print_options whose parameters were retrieved by
the above mentioned cursor
10-Apr-2008 priupadh 115.26 Bug 6670508 Added delete statment for pay_us_rpt_totals table
in archive_deinit,deleting for the current run as payroll act id gets
stored in column tax_unit_id .
21-Apr-2008 priupadh 115.27 Bug 6670508 Moved delete statment outside if clause , to delete the data
in 11i and R12 .
04-Aug-2008 kagangul 115.28 Bug 7297300. Changed Cursor (get_printer_details) Parameter Name
from request_id to c_request_id.
04-Mar-2009 skpatil 115.29 Bug 8216159: Changed action_creation_bra cursor to include balance
adjustments('B') action_type
14-Oct-2011 sgotlasw 115.30 Bug 12637772: Range cursor code has been modified to
include the employee in 'US GROSS TO NET SUMMARY REPORT'
who has 'Balance Adjustments' or 'Balance Initializations'
alone in the pay period.
*/
----------------------------------- range_cursor ----------------------------------
--
g_proc_name VARCHAR2(240);
select ppa.legislative_parameters,
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),
pay_paygtn_pkg.get_parameter('PPA_FINDER',ppa.legislative_parameters),
ppa.start_date,
ppa.effective_date,
ppa.business_group_id
into leg_param,
l_consolidation_set_id,
l_payroll_id,
l_tax_unit_id,
l_ppa_finder,
l_leg_start_date,
l_leg_end_date,
l_business_group_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,'GTN',l_ppa_finder,
leg_param, l_business_group_id,
to_char(l_leg_start_date,'MM/DD/YYYY'),
to_char(l_leg_end_date,'MM/DD/YYYY'));
sqlstr := 'select distinct asg.person_id
from per_assignments_f asg,
pay_assignment_actions act_run, /* run and quickpay assignment actions */
pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
pay_payroll_actions ppa_gen /* PYUGEN information */
where ppa_gen.payroll_action_id = :payroll_action_id
and ppa_run.effective_date between /* date join btwn run and pyugen ppa */
ppa_gen.start_date and ppa_gen.effective_date
and ppa_run.action_type in (''R'',''Q'',''V'',''B'',''I'')
and ppa_run.consolidation_set_id = '''||l_consolidation_set_id||''''||l_payroll_text||'
and ppa_run.payroll_action_id = act_run.payroll_action_id
and act_run.action_status = ''C''
and asg.assignment_id = act_run.assignment_id
and ppa_run.effective_date between /* date join btwn run and asg */
asg.effective_start_date and asg.effective_end_date
and asg.business_group_id +0 = ppa_gen.business_group_id
order by asg.person_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)
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select /*+ ORDERED
INDEX (ppa_gen PAY_PAYROLL_ACTIONS_PK)
INDEX (act_run PAY_ASSIGNMENT_ACTIONS_N50)
INDEX (asg PER_ASSIGNMENTS_F_PK) */
ppa_run.action_type,
act_run.assignment_action_id,
asg.assignment_id,
act_run.tax_unit_id
from pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
pay_assignment_actions act_run, /* run and quickpay assignment actions */
per_assignments_f asg
where ppa_run.effective_date between
l_start_date
and l_effective_date
and ppa_run.action_type in ('R','Q','V','B') /* 8216159 */
and ppa_run.consolidation_set_id = l_consolidation_set_id
AND (l_payroll_id IS NULL
OR PPA_RUN.PAYROLL_ID = l_payroll_id)
and ppa_run.payroll_action_id = act_run.payroll_action_id
and act_run.action_status = 'C'
and act_run.tax_unit_id = nvl(l_tax_unit_id,
act_run.tax_unit_id)
and asg.assignment_id = act_run.assignment_id
and ppa_run.effective_date between /* date join btwn run and asg */
asg.effective_start_date
and asg.effective_end_date
and asg.business_group_id +0 = l_business_group_id
and asg.person_id between stperson and endperson;
select to_number(ue.creator_id)
into l_gross_defined_balance_id
from ff_user_entities ue,
ff_database_items di
-- where di.user_name = 'PAYMENTS_ASG_GRE_RUN'
where di.user_name = 'GROSS_EARNINGS_ASG_GRE_RUN'
and ue.user_entity_id = di.user_entity_id
and ue.creator_type = 'B'
and nvl(ue.legislation_code,'US') = 'US';
select to_number(ue.creator_id)
into l_payments_defined_balance_id
from ff_user_entities ue,
ff_database_items di
where di.user_name = 'PAYMENTS_ASG_GRE_RUN'
and ue.user_entity_id = di.user_entity_id
and ue.creator_type = 'B'
and nvl(ue.legislation_code,'US') = 'US';
select 'Y'
into l_create_act
from dual
where exists (
select 'Y'
from pay_run_result_values rrv,
pay_input_values_F iv,
pay_run_results rr
where nvl(rrv.result_value,0) <> to_char(0)
and iv.input_value_id = rrv.input_value_id
and iv.element_type_id = rr.element_type_id
and iv.name = 'Pay Value'
and rr.run_result_id = rrv.run_result_id
and rr.assignment_action_id = lockedactid);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
-- insert the action record.
hr_nonrun_asact.insact(lockingactid =>lockingactid,
object_id =>lockedactid,
pactid =>pactid,
chunk =>chunk,
greid =>greid);
-- insert an interlock to this action.
-- hr_nonrun_asact.insint(lockingactid,lockedactid);
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)
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select /*+ ORDERED
INDEX (ppa_gen PAY_PAYROLL_ACTIONS_PK)
INDEX (act_run PAY_ASSIGNMENT_ACTIONS_N50)
INDEX (asg PER_ASSIGNMENTS_F_PK) */
ppa_run.action_type,
act_run.assignment_action_id,
asg.assignment_id,
act_run.tax_unit_id
from pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
pay_assignment_actions act_run, /* run and quickpay assignment actions */
per_assignments_f asg
where ppa_run.effective_date between
l_start_date
and l_effective_date
and ppa_run.action_type in ('R','Q','V')
and ppa_run.consolidation_set_id = l_consolidation_set_id
AND (l_payroll_id IS NULL
OR PPA_RUN.PAYROLL_ID = l_payroll_id)
and ppa_run.payroll_action_id = act_run.payroll_action_id
and act_run.action_status = 'C'
and act_run.tax_unit_id = nvl(l_tax_unit_id,
act_run.tax_unit_id)
and asg.assignment_id = act_run.assignment_id
and ppa_run.effective_date between /* date join btwn run and asg */
asg.effective_start_date
and asg.effective_end_date
and asg.business_group_id +0 = l_business_group_id
and asg.person_id between stperson and endperson;
select to_number(ue.creator_id)
into l_gross_defined_balance_id
from ff_user_entities ue,
ff_database_items di
-- where di.user_name = 'PAYMENTS_ASG_GRE_RUN'
where di.user_name = 'GROSS_EARNINGS_ASG_GRE_RUN'
and ue.user_entity_id = di.user_entity_id
and ue.creator_type = 'B'
and nvl(ue.legislation_code,'US') = 'US';
select to_number(ue.creator_id)
into l_payments_defined_balance_id
from ff_user_entities ue,
ff_database_items di
where di.user_name = 'PAYMENTS_ASG_GRE_RUN'
and ue.user_entity_id = di.user_entity_id
and ue.creator_type = 'B'
and nvl(ue.legislation_code,'US') = 'US';
select 'Y'
into l_create_act
from dual
where exists (
select 'Y'
from pay_run_result_values rrv,
pay_input_values_F iv,
pay_run_results rr
where nvl(rrv.result_value,0) <> to_char(0)
and iv.input_value_id = rrv.input_value_id
and iv.name = 'Pay Value'
and iv.element_type_id = rr.element_type_id
and rr.run_result_id = rrv.run_result_id
and rr.assignment_action_id = lockedactid);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
-- insert the action record.
hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
-- insert an interlock to this action.
hr_nonrun_asact.insint(lockingactid,lockedactid);
sqlstr := 'select paa1.rowid
from pay_assignment_actions paa1, -- PYUGEN assignment action
pay_payroll_actions ppa1 -- PYUGEN payroll action id
where ppa1.payroll_action_id = :pactid
and paa1.payroll_action_id = ppa1.payroll_action_id
order by paa1.assignment_action_id
for update of paa1.assignment_id';
SELECT /*+ index(pbd PAY_BALANCE_DIMENSIONS_PK)*/ pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.balance_type_id = pbt.balance_type_id
AND ( pdb.business_group_id = cp_business_group_id
OR pdb.legislation_code = 'US'
)
AND pbt.legislation_code = 'US'
AND pbd.legislation_code = 'US'
AND pbd.database_item_suffix = '_ASG_GRE_RUN'
AND pbt.balance_name = 'FUTA CREDIT';
select count(*)
from pay_balance_validation pbv
where pbv.business_group_id = cp_business_group_id
and pbv.defined_balance_id = cp_defined_balance_id
AND NVL (pbv.run_balance_status, 'I') = 'V'
and NVL(pbv.balance_load_date, cp_start_date) <= cp_start_date;
select count(*)
from pay_balance_validation pbv,
pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
where (pbv.business_group_id = cp_business_group_id)
and pbv.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.balance_type_id = pbt.balance_type_id
and (pdb.business_group_id = cp_business_group_id or
pdb.legislation_code = 'US')
and pbt.legislation_code = 'US'
and pbd.legislation_code = 'US'
and pbd.database_item_suffix = '_ASG_JD_GRE_RUN'
and pbt.balance_name in ('Workers Compensation',
'Workers Compensation2 ER',
'Workers Compensation3 ER')
AND NVL (pbv.run_balance_status, 'I') = 'V'
and NVL(pbv.balance_load_date, cp_start_date) <= cp_start_date;
select
ppa.legislative_parameters,
ppa.business_group_id,
ppa.start_date,
ppa.effective_date
into l_param,
p_business_group_id,
p_start_date,
p_end_date
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
SELECT request_id
FROM pay_payroll_actions
WHERE payroll_action_id = c_pact_id;
SELECT app.application_short_name, fcp.application_id
FROM fnd_application_vl app,
fnd_concurrent_programs fcp,
fnd_concurrent_requests r
WHERE fcp.concurrent_program_id = r.concurrent_program_id
AND r.request_id = c_request_id
and app.application_id = fcp.application_id;
SELECT template_type_code
FROM xdo_templates_vl
WHERE template_code = c_templ_code;
select to_number(substr(PRODUCT_VERSION,1,2))
from FND_PRODUCT_INSTALLATIONS
where APPLICATION_ID = 800;
SELECT printer, print_style, number_of_copies, save_output_flag, print_group
FROM fnd_concurrent_requests
WHERE request_id = request_id ;*/
SELECT printer, print_style, number_of_copies, save_output_flag, print_group
FROM fnd_concurrent_requests
WHERE request_id = c_request_id ;
delete from pay_us_rpt_totals where tax_unit_id = p_payroll_action_id;