APPS.JTF_EC_PUB SQL Statements

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

Line: 92

      p_last_updated_by    => l_user_id,
      p_last_update_date   => SYSDATE,
      p_last_update_login  => l_login_id,
      x_jtf_note_id        => l_note_id
    );

Line: 116

        p_last_updated_by      => l_user_id,
        p_last_update_date     => SYSDATE,
        p_jtf_note_id          => l_note_id,
        p_note_context_type    => l_notes_rec.note_context_type_01,
        p_note_context_type_id => l_notes_rec.note_context_type_id_01,
        p_last_update_login  => l_login_id,
        x_note_context_id      => l_note_context_id
      );

Line: 141

        p_last_updated_by      => l_user_id,
        p_last_update_date     => SYSDATE,
        p_jtf_note_id          => l_note_id,
        p_note_context_type    => l_notes_rec.note_context_type_02,
        p_note_context_type_id => l_notes_rec.note_context_type_id_02,
        p_last_update_login  => l_login_id,
        x_note_context_id      => l_note_context_id
      );

Line: 166

        p_last_updated_by      => l_user_id,
        p_last_update_date     => SYSDATE,
        p_jtf_note_id          => l_note_id,
        p_note_context_type    => l_notes_rec.note_context_type_03,
        p_note_context_type_id => l_notes_rec.note_context_type_id_03,
	p_last_update_login    => l_login_id,
        x_note_context_id      => l_note_context_id
      );

Line: 588

SELECT 'x'
from jtf_rs_emp_dtls_vl
where resource_id = p_resource_id
and (sysdate between nvl(start_date_active, sysdate)
and nvl(end_date_active, sysdate));

Line: 595

SELECT 	task_number
FROM 	jtf_tasks_b
WHERE 	task_id = p_task_id;

Line: 969

	-- insert the phone_id (contact_point) for this contact

		  Jtf_Task_Phones_Pub.create_task_phones (
        		p_api_version   	=> l_api_version,
        		p_init_msg_list         => fnd_api.g_false,
        		p_commit                => fnd_api.g_false,
        		p_task_contact_id       => l_escalation_contact_id,
			p_phone_id              => l_cont_points(j).contact_point_id,
        		x_task_phone_id         => l_task_phone_id,
        		x_return_status         => l_return_status,
        		x_msg_data              => x_msg_data,
        		x_msg_count             => x_msg_count,
        		p_attribute1            => l_cont_points(j).attribute1,
        		p_attribute2            => l_cont_points(j).attribute2,
        		p_attribute3            => l_cont_points(j).attribute3,
        		p_attribute4            => l_cont_points(j).attribute4,
        		p_attribute5            => l_cont_points(j).attribute5,
        		p_attribute6            => l_cont_points(j).attribute6,
       			p_attribute7            => l_cont_points(j).attribute7,
        		p_attribute8            => l_cont_points(j).attribute8,
        		p_attribute9            => l_cont_points(j).attribute9,
        		p_attribute10           => l_cont_points(j).attribute10,
        		p_attribute11           => l_cont_points(j).attribute11,
        		p_attribute12           => l_cont_points(j).attribute12,
        		p_attribute13           => l_cont_points(j).attribute13,
        		p_attribute14           => l_cont_points(j).attribute14,
        		p_attribute15           => l_cont_points(j).attribute15,
        		p_attribute_category    => l_cont_points(j).attribute_category
    			);

Line: 1097

	 update jtf_tasks_b
         set workflow_process_id = l_wf_process_id
         where task_id = l_escalation_id;

Line: 1196

PROCEDURE UPDATE_ESCALATION  (
	p_api_version         	IN	NUMBER,
	p_init_msg_list       	IN	VARCHAR2 DEFAULT fnd_api.g_false,
	p_commit              	IN	VARCHAR2 DEFAULT fnd_api.g_false,
	x_return_status       	OUT NOCOPY     VARCHAR2,
	x_msg_count           	OUT NOCOPY     NUMBER,
	x_msg_data            	OUT NOCOPY     VARCHAR2,
  	p_resp_appl_id		IN      NUMBER	:= NULL,
  	p_resp_id		IN      NUMBER	:= NULL,
  	p_user_id		IN      NUMBER	:= NULL, -- used for last updated by
  	p_login_id		IN      NUMBER	:= NULL,
	p_esc_id		IN	jtf_tasks_b.task_id%TYPE	:=NULL,
	p_esc_number		IN	jtf_tasks_b.task_number%TYPE	:=NULL,
	p_object_version	IN	NUMBER,
	p_esc_record		IN	Esc_Rec_Type,
	p_reference_documents	IN	Esc_Ref_Docs_Tbl_Type,
	p_esc_contacts		IN	Esc_Contacts_Tbl_Type,
	p_cont_points		IN	Esc_Cont_Points_Tbl_Type,
	p_notes			IN	Notes_Tbl_Type,
        x_object_version_number OUT NOCOPY	NUMBER,
	x_workflow_process_id	OUT NOCOPY	VARCHAR2) Is

l_api_version     	CONSTANT NUMBER 	:= 1.0;

Line: 1219

l_api_name		CONSTANT VARCHAR2(30)   := 'UPDATE_ESCALATION';

Line: 1257

select task_phone_id, object_version_number
from jtf_task_phones
where task_contact_id = p_task_contact_id;

Line: 1262

select 	owner_id, task_status_id, escalation_level, planned_end_date
from 	jtf_tasks_b
where 	task_id = p_escalation_id;

Line: 1267

Select  object_type_code,
	object_id,
       	object_name
from	jtf_task_references_b
where 	task_reference_id = p_task_ref_id;

Line: 1275

SAVEPOINT	Update_Escalation;

Line: 1299

	jtf_ec_cuhk.Update_Escalation_Pre(
		p_esc_id		=> l_escalation_id,
		p_esc_number		=> l_escalation_number,
		p_object_version	=> l_object_version_number,
		p_esc_record		=> l_esc_record,
		p_reference_documents	=> l_reference_documents,
		p_esc_contacts		=> l_esc_contacts,
		p_cont_points		=> l_cont_points,
		p_notes			=> l_notes,
		x_return_status       	=> l_return_status,
		x_msg_count           	=> x_msg_count,
		x_msg_data            	=> x_msg_data);

Line: 1324

	jtf_ec_vuhk.Update_Escalation_Pre(
		p_esc_id		=> l_escalation_id,
		p_esc_number		=> l_escalation_number,
		p_object_version	=> l_object_version_number,
		p_esc_record		=> l_esc_record,
		p_reference_documents	=> l_reference_documents,
		p_esc_contacts		=> l_esc_contacts,
		p_cont_points		=> l_cont_points,
		p_notes			=> l_notes,
		x_return_status       	=> l_return_status,
		x_msg_count           	=> x_msg_count,
		x_msg_data            	=> x_msg_data);

Line: 1530

   JTF_EC_PVT.UPDATE_ESCALATION (
     P_API_VERSION     		=> l_api_version,
     P_INIT_MSG_LIST   		=> fnd_api.g_false,
     P_COMMIT          		=> fnd_api.g_false,
     P_OBJECT_VERSION_NUMBER 	=> l_object_version_number,
     P_ESCALATION_ID   		=> l_escalation_id,
     P_ESCALATION_NUMBER 	=> l_escalation_number,
     P_ESCALATION_NAME 		=> l_esc_record.esc_name,
     P_DESCRIPTION     		=> l_esc_record.esc_description,
     P_ESCALATION_STATUS_NAME 	=> l_esc_record.status_name,
     P_ESCALATION_STATUS_ID     => l_esc_status_id,
 --    P_OPEN_DATE      		=> l_esc_record.esc_open_date, -- cannot be updated
     P_CLOSE_DATE		=> l_esc_close_date,
     P_ESCALATION_PRIORITY_NAME	=> NULL,
     P_ESCALATION_PRIORITY_ID  	=> NULL,
     P_OWNER_ID     		=> l_esc_record.esc_owner_id,
     P_ESCALATION_OWNER_TYPE_CODE => l_esc_record.esc_owner_type_code,   --bug 2723761
--     P_OWNER_TERRITORY_ID       => l_esc_record.esc_territory_id, -- not updatable in the form
     P_ASSIGNED_BY_NAME         => NULL,
     P_ASSIGNED_BY_ID          	=> NULL,
--     P_CUSTOMER_NUMBER         	=> l_esc_record.customer_number, -- not updatable in the form
--     P_CUSTOMER_ID             	=> l_esc_record.customer_id,
     P_CUST_ACCOUNT_NUMBER      => l_esc_record.cust_account_number,
     P_CUST_ACCOUNT_ID         	=> l_esc_record.cust_account_id,
     P_ADDRESS_ID              	=> l_esc_record.cust_address_id,
     P_ADDRESS_NUMBER          	=> l_esc_record.cust_address_number,
     P_TARGET_DATE             	=> l_esc_record.esc_target_date,
     P_REASON_CODE             	=> l_esc_record.reason_code,
     P_PRIVATE_FLAG            	=> NULL,
     P_PUBLISH_FLAG            	=> NULL,
     P_WORKFLOW_PROCESS_ID     	=> NULL,
     P_ESCALATION_LEVEL        	=> l_esc_record.escalation_level,
     X_RETURN_STATUS           	=> l_return_status,
     X_MSG_COUNT               	=> x_msg_count,
     X_MSG_DATA                	=> x_msg_data,
     p_attribute1            	=> l_esc_record.attribute1,
     p_attribute2               => l_esc_record.attribute2,
     p_attribute3            	=> l_esc_record.attribute3,
     p_attribute4            	=> l_esc_record.attribute4,
     p_attribute5            	=> l_esc_record.attribute5,
     p_attribute6            	=> l_esc_record.attribute6,
     p_attribute7            	=> l_esc_record.attribute7,
     p_attribute8            	=> l_esc_record.attribute8,
     p_attribute9            	=> l_esc_record.attribute9,
     p_attribute10           	=> l_esc_record.attribute10,
     p_attribute11           	=> l_esc_record.attribute11,
     p_attribute12           	=> l_esc_record.attribute12,
     p_attribute13           	=> l_esc_record.attribute13,
     p_attribute14           	=> l_esc_record.attribute14,
     p_attribute15           	=> l_esc_record.attribute15,
     p_attribute_category    	=> l_esc_record.attribute_category
     );

Line: 1615

		JTF_EC_REFERENCES_PVT.DELETE_REFERENCES(
     		    	P_API_VERSION      => l_api_version,
      			P_INIT_MSG_LIST    => fnd_api.g_false,
      			P_COMMIT           => fnd_api.g_false,
      			P_OBJECT_VERSION_NUMBER => l_reference_documents(j).object_version_number,
      			P_ESCALATION_REFERENCE_ID => l_reference_documents(j).reference_id,
      			X_RETURN_STATUS     =>l_return_status,
      			X_MSG_COUNT         =>x_msg_count,
      			X_MSG_DATA          =>x_msg_data);

Line: 1688

		    JTF_EC_REFERENCES_PVT.UPDATE_REFERENCES (
     			P_API_VERSION     => l_api_version,
     			P_INIT_MSG_LIST   => fnd_api.g_false,
     			P_COMMIT          => fnd_api.g_false,
     			P_OBJECT_VERSION_NUMBER	  => l_reference_documents(j).object_version_number,
     			P_ESCALATION_REFERENCE_ID => l_reference_documents(j).reference_id,
     			P_OBJECT_TYPE_CODE        => l_reference_documents(j).object_type_code,
     			P_OBJECT_NAME             => l_reference_documents(j).object_name,
     			P_OBJECT_ID               => l_reference_documents(j).object_id,
     			P_OBJECT_DETAILS          => NULL,
     			P_REFERENCE_CODE          => l_reference_documents(j).reference_code,
     			-- P_USAGE                => NULL, -- l_reference_documents(j).usage,
     			X_RETURN_STATUS     	  => l_return_status,
     			X_MSG_DATA          	  => x_msg_data,
     			X_MSG_COUNT         	  => x_msg_count,
        		p_attribute1            => l_reference_documents(j).attribute1,
        		p_attribute2            => l_reference_documents(j).attribute2,
        		p_attribute3            => l_reference_documents(j).attribute3,
        		p_attribute4            => l_reference_documents(j).attribute4,
        		p_attribute5            => l_reference_documents(j).attribute5,
        		p_attribute6            => l_reference_documents(j).attribute6,
       			p_attribute7            => l_reference_documents(j).attribute7,
        		p_attribute8            => l_reference_documents(j).attribute8,
        		p_attribute9            => l_reference_documents(j).attribute9,
        		p_attribute10           => l_reference_documents(j).attribute10,
        		p_attribute11           => l_reference_documents(j).attribute11,
        		p_attribute12           => l_reference_documents(j).attribute12,
        		p_attribute13           => l_reference_documents(j).attribute13,
        		p_attribute14           => l_reference_documents(j).attribute14,
        		p_attribute15           => l_reference_documents(j).attribute15,
        		p_attribute_category    => l_reference_documents(j).attribute_category);

Line: 1787

    		    Jtf_Task_Phones_Pub.delete_task_phones (
        		p_api_version   	=> l_api_version,
        		p_init_msg_list         => fnd_api.g_false,
        		p_commit                => fnd_api.g_false,
			p_object_version_number => l_cont_points(j).object_version_number,
        		p_task_phone_id         => l_cont_points(j).task_phone_id,
        		x_return_status         => l_return_status,
        		x_msg_data              => x_msg_data,
        		x_msg_count             => x_msg_count
			);

Line: 1822

		  Jtf_Task_Phones_Pub.update_task_phones(
        		p_api_version   	=> l_api_version,
        		p_init_msg_list         => fnd_api.g_false,
        		p_commit                => fnd_api.g_false,
			p_object_version_number => l_cont_points(j).object_version_number,
        		p_task_phone_id         => l_cont_points(j).task_phone_id,
			p_phone_id              => l_cont_points(j).contact_point_id,
        		x_return_status         => l_return_status,
        		x_msg_data              => x_msg_data,
        		x_msg_count             => x_msg_count,
        		p_attribute1            => l_cont_points(j).attribute1,
        		p_attribute2            => l_cont_points(j).attribute1,
        		p_attribute3            => l_cont_points(j).attribute1,
        		p_attribute4            => l_cont_points(j).attribute1,
        		p_attribute5            => l_cont_points(j).attribute1,
        		p_attribute6            => l_cont_points(j).attribute1,
        		p_attribute7            => l_cont_points(j).attribute1,
        		p_attribute8            => l_cont_points(j).attribute1,
        		p_attribute9            => l_cont_points(j).attribute1,
        		p_attribute10           => l_cont_points(j).attribute1,
        		p_attribute11           => l_cont_points(j).attribute1,
        		p_attribute12           => l_cont_points(j).attribute1,
        		p_attribute13           => l_cont_points(j).attribute1,
        		p_attribute14           => l_cont_points(j).attribute1,
       			p_attribute15           => l_cont_points(j).attribute1,
        		p_attribute_category    => l_cont_points(j).attribute_category
    			);

Line: 1867

  	-- deletes an existing contact. Removes the corresponding contact points from jtf_task_phones.

	if l_esc_contacts(j).action_code = 'D' then

		l_return_status := 'x';

Line: 1885

   		JTF_EC_CONTACTS_PVT.DELETE_ESCALATION_CONTACTS(
      			P_API_VERSION      => l_api_version,
      			P_INIT_MSG_LIST    => fnd_api.g_false,
      			P_COMMIT           => fnd_api.g_false,
      			P_OBJECT_VERSION_NUMBER => l_esc_contacts(j).object_version_number,
      			P_ESCALATION_CONTACT_ID => l_esc_contacts(j).task_contact_id,
      			X_RETURN_STATUS     => l_return_status,
      			X_MSG_COUNT         => x_msg_count,
      			X_MSG_DATA          => x_msg_data);

Line: 1901

			-- delete the contact points

		for c in c_cont_phone_id(l_esc_contacts(j).task_contact_id) loop

    		      Jtf_Task_Phones_Pub.delete_task_phones (
        			p_api_version   	=> l_api_version,
        			p_init_msg_list         => fnd_api.g_false,
        			p_commit                => fnd_api.g_false,
				p_object_version_number => c.object_version_number,
        			p_task_phone_id         => c.task_phone_id,
        			x_return_status         => l_return_status,
        			x_msg_data              => x_msg_data,
        			x_msg_count             => x_msg_count
				);

Line: 1929

	-- can be updated. Once the contact is created the contact_type_code and
	-- the contact_id cannot be changed.

		l_esc_contacts(j).contact_id := FND_API.G_MISS_NUM;

Line: 1955

   		JTF_EC_CONTACTS_PVT.UPDATE_ESCALATION_CONTACTS (
     			P_API_VERSION     =>  l_api_version,
     			P_INIT_MSG_LIST   =>  fnd_api.g_false,
    			P_COMMIT          =>  fnd_api.g_false,
     			P_OBJECT_VERSION_NUMBER => l_esc_contacts(j).object_version_number,
     			P_ESCALATION_CONTACT_ID => l_esc_contacts(j).task_contact_id,
     			P_CONTACT_ID        	=> l_esc_contacts(j).contact_id,
     			P_CONTACT_TYPE_CODE 	=> l_esc_contacts(j).contact_type_code,
     			P_ESCALATION_NOTIFY_FLAG => l_esc_contacts(j).escalation_notify_flag,
     			P_ESCALATION_REQUESTER_FLAG => l_esc_contacts(j).escalation_requester_flag,
     			X_RETURN_STATUS     	=> l_return_status,
     			X_MSG_DATA          	=> x_msg_data,
     			X_MSG_COUNT         	=> x_msg_count,
     			p_attribute1        	=> l_esc_contacts(j).attribute1,
     			p_attribute2        	=> l_esc_contacts(j).attribute2,
     			p_attribute3        	=> l_esc_contacts(j).attribute3,
     			p_attribute4        	=> l_esc_contacts(j).attribute4,
     			p_attribute5        	=> l_esc_contacts(j).attribute5,
     			p_attribute6        	=> l_esc_contacts(j).attribute6,
     			p_attribute7        	=> l_esc_contacts(j).attribute7,
     			p_attribute8        	=> l_esc_contacts(j).attribute8,
     			p_attribute9        	=> l_esc_contacts(j).attribute9,
     			p_attribute10        	=> l_esc_contacts(j).attribute10,
     			p_attribute11        	=> l_esc_contacts(j).attribute11,
     			p_attribute12        	=> l_esc_contacts(j).attribute12,
     			p_attribute13        	=> l_esc_contacts(j).attribute13,
     			p_attribute14        	=> l_esc_contacts(j).attribute14,
     			p_attribute15        	=> l_esc_contacts(j).attribute15,
     			p_attribute_category 	=> l_esc_contacts(j).attribute_category
     			);

Line: 2152

	jtf_notes_pub.Update_note(
		      	p_api_version    => l_api_version,
      			p_init_msg_list  => fnd_api.g_false,
      			p_commit         => fnd_api.g_false,
      			x_return_status  => l_return_status,
      			x_msg_count      => x_msg_count,
      			x_msg_data       => x_msg_data,
                        p_jtf_note_id	 => l_notes(j).note_id,
      			p_notes              => l_notes(j).note,
      			p_notes_detail       => l_notes(j).note_detail,
      			p_note_type          => l_notes(j).note_type,
      			p_note_status        => l_notes(j).note_status,
      			p_entered_by         => l_user_id,
      			p_last_updated_by    => l_user_id,
      			p_last_update_date   => SYSDATE
			);

Line: 2244

	 update jtf_tasks_b
         set workflow_process_id = l_wf_process_id
         where task_id = l_escalation_id;

Line: 2253

	jtf_ec_vuhk.Update_Escalation_Post(
		p_esc_id		=> l_escalation_id,
		p_esc_number		=> l_escalation_number,
		p_object_version	=> l_object_version_number,
		p_esc_record		=> l_esc_record,
		p_reference_documents	=> l_reference_documents,
		p_esc_contacts		=> l_esc_contacts,
		p_cont_points		=> l_cont_points,
		p_notes			=> l_notes,
		x_return_status       	=> l_return_status,
		x_msg_count           	=> x_msg_count,
		x_msg_data            	=> x_msg_data);

Line: 2278

	jtf_ec_cuhk.Update_Escalation_Post(
		p_esc_id		=> l_escalation_id,
		p_esc_number		=> l_escalation_number,
		p_object_version	=> l_object_version_number,
		p_esc_record		=> l_esc_record,
		p_reference_documents	=> l_reference_documents,
		p_esc_contacts		=> l_esc_contacts,
		p_cont_points		=> l_cont_points,
		p_notes			=> l_notes,
		x_return_status       	=> l_return_status,
		x_msg_count           	=> x_msg_count,
		x_msg_data            	=> x_msg_data);

Line: 2317

	ROLLBACK TO Update_Escalation;

Line: 2327

        ROLLBACK TO Update_Escalation;

Line: 2336

        ROLLBACK TO Update_Escalation;

Line: 2348

END UPDATE_ESCALATION;

Line: 2350

PROCEDURE DELETE_ESCALATION(
	p_api_version         	IN	NUMBER,
	p_init_msg_list       	IN	VARCHAR2 DEFAULT fnd_api.g_false,
	p_commit              	IN	VARCHAR2 DEFAULT fnd_api.g_false,
	x_return_status       	OUT NOCOPY     VARCHAR2,
	x_msg_count           	OUT NOCOPY     NUMBER,
	x_msg_data            	OUT NOCOPY     VARCHAR2,
  	p_user_id		IN      NUMBER,
  	p_login_id		IN      NUMBER,
	p_esc_id		IN	jtf_tasks_b.task_id%TYPE,
	p_esc_number		IN	jtf_tasks_b.task_number%TYPE,
	p_object_version	IN	NUMBER) Is

l_api_version     	CONSTANT NUMBER 	:= 1.0;

Line: 2364

l_api_name		CONSTANT VARCHAR2(30)   := 'DELETE_ESCALATION';

Line: 2375

SAVEPOINT	Delete_Escalation;

Line: 2403

	jtf_ec_cuhk.Delete_Escalation_Pre(
		p_esc_id		=> l_escalation_id,
		p_esc_number		=> l_escalation_number,
		p_object_version	=> l_object_version_number,
		x_return_status       	=> l_return_status,
		x_msg_count           	=> x_msg_count,
		x_msg_data            	=> x_msg_data);

Line: 2423

	jtf_ec_vuhk.Delete_Escalation_Pre(
		p_esc_id		=> l_escalation_id,
		p_esc_number		=> l_escalation_number,
		p_object_version	=> l_object_version_number,
		x_return_status       	=> l_return_status,
		x_msg_count           	=> x_msg_count,
		x_msg_data            	=> x_msg_data);

Line: 2491

   JTF_EC_PVT.DELETE_ESCALATION(
      		P_API_VERSION		=> l_api_version,
      		P_INIT_MSG_LIST    	=> fnd_api.g_false,
      		P_COMMIT           	=> fnd_api.g_false,
      		P_OBJECT_VERSION_NUMBER => l_object_version_number,
      		P_ESCALATION_ID    	=> l_escalation_id,
      		P_ESCALATION_NUMBER 	=> l_escalation_number,
      		X_RETURN_STATUS     	=> l_return_status,
     		X_MSG_COUNT         	=> x_msg_count,
      		X_MSG_DATA          	=> x_msg_data);

Line: 2513

	jtf_ec_vuhk.Delete_Escalation_Post(
		p_esc_id		=> l_escalation_id,
		p_esc_number		=> l_escalation_number,
		p_object_version	=> l_object_version_number,
		x_return_status       	=> l_return_status,
		x_msg_count           	=> x_msg_count,
		x_msg_data            	=> x_msg_data);

Line: 2535

	jtf_ec_cuhk.Delete_Escalation_Post(
		p_esc_id		=> l_escalation_id,
		p_esc_number		=> l_escalation_number,
		p_object_version	=> l_object_version_number,
		x_return_status       	=> l_return_status,
		x_msg_count           	=> x_msg_count,
		x_msg_data            	=> x_msg_data);

Line: 2566

	ROLLBACK TO Delete_Escalation;

Line: 2577

        ROLLBACK TO Delete_Escalation;

Line: 2583

        ROLLBACK TO Delete_Escalation;

Line: 2594

END 	DELETE_ESCALATION;