Home » Applications » Oracle Fusion Apps & E-Business Suite » supplier interface
|
|
|
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;
/
|
|
|
Re: supplier interface [message #519845 is a reply to message #519839] |
Wed, 17 August 2011 04:06 |
|
Littlefoot
Messages: 21823 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"
)
|
|
|
Re: supplier interface [message #519853 is a reply to message #519848] |
Wed, 17 August 2011 04:37 |
|
Littlefoot
Messages: 21823 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;
/
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 08:59:01 CST 2024
|