Apps R12 API to Create Supplier, Sites with interface Table

 CREATE TABLE APPS.XX_VENDOR_API_GL

(

  VENDOR_NO                   VARCHAR2(1000 BYTE),

  VENDOR_NAME                 VARCHAR2(1000 BYTE),

  VENDOR_SITE_CODE            VARCHAR2(1000 BYTE),

  ADDRESS                     VARCHAR2(1000 BYTE),

  CITY                        VARCHAR2(1000 BYTE),

  COUNTRY                     VARCHAR2(1000 BYTE),

  VENDOR_TYPE                 VARCHAR2(1000 BYTE),

  LIABILITY_ACCOUNT           VARCHAR2(1000 BYTE),

  PREPAID_ACCOUNT             VARCHAR2(1000 BYTE),

  CONTACT_PERSON              VARCHAR2(1000 BYTE),

  CONTACT_NUMBER              VARCHAR2(1000 BYTE),

  PAY_GROUP_CODE              VARCHAR2(1000 BYTE),

  PAY_DATE_BASIS_CODE         VARCHAR2(1000 BYTE),

  PAYMENT_CURRENCY_CODE       VARCHAR2(1000 BYTE),

  PAYMENT_METHOD_LOOKUP_CODE  VARCHAR2(1000 BYTE),

  PAY_TERMS                   VARCHAR2(1000 BYTE),

  TAX_CODE                    VARCHAR2(1000 BYTE),

  STATUS                      CHAR(1 BYTE),

  NUM                         NUMBER,

  INVOICE_CURRENCY_CODE       VARCHAR2(30 BYTE),

  VENDOR_ID                   NUMBER,

  VENDOR_SITE_ID              NUMBER,

  TERM_ID                     NUMBER

);

SELECT * FROM  

 XX_VENDOR_API_GL;

 

 

SELECT * FROM  FND_LOOKUP_VALUES

WHERE --LOOKUP_CODE LIKE 'AU%'AND

 LOOKUP_TYPE = 'VENDOR TYPE'

AND LANGUAGE = 'US'

; 

SELECT * FROM  GL_CODE_COMBINATIONS_V

where LAST_UPDATE_DATE like sysdate

ORDER BY LAST_UPDATE_DATE DESC

;

SELECT AP.*

 FROM AP_SUPPLIERS AP, AP_SUPPLIER_SITES_ALL APSA

WHERE AP.VENDOR_ID = APSA.VENDOR_ID

AND ORG_ID = 2400

AND APSA.CREATION_DATE LIKE SYSDATE;

  

  UPDATE XX_VENDOR_API_GL GL

  SET GL.VENDOR_ID = 

  (SELECT AP.VENDOR_ID FROM AP_SUPPLIERS AP

  WHERE AP.VENDOR_NAME = GL.VENDOR_NAME);

--------------------------------------------------------------------------------------------------------

declare

l_org_id number := 2400;

l_vendor_rec    ap_vendor_pub_pkg.r_vendor_rec_type;

l_return_status     VARCHAR2(20000);

l_msg_count     NUMBER;

l_msg_data  VARCHAR2(1000);

l_vendor_id NUMBER;

l_party_id  NUMBER;

l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_vendor_site_id    NUMBER;

l_party_site_id     NUMBER;

l_location_id   NUMBER;

V_count number:=0;

l_Vendor_No number;

l_liability number;

l_prepaid number;

l_term number;

v_country varchar2(100);

    l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;

    l_vendor_contact_id NUMBER;

    l_per_party_id NUMBER;

    l_rel_party_id NUMBER;

    l_rel_id NUMBER;

    l_org_contact_id NUMBER;

    l_msg                  varchar2(200);

     

begin

for i in (

--run multiple times, if error appears or records dont move check us and other suppliers

select * 

 from 

XX_VENDOR_API_GL  where

-- VENDOR_NAME = '???? ????? ??????' AND 

-- NUM NOT IN (1,2,5)

VENDOR_ID IS NULL AND

  VENDOR_SITE_ID IS NULL

  

 ) loop

dbms_output.put_line(1);

begin

select code_combination_id

into l_liability

from GL_CODE_COMBINATIONS

where SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7=-- '01.000.00.2210000.0001.0000.0000';

i.LIABILITY_ACCOUNT;

exception

when no_data_found then null;

end;

dbms_output.put_line(2);

l_vendor_rec.VENDOR_TYPE_LOOKUP_CODE:='EMPLOYEES';--i.Vendor_Type;

--l_vendor_rec.segment1 := i.Vendor_No; 

l_vendor_rec.vendor_name := i.Vendor_Name;

l_vendor_rec.PAY_DATE_BASIS_LOOKUP_CODE:= 'DUE';

--l_vendor_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_CODE;

l_vendor_rec.PAYMENT_CURRENCY_CODE := 'AED';

l_vendor_rec.invoice_CURRENCY_CODE := 'AED';

--l_vendor_rec.TERMS_NAME:=i.Pay_Terms;

l_vendor_rec.TERMS_ID:=10000;

--l_vendor_rec.ACCTS_PAY_CODE_COMBINATION_ID:=l_liability;

--l_vendor_rec.PREPAY_CODE_COMBINATION_ID:=l_prepaid; 

dbms_output.put_line('1--create vendor');

BEGIN

pos_vendor_pub_pkg.create_vendor

(

p_vendor_rec => l_vendor_rec,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_vendor_id => l_vendor_id,

x_party_id => l_party_id

);

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

  THEN     

    FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP

      l_msg := FND_MSG_PUB.get( p_msg_index => i,

                                p_encoded   => FND_API.G_FALSE

                              );   

       dbms_output.put_line('The API call failed with error '||l_msg);

    END LOOP;

  ELSE

    dbms_output.put_line('The API call ended with SUCESSS status');

  END IF; 

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END; 

 

dbms_output.put_line('2--create vendor site');

select TERRITORY_CODE--, vl.*

into v_country

from FND_TERRITORIES_VL vl

where TERRITORY_SHORT_NAME  = 'United Arab Emirates'; --'China'

l_vendor_site_rec.vendor_id :=l_vendor_id;

l_vendor_site_rec.vendor_site_code :=i.Vendor_Site_Code;

l_vendor_site_rec.address_line1 := 'Fujairah';

l_vendor_site_rec.country := 'AE';

l_vendor_site_rec.city := i.City;

l_vendor_site_rec.org_id := l_org_id;--'121';

l_vendor_site_rec.purchasing_site_flag:='Y';

l_vendor_site_rec.pay_site_flag :='Y';

l_vendor_site_rec.rfq_only_site_flag :='N';

IF i.Country = 'United States' then

l_vendor_site_rec.state := '-' ;

l_vendor_site_rec.county := '-';

end if;

--l_vendor_site_rec.vat_code:=i.tax_Code;

l_vendor_site_rec.TERMS_ID:=10000;

l_vendor_site_rec.PAY_DATE_BASIS_LOOKUP_CODE:='DUE';

--l_vendor_site_rec.PAY_GROUP_LOOKUP_CODE:=i.PAY_GROUP_CODE;

l_vendor_site_rec.INVOICE_CURRENCY_CODE:=  'AED';

l_vendor_site_rec.PAYMENT_CURRENCY_CODE:=  'AED';

l_vendor_site_rec.ACCTS_PAY_CODE_COMBINATION_ID := l_liability;

--l_vendor_site_rec.PREPAY_CODE_COMBINATION_ID:=l_prepaid;  --AFZAL

--l_vendor_site_rec.PHONE:=i.CONTACT_NUMBER;

--l_vendor_site_rec.AUTO_TAX_CALC_FLAG:='Y';

 

BEGIN

pos_vendor_pub_pkg.create_vendor_site

(

p_vendor_site_rec => l_vendor_site_rec,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

x_vendor_site_id => l_vendor_site_id,

x_party_site_id => l_party_site_id,

x_location_id => l_location_id

);

IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)

  THEN     

    FOR i IN 1..FND_MSG_PUB.COUNT_MSG LOOP

      l_msg := FND_MSG_PUB.get( p_msg_index => i,

                                p_encoded   => FND_API.G_FALSE

                              );   

       dbms_output.put_line('The API call failed with error '||l_msg);

    END LOOP;

  ELSE

    dbms_output.put_line('The API call ended with SUCESSS status');

  END IF; 

EXCEPTION WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

dbms_output.put_line('vendor_site_id: '||l_vendor_site_id);

    dbms_output.put_line('return_status: '||l_return_status);

    dbms_output.put_line('msg_data: '||l_msg_data);

dbms_output.put_line(7);

if l_return_status = 'S' then

dbms_output.put_line(8);

UPDATE XX_VENDOR_API_GL SET status='Y',VENDOR_ID = l_vendor_id, VENDOR_SITE_ID = l_vendor_site_id

  where vendor_name||'.'=i.vendor_name||'.'

and VENDOR_SITE_CODE= i.Vendor_Site_Code;

if i.tax_Code like '%RCM%' then

update ZX_PARTY_TAX_PROFILE set PROCESS_FOR_APPLICABILITY_FLAG='N' ,ALLOW_OFFSET_TAX_FLAG='Y' where party_id=l_party_id;

--dbms_output.put_line(9);

dbms_output.put_line(i.tax_Code);

else

update ZX_PARTY_TAX_PROFILE set PROCESS_FOR_APPLICABILITY_FLAG='Y' ,ALLOW_OFFSET_TAX_FLAG='N' where party_id=l_party_id;

--dbms_output.put_line(10);

dbms_output.put_line(i.tax_Code);

end if;

else

        dbms_output.put_line( 'x_return_status: ' || l_return_status);

        dbms_output.put_line( 'x_msg_data: ' || l_msg_data);

end if;

COMMIT;

End loop;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line('ERROR :'||SQLERRM);

End;