I really need help.PLEASE - ASAP

From: <azhan98_at_tm.net.my>
Date: Fri, 09 Oct 1998 02:20:16 GMT
Message-ID: <6vjrsv$lf9$1_at_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 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;

** The problem start here **

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 Fri Oct 09 1998 - 04:20:16 CEST

This message: [ Message body ]
Next message: david_levine_at_opcenter.net: "Re: Net80 and WINSOCK 2"
Previous message: Datasyst: "How: skip pages with report builder"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message