Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> I really need help. Please - ASAP

I really need help. Please - ASAP

From: <azhan98_at_tm.net.my>
Date: Fri, 09 Oct 1998 02:18:54 GMT
Message-ID: <6vjrqe$ldb$1@nnrp1.dejanews.com> 





I'm developing some customize report (Using Report 2.5) which will insert
certain data into my customized table.This for sure requires some PL/SQL
statement as part of the report. I have test the report in the Report 2.5
application and it work as what I need. When I put the report on the server
and group it as one of my Oracle Application (Oracle Manufacturing) report,
it says "There exist uncompiled program unit" in my report. I know the
compilation is not the problem because there is no error.



This is the code at my first attempt:


(All code under before report trigger)





function BeforeReport return boolean is

v_jobid NUMBER(7);
v_jobname VARCHAR(15);
v_initial_jobqty NUMBER(7);
v_mihoid NUMBER(7);
v_mihoname VARCHAR(15);
v_mihoqty NUMBER(7);
v_noof_lot NUMBER(7);
v_bal_qty NUMBER(7);
v_normallot_qty NUMBER(7);
v_combination_qty NUMBER(7);
v_last1_qty NUMBER(7);
v_last2_qty NUMBER(7);
v_divide2 NUMBER(7);
v_start_counter NUMBER(7);
v_last_week NUMBER(7);
v_current_week NUMBER(7);
v_lastlot_number NUMBER(4);
v_last_counter NUMBER(7);
v_parent_id NUMBER(7);
v_current_running NUMBER(15);
v_success_qty NUMBER(7);
v_jobflag VARCHAR(1);
v_max_allowed  NUMBER(7);




begin



v_jobflag := 'M';



SELECT ALL



WIP.WIP_DISCRETE_JOBS.WIP_ENTITY_ID,WIP.WIP_ENTITIES.WIP_ENTITY_NAME,

WIP.WIP_DISCRETE_JOBS.START_QUANTITY INTO v_jobid,v_jobname,v_initial_jobqty
FROM WIP.WIP_DISCRETE_JOBS, WIP.WIP_ENTITIES WHERE

WIP.WIP_ENTITIES.WIP_ENTITY_NAME= :P_JOBNO  AND 



(WIP.WIP_DISCRETE_JOBS.WIP_ENTITY_ID=WIP.WIP_ENTITIES.WIP_ENTITY_ID);




:P_JOBID := v_jobid;



SELECT SUBSTR(MAX(LOT_NO),3,2) INTO v_last_week FROM TI.TI_JOBSPLIT_LINES;
SELECT TO_CHAR(SYSDATE,'WW') INTO v_current_week FROM DUAL;
SELECT DISTINCT SUBSTR(NVL(MAX(LOT_NO),'0000'),5) INTO v_lastlot_number FROM
TI.TI_JOBSPLIT_LINES;



v_mihoid := 1;
v_mihoname := 'RRHP111';
v_mihoqty := 100000;





INSERT INTO



TI.TI_JOBSPLIT_HEADER(HEADER_ID,MIHO_ID,MIHO_NUMBER,MIHO_QUANTITY,JOB_ID,JOB_

NUM BER,INITIAL_JOB_QTY,QTY_TO_SPLIT,MRG_LOT_SIZE)

VALUES(TI_HEADER_ID.nextval,v_mihoid,v_mihoname,v_mihoqty,v_jobid,v_jobname,v
_in itial_jobqty,:P_QTY_TOSPLIT,:P_MFG_LOTSIZE); COMMIT;



SELECT NVL(MAX(HEADER_ID),0) INTO v_parent_id FROM TI.TI_JOBSPLIT_HEADER;



SELECT NVL(MAX(CURRENT_RUNNING_QTY),0) INTO v_current_running FROM
TI.TI_JOBSPLIT_HEADER



WHERE TI.TI_JOBSPLIT_HEADER.JOB_ID = v_jobid;



v_max_allowed := (v_initial_jobqty - v_current_running);



IF v_current_running = v_initial_jobqty THEN
srw.message (5,'No more job split allowed. This job have already completed');
raise srw.program_abort;


END IF;


IF :P_MFG_LOTSIZE > :P_QTY_TOSPLIT THEN

srw.message(4,'Manufacturing Lot size cannot be greater than Quantity To
Split');


raise srw.program_abort;


END IF;


IF :P_QTY_TOSPLIT > v_mihoqty THEN


srw.message(3,'Quantity to split cannot be greater than your MIHO quantity');
raise srw.program_abort;


END IF;


IF (v_current_running + :P_QTY_TOSPLIT) > v_initial_jobqty THEN
srw.message (v_max_allowed,'Overcompletion detected.Reduce your quantity to
split value.MSG-XXXX identified your maximum allowed to split is your XXXX
value.Your QTY TO SPLIT should not be greater than this value');
raise srw.program_abort;


END IF;


v_noof_lot := TRUNC(:P_QTY_TOSPLIT/:P_MFG_LOTSIZE);
v_bal_qty := (:P_QTY_TOSPLIT - (v_noof_lot * :P_MFG_LOTSIZE));



IF v_bal_qty = 0 THEN


v_normallot_qty := :P_MFG_LOTSIZE;



ELSE


v_noof_lot := v_noof_lot + 1;
v_combination_qty := v_bal_qty + :P_MFG_LOTSIZE;
v_divide2 := (v_combination_qty/2);




 IF MOD(v_combination_qty,2) = 0 THEN

 v_normallot_qty := :P_MFG_LOTSIZE;
 v_last1_qty := TRUNC(v_divide2);
 v_last2_qty := TRUNC(v_divide2);




 ELSE


 v_normallot_qty := :P_MFG_LOTSIZE;
 v_last1_qty := TRUNC(v_divide2);
 v_last2_qty := TRUNC(v_divide2) - 1;




 END IF;



END IF;


IF v_last_week = v_current_week THEN


v_start_counter := v_lastlot_number;


v_last_counter := v_start_counter + v_noof_lot;
ELSE



v_last_counter := v_noof_lot;


v_start_counter := 0;


END IF;






LOOP



v_start_counter := v_start_counter +1;


IF v_start_counter = (v_last_counter) THEN
  INSERT INTO TI.TI_JOBSPLIT_LINES(HEADER_ID,LINES_ID,LOT_NO,LOT_QTY,JOBFLAG)


VALUES(TI_HEADER_ID.currval,TI_LINES_ID.nextval,LTRIM(TO_CHAR(SYSDATE,'YY'))||LT
RIM(TO_CHAR(SYSDATE,'WW'))||LTRIM(TO_CHAR(v_start_counter,'0000')),v_last1_qty,v
_jobflag);


  COMMIT;



ELSIF v_start_counter = (v_last_counter - 1) THEN
INSERT INTO TI.TI_JOBSPLIT_LINES(HEADER_ID,LINES_ID,LOT_NO,LOT_QTY,JOBFLAG)

VALUES(TI_HEADER_ID.currval,TI_LINES_ID.nextval,LTRIM(TO_CHAR(SYSDATE,'YY'))||LT
RIM(TO_CHAR(SYSDATE,'WW'))||LTRIM(TO_CHAR(v_start_counter,'0000')),v_last2_qty,v
_jobflag);


COMMIT;



ELSE



INSERT INTO TI.TI_JOBSPLIT_LINES(HEADER_ID,LINES_ID,LOT_NO,LOT_QTY,JOBFLAG)

VALUES(TI_HEADER_ID.currval,TI_LINES_ID.nextval,LTRIM(TO_CHAR(SYSDATE,'YY'))||LT
RIM(TO_CHAR(SYSDATE,'WW'))||LTRIM(TO_CHAR(v_start_counter,'0000')),v_normallot_q
ty,v_jobflag);


COMMIT;



END IF;



EXIT WHEN v_start_counter = v_last_counter;
END LOOP;


SELECT SUM(LOT_QTY) INTO v_success_qty FROM TI.TI_JOBSPLIT_LINES
WHERE TI.TI_JOBSPLIT_LINES.HEADER_ID = v_parent_id;



UPDATE TI.TI_JOBSPLIT_HEADER



SET TI.TI_JOBSPLIT_HEADER.CURRENT_RUNNING_QTY = (v_current_running +
v_success_qty)


WHERE TI.TI_JOBSPLIT_HEADER.JOB_ID = v_jobid;
COMMIT;



  return (TRUE);


end;



I started to realize that the problem actually start at the LOOP portion (and
below until the end).Because when I remove that portion, I have no problem at
all at my Oracle Application (Oracle Manufacturing) and its "Completed
Normal".



First concern




My question is, is it because my code is too long to be put at before report
trigger and thus it assume anything below that LOOP portion as uncompiled.
If so then why under Report 2.5 application this is not a problem.Is it any
limit in before report trigger?. How to overcome this (extend the limit)?



If there is a limit.This is what I did. I break my code into portions


(procedure & function) and the code now looks like this:





function BeforeReport return boolean is

v_jobid NUMBER(7);
v_jobname VARCHAR(15);
v_initial_jobqty NUMBER(7);
v_mihoname VARCHAR(15);
v_mihoqty NUMBER(7);
v_noof_lot NUMBER(7);
v_bal_qty NUMBER(7);
v_normallot_qty NUMBER(7);
v_combination_qty NUMBER(7);
v_last1_qty NUMBER(7);
v_last2_qty NUMBER(7);
v_divide2 NUMBER(7);
v_start_counter NUMBER(7);
v_last_week NUMBER(7);
v_current_week NUMBER(7);
v_lastlot_number NUMBER(4);
v_last_counter NUMBER(7);
v_parent_id NUMBER(7);
v_current_running NUMBER(15);
v_success_qty NUMBER(7);
v_jobflag VARCHAR(1);
v_max_allowed  NUMBER(7);





begin


v_jobflag := 'M';



SELECT ALL



WIP.WIP_DISCRETE_JOBS.WIP_ENTITY_ID,WIP.WIP_ENTITIES.WIP_ENTITY_NAME,

WIP.WIP_DISCRETE_JOBS.START_QUANTITY INTO v_jobid,v_jobname,v_initial_jobqty
FROM WIP.WIP_DISCRETE_JOBS, WIP.WIP_ENTITIES WHERE

WIP.WIP_ENTITIES.WIP_ENTITY_NAME= :P_JOBNO  AND 



(WIP.WIP_DISCRETE_JOBS.WIP_ENTITY_ID=WIP.WIP_ENTITIES.WIP_ENTITY_ID);




SELECT SUBSTR(MAX(LOT_NO),3,2) INTO v_last_week FROM TI.TI_JOBSPLIT_LINES;
v_current_week := Current_Week(SYSDATE);
SELECT DISTINCT SUBSTR(NVL(MAX(LOT_NO),'0000'),5) INTO v_lastlot_number FROM
TI.TI_JOBSPLIT_LINES;


v_mihoname := 'RRHP111';


v_mihoqty := 100000;


Insert_Header(v_mihoname,v_mihoqty,v_jobid,v_jobname,v_initial_jobqty);



SELECT NVL(MAX(HEADER_ID),0) INTO v_parent_id FROM TI.TI_JOBSPLIT_HEADER;
v_current_running := Current_Running(v_jobid);
v_max_allowed := (v_initial_jobqty - v_current_running);



Check_Current_Running(v_current_running,v_initial_jobqty);
Check_Qty_ToSplit(v_current_running,v_initial_jobqty,v_max_allowed);
v_noof_lot := TRUNC(:P_QTY_TOSPLIT/:P_MFG_LOTSIZE);
v_bal_qty := (:P_QTY_TOSPLIT - (v_noof_lot * :P_MFG_LOTSIZE));



IF v_bal_qty = 0 THEN


v_normallot_qty := :P_MFG_LOTSIZE;



ELSE


v_noof_lot := v_noof_lot + 1;
v_combination_qty := v_bal_qty + :P_MFG_LOTSIZE;
v_divide2 := (v_combination_qty/2);




 IF MOD(v_combination_qty,2) = 0 THEN

 v_normallot_qty := :P_MFG_LOTSIZE;
 v_last1_qty := TRUNC(v_divide2);
 v_last2_qty := TRUNC(v_divide2);




 ELSE


 v_normallot_qty := :P_MFG_LOTSIZE;
 v_last1_qty := TRUNC(v_divide2);
 v_last2_qty := TRUNC(v_divide2) - 1;




 END IF;



END IF;



IF v_last_week = v_current_week THEN


v_start_counter := v_lastlot_number;


v_last_counter := v_start_counter + v_noof_lot;
ELSE



v_last_counter := v_noof_lot;


v_start_counter := 0;



Finishing_Touch(all value to be pass to it)
-- Finishing_Touch is the LOOP portion and the code below it
END IF;


return (TRUE);


end;



Yet still the same problem (at the LOOP portion). Even after I break it
further it show the same message.Uncompiled program unit.



Second Concern -------------- Is it something wrong with the LOOP portion and
the code below it (can somebody look at it?) that so special until it cannot
be compiled.



LOOP



v_start_counter := v_start_counter +1;


IF v_start_counter = (v_last_counter) THEN
Insert_If_Last(v_start_counter,v_last1_qty,v_jobflag);
ELSIF v_start_counter = (v_last_counter - 1) THEN
Insert_If_SecondLast(v_start_counter,v_last2_qty,v_jobflag);
ELSE



Insert_If_Normal(v_start_counter,v_normallot_qty,v_jobflag);
END IF;



EXIT WHEN v_start_counter = v_last_counter;
END LOOP;


 I don't know because I have test it in Report 2.5 application and it is ok.
For me it should be no problem at the Report 2.5 and Oracle Application. I
have also check all the object at the report and at the oracle application


(oracle manufacturing).All the same





I REALLY APPRECIATE IF SOMEBODY CAN TELL MW WHY ("THERE EXIST UNCOMPILED

PROGRAM UNIT") SINCE I HAVE WORK ON THIS FOR 3 DAYS


ALREADY.PLEASE...................... I WANT TO SOLVE IT......





Below, I paste my first message to the group:




I have created a report using Oracle Report 2.5.
The report contains some PL/SQL statements, to be
specific in the before report trigger.


After giving my finishing touch, I compile it and
the status is ok, no errors at all. Then I run it
using the Oracle Report application. I'm really happy
that it gives exactly what I want.So I save it.



Now, I plan to use it in Oracle Application (Oracle
Manufacturing under WIP module).I save the report
in a correct directory in the server. I did all the
requirements (define the concurrent->executable,concurrent
->program & tied it in the module in report group under
system administration). Time to run...



I use the oracle application to run the report. After
submission, I really get frustrated because every time its
ended with a message "There exist an uncompiled program
unit" in my report. I did not give up at first, maybe I
missed something.



I have make sure the report properties (tools-option in
the menu) have been set to "all" for PL/SQL. I also
use save as and select "all" for pl/sql. Also I  make  sure
I didn't check the disable PL/SL compilation button in one
of the property I don't remember which one.



I understand Oracle report have 2 type of executable, *.rdf (source)
& *.rep (binary). My question is why my report is still "Uncompiled"
in the Oracle application after I compile & compile all in the
Oracle report application and save it again & again. Is it that save
only save the report but not the compilation?



If I have missed any steps, I really appreciate for any guides.
Thanks for anybbody who spend their times to help..




Norazman



-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    
Received on Thu Oct 08 1998 - 21:18:54 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US