[Home] [Help]
PACKAGE BODY: APPS.WIP_WF_UPDATE_DATE
Source
1 PACKAGE BODY wip_wf_update_date AS
2 /*$Header: wipwfdtb.pls 115.16 2003/09/11 00:35:42 seli ship $ */
3
4 /* Initiate NEED_BY_DATE change workflow.
5 only pass in IDs from web. resolve names here */
6 PROCEDURE StartNBDWFProcess (item_type in varchar2 default null,
7 item_key in varchar2,
8 workflow_process in varchar2 default null,
9 p_init_scheduler in varchar2,
10 p_wip_entity_id in number,
11 p_wip_entity_name in varchar2,
12 p_organization_id in number,
13 p_rep_schedule_id in number,
14 p_wip_line_id in number,
15 p_wip_line_code in varchar2,
16 p_end_assembly_num in varchar2,
17 p_end_assembly_desc in varchar2,
18 p_po_number in varchar2,
19 p_new_need_by_date in date,
20 p_old_need_by_date in date,
21 p_comments in varchar2,
22 p_po_distribution_id in number,
23 p_operation_seq_num in number) IS
24
25 x_item_number varchar2(100);
26 x_item_desc varchar2(240);
27 x_po_line_id number;
28 x_line_location_id number;
29 x_po_header_id number;
30 x_org_id number;
31 x_qty_ordered number;
32 x_uom varchar2(25);
33 x_curr_promise_date date;
34 x_subcontractor_name PO_VENDORS.VENDOR_NAME%TYPE;
35 x_subcontractor_site VARCHAR2(80);
36
37
38 BEGIN
39
40 wf_engine.CreateProcess( itemtype => item_type,
41 itemkey => item_key,
42 process => workflow_process);
43
44 /* Get OSP Item and description */
45 begin
46 select MSI.concatenated_segments, pl.ITEM_DESCRIPTION
47 into x_item_number, x_item_desc
48 from po_lines_all pl,
49 po_distributions_all pd,
50 mtl_system_items_kfv msi
51 where pd.po_distribution_id = p_po_distribution_id
52 and pd.po_line_id = pl.po_line_id
53 and msi.organization_id = p_organization_id
54 and pl.item_id = msi.inventory_item_id;
55
56 exception
57 when No_Data_Found then
58 x_item_number := null;
59 x_item_desc := null;
60 when others then
61 null;
62 end;
63
64 /* get necessary PO data */
65 select po_line_id, line_location_id, po_header_id, org_id,
66 quantity_ordered
67 into x_po_line_id, x_line_location_id, x_po_header_id, x_org_id,
68 x_qty_ordered
69 from po_distributions_all
70 where po_distribution_id = p_po_distribution_id;
71
72 begin
73 select promised_date
74 into x_curr_promise_date
75 from po_line_locations_all
76 where line_location_id = x_line_location_id;
77
78 exception
79 when No_Data_Found then
80 x_curr_promise_date := null;
81 when others then
82 null;
83 end;
84
85 /* get vendor data */
86 begin
87 select pv.vendor_name, pvs.vendor_site_code
88 into x_subcontractor_name, x_subcontractor_site
89 from po_vendors pv, po_vendor_sites_all pvs, po_headers_all ph
90 where ph.po_header_id = x_po_header_id
91 and pv.vendor_id = ph.vendor_id
92 and pvs.vendor_site_id = ph.vendor_site_id
93 and pvs.org_id = ph.org_id;
94
95 exception
96 when No_Data_Found then
97 x_subcontractor_name := null;
98 x_subcontractor_site := null;
99 when others then
100 null;
101 end;
102
103 /* get PO UOM */
104 begin
105 select unit_meas_lookup_code
106 into x_uom
107 from po_lines_all
108 where po_line_id = x_po_line_id;
109
110 exception
111 when No_Data_Found then
112 x_curr_promise_date := null;
113 when others then
114 null;
115 end;
116
117
118 /* Set Attributes */
119 wf_engine.SetItemAttrNumber( itemtype => item_type,
120 itemkey => item_key,
121 aname => 'WIP_ENTITY_ID',
122 avalue => p_wip_entity_id);
123
124 wf_engine.SetItemAttrText( itemtype => item_type,
125 itemkey => item_key,
126 aname => 'WIP_ENTITY_NAME',
127 avalue => p_wip_entity_name);
128
129 wf_engine.SetItemAttrNumber( itemtype => item_type,
130 itemkey => item_key,
131 aname => 'REP_SCHEDULE_ID',
132 avalue => p_rep_schedule_id);
133
134 wf_engine.SetItemAttrNumber( itemtype => item_type,
135 itemkey => item_key,
136 aname => 'ORGANIZATION_ID',
137 avalue => p_organization_id);
138
139 wf_engine.SetItemAttrNumber( itemtype => item_type,
140 itemkey => item_key,
141 aname => 'ORG_ID',
142 avalue => x_org_id);
143
144 wf_engine.SetItemAttrNumber( itemtype => item_type,
145 itemkey => item_key,
146 aname => 'WIP_LINE_ID',
147 avalue => p_wip_line_id);
148
149 wf_engine.SetItemAttrText( itemtype => item_type,
150 itemkey => item_key,
151 aname => 'WIP_LINE_CODE',
152 avalue => p_wip_line_code);
153
154 wf_engine.SetItemAttrText( itemtype => item_type,
155 itemkey => item_key,
156 aname => 'ASSEMBLY_NUMBER',
157 avalue => p_end_assembly_num);
158
159 wf_engine.SetItemAttrText( itemtype => item_type,
160 itemkey => item_key,
161 aname => 'ASSEMBLY_DESC',
162 avalue => p_end_assembly_desc);
163
164 wf_engine.SetItemAttrNumber( itemtype => item_type,
165 itemkey => item_key,
166 aname => 'WIP_OP_SEQ',
167 avalue => p_operation_seq_num);
168
169 wf_engine.SetItemAttrText( itemtype => item_type,
170 itemkey => item_key,
171 aname => 'PO_NUMBER',
172 avalue => p_po_number);
173
174 wf_engine.SetItemAttrText( itemtype => item_type,
175 itemkey => item_key,
176 aname => 'ITEM_NUMBER',
177 avalue => x_item_number);
178
179 wf_engine.SetItemAttrText( itemtype => item_type,
180 itemkey => item_key,
181 aname => 'OSP_ITEM_DESC',
182 avalue => x_item_desc);
183
184 wf_engine.SetItemAttrNumber( itemtype => item_type,
185 itemkey => item_key,
186 aname => 'QUANTITY_ORDERED',
187 avalue => x_qty_ordered);
188
189 wf_engine.SetItemAttrText( itemtype => item_type,
190 itemkey => item_key,
191 aname => 'UOM',
192 avalue => x_uom);
193
194 wf_engine.SetItemAttrNumber( itemtype => item_type,
195 itemkey => item_key,
196 aname => 'PO_HEADER_ID',
197 avalue => x_po_header_id);
198
199 wf_engine.SetItemAttrNumber( itemtype => item_type,
200 itemkey => item_key,
201 aname => 'LINE_LOCATION_ID',
202 avalue => x_line_location_id);
203
204 wf_engine.SetItemAttrNumber( itemtype => item_type,
205 itemkey => item_key,
206 aname => 'PO_DISTRIBUTION_ID',
207 avalue => p_po_distribution_id);
208
209 wf_engine.SetItemAttrDate( itemtype => item_type,
210 itemkey => item_key,
211 aname => 'NEW_NEED_BY_DATE',
212 avalue => p_new_need_by_date);
213
214 wf_engine.SetItemAttrDate( itemtype => item_type,
215 itemkey => item_key,
216 aname => 'OLD_NEED_BY_DATE',
217 avalue => p_old_need_by_date);
218
219 wf_engine.SetItemAttrDate( itemtype => item_type,
220 itemkey => item_key,
221 aname => 'OLD_PROMISE_DATE',
222 avalue => x_curr_promise_date);
223
224 wf_engine.SetItemAttrText( itemtype => item_type,
225 itemkey => item_key,
226 aname => 'COMMENTS',
227 avalue => p_comments);
228
229 wf_engine.SetItemAttrText( itemtype => item_type,
230 itemkey => item_key,
231 aname => 'SUPPLIER',
232 avalue => x_subcontractor_name);
233
234 wf_engine.SetItemAttrText( itemtype => item_type,
235 itemkey => item_key,
236 aname => 'PRODUCTION_SCHEDULER',
237 avalue => p_init_scheduler);
238
239 wf_engine.SetItemAttrNumber( itemtype => item_type,
240 itemkey => item_key,
241 aname => 'ITEM_KEY',
242 avalue => item_key);
243
244 /* Start Process */
245 wf_engine.StartProcess( itemtype => item_type,
246 itemkey => item_key);
247
248 /* Set workflow process to background for better performance */
249 /* wf_engine.threshold := -1;*/
250
251 EXCEPTION
252
253 when others then
254 wf_core.context('WIP_WF_UPDATE_DATE', 'StartWFNBDProcess', item_key);
255 raise;
256
257 END StartNBDWFProcess;
258
259 /* update dates in PO_LINE_LOCATIONS */
260 PROCEDURE update_need_by_date( itemtype in varchar2,
261 itemkey in varchar2,
262 actid in number,
263 funcmode in varchar2,
264 resultout out nocopy varchar2) is
265
266 l_line_location_id number :=
267 wf_engine.GetItemAttrNumber( itemtype => itemtype,
268 itemkey => itemkey,
269 aname => 'LINE_LOCATION_ID');
270
271 l_new_need_by_date date :=
272 wf_engine.GetItemAttrDate( itemtype => itemtype,
273 itemkey => itemkey,
274 aname => 'NEW_NEED_BY_DATE');
275
276
277 BEGIN
278
279 if (funcmode = 'RUN') then
280
281 PO_UPDATE_DATE_PKG.UPDATE_NEED_BY_DATE(l_line_location_id,
282 l_new_need_by_date);
283
284 resultout := 'COMPLETE:';
285 return;
286
287 end if;
288
289 if (funcmode = 'CANCEL') then
290
291 resultout := 'COMPLETE:';
292 return;
293
294 end if;
295
296 if (funcmode = 'TIMEOUT') then
297
298 resultout := 'COMPLETE:';
299 return;
300
301 end if;
302
303 EXCEPTION
304
305 when others then
306 wf_core.context('WIP_WF_UPDATE_DATE', 'Update Need By Date', itemtype,
307 itemkey, actid, funcmode);
308 raise;
309
310 END update_need_by_date;
311
312 /* update dates in PO_LINE_LOCATIONS */
313 PROCEDURE update_promise_date( itemtype in varchar2,
314 itemkey in varchar2,
315 actid in number,
316 funcmode in varchar2,
317 resultout out nocopy varchar2) is
318
319 l_line_location_id number :=
320 wf_engine.GetItemAttrNumber( itemtype => itemtype,
321 itemkey => itemkey,
322 aname => 'LINE_LOCATION_ID');
323
324 l_new_promise_date date :=
325 wf_engine.GetItemAttrDate( itemtype => itemtype,
326 itemkey => itemkey,
327 aname => 'NEW_NEED_BY_DATE');
328
329 BEGIN
330
331 if (funcmode = 'RUN') then
332
333 PO_UPDATE_DATE_PKG.UPDATE_PROMISED_DATE(l_line_location_id,
334 l_new_promise_date);
335
336 resultout := 'COMPLETE:';
337 return;
338
339 end if;
340
341 if (funcmode = 'CANCEL') then
342
343 resultout := 'COMPLETE:';
344 return;
345
346 end if;
347
348 if (funcmode = 'TIMEOUT') then
349
350 resultout := 'COMPLETE:';
351 return;
352
353 end if;
354
355 EXCEPTION
356
357 when others then
358 wf_core.context('WIP_WF_UPDATE_DATE', 'Update Promise Date', itemtype,
359 itemkey, actid, funcmode);
360 raise;
361
362 END update_promise_date;
363
364 PROCEDURE promise_date(c_inputs1 in varchar2 default null,
365 c_inputs2 in varchar2 default null,
366 c_inputs3 in varchar2 default null,
367 c_inputs4 in varchar2 default null,
368 c_inputs5 in varchar2 default null,
369 c_inputs6 in varchar2 default null,
370 c_inputs7 in varchar2 default null,
371 c_inputs8 in varchar2 default null,
372 c_inputs9 in varchar2 default null,
373 c_inputs10 in varchar2 default null,
374 c_outputs1 out nocopy varchar2,
375 c_outputs2 out nocopy varchar2,
376 c_outputs3 out nocopy varchar2,
377 c_outputs4 out nocopy varchar2,
378 c_outputs5 out nocopy varchar2,
379 c_outputs6 out nocopy varchar2,
380 c_outputs7 out nocopy varchar2,
381 c_outputs8 out nocopy varchar2,
382 c_outputs9 out nocopy varchar2,
383 c_outputs10 out nocopy varchar2) IS
384 x_po_line_location_id number;
385 BEGIN
386
387 return;
388 /*Comment code because iSupplier team obsolete pkg POS_UPD_DATE.SEARCH_PO
389 this procedure is not called from any of WIP code bug2838302
390 also, need to remove the code due to ATG compliance for MOD_PLSQL */
391
392 END promise_date;
393
394 END wip_wf_update_date;