[Home] [Help]
309: p_return_status OUT NOCOPY VARCHAR2) IS
310:
311: CURSOR payroll_control_cur2 IS
312: SELECT payroll_control_id
313: FROM psp_payroll_controls
314: WHERE source_type = nvl(p_source_type,source_type)
315: AND payroll_source_code = nvl(p_source_code,payroll_source_code)
316: AND payroll_id = NVL (p_payroll_id, payroll_id) -- Bug 7137063
317: AND time_period_id = nvl(p_time_period_id,time_period_id)
331: payroll_source_code,
332: time_period_id,
333: batch_name,
334: phase
335: FROM psp_payroll_controls
336: where status_code in ('N','I')
337: and source_type <> 'A'
338: and payroll_control_id = l_payroll_control_id
339: or parent_payroll_control_id = l_payroll_control_id;
359: IF payroll_control_cur%NOTFOUND THEN
360: CLOSE payroll_control_cur;
361: EXIT;
362: END IF;
363: UPDATE psp_payroll_controls
364: SET status_code = 'I',
365: run_id = g_run_id
366: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
367:
414: payroll_source_code,
415: time_period_id,
416: batch_name,
417: phase
418: FROM psp_payroll_controls
419: WHERE status_code = 'I'
420: AND run_id = g_run_id
421: AND source_type <> 'A';
422:
481: --- Also removed update statment that makes summary lines status = R
482: -- whenever status = 'N', becos there is a new phase Summarize
483:
484: -- This part is used to delete the rejected summary lines, mark the status_code
485: -- in psp_payroll_controls to 'P' or 'N',delete the zero line reversal entry records
486: /* Bug 1929317.. Did some changes here...deleted the original code.....Look into previous version
487: to compare .. Basically to check the number of lines in dist lines ...instead of summing on dist amount
488: for deciding wether to change control rec status to either 'P' or 'N' */
489: IF payroll_control_rec.source_type = 'O' OR payroll_control_rec.source_type = 'N' THEN
492: WHERE distribution_amount = 0
493: AND payroll_sub_line_id IN (
494: select payroll_sub_line_id from psp_payroll_sub_lines where payroll_line_id IN (
495: select payroll_line_id from psp_payroll_lines where payroll_control_id IN (
496: select payroll_control_id from psp_payroll_controls where payroll_control_id=
497: payroll_control_rec.payroll_control_id)));
498:
499: SELECT count(*)
500: INTO l_line_counter
513: WHERE payroll_control_id = payroll_control_rec.payroll_control_id
514: AND status_code = 'R';
515: */
516:
517: -- update status_code to 'P' in psp_payroll_controls
518: UPDATE psp_payroll_controls
519: SET status_code = 'P',
520: run_id = NULL,
521: phase = NULL
514: AND status_code = 'R';
515: */
516:
517: -- update status_code to 'P' in psp_payroll_controls
518: UPDATE psp_payroll_controls
519: SET status_code = 'P',
520: run_id = NULL,
521: phase = NULL
522: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
520: run_id = NULL,
521: phase = NULL
522: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
523: ELSE
524: UPDATE psp_payroll_controls
525: SET status_code = 'N',
526: run_id = NULL
527: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
528: END IF;
545: WHERE payroll_control_id = payroll_control_rec.payroll_control_id
546: AND status_code = 'R';
547: */
548:
549: UPDATE psp_payroll_controls
550: SET status_code = 'P',
551: run_id = NULL,
552: phase = NULL
553: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
551: run_id = NULL,
552: phase = NULL
553: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
554: ELSE
555: UPDATE psp_payroll_controls
556: SET status_code = 'N',
557: run_id = NULL
558: WHERE payroll_control_id = payroll_control_rec.payroll_control_id;
559: END IF;
588: gl_posting_override_date,
589: business_group_id,
590: set_of_books_id,
591: currency_code -- Introduced for Bug 2916848 Ilo Mrc
592: FROM psp_payroll_controls
593: WHERE source_type <> 'A'
594: AND run_id = g_run_id
595: AND (phase is null or
596: phase in ('GMS_Tie_Back', 'GL_Tie_Back')); ---- added for 2444657
630: psp_organization_accounts pod,
631: psp_organization_accounts pos,
632: psp_element_type_accounts pea,
633: psp_default_labor_schedules pdls,
634: psp_payroll_controls ppc,
635: psp_payroll_lines ppl,
636: psp_payroll_sub_lines ppsl,
637: psp_distribution_lines pdl
638: WHERE pdl.status_code = 'N'
677: DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute10, pos.attribute10), NULL) attribute10
678: ---decode(ppg.suspense_org_Account_id, NULL, 'N', 'Y') Suspense_Flag commented for 2663344
679: FROM psp_pre_gen_dist_lines ppg,
680: psp_organization_accounts pos,
681: psp_payroll_controls ppc
682: WHERE ppg.status_code = 'N'
683: /* changed following condition for bug 2007521 */
684: AND ((ppg.gl_code_combination_id IS NOT NULL and pos.project_id is null)
685: OR pos.gl_code_combination_id IS NOT NULL)
712: DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute7, pos.attribute8), NULL) attribute7,
713: DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute8, pos.attribute8), NULL) attribute8,
714: DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute9, pos.attribute9), NULL) attribute9,
715: DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute10, pos.attribute10), NULL) attribute10
716: FROM psp_payroll_controls ppc,
717: psp_payroll_lines ppl,
718: psp_payroll_sub_lines ppsl,
719: psp_organization_accounts pos,
720: psp_distribution_lines pdl
829: IF gl_sum_lines_cursor%ROWCOUNT = 0 THEN
830: CLOSE gl_sum_lines_cursor;
831: EXIT;
832: ELSIF gl_sum_lines_cursor%NOTFOUND THEN
833: update psp_payroll_controls set phase = 'Summarize_GL_Lines' ---2444657:changed from NULL
834: where payroll_control_id = payroll_control_rec.payroll_control_id;
835: CLOSE gl_sum_lines_cursor;
836: EXIT;
837: END IF;
1210: time_period_id,
1211: batch_name,
1212: phase --- 2444657, to separate the old and new gl imports
1213: --- S and T can pull control recs with statuses 'I' and 'N' now
1214: FROM psp_payroll_controls
1215: WHERE status_code = 'I'
1216: AND source_type <> 'A'
1217: AND run_id = g_run_id
1218: AND phase in ('Summarize_GL_Lines', 'Submitted_JI_Request'); --- 2444657: added the condition
1225: SELECT payroll_control_id,
1226: currency_code,
1227: exchange_rate_type,
1228: phase --- 2444657: added
1229: FROM psp_payroll_controls
1230: WHERE source_type = p_source_type
1231: AND payroll_source_code = p_source_code
1232: AND time_period_id = p_time_period_id
1233: AND nvl(batch_name,'N') = nvl(nvl(p_batch_name,batch_name),'N')
1454: into l_group_id
1455: from psp_summary_lines
1456: where payroll_control_id in
1457: (SELECT payroll_control_id
1458: FROM psp_payroll_controls
1459: WHERE source_type = gl_batch_rec.source_type
1460: AND payroll_source_code = gl_batch_rec.payroll_source_code
1461: AND time_period_id = gl_batch_rec.time_period_id
1462: AND nvl(batch_name,'N') = nvl(nvl(gl_batch_rec.batch_name,batch_name),'N')
1524:
1525: ELSE
1526:
1527: -- Added the update for Rollback purposes.
1528: UPDATE psp_payroll_controls
1529: SET phase = 'Submitted_JI_Request'
1530: -- Introduced selective payroll control filter for bug fix 3157895
1531: WHERE source_type = NVL(gl_batch_rec.source_type, source_type)
1532: AND payroll_source_code = NVL(gl_batch_rec.payroll_source_code, payroll_source_code)
1947: delete gl_interface_Control
1948: where group_id = p_group_id
1949: and je_source_name = l_user_je_source_name;
1950:
1951: update psp_payroll_controls
1952: set phase = null
1953: where payroll_control_id = p_payroll_control_id;
1954:
1955: commit;
1979: --- moved this statement from transfer_to_gl procedure to the beginning of this
1980: -- procedure, becuase it not being set when user defined exceptions are occuring
1981: --- like invalid suspense FATAL error. Since there is a single commit for TIE-BACK
1982: -- this will work fine... for Bug fix 2444657
1983: UPDATE psp_payroll_controls
1984: SET phase = 'GL_Tie_Back'
1985: WHERE payroll_control_id = p_payroll_control_id;
1986:
1987: IF l_cnt_gl_interface > 0 THEN
2502: END IF;
2503:
2504: END LOOP;
2505:
2506: UPDATE psp_payroll_controls
2507: SET gl_dr_amount = nvl(gl_dr_amount,0) + l_dr_summary_amount,
2508: gl_cr_amount = nvl(gl_cr_amount,0) + l_cr_summary_amount
2509: WHERE payroll_control_id = p_payroll_control_id;
2510:
2566: pdl.set_of_books_id
2567: FROM psp_distribution_lines pdl,
2568: psp_payroll_sub_lines ppsl,
2569: psp_payroll_lines ppl,
2570: psp_payroll_controls ppc
2571: WHERE ppc.payroll_control_id = p_payroll_control_id
2572: AND ppc.payroll_control_id = ppl.payroll_control_id
2573: AND ppl.payroll_line_id = ppsl.payroll_line_id
2574: AND ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
2626: AND status_code = 'N'
2627: AND payroll_sub_line_id IN (
2628: select payroll_sub_line_id from psp_payroll_sub_lines where payroll_line_id IN (
2629: select payroll_line_id from psp_payroll_lines where payroll_control_id IN (
2630: select payroll_control_id from psp_payroll_controls where payroll_control_id=
2631: p_payroll_control_id)));
2632:
2633:
2634: -- recalculate and update the reversal sub-line amounts
2658: INTO l_cr_amount, l_dr_amount
2659: FROM psp_distribution_lines pdl,
2660: psp_payroll_sub_lines ppsl,
2661: psp_payroll_lines ppl,
2662: psp_payroll_controls ppc
2663: WHERE ppc.payroll_control_id = p_payroll_control_id
2664: AND ppc.payroll_control_id = ppl.payroll_control_id
2665: AND ppl.payroll_line_id = ppsl.payroll_line_id
2666: AND ppsl.payroll_sub_line_id = pdl.payroll_sub_line_id
2674: END IF;
2675:
2676: ELSIF p_source_type = 'P' THEN
2677: SELECT payroll_id INTO l_payroll_id
2678: FROM psp_payroll_controls
2679: WHERE payroll_control_id = p_payroll_control_id;
2680:
2681: BEGIN
2682: SELECT reversing_gl_ccid
2954: time_period_id,
2955: batch_name,
2956: business_group_id,
2957: set_of_books_id
2958: FROM psp_payroll_controls
2959: WHERE status_code = 'I'
2960: AND source_type <> 'A'
2961: AND run_id = g_run_id
2962: AND (phase is null or
3016: psp_organization_accounts pod,
3017: psp_organization_accounts pos,
3018: psp_element_type_accounts pea,
3019: psp_default_labor_schedules pdls,
3020: psp_payroll_controls ppc,
3021: psp_payroll_lines ppl,
3022: psp_payroll_sub_lines ppsl,
3023: psp_distribution_lines pdl
3024: WHERE pdl.status_code = 'N'
3070: DECODE(g_dff_grouping_option, 'Y', DECODE(ppg.suspense_org_account_id, NULL, ppg.attribute10, pos.attribute10), NULL) attribute10
3071: ---decode(ppg.suspense_org_Account_id, NULL, 'N', 'Y') Suspense_Flag commented for 2663344
3072: FROM psp_pre_gen_dist_lines ppg,
3073: psp_organization_accounts pos,
3074: psp_payroll_controls ppc
3075: WHERE ppg.status_code = 'N'
3076: /* changed following condn. First Pass can have suspense: Bug 2007521 */
3077: AND ((ppg.gl_code_combination_id IS NULL and pos.gl_code_combination_id is null) OR
3078: pos.project_id is not null)
3109: DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute9, pos.attribute9), NULL) attribute9,
3110: DECODE(g_dff_grouping_option, 'Y', DECODE(pdl.suspense_org_account_id, NULL, pdl.attribute10, pos.attribute10), NULL) attribute10
3111: ---decode(pdl.suspense_org_account_id, NULL, 'N', 'Y') Suspense_Flag uncommented for 2663344
3112: FROM psp_organization_accounts pos,
3113: psp_payroll_controls ppc,
3114: psp_payroll_lines ppl,
3115: psp_payroll_sub_lines ppsl,
3116: psp_distribution_lines pdl
3117: WHERE pdl.status_code = 'N'
3212: CLOSE gms_sum_lines_cursor;
3213: EXIT;
3214: ELSIF gms_sum_lines_cursor%NOTFOUND THEN
3215: if (l_rec_count > 1) then
3216: update psp_payroll_controls
3217: set phase = 'Summarize_GMS_Lines' ----2444657: Replaced NULL
3218: where payroll_control_id = payroll_control_rec.payroll_control_id;
3219: end if;
3220: CLOSE gms_sum_lines_cursor;
3478: -- Introduced the following currency_code,exchange_rate_type for bug 2916848
3479: Currency_code,
3480: exchange_rate_type,
3481: phase --- added for 2444657
3482: FROM psp_payroll_controls
3483: WHERE status_code = 'I'
3484: AND source_type <> 'A'
3485: AND run_id = g_run_id
3486: AND phase in ('Summarize_GMS_Lines','Submitted_TI_Request');
3946: fnd_msg_pub.add;
3947: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3948: END IF;
3949: END LOOP;
3950: update psp_payroll_controls
3951: set phase = 'Submitted_TI_Request'
3952: WHERE payroll_control_id = gms_batch_rec.payroll_control_id;
3953:
3954: commit;
4368: AND transaction_source = p_txn_source
4369: AND transaction_status_code in ('R', 'PO', 'PI', 'PR');
4370:
4371: /* moved this statement to beginning for 2444657 */
4372: UPDATE psp_payroll_controls
4373: SET phase = 'GL_Tie_Back'
4374: WHERE payroll_control_id = p_payroll_control_id;
4375: hr_utility.trace('entered gms_tie_back3');
4376:
4760:
4761: END LOOP;
4762: END LOOP;
4763: hr_utility.trace('update control record');
4764: UPDATE psp_payroll_controls
4765: SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
4766: ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
4767: WHERE payroll_control_id = p_payroll_control_id;
4768:
5025: --Moved delete xface stmnts from here to below for bug 2445196:
5026: END IF;
5027: END LOOP;
5028:
5029: UPDATE psp_payroll_controls
5030: SET ogm_dr_amount = nvl(ogm_dr_amount,0) + l_dr_summary_amount,
5031: ogm_cr_amount = nvl(ogm_cr_amount,0) + l_cr_summary_amount
5032: WHERE payroll_control_id = p_payroll_control_id;
5033:
5261: --- get all ER template runs, that overlapp S_AND_T time periods.
5262: cursor check_er_template_h is
5263: Select distinct pth.template_id,
5264: pth.request_id
5265: From psp_payroll_controls ppc,
5266: Psp_report_templates_h pth,
5267: Per_time_periods ptp
5268: Where ppc.run_id = p_run_id
5269: And ppc.time_period_id = ptp.time_period_id
5283: per.person_id,
5284: min(pal.summary_line_id) summary_line_id
5285: From psp_adjustment_lines_history pal,
5286: Psp_eff_reports per,
5287: Psp_payroll_controls ppc
5288: Where pal.distribution_date between per.start_date and per.end_date
5289: And per.request_id in ( select -person_id from psp_supercede_persons_gt where person_id < 0)
5290: And ppc.run_id = p_run_id
5291: And pal.person_id = per.person_id
5315: per.person_id,
5316: min(pregen.summary_line_id)
5317: From psp_pre_gen_dist_lines_history pregen,
5318: Psp_eff_reports per,
5319: Psp_payroll_controls ppc
5320: Where pregen.distribution_date between per.start_date and per.end_date
5321: And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
5322: And ppc.run_id = p_run_id
5323: And pregen.person_id = per.person_id
5351: per.person_id,
5352: MIN(dlh.summary_line_id) summary_line_id
5353: From psp_eff_reports per,
5354: Psp_distribution_lines_history DLH,
5355: Psp_payroll_controls ppc,
5356: psp_payroll_sub_lines psub,
5357: psp_payroll_lines ppl
5358: Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
5359: And per.request_id in (select -person_id from psp_supercede_persons_gt where person_id < 0)
5395: per.person_id,
5396: dlh.summary_line_id summary_line_id
5397: From psp_eff_reports per,
5398: Psp_distribution_lines_history DLH,
5399: Psp_payroll_controls ppc,
5400: psp_payroll_sub_lines psub,
5401: psp_payroll_lines ppl
5402: Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
5403: And per.request_id in (select -person_id from psp_supercede_persons_gt)
5433: per.person_id,
5434: pregen.summary_line_id summary_line_id
5435: From psp_pre_gen_dist_lines_history pregen,
5436: Psp_eff_reports per,
5437: Psp_payroll_controls ppc
5438: Where pregen.distribution_date between per.start_date and per.end_date
5439: And per.request_id in ( select -person_id from psp_supercede_persons_gt )
5440: And ppc.run_id = p_run_id
5441: And pregen.person_id = per.person_id
5470: per.person_id,
5471: min(pal.summary_line_id) summary_line_id
5472: From psp_adjustment_lines_history pal,
5473: Psp_eff_reports per,
5474: Psp_payroll_controls ppc,
5475: psp_eff_report_details perd
5476: Where pal.distribution_date between per.start_date and per.end_date
5477: and perd.effort_report_id = per.effort_report_id
5478: and perd.assignment_id = pal.assignment_id
5505: per.person_id,
5506: min(pregen.summary_line_id)
5507: From psp_pre_gen_dist_lines_history pregen,
5508: Psp_eff_reports per,
5509: Psp_payroll_controls ppc,
5510: psp_eff_report_details perd
5511: Where pregen.distribution_date between per.start_date and per.end_date
5512: and perd.effort_report_id = per.effort_report_id
5513: and perd.assignment_id = pregen.assignment_id
5544: per.person_id,
5545: MIN(dlh.summary_line_id) summary_line_id
5546: From psp_eff_reports per,
5547: Psp_distribution_lines_history DLH,
5548: Psp_payroll_controls ppc,
5549: psp_payroll_sub_lines psub,
5550: psp_payroll_lines ppl,
5551: psp_eff_report_details perd
5552: Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
5592: per.person_id,
5593: dlh.summary_line_id summary_line_id
5594: From psp_eff_reports per,
5595: Psp_distribution_lines_history DLH,
5596: Psp_payroll_controls ppc,
5597: psp_payroll_sub_lines psub,
5598: psp_payroll_lines ppl,
5599: psp_eff_report_details perd
5600: Where dlh.payroll_sub_line_id = psub.payroll_sub_line_id
5633: per.person_id,
5634: pregen.summary_line_id summary_line_id
5635: From psp_pre_gen_dist_lines_history pregen,
5636: Psp_eff_reports per,
5637: Psp_payroll_controls ppc,
5638: psp_eff_report_details perd
5639: Where pregen.distribution_date between per.start_date and per.end_date
5640: and perd.effort_report_id = per.effort_report_id
5641: and perd.assignment_id = pregen.assignment_id
5672: psp_summary_lines psl
5673: where per.person_id = psl.person_id
5674: and psl.payroll_control_id in
5675: (select payroll_control_id
5676: from psp_payroll_controls
5677: where run_id = p_run_id)
5678: and rownum = 1
5679: and per.request_id in
5680: (select abs(person_id)