[Home] [Help]
PACKAGE BODY: APPS.PO_AUTO_IMPLEMENT_PVT
Source
1 PACKAGE BODY PO_AUTO_IMPLEMENT_PVT AS
2 /* $Header: PO_AUTO_IMPLEMENT_PVT.plb 120.5 2011/04/15 07:07:43 agalande ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(1000) := 'PO_AUTO_IMPLEMENT_PVT';
6 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.PO_AUTO_IMPLEMENT_PVT.';
7
8 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
9 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
10
11
12 x_org_id PO_HEADERS_INTERFACE.org_id%TYPE;
13
14
15
16 /* -----------------------------------------------------
17 ---------------- PRIVATE PROCEDURES -----------------
18 ----------------------------------------------------- */
19
20 /* ============================================================================
21 NAME: populate_interface
22 DESC: Populate the interface table with Header and Line details from Requisition.
23
24 NOTE:
25 ============================================================================ */
26
27
28 PROCEDURE populate_interface(po_doc_id IN NUMBER, x_req_line_id IN NUMBER)
29 IS
30
31 l_api_name VARCHAR2(30) := 'populate_interface';
32 l_progress VARCHAR2(3) := '000';
33
34 x_info_slin_falg VARCHAR2(1) := 'N';
35
36 x_group_line_id PO_REQUISITION_LINES_ALL.group_line_id%TYPE;
37 x_po_line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;
38 x_po_shipment_num PO_LINE_LOCATIONS_ALL.shipment_num%TYPE;
39 x_po_line_num PO_LINES_ALL.line_num%TYPE;
40
41 -- Headers Interface
42 x_interface_header_id PO_HEADERS_INTERFACE.interface_header_id%TYPE;
43 x_document_num PO_HEADERS_INTERFACE.document_num%TYPE;
44 x_document_type PO_HEADERS_INTERFACE.document_type_code%TYPE;
45 x_document_subtype PO_HEADERS_INTERFACE.document_subtype%TYPE;
46 x_vendor_id PO_HEADERS_INTERFACE.vendor_id%TYPE;
47 x_agent_id PO_HEADERS_INTERFACE.agent_id%TYPE;
48 x_vendor_site_id PO_HEADERS_INTERFACE.vendor_site_id%TYPE;
49 x_currency_code PO_HEADERS_INTERFACE.currency_code%TYPE := null;
50 x_rate_type PO_HEADERS_INTERFACE.rate_type_code%TYPE := null;
51 x_rate_date PO_HEADERS_INTERFACE.rate_date%TYPE := null;
52 x_rate PO_HEADERS_INTERFACE.rate%TYPE := null;
53 x_style_id PO_HEADERS_INTERFACE.style_id%TYPE := null;
54 x_pcard_id PO_HEADERS_INTERFACE.pcard_id%TYPE := null;
55
56 -- Lines Interface
57 x_interface_line_id PO_LINES_INTERFACE.interface_line_id%TYPE;
58 x_clm_option_exercised PO_REQUISITION_LINES_ALL.clm_option_exercised%TYPE; -- Bug 9838483
59
60 BEGIN
61
62 IF g_debug_stmt THEN
63 PO_DEBUG.debug_begin(p_log_head => g_log_head||'l_api_name');
64 END IF;
65
66 /* Populate Header - starts here */
67
68 SELECT pha.type_lookup_code,
69 pha.segment1,
70 pha.agent_id,
71 pha.vendor_id,
72 pha.vendor_site_id,
73 pha.currency_code,
74 pha.rate_type,
75 pha.rate_date,
76 pha.rate,
77 pha.org_id,
78 pha.style_id,
79 pha.pcard_id
80 INTO x_document_subtype,
81 x_document_num,
82 x_agent_id,
83 x_vendor_id,
84 x_vendor_site_id,
85 x_currency_code,
86 x_rate_type,
87 x_rate_date,
88 x_rate,
89 x_org_id,
90 x_style_id,
91 x_pcard_id
92 FROM po_headers_all pha
93 WHERE pha.po_header_id = po_doc_id;
94
95 l_progress:= '010';
96
97 /* Figure out what type of doc we are auotimplementing */
98 IF x_document_subtype = 'STANDARD' OR x_document_subtype = 'PLANNED' THEN
99 x_document_type :='PO';
100 ELSIF x_document_subtype = 'BLANKET' THEN
101 x_document_type :='PA';
102 END IF;
103
104 l_progress:= '020';
105 SELECT po_headers_interface_s.nextval
106 INTO PO_AUTOCREATE_PARAMS.x_interface_header_id
107 FROM dual;
108
109 IF g_debug_stmt THEN
110 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
111 p_token => l_progress,
112 p_message => 'After getting interface header id :'||PO_AUTOCREATE_PARAMS.x_interface_header_id);
113 END IF;
114
115 l_progress:= '030';
116 BEGIN
117
118 INSERT INTO po_headers_interface
119 (interface_header_id,
120 interface_source_code,
121 batch_id,
122 process_code, /* This used to be process_flag */
123 action, /* This used to be action_type_code */
124 document_type_code,
125 document_subtype,
126 document_num,
127 group_code,
128 vendor_id,
129 vendor_site_id,
130 release_num,
131 release_date,
132 agent_id,
133 currency_code,
134 rate_type_code, /* This used to be rate_type */
135 rate_date,
136 rate,
137 vendor_list_header_id,
138 -- quote_type_lookup_code, <-- no longer in headers_interface
139 -- quotation_class_code, <-- no longer in headers_interface
140 --DPCARD{
141 pcard_id,
142 --DPCARD}
143 creation_date,
144 created_by,
145 last_update_date,
146 last_updated_by,
147 org_id, --<R12 MOAC>
148 style_id) --<R12 STYLES PHASE II >
149 VALUES (PO_AUTOCREATE_PARAMS.x_interface_header_id,
150 'PO',
151 PO_AUTOCREATE_PARAMS.x_interface_header_id,
152 'ADD',
153 'ADD',
154 x_document_type,
155 x_document_subtype,
156 x_document_num,
157 'REQUISITION',
158 x_vendor_id,
159 x_vendor_site_id,
160 NULL,
161 NULL,
162 x_agent_id,
163 x_currency_code,
164 x_rate_type,
165 x_rate_date,
166 x_rate,
167 NULL,
168 --DPCARD{
169 x_pcard_id,
170 --DPCARD}
171 sysdate,
172 FND_GLOBAL.user_id,
173 sysdate,
174 FND_GLOBAL.user_id,
175 x_org_id,
176 x_style_id);
177
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 IF g_debug_unexp THEN
182 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
183 p_progress => l_progress);
184 END IF;
188
185 PO_AUTOCREATE_PVT.wrapup;
186 RAISE;
187 END;
189 l_progress:= '040';
190 IF g_debug_stmt THEN
191 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
192 p_token => l_progress,
193 p_message => 'After inserting records into po_headers_interface: interface_header_id : '||PO_AUTOCREATE_PARAMS.x_interface_header_id);
194 END IF;
195
196 /* Populate Header - ends here */
197
198
199
200 /* Populate Lines - starts here */
201 l_progress:= '050';
202
203 BEGIN
204
205 -- Check if the Line is Info SLIN or not.
206 SELECT group_line_id,
207 clm_info_flag
208 INTO x_group_line_id,
209 x_info_slin_falg
210 FROM po_requisition_lines_all prl
211 WHERE prl.requisition_line_id=x_req_line_id
212 AND prl.group_line_id IS NOT NULL;
213
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 x_info_slin_falg := 'N';
218 END;
219
220 BEGIN
221
222 -- Bug 9838483: Retrieve clm_option_exercised from requisition lines
223 SELECT clm_option_exercised
224 INTO x_clm_option_exercised
225 FROM po_requisition_lines_all prl
226 WHERE prl.requisition_line_id=x_req_line_id;
227
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 x_clm_option_exercised := null;
232 END;
233
234 l_progress:= '060';
235
236 BEGIN
237 IF x_info_slin_falg = 'N' THEN
238
239 SELECT line_location_id
240 INTO x_po_line_location_id
241 FROM po_requisition_lines_all prl
242 WHERE prl.requisition_line_id = x_req_line_id;
243
244 ELSE
245 SELECT line_location_id
246 INTO x_po_line_location_id
247 FROM po_requisition_lines_all prl
248 WHERE prl.requisition_line_id = x_group_line_id;
249 END IF;
250
251 EXCEPTION
252 WHEN No_Data_Found THEN
253 IF g_debug_unexp THEN
254 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
255 p_progress => l_progress);
256 END IF;
257 x_po_line_location_id := NULL;
258 PO_AUTOCREATE_PVT.wrapup;
259 RAISE;
260 END;
261
262 l_progress:= '070';
263
264 SELECT pla.line_num,
265 pll.shipment_num
266 INTO x_po_line_num,
267 x_po_shipment_num
268 FROM po_line_locations_all pll,
269 po_lines_all pla
270 WHERE pla.po_line_id = pll.po_line_id
271 AND pll.line_location_id = x_po_line_location_id;
272
273
274 l_progress:= '080';
275 IF g_debug_stmt THEN
276 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
277 p_token => l_progress,
278 p_message => 'After getting PO details : line_num : '||x_po_line_num||': shipment_num :'||x_po_shipment_num);
279 END IF;
280
281
282
283 BEGIN
284
285 IF x_info_slin_falg = 'Y' THEN
286
287 -- Populate the CLIN line details
288 l_progress:= '090';
289
290 /* Get the next available interface_line_id */
291
292 SELECT po_lines_interface_s.nextval
293 INTO x_interface_line_id
294 FROM dual;
295
296
297 INSERT INTO po_lines_interface
298 (interface_header_id,
299 interface_line_id,
300 action,
301 line_num,
302 shipment_num,
303 requisition_line_id,
304 quantity,
305 amount,
306 creation_date,
307 created_by,
308 last_update_date,
309 last_updated_by)
310 VALUES(PO_AUTOCREATE_PARAMS.x_interface_header_id,
311 x_interface_line_id,
312 NULL,
313 x_po_line_num,
314 x_po_shipment_num,
315 x_group_line_id,
316 0,
317 0,
318 sysdate,
319 FND_GLOBAL.user_id,
320 sysdate,
321 FND_GLOBAL.user_id);
322
323
324 -- Populate the info line details
325 l_progress:= '100';
326 /* Get the next available interface_line_id */
327
328 SELECT po_lines_interface_s.nextval
329 INTO x_interface_line_id
330 FROM dual;
331
332
333 INSERT INTO po_lines_interface
334 (interface_header_id,
335 interface_line_id,
336 action,
337 requisition_line_id,
338 creation_date,
339 created_by,
340 last_update_date,
341 last_updated_by)
342 VALUES(PO_AUTOCREATE_PARAMS.x_interface_header_id,
343 x_interface_line_id,
344 NULL,
345 x_req_line_id,
346 sysdate,
347 FND_GLOBAL.user_id,
348 sysdate,
349 FND_GLOBAL.user_id);
350
351
352 ELSE -- x_info_slin_falg = 'N'
353 l_progress:= '110';
354 /* Get the next available interface_line_id */
355
356 SELECT po_lines_interface_s.nextval
357 INTO x_interface_line_id
358 FROM dual;
359
360
361 INSERT INTO po_lines_interface
362 (interface_header_id,
363 interface_line_id,
364 action,
365 line_num,
366 shipment_num,
367 requisition_line_id,
368 quantity,
369 amount,
370 creation_date,
371 created_by,
372 last_update_date,
373 last_updated_by,
374 clm_exercised_flag, -- Bug 9838483
375 clm_exercised_date) -- Bug 11890703
376 VALUES(PO_AUTOCREATE_PARAMS.x_interface_header_id,
377 x_interface_line_id,
378 NULL,
379 x_po_line_num,
380 x_po_shipment_num,
381 x_req_line_id,
382 0,
383 0,
384 sysdate,
385 FND_GLOBAL.user_id,
386 sysdate,
387 FND_GLOBAL.user_id,
388 x_clm_option_exercised, -- Bug 9838483
389 Decode(x_clm_option_exercised, 'Y', SYSDATE, NULL)); -- Bug 11890703
390
391 END IF; -- x_info_slin_falg = 'Y'
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 IF g_debug_unexp THEN
396 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
397 p_progress => l_progress);
398 END IF;
399 PO_AUTOCREATE_PVT.wrapup;
400 RAISE;
401 END;
402
403 l_progress:= '120';
404
405 /* Populate Lines - ends here */
406
407 IF g_debug_stmt THEN
408 PO_DEBUG.debug_end(p_log_head => g_log_head||'l_api_name');
409 END IF;
410
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 PO_AUTOCREATE_PVT.wrapup;
415 IF g_debug_unexp THEN
416 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
417 p_progress => l_progress);
418 END IF;
419 po_message_s.sql_error('populate_interface',l_progress,sqlcode);
420 raise;
421
422 END populate_interface;
423
424
425
426
427 /* -----------------------------------------------------
428 ---------------- PUBLIC PROCEDURES -----------------
429 ----------------------------------------------------- */
430
431 /* ============================================================================
432 NAME: process
433 DESC: Main Procedure for the PR AutoImplement which does the following
434 - populate the interface table
435 - call AutoCreate api to add new info SLIN with funding and distribution
436
437 Caller : POR_AMENDMENT_PKG.implement_dist_auto
438 ==============================================================================*/
439
440 PROCEDURE process(x_req_line_id IN NUMBER)
441 IS
442
443 l_api_name VARCHAR2(30) := 'process';
444 l_progress VARCHAR2(3) := '000';
445
446 x_info_slin_falg VARCHAR2(1):='N';
447
448 x_group_line_id PO_REQUISITION_LINES_ALL.group_line_id%TYPE;
449 x_po_line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;
450 x_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
451 x_req_operating_unit_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
452
453
454 BEGIN
455
456 IF g_debug_stmt THEN
457 PO_DEBUG.debug_begin(p_log_head => g_log_head||'l_api_name');
458 END IF;
459
460 l_progress:= '010';
461
462 -- Check if the Line is Info SLIN or not.
463 BEGIN
464 SELECT group_line_id,
465 clm_info_flag,
466 org_id
467 INTO x_group_line_id,
468 x_info_slin_falg,
469 x_req_operating_unit_id
470 FROM po_requisition_lines_all prl
471 WHERE prl.requisition_line_id=x_req_line_id
472 AND prl.group_line_id IS NOT NULL;
473
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 x_info_slin_falg := 'N';
478 END;
479
480 l_progress:= '020';
481
482 BEGIN
483 IF x_info_slin_falg = 'N' THEN
484
485 SELECT line_location_id
486 INTO x_po_line_location_id
487 FROM po_requisition_lines_all prl
488 WHERE prl.requisition_line_id = x_req_line_id;
489
490 ELSE
491 SELECT line_location_id
492 INTO x_po_line_location_id
493 FROM po_requisition_lines_all prl
494 WHERE prl.requisition_line_id = x_group_line_id;
495 END IF;
496
497 EXCEPTION
498 WHEN No_Data_Found THEN
499 IF g_debug_unexp THEN
500 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
501 p_progress => l_progress);
502 END IF;
503 x_po_line_location_id := NULL;
504 PO_AUTOCREATE_PVT.wrapup;
505 RAISE;
506 END;
507
508 l_progress:= '030';
509
510
511 SELECT pha.po_header_id
512 INTO x_po_header_id
513 FROM po_line_locations_all pll,
514 po_headers_all pha
515 WHERE pha.po_header_id = pll.po_header_id
516 AND pll.line_location_id = x_po_line_location_id;
517
518
519 l_progress:= '040';
520 IF g_debug_stmt THEN
521 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
522 p_token => l_progress,
523 p_message => 'After getting PO details : po_header_id : '||x_po_header_id);
524 END IF;
525
526
527 -- Populate the interface table with Header and Line details
528 populate_interface(x_po_header_id, x_req_line_id);
529
530 l_progress:= '050';
531 IF g_debug_stmt THEN
532 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
533 p_token => l_progress,
534 p_message => 'After populating the interface tables');
535 END IF;
536
537 -- Call the AutoCreate API to add the new Info Line or Distribution
538 PO_AUTOCREATE_PARAMS.g_is_clm_po := 'Y';
539 PO_AUTOCREATE_PVT.create_po(PO_AUTOCREATE_PARAMS.x_interface_header_id
540 ,x_po_header_id
541 ,NULL
542 ,'N'
543 ,'AUTOCREATE'
544 ,x_req_operating_unit_id
545 ,x_org_id
546 ,x_org_id
547 );
548
549 l_progress:= '060';
550 IF g_debug_stmt THEN
551 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
552 p_token => l_progress,
553 p_message => 'The AutoCreate returns successfully');
554 END IF;
555
556
557 IF g_debug_stmt THEN
558 PO_DEBUG.debug_end(p_log_head => g_log_head||'l_api_name');
559 END IF;
560
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 PO_AUTOCREATE_PVT.wrapup;
565 IF g_debug_unexp THEN
566 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
567 p_progress => l_progress);
568 END IF;
569 po_message_s.sql_error('process',l_progress,sqlcode);
570 raise;
571 END process;
572
573 END PO_AUTO_IMPLEMENT_PVT;