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 735: FROM psp_enc_summary_lines pesl,

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

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

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

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

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

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

1223: -- if the program is completed with a return code of success and if the
1224: -- return code is failed it updates ACTION_CODE = 'P'
1225:
1226: -- When the program returns a failure status, it also updates
1227: -- PSP_ENC_SUMMARY_LINES with STATUS_CODE = 'R'
1228:
1229: -- ##########################################################################
1230: PROCEDURE enc_batch_end (p_payroll_action_id IN NUMBER,
1231: -- p_payroll_id IN NUMBER,

Line 1240: FROM psp_enc_summary_lines

1236: p_return_status OUT NOCOPY VARCHAR2) IS
1237: CURSOR enc_control_cur IS
1238: SELECT DISTINCT enc_control_id
1239: --FROM psp_enc_controls
1240: FROM psp_enc_summary_lines
1241: --WHERE payroll_id = p_payroll_id
1242: WHERE payroll_action_id = p_payroll_action_id
1243: --AND run_id = g_run_id
1244: AND superceded_line_id IS NOT NULL

Line 1302: FROM psp_enc_summary_lines pesl,

1298: DECODE(pesl.dr_cr_flag, 'D', 'Debit', 'Credit') dr_cr_flag,
1299: DECODE(pesl.status_code, 'A', 'Accepted', 'L', 'Liquidated', 'S', 'Superceded', 'N', 'New', 'R', 'Rejected', pesl.status_code) status_code,
1300: SUM(pesl.summary_amount),
1301: COUNT(1)
1302: FROM psp_enc_summary_lines pesl,
1303: psp_enc_controls pec,
1304: per_assignments_f paf,
1305: per_people_f ppf
1306: WHERE pesl.enc_control_id = pec.enc_control_id

Line 1373: FROM psp_enc_summary_lines pesl

1369: FORALL recno IN 1..t_enc_control_id.COUNT
1370: UPDATE psp_enc_lines_history pelh
1371: SET change_flag = 'U'
1372: WHERE EXISTS (SELECT 1
1373: FROM psp_enc_summary_lines pesl
1374: WHERE status_code = 'A'
1375: AND pesl.enc_control_id = t_enc_control_id(recno)
1376: AND EXISTS (SELECT 1
1377: FROM psp_enc_summary_lines pesl2

Line 1377: FROM psp_enc_summary_lines pesl2

1373: FROM psp_enc_summary_lines pesl
1374: WHERE status_code = 'A'
1375: AND pesl.enc_control_id = t_enc_control_id(recno)
1376: AND EXISTS (SELECT 1
1377: FROM psp_enc_summary_lines pesl2
1378: WHERE pesl2.status_code = 'L'
1379: AND pesl2.enc_control_id = t_enc_control_id(recno)
1380: AND pesl2.superceded_line_id = pesl.enc_summary_line_id))
1381: AND pelh.enc_control_id = t_enc_control_id(recno);

Line 1383: 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 SQL%ROWCOUNT: ' || SQL%ROWCOUNT);

1379: AND pesl2.enc_control_id = t_enc_control_id(recno)
1380: AND pesl2.superceded_line_id = pesl.enc_summary_line_id))
1381: AND pelh.enc_control_id = t_enc_control_id(recno);
1382:
1383: 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 SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
1384:
1385: FORALL recno IN 1..t_enc_control_id.COUNT
1386: UPDATE psp_enc_summary_lines pesl
1387: SET status_code = 'S'

Line 1386: UPDATE psp_enc_summary_lines pesl

1382:
1383: 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 SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
1384:
1385: FORALL recno IN 1..t_enc_control_id.COUNT
1386: UPDATE psp_enc_summary_lines pesl
1387: SET status_code = 'S'
1388: WHERE EXISTS (SELECT 1
1389: FROM psp_enc_summary_lines pesl3
1390: WHERE pesl3.status_code = 'L'

Line 1389: FROM psp_enc_summary_lines pesl3

1385: FORALL recno IN 1..t_enc_control_id.COUNT
1386: UPDATE psp_enc_summary_lines pesl
1387: SET status_code = 'S'
1388: WHERE EXISTS (SELECT 1
1389: FROM psp_enc_summary_lines pesl3
1390: WHERE pesl3.status_code = 'L'
1391: AND pesl3.enc_control_id = t_enc_control_id(recno)
1392: AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
1393: AND pesl.enc_control_id = t_enc_control_id(recno);

Line 1395: 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 SQL%ROWCOUNT: ' || SQL%ROWCOUNT);

1391: AND pesl3.enc_control_id = t_enc_control_id(recno)
1392: AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
1393: AND pesl.enc_control_id = t_enc_control_id(recno);
1394:
1395: 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 SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
1396:
1397: FORALL recno IN 1..t_enc_control_id.COUNT
1398: UPDATE psp_enc_summary_lines pesl
1399: SET status_code = 'A'

Line 1398: UPDATE psp_enc_summary_lines pesl

1394:
1395: 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 SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
1396:
1397: FORALL recno IN 1..t_enc_control_id.COUNT
1398: UPDATE psp_enc_summary_lines pesl
1399: SET status_code = 'A'
1400: WHERE status_code = 'S'
1401: AND pesl.enc_summary_line_id IN (SELECT pesl2.superceded_line_id
1402: FROM psp_enc_summary_lines pesl2

Line 1402: FROM psp_enc_summary_lines pesl2

1398: UPDATE psp_enc_summary_lines pesl
1399: SET status_code = 'A'
1400: WHERE status_code = 'S'
1401: AND pesl.enc_summary_line_id IN (SELECT pesl2.superceded_line_id
1402: FROM psp_enc_summary_lines pesl2
1403: WHERE pesl2.status_code = 'N'
1404: AND pesl2.enc_control_id = t_enc_control_id(recno))
1405: AND NOT EXISTS (SELECT 1
1406: FROM psp_enc_summary_lines pesl3

Line 1406: FROM psp_enc_summary_lines pesl3

1402: FROM psp_enc_summary_lines pesl2
1403: WHERE pesl2.status_code = 'N'
1404: AND pesl2.enc_control_id = t_enc_control_id(recno))
1405: AND NOT EXISTS (SELECT 1
1406: FROM psp_enc_summary_lines pesl3
1407: WHERE pesl3.status_code = 'L'
1408: AND pesl3.enc_control_id = t_enc_control_id(recno)
1409: AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
1410: AND pesl.enc_control_id = t_enc_control_id(recno);

Line 1412: 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, SQL%ROWCOUNT: ' || SQL%ROWCOUNT);

1408: AND pesl3.enc_control_id = t_enc_control_id(recno)
1409: AND pesl3.superceded_line_id = pesl.enc_summary_line_id)
1410: AND pesl.enc_control_id = t_enc_control_id(recno);
1411:
1412: 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, SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
1413:
1414: FORALL recno IN 1..t_enc_control_id.COUNT
1415: UPDATE psp_enc_lines_history pelh
1416: SET change_flag = 'L'

Line 1418: FROM psp_enc_summary_lines pesl

1414: FORALL recno IN 1..t_enc_control_id.COUNT
1415: UPDATE psp_enc_lines_history pelh
1416: SET change_flag = 'L'
1417: WHERE pelh.enc_summary_line_id IN (SELECT pesl.superceded_line_id
1418: FROM psp_enc_summary_lines pesl
1419: WHERE pesl.status_code = 'L'
1420: AND pesl.enc_control_id = t_enc_control_id(recno))
1421: AND pelh.enc_control_id = t_enc_control_id(recno);
1422:

Line 1430: FROM psp_enc_summary_lines pesl

1426: UPDATE psp_enc_controls
1427: SET action_code = 'L'
1428: WHERE enc_control_id = t_enc_control_id(recno)
1429: AND NOT EXISTS (SELECT 1
1430: FROM psp_enc_summary_lines pesl
1431: WHERE pesl.enc_control_id = t_enc_control_id(recno)
1432: AND status_code IN ('N','R','A'));
1433:
1434: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''L'' in psp_enc_controls for control records whose enc summary lines are completely liquidated');

Line 1441: FROM psp_enc_summary_lines pesl

1437: UPDATE psp_enc_controls
1438: SET action_code = 'P'
1439: WHERE enc_control_id = t_enc_control_id(recno)
1440: AND EXISTS (SELECT 1
1441: FROM psp_enc_summary_lines pesl
1442: WHERE pesl.enc_control_id = t_enc_control_id(recno)
1443: AND status_code = 'A');
1444:
1445: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated action_code to ''P'' in psp_enc_controls for control records whose enc summary lines aren''t completely liquidated');

Line 1453: FROM psp_enc_summary_lines pesl

1449: process_status = 'P'
1450: WHERE payroll_action_id = p_payroll_action_id
1451: AND process_code = 'ST'
1452: AND NOT EXISTS (SELECT 1
1453: FROM psp_enc_summary_lines pesl
1454: WHERE pesl.payroll_action_id = p_payroll_action_id
1455: AND pesl.status_code = 'N');
1456:
1457: IF (SQL%ROWCOUNT> 0) THEN

Line 1466: FROM psp_enc_summary_lines pesl

1462: SET process_phase = 'summarize_transfer'
1463: WHERE payroll_action_id = p_payroll_action_id
1464: AND process_code = 'ST'
1465: AND EXISTS (SELECT 1
1466: FROM psp_enc_summary_lines pesl
1467: WHERE pesl.payroll_action_id = p_payroll_action_id
1468: AND pesl.status_code = 'N'
1469: AND pesl.superceded_line_id IS NULL);
1470:

Line 1480: FROM psp_enc_summary_lines pesl

1476: SET process_phase = 'liquidate'
1477: WHERE payroll_action_id = p_payroll_action_id
1478: AND process_code = 'ST'
1479: AND EXISTS (SELECT 1
1480: FROM psp_enc_summary_lines pesl
1481: WHERE pesl.payroll_action_id = p_payroll_action_id
1482: AND pesl.status_code = 'N'
1483: AND pesl.superceded_line_id IS NOT NULL);
1484: 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 1519: FROM psp_enc_summary_lines pesl

1515: UPDATE psp_enc_process_assignments pepa
1516: SET assignment_status = 'P'
1517: WHERE payroll_action_id = p_payroll_action_id
1518: AND NOT EXISTS (SELECT 1
1519: FROM psp_enc_summary_lines pesl
1520: WHERE pesl.payroll_action_id = p_payroll_action_id
1521: AND pesl.assignment_id = pepa.assignment_id
1522: AND pesl.payroll_id = pepa.payroll_id
1523: AND pesl.status_code = 'N');

Line 1529: FROM psp_enc_summary_lines pesl

1525: UPDATE psp_enc_process_assignments pepa
1526: SET assignment_status = 'S'
1527: WHERE payroll_action_id = p_payroll_action_id
1528: AND EXISTS (SELECT 1
1529: FROM psp_enc_summary_lines pesl
1530: WHERE pesl.payroll_action_id = p_payroll_action_id
1531: AND pesl.assignment_id = pepa.assignment_id
1532: AND pesl.payroll_id = pepa.payroll_id
1533: AND pesl.status_code = 'N'

Line 1540: FROM psp_enc_summary_lines pesl

1536: UPDATE psp_enc_process_assignments pepa
1537: SET assignment_status = 'L'
1538: WHERE payroll_action_id = p_payroll_action_id
1539: AND EXISTS (SELECT 1
1540: FROM psp_enc_summary_lines pesl
1541: WHERE pesl.payroll_action_id = p_payroll_action_id
1542: AND pesl.assignment_id = pepa.assignment_id
1543: AND pesl.payroll_id = pepa.payroll_id
1544: AND pesl.status_code = 'N'

Line 1681: FROM psp_enc_summary_lines

1677: AND set_of_books_id = g_sob_id; * /
1678:
1679: CURSOR rej_enc_summary_lines_cur(P_ENC_CONTROL_ID IN NUMBER) IS
1680: SELECT status_code,superceded_line_id
1681: FROM psp_enc_summary_lines
1682: WHERE enc_control_id = p_enc_control_id
1683: AND status_code <> 'A';
1684:
1685: enc_control_rec enc_control_cur%ROWTYPE;

Line 1695: FROM psp_enc_summary_lines

1691:
1692: -- Included the following cursors for Enh. 2143723
1693: CURSOR summary_line_count_cur IS
1694: SELECT count(*)
1695: FROM psp_enc_summary_lines
1696: WHERE enc_control_id = enc_control_rec.enc_control_id
1697: AND status_code = 'A';
1698:
1699: CURSOR pending_enc_lines_cur Is

Line 1717: UPDATE psp_enc_summary_lines

1713: END IF;
1714:
1715: -- This part is used to mark the status_code of non-transferred summary lines to 'R'
1716:
1717: UPDATE psp_enc_summary_lines
1718: SET status_code = 'R'
1719: WHERE enc_control_id = enc_control_rec.enc_control_id
1720: AND status_code = 'N';
1721:

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

1733:
1734: IF l_status_code = 'R' THEN
1735: if l_sup_enc_summary_line_id <>0 then
1736:
1737: UPDATE psp_Enc_summary_lines S1 set S1.status_code='A' where
1738: S1.enc_summary_line_id=l_sup_enc_summary_line_id and
1739: S1.status_code='S' and not exists
1740: -- added code to ensure there is no L line before reverting S
1741: -- for 2479579

Line 1742: (select 1 from psp_enc_summary_lines S2

1738: S1.enc_summary_line_id=l_sup_enc_summary_line_id and
1739: S1.status_code='S' and not exists
1740: -- added code to ensure there is no L line before reverting S
1741: -- for 2479579
1742: (select 1 from psp_enc_summary_lines S2
1743: where S2.status_code = 'L' and S1.enc_control_id = S2.enc_control_id
1744: and S2.superceded_line_id = l_sup_enc_summary_line_id);
1745: / * mark the change_flag in psp_Enc_lineS_history as well * /
1746: -- Update the change_flag to 'U' as per HQ review comment for Enh. 2143723

Line 1759: DELETE FROM psp_enc_summary_lines

1755:
1756: end if;
1757: / * Start Bug#2142865 Added delete to delete rejected records * /
1758: / * reverted the delete for 2445196
1759: DELETE FROM psp_enc_summary_lines
1760: WHERE enc_control_id = enc_control_rec.enc_control_id
1761: AND status_code = 'R'; * /
1762: / * Commented for Restart Update/Quick Update Encumbrance Lines Enh.
1763: DELETE FROM psp_enc_summary_lines

Line 1763: DELETE FROM psp_enc_summary_lines

1759: DELETE FROM psp_enc_summary_lines
1760: WHERE enc_control_id = enc_control_rec.enc_control_id
1761: AND status_code = 'R'; * /
1762: / * Commented for Restart Update/Quick Update Encumbrance Lines Enh.
1763: DELETE FROM psp_enc_summary_lines
1764: WHERE enc_control_id = enc_control_rec.enc_control_id
1765: AND status_code = 'R';
1766:
1767: -- update status_code to 'P' in psp_payroll_controls

Line 1807: FROM psp_enc_summary_lines

1803:
1804: -- Moved the following SELECT into cursor summary_line_count_cur
1805: SELECT count(*)
1806: into l_line_count
1807: FROM psp_enc_summary_lines
1808: WHERE enc_control_id = enc_control_rec.enc_control_id
1809: and status_code = 'A';
1810: End of Enh. fix 2143723 * /
1811:

Line 1918: -- psp_enc_summary_lines and sending them to GL

1914: End of comment for bug fix 4625734 ****/
1915:
1916: -- ##########################################################################
1917: -- This procedure liquidates the summary lines by creating new lines in
1918: -- psp_enc_summary_lines and sending them to GL
1919:
1920: -- This procedure reverses the summary lines with STATUS_CODE = 'A' by creating
1921: -- new lines in PSP_ENC_SUMMARY_LINES
1922: -- ##########################################################################

Line 1921: -- new lines in PSP_ENC_SUMMARY_LINES

1917: -- This procedure liquidates the summary lines by creating new lines in
1918: -- psp_enc_summary_lines and sending them to GL
1919:
1920: -- This procedure reverses the summary lines with STATUS_CODE = 'A' by creating
1921: -- new lines in PSP_ENC_SUMMARY_LINES
1922: -- ##########################################################################
1923: -- Commented the existing create_gl_enc_liq_lines procedure and created new procedure that made use of BULK FETCH
1924: -- statements to improve on performance of the liquidation process.
1925: -- Introduced the foolowing for bug fix 4625734

Line 1952: FROM psp_enc_summary_lines pesl

1948: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
1949: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
1950: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
1951: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
1952: FROM psp_enc_summary_lines pesl
1953: WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
1954: FROM psp_enc_controls pec
1955: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
1956: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 1991: FROM psp_enc_summary_lines pesl

1987: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
1988: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
1989: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
1990: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
1991: FROM psp_enc_summary_lines pesl
1992: WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
1993: FROM psp_enc_controls pec
1994: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
1995: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 2034: FROM psp_enc_summary_lines pesl

2030: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
2031: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
2032: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
2033: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
2034: FROM psp_enc_summary_lines pesl
2035: WHERE pesl.enc_control_id IN (SELECT pec.enc_control_id
2036: FROM psp_enc_controls pec
2037: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
2038: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 2078: FROM psp_enc_summary_lines pesl

2074: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
2075: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
2076: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
2077: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
2078: FROM psp_enc_summary_lines pesl
2079: WHERE enc_control_id IN (SELECT pec.enc_control_id
2080: FROM psp_enc_controls pec
2081: WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)
2082: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 2194: INSERT INTO psp_enc_summary_lines

2190:
2191: 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);
2192:
2193: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2194: INSERT INTO psp_enc_summary_lines
2195: (enc_summary_line_id, business_group_id, enc_control_id,
2196: time_period_id, person_id, assignment_id,
2197: effective_date, set_of_books_id, gl_code_combination_id,
2198: summary_amount, dr_cr_flag, status_code,

Line 2206: VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,

2202: attribute6, attribute7, attribute8,
2203: attribute9, attribute10, liquidate_request_id,
2204: proposed_termination_date, last_update_date, last_updated_by,
2205: last_update_login, created_by, creation_date)
2206: VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,
2207: r_liq_lines.enc_control_id(recno), r_liq_lines.time_period_id(recno),
2208: r_liq_lines.person_id(recno), r_liq_lines.assignment_id(recno),
2209: r_liq_lines.effective_date(recno), r_liq_lines.set_of_books_id(recno),
2210: r_liq_lines.gl_code_combination_id(recno), r_liq_lines.summary_amount(recno),

Line 2223: UPDATE psp_enc_summary_lines

2219: g_request_id, g_actual_term_date,
2220: SYSDATE, l_last_updated_by, l_last_update_login, l_last_updated_by, SYSDATE);
2221:
2222: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2223: UPDATE psp_enc_summary_lines
2224: SET status_code = 'S'
2225: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
2226:
2227: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');

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

2223: UPDATE psp_enc_summary_lines
2224: SET status_code = 'S'
2225: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
2226:
2227: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
2228:
2229: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
2230: UPDATE psp_enc_controls
2231: SET gl_phase = 'Summarize'

Line 2295: FROM psp_enc_summary_lines pesl

2291: DECODE(g_dff_grouping_option, 'Y', pesl.attribute7, NULL) attribute7,
2292: DECODE(g_dff_grouping_option, 'Y', pesl.attribute8, NULL) attribute8,
2293: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
2294: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10
2295: FROM psp_enc_summary_lines pesl
2296: WHERE pesl.enc_control_id = p_enc_control_id
2297: --- changed pelh to pesl for performance ..3684930
2298: AND pesl.gl_project_flag = 'G'
2299: AND pesl.status_code = 'A'

Line 2405: update psp_enc_summary_lines set status_code='S'

2401: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2402: END IF;
2403:
2404: / * Flag the original line as Superceded* /
2405: update psp_enc_summary_lines set status_code='S'
2406: where enc_summary_line_id=enc_liq_rec.enc_summary_line_id;
2407:
2408:
2409:

Line 2440: -- This procedure inserts records into psp_enc_summary_lines

2436: End of comment for bug fix 4625734 *****/
2437:
2438: /***** Commented the following procedure as it is no longer used (for bug fix 4625734)
2439: -- ##########################################################################
2440: -- This procedure inserts records into psp_enc_summary_lines
2441: -- ##########################################################################
2442:
2443: PROCEDURE insert_into_enc_sum_lines(
2444: p_enc_summary_line_id OUT NOCOPY NUMBER,

Line 2483: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL

2479: p_expenditure_item_id IN NUMBER, -- bug 4068182
2480: p_return_status OUT NOCOPY VARCHAR2
2481: ) IS
2482: BEGIN
2483: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL
2484: INTO P_ENC_SUMMARY_LINE_ID
2485: FROM DUAL;
2486: INSERT INTO PSP_ENC_SUMMARY_LINES(
2487: ENC_SUMMARY_LINE_ID,

Line 2486: INSERT INTO PSP_ENC_SUMMARY_LINES(

2482: BEGIN
2483: SELECT PSP_ENC_SUMMARY_LINES_S.NEXTVAL
2484: INTO P_ENC_SUMMARY_LINE_ID
2485: FROM DUAL;
2486: INSERT INTO PSP_ENC_SUMMARY_LINES(
2487: ENC_SUMMARY_LINE_ID,
2488: BUSINESS_GROUP_ID,
2489: ENC_CONTROL_ID,
2490: TIME_PERIOD_ID,

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

2570: p_return_status := fnd_api.g_ret_sts_success;
2571:
2572: EXCEPTION
2573: WHEN OTHERS THEN
2574: --dbms_output.put_line('Insert into psp_enc_summary_lines failed');
2575: g_error_api_path := 'insert_into_enc_sum_lines:'||g_error_api_path;
2576: fnd_msg_pub.add_exc_msg('PSP_ENC_LIQ_TRAN','INSERT_INTO_ENC_SUM_LINES');
2577: p_return_status := fnd_api.g_ret_sts_unexp_error;
2578: END insert_into_enc_sum_lines;

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

2578: END insert_into_enc_sum_lines;
2579: End of comment for bug fix 4625734 *****/
2580:
2581: -- ##########################################################################
2582: -- This procedure transfers the liquidated lines from psp_enc_summary_lines
2583: -- with gl_project_flag = 'G' to gl_interface
2584:
2585: -- This procedure transfers the liquidated lines from PSP_ENC_SUMMARY_LINES table
2586: -- to GL_INTERFACE table and kicks off the JOURNAL IMPORT program in GL and sends

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

2581: -- ##########################################################################
2582: -- This procedure transfers the liquidated lines from psp_enc_summary_lines
2583: -- with gl_project_flag = 'G' to gl_interface
2584:
2585: -- This procedure transfers the liquidated lines from PSP_ENC_SUMMARY_LINES table
2586: -- to GL_INTERFACE table and kicks off the JOURNAL IMPORT program in GL and sends
2587: -- ENC_CONTROL_ID and END_DATE for the relevant TIME_PERIOD_ID
2588: -- and GROUP_ID into the tie back procedure
2589: -- ##########################################################################

Line 2650: FROM psp_enc_summary_lines pesl

2646: pesl.attribute27,
2647: pesl.attribute28,
2648: pesl.attribute29,
2649: pesl.attribute30
2650: FROM psp_enc_summary_lines pesl
2651: WHERE pesl.status_code = 'N'
2652: AND pesl.gl_code_combination_id is NOT NULL
2653: AND pesl.enc_control_id = l_enc_control_id;
2654: End of comment for bug fix 4625734 *****/

Line 2720: FROM psp_enc_summary_lines pesl

2716: l_created_by NUMBER(15);
2717:
2718: CURSOR gl_group_id_cur IS
2719: SELECT DISTINCT group_id
2720: FROM psp_enc_summary_lines pesl
2721: WHERE pesl.payroll_action_id = p_payroll_action_id
2722: /*WHERE enc_control_id IN (SELECT pec.enc_control_id
2723: FROM psp_enc_controls pec
2724: WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)

Line 2736: FROM psp_enc_summary_lines

2732: AND gl_code_combination_id IS NOT NULL;
2733:
2734: CURSOR enc_control_id_cur IS
2735: SELECT DISTINCT enc_control_id
2736: FROM psp_enc_summary_lines
2737: WHERE group_id = l_group_id;
2738: -- End of changes for bug fix 4507892
2739: BEGIN
2740: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Entering Transfer to GL Interface');

Line 2776: UPDATE psp_enc_summary_lines

2772: FROM dual;
2773:
2774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' l_group_id: ' || l_group_id);
2775:
2776: UPDATE psp_enc_summary_lines
2777: SET group_id = l_group_id
2778: WHERE status_code = 'N'
2779: AND gl_code_combination_id IS NOT NULL
2780: AND superceded_line_id IS NOT NULL

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

2789: AND pec.business_group_id = g_bg_id
2790: AND pec.set_of_books_id = g_sob_id
2791: AND pec.gl_phase = 'Summarize');*/
2792:
2793: 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');
2794:
2795:
2796: INSERT INTO gl_interface
2797: (status, set_of_books_id, accounting_date,

Line 2833: FROM psp_enc_summary_lines pesl,

2829: attribute21, attribute22, attribute23,
2830: attribute24, attribute25, attribute26,
2831: attribute27, attribute28, attribute29,
2832: attribute30
2833: FROM psp_enc_summary_lines pesl,
2834: psp_enc_controls pec
2835: WHERE pec.enc_control_id = pesl.enc_control_id
2836: AND pesl.status_code = 'N'
2837: AND pesl.gl_code_combination_id is NOT NULL

Line 2880: UPDATE psp_enc_summary_lines

2876: fnd_msg_pub.add;
2877: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2878: END;
2879: If enc_control_rec.gl_phase = 'Summarize' then --- added for 2444657
2880: UPDATE psp_enc_summary_lines
2881: SET group_id = l_group_id
2882: WHERE status_code = 'N'
2883: AND gl_code_combination_id is NOT NULL
2884: AND enc_control_id = enc_control_rec.enc_control_id;

Line 3008: from psp_enc_summary_lines

3004: gl_tie_tab(l_rec_no).r_end_date := l_period_end_dt;
3005: gl_tie_tab(l_rec_no).r_control_id := enc_control_rec.enc_control_id;
3006: select group_id
3007: into gl_tie_tab(l_rec_no).r_group_id
3008: from psp_enc_summary_lines
3009: where status_code = 'N'
3010: and gl_code_combination_id is not null
3011: and rownum = 1;
3012: end if; --- 2444657

Line 3075: from psp_enc_summary_lines

3071: /***** Converted the following UPDATE TO BULK for R12 performance fixes (bug 4507892)
3072: update psp_enc_controls
3073: set gl_phase = 'Transfer'
3074: where enc_control_id in (select distinct enc_control_id
3075: from psp_enc_summary_lines
3076: where group_id = l_group_id);
3077: End of comment for bug fix 4507892 *****/
3078: -- Introduced the following for bug fix 4507892
3079: OPEN enc_control_id_cur;

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

3094:
3095: --insert into psp_stout values(5, ' request submitted ');
3096: COMMIT;
3097:
3098: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3099: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3100: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3101:
3102: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');

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

3095: --insert into psp_stout values(5, ' request submitted ');
3096: COMMIT;
3097:
3098: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3099: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3100: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3101:
3102: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3103:

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

3096: COMMIT;
3097:
3098: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
3099: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
3100: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
3101:
3102: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Waiting for Journal Import request to complete');
3103:
3104: call_status := fnd_concurrent.wait_for_request(req_id, 10, 0,

Line 3346: FROM psp_enc_summary_lines

3342: SELECT enc_summary_line_id,
3343: enc_control_id,
3344: dr_cr_flag,
3345: summary_amount
3346: FROM psp_enc_summary_lines
3347: WHERE group_id = p_group_id;
3348: -- and enc_control_id = p_enc_control_id; Commented for bug fix 4625734
3349:
3350: CURSOR gl_tie_back_reject_cur IS

Line 3359: FROM psp_enc_summary_lines pesl

3355: AND set_of_books_id = p_set_of_books_id
3356: AND group_id = p_group_id;
3357: /***** Commented for bug fix 4625734
3358: AND reference6 IN (SELECT 'E:' || enc_summary_line_id -- Introduced for bug fix 3953230
3359: FROM psp_enc_summary_lines pesl
3360: WHERE pesl.enc_control_id = p_enc_control_id);
3361: End of comment for bug fix 4625734 *****/
3362:
3363: /***** Commented for Enh. 2768298 Removal of suspense posting in Liquidation

Line 3370: FROM psp_enc_summary_lines pel

3366: pel.effective_date,
3367: --pel.attribute30
3368: pel.suspense_org_account_id,
3369: pel.superceded_line_id
3370: FROM psp_enc_summary_lines pel
3371: WHERE pel.enc_summary_line_id = p_enc_line_id
3372: and pel.enc_control_id=p_enc_control_id
3373: and pel.status_code='N';
3374:

Line 3391: psp_enc_summary_lines pel

3387: CURSOR get_org_id_cur(P_LINE_ID IN NUMBER) IS
3388: SELECT hou.organization_id, hou.name
3389: FROM hr_all_organization_units hou,
3390: per_assignments_f paf,
3391: psp_enc_summary_lines pel
3392: WHERE pel.enc_summary_line_id = p_line_id
3393: AND pel.enc_control_id=p_enc_control_id
3394: -- AND pel.assignment_id = paf.assignment_id
3395: AND pel.person_id= paf.person_id

Line 3420: psp_enc_summary_lines pel

3416: pel.attribute30,
3417: pel.gl_code_combination_id
3418: FROM hr_all_organization_units hou,
3419: per_assignments_f paf,
3420: psp_enc_summary_lines pel
3421: WHERE pel.enc_control_id = p_enc_control_id
3422: AND pel.gl_project_flag = 'G'
3423: AND pel.status_code = 'A'
3424: AND pel.assignment_id = paf.assignment_id(+)

Line 3550: FROM psp_enc_summary_lines

3546: r_superceded_lines r_superceded_line_rec;
3547:
3548: CURSOR superceded_line_id_cur IS
3549: SELECT superceded_line_id
3550: FROM psp_enc_summary_lines
3551: WHERE group_id = p_group_id;
3552:
3553: CURSOR enc_controls_cur IS
3554: SELECT DISTINCT enc_control_id

Line 3555: FROM psp_enc_summary_lines

3551: WHERE group_id = p_group_id;
3552:
3553: CURSOR enc_controls_cur IS
3554: SELECT DISTINCT enc_control_id
3555: FROM psp_enc_summary_lines
3556: WHERE group_id = p_group_id;
3557: -- End of changes for bug fix 4625734
3558: begin
3559: /***** Changed the following SELECT into CURSOR for bug fix 4625734

Line 3601: UPDATE psp_enc_summary_lines

3597: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;
3598: CLOSE superceded_line_id_cur;
3599:
3600: FORALL recno IN 1..r_superceded_lines.superceded_line_id.COUNT
3601: UPDATE psp_enc_summary_lines
3602: SET status_code = 'A'
3603: WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(recno);
3604:
3605: r_superceded_lines.superceded_line_id.DELETE;

Line 3614: delete from psp_enc_summary_lines

3610:
3611: 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);
3612: -- End of changes for bug fix 4625734
3613:
3614: delete from psp_enc_summary_lines
3615: where group_id = p_group_id;
3616: -- and enc_control_id = p_enc_control_id; Commented for bug fix 4625734
3617:
3618: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');

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

3614: delete from psp_enc_summary_lines
3615: where group_id = p_group_id;
3616: -- and enc_control_id = p_enc_control_id; Commented for bug fix 4625734
3617:
3618: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
3619:
3620: -- Introduced the following for bug fix 4625734
3621: FORALL recno IN 1..r_enc_controls.enc_control_id.COUNT
3622: UPDATE psp_enc_controls pec

Line 3684: UPDATE psp_enc_summary_lines

3680: end if;
3681:
3682:
3683: FORALL recno IN 1..r_interface.status.COUNT
3684: UPDATE psp_enc_summary_lines
3685: SET interface_status = r_interface.status(recno)
3686: -- status_code = 'R'
3687: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3688: AND r_interface.status(recno) <> 'P'

Line 3704: FROM psp_enc_summary_lines pesl

3700: FORALL recno IN 1..r_interface.status.COUNT
3701: UPDATE psp_enc_controls
3702: SET gl_phase = 'TieBack'
3703: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3704: FROM psp_enc_summary_lines pesl
3705: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3706:
3707: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' TieBack SQL%ROWCOUNT: ' || SQL%ROWCOUNT);
3708:

Line 3713: FROM psp_enc_summary_lines pesl

3709: FORALL recno IN 1..r_interface.status.COUNT
3710: UPDATE psp_enc_controls
3711: SET gl_phase = 'Summarize'
3712: WHERE enc_control_id IN (SELECT pesl.enc_control_id
3713: FROM psp_enc_summary_lines pesl
3714: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3715: AND pesl.status_code = 'N');
3716:
3717: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Summarize SQL%ROWCOUNT: ' || SQL%ROWCOUNT);

Line 3730: UPDATE psp_enc_summary_lines

3726:
3727: 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);
3728:
3729: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3730: UPDATE psp_enc_summary_lines
3731: SET status_code = 'L'
3732: WHERE enc_summary_line_id = r_interface.enc_summary_line_id(recno)
3733: AND status_code = 'N';
3734:

Line 3740: FROM psp_enc_summary_lines pesl2

3736: FORALL recno IN 1..r_interface.enc_summary_line_id.COUNT
3737: UPDATE psp_enc_lines_history
3738: SET change_flag = 'L'
3739: WHERE enc_summary_line_id = (SELECT pesl2.superceded_line_id
3740: FROM psp_enc_summary_lines pesl2
3741: WHERE pesl2.enc_summary_line_id = r_interface.enc_summary_line_id(recno));
3742: 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);
3743: END IF;
3744:

Line 3770: UPDATE psp_enc_summary_lines

3766: l_reference6 := substr(l_enc_ref, 3);
3767:
3768: -- Introduced the following for Enh. 2768298 Removal of suspense posting in Enc. Liquidation
3769: IF (l_status = 'P' OR substr(l_status,1,1) = 'W') THEN
3770: UPDATE psp_enc_summary_lines
3771: SET status_code='N'
3772: WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
3773: g_gl_run := TRUE;
3774: ELSE

Line 3775: UPDATE psp_enc_summary_lines

3771: SET status_code='N'
3772: WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
3773: g_gl_run := TRUE;
3774: ELSE
3775: UPDATE psp_enc_summary_lines
3776: SET interface_status = l_status,
3777: status_code='R'
3778: WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
3779:

Line 3780: UPDATE psp_enc_summary_lines

3776: SET interface_status = l_status,
3777: status_code='R'
3778: WHERE enc_summary_line_id = TO_NUMBER(l_reference6);
3779:
3780: UPDATE psp_enc_summary_lines
3781: SET status_code='A'
3782: WHERE enc_summary_line_id IN (SELECT superceded_line_id
3783: FROM psp_enc_summary_lines
3784: WHERE enc_summary_line_id = TO_NUMBER(l_reference6));

Line 3783: FROM psp_enc_summary_lines

3779:
3780: UPDATE psp_enc_summary_lines
3781: SET status_code='A'
3782: WHERE enc_summary_line_id IN (SELECT superceded_line_id
3783: FROM psp_enc_summary_lines
3784: WHERE enc_summary_line_id = TO_NUMBER(l_reference6));
3785: g_rejected_group_id := p_group_id; --- for 3477373
3786: END IF;
3787: -- End of changes for Enh. 2768298 Removal of suspense posting in Enc. Liquidation.

Line 3791: UPDATE psp_enc_summary_lines

3787: -- End of changes for Enh. 2768298 Removal of suspense posting in Enc. Liquidation.
3788:
3789: / ***** Commented the following for Enh. Removal of suspense posting in Liq.
3790: -- update enc_summary_lines with the reject status code
3791: UPDATE psp_enc_summary_lines
3792: SET interface_status = l_status
3793: --, status_code = 'R'
3794: WHERE enc_summary_line_id = to_number(l_reference6);
3795: / *

Line 3829: UPDATE psp_enc_summary_lines

3825:
3826: IF l_status = 'P' OR substr(l_status,1,1) = 'W' THEN
3827: -- insert into psp_stout values(29,'stauts in P or W ');
3828:
3829: UPDATE psp_enc_summary_lines
3830: -- SET status_code = 'A'
3831: -- set status_code='L'
3832: set status_code='N' ---- changed to NEW for 2479579
3833: WHERE rowid = l_rowid;

Line 3848: UPDATE psp_enc_summary_lines

3844: x_susp_failed_status := l_status;
3845: x_susp_failed_date := l_encumbrance_date;
3846: l_suspense_ac_failed := 'Y';
3847:
3848: UPDATE psp_enc_summary_lines
3849: SET reject_reason_code = l_status,
3850: -- status_code = 'A'
3851: status_code='R'
3852: WHERE rowid = l_rowid;

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

3850: -- status_code = 'A'
3851: status_code='R'
3852: WHERE rowid = l_rowid;
3853:
3854: update psp_enc_summary_lines set status_code='A' where
3855: enc_summary_line_id in (select superceded_line_id from
3856: psp_Enc_summary_lines where rowid=l_rowid);
3857:
3858: ELSE

Line 3856: psp_Enc_summary_lines where rowid=l_rowid);

3852: WHERE rowid = l_rowid;
3853:
3854: update psp_enc_summary_lines set status_code='A' where
3855: enc_summary_line_id in (select superceded_line_id from
3856: psp_Enc_summary_lines where rowid=l_rowid);
3857:
3858: ELSE
3859: --insert_into_psp_stout( 'stick susp a/c ');
3860: l_susp_ac_found := 'TRUE';

Line 3973: UPDATE psp_enc_summary_lines

3969:
3970: END IF;
3971:
3972: -- assign the organization suspense account and gl status
3973: UPDATE psp_enc_summary_lines
3974: SET suspense_org_account_id = l_organization_account_id,
3975: reject_reason_code ='EL:'||l_status,
3976: gl_project_flag = l_gl_project_flag,
3977: gl_code_combination_id = decode(l_gl_project_flag, 'P', null, l_susp_glccid),

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

3989: -- insert into psp_stout values (99, 'project_id is '||l_project_id);
3990: -- insert into psp_stout values (99, 'award id is '||l_award_id);
3991: --- modified the update for 2530853, flipping the sign of amount if 'C'
3992: if l_gl_project_flag ='P' then
3993: update psp_Enc_summary_lines set ------dr_cr_flag='D',
3994: summary_amount= decode(dr_cr_flag,'C',-summary_amount,summary_amount)
3995: where rowid=l_rowid;
3996: end if;
3997:

Line 3999: UPDATE psp_enc_summary_lines

3995: where rowid=l_rowid;
3996: end if;
3997:
3998: / * ************************************************************************
3999: UPDATE psp_enc_summary_lines
4000: SET attribute30 = l_organization_account_id,
4001: reject_reason_code = 'EL:' ||l_status,
4002: gl_project_flag = l_gl_project_flag,
4003: effective_date = l_encumbrance_date,

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

4078: CLOSE gl_tie_back_success_cur;
4079: EXIT;
4080: END IF;
4081: -- insert into psp_stout values(11,'in tie back success cur -- PROBLEM');
4082: -- update records in psp_enc_summary_lines as 'A'
4083: UPDATE psp_enc_summary_lines
4084: SET status_code = 'L'
4085: WHERE enc_summary_line_id = l_enc_summary_line_id
4086: and status_code = 'N';

Line 4083: UPDATE psp_enc_summary_lines

4079: EXIT;
4080: END IF;
4081: -- insert into psp_stout values(11,'in tie back success cur -- PROBLEM');
4082: -- update records in psp_enc_summary_lines as 'A'
4083: UPDATE psp_enc_summary_lines
4084: SET status_code = 'L'
4085: WHERE enc_summary_line_id = l_enc_summary_line_id
4086: and status_code = 'N';
4087:

Line 4093: from psp_enc_summary_lines

4089: --- added following for 3477373
4090: update psp_enc_lines_history
4091: set change_flag = 'L'
4092: where enc_summary_line_id = ( select superceded_line_id
4093: from psp_enc_summary_lines
4094: where enc_summary_line_id = l_enc_summary_line_id);
4095: end if;
4096:
4097:

Line 4106: UPDATE psp_enc_summary_lines

4102: END IF;
4103:
4104: END LOOP;
4105: / *
4106: UPDATE psp_enc_summary_lines
4107: SET status_code = 'P'
4108: -- WHERE enc_summary_line_id = l_enc_summary_line_id
4109: WHERE enc_control_id = p_enc_control_id
4110: and group_id=p_group_id

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

4346: END insert_into_gl_int;
4347: End of comment for bug fix 4625734 *****/
4348:
4349: -- ##########################################################################
4350: -- This procedure liquidates all the lines from psp_enc_summary_lines
4351: -- where gl_project_flag = 'P' by creating new lines
4352: -- in psp_enc_summary_lines
4353: -- ##########################################################################
4354: -- Introduced the following for bug fix 4625734

Line 4352: -- in psp_enc_summary_lines

4348:
4349: -- ##########################################################################
4350: -- This procedure liquidates all the lines from psp_enc_summary_lines
4351: -- where gl_project_flag = 'P' by creating new lines
4352: -- in psp_enc_summary_lines
4353: -- ##########################################################################
4354: -- Introduced the following for bug fix 4625734
4355: PROCEDURE create_gms_enc_liq_lines (p_payroll_id IN NUMBER,
4356: p_action_type IN VARCHAR2,

Line 4386: FROM psp_enc_summary_lines pesl

4382: DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
4383: DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
4384: DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
4385: DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
4386: FROM psp_enc_summary_lines pesl
4387: WHERE enc_control_id IN (SELECT pec.enc_control_id
4388: FROM psp_enc_controls pec
4389: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
4390: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 4432: FROM psp_enc_summary_lines pesl

4428: DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
4429: DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
4430: DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
4431: DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
4432: FROM psp_enc_summary_lines pesl
4433: WHERE enc_control_id IN (SELECT pec.enc_control_id
4434: FROM psp_enc_controls pec
4435: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
4436: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 4482: FROM psp_enc_summary_lines pesl

4478: DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
4479: DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
4480: DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
4481: DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
4482: FROM psp_enc_summary_lines pesl
4483: WHERE enc_control_id IN (SELECT pec.enc_control_id
4484: FROM psp_enc_controls pec
4485: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)
4486: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 4533: FROM psp_enc_summary_lines pesl

4529: DECODE(g_dff_grouping_option, 'Y', attribute7, NULL) attribute7,
4530: DECODE(g_dff_grouping_option, 'Y', attribute8, NULL) attribute8,
4531: DECODE(g_dff_grouping_option, 'Y', attribute9, NULL) attribute9,
4532: DECODE(g_dff_grouping_option, 'Y', attribute10, NULL) attribute10
4533: FROM psp_enc_summary_lines pesl
4534: WHERE enc_control_id IN (SELECT pec.enc_control_id
4535: FROM psp_enc_controls pec
4536: WHERE pec.payroll_id = NVL(p_payroll_id, pec.payroll_id)
4537: AND (pec.total_dr_amount IS NOT NULL OR pec.total_cr_amount IS NOT NULL)

Line 4672: INSERT INTO psp_enc_summary_lines

4668:
4669: 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);
4670:
4671: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4672: INSERT INTO psp_enc_summary_lines
4673: (enc_summary_line_id, business_group_id, enc_control_id,
4674: time_period_id, person_id, assignment_id,
4675: effective_date, set_of_books_id, project_id,
4676: task_id, award_id, expenditure_organization_id,

Line 4686: VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,

4682: attribute6, attribute7, attribute8,
4683: attribute9, attribute10, liquidate_request_id,
4684: proposed_termination_date, last_update_date, last_updated_by,
4685: last_update_login, created_by, creation_date)
4686: VALUES (psp_enc_summary_lines_s.NEXTVAL, g_bg_id,
4687: r_liq_lines.enc_control_id(recno), r_liq_lines.time_period_id(recno),
4688: r_liq_lines.person_id(recno), r_liq_lines.assignment_id(recno),
4689: r_liq_lines.effective_date(recno), r_liq_lines.set_of_books_id(recno),
4690: r_liq_lines.project_id(recno), r_liq_lines.task_id(recno),

Line 4705: UPDATE psp_enc_summary_lines

4701: g_request_id, g_actual_term_date,
4702: SYSDATE, l_last_updated_by, l_last_update_login, l_last_updated_by, SYSDATE);
4703:
4704: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4705: UPDATE psp_enc_summary_lines
4706: SET status_code = 'S'
4707: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
4708:
4709: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');

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

4705: UPDATE psp_enc_summary_lines
4706: SET status_code = 'S'
4707: WHERE enc_summary_line_id= r_liq_lines.enc_summary_line_id(recno);
4708:
4709: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''S'' in psp_enc_summary_lines');
4710:
4711: FORALL recno IN 1..r_liq_lines.enc_summary_line_id.COUNT
4712: UPDATE psp_enc_controls
4713: SET gms_phase = 'Summarize'

Line 4782: FROM psp_enc_summary_lines pesl

4778: DECODE(g_dff_grouping_option, 'Y', pesl.attribute9, NULL) attribute9,
4779: DECODE(g_dff_grouping_option, 'Y', pesl.attribute10, NULL) attribute10,
4780: pesl.expenditure_item_id -- 4068182
4781: --- removed history table from the from clause.. performace 3953230
4782: FROM psp_enc_summary_lines pesl
4783: WHERE pesl.enc_control_id = p_enc_control_id
4784: --- changed pelh to pesl for performance ..3684930
4785: AND pesl.gl_project_flag = 'P'
4786: AND pesl.status_code = 'A'

Line 4910: update psp_enc_summary_lines set status_code='S'

4906: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4907: END IF;
4908:
4909: / * Flag the original line as Superceded* /
4910: update psp_enc_summary_lines set status_code='S'
4911: where enc_summary_line_id=enc_liq_rec.enc_summary_line_id;
4912: END LOOP;
4913:
4914: END LOOP;

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

4925: END create_gms_enc_liq_lines;
4926: End of comment for bug fix 4625734 *****/
4927:
4928: -- ##########################################################################
4929: -- This procedure transfers liquidated lines from psp_enc_summary_lines
4930: -- with gl_project_flag = 'P' to pa_transaction_interface
4931:
4932: -- This procedure transfers lines from PSP_ENC_SUMMARY_LINES into PA_TRANSACTION_INTERFACE,
4933: -- kicks off the TRANSACTION IMPORT program in GMS and sends ENC_CONTROL_ID, END_DATE for

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

4928: -- ##########################################################################
4929: -- This procedure transfers liquidated lines from psp_enc_summary_lines
4930: -- with gl_project_flag = 'P' to pa_transaction_interface
4931:
4932: -- This procedure transfers lines from PSP_ENC_SUMMARY_LINES into PA_TRANSACTION_INTERFACE,
4933: -- kicks off the TRANSACTION IMPORT program in GMS and sends ENC_CONTROL_ID, END_DATE for
4934: -- the relevant TIME_PERIOD_ID and GMS_BATCH_NAME into the tie back procedure
4935: -- ##########################################################################
4936:

Line 4995: FROM psp_enc_summary_lines pesl,

4991: ptp.end_date,
4992: pesl.effective_date,
4993: papf.employee_number,
4994: pesl.gms_batch_name --6146805
4995: FROM psp_enc_summary_lines pesl,
4996: hr_organization_units hou, -- Introduced the following tables as part of bug fix 4625734
4997: pa_projects_all ppa,
4998: pa_tasks pt,
4999: per_time_periods ptp,

Line 5079: FROM psp_enc_summary_lines

5075: r_enc_controls r_enc_control_rec;
5076:
5077: CURSOR enc_control_id_cur IS
5078: SELECT DISTINCT enc_control_id
5079: FROM psp_enc_summary_lines
5080: WHERE gms_batch_name = g_gms_batch_name;
5081: -- End of changes for bug fix 4507892
5082:
5083: -- Introduced the following for bug fix 4625734

Line 5091: FROM psp_enc_summary_lines pesl

5087: WHERE transaction_source = 'GOLDE';
5088:
5089: CURSOR gms_batch_name_cur IS
5090: SELECT DISTINCT gms_batch_name
5091: FROM psp_enc_summary_lines pesl
5092: WHERE pesl.payroll_action_id = p_payroll_action_id
5093: /*WHERE enc_control_id IN (SELECT pec.enc_control_id
5094: FROM psp_enc_controls pec
5095: WHERE pec.payroll_id = nvl(p_payroll_id, pec.payroll_id)

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

5154: gms_batch_name t_number_15);
5155:
5156: r_interface t_interface;
5157:
5158: l_expenditure_item_id psp_enc_summary_lines.expenditure_item_id%TYPE; --Bug 6062628
5159:
5160: l_raise_error BOOLEAN;
5161: -- End of changes for bug fix 4625734
5162:

Line 5179: FROM psp_enc_summary_lines

5175: call_status_tab call_status_TYPE;
5176:
5177: CURSOR operating_unit_csr IS
5178: SELECT distinct org_id
5179: FROM psp_enc_summary_lines
5180: WHERE status_code = 'N'
5181: AND gl_code_combination_id IS NULL
5182: AND gms_batch_name IS NULL
5183: AND payroll_action_id = p_payroll_action_id;

Line 5266: UPDATE psp_enc_summary_lines

5262: End of Comment for Create and Update multi thread enh. *****/
5263:
5264: -- FORALL recno IN 1..r_enc_control.enc_control_id.COUNT
5265: FORALL I IN 1..org_id_tab.count
5266: UPDATE psp_enc_summary_lines
5267: SET gms_batch_name = gms_batch_name_tab(i)
5268: WHERE status_code = 'N'
5269: AND gl_code_combination_id is NULL
5270: AND superceded_line_id IS NOT NULL

Line 5374: UPDATE psp_enc_summary_lines pesl

5370:
5371: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed computation of PA week ending date(s)');
5372:
5373: FORALL recno IN 1..r_interface.txn_interface_id.COUNT
5374: UPDATE psp_enc_summary_lines pesl
5375: SET gms_posting_override_date = r_interface.gms_overriding_date(recno)
5376: WHERE pesl.enc_summary_line_id = r_interface.enc_summary_line_id(recno)
5377: AND TRUNC(r_interface.effective_date(recno)) <> TRUNC(r_interface.gms_overriding_date(recno));
5378:

Line 5421: FROM psp_enc_summary_lines

5417: LOOP
5418:
5419: SELECT expenditure_item_id
5420: INTO l_expenditure_item_id
5421: FROM psp_enc_summary_lines
5422: WHERE enc_summary_line_id = r_interface.superceded_line_id(recno); --Bug 6062628
5423:
5424:
5425: GMS_REC.TXN_INTERFACE_ID := r_interface.txn_interface_id(recno);

Line 5472: UPDATE psp_enc_summary_lines

5468: FROM per_time_periods
5469: WHERE time_period_id = enc_control_rec.time_period_id;
5470:
5471: if enc_control_rec.gms_phase = 'Summarize' then --- 2444657
5472: UPDATE psp_enc_summary_lines
5473: SET gms_batch_name = g_gms_batch_name --- replaced with global for 3473294
5474: WHERE status_code = 'N'
5475: AND gl_code_combination_id is NULL
5476: AND enc_control_id = enc_control_rec.enc_control_id;

Line 5595: update psp_Enc_summary_lines set gms_posting_override_date=l_effective_date

5591:
5592: -- Replaced != by <> ib the foll. condn. for GSCC warning.
5593: if trunc(l_effective_date) <> trunc(int_rec.effective_date)
5594: then
5595: update psp_Enc_summary_lines set gms_posting_override_date=l_effective_date
5596: where enc_summary_line_id=int_rec.enc_summary_line_id;
5597: end if;
5598:
5599: l_exp_end_dt := pa_utils.getweekending(l_effective_date);

Line 5707: from psp_enc_summary_lines

5703: gms_tie_tab(l_rec_no).r_control_id := enc_control_rec.enc_control_id;
5704: gms_tie_tab(l_rec_no).r_end_date := l_period_end_dt;
5705: select gms_batch_name
5706: into gms_tie_tab(l_rec_no).r_gms_batch_name
5707: from psp_enc_summary_lines
5708: where enc_control_id = enc_control_rec.enc_control_id
5709: and status_code = 'N'
5710: and gl_code_combination_id is null
5711: and rownum = 1;

Line 5751: from psp_enc_summary_lines

5747: /***** Modified teh following update to BULK UPDATE for R12 performance fixes (bug 4507892)
5748: update psp_enc_controls
5749: set gms_phase = 'Transfer'
5750: where enc_control_id in (select distinct enc_control_id
5751: from psp_enc_summary_lines
5752: where gms_batch_name = g_gms_batch_name); --- replaced with global for 3473294
5753: End of comment for bug fix 4507892 *****/
5754: -- Introduced the following for bug fix 4507892
5755: OPEN enc_control_id_cur;

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

5770: COMMIT;
5771: -- set the context again to multiple
5772: mo_global.set_policy_context('M', null);
5773:
5774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5775: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5776: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5777:
5778: FOR I IN 1..org_id_tab.count

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

5771: -- set the context again to multiple
5772: mo_global.set_policy_context('M', null);
5773:
5774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5775: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5776: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5777:
5778: FOR I IN 1..org_id_tab.count
5779: LOOP

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

5772: mo_global.set_policy_context('M', null);
5773:
5774: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Calling gather_table_stats for psp_enc_summary_lines');
5775: fnd_stats.gather_table_stats('PSP', 'PSP_ENC_SUMMARY_LINES');
5776: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Completed gather_table_stats for psp_enc_summary_lines');
5777:
5778: FOR I IN 1..org_id_tab.count
5779: LOOP
5780: -- insert into psp_Stout values(96, 'gms transfer started');

Line 5858: UPDATE psp_enc_summary_lines pesl

5854: End of comment for bug fix 4625734 *****/
5855:
5856: -- Introduced the folowing for bug fix 4625734
5857: FORALL recno IN 1..r_gms_batch.gms_batch_name.COUNT
5858: UPDATE psp_enc_summary_lines pesl
5859: SET (pesl.expenditure_id, pesl.expenditure_item_id, pesl.expenditure_ending_date,
5860: pesl.txn_interface_id, pesl.interface_id) =
5861: (SELECT ptxn.expenditure_id, ptxn.expenditure_item_id, ptxn.expenditure_ending_date,
5862: ptxn.txn_interface_id, ptxn.interface_id

Line 5940: FROM psp_enc_summary_lines

5936: SELECT enc_control_id,
5937: enc_summary_line_id,
5938: dr_cr_flag,
5939: TO_NUMBER(DECODE(dr_cr_flag, 'C', -summary_amount, summary_amount)) summary_amount
5940: FROM psp_enc_summary_lines
5941: WHERE gms_batch_name = p_gms_batch_name;
5942: -- and enc_control_id = p_enc_control_id; Removed enc_control_id check as part of bug fix 4625734
5943:
5944:

Line 5954: FROM psp_enc_summary_lines pesl

5950: WHERE transaction_source = 'GOLDE'
5951: AND batch_name = p_gms_batch_name;
5952: /***** Commented the following condition as Tie Back is by per gms batch and not by enc_control_id (for bug fix 4625734)
5953: AND orig_transaction_reference IN (SELECT 'E:' || enc_summary_line_id -- Introduced for bug fix 3953230
5954: FROM psp_enc_summary_lines pesl
5955: WHERE pesl.enc_control_id = p_enc_control_id);
5956: End of comment for bug fix 4625734 *****/
5957:
5958:

Line 5966: FROM psp_enc_summary_lines pel

5962: pel.effective_date,
5963: -- pel.attribute30
5964: pel.suspense_org_account_id,
5965: pel.superceded_line_id
5966: FROM psp_enc_summary_lines pel
5967: WHERE pel.enc_summary_line_id = p_enc_line_id
5968: and pel.enc_control_id=p_enc_control_id
5969: and pel.gl_project_flag='P' and
5970: pel.status_code='N';

Line 5988: psp_enc_summary_lines pel

5984: CURSOR get_org_id_cur(P_LINE_ID IN NUMBER) IS
5985: SELECT hou.organization_id, hou.name
5986: FROM hr_all_organization_units hou,
5987: per_assignments_f paf,
5988: psp_enc_summary_lines pel
5989: WHERE pel.enc_summary_line_id = p_line_id
5990: AND pel.enc_control_id=p_enc_control_id
5991: -- AND pel.assignment_id = paf.assignment_id
5992: and pel.person_id=paf.person_id

Line 6015: psp_enc_summary_lines pel

6011: pel.effective_date,
6012: pel.attribute30
6013: FROM hr_all_organization_units hou,
6014: per_assignments_f paf,
6015: psp_enc_summary_lines pel
6016: WHERE pel.enc_control_id = p_enc_control_id
6017: AND pel.gl_project_flag = 'P'
6018: AND pel.status_code = 'A'
6019: AND pel.assignment_id = paf.assignment_id(+)

Line 6127: FROM psp_enc_summary_lines

6123: SELECT enc_control_id,
6124: enc_summary_line_id,
6125: dr_cr_flag,
6126: TO_NUMBER(DECODE(dr_cr_flag, 'C', -summary_amount, summary_amount)) summary_amount
6127: FROM psp_enc_summary_lines
6128: WHERE gms_batch_name = p_gms_batch_name
6129: AND status_code = 'L';
6130:
6131: TYPE t_number_15 IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;

Line 6158: FROM psp_enc_summary_lines

6154: r_superceded_lines r_superceded_line_rec;
6155:
6156: CURSOR superceded_line_id_cur IS
6157: SELECT superceded_line_id
6158: FROM psp_enc_summary_lines
6159: WHERE gms_batch_name = p_gms_batch_name;
6160: -- End of changes for bug fix 4507892
6161:
6162: -- Introduced the following for bug fix 4625734

Line 6169: FROM psp_enc_summary_lines

6165: r_enc_controls r_enc_control_rec;
6166:
6167: CURSOR enc_controls_cur IS
6168: SELECT DISTINCT enc_control_id
6169: FROM psp_enc_summary_lines
6170: WHERE gms_batch_name = p_gms_batch_name;
6171:
6172: CURSOR transaction_status_cur IS
6173: SELECT COUNT(*),

Line 6189: from psp_enc_summary_lines

6185: into l_cnt, l_status
6186: from pa_transaction_interface_all
6187: where transaction_source = 'GOLDE'
6188: and batch_name = (select distinct gms_batch_name
6189: from psp_enc_summary_lines
6190: where enc_control_id = p_enc_control_id
6191: and gms_batch_name is not null)
6192: and transaction_status_code in ('P', 'I')
6193: group by transaction_status_code ;

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

6232:
6233: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from gms_trancsaction_interface_all');
6234:
6235: /***** Converted the following UPDATE to BULK for R12 performance fixes (bug 4507892)
6236: update psp_enc_summary_lines set status_code ='A' where
6237: enc_summary_line_id in (select superceded_line_id from
6238: psp_enc_summary_lines where gms_batch_name=p_gms_batch_name);
6239: End of comment for bug fix 4507892 *****/
6240: -- Introduced the following fo bug fix 4507892

Line 6238: psp_enc_summary_lines where gms_batch_name=p_gms_batch_name);

6234:
6235: /***** Converted the following UPDATE to BULK for R12 performance fixes (bug 4507892)
6236: update psp_enc_summary_lines set status_code ='A' where
6237: enc_summary_line_id in (select superceded_line_id from
6238: psp_enc_summary_lines where gms_batch_name=p_gms_batch_name);
6239: End of comment for bug fix 4507892 *****/
6240: -- Introduced the following fo bug fix 4507892
6241: OPEN superceded_line_id_cur;
6242: FETCH superceded_line_id_cur BULK COLLECT INTO r_superceded_lines.superceded_line_id;

Line 6248: UPDATE psp_enc_summary_lines

6244:
6245: 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);
6246:
6247: FORALL I IN 1..r_superceded_lines.superceded_line_id.COUNT
6248: UPDATE psp_enc_summary_lines
6249: SET status_code = 'A'
6250: WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(I);
6251:
6252: 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');

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

6248: UPDATE psp_enc_summary_lines
6249: SET status_code = 'A'
6250: WHERE enc_summary_line_id = r_superceded_lines.superceded_line_id(I);
6251:
6252: 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');
6253:
6254: r_superceded_lines.superceded_line_id.DELETE;
6255: -- End of changes for bug fix 4507892
6256:

Line 6265: delete from psp_enc_summary_lines

6261: -- End of changes for bug fix 4625734
6262:
6263: 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);
6264:
6265: delete from psp_enc_summary_lines
6266: where gms_batch_name = p_gms_batch_name;
6267: -- and enc_control_id = p_enc_control_id;
6268:
6269: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');

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

6265: delete from psp_enc_summary_lines
6266: where gms_batch_name = p_gms_batch_name;
6267: -- and enc_control_id = p_enc_control_id;
6268:
6269: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Deleted from psp_enc_summary_lines');
6270:
6271: -- Introduced the folowing for bug fix 4625734
6272: FORALL recno IN 1..r_enc_controls.enc_control_id.COUNT
6273: UPDATE psp_enc_controls pec

Line 6345: UPDATE psp_enc_summary_lines

6341: END IF;
6342: END LOOP;
6343:
6344: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6345: UPDATE psp_enc_summary_lines
6346: SET interface_status = r_reject_recs.reason_code(recno)
6347: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6348: AND r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR');
6349:

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

6346: SET interface_status = r_reject_recs.reason_code(recno)
6347: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6348: AND r_reject_recs.txn_status_code(recno) IN ('R', 'PI', 'PO', 'PR');
6349:
6350: 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');
6351:
6352: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6353: UPDATE psp_enc_summary_lines
6354: SET interface_status = r_reject_recs.reason_code(recno),

Line 6353: UPDATE psp_enc_summary_lines

6349:
6350: 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');
6351:
6352: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6353: UPDATE psp_enc_summary_lines
6354: SET interface_status = r_reject_recs.reason_code(recno),
6355: status_code = 'L'
6356: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6357: AND r_reject_recs.txn_status_code(recno) = 'A';

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

6355: status_code = 'L'
6356: WHERE enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno)
6357: AND r_reject_recs.txn_status_code(recno) = 'A';
6358:
6359: 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');
6360:
6361: IF (g_person_id IS NOT NULL) THEN
6362: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6363: UPDATE psp_enc_lines_history

Line 6366: FROM psp_enc_summary_lines pesl

6362: FORALL recno IN 1..r_reject_recs.enc_summary_line_id.COUNT
6363: UPDATE psp_enc_lines_history
6364: SET change_flag = 'L'
6365: WHERE enc_summary_line_id IN (SELECT superceded_line_id
6366: FROM psp_enc_summary_lines pesl
6367: WHERE pesl.enc_summary_line_id = r_reject_recs.enc_summary_line_id(recno))
6368: AND r_reject_recs.txn_status_code(recno) = 'A';
6369: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated change_flag to ''L'' in psp_enc_lines_history for employee level encumbrance liquidation');
6370: END IF;

Line 6413: UPDATE psp_enc_summary_lines

6409:
6410: -- update summary_lines with the reject status code
6411: IF l_trx_status_code in ('R', 'PI', 'PO', 'PR') THEN
6412:
6413: UPDATE psp_enc_summary_lines
6414: SET interface_status = l_trx_reject_code
6415: -- , status_code = 'R'
6416: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
6417:

Line 6429: UPDATE psp_enc_summary_lines

6425: end if;
6426: * /
6427: ELSIF l_trx_status_code = 'A' THEN
6428:
6429: UPDATE psp_enc_summary_lines
6430: SET interface_status = l_trx_reject_code, status_code = 'L'
6431: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
6432: -- and enc_control_id=p_enc_control_id;
6433:

Line 6439: from psp_enc_summary_lines

6435: if g_person_id is not null then
6436: update psp_enc_lines_history
6437: set change_flag = 'L'
6438: where enc_summary_line_id = ( select superceded_line_id
6439: from psp_enc_summary_lines
6440: where enc_summary_line_id = to_number(l_orig_trx_reference));
6441: end if;
6442:
6443:

Line 6470: FROM psp_enc_summary_lines

6466: -- delete the rejected batch records from gms_interface
6467:
6468: SELECT summary_amount, dr_cr_flag
6469: INTO l_summary_amount, l_dr_cr_flag
6470: FROM psp_enc_summary_lines
6471: WHERE enc_summary_line_id = to_number(l_orig_trx_reference);
6472:
6473: IF l_dr_cr_flag = 'D' THEN
6474: l_dr_summary_amount := l_dr_summary_amount + l_summary_amount;

Line 6494: UPDATE psp_enc_summary_lines

6490: -- IF l_trx_reject_code = 'P' THEN
6491:
6492: IF l_trx_status_code = 'A' THEN
6493:
6494: UPDATE psp_enc_summary_lines
6495: -- SET status_code = 'P'
6496: set status_code='L'
6497: WHERE rowid = l_rowid;
6498:

Line 6512: UPDATE psp_enc_summary_lines

6508: x_susp_failed_reject_code := l_trx_reject_code;
6509: x_susp_failed_date := l_encumbrance_date;
6510: l_suspense_ac_failed := 'Y';
6511:
6512: UPDATE psp_enc_summary_lines
6513: SET reject_reason_code = 'EL:' || l_trx_reject_code,
6514: -- status_code = 'A'
6515: status_code = 'R'
6516: WHERE rowid = l_rowid;

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

6514: -- status_code = 'A'
6515: status_code = 'R'
6516: WHERE rowid = l_rowid;
6517:
6518: update psp_enc_summary_lines set status_code='A' where
6519: enc_summary_line_id in (select superceded_line_id from
6520: psp_Enc_summary_lines where rowid=l_rowid);
6521:
6522: ELSE

Line 6520: psp_Enc_summary_lines where rowid=l_rowid);

6516: WHERE rowid = l_rowid;
6517:
6518: update psp_enc_summary_lines set status_code='A' where
6519: enc_summary_line_id in (select superceded_line_id from
6520: psp_Enc_summary_lines where rowid=l_rowid);
6521:
6522: ELSE
6523: l_susp_ac_found := 'TRUE';
6524: OPEN get_org_id_cur(to_number(l_orig_trx_reference));

Line 6621: UPDATE psp_enc_summary_lines

6617:
6618: -- assign the organization suspense account and gl status
6619: --dbms_output.put_line('Updating enc_lines ....NULL..');
6620:
6621: UPDATE psp_enc_summary_lines
6622: SET suspense_org_account_id = l_organization_account_id,
6623: reject_reason_code ='EL:'||l_trx_status_code,
6624: gl_project_flag = l_gl_project_flag,
6625: gl_code_combination_id = decode(l_gl_project_flag, 'P', null, l_gl_code_combination_id ),

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

6637: -- insert into psp_stout values (99, 'project_id is '||l_project_id);
6638: -- insert into psp_stout values (99, 'award id is '||l_award_id);
6639: --- modified the update for 2530853, flipping the sign of amount if 'C'
6640: if l_gl_project_flag ='G' then
6641: update psp_Enc_summary_lines set ----- dr_cr_flag='C',
6642: summary_amount=decode(dr_cr_flag,'C', -summary_amount,summary_amount)
6643: where rowid=l_rowid;
6644: end if;
6645:

Line 6647: UPDATE psp_enc_summary_lines

6643: where rowid=l_rowid;
6644: end if;
6645:
6646: / *
6647: UPDATE psp_enc_summary_lines
6648: SET attribute30 = l_organization_account_id,
6649: reject_reason_code = 'EL:' || l_trx_reject_code,
6650: gl_project_flag = l_gl_project_flag,
6651: effective_date = l_encumbrance_date,

Line 6712: UPDATE psp_enc_summary_lines

6708:
6709: 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);
6710:
6711: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6712: UPDATE psp_enc_summary_lines
6713: SET status_code = 'L'
6714: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6715:
6716: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' in psp_enc_summary_lines');

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

6712: UPDATE psp_enc_summary_lines
6713: SET status_code = 'L'
6714: WHERE enc_summary_line_id = r_success_recs.enc_summary_line_id(recno);
6715:
6716: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated status_code to ''L'' in psp_enc_summary_lines');
6717:
6718: IF (g_person_id IS NOT NULL) THEN
6719: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6720: UPDATE psp_enc_lines_history

Line 6723: FROM psp_enc_summary_lines pesl

6719: FORALL recno IN 1..r_success_recs.enc_summary_line_id.COUNT
6720: UPDATE psp_enc_lines_history
6721: SET change_flag = 'L'
6722: WHERE enc_summary_line_id IN (SELECT superceded_line_id
6723: FROM psp_enc_summary_lines pesl
6724: WHERE pesl.enc_summary_line_id = r_success_recs.enc_summary_line_id(recno));
6725: fnd_file.put_line(fnd_file.log, fnd_date.date_to_canonical(SYSDATE) || ' Updated change_flag to ''L'' in psp_enc_lines_history for employee level liquidation');
6726: END IF;
6727:

Line 6754: UPDATE psp_enc_summary_lines

6750: CLOSE gms_tie_back_success_cur;
6751: EXIT;
6752: END IF;
6753: -- update records in psp_summary_lines as 'A'
6754: UPDATE psp_enc_summary_lines
6755: SET status_code = 'L'
6756: WHERE enc_summary_line_id = l_enc_summary_line_id
6757: and status_code = 'N';
6758:

Line 6764: from psp_enc_summary_lines

6760: if g_person_id is not null then
6761: update psp_enc_lines_history
6762: set change_flag = 'L'
6763: where enc_summary_line_id = ( select superceded_line_id
6764: from psp_enc_summary_lines
6765: where enc_summary_line_id = l_enc_summary_line_id);
6766: end if;
6767:
6768:

Line 6793: UPDATE psp_enc_summary_lines

6789: END IF;
6790:
6791: END LOOP;
6792: / *
6793: UPDATE psp_enc_summary_lines
6794: SET status_code = 'P'
6795: where enc_control_id = p_enc_control_id
6796: and gl_project_flag = 'P'
6797: and gms_batch_name=p_gms_batch_name

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

7009: p_business_group_id in number,
7010: p_set_of_books_id in number,
7011: p_person_id in number,
7012: p_actual_term_date in date) is
7013: ---- there is no index on person/assignment on psp_enc_summary_lines
7014: --- psp_enc_lines_history has index on assignment
7015: cursor get_enc_hist_lines_cur is
7016: select ESL.payroll_id payroll_id,
7017: min(ESL.time_period_id) time_period_id

Line 7019: psp_enc_summary_lines ESL,

7015: cursor get_enc_hist_lines_cur is
7016: select ESL.payroll_id payroll_id,
7017: min(ESL.time_period_id) time_period_id
7018: from psp_enc_lines_history ELH,
7019: psp_enc_summary_lines ESL,
7020: per_all_assignments_f ASG
7021: where ASG.person_id = p_person_id
7022: and ASG.assignment_id = ELH.assignment_id
7023: and ELH.enc_summary_line_id = ESL.enc_summary_line_id