DBA Data[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;