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.

[Home] [Help]

APPS.PSP_XMLGEN dependencies on PSP_EFF_REPORTS

Line 227: psp_eff_reports per

223: per.request_id,
224: per.set_of_books_id
225: FROM psp_eff_report_details perd,
226: psp_eff_report_approvals pera,
227: psp_eff_reports per
228: WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
229: AND perd.effort_report_id = per.effort_report_id
230: AND pera.wf_item_key = p_wf_item_key
231: AND ROWNUM = 1

Line 236: FROM psp_eff_reports per2

232: UNION ALL
233: SELECT per2.template_id,
234: p_request_id,
235: per2.set_of_books_id
236: FROM psp_eff_reports per2
237: WHERE per2.request_id = p_request_id
238: AND ROWNUM = 1;
239:
240: CURSOR get_segment_delimeter_cur IS

Line 278: FROM psp_eff_reports

274: AND pdf_request_id = g_request_id;
275:
276: CURSOR format_mask_cur(p_length IN NUMBER) IS
277: SELECT fnd_currency.get_format_mask(currency_code, p_length)
278: FROM psp_eff_reports
279: WHERE request_id = l_request_id;
280:
281: CURSOR show_hide_FYI_lines_csr(p_request_id IN NUMBER,p_investigator_person_id IN NUMBER) IS
282: SELECT 'Y'

Line 284: psp_eff_reports per ,

280:
281: CURSOR show_hide_FYI_lines_csr(p_request_id IN NUMBER,p_investigator_person_id IN NUMBER) IS
282: SELECT 'Y'
283: FROM psp_eff_report_details perd,
284: psp_eff_reports per ,
285: psp_eff_report_details perd2
286: WHERE per.effort_report_id = perd.effort_report_id
287: AND per.status_code IN ('N', 'A')
288: AND per.request_id = NVL(p_request_id,per.request_id)

Line 373: query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE per.status_code IN (''A'', ''N'') '

369: dbms_lob.writeappend(l_xml, length(' '), ' ');
370:
371: IF (p_request_id IS NULL) THEN
372:
373: query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE per.status_code IN (''A'', ''N'') '
374: || 'AND EXISTS (SELECT 1 FROM psp_eff_report_details perd, '
375: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id AND '
376: || 'perd.effort_report_id = per.effort_report_id AND pera.wf_item_key = ' || TO_CHAR(p_wf_item_key)
377: || ' AND pera.approval_status <> ''R'')' || l_sort_option1;

Line 379: query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE status_code IN (''A'', ''N'') AND '

375: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id AND '
376: || 'perd.effort_report_id = per.effort_report_id AND pera.wf_item_key = ' || TO_CHAR(p_wf_item_key)
377: || ' AND pera.approval_status <> ''R'')' || l_sort_option1;
378: ELSE
379: query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE status_code IN (''A'', ''N'') AND '
380: || 'request_id = ' || TO_CHAR(l_request_id) || l_sort_option1;
381: END IF;
382:
383: OPEN person_xml_cur FOR query1;

Line 423: || ' AND EXISTS (SELECT 1 FROM psp_eff_reports per WHERE per.effort_report_id = perd.effort_report_id '

419: || 'TRIM(TO_CHAR(SUM(proposed_salary_amt), ''' || l_num30_fmask || ''')) total_pi_proposed_salary, '
420: || 'TRIM(TO_CHAR(SUM(actual_salary_amt), ''' || l_num30_fmask || ''')) total_pi_actual_salary FROM '
421: || 'psp_eff_report_details perd, psp_eff_report_approvals pera WHERE pera.effort_report_detail_id = '
422: || 'perd.effort_report_detail_id AND pera.wf_item_key = ' || TO_CHAR(p_wf_item_key)
423: || ' AND EXISTS (SELECT 1 FROM psp_eff_reports per WHERE per.effort_report_id = perd.effort_report_id '
424: || 'AND per.status_code IN (''A'', ''N'')) AND perd.investigator_person_id IS NOT NULL'
425: || ' GROUP BY investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id'
426: || l_sort_option1;
427: ELSE

Line 431: || 'psp_eff_report_details perd, psp_eff_reports per WHERE per.effort_report_id = '

427: ELSE
428: query1 := 'SELECT investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id, '
429: || 'TRIM(TO_CHAR(SUM(proposed_salary_amt), ''' || l_num30_fmask || ''')) total_pi_proposed_salary, '
430: || 'TRIM(TO_CHAR(SUM(actual_salary_amt), ''' || l_num30_fmask || ''')) total_pi_actual_salary FROM '
431: || 'psp_eff_report_details perd, psp_eff_reports per WHERE per.effort_report_id = '
432: || 'perd.effort_report_id AND per.request_id = ' || TO_CHAR(p_request_id)
433: || ' AND per.status_code IN (''A'', ''N'') AND perd.investigator_person_id IS NOT NULL'
434: || ' GROUP BY investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id'
435: || l_sort_option1;

Line 476: query2 := 'SELECT person_xml FROM psp_eff_reports per WHERE per.effort_report_id IN (SELECT perd.effort_report_id '

472: ');
473:
474: -- dbms_lob.writeappend(l_xml, length(' '), ' ');
475:
476: query2 := 'SELECT person_xml FROM psp_eff_reports per WHERE per.effort_report_id IN (SELECT perd.effort_report_id '
477: || 'FROM psp_eff_report_details perd WHERE perd.investigator_person_id = ' || TO_CHAR(l_investigator_id) || ')'
478: || ' AND request_id = ' || TO_CHAR(l_request_id) || ' AND status_code <> ''R''' || l_emp_sort_option;
479: OPEN person_xml_cur FOR query2;
480: LOOP

Line 495: || ' approval_date FROM psp_eff_report_approvals pera, psp_eff_reports per'

491: -- Including the WorkFlow Note in Pdf
492:
493: /* query2 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) '
494: || 'approver_name, TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
495: || ' approval_date FROM psp_eff_report_approvals pera, psp_eff_reports per'
496: || ',psp_eff_report_details perd WHERE perd.effort_report_detail_id=pera.effort_report_detail_id AND '
497: || 'per.effort_report_id = perd.effort_report_id AND per.request_id = ' || TO_CHAR(l_request_id)
498: || ' AND perd.investigator_person_id = ' || TO_CHAR(l_investigator_id)
499: || ' ORDER BY approver_order_num DESC';

Line 504: || ' approval_date, wfna.TEXT_VALUE note FROM psp_eff_report_approvals pera, psp_eff_reports per'

500: */
501:
502: query2 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) '
503: || 'approver_name, TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
504: || ' approval_date, wfna.TEXT_VALUE note FROM psp_eff_report_approvals pera, psp_eff_reports per'
505: || ',psp_eff_report_details perd , WF_NOTIFICATION_ATTRIBUTES wfna WHERE perd.effort_report_detail_id=pera.effort_report_detail_id AND '
506: || 'per.effort_report_id = perd.effort_report_id AND per.request_id = ' || TO_CHAR(l_request_id)
507: || ' AND perd.investigator_person_id = ' || TO_CHAR(l_investigator_id)
508: ||' AND wfna.NAME(+) =''WF_NOTE'' AND wfna.NOTIFICATION_ID(+) = pera.NOTIFICATION_ID '

Line 1057: psp_eff_reports per

1053: /*TGEN bug 6864426*/
1054:
1055: SELECT distinct prt.approval_type INTO l_approval_type
1056: FROM psp_report_templates prt,
1057: psp_eff_reports per
1058: where per.effort_report_id = p_effort_report_id
1059: and per.template_id = prt.template_id;
1060:
1061:

Line 1106: || 'FROM psp_eff_Reports er, psp_eff_report_details perd, '

1102: || 'pera.pera_information1, pera.pera_information2, pera.pera_information3, pera.pera_information4, pera.pera_information5, '
1103: || 'pera.pera_information6, pera.pera_information7, pera.pera_information8, pera.pera_information9, pera.pera_information10, '
1104: || 'pera.pera_information11, pera.pera_information12, pera.pera_information13, pera.pera_information14, pera.pera_information15, '
1105: || 'pera.pera_information16, pera.pera_information17, pera.pera_information8, pera.pera_information19, pera.pera_information20 '
1106: || 'FROM psp_eff_Reports er, psp_eff_report_details perd, '
1107: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND er.effort_report_id = perd.effort_Report_id and '
1108: || ' perd.effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1109: || l_er_check || l_sort_option1;
1110:

Line 1175: FROM psp_eff_reports per

1171: AND pdf_request_id = g_request_id;
1172:
1173: CURSOR get_request_id_cur IS
1174: SELECT request_id
1175: FROM psp_eff_reports per
1176: WHERE EXISTS (SELECT 1
1177: FROM psp_eff_report_details perd,
1178: psp_eff_report_approvals pera
1179: WHERE perd.effort_report_id = per.effort_report_id

Line 1324: FROM psp_eff_reports per

1320: AND pdf_request_id = g_request_id;
1321:
1322: CURSOR get_request_id_cur IS
1323: SELECT request_id
1324: FROM psp_eff_reports per
1325: WHERE EXISTS (SELECT 1
1326: FROM psp_eff_report_details perd,
1327: psp_eff_report_approvals pera
1328: WHERE perd.effort_report_id = per.effort_report_id

Line 1419: rec psp_Eff_reports%rowtype;

1415: AND message_level = 'E'
1416: AND error_message = SUBSTR(p_sqlerrm, 1, 2000)
1417: AND pdf_request_id = g_request_id;
1418: --- added er_cur for 4429787
1419: rec psp_Eff_reports%rowtype;
1420: cursor er_cur is select effort_Report_id,
1421: person_id, template_id, request_id, set_of_books_id, full_name,
1422: employee_number, mailstop, emp_primary_org_name, emp_primary_org_id, currency_code
1423: from psp_Eff_reports where request_id = p_request_id

Line 1423: from psp_Eff_reports where request_id = p_request_id

1419: rec psp_Eff_reports%rowtype;
1420: cursor er_cur is select effort_Report_id,
1421: person_id, template_id, request_id, set_of_books_id, full_name,
1422: employee_number, mailstop, emp_primary_org_name, emp_primary_org_id, currency_code
1423: from psp_Eff_reports where request_id = p_request_id
1424: AND person_id BETWEEN p_start_person AND p_end_person
1425: AND status_code <> 'R';
1426: x_lob clob;
1427: l_error_count NUMBER;

Line 1455: UPDATE psp_eff_reports

1451: rec.emp_primary_org_name, rec.emp_primary_org_id,
1452: rec.currency_code );
1453:
1454:
1455: UPDATE psp_eff_reports
1456: SET person_xml = x_lob
1457: WHERE effort_report_id = rec.effort_report_id;
1458:
1459: end loop;

Line 1497: UPDATE psp_eff_reports

1493: l_error_count NUMBER;
1494: l_return_status CHAR(1);
1495: BEGIN
1496: g_request_id := fnd_global.conc_request_id;
1497: UPDATE psp_eff_reports
1498: SET person_xml = generate_person_xml(person_id, template_id, effort_report_id, request_id, set_of_books_id, full_name, employee_number, mailstop, emp_primary_org_name, emp_primary_org_id, currency_code)
1499: WHERE request_id = p_request_id
1500: AND status_code <> 'R';
1501:

Line 1530: FROM psp_eff_reports per,

1526: PROCEDURE update_er_person_xml (p_wf_item_key IN NUMBER,
1527: p_return_status OUT NOCOPY VARCHAR2) IS
1528: CURSOR get_request_id IS
1529: SELECT request_id
1530: FROM psp_eff_reports per,
1531: psp_eff_report_details perd,
1532: psp_eff_report_approvals pera
1533: WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
1534: AND per.effort_report_id = perd.effort_report_id

Line 1562: FROM psp_eff_reports

1558: mailstop,
1559: emp_primary_org_name,
1560: emp_primary_org_id,
1561: currency_code
1562: FROM psp_eff_reports
1563: WHERE effort_report_id IN (SELECT effort_report_id
1564: FROM psp_eff_report_details perd,
1565: psp_eff_report_approvals pera
1566: WHERE perd.effort_report_detail_id = pera.effort_report_detail_id

Line 1593: UPDATE psp_eff_reports

1589: person_rec.emp_primary_org_name,
1590: person_rec.emp_primary_org_id,
1591: person_rec.currency_code );
1592:
1593: UPDATE psp_eff_reports
1594: SET person_xml = l_xml
1595: WHERE effort_report_id = person_rec.effort_report_id;
1596: END LOOP;
1597: CLOSE person_cur;

Line 1600: UPDATE psp_eff_reports

1596: END LOOP;
1597: CLOSE person_cur;
1598:
1599: /***** Converted single xml clob update statement into row by row update
1600: UPDATE psp_eff_reports
1601: SET person_xml = generate_person_xml(person_id, template_id, effort_report_id, request_id, set_of_books_id, full_name, employee_number, mailstop, emp_primary_org_name, emp_primary_org_id, currency_code)
1602: WHERE effort_report_id IN (SELECT effort_report_id
1603: FROM psp_eff_report_details perd,
1604: psp_eff_report_approvals pera

Line 1663: FROM psp_eff_reports per

1659: CURSOR effort_report_id_cur IS
1660: SELECT effort_report_id,
1661: start_date,
1662: end_date
1663: FROM psp_eff_reports per
1664: WHERE person_id BETWEEN p_start_person AND p_end_person
1665: AND status_code <> 'R'
1666: AND request_id = p_request_id;
1667:

Line 1673: FROM psp_eff_reports per,

1669: SELECT SUBSTR(report_template_code, 6, 3) layout_type,
1670: per.template_id,
1671: per.set_of_books_id,
1672: prth.report_template_code
1673: FROM psp_eff_reports per,
1674: psp_report_templates_h prth
1675: WHERE per.request_id = p_request_id
1676: AND prth.request_id = per.request_id
1677: AND ROWNUM = 1;

Line 1727: from psp_eff_reports

1723: l_effort_report_id number;
1724:
1725: cursor eff_reports_emp_data_null_cur(l_effort_report_id number) is
1726: select 1
1727: from psp_eff_reports
1728: where effort_report_id = l_effort_report_id
1729: and employee_number is NULL;
1730:
1731:

Line 1752: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per

1748:
1749: IF (l_layout_type = 'EMP') THEN
1750: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1751: UPDATE psp_eff_report_details perd
1752: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1753: WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1754: AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1755: AND paaf.effective_end_date >= r_effort_report.r_start_date(I)
1756: AND paaf.assignment_number is not null -- Bug 8540341

Line 1789: || ' WHERE effort_report_id IN (SELECT per.effort_report_id FROM psp_eff_reports per WHERE per.request_id = '

1785: CLOSE get_segment_name_cur;
1786:
1787: IF (l_query IS NOT NULL) THEN
1788: l_query := 'UPDATE psp_eff_report_details SET gl_sum_criteria_segment_name = ' || l_query
1789: || ' WHERE effort_report_id IN (SELECT per.effort_report_id FROM psp_eff_reports per WHERE per.request_id = '
1790: || TO_CHAR(p_request_id) || ' AND per.person_id BETWEEN '
1791: || TO_CHAR(p_start_person) || ' AND ' || TO_CHAR(p_end_person) || ') AND '
1792: || '(gl_segment1 IS NOT NULL OR gl_segment2 IS NOT NULL OR gl_segment3 IS NOT NULL OR '
1793: || 'gl_segment4 IS NOT NULL OR gl_segment5 IS NOT NULL OR gl_segment6 IS NOT NULL OR '

Line 1811: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per

1807: END IF;
1808: ELSIF (l_layout_type = 'PIV') THEN
1809: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1810: UPDATE psp_eff_report_details perd
1811: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1812: WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1813: AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1814: AND paaf.effective_end_date >= r_effort_report.r_start_date(I)
1815: AND paaf.assignment_number is not null -- Bug 8540341

Line 1845: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per

1841: WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1842: ELSIF (l_layout_type = 'PMG') THEN
1843: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1844: UPDATE psp_eff_report_details perd
1845: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1846: WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1847: AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1848: AND paaf.effective_end_date >= r_effort_report.r_start_date(I)
1849: AND paaf.assignment_number is not null -- Bug 8540341

Line 1880: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per

1876: WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1877: ELSIF (l_layout_type = 'TMG') THEN
1878: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1879: UPDATE psp_eff_report_details perd
1880: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1881: WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1882: AND paaf.effective_start_date <= per.end_date
1883: AND paaf.effective_end_date >= per.start_date
1884: AND paaf.assignment_number is not null -- Bug 8540341

Line 1919: UPDATE psp_eff_reports per

1915:
1916: --Commented the following code for the bug 12326688
1917: /*
1918: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1919: UPDATE psp_eff_reports per
1920: SET (employee_number, full_name, mailstop) = (SELECT papf.employee_number, papf.full_name, papf.mailstop FROM per_all_people_f papf
1921: WHERE papf.person_id = per.person_id AND papf.effective_start_date <= per.end_date
1922: AND papf.effective_end_date >= per.start_date
1923: AND papf.employee_number is not NULL -- Bug 8540341

Line 1936: /* updating psp_eff_reports with the data as of effort report end date*/

1932: */
1933:
1934: --Commented the following code for the bug 12752455
1935:
1936: /* updating psp_eff_reports with the data as of effort report end date*/
1937: /*
1938: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1939: UPDATE psp_eff_reports per1
1940: set (employee_number, full_name, mailstop, emp_primary_org_id, emp_primary_org_name ) =

Line 1939: UPDATE psp_eff_reports per1

1935:
1936: /* updating psp_eff_reports with the data as of effort report end date*/
1937: /*
1938: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1939: UPDATE psp_eff_reports per1
1940: set (employee_number, full_name, mailstop, emp_primary_org_id, emp_primary_org_name ) =
1941: (
1942: select distinct papf.employee_number,papf.full_name,papf.mailstop,haou.organization_id,haou.name
1943: from per_all_people_f papf,hr_all_organization_units haou, per_all_assignments_f paaf

Line 1959: updating psp_eff_reports with the data as of effort report start date*/

1955: where per1.effort_report_id = r_effort_report.r_effort_report_id(I)
1956: ;
1957: */
1958: /* For the rest of the records that dont have data as of effort report end date,
1959: updating psp_eff_reports with the data as of effort report start date*/
1960:
1961: /*
1962: FOR I IN 1..r_effort_report.r_effort_report_id.COUNT
1963: loop

Line 1975: UPDATE psp_eff_reports per1

1971:
1972: fnd_file.put_line(fnd_file.log,' There is a problem with the effort report id: '||l_effort_report_id);
1973:
1974: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1975: UPDATE psp_eff_reports per1
1976: set (employee_number, full_name, mailstop, emp_primary_org_id, emp_primary_org_name ) =
1977: (
1978: select distinct papf.employee_number,papf.full_name,papf.mailstop,haou.organization_id,haou.name
1979: from per_all_people_f papf,hr_all_organization_units haou, per_all_assignments_f paaf

Line 2008: UPDATE psp_eff_reports per

2004:
2005: -- Changes begins for the Bug 12752455
2006:
2007: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
2008: UPDATE psp_eff_reports per
2009: SET (employee_number, full_name, mailstop) = (select a.employee_number
2010: ,a.full_name
2011: ,a.mailstop
2012: FROM(

Line 2018: psp_eff_reports per1

2014: ,papf.employee_number
2015: ,papf.full_name
2016: ,papf.mailstop
2017: FROM per_all_people_f papf,
2018: psp_eff_reports per1
2019: WHERE papf.person_id = per1.person_id
2020: AND per1.effort_report_id = r_effort_report.r_effort_report_id(I)
2021: AND papf.effective_start_date <= per1.end_date
2022: AND papf.effective_end_date >= per1.start_date

Line 2035: psp_eff_reports per1

2031: haou.organization_id,
2032: haou.name
2033: FROM hr_all_organization_units haou,
2034: per_all_assignments_f paaf,
2035: psp_eff_reports per1
2036: WHERE haou.organization_id = paaf.organization_id
2037: AND paaf.person_id = per1.person_id
2038: AND per1.effort_report_id = r_effort_report.r_effort_report_id(I)
2039: AND paaf.effective_start_date <= per1.end_date

Line 2082: FROM psp_eff_reports per

2078: p_return_status OUT NOCOPY VARCHAR2) IS
2079:
2080: CURSOR effort_report_id_cur IS
2081: SELECT effort_report_id
2082: FROM psp_eff_reports per
2083: WHERE person_id BETWEEN p_start_person AND p_end_person
2084: AND status_code <> 'R'
2085: AND request_id = p_request_id;
2086:

Line 2116: || ' , EXPENDITURE_TYPE = GL_'|| l_exp_type_segment ||' WHERE effort_report_id in (select effort_report_id FROM psp_eff_reports per'

2112: p_exp_type_segment => l_exp_type_segment);
2113:
2114: sql_stmt := ' Update psp_eff_report_details set project_id = GL_'||l_proj_segment ||
2115: ' , TASK_ID = GL_'|| l_tsk_segment || ' , AWARD_ID = GL_' || l_awd_sgement || ' , EXPENDITURE_ORGANIZATION_ID = GL_' || l_exp_org_segment
2116: || ' , EXPENDITURE_TYPE = GL_'|| l_exp_type_segment ||' WHERE effort_report_id in (select effort_report_id FROM psp_eff_reports per'
2117: || ' WHERE person_id BETWEEN ' || p_start_person || ' AND ' || p_end_person
2118: || ' AND status_code <> ''R'' AND request_id = ' || p_request_id ||' )';
2119:
2120: EXECUTE IMMEDIATE sql_stmt ;

Line 2180: FROM psp_eff_reports per

2176: CLOSE er_dates_cur;
2177:
2178: UPDATE psp_report_errors pre
2179: SET (source_name, parent_source_id, parent_source_name) = (SELECT full_name, emp_primary_org_id, emp_primary_org_name
2180: FROM psp_eff_reports per
2181: WHERE per.request_id = p_request_id
2182: AND per.person_id = TO_NUMBER(pre.source_id))
2183: WHERE pre.request_id = p_request_id
2184: AND pre.source_id IS NOT NULL

Line 2271: FROM psp_eff_reports per,

2267: l_return_status CHAR(1);
2268:
2269: CURSOR GLA_effort_report_detail_cur IS
2270: SELECT perd.effort_report_detail_id
2271: FROM psp_eff_reports per,
2272: psp_eff_report_details perd
2273: WHERE per.effort_report_id = perd.effort_report_id
2274: AND per.person_id BETWEEN p_start_person AND p_end_person
2275: AND request_id = p_request_id

Line 2294: FROM psp_eff_reports per,

2290: OR perd.gl_segment29 IS NOT NULL OR perd.gl_segment30 IS NOT NULL);
2291:
2292: CURSOR SPO_effort_report_detail_cur IS
2293: SELECT perd.effort_report_detail_id
2294: FROM psp_eff_reports per,
2295: psp_eff_report_details perd
2296: WHERE per.effort_report_id = perd.effort_report_id
2297: AND per.person_id BETWEEN p_start_person AND p_end_person
2298: AND request_id = p_request_id

Line 2309: FROM psp_eff_reports per,

2305: AND ppa.project_type <> 'AWARD_PROJECT' AND NVL(gpta.sponsored_flag, 'N') ='Y');
2306:
2307: CURSOR NSP_effort_report_detail_cur IS
2308: SELECT perd.effort_report_detail_id
2309: FROM psp_eff_reports per,
2310: psp_eff_report_details perd
2311: WHERE per.effort_report_id = perd.effort_report_id
2312: AND per.person_id BETWEEN p_start_person AND p_end_person
2313: AND request_id = p_request_id