[Home] [Help]
281: lv_state_fs_meaning hr_lookups.meaning%TYPE;
282: lv_state_04_fs_meaning hr_lookups.meaning%TYPE;
283:
284: BEGIN
285: hr_utility.trace('Entering Into '||gv_package_name||'.Fed_State_Filing_Status_Match');
286: hr_utility.trace('p_state_code := '||p_state_code);
287: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
288: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
282: lv_state_04_fs_meaning hr_lookups.meaning%TYPE;
283:
284: BEGIN
285: hr_utility.trace('Entering Into '||gv_package_name||'.Fed_State_Filing_Status_Match');
286: hr_utility.trace('p_state_code := '||p_state_code);
287: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
288: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
283:
284: BEGIN
285: hr_utility.trace('Entering Into '||gv_package_name||'.Fed_State_Filing_Status_Match');
286: hr_utility.trace('p_state_code := '||p_state_code);
287: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
288: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
291: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
284: BEGIN
285: hr_utility.trace('Entering Into '||gv_package_name||'.Fed_State_Filing_Status_Match');
286: hr_utility.trace('p_state_code := '||p_state_code);
287: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
288: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
291: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
292: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
285: hr_utility.trace('Entering Into '||gv_package_name||'.Fed_State_Filing_Status_Match');
286: hr_utility.trace('p_state_code := '||p_state_code);
287: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
288: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
291: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
292: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
293: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
286: hr_utility.trace('p_state_code := '||p_state_code);
287: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
288: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
291: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
292: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
293: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
294:
287: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
288: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
291: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
292: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
293: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
294:
295: lv_fed_03_meaning := REPLACE(UPPER('Married, but Withhold at Higher Single Rate'), ',');
288: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
291: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
292: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
293: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
294:
295: lv_fed_03_meaning := REPLACE(UPPER('Married, but Withhold at Higher Single Rate'), ',');
296:
289: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
290: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
291: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
292: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
293: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
294:
295: lv_fed_03_meaning := REPLACE(UPPER('Married, but Withhold at Higher Single Rate'), ',');
296:
297: OPEN cur_state_name(p_state_code);
297: OPEN cur_state_name(p_state_code);
298: FETCH cur_state_name INTO lv_state_name;
299: CLOSE cur_state_name;
300:
301: hr_utility.trace('lv_state_name := '||lv_state_name);
302:
303: OPEN cur_filing_status_meaning(p_state_code
304: ,p_state_org_filing_status_code) ;
305: FETCH cur_filing_status_meaning INTO lv_state_fs_meaning;
304: ,p_state_org_filing_status_code) ;
305: FETCH cur_filing_status_meaning INTO lv_state_fs_meaning;
306: CLOSE cur_filing_status_meaning;
307:
308: hr_utility.trace('lv_state_fs_meaning := '||lv_state_fs_meaning);
309:
310: OPEN cur_filing_status_meaning(p_state_code
311: ,'04') ;
312: FETCH cur_filing_status_meaning INTO lv_state_04_fs_meaning;
311: ,'04') ;
312: FETCH cur_filing_status_meaning INTO lv_state_04_fs_meaning;
313: CLOSE cur_filing_status_meaning;
314:
315: hr_utility.trace('lv_state_04_fs_meaning := '||lv_state_04_fs_meaning);
316:
317: /* Bug 6346579 : Start. NRA employee trying to enable FIT Exempt. No need to proceed. */
318: IF (g_NRA_flag = 'Y') THEN
319: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
323: g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
324: END IF;
325: g_nonmatch_cntr := g_nonmatch_cntr + 1;
326: END IF;
327: hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
328: hr_utility.trace('Not Going to Update State Info From Federal.');
329: RETURN FALSE;
330: END IF;
331: /* Bug 6346579 : End */
324: END IF;
325: g_nonmatch_cntr := g_nonmatch_cntr + 1;
326: END IF;
327: hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
328: hr_utility.trace('Not Going to Update State Info From Federal.');
329: RETURN FALSE;
330: END IF;
331: /* Bug 6346579 : End */
332:
345: IF p_new_filing_status_code = '03' THEN
346: /* Modified for bug no 7521930*/
347: IF p_state_code NOT IN ('27','38','41','45') THEN
348: p_new_filing_status_code := '04';
349: hr_utility.trace('p_new_filing_status_code 1 := ' ||p_new_filing_status_code);
350: RETURN TRUE;
351: ELSE
352: hr_utility.trace('p_new_filing_status_code 1 := ' ||p_new_filing_status_code);
353: RETURN TRUE;
348: p_new_filing_status_code := '04';
349: hr_utility.trace('p_new_filing_status_code 1 := ' ||p_new_filing_status_code);
350: RETURN TRUE;
351: ELSE
352: hr_utility.trace('p_new_filing_status_code 1 := ' ||p_new_filing_status_code);
353: RETURN TRUE;
354: END IF ;
355:
356: ELSE
353: RETURN TRUE;
354: END IF ;
355:
356: ELSE
357: hr_utility.trace('p_new_filing_status_code 2 := ' ||p_new_filing_status_code);
358: RETURN TRUE;
359: END IF;
360:
361: ELSIF (p_fed_org_filing_status_code = '03' AND p_state_org_filing_status_code = '04'
360:
361: ELSIF (p_fed_org_filing_status_code = '03' AND p_state_org_filing_status_code = '04'
362: AND p_state_org_wa = p_fed_org_wa
363: AND p_state_empt_cnt = p_fed_exmpt_cnt) THEN
364: hr_utility.trace('p_new_filing_status_code 2 := ' ||p_new_filing_status_code);
365: RETURN TRUE;
366: ELSE
367: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
368:
374: g_nonmatch_cntr := g_nonmatch_cntr + 1;
375: END IF;
376:
377: p_new_filing_status_code := p_state_org_filing_status_code ;
378: hr_utility.trace('p_new_filing_status_code 3 := ' || p_new_filing_status_code);
379: hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
380: hr_utility.trace('Not Going to Update State Info From Federal.');
381: RETURN FALSE;
382: END IF;
375: END IF;
376:
377: p_new_filing_status_code := p_state_org_filing_status_code ;
378: hr_utility.trace('p_new_filing_status_code 3 := ' || p_new_filing_status_code);
379: hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
380: hr_utility.trace('Not Going to Update State Info From Federal.');
381: RETURN FALSE;
382: END IF;
383:
376:
377: p_new_filing_status_code := p_state_org_filing_status_code ;
378: hr_utility.trace('p_new_filing_status_code 3 := ' || p_new_filing_status_code);
379: hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
380: hr_utility.trace('Not Going to Update State Info From Federal.');
381: RETURN FALSE;
382: END IF;
383:
384: ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
382: END IF;
383:
384: ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
385:
386: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
387: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
388: hr_utility.trace('above should match here');
389: IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
390: AND p_state_org_wa = p_fed_org_wa
383:
384: ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
385:
386: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
387: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
388: hr_utility.trace('above should match here');
389: IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
390: AND p_state_org_wa = p_fed_org_wa
391: AND p_state_empt_cnt = p_fed_exmpt_cnt) OR
384: ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
385:
386: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
387: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
388: hr_utility.trace('above should match here');
389: IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
390: AND p_state_org_wa = p_fed_org_wa
391: AND p_state_empt_cnt = p_fed_exmpt_cnt) OR
392:
394: AND p_state_org_wa = p_fed_org_wa
395: AND p_state_empt_cnt = p_fed_exmpt_cnt) THEN
396:
397: p_new_filing_status_code := p_state_org_filing_status_code;
398: hr_utility.trace('p_new_filing_status_code 4 := ' ||p_new_filing_status_code);
399: hr_utility.trace('RETURNING TRUE HERE 2 ' ||p_new_filing_status_code);
400: RETURN TRUE;
401: ELSE
402: hr_utility.trace('g_not_matching_state_list := '||g_not_matching_state_list);
395: AND p_state_empt_cnt = p_fed_exmpt_cnt) THEN
396:
397: p_new_filing_status_code := p_state_org_filing_status_code;
398: hr_utility.trace('p_new_filing_status_code 4 := ' ||p_new_filing_status_code);
399: hr_utility.trace('RETURNING TRUE HERE 2 ' ||p_new_filing_status_code);
400: RETURN TRUE;
401: ELSE
402: hr_utility.trace('g_not_matching_state_list := '||g_not_matching_state_list);
403: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
398: hr_utility.trace('p_new_filing_status_code 4 := ' ||p_new_filing_status_code);
399: hr_utility.trace('RETURNING TRUE HERE 2 ' ||p_new_filing_status_code);
400: RETURN TRUE;
401: ELSE
402: hr_utility.trace('g_not_matching_state_list := '||g_not_matching_state_list);
403: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
404:
405: IF g_not_matching_state_list IS NOT NULL THEN
406: hr_utility.trace('g_not_matchingIS NOT NULL THEN := '||g_not_matching_state_list);
402: hr_utility.trace('g_not_matching_state_list := '||g_not_matching_state_list);
403: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
404:
405: IF g_not_matching_state_list IS NOT NULL THEN
406: hr_utility.trace('g_not_matchingIS NOT NULL THEN := '||g_not_matching_state_list);
407: g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
408: ELSE
409: hr_utility.trace('g_not_matchingIS NULL THEN := '||g_not_matching_state_list);
410: g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
405: IF g_not_matching_state_list IS NOT NULL THEN
406: hr_utility.trace('g_not_matchingIS NOT NULL THEN := '||g_not_matching_state_list);
407: g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
408: ELSE
409: hr_utility.trace('g_not_matchingIS NULL THEN := '||g_not_matching_state_list);
410: g_not_matching_state_list := g_not_matching_state_list || ' '||lv_state_name;
411: END IF;
412: g_nonmatch_cntr := g_nonmatch_cntr + 1;
413: END IF;
412: g_nonmatch_cntr := g_nonmatch_cntr + 1;
413: END IF;
414:
415: p_new_filing_status_code := p_state_org_filing_status_code ;
416: hr_utility.trace('p_new_filing_status_code 4 := ' || p_new_filing_status_code);
417: hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
418: hr_utility.trace('Not Going to Update State Info From Federal.');
419:
420: RETURN FALSE;
413: END IF;
414:
415: p_new_filing_status_code := p_state_org_filing_status_code ;
416: hr_utility.trace('p_new_filing_status_code 4 := ' || p_new_filing_status_code);
417: hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
418: hr_utility.trace('Not Going to Update State Info From Federal.');
419:
420: RETURN FALSE;
421: END IF;
414:
415: p_new_filing_status_code := p_state_org_filing_status_code ;
416: hr_utility.trace('p_new_filing_status_code 4 := ' || p_new_filing_status_code);
417: hr_utility.trace('g_nonmatch_cntr := '|| g_nonmatch_cntr);
418: hr_utility.trace('Not Going to Update State Info From Federal.');
419:
420: RETURN FALSE;
421: END IF;
422: END IF; -- p_fed_org_filing_status_code <> p_new_filing_status_code
428: ELSIF p_state_code IN ('04', '05', '19', '22', '31', '46', '49', '50') THEN */
429:
430: ELSIF p_state_code IN ('05', '19', '22', '31', '46', '49', '50') THEN
431: -- For States that May or May Not Follow Federal
432: hr_utility.trace('States that May or May Not Follow Federal ');
433: IF p_fed_org_filing_status_code <> p_new_filing_status_code THEN
434: -- Filing Status Changed during Transaction
435:
436: IF p_state_code NOT IN ( '19', '22' ) THEN
448: )
449: ) THEN
450:
451: IF p_new_filing_status_code IN ('01', '02') THEN
452: hr_utility.trace('Returning True Here 01 or 02');
453: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
454: RETURN TRUE;
455: ELSIF p_new_filing_status_code = '03' AND
456: REPLACE(UPPER(lv_state_04_fs_meaning), ',') = lv_fed_03_meaning THEN
449: ) THEN
450:
451: IF p_new_filing_status_code IN ('01', '02') THEN
452: hr_utility.trace('Returning True Here 01 or 02');
453: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
454: RETURN TRUE;
455: ELSIF p_new_filing_status_code = '03' AND
456: REPLACE(UPPER(lv_state_04_fs_meaning), ',') = lv_fed_03_meaning THEN
457: p_new_filing_status_code := '04';
466: END IF;
467: g_nonmatch_cntr := g_nonmatch_cntr + 1;
468: END IF;
469: p_new_filing_status_code := p_state_org_filing_status_code ;
470: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
471: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
472: hr_utility.trace('Not Going to Update State Info From Federal.');
473: RETURN FALSE;
474: END IF;
467: g_nonmatch_cntr := g_nonmatch_cntr + 1;
468: END IF;
469: p_new_filing_status_code := p_state_org_filing_status_code ;
470: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
471: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
472: hr_utility.trace('Not Going to Update State Info From Federal.');
473: RETURN FALSE;
474: END IF;
475: ELSE
468: END IF;
469: p_new_filing_status_code := p_state_org_filing_status_code ;
470: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
471: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
472: hr_utility.trace('Not Going to Update State Info From Federal.');
473: RETURN FALSE;
474: END IF;
475: ELSE
476: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
482: END IF;
483: g_nonmatch_cntr := g_nonmatch_cntr + 1;
484: END IF;
485: p_new_filing_status_code := p_state_org_filing_status_code ;
486: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
487: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
488: hr_utility.trace('Not Going to Update State Info From Federal.');
489: RETURN FALSE;
490:
483: g_nonmatch_cntr := g_nonmatch_cntr + 1;
484: END IF;
485: p_new_filing_status_code := p_state_org_filing_status_code ;
486: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
487: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
488: hr_utility.trace('Not Going to Update State Info From Federal.');
489: RETURN FALSE;
490:
491: END IF;
484: END IF;
485: p_new_filing_status_code := p_state_org_filing_status_code ;
486: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
487: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
488: hr_utility.trace('Not Going to Update State Info From Federal.');
489: RETURN FALSE;
490:
491: END IF;
492:
526: END IF;
527: g_nonmatch_cntr := g_nonmatch_cntr + 1;
528: END IF;
529: p_new_filing_status_code := p_state_org_filing_status_code ;
530: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
531: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
532: hr_utility.trace('Not Going to Update State Info From Federal.');
533: RETURN FALSE;
534: END IF;
527: g_nonmatch_cntr := g_nonmatch_cntr + 1;
528: END IF;
529: p_new_filing_status_code := p_state_org_filing_status_code ;
530: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
531: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
532: hr_utility.trace('Not Going to Update State Info From Federal.');
533: RETURN FALSE;
534: END IF;
535: ELSE
528: END IF;
529: p_new_filing_status_code := p_state_org_filing_status_code ;
530: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
531: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
532: hr_utility.trace('Not Going to Update State Info From Federal.');
533: RETURN FALSE;
534: END IF;
535: ELSE
536: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
542: END IF;
543: g_nonmatch_cntr := g_nonmatch_cntr + 1;
544: END IF;
545: p_new_filing_status_code := p_state_org_filing_status_code ;
546: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
547: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
548: hr_utility.trace('Not Going to Update State Info From Federal.');
549: RETURN FALSE;
550: END IF;
543: g_nonmatch_cntr := g_nonmatch_cntr + 1;
544: END IF;
545: p_new_filing_status_code := p_state_org_filing_status_code ;
546: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
547: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
548: hr_utility.trace('Not Going to Update State Info From Federal.');
549: RETURN FALSE;
550: END IF;
551:
544: END IF;
545: p_new_filing_status_code := p_state_org_filing_status_code ;
546: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
547: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
548: hr_utility.trace('Not Going to Update State Info From Federal.');
549: RETURN FALSE;
550: END IF;
551:
552: ELSIF p_state_code = '22' THEN -- For MA, Single (04), Married (04) --> Other than Head of Household
586: END IF;
587: g_nonmatch_cntr := g_nonmatch_cntr + 1;
588: END IF;
589: p_new_filing_status_code := p_state_org_filing_status_code ;
590: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
591: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
592: hr_utility.trace('Not Going to Update State Info From Federal.');
593: RETURN FALSE;
594: END IF;
587: g_nonmatch_cntr := g_nonmatch_cntr + 1;
588: END IF;
589: p_new_filing_status_code := p_state_org_filing_status_code ;
590: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
591: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
592: hr_utility.trace('Not Going to Update State Info From Federal.');
593: RETURN FALSE;
594: END IF;
595: ELSE
588: END IF;
589: p_new_filing_status_code := p_state_org_filing_status_code ;
590: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
591: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
592: hr_utility.trace('Not Going to Update State Info From Federal.');
593: RETURN FALSE;
594: END IF;
595: ELSE
596: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
602: END IF;
603: g_nonmatch_cntr := g_nonmatch_cntr + 1;
604: END IF;
605: p_new_filing_status_code := p_state_org_filing_status_code ;
606: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
607: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
608: RETURN FALSE;
609: END IF;
610:
603: g_nonmatch_cntr := g_nonmatch_cntr + 1;
604: END IF;
605: p_new_filing_status_code := p_state_org_filing_status_code ;
606: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
607: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
608: RETURN FALSE;
609: END IF;
610:
611: END IF; --- p_state_code NOT IN ('19', '22)
611: END IF; --- p_state_code NOT IN ('19', '22)
612:
613: ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
614: --RLN 115.39 bug fix 9546309
615: hr_utility.trace('p_state_code := '||p_state_code);
616: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
617: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
618: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
612:
613: ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
614: --RLN 115.39 bug fix 9546309
615: hr_utility.trace('p_state_code := '||p_state_code);
616: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
617: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
618: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
620: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
613: ELSIF p_fed_org_filing_status_code = p_new_filing_status_code THEN
614: --RLN 115.39 bug fix 9546309
615: hr_utility.trace('p_state_code := '||p_state_code);
616: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
617: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
618: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
620: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
621: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
614: --RLN 115.39 bug fix 9546309
615: hr_utility.trace('p_state_code := '||p_state_code);
616: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
617: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
618: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
620: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
621: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
622: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
615: hr_utility.trace('p_state_code := '||p_state_code);
616: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
617: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
618: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
620: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
621: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
622: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
623:
616: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
617: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
618: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
620: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
621: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
622: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
623:
624: IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
617: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
618: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
620: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
621: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
622: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
623:
624: IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
625: AND REPLACE(UPPER(lv_state_fs_meaning), ',') = lv_fed_03_meaning
618: hr_utility.trace('p_fed_org_wa := '||p_fed_org_wa);
619: hr_utility.trace('p_state_org_wa := '||p_state_org_wa);
620: hr_utility.trace('p_fed_exmpt_cnt := '||p_fed_exmpt_cnt);
621: hr_utility.trace('p_state_empt_cnt := '||p_state_empt_cnt);
622: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
623:
624: IF (p_state_org_filing_status_code = p_fed_org_filing_status_code
625: AND REPLACE(UPPER(lv_state_fs_meaning), ',') = lv_fed_03_meaning
626: AND p_state_org_wa = p_fed_org_wa
660: )
661: THEN
662:
663: p_new_filing_status_code := p_state_org_filing_status_code;
664: hr_utility.trace('p_new_filing_status_code 4 := ' ||p_new_filing_status_code);
665: hr_utility.trace('RETURNING TRUE HERE ' ||p_new_filing_status_code);
666: RETURN TRUE;
667: ELSE
668:
661: THEN
662:
663: p_new_filing_status_code := p_state_org_filing_status_code;
664: hr_utility.trace('p_new_filing_status_code 4 := ' ||p_new_filing_status_code);
665: hr_utility.trace('RETURNING TRUE HERE ' ||p_new_filing_status_code);
666: RETURN TRUE;
667: ELSE
668:
669: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
675: END IF;
676: g_nonmatch_cntr := g_nonmatch_cntr + 1;
677: END IF;
678: p_new_filing_status_code := p_state_org_filing_status_code ;
679: hr_utility.trace('g_nonmatch_cntr 3 := '||g_nonmatch_cntr);
680: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
681: RETURN FALSE;
682:
683: END IF;
676: g_nonmatch_cntr := g_nonmatch_cntr + 1;
677: END IF;
678: p_new_filing_status_code := p_state_org_filing_status_code ;
679: hr_utility.trace('g_nonmatch_cntr 3 := '||g_nonmatch_cntr);
680: hr_utility.trace('p_state_org_filing_status_code := '||p_state_org_filing_status_code);
681: RETURN FALSE;
682:
683: END IF;
684: END IF; -- p_fed_org_filing_status_code <> p_new_filing_status_code
682:
683: END IF;
684: END IF; -- p_fed_org_filing_status_code <> p_new_filing_status_code
685: ELSE
686: hr_utility.trace('here 2');
687: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
688: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
689: hr_utility.trace('above should match here');
690:
683: END IF;
684: END IF; -- p_fed_org_filing_status_code <> p_new_filing_status_code
685: ELSE
686: hr_utility.trace('here 2');
687: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
688: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
689: hr_utility.trace('above should match here');
690:
691: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
684: END IF; -- p_fed_org_filing_status_code <> p_new_filing_status_code
685: ELSE
686: hr_utility.trace('here 2');
687: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
688: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
689: hr_utility.trace('above should match here');
690:
691: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
692:
685: ELSE
686: hr_utility.trace('here 2');
687: hr_utility.trace('p_fed_org_filing_status_code := '||p_fed_org_filing_status_code);
688: hr_utility.trace('p_new_filing_status_code := '||p_new_filing_status_code);
689: hr_utility.trace('above should match here');
690:
691: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
692:
693: hr_utility.trace('g_not_matching_state_list := '||g_not_matching_state_list);
689: hr_utility.trace('above should match here');
690:
691: IF INSTR(NVL(g_not_matching_state_list, '0'), lv_state_name) = 0 THEN
692:
693: hr_utility.trace('g_not_matching_state_list := '||g_not_matching_state_list);
694:
695: IF g_not_matching_state_list IS NOT NULL THEN
696: g_not_matching_state_list := g_not_matching_state_list || ', ' ||lv_state_name;
697: ELSE
700: g_nonmatch_cntr := g_nonmatch_cntr + 1;
701: END IF;
702: p_new_filing_status_code := p_state_org_filing_status_code ;
703:
704: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
705: RETURN FALSE;
706: END IF; -- State Following Federal Or NOT
707: hr_utility.trace('g_nonmatch_cntr := '||g_nonmatch_cntr);
708: END Fed_State_Filing_Status_Match;
703:
704: hr_utility.trace('g_nonmatch_cntr 2 := '||g_nonmatch_cntr);
705: RETURN FALSE;
706: END IF; -- State Following Federal Or NOT
707: hr_utility.trace('g_nonmatch_cntr := '||g_nonmatch_cntr);
708: END Fed_State_Filing_Status_Match;
709:
710: FUNCTION check_update_status(p_person_id IN per_people_f.person_id%TYPE)
711: RETURN VARCHAR2
794: lv_update_method VARCHAR2(30);
795:
796: BEGIN
797:
798: hr_utility.trace('Entering ' || gv_package_name || '.check_update_status');
799:
800:
801: lv_update_method := fnd_profile.value('HR_OTF_UPDATE_METHOD');
802: hr_utility.trace('OTF Update Method = ' || lv_update_method);
798: hr_utility.trace('Entering ' || gv_package_name || '.check_update_status');
799:
800:
801: lv_update_method := fnd_profile.value('HR_OTF_UPDATE_METHOD');
802: hr_utility.trace('OTF Update Method = ' || lv_update_method);
803:
804: -- check for update method set to NONE
805: hr_utility.trace('Testing PROFILE HR_OTF_UPDATE_METHOD');
806:
801: lv_update_method := fnd_profile.value('HR_OTF_UPDATE_METHOD');
802: hr_utility.trace('OTF Update Method = ' || lv_update_method);
803:
804: -- check for update method set to NONE
805: hr_utility.trace('Testing PROFILE HR_OTF_UPDATE_METHOD');
806:
807: if lv_update_method = 'PRIMARY' then
808: l_primary_only := 'Y';
809:
815: l_primary_only := 'Y';
816:
817: end if;
818:
819: hr_utility.trace('Passed PROFILE HR_OTF_UPDATE_METHOD');
820:
821: -- check for excessive wa reject date or override amounts
822: -- Note: we don't actually check the date of the reject, just
823: -- it's existence shuts the employee out
821: -- check for excessive wa reject date or override amounts
822: -- Note: we don't actually check the date of the reject, just
823: -- it's existence shuts the employee out
824:
825: hr_utility.trace('Testing FED_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
826: open c_excess_over_fed;
827: fetch c_excess_over_fed into curs_dummy;
828:
829: if c_excess_over_fed%FOUND then
826: open c_excess_over_fed;
827: fetch c_excess_over_fed into curs_dummy;
828:
829: if c_excess_over_fed%FOUND then
830: hr_utility.trace('Failed on FED_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
831: close c_excess_over_fed;
832: return ('PAY-PAY_US_OTF_REJECT_DATE_OR_OVER');
833: end if;
834:
832: return ('PAY-PAY_US_OTF_REJECT_DATE_OR_OVER');
833: end if;
834:
835: close c_excess_over_fed;
836: hr_utility.trace('Passed FED_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
837:
838: hr_utility.trace('Testing STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
839: open c_excess_over_state;
840: fetch c_excess_over_state into curs_dummy;
834:
835: close c_excess_over_fed;
836: hr_utility.trace('Passed FED_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
837:
838: hr_utility.trace('Testing STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
839: open c_excess_over_state;
840: fetch c_excess_over_state into curs_dummy;
841:
842: if c_excess_over_state%FOUND then
839: open c_excess_over_state;
840: fetch c_excess_over_state into curs_dummy;
841:
842: if c_excess_over_state%FOUND then
843: hr_utility.trace('Failed on STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
844: close c_excess_over_state;
845: return ('PAY-PAY_US_OTF_REJECT_DATE_OR_OVER');
846: end if;
847: close c_excess_over_state;
844: close c_excess_over_state;
845: return ('PAY-PAY_US_OTF_REJECT_DATE_OR_OVER');
846: end if;
847: close c_excess_over_state;
848: hr_utility.trace('Passed STATE_EXCESSIVE_WA_REJECT_DATE/OVERRIDES');
849:
850:
851: -- check for any future dated changes in non-retiree asgs for both state and fed
852:
849:
850:
851: -- check for any future dated changes in non-retiree asgs for both state and fed
852:
853: hr_utility.trace('Testing FED_FUTURE_DATED_CHANGES');
854: open c_future_fed_recs;
855: fetch c_future_fed_recs into curs_dummy;
856: if c_future_fed_recs%FOUND then
857: hr_utility.trace('Failed on FED_FUTURE_DATED_CHANGES');
853: hr_utility.trace('Testing FED_FUTURE_DATED_CHANGES');
854: open c_future_fed_recs;
855: fetch c_future_fed_recs into curs_dummy;
856: if c_future_fed_recs%FOUND then
857: hr_utility.trace('Failed on FED_FUTURE_DATED_CHANGES');
858: return ('PAY-PAY_US_OTF_FUTURE_RECORDS');
859: end if;
860:
861: close c_future_fed_recs;
858: return ('PAY-PAY_US_OTF_FUTURE_RECORDS');
859: end if;
860:
861: close c_future_fed_recs;
862: hr_utility.trace('Passed FED_FUTURE_DATED_CHANGES');
863:
864: hr_utility.trace('Testing STATE_FUTURE_DATED_CHANGES');
865: open c_future_state_recs;
866: fetch c_future_state_recs into curs_dummy;
860:
861: close c_future_fed_recs;
862: hr_utility.trace('Passed FED_FUTURE_DATED_CHANGES');
863:
864: hr_utility.trace('Testing STATE_FUTURE_DATED_CHANGES');
865: open c_future_state_recs;
866: fetch c_future_state_recs into curs_dummy;
867: if c_future_state_recs%FOUND then
868: hr_utility.trace('Failed on STATE_FUTURE_DATED_CHANGES');
864: hr_utility.trace('Testing STATE_FUTURE_DATED_CHANGES');
865: open c_future_state_recs;
866: fetch c_future_state_recs into curs_dummy;
867: if c_future_state_recs%FOUND then
868: hr_utility.trace('Failed on STATE_FUTURE_DATED_CHANGES');
869: return ('PAY-PAY_US_OTF_FUTURE_RECORDS');
870: end if;
871:
872: close c_future_state_recs;
869: return ('PAY-PAY_US_OTF_FUTURE_RECORDS');
870: end if;
871:
872: close c_future_state_recs;
873: hr_utility.trace('Passed STATE_FUTURE_DATED_CHANGES');
874:
875: -- if we've reached this point, then allow update
876: hr_utility.trace('Leaving ' || gv_package_name || '.check_update_status');
877: return null;
872: close c_future_state_recs;
873: hr_utility.trace('Passed STATE_FUTURE_DATED_CHANGES');
874:
875: -- if we've reached this point, then allow update
876: hr_utility.trace('Leaving ' || gv_package_name || '.check_update_status');
877: return null;
878:
879: EXCEPTION
880: WHEN OTHERS THEN
1011:
1012:
1013: BEGIN
1014:
1015: hr_utility.trace('Entering '|| gv_package_name || '.validate_submission');
1016: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
1017: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
1018: hr_utility.trace('p_allowances = ' || p_allowances);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1012:
1013: BEGIN
1014:
1015: hr_utility.trace('Entering '|| gv_package_name || '.validate_submission');
1016: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
1017: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
1018: hr_utility.trace('p_allowances = ' || p_allowances);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1013: BEGIN
1014:
1015: hr_utility.trace('Entering '|| gv_package_name || '.validate_submission');
1016: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
1017: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
1018: hr_utility.trace('p_allowances = ' || p_allowances);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1014:
1015: hr_utility.trace('Entering '|| gv_package_name || '.validate_submission');
1016: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
1017: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
1018: hr_utility.trace('p_allowances = ' || p_allowances);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1015: hr_utility.trace('Entering '|| gv_package_name || '.validate_submission');
1016: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
1017: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
1018: hr_utility.trace('p_allowances = ' || p_allowances);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1016: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
1017: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
1018: hr_utility.trace('p_allowances = ' || p_allowances);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1017: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
1018: hr_utility.trace('p_allowances = ' || p_allowances);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1018: hr_utility.trace('p_allowances = ' || p_allowances);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1026: hr_utility.trace('p_original_fs = ' || p_original_fs);
1019: hr_utility.trace('p_additional_amount = ' || p_additional_amount);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1026: hr_utility.trace('p_original_fs = ' || p_original_fs);
1027: hr_utility.trace('p_original_wa = ' || p_original_wa);
1020: hr_utility.trace('p_itemtype = ' || p_itemtype);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1026: hr_utility.trace('p_original_fs = ' || p_original_fs);
1027: hr_utility.trace('p_original_wa = ' || p_original_wa);
1028: hr_utility.trace('p_original_exempt = ' || p_original_exempt);
1021: hr_utility.trace('p_itemkey = ' || p_itemkey);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1026: hr_utility.trace('p_original_fs = ' || p_original_fs);
1027: hr_utility.trace('p_original_wa = ' || p_original_wa);
1028: hr_utility.trace('p_original_exempt = ' || p_original_exempt);
1029: hr_utility.trace('p_original_aa = ' || p_original_aa);
1022: hr_utility.trace('p_activity_id = ' || p_activity_id);
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1026: hr_utility.trace('p_original_fs = ' || p_original_fs);
1027: hr_utility.trace('p_original_wa = ' || p_original_wa);
1028: hr_utility.trace('p_original_exempt = ' || p_original_exempt);
1029: hr_utility.trace('p_original_aa = ' || p_original_aa);
1030:
1023: hr_utility.trace('p_last_name_diff = ' || p_last_name_diff);
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1026: hr_utility.trace('p_original_fs = ' || p_original_fs);
1027: hr_utility.trace('p_original_wa = ' || p_original_wa);
1028: hr_utility.trace('p_original_exempt = ' || p_original_exempt);
1029: hr_utility.trace('p_original_aa = ' || p_original_aa);
1030:
1031: -- first this is we clear the global value of g_state_list before we continue
1024: hr_utility.trace('p_agreement = ' || p_agreement);
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1026: hr_utility.trace('p_original_fs = ' || p_original_fs);
1027: hr_utility.trace('p_original_wa = ' || p_original_wa);
1028: hr_utility.trace('p_original_exempt = ' || p_original_exempt);
1029: hr_utility.trace('p_original_aa = ' || p_original_aa);
1030:
1031: -- first this is we clear the global value of g_state_list before we continue
1032: -- this will ensure we get the fresh list everytime a user comes back from the
1025: hr_utility.trace('p_person_id = ' || p_person_id);
1026: hr_utility.trace('p_original_fs = ' || p_original_fs);
1027: hr_utility.trace('p_original_wa = ' || p_original_wa);
1028: hr_utility.trace('p_original_exempt = ' || p_original_exempt);
1029: hr_utility.trace('p_original_aa = ' || p_original_aa);
1030:
1031: -- first this is we clear the global value of g_state_list before we continue
1032: -- this will ensure we get the fresh list everytime a user comes back from the
1033: -- review page.
1044:
1045: -- validate filing status
1046:
1047: BEGIN
1048: hr_utility.trace('Checking filing status');
1049: -- next call the api chk_procedure
1050: -- we call it with a null id to insure that it is validated
1051: -- withhout having to worry about calling api_updating
1052: -- we also provide the sysdate and end-of-time as the validation
1060: ,p_validation_end_date => to_date('31/12/4712','DD/MM/YYYY')
1061: );
1062: EXCEPTION
1063: WHEN OTHERS then
1064: hr_utility.trace('Rejecting Filing Status');
1065:
1066: if l_error is null then
1067: l_error := 'PAY-PAY_FILING_STATUS_ERROR-FilingStatusCode';
1068: else
1074: END;
1075:
1076: -- validate allowances
1077: BEGIN
1078: hr_utility.trace('Checking total allowances');
1079: -- First we convert it into a NUMBER
1080:
1081: l_allowances := to_number(nvl(p_allowances,0),'999');
1082:
1079: -- First we convert it into a NUMBER
1080:
1081: l_allowances := to_number(nvl(p_allowances,0),'999');
1082:
1083: hr_utility.trace('l_allowances = ' || to_char(l_allowances));
1084:
1085: if l_allowances < 0 then
1086: raise VALUE_ERROR;
1087: end if;
1092: ,p_withholding_allowances => l_allowances
1093: );
1094: EXCEPTION
1095: WHEN VALUE_ERROR then
1096: hr_utility.trace('Rejecting Total Allowances - number err');
1097:
1098: if l_error is null then
1099: l_error := 'PAY-PAY_US_OTF_FED_WA_NUMBER-WithholdingAllowances';
1100: else
1104: l_num := l_num + 1;
1105: lv_has_errors := 'Y';
1106:
1107: WHEN OTHERS then
1108: hr_utility.trace('Reject Total Allowances - api err');
1109:
1110: if l_error is null then
1111: l_error := 'PAY-PAY_PLSQL_ERROR-WithholdingAllowances';
1112: else
1124: lv_level := 'ORG';
1125:
1126: lv_notify := get_org_context(ln_person_id, lv_context,lv_level);
1127:
1128: hr_utility.trace('ORG get_org_context lv_notify = '||lv_notify);
1129: if lv_notify = 'NOTFOUND' then
1130: -- Business Group Level Context
1131: lv_context := 'US_BG_REP_PREFERENCES';
1132: lv_level := 'BG';
1130: -- Business Group Level Context
1131: lv_context := 'US_BG_REP_PREFERENCES';
1132: lv_level := 'BG';
1133: lv_notify := get_org_context(ln_person_id, lv_context,lv_level);
1134: hr_utility.trace('BG get_org_context lv_notify = '||lv_notify);
1135: end if;
1136:
1137:
1138: -- Also check if the allowance entered by the user is > to the federal allowance limit.
1140: open c_fed_allowance_limit;
1141: fetch c_fed_allowance_limit into l_fed_allowance_limit;
1142: close c_fed_allowance_limit;
1143:
1144: hr_utility.trace('b4 checking lv_notify = '||lv_notify);
1145:
1146: --------------- added by vaprakas bug 5601735
1147: l_student_flag :='No';
1148:
1208: end if;
1209:
1210: -- validate additional tax
1211: BEGIN
1212: hr_utility.trace('Checking Additional Tax');
1213:
1214: ln_add_tax := nvl(p_additional_amount,0);
1215:
1216: hr_utility.trace('ln_add_tax value is : '||ln_add_tax);
1212: hr_utility.trace('Checking Additional Tax');
1213:
1214: ln_add_tax := nvl(p_additional_amount,0);
1215:
1216: hr_utility.trace('ln_add_tax value is : '||ln_add_tax);
1217: l_additional_tax := ln_add_tax;
1218: hr_utility.trace('l_additional_tax value is : '|| l_additional_tax);
1219:
1220: if l_additional_tax < 0 then
1214: ln_add_tax := nvl(p_additional_amount,0);
1215:
1216: hr_utility.trace('ln_add_tax value is : '||ln_add_tax);
1217: l_additional_tax := ln_add_tax;
1218: hr_utility.trace('l_additional_tax value is : '|| l_additional_tax);
1219:
1220: if l_additional_tax < 0 then
1221: raise value_error;
1222: end if;
1227: ,p_fit_additional_tax => l_additional_tax
1228: );
1229: EXCEPTION
1230: WHEN VALUE_ERROR then
1231: hr_utility.trace('Rejecting add. tax - number err');
1232:
1233: if l_error is null then
1234: l_error := 'PAY-PAY_US_OTF_FED_ADD_TAX_NUMBER-TaxString';
1235: else
1239: l_num := l_num + 1;
1240: lv_has_errors := 'Y';
1241:
1242: WHEN OTHERS then
1243: hr_utility.trace('Rejecting add. tax - api err');
1244:
1245: if l_error is null then
1246: l_error := 'PAY-PAY_PLSQL_ERROR-TaxString';
1247: else
1253: END;
1254:
1255: -- validate exempt status code
1256:
1257: hr_utility.trace('Checking exempt status');
1258: -- the only validation we do is replace a 'N' for null
1259: if p_exempt_status_code = 'on' then
1260: l_exempt_status_code := 'Yes';
1261:
1268: l_exempt_status_code := 'No';
1269: end if;
1270:
1271: -- validate the agreement prompt
1272: hr_utility.trace('Checking agreement flag');
1273:
1274: if p_agreement = 'on' then
1275: l_agreement := 'Y';
1276: else
1273:
1274: if p_agreement = 'on' then
1275: l_agreement := 'Y';
1276: else
1277: hr_utility.trace('Rejecting agreement flag');
1278:
1279: if l_error is null then
1280: l_error := 'PAY-PAY_US_OTF_AGREE_ERROR-Agreementflag';
1281: else
1286: lv_has_errors := 'Y';
1287: end if;
1288:
1289: -- validate the Last name Different Flag
1290: hr_utility.trace('Checking Last Name Different flag');
1291:
1292: if p_last_name_diff = 'on' then
1293: l_last_name_diff_flag := 'Y';
1294: else
1301:
1302: p_errorcnt := l_num ;
1303: p_error := l_error;
1304:
1305: hr_utility.trace('Error string : '|| p_error);
1306: hr_utility.trace('Number of Errors : '|| to_char(p_errorcnt));
1307: hr_utility.trace('Validation Error - returning to Update');
1308:
1309: raise common_exception;
1302: p_errorcnt := l_num ;
1303: p_error := l_error;
1304:
1305: hr_utility.trace('Error string : '|| p_error);
1306: hr_utility.trace('Number of Errors : '|| to_char(p_errorcnt));
1307: hr_utility.trace('Validation Error - returning to Update');
1308:
1309: raise common_exception;
1310:
1303: p_error := l_error;
1304:
1305: hr_utility.trace('Error string : '|| p_error);
1306: hr_utility.trace('Number of Errors : '|| to_char(p_errorcnt));
1307: hr_utility.trace('Validation Error - returning to Update');
1308:
1309: raise common_exception;
1310:
1311: end if;
1332: p_source_name => lv_source_name,
1333: --p_update_method => lv_update_method,
1334: p_validate => TRUE );
1335:
1336: hr_utility.trace('The global state list is : '|| g_state_list);
1337: hr_utility.trace('The global state exempt list is : '|| g_state_exempt_list);
1338:
1339: -- Forming Proper Message Text
1340:
1333: --p_update_method => lv_update_method,
1334: p_validate => TRUE );
1335:
1336: hr_utility.trace('The global state list is : '|| g_state_list);
1337: hr_utility.trace('The global state exempt list is : '|| g_state_exempt_list);
1338:
1339: -- Forming Proper Message Text
1340:
1341: IF g_nonmatch_cntr > 0 then
1339: -- Forming Proper Message Text
1340:
1341: IF g_nonmatch_cntr > 0 then
1342:
1343: hr_utility.trace('The global Not Matching list is : '|| g_not_matching_state_list);
1344: hr_utility.trace('Final g_nonmatch_cntr := ' || g_nonmatch_cntr);
1345:
1346: ln_prev_comma_position := 0;
1347: FOR i IN 1..g_nonmatch_cntr
1340:
1341: IF g_nonmatch_cntr > 0 then
1342:
1343: hr_utility.trace('The global Not Matching list is : '|| g_not_matching_state_list);
1344: hr_utility.trace('Final g_nonmatch_cntr := ' || g_nonmatch_cntr);
1345:
1346: ln_prev_comma_position := 0;
1347: FOR i IN 1..g_nonmatch_cntr
1348: LOOP
1346: ln_prev_comma_position := 0;
1347: FOR i IN 1..g_nonmatch_cntr
1348: LOOP
1349: ln_comma_position := INSTR(g_not_matching_state_list, ',', 1, i);
1350: hr_utility.trace('i := ' || i);
1351: hr_utility.trace('ln_comma_position := ' || ln_comma_position);
1352:
1353: IF ln_comma_position = 0 THEN
1354: ln_comma_position := LENGTH(g_not_matching_state_list) + 1;
1347: FOR i IN 1..g_nonmatch_cntr
1348: LOOP
1349: ln_comma_position := INSTR(g_not_matching_state_list, ',', 1, i);
1350: hr_utility.trace('i := ' || i);
1351: hr_utility.trace('ln_comma_position := ' || ln_comma_position);
1352:
1353: IF ln_comma_position = 0 THEN
1354: ln_comma_position := LENGTH(g_not_matching_state_list) + 1;
1355: END IF;
1352:
1353: IF ln_comma_position = 0 THEN
1354: ln_comma_position := LENGTH(g_not_matching_state_list) + 1;
1355: END IF;
1356: hr_utility.trace('ln_comma_position := ' || ln_comma_position);
1357: hr_utility.trace('ln_prev_comma_position := ' || ln_prev_comma_position);
1358:
1359: lv_notmatch_state := LTRIM(RTRIM(SUBSTR(g_not_matching_state_list, (ln_prev_comma_position + 1), (ln_comma_position - ln_prev_comma_position - 1))));
1360: hr_utility.trace('lv_notmatch_state := ' || lv_notmatch_state);
1353: IF ln_comma_position = 0 THEN
1354: ln_comma_position := LENGTH(g_not_matching_state_list) + 1;
1355: END IF;
1356: hr_utility.trace('ln_comma_position := ' || ln_comma_position);
1357: hr_utility.trace('ln_prev_comma_position := ' || ln_prev_comma_position);
1358:
1359: lv_notmatch_state := LTRIM(RTRIM(SUBSTR(g_not_matching_state_list, (ln_prev_comma_position + 1), (ln_comma_position - ln_prev_comma_position - 1))));
1360: hr_utility.trace('lv_notmatch_state := ' || lv_notmatch_state);
1361:
1356: hr_utility.trace('ln_comma_position := ' || ln_comma_position);
1357: hr_utility.trace('ln_prev_comma_position := ' || ln_prev_comma_position);
1358:
1359: lv_notmatch_state := LTRIM(RTRIM(SUBSTR(g_not_matching_state_list, (ln_prev_comma_position + 1), (ln_comma_position - ln_prev_comma_position - 1))));
1360: hr_utility.trace('lv_notmatch_state := ' || lv_notmatch_state);
1361:
1362: IF INSTR(g_state_list, lv_notmatch_state) <> 0 THEN
1363: IF INSTR(g_state_list, lv_notmatch_state || ',') <> 0 THEN
1364: g_state_list := REPLACE(g_state_list, lv_notmatch_state || ',');
1372: ELSE
1373: g_state_exempt_list := REPLACE(g_state_exempt_list, lv_notmatch_state );
1374: END IF;
1375: END IF;
1376: hr_utility.trace('Success ' || i);
1377: ln_prev_comma_position := ln_comma_position;
1378: END LOOP;
1379: g_not_matching_state_list := hr_util_misc_web.return_msg_text('PAY_US_OTF_NOTMATCHING_STATES','PAY')
1380: || g_not_matching_state_list;
1403: else
1404: p_exempt_state_list := g_state_exempt_list;
1405: end if;
1406:
1407: hr_utility.trace('The global state list is : '|| g_state_list);
1408: hr_utility.trace('The global state exempt list is : '|| g_state_exempt_list);
1409:
1410:
1411: /* If we have come so far that means we have no errors. So insert data
1404: p_exempt_state_list := g_state_exempt_list;
1405: end if;
1406:
1407: hr_utility.trace('The global state list is : '|| g_state_list);
1408: hr_utility.trace('The global state exempt list is : '|| g_state_exempt_list);
1409:
1410:
1411: /* If we have come so far that means we have no errors. So insert data
1412: into hr_api_transactions, hr_api_transaction_steps and hr_api_transaction_values.
1418: l_transaction_id := hr_transaction_ss.get_transaction_id(p_itemtype, p_itemkey);
1419:
1420: if l_transaction_id is null then /* transaction does not exists */
1421:
1422: hr_utility.trace('l_transaction_id is null INSERTING');
1423:
1424: /*
1425: hr_transaction_api.create_transaction( p_creator_person_id => ln_person_id ,
1426: p_transaction_privilege => 'PRIVATE',
1483: avalue => l_transaction_id);
1484:
1485: else /* transaction exists */
1486:
1487: hr_utility.trace('l_transaction_id : '|| l_transaction_id);
1488: select transaction_step_id into l_transaction_step_id
1489: from HR_API_TRANSACTION_STEPS
1490: where transaction_id = l_transaction_id;
1491:
1495:
1496: -- Filing Status Meaning
1497: -- get the filing status meaning to store in the transaction_value table
1498:
1499: hr_utility.trace('After END IF');
1500:
1501: select fcl.meaning Meaning into transaction_value_fs
1502: from fnd_common_lookups fcl
1503: where fcl.lookup_type = 'US_FIT_FILING_STATUS'
1526: select fcl.meaning Meaning into transaction_value_fs
1527: from fnd_common_lookups fcl
1528: where fcl.lookup_type = 'US_FIT_FILING_STATUS'
1529: and fcl.lookup_code = p_original_fs ;
1530: hr_utility.trace('transaction_step_id = ' || l_transaction_step_id);
1531: hr_transaction_api.set_varchar2_value (
1532: p_transaction_step_id => l_transaction_step_id
1533: ,p_person_id => ln_person_id
1534: ,p_name => 'P_ORG_FILING_STATUS'
1603: ,p_person_id => ln_person_id
1604: ,p_name => 'P_LAST_NAME_DIFF'
1605: ,p_value => l_last_name_diff_flag ) ;
1606:
1607: hr_utility.trace('B4 Commit');
1608: commit;
1609:
1610: hr_utility.trace('Leaving ' || gv_package_name || '.validate_submission');
1611:
1606:
1607: hr_utility.trace('B4 Commit');
1608: commit;
1609:
1610: hr_utility.trace('Leaving ' || gv_package_name || '.validate_submission');
1611:
1612: EXCEPTION
1613:
1614: When common_exception then
1611:
1612: EXCEPTION
1613:
1614: When common_exception then
1615: hr_utility.trace('In exception common_exception');
1616: return;
1617:
1618: When no_data_found then
1619: hr_utility.trace('In exception no_data_found');
1615: hr_utility.trace('In exception common_exception');
1616: return;
1617:
1618: When no_data_found then
1619: hr_utility.trace('In exception no_data_found');
1620: return;
1621:
1622:
1623: WHEN OTHERS THEN
1620: return;
1621:
1622:
1623: WHEN OTHERS THEN
1624: hr_utility.trace(gv_package_name || '.validate_submission FATAL ERROR');
1625: hr_utility.trace(SQLERRM || ' ' || SQLCODE);
1626:
1627: l_error := l_error ||';'||'PAY-'||gv_package_name || '.validate_submission'||
1628: SQLERRM || ' ' ||SQLCODE||'-Dummy';
1621:
1622:
1623: WHEN OTHERS THEN
1624: hr_utility.trace(gv_package_name || '.validate_submission FATAL ERROR');
1625: hr_utility.trace(SQLERRM || ' ' || SQLCODE);
1626:
1627: l_error := l_error ||';'||'PAY-'||gv_package_name || '.validate_submission'||
1628: SQLERRM || ' ' ||SQLCODE||'-Dummy';
1629:
1666:
1667: common_exception EXCEPTION;
1668: BEGIN
1669:
1670: hr_utility.trace('Transaction Id is : '|| p_trans_id);
1671: hr_utility.trace('Transaction Step Id is : '|| p_step_id);
1672:
1673: if (p_trans_id is null and p_step_id is null) then
1674: p_out_values := 'Error: Please enter Transaction Id or Transaction Step Id';
1667: common_exception EXCEPTION;
1668: BEGIN
1669:
1670: hr_utility.trace('Transaction Id is : '|| p_trans_id);
1671: hr_utility.trace('Transaction Step Id is : '|| p_step_id);
1672:
1673: if (p_trans_id is null and p_step_id is null) then
1674: p_out_values := 'Error: Please enter Transaction Id or Transaction Step Id';
1675: raise common_exception;
1705:
1706: end loop;
1707: close c_trans_values;
1708:
1709: hr_utility.trace('The out value is : '|| p_out_values);
1710:
1711: exception when common_exception then
1712:
1713: hr_utility.trace('Error: Please enter Transaction Id or Transaction Step Id');
1709: hr_utility.trace('The out value is : '|| p_out_values);
1710:
1711: exception when common_exception then
1712:
1713: hr_utility.trace('Error: Please enter Transaction Id or Transaction Step Id');
1714: return;
1715:
1716:
1717: END;
2051: l_state_tax_rec c_state_tax_rows%rowtype;
2052:
2053: BEGIN
2054:
2055: hr_utility.trace('Entering ' || gv_package_name || '.update_tax_records');
2056:
2057: -- set a savepoint before we do anything
2058: SAVEPOINT update_tax_records;
2059:
2089:
2090: lv_fed_exemptions_count := 0;
2091: lv_state_exemptions_count := 0;
2092:
2093: hr_utility.trace('Update Method = ' || lv_update_method);
2094:
2095: lv_filing_status_code := p_filing_status_code;
2096:
2097: -- lock records
2094:
2095: lv_filing_status_code := p_filing_status_code;
2096:
2097: -- lock records
2098: hr_utility.trace(gv_package_name||'.update_tax_records - Locking Employee Tax Records');
2099: open c_fed_tax_rows;
2100:
2101: loop /* Federal cursor loop */
2102:
2121: + l_fed_tax_rec.futa_tax_exempt_count
2122: + l_fed_tax_rec.medicare_tax_exempt_count
2123: + l_fed_tax_rec.ss_tax_exempt_count ;
2124:
2125: hr_utility.trace(gv_package_name ||'.update_tax_records - BEFORE FED UPDATE');
2126:
2127: -- We insert using datetrack mode of UPDATE
2128: -- future dated records will cause an error
2129: -- if the old start date = sysdate, we perform a correction instead
2134: else
2135: lv_datetrack_mode := 'UPDATE';
2136: end if;
2137:
2138: hr_utility.trace('Updating Fed Record ID = ' || to_char(ln_fed_tax_rule_id));
2139:
2140: /*
2141: pay_federal_tax_rule_api.update_fed_tax_rule
2142: (p_emp_fed_tax_rule_id => ln_fed_tax_rule_id
2252: for the primary flag. */
2253:
2254: if l_fed_tax_rec.primary_flag = 'Y' then
2255:
2256: hr_utility.trace('Primary Flag is = ' || l_fed_tax_rec.primary_flag);
2257: pay_aud_ins.ins(
2258: p_effective_date => l_fed_tax_rec.cur_sysdate
2259: ,p_transaction_type => lv_trans_type --'ONLINE_TAX_FORMS'
2260: ,p_transaction_date => l_fed_tax_rec.cur_sysdate
2281: ,p_stat_trans_audit_id => ln_parent_audit_id
2282: ,p_object_version_number => l_fed_tax_rec.object_version_number
2283: );
2284:
2285: hr_utility.trace('Executed pay_aud_ins.ins ' );
2286: else /* l_primary_flag */
2287:
2288: /* reversing ln_dummy and ln_parent_audit_id */
2289:
2286: else /* l_primary_flag */
2287:
2288: /* reversing ln_dummy and ln_parent_audit_id */
2289:
2290: hr_utility.trace('Primary Flag is = ' || l_fed_tax_rec.primary_flag);
2291: pay_aud_ins.ins(
2292: p_effective_date => l_fed_tax_rec.cur_sysdate
2293: ,p_transaction_type => lv_trans_type --'ONLINE_TAX_FORMS'
2294: ,p_transaction_date => l_fed_tax_rec.cur_sysdate
2321: /* Commenting this for bug 2038691
2322: -- as a sanity check we make sure that the dates are right
2323: if (ld_start_date <> trunc(sysdate)) or
2324: (ld_end_date <> to_date('31/12/4712','DD/MM/YYYY')) then
2325: hr_utility.trace('ld_start_date is : '|| ld_start_date);
2326: hr_utility.trace('trunc(sysdate) is : '|| trunc(sysdate));
2327: hr_utility.trace('ld_end_date is : '|| ld_end_date);
2328: hr_utility.trace('Date sanity checking');
2329: raise e_date_error;
2322: -- as a sanity check we make sure that the dates are right
2323: if (ld_start_date <> trunc(sysdate)) or
2324: (ld_end_date <> to_date('31/12/4712','DD/MM/YYYY')) then
2325: hr_utility.trace('ld_start_date is : '|| ld_start_date);
2326: hr_utility.trace('trunc(sysdate) is : '|| trunc(sysdate));
2327: hr_utility.trace('ld_end_date is : '|| ld_end_date);
2328: hr_utility.trace('Date sanity checking');
2329: raise e_date_error;
2330: end if;
2323: if (ld_start_date <> trunc(sysdate)) or
2324: (ld_end_date <> to_date('31/12/4712','DD/MM/YYYY')) then
2325: hr_utility.trace('ld_start_date is : '|| ld_start_date);
2326: hr_utility.trace('trunc(sysdate) is : '|| trunc(sysdate));
2327: hr_utility.trace('ld_end_date is : '|| ld_end_date);
2328: hr_utility.trace('Date sanity checking');
2329: raise e_date_error;
2330: end if;
2331: Commenting this for bug 2038691*/
2324: (ld_end_date <> to_date('31/12/4712','DD/MM/YYYY')) then
2325: hr_utility.trace('ld_start_date is : '|| ld_start_date);
2326: hr_utility.trace('trunc(sysdate) is : '|| trunc(sysdate));
2327: hr_utility.trace('ld_end_date is : '|| ld_end_date);
2328: hr_utility.trace('Date sanity checking');
2329: raise e_date_error;
2330: end if;
2331: Commenting this for bug 2038691*/
2332:
2337: -- We will update state record only if the one of the following has changed.
2338:
2339: /* to update state tax Yes or No */
2340:
2341: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
2342: hr_utility.trace('p_org_filing_status_code = ' || p_org_filing_status_code);
2343:
2344: hr_utility.trace('p_allowances = ' || p_allowances);
2345: hr_utility.trace('p_org_allowances = ' || p_org_allowances);
2338:
2339: /* to update state tax Yes or No */
2340:
2341: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
2342: hr_utility.trace('p_org_filing_status_code = ' || p_org_filing_status_code);
2343:
2344: hr_utility.trace('p_allowances = ' || p_allowances);
2345: hr_utility.trace('p_org_allowances = ' || p_org_allowances);
2346:
2340:
2341: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
2342: hr_utility.trace('p_org_filing_status_code = ' || p_org_filing_status_code);
2343:
2344: hr_utility.trace('p_allowances = ' || p_allowances);
2345: hr_utility.trace('p_org_allowances = ' || p_org_allowances);
2346:
2347: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
2348: hr_utility.trace('p_org_exempt_status_code = ' || p_org_exempt_status_code);
2341: hr_utility.trace('p_filing_status_code = ' || p_filing_status_code);
2342: hr_utility.trace('p_org_filing_status_code = ' || p_org_filing_status_code);
2343:
2344: hr_utility.trace('p_allowances = ' || p_allowances);
2345: hr_utility.trace('p_org_allowances = ' || p_org_allowances);
2346:
2347: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
2348: hr_utility.trace('p_org_exempt_status_code = ' || p_org_exempt_status_code);
2349:
2343:
2344: hr_utility.trace('p_allowances = ' || p_allowances);
2345: hr_utility.trace('p_org_allowances = ' || p_org_allowances);
2346:
2347: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
2348: hr_utility.trace('p_org_exempt_status_code = ' || p_org_exempt_status_code);
2349:
2350: if ((p_filing_status_code <> p_org_filing_status_code ) OR
2351: (p_allowances <> p_org_allowances) OR
2344: hr_utility.trace('p_allowances = ' || p_allowances);
2345: hr_utility.trace('p_org_allowances = ' || p_org_allowances);
2346:
2347: hr_utility.trace('p_exempt_status_code = ' || p_exempt_status_code);
2348: hr_utility.trace('p_org_exempt_status_code = ' || p_org_exempt_status_code);
2349:
2350: if ((p_filing_status_code <> p_org_filing_status_code ) OR
2351: (p_allowances <> p_org_allowances) OR
2352: (p_exempt_status_code <> p_org_exempt_status_code) ) then
2350: if ((p_filing_status_code <> p_org_filing_status_code ) OR
2351: (p_allowances <> p_org_allowances) OR
2352: (p_exempt_status_code <> p_org_exempt_status_code) ) then
2353:
2354: hr_utility.trace('*** Updating State Record *** ' );
2355:
2356: if p_filing_status_code <> p_org_filing_status_code then
2357:
2358: lv_filing_status_changed := TRUE;
2392:
2393:
2394: exit when c_state_tax_rows%NOTFOUND;
2395:
2396: hr_utility.trace('Fetched state : '|| l_state_tax_rec.state_code );
2397: hr_utility.trace(' FS : '|| l_state_tax_rec.filing_status_code ||
2398: ' WA : '|| to_char(l_state_tax_rec.withholding_allowances) );
2399:
2400: -- count the no. of State Tax Exemptions
2393:
2394: exit when c_state_tax_rows%NOTFOUND;
2395:
2396: hr_utility.trace('Fetched state : '|| l_state_tax_rec.state_code );
2397: hr_utility.trace(' FS : '|| l_state_tax_rec.filing_status_code ||
2398: ' WA : '|| to_char(l_state_tax_rec.withholding_allowances) );
2399:
2400: -- count the no. of State Tax Exemptions
2401: lv_state_exemptions_count := l_state_tax_rec.sit_exempt_count
2402: + l_state_tax_rec.sui_exempt_count
2403: + l_state_tax_rec.sdi_exempt_count
2404: + l_state_tax_rec.wc_exempt_count;
2405:
2406: hr_utility.trace(gv_package_name||'.update_tax_records-BEFORE STATE UPDATE');
2407:
2408: if l_state_tax_rec.effective_start_date = l_state_tax_rec.cur_sysdate then
2409: lv_datetrack_mode := 'CORRECTION';
2410: else
2419: the state records also. Bug 1668926.
2420:
2421: Check fed = state FS and WA */
2422:
2423: hr_utility.trace('cu_state_fs_code = ' || l_state_tax_rec.filing_status_code);
2424: hr_utility.trace('cu_fed_fs_code = ' || l_fed_tax_rec.filing_status_code);
2425:
2426: hr_utility.trace('cu_state_wa = ' || l_state_tax_rec.withholding_allowances);
2427: hr_utility.trace('cu_fed_wa = ' || l_fed_tax_rec.withholding_allowances);
2420:
2421: Check fed = state FS and WA */
2422:
2423: hr_utility.trace('cu_state_fs_code = ' || l_state_tax_rec.filing_status_code);
2424: hr_utility.trace('cu_fed_fs_code = ' || l_fed_tax_rec.filing_status_code);
2425:
2426: hr_utility.trace('cu_state_wa = ' || l_state_tax_rec.withholding_allowances);
2427: hr_utility.trace('cu_fed_wa = ' || l_fed_tax_rec.withholding_allowances);
2428:
2422:
2423: hr_utility.trace('cu_state_fs_code = ' || l_state_tax_rec.filing_status_code);
2424: hr_utility.trace('cu_fed_fs_code = ' || l_fed_tax_rec.filing_status_code);
2425:
2426: hr_utility.trace('cu_state_wa = ' || l_state_tax_rec.withholding_allowances);
2427: hr_utility.trace('cu_fed_wa = ' || l_fed_tax_rec.withholding_allowances);
2428:
2429: hr_utility.trace('lv_fed_exemptions_count = ' || lv_fed_exemptions_count);
2430: hr_utility.trace('lv_state_exemptions_count = ' || lv_state_exemptions_count);
2423: hr_utility.trace('cu_state_fs_code = ' || l_state_tax_rec.filing_status_code);
2424: hr_utility.trace('cu_fed_fs_code = ' || l_fed_tax_rec.filing_status_code);
2425:
2426: hr_utility.trace('cu_state_wa = ' || l_state_tax_rec.withholding_allowances);
2427: hr_utility.trace('cu_fed_wa = ' || l_fed_tax_rec.withholding_allowances);
2428:
2429: hr_utility.trace('lv_fed_exemptions_count = ' || lv_fed_exemptions_count);
2430: hr_utility.trace('lv_state_exemptions_count = ' || lv_state_exemptions_count);
2431:
2425:
2426: hr_utility.trace('cu_state_wa = ' || l_state_tax_rec.withholding_allowances);
2427: hr_utility.trace('cu_fed_wa = ' || l_fed_tax_rec.withholding_allowances);
2428:
2429: hr_utility.trace('lv_fed_exemptions_count = ' || lv_fed_exemptions_count);
2430: hr_utility.trace('lv_state_exemptions_count = ' || lv_state_exemptions_count);
2431:
2432: lv_filing_status_code_o := p_filing_status_code;
2433:
2426: hr_utility.trace('cu_state_wa = ' || l_state_tax_rec.withholding_allowances);
2427: hr_utility.trace('cu_fed_wa = ' || l_fed_tax_rec.withholding_allowances);
2428:
2429: hr_utility.trace('lv_fed_exemptions_count = ' || lv_fed_exemptions_count);
2430: hr_utility.trace('lv_state_exemptions_count = ' || lv_state_exemptions_count);
2431:
2432: lv_filing_status_code_o := p_filing_status_code;
2433:
2434: /*if (l_state_tax_rec.filing_status_code = l_fed_tax_rec.filing_status_code and
2449: ,lv_state_exemptions_count
2450: ,lv_filing_status_code_o) THEN
2451:
2452: if p_validate then
2453: hr_utility.trace('B4 g_state_list = ' || g_state_list);
2454:
2455: if p_exempt_status_code = 'Y' and lv_exempt_changed = TRUE
2456: then
2457:
2459:
2460: end if;
2461:
2462: g_state_list := get_state_list(ln_person_id,l_primary_only);
2463: hr_utility.trace('g_state_list = ' || g_state_list);
2464: hr_utility.trace('g_state_exempt_list = ' || g_state_exempt_list);
2465:
2466: end if;
2467:
2460: end if;
2461:
2462: g_state_list := get_state_list(ln_person_id,l_primary_only);
2463: hr_utility.trace('g_state_list = ' || g_state_list);
2464: hr_utility.trace('g_state_exempt_list = ' || g_state_exempt_list);
2465:
2466: end if;
2467:
2468: /* allow update of state tax record(s).
2506: lv_state_fs_changed := TRUE;
2507:
2508: end if;
2509:
2510: hr_utility.trace('Updating State Record ID = ' || to_char(l_state_tax_rec.emp_state_tax_rule_id));
2511:
2512: /* Check the state exempt code here -- If the code Y then default the exempt code from federal
2513: else do not replace the exempt code */
2514:
2525: -- we cannot default
2526: l_state_exempt := NVL(l_state_tax_rec.sit_exempt, 'N');
2527: end if;
2528:
2529: hr_utility.trace('*** l_state_exempt = ' || l_state_exempt);
2530: hr_utility.trace('*** p_exempt_status_code = ' || p_exempt_status_code);
2531: hr_utility.trace('*** cu_sit_exempt = ' || l_state_tax_rec.sit_exempt);
2532:
2533:
2526: l_state_exempt := NVL(l_state_tax_rec.sit_exempt, 'N');
2527: end if;
2528:
2529: hr_utility.trace('*** l_state_exempt = ' || l_state_exempt);
2530: hr_utility.trace('*** p_exempt_status_code = ' || p_exempt_status_code);
2531: hr_utility.trace('*** cu_sit_exempt = ' || l_state_tax_rec.sit_exempt);
2532:
2533:
2534: --If lv_filing_status_changed or lv_allowance_changed then
2527: end if;
2528:
2529: hr_utility.trace('*** l_state_exempt = ' || l_state_exempt);
2530: hr_utility.trace('*** p_exempt_status_code = ' || p_exempt_status_code);
2531: hr_utility.trace('*** cu_sit_exempt = ' || l_state_tax_rec.sit_exempt);
2532:
2533:
2534: --If lv_filing_status_changed or lv_allowance_changed then
2535: /* If lv_state_fs_changed or lv_allowance_changed then
2549:
2550: If lv_state_fs_changed then
2551:
2552: lv_insert_flag := TRUE;
2553: hr_utility.trace('RLN lv_state_fs_changed TRUE' );
2554: elsif lv_exempt_changed and lv_state_exempt_code = 'Y' then
2555:
2556: lv_insert_flag := TRUE;
2557: hr_utility.trace('*RLN** lv_exempt_changed TRUE');
2553: hr_utility.trace('RLN lv_state_fs_changed TRUE' );
2554: elsif lv_exempt_changed and lv_state_exempt_code = 'Y' then
2555:
2556: lv_insert_flag := TRUE;
2557: hr_utility.trace('*RLN** lv_exempt_changed TRUE');
2558: elsif lv_allowance_changed then
2559: if (l_state_exempt <> p_exempt_status_code and p_allowances = 0) then
2560: lv_insert_flag := FALSE;
2561: else
2559: if (l_state_exempt <> p_exempt_status_code and p_allowances = 0) then
2560: lv_insert_flag := FALSE;
2561: else
2562: lv_insert_flag := TRUE;
2563: hr_utility.trace('*RLN** lv_allowance_changed TRUE');
2564: end if;
2565: end if;
2566:
2567:
2568: /* Modified for bug no 7005814 */
2569:
2570: if lv_insert_flag then /* mehul */
2571:
2572: hr_utility.trace('Update state info : '|| l_state_tax_rec.emp_state_tax_rule_id);
2573: /*
2574: pay_state_tax_rule_api.update_state_tax_rule
2575: (p_emp_state_tax_rule_id => l_state_tax_rec.emp_state_tax_rule_id
2576: ,p_withholding_allowances => p_allowances
2694: -- store it, otherwise there is nothing stored except the child
2695: -- record info
2696:
2697: lv_context := 'W4 State';
2698: hr_utility.trace('State Context is : ' || lv_context);
2699:
2700: -- insert a row in the transaction table
2701: pay_aud_ins.ins(
2702: p_effective_date => l_state_tax_rec.cur_sysdate
2725: ,p_stat_trans_audit_id => ln_dummy
2726: ,p_object_version_number => l_state_tax_rec.object_version_number
2727: );
2728:
2729: hr_utility.trace('State Context is : ' || lv_context ||' after insert ');
2730:
2731: end if; /* mehul */
2732: /* Commenting this for bug 2038691
2733: -- as a sanity check we make sure that the dates are right
2756: else
2757: commit;
2758: end if;
2759:
2760: hr_utility.trace('Leaving ' || gv_package_name || '.update_tax_records');
2761:
2762:
2763: EXCEPTION
2764: WHEN OTHERS THEN
2814: lv_trans_type VARCHAR2(50);
2815: lv_source_name VARCHAR2(50);
2816:
2817: begin
2818: hr_utility.trace('Entering package update_w4_info ');
2819: hr_utility.trace('p_transaction_step_id is : ' || p_transaction_step_id );
2820:
2821: open c_trans_step;
2822: fetch c_trans_step into l_transaction_id,l_person_id;
2815: lv_source_name VARCHAR2(50);
2816:
2817: begin
2818: hr_utility.trace('Entering package update_w4_info ');
2819: hr_utility.trace('p_transaction_step_id is : ' || p_transaction_step_id );
2820:
2821: open c_trans_step;
2822: fetch c_trans_step into l_transaction_id,l_person_id;
2823: close c_trans_step;
2863:
2864: end loop;
2865: close c_trans_value;
2866:
2867: hr_utility.trace(' Transaction Id is : '|| to_char(l_transaction_id));
2868: hr_utility.trace(' Person Id is : '|| to_char(l_person_id));
2869:
2870: hr_utility.trace(' Filing Status is : '|| l_filing_status_code);
2871: hr_utility.trace(' Allowances are : '|| to_char(l_allowances));
2864: end loop;
2865: close c_trans_value;
2866:
2867: hr_utility.trace(' Transaction Id is : '|| to_char(l_transaction_id));
2868: hr_utility.trace(' Person Id is : '|| to_char(l_person_id));
2869:
2870: hr_utility.trace(' Filing Status is : '|| l_filing_status_code);
2871: hr_utility.trace(' Allowances are : '|| to_char(l_allowances));
2872: hr_utility.trace(' Additional Tax is : '|| to_char(l_add_tax));
2866:
2867: hr_utility.trace(' Transaction Id is : '|| to_char(l_transaction_id));
2868: hr_utility.trace(' Person Id is : '|| to_char(l_person_id));
2869:
2870: hr_utility.trace(' Filing Status is : '|| l_filing_status_code);
2871: hr_utility.trace(' Allowances are : '|| to_char(l_allowances));
2872: hr_utility.trace(' Additional Tax is : '|| to_char(l_add_tax));
2873: hr_utility.trace(' Exempt Status is : '|| l_exempt);
2874:
2867: hr_utility.trace(' Transaction Id is : '|| to_char(l_transaction_id));
2868: hr_utility.trace(' Person Id is : '|| to_char(l_person_id));
2869:
2870: hr_utility.trace(' Filing Status is : '|| l_filing_status_code);
2871: hr_utility.trace(' Allowances are : '|| to_char(l_allowances));
2872: hr_utility.trace(' Additional Tax is : '|| to_char(l_add_tax));
2873: hr_utility.trace(' Exempt Status is : '|| l_exempt);
2874:
2875: lv_trans_type := 'ONLINE_TAX_FORMS';
2868: hr_utility.trace(' Person Id is : '|| to_char(l_person_id));
2869:
2870: hr_utility.trace(' Filing Status is : '|| l_filing_status_code);
2871: hr_utility.trace(' Allowances are : '|| to_char(l_allowances));
2872: hr_utility.trace(' Additional Tax is : '|| to_char(l_add_tax));
2873: hr_utility.trace(' Exempt Status is : '|| l_exempt);
2874:
2875: lv_trans_type := 'ONLINE_TAX_FORMS';
2876: lv_source_name := 'ONLINE W4 FORM';
2869:
2870: hr_utility.trace(' Filing Status is : '|| l_filing_status_code);
2871: hr_utility.trace(' Allowances are : '|| to_char(l_allowances));
2872: hr_utility.trace(' Additional Tax is : '|| to_char(l_add_tax));
2873: hr_utility.trace(' Exempt Status is : '|| l_exempt);
2874:
2875: lv_trans_type := 'ONLINE_TAX_FORMS';
2876: lv_source_name := 'ONLINE W4 FORM';
2877:
2888: p_transaction_type => lv_trans_type,
2889: p_source_name => lv_source_name,
2890: p_validate => p_validate);
2891:
2892: hr_utility.trace('Leaving package update_w4_info ');
2893: End;
2894:
2895: FUNCTION get_state_list(p_person_id IN per_people_f.person_id%TYPE,
2896: p_primary_flag IN varchar2 )
2936: l_state_count number(3) := 0 ;
2937: l_state_exempt_count number(3) := 0 ;
2938:
2939: Begin
2940: hr_utility.trace('Entering get_state_list');
2941:
2942: IF gv_exempt_change = 'Y' THEN
2943: lv_state_list := hr_util_misc_web.return_msg_text('PAY_US_OTF_ADDTAX_STATE_LIST','PAY');
2944: ELSE
2954: FETCH c_state_tax_rows INTO lv_state_name, lv_state_default_code,lv_state_exempt_code;
2955:
2956: exit when c_state_tax_rows%NOTFOUND; --Bug 14109584
2957:
2958: hr_utility.trace('state is --> '|| lv_state_name ||' Code is --> '|| lv_state_default_code||' Exempt Code is --> '|| lv_state_exempt_code);
2959: hr_utility.trace('looking at c_state_tax_rows');
2960:
2961: -- append the name to the state list to the message
2962: -- we do not append it if the code is 'Y QUIET'
2955:
2956: exit when c_state_tax_rows%NOTFOUND; --Bug 14109584
2957:
2958: hr_utility.trace('state is --> '|| lv_state_name ||' Code is --> '|| lv_state_default_code||' Exempt Code is --> '|| lv_state_exempt_code);
2959: hr_utility.trace('looking at c_state_tax_rows');
2960:
2961: -- append the name to the state list to the message
2962: -- we do not append it if the code is 'Y QUIET'
2963: -- instr(lv_state_list,lv_state_name) will ensure that the state
2998:
2999: g_state_exempt_list := null;
3000: end if;
3001:
3002: hr_utility.trace('Leaving get_state_list');
3003: return lv_state_list;
3004:
3005: End;
3006:
3042:
3043: Begin
3044: lv_proc := gv_package_name || '.get_org_context';
3045:
3046: hr_utility.trace('Entering '||lv_proc);
3047:
3048: OPEN c_person_info;
3049: FETCH c_person_info INTO lr_person_info_rec;
3050:
3054: ELSE
3055: close c_person_info;
3056: end if;
3057:
3058: hr_utility.trace('lr_person_info_rec.organization_id = '||lr_person_info_rec.organization_id);
3059: hr_utility.trace('lr_person_info_rec.business_group_id = '||lr_person_info_rec.business_group_id);
3060:
3061: if p_level = 'BG' then
3062: OPEN c_org_context(lr_person_info_rec.business_group_id);
3055: close c_person_info;
3056: end if;
3057:
3058: hr_utility.trace('lr_person_info_rec.organization_id = '||lr_person_info_rec.organization_id);
3059: hr_utility.trace('lr_person_info_rec.business_group_id = '||lr_person_info_rec.business_group_id);
3060:
3061: if p_level = 'BG' then
3062: OPEN c_org_context(lr_person_info_rec.business_group_id);
3063: FETCH c_org_context INTO lr_org_info_rec;
3077: end if;
3078: CLOSE c_org_context;
3079: end if;
3080:
3081: hr_utility.trace('lr_org_info_rec.org_information2 = '||lr_org_info_rec.org_information2);
3082: hr_utility.trace('lv_result = '||lv_result);
3083:
3084: hr_utility.trace('Leaving '||lv_proc);
3085: return lv_result;
3078: CLOSE c_org_context;
3079: end if;
3080:
3081: hr_utility.trace('lr_org_info_rec.org_information2 = '||lr_org_info_rec.org_information2);
3082: hr_utility.trace('lv_result = '||lv_result);
3083:
3084: hr_utility.trace('Leaving '||lv_proc);
3085: return lv_result;
3086:
3080:
3081: hr_utility.trace('lr_org_info_rec.org_information2 = '||lr_org_info_rec.org_information2);
3082: hr_utility.trace('lv_result = '||lv_result);
3083:
3084: hr_utility.trace('Leaving '||lv_proc);
3085: return lv_result;
3086:
3087: exception
3088: when no_data_found then
3085: return lv_result;
3086:
3087: exception
3088: when no_data_found then
3089: hr_utility.trace('Person Data not Found');
3090: hr_utility.trace('In Exception '||lv_proc);
3091:
3092: End get_org_context;
3093:
3086:
3087: exception
3088: when no_data_found then
3089: hr_utility.trace('Person Data not Found');
3090: hr_utility.trace('In Exception '||lv_proc);
3091:
3092: End get_org_context;
3093:
3094: /* Uncomment following two lines for debug */
3092: End get_org_context;
3093:
3094: /* Uncomment following two lines for debug */
3095: -- begin
3096: -- hr_utility.trace_on(null,'ORACLETEST');
3097:
3098: END pay_us_web_w4;