[Home] [Help]
408: IF p_order_tbl(1).line_id IS NULL THEN
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
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:
746:
747: --ER#7479609 start
748: ---------------------------------------------------------------------------------------------------
749: -- PROCEDURE : InsertTable_OOH_Header
750: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table
751: -- for header level holds
752:
753: -- Caller : Create_Order_Holds Procedure
754: -- PARAMETERS:
783:
784: OE_DEBUG_PUB.ADD('Entering InsertTable_OOH_Header');
785:
786: IF p_header_id IS NOT NULL THEN
787: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
788: ( ORDER_HOLD_ID
789: , LAST_UPDATE_DATE
790: , LAST_UPDATED_BY
791: , CREATION_DATE
797: , RELEASED_FLAG
798: , ORG_ID
799: )
800: SELECT
801: OE_ORDER_HOLDS_S.NEXTVAL
802: , SYSDATE
803: , :l_user_id
804: , SYSDATE
805: , :l_user_id
814: and h.org_id = :l_org_id
815: and h.header_id = :header_id
816: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
817: and not exists ( select ''x''
818: from oe_order_holds_ALL oh
819: where oh.header_id = h.header_id
820: and oh.line_id IS NULL
821: and oh.hold_source_id =:hold_source_id )';
822: IF p_item_type is not null and p_activity_name is not null then
870:
871: ELSE
872:
873:
874: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
875: ( ORDER_HOLD_ID
876: , LAST_UPDATE_DATE
877: , LAST_UPDATED_BY
878: , CREATION_DATE
884: , RELEASED_FLAG
885: , ORG_ID
886: )
887: SELECT
888: OE_ORDER_HOLDS_S.NEXTVAL
889: , SYSDATE
890: , :l_user_id
891: , SYSDATE
892: , :l_user_id
900: WHERE h.OPEN_FLAG = ''Y''
901: and h.org_id = :l_org_id
902: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
903: and not exists ( select ''x''
904: from oe_order_holds_ALL oh
905: where oh.header_id = h.header_id
906: and oh.line_id IS NULL
907: and oh.hold_source_id =:hold_source_id )';
908: IF p_item_type is not null and p_activity_name is not null then
967: FROM OE_ORDER_HEADERS_ALL h
968: WHERE h.OPEN_FLAG = ''Y''
969: AND nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
970: AND NOT EXISTS ( select ''x''
971: from oe_order_holds_ALL oh
972: where oh.header_id = h.header_id
973: and oh.hold_source_id = '||p_hold_source_id||' )';
974:
975: IF p_hold_entity_where_clause IS NOT NULL THEN
1011:
1012: --ER#7479609 start
1013: ---------------------------------------------------------------------------------------------------
1014: -- PROCEDURE : InsertTable_OOH_Line
1015: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table
1016: -- for line level holds
1017:
1018: -- Caller : Create_Order_Holds Procedure
1019: -- PARAMETERS:
1048: OE_DEBUG_PUB.ADD('Entering InsertTable_OOH_Line');
1049:
1050: IF p_line_id IS NOT NULL THEN
1051:
1052: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
1053: ( ORDER_HOLD_ID
1054: , LAST_UPDATE_DATE
1055: , LAST_UPDATED_BY
1056: , CREATION_DATE
1062: , RELEASED_FLAG
1063: , ORG_ID
1064: )
1065: SELECT
1066: OE_ORDER_HOLDS_S.NEXTVAL
1067: , SYSDATE
1068: , :l_user_id
1069: , SYSDATE
1070: , :l_user_id
1081: and ol.line_id = :line_id
1082: and ol.OPEN_FLAG = ''Y''
1083: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1084: and not exists ( select ''x''
1085: from oe_order_holds_ALL oh
1086: where oh.header_id = h.header_id
1087: and oh.line_id = ol.line_id
1088: and oh.hold_source_id = :hold_source_id )';
1089: IF p_item_type is not null and p_activity_name is not null then
1151: END IF;
1152:
1153: ELSE
1154:
1155: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
1156: ( ORDER_HOLD_ID
1157: , LAST_UPDATE_DATE
1158: , LAST_UPDATED_BY
1159: , CREATION_DATE
1165: , RELEASED_FLAG
1166: , ORG_ID
1167: )
1168: SELECT
1169: OE_ORDER_HOLDS_S.NEXTVAL
1170: , SYSDATE
1171: , :l_user_id
1172: , SYSDATE
1173: , :l_user_id
1183: and h.org_id = :l_org_id
1184: and ol.OPEN_FLAG = ''Y''
1185: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1186: and not exists ( select ''x''
1187: from oe_order_holds_ALL oh
1188: where oh.header_id = h.header_id
1189: and oh.line_id = ol.line_id
1190: and oh.hold_source_id =:hold_source_id )';
1191:
1262: AND h.header_id = ol.header_id
1263: AND ol.OPEN_FLAG = ''Y''
1264: AND NVL(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1265: AND NOT EXISTS ( SELECT ''x''
1266: FROM oe_order_holds_ALL oh
1267: WHERE oh.header_id = h.header_id
1268: AND oh.line_id = ol.line_id
1269: AND oh.hold_source_id = '||p_hold_source_id||' )';
1270:
1305:
1306: --ER#7479609 start
1307: ---------------------------------------------------------------------------------------------------
1308: -- PROCEDURE : PaymentType_Hold
1309: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table
1310: -- for header level holds
1311:
1312: -- Caller : Create_Order_Holds Procedure
1313: -- PARAMETERS:
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)
1553: and hs.HOLD_SOURCE_ID = oh.HOLD_SOURCE_ID
1559: CURSOR check_hold_type_cur(p_ord_hld_id IN NUMBER) IS
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
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;
1587: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1688: , l_order_hold_id
1689: );
1690:
1691: --dbms_output.put_line('RlsID:'||to_char(p_hold_release_rec.hold_release_id));
1692: UPDATE oe_order_holds
1693: SET hold_release_id = x_hold_release_id,
1694: LAST_UPDATED_BY = l_user_id,
1695: LAST_UPDATE_DATE = sysdate,
1696: RELEASED_FLAG = 'Y'
2342: AND HS.RELEASED_FLAG = 'N'
2343: AND HS.org_id= p_org_id --ER#7479609
2344: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2345: AND EXISTS ( select 'x'
2346: --ER#7479609 from oe_order_holds OH
2347: from oe_order_holds_all OH --ER#7479609
2348: where OH.line_id = p_hold_source_rec.line_id
2349: and OH.org_id= p_org_id --ER#7479609
2350: and OH.hold_source_id = HS.hold_source_id);
2343: AND HS.org_id= p_org_id --ER#7479609
2344: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2345: AND EXISTS ( select 'x'
2346: --ER#7479609 from oe_order_holds OH
2347: from oe_order_holds_all OH --ER#7479609
2348: where OH.line_id = p_hold_source_rec.line_id
2349: and OH.org_id= p_org_id --ER#7479609
2350: and OH.hold_source_id = HS.hold_source_id);
2351:
2373: AND HS.RELEASED_FLAG = 'N'
2374: AND HS.org_id= p_org_id --ER#7479609
2375: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2376: AND EXISTS ( select 'x'
2377: --ER#7479609 from oe_order_holds OH
2378: from oe_order_holds_all OH --ER#7479609
2379: where OH.line_id is null
2380: and OH.org_id= p_org_id --ER#7479609
2381: and OH.hold_source_id = HS.hold_source_id);
2374: AND HS.org_id= p_org_id --ER#7479609
2375: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2376: AND EXISTS ( select 'x'
2377: --ER#7479609 from oe_order_holds OH
2378: from oe_order_holds_all OH --ER#7479609
2379: where OH.line_id is null
2380: and OH.org_id= p_org_id --ER#7479609
2381: and OH.hold_source_id = HS.hold_source_id);
2382:
2584: AND HS.RELEASED_FLAG = 'N'
2585: AND HS.ORG_ID = p_org_id --ER#7479609
2586: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2587: AND EXISTS ( select 'x'
2588: --ER#7479609 from oe_order_holds OH
2589: from oe_order_holds_all OH --ER#7479609
2590: where OH.line_id = p_hold_source_rec.line_id
2591: and OH.hold_source_id = HS.hold_source_id);
2592:
2585: AND HS.ORG_ID = p_org_id --ER#7479609
2586: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2587: AND EXISTS ( select 'x'
2588: --ER#7479609 from oe_order_holds OH
2589: from oe_order_holds_all OH --ER#7479609
2590: where OH.line_id = p_hold_source_rec.line_id
2591: and OH.hold_source_id = HS.hold_source_id);
2592:
2593: IF l_count > 0 THEN
2613: AND HS.RELEASED_FLAG = 'N'
2614: AND HS.ORG_ID = p_org_id --ER#7479609
2615: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2616: AND EXISTS ( select 'x'
2617: --ER#7479609 from oe_order_holds OH
2618: from oe_order_holds_all OH --ER#7479609
2619: where OH.line_id is null
2620: and OH.hold_source_id = HS.hold_source_id);
2621:
2614: AND HS.ORG_ID = p_org_id --ER#7479609
2615: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2616: AND EXISTS ( select 'x'
2617: --ER#7479609 from oe_order_holds OH
2618: from oe_order_holds_all OH --ER#7479609
2619: where OH.line_id is null
2620: and OH.hold_source_id = HS.hold_source_id);
2621:
2622: IF l_count > 0 THEN
2646: AND HS.ORG_ID = p_org_id --ER#7479609
2647: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
2648:
2649: -- AND EXISTS ( select 'x'
2650: -- from oe_order_holds
2651: -- where header_id = p_hold_source_rec.hold_entity_id
2652: -- and line_id = nvl(p_hold_source_rec.line_id, -99));
2653:
2654: IF l_count > 0 THEN
2840: CURSOR check_line_hold_type_cur(p_line_id IN NUMBER) IS
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
2857: CURSOR check_hdr_hold_type_cur(p_hdr_id IN NUMBER) IS
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
2933:
2934: IF p_hold_source_rec.hold_entity_code = 'I' and
2935: p_hold_source_rec.hold_entity_code2 = 'C' THEN
2936: IF p_hold_source_rec.line_id IS NOT NULL THEN
2937: INSERT INTO OE_ORDER_HOLDS_ALL
2938: ( ORDER_HOLD_ID
2939: , LAST_UPDATE_DATE
2940: , LAST_UPDATED_BY
2941: , CREATION_DATE
2947: , RELEASED_FLAG
2948: , ORG_ID
2949: )
2950: SELECT
2951: OE_ORDER_HOLDS_S.NEXTVAL
2952: , SYSDATE
2953: , l_user_id
2954: , SYSDATE
2955: , l_user_id
2969: and ol.OPEN_FLAG = 'Y'
2970: -- QUOTING change
2971: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
2972: and not exists ( select 'x'
2973: from oe_order_holds_ALL oh
2974: where oh.header_id = h.header_id
2975: and oh.line_id = ol.line_id
2976: and oh.hold_source_id =
2977: p_hold_source_rec.hold_source_id );
2975: and oh.line_id = ol.line_id
2976: and oh.hold_source_id =
2977: p_hold_source_rec.hold_source_id );
2978: ELSE
2979: INSERT INTO OE_ORDER_HOLDS_ALL
2980: ( ORDER_HOLD_ID
2981: , LAST_UPDATE_DATE
2982: , LAST_UPDATED_BY
2983: , CREATION_DATE
2989: , RELEASED_FLAG
2990: , ORG_ID
2991: )
2992: SELECT
2993: OE_ORDER_HOLDS_S.NEXTVAL
2994: , SYSDATE
2995: , l_user_id
2996: , SYSDATE
2997: , l_user_id
3010: and ol.OPEN_FLAG = 'Y'
3011: -- QUOTING change
3012: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3013: and not exists ( select 'x'
3014: from oe_order_holds_ALL oh
3015: where oh.header_id = h.header_id
3016: and oh.line_id = ol.line_id
3017: and oh.hold_source_id =
3018: p_hold_source_rec.hold_source_id );
3020: Only used by Credit checking
3021: ELSIF p_hold_source_rec.hold_entity_code = 'B' and
3022: p_hold_source_rec.hold_entity_code2 = 'O' THEN
3023: IF p_hold_source_rec.line_id IS NOT NULL THEN
3024: INSERT INTO OE_ORDER_HOLDS_ALL
3025: ( ORDER_HOLD_ID
3026: , LAST_UPDATE_DATE
3027: , LAST_UPDATED_BY
3028: , CREATION_DATE
3034: , RELEASED_FLAG
3035: , ORG_ID
3036: )
3037: SELECT
3038: OE_ORDER_HOLDS_S.NEXTVAL
3039: , SYSDATE
3040: , l_user_id
3041: , SYSDATE
3042: , l_user_id
3058: and rt.CREDIT_CHECK_FLAG = 'Y'
3059: -- QUOTING change
3060: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3061: and not exists ( select 'x'
3062: from oe_order_holds_ALL oh
3063: where oh.header_id = h.header_id
3064: and oh.line_id = ol.line_id
3065: and oh.hold_source_id =
3066: p_hold_source_rec.hold_source_id );
3064: and oh.line_id = ol.line_id
3065: and oh.hold_source_id =
3066: p_hold_source_rec.hold_source_id );
3067: ELSE
3068: INSERT INTO OE_ORDER_HOLDS_ALL
3069: ( ORDER_HOLD_ID
3070: , LAST_UPDATE_DATE
3071: , LAST_UPDATED_BY
3072: , CREATION_DATE
3078: , RELEASED_FLAG
3079: , ORG_ID
3080: )
3081: SELECT
3082: OE_ORDER_HOLDS_S.NEXTVAL
3083: , SYSDATE
3084: , l_user_id
3085: , SYSDATE
3086: , l_user_id
3101: and rt.CREDIT_CHECK_FLAG = 'Y'
3102: -- QUOTING change
3103: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3104: and not exists ( select 'x'
3105: from oe_order_holds_ALL oh
3106: where oh.header_id = h.header_id
3107: and oh.line_id = ol.line_id
3108: and oh.hold_source_id =
3109: p_hold_source_rec.hold_source_id );
3111:
3112: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
3113: p_hold_source_rec.hold_entity_code2 = 'B' THEN
3114: IF p_hold_source_rec.line_id IS NOT NULL THEN
3115: INSERT INTO OE_ORDER_HOLDS_ALL
3116: ( ORDER_HOLD_ID
3117: , LAST_UPDATE_DATE
3118: , LAST_UPDATED_BY
3119: , CREATION_DATE
3125: , RELEASED_FLAG
3126: , ORG_ID
3127: )
3128: SELECT
3129: OE_ORDER_HOLDS_S.NEXTVAL
3130: , SYSDATE
3131: , l_user_id
3132: , SYSDATE
3133: , l_user_id
3147: and ol.OPEN_FLAG = 'Y'
3148: -- QUOTING change
3149: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3150: and not exists ( select 'x'
3151: from oe_order_holds_ALL oh
3152: where oh.header_id = h.header_id
3153: and oh.line_id = ol.line_id
3154: and oh.hold_source_id =
3155: p_hold_source_rec.hold_source_id );
3153: and oh.line_id = ol.line_id
3154: and oh.hold_source_id =
3155: p_hold_source_rec.hold_source_id );
3156: ELSE
3157: INSERT INTO OE_ORDER_HOLDS_ALL
3158: ( ORDER_HOLD_ID
3159: , LAST_UPDATE_DATE
3160: , LAST_UPDATED_BY
3161: , CREATION_DATE
3167: , RELEASED_FLAG
3168: , ORG_ID
3169: )
3170: SELECT
3171: OE_ORDER_HOLDS_S.NEXTVAL
3172: , SYSDATE
3173: , l_user_id
3174: , SYSDATE
3175: , l_user_id
3188: and ol.OPEN_FLAG = 'Y'
3189: -- QUOTING change
3190: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3191: and not exists ( select 'x'
3192: from oe_order_holds_ALL oh
3193: where oh.header_id = h.header_id
3194: and oh.line_id = ol.line_id
3195: and oh.hold_source_id =
3196: p_hold_source_rec.hold_source_id );
3197: END IF;
3198: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
3199: p_hold_source_rec.hold_entity_code2 = 'S' THEN
3200: IF p_hold_source_rec.line_id IS NOT NULL THEN
3201: INSERT INTO OE_ORDER_HOLDS_ALL
3202: ( ORDER_HOLD_ID
3203: , LAST_UPDATE_DATE
3204: , LAST_UPDATED_BY
3205: , CREATION_DATE
3211: , RELEASED_FLAG
3212: , ORG_ID
3213: )
3214: SELECT
3215: OE_ORDER_HOLDS_S.NEXTVAL
3216: , SYSDATE
3217: , l_user_id
3218: , SYSDATE
3219: , l_user_id
3233: and ol.OPEN_FLAG = 'Y'
3234: -- QUOTING change
3235: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3236: and not exists ( select 'x'
3237: from oe_order_holds_ALL oh
3238: where oh.header_id = h.header_id
3239: and oh.line_id = ol.line_id
3240: and oh.hold_source_id =
3241: p_hold_source_rec.hold_source_id );
3239: and oh.line_id = ol.line_id
3240: and oh.hold_source_id =
3241: p_hold_source_rec.hold_source_id );
3242: ELSE
3243: INSERT INTO OE_ORDER_HOLDS_ALL
3244: ( ORDER_HOLD_ID
3245: , LAST_UPDATE_DATE
3246: , LAST_UPDATED_BY
3247: , CREATION_DATE
3253: , RELEASED_FLAG
3254: , ORG_ID
3255: )
3256: SELECT
3257: OE_ORDER_HOLDS_S.NEXTVAL
3258: , SYSDATE
3259: , l_user_id
3260: , SYSDATE
3261: , l_user_id
3274: and ol.OPEN_FLAG = 'Y'
3275: -- QUOTING change
3276: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3277: and not exists ( select 'x'
3278: from oe_order_holds_ALL oh
3279: where oh.header_id = h.header_id
3280: and oh.line_id = ol.line_id
3281: and oh.hold_source_id =
3282: p_hold_source_rec.hold_source_id );
3283: END IF;
3284: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
3285: p_hold_source_rec.hold_entity_code2 = 'W' THEN
3286: IF p_hold_source_rec.line_id IS NOT NULL THEN
3287: INSERT INTO OE_ORDER_HOLDS_ALL
3288: ( ORDER_HOLD_ID
3289: , LAST_UPDATE_DATE
3290: , LAST_UPDATED_BY
3291: , CREATION_DATE
3297: , RELEASED_FLAG
3298: , ORG_ID
3299: )
3300: SELECT
3301: OE_ORDER_HOLDS_S.NEXTVAL
3302: , SYSDATE
3303: , l_user_id
3304: , SYSDATE
3305: , l_user_id
3319: and ol.OPEN_FLAG = 'Y'
3320: -- QUOTING change
3321: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3322: and not exists ( select 'x'
3323: from oe_order_holds_ALL oh
3324: where oh.header_id = h.header_id
3325: and oh.line_id = ol.line_id
3326: and oh.hold_source_id =
3327: p_hold_source_rec.hold_source_id );
3325: and oh.line_id = ol.line_id
3326: and oh.hold_source_id =
3327: p_hold_source_rec.hold_source_id );
3328: ELSE
3329: INSERT INTO OE_ORDER_HOLDS_ALL
3330: ( ORDER_HOLD_ID
3331: , LAST_UPDATE_DATE
3332: , LAST_UPDATED_BY
3333: , CREATION_DATE
3339: , RELEASED_FLAG
3340: , ORG_ID
3341: )
3342: SELECT
3343: OE_ORDER_HOLDS_S.NEXTVAL
3344: , SYSDATE
3345: , l_user_id
3346: , SYSDATE
3347: , l_user_id
3360: and ol.OPEN_FLAG = 'Y'
3361: -- QUOTING change
3362: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3363: and not exists ( select 'x'
3364: from oe_order_holds_ALL oh
3365: where oh.header_id = h.header_id
3366: and oh.line_id = ol.line_id
3367: and oh.hold_source_id =
3368: p_hold_source_rec.hold_source_id );
3369: END IF;
3370: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
3371: p_hold_source_rec.hold_entity_code2 = 'H' THEN
3372: IF p_hold_source_rec.line_id IS NOT NULL THEN
3373: INSERT INTO OE_ORDER_HOLDS_ALL
3374: ( ORDER_HOLD_ID
3375: , LAST_UPDATE_DATE
3376: , LAST_UPDATED_BY
3377: , CREATION_DATE
3383: , RELEASED_FLAG
3384: , ORG_ID
3385: )
3386: SELECT
3387: OE_ORDER_HOLDS_S.NEXTVAL
3388: , SYSDATE
3389: , l_user_id
3390: , SYSDATE
3391: , l_user_id
3405: and ol.OPEN_FLAG = 'Y'
3406: -- QUOTING change
3407: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3408: and not exists ( select 'x'
3409: from oe_order_holds_ALL oh
3410: where oh.header_id = h.header_id
3411: and oh.line_id = ol.line_id
3412: and oh.hold_source_id =
3413: p_hold_source_rec.hold_source_id );
3411: and oh.line_id = ol.line_id
3412: and oh.hold_source_id =
3413: p_hold_source_rec.hold_source_id );
3414: ELSE
3415: INSERT INTO OE_ORDER_HOLDS_ALL
3416: ( ORDER_HOLD_ID
3417: , LAST_UPDATE_DATE
3418: , LAST_UPDATED_BY
3419: , CREATION_DATE
3425: , RELEASED_FLAG
3426: , ORG_ID
3427: )
3428: SELECT
3429: OE_ORDER_HOLDS_S.NEXTVAL
3430: , SYSDATE
3431: , l_user_id
3432: , SYSDATE
3433: , l_user_id
3446: and ol.OPEN_FLAG = 'Y'
3447: -- QUOTING change
3448: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3449: and not exists ( select 'x'
3450: from oe_order_holds_ALL oh
3451: where oh.header_id = h.header_id
3452: and oh.line_id = ol.line_id
3453: and oh.hold_source_id =
3454: p_hold_source_rec.hold_source_id );
3456:
3457: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
3458: p_hold_source_rec.hold_entity_code2 = 'C' THEN
3459: IF p_hold_source_rec.line_id IS NOT NULL THEN
3460: INSERT INTO OE_ORDER_HOLDS_ALL
3461: ( ORDER_HOLD_ID
3462: , LAST_UPDATE_DATE
3463: , LAST_UPDATED_BY
3464: , CREATION_DATE
3470: , RELEASED_FLAG
3471: , ORG_ID
3472: )
3473: SELECT
3474: OE_ORDER_HOLDS_S.NEXTVAL
3475: , SYSDATE
3476: , l_user_id
3477: , SYSDATE
3478: , l_user_id
3492: and ol.OPEN_FLAG = 'Y'
3493: -- QUOTING change
3494: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3495: and not exists ( select 'x'
3496: from oe_order_holds_ALL oh
3497: where oh.header_id = h.header_id
3498: and oh.line_id = ol.line_id
3499: and oh.hold_source_id =
3500: p_hold_source_rec.hold_source_id );
3498: and oh.line_id = ol.line_id
3499: and oh.hold_source_id =
3500: p_hold_source_rec.hold_source_id );
3501: ELSE
3502: INSERT INTO OE_ORDER_HOLDS_ALL
3503: ( ORDER_HOLD_ID
3504: , LAST_UPDATE_DATE
3505: , LAST_UPDATED_BY
3506: , CREATION_DATE
3512: , RELEASED_FLAG
3513: , ORG_ID
3514: )
3515: SELECT
3516: OE_ORDER_HOLDS_S.NEXTVAL
3517: , SYSDATE
3518: , l_user_id
3519: , SYSDATE
3520: , l_user_id
3533: and ol.OPEN_FLAG = 'Y'
3534: -- QUOTING change
3535: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3536: and not exists ( select 'x'
3537: from oe_order_holds_ALL oh
3538: where oh.header_id = h.header_id
3539: and oh.line_id = ol.line_id
3540: and oh.hold_source_id =
3541: p_hold_source_rec.hold_source_id );
3542: END IF;
3543: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
3544: p_hold_source_rec.hold_entity_code2 = 'B' THEN
3545: IF p_hold_source_rec.line_id IS NOT NULL THEN
3546: INSERT INTO OE_ORDER_HOLDS_ALL
3547: ( ORDER_HOLD_ID
3548: , LAST_UPDATE_DATE
3549: , LAST_UPDATED_BY
3550: , CREATION_DATE
3556: , RELEASED_FLAG
3557: , ORG_ID
3558: )
3559: SELECT
3560: OE_ORDER_HOLDS_S.NEXTVAL
3561: , SYSDATE
3562: , l_user_id
3563: , SYSDATE
3564: , l_user_id
3578: and ol.OPEN_FLAG = 'Y'
3579: -- QUOTING change
3580: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3581: AND NOT EXISTS ( SELECT 'x'
3582: FROM oe_order_holds_ALL oh
3583: WHERE oh.header_id = h.header_id
3584: AND oh.line_id = ol.line_id
3585: AND oh.hold_source_id =
3586: p_hold_source_rec.hold_source_id );
3584: AND oh.line_id = ol.line_id
3585: AND oh.hold_source_id =
3586: p_hold_source_rec.hold_source_id );
3587: ELSE
3588: INSERT INTO OE_ORDER_HOLDS_ALL
3589: ( ORDER_HOLD_ID
3590: , LAST_UPDATE_DATE
3591: , LAST_UPDATED_BY
3592: , CREATION_DATE
3598: , RELEASED_FLAG
3599: , ORG_ID
3600: )
3601: SELECT
3602: OE_ORDER_HOLDS_S.NEXTVAL
3603: , SYSDATE
3604: , l_user_id
3605: , SYSDATE
3606: , l_user_id
3619: and ol.OPEN_FLAG = 'Y'
3620: -- QUOTING change
3621: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3622: AND NOT EXISTS ( SELECT 'x'
3623: FROM oe_order_holds_ALL oh
3624: WHERE oh.header_id = h.header_id
3625: AND oh.line_id = ol.line_id
3626: AND oh.hold_source_id =
3627: p_hold_source_rec.hold_source_id );
3628: END IF;
3629: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
3630: p_hold_source_rec.hold_entity_code2 = 'S' THEN
3631: IF p_hold_source_rec.line_id IS NOT NULL THEN
3632: INSERT INTO OE_ORDER_HOLDS_ALL
3633: ( ORDER_HOLD_ID
3634: , LAST_UPDATE_DATE
3635: , LAST_UPDATED_BY
3636: , CREATION_DATE
3642: , RELEASED_FLAG
3643: , ORG_ID
3644: )
3645: SELECT
3646: OE_ORDER_HOLDS_S.NEXTVAL
3647: , SYSDATE
3648: , l_user_id
3649: , SYSDATE
3650: , l_user_id
3664: and ol.OPEN_FLAG = 'Y'
3665: -- QUOTING change
3666: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3667: AND NOT EXISTS ( SELECT 'x'
3668: FROM oe_order_holds_ALL oh
3669: WHERE oh.header_id = h.header_id
3670: AND oh.line_id = ol.line_id
3671: AND oh.hold_source_id =
3672: p_hold_source_rec.hold_source_id );
3670: AND oh.line_id = ol.line_id
3671: AND oh.hold_source_id =
3672: p_hold_source_rec.hold_source_id );
3673: ELSE
3674: INSERT INTO OE_ORDER_HOLDS_ALL
3675: ( ORDER_HOLD_ID
3676: , LAST_UPDATE_DATE
3677: , LAST_UPDATED_BY
3678: , CREATION_DATE
3684: , RELEASED_FLAG
3685: , ORG_ID
3686: )
3687: SELECT
3688: OE_ORDER_HOLDS_S.NEXTVAL
3689: , SYSDATE
3690: , l_user_id
3691: , SYSDATE
3692: , l_user_id
3705: and ol.OPEN_FLAG = 'Y'
3706: -- QUOTING change
3707: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3708: AND NOT EXISTS ( SELECT 'x'
3709: FROM oe_order_holds_ALL oh
3710: WHERE oh.header_id = h.header_id
3711: AND oh.line_id = ol.line_id
3712: AND oh.hold_source_id =
3713: p_hold_source_rec.hold_source_id );
3714: END IF;
3715: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
3716: p_hold_source_rec.hold_entity_code2 = 'B' THEN
3717: IF p_hold_source_rec.line_id IS NOT NULL THEN
3718: INSERT INTO OE_ORDER_HOLDS_ALL
3719: ( ORDER_HOLD_ID
3720: , LAST_UPDATE_DATE
3721: , LAST_UPDATED_BY
3722: , CREATION_DATE
3728: , RELEASED_FLAG
3729: , ORG_ID
3730: )
3731: SELECT
3732: OE_ORDER_HOLDS_S.NEXTVAL
3733: , SYSDATE
3734: , l_user_id
3735: , SYSDATE
3736: , l_user_id
3750: and ol.OPEN_FLAG = 'Y'
3751: -- QUOTING change
3752: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3753: and not exists ( select 'x'
3754: from oe_order_holds_ALL oh
3755: where oh.header_id = h.header_id
3756: and oh.line_id = ol.line_id
3757: and oh.hold_source_id =
3758: p_hold_source_rec.hold_source_id );
3756: and oh.line_id = ol.line_id
3757: and oh.hold_source_id =
3758: p_hold_source_rec.hold_source_id );
3759: ELSE
3760: INSERT INTO OE_ORDER_HOLDS_ALL
3761: ( ORDER_HOLD_ID
3762: , LAST_UPDATE_DATE
3763: , LAST_UPDATED_BY
3764: , CREATION_DATE
3770: , RELEASED_FLAG
3771: , ORG_ID
3772: )
3773: SELECT
3774: OE_ORDER_HOLDS_S.NEXTVAL
3775: , SYSDATE
3776: , l_user_id
3777: , SYSDATE
3778: , l_user_id
3791: and ol.OPEN_FLAG = 'Y'
3792: -- QUOTING change
3793: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3794: and not exists ( select 'x'
3795: from oe_order_holds_ALL oh
3796: where oh.header_id = h.header_id
3797: and oh.line_id = ol.line_id
3798: and oh.hold_source_id =
3799: p_hold_source_rec.hold_source_id );
3801:
3802: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
3803: p_hold_source_rec.hold_entity_code2 = 'S' THEN
3804: IF p_hold_source_rec.line_id IS NOT NULL THEN
3805: INSERT INTO OE_ORDER_HOLDS_ALL
3806: ( ORDER_HOLD_ID
3807: , LAST_UPDATE_DATE
3808: , LAST_UPDATED_BY
3809: , CREATION_DATE
3815: , RELEASED_FLAG
3816: , ORG_ID
3817: )
3818: SELECT
3819: OE_ORDER_HOLDS_S.NEXTVAL
3820: , SYSDATE
3821: , l_user_id
3822: , SYSDATE
3823: , l_user_id
3837: and ol.OPEN_FLAG = 'Y'
3838: -- QUOTING change
3839: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3840: and not exists ( select 'x'
3841: from oe_order_holds_ALL oh
3842: where oh.header_id = h.header_id
3843: and oh.line_id = ol.line_id
3844: and oh.hold_source_id =
3845: p_hold_source_rec.hold_source_id );
3843: and oh.line_id = ol.line_id
3844: and oh.hold_source_id =
3845: p_hold_source_rec.hold_source_id );
3846: ELSE
3847: INSERT INTO OE_ORDER_HOLDS_ALL
3848: ( ORDER_HOLD_ID
3849: , LAST_UPDATE_DATE
3850: , LAST_UPDATED_BY
3851: , CREATION_DATE
3857: , RELEASED_FLAG
3858: , ORG_ID
3859: )
3860: SELECT
3861: OE_ORDER_HOLDS_S.NEXTVAL
3862: , SYSDATE
3863: , l_user_id
3864: , SYSDATE
3865: , l_user_id
3878: and ol.OPEN_FLAG = 'Y'
3879: -- QUOTING change
3880: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3881: and not exists ( select 'x'
3882: from oe_order_holds_ALL oh
3883: where oh.header_id = h.header_id
3884: and oh.line_id = ol.line_id
3885: and oh.hold_source_id =
3886: p_hold_source_rec.hold_source_id );
3887: END IF;
3888: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
3889: p_hold_source_rec.hold_entity_code2 = 'W' THEN
3890: IF p_hold_source_rec.line_id IS NOT NULL THEN
3891: INSERT INTO OE_ORDER_HOLDS_ALL
3892: ( ORDER_HOLD_ID
3893: , LAST_UPDATE_DATE
3894: , LAST_UPDATED_BY
3895: , CREATION_DATE
3901: , RELEASED_FLAG
3902: , ORG_ID
3903: )
3904: SELECT
3905: OE_ORDER_HOLDS_S.NEXTVAL
3906: , SYSDATE
3907: , l_user_id
3908: , SYSDATE
3909: , l_user_id
3923: and ol.OPEN_FLAG = 'Y'
3924: -- QUOTING change
3925: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3926: and not exists ( select 'x'
3927: from oe_order_holds_ALL oh
3928: where oh.header_id = h.header_id
3929: and oh.line_id = ol.line_id
3930: and oh.hold_source_id =
3931: p_hold_source_rec.hold_source_id );
3929: and oh.line_id = ol.line_id
3930: and oh.hold_source_id =
3931: p_hold_source_rec.hold_source_id );
3932: ELSE
3933: INSERT INTO OE_ORDER_HOLDS_ALL
3934: ( ORDER_HOLD_ID
3935: , LAST_UPDATE_DATE
3936: , LAST_UPDATED_BY
3937: , CREATION_DATE
3943: , RELEASED_FLAG
3944: , ORG_ID
3945: )
3946: SELECT
3947: OE_ORDER_HOLDS_S.NEXTVAL
3948: , SYSDATE
3949: , l_user_id
3950: , SYSDATE
3951: , l_user_id
3964: and ol.OPEN_FLAG = 'Y'
3965: -- QUOTING change
3966: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3967: and not exists ( select 'x'
3968: from oe_order_holds_ALL oh
3969: where oh.header_id = h.header_id
3970: and oh.line_id = ol.line_id
3971: and oh.hold_source_id =
3972: p_hold_source_rec.hold_source_id );
3974:
3975: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
3976: p_hold_source_rec.hold_entity_code2 = 'L' THEN
3977: IF p_hold_source_rec.line_id IS NOT NULL THEN
3978: INSERT INTO OE_ORDER_HOLDS_ALL
3979: ( ORDER_HOLD_ID
3980: , LAST_UPDATE_DATE
3981: , LAST_UPDATED_BY
3982: , CREATION_DATE
3988: , RELEASED_FLAG
3989: , ORG_ID
3990: )
3991: SELECT
3992: OE_ORDER_HOLDS_S.NEXTVAL
3993: , SYSDATE
3994: , l_user_id
3995: , SYSDATE
3996: , l_user_id
4010: and ol.OPEN_FLAG = 'Y'
4011: -- QUOTING change
4012: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4013: and not exists ( select 'x'
4014: from oe_order_holds_ALL oh
4015: where oh.header_id = h.header_id
4016: and oh.line_id = ol.line_id
4017: and oh.hold_source_id =
4018: p_hold_source_rec.hold_source_id );
4016: and oh.line_id = ol.line_id
4017: and oh.hold_source_id =
4018: p_hold_source_rec.hold_source_id );
4019: ELSE
4020: INSERT INTO OE_ORDER_HOLDS_ALL
4021: ( ORDER_HOLD_ID
4022: , LAST_UPDATE_DATE
4023: , LAST_UPDATED_BY
4024: , CREATION_DATE
4030: , RELEASED_FLAG
4031: , ORG_ID
4032: )
4033: SELECT
4034: OE_ORDER_HOLDS_S.NEXTVAL
4035: , SYSDATE
4036: , l_user_id
4037: , SYSDATE
4038: , l_user_id
4051: and ol.OPEN_FLAG = 'Y'
4052: -- QUOTING change
4053: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4054: and not exists ( select 'x'
4055: from oe_order_holds_ALL oh
4056: where oh.header_id = h.header_id
4057: and oh.line_id = ol.line_id
4058: and oh.hold_source_id =
4059: p_hold_source_rec.hold_source_id );
4060: END IF;
4061:
4062: ELSIF p_hold_source_rec.hold_entity_code = 'H' THEN
4063: IF p_hold_source_rec.line_id IS NOT NULL THEN
4064: INSERT INTO OE_ORDER_HOLDS_ALL
4065: ( ORDER_HOLD_ID
4066: , LAST_UPDATE_DATE
4067: , LAST_UPDATED_BY
4068: , CREATION_DATE
4074: , RELEASED_FLAG
4075: , ORG_ID
4076: )
4077: SELECT
4078: OE_ORDER_HOLDS_S.NEXTVAL
4079: , SYSDATE
4080: , l_user_id
4081: , SYSDATE
4082: , l_user_id
4095: and ol.OPEN_FLAG = 'Y'
4096: -- QUOTING change
4097: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4098: and not exists ( select 'x'
4099: from oe_order_holds_ALL oh
4100: where oh.header_id = h.header_id
4101: and oh.line_id = ol.line_id
4102: and oh.hold_source_id =
4103: p_hold_source_rec.hold_source_id );
4101: and oh.line_id = ol.line_id
4102: and oh.hold_source_id =
4103: p_hold_source_rec.hold_source_id );
4104: ELSE
4105: INSERT INTO OE_ORDER_HOLDS_ALL
4106: ( ORDER_HOLD_ID
4107: , LAST_UPDATE_DATE
4108: , LAST_UPDATED_BY
4109: , CREATION_DATE
4115: , RELEASED_FLAG
4116: , ORG_ID
4117: )
4118: SELECT
4119: OE_ORDER_HOLDS_S.NEXTVAL
4120: , SYSDATE
4121: , l_user_id
4122: , SYSDATE
4123: , l_user_id
4135: and ol.OPEN_FLAG = 'Y'
4136: -- QUOTING change
4137: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4138: and not exists ( select 'x'
4139: from oe_order_holds_ALL oh
4140: where oh.header_id = h.header_id
4141: and oh.line_id = ol.line_id
4142: and oh.hold_source_id =
4143: p_hold_source_rec.hold_source_id );
4144: END IF;
4145:
4146: ELSIF p_hold_source_rec.hold_entity_code = 'O' THEN
4147: IF p_hold_source_rec.line_id is NULL THEN
4148: INSERT INTO OE_ORDER_HOLDS_ALL
4149: ( ORDER_HOLD_ID
4150: , LAST_UPDATE_DATE
4151: , LAST_UPDATED_BY
4152: , CREATION_DATE
4158: , RELEASED_FLAG
4159: , ORG_ID
4160: )
4161: SELECT
4162: OE_ORDER_HOLDS_S.NEXTVAL
4163: , SYSDATE
4164: , l_user_id
4165: , SYSDATE
4166: , l_user_id
4175: and h.header_id = p_hold_source_rec.hold_entity_id
4176: -- QUOTING change
4177: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4178: and not exists ( select 'x'
4179: from oe_order_holds_ALL oh
4180: where oh.header_id = h.header_id
4181: and oh.hold_source_id =
4182: p_hold_source_rec.hold_source_id );
4183: ELSE
4180: where oh.header_id = h.header_id
4181: and oh.hold_source_id =
4182: p_hold_source_rec.hold_source_id );
4183: ELSE
4184: INSERT INTO OE_ORDER_HOLDS_ALL
4185: ( ORDER_HOLD_ID
4186: , LAST_UPDATE_DATE
4187: , LAST_UPDATED_BY
4188: , CREATION_DATE
4194: , RELEASED_FLAG
4195: , ORG_ID
4196: )
4197: SELECT
4198: OE_ORDER_HOLDS_S.NEXTVAL
4199: , SYSDATE
4200: , l_user_id
4201: , SYSDATE
4202: , l_user_id
4214: and ol.open_flag = 'Y'
4215: -- QUOTING change
4216: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4217: and not exists ( select 'x'
4218: from oe_order_holds_ALL oh
4219: where oh.header_id = h.header_id
4220: and oh.line_id = ol.line_id
4221: and oh.hold_source_id =
4222: p_hold_source_rec.hold_source_id );
4224:
4225: ELSIF p_hold_source_rec.hold_entity_code = 'C' THEN
4226: -- Use header_id for Customer based hold source
4227: IF p_hold_source_rec.header_id IS NOT NULL THEN
4228: INSERT INTO OE_ORDER_HOLDS_ALL
4229: ( ORDER_HOLD_ID
4230: , LAST_UPDATE_DATE
4231: , LAST_UPDATED_BY
4232: , CREATION_DATE
4238: , RELEASED_FLAG
4239: , ORG_ID
4240: )
4241: SELECT
4242: OE_ORDER_HOLDS_S.NEXTVAL
4243: , SYSDATE
4244: , l_user_id
4245: , SYSDATE
4246: , l_user_id
4257: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
4258: -- QUOTING change
4259: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4260: and not exists ( select 'x'
4261: from oe_order_holds_ALL oh
4262: where oh.header_id = h.header_id
4263: and oh.hold_source_id =
4264: p_hold_source_rec.hold_source_id );
4265: ELSE
4262: where oh.header_id = h.header_id
4263: and oh.hold_source_id =
4264: p_hold_source_rec.hold_source_id );
4265: ELSE
4266: INSERT INTO OE_ORDER_HOLDS_ALL
4267: ( ORDER_HOLD_ID
4268: , LAST_UPDATE_DATE
4269: , LAST_UPDATED_BY
4270: , CREATION_DATE
4276: , RELEASED_FLAG
4277: , ORG_ID
4278: )
4279: SELECT
4280: OE_ORDER_HOLDS_S.NEXTVAL
4281: , SYSDATE
4282: , l_user_id
4283: , SYSDATE
4284: , l_user_id
4294: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
4295: -- QUOTING change
4296: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4297: and not exists ( select 'x'
4298: from oe_order_holds_ALL oh
4299: where oh.header_id = h.header_id
4300: and oh.hold_source_id =
4301: p_hold_source_rec.hold_source_id );
4302: END IF;
4301: p_hold_source_rec.hold_source_id );
4302: END IF;
4303: ELSIF p_hold_source_rec.hold_entity_code = 'B' THEN
4304: IF p_hold_source_rec.line_id IS NOT NULL THEN
4305: INSERT INTO OE_ORDER_HOLDS_ALL
4306: ( ORDER_HOLD_ID
4307: , LAST_UPDATE_DATE
4308: , LAST_UPDATED_BY
4309: , CREATION_DATE
4315: , RELEASED_FLAG
4316: , ORG_ID
4317: )
4318: SELECT
4319: OE_ORDER_HOLDS_S.NEXTVAL
4320: , SYSDATE
4321: , l_user_id
4322: , SYSDATE
4323: , l_user_id
4336: and ol.OPEN_FLAG = 'Y'
4337: -- QUOTING change
4338: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4339: and not exists ( select 'x'
4340: from oe_order_holds_ALL oh
4341: where oh.header_id = h.header_id
4342: and oh.line_id = ol.line_id
4343: and oh.hold_source_id =
4344: p_hold_source_rec.hold_source_id );
4342: and oh.line_id = ol.line_id
4343: and oh.hold_source_id =
4344: p_hold_source_rec.hold_source_id );
4345: ELSE
4346: INSERT INTO OE_ORDER_HOLDS_ALL
4347: ( ORDER_HOLD_ID
4348: , LAST_UPDATE_DATE
4349: , LAST_UPDATED_BY
4350: , CREATION_DATE
4356: , RELEASED_FLAG
4357: , ORG_ID
4358: )
4359: SELECT
4360: OE_ORDER_HOLDS_S.NEXTVAL
4361: , SYSDATE
4362: , l_user_id
4363: , SYSDATE
4364: , l_user_id
4376: and ol.OPEN_FLAG = 'Y'
4377: -- QUOTING change
4378: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4379: and not exists ( select 'x'
4380: from oe_order_holds_ALL oh
4381: where oh.header_id = h.header_id
4382: and oh.line_id = ol.line_id
4383: and oh.hold_source_id =
4384: p_hold_source_rec.hold_source_id );
4384: p_hold_source_rec.hold_source_id );
4385: END IF;
4386: ELSIF p_hold_source_rec.hold_entity_code = 'S' THEN
4387: IF p_hold_source_rec.line_id IS NOT NULL THEN
4388: INSERT INTO OE_ORDER_HOLDS_ALL
4389: ( ORDER_HOLD_ID
4390: , LAST_UPDATE_DATE
4391: , LAST_UPDATED_BY
4392: , CREATION_DATE
4398: , RELEASED_FLAG
4399: , ORG_ID
4400: )
4401: SELECT
4402: OE_ORDER_HOLDS_S.NEXTVAL
4403: , SYSDATE
4404: , l_user_id
4405: , SYSDATE
4406: , l_user_id
4419: and ol.OPEN_FLAG = 'Y'
4420: -- QUOTING change
4421: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4422: and not exists ( select 'x'
4423: from oe_order_holds_ALL oh
4424: where oh.header_id = h.header_id
4425: and oh.line_id = ol.line_id
4426: and oh.hold_source_id =
4427: p_hold_source_rec.hold_source_id );
4425: and oh.line_id = ol.line_id
4426: and oh.hold_source_id =
4427: p_hold_source_rec.hold_source_id );
4428: ELSE
4429: INSERT INTO OE_ORDER_HOLDS_ALL
4430: ( ORDER_HOLD_ID
4431: , LAST_UPDATE_DATE
4432: , LAST_UPDATED_BY
4433: , CREATION_DATE
4439: , RELEASED_FLAG
4440: , ORG_ID
4441: )
4442: SELECT
4443: OE_ORDER_HOLDS_S.NEXTVAL
4444: , SYSDATE
4445: , l_user_id
4446: , SYSDATE
4447: , l_user_id
4459: and ol.OPEN_FLAG = 'Y'
4460: -- QUOTING change
4461: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4462: and not exists ( select 'x'
4463: from oe_order_holds_ALL oh
4464: where oh.header_id = h.header_id
4465: and oh.line_id = ol.line_id
4466: and oh.hold_source_id =
4467: p_hold_source_rec.hold_source_id );
4467: p_hold_source_rec.hold_source_id );
4468: END IF;
4469: ELSIF p_hold_source_rec.hold_entity_code = 'W' THEN
4470: IF p_hold_source_rec.line_id IS NOT NULL THEN
4471: INSERT INTO OE_ORDER_HOLDS_ALL
4472: ( ORDER_HOLD_ID
4473: , LAST_UPDATE_DATE
4474: , LAST_UPDATED_BY
4475: , CREATION_DATE
4481: , RELEASED_FLAG
4482: , ORG_ID
4483: )
4484: SELECT
4485: OE_ORDER_HOLDS_S.NEXTVAL
4486: , SYSDATE
4487: , l_user_id
4488: , SYSDATE
4489: , l_user_id
4502: and ol.OPEN_FLAG = 'Y'
4503: -- QUOTING change
4504: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4505: and not exists ( select 'x'
4506: from oe_order_holds_ALL oh
4507: where oh.header_id = h.header_id
4508: and oh.line_id = ol.line_id
4509: and oh.hold_source_id =
4510: p_hold_source_rec.hold_source_id );
4508: and oh.line_id = ol.line_id
4509: and oh.hold_source_id =
4510: p_hold_source_rec.hold_source_id );
4511: ELSE
4512: INSERT INTO OE_ORDER_HOLDS_ALL
4513: ( ORDER_HOLD_ID
4514: , LAST_UPDATE_DATE
4515: , LAST_UPDATED_BY
4516: , CREATION_DATE
4522: , RELEASED_FLAG
4523: , ORG_ID
4524: )
4525: SELECT
4526: OE_ORDER_HOLDS_S.NEXTVAL
4527: , SYSDATE
4528: , l_user_id
4529: , SYSDATE
4530: , l_user_id
4542: and ol.OPEN_FLAG = 'Y'
4543: -- QUOTING change
4544: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4545: and not exists ( select 'x'
4546: from oe_order_holds_ALL oh
4547: where oh.header_id = h.header_id
4548: and oh.line_id = ol.line_id
4549: and oh.hold_source_id =
4550: p_hold_source_rec.hold_source_id );
4550: p_hold_source_rec.hold_source_id );
4551: END IF;
4552: ELSIF p_hold_source_rec.hold_entity_code = 'I' THEN
4553: IF p_hold_source_rec.line_id IS NOT NULL THEN
4554: INSERT INTO OE_ORDER_HOLDS_ALL
4555: ( ORDER_HOLD_ID
4556: , LAST_UPDATE_DATE
4557: , LAST_UPDATED_BY
4558: , CREATION_DATE
4564: , RELEASED_FLAG
4565: , ORG_ID
4566: )
4567: SELECT
4568: OE_ORDER_HOLDS_S.NEXTVAL
4569: , SYSDATE
4570: , l_user_id
4571: , SYSDATE
4572: , l_user_id
4585: and ol.OPEN_FLAG = 'Y'
4586: -- QUOTING change
4587: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4588: and not exists ( select 'x'
4589: from oe_order_holds_ALL oh
4590: where oh.header_id = h.header_id
4591: and oh.line_id = ol.line_id
4592: and oh.hold_source_id =
4593: p_hold_source_rec.hold_source_id );
4591: and oh.line_id = ol.line_id
4592: and oh.hold_source_id =
4593: p_hold_source_rec.hold_source_id );
4594: ELSE
4595: INSERT INTO OE_ORDER_HOLDS_ALL
4596: ( ORDER_HOLD_ID
4597: , LAST_UPDATE_DATE
4598: , LAST_UPDATED_BY
4599: , CREATION_DATE
4605: , RELEASED_FLAG
4606: , ORG_ID
4607: )
4608: SELECT
4609: OE_ORDER_HOLDS_S.NEXTVAL
4610: , SYSDATE
4611: , l_user_id
4612: , SYSDATE
4613: , l_user_id
4625: and ol.OPEN_FLAG = 'Y'
4626: -- QUOTING change
4627: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4628: and not exists ( select 'x'
4629: from oe_order_holds_ALL oh
4630: where oh.header_id = h.header_id
4631: and oh.line_id = ol.line_id
4632: and oh.hold_source_id =
4633: p_hold_source_rec.hold_source_id );
4803: x_return_status := FND_API.G_RET_STS_SUCCESS;
4804: l_user_id := OE_HOLDS_PVT.get_user_id;
4805: -- Flag all orders and order line holds for this hold source
4806: -- as released
4807: UPDATE oe_order_holds
4808: SET hold_release_id = p_hold_release_rec.hold_release_id,
4809: LAST_UPDATED_BY = l_user_id,
4810: LAST_UPDATE_DATE = sysdate,
4811: RELEASED_FLAG = 'Y'
5023: -- nvl(p_hold_source_rec.hold_entity_code2, 'NO_ENTITY_CODE2')
5024: -- AND nvl(HS.HOLD_ENTITY_ID2, -99) =
5025: -- nvl(p_hold_source_rec.hold_entity_id2, -99)
5026: AND EXISTS (select 1
5027: from oe_order_holds oh
5028: where oh.hold_source_id = hs.hold_source_id
5029: and oh.header_id =
5030: nvl(p_hold_source_rec.header_id, oh.header_id)
5031: and nvl(oh.line_id, -99) =
5200:
5201:
5202: ----------------------------------------------------------------------------
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
5222: l_hold_source_id NUMBER;
5223: l_hold_release_id NUMBER := 0;
5224: CURSOR order_hold IS
5225: SELECT order_hold_id, NVL(hold_release_id,0)
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)
5235: FROM OE_ORDER_HOLDS_all
5236: WHERE LINE_ID = p_order_rec.line_id;
5237:
5238: BEGIN
5239: -- Standard Start of API savepoint
5253: RAISE FND_API.G_EXC_ERROR;
5254:
5255: END IF;
5256:
5257: -- Delete the hold records corr. to this order or line in OE_ORDER_HOLDS
5258:
5259: IF p_order_rec.line_id = FND_API.G_MISS_NUM THEN
5260:
5261: -- Delete order hold records
5269: END IF;
5270:
5271: OE_Debug_PUB.Add('Deleting order hold record',1);
5272:
5273: DELETE FROM OE_ORDER_HOLDS_all
5274: WHERE order_hold_id = l_order_hold_id;
5275:
5276: OE_Debug_PUB.Add('Deleting hold release record',1);
5277: DELETE FROM OE_HOLD_RELEASES
5276: OE_Debug_PUB.Add('Deleting hold release record',1);
5277: DELETE FROM OE_HOLD_RELEASES
5278: WHERE HOLD_RELEASE_ID = l_hold_release_id
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: );
5302: DELETE FROM OE_HOLD_RELEASES
5303: WHERE HOLD_RELEASE_ID = l_hold_release_id
5304: AND HOLD_RELEASE_ID NOT IN
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: );
5328: END IF;
5329:
5330: OE_Debug_PUB.Add('Deleting order hold record',1);
5331:
5332: DELETE FROM OE_ORDER_HOLDS
5333: WHERE order_hold_id = l_order_hold_id;
5334:
5335: DELETE FROM OE_HOLD_RELEASES
5336: WHERE HOLD_RELEASE_ID = l_hold_release_id
5335: DELETE FROM OE_HOLD_RELEASES
5336: WHERE HOLD_RELEASE_ID = l_hold_release_id
5337: AND HOLD_RELEASE_ID NOT IN
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: );
5805: -- When release holds for orders, check to see if this order was put on
5806: -- hold as an
5807: -- Order based hold. If yes, then release the Hold_Source from hold and
5808: -- also order
5809: -- from hold in oe_order_holds and insert record in oe_hold_releases with
5810: -- order_hold_id(No with Hold Source ID).
5811: -- IF the order being releasesed is part of a different hold source (e.g. 'C')
5812: -- then don't release the hold source(what if its the last order)
5813: -- and only release the order from hold in
5810: -- order_hold_id(No with Hold Source ID).
5811: -- IF the order being releasesed is part of a different hold source (e.g. 'C')
5812: -- then don't release the hold source(what if its the last order)
5813: -- and only release the order from hold in
5814: -- OE_ORDER_HOLDS and create a record in OE_HOLD_RELEASES with the
5815: -- Order_Hold_ID.
5816: Validate_Order (p_order_tbl(j).header_id,
5817: p_order_tbl(j).line_id,
5818: x_return_status => x_return_status,
5849: IF entity_code = 'O' THEN
5850: BEGIN
5851: SELECT 'NO HOLDS'
5852: INTO l_dummy
5853: FROM OE_ORDER_HOLDS
5854: WHERE HOLD_SOURCE_ID = l_hold_source_id
5855: AND HOLD_RELEASE_ID IS NULL
5856: AND ROWNUM = 1;
5857:
7802: , hs.ATTRIBUTE13
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;
7860: l_hold_source_id := c_rec.hold_source_id;
7861: --l_hold_source_rec.hold_source_id := c_rec.hold_source_id;
7862: END IF;
7863:
7864: INSERT INTO OE_ORDER_HOLDS_all
7865: ( ORDER_HOLD_ID
7866: , LAST_UPDATE_DATE
7867: , LAST_UPDATED_BY
7868: , CREATION_DATE
7874: , RELEASED_FLAG
7875: , ORG_ID
7876: )
7877: VALUES (
7878: OE_ORDER_HOLDS_S.NEXTVAL
7879: , SYSDATE
7880: , l_user_id
7881: , SYSDATE
7882: , l_user_id
8062: CURSOR check_line_hold_type_cur(p_line_id IN NUMBER) IS
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
8079: CURSOR check_hdr_hold_type_cur(p_hdr_id IN NUMBER) IS
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
8108: CURSOR check_line_hold_type_cur(p_line_id IN NUMBER) IS
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
8127: CURSOR check_hdr_hold_type_cur(p_hdr_id IN NUMBER) IS
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
8222:
8223:
8224: /*ER#7479609 start
8225: IF p_hold_source_rec.line_id IS NOT NULL THEN
8226: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
8227: ( ORDER_HOLD_ID
8228: , LAST_UPDATE_DATE
8229: , LAST_UPDATED_BY
8230: , CREATION_DATE
8236: , RELEASED_FLAG
8237: , ORG_ID
8238: )
8239: SELECT
8240: OE_ORDER_HOLDS_S.NEXTVAL
8241: , SYSDATE
8242: , :l_user_id
8243: , SYSDATE
8244: , :l_user_id
8259: and ol.OPEN_FLAG = ''Y''
8260: -- QUOTING change
8261: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
8262: and not exists ( select ''x''
8263: from oe_order_holds_ALL oh
8264: where oh.header_id = h.header_id
8265: and oh.line_id = ol.line_id
8266: and oh.hold_source_id = :hold_source_id )';
8267: IF p_item_type is not null and p_activity_name is not null then
8299: END IF;
8300: END IF;
8301:
8302: ELSE
8303: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
8304: ( ORDER_HOLD_ID
8305: , LAST_UPDATE_DATE
8306: , LAST_UPDATED_BY
8307: , CREATION_DATE
8313: , RELEASED_FLAG
8314: , ORG_ID
8315: )
8316: SELECT
8317: OE_ORDER_HOLDS_S.NEXTVAL
8318: , SYSDATE
8319: , :l_user_id
8320: , SYSDATE
8321: , :l_user_id
8335: and ol.OPEN_FLAG = ''Y''
8336: -- QUOTING change
8337: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
8338: and not exists ( select ''x''
8339: from oe_order_holds_ALL oh
8340: where oh.header_id = h.header_id
8341: and oh.line_id = ol.line_id
8342: and oh.hold_source_id =:hold_source_id )';
8343:
8392: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
8393: and ol.OPEN_FLAG = 'Y'
8394: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
8395: and not exists ( select 'x'
8396: from oe_order_holds_ALL oh
8397: where oh.header_id = h.header_id
8398: and oh.line_id = ol.line_id
8399: and oh.hold_source_id =
8400: p_hold_source_rec.hold_source_id );
8548: --ER#7479609 end
8549:
8550: /*ER#7479609 start
8551: IF p_hold_source_rec.line_id IS NOT NULL THEN
8552: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
8553: ( ORDER_HOLD_ID
8554: , LAST_UPDATE_DATE
8555: , LAST_UPDATED_BY
8556: , CREATION_DATE
8562: , RELEASED_FLAG
8563: , ORG_ID
8564: )
8565: SELECT
8566: OE_ORDER_HOLDS_S.NEXTVAL
8567: , SYSDATE
8568: , :l_user_id
8569: , SYSDATE
8570: , :l_user_id
8586: and ol.PAYMENT_TERM_ID = rt.TERM_ID
8587: and rt.CREDIT_CHECK_FLAG = ''Y''
8588: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
8589: and not exists ( select ''x''
8590: from oe_order_holds_ALL oh
8591: where oh.header_id = h.header_id
8592: and oh.line_id = ol.line_id
8593: and oh.hold_source_id =:hold_source_id )';
8594: IF p_item_type is not null and p_activity_name is not null then
8620: END IF;
8621: END IF;
8622:
8623: ELSE
8624: l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL
8625: ( ORDER_HOLD_ID
8626: , LAST_UPDATE_DATE
8627: , LAST_UPDATED_BY
8628: , CREATION_DATE
8634: , RELEASED_FLAG
8635: , ORG_ID
8636: )
8637: SELECT
8638: OE_ORDER_HOLDS_S.NEXTVAL
8639: , SYSDATE
8640: , :l_user_id
8641: , SYSDATE
8642: , :l_user_id
8657: and ol.PAYMENT_TERM_ID = rt.TERM_ID
8658: and rt.CREDIT_CHECK_FLAG = ''Y''
8659: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
8660: and not exists ( select ''x''
8661: from oe_order_holds_ALL oh
8662: where oh.header_id = h.header_id
8663: and oh.line_id = ol.line_id
8664: and oh.hold_source_id =:hold_source_id )';
8665: IF p_item_type is not null and p_activity_name is not null then
8716: and ol.PAYMENT_TERM_ID = rt.TERM_ID
8717: and rt.CREDIT_CHECK_FLAG = 'Y'
8718: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
8719: and not exists ( select 'x'
8720: from oe_order_holds_ALL oh
8721: where oh.header_id = h.header_id
8722: and oh.line_id = ol.line_id
8723: and oh.hold_source_id =
8724: p_hold_source_rec.hold_source_id );
8765: --ER#7479609 end
8766:
8767: /*ER#7479609 start
8768: IF p_hold_source_rec.line_id IS NOT NULL THEN
8769: l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL
8770: ( ORDER_HOLD_ID
8771: , LAST_UPDATE_DATE
8772: , LAST_UPDATED_BY
8773: , CREATION_DATE
8779: , RELEASED_FLAG
8780: , ORG_ID
8781: )
8782: SELECT
8783: OE_ORDER_HOLDS_S.NEXTVAL
8784: , SYSDATE
8785: , :l_user_id
8786: , SYSDATE
8787: , :l_user_id
8801: and ol.INVENTORY_ITEM_ID = :hold_entity_id
8802: and ol.OPEN_FLAG = ''Y''
8803: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
8804: and not exists ( select ''x''
8805: from oe_order_holds_ALL oh
8806: where oh.header_id = h.header_id
8807: and oh.line_id = ol.line_id
8808: and oh.hold_source_id = :hold_source_id )';
8809: IF p_item_type is not null and p_activity_name is not null then
8839: x_is_hold_applied := TRUE;
8840: END IF;
8841: END IF;
8842: ELSE
8843: l_sqlmt := 'Insert into oe_order_holds_all
8844: ( ORDER_HOLD_ID
8845: , LAST_UPDATE_DATE
8846: , LAST_UPDATED_BY
8847: , CREATION_DATE
8853: , RELEASED_FLAG
8854: , ORG_ID
8855: )
8856: SELECT
8857: OE_ORDER_HOLDS_S.NEXTVAL
8858: , SYSDATE
8859: , :l_user_id
8860: , SYSDATE
8861: , :l_user_id
8874: and ol.INVENTORY_ITEM_ID = :hold_entity_id
8875: and ol.OPEN_FLAG = ''Y''
8876: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
8877: and not exists ( select ''x''
8878: from oe_order_holds_ALL oh
8879: where oh.header_id = h.header_id
8880: and oh.line_id = ol.line_id
8881: and oh.hold_source_id = :hold_source_id )';
8882: IF p_item_type is not null and p_activity_name is not null then
8931: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
8932: and ol.OPEN_FLAG = 'Y'
8933: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
8934: and not exists ( select 'x'
8935: from oe_order_holds_ALL oh
8936: where oh.header_id = h.header_id
8937: and oh.line_id = ol.line_id
8938: and oh.hold_source_id =
8939: p_hold_source_rec.hold_source_id );
8980: --ER#7479609 end
8981:
8982: /*ER#7479609 start
8983: IF p_hold_source_rec.line_id IS NOT NULL THEN
8984: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
8985: ( ORDER_HOLD_ID
8986: , LAST_UPDATE_DATE
8987: , LAST_UPDATED_BY
8988: , CREATION_DATE
8994: , RELEASED_FLAG
8995: , ORG_ID
8996: )
8997: SELECT
8998: OE_ORDER_HOLDS_S.NEXTVAL
8999: , SYSDATE
9000: , :l_user_id
9001: , SYSDATE
9002: , :l_user_id
9016: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9017: and ol.OPEN_FLAG = ''Y''
9018: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9019: and not exists ( select ''x''
9020: from oe_order_holds_ALL oh
9021: where oh.header_id = h.header_id
9022: and oh.line_id = ol.line_id
9023: and oh.hold_source_id =:hold_source_id )';
9024: IF p_item_type is not null and p_activity_name is not null then
9053: x_is_hold_applied := TRUE;
9054: END IF;
9055: END IF;
9056: ELSE
9057: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9058: ( ORDER_HOLD_ID
9059: , LAST_UPDATE_DATE
9060: , LAST_UPDATED_BY
9061: , CREATION_DATE
9067: , RELEASED_FLAG
9068: , ORG_ID
9069: )
9070: SELECT
9071: OE_ORDER_HOLDS_S.NEXTVAL
9072: , SYSDATE
9073: , :l_user_id
9074: , SYSDATE
9075: , :l_user_id
9088: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9089: and ol.OPEN_FLAG = ''Y''
9090: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9091: and not exists ( select ''x''
9092: from oe_order_holds_ALL oh
9093: where oh.header_id = h.header_id
9094: and oh.line_id = ol.line_id
9095: and oh.hold_source_id =:hold_source_id )';
9096: IF p_item_type is not null and p_activity_name is not null then
9143: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
9144: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9145: and ol.OPEN_FLAG = 'Y' and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
9146: and not exists ( select 'x'
9147: from oe_order_holds_ALL oh
9148: where oh.header_id = h.header_id
9149: and oh.line_id = ol.line_id
9150: and oh.hold_source_id =
9151: p_hold_source_rec.hold_source_id );
9193: --ER#7479609 end
9194:
9195: /*ER#7479609 start
9196: IF p_hold_source_rec.line_id IS NOT NULL THEN
9197: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9198: ( ORDER_HOLD_ID
9199: , LAST_UPDATE_DATE
9200: , LAST_UPDATED_BY
9201: , CREATION_DATE
9207: , RELEASED_FLAG
9208: , ORG_ID
9209: )
9210: SELECT
9211: OE_ORDER_HOLDS_S.NEXTVAL
9212: , SYSDATE
9213: , :l_user_id
9214: , SYSDATE
9215: , :l_user_id
9229: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9230: and ol.OPEN_FLAG = ''Y''
9231: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9232: and not exists ( select ''x''
9233: from oe_order_holds_ALL oh
9234: where oh.header_id = h.header_id
9235: and oh.line_id = ol.line_id
9236: and oh.hold_source_id =:hold_source_id )';
9237: IF p_item_type is not null and p_activity_name is not null then
9267: x_is_hold_applied := TRUE;
9268: END IF;
9269: END IF;
9270: ELSE
9271: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9272: ( ORDER_HOLD_ID
9273: , LAST_UPDATE_DATE
9274: , LAST_UPDATED_BY
9275: , CREATION_DATE
9281: , RELEASED_FLAG
9282: , ORG_ID
9283: )
9284: SELECT
9285: OE_ORDER_HOLDS_S.NEXTVAL
9286: , SYSDATE
9287: , :l_user_id
9288: , SYSDATE
9289: , :l_user_id
9302: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9303: and ol.OPEN_FLAG = ''Y''
9304: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9305: and not exists ( select ''x''
9306: from oe_order_holds_ALL oh
9307: where oh.header_id = h.header_id
9308: and oh.line_id = ol.line_id
9309: and oh.hold_source_id =:hold_source_id )';
9310: IF p_item_type is not null and p_activity_name is not null then
9358: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9359: and ol.OPEN_FLAG = 'Y'
9360: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
9361: and not exists ( select 'x'
9362: from oe_order_holds_ALL oh
9363: where oh.header_id = h.header_id
9364: and oh.line_id = ol.line_id
9365: and oh.hold_source_id =
9366: p_hold_source_rec.hold_source_id );
9408: --ER#7479609 end
9409:
9410: /*ER#7479609 start
9411: IF p_hold_source_rec.line_id IS NOT NULL THEN
9412: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9413: ( ORDER_HOLD_ID
9414: , LAST_UPDATE_DATE
9415: , LAST_UPDATED_BY
9416: , CREATION_DATE
9422: , RELEASED_FLAG
9423: , ORG_ID
9424: )
9425: SELECT
9426: OE_ORDER_HOLDS_S.NEXTVAL
9427: , SYSDATE
9428: , :l_user_id
9429: , SYSDATE
9430: , :l_user_id
9444: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9445: and ol.OPEN_FLAG = ''Y''
9446: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9447: and not exists ( select ''x''
9448: from oe_order_holds_ALL oh
9449: where oh.header_id = h.header_id
9450: and oh.line_id = ol.line_id
9451: and oh.hold_source_id =:hold_source_id )';
9452: IF p_item_type is not null and p_activity_name is not null then
9481: x_is_hold_applied := TRUE;
9482: END IF;
9483: END IF;
9484: ELSE
9485: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9486: ( ORDER_HOLD_ID
9487: , LAST_UPDATE_DATE
9488: , LAST_UPDATED_BY
9489: , CREATION_DATE
9495: , RELEASED_FLAG
9496: , ORG_ID
9497: )
9498: SELECT
9499: OE_ORDER_HOLDS_S.NEXTVAL
9500: , SYSDATE
9501: , :l_user_id
9502: , SYSDATE
9503: , :l_user_id
9516: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9517: and ol.OPEN_FLAG = ''Y''
9518: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9519: and not exists ( select ''x''
9520: from oe_order_holds_ALL oh
9521: where oh.header_id = h.header_id
9522: and oh.line_id = ol.line_id
9523: and oh.hold_source_id =:hold_source_id )';
9524: IF p_item_type is not null and p_activity_name is not null then
9572: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9573: and ol.OPEN_FLAG = 'Y'
9574: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
9575: and not exists ( select 'x'
9576: from oe_order_holds_ALL oh
9577: where oh.header_id = h.header_id
9578: and oh.line_id = ol.line_id
9579: and oh.hold_source_id =
9580: p_hold_source_rec.hold_source_id );
9625: --ER#7479609 end
9626:
9627: /*ER#7479609 start
9628: IF p_hold_source_rec.line_id IS NOT NULL THEN
9629: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9630: ( ORDER_HOLD_ID
9631: , LAST_UPDATE_DATE
9632: , LAST_UPDATED_BY
9633: , CREATION_DATE
9639: , RELEASED_FLAG
9640: , ORG_ID
9641: )
9642: SELECT
9643: OE_ORDER_HOLDS_S.NEXTVAL
9644: , SYSDATE
9645: , :l_user_id
9646: , SYSDATE
9647: , :l_user_id
9661: and h.SOLD_TO_ORG_ID = :hold_entity_id2
9662: and ol.OPEN_FLAG = ''Y''
9663: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9664: and not exists ( select ''x''
9665: from oe_order_holds_ALL oh
9666: where oh.header_id = h.header_id
9667: and oh.line_id = ol.line_id
9668: and oh.hold_source_id =:hold_source_id )';
9669: IF p_item_type is not null and p_activity_name is not null then
9698: x_is_hold_applied := TRUE;
9699: END IF;
9700: END IF;
9701: ELSE
9702: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9703: ( ORDER_HOLD_ID
9704: , LAST_UPDATE_DATE
9705: , LAST_UPDATED_BY
9706: , CREATION_DATE
9712: , RELEASED_FLAG
9713: , ORG_ID
9714: )
9715: SELECT
9716: OE_ORDER_HOLDS_S.NEXTVAL
9717: , SYSDATE
9718: , :l_user_id
9719: , SYSDATE
9720: , :l_user_id
9734: and ol.OPEN_FLAG = ''Y''
9735: -- QUOTING change
9736: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9737: and not exists ( select ''x''
9738: from oe_order_holds_ALL oh
9739: where oh.header_id = h.header_id
9740: and oh.line_id = ol.line_id
9741: and oh.hold_source_id =:hold_source_id )';
9742: IF p_item_type is not null and p_activity_name is not null then
9790: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
9791: and ol.OPEN_FLAG = 'Y'
9792: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
9793: and not exists ( select 'x'
9794: from oe_order_holds_ALL oh
9795: where oh.header_id = h.header_id
9796: and oh.line_id = ol.line_id
9797: and oh.hold_source_id =
9798: p_hold_source_rec.hold_source_id );
9929: --ER#7479609 end
9930:
9931: /*ER#7479609 start
9932: IF p_hold_source_rec.line_id IS NOT NULL THEN
9933: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9934: ( ORDER_HOLD_ID
9935: , LAST_UPDATE_DATE
9936: , LAST_UPDATED_BY
9937: , CREATION_DATE
9943: , RELEASED_FLAG
9944: , ORG_ID
9945: )
9946: SELECT
9947: OE_ORDER_HOLDS_S.NEXTVAL
9948: , SYSDATE
9949: , :l_user_id
9950: , SYSDATE
9951: , :l_user_id
9966: and ol.OPEN_FLAG = ''Y''
9967: -- QUOTING change
9968: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9969: AND NOT EXISTS ( select ''x''
9970: FROM oe_order_holds_ALL oh
9971: WHERE oh.header_id = h.header_id
9972: AND oh.line_id = ol.line_id
9973: AND oh.hold_source_id =:hold_source_id )';
9974: IF p_item_type is not null and p_activity_name is not null then
10003: x_is_hold_applied := TRUE;
10004: END IF;
10005: END IF;
10006: ELSE
10007: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10008: ( ORDER_HOLD_ID
10009: , LAST_UPDATE_DATE
10010: , LAST_UPDATED_BY
10011: , CREATION_DATE
10017: , RELEASED_FLAG
10018: , ORG_ID
10019: )
10020: SELECT
10021: OE_ORDER_HOLDS_S.NEXTVAL
10022: , SYSDATE
10023: , :l_user_id
10024: , SYSDATE
10025: , :l_user_id
10039: and ol.OPEN_FLAG = ''Y''
10040: -- QUOTING change
10041: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10042: AND NOT EXISTS ( select ''x''
10043: FROM oe_order_holds_ALL oh
10044: WHERE oh.header_id = h.header_id
10045: AND oh.line_id = ol.line_id
10046: AND oh.hold_source_id =:hold_source_id )';
10047: IF p_item_type is not null and p_activity_name is not null then
10095: AND ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
10096: and ol.OPEN_FLAG = 'Y'
10097: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
10098: and not exists ( select 'x'
10099: from oe_order_holds_ALL oh
10100: where oh.header_id = h.header_id
10101: and oh.line_id = ol.line_id
10102: and oh.hold_source_id =
10103: p_hold_source_rec.hold_source_id );
10145: --ER#7479609 end
10146:
10147: /*ER#7479609 start
10148: IF p_hold_source_rec.line_id IS NOT NULL THEN
10149: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10150: ( ORDER_HOLD_ID
10151: , LAST_UPDATE_DATE
10152: , LAST_UPDATED_BY
10153: , CREATION_DATE
10159: , RELEASED_FLAG
10160: , ORG_ID
10161: )
10162: SELECT
10163: OE_ORDER_HOLDS_S.NEXTVAL
10164: , SYSDATE
10165: , :l_user_id
10166: , SYSDATE
10167: , :l_user_id
10181: and ol.OPEN_FLAG = ''Y''
10182: -- QUOTING change
10183: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10184: AND NOT EXISTS ( select ''x''
10185: FROM oe_order_holds_ALL oh
10186: WHERE oh.header_id = h.header_id
10187: AND oh.line_id = ol.line_id
10188: AND oh.hold_source_id =:hold_source_id )';
10189: IF p_item_type is not null and p_activity_name is not null then
10218: x_is_hold_applied := TRUE;
10219: END IF;
10220: END IF;
10221: ELSE
10222: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10223: ( ORDER_HOLD_ID
10224: , LAST_UPDATE_DATE
10225: , LAST_UPDATED_BY
10226: , CREATION_DATE
10232: , RELEASED_FLAG
10233: , ORG_ID
10234: )
10235: SELECT
10236: OE_ORDER_HOLDS_S.NEXTVAL
10237: , SYSDATE
10238: , :l_user_id
10239: , SYSDATE
10240: , :l_user_id
10253: and ol.OPEN_FLAG = ''Y''
10254: -- QUOTING change
10255: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10256: AND NOT EXISTS ( select ''x''
10257: FROM oe_order_holds_ALL oh
10258: WHERE oh.header_id = h.header_id
10259: AND oh.line_id = ol.line_id
10260: AND oh.hold_source_id =:hold_source_id )';
10261: IF p_item_type is not null and p_activity_name is not null then
10309: AND ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
10310: and ol.OPEN_FLAG = 'Y'
10311: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
10312: and not exists ( select 'x'
10313: from oe_order_holds_ALL oh
10314: where oh.header_id = h.header_id
10315: and oh.line_id = ol.line_id
10316: and oh.hold_source_id =
10317: p_hold_source_rec.hold_source_id );
10362: --ER#7479609 end
10363:
10364: /*ER#7479609 start
10365: IF p_hold_source_rec.line_id IS NOT NULL THEN
10366: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10367: ( ORDER_HOLD_ID
10368: , LAST_UPDATE_DATE
10369: , LAST_UPDATED_BY
10370: , CREATION_DATE
10376: , RELEASED_FLAG
10377: , ORG_ID
10378: )
10379: SELECT
10380: OE_ORDER_HOLDS_S.NEXTVAL
10381: , SYSDATE
10382: , :l_user_id
10383: , SYSDATE
10384: , :l_user_id
10398: and ol.OPEN_FLAG = ''Y''
10399: -- QUOTING change
10400: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10401: and not exists ( select ''x''
10402: from oe_order_holds_ALL oh
10403: where oh.header_id = h.header_id
10404: and oh.line_id = ol.line_id
10405: and oh.hold_source_id =:hold_source_id )';
10406: IF p_item_type is not null and p_activity_name is not null then
10435: x_is_hold_applied := TRUE;
10436: END IF;
10437: END IF;
10438: ELSE
10439: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10440: ( ORDER_HOLD_ID
10441: , LAST_UPDATE_DATE
10442: , LAST_UPDATED_BY
10443: , CREATION_DATE
10449: , RELEASED_FLAG
10450: , ORG_ID
10451: )
10452: SELECT
10453: OE_ORDER_HOLDS_S.NEXTVAL
10454: , SYSDATE
10455: , :l_user_id
10456: , SYSDATE
10457: , :l_user_id
10470: and ol.OPEN_FLAG = ''Y''
10471: -- QUOTING change
10472: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10473: and not exists ( select ''x''
10474: from oe_order_holds_ALL oh
10475: where oh.header_id = h.header_id
10476: and oh.line_id = ol.line_id
10477: and oh.hold_source_id =:hold_source_id )';
10478: IF p_item_type is not null and p_activity_name is not null then
10526: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
10527: and ol.OPEN_FLAG = 'Y'
10528: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
10529: and not exists ( select 'x'
10530: from oe_order_holds_ALL oh
10531: where oh.header_id = h.header_id
10532: and oh.line_id = ol.line_id
10533: and oh.hold_source_id =
10534: p_hold_source_rec.hold_source_id );
10686: --ER#7479609 end
10687:
10688: /*ER#7479609 start
10689: IF p_hold_source_rec.line_id IS NOT NULL THEN
10690: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10691: ( ORDER_HOLD_ID
10692: , LAST_UPDATE_DATE
10693: , LAST_UPDATED_BY
10694: , CREATION_DATE
10700: , RELEASED_FLAG
10701: , ORG_ID
10702: )
10703: SELECT
10704: OE_ORDER_HOLDS_S.NEXTVAL
10705: , SYSDATE
10706: , :l_user_id
10707: , SYSDATE
10708: , :l_user_id
10722: and ol.OPEN_FLAG = ''Y''
10723: -- QUOTING change
10724: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10725: and not exists ( select ''x''
10726: from oe_order_holds_ALL oh
10727: where oh.header_id = h.header_id
10728: and oh.line_id = ol.line_id
10729: and oh.hold_source_id =:hold_source_id )';
10730: IF p_item_type is not null and p_activity_name is not null then
10759: x_is_hold_applied := TRUE;
10760: END IF;
10761: END IF;
10762: ELSE
10763: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10764: ( ORDER_HOLD_ID
10765: , LAST_UPDATE_DATE
10766: , LAST_UPDATED_BY
10767: , CREATION_DATE
10773: , RELEASED_FLAG
10774: , ORG_ID
10775: )
10776: SELECT
10777: OE_ORDER_HOLDS_S.NEXTVAL
10778: , SYSDATE
10779: , :l_user_id
10780: , SYSDATE
10781: , :l_user_id
10794: and ol.OPEN_FLAG = ''Y''
10795: -- QUOTING change
10796: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10797: and not exists ( select ''x''
10798: from oe_order_holds_ALL oh
10799: where oh.header_id = h.header_id
10800: and oh.line_id = ol.line_id
10801: and oh.hold_source_id =:hold_source_id )';
10802: IF p_item_type is not null and p_activity_name is not null then
10851: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
10852: and ol.OPEN_FLAG = 'Y'
10853: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
10854: and not exists ( select 'x'
10855: from oe_order_holds_ALL oh
10856: where oh.header_id = h.header_id
10857: and oh.line_id = ol.line_id
10858: and oh.hold_source_id =
10859: p_hold_source_rec.hold_source_id );
10901: --ER#7479609 end
10902:
10903: /*ER#7479609 start
10904: IF p_hold_source_rec.line_id IS NOT NULL THEN
10905: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10906: ( ORDER_HOLD_ID
10907: , LAST_UPDATE_DATE
10908: , LAST_UPDATED_BY
10909: , CREATION_DATE
10915: , RELEASED_FLAG
10916: , ORG_ID
10917: )
10918: SELECT
10919: OE_ORDER_HOLDS_S.NEXTVAL
10920: , SYSDATE
10921: , :l_user_id
10922: , SYSDATE
10923: , :l_user_id
10937: and ol.OPEN_FLAG = ''Y''
10938: -- QUOTING change
10939: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10940: and not exists ( select ''x''
10941: from oe_order_holds_ALL oh
10942: where oh.header_id = h.header_id
10943: and oh.line_id = ol.line_id
10944: and oh.hold_source_id =:hold_source_id )';
10945: IF p_item_type is not null and p_activity_name is not null then
10974: x_is_hold_applied := TRUE;
10975: END IF;
10976: END IF;
10977: ELSE
10978: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10979: ( ORDER_HOLD_ID
10980: , LAST_UPDATE_DATE
10981: , LAST_UPDATED_BY
10982: , CREATION_DATE
10988: , RELEASED_FLAG
10989: , ORG_ID
10990: )
10991: SELECT
10992: OE_ORDER_HOLDS_S.NEXTVAL
10993: , SYSDATE
10994: , :l_user_id
10995: , SYSDATE
10996: , :l_user_id
11009: and ol.OPEN_FLAG = ''Y''
11010: -- QUOTING change
11011: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11012: and not exists ( select ''x''
11013: from oe_order_holds_ALL oh
11014: where oh.header_id = h.header_id
11015: and oh.line_id = ol.line_id
11016: and oh.hold_source_id =:hold_source_id )';
11017: IF p_item_type is not null and p_activity_name is not null then
11065: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
11066: and ol.OPEN_FLAG = 'Y'
11067: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
11068: and not exists ( select 'x'
11069: from oe_order_holds_ALL oh
11070: where oh.header_id = h.header_id
11071: and oh.line_id = ol.line_id
11072: and oh.hold_source_id =
11073: p_hold_source_rec.hold_source_id );
11115: --ER#7479609 end
11116:
11117: /*ER#7479609 start
11118: IF p_hold_source_rec.line_id IS NOT NULL THEN
11119: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11120: ( ORDER_HOLD_ID
11121: , LAST_UPDATE_DATE
11122: , LAST_UPDATED_BY
11123: , CREATION_DATE
11129: , RELEASED_FLAG
11130: , ORG_ID
11131: )
11132: SELECT
11133: OE_ORDER_HOLDS_S.NEXTVAL
11134: , SYSDATE
11135: , :l_user_id
11136: , SYSDATE
11137: , :l_user_id
11151: and ol.OPEN_FLAG = ''Y''
11152: -- QUOTING change
11153: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11154: and not exists ( select ''x''
11155: from oe_order_holds_ALL oh
11156: where oh.header_id = h.header_id
11157: and oh.line_id = ol.line_id
11158: and oh.hold_source_id =:hold_source_id )';
11159: IF p_item_type is not null and p_activity_name is not null then
11188: x_is_hold_applied := TRUE;
11189: END IF;
11190: END IF;
11191: ELSE
11192: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11193: ( ORDER_HOLD_ID
11194: , LAST_UPDATE_DATE
11195: , LAST_UPDATED_BY
11196: , CREATION_DATE
11202: , RELEASED_FLAG
11203: , ORG_ID
11204: )
11205: SELECT
11206: OE_ORDER_HOLDS_S.NEXTVAL
11207: , SYSDATE
11208: , :l_user_id
11209: , SYSDATE
11210: , :l_user_id
11223: and ol.OPEN_FLAG = ''Y''
11224: -- QUOTING change
11225: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11226: and not exists ( select ''x''
11227: from oe_order_holds_ALL oh
11228: where oh.header_id = h.header_id
11229: and oh.line_id = ol.line_id
11230: and oh.hold_source_id =:hold_source_id )';
11231: IF p_item_type is not null and p_activity_name is not null then
11279: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
11280: and ol.OPEN_FLAG = 'Y'
11281: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
11282: and not exists ( select 'x'
11283: from oe_order_holds_ALL oh
11284: where oh.header_id = h.header_id
11285: and oh.line_id = ol.line_id
11286: and oh.hold_source_id =
11287: p_hold_source_rec.hold_source_id );
11328: --ER#7479609 end
11329:
11330: /*ER#7479609 start
11331: IF p_hold_source_rec.line_id IS NOT NULL THEN
11332: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11333: ( ORDER_HOLD_ID
11334: , LAST_UPDATE_DATE
11335: , LAST_UPDATED_BY
11336: , CREATION_DATE
11342: , RELEASED_FLAG
11343: , ORG_ID
11344: )
11345: SELECT
11346: OE_ORDER_HOLDS_S.NEXTVAL
11347: , SYSDATE
11348: , :l_user_id
11349: , SYSDATE
11350: , :l_user_id
11363: and ol.OPEN_FLAG = ''Y''
11364: -- QUOTING change
11365: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11366: and not exists ( select ''x''
11367: from oe_order_holds_ALL oh
11368: where oh.header_id = h.header_id
11369: and oh.line_id = ol.line_id
11370: and oh.hold_source_id =:hold_source_id )';
11371: IF p_item_type is not null and p_activity_name is not null then
11400: x_is_hold_applied := TRUE;
11401: END IF;
11402: END IF;
11403: ELSE
11404: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11405: ( ORDER_HOLD_ID
11406: , LAST_UPDATE_DATE
11407: , LAST_UPDATED_BY
11408: , CREATION_DATE
11414: , RELEASED_FLAG
11415: , ORG_ID
11416: )
11417: SELECT
11418: OE_ORDER_HOLDS_S.NEXTVAL
11419: , SYSDATE
11420: , :l_user_id
11421: , SYSDATE
11422: , :l_user_id
11434: and ol.OPEN_FLAG = ''Y''
11435: -- QUOTING change
11436: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11437: and not exists ( select ''x''
11438: from oe_order_holds_ALL oh
11439: where oh.header_id = h.header_id
11440: and oh.line_id = ol.line_id
11441: and oh.hold_source_id =:hold_source_id )';
11442: IF p_item_type is not null and p_activity_name is not null then
11489: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
11490: and ol.OPEN_FLAG = 'Y'
11491: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
11492: and not exists ( select 'x'
11493: from oe_order_holds_ALL oh
11494: where oh.header_id = h.header_id
11495: and oh.line_id = ol.line_id
11496: and oh.hold_source_id =
11497: p_hold_source_rec.hold_source_id );
11521: ******************************************************************************************************/
11522:
11523: ELSIF p_hold_source_rec.hold_entity_code = 'O' THEN
11524: IF p_hold_source_rec.line_id is NULL THEN
11525: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11526: ( ORDER_HOLD_ID
11527: , LAST_UPDATE_DATE
11528: , LAST_UPDATED_BY
11529: , CREATION_DATE
11535: , RELEASED_FLAG
11536: , ORG_ID
11537: )
11538: SELECT
11539: OE_ORDER_HOLDS_S.NEXTVAL
11540: , SYSDATE
11541: , :l_user_id
11542: , SYSDATE
11543: , :l_user_id
11553: and h.header_id = :hold_entity_id
11554: -- QUOTING change
11555: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11556: and not exists ( select ''x''
11557: from oe_order_holds_ALL oh
11558: where oh.header_id = h.header_id
11559: and oh.hold_source_id =:hold_source_id2 )';
11560: IF p_item_type is not null and p_activity_name is not null then
11561: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11577: x_is_hold_applied := TRUE;
11578: END IF;
11579: END IF;
11580: ELSE
11581: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11582: ( ORDER_HOLD_ID
11583: , LAST_UPDATE_DATE
11584: , LAST_UPDATED_BY
11585: , CREATION_DATE
11591: , RELEASED_FLAG
11592: , ORG_ID
11593: )
11594: SELECT
11595: OE_ORDER_HOLDS_S.NEXTVAL
11596: , SYSDATE
11597: , :l_user_id
11598: , SYSDATE
11599: , :l_user_id
11612: and ol.OPEN_FLAG = ''Y''
11613: -- QUOTING change
11614: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11615: and not exists ( select ''x''
11616: from oe_order_holds_ALL oh
11617: where oh.header_id = h.header_id
11618: and oh.line_id = ol.line_id
11619: and oh.hold_source_id =:hold_source_id )';
11620: IF p_item_type is not null and p_activity_name is not null then
11671: and ol.line_id = p_hold_source_rec.line_id
11672: and ol.open_flag = 'Y'
11673: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
11674: and not exists ( select 'x'
11675: from oe_order_holds_ALL oh
11676: where oh.header_id = h.header_id
11677: and oh.line_id = ol.line_id
11678: and oh.hold_source_id =
11679: p_hold_source_rec.hold_source_id );
11726:
11727:
11728: /*ER#7479609 start
11729: IF p_hold_source_rec.header_id IS NOT NULL THEN
11730: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11731: ( ORDER_HOLD_ID
11732: , LAST_UPDATE_DATE
11733: , LAST_UPDATED_BY
11734: , CREATION_DATE
11740: , RELEASED_FLAG
11741: , ORG_ID
11742: )
11743: SELECT
11744: OE_ORDER_HOLDS_S.NEXTVAL
11745: , SYSDATE
11746: , :l_user_id
11747: , SYSDATE
11748: , :l_user_id
11759: and h.SOLD_TO_ORG_ID = :hold_entity_id
11760: -- QUOTING change
11761: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11762: and not exists ( select ''x''
11763: from oe_order_holds_ALL oh
11764: where oh.header_id = h.header_id
11765: and oh.hold_source_id =:hold_source_id )';
11766: IF p_item_type is not null and p_activity_name is not null then
11767: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11784: x_is_hold_applied := TRUE;
11785: END IF;
11786: END IF;
11787: ELSE
11788: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11789: ( ORDER_HOLD_ID
11790: , LAST_UPDATE_DATE
11791: , LAST_UPDATED_BY
11792: , CREATION_DATE
11798: , RELEASED_FLAG
11799: , ORG_ID
11800: )
11801: SELECT
11802: OE_ORDER_HOLDS_S.NEXTVAL
11803: , SYSDATE
11804: , :l_user_id
11805: , SYSDATE
11806: , :l_user_id
11817: and h.org_id = :l_org_id --ER#7479609
11818: -- QUOTING change
11819: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11820: and not exists ( select ''x''
11821: from oe_order_holds_ALL oh
11822: where oh.header_id = h.header_id
11823: and oh.hold_source_id =:hold_source_id )';
11824: IF p_item_type is not null and p_activity_name is not null then
11825: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
11861:
11862: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
11863: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
11864: and not exists ( select 'x'
11865: from oe_order_holds_ALL oh
11866: where oh.header_id = h.header_id
11867: and oh.hold_source_id =
11868: p_hold_source_rec.hold_source_id );
11869:
12164: --ER#7479609 end
12165:
12166: /*ER#7479609 start
12167: IF p_hold_source_rec.line_id IS NOT NULL THEN
12168: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12169: ( ORDER_HOLD_ID
12170: , LAST_UPDATE_DATE
12171: , LAST_UPDATED_BY
12172: , CREATION_DATE
12178: , RELEASED_FLAG
12179: , ORG_ID
12180: )
12181: SELECT
12182: OE_ORDER_HOLDS_S.NEXTVAL
12183: , SYSDATE
12184: , :l_user_id
12185: , SYSDATE
12186: , :l_user_id
12199: and ol.OPEN_FLAG = ''Y''
12200: -- QUOTING change
12201: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12202: and not exists ( select ''x''
12203: from oe_order_holds_ALL oh
12204: where oh.header_id = h.header_id
12205: and oh.line_id = ol.line_id
12206: and oh.hold_source_id =:hold_source_id )';
12207: IF p_item_type is not null and p_activity_name is not null then
12236: END IF;
12237: END IF;
12238:
12239: ELSE
12240: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12241: ( ORDER_HOLD_ID
12242: , LAST_UPDATE_DATE
12243: , LAST_UPDATED_BY
12244: , CREATION_DATE
12250: , RELEASED_FLAG
12251: , ORG_ID
12252: )
12253: SELECT
12254: OE_ORDER_HOLDS_S.NEXTVAL
12255: , SYSDATE
12256: , :l_user_id
12257: , SYSDATE
12258: , :l_user_id
12270: and ol.OPEN_FLAG = ''Y''
12271: -- QUOTING change
12272: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12273: and not exists ( select ''x''
12274: from oe_order_holds_ALL oh
12275: where oh.header_id = h.header_id
12276: and oh.line_id = ol.line_id
12277: and oh.hold_source_id =:hold_source_id )';
12278: IF p_item_type is not null and p_activity_name is not null then
12324: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
12325: and ol.OPEN_FLAG = 'Y'
12326: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12327: and not exists ( select 'x'
12328: from oe_order_holds_ALL oh
12329: where oh.header_id = h.header_id
12330: and oh.line_id = ol.line_id
12331: and oh.hold_source_id =
12332: p_hold_source_rec.hold_source_id );
12377: --ER#7479609 end
12378:
12379: /*ER#7479609 start
12380: IF p_hold_source_rec.line_id IS NOT NULL THEN
12381: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12382: ( ORDER_HOLD_ID
12383: , LAST_UPDATE_DATE
12384: , LAST_UPDATED_BY
12385: , CREATION_DATE
12391: , RELEASED_FLAG
12392: , ORG_ID
12393: )
12394: SELECT
12395: OE_ORDER_HOLDS_S.NEXTVAL
12396: , SYSDATE
12397: , :l_user_id
12398: , SYSDATE
12399: , :l_user_id
12412: and ol.OPEN_FLAG = ''Y''
12413: -- QUOTING change
12414: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12415: and not exists ( select ''x''
12416: from oe_order_holds_ALL oh
12417: where oh.header_id = h.header_id
12418: and oh.line_id = ol.line_id
12419: and oh.hold_source_id =:hold_source_id )';
12420: IF p_item_type is not null and p_activity_name is not null then
12448: x_is_hold_applied := TRUE;
12449: END IF;
12450: END IF;
12451: ELSE
12452: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12453: ( ORDER_HOLD_ID
12454: , LAST_UPDATE_DATE
12455: , LAST_UPDATED_BY
12456: , CREATION_DATE
12462: , RELEASED_FLAG
12463: , ORG_ID
12464: )
12465: SELECT
12466: OE_ORDER_HOLDS_S.NEXTVAL
12467: , SYSDATE
12468: , :l_user_id
12469: , SYSDATE
12470: , :l_user_id
12482: and ol.OPEN_FLAG = ''Y''
12483: -- QUOTING change
12484: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12485: and not exists ( select ''x''
12486: from oe_order_holds_ALL oh
12487: where oh.header_id = h.header_id
12488: and oh.line_id = ol.line_id
12489: and oh.hold_source_id =:hold_source_id )';
12490: IF p_item_type is not null and p_activity_name is not null then
12536: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id
12537: and ol.OPEN_FLAG = 'Y'
12538: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12539: and not exists ( select 'x'
12540: from oe_order_holds_ALL oh
12541: where oh.header_id = h.header_id
12542: and oh.line_id = ol.line_id
12543: and oh.hold_source_id =
12544: p_hold_source_rec.hold_source_id );
12588: --ER#7479609 end
12589:
12590: /*ER#7479609 start
12591: IF p_hold_source_rec.line_id IS NOT NULL THEN
12592: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12593: ( ORDER_HOLD_ID
12594: , LAST_UPDATE_DATE
12595: , LAST_UPDATED_BY
12596: , CREATION_DATE
12602: , RELEASED_FLAG
12603: , ORG_ID
12604: )
12605: SELECT
12606: OE_ORDER_HOLDS_S.NEXTVAL
12607: , SYSDATE
12608: , :l_user_id
12609: , SYSDATE
12610: , :l_user_id
12623: and ol.OPEN_FLAG = ''Y''
12624: -- QUOTING change
12625: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12626: and not exists ( select ''x''
12627: from oe_order_holds_ALL oh
12628: where oh.header_id = h.header_id
12629: and oh.line_id = ol.line_id
12630: and oh.hold_source_id =:hold_source_id )';
12631: IF p_item_type is not null and p_activity_name is not null then
12659: x_is_hold_applied := TRUE;
12660: END IF;
12661: END IF;
12662: ELSE
12663: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12664: ( ORDER_HOLD_ID
12665: , LAST_UPDATE_DATE
12666: , LAST_UPDATED_BY
12667: , CREATION_DATE
12673: , RELEASED_FLAG
12674: , ORG_ID
12675: )
12676: SELECT
12677: OE_ORDER_HOLDS_S.NEXTVAL
12678: , SYSDATE
12679: , :l_user_id
12680: , SYSDATE
12681: , :l_user_id
12693: and ol.OPEN_FLAG = ''Y''
12694: -- QUOTING change
12695: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12696: and not exists ( select ''x''
12697: from oe_order_holds_ALL oh
12698: where oh.header_id = h.header_id
12699: and oh.line_id = ol.line_id
12700: and oh.hold_source_id =:hold_source_id )';
12701: IF p_item_type is not null and p_activity_name is not null then
12747: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
12748: and ol.OPEN_FLAG = 'Y'
12749: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12750: and not exists ( select 'x'
12751: from oe_order_holds_ALL oh
12752: where oh.header_id = h.header_id
12753: and oh.line_id = ol.line_id
12754: and oh.hold_source_id =
12755: p_hold_source_rec.hold_source_id );
12799: --ER#7479609 end
12800:
12801: /*ER#7479609 start
12802: IF p_hold_source_rec.line_id IS NOT NULL THEN
12803: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12804: ( ORDER_HOLD_ID
12805: , LAST_UPDATE_DATE
12806: , LAST_UPDATED_BY
12807: , CREATION_DATE
12813: , RELEASED_FLAG
12814: , ORG_ID
12815: )
12816: SELECT
12817: OE_ORDER_HOLDS_S.NEXTVAL
12818: , SYSDATE
12819: , :l_user_id
12820: , SYSDATE
12821: , :l_user_id
12834: and ol.OPEN_FLAG = ''Y''
12835: -- QUOTING change
12836: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12837: and not exists ( select ''x''
12838: from oe_order_holds_ALL oh
12839: where oh.header_id = h.header_id
12840: and oh.line_id = ol.line_id
12841: and oh.hold_source_id =:hold_source_id )';
12842: IF p_item_type is not null and p_activity_name is not null then
12871: END IF;
12872: END IF;
12873:
12874: ELSE
12875: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12876: ( ORDER_HOLD_ID
12877: , LAST_UPDATE_DATE
12878: , LAST_UPDATED_BY
12879: , CREATION_DATE
12885: , RELEASED_FLAG
12886: , ORG_ID
12887: )
12888: SELECT
12889: OE_ORDER_HOLDS_S.NEXTVAL
12890: , SYSDATE
12891: , :l_user_id
12892: , SYSDATE
12893: , :l_user_id
12905: and ol.OPEN_FLAG = ''Y''
12906: -- QUOTING change
12907: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12908: and not exists ( select ''x''
12909: from oe_order_holds_ALL oh
12910: where oh.header_id = h.header_id
12911: and oh.line_id = ol.line_id
12912: and oh.hold_source_id =:hold_source_id )';
12913: IF p_item_type is not null and p_activity_name is not null then
12958: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
12959: and ol.OPEN_FLAG = 'Y'
12960: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12961: and not exists ( select 'x'
12962: from oe_order_holds_ALL oh
12963: where oh.header_id = h.header_id
12964: and oh.line_id = ol.line_id
12965: and oh.hold_source_id =
12966: p_hold_source_rec.hold_source_id );