---create custom object CREATE OR REPLACE TYPE KOL_UNIT_TYPE AS OBJECT ( ADDRESS_ID NUMBER (10), CUSTOMER_ID NUMBER (10), CONTACT1_FIRSTNAME VARCHAR2 (40), CONTACT1_LASTNAME VARCHAR2 (50), CONTACT1_TITLE VARCHAR2 (100), CONTACT1_TELNO VARCHAR2 (50), CONTACT1_FAXNO VARCHAR2 (50), CONTACT1_EMAIL VARCHAR2 (2000), CONTACT2_FIRSTNAME VARCHAR2 (40), CONTACT2_LASTNAME VARCHAR2 (50), CONTACT2_TITLE VARCHAR2 (100), CONTACT2_TELNO VARCHAR2 (50), CONTACT2_FAXNO VARCHAR2 (50), CONTACT2_EMAIL VARCHAR2 (2000), CONTACT3_FIRSTNAME VARCHAR2 (40), CONTACT3_LASTNAME VARCHAR2 (50), CONTACT3_TITLE VARCHAR2 (100), CONTACT3_TELNO VARCHAR2 (50), CONTACT3_FAXNO VARCHAR2 (50), CONTACT3_EMAIL VARCHAR2 (2000) ) ---create table object by type Object CREATE TYPE kol_unit AS TABLE OF kol_unit_type; ---create pipe function , important step create or replace function kol_autolinefeed(p_addrress_id in number,p_customer_id in number) return kol_unit PIPELINED as temp_str varchar2(32767):=''; temp_index number:=0; temp_count number:=0; temp_sum number:=0; -- temp_unit1 varchar2(50); -- temp_unit2 varchar2(50); -- temp_unit3 varchar2(50); -- temp_unit4 varchar2(50); Contact1_FirstName varchar2(40); Contact1_LastName varchar2(50) ; Contact1_Title varchar2(100) ; Contact1_TelNo varchar2(50) ; Contact1_FaxNo varchar2(50) ; Contact1_email varchar2(2000) ; Contact2_FirstName varchar2(40) ; Contact2_LastName varchar2(50) ; Contact2_Title varchar2(100) ; Contact2_TelNo varchar2(50) ; Contact2_FaxNo varchar2(50) ; Contact2_email varchar2(2000) ; Contact3_FirstName varchar2(40) ; Contact3_LastName varchar2(50) ; Contact3_Title varchar2(100) ; Contact3_TelNo varchar2(50) ; Contact3_FaxNo varchar2(50) ; Contact3_email varchar2(2000) ; begin select count(*) into temp_sum from AR_CONTACTS_V ac where ac.CUSTOMER_ID = p_customer_id AND ac.ADDRESS_ID = p_addrress_id; if temp_sum<=0 then pipe row(null); return; end if; for v_unit in ( SELECT AC.ADDRESS_ID, ac.customer_id, FIRST_NAME, LAST_NAME ,TITLE ,TITLE_MEANING ,MAIL_STOP ,(SELECT AP.PHONE_NUMBER FROM AR_PHONES_V AP WHERE 1=1 AND AC.REL_PARTY_ID = AP.OWNER_TABLE_ID AND UPPER(AP.PHONE_TYPE_MEANING) = 'E-MAIL' ) EMAIL ,(SELECT AP.PHONE_NUMBER FROM AR_PHONES_V AP WHERE 1=1 AND AC.REL_PARTY_ID = AP.OWNER_TABLE_ID AND UPPER(AP.PHONE_TYPE_MEANING) = 'FAX' ) FAX ,(SELECT AP.PHONE_NUMBER FROM AR_PHONES_V AP WHERE 1=1 AND AC.REL_PARTY_ID = AP.OWNER_TABLE_ID AND UPPER(AP.PHONE_TYPE_MEANING) = 'TELEPHONE' ) PHONE FROM AR_CONTACTS_V AC WHERE 1=1 AND ac.CUSTOMER_ID = p_customer_id AND ac.ADDRESS_ID = p_addrress_id) loop temp_count:=(temp_count+1); if (mod(temp_count,3)=1) then Contact1_FirstName :=v_unit.FIRST_NAME; Contact1_LastName :=v_unit.LAST_NAME; Contact1_Title :=v_unit.TITLE; Contact1_TelNo :=v_unit.PHONE; Contact1_FaxNo :=v_unit.FAX; Contact1_email :=v_unit.EMAIL; elsif (mod(temp_count,3)=2) then Contact2_FirstName :=v_unit.FIRST_NAME; Contact2_LastName :=v_unit.LAST_NAME; Contact2_Title :=v_unit.TITLE; Contact2_TelNo :=v_unit.PHONE; Contact2_FaxNo :=v_unit.FAX; Contact2_email :=v_unit.EMAIL; else Contact3_FirstName :=v_unit.FIRST_NAME; Contact3_LastName :=v_unit.LAST_NAME; Contact3_Title :=v_unit.TITLE; Contact3_TelNo :=v_unit.PHONE; Contact3_FaxNo :=v_unit.FAX; Contact3_email :=v_unit.EMAIL; end if; if (mod(temp_count,3)=0) then pipe row(kol_unit_type(p_addrress_id, p_customer_id, Contact1_FirstName, Contact1_LastName, Contact1_Title, Contact1_TelNo, Contact1_FaxNo, Contact1_email , Contact2_FirstName, Contact2_LastName, Contact2_Title, Contact2_TelNo, Contact2_FaxNo, Contact2_email , Contact3_FirstName, Contact3_LastName, Contact3_Title, Contact3_TelNo, Contact3_FaxNo, Contact3_email)); else if (temp_sum=temp_count) then if (mod(temp_count,3)=1) then pipe row(kol_unit_type(p_addrress_id, p_customer_id, Contact1_FirstName, Contact1_LastName, Contact1_Title, Contact1_TelNo, Contact1_FaxNo, Contact1_email , '', '', '', '', '', '' , '', '', '', '', '', '')); elsif (mod(temp_count,3)=2) then pipe row(kol_unit_type(p_addrress_id, p_customer_id, Contact1_FirstName, Contact1_LastName, Contact1_Title, Contact1_TelNo, Contact1_FaxNo, Contact1_email , Contact2_FirstName, Contact2_LastName, Contact2_Title, Contact2_TelNo, Contact2_FaxNo, Contact2_email , '', '', '', '', '', '')); else pipe row(kol_unit_type(p_addrress_id, p_customer_id, Contact1_FirstName, Contact1_LastName, Contact1_Title, Contact1_TelNo, Contact1_FaxNo, Contact1_email , Contact2_FirstName, Contact2_LastName, Contact2_Title, Contact2_TelNo, Contact2_FaxNo, Contact2_email , Contact3_FirstName, Contact3_LastName, Contact3_Title, Contact3_TelNo, Contact3_FaxNo, Contact3_email)); end if; end if; end if; end loop; exception when others then pipe row(null); end; -- Customer Info SELECT CUST.CUSTOMER_NUMBER, CUST.CUSTOMER_NAME, ADDR.ADDRESS1, ADDR.ADDRESS2, ADDR.ADDRESS3, ADDR.ADDRESS4, ADDR.POSTAL_CODE, ADDR.ATTRIBUTE5 REGION, CONTACTS.CONTACT1_FIRSTNAME, CONTACTS.CONTACT1_LASTNAME, CONTACTS.CONTACT1_TITLE, CONTACTS.CONTACT1_TELNO, CONTACTS.CONTACT1_FAXNO, CONTACTS.CONTACT1_EMAIL, CONTACTS.CONTACT2_FIRSTNAME, CONTACTS.CONTACT2_LASTNAME, CONTACTS.CONTACT2_TITLE, CONTACTS.CONTACT2_TELNO, CONTACTS.CONTACT2_FAXNO, CONTACTS.CONTACT2_EMAIL, CONTACTS.CONTACT3_FIRSTNAME, CONTACTS.CONTACT3_LASTNAME, CONTACTS.CONTACT3_TITLE, CONTACTS.CONTACT3_TELNO, CONTACTS.CONTACT3_FAXNO, CONTACTS.CONTACT3_EMAIL, ADDR.ATTRIBUTE10 SALESMAN, SITE.SALESPERSON, ADDR.STATUS -- CURSOR( -- SELECT -- AC.ADDRESS_ID, -- FIRST_NAME, -- LAST_NAME -- ,TITLE -- ,TITLE_MEANING -- ,MAIL_STOP -- ,(SELECT AP.PHONE_NUMBER FROM AR_PHONES_V AP WHERE 1=1 AND AC.REL_PARTY_ID = AP.OWNER_TABLE_ID AND UPPER(AP.PHONE_TYPE_MEANING) = 'E-MAIL' ) EMAIL -- ,(SELECT AP.PHONE_NUMBER FROM AR_PHONES_V AP WHERE 1=1 AND AC.REL_PARTY_ID = AP.OWNER_TABLE_ID AND UPPER(AP.PHONE_TYPE_MEANING) = 'FAX' ) FAX -- ,(SELECT AP.PHONE_NUMBER FROM AR_PHONES_V AP WHERE 1=1 AND AC.REL_PARTY_ID = AP.OWNER_TABLE_ID AND UPPER(AP.PHONE_TYPE_MEANING) = 'TELEPHONE' ) PHONE -- FROM AR_CONTACTS_V AC -- WHERE 1=1 -- -- AND AC.ADDRESS_ID = 3557 -- AND ADDR.CUSTOMER_ID = AC.CUSTOMER_ID -- AND ADDR.ADDRESS_ID = AC.ADDRESS_ID) CONTACTS ----------------- FROM -- AR_CONTACTS_V AC, --HZ_SITE_USES_V SITE, ( SELECT DISTINCT SU.SITE_USE_ID, SU.SITE_USE_CODE, SU.CUST_ACCT_SITE_ID ADDRESS_ID, SU.PRIMARY_FLAG, SU.STATUS, SU.LOCATION, SU.BILL_TO_SITE_USE_ID, SU_BILL.LOCATION BILL_TO_LOCATION, SU.PAYMENT_TERM_ID, TERM.NAME PAYMENT_TERM_NAME, SR.NAME SALESPERSON FROM HZ_CUST_SITE_USES_ALL SU, HZ_CUST_SITE_USES_ALL SU_BILL, RA_TERMS_TL TERM, JTF_RS_SALESREPS SR WHERE 1 = 1 AND SU.PRIMARY_SALESREP_ID = SR.SALESREP_ID(+) AND SU.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID(+) AND SU.PAYMENT_TERM_ID = TERM.TERM_ID(+) AND TERM.LANGUAGE = 'US' ) SITE, --AR_ADDRESSES_V ADDR, ( SELECT ADDR.CUST_ACCT_SITE_ID ADDRESS_ID, ADDR.CUST_ACCOUNT_ID CUSTOMER_ID, ADDR.STATUS STATUS, ADDR.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE, LOC.COUNTRY COUNTRY, T.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME, B.ADDRESS_STYLE ADDRESS_STYLE, LOC.ADDRESS1 ADDRESS1, LOC.ADDRESS2 ADDRESS2, LOC.ADDRESS3 ADDRESS3, LOC.ADDRESS4 ADDRESS4, LOC.CITY CITY, LOC.COUNTY COUNTY, LOC.STATE STATE, LOC.PROVINCE PROVINCE, LOC.POSTAL_CODE POSTAL_CODE, ARP_ADDR_PKG.FORMAT_ADDRESS (LOC.ADDRESS_STYLE, LOC.ADDRESS1, LOC.ADDRESS2, LOC.ADDRESS3, LOC.ADDRESS4, LOC.CITY, LOC.COUNTY, LOC.STATE, LOC.PROVINCE, LOC.POSTAL_CODE, T.TERRITORY_SHORT_NAME) CONCATENATED_ADDRESS, ADDR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, ADDR.TERRITORY_ID TERRITORY_ID, LOC.ADDRESS_KEY, ADDR.ATTRIBUTE1 ATTRIBUTE1, ADDR.ATTRIBUTE2 ATTRIBUTE2, ADDR.ATTRIBUTE3 ATTRIBUTE3, ADDR.ATTRIBUTE4 ATTRIBUTE4, ADDR.ATTRIBUTE5 ATTRIBUTE5, ADDR.ATTRIBUTE6 ATTRIBUTE6, ADDR.ATTRIBUTE7 ATTRIBUTE7, ADDR.ATTRIBUTE8 ATTRIBUTE8, ADDR.ATTRIBUTE9 ATTRIBUTE9, ADDR.ATTRIBUTE10 ATTRIBUTE10, ADDR.ATTRIBUTE11 ATTRIBUTE11, ADDR.ATTRIBUTE12 ATTRIBUTE12, ADDR.ATTRIBUTE13 ATTRIBUTE13, ADDR.ATTRIBUTE14 ATTRIBUTE14, ADDR.ATTRIBUTE15 ATTRIBUTE15, PARTY_SITE.PARTY_SITE_ID, PARTY_SITE.PARTY_ID, LOC.LOCATION_ID PARTY_LOCATION_ID, PARTY_SITE.PARTY_SITE_NUMBER SITE_NUMBER, PARTY_SITE.IDENTIFYING_ADDRESS_FLAG, ADDR.ATTRIBUTE16, ADDR.ATTRIBUTE17, ADDR.ATTRIBUTE18, ADDR.ATTRIBUTE19, ADDR.ATTRIBUTE20 FROM FND_LOOKUP_VALUES L_CAT, FND_TERRITORIES_TL T, FND_TERRITORIES B, HZ_CUST_ACCT_SITES_ALL ADDR, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC WHERE 1=1 AND L_CAT.LANGUAGE(+) = 'US' AND L_CAT.VIEW_APPLICATION_ID(+) = 222 AND L_CAT.SECURITY_GROUP_ID(+) = 0 AND B.TERRITORY_CODE = T.TERRITORY_CODE AND T.LANGUAGE = 'US' AND ADDR.CUSTOMER_CATEGORY_CODE = L_CAT.LOOKUP_CODE(+) AND L_CAT.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY' AND LOC.COUNTRY = B.TERRITORY_CODE(+) AND ADDR.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID ) ADDR, --RA_CUSTOMERS CUST ( SELECT CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID, PARTY.PARTY_ID, PARTY.PARTY_NUMBER, PARTY.PARTY_TYPE, SUBSTRB (PARTY.PARTY_NAME, 1, 50) CUSTOMER_NAME, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER, CUST_ACCT.STATUS, CUST_ACCT.CUSTOMER_TYPE FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID ) CUST ------------- , TABLE(KOL_AUTOLINEFEED(ADDR.ADDRESS_ID, ADDR.CUSTOMER_ID)) CONTACTS WHERE 1 = 1 AND CONTACTS.ADDRESS_ID = ADDR.ADDRESS_ID AND CONTACTS.CUSTOMER_ID = ADDR.CUSTOMER_ID --------------------- AND SITE.ADDRESS_ID = ADDR.ADDRESS_ID AND ADDR.CUSTOMER_ID = CUST.CUSTOMER_ID --AND (SITE.ADDRESS_ID = 3557) --AND ADDR.CUSTOMER_ID = AC.CUSTOMER_ID --AND ADDR.ADDRESS_ID = AC.ADDRESS_ID ------------------------- AND SITE.SITE_USE_CODE = 'BILL_TO' AND CUST.CUSTOMER_NAME = 'Customer 11196' AND CUST.CUSTOMER_NUMBER = 'DZD02';