APPS.AMV_MYCHANNEL_PVT SQL Statements

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

Line: 75

select 	my_channel_id
from		amv_u_my_channels
where	user_or_group_id = p_user_or_group_id
and		user_or_group_type = p_user_or_group_type
and		subscribing_to_id = p_subscribing_to_id
and		subscribing_to_type = p_subscribing_to_type;

Line: 200

  select u.user_name, r.resource_id
  From   amv_rs_all_res_extns_vl r, fnd_user u
  where  r.resource_id = res_id
  and    u.user_id = r.user_id;

Line: 400

select amv_u_my_channels_s.nextval
from dual;

Line: 404

SELECT NVL(MAX(order_number) + 1, 1)
FROM   amv_u_my_channels
WHERE  user_or_group_id = p_mychannel_obj.user_or_group_id
and    user_or_group_type = p_mychannel_obj.user_or_group_type;

Line: 495

    	-- Select the channel sequence
    	OPEN MyChannelId_Seq;

Line: 501

    	INSERT INTO amv_u_my_channels (
    		my_channel_id,
		object_version_number,
		last_update_date,
		last_updated_by,
		creation_date,
		created_by,
		last_update_login,
		user_or_group_id,
		user_or_group_type,
		subscribing_to_id,
		subscribing_to_type,
		subscription_reason_type,
		order_number,
		status,
		notify_flag,
		notification_interval_type
    	)
    	VALUES (
		l_mychannel_id,
		l_object_version_number,
        	sysdate,
        	l_user_id,
        	sysdate,
        	l_user_id,
        	l_login_user_id,
		p_mychannel_obj.user_or_group_id,
		p_mychannel_obj.user_or_group_type,
		p_mychannel_obj.subscribing_to_id,
		p_mychannel_obj.subscribing_to_type,
		p_mychannel_obj.subscription_reason_type,
		l_order,
		p_mychannel_obj.status,
		p_mychannel_obj.notify_flag,
		p_mychannel_obj.notification_interval_type
   	 );

Line: 681

select user_or_group_id
,      user_or_group_type
,      subscribing_to_id
,      subscribing_to_type
from   amv_u_my_channels
where  my_channel_id = p_mychannel_id;

Line: 779

    DELETE FROM amv_u_my_channels
    WHERE  my_channel_id = l_mychannel_id;

Line: 867

PROCEDURE Update_MyChannel
(    p_api_version      IN  NUMBER,
     p_init_msg_list    IN  VARCHAR2 := FND_API.G_FALSE,
     p_commit           IN  VARCHAR2 := FND_API.G_FALSE,
     p_validation_level IN  NUMBER := FND_API.G_VALID_LEVEL_FULL,
     x_return_status    OUT NOCOPY  VARCHAR2,
     x_msg_count        OUT NOCOPY  NUMBER,
     x_msg_data         OUT NOCOPY  VARCHAR2,
     p_check_login_user IN  VARCHAR2 := FND_API.G_TRUE,
     p_mychannel_obj  	IN  AMV_MY_CHANNEL_OBJ_TYPE
)
IS
l_api_name              CONSTANT VARCHAR2(30) := 'Update_MyChannel';

Line: 898

select	order_number
,		status
,		notify_flag
,		notification_interval_type
from		amv_u_my_channels
where	my_channel_id = l_mychannel_id;

Line: 907

    SAVEPOINT  Update_MyChannel_PVT;

Line: 1027

    UPDATE amv_u_my_channels
    SET	order_number 	= l_order_number
    ,	status 		= l_status
    ,	notify_flag 	= l_notify_flag
    ,	notification_interval_type = l_notif_interval_type
    WHERE my_channel_id = l_mychannel_id;

Line: 1060

       ROLLBACK TO  Update_MyChannel_PVT;

Line: 1069

       ROLLBACK TO  Update_MyChannel_PVT;

Line: 1078

       ROLLBACK TO  Update_MyChannel_PVT;

Line: 1091

END Update_MyChannel;

Line: 1160

select 	my_channel_id
,		user_or_group_id
,		user_or_group_type
,		subscribing_to_id
,		subscribing_to_type
,		subscription_reason_type
,		order_number
,		status
,		notify_flag
,		notification_interval_type
from   	amv_u_my_channels
where  	user_or_group_id = p_user_id
and    	user_or_group_type = AMV_UTILITY_PVT.G_USER
union
select 	my_channel_id
,		user_or_group_id
,		user_or_group_type
,		subscribing_to_id
,		subscribing_to_type
,		subscription_reason_type
,		order_number
,		status
,		notify_flag
,		notification_interval_type
from   	amv_u_my_channels
where  	user_or_group_id in (select 	group_id
						from   	jtf_rs_group_members
						where  	resource_id = p_user_id and
						delete_flag = 'N')
and    	user_or_group_type = AMV_UTILITY_PVT.G_GROUP;

Line: 1192

select  channel_type
,	   access_level_type
,	   effective_start_date
,	   nvl(expiration_date,sysdate)
from	   amv_c_channels_b
where   channel_id = l_channel_id;

Line: 1437

select channel_id
from   amv_c_channels_b
where  channel_category_id in (
                select b1.channel_category_id
                from   amv_c_categories_b b1
                where  b1.channel_category_id = p_category_id
                or     b1.channel_category_id in (
                        select b2.channel_category_id
                        from   amv_c_categories_b b2
                        where  b2.parent_channel_category_id = p_category_id))
and	channel_type = AMV_UTILITY_PVT.G_CONTENT
and	access_level_type = AMV_UTILITY_PVT.G_PUBLIC;

Line: 1637

 SELECT N.NOTIFICATION_ID
 FROM WF_NOTIFICATIONS N
 WHERE N.RECIPIENT_ROLE = l_recipient_role
 AND N.MESSAGE_TYPE = 'AMV_APPR'
 AND N.STATUS = l_status
 AND EXISTS
 (SELECT NULL
        FROM WF_MESSAGE_ATTRIBUTES MA
        WHERE N.MESSAGE_TYPE = MA.MESSAGE_TYPE
        AND N.MESSAGE_NAME = MA.MESSAGE_NAME
        AND MA.SUBTYPE = 'RESPOND');

Line: 1650

select DISTINCT N.NOTIFICATION_ID
FROM   WF_NOTIFICATIONS_VIEW N
,      WF_MESSAGE_ATTRIBUTES_VL MA
WHERE  N.RECIPIENT_ROLE = l_recipient_role
AND    N.MESSAGE_TYPE = MA.MESSAGE_TYPE
AND    N.MESSAGE_NAME = MA.MESSAGE_NAME
AND	  MA.MESSAGE_NAME IN (
				select x.message_name
				from wf_message_attributes_vl x
				where x.subtype = 'SEND'
				and NOT EXISTS(
					select '1'
					from wf_message_attributes_vl b
					where b.subtype = 'RESPOND'
					and b.message_name = MA.MESSAGE_NAME)
				)
AND    N.MESSAGE_TYPE = 'AMV_APPR'
AND    N.STATUS = l_status;

Line: 1670

select SUBJECT
,	  BEGIN_DATE
,	  END_DATE
,	  DUE_DATE
,	  STATUS
,	  PRIORITY
FROM   WF_NOTIFICATIONS_VIEW
WHERE  NOTIFICATION_ID = l_notification_id
AND    RECIPIENT_ROLE = l_recipient_role
AND    STATUS = l_status
ORDER BY BEGIN_DATE;

Line: 1683

select FND.USER_NAME
from	 JTF_RS_RESOURCE_EXTNS RD
,	 FND_USER FND
where RD.USER_ID = FND.USER_ID
and	 RD.RESOURCE_ID = p_resource_id;

Line: 1690

select USER_NAME
from	  FND_USER
where  USER_ID = p_user_id;

Line: 1696

select text from wf_resources
where name = 'WF_WEB_AGENT'
and language = 'US';

Line: 1998

select 	subscribing_to_id
from   	amv_u_my_channels
where  	user_or_group_id in (select 	group_id
						from   	jtf_rs_group_members
						where  	resource_id = p_user_id)
and    	user_or_group_type = AMV_UTILITY_PVT.G_GROUP
and		subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
union
select 	subscribing_to_id
from		amv_u_my_channels
where	user_or_group_id = p_user_id
and		user_or_group_type = AMV_UTILITY_PVT.G_USER
and		subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL;

Line: 2014

select 	subscribing_to_id
from   	amv_u_my_channels
where  	user_or_group_id in (select 	group_id
						from   	jtf_rs_group_members
						where  	resource_id = p_user_id
						and  	delete_flag <> 'Y')
and    	user_or_group_type = AMV_UTILITY_PVT.G_GROUP
and		subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY
union
select 	subscribing_to_id
from		amv_u_my_channels
where	user_or_group_id = p_user_id
and		user_or_group_type = AMV_UTILITY_PVT.G_USER
and		subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY;

Line: 2030

select ib.item_id
,      ib.item_name
,			 cim.channel_id
from   amv_c_chl_item_match cim
,      jtf_amv_items_vl ib
where  cim.channel_id in (select 	subscribing_to_id
from   	amv_u_my_channels
where  	user_or_group_id in (select 	group_id
						from   	jtf_rs_group_members
						where  	resource_id = p_user_id)
and    	user_or_group_type = AMV_UTILITY_PVT.G_GROUP
and		subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
union
select 	subscribing_to_id
from		amv_u_my_channels
where	user_or_group_id = p_user_id
and		user_or_group_type = AMV_UTILITY_PVT.G_USER
and		subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
and    cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
and    cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
and    cim.available_for_channel_date <= sysdate
and    cim.item_id = ib.item_id
and    nvl(ib.effective_start_date, sysdate) <= sysdate + 1
and    nvl(ib.expiration_date, sysdate) >= sysdate
order by ib.effective_start_date desc;

Line: 2057

select count(cim.item_id)
from   amv_c_chl_item_match cim
,      jtf_amv_items_vl ib
where  cim.channel_id in (select 	subscribing_to_id
from   	amv_u_my_channels
where  	user_or_group_id in (select 	group_id
						from   	jtf_rs_group_members
						where  	resource_id = p_user_id)
and    	user_or_group_type = AMV_UTILITY_PVT.G_GROUP
and		subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL
union
select 	subscribing_to_id
from		amv_u_my_channels
where	user_or_group_id = p_user_id
and		user_or_group_type = AMV_UTILITY_PVT.G_USER
and		subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL )
and    cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
and    cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
and    cim.available_for_channel_date <= sysdate
and    cim.item_id = ib.item_id
and    nvl(ib.effective_start_date, sysdate) <= sysdate + 1
and    nvl(ib.expiration_date, sysdate) >= sysdate;

Line: 2292

select item_id
,      item_name
,	  description
,	  item_type
from   jtf_amv_items_vl
where  owner_id = p_user_id
and	  application_id = p_application_id
and  nvl( expiration_date, sysdate ) >= sysdate
and   nvl(effective_start_date, sysdate) <= sysdate
order by item_name;

Line: 2304

select count(item_id)
from   jtf_amv_items_vl
where  owner_id = p_user_id
and  nvl( expiration_date, sysdate ) >= sysdate
and   nvl(effective_start_date, sysdate) <= sysdate
and	  application_id = p_application_id;

Line: 2502

select owner_user_id
,      default_approver_user_id
from   amv_c_channels_vl
where  channel_id = p_channel_id;

Line: 2508

select chl_approver_flag
from   amv_u_access
where  access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
and    access_to_table_record_id = p_channel_id
and    user_or_group_type = AMV_UTILITY_PVT.G_USER
and    user_or_group_id = p_user_id
and	  effective_start_date <= sysdate
and	  nvl(expiration_date, sysdate) >= sysdate;