1 package body FND_FORM_CUSTOM_RULES_PKG as
2 /* $Header: AFFRRULB.pls 120.6 2010/08/07 21:34:04 dbowles ship $ */
3
4 /*
5 ** Last Delete Context:
6 **
7 ** The Forms Personalization Loader (affrmcus.lct) uses a replace strategy
8 ** for upload (delete then insert) rather than the ususal merge. This is
9 ** because the individual personalization rules do not have a portable unique
10 ** key, and also for simplicity of operation.
11 **
12 ** Since there can be multiple rules per target form/function/rule key
13 ** combination, we must only delete the data for a particular context
14 ** once during an upload session.
15 **
16 ** Warning: requires all rules for a given context to be loaded from the same
17 ** data file (LDT) and requires rules within the file to be grouped by context.
18 */
19 g_last_delete_rule_key VARCHAR2(30) := NULL;
20 g_last_delete_rule_type VARCHAR2(30) := NULL;
21 g_last_delete_function VARCHAR2(30) := NULL;
22 g_last_delete_form VARCHAR2(30) := NULL;
23 g_curr_seq NUMBER := 0;
24
25
26
27
28
29 /*
30 ** DELETE_SET - Delete a set of Forms Personalization Rules
31 ** (see AFFRRULS.pls for spec)
32 */
33 PROCEDURE DELETE_SET(
34 X_RULE_KEY IN VARCHAR2,
35 X_RULE_TYPE IN VARCHAR2,
36 X_FUNCTION_NAME IN VARCHAR2,
37 X_FORM_NAME IN VARCHAR2) IS
38
39
40 BEGIN
41 /*
42 ** Check if last delete context matches exactly
43 **
44 ** Note: A NULL RULE_KEY is considered a valid, distinct context value
45 ** but the other values should not be null
46 */
47 /* Bug 9975835 Added g_curr_seq and set to FND_FORM_CUSTOM_RULES_S.NextVal
48 ** Once we have deleted all of the personalizations for a given form where the
49 ** rule type is F, we don't want to delete again if processing an ldt that has multiple
50 ** functions that with a rule type of F for a given form.
51 ** When we insert the data into the FND_FORM_CUSTOM_RULES, the id has a value of FND_FORM_CUSTOM_RULES_S.NextVal (see affrmcus.lct).
52 ** Since we delete before inserting the new rows will always have an id greater then the value of g_curr_seq
53 ** WHERE clauses modified for the X_RULE_TYPE = 'F' to only delete rows that have an id < g_curr_seq
54 */
55 if g_curr_seq = 0 then
56 select FND_FORM_CUSTOM_RULES_S.NextVal into g_curr_seq from dual;
57 end if;
58 if nvl(X_RULE_KEY, '*NULL*') = nvl(g_last_delete_rule_key, '*NULL*') and
59 X_RULE_TYPE = g_last_delete_rule_type and
60 ((X_RULE_TYPE = 'A' and X_FUNCTION_NAME = g_last_delete_function) or
61 (X_RULE_TYPE = 'F' and X_FORM_NAME = g_last_delete_form)) then
62 /* delete for this context was already completed, just exit */
63 return;
64 end if;
65
66 /*
67 ** Delete Forms Personalizations for specified context
68 */
69 delete from fnd_form_custom_params
70 where action_id in
71 (select action_id
72 from fnd_form_custom_actions a , fnd_form_custom_rules r
73 where a.rule_id = r.id
74 and nvl(r.rule_key, '*NULL*') = nvl(X_RULE_KEY,'*NULL*')
75 and ((X_RULE_TYPE = 'A' and r.rule_type = 'A' and r.function_name = X_FUNCTION_NAME) or
76 (X_RULE_TYPE = 'F' and r.rule_type = 'F' and r.form_name = X_FORM_NAME and r.id < g_curr_seq )));
77
78
79 delete from fnd_form_custom_actions
80 where rule_id in
81 (select id
82 from fnd_form_custom_rules r
83 where nvl(r.rule_key, '*NULL*') = nvl(X_RULE_KEY,'*NULL*')
84 and ((X_RULE_TYPE = 'A' and r.rule_type = 'A' and r.function_name = X_FUNCTION_NAME) or
85 (X_RULE_TYPE = 'F' and r.rule_type = 'F' and r.form_name = X_FORM_NAME and r.id < g_curr_seq )));
86
87 delete from fnd_form_custom_scopes
88 where rule_id in
89 (select id
90 from fnd_form_custom_rules r
91 where nvl(r.rule_key, '*NULL*') = nvl(X_RULE_KEY,'*NULL*')
92 and ((X_RULE_TYPE = 'A' and r.rule_type = 'A' and r.function_name = X_FUNCTION_NAME) or
93 (X_RULE_TYPE = 'F' and r.rule_type = 'F' and r.form_name = X_FORM_NAME and r.id < g_curr_seq )));
94
95 delete from fnd_form_custom_rules r
96 where nvl(r.rule_key, '*NULL*') = nvl(X_RULE_KEY,'*NULL*')
97 and ((X_RULE_TYPE = 'A' and r.rule_type = 'A' and r.function_name = X_FUNCTION_NAME) or
98 (X_RULE_TYPE = 'F' and r.rule_type = 'F' and r.form_name = X_FORM_NAME and r.id < g_curr_seq ));
99
100
101
102 /*
103 ** save last delete context
104 */
105 g_last_delete_rule_key := X_RULE_KEY;
106 g_last_delete_rule_type := X_RULE_TYPE;
107 g_last_delete_function := X_FUNCTION_NAME;
108 g_last_delete_form := X_FORM_NAME;
109
110 END DELETE_SET;
111
112 END FND_FORM_CUSTOM_RULES_PKG;
[Home] [Help]