[Home] [Help]
PACKAGE BODY: APPS.GML_PO_CON_REQ
Source
1 PACKAGE BODY GML_PO_CON_REQ AS
2 /* $Header: GMLPORCB.pls 115.13 2002/12/04 23:30:45 uphadtar ship $ */
3
4 inserted_ind VARCHAR2(1) := NULL;
5
6 /*############################################################################
7 # PROC
8 # fire_request
9 #
10 # GMLPORCO.pls Concurrent Program for Synchronizing POs RCPT.
11 #
12 # DESCRIPTION
13 #
14 # This Procedure fires the Concurrent Request to Synchronize the
15 # Purchase Orders (Standard and Planned), Releases from Oralce to GEMMS
16 # and Receipts from GEMMS to Oracle.
17 #
18 # This Executable Fired with the FND_SUBMIT_REQUEST is a SQL File which
19 # opens the Common Purchasing Log File in the directory named in the
20 # init.ora and displays the log file in the Standard Concurent Manager
21 # request log.
22 #
23 # MODIFICATION HISTORY
24 #
25 # 29-JAN-99 Tony Ricci
26 #
27 ########################################################################### */
28 PROCEDURE fire_request IS
29
30 v_request_id NUMBER ;
31 dummy BOOLEAN ;
32
33 v_call_status BOOLEAN ;
34 v_request_phase VARCHAR2(30) ;
35 v_request_status VARCHAR2(30) ;
36 v_dev_phase VARCHAR2(30) ;
37 v_dev_status VARCHAR2(30) ;
38 v_line VARCHAR2(80) ;
39 v_message VARCHAR2(240);
40
41 err_num NUMBER;
42 tmp_num NUMBER;
43 Err_Msg VARCHAR2(100);
44
45 BEGIN
46
47 dummy := fnd_request.set_mode(TRUE);
48
49
50 v_call_status := fnd_concurrent.get_request_status
51 (v_request_id,
52 'GML',
53 'GMLPORCV',
54 v_request_phase,
55 v_request_status,
56 v_dev_phase,
57 v_dev_status,
58 v_message);
59
60 /* Fire the Concurrent Request, Only if the Previous one is Completed. If,
61 the program, fired earlier is Pending or Running State, do not start a
62 fresh request, since the current request will pick up the current set of
63 rows also from the Interface Table */
64
65 IF ((v_dev_phase = 'PENDING') OR (v_dev_phase = 'RUNNING')) AND
66 (v_dev_status = 'NORMAL') THEN
67 tmp_num := 1;
68 ELSIF (v_dev_phase = 'INACTIVE' AND v_dev_status = 'NO_MANAGER') THEN
69 tmp_num := 2;
70 ELSE
71
72 v_request_id := fnd_request.submit_request(
73 'GML', /* Application name*/
74 'GMLPORCV', /* Program Name*/
75 'OPM Common Purchasing Synchronization', /*Description*/
76 '', /* Start Date*/
77 FALSE, /* Not called from another */
78 /* concurrent request*/
79 CHR(0), '', '','','','','','','','',
80 '','','','','','','','','','',
81 '','','','','','','','','','',
82 '','','','','','','','','','',
83 '','','','','','','','','','',
84 '','','','','','','','','','',
85 '','','','','','','','','','',
86 '','','','','','','','','','',
87 '','','','','','','','','','',
88 '','','','','','','','','',''
89 );
90
91
92 IF (v_request_id = 0) THEN
93
94 fnd_message.set_name('FND', 'CONC-MENU-MANAGERS');
95 fnd_message.set_token('Managers', 'Cannot Fire Concurrent Request...');
96 app_exception.raise_exception;
97
98 END IF; /* v_request_id = 0 */
99 END IF; /* v_request_id = 0 */
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 err_num := SQLCODE;
104 Err_Msg := SUBSTRB(SQLERRM, 1, 100);
105 RAISE_APPLICATION_ERROR(-20000, Err_Msg);
106 END fire_request;
107
108 /*===========================================================================
109 | |
110 | PROCEDURE NAME po_resub_insert |
111 | |
112 | DESCRIPTION This private procedure will call procedure to |
113 | insert rows in cpg_purchasing_interface table |
114 | |
115 | |
116 | MODIFICATION HISTORY |
117 | Uday Phadtare new procedure created for Bug2048971. |
118 ============================================================================*/
119 PROCEDURE po_resub_insert (v_po_header_id IN NUMBER,
120 v_po_line_id IN NUMBER,
121 v_po_line_location_id IN NUMBER,
122 v_po_release_id IN NUMBER,
123 v_transaction_type IN VARCHAR2) IS
124
125 CURSOR get_failed_releases (v_po_header_id NUMBER, v_po_line_id NUMBER,
126 v_po_line_location_id NUMBER, v_po_release_id NUMBER) IS
127 SELECT po_release_id, invalid_ind
128 FROM cpg_purchasing_interface
129 WHERE po_header_id = v_po_header_id
130 AND po_line_id = v_po_line_id
131 AND po_line_location_id = v_po_line_location_id
132 AND po_release_id = v_po_release_id
133 AND release_num <> 0
134 ORDER BY transaction_id DESC;
135
136 CURSOR get_failed_release_details (v_po_header_id NUMBER, v_release_id NUMBER)
137 IS
138 SELECT po_header_id, po_line_id, line_location_id, ship_to_location_id
139 FROM po_line_locations_all
140 WHERE po_header_id = v_po_header_id
141 AND po_release_id = v_release_id
142 ORDER BY po_header_id, po_line_id,line_location_id;
143
144
145 CURSOR shipping_details(v_po_header_id NUMBER) IS
146 SELECT po_header_id, po_line_id, line_location_id, ship_to_location_id
147 FROM po_line_locations_all
148 WHERE po_header_id = v_po_header_id
149 AND approved_flag ='Y'
150 ORDER BY po_header_id, po_line_id, line_location_id;
151
152 get_failed_releases_rec get_failed_releases%ROWTYPE;
153 fetch_failed NUMBER := 0;
154 err_num NUMBER;
155 Err_Msg VARCHAR2(1000);
156
157 BEGIN
158
159 IF v_transaction_type IN('STANDARD') THEN
160
161 FOR shipping_details_rec IN shipping_details(v_po_header_id)
162 LOOP
163 Gml_Po_Interface.insert_rec( shipping_details_rec.po_header_id,
164 shipping_details_rec.po_line_id,
165 shipping_details_rec.line_location_id,
166 NULL, NULL, NULL,
167 NULL, NULL, NULL,
168 NULL, NULL, 'N',
169 NULL, shipping_details_rec.ship_to_location_id, NULL);
170
171 inserted_ind := 'Y';
172 END LOOP;
173 ELSE
174
175 OPEN get_failed_releases(v_po_header_id,v_po_line_id,v_po_line_location_id,v_po_release_id);
176 FETCH get_failed_releases INTO get_failed_releases_rec;
177
178 IF get_failed_releases%NOTFOUND THEN
179 fetch_failed := 1;
180 END IF;
181
182 CLOSE get_failed_releases;
183
184 IF (fetch_failed = 1 OR get_failed_releases_rec.invalid_ind = 'Y') THEN
185 FOR get_failed_release_details_rec IN get_failed_release_details(v_po_header_id, v_po_release_id)
186 LOOP
187
188 Gml_Po_Interface.insert_rec( get_failed_release_details_rec.po_header_id,
189 get_failed_release_details_rec.po_line_id,
190 get_failed_release_details_rec.line_location_id,
191 NULL, NULL, NULL,
192 NULL, NULL, NULL,
193 NULL, NULL, 'N',
194 NULL, get_failed_release_details_rec.ship_to_location_id, NULL);
195
196 inserted_ind := 'Y';
197 fetch_failed := 0;
198
199 END LOOP;
200 END IF;
201 END IF;
202
203 EXCEPTION
204
205 WHEN OTHERS THEN
206 err_num := SQLCODE;
207 Err_Msg := SUBSTRB(SQLERRM, 1, 1000);
208 RAISE_APPLICATION_ERROR(-20099, Err_Msg);
209
210 END po_resub_insert;
211
212 /*===========================================================================
213 | |
214 | PROCEDURE NAME po_resub |
215 | |
216 | DESCRIPTION Resubmission procedure which resubmits the set of |
217 | PO's which fall BETWEEN the given dates OR OF PO |
218 | NUMBER given |
219 | |
220 | MODIFICATION HISTORY |
221 | |
222 | 30-JAN-99 Tony Ricci |
223 | |
224 | 11/10/99 - BUG#:1030064 - Allow USER TO RUN report without making PO |
225 | required. Retrieve po_header_id AND COMMENT |
226 | OUT converting from_date AND TO_DATE TO_DATE IN |
227 | second LOOP. |
228 | 15-NOV-2001 Bug#2048971 Uday Phadtare Entire procedure was rewritten |
229 | For standard PO whole PO will be synched again. |
230 | For Planned PO only failed releases will be synched. |
231 | For Blanket PO only failed releases will be synched. |
232 | PPO template does not get synched through po_resub. It has |
233 | to be exclusively synched through PO form. |
234 ============================================================================*/
235
236
237 PROCEDURE po_resub
238 (errbuf OUT NOCOPY VARCHAR2,
239 retcode OUT NOCOPY NUMBER,
240 v_from_date IN OUT NOCOPY VARCHAR2,
241 v_to_date IN OUT NOCOPY VARCHAR2,
242 v_po_no IN VARCHAR2)
243 IS
244
245 v_po_header_id cpg_purchasing_interface.po_header_id%TYPE := NULL;
246 v_po_line_id cpg_purchasing_interface.po_line_id%TYPE := NULL;
247 v_line_location_id cpg_purchasing_interface.po_line_location_id%TYPE := NULL;
248 v_transaction_id cpg_purchasing_interface.transaction_id%TYPE := NULL;
249 v_po_release_id cpg_purchasing_interface.po_release_id%TYPE := NULL;
250 v_transaction_type cpg_purchasing_interface.transaction_type%TYPE := NULL;
251
252 CURSOR get_synch_failed_rows_DT (v_date_from DATE, v_date_to DATE) IS
253 SELECT po_header_id,
254 po_line_id,
255 line_location_id,
256 po_release_id
257 FROM po_line_locations_all
258 WHERE (creation_date >= v_date_from AND creation_date < v_date_to + 1 )
259 ORDER BY po_header_id,po_release_id,po_line_id,line_location_id;
260
261 CURSOR get_transaction_type(v_po_head_id NUMBER) IS
262 SELECT type_lookup_code
263 FROM po_headers_all
264 WHERE po_header_id = v_po_head_id;
265
266 CURSOR get_trans_type(v_po_no VARCHAR2) IS
267 SELECT type_lookup_code, po_header_id
268 FROM po_headers_all
269 WHERE segment1 = v_po_no;
270
271 CURSOR get_synch_failed_rows_PO (v_po_header_id VARCHAR2) IS
272 SELECT po_header_id,
273 po_line_id,
274 line_location_id,
275 po_release_id
276 FROM po_line_locations_all
277 WHERE po_header_id = v_po_header_id
278 ORDER BY po_header_id,po_release_id,po_line_id,line_location_id;
279
280 get_synch_failed_rows_PO_rec get_synch_failed_rows_PO%ROWTYPE;
281 get_trans_type_rec get_trans_type%ROWTYPE;
282 err_num NUMBER;
283 Err_Msg VARCHAR2(1000);
284 date_format VARCHAR2(20) := 'DD-MON-YYYY';
285
286 BEGIN
287
288 v_from_date := TO_CHAR(TO_DATE(v_from_date, date_format),date_format) ;
289 v_to_date := TO_CHAR(TO_DATE(v_to_date, date_format),date_format) ;
290
291 IF (v_po_no IS NULL) THEN /* po number is not given */
292
293 FOR get_synch_failed_rows_DT_rec IN get_synch_failed_rows_DT(
294 NVL(TO_DATE(v_from_date,date_format),TO_DATE('01-01-1970','DD-MM-YYYY')),
295 NVL(TO_DATE(v_to_date,date_format),SYSDATE))
296 LOOP
297 IF get_synch_failed_rows_DT_rec.po_release_id IS NULL THEN
298
299 IF v_po_header_id IS NULL THEN
300
301 inserted_ind := 'N';
302 v_po_header_id := get_synch_failed_rows_DT_rec.po_header_id;
303 v_po_line_id := get_synch_failed_rows_DT_rec.po_line_id;
304 v_line_location_id:= get_synch_failed_rows_DT_rec.line_location_id;
305
306 OPEN get_transaction_type(v_po_header_id);
307 FETCH get_transaction_type INTO v_transaction_type;
308 CLOSE get_transaction_type;
309
310 po_resub_insert(v_po_header_id,v_po_line_id,v_line_location_id,NULL,v_transaction_type);
311
312 ELSE
313
314 IF get_synch_failed_rows_DT_rec.po_header_id = v_po_header_id AND inserted_ind = 'Y' THEN
315 NULL;
316 ELSE
317 inserted_ind := 'N';
318 v_po_header_id := get_synch_failed_rows_DT_rec.po_header_id;
319 v_po_line_id := get_synch_failed_rows_DT_rec.po_line_id;
320 v_line_location_id:= get_synch_failed_rows_DT_rec.line_location_id;
321
322 OPEN get_transaction_type(v_po_header_id);
323 FETCH get_transaction_type INTO v_transaction_type;
324 CLOSE get_transaction_type;
325
326 po_resub_insert(v_po_header_id,v_po_line_id,v_line_location_id,NULL,v_transaction_type);
327
328 END IF;
329 END IF;
330
331 ELSE
332
333 IF v_po_header_id IS NULL THEN
334 inserted_ind := 'N';
335 v_po_header_id := get_synch_failed_rows_DT_rec.po_header_id;
336 v_po_line_id := get_synch_failed_rows_DT_rec.po_line_id;
337 v_line_location_id:= get_synch_failed_rows_DT_rec.line_location_id;
338 v_po_release_id := get_synch_failed_rows_DT_rec.po_release_id;
339
340 OPEN get_transaction_type(v_po_header_id);
341 FETCH get_transaction_type INTO v_transaction_type;
342 CLOSE get_transaction_type;
343
344 po_resub_insert(v_po_header_id,v_po_line_id,v_line_location_id,v_po_release_id,v_transaction_type);
345
346 ELSE
347 IF get_synch_failed_rows_DT_rec.po_header_id = v_po_header_id AND
348 get_synch_failed_rows_DT_rec.po_release_id = NVL(v_po_release_id,0)
349 AND inserted_ind = 'Y' THEN
350 NULL;
351 ELSE
352 inserted_ind := 'N';
353 v_po_header_id := get_synch_failed_rows_DT_rec.po_header_id;
354 v_po_line_id := get_synch_failed_rows_DT_rec.po_line_id;
355 v_line_location_id:= get_synch_failed_rows_DT_rec.line_location_id;
356 v_po_release_id := get_synch_failed_rows_DT_rec.po_release_id;
357
358 OPEN get_transaction_type(v_po_header_id);
359 FETCH get_transaction_type INTO v_transaction_type;
360 CLOSE get_transaction_type;
361
362 po_resub_insert(v_po_header_id,v_po_line_id,v_line_location_id,v_po_release_id,v_transaction_type);
363 END IF;
364 END IF;
365 END IF;
366
367 END LOOP;
368
369 ELSE /* po number is given */
370
371 OPEN get_trans_type(v_po_no);
372 FETCH get_trans_type INTO get_trans_type_rec;
373 CLOSE get_trans_type;
374
375 IF get_trans_type_rec.type_lookup_code = 'STANDARD' THEN
376
377 OPEN get_synch_failed_rows_PO(get_trans_type_rec.po_header_id);
378 FETCH get_synch_failed_rows_PO INTO get_synch_failed_rows_PO_rec;
379 CLOSE get_synch_failed_rows_PO;
380
381 v_po_header_id := get_synch_failed_rows_PO_rec.po_header_id;
382 v_po_line_id := get_synch_failed_rows_PO_rec.po_line_id;
383 v_line_location_id:= get_synch_failed_rows_PO_rec.line_location_id;
384
385 po_resub_insert(v_po_header_id,v_po_line_id,v_line_location_id,NULL,get_trans_type_rec.type_lookup_code);
386
387 ELSE
388
389 FOR get_synch_failed_rows_PO_rec IN get_synch_failed_rows_PO(get_trans_type_rec.po_header_id)
390
391 LOOP
392
393 IF v_po_header_id IS NULL THEN
394
395 inserted_ind := 'N';
396 v_po_header_id := get_synch_failed_rows_PO_rec.po_header_id;
397 v_po_line_id := get_synch_failed_rows_PO_rec.po_line_id;
398 v_line_location_id:= get_synch_failed_rows_PO_rec.line_location_id;
399 v_po_release_id := get_synch_failed_rows_PO_rec.po_release_id;
400
401 po_resub_insert(v_po_header_id,v_po_line_id,v_line_location_id,v_po_release_id,get_trans_type_rec.type_lookup_code);
402
403
404 ELSE
405 IF get_synch_failed_rows_PO_rec.po_header_id = v_po_header_id AND
406 get_synch_failed_rows_PO_rec.po_release_id = NVL(v_po_release_id,0)
407 AND inserted_ind = 'Y' THEN
408 NULL;
409 ELSE
410 inserted_ind := 'N';
411 v_po_header_id := get_synch_failed_rows_PO_rec.po_header_id;
412 v_po_line_id := get_synch_failed_rows_PO_rec.po_line_id;
413 v_line_location_id := get_synch_failed_rows_PO_rec.line_location_id;
414 v_po_release_id := get_synch_failed_rows_PO_rec.po_release_id;
415
416 po_resub_insert(v_po_header_id,v_po_line_id,v_line_location_id,v_po_release_id,get_trans_type_rec.type_lookup_code);
417
418 END IF;
419 END IF;
420
421 END LOOP;
422 END IF;
423 END IF;
424
425 /* Fire the CPG Purchasing Synchronization Concurrent Request */
426 GML_PO_CON_REQ.fire_request;
427
428 EXCEPTION
429
430 WHEN OTHERS THEN
431 err_num := SQLCODE;
432 Err_Msg := SUBSTRB(SQLERRM, 1, 1000);
433 retcode:=1;
434 RAISE_APPLICATION_ERROR(-20098, Err_Msg);
435
436 END po_resub;
437
438 /*==========================================================================
439 | PROCEDURE NAME recv_resub |
440 | |
441 | DESCRIPTION Resubmission procedure which resubmits the |
442 | receiving and returning information corresponding |
443 | to the given PO. |
444 | |
445 | MODIFICATION HISTORY |
446 | |
447 | 30-JAN-99 Tony Ricci |
448 ===========================================================================*/
449
450 /* Notice: resubmission is contingent upon the correct status in the */
451 /* mapping table */
452 /*Preetam B Commented this out as it is no longer used.To solve the problems
453 of the invalid objects*/
454 /*
455 PROCEDURE recv_resub
456 (errbuf out NOCOPY varchar2,
457 retcode out NOCOPY number,
458 v_po_no IN VARCHAR2)
459 IS
460 err_num NUMBER;
461 err_msg VARCHAR2(100);
462
463 CURSOR line_cur IS
464 SELECT po_id, line_id
465 FROM po_ordr_dtl
466 WHERE po_id = (SELECT po_id
467 FROM po_ordr_hdr
468 WHERE po_no = v_po_no)
469 ORDER BY line_id;
470
471 BEGIN
472
473 retcode :=0;
474 FOR v_line IN line_cur LOOP
475 gml_cpg_receiving_interface.store_id(v_line.po_id, v_line.line_id);
476 IF gml_cpg_receiving_interface.check_mapping THEN
477 gml_cpg_receiving_interface.sum_recv;
478 END IF;
479 END LOOP;
480
481 EXCEPTION
482
483 WHEN OTHERS THEN
484 err_num := SQLCODE;
485 err_msg := SUBSTRB(SQLERRM, 1, 100);
486 retcode :=1;
487 errbuf := 'Error IN recv_resub';
488 RAISE_APPLICATION_ERROR(-20000, err_msg);
489
490 END recv_resub;
491 */
492 END GML_PO_CON_REQ;