declare
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_vendor_site_id number;
begin
icx_user_sec_attr_pub.create_user_sec_attr(
p_api_version_number => 1,
p_return_status => x_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_web_user_id => 1394,
p_attribute_code => ‘ICX_SUPPLIER_ORG_ID’,
p_attribute_appl_id => 177, —-iSupplier Portal
p_varchar2_value => ”,
p_date_value => ”,
p_number_value => 493, — Vendor_ID for ICX_SUPPLIER_ORG_ID’and Vendor_site_id for ICX_SUPPLIER_SITE_ID’ attribute code
p_created_by => -1,
p_creation_date => sysdate,
p_last_updated_by => -1,
p_last_update_date => sysdate,
p_last_update_login => -1);
if (x_return_status <> ‘S’) then
dbms_output.put_line(‘Encountered ERROR in Attribute Creation!!!’);
dbms_output.put_line(‘————————————–‘);
dbms_output.put_line(x_msg_data);
IF x_msg_count > 1 THEN
FOR i IN 1..x_msg_count LOOP
dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
ELSE
dbms_output.put_line(‘Attribute has been attached !!!!!!’);
end if;
end;
API – User Security Attribute Creation
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_vendor_site_id number;
begin
icx_user_sec_attr_pub.create_user_sec_attr(
p_api_version_number => 1,
p_return_status => x_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_web_user_id => 1394,
p_attribute_code => ‘ICX_SUPPLIER_ORG_ID’,
p_attribute_appl_id => 177, —-iSupplier Portal
p_varchar2_value => ”,
p_date_value => ”,
p_number_value => 493, — Vendor_ID for ICX_SUPPLIER_ORG_ID’and Vendor_site_id for ICX_SUPPLIER_SITE_ID’ attribute code
p_created_by => -1,
p_creation_date => sysdate,
p_last_updated_by => -1,
p_last_update_date => sysdate,
p_last_update_login => -1);
if (x_return_status <> ‘S’) then
dbms_output.put_line(‘Encountered ERROR in Attribute Creation!!!’);
dbms_output.put_line(‘————————————–‘);
dbms_output.put_line(x_msg_data);
IF x_msg_count > 1 THEN
FOR i IN 1..x_msg_count LOOP
dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
ELSE
dbms_output.put_line(‘Attribute has been attached !!!!!!’);
end if;
end;
Share this:
API – Vendor Site Update
l_vendor_site_rec.org_id := v_org_id; — Operating Unit id
x_vendor_site_id :=v_site_id; — Site Id to be updated
l_vendor_site_rec.email_address := ‘[email protected]‘; — eg: Email address update
AP_VENDOR_PUB_PKG.Update_Vendor_Site
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
p_vendor_site_id => x_vendor_site_id
);
Share this:
API – Supplier & Supplier Site Creation
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
l_upd number := 0;
x_vendor_id number;
x_party_id number;
x_vendor_site_id number;
x_party_site_id number;
x_location_id number;
l_party_site_id number;
l_organization_id number;
begin
–mo_global.set_policy_context(‘S’,1650);
l_vendor_rec.SEGMENT1 := ‘1234577’;
l_vendor_rec.VENDOR_NAME := ‘TESTSUPPLIER’;
l_vendor_rec.SUMMARY_FLAG := ‘N’;
l_vendor_rec.ENABLED_FLAG := ‘Y’;
AP_VENDOR_PUB_PKG.Create_Vendor
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => l_vendor_rec,
x_vendor_id => x_vendor_id,
x_party_id => x_party_id
);
if (x_return_status <> ‘S’) then
dbms_output.put_line(‘Encountered ERROR in supplier creation!!!’);
dbms_output.put_line(‘————————————–‘);
dbms_output.put_line(x_msg_data);
IF x_msg_count > 1 THEN
FOR i IN 1..x_msg_count LOOP
dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
else
l_vendor_site_rec.vendor_id := x_vendor_id;
l_vendor_site_rec.VENDOR_SITE_CODE := ‘HOME’;
l_vendor_site_rec.org_id := 1650;
l_vendor_site_rec.COUNTRY := ‘AE’;
l_vendor_site_rec.ADDRESS_LINE1 :=’Abu Dhabi’;
l_vendor_site_rec.PURCHASING_SITE_FLAG := ‘Y’;
l_vendor_site_rec.PAY_SITE_FLAG := ‘Y’;
AP_VENDOR_PUB_PKG.Create_Vendor_Site
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => x_party_site_id,
x_location_id => x_location_id
);
if (x_return_status <> ‘S’) then
dbms_output.put_line(‘Encountered ERROR in supplier site creation!!!’);
dbms_output.put_line(‘————————————–‘);
dbms_output.put_line(x_msg_data);
IF x_msg_count > 1 THEN
FOR i IN 1..x_msg_count LOOP
dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
ELSE
dbms_output.put_line(‘Supplier Site Created!!!’);
end if;
end if;
end;
Share this:
API – AR Cash Receipt
retcode out NOCOPY varchar2)
IS
L_RETURN_STATUS VARCHAR2(240);
L_MSG_COUNT NUMBER;
L_MSG_DATA VARCHAR2(240);
L_CASH_RECEIPT_ID NUMBER;
v_customer_number VARCHAR2(240);
v_cust_name VARCHAR(240);
v_amount NUMBER;
v_receipt_number NUMBER;
CURSOR C1
IS
SELECT * FROM XX_AR_RECEIPTS_GMC;
BEGIN
BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,150);
END;
FOR I IN C1 LOOP
BEGIN
v_cust_name := I.customer_name; –substr(I.CUSTOMER_NAME,1,length(I.CUSTOMER_NAME)-1);
SELECT DISTINCT ARC.CUSTOMER_NUMBER
INTO v_customer_number
FROM AR_CUSTOMERS ARC
,HZ_CUST_ACCOUNTS_ALL HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
WHERE HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = ARC.CUSTOMER_ID
AND HCAS.ORG_ID = 150
AND LTRIM(RTRIM(UPPER(ARC.CUSTOMER_NAME))) = LTRIM(RTRIM(UPPER(v_cust_name)));
DBMS_OUTPUT.PUT_LINE (‘Customer Id – ‘||v_customer_number);
EXCEPTION
WHEN NO_DATA_FOUNd THEN
DBMS_OUTPUT.PUT_LINE(I.CUSTOMER_NAME||’ Customer Error: ‘||SUBSTR(SQLERRM,1,150));
END;
v_amount:= to_number(substr(I.AMOUNT,1,length(I.AMOUNT)-1));
–v_amount := to_number(I.AMOUNT);
v_receipt_number := to_number(I.RECEIPT_NUMBER);
AR_RECEIPT_API_PUB.create_cash
( p_api_version => ‘1.0’,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => ‘AED’,
p_amount => v_amount,
p_receipt_number => v_receipt_number,
p_receipt_date => sysdate,
p_gl_date => to_date(’31-dec-2008′),
p_customer_number => v_customer_number,
p_org_id => 150,
p_receipt_method_id => ‘2007’,
p_cr_id => l_cash_receipt_id);
/* AR_RECEIPT_API_PUB.create_and_apply
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_amount => v_amount,
p_receipt_number => v_receipt_number,
p_receipt_date => sysdate,
p_gl_date => to_date(’31-dec-2008′),
p_customer_number => v_customer_number,
p_location => ‘Abu Dhabi’,
p_receipt_method_id => ‘2007’,
p_trx_number => ‘500001’,
p_cr_id => l_cash_receipt_id
);
*/
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Cash Receipt Created & Applied’||’-‘||l_cash_receipt_id||’- Comments : ‘||l_msg_data||l_return_status);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,150)||’-‘||l_msg_data);
END;
/
Share this:
API – Inventory Item Category Combination Creation
CREATE OR REPLACE PROCEDURE APPS.xx_create_new_item_category
IS
l_category_rec inv_item_category_pub.category_rec_type;
/*
Query to check results
SELECT M1.SEGMENT1||'.'||M1.SEGMENT2||'.'||M1.SEGMENT3||'.'||M1.SEGMENT4||'.'||M1.SEGMENT5||'.'||M1.SEGMENT6
FROM MTL_CATEGORIES_B M1,
MTL_CATEGORIES_TL M2
WHERE M1.CATEGORY_ID = M2.CATEGORY_ID
AND M2.LANGUAGE = 'US'
Table Structure
CREATE TABLE XX_CREATE_NEW_CATEGORY
(
STRUCTURE_ID NUMBER,
SEGMENT1 VARCHAR2(2000 BYTE),
SEGMENT2 VARCHAR2(2000 BYTE),
SEGMENT3 VARCHAR2(2000 BYTE),
SEGMENT4 VARCHAR2(2000 BYTE),
SEGMENT5 VARCHAR2(2000 BYTE),
SEGMENT6 VARCHAR2(2000 BYTE)
)
*/
CURSOR c_cat
IS
SELECT *
FROM xx_create_new_category;
l_api_version NUMBER;
o_return_status VARCHAR2 (2000);
o_msg_count NUMBER;
o_msg_data VARCHAR2 (2000);
o_errorcode VARCHAR2 (2000);
l_error_code VARCHAR2 (2000);
l_error_desc VARCHAR2 (2000);
l_conv_status VARCHAR2 (2000);
v_category_id NUMBER;
BEGIN
fnd_global.apps_initialize (1130, 50634, 401);
FOR i IN c_cat
LOOP
l_category_rec := NULL;
l_category_rec.structure_id := i.structure_id; -- inventory super user > setup > items > categories > Query The Category Set > get the Struture id from Examine
-- l_category_rec.structure_code := g_cat_flex_code;
l_category_rec.summary_flag := 'N';
l_category_rec.enabled_flag := 'Y';
l_category_rec.segment1 := TO_CHAR (i.segment1);
l_category_rec.segment2 := TO_CHAR (i.segment2);
l_category_rec.segment3 := TO_CHAR (i.segment3);
l_category_rec.segment4 := TO_CHAR (i.segment4);
l_category_rec.segment5 := TO_CHAR (i.segment5);
l_category_rec.segment6 := TO_CHAR (i.segment6);
--
-- After the category record is loaded, then call the create_category api to
-- create the new mtl_categories record.
inv_item_category_pub.create_category
(p_api_version => 1.0,
--l_api_version,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
x_return_status => o_return_status,
x_errorcode => o_errorcode,
x_msg_count => o_msg_count,
x_msg_data => o_msg_data,
p_category_rec => l_category_rec,
x_category_id => v_category_id
); --l_category_rec.category_id);
IF o_return_status = 'S'
THEN
DBMS_OUTPUT.put_line ('*****************');
DBMS_OUTPUT.put_line ( 'Category successfully created. New Category Id = '|| v_category_id);
DBMS_OUTPUT.put_line ( 'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5|| '.'|| i.segment6);
DBMS_OUTPUT.put_line ('*****************');
ELSE
l_error_code := NVL (l_error_code, '') || 'CATCONV005,';
l_error_desc := NVL (l_error_desc, '') || '#API Error while creating Category';
l_conv_status := 'ERROR';
DBMS_OUTPUT.put_line ('API STATUS : ' || o_return_status);
DBMS_OUTPUT.put_line ( 'Segment Combination = '|| i.segment1|| '.'|| i.segment2|| '.'|| i.segment3|| '.'|| i.segment4|| '.'|| i.segment5
|| '.'|| i.segment6|| SQLERRM);
IF o_msg_count > 0
THEN
DBMS_OUTPUT.put_line (o_errorcode);
DBMS_OUTPUT.put_line (o_msg_data);
FOR i IN 1 .. o_msg_count
LOOP
DBMS_OUTPUT.put_line
( i|| '.'|| SUBSTR(fnd_msg_pub.get (p_encoded => fnd_api.g_false),1,255));
END LOOP;
END IF;
END IF;
END LOOP;
END;
/
begin
xx_create_new_item_category;
end;
Share this: