eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OE_HOLDS_PVT dependencies on OE_ORDER_HOLDS_ALL

Line 750: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table

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:

Line 787: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 818: from oe_order_holds_ALL oh

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

Line 874: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 904: from oe_order_holds_ALL oh

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

Line 971: from oe_order_holds_ALL oh

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

Line 1015: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table

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:

Line 1052: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 1085: from oe_order_holds_ALL oh

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

Line 1155: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 1187: from oe_order_holds_ALL oh

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:

Line 1266: FROM oe_order_holds_ALL oh

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:

Line 1309: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table

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:

Line 2347: from oe_order_holds_all OH --ER#7479609

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:

Line 2378: from oe_order_holds_all OH --ER#7479609

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:

Line 2589: from oe_order_holds_all OH --ER#7479609

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

Line 2618: from oe_order_holds_all OH --ER#7479609

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

Line 2937: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 2973: from oe_order_holds_ALL oh

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 );

Line 2979: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3014: from oe_order_holds_ALL oh

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 );

Line 3024: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3062: from oe_order_holds_ALL oh

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 );

Line 3068: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3105: from oe_order_holds_ALL oh

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 );

Line 3115: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3151: from oe_order_holds_ALL oh

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 );

Line 3157: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3192: from oe_order_holds_ALL oh

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 );

Line 3201: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3237: from oe_order_holds_ALL oh

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 );

Line 3243: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3278: from oe_order_holds_ALL oh

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 );

Line 3287: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3323: from oe_order_holds_ALL oh

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 );

Line 3329: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3364: from oe_order_holds_ALL oh

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 );

Line 3373: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3409: from oe_order_holds_ALL oh

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 );

Line 3415: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3450: from oe_order_holds_ALL oh

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 );

Line 3460: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3496: from oe_order_holds_ALL oh

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 );

Line 3502: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3537: from oe_order_holds_ALL oh

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 );

Line 3546: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3582: FROM oe_order_holds_ALL oh

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 );

Line 3588: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3623: FROM oe_order_holds_ALL oh

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 );

Line 3632: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3668: FROM oe_order_holds_ALL oh

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 );

Line 3674: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3709: FROM oe_order_holds_ALL oh

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 );

Line 3718: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3754: from oe_order_holds_ALL oh

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 );

Line 3760: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3795: from oe_order_holds_ALL oh

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 );

Line 3805: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3841: from oe_order_holds_ALL oh

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 );

Line 3847: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3882: from oe_order_holds_ALL oh

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 );

Line 3891: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3927: from oe_order_holds_ALL oh

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 );

Line 3933: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3968: from oe_order_holds_ALL oh

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 );

Line 3978: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4014: from oe_order_holds_ALL oh

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 );

Line 4020: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4055: from oe_order_holds_ALL oh

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 );

Line 4064: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4099: from oe_order_holds_ALL oh

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 );

Line 4105: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4139: from oe_order_holds_ALL oh

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 );

Line 4148: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4179: from oe_order_holds_ALL oh

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

Line 4184: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4218: from oe_order_holds_ALL oh

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 );

Line 4228: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4261: from oe_order_holds_ALL oh

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

Line 4266: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4298: from oe_order_holds_ALL oh

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;

Line 4305: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4340: from oe_order_holds_ALL oh

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 );

Line 4346: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4380: from oe_order_holds_ALL oh

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 );

Line 4388: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4423: from oe_order_holds_ALL oh

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 );

Line 4429: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4463: from oe_order_holds_ALL oh

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 );

Line 4471: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4506: from oe_order_holds_ALL oh

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 );

Line 4512: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4546: from oe_order_holds_ALL oh

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 );

Line 4554: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4589: from oe_order_holds_ALL oh

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 );

Line 4595: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4629: from oe_order_holds_ALL oh

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 );

Line 5204: -- Deletes from OE_ORDER_HOLDS all hold records for an order (p_header_id)

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

Line 5226: FROM OE_ORDER_HOLDS_all

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

Line 5235: FROM OE_ORDER_HOLDS_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

Line 5273: DELETE FROM OE_ORDER_HOLDS_all

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

Line 5280: FROM OE_ORDER_HOLDS_all

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: );

Line 5306: FROM OE_ORDER_HOLDS_all

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: );

Line 5339: FROM OE_ORDER_HOLDS_all

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: );

Line 7806: FROM OE_ORDER_HOLDS_all oh,

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;

Line 7864: INSERT INTO OE_ORDER_HOLDS_all

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

Line 8112: FROM oe_order_holds_all ooh,

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

Line 8131: FROM oe_order_holds_all ooh,

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

Line 8226: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 8263: from oe_order_holds_ALL oh

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

Line 8303: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 8339: from oe_order_holds_ALL oh

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:

Line 8396: from oe_order_holds_ALL oh

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 );

Line 8552: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 8590: from oe_order_holds_ALL oh

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

Line 8624: l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 8661: from oe_order_holds_ALL oh

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

Line 8720: from oe_order_holds_ALL oh

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 );

Line 8769: l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 8805: from oe_order_holds_ALL oh

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

Line 8843: l_sqlmt := 'Insert into oe_order_holds_all

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

Line 8878: from oe_order_holds_ALL oh

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

Line 8935: from oe_order_holds_ALL oh

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 );

Line 8984: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9020: from oe_order_holds_ALL oh

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

Line 9057: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9092: from oe_order_holds_ALL oh

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

Line 9147: from oe_order_holds_ALL oh

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 );

Line 9197: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9233: from oe_order_holds_ALL oh

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

Line 9271: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9306: from oe_order_holds_ALL oh

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

Line 9362: from oe_order_holds_ALL oh

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 );

Line 9412: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9448: from oe_order_holds_ALL oh

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

Line 9485: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9520: from oe_order_holds_ALL oh

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

Line 9576: from oe_order_holds_ALL oh

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 );

Line 9629: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9665: from oe_order_holds_ALL oh

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

Line 9702: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9738: from oe_order_holds_ALL oh

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

Line 9794: from oe_order_holds_ALL oh

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 );

Line 9933: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9970: FROM oe_order_holds_ALL oh

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

Line 10007: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10043: FROM oe_order_holds_ALL oh

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

Line 10099: from oe_order_holds_ALL oh

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 );

Line 10149: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10185: FROM oe_order_holds_ALL oh

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

Line 10222: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10257: FROM oe_order_holds_ALL oh

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

Line 10313: from oe_order_holds_ALL oh

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 );

Line 10366: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10402: from oe_order_holds_ALL oh

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

Line 10439: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10474: from oe_order_holds_ALL oh

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

Line 10530: from oe_order_holds_ALL oh

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 );

Line 10690: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10726: from oe_order_holds_ALL oh

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

Line 10763: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10798: from oe_order_holds_ALL oh

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

Line 10855: from oe_order_holds_ALL oh

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 );

Line 10905: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10941: from oe_order_holds_ALL oh

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

Line 10978: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11013: from oe_order_holds_ALL oh

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

Line 11069: from oe_order_holds_ALL oh

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 );

Line 11119: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11155: from oe_order_holds_ALL oh

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

Line 11192: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11227: from oe_order_holds_ALL oh

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

Line 11283: from oe_order_holds_ALL oh

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 );

Line 11332: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11367: from oe_order_holds_ALL oh

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

Line 11404: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11438: from oe_order_holds_ALL oh

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

Line 11493: from oe_order_holds_ALL oh

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 );

Line 11525: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11557: from oe_order_holds_ALL oh

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

Line 11581: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11616: from oe_order_holds_ALL oh

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

Line 11675: from oe_order_holds_ALL oh

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 );

Line 11730: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11763: from oe_order_holds_ALL oh

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

Line 11788: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11821: from oe_order_holds_ALL oh

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

Line 11865: from oe_order_holds_ALL oh

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:

Line 12168: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12203: from oe_order_holds_ALL oh

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

Line 12240: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12274: from oe_order_holds_ALL oh

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

Line 12328: from oe_order_holds_ALL oh

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 );

Line 12381: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12416: from oe_order_holds_ALL oh

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

Line 12452: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12486: from oe_order_holds_ALL oh

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

Line 12540: from oe_order_holds_ALL oh

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 );

Line 12592: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12627: from oe_order_holds_ALL oh

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

Line 12663: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12697: from oe_order_holds_ALL oh

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

Line 12751: from oe_order_holds_ALL oh

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 );

Line 12803: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12838: from oe_order_holds_ALL oh

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

Line 12875: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12909: from oe_order_holds_ALL oh

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

Line 12962: from oe_order_holds_ALL oh

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 );