asked on

i am getting this error any idea single-row subquery returns more than one row

/* Formatted on 12/6/2012 10:49:49 AM (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE BOLINF.TPCO_VENDOR_USAGE_EXTRACT (
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_dir_path IN VARCHAR2)
IS
-- $ Header TPCO_VENDOR_USAGE_EXTRACT V1.1 puppalav 03-01-10 $
--
-- Purpose: Briefly explain the functionality of the Procedures
-- Get the Vendor Usage information By Dates
-- MODIFICATION HISTORY
-- Person Date Comments
-- --------- ------ ------------------------------------------
-- Vasu Puppala 08-11-08 Initial creation
-- Vasu Puppala 03-01-10 added currecncy code column

CURSOR c1 (
p_org_id NUMBER)
IS
SELECT --a.transaction_source_id,
d.segment1 part_number,
d.description,
a.unit_of_measure uom,
c.unit_price item_price, --e.category_concat_segs PO_CATEGORY_CODE,
b.segment1 po_number,
SUM (a.quantity) Quantity,
SUM (a.quantity * a.po_unit_price) PO_Value,
c.unit_price * SUM (a.quantity) receipt_value,
a.currency_code currency_code,
d.inventory_item_id, --c.category_id,
f.segment1 vendor_number,
f.vendor_name,
g.vendor_site_code vendor_site,
g.address_line1 address,
g.city,
g.state,
g.zip,
g.country,
(SELECT TPCO_stragg(b.CONCATENATED_SEGMENTS)
FROM apps.mtl_item_categories a,
apps.mtl_categories_kfv b,
apps.mtl_category_sets_vl c,
apps.mtl_system_items_vl d
WHERE a.category_id = b.category_id
AND a.category_set_id = c.category_set_id
AND c.category_set_name = 'Purchasing'
AND a.inventory_item_id = d.inventory_item_id
AND a.organization_id = d.organization_id
AND a.inventory_item_id = d.inventory_item_id
AND a.organization_id = d.organization_id
AND ROWNUM=1)
Purchasing_Category_Code,
(SELECT TPCO_stragg (description)
FROM (SELECT t.DESCRIPTION
FROM fnd_flex_values v,
fnd_flex_value_sets s,
fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME = 'PO_CATEGORY_PRIMARY'
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
AND language = 'F'
AND flex_value_meaning =
(SELECT b.segment1
FROM apps.mtl_item_categories a,
apps.mtl_categories_kfv b,
apps.mtl_category_sets_vl c,
apps.mtl_system_items_vl d
WHERE a.category_id = b.category_id
AND a.category_set_id =
c.category_set_id
AND c.category_set_name =
'Purchasing'
AND a.inventory_item_id =
d.inventory_item_id
AND a.organization_id =
d.organization_id
AND a.inventory_item_id =
d.inventory_item_id
AND a.organization_id =
d.organization_id AND ROWNUM=1)
UNION ALL
SELECT t.DESCRIPTION
FROM fnd_flex_values v,
fnd_flex_value_sets s,
fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME = 'PO_CATEGORY_SECONDARY'
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
AND language = 'F'
AND flex_value_meaning =
(SELECT b.segment2
FROM apps.mtl_item_categories a,
apps.mtl_categories_kfv b,
apps.mtl_category_sets_vl c,
apps.mtl_system_items_vl d
WHERE a.category_id = b.category_id
AND a.category_set_id =
c.category_set_id
AND c.category_set_name =
'Purchasing'
AND a.inventory_item_id =
d.inventory_item_id
AND a.organization_id =
d.organization_id
AND a.inventory_item_id =
d.inventory_item_id
AND a.organization_id =
d.organization_id AND ROWNUM=1 )
UNION ALL
SELECT t.DESCRIPTION
FROM fnd_flex_values v,
fnd_flex_value_sets s,
fnd_flex_values_tl t
WHERE FLEX_VALUE_SET_NAME = 'PO_CATEGORY_EXTENDED'
AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
AND language = 'F'
AND flex_value_meaning =
(SELECT b.segment3
FROM apps.mtl_item_categories a,
apps.mtl_categories_kfv b,
apps.mtl_category_sets_vl c,
apps.mtl_system_items_vl d
WHERE a.category_id = b.category_id
AND a.category_set_id =
c.category_set_id
AND c.category_set_name =
'Purchasing'
AND a.inventory_item_id =
d.inventory_item_id
AND a.organization_id =
d.organization_id
AND a.inventory_item_id =
d.inventory_item_id
AND a.organization_id =
d.organization_id AND ROWNUM=1 )))
Purchasing_Cat_description,
(SELECT TPCO_stragg(a.segment1)
FROM apps.mtl_item_catalog_groups_B a,
apps.mtl_system_items_fvl b
WHERE 1 = 1
--c.segment1='TPC Product Catalog'
AND a.item_catalog_group_id = b.item_catalog_group_id
AND b.inventory_item_id = d.inventory_item_id
AND b.organization_id = d.organization_id
AND ROWNUM = 1)
Engineering_Catalog,
g.vendor_site_id,
c.category_id,
a.organization_id
--h.first_name||' '||h.middle_name||' '||h.last_name Contact_Name,h.area_code||h.phone Phone_Number,h.email_address,h.fax_area_code||h.fax Fax_Number
FROM apps.RCV_TRANSACTIONS a,
apps.po_headers b,
apps.po_lines c,
apps.mtl_system_items_b d, --apps.MTL_ITEM_CATEGORIES_V e,
apps.po_vendors f,
apps.po_vendor_sites g --,po_vendor_contacts h
WHERE b.po_header_id = c.po_header_id
AND a.po_line_id = c.po_line_id
AND a.po_header_id = c.po_header_id
AND c.item_id = d.inventory_item_id
AND a.organization_id = d.organization_id
AND b.org_id = p_org_id
AND b.org_id = g.org_id
AND a.transaction_type IN
('RETURN TO VENDOR', 'RECEIVE', 'CORRECT')
AND a.source_document_code = 'PO'
--and c.category_id =e.category_id
--and d.inventory_item_id = e.inventory_item_id
--and e.category_set_name = 'Purchasing'
AND b.vendor_id = f.vendor_id
AND a.vendor_id = b.vendor_id
AND a.vendor_site_id = b.vendor_site_id
AND b.vendor_site_id = g.vendor_site_id
AND f.vendor_id = g.vendor_id
--and g.vendor_site_id = h.vendor_site_id(+)
AND TRUNC (a.transaction_date) >= p_start_date
AND TRUNC (a.transaction_date) <= p_end_date
GROUP BY d.segment1,
a.currency_code,
d.description,
a.unit_of_measure,
b.segment1,
c.unit_price,
b.segment1,
d.inventory_item_id,
f.segment1,
f.vendor_name,
g.vendor_site_code,
g.address_line1,
g.city,
g.state,
g.zip,
g.country,
g.vendor_site_id,
c.category_id,
a.organization_id,
d.organization_id; --,

CURSOR c2 (
p_vendor_site_id IN NUMBER,
p_date IN VARCHAR2)
IS
SELECT h.first_name || ' ' || h.middle_name || ' ' || h.last_name
Contact_Name,
h.area_code || h.phone Phone_Number,
h.email_address,
h.fax_area_code || h.fax Fax_Number
FROM apps.po_vendor_contacts h
WHERE TO_CHAR (h.creation_date, 'DD-MON-YYYY HH24:MI') = p_date
AND h.vendor_site_id = p_vendor_site_id
AND h.phone IS NOT NULL;

l_date VARCHAR2 (1000);
l_contact_name VARCHAR2 (1000);
l_phone_number VARCHAR2 (1000);
l_email VARCHAR2 (1000);
l_phone VARCHAR2 (1000);
l_fax VARCHAR2 (1000);
l_con_segs VARCHAR2 (1000);
l_name VARCHAR2 (1000);
l_org_id NUMBER;
v_file UTL_FILE.file_type;
c_filename VARCHAR2 (1000)
:= TO_CHAR (SYSDATE, 'DDMMYYYYHHMMSS')
|| fnd_global.conc_request_id
|| '.txt';
l_string1 VARCHAR2 (2000);
--l_name Varchar2(50);
l_org_name VARCHAR2 (1000);
BEGIN
l_org_id := fnd_profile.VALUE ('ORG_ID');
c_filename := 'TPCO_VENDOR_USAGE_EXTRACT_' || c_filename;

IF UTL_FILE.is_open (v_file)
THEN
UTL_FILE.fclose (v_file);
END IF;

v_file :=
UTL_FILE.fopen (p_dir_path,
c_filename,
'W',
32767);

UTL_FILE.
put_line (
v_file,
'PART_NUMBER'
|| CHR (9)
|| 'DESCRIPTION'
|| CHR (9)
|| 'UOM'
|| CHR (9)
|| 'ITEM_PRICE'
|| CHR (9)
|| 'PO_CATEGORY_CODE'
|| CHR (9)
|| 'PO_NUMBER'
|| CHR (9)
|| 'QUANTITY'
|| CHR (9)
|| 'PO_VALUE'
|| CHR (9)
|| 'CURRECY'
|| CHR (9)
|| 'ORGANIZATION'
|| CHR (9)
|| 'VENDOR_NUMBER'
|| CHR (9)
|| 'VENDOR_NAME'
|| CHR (9)
|| 'VENDOR_SITE'
|| CHR (9)
|| 'ADDRESS'
|| CHR (9)
|| 'CITY'
|| CHR (9)
|| 'STATE'
|| CHR (9)
|| 'ZIP'
|| CHR (9)
|| 'COUNTRY'
|| CHR (9)
|| 'PURCHASING_CATEGORY_CODE'
|| CHR (9)
|| 'PURCHASING_CAT_DESCRIPTION'
|| CHR (9)
|| 'ENGINEERING_CATALOG'
|| CHR (9)
|| 'CONTACT_NAME'
|| CHR (9)
|| 'EMAIL'
|| CHR (9)
|| 'PHONE'
|| CHR (9)
|| 'FAX');
fnd_file.
put_line (
fnd_file.output,
'PART_NUMBER'
|| CHR (9)
|| 'DESCRIPTION'
|| CHR (9)
|| 'UOM'
|| CHR (9)
|| 'ITEM_PRICE'
|| CHR (9)
|| 'PO_CATEGORY_CODE'
|| CHR (9)
|| 'PO_NUMBER'
|| CHR (9)
|| 'QUANTITY'
|| CHR (9)
|| 'PO_VALUE'
|| CHR (9)
|| 'CURRECY'
|| CHR (9)
|| 'ORGANIZATION'
|| CHR (9)
|| 'VENDOR_NUMBER'
|| CHR (9)
|| 'VENDOR_NAME'
|| CHR (9)
|| 'VENDOR_SITE'
|| CHR (9)
|| 'ADDRESS'
|| CHR (9)
|| 'CITY'
|| CHR (9)
|| 'STATE'
|| CHR (9)
|| 'ZIP'
|| CHR (9)
|| 'COUNTRY'
|| CHR (9)
|| 'PURCHASING_CATEGORY_CODE'
|| CHR (9)
|| 'PURCHASING_CAT_DESCRIPTION'
|| CHR (9)
|| 'ENGINEERING_CATALOG'
|| CHR (9)
|| 'CONTACT_NAME'
|| CHR (9)
|| 'EMAIL'
|| CHR (9)
|| 'PHONE'
|| CHR (9)
|| 'FAX');

FOR c1rec IN c1 (l_org_id)
LOOP
l_contact_name := '';
l_phone_number := '';
l_email := '';
l_phone := '';
l_fax := '';
l_con_segs := '';
l_name := '';

BEGIN
SELECT DISTINCT MIN (TO_CHAR (creation_date, 'DD-MON-YYYY HH24:MI'))
INTO l_date
FROM apps.po_vendor_contacts
WHERE vendor_site_id = c1rec.vendor_site_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.
put_line (fnd_file.LOG, 'Error getting Contact Date' || SQLERRM);
RAISE;
END;

--DBMS_OUTPUT.put_line ('Category'||c1rec.category_id);
OPEN c2 (c1rec.vendor_site_id, l_date);

FETCH c2
INTO l_contact_name, l_phone_number, l_email, l_fax;

CLOSE c2;

BEGIN
SELECT DISTINCT category_concat_segs
INTO l_con_segs
FROM apps.mtl_categories_v
WHERE category_id = c1rec.category_id;
--and inventory_item_id =c1rec.inventory_item_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.
put_line (
fnd_file.LOG,
'Error getting for Category info'
|| c1rec.category_id
|| ':'
|| c1rec.po_number
|| ':'
|| c1rec.inventory_item_id);
RAISE;
END;

BEGIN
SELECT name
INTO l_name
FROM hr_all_organization_units
WHERE organization_id = c1rec.organization_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.
put_line (
fnd_file.LOG,
'Error Getting Organization: ' || c1rec.organization_id);
RAISE;
END;

UTL_FILE.
put_line (
v_file,
c1rec.PART_NUMBER
|| CHR (9)
|| c1rec.DESCRIPTION
|| CHR (9)
|| c1rec.uom
|| CHR (9)
|| c1rec.ITEM_PRICE
|| CHR (9)
|| l_con_segs
|| CHR (9)
|| c1rec.PO_NUMBER
|| CHR (9)
|| c1rec.QUANTITY
|| CHR (9)
|| c1rec.PO_VALUE
|| CHR (9)
|| c1rec.currency_code
|| CHR (9)
|| l_name
|| CHR (9)
|| c1rec.VENDOR_NUMBER
|| CHR (9)
|| c1rec.VENDOR_NAME
|| CHR (9)
|| c1rec.VENDOR_SITE
|| CHR (9)
|| c1rec.ADDRESS
|| CHR (9)
|| c1rec.CITY
|| CHR (9)
|| c1rec.STATE
|| CHR (9)
|| c1rec.ZIP
|| CHR (9)
|| c1rec.COUNTRY
|| CHR (9)
|| c1rec.PURCHASING_CATEGORY_CODE
|| CHR (9)
|| c1rec.PURCHASING_CAT_DESCRIPTION
|| CHR (9)
|| c1rec.ENGINEERING_CATALOG
|| CHR (9)
|| l_CONTACT_NAME
|| CHR (9)
|| l_EMAIL
|| CHR (9)
|| l_PHONE_NUMBER
|| CHR (9)
|| l_FAX);
fnd_file.
put_line (
fnd_file.output,
c1rec.PART_NUMBER
|| CHR (9)
|| c1rec.DESCRIPTION
|| CHR (9)
|| c1rec.uom
|| CHR (9)
|| c1rec.ITEM_PRICE
|| CHR (9)
|| l_con_segs
|| CHR (9)
|| c1rec.PO_NUMBER
|| CHR (9)
|| c1rec.QUANTITY
|| CHR (9)
|| c1rec.PO_VALUE
|| CHR (9)
|| c1rec.currency_code
|| CHR (9)
|| l_name
|| CHR (9)
|| c1rec.VENDOR_NUMBER
|| CHR (9)
|| c1rec.VENDOR_NAME
|| CHR (9)
|| c1rec.VENDOR_SITE
|| CHR (9)
|| c1rec.ADDRESS
|| CHR (9)
|| c1rec.CITY
|| CHR (9)
|| c1rec.STATE
|| CHR (9)
|| c1rec.ZIP
|| CHR (9)
|| CHR (9)
|| c1rec.COUNTRY
|| CHR (9)
|| c1rec.PURCHASING_CATEGORY_CODE
|| CHR (9)
|| c1rec.PURCHASING_CAT_DESCRIPTION
|| CHR (9)
|| c1rec.ENGINEERING_CATALOG
|| CHR (9)
|| l_CONTACT_NAME
|| CHR (9)
|| l_EMAIL
|| CHR (9)
|| l_PHONE_NUMBER
|| CHR (9)
|| l_FAX);
END LOOP;

UTL_FILE.fclose (v_file);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error in Main' || SQLERRM);
RAISE;
END;
/

TPCo_STRAGG converts multiple rows in one column i thought this will take care of the above error

slightwv (䄆 Netminder)

Could it be this one:
SELECT DISTINCT MIN (TO_CHAR (creation_date, 'DD-MON-YYYY HH24:MI')) INTO l_date

If two dates are the same, min will retirn more than one row.

If that isn't it, I'm afraid you will probably have to troubleshoot this on your own. Start with all the inner selects and work your way back out until you find the offending select.

ASKER CERTIFIED SOLUTION

paquicuba

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

slightwv (䄆 Netminder)

>>ORA-01422: exact fetch returns more than requested number of rows.

Good point. I wasn't sure the exact error and didn't set up a test case to find out.

Gerwin Jansen

What is the output of each SELECT TPCO_stragg( in the first cursor definition?

johnsone

Based on the error message and the queries, I would think this is in the main cursor (c1).

One of the two subselects that are listed in the select list are returning more than one row. I believe that is about the only thing in your procedure where you could get that message.

Given that 2 of them are pretty simple and contain a rownum restriction to limit it to one row, I believe it is this one that is causing more than one row to be returned.

               (SELECT TPCO_stragg (description)
                  FROM (SELECT t.DESCRIPTION
                          FROM fnd_flex_values v,
                               fnd_flex_value_sets s,
                               fnd_flex_values_tl t
                         WHERE     FLEX_VALUE_SET_NAME = 'PO_CATEGORY_PRIMARY'
                               AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
                               AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
                               AND language = 'F'
                               AND flex_value_meaning =
                                      (SELECT b.segment1
                                         FROM apps.mtl_item_categories a,
                                              apps.mtl_categories_kfv b,
                                              apps.mtl_category_sets_vl c,
                                              apps.mtl_system_items_vl d
                                        WHERE a.category_id = b.category_id
                                              AND a.category_set_id =
                                                     c.category_set_id
                                              AND c.category_set_name =
                                                     'Purchasing'
                                              AND a.inventory_item_id =
                                                     d.inventory_item_id
                                              AND a.organization_id =
                                                     d.organization_id
                                              AND a.inventory_item_id =
                                                     d.inventory_item_id
                                              AND a.organization_id =
                                                     d.organization_id AND ROWNUM=1)
                        UNION ALL
                        SELECT t.DESCRIPTION
                          FROM fnd_flex_values v,
                               fnd_flex_value_sets s,
                               fnd_flex_values_tl t
                         WHERE FLEX_VALUE_SET_NAME = 'PO_CATEGORY_SECONDARY'
                               AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
                               AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
                               AND language = 'F'
                               AND flex_value_meaning =
                                      (SELECT b.segment2
                                         FROM apps.mtl_item_categories a,
                                              apps.mtl_categories_kfv b,
                                              apps.mtl_category_sets_vl c,
                                              apps.mtl_system_items_vl d
                                        WHERE a.category_id = b.category_id
                                              AND a.category_set_id =
                                                     c.category_set_id
                                              AND c.category_set_name =
                                                     'Purchasing'
                                              AND a.inventory_item_id =
                                                     d.inventory_item_id
                                              AND a.organization_id =
                                                     d.organization_id
                                              AND a.inventory_item_id =
                                                     d.inventory_item_id
                                              AND a.organization_id =
                                                     d.organization_id AND ROWNUM=1 )
                        UNION ALL
                        SELECT t.DESCRIPTION
                          FROM fnd_flex_values v,
                               fnd_flex_value_sets s,
                               fnd_flex_values_tl t
                         WHERE     FLEX_VALUE_SET_NAME = 'PO_CATEGORY_EXTENDED'
                               AND s.FLEX_VALUE_SET_ID = v.FLEX_VALUE_SET_ID
                               AND t.FLEX_VALUE_ID = v.FLEX_VALUE_ID
                               AND language = 'F'
                               AND flex_value_meaning =
                                      (SELECT b.segment3
                                         FROM apps.mtl_item_categories a,
                                              apps.mtl_categories_kfv b,
                                              apps.mtl_category_sets_vl c,
                                              apps.mtl_system_items_vl d
                                        WHERE a.category_id = b.category_id
                                              AND a.category_set_id =
                                                     c.category_set_id
                                              AND c.category_set_name =
                                                     'Purchasing'
                                              AND a.inventory_item_id =
                                                     d.inventory_item_id
                                              AND a.organization_id =
                                                     d.organization_id
                                              AND a.inventory_item_id =
                                                     d.inventory_item_id
                                              AND a.organization_id =
                                                     d.organization_id AND ROWNUM=1 )))

Open in new window

There seems to be multiple queries connected with UNION ALL and if more than one of them returns a row, then it looks like you will get multiple rows.

pardeshirahul

ASKER

Hi somehow the ROWNUM=1 inside the subquery ios working fine; i will close the question
thanks for your feedback