[Home] [Help]
66: order by UPPER(rt.name) asc;
67:
68: cursor c_emailProcs(v_rule_type varchar2) is
69: select ep.emailproc_id, ep.name, ep.description, ep.rule_type, fu.user_name, to_char(ep.creation_date) creation_date
70: from iem_emailprocs ep, fnd_user fu
71: where ep.created_by=fu.user_id and ep.rule_type= v_rule_type
72: order by UPPER(name);
73: BEGIN
74:
284:
285: cursor c_emailProcs( v_acct_Id number, v_rule_type varchar2) is
286: select a.account_emailProc_id, a.priority, r.emailproc_id, r.name,r.description,
287: a.enabled_flag
288: from iem_emailprocs r, iem_account_emailprocs a
289: where r.emailproc_id = a.emailproc_id and a.email_account_id = v_acct_Id
290: and r.rule_type = v_rule_type order by a.priority asc;
291:
292: --used to retrieve rule types with different actions
292: --used to retrieve rule types with different actions
293: cursor c_emailProc_rrr( v_acct_Id number, v_rule_type varchar2) is
294: select a.account_emailProc_id, a.priority, r.emailproc_id, r.name, r.description,
295: b.action, a.enabled_flag
296: from iem_emailprocs r, iem_account_emailprocs a, iem_actions b
297: where r.emailproc_id = a.emailproc_id and a.email_account_id = v_acct_Id
298: and r.rule_type = v_rule_type and a.emailproc_id = b.emailproc_id order by a.priority asc;
299:
300: cursor c_routes(v_acct_Id number) is
561: ( Select priority from iem_account_emailprocs
562: where emailproc_id=p_emailProc_id
563: and email_account_id=p_acct_id)
564: and emailproc_id in
565: ( select emailproc_id from iem_emailprocs
566: where rule_type = p_rule_type );
567: DELETE
568: FROM iem_account_emailprocs
569: WHERE emailproc_id = p_emailProc_id and email_account_id = p_acct_id;
698: --API Body
699:
700:
701: --check if the route_id exist before update
702: select count(*) into l_route from iem_emailprocs where emailproc_id = p_emailproc_id;
703:
704: if l_route < 1 then
705: raise IEM_NO_ROUTE_UPDATE;
706: end if;
822:
823: -- check if exist at least one rule for each route
824:
825: select all_email into l_all_emails
826: from iem_emailprocs where emailproc_id = p_emailproc_id;
827:
828: if l_all_emails<>'Y' then
829: select count(*) into l_rule_count from iem_emailproc_rules where emailproc_id = p_emailproc_id;
830:
1014:
1015: l_api_name VARCHAR2(255):='create_emailproc_wrap';
1016: l_api_version_number NUMBER:=1.0;
1017:
1018: l_emailproc_id IEM_EMAILPROCS.EMAILPROC_ID%TYPE;
1019: l_route_rule_id IEM_EMAILPROC_RULES.EMAILPROC_RULE_ID%TYPE;
1020: l_action_id IEM_ACTIONS.ACTION_ID%type;
1021: l_param2_count NUMBER := 0;
1022: l_param_tag_count NUMBER := 0;
1083: end if;
1084: end if;
1085: end if;
1086:
1087: --Create iem_emailprocs
1088: iem_emailproc_hdl_pvt.create_item_emailprocs (
1089: p_api_version_number=>p_api_version_number,
1090: p_init_msg_list => p_init_msg_list,
1091: p_commit => FND_API.G_FALSE,
1313: x_return_status := FND_API.G_RET_STS_SUCCESS;
1314:
1315:
1316: -- check if the route_id exist in iem_routes
1317: select count(*) into l_count from iem_emailprocs
1318: where emailproc_id = p_emailproc_id;
1319:
1320: if l_count < 1 then
1321:
1331: raise IEM_ADMIN_ACCOUNT_NOT_EXIST;
1332: end if;
1333:
1334: select count(*) into l_redirect_same_acct
1335: from iem_emailprocs a, iem_actions b, iem_action_dtls c
1336: where a.emailproc_id = p_emailproc_id and a.emailproc_id=b.emailproc_id
1337: and a.rule_type='AUTOREDIRECT' and b.action='AUTOREDIRECT_INTERNAL'
1338: and b.action_id=c.action_id and c.parameter1=to_char(p_email_account_id);
1339:
1637: x_return_status := FND_API.G_RET_STS_SUCCESS;
1638:
1639: --Actual API starts here
1640: DELETE
1641: FROM IEM_EMAILPROCS
1642: WHERE emailproc_id = p_emailproc_id;
1643:
1644: if SQL%NOTFOUND then
1645: raise IEM_ROUTE_NOT_DELETED;
1657:
1658: where email_account_id=acct_id.email_account_id
1659: and emailproc_id in
1660: ( select emailproc_id
1661: from iem_emailprocs
1662: where rule_type=p_rule_type )
1663: and priority > (Select priority from iem_account_emailprocs
1664: where emailproc_id=p_emailproc_id and email_account_id = acct_id.email_account_id);
1665: END LOOP;