42: X_return_status OUT NOCOPY VARCHAR2,
43: X_msg_count OUT NOCOPY NUMBER,
44: X_G_msg_data OUT NOCOPY LONG) IS
45:
46: insCtx DBMS_XMLSave.ctxType;
47: rows NUMBER;
48: ename_v VARCHAR2(20);
49:
50: l_rtg_header_rec Bom_Rtg_Pub.Rtg_Header_Rec_Type
834: FND_MESSAGE.SET_NAME('BOM', 'BOM_SETUP_NO_ROWS');
835: FND_MESSAGE.RETRIEVE(X_G_msg_data);
836: ELSE
837: -- get the context handle
838: insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_HEADER_TEMP');
839: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
840: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
841: DBMS_XMLSave.setRowTag(insCtx , 'OperationalRoutingsVO');
842: -- this inserts the document
835: FND_MESSAGE.RETRIEVE(X_G_msg_data);
836: ELSE
837: -- get the context handle
838: insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_HEADER_TEMP');
839: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
840: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
841: DBMS_XMLSave.setRowTag(insCtx , 'OperationalRoutingsVO');
842: -- this inserts the document
843: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_hdr_XML);
836: ELSE
837: -- get the context handle
838: insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_HEADER_TEMP');
839: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
840: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
841: DBMS_XMLSave.setRowTag(insCtx , 'OperationalRoutingsVO');
842: -- this inserts the document
843: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_hdr_XML);
844: -- this closes the handle
837: -- get the context handle
838: insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_HEADER_TEMP');
839: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
840: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
841: DBMS_XMLSave.setRowTag(insCtx , 'OperationalRoutingsVO');
842: -- this inserts the document
843: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_hdr_XML);
844: -- this closes the handle
845: DBMS_XMLSave.closeContext(insCtx);
839: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
840: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
841: DBMS_XMLSave.setRowTag(insCtx , 'OperationalRoutingsVO');
842: -- this inserts the document
843: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_hdr_XML);
844: -- this closes the handle
845: DBMS_XMLSave.closeContext(insCtx);
846: -- Routing Revisions Table
847: IF (P_rtg_rev_XML IS NOT NULL) THEN
841: DBMS_XMLSave.setRowTag(insCtx , 'OperationalRoutingsVO');
842: -- this inserts the document
843: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_hdr_XML);
844: -- this closes the handle
845: DBMS_XMLSave.closeContext(insCtx);
846: -- Routing Revisions Table
847: IF (P_rtg_rev_XML IS NOT NULL) THEN
848: -- get the context handle
849: insCtx := DBMS_XMLSave.newContext('BOM_RTG_REVISIONS_TEMP');
845: DBMS_XMLSave.closeContext(insCtx);
846: -- Routing Revisions Table
847: IF (P_rtg_rev_XML IS NOT NULL) THEN
848: -- get the context handle
849: insCtx := DBMS_XMLSave.newContext('BOM_RTG_REVISIONS_TEMP');
850: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
851: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
852: DBMS_XMLSave.setRowTag(insCtx , 'RoutingRevisionsVO');
853: -- this inserts the document
846: -- Routing Revisions Table
847: IF (P_rtg_rev_XML IS NOT NULL) THEN
848: -- get the context handle
849: insCtx := DBMS_XMLSave.newContext('BOM_RTG_REVISIONS_TEMP');
850: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
851: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
852: DBMS_XMLSave.setRowTag(insCtx , 'RoutingRevisionsVO');
853: -- this inserts the document
854: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_rev_XML);
847: IF (P_rtg_rev_XML IS NOT NULL) THEN
848: -- get the context handle
849: insCtx := DBMS_XMLSave.newContext('BOM_RTG_REVISIONS_TEMP');
850: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
851: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
852: DBMS_XMLSave.setRowTag(insCtx , 'RoutingRevisionsVO');
853: -- this inserts the document
854: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_rev_XML);
855: -- this closes the handle
848: -- get the context handle
849: insCtx := DBMS_XMLSave.newContext('BOM_RTG_REVISIONS_TEMP');
850: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
851: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
852: DBMS_XMLSave.setRowTag(insCtx , 'RoutingRevisionsVO');
853: -- this inserts the document
854: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_rev_XML);
855: -- this closes the handle
856: DBMS_XMLSave.closeContext(insCtx);
850: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
851: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
852: DBMS_XMLSave.setRowTag(insCtx , 'RoutingRevisionsVO');
853: -- this inserts the document
854: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_rev_XML);
855: -- this closes the handle
856: DBMS_XMLSave.closeContext(insCtx);
857: END IF;
858:
852: DBMS_XMLSave.setRowTag(insCtx , 'RoutingRevisionsVO');
853: -- this inserts the document
854: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_rev_XML);
855: -- this closes the handle
856: DBMS_XMLSave.closeContext(insCtx);
857: END IF;
858:
859: -- Routing Operations Table
860: IF (P_rtg_op_XML IS NOT NULL) THEN
858:
859: -- Routing Operations Table
860: IF (P_rtg_op_XML IS NOT NULL) THEN
861: -- get the context handle
862: insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_OPERATIONS_TEMP');
863: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
864: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
865: DBMS_XMLSave.setRowTag(insCtx , 'OperationSequencesVO');
866: -- this inserts the document
859: -- Routing Operations Table
860: IF (P_rtg_op_XML IS NOT NULL) THEN
861: -- get the context handle
862: insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_OPERATIONS_TEMP');
863: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
864: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
865: DBMS_XMLSave.setRowTag(insCtx , 'OperationSequencesVO');
866: -- this inserts the document
867: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_XML);
860: IF (P_rtg_op_XML IS NOT NULL) THEN
861: -- get the context handle
862: insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_OPERATIONS_TEMP');
863: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
864: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
865: DBMS_XMLSave.setRowTag(insCtx , 'OperationSequencesVO');
866: -- this inserts the document
867: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_XML);
868: -- this closes the handle
861: -- get the context handle
862: insCtx := DBMS_XMLSave.newContext('BOM_ROUTING_OPERATIONS_TEMP');
863: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
864: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
865: DBMS_XMLSave.setRowTag(insCtx , 'OperationSequencesVO');
866: -- this inserts the document
867: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_XML);
868: -- this closes the handle
869: DBMS_XMLSave.closeContext(insCtx);
863: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
864: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
865: DBMS_XMLSave.setRowTag(insCtx , 'OperationSequencesVO');
866: -- this inserts the document
867: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_XML);
868: -- this closes the handle
869: DBMS_XMLSave.closeContext(insCtx);
870: END IF;
871:
865: DBMS_XMLSave.setRowTag(insCtx , 'OperationSequencesVO');
866: -- this inserts the document
867: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_XML);
868: -- this closes the handle
869: DBMS_XMLSave.closeContext(insCtx);
870: END IF;
871:
872: -- Routing Operation Resources Table
873: IF (P_rtg_op_res_XML IS NOT NULL) THEN
871:
872: -- Routing Operation Resources Table
873: IF (P_rtg_op_res_XML IS NOT NULL) THEN
874: -- get the context handle
875: insCtx := DBMS_XMLSave.newContext('BOM_RTG_OPER_RES_TEMP');
876: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
877: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
878: DBMS_XMLSave.setRowTag(insCtx , 'OperationResourcesVO');
879: -- this inserts the document
872: -- Routing Operation Resources Table
873: IF (P_rtg_op_res_XML IS NOT NULL) THEN
874: -- get the context handle
875: insCtx := DBMS_XMLSave.newContext('BOM_RTG_OPER_RES_TEMP');
876: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
877: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
878: DBMS_XMLSave.setRowTag(insCtx , 'OperationResourcesVO');
879: -- this inserts the document
880: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_res_XML);
873: IF (P_rtg_op_res_XML IS NOT NULL) THEN
874: -- get the context handle
875: insCtx := DBMS_XMLSave.newContext('BOM_RTG_OPER_RES_TEMP');
876: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
877: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
878: DBMS_XMLSave.setRowTag(insCtx , 'OperationResourcesVO');
879: -- this inserts the document
880: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_res_XML);
881: -- this closes the handle
874: -- get the context handle
875: insCtx := DBMS_XMLSave.newContext('BOM_RTG_OPER_RES_TEMP');
876: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
877: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
878: DBMS_XMLSave.setRowTag(insCtx , 'OperationResourcesVO');
879: -- this inserts the document
880: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_res_XML);
881: -- this closes the handle
882: DBMS_XMLSave.closeContext(insCtx);
876: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
877: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
878: DBMS_XMLSave.setRowTag(insCtx , 'OperationResourcesVO');
879: -- this inserts the document
880: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_res_XML);
881: -- this closes the handle
882: DBMS_XMLSave.closeContext(insCtx);
883: END IF;
884:
878: DBMS_XMLSave.setRowTag(insCtx , 'OperationResourcesVO');
879: -- this inserts the document
880: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_res_XML);
881: -- this closes the handle
882: DBMS_XMLSave.closeContext(insCtx);
883: END IF;
884:
885: -- Routing Substitute Operation Resources Table
886: IF (p_rtg_sub_op_res_XML IS NOT NULL) THEN
884:
885: -- Routing Substitute Operation Resources Table
886: IF (p_rtg_sub_op_res_XML IS NOT NULL) THEN
887: -- get the context handle
888: insCtx := DBMS_XMLSave.newContext('BOM_SUB_OPER_RESOURCES_TEMP');
889: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
890: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
891: DBMS_XMLSave.setRowTag(insCtx , 'SubstituteOperationResourcesVO');
892: -- this inserts the document
885: -- Routing Substitute Operation Resources Table
886: IF (p_rtg_sub_op_res_XML IS NOT NULL) THEN
887: -- get the context handle
888: insCtx := DBMS_XMLSave.newContext('BOM_SUB_OPER_RESOURCES_TEMP');
889: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
890: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
891: DBMS_XMLSave.setRowTag(insCtx , 'SubstituteOperationResourcesVO');
892: -- this inserts the document
893: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_sub_op_res_XML);
886: IF (p_rtg_sub_op_res_XML IS NOT NULL) THEN
887: -- get the context handle
888: insCtx := DBMS_XMLSave.newContext('BOM_SUB_OPER_RESOURCES_TEMP');
889: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
890: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
891: DBMS_XMLSave.setRowTag(insCtx , 'SubstituteOperationResourcesVO');
892: -- this inserts the document
893: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_sub_op_res_XML);
894: -- this closes the handle
887: -- get the context handle
888: insCtx := DBMS_XMLSave.newContext('BOM_SUB_OPER_RESOURCES_TEMP');
889: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
890: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
891: DBMS_XMLSave.setRowTag(insCtx , 'SubstituteOperationResourcesVO');
892: -- this inserts the document
893: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_sub_op_res_XML);
894: -- this closes the handle
895: DBMS_XMLSave.closeContext(insCtx);
889: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
890: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
891: DBMS_XMLSave.setRowTag(insCtx , 'SubstituteOperationResourcesVO');
892: -- this inserts the document
893: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_sub_op_res_XML);
894: -- this closes the handle
895: DBMS_XMLSave.closeContext(insCtx);
896: END IF;
897:
891: DBMS_XMLSave.setRowTag(insCtx , 'SubstituteOperationResourcesVO');
892: -- this inserts the document
893: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_sub_op_res_XML);
894: -- this closes the handle
895: DBMS_XMLSave.closeContext(insCtx);
896: END IF;
897:
898: -- Routing Operation Networks Table
899: IF (P_rtg_op_network_XML IS NOT NULL) THEN
897:
898: -- Routing Operation Networks Table
899: IF (P_rtg_op_network_XML IS NOT NULL) THEN
900: -- get the context handle
901: insCtx := DBMS_XMLSave.newContext('BOM_OPER_NETWORKS_TEMP');
902: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
903: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
904: DBMS_XMLSave.setRowTag(insCtx , 'OperationNetworksVO');
905: -- this inserts the document
898: -- Routing Operation Networks Table
899: IF (P_rtg_op_network_XML IS NOT NULL) THEN
900: -- get the context handle
901: insCtx := DBMS_XMLSave.newContext('BOM_OPER_NETWORKS_TEMP');
902: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
903: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
904: DBMS_XMLSave.setRowTag(insCtx , 'OperationNetworksVO');
905: -- this inserts the document
906: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_network_XML);
899: IF (P_rtg_op_network_XML IS NOT NULL) THEN
900: -- get the context handle
901: insCtx := DBMS_XMLSave.newContext('BOM_OPER_NETWORKS_TEMP');
902: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
903: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
904: DBMS_XMLSave.setRowTag(insCtx , 'OperationNetworksVO');
905: -- this inserts the document
906: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_network_XML);
907: -- this closes the handle
900: -- get the context handle
901: insCtx := DBMS_XMLSave.newContext('BOM_OPER_NETWORKS_TEMP');
902: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
903: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
904: DBMS_XMLSave.setRowTag(insCtx , 'OperationNetworksVO');
905: -- this inserts the document
906: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_network_XML);
907: -- this closes the handle
908: DBMS_XMLSave.closeContext(insCtx);
902: DBMS_XMLSave.setIgnoreCase(insCtx, 1);
903: DBMS_XMLSave.setDateFormat(insCtx, 'yyyy-MM-dd HH:mm:ss');
904: DBMS_XMLSave.setRowTag(insCtx , 'OperationNetworksVO');
905: -- this inserts the document
906: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_network_XML);
907: -- this closes the handle
908: DBMS_XMLSave.closeContext(insCtx);
909: END IF;
910:
904: DBMS_XMLSave.setRowTag(insCtx , 'OperationNetworksVO');
905: -- this inserts the document
906: rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_network_XML);
907: -- this closes the handle
908: DBMS_XMLSave.closeContext(insCtx);
909: END IF;
910:
911: OPEN rtg_hdr_CUR;
912: LOOP