Oracle APPS Consultancy

Monday, 19 June 2017

When data will be inserted into wsh_delivery_details and wsh_delivery_assignments

At the time of book order records will be inserted into wsh_delivery_details with released_status =’R’ (Ready to Release) and wsh_delivery_assignments

What is maximum number of levels in BOM?

Maximum bill can be defined is 60.

Saturday, 3 June 2017

How we can skip header records while loading?

By using the "SKIP=n" keyword. "n" specifies the number of logical rows to skip.

i.e. SKIP=1

How to Load Multiple Input Files into Multiple Tables on Conditionally basis?

Example:

OPTIONS (SKIP=1,READSIZE=1000000, BINDSIZE=1000000, ROWS=200,ERRORS=200000  )

LOAD DATA

INFILE '/erpesed1/erpapp/fs2/EBSapps/appl/geps/gepsont/1.0.0/in/1309751.txt'

INFILE '/erpesed1/erpapp/fs2/EBSapps/appl/geps/gepsont/1.0.0/in/1309752.txt'

INFILE '/erpesed1/erpapp/fs2/EBSapps/appl/geps/gepsont/1.0.0/in/1309753.txt'

TRUNCATE

INTO TABLE geps_price_list_stg

WHEN Product_value = '1154903'

FIELDS TERMINATED BY "|"

TRAILING NULLCOLS

Price_List_Name           "TRIM(:Price_List_Name)",

Product_Context           "TRIM(:Product_Context)",

Product_Attribute         "TRIM(:Product_Attribute)",

Product_value             "TRIM(:Product_value)",

Product_Description       "TRIM(:Product_Description)",

UOM                       "TRIM(:UOM)",

Primary_UOM               "TRIM(:Primary_UOM)",

Line_Type                 "TRIM(:Line_Type)",

price_break_type          "TRIM(:price_break_type)",

Application_Method        "TRIM(:Application_Method)",

Value_uom                 "TRIM(ROUND(:VALUE_UOM, 2))",

Dynamic_Formula           "TRIM(:Dynamic_Formula)",

Static_Formula            "TRIM(:Static_Formula)",

Start_Date                "TRIM(:Start_Date)",

End_Date                  "TRIM(:End_Date)",

Precedence                "TRIM(:Precedence)",

process_flag              "TRIM(:process_flag)",

TRANSACTION_TYPE          "TRIM(:TRANSACTION_TYPE)"

INTO TABLE geps_price_list_stg_sss

WHEN Product_value != '1154903'

FIELDS TERMINATED BY "|"

TRAILING NULLCOLS

Price_List_Name POSITION(1)        "TRIM(:Price_List_Name)",

Product_Context           "TRIM(:Product_Context)",

Product_Attribute         "TRIM(:Product_Attribute)",

Product_value             "TRIM(:Product_value)",

Product_Description       "TRIM(:Product_Description)",

UOM                       "TRIM(:UOM)",

Primary_UOM               "TRIM(:Primary_UOM)",

Line_Type                 "TRIM(:Line_Type)",

price_break_type          "TRIM(:price_break_type)",

Application_Method        "TRIM(:Application_Method)",

Value_uom                 "TRIM(ROUND(:VALUE_UOM, 2))",

Dynamic_Formula           "TRIM(:Dynamic_Formula)",

Static_Formula            "TRIM(:Static_Formula)",

Start_Date                "TRIM(:Start_Date)",

End_Date                  "TRIM(:End_Date)",

Precedence                "TRIM(:Precedence)",

process_flag              "TRIM(:process_flag)",

TRANSACTION_TYPE          "TRIM(:TRANSACTION_TYPE)"

How to upload one data file into multiple tables by using SQL Loader in oracle?

When the POSITION parameter is not used, multiple INTO TABLE clauses process different parts of the same (delimited data) input record, allowing multiple tables to be loaded from one record. When the POSITION parameter is used, multiple INTO TABLE clauses can process the same record in different ways, allowing multiple formats to be recognized in one input file

POSITION is needed to reset the pointer to the beginning of the record again. In delimited formats, use "POSITION(1)" after the first column to reset the pointer.