[Home] [Help]
1: PACKAGE BODY OE_split_util AS
2: /* $Header: OEXUSPLB.pls 120.26.12020000.10 2013/04/15 06:20:29 jialiali ship $ */
3: G_PKG_NAME CONSTANT VARCHAR2(30):='OE_Split_Util';
4: G_min_model NUMBER;
5: G_max_model NUMBER;
1: PACKAGE BODY OE_split_util AS
2: /* $Header: OEXUSPLB.pls 120.26.12020000.10 2013/04/15 06:20:29 jialiali ship $ */
3: G_PKG_NAME CONSTANT VARCHAR2(30):='OE_Split_Util';
4: G_min_model NUMBER;
5: G_max_model NUMBER;
6: g_over_shipment boolean := false;
7: g_remnant_only Boolean := FALSE;
315: END Check_Complete_Shipment;
316:
317: Procedure Get_Service_lines(p_line_tbl IN OE_ORDER_PUB.Line_Tbl_Type,
318: x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_ORDER_PUB.Line_Tbl_Type,
319: g_split_line_tbl oe_split_util.split_line_tbl) IS
320: l_ser_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL;
321: l_line_id NUMBER;
322: l_service_count NUMBER := 1;
323: l_ser_rec OE_ORDER_PUB.Line_rec_Type := OE_ORDER_PUB.G_MISS_LINE_REC;
365: l_ser_line_tbl(l_service_count).line_id := fnd_api.g_miss_num;
366: l_ser_line_tbl(l_service_count).split_from_line_id := l_ser_rec.line_id;
367: l_ser_line_tbl(l_service_count).operation := oe_globals.g_opr_create;
368: g_qry_out_rec := l_ser_line_tbl(l_service_count) ;
369: OE_Split_Util.Default_Attributes(
370: p_x_line_rec =>g_qry_out_rec
371: , p_old_line_rec => l_ser_line_tbl(l_service_count)
372: );
373: l_ser_line_tbl(l_service_count) := g_qry_out_rec;
568: oe_debug_pub.add('Line set id is : '||l_set_id,5) ;
569: END IF;
570: l_parent_line.line_id := fnd_api.g_miss_num;
571: l_parent_line.orig_sys_line_ref :=fnd_api.g_miss_char; -- Bug5207907
572: OE_Split_Util.Default_Attributes
573: (p_x_line_rec => l_parent_line,
574: p_old_line_rec => l_parent_line );
575: IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
576: Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
590: l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
591: l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
592: l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
593: g_qry_out_rec := l_option_line_tbl(l_option_count) ;
594: OE_Split_Util.Default_Attributes
595: ( p_x_line_rec => g_qry_out_rec
596: ,p_old_line_rec => l_option_line_tbl(l_option_count)
597: );
598: l_option_line_tbl(l_option_count) := g_qry_out_rec;
655: l_parent_line.line_id := fnd_api.g_miss_num;
656: l_parent_line.config_header_id := NULL;
657: l_parent_line.config_rev_nbr := NULL;
658: l_parent_line.orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
659: OE_Split_Util.Default_Attributes
660: (p_x_line_rec => l_parent_line
661: ,p_old_line_rec => l_parent_line
662: );
663: l_rem_top_model_line_id := l_parent_line.line_id;
700: l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
701: l_option_line_tbl(l_option_count).split_by := 'SYSTEM';
702: l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
703: g_qry_out_rec := l_option_line_tbl(l_option_count) ;
704: OE_Split_Util.Default_Attributes
705: ( p_x_line_rec => g_qry_out_rec
706: ,p_old_line_rec => l_option_line_tbl(l_option_count)
707: );
708: l_option_line_tbl(l_option_count) := g_qry_out_rec;
770: END IF;
771: l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5223102
772: g_qry_out_rec := l_option_line_tbl(l_option_count) ;
773:
774: OE_Split_Util.Default_Attributes
775: ( p_x_line_rec => g_qry_out_rec
776: ,p_old_line_rec => l_option_line_tbl(l_option_count)
777: );
778:
831: l_parent_line.split_by := 'SYSTEM';
832: l_parent_line.split_from_line_id := l_parent_line.line_id;
833: l_parent_line.line_id := fnd_api.g_miss_num;
834: l_parent_line.orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
835: OE_Split_Util.Default_Attributes
836: ( p_x_line_rec => l_parent_line
837: , p_old_line_rec => l_parent_line
838: );
839: l_parent_line.line_set_id := l_set_id;
896: l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
897: l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
898: l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
899: g_qry_out_rec := l_option_line_tbl(l_option_count) ;
900: OE_Split_Util.Default_Attributes
901: ( p_x_line_rec => g_qry_out_rec
902: ,p_old_line_rec => l_option_line_tbl(l_option_count)
903: );
904: l_option_line_tbl(l_option_count) := g_qry_out_rec;
1102: oe_debug_pub.add( 'LINE SET IS-'||L_SET_ID ) ;
1103: END IF;
1104: l_parent_line.line_id := fnd_api.g_miss_num;
1105: l_parent_line.orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
1106: OE_Split_Util.Default_Attributes
1107: (p_x_line_rec => l_parent_line
1108: ,p_old_line_rec => l_parent_line );
1109: IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
1110: Oe_Set_Util.Create_Line_Set(p_x_line_rec => l_parent_line);
1124: l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
1125: l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
1126: l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
1127: g_qry_out_rec := l_option_line_tbl(l_option_count) ;
1128: OE_Split_Util.Default_Attributes
1129: (p_x_line_rec => g_qry_out_rec
1130: ,p_old_line_rec => l_option_line_tbl(l_option_count)
1131: );
1132: l_option_line_tbl(l_option_count) := g_qry_out_rec;
1180: l_parent_line.line_id := fnd_api.g_miss_num;
1181: l_parent_line.config_header_id := NULL;
1182: l_parent_line.config_rev_nbr := NULL;
1183: l_parent_line.orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
1184: OE_Split_Util.Default_Attributes
1185: ( p_x_line_rec => l_parent_line
1186: ,p_old_line_rec => l_parent_line );
1187: l_rem_shp_top_model_line_id := l_parent_line.line_id;
1188: IF l_parent_line.line_set_id is null AND l_set_id IS NULL THEN
1211: l_parent_line.operation := oe_globals.g_opr_create;
1212: l_parent_line.split_by := 'SYSTEM';
1213: l_parent_line.split_from_line_id := l_parent_line.line_id;
1214: l_parent_line.line_id := fnd_api.g_miss_num;
1215: OE_Split_Util.Default_Attributes
1216: ( p_x_line_rec => l_parent_line
1217: ,p_old_line_rec => l_parent_line
1218: );
1219: l_parent_line.line_set_id := l_set_id;
1255: l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
1256: l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
1257: l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
1258: g_qry_out_rec := l_option_line_tbl(l_option_count) ;
1259: OE_Split_Util.Default_Attributes
1260: (p_x_line_rec => g_qry_out_rec
1261: , p_old_line_rec => l_option_line_tbl(l_option_count));
1262: l_option_line_tbl(l_option_count) := g_qry_out_rec;
1263: l_option_line_tbl(l_option_count).ordered_quantity :=
1327: l_option_line_tbl(l_option_count).line_id := fnd_api.g_miss_num;
1328: l_option_line_tbl(l_option_count).operation := oe_globals.g_opr_create;
1329: l_option_line_tbl(l_option_count).orig_sys_line_ref := fnd_api.g_miss_char; --Bug5207907
1330: g_qry_out_rec := l_option_line_tbl(l_option_count) ;
1331: OE_Split_Util.Default_Attributes
1332: ( p_x_line_rec => g_qry_out_rec
1333: , p_old_line_rec => l_option_line_tbl(l_option_count)
1334: );
1335: l_option_line_tbl(l_option_count) := g_qry_out_rec;
1407: l_line_scredit_tbl OE_Order_Pub.Line_scredit_Tbl_type;
1408: l_line_scredit_temp_tbl OE_Order_Pub.Line_scredit_Tbl_type;
1409: l_adjustment_count NUMBER := 0;
1410: l_scredit_count NUMBER := 0;
1411: l_split_line_tbl oe_split_util.split_line_tbl;
1412: l_split_count NUMBER := 0;
1413: l_model_map_tbl oe_split_util.model_map_tbl;
1414: l_map_count NUMBER := 0;
1415: L_FOUND BOOLEAN; --8706868
1409: l_adjustment_count NUMBER := 0;
1410: l_scredit_count NUMBER := 0;
1411: l_split_line_tbl oe_split_util.split_line_tbl;
1412: l_split_count NUMBER := 0;
1413: l_model_map_tbl oe_split_util.model_map_tbl;
1414: l_map_count NUMBER := 0;
1415: L_FOUND BOOLEAN; --8706868
1416: l_order_date_type_code VARCHAR2(20); --bug 13954474
1417: Type Optioncur IS REF CURSOR;
1526: l_option_line_tbl(l_option_count).split_from_line_id := optionrec.line_id;
1527: l_option_line_tbl(l_option_count).split_by := p_line_tbl(p_index).split_by;
1528: g_qry_out_rec := l_option_line_tbl(l_option_count) ;
1529: g_qry_out_rec.orig_sys_line_ref :=fnd_api.g_miss_char; --bug5207907
1530: OE_Split_Util.Default_Attributes ( p_x_line_rec => g_qry_out_rec
1531: ,p_old_line_rec => l_option_line_tbl(l_option_count));
1532: l_option_line_tbl(l_option_count) := g_qry_out_rec;
1533:
1534: -- l_option_line_tbl(l_option_count).top_model_line_id := g_split_line_tbl(I).line_id; -- commeneted for bug11714193
1686: l_code_level := OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL;
1687: IF l_debug_level > 0 THEN
1688: oe_debug_pub.add( 'L_CODE_LEVEL = '||L_CODE_LEVEL , 5 ) ;
1689: END IF;
1690: IF oe_split_util.g_sch_recursion = 'FALSE' THEN
1691: IF l_debug_level > 0 THEN
1692: oe_debug_pub.add( 'ENTER DEFAULT ATTRIBUTES FOR SPLIT' ) ;
1693: oe_debug_pub.add( 'LINEID:'||P_X_LINE_REC.LINE_ID ) ;
1694: oe_debug_pub.add( 'SPLITLINEID:'||P_X_LINE_REC.SPLIT_FROM_LINE_ID ) ;
1938: l_line_scredit_tbl OE_Order_Pub.Line_scredit_Tbl_type;
1939: l_line_scredit_temp_tbl OE_Order_Pub.Line_scredit_Tbl_type;
1940: l_adjustment_count NUMBER := 0;
1941: l_scredit_count NUMBER := 0;
1942: l_Split_line_Tbl OE_SPLIT_UTIL.Split_line_Tbl;
1943: l_split_count NUMBER := 0;
1944: l_miss_rec OE_Order_Pub.Line_rec_type := oe_order_pub.g_miss_line_rec;
1945: l_sch_tbl OE_Order_Pub.Line_tbl_type;
1946: l_sch_count NUMBER := 0;
1956: IF l_debug_level > 0 THEN
1957: oe_debug_pub.add( 'ENTER CHECK SPLIT COURSE' ) ;
1958: END IF;
1959: l_line_tbl := p_x_line_tbl;
1960: IF oe_split_util.g_sch_recursion = 'FALSE' AND NOT g_non_prop_split THEN
1961: -- Loop Through Lines Table to find Split Action
1962: -- i := l_line_tbl.First;
1963: -- This Check is to make sure Split will not process any table
1964: -- that is not in sesequence. It will fail if there is a gap in the
2118: END IF;
2119: RAISE FND_API.G_EXC_ERROR;
2120: END IF;
2121: g_qry_out_rec := l_line_tbl(J);
2122: OE_Split_Util.Default_Attributes (p_x_line_rec => g_qry_out_rec
2123: ,p_old_line_rec => l_line_tbl(J));
2124:
2125: l_line_tbl(J) := g_qry_out_rec;
2126: l_split_count := l_split_count + 1;
2211: END IF;
2212: -- Call scheduling
2213: -- This call to scheduling is commented to fix splitting of reservations
2214: -- issue. The call is moved to post line loop and is in control of scheduling
2215: OE_SPLIT_UTIL.G_SPLIT_ACTION := TRUE;
2216: /* OE_ORDER_SCH_UTIL.Split_Scheduling(p_line_tbl => l_sch_tbl,
2217: x_line_tbl => l_sch_tbl,
2218: x_return_status => l_return_status);
2219: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2480: l_control_rec.controlled_operation := TRUE;
2481: l_control_rec.check_security := FALSE;
2482: l_control_rec.change_attributes := TRUE;
2483: l_control_rec.default_attributes := TRUE;
2484: OE_SPLIT_UTIL.G_SPLIT_ACTION := TRUE;
2485: IF l_debug_level > 0 THEN
2486: oe_debug_pub.add('Before calling process order in splits',1) ;
2487: END IF;
2488: oe_order_pvt.Lines
2526: , p_old_line_scredit_tbl => l_old_line_Scredit_tbl
2527: );
2528: g_non_prop_split := FALSE;
2529: OE_CONFIG_PVT.OECFG_VALIDATE_CONFIG := 'Y' ;
2530: OE_SPLIT_UTIL.G_SPLIT_ACTION := FALSE;
2531: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2532: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2533: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2534: RAISE FND_API.G_EXC_ERROR;
2639: l_x_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
2640: l_x_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
2641: l_control_rec OE_GLOBALS.Control_Rec_Type;
2642: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2643: l_line_conc_rec OE_Split_Util.Split_Line_Rec_Type;
2644: l_line_conc_tbl OE_Split_Util.Split_Line_Tbl_Type;
2645: l_rec_count number;
2646: l_return_status VARCHAR2(1);
2647: l_msg_data VARCHAR2(2000);
2640: l_x_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
2641: l_control_rec OE_GLOBALS.Control_Rec_Type;
2642: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2643: l_line_conc_rec OE_Split_Util.Split_Line_Rec_Type;
2644: l_line_conc_tbl OE_Split_Util.Split_Line_Tbl_Type;
2645: l_rec_count number;
2646: l_return_status VARCHAR2(1);
2647: l_msg_data VARCHAR2(2000);
2648: l_msg_count NUMBER;
2671: FOR UPDATE NOWAIT;
2672:
2673:
2674: BEGIN
2675: oe_debug_pub.add('Entering Procedure OE_Split_Util.Defer_Split', 1);
2676: l_init_line_id :=to_number(p_line_id);
2677:
2678: select org_id
2679: into l_org_id
2695: INTO l_line_conc_tbl;
2696: CLOSE c_split_details;
2697: EXCEPTION
2698: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
2699: oe_debug_pub.add('record_lock exception in Oe_Split_Util.Defer_Split',1);
2700: Retcode := 2;
2701: errbuf := sqlerrm;
2702: raise;
2703: END;
3025: l_x_Line_Scredit_tbl.DELETE;
3026: l_x_lot_serial_tbl.DELETE;
3027:
3028: IF l_process_add_attributes THEN
3029: oe_debug_pub.add('calling process_order for service lines from oe_split_util.defer_split',5);
3030: Oe_Order_Pvt.Process_order
3031: ( p_api_version_number => 1.0
3032: , p_init_msg_list => FND_API.G_TRUE
3033: , x_return_status => l_return_status
3100: FND_FILE.put_line(FND_FILE.output,' << No Errors or Warnings reported >>');
3101: END IF;
3102: END IF;
3103: COMMIT;
3104: oe_debug_pub.add('Exiting Procedure OE_Split_Util.Defer_Split', 1);
3105:
3106: EXCEPTION
3107: WHEN OTHERS THEN
3108: retcode := 2;
3105:
3106: EXCEPTION
3107: WHEN OTHERS THEN
3108: retcode := 2;
3109: oe_debug_pub.add('Inside the exception block of oe_split_util.defer_split',5);
3110: IF NVL(FND_PROFILE.VALUE('CONC_REQUEST_ID'), 0) <> 0 THEN
3111: -- Called from concurrent request */
3112: IF l_msg_total > 0 THEN
3113: FOR I IN 1 .. l_msg_total LOOP
3135:
3136: PROCEDURE Bulk_Insert (p_line_conc_tbl IN Split_Line_Tbl_Type) is
3137:
3138: begin
3139: oe_debug_pub.add('entering oe_split_util.bulk_insert');
3140:
3141: FORALL j IN p_line_conc_tbl.FIRST .. p_line_conc_tbl.LAST
3142: INSERT INTO OE_LINE_SPLIT_DETAILS
3143: VALUES p_line_conc_tbl(j);
3149: end Bulk_Insert;
3150:
3151: /* Defer Split ER Changes END */
3152:
3153: END OE_Split_Util;