[Home] [Help]
529: IF p_order_tbl(1).line_id IS NULL THEN
530: BEGIN
531: SELECT 'Y'
532: INTO l_release_children
533: FROM oe_order_holds oh,
534: oe_hold_sources hs,
535: oe_hold_definitions hd
536: WHERE hs.hold_source_id = oh.hold_source_id
537: AND hs.hold_id = hd.hold_id
779: CURSOR released_orders_lines
780: IS
781: SELECT NVL(line_id, header_id) entity_id,
782: DECODE(line_id , NULL, 'OEOH', 'OEOL') entity_type
783: FROM oe_order_holds oh, oe_hold_sources hs
784: WHERE hs.hold_source_id = p_hold_source_id
785: AND oh.hold_release_id = hs.hold_release_id
786: AND oh.released_flag = 'Y';
787:
1020:
1021: --ER#7479609 start
1022: ---------------------------------------------------------------------------------------------------
1023: -- PROCEDURE : InsertTable_OOH_Header
1024: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table
1025: -- for header level holds
1026:
1027: -- Caller : Create_Order_Holds Procedure
1028: -- PARAMETERS:
1057:
1058: OE_DEBUG_PUB.ADD('Entering InsertTable_OOH_Header');
1059:
1060: IF p_header_id IS NOT NULL THEN
1061: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
1062: ( ORDER_HOLD_ID
1063: , LAST_UPDATE_DATE
1064: , LAST_UPDATED_BY
1065: , CREATION_DATE
1071: , RELEASED_FLAG
1072: , ORG_ID
1073: )
1074: SELECT
1075: OE_ORDER_HOLDS_S.NEXTVAL
1076: , SYSDATE
1077: , :l_user_id
1078: , SYSDATE
1079: , :l_user_id
1088: and h.org_id = :l_org_id
1089: and h.header_id = :header_id
1090: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1091: and not exists ( select ''x''
1092: from oe_order_holds_ALL oh
1093: where oh.header_id = h.header_id
1094: and oh.line_id IS NULL
1095: and oh.hold_source_id =:hold_source_id )';
1096: IF p_item_type is not null and p_activity_name is not null then
1147:
1148: ELSE
1149:
1150:
1151: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
1152: ( ORDER_HOLD_ID
1153: , LAST_UPDATE_DATE
1154: , LAST_UPDATED_BY
1155: , CREATION_DATE
1161: , RELEASED_FLAG
1162: , ORG_ID
1163: )
1164: SELECT
1165: OE_ORDER_HOLDS_S.NEXTVAL
1166: , SYSDATE
1167: , :l_user_id
1168: , SYSDATE
1169: , :l_user_id
1177: WHERE h.OPEN_FLAG = ''Y''
1178: and h.org_id = :l_org_id
1179: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1180: and not exists ( select ''x''
1181: from oe_order_holds_ALL oh
1182: where oh.header_id = h.header_id
1183: and oh.line_id IS NULL
1184: and oh.hold_source_id =:hold_source_id )';
1185: IF p_item_type is not null and p_activity_name is not null then
1247: FROM OE_ORDER_HEADERS_ALL h
1248: WHERE h.OPEN_FLAG = ''Y''
1249: AND nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1250: AND NOT EXISTS ( select ''x''
1251: from oe_order_holds_ALL oh
1252: where oh.header_id = h.header_id
1253: and oh.hold_source_id = '||p_hold_source_id||' )';
1254:
1255: IF p_hold_entity_where_clause IS NOT NULL THEN
1291:
1292: --ER#7479609 start
1293: ---------------------------------------------------------------------------------------------------
1294: -- PROCEDURE : InsertTable_OOH_Line
1295: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table
1296: -- for line level holds
1297:
1298: -- Caller : Create_Order_Holds Procedure
1299: -- PARAMETERS:
1328: OE_DEBUG_PUB.ADD('Entering InsertTable_OOH_Line');
1329:
1330: IF p_line_id IS NOT NULL THEN
1331:
1332: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
1333: ( ORDER_HOLD_ID
1334: , LAST_UPDATE_DATE
1335: , LAST_UPDATED_BY
1336: , CREATION_DATE
1342: , RELEASED_FLAG
1343: , ORG_ID
1344: )
1345: SELECT
1346: OE_ORDER_HOLDS_S.NEXTVAL
1347: , SYSDATE
1348: , :l_user_id
1349: , SYSDATE
1350: , :l_user_id
1361: and ol.line_id = :line_id
1362: and ol.OPEN_FLAG = ''Y''
1363: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1364: and not exists ( select ''x''
1365: from oe_order_holds_ALL oh
1366: where oh.header_id = h.header_id
1367: and oh.line_id = ol.line_id
1368: and oh.hold_source_id = :hold_source_id )';
1369: IF p_item_type is not null and p_activity_name is not null then
1434: END IF;
1435:
1436: ELSE
1437:
1438: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
1439: ( ORDER_HOLD_ID
1440: , LAST_UPDATE_DATE
1441: , LAST_UPDATED_BY
1442: , CREATION_DATE
1448: , RELEASED_FLAG
1449: , ORG_ID
1450: )
1451: SELECT
1452: OE_ORDER_HOLDS_S.NEXTVAL
1453: , SYSDATE
1454: , :l_user_id
1455: , SYSDATE
1456: , :l_user_id
1466: and h.org_id = :l_org_id
1467: and ol.OPEN_FLAG = ''Y''
1468: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1469: and not exists ( select ''x''
1470: from oe_order_holds_ALL oh
1471: where oh.header_id = h.header_id
1472: and oh.line_id = ol.line_id
1473: and oh.hold_source_id =:hold_source_id )';
1474:
1548: AND h.header_id = ol.header_id
1549: AND ol.OPEN_FLAG = ''Y''
1550: AND NVL(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
1551: AND NOT EXISTS ( SELECT ''x''
1552: FROM oe_order_holds_ALL oh
1553: WHERE oh.header_id = h.header_id
1554: AND oh.line_id = ol.line_id
1555: AND oh.hold_source_id = '||p_hold_source_id||' )';
1556:
1591:
1592: --ER#7479609 start
1593: ---------------------------------------------------------------------------------------------------
1594: -- PROCEDURE : PaymentType_Hold
1595: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table
1596: -- for header level holds
1597:
1598: -- Caller : Create_Order_Holds Procedure
1599: -- PARAMETERS:
1859: HS.HOLD_ENTITY_CODE,
1860: hs.hold_id,
1861: oh.order_hold_id
1862: FROM OE_HOLD_SOURCES HS,
1863: OE_ORDER_HOLDS oh
1864: WHERE HS.HOLD_ID = nvl(p_hold_source_rec.hold_id, HS.HOLD_ID)
1865: and hs.hold_entity_code = nvl(p_hold_source_rec.hold_entity_code, hs.hold_entity_code)
1866: and hs.hold_entity_id = nvl(p_hold_source_rec.hold_entity_id, hs.hold_entity_id)
1867: and hs.HOLD_SOURCE_ID = oh.HOLD_SOURCE_ID
1873: CURSOR check_hold_type_cur(p_ord_hld_id IN NUMBER) IS
1874: SELECT 'Y'
1875: FROM DUAL
1876: WHERE EXISTS (SELECT NULL
1877: FROM oe_order_holds ooh,
1878: oe_hold_sources ohs,
1879: oe_hold_definitions ohd,
1880: oe_order_headers_all h,
1881: oe_order_sources oos
1897:
1898: cursor header_line_id_cur(p_ord_hld_id in number) is
1899: select ohld.header_id,
1900: ohld.line_id
1901: from oe_order_holds ohld,
1902: oe_order_headers ooh,
1903: oe_order_sources src
1904: where ohld.order_hold_id = p_ord_hld_id
1905: and ohld.header_id = ooh.header_id
1916: x_hold_release_id oe_hold_releases.HOLD_RELEASE_ID%type;
1917: l_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;
1918: x_hold_source_id OE_HOLD_SOURCES.HOLD_SOURCE_ID%TYPE;
1919: l_hold_entity_code OE_HOLD_SOURCES.HOLD_ENTITY_CODE%TYPE;
1920: l_order_hold_id OE_ORDER_HOLDS.ORDER_HOLD_ID%TYPE;
1921: l_hold_id OE_HOLD_DEFINITIONS.hold_id%type;
1922:
1923: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
1924: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2024: , l_order_hold_id
2025: );
2026:
2027: --dbms_output.put_line('RlsID:'||to_char(p_hold_release_rec.hold_release_id));
2028: UPDATE oe_order_holds
2029: SET hold_release_id = x_hold_release_id,
2030: LAST_UPDATED_BY = l_user_id,
2031: LAST_UPDATE_DATE = sysdate,
2032: RELEASED_FLAG = 'Y'
2053: l_nameVal_tbl(l_count+3).value := x_hold_release_id;
2054: BEGIN
2055: SELECT ohld.header_id, ohld.line_id
2056: INTO l_header_id, l_line_id
2057: FROM oe_order_holds ohld,
2058: oe_order_headers ooh
2059: WHERE ohld.order_hold_id = l_order_hold_id
2060: AND ohld.header_id = ooh.header_id;
2061: EXCEPTION
2797: AND HS.RELEASED_FLAG = 'N'
2798: AND HS.org_id= p_org_id --ER#7479609
2799: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2800: AND EXISTS ( select 'x'
2801: --ER#7479609 from oe_order_holds OH
2802: from oe_order_holds_all OH --ER#7479609
2803: where OH.line_id = p_hold_source_rec.line_id
2804: and OH.org_id= p_org_id --ER#7479609
2805: and OH.hold_source_id = HS.hold_source_id);
2798: AND HS.org_id= p_org_id --ER#7479609
2799: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2800: AND EXISTS ( select 'x'
2801: --ER#7479609 from oe_order_holds OH
2802: from oe_order_holds_all OH --ER#7479609
2803: where OH.line_id = p_hold_source_rec.line_id
2804: and OH.org_id= p_org_id --ER#7479609
2805: and OH.hold_source_id = HS.hold_source_id);
2806:
2828: AND HS.RELEASED_FLAG = 'N'
2829: AND HS.org_id= p_org_id --ER#7479609
2830: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2831: AND EXISTS ( select 'x'
2832: --ER#7479609 from oe_order_holds OH
2833: from oe_order_holds_all OH --ER#7479609
2834: where OH.line_id is null
2835: and OH.org_id= p_org_id --ER#7479609
2836: and OH.hold_source_id = HS.hold_source_id);
2829: AND HS.org_id= p_org_id --ER#7479609
2830: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
2831: AND EXISTS ( select 'x'
2832: --ER#7479609 from oe_order_holds OH
2833: from oe_order_holds_all OH --ER#7479609
2834: where OH.line_id is null
2835: and OH.org_id= p_org_id --ER#7479609
2836: and OH.hold_source_id = HS.hold_source_id);
2837:
3039: AND HS.RELEASED_FLAG = 'N'
3040: AND HS.ORG_ID = p_org_id --ER#7479609
3041: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
3042: AND EXISTS ( select 'x'
3043: --ER#7479609 from oe_order_holds OH
3044: from oe_order_holds_all OH --ER#7479609
3045: where OH.line_id = p_hold_source_rec.line_id
3046: and OH.hold_source_id = HS.hold_source_id);
3047:
3040: AND HS.ORG_ID = p_org_id --ER#7479609
3041: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
3042: AND EXISTS ( select 'x'
3043: --ER#7479609 from oe_order_holds OH
3044: from oe_order_holds_all OH --ER#7479609
3045: where OH.line_id = p_hold_source_rec.line_id
3046: and OH.hold_source_id = HS.hold_source_id);
3047:
3048: IF l_count > 0 THEN
3068: AND HS.RELEASED_FLAG = 'N'
3069: AND HS.ORG_ID = p_org_id --ER#7479609
3070: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
3071: AND EXISTS ( select 'x'
3072: --ER#7479609 from oe_order_holds OH
3073: from oe_order_holds_all OH --ER#7479609
3074: where OH.line_id is null
3075: and OH.hold_source_id = HS.hold_source_id);
3076:
3069: AND HS.ORG_ID = p_org_id --ER#7479609
3070: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE
3071: AND EXISTS ( select 'x'
3072: --ER#7479609 from oe_order_holds OH
3073: from oe_order_holds_all OH --ER#7479609
3074: where OH.line_id is null
3075: and OH.hold_source_id = HS.hold_source_id);
3076:
3077: IF l_count > 0 THEN
3101: AND HS.ORG_ID = p_org_id --ER#7479609
3102: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
3103:
3104: -- AND EXISTS ( select 'x'
3105: -- from oe_order_holds
3106: -- where header_id = p_hold_source_rec.hold_entity_id
3107: -- and line_id = nvl(p_hold_source_rec.line_id, -99));
3108:
3109: IF l_count > 0 THEN
3295: CURSOR check_line_hold_type_cur(p_line_id IN NUMBER) IS
3296: SELECT 'Y'
3297: FROM DUAL
3298: WHERE EXISTS (SELECT NULL
3299: FROM oe_order_holds ooh,
3300: oe_hold_sources ohs,
3301: oe_hold_definitions ohd,
3302: oe_order_headers_all h,
3303: oe_order_sources oos
3312: CURSOR check_hdr_hold_type_cur(p_hdr_id IN NUMBER) IS
3313: SELECT 'Y'
3314: FROM DUAL
3315: WHERE EXISTS (SELECT NULL
3316: FROM oe_order_holds ooh,
3317: oe_hold_sources ohs,
3318: oe_hold_definitions ohd,
3319: oe_order_headers_all h,
3320: oe_order_sources oos
3388:
3389: IF p_hold_source_rec.hold_entity_code = 'I' and
3390: p_hold_source_rec.hold_entity_code2 = 'C' THEN
3391: IF p_hold_source_rec.line_id IS NOT NULL THEN
3392: INSERT INTO OE_ORDER_HOLDS_ALL
3393: ( ORDER_HOLD_ID
3394: , LAST_UPDATE_DATE
3395: , LAST_UPDATED_BY
3396: , CREATION_DATE
3402: , RELEASED_FLAG
3403: , ORG_ID
3404: )
3405: SELECT
3406: OE_ORDER_HOLDS_S.NEXTVAL
3407: , SYSDATE
3408: , l_user_id
3409: , SYSDATE
3410: , l_user_id
3424: and ol.OPEN_FLAG = 'Y'
3425: -- QUOTING change
3426: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3427: and not exists ( select 'x'
3428: from oe_order_holds_ALL oh
3429: where oh.header_id = h.header_id
3430: and oh.line_id = ol.line_id
3431: and oh.hold_source_id =
3432: p_hold_source_rec.hold_source_id );
3430: and oh.line_id = ol.line_id
3431: and oh.hold_source_id =
3432: p_hold_source_rec.hold_source_id );
3433: ELSE
3434: INSERT INTO OE_ORDER_HOLDS_ALL
3435: ( ORDER_HOLD_ID
3436: , LAST_UPDATE_DATE
3437: , LAST_UPDATED_BY
3438: , CREATION_DATE
3444: , RELEASED_FLAG
3445: , ORG_ID
3446: )
3447: SELECT
3448: OE_ORDER_HOLDS_S.NEXTVAL
3449: , SYSDATE
3450: , l_user_id
3451: , SYSDATE
3452: , l_user_id
3465: and ol.OPEN_FLAG = 'Y'
3466: -- QUOTING change
3467: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3468: and not exists ( select 'x'
3469: from oe_order_holds_ALL oh
3470: where oh.header_id = h.header_id
3471: and oh.line_id = ol.line_id
3472: and oh.hold_source_id =
3473: p_hold_source_rec.hold_source_id );
3475: Only used by Credit checking
3476: ELSIF p_hold_source_rec.hold_entity_code = 'B' and
3477: p_hold_source_rec.hold_entity_code2 = 'O' THEN
3478: IF p_hold_source_rec.line_id IS NOT NULL THEN
3479: INSERT INTO OE_ORDER_HOLDS_ALL
3480: ( ORDER_HOLD_ID
3481: , LAST_UPDATE_DATE
3482: , LAST_UPDATED_BY
3483: , CREATION_DATE
3489: , RELEASED_FLAG
3490: , ORG_ID
3491: )
3492: SELECT
3493: OE_ORDER_HOLDS_S.NEXTVAL
3494: , SYSDATE
3495: , l_user_id
3496: , SYSDATE
3497: , l_user_id
3513: and rt.CREDIT_CHECK_FLAG = 'Y'
3514: -- QUOTING change
3515: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3516: and not exists ( select 'x'
3517: from oe_order_holds_ALL oh
3518: where oh.header_id = h.header_id
3519: and oh.line_id = ol.line_id
3520: and oh.hold_source_id =
3521: p_hold_source_rec.hold_source_id );
3519: and oh.line_id = ol.line_id
3520: and oh.hold_source_id =
3521: p_hold_source_rec.hold_source_id );
3522: ELSE
3523: INSERT INTO OE_ORDER_HOLDS_ALL
3524: ( ORDER_HOLD_ID
3525: , LAST_UPDATE_DATE
3526: , LAST_UPDATED_BY
3527: , CREATION_DATE
3533: , RELEASED_FLAG
3534: , ORG_ID
3535: )
3536: SELECT
3537: OE_ORDER_HOLDS_S.NEXTVAL
3538: , SYSDATE
3539: , l_user_id
3540: , SYSDATE
3541: , l_user_id
3556: and rt.CREDIT_CHECK_FLAG = 'Y'
3557: -- QUOTING change
3558: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3559: and not exists ( select 'x'
3560: from oe_order_holds_ALL oh
3561: where oh.header_id = h.header_id
3562: and oh.line_id = ol.line_id
3563: and oh.hold_source_id =
3564: p_hold_source_rec.hold_source_id );
3566:
3567: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
3568: p_hold_source_rec.hold_entity_code2 = 'B' THEN
3569: IF p_hold_source_rec.line_id IS NOT NULL THEN
3570: INSERT INTO OE_ORDER_HOLDS_ALL
3571: ( ORDER_HOLD_ID
3572: , LAST_UPDATE_DATE
3573: , LAST_UPDATED_BY
3574: , CREATION_DATE
3580: , RELEASED_FLAG
3581: , ORG_ID
3582: )
3583: SELECT
3584: OE_ORDER_HOLDS_S.NEXTVAL
3585: , SYSDATE
3586: , l_user_id
3587: , SYSDATE
3588: , l_user_id
3602: and ol.OPEN_FLAG = 'Y'
3603: -- QUOTING change
3604: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3605: and not exists ( select 'x'
3606: from oe_order_holds_ALL oh
3607: where oh.header_id = h.header_id
3608: and oh.line_id = ol.line_id
3609: and oh.hold_source_id =
3610: p_hold_source_rec.hold_source_id );
3608: and oh.line_id = ol.line_id
3609: and oh.hold_source_id =
3610: p_hold_source_rec.hold_source_id );
3611: ELSE
3612: INSERT INTO OE_ORDER_HOLDS_ALL
3613: ( ORDER_HOLD_ID
3614: , LAST_UPDATE_DATE
3615: , LAST_UPDATED_BY
3616: , CREATION_DATE
3622: , RELEASED_FLAG
3623: , ORG_ID
3624: )
3625: SELECT
3626: OE_ORDER_HOLDS_S.NEXTVAL
3627: , SYSDATE
3628: , l_user_id
3629: , SYSDATE
3630: , l_user_id
3643: and ol.OPEN_FLAG = 'Y'
3644: -- QUOTING change
3645: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3646: and not exists ( select 'x'
3647: from oe_order_holds_ALL oh
3648: where oh.header_id = h.header_id
3649: and oh.line_id = ol.line_id
3650: and oh.hold_source_id =
3651: p_hold_source_rec.hold_source_id );
3652: END IF;
3653: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
3654: p_hold_source_rec.hold_entity_code2 = 'S' THEN
3655: IF p_hold_source_rec.line_id IS NOT NULL THEN
3656: INSERT INTO OE_ORDER_HOLDS_ALL
3657: ( ORDER_HOLD_ID
3658: , LAST_UPDATE_DATE
3659: , LAST_UPDATED_BY
3660: , CREATION_DATE
3666: , RELEASED_FLAG
3667: , ORG_ID
3668: )
3669: SELECT
3670: OE_ORDER_HOLDS_S.NEXTVAL
3671: , SYSDATE
3672: , l_user_id
3673: , SYSDATE
3674: , l_user_id
3688: and ol.OPEN_FLAG = 'Y'
3689: -- QUOTING change
3690: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3691: and not exists ( select 'x'
3692: from oe_order_holds_ALL oh
3693: where oh.header_id = h.header_id
3694: and oh.line_id = ol.line_id
3695: and oh.hold_source_id =
3696: p_hold_source_rec.hold_source_id );
3694: and oh.line_id = ol.line_id
3695: and oh.hold_source_id =
3696: p_hold_source_rec.hold_source_id );
3697: ELSE
3698: INSERT INTO OE_ORDER_HOLDS_ALL
3699: ( ORDER_HOLD_ID
3700: , LAST_UPDATE_DATE
3701: , LAST_UPDATED_BY
3702: , CREATION_DATE
3708: , RELEASED_FLAG
3709: , ORG_ID
3710: )
3711: SELECT
3712: OE_ORDER_HOLDS_S.NEXTVAL
3713: , SYSDATE
3714: , l_user_id
3715: , SYSDATE
3716: , l_user_id
3729: and ol.OPEN_FLAG = 'Y'
3730: -- QUOTING change
3731: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3732: and not exists ( select 'x'
3733: from oe_order_holds_ALL oh
3734: where oh.header_id = h.header_id
3735: and oh.line_id = ol.line_id
3736: and oh.hold_source_id =
3737: p_hold_source_rec.hold_source_id );
3738: END IF;
3739: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
3740: p_hold_source_rec.hold_entity_code2 = 'W' THEN
3741: IF p_hold_source_rec.line_id IS NOT NULL THEN
3742: INSERT INTO OE_ORDER_HOLDS_ALL
3743: ( ORDER_HOLD_ID
3744: , LAST_UPDATE_DATE
3745: , LAST_UPDATED_BY
3746: , CREATION_DATE
3752: , RELEASED_FLAG
3753: , ORG_ID
3754: )
3755: SELECT
3756: OE_ORDER_HOLDS_S.NEXTVAL
3757: , SYSDATE
3758: , l_user_id
3759: , SYSDATE
3760: , l_user_id
3774: and ol.OPEN_FLAG = 'Y'
3775: -- QUOTING change
3776: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3777: and not exists ( select 'x'
3778: from oe_order_holds_ALL oh
3779: where oh.header_id = h.header_id
3780: and oh.line_id = ol.line_id
3781: and oh.hold_source_id =
3782: p_hold_source_rec.hold_source_id );
3780: and oh.line_id = ol.line_id
3781: and oh.hold_source_id =
3782: p_hold_source_rec.hold_source_id );
3783: ELSE
3784: INSERT INTO OE_ORDER_HOLDS_ALL
3785: ( ORDER_HOLD_ID
3786: , LAST_UPDATE_DATE
3787: , LAST_UPDATED_BY
3788: , CREATION_DATE
3794: , RELEASED_FLAG
3795: , ORG_ID
3796: )
3797: SELECT
3798: OE_ORDER_HOLDS_S.NEXTVAL
3799: , SYSDATE
3800: , l_user_id
3801: , SYSDATE
3802: , l_user_id
3815: and ol.OPEN_FLAG = 'Y'
3816: -- QUOTING change
3817: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3818: and not exists ( select 'x'
3819: from oe_order_holds_ALL oh
3820: where oh.header_id = h.header_id
3821: and oh.line_id = ol.line_id
3822: and oh.hold_source_id =
3823: p_hold_source_rec.hold_source_id );
3824: END IF;
3825: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
3826: p_hold_source_rec.hold_entity_code2 = 'H' THEN
3827: IF p_hold_source_rec.line_id IS NOT NULL THEN
3828: INSERT INTO OE_ORDER_HOLDS_ALL
3829: ( ORDER_HOLD_ID
3830: , LAST_UPDATE_DATE
3831: , LAST_UPDATED_BY
3832: , CREATION_DATE
3838: , RELEASED_FLAG
3839: , ORG_ID
3840: )
3841: SELECT
3842: OE_ORDER_HOLDS_S.NEXTVAL
3843: , SYSDATE
3844: , l_user_id
3845: , SYSDATE
3846: , l_user_id
3860: and ol.OPEN_FLAG = 'Y'
3861: -- QUOTING change
3862: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3863: and not exists ( select 'x'
3864: from oe_order_holds_ALL oh
3865: where oh.header_id = h.header_id
3866: and oh.line_id = ol.line_id
3867: and oh.hold_source_id =
3868: p_hold_source_rec.hold_source_id );
3866: and oh.line_id = ol.line_id
3867: and oh.hold_source_id =
3868: p_hold_source_rec.hold_source_id );
3869: ELSE
3870: INSERT INTO OE_ORDER_HOLDS_ALL
3871: ( ORDER_HOLD_ID
3872: , LAST_UPDATE_DATE
3873: , LAST_UPDATED_BY
3874: , CREATION_DATE
3880: , RELEASED_FLAG
3881: , ORG_ID
3882: )
3883: SELECT
3884: OE_ORDER_HOLDS_S.NEXTVAL
3885: , SYSDATE
3886: , l_user_id
3887: , SYSDATE
3888: , l_user_id
3901: and ol.OPEN_FLAG = 'Y'
3902: -- QUOTING change
3903: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3904: and not exists ( select 'x'
3905: from oe_order_holds_ALL oh
3906: where oh.header_id = h.header_id
3907: and oh.line_id = ol.line_id
3908: and oh.hold_source_id =
3909: p_hold_source_rec.hold_source_id );
3911:
3912: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
3913: p_hold_source_rec.hold_entity_code2 = 'C' THEN
3914: IF p_hold_source_rec.line_id IS NOT NULL THEN
3915: INSERT INTO OE_ORDER_HOLDS_ALL
3916: ( ORDER_HOLD_ID
3917: , LAST_UPDATE_DATE
3918: , LAST_UPDATED_BY
3919: , CREATION_DATE
3925: , RELEASED_FLAG
3926: , ORG_ID
3927: )
3928: SELECT
3929: OE_ORDER_HOLDS_S.NEXTVAL
3930: , SYSDATE
3931: , l_user_id
3932: , SYSDATE
3933: , l_user_id
3947: and ol.OPEN_FLAG = 'Y'
3948: -- QUOTING change
3949: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3950: and not exists ( select 'x'
3951: from oe_order_holds_ALL oh
3952: where oh.header_id = h.header_id
3953: and oh.line_id = ol.line_id
3954: and oh.hold_source_id =
3955: p_hold_source_rec.hold_source_id );
3953: and oh.line_id = ol.line_id
3954: and oh.hold_source_id =
3955: p_hold_source_rec.hold_source_id );
3956: ELSE
3957: INSERT INTO OE_ORDER_HOLDS_ALL
3958: ( ORDER_HOLD_ID
3959: , LAST_UPDATE_DATE
3960: , LAST_UPDATED_BY
3961: , CREATION_DATE
3967: , RELEASED_FLAG
3968: , ORG_ID
3969: )
3970: SELECT
3971: OE_ORDER_HOLDS_S.NEXTVAL
3972: , SYSDATE
3973: , l_user_id
3974: , SYSDATE
3975: , l_user_id
3988: and ol.OPEN_FLAG = 'Y'
3989: -- QUOTING change
3990: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
3991: and not exists ( select 'x'
3992: from oe_order_holds_ALL oh
3993: where oh.header_id = h.header_id
3994: and oh.line_id = ol.line_id
3995: and oh.hold_source_id =
3996: p_hold_source_rec.hold_source_id );
3997: END IF;
3998: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
3999: p_hold_source_rec.hold_entity_code2 = 'B' THEN
4000: IF p_hold_source_rec.line_id IS NOT NULL THEN
4001: INSERT INTO OE_ORDER_HOLDS_ALL
4002: ( ORDER_HOLD_ID
4003: , LAST_UPDATE_DATE
4004: , LAST_UPDATED_BY
4005: , CREATION_DATE
4011: , RELEASED_FLAG
4012: , ORG_ID
4013: )
4014: SELECT
4015: OE_ORDER_HOLDS_S.NEXTVAL
4016: , SYSDATE
4017: , l_user_id
4018: , SYSDATE
4019: , l_user_id
4033: and ol.OPEN_FLAG = 'Y'
4034: -- QUOTING change
4035: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4036: AND NOT EXISTS ( SELECT 'x'
4037: FROM oe_order_holds_ALL oh
4038: WHERE oh.header_id = h.header_id
4039: AND oh.line_id = ol.line_id
4040: AND oh.hold_source_id =
4041: p_hold_source_rec.hold_source_id );
4039: AND oh.line_id = ol.line_id
4040: AND oh.hold_source_id =
4041: p_hold_source_rec.hold_source_id );
4042: ELSE
4043: INSERT INTO OE_ORDER_HOLDS_ALL
4044: ( ORDER_HOLD_ID
4045: , LAST_UPDATE_DATE
4046: , LAST_UPDATED_BY
4047: , CREATION_DATE
4053: , RELEASED_FLAG
4054: , ORG_ID
4055: )
4056: SELECT
4057: OE_ORDER_HOLDS_S.NEXTVAL
4058: , SYSDATE
4059: , l_user_id
4060: , SYSDATE
4061: , l_user_id
4074: and ol.OPEN_FLAG = 'Y'
4075: -- QUOTING change
4076: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4077: AND NOT EXISTS ( SELECT 'x'
4078: FROM oe_order_holds_ALL oh
4079: WHERE oh.header_id = h.header_id
4080: AND oh.line_id = ol.line_id
4081: AND oh.hold_source_id =
4082: p_hold_source_rec.hold_source_id );
4083: END IF;
4084: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
4085: p_hold_source_rec.hold_entity_code2 = 'S' THEN
4086: IF p_hold_source_rec.line_id IS NOT NULL THEN
4087: INSERT INTO OE_ORDER_HOLDS_ALL
4088: ( ORDER_HOLD_ID
4089: , LAST_UPDATE_DATE
4090: , LAST_UPDATED_BY
4091: , CREATION_DATE
4097: , RELEASED_FLAG
4098: , ORG_ID
4099: )
4100: SELECT
4101: OE_ORDER_HOLDS_S.NEXTVAL
4102: , SYSDATE
4103: , l_user_id
4104: , SYSDATE
4105: , l_user_id
4119: and ol.OPEN_FLAG = 'Y'
4120: -- QUOTING change
4121: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4122: AND NOT EXISTS ( SELECT 'x'
4123: FROM oe_order_holds_ALL oh
4124: WHERE oh.header_id = h.header_id
4125: AND oh.line_id = ol.line_id
4126: AND oh.hold_source_id =
4127: p_hold_source_rec.hold_source_id );
4125: AND oh.line_id = ol.line_id
4126: AND oh.hold_source_id =
4127: p_hold_source_rec.hold_source_id );
4128: ELSE
4129: INSERT INTO OE_ORDER_HOLDS_ALL
4130: ( ORDER_HOLD_ID
4131: , LAST_UPDATE_DATE
4132: , LAST_UPDATED_BY
4133: , CREATION_DATE
4139: , RELEASED_FLAG
4140: , ORG_ID
4141: )
4142: SELECT
4143: OE_ORDER_HOLDS_S.NEXTVAL
4144: , SYSDATE
4145: , l_user_id
4146: , SYSDATE
4147: , l_user_id
4160: and ol.OPEN_FLAG = 'Y'
4161: -- QUOTING change
4162: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4163: AND NOT EXISTS ( SELECT 'x'
4164: FROM oe_order_holds_ALL oh
4165: WHERE oh.header_id = h.header_id
4166: AND oh.line_id = ol.line_id
4167: AND oh.hold_source_id =
4168: p_hold_source_rec.hold_source_id );
4169: END IF;
4170: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
4171: p_hold_source_rec.hold_entity_code2 = 'B' THEN
4172: IF p_hold_source_rec.line_id IS NOT NULL THEN
4173: INSERT INTO OE_ORDER_HOLDS_ALL
4174: ( ORDER_HOLD_ID
4175: , LAST_UPDATE_DATE
4176: , LAST_UPDATED_BY
4177: , CREATION_DATE
4183: , RELEASED_FLAG
4184: , ORG_ID
4185: )
4186: SELECT
4187: OE_ORDER_HOLDS_S.NEXTVAL
4188: , SYSDATE
4189: , l_user_id
4190: , SYSDATE
4191: , l_user_id
4205: and ol.OPEN_FLAG = 'Y'
4206: -- QUOTING change
4207: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4208: and not exists ( select 'x'
4209: from oe_order_holds_ALL oh
4210: where oh.header_id = h.header_id
4211: and oh.line_id = ol.line_id
4212: and oh.hold_source_id =
4213: p_hold_source_rec.hold_source_id );
4211: and oh.line_id = ol.line_id
4212: and oh.hold_source_id =
4213: p_hold_source_rec.hold_source_id );
4214: ELSE
4215: INSERT INTO OE_ORDER_HOLDS_ALL
4216: ( ORDER_HOLD_ID
4217: , LAST_UPDATE_DATE
4218: , LAST_UPDATED_BY
4219: , CREATION_DATE
4225: , RELEASED_FLAG
4226: , ORG_ID
4227: )
4228: SELECT
4229: OE_ORDER_HOLDS_S.NEXTVAL
4230: , SYSDATE
4231: , l_user_id
4232: , SYSDATE
4233: , l_user_id
4246: and ol.OPEN_FLAG = 'Y'
4247: -- QUOTING change
4248: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4249: and not exists ( select 'x'
4250: from oe_order_holds_ALL oh
4251: where oh.header_id = h.header_id
4252: and oh.line_id = ol.line_id
4253: and oh.hold_source_id =
4254: p_hold_source_rec.hold_source_id );
4256:
4257: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
4258: p_hold_source_rec.hold_entity_code2 = 'S' THEN
4259: IF p_hold_source_rec.line_id IS NOT NULL THEN
4260: INSERT INTO OE_ORDER_HOLDS_ALL
4261: ( ORDER_HOLD_ID
4262: , LAST_UPDATE_DATE
4263: , LAST_UPDATED_BY
4264: , CREATION_DATE
4270: , RELEASED_FLAG
4271: , ORG_ID
4272: )
4273: SELECT
4274: OE_ORDER_HOLDS_S.NEXTVAL
4275: , SYSDATE
4276: , l_user_id
4277: , SYSDATE
4278: , l_user_id
4292: and ol.OPEN_FLAG = 'Y'
4293: -- QUOTING change
4294: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4295: and not exists ( select 'x'
4296: from oe_order_holds_ALL oh
4297: where oh.header_id = h.header_id
4298: and oh.line_id = ol.line_id
4299: and oh.hold_source_id =
4300: p_hold_source_rec.hold_source_id );
4298: and oh.line_id = ol.line_id
4299: and oh.hold_source_id =
4300: p_hold_source_rec.hold_source_id );
4301: ELSE
4302: INSERT INTO OE_ORDER_HOLDS_ALL
4303: ( ORDER_HOLD_ID
4304: , LAST_UPDATE_DATE
4305: , LAST_UPDATED_BY
4306: , CREATION_DATE
4312: , RELEASED_FLAG
4313: , ORG_ID
4314: )
4315: SELECT
4316: OE_ORDER_HOLDS_S.NEXTVAL
4317: , SYSDATE
4318: , l_user_id
4319: , SYSDATE
4320: , l_user_id
4333: and ol.OPEN_FLAG = 'Y'
4334: -- QUOTING change
4335: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4336: and not exists ( select 'x'
4337: from oe_order_holds_ALL oh
4338: where oh.header_id = h.header_id
4339: and oh.line_id = ol.line_id
4340: and oh.hold_source_id =
4341: p_hold_source_rec.hold_source_id );
4342: END IF;
4343: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
4344: p_hold_source_rec.hold_entity_code2 = 'W' THEN
4345: IF p_hold_source_rec.line_id IS NOT NULL THEN
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
4378: and ol.OPEN_FLAG = 'Y'
4379: -- QUOTING change
4380: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4381: and not exists ( select 'x'
4382: from oe_order_holds_ALL oh
4383: where oh.header_id = h.header_id
4384: and oh.line_id = ol.line_id
4385: and oh.hold_source_id =
4386: p_hold_source_rec.hold_source_id );
4384: and oh.line_id = ol.line_id
4385: and oh.hold_source_id =
4386: p_hold_source_rec.hold_source_id );
4387: ELSE
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 );
4429:
4430: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
4431: p_hold_source_rec.hold_entity_code2 = 'L' THEN
4432: IF p_hold_source_rec.line_id IS NOT NULL THEN
4433: INSERT INTO OE_ORDER_HOLDS_ALL
4434: ( ORDER_HOLD_ID
4435: , LAST_UPDATE_DATE
4436: , LAST_UPDATED_BY
4437: , CREATION_DATE
4443: , RELEASED_FLAG
4444: , ORG_ID
4445: )
4446: SELECT
4447: OE_ORDER_HOLDS_S.NEXTVAL
4448: , SYSDATE
4449: , l_user_id
4450: , SYSDATE
4451: , l_user_id
4465: and ol.OPEN_FLAG = 'Y'
4466: -- QUOTING change
4467: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4468: and not exists ( select 'x'
4469: from oe_order_holds_ALL oh
4470: where oh.header_id = h.header_id
4471: and oh.line_id = ol.line_id
4472: and oh.hold_source_id =
4473: p_hold_source_rec.hold_source_id );
4471: and oh.line_id = ol.line_id
4472: and oh.hold_source_id =
4473: p_hold_source_rec.hold_source_id );
4474: ELSE
4475: INSERT INTO OE_ORDER_HOLDS_ALL
4476: ( ORDER_HOLD_ID
4477: , LAST_UPDATE_DATE
4478: , LAST_UPDATED_BY
4479: , CREATION_DATE
4485: , RELEASED_FLAG
4486: , ORG_ID
4487: )
4488: SELECT
4489: OE_ORDER_HOLDS_S.NEXTVAL
4490: , SYSDATE
4491: , l_user_id
4492: , SYSDATE
4493: , l_user_id
4506: and ol.OPEN_FLAG = 'Y'
4507: -- QUOTING change
4508: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4509: and not exists ( select 'x'
4510: from oe_order_holds_ALL oh
4511: where oh.header_id = h.header_id
4512: and oh.line_id = ol.line_id
4513: and oh.hold_source_id =
4514: p_hold_source_rec.hold_source_id );
4515: END IF;
4516:
4517: ELSIF p_hold_source_rec.hold_entity_code = 'H' THEN
4518: IF p_hold_source_rec.line_id IS NOT NULL THEN
4519: INSERT INTO OE_ORDER_HOLDS_ALL
4520: ( ORDER_HOLD_ID
4521: , LAST_UPDATE_DATE
4522: , LAST_UPDATED_BY
4523: , CREATION_DATE
4529: , RELEASED_FLAG
4530: , ORG_ID
4531: )
4532: SELECT
4533: OE_ORDER_HOLDS_S.NEXTVAL
4534: , SYSDATE
4535: , l_user_id
4536: , SYSDATE
4537: , l_user_id
4550: and ol.OPEN_FLAG = 'Y'
4551: -- QUOTING change
4552: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4553: and not exists ( select 'x'
4554: from oe_order_holds_ALL oh
4555: where oh.header_id = h.header_id
4556: and oh.line_id = ol.line_id
4557: and oh.hold_source_id =
4558: p_hold_source_rec.hold_source_id );
4556: and oh.line_id = ol.line_id
4557: and oh.hold_source_id =
4558: p_hold_source_rec.hold_source_id );
4559: ELSE
4560: INSERT INTO OE_ORDER_HOLDS_ALL
4561: ( ORDER_HOLD_ID
4562: , LAST_UPDATE_DATE
4563: , LAST_UPDATED_BY
4564: , CREATION_DATE
4570: , RELEASED_FLAG
4571: , ORG_ID
4572: )
4573: SELECT
4574: OE_ORDER_HOLDS_S.NEXTVAL
4575: , SYSDATE
4576: , l_user_id
4577: , SYSDATE
4578: , l_user_id
4590: and ol.OPEN_FLAG = 'Y'
4591: -- QUOTING change
4592: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4593: and not exists ( select 'x'
4594: from oe_order_holds_ALL oh
4595: where oh.header_id = h.header_id
4596: and oh.line_id = ol.line_id
4597: and oh.hold_source_id =
4598: p_hold_source_rec.hold_source_id );
4599: END IF;
4600:
4601: ELSIF p_hold_source_rec.hold_entity_code = 'O' THEN
4602: IF p_hold_source_rec.line_id is NULL THEN
4603: INSERT INTO OE_ORDER_HOLDS_ALL
4604: ( ORDER_HOLD_ID
4605: , LAST_UPDATE_DATE
4606: , LAST_UPDATED_BY
4607: , CREATION_DATE
4613: , RELEASED_FLAG
4614: , ORG_ID
4615: )
4616: SELECT
4617: OE_ORDER_HOLDS_S.NEXTVAL
4618: , SYSDATE
4619: , l_user_id
4620: , SYSDATE
4621: , l_user_id
4630: and h.header_id = p_hold_source_rec.hold_entity_id
4631: -- QUOTING change
4632: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4633: and not exists ( select 'x'
4634: from oe_order_holds_ALL oh
4635: where oh.header_id = h.header_id
4636: and oh.hold_source_id =
4637: p_hold_source_rec.hold_source_id );
4638: ELSE
4635: where oh.header_id = h.header_id
4636: and oh.hold_source_id =
4637: p_hold_source_rec.hold_source_id );
4638: ELSE
4639: INSERT INTO OE_ORDER_HOLDS_ALL
4640: ( ORDER_HOLD_ID
4641: , LAST_UPDATE_DATE
4642: , LAST_UPDATED_BY
4643: , CREATION_DATE
4649: , RELEASED_FLAG
4650: , ORG_ID
4651: )
4652: SELECT
4653: OE_ORDER_HOLDS_S.NEXTVAL
4654: , SYSDATE
4655: , l_user_id
4656: , SYSDATE
4657: , l_user_id
4669: and ol.open_flag = 'Y'
4670: -- QUOTING change
4671: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4672: and not exists ( select 'x'
4673: from oe_order_holds_ALL oh
4674: where oh.header_id = h.header_id
4675: and oh.line_id = ol.line_id
4676: and oh.hold_source_id =
4677: p_hold_source_rec.hold_source_id );
4679:
4680: ELSIF p_hold_source_rec.hold_entity_code = 'C' THEN
4681: -- Use header_id for Customer based hold source
4682: IF p_hold_source_rec.header_id IS NOT NULL THEN
4683: INSERT INTO OE_ORDER_HOLDS_ALL
4684: ( ORDER_HOLD_ID
4685: , LAST_UPDATE_DATE
4686: , LAST_UPDATED_BY
4687: , CREATION_DATE
4693: , RELEASED_FLAG
4694: , ORG_ID
4695: )
4696: SELECT
4697: OE_ORDER_HOLDS_S.NEXTVAL
4698: , SYSDATE
4699: , l_user_id
4700: , SYSDATE
4701: , l_user_id
4712: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
4713: -- QUOTING change
4714: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4715: and not exists ( select 'x'
4716: from oe_order_holds_ALL oh
4717: where oh.header_id = h.header_id
4718: and oh.hold_source_id =
4719: p_hold_source_rec.hold_source_id );
4720: ELSE
4717: where oh.header_id = h.header_id
4718: and oh.hold_source_id =
4719: p_hold_source_rec.hold_source_id );
4720: ELSE
4721: INSERT INTO OE_ORDER_HOLDS_ALL
4722: ( ORDER_HOLD_ID
4723: , LAST_UPDATE_DATE
4724: , LAST_UPDATED_BY
4725: , CREATION_DATE
4731: , RELEASED_FLAG
4732: , ORG_ID
4733: )
4734: SELECT
4735: OE_ORDER_HOLDS_S.NEXTVAL
4736: , SYSDATE
4737: , l_user_id
4738: , SYSDATE
4739: , l_user_id
4749: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
4750: -- QUOTING change
4751: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4752: and not exists ( select 'x'
4753: from oe_order_holds_ALL oh
4754: where oh.header_id = h.header_id
4755: and oh.hold_source_id =
4756: p_hold_source_rec.hold_source_id );
4757: END IF;
4756: p_hold_source_rec.hold_source_id );
4757: END IF;
4758: ELSIF p_hold_source_rec.hold_entity_code = 'B' THEN
4759: IF p_hold_source_rec.line_id IS NOT NULL THEN
4760: INSERT INTO OE_ORDER_HOLDS_ALL
4761: ( ORDER_HOLD_ID
4762: , LAST_UPDATE_DATE
4763: , LAST_UPDATED_BY
4764: , CREATION_DATE
4770: , RELEASED_FLAG
4771: , ORG_ID
4772: )
4773: SELECT
4774: OE_ORDER_HOLDS_S.NEXTVAL
4775: , SYSDATE
4776: , l_user_id
4777: , SYSDATE
4778: , l_user_id
4791: and ol.OPEN_FLAG = 'Y'
4792: -- QUOTING change
4793: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4794: and not exists ( select 'x'
4795: from oe_order_holds_ALL oh
4796: where oh.header_id = h.header_id
4797: and oh.line_id = ol.line_id
4798: and oh.hold_source_id =
4799: p_hold_source_rec.hold_source_id );
4797: and oh.line_id = ol.line_id
4798: and oh.hold_source_id =
4799: p_hold_source_rec.hold_source_id );
4800: ELSE
4801: INSERT INTO OE_ORDER_HOLDS_ALL
4802: ( ORDER_HOLD_ID
4803: , LAST_UPDATE_DATE
4804: , LAST_UPDATED_BY
4805: , CREATION_DATE
4811: , RELEASED_FLAG
4812: , ORG_ID
4813: )
4814: SELECT
4815: OE_ORDER_HOLDS_S.NEXTVAL
4816: , SYSDATE
4817: , l_user_id
4818: , SYSDATE
4819: , l_user_id
4831: and ol.OPEN_FLAG = 'Y'
4832: -- QUOTING change
4833: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4834: and not exists ( select 'x'
4835: from oe_order_holds_ALL oh
4836: where oh.header_id = h.header_id
4837: and oh.line_id = ol.line_id
4838: and oh.hold_source_id =
4839: p_hold_source_rec.hold_source_id );
4839: p_hold_source_rec.hold_source_id );
4840: END IF;
4841: ELSIF p_hold_source_rec.hold_entity_code = 'S' THEN
4842: IF p_hold_source_rec.line_id IS NOT NULL THEN
4843: INSERT INTO OE_ORDER_HOLDS_ALL
4844: ( ORDER_HOLD_ID
4845: , LAST_UPDATE_DATE
4846: , LAST_UPDATED_BY
4847: , CREATION_DATE
4853: , RELEASED_FLAG
4854: , ORG_ID
4855: )
4856: SELECT
4857: OE_ORDER_HOLDS_S.NEXTVAL
4858: , SYSDATE
4859: , l_user_id
4860: , SYSDATE
4861: , l_user_id
4874: and ol.OPEN_FLAG = 'Y'
4875: -- QUOTING change
4876: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4877: and not exists ( select 'x'
4878: from oe_order_holds_ALL oh
4879: where oh.header_id = h.header_id
4880: and oh.line_id = ol.line_id
4881: and oh.hold_source_id =
4882: p_hold_source_rec.hold_source_id );
4880: and oh.line_id = ol.line_id
4881: and oh.hold_source_id =
4882: p_hold_source_rec.hold_source_id );
4883: ELSE
4884: INSERT INTO OE_ORDER_HOLDS_ALL
4885: ( ORDER_HOLD_ID
4886: , LAST_UPDATE_DATE
4887: , LAST_UPDATED_BY
4888: , CREATION_DATE
4894: , RELEASED_FLAG
4895: , ORG_ID
4896: )
4897: SELECT
4898: OE_ORDER_HOLDS_S.NEXTVAL
4899: , SYSDATE
4900: , l_user_id
4901: , SYSDATE
4902: , l_user_id
4914: and ol.OPEN_FLAG = 'Y'
4915: -- QUOTING change
4916: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4917: and not exists ( select 'x'
4918: from oe_order_holds_ALL oh
4919: where oh.header_id = h.header_id
4920: and oh.line_id = ol.line_id
4921: and oh.hold_source_id =
4922: p_hold_source_rec.hold_source_id );
4922: p_hold_source_rec.hold_source_id );
4923: END IF;
4924: ELSIF p_hold_source_rec.hold_entity_code = 'W' THEN
4925: IF p_hold_source_rec.line_id IS NOT NULL THEN
4926: INSERT INTO OE_ORDER_HOLDS_ALL
4927: ( ORDER_HOLD_ID
4928: , LAST_UPDATE_DATE
4929: , LAST_UPDATED_BY
4930: , CREATION_DATE
4936: , RELEASED_FLAG
4937: , ORG_ID
4938: )
4939: SELECT
4940: OE_ORDER_HOLDS_S.NEXTVAL
4941: , SYSDATE
4942: , l_user_id
4943: , SYSDATE
4944: , l_user_id
4957: and ol.OPEN_FLAG = 'Y'
4958: -- QUOTING change
4959: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
4960: and not exists ( select 'x'
4961: from oe_order_holds_ALL oh
4962: where oh.header_id = h.header_id
4963: and oh.line_id = ol.line_id
4964: and oh.hold_source_id =
4965: p_hold_source_rec.hold_source_id );
4963: and oh.line_id = ol.line_id
4964: and oh.hold_source_id =
4965: p_hold_source_rec.hold_source_id );
4966: ELSE
4967: INSERT INTO OE_ORDER_HOLDS_ALL
4968: ( ORDER_HOLD_ID
4969: , LAST_UPDATE_DATE
4970: , LAST_UPDATED_BY
4971: , CREATION_DATE
4977: , RELEASED_FLAG
4978: , ORG_ID
4979: )
4980: SELECT
4981: OE_ORDER_HOLDS_S.NEXTVAL
4982: , SYSDATE
4983: , l_user_id
4984: , SYSDATE
4985: , l_user_id
4997: and ol.OPEN_FLAG = 'Y'
4998: -- QUOTING change
4999: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
5000: and not exists ( select 'x'
5001: from oe_order_holds_ALL oh
5002: where oh.header_id = h.header_id
5003: and oh.line_id = ol.line_id
5004: and oh.hold_source_id =
5005: p_hold_source_rec.hold_source_id );
5005: p_hold_source_rec.hold_source_id );
5006: END IF;
5007: ELSIF p_hold_source_rec.hold_entity_code = 'I' THEN
5008: IF p_hold_source_rec.line_id IS NOT NULL THEN
5009: INSERT INTO OE_ORDER_HOLDS_ALL
5010: ( ORDER_HOLD_ID
5011: , LAST_UPDATE_DATE
5012: , LAST_UPDATED_BY
5013: , CREATION_DATE
5019: , RELEASED_FLAG
5020: , ORG_ID
5021: )
5022: SELECT
5023: OE_ORDER_HOLDS_S.NEXTVAL
5024: , SYSDATE
5025: , l_user_id
5026: , SYSDATE
5027: , l_user_id
5040: and ol.OPEN_FLAG = 'Y'
5041: -- QUOTING change
5042: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
5043: and not exists ( select 'x'
5044: from oe_order_holds_ALL oh
5045: where oh.header_id = h.header_id
5046: and oh.line_id = ol.line_id
5047: and oh.hold_source_id =
5048: p_hold_source_rec.hold_source_id );
5046: and oh.line_id = ol.line_id
5047: and oh.hold_source_id =
5048: p_hold_source_rec.hold_source_id );
5049: ELSE
5050: INSERT INTO OE_ORDER_HOLDS_ALL
5051: ( ORDER_HOLD_ID
5052: , LAST_UPDATE_DATE
5053: , LAST_UPDATED_BY
5054: , CREATION_DATE
5060: , RELEASED_FLAG
5061: , ORG_ID
5062: )
5063: SELECT
5064: OE_ORDER_HOLDS_S.NEXTVAL
5065: , SYSDATE
5066: , l_user_id
5067: , SYSDATE
5068: , l_user_id
5080: and ol.OPEN_FLAG = 'Y'
5081: -- QUOTING change
5082: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
5083: and not exists ( select 'x'
5084: from oe_order_holds_ALL oh
5085: where oh.header_id = h.header_id
5086: and oh.line_id = ol.line_id
5087: and oh.hold_source_id =
5088: p_hold_source_rec.hold_source_id );
5259:
5260: cursor header_line_id_cur(p_ord_hld_id in number) is
5261: select ohld.header_id,
5262: ohld.line_id
5263: from oe_order_holds ohld,
5264: oe_order_headers ooh,
5265: oe_order_sources src
5266: where ohld.order_hold_id = p_ord_hld_id
5267: and ohld.header_id = ooh.header_id
5281: x_return_status := FND_API.G_RET_STS_SUCCESS;
5282: l_user_id := OE_HOLDS_PVT.get_user_id;
5283: -- Flag all orders and order line holds for this hold source
5284: -- as released
5285: UPDATE oe_order_holds
5286: SET hold_release_id = p_hold_release_rec.hold_release_id,
5287: LAST_UPDATED_BY = l_user_id,
5288: LAST_UPDATE_DATE = sysdate,
5289: RELEASED_FLAG = 'Y'
5315: SELECT ohld.header_id,
5316: ohld.line_id
5317: INTO l_header_id,
5318: l_line_id
5319: FROM oe_order_holds ohld,
5320: oe_order_headers ooh
5321: WHERE ohld.order_hold_id = p_hold_release_rec.order_hold_id
5322: AND ohld.header_id = ooh.header_id;
5323: EXCEPTION
5578: -- nvl(p_hold_source_rec.hold_entity_code2, 'NO_ENTITY_CODE2')
5579: -- AND nvl(HS.HOLD_ENTITY_ID2, -99) =
5580: -- nvl(p_hold_source_rec.hold_entity_id2, -99)
5581: AND EXISTS (select 1
5582: from oe_order_holds oh
5583: where oh.hold_source_id = hs.hold_source_id
5584: and oh.header_id =
5585: nvl(p_hold_source_rec.header_id, oh.header_id)
5586: and nvl(oh.line_id, -99) =
5761:
5762:
5763: ----------------------------------------------------------------------------
5764: -- Delete Holds
5765: -- Deletes from OE_ORDER_HOLDS all hold records for an order (p_header_id)
5766: -- or for a line (p_line_id).
5767: -- Also, if there are ORDER hold sources (hold_entity_code = 'O') for this
5768: -- order, deletes hold source records from OE_HOLD_SOURCES.
5769: -- If the hold or hold source was released and the same release record is
5783: l_hold_source_id NUMBER;
5784: l_hold_release_id NUMBER := 0;
5785: CURSOR order_hold IS
5786: SELECT order_hold_id, NVL(hold_release_id,0)
5787: FROM OE_ORDER_HOLDS_all
5788: WHERE HEADER_ID = p_order_rec.header_id;
5789: CURSOR hold_source IS
5790: SELECT hold_source_id, NVL(hold_release_id,0)
5791: FROM OE_HOLD_SOURCES_all
5792: WHERE HOLD_ENTITY_CODE = 'O'
5793: AND HOLD_ENTITY_ID = p_order_rec.header_id;
5794: CURSOR line_hold IS
5795: SELECT order_hold_id, NVL(hold_release_id,0)
5796: FROM OE_ORDER_HOLDS_all
5797: WHERE LINE_ID = p_order_rec.line_id;
5798:
5799: BEGIN
5800: -- Standard Start of API savepoint
5814: RAISE FND_API.G_EXC_ERROR;
5815:
5816: END IF;
5817:
5818: -- Delete the hold records corr. to this order or line in OE_ORDER_HOLDS
5819:
5820: IF p_order_rec.line_id = FND_API.G_MISS_NUM THEN
5821:
5822: -- Delete order hold records
5830: END IF;
5831:
5832: OE_Debug_PUB.Add('Deleting order hold record',1);
5833:
5834: DELETE FROM OE_ORDER_HOLDS_all
5835: WHERE order_hold_id = l_order_hold_id;
5836:
5837: OE_Debug_PUB.Add('Deleting hold release record',1);
5838: DELETE FROM OE_HOLD_RELEASES
5837: OE_Debug_PUB.Add('Deleting hold release record',1);
5838: DELETE FROM OE_HOLD_RELEASES
5839: WHERE HOLD_RELEASE_ID = l_hold_release_id
5840: AND HOLD_RELEASE_ID NOT IN (SELECT NVL(HOLD_RELEASE_ID,0)
5841: FROM OE_ORDER_HOLDS_all
5842: UNION
5843: SELECT NVL(HOLD_RELEASE_ID,0)
5844: FROM OE_HOLD_SOURCES_all
5845: );
5863: DELETE FROM OE_HOLD_RELEASES
5864: WHERE HOLD_RELEASE_ID = l_hold_release_id
5865: AND HOLD_RELEASE_ID NOT IN
5866: ( SELECT NVL(HOLD_RELEASE_ID,0)
5867: FROM OE_ORDER_HOLDS_all
5868: UNION
5869: SELECT NVL(HOLD_RELEASE_ID,0)
5870: FROM OE_HOLD_SOURCES_all
5871: );
5889: END IF;
5890:
5891: OE_Debug_PUB.Add('Deleting order hold record',1);
5892:
5893: DELETE FROM OE_ORDER_HOLDS
5894: WHERE order_hold_id = l_order_hold_id;
5895:
5896: DELETE FROM OE_HOLD_RELEASES
5897: WHERE HOLD_RELEASE_ID = l_hold_release_id
5896: DELETE FROM OE_HOLD_RELEASES
5897: WHERE HOLD_RELEASE_ID = l_hold_release_id
5898: AND HOLD_RELEASE_ID NOT IN
5899: (SELECT NVL(HOLD_RELEASE_ID,0)
5900: FROM OE_ORDER_HOLDS_all
5901: UNION
5902: SELECT NVL(HOLD_RELEASE_ID,0)
5903: FROM OE_HOLD_SOURCES_all
5904: );
6563: -- When release holds for orders, check to see if this order was put on
6564: -- hold as an
6565: -- Order based hold. If yes, then release the Hold_Source from hold and
6566: -- also order
6567: -- from hold in oe_order_holds and insert record in oe_hold_releases with
6568: -- order_hold_id(No with Hold Source ID).
6569: -- IF the order being releasesed is part of a different hold source (e.g. 'C')
6570: -- then don't release the hold source(what if its the last order)
6571: -- and only release the order from hold in
6568: -- order_hold_id(No with Hold Source ID).
6569: -- IF the order being releasesed is part of a different hold source (e.g. 'C')
6570: -- then don't release the hold source(what if its the last order)
6571: -- and only release the order from hold in
6572: -- OE_ORDER_HOLDS and create a record in OE_HOLD_RELEASES with the
6573: -- Order_Hold_ID.
6574: Validate_Order (p_order_tbl(j).header_id,
6575: p_order_tbl(j).line_id,
6576: x_return_status => x_return_status,
6607: IF entity_code = 'O' THEN
6608: BEGIN
6609: SELECT 'NO HOLDS'
6610: INTO l_dummy
6611: FROM OE_ORDER_HOLDS
6612: WHERE HOLD_SOURCE_ID = l_hold_source_id
6613: AND HOLD_RELEASE_ID IS NULL
6614: AND ROWNUM = 1;
6615:
8589: , hs.ATTRIBUTE13
8590: , hs.ATTRIBUTE14
8591: , hs.ATTRIBUTE15
8592: , oh.org_id
8593: FROM OE_ORDER_HOLDS_all oh,
8594: OE_HOLD_SOURCES_all hs
8595: WHERE oh.line_id = p_split_from_line_id
8596: AND oh.RELEASED_FLAG = 'N'
8597: AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID;
8647: l_hold_source_id := c_rec.hold_source_id;
8648: --l_hold_source_rec.hold_source_id := c_rec.hold_source_id;
8649: END IF;
8650:
8651: INSERT INTO OE_ORDER_HOLDS_all
8652: ( ORDER_HOLD_ID
8653: , LAST_UPDATE_DATE
8654: , LAST_UPDATED_BY
8655: , CREATION_DATE
8661: , RELEASED_FLAG
8662: , ORG_ID
8663: )
8664: VALUES (
8665: OE_ORDER_HOLDS_S.NEXTVAL
8666: , SYSDATE
8667: , l_user_id
8668: , SYSDATE
8669: , l_user_id
8918: CURSOR check_line_hold_type_cur(p_line_id IN NUMBER) IS
8919: SELECT 'Y'
8920: FROM DUAL
8921: WHERE EXISTS (SELECT NULL
8922: FROM oe_order_holds ooh,
8923: oe_hold_sources ohs,
8924: oe_hold_definitions ohd,
8925: oe_order_headers_all h,
8926: oe_order_sources oos
8935: CURSOR check_hdr_hold_type_cur(p_hdr_id IN NUMBER) IS
8936: SELECT 'Y'
8937: FROM DUAL
8938: WHERE EXISTS (SELECT NULL
8939: FROM oe_order_holds ooh,
8940: oe_hold_sources ohs,
8941: oe_hold_definitions ohd,
8942: oe_order_headers_all h,
8943: oe_order_sources oos
8964: CURSOR check_line_hold_type_cur(p_line_id IN NUMBER) IS
8965: SELECT 'Y'
8966: FROM DUAL
8967: WHERE EXISTS (SELECT NULL
8968: FROM oe_order_holds_all ooh,
8969: oe_hold_sources_all ohs,
8970: oe_hold_definitions ohd,
8971: oe_order_headers_all h,
8972: oe_order_sources oos
8983: CURSOR check_hdr_hold_type_cur(p_hdr_id IN NUMBER) IS
8984: SELECT 'Y'
8985: FROM DUAL
8986: WHERE EXISTS (SELECT NULL
8987: FROM oe_order_holds_all ooh,
8988: oe_hold_sources_all ohs,
8989: oe_hold_definitions ohd,
8990: oe_order_headers_all h,
8991: oe_order_sources oos
9086:
9087:
9088: /*ER#7479609 start
9089: IF p_hold_source_rec.line_id IS NOT NULL THEN
9090: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9091: ( ORDER_HOLD_ID
9092: , LAST_UPDATE_DATE
9093: , LAST_UPDATED_BY
9094: , CREATION_DATE
9100: , RELEASED_FLAG
9101: , ORG_ID
9102: )
9103: SELECT
9104: OE_ORDER_HOLDS_S.NEXTVAL
9105: , SYSDATE
9106: , :l_user_id
9107: , SYSDATE
9108: , :l_user_id
9123: and ol.OPEN_FLAG = ''Y''
9124: -- QUOTING change
9125: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9126: and not exists ( select ''x''
9127: from oe_order_holds_ALL oh
9128: where oh.header_id = h.header_id
9129: and oh.line_id = ol.line_id
9130: and oh.hold_source_id = :hold_source_id )';
9131: IF p_item_type is not null and p_activity_name is not null then
9163: END IF;
9164: END IF;
9165:
9166: ELSE
9167: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9168: ( ORDER_HOLD_ID
9169: , LAST_UPDATE_DATE
9170: , LAST_UPDATED_BY
9171: , CREATION_DATE
9177: , RELEASED_FLAG
9178: , ORG_ID
9179: )
9180: SELECT
9181: OE_ORDER_HOLDS_S.NEXTVAL
9182: , SYSDATE
9183: , :l_user_id
9184: , SYSDATE
9185: , :l_user_id
9199: and ol.OPEN_FLAG = ''Y''
9200: -- QUOTING change
9201: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9202: and not exists ( select ''x''
9203: from oe_order_holds_ALL oh
9204: where oh.header_id = h.header_id
9205: and oh.line_id = ol.line_id
9206: and oh.hold_source_id =:hold_source_id )';
9207:
9256: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9257: and ol.OPEN_FLAG = 'Y'
9258: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
9259: and not exists ( select 'x'
9260: from oe_order_holds_ALL oh
9261: where oh.header_id = h.header_id
9262: and oh.line_id = ol.line_id
9263: and oh.hold_source_id =
9264: p_hold_source_rec.hold_source_id );
9412: --ER#7479609 end
9413:
9414: /*ER#7479609 start
9415: IF p_hold_source_rec.line_id IS NOT NULL THEN
9416: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9417: ( ORDER_HOLD_ID
9418: , LAST_UPDATE_DATE
9419: , LAST_UPDATED_BY
9420: , CREATION_DATE
9426: , RELEASED_FLAG
9427: , ORG_ID
9428: )
9429: SELECT
9430: OE_ORDER_HOLDS_S.NEXTVAL
9431: , SYSDATE
9432: , :l_user_id
9433: , SYSDATE
9434: , :l_user_id
9450: and ol.PAYMENT_TERM_ID = rt.TERM_ID
9451: and rt.CREDIT_CHECK_FLAG = ''Y''
9452: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9453: and not exists ( select ''x''
9454: from oe_order_holds_ALL oh
9455: where oh.header_id = h.header_id
9456: and oh.line_id = ol.line_id
9457: and oh.hold_source_id =:hold_source_id )';
9458: IF p_item_type is not null and p_activity_name is not null then
9484: END IF;
9485: END IF;
9486:
9487: ELSE
9488: l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL
9489: ( ORDER_HOLD_ID
9490: , LAST_UPDATE_DATE
9491: , LAST_UPDATED_BY
9492: , CREATION_DATE
9498: , RELEASED_FLAG
9499: , ORG_ID
9500: )
9501: SELECT
9502: OE_ORDER_HOLDS_S.NEXTVAL
9503: , SYSDATE
9504: , :l_user_id
9505: , SYSDATE
9506: , :l_user_id
9521: and ol.PAYMENT_TERM_ID = rt.TERM_ID
9522: and rt.CREDIT_CHECK_FLAG = ''Y''
9523: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9524: and not exists ( select ''x''
9525: from oe_order_holds_ALL oh
9526: where oh.header_id = h.header_id
9527: and oh.line_id = ol.line_id
9528: and oh.hold_source_id =:hold_source_id )';
9529: IF p_item_type is not null and p_activity_name is not null then
9580: and ol.PAYMENT_TERM_ID = rt.TERM_ID
9581: and rt.CREDIT_CHECK_FLAG = 'Y'
9582: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
9583: and not exists ( select 'x'
9584: from oe_order_holds_ALL oh
9585: where oh.header_id = h.header_id
9586: and oh.line_id = ol.line_id
9587: and oh.hold_source_id =
9588: p_hold_source_rec.hold_source_id );
9629: --ER#7479609 end
9630:
9631: /*ER#7479609 start
9632: IF p_hold_source_rec.line_id IS NOT NULL THEN
9633: l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL
9634: ( ORDER_HOLD_ID
9635: , LAST_UPDATE_DATE
9636: , LAST_UPDATED_BY
9637: , CREATION_DATE
9643: , RELEASED_FLAG
9644: , ORG_ID
9645: )
9646: SELECT
9647: OE_ORDER_HOLDS_S.NEXTVAL
9648: , SYSDATE
9649: , :l_user_id
9650: , SYSDATE
9651: , :l_user_id
9665: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9666: and ol.OPEN_FLAG = ''Y''
9667: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9668: and not exists ( select ''x''
9669: from oe_order_holds_ALL oh
9670: where oh.header_id = h.header_id
9671: and oh.line_id = ol.line_id
9672: and oh.hold_source_id = :hold_source_id )';
9673: IF p_item_type is not null and p_activity_name is not null then
9703: x_is_hold_applied := TRUE;
9704: END IF;
9705: END IF;
9706: ELSE
9707: l_sqlmt := 'Insert into oe_order_holds_all
9708: ( ORDER_HOLD_ID
9709: , LAST_UPDATE_DATE
9710: , LAST_UPDATED_BY
9711: , CREATION_DATE
9717: , RELEASED_FLAG
9718: , ORG_ID
9719: )
9720: SELECT
9721: OE_ORDER_HOLDS_S.NEXTVAL
9722: , SYSDATE
9723: , :l_user_id
9724: , SYSDATE
9725: , :l_user_id
9738: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9739: and ol.OPEN_FLAG = ''Y''
9740: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9741: and not exists ( select ''x''
9742: from oe_order_holds_ALL oh
9743: where oh.header_id = h.header_id
9744: and oh.line_id = ol.line_id
9745: and oh.hold_source_id = :hold_source_id )';
9746: IF p_item_type is not null and p_activity_name is not null then
9795: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
9796: and ol.OPEN_FLAG = 'Y'
9797: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
9798: and not exists ( select 'x'
9799: from oe_order_holds_ALL oh
9800: where oh.header_id = h.header_id
9801: and oh.line_id = ol.line_id
9802: and oh.hold_source_id =
9803: p_hold_source_rec.hold_source_id );
9844: --ER#7479609 end
9845:
9846: /*ER#7479609 start
9847: IF p_hold_source_rec.line_id IS NOT NULL THEN
9848: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9849: ( ORDER_HOLD_ID
9850: , LAST_UPDATE_DATE
9851: , LAST_UPDATED_BY
9852: , CREATION_DATE
9858: , RELEASED_FLAG
9859: , ORG_ID
9860: )
9861: SELECT
9862: OE_ORDER_HOLDS_S.NEXTVAL
9863: , SYSDATE
9864: , :l_user_id
9865: , SYSDATE
9866: , :l_user_id
9880: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9881: and ol.OPEN_FLAG = ''Y''
9882: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9883: and not exists ( select ''x''
9884: from oe_order_holds_ALL oh
9885: where oh.header_id = h.header_id
9886: and oh.line_id = ol.line_id
9887: and oh.hold_source_id =:hold_source_id )';
9888: IF p_item_type is not null and p_activity_name is not null then
9917: x_is_hold_applied := TRUE;
9918: END IF;
9919: END IF;
9920: ELSE
9921: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
9922: ( ORDER_HOLD_ID
9923: , LAST_UPDATE_DATE
9924: , LAST_UPDATED_BY
9925: , CREATION_DATE
9931: , RELEASED_FLAG
9932: , ORG_ID
9933: )
9934: SELECT
9935: OE_ORDER_HOLDS_S.NEXTVAL
9936: , SYSDATE
9937: , :l_user_id
9938: , SYSDATE
9939: , :l_user_id
9952: and ol.INVENTORY_ITEM_ID = :hold_entity_id
9953: and ol.OPEN_FLAG = ''Y''
9954: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
9955: and not exists ( select ''x''
9956: from oe_order_holds_ALL oh
9957: where oh.header_id = h.header_id
9958: and oh.line_id = ol.line_id
9959: and oh.hold_source_id =:hold_source_id )';
9960: IF p_item_type is not null and p_activity_name is not null then
10007: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
10008: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
10009: and ol.OPEN_FLAG = 'Y' and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
10010: and not exists ( select 'x'
10011: from oe_order_holds_ALL oh
10012: where oh.header_id = h.header_id
10013: and oh.line_id = ol.line_id
10014: and oh.hold_source_id =
10015: p_hold_source_rec.hold_source_id );
10057: --ER#7479609 end
10058:
10059: /*ER#7479609 start
10060: IF p_hold_source_rec.line_id IS NOT NULL THEN
10061: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10062: ( ORDER_HOLD_ID
10063: , LAST_UPDATE_DATE
10064: , LAST_UPDATED_BY
10065: , CREATION_DATE
10071: , RELEASED_FLAG
10072: , ORG_ID
10073: )
10074: SELECT
10075: OE_ORDER_HOLDS_S.NEXTVAL
10076: , SYSDATE
10077: , :l_user_id
10078: , SYSDATE
10079: , :l_user_id
10093: and ol.INVENTORY_ITEM_ID = :hold_entity_id
10094: and ol.OPEN_FLAG = ''Y''
10095: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10096: and not exists ( select ''x''
10097: from oe_order_holds_ALL oh
10098: where oh.header_id = h.header_id
10099: and oh.line_id = ol.line_id
10100: and oh.hold_source_id =:hold_source_id )';
10101: IF p_item_type is not null and p_activity_name is not null then
10131: x_is_hold_applied := TRUE;
10132: END IF;
10133: END IF;
10134: ELSE
10135: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10136: ( ORDER_HOLD_ID
10137: , LAST_UPDATE_DATE
10138: , LAST_UPDATED_BY
10139: , CREATION_DATE
10145: , RELEASED_FLAG
10146: , ORG_ID
10147: )
10148: SELECT
10149: OE_ORDER_HOLDS_S.NEXTVAL
10150: , SYSDATE
10151: , :l_user_id
10152: , SYSDATE
10153: , :l_user_id
10166: and ol.INVENTORY_ITEM_ID = :hold_entity_id
10167: and ol.OPEN_FLAG = ''Y''
10168: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10169: and not exists ( select ''x''
10170: from oe_order_holds_ALL oh
10171: where oh.header_id = h.header_id
10172: and oh.line_id = ol.line_id
10173: and oh.hold_source_id =:hold_source_id )';
10174: IF p_item_type is not null and p_activity_name is not null then
10222: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
10223: and ol.OPEN_FLAG = 'Y'
10224: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
10225: and not exists ( select 'x'
10226: from oe_order_holds_ALL oh
10227: where oh.header_id = h.header_id
10228: and oh.line_id = ol.line_id
10229: and oh.hold_source_id =
10230: p_hold_source_rec.hold_source_id );
10293: -- 13990273 END
10294:
10295: /*ER#7479609 start
10296: IF p_hold_source_rec.line_id IS NOT NULL THEN
10297: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10298: ( ORDER_HOLD_ID
10299: , LAST_UPDATE_DATE
10300: , LAST_UPDATED_BY
10301: , CREATION_DATE
10307: , RELEASED_FLAG
10308: , ORG_ID
10309: )
10310: SELECT
10311: OE_ORDER_HOLDS_S.NEXTVAL
10312: , SYSDATE
10313: , :l_user_id
10314: , SYSDATE
10315: , :l_user_id
10329: and ol.INVENTORY_ITEM_ID = :hold_entity_id
10330: and ol.OPEN_FLAG = ''Y''
10331: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10332: and not exists ( select ''x''
10333: from oe_order_holds_ALL oh
10334: where oh.header_id = h.header_id
10335: and oh.line_id = ol.line_id
10336: and oh.hold_source_id =:hold_source_id )';
10337: IF p_item_type is not null and p_activity_name is not null then
10366: x_is_hold_applied := TRUE;
10367: END IF;
10368: END IF;
10369: ELSE
10370: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10371: ( ORDER_HOLD_ID
10372: , LAST_UPDATE_DATE
10373: , LAST_UPDATED_BY
10374: , CREATION_DATE
10380: , RELEASED_FLAG
10381: , ORG_ID
10382: )
10383: SELECT
10384: OE_ORDER_HOLDS_S.NEXTVAL
10385: , SYSDATE
10386: , :l_user_id
10387: , SYSDATE
10388: , :l_user_id
10401: and ol.INVENTORY_ITEM_ID = :hold_entity_id
10402: and ol.OPEN_FLAG = ''Y''
10403: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10404: and not exists ( select ''x''
10405: from oe_order_holds_ALL oh
10406: where oh.header_id = h.header_id
10407: and oh.line_id = ol.line_id
10408: and oh.hold_source_id =:hold_source_id )';
10409: IF p_item_type is not null and p_activity_name is not null then
10457: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
10458: and ol.OPEN_FLAG = 'Y'
10459: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
10460: and not exists ( select 'x'
10461: from oe_order_holds_ALL oh
10462: where oh.header_id = h.header_id
10463: and oh.line_id = ol.line_id
10464: and oh.hold_source_id =
10465: p_hold_source_rec.hold_source_id );
10805: --ER#7479609 end
10806:
10807: /*ER#7479609 start
10808: IF p_hold_source_rec.line_id IS NOT NULL THEN
10809: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10810: ( ORDER_HOLD_ID
10811: , LAST_UPDATE_DATE
10812: , LAST_UPDATED_BY
10813: , CREATION_DATE
10819: , RELEASED_FLAG
10820: , ORG_ID
10821: )
10822: SELECT
10823: OE_ORDER_HOLDS_S.NEXTVAL
10824: , SYSDATE
10825: , :l_user_id
10826: , SYSDATE
10827: , :l_user_id
10841: and h.SOLD_TO_ORG_ID = :hold_entity_id2
10842: and ol.OPEN_FLAG = ''Y''
10843: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10844: and not exists ( select ''x''
10845: from oe_order_holds_ALL oh
10846: where oh.header_id = h.header_id
10847: and oh.line_id = ol.line_id
10848: and oh.hold_source_id =:hold_source_id )';
10849: IF p_item_type is not null and p_activity_name is not null then
10878: x_is_hold_applied := TRUE;
10879: END IF;
10880: END IF;
10881: ELSE
10882: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
10883: ( ORDER_HOLD_ID
10884: , LAST_UPDATE_DATE
10885: , LAST_UPDATED_BY
10886: , CREATION_DATE
10892: , RELEASED_FLAG
10893: , ORG_ID
10894: )
10895: SELECT
10896: OE_ORDER_HOLDS_S.NEXTVAL
10897: , SYSDATE
10898: , :l_user_id
10899: , SYSDATE
10900: , :l_user_id
10914: and ol.OPEN_FLAG = ''Y''
10915: -- QUOTING change
10916: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
10917: and not exists ( select ''x''
10918: from oe_order_holds_ALL oh
10919: where oh.header_id = h.header_id
10920: and oh.line_id = ol.line_id
10921: and oh.hold_source_id =:hold_source_id )';
10922: IF p_item_type is not null and p_activity_name is not null then
10970: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
10971: and ol.OPEN_FLAG = 'Y'
10972: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
10973: and not exists ( select 'x'
10974: from oe_order_holds_ALL oh
10975: where oh.header_id = h.header_id
10976: and oh.line_id = ol.line_id
10977: and oh.hold_source_id =
10978: p_hold_source_rec.hold_source_id );
11109: --ER#7479609 end
11110:
11111: /*ER#7479609 start
11112: IF p_hold_source_rec.line_id IS NOT NULL THEN
11113: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11114: ( ORDER_HOLD_ID
11115: , LAST_UPDATE_DATE
11116: , LAST_UPDATED_BY
11117: , CREATION_DATE
11123: , RELEASED_FLAG
11124: , ORG_ID
11125: )
11126: SELECT
11127: OE_ORDER_HOLDS_S.NEXTVAL
11128: , SYSDATE
11129: , :l_user_id
11130: , SYSDATE
11131: , :l_user_id
11146: and ol.OPEN_FLAG = ''Y''
11147: -- QUOTING change
11148: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11149: AND NOT EXISTS ( select ''x''
11150: FROM oe_order_holds_ALL oh
11151: WHERE oh.header_id = h.header_id
11152: AND oh.line_id = ol.line_id
11153: AND oh.hold_source_id =:hold_source_id )';
11154: IF p_item_type is not null and p_activity_name is not null then
11183: x_is_hold_applied := TRUE;
11184: END IF;
11185: END IF;
11186: ELSE
11187: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11188: ( ORDER_HOLD_ID
11189: , LAST_UPDATE_DATE
11190: , LAST_UPDATED_BY
11191: , CREATION_DATE
11197: , RELEASED_FLAG
11198: , ORG_ID
11199: )
11200: SELECT
11201: OE_ORDER_HOLDS_S.NEXTVAL
11202: , SYSDATE
11203: , :l_user_id
11204: , SYSDATE
11205: , :l_user_id
11219: and ol.OPEN_FLAG = ''Y''
11220: -- QUOTING change
11221: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11222: AND NOT EXISTS ( select ''x''
11223: FROM oe_order_holds_ALL oh
11224: WHERE oh.header_id = h.header_id
11225: AND oh.line_id = ol.line_id
11226: AND oh.hold_source_id =:hold_source_id )';
11227: IF p_item_type is not null and p_activity_name is not null then
11275: AND ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
11276: and ol.OPEN_FLAG = 'Y'
11277: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
11278: and not exists ( select 'x'
11279: from oe_order_holds_ALL oh
11280: where oh.header_id = h.header_id
11281: and oh.line_id = ol.line_id
11282: and oh.hold_source_id =
11283: p_hold_source_rec.hold_source_id );
11325: --ER#7479609 end
11326:
11327: /*ER#7479609 start
11328: IF p_hold_source_rec.line_id IS NOT NULL THEN
11329: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11330: ( ORDER_HOLD_ID
11331: , LAST_UPDATE_DATE
11332: , LAST_UPDATED_BY
11333: , CREATION_DATE
11339: , RELEASED_FLAG
11340: , ORG_ID
11341: )
11342: SELECT
11343: OE_ORDER_HOLDS_S.NEXTVAL
11344: , SYSDATE
11345: , :l_user_id
11346: , SYSDATE
11347: , :l_user_id
11361: and ol.OPEN_FLAG = ''Y''
11362: -- QUOTING change
11363: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11364: AND NOT EXISTS ( select ''x''
11365: FROM oe_order_holds_ALL oh
11366: WHERE oh.header_id = h.header_id
11367: AND oh.line_id = ol.line_id
11368: AND oh.hold_source_id =:hold_source_id )';
11369: IF p_item_type is not null and p_activity_name is not null then
11398: x_is_hold_applied := TRUE;
11399: END IF;
11400: END IF;
11401: ELSE
11402: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11403: ( ORDER_HOLD_ID
11404: , LAST_UPDATE_DATE
11405: , LAST_UPDATED_BY
11406: , CREATION_DATE
11412: , RELEASED_FLAG
11413: , ORG_ID
11414: )
11415: SELECT
11416: OE_ORDER_HOLDS_S.NEXTVAL
11417: , SYSDATE
11418: , :l_user_id
11419: , SYSDATE
11420: , :l_user_id
11433: and ol.OPEN_FLAG = ''Y''
11434: -- QUOTING change
11435: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11436: AND NOT EXISTS ( select ''x''
11437: FROM oe_order_holds_ALL oh
11438: WHERE oh.header_id = h.header_id
11439: AND oh.line_id = ol.line_id
11440: AND oh.hold_source_id =:hold_source_id )';
11441: IF p_item_type is not null and p_activity_name is not null then
11489: AND ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id2
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 );
11542: --ER#7479609 end
11543:
11544: /*ER#7479609 start
11545: IF p_hold_source_rec.line_id IS NOT NULL THEN
11546: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11547: ( ORDER_HOLD_ID
11548: , LAST_UPDATE_DATE
11549: , LAST_UPDATED_BY
11550: , CREATION_DATE
11556: , RELEASED_FLAG
11557: , ORG_ID
11558: )
11559: SELECT
11560: OE_ORDER_HOLDS_S.NEXTVAL
11561: , SYSDATE
11562: , :l_user_id
11563: , SYSDATE
11564: , :l_user_id
11578: and ol.OPEN_FLAG = ''Y''
11579: -- QUOTING change
11580: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11581: and not exists ( select ''x''
11582: from oe_order_holds_ALL oh
11583: where oh.header_id = h.header_id
11584: and oh.line_id = ol.line_id
11585: and oh.hold_source_id =:hold_source_id )';
11586: IF p_item_type is not null and p_activity_name is not null then
11615: x_is_hold_applied := TRUE;
11616: END IF;
11617: END IF;
11618: ELSE
11619: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11620: ( ORDER_HOLD_ID
11621: , LAST_UPDATE_DATE
11622: , LAST_UPDATED_BY
11623: , CREATION_DATE
11629: , RELEASED_FLAG
11630: , ORG_ID
11631: )
11632: SELECT
11633: OE_ORDER_HOLDS_S.NEXTVAL
11634: , SYSDATE
11635: , :l_user_id
11636: , SYSDATE
11637: , :l_user_id
11650: and ol.OPEN_FLAG = ''Y''
11651: -- QUOTING change
11652: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11653: and not exists ( select ''x''
11654: from oe_order_holds_ALL oh
11655: where oh.header_id = h.header_id
11656: and oh.line_id = ol.line_id
11657: and oh.hold_source_id =:hold_source_id )';
11658: IF p_item_type is not null and p_activity_name is not null then
11706: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
11707: and ol.OPEN_FLAG = 'Y'
11708: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
11709: and not exists ( select 'x'
11710: from oe_order_holds_ALL oh
11711: where oh.header_id = h.header_id
11712: and oh.line_id = ol.line_id
11713: and oh.hold_source_id =
11714: p_hold_source_rec.hold_source_id );
11866: --ER#7479609 end
11867:
11868: /*ER#7479609 start
11869: IF p_hold_source_rec.line_id IS NOT NULL THEN
11870: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11871: ( ORDER_HOLD_ID
11872: , LAST_UPDATE_DATE
11873: , LAST_UPDATED_BY
11874: , CREATION_DATE
11880: , RELEASED_FLAG
11881: , ORG_ID
11882: )
11883: SELECT
11884: OE_ORDER_HOLDS_S.NEXTVAL
11885: , SYSDATE
11886: , :l_user_id
11887: , SYSDATE
11888: , :l_user_id
11902: and ol.OPEN_FLAG = ''Y''
11903: -- QUOTING change
11904: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11905: and not exists ( select ''x''
11906: from oe_order_holds_ALL oh
11907: where oh.header_id = h.header_id
11908: and oh.line_id = ol.line_id
11909: and oh.hold_source_id =:hold_source_id )';
11910: IF p_item_type is not null and p_activity_name is not null then
11939: x_is_hold_applied := TRUE;
11940: END IF;
11941: END IF;
11942: ELSE
11943: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
11944: ( ORDER_HOLD_ID
11945: , LAST_UPDATE_DATE
11946: , LAST_UPDATED_BY
11947: , CREATION_DATE
11953: , RELEASED_FLAG
11954: , ORG_ID
11955: )
11956: SELECT
11957: OE_ORDER_HOLDS_S.NEXTVAL
11958: , SYSDATE
11959: , :l_user_id
11960: , SYSDATE
11961: , :l_user_id
11974: and ol.OPEN_FLAG = ''Y''
11975: -- QUOTING change
11976: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
11977: and not exists ( select ''x''
11978: from oe_order_holds_ALL oh
11979: where oh.header_id = h.header_id
11980: and oh.line_id = ol.line_id
11981: and oh.hold_source_id =:hold_source_id )';
11982: IF p_item_type is not null and p_activity_name is not null then
12031: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
12032: and ol.OPEN_FLAG = 'Y'
12033: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12034: and not exists ( select 'x'
12035: from oe_order_holds_ALL oh
12036: where oh.header_id = h.header_id
12037: and oh.line_id = ol.line_id
12038: and oh.hold_source_id =
12039: p_hold_source_rec.hold_source_id );
12081: --ER#7479609 end
12082:
12083: /*ER#7479609 start
12084: IF p_hold_source_rec.line_id IS NOT NULL THEN
12085: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12086: ( ORDER_HOLD_ID
12087: , LAST_UPDATE_DATE
12088: , LAST_UPDATED_BY
12089: , CREATION_DATE
12095: , RELEASED_FLAG
12096: , ORG_ID
12097: )
12098: SELECT
12099: OE_ORDER_HOLDS_S.NEXTVAL
12100: , SYSDATE
12101: , :l_user_id
12102: , SYSDATE
12103: , :l_user_id
12117: and ol.OPEN_FLAG = ''Y''
12118: -- QUOTING change
12119: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12120: and not exists ( select ''x''
12121: from oe_order_holds_ALL oh
12122: where oh.header_id = h.header_id
12123: and oh.line_id = ol.line_id
12124: and oh.hold_source_id =:hold_source_id )';
12125: IF p_item_type is not null and p_activity_name is not null then
12154: x_is_hold_applied := TRUE;
12155: END IF;
12156: END IF;
12157: ELSE
12158: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12159: ( ORDER_HOLD_ID
12160: , LAST_UPDATE_DATE
12161: , LAST_UPDATED_BY
12162: , CREATION_DATE
12168: , RELEASED_FLAG
12169: , ORG_ID
12170: )
12171: SELECT
12172: OE_ORDER_HOLDS_S.NEXTVAL
12173: , SYSDATE
12174: , :l_user_id
12175: , SYSDATE
12176: , :l_user_id
12189: and ol.OPEN_FLAG = ''Y''
12190: -- QUOTING change
12191: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12192: and not exists ( select ''x''
12193: from oe_order_holds_ALL oh
12194: where oh.header_id = h.header_id
12195: and oh.line_id = ol.line_id
12196: and oh.hold_source_id =:hold_source_id )';
12197: IF p_item_type is not null and p_activity_name is not null then
12245: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
12246: and ol.OPEN_FLAG = 'Y'
12247: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12248: and not exists ( select 'x'
12249: from oe_order_holds_ALL oh
12250: where oh.header_id = h.header_id
12251: and oh.line_id = ol.line_id
12252: and oh.hold_source_id =
12253: p_hold_source_rec.hold_source_id );
12295: --ER#7479609 end
12296:
12297: /*ER#7479609 start
12298: IF p_hold_source_rec.line_id IS NOT NULL THEN
12299: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12300: ( ORDER_HOLD_ID
12301: , LAST_UPDATE_DATE
12302: , LAST_UPDATED_BY
12303: , CREATION_DATE
12309: , RELEASED_FLAG
12310: , ORG_ID
12311: )
12312: SELECT
12313: OE_ORDER_HOLDS_S.NEXTVAL
12314: , SYSDATE
12315: , :l_user_id
12316: , SYSDATE
12317: , :l_user_id
12331: and ol.OPEN_FLAG = ''Y''
12332: -- QUOTING change
12333: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12334: and not exists ( select ''x''
12335: from oe_order_holds_ALL oh
12336: where oh.header_id = h.header_id
12337: and oh.line_id = ol.line_id
12338: and oh.hold_source_id =:hold_source_id )';
12339: IF p_item_type is not null and p_activity_name is not null then
12368: x_is_hold_applied := TRUE;
12369: END IF;
12370: END IF;
12371: ELSE
12372: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12373: ( ORDER_HOLD_ID
12374: , LAST_UPDATE_DATE
12375: , LAST_UPDATED_BY
12376: , CREATION_DATE
12382: , RELEASED_FLAG
12383: , ORG_ID
12384: )
12385: SELECT
12386: OE_ORDER_HOLDS_S.NEXTVAL
12387: , SYSDATE
12388: , :l_user_id
12389: , SYSDATE
12390: , :l_user_id
12403: and ol.OPEN_FLAG = ''Y''
12404: -- QUOTING change
12405: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12406: and not exists ( select ''x''
12407: from oe_order_holds_ALL oh
12408: where oh.header_id = h.header_id
12409: and oh.line_id = ol.line_id
12410: and oh.hold_source_id =:hold_source_id )';
12411: IF p_item_type is not null and p_activity_name is not null then
12459: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
12460: and ol.OPEN_FLAG = 'Y'
12461: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12462: and not exists ( select 'x'
12463: from oe_order_holds_ALL oh
12464: where oh.header_id = h.header_id
12465: and oh.line_id = ol.line_id
12466: and oh.hold_source_id =
12467: p_hold_source_rec.hold_source_id );
12508: --ER#7479609 end
12509:
12510: /*ER#7479609 start
12511: IF p_hold_source_rec.line_id IS NOT NULL THEN
12512: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12513: ( ORDER_HOLD_ID
12514: , LAST_UPDATE_DATE
12515: , LAST_UPDATED_BY
12516: , CREATION_DATE
12522: , RELEASED_FLAG
12523: , ORG_ID
12524: )
12525: SELECT
12526: OE_ORDER_HOLDS_S.NEXTVAL
12527: , SYSDATE
12528: , :l_user_id
12529: , SYSDATE
12530: , :l_user_id
12543: and ol.OPEN_FLAG = ''Y''
12544: -- QUOTING change
12545: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12546: and not exists ( select ''x''
12547: from oe_order_holds_ALL oh
12548: where oh.header_id = h.header_id
12549: and oh.line_id = ol.line_id
12550: and oh.hold_source_id =:hold_source_id )';
12551: IF p_item_type is not null and p_activity_name is not null then
12580: x_is_hold_applied := TRUE;
12581: END IF;
12582: END IF;
12583: ELSE
12584: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12585: ( ORDER_HOLD_ID
12586: , LAST_UPDATE_DATE
12587: , LAST_UPDATED_BY
12588: , CREATION_DATE
12594: , RELEASED_FLAG
12595: , ORG_ID
12596: )
12597: SELECT
12598: OE_ORDER_HOLDS_S.NEXTVAL
12599: , SYSDATE
12600: , :l_user_id
12601: , SYSDATE
12602: , :l_user_id
12614: and ol.OPEN_FLAG = ''Y''
12615: -- QUOTING change
12616: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12617: and not exists ( select ''x''
12618: from oe_order_holds_ALL oh
12619: where oh.header_id = h.header_id
12620: and oh.line_id = ol.line_id
12621: and oh.hold_source_id =:hold_source_id )';
12622: IF p_item_type is not null and p_activity_name is not null then
12669: and ol.BLANKET_NUMBER = p_hold_source_rec.hold_entity_id
12670: and ol.OPEN_FLAG = 'Y'
12671: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12672: and not exists ( select 'x'
12673: from oe_order_holds_ALL oh
12674: where oh.header_id = h.header_id
12675: and oh.line_id = ol.line_id
12676: and oh.hold_source_id =
12677: p_hold_source_rec.hold_source_id );
12701: ******************************************************************************************************/
12702:
12703: ELSIF p_hold_source_rec.hold_entity_code = 'O' THEN
12704: IF p_hold_source_rec.line_id is NULL THEN
12705: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12706: ( ORDER_HOLD_ID
12707: , LAST_UPDATE_DATE
12708: , LAST_UPDATED_BY
12709: , CREATION_DATE
12715: , RELEASED_FLAG
12716: , ORG_ID
12717: )
12718: SELECT
12719: OE_ORDER_HOLDS_S.NEXTVAL
12720: , SYSDATE
12721: , :l_user_id
12722: , SYSDATE
12723: , :l_user_id
12733: and h.header_id = :hold_entity_id
12734: -- QUOTING change
12735: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12736: and not exists ( select ''x''
12737: from oe_order_holds_ALL oh
12738: where oh.header_id = h.header_id
12739: and oh.hold_source_id =:hold_source_id2 )';
12740: IF p_item_type is not null and p_activity_name is not null then
12741: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
12760: x_is_hold_applied := TRUE;
12761: END IF;
12762: END IF;
12763: ELSE
12764: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12765: ( ORDER_HOLD_ID
12766: , LAST_UPDATE_DATE
12767: , LAST_UPDATED_BY
12768: , CREATION_DATE
12774: , RELEASED_FLAG
12775: , ORG_ID
12776: )
12777: SELECT
12778: OE_ORDER_HOLDS_S.NEXTVAL
12779: , SYSDATE
12780: , :l_user_id
12781: , SYSDATE
12782: , :l_user_id
12795: and ol.OPEN_FLAG = ''Y''
12796: -- QUOTING change
12797: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
12798: and not exists ( select ''x''
12799: from oe_order_holds_ALL oh
12800: where oh.header_id = h.header_id
12801: and oh.line_id = ol.line_id
12802: and oh.hold_source_id =:hold_source_id )';
12803: IF p_item_type is not null and p_activity_name is not null then
12857: and ol.line_id = p_hold_source_rec.line_id
12858: and ol.open_flag = 'Y'
12859: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
12860: and not exists ( select 'x'
12861: from oe_order_holds_ALL oh
12862: where oh.header_id = h.header_id
12863: and oh.line_id = ol.line_id
12864: and oh.hold_source_id =
12865: p_hold_source_rec.hold_source_id );
12976:
12977:
12978: /*ER#7479609 start
12979: IF p_hold_source_rec.header_id IS NOT NULL THEN
12980: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
12981: ( ORDER_HOLD_ID
12982: , LAST_UPDATE_DATE
12983: , LAST_UPDATED_BY
12984: , CREATION_DATE
12990: , RELEASED_FLAG
12991: , ORG_ID
12992: )
12993: SELECT
12994: OE_ORDER_HOLDS_S.NEXTVAL
12995: , SYSDATE
12996: , :l_user_id
12997: , SYSDATE
12998: , :l_user_id
13009: and h.SOLD_TO_ORG_ID = :hold_entity_id
13010: -- QUOTING change
13011: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
13012: and not exists ( select ''x''
13013: from oe_order_holds_ALL oh
13014: where oh.header_id = h.header_id
13015: and oh.hold_source_id =:hold_source_id )';
13016: IF p_item_type is not null and p_activity_name is not null then
13017: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
13034: x_is_hold_applied := TRUE;
13035: END IF;
13036: END IF;
13037: ELSE
13038: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
13039: ( ORDER_HOLD_ID
13040: , LAST_UPDATE_DATE
13041: , LAST_UPDATED_BY
13042: , CREATION_DATE
13048: , RELEASED_FLAG
13049: , ORG_ID
13050: )
13051: SELECT
13052: OE_ORDER_HOLDS_S.NEXTVAL
13053: , SYSDATE
13054: , :l_user_id
13055: , SYSDATE
13056: , :l_user_id
13067: and h.org_id = :l_org_id --ER#7479609
13068: -- QUOTING change
13069: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
13070: and not exists ( select ''x''
13071: from oe_order_holds_ALL oh
13072: where oh.header_id = h.header_id
13073: and oh.hold_source_id =:hold_source_id )';
13074: IF p_item_type is not null and p_activity_name is not null then
13075: l_sqlmt := l_sqlmt||' and not exists (select 1 from wf_item_activity_statuses was
13111:
13112: and h.SOLD_TO_ORG_ID = p_hold_source_rec.hold_entity_id
13113: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
13114: and not exists ( select 'x'
13115: from oe_order_holds_ALL oh
13116: where oh.header_id = h.header_id
13117: and oh.hold_source_id =
13118: p_hold_source_rec.hold_source_id );
13119:
13425: --ER#7479609 end
13426:
13427: /*ER#7479609 start
13428: IF p_hold_source_rec.line_id IS NOT NULL THEN
13429: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
13430: ( ORDER_HOLD_ID
13431: , LAST_UPDATE_DATE
13432: , LAST_UPDATED_BY
13433: , CREATION_DATE
13439: , RELEASED_FLAG
13440: , ORG_ID
13441: )
13442: SELECT
13443: OE_ORDER_HOLDS_S.NEXTVAL
13444: , SYSDATE
13445: , :l_user_id
13446: , SYSDATE
13447: , :l_user_id
13460: and ol.OPEN_FLAG = ''Y''
13461: -- QUOTING change
13462: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
13463: and not exists ( select ''x''
13464: from oe_order_holds_ALL oh
13465: where oh.header_id = h.header_id
13466: and oh.line_id = ol.line_id
13467: and oh.hold_source_id =:hold_source_id )';
13468: IF p_item_type is not null and p_activity_name is not null then
13497: END IF;
13498: END IF;
13499:
13500: ELSE
13501: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
13502: ( ORDER_HOLD_ID
13503: , LAST_UPDATE_DATE
13504: , LAST_UPDATED_BY
13505: , CREATION_DATE
13511: , RELEASED_FLAG
13512: , ORG_ID
13513: )
13514: SELECT
13515: OE_ORDER_HOLDS_S.NEXTVAL
13516: , SYSDATE
13517: , :l_user_id
13518: , SYSDATE
13519: , :l_user_id
13531: and ol.OPEN_FLAG = ''Y''
13532: -- QUOTING change
13533: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
13534: and not exists ( select ''x''
13535: from oe_order_holds_ALL oh
13536: where oh.header_id = h.header_id
13537: and oh.line_id = ol.line_id
13538: and oh.hold_source_id =:hold_source_id )';
13539: IF p_item_type is not null and p_activity_name is not null then
13585: and ol.INVOICE_TO_ORG_ID = p_hold_source_rec.hold_entity_id
13586: and ol.OPEN_FLAG = 'Y'
13587: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
13588: and not exists ( select 'x'
13589: from oe_order_holds_ALL oh
13590: where oh.header_id = h.header_id
13591: and oh.line_id = ol.line_id
13592: and oh.hold_source_id =
13593: p_hold_source_rec.hold_source_id );
13638: --ER#7479609 end
13639:
13640: /*ER#7479609 start
13641: IF p_hold_source_rec.line_id IS NOT NULL THEN
13642: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
13643: ( ORDER_HOLD_ID
13644: , LAST_UPDATE_DATE
13645: , LAST_UPDATED_BY
13646: , CREATION_DATE
13652: , RELEASED_FLAG
13653: , ORG_ID
13654: )
13655: SELECT
13656: OE_ORDER_HOLDS_S.NEXTVAL
13657: , SYSDATE
13658: , :l_user_id
13659: , SYSDATE
13660: , :l_user_id
13673: and ol.OPEN_FLAG = ''Y''
13674: -- QUOTING change
13675: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
13676: and not exists ( select ''x''
13677: from oe_order_holds_ALL oh
13678: where oh.header_id = h.header_id
13679: and oh.line_id = ol.line_id
13680: and oh.hold_source_id =:hold_source_id )';
13681: IF p_item_type is not null and p_activity_name is not null then
13709: x_is_hold_applied := TRUE;
13710: END IF;
13711: END IF;
13712: ELSE
13713: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
13714: ( ORDER_HOLD_ID
13715: , LAST_UPDATE_DATE
13716: , LAST_UPDATED_BY
13717: , CREATION_DATE
13723: , RELEASED_FLAG
13724: , ORG_ID
13725: )
13726: SELECT
13727: OE_ORDER_HOLDS_S.NEXTVAL
13728: , SYSDATE
13729: , :l_user_id
13730: , SYSDATE
13731: , :l_user_id
13743: and ol.OPEN_FLAG = ''Y''
13744: -- QUOTING change
13745: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
13746: and not exists ( select ''x''
13747: from oe_order_holds_ALL oh
13748: where oh.header_id = h.header_id
13749: and oh.line_id = ol.line_id
13750: and oh.hold_source_id =:hold_source_id )';
13751: IF p_item_type is not null and p_activity_name is not null then
13797: and ol.SHIP_TO_ORG_ID = p_hold_source_rec.hold_entity_id
13798: and ol.OPEN_FLAG = 'Y'
13799: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
13800: and not exists ( select 'x'
13801: from oe_order_holds_ALL oh
13802: where oh.header_id = h.header_id
13803: and oh.line_id = ol.line_id
13804: and oh.hold_source_id =
13805: p_hold_source_rec.hold_source_id );
13849: --ER#7479609 end
13850:
13851: /*ER#7479609 start
13852: IF p_hold_source_rec.line_id IS NOT NULL THEN
13853: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
13854: ( ORDER_HOLD_ID
13855: , LAST_UPDATE_DATE
13856: , LAST_UPDATED_BY
13857: , CREATION_DATE
13863: , RELEASED_FLAG
13864: , ORG_ID
13865: )
13866: SELECT
13867: OE_ORDER_HOLDS_S.NEXTVAL
13868: , SYSDATE
13869: , :l_user_id
13870: , SYSDATE
13871: , :l_user_id
13884: and ol.OPEN_FLAG = ''Y''
13885: -- QUOTING change
13886: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
13887: and not exists ( select ''x''
13888: from oe_order_holds_ALL oh
13889: where oh.header_id = h.header_id
13890: and oh.line_id = ol.line_id
13891: and oh.hold_source_id =:hold_source_id )';
13892: IF p_item_type is not null and p_activity_name is not null then
13920: x_is_hold_applied := TRUE;
13921: END IF;
13922: END IF;
13923: ELSE
13924: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
13925: ( ORDER_HOLD_ID
13926: , LAST_UPDATE_DATE
13927: , LAST_UPDATED_BY
13928: , CREATION_DATE
13934: , RELEASED_FLAG
13935: , ORG_ID
13936: )
13937: SELECT
13938: OE_ORDER_HOLDS_S.NEXTVAL
13939: , SYSDATE
13940: , :l_user_id
13941: , SYSDATE
13942: , :l_user_id
13954: and ol.OPEN_FLAG = ''Y''
13955: -- QUOTING change
13956: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
13957: and not exists ( select ''x''
13958: from oe_order_holds_ALL oh
13959: where oh.header_id = h.header_id
13960: and oh.line_id = ol.line_id
13961: and oh.hold_source_id =:hold_source_id )';
13962: IF p_item_type is not null and p_activity_name is not null then
14008: and ol.SHIP_FROM_ORG_ID = p_hold_source_rec.hold_entity_id
14009: and ol.OPEN_FLAG = 'Y'
14010: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
14011: and not exists ( select 'x'
14012: from oe_order_holds_ALL oh
14013: where oh.header_id = h.header_id
14014: and oh.line_id = ol.line_id
14015: and oh.hold_source_id =
14016: p_hold_source_rec.hold_source_id );
14060: --ER#7479609 end
14061:
14062: /*ER#7479609 start
14063: IF p_hold_source_rec.line_id IS NOT NULL THEN
14064: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
14065: ( ORDER_HOLD_ID
14066: , LAST_UPDATE_DATE
14067: , LAST_UPDATED_BY
14068: , CREATION_DATE
14074: , RELEASED_FLAG
14075: , ORG_ID
14076: )
14077: SELECT
14078: OE_ORDER_HOLDS_S.NEXTVAL
14079: , SYSDATE
14080: , :l_user_id
14081: , SYSDATE
14082: , :l_user_id
14095: and ol.OPEN_FLAG = ''Y''
14096: -- QUOTING change
14097: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
14098: and not exists ( select ''x''
14099: from oe_order_holds_ALL oh
14100: where oh.header_id = h.header_id
14101: and oh.line_id = ol.line_id
14102: and oh.hold_source_id =:hold_source_id )';
14103: IF p_item_type is not null and p_activity_name is not null then
14132: END IF;
14133: END IF;
14134:
14135: ELSE
14136: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
14137: ( ORDER_HOLD_ID
14138: , LAST_UPDATE_DATE
14139: , LAST_UPDATED_BY
14140: , CREATION_DATE
14146: , RELEASED_FLAG
14147: , ORG_ID
14148: )
14149: SELECT
14150: OE_ORDER_HOLDS_S.NEXTVAL
14151: , SYSDATE
14152: , :l_user_id
14153: , SYSDATE
14154: , :l_user_id
14166: and ol.OPEN_FLAG = ''Y''
14167: -- QUOTING change
14168: and nvl(h.TRANSACTION_PHASE_CODE,''F'') = ''F''
14169: and not exists ( select ''x''
14170: from oe_order_holds_ALL oh
14171: where oh.header_id = h.header_id
14172: and oh.line_id = ol.line_id
14173: and oh.hold_source_id =:hold_source_id )';
14174: IF p_item_type is not null and p_activity_name is not null then
14219: and ol.INVENTORY_ITEM_ID = p_hold_source_rec.hold_entity_id
14220: and ol.OPEN_FLAG = 'Y'
14221: and nvl(h.TRANSACTION_PHASE_CODE,'F') = 'F'
14222: and not exists ( select 'x'
14223: from oe_order_holds_ALL oh
14224: where oh.header_id = h.header_id
14225: and oh.line_id = ol.line_id
14226: and oh.hold_source_id =
14227: p_hold_source_rec.hold_source_id );