1 PACKAGE BODY PO_CREATE_SR_ASL AS
2 /* $Header: POXWSRAB.pls 120.6.12020000.2 2013/02/10 20:19:22 vegajula ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 --<LOCAL SR/ASL PROJECT 11i11 START>
8 g_PKG_NAME CONSTANT varchar2(30) := 'PO_CREATE_SR_ASL.';
9 g_po_pdoi_write_to_file VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_PDOI_WRITE_TO_FILE'),'N');
10 --<LOCAL SR/ASL PROJECT 11i11 END>
11
12 /*=======================================================================+
13 | FILENAME
14 | POXWSRB.pls
15 |
16 | DESCRIPTION
17 | PL/SQL body for package: PO_CREATE_SR_ASL
18 |
19 | NOTES
20
21 | MODIFIED (MM/DD/YY)
22 |
23 | ksareddy - 24-APR-2002 - Modified calls to ASL and SR to pass in ASR as
24 | additional parameter to identify interface errors generated
25 | by calls from WF
26 *=======================================================================*/
27
28 procedure PROCESS_PO_LINES_FOR_SR_ASL( itemtype in varchar2,
29 itemkey in varchar2,
30 actid in number,
31 funcmode in varchar2,
32 resultout out NOCOPY varchar2 ) IS
33 l_sr_organization_id number;
34 l_process_po varchar2(1);
35 x_progress varchar2(300);
36 l_document_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
37 l_po_line_id PO_LINES_ALL.PO_LINE_ID%TYPE;
38 l_po_line_num PO_LINES_ALL.LINE_NUM%TYPE;
39 l_new_line_num PO_LINES_ALL.LINE_NUM%TYPE;
40 l_vendor_id PO_HEADERS_ALL.VENDOR_ID%TYPE;
41 l_vendor_site_id PO_HEADERS_ALL.VENDOR_SITE_ID%TYPE;
42 l_item_id PO_LINES_ALL.ITEM_ID%TYPE;
43 l_approved_flag varchar2(20);
44 l_start_date DATE;
45 l_end_date DATE;
46 l_interface_header_id NUMBER;
47 l_interface_line_id NUMBER;
48 l_org_assign_change VARCHAR2(1);
49
50 BEGIN
51 x_progress := 'PO_CREATE_SR_ASL:PROCESS_PO_LINES_FOR_SR_ASL: 01';
52 IF (g_po_wf_debug = 'Y') THEN
53 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
54 END IF;
55
56
57 -- Do nothing in cancel or timeout mode
58 --
59 if (funcmode <> wf_engine.eng_run) then
60 resultout := wf_engine.eng_null;
61 return;
62 end if;
63
64 /* Bug# 2846210
65 ** Desc: Setting application context as this wf api will be executed
66 ** after the background engine is run.
67 */
68
69 PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(itemtype, itemkey);
70
71 l_document_id := wf_engine.GetItemAttrNumber ( itemtype, itemkey, 'DOCUMENT_ID');
72
73 l_po_line_num := wf_engine.GetItemAttrNumber ( itemtype, itemkey, 'PO_LINE_NUM');
74
75 l_org_assign_change := PO_WF_UTIL_PKG.GetItemAttrText (itemtype , itemkey,'GA_ORG_ASSIGN_CHANGE'); -- GA FPI
76
77
78 if l_po_line_num is null then
79 l_po_line_num := 0;
80 end if;
81
82
83 get_line_for_process(x_header_id => l_document_id,
84 x_prev_line_num => l_po_line_num,
85 x_line_id => l_po_line_id,
86 x_line_num => l_new_line_num,
87 x_vendor_id => l_vendor_id,
88 x_vendor_site_id => l_vendor_site_id,
89 x_item_id => l_item_id,
90 x_approved_flag => l_approved_flag,
91 x_start_date => l_start_date,
92 x_end_date => l_end_date,
93 x_interface_header_id => l_interface_header_id,
94 x_interface_line_id => l_interface_line_id,
95 x_org_assign_change => l_org_assign_change);
96
97
98 if l_po_line_id is null then
99 l_process_po := 'N';
100 else
101 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'PO_LINE_ID',l_po_line_id);
102 wf_engine.SetItemAttrNumber(itemtype, itemkey,'PO_VENDOR_ID',l_vendor_id);
103 wf_engine.SetItemAttrNumber(itemtype,itemkey,'PO_VENDOR_SITE_ID',l_vendor_site_id);
104 wf_engine.SetItemAttrNumber(itemtype, itemkey,'PO_LINE_ITEM_ID',l_item_id);
105 wf_engine.SetItemAttrText(itemtype , itemkey, 'PO_APPROVED_FLAG',l_approved_flag);
106 wf_engine.SetItemAttrDate(itemtype,itemkey,'PO_START_DATE',l_start_date);
107 wf_engine.SetItemAttrDate(itemtype, itemkey, 'PO_END_DATE',l_end_date);
108 wf_engine.SetItemAttrNumber(itemtype,itemkey,'PO_INTERFACE_HEADER_ID',l_interface_header_id);
109 wf_engine.SetItemAttrNumber(itemtype,itemkey,'PO_INTERFACE_LINE_ID',l_interface_line_id);
110 wf_engine.SetItemAttrNumber(itemtype,itemkey,'PO_LINE_NUM',l_new_line_num);
111 l_process_po := 'Y';
112 end if;
113
114
115 resultout := wf_engine.eng_completed || ':' || l_process_po;
116 x_progress := ': 02. Result= ' || l_process_po;
117 IF (g_po_wf_debug = 'Y') THEN
118 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
119 END IF;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 l_process_po := 'N';
124 x_progress := ': 03. Result= ' || l_process_po;
125 --PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
126 resultout := wf_engine.eng_completed || ':' || l_process_po;
127
128 END PROCESS_PO_LINES_FOR_SR_ASL;
129
130 PROCEDURE GET_LINE_FOR_PROCESS
131 (
132 x_header_id IN NUMBER, -- PO Header ID
133 x_prev_line_num IN NUMBER, -- Line number last processed
134 x_line_id OUT NOCOPY NUMBER, -- PO Line ID
135 x_line_num OUT NOCOPY NUMBER, -- PO Line Num
136 x_vendor_id OUT NOCOPY NUMBER, -- Vendor ID
137 x_vendor_site_id OUT NOCOPY NUMBER, -- Vendor Site ID
138 x_item_id OUT NOCOPY NUMBER, -- Inventory Item ID
139 x_approved_flag OUT NOCOPY VARCHAR2, -- Approval Status
140 x_start_date OUT NOCOPY DATE,
141 x_end_date OUT NOCOPY DATE,
142 x_interface_header_id OUT NOCOPY NUMBER,
143 x_interface_line_id OUT NOCOPY NUMBER,
144 x_org_assign_change IN VARCHAR2) IS
145 --
146 CURSOR get_line_to_be_processed IS
147 SELECT pl.po_line_id,
148 pl.line_num,
149 ph.start_date,
150 ph.end_date,
151 pl.item_id,
152 ph.vendor_id,
153 ph.vendor_site_id,
154 DECODE(ph.approved_flag,'Y','APPROVED',null)
155 FROM po_lines_all pl,
156 po_headers_all ph
157 WHERE pl.po_header_id = ph.po_header_id
158 AND ph.po_header_id = x_header_id
159 AND pl.item_id is not null
160 AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
161 AND nvl(pl.cancel_flag,'N') = 'N'
162 AND NOT EXISTS
163 (SELECT 'Line included in Prior Revision'
164 FROM po_lines_archive pla
165 WHERE pla.po_line_id = pl.po_line_id
166 AND pla.revision_num < ph.revision_num)
167 AND pl.line_num > x_prev_line_num
168 ORDER BY ph.po_header_id, pl.line_num;
169 --
170 /* GA FPI Start : new cursor for GA lines with org change */
171 CURSOR get_line_processed_for_gaorgs IS
172 SELECT pl.po_line_id,
173 pl.line_num,
174 ph.start_date,
175 ph.end_date,
176 pl.item_id,
177 ph.vendor_id,
178 ph.vendor_site_id,
179 DECODE(ph.approved_flag,'Y','APPROVED',null)
180 FROM po_lines_all pl,
181 po_headers_all ph
182 WHERE pl.po_header_id = ph.po_header_id
183 AND ph.po_header_id = x_header_id
184 AND pl.item_id is not null
185 AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
186 AND nvl(pl.cancel_flag,'N') = 'N'
187 AND pl.line_num > x_prev_line_num
188 ORDER BY ph.po_header_id, pl.line_num;
189 /* GA FPI End */
190
191 BEGIN
192 --
193
194 if (x_prev_line_num = 0) then
195
196 /* bug 2363681 : Instead of passing a random value from a sequence as the interface
197 header and line id we pass the po header and line id so that it is easy to
198 trouble shoot */
199
200 -- SELECT po_headers_interface_s.nextval INTO x_interface_header_id FROM dual;
201 x_interface_header_id := x_header_id;
202 end if;
203
204 /* GA FPI Start : If the re approval is because of an org assignment change
205 Then open the cursor with all lines
206 */
207
208 IF x_org_assign_change = 'Y' THEN
209
210 OPEN get_line_processed_for_gaorgs;
211 FETCH get_line_processed_for_gaorgs
212 INTO x_line_id, x_line_num, x_start_date, x_end_date,
213 x_item_id, x_vendor_id, x_vendor_site_id, x_approved_flag;
214
215 CLOSE get_line_processed_for_gaorgs;
216 /* GA FPI End */
217 ELSE
218
219 OPEN get_line_to_be_processed;
220 FETCH get_line_to_be_processed
221 INTO x_line_id, x_line_num, x_start_date, x_end_date,
222 x_item_id, x_vendor_id, x_vendor_site_id, x_approved_flag;
223
224 CLOSE get_line_to_be_processed;
225
226 END IF;
227
228 x_interface_line_id := x_line_id;
229
230 --
231 EXCEPTION
232 WHEN OTHERS THEN
233 x_line_id := NULL;
234 --
235 END GET_LINE_FOR_PROCESS;
236
237
238 procedure Create_ASL (itemtype in varchar2,
239 itemkey in varchar2,
240 actid in number,
241 funcmode in varchar2,
242 resultout out NOCOPY varchar2 ) is
243 l_create_asl varchar2(2);
244 x_progress varchar2(300);
245 l_interface_header_id NUMBER;
246 l_interface_line_id NUMBER;
247 l_item_id PO_LINES_ALL.ITEM_ID%TYPE;
248 l_category_id NUMBER;
249 l_document_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
250 l_po_line_id PO_LINES_ALL.PO_LINE_ID%TYPE := null;
251 l_document_type varchar2(25);
252 l_rel_gen_method varchar2(20);
253 l_sr_organization_id NUMBER;
254 l_header_processable_flag varchar2(1);
255 l_vendor_site_id number;
256 l_ga_flag varchar2(1);
257
258 CURSOR c_enabled_sites(v_doc_id number) is
259 select vendor_site_id
260 from po_ga_org_assignments
261 where po_header_id = v_doc_id
262 and enabled_flag = 'Y';
263
264 BEGIN
265
266 x_progress := 'PO_CREATE_SR_ASL:Create_ASL: 01';
267 IF (g_po_wf_debug = 'Y') THEN
268 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
269 END IF;
270
271 l_interface_header_id := wf_engine.GetItemAttrNumber (itemtype, itemkey,'PO_INTERFACE_HEADER_ID');
272
273 l_interface_line_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'PO_INTERFACE_LINE_ID');
274
275 l_item_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'PO_LINE_ITEM_ID');
276
277 l_document_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'DOCUMENT_ID');
278 l_po_line_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'PO_LINE_ID');
279
280 l_document_type := wf_engine.GetItemAttrText ( itemtype, itemkey, 'DOCUMENT_TYPE');
281
282 l_rel_gen_method := wf_engine.GetItemAttrText ( itemtype, itemkey, 'RELEASE_GENERATION_METHOD');
283
284 l_sr_organization_id := Wf_Engine.GetActivityAttrNumber(itemtype, itemkey, actid, 'PO_SR_ORGANIZATION_ID');
285
286 l_category_id := Wf_Engine.GetActivityAttrNumber(itemtype, itemkey, actid, 'PO_ASL_CATEGORY_ID');
287
288
289 /* GA FPI start */
290 /* Check if the document is a global agreement */
291 IF l_document_id is not null then
292
293 select global_agreement_flag,vendor_site_id
294 into l_ga_flag , l_vendor_site_id
295 from po_headers_all
296 where po_header_id = l_document_id;
297
298 END IF;
299
300 IF nvl(l_ga_flag,'N') = 'Y' THEN
301 open c_enabled_sites(l_document_id);
302
303 LOOP
304 fetch c_enabled_sites into l_vendor_site_id;
305 exit when c_enabled_sites%NOTFOUND;
306
307 PO_APPROVED_SUPPLIER_LIST_SV.create_po_asl_entries(l_interface_header_id,
308 l_interface_line_id,
309 l_item_id,
310 l_category_id,
311 l_document_id,
312 l_po_line_id,
313 l_document_type,
314 l_vendor_site_id,
315 l_rel_gen_method, -- bug 2697181
316 l_sr_organization_id,
317 l_header_processable_flag,
318 'ASR');
319
320 END LOOP;
321
322 close c_enabled_sites;
323
324 ELSE -- If not a global agreement you do not need to loop
325
326 PO_APPROVED_SUPPLIER_LIST_SV.create_po_asl_entries(l_interface_header_id,
327 l_interface_line_id,
328 l_item_id,
329 l_category_id,
330 l_document_id,
331 l_po_line_id,
332 l_document_type,
333 l_vendor_site_id,
334 l_rel_gen_method,
335 l_sr_organization_id,
336 l_header_processable_flag,
337 'ASR');
338 END IF;
339
340 /* GA FPI end */
341
342 l_create_asl := 'Y';
343 x_progress := ': 02. Result= ' || l_create_asl;
344 IF (g_po_wf_debug = 'Y') THEN
345 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
346 END IF;
347 resultout := wf_engine.eng_completed || ':' || l_create_asl;
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 l_create_asl := 'Y';
352 x_progress := ': 03. Result= ' || l_create_asl;
353 IF (g_po_wf_debug = 'Y') THEN
354 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
355 END IF;
356 resultout := wf_engine.eng_completed || ':' || l_create_asl;
357 END Create_ASL;
358
359
360 procedure Create_Sourcing_Rule(itemtype in varchar2,
361 itemkey in varchar2,
362 actid in number,
363 funcmode in varchar2,
364 resultout out NOCOPY varchar2 ) is
365
366 l_create_sr VARCHAR2(2);
367 x_progress VARCHAR2(300);
368 l_interface_header_id NUMBER;
369 l_interface_line_id NUMBER;
370 l_item_id PO_LINES_ALL.ITEM_ID%TYPE;
371 l_vendor_id PO_HEADERS_ALL.VENDOR_ID%TYPE;
372 l_document_id PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
373 l_po_line_id PO_LINES_ALL.PO_LINE_ID%TYPE := null;
374 l_document_type VARCHAR2(25);
375 l_approved_flag VARCHAR2(20);
376 l_rule_name VARCHAR2(50);
377 l_rule_prefix VARCHAR2(20);
378 l_start_date DATE;
379 l_end_date DATE;
380 l_create_update_flag VARCHAR2(20);
381 l_sr_organization_id NUMBER;
382 l_assignment_type_id NUMBER;
383 l_header_processable_flag varchar2(1);
384 l_create_sourcing_rule_flag varchar2(1);
385 l_update_sourcing_rule_flag varchar2(1);
386 l_return_status varchar2(1); --<Shared Proc FPJ>
387
388 BEGIN
389 x_progress := 'PO_CREATE_SR_ASL:Create_Sourcing_Rule: 01';
390 IF (g_po_wf_debug = 'Y') THEN
391 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
392 END IF;
393
394 l_interface_header_id := wf_engine.GetItemAttrNumber (itemtype, itemkey,'PO_INTERFACE_HEADER_ID');
395
396 l_interface_line_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'PO_INTERFACE_LINE_ID');
397
398 l_item_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'PO_LINE_ITEM_ID');
399
400 l_vendor_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'PO_VENDOR_ID');
401
402 l_document_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'DOCUMENT_ID');
403
404 l_po_line_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'PO_LINE_ID');
405
406 l_document_type := wf_engine.GetItemAttrText ( itemtype, itemkey, 'DOCUMENT_TYPE');
407
408 l_approved_flag := wf_engine.GetItemAttrText (itemtype, itemkey, 'PO_APPROVED_FLAG');
409
410 l_start_date := wf_engine.GetItemAttrDate(itemtype, itemkey, 'PO_START_DATE');
411
412 l_end_date := wf_engine.GetItemAttrDate(itemtype, itemkey, 'PO_END_DATE');
413
414 l_sr_organization_id := Wf_Engine.GetActivityAttrNumber(itemtype, itemkey, actid, 'PO_SR_ORGANIZATION_ID');
415
416 l_assignment_type_id := Wf_Engine.GetActivityAttrNumber(itemtype, itemkey, actid, 'PO_SR_ASSIGNMENT_TYPE_ID');
417
418 l_rule_prefix := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'PO_SR_RULENAME_PREFIX');
419
420 l_create_sourcing_rule_flag := wf_engine.GetItemAttrText(itemtype, itemkey, 'CREATE_SOURCING_RULE');
421
422 l_update_sourcing_rule_flag := wf_engine.GetItemAttrText(itemtype, itemkey, 'UPDATE_SOURCING_RULE');
423
424 if l_create_sourcing_rule_flag = 'Y' then
425 l_create_update_flag := 'CREATE';
426 end if;
427
428 if l_update_sourcing_rule_flag = 'Y' then
429 l_create_update_flag := 'CREATE_UPDATE';
430 end if;
431
432 --<Shared Proc FPJ START>
433 l_header_processable_flag := 'Y';
434 --Changed the call to conform to the new signature of API
435 PO_SOURCING_RULES_SV.create_update_sourcing_rule(
436 p_interface_header_id => l_interface_header_id,
437 p_interface_line_id => l_interface_line_id,
438 p_item_id => l_item_id,
439 p_vendor_id => l_vendor_id,
440 p_po_header_id => l_document_id,
441 p_po_line_id => l_po_line_id,
442 p_document_type => l_document_type,
443 p_approval_status => l_approved_flag,
444 p_rule_name =>l_rule_name,
445 p_rule_name_prefix =>l_rule_prefix,
446 p_start_date =>l_start_date,
447 p_end_date =>l_end_date,
448 p_create_update_code => l_create_update_flag,
449 p_organization_id => l_sr_organization_id,
450 p_assignment_type_id => l_assignment_type_id,
451 p_po_interface_error_code =>'ASR',
452 x_header_processable_flag => l_header_processable_flag,
453 x_return_status =>l_return_status);
454
455 --We are hard coding the return status to 'Y'. This is to ensure that
456 --ASL creation goes through even if Sourcing Rule creation failed
457 l_create_sr := 'Y';
458
459 x_progress := ': 02. Result= ' || l_create_sr;
460 IF (g_po_wf_debug = 'Y') THEN
461 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
462 END IF;
463 resultout := wf_engine.eng_completed || ':' || l_create_sr;
464
465 EXCEPTION
466 WHEN OTHERS THEN
467 l_create_sr := 'Y';
468 x_progress := ': 03. Result= ' || l_create_sr;
469 IF (g_po_wf_debug = 'Y') THEN
470 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
471 END IF;
472 resultout := wf_engine.eng_completed || ':' || l_create_sr;
473 END Create_Sourcing_Rule;
474
475
476 --<LOCAL SR/ASL PROJECT 11i11 START>
477 -------------------------------------------------------------------------------
478 --Start of Comments
479 --Name: create_autosource_rules
480 --Pre-reqs:
481 -- ARCHIVAL option should be APPROVE in document types for BLANKET AGREEMENTS
482 --Modifies:
483 -- n/a
484 --Locks:
485 -- n/a
486 --Function:
487 -- This procedure is responsible for doing the basic validations required for
488 -- the "Document Sourcing Rules Creation Process". Subsequent to this the procedure
489 -- PO_CREATE_SOURCING_RULES.create_sourcing_rule_asl would be called, which
490 -- would create the ASL and Sourcing Rule for each Agrement line.
491 --Parameters:
492 --IN:
493 --p_api_version
494 -- Standard parameter for verifying the api version
495 --p_init_msg_list
496 -- The p_init_msg_list parameter allows API callers to request
497 -- that the API does the initialization of the message list on
498 -- their behalf.
499 --p_commit
500 -- Standard parameter which dictates whether or not data should
501 -- be commited in the api
502 --p_validation_level
503 -- The p_validation_level parameter to determine which validation
504 -- steps should be executed and which steps should be skipped
505 --P_purchasing_org_id
506 -- Specifies the purchasing org in which the ASL/SR would have to be created.
507 --p_vendor_site_id
508 -- Specifies the Supplier Site Id Enabled corresponding to the owining
509 -- org/Purchasing Org
510 --p_create_sourcing_rule
511 -- This would have to be 'Y' by default. Infact we can hardcode this
512 -- and need not have this as a parameter.
513 --p_update_sourcing_rule
514 -- This would have to be 'Y' by default. Infact we can hardcode this
515 -- and need not have this as a parameter.
516 --p_agreement_lines_selection
517 -- This parameter specifies whether the sourcing rules would be created
518 -- for all the lines or for just the new lines. Possible values for
519 -- this parameter are 'ALL' 'NEW'
520 --p_sourcing_level
521 -- This parameter specifies if the Sourcing Rule should be a Global/Local
522 -- Sourcing Rule and if the assignment should be Item or Item Organization.
523 --p_inv_org
524 -- Specifies the Inventory Org for which the sourcing rule needs to be created.
525 --p_sourcing_rule_name
526 -- Specifies the user defined sourcing rule name.
527 --p_assigment_set_id
528 -- Specifies the assignment set to which the created sourcing rule
529 -- should be assigned.
530 --p_release_gen_method
531 -- This specifies what the release generation method would be :
532 -- CREATE, CREATE_AND_APPROVE, NONE
533 --OUT:
534 --x_return_status
535 -- This indicates whether or not the api call was successful.
536 -- Returns 'S' or 'E' for success or error respectively.
537 --x_msg_count
538 -- Holds the count for the number of messages in the message list.
539 --x_msg_data
540 -- If only one error is encountered message data holds the error message
541
542 --Testing:
543 --
544 --
545 --End of Comments
546 -------------------------------------------------------------------------------
547
548 PROCEDURE create_autosource_rules(
549 p_api_version IN NUMBER,
550 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE ,
551 p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
552 x_return_status OUT NOCOPY VARCHAR2,
553 x_msg_count OUT NOCOPY NUMBER,
554 x_msg_data OUT NOCOPY VARCHAR2,
555 p_document_id IN PO_HEADERS_ALL.po_header_id%type,
556 p_vendor_id IN PO_HEADERS_ALL.vendor_id%type,
557 p_purchasing_org_id IN PO_HEADERS_ALL.org_id%type,
558 p_vendor_site_id IN PO_HEADERS_ALL.vendor_site_id%type,
559 p_create_sourcing_rule IN VARCHAR2,
560 p_update_sourcing_rule IN VARCHAR2,
561 p_agreement_lines_selection IN VARCHAR2,
562 p_sourcing_level IN VARCHAR2,
563 p_inv_org IN HR_ALL_ORGANIZATION_UNITS.organization_id%type,
564 p_sourcing_rule_name IN VARCHAR2,
565 p_release_gen_method IN PO_ASL_ATTRIBUTES.release_generation_method%type,
566 p_assignment_set_id IN MRP_ASSIGNMENT_SETS.assignment_set_id%type) IS
567
568 /* Local variables declared for Concurrent Program specific requirements */
569 l_archive_on_approve PO_DOCUMENT_TYPES_ALL.archive_external_revision_code%type;
570 l_previous_line_num PO_LINES_ALL.line_num%type;
571 l_orgid PO_HEADERS_ALL.org_id%type;
572
573 /* Local variables Package specific information... Required for logging */
574 l_procedure_name varchar2(50):='create_autosource_rules : ';
575 l_progress VARCHAR2(2000);
576
577 /* Parameters Required for calling the new procedure create_sourcing_rules_asl */
578 l_interface_header_id NUMBER;
579 l_interface_line_id NUMBER;
580 l_document_type_code VARCHAR2(10):= 'PA';
581 l_document_subtype VARCHAR2(10):= 'BLANKET';
582 l_item_id PO_LINES_ALL.ITEM_ID%TYPE;
583 l_category_id NUMBER;
584 l_po_line_id PO_LINES_ALL.PO_LINE_ID%TYPE := null;
585 l_approved_flag VARCHAR2(20);
586 l_rule_prefix VARCHAR2(20);
587 l_start_date DATE;
588 l_end_date DATE;
589 l_create_update_flag VARCHAR2(20);
590 l_header_processable_flag VARCHAR2(1):='Y';
591 l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%TYPE;
592 l_sourcing_level VARCHAR2(20);
593
594 /* Local variables for API Standard Parameters */
595 l_api_version NUMBER :=1.0;
596 l_init_msg_list VARCHAR2(5):=FND_API.G_FALSE;
597 l_commit VARCHAR2(5):=FND_API.G_FALSE;
598 l_return_status VARCHAR2(5);
599 l_msg_count NUMBER;
600 l_msg_data VARCHAR2(2000);
601 l_cr_api_version NUMBER :=1.0;
602 l_cr_api_name VARCHAR2(50):='create_autosource_rules';
603 l_error_message VARCHAR2(4000);
604 l_sr_name_count NUMBER;
605
606 /****************************Cursors Declaration*******************/
607
608 -- SQL What: Querying for all lines that have been created in the
609 -- current revision of the document
610 -- SQL Why: Need to create SR/ASL only for these new lines.
611 -- SQL Join: po_header_id
612
613 CURSOR get_new_lines_to_be_processed IS
614 SELECT pl.po_line_id,
615 -- pl.line_num,
616 ph.start_date,
617 ph.end_date,
618 pl.item_id,
619 DECODE(ph.approved_flag,'Y','APPROVED',null)
620 FROM po_lines_all pl,
621 po_headers_all ph
622 WHERE pl.po_header_id = ph.po_header_id
623 AND ph.po_header_id = p_document_id
624 AND pl.item_id is not null
625 AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
626 AND nvl(pl.cancel_flag,'N') = 'N'
627 /* AND NOT EXISTS
628 (SELECT 'Line included in Prior Revision'
629 FROM po_lines_archive pla
630 WHERE pla.po_line_id = pl.po_line_id
631 AND pla.revision_num < ph.revision_num)*/
632 AND pl.line_num > l_previous_line_num
633 AND NVL(pl.expiration_date ,sysdate+1) >= sysdate ---Bug 10022351,10192008
634 ORDER BY ph.po_header_id, pl.line_num;
635
636 -- SQL What: Querying for all lines in the document that are still valid
637 -- SQL Why: Need to create SR/ASL for all the lines.
638 -- SQL Join: po_header_id
639
640 CURSOR get_all_document_lines IS
641 SELECT pl.po_line_id,
642 -- pl.line_num,
643 ph.start_date,
644 ph.end_date,
645 pl.item_id,
646 DECODE(ph.approved_flag,'Y','APPROVED',null)
647 FROM po_lines_all pl,
648 po_headers_all ph
649 WHERE pl.po_header_id = ph.po_header_id
650 AND ph.po_header_id = p_document_id
651 AND pl.item_id is not null
652 AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
653 AND nvl(pl.cancel_flag,'N') = 'N'
654 AND NVL(pl.expiration_date ,sysdate+1) >= sysdate ---Bug 10022351,10192008
655 ORDER BY ph.po_header_id, pl.line_num;
656
657 /****************************Cursors Declaration End*******************/
658
659 BEGIN
660 IF g_po_pdoi_write_to_file IS NULL THEN
661 po_debug.set_file_io(NULL);
662 ELSIF g_po_pdoi_write_to_file = 'Y' THEN
663 po_debug.set_file_io(TRUE);
664 ELSE
665 po_debug.set_file_io(FALSE);
666 END IF;
667
668 FND_FILE.put_line(FND_FILE.LOG,'==>POASLGEN starts at ' || TO_CHAR(sysdate,'MM/DD/RR HH24:MI:SS'));
669
670 IF g_po_pdoi_write_to_file ='Y' THEN
671 l_progress :='000';
672 PO_DEBUG.put_line('Entering '||g_pkg_name||l_procedure_name);
673 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_document_id : '||to_char(p_document_id));
674 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_vendor_id : '||to_char(p_vendor_id));
675 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_purchasing_org_id : '||to_char(p_purchasing_org_id));
676 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_vendor_site_id : '||to_char(p_vendor_site_id));
677 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_create_sourcing_rule : '||p_create_sourcing_rule);
678 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_update_sourcing_rule : '||p_update_sourcing_rule);
679 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_agreement_lines_selection : '||p_agreement_lines_selection);
680 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_sourcing_level : '||p_sourcing_level);
681 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_inv_org : '||to_char(p_inv_org));
682 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_sourcing_rule_name : '||p_sourcing_rule_name);
683 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_release_gen_method : '||p_release_gen_method);
684 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_assignment_set_id : '||p_assignment_set_id);
685 END IF;
686
687
688
689 ----Checking for API compatibility
690 IF NOT FND_API.Compatible_API_Call (l_cr_api_version,
691 p_api_version,
692 l_cr_api_name,
693 g_pkg_name
694 )
695 THEN
696
697 IF g_po_pdoi_write_to_file='Y' THEN
698 l_progress := '001';
699 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_progress||' Incompatible call to API : '||l_cr_api_version);
700 PO_DEBUG.put_line(l_progress||' Incompatible call to API : '||l_cr_api_version);
701 END IF;
702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
703 END IF;
704
705 ----This Procedure Should Work Only If Archival Is Set To Archive On Approve
706 IF p_purchasing_org_id is NULL THEN
707 l_orgid := po_moac_utils_pvt.get_current_org_id; --<R12 MOAC>
708 ELSE
709 l_orgid :=p_purchasing_org_id;
710 END IF;
711
712 ----Set The Org Context
713 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
714
715 -- SQL What: Querying for archival option for the purchasing org
716 -- SQL Why: Would continue processing only if archival is on 'APPROVE'
717
718 SELECT archive_external_revision_code
719 INTO l_archive_on_approve
720 FROM po_document_types_all
721 WHERE org_id=l_orgid
722 AND document_type_code='PA'
723 AND document_subtype='BLANKET';
724
725
726 IF g_po_pdoi_write_to_file='Y' THEN
727 l_progress :='002';
728 PO_DEBUG.put_line(l_progress||' : Operating Unit Id : '||l_orgid);
729 l_progress :='003';
730 PO_DEBUG.put_line(l_progress||' : Archival Option :'||l_archive_on_approve);
731 END IF;
732
733 ----This Concurrent Program should run Only If Archival Is Set To Archive On Approve
734 IF(l_archive_on_approve <> 'APPROVE')THEN
735 IF g_po_pdoi_write_to_file='Y' THEN
736 l_progress :='004';
737 PO_DEBUG.put_line(l_progress||' : Archival Option '||l_archive_on_approve||' is incompatible with this concurrent request :');
738 END IF;
739 l_error_message:=PO_CORE_S.get_translated_text('PO_SR_ASL_ARCHIVAL_OPTION');
740 FND_FILE.put_line(FND_FILE.OUTPUT,l_error_message);
741 RAISE FND_API.G_EXC_ERROR;--RAISE ERROR
742 END IF;
743
744 --ECO#4713869 We would now remove this because the behaviour of sourcing
745 --rule name is being defined differently. It would merely be used to update
746 --if it exists and assigned to the default assignment set. Or else the name
747 --would be ignored.
748 --
749 -- select count(*)
750 -- into l_sr_name_count
751 -- from mrp_sourcing_rules
752 -- where sourcing_rule_name=p_sourcing_rule_name;
753 --
754 -- IF l_sr_name_count >0 THEN
755 -- l_error_message :=PO_CORE_S.get_translated_text('PO_SR_ASL_UNIQUE_RULE_NAME');
756 -- FND_FILE.put_line(FND_FILE.OUTPUT,l_error_message);
757 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
758 -- END IF;
759
760 --The minimum line number that has an archival revision same as the header revision
761 --and has not been archived before would signify the last created line on the
762 --Blanket PO in the previous revision.
763
764 IF(p_agreement_lines_selection='NEW')THEN
765
766 IF g_po_pdoi_write_to_file ='Y' THEN
767 l_progress :='005';
768 PO_DEBUG.put_line(l_progress||' : Agreement Lines Selection :'||p_agreement_lines_selection);
769 END IF;
770
771 -- SQL What: Querying to find the last line created in a previous
772 -- revision of the document
776 SELECT max(pola.line_num)
773 -- SQL Why: Would process all lines that were created after the line
774 -- that we get from the query.
775 -- SQL Join: po_header_id
777 INTO l_previous_line_num
778 FROM
779 po_lines_archive_all pola,
780 po_headers_all poh
781 WHERE
782 poh.po_header_id=p_document_id
783 AND pola.po_header_id=poh.po_header_id
784 AND pola.revision_num < poh.revision_num ;
785
786 -- If the revision is the first revision then max would return null without a no data
787 -- found .. In this case we would set the value of l_previous_line_num to 0
788 IF l_previous_line_num is null THEN
789 l_previous_line_num:=0;
790 END IF;
791
792
793 IF g_po_pdoi_write_to_file ='Y' THEN
794 l_progress :='006';
795 PO_DEBUG.put_line(l_progress||': Previous Line Number :'||p_agreement_lines_selection);
796 END IF;
797
798 END IF;
799
800 l_interface_header_id := p_document_id;
801
802 --Open the cursor for new lines or old lines depending on the
803 --value of p_agreement_lines_selection.
804
805 IF p_agreement_lines_selection='NEW' THEN
806 OPEN get_new_lines_to_be_processed;
807 IF g_po_pdoi_write_to_file ='Y' THEN
808 l_progress :='007';
809 PO_DEBUG.put_line(l_progress||': Opened cursor :'||'get_new_lines_to_be_processed');
810 END IF;
811 ELSE
812 OPEN get_all_document_lines;
813 IF g_po_pdoi_write_to_file ='Y' THEN
814 l_progress :='008';
815 PO_DEBUG.put_line(l_progress||': Opened cursor : get_all_document_lines');
816 END IF;
817 END IF;
818
819 -- If the create sourcing rule is set to 'Y' set the value of create_update_flag to CREATE.
820 -- The value of p_update_sourcing_rule can be 'Y' only if the value of p_create_sourcing_rule
821 -- is set to 'Y'. If the value of p_update_sourcing_rule is set to 'Y' then override the value
822 -- of l_create__update_flag and set it to 'CREATE_UPDATE'
823 IF p_create_sourcing_rule = 'Y' THEN
824 l_create_update_flag := 'CREATE';
825 END IF;
826
827 IF p_update_sourcing_rule = 'Y' THEN
828 l_create_update_flag := 'CREATE_UPDATE';
829 END IF;
830
831 LOOP
832
833 IF p_agreement_lines_selection='NEW' THEN
834 FETCH get_new_lines_to_be_processed
835 INTO l_po_line_id, l_start_date, l_end_date,
836 l_item_id, l_approved_flag;
837 EXIT WHEN get_new_lines_to_be_processed%NOTFOUND;
838 ELSE
839 FETCH get_all_document_lines
840 INTO l_po_line_id, l_start_date, l_end_date,
841 l_item_id, l_approved_flag;
842 EXIT WHEN get_all_document_lines%NOTFOUND;
843 END IF;
844
845 l_interface_line_id:=l_po_line_id;
846 l_header_processable_flag :='Y';
847 create_sourcing_rules_asl
848 (
849 p_api_version => l_api_version,
850 p_init_msg_list => l_init_msg_list,
851 p_commit => l_commit,
852 x_return_status => l_return_status,
853 x_msg_count => l_msg_count,
854 x_msg_data => l_msg_data,
855 p_interface_header_id => l_interface_header_id,
856 p_interface_line_id => l_interface_line_id,
857 p_document_id => p_document_id,
858 p_po_line_id => l_po_line_id,
859 p_document_type => l_document_type_code,
860 p_approval_status => l_approved_flag,
861 p_vendor_id => p_vendor_id,
862 p_vendor_site_id => p_vendor_site_id,
863 p_inv_org_id => p_inv_org,
864 p_sourcing_level => p_sourcing_level,
865 p_item_id => l_item_id,
866 p_category_id => l_category_id,
867 p_rel_gen_method => p_release_gen_method,
868 p_rule_name => p_sourcing_rule_name,
869 p_rule_name_prefix => l_rule_prefix,
870 p_start_date => l_start_date,
871 p_end_date => l_end_date,
872 p_assignment_set_id => p_assignment_set_id,
873 p_create_update_code => l_create_update_flag,
874 p_interface_error_code => 'PO_DOCS_OPEN_INTERFACE',
875 x_header_processable_flag => l_header_processable_flag
876 );
877
878 x_return_status := l_return_status;
879
880 END LOOP;
881
882 FND_FILE.put_line(FND_FILE.LOG,'==>POASLGEN ends at ' || TO_CHAR(sysdate,'MM/DD/RR HH24:MI:SS'));
883 --Close the cursor after the processing has been done.
884 IF p_agreement_lines_selection='NEW' THEN
885 CLOSE get_new_lines_to_be_processed;
886 IF g_po_pdoi_write_to_file ='Y' THEN
887 l_progress :='009';
888 PO_DEBUG.put_line(l_progress||' : Closed cursor : get_new_lines_to_be_processed');
889 END IF;
890 ELSE
891 CLOSE get_all_document_lines;
892 IF g_po_pdoi_write_to_file ='Y' THEN
893 l_progress :='010';
894 PO_DEBUG.put_line(l_progress||' : Closed cursor : get_all_document_lines');
895 END IF;
896 END IF;
897
898 EXCEPTION
899 WHEN OTHERS THEN
900
901 FND_FILE.put_line(FND_FILE.LOG,'Error occured in '||' : '||g_pkg_name||l_procedure_name||SQLERRM||' : '||SQLCODE);
902
903
904 IF p_agreement_lines_selection='NEW' and get_new_lines_to_be_processed%isopen THEN
905 CLOSE get_new_lines_to_be_processed;
906 ELSIF get_all_document_lines%ISOPEN THEN
907 CLOSE get_all_document_lines;
908 END IF;
909
910 po_message_s.SQL_ERROR(
911 p_package => g_pkg_name
912 , p_routine => l_procedure_name
913 , p_location => l_progress
914 , p_sqlcode => SQLCODE
915 , p_sqlerrm => SQLERRM(SQLCODE)
916 );
917
918 END create_autosource_rules;
919 --<LOCAL SR/ASL PROJECT 11i11 END>
920
921 --<LOCAL SR/ASL PROJECT 11i11 START>
922 -------------------------------------------------------------------------------
923 --Start of Comments
924 --Name: create_sourcing_rules_asl
925 --Pre-reqs:
926 -- None
927 --Modifies:
928 -- n/a
929 --Locks:
930 -- n/a
931 --Function:
932 -- The procedure create_sourcing_rules_asl is a new procedure that is
933 -- called by the procedure CREATE_AUTOSOURCE_RULES. This takes all the
934 -- relevant parameters pertaining to a single document line and creates
935 -- the Sourcing Rules and ASL's for that line. This procedure calls the
936 -- two procedures PO_SOURCING_RULES_SV.create_updates_sourcing_rules for
937 -- creating the sourcing rule and procedure
938 -- PO_APPROVED_SUPPLIER_LIST_SV. create_po_asl_entries for creating the ASL's.
939 --Parameters:
940 --IN:
941 --p_api_version
942 -- Standard parameter for verifying the api version
943 --p_init_msg_list
944 -- The p_init_msg_list parameter allows API callers to request
945 -- that the API does the initialization of the message list on
946 -- their behalf.
947 --p_commit
948 -- Standard parameter which dictates whether or not data should
949 -- be commited in the api
950 --p_validation_level
951 -- The p_validation_level parameter to determine which validation
952 -- steps should be executed and which steps should be skipped
953 --p_interface_header_id
954 -- sequence generated unique identifier of interface headers table. Used for
955 -- insertion into po_interface_errors
956 --p_interface_line_id
957 -- sequence generated unique identifier of interface line table. Used for
958 -- insertion into po_interface_errors
959 --p_document_id
960 -- unique identifier of the document for which the sourcing
961 -- rule is created or updated
962 --p_po_line_id
963 -- The line id of the document being processed.
964 --p_document_type
965 -- The type of document being created. Should be Blanket/GA
966 --p_approval_status
967 -- The approval status of the document for which the sourcing
968 -- rule is created or updated
969 --p_vendor_id
970 -- unique identifier of vendor of the document for which the sourcing
971 -- rule is created or updated
972 --p_vendor_site_id
973 -- unique identifier of vendor site enabled for the document for which
974 -- the sourcing rule is created or updated
975 --p_inv_org_id
976 -- Inventory organization_id for which the SR/ASL needs to be created.
977 --p_sourcing_level
978 -- Determines whether the Assignment of SR is done to the Assignment
979 -- Set at Item/Item Organization level
980 --p_item_id
981 -- Item for which the ASL needs to be created.
982 --p_category_id
983 -- If the ASL is being created for a Category this field needs to be populated.
984 --p_release_gen_method
985 -- This specifies what the release generation method would be :
986 -- CREATE, CREATE_AND_APPROVE, NONE
987 --p_rule_name
988 -- The name of Sourcing Rule being created/updated.
989 --p_rule_name_prefix
990 -- Prefix that will be used to create a name for new sourcing rule.
991 -- The name is p_rule_name_prefix_<SR Sequence number);
992 --p_start_date
993 -- The start date of Sourcing Rule
994 --p_end_date
995 -- The disable date of Sourcing Rule
996 --p_assignment_set_id
997 -- Assignment Set to which the Sourcing Rules would be assigned.
998 --p_assignment_type_id
999 -- Type of Assignment. Valid values are (1,3,4,5,6)
1000 --p_create_update_code
1001 -- Valid values are CREATE / CREATE_UPDATE
1002 --OUT:
1003 --x_return_status
1004 -- This indicates whether or not the api call was successful.
1005 -- Returns 'S' or 'E' for success or error respectively.
1006 --x_msg_count
1007 -- Holds the count for the number of messages in the message list.
1008 --x_msg_data
1009 -- If only one error is encountered message data holds the error message
1010 --x_Header_processable_flag
1011 -- Running parameter which decides whether to do further processing or error out
1012 -- Value is set to N if there was any error encountered.
1013 --Testing:
1014 --
1015 --
1016 --End of Comments
1017 -------------------------------------------------------------------------------
1018
1019 /*
1020 Assignment Type => Assignment Type ID Mapping
1021
1022 Assignment Type Assignment Type Id
1023 --------------------------------------------
1024 --------------------------------------------
1025 Global => 1
1026 Item => 3
1027 Organization => 4
1028 Category-Org => 5
1029 Item-Organization => 6
1030
1031 */
1032
1033 PROCEDURE CREATE_SOURCING_RULES_ASL
1034 (
1035 p_api_version IN NUMBER,
1036 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1037 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1038 x_return_status OUT NOCOPY VARCHAR2,
1039 x_msg_count OUT NOCOPY NUMBER,
1040 x_msg_data OUT NOCOPY VARCHAR2,
1041 p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%type,
1042 p_interface_line_id IN PO_LINES_INTERFACE.interface_line_id%type,
1043 p_document_id IN PO_HEADERS.po_header_id%type,
1044 p_po_line_id IN PO_LINES.po_line_id%type,
1045 p_document_type IN PO_HEADERS.type_lookup_code%type,
1046 p_approval_status IN VARCHAR2,
1047 p_vendor_id IN PO_HEADERS.vendor_id%type,
1048 p_vendor_site_id IN PO_HEADERS.vendor_site_id%type,
1049 p_inv_org_id IN HR_ALL_ORGANIZATION_UNITS.organization_id%type,
1050 p_sourcing_level IN VARCHAR2,
1051 p_item_id IN MTL_SYSTEM_ITEMS.inventory_item_id%type,
1052 p_category_id IN MTL_ITEM_CATEGORIES.category_id%type,
1053 p_rel_gen_method IN PO_ASL_ATTRIBUTES.release_generation_method%type,
1054 p_rule_name IN MRP_SOURCING_RULES.sourcing_rule_name%type,
1055 p_rule_name_prefix IN VARCHAR2,
1056 p_start_date IN DATE,
1057 p_end_date IN DATE,
1058 p_assignment_set_id IN MRP_ASSIGNMENT_SETS.assignment_set_id%type,
1059 p_create_update_code IN VARCHAR2,
1060 p_interface_error_code IN VARCHAR2,
1061 x_header_processable_flag IN OUT NOCOPY VARCHAR2
1062 )IS
1063 l_document_id PO_HEADERS_ALL.po_header_id%type;
1064 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%type;
1065 l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%type;
1066 l_assignment_type_id NUMBER;
1067
1068 /* Package specific information. Required for logging */
1069 l_procedure_name varchar2(50):='create_sourcing_rules_asl : ';
1070 l_progress VARCHAR2(3);
1071
1072 -- SQL What: Querying for all vendor sites enabled for the GA.
1073 -- SQL Why: Need to create sourcing rules/asl's for all vendor sites
1074 -- retrieved from the cursor.
1075
1076 CURSOR c_enabled_sites is
1077 select vendor_site_id
1078 from po_ga_org_assignments
1079 where po_header_id = p_document_id
1080 and enabled_flag = 'Y';
1081
1082 BEGIN
1083
1084 IF g_po_pdoi_write_to_file ='Y' THEN
1085 l_progress :='000';
1086 PO_DEBUG.put_line(l_progress||'Calling procedure create_update_sourcing_rules ');
1087 END IF;
1088 --Setting the assignment type depending on the value of sourcing_level
1089 IF nvl(p_sourcing_level,'ITEM')='ITEM' THEN
1090 l_assignment_type_id:=3;
1091 ELSIF p_sourcing_level='ITEM-ORGANIZATION' THEN
1092 l_assignment_type_id:=6;
1093 END IF;
1094
1095 PO_SOURCING_RULES_SV.create_update_sourcing_rule(
1096 p_interface_header_id => p_interface_header_id,
1097 p_interface_line_id => p_interface_line_id,
1098 p_item_id => p_item_id,
1099 p_vendor_id => p_vendor_id,
1100 p_po_header_id => p_document_id,
1101 p_po_line_id => p_po_line_id,
1102 p_document_type => p_document_type,
1103 p_approval_status => p_approval_status,
1104 p_rule_name => p_rule_name,
1105 p_rule_name_prefix => p_rule_name_prefix,
1106 p_start_date => p_start_date,
1107 p_end_date => p_end_date,
1108 p_create_update_code => p_create_update_code,
1109 p_organization_id => p_inv_org_id,
1110 p_assignment_type_id => l_assignment_type_id,
1111 p_po_interface_error_code => p_interface_error_code,
1112 x_header_processable_flag => x_header_processable_flag,
1113 x_return_status => x_return_status,
1114 --<LOCAL SR/ASL PROJECT 11i11 START>
1115 p_assignment_set_id => p_assignment_set_id,
1116 p_vendor_site_id => p_vendor_site_id);
1117 --<LOCAL SR/ASL PROJECT 11i11 END>
1118 -- We should proceed with creation of ASL's even if sourcing rules fail. This is why we set
1119 -- the header processable flag to 'Y'
1120 x_header_processable_flag := 'Y';
1121
1122 IF (g_po_pdoi_write_to_file = 'Y') THEN
1123 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1124 PO_DEBUG.put_line('Sourcing Rules creation API returned successfully');
1125 ELSE
1126 PO_DEBUG.put_line('Sourcing Rules creation API returned failure');
1127 END IF;
1128 END IF;
1129
1130 -- SQL What: Querying for the value of Global Agreement Flag to
1131 -- check if the document is a GA.
1132 -- SQL Why: If the document is a GA then we would create the ASL
1133 -- for all vendor sites if p_vendor_site_id is null. i.e
1134 -- if value of vendor site id has been passed as null to
1135 -- the procedure create_sourcing_rules_asl.
1136
1137 IF p_document_id is not null THEN
1138 SELECT global_agreement_flag,vendor_site_id
1139 INTO l_ga_flag , l_vendor_site_id
1140 FROM po_headers_all
1141 WHERE po_header_id = p_document_id;
1142 END IF;
1143
1144 IF g_po_pdoi_write_to_file ='Y' THEN
1145 l_progress :='001';
1146 PO_DEBUG.put_line(l_progress||' : Global Agreement Flag : '||l_ga_flag);
1147 END IF;
1148
1149 IF (p_vendor_site_id IS NOT NULL)THEN
1150 l_vendor_site_id:=p_vendor_site_id;
1151 IF g_po_pdoi_write_to_file ='Y' THEN
1152 l_progress :='002';
1153 PO_DEBUG.put_line(l_progress||' : Value of p_vendor_site_id : '||to_char(p_vendor_site_id));
1154 END IF;
1155 END IF;
1156
1157 IF (nvl(l_ga_flag,'N') = 'Y' AND ( p_vendor_site_id IS NULL)) THEN
1158
1159 OPEN c_enabled_sites;
1160 LOOP
1161 FETCH c_enabled_sites INTO l_vendor_site_id;
1162 EXIT WHEN c_enabled_sites%NOTFOUND;
1163 PO_APPROVED_SUPPLIER_LIST_SV.create_po_asl_entries(
1164 x_interface_header_id => p_interface_header_id,
1165 X_interface_line_id => p_interface_line_id,
1166 X_item_id => p_item_id,
1167 X_category_id => p_category_id,
1168 X_po_header_id => p_document_id,
1169 X_po_line_id => p_po_line_id,
1170 X_document_type => p_document_type,
1171 x_vendor_site_id => l_vendor_site_id,
1172 X_rel_gen_method => p_rel_gen_method,
1173 X_asl_org_id => p_inv_org_id,
1174 X_header_processable_flag => x_header_processable_flag,
1175 X_po_interface_error_code => p_interface_error_code,
1176 --<LOCAL SR/ASL PROJECT 11i11 START>
1177 p_sourcing_level => p_sourcing_level
1178 --<LOCAL SR/ASL PROJECT 11i11 END>
1179 );
1180 END LOOP;
1181
1182 close c_enabled_sites;
1183
1184 ELSE -- If not a global agreement you do not need to loop
1185
1186 PO_APPROVED_SUPPLIER_LIST_SV.create_po_asl_entries(
1187 x_interface_header_id => p_interface_header_id,
1188 X_interface_line_id => p_interface_line_id,
1189 X_item_id => p_item_id,
1190 X_category_id => p_category_id,
1191 X_po_header_id => p_document_id,
1192 X_po_line_id => p_po_line_id,
1193 X_document_type => p_document_type,
1194 x_vendor_site_id => l_vendor_site_id,
1195 X_rel_gen_method => p_rel_gen_method,
1196 X_asl_org_id => p_inv_org_id,
1197 X_header_processable_flag => x_header_processable_flag,
1198 X_po_interface_error_code => p_interface_error_code,
1199 --<LOCAL SR/ASL PROJECT 11i11 START>
1200 p_sourcing_level => p_sourcing_level
1201 --<LOCAL SR/ASL PROJECT 11i11 END>
1202 );
1203 END IF;
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 x_header_processable_flag := 'N';
1207 FND_FILE.put_line(FND_FILE.LOG,'Error occured in '||' : '||g_pkg_name||l_procedure_name||SQLERRM||' : '||SQLCODE);
1208 IF (g_po_pdoi_write_to_file = 'Y') THEN
1209 PO_DEBUG.put_line('Sourcing Rules/ASL creation failed');
1210 END IF;
1211 po_message_s.SQL_ERROR(
1212 p_package => g_pkg_name
1213 , p_routine => l_procedure_name
1214 , p_location => l_progress
1215 , p_sqlcode => SQLCODE
1216 , p_sqlerrm => SQLERRM(SQLCODE)
1217 );
1218 IF(c_enabled_sites%ISOPEN)THEN
1219 CLOSE c_enabled_sites;
1220 END IF;
1221 END CREATE_SOURCING_RULES_ASL;
1222 --<LOCAL SR/ASL PROJECT 11i11 END>
1223
1224 -------------------------------------------------------------------------------
1225 --Bug 10417292 - Launching Conucrrent Request for Creating ASL and SR
1226 --Instead of doing it in workflow
1227 --Name: create_autosource_rules
1228 -- This procedure is a wrapper around the call to PO_CREATE_SR_ASL.create_autosource_rules.
1229 -- This will be used in poapparv.wft to create AR and ASL in one go without iterating for each line.
1230 --End of Comments
1231 -------------------------------------------------------------------------------
1232 procedure CREATE_SR_ASL (itemtype in varchar2,
1233 itemkey in varchar2,
1234 actid in number,
1235 funcmode in varchar2,
1236 resultout out NOCOPY varchar2 ) is
1237
1238 l_vendor_id PO_HEADERS_ALL.vendor_id%type;
1239 l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%type;
1240 l_document_id PO_HEADERS_ALL.po_header_id%type;
1241 l_release_generation_method PO_ASL_ATTRIBUTES.release_generation_method%type;
1242 l_api_version NUMBER :=1.0;
1243 l_init_msg_list VARCHAR2(5) :=FND_API.G_FALSE;
1244 l_commit VARCHAR2(5) :=FND_API.G_FALSE;
1245 l_validation_level NUMBER :=FND_API.G_VALID_LEVEL_FULL;
1246 l_return_status VARCHAR2(5);
1247 l_msg_count NUMBER;
1248 l_msg_data VARCHAR2(2000);
1249 l_progress VARCHAR2(3) :='000';
1250 x_progress varchar2(300);
1251 l_request_id number;
1252 l_orgid number;
1253
1254
1255
1256 BEGIN
1257
1258 x_progress := 'PO_CREATE_SR_ASL.Create_SR_ASL: 01';
1259 IF (g_po_wf_debug = 'Y') THEN
1260 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1261 END IF;
1262
1263 l_document_id := wf_engine.GetItemAttrNumber (itemtype, itemkey, 'DOCUMENT_ID');
1264
1265
1266 begin
1267 select vendor_id, vendor_site_id
1268 into l_vendor_id, l_vendor_site_id
1269 from po_headers_all
1270 where po_header_id = l_document_id;
1271 exception
1272 when no_data_found then
1273 l_vendor_id := null;
1274 l_vendor_site_id := null;
1275 end;
1276
1277 l_release_generation_method := wf_engine.GetItemAttrText ( itemtype, itemkey, 'RELEASE_GENERATION_METHOD');
1278
1279 x_progress := 'PO_CREATE_SR_ASL.Create_SR_ASL: l_vendor_id: '||l_vendor_id||', l_vendor_site_id: '||l_vendor_site_id||', l_document_id: '||l_document_id||', l_release_generation_method: '||l_release_generation_method;
1280
1281 IF (g_po_wf_debug = 'Y') THEN
1282 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1283 END IF;
1284
1285 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1286 itemkey => itemkey,
1287 aname => 'ORG_ID');
1288
1289 IF l_orgid is NOT NULL THEN
1290
1291 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
1292
1293 END IF;
1294
1295
1296
1297
1298 l_request_id := fnd_request.submit_request(
1299 'PO'
1300 ,'POASLGEN'
1301 ,null
1302 ,null
1303 ,FALSE
1304 ,l_vendor_id -- Supplier
1305 ,l_document_id -- Document number
1306 ,null -- Global agreement flag
1307 ,l_orgid -- purchasing organization
1308 ,l_vendor_site_id -- supplier site
1309 ,'ALL' -- select agreement lines
1310 ,null -- assignment set
1311 ,null -- sourcing level
1312 ,null -- Inv org enable
1313 ,null -- inventory organization
1314 ,null -- sourcing rule name
1315 ,l_release_generation_method -- Release generation method
1316 );
1317
1318 x_progress := 'PO_CREATE_SR_ASL.Create_SR_ASL: : Request id is - '|| l_request_id;
1319 IF (g_po_wf_debug = 'Y') THEN
1320 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1321 END IF;
1322
1323 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemtype,
1324 itemkey => itemkey,
1325 aname => 'SR_ASL_REQUEST_ID',
1326 avalue => l_request_id);
1327
1328 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1329
1330
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 x_progress := 'PO_CREATE_SR_ASL.Create_SR_ASL: Error: '||sqlerrm;
1334 IF (g_po_wf_debug = 'Y') THEN
1335 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1336 END IF;
1337 resultout := wf_engine.eng_completed || ':' || '';
1338
1339
1340 END Create_SR_ASL;
1341
1342
1343
1344 END PO_CREATE_SR_ASL;
[Home] [Help]