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.PO_MASS_UPDATE_REQ_PVT SQL Statements

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

Line: 14


g_pkg_name                   CONSTANT VARCHAR2(100) := 'PO_Mass_Update_Req_PVT';

Line: 49


PROCEDURE DO_Update(p_update_person    IN VARCHAR2,
                    p_old_personid     IN NUMBER,
                    p_new_personid     IN NUMBER,
                    p_document_type    IN VARCHAR2,
                    p_document_no_from IN VARCHAR2,
                    p_document_no_to   IN VARCHAR2,
                    p_date_from        IN DATE,
                    p_date_to          IN DATE,
		    p_commit_interval  IN NUMBER,
		    p_msg_data         OUT NOCOPY  VARCHAR2,
                    p_msg_count        OUT NOCOPY  NUMBER,
                    p_return_status    OUT NOCOPY  VARCHAR2) IS

l_progress          VARCHAR2(3) := '000';

Line: 63


l_log_head          CONSTANT VARCHAR2(1000) := g_log_head||'Do_Update';

Line: 71


	PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 82


SAVEPOINT Do_Update_SP;

Line: 86


	IF (p_update_person = 'PREPARER' OR p_update_person = 'ALL') THEN

		BEGIN

			l_progress := '002';

Line: 94


				PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 98


			SAVEPOINT PO_Mass_Update_Preparer_SP;

Line: 100


			Update_Preparer(p_update_person,
				        p_old_personid,
			                p_new_personid,
			                p_document_type,
			                p_document_no_from,
			                p_document_no_to,
			                p_date_from,
			                p_date_to,
			                p_commit_interval,
					p_msg_data,
                                        p_msg_count,
	                                l_return_status);

Line: 114


				   ROLLBACK TO PO_Mass_Update_Preparer_SP;

Line: 122


			ROLLBACK TO PO_Mass_Update_Preparer_SP;

Line: 140


	IF (p_update_person = 'APPROVER' OR p_update_person = 'ALL') THEN

		BEGIN

			l_progress := '003';

Line: 148


				PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 152


			SAVEPOINT PO_Mass_Update_Approver_SP;

Line: 154


			Update_Approver(p_update_person,
					p_old_personid,
			                p_new_personid,
			                p_document_type,
			                p_document_no_from,
			                p_document_no_to,
			                p_date_from,
			                p_date_to,
					p_commit_interval,
					p_msg_data,
                                        p_msg_count,
	                                l_return_status);

Line: 168


				   ROLLBACK TO PO_Mass_Update_Approver_SP;

Line: 176


			ROLLBACK TO PO_Mass_Update_Approver_SP;

Line: 194


	IF (p_update_person = 'REQUESTOR' OR p_update_person = 'ALL') THEN

		BEGIN

			l_progress := '004';

Line: 202


				PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 206


			SAVEPOINT PO_Mass_Update_Requestor_SP;

Line: 208


			Update_Requestor(p_update_person,
					 p_old_personid,
					 p_new_personid,
					 p_document_type,
					 p_document_no_from,
					 p_document_no_to,
					 p_date_from,
					 p_date_to,
					 p_commit_interval,
					 p_msg_data,
					 p_msg_count,
					 l_return_status);

Line: 222


				   ROLLBACK TO PO_Mass_Update_Requestor_SP;

Line: 232


			ROLLBACK TO PO_Mass_Update_Requestor_SP;

Line: 254


ROLLBACK TO Do_Update_SP;

Line: 268


END DO_Update;

Line: 299


PROCEDURE Update_Preparer (p_update_person    IN VARCHAR2,
			   p_old_personid     IN NUMBER,
                           p_new_personid     IN NUMBER,
                           p_document_type    IN VARCHAR2,
                           p_document_no_from IN VARCHAR2,
                           p_document_no_to   IN VARCHAR2,
                           p_date_from        IN DATE,
                           p_date_to          IN DATE,
                           p_commit_interval  IN NUMBER,
			   p_msg_data         OUT NOCOPY  VARCHAR2,
                           p_msg_count        OUT NOCOPY  NUMBER,
                           p_return_status    OUT NOCOPY  VARCHAR2) IS

c_req                     g_req;

Line: 318


l_log_head                CONSTANT VARCHAR2(1000) := g_log_head||'Update_Preparer';

Line: 347


	PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 358


SAVEPOINT  PO_Mass_Update_Preparer_SP;

Line: 370


	SAVEPOINT Update_Preparer_SP;

Line: 398


	IF (p_update_person = 'PREPARER' OR p_update_person = 'ALL') THEN

		Print_Output(p_update_person,
			     p_old_preparer_name,
		             p_new_preparer_name,
			     p_org_name,
			     p_document_type,
			     p_document_no_from,
		             p_document_no_to,
			     p_date_from,
		             p_date_to,
			     p_msg_data,
			     p_msg_count,
		             p_return_status);

Line: 415


	SELECT  manual_req_num_type
	  INTO  req_num_type
	  FROM  po_system_parameters;

Line: 434


	ROLLBACK TO Update_Preparer_SP;

Line: 450


stmt_req := 'SELECT por.ROWID,
		    por.segment1,
		    pdt.type_name,
		    por.authorization_status,
		    por.wf_item_type,
		    por.wf_item_key
	       FROM po_requisition_headers por,
		    po_document_types_vl pdt
	      WHERE por.preparer_id = PO_MASS_UPDATE_REQ_PVT.get_old_personid
	        AND pdt.document_type_code IN (''REQUISITION'')
	        AND pdt.document_subtype = por.type_lookup_code
		AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'',''RETURNED'')
	        AND Nvl(por.cancel_flag,''N'') = ''N''';

Line: 466


		stmt_req := stmt_req || ' AND por.type_lookup_code = PO_MASS_UPDATE_REQ_PVT.get_document_type';

Line: 478


			stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_from)';

Line: 482


			stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_to)';

Line: 488


						      BETWEEN to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_to )';

Line: 500


			stmt_req := stmt_req || ' AND POR.SEGMENT1 >= PO_MASS_UPDATE_REQ_PVT.get_document_no_from';

Line: 504


			stmt_req := stmt_req || ' AND POR.SEGMENT1 <= PO_MASS_UPDATE_REQ_PVT.get_document_no_to';

Line: 508


			stmt_req := stmt_req || ' AND POR.SEGMENT1 BETWEEN PO_MASS_UPDATE_REQ_PVT.get_document_no_from AND PO_MASS_UPDATE_REQ_PVT.get_document_no_to';

Line: 522


		stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_from)';

Line: 526


		stmt_req := stmt_req || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_to)';

Line: 529


	        stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_from)
		                          AND POR.creation_date < Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_to)+1';

Line: 553


SAVEPOINT Update_Preparer_RECREQ_SP;

Line: 568


    UPDATE po_requisition_headers_all
       SET preparer_id = p_new_personid,
           last_update_date  = sysdate,
           last_updated_by   = fnd_global.user_id,
           last_update_login = fnd_global.login_id
     WHERE rowid = l_req_rowid;

Line: 663


  ROLLBACK TO Update_Preparer_RECREQ_SP;

Line: 691


ROLLBACK TO PO_Mass_Update_Buyer_SP;

Line: 701


END Update_Preparer;

Line: 732


PROCEDURE Update_Approver(p_update_person    IN VARCHAR2,
			  p_old_personid     IN NUMBER,
                          p_new_personid     IN NUMBER,
                          p_document_type    IN VARCHAR2,
                          p_document_no_from IN VARCHAR2,
                          p_document_no_to   IN VARCHAR2,
                          p_date_from        IN DATE,
                          p_date_to          IN DATE,
                          p_commit_interval  IN NUMBER,
			  p_msg_data         OUT NOCOPY  VARCHAR2,
                          p_msg_count        OUT NOCOPY  NUMBER,
                          p_return_status    OUT NOCOPY  VARCHAR2) IS

c_req_approver            g_req_approver;

Line: 752


l_log_head                CONSTANT VARCHAR2(1000) := g_log_head||'Update_Approver';

Line: 773


	PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 784


SAVEPOINT  PO_Mass_Update_Approver_SP;

Line: 796


	SAVEPOINT Update_Approver_SP;

Line: 827


	IF (p_update_person = 'APPROVER' ) THEN

		Print_Output(p_update_person,
			     p_old_preparer_name,
		             p_new_preparer_name,
			     p_org_name,
			     p_document_type,
			     p_document_no_from,
		             p_document_no_to,
			     p_date_from,
		             p_date_to,
			     p_msg_data,
			     p_msg_count,
		             p_return_status);

Line: 845


	SELECT  manual_req_num_type
	  INTO  req_num_type
	  FROM  po_system_parameters;

Line: 867


	ROLLBACK TO Update_Approver_SP;

Line: 884


stmt_req := 'SELECT  wfn.notification_id,
        por.segment1,
        pdt.type_name
  FROM  wf_notifications wfn,
        wf_item_activity_statuses wfa,
        po_requisition_headers por,
        po_document_types_vl pdt
 WHERE  wfn.notification_id = wfa.notification_id
   AND  wfa.item_type       = por.wf_item_type
   AND  wfa.item_key        = por.wf_item_key
   AND  wfn.status NOT IN (''CLOSED'',''CANCELED'')
   AND  Nvl(por.authorization_status,''INCOMPLETE'') IN (''IN PROCESS'',''PRE-APPROVED'')
   AND  wfn.recipient_role = PO_Mass_Update_Req_PVT.get_old_username
   AND  pdt.document_type_code in (''REQUISITION'')
   AND  pdt.document_subtype = por.type_lookup_code';

Line: 902


		stmt_req := stmt_req || ' AND por.type_lookup_code = PO_Mass_Update_Req_PVT.get_document_type';

Line: 913


			stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) >= to_number(PO_Mass_Update_Req_PVT.get_document_no_from)';

Line: 917


			stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) <= to_number(PO_Mass_Update_Req_PVT.get_document_no_to)';

Line: 923


						      BETWEEN to_number(PO_Mass_Update_Req_PVT.get_document_no_from) AND to_number(PO_Mass_Update_Req_PVT.get_document_no_to ||)';

Line: 935


			stmt_req := stmt_req || ' AND POR.SEGMENT1 >= PO_Mass_Update_Req_PVT.get_document_no_from';

Line: 939


			stmt_req := stmt_req || ' AND POR.SEGMENT1 <= PO_Mass_Update_Req_PVT.get_document_no_to';

Line: 943


			stmt_req := stmt_req || ' AND POR.SEGMENT1 BETWEEN PO_Mass_Update_Req_PVT.get_document_no_from AND PO_Mass_Update_Req_PVT.get_document_no_to';

Line: 957


		stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_Mass_Update_Req_PVT.get_date_from)';

Line: 961


		stmt_req := stmt_req || ' AND POR.creation_date <= Trunc(PO_Mass_Update_Req_PVT.get_date_to)';

Line: 964


	        stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_Mass_Update_Req_PVT.get_date_from)
		                          AND POR.creation_date < Trunc(PO_Mass_Update_Req_PVT.get_date_to)+1';

Line: 995


	SAVEPOINT Update_Req_Forward_SP;

Line: 1036


	ROLLBACK TO Update_Req_Forward_SP;

Line: 1067


ROLLBACK TO PO_Mass_Update_Approver_SP;

Line: 1079


END Update_Approver;

Line: 1108


PROCEDURE Update_Requestor(p_update_person    IN VARCHAR2,
			   p_old_personid     IN NUMBER,
                           p_new_personid     IN NUMBER,
                           p_document_type    IN VARCHAR2,
                           p_document_no_from IN VARCHAR2,
                           p_document_no_to   IN VARCHAR2,
                           p_date_from        IN DATE,
                           p_date_to          IN DATE,
                           p_commit_interval  IN NUMBER,
			   p_msg_data         OUT NOCOPY  VARCHAR2,
                           p_msg_count        OUT NOCOPY  NUMBER,
                           p_return_status    OUT NOCOPY  VARCHAR2) IS

c_req                     g_req;

Line: 1128


l_log_head                CONSTANT VARCHAR2(1000) := g_log_head||'Update_Requestor';

Line: 1147


	PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 1158


SAVEPOINT  PO_Mass_Update_Requestor_SP;

Line: 1170


	SAVEPOINT Update_Requestor_SP;

Line: 1198


	IF (p_update_person = 'REQUESTOR' ) THEN

		Print_Output(p_update_person,
			     p_old_preparer_name,
		             p_new_preparer_name,
			     p_org_name,
			     p_document_type,
			     p_document_no_from,
		             p_document_no_to,
			     p_date_from,
		             p_date_to,
			     p_msg_data,
			     p_msg_count,
		             p_return_status);

Line: 1215


	SELECT  manual_req_num_type
	  INTO  req_num_type
	  FROM  po_system_parameters;

Line: 1234


	ROLLBACK TO Update_Requestor_SP;

Line: 1252


stmt_req := 'SELECT prl.ROWID,
       por.segment1,
       pdt.type_name
  FROM po_requisition_headers por,
       po_document_types_vl pdt,
       po_requisition_lines_all prl
 WHERE prl.to_person_id = PO_MASS_UPDATE_REQ_PVT.get_old_personid
   AND por.requisition_header_id = prl.requisition_header_id
   AND pdt.document_type_code IN (''REQUISITION'')
   AND pdt.document_subtype = por.type_lookup_code
   AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'',''RETURNED'')
   AND Nvl(por.cancel_flag,''N'') = ''N''';

Line: 1267


		stmt_req := stmt_req || ' AND por.type_lookup_code = PO_MASS_UPDATE_REQ_PVT.get_document_type';

Line: 1278


			stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_from';

Line: 1282


			stmt_req := stmt_req || ' AND DECODE ( RTRIM ( POR.SEGMENT1,''0123456789'' ), NULL, To_Number ( POR.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_to)';

Line: 1288


						      BETWEEN to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_from) AND to_number(PO_MASS_UPDATE_REQ_PVT.get_document_no_to)';

Line: 1300


			stmt_req := stmt_req || ' AND POR.SEGMENT1 >= PO_MASS_UPDATE_REQ_PVT.get_document_no_from';

Line: 1304


			stmt_req := stmt_req || ' AND POR.SEGMENT1 <= PO_MASS_UPDATE_REQ_PVT.get_document_no_to';

Line: 1308


			stmt_req := stmt_req || ' AND POR.SEGMENT1 BETWEEN PO_MASS_UPDATE_REQ_PVT.get_document_no_from AND PO_MASS_UPDATE_REQ_PVT.get_document_no_to';

Line: 1322


		stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_from)';

Line: 1326


		stmt_req := stmt_req || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_to)';

Line: 1329


	        stmt_req := stmt_req || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_from)
		                          AND POR.creation_date < Trunc(PO_MASS_UPDATE_REQ_PVT.get_date_to)+1';

Line: 1350


SAVEPOINT Update_Requestor_RECREQ_SP;

Line: 1362


   UPDATE po_requisition_lines_all
      SET to_person_id = p_new_personid,
          last_update_date  = sysdate,
          last_updated_by   = fnd_global.user_id,
          last_update_login = fnd_global.login_id
    WHERE rowid = l_req_rowid;

Line: 1408


   ROLLBACK TO Update_Requestor_RECREQ_SP;

Line: 1438


ROLLBACK TO PO_Mass_Update_Requestor_SP;

Line: 1450


END Update_Requestor;

Line: 1481


PROCEDURE Print_Output(p_update_person       IN VARCHAR2,
		       p_old_preparer_name   IN VARCHAR2,
                       p_new_preparer_name   IN VARCHAR2,
                       p_org_name            IN VARCHAR2,
                       p_document_type       IN VARCHAR2,
                       p_document_no_from    IN VARCHAR2,
                       p_document_no_to      IN VARCHAR2,
                       p_date_from           IN DATE,
                       p_date_to             IN DATE,
		       p_msg_data            OUT NOCOPY  VARCHAR2,
                       p_msg_count           OUT NOCOPY  NUMBER,
                       p_return_status       OUT NOCOPY  VARCHAR2) IS

l_msg1             VARCHAR2(240);

Line: 1519


	PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 1564


     IF (p_update_person = 'PREPARER') THEN

	fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER2');

Line: 1570


     ELSIF (p_update_person = 'APPROVER') THEN

	fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER3');

Line: 1576


     ELSIF (p_update_person = 'REQUESTOR') THEN

	fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER4');

Line: 1582


     ELSIF (p_update_person = 'ALL') THEN

	fnd_message.set_name('PO','PO_MUB_MSG_PREPARER_HEADER5');

Line: 1594


	PO_DEBUG.debug_var(l_log_head,l_progress,'p_update_person',p_update_person );

Line: 1722


SELECT org_id
  INTO l_org_id
  FROM po_system_parameters;

Line: 1726


SELECT hou.name
  INTO p_org_name
  FROM hr_all_organization_units hou,
       hr_all_organization_units_tl hout
 WHERE hou.organization_id = hout.organization_id
   AND  hout.LANGUAGE = UserEnv('LANG')
   AND hou.organization_id = l_org_id;