Home » RDBMS Server » Server Utilities » AP Supplier Import (Oracle Financials, 11.10.5,Solaries)
AP Supplier Import [message #421012] Thu, 03 September 2009 11:46 Go to next message
ANDY1960
Messages: 4
Registered: September 2009
Junior Member
I am trying to import supplier information from a data file created by the users, into AP supplier interface tables like
AP_SUPPLIERS_INT and AP_SUPPLIER_SITES_INT .
The data file contains all information required to load both the tables.
I want to refer VENDOR_INTERFACE_ID in the parent table AP_SUPPLIERS_INT in the child table AP_SUPPLIER_SITES_INT at the time of loading.
I have created a control file which is giving syntax error.
Could you please have a look and let me know ?

Control file:

LOAD DATA
INFILE '$MKAPPL_TOP/mkap/unprocessed/SupUpd1099'
REPLACE
INTO TABLE AP_SUPPLIERS_INT
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
( VENDOR_INTERFACE_ID "AP_SUPPLIERs_INT_s.nextval",
NUM_1099 POSITION(1:15),
VENDOR_NAME POSITION(17:41),
VENDOR_TYPE_LOOKUP_CODE POSITION(43:48),
TYPE_1099 POSITION(50:54),
FEDERAL_REPORTABLE_FLAG POSITION(56:56),
TAX_REPORTING_NAME POSITION(58:82)
)
INTO TABLE AP_SUPPLIER_SITES_INT
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( VENDOR_INTERFACE_ID REF ( CONSTANT 'AP_SUPPLIERS_INT',VENDOR_INTERFACE_ID),
VENDOR_INTERFACE_ID FILLER INTEGER,
VENDOR_SITE_CODE POSITION(84:108) "substr(:VENDOR_SITE_CODE,1,15)",
ADDRESS_LINE1 POSITION(110:139),
ADDRESS_LINE2 POSITION(141:170) NULLIF ADDRESS_LINE2=BLANKS,
CITY POSITION(172:196),
STATE POSITION(198:199),
COUNTRY POSITION(201:202),
ZIP POSITION(204:208),
PAY_SITE_FLAG POSITION(210:210),
TAX_REPORTING_SITE_FLAG POSITION(212:212),
ORG_ID POSITION(214:216)
)

Thanks,

Andy

Report message to a moderator

Re: AP Supplier Import [message #421013 is a reply to message #421012] Thu, 03 September 2009 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68637
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think we have a syntax analyzer transplanted in our brain?
Post your log file and table description.

Before please ead OraFAQ Forum Guide, especially "How to format your post?" section and use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: AP Supplier Import [message #421014 is a reply to message #421013] Thu, 03 September 2009 12:03 Go to previous messageGo to next message
ANDY1960
Messages: 4
Registered: September 2009
Junior Member
oops!
error as follows:
SQL*Loader: Release 8.0.6.3.0 - Production on Thu Sep 3 10:04:02 2009

(c) Copyright 1999 Oracle Corporation. All rights reserved.

SQL*Loader-350: Syntax error at line 32.
Expecting "," or ")", found "REF".
( VENDOR_INTERFACE_ID REF ( CONSTANT 'AP_SUPPLIERS_INT',VENDOR_
^
----------------
Please refer the table description file attached.

thanks,


ANDY

Report message to a moderator

Re: AP Supplier Import [message #421016 is a reply to message #421014] Thu, 03 September 2009 12:15 Go to previous messageGo to next message
ANDY1960
Messages: 4
Registered: September 2009
Junior Member
I guess many of you may not read my code properly as it was not formatted.

here is the code resent to you .

LOAD DATA
INFILE '$MKAPPL_TOP/mkap/unprocessed/SupUpd1099'
REPLACE
INTO TABLE AP_SUPPLIERS_INT 
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' 
(  VENDOR_INTERFACE_ID       "AP_SUPPLIERs_INT_s.nextval",  
   NUM_1099                  POSITION(1:15),
   VENDOR_NAME               POSITION(17:41),
   VENDOR_TYPE_LOOKUP_CODE   POSITION(43:48), 
   TYPE_1099                 POSITION(50:54),
   FEDERAL_REPORTABLE_FLAG   POSITION(56:56),
   TAX_REPORTING_NAME        POSITION(58:82)
)
INTO TABLE AP_SUPPLIER_SITES_INT
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
(  VENDOR_INTERFACE_ID       REF ( CONSTANT 'AP_SUPPLIERS_INT',VENDOR_INTERFACE_ID),
   VENDOR_INTERFACE_ID       FILLER  INTEGER,
   VENDOR_SITE_CODE          POSITION(84:108)  "substr(:VENDOR_SITE_CODE,1,15)",  
   ADDRESS_LINE1             POSITION(110:139), 
   ADDRESS_LINE2             POSITION(141:170)   NULLIF ADDRESS_LINE2=BLANKS,
   CITY                      POSITION(172:196),
   STATE                     POSITION(198:199),
   COUNTRY                   POSITION(201:202),
   ZIP                       POSITION(204:208),
   PAY_SITE_FLAG             POSITION(210:210),
   TAX_REPORTING_SITE_FLAG   POSITION(212:212),
   ORG_ID                    POSITION(214:216)
   )


Thanks for correcting me.( this is my first question using any forum so far).
Regards,

Andy

Report message to a moderator

Re: AP Supplier Import [message #421020 is a reply to message #421014] Thu, 03 September 2009 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>SQL*Loader: Release 8.0.6.3.0
about a decade or so old & I doubt it can interface with any supported (V10+) version of Oracle RDBMS.

Report message to a moderator

Re: AP Supplier Import [message #421035 is a reply to message #421020] Thu, 03 September 2009 15:13 Go to previous messageGo to next message
ANDY1960
Messages: 4
Registered: September 2009
Junior Member
Yes, it does now.

I have tested the whole process inserting a few supplier data in the interface tables and a process to populate po_vendors and po_vendor_sites tables.

Please refer Note 291903.1 : How to use the supplier open interface Import Process.

I am having problem in sql*loader when I try to load into both the tables in a single control file.
thanks,

Andy

Report message to a moderator

Re: AP Supplier Import [message #421038 is a reply to message #421035] Thu, 03 September 2009 15:33 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
What is REF? A stored procedure? In that case you nned to wrap the it and its parameters in double quotes.

Report message to a moderator

Re: AP Supplier Import [message #421039 is a reply to message #421038] Thu, 03 September 2009 15:38 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
joy_division wrote on Thu, 03 September 2009 13:33
What is REF? A stored procedure? In that case you nned to wrap the it and its parameters in double quotes.



http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_loading.htm#sthref1357

I had to look it up, too.
It may be a newer feature & not supported by V8.0 sqlldr.

Report message to a moderator

Previous Topic: IMP with commit=n ends with IMP-00003 ORA-30036
Next Topic: Using user-defined stored functions in SQL*Loader DIRECT path
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 17:22:47 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.