BICC PVO job issues in ODI 12c

 

#ERROR1:

Caused By: javax.script.ScriptException: javax.script.ScriptException: java.lang.InterruptedException: sleep interrupted

Solution:

BICC extract script failing in the timespane

Go to mapping and select LKM LKM BICC ADW Extrernal table-- BICC job timeout- 4000 or 1000 or 0

#ERROR2:

ODI-1228: Task Merge rows-IKM Oracle Merge-Load TGT_FSCMTOPMODELAM_FINVRMRRSHAREDPUBLICMODELAM_PERFOBLIGATIONLINDISTSPVO_1 fails on the target connection .

Caused By: java.sql.SQLException: ORA-30926: unable to get a stable set of rows in the source tables

Solution:

use ikm oracle incremental update merge instead of ikm oracle merge

Check primary key enabled in mapping .

If same data trying to insert /update on target table  which is already exist in target table then it will not allow to update or insert using merge

delete data from target or source based on requirement

Delete files from object storage in odi package

 Delete files from object storage using odi object

configure oracle object storage technology in topology 

create package

drag the OdiObjectStorageDelete object

add details for target logical schema, filter file names 

save and execute

BICC Reset to Full Extract by Offering ID using groovy script

 BICC Reset to Full Extract by Offering ID using groovy script

ODI procedure: 

import oracle.odi.core.OdiInstance

import java.sql.Connection

import java.sql.Statement

import java.sql.ResultSet

import groovy.json.*

import java.net.URL

import java.net.HttpURLConnection

import org.apache.http.HttpEntity;

import org.apache.http.HttpResponse;

import org.apache.http.StatusLine;

import org.apache.http.HttpHost;

import org.apache.http.client.CredentialsProvider;

import org.apache.http.client.methods.HttpPost;

import org.apache.http.auth.Credentials;

import org.apache.http.auth.UsernamePasswordCredentials;

import org.apache.http.auth.AuthScope;

import org.apache.http.impl.client.BasicCredentialsProvider;

import org.apache.http.impl.client.CloseableHttpClient;

import org.apache.http.impl.client.HttpClientBuilder;

import org.apache.http.entity.ByteArrayEntity;

import org.apache.http.entity.ContentType;

import org.apache.http.util.EntityUtils;

import javax.xml.stream.XMLInputFactory;

import javax.xml.stream.XMLStreamConstants;

import javax.xml.stream.XMLStreamReader;

// Enter username, pwd

username="XX_SVC_BICC"

//password="************"

//password="<@=odiRef.getInfo ("BICC_CRM")@>"

password = "<@=odiRef.getDataServerInfo("BICC_PASSWORD", "BICC_CRM") @>"

// Create Connection object to the schema

Connection con = odiRef.getJDBCConnectionFromLSchema( "<%=odiRef.getOption("DB_SCHEMA")%>", "<%=odiRef.getOption("ODI_CONTEXT")%>" )

// Create SQL Statement

Statement stmt = con.createStatement()

 

// Credentials for basic authorization need to be passed through as username:pwd

userCredentials = username + ":" + password;

// Basic authorization is set up, but the encoding for credentials need to be changed.

basicAuth = 'Basic '+javax.xml.bind.DatatypeConverter.printBase64Binary(userCredentials.getBytes())

// Get the Encoding

String encoding = Base64.getEncoder().encodeToString(userCredentials.getBytes("utf-8"))

  

sql = "select distinct OFFERING_ID from " + "<%=odiRef.getOption("DB_SCHEMA")%>" + "." + "<%=odiRef.getOption("BICC_OFFERING_DATASTORE_TABLE")%>" 

    

try {

  ResultSet rs = stmt.executeQuery( sql )

  while (rs.next()) {

    println('--------------------------------------------------------------------')

    println('Reset to Full Extract for Offering: ' + rs.getString("OFFERING_ID"))

          

    address = "https://ea-" + "<%=odiRef.getOption("BICC_INSTANCE")%>" + ".fa.us2.oraclecloud.com/biacm/rest/meta/offerings/"+ rs.getString("OFFERING_ID") +"/actions/resetToFull"

    println(address)

    urlInfo = address.toURL()                                            // Variable converted to URL

      

    connection = urlInfo.openConnection()

    

    connection.setRequestMethod("POST")

    connection.setRequestProperty("Accept", "application/json")

    connection.setRequestProperty("Content-Type", "application/json")    

    connection.setDoInput(true)

    connection.setDoOutput(true)

    connection.setRequestProperty("Content-Length", encoding.length().toString())    

    connection.setRequestProperty("Authorization", 'Basic '+encoding)

    

    DataOutputStream writer = new DataOutputStream(connection.getOutputStream())

    writer.writeBytes("REPLACE ME WITH DATA TO BE WRITTEN")

    writer.flush()

    println "Connection Class: " + connection.class

    int respCode = connection.getResponseCode()

    println "Response Code/Message: " + respCode

    connection.disconnect()

  }

} finally {

  if (stmt != null) { stmt.closeOnCompletion() }

}

Load Data From ObjectStorage to ADW using groovy script in ODI procedure

 Load Data From ObjectStorage to ADW using groovy script in ODI procedure

ODI procedure: Groovy script 

// Export URIS list

sourceDataStore = "FscmTopModelAM.ScmExtractAM.EgpBiccExtractAM.ItemExtractPVO"

filePrefix = "file_" + sourceDataStore.toLowerCase().replaceAll("\\.","_") + "-"

sql = "SELECT object_name, bytes FROM table(dbms_cloud.list_objects(credential_name => 'ODI',location_uri => 'https://bjectstorage.us1.oraclecloud.com/v1/compsvc/bucket-DevSAASExtract-5001/')) WHERE object_name LIKE '${filePrefix}%'"

summaryMessage = ""

summaryMessage = "Query :\n"

summaryMessage = summaryMessage + sql

odiRef.setSummaryMessage(summaryMessage)

con = odiRef.getJDBCConnection( "DEST" )

try {

    stmt = con.createStatement()

    result = stmt.executeQuery( sql )

} finally {

    if (stmt != null) { stmt.closeOnCompletion() }

}

summaryMessage = summaryMessage + "\n\nExtract files:\n"

uris = ""

if (!result.isBeforeFirst() && result.getRow() == 0) {

} else {

  while (result.next()) {

    uris = uris + 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/compsvc/b/bucket-DevSAASExtract-5001/o/' + result.getString(1) + ",\n"

    summaryMessage = summaryMessage + result.getString(1) + "  :  " + result.getString(2)  + "\n"

  }

  uris = uris.trim().substring(0, uris.length() - 2)

  println(uris)

}

summaryMessage = summaryMessage + "\n\nURIs :\n"

summaryMessage = summaryMessage + uris

odiRef.setSummaryMessage(summaryMessage)

if (uris.equals("")) {

   throw new OdiKMException( "No extraction files found on Object Storage for given search criteria.");

}

// Load Data to ADW

table_name = 'C$_0FSCMTOPMODELAM_SCMEXTRACTAM_EGPBICCEXTRACTAM_ITEMEXTRACTPVO_EXT';

sql = """

DECLARE

  uri_list CLOB;

  ext_table_name varchar2(200);

BEGIN

  uri_list := '${uris}';

  ext_table_name := '${table_name}';

  dbms_cloud.create_external_table(

    table_name => ext_table_name,

    file_uri_list => uri_list,

    credential_name =>'ODI',

    column_list => 'ITEMBASEPEOACCEPTABLEEARLYDAYS NUMBER, ITEMBASEPEOACCEPTABLERATEDECREASE NUMBER, ITEMBASEPEOACCEPTABLERATEINCREASE NUMBER, ITEMBASEPEOACCOUNTINGRULEID NUMBER(18,0), ITEMBASEPEOACDTYPE VARCHAR2(30), ITEMBASEPEOALLOWEXPRESSDELIVERYFLAG VARCHAR2(1), ITEMBASEPEOALLOWITEMDESCUPDATEFLAG VARCHAR2(1), ITEMBASEPEOALLOWMAINTENANCEASSETFLAG VARCHAR2(1), ITEMBASEPEOALLOWSUBSTITUTERECEIPTSFLAG VARCHAR2(1), ITEMBASEPEOALLOWSUSPENDFLAG VARCHAR2(1), ITEMBASEPEOALLOWTERMINATEFLAG VARCHAR2(1), ITEMBASEPEOALLOWUNORDEREDRECEIPTSFLAG VARCHAR2(1), ITEMBASEPEOALLOWEDUNITSLOOKUPCODE NUMBER, ITEMBASEPEOALTITEMCODE VARCHAR2(40), ITEMBASEPEOAPPROVALSTATUS VARCHAR2(30), ITEMBASEPEOASNAUTOEXPIREFLAG NUMBER, ITEMBASEPEOASSETCATEGORYID NUMBER(18,0), ITEMBASEPEOASSETCLASS VARCHAR2(30), ITEMBASEPEOASSETTRACKEDFLAG VARCHAR2(1), ITEMBASEPEOATOFORECASTCONTROL NUMBER, ITEMBASEPEOATPCOMPONENTSFLAG VARCHAR2(1), ITEMBASEPEOATPFLAG VARCHAR2(1), ITEMBASEPEOATPRULEID NUMBER(18,0), ITEMBASEPEOATTRIBUTE10 VARCHAR2(240), ITEMBASEPEOAUTOCREATEDCONFIGFLAG VARCHAR2(1), ITEMBASEPEOAUTOLOTALPHAPREFIX VARCHAR2(80), ITEMBASEPEOAUTOREDUCEMPS NUMBER(22,0), ITEMBASEPEOAUTOSERIALALPHAPREFIX VARCHAR2(80), ITEMBASEPEOBACKORDERABLEFLAG VARCHAR2(1), ITEMBASEPEOBACKTOBACKENABLED VARCHAR2(1), ITEMBASEPEOBASEITEMID NUMBER(18,0), ITEMBASEPEOBOMITEMTYPE NUMBER, ITEMBASEPEOBUILDINWIPFLAG VARCHAR2(1), ITEMBASEPEOBULKPICKEDFLAG VARCHAR2(1), ITEMBASEPEOBUYERID NUMBER(18,0), ITEMBASEPEOCARRYINGCOST NUMBER, ITEMBASEPEOCASNUMBER VARCHAR2(30), ITEMBASEPEOCHANGEBITMAP VARCHAR2(400), ITEMBASEPEOCHANGELINEID NUMBER(20,0), ITEMBASEPEOCHARGEPERIODICITYCODE VARCHAR2(3), ITEMBASEPEOCHECKSHORTAGESFLAG VARCHAR2(1), ITEMBASEPEOCHILDLOTFLAG VARCHAR2(1), ITEMBASEPEOCHILDLOTPREFIX VARCHAR2(30), ITEMBASEPEOCHILDLOTSTARTINGNUMBER NUMBER, ITEMBASEPEOCHILDLOTVALIDATIONFLAG VARCHAR2(1), ITEMBASEPEOCOLLATERALFLAG VARCHAR2(1), ITEMBASEPEOCOMMSACTIVATIONREQDFLAG VARCHAR2(1), ITEMBASEPEOCOMNSUPPLYPRJDEMANDFLAG VARCHAR2(1), ITEMBASEPEOCOMPLETENESSSCORE NUMBER(3,0), ITEMBASEPEOCONFIGMATCH VARCHAR2(30), ITEMBASEPEOCONFIGMODELTYPE VARCHAR2(30), ITEMBASEPEOCONFIGORGS VARCHAR2(30), ITEMBASEPEOCONSIGNEDFLAG NUMBER, ITEMBASEPEOCONTAINERITEMFLAG VARCHAR2(1), ITEMBASEPEOCONTAINERTYPECODE VARCHAR2(30), ITEMBASEPEOCONTINOUSTRANSFER NUMBER, ITEMBASEPEOCONTRACTITEMTYPECODE VARCHAR2(30), ITEMBASEPEOCONTRACTMANUFACTURING VARCHAR2(1), ITEMBASEPEOCONVERGENCE NUMBER, ITEMBASEPEOCOPYLOTATTRIBUTEFLAG VARCHAR2(1), ITEMBASEPEOCOSTINGENABLEDFLAG VARCHAR2(1), ITEMBASEPEOCOVERAGESCHEDULEID NUMBER(18,0), ITEMBASEPEOCREATESUPPLYFLAG VARCHAR2(1), ITEMBASEPEOCREATEDBY VARCHAR2(64), ITEMBASEPEOCREATIONDATE TIMESTAMP(9), ITEMBASEPEOCRITICALCOMPONENTFLAG NUMBER, ITEMBASEPEOCSSENABLEDFLAG VARCHAR2(1), ITEMBASEPEOCUMMANUFACTURINGLEADTIME NUMBER, ITEMBASEPEOCUMULATIVETOTALLEADTIME NUMBER, ITEMBASEPEOCURRENTPHASECODE VARCHAR2(120), ITEMBASEPEOCURRENTPHASEID NUMBER(18,0), ITEMBASEPEOCUSTOMERORDERENABLEDFLAG VARCHAR2(1), ITEMBASEPEOCUSTOMERORDERFLAG VARCHAR2(1), ITEMBASEPEOCYCLECOUNTENABLEDFLAG VARCHAR2(1), ITEMBASEPEODAYSEARLYRECEIPTALLOWED NUMBER, ITEMBASEPEODAYSLATERECEIPTALLOWED NUMBER, ITEMBASEPEODAYSMAXINVSUPPLY NUMBER, ITEMBASEPEODAYSMAXINVWINDOW NUMBER, ITEMBASEPEODAYSOFCOVER NUMBER, ITEMBASEPEODAYSTGTINVSUPPLY NUMBER, ITEMBASEPEODAYSTGTINVWINDOW NUMBER, ITEMBASEPEODEFAULTGRADE VARCHAR2(150), ITEMBASEPEODEFAULTINCLUDEINROLLUPFLAG VARCHAR2(1), ITEMBASEPEODEFAULTLOTSTATUSID NUMBER(18,0), ITEMBASEPEODEFAULTSERIALSTATUSID NUMBER(18,0), ITEMBASEPEODEFAULTSHIPPINGORG NUMBER(18,0), ITEMBASEPEODEFAULTSOSOURCETYPE VARCHAR2(30), ITEMBASEPEODEFAULTTEMPLATEFLAG VARCHAR2(1), ITEMBASEPEODEFECTTRACKINGONFLAG VARCHAR2(1), ITEMBASEPEODEMANDPERIOD NUMBER, ITEMBASEPEODEMANDTIMEFENCECODE NUMBER, ITEMBASEPEODEMANDTIMEFENCEDAYS NUMBER, ITEMBASEPEODIMENSIONUOMCODE VARCHAR2(3), ITEMBASEPEODIVERGENCE NUMBER, ITEMBASEPEODOWNLOADABLEFLAG VARCHAR2(1), ITEMBASEPEODQREQUIREDFLAG VARCHAR2(1), ITEMBASEPEODQSEMANTICKEY VARCHAR2(4000), ITEMBASEPEODRPPLANNEDFLAG NUMBER, ITEMBASEPEODUALUOMCONTROL NUMBER, ITEMBASEPEODUALUOMDEVIATIONHIGH NUMBER, ITEMBASEPEODUALUOMDEVIATIONLOW NUMBER, ITEMBASEPEOEFFECTIVITYCONTROL NUMBER, ITEMBASEPEOELECTRONICFLAG VARCHAR2(1), ITEMBASEPEOELIGIBILITYCOMPATIBILITYRULE VARCHAR2(1), ITEMBASEPEOENABLEGENEALOGYTRACKINGFLAG VARCHAR2(1), ITEMBASEPEOENABLEIOTFLAG VARCHAR2(1), ITEMBASEPEOENABLEDFLAG VARCHAR2(1), ITEMBASEPEOENDASSEMBLYPEGGINGFLAG VARCHAR2(1), ITEMBASEPEOENDDATEACTIVE TIMESTAMP(9), ITEMBASEPEOENFORCESHIPTOLOCATIONCODE VARCHAR2(25), ITEMBASEPEOENGINEEREDITEMFLAG VARCHAR2(1), ITEMBASEPEOENGINEERINGITEMID NUMBER(18,0), ITEMBASEPEOEQUIPMENTTYPE NUMBER, ITEMBASEPEOEVENTFLAG VARCHAR2(1), ITEMBASEPEOEXCLUDEFROMBUDGETFLAG NUMBER, ITEMBASEPEOEXPENDITURETYPEID NUMBER(18,0), ITEMBASEPEOEXPENSEACCOUNT NUMBER(18,0), ITEMBASEPEOEXPIRATIONACTIONCODE VARCHAR2(32), ITEMBASEPEOEXPIRATIONACTIONINTERVAL NUMBER, ITEMBASEPEOFINANCINGALLOWEDFLAG VARCHAR2(1), ITEMBASEPEOFIXEDDAYSSUPPLY NUMBER, ITEMBASEPEOFIXEDLEADTIME NUMBER, ITEMBASEPEOFIXEDLOTMULTIPLIER NUMBER, ITEMBASEPEOFIXEDORDERQUANTITY NUMBER, ITEMBASEPEOFORCEPURCHASELEADTIMEFLAG VARCHAR2(1), ITEMBASEPEOFORECASTHORIZON NUMBER, ITEMBASEPEOFULLLEADTIME NUMBER, ITEMBASEPEOGDSNOUTBOUNDENABLEDFLAG VARCHAR2(1), ITEMBASEPEOGRADECONTROLFLAG VARCHAR2(1), ITEMBASEPEOHARDPEGGINGLEVEL VARCHAR2(25), ITEMBASEPEOHAZARDCLASSID NUMBER(18,0), ITEMBASEPEOHAZARDOUSMATERIALFLAG VARCHAR2(1), ITEMBASEPEOHOLDDAYS NUMBER, ITEMBASEPEOIMPLEMENTATIONDATE DATE, ITEMBASEPEOINDIVISIBLEFLAG VARCHAR2(1), ITEMBASEPEOINSPECTIONREQUIREDFLAG VARCHAR2(1), ITEMBASEPEOINTERNALORDERENABLEDFLAG VARCHAR2(1), ITEMBASEPEOINTERNALORDERFLAG VARCHAR2(1), ITEMBASEPEOINTERNALVOLUME NUMBER, ITEMBASEPEOINVENTORYASSETFLAG VARCHAR2(1), ITEMBASEPEOINVENTORYCARRYPENALTY NUMBER, ITEMBASEPEOINVENTORYITEMFLAG VARCHAR2(1), ITEMBASEPEOINVENTORYITEMID NUMBER(18,0), ITEMBASEPEOINVENTORYITEMSTATUSCODE VARCHAR2(10), ITEMBASEPEOINVENTORYORGANIZATIONID NUMBER(18,0), ITEMBASEPEOINVENTORYPLANNINGCODE NUMBER, ITEMBASEPEOINVOICECLOSETOLERANCE NUMBER, ITEMBASEPEOINVOICEENABLEDFLAG VARCHAR2(1), ITEMBASEPEOINVOICEMATCHOPTION NUMBER(1,0), ITEMBASEPEOINVOICEABLEITEMFLAG VARCHAR2(1), ITEMBASEPEOINVOICINGRULEID NUMBER(18,0), ITEMBASEPEOITEMCATALOGGROUPID NUMBER(18,0), ITEMBASEPEOITEMNUMBER VARCHAR2(300), ITEMBASEPEOITEMTYPE VARCHAR2(30), ITEMBASEPEOLASTSUBMITTEDNIRID NUMBER(18,0), ITEMBASEPEOLASTUPDATEDATE TIMESTAMP(9), ITEMBASEPEOLASTUPDATELOGIN VARCHAR2(32), ITEMBASEPEOLASTUPDATEDBY VARCHAR2(64), ITEMBASEPEOLEADTIMELOTSIZE NUMBER, ITEMBASEPEOLISTPRICEPERUNIT NUMBER, ITEMBASEPEOLOCATIONCONTROLCODE NUMBER, ITEMBASEPEOLOTCONTROLCODE NUMBER, ITEMBASEPEOLOTDIVISIBLEFLAG VARCHAR2(1), ITEMBASEPEOLOTMERGEENABLED VARCHAR2(1), ITEMBASEPEOLOTSPLITENABLED VARCHAR2(1), ITEMBASEPEOLOTSTATUSENABLED VARCHAR2(1), ITEMBASEPEOLOTSUBSTITUTIONENABLED VARCHAR2(1), ITEMBASEPEOLOTTRANSLATEENABLED VARCHAR2(1), ITEMBASEPEOMARKETPRICE NUMBER, ITEMBASEPEOMATCHAPPROVALLEVEL NUMBER(1,0), ITEMBASEPEOMATERIALBILLABLEFLAG VARCHAR2(30), ITEMBASEPEOMATURITYDAYS NUMBER, ITEMBASEPEOMAXMINMAXQUANTITY NUMBER, ITEMBASEPEOMAXIMUMLOADWEIGHT NUMBER, ITEMBASEPEOMAXIMUMORDERQUANTITY NUMBER, ITEMBASEPEOMINMINMAXQUANTITY NUMBER, ITEMBASEPEOMINIMUMFILLPERCENT NUMBER, ITEMBASEPEOMINIMUMLICENSEQUANTITY NUMBER, ITEMBASEPEOMINIMUMORDERQUANTITY NUMBER, ITEMBASEPEOMRPCALCULATEATPFLAG VARCHAR2(1), ITEMBASEPEOMRPPLANNINGCODE NUMBER, ITEMBASEPEOMRPSAFETYSTOCKCODE NUMBER, ITEMBASEPEOMRPSAFETYSTOCKPERCENT NUMBER, ITEMBASEPEOMTLTRANSACTIONSENABLEDFLAG VARCHAR2(1), ITEMBASEPEOMUSTUSEAPPROVEDVENDORFLAG VARCHAR2(1), ITEMBASEPEONEGATIVEMEASUREMENTERROR NUMBER, ITEMBASEPEONEGOTIATIONREQUIREDFLAG VARCHAR2(1), ITEMBASEPEOONTPRICINGQTYSOURCE VARCHAR2(30), ITEMBASEPEOOPERATIONSLACKPENALTY NUMBER, ITEMBASEPEOOPTIONSPECIFICSOURCED NUMBER, ITEMBASEPEOORDERCOST NUMBER, ITEMBASEPEOORDERABLEONWEBFLAG VARCHAR2(1), ITEMBASEPEOORGANIZATIONID NUMBER(18,0), ITEMBASEPEOOUTSIDEOPERATIONUOMTYPE VARCHAR2(25), ITEMBASEPEOOUTSIDEPROCESSSERVICEFLAG VARCHAR2(1), ITEMBASEPEOOVERRETURNTOLERANCE NUMBER, ITEMBASEPEOOVERSHIPMENTTOLERANCE NUMBER, ITEMBASEPEOOVERCOMPLETIONTOLERANCETYPE NUMBER, ITEMBASEPEOOVERCOMPLETIONTOLERANCEVALUE NUMBER, ITEMBASEPEOOVERRUNPERCENTAGE NUMBER, ITEMBASEPEOPARENTCHILDGENERATIONFLAG VARCHAR2(1), ITEMBASEPEOPAYMENTTERMSID NUMBER(18,0), ITEMBASEPEOPICKCOMPONENTSFLAG VARCHAR2(1), ITEMBASEPEOPICKINGRULEID NUMBER(18,0), ITEMBASEPEOPLANNEDINVPOINTFLAG VARCHAR2(1), ITEMBASEPEOPLANNERCODE VARCHAR2(10), ITEMBASEPEOPLANNINGEXCEPTIONSET VARCHAR2(10), ITEMBASEPEOPLANNINGMAKEBUYCODE NUMBER, ITEMBASEPEOPLANNINGTIMEFENCECODE NUMBER, ITEMBASEPEOPLANNINGTIMEFENCEDAYS NUMBER, ITEMBASEPEOPOSITIVEMEASUREMENTERROR NUMBER, ITEMBASEPEOPOSTPROCESSINGLEADTIME NUMBER, ITEMBASEPEOPREPOSITIONPOINT VARCHAR2(1), ITEMBASEPEOPREPROCESSINGLEADTIME NUMBER, ITEMBASEPEOPRICETOLERANCEPERCENT NUMBER, ITEMBASEPEOPRIMARYUOMCODE VARCHAR2(3), ITEMBASEPEOPROCESSCOSTINGENABLEDFLAG VARCHAR2(1), ITEMBASEPEOPROCESSEXECUTIONENABLEDFLAG VARCHAR2(1), ITEMBASEPEOPROCESSQUALITYENABLEDFLAG VARCHAR2(1), ITEMBASEPEOPROCESSSUPPLYLOCATORID NUMBER(18,0), ITEMBASEPEOPROCESSSUPPLYSUBINVENTORY VARCHAR2(10), ITEMBASEPEOPROCESSYIELDLOCATORID NUMBER(18,0), ITEMBASEPEOPROCESSYIELDSUBINVENTORY VARCHAR2(10), ITEMBASEPEOPRODUCTFAMILYITEMID NUMBER(18,0), ITEMBASEPEOPURCHASINGENABLEDFLAG VARCHAR2(1), ITEMBASEPEOPURCHASINGITEMFLAG VARCHAR2(1), ITEMBASEPEOPURCHASINGTAXCODE VARCHAR2(50), ITEMBASEPEOQSCSALESPRODUCTTYPE VARCHAR2(20), ITEMBASEPEOQTYRCVEXCEPTIONCODE VARCHAR2(25), ITEMBASEPEOQTYRCVTOLERANCE NUMBER, ITEMBASEPEORECEIPTDAYSEXCEPTIONCODE VARCHAR2(25), ITEMBASEPEORECEIPTREQUIREDFLAG VARCHAR2(1), ITEMBASEPEORECEIVECLOSETOLERANCE NUMBER, ITEMBASEPEORECEIVINGROUTINGID NUMBER(18,0), ITEMBASEPEORECIPEENABLEDFLAG VARCHAR2(1), ITEMBASEPEORECOVEREDPARTDISPCODE VARCHAR2(30), ITEMBASEPEORELEASETIMEFENCECODE NUMBER, ITEMBASEPEORELEASETIMEFENCEDAYS NUMBER, ITEMBASEPEOREPAIRLEADTIME NUMBER, ITEMBASEPEOREPAIRPROGRAM NUMBER, ITEMBASEPEOREPAIRYIELD NUMBER, ITEMBASEPEOREPETITIVEPLANNINGFLAG VARCHAR2(1), ITEMBASEPEOREPLACEMENTTYPE VARCHAR2(30), ITEMBASEPEOREPLENISHTOORDERFLAG VARCHAR2(1), ITEMBASEPEOREQUIRESFULFILLMENTLOCFLAG VARCHAR2(1), ITEMBASEPEOREQUIRESITMASSOCIATIONFLAG VARCHAR2(1), ITEMBASEPEORESERVABLETYPE NUMBER, ITEMBASEPEORESTRICTLOCATORSCODE NUMBER, ITEMBASEPEORESTRICTSUBINVENTORIESCODE NUMBER, ITEMBASEPEORETESTINTERVAL NUMBER, ITEMBASEPEORETURNINSPECTIONREQUIREMENT NUMBER, ITEMBASEPEORETURNABLEFLAG VARCHAR2(1), ITEMBASEPEOREVISIONQTYCONTROLCODE NUMBER, ITEMBASEPEOROUNDINGCONTROLTYPE NUMBER, ITEMBASEPEOROUNDINGFACTOR NUMBER, ITEMBASEPEOSAFETYSTOCKBUCKETDAYS NUMBER, ITEMBASEPEOSAFETYSTOCKPLANNINGMETHOD VARCHAR2(30), ITEMBASEPEOSALESACCOUNT NUMBER(18,0), ITEMBASEPEOSALESPRODUCTTYPE VARCHAR2(20), ITEMBASEPEOSECONDARYDEFAULTIND VARCHAR2(30), ITEMBASEPEOSECONDARYUOMCODE VARCHAR2(3), ITEMBASEPEOSERIALNUMBERCONTROLCODE NUMBER, ITEMBASEPEOSERIALSTATUSENABLED VARCHAR2(1), ITEMBASEPEOSERVBILLINGENABLEDFLAG VARCHAR2(1), ITEMBASEPEOSERVREQENABLEDCODE VARCHAR2(30), ITEMBASEPEOSERVICEDURATION NUMBER, ITEMBASEPEOSERVICEDURATIONPERIODCODE VARCHAR2(10), ITEMBASEPEOSERVICEDURATIONTYPECODE VARCHAR2(3), ITEMBASEPEOSERVICESTARTDELAY NUMBER, ITEMBASEPEOSERVICESTARTTYPECODE VARCHAR2(3), ITEMBASEPEOSERVICESTARTINGDELAY NUMBER, ITEMBASEPEOSERVICEABLEPRODUCTFLAG VARCHAR2(1), ITEMBASEPEOSHELFLIFECODE NUMBER, ITEMBASEPEOSHELFLIFEDAYS NUMBER, ITEMBASEPEOSHIPMODELCOMPLETEFLAG VARCHAR2(1), ITEMBASEPEOSHIPPABLEITEMFLAG VARCHAR2(1), ITEMBASEPEOSHRINKAGERATE NUMBER, ITEMBASEPEOSOAUTHORIZATIONFLAG NUMBER, ITEMBASEPEOSOTRANSACTIONSFLAG VARCHAR2(1), ITEMBASEPEOSOURCEORGANIZATIONID NUMBER(18,0), ITEMBASEPEOSOURCESUBINVENTORY VARCHAR2(10), ITEMBASEPEOSOURCETYPE NUMBER, ITEMBASEPEOSTARTAUTOLOTNUMBER VARCHAR2(80), ITEMBASEPEOSTARTAUTOSERIALNUMBER VARCHAR2(80), ITEMBASEPEOSTARTDATEACTIVE TIMESTAMP(9), ITEMBASEPEOSTDLOTSIZE NUMBER, ITEMBASEPEOSTOCKENABLEDFLAG VARCHAR2(1), ITEMBASEPEOSTYLEITEMFLAG VARCHAR2(1), ITEMBASEPEOSTYLEITEMID NUMBER(18,0), ITEMBASEPEOSUBCONTRACTINGCOMPONENT NUMBER, ITEMBASEPEOSUBSTITUTIONWINDOWCODE NUMBER, ITEMBASEPEOSUBSTITUTIONWINDOWDAYS NUMBER, ITEMBASEPEOSUMMARYFLAG VARCHAR2(1), ITEMBASEPEOTAXCODE VARCHAR2(50), ITEMBASEPEOTAXABLEFLAG VARCHAR2(1), ITEMBASEPEOTEMPLATEITEMFLAG VARCHAR2(1), ITEMBASEPEOTEMPLATENAME VARCHAR2(300), ITEMBASEPEOTRACKINGQUANTITYIND VARCHAR2(30), ITEMBASEPEOTRADEITEMDESCRIPTOR VARCHAR2(35), ITEMBASEPEOUNNUMBERID NUMBER(18,0), ITEMBASEPEOUNDERRETURNTOLERANCE NUMBER, ITEMBASEPEOUNDERSHIPMENTTOLERANCE NUMBER, ITEMBASEPEOUNITHEIGHT NUMBER, ITEMBASEPEOUNITLENGTH NUMBER, ITEMBASEPEOUNITOFISSUE VARCHAR2(25), ITEMBASEPEOUNITVOLUME NUMBER, ITEMBASEPEOUNITWEIGHT NUMBER, ITEMBASEPEOUNITWIDTH NUMBER, ITEMBASEPEOVARIABLELEADTIME NUMBER, ITEMBASEPEOVEHICLEITEMFLAG VARCHAR2(1), ITEMBASEPEOVERSIONENDDATE DATE, ITEMBASEPEOVERSIONID NUMBER(20,0), ITEMBASEPEOVERSIONSTARTDATE DATE, ITEMBASEPEOVMIFIXEDORDERQUANTITY NUMBER, ITEMBASEPEOVMIFORECASTTYPE NUMBER, ITEMBASEPEOVMIMAXIMUMDAYS NUMBER, ITEMBASEPEOVMIMAXIMUMUNITS NUMBER, ITEMBASEPEOVMIMINIMUMDAYS NUMBER, ITEMBASEPEOVMIMINIMUMUNITS NUMBER, ITEMBASEPEOVOLUMEUOMCODE VARCHAR2(3), ITEMBASEPEOWEBSTATUS VARCHAR2(30), ITEMBASEPEOWEIGHTUOMCODE VARCHAR2(3), ITEMBASEPEOWHUPDATEDATE DATE, ITEMBASEPEOWIPSUPPLYLOCATORID NUMBER(18,0), ITEMBASEPEOWIPSUPPLYSUBINVENTORY VARCHAR2(10), ITEMBASEPEOWIPSUPPLYTYPE NUMBER(18,0), ITEMTRANSLATIONPEODESCRIPTION VARCHAR2(240), ITEMTRANSLATIONPEOLANGUAGE VARCHAR2(4), ITEMTRANSLATIONPEOLONGDESCRIPTION VARCHAR2(4000)',

    format => json_object(

    'type' VALUE 'CSV',

    'skipheaders' VALUE '1',

    'compression' VALUE 'gzip',

    'dateformat' VALUE 'AUTO',

    'timestampformat' VALUE 'YYYY-MM-DD HH24:MI:SS.FF6',

    'rejectlimit' VALUE 'UNLIMITED')

 );

END;

"""

odiRef.setSummaryMessage(sql)

if ( con == null ) {

    con = odiRef.getJDBCConnection( "DEST" )

}

try {

    stmt = con.createStatement()

    stmt.execute( sql )

} finally {

    if (stmt != null) { stmt.closeOnCompletion() }

}