eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PSP_ENC_SUM_TRAN dependencies on PSP_ENC_SUMMARY_LINES

Line 142: FROM psp_enc_summary_lines pesl

138:
139: CURSOR st_asgs_cur IS
140: SELECT DISTINCT assignment_id,
141: payroll_id
142: FROM psp_enc_summary_lines pesl
143: WHERE payroll_action_id = p_payroll_action_id
144: AND superceded_line_id IS NULL
145: AND status_code = 'N';
146:

Line 156: FROM psp_enc_summary_lines

152: AND peed.default_org_flag = 'Y';
153:
154: CURSOR check_gl_lines IS
155: SELECT COUNT(1)
156: FROM psp_enc_summary_lines
157: WHERE payroll_action_id = p_payroll_action_id
158: AND superceded_line_id IS NULL
159: AND gl_code_combination_id IS NOT NULL
160: AND status_code = 'N';

Line 164: FROM psp_enc_summary_lines

160: AND status_code = 'N';
161:
162: CURSOR check_gms_lines IS
163: SELECT COUNT(1)
164: FROM psp_enc_summary_lines
165: WHERE payroll_action_id = p_payroll_action_id
166: AND superceded_line_id IS NULL
167: AND award_id IS NOT NULL
168: AND status_code = 'N';

Line 414: DELETE psp_enc_summary_lines

410: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' After tr_to_gms_int');
411: END IF;
412:
413: g_suspense_failed := 'TRUE';
414: DELETE psp_enc_summary_lines
415: WHERE payroll_action_id = p_payroll_action_id
416: AND superceded_line_id IS NULL
417: AND status_code = 'N';
418: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted un-imported summary lines (' || SQL%ROWCOUNT || ')');

Line 571: -- with STATUS_CODE = 'N' and PSP_ENC_SUMMARY_LINES with STATUS_CODE = 'R'

567: -- if the program is completed with a return code of success and if the
568: -- return code is failed it updates ACTION_CODE = 'N'.
569:
570: -- When the program returns a failure status, it also updates PSP_ENC_LINES
571: -- with STATUS_CODE = 'N' and PSP_ENC_SUMMARY_LINES with STATUS_CODE = 'R'
572:
573: -- ##########################################################################
574:
575: PROCEDURE enc_batch_end (p_payroll_action_id IN NUMBER,

Line 579: FROM psp_enc_summary_lines pesl

575: PROCEDURE enc_batch_end (p_payroll_action_id IN NUMBER,
576: p_return_status OUT NOCOPY VARCHAR2) IS
577: CURSOR pending_line_cur IS
578: SELECT COUNT(1)
579: FROM psp_enc_summary_lines pesl
580: WHERE pesl.payroll_action_id = p_payroll_action_id
581: AND pesl.status_code = 'N';
582:
583: CURSOR check_enc_lines IS

Line 621: FROM psp_enc_summary_lines pesl

617: UPDATE psp_enc_controls pec
618: SET action_code = 'P'
619: WHERE payroll_action_id = p_payroll_action_id
620: AND NOT EXISTS (SELECT 1
621: FROM psp_enc_summary_lines pesl
622: WHERE pesl.payroll_action_id = p_payroll_action_id
623: AND pesl.enc_control_id = pec.enc_control_id
624: AND pesl.status_code = 'N');
625: IF (SQL%ROWCOUNT > 0) THEN

Line 634: FROM psp_enc_summary_lines pesl

630: SET process_status = 'P', process_phase = 'completed'
631: WHERE payroll_action_id = p_payroll_action_id
632: AND process_code = 'ST'
633: AND NOT EXISTS (SELECT 1
634: FROM psp_enc_summary_lines pesl
635: WHERE pesl.payroll_action_id = p_payroll_action_id
636: AND pesl.status_code = 'N');
637: IF (SQL%ROWCOUNT > 0) THEN
638: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to completed as no summarize and transfer is required');

Line 657: FROM psp_enc_summary_lines pesl

653: SET process_phase = 'liquidate'
654: WHERE payroll_action_id = p_payroll_action_id
655: AND process_code = 'ST'
656: AND EXISTS (SELECT 1
657: FROM psp_enc_summary_lines pesl
658: WHERE pesl.payroll_action_id = p_payroll_action_id
659: AND pesl.status_code = 'N'
660: AND pesl.superceded_line_id IS NOT NULL);
661: IF (SQL%ROWCOUNT > 0) THEN

Line 669: FROM psp_enc_summary_lines pesl

665: UPDATE psp_enc_process_assignments pepa
666: SET assignment_status = 'P'
667: WHERE payroll_action_id = p_payroll_action_id
668: AND NOT EXISTS (SELECT 1
669: FROM psp_enc_summary_lines pesl
670: WHERE pesl.payroll_action_id = p_payroll_action_id
671: AND pesl.assignment_id = pepa.assignment_id
672: AND pesl.payroll_id = pepa.payroll_id
673: AND pesl.status_code = 'N');

Line 682: FROM psp_enc_summary_lines pesl

678: UPDATE psp_enc_process_assignments pepa
679: SET assignment_status = 'S'
680: WHERE payroll_action_id = p_payroll_action_id
681: AND EXISTS (SELECT 1
682: FROM psp_enc_summary_lines pesl
683: WHERE pesl.payroll_action_id = p_payroll_action_id
684: AND pesl.assignment_id = pepa.assignment_id
685: AND pesl.payroll_id = pepa.payroll_id
686: AND pesl.status_code = 'N'

Line 696: FROM psp_enc_summary_lines pesl

692: UPDATE psp_enc_process_assignments pepa
693: SET assignment_status = 'L'
694: WHERE payroll_action_id = p_payroll_action_id
695: AND EXISTS (SELECT 1
696: FROM psp_enc_summary_lines pesl
697: WHERE pesl.payroll_action_id = p_payroll_action_id
698: AND pesl.assignment_id = pepa.assignment_id
699: AND pesl.payroll_id = pepa.payroll_id
700: AND pesl.status_code = 'N'

Line 751: FROM psp_enc_summary_lines

747: AND set_of_books_id = g_set_of_books_id;
748:
749: CURSOR new_enc_summary_lines_cur(P_ENC_CONTROL_ID IN NUMBER) IS
750: SELECT enc_summary_line_id
751: FROM psp_enc_summary_lines
752: WHERE enc_control_id = p_enc_control_id
753: AND status_code = 'N';
754:
755: enc_control_rec enc_control_cur%ROWTYPE;

Line 798: UPDATE psp_enc_summary_lines

794: set action_code = 'I'
795: where enc_control_id = enc_control_rec.enc_control_id; * / --- commented for 2479579
796: ELSE
797:
798: UPDATE psp_enc_summary_lines
799: SET status_code = 'R'
800: WHERE enc_control_id = enc_control_rec.enc_control_id
801: AND status_code = 'N';
802:

Line 822: DELETE FROM psp_enc_summary_lines

818:
819: / * Included as part of bug fix #1776606 * /
820: IF l_line_count = 0 THEN
821: / * commented for 2445196: preserving this lines useful for debugging purposes
822: DELETE FROM psp_enc_summary_lines
823: WHERE enc_control_id = enc_control_rec.enc_control_id
824: AND status_code = 'R'; * /
825:
826: UPDATE psp_enc_controls

Line 839: DELETE FROM psp_enc_summary_lines

835: WHERE enc_control_id = enc_control_rec.enc_control_id;
836: END IF;
837: / * Commented as part of Bug fix #1776606 * /
838: / * IF l_o_enc_sum = 0 THEN
839: DELETE FROM psp_enc_summary_lines
840: WHERE enc_control_id = enc_control_rec.enc_control_id
841: AND status_code = 'R';
842:
843: UPDATE psp_enc_controls

Line 861: FROM psp_enc_summary_lines pesl

857: UPDATE psp_enc_processes
858: SET process_status = 'P', process_phase = 'completed'
859: WHERE payroll_action_id = p_payroll_action_id
860: AND NOT EXISTS (SELECT 1
861: FROM psp_enc_summary_lines pesl
862: WHERE pesl.payroll_action_id = p_payroll_action_id
863: AND pesl.status_code = 'N');
864: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to completed as no summarize and transfer is required');
865:

Line 870: FROM psp_enc_summary_lines pesl

866: UPDATE psp_enc_processes
867: SET process_phase = 'summarize_transfer'
868: WHERE payroll_action_id = p_payroll_action_id
869: AND EXISTS (SELECT 1
870: FROM psp_enc_summary_lines pesl
871: WHERE pesl.payroll_action_id = p_payroll_action_id
872: AND pesl.status_code = 'N'
873: AND pesl.superceded_line_id IS NULL);
874: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to summarize and transfer re-processing');

Line 880: FROM psp_enc_summary_lines pesl

876: UPDATE psp_enc_processes
877: SET process_phase = 'liquidate'
878: WHERE payroll_action_id = p_payroll_action_id
879: AND EXISTS (SELECT 1
880: FROM psp_enc_summary_lines pesl
881: WHERE pesl.payroll_action_id = p_payroll_action_id
882: AND pesl.status_code = 'N'
883: AND pesl.superceded_line_id IS NOT NULL);
884: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate for liqduidation re-processing');

Line 899: -- and inserts the summarized lines into psp_enc_summary_lines

895: End of comment for Create and Update multi thread enh. *****/
896:
897: -- ##########################################################################
898: -- This procedure summarizes all the lines from psp_enc_lines
899: -- and inserts the summarized lines into psp_enc_summary_lines
900:
901: -- Depending on the setup options, this procedure groups transactions from
902: -- PSP_ENC_LINES and inserts the summarized lines into PSP_ENC_SUMMARY_LINES
903:

Line 902: -- PSP_ENC_LINES and inserts the summarized lines into PSP_ENC_SUMMARY_LINES

898: -- This procedure summarizes all the lines from psp_enc_lines
899: -- and inserts the summarized lines into psp_enc_summary_lines
900:
901: -- Depending on the setup options, this procedure groups transactions from
902: -- PSP_ENC_LINES and inserts the summarized lines into PSP_ENC_SUMMARY_LINES
903:
904: -- There are two setup options in PSP_ENC_SETUP_OPTIONS table called 'TIME_BASED_SUMM'
905: -- and 'CI_BASED_SUMM_GL' on which the procedure depends.
906:

Line 1717: -- This procedure inserts records into psp_enc_summary_lines

1713: END;
1714: End of comment for Create and Update enh. *****/
1715:
1716: -- ##########################################################################
1717: -- This procedure inserts records into psp_enc_summary_lines
1718: -- ##########################################################################
1719:
1720: PROCEDURE insert_into_enc_sum_lines(
1721: p_enc_summary_line_id OUT NOCOPY NUMBER,

Line 1755: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL

1751: p_attribute10 IN VARCHAR2,
1752: p_return_status OUT NOCOPY VARCHAR2
1753: ) IS
1754: BEGIN
1755: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL
1756: INTO P_ENC_SUMMARY_LINE_ID
1757: FROM DUAL;
1758: INSERT INTO PSP_ENC_SUMMARY_LINES(
1759: ENC_SUMMARY_LINE_ID,

Line 1758: INSERT INTO PSP_ENC_SUMMARY_LINES(

1754: BEGIN
1755: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL
1756: INTO P_ENC_SUMMARY_LINE_ID
1757: FROM DUAL;
1758: INSERT INTO PSP_ENC_SUMMARY_LINES(
1759: ENC_SUMMARY_LINE_ID,
1760: BUSINESS_GROUP_ID,
1761: ENC_CONTROL_ID,
1762: TIME_PERIOD_ID,

Line 1842: -- This procedure transfers the summarized lines from psp_enc_summary_lines

1838: p_return_status := fnd_api.g_ret_sts_unexp_error;
1839: END;
1840:
1841: -- ##########################################################################
1842: -- This procedure transfers the summarized lines from psp_enc_summary_lines
1843: -- with gl_project_flag = 'G' to gl_interface
1844:
1845: -- This procedure transfers the summarized lines from PSP_ENC_SUMMARY_LINES table
1846: -- to GL_INTERFACE table and kicks off the JOURNAL IMPORT program in GL and sends

Line 1845: -- This procedure transfers the summarized lines from PSP_ENC_SUMMARY_LINES table

1841: -- ##########################################################################
1842: -- This procedure transfers the summarized lines from psp_enc_summary_lines
1843: -- with gl_project_flag = 'G' to gl_interface
1844:
1845: -- This procedure transfers the summarized lines from PSP_ENC_SUMMARY_LINES table
1846: -- to GL_INTERFACE table and kicks off the JOURNAL IMPORT program in GL and sends
1847: -- ENC_CONTROL_ID and END_DATE for the relevant TIME_PERIOD_ID
1848: -- and GROUP_ID into the tie back procedure
1849: -- ##########################################################################

Line 1908: FROM psp_enc_summary_lines pesl

1904: pesl.attribute27,
1905: pesl.attribute28,
1906: pesl.attribute29,
1907: pesl.attribute30
1908: FROM psp_enc_summary_lines pesl
1909: WHERE pesl.status_code = 'N'
1910: AND pesl.enc_control_id = l_enc_control_id
1911: AND pesl.payroll_id = p_payroll_id
1912: AND pesl.gl_code_combination_id is NOT NULL;

Line 1976: FROM psp_enc_summary_lines

1972: r_enc_controls r_enc_control_rec;
1973:
1974: CURSOR enc_control_id_cur IS
1975: SELECT DISTINCT enc_control_id
1976: FROM psp_enc_summary_lines
1977: WHERE group_id = l_group_id;
1978: -- End of changes for bug fix 4507892
1979: BEGIN
1980: gl_tie_tab.delete;

Line 2018: UPDATE psp_enc_summary_lines

2014: l_rec_no := l_rec_no +1;
2015:
2016: gl_tie_tab(l_rec_no).r_control_id := enc_control_rec.enc_control_id;
2017:
2018: UPDATE psp_enc_summary_lines
2019: SET group_id = l_group_id
2020: WHERE status_code = 'N'
2021: AND gl_code_combination_id is NOT NULL
2022: AND enc_control_id = enc_control_rec.enc_control_id;

Line 2146: UPDATE psp_enc_summary_lines

2142: gl_tie_tab(l_rec_no).r_end_date := l_period_end_dt;* /
2143: -- Introduced the following as part of Enh. 2143723 (base bug 2124607)
2144: -- (Code moved from inside int_cur loop to here)
2145: IF enc_control_rec.gl_phase = 'Summarize' THEN
2146: UPDATE psp_enc_summary_lines
2147: SET group_id = l_group_id
2148: WHERE status_code = 'N'
2149: AND gl_code_combination_id is NOT NULL
2150: AND enc_control_id = enc_control_rec.enc_control_id;

Line 2163: from psp_enc_summary_lines

2159: gl_tie_tab(l_rec_no).r_control_id := enc_control_rec.enc_control_id;
2160: gl_tie_tab(l_rec_no).r_end_date := l_period_end_dt;
2161: select group_id
2162: into gl_tie_tab(l_rec_no).r_group_id
2163: from psp_enc_summary_lines
2164: where gl_code_combination_id is NOT NULL
2165: AND enc_control_id = enc_control_rec.enc_control_id
2166: AND rownum =1;
2167: END IF;

Line 2218: from psp_enc_summary_lines

2214: / ***** Converted the following UPDATE to BULK for R12 performance fixes
2215: UPDATE psp_enc_controls
2216: SET gl_phase = 'Transfer'
2217: WHERE enc_control_id in (select distinct enc_control_id
2218: from psp_enc_summary_lines
2219: where group_id = l_group_id);
2220: End of comment for bug fix 4507892 ***** /
2221:
2222: -- Introduced the following for bug fix 4507892

Line 2350: FROM psp_enc_summary_lines pesl

2346: l_created_by NUMBER(15);
2347:
2348: CURSOR gl_group_id_cur IS
2349: SELECT DISTINCT group_id
2350: FROM psp_enc_summary_lines pesl
2351: WHERE payroll_action_id = p_payroll_action_id
2352: AND status_code = 'N'
2353: AND superceded_line_id IS NULL
2354: AND gl_code_combination_id IS NOT NULL;

Line 2398: UPDATE psp_enc_summary_lines pesl

2394: FROM dual;
2395:
2396: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_group_id: ' || l_group_id);
2397:
2398: UPDATE psp_enc_summary_lines pesl
2399: SET group_id = l_group_id
2400: WHERE status_code = 'N'
2401: AND gl_code_combination_id IS NOT NULL
2402: AND group_id IS NULL

Line 2405: FROM psp_enc_summary_lines pesl2

2401: AND gl_code_combination_id IS NOT NULL
2402: AND group_id IS NULL
2403: AND superceded_line_id IS NULL
2404: AND NOT EXISTS (SELECT 1
2405: FROM psp_enc_summary_lines pesl2
2406: WHERE pesl2.payroll_action_id = p_payroll_action_id
2407: AND pesl2.assignment_id = pesl.assignment_id
2408: AND pesl2.time_period_id = pesl.time_period_id
2409: AND pesl2.status_code IN ('N', 'R')

Line 2413: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated group_id in psp_enc_summary_lines for new liquidation lines count: '||sql%rowcount);

2409: AND pesl2.status_code IN ('N', 'R')
2410: AND pesl2.superceded_line_id IS NOT NULL)
2411: AND payroll_action_id = p_payroll_action_id;
2412:
2413: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated group_id in psp_enc_summary_lines for new liquidation lines count: '||sql%rowcount);
2414: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'Inserting into gl_interface');
2415: INSERT INTO gl_interface
2416: (status, set_of_books_id, accounting_date,
2417: currency_code, date_created, created_by,

Line 2452: FROM psp_enc_summary_lines pesl,

2448: pesl.attribute21, pesl.attribute22, pesl.attribute23,
2449: pesl.attribute24, pesl.attribute25, pesl.attribute26,
2450: pesl.attribute27, pesl.attribute28, pesl.attribute29,
2451: pesl.attribute30
2452: FROM psp_enc_summary_lines pesl,
2453: psp_enc_controls pec
2454: WHERE pec.enc_control_id = pesl.enc_control_id
2455: AND pec.payroll_action_id = p_payroll_action_id
2456: AND pesl.status_code = 'N'

Line 2522: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');

2518: r_enc_controls.enc_control_id.DELETE;
2519:
2520: COMMIT;
2521:
2522: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2523: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2524: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2525:
2526: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');

Line 2523: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');

2519:
2520: COMMIT;
2521:
2522: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2523: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2524: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2525:
2526: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
2527:

Line 2524: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');

2520: COMMIT;
2521:
2522: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
2523: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
2524: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
2525:
2526: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
2527:
2528: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0, rphase, rstatus, dphase, dstatus, message);

Line 2580: UPDATE psp_enc_summary_lines

2576:
2577: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted groups from gl_interface for which gl_enc_tie_back is complete count: '||g_bulk_row_count);
2578:
2579: FORALL recno IN 1..r_groups.group_id.COUNT
2580: UPDATE psp_enc_summary_lines
2581: SET group_id = NULL
2582: WHERE group_id = r_groups.group_id(recno)
2583: AND status_code = 'N';
2584:

Line 2738: FROM psp_enc_summary_lines

2734: CURSOR gl_tie_back_success_cur IS
2735: SELECT enc_summary_line_id,
2736: dr_cr_flag,
2737: summary_amount
2738: FROM psp_enc_summary_lines
2739: WHERE group_id = p_group_id
2740: and enc_control_id = p_enc_control_id;
2741:
2742:

Line 2751: FROM psp_enc_summary_lines pesl

2747: WHERE user_je_source_name = 'OLD'
2748: AND set_of_books_id = p_set_of_books_id
2749: AND group_id = p_group_id
2750: AND reference6 IN (SELECT 'E:' || enc_summary_line_id -- Introduced for bug fix 3953230
2751: FROM psp_enc_summary_lines pesl
2752: WHERE pesl.enc_control_id = p_enc_control_id);
2753:
2754: CURSOR assign_susp_ac_cur(P_ENC_LINE_ID IN NUMBER) IS
2755: SELECT pel.rowid,

Line 2933: delete from psp_enc_summary_lines

2929: where user_je_source_name = 'OLD'
2930: and set_of_books_id = p_set_of_books_id
2931: and group_id = p_group_id;
2932:
2933: delete from psp_enc_summary_lines
2934: where group_id = p_group_id
2935: and enc_control_id = p_enc_control_id;
2936:
2937: return FALSE;

Line 2972: UPDATE psp_enc_summary_lines

2968: END IF;
2969:
2970: l_reference6 := substr(l_enc_ref, 3);
2971:
2972: UPDATE psp_enc_summary_lines
2973: SET interface_status = l_status,
2974: status_code = 'R'
2975: WHERE enc_summary_line_id = to_number(l_reference6);
2976:

Line 3184: FROM psp_enc_summary_lines

3180: encumbrance_date = prev_effective_date
3181: WHERE suspense_reason_code like 'ES:%'
3182: AND enc_summary_line_id
3183: IN (SELECT enc_summary_line_id
3184: FROM psp_enc_summary_lines
3185: WHERE enc_control_id = p_enc_control_id);
3186:
3187:
3188: / * End of code changes for Bug 3194807 * /

Line 3222: -- update records in psp_enc_summary_lines as 'A'

3218: EXIT;
3219: END IF;
3220:
3221: l_rec_cnt := l_rec_cnt + 1;
3222: -- update records in psp_enc_summary_lines as 'A'
3223: UPDATE psp_enc_summary_lines
3224: SET status_code = 'A'
3225: WHERE enc_summary_line_id = l_enc_summary_line_id;
3226:

Line 3223: UPDATE psp_enc_summary_lines

3219: END IF;
3220:
3221: l_rec_cnt := l_rec_cnt + 1;
3222: -- update records in psp_enc_summary_lines as 'A'
3223: UPDATE psp_enc_summary_lines
3224: SET status_code = 'A'
3225: WHERE enc_summary_line_id = l_enc_summary_line_id;
3226:
3227: IF l_dr_cr_flag = 'D' THEN

Line 3357: FROM psp_enc_summary_lines

3353: SELECT enc_summary_line_id,
3354: enc_control_id,
3355: dr_cr_flag,
3356: summary_amount
3357: FROM psp_enc_summary_lines
3358: WHERE group_id = p_group_id;
3359:
3360: CURSOR gl_tie_back_reject_cur IS
3361: SELECT status,

Line 3381: psp_enc_summary_lines pesl,

3377: pel.suspense_org_account_id,
3378: pesl.interface_status,
3379: ptp.end_date
3380: FROM psp_enc_lines pel,
3381: psp_enc_summary_lines pesl,
3382: per_time_periods ptp
3383: WHERE pel.payroll_action_id = p_payroll_action_id
3384: AND pel.enc_summary_line_id = pesl.enc_summary_line_id
3385: AND pesl.payroll_action_id = p_payroll_action_id

Line 3513: FROM psp_enc_summary_lines

3509: r_superceded_lines r_superceded_line_rec;
3510:
3511: CURSOR enc_controls_cur IS
3512: SELECT DISTINCT enc_control_id
3513: FROM psp_enc_summary_lines
3514: WHERE group_id = p_group_id;
3515: BEGIN
3516: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3517:

Line 3534: DELETE FROM psp_enc_summary_lines

3530: AND set_of_books_id = p_set_of_books_id
3531: AND group_id = p_group_id;
3532: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface count: '||sql%rowcount);
3533:
3534: DELETE FROM psp_enc_summary_lines
3535: WHERE group_id = p_group_id;
3536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines '||sql%rowcount);
3537: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3538: RETURN FALSE;

Line 3536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines '||sql%rowcount);

3532: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gl_interface count: '||sql%rowcount);
3533:
3534: DELETE FROM psp_enc_summary_lines
3535: WHERE group_id = p_group_id;
3536: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines '||sql%rowcount);
3537: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GL_ENC_TIE_BACK.PROCESS_COMPLETE');
3538: RETURN FALSE;
3539: END IF;
3540:

Line 3566: UPDATE psp_enc_summary_lines

3562:
3563: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.status.COUNT: ' || r_interface.status.COUNT);
3564:
3565: FORALL recno IN 1..r_interface.status.COUNT
3566: UPDATE psp_enc_summary_lines
3567: SET interface_status = r_interface.status(recno),
3568: status_code = 'R'
3569: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3570: AND r_interface.status(recno) <> 'P'

Line 3579: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated rejected lines status in psp_enc_summary_lines count: ' ||g_bulk_row_count);

3575: loop
3576: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3577: end loop;
3578:
3579: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated rejected lines status in psp_enc_summary_lines count: ' ||g_bulk_row_count);
3580:
3581: OPEN assign_susp_ac_cur;
3582: FETCH assign_susp_ac_cur BULK COLLECT INTO r_suspense_ac.row_id, r_suspense_ac.encumbrance_date,
3583: r_suspense_ac.enc_start_date, r_suspense_ac.enc_end_date, r_suspense_ac.assignment_id,

Line 3724: FROM psp_enc_summary_lines pesl

3720: gl_project_flag = decode(orig_gl_code_combination_id,NULL,'P','G'),
3721: encumbrance_date = prev_effective_date
3722: WHERE suspense_reason_code like 'ES:%'
3723: AND enc_summary_line_id IN (SELECT enc_summary_line_id
3724: FROM psp_enc_summary_lines pesl
3725: WHERE pesl.payroll_action_id = p_payroll_action_id
3726: AND pesl.group_id = p_group_id
3727: AND status_code = 'R');
3728: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updated psp_enc_lines2 count'||sql%rowcount);

Line 3740: FROM psp_enc_summary_lines pesl

3736: orig_expenditure_type = NULL,
3737: suspense_reason_code = NULL
3738: WHERE suspense_reason_code like 'ES:%'
3739: AND enc_summary_line_id IN (SELECT enc_summary_line_id
3740: FROM psp_enc_summary_lines pesl
3741: WHERE pesl.payroll_action_id = p_payroll_action_id
3742: AND pesl.group_id = p_group_id
3743: AND status_code = 'R');
3744:

Line 3764: FROM psp_enc_summary_lines pesl

3760: FORALL recno IN 1..r_interface.status.COUNT
3761: UPDATE psp_enc_controls
3762: SET gl_phase = 'TieBack'
3763: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3764: FROM psp_enc_summary_lines pesl
3765: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3766:
3767: g_bulk_row_count :=0;
3768: FOR bulk_idx IN 1..r_interface.status.COUNT

Line 3779: FROM psp_enc_summary_lines pesl

3775: FORALL recno IN 1..r_interface.status.COUNT
3776: UPDATE psp_enc_controls
3777: SET gl_phase = 'Summarize'
3778: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3779: FROM psp_enc_summary_lines pesl
3780: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3781: AND pesl.status_code = 'N');
3782:
3783: g_bulk_row_count :=0;

Line 3798: UPDATE psp_enc_summary_lines

3794:
3795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_interface.enc_summary_line_id.COUNT: ' || r_interface.enc_summary_line_id.COUNT);
3796:
3797: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3798: UPDATE psp_enc_summary_lines
3799: SET status_code = 'A'
3800: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno);
3801:
3802: g_bulk_row_count :=0;

Line 3808: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines count: '||g_bulk_row_count);

3804: loop
3805: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
3806: end loop;
3807:
3808: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines count: '||g_bulk_row_count);
3809:
3810: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3811: INSERT INTO psp_enc_lines_history
3812: (enc_line_id, business_group_id, enc_element_type_id, encumbrance_date,

Line 4092: -- into psp_enc_summary_lines

4088:
4089: -- ##########################################################################
4090: -- This procedure summarizes all the lines from psp_enc_lines
4091: -- where gl_project_flag = 'P' and inserts the summarized lines
4092: -- into psp_enc_summary_lines
4093:
4094: -- Depending on the setup options, this procedure groups transactions from
4095: -- PSP_ENC_LINES and inserts the summarized lines into PSP_ENC_SUMMARY_LINES
4096:

Line 4095: -- PSP_ENC_LINES and inserts the summarized lines into PSP_ENC_SUMMARY_LINES

4091: -- where gl_project_flag = 'P' and inserts the summarized lines
4092: -- into psp_enc_summary_lines
4093:
4094: -- Depending on the setup options, this procedure groups transactions from
4095: -- PSP_ENC_LINES and inserts the summarized lines into PSP_ENC_SUMMARY_LINES
4096:
4097: -- There are two setup options in PSP_ENC_SETUP_OPTIONS table called 'TIME_BASED_SUMM'
4098: -- and 'CI_BASED_SUMM_OGM' on which the procedure depends.
4099:

Line 4552: -- This procedure transfers summarized lines from psp_enc_summary_lines

4548: END;
4549: End of comment for Create and Update multi thread *****/
4550:
4551: -- ##########################################################################
4552: -- This procedure transfers summarized lines from psp_enc_summary_lines
4553: -- with gl_project_flag = 'P' to pa_transaction_interface
4554:
4555: -- This procedure transfers lines from PSP_ENC_SUMMARY_LINES into PA_TRANSACTION_INTERFACE,
4556: -- kicks off the TRANSACTION IMPORT program in GMS and sends ENC_CONTROL_ID, END_DATE for

Line 4555: -- This procedure transfers lines from PSP_ENC_SUMMARY_LINES into PA_TRANSACTION_INTERFACE,

4551: -- ##########################################################################
4552: -- This procedure transfers summarized lines from psp_enc_summary_lines
4553: -- with gl_project_flag = 'P' to pa_transaction_interface
4554:
4555: -- This procedure transfers lines from PSP_ENC_SUMMARY_LINES into PA_TRANSACTION_INTERFACE,
4556: -- kicks off the TRANSACTION IMPORT program in GMS and sends ENC_CONTROL_ID, END_DATE for
4557: -- the relevant TIME_PERIOD_ID and GMS_BATCH_NAME into the tie back procedure.
4558: -- ##########################################################################
4559: -- Introduced the following modified procedure for Create and Update multi thread

Line 4620: FROM psp_enc_summary_lines pesl,

4616: pesl.effective_date,
4617: pesl.gms_batch_name,
4618: DECODE(pec.uom, 'H', DECODE(SIGN(summary_amount), -1, 'Y')) unmatched_nve_txn_flag, --6242618
4619: papf.employee_number --Added for Bug 10126350
4620: FROM psp_enc_summary_lines pesl,
4621: hr_organization_units hou,
4622: pa_projects_all ppa,
4623: pa_tasks_expend_v pt, -- Bug : 16391366 (20/03/2013)
4624: per_time_periods ptp,

Line 4669: FROM psp_enc_summary_lines

4665: r_enc_controls r_enc_control_rec;
4666:
4667: CURSOR enc_control_id_cur IS
4668: SELECT DISTINCT enc_control_id
4669: FROM psp_enc_summary_lines
4670: WHERE payroll_action_id = p_payroll_action_id
4671: AND superceded_line_id IS NULL
4672: AND gms_batch_name IS NOT NULL;
4673:

Line 4681: FROM psp_enc_summary_lines pesl

4677: WHERE transaction_source = 'GOLDE';
4678:
4679: CURSOR gms_batch_name_cur IS
4680: SELECT DISTINCT gms_batch_name
4681: FROM psp_enc_summary_lines pesl
4682: WHERE payroll_action_id = p_payroll_action_id
4683: AND status_code = 'N'
4684: AND superceded_line_id IS NULL
4685: AND gl_code_combination_id IS NULL;

Line 4691: psp_enc_summary_lines pesl

4687: CURSOR employee_number_cur IS
4688: SELECT DISTINCT pesl.person_id,
4689: papf.employee_number
4690: FROM per_all_people_f papf,
4691: psp_enc_summary_lines pesl
4692: WHERE pesl.payroll_action_id = p_payroll_action_id
4693: AND papf.person_id = pesl.person_id
4694: AND pesl.superceded_line_id IS NULL
4695: AND pesl.gms_batch_name IS NOT NULL

Line 4759: FROM psp_enc_summary_lines

4755: call_status_tab call_status_TYPE;
4756:
4757: CURSOR operating_unit_csr IS
4758: SELECT DISTINCT org_id
4759: FROM psp_enc_summary_lines
4760: WHERE status_code = 'N'
4761: AND gl_code_combination_id IS NULL
4762: AND gms_batch_name IS NULL
4763: AND payroll_action_id = p_payroll_action_id;

Line 4807: UPDATE psp_enc_summary_lines pesl

4803: END LOOP;
4804:
4805:
4806: FORALL I IN 1..org_id_tab.count
4807: UPDATE psp_enc_summary_lines pesl
4808: SET gms_batch_name = gms_batch_name_tab(i)
4809: WHERE status_code = 'N'
4810: AND gl_code_combination_id IS NULL
4811: AND gms_batch_name IS NULL

Line 4814: FROM psp_enc_summary_lines pesl2

4810: AND gl_code_combination_id IS NULL
4811: AND gms_batch_name IS NULL
4812: ANd superceded_line_id IS NULL
4813: AND NOT EXISTS (SELECT 1
4814: FROM psp_enc_summary_lines pesl2
4815: WHERE pesl2.payroll_action_id = p_payroll_action_id
4816: AND pesl2.assignment_id = pesl.assignment_id
4817: AND pesl2.time_period_id = pesl.time_period_id
4818: AND pesl2.status_code IN ('N', 'R')

Line 4829: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updating psp_enc_summary_lines setting status_code N count: '||g_bulk_row_count);

4825: loop
4826: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
4827: end loop;
4828:
4829: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'updating psp_enc_summary_lines setting status_code N count: '||g_bulk_row_count);
4830:
4831: OPEN int_cur;
4832: FETCH int_cur BULK COLLECT INTO r_interface.txn_interface_id, r_interface.enc_summary_line_id,
4833: r_interface.effective_date, r_interface.time_period_id,

Line 4950: UPDATE psp_enc_summary_lines pesl

4946:
4947: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed computation of PA week ending date(s)');
4948:
4949: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
4950: UPDATE psp_enc_summary_lines pesl
4951: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
4952: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
4953: AND TRUNC(r_interface.effective_date(recno)) <> TRUNC(r_interface.gms_overriding_date(recno));
4954:

Line 5081: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');

5077: COMMIT;
5078: -- set the context again to multiple
5079: mo_global.set_policy_context('M', null);
5080:
5081: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5082: -- fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5083: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5084:
5085: FOR I IN 1..org_id_tab.count

Line 5082: -- fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');

5078: -- set the context again to multiple
5079: mo_global.set_policy_context('M', null);
5080:
5081: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5082: -- fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5083: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5084:
5085: FOR I IN 1..org_id_tab.count
5086: LOOP

Line 5083: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');

5079: mo_global.set_policy_context('M', null);
5080:
5081: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5082: -- fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5083: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5084:
5085: FOR I IN 1..org_id_tab.count
5086: LOOP
5087: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for comlpetion of Transaction Import');

Line 5123: UPDATE psp_enc_summary_lines pesl

5119: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' gms_enc_tie_back successful for gms_batch_name: ' || r_gms_batch.gms_batch_name(recno));
5120: END LOOP;
5121:
5122: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5123: UPDATE psp_enc_summary_lines pesl
5124: SET (pesl.expenditure_id, pesl.expenditure_item_id, pesl.expenditure_ending_date,
5125: pesl.txn_interface_id, pesl.interface_id) =
5126: (SELECT ptxn.expenditure_id, ptxn.expenditure_item_id, ptxn.expenditure_ending_date,
5127: ptxn.txn_interface_id, ptxn.interface_id

Line 5140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'update psp_enc_summary_lines setting encumbrance_item_id count: '||g_bulk_row_count);

5136: loop
5137: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5138: end loop;
5139:
5140: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || 'update psp_enc_summary_lines setting encumbrance_item_id count: '||g_bulk_row_count);
5141:
5142:
5143: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5144: DELETE pa_transaction_interface_all

Line 5174: UPDATE psp_enc_summary_lines

5170:
5171:
5172:
5173: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5174: UPDATE psp_enc_summary_lines
5175: SET gms_batch_name = NULL
5176: WHERE gms_batch_name = r_gms_batch.gms_batch_name(recno)
5177: AND status_code = 'N';
5178:

Line 5231: FROM psp_enc_summary_lines

5227: CURSOR gms_tie_back_success_cur IS
5228: SELECT enc_summary_line_id,
5229: dr_cr_flag,
5230: summary_amount
5231: FROM psp_enc_summary_lines
5232: WHERE gms_batch_name = p_gms_batch_name
5233: and enc_control_id = p_enc_control_id;
5234:
5235: CURSOR gms_tie_back_reject_cur IS

Line 5243: FROM psp_enc_summary_lines pesl

5239: FROM pa_transaction_interface_all
5240: WHERE transaction_source = 'GOLDE'
5241: AND batch_name = p_gms_batch_name
5242: AND orig_transaction_reference IN (SELECT 'E:' || enc_summary_line_id --added subqry for 3953230
5243: FROM psp_enc_summary_lines pesl
5244: WHERE pesl.enc_control_id = p_enc_control_id);
5245:
5246: CURSOR assign_susp_ac_cur(P_ENC_LINE_ID IN NUMBER) IS
5247: SELECT pel.rowid,

Line 5411: from psp_enc_summary_lines

5407: into l_cnt, l_status
5408: from pa_transaction_interface_all
5409: where transaction_source = 'GOLDE'
5410: and batch_name = (select distinct gms_batch_name
5411: from psp_enc_summary_lines
5412: where enc_control_id = p_enc_control_id
5413: and gms_batch_name is not null)
5414: and transaction_status_code in ('P', 'I')
5415: group by transaction_status_code ;

Line 5436: delete from psp_enc_summary_lines

5432: delete from gms_transaction_interface_all
5433: where transaction_source = 'GOLDE'
5434: and batch_name = p_gms_batch_name;
5435:
5436: delete from psp_enc_summary_lines
5437: where gms_batch_name = p_gms_batch_name
5438: and enc_control_id = p_enc_control_id;
5439:
5440: return 'NOT_RUN';

Line 5488: UPDATE psp_enc_summary_lines

5484:
5485: l_orig_trx_reference := substr(l_enc_ref, 3);
5486:
5487: IF l_trx_status_code in ('R', 'PI', 'PR', 'PO') THEN
5488: UPDATE psp_enc_summary_lines
5489: SET interface_status = l_trx_reject_code, status_code = 'R'
5490: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
5491: ELSIF l_trx_status_code = 'A' THEN
5492: UPDATE psp_enc_summary_lines

Line 5492: UPDATE psp_enc_summary_lines

5488: UPDATE psp_enc_summary_lines
5489: SET interface_status = l_trx_reject_code, status_code = 'R'
5490: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
5491: ELSIF l_trx_status_code = 'A' THEN
5492: UPDATE psp_enc_summary_lines
5493: SET interface_status = l_trx_reject_code, status_code = 'A'
5494: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
5495: -- END IF;
5496:

Line 5499: FROM psp_enc_summary_lines

5495: -- END IF;
5496:
5497: SELECT summary_amount, dr_cr_flag
5498: INTO l_summary_amount, l_dr_cr_flag
5499: FROM psp_enc_summary_lines
5500: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
5501:
5502: IF l_dr_cr_flag = 'D' THEN
5503: l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;

Line 5745: FROM psp_enc_summary_lines

5741: encumbrance_date = prev_effective_date
5742: WHERE suspense_reason_code like 'ES:%'
5743: AND enc_summary_line_id
5744: IN (SELECT enc_summary_line_id
5745: FROM psp_enc_summary_lines
5746: WHERE enc_control_id = p_enc_control_id);
5747:
5748:
5749: / * End of code changes for Bug 3194807 * /

Line 5788: UPDATE psp_enc_summary_lines

5784: EXIT;
5785: END IF;
5786: -- update records in psp_summary_lines as 'A'
5787:
5788: UPDATE psp_enc_summary_lines
5789: SET status_code = 'A'
5790: WHERE enc_summary_line_id = l_enc_summary_line_id;
5791:
5792: IF l_dr_cr_flag = 'D' THEN

Line 5926: FROM psp_enc_summary_lines

5922: SELECT enc_control_id,
5923: enc_summary_line_id,
5924: dr_cr_flag,
5925: TO_NUMBER(DECODE(dr_cr_flag, 'C', -summary_amount, summary_amount)) summary_amount
5926: FROM psp_enc_summary_lines
5927: WHERE gms_batch_name = p_gms_batch_name;
5928:
5929: CURSOR gms_tie_back_reject_cur IS
5930: SELECT NVL(transaction_rejection_code,'P'),

Line 5955: psp_enc_summary_lines pesl,

5951: pel.suspense_org_account_id,
5952: pesl.interface_status,
5953: ptp.end_date
5954: FROM psp_enc_lines pel,
5955: psp_enc_summary_lines pesl,
5956: per_time_periods ptp
5957: WHERE pel.payroll_action_id = p_payroll_action_id
5958: AND pel.enc_summary_line_id = pesl.enc_summary_line_id
5959: AND pesl.payroll_action_id = p_payroll_action_id

Line 6024: FROM psp_enc_summary_lines

6020: SELECT enc_control_id,
6021: enc_summary_line_id,
6022: dr_cr_flag,
6023: TO_NUMBER(DECODE(dr_cr_flag, 'C', -summary_amount, summary_amount)) summary_amount
6024: FROM psp_enc_summary_lines
6025: WHERE gms_batch_name = p_gms_batch_name
6026: AND status_code = 'A';
6027:
6028: l_encumbrance_date DATE;

Line 6171: DELETE FROM psp_enc_summary_lines

6167: DELETE FROM gms_transaction_interface_all
6168: WHERE transaction_source = 'GOLDE'
6169: AND batch_name = p_gms_batch_name;
6170: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all count: '||sql%rowcount);
6171: DELETE FROM psp_enc_summary_lines
6172: WHERE gms_batch_name = p_gms_batch_name;
6173: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines count: '||sql%rowcount);
6174:
6175: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE count: '||sql%rowcount);

Line 6173: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines count: '||sql%rowcount);

6169: AND batch_name = p_gms_batch_name;
6170: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all count: '||sql%rowcount);
6171: DELETE FROM psp_enc_summary_lines
6172: WHERE gms_batch_name = p_gms_batch_name;
6173: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines count: '||sql%rowcount);
6174:
6175: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Leaving GMS_ENC_TIE_BACK.PROCESS_COMPLETE count: '||sql%rowcount);
6176: RETURN FALSE;
6177: ELSIF l_status = 'I' THEN

Line 6202: UPDATE psp_enc_summary_lines

6198:
6199: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_reject_recs.enc_summary_line_id.COUNT: ' || r_reject_recs.enc_summary_line_id.COUNT);
6200:
6201: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6202: UPDATE psp_enc_summary_lines
6203: SET interface_status = r_reject_recs.reason_code(recno),
6204: status_code = 'R'
6205: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6206: AND r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR');

Line 6215: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated interface_status with reject reason code in psp_enc_summary_lines count: '||g_bulk_row_count);

6211: loop
6212: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6213: end loop;
6214:
6215: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated interface_status with reject reason code in psp_enc_summary_lines count: '||g_bulk_row_count);
6216:
6217: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6218: UPDATE psp_enc_summary_lines
6219: SET interface_status = r_reject_recs.reason_code(recno),

Line 6218: UPDATE psp_enc_summary_lines

6214:
6215: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated interface_status with reject reason code in psp_enc_summary_lines count: '||g_bulk_row_count);
6216:
6217: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6218: UPDATE psp_enc_summary_lines
6219: SET interface_status = r_reject_recs.reason_code(recno),
6220: status_code = 'A'
6221: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6222: AND r_reject_recs.txn_status_code(recno) = 'A';

Line 6231: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' for accepted records in psp_enc_summary_lines count: '||g_bulk_row_count);

6227: loop
6228: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6229: end loop;
6230:
6231: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' for accepted records in psp_enc_summary_lines count: '||g_bulk_row_count);
6232:
6233: OPEN get_success_recs_cur;
6234: FETCH get_success_recs_cur BULK COLLECT INTO r_success_recs.enc_control_id, r_success_recs.enc_summary_line_id,
6235: r_success_recs.dr_cr_flag, r_success_recs.summary_amount;

Line 6531: FROM psp_enc_summary_lines pesl

6527: enc_end_date = NVL(prev_enc_end_date, enc_end_date),
6528: encumbrance_amount = NVL(prev_encumbrance_amount, encumbrance_amount)
6529: WHERE suspense_reason_code like 'ES:%'
6530: AND enc_summary_line_id IN (SELECT enc_summary_line_id
6531: FROM psp_enc_summary_lines pesl
6532: WHERE pesl.payroll_action_id = p_payroll_action_id
6533: AND pesl.gms_batch_name = p_gms_batch_name
6534: AND status_code = 'R');
6535: UPDATE psp_enc_lines

Line 6547: FROM psp_enc_summary_lines pesl

6543: prev_enc_end_date = NULL,
6544: prev_encumbrance_amount = NULL
6545: WHERE suspense_reason_code like 'ES:%'
6546: AND enc_summary_line_id IN (SELECT enc_summary_line_id
6547: FROM psp_enc_summary_lines pesl
6548: WHERE pesl.payroll_action_id = p_payroll_action_id
6549: AND pesl.gms_batch_name = p_gms_batch_name
6550: AND status_code = 'R');
6551: END IF;

Line 6591: UPDATE psp_enc_summary_lines

6587:
6588: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_success_recs.enc_summary_line_id.COUNT: ' || r_success_recs.enc_summary_line_id.COUNT);
6589:
6590: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6591: UPDATE psp_enc_summary_lines
6592: SET status_code = 'A'
6593: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6594:
6595: g_bulk_row_count :=0;

Line 6601: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines count: '||g_bulk_row_count);

6597: loop
6598: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6599: end loop;
6600:
6601: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines count: '||g_bulk_row_count);
6602:
6603:
6604:
6605: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT

Line 6834: SELECT psp_enc_summary_lines_s.NEXTVAL INTO t_sum_lines.enc_summary_line_id(recno) FROM DUAL;

6830: CLOSE sum_lines_cur;
6831:
6832: FOR recno IN 1..t_sum_lines.enc_control_id.COUNT
6833: LOOP
6834: SELECT psp_enc_summary_lines_s.NEXTVAL INTO t_sum_lines.enc_summary_line_id(recno) FROM DUAL;
6835: END LOOP;
6836:
6837: FORALL recno IN 1..t_sum_lines.enc_control_id.COUNT
6838: INSERT INTO psp_enc_summary_lines

Line 6838: INSERT INTO psp_enc_summary_lines

6834: SELECT psp_enc_summary_lines_s.NEXTVAL INTO t_sum_lines.enc_summary_line_id(recno) FROM DUAL;
6835: END LOOP;
6836:
6837: FORALL recno IN 1..t_sum_lines.enc_control_id.COUNT
6838: INSERT INTO psp_enc_summary_lines
6839: (enc_summary_line_id, business_group_id, enc_control_id,
6840: time_period_id, person_id, assignment_id,
6841: effective_date, set_of_books_id, gl_code_combination_id,
6842: project_id, task_id, award_id,

Line 6957: FROM psp_enc_summary_lines pesl

6953: LOOP
6954: UPDATE psp_enc_lines_history pelh
6955: SET change_flag = 'N'
6956: WHERE EXISTS (SELECT 1
6957: FROM psp_enc_summary_lines pesl
6958: WHERE pesl.enc_summary_line_id = pelh.enc_summary_line_id
6959: AND status_code = 'A')
6960: AND assignment_id = l_asgs.assignment_id(asg_no)
6961: AND payroll_id = l_asgs.payroll_id(asg_no);

Line 7320: INSERT INTO PSP_ENC_SUMMARY_LINES_ARCH

7316: END add_st_warnings;
7317:
7318: PROCEDURE move_rej_lines_to_arch (p_payroll_action_id IN NUMBER) IS
7319: BEGIN
7320: INSERT INTO PSP_ENC_SUMMARY_LINES_ARCH
7321: (enc_summary_line_id, business_group_id, gms_batch_name,
7322: time_period_id, person_id, assignment_id,
7323: effective_date, set_of_books_id, gl_code_combination_id,
7324: project_id, expenditure_organization_id, expenditure_type,

Line 7371: FROM psp_enc_summary_lines

7367: gl_posting_override_date, expenditure_id, expenditure_item_id,
7368: expenditure_ending_date, interface_id, txn_interface_id,
7369: payroll_action_id, liquidate_request_id, proposed_termination_date,
7370: update_flag
7371: FROM psp_enc_summary_lines
7372: WHERE payroll_action_id = p_payroll_action_id
7373: AND status_code = 'R';
7374:
7375: IF (SQL%ROWCOUNT > 0) THEN

Line 7379: DELETE psp_enc_summary_lines

7375: IF (SQL%ROWCOUNT > 0) THEN
7376: fnd_file.put_line(fnd_file.log, 'Moved rejected lines (if any) to archival table as they are no longer useful count '|| sql%rowcount);
7377: END IF;
7378:
7379: DELETE psp_enc_summary_lines
7380: WHERE payroll_action_id = p_payroll_action_id
7381: AND status_code = 'R';
7382:
7383: fnd_file.put_line(fnd_file.log, 'Deleted rows from psp_enc_summary_lines with rejection code R'||sql%rowcount);

Line 7383: fnd_file.put_line(fnd_file.log, 'Deleted rows from psp_enc_summary_lines with rejection code R'||sql%rowcount);

7379: DELETE psp_enc_summary_lines
7380: WHERE payroll_action_id = p_payroll_action_id
7381: AND status_code = 'R';
7382:
7383: fnd_file.put_line(fnd_file.log, 'Deleted rows from psp_enc_summary_lines with rejection code R'||sql%rowcount);
7384:
7385: END move_rej_lines_to_arch;
7386: END psp_enc_sum_tran;