1 PACKAGE BODY OE_Holds_WF as
2 /* $Header: OEXWHLDB.pls 120.0 2005/05/31 23:08:06 appldev noship $ */
3
4 procedure APPLY_HOLDS(
5 itemtype in varchar2,
6 itemkey in varchar2,
7 actid in number,
8 funcmode in varchar2,
9 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
10 is
11 l_line_id NUMBER;
12 l_header_id NUMBER;
13 l_hold_id NUMBER;
14 l_reason_code VARCHAR2(30);
15 l_entity_code NUMBER;
16 l_entity_id NUMBER;
17 l_comment VARCHAR2(30);
18 begin
19 -- start data fix project
20 OE_STANDARD_WF.Set_Msg_Context(actid);
21 -- end data fix project
22 --
23 -- RUN mode - normal process execution
24 --
25 if (funcmode = 'RUN') then
26
27 null;
28
29 end if;
30 --
31 -- CANCEL mode - activity 'compensation'
32 --
33 -- This is an event point is called with the effect of the activity must
34 -- be undone, for example when a process is reset to an earlier point
35 -- due to a loop back.
36 --
37 if (funcmode = 'CANCEL') then
38
39 -- your cancel code goes here
40 null;
41
42 -- no result needed
43 resultout := 'COMPLETE';
44 return;
45 end if;
46
47
48 --
49 -- Other execution modes may be created in the future. Your
50 -- activity will indicate that it does not implement a mode
51 -- by returning null
52 --
53 -- resultout := '';
54 -- return;
55
56 exception
57 when others then
58 -- The line below records this function call in the error system
59 -- in the case of an exception.
60 wf_core.context('OE_Holds_WF', 'APPLY_HOLDS',
61 itemtype, itemkey, to_char(actid), funcmode);
62 -- start data fix project
63 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
64 p_itemtype => itemtype,
65 p_itemkey => itemkey);
66 OE_STANDARD_WF.Save_Messages;
67 OE_STANDARD_WF.Clear_Msg_Context;
68 -- end data fix project
69 raise;
70 end APPLY_HOLDS;
71
72 procedure CHECK_HOLDS(
73 itemtype in varchar2,
74 itemkey in varchar2,
75 actid in number,
76 funcmode in varchar2,
77 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
78 is
79 l_line_id NUMBER := NULL;
80 l_header_id NUMBER := NULL;
81 l_msg_count NUMBER := 0;
82 l_msg_data VARCHAR2(2000);
83 l_result_out VARCHAR2(30);
84 l_return_status VARCHAR2(30);
85 l_wf_item VARCHAR2(8);
86 l_wf_activity VARCHAR2(30);
87 begin
88 -- start data fix project
89 OE_STANDARD_WF.Set_Msg_Context(actid);
90 -- end data fix project
91 --
92 -- RUN mode - normal process execution
93 --
94 if (funcmode = 'RUN') then
95
96 -- Retrieving the order header_id/line_id based on the current
97 -- workflow(itemtype)
98 IF itemtype = 'OEOH' THEN
99
100 l_header_id := to_number(itemkey);
101
102 ELSIF itemtype = 'OEOL' THEN
103
104 l_line_id := to_number(itemkey);
105 SELECT header_id
106 INTO l_header_id
107 FROM oe_order_lines
108 WHERE line_id = to_number(itemkey);
109
110 -- Not needed anymore.
111 --ELSIF itemtype='OECHGORD' THEN
112 --
113 -- SELECT header_id, line_id
114 -- INTO l_header_id, l_line_id
115 -- FROM oe_line_pending_actions
116 -- WHERE wf_key_id = to_number(itemkey);
117 --
118 -- IF l_header_id IS NULL then
119 -- SELECT header_id
120 -- INTO l_header_id
121 -- FROM oe_order_lines
122 -- WHERE line_id = l_line_id;
123 -- END IF;
124
125 END IF; -- end for the itemtype check
126
127
128 -- Retrieving internal name and itemtype of the parent process.
129
130 SELECT PARENT.ACTIVITY_ITEM_TYPE, PARENT.ACTIVITY_NAME
131 INTO l_wf_item, l_wf_activity
132 FROM WF_ITEM_ACTIVITY_STATUSES IAS,
133 WF_PROCESS_ACTIVITIES CHILD,
134 WF_PROCESS_ACTIVITIES PARENT
135 WHERE CHILD.INSTANCE_ID = actid
136 AND CHILD.PROCESS_ITEM_TYPE = PARENT.ACTIVITY_ITEM_TYPE
137 AND CHILD.PROCESS_NAME = PARENT.ACTIVITY_NAME
138 AND PARENT.INSTANCE_ID = IAS.PROCESS_ACTIVITY
139 AND IAS.ITEM_TYPE = itemtype
140 AND IAS.ITEM_KEY = itemkey;
141
142
143 OE_Holds_PUB.Check_Holds
144 ( p_api_version => 1.0
145 , p_init_msg_list => FND_API.G_FALSE
146 , p_commit => FND_API.G_FALSE
147 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
148 , x_return_status => l_return_status
149 , x_msg_count => l_msg_count
150 , x_msg_data => l_msg_data
151 , p_header_id => l_header_id
152 , p_line_id => l_line_id
153 , p_wf_item => l_wf_item
154 , p_wf_activity => l_wf_activity
155 , x_result_out => l_result_out
156 );
157
158
159 if l_return_status = FND_API.G_RET_STS_SUCCESS then
160
161 if l_result_out = FND_API.G_TRUE then
162 resultout := 'NOTIFIED:HOLDS EXIST';
163 elsif l_result_out = FND_API.G_FALSE then
164 resultout := 'COMPLETE:NO_HOLDS';
165 end if;
166
167 else
168 raise program_error;
169
170 end if;
171
172 return;
173
174 end if; -- End for 'RUN' mode
175 --
176 -- CANCEL mode - activity 'compensation'
177 --
178 -- This is an event point is called with the effect of the activity must
179 -- be undone, for example when a process is reset to an earlier point
180 -- due to a loop back.
181 --
182 if (funcmode = 'CANCEL') then
183
184 -- your cancel code goes here
185 null;
186
187 -- no result needed
188 resultout := 'COMPLETE';
189 return;
190 end if;
191
192
193 --
194 -- Other execution modes may be created in the future. Your
195 -- activity will indicate that it does not implement a mode
196 -- by returning null
197 --
198 -- resultout := '';
199 -- return;
200
201 exception
202 when others then
203 -- The line below records this function call in the error system
204 -- in the case of an exception.
205 wf_core.context('OE_Holds_WF', 'CHECK_HOLDS',
206 itemtype, itemkey, to_char(actid), funcmode);
207 -- start data fix project
208 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
209 p_itemtype => itemtype,
210 p_itemkey => itemkey);
211 OE_STANDARD_WF.Save_Messages;
212 OE_STANDARD_WF.Clear_Msg_Context;
213 -- end data fix project
214 raise;
215 end CHECK_HOLDS;
216
217
218 procedure RELEASE_HOLDS(
219 itemtype in varchar2,
220 itemkey in varchar2,
221 actid in number,
222 funcmode in varchar2,
223 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
224 is
225 l_line_id NUMBER;
226 l_header_id NUMBER;
227 l_hold_id NUMBER;
228 l_hold_source_id NUMBER;
229 l_reason_code VARCHAR2(30);
230 l_entity_code NUMBER;
231 l_entity_id NUMBER;
232 l_comment VARCHAR2(30);
233 l_return_status VARCHAR2(240);
234 begin
235 -- start data fix project
236 OE_STANDARD_WF.Set_Msg_Context(actid);
237 -- end data fix project
238 --
239 -- RUN mode - normal process execution
240 --
241 if (funcmode = 'RUN') then
242
243 null;
244
245 end if;
246 --
247 -- CANCEL mode - activity 'compensation'
248 --
249 -- This is an event point is called with the effect of the activity must
250 -- be undone, for example when a process is reset to an earlier point
251 -- due to a loop back.
252 --
253 if (funcmode = 'CANCEL') then
254
255 -- your cancel code goes here
256 null;
257
258 -- no result needed
259 resultout := 'COMPLETE';
260 return;
261 end if;
262
263
264 --
265 -- Other execution modes may be created in the future. Your
266 -- activity will indicate that it does not implement a mode
267 -- by returning null
268 --
269 -- resultout := '';
270 -- return;
271
272 exception
273 when others then
274 -- The line below records this function call in the error system
275 -- in the case of an exception.
276 wf_core.context('OE_Holds_WF', 'RELEASE_HOLDS',
277 itemtype, itemkey, to_char(actid), funcmode);
278 -- start data fix project
279 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
280 p_itemtype => itemtype,
281 p_itemkey => itemkey);
282 OE_STANDARD_WF.Save_Messages;
283 OE_STANDARD_WF.Clear_Msg_Context;
284 -- end data fix project
285 raise;
286 end RELEASE_HOLDS;
287
288 END OE_Holds_WF;