External Table - Excel Base Table - External Source table

To Im going to discuss how to load data from excel (CSV) to oracle table directly, In other word External tables allow users to query data that is stored outside the database in a flat file.

 Requirement

1.Database directry to park the external file.(need to create db directry in oracle as well ex. XX_STG) .here is the table script for the table

cheers !!!

CREATE TABLE XX_AR_INV_STG

(   HOTEL             VARCHAR2(30 BYTE),

    CUST_ACCOUNT_CODE VARCHAR2(30 BYTE),     

    CUST_SITE_CODE    VARCHAR2(30 BYTE),

    INVOICE_DATE      VARCHAR2(30 BYTE),

    INVOICE_NUMBER    VARCHAR2(30 BYTE),

    DISCOUNTED_AMT_WITHOUT_TAX  Number, 

    VAT_AMOUNT                  Number,  

    NBT_AMOUNT                  Number,                      

    TDL_AMOUNT                  Number,    

    SERVICE_CHRG_AMT            Number,    

    INV_TOTAL                   Number,

    TOUR_ID         VARCHAR2(30 BYTE),

    ARRIVAL_DATE    VARCHAR2(30 BYTE),

    DEPARTURE_DATE  VARCHAR2(30 BYTE),  

    PAX_COUNT       VARCHAR2(30 BYTE),

    FIT_CUST_NAME   VARCHAR2(150 BYTE), 

    REGION_CHANNL   VARCHAR2(30 BYTE) 

)

ORGANIZATION EXTERNAL

  (  TYPE ORACLE_LOADER

     DEFAULT DIRECTORY KHC_STG

     ACCESS PARAMETERS 

       ( RECORDS DELIMITED BY NEWLINE

   FIELDS TERMINATED BY ','

  )

     LOCATION (XX_STG:'xxxx.csv')

  )

REJECT LIMIT 0

NOPARALLEL

NOMONITORING;

/

alter table DMSORA.XX_AR_INV_STG reject limit unlimited;

Link between Operating Units and Inventory Organizations

SELECT   hou.NAME operating_unit_name, hou.short_code,
         hou.organization_id operating_unit_id, hou.set_of_books_id,
         hou.business_group_id,
         ood.organization_name inventory_organization_name,
         ood.organization_code inv_organization_code,
         ood.organization_id inv_organization_id, ood.chart_of_accounts_id
    FROM hr_operating_units hou, org_organization_definitions ood
   WHERE 1 = 1
     AND hou.organization_id = ood.operating_unit
     AND hou.organization_id = :operating_unit_id
ORDER BY hou.organization_id

Delete XML Template and Definition / Remove XML Template and Definition

delete   from XDO_TEMPLATES_B        where template_code    = :p_code;
/
delete   from XDO_TEMPLATES_TL       where template_code    = :p_code;
/
delete   from xdo_lobs               where lob_code         = :p_code;
/
delete   from XDO_DS_DEFINITIONS_TL  where data_source_code = :p_code;
/
delete   from XDO_DS_DEFINITIONS_b   where data_source_code = :p_code;
/

Oracle EBS - DFF Segments Values

SELECT fdfv.title, fdfv.application_table_name, fdfv.context_column_name,
               fdfcu.descriptive_flexfield_name,
               fdfcu.descriptive_flex_context_code, fdfcu.column_seq_num,
               fdfcu.application_column_name, fdfcu.end_user_column_name
   FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_descriptive_flexs_vl fdfv
 WHERE fdfv.title = 'Line Transaction Flexfield'
      AND fdfcu.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
      AND fdfcu.application_id = fdfv.application_id
 ORDER BY fdfcu.descriptive_flexfield_name,
               fdfcu.descriptive_flex_context_code,
               fdfcu.column_seq_num;

Oracle EBS R12 - How to get Application ID and Application Short name

SELECT APPLICATION_NAME,
               APPLICATION_SHORT_NAME,
               FA.APPLICATION_ID
   FROM FND_APPLICATION FA, FND_APPLICATION_TL FAT
 WHERE APPLICATION_SHORT_NAME LIKE '%AP%'
      AND FA.APPLICATION_ID = FAT.APPLICATION_ID
  ORDER BY FA.APPLICATION_ID;

Oracle EBS - How get values of Value Sets

SELECT L.FLEX_VALUE
   FROM FND_FLEX_VALUE_SETS H,
               FND_FLEX_VALUES_VL L
WHERE H.FLEX_VALUE_SET_NAME = 'XX_VALUE_SET_NAME'
     AND L.FLEX_VALUE_SET_ID   = H.FLEX_VALUE_SET_ID
     AND L.ENABLED_FLAG        = 'Y';

Oracle EBS R12 - Get value using FND_PROFILE.VALUE

SELECT fnd_profile.value('PROFILEOPTION')
      ,fnd_profile.value('MFG_ORGANIZATION_ID')
      ,fnd_profile.value('ORG_ID')
      ,fnd_profile.value('LOGIN_ID')
      ,fnd_profile.value('USER_ID')
      ,fnd_profile.value('USERNAME')
      ,fnd_profile.value('CONCURRENT_REQUEST_ID')
      ,fnd_profile.value('GL_SET_OF_BKS_ID')
      ,fnd_profile.value('SO_ORGANIZATION_ID')
      ,fnd_profile.value('APPL_SHRT_NAME')
      ,fnd_profile.value('RESP_NAME')
      ,fnd_profile.value('RESP_ID')
      ,fnd_profile.value('DEFAULT_ORG_ID')
  FROM DUAL;