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 1024: -- DESCRIPTION : This is a Local Procedure used to insert record into the OE_ORDER_HOLDS_ALL table

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:

Line 1061: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 1092: from oe_order_holds_ALL oh

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

Line 1151: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 1181: from oe_order_holds_ALL oh

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

Line 1251: from oe_order_holds_ALL oh

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

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

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:

Line 1332: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 1365: from oe_order_holds_ALL oh

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

Line 1438: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 1470: from oe_order_holds_ALL oh

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:

Line 1552: FROM oe_order_holds_ALL oh

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:

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

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:

Line 2802: from oe_order_holds_all OH --ER#7479609

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:

Line 2833: from oe_order_holds_all OH --ER#7479609

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:

Line 3044: from oe_order_holds_all OH --ER#7479609

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

Line 3073: from oe_order_holds_all OH --ER#7479609

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

Line 3392: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3428: from oe_order_holds_ALL oh

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

Line 3434: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3469: from oe_order_holds_ALL oh

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

Line 3479: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3517: from oe_order_holds_ALL oh

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

Line 3523: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3560: from oe_order_holds_ALL oh

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

Line 3570: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3606: from oe_order_holds_ALL oh

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

Line 3612: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3647: from oe_order_holds_ALL oh

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

Line 3656: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3692: from oe_order_holds_ALL oh

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

Line 3698: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3733: from oe_order_holds_ALL oh

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

Line 3742: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3778: from oe_order_holds_ALL oh

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

Line 3784: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3819: from oe_order_holds_ALL oh

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

Line 3828: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3864: from oe_order_holds_ALL oh

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

Line 3870: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3905: from oe_order_holds_ALL oh

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

Line 3915: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3951: from oe_order_holds_ALL oh

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

Line 3957: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 3992: from oe_order_holds_ALL oh

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

Line 4001: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4037: FROM oe_order_holds_ALL oh

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

Line 4043: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4078: FROM oe_order_holds_ALL oh

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

Line 4087: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4123: FROM oe_order_holds_ALL oh

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

Line 4129: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4164: FROM oe_order_holds_ALL oh

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

Line 4173: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4209: from oe_order_holds_ALL oh

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

Line 4215: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4250: from oe_order_holds_ALL oh

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

Line 4260: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4296: from oe_order_holds_ALL oh

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

Line 4302: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4337: from oe_order_holds_ALL oh

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

Line 4346: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4382: from oe_order_holds_ALL oh

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

Line 4388: INSERT INTO OE_ORDER_HOLDS_ALL

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

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 4433: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4469: from oe_order_holds_ALL oh

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

Line 4475: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4510: from oe_order_holds_ALL oh

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

Line 4519: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4554: from oe_order_holds_ALL oh

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

Line 4560: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4594: from oe_order_holds_ALL oh

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

Line 4603: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4634: from oe_order_holds_ALL oh

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

Line 4639: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4673: from oe_order_holds_ALL oh

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

Line 4683: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4716: from oe_order_holds_ALL oh

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

Line 4721: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4753: from oe_order_holds_ALL oh

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;

Line 4760: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4795: from oe_order_holds_ALL oh

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

Line 4801: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4835: from oe_order_holds_ALL oh

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

Line 4843: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4878: from oe_order_holds_ALL oh

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

Line 4884: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4918: from oe_order_holds_ALL oh

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

Line 4926: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 4961: from oe_order_holds_ALL oh

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

Line 4967: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 5001: from oe_order_holds_ALL oh

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

Line 5009: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 5044: from oe_order_holds_ALL oh

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

Line 5050: INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 5084: from oe_order_holds_ALL oh

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

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

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

Line 5787: FROM OE_ORDER_HOLDS_all

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

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

Line 5834: DELETE FROM OE_ORDER_HOLDS_all

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

Line 5841: FROM OE_ORDER_HOLDS_all

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

Line 5867: FROM OE_ORDER_HOLDS_all

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

Line 5900: FROM OE_ORDER_HOLDS_all

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

Line 8593: FROM OE_ORDER_HOLDS_all oh,

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;

Line 8651: INSERT INTO OE_ORDER_HOLDS_all

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

Line 8968: FROM oe_order_holds_all ooh,

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

Line 8987: FROM oe_order_holds_all ooh,

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

Line 9090: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9127: from oe_order_holds_ALL oh

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

Line 9167: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9203: from oe_order_holds_ALL oh

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:

Line 9260: from oe_order_holds_ALL oh

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

Line 9416: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9454: from oe_order_holds_ALL oh

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

Line 9488: l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9525: from oe_order_holds_ALL oh

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

Line 9584: from oe_order_holds_ALL oh

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

Line 9633: l_sqlmt :='INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9669: from oe_order_holds_ALL oh

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

Line 9707: l_sqlmt := 'Insert into oe_order_holds_all

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

Line 9742: from oe_order_holds_ALL oh

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

Line 9799: from oe_order_holds_ALL oh

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

Line 9848: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9884: from oe_order_holds_ALL oh

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

Line 9921: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 9956: from oe_order_holds_ALL oh

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

Line 10011: from oe_order_holds_ALL oh

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

Line 10061: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10097: from oe_order_holds_ALL oh

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

Line 10135: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10170: from oe_order_holds_ALL oh

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

Line 10226: from oe_order_holds_ALL oh

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

Line 10297: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10333: from oe_order_holds_ALL oh

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

Line 10370: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10405: from oe_order_holds_ALL oh

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

Line 10461: from oe_order_holds_ALL oh

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

Line 10809: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10845: from oe_order_holds_ALL oh

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

Line 10882: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 10918: from oe_order_holds_ALL oh

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

Line 10974: from oe_order_holds_ALL oh

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

Line 11113: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11150: FROM oe_order_holds_ALL oh

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

Line 11187: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11223: FROM oe_order_holds_ALL oh

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

Line 11279: from oe_order_holds_ALL oh

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

Line 11329: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11365: FROM oe_order_holds_ALL oh

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

Line 11402: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11437: FROM oe_order_holds_ALL oh

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

Line 11493: from oe_order_holds_ALL oh

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

Line 11546: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11582: from oe_order_holds_ALL oh

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

Line 11619: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11654: from oe_order_holds_ALL oh

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

Line 11710: from oe_order_holds_ALL oh

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

Line 11870: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11906: from oe_order_holds_ALL oh

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

Line 11943: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 11978: from oe_order_holds_ALL oh

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

Line 12035: from oe_order_holds_ALL oh

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

Line 12085: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12121: from oe_order_holds_ALL oh

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

Line 12158: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12193: from oe_order_holds_ALL oh

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

Line 12249: from oe_order_holds_ALL oh

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

Line 12299: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12335: from oe_order_holds_ALL oh

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

Line 12372: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12407: from oe_order_holds_ALL oh

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

Line 12463: from oe_order_holds_ALL oh

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

Line 12512: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12547: from oe_order_holds_ALL oh

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

Line 12584: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12618: from oe_order_holds_ALL oh

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

Line 12673: from oe_order_holds_ALL oh

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

Line 12705: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12737: from oe_order_holds_ALL oh

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

Line 12764: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 12799: from oe_order_holds_ALL oh

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

Line 12861: from oe_order_holds_ALL oh

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

Line 12980: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 13013: from oe_order_holds_ALL oh

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

Line 13038: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 13071: from oe_order_holds_ALL oh

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

Line 13115: from oe_order_holds_ALL oh

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:

Line 13429: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 13464: from oe_order_holds_ALL oh

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

Line 13501: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 13535: from oe_order_holds_ALL oh

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

Line 13589: from oe_order_holds_ALL oh

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

Line 13642: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 13677: from oe_order_holds_ALL oh

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

Line 13713: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 13747: from oe_order_holds_ALL oh

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

Line 13801: from oe_order_holds_ALL oh

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

Line 13853: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 13888: from oe_order_holds_ALL oh

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

Line 13924: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 13958: from oe_order_holds_ALL oh

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

Line 14012: from oe_order_holds_ALL oh

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

Line 14064: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 14099: from oe_order_holds_ALL oh

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

Line 14136: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL

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

Line 14170: from oe_order_holds_ALL oh

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

Line 14223: from oe_order_holds_ALL oh

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