CREATE OR REPLACE PROCEDURE APPS.XXBOM_IMPORT_VA_PRC (p_error_message OUT VARCHAR2,p_error_code OUT VARCHAR2 ) IS --********************************************************************************* -- Copyright (c) 2003 Emerson Electric Company -- This program contains proprietary and confidential information. -- All rights reserved except as may be permitted by prior -- written consent. -- -- -- File Name: XXBOM/11.5.0/admin/sql/XXBOM_IMPORT_VA_PRC.pro -- PVCS Spec: BETSY:A273.A-PRO;2 -- -- Attribute Label: Kintana Pkg# 67760 -- -- Description: -- Bill of material conversion program -- -- Modification History: -- Revision 2 (IN STAGING) -- Updated: 28-JUL-2008 06:03:30 KINTP1 -- Updated attribute(s) -- Created: 22-JUL-2008 21:57:48 ABAJPAI -- Modified the code to change the WHO column update -- statements. -- Updated: 22-JUL-2008 21:29:17 ABAJPAI -- Item revision 2 created from revision 1 with status -- $TO_BE_DEFINED -- -- Revision 1 (IN STAGING) -- Updated: 07-JUL-2008 17:07:38 KINTP1 -- Updated attribute(s) -- Created: 22-JUN-2008 05:08:16 ABAJPAI -- Initial revision. -- -- --********************************************************************************* -- PROJECT : EMR-VAD-SOM -- -- APPLICATION : EMR Bill of Materials -- -- SCRIPT : XXBOM_IMPORT_VA_PRC.pro -- -- CREATED BY : WIPRO -- CREATION DATE : 21-JUN-2008 -- -- DESCRIPTION : Bill of Material Conversion Program -- -- PARAMETERS : : -- : -- -- INPUTS : None -- -- OUTPUTS : o_errbuf : captures the error message -- o_retcode : captures the return code -- -- PLATFORM - OS : SOLARIS -- PLATFORM - H/W : SUN ULTRASPARC -- -- DATABASE : ORACLE 9.2.0.6.0 -- APPS. RELEASE : ORACLE APPLICATIONS 11.5.10 -- -- DEVELOPMENT AND MAINTENANCE HISTORY -- -- DATE PVCS_Version AUTHOR DESCRIPTION -- ****** ************* ******** ******************* -- -- 21-JUN-2008 1 NUTAN-WIPRO CREATED -- 22-Jul-2008 2 Wipro Modified the code to change the WHO column update statements -- --********************************************************************************* -- -- ************ Local Variables Declaration **********-- */ -- Variable Declarations v_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC; v_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL; v_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL; v_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL; v_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL; v_error_message_list Error_handler.error_tbl_type; v_x_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type; v_x_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type; v_x_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type; v_x_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type; v_x_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type; v_assembly_item_name VARCHAR2(81); v_organization_code VARCHAR2(3); v_x_return_status VARCHAR2(2000); v_x_msg_count NUMBER; v_msg_text1 VARCHAR2(230); v_msg_text2 VARCHAR2(230); v_ii NUMBER; v_jj NUMBER; v_org_id NUMBER; v_job_id NUMBER; v_batch_num VARCHAR2(30); v_record_type VARCHAR2(1); v_item_id VARCHAR2(81); v_component_id VARCHAR2(81); v_error_flag VARCHAR2(1); v_organization_id NUMBER; v_error_message VARCHAR2(2000); v_error VARCHAR2(2000); v_error_msg VARCHAR2(2000); CURSOR cur_bom_header_data IS SELECT rtrim(xbomi.item_number) item_number ,xbomi.organization_code organization_code ,xbomi.assembly_type ,xbomi.SPECIFIC_ASSEMBLY_COMMENT ,xbomi.data_flow ,xbomi.status ,xbomi.record_type FROM XXBOM_BILL_OF_MTLS_IFACE_VA xbomi WHERE xbomi.status IN ('PICK', 'ERRSTG', 'ERRINT') AND xbomi.data_flow = 'C' AND xbomi.record_type = 'A'; CURSOR cur_bom_lines_data (p_item_name VARCHAR2, p_organization_code VARCHAR2) IS SELECT rtrim(assembly_item_number) assembly_item_number ,organization_code ,NVL(effectivity_date, TRUNC(SYSDATE+1)) effectivity_date ,disable_date ,nvl(operation_seq_num,1) operation_seq_num ,component_item_number ,component_quantity ,component_sequence_id ,wip_supply_type ,supply_subinventory ,location_name ,optional ,mutually_exclusive_options ,low_quantity ,high_quantity ,check_atp ,shipping_allowed ,required_to_ship ,required_for_revenue ,include_on_ship_docs FROM XXBOM_INV_COMPS_IFACE_VA WHERE rtrim(assembly_item_number) = p_item_name AND organization_code = p_organization_code; CURSOR cur_bom_designator_data (p_item_name VARCHAR2, p_organization_code VARCHAR2 ,p_component_item_number VARCHAR2,p_op_seq_num IN VARCHAR2) IS SELECT rtrim(xbdi.assembly_item_name) assembly_item_name ,xbdi.organization_code ,NVL(xbdi.start_effective_date, TRUNC(SYSDATE+1)) start_effective_date ,nvl(xbdi.operation_sequence_number,1) operation_sequence_number ,rtrim(xbdi.component_item_name) component_item_name ,xbdi.reference_designator_name ,xbdi.ref_designator_comment ,xbdi.attribute1 ,xbdi.attribute2 ,xbdi.attribute3 FROM XXBOM_DESIGNATOR_IFACE_VA xbdi WHERE rtrim(xbdi.assembly_item_name) = p_item_name AND xbdi.organization_code = p_organization_code AND rtrim(xbdi.component_item_name) = p_component_item_number AND nvl(xbdi.operation_sequence_number,-999) = nvl(p_op_seq_num,nvl(xbdi.operation_sequence_number,-999)); BEGIN fnd_file.put_line(fnd_file.LOG,'XXBOM_IMPORT Importing BOM '); v_org_id := FND_PROFILE.VALUE('ORG_ID'); /* Getting Batch Number */ SELECT xxfnd_job_s.NEXTVAL INTO v_job_id FROM dual; v_batch_num := 'XXBOMIMP'||v_job_id; -- After Creating Batch -- Need to initialize for calling BOM API -- Each database table that the program writes to requires system information, such as who is -- trying to update the current record. User must provide this information to the import program -- initializing certain variables. To initialize the varables the user must call the following -- procedure. -- Arguments are : user_id (fnd_user), responsibility_id (), Responsibility_application_id (), security_group_id (0). fnd_file.put_line (fnd_file.LOG, ' User Id is '|| fnd_profile.value('USER_ID')|| ' Responsibility Id is '||fnd_profile.value('RESP_ID')|| ' Appl Id is '||fnd_profile.value('RESP_APPL_ID')); FND_GLOBAL.apps_initialize (TO_NUMBER(fnd_profile.value('USER_ID')), TO_NUMBER(fnd_profile.value('RESP_ID')), TO_NUMBER(fnd_profile.value('RESP_APPL_ID')), 0); -- Read the BOM header lines having status = PICK FOR h IN cur_bom_header_data LOOP -- header loop -- Initializing Message Variable. v_error_message := NULL; /* Validation for Assembly Item Name */ BEGIN v_error_flag := 'N'; BEGIN SELECT ood.organization_id, ood.organization_code INTO v_organization_id, v_organization_code FROM org_organization_definitions ood WHERE ood.organization_code = h.organization_code; EXCEPTION WHEN OTHERS THEN v_error_flag := 'Y'; v_error := h.organization_code||' is Not a Valid Organization '|| ' -Item ' || h.item_number||';'; v_error_message := v_error_message||v_error; fnd_file.put_line(fnd_file.LOG, v_error); END; IF v_error_flag = 'Y' THEN UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA SET STATUS = 'ERRSTG', BATCH_NUM = v_batch_num, orac_error_desc = v_error_message, LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370 LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370 WHERE organization_code = h.organization_code AND rtrim(item_number) = h.item_number; --AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370 --AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370 GOTO ORG; END IF; /* Validation for Assembly Item Name */ BEGIN SELECT msi.segment1 INTO v_item_id FROM mtl_system_items msi WHERE msi.segment1 = h.item_number AND msi.organization_id = v_organization_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_flag := 'Y'; v_error := h.item_number||' is Not a Valid Item '|| 'Org -'|| h.organization_code || ';'; v_error_message := v_error_message||v_error; fnd_file.put_line(fnd_file.LOG, v_error); END; IF v_error_flag = 'Y' THEN UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA SET STATUS = 'ERRSTG', BATCH_NUM = v_batch_num, orac_error_desc = v_error_message, LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370 LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370 WHERE organization_code = h.organization_code AND rtrim(item_number) = h.item_number; --AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370 --AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370 GOTO ORG; END IF; v_bom_header_rec.assembly_item_name := h.item_number; v_bom_header_rec.organization_code := v_organization_code; v_bom_header_rec.alternate_bom_code := NULL; v_bom_header_rec.Assembly_type := 1; v_bom_header_rec.Transaction_Type := 'Create'; v_bom_header_rec.Return_Status := NULL; v_bom_header_rec.Assembly_Comment := h.SPECIFIC_ASSEMBLY_COMMENT; v_assembly_item_name := h.item_number; v_ii := 0; v_bom_component_tbl.DELETE; v_bom_ref_designator_tbl.DELETE; v_jj := 0; FOR l IN cur_bom_lines_data (h.item_number, h.organization_code) LOOP -- lines loop BEGIN -- Validating component Item Name SELECT msi.segment1 INTO v_component_id FROM mtl_system_items msi WHERE msi.segment1 = l.component_item_number AND msi.organization_id = v_organization_id; IF (l.effectivity_date 'BOM' ,p_api_version_number => 1.0 ,p_init_msg_list => TRUE ,p_bom_header_rec => v_bom_header_rec ,p_bom_revision_tbl => v_bom_revision_tbl ,p_bom_component_tbl => v_bom_component_tbl ,p_bom_ref_designator_tbl => v_bom_ref_designator_tbl ,p_bom_sub_component_tbl => v_bom_sub_component_tbl ,x_bom_header_rec => v_x_bom_header_rec ,x_bom_revision_tbl => v_x_bom_revision_tbl ,x_bom_component_tbl => v_x_bom_component_tbl ,x_bom_ref_designator_tbl => v_x_bom_ref_designator_tbl ,x_bom_sub_component_tbl => v_x_bom_sub_component_tbl ,x_return_status => v_x_return_status ,x_msg_count => v_x_msg_count ,p_debug => 'N' -- This should be 'N' to not to have Debug ,p_output_dir => NULL -- This should be NULL and can be ,p_debug_filename => '' -- This field to be NULL if we do not want any log file in tmp dir ); EXCEPTION WHEN OTHERS THEN v_error_flag := 'Y'; fnd_file.put_line(fnd_file.LOG,'Return Status = '||v_x_return_status || ' Err '|| SQLERRM); END; fnd_file.new_line(fnd_file.LOG,1); fnd_file.put_line(fnd_file.LOG,'Assembly Item: '||v_assembly_item_name); fnd_file.put_line(fnd_file.LOG,'Return Status = '||v_x_return_status); fnd_file.put_line(fnd_file.LOG,'Message Count = '||v_x_msg_count); /**** Error messages ****/ --After API Error_Handler.Get_message_list(v_error_message_list); IF NVL(v_x_return_status, '-1') <> 'S' THEN fnd_file.put_line(fnd_file.LOG,'error-arm1'); -- Error Processing FOR v_ii IN 1..v_x_msg_count LOOP fnd_file.put_line(fnd_file.LOG,TO_CHAR(v_ii)||' MESSAGE TYPE: '||v_error_message_list(v_ii).message_type); fnd_file.put_line(fnd_file.LOG,'MESSAGE TEXT: '||v_error_message_list(v_ii).message_text); v_error_msg:=trim(SUBSTR(v_error_msg||', '||v_error_message_list(v_ii).message_text,1,2000)); END LOOP; -- The business object APIs do not issue commits or rollbacks. It is the responsibility of -- the calling code to issue them. This ensures that parts of the transactions are not left -- in the database. If an error occurs, the whole transaction is rolled back. ROLLBACK; UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA SET STATUS = 'ERRINT', BATCH_NUM = v_batch_num, ORAC_ERROR_DESC = v_error_msg, LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370 LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370 WHERE organization_code = h.organization_code AND rtrim(item_number) = h.item_number; --AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370 --AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370 v_error_msg:=NULL; ELSE --After success UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA SET STATUS = 'PROCESSED', BATCH_NUM = v_batch_num, LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370 LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370 WHERE organization_code = h.organization_code AND rtrim(item_number) = h.item_number; --AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370 --AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370 END IF;-- if NVL(v_x_return_status, '-1') <> 'S' v_error_msg:=NULL; COMMIT; END IF;--if v_error_flag != 'Y' <> NULL; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_flag := 'Y'; fnd_file.put_line(fnd_file.LOG,'Return Status = '||v_x_return_status); UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA SET STATUS = 'ERRSTG', BATCH_NUM = v_batch_num, orac_error_desc = v_error_message, LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370 LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370 WHERE organization_code = h.organization_code AND rtrim(item_number) = h.item_number; --AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370 --AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370 fnd_file.put_line(fnd_file.LOG, h.item_number||' Item is Invalid!' || ' Org'|| h.organization_code); END; END LOOP; -- header loop fnd_file.new_line(fnd_file.LOG,1); fnd_file.put_line(fnd_file.LOG,'Process Completed !'); END XXBOM_IMPORT_VA_PRC; /*End of the procedure*/ /