[Home] [Help]
62: 16-Jun-2008 pannapur 115.11 Modified the cursor definitions of all
63: process types to generate proper sequence id
64: (6854964)
65: 27-JUL-2012 rnestor 115.12 Perf Bug 14270689 added Index hint
66: PAY_PAYROLL_ACTIONS_N5 in range_cursor
67:
68: */
69:
70: -------------------------------- range_cursor ----------------------------------
71: PROCEDURE range_cursor (pactid in number,
72: sqlstr out nocopy varchar2) is
73:
74: --
75: leg_param pay_payroll_actions.legislative_parameters%type;
76:
77: l_business_group_id number;
78: l_consolidation_set_id number;
79: l_payroll_id number;
90:
91: begin
92: select legislative_parameters
93: into leg_param
94: from pay_payroll_actions ppa
95: where ppa.payroll_action_id = pactid;
96:
97: -- pay_process := pay_payact_pkg.get_parameter('P_P_TY',leg_param);
98:
115: l_person_id,
116: l_leg_start_date,
117: l_leg_end_date,
118: l_business_group_id
119: from pay_payroll_actions ppa
120: where ppa.payroll_action_id = pactid;
121:
122: IF l_consolidation_set_id is not null THEN
123:
169: -- Modified to a single sql statement with dynamic selection criteria
170: -- for payroll and consolidation set
171:
172: sqlstr :=
173: 'select /*+ leading(ppa,pa1,act,asg) use_nl(ppa, pa1, act, asg) index(pa1,PAY_PAYROLL_ACTIONS_N5) */
174: distinct asg.person_id
175: from pay_payroll_actions ppa,
176: pay_payroll_actions pa1,
177: pay_assignment_actions act,
171:
172: sqlstr :=
173: 'select /*+ leading(ppa,pa1,act,asg) use_nl(ppa, pa1, act, asg) index(pa1,PAY_PAYROLL_ACTIONS_N5) */
174: distinct asg.person_id
175: from pay_payroll_actions ppa,
176: pay_payroll_actions pa1,
177: pay_assignment_actions act,
178: per_assignments_f asg
179: where ppa.payroll_action_id = :payroll_action_id
172: sqlstr :=
173: 'select /*+ leading(ppa,pa1,act,asg) use_nl(ppa, pa1, act, asg) index(pa1,PAY_PAYROLL_ACTIONS_N5) */
174: distinct asg.person_id
175: from pay_payroll_actions ppa,
176: pay_payroll_actions pa1,
177: pay_assignment_actions act,
178: per_assignments_f asg
179: where ppa.payroll_action_id = :payroll_action_id
180: '||l_consolidation_set_text||'
217: pay_payrg_pkg.get_parameter('P_ID',ppa.legislative_parameters) person_id,
218: pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id,
219: ppa.start_date start_date,
220: ppa.effective_date effective_date
221: from pay_payroll_actions ppa
222: where ppa.payroll_action_id = pactid;
223:
224:
225: -- Bug 3372732 : All cursors defined below are changed
244: act.assignment_id,
245: act.tax_unit_id
246: from pay_assignment_actions act,
247: per_assignments_f paf,
248: pay_payroll_actions ppa, /* pre-payments and reversals
249: payroll action id */
250: pay_payrolls_f ppf -- Bug 3372732
251: where ppa.payroll_id = nvl(c_payroll_id,ppa.payroll_id)
252: and ppa.consolidation_set_id + 0 = nvl(c_consolidation_set_id,
300: act.assignment_id,
301: act.tax_unit_id
302: from pay_assignment_actions act,
303: per_assignments_f paf,
304: pay_payroll_actions ppa, /* pre-payments and reversals payroll action id */
305: pay_payrolls_f ppf -- Bug 3372732
306: where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
307: and ppa.consolidation_set_id +0 =
308: nvl(c_consolidation_set_id, ppa.consolidation_set_id)
355: act.assignment_id,
356: act.tax_unit_id
357: from pay_assignment_actions act,
358: per_assignments_f paf,
359: pay_payroll_actions ppa, /* pre-payments and reversals payroll action id */
360: pay_payrolls_f ppf -- Bug 3372732
361: where ppa.payroll_id =
362: nvl(c_payroll_id, ppa.payroll_id)
363: and ppa.consolidation_set_id +0 =
410: act.assignment_id,
411: act.tax_unit_id
412: from pay_assignment_actions act,
413: per_assignments_f paf,
414: pay_payroll_actions ppa, /* pre-payments and reversals */
415: /* payroll action id */
416: pay_payrolls_f ppf -- Bug 3372732
417: where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
418: and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
465: act.assignment_id,
466: act.tax_unit_id
467: from pay_assignment_actions act,
468: per_assignments_f paf,
469: pay_payroll_actions ppa, /* pre-payments and */
470: /* reversals payroll action id */
471: pay_payrolls_f ppf -- Bug 3372732
472: where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
473: and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
520: act.assignment_id,
521: act.tax_unit_id
522: from pay_assignment_actions act,
523: per_assignments_f paf,
524: pay_payroll_actions ppa, /* pre-payments and */
525: /* reversals payroll action id */
526: pay_payrolls_f ppf
527: where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
528: and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
574: act.assignment_id,
575: act.tax_unit_id
576: from pay_assignment_actions act,
577: per_assignments_f paf,
578: pay_payroll_actions ppa, /* pre-payments and */
579: /* reversals payroll action id */
580: pay_payrolls_f ppf -- Bug 3372732
581: where ppa.payroll_id =
582: nvl(c_payroll_id, ppa.payroll_id)
629: act.assignment_id,
630: act.tax_unit_id
631: from pay_assignment_actions act,
632: per_assignments_f paf,
633: pay_payroll_actions ppa, /* pre-payments and */
634: /* reversals payroll action id */
635: pay_payrolls_f ppf
636: where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
637: and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id ,ppa.consolidation_set_id)
673: num number;
674: process_type varchar2(20);
675:
676: -- Bug 3372732
677: leg_param pay_payroll_actions.legislative_parameters%type;
678: ass_set_id number;
679: ass_flag varchar2(2);
680:
681: --
678: ass_set_id number;
679: ass_flag varchar2(2);
680:
681: --
682: l_payroll_id pay_payroll_actions.payroll_id%TYPE;
683: l_location_id per_all_assignments_f.location_id%TYPE;
684: l_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE;
685: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
686: l_person_id per_all_assignments_f.person_id%TYPE;
680:
681: --
682: l_payroll_id pay_payroll_actions.payroll_id%TYPE;
683: l_location_id per_all_assignments_f.location_id%TYPE;
684: l_consolidation_set_id pay_payroll_actions.consolidation_set_id%TYPE;
685: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
686: l_person_id per_all_assignments_f.person_id%TYPE;
687: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
688: l_organization_id per_all_assignments_f.organization_id%TYPE;
685: l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
686: l_person_id per_all_assignments_f.person_id%TYPE;
687: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
688: l_organization_id per_all_assignments_f.organization_id%TYPE;
689: l_start_date pay_payroll_actions.effective_date%TYPE;
690: l_effective_date pay_payroll_actions.effective_date%TYPE;
691:
692: -- algorithm is quite similar to the other process cases,
693: -- but we have to take into account assignments and
686: l_person_id per_all_assignments_f.person_id%TYPE;
687: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
688: l_organization_id per_all_assignments_f.organization_id%TYPE;
689: l_start_date pay_payroll_actions.effective_date%TYPE;
690: l_effective_date pay_payroll_actions.effective_date%TYPE;
691:
692: -- algorithm is quite similar to the other process cases,
693: -- but we have to take into account assignments and
694: -- personal payment methods.
696:
697: hr_utility.set_location('procpyr',1);
698:
699: select legislative_parameters into leg_param
700: from pay_payroll_actions
701: where payroll_action_id = pactid;
702:
703: -- Bug 3372732:Fetching the Input parameters that are passed to other cursors
704: open c_inputs(pactid);
1292: -- Cursor to get legislative parameters for Payroll Activity
1293: -- Bug 3037633
1294: cursor cur_leg_params(pactid varchar2) is
1295: select legislative_parameters
1296: from pay_payroll_actions
1297: where payroll_action_id = pactid;
1298:
1299: leg_params pay_payroll_actions.legislative_parameters%type;
1300: begin
1295: select legislative_parameters
1296: from pay_payroll_actions
1297: where payroll_action_id = pactid;
1298:
1299: leg_params pay_payroll_actions.legislative_parameters%type;
1300: begin
1301: sqlstr := 'select paa.rowid
1302: /* we need the row id of the assignment actions
1303: that are created by PYUGEN */
1306: hr_locations_all loc,
1307: per_all_people_f ppf,
1308: per_all_assignments_f paf,
1309: pay_assignment_actions paa, /* PYUGEN assignment action */
1310: pay_payroll_actions ppa, /* PYUGEN payroll action id */
1311: pay_assignment_actions paa1, /*For Sorting */
1312: pay_action_interlocks pai,
1313: pay_payroll_actions ppa1 /*For Sorting */
1314: where ppa.payroll_action_id = :payactid
1309: pay_assignment_actions paa, /* PYUGEN assignment action */
1310: pay_payroll_actions ppa, /* PYUGEN payroll action id */
1311: pay_assignment_actions paa1, /*For Sorting */
1312: pay_action_interlocks pai,
1313: pay_payroll_actions ppa1 /*For Sorting */
1314: where ppa.payroll_action_id = :payactid
1315: and paa.payroll_action_id = ppa.payroll_action_id
1316: and paf.assignment_id = paa.assignment_id
1317: and paf.effective_start_date =
1392: parameter_list varchar2) return varchar2
1393: is
1394: start_ptr number;
1395: end_ptr number;
1396: token_val pay_payroll_actions.legislative_parameters%type;
1397: par_value pay_payroll_actions.legislative_parameters%type;
1398: begin
1399: --
1400: token_val := name||'=';
1393: is
1394: start_ptr number;
1395: end_ptr number;
1396: token_val pay_payroll_actions.legislative_parameters%type;
1397: par_value pay_payroll_actions.legislative_parameters%type;
1398: begin
1399: --
1400: token_val := name||'=';
1401: --