RICE

here i am defining the RICE components

R--------------------------> Reports
I--------------------------> Inter faces(inbound& outbound)
C--------------------------> Conversions
E--------------------------> Extensions.

Reports are developed by using Report builder and also we can do customization of reports. customization means doing some modification of already existing report. Reports 10g is the latest version for these the data base is oracle 10g for 6i oracle 9i.
Interfaces:  Basically interfaces are two types one is inbound and another one is out bound interface. Inbound interface means inserting the data from legacy system(Main frames,SAP,People Soft) into oracle base tables. This inbound interface can be done in two types one is open interface and another one is closed interface.
Conversion: conversion it is also same like as inbound interface but it can be done only once. Mainly this conversion can be done when the client wants to upgrade the version of ERP  or completely shifted from legacy system to ERP then that conversions can be done.
Extension: Extension means adding the some thing to already exist once and it comes for all means Reports , Interfaces and Conversions.

vendor contacts extraction

 SELECT  30 record_id
               ,vendor_site_id
               ,vendor_contact_id
               ,first_name
               ,NVL(last_name,'UNKNOWN')last_name
               ,title
               ,mail_stop mail_stop
               ,LTRIM(NVL(DECODE(area_code,NULL,phone,area_code||'-'||phone),NULL))phone
               ,SUBSTR(email_address,1,180)email_address
               ,SUBSTR(url,1,180)url
               ,LTRIM(NVL(DECODE(fax_area_code,NULL,fax,fax_area_code||'-'||fax),NULL))fax
               ,TO_CHAR(inactive_date,'DDMMYYYY')inactive_date
          FROM  po_vendor_contacts
         WHERE  last_name IS NOT NULL
           AND  vendor_site_id = p_vendor_site_id
      ORDER BY  vendor_contact_id;

vendor sites extraction

SELECT  20 record_id
               ,povs.vendor_id
               ,povs.vendor_site_id
               ,povs.vendor_site_code
               ,povs.org_id
               ,povs.purchasing_site_flag
               ,povs.pay_site_flag
               ,povs.address_line1
               ,povs.address_line2
               ,povs.address_line3
               ,povs.city
               ,povs.state
               ,povs.zip
               ,povs.province province
               ,povs.country
               ,LTRIM(NVL(DECODE(povs.area_code,NULL,povs.phone,povs.area_code||'-'||povs.phone),NULL))phone
               ,LTRIM(NVL(DECODE(povs.fax_area_code,NULL,povs.fax,povs.fax_area_code||'-'||povs.fax),NULL))site_fax  --M4
               ,povs.terms_id
               ,apt.name
               ,povs.payment_priority
               ,povs.invoice_amount_limit
               ,povs.payment_method_lookup_code
               ,povs.attribute8  PO_TRANSMISSION_TYPE
               ,TO_CHAR(povs.inactive_date,'DDMMYYYY')inactive_date
          FROM  po_vendor_sites_all povs,
                ap_terms apt
         WHERE  povs.terms_id= apt.term_id(+)

vendor extraction

 SELECT  10 record_id
               ,P.vendor_id
               ,P.vendor_name    vendor_name
               ,P.segment1
               ,P.enabled_flag
               ,TO_CHAR(P.creation_date,'DDMMYYYY')creation_date
               ,P.vendor_type_lookup_code
               ,P.terms_id
               ,a.name --payment_terms
               ,P.payment_priority
               ,P.invoice_amount_limit
               ,P.payment_method_lookup_code
               ,P.num_1099 num_1099
               ,vat_registration_num vat_registration_num
               ,TO_CHAR(P.start_date_active,'DDMMYYYY')start_date_active
               ,TO_CHAR(P.end_date_active,'DDMMYYYY')end_date_active
               ,hold_all_payments_flag Hold_All_Payments   --<M1>
               ,hold_flag Purchase_Order_Hold              --<M1>
               --,p.end_date_active end_date               --<M1> <M2>
               ,SUBSTR(P.attribute11,1,INSTR(P.attribute11,'.',1,1)-1) business      --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,1)+1,(INSTR (P.attribute11,'.',1,2)- INSTR (P.attribute11,'.',1,1))),'.','')disabled              --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,2)+1,(INSTR (P.attribute11,'.',1,3)- INSTR (P.attribute11,'.',1,2))),'.','')disabled_veteran      --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,3)+1,(INSTR (P.attribute11,'.',1,4)- INSTR (P.attribute11,'.',1,3))),'.','')disadv_business       --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,4)+1,(INSTR (P.attribute11,'.',1,5)- INSTR (P.attribute11,'.',1,4))),'.','')hub_zone              --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,5)+1,(INSTR (P.attribute11,'.',1,6)- INSTR (P.attribute11,'.',1,5))),'.','')nmsdc                 --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,6)+1,(INSTR (P.attribute11,'.',1,7)- INSTR (P.attribute11,'.',1,6))),'.','')small_disadv_business --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,7)+1,(INSTR (P.attribute11,'.',1,8)- INSTR (P.attribute11,'.',1,7))),'.','')cali_utility_comm     --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,8)+1,(INSTR (P.attribute11,'.',1,9)- INSTR (P.attribute11,'.',1,8))),'.','')local_muni            --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,9)+1,(INSTR (P.attribute11,'.',1,10)- INSTR (P.attribute11,'.',1,9))),'.','')small_business_enterprise   --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,10)+1,(INSTR (P.attribute11,'.',1,11)- INSTR (P.attribute11,'.',1,10))),'.','')state              --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,11)+1,(INSTR (P.attribute11,'.',1,12)- INSTR (P.attribute11,'.',1,11))),'.','')veteran            --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,12)+1,(INSTR (P.attribute11,'.',1,13)- INSTR (P.attribute11,'.',1,12))),'.','')wbenc              --<M1>
               ,REPLACE(SUBSTR(P.attribute11,INSTR(P.attribute11,'.',1,13)+1),'.','')other   --<M1>
               ,p.invoice_currency_code --<M5>
               ,P.attribute14 Commodity_code --<M6>
               ,p.attribute3          non_po_supplier_flag   --<M7>  
               ,p.attribute1          supplier_creator       --<M8>
      ,p.last_update_date    last_updatedate        --<M9>
               ,(CASE WHEN tmp.cnt = 0 THEN 'N'ELSE 'Y'END) ISP_SUPPLIER_FLAG
          FROM  po_vendors P
               ,ap_terms a
               ,(SELECT COUNT(1) cnt
                 FROM po_vendor_sites_all
                 WHERE vendor_id = p.vendor_id
                 AND vendor_site_code = '0 ISP') tmp
         WHERE  P.terms_id  = a.term_id(+)