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;
