[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;
701: --API Body
702:
703:
704: --check if the route_id exist before update
705: select count(*) into l_route from iem_emailprocs where emailproc_id = p_emailproc_id;
706:
707: if l_route < 1 then
708: raise IEM_NO_ROUTE_UPDATE;
709: end if;
825:
826: -- check if exist at least one rule for each route
827:
828: select all_email into l_all_emails
829: from iem_emailprocs where emailproc_id = p_emailproc_id;
830:
831: if l_all_emails<>'Y' then
832: select count(*) into l_rule_count from iem_emailproc_rules where emailproc_id = p_emailproc_id;
833:
1032:
1033: l_api_name VARCHAR2(255):='create_emailproc_wrap';
1034: l_api_version_number NUMBER:=1.0;
1035:
1036: l_emailproc_id IEM_EMAILPROCS.EMAILPROC_ID%TYPE;
1037: l_route_rule_id IEM_EMAILPROC_RULES.EMAILPROC_RULE_ID%TYPE;
1038: l_action_id IEM_ACTIONS.ACTION_ID%type;
1039: l_param2_count NUMBER := 0;
1040: l_param3_count NUMBER := 0;
1103: end if;
1104: end if;
1105: end if;
1106:
1107: --Create iem_emailprocs
1108: iem_emailproc_hdl_pvt.create_item_emailprocs (
1109: p_api_version_number=>p_api_version_number,
1110: p_init_msg_list => p_init_msg_list,
1111: p_commit => FND_API.G_FALSE,
1346: x_return_status := FND_API.G_RET_STS_SUCCESS;
1347:
1348:
1349: -- check if the route_id exist in iem_routes
1350: select count(*) into l_count from iem_emailprocs
1351: where emailproc_id = p_emailproc_id;
1352:
1353: if l_count < 1 then
1354:
1364: raise IEM_ADMIN_ACCOUNT_NOT_EXIST;
1365: end if;
1366:
1367: select count(*) into l_redirect_same_acct
1368: from iem_emailprocs a, iem_actions b, iem_action_dtls c
1369: where a.emailproc_id = p_emailproc_id and a.emailproc_id=b.emailproc_id
1370: and a.rule_type='AUTOREDIRECT' and b.action='AUTOREDIRECT_INTERNAL'
1371: and b.action_id=c.action_id and c.parameter1=to_char(p_email_account_id);
1372:
1670: x_return_status := FND_API.G_RET_STS_SUCCESS;
1671:
1672: --Actual API starts here
1673: DELETE
1674: FROM IEM_EMAILPROCS
1675: WHERE emailproc_id = p_emailproc_id;
1676:
1677: if SQL%NOTFOUND then
1678: raise IEM_ROUTE_NOT_DELETED;
1690:
1691: where email_account_id=acct_id.email_account_id
1692: and emailproc_id in
1693: ( select emailproc_id
1694: from iem_emailprocs
1695: where rule_type=p_rule_type )
1696: and priority > (Select priority from iem_account_emailprocs
1697: where emailproc_id=p_emailproc_id and email_account_id = acct_id.email_account_id);
1698: END LOOP;