[Home] [Help]
7540: WHERE assignment_id = l_assignment_id
7541: AND payroll_id = l_payroll_id
7542: AND change_flag = 'U'
7543: AND EXISTS (SELECT 1
7544: FROM psp_enc_summary_lines pesl
7545: WHERE pesl.assignment_id = l_assignment_id
7546: AND pesl.payroll_id = l_payroll_id
7547: AND status_code = 'A'
7548: AND pesl.enc_summary_line_id = pelh.enc_summary_line_id);
7557: WHERE assignment_id = l_assignment_id
7558: AND payroll_id = l_payroll_id
7559: AND change_flag = 'U'
7560: AND EXISTS (SELECT 1
7561: FROM psp_enc_summary_lines pesl
7562: WHERE pesl.assignment_id = l_assignment_id
7563: AND pesl.payroll_id = l_payroll_id
7564: AND status_code = 'A'
7565: AND pesl.enc_summary_line_id = pelh.enc_summary_line_id
7573: WHERE assignment_id = l_assignment_id
7574: AND payroll_id = l_payroll_id
7575: AND change_flag = 'N'
7576: AND EXISTS (SELECT 1
7577: FROM psp_enc_summary_lines pesl
7578: WHERE pesl.assignment_id = l_assignment_id
7579: AND pesl.payroll_id = l_payroll_id
7580: AND status_code = 'A'
7581: AND pesl.enc_summary_line_id = pelh.enc_summary_line_id
7628: WHERE pepa.payroll_action_id = p_payroll_action_id
7629: AND pepa.assignment_id = t_assignments.asg_array(recno)
7630: AND pepa.payroll_id = t_assignments.payroll_array(recno)
7631: AND NOT EXISTS (SELECT 1
7632: FROM psp_enc_summary_lines pesl
7633: WHERE pesl.payroll_action_id = p_payroll_action_id
7634: AND pesl.assignment_id = t_assignments.asg_array(recno)
7635: AND pesl.payroll_id = t_assignments.payroll_array(recno));
7636:
7640: WHERE pepa.payroll_action_id = p_payroll_action_id
7641: AND pepa.assignment_id = t_assignments.asg_array(recno)
7642: AND pepa.payroll_id = t_assignments.payroll_array(recno)
7643: AND EXISTS (SELECT 1
7644: FROM psp_enc_summary_lines pesl
7645: WHERE pesl.payroll_action_id = p_payroll_action_id
7646: AND pesl.assignment_id = t_assignments.asg_array(recno)
7647: AND pesl.payroll_id = t_assignments.payroll_array(recno));
7648:
7652: WHERE pepa.payroll_action_id = p_payroll_action_id
7653: AND pepa.assignment_id = t_assignments.asg_array(recno)
7654: AND pepa.payroll_id = t_assignments.payroll_array(recno)
7655: AND EXISTS (SELECT 1
7656: FROM psp_enc_summary_lines pesl
7657: WHERE pesl.payroll_action_id = p_payroll_action_id
7658: AND pesl.superceded_line_id IS NOT NULL
7659: AND pesl.assignment_id = t_assignments.asg_array(recno)
7660: AND pesl.payroll_id = t_assignments.payroll_array(recno));
7767: AND message_level = 'E';
7768:
7769: CURSOR st_count_cur Is
7770: SELECT COUNT(1)
7771: FROM psp_enc_summary_lines
7772: WHERE payroll_action_id = p_payroll_action_id
7773: AND status_code = 'N';
7774: BEGIN
7775: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering CEL_DEINIT payroll_action_id: ' || p_payroll_action_id);
7820: UPDATE psp_enc_process_assignments pepa
7821: SET assignment_status = 'B'
7822: WHERE payroll_action_id = p_payroll_action_id
7823: AND NOT EXISTS (SELECT 1
7824: FROM psp_enc_summary_lines pesl
7825: WHERE pesl.payroll_action_id = p_payroll_action_id
7826: AND pesl.assignment_id = pepa.assignment_id
7827: AND pesl.payroll_id = pepa.payroll_id
7828: AND pesl.status_code = 'N');
7830: UPDATE psp_enc_process_assignments pepa
7831: SET assignment_status = 'S'
7832: WHERE payroll_action_id = p_payroll_action_id
7833: AND EXISTS (SELECT 1
7834: FROM psp_enc_summary_lines pesl
7835: WHERE pesl.payroll_action_id = p_payroll_action_id
7836: AND pesl.assignment_id = pepa.assignment_id
7837: AND pesl.payroll_id = pepa.payroll_id
7838: AND pesl.status_code = 'N'
7841: UPDATE psp_enc_process_assignments pepa
7842: SET assignment_status = 'L'
7843: WHERE payroll_action_id = p_payroll_action_id
7844: AND EXISTS (SELECT 1
7845: FROM psp_enc_summary_lines pesl
7846: WHERE pesl.payroll_action_id = p_payroll_action_id
7847: AND pesl.assignment_id = pepa.assignment_id
7848: AND pesl.payroll_id = pepa.payroll_id
7849: AND pesl.status_code = 'N'
7850: AND pesl.superceded_line_id IS NOT NULL);
7851:
7852: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_CONTROLS');
7853: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_LINES');
7854: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
7855: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed Gather Statistics');
7856:
7857: DELETE FROM psp_enc_controls pec
7858: WHERE pec.payroll_action_id = p_payroll_action_id
7911: SET process_status = 'B',
7912: process_phase = 'no_summarize_transfer'
7913: WHERE payroll_action_id = p_payroll_action_id
7914: AND NOT EXISTS (SELECT 1
7915: FROM psp_enc_summary_lines pesl
7916: WHERE pesl.payroll_action_id = p_payroll_action_id
7917: AND pesl.status_code = 'N');
7918:
7919: UPDATE psp_enc_processes
7919: UPDATE psp_enc_processes
7920: SET process_phase = 'summarize_transfer'
7921: WHERE payroll_action_id = p_payroll_action_id
7922: AND EXISTS (SELECT 1
7923: FROM psp_enc_summary_lines pesl
7924: WHERE pesl.payroll_action_id = p_payroll_action_id
7925: AND pesl.status_code = 'N'
7926: AND pesl.superceded_line_id IS NULL);
7927:
7928: UPDATE psp_enc_processes
7929: SET process_phase = 'liquidate'
7930: WHERE payroll_action_id = p_payroll_action_id
7931: AND EXISTS (SELECT 1
7932: FROM psp_enc_summary_lines pesl
7933: WHERE pesl.payroll_action_id = p_payroll_action_id
7934: AND pesl.status_code = 'N'
7935: AND pesl.superceded_line_id is NOT NULL);
7936: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase in psp_enc_processes');
8484: BEGIN
8485: l_last_updated_by := fnd_global.user_id;
8486: l_last_update_login := fnd_global.login_id;
8487:
8488: INSERT INTO psp_enc_summary_lines
8489: (enc_summary_line_id, business_group_id, set_of_books_id,
8490: enc_control_id, time_period_id, person_id,
8491: assignment_id, effective_date, gl_code_combination_id,
8492: project_id, task_id, award_id,
8499: attribute9, attribute10, payroll_action_id,
8500: proposed_termination_date, last_update_date, last_updated_by,
8501: last_update_login, created_by, creation_date,
8502: update_flag, org_id)
8503: SELECT psp_enc_summary_lines_s.NEXTVAL,
8504: p_business_group_id,
8505: p_set_of_books_id,
8506: pesl.enc_control_id,
8507: pesl.time_period_id,
8539: l_last_updated_by,
8540: SYSDATE,
8541: DECODE(SIGN(TRUNC(effective_date)-TRUNC(p_enc_begin_date)), 1, 'U', 'L'),
8542: pesl.org_id
8543: FROM psp_enc_summary_lines pesl
8544: WHERE pesl.assignment_id = p_assignment_id
8545: AND pesl.payroll_id = p_payroll_id
8546: AND pesl.status_code = 'A'
8547: AND pesl.enc_summary_line_id IN (SELECT pelh.enc_summary_line_id
8681: CLOSE sum_lines_cur;
8682:
8683: FOR recno IN 1..t_sum_lines.enc_control_id.COUNT
8684: LOOP
8685: SELECT psp_enc_summary_lines_s.NEXTVAL INTO t_sum_lines.enc_summary_line_id(recno) FROM DUAL;
8686: END LOOP;
8687:
8688: FORALL recno IN 1..t_sum_lines.enc_control_id.COUNT
8689: INSERT INTO psp_enc_summary_lines
8685: SELECT psp_enc_summary_lines_s.NEXTVAL INTO t_sum_lines.enc_summary_line_id(recno) FROM DUAL;
8686: END LOOP;
8687:
8688: FORALL recno IN 1..t_sum_lines.enc_control_id.COUNT
8689: INSERT INTO psp_enc_summary_lines
8690: (enc_summary_line_id, business_group_id, enc_control_id,
8691: time_period_id, person_id, assignment_id,
8692: effective_date, set_of_books_id, gl_code_combination_id,
8693: project_id, task_id, award_id,
8902: FROM psp_enc_processes pep,
8903: fnd_concurrent_requests fcr,
8904: fnd_concurrent_programs_tl fcpt
8905: WHERE EXISTS (SELECT 1
8906: FROM psp_enc_summary_lines pesl
8907: WHERE pesl.payroll_action_id = pep.payroll_action_id
8908: AND pesl.payroll_action_id = l_payroll_action_id)
8909: AND fcr.request_id = pep.request_id
8910: AND fcpt.concurrent_program_id = fcr.concurrent_program_id
8914: CURSOR in_process_term_cur (p_person_id IN NUMBER) IS
8915: SELECT DISTINCT assignment_id,
8916: payroll_id,
8917: payroll_action_id
8918: FROM psp_enc_summary_lines pesl
8919: WHERE pesl.person_id = p_person_id
8920: AND pesl.status_code = 'N';
8921:
8922: CURSOR in_process_asg_cur IS
9092: FROM psp_enc_changed_assignments peca
9093: WHERE peca.assignment_id = pa.assignment_id
9094: AND peca.payroll_id = p_payroll_id)
9095: AND NOT EXISTS (SELECT 1
9096: FROM psp_enc_summary_lines pesl
9097: WHERE pesl.assignment_id = pa.assignment_id
9098: AND pesl.status_code IN ('A', 'N')
9099: -- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
9100: AND pesl.effective_date > (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl.assignment_id,pesl.business_group_id,pesl.payroll_id) --13566809
9131: FROM psp_enc_changed_assignments peca
9132: WHERE peca.assignment_id = pa.assignment_id
9133: AND peca.payroll_id = p_payroll_id)
9134: AND NOT EXISTS (SELECT 1
9135: FROM psp_enc_summary_lines pesl
9136: WHERE pesl.assignment_id = pa.assignment_id
9137: AND pesl.status_code IN ('A', 'N')
9138: AND pesl.effective_date > l_prev_enc_end_date
9139: AND pesl.payroll_id = p_payroll_id);
9162: AND pepa.set_of_books_id = pep.set_of_books_id
9163: AND pep.payroll_id = p_payroll_id
9164: AND pepa.assignment_id = pa.assignment_id)
9165: AND EXISTS (SELECT 1
9166: FROM psp_enc_summary_lines pesl
9167: WHERE pesl.assignment_id = pa.assignment_id
9168: AND pesl.status_code = 'A'
9169: AND pesl.payroll_id = p_payroll_id)
9170: AND NOT EXISTS (SELECT 1
9167: WHERE pesl.assignment_id = pa.assignment_id
9168: AND pesl.status_code = 'A'
9169: AND pesl.payroll_id = p_payroll_id)
9170: AND NOT EXISTS (SELECT 1
9171: FROM psp_enc_summary_lines pesl
9172: WHERE pesl.assignment_id = pa.assignment_id
9173: AND pesl.status_code = 'N'
9174: AND pesl.payroll_id = p_payroll_id);
9175:
9202: FROM psp_enc_changed_assignments peca
9203: WHERE peca.assignment_id = pepa.assignment_id
9204: AND peca.payroll_id = p_payroll_id)
9205: AND NOT EXISTS (SELECT 1
9206: FROM psp_enc_summary_lines pesl
9207: WHERE pesl.assignment_id = pa.assignment_id
9208: AND pesl.payroll_id = pa.payroll_id -- Added for bug 9862281
9209: AND pesl.status_code IN ('A', 'N')
9210: -- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
9235: FROM psp_enc_changed_assignments peca
9236: WHERE peca.assignment_id = pepa.assignment_id
9237: AND peca.payroll_id = p_payroll_id)
9238: AND NOT EXISTS (SELECT 1
9239: FROM psp_enc_summary_lines pesl
9240: WHERE pesl.assignment_id = pa.assignment_id
9241: AND pesl.status_code IN ('A', 'N')
9242: AND pesl.effective_date > l_prev_enc_end_date);
9243:
9265: AND pepa.set_of_books_id = pep.set_of_books_id
9266: AND pep.payroll_id = p_payroll_id
9267: AND pepa.assignment_id = pa.assignment_id)
9268: AND EXISTS (SELECT 1
9269: FROM psp_enc_summary_lines pesl
9270: WHERE pesl.assignment_id = pa.assignment_id
9271: AND pesl.status_code = 'A'
9272: AND pesl.payroll_id = p_payroll_id)
9273: AND NOT EXISTS (SELECT 1
9270: WHERE pesl.assignment_id = pa.assignment_id
9271: AND pesl.status_code = 'A'
9272: AND pesl.payroll_id = p_payroll_id)
9273: AND NOT EXISTS (SELECT 1
9274: FROM psp_enc_summary_lines pesl
9275: WHERE pesl.assignment_id = pa.assignment_id
9276: AND pesl.status_code = 'N'
9277: AND pesl.payroll_id = p_payroll_id);
9278: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '40 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9280:
9281: INSERT INTO psp_enc_changed_assignments
9282: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9283: SELECT DISTINCT l_request_id, p_payroll_id, assignment_id, 'LQ', p_payroll_action_id
9284: FROM psp_enc_summary_lines pesl
9285: WHERE payroll_id = p_payroll_id
9286: AND business_group_id = l_business_group_id
9287: AND status_code = 'A'
9288: -- AND effective_date <= l_enc_begin_date -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
9293: AND NOT EXISTS (SELECT 1
9294: FROM psp_enc_changed_assignments peca
9295: WHERE peca.assignment_id = pesl.assignment_id
9296: AND peca.payroll_id = p_payroll_id)
9297: AND NOT EXISTS (SELECT /*+INDEX(pesl2 PSP_ENC_SUMMARY_LINES_N6)*/ 1
9298: FROM psp_enc_summary_lines pesl2
9299: WHERE pesl2.assignment_id = pesl.assignment_id
9300: AND pesl2.status_code = 'N'
9301: -- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
9294: FROM psp_enc_changed_assignments peca
9295: WHERE peca.assignment_id = pesl.assignment_id
9296: AND peca.payroll_id = p_payroll_id)
9297: AND NOT EXISTS (SELECT /*+INDEX(pesl2 PSP_ENC_SUMMARY_LINES_N6)*/ 1
9298: FROM psp_enc_summary_lines pesl2
9299: WHERE pesl2.assignment_id = pesl.assignment_id
9300: AND pesl2.status_code = 'N'
9301: -- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
9302: AND pesl2.effective_date <= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl2.assignment_id,pesl2.business_group_id,pesl2.payroll_id) --13566809
9338: FROM psp_enc_changed_assignments peca
9339: WHERE peca.assignment_id = pa.assignment_id
9340: AND peca.payroll_id = p_payroll_id)
9341: AND NOT EXISTS (SELECT 1
9342: FROM psp_enc_summary_lines pesl
9343: WHERE pesl.assignment_id = pa.assignment_id
9344: AND pesl.status_code IN ('A', 'N')
9345: -- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
9346: AND pesl.effective_date > (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl.assignment_id,pesl.business_group_id,pesl.payroll_id) --13566809
9376: FROM psp_enc_changed_assignments peca
9377: WHERE peca.assignment_id = pa.assignment_id
9378: AND peca.payroll_id = p_payroll_id)
9379: AND NOT EXISTS (SELECT 1
9380: FROM psp_enc_summary_lines pesl
9381: WHERE pesl.assignment_id = pa.assignment_id
9382: AND pesl.status_code IN ('A', 'N')
9383: AND pesl.effective_date > l_prev_enc_end_date
9384: AND pesl.payroll_id = p_payroll_id);
9407: AND pepa.set_of_books_id = pep.set_of_books_id
9408: AND pep.payroll_id = p_payroll_id
9409: AND pepa.assignment_id = pa.assignment_id)
9410: AND EXISTS (SELECT 1
9411: FROM psp_enc_summary_lines pesl
9412: WHERE pesl.assignment_id = pa.assignment_id
9413: AND pesl.status_code = 'A'
9414: AND pesl.payroll_id = p_payroll_id)
9415: AND NOT EXISTS (SELECT 1
9412: WHERE pesl.assignment_id = pa.assignment_id
9413: AND pesl.status_code = 'A'
9414: AND pesl.payroll_id = p_payroll_id)
9415: AND NOT EXISTS (SELECT 1
9416: FROM psp_enc_summary_lines pesl
9417: WHERE pesl.assignment_id = pa.assignment_id
9418: AND pesl.status_code = 'N'
9419: AND pesl.payroll_id = p_payroll_id);
9420:
9447: FROM psp_enc_changed_assignments peca
9448: WHERE peca.assignment_id = pepa.assignment_id
9449: AND peca.payroll_id = p_payroll_id)
9450: AND NOT EXISTS (SELECT 1
9451: FROM psp_enc_summary_lines pesl
9452: WHERE pesl.assignment_id = pa.assignment_id
9453: AND pesl.payroll_id = pa.payroll_id -- Added for bug 9862281
9454: AND pesl.status_code IN ('A', 'N')
9455: -- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
9496: FROM psp_enc_changed_assignments peca
9497: WHERE peca.assignment_id = pepa.assignment_id
9498: AND peca.payroll_id = p_payroll_id)
9499: AND NOT EXISTS (SELECT 1
9500: FROM psp_enc_summary_lines pesl
9501: WHERE pesl.assignment_id = pa.assignment_id
9502: AND pesl.status_code IN ('A', 'N')
9503: AND pesl.effective_date > l_prev_enc_end_date);
9504:
9526: AND pepa.set_of_books_id = pep.set_of_books_id
9527: AND pep.payroll_id = p_payroll_id
9528: AND pepa.assignment_id = pa.assignment_id)
9529: AND EXISTS (SELECT 1
9530: FROM psp_enc_summary_lines pesl
9531: WHERE pesl.assignment_id = pa.assignment_id
9532: AND pesl.status_code = 'A'
9533: AND pesl.payroll_id = p_payroll_id)
9534: AND NOT EXISTS (SELECT 1
9531: WHERE pesl.assignment_id = pa.assignment_id
9532: AND pesl.status_code = 'A'
9533: AND pesl.payroll_id = p_payroll_id)
9534: AND NOT EXISTS (SELECT 1
9535: FROM psp_enc_summary_lines pesl
9536: WHERE pesl.assignment_id = pa.assignment_id
9537: AND pesl.status_code = 'N'
9538: AND pesl.payroll_id = p_payroll_id);
9539: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || '40 Inserted ' || SQL%ROWCOUNT || ' assignments into psp_enc_changed_assignments');
9541:
9542: INSERT INTO psp_enc_changed_assignments
9543: (request_id, payroll_id, assignment_id, change_type, payroll_action_id)
9544: SELECT DISTINCT l_request_id, p_payroll_id, assignment_id, 'LQ', p_payroll_action_id
9545: FROM psp_enc_summary_lines pesl
9546: WHERE payroll_id = p_payroll_id
9547: AND business_group_id = l_business_group_id
9548: AND status_code = 'A'
9549: -- AND effective_date <= l_enc_begin_date -- Commenting this in the fix 12769510 as it is found to be commented in 11i code
9554: AND NOT EXISTS (SELECT 1
9555: FROM psp_enc_changed_assignments peca
9556: WHERE peca.assignment_id = pesl.assignment_id
9557: AND peca.payroll_id = p_payroll_id)
9558: AND NOT EXISTS (SELECT /*+INDEX(pesl2 PSP_ENC_SUMMARY_LINES_N6)*/ 1
9559: FROM psp_enc_summary_lines pesl2
9560: WHERE pesl2.assignment_id = pesl.assignment_id
9561: AND pesl2.status_code = 'N'
9562: -- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
9555: FROM psp_enc_changed_assignments peca
9556: WHERE peca.assignment_id = pesl.assignment_id
9557: AND peca.payroll_id = p_payroll_id)
9558: AND NOT EXISTS (SELECT /*+INDEX(pesl2 PSP_ENC_SUMMARY_LINES_N6)*/ 1
9559: FROM psp_enc_summary_lines pesl2
9560: WHERE pesl2.assignment_id = pesl.assignment_id
9561: AND pesl2.status_code = 'N'
9562: -- Bug 5642002: Replaced last Payroll Process date with last assignment Process date
9563: AND pesl2.effective_date <= (NVL((get_asg_pay_end_date(g_payroll_mode,p_payroll_action_id,pesl2.assignment_id,pesl2.business_group_id,pesl2.payroll_id)--13566809
9710: pesl.payroll_id,
9711: 'TR',
9712: p_payroll_action_id,
9713: l_termination_date1
9714: FROM psp_enc_summary_lines pesl
9715: WHERE pesl.person_id = l_person_id1
9716: AND pesl.effective_date >= l_termination_date1
9717: AND ( gl_code_combination_id IS NOT NULL
9718: OR award_id IS NOT NULL)
9717: AND ( gl_code_combination_id IS NOT NULL
9718: OR award_id IS NOT NULL)
9719: AND pesl.status_code = 'A'
9720: AND NOT EXISTS (SELECT 1
9721: FROM psp_enc_summary_lines pesl2
9722: WHERE pesl2.person_id = l_person_id1
9723: AND pesl2.status_code = 'N');
9724: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
9725: IF (SQL%ROWCOUNT = 0) THEN
9778: pesl.payroll_id,
9779: 'TR',
9780: p_payroll_action_id,
9781: l_termination_date2
9782: FROM psp_enc_summary_lines pesl
9783: WHERE pesl.person_id = l_person_id2
9784: AND pesl.effective_date >= l_termination_date2
9785: AND ( gl_code_combination_id IS NOT NULL
9786: OR award_id IS NOT NULL)
9785: AND ( gl_code_combination_id IS NOT NULL
9786: OR award_id IS NOT NULL)
9787: AND pesl.status_code = 'A'
9788: AND NOT EXISTS (SELECT 1
9789: FROM psp_enc_summary_lines pesl2
9790: WHERE pesl2.person_id = l_person_id2
9791: AND pesl2.status_code = 'N');
9792: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
9793: IF (SQL%ROWCOUNT = 0) THEN
9847: pesl.payroll_id,
9848: 'TR',
9849: p_payroll_action_id,
9850: l_termination_date3
9851: FROM psp_enc_summary_lines pesl
9852: WHERE pesl.person_id = l_person_id3
9853: AND pesl.effective_date >= l_termination_date3
9854: AND ( gl_code_combination_id IS NOT NULL
9855: OR award_id IS NOT NULL)
9854: AND ( gl_code_combination_id IS NOT NULL
9855: OR award_id IS NOT NULL)
9856: AND pesl.status_code = 'A'
9857: AND NOT EXISTS (SELECT 1
9858: FROM psp_enc_summary_lines pesl2
9859: WHERE pesl2.person_id = l_person_id3
9860: AND pesl2.status_code = 'N');
9861: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
9862: IF (SQL%ROWCOUNT = 0) THEN
9916: pesl.payroll_id,
9917: 'TR',
9918: p_payroll_action_id,
9919: l_termination_date4
9920: FROM psp_enc_summary_lines pesl
9921: WHERE pesl.person_id = l_person_id4
9922: AND pesl.effective_date >= l_termination_date4
9923: AND ( gl_code_combination_id IS NOT NULL
9924: OR award_id IS NOT NULL)
9923: AND ( gl_code_combination_id IS NOT NULL
9924: OR award_id IS NOT NULL)
9925: AND pesl.status_code = 'A'
9926: AND NOT EXISTS (SELECT 1
9927: FROM psp_enc_summary_lines pesl2
9928: WHERE pesl2.person_id = l_person_id4
9929: AND pesl2.status_code = 'N');
9930: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
9931: IF (SQL%ROWCOUNT = 0) THEN
9985: pesl.payroll_id,
9986: 'TR',
9987: p_payroll_action_id,
9988: l_termination_date5
9989: FROM psp_enc_summary_lines pesl
9990: WHERE pesl.person_id = l_person_id5
9991: AND pesl.effective_date >= l_termination_date5
9992: AND ( gl_code_combination_id IS NOT NULL
9993: OR award_id IS NOT NULL)
9992: AND ( gl_code_combination_id IS NOT NULL
9993: OR award_id IS NOT NULL)
9994: AND pesl.status_code = 'A'
9995: AND NOT EXISTS (SELECT 1
9996: FROM psp_enc_summary_lines pesl2
9997: WHERE pesl2.person_id = l_person_id5
9998: AND pesl2.status_code = 'N');
9999: hr_utility.trace(fnd_date.date_to_canonical(SYSDATE) || ' Inserted termination assignments into psp_enc_changed_assignments ' || SQL%ROWCOUNT);
10000: IF (SQL%ROWCOUNT = 0) THEN
10105: OR pepa.assignment_id = p_assignment_id);
10106:
10107: CURSOR superceded_line_cur (p_assignment_id IN NUMBER) IS
10108: SELECT superceded_line_id
10109: FROM psp_enc_summary_lines pesl
10110: WHERE pesl.payroll_action_id = p_payroll_action_id
10111: AND pesl.superceded_line_id IS NOT NULL
10112: AND assignment_id = p_assignment_id;
10113:
10126: SET change_flag = 'N'
10127: WHERE enc_summary_line_id = t_superceded_lines.enc_summary_line_id(recno);
10128: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted respective superceded lines in psp_enc_lines_history');
10129:
10130: DELETE psp_enc_summary_lines
10131: WHERE payroll_action_id = p_payroll_action_id;
10132: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
10133:
10134: DELETE psp_enc_lines
10128: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted respective superceded lines in psp_enc_lines_history');
10129:
10130: DELETE psp_enc_summary_lines
10131: WHERE payroll_action_id = p_payroll_action_id;
10132: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
10133:
10134: DELETE psp_enc_lines
10135: WHERE payroll_action_id = p_payroll_action_id;
10136: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_lines');
10244: SET change_flag = 'N'
10245: WHERE enc_summary_line_id = t_superceded_lines.enc_summary_line_id(recno);
10246: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Reverted respective superceded lines in psp_enc_lines_history');
10247:
10248: DELETE psp_enc_summary_lines
10249: WHERE payroll_action_id = p_payroll_action_id
10250: AND assignment_id = l_assignments(recno);
10251: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
10252:
10247:
10248: DELETE psp_enc_summary_lines
10249: WHERE payroll_action_id = p_payroll_action_id
10250: AND assignment_id = l_assignments(recno);
10251: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted respective lines in psp_enc_summary_lines');
10252:
10253: DELETE psp_enc_lines
10254: WHERE payroll_action_id = p_payroll_action_id
10255: AND assignment_id = l_assignments(recno);
10301: SET process_status = 'B',
10302: process_phase = 'rollback'
10303: WHERE payroll_action_id = p_payroll_action_id
10304: AND NOT EXISTS (SELECT 1
10305: FROM psp_enc_summary_lines
10306: WHERE payroll_action_id = p_payroll_action_id);
10307: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process status to ''Rollback'' in psp_enc_processes');
10308: END IF;
10309: