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_LIQ_TRAN dependencies on PSP_ENC_SUMMARY_LINES

Line 737: FROM psp_enc_summary_lines pesl,

733: DECODE(pesl.dr_cr_flag, 'D', 'Debit', 'Credit') dr_cr_flag,
734: DECODE(pesl.status_code, 'A', 'Accepted', 'L', 'Liquidated', 'S', 'Superceded', 'N', 'New', 'R', 'Rejected', pesl.status_code) status_code,
735: SUM(pesl.summary_amount),
736: COUNT(1)
737: FROM psp_enc_summary_lines pesl,
738: psp_enc_controls pec,
739: per_assignments_f paf,
740: per_people_f ppf
741: WHERE pesl.enc_control_id = pec.enc_control_id

Line 852: (SELECT pelh.assignment_id from psp_enc_summary_lines pesl,

848: and payroll_id = p_payroll_id);
849: / * Commented the following for Enh. 2143723 as Super Summarization is obsolete
850: Includes Restart Update process fix.
851: AND EXISTS
852: (SELECT pelh.assignment_id from psp_enc_summary_lines pesl,
853: psp_Enc_lines_history pelh where
854: pelh.enc_control_id=pec.enc_control_id and
855: pelh.enc_summary_line_id=pesl.enc_summary_line_id
856: AND pesl.enc_control_id=pec.enc_control_id AND

Line 891: AND EXISTS (SELECT pelh.assignment_id from psp_enc_summary_lines pesl,

887: and action_status = 'C')
888: and payroll_id = p_payroll_id);
889: / * Commented the following for Enh. 2143723 as Super Summarization is obsolete
890: Includes Restart Update process fix.
891: AND EXISTS (SELECT pelh.assignment_id from psp_enc_summary_lines pesl,
892: psp_enc_lines_history pelh WHERE
893: pelh.enc_control_id=pec.enc_control_id AND
894: pelh.enc_summary_line_id =pesl.enc_summary_line_id AND
895: pesl.enc_control_id=pec.enc_control_id AND

Line 1236: -- PSP_ENC_SUMMARY_LINES with STATUS_CODE = 'R'

1232: -- if the program is completed with a return code of success and if the
1233: -- return code is failed it updates ACTION_CODE = 'P'
1234:
1235: -- When the program returns a failure status, it also updates
1236: -- PSP_ENC_SUMMARY_LINES with STATUS_CODE = 'R'
1237:
1238: -- ##########################################################################
1239: PROCEDURE enc_batch_end (p_payroll_action_id IN NUMBER,
1240: -- p_payroll_id IN NUMBER,

Line 1249: FROM psp_enc_summary_lines

1245: p_return_status OUT NOCOPY VARCHAR2) IS
1246: CURSOR enc_control_cur IS
1247: SELECT DISTINCT enc_control_id
1248: --FROM psp_enc_controls
1249: FROM psp_enc_summary_lines
1250: --WHERE payroll_id = p_payroll_id
1251: WHERE payroll_action_id = p_payroll_action_id
1252: --AND run_id = g_run_id
1253: AND superceded_line_id IS NOT NULL

Line 1311: FROM psp_enc_summary_lines pesl,

1307: DECODE(pesl.dr_cr_flag, 'D', 'Debit', 'Credit') dr_cr_flag,
1308: DECODE(pesl.status_code, 'A', 'Accepted', 'L', 'Liquidated', 'S', 'Superceded', 'N', 'New', 'R', 'Rejected', pesl.status_code) status_code,
1309: SUM(pesl.summary_amount),
1310: COUNT(1)
1311: FROM psp_enc_summary_lines pesl,
1312: psp_enc_controls pec,
1313: per_assignments_f paf,
1314: per_people_f ppf
1315: WHERE pesl.enc_control_id = pec.enc_control_id

Line 1382: FROM psp_enc_summary_lines pesl

1378: FORALL recno IN 1..t_enc_control_id.COUNT
1379: UPDATE psp_enc_lines_history pelh
1380: SET change_flag = 'U'
1381: WHERE EXISTS (SELECT 1
1382: FROM psp_enc_summary_lines pesl
1383: WHERE status_code = 'A'
1384: AND pesl.enc_control_id = t_enc_control_id(recno)
1385: AND EXISTS (SELECT 1
1386: FROM psp_enc_summary_lines pesl2

Line 1386: FROM psp_enc_summary_lines pesl2

1382: FROM psp_enc_summary_lines pesl
1383: WHERE status_code = 'A'
1384: AND pesl.enc_control_id = t_enc_control_id(recno)
1385: AND EXISTS (SELECT 1
1386: FROM psp_enc_summary_lines pesl2
1387: WHERE pesl2.status_code = 'L'
1388: AND pesl2.enc_control_id = t_enc_control_id(recno)
1389: AND pesl2.superceded_line_id = pesl.enc_summary_line_id))
1390: AND pelh.enc_control_id = t_enc_control_id(recno);

Line 1397: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count:'|| g_bulk_row_count);

1393: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1394: loop
1395: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1396: end loop;
1397: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count:'|| g_bulk_row_count);
1398:
1399: FORALL recno IN 1..t_enc_control_id.COUNT
1400: UPDATE psp_enc_summary_lines pesl
1401: SET status_code = 'S'

Line 1400: UPDATE psp_enc_summary_lines pesl

1396: end loop;
1397: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count:'|| g_bulk_row_count);
1398:
1399: FORALL recno IN 1..t_enc_control_id.COUNT
1400: UPDATE psp_enc_summary_lines pesl
1401: SET status_code = 'S'
1402: WHERE EXISTS (SELECT 1
1403: FROM psp_enc_summary_lines pesl3
1404: WHERE pesl3.status_code = 'L'

Line 1403: FROM psp_enc_summary_lines pesl3

1399: FORALL recno IN 1..t_enc_control_id.COUNT
1400: UPDATE psp_enc_summary_lines pesl
1401: SET status_code = 'S'
1402: WHERE EXISTS (SELECT 1
1403: FROM psp_enc_summary_lines pesl3
1404: WHERE pesl3.status_code = 'L'
1405: AND pesl3.enc_control_id = t_enc_control_id(recno)
1406: AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
1407: AND pesl.enc_control_id = t_enc_control_id(recno);

Line 1414: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count'||g_bulk_row_count);

1410: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1411: loop
1412: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1413: end loop;
1414: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count'||g_bulk_row_count);
1415:
1416:
1417: FORALL recno IN 1..t_enc_control_id.COUNT
1418: UPDATE psp_enc_summary_lines pesl

Line 1418: UPDATE psp_enc_summary_lines pesl

1414: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines for superceded lines count'||g_bulk_row_count);
1415:
1416:
1417: FORALL recno IN 1..t_enc_control_id.COUNT
1418: UPDATE psp_enc_summary_lines pesl
1419: SET status_code = 'A'
1420: WHERE status_code = 'S'
1421: AND pesl.enc_summary_line_id IN (SELECT pesl2.superceded_line_id
1422: FROM psp_enc_summary_lines pesl2

Line 1422: FROM psp_enc_summary_lines pesl2

1418: UPDATE psp_enc_summary_lines pesl
1419: SET status_code = 'A'
1420: WHERE status_code = 'S'
1421: AND pesl.enc_summary_line_id IN (SELECT pesl2.superceded_line_id
1422: FROM psp_enc_summary_lines pesl2
1423: WHERE pesl2.status_code = 'N'
1424: AND pesl2.enc_control_id = t_enc_control_id(recno))
1425: AND NOT EXISTS (SELECT 1
1426: FROM psp_enc_summary_lines pesl3

Line 1426: FROM psp_enc_summary_lines pesl3

1422: FROM psp_enc_summary_lines pesl2
1423: WHERE pesl2.status_code = 'N'
1424: AND pesl2.enc_control_id = t_enc_control_id(recno))
1425: AND NOT EXISTS (SELECT 1
1426: FROM psp_enc_summary_lines pesl3
1427: WHERE pesl3.status_code = 'L'
1428: AND pesl3.enc_control_id = t_enc_control_id(recno)
1429: AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
1430: AND pesl.enc_control_id = t_enc_control_id(recno);

Line 1437: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines for lines rejected or not imported into target systems, count: '||g_bulk_row_count);

1433: FOR bulk_idx IN 1..t_enc_control_id.COUNT
1434: loop
1435: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
1436: end loop;
1437: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''A'' in psp_enc_summary_lines for lines rejected or not imported into target systems, count: '||g_bulk_row_count);
1438:
1439:
1440: FORALL recno IN 1..t_enc_control_id.COUNT
1441: UPDATE psp_enc_lines_history pelh

Line 1444: FROM psp_enc_summary_lines pesl

1440: FORALL recno IN 1..t_enc_control_id.COUNT
1441: UPDATE psp_enc_lines_history pelh
1442: SET change_flag = 'L'
1443: WHERE pelh.enc_summary_line_id IN (SELECT pesl.superceded_line_id
1444: FROM psp_enc_summary_lines pesl
1445: WHERE pesl.status_code = 'L'
1446: AND pesl.enc_control_id = t_enc_control_id(recno))
1447: AND pelh.enc_control_id = t_enc_control_id(recno);
1448:

Line 1462: FROM psp_enc_summary_lines pesl

1458: UPDATE psp_enc_controls
1459: SET action_code = 'L'
1460: WHERE enc_control_id = t_enc_control_id(recno)
1461: AND NOT EXISTS (SELECT 1
1462: FROM psp_enc_summary_lines pesl
1463: WHERE pesl.enc_control_id = t_enc_control_id(recno)
1464: AND status_code IN ('N','R','A'));
1465:
1466: g_bulk_row_count :=0;

Line 1479: FROM psp_enc_summary_lines pesl

1475: UPDATE psp_enc_controls
1476: SET action_code = 'P'
1477: WHERE enc_control_id = t_enc_control_id(recno)
1478: AND EXISTS (SELECT 1
1479: FROM psp_enc_summary_lines pesl
1480: WHERE pesl.enc_control_id = t_enc_control_id(recno)
1481: AND status_code = 'A');
1482: g_bulk_row_count :=0;
1483: FOR bulk_idx IN 1..t_enc_control_id.COUNT

Line 1496: FROM psp_enc_summary_lines pesl

1492: process_status = 'P'
1493: WHERE payroll_action_id = p_payroll_action_id
1494: AND process_code = 'ST'
1495: AND NOT EXISTS (SELECT 1
1496: FROM psp_enc_summary_lines pesl
1497: WHERE pesl.payroll_action_id = p_payroll_action_id
1498: AND pesl.status_code = 'N');
1499:
1500: IF (SQL%ROWCOUNT> 0) THEN

Line 1509: FROM psp_enc_summary_lines pesl

1505: SET process_phase = 'summarize_transfer'
1506: WHERE payroll_action_id = p_payroll_action_id
1507: AND process_code = 'ST'
1508: AND EXISTS (SELECT 1
1509: FROM psp_enc_summary_lines pesl
1510: WHERE pesl.payroll_action_id = p_payroll_action_id
1511: AND pesl.status_code = 'N'
1512: AND pesl.superceded_line_id IS NULL);
1513:

Line 1523: FROM psp_enc_summary_lines pesl

1519: SET process_phase = 'liquidate'
1520: WHERE payroll_action_id = p_payroll_action_id
1521: AND process_code = 'ST'
1522: AND EXISTS (SELECT 1
1523: FROM psp_enc_summary_lines pesl
1524: WHERE pesl.payroll_action_id = p_payroll_action_id
1525: AND pesl.status_code = 'N'
1526: AND pesl.superceded_line_id IS NOT NULL);
1527: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated process_phase to liquidate as liquidation process isn''t complete');

Line 1562: FROM psp_enc_summary_lines pesl

1558: UPDATE psp_enc_process_assignments pepa
1559: SET assignment_status = 'P'
1560: WHERE payroll_action_id = p_payroll_action_id
1561: AND NOT EXISTS (SELECT 1
1562: FROM psp_enc_summary_lines pesl
1563: WHERE pesl.payroll_action_id = p_payroll_action_id
1564: AND pesl.assignment_id = pepa.assignment_id
1565: AND pesl.payroll_id = pepa.payroll_id
1566: AND pesl.status_code = 'N');

Line 1575: FROM psp_enc_summary_lines pesl

1571: UPDATE psp_enc_process_assignments pepa
1572: SET assignment_status = 'S'
1573: WHERE payroll_action_id = p_payroll_action_id
1574: AND EXISTS (SELECT 1
1575: FROM psp_enc_summary_lines pesl
1576: WHERE pesl.payroll_action_id = p_payroll_action_id
1577: AND pesl.assignment_id = pepa.assignment_id
1578: AND pesl.payroll_id = pepa.payroll_id
1579: AND pesl.status_code = 'N'

Line 1589: FROM psp_enc_summary_lines pesl

1585: UPDATE psp_enc_process_assignments pepa
1586: SET assignment_status = 'L'
1587: WHERE payroll_action_id = p_payroll_action_id
1588: AND EXISTS (SELECT 1
1589: FROM psp_enc_summary_lines pesl
1590: WHERE pesl.payroll_action_id = p_payroll_action_id
1591: AND pesl.assignment_id = pepa.assignment_id
1592: AND pesl.payroll_id = pepa.payroll_id
1593: AND pesl.status_code = 'N'

Line 1733: FROM psp_enc_summary_lines

1729: AND set_of_books_id = g_sob_id; * /
1730:
1731: CURSOR rej_enc_summary_lines_cur(P_ENC_CONTROL_ID IN NUMBER) IS
1732: SELECT status_code,superceded_line_id
1733: FROM psp_enc_summary_lines
1734: WHERE enc_control_id = p_enc_control_id
1735: AND status_code <> 'A';
1736:
1737: enc_control_rec enc_control_cur%ROWTYPE;

Line 1747: FROM psp_enc_summary_lines

1743:
1744: -- Included the following cursors for Enh. 2143723
1745: CURSOR summary_line_count_cur IS
1746: SELECT count(*)
1747: FROM psp_enc_summary_lines
1748: WHERE enc_control_id = enc_control_rec.enc_control_id
1749: AND status_code = 'A';
1750:
1751: CURSOR pending_enc_lines_cur Is

Line 1769: UPDATE psp_enc_summary_lines

1765: END IF;
1766:
1767: -- This part is used to mark the status_code of non-transferred summary lines to 'R'
1768:
1769: UPDATE psp_enc_summary_lines
1770: SET status_code = 'R'
1771: WHERE enc_control_id = enc_control_rec.enc_control_id
1772: AND status_code = 'N';
1773:

Line 1789: UPDATE psp_Enc_summary_lines S1 set S1.status_code='A' where

1785:
1786: IF l_status_code = 'R' THEN
1787: if l_sup_enc_summary_line_id <>0 then
1788:
1789: UPDATE psp_Enc_summary_lines S1 set S1.status_code='A' where
1790: S1.enc_summary_line_id=l_sup_enc_summary_line_id and
1791: S1.status_code='S' and not exists
1792: -- added code to ensure there is no L line before reverting S
1793: -- for 2479579

Line 1794: (select 1 from psp_enc_summary_lines S2

1790: S1.enc_summary_line_id=l_sup_enc_summary_line_id and
1791: S1.status_code='S' and not exists
1792: -- added code to ensure there is no L line before reverting S
1793: -- for 2479579
1794: (select 1 from psp_enc_summary_lines S2
1795: where S2.status_code = 'L' and S1.enc_control_id = S2.enc_control_id
1796: and S2.superceded_line_id = l_sup_enc_summary_line_id);
1797: / * mark the change_flag in psp_Enc_lineS_history as well * /
1798: -- Update the change_flag to 'U' as per HQ review comment for Enh. 2143723

Line 1811: DELETE FROM psp_enc_summary_lines

1807:
1808: end if;
1809: / * Start Bug#2142865 Added delete to delete rejected records * /
1810: / * reverted the delete for 2445196
1811: DELETE FROM psp_enc_summary_lines
1812: WHERE enc_control_id = enc_control_rec.enc_control_id
1813: AND status_code = 'R'; * /
1814: / * Commented for Restart Update/Quick Update Encumbrance Lines Enh.
1815: DELETE FROM psp_enc_summary_lines

Line 1815: DELETE FROM psp_enc_summary_lines

1811: DELETE FROM psp_enc_summary_lines
1812: WHERE enc_control_id = enc_control_rec.enc_control_id
1813: AND status_code = 'R'; * /
1814: / * Commented for Restart Update/Quick Update Encumbrance Lines Enh.
1815: DELETE FROM psp_enc_summary_lines
1816: WHERE enc_control_id = enc_control_rec.enc_control_id
1817: AND status_code = 'R';
1818:
1819: -- update status_code to 'P' in psp_payroll_controls

Line 1859: FROM psp_enc_summary_lines

1855:
1856: -- Moved the following SELECT into cursor summary_line_count_cur
1857: SELECT count(*)
1858: into l_line_count
1859: FROM psp_enc_summary_lines
1860: WHERE enc_control_id = enc_control_rec.enc_control_id
1861: and status_code = 'A';
1862: End of Enh. fix 2143723 * /
1863:

Line 1970: -- psp_enc_summary_lines and sending them to GL

1966: End of comment for bug fix 4625734 ****/
1967:
1968: -- ##########################################################################
1969: -- This procedure liquidates the summary lines by creating new lines in
1970: -- psp_enc_summary_lines and sending them to GL
1971:
1972: -- This procedure reverses the summary lines with STATUS_CODE = 'A' by creating
1973: -- new lines in PSP_ENC_SUMMARY_LINES
1974: -- ##########################################################################

Line 1973: -- new lines in PSP_ENC_SUMMARY_LINES

1969: -- This procedure liquidates the summary lines by creating new lines in
1970: -- psp_enc_summary_lines and sending them to GL
1971:
1972: -- This procedure reverses the summary lines with STATUS_CODE = 'A' by creating
1973: -- new lines in PSP_ENC_SUMMARY_LINES
1974: -- ##########################################################################
1975: -- Commented the existing create_gl_enc_liq_lines procedure and created new procedure that made use of BULK FETCH
1976: -- statements to improve on performance of the liquidation process.
1977: -- Introduced the foolowing for bug fix 4625734

Line 2004: FROM psp_enc_summary_lines pesl

2000: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
2001: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
2002: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
2003: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
2004: FROM psp_enc_summary_lines pesl
2005: WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
2006: FROM psp_enc_controls pec
2007: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
2008: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 2043: FROM psp_enc_summary_lines pesl

2039: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
2040: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
2041: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
2042: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
2043: FROM psp_enc_summary_lines pesl
2044: WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
2045: FROM psp_enc_controls pec
2046: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
2047: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 2086: FROM psp_enc_summary_lines pesl

2082: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
2083: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
2084: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
2085: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
2086: FROM psp_enc_summary_lines pesl
2087: WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
2088: FROM psp_enc_controls pec
2089: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
2090: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 2130: FROM psp_enc_summary_lines pesl

2126: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
2127: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
2128: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
2129: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
2130: FROM psp_enc_summary_lines pesl
2131: WHERE enc_control_id IN (SELECT pec.enc_control_id
2132: FROM psp_enc_controls pec
2133: WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)
2134: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 2246: INSERT INTO psp_enc_summary_lines

2242:
2243: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_liq_lines.enc_summary_line_id.COUNT: ' || r_liq_lines.enc_summary_line_id.COUNT);
2244:
2245: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2246: INSERT INTO psp_enc_summary_lines
2247: (enc_summary_line_id, business_group_id, enc_control_id,
2248: time_period_id, person_id, assignment_id,
2249: effective_date, set_of_books_id, gl_code_combination_id,
2250: summary_amount, dr_cr_flag, status_code,

Line 2258: VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,

2254: attribute6, attribute7, attribute8,
2255: attribute9, attribute10, liquidate_request_id,
2256: proposed_termination_date, last_update_date, last_updated_by,
2257: last_update_login, created_by, creation_date)
2258: VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,
2259: r_liq_lines.enc_control_id(recno), r_liq_lines.time_period_id(recno),
2260: r_liq_lines.person_id(recno), r_liq_lines.assignment_id(recno),
2261: r_liq_lines.effective_date(recno), r_liq_lines.set_of_books_id(recno),
2262: r_liq_lines.gl_code_combination_id(recno), r_liq_lines.summary_amount(recno),

Line 2275: UPDATE psp_enc_summary_lines

2271: g_request_id, g_actual_term_date,
2272: SYSDATE, l_last_updated_by, l_last_update_login, l_last_updated_by, SYSDATE);
2273:
2274: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2275: UPDATE psp_enc_summary_lines
2276: SET status_code = 'S'
2277: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
2278:
2279: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');

Line 2279: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');

2275: UPDATE psp_enc_summary_lines
2276: SET status_code = 'S'
2277: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
2278:
2279: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
2280:
2281: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2282: UPDATE psp_enc_controls
2283: SET gl_phase = 'Summarize'

Line 2348: FROM psp_enc_summary_lines pesl

2344: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
2345: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
2346: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
2347: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
2348: FROM psp_enc_summary_lines pesl
2349: WHERE pesl.enc_control_id = p_enc_control_id
2350: --- changed pelh to pesl for performance ..3684930
2351: AND pesl.gl_project_flag = 'G'
2352: AND pesl.status_code = 'A'

Line 2458: update psp_enc_summary_lines set status_code='S'

2454: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2455: END IF;
2456:
2457: / * Flag the original line as Superceded* /
2458: update psp_enc_summary_lines set status_code='S'
2459: where enc_summary_line_id=enc_liq_rec.enc_summary_line_id;
2460:
2461:
2462:

Line 2493: -- This procedure inserts records into psp_enc_summary_lines

2489: End of comment for bug fix 4625734 *****/
2490:
2491: /***** Commented the following procedure as it is no longer used (for bug fix 4625734)
2492: -- ##########################################################################
2493: -- This procedure inserts records into psp_enc_summary_lines
2494: -- ##########################################################################
2495:
2496: PROCEDURE insert_into_enc_sum_lines(
2497: p_enc_summary_line_id OUT NOCOPY NUMBER,

Line 2536: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL

2532: p_expenditure_item_id IN NUMBER, -- bug 4068182
2533: p_return_status OUT NOCOPY VARCHAR2
2534: ) IS
2535: BEGIN
2536: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL
2537: INTO P_ENC_SUMMARY_LINE_ID
2538: FROM DUAL;
2539: INSERT INTO PSP_ENC_SUMMARY_LINES(
2540: ENC_SUMMARY_LINE_ID,

Line 2539: INSERT INTO PSP_ENC_SUMMARY_LINES(

2535: BEGIN
2536: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL
2537: INTO P_ENC_SUMMARY_LINE_ID
2538: FROM DUAL;
2539: INSERT INTO PSP_ENC_SUMMARY_LINES(
2540: ENC_SUMMARY_LINE_ID,
2541: BUSINESS_GROUP_ID,
2542: ENC_CONTROL_ID,
2543: TIME_PERIOD_ID,

Line 2627: --dbms_output.put_line('Insert into psp_enc_summary_lines failed');

2623: p_return_status := fnd_api.g_ret_sts_success;
2624:
2625: EXCEPTION
2626: WHEN OTHERS THEN
2627: --dbms_output.put_line('Insert into psp_enc_summary_lines failed');
2628: g_error_api_path := 'insert_into_enc_sum_lines:'||g_error_api_path;
2629: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','INSERT_INTO_ENC_SUM_LINES');
2630: p_return_status := fnd_api.g_ret_sts_unexp_error;
2631: END insert_into_enc_sum_lines;

Line 2635: -- This procedure transfers the liquidated lines from psp_enc_summary_lines

2631: END insert_into_enc_sum_lines;
2632: End of comment for bug fix 4625734 *****/
2633:
2634: -- ##########################################################################
2635: -- This procedure transfers the liquidated lines from psp_enc_summary_lines
2636: -- with gl_project_flag = 'G' to gl_interface
2637:
2638: -- This procedure transfers the liquidated lines from PSP_ENC_SUMMARY_LINES table
2639: -- to GL_INTERFACE table and kicks off the JOURNAL IMPORT program in GL and sends

Line 2638: -- This procedure transfers the liquidated lines from PSP_ENC_SUMMARY_LINES table

2634: -- ##########################################################################
2635: -- This procedure transfers the liquidated lines from psp_enc_summary_lines
2636: -- with gl_project_flag = 'G' to gl_interface
2637:
2638: -- This procedure transfers the liquidated lines from PSP_ENC_SUMMARY_LINES table
2639: -- to GL_INTERFACE table and kicks off the JOURNAL IMPORT program in GL and sends
2640: -- ENC_CONTROL_ID and END_DATE for the relevant TIME_PERIOD_ID
2641: -- and GROUP_ID into the tie back procedure
2642: -- ##########################################################################

Line 2703: FROM psp_enc_summary_lines pesl

2699: pesl.attribute27,
2700: pesl.attribute28,
2701: pesl.attribute29,
2702: pesl.attribute30
2703: FROM psp_enc_summary_lines pesl
2704: WHERE pesl.status_code = 'N'
2705: AND pesl.gl_code_combination_id is NOT NULL
2706: AND pesl.enc_control_id = l_enc_control_id;
2707: End of comment for bug fix 4625734 *****/

Line 2773: FROM psp_enc_summary_lines pesl

2769: l_created_by NUMBER(15);
2770:
2771: CURSOR gl_group_id_cur IS
2772: SELECT DISTINCT group_id
2773: FROM psp_enc_summary_lines pesl
2774: WHERE pesl.payroll_action_id = p_payroll_action_id
2775: /*WHERE enc_control_id IN (SELECT pec.enc_control_id
2776: FROM psp_enc_controls pec
2777: WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)

Line 2789: FROM psp_enc_summary_lines

2785: AND gl_code_combination_id IS NOT NULL;
2786:
2787: CURSOR enc_control_id_cur IS
2788: SELECT DISTINCT enc_control_id
2789: FROM psp_enc_summary_lines
2790: WHERE group_id = l_group_id;
2791: -- End of changes for bug fix 4507892
2792: BEGIN
2793: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering Transfer to GL Interface');

Line 2830: UPDATE psp_enc_summary_lines

2826:
2827: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_group_id: ' || l_group_id);
2828:
2829:
2830: UPDATE psp_enc_summary_lines
2831: SET group_id = l_group_id
2832: WHERE status_code = 'N'
2833: AND gl_code_combination_id IS NOT NULL
2834: AND superceded_line_id IS NOT NULL

Line 2847: 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');

2843: AND pec.business_group_id = g_bg_id
2844: AND pec.set_of_books_id = g_sob_id
2845: AND pec.gl_phase = 'Summarize');*/
2846:
2847: 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');
2848: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Inserting into gl_interface');
2849:
2850: INSERT INTO gl_interface
2851: (status, set_of_books_id, accounting_date,

Line 2887: FROM psp_enc_summary_lines pesl,

2883: attribute21, attribute22, attribute23,
2884: attribute24, attribute25, attribute26,
2885: attribute27, attribute28, attribute29,
2886: attribute30
2887: FROM psp_enc_summary_lines pesl,
2888: psp_enc_controls pec
2889: WHERE pec.enc_control_id = pesl.enc_control_id
2890: AND pesl.status_code = 'N'
2891: AND pesl.gl_code_combination_id is NOT NULL

Line 2933: UPDATE psp_enc_summary_lines

2929: fnd_msg_pub.add;
2930: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2931: END;
2932: If enc_control_rec.gl_phase = 'Summarize' then --- added for 2444657
2933: UPDATE psp_enc_summary_lines
2934: SET group_id = l_group_id
2935: WHERE status_code = 'N'
2936: AND gl_code_combination_id is NOT NULL
2937: AND enc_control_id = enc_control_rec.enc_control_id;

Line 3061: from psp_enc_summary_lines

3057: gl_tie_tab(l_rec_no).r_end_date := l_period_end_dt;
3058: gl_tie_tab(l_rec_no).r_control_id := enc_control_rec.enc_control_id;
3059: select group_id
3060: into gl_tie_tab(l_rec_no).r_group_id
3061: from psp_enc_summary_lines
3062: where status_code = 'N'
3063: and gl_code_combination_id is not null
3064: and rownum = 1;
3065: end if; --- 2444657

Line 3128: from psp_enc_summary_lines

3124: /***** Converted the following UPDATE TO BULK for R12 performance fixes (bug 4507892)
3125: update psp_enc_controls
3126: set gl_phase = 'Transfer'
3127: where enc_control_id in (select distinct enc_control_id
3128: from psp_enc_summary_lines
3129: where group_id = l_group_id);
3130: End of comment for bug fix 4507892 *****/
3131: -- Introduced the following for bug fix 4507892
3132: OPEN enc_control_id_cur;

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

3147:
3148: --insert into psp_stout values(5, ' request submitted ');
3149: COMMIT;
3150:
3151: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3152: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3153: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3154:
3155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');

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

3148: --insert into psp_stout values(5, ' request submitted ');
3149: COMMIT;
3150:
3151: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3152: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3153: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3154:
3155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3156:

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

3149: COMMIT;
3150:
3151: -- fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3152: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3153: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3154:
3155: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3156:
3157: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0,

Line 3403: FROM psp_enc_summary_lines

3399: SELECT enc_summary_line_id,
3400: enc_control_id,
3401: dr_cr_flag,
3402: summary_amount
3403: FROM psp_enc_summary_lines
3404: WHERE group_id = p_group_id;
3405: -- and enc_control_id = p_enc_control_id; Commented for bug fix 4625734
3406:
3407: CURSOR gl_tie_back_reject_cur IS

Line 3416: FROM psp_enc_summary_lines pesl

3412: AND set_of_books_id = p_set_of_books_id
3413: AND group_id = p_group_id;
3414: /***** Commented for bug fix 4625734
3415: AND reference6 IN (SELECT 'E:' || enc_summary_line_id -- Introduced for bug fix 3953230
3416: FROM psp_enc_summary_lines pesl
3417: WHERE pesl.enc_control_id = p_enc_control_id);
3418: End of comment for bug fix 4625734 *****/
3419:
3420: /***** Commented for Enh. 2768298 Removal of suspense posting in Liquidation

Line 3427: FROM psp_enc_summary_lines pel

3423: pel.effective_date,
3424: --pel.attribute30
3425: pel.suspense_org_account_id,
3426: pel.superceded_line_id
3427: FROM psp_enc_summary_lines pel
3428: WHERE pel.enc_summary_line_id = p_enc_line_id
3429: and pel.enc_control_id=p_enc_control_id
3430: and pel.status_code='N';
3431:

Line 3448: psp_enc_summary_lines pel

3444: CURSOR get_org_id_cur(P_LINE_ID IN NUMBER) IS
3445: SELECT hou.organization_id, hou.name
3446: FROM hr_all_organization_units hou,
3447: per_assignments_f paf,
3448: psp_enc_summary_lines pel
3449: WHERE pel.enc_summary_line_id = p_line_id
3450: AND pel.enc_control_id=p_enc_control_id
3451: -- AND pel.assignment_id = paf.assignment_id
3452: AND pel.person_id= paf.person_id

Line 3477: psp_enc_summary_lines pel

3473: pel.attribute30,
3474: pel.gl_code_combination_id
3475: FROM hr_all_organization_units hou,
3476: per_assignments_f paf,
3477: psp_enc_summary_lines pel
3478: WHERE pel.enc_control_id = p_enc_control_id
3479: AND pel.gl_project_flag = 'G'
3480: AND pel.status_code = 'A'
3481: AND pel.assignment_id = paf.assignment_id(+)

Line 3607: FROM psp_enc_summary_lines

3603: r_superceded_lines r_superceded_line_rec;
3604:
3605: CURSOR superceded_line_id_cur IS
3606: SELECT superceded_line_id
3607: FROM psp_enc_summary_lines
3608: WHERE group_id = p_group_id;
3609:
3610: CURSOR enc_controls_cur IS
3611: SELECT DISTINCT enc_control_id

Line 3612: FROM psp_enc_summary_lines

3608: WHERE group_id = p_group_id;
3609:
3610: CURSOR enc_controls_cur IS
3611: SELECT DISTINCT enc_control_id
3612: FROM psp_enc_summary_lines
3613: WHERE group_id = p_group_id;
3614: -- End of changes for bug fix 4625734
3615: begin
3616: /***** Changed the following SELECT into CURSOR for bug fix 4625734

Line 3658: UPDATE psp_enc_summary_lines

3654: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;
3655: CLOSE superceded_line_id_cur;
3656:
3657: FORALL recno IN 1..r_superceded_lines.superceded_line_id.COUNT
3658: UPDATE psp_enc_summary_lines
3659: SET status_code = 'A'
3660: WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(recno);
3661:
3662: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to A for original summary lines');

Line 3673: delete from psp_enc_summary_lines

3669:
3670: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_enc_controls.enc_control_id.COUNT: ' || r_enc_controls.enc_control_id.COUNT);
3671: -- End of changes for bug fix 4625734
3672:
3673: delete from psp_enc_summary_lines
3674: where group_id = p_group_id;
3675: -- and enc_control_id = p_enc_control_id; Commented for bug fix 4625734
3676:
3677: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');

Line 3677: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');

3673: delete from psp_enc_summary_lines
3674: where group_id = p_group_id;
3675: -- and enc_control_id = p_enc_control_id; Commented for bug fix 4625734
3676:
3677: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
3678:
3679: -- Introduced the following for bug fix 4625734
3680: FORALL recno IN 1..r_enc_controls.enc_control_id.COUNT
3681: UPDATE psp_enc_controls pec

Line 3741: UPDATE psp_enc_summary_lines

3737: end if;
3738:
3739:
3740: FORALL recno IN 1..r_interface.status.COUNT
3741: UPDATE psp_enc_summary_lines
3742: SET interface_status = r_interface.status(recno)
3743: -- status_code = 'R'
3744: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3745: AND r_interface.status(recno) <> 'P'

Line 3747: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_enc_summary_lines setting interface_status');

3743: -- status_code = 'R'
3744: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3745: AND r_interface.status(recno) <> 'P'
3746: AND SUBSTR(r_interface.status(recno), 1, 1) <> 'W';
3747: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated psp_enc_summary_lines setting interface_status');
3748:
3749: IF (SQL%ROWCOUNT > 0) THEN
3750: g_liq_has_failed_transactions := TRUE;
3751: g_rejected_group_id := p_group_id;

Line 3762: FROM psp_enc_summary_lines pesl

3758: FORALL recno IN 1..r_interface.status.COUNT
3759: UPDATE psp_enc_controls
3760: SET gl_phase = 'TieBack'
3761: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3762: FROM psp_enc_summary_lines pesl
3763: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3764:
3765: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_controls resetting gl_phase to TieBack SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3766:

Line 3771: FROM psp_enc_summary_lines pesl

3767: FORALL recno IN 1..r_interface.status.COUNT
3768: UPDATE psp_enc_controls
3769: SET gl_phase = 'Summarize'
3770: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3771: FROM psp_enc_summary_lines pesl
3772: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3773: AND pesl.status_code = 'N');
3774:
3775: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_controls resetting gl_phase to Summarize SQL%ROWCOUNT: ' || SQL%ROWCOUNT);

Line 3790: UPDATE psp_enc_summary_lines

3786:
3787: 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);
3788:
3789: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3790: UPDATE psp_enc_summary_lines
3791: SET status_code = 'L'
3792: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3793: AND status_code = 'N';
3794:

Line 3795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_summary_lines setting status_code to ''L'' SQL%ROWCOUNT ' || SQL%ROWCOUNT);

3791: SET status_code = 'L'
3792: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3793: AND status_code = 'N';
3794:
3795: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_summary_lines setting status_code to ''L'' SQL%ROWCOUNT ' || SQL%ROWCOUNT);
3796:
3797:
3798: IF (g_person_id IS NOT NULL) THEN
3799: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT

Line 3803: FROM psp_enc_summary_lines pesl2

3799: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3800: UPDATE psp_enc_lines_history
3801: SET change_flag = 'L'
3802: WHERE enc_summary_line_id = (SELECT pesl2.superceded_line_id
3803: FROM psp_enc_summary_lines pesl2
3804: WHERE pesl2.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3805: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated respective lines in psp_enc_lines_history to ''L'' status SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3806: END IF;
3807:

Line 3833: UPDATE psp_enc_summary_lines

3829: l_reference6 := substr(l_enc_ref, 3);
3830:
3831: -- Introduced the following for Enh. 2768298 Removal of suspense posting in Enc. Liquidation
3832: IF (l_status = 'P' OR substr(l_status,1,1) = 'W') THEN
3833: UPDATE psp_enc_summary_lines
3834: SET status_code='N'
3835: WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
3836: g_gl_run := TRUE;
3837: ELSE

Line 3838: UPDATE psp_enc_summary_lines

3834: SET status_code='N'
3835: WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
3836: g_gl_run := TRUE;
3837: ELSE
3838: UPDATE psp_enc_summary_lines
3839: SET interface_status = l_status,
3840: status_code='R'
3841: WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
3842:

Line 3843: UPDATE psp_enc_summary_lines

3839: SET interface_status = l_status,
3840: status_code='R'
3841: WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
3842:
3843: UPDATE psp_enc_summary_lines
3844: SET status_code='A'
3845: WHERE enc_summary_line_id IN (SELECT superceded_line_id
3846: FROM psp_enc_summary_lines
3847: WHERE enc_summary_line_id = TO_NUMBER(l_reference6));

Line 3846: FROM psp_enc_summary_lines

3842:
3843: UPDATE psp_enc_summary_lines
3844: SET status_code='A'
3845: WHERE enc_summary_line_id IN (SELECT superceded_line_id
3846: FROM psp_enc_summary_lines
3847: WHERE enc_summary_line_id = TO_NUMBER(l_reference6));
3848: g_rejected_group_id := p_group_id; --- for 3477373
3849: END IF;
3850: -- End of changes for Enh. 2768298 Removal of suspense posting in Enc. Liquidation.

Line 3854: UPDATE psp_enc_summary_lines

3850: -- End of changes for Enh. 2768298 Removal of suspense posting in Enc. Liquidation.
3851:
3852: / ***** Commented the following for Enh. Removal of suspense posting in Liq.
3853: -- update enc_summary_lines with the reject status code
3854: UPDATE psp_enc_summary_lines
3855: SET interface_status = l_status
3856: --, status_code = 'R'
3857: WHERE enc_summary_line_id = to_number(l_reference6);
3858: / *

Line 3892: UPDATE psp_enc_summary_lines

3888:
3889: IF l_status = 'P' OR substr(l_status,1,1) = 'W' THEN
3890: -- insert into psp_stout values(29,'stauts in P or W ');
3891:
3892: UPDATE psp_enc_summary_lines
3893: -- SET status_code = 'A'
3894: -- set status_code='L'
3895: set status_code='N' ---- changed to NEW for 2479579
3896: WHERE rowid = l_rowid;

Line 3911: UPDATE psp_enc_summary_lines

3907: x_susp_failed_status := l_status;
3908: x_susp_failed_date := l_encumbrance_date;
3909: l_suspense_ac_failed := 'Y';
3910:
3911: UPDATE psp_enc_summary_lines
3912: SET reject_reason_code = l_status,
3913: -- status_code = 'A'
3914: status_code='R'
3915: WHERE rowid = l_rowid;

Line 3917: update psp_enc_summary_lines set status_code='A' where

3913: -- status_code = 'A'
3914: status_code='R'
3915: WHERE rowid = l_rowid;
3916:
3917: update psp_enc_summary_lines set status_code='A' where
3918: enc_summary_line_id in (select superceded_line_id from
3919: psp_Enc_summary_lines where rowid=l_rowid);
3920:
3921: ELSE

Line 3919: psp_Enc_summary_lines where rowid=l_rowid);

3915: WHERE rowid = l_rowid;
3916:
3917: update psp_enc_summary_lines set status_code='A' where
3918: enc_summary_line_id in (select superceded_line_id from
3919: psp_Enc_summary_lines where rowid=l_rowid);
3920:
3921: ELSE
3922: --insert_into_psp_stout( 'stick susp a/c ');
3923: l_susp_ac_found := 'TRUE';

Line 4036: UPDATE psp_enc_summary_lines

4032:
4033: END IF;
4034:
4035: -- assign the organization suspense account and gl status
4036: UPDATE psp_enc_summary_lines
4037: SET suspense_org_account_id = l_organization_account_id,
4038: reject_reason_code ='EL:'||l_status,
4039: gl_project_flag = l_gl_project_flag,
4040: gl_code_combination_id = decode(l_gl_project_flag, 'P', null, l_susp_glccid),

Line 4056: update psp_Enc_summary_lines set ------dr_cr_flag='D',

4052: -- insert into psp_stout values (99, 'project_id is '||l_project_id);
4053: -- insert into psp_stout values (99, 'award id is '||l_award_id);
4054: --- modified the update for 2530853, flipping the sign of amount if 'C'
4055: if l_gl_project_flag ='P' then
4056: update psp_Enc_summary_lines set ------dr_cr_flag='D',
4057: summary_amount= decode(dr_cr_flag,'C',-summary_amount,summary_amount)
4058: where rowid=l_rowid;
4059: end if;
4060:

Line 4062: UPDATE psp_enc_summary_lines

4058: where rowid=l_rowid;
4059: end if;
4060:
4061: / * ************************************************************************
4062: UPDATE psp_enc_summary_lines
4063: SET attribute30 = l_organization_account_id,
4064: reject_reason_code = 'EL:' ||l_status,
4065: gl_project_flag = l_gl_project_flag,
4066: effective_date = l_encumbrance_date,

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

4141: CLOSE gl_tie_back_success_cur;
4142: EXIT;
4143: END IF;
4144: -- insert into psp_stout values(11,'in tie back success cur -- PROBLEM');
4145: -- update records in psp_enc_summary_lines as 'A'
4146: UPDATE psp_enc_summary_lines
4147: SET status_code = 'L'
4148: WHERE enc_summary_line_id = l_enc_summary_line_id
4149: and status_code = 'N';

Line 4146: UPDATE psp_enc_summary_lines

4142: EXIT;
4143: END IF;
4144: -- insert into psp_stout values(11,'in tie back success cur -- PROBLEM');
4145: -- update records in psp_enc_summary_lines as 'A'
4146: UPDATE psp_enc_summary_lines
4147: SET status_code = 'L'
4148: WHERE enc_summary_line_id = l_enc_summary_line_id
4149: and status_code = 'N';
4150:

Line 4156: from psp_enc_summary_lines

4152: --- added following for 3477373
4153: update psp_enc_lines_history
4154: set change_flag = 'L'
4155: where enc_summary_line_id = ( select superceded_line_id
4156: from psp_enc_summary_lines
4157: where enc_summary_line_id = l_enc_summary_line_id);
4158: end if;
4159:
4160:

Line 4169: UPDATE psp_enc_summary_lines

4165: END IF;
4166:
4167: END LOOP;
4168: / *
4169: UPDATE psp_enc_summary_lines
4170: SET status_code = 'P'
4171: -- WHERE enc_summary_line_id = l_enc_summary_line_id
4172: WHERE enc_control_id = p_enc_control_id
4173: and group_id=p_group_id

Line 4414: -- This procedure liquidates all the lines from psp_enc_summary_lines

4410: END insert_into_gl_int;
4411: End of comment for bug fix 4625734 *****/
4412:
4413: -- ##########################################################################
4414: -- This procedure liquidates all the lines from psp_enc_summary_lines
4415: -- where gl_project_flag = 'P' by creating new lines
4416: -- in psp_enc_summary_lines
4417: -- ##########################################################################
4418: -- Introduced the following for bug fix 4625734

Line 4416: -- in psp_enc_summary_lines

4412:
4413: -- ##########################################################################
4414: -- This procedure liquidates all the lines from psp_enc_summary_lines
4415: -- where gl_project_flag = 'P' by creating new lines
4416: -- in psp_enc_summary_lines
4417: -- ##########################################################################
4418: -- Introduced the following for bug fix 4625734
4419: PROCEDURE create_gms_enc_liq_lines (p_payroll_id IN NUMBER,
4420: p_action_type IN VARCHAR2,

Line 4450: FROM psp_enc_summary_lines pesl

4446: DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
4447: DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
4448: DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
4449: DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
4450: FROM psp_enc_summary_lines pesl
4451: WHERE enc_control_id IN (SELECT pec.enc_control_id
4452: FROM psp_enc_controls pec
4453: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
4454: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 4496: FROM psp_enc_summary_lines pesl

4492: DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
4493: DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
4494: DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
4495: DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
4496: FROM psp_enc_summary_lines pesl
4497: WHERE enc_control_id IN (SELECT pec.enc_control_id
4498: FROM psp_enc_controls pec
4499: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
4500: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 4546: FROM psp_enc_summary_lines pesl

4542: DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
4543: DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
4544: DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
4545: DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
4546: FROM psp_enc_summary_lines pesl
4547: WHERE enc_control_id IN (SELECT pec.enc_control_id
4548: FROM psp_enc_controls pec
4549: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
4550: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 4597: FROM psp_enc_summary_lines pesl

4593: DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
4594: DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
4595: DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
4596: DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
4597: FROM psp_enc_summary_lines pesl
4598: WHERE enc_control_id IN (SELECT pec.enc_control_id
4599: FROM psp_enc_controls pec
4600: WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)
4601: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 4736: INSERT INTO psp_enc_summary_lines

4732:
4733: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_liq_lines.enc_summary_line_id.COUNT: ' || r_liq_lines.enc_summary_line_id.COUNT);
4734:
4735: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4736: INSERT INTO psp_enc_summary_lines
4737: (enc_summary_line_id, business_group_id, enc_control_id,
4738: time_period_id, person_id, assignment_id,
4739: effective_date, set_of_books_id, project_id,
4740: task_id, award_id, expenditure_organization_id,

Line 4750: VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,

4746: attribute6, attribute7, attribute8,
4747: attribute9, attribute10, liquidate_request_id,
4748: proposed_termination_date, last_update_date, last_updated_by,
4749: last_update_login, created_by, creation_date)
4750: VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,
4751: r_liq_lines.enc_control_id(recno), r_liq_lines.time_period_id(recno),
4752: r_liq_lines.person_id(recno), r_liq_lines.assignment_id(recno),
4753: r_liq_lines.effective_date(recno), r_liq_lines.set_of_books_id(recno),
4754: r_liq_lines.project_id(recno), r_liq_lines.task_id(recno),

Line 4769: UPDATE psp_enc_summary_lines

4765: g_request_id, g_actual_term_date,
4766: SYSDATE, l_last_updated_by, l_last_update_login, l_last_updated_by, SYSDATE);
4767:
4768: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4769: UPDATE psp_enc_summary_lines
4770: SET status_code = 'S'
4771: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
4772:
4773: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');

Line 4773: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');

4769: UPDATE psp_enc_summary_lines
4770: SET status_code = 'S'
4771: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
4772:
4773: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
4774:
4775: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4776: UPDATE psp_enc_controls
4777: SET gms_phase = 'Summarize'

Line 4847: FROM psp_enc_summary_lines pesl

4843: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
4844: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10,
4845: pesl.expenditure_item_id -- 4068182
4846: --- removed history table from the from clause.. performace 3953230
4847: FROM psp_enc_summary_lines pesl
4848: WHERE pesl.enc_control_id = p_enc_control_id
4849: --- changed pelh to pesl for performance ..3684930
4850: AND pesl.gl_project_flag = 'P'
4851: AND pesl.status_code = 'A'

Line 4975: update psp_enc_summary_lines set status_code='S'

4971: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4972: END IF;
4973:
4974: / * Flag the original line as Superceded* /
4975: update psp_enc_summary_lines set status_code='S'
4976: where enc_summary_line_id=enc_liq_rec.enc_summary_line_id;
4977: END LOOP;
4978:
4979: END LOOP;

Line 4994: -- This procedure transfers liquidated lines from psp_enc_summary_lines

4990: END create_gms_enc_liq_lines;
4991: End of comment for bug fix 4625734 *****/
4992:
4993: -- ##########################################################################
4994: -- This procedure transfers liquidated lines from psp_enc_summary_lines
4995: -- with gl_project_flag = 'P' to pa_transaction_interface
4996:
4997: -- This procedure transfers lines from PSP_ENC_SUMMARY_LINES into PA_TRANSACTION_INTERFACE,
4998: -- kicks off the TRANSACTION IMPORT program in GMS and sends ENC_CONTROL_ID, END_DATE for

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

4993: -- ##########################################################################
4994: -- This procedure transfers liquidated lines from psp_enc_summary_lines
4995: -- with gl_project_flag = 'P' to pa_transaction_interface
4996:
4997: -- This procedure transfers lines from PSP_ENC_SUMMARY_LINES into PA_TRANSACTION_INTERFACE,
4998: -- kicks off the TRANSACTION IMPORT program in GMS and sends ENC_CONTROL_ID, END_DATE for
4999: -- the relevant TIME_PERIOD_ID and GMS_BATCH_NAME into the tie back procedure
5000: -- ##########################################################################
5001:

Line 5060: FROM psp_enc_summary_lines pesl,

5056: ptp.end_date,
5057: pesl.effective_date,
5058: papf.employee_number,
5059: pesl.gms_batch_name --6146805
5060: FROM psp_enc_summary_lines pesl,
5061: hr_organization_units hou, -- Introduced the following tables as part of bug fix 4625734
5062: pa_projects_all ppa,
5063: pa_tasks_expend_v pt, -- Replaced pa_tasks with pa_tasks_expend_v for Bug : 16391366
5064: per_time_periods ptp,

Line 5144: FROM psp_enc_summary_lines

5140: r_enc_controls r_enc_control_rec;
5141:
5142: CURSOR enc_control_id_cur IS
5143: SELECT DISTINCT enc_control_id
5144: FROM psp_enc_summary_lines
5145: WHERE gms_batch_name = g_gms_batch_name;
5146: -- End of changes for bug fix 4507892
5147:
5148: -- Introduced the following for bug fix 4625734

Line 5156: FROM psp_enc_summary_lines pesl

5152: WHERE transaction_source = 'GOLDE';
5153:
5154: CURSOR gms_batch_name_cur IS
5155: SELECT DISTINCT gms_batch_name
5156: FROM psp_enc_summary_lines pesl
5157: WHERE pesl.payroll_action_id = p_payroll_action_id
5158: /*WHERE enc_control_id IN (SELECT pec.enc_control_id
5159: FROM psp_enc_controls pec
5160: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)

Line 5223: l_expenditure_item_id psp_enc_summary_lines.expenditure_item_id%TYPE; --Bug 6062628

5219: gms_batch_name t_number_15);
5220:
5221: r_interface t_interface;
5222:
5223: l_expenditure_item_id psp_enc_summary_lines.expenditure_item_id%TYPE; --Bug 6062628
5224:
5225: l_raise_error BOOLEAN;
5226: -- End of changes for bug fix 4625734
5227:

Line 5244: FROM psp_enc_summary_lines

5240: call_status_tab call_status_TYPE;
5241:
5242: CURSOR operating_unit_csr IS
5243: SELECT distinct org_id
5244: FROM psp_enc_summary_lines
5245: WHERE status_code = 'N'
5246: AND gl_code_combination_id IS NULL
5247: AND gms_batch_name IS NULL
5248: AND payroll_action_id = p_payroll_action_id;

Line 5334: UPDATE psp_enc_summary_lines

5330: End of Comment for Create and Update multi thread enh. *****/
5331:
5332: -- FORALL recno IN 1..r_enc_control.enc_control_id.COUNT
5333: FORALL I IN 1..org_id_tab.count
5334: UPDATE psp_enc_summary_lines
5335: SET gms_batch_name = gms_batch_name_tab(i)
5336: WHERE status_code = 'N'
5337: AND gl_code_combination_id is NULL
5338: AND superceded_line_id IS NOT NULL

Line 5350: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated batch_name in psp_enc_summary_lines for each org_id count: '||g_bulk_row_count);

5346: loop
5347: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5348: end loop;
5349:
5350: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' updated batch_name in psp_enc_summary_lines for each org_id count: '||g_bulk_row_count);
5351:
5352:
5353: OPEN int_cur;
5354: FETCH int_cur BULK COLLECT INTO r_interface.txn_interface_id, r_interface.enc_summary_line_id,

Line 5455: UPDATE psp_enc_summary_lines pesl

5451:
5452: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed computation of PA week ending date(s)');
5453:
5454: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
5455: UPDATE psp_enc_summary_lines pesl
5456: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
5457: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
5458: AND TRUNC(r_interface.effective_date(recno)) <> TRUNC(r_interface.gms_overriding_date(recno));
5459:

Line 5516: FROM psp_enc_summary_lines

5512: LOOP
5513:
5514: SELECT expenditure_item_id
5515: INTO l_expenditure_item_id
5516: FROM psp_enc_summary_lines
5517: WHERE enc_summary_line_id = r_interface.superceded_line_id(recno); --Bug 6062628
5518:
5519:
5520: GMS_REC.TXN_INTERFACE_ID := r_interface.txn_interface_id(recno);

Line 5567: UPDATE psp_enc_summary_lines

5563: FROM per_time_periods
5564: WHERE time_period_id = enc_control_rec.time_period_id;
5565:
5566: if enc_control_rec.gms_phase = 'Summarize' then --- 2444657
5567: UPDATE psp_enc_summary_lines
5568: SET gms_batch_name = g_gms_batch_name --- replaced with global for 3473294
5569: WHERE status_code = 'N'
5570: AND gl_code_combination_id is NULL
5571: AND enc_control_id = enc_control_rec.enc_control_id;

Line 5690: update psp_Enc_summary_lines set gms_posting_override_date=l_effective_date

5686:
5687: -- Replaced != by <> ib the foll. condn. for GSCC warning.
5688: if trunc(l_effective_date) <> trunc(int_rec.effective_date)
5689: then
5690: update psp_Enc_summary_lines set gms_posting_override_date=l_effective_date
5691: where enc_summary_line_id=int_rec.enc_summary_line_id;
5692: end if;
5693:
5694: l_exp_end_dt := pa_utils.getweekending(l_effective_date);

Line 5802: from psp_enc_summary_lines

5798: gms_tie_tab(l_rec_no).r_control_id := enc_control_rec.enc_control_id;
5799: gms_tie_tab(l_rec_no).r_end_date := l_period_end_dt;
5800: select gms_batch_name
5801: into gms_tie_tab(l_rec_no).r_gms_batch_name
5802: from psp_enc_summary_lines
5803: where enc_control_id = enc_control_rec.enc_control_id
5804: and status_code = 'N'
5805: and gl_code_combination_id is null
5806: and rownum = 1;

Line 5847: from psp_enc_summary_lines

5843: /***** Modified teh following update to BULK UPDATE for R12 performance fixes (bug 4507892)
5844: update psp_enc_controls
5845: set gms_phase = 'Transfer'
5846: where enc_control_id in (select distinct enc_control_id
5847: from psp_enc_summary_lines
5848: where gms_batch_name = g_gms_batch_name); --- replaced with global for 3473294
5849: End of comment for bug fix 4507892 *****/
5850: -- Introduced the following for bug fix 4507892
5851: OPEN enc_control_id_cur;

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

5873:
5874: -- set the context again to multiple
5875: mo_global.set_policy_context('M', null);
5876:
5877: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5878: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5879: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5880:
5881: FOR I IN 1..org_id_tab.count

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

5874: -- set the context again to multiple
5875: mo_global.set_policy_context('M', null);
5876:
5877: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5878: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5879: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5880:
5881: FOR I IN 1..org_id_tab.count
5882: LOOP

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

5875: mo_global.set_policy_context('M', null);
5876:
5877: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5878: --fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5879: --fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5880:
5881: FOR I IN 1..org_id_tab.count
5882: LOOP
5883:

Line 5963: UPDATE psp_enc_summary_lines pesl

5959: End of comment for bug fix 4625734 *****/
5960:
5961: -- Introduced the folowing for bug fix 4625734
5962: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5963: UPDATE psp_enc_summary_lines pesl
5964: SET (pesl.expenditure_id, pesl.expenditure_item_id, pesl.expenditure_ending_date,
5965: pesl.txn_interface_id, pesl.interface_id) =
5966: (SELECT ptxn.expenditure_id, ptxn.expenditure_item_id, ptxn.expenditure_ending_date,
5967: ptxn.txn_interface_id, ptxn.interface_id

Line 5981: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_summary_lines setting expenditure_item_id count'||g_bulk_row_count);

5977: loop
5978: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
5979: end loop;
5980:
5981: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated psp_enc_summary_lines setting expenditure_item_id count'||g_bulk_row_count);
5982:
5983:
5984: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5985: DELETE pa_transaction_interface_all

Line 6073: FROM psp_enc_summary_lines

6069: SELECT enc_control_id,
6070: enc_summary_line_id,
6071: dr_cr_flag,
6072: TO_NUMBER(DECODE(dr_cr_flag, 'C', -summary_amount, summary_amount)) summary_amount
6073: FROM psp_enc_summary_lines
6074: WHERE gms_batch_name = p_gms_batch_name;
6075: -- and enc_control_id = p_enc_control_id; Removed enc_control_id check as part of bug fix 4625734
6076:
6077:

Line 6087: FROM psp_enc_summary_lines pesl

6083: WHERE transaction_source = 'GOLDE'
6084: AND batch_name = p_gms_batch_name;
6085: /***** Commented the following condition as Tie Back is by per gms batch and not by enc_control_id (for bug fix 4625734)
6086: AND orig_transaction_reference IN (SELECT 'E:' || enc_summary_line_id -- Introduced for bug fix 3953230
6087: FROM psp_enc_summary_lines pesl
6088: WHERE pesl.enc_control_id = p_enc_control_id);
6089: End of comment for bug fix 4625734 *****/
6090:
6091:

Line 6099: FROM psp_enc_summary_lines pel

6095: pel.effective_date,
6096: -- pel.attribute30
6097: pel.suspense_org_account_id,
6098: pel.superceded_line_id
6099: FROM psp_enc_summary_lines pel
6100: WHERE pel.enc_summary_line_id = p_enc_line_id
6101: and pel.enc_control_id=p_enc_control_id
6102: and pel.gl_project_flag='P' and
6103: pel.status_code='N';

Line 6121: psp_enc_summary_lines pel

6117: CURSOR get_org_id_cur(P_LINE_ID IN NUMBER) IS
6118: SELECT hou.organization_id, hou.name
6119: FROM hr_all_organization_units hou,
6120: per_assignments_f paf,
6121: psp_enc_summary_lines pel
6122: WHERE pel.enc_summary_line_id = p_line_id
6123: AND pel.enc_control_id=p_enc_control_id
6124: -- AND pel.assignment_id = paf.assignment_id
6125: and pel.person_id=paf.person_id

Line 6148: psp_enc_summary_lines pel

6144: pel.effective_date,
6145: pel.attribute30
6146: FROM hr_all_organization_units hou,
6147: per_assignments_f paf,
6148: psp_enc_summary_lines pel
6149: WHERE pel.enc_control_id = p_enc_control_id
6150: AND pel.gl_project_flag = 'P'
6151: AND pel.status_code = 'A'
6152: AND pel.assignment_id = paf.assignment_id(+)

Line 6260: FROM psp_enc_summary_lines

6256: SELECT enc_control_id,
6257: enc_summary_line_id,
6258: dr_cr_flag,
6259: TO_NUMBER(DECODE(dr_cr_flag, 'C', -summary_amount, summary_amount)) summary_amount
6260: FROM psp_enc_summary_lines
6261: WHERE gms_batch_name = p_gms_batch_name
6262: AND status_code = 'L';
6263:
6264: TYPE t_number_15 IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;

Line 6291: FROM psp_enc_summary_lines

6287: r_superceded_lines r_superceded_line_rec;
6288:
6289: CURSOR superceded_line_id_cur IS
6290: SELECT superceded_line_id
6291: FROM psp_enc_summary_lines
6292: WHERE enc_summary_line_id in
6293: (
6294: select to_number(substr(ORIG_TRANSACTION_REFERENCE,3))
6295: from pa_transaction_interface_all

Line 6310: FROM psp_enc_summary_lines

6306: r_enc_controls r_enc_control_rec;
6307:
6308: CURSOR enc_controls_cur IS
6309: SELECT DISTINCT enc_control_id
6310: FROM psp_enc_summary_lines
6311: WHERE enc_summary_line_id in
6312: (
6313: select to_number(substr(ORIG_TRANSACTION_REFERENCE,3))
6314: from pa_transaction_interface_all

Line 6338: from psp_enc_summary_lines

6334: into l_cnt, l_status
6335: from pa_transaction_interface_all
6336: where transaction_source = 'GOLDE'
6337: and batch_name = (select distinct gms_batch_name
6338: from psp_enc_summary_lines
6339: where enc_control_id = p_enc_control_id
6340: and gms_batch_name is not null)
6341: and transaction_status_code in ('P', 'I')
6342: group by transaction_status_code ;

Line 6389: update psp_enc_summary_lines set status_code ='A' where

6385: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all sql%rowcount'||sql%rowcount);
6386: ***/
6387:
6388: /***** Converted the following UPDATE to BULK for R12 performance fixes (bug 4507892)
6389: update psp_enc_summary_lines set status_code ='A' where
6390: enc_summary_line_id in (select superceded_line_id from
6391: psp_enc_summary_lines where gms_batch_name=p_gms_batch_name);
6392: End of comment for bug fix 4507892 *****/
6393: -- Introduced the following fo bug fix 4507892

Line 6391: psp_enc_summary_lines where gms_batch_name=p_gms_batch_name);

6387:
6388: /***** Converted the following UPDATE to BULK for R12 performance fixes (bug 4507892)
6389: update psp_enc_summary_lines set status_code ='A' where
6390: enc_summary_line_id in (select superceded_line_id from
6391: psp_enc_summary_lines where gms_batch_name=p_gms_batch_name);
6392: End of comment for bug fix 4507892 *****/
6393: -- Introduced the following fo bug fix 4507892
6394: OPEN superceded_line_id_cur;
6395: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;

Line 6401: UPDATE psp_enc_summary_lines

6397:
6398: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_superceded_lines.superceded_line_id.COUNT: ' || r_superceded_lines.superceded_line_id.COUNT);
6399:
6400: FORALL I IN 1..r_superceded_lines.superceded_line_id.COUNT
6401: UPDATE psp_enc_summary_lines
6402: SET status_code = 'A'
6403: WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(I);
6404:
6405: g_bulk_row_count :=0;

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

6407: loop
6408: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6409: end loop;
6410:
6411: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated original lines to status_code ''A'' in psp_enc_summary_lines g_bulk_row_count'||g_bulk_row_count);
6412:
6413: r_superceded_lines.superceded_line_id.DELETE;
6414: -- End of changes for bug fix 4507892
6415:

Line 6424: delete from psp_enc_summary_lines

6420: -- End of changes for bug fix 4625734
6421:
6422: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' r_enc_controls.enc_control_id.COUNT: ' || r_enc_controls.enc_control_id.COUNT);
6423:
6424: delete from psp_enc_summary_lines
6425: where enc_summary_line_id in
6426: (
6427: select to_number(substr(ORIG_TRANSACTION_REFERENCE,3))
6428: from pa_transaction_interface_all

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

6432: AND gms_batch_name = p_gms_batch_name;
6433: -- Bug 13036705
6434: -- and enc_control_id = p_enc_control_id;
6435:
6436: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines sql%rowcount'||sql%rowcount);
6437:
6438: -- Introduced the folowing for bug fix 4625734
6439: FORALL recno IN 1..r_enc_controls.enc_control_id.COUNT
6440: UPDATE psp_enc_controls pec

Line 6534: UPDATE psp_enc_summary_lines

6530: END IF;
6531: END LOOP;
6532:
6533: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6534: UPDATE psp_enc_summary_lines
6535: SET interface_status = r_reject_recs.reason_code(recno)
6536: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6537: AND r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR');
6538:

Line 6545: 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);

6541: loop
6542: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6543: end loop;
6544:
6545: 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);
6546:
6547: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6548: UPDATE psp_enc_summary_lines
6549: SET interface_status = r_reject_recs.reason_code(recno),

Line 6548: UPDATE psp_enc_summary_lines

6544:
6545: 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);
6546:
6547: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6548: UPDATE psp_enc_summary_lines
6549: SET interface_status = r_reject_recs.reason_code(recno),
6550: status_code = 'L'
6551: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6552: AND r_reject_recs.txn_status_code(recno) = 'A';

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

6556: FOR bulk_idx IN 1..r_reject_recs.enc_summary_line_id.COUNT
6557: loop
6558: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6559: end loop;
6560: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' for accepted records in psp_enc_summary_lines count:'||g_bulk_row_count);
6561:
6562: IF (g_person_id IS NOT NULL) THEN
6563: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6564: UPDATE psp_enc_lines_history

Line 6567: FROM psp_enc_summary_lines pesl

6563: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6564: UPDATE psp_enc_lines_history
6565: SET change_flag = 'L'
6566: WHERE enc_summary_line_id IN (SELECT superceded_line_id
6567: FROM psp_enc_summary_lines pesl
6568: WHERE pesl.enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno))
6569: AND r_reject_recs.txn_status_code(recno) = 'A';
6570:
6571:

Line 6630: UPDATE psp_enc_summary_lines

6626:
6627: -- update summary_lines with the reject status code
6628: IF l_trx_status_code in ('R', 'PI', 'PO', 'PR') THEN
6629:
6630: UPDATE psp_enc_summary_lines
6631: SET interface_status = l_trx_reject_code
6632: -- , status_code = 'R'
6633: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
6634:

Line 6646: UPDATE psp_enc_summary_lines

6642: end if;
6643: * /
6644: ELSIF l_trx_status_code = 'A' THEN
6645:
6646: UPDATE psp_enc_summary_lines
6647: SET interface_status = l_trx_reject_code, status_code = 'L'
6648: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
6649: -- and enc_control_id=p_enc_control_id;
6650:

Line 6656: from psp_enc_summary_lines

6652: if g_person_id is not null then
6653: update psp_enc_lines_history
6654: set change_flag = 'L'
6655: where enc_summary_line_id = ( select superceded_line_id
6656: from psp_enc_summary_lines
6657: where enc_summary_line_id = to_number(l_orig_trx_reference));
6658: end if;
6659:
6660:

Line 6687: FROM psp_enc_summary_lines

6683: -- delete the rejected batch records from gms_interface
6684:
6685: SELECT summary_amount, dr_cr_flag
6686: INTO l_summary_amount, l_dr_cr_flag
6687: FROM psp_enc_summary_lines
6688: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
6689:
6690: IF l_dr_cr_flag = 'D' THEN
6691: l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;

Line 6711: UPDATE psp_enc_summary_lines

6707: -- IF l_trx_reject_code = 'P' THEN
6708:
6709: IF l_trx_status_code = 'A' THEN
6710:
6711: UPDATE psp_enc_summary_lines
6712: -- SET status_code = 'P'
6713: set status_code='L'
6714: WHERE rowid = l_rowid;
6715:

Line 6729: UPDATE psp_enc_summary_lines

6725: x_susp_failed_reject_code := l_trx_reject_code;
6726: x_susp_failed_date := l_encumbrance_date;
6727: l_suspense_ac_failed := 'Y';
6728:
6729: UPDATE psp_enc_summary_lines
6730: SET reject_reason_code = 'EL:' || l_trx_reject_code,
6731: -- status_code = 'A'
6732: status_code = 'R'
6733: WHERE rowid = l_rowid;

Line 6735: update psp_enc_summary_lines set status_code='A' where

6731: -- status_code = 'A'
6732: status_code = 'R'
6733: WHERE rowid = l_rowid;
6734:
6735: update psp_enc_summary_lines set status_code='A' where
6736: enc_summary_line_id in (select superceded_line_id from
6737: psp_Enc_summary_lines where rowid=l_rowid);
6738:
6739: ELSE

Line 6737: psp_Enc_summary_lines where rowid=l_rowid);

6733: WHERE rowid = l_rowid;
6734:
6735: update psp_enc_summary_lines set status_code='A' where
6736: enc_summary_line_id in (select superceded_line_id from
6737: psp_Enc_summary_lines where rowid=l_rowid);
6738:
6739: ELSE
6740: l_susp_ac_found := 'TRUE';
6741: OPEN get_org_id_cur(to_number(l_orig_trx_reference));

Line 6838: UPDATE psp_enc_summary_lines

6834:
6835: -- assign the organization suspense account and gl status
6836: --dbms_output.put_line('Updating enc_lines ....NULL..');
6837:
6838: UPDATE psp_enc_summary_lines
6839: SET suspense_org_account_id = l_organization_account_id,
6840: reject_reason_code ='EL:'||l_trx_status_code,
6841: gl_project_flag = l_gl_project_flag,
6842: gl_code_combination_id = decode(l_gl_project_flag, 'P', null, l_gl_code_combination_id ),

Line 6858: update psp_Enc_summary_lines set ----- dr_cr_flag='C',

6854: -- insert into psp_stout values (99, 'project_id is '||l_project_id);
6855: -- insert into psp_stout values (99, 'award id is '||l_award_id);
6856: --- modified the update for 2530853, flipping the sign of amount if 'C'
6857: if l_gl_project_flag ='G' then
6858: update psp_Enc_summary_lines set ----- dr_cr_flag='C',
6859: summary_amount=decode(dr_cr_flag,'C', -summary_amount,summary_amount)
6860: where rowid=l_rowid;
6861: end if;
6862:

Line 6864: UPDATE psp_enc_summary_lines

6860: where rowid=l_rowid;
6861: end if;
6862:
6863: / *
6864: UPDATE psp_enc_summary_lines
6865: SET attribute30 = l_organization_account_id,
6866: reject_reason_code = 'EL:' || l_trx_reject_code,
6867: gl_project_flag = l_gl_project_flag,
6868: effective_date = l_encumbrance_date,

Line 6929: UPDATE psp_enc_summary_lines

6925:
6926: 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);
6927:
6928: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6929: UPDATE psp_enc_summary_lines
6930: SET status_code = 'L'
6931: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6932:
6933:

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

6937: g_bulk_row_count := g_bulk_row_count + SQL%BULK_ROWCOUNT(bulk_idx);
6938: end loop;
6939:
6940:
6941: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' in psp_enc_summary_lines '||g_bulk_row_count);
6942:
6943: IF (g_person_id IS NOT NULL) THEN
6944: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6945: UPDATE psp_enc_lines_history

Line 6948: FROM psp_enc_summary_lines pesl

6944: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6945: UPDATE psp_enc_lines_history
6946: SET change_flag = 'L'
6947: WHERE enc_summary_line_id IN (SELECT superceded_line_id
6948: FROM psp_enc_summary_lines pesl
6949: WHERE pesl.enc_summary_line_id = r_success_recs.enc_summary_line_id(recno));
6950:
6951: g_bulk_row_count :=0;
6952: FOR bulk_idx IN 1..r_success_recs.enc_summary_line_id.COUNT

Line 6991: UPDATE psp_enc_summary_lines

6987: CLOSE gms_tie_back_success_cur;
6988: EXIT;
6989: END IF;
6990: -- update records in psp_summary_lines as 'A'
6991: UPDATE psp_enc_summary_lines
6992: SET status_code = 'L'
6993: WHERE enc_summary_line_id = l_enc_summary_line_id
6994: and status_code = 'N';
6995:

Line 7001: from psp_enc_summary_lines

6997: if g_person_id is not null then
6998: update psp_enc_lines_history
6999: set change_flag = 'L'
7000: where enc_summary_line_id = ( select superceded_line_id
7001: from psp_enc_summary_lines
7002: where enc_summary_line_id = l_enc_summary_line_id);
7003: end if;
7004:
7005:

Line 7030: UPDATE psp_enc_summary_lines

7026: END IF;
7027:
7028: END LOOP;
7029: / *
7030: UPDATE psp_enc_summary_lines
7031: SET status_code = 'P'
7032: where enc_control_id = p_enc_control_id
7033: and gl_project_flag = 'P'
7034: and gms_batch_name=p_gms_batch_name

Line 7251: ---- there is no index on person/assignment on psp_enc_summary_lines

7247: p_business_group_id in number,
7248: p_set_of_books_id in number,
7249: p_person_id in number,
7250: p_actual_term_date in date) is
7251: ---- there is no index on person/assignment on psp_enc_summary_lines
7252: --- psp_enc_lines_history has index on assignment
7253: cursor get_enc_hist_lines_cur is
7254: select ESL.payroll_id payroll_id,
7255: min(ESL.time_period_id) time_period_id

Line 7257: psp_enc_summary_lines ESL,

7253: cursor get_enc_hist_lines_cur is
7254: select ESL.payroll_id payroll_id,
7255: min(ESL.time_period_id) time_period_id
7256: from psp_enc_lines_history ELH,
7257: psp_enc_summary_lines ESL,
7258: per_all_assignments_f ASG
7259: where ASG.person_id = p_person_id
7260: and ASG.assignment_id = ELH.assignment_id
7261: and ELH.enc_summary_line_id = ESL.enc_summary_line_id