Home » SQL & PL/SQL » SQL & PL/SQL » STS_ERROR :ORA-01821
STS_ERROR :ORA-01821 [message #241543] Tue, 29 May 2007 22:59 Go to next message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
hi there,

Like everyone, i too have a problem. I am able to execute the coding successfully, and the PL/SQL procedure successfully completed. I am trying to view my report using leechftp in order to see the output of my report and do some ammendements and alignments but then there is no results on the output file.

the error which it shows on the log file is

MAX_LINE : -1
STS_ERROR :ORA-01821: date format not recognized
SQLCODE : 1
SQLERRM : User-Defined Exception
------------------------------
ERROR-2


why is that so, I checks other reports and it all fine but why mine...please assist me..i urgently need to complete this report.below is the coding of my report. thank you.


1 /*=========================================================================
2 Program ID    : TMIN0870 TIM DTS Receiving Lot by Vendor
3 Created Date  : 
4 Created By    :
5 Purpose       :

6 Parameter     : Rcv Date From
7 Rcv Date To 
8 Vendor No 
9 Item No 

10 ==============================================================================*/
11 SET SERVEROUTPUT ON FORMAT WRAPPED
12 CREATE OR REPLACE PROCEDURE TMIN0870( 
13 ERRBUF           OUT VARCHAR2,
14 RETCODE          OUT VARCHAR2,
15 PRA_VEND_NO      IN  VARCHAR2,
16 PRA_RCV_FROM     IN  DATE,
17 PRA_RCV_TO       IN  DATE,
18 PRA_ITEM_NO      IN  VARCHAR2
19 ) IS

20 CURSOR C01
21 IS   

22 SELECT RCV.VEND_NO,
23 RCV.VENDOR_NAME,
24 RCV.ITEM_NO,
25 SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION, 
26 RCV.CATEGORY ,
27 COUNT(*)REC_LOT
28 FROM ( SELECT RT.SHIPMENT_HEADER_ID,
29 MSI.SEGMENT1 ITEM_NO, 
30 MSI.DESCRIPTION,
31 PV.SEGMENT1 VEND_NO,
32 PV.VENDOR_NAME, 
33 NVL(B.CATEGORY,'DTS') CATEGORY
34 FROM   RCV_TRANSACTIONS RT, 
35 PO_LINES_ALL PLA, 
36 PO_VENDORS PV, 
37 MTL_SYSTEM_ITEMS_B MSI,
38 ( SELECT SUM(QUANTITY)QTY,
39 PARENT_TRANSACTION_ID
40 FROM  RCV_TRANSACTIONS
41 WHERE  TRANSACTION_TYPE = 'CORRECT'
42 GROUP BY PARENT_TRANSACTION_ID) A,
43 ( SELECT MIC.INVENTORY_ITEM_ID,
44 DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
45 FROM   MTL_ITEM_CATEGORIES MIC
46 WHERE  MIC.CATEGORY_SET_ID IN (243,183)
47 UNION 
48 SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
49 FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B

50 WHERE TRUNC(RT.TRANSACTION_DATE) BETWEEN TO_DATE(PRA_RCV_FROM,'DD-MON-YYYY')AND TO_DATE(PRA_RCV_TO,'DD-MON-YYYY') 
51 AND RT.PO_LINE_ID = PLA.PO_LINE_ID
52 AND RT.VENDOR_ID = PV.VENDOR_ID
53 AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
54 AND RT.TRANSACTION_TYPE = 'RECEIVE'
55 AND RT.ROUTING_HEADER_ID = 3
56 AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
57 AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
58 AND RT.QUANTITY + NVL(A.QTY,0) > 0
59 GROUP BY RT.SHIPMENT_HEADER_ID,
60 MSI.SEGMENT1, 
61 MSI.DESCRIPTION,
62 PV.SEGMENT1,
63 PV.VENDOR_NAME, 
64 NVL(B.CATEGORY,'DTS')) RCV
65 GROUP BY RCV.VEND_NO,
66 RCV.VENDOR_NAME, 
67 RCV.ITEM_NO, 
68 RCV.DESCRIPTION,
69 RCV.CATEGORY
70 ORDER BY RCV.VEND_NO,
71 RCV.ITEM_NO

72 ;/* END C01 */


73 CURSOR C02
74 IS

75 SELECT RCV.VEND_NO, 
76 RCV.VENDOR_NAME,
77 RCV.ITEM_NO,
78 SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
79 RCV.CATEGORY,
80 COUNT(*)REC_LOT 
81 FROM ( SELECT RT.SHIPMENT_HEADER_ID,
82 MSI.SEGMENT1 ITEM_NO, 
83 MSI.DESCRIPTION,
84 PV.SEGMENT1 VEND_NO,
85 PV.VENDOR_NAME, 
86 NVL(B.CATEGORY,'DTS') CATEGORY
87 FROM   RCV_TRANSACTIONS RT, 
88 PO_LINES_ALL PLA, 
89 PO_VENDORS PV, 
90 MTL_SYSTEM_ITEMS_B MSI,
91 ( SELECT SUM(QUANTITY)QTY,
92 PARENT_TRANSACTION_ID
93 FROM  RCV_TRANSACTIONS
94 WHERE  TRANSACTION_TYPE = 'CORRECT'
95 GROUP BY PARENT_TRANSACTION_ID) A,
96 ( SELECT MIC.INVENTORY_ITEM_ID,
97 DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
98 FROM   MTL_ITEM_CATEGORIES MIC
99 WHERE  MIC.CATEGORY_SET_ID IN (243,183)
100 UNION 
101 SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
102 FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B

103 WHERE TRUNC(RT.TRANSACTION_DATE) BETWEEN TO_DATE(PRA_RCV_FROM,'DD-MON-YYYY')AND TO_DATE(PRA_RCV_TO,'DD-MON-YYYY') 
104 AND RT.PO_LINE_ID = PLA.PO_LINE_ID
105 AND RT.VENDOR_ID = PV.VENDOR_ID
106 AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
107 AND RT.TRANSACTION_TYPE = 'RECEIVE'
108 AND RT.ROUTING_HEADER_ID = 3 
109 AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
110 AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
111 AND RT.QUANTITY + NVL(A.QTY,0) > 0
112 AND PV.VENDOR_ID = PRA_VEND_NO 
113 GROUP BY RT.SHIPMENT_HEADER_ID,
114 MSI.SEGMENT1, 
115 MSI.DESCRIPTION,
116 PV.SEGMENT1,
117 PV.VENDOR_NAME, 
118 NVL(B.CATEGORY,'DTS')) RCV
119 GROUP BY RCV.VEND_NO,
120 RCV.VENDOR_NAME,
121 RCV.ITEM_NO,
122 RCV.DESCRIPTION,
123 RCV.CATEGORY         
124 ORDER BY RCV.VEND_NO,
125 RCV.ITEM_NO


126 ; /* END C02 */       


127 CURSOR C03
128 IS

129 SELECT RCV.VEND_NO,
130 RCV.VENDOR_NAME,
131 RCV.ITEM_NO,
132 SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
133 RCV.CATEGORY,
134 COUNT(*)REC_LOT 
135 FROM ( SELECT RT.SHIPMENT_HEADER_ID,
136 MSI.SEGMENT1 ITEM_NO, 
137 MSI.DESCRIPTION,
138 PV.SEGMENT1 VEND_NO,
139 PV.VENDOR_NAME, 
140 NVL(B.CATEGORY,'DTS') CATEGORY
141 FROM   RCV_TRANSACTIONS RT, 
142 PO_LINES_ALL PLA, 
143 PO_VENDORS PV, 
144 MTL_SYSTEM_ITEMS_B MSI,
145 ( SELECT SUM(QUANTITY)QTY,
146 PARENT_TRANSACTION_ID
147 FROM  RCV_TRANSACTIONS
148 WHERE  TRANSACTION_TYPE = 'CORRECT'
149 GROUP BY PARENT_TRANSACTION_ID) A,
150 ( SELECT MIC.INVENTORY_ITEM_ID,
151 DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
152 FROM   MTL_ITEM_CATEGORIES MIC
153 WHERE  MIC.CATEGORY_SET_ID IN (243,183)
154 UNION 
155 SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
156 FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B

157 WHERE TRUNC(RT.TRANSACTION_DATE) BETWEEN TO_DATE(PRA_RCV_FROM,'DD-MON-YYYY')AND TO_DATE(PRA_RCV_TO,'DD-MON-YYYY') 
158 AND RT.PO_LINE_ID = PLA.PO_LINE_ID
159 AND RT.VENDOR_ID = PV.VENDOR_ID
160 AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
161 AND RT.TRANSACTION_TYPE = 'RECEIVE'
162 AND RT.ROUTING_HEADER_ID = 3 
163 AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
164 AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
165 AND RT.QUANTITY + NVL(A.QTY,0) > 0
166 AND MSI.INVENTORY_ITEM_ID = PRA_ITEM_NO 
167 GROUP BY RT.SHIPMENT_HEADER_ID,
168 MSI.SEGMENT1, 
169 MSI.DESCRIPTION,
170 PV.SEGMENT1,
171 PV.VENDOR_NAME, 
172 NVL(B.CATEGORY,'DTS')) RCV
173 GROUP BY RCV.VEND_NO,
174 RCV.VENDOR_NAME,
175 RCV.ITEM_NO,
176 RCV.DESCRIPTION,
177 RCV.CATEGORY         
178 ORDER BY RCV.VEND_NO,
179 RCV.ITEM_NO


180 ;/* END C03*/


181 CURSOR C04 
182 IS

183 SELECT RCV.VEND_NO,
184 RCV.VENDOR_NAME,
185 RCV.ITEM_NO,
186 SUBSTR(RCV.DESCRIPTION,1,25)DESCRIPTION,
187 RCV.CATEGORY,
188 COUNT(*)REC_LOT 
189 FROM ( SELECT RT.SHIPMENT_HEADER_ID,
190 MSI.SEGMENT1 ITEM_NO, 
191 MSI.DESCRIPTION,
192 PV.SEGMENT1 VEND_NO,
193 PV.VENDOR_NAME, 
194 NVL(B.CATEGORY,'DTS') CATEGORY
195 FROM   RCV_TRANSACTIONS RT, 
196 PO_LINES_ALL PLA, 
197 PO_VENDORS PV, 
198 MTL_SYSTEM_ITEMS_B MSI,
199 ( SELECT SUM(QUANTITY)QTY,
200 PARENT_TRANSACTION_ID
201 FROM  RCV_TRANSACTIONS
202 WHERE  TRANSACTION_TYPE = 'CORRECT'
203 GROUP BY PARENT_TRANSACTION_ID) A,
204 ( SELECT MIC.INVENTORY_ITEM_ID,
205 DECODE(MIC.CATEGORY_SET_ID,243,'JIT', 183,'VMI') CATEGORY
206 FROM   MTL_ITEM_CATEGORIES MIC
207 WHERE  MIC.CATEGORY_SET_ID IN (243,183)
208 UNION 
209 SELECT MKP.INVENTORY_ITEM_ID, 'KANBAN'
210 FROM   MTL_KANBAN_PULL_SEQUENCES MKP) B

211 WHERE TRUNC(RT.TRANSACTION_DATE) BETWEEN TO_DATE(PRA_RCV_FROM,'DD-MON-YYYY')AND TO_DATE(PRA_RCV_TO,'DD-MON-YYYY') 
212 AND RT.PO_LINE_ID = PLA.PO_LINE_ID
213 AND RT.VENDOR_ID = PV.VENDOR_ID
214 AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
215 AND RT.TRANSACTION_TYPE = 'RECEIVE'
216 AND RT.ROUTING_HEADER_ID = 3
217 AND RT.TRANSACTION_ID = A.PARENT_TRANSACTION_ID (+)
218 AND MSI.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID (+)
219 AND RT.QUANTITY + NVL(A.QTY,0) > 0
220 AND PV.VENDOR_ID = PRA_VEND_NO 
221 AND MSI.INVENTORY_ITEM_ID = PRA_ITEM_NO
222 GROUP BY RT.SHIPMENT_HEADER_ID,
223 MSI.SEGMENT1, 
224 MSI.DESCRIPTION,
225 PV.SEGMENT1,
226 PV.VENDOR_NAME, 
227 NVL(B.CATEGORY,'DTS')) RCV
228 GROUP BY RCV.VEND_NO,
229 RCV.VENDOR_NAME,
230 RCV.ITEM_NO,
231 RCV.DESCRIPTION,
232 RCV.CATEGORY         
233 ORDER BY RCV.VEND_NO,
234 RCV.ITEM_NO


235 ;/* END C04 */ 



236 TYPE REC_VEND_NO_TYPE IS TABLE OF PO_VENDORS.SEGMENT1%TYPE
237 INDEX BY BINARY_INTEGER;
238 REC_VEND_NO REC_VEND_NO_TYPE;  

239 TYPE REC_VENDOR_NAME_TYPE IS TABLE OF PO_VENDORS.VENDOR_NAME%TYPE
240 INDEX BY BINARY_INTEGER;
241 REC_VENDOR_NAME REC_VENDOR_NAME_TYPE; 

242 TYPE REC_ITEM_NO_TYPE IS TABLE OF MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE
243 INDEX BY BINARY_INTEGER;
244 REC_ITEM_NO REC_ITEM_NO_TYPE;

245 TYPE REC_DESC_TYPE IS TABLE OF MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE
246 INDEX BY BINARY_INTEGER;
247 REC_DESC REC_DESC_TYPE;

248 TYPE REC_CATEGORY_TYPE IS TABLE OF VARCHAR2(6) 
249 INDEX BY BINARY_INTEGER;
250 REC_CATEGORY REC_CATEGORY_TYPE; 

251 TYPE REC_COUNT_TYPE IS TABLE OF NUMBER
252 INDEX BY BINARY_INTEGER;
253 REC_COUNT REC_COUNT_TYPE; 



254 V_ORG_NAME       ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_NAME%TYPE;
255 V_VEND_NO        PO_VENDORS.SEGMENT1%TYPE; 
256 V_VENDOR_NAME    PO_VENDORS.VENDOR_NAME%TYPE;
257 V_ITEM_NO        MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
258 V_DESC           MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE;
259 V_CATEGORY       VARCHAR2(6);
260 V_COUNT          NUMBER;

261 V_MSG            VARCHAR2(240);
262 N_RET_VAL        NUMBER;
263 TOTAL_PAGE       NUMBER;
264 PAGE_LINE        CONSTANT NUMBER := 45;
265 l                BINARY_INTEGER DEFAULT 0;
266 IDX              BINARY_INTEGER DEFAULT 0; 
267 MAX_LINE         BINARY_INTEGER DEFAULT 0;
268 STS              NUMBER DEFAULT 0;
269 CNC_RET          BOOLEAN;
270 STS_ERROR        EXCEPTION;
271 ERR_RET          CONSTANT NUMBER := -1;
272 OK_RET           CONSTANT NUMBER := 0;



273 /*==============================================
274 GET_TOTAL_PAGE
275 =============================================*/

276 FUNCTION GET_TOTAL_PAGE 
277 (
278 P_MAX_LINE   IN NUMBER,
279 P_TOTAL      OUT NUMBER,
280 P_MSG        OUT VARCHAR2   
281 )
282 RETURN NUMBER IS
283 LINE_CNT NUMBER DEFAULT 0;
284 PAGE_CNT NUMBER DEFAULT 0;


285 BEGIN
286 FOR IDX IN 0..P_MAX_LINE LOOP   
287 IF LINE_CNT = 0 THEN
288 PAGE_CNT := PAGE_CNT + 1; 
289 END IF;

290 IF IDX < P_MAX_LINE THEN
291 IF LINE_CNT + 3 > PAGE_LINE THEN
292 LINE_CNT := 0;
293 PAGE_CNT := PAGE_CNT + 1;
294 END IF;
295 LINE_CNT := LINE_CNT + 3;
296 END IF;

297 IF LINE_CNT >= PAGE_LINE THEN
298 LINE_CNT := 0;
299 END IF;

300 END LOOP;

301 IF LINE_CNT = 0 THEN
302 PAGE_CNT := PAGE_CNT + 1;
303 END IF;

304 IF LINE_CNT + 3 >= PAGE_LINE THEN
305 PAGE_CNT := PAGE_CNT + 1;
306 END IF;

307 IF LINE_CNT + 4 >= PAGE_LINE THEN
308 PAGE_CNT := PAGE_CNT + 1;
309 END IF;

310 P_TOTAL := PAGE_CNT;          


311 RETURN OK_RET;

312 EXCEPTION

313 WHEN OTHERS THEN
314 P_MSG := SUBSTR(SQLERRM, 1,256);
315 RETURN ERR_RET;

316 END GET_TOTAL_PAGE;



317 /*==============================================
318 PRINT_HEADER
319 =============================================*/

320 PROCEDURE PRINT_HEADER (PAGE_CNT IN NUMBER) IS

321 BEGIN

322 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(V_ORG_NAME,30) || RPAD(' ',117) || 'Report Date : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH 24:MI'));

323 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Report ID : TMIN0870' || RPAD(' ',138) || 'Page: ' ||RPAD(' ',7)|| LPAD(TO_CHAR(PAGE_CNT),4)||' OF '||LPAD(TO_CHAR(TOTAL_PAGE),4));

324 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');

325 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(' ',83) || 'TIM DTS Receiving Lot by Vendor');
326 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');

327 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor No   Vendor Name                 Item No      Description                    Category Rec Lot');

328 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------- --------------------------- ------------ ------------------------------ -------- -------'); 
329 END;



330 /*==============================================
331 DISPLAY_RECORD
332 =============================================*/

333 FUNCTION DISPLAY_RECORD (
334 P_MAX_LINE  IN NUMBER,
335 P_MSG   OUT VARCHAR2
336 ) RETURN NUMBER IS

337 LINE_CNT NUMBER DEFAULT 0;
338 PAGE_CNT NUMBER DEFAULT 0;


339 BEGIN

340 FOR IDX IN 0..P_MAX_LINE LOOP

341 IF LINE_CNT = 0 THEN
342 PAGE_CNT := PAGE_CNT + 1;
343 PRINT_HEADER(PAGE_CNT);
344 LINE_CNT := 0;
345 END IF;


346 IF LINE_CNT = 0 THEN 
347 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
348 RPAD(REC_VEND_NO(IDX), 11)
349 ||' '|| RPAD(REC_VENDOR_NAME(IDX), 27)  
350 ||' '||RPAD(REC_ITEM_NO(IDX), 12)
351 ||' '||RPAD(REC_DESC(IDX), 30)
352 ||' '||RPAD(' ',9)||REC_CATEGORY(IDX)
353 ||' '||REC_COUNT(IDX));

354 END IF;   


355 --LINE_CNT := LINE_CNT + 1;

356 IF IDX < P_MAX_LINE THEN
357 IF LINE_CNT+3 > PAGE_LINE THEN
358 LINE_CNT := 0;
359 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, CHR(12));
360 PAGE_CNT := PAGE_CNT + 1;
361 PRINT_HEADER(PAGE_CNT);
362 END IF;
363 END IF;

364 IF LINE_CNT >= PAGE_LINE THEN
365 FND_FILE.PUT(FND_FILE.OUTPUT,CHR(12));
366 LINE_CNT := 0; 
367 END IF;

368 END LOOP;

369 IF LINE_CNT = 0 THEN
370 PAGE_CNT := PAGE_CNT + 1;
371 PRINT_HEADER (PAGE_CNT);
372 END IF;

373 IF LINE_CNT + 3 >= PAGE_LINE THEN
374 LINE_CNT := 0;
375 PAGE_CNT := PAGE_CNT + 1;
376 PRINT_HEADER(PAGE_CNT);
377 END IF;


378 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
379 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(' ',78) ||'* * * * * END OF REPORT * * * * *');    


380 RETURN OK_RET;

381 EXCEPTION
382 WHEN OTHERS THEN
383 P_MSG := SUBSTR(SQLERRM,1,256);
384 RETURN ERR_RET;    

385 END DISPLAY_RECORD;

386 /*=================================================
387 MAIN
388 =================================================*/

389 BEGIN

390 FND_FILE.PUT_NAMES('/usr/tmp/IN0870.log','/usr/tmp/IN0870.out','/usr/tmp');  


391 SELECT ORGANIZATION_NAME
392 INTO V_ORG_NAME
393 FROM ORG_ORGANIZATION_DEFINITIONS
394 WHERE ORGANIZATION_ID = 21;


395 IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NULL THEN
396 V_MSG := 'OPEN C01';
397 OPEN C01;

398 ELSIF PRA_VEND_NO IS NOT NULL THEN 
399 V_MSG := 'OPEN C02';
400 OPEN C02;

401 ELSIF PRA_ITEM_NO IS NOT NULL THEN
402 V_MSG := 'OPEN C03';
403 OPEN C03;

404 ELSE 
405 V_MSG := 'OPEN C04';
406 OPEN C04;

407 END IF;


408 LOOP
409 IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NULL THEN
410 V_MSG := 'FETCH C01';
411 FETCH C01 INTO 
412 V_VEND_NO,
413 V_VENDOR_NAME,
414 V_ITEM_NO,
415 V_DESC,
416 V_CATEGORY,
417 V_COUNT;  
418 EXIT WHEN C01%NOTFOUND;

419 ELSIF PRA_VEND_NO IS NOT NULL THEN 
420 V_MSG := 'FETCH C02';
421 FETCH C02 INTO 
422 V_VEND_NO,
423 V_VENDOR_NAME,
424 V_ITEM_NO,
425 V_DESC,
426 V_CATEGORY,
427 V_COUNT;  
428 EXIT WHEN C02%NOTFOUND;

429 ELSIF PRA_ITEM_NO IS NOT NULL THEN
430 V_MSG := 'FETCH C03';
431 FETCH C03 INTO 
432 V_VEND_NO,
433 V_VENDOR_NAME,
434 V_ITEM_NO,
435 V_DESC,
436 V_CATEGORY,
437 V_COUNT;  
438 EXIT WHEN C03%NOTFOUND;

439 ELSE 
440 V_MSG := 'FETCH C04';
441 FETCH C04 INTO 
442 V_VEND_NO,
443 V_VENDOR_NAME,
444 V_ITEM_NO,
445 V_DESC,
446 V_CATEGORY,
447 V_COUNT;  
448 EXIT WHEN C04%NOTFOUND;

449 END IF; 

450 END LOOP;


451 IF PRA_VEND_NO IS NULL AND PRA_ITEM_NO IS NULL THEN
452 V_MSG := 'CLOSE C01';
453 CLOSE C01;

454 ELSIF PRA_VEND_NO IS NOT NULL THEN 
455 V_MSG := 'CLOSE C02';
456 CLOSE C02;

457 ELSIF PRA_ITEM_NO IS NOT NULL THEN
458 V_MSG := 'CLOSE C03';
459 CLOSE C03;

460 ELSE
461 V_MSG := 'CLOSE C04';
462 CLOSE C04;

463 END IF; 


464 MAX_LINE := l - 1;
465 FND_FILE.PUT_LINE(FND_FILE.LOG, 'MAX_LINE       : '||MAX_LINE);   

466 STS := GET_TOTAL_PAGE(MAX_LINE,TOTAL_PAGE, V_MSG);
467 IF STS = ERR_RET THEN RAISE STS_ERROR;
468 END IF;

469 STS := DISPLAY_RECORD(MAX_LINE,V_MSG);
470 IF STS = ERR_RET THEN RAISE STS_ERROR;
471 END IF;


472 CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',NULL);
473 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

474 EXCEPTION
475 WHEN STS_ERROR THEN
476 IF C01%ISOPEN THEN CLOSE C01;
477 END IF;
478 IF C02%ISOPEN THEN CLOSE C02;
479 END IF;
480 IF C03%ISOPEN THEN CLOSE C03;
481 END IF;
482 IF C04%ISOPEN THEN CLOSE C04; 
483 END IF; 

484 FND_FILE.PUT_LINE(FND_FILE.LOG,'STS_ERROR :'||V_MSG);

485 IF SQLCODE != 0 THEN
486 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLCODE : '||TO_CHAR(SQLCODE));
487 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM : '||SQLERRM);
488 END IF;

489 FND_FILE.PUT_LINE(FND_FILE.LOG,'------------------------------');
490 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-2');
491 CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);

492 WHEN OTHERS THEN
493 IF C01%ISOPEN THEN CLOSE C01;
494 END IF;
495 IF C02%ISOPEN THEN CLOSE C02;
496 END IF;
497 IF C03%ISOPEN THEN CLOSE C03;
498 END IF;
499 IF C04%ISOPEN THEN CLOSE C04;
500 END IF;


501 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-1 : '||V_MSG);
502 IF SQLCODE != 0 THEN
503 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLCODE : '||TO_CHAR(SQLCODE));
504 FND_FILE.PUT_LINE(FND_FILE.LOG,'SQLERRM : '||SQLERRM);
505 END IF;

506 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------');
507 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-2');
508 CNC_RET := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',NULL);

509 END;
510 /
511 show error







Re: STS_ERROR :ORA-01821 [message #241555 is a reply to message #241543] Wed, 30 May 2007 00:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you hit such an error again, (temporarily) remove your exception handler, since it obfuscates the line where the error occurs.
The erroneous dateformat is TO_CHAR(SYSDATE,'DD-MON-YYYY HH 24:MI')
hh24 should not be separated with a space.
Re: STS_ERROR :ORA-01821 [message #241591 is a reply to message #241543] Wed, 30 May 2007 02:16 Go to previous message
kogilaah_k
Messages: 20
Registered: May 2007
Location: Malaysia
Junior Member
Thank you very much frnak..really appreciate your help..
thank you again...
Previous Topic: Pl/SQL procedure for Packed data format
Next Topic: how to reslove table spaces
Goto Forum:
  


Current Time: Tue Dec 06 00:04:35 CST 2016

Total time taken to generate the page: 0.06218 seconds