BLOB field in group by
I have to include a blob type field in a group by statement. Is there a way around it? Please find select statement below. The blob type field is FND_LOBS.FILE_DATA. I appreciate all the help I can have. Thanks, Mark SELECT -- :c_q2_delivery_id c_q3_delivery_id --, :c_q2_print_all c_q3_print_all p_item_flex c_item_flex ,det.inventory_item_id c_inv_item_id , nvl(det.item_description,msi.description) c_item_description , det.customer_item_id c_customer_item_id , det.source_header_number c_so_number ,det.source_line_number c_so_line_number , det.cust_po_number c_po_number , sum ( round(nvl(det.requested_quantity,0),:p_quantity_precision)) c_req_qty , det.requested_quantity_uom c_req_qty_uom , det.requested_quantity_uom2 c_req_qty_uom2 , det.src_requested_quantity_uom c_src_req_uom , det.src_requested_quantity_uom2 c_src_req_uom2 -- src_requested_quantity remains the same for delivery details from the same source_line_id -- so we take the avg. For other columns take the sum. ,avg( round(nvl(det.src_requested_quantity,0),:p_quantity_precision) )c_src_ req_qty ,avg( round(nvl(det.src_requested_quantity2,0),:p_quantity_precision) ) c_src_req_qty2 , sum(round(nvl(det.shipped_quantity,0),:p_quantity_precision) ) c_ship_qty ,sum( round(nvl(det.shipped_quantity2,0),:p_quantity_precision)) c_ship_qty2 ,sum( round(nvl(det.delivered_quantity,0),:p_quantity_precision) ) c_del_qty ,sum( round(nvl(det.src_requested_quantity,0)-nvl(det.shipped_quantity,0)-nv l(det.requested_quantity,0),:p_quantity_precision) )c_back_qty -- this column no longer being used as it is wrong. , sum(round(nvl(det.src_requested_quantity2,0)-nvl(det.shipped_quantity2,0)-nv l(det.requested_quantity2,0),:p_quantity_precision) )c_back_qty2 -- this column should also be ot used , , det.customer_id c_customer_id , det.source_code c_src_code , det.source_header_id c_src_hdr_id , det.source_line_id c_src_line_id , det.lot_number c_lot_number , det.sublot_number c_sublot_number , det.preferred_grade c_preferred_grade , det.shipping_instructions c_ship_instructions ,FND_LOBS.FILE_DATA ,FND_DOCUMENTS_TL.MEDIA_ID ,OE_ORDER_LINES_ALL.ORIG_SYS_DOCUMENT_REF , det.packing_instructions c_pack_instructions FROM wsh_delivery_details det , wsh_delivery_assignments das ,OE_ORDER_LINES_ALL ,FND_ATTACHED_DOCUMENTS ,FND_DOCUMENTS_TL ,FND_LOBS , mtl_system_items msi WHERE det.delivery_detail_id=das.delivery_detail_id AND det.container_flag='N' --AD nvl(det.released_status, 'N') in ('Y', 'C', 'X') AND das.delivery_id=:c_q2_delivery_id AND msi.organization_id(+)=:p_organization_id AND msi.inventory_item_id(+)=det.inventory_item_id AND FND_LOBS.FILE_ID=FND_DOCUMENTS_TL.MEDIA_ID AND FND_DOCUMENTS_TL.DOCUMENT_ID=FND_ATTACHED_DOCUMENTS.DOCUMENT_ID AND FND_ATTACHED_DOCUMENTS.PK1_VALUE=OE_ORDER_LINES_ALL.LINE_ID AND OE_ORDER_LINES_ALL.LINE_ID=det.SOURCE_LINE_ID group by p_item_flex ,det.inventory_item_id , nvl(det.item_description,msi.description) , det.customer_item_id , det.source_header_number ,det.source_line_number , det.cust_po_number , det.requested_quantity_uom , det.requested_quantity_uom2 , det.src_requested_quantity_uom , det.src_requested_quantity_uom2 , det.customer_id , det.source_code , det.source_header_id , det.source_line_id ,FND_DOCUMENTS_TL.MEDIA_ID ,OE_ORDER_LINES_ALL.ORIG_SYS_DOCUMENT_REF , det.lot_number , det.sublot_number , det.preferred_grade , det.shipping_instructions , det.packing_instructions ORDER BY p_order_by -- 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).
Help Please
If I have a table that has a fields of type long, how can I move the data to a varchar2(300)? Thanks, Mark -- 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).
Extracting information from a table on another database
Hi Gurus, Can anyone tell me how to extract information from a table in another database. Please provide the syntax for it. Thanks, Mark -- 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).
Documentation for pricing database
Hi Gurus, Can anyone suggest some kind of documentation I can use to develop a pricing/costing database in Oracle? I have been looking around for a reference manula but they are quite general. Thanks, Mark -- 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: get yesterdays date
try SELECT (sysdate - 1) FROM DUAL; MARK -Original Message- Karadeniz Sent: Monday, July 23, 2001 7:27 AM To: Multiple recipients of list ORACLE-L SELECT SYSDATE - 1 from DUAL; - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, July 23, 2001 2:35 PM Anyone know how to get yesterdays date in PL/SQL? John -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Dunn 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: Bunyamin K. Karadeniz 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 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).
GETTING FIRST 250 CHARACTERS FROM A LONG DATATYPE
HI GURUS, If I have a table that has a field with long data type and I want to get the first 250 characters out of it how can I do it. I tried using SUBSTR(long data type field, 1, 250) but it complained about ORA-00932: inconsistent datatypes. I then tried TO_CHAR(SUBSTR(long data type field, 1, 250)) but still gave me the same result. Tried TO_VARCHAR2 but to no avail. Thanks, Mark -- 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).
Query on Month-to-date and Year_to_date
I am creating a report based on a couple tables. The report should show the total amount sold for the current month and another field for total amount sold Year To Date. How can I created the report so that the month to date value for a particular item is adjacent to the total amount for Year to date. Please see below for desired reports output. Item # Item DescriptionAmount Sold (Current Month) Amount Sold (YTD) 54654 Door jams 20,245.00 465,475.00 46545 Windows Casing 1500.00 124,445.57 Does anyone know what reference material I should purchase for oracle reports? Thanks, Mark -- 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).
Export from sql plus
Is it possible to export the whole content of a table to a file. If is it , what is the command to do this? Thanks, Mark -- 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).
HELP !!! scrpt works with sql plus but not in a procedure
I have this script that works fine in sql plus but when I attach it to a procedure in my report it gives me an error error 101 Encountered a symbol SELECT when expecting one of the following The error surfaces when the bolded line (choosing the date) is included in the script. What is the possible fix for this? INSERT INTO AWW_ISSUED SELECT ALL INV.MTL_MATERIAL_TRANSACTIONS.INVENTORY_ITEM_ID, SUM(INV.MTL_MATERIAL_TRANSACTIONS.TRANSACTION_QUANTITY)RECEIVED FROM INV.MTL_MATERIAL_TRANSACTIONS WHERE (INV.MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID=3 AND INV.MTL_MATERIAL_TRANSACTIONS.TRANSACTION_TYPE_ID IN (1, 31, 32, 33, 34, 36, 40, 41, 42, 18, 2, 50, 51, 12, 61, 21, 4, 80) AND INV.MTL_MATERIAL_TRANSACTIONS.TRANSACTION_QUANTITY 0) AND INV.MTL_MATERIAL_TRANSACTIONS.TRANSACTION_DATE BETWEEN '01-JUN-01' AND '30-JUN-01' AND INV.MTL_MATERIAL_TRANSACTIONS.TRANSACTION_DATE BETWEEN (SELECT TO_DATE('01' || SUBSTR(TO_CHAR(SYSDATE),3,9)) FROM DUAL)AND (SELECT LAST_DAY(SYSDATE) FROM DUAL) GROUP BY INV.MTL_MATERIAL_TRANSACTIONS.INVENTORY_ITEM_ID; I appreciate all the help -- 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).
Registering custom reports
I have a custom report that I want to register in Oracle Application 11i. Does anyone know how to do this or probably suggest a good reference . Thanks, Mark -- 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).
Oracle reports
I am creating a report based on a couple tables. The report should show the total amount sold for the current month and another field for total amount sold Year To Date. How can I created the report so that the month to date value for a particular item is adjacent to the total amount for Year to date. Please see below for desired reports output. Item # Item DescriptionAmount Sold (Current Month) Amount Sold (YTD) 54654 Door jams 20,245.00 465,475.00 46545 Windows Casing 1500.00 124,445.57 Does anyone know what reference material I should purchase for oracle reports? Thanks, Mark -- 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
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
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).
creating and dropping tables in plsql
i am creating a report that involves several tables (15) but for me to get the right result I have to create a plsql statement that creates a table that queries a couple tables. This table wil be used to create another table queried with another table and so on. I was reading the plsql section and it said that you cannot use DDL in a plsql statement. Is that right, and if it is , is there a way around it? If I could do it in a plsql statement how should I do it? thanks, mark -- 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).
Autologout of idle users
What commant should I use to automatically logout uses in oracle who has been idle for more than 30 minutes? We have UNIX as an OS. Thanks, Mark -- 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).