[Home] [Help]
906: into dummy
907: from sys.dual
908: where exists (
909: select null
910: from hr_assignment_set_amendments amd
911: where amd.assignment_set_id = asetid
912: and amd.include_or_exclude = 'I');
913: exception
914: when no_data_found then include := FALSE;
922: into dummy
923: from sys.dual
924: where exists (
925: select null
926: from hr_assignment_set_amendments amd
927: where amd.assignment_set_id = asetid
928: and amd.include_or_exclude = 'E');
929: exception
930: when no_data_found then exclude := FALSE;
1291: select act.assignment_id,
1292: act.assignment_action_id
1293: from pay_payroll_actions pac,
1294: pay_population_ranges pop,
1295: hr_assignment_set_amendments amd,
1296: per_all_assignments_f pay_asg,
1297: pay_assignment_actions act
1298: where pac.payroll_action_id = :pactid
1299: and act.payroll_action_id = pac.payroll_action_id
1317: if(exclude) then
1318: sqlstr := sqlstr || '
1319: and not exists (
1320: select null
1321: from hr_assignment_set_amendments exc
1322: where exc.assignment_set_id = has.assignment_set_id
1323: and exc.include_or_exclude = ''E''
1324: and act.assignment_id = exc.assignment_id)';
1325: end if;
1382: sqlstr := '
1383: select pay_asg.assignment_id
1384: from per_all_assignments_f pay_asg,
1385: hr_assignment_sets has,
1386: hr_assignment_set_amendments amd
1387: where has.assignment_set_id = :asetid
1388: and amd.assignment_set_id = has.assignment_set_id
1389: and pay_asg.payroll_id + 0 = has.payroll_id
1390: and pay_asg.assignment_id = amd.assignment_id
1403: and fnd_date.canonical_to_date(:v_effective_date) between
1404: pay_asg.effective_start_date and pay_asg.effective_end_date
1405: and not exists (
1406: select null
1407: from hr_assignment_set_amendments amd
1408: where amd.assignment_set_id = has.assignment_set_id
1409: and pay_asg.assignment_id = amd.assignment_id
1410: and amd.include_or_exclude = ''E'')';
1411: end if;
1525: select pay_asg.assignment_id, pay_asg.assignment_number,
1526: pay_asg.payroll_id, pesm.element_type_id, petf.element_name
1527: from per_all_assignments_f pay_asg,
1528: hr_assignment_sets has,
1529: hr_assignment_set_amendments amd,
1530: PAY_ELEMENT_SET_MEMBERS pesm,
1531: pay_element_types_f petf
1532: where pay_asg.business_group_id = :p_bgid
1533: and has.assignment_set_id = :pasetid
1562: select pay_asg.assignment_id, pay_asg.assignment_number,
1563: pay_asg.payroll_id,petf.element_type_id, petf.element_name
1564: from per_all_assignments_f pay_asg,
1565: hr_assignment_sets has,
1566: hr_assignment_set_amendments amd,
1567: pay_element_types_f petf
1568: where pay_asg.business_group_id = :p_bgid
1569: and has.assignment_set_id = :pasetid
1570: and amd.assignment_set_id = has.assignment_set_id
1596: and fnd_date.canonical_to_date(:p_effective_date) between
1597: pay_asg.effective_start_date and pay_asg.effective_end_date
1598: and not exists (
1599: select null
1600: from hr_assignment_set_amendments amd
1601: where amd.assignment_set_id = has.assignment_set_id
1602: and pay_asg.assignment_id = amd.assignment_id
1603: and amd.include_or_exclude = ''E'')
1604: and pesm.element_set_id = :p_elesetid
1638: and fnd_date.canonical_to_date(:p_effective_date) between
1639: pay_asg.effective_start_date and pay_asg.effective_end_date
1640: and not exists (
1641: select null
1642: from hr_assignment_set_amendments amd
1643: where amd.assignment_set_id = has.assignment_set_id
1644: and pay_asg.assignment_id = amd.assignment_id
1645: and amd.include_or_exclude = ''E'')';
1646: --
1761: select pay_asg.assignment_id,
1762: pay_asg.payroll_id
1763: from per_all_assignments_f pay_asg,
1764: hr_assignment_sets has,
1765: hr_assignment_set_amendments amd,
1766: per_quickpaint_invocations inv
1767: where inv.qp_invocation_id = :qp_invocation_id
1768: and has.assignment_set_id = inv.invocation_context
1769: and amd.assignment_set_id = has.assignment_set_id
1782: if(exclude) then
1783: sqlstr := sqlstr || '
1784: and not exists (
1785: select null
1786: from hr_assignment_set_amendments amd
1787: where amd.assignment_set_id = has.assignment_set_id
1788: and amd.include_or_exclude = ''E''
1789: and pay_asg.assignment_id = amd.assignment_id)';
1790: end if;
2198: PYG_AT_REV constant varchar2(1) := 'V'; -- Reversal
2199:
2200: l_ret_timedepflg varchar2(1);
2201: l_asg_set_id number;
2202: l_inc_or_excl HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE%TYPE;
2203: begin
2204: --
2205: pay_proc_logging.PY_ENTRY('hr_dynsql.pyrsql');
2206: --
2524: -- Find out if an include or exclude assignment set
2525: --
2526: select include_or_exclude
2527: into l_inc_or_excl
2528: from hr_assignment_set_amendments
2529: where assignment_set_id = l_asg_set_id
2530: and rownum = 1;
2531: EXCEPTION
2532: When OTHERS Then
3044: adeincspc := '
3045: from per_periods_of_service pay_pos,
3046: per_all_assignments_f pay_asg,
3047: pay_payroll_actions pay_pac,
3048: hr_assignment_set_amendments pay_inc
3049: where pay_pac.payroll_action_id = :pactid
3050: and pay_asg.payroll_id = pay_pac.payroll_id
3051: and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3052: and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3091: rspcinc := '
3092: from per_periods_of_service pay_pos,
3093: per_all_assignments_f pay_asg,
3094: per_all_assignments_f pay_as2,
3095: hr_assignment_set_amendments pay_inc,
3096: pay_payroll_actions pay_pac
3097: where pay_pac.payroll_action_id = :payroll_action_id
3098: and pay_pac.assignment_set_id = pay_inc.assignment_set_id
3099: and pay_asg.assignment_id = pay_inc.assignment_id
3111: spcinc := '
3112: from per_periods_of_service pay_pos,
3113: per_all_assignments_f pay_asg,
3114: per_all_assignments_f pay_as2,
3115: hr_assignment_set_amendments pay_inc,
3116: pay_payroll_actions pay_pac
3117: where pay_pac.payroll_action_id = :payroll_action_id
3118: and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3119: and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3130: revspcinc := '
3131: from per_periods_of_service pay_pos,
3132: per_all_assignments_f pay_asg,
3133: per_all_assignments_f pay_as2,
3134: hr_assignment_set_amendments pay_inc,
3135: pay_payroll_actions pay_pac,
3136: pay_assignment_actions pay_paa2,
3137: pay_payroll_actions pay_pac2
3138: where pay_pac.payroll_action_id = :payroll_action_id
3156: -- Purge Specific inclusions.
3157: purspcinc := '
3158: from per_periods_of_service pay_pos,
3159: per_all_assignments_f pay_asg,
3160: hr_assignment_set_amendments pay_inc,
3161: pay_payroll_actions pay_pac
3162: where pay_pac.payroll_action_id = :payroll_action_id
3163: and pay_inc.assignment_set_id = pay_pac.assignment_set_id
3164: and pay_inc.assignment_id = pay_asg.assignment_id
3170: -- Retropay inclusions
3171: spcretinc := '
3172: from per_periods_of_service pay_pos,
3173: per_all_assignments_f pay_asg,
3174: hr_assignment_set_amendments pay_inc,
3175: pay_payroll_actions pay_pac
3176: where pay_pac.payroll_action_id = :payroll_action_id
3177: and pay_inc.assignment_set_id = pay_pac.assignment_set_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3178: and pay_inc.assignment_id = pay_asg.assignment_id + decode(pay_pos.period_of_service_id, null, 0, 0)
3184: --
3185: -- RetroCosting inclusions
3186: spcrcsinc := '
3187: from per_periods_of_service pay_pos,
3188: hr_assignment_set_amendments pay_inc,
3189: per_all_assignments_f pay_asg,
3190: per_all_assignments_f pay_as2,
3191: pay_payroll_actions pay_pac
3192: where pay_pac.payroll_action_id = :pactid
3375: -- not exists to exclude specific assignments.
3376: excspc := '
3377: and not exists (
3378: select null
3379: from hr_assignment_set_amendments pay_exc
3380: where pay_exc.assignment_set_id = pay_pac.assignment_set_id
3381: and pay_exc.assignment_id = pay_asg.assignment_id
3382: and pay_exc.include_or_exclude = ''E'')';
3383: --