[Home] [Help]
1: PACKAGE BODY OE_Holds_Pvt AS
2: /* $Header: OEXVHLDB.pls 120.47.12020000.5 2013/04/10 09:04:18 spothula ship $ */
3:
4: -- Global constant holding the package name
5:
2: /* $Header: OEXVHLDB.pls 120.47.12020000.5 2013/04/10 09:04:18 spothula ship $ */
3:
4: -- Global constant holding the package name
5:
6: G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Holds_Pvt';
7:
8:
9: Procedure Release_Hold_Source (
10: p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type,
6: G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Holds_Pvt';
7:
8:
9: Procedure Release_Hold_Source (
10: p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type,
11: p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type,
12: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
13: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
14: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7:
8:
9: Procedure Release_Hold_Source (
10: p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type,
11: p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type,
12: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
13: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
14: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
15: );
441: x_return_status Return status
442: */
443: PROCEDURE progress_order( p_hold_id IN NUMBER,
444: p_num_of_records IN NUMBER,
445: p_order_tbl OE_HOLDS_PVT.order_tbl_type,
446: x_return_status OUT NOCOPY VARCHAR2,
447: x_msg_count OUT NOCOPY NUMBER,
448: x_msg_data OUT NOCOPY VARCHAR2
449: )
1045: ,p_activity_status VARCHAR2
1046: ,p_additional_where_clause VARCHAR2
1047: ,x_is_hold_applied OUT NOCOPY BOOLEAN)
1048: IS
1049: l_user_id NUMBER := OE_HOLDS_PVT.get_user_id;
1050: l_parent_count NUMBER;
1051: l_user_activity_name VARCHAR2(80);
1052: l_sql_rowcount NUMBER;
1053: l_sqlmt VARCHAR2(3000);
1316: p_activity_status VARCHAR2,
1317: p_additional_where_clause VARCHAR2,
1318: x_is_hold_applied OUT NOCOPY BOOLEAN)
1319: IS
1320: l_user_id NUMBER := OE_HOLDS_PVT.get_user_id;
1321: l_parent_count NUMBER;
1322: l_user_activity_name VARCHAR2(80);
1323: l_sql_rowcount NUMBER;
1324: l_sqlmt VARCHAR2(3000);
1599: -- PARAMETERS:
1600: -- p_hold_source_id : Hold Source Id of the hold
1601: -- p_header_id : Line Id if the hold is applied using action -> apply hold for a specific Hold
1602: ---------------------------------------------------------------------------------------------------
1603: Procedure PaymentType_Hold (p_hold_source_rec OE_HOLDS_PVT.Hold_source_Rec_Type
1604: ,p_org_id OE_ORDER_HEADERS_ALL.org_id%type
1605: ,p_item_type VARCHAR2
1606: ,p_activity_name VARCHAR2
1607: ,p_activity_status VARCHAR2
1844: /****************************
1845: | RELEASE_ORDERS
1846: ***************************/
1847: Procedure release_orders (
1848: p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type,
1849: p_order_rec IN OE_HOLDS_PVT.order_rec_type,
1850: p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type,
1851: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1852: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1845: | RELEASE_ORDERS
1846: ***************************/
1847: Procedure release_orders (
1848: p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type,
1849: p_order_rec IN OE_HOLDS_PVT.order_rec_type,
1850: p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type,
1851: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1852: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1853: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1846: ***************************/
1847: Procedure release_orders (
1848: p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type,
1849: p_order_rec IN OE_HOLDS_PVT.order_rec_type,
1850: p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type,
1851: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1852: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1853: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1854: )
1919: l_hold_entity_code OE_HOLD_SOURCES.HOLD_ENTITY_CODE%TYPE;
1920: l_order_hold_id OE_ORDER_HOLDS.ORDER_HOLD_ID%TYPE;
1921: l_hold_id OE_HOLD_DEFINITIONS.hold_id%type;
1922:
1923: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
1924: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1925: BEGIN
1926: x_return_status := FND_API.G_RET_STS_SUCCESS;
1927: l_user_id := OE_HOLDS_PVT.get_user_id;
1923: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
1924: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1925: BEGIN
1926: x_return_status := FND_API.G_RET_STS_SUCCESS;
1927: l_user_id := OE_HOLDS_PVT.get_user_id;
1928: -- XX Also check for release reason code
1929: OPEN hold_source;
1930: LOOP
1931:
1942: l_hold_source_rec.hold_source_id := l_hold_source_id;
1943: l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
1944: l_hold_source_rec.HOLD_ENTITY_ID := p_order_rec.header_id;
1945: l_hold_source_rec.HOLD_ID := l_hold_id;
1946: oe_holds_pvt.Release_Hold_Source (
1947: p_hold_source_rec => l_hold_source_rec,
1948: p_hold_release_rec => p_hold_release_rec,
1949: x_return_status => x_return_status,
1950: x_msg_count => x_msg_count,
2035: END IF;
2036: -- XX Also check to see if its the last order from a non order based
2037: -- hold and release the hold source also. NO
2038: -- 16053361
2039: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Applying hold');
2040: If NVL (Fnd_Profile.Value('ONT_RAISE_STATUS_CHANGE_BUSINESS_EVENT'), 'N')='Y'
2041: THEN
2042: IF l_debug_level > 0 THEN
2043: oe_debug_pub.add( 'OE_HOLDS_PVT - Preparing paramters to pass to Integration event for Releasing hold');
2039: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Applying hold');
2040: If NVL (Fnd_Profile.Value('ONT_RAISE_STATUS_CHANGE_BUSINESS_EVENT'), 'N')='Y'
2041: THEN
2042: IF l_debug_level > 0 THEN
2043: oe_debug_pub.add( 'OE_HOLDS_PVT - Preparing paramters to pass to Integration event for Releasing hold');
2044: END IF;
2045: l_count := 1;
2046: l_nameVal_tbl(l_count).name := 'CHANGE_TYPE';
2047: l_nameVal_tbl(l_count).value := 'RELEASE';
2070: p_line_id => l_line_id,
2071: p_event_name => 'oracle.apps.ont.hold.action',
2072: p_nameVal_tbl => l_nameVal_tbl);
2073: IF l_debug_level > 0 THEN
2074: oe_debug_pub.add( 'OE_HOLDS_PVT - Done with Raising Integration event for Releasing hold');
2075: END IF;
2076: END IF;
2077: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Applying hold');
2078: -- 16053361
2073: IF l_debug_level > 0 THEN
2074: oe_debug_pub.add( 'OE_HOLDS_PVT - Done with Raising Integration event for Releasing hold');
2075: END IF;
2076: END IF;
2077: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Applying hold');
2078: -- 16053361
2079:
2080: -- GENESIS --
2081: IF l_debug_level > 0 THEN
2078: -- 16053361
2079:
2080: -- GENESIS --
2081: IF l_debug_level > 0 THEN
2082: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDERS- BEFORE GENESIS CALL');
2083: END IF;
2084:
2085: OPEN check_hold_type_cur(l_order_hold_id);
2086: FETCH check_hold_type_cur INTO l_chk_hold;
2087: CLOSE check_hold_type_cur;
2088:
2089: IF NVL(l_chk_hold, 'N') = 'Y' THEN
2090: IF l_debug_level > 0 THEN
2091: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDERS- BEFORE SYNC_HEADER_LINE');
2092: END IF;
2093:
2094: -- Bug 8463870
2095: for hld_rls_rec in header_line_id_cur(l_order_hold_id)
2137:
2138:
2139:
2140: Procedure Validate_Hold_Source (
2141: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
2142: , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2143: , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
2144: , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2145: )
2752:
2753: ----------------------
2754: ---Overload procedure for bug5548778
2755: Procedure Create_Hold_Source (
2756: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
2757: p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
2758: x_hold_source_id OUT NOCOPY /* file.sql.39 change */ OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE,
2759: x_hold_exists OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2760: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2765: l_user_id NUMBER;
2766: l_org_id NUMBER;
2767: l_count NUMBER;
2768: --l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
2769: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
2770: BEGIN
2771: x_return_status := FND_API.G_RET_STS_SUCCESS;
2772: x_hold_exists := 'N';
2773: l_user_id := OE_HOLDS_PVT.get_user_id;
2769: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
2770: BEGIN
2771: x_return_status := FND_API.G_RET_STS_SUCCESS;
2772: x_hold_exists := 'N';
2773: l_user_id := OE_HOLDS_PVT.get_user_id;
2774: l_org_id := MO_GLOBAL.get_current_org_id;
2775: IF l_org_id IS NULL THEN
2776: -- org_id is null, raise an error.
2777: oe_debug_pub.add('Org_Id is NULL',1);
2995: END Create_Hold_Source;
2996:
2997: ------
2998: Procedure Create_Hold_Source (
2999: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
3000: p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
3001: x_hold_source_id OUT NOCOPY /* file.sql.39 change */ OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE,
3002: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3003: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3007: l_user_id NUMBER;
3008: l_org_id NUMBER;
3009: l_count NUMBER;
3010: --l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
3011: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
3012: l_hold_comment OE_HOLD_SOURCES_ALL.HOLD_COMMENT%TYPE; --ER#7479609
3013: BEGIN
3014: x_return_status := FND_API.G_RET_STS_SUCCESS;
3015: l_user_id := OE_HOLDS_PVT.get_user_id;
3011: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
3012: l_hold_comment OE_HOLD_SOURCES_ALL.HOLD_COMMENT%TYPE; --ER#7479609
3013: BEGIN
3014: x_return_status := FND_API.G_RET_STS_SUCCESS;
3015: l_user_id := OE_HOLDS_PVT.get_user_id;
3016: l_org_id := MO_GLOBAL.get_current_org_id;
3017: IF l_org_id IS NULL THEN
3018: -- org_id is null, raise an error.
3019: oe_debug_pub.add('Org_Id is NULL',1);
3271:
3272:
3273: ---------------------
3274: Procedure Create_Order_Holds(
3275: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
3276: p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
3277: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
3278: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
3279: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
3341: -- GENESIS --
3342: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3343: BEGIN
3344: x_return_status := FND_API.G_RET_STS_SUCCESS;
3345: l_user_id := OE_HOLDS_PVT.get_user_id;
3346: l_org_id := MO_GLOBAL.get_current_org_id;
3347: IF l_org_id IS NULL THEN
3348: -- org_id is null, raise an error.
3349: oe_debug_pub.add('Org_Id is NULL',1);
3359: from oe_hold_definitions
3360: where hold_id = p_hold_source_rec.hold_id;
3361: Exception
3362: When NO_DATA_FOUND Then
3363: NULL; -- OE_Holds_Pvt.Validate has not yet been called.
3364: End;
3365:
3366: OE_DEBUG_PUB.Add ('Calling Overloaded Create_Order_Holds Based on Workflow from original Create_Order_Holds',1);
3367: Create_Order_Holds (
5092: -- GENESIS --
5093: IF p_hold_source_rec.line_id IS NOT NULL THEN
5094:
5095: IF l_debug_level > 0 THEN
5096: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.line_id IS NOT NULL');
5097: END IF;
5098:
5099: OPEN check_line_hold_type_cur(p_hold_source_rec.line_id);
5100: FETCH check_line_hold_type_cur INTO l_check_hold;
5099: OPEN check_line_hold_type_cur(p_hold_source_rec.line_id);
5100: FETCH check_line_hold_type_cur INTO l_check_hold;
5101: CLOSE check_line_hold_type_cur;
5102: IF l_debug_level > 0 THEN
5103: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold' || l_check_hold);
5104: END IF;
5105:
5106: ELSIF p_hold_source_rec.line_id IS NULL AND
5107: p_hold_source_rec.header_id IS NOT NULL THEN
5106: ELSIF p_hold_source_rec.line_id IS NULL AND
5107: p_hold_source_rec.header_id IS NOT NULL THEN
5108:
5109: IF l_debug_level > 0 THEN
5110: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.header_id IS NOT NULL');
5111: END IF;
5112: OPEN check_hdr_hold_type_cur(p_hold_source_rec.header_id);
5113: FETCH check_hdr_hold_type_cur INTO l_check_hold;
5114: CLOSE check_hdr_hold_type_cur;
5112: OPEN check_hdr_hold_type_cur(p_hold_source_rec.header_id);
5113: FETCH check_hdr_hold_type_cur INTO l_check_hold;
5114: CLOSE check_hdr_hold_type_cur;
5115: IF l_debug_level > 0 THEN
5116: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold' || l_check_hold);
5117: END IF;
5118:
5119: ELSIF p_hold_source_rec.line_id IS NULL AND
5120: p_hold_source_rec.header_id IS NULL AND
5120: p_hold_source_rec.header_id IS NULL AND
5121: p_hold_source_rec.hold_source_id IS NOT NULL THEN
5122:
5123: IF l_debug_level > 0 THEN
5124: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.hold_source_id IS NOT NULL');
5125: END IF;
5126: OPEN check_src_hold_type_cur(p_hold_source_rec.hold_source_id);
5127: FETCH check_src_hold_type_cur INTO l_check_hold;
5128: CLOSE check_src_hold_type_cur;
5127: FETCH check_src_hold_type_cur INTO l_check_hold;
5128: CLOSE check_src_hold_type_cur;
5129:
5130: IF l_debug_level > 0 THEN
5131: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold :' || l_check_hold);
5132: END IF;
5133:
5134: END IF;
5135:
5133:
5134: END IF;
5135:
5136: IF l_debug_level > 0 THEN
5137: oe_debug_pub.add( 'OE_HOLDS_PVT - CREATE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
5138: END IF;
5139:
5140: IF NVL(l_check_hold, 'N') = 'Y' THEN
5141:
5139:
5140: IF NVL(l_check_hold, 'N') = 'Y' THEN
5141:
5142: IF l_debug_level > 0 THEN
5143: oe_debug_pub.add( 'OE_HOLDS_PVT - CREATE ORDER HOLDS - l_check_hold: ' || l_check_hold);
5144: END IF;
5145:
5146: IF p_hold_source_rec.hold_entity_code = 'O' THEN
5147: IF l_debug_level > 0 THEN
5230: END Create_Order_Holds;
5231:
5232:
5233: Procedure Release_Order_holds (
5234: p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type,
5235: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5236: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5237: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
5238: IS
5278: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5279:
5280: BEGIN
5281: x_return_status := FND_API.G_RET_STS_SUCCESS;
5282: l_user_id := OE_HOLDS_PVT.get_user_id;
5283: -- Flag all orders and order line holds for this hold source
5284: -- as released
5285: UPDATE oe_order_holds
5286: SET hold_release_id = p_hold_release_rec.hold_release_id,
5295: -- be the case if selected lines of an order were put on hold.
5296: -- IF l_entity_code = 'O' THEN
5297: -- END IF;
5298: -- 16053361
5299: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Releasing hold');
5300: If NVL (Fnd_Profile.Value('ONT_RAISE_STATUS_CHANGE_BUSINESS_EVENT'), 'N')='Y'
5301: THEN
5302: IF l_debug_level > 0 THEN
5303: oe_debug_pub.add( 'OE_HOLDS_PVT - Preparing paramters to pass to Integration event for Releasing hold');
5299: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Releasing hold');
5300: If NVL (Fnd_Profile.Value('ONT_RAISE_STATUS_CHANGE_BUSINESS_EVENT'), 'N')='Y'
5301: THEN
5302: IF l_debug_level > 0 THEN
5303: oe_debug_pub.add( 'OE_HOLDS_PVT - Preparing paramters to pass to Integration event for Releasing hold');
5304: END IF;
5305: l_count := 1;
5306: l_nameVal_tbl(l_count).name := 'CHANGE_TYPE';
5307: l_nameVal_tbl(l_count).value := 'RELEASE';
5332: p_line_id => l_line_id,
5333: p_event_name => 'oracle.apps.ont.hold.action',
5334: p_nameVal_tbl => l_nameVal_tbl);
5335: IF l_debug_level > 0 THEN
5336: oe_debug_pub.add( 'OE_HOLDS_PVT - Done with Raising Integration event for Releasing hold');
5337: END IF;
5338: END IF;
5339: oe_debug_pub.add( 'OE_HOLDS_PVT - Raised Integration event for Releasing hold');
5340: -- 16053361
5335: IF l_debug_level > 0 THEN
5336: oe_debug_pub.add( 'OE_HOLDS_PVT - Done with Raising Integration event for Releasing hold');
5337: END IF;
5338: END IF;
5339: oe_debug_pub.add( 'OE_HOLDS_PVT - Raised Integration event for Releasing hold');
5340: -- 16053361
5341: -- GENESIS --
5342: IF l_debug_level > 0 THEN
5343: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDER HOLDS - BEFORE GENESIS CALL');
5339: oe_debug_pub.add( 'OE_HOLDS_PVT - Raised Integration event for Releasing hold');
5340: -- 16053361
5341: -- GENESIS --
5342: IF l_debug_level > 0 THEN
5343: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDER HOLDS - BEFORE GENESIS CALL');
5344: END IF;
5345: OPEN check_hold_typ_cur(p_hold_release_rec.hold_source_id);
5346: FETCH check_hold_typ_cur INTO l_chk_hld;
5347: CLOSE check_hold_typ_cur;
5347: CLOSE check_hold_typ_cur;
5348:
5349: IF NVL(l_chk_hld, 'N') = 'Y' THEN
5350: IF l_debug_level > 0 THEN
5351: oe_debug_pub.add( 'OE_HOLDS_PVT - 1 RELEASE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
5352: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_source _id '|| p_hold_release_rec.hold_source_id);
5353: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_release_id' ||p_hold_release_rec.hold_release_id);
5354: END IF;
5355:
5348:
5349: IF NVL(l_chk_hld, 'N') = 'Y' THEN
5350: IF l_debug_level > 0 THEN
5351: oe_debug_pub.add( 'OE_HOLDS_PVT - 1 RELEASE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
5352: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_source _id '|| p_hold_release_rec.hold_source_id);
5353: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_release_id' ||p_hold_release_rec.hold_release_id);
5354: END IF;
5355:
5356: -- Bug 8463870
5349: IF NVL(l_chk_hld, 'N') = 'Y' THEN
5350: IF l_debug_level > 0 THEN
5351: oe_debug_pub.add( 'OE_HOLDS_PVT - 1 RELEASE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
5352: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_source _id '|| p_hold_release_rec.hold_source_id);
5353: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_release_id' ||p_hold_release_rec.hold_release_id);
5354: END IF;
5355:
5356: -- Bug 8463870
5357: for hld_rls_rec in header_line_id_cur(p_hold_release_rec.order_hold_id)
5398:
5399:
5400:
5401: PROCEDURE Create_Release_Source (
5402: p_hold_release_rec IN OE_Holds_Pvt.Hold_Release_Rec_type,
5403: x_hold_release_id OUT NOCOPY /* file.sql.39 change */ oe_hold_releases.HOLD_RELEASE_ID%type,
5404: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5405: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5406: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5407: )
5408: IS
5409: l_dummy VARCHAR2(30);
5410: l_user_id NUMBER;
5411: l_hold_release_rec OE_Holds_Pvt.Hold_Release_Rec_type;
5412: BEGIN
5413:
5414: -- Fix For Bug 1903900
5415: SAVEPOINT insert_hold_release;
5414: -- Fix For Bug 1903900
5415: SAVEPOINT insert_hold_release;
5416: x_return_status := FND_API.G_RET_STS_SUCCESS;
5417:
5418: l_user_id := OE_HOLDS_PVT.get_user_id;
5419:
5420: -- Validate Reason Code
5421: BEGIN
5422:
5540:
5541: END Create_Release_Source;
5542:
5543: Procedure Release_Hold_Source (
5544: p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type,
5545: p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type,
5546: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5547: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5548: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5541: END Create_Release_Source;
5542:
5543: Procedure Release_Hold_Source (
5544: p_hold_source_rec IN OE_HOLDS_PVT.Hold_source_Rec_Type,
5545: p_hold_release_rec IN OE_HOLDS_PVT.hold_release_rec_type,
5546: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5547: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5548: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5549: )
5547: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5548: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5549: )
5550: IS
5551: l_hold_release_rec OE_HOLDS_PVT.hold_release_rec_type;
5552: l_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
5553: --
5554: CURSOR hold_source IS
5555: SELECT HS.HOLD_SOURCE_ID
5638: oe_debug_pub.add('Using Hold Source ID:' ||
5639: to_char(l_hold_release_rec.hold_source_id) ,1);
5640: END IF;
5641:
5642: OE_HOLDS_PVT.Create_Release_Source(p_hold_release_rec => l_hold_release_rec,
5643: x_hold_release_id => l_hold_release_id,
5644: x_return_status => x_return_status,
5645: x_msg_count => x_msg_count,
5646: x_msg_data => x_msg_data);
5655: oe_debug_pub.add('Calling Release_Order_holds using l_hold_release_id' ||
5656: to_char(l_hold_release_id) ,1);
5657: l_hold_release_rec.hold_release_id := l_hold_release_id;
5658:
5659: OE_HOLDS_PVT.Release_Order_holds(
5660: p_hold_release_rec => l_hold_release_rec,
5661: x_return_status => x_return_status,
5662: x_msg_count => x_msg_count,
5663: x_msg_data => x_msg_data);
5732: END Validate_Order;
5733:
5734:
5735: Procedure Validate (
5736: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_type,
5737: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5738: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5739: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
5740: IS
5770: -- not used by an existing hold or hold source, then deletes the
5771: -- release record also from OE_HOLD_RELEASES;
5772: ----------------------------------------------------------------------------
5773: PROCEDURE Delete_Holds (
5774: p_order_rec IN OE_HOLDS_PVT.order_rec_Type,
5775: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5776: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5777: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5778: )
5954:
5955:
5956: --------------------
5957: Procedure Apply_Holds (
5958: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
5959: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
5960: p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE,
5961: p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE,
5962: p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
5964: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5965: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
5966: IS
5967: j NUMBER;
5968: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
5969: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
5970: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
5971: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
5972: l_hold_exists VARCHAR2(1) :='N'; --bug 5548778
6218: END Apply_Holds;
6219:
6220:
6221: Procedure Apply_Holds(
6222: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
6223: p_hold_existing_flg IN VARCHAR2,
6224: p_hold_future_flg IN VARCHAR2,
6225: p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', --bug 8477694
6226: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6229: IS
6230: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
6231: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
6232: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
6233: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
6234: l_hold_exists VARCHAR2(1) :='N'; --bug 5548778
6235: l_msg_token VARCHAR2(100); --8477694
6236: /* Commented OLD Processing and added the lines below for WF_HOLS ER (bug 6449458)*/
6237: l_hold_msg_applied BOOLEAN DEFAULT NULL;
6248: from oe_hold_definitions
6249: where hold_id = p_hold_source_rec.hold_id;
6250: Exception
6251: When NO_DATA_FOUND Then
6252: NULL; -- OE_Holds_Pvt.Validate has not yet been called.
6253: End;
6254:
6255: Apply_Holds(
6256: p_hold_source_rec => p_hold_source_rec,
6263: x_msg_count => x_msg_count,
6264: x_msg_data => x_msg_data,
6265: x_is_hold_applied => l_hold_msg_applied);
6266: /*BEGIN
6267: OE_DEBUG_PUB.Add('In OE_Holds_pvt.Apply Holds, Creating Hold Source',1);
6268:
6269: x_return_status := FND_API.G_RET_STS_SUCCESS;
6270: l_hold_source_rec := p_hold_source_rec;
6271: Validate (p_hold_source_rec => p_hold_source_rec,
6319:
6320:
6321: ---------------------
6322: Procedure Release_Holds (
6323: p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
6324: p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
6325: p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
6326: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6327: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
6320:
6321: ---------------------
6322: Procedure Release_Holds (
6323: p_hold_source_rec IN OE_HOLDS_PVT.hold_source_rec_type,
6324: p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type,
6325: p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
6326: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6327: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
6328: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
6326: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
6327: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
6328: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
6329: IS
6330: l_order_rec OE_HOLDS_PVT.order_rec_type;
6331: --bug 5051532
6332: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
6333: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID; --8477694
6334: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID; --8477694
6328: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
6329: IS
6330: l_order_rec OE_HOLDS_PVT.order_rec_type;
6331: --bug 5051532
6332: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
6333: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID; --8477694
6334: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID; --8477694
6335: l_msg_token VARCHAR2(100); --8477694
6336:
6442: p_hold_source_rec => l_hold_source_rec,
6443: x_return_status => x_return_status,
6444: x_msg_count => x_msg_count,
6445: x_msg_data => x_msg_data );
6446: oe_debug_pub.add('After oe_holds_pvt.release_orders with x_return_status' || x_return_status, 1);
6447: --bug 3977747 ends--
6448: else
6449:
6450: if (p_hold_source_rec.hold_source_id is null )then
6469:
6470:
6471: ---------------------
6472: Procedure Release_Holds (
6473: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
6474: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
6475: p_release_reason_code IN OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE,
6476: p_release_comment IN OE_HOLD_RELEASES.RELEASE_COMMENT%TYPE,
6477: p_check_authorization_flag IN VARCHAR2 DEFAULT 'N', -- bug 8477694
6479: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
6480: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
6481: IS
6482: j NUMBER;
6483: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
6484: l_order_rec OE_HOLDS_PVT.order_rec_type;
6485: l_hold_release_rec OE_HOLDS_PVT.hold_release_rec_type;
6486: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID; --8477694
6487: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID; --8477694
6480: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
6481: IS
6482: j NUMBER;
6483: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
6484: l_order_rec OE_HOLDS_PVT.order_rec_type;
6485: l_hold_release_rec OE_HOLDS_PVT.hold_release_rec_type;
6486: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID; --8477694
6487: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID; --8477694
6488: l_msg_token VARCHAR2(100); --8477694
6481: IS
6482: j NUMBER;
6483: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
6484: l_order_rec OE_HOLDS_PVT.order_rec_type;
6485: l_hold_release_rec OE_HOLDS_PVT.hold_release_rec_type;
6486: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID; --8477694
6487: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID; --8477694
6488: l_msg_token VARCHAR2(100); --8477694
6489:
6589: END IF;
6590:
6591: oe_debug_pub.add('l_order_rec.header_id'||l_order_rec.header_id);
6592: oe_debug_pub.add('lhldrlsrec.rel_reas_code' || l_hold_release_rec.release_reason_code);
6593: oe_holds_pvt.release_orders (
6594: p_hold_release_rec => l_hold_release_rec,
6595: p_order_rec => l_order_rec,
6596: p_hold_source_rec => l_hold_source_rec,
6597: x_return_status => x_return_status,
6596: p_hold_source_rec => l_hold_source_rec,
6597: x_return_status => x_return_status,
6598: x_msg_count => x_msg_count,
6599: x_msg_data => x_msg_data );
6600: oe_debug_pub.add('After oe_holds_pvt.release_orders:' ||
6601: x_return_status);
6602: -- If the entity code is order, then release hold source also, if no
6603: -- other order hold records exist for this hold source. This would
6604: -- be the case if selected lines of an order were put on hold.
6697: l_line_rec OE_ORDER_PUB.line_rec_type;
6698: l_api_name CONSTANT VARCHAR2(30) := 'process_apply_holds_lines';
6699: l_line_id NUMBER;
6700: l_return_status VARCHAR2(30);
6701: l_order_tbl OE_HOLDS_PVT.order_tbl_type;
6702: l_error_count NUMBER :=0;
6703: l_ordered_quantity NUMBER ;
6704: j INTEGER;
6705: initial INTEGER;
6705: initial INTEGER;
6706: nextpos INTEGER;
6707:
6708: l_prev_org_id number;
6709: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
6710: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
6711: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
6712: l_msg_token VARCHAR2(100);
6713: /*Added the following variables for WF_HOLDS ER (bug 6449458)*/
6720:
6721: BEGIN
6722: oe_msg_pub.initialize;
6723:
6724: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_apply_holds_lines',1);
6725:
6726: IF check_system_holds(
6727: p_hold_id => p_hold_id,
6728: x_return_status => x_return_status,
6803: from oe_hold_definitions
6804: where hold_id = l_hold_source_rec.hold_id;
6805: END IF;
6806:
6807: OE_DEBUG_PUB.Add('Before calling oe_holds_pvt.apply_holds');
6808: /*Calling overloaded procedure apply_holds, modified for WF_HOLDS ER (bug 6449458)*/
6809: /*oe_holds_pvt.apply_Holds(
6810: p_hold_source_rec => l_hold_source_rec
6811: ,p_hold_existing_flg => 'Y'
6805: END IF;
6806:
6807: OE_DEBUG_PUB.Add('Before calling oe_holds_pvt.apply_holds');
6808: /*Calling overloaded procedure apply_holds, modified for WF_HOLDS ER (bug 6449458)*/
6809: /*oe_holds_pvt.apply_Holds(
6810: p_hold_source_rec => l_hold_source_rec
6811: ,p_hold_existing_flg => 'Y'
6812: ,p_hold_future_flg => 'Y'
6813: ,x_return_status => x_return_status
6812: ,p_hold_future_flg => 'Y'
6813: ,x_return_status => x_return_status
6814: ,x_msg_count => x_msg_count
6815: ,x_msg_data => x_msg_data);*/
6816: oe_holds_pvt.apply_Holds(
6817: p_hold_source_rec => l_hold_source_rec
6818: ,p_hold_existing_flg => 'Y'
6819: ,p_hold_future_flg => 'Y'
6820: ,p_org_id =>p_sel_rec_tbl(j).org_id --ER#7479609
6836: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6837: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6838: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6839:
6840: /*OE_DEBUG_PUB.Add('Before calling OE_Holds_PVT.Apply_Holds');
6841: oe_holds_pvt.apply_Holds(
6842: p_hold_source_rec => l_hold_source_rec
6843: ,p_hold_existing_flg => 'Y'
6844: ,p_hold_future_flg => 'Y'
6837: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6838: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6839:
6840: /*OE_DEBUG_PUB.Add('Before calling OE_Holds_PVT.Apply_Holds');
6841: oe_holds_pvt.apply_Holds(
6842: p_hold_source_rec => l_hold_source_rec
6843: ,p_hold_existing_flg => 'Y'
6844: ,p_hold_future_flg => 'Y'
6845: ,x_return_status => x_return_status
6896: ( p_count => x_msg_count
6897: , p_data => x_msg_data
6898: );
6899:
6900: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_apply_holds_lines',1);
6901:
6902: EXCEPTION /* Procedure exception handler */
6903:
6904: WHEN FND_API.G_EXC_ERROR THEN
6951: --l_old_header_rec OE_ORDER_PUB.Header_Rec_Type;
6952: l_api_name CONSTANT VARCHAR2(30) := 'process_apply_holds_orders';
6953: l_header_id NUMBER;
6954: l_return_status VARCHAR2(30);
6955: l_order_tbl OE_HOLDS_PVT.order_tbl_type;
6956: l_error_count NUMBER :=0;
6957: j INTEGER;
6958:
6959: L_prev_org_id number;
6956: l_error_count NUMBER :=0;
6957: j INTEGER;
6958:
6959: L_prev_org_id number;
6960: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
6961: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
6962: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
6963: l_msg_token VARCHAR2(100);
6964: /*Added the following variables for WF_HOLDS ER (bug 6449458)*/
6970:
6971: BEGIN
6972: oe_msg_pub.initialize;
6973:
6974: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_apply_holds_orders',1);
6975:
6976: IF check_system_holds( p_hold_id => p_hold_id
6977: , x_return_status => x_return_status
6978: , x_msg_count => x_msg_count
7047: from oe_hold_definitions
7048: where hold_id = l_hold_source_rec.hold_id;
7049: END IF;
7050:
7051: OE_DEBUG_PUB.Add('Before calling oe_holds_pvt.apply_holds');
7052: /*oe_holds_pvt.apply_Holds(
7053: p_hold_source_rec => l_hold_source_rec
7054: ,p_hold_existing_flg => 'Y'
7055: ,p_hold_future_flg => 'Y'
7048: where hold_id = l_hold_source_rec.hold_id;
7049: END IF;
7050:
7051: OE_DEBUG_PUB.Add('Before calling oe_holds_pvt.apply_holds');
7052: /*oe_holds_pvt.apply_Holds(
7053: p_hold_source_rec => l_hold_source_rec
7054: ,p_hold_existing_flg => 'Y'
7055: ,p_hold_future_flg => 'Y'
7056: ,x_return_status => x_return_status
7056: ,x_return_status => x_return_status
7057: ,x_msg_count => x_msg_count
7058: ,x_msg_data => x_msg_data);*/
7059:
7060: oe_holds_pvt.apply_Holds(
7061: p_hold_source_rec => l_hold_source_rec
7062: ,p_hold_existing_flg => 'Y'
7063: ,p_hold_future_flg => 'Y'
7064: ,p_org_id =>p_sel_rec_tbl(j).org_id --ER#7479609
7130: ( p_count => x_msg_count
7131: , p_data => x_msg_data
7132: );
7133:
7134: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_apply_holds_orders',1);
7135:
7136: EXCEPTION /* Procedure exception handler */
7137:
7138: WHEN FND_API.G_EXC_ERROR THEN
7192: l_line_rec OE_ORDER_PUB.line_rec_type;
7193: l_api_name CONSTANT VARCHAR2(30) := 'process_release_holds_lines';
7194: l_line_id NUMBER;
7195: l_return_status VARCHAR2(30);
7196: l_order_tbl OE_HOLDS_PVT.order_tbl_type;
7197: l_error_count NUMBER :=0;
7198: l_ordered_quantity NUMBER ;
7199: j INTEGER;
7200: initial INTEGER;
7200: initial INTEGER;
7201: nextpos INTEGER;
7202:
7203: l_prev_org_id number;
7204: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7205: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
7206: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7207: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
7208: l_msg_token VARCHAR2(100);
7201: nextpos INTEGER;
7202:
7203: l_prev_org_id number;
7204: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7205: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
7206: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7207: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
7208: l_msg_token VARCHAR2(100);
7209: BEGIN
7208: l_msg_token VARCHAR2(100);
7209: BEGIN
7210: oe_msg_pub.initialize;
7211:
7212: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_release_holds_lines',1);
7213:
7214: IF check_system_holds( p_hold_id => p_hold_id
7215: ,x_return_status => x_return_status
7216: ,x_msg_count => x_msg_count
7279: l_order_tbl(1).header_id := l_line_rec.header_id;
7280: l_order_tbl(1).line_id := l_line_rec.line_id;
7281:
7282:
7283: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds:HeaderID'
7284: || l_order_tbl(1).header_id || ', LineID:' || l_order_tbl(1).line_id);
7285: oe_holds_pvt.release_holds(
7286: p_order_tbl => l_order_tbl,
7287: p_hold_id => p_hold_id,
7281:
7282:
7283: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds:HeaderID'
7284: || l_order_tbl(1).header_id || ', LineID:' || l_order_tbl(1).line_id);
7285: oe_holds_pvt.release_holds(
7286: p_order_tbl => l_order_tbl,
7287: p_hold_id => p_hold_id,
7288: p_release_reason_code => p_release_reason_code,
7289: p_release_comment => p_release_comment,
7292: x_msg_count => x_msg_count,
7293: x_msg_data => x_msg_data
7294: );
7295:
7296: OE_DEBUG_PUB.Add('After oe_holds_pvt.release_holds:' || x_return_status,3);
7297:
7298:
7299: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7300: oe_debug_pub.add('process_release_holds_lines unexpected failure',3);
7312: ( p_count => x_msg_count
7313: , p_data => x_msg_data
7314: );
7315:
7316: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_release_holds_lines',1);
7317:
7318: EXCEPTION /* Procedure exception handler */
7319:
7320: WHEN FND_API.G_EXC_ERROR THEN
7367: l_line_rec OE_ORDER_PUB.line_rec_type;
7368: l_api_name CONSTANT VARCHAR2(30) := 'process_release_holds_orders';
7369: l_header_id NUMBER;
7370: l_return_status VARCHAR2(30);
7371: l_order_tbl OE_HOLDS_PVT.order_tbl_type;
7372: l_error_count NUMBER :=0;
7373: j INTEGER;
7374: initial INTEGER;
7375: nextpos INTEGER;
7373: j INTEGER;
7374: initial INTEGER;
7375: nextpos INTEGER;
7376: L_prev_org_id number; -- MOAC
7377: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7378: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
7379: --l_record_ids varchar2(2000) := p_record_ids || ',';
7380: --l_num_of_records number;
7381:
7374: initial INTEGER;
7375: nextpos INTEGER;
7376: L_prev_org_id number; -- MOAC
7377: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7378: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
7379: --l_record_ids varchar2(2000) := p_record_ids || ',';
7380: --l_num_of_records number;
7381:
7382: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7384: l_msg_token VARCHAR2(100);
7385: BEGIN
7386: oe_msg_pub.initialize;
7387:
7388: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_release_holds_orders',1);
7389:
7390: IF check_system_holds( p_hold_id => p_hold_id
7391: ,x_return_status => x_return_status
7392: ,x_msg_count => x_msg_count
7448: l_prev_org_id := p_sel_rec_tbl(j).org_id;
7449: END IF;
7450: l_order_tbl(1).header_id := p_sel_rec_tbl(j).Id1;
7451:
7452: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds 4 HeaderID' || l_order_tbl(1).header_id);
7453: oe_holds_pvt.release_holds(
7454: p_order_tbl => l_order_tbl,
7455: p_hold_id => p_hold_id,
7456: p_release_reason_code => p_release_reason_code,
7449: END IF;
7450: l_order_tbl(1).header_id := p_sel_rec_tbl(j).Id1;
7451:
7452: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds 4 HeaderID' || l_order_tbl(1).header_id);
7453: oe_holds_pvt.release_holds(
7454: p_order_tbl => l_order_tbl,
7455: p_hold_id => p_hold_id,
7456: p_release_reason_code => p_release_reason_code,
7457: p_release_comment => p_release_comment,
7482: ( p_count => x_msg_count
7483: , p_data => x_msg_data
7484: );
7485:
7486: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_release_holds_orders',1);
7487:
7488: EXCEPTION /* Procedure exception handler */
7489:
7490: WHEN FND_API.G_EXC_ERROR THEN
7527: /* This procedure gets called when Create Holds Source is selected
7528: from the special menu.
7529: */
7530: procedure process_create_source(
7531: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
7532: ,p_hold_existing_flg IN varchar2
7533: ,p_hold_future_flg IN varchar2
7534: ,p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id --ER#7479609
7535: ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7536: ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
7537: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7538: )
7539: IS
7540: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7541: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
7542: l_hold_release_rec OE_HOLDS_PVT.hold_release_rec_type;
7543: l_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
7544: l_api_name CONSTANT VARCHAR2(30) := 'process_create_holds';
7538: )
7539: IS
7540: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7541: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
7542: l_hold_release_rec OE_HOLDS_PVT.hold_release_rec_type;
7543: l_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
7544: l_api_name CONSTANT VARCHAR2(30) := 'process_create_holds';
7545: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7546: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
7632: from oe_hold_definitions
7633: where hold_id = l_hold_source_rec.hold_id;
7634:
7635: if (p_hold_existing_flg = 'Y' AND p_hold_future_flg = 'Y') then
7636: oe_debug_pub.add('Calling oe_holds_pvt.apply_Holds',3);
7637: /*oe_holds_pvt.apply_Holds(
7638: p_hold_source_rec => l_hold_source_rec
7639: ,x_return_status => x_return_status
7640: ,x_msg_count => x_msg_count
7633: where hold_id = l_hold_source_rec.hold_id;
7634:
7635: if (p_hold_existing_flg = 'Y' AND p_hold_future_flg = 'Y') then
7636: oe_debug_pub.add('Calling oe_holds_pvt.apply_Holds',3);
7637: /*oe_holds_pvt.apply_Holds(
7638: p_hold_source_rec => l_hold_source_rec
7639: ,x_return_status => x_return_status
7640: ,x_msg_count => x_msg_count
7641: ,x_msg_data => x_msg_data
7640: ,x_msg_count => x_msg_count
7641: ,x_msg_data => x_msg_data
7642: );*/
7643: /*Calling Overloaded Apply_hold Procedure for WF_HOLDS ER (bug 6449458)*/
7644: oe_holds_pvt.apply_Holds(
7645: p_hold_source_rec => l_hold_source_rec
7646: ,p_hold_existing_flg => 'Y'
7647: ,p_hold_future_flg => 'Y'
7648: ,p_org_id => p_org_id --ER#7479609
7699: l_hold_release_rec.hold_source_id := l_hold_source_id;
7700: l_hold_release_rec.release_reason_code := 'EXPIRE';
7701: l_hold_release_rec.release_comment := 'Released automatically by System';
7702: oe_debug_pub.add('Calling Create_Release_Source',3);
7703: OE_HOLDS_PVT.Create_Release_Source(p_hold_release_rec => l_hold_release_rec,
7704: x_hold_release_id => l_hold_release_id,
7705: x_return_status => x_return_status,
7706: x_msg_count => x_msg_count,
7707: x_msg_data => x_msg_data);
7779: from the Release Source window.
7780: */
7781: procedure process_release_source(
7782: p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
7783: ,p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type
7784: ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7785: ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
7786: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7787: )
7785: ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
7786: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7787: )
7788: IS
7789: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7790: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
7791: l_api_name CONSTANT VARCHAR2(30) := 'process_release_source';
7792: l_hold_id OE_HOLD_DEFINITIONS.HOLD_ID%TYPE;
7793: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7786: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7787: )
7788: IS
7789: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7790: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
7791: l_api_name CONSTANT VARCHAR2(30) := 'process_release_source';
7792: l_hold_id OE_HOLD_DEFINITIONS.HOLD_ID%TYPE;
7793: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7794: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
7874: l_hold_release_rec := p_hold_release_rec;
7875: --l_hold_release_rec.RELEASE_REASON_CODE := p_release_reason_code;
7876: --l_hold_release_rec.RELEASE_COMMENT := p_release_comment;
7877:
7878: oe_holds_pvt.Release_Holds(
7879: p_hold_source_rec => l_hold_source_rec
7880: ,p_hold_release_rec => l_hold_release_rec
7881: ,p_check_authorization_flag => 'N' -- bug 8477694
7882: ,x_return_status => x_return_status
7975: l_line_rec OE_ORDER_PUB.line_rec_type;
7976: l_api_name CONSTANT VARCHAR2(30) := 'process_release_holds_lines';
7977: l_line_id NUMBER;
7978: l_return_status VARCHAR2(30);
7979: l_order_tbl OE_HOLDS_PVT.order_tbl_type;
7980: l_error_count NUMBER :=0;
7981: l_ordered_quantity NUMBER ;
7982: j INTEGER;
7983: initial INTEGER;
7983: initial INTEGER;
7984: nextpos INTEGER;
7985:
7986: l_prev_org_id number;
7987: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7988: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
7989: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7990: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
7991: l_msg_token VARCHAR2(100);
7984: nextpos INTEGER;
7985:
7986: l_prev_org_id number;
7987: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7988: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
7989: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7990: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
7991: l_msg_token VARCHAR2(100);
7992: BEGIN
7991: l_msg_token VARCHAR2(100);
7992: BEGIN
7993: oe_msg_pub.initialize;
7994:
7995: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_release_holds_lines',1);
7996:
7997: IF check_system_holds( p_hold_id => p_hold_id
7998: ,x_return_status => x_return_status
7999: ,x_msg_count => x_msg_count
8061: l_order_tbl(1).header_id := l_line_rec.header_id;
8062: l_order_tbl(1).line_id := l_line_rec.line_id;
8063:
8064:
8065: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds:HeaderID'
8066: || l_order_tbl(1).header_id || ', LineID:' || l_order_tbl(1).line_id);
8067: oe_holds_pvt.release_holds(
8068: p_order_tbl => l_order_tbl,
8069: p_hold_id => p_hold_id,
8063:
8064:
8065: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds:HeaderID'
8066: || l_order_tbl(1).header_id || ', LineID:' || l_order_tbl(1).line_id);
8067: oe_holds_pvt.release_holds(
8068: p_order_tbl => l_order_tbl,
8069: p_hold_id => p_hold_id,
8070: p_release_reason_code => p_release_reason_code,
8071: p_release_comment => p_release_comment,
8074: x_msg_count => x_msg_count,
8075: x_msg_data => x_msg_data
8076: );
8077:
8078: OE_DEBUG_PUB.Add('After oe_holds_pvt.release_holds:' || x_return_status,3);
8079:
8080: -- Changes begin : ER 1373910
8081: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
8082:
8080: -- Changes begin : ER 1373910
8081: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
8082:
8083: IF p_wf_release_action = 'Y' THEN
8084: OE_DEBUG_PUB.Add('Before calling Oe_holds_pvt.progress_order');
8085: progress_order( p_hold_id,
8086: p_num_of_records,
8087: l_order_tbl,
8088: x_return_status,
8087: l_order_tbl,
8088: x_return_status,
8089: x_msg_count,
8090: x_msg_data );
8091: OE_DEBUG_PUB.Add('After Oe_holds_pvt.progress_order : ' || x_return_status,3);
8092: END IF;
8093: END IF;
8094: -- Changes End : ER 1373910
8095:
8109: ( p_count => x_msg_count
8110: , p_data => x_msg_data
8111: );
8112:
8113: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_release_holds_lines',1);
8114:
8115: EXCEPTION /* Procedure exception handler */
8116:
8117: WHEN FND_API.G_EXC_ERROR THEN
8183: l_line_rec OE_ORDER_PUB.line_rec_type;
8184: l_api_name CONSTANT VARCHAR2(30) := 'process_release_holds_orders';
8185: l_header_id NUMBER;
8186: l_return_status VARCHAR2(30);
8187: l_order_tbl OE_HOLDS_PVT.order_tbl_type;
8188: l_error_count NUMBER :=0;
8189: j INTEGER;
8190: initial INTEGER;
8191: nextpos INTEGER;
8189: j INTEGER;
8190: initial INTEGER;
8191: nextpos INTEGER;
8192: l_prev_org_id number; -- MOAC
8193: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
8194: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
8195: --l_record_ids varchar2(2000) := p_record_ids || ',';
8196: --l_num_of_records number;
8197:
8190: initial INTEGER;
8191: nextpos INTEGER;
8192: l_prev_org_id number; -- MOAC
8193: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
8194: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
8195: --l_record_ids varchar2(2000) := p_record_ids || ',';
8196: --l_num_of_records number;
8197:
8198: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
8200: l_msg_token VARCHAR2(100);
8201: BEGIN
8202: oe_msg_pub.initialize;
8203:
8204: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_release_holds_orders',1);
8205:
8206: IF check_system_holds( p_hold_id => p_hold_id
8207: ,x_return_status => x_return_status
8208: ,x_msg_count => x_msg_count
8263: l_prev_org_id := p_sel_rec_tbl(j).org_id;
8264: END IF;
8265: l_order_tbl(1).header_id := p_sel_rec_tbl(j).Id1;
8266:
8267: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds 4 HeaderID' || l_order_tbl(1).header_id);
8268: oe_holds_pvt.release_holds(
8269: p_order_tbl => l_order_tbl,
8270: p_hold_id => p_hold_id,
8271: p_release_reason_code => p_release_reason_code,
8264: END IF;
8265: l_order_tbl(1).header_id := p_sel_rec_tbl(j).Id1;
8266:
8267: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds 4 HeaderID' || l_order_tbl(1).header_id);
8268: oe_holds_pvt.release_holds(
8269: p_order_tbl => l_order_tbl,
8270: p_hold_id => p_hold_id,
8271: p_release_reason_code => p_release_reason_code,
8272: p_release_comment => p_release_comment,
8282:
8283: -- Changes begin : ER 1373910
8284: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
8285: IF p_wf_release_action = 'Y' THEN
8286: OE_DEBUG_PUB.Add('Before calling Oe_holds_pvt.progress_order');
8287: progress_order( p_hold_id,
8288: p_num_of_records,
8289: l_order_tbl,
8290: x_return_status,
8289: l_order_tbl,
8290: x_return_status,
8291: x_msg_count,
8292: x_msg_data);
8293: OE_DEBUG_PUB.Add('After Oe_holds_pvt.progress_order : ' || x_return_status,3);
8294: END IF;
8295: END IF;
8296: -- Changes end : ER 1373910
8297:
8312: ( p_count => x_msg_count
8313: , p_data => x_msg_data
8314: );
8315:
8316: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_release_holds_orders',1);
8317:
8318: EXCEPTION /* Procedure exception handler */
8319:
8320: WHEN FND_API.G_EXC_ERROR THEN
8371: */
8372:
8373: procedure process_release_source(
8374: p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
8375: ,p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type
8376: ,p_wf_release_action IN VARCHAR2
8377: ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
8378: ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
8379: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
8378: ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
8379: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
8380: )
8381: IS
8382: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
8383: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
8384: l_api_name CONSTANT VARCHAR2(30) := 'process_release_source';
8385: l_hold_id OE_HOLD_DEFINITIONS.HOLD_ID%TYPE;
8386: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
8379: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
8380: )
8381: IS
8382: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
8383: l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
8384: l_api_name CONSTANT VARCHAR2(30) := 'process_release_source';
8385: l_hold_id OE_HOLD_DEFINITIONS.HOLD_ID%TYPE;
8386: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
8387: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
8468: l_hold_release_rec := p_hold_release_rec;
8469: --l_hold_release_rec.RELEASE_REASON_CODE := p_release_reason_code;
8470: --l_hold_release_rec.RELEASE_COMMENT := p_release_comment;
8471:
8472: oe_holds_pvt.Release_Holds(
8473: p_hold_source_rec => l_hold_source_rec
8474: ,p_hold_release_rec => l_hold_release_rec
8475: ,p_check_authorization_flag => 'N' -- bug 8477694
8476: ,x_return_status => x_return_status
8483:
8484: -- Changes begin : ER 1373910
8485: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
8486: IF p_wf_release_action = 'Y' THEN
8487: Oe_debug_pub.Add('Before calling Oe_holds_pvt.progress_order');
8488: progress_order( p_hold_source_id,
8489: x_return_status,
8490: x_msg_count,
8491: x_msg_data);
8488: progress_order( p_hold_source_id,
8489: x_return_status,
8490: x_msg_count,
8491: x_msg_data);
8492: Oe_debug_pub.ADD('Oe_holds_pvt.progress order returned with : ' || x_return_status);
8493: END IF;
8494: END IF;
8495: -- Changes end : ER 1373910
8496:
8563: l_api_name CONSTANT VARCHAR2(30) := 'SPLIT_HOLD';
8564:
8565: l_line_number NUMBER;
8566: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
8567: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
8568:
8569: CURSOR c_order_holds IS
8570: SELECT oh.HOLD_SOURCE_ID
8571: , oh.HEADER_ID
8599:
8600: BEGIN
8601: x_return_status := FND_API.G_RET_STS_SUCCESS;
8602:
8603: l_user_id := OE_HOLDS_PVT.get_user_id;
8604: l_org_id := OE_GLOBALS.G_ORG_ID;
8605: if l_org_id IS NULL THEN
8606: OE_GLOBALS.Set_Context;
8607: l_org_id := OE_GLOBALS.G_ORG_ID;
8633: l_hold_source_rec.ATTRIBUTE14 := c_rec.ATTRIBUTE14;
8634: l_hold_source_rec.ATTRIBUTE15 := c_rec.ATTRIBUTE15;
8635: l_hold_source_rec.org_id := c_rec.org_id;
8636:
8637: oe_holds_pvt.Create_Hold_Source (
8638: p_hold_source_rec => l_hold_source_rec,
8639: p_org_id =>l_org_id, --ER#7479609
8640: x_hold_source_id => l_hold_source_id,
8641: x_return_status => x_return_status,
8708: END split_hold;
8709:
8710: /*Added Overloaded procedure apply_holds for WF_HOLDS ER (bug 6449458)*/
8711: Procedure Apply_Holds(
8712: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
8713: p_hold_existing_flg IN VARCHAR2,
8714: p_hold_future_flg IN VARCHAR2,
8715: p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
8716: p_wf_item_type IN VARCHAR2 DEFAULT NULL,
8727: FND_GLOBAL.RESP_APPL_ID;
8728: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE
8729: := FND_GLOBAL.RESP_ID;
8730: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
8731: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
8732: l_hold_exists VARCHAR2(1) :='N'; --bug 5548778
8733: l_msg_token VARCHAR2(100); --8477694
8734:
8735: l_is_hold_applied BOOLEAN;
8735: l_is_hold_applied BOOLEAN;
8736:
8737: BEGIN
8738:
8739: OE_DEBUG_PUB.Add('In OE_Holds_pvt.Apply Holds, Creating Hold Source',1);
8740:
8741: x_return_status := FND_API.G_RET_STS_SUCCESS;
8742:
8743: -- 8477694
8883: END Apply_Holds;
8884:
8885: /*Added New Overloaded Procedure Create_Order_hols for WF_HOLDS ER (bug 6449458)*/
8886: Procedure Create_Order_Holds(
8887: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
8888: p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
8889: p_item_type IN VARCHAR2,
8890: p_activity_name IN VARCHAR2,
8891: x_return_status OUT NOCOPY VARCHAR2,
9022: l_credithold_cust VARCHAR2(10) := NVL(OE_SYS_PARAMETERS.value('ONT_CREDITHOLD_TYPE'),'S') ;--ER# 3667551
9023: l_master_orgid VARCHAR2(10) := oe_sys_parameters.Value('MASTER_ORGANIZATION_ID'); --13653352
9024: BEGIN
9025: x_return_status := FND_API.G_RET_STS_SUCCESS;
9026: l_user_id := OE_HOLDS_PVT.get_user_id;
9027: l_org_id := MO_GLOBAL.get_current_org_id;
9028: x_is_hold_applied := NULL;
9029: IF l_org_id IS NULL THEN
9030: -- org_id is null, raise an error.
14967: -----------------------------------------------
14968:
14969: END IF;
14970: -- 16053361
14971: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Applying hold');
14972: If NVL (Fnd_Profile.Value('ONT_RAISE_STATUS_CHANGE_BUSINESS_EVENT'), 'N')='Y'
14973: THEN
14974: IF l_debug_level > 0 THEN
14975: oe_debug_pub.add( 'OE_HOLDS_PVT -Preparing paramters to pass to Integration event for Applying hold');
14971: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Applying hold');
14972: If NVL (Fnd_Profile.Value('ONT_RAISE_STATUS_CHANGE_BUSINESS_EVENT'), 'N')='Y'
14973: THEN
14974: IF l_debug_level > 0 THEN
14975: oe_debug_pub.add( 'OE_HOLDS_PVT -Preparing paramters to pass to Integration event for Applying hold');
14976: END IF;
14977: l_count := 1;
14978: l_nameVal_tbl(l_count).name := 'CHANGE_TYPE';
14979: l_nameVal_tbl(l_count).value := 'APPLY';
15007: p_line_id => p_hold_source_rec.line_id,
15008: p_event_name => 'oracle.apps.ont.hold.action',
15009: p_nameVal_tbl => l_nameVal_tbl);
15010: IF l_debug_level > 0 THEN
15011: oe_debug_pub.add( 'OE_HOLDS_PVT - Done with Raising Integration event for Applying hold');
15012: END IF;
15013: END IF;
15014: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Applying hold');
15015: -- 16053361
15010: IF l_debug_level > 0 THEN
15011: oe_debug_pub.add( 'OE_HOLDS_PVT - Done with Raising Integration event for Applying hold');
15012: END IF;
15013: END IF;
15014: oe_debug_pub.add( 'OE_HOLDS_PVT - Raise Integration event for Applying hold');
15015: -- 16053361
15016:
15017:
15018: -- GENESIS --
15018: -- GENESIS --
15019: IF p_hold_source_rec.line_id IS NOT NULL THEN
15020:
15021: IF l_debug_level > 0 THEN
15022: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.line_id IS NOT NULL');
15023: END IF;
15024:
15025: OPEN check_line_hold_type_cur(p_hold_source_rec.line_id);
15026: FETCH check_line_hold_type_cur INTO l_check_hold;
15025: OPEN check_line_hold_type_cur(p_hold_source_rec.line_id);
15026: FETCH check_line_hold_type_cur INTO l_check_hold;
15027: CLOSE check_line_hold_type_cur;
15028: IF l_debug_level > 0 THEN
15029: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold' || l_check_hold);
15030: END IF;
15031:
15032: ELSIF p_hold_source_rec.line_id IS NULL AND
15033: p_hold_source_rec.header_id IS NOT NULL THEN
15031:
15032: ELSIF p_hold_source_rec.line_id IS NULL AND
15033: p_hold_source_rec.header_id IS NOT NULL THEN
15034: IF l_debug_level > 0 THEN
15035: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.header_id IS NOT NULL');
15036: END IF;
15037: OPEN check_hdr_hold_type_cur(p_hold_source_rec.header_id);
15038: FETCH check_hdr_hold_type_cur INTO l_check_hold;
15039: CLOSE check_hdr_hold_type_cur;
15037: OPEN check_hdr_hold_type_cur(p_hold_source_rec.header_id);
15038: FETCH check_hdr_hold_type_cur INTO l_check_hold;
15039: CLOSE check_hdr_hold_type_cur;
15040: IF l_debug_level > 0 THEN
15041: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold' || l_check_hold);
15042: END IF;
15043:
15044: ELSIF p_hold_source_rec.line_id IS NULL AND
15045: p_hold_source_rec.header_id IS NULL AND
15044: ELSIF p_hold_source_rec.line_id IS NULL AND
15045: p_hold_source_rec.header_id IS NULL AND
15046: p_hold_source_rec.hold_source_id IS NOT NULL THEN
15047: IF l_debug_level > 0 THEN
15048: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.hold_source_id IS NOT NULL');
15049: END IF;
15050: OPEN check_src_hold_type_cur(p_hold_source_rec.hold_source_id);
15051: FETCH check_src_hold_type_cur INTO l_check_hold;
15052: CLOSE check_src_hold_type_cur;
15051: FETCH check_src_hold_type_cur INTO l_check_hold;
15052: CLOSE check_src_hold_type_cur;
15053:
15054: IF l_debug_level > 0 THEN
15055: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold :' || l_check_hold);
15056: END IF;
15057:
15058: END IF;
15059:
15057:
15058: END IF;
15059:
15060: IF l_debug_level > 0 THEN
15061: oe_debug_pub.add( 'OE_HOLDS_PVT - CREATE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
15062: END IF;
15063:
15064: IF NVL(l_check_hold, 'N') = 'Y' THEN
15065: IF l_debug_level > 0 THEN
15062: END IF;
15063:
15064: IF NVL(l_check_hold, 'N') = 'Y' THEN
15065: IF l_debug_level > 0 THEN
15066: oe_debug_pub.add( 'OE_HOLDS_PVT - CREATE ORDER HOLDS - l_check_hold: ' || l_check_hold);
15067: END IF;
15068:
15069: IF p_hold_source_rec.hold_entity_code = 'O' THEN
15070: IF l_debug_level > 0 THEN
15155:
15156: END Create_Order_Holds;
15157:
15158:
15159: END OE_HOLDS_PVT;
15160: