[Home] [Help]
18: report the Employer(GRE) with zero wages but
19: not display employees with zero wages.
20:
21: Added code to update Serial Number column
22: of pay_assignment_actions to 'X' when the
23: SUI wages value is zero for the employee.
24:
25: The assignments which are marked as 'X' in the Serial_number
26: columns will be excluded from the flat file. The cursor
240: req 1063413
241: 03_DEC-1999 asasthan 40.6 1093595
242: 03-DEC-1999 rpotnuru 40.5 1095096 NY sqwl for 4th qtr date range is Year St to
243: 1085774 Year End. so for reporting QTD balances
244: setting a flag in pay_assignment_actions
245: if the employee doesnt have balances for the QTD.
246: Added function update_ff_archive_item.
247:
248: 17-NOV-1999 asasthan Performance Tuning 1079787
283: 30-dec-1998 vmehta 709641 Look at SUI_ER_SUBJ_WHABLE instead of SUI_ER_GROSS
284: for picking up people for SQWL . This makes sure
285: that only people with SUI wages are picked up.
286: 27-dec-1998 vmehta Corrected the cursor in action creation to get the
287: tax_unit_name from pay_assignment_actions.
288: 21-DEC-1998 achauhan Changed the cursor in action creation to get the
289: assignments from the pay_assignment_actions table.
290:
291: 08-DEC-1998 vmehta Removed grouping by on assignment_id while creating
285: that only people with SUI wages are picked up.
286: 27-dec-1998 vmehta Corrected the cursor in action creation to get the
287: tax_unit_name from pay_assignment_actions.
288: 21-DEC-1998 achauhan Changed the cursor in action creation to get the
289: assignments from the pay_assignment_actions table.
290:
291: 08-DEC-1998 vmehta Removed grouping by on assignment_id while creating
292: assignment_ids
293: 08-DEC-1998 nbristow Updated the c_state cursor to use
766: that are created. */
767:
768: l_payroll_action_created boolean := false;
769: l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
770: l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
771:
772:
773: /* Variable holding the balance to be tested. */
774:
829: ASG.assignment_id assignment_id,
830: paa.tax_unit_id tax_unit_id,
831: ppa.effective_date effective_end_date
832: FROM per_all_assignments_f ASG,
833: pay_assignment_actions paa,
834: pay_payroll_actions ppa
835: WHERE ppa.effective_date between l_period_start
836: and l_period_end
837: and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
867: ASG.assignment_id assignment_id,
868: paa.tax_unit_id tax_unit_id,
869: ppa.effective_date effective_end_date
870: FROM per_all_assignments_f ASG,
871: pay_assignment_actions paa,
872: pay_payroll_actions ppa,
873: pay_population_ranges ppr
874: WHERE ppa.effective_date between l_period_start
875: and l_period_end
906: ASG.assignment_id assignment_id,
907: paa.tax_unit_id tax_unit_id,
908: ppa.effective_date effective_end_date
909: FROM per_all_assignments_f ASG,
910: pay_assignment_actions paa,
911: pay_payroll_actions ppa
912: WHERE ppa.effective_date between l_period_start
913: and l_period_end
914: and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
944: ASG.assignment_id assignment_id,
945: paa.tax_unit_id tax_unit_id,
946: ppa.effective_date effective_end_date
947: FROM per_all_assignments_f ASG,
948: pay_assignment_actions paa,
949: pay_payroll_actions ppa,
950: pay_population_ranges ppr
951: WHERE ppa.effective_date between l_period_start
952: and l_period_end
986: ASG.assignment_id assignment_id,
987: paa.tax_unit_id tax_unit_id,
988: ppa.effective_date effective_end_date
989: FROM per_all_assignments_f ASG,
990: pay_assignment_actions paa,
991: pay_payroll_actions ppa
992: WHERE ppa.effective_date between l_period_start
993: and l_period_end
994: and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1023: ASG.assignment_id assignment_id,
1024: paa.tax_unit_id tax_unit_id,
1025: ppa.effective_date effective_end_date
1026: FROM per_all_assignments_f ASG,
1027: pay_assignment_actions paa,
1028: pay_payroll_actions ppa,
1029: pay_population_ranges ppr
1030: WHERE ppa.effective_date between l_period_start
1031: and l_period_end
1062: ASG.assignment_id assignment_id,
1063: paa.tax_unit_id tax_unit_id,
1064: ppa.effective_date effective_end_date
1065: FROM per_all_assignments_f ASG,
1066: pay_assignment_actions paa,
1067: pay_payroll_actions ppa,
1068: hr_organization_information HOI_PR
1069: WHERE ppa.effective_date between l_period_start and l_period_end
1070: AND ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1109: ASG.assignment_id assignment_id,
1110: paa.tax_unit_id tax_unit_id,
1111: ppa.effective_date effective_end_date
1112: FROM per_all_assignments_f ASG,
1113: pay_assignment_actions paa,
1114: pay_payroll_actions ppa,
1115: hr_organization_information HOI_PR,
1116: pay_population_ranges ppr
1117: WHERE ppa.effective_date between l_period_start and l_period_end
1545: --
1546: -- Create the assignment action to represnt the person / tax unit
1547: -- combination.
1548: --
1549: select pay_assignment_actions_s.nextval
1550: into lockingactid
1551: from dual;
1552: --
1553:
1556: FROM pay_payroll_actions
1557: WHERE payroll_action_id = pactid;
1558:
1559: hr_utility.set_location ('actio_creation',8);
1560: -- insert into pay_assignment_actions.
1561: /* Bug: 10649380 : Create Assignment action only if GRE is equal to the
1562: Tranfer GRE parameter which is getting passed through the new SQWL process
1563: State Quarterly Wage Listing (Enhanced) */
1564: IF l_transfer_gre_id is null OR l_transfer_gre_id = l_tax_unit_id THEN
1567: hr_nonrun_asact.insact(lockingactid,l_assignment_id,
1568: pactid,chunk,l_tax_unit_id);
1569: hr_utility.set_location ('actio_creation',10);
1570: IF l_value= 0 THEN /*bug 14456648 */
1571: UPDATE pay_assignment_actions
1572: SET SERIAL_NUMBER = 'X'
1573: WHERE assignment_action_id = lockingactid;
1574: END IF;
1575:
1605: l_archive boolean:= FALSE;
1606:
1607: cursor c_get_min_chunk is
1608: select min(paa.chunk_number)
1609: from pay_assignment_actions paa
1610: where paa.payroll_action_id = p_payroll_action_id;
1611:
1612: /* End of Bug 773937 */
1613:
1950: */
1951:
1952: procedure archive_data(p_assactid in number, p_effective_date in date) is
1953:
1954: aaid pay_assignment_actions.assignment_action_id%type;
1955: aaseq pay_assignment_actions.action_sequence%type;
1956: asgid pay_assignment_actions.assignment_id%type;
1957: date_earned date;
1958: eff_date date;
1951:
1952: procedure archive_data(p_assactid in number, p_effective_date in date) is
1953:
1954: aaid pay_assignment_actions.assignment_action_id%type;
1955: aaseq pay_assignment_actions.action_sequence%type;
1956: asgid pay_assignment_actions.assignment_id%type;
1957: date_earned date;
1958: eff_date date;
1959: l_year_start date;
1952: procedure archive_data(p_assactid in number, p_effective_date in date) is
1953:
1954: aaid pay_assignment_actions.assignment_action_id%type;
1955: aaseq pay_assignment_actions.action_sequence%type;
1956: asgid pay_assignment_actions.assignment_id%type;
1957: date_earned date;
1958: eff_date date;
1959: l_year_start date;
1960: l_year_end date;
1957: date_earned date;
1958: eff_date date;
1959: l_year_start date;
1960: l_year_end date;
1961: taxunitid pay_assignment_actions.tax_unit_id%type;
1962: l_period_start date;
1963: l_period_end date;
1964:
1965: /* Bug 773937 */
2042: -- AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
2043: -- AND EXISTS(
2044: -- SELECT NULL
2045: -- FROM pay_payroll_actions ppa1,
2046: -- pay_assignment_actions paa1,
2047: -- pay_us_asg_reporting puar1
2048: -- WHERE paa1.assignment_id = paf1.assignment_id
2049: -- AND ppa1.payroll_action_id = paa1.payroll_action_id
2050: -- AND puar1.assignment_id = paf1.assignment_id
2092: -- AND paei.information_type(+) = 'PAY_US_ASG_STATE_WAGE_PLAN_CD'
2093: -- AND EXISTS(
2094: -- SELECT NULL
2095: -- FROM pay_payroll_actions ppa1,
2096: -- pay_assignment_actions paa1,
2097: -- pay_us_asg_reporting puar1
2098: -- WHERE paa1.assignment_id = paf1.assignment_id
2099: -- AND ppa1.payroll_action_id = paa1.payroll_action_id
2100: -- AND puar1.assignment_id = paf1.assignment_id
2138: /* Commented out and modified query for improving performance (bug 6774422)
2139: CURSOR c_get_latest_asg(p_assignment_id number ) IS
2140: select paa.assignment_action_id,
2141: ppa.effective_date
2142: from pay_assignment_actions paa,
2143: per_all_assignments_f paf,
2144: pay_payroll_actions ppa,
2145: pay_action_classifications pac,
2146: per_all_assignments_f paf1
2172: PAA.ASSIGNMENT_ACTION_ID,
2173: PPA.EFFECTIVE_DATE
2174: FROM PER_ALL_ASSIGNMENTS_F PAF1,
2175: PER_ALL_ASSIGNMENTS_F PAF ,
2176: PAY_ASSIGNMENT_ACTIONS PAA,
2177: PAY_PAYROLL_ACTIONS PPA ,
2178: PAY_ACTION_CLASSIFICATIONS PAC
2179: WHERE PAF1.ASSIGNMENT_ID = p_assignment_id
2180: AND PAF.PERSON_ID = PAF1.PERSON_ID
2236:
2237: CURSOR get_previous_fl_taxable IS
2238: select sum(to_number(nvl(value,'0')))
2239: from ff_archive_items ffai,
2240: pay_assignment_actions paa,
2241: pay_payroll_actions ppa
2242: where ffai.user_entity_id = l_user_entity_id
2243: and ffai.context1=to_char(paa.assignment_action_id)
2244: and paa.tax_unit_id = taxunitid
2268: CURSOR get_effective_date (p_quarter_start_date DATE,
2269: p_quarter_end_date DATE) IS
2270: select max(ppa.effective_date)
2271: from per_all_assignments_f asg,
2272: pay_assignment_actions paa,
2273: pay_payroll_actions ppa
2274: where ppa.effective_date between p_quarter_start_date
2275: and p_quarter_end_date
2276: and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
2307: -- Cursor to get the sum of archived values in the Year for AR
2308: CURSOR get_previous_ar_value(l_user_entity_id IN NUMBER) IS
2309: select sum(to_number(nvl(value,'0')))
2310: from ff_archive_items ffai,
2311: pay_assignment_actions paa,
2312: pay_payroll_actions ppa
2313: where ffai.user_entity_id = l_user_entity_id
2314: and ffai.context1=to_char(paa.assignment_action_id)
2315: and paa.tax_unit_id = taxunitid
2447: date_earned,
2448: taxunitid,
2449: l_chunk, /* Bug 773937 */
2450: l_payroll_action_id /* Bug 773937 */
2451: FROM pay_assignment_actions aa
2452: WHERE aa.assignment_action_id = p_assactid;
2453:
2454: /*
2455: BUG# 12322280
3281: aaid := -9999;
3282: raise_application_error(-20001,'Balance Assignment Action does not exist for : '||to_char(asgid));
3283: end;
3284:
3285: /* Updating the serial Number column of pay_assignment_actions with 1 if
3286: the effective_date of the assignment action id is lying in the
3287: fourth quarter. */
3288:
3289: if g_sqwl_state = 'NY' and to_char(p_effective_date,'MM-DD') = '12-31'
3289: if g_sqwl_state = 'NY' and to_char(p_effective_date,'MM-DD') = '12-31'
3290: THEN
3291: if(eff_date < trunc(p_effective_date,'Q'))
3292: THEN
3293: update pay_assignment_actions paa
3294: set serial_number = 1
3295: where paa.assignment_action_id = p_assactid;
3296: END IF;
3297: END IF;
3339: 'A_SIT_WITHHELD_PER_JD_GRE_QTD')
3340: )
3341: and ffai.context1 in (
3342: select paa.assignment_action_id
3343: from pay_assignment_actions paa,
3344: pay_payroll_actions ppa
3345: where ppa.payroll_action_id = paa.payroll_action_id
3346: and ppa.report_type = 'SQWL'
3347: and ppa.report_qualifier = 'NY'
3523: CURSOR c_asg_loc_mon ( p_ass_act_id number
3524: ,p_mon_of_qtr number) IS
3525: SELECT ASG.LOCATION_ID
3526: FROM per_assignments_f ASG
3527: , pay_assignment_actions ASSACT
3528: , pay_payroll_actions PACT
3529: WHERE ASSACT.assignment_action_id = p_ass_act_id
3530: AND ASSACT.payroll_action_id = PACT.payroll_action_id
3531: AND ASSACT.assignment_id = ASG.assignment_id
3536: CURSOR c_asg_loc_mon2 ( p_ass_act_id number
3537: ,p_mon_of_qtr number) IS
3538: SELECT ASG.LOCATION_ID
3539: FROM per_assignments_f ASG
3540: , pay_assignment_actions ASSACT
3541: , pay_payroll_actions PACT
3542: WHERE ASSACT.assignment_action_id = p_ass_act_id
3543: AND ASSACT.payroll_action_id = PACT.payroll_action_id
3544: AND ASSACT.assignment_id = ASG.assignment_id
3562: CURSOR c_asg_loc_end (p_ass_acti_id number) IS
3563: /*Commenting for bug 2510853
3564: SELECT paf.location_id
3565: FROM per_assignments_f paf,
3566: pay_assignment_actions paa,
3567: pay_payroll_actions ppa
3568: WHERE (paa.assignment_action_id = p_ass_acti_id
3569: AND paa.payroll_action_id = ppa.payroll_action_id
3570: AND paa.assignment_id = paf.assignment_id
3587: */
3588:
3589: SELECT paf.location_id
3590: FROM per_assignments_f paf,
3591: pay_assignment_actions paa,
3592: pay_payroll_actions ppa
3593: WHERE paa.assignment_action_id = p_ass_acti_id
3594: AND paa.payroll_action_id = ppa.payroll_action_id
3595: AND paa.assignment_id = paf.assignment_id