[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select USERTYPE_SHORTNAME
from jtf_um_usertypes_vl
where usertype_id = x_usertype_id;
select SUBSCRIPTION_NAME
from jtf_um_subscriptions_vl
where subscription_id = x_subscription_id;
select application_id
from jtf_um_usertypes_b
where usertype_id = x_usertype_id;
select application_id
from jtf_um_subscriptions_vl
where subscription_id = x_subscription_id;
select USER_NAME
from FND_USER
where USER_ID = userID
and (nvl (END_DATE, sysdate + 1) > sysdate
or to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
select user_id
from FND_USER
where USER_NAME = username
and (nvl (END_DATE, sysdate + 1) > sysdate OR
to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
select hz.party_name, hz.party_number
from fnd_user fnd, hz_parties hz, hz_relationships hzr
where fnd.user_id = p_user_id
and fnd.customer_id = hzr.party_id
and hzr.start_date <= sysdate
and nvl (hzr.end_date, sysdate + 1) > sysdate
and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_id = hz.party_id;
select APPROVAL_ID
from JTF_UM_USERTYPES_B
where USERTYPE_ID = requestID;
select APPROVAL_ID
from JTF_UM_SUBSCRIPTIONS_B
where SUBSCRIPTION_ID = requestID;
select USERTYPE_ID
from JTF_UM_USERTYPE_REG
where USER_ID = requesterUserID
and STATUS_CODE <>'REJECTED'
and EFFECTIVE_START_DATE <= sysdate
and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate;
select USE_PENDING_REQ_FLAG
from JTF_UM_APPROVALS_B
where APPROVAL_ID = approvalID
and EFFECTIVE_START_DATE <= sysdate
and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate;
select WF_ITEM_TYPE
from JTF_UM_APPROVALS_B
where APPROVAL_ID = approvalID;
select WF_ITEM_TYPE
from JTF_UM_USERTYPE_REG
where USERTYPE_REG_ID = requestRegID;
select WF_ITEM_TYPE
from JTF_UM_SUBSCRIPTION_REG
where SUBSCRIPTION_REG_ID = requestRegID;
procedure Selector (item_type in varchar2,
item_key in varchar2,
activity_id in number,
command in varchar2,
resultout out NOCOPY varchar2) is
--
begin
JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering Selector (' ||
item_type || ',' || item_key || ',' || activity_id || ',' ||
command || ') API');
JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting Selector API');
wf_core.context ('JTF_UM_WF_APPROVAL', 'Selector', item_type, item_key, to_char (activity_id), command);
end selector;
select hz.party_number
from hz_parties hz, hz_relationships hzr, fnd_user fnd
where fnd.user_id = requesterUserID
and fnd.customer_id = hzr.party_id
and hzr.start_date <= sysdate
and nvl (hzr.end_date, sysdate + 1) > sysdate
and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_id = hz.party_id;
select USERTYPE_KEY
from JTF_UM_USERTYPES_B
where USERTYPE_ID = usertypeId
and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate;
select rvl.responsibility_id, rvl.application_id
from fnd_responsibility_vl rvl, jtf_um_usertype_resp utr
where utr.usertype_id = requestId
and utr.responsibility_key = rvl.responsibility_key
and nvl (rvl.end_date, sysdate + 1) > sysdate
and nvl (utr.effective_end_date, sysdate + 1) > sysdate
and rvl.version in ('W','4');
select rvl.responsibility_id, rvl.application_id
from fnd_responsibility_vl rvl, jtf_um_subscription_resp utr
where utr.subscription_id = requestId
and utr.responsibility_key = rvl.responsibility_key
and nvl (rvl.end_date, sysdate + 1) > sysdate
and nvl (utr.effective_end_date, sysdate + 1) > sysdate
and rvl.version in ('W','4');
select email_address from wf_local_roles where name=upper(requesterUserName);
select name, display_name
from WF_LOCAL_ROLES
where name = x_role_name;
* 1) create/update an ad hoc role with named "JTAUM###".
* 2) find all approvers from the same organization and with
* "JTF_PRIMARY_USER_SUMMARY" permission.
* 3) associate the ad hoc role with approvers
* Parameters
* input parameters: p_itemtype - itemtype of the workflow
* p_itemkey - itemkey of the workflow
* output parameters: x_role_name - The name of the ad hoc role, null if
* role didn't get created.
* x_role_name_display - The display name of the ad hoc role.
* Errors:
* Other Comments:
*/
procedure get_org_ad_hoc_role (p_itemtype in varchar2,
p_itemkey in varchar2,
x_role_name out NOCOPY varchar2,
x_role_name_display out NOCOPY varchar2) is
l_method_name varchar2 (20) := 'GET_ORG_AD_HOC_ROLE';
select name, display_name
from WF_LOCAL_ROLES
where name = x_role_name;
select fnd.user_name
from hz_parties hz_org, hz_relationships hzr, fnd_user fnd
where hz_org.party_number = l_org_number
and hz_org.party_type = 'ORGANIZATION'
and hz_org.party_id = hzr.object_id
and hzr.start_date <= sysdate
and nvl (hzr.end_date, sysdate + 1) > sysdate
and hzr.relationship_code = 'EMPLOYEE_OF'
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.subject_table_name = 'HZ_PARTIES'
and fnd.customer_id = hzr.party_id
and fnd.start_date <= sysdate
and nvl (fnd.end_date, sysdate + 1) > sysdate
and exists (
select prin_b.principal_name
from jtf_auth_domains_b domains_b, jtf_auth_permissions_b perm,
jtf_auth_principal_maps prin_maps, jtf_auth_role_perms role_perms,
jtf_auth_principals_b prin_b, jtf_auth_principals_b prin_b2
where prin_b.jtf_auth_principal_id = prin_maps.jtf_auth_principal_id
and prin_maps.jtf_auth_parent_principal_id = prin_b2.jtf_auth_principal_id
and prin_b2.jtf_auth_principal_id = role_perms.jtf_auth_principal_id
and role_perms.jtf_auth_permission_id = perm.jtf_auth_permission_id
and prin_maps.jtf_auth_domain_id = domains_b.jtf_auth_domain_id
and domains_b.domain_name = 'CRM_DOMAIN'
and perm.permission_name = 'JTF_PRIMARY_USER_SUMMARY'
and prin_b.principal_name = fnd.user_name
);
procedure SelectApprover (itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
--
applID number;
JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Entering SelectApprover (' ||
itemtype || ',' || itemkey || ',' || actid || ',' ||
funcmode || ') API');
update JTF_UM_USERTYPE_REG
set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
APPROVER_USER_ID = approverUserID
where USERTYPE_REG_ID = itemkey;
update JTF_UM_SUBSCRIPTION_REG
set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
APPROVER_USER_ID = approverUserID
where SUBSCRIPTION_REG_ID = itemkey;
wf_core.context ('JTF_UM_WF_APPROVAL', 'SelectorApprover', itemtype,
itemkey, to_char (actid), funcmode);
end SelectApprover;
select APPROVER_SEQ
from JTF_UM_APPROVERS
where APPROVER_ID = x_approverID
and APPROVAL_ID = l_approvalID;
select a.APPROVER_ID, a.USER_ID, f.USER_NAME
from JTF_UM_APPROVERS a, FND_USER f
where a.APPROVER_SEQ > l_approverSeq
and a.APPROVAL_ID = l_approvalID
and a.org_party_id is null
and a.EFFECTIVE_START_DATE <= sysdate
and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
and a.USER_ID = f.USER_ID
and f.START_DATE <= sysdate
and nvl (f.END_DATE, sysdate + 1) > sysdate
order by a.APPROVER_SEQ;
select a.APPROVER_ID, a.USER_ID, f.USER_NAME
from JTF_UM_APPROVERS a, FND_USER f
where a.APPROVER_SEQ > l_approverSeq
and a.APPROVAL_ID = l_approvalID
and a.ORG_PARTY_ID = l_org_party_id
and a.EFFECTIVE_START_DATE <= sysdate
and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
and a.USER_ID = f.USER_ID
and f.START_DATE <= sysdate
and nvl (f.END_DATE, sysdate + 1) > sysdate
order by a.APPROVER_SEQ;
select 'X'
from JTF_UM_APPROVERS a,
FND_USER f
where a.APPROVAL_ID = l_approvalID
and a.ORG_PARTY_ID = l_org_party_id
and a.EFFECTIVE_START_DATE <= sysdate
and nvl (a.EFFECTIVE_END_DATE, sysdate + 1) > sysdate
and a.USER_ID = f.USER_ID
and f.START_DATE <= sysdate
and nvl (f.END_DATE, sysdate + 1) > sysdate;
select hzr.object_id requester_org_id
from hz_relationships hzr,
FND_USER fu
where fu.USER_ID = l_requesterUserID
and fu.CUSTOMER_ID = hzr.PARTY_ID
and hzr.start_date <= sysdate
and nvl (hzr.END_DATE, sysdate + 1) > sysdate
and hzr.relationship_code in ('EMPLOYEE_OF','CONTACT_OF')
and hzr.object_type = 'ORGANIZATION'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_table_name = 'HZ_PARTIES';
procedure SelectRequestType (itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2) is
--
requestType varchar2 (10);
JTF_DEBUG_PUB.LOG_DEBUG (2, G_MODULE, 'Exiting SelectRequestType API');
wf_core.context ('JTF_UM_WF_APPROVAL', 'SelectRequestType', itemtype, itemkey, to_char (actid), funcmode);
end SelectRequestType;
select wias.notification_id
from wf_process_activities wpa, wf_item_activity_statuses wias, wf_notifications wn
where wpa.PROCESS_ITEM_TYPE = p_itemtype
and wpa.ACTIVITY_ITEM_TYPE = wpa.PROCESS_ITEM_TYPE
and (wpa.INSTANCE_LABEL = 'NTF_APPROVAL_USERTYPE_REQUIRED'
or wpa.INSTANCE_LABEL = 'NTF_REMIND_USERTYPE_REQUIRED'
or wpa.INSTANCE_LABEL = 'NTF_FAIL_ESCALATE_USERTYPE_REQ')
and wias.item_type = wpa.PROCESS_ITEM_TYPE
and wias.item_key = p_itemkey
and wias.process_activity = wpa.instance_id
and wn.status = 'OPEN'
and wn.notification_id = wias.notification_id;
select USER_ID
from FND_USER
where USER_NAME = ownerUsername;
update JTF_UM_USERTYPE_REG
set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
APPROVER_USER_ID = ownerUserID
where USERTYPE_REG_ID = to_number(itemkey);
update JTF_UM_SUBSCRIPTION_REG
set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
APPROVER_USER_ID = ownerUserID
where SUBSCRIPTION_REG_ID = to_number(itemkey);
select USER_ID
from FND_USER
where USER_NAME = WF_ENGINE.context_text;
update JTF_UM_USERTYPE_REG
set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
APPROVER_USER_ID = userId
where USERTYPE_REG_ID = to_number(itemkey);
update JTF_UM_SUBSCRIPTION_REG
set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
APPROVER_USER_ID = userId
where SUBSCRIPTION_REG_ID = to_number(itemkey);
JTF_UM_SUBSCRIPTIONS_PKG.UPDATE_GRANT_DELEGATION_FLAG (
P_SUBSCRIPTION_ID => l_request_id,
P_USER_ID => l_requesterUserID,
P_GRANT_DELEGATION_FLAG => l_bool_flag);
select SUBSCRIPTION_ID
from JTF_UM_SUBSCRIPTION_REG
where USER_ID = requesterUserID
and EFFECTIVE_START_DATE <= sysdate
and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
and WF_ITEM_TYPE is null
and STATUS_CODE = 'PENDING';
select WF_ITEM_TYPE, SUBSCRIPTION_REG_ID
from JTF_UM_SUBSCRIPTION_REG
where USER_ID = requesterUserID
and EFFECTIVE_START_DATE <= sysdate
and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
and WF_ITEM_TYPE is not null
and STATUS_CODE = 'PENDING';
select USER_NAME
from FND_USER
where USER_ID = requesterUserID
and (nvl(END_DATE,sysdate) >= sysdate OR
to_char(END_DATE) = to_char(FND_API.G_MISS_DATE));
select language, territory into langProfileValue, terrProfileValue
from wf_roles
where name = '__JTA_UM' ||itemkey;
update JTF_UM_USERTYPE_REG
set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_APPROVER_COMMENT = approverComment
where USERTYPE_REG_ID = itemkey;
update JTF_UM_SUBSCRIPTION_REG
set LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_APPROVER_COMMENT = approverComment
where subscription_reg_id = itemkey;
select WF_ITEM_TYPE, SUBSCRIPTION_REG_ID
from JTF_UM_SUBSCRIPTION_REG
where USER_ID = requesterUserID
and EFFECTIVE_START_DATE <= sysdate
and nvl (EFFECTIVE_END_DATE, sysdate + 1) > sysdate
and STATUS_CODE = 'PENDING';
Select ut.APPLICATION_ID,ut.USERTYPE_KEY,reg.USERTYPE_REG_ID
From JTF_UM_USERTYPES_B ut , JTF_UM_USERTYPE_REG reg
where ut.USERTYPE_ID=reg.USERTYPE_ID and reg.USERTYPE_REG_ID=to_number(itemkey);
update JTF_UM_USERTYPE_REG
set STATUS_CODE = 'REJECTED',
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_APPROVER_COMMENT = approverComment,
EFFECTIVE_END_DATE = sysdate
where USERTYPE_REG_ID = itemkey;
update JTF_UM_SUBSCRIPTION_REG
set STATUS_CODE = 'USER_REJECTED',
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
EFFECTIVE_END_DATE = sysdate
where SUBSCRIPTION_REG_ID = enrollRegRow.SUBSCRIPTION_REG_ID;
Select start_date,end_date,USER_NAME,customer_id,person_party_id
Into userStartDate,userEndDate,requesterUsername,l_customer_id,l_person_party_id
From FND_USER
Where user_id = requesterUserID;
-- delete parameter list as it is no longer required
l_parameter_list.DELETE;
update JTF_UM_SUBSCRIPTION_REG
set STATUS_CODE = 'REJECTED',
LAST_APPROVER_COMMENT = approverComment,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
EFFECTIVE_END_DATE = sysdate
where SUBSCRIPTION_REG_ID = itemkey;
select user_id
from fnd_user
where (nvl (end_date, sysdate + 1) > sysdate
OR to_char(END_DATE) = to_char(FND_API.G_MISS_DATE))
and user_name = l_approver_username;
if (l_result = JTF_UM_WF_DELEGATION_PVT.CHECKED_UPDATE) then
-- Grant Delegation Flag is set to Yes.
wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'DELEGATION_FLAG',
avalue => 'Y');
elsif (l_result = JTF_UM_WF_DELEGATION_PVT.NOT_CHECKED_UPDATE) then
-- Grant Delegation Flag is set to No.
wf_engine.SetItemAttrText (itemtype => itemtype,
itemkey => itemkey,
aname => 'DELEGATION_FLAG',
avalue => 'N');
select USERTYPE_KEY, EMAIL_NOTIFICATION_FLAG
from JTF_UM_USERTYPES_B
where USERTYPE_ID = usertypeID;
lastUpdateDate in varchar2 := null) is
l_last_update_date varchar2 (14);
select to_char (last_update_date, 'mmddyyyyhh24miss')
from jtf_um_usertype_reg
where usertype_reg_id = to_number (itemkey);
select to_char (last_update_date, 'mmddyyyyhh24miss')
from jtf_um_subscription_reg
where subscription_reg_id = to_number (itemkey);
SELECT party.status FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS PREL
WHERE PARTY.PARTY_ID = PREL.OBJECT_ID
AND PREL.PARTY_ID = (select fnd.customer_id
from jtf_um_usertype_reg reg , fnd_user fnd
where usertype_reg_id = to_number(itemkey)
and reg.user_id=fnd.USER_ID
)
AND PREL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PREL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PREL.START_DATE < SYSDATE
AND NVL(PREL.END_DATE, SYSDATE+1) > SYSDATE
AND PREL.RELATIONSHIP_CODE in ('EMPLOYEE_OF', 'CONTACT_OF');
if lastUpdateDate is not null then
open getLUDFromUserReg;
fetch getLUDFromUserReg into l_last_update_date;
if (lastUpdateDate <> l_last_update_date) then
-- not the same request
raise_application_error (-20001, 'The last update date from the input parameter and the last update date stored in the database is different.');
if lastUpdateDate is not null then
open getLUDFromEnrollReg;
fetch getLUDFromEnrollReg into l_last_update_date;
if (lastUpdateDate <> l_last_update_date) then
-- not the same request
raise_application_error (-20001, 'The last update date from the input parameter and the last update date stored in the database is different.');
select usertype_reg_id,user_id
from jtf_um_usertype_reg
where usertype_id = p_usertype_id
and status_code = 'PENDING'
and nvl (effective_end_date, sysdate + 1) > sysdate;
select utreg.wf_item_type
from jtf_um_usertype_reg utreg, jtf_um_usertypes_b ut
where utreg.usertype_id = p_usertype_id
and utreg.usertype_id = ut.usertype_id
and utreg.status_code = 'PENDING'
and nvl (utreg.effective_end_date, sysdate + 1) > sysdate;
select wf_item_type
from jtf_um_approvals_b
where approval_id = p_new_approval_id;
update JTF_UM_USERTYPE_REG set effective_end_date = sysdate,
last_update_date = sysdate, last_updated_by = FND_GLOBAL.USER_ID
where usertype_reg_id = p_usertype_reg_id;
JTF_UM_USERTYPES_PKG.INSERT_UMREG_ROW (
X_USERTYPE_ID => p_usertype_id,
X_LAST_APPROVER_COMMENT => null,
X_APPROVER_USER_ID => null,
X_EFFECTIVE_END_DATE => null,
X_WF_ITEM_TYPE => p_wf_new_item_type,
X_EFFECTIVE_START_DATE => sysdate,
X_USERTYPE_REG_ID => p_new_item_key,
X_USER_ID => p_user_id,
X_STATUS_CODE => 'PENDING',
X_CREATION_DATE => sysdate,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => null);
select utreg.usertype_reg_id, utreg.user_id
from jtf_um_usertype_reg utreg, fnd_user fu, hz_relationships hzr
where utreg.usertype_id = p_usertype_id
and utreg.status_code = 'PENDING'
and nvl (utreg.effective_end_date, sysdate + 1) > sysdate
and utreg.user_id = fu.user_id
and fu.customer_id = hzr.party_id
and hzr.start_date <= sysdate
and nvl (hzr.end_date, sysdate + 1) > sysdate
and hzr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_id = p_org_party_id;
select utreg.wf_item_type
from jtf_um_usertype_reg utreg, jtf_um_usertypes_b ut
where utreg.usertype_id = p_usertype_id
and utreg.usertype_id = ut.usertype_id
and utreg.status_code = 'PENDING'
and nvl (utreg.effective_end_date, sysdate + 1) > sysdate;
select wf_item_type
from jtf_um_approvals_b
where approval_id = p_new_approval_id;
update JTF_UM_USERTYPE_REG set effective_end_date = sysdate,
last_update_date = sysdate, last_updated_by = FND_GLOBAL.USER_ID
where usertype_reg_id = p_usertype_reg_id
and user_id = p_user_id and status_code='PENDING';
JTF_UM_USERTYPES_PKG.INSERT_UMREG_ROW (
X_USERTYPE_ID => p_usertype_id,
X_LAST_APPROVER_COMMENT => null,
X_APPROVER_USER_ID => null,
X_EFFECTIVE_END_DATE => null,
X_WF_ITEM_TYPE => p_wf_new_item_type,
X_EFFECTIVE_START_DATE => sysdate,
X_USERTYPE_REG_ID => p_new_item_key,
X_USER_ID => p_user_id,
X_STATUS_CODE => 'PENDING',
X_CREATION_DATE => sysdate,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => null);
select subscription_reg_id, user_id, wf_item_type
from jtf_um_subscription_reg
where subscription_id = p_subscription_id
and status_code = 'PENDING'
and (effective_end_date is null
or effective_end_date > sysdate);
select subreg.subscription_reg_id, fu.user_id, subreg.wf_item_type
from jtf_um_subscription_reg subreg, fnd_user fu, hz_relationships hzr
where subreg.subscription_id = p_subscription_id
and subreg.status_code = 'PENDING'
and nvl (subreg.effective_end_date, sysdate + 1) > sysdate
and subreg.user_id = fu.user_id
and fu.customer_id = hzr.party_id
and hzr.start_date <= sysdate
and nvl (hzr.end_date, sysdate + 1) > sysdate
and hzr.relationship_code in ('EMPLOYEE_OF', 'CONTACT_OF')
and hzr.object_table_name = 'HZ_PARTIES'
and hzr.subject_table_name = 'HZ_PARTIES'
and hzr.object_id = p_org_party_id;
select wf_item_type
from jtf_um_approvals_b
where approval_id = p_new_approval_id
and (effective_end_date is null
or effective_end_date > sysdate);
select status_code
from jtf_um_usertype_reg
where user_id = p_user_id
and (effective_end_date is null
or effective_end_date > sysdate);
update JTF_UM_SUBSCRIPTION_REG
set EFFECTIVE_END_DATE = sysdate,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID
where SUBSCRIPTION_REG_ID = p_subscription_reg_id;
JTF_UM_SUBSCRIPTIONS_PKG.INSERT_SUBREG_ROW
(X_SUBSCRIPTION_ID => p_subscription_id,
X_LAST_APPROVER_COMMENT => null,
X_APPROVER_USER_ID => null,
X_EFFECTIVE_END_DATE => null,
X_WF_ITEM_TYPE => null,
X_EFFECTIVE_START_DATE => sysdate,
X_SUBSCRIPTION_REG_ID => p_new_item_key,
X_USER_ID => p_user_id,
X_STATUS_CODE => 'PENDING',
X_CREATION_DATE => sysdate,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => null);
update JTF_UM_SUBSCRIPTION_REG
set EFFECTIVE_END_DATE = sysdate,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID
where SUBSCRIPTION_REG_ID = p_subscription_reg_id;
JTF_UM_SUBSCRIPTIONS_PKG.INSERT_SUBREG_ROW
(X_SUBSCRIPTION_ID => p_subscription_id,
X_LAST_APPROVER_COMMENT => null,
X_APPROVER_USER_ID => null,
X_EFFECTIVE_END_DATE => null,
X_WF_ITEM_TYPE => p_wf_new_item_type,
X_EFFECTIVE_START_DATE => sysdate,
X_SUBSCRIPTION_REG_ID => p_new_item_key,
X_USER_ID => p_user_id,
X_STATUS_CODE => 'PENDING',
X_CREATION_DATE => sysdate,
X_CREATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
X_LAST_UPDATE_LOGIN => null);
cursor usertype_approval is select usertype_id from jtf_um_usertypes_b
where approval_id = p_approval_id
and nvl (effective_end_date, sysdate + 1) > sysdate;
cursor subscription_approval is select subscription_id from jtf_um_subscriptions_b
where approval_id = p_approval_id
and nvl (effective_end_date, sysdate + 1) > sysdate;