APPS.PSP_ROLLBACK_EFF_REPORTS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 4

PROCEDURE DELETE_EFF_REPORTS(
			errBuf          	OUT NOCOPY VARCHAR2,
 			retCode 	    	OUT NOCOPY VARCHAR2,
		        p_request_id		IN	NUMBER,
		        p_person_id		IN	NUMBER
) IS

	TYPE t_varchar2_240_type is TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;

Line: 16

	select effort_report_id,  person_id
	from psp_eff_reports
	WHERE   request_id   = p_request_id
	AND   nvl(p_person_id, person_id) = person_id ;

Line: 22

	select nvl(PARENT_ITEM_KEY,ITEM_KEY), ITEM_KEY from wf_items_v
	where ITEM_TYPE = 'PSPERAVL'
	and ITEM_KEY in (
	select pera.WF_ITEM_KEY --into l_itemkey
	from psp_eff_Report_approvals pera,
	psp_eff_report_details perd,
	psp_eff_reports per
	where pera.EFFORT_REPORT_DETAIL_ID = perd.EFFORT_REPORT_DETAIL_ID
	and perd.EFFORT_REPORT_ID = per.EFFORT_REPORT_ID
	and per.request_id = p_request_id );

Line: 34

    SELECT 1
    FROM PSP_EFF_REPORTS per ,
    PSP_REPORT_TEMPLATES_H PRTH
    WHERE per.STATUS_CODE ='A'
    AND per.request_id= p_request_id
    and nvl(P_PERSON_ID ,per.person_id)= per.PERSON_ID
    AND PER.REQUEST_ID = PRTH.REQUEST_ID
    AND PRTH.APPROVAL_TYPE <>'PRE'
    and rownum=1;

Line: 45

	l_deleted			BOOLEAN := TRUE;

Line: 66

      SELECT 1 INTO l_appr_exists FROM PSP_EFF_REPORTS per WHERE per.STATUS_CODE ='A'
         AND per.request_id= p_request_id and nvl(P_PERSON_ID ,per.person_id)= per.PERSON_ID
      and rownum=1;

Line: 103

	/*Delete recodrs  for all the initiator thread */

		DELETE fnd_lobs fl
		WHERE EXISTS  (SELECT 1
		FROM fnd_attached_documents fad,
		fnd_documents_vl  fdl
		WHERE fad.pk1_value = l_itemkey(i)
		AND fdl.document_id = fad.document_id
		AND fdl.media_id = fl.file_id
		AND fad.entity_name = 'ERDETAILS');

Line: 114

	/*Delete recodrs  for all the Approver thread */
		DELETE fnd_lobs fl
		WHERE EXISTS  (SELECT 1
		FROM fnd_attached_documents fad,
		fnd_documents_vl  fdl
		WHERE fad.pk1_value = l_itemkey(i)
		AND fdl.document_id = fad.document_id
		AND fdl.media_id = fl.file_id
		AND fad.entity_name = 'ERDETAILS');

Line: 136

           select effort_report_detail_id BULK COLLECT into eff_report_details_rec.effort_report_detail_id
           from psp_eff_report_details where
           effort_report_id =eff_report_master_rec.effort_report_id(i) ;

Line: 140

    Implementation Restriction forall, BULK COLLECT and SELECT do not work together
*/


            FORALL i in 1.. eff_report_master_rec.effort_report_id.count
               delete from psp_eff_report_approvals where effort_report_detail_id in (
       select effort_report_detail_id from psp_eff_report_details where effort_report_id
        = eff_report_master_rec.effort_report_id(i));

Line: 150

               delete from psp_eff_report_details where effort_report_detail_id in (
       select effort_report_detail_id from psp_eff_report_details where effort_report_id
        = eff_report_master_rec.effort_report_id(i));

Line: 156

                Delete from psp_eff_reports where effort_report_id = eff_report_master_rec.effort_report_id(i);

Line: 161

               delete from psp_eff_report_details where effort_report_detail_id = eff_report_details_rec.effort_report_detail_id(i);

Line: 164

		Delete from psp_report_errors where request_id = p_request_id;

Line: 186

END delete_eff_reports;