[Home] [Help]
PACKAGE BODY: APPS.M4R_7B5_OSFM_PKG
Source
1 PACKAGE BODY M4R_7B5_OSFM_PKG AS
2 /* $Header: M4R7B5OB.pls 120.6 2006/09/19 12:34:27 bsaratna noship $ */
3
4 g_debug_level NUMBER;
5 g_exception_tracking_msg VARCHAR2(200);
6
7 -- Package
8 -- M4R_7B5_OSFM_PKG
9 --
10 -- Purpose
11 -- This package is called from the 7B5 OSFM WF 'M4R 7B5 OSFM Outbound'.
12 --
13
14 -- Procedure
15 -- SET_WF_ATTRIBUTES
16
17 -- Purpose
18 -- This is called from the Workflow 'M4R 7B5 OSFM Outbound'.
19 -- It checks whether the approved PO has any Outside Processing Items.If found,
20 -- sets the WF Item Attributes.
21
22 -- Arguments
23
24 -- Notes
25 -- None
26
27 PROCEDURE SET_WF_ATTRIBUTES(p_itemtype IN VARCHAR2,
28 p_itemkey IN VARCHAR2,
29 p_actid IN NUMBER,
30 p_funcmode IN VARCHAR2,
31 x_resultout IN OUT NOCOPY VARCHAR2) IS
32
33 l_po_header_id NUMBER;
34 l_po_doc_id NUMBER;
35 l_po_rev_id NUMBER;
36 l_po_rel_id NUMBER;
37 l_po_doc_type VARCHAR2(30);
38 l_po_rev_num NUMBER;
39 l_po_rel_num NUMBER;
40 l_po_rel_rev_num NUMBER;
41 l_osp_item_exists VARCHAR2(6);
42 l_party_site_id NUMBER;
43 l_org_id NUMBER;
44 l_party_id NUMBER;
45 l_party_type VARCHAR2(30);
46 l_error_code NUMBER;
47 l_errmsg VARCHAR2(2000);
48 l_gen_wf_param wf_parameter_list_t;
49
50 BEGIN
51
52 IF (g_debug_level <= 2) THEN
53 cln_debug_pub.Add('ENTERING M4R_7B5_OSFM_PKG.SET_WF_ATTRIBUTES procedure with the following parameters:', 2);
54 cln_debug_pub.Add('itemtype:' || p_itemtype, 2);
55 cln_debug_pub.Add('itemkey:' || p_itemkey, 2);
56 cln_debug_pub.Add('actid:' || p_actid, 2);
57 cln_debug_pub.Add('funcmode:' || p_funcmode, 2);
58 cln_debug_pub.Add('resultout:' || x_resultout, 2);
59 END IF;
60
61 l_po_doc_id := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'DOCUMENT_ID');
62 IF (g_debug_level <= 1) THEN
63 cln_debug_pub.Add('PO Document ID ' || l_po_doc_id, 1);
64 END IF;
65
66 l_po_doc_type := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'DOCUMENT_TYPE');
67 IF (g_debug_level <= 1) THEN
68 cln_debug_pub.Add('PO Document Type ' || l_po_doc_type, 1);
69 END IF;
70
71 l_po_rev_num := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PO_REVISION_NUM');
72 IF (g_debug_level <= 1) THEN
73 cln_debug_pub.Add('PO Document Revision Number ' || l_po_rev_num, 1);
74 END IF;
75
76 IF l_po_doc_type = 'RELEASE' THEN ---- Get the PO Release ID if the PO is a Release
77
78 l_po_rel_id := l_po_doc_id;
79 l_po_rel_rev_num := l_po_rev_num;
80
81 g_exception_tracking_msg := 'Query po_releases_all for header_id,release_num';
82
83 SELECT po_header_id,release_num
84 INTO l_po_header_id, l_po_rel_num
85 FROM po_releases_archive_all
86 WHERE po_release_id = l_po_rel_id
87 AND revision_num = l_po_rel_rev_num;
88
89 IF (g_debug_level <= 1) THEN
90 cln_debug_pub.Add('PO Header ID ' || l_po_header_id, 1);
91 cln_debug_pub.Add('PO Release ID ' || l_po_rel_id, 1);
92 cln_debug_pub.Add('PO Release Number ' || l_po_rel_num, 1);
93 cln_debug_pub.Add('Release Revision Number ' || l_po_rel_rev_num, 1);
94 END IF;
95 ELSE
96 l_po_header_id := l_po_doc_id;
97
98 IF (g_debug_level <= 1) THEN
99 cln_debug_pub.Add('PO Header ID' || l_po_header_id, 1);
100 END IF;
101
102 END IF;
103
104 -- Check for OSP items depending upon the PO type.
105
106 IF (l_po_doc_type = 'RELEASE' OR l_po_doc_type = 'STANDARD' OR l_po_doc_type = 'PO') THEN
107
108 g_exception_tracking_msg := 'Query po_lines_all into l_osp_item_exists';
109
110 -- Check if there are any OSP items in the PO.
111 BEGIN
112 SELECT 'YES'
113 INTO l_osp_item_exists
114 FROM po_lines_archive_all
115 WHERE po_header_id = l_po_header_id
116 AND line_type_id IN
117 ( SELECT line_type_id
118 FROM po_line_types
119 WHERE outside_operation_flag ='Y'
120 )
121 AND ROWNUM < 2;
122
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 x_resultout := 'F';
126
127 IF (g_debug_level <= 5) THEN
128 cln_debug_pub.Add('x_resultout = ' || x_resultout, 5);
129 cln_debug_pub.Add('There are NO OSP items in the PO', 5);
130 END IF;
131 END;
132
133 IF l_osp_item_exists = 'YES' THEN
134
135 x_resultout := 'T';
136 -- retrive the other attributes required for workflow
137
138 g_exception_tracking_msg := 'Query po_headers_all for Vendor ID, Vendor Site ID, Org ID';
139
140 IF l_po_doc_type = 'RELEASE' THEN
141 SELECT vendor_id,vendor_site_id,org_id,revision_num
142 INTO l_party_id,l_party_site_id,l_org_id,l_po_rev_num
143 FROM po_headers_archive_all
144 WHERE po_header_id = l_po_header_id
145 AND latest_external_flag = 'Y';
146 ELSE
147 SELECT vendor_id,vendor_site_id,org_id,revision_num
148 INTO l_party_id,l_party_site_id,l_org_id,l_po_rev_num
149 FROM po_headers_archive_all
150 WHERE po_header_id = l_po_header_id
151 AND revision_num = l_po_rev_num;
152 END IF;
153
154 IF (g_debug_level <= 1) THEN
155 cln_debug_pub.Add('x_resultout = ' || x_resultout, 1);
156 cln_debug_pub.Add('There are OSP items in the PO, The WF attributes are set as below.', 1);
157 cln_debug_pub.Add('PARTY ID = ' || l_party_id, 1);
158 cln_debug_pub.Add('PARTY SITE ID = '|| l_party_site_id , 1);
159 cln_debug_pub.Add('PO_HEADER_ID = '|| l_po_header_id , 1);
160 cln_debug_pub.Add('PO_REVISION_NUM = '|| l_po_rev_num , 1);
161 cln_debug_pub.Add('PO_RELEASE_ID = ' || l_po_rel_id, 1);
162 cln_debug_pub.Add('PO_RELEASE_NUM = '|| l_po_rel_num , 1);
163 cln_debug_pub.Add('PO_REL_REV_NUM = '|| l_po_rel_rev_num , 1);
164 END IF;
165
166 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_TRANSACTION_TYPE', 'M4R');
167 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_TRANSACTION_SUBTYPE', '7B5_OSFM_WO');
168 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_ID', l_party_id);
169 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_SITE_ID', l_party_site_id);
170 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ECX_PARTY_TYPE', 'S');
171 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'ORG_ID', l_org_id);
172 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PO_RELEASE_ID', l_po_rel_id);
173 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PO_HEADER_ID', l_po_header_id);
174 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PO_RELEASE_NUM', l_po_rel_num);
175 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PO_REVISION_NUM', l_po_rev_num);
176 wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'PO_REL_REV_NUM', l_po_rel_rev_num);
177
178 IF (g_debug_level <= 1) THEN
179 cln_debug_pub.Add('Workflow Attributes set', 1);
180 END IF;
181
182 END IF; -- l_osp_item_exists = 'YES'
183
184 ELSE
185 x_resultout := 'F';
186
187 IF (g_debug_level <= 1) THEN
188 cln_debug_pub.Add('The Document Type is either BLANKET OR PLANNED', 1);
189 cln_debug_pub.Add('x_resultout = ' || x_resultout, 1);
190 END IF;
191
192 END IF; -- If doc type is Release, PO or Standard
193
194 IF (g_debug_level <= 2) THEN
195 cln_debug_pub.Add('Exiting the M4R_7B5_OSFM_PKG.SET_WF_ATTRIBUTES procedure', 2);
196 END IF;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 l_error_code := SQLCODE;
201 l_errmsg := SQLERRM;
202 x_resultout := 'ERROR:'||l_error_code||'-'||l_errmsg;
203
204 IF (g_debug_level <= 5) THEN
205 cln_debug_pub.Add('g_exception_tracking_msg : '|| g_exception_tracking_msg,5);
206 cln_debug_pub.Add('Exception - In SET_WF_ATTRIBUTES API', 5);
207 cln_debug_pub.Add('Error is ' || l_error_code || ':' || l_errmsg, 5);
208 END IF;
209
210 END SET_WF_ATTRIBUTES;
211
212
213 -- Procedure
214 -- PROCESS_WO
215
216 -- Purpose
217 -- This procedure is called from the Workflow. It checks whether the OSP Work Order
218 -- request is New/Cancel/Change and raises the Generic Outbound Workflow.
219
220
221 PROCEDURE PROCESS_WO(p_itemtype IN VARCHAR2,
222 p_itemkey IN VARCHAR2,
223 p_actid IN NUMBER,
224 p_funcmode IN VARCHAR2,
225 x_resultout IN OUT NOCOPY VARCHAR2) IS
226
227 l_po_header_id NUMBER;
228 l_po_rel_id NUMBER;
229 l_po_doc_type VARCHAR2(30);
230 l_po_rev_num NUMBER;
231 l_po_rel_rev_num NUMBER;
232 l_po_rev_num_x NUMBER;
233 l_po_rel_num NUMBER;
234 l_error_code NUMBER;
235 l_action_code VARCHAR2(4);
236 l_cancel_flag VARCHAR2(2);
237 l_doc_id NUMBER;
238 l_rout_seq_num NUMBER;
239 l_job_num VARCHAR2(100);
240 l_doc_num VARCHAR2(100);
241 l_wip_entity_id NUMBER;
242 l_party_id NUMBER;
243 l_party_site_id NUMBER;
244 l_org_id NUMBER;
245 l_osfm_org_id NUMBER;
246 l_line_loc_id NUMBER;
247 l_line_loc_rev_num NUMBER;
248 l_line_rev_num NUMBER;
249 l_dist_quant_ord NUMBER;
250
251 l_creation_date VARCHAR2(40);
252 l_rev_date VARCHAR2(40);
253 l_party_type VARCHAR2(3);
254 l_event_key VARCHAR2(100);
255 l_assembly_name VARCHAR2(200);
256 l_op_desc VARCHAR2(2000);
257 l_errmsg VARCHAR2(2000);
258 l_raise_flag VARCHAR2(2);
259 l_header_change VARCHAR2(2);
260 l_lines_chk NUMBER;
261 l_lines_loc_chk VARCHAR2(2);
262 l_dist_chk VARCHAR2(2);
263 l_all_new_flag VARCHAR2(2);
264 l_seq_num NUMBER;
265 l_cn_create_date VARCHAR2(50);
266 l_cn_rev_date VARCHAR2(50);
267 x_rn_datetime VARCHAR2(50);
268 l_item VARCHAR2(500);
269 l_item_rv VARCHAR2(500);
270 l_uom VARCHAR2(50);
271
272 l_all_cancel_flag VARCHAR2(2);
273 l_line_cancel NUMBER;
274 l_this_line_cancel VARCHAR2(2);
275 l_this_line_loc_cancel VARCHAR2(2);
276 l_this_line_changed VARCHAR2(2);
277 l_this_line_loc_changed VARCHAR2(2);
278 l_this_line_dist_changed VARCHAR2(2);
279 x_header_change_flag VARCHAR2(2);
280 l_this_line_exists_flag VARCHAR2(2);
281 l_this_line_exists_chk VARCHAR2(2);
282 l_gen_wf_param wf_parameter_list_t;
283
284 CURSOR M4R_7B5_OSFM_C1(l_po_header_id NUMBER, l_po_rel_id NUMBER)
285 IS
286 SELECT l.po_header_id,ll.po_line_id,ll.line_location_id,d.PO_DISTRIBUTION_ID
287 FROM po_lines_all l, po_line_locations_all ll, po_distributions_all d
288 WHERE l.po_header_id = l_po_header_id
289 AND l.line_type_id IN (
290 SELECT line_type_id
291 FROM po_line_types
292 WHERE outside_operation_flag ='Y'
293 )
294 AND ll.po_line_id = l.po_line_id
295 AND (ll.po_release_id = l_po_rel_id OR ll.po_release_id IS NULL)
296 AND d.line_location_id = ll.line_location_id;
297
298
299 BEGIN
300
301 IF (g_debug_level <= 2) THEN
302 cln_debug_pub.Add('ENTERING M4R_7B5_OSFM_PKG.PROCESS_WO procedure with the following parameters:', 2);
303 cln_debug_pub.Add('itemtype:' || p_itemtype, 2);
304 cln_debug_pub.Add('itemkey:' || p_itemkey, 2);
305 cln_debug_pub.Add('actid:' || p_actid, 2);
306 cln_debug_pub.Add('funcmode:' || p_funcmode, 2);
307 cln_debug_pub.Add('resultout:' || x_resultout, 2);
308 END IF;
309
310
311 -- read wf item attributes into local variable, begins
312 l_po_header_id := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PO_HEADER_ID');
313 IF (g_debug_level <= 1) THEN
314 cln_debug_pub.Add('PO Header ID ' || l_po_header_id, 1);
315 END IF;
316
317 l_po_rel_id := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PO_RELEASE_ID');
318 IF (g_debug_level <= 1) THEN
319 cln_debug_pub.Add('PO Release ID ' || l_po_rel_id, 1);
320 END IF;
321
322 l_po_doc_type := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'DOCUMENT_TYPE');
323 IF (g_debug_level <= 1) THEN
324 cln_debug_pub.Add('PO Document Type ' || l_po_doc_type, 1);
325 END IF;
326
327 l_po_rev_num := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PO_REVISION_NUM');
328 IF (g_debug_level <= 1) THEN
329 cln_debug_pub.Add('PO Revision Number ' || l_po_rev_num, 1);
330 END IF;
331
332 l_po_rel_num := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PO_RELEASE_NUM');
333 IF (g_debug_level <= 1) THEN
334 cln_debug_pub.Add('PO Release Number ' || l_po_rel_num, 1);
335 END IF;
336
337 l_po_rel_rev_num := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'PO_REL_REV_NUM');
338 IF (g_debug_level <= 1) THEN
339 cln_debug_pub.Add('PO Release Revision Number ' || l_po_rel_rev_num, 1);
340 END IF;
341
342 l_org_id := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'ORG_ID');
343 IF (g_debug_level <= 1) THEN
344 cln_debug_pub.Add('Org ID ' || l_org_id, 1);
345 END IF;
346
347 l_party_id := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'ECX_PARTY_ID');
348 IF (g_debug_level <= 1) THEN
349 cln_debug_pub.Add('Party ID ' || l_party_id, 1);
350 END IF;
351
352 l_party_site_id := wf_engine.GetActivityAttrText(p_itemtype,p_itemkey,p_actid,'ECX_PARTY_SITE_ID');
353 IF (g_debug_level <= 1) THEN
354 cln_debug_pub.Add('Party Site ID ' || l_party_site_id, 1);
355 END IF;
356 -- read wf item attributes into local variable, ends
357
358 -- query date attributes and cancel flags for the po/release
359 -- Check if this is
360 -- A. New PO
361 -- B. New Release
362 -- C. Cancelled PO
363 -- D. Cancelled Release
364
365 -- Defaulting
366 l_all_cancel_flag := 'N';
367 l_all_new_flag := 'N';
368
369 IF l_po_doc_type ='RELEASE' THEN
370
371 g_exception_tracking_msg := 'Query po_releases_all for dates';
372
373 SELECT revised_date,creation_date
374 INTO l_rev_date,l_creation_date
375 FROM po_releases_archive_all
376 WHERE po_release_id = l_po_rel_id
377 AND revision_num = l_po_rel_rev_num;
378
379 IF (g_debug_level <= 1) THEN
380 cln_debug_pub.Add('From po_releases_all Table',1);
381 cln_debug_pub.Add('creation_date - ' ||l_creation_date,1);
382 cln_debug_pub.Add('revised_date - ' ||l_rev_date,1);
383 cln_debug_pub.Add('cancel_flag - ' ||l_cancel_flag,1);
384 END IF;
385
386 g_exception_tracking_msg := 'Query po_releases_all for cance_flag';
387
388 -- Query for Cancel flag
389 SELECT cancel_flag
390 INTO l_cancel_flag
391 FROM po_releases_all
392 WHERE po_release_id = l_po_rel_id;
393
394 IF (g_debug_level <= 1) THEN
395 cln_debug_pub.Add('cancel_flag - '||l_cancel_flag,1);
396 END IF;
397
398 ELSE
399
400 g_exception_tracking_msg := 'Query po_headers_all for dates, cancel flag';
401
402 SELECT creation_date,revised_date,cancel_flag
403 INTO l_creation_date,l_rev_date,l_cancel_flag
404 FROM po_headers_archive_all
405 WHERE po_header_id = l_po_header_id
406 AND ((revision_num = l_po_rev_num) OR (revision_num IS NULL));
407
408 IF (g_debug_level <= 1) THEN
409 cln_debug_pub.Add('From po_headers_all Table',1);
410 cln_debug_pub.Add('creation_date - ' ||l_creation_date,1);
411 cln_debug_pub.Add('revised_date - ' ||l_rev_date,1);
412 cln_debug_pub.Add('cancel_flag - ' ||l_cancel_flag,1);
413 END IF;
414
415 END IF;
416 -- query date attributes and cancel flags for the po/release ends here
417
418
419 -- set flags corresponding to new/cancel po release
420 IF (l_po_doc_type ='RELEASE' and l_po_rel_rev_num = 0) OR
421 ((l_po_doc_type ='STANDARD' OR l_po_doc_type = 'PO') AND l_po_rev_num = 0) THEN
422
423
424 l_all_new_flag := 'Y';
425 l_raise_flag := 'Y';
426 l_action_code := 'WOR';
427 -- Check if it is cancelled PO or release
428 ELSE
429 IF l_cancel_flag = 'Y' THEN
430
431 l_all_cancel_flag := 'Y';
432 l_action_code := 'WON';
433 l_raise_flag := 'Y';
434
435 IF (g_debug_level <= 1) THEN
436 cln_debug_pub.Add('Cancel Flag is Y. Document Type is Standard or PO and Action Code is WON', 1);
437 END IF;
438 END IF;
439 END IF;
440 -- Finished checking if it is new PO/Release or Cancelled PO/release
441
442 IF (g_debug_level <= 1) THEN
443 cln_debug_pub.Add('Raise Flag: ' || l_raise_flag, 1);
444 cln_debug_pub.Add('l_all_cancel_flag: ' || l_all_cancel_flag, 1);
445 cln_debug_pub.Add('l_all_new_flag: ' || l_all_new_flag, 1);
446 cln_debug_pub.Add('l_action_code: ' || l_action_code, 1);
447 END IF;
448
449 -- Check for header change
450 -- IF it is not a new or cancelled release
451 -- then compare headers, this is to determine if the new PO revision is due to change
452 -- in the header or change in the line.
453 -- if it is due to a change in the header, then we need to 7B5 for everyline
454 -- else, we can send only the modified lines
455 IF l_all_new_flag <> 'Y' and l_all_cancel_flag <> 'Y' THEN -- not a new or cancelled order
456
457 IF l_po_doc_type = 'RELEASE' THEN
458 l_po_rev_num_x := l_po_rel_rev_num;
459 ELSE
460 l_po_rev_num_x := l_po_rev_num;
461 END IF;
462
463 IF (g_debug_level <= 1) THEN
464 cln_debug_pub.Add('l_po_rev_num_x - ' ||l_po_rev_num_x,1);
465 END IF;
466
467 compare_headers(l_po_header_id,l_po_rel_id,l_po_rev_num_x,x_header_change_flag);
468
469 IF (g_debug_level <= 1) THEN
470 cln_debug_pub.Add('x_header_change_flag - ' ||x_header_change_flag,1);
471 END IF;
472
473 END IF;
474 -- Check for header change
475 -- If it is not a new or cancelled order
476
477
478 -- Loop through every distribution for the PO
479 -- If it is not a new/cancel/header level change
480 -- check it is a cancel change
481 FOR lines_rec IN M4R_7B5_OSFM_C1(l_po_header_id,l_po_rel_id) LOOP
482
483 l_this_line_loc_cancel := 'N';
484 l_this_line_changed := 'N';
485 l_lines_loc_chk := NULL;
486 l_lines_chk := NULL;
487
488 IF (g_debug_level <= 1) THEN
489 cln_debug_pub.Add('Inside Cursor for',1);
490 cln_debug_pub.Add('Line ID - ' || lines_rec.po_line_id,1);
491 cln_debug_pub.Add('Line Location ID - '|| lines_rec.line_location_id,1);
492 cln_debug_pub.Add('Distribution ID - ' || lines_rec.po_distribution_id,1);
493 END IF;
494
495 IF l_all_new_flag = 'Y' THEN
496 l_raise_flag := 'Y';
497 l_action_code := 'WOR';
498 ELSIF l_all_cancel_flag = 'Y' THEN
499 l_raise_flag := 'Y';
500 l_action_code := 'WON';
501 ELSE
502
503 g_exception_tracking_msg := 'Query po_line_locations_archive_all into l_lines_loc_chk';
504
505 BEGIN -- Checks if the Shipments got cancelled
506
507 SELECT 'x'
508 INTO l_lines_loc_chk
509 FROM po_line_locations_archive_all
510 WHERE po_header_id = l_po_header_id
511 AND revision_num = l_po_rev_num_x
512 AND po_line_id = lines_rec.po_line_id
513 AND line_location_id = lines_rec.line_location_id
514 AND cancel_flag = 'Y';
515
516 IF l_lines_loc_chk ='x' THEN
517 l_this_line_loc_cancel := 'Y';
518 END IF;
519
520 IF (g_debug_level <= 1) THEN
521 cln_debug_pub.Add('l_this_line_loc_cancel - '||l_this_line_loc_cancel,1);
522 END IF;
523 EXCEPTION
524 WHEN NO_DATA_FOUND THEN
525 l_this_line_loc_cancel := 'N';
526
527 IF (g_debug_level <= 5) THEN
528 cln_debug_pub.Add('No Rows Found',5);
529 cln_debug_pub.Add('l_this_line_loc_cancel - '||l_this_line_loc_cancel,5);
530 END IF;
531 END;
532
533 IF (l_this_line_loc_cancel <> 'Y') THEN
534
535 g_exception_tracking_msg := 'Query PO archive tables to check whether line have changes';
536
537 BEGIN -- Checks if the line have changes
538
539 SELECT COUNT(*)
540 INTO l_lines_chk
541 FROM dual
542 WHERE EXISTS ( (
543 SELECT po_header_id
544 FROM po_lines_archive_all
545 WHERE po_header_id = l_po_header_id
546 AND po_line_id = lines_rec.po_line_id
547 AND revision_num = l_po_rev_num_x
548 )
549 UNION
550 (
551 SELECT po_header_id
552 FROM po_line_locations_archive_all
553 WHERE po_header_id = l_po_header_id
554 AND revision_num = l_po_rev_num_x
555 AND po_line_id = lines_rec.po_line_id
556 AND line_location_id = lines_rec.line_location_id
557 )
558 UNION
559 (
560 SELECT po_header_id
561 FROM po_distributions_archive_all
562 WHERE po_header_id = l_po_header_id
563 AND revision_num = l_po_rev_num_x
564 AND po_line_id = lines_rec.po_line_id
565 AND po_distribution_id = lines_rec.po_distribution_id
566 )
567 );
568
569 IF l_lines_chk > 0 THEN
570 l_this_line_changed := 'Y';
571 END IF;
572
573 IF (g_debug_level <= 1) THEN
574 cln_debug_pub.Add('l_lines_chk - ' ||l_lines_chk,1);
575 cln_debug_pub.Add('l_this_line_changed - ' ||l_this_line_changed,1);
576 END IF;
577
578 EXCEPTION
579 WHEN NO_DATA_FOUND THEN
580 l_this_line_changed := 'N';
581 IF (g_debug_level <= 5) THEN
582 cln_debug_pub.Add('No Rows Found',5);
583 cln_debug_pub.Add('l_this_line_changed - '||l_this_line_changed,5);
584 END IF;
585 END;
586 END IF;
587
588 IF ((l_this_line_changed ='Y') OR (x_header_change_flag = 'Y')) THEN
589
590 l_raise_flag := 'Y';
591 l_action_code := 'WOC';
592
593 ELSIF l_this_line_loc_cancel = 'Y' THEN -- if shipments cancelled
594
595 l_raise_flag := 'Y';
596 l_action_code := 'WON';
597
598 ELSE -- No header Change and No Cancel/Change in the line
599 l_raise_flag := 'N';
600 END IF;
601
602 END IF;
603
604 IF (g_debug_level <= 1) THEN
605 cln_debug_pub.Add('Before Setting the WF attributes', 1);
606 cln_debug_pub.Add('l_action_code : ' || l_action_code, 1);
607 cln_debug_pub.Add('l_raise_flag : ' || l_raise_flag, 1);
608 END IF;
609
610 IF l_raise_flag = 'Y' THEN --- Set the event parameters for Generic Outbound WF
611
612 g_exception_tracking_msg := 'Query po_lines_archive_all for revision_num';
613
614 -- gets the Line Revision Number
615 SELECT revision_num,vendor_product_num,item_revision,unit_meas_lookup_code
616 INTO l_line_rev_num,l_item,l_item_rv,l_uom
617 FROM po_lines_archive_all
618 WHERE po_header_id = l_po_header_id
619 AND po_line_id = lines_rec.po_line_id
620 AND latest_external_flag = 'Y';
621
622 IF (g_debug_level <= 1) THEN
623 cln_debug_pub.Add('Line Revision Number : ' || l_line_rev_num, 1);
624 END IF;
625
626 g_exception_tracking_msg := 'Query po_line_locations_archive_all for line_location_id,revision_num';
627
628 -- gets the Line Location ID, Line Location Revision Number
629 SELECT line_location_id,revision_num
630 INTO l_line_loc_id,l_line_loc_rev_num
631 FROM po_line_locations_archive_all
632 WHERE po_header_id = l_po_header_id
633 AND po_line_id = lines_rec.po_line_id
634 AND line_location_id = lines_rec.line_location_id
635 AND ((po_release_id = l_po_rel_id) OR (po_release_id IS NULL))
636 AND latest_external_flag = 'Y'
637 AND revision_num = (
638 SELECT MAX(revision_num)
639 FROM po_line_locations_archive_all
640 WHERE po_header_id = l_po_header_id
641 AND po_line_id = lines_rec.po_line_id
642 AND line_location_id = lines_rec.line_location_id
643 AND ((po_release_id = l_po_rel_id) OR (po_release_id IS NULL))
644 );
645
646 IF (g_debug_level <= 1) THEN
647 cln_debug_pub.Add('Line Location ID : ' || l_line_loc_id, 1);
648 cln_debug_pub.Add('Line Location Revision Number : ' || l_line_loc_rev_num, 1);
649 END IF;
650
651 g_exception_tracking_msg := 'Query PO_DISTRIBUTIONS_ALL for WIP parameters';
652
653 -- gets the WIP attributs
654 SELECT WIP_ENTITY_ID,WIP_OPERATION_SEQ_NUM,DESTINATION_ORGANIZATION_ID,(QUANTITY_ORDERED-QUANTITY_CANCELLED)
655 INTO l_wip_entity_id,l_rout_seq_num,l_osfm_org_id,l_dist_quant_ord
656 FROM PO_DISTRIBUTIONS_ALL
657 WHERE po_header_id = l_po_header_id
658 AND po_line_id = lines_rec.po_line_id
659 AND ((po_release_id = l_po_rel_id) OR (po_release_id IS NULL))
660 AND line_location_id = l_line_loc_id
661 AND po_distribution_id = lines_rec.po_distribution_id;
662
663 IF (g_debug_level <= 1) THEN
664 cln_debug_pub.Add('WIP Entity ID : ' || l_wip_entity_id, 1);
665 cln_debug_pub.Add('WIP Routing Seq Number : ' || l_rout_seq_num, 1);
666 cln_debug_pub.Add('OSFM Org ID : ' || l_osfm_org_id, 1);
667 END IF;
668
669 g_exception_tracking_msg := 'Query WSM_WIP_GENEALOGY_V for Assembly parameters';
670
671 -- gets the Job Name, Assembly Name
672 SELECT wip_entity_name,item_number
673 INTO l_job_num,l_assembly_name
674 FROM WSM_WIP_GENEALOGY_V
675 WHERE WIP_ENTITY_ID = l_wip_entity_id
676 AND organization_id = l_osfm_org_id;
677
678 IF (g_debug_level <= 1) THEN
679 cln_debug_pub.Add('WIP Job Number/ Entity Name' || l_job_num, 1);
680 cln_debug_pub.Add('Assembly Name' || l_assembly_name, 1);
681 END IF;
682
683 l_doc_num := l_job_num ||':' || l_rout_seq_num;
684
685 IF (g_debug_level <= 1) THEN
686 cln_debug_pub.Add('Document Number' || l_doc_num, 1);
687 END IF;
688
689 g_exception_tracking_msg := 'Query M4R_7B5_OSFM_S1 into l_doc_id';
690
691 SELECT M4R_7B5_OSFM_S1.NEXTVAL
692 INTO l_doc_id
693 FROM dual;
694
695 l_event_key := '7B5:'|| l_doc_id || to_char(cast(sysdate as timestamp),'DD/MM/YY:HHMMSS');
696
697 IF (g_debug_level <= 1) THEN
698 cln_debug_pub.Add('l_event_key' || l_event_key, 1);
699 END IF;
700
701 g_exception_tracking_msg := 'Query wip_operations for Assembly Description';
702
703 SELECT description
704 INTO l_op_desc
705 FROM wip_operations
706 WHERE wip_entity_id = l_wip_entity_id
707 AND operation_seq_num = l_rout_seq_num;
708
709 IF (g_debug_level <= 1) THEN
710 cln_debug_pub.Add('Operation Desc' || l_op_desc, 1);
711 END IF;
712
713 IF (g_debug_level <= 2) THEN
714 cln_debug_pub.Add('Raising Generic WF with the following parameters', 2);
715 cln_debug_pub.Add('Party Type : ' || l_party_type,2);
716 cln_debug_pub.Add('Party ID : ' || l_party_id,2);
717 cln_debug_pub.Add('Party Site ID : ' || l_party_site_id,2);
718 cln_debug_pub.Add('Org ID : ' || l_org_id,2);
719 cln_debug_pub.Add('Document Number : ' || l_doc_num,2);
720 cln_debug_pub.Add('Document ID : ' || l_doc_id,2);
721 cln_debug_pub.Add('PO Header ID : ' || l_po_header_id,2);
722 cln_debug_pub.Add('PO Release ID : ' || l_po_rel_id,2);
723
724 cln_debug_pub.Add('PO Line ID : ' || lines_rec.po_line_id,2);
725 cln_debug_pub.Add('PO Line Location ID : ' || l_line_loc_id,2);
726 cln_debug_pub.Add('PO Revision Number : ' || l_po_rev_num,2);
727 cln_debug_pub.Add('PO Release Number : ' || l_po_rel_num,2);
728 cln_debug_pub.Add('PO Release Revision Number : ' || l_po_rel_rev_num,2);
729 cln_debug_pub.Add('PO Lines Revision Number : ' || l_line_rev_num,2);
730 cln_debug_pub.Add('Line Locations Revision Number : ' || l_line_loc_rev_num,2);
731 cln_debug_pub.Add('Document Creation Date : ' || l_cn_create_date,2);
732
733 cln_debug_pub.Add('Document Revision Date : ' || l_cn_rev_date,2);
734 cln_debug_pub.Add('Action Code : ' || l_action_code,2);
735 cln_debug_pub.Add('WIP Entity ID : ' || l_wip_entity_id,2);
736 cln_debug_pub.Add('Assembly Name : ' || l_assembly_name,2);
737 cln_debug_pub.Add('Operation Desc : ' || l_op_desc,2);
738 cln_debug_pub.Add('Operation Sequence Number : ' || l_rout_seq_num,2);
739 cln_debug_pub.Add('Reference ID : ' || l_event_key,2);
740 cln_debug_pub.Add('UOM : ' || l_uom,2);
741 END IF;
742
743 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'M4R', l_gen_wf_param);
744 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', '7B5_OSFM_WO', l_gen_wf_param);
745 WF_EVENT.AddParameterToList('ECX_PARTY_TYPE', 'S', l_gen_wf_param);
746 WF_EVENT.AddParameterToList('ECX_PARTY_ID', l_party_id, l_gen_wf_param);
747 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', l_party_site_id, l_gen_wf_param);
748 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', l_event_key, l_gen_wf_param);
749 WF_EVENT.AddParameterToList('ORG_ID', l_org_id, l_gen_wf_param);
750 WF_EVENT.AddParameterToList('DOCUMENT_NO', l_doc_num, l_gen_wf_param);
751 WF_EVENT.AddParameterToList('VALIDATION_REQUIRED_YN', 'N', l_gen_wf_param);
752 WF_EVENT.AddParameterToList('CH_MESSAGE_BEFORE_GENERATE_XML', 'M4R_7B5_OSFM_CH_CREATED', l_gen_wf_param);
753 WF_EVENT.AddParameterToList('CH_MESSAGE_AFTER_XML_SENT', 'M4R_7B5_OSFM_CH_XML_GENERATED', l_gen_wf_param);
754 WF_EVENT.AddParameterToList('ECX_DELIVERY_CHECK_REQUIRED', 'N', l_gen_wf_param);
755 WF_EVENT.AddParameterToList('ECX_PARAMETER1', l_po_header_id, l_gen_wf_param);
756 WF_EVENT.AddParameterToList('ECX_PARAMETER2', l_po_rev_num, l_gen_wf_param);
757 WF_EVENT.AddParameterToList('ECX_PARAMETER3', lines_rec.po_line_id, l_gen_wf_param);
758 WF_EVENT.AddParameterToList('ECX_PARAMETER4', l_action_code, l_gen_wf_param);
759 WF_EVENT.AddParameterToList('ECX_PARAMETER5', l_wip_entity_id, l_gen_wf_param);
760
761 g_exception_tracking_msg := 'FND_DATE.DATE_TO_CANONICAL(l_rev_date)';
762
763 l_cn_rev_date := FND_DATE.DATE_TO_CANONICAL(l_rev_date);
764 WF_EVENT.AddParameterToList('DOCUMENT_REVISION_DATE', l_cn_rev_date, l_gen_wf_param);
765
766 g_exception_tracking_msg := 'FND_DATE.DATE_TO_CANONICAL(l_creation_date)';
767
768 l_cn_create_date := FND_DATE.DATE_TO_CANONICAL(l_creation_date);
769 WF_EVENT.AddParameterToList('DOCUMENT_CREATION_DATE', l_cn_create_date, l_gen_wf_param);
770
771 WF_EVENT.AddParameterToList('COLLABORATION_STATUS_SET', 'Y', l_gen_wf_param);
772 WF_EVENT.AddParameterToList('ATTRIBUTE1', l_po_rel_id, l_gen_wf_param);
773 WF_EVENT.AddParameterToList('ATTRIBUTE2', l_rout_seq_num, l_gen_wf_param);
774 WF_EVENT.AddParameterToList('ATTRIBUTE3', l_line_loc_id, l_gen_wf_param);
775 WF_EVENT.AddParameterToList('ATTRIBUTE5', l_po_rel_num, l_gen_wf_param);
776 WF_EVENT.AddParameterToList('ATTRIBUTE6', l_assembly_name, l_gen_wf_param);
777 WF_EVENT.AddParameterToList('ATTRIBUTE7', l_op_desc, l_gen_wf_param);
778 WF_EVENT.AddParameterToList('ATTRIBUTE8', l_line_loc_rev_num, l_gen_wf_param);
779 WF_EVENT.AddParameterToList('ATTRIBUTE9', l_line_rev_num, l_gen_wf_param);
780 WF_EVENT.AddParameterToList('ATTRIBUTE10', l_dist_quant_ord, l_gen_wf_param);
781 WF_EVENT.AddParameterToList('ATTRIBUTE11', l_item, l_gen_wf_param);
782 WF_EVENT.AddParameterToList('ATTRIBUTE12', l_item_rv, l_gen_wf_param);
783 WF_EVENT.AddParameterToList('ATTRIBUTE13', l_uom, l_gen_wf_param);
784
785 g_exception_tracking_msg := 'CONVERT_TO_RN_DATETIME(l_rev_date,x_rn_datetime)';
786
787 cln_rn_utils.CONVERT_TO_RN_DATETIME(l_rev_date,x_rn_datetime);
788 WF_EVENT.AddParameterToList('DATTRIBUTE1', x_rn_datetime, l_gen_wf_param);
789
790 g_exception_tracking_msg := 'CONVERT_TO_RN_DATETIME(l_creation_date,x_rn_datetime)';
791
792 cln_rn_utils.CONVERT_TO_RN_DATETIME(l_creation_date,x_rn_datetime);
793 WF_EVENT.AddParameterToList('DATTRIBUTE2', x_rn_datetime, l_gen_wf_param);
794
795 IF l_po_doc_type = 'RELEASE' THEN
796 WF_EVENT.AddParameterToList('ATTRIBUTE4', l_po_rel_rev_num, l_gen_wf_param); -- Release Revision Number
797 ELSE
798 WF_EVENT.AddParameterToList('ATTRIBUTE4', l_po_rev_num, l_gen_wf_param); ---- PO Revision Number
799 END IF;
800
801 IF (g_debug_level <= 2) THEN
802 cln_debug_pub.Add('ATTRIBUTE4 : ' || l_po_rev_num || ' Header Revision Number' ,2);
803 END IF;
804
805 WF_EVENT.AddParameterToList('REFERENCE_ID', l_event_key, l_gen_wf_param);
806
807 WF_EVENT.Raise('oracle.apps.cln.common.xml.out',l_event_key, NULL, l_gen_wf_param, NULL);
808
809 IF (g_debug_Level <= 1) THEN
810 cln_debug_pub.Add('---------Generic Workflow Triggered ---------', 1);
811 END IF;
812 END IF; -- For Raise Flag = Y
813 -- reset l_raise_flag before next iteration
814 l_raise_flag := 'N';
815
816 END LOOP;
817
818 x_resultout := wf_engine.eng_completed;
819
820 IF (g_debug_level <= 2) THEN
821 cln_debug_pub.Add('Exiting the M4R_7B5_OSFM_PKG.PROCESS_WO procedure', 2);
822 END IF;
823
824 EXCEPTION
825 WHEN OTHERS THEN
826 l_error_code := SQLCODE;
827 l_errmsg := SQLERRM;
828
829 IF (g_debug_level <= 5) THEN
830 cln_debug_pub.Add('g_exception_tracking_msg : ' || g_exception_tracking_msg,5);
831 cln_debug_pub.Add('Exception in PROCESS_WO proc', 5);
832 cln_debug_pub.Add('Error is ' || l_error_code || ':' || l_errmsg, 5);
833 END IF;
834
835 END PROCESS_WO;
836
837
838 -- Procedure
839 -- COMPARE_HEADERS
840
841 -- Purpose
842 -- This procedure is called from the PROCESS_WO procedure. It checks for chnages in the header of the PO with the
843 -- previous revision of the PO.
844
845 PROCEDURE compare_headers( p_header_id IN NUMBER,
846 p_release_id IN NUMBER,
847 p_revision_num IN NUMBER,
848 x_header_change_flag OUT NOCOPY VARCHAR2) AS
849
850 l_from_ship_to_location_id NUMBER;
851 l_to_ship_to_location_id NUMBER;
852 l_from_bill_to_location_id NUMBER;
853 l_to_bill_to_location_id NUMBER;
854 l_from_terms_id NUMBER;
855 l_to_terms_id NUMBER;
856 l_from_ship_via_lookup_code VARCHAR2(25);
857 l_to_ship_via_lookup_code VARCHAR2(25);
858 l_from_fob_lookup_code VARCHAR2(25);
859 l_to_fob_lookup_code VARCHAR2(25);
860 l_from_vendor_site_id NUMBER;
861 l_to_vendor_site_id NUMBER;
862 l_from_amount_limit NUMBER;
863 l_to_amount_limit NUMBER;
864 l_from_rel_num NUMBER;
865 l_to_rel_num NUMBER;
866 l_from_start_date DATE;
867 l_to_start_date DATE;
868 l_from_end_date DATE;
869 l_to_end_date DATE;
870 l_error_code NUMBER;
871 l_errmsg VARCHAR2(2000);
872
873 BEGIN
874
875 IF (g_debug_level <= 2) THEN
876 cln_debug_pub.Add('Entering M4R_7B5_OSFM_PKG.compare_headers', 2);
877 END IF;
878
879 IF p_revision_num <= 0
880 THEN
881 RETURN;
882 END IF;
883
884 IF p_release_id IS NULL THEN
885
886 g_exception_tracking_msg := 'Query po_headers_archive_all to find Header change between current and previous revision';
887
888 BEGIN
889 SELECT f.ship_to_location_id,t.ship_to_location_id,
890 f.bill_to_location_id,t.bill_to_location_id,
891 f.terms_id,t.terms_id,
892 f.ship_via_lookup_code,t.ship_via_lookup_code,
893 f.fob_lookup_code,t.fob_lookup_code,
894 f.vendor_site_id,t.vendor_site_id,
895 f.amount_limit,t.amount_limit,
896 f.start_date,t.start_date,
897 f.end_date,t.end_date
898 INTO l_from_ship_to_location_id,l_to_ship_to_location_id,
899 l_from_bill_to_location_id,l_to_bill_to_location_id,
900 l_from_terms_id,l_to_terms_id,
901 l_from_ship_via_lookup_code,l_to_ship_via_lookup_code,
902 l_from_fob_lookup_code,l_to_fob_lookup_code,
903 l_from_vendor_site_id,l_to_vendor_site_id,
904 l_from_amount_limit,l_to_amount_limit,
905 l_from_start_date,l_to_start_date,
906 l_from_end_date,l_to_end_date
907 FROM po_headers_archive_all f, po_headers_archive_all t
908 WHERE f.po_header_id = p_header_id
909 AND f.revision_num = p_revision_num
910 AND t.po_header_id = p_header_id
911 AND t.revision_num = p_revision_num-1;
912
913 EXCEPTION
914 WHEN no_data_found THEN
915
916 IF (g_debug_level <= 5) THEN
917 cln_debug_pub.Add('Exception in COMPARE_PO proc - headers query - NO DATA FOUND', 5);
918 END IF;
919 END;
920
921 IF ((NVL( l_from_ship_to_location_id, -99 ) <> NVL( l_to_ship_to_location_id, -99 )) OR
922 (NVL( l_from_bill_to_location_id, -99 ) <> NVL( l_to_bill_to_location_id, -99 )) OR
923 (NVL( l_from_terms_id, -99 ) <> NVL( l_to_terms_id, -99 )) OR
924 (NVL( l_from_ship_via_lookup_code, ' ' ) <> NVL( l_to_ship_via_lookup_code, ' ' ))OR
925 (NVL( l_from_fob_lookup_code, ' ' ) <> NVL( l_to_fob_lookup_code, ' ' )) OR
926 (NVL( l_from_vendor_site_id, -99 ) <> NVL( l_to_vendor_site_id, -99 )) OR
927 (NVL( l_from_amount_limit, -99 ) <> NVL( l_to_amount_limit, -99 )) OR
928 (NVL( l_from_start_date,TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <> NVL( l_to_start_date,TO_DATE( '01/01/1000', 'DD/MM/YYYY' ))) OR
929 (NVL( l_from_end_date,TO_DATE( '01/01/1000', 'DD/MM/YYYY' )) <> NVL( l_to_end_date,TO_DATE( '01/01/1000', 'DD/MM/YYYY' )))
930 ) THEN
931
932 x_header_change_flag := 'Y';
933 ELSE
934 x_header_change_flag := 'N';
935 END IF;
936
937 ELSE
938
939 g_exception_tracking_msg := 'Query po_releases_archive_all to find Header change between current and previous revision';
940
941 BEGIN
942 SELECT f.release_num ,t.release_num
943 INTO l_from_rel_num,l_to_rel_num
944 FROM po_releases_archive_all f,po_releases_archive_all t
945 WHERE f.po_release_id = p_release_id
946 AND f.revision_num = p_revision_num
947 AND t.po_release_id = p_release_id
948 AND t.revision_num = p_revision_num-1;
949
950 EXCEPTION
951 WHEN NO_DATA_FOUND THEN
952 IF (g_debug_level <= 5) THEN
953 cln_debug_pub.Add('Exception in COMPARE_PO proc - releases query - NO DATA FOUND', 5);
954 END IF;
955
956 END;
957
958 IF (NVL(l_from_rel_num,-99) <> NVL(l_to_rel_num, -99 )) THEN
959 x_header_change_flag := 'Y';
960 ELSE
961 x_header_change_flag := 'N';
962 END IF;
963
964 END IF;
965
966 EXCEPTION
967 WHEN OTHERS THEN
968 l_error_code := SQLCODE;
969 l_errmsg := SQLERRM;
970
971 IF (g_debug_level <= 5) THEN
972 cln_debug_pub.Add('g_exception_tracking_msg :' || g_exception_tracking_msg,5);
973 cln_debug_pub.Add('Exception in PROCESS_WO proc', 5);
974 cln_debug_pub.Add('Error is ' || l_error_code || ':' || l_errmsg, 5);
975 END IF;
976
977 END compare_headers;
978
979 BEGIN
980 g_debug_level := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
981
982 END M4R_7B5_OSFM_PKG;