[Home] [Help]
258: -- Query is modified for better performance Bug3512116--
259:
260: if ln_payroll_id is not null then
261: sqlstr := 'select '||l_db_version||' distinct paa.assignment_id
262: from pay_assignment_actions paa,
263: pay_payroll_actions ppa
264: where :payroll_action_id is not null
265: and ppa.action_type in (''R'',''Q'')
266: and ppa.consolidation_set_id = ' || ln_consolidation_set_id
271: order by paa.assignment_id';
272:
273: else
274: sqlstr := 'select '||l_db_version||' distinct paa.assignment_id
275: from pay_assignment_actions paa,
276: pay_payroll_actions ppa
277: where :payroll_action_id is not null
278: and ppa.action_type in (''R'',''Q'')
279: and ppa.consolidation_set_id = ' || ln_consolidation_set_id
310: from dual
311: where exists
312: (select 1
313: from pay_action_interlocks pai_mag,
314: pay_assignment_actions paa_mag,
315: pay_payroll_actions ppa_mag,
316: pay_org_payment_methods_f popm,
317: pay_pre_payments ppp,
318: pay_payment_types ppt
394: ****************************************************************/
395: cursor c_prepay_arch_action(cp_assignment_action_id in number) is
396: select paa.assignment_action_id
397: from pay_action_interlocks paci,
398: pay_assignment_actions paa,
399: pay_payroll_actions ppa
400: where paci.locking_action_id = cp_assignment_action_id
401: and paa.assignment_action_id = paci.locked_action_id
402: and ppa.payroll_action_id = paa.payroll_action_id
412: cursor c_prepay_run_arch_action(cp_assignment_action_id in number) is
413: select paa_pre.assignment_action_id
414: from pay_action_interlocks pai_run,
415: pay_action_interlocks pai_pre,
416: pay_assignment_actions paa_pre,
417: pay_payroll_actions ppa_pre
418: where pai_run.locking_action_id = cp_assignment_action_id
419: and pai_pre.locked_action_id = pai_run.locked_action_id
420: and paa_pre.assignment_Action_id = pai_pre.locking_action_id
465: where exists
466: (Select /*+ ORDERED */ 1
467: from pay_action_interlocks pai_run, --Pre > Run
468: pay_action_interlocks pai_rev, --Run > Rev
469: pay_assignment_actions paa_rev, --Rev
470: pay_payroll_actions ppa_rev --Rev
471: where pai_run.locking_action_id = ln_prepay_action_id
472: and pai_rev.locked_action_id = pai_run.locked_action_id
473: and paa_rev.assignment_action_id = pai_run.locking_action_id
513: paa_xfr.assignment_id,
514: paa_xfr.tax_unit_id
515: from
516: pay_payroll_actions ppa_xfr,
517: pay_assignment_actions paa_xfr,
518: pay_action_interlocks pai_pre
519: where ppa_xfr.report_type = 'XFR_INTERFACE'
520: and ppa_xfr.report_category = 'RT'
521: and ppa_xfr.report_qualifier = 'FED'
549: and not exists
550: (Select /*+ ORDERED */ 1
551: from pay_action_interlocks pai_run, --Pre > Run
552: pay_action_interlocks pai_rev, --Run > Rev
553: pay_assignment_actions paa_rev, --Rev
554: pay_payroll_actions ppa_rev --Rev
555: where pai_run.locking_action_id = pai_pre.locked_action_id
556: and pai_rev.locked_action_id = pai_run.locked_action_id
557: and paa_rev.assignment_action_id = pai_run.locking_action_id
624:
625:
626:
627: hr_utility.set_location('procdar archive',3);
628: select pay_assignment_actions_s.nextval
629: into ln_deposit_action_id
630: from dual;
631:
632: -- insert the action record.
636: hr_utility.trace('Inserted into paa');
637: -- insert an interlock to this action.
638: hr_nonrun_asact.insint(ln_deposit_action_id, ln_nacha_action_id);
639:
640: update pay_assignment_Actions
641: set serial_number = ln_nacha_action_id
642: where assignment_action_id = ln_deposit_action_id;
643:
644: END IF;
678: sqlstr := 'select '||l_db_version||' paa.rowid
679: from hr_all_organization_units hou,
680: per_all_people_f ppf,
681: per_all_assignments_f paf,
682: pay_assignment_actions paa,
683: pay_payroll_actions ppa
684: where ppa.payroll_action_id = :pactid
685: and paa.payroll_action_id = ppa.payroll_action_id
686: and paa.assignment_id = paf.assignment_id
730: act.assignment_id,
731: act.tax_unit_id,
732: ppa_mag.effective_date,
733: ppa_mag.action_type
734: from pay_assignment_actions act,
735: pay_payroll_actions ppa_dar,
736: pay_payroll_actions ppa_mag,
737: pay_org_payment_methods_f popm,
738: pay_all_payrolls_f ppf,
783: and not exists
784: ( select ''
785: from pay_action_interlocks int2,
786: pay_action_interlocks int3,
787: pay_assignment_actions paa4,
788: pay_payroll_actions ppa_run, --- RUN
789: pay_payroll_actions pact4, --- Reversal
790: pay_assignment_actions paa_run, --- RUN
791: pay_assignment_actions paa_pp --- PREPAY
786: pay_action_interlocks int3,
787: pay_assignment_actions paa4,
788: pay_payroll_actions ppa_run, --- RUN
789: pay_payroll_actions pact4, --- Reversal
790: pay_assignment_actions paa_run, --- RUN
791: pay_assignment_actions paa_pp --- PREPAY
792: where int3.locked_action_id = act.assignment_action_id
793: and int3.locking_action_id = paa_pp.assignment_action_id
794: and int2.locked_action_id = paa_pp.assignment_action_id
787: pay_assignment_actions paa4,
788: pay_payroll_actions ppa_run, --- RUN
789: pay_payroll_actions pact4, --- Reversal
790: pay_assignment_actions paa_run, --- RUN
791: pay_assignment_actions paa_pp --- PREPAY
792: where int3.locked_action_id = act.assignment_action_id
793: and int3.locking_action_id = paa_pp.assignment_action_id
794: and int2.locked_action_id = paa_pp.assignment_action_id
795: and int2.locking_action_id = paa_run.assignment_action_id
816:
817: cursor c_payroll_run (cp_pre_pymt_action_id in number) is
818: select assignment_action_id
819: from pay_action_interlocks pai,
820: pay_assignment_actions paa
821: where pai.locking_action_id = cp_pre_pymt_action_id
822: and paa.assignment_Action_id = pai.locked_action_id
823: and paa.run_type_id is null
824: order by action_sequence desc;
894: act.assignment_id,
895: act.tax_unit_id,
896: ppa_mag.effective_date,
897: ppa_mag.action_type
898: from pay_assignment_actions act,
899: pay_payroll_actions ppa_dar,
900: pay_payroll_actions ppa_mag,
901: per_all_assignments_f paf2,
902: pay_org_payment_methods_f popm, --Bug 3009643
942: (
943: Select ''''
944: from pay_action_interlocks int2,
945: pay_action_interlocks int3,
946: pay_assignment_actions paa4,
947: pay_payroll_actions ppa_run, --- RUN
948: pay_payroll_actions pact4, --- Reversal
949: pay_assignment_actions paa_run, --- RUN
950: pay_assignment_actions paa_pp --- PREPAY
945: pay_action_interlocks int3,
946: pay_assignment_actions paa4,
947: pay_payroll_actions ppa_run, --- RUN
948: pay_payroll_actions pact4, --- Reversal
949: pay_assignment_actions paa_run, --- RUN
950: pay_assignment_actions paa_pp --- PREPAY
951: where
952: int3.locked_action_id = act.assignment_action_id
953: and int3.locking_action_id = paa_pp.assignment_action_id
946: pay_assignment_actions paa4,
947: pay_payroll_actions ppa_run, --- RUN
948: pay_payroll_actions pact4, --- Reversal
949: pay_assignment_actions paa_run, --- RUN
950: pay_assignment_actions paa_pp --- PREPAY
951: where
952: int3.locked_action_id = act.assignment_action_id
953: and int3.locking_action_id = paa_pp.assignment_action_id
954: and int2.locked_action_id = paa_pp.assignment_action_id
1031: ln_source_action_id <> ln_prev_source_action_id or
1032: ln_source_action_id is null) then
1033:
1034: hr_utility.set_location('procdar',3);
1035: select pay_assignment_actions_s.nextval
1036: into ln_deposit_action_id
1037: from dual;
1038:
1039: -- insert the action record.
1047: hr_utility.trace('Inserted into interlock');
1048: if ln_source_action_id is not null then
1049: hr_utility.trace('serial number updated if loop ');
1050: hr_utility.trace('serial number is '||to_char(ln_source_action_id));
1051: update pay_assignment_Actions
1052: set serial_number = 'P'||ln_source_action_id
1053: where assignment_action_id = ln_deposit_action_id;
1054: else
1055: hr_utility.trace('serial number else ');
1058: close c_payroll_run;
1059: hr_utility.trace(' ln_master_action_id is'
1060: ||to_char(ln_master_action_id));
1061:
1062: update pay_assignment_Actions
1063: set serial_number = 'M'||ln_master_action_id
1064: where assignment_action_id = ln_deposit_action_id;
1065: end if;
1066: -- skip till next source action id
1074: hr_utility.trace('Zero Pay Assignment');
1075: if (ln_prev_pre_pymt_action_id is null or
1076: ln_prev_pre_pymt_action_id <> ln_pre_pymt_action_id) then
1077: hr_utility.set_location('procdar',6);
1078: select pay_assignment_actions_s.nextval
1079: into ln_deposit_action_id
1080: from dual;
1081:
1082: -- insert the action record.
1092: open c_payroll_run (ln_nacha_action_id);
1093: fetch c_payroll_run into ln_master_action_id;
1094: close c_payroll_run;
1095:
1096: update pay_assignment_Actions
1097: set serial_number = 'M'||ln_master_action_id
1098: where assignment_action_id = ln_deposit_action_id;
1099:
1100: hr_utility.trace(' NZ ln_master_action_id is'
1168: ,c_legislation_code VARCHAR2) IS
1169: SELECT /* 'Y' */
1170: distinct paa.assignment_id
1171: ,pai.action_context_id
1172: FROM pay_assignment_actions paa
1173: ,pay_payroll_actions ppa
1174: ,hr_lookups hrl
1175: ,pay_action_information pai
1176: ,per_time_periods ptp
1213: ,c_assignment_set_id NUMBER ) IS
1214: SELECT /* 'Y' */
1215: distinct paa.assignment_id
1216: ,pai.action_context_id
1217: FROM pay_assignment_actions paa
1218: ,pay_payroll_actions ppa
1219: ,hr_lookups hrl
1220: ,hr_assignment_set_amendments hasa
1221: ,pay_action_information pai
1485: if ln_payroll_id is not null then
1486: if ln_assignment_set_id is not null then
1487:
1488: lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
1489: from pay_assignment_actions paa,
1490: pay_payroll_actions ppa,
1491: per_assignments_f paf,
1492: hr_assignment_set_amendments hasa
1493: where :payroll_action_id is not null
1503: || ' and hasa.include_or_exclude = ''I'''
1504: || ' order by paf.person_id';
1505: else
1506: lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
1507: from pay_assignment_actions paa,
1508: pay_payroll_actions ppa,
1509: per_assignments_f paf
1510: where :payroll_action_id is not null
1511: and ppa.action_type in (''R'',''Q'')
1520:
1521: else
1522: if ln_assignment_set_id is not null then
1523: lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
1524: from pay_assignment_actions paa,
1525: pay_payroll_actions ppa,
1526: per_assignments_f paf,
1527: hr_assignment_set_amendments hasa
1528: where :payroll_action_id is not null
1538: || ' order by paf.person_id';
1539:
1540: else
1541: lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
1542: from pay_assignment_actions paa,
1543: pay_payroll_actions ppa,
1544: per_assignments_f paf
1545: where :payroll_action_id is not null
1546: and ppa.action_type in (''R'',''Q'')