[Home] [Help]
PACKAGE BODY: APPS.WSH_XC_PURGE_PKG
Source
1 PACKAGE BODY WSH_XC_PURGE_PKG AS
2 /* $Header: WSHXCPRB.pls 115.10 2004/01/19 14:56:03 rahujain ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_XC_PURGE_PKG';
6 --
7 PROCEDURE Purge_Seed_Definition AS
8 -- -----------------------------------------------------------------------------------------
9 -- This procedure bumps the sequence number, removes the seeded exception
10 -- definitions and pushes the customer exception definitions up.
11 --
12 -- ------------------------------------------------------------------------------------------
13
14
15 l_sequence NUMBER := 0;
16
17
18
19 CURSOR c_exception_definition IS
20 --Changed for #BUG3330869
21 SELECT
22 -- SELECT
23 exception_definition_id,
24 update_allowed,
25 exception_name,
26 description,
27 exception_type,
28 default_severity,
29 exception_handling,
30 workflow_item_type,
31 workflow_process,
32 initiate_workflow,
33 attribute_category,
34 attribute1,
35 attribute2,
36 attribute3,
37 attribute4,
38 attribute5,
39 attribute6,
40 attribute7,
41 attribute8,
42 attribute9,
43 attribute10,
44 attribute11,
45 attribute12,
46 attribute13,
47 attribute14,
48 attribute15,
49 creation_date,
50 created_by,
51 last_update_date,
52 last_updated_by,
53 last_update_login
54 FROM WSH_EXCEPTION_DEFINITIONS_VL
55 WHERE EXCEPTION_DEFINITION_ID < 1001;
56
57
58
59
60 l_definition_rec c_exception_definition%ROWTYPE;
61
62 --
63 l_debug_on BOOLEAN;
64 --
65 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_SEED_DEFINITION';
66 --
67 BEGIN
68 -- --------------------------------------------------------------------
69 -- check the sequence, if it is less than 5000, consume the sequence
70 -- --------------------------------------------------------------------
71 --
72 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
73 --
74 IF l_debug_on IS NULL
75 THEN
76 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
77 END IF;
78 --
79 IF l_debug_on THEN
80 WSH_DEBUG_SV.push(l_module_name);
81 END IF;
82 --
83 LOOP
84 SELECT WSH_EXCEPTION_DEFINITIONS_S.nextval INTO l_sequence FROM SYS.DUAL ;
85 IF l_sequence > 1000 THEN
86 EXIT;
87 END IF;
88 END LOOP;
89
90 -- delete all the seeded exception
91
92 OPEN c_exception_definition;
93 FETCH c_exception_definition INTO l_definition_rec;
94 WHILE c_exception_definition%FOUND LOOP
95 --
96 WSH_XC_UTIL.delete_xc_def_form(l_definition_rec.exception_definition_id);
97 IF l_definition_rec.update_allowed ='Y' THEN
98 -- customer data
99 SELECT WSH_EXCEPTION_DEFINITIONS_S.nextval INTO l_sequence FROM DUAL;
100 WSH_XC_UTIL.insert_xc_def_form (
101 x_exception_definition_id => l_sequence ,
102 p_exception_name => l_definition_rec.exception_name,
103 p_description => l_definition_rec.description,
104 p_exception_type=> l_definition_rec.exception_type,
105 p_default_severity => l_definition_rec.default_severity,
106 p_exception_handling => l_definition_rec.exception_handling,
107 p_workflow_item_type => l_definition_rec.workflow_item_type,
108 p_workflow_process => l_definition_rec.workflow_process,
109 p_initiate_workflow => l_definition_rec.initiate_workflow,
110 p_update_allowed => l_definition_rec.update_allowed,
111 p_attribute_category => l_definition_rec.attribute_category,
112 p_attribute1 => l_definition_rec.attribute1,
113 p_attribute2 => l_definition_rec.attribute2,
114 p_attribute3 => l_definition_rec.attribute3,
115 p_attribute4 => l_definition_rec.attribute4,
116 p_attribute5 => l_definition_rec.attribute5,
117 p_attribute6 => l_definition_rec.attribute6,
118 p_attribute7 => l_definition_rec.attribute7,
119 p_attribute8 => l_definition_rec.attribute8,
120 p_attribute9 => l_definition_rec.attribute9,
121 p_attribute10 => l_definition_rec.attribute10,
122 p_attribute11 => l_definition_rec.attribute11,
123 p_attribute12 => l_definition_rec.attribute12,
124 p_attribute13 => l_definition_rec.attribute13,
125 p_attribute14 => l_definition_rec.attribute14,
126 p_attribute15 => l_definition_rec.attribute15,
127 p_creation_date => l_definition_rec.creation_date,
128 p_created_by => l_definition_rec.created_by,
129 p_last_update_date => l_definition_rec.last_update_date,
130 p_last_updated_by => l_definition_rec.last_updated_by,
131 p_last_update_login => l_definition_rec.last_update_login
132 );
133
134
135
136 END IF;
137 FETCH c_exception_definition INTO l_definition_rec;
138 END LOOP;
139 CLOSE c_exception_definition;
140 commit;
141 --
142 IF l_debug_on THEN
143 WSH_DEBUG_SV.pop(l_module_name);
144 END IF;
145 --
146 END Purge_Seed_Definition;
147
148 END WSH_XC_PURGE_PKG;