Create Virtual Column in Oracle 11g Table

create table sales

  (

       sales_id      number,

       cust_id       number,

       sales_amt     number,

       sale_category varchar2(6)

       generated always as

     (

          case

            when sales_amt <= 10000 then 'LOW'

           when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'

          when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'

            else 'ULTRA'

        end

     ) virtual,

           sale_category1 varchar2(6)

       generated always as

     (

          case

            when sales_amt = 10000 then 'LOW'

            else 'ULTRA'

        end

     ) virtual

  );

INSERT RECORDS 

  insert into sales (sales_id, cust_id, sales_amt) values (1,1,100);

  

  insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500);

  

  insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000);

OUTPUT :

SALES_ID	CUST_ID	SALES_AMT	SALE_CATEGORY	SALE_CATEGORY1
1	1	100	LOW	ULTRA
2	102	1,500	LOW	ULTRA
3	102	100,000	MEDIUM	ULTRA

table properties

VIRTUAL_COLUMN	COLUMN_NAME	COLUMN_ID
NO	SALES_ID	1
NO	CUST_ID	2
NO	SALES_AMT	3
YES	SALE_CATEGORY	4
YES	SALE_CATEGORY1	5

WEBADI Table Informations

Integrator

BNE_INTEGRATORS_B

BNE_INTEGRATORS_TL

BNE_INTEGRATORS_VL

Layout

BNE_LAYOUTS_B

BNE_LAYOUTS_TL

BNE_LAYOUTS_VL

Layout Columns

BNE_LAYOUT_COLS

BNE_LAYOUT_COLS_V

Mapping

BNE_MAPPINGS_B

BNE_MAPPINGS_TL

BNE_MAPPINGS_VL

Content

BNE_CONTENTS_B

BNE_CONTENTS_TL

BNE_CONTENTS_VL

Interface

BNE_INTERFACES_B

BNE_INTERFACES_TL

BNE_INTERFACES_VL

WEBADI Interface used Package and View Query

   SELECT BIT.USER_NAME WEB_ADI, BA.ATTRIBUTE2 "PACKAGE", BCT.USER_NAME "VIEW"

  FROM BNE_INTEGRATORS_TL BIT,

       BNE_ATTRIBUTES BA,

       BNE_CONTENTS_TL BCT

 WHERE substr(BIT.INTEGRATOR_CODE,1,length(BIT.INTEGRATOR_CODE)-5)||'_P0_ATT' = BA.ATTRIBUTE_CODE

   AND BIT.APPLICATION_ID = BCT.APPLICATION_ID

   AND BIT.LANGUAGE = 'US'

   AND BCT.LANGUAGE = 'US'

   AND BA.ATTRIBUTE1 = 'PROCEDURE'

   AND BIT.APPLICATION_ID = BA.APPLICATION_ID

   AND BCT.CONTENT_CODE = substr(BIT.INTEGRATOR_CODE,1,length(BIT.INTEGRATOR_CODE)-5)||'_CNT'

   AND BIT.USER_NAME LIKE 'My Integrator Name '

   order by BIT.USER_NAME;

Query to know the Attributes(DFF Fields) Actual Column Names in Oracle tables

SELECT b.APPLICATION_TABLE_NAME,

       b.DESCRIPTIVE_FLEXFIELD_NAME,

       a.DESCRIPTIVE_FLEX_CONTEXT_CODE,

       a.APPLICATION_COLUMN_NAME,

       a.END_USER_COLUMN_NAME

  FROM FND_DESCR_FLEX_COLUMN_USAGES a,

       FND_DESCRIPTIVE_FLEXS_vl b

 WHERE   
b.APPLICATION_TABLE_NAME IN (UPPER ('<table_name>'))       
AND b.DESCRIPTIVE_FLEXFIELD_NAME = a.DESCRIPTIVE_FLEXFIELD_NAME

AND b.APPLICATION_ID = a.APPLICATION_ID

Customer contact points query

select account_number "Account Number"

     , obj.party_name "Customer Name"

     , sub.party_name "Contact Name"

     , hcp.contact_point_type || ': ' ||

       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address

                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number

                                    , 'WEB'  , hcp.url

                                    , 'Unknow contact Point Type ' || hcp.contact_point_type

             ) "How to Contact"

  from apps.hz_cust_accounts  hca

     , apps.hz_parties        obj

     , apps.hz_relationships  rel

     , apps.hz_contact_points hcp

     , apps.hz_parties        sub

 where hca.party_id           = rel.object_id

   and hca.party_id           = obj.party_id

   and rel.subject_id         = sub.party_id

   and rel.relationship_type  = 'CONTACT'

   and rel.directional_flag   = 'F'

   and rel.party_id           = hcp.owner_table_id

   and hcp.owner_table_name   = 'HZ_PARTIES'

;

select account_number "Account Number"

     , hp.party_name  "Customer Name"

     , hcp.contact_point_type || ': ' ||

       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address

                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number

                                    , 'WEB'  , hcp.url

                                    , 'Unknow contact Point Type ' || hcp.contact_point_type

             ) "How to Contact"

  from apps.hz_cust_accounts hca

     , apps.hz_parties       hp

     , apps.hz_contact_points hcp

 where hca.party_id          = hp.party_id

   and hp.party_id          = hcp.owner_table_id

   and hcp.owner_table_name = 'HZ_PARTIES'

;

select account_number "Account Number"

     , hp.party_name  "Customer Name"

     , loc.address1 ||

       decode(loc.address2, null, null, ', ' || loc.address2) ||

       decode(loc.address3, null, null, ', ' || loc.address3) ||

       decode(loc.address4, null, null, ', ' || loc.address4) ||

       ' ' || loc.city || ', ' || loc.state || ' ' || loc.postal_code "Address"

     , hcp.contact_point_type || ': ' ||

       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address

                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number

                                    , 'WEB'  , hcp.url

                                    , 'Unknow contact Point Type ' || hcp.contact_point_type

             ) "How to Contact"

  from apps.hz_cust_accounts hca

     , apps.hz_parties       hp

     , apps.hz_cust_acct_sites_all cas

     , apps.hz_contact_points      hcp

     , apps.hz_party_sites         hps

     , apps.hz_locations           loc

 where hca.cust_account_id   = cas.cust_account_id

   and hca.party_id          = hp.party_id

   and hcp.owner_table_id    = cas.party_site_id

   and hcp.owner_table_name  = 'HZ_PARTY_SITES'

   and cas.party_site_id     = hps.party_site_id

   and hps.location_id       = loc.location_id

;