OraFAQ Forum: Oracle Fusion Apps & E-Business Suite

Home » Applications » Oracle Fusion Apps & E-Business Suite » supplier interface

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

supplier interface [message #519753]

Tue, 16 August 2011 12:13

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

hi frds i got erroors when i insert suppliers contacts....i inserted sites and supplier s.but i cant insert site contcts.plz help me

[MERGED by LF]

[Updated on: Wed, 17 August 2011 04:09] by Moderator

Report message to a moderator

Re: supplier interface [message #519755 is a reply to message #519753]

Tue, 16 August 2011 12:43

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and do not use IM/SMS speak.
And also, we don't get answer to your question if you don't post it in the correct forum.
No one answers to technical questions in Marketplace forum.

Regards
Michel

Report message to a moderator

Re: supplier interface [message #519757 is a reply to message #519755]

Tue, 16 August 2011 13:00

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Which is why I moved it to the Oracle Apps forum

Report message to a moderator

supplier interface [message #519839 is a reply to message #519753]

Wed, 17 August 2011 03:54

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

hi frds i am fresher for apps tech.i have prob with inserting site contacts.i can insert suppliers and sites.but i cant insert contacts.am using 11i version.plz help me.
just i posted simple code.plz correct me.

my code:
--------

CREATE OR REPLACE PROCEDURE SUPPROC
(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2)
IS
V_SUP SUPSTG%ROWTYPE;
V_SUPSITES SUPSTG%ROWTYPE;
VENDOR_INTERFACE_ID AP_SUPPLIERS_INT.VENDOR_INTERFACE_ID%TYPE;
V_ORG_ID NUMBER:=FND_PROFILE.VALUE('ORG_ID');
VENDOR_NAME SUPSTG.VENDOR_NAME%TYPE;

VENDOR_SITE_CODE SUPSTG.VENDOR_SITE_CODE%TYPE;
v_sup_val NUMBER;
V_VENDOR_SITE_ID PO_VENDOR_SITES_ALL.VENDOR_SITE_ID %TYPE;
CURSOR CUR_X IS
SELECT VENDOR_NAME FROM SUPSTG;

CURSOR CUR_X1
(P_VEN IN VARCHAR2) IS SELECT DISTINCT VENDOR_SITE_CODE
FROM SUPSTG
WHERE VENDOR_NAME=P_VEN;
CURSOR CUR_X2(P_VEN IN VARCHAR2,P_SITE IN VARCHAR2) IS SELECT DISTINCT LAST_NAME
FROM SUPSTG
WHERE VENDOR_NAME=P_VEN
AND VENDOR_SITE_CODE =P_SITE;

BEGIN

FOR H1 IN CUR_X
LOOP

INSERT INTO AP.AP_SUPPLIERS_INT
(VENDOR_INTERFACE_ID
,VENDOR_NAME)
VALUES
(SEQSUP.NEXTVAL
,H1.VENDOR_NAME);

FOR L1 IN CUR_X1(H1.vendor_name)
LOOP

INSERT INTO AP.AP_SUPPLIER_SITES_INT
(VENDOR_INTERFACE_ID
,VENDOR_SITE_CODE
,ORG_ID)
VALUES
(SEQSUP.CURRVAL
,L1.VENDOR_SITE_CODE
,V_ORG_ID );

FOR C1 IN CUR_X2(H1.vendor_name,VENDOR_SITE_CODE)
LOOP
INSERT INTO ap.ap_sup_site_contact_int
(vendor_site_CODE, last_name,
org_id)
VALUES
(L1.VENDOR_SITE_CODE
,C1.LAST_NAME
, V_ORG_ID);

END LOOP;
END LOOP;
END LOOP;
COMMIT;
END;

/

Report message to a moderator

Re: supplier interface [message #519845 is a reply to message #519839]

Wed, 17 August 2011 04:06

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It is difficult to guess what might be a problem because you never explained what errors (if any) you got. We don't have your tables, your data, so can't even run your code. Read OraFAQ Forum Guide and try to follow its suggestions, as following those simple rules helps people help you.

P.S. Please, do not scatter your message all over the forum; continue discussion in the same topic.

[Updated on: Wed, 17 August 2011 04:10]

Report message to a moderator

Re: supplier interface [message #519848 is a reply to message #519845]

Wed, 17 August 2011 04:14

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

sorry.i can inserting my supplier and sites.
now i sent my sample data.AND I DIDNT GET ANY ERRORS.I SUCESSFULLY INSERTED SUPPLIER ,SITE DETAILS.BOTH DETAILS I CAN SEND FROM STAGING TABLE TO INTERFACE TABLES.BUT SITE DETAILS I CANT.

MY TABLE CREATION:
-----------------
CREATE TABLE SUPSTG
(VENDOR_NAME VARCHAR2(240)
,VENDOR_SITE_CODE VARCHAR2(15)
,LAST_NAME VARCHAR2(20)
,VENDOR_INTERFACE_ID NUMBER(15)
,PROCESS_FLAG VARCHAR2(15)
,ERROR_MESSAGE VARCHAR2(4000)
,LAST_UPDATE_DATE DATE
,LAST_UPDATED_BY NUMBER(15)
,CREATION_DATE DATE
,CREATED_BY NUMBER)
;

MY DATA FILE:
------------

VENDOR_NAME,VENDOR_SITE_CODE,LAST_NAME
SUP17816,VSP,SUBHA16811
SUP17817,VSP1,SUBHA16812
SUP17818,VSP2,SUBHA16813
SUP17819,VSP3,SUBHA16814
SUP17820,VSP4,SUBHA16815
MY CONTROL FILE:
----------------

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'D:\oracle\visappl\ap\11.5.0\bin\SUPD.csv'
INSERT
into table SUPSTG
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
VENDOR_NAME
,VENDOR_SITE_CODE
,LAST_NAME
,process_flag CONSTANT "N"
,error_message
,created_by "UID"
,creation_date "SYSDATE"
,last_updated_by "UID"
,last_update_date "SYSDATE"
)

Report message to a moderator

Re: supplier interface [message #519853 is a reply to message #519848]

Wed, 17 August 2011 04:37

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't understand what you are saying.

I created a table and ran SQL*Loader, all based on information you posted in the previous message - all records were loaded successfully. So, what is the problem?

[EDIT: Execution]
A control file:

LOAD DATA
INFILE *
INSERT
into table SUPSTG
fields terminated by ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
 (
  VENDOR_NAME 
 ,VENDOR_SITE_CODE 
 ,LAST_NAME 
 ,process_flag CONSTANT "N" 
 ,error_message 
 ,created_by "UID" 
 ,creation_date "SYSDATE"
 ,last_updated_by "UID"
 ,last_update_date "SYSDATE"
 )

begindata
SUP17816,VSP,SUBHA16811
SUP17817,VSP1,SUBHA16812
SUP17818,VSP2,SUBHA16813
SUP17819,VSP3,SUBHA16814
SUP17820,VSP4,SUBHA16815

Loading:

SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri Kol 17 11:38:55 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 4
Commit point reached - logical record count 5

Select:

SQL> select vendor_name, vendor_site_code, last_name
  2  from supstg;

VENDOR_NAM VENDOR_SITE_CODE     LAST_NAME
---------- -------------------- --------------------
SUP17816   VSP                  SUBHA16811
SUP17817   VSP1                 SUBHA16812
SUP17818   VSP2                 SUBHA16813
SUP17819   VSP3                 SUBHA16814
SUP17820   VSP4                 SUBHA16815

[Updated on: Wed, 17 August 2011 04:41]

Report message to a moderator

Re: supplier interface [message #519854 is a reply to message #519753]

Wed, 17 August 2011 04:46

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

from staging table to interface tables i cant send.i think i hava problem with my procedure.

CREATE OR REPLACE PROCEDURE SUPPROC
(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2)
IS
V_SUP SUPSTG%ROWTYPE;
V_SUPSITES SUPSTG%ROWTYPE;
VENDOR_INTERFACE_ID AP_SUPPLIERS_INT.VENDOR_INTERFACE_ID%TYPE;
V_ORG_ID NUMBER:=FND_PROFILE.VALUE('ORG_ID');
VENDOR_NAME SUPSTG.VENDOR_NAME%TYPE;

VENDOR_SITE_CODE SUPSTG.VENDOR_SITE_CODE%TYPE;
v_sup_val NUMBER;
V_VENDOR_SITE_ID PO_VENDOR_SITES_ALL.VENDOR_SITE_ID %TYPE;
CURSOR CUR_X IS
SELECT VENDOR_NAME FROM SUPSTG;

CURSOR CUR_X1
(P_VEN IN VARCHAR2) IS SELECT DISTINCT VENDOR_SITE_CODE
FROM SUPSTG
WHERE VENDOR_NAME=P_VEN;
CURSOR CUR_X2(P_VEN IN VARCHAR2,P_SITE IN VARCHAR2) IS SELECT DISTINCT LAST_NAME
FROM SUPSTG
WHERE VENDOR_NAME=P_VEN
AND VENDOR_SITE_CODE =P_SITE;

BEGIN

FOR H1 IN CUR_X
LOOP

INSERT INTO AP.AP_SUPPLIERS_INT
(VENDOR_INTERFACE_ID
,VENDOR_NAME)
VALUES
(SEQSUP.NEXTVAL
,H1.VENDOR_NAME);

FOR L1 IN CUR_X1(H1.vendor_name)
LOOP

INSERT INTO AP.AP_SUPPLIER_SITES_INT
(VENDOR_INTERFACE_ID
,VENDOR_SITE_CODE
,ORG_ID)
VALUES
(SEQSUP.CURRVAL
,L1.VENDOR_SITE_CODE
,V_ORG_ID );

FOR C1 IN CUR_X2(H1.vendor_name,VENDOR_SITE_CODE)
LOOP
INSERT INTO ap.ap_sup_site_contact_int
(vendor_site_CODE, last_name,
org_id)
VALUES
(L1.VENDOR_SITE_CODE
,C1.LAST_NAME
, V_ORG_ID);

END LOOP;
END LOOP;
END LOOP;
COMMIT;

END;
/

Report message to a moderator

Re: supplier interface [message #519856 is a reply to message #519854]

Wed, 17 August 2011 04:48

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

from staging table to interface tables i cant send.

Report message to a moderator

Re: supplier interface [message #519871 is a reply to message #519856]

Wed, 17 August 2011 05:55

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

i think i have problem with my procedure.

What kind of a problem? What happens when you run the procedure?

Report message to a moderator

Re: supplier interface [message #519875 is a reply to message #519871]

Wed, 17 August 2011 06:02

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

my procedure runs sucesufully.but when i run my procedure concurent program(which is preapred by me) the data from staging table to inteface tables(ap_suppliers_int,ap_supplier_sites_int) sucesuffly inserted.but it cant insert in contact interface tables i.e ap_sup_site_contact_int

Report message to a moderator

Re: supplier interface [message #519882 is a reply to message #519875]

Wed, 17 August 2011 06:26

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

please rectify my code .while inserting the interface tables think my code not working in case of contacts.for supplier and sites its wrking

Report message to a moderator

Re: supplier interface [message #519887 is a reply to message #519882]

Wed, 17 August 2011 07:29

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If there's no error, but the procedure does nothing, maybe the problem is in the way you call this procedure. Is there, by any chance, any WHEN OTHERS exception handler? If so, how does it look like? What happens if you comment it and run the whole process again (but this time let Oracle raise a possible error)?

Report message to a moderator

Re: supplier interface [message #519890 is a reply to message #519887]

Wed, 17 August 2011 07:38

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

i regd my procedure perfectly.thatz why am getting sites and supplier information.but am not getting contacts information.

its my cursor:

CURSOR CUR_X2(P_VEN IN VARCHAR2,P_SITE IN VARCHAR2) IS SELECT DISTINCT LAST_NAME
FROM SUPSTG
WHERE VENDOR_NAME=P_VEN
AND VENDOR_SITE_CODE =P_SITE;

its my inserting statement for contacts:

FOR C1 IN CUR_X2(H1.vendor_name,VENDOR_SITE_CODE)
LOOP
INSERT INTO ap.ap_sup_site_contact_int
(vendor_site_CODE, last_name,
org_id)
VALUES
(L1.VENDOR_SITE_CODE
,C1.LAST_NAME
, V_ORG_ID);

"i think prob is hear only..well am fresher after sucesfully insert my code then only am trying for exceptions(am weak in writing exceptions and pl/sql code)now am learning stage only.its sample data.actuavally am d2k person last ten days back i converted technical consultent."
first am trying to insert just dummy data .thetz y just a prepared procedure for only insert data.
please check any mandatory columns missing or any wrog code

Report message to a moderator

Re: supplier interface [message #519892 is a reply to message #519890]

Wed, 17 August 2011 07:42

durga aswini
Messages: 10
Registered: August 2011
Location: hyd

Junior Member

well i saw many codes through blogs regarding supplier interface.they insert only supplier and site information.but they didnt use and insert contact informations.

Report message to a moderator

Re: supplier interface [message #519901 is a reply to message #519892]

Wed, 17 August 2011 08:46

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

OK, so there's no error at all (according to you). If the INSERT does nothing, and knowing that it is supposed to insert data fetched by the cursor, it appears that cursor returned nothing (and, accordingly to that, INSERT doesn't have any values to insert).

I don't think I can help any further as I don't use Oracle Apps. so most of terms you use are unknown to me.

Report message to a moderator