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_CREATE_EFF_REPORTS dependencies on PSP_EFF_REPORTS

Line 411: /* pspt.person_id not in (select person_id from psp_eff_reports pea where

407: OR EXISTS
408: (select 1 from psp_element_set_members_v pesr where pesr.element_set_id =
409: :l_element_set_id and pesr.element_type_id = ppg.element_type_id )
410: ) AND
411: /* pspt.person_id not in (select person_id from psp_eff_reports pea where
412: pea.end_date >= :effort_start and pea.start_date <= :effort_end and
413: status_code in ('||''''||'N'||''''||','||''''||'A'||''''||')) AND */
414: ((psl.source_type in ('||''''||'N'||''''||' , '|| ''''|| 'O' ||'''' ||' ) and
415: psl.summary_line_id = pdnh.summary_line_id

Line 491: from psp_Eff_reports er,

487: else
488: */
489: insert into psp_selected_persons_t (request_id, person_id, assignment_id)
490: select -1 * l_request_id, person_id, assignment_id
491: from psp_Eff_reports er,
492: psp_eff_report_details erd
493: where er.effort_Report_id = erd.effort_Report_id
494: and er.request_id = l_request_id
495: and er.status_code in ('N','A')

Line 505: g_exec_string := replace(g_exec_string, 'pspt.person_id not in (select person_id from psp_eff_reports pea where',null);

501: ' nvl(:l_start_person,-9) = nvl(:l_start_person,-9) and
502: nvl(:l_end_person,-9) = nvl(:l_end_person,-9) ');
503: g_exec_string := replace(g_exec_string, 'pea.end_date >= :effort_start and pea.start_date <= :effort_end and',null);
504: g_exec_string := replace(g_exec_string, 'status_code in ('||''''||'N'||''''||','||''''||'A'||''''||')) AND',null);
505: g_exec_string := replace(g_exec_string, 'pspt.person_id not in (select person_id from psp_eff_reports pea where',null);
506: g_exec_string := replace(g_exec_string, 'group by psl.person_id',
507: ' and pspt.person_id in (select person_id from psp_Eff_reports where request_id ='|| p_request_id||' and status_code in ( '||''''||'A'||''''||','||''''||'N'||''''||')) group by psl.person_id');
508: g_exec_string := replace(g_exec_string, 'and pspt.request_id = :l_request_id',
509: ' and -1 * pspt.request_id = :l_request_id and pspt.skip_reason is null ');

Line 507: ' and pspt.person_id in (select person_id from psp_Eff_reports where request_id ='|| p_request_id||' and status_code in ( '||''''||'A'||''''||','||''''||'N'||''''||')) group by psl.person_id');

503: g_exec_string := replace(g_exec_string, 'pea.end_date >= :effort_start and pea.start_date <= :effort_end and',null);
504: g_exec_string := replace(g_exec_string, 'status_code in ('||''''||'N'||''''||','||''''||'A'||''''||')) AND',null);
505: g_exec_string := replace(g_exec_string, 'pspt.person_id not in (select person_id from psp_eff_reports pea where',null);
506: g_exec_string := replace(g_exec_string, 'group by psl.person_id',
507: ' and pspt.person_id in (select person_id from psp_Eff_reports where request_id ='|| p_request_id||' and status_code in ( '||''''||'A'||''''||','||''''||'N'||''''||')) group by psl.person_id');
508: g_exec_string := replace(g_exec_string, 'and pspt.request_id = :l_request_id',
509: ' and -1 * pspt.request_id = :l_request_id and pspt.skip_reason is null ');
510: else
511: -- added for UVA issue.

Line 683: SELECT NVL(max(effort_report_id),0) into min_effort_report_id from psp_eff_reports;

679:
680:
681:
682:
683: SELECT NVL(max(effort_report_id),0) into min_effort_report_id from psp_eff_reports;
684: if p_supercede_mode is null then
685: OPEN get_all_person(l_request_id);
686: FETCH get_all_person bulk collect into person_rec.array_person_id;
687: CLOSE get_all_person;

Line 718: -- fnd_file.put_line(fnd_file.log,' before inserting into psp_eff_reports ');

714: hr_utility.trace ('count person is '||det_person_id.count);
715:
716: IF det_person_id.count >0 then
717: l_loop_count := l_loop_count + det_person_id.count;
718: -- fnd_file.put_line(fnd_file.log,' before inserting into psp_eff_reports ');
719:
720: -- fnd_file.put_line(fnd_file.log,' =====inserting in psp_eff_reports ');
721:
722: FORALL i IN 1..person_rec.array_person_id.count

Line 720: -- fnd_file.put_line(fnd_file.log,' =====inserting in psp_eff_reports ');

716: IF det_person_id.count >0 then
717: l_loop_count := l_loop_count + det_person_id.count;
718: -- fnd_file.put_line(fnd_file.log,' before inserting into psp_eff_reports ');
719:
720: -- fnd_file.put_line(fnd_file.log,' =====inserting in psp_eff_reports ');
721:
722: FORALL i IN 1..person_rec.array_person_id.count
723: insert into psp_eff_reports(
724: effort_report_id,

Line 723: insert into psp_eff_reports(

719:
720: -- fnd_file.put_line(fnd_file.log,' =====inserting in psp_eff_reports ');
721:
722: FORALL i IN 1..person_rec.array_person_id.count
723: insert into psp_eff_reports(
724: effort_report_id,
725: status_code,
726: person_id,
727: object_version_number,

Line 896: (select effort_report_id from psp_eff_reports where

892: hr_utility.trace(' psp_create_eff_reports--> updating erd count ='|| effort_det_lines_rec.effort_report_detail_id.count ||' min_effort_Report_id = '||min_effort_report_id );
893:
894: FORALL i in 1..effort_det_lines_rec.effort_report_detail_id.count
895: update psp_eff_report_details set effort_report_id =
896: (select effort_report_id from psp_eff_reports where
897: person_id =effort_Det_lines_rec.person_id(i) and status_code = 'T'
898: and request_id = l_request_id ) where
899: effort_report_detail_id= effort_det_lines_rec.effort_report_detail_id(i) ;
900:

Line 905: (select effort_report_id from psp_eff_reports where

901: ELSE
902:
903: FORALL i in 1..effort_det_lines_rec.effort_report_detail_id.count
904: update psp_eff_report_details set effort_report_id =
905: (select effort_report_id from psp_eff_reports where
906: person_id =effort_Det_lines_rec.person_id(i) and effort_report_id > min_effort_report_id ) where
907: effort_report_detail_id= effort_det_lines_rec.effort_report_detail_id(i) ;
908:
909: END IF;

Line 1082: delete from psp_eff_reports per where effort_report_id > min_effort_report_id and

1078: effort_report_id = person_rec.array_effort_report_id(i) and person_rec.payroll_percent_tot(i)<>0) ;
1079: */
1080:
1081:
1082: delete from psp_eff_reports per where effort_report_id > min_effort_report_id and
1083: not exists (select 1 from psp_eff_report_details perd where
1084: perd.effort_report_id = per.effort_report_id);
1085:
1086: -- fnd_file.put_line(fnd_file.log,' after delete of orphan record ');

Line 1340: (select person_id from psp_eff_reports

1336: set skip_reason = 'ALREADY_EXISTS'
1337: where person_id between p_start_person and p_end_person
1338: and request_id = p_request_id
1339: and person_id in
1340: (select person_id from psp_eff_reports
1341: where status_code in ('N', 'A')
1342: and g_psp_effort_end >= start_date
1343: and g_psp_effort_start <= end_date );
1344:

Line 1366: from psp_eff_reports er, psp_eff_report_details erd

1362: where person_id between p_start_person and p_end_person
1363: and request_id = p_request_id
1364: and (person_id, assignment_id) in
1365: (select er.person_id, erd.assignment_id
1366: from psp_eff_reports er, psp_eff_report_details erd
1367: where er.status_code in ('N', 'A')
1368: and er.effort_Report_id = erd.effort_report_id
1369: and g_psp_effort_end >= er.start_date
1370: and g_psp_effort_start <= er.end_date );

Line 1379: from psp_eff_reports er, psp_eff_report_details erd

1375: where person_id between p_start_person and p_end_person
1376: and request_id = p_request_id
1377: and person_id in
1378: (select er.person_id
1379: from psp_eff_reports er, psp_eff_report_details erd
1380: where er.status_code in ('N', 'A')
1381: and er.effort_Report_id = erd.effort_report_id
1382: and g_psp_effort_end >= er.start_date
1383: and g_psp_effort_start <= er.end_date

Line 1446: pspt.person_id not in (select nvl(person_id,0) from psp_eff_reports where g_psp_effort_end >= start_date and

1442: pspt.person_id, l_msg_str, p_retry_request_id , null, NULL, NULL, NULL
1443: from psp_selected_persons_t pspt where pspt.person_id between p_start_person
1444: and p_end_person and
1445: pspt.request_id = p_request_id and
1446: pspt.person_id not in (select nvl(person_id,0) from psp_eff_reports where g_psp_effort_end >= start_date and
1447: g_psp_effort_start <= end_date and status_code in ('N', 'A'))
1448: and pspt.person_id not in (select nvl(source_id,0) from psp_report_errors where request_id = g_psp_request_id);
1449:
1450:

Line 1463: pspt.person_id not in (select nvl(person_id, 0) from psp_eff_reports where g_psp_effort_end>=start_date and

1459: from psp_selected_persons_t pspt where pspt.person_id between p_start_person and p_end_person and
1460: pspt.request_id = p_request_id and
1461: exists (select nvl(person_id,0) from psp_adjustment_lines where effective_date between g_psp_effort_start
1462: and g_psp_effort_end ) and pspt.person_id not in (select source_id from psp_report_errors where request_id=p_request_id) and
1463: pspt.person_id not in (select nvl(person_id, 0) from psp_eff_reports where g_psp_effort_end>=start_date and
1464: g_psp_effort_start <= end_date);
1465: end if;
1466:
1467: EXCEPTION

Line 1505: from psp_eff_reports per ,

1501: Cursor check_ptaoe is
1502: select 1, per.person_id, perd.project_id, NVL(perd.project_name,'NOTFOUND'), perd.task_id, NVL(perd.task_name,'NOTFOUND'),
1503: perd.award_id, NVL(perd.AWARD_SHORT_NAME,'NOTFOUND'), EXPENDITURE_ORGANIZATION_ID ,NVL(perd.exp_org_name,'NOTFOUND'),
1504: perd.expenditure_type
1505: from psp_eff_reports per ,
1506: psp_eff_report_details perd
1507: where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
1508: AND per.request_id = p_request_id
1509: AND person_id between p_start_person and p_end_person

Line 1518: from psp_eff_reports per ,

1514: UNION ALL
1515: select 2, per.person_id, perd.project_id, perd.project_name, perd.task_id, perd.task_name,
1516: perd.award_id, perd.AWARD_SHORT_NAME, EXPENDITURE_ORGANIZATION_ID ,perd.exp_org_name,
1517: perd.expenditure_type
1518: from psp_eff_reports per ,
1519: psp_eff_report_details perd
1520: where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
1521: and per.request_id = p_request_id
1522: AND person_id between p_start_person and p_end_person

Line 1545: FROM psp_eff_reports per,

1541: CURSOR check_award_pi_cur IS
1542: SELECT DISTINCT per.person_id,
1543: perd.award_id,
1544: perd.award_number
1545: FROM psp_eff_reports per,
1546: psp_eff_report_details perd
1547: WHERE per.request_id = p_request_id
1548: AND per.effort_report_id = perd.effort_report_id
1549: AND per.person_id BETWEEN p_start_person AND p_end_person

Line 1557: FROM psp_eff_reports per,

1553: CURSOR check_pm_cur IS
1554: SELECT DISTINCT per.person_id,
1555: perd.project_id,
1556: perd.project_number
1557: FROM psp_eff_reports per,
1558: psp_eff_report_details perd
1559: WHERE per.request_id = p_request_id
1560: AND per.effort_report_id = perd.effort_report_id
1561: AND per.person_id BETWEEN p_start_person AND p_end_person

Line 1570: FROM psp_eff_reports per,

1566: SELECT DISTINCT per.person_id,
1567: perd.project_number,
1568: perd.task_id,
1569: perd.task_number
1570: FROM psp_eff_reports per,
1571: psp_eff_report_details perd
1572: WHERE per.request_id = p_request_id
1573: AND per.effort_report_id = perd.effort_report_id
1574: AND per.person_id BETWEEN p_start_person AND p_end_person

Line 1583: FROM psp_eff_reports per,

1579: SELECT DISTINCT per.person_id,
1580: ppa.segment1,
1581: perd.task_id,
1582: perd.task_number
1583: FROM psp_eff_reports per,
1584: psp_eff_report_details perd,
1585: pa_tasks pt,
1586: pa_projects_all ppa
1587: WHERE per.request_id = p_request_id

Line 1597: FROM psp_eff_reports per,

1593: AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1594:
1595: CURSOR check_project_number_cur IS
1596: SELECT COUNT(1)
1597: FROM psp_eff_reports per,
1598: psp_eff_report_details perd
1599: WHERE per.request_id = p_request_id
1600: AND per.person_id BETWEEN p_start_person AND p_end_person
1601: AND perd.task_id IS NOT NULL