1 PACKAGE BODY PO_CREATE_SR_ASL AS
2 /* $Header: POXWSRAB.pls 120.2.12000000.2 2007/05/04 00:04:22 lswamy 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 ORDER BY ph.po_header_id, pl.line_num;
634
635 -- SQL What: Querying for all lines in the document that are still valid
636 -- SQL Why: Need to create SR/ASL for all the lines.
637 -- SQL Join: po_header_id
638
639 CURSOR get_all_document_lines IS
640 SELECT pl.po_line_id,
641 -- pl.line_num,
642 ph.start_date,
643 ph.end_date,
644 pl.item_id,
645 DECODE(ph.approved_flag,'Y','APPROVED',null)
646 FROM po_lines_all pl,
647 po_headers_all ph
648 WHERE pl.po_header_id = ph.po_header_id
649 AND ph.po_header_id = p_document_id
650 AND pl.item_id is not null
651 AND nvl(pl.closed_code,'OPEN') <> 'FINALLY CLOSED'
652 AND nvl(pl.cancel_flag,'N') = 'N'
653 ORDER BY ph.po_header_id, pl.line_num;
654
655 /****************************Cursors Declaration End*******************/
656
657 BEGIN
658 IF g_po_pdoi_write_to_file IS NULL THEN
659 po_debug.set_file_io(NULL);
660 ELSIF g_po_pdoi_write_to_file = 'Y' THEN
661 po_debug.set_file_io(TRUE);
662 ELSE
663 po_debug.set_file_io(FALSE);
664 END IF;
665
666 FND_FILE.put_line(FND_FILE.LOG,'==>POASLGEN starts at ' || TO_CHAR(sysdate,'MM/DD/RR HH24:MI:SS'));
667
668 IF g_po_pdoi_write_to_file ='Y' THEN
669 l_progress :='000';
670 PO_DEBUG.put_line('Entering '||g_pkg_name||l_procedure_name);
671 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_document_id : '||to_char(p_document_id));
672 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_vendor_id : '||to_char(p_vendor_id));
673 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_purchasing_org_id : '||to_char(p_purchasing_org_id));
674 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_vendor_site_id : '||to_char(p_vendor_site_id));
675 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_create_sourcing_rule : '||p_create_sourcing_rule);
676 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_update_sourcing_rule : '||p_update_sourcing_rule);
677 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_agreement_lines_selection : '||p_agreement_lines_selection);
678 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_sourcing_level : '||p_sourcing_level);
679 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_inv_org : '||to_char(p_inv_org));
680 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_sourcing_rule_name : '||p_sourcing_rule_name);
681 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_release_gen_method : '||p_release_gen_method);
682 PO_DEBUG.put_line(l_progress||' : Input Parameter : '||'p_assignment_set_id : '||p_assignment_set_id);
683 END IF;
684
685
686
687 ----Checking for API compatibility
688 IF NOT FND_API.Compatible_API_Call (l_cr_api_version,
689 p_api_version,
690 l_cr_api_name,
691 g_pkg_name
692 )
693 THEN
694
695 IF g_po_pdoi_write_to_file='Y' THEN
696 l_progress := '001';
697 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_progress||' Incompatible call to API : '||l_cr_api_version);
698 PO_DEBUG.put_line(l_progress||' Incompatible call to API : '||l_cr_api_version);
699 END IF;
700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
701 END IF;
702
703 ----This Procedure Should Work Only If Archival Is Set To Archive On Approve
704 IF p_purchasing_org_id is NULL THEN
705 l_orgid := po_moac_utils_pvt.get_current_org_id; --<R12 MOAC>
706 ELSE
707 l_orgid :=p_purchasing_org_id;
708 END IF;
709
710 ----Set The Org Context
711 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
712
713 -- SQL What: Querying for archival option for the purchasing org
714 -- SQL Why: Would continue processing only if archival is on 'APPROVE'
715
716 SELECT archive_external_revision_code
717 INTO l_archive_on_approve
718 FROM po_document_types_all
719 WHERE org_id=l_orgid
720 AND document_type_code='PA'
721 AND document_subtype='BLANKET';
722
723
724 IF g_po_pdoi_write_to_file='Y' THEN
725 l_progress :='002';
726 PO_DEBUG.put_line(l_progress||' : Operating Unit Id : '||l_orgid);
727 l_progress :='003';
728 PO_DEBUG.put_line(l_progress||' : Archival Option :'||l_archive_on_approve);
729 END IF;
730
731 ----This Concurrent Program should run Only If Archival Is Set To Archive On Approve
732 IF(l_archive_on_approve <> 'APPROVE')THEN
733 IF g_po_pdoi_write_to_file='Y' THEN
734 l_progress :='004';
735 PO_DEBUG.put_line(l_progress||' : Archival Option '||l_archive_on_approve||' is incompatible with this concurrent request :');
736 END IF;
737 l_error_message:=PO_CORE_S.get_translated_text('PO_SR_ASL_ARCHIVAL_OPTION');
738 FND_FILE.put_line(FND_FILE.OUTPUT,l_error_message);
739 RAISE FND_API.G_EXC_ERROR;--RAISE ERROR
740 END IF;
741
742 --ECO#4713869 We would now remove this because the behaviour of sourcing
743 --rule name is being defined differently. It would merely be used to update
744 --if it exists and assigned to the default assignment set. Or else the name
745 --would be ignored.
746 --
747 -- select count(*)
748 -- into l_sr_name_count
749 -- from mrp_sourcing_rules
750 -- where sourcing_rule_name=p_sourcing_rule_name;
751 --
752 -- IF l_sr_name_count >0 THEN
753 -- l_error_message :=PO_CORE_S.get_translated_text('PO_SR_ASL_UNIQUE_RULE_NAME');
754 -- FND_FILE.put_line(FND_FILE.OUTPUT,l_error_message);
755 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
756 -- END IF;
757
758 --The minimum line number that has an archival revision same as the header revision
759 --and has not been archived before would signify the last created line on the
760 --Blanket PO in the previous revision.
761
762 IF(p_agreement_lines_selection='NEW')THEN
763
764 IF g_po_pdoi_write_to_file ='Y' THEN
765 l_progress :='005';
766 PO_DEBUG.put_line(l_progress||' : Agreement Lines Selection :'||p_agreement_lines_selection);
767 END IF;
768
769 -- SQL What: Querying to find the last line created in a previous
770 -- revision of the document
771 -- SQL Why: Would process all lines that were created after the line
772 -- that we get from the query.
773 -- SQL Join: po_header_id
774 SELECT max(pola.line_num)
775 INTO l_previous_line_num
776 FROM
777 po_lines_archive_all pola,
778 po_headers_all poh
779 WHERE
780 poh.po_header_id=p_document_id
781 AND pola.po_header_id=poh.po_header_id
782 AND pola.revision_num < poh.revision_num ;
783
784 -- If the revision is the first revision then max would return null without a no data
785 -- found .. In this case we would set the value of l_previous_line_num to 0
786 IF l_previous_line_num is null THEN
787 l_previous_line_num:=0;
788 END IF;
789
790
791 IF g_po_pdoi_write_to_file ='Y' THEN
792 l_progress :='006';
793 PO_DEBUG.put_line(l_progress||': Previous Line Number :'||p_agreement_lines_selection);
794 END IF;
795
796 END IF;
797
798 l_interface_header_id := p_document_id;
799
800 --Open the cursor for new lines or old lines depending on the
801 --value of p_agreement_lines_selection.
802
803 IF p_agreement_lines_selection='NEW' THEN
804 OPEN get_new_lines_to_be_processed;
805 IF g_po_pdoi_write_to_file ='Y' THEN
806 l_progress :='007';
807 PO_DEBUG.put_line(l_progress||': Opened cursor :'||'get_new_lines_to_be_processed');
808 END IF;
809 ELSE
810 OPEN get_all_document_lines;
811 IF g_po_pdoi_write_to_file ='Y' THEN
812 l_progress :='008';
813 PO_DEBUG.put_line(l_progress||': Opened cursor : get_all_document_lines');
814 END IF;
815 END IF;
816
817 -- If the create sourcing rule is set to 'Y' set the value of create_update_flag to CREATE.
818 -- The value of p_update_sourcing_rule can be 'Y' only if the value of p_create_sourcing_rule
819 -- is set to 'Y'. If the value of p_update_sourcing_rule is set to 'Y' then override the value
820 -- of l_create__update_flag and set it to 'CREATE_UPDATE'
821 IF p_create_sourcing_rule = 'Y' THEN
822 l_create_update_flag := 'CREATE';
823 END IF;
824
825 IF p_update_sourcing_rule = 'Y' THEN
826 l_create_update_flag := 'CREATE_UPDATE';
827 END IF;
828
829 LOOP
830
831 IF p_agreement_lines_selection='NEW' THEN
832 FETCH get_new_lines_to_be_processed
833 INTO l_po_line_id, l_start_date, l_end_date,
834 l_item_id, l_approved_flag;
835 EXIT WHEN get_new_lines_to_be_processed%NOTFOUND;
836 ELSE
837 FETCH get_all_document_lines
838 INTO l_po_line_id, l_start_date, l_end_date,
839 l_item_id, l_approved_flag;
840 EXIT WHEN get_all_document_lines%NOTFOUND;
841 END IF;
842
843 l_interface_line_id:=l_po_line_id;
844 l_header_processable_flag :='Y';
845 create_sourcing_rules_asl
846 (
847 p_api_version => l_api_version,
848 p_init_msg_list => l_init_msg_list,
849 p_commit => l_commit,
850 x_return_status => l_return_status,
851 x_msg_count => l_msg_count,
852 x_msg_data => l_msg_data,
853 p_interface_header_id => l_interface_header_id,
854 p_interface_line_id => l_interface_line_id,
855 p_document_id => p_document_id,
856 p_po_line_id => l_po_line_id,
857 p_document_type => l_document_type_code,
858 p_approval_status => l_approved_flag,
859 p_vendor_id => p_vendor_id,
860 p_vendor_site_id => p_vendor_site_id,
861 p_inv_org_id => p_inv_org,
862 p_sourcing_level => p_sourcing_level,
863 p_item_id => l_item_id,
864 p_category_id => l_category_id,
865 p_rel_gen_method => p_release_gen_method,
866 p_rule_name => p_sourcing_rule_name,
867 p_rule_name_prefix => l_rule_prefix,
868 p_start_date => l_start_date,
869 p_end_date => l_end_date,
870 p_assignment_set_id => p_assignment_set_id,
871 p_create_update_code => l_create_update_flag,
872 p_interface_error_code => 'PO_DOCS_OPEN_INTERFACE',
873 x_header_processable_flag => l_header_processable_flag
874 );
875
876 x_return_status := l_return_status;
877
878 END LOOP;
879
880 FND_FILE.put_line(FND_FILE.LOG,'==>POASLGEN ends at ' || TO_CHAR(sysdate,'MM/DD/RR HH24:MI:SS'));
881 --Close the cursor after the processing has been done.
882 IF p_agreement_lines_selection='NEW' THEN
883 CLOSE get_new_lines_to_be_processed;
884 IF g_po_pdoi_write_to_file ='Y' THEN
885 l_progress :='009';
886 PO_DEBUG.put_line(l_progress||' : Closed cursor : get_new_lines_to_be_processed');
887 END IF;
888 ELSE
889 CLOSE get_all_document_lines;
890 IF g_po_pdoi_write_to_file ='Y' THEN
891 l_progress :='010';
892 PO_DEBUG.put_line(l_progress||' : Closed cursor : get_all_document_lines');
893 END IF;
894 END IF;
895
896 EXCEPTION
897 WHEN OTHERS THEN
898
899 FND_FILE.put_line(FND_FILE.LOG,'Error occured in '||' : '||g_pkg_name||l_procedure_name||SQLERRM||' : '||SQLCODE);
900
901
902 IF p_agreement_lines_selection='NEW' and get_new_lines_to_be_processed%isopen THEN
903 CLOSE get_new_lines_to_be_processed;
904 ELSIF get_all_document_lines%ISOPEN THEN
905 CLOSE get_all_document_lines;
906 END IF;
907
908 po_message_s.SQL_ERROR(
909 p_package => g_pkg_name
910 , p_routine => l_procedure_name
911 , p_location => l_progress
912 , p_sqlcode => SQLCODE
913 , p_sqlerrm => SQLERRM(SQLCODE)
914 );
915
916 END create_autosource_rules;
917 --<LOCAL SR/ASL PROJECT 11i11 END>
918
919 --<LOCAL SR/ASL PROJECT 11i11 START>
920 -------------------------------------------------------------------------------
921 --Start of Comments
922 --Name: create_sourcing_rules_asl
923 --Pre-reqs:
924 -- None
925 --Modifies:
926 -- n/a
927 --Locks:
928 -- n/a
929 --Function:
930 -- The procedure create_sourcing_rules_asl is a new procedure that is
931 -- called by the procedure CREATE_AUTOSOURCE_RULES. This takes all the
932 -- relevant parameters pertaining to a single document line and creates
933 -- the Sourcing Rules and ASL's for that line. This procedure calls the
934 -- two procedures PO_SOURCING_RULES_SV.create_updates_sourcing_rules for
935 -- creating the sourcing rule and procedure
936 -- PO_APPROVED_SUPPLIER_LIST_SV. create_po_asl_entries for creating the ASL's.
937 --Parameters:
938 --IN:
939 --p_api_version
940 -- Standard parameter for verifying the api version
941 --p_init_msg_list
942 -- The p_init_msg_list parameter allows API callers to request
943 -- that the API does the initialization of the message list on
944 -- their behalf.
945 --p_commit
946 -- Standard parameter which dictates whether or not data should
947 -- be commited in the api
948 --p_validation_level
949 -- The p_validation_level parameter to determine which validation
950 -- steps should be executed and which steps should be skipped
951 --p_interface_header_id
952 -- sequence generated unique identifier of interface headers table. Used for
953 -- insertion into po_interface_errors
954 --p_interface_line_id
955 -- sequence generated unique identifier of interface line table. Used for
956 -- insertion into po_interface_errors
957 --p_document_id
958 -- unique identifier of the document for which the sourcing
959 -- rule is created or updated
960 --p_po_line_id
961 -- The line id of the document being processed.
962 --p_document_type
963 -- The type of document being created. Should be Blanket/GA
964 --p_approval_status
965 -- The approval status of the document for which the sourcing
966 -- rule is created or updated
967 --p_vendor_id
968 -- unique identifier of vendor of the document for which the sourcing
969 -- rule is created or updated
970 --p_vendor_site_id
971 -- unique identifier of vendor site enabled for the document for which
972 -- the sourcing rule is created or updated
973 --p_inv_org_id
974 -- Inventory organization_id for which the SR/ASL needs to be created.
975 --p_sourcing_level
976 -- Determines whether the Assignment of SR is done to the Assignment
977 -- Set at Item/Item Organization level
978 --p_item_id
979 -- Item for which the ASL needs to be created.
980 --p_category_id
981 -- If the ASL is being created for a Category this field needs to be populated.
982 --p_release_gen_method
983 -- This specifies what the release generation method would be :
984 -- CREATE, CREATE_AND_APPROVE, NONE
985 --p_rule_name
986 -- The name of Sourcing Rule being created/updated.
987 --p_rule_name_prefix
988 -- Prefix that will be used to create a name for new sourcing rule.
989 -- The name is p_rule_name_prefix_<SR Sequence number);
990 --p_start_date
991 -- The start date of Sourcing Rule
992 --p_end_date
993 -- The disable date of Sourcing Rule
994 --p_assignment_set_id
995 -- Assignment Set to which the Sourcing Rules would be assigned.
996 --p_assignment_type_id
997 -- Type of Assignment. Valid values are (1,3,4,5,6)
998 --p_create_update_code
999 -- Valid values are CREATE / CREATE_UPDATE
1000 --OUT:
1001 --x_return_status
1002 -- This indicates whether or not the api call was successful.
1003 -- Returns 'S' or 'E' for success or error respectively.
1004 --x_msg_count
1005 -- Holds the count for the number of messages in the message list.
1006 --x_msg_data
1007 -- If only one error is encountered message data holds the error message
1008 --x_Header_processable_flag
1009 -- Running parameter which decides whether to do further processing or error out
1010 -- Value is set to N if there was any error encountered.
1011 --Testing:
1012 --
1013 --
1014 --End of Comments
1015 -------------------------------------------------------------------------------
1016
1017 /*
1018 Assignment Type => Assignment Type ID Mapping
1019
1020 Assignment Type Assignment Type Id
1021 --------------------------------------------
1022 --------------------------------------------
1023 Global => 1
1024 Item => 3
1025 Organization => 4
1026 Category-Org => 5
1027 Item-Organization => 6
1028
1029 */
1030
1031 PROCEDURE CREATE_SOURCING_RULES_ASL
1032 (
1033 p_api_version IN NUMBER,
1034 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
1035 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2,
1039 p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%type,
1040 p_interface_line_id IN PO_LINES_INTERFACE.interface_line_id%type,
1041 p_document_id IN PO_HEADERS.po_header_id%type,
1042 p_po_line_id IN PO_LINES.po_line_id%type,
1043 p_document_type IN PO_HEADERS.type_lookup_code%type,
1044 p_approval_status IN VARCHAR2,
1045 p_vendor_id IN PO_HEADERS.vendor_id%type,
1046 p_vendor_site_id IN PO_HEADERS.vendor_site_id%type,
1047 p_inv_org_id IN HR_ALL_ORGANIZATION_UNITS.organization_id%type,
1048 p_sourcing_level IN VARCHAR2,
1049 p_item_id IN MTL_SYSTEM_ITEMS.inventory_item_id%type,
1050 p_category_id IN MTL_ITEM_CATEGORIES.category_id%type,
1051 p_rel_gen_method IN PO_ASL_ATTRIBUTES.release_generation_method%type,
1052 p_rule_name IN MRP_SOURCING_RULES.sourcing_rule_name%type,
1053 p_rule_name_prefix IN VARCHAR2,
1054 p_start_date IN DATE,
1055 p_end_date IN DATE,
1056 p_assignment_set_id IN MRP_ASSIGNMENT_SETS.assignment_set_id%type,
1057 p_create_update_code IN VARCHAR2,
1058 p_interface_error_code IN VARCHAR2,
1059 x_header_processable_flag IN OUT NOCOPY VARCHAR2
1060 )IS
1061 l_document_id PO_HEADERS_ALL.po_header_id%type;
1062 l_ga_flag PO_HEADERS_ALL.global_agreement_flag%type;
1063 l_vendor_site_id PO_HEADERS_ALL.vendor_site_id%type;
1064 l_assignment_type_id NUMBER;
1065
1066 /* Package specific information. Required for logging */
1067 l_procedure_name varchar2(50):='create_sourcing_rules_asl : ';
1068 l_progress VARCHAR2(3);
1069
1070 -- SQL What: Querying for all vendor sites enabled for the GA.
1071 -- SQL Why: Need to create sourcing rules/asl's for all vendor sites
1072 -- retrieved from the cursor.
1073
1074 CURSOR c_enabled_sites is
1075 select vendor_site_id
1076 from po_ga_org_assignments
1077 where po_header_id = p_document_id
1078 and enabled_flag = 'Y';
1079
1080 BEGIN
1081
1082 IF g_po_pdoi_write_to_file ='Y' THEN
1083 l_progress :='000';
1084 PO_DEBUG.put_line(l_progress||'Calling procedure create_update_sourcing_rules ');
1085 END IF;
1086 --Setting the assignment type depending on the value of sourcing_level
1087 IF nvl(p_sourcing_level,'ITEM')='ITEM' THEN
1088 l_assignment_type_id:=3;
1089 ELSIF p_sourcing_level='ITEM-ORGANIZATION' THEN
1090 l_assignment_type_id:=6;
1091 END IF;
1092
1093 PO_SOURCING_RULES_SV.create_update_sourcing_rule(
1094 p_interface_header_id => p_interface_header_id,
1095 p_interface_line_id => p_interface_line_id,
1096 p_item_id => p_item_id,
1097 p_vendor_id => p_vendor_id,
1098 p_po_header_id => p_document_id,
1099 p_po_line_id => p_po_line_id,
1100 p_document_type => p_document_type,
1101 p_approval_status => p_approval_status,
1102 p_rule_name => p_rule_name,
1103 p_rule_name_prefix => p_rule_name_prefix,
1104 p_start_date => p_start_date,
1105 p_end_date => p_end_date,
1106 p_create_update_code => p_create_update_code,
1107 p_organization_id => p_inv_org_id,
1108 p_assignment_type_id => l_assignment_type_id,
1109 p_po_interface_error_code => p_interface_error_code,
1110 x_header_processable_flag => x_header_processable_flag,
1111 x_return_status => x_return_status,
1112 --<LOCAL SR/ASL PROJECT 11i11 START>
1113 p_assignment_set_id => p_assignment_set_id,
1114 p_vendor_site_id => p_vendor_site_id);
1115 --<LOCAL SR/ASL PROJECT 11i11 END>
1116 -- We should proceed with creation of ASL's even if sourcing rules fail. This is why we set
1117 -- the header processable flag to 'Y'
1118 x_header_processable_flag := 'Y';
1119
1120 IF (g_po_pdoi_write_to_file = 'Y') THEN
1121 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1122 PO_DEBUG.put_line('Sourcing Rules creation API returned successfully');
1123 ELSE
1124 PO_DEBUG.put_line('Sourcing Rules creation API returned failure');
1125 END IF;
1126 END IF;
1127
1128 -- SQL What: Querying for the value of Global Agreement Flag to
1129 -- check if the document is a GA.
1130 -- SQL Why: If the document is a GA then we would create the ASL
1131 -- for all vendor sites if p_vendor_site_id is null. i.e
1132 -- if value of vendor site id has been passed as null to
1133 -- the procedure create_sourcing_rules_asl.
1134
1135 IF p_document_id is not null THEN
1136 SELECT global_agreement_flag,vendor_site_id
1137 INTO l_ga_flag , l_vendor_site_id
1138 FROM po_headers_all
1139 WHERE po_header_id = p_document_id;
1140 END IF;
1141
1142 IF g_po_pdoi_write_to_file ='Y' THEN
1143 l_progress :='001';
1144 PO_DEBUG.put_line(l_progress||' : Global Agreement Flag : '||l_ga_flag);
1145 END IF;
1146
1147 IF (p_vendor_site_id IS NOT NULL)THEN
1148 l_vendor_site_id:=p_vendor_site_id;
1149 IF g_po_pdoi_write_to_file ='Y' THEN
1150 l_progress :='002';
1151 PO_DEBUG.put_line(l_progress||' : Value of p_vendor_site_id : '||to_char(p_vendor_site_id));
1152 END IF;
1153 END IF;
1154
1155 IF (nvl(l_ga_flag,'N') = 'Y' AND ( p_vendor_site_id IS NULL)) THEN
1156
1157 OPEN c_enabled_sites;
1158 LOOP
1159 FETCH c_enabled_sites INTO l_vendor_site_id;
1160 EXIT WHEN c_enabled_sites%NOTFOUND;
1161 PO_APPROVED_SUPPLIER_LIST_SV.create_po_asl_entries(
1162 x_interface_header_id => p_interface_header_id,
1163 X_interface_line_id => p_interface_line_id,
1164 X_item_id => p_item_id,
1165 X_category_id => p_category_id,
1166 X_po_header_id => p_document_id,
1167 X_po_line_id => p_po_line_id,
1168 X_document_type => p_document_type,
1169 x_vendor_site_id => l_vendor_site_id,
1170 X_rel_gen_method => p_rel_gen_method,
1171 X_asl_org_id => p_inv_org_id,
1172 X_header_processable_flag => x_header_processable_flag,
1173 X_po_interface_error_code => p_interface_error_code,
1174 --<LOCAL SR/ASL PROJECT 11i11 START>
1175 p_sourcing_level => p_sourcing_level
1176 --<LOCAL SR/ASL PROJECT 11i11 END>
1177 );
1178 END LOOP;
1179
1180 close c_enabled_sites;
1181
1182 ELSE -- If not a global agreement you do not need to loop
1183
1184 PO_APPROVED_SUPPLIER_LIST_SV.create_po_asl_entries(
1185 x_interface_header_id => p_interface_header_id,
1186 X_interface_line_id => p_interface_line_id,
1187 X_item_id => p_item_id,
1188 X_category_id => p_category_id,
1189 X_po_header_id => p_document_id,
1190 X_po_line_id => p_po_line_id,
1191 X_document_type => p_document_type,
1192 x_vendor_site_id => l_vendor_site_id,
1193 X_rel_gen_method => p_rel_gen_method,
1194 X_asl_org_id => p_inv_org_id,
1195 X_header_processable_flag => x_header_processable_flag,
1196 X_po_interface_error_code => p_interface_error_code,
1197 --<LOCAL SR/ASL PROJECT 11i11 START>
1198 p_sourcing_level => p_sourcing_level
1199 --<LOCAL SR/ASL PROJECT 11i11 END>
1200 );
1201 END IF;
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 x_header_processable_flag := 'N';
1205 FND_FILE.put_line(FND_FILE.LOG,'Error occured in '||' : '||g_pkg_name||l_procedure_name||SQLERRM||' : '||SQLCODE);
1206 IF (g_po_pdoi_write_to_file = 'Y') THEN
1207 PO_DEBUG.put_line('Sourcing Rules/ASL creation failed');
1208 END IF;
1209 po_message_s.SQL_ERROR(
1210 p_package => g_pkg_name
1211 , p_routine => l_procedure_name
1212 , p_location => l_progress
1213 , p_sqlcode => SQLCODE
1214 , p_sqlerrm => SQLERRM(SQLCODE)
1215 );
1216 IF(c_enabled_sites%ISOPEN)THEN
1217 CLOSE c_enabled_sites;
1218 END IF;
1219 END CREATE_SOURCING_RULES_ASL;
1220 --<LOCAL SR/ASL PROJECT 11i11 END>
1221 END PO_CREATE_SR_ASL;