DBA Data[Home] [Help]

APPS.OE_HOLDS_PVT dependencies on OE_HOLD_SOURCES

Line 25: p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE

21: /*
22: *
23: */
24: function entity_code_value (
25: p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE
26: )
27: return VARCHAR2
28: IS
29: l_hold_entity_code_value VARCHAR2 (100) := NULL;

Line 65: p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE,

61: END get_user_id;
62:
63:
64: function entity_id_value (
65: p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE,
66: p_hold_entity_id IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID%TYPE )
67: return VARCHAR2
68: IS
69: l_hold_entity_id_value VARCHAR2 (100) := NULL;

Line 66: p_hold_entity_id IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID%TYPE )

62:
63:
64: function entity_id_value (
65: p_hold_entity_code IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE,
66: p_hold_entity_id IN OE_HOLD_SOURCES_ALL.HOLD_ENTITY_ID%TYPE )
67: return VARCHAR2
68: IS
69: l_hold_entity_id_value VARCHAR2 (100) := NULL;
70:

Line 298: p_hold_source_id IN OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE)

294:
295: END entity_id_value;
296: --------------------------
297: function hold_name(
298: p_hold_source_id IN OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE)
299: return varchar2
300: IS
301: l_hold_name oe_hold_definitions.name%type := '';
302: BEGIN

Line 305: from oe_hold_sources hs,

301: l_hold_name oe_hold_definitions.name%type := '';
302: BEGIN
303: select hd.name
304: into l_hold_name
305: from oe_hold_sources hs,
306: oe_hold_definitions hd
307: where hs.HOLD_SOURCE_ID = p_hold_source_id
308: and hs.hold_id = hd.hold_id;
309:

Line 413: oe_hold_sources hs,

409: BEGIN
410: SELECT 'Y'
411: INTO l_release_children
412: FROM oe_order_holds oh,
413: oe_hold_sources hs,
414: oe_hold_definitions hd
415: WHERE hs.hold_source_id = oh.hold_source_id
416: AND hs.hold_id = hd.hold_id
417: AND hd.hold_id = p_hold_id

Line 578: FROM oe_order_holds oh, oe_hold_sources hs

574: CURSOR released_orders_lines
575: IS
576: SELECT NVL(line_id, header_id) entity_id,
577: DECODE(line_id , NULL, 'OEOH', 'OEOL') entity_type
578: FROM oe_order_holds oh, oe_hold_sources hs
579: WHERE hs.hold_source_id = p_hold_source_id
580: AND oh.hold_release_id = hs.hold_release_id
581: AND oh.released_flag = 'Y';
582:

Line 592: FROM oe_hold_sources

588:
589: BEGIN
590: SELECT hold_entity_code
591: INTO l_hold_entity_code
592: FROM oe_hold_sources
593: WHERE hold_source_id = p_hold_source_id;
594: EXCEPTION
595: WHEN OTHERS THEN
596: Oe_debug_pub.ADD('Source not found.!!');

Line 765: Procedure InsertTable_OOH_Header (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type

761: -- p_activity_name : Workflow activity name
762: -- p_activity_status : Workflow activity status
763: -- p_additional_where_clause : Its derived from the p_item_type and p_activity_name
764: ---------------------------------------------------------------------------------------------------
765: Procedure InsertTable_OOH_Header (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type
766: ,p_header_id OE_ORDER_HEADERS_ALL.header_id%type
767: ,p_org_id OE_ORDER_HEADERS_ALL.org_id%type
768: ,p_hold_entity_where_clause VARCHAR2
769: ,p_item_type VARCHAR2

Line 1030: Procedure InsertTable_OOH_Line (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type,

1026: -- p_activity_name : Workflow activity name
1027: -- p_activity_status : Workflow activity status
1028: -- p_additional_where_clause : Its derived from the p_item_type and p_activity_name
1029: ---------------------------------------------------------------------------------------------------
1030: Procedure InsertTable_OOH_Line (p_hold_source_id OE_HOLD_SOURCES_ALL.hold_source_id%type,
1031: p_line_id OE_ORDER_LINES_ALL.line_id%type,
1032: p_org_id OE_ORDER_HEADERS_ALL.org_id%type,
1033: p_hold_entity_where_clause VARCHAR2,
1034: p_item_type VARCHAR2,

Line 1548: FROM OE_HOLD_SOURCES HS,

1544: SELECT HS.HOLD_SOURCE_ID,
1545: HS.HOLD_ENTITY_CODE,
1546: hs.hold_id,
1547: oh.order_hold_id
1548: FROM OE_HOLD_SOURCES HS,
1549: OE_ORDER_HOLDS oh
1550: WHERE HS.HOLD_ID = nvl(p_hold_source_rec.hold_id, HS.HOLD_ID)
1551: and hs.hold_entity_code = nvl(p_hold_source_rec.hold_entity_code, hs.hold_entity_code)
1552: and hs.hold_entity_id = nvl(p_hold_source_rec.hold_entity_id, hs.hold_entity_id)

Line 1564: oe_hold_sources ohs,

1560: SELECT 'Y'
1561: FROM DUAL
1562: WHERE EXISTS (SELECT NULL
1563: FROM oe_order_holds ooh,
1564: oe_hold_sources ohs,
1565: oe_hold_definitions ohd,
1566: oe_order_headers_all h,
1567: oe_order_sources oos
1568: WHERE ooh.header_id = h.header_id

Line 1580: l_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;

1576: -- GENESIS --
1577:
1578: l_user_id NUMBER;
1579: x_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
1580: l_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;
1581: x_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;
1582: l_hold_entity_code OE_HOLD_SOURCES.HOLD_ENTITY_CODE%TYPE;
1583: l_order_hold_id OE_ORDER_HOLDS.ORDER_HOLD_ID%TYPE;
1584: l_hold_id OE_HOLD_DEFINITIONS.hold_id%type;

Line 1581: x_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;

1577:
1578: l_user_id NUMBER;
1579: x_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
1580: l_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;
1581: x_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;
1582: l_hold_entity_code OE_HOLD_SOURCES.HOLD_ENTITY_CODE%TYPE;
1583: l_order_hold_id OE_ORDER_HOLDS.ORDER_HOLD_ID%TYPE;
1584: l_hold_id OE_HOLD_DEFINITIONS.hold_id%type;
1585:

Line 1582: l_hold_entity_code OE_HOLD_SOURCES.HOLD_ENTITY_CODE%TYPE;

1578: l_user_id NUMBER;
1579: x_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
1580: l_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;
1581: x_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;
1582: l_hold_entity_code OE_HOLD_SOURCES.HOLD_ENTITY_CODE%TYPE;
1583: l_order_hold_id OE_ORDER_HOLDS.ORDER_HOLD_ID%TYPE;
1584: l_hold_id OE_HOLD_DEFINITIONS.hold_id%type;
1585:
1586: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;

Line 2303: x_hold_source_id OUT NOCOPY /* file.sql.39 change */ OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE,

2299: ---Overload procedure for bug5548778
2300: Procedure Create_Hold_Source (
2301: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
2302: p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
2303: x_hold_source_id OUT NOCOPY /* file.sql.39 change */ OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE,
2304: x_hold_exists OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2305: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2306: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2307: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)

Line 2313: --l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;

2309: l_api_name CONSTANT VARCHAR2(30) := 'CREATE_HOLD_SOURCE';
2310: l_user_id NUMBER;
2311: l_org_id NUMBER;
2312: l_count NUMBER;
2313: --l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
2314: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
2315: BEGIN
2316: x_return_status := FND_API.G_RET_STS_SUCCESS;
2317: x_hold_exists := 'N';

Line 2335: --ER#7479609 FROM OE_HOLD_SOURCES HS

2331: /* If Line-level hold */
2332: IF p_hold_source_rec.line_id is not null THEN
2333: select count(*)
2334: into l_count
2335: --ER#7479609 FROM OE_HOLD_SOURCES HS
2336: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2337: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2338: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2339: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id

Line 2336: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609

2332: IF p_hold_source_rec.line_id is not null THEN
2333: select count(*)
2334: into l_count
2335: --ER#7479609 FROM OE_HOLD_SOURCES HS
2336: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2337: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2338: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2339: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
2340: AND HS.HOLD_ENTITY_CODE2 is null

Line 2366: --ER#7479609 FROM OE_HOLD_SOURCES HS

2362: /* If Order Level Hold */
2363: ELSE
2364: select count(*)
2365: into l_count
2366: --ER#7479609 FROM OE_HOLD_SOURCES HS
2367: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2368: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2369: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2370: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id

Line 2367: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609

2363: ELSE
2364: select count(*)
2365: into l_count
2366: --ER#7479609 FROM OE_HOLD_SOURCES HS
2367: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2368: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2369: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2370: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
2371: AND HS.HOLD_ENTITY_CODE2 is null

Line 2398: --ER#7479609 FROM OE_HOLD_SOURCES HS

2394: END IF; /*If Order Level Hold */
2395: else
2396: select count(*)
2397: into l_count
2398: --ER#7479609 FROM OE_HOLD_SOURCES HS
2399: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2400: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2401: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2402: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id

Line 2399: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609

2395: else
2396: select count(*)
2397: into l_count
2398: --ER#7479609 FROM OE_HOLD_SOURCES HS
2399: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2400: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2401: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2402: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
2403: AND HS.org_id= p_org_id --ER#7479609

Line 2425: SELECT OE_HOLD_SOURCES_S.NEXTVAL

2421:
2422:
2423: -- Inserting a NEW HOLD SOURCE record
2424:
2425: SELECT OE_HOLD_SOURCES_S.NEXTVAL
2426: INTO x_hold_source_id
2427: FROM DUAL;
2428:
2429: INSERT INTO OE_HOLD_SOURCES_ALL

Line 2429: INSERT INTO OE_HOLD_SOURCES_ALL

2425: SELECT OE_HOLD_SOURCES_S.NEXTVAL
2426: INTO x_hold_source_id
2427: FROM DUAL;
2428:
2429: INSERT INTO OE_HOLD_SOURCES_ALL
2430: ( HOLD_SOURCE_ID
2431: , LAST_UPDATE_DATE
2432: , LAST_UPDATED_BY
2433: , CREATION_DATE

Line 2546: x_hold_source_id OUT NOCOPY /* file.sql.39 change */ OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE,

2542: ------
2543: Procedure Create_Hold_Source (
2544: p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type,
2545: p_org_id IN NUMBER DEFAULT MO_GLOBAL.get_current_org_id, --ER#7479609
2546: x_hold_source_id OUT NOCOPY /* file.sql.39 change */ OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE,
2547: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2548: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2549: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2550: IS

Line 2555: --l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;

2551: l_api_name CONSTANT VARCHAR2(30) := 'CREATE_HOLD_SOURCE';
2552: l_user_id NUMBER;
2553: l_org_id NUMBER;
2554: l_count NUMBER;
2555: --l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
2556: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
2557: l_hold_comment OE_HOLD_SOURCES_ALL.HOLD_COMMENT%TYPE; --ER#7479609
2558: BEGIN
2559: x_return_status := FND_API.G_RET_STS_SUCCESS;

Line 2557: l_hold_comment OE_HOLD_SOURCES_ALL.HOLD_COMMENT%TYPE; --ER#7479609

2553: l_org_id NUMBER;
2554: l_count NUMBER;
2555: --l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
2556: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
2557: l_hold_comment OE_HOLD_SOURCES_ALL.HOLD_COMMENT%TYPE; --ER#7479609
2558: BEGIN
2559: x_return_status := FND_API.G_RET_STS_SUCCESS;
2560: l_user_id := OE_HOLDS_PVT.get_user_id;
2561: l_org_id := MO_GLOBAL.get_current_org_id;

Line 2577: --ER#7479609 FROM OE_HOLD_SOURCES HS

2573: /* If Line-level hold */
2574: IF p_hold_source_rec.line_id is not null THEN
2575: select count(*)
2576: into l_count
2577: --ER#7479609 FROM OE_HOLD_SOURCES HS
2578: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2579: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2580: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2581: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id

Line 2578: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609

2574: IF p_hold_source_rec.line_id is not null THEN
2575: select count(*)
2576: into l_count
2577: --ER#7479609 FROM OE_HOLD_SOURCES HS
2578: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2579: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2580: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2581: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
2582: AND HS.HOLD_ENTITY_CODE2 is null

Line 2606: --ER#7479609 FROM OE_HOLD_SOURCES HS

2602: /* If Order Level Hold */
2603: ELSE
2604: select count(*)
2605: into l_count
2606: --ER#7479609 FROM OE_HOLD_SOURCES HS
2607: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2608: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2609: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2610: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id

Line 2607: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609

2603: ELSE
2604: select count(*)
2605: into l_count
2606: --ER#7479609 FROM OE_HOLD_SOURCES HS
2607: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2608: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2609: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2610: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
2611: AND HS.HOLD_ENTITY_CODE2 is null

Line 2636: --ER#7479609 FROM OE_HOLD_SOURCES HS

2632: END IF; /*If Order Level Hold */
2633: else
2634: select count(*)
2635: into l_count
2636: --ER#7479609 FROM OE_HOLD_SOURCES HS
2637: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2638: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2639: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2640: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id

Line 2637: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609

2633: else
2634: select count(*)
2635: into l_count
2636: --ER#7479609 FROM OE_HOLD_SOURCES HS
2637: FROM OE_HOLD_SOURCES_ALL HS --ER#7479609
2638: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2639: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2640: AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
2641: AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =

Line 2672: -- FROM OE_HOLD_SOURCES HS

2668:
2669: -- BEGIN
2670: -- SELECT HOLD_SOURCE_ID
2671: -- INTO l_hold_source_id
2672: -- FROM OE_HOLD_SOURCES HS
2673: -- WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
2674: -- AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code
2675: -- AND HS.HOLD_ENTITY_ID = p_hold_source_rec.hold_entity_id
2676: -- AND nvl(HS.HOLD_ENTITY_CODE2, 'NO_ENTITY_CODE2') =

Line 2700: SELECT OE_HOLD_SOURCES_S.NEXTVAL

2696:
2697: --ER#7479609 end
2698:
2699:
2700: SELECT OE_HOLD_SOURCES_S.NEXTVAL
2701: INTO x_hold_source_id
2702: FROM DUAL;
2703:
2704: INSERT INTO OE_HOLD_SOURCES_ALL

Line 2704: INSERT INTO OE_HOLD_SOURCES_ALL

2700: SELECT OE_HOLD_SOURCES_S.NEXTVAL
2701: INTO x_hold_source_id
2702: FROM DUAL;
2703:
2704: INSERT INTO OE_HOLD_SOURCES_ALL
2705: ( HOLD_SOURCE_ID
2706: , LAST_UPDATE_DATE
2707: , LAST_UPDATED_BY
2708: , CREATION_DATE

Line 2845: oe_hold_sources ohs,

2841: SELECT 'Y'
2842: FROM DUAL
2843: WHERE EXISTS (SELECT NULL
2844: FROM oe_order_holds ooh,
2845: oe_hold_sources ohs,
2846: oe_hold_definitions ohd,
2847: oe_order_headers_all h,
2848: oe_order_sources oos
2849: WHERE ohd.activity_name IS NULL

Line 2862: oe_hold_sources ohs,

2858: SELECT 'Y'
2859: FROM DUAL
2860: WHERE EXISTS (SELECT NULL
2861: FROM oe_order_holds ooh,
2862: oe_hold_sources ohs,
2863: oe_hold_definitions ohd,
2864: oe_order_headers_all h,
2865: oe_order_sources oos
2866: WHERE ohd.activity_name IS NULL

Line 2878: FROM oe_hold_sources ohs,

2874: CURSOR check_src_hold_type_cur(p_hld_src_id IN NUMBER) IS
2875: SELECT 'Y'
2876: FROM DUAL
2877: WHERE EXISTS (SELECT NULL
2878: FROM oe_hold_sources ohs,
2879: oe_hold_definitions ohd
2880: WHERE ohd.activity_name IS NULL
2881: AND ohd.hold_id = ohs.hold_id
2882: AND ohs.hold_source_id = p_hld_src_id);

Line 4789: FROM oe_hold_sources ohs,

4785: CURSOR check_hold_typ_cur(p_hld_src_id IN NUMBER) IS
4786: SELECT 'Y'
4787: FROM DUAL
4788: WHERE EXISTS (SELECT NULL
4789: FROM oe_hold_sources ohs,
4790: oe_hold_definitions ohd
4791: WHERE ohd.hold_id = ohs.hold_id
4792: -- AND ohd.activity_name IS NULL Bug 6791587
4793: AND ohs.hold_source_id = p_hld_src_id

Line 4958: oe_debug_pub.add('Updating oe_hold_sources for Release', 1);

4954: );
4955:
4956: -- Flag the hold source as released
4957:
4958: oe_debug_pub.add('Updating oe_hold_sources for Release', 1);
4959: --ER#7479609 UPDATE oe_hold_sources
4960: UPDATE oe_hold_sources_all --ER#7479609
4961: SET hold_release_id = x_hold_release_id
4962: , released_flag = 'Y'

Line 4959: --ER#7479609 UPDATE oe_hold_sources

4955:
4956: -- Flag the hold source as released
4957:
4958: oe_debug_pub.add('Updating oe_hold_sources for Release', 1);
4959: --ER#7479609 UPDATE oe_hold_sources
4960: UPDATE oe_hold_sources_all --ER#7479609
4961: SET hold_release_id = x_hold_release_id
4962: , released_flag = 'Y'
4963: , LAST_UPDATED_BY = l_user_id

Line 4960: UPDATE oe_hold_sources_all --ER#7479609

4956: -- Flag the hold source as released
4957:
4958: oe_debug_pub.add('Updating oe_hold_sources for Release', 1);
4959: --ER#7479609 UPDATE oe_hold_sources
4960: UPDATE oe_hold_sources_all --ER#7479609
4961: SET hold_release_id = x_hold_release_id
4962: , released_flag = 'Y'
4963: , LAST_UPDATED_BY = l_user_id
4964: , LAST_UPDATE_DATE = sysdate

Line 5001: FROM OE_HOLD_SOURCES HS

4997: l_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
4998: --
4999: CURSOR hold_source IS
5000: SELECT HS.HOLD_SOURCE_ID
5001: FROM OE_HOLD_SOURCES HS
5002: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
5003: AND HS.RELEASED_FLAG = 'N'
5004: --AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
5005: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code

Line 5014: FROM OE_HOLD_SOURCES HS

5010: nvl(p_hold_source_rec.hold_entity_id2, -99);
5011:
5012: CURSOR order_hold_source IS
5013: SELECT HS.HOLD_SOURCE_ID
5014: FROM OE_HOLD_SOURCES HS
5015: WHERE HS.HOLD_ID = p_hold_source_rec.hold_id
5016: AND HS.RELEASED_FLAG = 'N'
5017: --AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
5018: AND HS.HOLD_ENTITY_CODE = p_hold_source_rec.hold_entity_code

Line 5207: -- order, deletes hold source records from OE_HOLD_SOURCES.

5203: -- Delete Holds
5204: -- Deletes from OE_ORDER_HOLDS all hold records for an order (p_header_id)
5205: -- or for a line (p_line_id).
5206: -- Also, if there are ORDER hold sources (hold_entity_code = 'O') for this
5207: -- order, deletes hold source records from OE_HOLD_SOURCES.
5208: -- If the hold or hold source was released and the same release record is
5209: -- not used by an existing hold or hold source, then deletes the
5210: -- release record also from OE_HOLD_RELEASES;
5211: ----------------------------------------------------------------------------

Line 5230: FROM OE_HOLD_SOURCES_all

5226: FROM OE_ORDER_HOLDS_all
5227: WHERE HEADER_ID = p_order_rec.header_id;
5228: CURSOR hold_source IS
5229: SELECT hold_source_id, NVL(hold_release_id,0)
5230: FROM OE_HOLD_SOURCES_all
5231: WHERE HOLD_ENTITY_CODE = 'O'
5232: AND HOLD_ENTITY_ID = p_order_rec.header_id;
5233: CURSOR line_hold IS
5234: SELECT order_hold_id, NVL(hold_release_id,0)

Line 5283: FROM OE_HOLD_SOURCES_all

5279: AND HOLD_RELEASE_ID NOT IN (SELECT NVL(HOLD_RELEASE_ID,0)
5280: FROM OE_ORDER_HOLDS_all
5281: UNION
5282: SELECT NVL(HOLD_RELEASE_ID,0)
5283: FROM OE_HOLD_SOURCES_all
5284: );
5285: END LOOP;
5286:
5287: CLOSE order_hold;

Line 5298: DELETE FROM OE_HOLD_SOURCES_all

5294: EXIT;
5295: END IF;
5296: OE_Debug_PUB.Add('Deleting hold source record',1);
5297:
5298: DELETE FROM OE_HOLD_SOURCES_all
5299: WHERE HOLD_SOURCE_ID = l_hold_source_id;
5300:
5301: OE_Debug_PUB.Add('Deleting hold release record',1);
5302: DELETE FROM OE_HOLD_RELEASES

Line 5309: FROM OE_HOLD_SOURCES_all

5305: ( SELECT NVL(HOLD_RELEASE_ID,0)
5306: FROM OE_ORDER_HOLDS_all
5307: UNION
5308: SELECT NVL(HOLD_RELEASE_ID,0)
5309: FROM OE_HOLD_SOURCES_all
5310: );
5311:
5312: END LOOP;
5313:

Line 5342: FROM OE_HOLD_SOURCES_all

5338: (SELECT NVL(HOLD_RELEASE_ID,0)
5339: FROM OE_ORDER_HOLDS_all
5340: UNION
5341: SELECT NVL(HOLD_RELEASE_ID,0)
5342: FROM OE_HOLD_SOURCES_all
5343: );
5344:
5345: END LOOP;
5346:

Line 5399: p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE,

5395: --------------------
5396: Procedure Apply_Holds (
5397: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
5398: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
5399: p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE,
5400: p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE,
5401: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5402: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5403: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )

Line 5400: p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE,

5396: Procedure Apply_Holds (
5397: p_order_tbl IN OE_HOLDS_PVT.order_tbl_type,
5398: p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE,
5399: p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE,
5400: p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE,
5401: x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5402: x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
5403: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
5404: IS

Line 5407: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;

5403: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
5404: IS
5405: j NUMBER;
5406: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
5407: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
5408: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
5409: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
5410: l_hold_exists VARCHAR2(1) :='N'; --bug 5548778
5411: /*Added the Following Variables for WF_HOLDS ER (bug 6449458)*/

Line 5606: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;

5602: x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 )
5603: IS
5604: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
5605: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
5606: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
5607: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
5608: l_hold_exists VARCHAR2(1) :='N'; --bug 5548778
5609: /* Commented OLD Processing and added the lines below for WF_HOLS ER (bug 6449458)*/
5610: l_hold_msg_applied BOOLEAN DEFAULT NULL;

Line 5861: UPDATE OE_HOLD_SOURCES

5857:
5858: EXCEPTION
5859: WHEN NO_DATA_FOUND THEN
5860: OE_Debug_PUB.Add('Setting hold source status to released');
5861: UPDATE OE_HOLD_SOURCES
5862: SET RELEASED_FLAG = 'Y'
5863: , HOLD_RELEASE_ID = l_hold_release_id
5864: , LAST_UPDATED_BY = l_user_id
5865: , LAST_UPDATE_DATE = SYSDATE

Line 5932: , p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE

5928: procedure process_apply_holds_lines (
5929: p_num_of_records IN NUMBER
5930: , p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
5931: , p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
5932: , p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
5933: , p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE
5934: , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5935: , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5936: , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2

Line 5933: , p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE

5929: p_num_of_records IN NUMBER
5930: , p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
5931: , p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
5932: , p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
5933: , p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE
5934: , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5935: , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
5936: , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
5937: )

Line 6185: , p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE

6181: procedure process_apply_holds_orders (
6182: p_num_of_records IN NUMBER
6183: , p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
6184: , p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
6185: , p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
6186: , p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE
6187: , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6188: , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
6189: , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2

Line 6186: , p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE

6182: p_num_of_records IN NUMBER
6183: , p_sel_rec_tbl IN OE_GLOBALS.Selected_Record_Tbl
6184: , p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
6185: , p_hold_until_date IN OE_HOLD_SOURCES.HOLD_UNTIL_DATE%TYPE
6186: , p_hold_comment IN OE_HOLD_SOURCES.HOLD_COMMENT%TYPE
6187: , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6188: , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
6189: , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6190: )

Line 6781: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;

6777: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6778: )
6779: IS
6780: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
6781: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
6782: l_hold_release_rec OE_HOLDS_PVT.hold_release_rec_type;
6783: l_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
6784: l_api_name CONSTANT VARCHAR2(30) := 'process_create_holds';
6785: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;

Line 6999: p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE

6995: /* This procedure gets called when Release Source button is pressed
6996: from the Release Source window.
6997: */
6998: procedure process_release_source(
6999: p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
7000: ,p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type
7001: ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7002: ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
7003: ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2

Line 7013: l_hold_entity_code OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE; --ER#7479609

7009: l_hold_id OE_HOLD_DEFINITIONS.HOLD_ID%TYPE;
7010: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7011: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
7012: --ER#7479609 l_hold_entity_code varchar2(1); /* Added for Bug 1946783 */
7013: l_hold_entity_code OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE; --ER#7479609
7014: l_msg_token VARCHAR2(100);
7015: BEGIN
7016:
7017: BEGIN

Line 7020: from OE_HOLD_SOURCES_all

7016:
7017: BEGIN
7018: select hold_id, hold_entity_code
7019: into l_hold_id, l_hold_entity_code
7020: from OE_HOLD_SOURCES_all
7021: where hold_source_id = p_hold_source_id;
7022: EXCEPTION
7023: WHEN NO_DATA_FOUND THEN
7024: null;

Line 7588: p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE

7584: x_return_status Return status
7585: */
7586:
7587: procedure process_release_source(
7588: p_hold_source_id IN OE_Hold_Sources_ALL.HOLD_SOURCE_ID%TYPE
7589: ,p_hold_release_rec IN OE_HOLDS_PVT.Hold_Release_Rec_Type
7590: ,p_wf_release_action IN VARCHAR2
7591: ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7592: ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER

Line 7603: l_hold_entity_code OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE; --ER#7479609

7599: l_hold_id OE_HOLD_DEFINITIONS.HOLD_ID%TYPE;
7600: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE := FND_GLOBAL.RESP_APPL_ID;
7601: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE := FND_GLOBAL.RESP_ID;
7602: --ER#7479609 l_hold_entity_code VARCHAR2(1); /* Added for Bug 1946783 */
7603: l_hold_entity_code OE_HOLD_SOURCES_ALL.HOLD_ENTITY_CODE%TYPE; --ER#7479609
7604: l_msg_token VARCHAR2(100);
7605: BEGIN
7606:
7607:

Line 7611: from OE_HOLD_SOURCES_all

7607:
7608: BEGIN
7609: select hold_id, hold_entity_code
7610: into l_hold_id, l_hold_entity_code
7611: from OE_HOLD_SOURCES_all
7612: where hold_source_id = p_hold_source_id;
7613: EXCEPTION
7614: WHEN NO_DATA_FOUND THEN
7615: null;

Line 7779: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;

7775: l_org_id NUMBER;
7776: l_api_name CONSTANT VARCHAR2(30) := 'SPLIT_HOLD';
7777:
7778: l_line_number NUMBER;
7779: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
7780: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7781:
7782: CURSOR c_order_holds IS
7783: SELECT oh.HOLD_SOURCE_ID

Line 7807: OE_HOLD_SOURCES_all hs

7803: , hs.ATTRIBUTE14
7804: , hs.ATTRIBUTE15
7805: , oh.org_id
7806: FROM OE_ORDER_HOLDS_all oh,
7807: OE_HOLD_SOURCES_all hs
7808: WHERE oh.line_id = p_split_from_line_id
7809: AND oh.RELEASED_FLAG = 'N'
7810: AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID;
7811:

Line 7942: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;

7938: l_application_id OE_HOLD_AUTHORIZATIONS.APPLICATION_ID%TYPE :=
7939: FND_GLOBAL.RESP_APPL_ID;
7940: l_resp_id OE_HOLD_AUTHORIZATIONS.RESPONSIBILITY_ID%TYPE
7941: := FND_GLOBAL.RESP_ID;
7942: l_hold_source_id OE_HOLD_SOURCES_ALL.HOLD_SOURCE_ID%TYPE;
7943: l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
7944: l_hold_exists VARCHAR2(1) :='N'; --bug 5548778
7945:
7946: l_is_hold_applied BOOLEAN;

Line 8067: oe_hold_sources ohs,

8063: SELECT 'Y'
8064: FROM DUAL
8065: WHERE EXISTS (SELECT NULL
8066: FROM oe_order_holds ooh,
8067: oe_hold_sources ohs,
8068: oe_hold_definitions ohd,
8069: oe_order_headers_all h,
8070: oe_order_sources oos
8071: WHERE ohd.hold_id = ohs.hold_id

Line 8084: oe_hold_sources ohs,

8080: SELECT 'Y'
8081: FROM DUAL
8082: WHERE EXISTS (SELECT NULL
8083: FROM oe_order_holds ooh,
8084: oe_hold_sources ohs,
8085: oe_hold_definitions ohd,
8086: oe_order_headers_all h,
8087: oe_order_sources oos
8088: WHERE ohd.hold_id = ohs.hold_id

Line 8100: FROM oe_hold_sources ohs,

8096: CURSOR check_src_hold_type_cur(p_hld_src_id IN NUMBER) IS
8097: SELECT 'Y'
8098: FROM DUAL
8099: WHERE EXISTS (SELECT NULL
8100: FROM oe_hold_sources ohs,
8101: oe_hold_definitions ohd
8102: WHERE ohd.hold_id = ohs.hold_id
8103: --AND ohd.activity_name IS NULL Bug 6791587
8104: AND ohs.hold_source_id = p_hld_src_id);

Line 8113: oe_hold_sources_all ohs,

8109: SELECT 'Y'
8110: FROM DUAL
8111: WHERE EXISTS (SELECT NULL
8112: FROM oe_order_holds_all ooh,
8113: oe_hold_sources_all ohs,
8114: oe_hold_definitions ohd,
8115: oe_order_headers_all h,
8116: oe_order_sources oos
8117: WHERE ohd.hold_id = ohs.hold_id

Line 8132: oe_hold_sources_all ohs,

8128: SELECT 'Y'
8129: FROM DUAL
8130: WHERE EXISTS (SELECT NULL
8131: FROM oe_order_holds_all ooh,
8132: oe_hold_sources_all ohs,
8133: oe_hold_definitions ohd,
8134: oe_order_headers_all h,
8135: oe_order_sources oos
8136: WHERE ohd.hold_id = ohs.hold_id

Line 8150: FROM oe_hold_sources_all ohs,

8146: CURSOR check_src_hold_type_cur(p_hld_src_id IN NUMBER) IS
8147: SELECT 'Y'
8148: FROM DUAL
8149: WHERE EXISTS (SELECT NULL
8150: FROM oe_hold_sources_all ohs,
8151: oe_hold_definitions ohd
8152: WHERE ohd.hold_id = ohs.hold_id
8153: AND ohs.org_id = p_org_id
8154: AND ohs.hold_source_id = p_hld_src_id);