[Home] [Help]
186: req 1063413
187: 03_DEC-1999 asasthan 40.6 1093595
188: 03-DEC-1999 rpotnuru 40.5 1095096 NY sqwl for 4th qtr date range is Year St to
189: 1085774 Year End. so for reporting QTD balances
190: setting a flag in pay_assignment_actions
191: if the employee doesnt have balances for the QTD.
192: Added function update_ff_archive_item.
193:
194: 17-NOV-1999 asasthan Performance Tuning 1079787
229: 30-dec-1998 vmehta 709641 Look at SUI_ER_SUBJ_WHABLE instead of SUI_ER_GROSS
230: for picking up people for SQWL . This makes sure
231: that only people with SUI wages are picked up.
232: 27-dec-1998 vmehta Corrected the cursor in action creation to get the
233: tax_unit_name from pay_assignment_actions.
234: 21-DEC-1998 achauhan Changed the cursor in action creation to get the
235: assignments from the pay_assignment_actions table.
236:
237: 08-DEC-1998 vmehta Removed grouping by on assignment_id while creating
231: that only people with SUI wages are picked up.
232: 27-dec-1998 vmehta Corrected the cursor in action creation to get the
233: tax_unit_name from pay_assignment_actions.
234: 21-DEC-1998 achauhan Changed the cursor in action creation to get the
235: assignments from the pay_assignment_actions table.
236:
237: 08-DEC-1998 vmehta Removed grouping by on assignment_id while creating
238: assignment_ids
239: 08-DEC-1998 nbristow Updated the c_state cursor to use
697: that are created. */
698:
699: l_payroll_action_created boolean := false;
700: l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
701: l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
702:
703:
704: /* Variable holding the balance to be tested. */
705:
759: ASG.assignment_id assignment_id,
760: paa.tax_unit_id tax_unit_id,
761: ppa.effective_date effective_end_date
762: FROM per_all_assignments_f ASG,
763: pay_assignment_actions paa,
764: pay_payroll_actions ppa
765: WHERE ppa.effective_date between l_period_start
766: and l_period_end
767: and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
797: ASG.assignment_id assignment_id,
798: paa.tax_unit_id tax_unit_id,
799: ppa.effective_date effective_end_date
800: FROM per_all_assignments_f ASG,
801: pay_assignment_actions paa,
802: pay_payroll_actions ppa,
803: pay_population_ranges ppr
804: WHERE ppa.effective_date between l_period_start
805: and l_period_end
836: ASG.assignment_id assignment_id,
837: paa.tax_unit_id tax_unit_id,
838: ppa.effective_date effective_end_date
839: FROM per_all_assignments_f ASG,
840: pay_assignment_actions paa,
841: pay_payroll_actions ppa
842: WHERE ppa.effective_date between l_period_start
843: and l_period_end
844: and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
874: ASG.assignment_id assignment_id,
875: paa.tax_unit_id tax_unit_id,
876: ppa.effective_date effective_end_date
877: FROM per_all_assignments_f ASG,
878: pay_assignment_actions paa,
879: pay_payroll_actions ppa,
880: pay_population_ranges ppr
881: WHERE ppa.effective_date between l_period_start
882: and l_period_end
916: ASG.assignment_id assignment_id,
917: paa.tax_unit_id tax_unit_id,
918: ppa.effective_date effective_end_date
919: FROM per_all_assignments_f ASG,
920: pay_assignment_actions paa,
921: pay_payroll_actions ppa
922: WHERE ppa.effective_date between l_period_start
923: and l_period_end
924: and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
953: ASG.assignment_id assignment_id,
954: paa.tax_unit_id tax_unit_id,
955: ppa.effective_date effective_end_date
956: FROM per_all_assignments_f ASG,
957: pay_assignment_actions paa,
958: pay_payroll_actions ppa,
959: pay_population_ranges ppr
960: WHERE ppa.effective_date between l_period_start
961: and l_period_end
992: ASG.assignment_id assignment_id,
993: paa.tax_unit_id tax_unit_id,
994: ppa.effective_date effective_end_date
995: FROM per_all_assignments_f ASG,
996: pay_assignment_actions paa,
997: pay_payroll_actions ppa,
998: hr_organization_information HOI_PR
999: WHERE ppa.effective_date between l_period_start and l_period_end
1000: AND ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1039: ASG.assignment_id assignment_id,
1040: paa.tax_unit_id tax_unit_id,
1041: ppa.effective_date effective_end_date
1042: FROM per_all_assignments_f ASG,
1043: pay_assignment_actions paa,
1044: pay_payroll_actions ppa,
1045: hr_organization_information HOI_PR,
1046: pay_population_ranges ppr
1047: WHERE ppa.effective_date between l_period_start and l_period_end
1464: --
1465: -- Create the assignment action to represnt the person / tax unit
1466: -- combination.
1467: --
1468: select pay_assignment_actions_s.nextval
1469: into lockingactid
1470: from dual;
1471: --
1472: -- insert into pay_assignment_actions.
1468: select pay_assignment_actions_s.nextval
1469: into lockingactid
1470: from dual;
1471: --
1472: -- insert into pay_assignment_actions.
1473: hr_nonrun_asact.insact(lockingactid,l_assignment_id,
1474: pactid,chunk,l_tax_unit_id);
1475: hr_utility.set_location ('actio_creation',8);
1476:
1506: l_archive boolean:= FALSE;
1507:
1508: cursor c_get_min_chunk is
1509: select min(paa.chunk_number)
1510: from pay_assignment_actions paa
1511: where paa.payroll_action_id = p_payroll_action_id;
1512:
1513: /* End of Bug 773937 */
1514:
1851: */
1852:
1853: procedure archive_data(p_assactid in number, p_effective_date in date) is
1854:
1855: aaid pay_assignment_actions.assignment_action_id%type;
1856: aaseq pay_assignment_actions.action_sequence%type;
1857: asgid pay_assignment_actions.assignment_id%type;
1858: date_earned date;
1859: eff_date date;
1852:
1853: procedure archive_data(p_assactid in number, p_effective_date in date) is
1854:
1855: aaid pay_assignment_actions.assignment_action_id%type;
1856: aaseq pay_assignment_actions.action_sequence%type;
1857: asgid pay_assignment_actions.assignment_id%type;
1858: date_earned date;
1859: eff_date date;
1860: l_year_start date;
1853: procedure archive_data(p_assactid in number, p_effective_date in date) is
1854:
1855: aaid pay_assignment_actions.assignment_action_id%type;
1856: aaseq pay_assignment_actions.action_sequence%type;
1857: asgid pay_assignment_actions.assignment_id%type;
1858: date_earned date;
1859: eff_date date;
1860: l_year_start date;
1861: l_year_end date;
1858: date_earned date;
1859: eff_date date;
1860: l_year_start date;
1861: l_year_end date;
1862: taxunitid pay_assignment_actions.tax_unit_id%type;
1863: l_period_start date;
1864: l_period_end date;
1865:
1866: /* Bug 773937 */
1944: -- AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
1945: -- AND EXISTS(
1946: -- SELECT NULL
1947: -- FROM pay_payroll_actions ppa1,
1948: -- pay_assignment_actions paa1,
1949: -- pay_us_asg_reporting puar1
1950: -- WHERE paa1.assignment_id = paf1.assignment_id
1951: -- AND ppa1.payroll_action_id = paa1.payroll_action_id
1952: -- AND puar1.assignment_id = paf1.assignment_id
1994: -- AND paei.information_type(+) = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
1995: -- AND EXISTS(
1996: -- SELECT NULL
1997: -- FROM pay_payroll_actions ppa1,
1998: -- pay_assignment_actions paa1,
1999: -- pay_us_asg_reporting puar1
2000: -- WHERE paa1.assignment_id = paf1.assignment_id
2001: -- AND ppa1.payroll_action_id = paa1.payroll_action_id
2002: -- AND puar1.assignment_id = paf1.assignment_id
2040: /* Commented out and modified query for improving performance (bug 6774422)
2041: CURSOR c_get_latest_asg(p_assignment_id number ) IS
2042: select paa.assignment_action_id,
2043: ppa.effective_date
2044: from pay_assignment_actions paa,
2045: per_all_assignments_f paf,
2046: pay_payroll_actions ppa,
2047: pay_action_classifications pac,
2048: per_all_assignments_f paf1
2074: PAA.ASSIGNMENT_ACTION_ID,
2075: PPA.EFFECTIVE_DATE
2076: FROM PER_ALL_ASSIGNMENTS_F PAF1,
2077: PER_ALL_ASSIGNMENTS_F PAF ,
2078: PAY_ASSIGNMENT_ACTIONS PAA,
2079: PAY_PAYROLL_ACTIONS PPA ,
2080: PAY_ACTION_CLASSIFICATIONS PAC
2081: WHERE PAF1.ASSIGNMENT_ID = p_assignment_id
2082: AND PAF.PERSON_ID = PAF1.PERSON_ID
2110: date_earned,
2111: taxunitid,
2112: l_chunk, /* Bug 773937 */
2113: l_payroll_action_id /* Bug 773937 */
2114: FROM pay_assignment_actions aa
2115: WHERE aa.assignment_action_id = p_assactid;
2116:
2117: /*
2118: The following code was added on 08-FEB-2000 by Ashu Gupta (ashgupta) to
2348: aaid := -9999;
2349: raise_application_error(-20001,'Balance Assignment Action does not exist for : '||to_char(asgid));
2350: end;
2351:
2352: /* Updating the serial Number column of pay_assignment_actions with 1 if
2353: the effective_date of the assignment action id is lying in the
2354: fourth quarter. */
2355:
2356: if g_sqwl_state = 'NY' and to_char(p_effective_date,'MM-DD') = '12-31'
2356: if g_sqwl_state = 'NY' and to_char(p_effective_date,'MM-DD') = '12-31'
2357: THEN
2358: if(eff_date < trunc(p_effective_date,'Q'))
2359: THEN
2360: update pay_assignment_actions paa
2361: set serial_number = 1
2362: where paa.assignment_action_id = p_assactid;
2363: END IF;
2364: END IF;
2402: 'A_SIT_WITHHELD_PER_JD_GRE_QTD')
2403: )
2404: and ffai.context1 in (
2405: select paa.assignment_action_id
2406: from pay_assignment_actions paa,
2407: pay_payroll_actions ppa
2408: where ppa.payroll_action_id = paa.payroll_action_id
2409: and ppa.report_type = 'SQWL'
2410: and ppa.report_qualifier = 'NY'
2586: CURSOR c_asg_loc_mon ( p_ass_act_id number
2587: ,p_mon_of_qtr number) IS
2588: SELECT ASG.LOCATION_ID
2589: FROM per_assignments_f ASG
2590: , pay_assignment_actions ASSACT
2591: , pay_payroll_actions PACT
2592: WHERE ASSACT.assignment_action_id = p_ass_act_id
2593: AND ASSACT.payroll_action_id = PACT.payroll_action_id
2594: AND ASSACT.assignment_id = ASG.assignment_id
2608: CURSOR c_asg_loc_end (p_ass_acti_id number) IS
2609: /*Commenting for bug 2510853
2610: SELECT paf.location_id
2611: FROM per_assignments_f paf,
2612: pay_assignment_actions paa,
2613: pay_payroll_actions ppa
2614: WHERE (paa.assignment_action_id = p_ass_acti_id
2615: AND paa.payroll_action_id = ppa.payroll_action_id
2616: AND paa.assignment_id = paf.assignment_id
2633: */
2634:
2635: SELECT paf.location_id
2636: FROM per_assignments_f paf,
2637: pay_assignment_actions paa,
2638: pay_payroll_actions ppa
2639: WHERE paa.assignment_action_id = p_ass_acti_id
2640: AND paa.payroll_action_id = ppa.payroll_action_id
2641: AND paa.assignment_id = paf.assignment_id