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_PO_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_PO_PVT';

Line: 55


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_supplier_id      IN NUMBER,
                    p_include_close_po IN VARCHAR2,
		    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: 71


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

Line: 80


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

Line: 93


SAVEPOINT Do_Update_SP;

Line: 97


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

		BEGIN

			l_progress := '002';

Line: 105


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

Line: 109


			SAVEPOINT PO_Mass_Update_Buyer_SP;

Line: 112


				SELECT 'Y' INTO x_valid_buyer
				  FROM po_buyers_val_v
				 WHERE employee_id = p_new_personid;

Line: 125


				Update_Buyer(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_supplier_id,
                        		p_include_close_po,
                        		p_commit_interval,
                        		p_msg_data,
					p_msg_count,
					l_return_status);

Line: 141


				   ROLLBACK TO PO_Mass_Update_Buyer_SP;

Line: 151


			ROLLBACK TO PO_Mass_Update_Buyer_SP;

Line: 169


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

		BEGIN

			l_progress := '003';

Line: 177


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

Line: 181


			SAVEPOINT PO_Mass_Update_Approver_SP;

Line: 183


			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_supplier_id,
					p_include_close_po,
					p_commit_interval,
					p_msg_data,
					p_msg_count,
					l_return_status);

Line: 199


				   ROLLBACK TO PO_Mass_Update_Approver_SP;

Line: 208


			ROLLBACK TO PO_Mass_Update_Approver_SP;

Line: 226


	IF (p_update_person = 'DELIVER TO' OR p_update_person = 'ALL') THEN

		BEGIN

			l_progress := '004';

Line: 234


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

Line: 238


			SAVEPOINT PO_Mass_Update_Deliver_To_SP;

Line: 240


			Update_Deliver_To(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_supplier_id,
					  p_include_close_po,
					  p_commit_interval,
					  p_msg_data,
					  p_msg_count,
					  l_return_status);

Line: 256


				   ROLLBACK TO PO_Mass_Update_Deliver_To_SP;

Line: 267


			ROLLBACK TO PO_Mass_Update_Deliver_To_SP;

Line: 289


	ROLLBACK TO Do_Update_SP;

Line: 303


END DO_Update;

Line: 336


PROCEDURE Update_Buyer (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_supplier_id      IN NUMBER,
                        p_include_close_po IN VARCHAR2,
			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_po g_po;

Line: 359


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

Line: 405


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

Line: 418


SAVEPOINT  PO_Mass_Update_Buyer_SP;

Line: 430


	SAVEPOINT Update_Buyer_REC_SP;

Line: 462


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

		Print_Output(p_update_person,
			     p_old_buyer_name,
		             p_new_buyer_name,
			     p_org_name,
			     p_document_type,
			     p_document_no_from,
		             p_document_no_to,
			     p_date_from,
		             p_date_to,
			     p_supplier_name,
			     p_msg_data,
			     p_msg_count,
		             p_return_status);

Line: 480


	SELECT  manual_po_num_type
	  INTO  po_num_type
	  FROM  po_system_parameters;

Line: 499


	ROLLBACK TO Update_Buyer_REC_SP;

Line: 515


stmt_po := 'SELECT poh.ROWID,
	           poh.segment1,
		   pdt.type_name,
		   poh.authorization_status,
		   poh.wf_item_type,
		   poh.wf_item_key,
		   poh.po_header_id,
		   poh.type_lookup_code,
		   poh.revision_num,
		   Nvl(poh.conterms_exist_flag, ''N'')
	      FROM po_headers poh,
		   po_document_types_vl pdt
             WHERE poh.agent_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
	       AND pdt.document_type_code IN (''PO'',''PA'')
	       AND pdt.document_subtype = poh.type_lookup_code
	       AND Nvl(poh.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
	       AND Nvl(poh.cancel_flag,''N'') = ''N''
	       AND Nvl(poh.frozen_flag,''N'') = ''N'' ';

Line: 536


		stmt_po := stmt_po || ' AND poh.type_lookup_code = PO_MASS_UPDATE_PO_PVT.get_document_type';

Line: 548


			stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from) ';

Line: 552


			stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to ||) ';

Line: 558


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

Line: 570


			stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';

Line: 574


			stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 578


			stmt_po := stmt_po || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 592


		stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';

Line: 596


		stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';

Line: 599


	        stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
		                        AND POH.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';

Line: 607


		stmt_po := stmt_po || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';

Line: 644


SAVEPOINT Update_Buyer_REC_PO_SP;

Line: 664


       UPDATE po_headers_all
       SET agent_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_po_rowid;

Line: 672


       UPDATE po_headers_archive_all
       SET  agent_id = p_new_personid,
	    last_update_date  = sysdate,
	    last_updated_by   = fnd_global.user_id,
	    last_update_login = fnd_global.login_id
       WHERE po_header_id=l_document_id
       AND   latest_external_flag= 'Y';

Line: 736


                        POXPOPDF uses 'USER_ID' and not 'BUYER_USER_ID', so this will make sure that, even after 'Mass Update' is run to
                        change a terminated buyer, POXPOPDF would not run with the terminated user's USER_ID
                        */
                        po_wf_util_pkg.SetItemAttrText ( itemtype   => l_itemtype,
                                                         itemkey    => l_itemkey,
                                                         aname      => 'USER_ID' ,
                                                         avalue     =>  p_new_buyer_user_id);

Line: 771


    Added this condition to take care of the case in which Mass Update is run on an 'Approved' PO.
    */
    ELSIF (l_auth_status='APPROVED') THEN

        l_buyer_user_id := po_wf_util_pkg.GetItemAttrText ( itemtype   => l_itemtype,
                                                            itemkey    => l_itemkey,
                                                            aname      => 'BUYER_USER_ID');

Line: 834


		okc_manage_deliverables_grp.updateIntContactOnDeliverables (
			p_api_version                  => 1.0,
			p_init_msg_list                => FND_API.G_FALSE,
	                p_commit                       => FND_API.G_FALSE,
		        p_bus_docs_tbl                 => l_busdocs_tbl,
	                p_original_internal_contact_id => p_old_personid,
		        p_new_internal_contact_id      => p_new_personid,
	                x_msg_data                     => p_msg_data,
		        x_msg_count                    => p_msg_count,
	                x_return_status                => p_return_status);

Line: 874


ROLLBACK TO Update_Buyer_REC_PO_SP;

Line: 897


	okc_manage_deliverables_grp.updateIntContactOnDeliverables (
		  p_api_version                  => 1.0,
                  p_init_msg_list                => FND_API.G_FALSE,
                  p_commit                       => FND_API.G_FALSE,
                  p_bus_docs_tbl                 => l_busdocs_tbl,
                  p_original_internal_contact_id => p_old_personid,
                  p_new_internal_contact_id      => p_new_personid,
                  x_msg_data                     => p_msg_data,
                  x_msg_count                    => p_msg_count,
                  x_return_status                => p_return_status);

Line: 919


stmt_rel := 'SELECT por.ROWID,
		    poh.segment1,
		    por.release_num,
		    pdt.type_name,
		    por.authorization_status,
		    por.wf_item_type,
		    por.wf_item_key,
		    por.po_release_id --8846315 fix
	       FROM po_headers poh,
		    po_releases por,
		    po_document_types_vl pdt
	      WHERE poh.po_header_id = por.po_header_id
		AND por.agent_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
		AND pdt.document_type_code   = ''RELEASE''
	        AND pdt.document_subtype     = por.release_type
		AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
		AND Nvl(por.cancel_flag,''N'') = ''N''
		AND Nvl(por.frozen_flag,''N'') = ''N''';

Line: 941


		stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')

	                                  OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';

Line: 956


			stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';

Line: 960


			stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';

Line: 966


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

Line: 978


			stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';

Line: 982


			stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 986


			stmt_rel := stmt_rel || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 1001


		stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';

Line: 1005


		stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';

Line: 1009


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

Line: 1017


		stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';

Line: 1054


	SAVEPOINT Update_Buyer_REC_REL_SP;

Line: 1071


           UPDATE po_releases_all
           SET agent_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_rel_rowid;

Line: 1079


	   UPDATE po_releases_archive_all
	   SET  agent_id = p_new_personid,
	        last_update_date  = sysdate,
		last_updated_by   = fnd_global.user_id,
		last_update_login = fnd_global.login_id
	   WHERE po_release_id=l_document_id
	   AND   latest_external_flag= 'Y';

Line: 1202


	ROLLBACK TO Update_Buyer_REC_REL_SP;

Line: 1232


ROLLBACK TO PO_Mass_Update_Buyer_SP;

Line: 1242


END Update_Buyer;

Line: 1275


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_supplier_id      IN NUMBER,
                          p_include_close_po IN VARCHAR2,
			  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_po_approver             g_po_approver;

Line: 1301


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

Line: 1324


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

Line: 1337


SAVEPOINT  PO_Mass_Update_Approver_SP;

Line: 1349


	SAVEPOINT Update_Approver_REC_SP;

Line: 1383


	IF (p_update_person = 'APPROVER' ) THEN

		Print_Output(p_update_person,
			     p_old_buyer_name,
		             p_new_buyer_name,
			     p_org_name,
			     p_document_type,
			     p_document_no_from,
		             p_document_no_to,
			     p_date_from,
		             p_date_to,
			     p_supplier_name,
			     p_msg_data,
			     p_msg_count,
		             p_return_status);

Line: 1401


	SELECT  manual_po_num_type
	  INTO  po_num_type
	  FROM  po_system_parameters;

Line: 1423


	ROLLBACK TO Update_Approver_REC_SP;

Line: 1440


stmt_po := 'SELECT  wfn.notification_id,
        poh.segment1,
        pdt.type_name
  FROM  wf_notifications wfn,
        wf_item_activity_statuses wfa,
        po_headers poh,
        po_document_types_vl pdt
 WHERE  wfn.notification_id = wfa.notification_id
   AND  wfa.item_type       = poh.wf_item_type
   AND  wfa.item_key        = poh.wf_item_key
   AND  wfn.status NOT IN (''CLOSED'',''CANCELED'')
   AND  Nvl(poh.authorization_status,''INCOMPLETE'') IN (''IN PROCESS'',''PRE-APPROVED'')
   AND  wfn.recipient_role = PO_Mass_Update_PO_PVT.get_old_username
   AND  pdt.document_type_code in (''PO'',''PA'')
   AND  pdt.document_subtype = poh.type_lookup_code';

Line: 1459


		stmt_po := stmt_po || ' AND poh.type_lookup_code = PO_MASS_UPDATE_PO_PVT.get_document_type';

Line: 1472


			stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';

Line: 1476


			stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';

Line: 1482


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

Line: 1494


			stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';

Line: 1498


			stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 1502


			stmt_po := stmt_po || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 1516


		stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';

Line: 1520


		stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';

Line: 1524


		stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
		                        AND POH.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';

Line: 1532


		stmt_po := stmt_po || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';

Line: 1572


	SAVEPOINT Mass_Update_Forward_SP;

Line: 1615


		ROLLBACK TO Mass_Update_Forward_SP;

Line: 1640


		stmt_rel := 'SELECT  wfn.notification_id,
				     poh.segment1,
				     por.release_num,
				     pdt.type_name
			       FROM  wf_notifications wfn,
				     wf_item_activity_statuses wfa,
				     po_headers poh,
				     po_releases 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 recipient_role         = PO_Mass_Update_PO_PVT.get_old_username
			        AND por.po_header_id       = poh.po_header_id
			        AND pdt.document_type_code = ''RELEASE''
			        AND pdt.document_subtype   = por.release_type';

Line: 1662


			stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')

						  OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';

Line: 1676


				stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';

Line: 1680


				stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';

Line: 1686


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

Line: 1698


				stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';

Line: 1702


				stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 1706


				stmt_rel := stmt_rel || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 1720


			stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';

Line: 1724


			stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';

Line: 1728


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

Line: 1735


			stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';

Line: 1775


			SAVEPOINT Mass_Update_Forward_SP;

Line: 1817


		ROLLBACK TO Mass_Update_Forward_SP;

Line: 1847


ROLLBACK TO PO_Mass_Update_Approver_SP;

Line: 1859


END Update_Approver;

Line: 1890


PROCEDURE Update_Deliver_To(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_supplier_id      IN NUMBER,
                            p_include_close_po IN VARCHAR2,
			    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_po                      g_po;

Line: 1916


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

Line: 1938


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

Line: 1951


SAVEPOINT  PO_Mass_Update_DeliverTo_SP;

Line: 1963


	SAVEPOINT Update_DeliverTo_REC_SP;

Line: 1995


	IF (p_update_person = 'DELIVER TO' ) THEN

		Print_Output(p_update_person,
			     p_old_buyer_name,
		             p_new_buyer_name,
			     p_org_name,
			     p_document_type,
			     p_document_no_from,
		             p_document_no_to,
			     p_date_from,
		             p_date_to,
			     p_supplier_name,
			     p_msg_data,
			     p_msg_count,
		             p_return_status);

Line: 2013


	SELECT  manual_po_num_type
	  INTO  po_num_type
	  FROM  po_system_parameters;

Line: 2032


	ROLLBACK TO Update_DeliverTo_REC_SP;

Line: 2049


stmt_po := 'SELECT pod.ROWID,
       poh.segment1,
       pdt.type_name,
       pod.po_release_id
  FROM po_headers poh,
       po_document_types_vl pdt,
       po_distributions pod
 WHERE pod.deliver_to_person_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
   AND poh.po_header_id = pod.po_header_id
   AND pdt.document_type_code IN (''PO'',''PA'')
   AND pdt.document_subtype = poh.type_lookup_code
   AND Nvl(poh.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
   AND Nvl(poh.cancel_flag,''N'') = ''N''
   AND Nvl(poh.frozen_flag,''N'') = ''N''';

Line: 2066


   stmt_po := stmt_po || 'AND poh.type_lookup_code = PO_MASS_UPDATE_PO_PVT.get_document_type';

Line: 2078


		stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';

Line: 2082


		stmt_po := stmt_po || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to)';

Line: 2088


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

Line: 2100


		stmt_po := stmt_po || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';

Line: 2104


		stmt_po := stmt_po || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 2108


		stmt_po := stmt_po || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 2122


		stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';

Line: 2126


		stmt_po := stmt_po || ' AND POH.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';

Line: 2129


	        stmt_po := stmt_po || ' AND POH.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)
		                        AND POH.creation_date < Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)+1';

Line: 2136


		stmt_po := stmt_po || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';

Line: 2167


   SAVEPOINT Update_DeliverTo_RECPO_SP;

Line: 2181


   UPDATE po_distributions_all
      SET deliver_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_po_rowid
      AND po_release_id IS NULL;

Line: 2232


   ROLLBACK TO Update_DeliverTo_RECPO_SP;

Line: 2252


   stmt_rel := 'SELECT  pod.ROWID,
			poh.segment1,
		        por.release_num,
		        pdt.type_name
		  FROM  po_releases por,
		        po_headers poh,
		        po_document_types_vl pdt,
			po_distributions_all pod
		 WHERE  por.po_header_id = poh.po_header_id
		   AND poh.po_header_id = pod.po_header_id
		   AND pod.po_release_id = por.po_release_id     /* Bug 6868589 */
		   AND pod.deliver_to_person_id = PO_MASS_UPDATE_PO_PVT.get_old_personid
		   AND pdt.document_type_code   =''RELEASE''
		   AND pdt.document_subtype     = por.release_type
		   AND Nvl(por.authorization_status,''INCOMPLETE'') IN (''APPROVED'',''REQUIRES REAPPROVAL'',''INCOMPLETE'',''REJECTED'',''IN PROCESS'',''PRE-APPROVED'')
		   AND Nvl(por.cancel_flag,''N'') = ''N''
		   AND Nvl(por.frozen_flag,''N'') = ''N''';

Line: 2272


	stmt_rel := stmt_rel || ' AND ((PO_MASS_UPDATE_PO_PVT.get_document_type = ''PLANNED'' and por.release_type = ''SCHEDULED'')

				OR (por.release_type = Nvl(PO_MASS_UPDATE_PO_PVT.get_document_type,por.release_type)))';

Line: 2287


		stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) >= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_from)';

Line: 2291


		stmt_rel := stmt_rel || ' AND DECODE ( RTRIM ( POH.SEGMENT1,''0123456789'' ), NULL, To_Number ( POH.SEGMENT1 ) , NULL ) <= to_number(PO_MASS_UPDATE_PO_PVT.get_document_no_to )';

Line: 2297


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

Line: 2309


		stmt_rel := stmt_rel || ' AND POH.SEGMENT1 >= PO_MASS_UPDATE_PO_PVT.get_document_no_from';

Line: 2313


		stmt_rel := stmt_rel || ' AND POH.SEGMENT1 <= PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 2317


		stmt_rel := stmt_rel || ' AND POH.SEGMENT1 BETWEEN PO_MASS_UPDATE_PO_PVT.get_document_no_from AND PO_MASS_UPDATE_PO_PVT.get_document_no_to';

Line: 2331


	stmt_rel := stmt_rel || ' AND POR.creation_date >= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_from)';

Line: 2335


	stmt_rel := stmt_rel || ' AND POR.creation_date <= Trunc(PO_MASS_UPDATE_PO_PVT.get_date_to)';

Line: 2339


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

Line: 2346


	stmt_rel := stmt_rel || ' AND POH.vendor_id = PO_MASS_UPDATE_PO_PVT.get_supplier_id';

Line: 2378


	SAVEPOINT Update_DeliverTo_RECREL_SP;

Line: 2391


        UPDATE po_distributions_all
           SET deliver_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_rel_rowid;

Line: 2437


	ROLLBACK TO Update_DeliverTo_RECREL_SP;

Line: 2467


ROLLBACK TO PO_Mass_Update_DeliverTo_SP;

Line: 2479


END Update_Deliver_To;

Line: 2511


PROCEDURE Print_Output(p_update_person    IN VARCHAR2,
		       p_old_buyer_name   IN VARCHAR2,
                       p_new_buyer_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_supplier_name    IN VARCHAR2,
		       p_msg_data         OUT NOCOPY  VARCHAR2,
                       p_msg_count        OUT NOCOPY  NUMBER,
                       p_return_status    OUT NOCOPY  VARCHAR2) IS

l_msg1             VARCHAR2(240);

Line: 2552


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

Line: 2600


     IF (p_update_person = 'BUYER') THEN

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

Line: 2606


     ELSIF (p_update_person = 'APPROVER') THEN

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

Line: 2612


     ELSIF (p_update_person = 'DELIVER TO') THEN

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

Line: 2618


     ELSIF (p_update_person = 'ALL') THEN

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

Line: 2630


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

Line: 2742


 	     select name,
 	            substrb(display_name,1,360)
 	            p_display_name
 	     from   wf_local_roles
 	     where  orig_system     = 'PER'
 	     and    orig_system_id  = p_old_personid
 	     order by status, start_date;                                   ---Bug 9949640

Line: 2826


SELECT user_id
  INTO p_old_buyer_user_id
  FROM fnd_user
 WHERE employee_id = p_old_personid
   AND user_name = p_old_username;

Line: 2846


	SELECT user_id
	INTO p_new_buyer_user_id
	FROM fnd_user
	WHERE employee_id = p_new_personid
	AND user_name = p_new_username;

Line: 2868


    SELECT vendor_name
      INTO p_supplier_name
      FROM po_vendors
     WHERE vendor_id = p_supplier_id;

Line: 2875


SELECT org_id
  INTO l_org_id
  FROM po_system_parameters;

Line: 2879


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;