Set serveroutput on
Declare
v_FileHandle UTL_FILE.FILE_TYPE;
v_NewLine varchar2(100); -- Input line
v_FirstComma number;
v_SecondComma number;
v_ThirdComma number;
v_EntityType varchar2(30);
v_ItemName varchar2(30);
v_AssemblyName varchar2(30);
v_OrganizationCode varchar2(30);
v_NumberOfComps number;
v_QtyPerAssy number;
l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.G_MISS_BOM_HEADER_RE
C;
l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_REV
ISION_TBL;
l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_COMPO
NENT_TBL;
l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type := Bom_Bo_Pub.G_
MISS_BOM_REF_DESIGNATOR_TBL;
l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.G_MI
SS_BOM_SUB_COMPONENT_TBL;
l_error_message_list Error_handler.error_tbl_type;
l_x_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;
l_x_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
l_x_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;
l_x_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
l_x_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
l_x_return_status VARCHAR2(2000);
l_x_msg_count NUMBER;
i NUMBER;
Begin
-- Open the specified file for reading.
-- UTL_FILE.FOPEN has three parameters
-- Location - In this case this should be the same the environment variable $APP
LPTMP
-- Filename - Any valid filename
-- Open mode - We are opening the file in read mode.
v_FileHandle := UTL_FILE.FOPEN('/sqlcom/out','bomlist.txt','r');
-- Need to initialize for calling BOM API
-- Each database table that the program writes to requires system information, s
uch as who is
-- trying to update the current record. User must provide this information to th
e import program
-- initializing certain variables. To initialize the varables the user must call
the following
-- procedure.
FND_GLOBAL.apps_initialize (1001255, 50326, 700, 0);
-- Loop over the file, reading in each line. GET_LINE will raise
-- NO_DATA_FOUND when it is done, so we use that as the exit condition
-- for the loop
loop
begin
UTL_FILE.GET_LINE(v_FileHandle,v_NewLine);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
end;
-- Each field in the input the record is delimited by commas. We need
-- to find the location of the three commas (in this case) in the line,
-- and use these locations to get the fields from the v_Newline.
-- Use INSTR to find the locations of the commas.
v_FirstComma := INSTR(v_NewLine,',',1,1);
v_SecondComma := INSTR(v_NewLine,',',1,2);
v_ThirdComma := INSTR(v_NewLine,',',1,3);
v_EntityType := SUBSTR(v_NewLine,1,(v_FirstComma - 1));
v_ItemName := SUBSTR(v_NewLine, (v_FirstComma + 1), (v_SecondComma - v_FirstComm
a - 1));
v_OrganizationCode := SUBSTR(v_NewLine, (v_SecondComma + 1), (v_ThirdComma - v_S
econdComma - 1));
v_NumberOfComps := TO_NUMBER(SUBSTR(v_NewLine, (v_ThirdComma + 1)));
-- Set the Values for the Bill.
if v_EntityType = 'BO' THEN
v_AssemblyName := v_ItemName;
l_bom_header_rec.Assembly_item_name := v_ItemName;
l_bom_header_rec.Organization_code := v_OrganizationCode;
l_bom_header_rec.Assembly_type := 1;
l_bom_header_rec.Transaction_Type := 'CREATE';
l_Bom_Header_Rec.Return_Status := NULL;
-- Loop over the file, reading the in each line. We loop v_NumberOfComps times.
-- v_NumberOfComps has the values for number of components for the bill.
for i in 1..v_NumberOfComps loop
begin
UTL_FILE.GET_LINE(v_FileHandle,v_NewLine);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
end;
v_FirstComma := INSTR(v_NewLine,',',1,1);
v_SecondComma := INSTR(v_NewLine,',',1,2);
v_ThirdComma := INSTR(v_NewLine,',',1,3);
v_EntityType := SUBSTR(v_NewLine,1,(v_FirstComma - 1));
v_ItemName := SUBSTR(v_NewLine, (v_FirstComma + 1), (v_SecondComma - v_FirstComm
a - 1));
v_OrganizationCode := SUBSTR(v_NewLine, (v_SecondComma + 1), (v_ThirdComma - v_S
econdComma - 1));
v_QtyPerAssy := TO_NUMBER(SUBSTR(v_NewLine, (v_ThirdComma + 1)));
-- Set the Values for the Components.
l_bom_component_tbl(i).Organization_CODE := v_OrganizationCode;
l_bom_component_tbl(i).Assembly_Item_name := v_AssemblyName;
l_bom_component_tbl(i).Start_effective_date := sysdate;
l_bom_component_tbl(i).Component_Item_Name := v_ItemName;
l_bom_component_tbl(i).Alternate_bom_code := NULL;
l_bom_component_tbl(i).projected_yield := NULL;
l_bom_component_tbl(i).planning_percent := NULL;
l_bom_component_tbl(i).quantity_related := NULL;
l_bom_component_tbl(i).check_atp := NULL;
l_bom_component_tbl(i).Include_In_Cost_Rollup := NULL;
l_bom_component_tbl(i).Wip_Supply_Type := NULL;
l_bom_component_tbl(i).So_Basis := NULL;
l_bom_component_tbl(i).Optional := NULL;
l_bom_component_tbl(i).Mutually_Exclusive := NULL;
l_bom_component_tbl(i).Shipping_Allowed := NULL;
l_bom_component_tbl(i).Required_To_Ship := NULL;
l_bom_component_tbl(i).Required_For_Revenue := NULL;
l_bom_component_tbl(i).Include_On_Ship_Docs := NULL;
l_bom_component_tbl(i).Supply_Subinventory := NULL;
l_bom_component_tbl(i).Location_Name := NULL;
l_bom_component_tbl(i).Minimum_Allowed_Quantity := NULL;
l_bom_component_tbl(i).Maximum_Allowed_Quantity := NULL;
l_bom_component_tbl(i).Comments := NULL;
l_bom_component_tbl(i).from_end_item_unit_number := NULL;
l_bom_component_tbl(i).to_end_item_unit_number := NULL;
l_bom_component_tbl(i).Item_Sequence_Number := (i*10);
l_bom_component_tbl(i).operation_Sequence_Number := 1;
l_bom_component_tbl(i).Transaction_Type := 'CREATE';
l_bom_component_tbl(i).Quantity_Per_Assembly := v_QtyPerAssy;
l_bom_component_tbl(i).return_status := NULL;
end loop;
Error_Handler.Initialize;
-- Call the Public API
-- The public API is the user's interface to the import program. The user must c
all it
-- programatically, while sending in one business object at a time. The public A
PI returns
-- the processed business object, the business object status, and a count of all
-- associated error and warning messages.
bom_bo_pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0 -- This parameter is required. It is used by the
-- API to compare the version number of incoming
-- calls to its current version number.
, p_init_msg_list => TRUE -- This parameter is set to TRUE, allows callers to
-- to request that the API do the initialization
-- of message list on their behalf.
, p_bom_header_rec => l_bom_header_rec -- This is a set of data structures that
represent
-- the incoming business objects. This is a record
-- that holds the Bill of Materials header for the
-- BOM
, p_bom_revision_tbl => l_bom_revision_tbl -- All the p*_tbl parameters are data
structure
-- that represent incoming business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities.
, p_bom_component_tbl => l_bom_component_tbl
, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, p_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_bom_header_rec => l_x_bom_header_rec -- All the x*_tbl parameters are data s
tructure
-- that represent outgoing business objects They
-- are PL/SQL tables of records that hold records
-- for each of the other entities except now they
-- have all the changes that the import program
-- made to it through all the steps.
, x_bom_revision_tbl => l_x_bom_revision_tbl
, x_bom_component_tbl => l_x_bom_component_tbl
, x_bom_ref_designator_tbl => l_x_bom_ref_designator_tbl
, x_bom_sub_component_tbl => l_x_bom_sub_component_tbl
, x_return_status => l_x_return_status -- This is a flag that indicates the stat
e of the
-- whole business object after the import.
-- 'S' - Success
-- 'E' - Error
-- 'F' - Fatal Error
-- 'U' - Unexpected Error
, x_msg_count => l_x_msg_count -- This holds the number of messages in the API
-- message stack after the import.
, p_debug => 'N'
, p_output_dir => ''
, p_debug_filename => ''
);
dbms_output.put_line('Return Status = '||l_x_return_status);
dbms_output.put_line('Message Count = '||l_x_msg_count);
/**** Error messages ****/
Error_Handler.Get_message_list(l_error_message_list);
if l_x_return_status <> 'S'
then
-- Error Processing
for i in 1..l_x_msg_count loop
dbms_output.put_line(TO_CHAR(i)||' MESSAGE TEXT '||SUBSTR(l_error_message_list(i
).message_text,1,250));
dbms_output.put_line(TO_CHAR(i)||' MESSAGE TYPE '||l_error_message_list(i).messa
ge_type);
end loop;
-- The business object APIs do not issue commits or rollbacks. It is the respons
ibility of
-- the calling code to issue them. This ensures that parts of the transactions a
re not left
-- in the database. If an error occurs, the whole transaction is rolled back.
rollback;
else
commit;
end if;
end if;
end loop;
UTL_FILE.FCLOSE(v_FileHandle);
-- Handle the UTL_FILE exceptions meaningfully, and make sure that the file is
-- properly closed.
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,' File Error : Invalid Path ');
WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,' File Error : Invalid Mode ');
WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20101,' File Error : Invalid Operation');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE_APPLICATION_ERROR(-20152,' File Error : Invalid File Handle ');
WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE_APPLICATION_ERROR(-20053,' File Error : Read Error ');
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_FileHandle);
RAISE;
end;
