Download as DOCX, PPTX
Uploaded byAhmed Elshayeb
Oracle Web Adi For upload item master
This document outlines the steps to create a WebADI integration for uploading master item data in Oracle Applications. It involves: 1. Creating tables and packages to stage and process the data 2. Defining the API interface and mapping fields 3. Creating a template for the WebADI upload sheet 4. Setting lookups and validation for fields 5. Assigning a function to launch the WebADI from a menu The integration will allow master item records to be uploaded via a WebADI spreadsheet, with the data staged and validated before inserting into the target interface table for processing. Lookup codes are set up for fields like organization and template to standardize input.
In this document
Powered by AI
Creating a table for master item uploads with multiple attributes including SEGMENT, ORGANIZATION_ID, and STATUS.
Defining PL/SQL package for data initialization and importing item data to interface, including validation procedures.
Loading validated data into a staging area and ensuring correct attributes like DESCRIPTION and UOM.
Defining and setting up integrator interface for item uploads, detailing parameters for data entry.
Defining layout for item uploads and mapping necessary fields to ensure proper data integration.
Defining layout for item uploads and mapping necessary fields to ensure proper data integration.
Creating List of Values (LOV) for different fields to enhance data entry options and linking functions.
Creating List of Values (LOV) for different fields to enhance data entry options and linking functions.
Oracle Web Adi For upload item master
- 1. WEBADI For MasterItems Uploading 1- Create Table DROP TABLE APPS.SH_WEBADI_ITEM_UPLOAD CASCADE CONSTRAINTS; CREATE TABLE APPS.SH_WEBADI_ITEM_UPLOAD ( SEGMENT1 VARCHAR2(240 BYTE), SEGMENT2 VARCHAR2(240 BYTE), SEGMENT3 VARCHAR2(240 BYTE), ORGANIZATION_ID NUMBER(35), ORGANIZATION_CODE VARCHAR2(3 BYTE), DESCRIPTION VARCHAR2(240 BYTE), INVENTORY_ITEM_STATUS_CODE VARCHAR2(240 BYTE), TEMPLATE_ID VARCHAR2(240 BYTE), TEMPLATE_NAME VARCHAR2(30 BYTE), PRIMARY_UOM_CODE VARCHAR2(240 BYTE), ATTRIBUTE1 VARCHAR2(240 BYTE), PROCESS_FLAG NUMBER(35), SET_PROCESS_ID NUMBER(35), INTERFACE_STATUS VARCHAR2(240 BYTE), SUMMARY_FLAG VARCHAR2(240 BYTE), ENABLED_FLAG VARCHAR2(240 BYTE), SEGMENT4 VARCHAR2(240 BYTE), SEGMENT5 VARCHAR2(240 BYTE), SEGMENT6 VARCHAR2(240 BYTE), SEGMENT7 VARCHAR2(240 BYTE), SEGMENT8 VARCHAR2(240 BYTE), TRANSACTION_TYPE VARCHAR2(240 BYTE) ) CREATE OR REPLACE PUBLIC SYNONYM SH_WEBADI_ITEM_UPLOAD FOR APPS.SH_WEBADI_ITEM_UPLOAD;
- 2. 2- Create PakedgInDatabase CREATEOR REPLACE PACKAGE APPS.SH_webadi_item_upload_pkg IS l_segment1_len NUMBER; l_segment2_len NUMBER; PROCEDURE initialize_segment_len; PROCEDURE cust_import_data_to_interface; PROCEDURE cust_upload_data_to_staging ( p_segment1 VARCHAR2, p_segment2 VARCHAR2, p_organization_id VARCHAR2, p_organization_name VARCHAR2, p_description VARCHAR2, p_inventory_item_status_code VARCHAR2, p_template_name VARCHAR2, p_template_id VARCHAR2, p_primary_uom_code VARCHAR2, p_attribute1 VARCHAR2 ); END SH_webadi_item_upload_pkg; CREATE OR REPLACE PACKAGE BODY APPS.SH_webadi_item_upload_pkg IS PROCEDURE initialize_segment_len AS BEGIN BEGIN SELECT ffv.maximum_size INTO l_segment1_len FROM fnd_id_flex_segments_vl fifs, fnd_flex_value_sets ffv WHERE 1 = 1 AND fifs.flex_value_set_id = ffv.flex_value_set_id AND fifs.id_flex_code = 'MSTK' AND fifs.enabled_flag = 'Y' AND fifs.application_column_name = 'SEGMENT1'; EXCEPTION WHEN NO_DATA_FOUND THEN l_segment1_len := 0; END; BEGIN SELECT ffv.maximum_size
- 3. INTO l_segment2_len FROM fnd_id_flex_segments_vlfifs, fnd_flex_value_sets ffv WHERE 1 = 1 AND fifs.flex_value_set_id = ffv.flex_value_set_id AND fifs.id_flex_code = 'MSTK' AND fifs.enabled_flag = 'Y' AND fifs.application_column_name = 'SEGMENT2'; EXCEPTION WHEN NO_DATA_FOUND THEN l_segment2_len := 0; END; END; PROCEDURE cust_import_data_to_interface IS CURSOR int_trans IS SELECT segment1, segment2, organization_id, ORGANIZATION_CODE, description, inventory_item_status_code, template_id, TEMPLATE_NAME, primary_uom_code, attribute1, transaction_type, process_flag, set_process_id FROM SH_webadi_item_upload; BEGIN FOR r_int_trans IN int_trans LOOP INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE (segment1, segment2, organization_id, ORGANIZATION_CODE, description, inventory_item_status_code, template_id, TEMPLATE_NAME, primary_uom_code, attribute1, transaction_type,
- 4. process_flag, set_process_id) VALUES (r_int_trans.segment1, r_int_trans.segment2, r_int_trans.organization_id, r_int_trans.ORGANIZATION_CODE, r_int_trans.description, r_int_trans.inventory_item_status_code, r_int_trans.template_id, r_int_trans.TEMPLATE_NAME, r_int_trans.primary_uom_code, r_int_trans.attribute1, r_int_trans.transaction_type, r_int_trans.process_flag, r_int_trans.set_process_id); DELETE FROMSH_webadi_item_upload cwi WHERE 1 = 1 AND NVL (cwi.SEGMENT1, 1) = NVL (r_int_trans.SEGMENT1, 1) AND NVL (cwi.SEGMENT2, 1) = NVL (r_int_trans.SEGMENT2, 1); END LOOP; COMMIT; END cust_import_data_to_interface; PROCEDURE cust_upload_data_to_staging ( p_segment1 VARCHAR2, p_segment2 VARCHAR2, p_organization_id VARCHAR2, p_organization_name VARCHAR2, p_description VARCHAR2, p_inventory_item_status_code VARCHAR2, p_template_name VARCHAR2, p_template_id VARCHAR2, p_primary_uom_code VARCHAR2, p_attribute1 VARCHAR2) IS l_error_message VARCHAR2 (3000); l_error_code VARCHAR2 (3000); l_organization_id NUMBER; l_template_id NUMBER; l_uom_validity VARCHAR2 (250); l_flag_validation VARCHAR2 (250); l_item_desc_len NUMBER; BEGIN ----------------------------------------
- 5. -----Load Segment Sizesfrom setup------ ---------------------------------------- BEGIN initialize_segment_len; END; ---------------------------------------- ----------Select Org ID----------------- ---------------------------------------- IF p_organization_name IS NOT NULL THEN BEGIN SELECT hou.ORGANIZATION_ID INTO l_organization_id FROM hr_organization_units hou WHERE hou.NAME = p_organization_name; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_message := l_error_message || ',' || 'Please enter correct organization'; l_error_code := 'E'; END; END IF; IF p_organization_id IS NOT NULL THEN BEGIN SELECT hou.ORGANIZATION_ID INTO l_organization_id FROM hr_organization_units hou WHERE hou.ORGANIZATION_ID = p_organization_id; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_message := l_error_message || ',' || 'Please enter correct organization'; l_error_code := 'E'; END; END IF; ---------------------------------------- ----------Select Template ID------------
- 6. ---------------------------------------- IF p_template_name ISNOT NULL THEN BEGIN SELECT mit.TEMPLATE_ID INTO l_template_id FROM MTL_ITEM_TEMPLATES mit WHERE mit.TEMPLATE_NAME = p_template_name; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_message := l_error_message || ',' || 'Please enter correct template'; l_error_code := 'E'; END; END IF; ---------------------------------------- ------Validate Primary UOM Code--------- ---------------------------------------- IF p_primary_uom_code IS NOT NULL THEN BEGIN SELECT 'Valid' INTO l_uom_validity FROM MTL_UNITS_OF_MEASURE_VL uom WHERE uom.UOM_CODE = p_primary_uom_code; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_message := l_error_message || ',' || 'Please enter the correct Primary/Secondary UOM Code'; l_error_code := 'E'; END; END IF; ---------------------------------------- -----Validate Description entered------- ---------------------------------------- BEGIN SELECT LENGTH (TRIM (p_description)) INTO l_item_desc_len FROM DUAL; IF l_item_desc_len > 240 THEN l_error_message :=
- 7. l_error_message || ',' || 'Pleaseensure the description LENGTH IS lesser THAN 240 CHARACTERS'; l_error_code := 'E'; END IF; END; ---------------------------------------- --------Validate Item Segments---------- ---------------------------------------- BEGIN IF l_segment1_len > 0 AND LENGTH (P_SEGMENT1) > l_segment1_len OR l_segment2_len > 0 AND LENGTH (P_SEGMENT2) > l_segment2_len THEN l_error_message := l_error_message || ',' || 'Please ensure that all Item SEGMENT lengths are as per setup.'; l_error_code := 'E'; END IF; END; -------------------------------------------------------------------------------------------------------------- --------Condition to show error if any of the above validation picks up a data entry error-------------------- --------Condition to insert data into custom staging table if the data passes all above validations----------- IF l_error_code = 'E' THEN raise_application_error (-20101, l_error_message); ELSIF NVL (l_error_code, 'A') <> 'E' THEN INSERT INTO apps.SH_webadi_item_upload (segment1, segment2, organization_id, description, inventory_item_status_code, template_id, primary_uom_code, attribute1, process_flag, transaction_type, set_process_id, summary_flag, enabled_flag, interface_status) VALUES (TRIM (p_segment1),
- 8. TRIM (p_segment2), l_organization_id, TRIM (p_description), TRIM(p_inventory_item_status_code), l_template_id, TRIM (p_primary_uom_code), TRIM (p_attribute1), 1, 'CREATE', 1, 'N', 'Y', 'NO'); ---------------------------------------------------------------------------------------------------- -----------Insert data into MTL_SYSTEM_ITEMS_INTERFACE after loading into staging table------------- ---------------------------------------------------------------------------------------------------- BEGIN cust_import_data_to_interface; END; END IF; END cust_upload_data_to_staging; END SH_webadi_item_upload_pkg; / 3- Create Viwe CREATE OR REPLACE VIEW XX_INV_ORG_V AS SELECT AA.MASTER_ORG MASTER_ORG_NAME, AA.ORGANIZATION_ID, NAME Inv_ORG_Name, AA.ORGANIZATION_CODE INV_ORG_Code FROM MTL_PARAMETERS_VIEW aa, HR_ORGANIZATION_UNITs org WHERE AA.ORGANIZATION_ID = org.ORGANIZATION_ID; 4- Create Integrator
- 9. Integrator Name: SH-Item InternalName: SH_ITEM Application Inventory The ‘Display in Create Document Page’ checkbox will be checkedandthe followingfunctionswill be addedbypressingthe highlightedbuttonsabove: 1. DesktopIntegration - Create Document 2. Create Document DesktopIntegration - Create Document BNE_ADI_CREATE_DOCUMENT Create Document BNE_CREATE_DOCUMENT
- 10. Aftercompletingthe stepsabove,pressthe ‘Next’button.Aninterfacewill nowbe createdasfollows: The setupwill be asfollows:
- 11. Interface Name: SH-ITEM InterfaceType: API-Procedure Package Name XX_webadi_item_upload_pkg Procedure/FunctionName cust_upload_data_to_staging API Returns FND Message Code Afterthe entryabove,pressthe ‘Apply’button.The followingscreenwill appear: Clickon the highlightedInterface selectbox toviewthe parametersof the APIasfollows:
- 12. Clickon the ‘Next’button.Thefollowingscreenwill appear:
- 13. Clickon the highlighted‘Create Content’ buttontocreate the WebADIsheettemplatethatwill be usedfordataentry: The setupwill be asfollows:
- 14. ContentName: SH-Items ContentType: TextFile NumberofColumns 10 Clickon the ‘Apply’button. The contentwill be createdas follows:
- 15. You will nowhavetomanuallyfill inall the columndisplaynames.Thesecolumnswill be linkedtoall the parametersof the APIat a laterstage.All the columnswill be namedas perthe 10 parametersof the APIas follows: 1. SEGMENT1 2. SEGMENT2 3.Organization_ID 4. ORGANIZATION_NAME 5. DESCRIPTION 6. INVENTORY_ITEM_STATUS_CODE 7. Template_ID 8. TEMPLATE_NAME 9. PRIMARY_UOM_CODE 10. Attribute1 In ParameterListAddThe follows: Parameter List: WebADI: Download"TextFile"Parameters
- 16. Choose ‘FromTemplate’ andpress‘Create’.The followingwill appear.Ensure youruploadersetupisasfollows:
- 18.
- 19. Define Layout Navigation: The followingscreenwillappear: Clickon ‘Create’
- 20. Provide the Layoutnameas follows: Layout Name: Customized –ItemUploadADI – Layout Numberof Headers: 1
- 21. You will settheplacementof all the fieldsas ‘Line’.Click‘Next’.The followingwillappear.
- 22. Click‘Apply’.The Layout willbe successfullycreated.
- 24. Define Mapping Navigation: DesktopIntegration DefineMapping The followingwill appear: Clickon ‘Define Mapping’ The followingwill appear:
- 25.
- 26. Enter the requireddetailsasfollows: MappingName: SH_ITEMS_MAP Mapping Key: SH_ITEMS_MAP Numberof Columns 10 Click‘Next’.The followingwillappear: SelectFromLOV For Both Side To Map The Template LayoutAndIntegrator
- 27. You will nowenterallthe Source andTarget columnson thispage. All 10 parametersof the APIsetupinthe WebADIwill be associatedwiththe contentcolumnsof the WebADIsheetonthispage. Aftermappingisdefinedforall the columns,clickon ‘Apply’. Afterthe completionof thisstep,youare nowreadyto uploaditemsthroughWebADI.
- 28. Defining LOV ForField FieldName Code ORGANIZATION_ID DECLARE BEGIN BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV ( P_APPLICATION_ID => 401, P_INTERFACE_CODE => 'SH_ITEM_XINTG_INTF1', P_INTERFACE_COL_NAME => 'P_ORGANIZATION_ID', P_ID_COL => 'ORGANIZATION_ID', P_MEAN_COL => 'Inv_ORG_Name', P_DESC_COL => 'MASTER_ORG_NAME', P_TABLE => 'XX_INV_ORG_V', P_ADDL_W_C => '1=1', P_WINDOW_CAPTION => 'Organization List', P_WINDOW_WIDTH => 400, P_WINDOW_HEIGHT => 600, P_TABLE_BLOCK_SIZE => 50, P_TABLE_SORT_ORDER => 'Inv_ORG_Name', P_USER_ID => 1013415, P_POPLIST_FLAG => 'N'); COMMIT; END; ORGANIZATION_NAME DECLARE BEGIN BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV ( P_APPLICATION_ID => 401, P_INTERFACE_CODE => 'SH_ITEM1_XINTG_INTF1', P_INTERFACE_COL_NAME => 'P_ORGANIZATION_NAME', P_ID_COL => 'Inv_ORG_Name', P_MEAN_COL => 'Inv_ORG_Name', P_DESC_COL => 'MASTER_ORG_NAME', P_TABLE => 'XX_INV_ORG_V', P_ADDL_W_C => 'ORGANIZATION_ID in (7872,7851)', P_WINDOW_CAPTION => 'Organization List', P_WINDOW_WIDTH => 400, P_WINDOW_HEIGHT => 600, P_TABLE_BLOCK_SIZE => 50, P_TABLE_SORT_ORDER => 'Inv_ORG_Name', P_USER_ID => 1013415, P_POPLIST_FLAG => 'N'); COMMIT; END; TEMPLATE_NAME DECLARE BEGIN BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV ( P_APPLICATION_ID => 401,
- 29. P_INTERFACE_CODE => 'SH_ITEM_XINTG_INTF1', P_INTERFACE_COL_NAME=> 'P_TEMPLATE_NAME', P_ID_COL => 'TEMPLATE_NAME', P_MEAN_COL => 'TEMPLATE_NAME', P_DESC_COL => 'DESCRIPTION', P_TABLE => 'MTL_ITEM_TEMPLATES_VL', P_ADDL_W_C => '1=1', P_WINDOW_CAPTION => 'Template Name', P_WINDOW_WIDTH => 400, P_WINDOW_HEIGHT => 600, P_TABLE_BLOCK_SIZE => 50, P_TABLE_SORT_ORDER => 'TEMPLATE_NAME', P_USER_ID => 1013415, P_POPLIST_FLAG => 'N'); COMMIT; END; PRIMARY_UOM_CODE DECLARE BEGIN BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV ( P_APPLICATION_ID => 401, P_INTERFACE_CODE => 'SH_ITEM_XINTG_INTF1', -- BNE_INTERFACE_COLS_B.INTERFACE_CODE P_INTERFACE_COL_NAME => 'P_PRIMARY_UOM_CODE', -- BNE_INTERFACE_COLS_B.INTERFACE_COL_NAME P_ID_COL => 'UOM_CODE', P_MEAN_COL => 'UNIT_OF_MEASURE', P_DESC_COL => 'UOM_CLASS', P_TABLE => 'MTL_UNITS_OF_MEASURE_VL', P_ADDL_W_C => 'DISABLE_DATE is null', P_WINDOW_CAPTION => 'Primary UOM Name', P_WINDOW_WIDTH => 400, P_WINDOW_HEIGHT => 600, P_TABLE_BLOCK_SIZE => 50, P_TABLE_SORT_ORDER => 'UOM_CODE', P_USER_ID => 1013415, P_POPLIST_FLAG => 'N'); COMMIT; END;
- 30. Create Function LinkedTo WebADI The nextstepisto assignthisfunctiontoa Menuof yourchoice:Navigation: SystemAdministratorApplicationFunction
- 31.
- 32.
- 33. Navigate tothe responsibilitywherethe menuisassigned:
- 35. Try to Launchthe WebADIfromthe customfunction: My Youtube https://www.youtube.com/c/AhmedElshayeb2002?sub_confirmation=1