Creating reports
I am creating a custom report using Oracle Report Builder 6.0.8.8.3. I have created the report and they decided to register it in Oracle Application. The way the report works is that it would have to deletes all records from several custom tables and inserts records into it again. So what I did was to create a script in the BEFORE REPORT trigger that deletes all records from the table. It compiles fine but when I try to run the report it seems like it deosn't run execute the script (please see below script). I tried it too in BEFORE PARAMETER FORM and AFTER PARAMETER FORM but to no avail. PROCEDURE P_DLT_RECORDS IS BEGIN DELETE FROM AWW_RA_CUSTOMER_TRX_LINES_ALL WHERE CUSTOMER_TRX_ID IS NOT NULL; COMMIT; END; I then close the whole application and open the report. At this point it giave me an error REP-1247: Report contains uncompiles PL/SQL I then go to program unit, at this point it shows an error: "ERROR 907 at line 0, column 0 cannot load library unit APPS.AWW_RA_CUSTOMER_TRX_LINES_ALL (reference by P_DLT_RECORDS) How should I get this one fixed? Am I putting it in the wrong place? Are there other libraries that have to be loaded for this to work? When I insert records records in the custom tables can I just put it in the same trigger where I delete records. If possible please attach a sample script for deleting and inserting records. please see below the script I created in inserting records in the table. Please let me know if there are changes. INSERT INTO AWW_RA_CUSTOMER_TRX_LINES_ALL SELECT ALL AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID, SUM(AR.RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT) FROM AR.RA_CUSTOMER_TRX_LINES_ALL WHERE AR.RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE='LINE' AND AR.RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE2 IN ('WHOLESALE', 'WILL ADVISE', 'WINTER BOOKING', 'RMA') GROUP BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID ORDER BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID; Thanks in advace for the help. Mark Liggayu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Creating reports
Procedures and stored functions written in PL/SQL can issue COMMIT and ROLLBACK statements. If your application would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, use the DISABLE form of this clause to prevent procedures and stored functions called during your session from issuing these statements. You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the ENABLE form of this clause. Some applications (such as SQL*Forms) automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation. This is from Oracle Doc. Regards, Waleed -Original Message- Sent: Thursday, June 07, 2001 7:37 PM To: Multiple recipients of list ORACLE-L I am creating a custom report using Oracle Report Builder 6.0.8.8.3. I have created the report and they decided to register it in Oracle Application. The way the report works is that it would have to deletes all records from several custom tables and inserts records into it again. So what I did was to create a script in the BEFORE REPORT trigger that deletes all records from the table. It compiles fine but when I try to run the report it seems like it deosn't run execute the script (please see below script). I tried it too in BEFORE PARAMETER FORM and AFTER PARAMETER FORM but to no avail. PROCEDURE P_DLT_RECORDS IS BEGIN DELETE FROM AWW_RA_CUSTOMER_TRX_LINES_ALL WHERE CUSTOMER_TRX_ID IS NOT NULL; COMMIT; END; I then close the whole application and open the report. At this point it giave me an error REP-1247: Report contains uncompiles PL/SQL I then go to program unit, at this point it shows an error: "ERROR 907 at line 0, column 0 cannot load library unit APPS.AWW_RA_CUSTOMER_TRX_LINES_ALL (reference by P_DLT_RECORDS) How should I get this one fixed? Am I putting it in the wrong place? Are there other libraries that have to be loaded for this to work? When I insert records records in the custom tables can I just put it in the same trigger where I delete records. If possible please attach a sample script for deleting and inserting records. please see below the script I created in inserting records in the table. Please let me know if there are changes. INSERT INTO AWW_RA_CUSTOMER_TRX_LINES_ALL SELECT ALL AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID, SUM(AR.RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT) FROM AR.RA_CUSTOMER_TRX_LINES_ALL WHERE AR.RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE='LINE' AND AR.RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE2 IN ('WHOLESALE', 'WILL ADVISE', 'WINTER BOOKING', 'RMA') GROUP BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID ORDER BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID; Thanks in advace for the help. Mark Liggayu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Creating reports
I initially created my script without COMMMIT but it still gave me the same problem. Can you think of other cause of the problem? Thanks, Mark -Original Message- Waleed Sent: Thursday, June 07, 2001 6:02 PM To: Multiple recipients of list ORACLE-L Procedures and stored functions written in PL/SQL can issue COMMIT and ROLLBACK statements. If your application would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, use the DISABLE form of this clause to prevent procedures and stored functions called during your session from issuing these statements. You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the ENABLE form of this clause. Some applications (such as SQL*Forms) automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation. This is from Oracle Doc. Regards, Waleed -Original Message- Sent: Thursday, June 07, 2001 7:37 PM To: Multiple recipients of list ORACLE-L I am creating a custom report using Oracle Report Builder 6.0.8.8.3. I have created the report and they decided to register it in Oracle Application. The way the report works is that it would have to deletes all records from several custom tables and inserts records into it again. So what I did was to create a script in the BEFORE REPORT trigger that deletes all records from the table. It compiles fine but when I try to run the report it seems like it deosn't run execute the script (please see below script). I tried it too in BEFORE PARAMETER FORM and AFTER PARAMETER FORM but to no avail. PROCEDURE P_DLT_RECORDS IS BEGIN DELETE FROM AWW_RA_CUSTOMER_TRX_LINES_ALL WHERE CUSTOMER_TRX_ID IS NOT NULL; COMMIT; END; I then close the whole application and open the report. At this point it giave me an error REP-1247: Report contains uncompiles PL/SQL I then go to program unit, at this point it shows an error: "ERROR 907 at line 0, column 0 cannot load library unit APPS.AWW_RA_CUSTOMER_TRX_LINES_ALL (reference by P_DLT_RECORDS) How should I get this one fixed? Am I putting it in the wrong place? Are there other libraries that have to be loaded for this to work? When I insert records records in the custom tables can I just put it in the same trigger where I delete records. If possible please attach a sample script for deleting and inserting records. please see below the script I created in inserting records in the table. Please let me know if there are changes. INSERT INTO AWW_RA_CUSTOMER_TRX_LINES_ALL SELECT ALL AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID, SUM(AR.RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT) FROM AR.RA_CUSTOMER_TRX_LINES_ALL WHERE AR.RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE='LINE' AND AR.RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE2 IN ('WHOLESALE', 'WILL ADVISE', 'WINTER BOOKING', 'RMA') GROUP BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID ORDER BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID; Thanks in advace for the help. Mark Liggayu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet acc
RE: Creating reports
Are these tables in APPS schema? Is this a stored proc? If yes, who is the owner of this stored proc? Did you consider using Global Temporary Tables? Regards Waleed -Original Message- Sent: Friday, June 08, 2001 11:06 AM To: Multiple recipients of list ORACLE-L I initially created my script without COMMMIT but it still gave me the same problem. Can you think of other cause of the problem? Thanks, Mark -Original Message- Waleed Sent: Thursday, June 07, 2001 6:02 PM To: Multiple recipients of list ORACLE-L Procedures and stored functions written in PL/SQL can issue COMMIT and ROLLBACK statements. If your application would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, use the DISABLE form of this clause to prevent procedures and stored functions called during your session from issuing these statements. You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the ENABLE form of this clause. Some applications (such as SQL*Forms) automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation. This is from Oracle Doc. Regards, Waleed -Original Message- Sent: Thursday, June 07, 2001 7:37 PM To: Multiple recipients of list ORACLE-L I am creating a custom report using Oracle Report Builder 6.0.8.8.3. I have created the report and they decided to register it in Oracle Application. The way the report works is that it would have to deletes all records from several custom tables and inserts records into it again. So what I did was to create a script in the BEFORE REPORT trigger that deletes all records from the table. It compiles fine but when I try to run the report it seems like it deosn't run execute the script (please see below script). I tried it too in BEFORE PARAMETER FORM and AFTER PARAMETER FORM but to no avail. PROCEDURE P_DLT_RECORDS IS BEGIN DELETE FROM AWW_RA_CUSTOMER_TRX_LINES_ALL WHERE CUSTOMER_TRX_ID IS NOT NULL; COMMIT; END; I then close the whole application and open the report. At this point it giave me an error REP-1247: Report contains uncompiles PL/SQL I then go to program unit, at this point it shows an error: "ERROR 907 at line 0, column 0 cannot load library unit APPS.AWW_RA_CUSTOMER_TRX_LINES_ALL (reference by P_DLT_RECORDS) How should I get this one fixed? Am I putting it in the wrong place? Are there other libraries that have to be loaded for this to work? When I insert records records in the custom tables can I just put it in the same trigger where I delete records. If possible please attach a sample script for deleting and inserting records. please see below the script I created in inserting records in the table. Please let me know if there are changes. INSERT INTO AWW_RA_CUSTOMER_TRX_LINES_ALL SELECT ALL AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID, SUM(AR.RA_CUSTOMER_TRX_LINES_ALL.EXTENDED_AMOUNT) FROM AR.RA_CUSTOMER_TRX_LINES_ALL WHERE AR.RA_CUSTOMER_TRX_LINES_ALL.LINE_TYPE='LINE' AND AR.RA_CUSTOMER_TRX_LINES_ALL.INTERFACE_LINE_ATTRIBUTE2 IN ('WHOLESALE', 'WILL ADVISE', 'WINTER BOOKING', 'RMA') GROUP BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID ORDER BY AR.RA_CUSTOMER_TRX_LINES_ALL.CUSTOMER_TRX_ID; Thanks in advace for the help. Mark Liggayu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Liggayu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command fo