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_XMLGEN dependencies on PSP_EFF_REPORT_DETAILS

Line 137: l_investigator_name psp_eff_report_details.investigator_name%TYPE;

133: l_segment_delimiter CHAR(1);
134: l_gl_header VARCHAR2(1000);
135: l_segment_header VARCHAR2(200);
136: l_investigator_id NUMBER(15);
137: l_investigator_name psp_eff_report_details.investigator_name%TYPE;
138: l_investigator_org_name psp_eff_report_details.investigator_org_name%TYPE;
139: l_investigator_primary_org_id psp_eff_report_details.investigator_primary_org_id%TYPE;
140: l_total_pi_proposed_salary VARCHAR2(50);
141: l_total_pi_actual_salary VARCHAR2(50);

Line 138: l_investigator_org_name psp_eff_report_details.investigator_org_name%TYPE;

134: l_gl_header VARCHAR2(1000);
135: l_segment_header VARCHAR2(200);
136: l_investigator_id NUMBER(15);
137: l_investigator_name psp_eff_report_details.investigator_name%TYPE;
138: l_investigator_org_name psp_eff_report_details.investigator_org_name%TYPE;
139: l_investigator_primary_org_id psp_eff_report_details.investigator_primary_org_id%TYPE;
140: l_total_pi_proposed_salary VARCHAR2(50);
141: l_total_pi_actual_salary VARCHAR2(50);
142: l_display_flag psp_report_templates_h.display_all_emp_distrib_flag%TYPE;

Line 139: l_investigator_primary_org_id psp_eff_report_details.investigator_primary_org_id%TYPE;

135: l_segment_header VARCHAR2(200);
136: l_investigator_id NUMBER(15);
137: l_investigator_name psp_eff_report_details.investigator_name%TYPE;
138: l_investigator_org_name psp_eff_report_details.investigator_org_name%TYPE;
139: l_investigator_primary_org_id psp_eff_report_details.investigator_primary_org_id%TYPE;
140: l_total_pi_proposed_salary VARCHAR2(50);
141: l_total_pi_actual_salary VARCHAR2(50);
142: l_display_flag psp_report_templates_h.display_all_emp_distrib_flag%TYPE;
143: l_report_info VARCHAR2(4000);

Line 223: FROM psp_eff_report_details perd,

219: CURSOR template_id_cur IS
220: SELECT per.template_id,
221: per.request_id,
222: per.set_of_books_id
223: FROM psp_eff_report_details perd,
224: psp_eff_report_approvals pera,
225: psp_eff_reports per
226: WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
227: AND perd.effort_report_id = per.effort_report_id

Line 281: FROM psp_eff_report_details perd,

277: WHERE request_id = l_request_id;
278:
279: CURSOR show_hide_FYI_lines_csr(p_request_id IN NUMBER,p_investigator_person_id IN NUMBER) IS
280: SELECT 'Y'
281: FROM psp_eff_report_details perd,
282: psp_eff_reports per ,
283: psp_eff_report_details perd2
284: WHERE per.effort_report_id = perd.effort_report_id
285: AND per.status_code IN ('N', 'A')

Line 283: psp_eff_report_details perd2

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

Line 372: || 'AND EXISTS (SELECT 1 FROM psp_eff_report_details perd, '

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

Line 419: || 'psp_eff_report_details perd, psp_eff_report_approvals pera WHERE pera.effort_report_detail_id = '

415: IF (p_request_id IS NULL) THEN
416: query1 := 'SELECT investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id, '
417: || 'TRIM(TO_CHAR(SUM(proposed_salary_amt), ''' || l_num30_fmask || ''')) total_pi_proposed_salary, '
418: || 'TRIM(TO_CHAR(SUM(actual_salary_amt), ''' || l_num30_fmask || ''')) total_pi_actual_salary FROM '
419: || 'psp_eff_report_details perd, psp_eff_report_approvals pera WHERE pera.effort_report_detail_id = '
420: || 'perd.effort_report_detail_id AND pera.wf_item_key = ' || TO_CHAR(p_wf_item_key)
421: || ' AND EXISTS (SELECT 1 FROM psp_eff_reports per WHERE per.effort_report_id = perd.effort_report_id '
422: || 'AND per.status_code IN (''A'', ''N'')) AND perd.investigator_person_id IS NOT NULL'
423: || ' GROUP BY investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_id'

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

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

Line 475: || 'FROM psp_eff_report_details perd WHERE perd.investigator_person_id = ' || TO_CHAR(l_investigator_id) || ')'

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

Line 494: || ',psp_eff_report_details perd WHERE perd.effort_report_detail_id=pera.effort_report_detail_id AND '

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

Line 503: || ',psp_eff_report_details perd , WF_NOTIFICATION_ATTRIBUTES wfna WHERE perd.effort_report_detail_id=pera.effort_report_detail_id AND '

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

Line 623: FROM psp_eff_report_details

619:
620: CURSOR assign_cur (p_effort_report_id IN NUMBER) IS
621: SELECT DISTINCT assignment_id,
622: assignment_number
623: FROM psp_eff_report_details
624: WHERE effort_report_id = p_effort_report_id
625: ORDER BY assignment_number ASC; -- Bug 4247734
626:
627: CURSOR add_report_error_cur (p_sqlerrm IN VARCHAR2) IS

Line 638: FROM psp_eff_report_details perd,

634:
635: /* Added for Hospital effort report */
636: CURSOR grouping_category_csr(p_assignment_id IN NUMBER) IS
637: SELECT distinct perd.grouping_category, plo.layout_option_order
638: FROM psp_eff_report_details perd,
639: psp_layout_options plo
640: WHERE effort_report_id = p_effort_report_id
641: AND NVL(assignment_id,-1) = NVL(p_assignment_id,-1)
642: AND plo.layout_lookup_type ='PSP_EFFORT_CATEGORY'

Line 701: l_er_check := ' AND NOT EXISTS (SELECT 1 FROM psp_eff_report_details perd1, psp_eff_report_approvals pera1 '

697: END IF;
698: END LOOP;
699: CLOSE sort_option_cur;
700:
701: l_er_check := ' AND NOT EXISTS (SELECT 1 FROM psp_eff_report_details perd1, psp_eff_report_approvals pera1 '
702: || 'WHERE perd.effort_report_id = perd1.effort_report_id AND perd1.effort_report_detail_id = pera1.effort_report_detail_id '
703: || 'AND pera1.approval_status = ''R'') '
704: || 'AND NVL(pera.approver_order_num, 1) = (SELECT NVL(MAX(pera1.approver_order_num), 1) FROM psp_eff_report_approvals pera1 '
705: || 'WHERE pera1.effort_report_detail_id = perd.effort_report_detail_id)';

Line 721: || ' FROM psp_eff_report_details perd, '

717:
718: query1 := 'select distinct pera.eff_information1, pera.eff_information2, pera.eff_information3,pera.eff_information4, pera.eff_information5, '
719: || ' pera.eff_information6,pera.eff_information7, pera.eff_information8 , pera.eff_information9,pera.eff_information10, '
720: || ' pera.eff_information11, pera.eff_information12, pera.eff_information13, pera.eff_information14, pera.eff_information15 '
721: || ' FROM psp_eff_report_details perd, '
722: || ' psp_eff_report_approvals pera '
723: || ' WHERE perd.effort_report_detail_id = pera.effort_report_detail_id '
724: || ' AND perd.effort_report_id = ' || p_effort_report_id
725: || ' AND APPROVER_ORDER_NUM = (SELECT max(APPROVER_ORDER_NUM) '

Line 727: || ' psp_eff_report_details perd2 '

723: || ' WHERE perd.effort_report_detail_id = pera.effort_report_detail_id '
724: || ' AND perd.effort_report_id = ' || p_effort_report_id
725: || ' AND APPROVER_ORDER_NUM = (SELECT max(APPROVER_ORDER_NUM) '
726: || ' FROM psp_eff_report_approvals pera2, '
727: || ' psp_eff_report_details perd2 '
728: || ' WHERE pera2.effort_report_detail_id = perd2.effort_report_detail_id '
729: || ' AND perd2.effort_report_id = perd.effort_report_id) ';
730:
731: qryCtx2 := dbms_xmlquery.newContext(query1);

Line 750: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '

746: || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_emp_actual_salary, '
747: || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_emp_actual_effort, '
748: || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_emp_overwritten_effort, '
749: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_emp_actual_cost_share '
750: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
751: || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
752: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN ( ''P'', ''A'')' || l_er_check;
753: qryCtx1 := dbms_xmlgen.newContext(query1);
754: dbms_xmlgen.setRowTag(qryCtx1, NULL);

Line 784: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '

780: || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_asg_actual_salary, '
781: || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_asg_actual_effort, '
782: || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_asg_overwritten_effort, '
783: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_asg_actual_cost_share '
784: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera where '
785: || 'perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
786: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')' || l_assignment_check;
787: qryCtx1 := dbms_xmlgen.newContext(query1);
788: dbms_xmlgen.setRowTag(qryCtx1, NULL);

Line 814: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera, '

810: || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_overwritten_effort, '
811: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_actual_cost_share, '
812: || 'TRIM(MAX(lookup.lookup_code)) category_code, '
813: || 'TRIM(MAX(lookup.meaning)) category_desc '
814: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera, '
815: || 'psp_layout_options plo, fnd_lookup_values_vl lookup, psp_report_templates_h prth '
816: || 'WHERE perd.grouping_category = plo.layout_lookup_code '
817: || 'AND plo.layout_lookup_code = lookup.lookup_code '
818: || 'AND plo.layout_lookup_type =''PSP_EFFORT_CATEGORY'' '

Line 852: || 'FROM psp_eff_report_details perd, '

848: || 'pera.pera_information1 pera_information1, pera.pera_information2, pera.pera_information3, pera.pera_information4, pera.pera_information5, '
849: || 'pera.pera_information6, pera.pera_information7, pera.pera_information8, pera.pera_information9, pera.pera_information10, '
850: || 'pera.pera_information11, pera.pera_information12, pera.pera_information13, pera.pera_information14, pera.pera_information15, '
851: || 'pera.pera_information16, pera.pera_information17, pera.pera_information8, pera.pera_information19, pera.pera_information20 '
852: || 'FROM psp_eff_report_details perd, '
853: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
854: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
855: || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
856: || l_assignment_check || l_sort_option1;

Line 883: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '

879: || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_spon_actual_salary, '
880: || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_spon_actual_effort, '
881: || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_spon_overwritten_effort, '
882: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_spon_actual_cost_share '
883: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
884: || 'WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
885: || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
886: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
887: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='

Line 905: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '

901: || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_nspon_actual_salary, '
902: || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_nspon_actual_effort, '
903: || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_nspon_overwritten_effort, '
904: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_nspon_actual_cost_share '
905: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
906: || 'WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
907: || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
908: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type (+) = ppa.project_type AND '
909: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='

Line 927: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '

923: || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_gl_actual_salary, '
924: || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_gl_actual_effort, '
925: || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_gl_overwritten_effort, '
926: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_gl_actual_cost_share '
927: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
928: || 'WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '
929: || '(perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL OR perd.gl_segment3 IS NOT NULL OR perd.gl_segment4 '
930: || 'IS NOT NULL OR perd.gl_segment5 IS NOT NULL OR perd.gl_segment6 IS NOT NULL OR perd.gl_segment7 IS NOT NULL OR '
931: || 'perd.gl_segment8 IS NOT NULL OR perd.gl_segment9 IS NOT NULL OR perd.gl_segment10 IS NOT NULL OR perd.gl_segment11'

Line 956: || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '

952: || 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
953: || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
954: || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
955: || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
956: || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
957: || 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
958: || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
959: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
960: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='

Line 981: || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '

977: || 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
978: || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
979: || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
980: || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
981: || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
982: || 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
983: || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
984: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type(+) = ppa.project_type AND '
985: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='

Line 1006: || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '

1002: || 'TRIM(TO_CHAR(perd.COMMITTED_COST_SHARE, ''999G990D00'')) committed_cost_share, '
1003: || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
1004: || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
1005: || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
1006: || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
1007: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '
1008: || '(perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL OR perd.gl_segment3 IS NOT NULL OR '
1009: || 'perd.gl_segment4 IS NOT NULL OR perd.gl_segment5 IS NOT NULL OR perd.gl_segment6 IS NOT NULL OR '
1010: || 'perd.gl_segment7 IS NOT NULL OR perd.gl_segment8 IS NOT NULL OR perd.gl_segment9 IS NOT NULL OR '

Line 1043: || ' approval_date FROM psp_eff_report_approvals pera, psp_eff_report_details perd '

1039: /*
1040:
1041: query1 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) approver_name, '
1042: || 'TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
1043: || ' approval_date FROM psp_eff_report_approvals pera, psp_eff_report_details perd '
1044: || 'WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
1045: || TO_CHAR(p_effort_report_id) || ' ORDER BY approver_order_num DESC';
1046: */
1047:

Line 1061: || ' approval_date, wfna.TEXT_VALUE note FROM psp_eff_report_approvals pera, psp_eff_report_details perd, WF_NOTIFICATION_ATTRIBUTES wfna '

1057:
1058: IF (l_approval_type <> 'PRE') THEN
1059: query1 := 'SELECT DISTINCT approver_order_num approval_sequence, NVL(wf_role_display_name, wf_role_name) approver_name, '
1060: || 'TO_CHAR(response_date, ''' || l_icx_date_format || ''')'
1061: || ' approval_date, wfna.TEXT_VALUE note FROM psp_eff_report_approvals pera, psp_eff_report_details perd, WF_NOTIFICATION_ATTRIBUTES wfna '
1062: || ' WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND perd.effort_report_id = '
1063: || TO_CHAR(p_effort_report_id) || ' AND wfna.NAME(+) =''WF_NOTE'' AND wfna.NOTIFICATION_ID(+) = pera.NOTIFICATION_ID '
1064: || ' ORDER BY approver_order_num DESC';
1065: ELSE -- Added this ELSE for TGEN bug 6864426

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

1098: || 'pera.pera_information1, pera.pera_information2, pera.pera_information3, pera.pera_information4, pera.pera_information5, '
1099: || 'pera.pera_information6, pera.pera_information7, pera.pera_information8, pera.pera_information9, pera.pera_information10, '
1100: || 'pera.pera_information11, pera.pera_information12, pera.pera_information13, pera.pera_information14, pera.pera_information15, '
1101: || 'pera.pera_information16, pera.pera_information17, pera.pera_information8, pera.pera_information19, pera.pera_information20 '
1102: || 'FROM psp_eff_Reports er, psp_eff_report_details perd, '
1103: || '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 '
1104: || ' perd.effort_report_id = ' || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
1105: || l_er_check || l_sort_option1;
1106:

Line 1171: FROM psp_eff_report_details perd,

1167: CURSOR get_request_id_cur IS
1168: SELECT request_id
1169: FROM psp_eff_reports per
1170: WHERE EXISTS (SELECT 1
1171: FROM psp_eff_report_details perd,
1172: psp_eff_report_approvals pera
1173: WHERE perd.effort_report_id = per.effort_report_id
1174: AND perd.effort_report_detail_id = pera.effort_report_detail_id)
1175: AND ROWNUM = 1;

Line 1318: FROM psp_eff_report_details perd,

1314: CURSOR get_request_id_cur IS
1315: SELECT request_id
1316: FROM psp_eff_reports per
1317: WHERE EXISTS (SELECT 1
1318: FROM psp_eff_report_details perd,
1319: psp_eff_report_approvals pera
1320: WHERE perd.effort_report_id = per.effort_report_id
1321: AND perd.effort_report_detail_id = pera.effort_report_detail_id)
1322: AND ROWNUM = 1;

Line 1520: psp_eff_report_details perd,

1516: p_return_status OUT NOCOPY VARCHAR2) IS
1517: CURSOR get_request_id IS
1518: SELECT request_id
1519: FROM psp_eff_reports per,
1520: psp_eff_report_details perd,
1521: psp_eff_report_approvals pera
1522: WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
1523: AND per.effort_report_id = perd.effort_report_id
1524: AND pera.wf_item_key = p_wf_item_key;

Line 1553: FROM psp_eff_report_details perd,

1549: emp_primary_org_id,
1550: currency_code
1551: FROM psp_eff_reports
1552: WHERE effort_report_id IN (SELECT effort_report_id
1553: FROM psp_eff_report_details perd,
1554: psp_eff_report_approvals pera
1555: WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
1556: AND pera.wf_item_key = p_wf_item_key)
1557: AND status_code <> 'R';

Line 1592: FROM psp_eff_report_details perd,

1588: /***** Converted single xml clob update statement into row by row update
1589: UPDATE psp_eff_reports
1590: 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)
1591: WHERE effort_report_id IN (SELECT effort_report_id
1592: FROM psp_eff_report_details perd,
1593: psp_eff_report_approvals pera
1594: WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
1595: AND pera.wf_item_key = p_wf_item_key)
1596: AND status_code <> 'R';

Line 1726: UPDATE psp_eff_report_details perd

1722: l_project_manager_role := NVL(l_project_manager_role, 'PROJECT MANAGER');
1723:
1724: IF (l_layout_type = 'EMP') THEN
1725: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1726: UPDATE psp_eff_report_details perd
1727: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1728: WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1729: AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1730: AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),

Line 1761: l_query := 'UPDATE psp_eff_report_details SET gl_sum_criteria_segment_name = ' || l_query

1757: l_query := SUBSTR(l_query, 1, LENGTH(l_query) - 11);
1758: CLOSE get_segment_name_cur;
1759:
1760: IF (l_query IS NOT NULL) THEN
1761: l_query := 'UPDATE psp_eff_report_details SET gl_sum_criteria_segment_name = ' || l_query
1762: || ' WHERE effort_report_id IN (SELECT per.effort_report_id FROM psp_eff_reports per WHERE per.request_id = '
1763: || TO_CHAR(p_request_id) || ' AND per.person_id BETWEEN '
1764: || TO_CHAR(p_start_person) || ' AND ' || TO_CHAR(p_end_person) || ') AND '
1765: || '(gl_segment1 IS NOT NULL OR gl_segment2 IS NOT NULL OR gl_segment3 IS NOT NULL OR '

Line 1783: UPDATE psp_eff_report_details perd

1779: -- EXECUTE IMMEDIATE l_query;
1780: END IF;
1781: ELSIF (l_layout_type = 'PIV') THEN
1782: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1783: UPDATE psp_eff_report_details perd
1784: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1785: WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1786: AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1787: AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),

Line 1815: UPDATE psp_eff_report_details perd

1811: committed_cost_share = NVL(committed_cost_share, 0)
1812: WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1813: ELSIF (l_layout_type = 'PMG') THEN
1814: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1815: UPDATE psp_eff_report_details perd
1816: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1817: WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1818: AND paaf.effective_start_date <= r_effort_report.r_end_date(I)
1819: AND paaf.effective_end_date >= r_effort_report.r_start_date(I) AND ROWNUM = 1),

Line 1848: UPDATE psp_eff_report_details perd

1844: committed_cost_share = NVL(committed_cost_share, 0)
1845: WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1846: ELSIF (l_layout_type = 'TMG') THEN
1847: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1848: UPDATE psp_eff_report_details perd
1849: SET assignment_number = (SELECT assignment_number FROM per_all_assignments_f paaf, psp_eff_reports per
1850: WHERE per.effort_report_id = perd.effort_report_id AND paaf.assignment_id = perd.assignment_id
1851: AND paaf.effective_start_date <= per.end_date
1852: AND paaf.effective_end_date >= per.start_date AND ROWNUM = 1),

Line 1873: UPDATE psp_eff_report_details perd

1869: WHERE perd.effort_report_id = r_effort_report.r_effort_report_id(I);
1870: END IF;
1871:
1872: FORALL I IN 1..r_effort_report.r_effort_report_id.COUNT
1873: UPDATE psp_eff_report_details perd
1874: SET (investigator_primary_org_id, investigator_org_name) = (SELECT haou.organization_id, haou.name
1875: FROM hr_all_organization_units haou, per_all_assignments_f paaf
1876: WHERE haou.organization_id = paaf.organization_id AND paaf.person_id = perd.investigator_person_id
1877: AND paaf.effective_start_date <= r_effort_report.r_end_date(I)

Line 1957: sql_stmt := ' Update psp_eff_report_details set project_id = GL_'||l_proj_segment ||

1953: p_awd_sgement => l_awd_sgement,
1954: p_exp_org_segment=> l_exp_org_segment,
1955: p_exp_type_segment => l_exp_type_segment);
1956:
1957: sql_stmt := ' Update psp_eff_report_details set project_id = GL_'||l_proj_segment ||
1958: ' , TASK_ID = GL_'|| l_tsk_segment || ' , AWARD_ID = GL_' || l_awd_sgement || ' , EXPENDITURE_ORGANIZATION_ID = GL_' || l_exp_org_segment
1959: || ' , EXPENDITURE_TYPE = GL_'|| l_exp_type_segment ||' WHERE effort_report_id in (select effort_report_id FROM psp_eff_reports per'
1960: || ' WHERE person_id BETWEEN ' || p_start_person || ' AND ' || p_end_person
1961: || ' AND status_code <> ''R'' AND request_id = ' || p_request_id ||' )';

Line 2111: psp_eff_report_details perd

2107:
2108: CURSOR GLA_effort_report_detail_cur IS
2109: SELECT perd.effort_report_detail_id
2110: FROM psp_eff_reports per,
2111: psp_eff_report_details perd
2112: WHERE per.effort_report_id = perd.effort_report_id
2113: AND per.person_id BETWEEN p_start_person AND p_end_person
2114: AND request_id = p_request_id
2115: AND (perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL

Line 2134: psp_eff_report_details perd

2130:
2131: CURSOR SPO_effort_report_detail_cur IS
2132: SELECT perd.effort_report_detail_id
2133: FROM psp_eff_reports per,
2134: psp_eff_report_details perd
2135: WHERE per.effort_report_id = perd.effort_report_id
2136: AND per.person_id BETWEEN p_start_person AND p_end_person
2137: AND request_id = p_request_id
2138: AND ( perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL

Line 2149: psp_eff_report_details perd

2145:
2146: CURSOR NSP_effort_report_detail_cur IS
2147: SELECT perd.effort_report_detail_id
2148: FROM psp_eff_reports per,
2149: psp_eff_report_details perd
2150: WHERE per.effort_report_id = perd.effort_report_id
2151: AND per.person_id BETWEEN p_start_person AND p_end_person
2152: AND request_id = p_request_id
2153: AND ( perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL

Line 2168: UPDATE psp_eff_report_details perd SET grouping_category = 'GLA'

2164: FETCH GLA_effort_report_detail_cur BULK COLLECT INTO effort_report_detail_id_rec;
2165: CLOSE GLA_effort_report_detail_cur;
2166:
2167: FORALL I IN 1..effort_report_detail_id_rec.COUNT
2168: UPDATE psp_eff_report_details perd SET grouping_category = 'GLA'
2169: WHERE effort_report_detail_id = effort_report_detail_id_rec(I);
2170:
2171: effort_report_detail_id_rec.delete;
2172:

Line 2178: UPDATE psp_eff_report_details perd SET grouping_category = 'SPO'

2174: FETCH SPO_effort_report_detail_cur BULK COLLECT INTO effort_report_detail_id_rec;
2175: CLOSE SPO_effort_report_detail_cur;
2176:
2177: FORALL I IN 1..effort_report_detail_id_rec.COUNT
2178: UPDATE psp_eff_report_details perd SET grouping_category = 'SPO'
2179: WHERE effort_report_detail_id = effort_report_detail_id_rec(I);
2180:
2181: effort_report_detail_id_rec.delete;
2182:

Line 2189: UPDATE psp_eff_report_details perd SET grouping_category = 'NSP'

2185: FETCH NSP_effort_report_detail_cur BULK COLLECT INTO effort_report_detail_id_rec;
2186: CLOSE NSP_effort_report_detail_cur;
2187:
2188: FORALL I IN 1..effort_report_detail_id_rec.COUNT
2189: UPDATE psp_eff_report_details perd SET grouping_category = 'NSP'
2190: WHERE effort_report_detail_id = effort_report_detail_id_rec(I);
2191:
2192: effort_report_detail_id_rec.delete;
2193: