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_APPROVALS

Line 211: psp_eff_report_approvals pera,

207: CURSOR approver_info_cur IS
208: SELECT DISTINCT pera.wf_role_display_name,
209: haou.name
210: FROM hr_all_organization_units haou,
211: psp_eff_report_approvals pera,
212: per_all_assignments_f paaf,
213: fnd_user fu
214: WHERE pera.wf_item_key = p_wf_item_key
215: AND fu.employee_id = paaf.person_id

Line 226: psp_eff_report_approvals pera,

222: SELECT per.template_id,
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

Line 375: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id AND '

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;
378: ELSE
379: query1 := 'SELECT person_xml FROM psp_eff_reports per WHERE status_code IN (''A'', ''N'') AND '

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

417: IF (p_request_id IS NULL) THEN
418: query1 := 'SELECT investigator_person_id, investigator_name, investigator_org_name, investigator_primary_org_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'

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 705: l_er_check := ' AND NOT EXISTS (SELECT 1 FROM psp_eff_report_details perd1, psp_eff_report_approvals pera1 '

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

Line 708: || 'AND NVL(pera.approver_order_num, 1) = (SELECT NVL(MAX(pera1.approver_order_num), 1) FROM psp_eff_report_approvals pera1 '

704:
705: l_er_check := ' AND NOT EXISTS (SELECT 1 FROM psp_eff_report_details perd1, psp_eff_report_approvals pera1 '
706: || 'WHERE perd.effort_report_id = perd1.effort_report_id AND perd1.effort_report_detail_id = pera1.effort_report_detail_id '
707: || 'AND pera1.approval_status = ''R'') '
708: || 'AND NVL(pera.approver_order_num, 1) = (SELECT NVL(MAX(pera1.approver_order_num), 1) FROM psp_eff_report_approvals pera1 '
709: || 'WHERE pera1.effort_report_detail_id = perd.effort_report_detail_id)';
710:
711: IF (l_layout_type = 'EMP') THEN
712: l_employee_info := '

Line 726: || ' psp_eff_report_approvals pera '

722: query1 := 'select distinct pera.eff_information1, pera.eff_information2, pera.eff_information3,pera.eff_information4, pera.eff_information5, '
723: || ' pera.eff_information6,pera.eff_information7, pera.eff_information8 , pera.eff_information9,pera.eff_information10, '
724: || ' pera.eff_information11, pera.eff_information12, pera.eff_information13, pera.eff_information14, pera.eff_information15 '
725: || ' FROM psp_eff_report_details perd, '
726: || ' psp_eff_report_approvals pera '
727: || ' WHERE perd.effort_report_detail_id = pera.effort_report_detail_id '
728: || ' AND perd.effort_report_id = ' || p_effort_report_id
729: || ' AND APPROVER_ORDER_NUM = (SELECT max(APPROVER_ORDER_NUM) '
730: || ' FROM psp_eff_report_approvals pera2, '

Line 730: || ' FROM psp_eff_report_approvals pera2, '

726: || ' psp_eff_report_approvals pera '
727: || ' WHERE perd.effort_report_detail_id = pera.effort_report_detail_id '
728: || ' AND perd.effort_report_id = ' || p_effort_report_id
729: || ' AND APPROVER_ORDER_NUM = (SELECT max(APPROVER_ORDER_NUM) '
730: || ' FROM psp_eff_report_approvals pera2, '
731: || ' psp_eff_report_details perd2 '
732: || ' WHERE pera2.effort_report_detail_id = perd2.effort_report_detail_id '
733: || ' AND perd2.effort_report_id = perd.effort_report_id) ';
734:

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

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

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

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

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

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

Line 857: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '

853: || 'pera.pera_information6, pera.pera_information7, pera.pera_information8, pera.pera_information9, pera.pera_information10, '
854: || 'pera.pera_information11, pera.pera_information12, pera.pera_information13, pera.pera_information14, pera.pera_information15, '
855: || 'pera.pera_information16, pera.pera_information17, pera.pera_information8, pera.pera_information19, pera.pera_information20 '
856: || 'FROM psp_eff_report_details perd, '
857: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '
858: || TO_CHAR(p_effort_report_id) || ' AND NVL(pera.approval_status, ''A'') IN (''P'', ''A'')'
859: || 'AND perd.grouping_category = ''' || l_grouping_category || ''' '
860: || l_assignment_check || l_sort_option1;
861: qryCtx1 := dbms_xmlgen.newContext(query1);

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

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

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

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

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

927: || 'TRIM(TO_CHAR(SUM(NVL(actual_salary_amt, 0)), ''' || l_num25_fmask || ''')) total_gl_actual_salary, '
928: || 'TRIM(TO_CHAR(SUM(NVL(payroll_percent, 0)), ''999G990D00'')) total_gl_actual_effort, '
929: || 'TRIM(TO_CHAR(SUM(NVL(pera.overwritten_effort_percent, 0)), ''999G990D00'')) total_gl_overwritten_effort, '
930: || 'TRIM(TO_CHAR(SUM(NVL(pera.actual_cost_share, 0)), ''999G990D00'')) total_gl_actual_cost_share '
931: || 'FROM psp_eff_report_details perd, psp_eff_report_approvals pera '
932: || 'WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '
933: || '(perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL OR perd.gl_segment3 IS NOT NULL OR perd.gl_segment4 '
934: || '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 '
935: || '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 961: || 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '

957: || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
958: || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
959: || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
960: || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
961: || 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '
962: || 'IS NOT NULL OR perd.expenditure_organization_id IS NOT NULL OR perd.expenditure_type IS NOT NULL) AND EXISTS '
963: || '(SELECT 1 FROM pa_projects_all ppa, gms_project_types_all gpta WHERE gpta.project_type = ppa.project_type AND '
964: || 'ppa.project_id = perd.project_id AND ppa.project_type <> ''AWARD_PROJECT'' AND NVL(gpta.sponsored_flag, ''N'') ='
965: || '''Y'') AND perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND effort_report_id = '

Line 986: || 'psp_eff_report_approvals pera WHERE (perd.project_id IS NOT NULL OR perd.task_id IS NOT NULL OR perd.award_id '

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

Line 1011: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '

1007: || 'TRIM(TO_CHAR(perd.SCHEDULE_START_DATE, ''' || l_icx_date_format || ''')) schedule_start_date, '
1008: || 'TRIM(TO_CHAR(perd.SCHEDULE_END_DATE, ''' || l_icx_date_format || ''')) schedule_end_date, '
1009: || 'perd.*, TRIM(TO_CHAR(NVL(pera.overwritten_effort_percent, 0), ''999G990D00'')) overwritten_effort_percent, '
1010: || 'TRIM(TO_CHAR(NVL(pera.actual_cost_share, 0), ''999G990D00'')) actual_cost_share FROM psp_eff_report_details perd, '
1011: || 'psp_eff_report_approvals pera WHERE perd.effort_report_detail_id = pera.effort_report_detail_id (+) AND '
1012: || '(perd.gl_segment1 IS NOT NULL OR perd.gl_segment2 IS NOT NULL OR perd.gl_segment3 IS NOT NULL OR '
1013: || 'perd.gl_segment4 IS NOT NULL OR perd.gl_segment5 IS NOT NULL OR perd.gl_segment6 IS NOT NULL OR '
1014: || 'perd.gl_segment7 IS NOT NULL OR perd.gl_segment8 IS NOT NULL OR perd.gl_segment9 IS NOT NULL OR '
1015: || 'perd.gl_segment10 IS NOT NULL OR perd.gl_segment11 IS NOT NULL OR perd.gl_segment12 IS NOT NULL OR '

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

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

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

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

Line 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 '

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:
1111: qryCtx1 := dbms_xmlgen.newContext(query1);

Line 1178: psp_eff_report_approvals pera

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
1180: AND perd.effort_report_detail_id = pera.effort_report_detail_id)
1181: AND ROWNUM = 1;
1182:

Line 1327: psp_eff_report_approvals pera

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
1329: AND perd.effort_report_detail_id = pera.effort_report_detail_id)
1330: AND ROWNUM = 1;
1331:

Line 1532: psp_eff_report_approvals pera

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
1535: AND pera.wf_item_key = p_wf_item_key;
1536:

Line 1565: psp_eff_report_approvals pera

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
1567: AND pera.wf_item_key = p_wf_item_key)
1568: AND status_code <> 'R';
1569:

Line 1604: psp_eff_report_approvals pera

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
1605: WHERE perd.effort_report_detail_id = pera.effort_report_detail_id
1606: AND pera.wf_item_key = p_wf_item_key)
1607: AND status_code <> 'R';
1608: End of changes for bug fix 4429787 *****/