[Home] [Help]
424: AND NVL(hd.apply_to_order_and_line_flag,'N') = 'Y';
425:
426: EXCEPTION
427: WHEN NO_DATA_FOUND THEN
428: Oe_debug_pub.ADD('Normal hold release.');
429: WHEN OTHERS THEN
430: Oe_debug_pub.ADD(SQLERRM);
431: -- RAISE;
432: END;
426: EXCEPTION
427: WHEN NO_DATA_FOUND THEN
428: Oe_debug_pub.ADD('Normal hold release.');
429: WHEN OTHERS THEN
430: Oe_debug_pub.ADD(SQLERRM);
431: -- RAISE;
432: END;
433:
434: IF l_release_children = 'Y' THEN
432: END;
433:
434: IF l_release_children = 'Y' THEN
435:
436: Oe_debug_pub.ADD('Handling apply_to_order_and_line_flag..');
437:
438: wf_engine.threshold := -1;
439:
440: FOR c_rec IN c_child_lines(p_order_tbl(1).header_id)
438: wf_engine.threshold := -1;
439:
440: FOR c_rec IN c_child_lines(p_order_tbl(1).header_id)
441: LOOP
442: Oe_debug_pub.ADD('Processing OEOL : ' || c_rec.item_key);
443: wf_engine.CompleteActivity('OEOL', c_rec.item_key, c_rec.full_activity_name, l_result);
444: END LOOP;
445:
446: wf_engine.threshold := l_rel_threshold;
458: IF p_num_of_records > 1 THEN
459: wf_engine.threshold := -1 ;
460: END IF;
461:
462: Oe_debug_pub.ADD('p_num_of_records : ' || p_num_of_records);
463:
464: FOR i in p_order_tbl.FIRST..p_order_tbl.LAST
465: LOOP
466: BEGIN
498: AND wias_to.activity_status = 'NOTIFIED'
499: AND wias_to.item_type = wias_from.item_type
500: AND wias_to.item_key = wias_from.item_key;
501:
502: Oe_debug_pub.ADD('Processing ' || l_item_type || ':' || NVL(p_order_tbl(i).line_id,p_order_tbl(i).header_id));
503:
504: EXCEPTION
505: WHEN OTHERS THEN
506: Oe_debug_pub.ADD('Could not get activity ID for header/line : ' || To_Char(NVL(p_order_tbl(i).line_id,p_order_tbl(i).header_id)));
502: Oe_debug_pub.ADD('Processing ' || l_item_type || ':' || NVL(p_order_tbl(i).line_id,p_order_tbl(i).header_id));
503:
504: EXCEPTION
505: WHEN OTHERS THEN
506: Oe_debug_pub.ADD('Could not get activity ID for header/line : ' || To_Char(NVL(p_order_tbl(i).line_id,p_order_tbl(i).header_id)));
507: l_found := 'F';
508: -- Do not raise an exception here as the query might fail because
509: -- entity being processed is not at this workflow stage (not reached
510: -- yet/ crossed already). This is a valid scenario. say, line hasn't
528: );
529:
530: EXCEPTION
531: WHEN OTHERS THEN
532: Oe_debug_pub.ADD('Failed from progress order.');
533: wf_engine.threshold := l_rel_threshold;
534: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
535: Oe_msg_pub.Count_And_Get
536: ( p_count => x_msg_count
581: AND oh.released_flag = 'Y';
582:
583: BEGIN
584:
585: Oe_debug_pub.ADD('In overloaded progress_order!!');
586:
587: l_rel_threshold := wf_engine.threshold;
588:
589: BEGIN
592: FROM oe_hold_sources
593: WHERE hold_source_id = p_hold_source_id;
594: EXCEPTION
595: WHEN OTHERS THEN
596: Oe_debug_pub.ADD('Source not found.!!');
597: -- RAISE;
598: END;
599:
600: IF NVL(l_hold_entity_code,'INVALID') <> 'O' THEN
638: AND wias_to.activity_status = 'NOTIFIED'
639: AND wias_to.item_type = x.entity_type
640: AND wias_to.item_key = To_Char(x.entity_id);
641:
642: Oe_debug_pub.ADD('Processing ' || l_item_type || ':' || x.entity_id);
643: EXCEPTION
644: WHEN OTHERS THEN
645: Oe_debug_pub.ADD('Could not get activity ID for header/line : ' || x.entity_id);
646: l_found := 'F';
641:
642: Oe_debug_pub.ADD('Processing ' || l_item_type || ':' || x.entity_id);
643: EXCEPTION
644: WHEN OTHERS THEN
645: Oe_debug_pub.ADD('Could not get activity ID for header/line : ' || x.entity_id);
646: l_found := 'F';
647: -- Do not raise an exception here as the query might fail because
648: -- entity being processed is not at this workflow stage (not reached
649: -- yet/ crossed already). This is a valid scenario. say, line hasn't
669: );
670:
671: EXCEPTION
672: WHEN OTHERS THEN
673: OE_debug_pub.ADD('Failed from progress order');
674: wf_engine.threshold := l_rel_threshold;
675: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676: Oe_msg_pub.Count_And_Get
677: ( p_count => x_msg_count
697: l_auth_exists Varchar2(1) := 'N';
698: l_authorized_or_not varchar2(1) := 'Y';
699: l_dummy VARCHAR2(1);
700: BEGIN
701: OE_Debug_PUB.Add('Hold Check_Authorization Action' ||
702: p_authorized_action_code,1);
703: OE_Debug_PUB.Add('Hold_id' || to_char(p_hold_id),1);
704: OE_Debug_PUB.Add('Responsibility_Id' || to_char(p_responsibility_id),1);
705: OE_Debug_PUB.Add('Application_Id' || to_char(p_application_id),1);
699: l_dummy VARCHAR2(1);
700: BEGIN
701: OE_Debug_PUB.Add('Hold Check_Authorization Action' ||
702: p_authorized_action_code,1);
703: OE_Debug_PUB.Add('Hold_id' || to_char(p_hold_id),1);
704: OE_Debug_PUB.Add('Responsibility_Id' || to_char(p_responsibility_id),1);
705: OE_Debug_PUB.Add('Application_Id' || to_char(p_application_id),1);
706: begin
707: select 'Y'
700: BEGIN
701: OE_Debug_PUB.Add('Hold Check_Authorization Action' ||
702: p_authorized_action_code,1);
703: OE_Debug_PUB.Add('Hold_id' || to_char(p_hold_id),1);
704: OE_Debug_PUB.Add('Responsibility_Id' || to_char(p_responsibility_id),1);
705: OE_Debug_PUB.Add('Application_Id' || to_char(p_application_id),1);
706: begin
707: select 'Y'
708: Into l_auth_exists
701: OE_Debug_PUB.Add('Hold Check_Authorization Action' ||
702: p_authorized_action_code,1);
703: OE_Debug_PUB.Add('Hold_id' || to_char(p_hold_id),1);
704: OE_Debug_PUB.Add('Responsibility_Id' || to_char(p_responsibility_id),1);
705: OE_Debug_PUB.Add('Application_Id' || to_char(p_application_id),1);
706: begin
707: select 'Y'
708: Into l_auth_exists
709: from oe_hold_authorizations
711: and authorized_action_code = p_authorized_action_code
712: and rownum = 1;
713: Exception
714: When NO_DATA_FOUND Then
715: OE_Debug_PUB.Add('No Authorization exists, Authorized=Yes',1);
716: l_authorized_or_not := 'Y';
717: l_auth_exists := 'N';
718: when others then
719: null;
732: and hold_id = p_hold_id
733: and rownum = 1;
734: exception
735: when no_data_found then
736: OE_Debug_PUB.Add('Not Authorized', 1);
737: l_authorized_or_not := 'N';
738: when others then
739: null;
740: end;
780: l_wf_sqlmt VARCHAR2(3000);
781:
782: BEGIN
783:
784: OE_DEBUG_PUB.ADD('Entering InsertTable_OOH_Header');
785:
786: IF p_header_id IS NOT NULL THEN
787: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
788: ( ORDER_HOLD_ID
942:
943:
944: l_sql_rowcount := sql%rowcount;
945:
946: OE_DEBUG_PUB.ADD('l_sql_rowcount - '||l_sql_rowcount);
947:
948: IF l_sql_rowcount = 0 THEN
949:
950: SELECT meaning
957:
958: FND_MESSAGE.SET_NAME('ONT', 'OE_NO_HOLD_ALL_LINES');
959: FND_MESSAGE.SET_TOKEN('WF_ACT', l_user_activity_name);
960: OE_MSG_PUB.ADD;
961: OE_DEBUG_PUB.ADD(' Hold Not applied for All requested records');
962: RAISE FND_API.G_EXC_ERROR;
963:
964: ELSIF l_sql_rowcount > 0 THEN
965:
980: EXECUTE IMMEDIATE l_wf_sqlmt INTO l_parent_count;
981:
982: /* Note: The above query is used based on WHERE clause of the l_sqlmt but without any query on Workflow or Shipping product tables. */
983:
984: OE_DEBUG_PUB.ADD('l_parent_count - '||l_parent_count);
985:
986: IF l_sql_rowcount < (l_parent_count+l_sql_rowcount) THEN
987:
988: SELECT meaning
995:
996: FND_MESSAGE.SET_NAME('ONT', 'OE_NO_HOLD_FEW_LINES');
997: FND_MESSAGE.SET_TOKEN('WF_ACT', l_user_activity_name);
998: OE_MSG_PUB.ADD;
999: OE_DEBUG_PUB.ADD(' Hold Not applied for FEW of the requested records');
1000: END IF;
1001: END IF;
1002: END IF;
1003: END IF;
1000: END IF;
1001: END IF;
1002: END IF;
1003: END IF;
1004: OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Header Successfully');
1005: EXCEPTION
1006: WHEN OTHERS THEN
1007: OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Header with Error:'||SQLCODE);
1008: END InsertTable_OOH_Header;
1003: END IF;
1004: OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Header Successfully');
1005: EXCEPTION
1006: WHEN OTHERS THEN
1007: OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Header with Error:'||SQLCODE);
1008: END InsertTable_OOH_Header;
1009: --ER#7479609 end
1010:
1011:
1044: l_sqlmt VARCHAR2(3000);
1045: l_wf_sqlmt VARCHAR2(3000);
1046: BEGIN
1047:
1048: OE_DEBUG_PUB.ADD('Entering InsertTable_OOH_Line');
1049:
1050: IF p_line_id IS NOT NULL THEN
1051:
1052: l_sqlmt := 'INSERT INTO OE_ORDER_HOLDS_ALL
1214: l_sqlmt :=l_sqlmt||' '||p_hold_entity_where_clause;
1215: END IF;
1216:
1217:
1218: OE_DEBUG_PUB.ADD('Before Executing SQL:'||l_sqlmt);
1219: IF p_item_type is null AND p_activity_name is null THEN
1220:
1221: EXECUTE IMMEDIATE
1222: l_sqlmt USING l_user_id,
1223: l_user_id,
1224: p_hold_source_id,
1225: p_org_id ,
1226: p_hold_source_id;
1227: OE_DEBUG_PUB.ADD('After Executing SQL for non-WF Hold');
1228: ELSE
1229:
1230: EXECUTE IMMEDIATE
1231: l_sqlmt USING l_user_id,
1251:
1252: FND_MESSAGE.SET_NAME('ONT', 'OE_NO_HOLD_ALL_LINES');
1253: FND_MESSAGE.SET_TOKEN('WF_ACT',l_user_activity_name);
1254: OE_MSG_PUB.ADD;
1255: OE_DEBUG_PUB.ADD(' Hold Not applied for All requested records');
1256: RAISE FND_API.G_EXC_ERROR;
1257:
1258: ELSIF l_sql_rowcount > 0 THEN
1259: l_wf_sqlmt := 'SELECT count(*)
1273: END IF;
1274:
1275: EXECUTE IMMEDIATE l_wf_sqlmt INTO l_parent_count;
1276:
1277: OE_DEBUG_PUB.ADD('l_parent_count/sql_count'||l_parent_count||sql%rowcount);
1278: /* Note: The above query is used based on WHERE clause of thel_sqlmt but without any query on Workflow or Shipping product tables. */
1279:
1280: IF l_sql_rowcount < (l_parent_count+l_sql_rowcount) THEN
1281:
1289:
1290: FND_MESSAGE.SET_NAME('ONT', 'OE_NO_HOLD_FEW_LINES');
1291: FND_MESSAGE.SET_TOKEN('WF_ACT',l_user_activity_name);
1292: OE_MSG_PUB.ADD;
1293: OE_DEBUG_PUB.ADD(' Hold Not applied for FEW of the requested records');
1294: END IF;
1295: END IF;
1296: END IF;
1297: END IF;
1295: END IF;
1296: END IF;
1297: END IF;
1298:
1299: OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Line Successfully');
1300: EXCEPTION
1301: WHEN OTHERS THEN
1302: OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Line with Error:'||SQLCODE);
1303: END InsertTable_OOH_Line;
1298:
1299: OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Line Successfully');
1300: EXCEPTION
1301: WHEN OTHERS THEN
1302: OE_DEBUG_PUB.ADD('Exiting InsertTable_OOH_Line with Error:'||SQLCODE);
1303: END InsertTable_OOH_Line;
1304: --ER#7479609 end
1305:
1306: --ER#7479609 start
1331: l_eligible_record_tab eligible_record_tab;
1332:
1333: BEGIN
1334:
1335: OE_DEBUG_PUB.ADD('Entering PaymentType_Hold');
1336:
1337: OE_DEBUG_PUB.ADD('Header_id :'||p_hold_source_rec.header_id);
1338: OE_DEBUG_PUB.ADD('Line_id :'||p_hold_source_rec.line_id);
1339:
1333: BEGIN
1334:
1335: OE_DEBUG_PUB.ADD('Entering PaymentType_Hold');
1336:
1337: OE_DEBUG_PUB.ADD('Header_id :'||p_hold_source_rec.header_id);
1338: OE_DEBUG_PUB.ADD('Line_id :'||p_hold_source_rec.line_id);
1339:
1340: IF p_item_type = 'OEOL' or p_item_type IS NULL THEN
1341: oe_debug_pub.add('Line Level Processing Starts');
1334:
1335: OE_DEBUG_PUB.ADD('Entering PaymentType_Hold');
1336:
1337: OE_DEBUG_PUB.ADD('Header_id :'||p_hold_source_rec.header_id);
1338: OE_DEBUG_PUB.ADD('Line_id :'||p_hold_source_rec.line_id);
1339:
1340: IF p_item_type = 'OEOL' or p_item_type IS NULL THEN
1341: oe_debug_pub.add('Line Level Processing Starts');
1342: l_eligible_record_tab.delete;
1337: OE_DEBUG_PUB.ADD('Header_id :'||p_hold_source_rec.header_id);
1338: OE_DEBUG_PUB.ADD('Line_id :'||p_hold_source_rec.line_id);
1339:
1340: IF p_item_type = 'OEOL' or p_item_type IS NULL THEN
1341: oe_debug_pub.add('Line Level Processing Starts');
1342: l_eligible_record_tab.delete;
1343:
1344: IF p_hold_source_rec.hold_entity_code = 'P' and p_hold_source_rec.hold_entity_code2 IS NULL THEN
1345:
1397:
1398:
1399: FOR i in 1 .. l_eligible_record_tab.count LOOP
1400:
1401: oe_debug_pub.add('Calling InsertTable_OOH_Line for P for line_id:'||l_eligible_record_tab(i));
1402: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
1403: ,p_line_id => l_eligible_record_tab(i)
1404: ,p_org_id => p_org_id
1405: ,p_hold_entity_where_clause => NULL
1407: ,p_activity_name => p_activity_name
1408: ,p_activity_status => p_activity_status
1409: ,p_additional_where_clause => p_additional_where_clause
1410: ,x_is_hold_applied => x_is_hold_applied);
1411: oe_debug_pub.add('After Calling InsertTable_OOH_Line for P');
1412:
1413: END LOOP;
1414: END IF;
1415:
1413: END LOOP;
1414: END IF;
1415:
1416: IF p_item_type = 'OEOH' or p_item_type IS NULL THEN
1417: oe_debug_pub.add('Header Level Processing Starts');
1418:
1419: l_eligible_record_tab.delete;
1420:
1421: IF p_hold_source_rec.hold_entity_code = 'P' and p_hold_source_rec.hold_entity_code2 IS NULL THEN
1499:
1500:
1501: FOR i in 1 .. l_eligible_record_tab.count LOOP
1502:
1503: oe_debug_pub.add('Calling InsertTable_OOH_Header for P for header_id:'||l_eligible_record_tab(i));
1504: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
1505: ,p_header_id =>l_eligible_record_tab(i)
1506: ,p_org_id => p_org_id
1507: ,p_hold_entity_where_clause => NULL
1510: ,p_activity_status => p_activity_status
1511: ,p_additional_where_clause => p_additional_where_clause
1512: ,x_is_hold_applied => x_is_hold_applied);
1513:
1514: oe_debug_pub.add('After Calling InsertTable_OOH_Header for P');
1515:
1516: END LOOP;
1517: END IF;
1518:
1517: END IF;
1518:
1519:
1520:
1521: oe_debug_pub.add('Exiting PaymentType_Hold Successfully');
1522:
1523: EXCEPTION
1524: WHEN OTHERS THEN
1525: OE_DEBUG_PUB.ADD('Exiting PaymentType_Hold with Error:'||SQLCODE);
1521: oe_debug_pub.add('Exiting PaymentType_Hold Successfully');
1522:
1523: EXCEPTION
1524: WHEN OTHERS THEN
1525: OE_DEBUG_PUB.ADD('Exiting PaymentType_Hold with Error:'||SQLCODE);
1526: END PaymentType_Hold;
1527: --ER#7479609 end
1528:
1529:
1583: l_order_hold_id OE_ORDER_HOLDS.ORDER_HOLD_ID%TYPE;
1584: l_hold_id OE_HOLD_DEFINITIONS.hold_id%type;
1585:
1586: l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
1587: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1588: BEGIN
1589: x_return_status := FND_API.G_RET_STS_SUCCESS;
1590: l_user_id := OE_HOLDS_PVT.get_user_id;
1591: -- XX Also check for release reason code
1597: l_hold_id,
1598: l_order_hold_id;
1599: exit when hold_source%NOTFOUND;
1600: -- If the Order was created as an Order Based Hold.
1601: OE_Debug_PUB.Add('Rleaseing Hold' || l_hold_id);
1602: if l_hold_entity_code = 'O' THEN
1603: --x_hold_source_id := l_hold_source_id;
1604: OE_Debug_PUB.Add('Rlsing Source for:'||to_char(p_order_rec.header_id),1);
1605: l_hold_source_rec.hold_source_id := l_hold_source_id;
1600: -- If the Order was created as an Order Based Hold.
1601: OE_Debug_PUB.Add('Rleaseing Hold' || l_hold_id);
1602: if l_hold_entity_code = 'O' THEN
1603: --x_hold_source_id := l_hold_source_id;
1604: OE_Debug_PUB.Add('Rlsing Source for:'||to_char(p_order_rec.header_id),1);
1605: l_hold_source_rec.hold_source_id := l_hold_source_id;
1606: l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
1607: l_hold_source_rec.HOLD_ENTITY_ID := p_order_rec.header_id;
1608: l_hold_source_rec.HOLD_ID := l_hold_id;
1615:
1616: ELSE
1617: -- Releasing only orders from hold. Insert a record in the
1618: -- OE_HOLD_RELEASES with hold_source_id as null.
1619: OE_Debug_PUB.Add('Releasing Orders from Hold',1);
1620: --x_hold_source_id := '';
1621:
1622: SELECT OE_HOLD_RELEASES_S.NEXTVAL
1623: INTO x_hold_release_id
1701: -- hold and release the hold source also. NO
1702:
1703: -- GENESIS --
1704: IF l_debug_level > 0 THEN
1705: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDERS- BEFORE GENESIS CALL');
1706: END IF;
1707:
1708: OPEN check_hold_type_cur(l_order_hold_id);
1709: FETCH check_hold_type_cur INTO l_chk_hold;
1710: CLOSE check_hold_type_cur;
1711:
1712: IF NVL(l_chk_hold, 'N') = 'Y' THEN
1713: IF l_debug_level > 0 THEN
1714: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDERS- BEFORE SYNC_HEADER_LINE');
1715: END IF;
1716: -- XXXX Do we need to generate req_id here
1717: OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE( p_header_rec => NULL
1718: ,p_line_rec => NULL
1751: from oe_hold_definitions
1752: where hold_id = l_hold_id;
1753:
1754: BEGIN
1755: OE_Debug_PUB.Add('In OE_holds_PUB.Validate_Hold_source',1);
1756: -- Initialize API return status to success
1757: x_return_status := FND_API.G_RET_STS_SUCCESS;
1758:
1759: --XXX Check the Entity Combination
1774: > Blanket Number - Customer Bill to Site
1775: > Blanket Number - Warehouse
1776: > Blanket Number - Blanket Line Number
1777: */
1778: OE_Debug_PUB.Add('Entity Combination:' || p_hold_source_rec.hold_entity_code
1779: || '/' || p_hold_source_rec.hold_entity_code2,1);
1780:
1781: /* ER # 2662206 Start */
1782: IF p_hold_source_rec.hold_entity_code IS NOT NULL THEN
1789: IF p_hold_source_rec.hold_entity_code IN ('B','H','I','W','S','L') OR
1790: (p_hold_source_rec.hold_entity_code = 'O' AND
1791: p_hold_source_rec.line_id IS NOT NULL) OR
1792: p_hold_source_rec.hold_entity_code2 IS NOT NULL THEN
1793: oe_debug_pub.add('Error: Order WF Hold, being applied at Line Level');
1794: FND_MESSAGE.SET_NAME('ONT','OE_ORDER_HOLD_INVALID_CRITERIA');
1795: OE_MSG_PUB.ADD;
1796: RAISE FND_API.G_EXC_ERROR;
1797: END IF;
1798: ELSIF l_item_type = 'OEOL' THEN
1799: IF p_hold_source_rec.hold_entity_code IN ('C','O') AND
1800: p_hold_source_rec.line_id IS NULL AND
1801: l_apply_to_flag = 'N' THEN
1802: oe_debug_pub.add('Error: Line WF Hold, being applied at Order Level');
1803: FND_MESSAGE.SET_NAME('ONT','OE_LINE_HOLD_INVALID_CRITERIA');
1804: OE_MSG_PUB.ADD;
1805: RAISE FND_API.G_EXC_ERROR;
1806: END IF;
1825: --ER#7479609 (p_hold_source_rec.hold_entity_code = 'P' and p_hold_source_rec.hold_entity_code2 IS NULL)
1826: THEN
1827: IF l_item_type = 'OEOH' THEN
1828: NULL;
1829: oe_debug_pub.add('Order WF HOLD');
1830: ELSIF l_item_type = 'OEOL' AND l_apply_to_flag = 'N' THEN
1831: oe_debug_pub.add('Error: Line WF Hold, being applied at Order Level');
1832: FND_MESSAGE.SET_NAME('ONT','OE_LINE_HOLD_INVALID_CRITERIA');
1833: OE_MSG_PUB.ADD;
1827: IF l_item_type = 'OEOH' THEN
1828: NULL;
1829: oe_debug_pub.add('Order WF HOLD');
1830: ELSIF l_item_type = 'OEOL' AND l_apply_to_flag = 'N' THEN
1831: oe_debug_pub.add('Error: Line WF Hold, being applied at Order Level');
1832: FND_MESSAGE.SET_NAME('ONT','OE_LINE_HOLD_INVALID_CRITERIA');
1833: OE_MSG_PUB.ADD;
1834: RAISE FND_API.G_EXC_ERROR;
1835: END IF;
1838: NULL;
1839: ELSE
1840: IF l_item_type = 'OEOL' AND l_apply_to_flag = 'N' THEN
1841: NULL;
1842: oe_debug_pub.add('Line WF HOLD');
1843: ELSIF l_item_type = 'OEOH' THEN
1844: oe_debug_pub.add('Error: Order WF Hold, being applied at Line Level');
1845: FND_MESSAGE.SET_NAME('ONT','OE_ORDER_HOLD_INVALID_CRITERIA');
1846: OE_MSG_PUB.ADD;
1840: IF l_item_type = 'OEOL' AND l_apply_to_flag = 'N' THEN
1841: NULL;
1842: oe_debug_pub.add('Line WF HOLD');
1843: ELSIF l_item_type = 'OEOH' THEN
1844: oe_debug_pub.add('Error: Order WF Hold, being applied at Line Level');
1845: FND_MESSAGE.SET_NAME('ONT','OE_ORDER_HOLD_INVALID_CRITERIA');
1846: OE_MSG_PUB.ADD;
1847: RAISE FND_API.G_EXC_ERROR;
1848: END IF;
1857: if (p_hold_source_rec.hold_entity_code2 is not null) then
1858: if p_hold_source_rec.hold_entity_code = 'I' then
1859: --ER#7479609 if p_hold_source_rec.hold_entity_code2 not in ('C', 'S', 'B', 'W','H') then
1860: if p_hold_source_rec.hold_entity_code2 not in ('C', 'S', 'B', 'W','H','SM','D','PL','PR','ST','LT') then --ER#7479609
1861: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1862: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1863: OE_MSG_PUB.ADD;
1864: RAISE FND_API.G_EXC_ERROR;
1865: end if;
1865: end if;
1866: elsif p_hold_source_rec.hold_entity_code = 'W' then
1867: --ER#7479609 if p_hold_source_rec.hold_entity_code2 not in ('C', 'S', 'B') then
1868: if p_hold_source_rec.hold_entity_code2 not in ('C', 'S', 'B','LT','SM','D','ST') then --ER#7479609
1869: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1870: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1871: OE_MSG_PUB.ADD;
1872: RAISE FND_API.G_EXC_ERROR;
1873: end if;
1872: RAISE FND_API.G_EXC_ERROR;
1873: end if;
1874: elsif p_hold_source_rec.hold_entity_code = 'B' then
1875: if p_hold_source_rec.hold_entity_code2 not in ('O') then
1876: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1877: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1878: OE_MSG_PUB.ADD;
1879: RAISE FND_API.G_EXC_ERROR;
1880: end if;
1880: end if;
1881: elsif p_hold_source_rec.hold_entity_code = 'H' then
1882: --ER#7479609 if p_hold_source_rec.hold_entity_code2 not in ('S', 'B', 'W', 'L') then
1883: if p_hold_source_rec.hold_entity_code2 not in ('S', 'B', 'W', 'L','PL','PT','SM','D','LT') then --ER#7479609
1884: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1885: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1886: OE_MSG_PUB.ADD;
1887: RAISE FND_API.G_EXC_ERROR;
1888: end if;
1888: end if;
1889: --ER#7479609 start
1890: elsif p_hold_source_rec.hold_entity_code = 'TM' then
1891: if p_hold_source_rec.hold_entity_code2 not in ('OI') then
1892: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1893: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1894: OE_MSG_PUB.ADD;
1895: RAISE FND_API.G_EXC_ERROR;
1896: end if;
1895: RAISE FND_API.G_EXC_ERROR;
1896: end if;
1897: elsif p_hold_source_rec.hold_entity_code = 'PR' then
1898: if p_hold_source_rec.hold_entity_code2 not in ('T') then
1899: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1900: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1901: OE_MSG_PUB.ADD;
1902: RAISE FND_API.G_EXC_ERROR;
1903: end if;
1902: RAISE FND_API.G_EXC_ERROR;
1903: end if;
1904: elsif p_hold_source_rec.hold_entity_code = 'C' then
1905: if p_hold_source_rec.hold_entity_code2 not in ('ST','B','S','D','PL','LT','PT','OT','P','TC','SC') then
1906: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1907: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1908: OE_MSG_PUB.ADD;
1909: RAISE FND_API.G_EXC_ERROR;
1910: end if;
1909: RAISE FND_API.G_EXC_ERROR;
1910: end if;
1911: elsif p_hold_source_rec.hold_entity_code = 'PL' then
1912: if p_hold_source_rec.hold_entity_code2 not in ('TC') then
1913: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1914: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1915: OE_MSG_PUB.ADD;
1916: RAISE FND_API.G_EXC_ERROR;
1917: end if;
1916: RAISE FND_API.G_EXC_ERROR;
1917: end if;
1918: elsif p_hold_source_rec.hold_entity_code = 'OT' then
1919: if p_hold_source_rec.hold_entity_code2 not in ('LT','TC') then
1920: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1921: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1922: OE_MSG_PUB.ADD;
1923: RAISE FND_API.G_EXC_ERROR;
1924: end if;
1923: RAISE FND_API.G_EXC_ERROR;
1924: end if;
1925: elsif p_hold_source_rec.hold_entity_code = 'CD' then
1926: if p_hold_source_rec.hold_entity_code2 not in ('CB') then
1927: OE_Debug_PUB.Add('Invalid Entity Combination',1);
1928: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ENTITY_CONBINATION');
1929: OE_MSG_PUB.ADD;
1930: RAISE FND_API.G_EXC_ERROR;
1931: end if;
1934: null;
1935: end if;
1936: end if;
1937:
1938: OE_Debug_PUB.Add('Entity ID Combination->' ||
1939: to_char(p_hold_source_rec.hold_entity_id) || '/' ||
1940: to_char(p_hold_source_rec.hold_entity_id2),1);
1941: IF p_hold_source_rec.hold_entity_code = 'O' THEN
1942:
2059: WHERE lookup_code= p_hold_source_rec.hold_entity_id
2060: AND lookup_type = 'SHIP_METHOD';
2061: --ER#7479609 end
2062: ELSE
2063: OE_Debug_PUB.Add('Invalid Entity Code');
2064: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
2065: OE_MSG_PUB.ADD;
2066: RAISE FND_API.G_EXC_ERROR;
2067: END IF;
2069: /*********************************
2070: ** Check the Second Entity **
2071: *********************************/
2072: IF p_hold_source_rec.hold_entity_code2 is not null THEN
2073: OE_Debug_PUB.Add('Second Entity Code->' ||
2074: p_hold_source_rec.hold_entity_code2,1);
2075: OE_Debug_PUB.Add('Second Entity ID' ||
2076: to_char(p_hold_source_rec.hold_entity_id2),1);
2077:
2071: *********************************/
2072: IF p_hold_source_rec.hold_entity_code2 is not null THEN
2073: OE_Debug_PUB.Add('Second Entity Code->' ||
2074: p_hold_source_rec.hold_entity_code2,1);
2075: OE_Debug_PUB.Add('Second Entity ID' ||
2076: to_char(p_hold_source_rec.hold_entity_id2),1);
2077:
2078: IF p_hold_source_rec.hold_entity_code2 = 'O' THEN
2079:
2210: from fnd_currencies_vl
2211: where currency_code = p_hold_source_rec.hold_entity_id2;
2212: --ER#7479609 end
2213: ELSE
2214: OE_Debug_PUB.Add('Invalid Second Entity Code');
2215: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_CODE');
2216: OE_MSG_PUB.ADD;
2217: RAISE FND_API.G_EXC_ERROR;
2218: END IF;
2223: WHEN TOO_MANY_ROWS THEN
2224: null;
2225: WHEN NO_DATA_FOUND THEN
2226: /* XXX Error message here */
2227: OE_Debug_PUB.Add('Hold Entity ID not found for entity',1);
2228: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_ENTITY_ID');
2229: OE_MSG_PUB.ADD;
2230: RAISE FND_API.G_EXC_ERROR;
2231: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2234: ** This message is not required.
2235: FND_MESSAGE.SET_NAME('ONT', 'OE_ENTITY_NOT_ON_ORDER_OR_LINE');
2236: OE_MSG_PUB.ADD;
2237: */
2238: OE_Debug_PUB.Add('Expected error in Validate_Hold_source',1);
2239: x_return_status := FND_API.G_RET_STS_ERROR;
2240: WHEN OTHERS THEN
2241: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2242: IF OE_MSG_PUB.Check_Msg_Level
2284:
2285: EXCEPTION
2286:
2287: WHEN NO_DATA_FOUND THEN
2288: OE_Debug_PUB.Add('Invalid Hold ID'||to_char(p_hold_id),1);
2289: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_HOLD_ID');
2290: FND_MESSAGE.SET_TOKEN('HOLD_ID',p_hold_id);
2291: OE_MSG_PUB.ADD;
2292: x_return_status := FND_API.G_RET_STS_ERROR;
2318: l_user_id := OE_HOLDS_PVT.get_user_id;
2319: l_org_id := MO_GLOBAL.get_current_org_id;
2320: IF l_org_id IS NULL THEN
2321: -- org_id is null, raise an error.
2322: oe_debug_pub.add('Org_Id is NULL',1);
2323: x_return_status := FND_API.G_RET_STS_ERROR;
2324: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2325: FND_MSG_PUB.ADD;
2326: RAISE FND_API.G_EXC_ERROR;
2349: and OH.org_id= p_org_id --ER#7479609
2350: and OH.hold_source_id = HS.hold_source_id);
2351:
2352: IF l_count > 0 THEN
2353: OE_Debug_PUB.Add('Duplicate Hold Source for EntityID'||
2354: to_char(p_hold_source_rec.hold_entity_id) ,1);
2355: --FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
2356: FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD_SOURCE');
2357: OE_MSG_PUB.ADD;
2380: and OH.org_id= p_org_id --ER#7479609
2381: and OH.hold_source_id = HS.hold_source_id);
2382:
2383: IF l_count > 0 THEN
2384: OE_Debug_PUB.Add('Duplicate Hold Source for EntityID'||
2385: to_char(p_hold_source_rec.hold_entity_id) ,1);
2386: --FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
2387: FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD_SOURCE');
2388: OE_MSG_PUB.ADD;
2408: AND HS.RELEASED_FLAG = 'N'
2409: AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
2410:
2411: IF l_count > 0 THEN
2412: OE_Debug_PUB.Add('Duplicate Hold Source for EntityID'||
2413: to_char(p_hold_source_rec.hold_entity_id) ,1);
2414: --FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
2415: FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD_SOURCE');
2416: OE_MSG_PUB.ADD;
2560: l_user_id := OE_HOLDS_PVT.get_user_id;
2561: l_org_id := MO_GLOBAL.get_current_org_id;
2562: IF l_org_id IS NULL THEN
2563: -- org_id is null, raise an error.
2564: oe_debug_pub.add('Org_Id is NULL',1);
2565: x_return_status := FND_API.G_RET_STS_ERROR;
2566: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2567: FND_MSG_PUB.ADD;
2568: RAISE FND_API.G_EXC_ERROR;
2590: where OH.line_id = p_hold_source_rec.line_id
2591: and OH.hold_source_id = HS.hold_source_id);
2592:
2593: IF l_count > 0 THEN
2594: OE_Debug_PUB.Add('Duplicate Hold Source for EntityID'||
2595: to_char(p_hold_source_rec.hold_entity_id) ,1);
2596: --FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
2597: FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD_SOURCE');
2598: OE_MSG_PUB.ADD;
2619: where OH.line_id is null
2620: and OH.hold_source_id = HS.hold_source_id);
2621:
2622: IF l_count > 0 THEN
2623: OE_Debug_PUB.Add('Duplicate Hold Source for EntityID'||
2624: to_char(p_hold_source_rec.hold_entity_id) ,1);
2625: --FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
2626: FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD_SOURCE');
2627: OE_MSG_PUB.ADD;
2651: -- where header_id = p_hold_source_rec.hold_entity_id
2652: -- and line_id = nvl(p_hold_source_rec.line_id, -99));
2653:
2654: IF l_count > 0 THEN
2655: OE_Debug_PUB.Add('Duplicate Hold Source for EntityID'||
2656: to_char(p_hold_source_rec.hold_entity_id) ,1);
2657: --FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD');
2658: FND_MESSAGE.SET_NAME('ONT', 'OE_DUPLICATE_HOLD_SOURCE');
2659: OE_MSG_PUB.ADD;
2679: -- nvl(p_hold_source_rec.hold_entity_id2,-99 )
2680: -- AND HS.RELEASED_FLAG = 'N'
2681: -- AND NVL(HS.HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
2682:
2683: -- oe_debug_pub.add('Using Existing Hold Source for:' ||
2684: -- to_char(p_hold_source_rec.hold_entity_id) || 'And:' ||
2685: -- nvl(to_char(p_hold_source_rec.hold_entity_id2), 'No Second Entity'),3);
2686: -- EXCEPTION
2687: -- WHEN NO_DATA_FOUND THEN
2883:
2884: l_header_rec OE_Order_PUB.Header_Rec_Type;
2885: l_line_rec OE_Order_PUB.Line_Rec_Type;*/
2886: -- GENESIS --
2887: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2888: BEGIN
2889: x_return_status := FND_API.G_RET_STS_SUCCESS;
2890: l_user_id := OE_HOLDS_PVT.get_user_id;
2891: l_org_id := MO_GLOBAL.get_current_org_id;
2890: l_user_id := OE_HOLDS_PVT.get_user_id;
2891: l_org_id := MO_GLOBAL.get_current_org_id;
2892: IF l_org_id IS NULL THEN
2893: -- org_id is null, raise an error.
2894: oe_debug_pub.add('Org_Id is NULL',1);
2895: x_return_status := FND_API.G_RET_STS_ERROR;
2896: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
2897: FND_MSG_PUB.ADD;
2898: RAISE FND_API.G_EXC_ERROR;
2907: When NO_DATA_FOUND Then
2908: NULL; -- OE_Holds_Pvt.Validate has not yet been called.
2909: End;
2910:
2911: OE_DEBUG_PUB.Add ('Calling Overloaded Create_Order_Holds Based on Workflow from original Create_Order_Holds',1);
2912: Create_Order_Holds (
2913: p_hold_source_rec => p_hold_source_rec
2914: ,p_org_id => p_org_id --ER#7479609
2915: ,p_item_type => l_wf_item_type
2918: ,x_msg_count => x_msg_count
2919: ,x_msg_data => x_msg_data
2920: ,x_is_hold_applied => l_is_hold_applied);
2921:
2922: /*oe_debug_pub.add('p_hold_source_rec.hold_source_id:' ||
2923: p_hold_source_rec.hold_source_id);
2924: oe_debug_pub.add('Hold_entity_code/Hold_entity_id/' ||
2925: 'Hold_entity_code2/Hold_entity_id2:' ||
2926: p_hold_source_rec.Hold_entity_code || '/' ||
2920: ,x_is_hold_applied => l_is_hold_applied);
2921:
2922: /*oe_debug_pub.add('p_hold_source_rec.hold_source_id:' ||
2923: p_hold_source_rec.hold_source_id);
2924: oe_debug_pub.add('Hold_entity_code/Hold_entity_id/' ||
2925: 'Hold_entity_code2/Hold_entity_id2:' ||
2926: p_hold_source_rec.Hold_entity_code || '/' ||
2927: p_hold_source_rec.Hold_entity_id || '/' ||
2928: p_hold_source_rec.Hold_entity_code2 || '/' ||
2926: p_hold_source_rec.Hold_entity_code || '/' ||
2927: p_hold_source_rec.Hold_entity_id || '/' ||
2928: p_hold_source_rec.Hold_entity_code2 || '/' ||
2929: p_hold_source_rec.Hold_entity_id2);
2930: oe_debug_pub.add('p_hold_source_rec.header_id:' || p_hold_source_rec.header_id);
2931: oe_debug_pub.add('p_hold_source_rec.line_id:' || p_hold_source_rec.line_id);
2932: -- Insert a hold record for the order header or the order line.
2933:
2934: IF p_hold_source_rec.hold_entity_code = 'I' and
2927: p_hold_source_rec.Hold_entity_id || '/' ||
2928: p_hold_source_rec.Hold_entity_code2 || '/' ||
2929: p_hold_source_rec.Hold_entity_id2);
2930: oe_debug_pub.add('p_hold_source_rec.header_id:' || p_hold_source_rec.header_id);
2931: oe_debug_pub.add('p_hold_source_rec.line_id:' || p_hold_source_rec.line_id);
2932: -- Insert a hold record for the order header or the order line.
2933:
2934: IF p_hold_source_rec.hold_entity_code = 'I' and
2935: p_hold_source_rec.hold_entity_code2 = 'C' THEN
4637: -- GENESIS --
4638: IF p_hold_source_rec.line_id IS NOT NULL THEN
4639:
4640: IF l_debug_level > 0 THEN
4641: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.line_id IS NOT NULL');
4642: END IF;
4643:
4644: OPEN check_line_hold_type_cur(p_hold_source_rec.line_id);
4645: FETCH check_line_hold_type_cur INTO l_check_hold;
4644: OPEN check_line_hold_type_cur(p_hold_source_rec.line_id);
4645: FETCH check_line_hold_type_cur INTO l_check_hold;
4646: CLOSE check_line_hold_type_cur;
4647: IF l_debug_level > 0 THEN
4648: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold' || l_check_hold);
4649: END IF;
4650:
4651: ELSIF p_hold_source_rec.line_id IS NULL AND
4652: p_hold_source_rec.header_id IS NOT NULL THEN
4651: ELSIF p_hold_source_rec.line_id IS NULL AND
4652: p_hold_source_rec.header_id IS NOT NULL THEN
4653:
4654: IF l_debug_level > 0 THEN
4655: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.header_id IS NOT NULL');
4656: END IF;
4657: OPEN check_hdr_hold_type_cur(p_hold_source_rec.header_id);
4658: FETCH check_hdr_hold_type_cur INTO l_check_hold;
4659: CLOSE check_hdr_hold_type_cur;
4657: OPEN check_hdr_hold_type_cur(p_hold_source_rec.header_id);
4658: FETCH check_hdr_hold_type_cur INTO l_check_hold;
4659: CLOSE check_hdr_hold_type_cur;
4660: IF l_debug_level > 0 THEN
4661: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold' || l_check_hold);
4662: END IF;
4663:
4664: ELSIF p_hold_source_rec.line_id IS NULL AND
4665: p_hold_source_rec.header_id IS NULL AND
4665: p_hold_source_rec.header_id IS NULL AND
4666: p_hold_source_rec.hold_source_id IS NOT NULL THEN
4667:
4668: IF l_debug_level > 0 THEN
4669: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.hold_source_id IS NOT NULL');
4670: END IF;
4671: OPEN check_src_hold_type_cur(p_hold_source_rec.hold_source_id);
4672: FETCH check_src_hold_type_cur INTO l_check_hold;
4673: CLOSE check_src_hold_type_cur;
4672: FETCH check_src_hold_type_cur INTO l_check_hold;
4673: CLOSE check_src_hold_type_cur;
4674:
4675: IF l_debug_level > 0 THEN
4676: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold :' || l_check_hold);
4677: END IF;
4678:
4679: END IF;
4680:
4678:
4679: END IF;
4680:
4681: IF l_debug_level > 0 THEN
4682: oe_debug_pub.add( 'OE_HOLDS_PVT - CREATE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
4683: END IF;
4684:
4685: IF NVL(l_check_hold, 'N') = 'Y' THEN
4686:
4684:
4685: IF NVL(l_check_hold, 'N') = 'Y' THEN
4686:
4687: IF l_debug_level > 0 THEN
4688: oe_debug_pub.add( 'OE_HOLDS_PVT - CREATE ORDER HOLDS - l_check_hold: ' || l_check_hold);
4689: END IF;
4690:
4691: IF p_hold_source_rec.hold_entity_code = 'O' THEN
4692: IF l_debug_level > 0 THEN
4689: END IF;
4690:
4691: IF p_hold_source_rec.hold_entity_code = 'O' THEN
4692: IF l_debug_level > 0 THEN
4693: oe_debug_pub.add(' p_hold_source_rec.hold_entity_code ' || p_hold_source_rec.hold_entity_code);
4694: END IF;
4695: IF p_hold_source_rec.hold_entity_id is NOT NULL THEN
4696: IF l_debug_level > 0 THEN
4697: oe_debug_pub.add(' p_hold_source_rec.hold_entity_id : ' || p_hold_source_rec.hold_entity_id);
4693: oe_debug_pub.add(' p_hold_source_rec.hold_entity_code ' || p_hold_source_rec.hold_entity_code);
4694: END IF;
4695: IF p_hold_source_rec.hold_entity_id is NOT NULL THEN
4696: IF l_debug_level > 0 THEN
4697: oe_debug_pub.add(' p_hold_source_rec.hold_entity_id : ' || p_hold_source_rec.hold_entity_id);
4698: END IF;
4699: oe_header_util.query_row ( p_header_id => p_hold_source_rec.hold_entity_id,
4700: x_header_rec => l_header_rec);
4701: END IF; -- p_hold_source_rec.hold_entity_id is NOT NULL
4701: END IF; -- p_hold_source_rec.hold_entity_id is NOT NULL
4702:
4703: IF p_hold_source_rec.line_id is not NULL THEN
4704: IF l_debug_level > 0 THEN
4705: oe_debug_pub.add(' p_hold_source_rec.line_id : ' || p_hold_source_rec.line_id );
4706: END IF;
4707: oe_line_util.query_row(
4708: p_line_id => p_hold_source_rec.line_id
4709: ,x_line_rec => l_line_rec
4717: ,p_change_type => 'APPLY');
4718: ELSE --p_hold_source_rec.hold_entity_code = 'O'
4719:
4720: IF l_debug_level > 0 THEN
4721: oe_debug_pub.add(' p_hold_source_rec.hold_entity_code ' || p_hold_source_rec.hold_entity_code);
4722: END IF;
4723:
4724: IF p_hold_source_rec.header_id is NOT NULL THEN
4725: oe_header_util.query_row ( p_header_id => p_hold_source_rec.header_id,
4796: l_chk_hld VARCHAR2(1) := 'N';
4797: -- GENESIS --
4798:
4799: l_user_id NUMBER;
4800: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4801:
4802: BEGIN
4803: x_return_status := FND_API.G_RET_STS_SUCCESS;
4804: l_user_id := OE_HOLDS_PVT.get_user_id;
4818: -- IF l_entity_code = 'O' THEN
4819: -- END IF;
4820: -- GENESIS --
4821: IF l_debug_level > 0 THEN
4822: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDER HOLDS - BEFORE GENESIS CALL');
4823: END IF;
4824: OPEN check_hold_typ_cur(p_hold_release_rec.hold_source_id);
4825: FETCH check_hold_typ_cur INTO l_chk_hld;
4826: CLOSE check_hold_typ_cur;
4826: CLOSE check_hold_typ_cur;
4827:
4828: IF NVL(l_chk_hld, 'N') = 'Y' THEN
4829: IF l_debug_level > 0 THEN
4830: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
4831: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_source _id '|| p_hold_release_rec.hold_source_id);
4832: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_release_id' ||p_hold_release_rec.hold_release_id);
4833: END IF;
4834: -- XXXX Do we need to generate req_id here
4827:
4828: IF NVL(l_chk_hld, 'N') = 'Y' THEN
4829: IF l_debug_level > 0 THEN
4830: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
4831: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_source _id '|| p_hold_release_rec.hold_source_id);
4832: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_release_id' ||p_hold_release_rec.hold_release_id);
4833: END IF;
4834: -- XXXX Do we need to generate req_id here
4835: OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE(p_header_rec => NULL
4828: IF NVL(l_chk_hld, 'N') = 'Y' THEN
4829: IF l_debug_level > 0 THEN
4830: oe_debug_pub.add( 'OE_HOLDS_PVT - RELEASE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
4831: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_source _id '|| p_hold_release_rec.hold_source_id);
4832: oe_debug_pub.add( 'OE_HOLDS_PVT - hold_release_id' ||p_hold_release_rec.hold_release_id);
4833: END IF;
4834: -- XXXX Do we need to generate req_id here
4835: OE_SYNC_ORDER_PVT.SYNC_HEADER_LINE(p_header_rec => NULL
4836: ,p_line_rec => NULL
4873:
4874: EXCEPTION
4875:
4876: WHEN NO_DATA_FOUND THEN
4877: oe_debug_pub.add('Invalid Reason Code:' ||
4878: p_hold_release_rec.release_reason_code ,2);
4879: FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_REASON_CODE');
4880: FND_MESSAGE.SET_TOKEN('REASON_CODE',
4881: p_hold_release_rec.release_reason_code);
4887: SELECT OE_HOLD_RELEASES_S.NEXTVAL
4888: INTO x_hold_release_id
4889: FROM DUAL;
4890:
4891: oe_debug_pub.add('Creating OE_HOLD_RELEASES record', 1);
4892: /*Bug3042838 Added nvl condition for insertion into CREATED_BY column */
4893: INSERT INTO OE_HOLD_RELEASES
4894: ( HOLD_RELEASE_ID
4895: , CREATION_DATE
4954: );
4955:
4956: -- Flag the hold source as released
4957:
4958: oe_debug_pub.add('Updating oe_hold_sources for Release', 1);
4959: --ER#7479609 UPDATE oe_hold_sources
4960: UPDATE oe_hold_sources_all --ER#7479609
4961: SET hold_release_id = x_hold_release_id
4962: , released_flag = 'Y'
5037: -- Retrieving hold source ID if not passed
5038: IF p_hold_source_rec.hold_source_id IS NULL THEN
5039: IF p_hold_source_rec.hold_entity_code = 'O' AND
5040: p_hold_source_rec.hold_entity_code2 is null THEN
5041: oe_debug_pub.add('Releasing Order based holds', 1);
5042: OPEN order_hold_source;
5043: FETCH order_hold_source INTO l_hold_release_rec.hold_source_id;
5044: IF (order_hold_source%NOTFOUND) THEN
5045: /* Note:Fix for bug#2669137 */
5043: FETCH order_hold_source INTO l_hold_release_rec.hold_source_id;
5044: IF (order_hold_source%NOTFOUND) THEN
5045: /* Note:Fix for bug#2669137 */
5046: /*
5047: oe_debug_pub.add('Missing Order Hold Source ID...',1);
5048: oe_debug_pub.add('Entity Code/ID/Header_id/Line_id' ||
5049: p_hold_source_rec.hold_entity_code || '/' ||
5050: to_char(p_hold_source_rec.hold_entity_id) || '/' ||
5051: p_hold_source_rec.header_id || '/' ||
5044: IF (order_hold_source%NOTFOUND) THEN
5045: /* Note:Fix for bug#2669137 */
5046: /*
5047: oe_debug_pub.add('Missing Order Hold Source ID...',1);
5048: oe_debug_pub.add('Entity Code/ID/Header_id/Line_id' ||
5049: p_hold_source_rec.hold_entity_code || '/' ||
5050: to_char(p_hold_source_rec.hold_entity_id) || '/' ||
5051: p_hold_source_rec.header_id || '/' ||
5052: p_hold_source_rec.line_id,1);
5049: p_hold_source_rec.hold_entity_code || '/' ||
5050: to_char(p_hold_source_rec.hold_entity_id) || '/' ||
5051: p_hold_source_rec.header_id || '/' ||
5052: p_hold_source_rec.line_id,1);
5053: oe_debug_pub.add('Entity Code2/ID2' ||
5054: p_hold_source_rec.hold_entity_code2 || '/' ||
5055: to_char(p_hold_source_rec.hold_entity_id2) ,1);
5056: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_SOURCE');
5057: OE_MSG_PUB.ADD;
5064: ELSE
5065: OPEN hold_source;
5066: FETCH hold_source INTO l_hold_release_rec.hold_source_id;
5067: IF (hold_source%NOTFOUND) THEN
5068: oe_debug_pub.add('Missing Hold Source ID...',1);
5069: oe_debug_pub.add('Entity Code/ID' ||
5070: p_hold_source_rec.hold_entity_code || '/' ||
5071: to_char(p_hold_source_rec.hold_entity_id) ,1);
5072: oe_debug_pub.add('Entity Code2/ID2' ||
5065: OPEN hold_source;
5066: FETCH hold_source INTO l_hold_release_rec.hold_source_id;
5067: IF (hold_source%NOTFOUND) THEN
5068: oe_debug_pub.add('Missing Hold Source ID...',1);
5069: oe_debug_pub.add('Entity Code/ID' ||
5070: p_hold_source_rec.hold_entity_code || '/' ||
5071: to_char(p_hold_source_rec.hold_entity_id) ,1);
5072: oe_debug_pub.add('Entity Code2/ID2' ||
5073: p_hold_source_rec.hold_entity_code2 || '/' ||
5068: oe_debug_pub.add('Missing Hold Source ID...',1);
5069: oe_debug_pub.add('Entity Code/ID' ||
5070: p_hold_source_rec.hold_entity_code || '/' ||
5071: to_char(p_hold_source_rec.hold_entity_id) ,1);
5072: oe_debug_pub.add('Entity Code2/ID2' ||
5073: p_hold_source_rec.hold_entity_code2 || '/' ||
5074: to_char(p_hold_source_rec.hold_entity_id2) ,1);
5075: FND_MESSAGE.SET_NAME('ONT', 'OE_MISSING_HOLD_SOURCE');
5076: OE_MSG_PUB.ADD;
5079: CLOSE hold_source;
5080: END IF; -- p_hold_source_rec.hold_entity_code = 'O'
5081: ELSE
5082: l_hold_release_rec.hold_source_id := p_hold_source_rec.hold_source_id;
5083: oe_debug_pub.add('Using Hold Source ID:' ||
5084: to_char(l_hold_release_rec.hold_source_id) ,1);
5085: END IF;
5086:
5087: OE_HOLDS_PVT.Create_Release_Source(p_hold_release_rec => l_hold_release_rec,
5096: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5097: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5098: END IF;
5099: END IF;
5100: oe_debug_pub.add('Calling Release_Order_holds using l_hold_release_id' ||
5101: to_char(l_hold_release_id) ,1);
5102: l_hold_release_rec.hold_release_id := l_hold_release_id;
5103:
5104: OE_HOLDS_PVT.Release_Order_holds(
5165: FND_MESSAGE.SET_NAME('ONT', 'OE_COGS_INVALID_LINE_ID');
5166: FND_MESSAGE.SET_TOKEN('LINE_ID',p_line_id);
5167: OE_MSG_PUB.ADD;
5168: END IF;
5169: oe_debug_pub.add('Return Status : ' || x_return_status, 5);
5170: -- RAISE FND_API.G_EXC_ERROR; -- Commented for bug 7112725
5171: END Validate_Order;
5172:
5173:
5267: IF (order_hold%notfound) THEN
5268: EXIT;
5269: END IF;
5270:
5271: OE_Debug_PUB.Add('Deleting order hold record',1);
5272:
5273: DELETE FROM OE_ORDER_HOLDS_all
5274: WHERE order_hold_id = l_order_hold_id;
5275:
5272:
5273: DELETE FROM OE_ORDER_HOLDS_all
5274: WHERE order_hold_id = l_order_hold_id;
5275:
5276: OE_Debug_PUB.Add('Deleting hold release record',1);
5277: DELETE FROM OE_HOLD_RELEASES
5278: WHERE HOLD_RELEASE_ID = l_hold_release_id
5279: AND HOLD_RELEASE_ID NOT IN (SELECT NVL(HOLD_RELEASE_ID,0)
5280: FROM OE_ORDER_HOLDS_all
5292: FETCH hold_source INTO l_hold_source_id, l_hold_release_id;
5293: IF (hold_source%notfound) THEN
5294: EXIT;
5295: END IF;
5296: OE_Debug_PUB.Add('Deleting hold source record',1);
5297:
5298: DELETE FROM OE_HOLD_SOURCES_all
5299: WHERE HOLD_SOURCE_ID = l_hold_source_id;
5300:
5297:
5298: DELETE FROM OE_HOLD_SOURCES_all
5299: WHERE HOLD_SOURCE_ID = l_hold_source_id;
5300:
5301: OE_Debug_PUB.Add('Deleting hold release record',1);
5302: DELETE FROM OE_HOLD_RELEASES
5303: WHERE HOLD_RELEASE_ID = l_hold_release_id
5304: AND HOLD_RELEASE_ID NOT IN
5305: ( SELECT NVL(HOLD_RELEASE_ID,0)
5326: IF (line_hold%notfound) THEN
5327: EXIT;
5328: END IF;
5329:
5330: OE_Debug_PUB.Add('Deleting order hold record',1);
5331:
5332: DELETE FROM OE_ORDER_HOLDS
5333: WHERE order_hold_id = l_order_hold_id;
5334:
5416: l_user_activity_name VARCHAR2(80);
5417:
5418: BEGIN
5419: x_return_status := FND_API.G_RET_STS_SUCCESS;
5420: OE_DEBUG_PUB.Add('IN Apply Holds..Orders',1);
5421:
5422: for j in 1..p_order_tbl.COUNT loop
5423: OE_DEBUG_PUB.Add('IN Apply Holds Loop',3);
5424: l_hold_exists := 'N'; --bug 5548778
5419: x_return_status := FND_API.G_RET_STS_SUCCESS;
5420: OE_DEBUG_PUB.Add('IN Apply Holds..Orders',1);
5421:
5422: for j in 1..p_order_tbl.COUNT loop
5423: OE_DEBUG_PUB.Add('IN Apply Holds Loop',3);
5424: l_hold_exists := 'N'; --bug 5548778
5425: x_return_status := FND_API.G_RET_STS_SUCCESS;
5426: l_hold_source_rec.hold_entity_code := 'O';
5427: l_hold_source_rec.hold_entity_id := p_order_tbl(j).header_id;
5433:
5434: --dbms_output.put_line ('p_order_tbl.header_id'||p_order_tbl(j).header_id);
5435: --dbms_output.put_line ('Hold_id'|| l_hold_source_rec.hold_id);
5436:
5437: OE_DEBUG_PUB.Add('headerID'|| to_char(p_order_tbl(j).header_id),3);
5438: OE_DEBUG_PUB.Add('HoldID:' || to_char(l_hold_source_rec.hold_id),3 );
5439:
5440: OE_DEBUG_PUB.Add('Validating ORder',1);
5441: Validate_Order (p_order_tbl(j).header_id,
5434: --dbms_output.put_line ('p_order_tbl.header_id'||p_order_tbl(j).header_id);
5435: --dbms_output.put_line ('Hold_id'|| l_hold_source_rec.hold_id);
5436:
5437: OE_DEBUG_PUB.Add('headerID'|| to_char(p_order_tbl(j).header_id),3);
5438: OE_DEBUG_PUB.Add('HoldID:' || to_char(l_hold_source_rec.hold_id),3 );
5439:
5440: OE_DEBUG_PUB.Add('Validating ORder',1);
5441: Validate_Order (p_order_tbl(j).header_id,
5442: p_order_tbl(j).line_id,
5436:
5437: OE_DEBUG_PUB.Add('headerID'|| to_char(p_order_tbl(j).header_id),3);
5438: OE_DEBUG_PUB.Add('HoldID:' || to_char(l_hold_source_rec.hold_id),3 );
5439:
5440: OE_DEBUG_PUB.Add('Validating ORder',1);
5441: Validate_Order (p_order_tbl(j).header_id,
5442: p_order_tbl(j).line_id,
5443: x_return_status => x_return_status,
5444: x_msg_count => x_msg_count,
5447: --dbms_output.put_line ('Validate_Order:x_return_status' || x_return_status );
5448: --dbms_output.put_line ('Validate_Order:x_msg_count' || to_char(x_msg_count) );
5449: --dbms_output.put_line ('Validate_Order:x_msg_data' || x_msg_data );
5450:
5451: OE_DEBUG_PUB.Add('After Validate_Order',2);
5452: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5453: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2 );
5454: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2 );
5455:
5448: --dbms_output.put_line ('Validate_Order:x_msg_count' || to_char(x_msg_count) );
5449: --dbms_output.put_line ('Validate_Order:x_msg_data' || x_msg_data );
5450:
5451: OE_DEBUG_PUB.Add('After Validate_Order',2);
5452: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5453: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2 );
5454: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2 );
5455:
5456: -- Added for bug 7112725
5449: --dbms_output.put_line ('Validate_Order:x_msg_data' || x_msg_data );
5450:
5451: OE_DEBUG_PUB.Add('After Validate_Order',2);
5452: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5453: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2 );
5454: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2 );
5455:
5456: -- Added for bug 7112725
5457: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5450:
5451: OE_DEBUG_PUB.Add('After Validate_Order',2);
5452: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5453: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2 );
5454: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2 );
5455:
5456: -- Added for bug 7112725
5457: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5458: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5485: x_return_status => x_return_status,
5486: x_msg_count => x_msg_count,
5487: x_msg_data => x_msg_data
5488: );
5489: OE_DEBUG_PUB.Add('After Create_Hold_Source, x_return_status:' ||
5490: x_return_status,1);
5491: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),3 );
5492: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data ,3);
5493: OE_DEBUG_PUB.Add('l_hold_exists:' || l_hold_exists ,3);
5487: x_msg_data => x_msg_data
5488: );
5489: OE_DEBUG_PUB.Add('After Create_Hold_Source, x_return_status:' ||
5490: x_return_status,1);
5491: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),3 );
5492: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data ,3);
5493: OE_DEBUG_PUB.Add('l_hold_exists:' || l_hold_exists ,3);
5494: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5495: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5488: );
5489: OE_DEBUG_PUB.Add('After Create_Hold_Source, x_return_status:' ||
5490: x_return_status,1);
5491: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),3 );
5492: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data ,3);
5493: OE_DEBUG_PUB.Add('l_hold_exists:' || l_hold_exists ,3);
5494: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5495: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5496: RAISE FND_API.G_EXC_ERROR;
5489: OE_DEBUG_PUB.Add('After Create_Hold_Source, x_return_status:' ||
5490: x_return_status,1);
5491: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),3 );
5492: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data ,3);
5493: OE_DEBUG_PUB.Add('l_hold_exists:' || l_hold_exists ,3);
5494: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5495: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5496: RAISE FND_API.G_EXC_ERROR;
5497: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5498: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5499: END IF;
5500: END IF;
5501:
5502: OE_DEBUG_PUB.Add('l_hold_source_id->' || to_char(l_hold_source_id) ,1);
5503: l_hold_source_rec.hold_source_id := l_hold_source_id;
5504: IF l_hold_exists = 'N' THEN --bug 5548778
5505: /*Added the Select query and IF Condition below and calling overloaded create_order_holds procedure
5506: for WF_HOLDS ER (bug 6449458)*/
5512: END IF;
5513:
5514: IF l_wf_item_type IS NOT NULL AND l_wf_activity_name IS NOT
5515: NULL THEN
5516: OE_DEBUG_PUB.Add ('Calling Overloaded Create_Order_Holds Based on Workflow',1);
5517: Create_Order_Holds (
5518: p_hold_source_rec => l_hold_source_rec
5519: ,p_item_type => l_wf_item_type
5520: ,p_activity_name => l_wf_activity_name
5524: ,x_is_hold_applied => l_is_hold_applied);
5525:
5526: ELSE
5527:
5528: OE_DEBUG_PUB.Add ('Calling Create_Order_Holds',1);
5529: Create_Order_Holds (
5530: p_hold_source_rec => l_hold_source_rec
5531: ,x_return_status => x_return_status
5532: ,x_msg_count => x_msg_count
5531: ,x_return_status => x_return_status
5532: ,x_msg_count => x_msg_count
5533: ,x_msg_data => x_msg_data
5534: );
5535: OE_DEBUG_PUB.Add('After Create_Order_Holds',1);
5536: END IF; -- l_item_type and l_activity_name
5537:
5538: IF NVL(l_is_hold_applied,FALSE) THEN
5539: l_count_of_holds_applied := l_count_of_holds_applied + 1;
5536: END IF; -- l_item_type and l_activity_name
5537:
5538: IF NVL(l_is_hold_applied,FALSE) THEN
5539: l_count_of_holds_applied := l_count_of_holds_applied + 1;
5540: OE_DEBUG_PUB.Add('Hold is applied :',2);
5541: END IF;
5542: l_is_hold_applied := FALSE;
5543: /* OE_DEBUG_PUB.Add ('Calling Create_Order_Holds',1);
5544: Create_Order_Holds (
5539: l_count_of_holds_applied := l_count_of_holds_applied + 1;
5540: OE_DEBUG_PUB.Add('Hold is applied :',2);
5541: END IF;
5542: l_is_hold_applied := FALSE;
5543: /* OE_DEBUG_PUB.Add ('Calling Create_Order_Holds',1);
5544: Create_Order_Holds (
5545: p_hold_source_rec => l_hold_source_rec
5546: ,x_return_status => x_return_status
5547: ,x_msg_count => x_msg_count
5546: ,x_return_status => x_return_status
5547: ,x_msg_count => x_msg_count
5548: ,x_msg_data => x_msg_data
5549: );
5550: OE_DEBUG_PUB.Add('After Create_Order_Holds',1);*/
5551: END IF;
5552: end loop;
5553: /*Added the query and IF Condition below for WF_HOLDS ER (bug 6449458)*/
5554: IF l_wf_item_type is not null and l_wf_activity_name is not null THEN
5562: IF p_order_tbl.COUNT = 1 AND l_count_of_holds_applied = 0 THEN
5563: fnd_message.set_name('ONT', 'OE_NO_HOLD_FOR_ACTIVITY');
5564: fnd_message.set_token('WF_ACT',l_user_activity_name);
5565: OE_MSG_PUB.ADD;
5566: oe_debug_pub.add(' Hold Not applied for the requested line');
5567: RAISE FND_API.G_EXC_ERROR;
5568: ELSIF l_count_of_holds_applied = 0
5569: AND p_order_tbl.COUNT > 1 THEN
5570: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
5569: AND p_order_tbl.COUNT > 1 THEN
5570: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
5571: fnd_message.set_token('WF_ACT',l_user_activity_name);
5572: OE_MSG_PUB.ADD;
5573: oe_debug_pub.add('Hold Not applied for ALL requested lines');
5574: RAISE FND_API.G_EXC_ERROR;
5575: ELSIF p_order_tbl.COUNT > l_count_of_holds_applied
5576: AND p_order_tbl.COUNT > 1 THEN
5577: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
5576: AND p_order_tbl.COUNT > 1 THEN
5577: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
5578: fnd_message.set_token('WF_ACT',l_user_activity_name);
5579: OE_MSG_PUB.ADD;
5580: oe_debug_pub.add('Hold Not applied for FEW requested lines');
5581: ELSE
5582: NULL; -- No messages are required to be logged.
5583: END IF;
5584: END IF;
5585:
5586: EXCEPTION
5587: WHEN OTHERS THEN
5588: --dbms_output.put_line ('ApplyHolds-EXCEPTION');
5589: OE_DEBUG_PUB.Add('Error:Apply Holds',1);
5590: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5591: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),1 );
5592: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data ,1);
5593: END Apply_Holds;
5586: EXCEPTION
5587: WHEN OTHERS THEN
5588: --dbms_output.put_line ('ApplyHolds-EXCEPTION');
5589: OE_DEBUG_PUB.Add('Error:Apply Holds',1);
5590: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5591: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),1 );
5592: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data ,1);
5593: END Apply_Holds;
5594:
5587: WHEN OTHERS THEN
5588: --dbms_output.put_line ('ApplyHolds-EXCEPTION');
5589: OE_DEBUG_PUB.Add('Error:Apply Holds',1);
5590: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5591: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),1 );
5592: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data ,1);
5593: END Apply_Holds;
5594:
5595:
5588: --dbms_output.put_line ('ApplyHolds-EXCEPTION');
5589: OE_DEBUG_PUB.Add('Error:Apply Holds',1);
5590: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
5591: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),1 );
5592: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data ,1);
5593: END Apply_Holds;
5594:
5595:
5596: Procedure Apply_Holds(
5634: x_msg_count => x_msg_count,
5635: x_msg_data => x_msg_data,
5636: x_is_hold_applied => l_hold_msg_applied);
5637: /*BEGIN
5638: OE_DEBUG_PUB.Add('In OE_Holds_pvt.Apply Holds, Creating Hold Source',1);
5639:
5640: x_return_status := FND_API.G_RET_STS_SUCCESS;
5641: l_hold_source_rec := p_hold_source_rec;
5642: Validate (p_hold_source_rec => p_hold_source_rec,
5642: Validate (p_hold_source_rec => p_hold_source_rec,
5643: x_return_status => x_return_status,
5644: x_msg_count => x_msg_count,
5645: x_msg_data => x_msg_data );
5646: OE_DEBUG_PUB.Add('Validate return status:' || x_return_status,1);
5647: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5648: OE_Debug_PUB.Add('Validate not successful',1);
5649: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5650: RAISE FND_API.G_EXC_ERROR;
5644: x_msg_count => x_msg_count,
5645: x_msg_data => x_msg_data );
5646: OE_DEBUG_PUB.Add('Validate return status:' || x_return_status,1);
5647: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5648: OE_Debug_PUB.Add('Validate not successful',1);
5649: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5650: RAISE FND_API.G_EXC_ERROR;
5651: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5652: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5651: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5652: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5653: END IF;
5654: ELSE
5655: OE_DEBUG_PUB.Add ('Calling Create_Hold_Source bug 5548778 overload',1);
5656: Create_Hold_Source (
5657: p_hold_source_rec => p_hold_source_rec,
5658: x_hold_source_id => l_hold_source_id,
5659: x_hold_exists => l_hold_exists,
5660: x_return_status => x_return_status,
5661: x_msg_count => x_msg_count,
5662: x_msg_data => x_msg_data
5663: );
5664: OE_DEBUG_PUB.Add('x_return_status->' || x_return_status,1);
5665: OE_DEBUG_PUB.Add('x_msg_count->' || x_msg_count,1);
5666: OE_DEBUG_PUB.Add('x_msg_data' || x_msg_data,1);
5667: OE_DEBUG_PUB.Add('l_hold_exists' || l_hold_exists,1);
5668: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5661: x_msg_count => x_msg_count,
5662: x_msg_data => x_msg_data
5663: );
5664: OE_DEBUG_PUB.Add('x_return_status->' || x_return_status,1);
5665: OE_DEBUG_PUB.Add('x_msg_count->' || x_msg_count,1);
5666: OE_DEBUG_PUB.Add('x_msg_data' || x_msg_data,1);
5667: OE_DEBUG_PUB.Add('l_hold_exists' || l_hold_exists,1);
5668: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5669: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5662: x_msg_data => x_msg_data
5663: );
5664: OE_DEBUG_PUB.Add('x_return_status->' || x_return_status,1);
5665: OE_DEBUG_PUB.Add('x_msg_count->' || x_msg_count,1);
5666: OE_DEBUG_PUB.Add('x_msg_data' || x_msg_data,1);
5667: OE_DEBUG_PUB.Add('l_hold_exists' || l_hold_exists,1);
5668: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5669: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5670: RAISE FND_API.G_EXC_ERROR;
5663: );
5664: OE_DEBUG_PUB.Add('x_return_status->' || x_return_status,1);
5665: OE_DEBUG_PUB.Add('x_msg_count->' || x_msg_count,1);
5666: OE_DEBUG_PUB.Add('x_msg_data' || x_msg_data,1);
5667: OE_DEBUG_PUB.Add('l_hold_exists' || l_hold_exists,1);
5668: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5669: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5670: RAISE FND_API.G_EXC_ERROR;
5671: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5671: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
5672: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5673: END IF;
5674: END IF;
5675: OE_DEBUG_PUB.Add('l_hold_source_id->' || to_char(l_hold_source_id) ,1);
5676: l_hold_source_rec.hold_source_id := l_hold_source_id;
5677: --bug 5548778
5678: IF l_hold_exists = 'N' THEN
5679: OE_DEBUG_PUB.Add ('Calling Create_Order_Holds',1);
5675: OE_DEBUG_PUB.Add('l_hold_source_id->' || to_char(l_hold_source_id) ,1);
5676: l_hold_source_rec.hold_source_id := l_hold_source_id;
5677: --bug 5548778
5678: IF l_hold_exists = 'N' THEN
5679: OE_DEBUG_PUB.Add ('Calling Create_Order_Holds',1);
5680: Create_Order_Holds (
5681: p_hold_source_rec => l_hold_source_rec
5682: ,x_return_status => x_return_status
5683: ,x_msg_count => x_msg_count
5682: ,x_return_status => x_return_status
5683: ,x_msg_count => x_msg_count
5684: ,x_msg_data => x_msg_data
5685: );
5686: OE_DEBUG_PUB.Add('After Create_Order_Holds',1);
5687: END IF;
5688: END IF;*/
5689: END Apply_Holds;
5690:
5720: from oe_order_lines_all
5721: where line_id = l_hold_source_rec.line_id;
5722: end if;
5723:
5724: oe_debug_pub.add('l_hold_source_rec.header_id ' || l_hold_source_rec.header_id);
5725: oe_debug_pub.add('l_hold_source_rec.line_id ' || l_hold_source_rec.line_id);
5726: l_order_rec.header_id := l_hold_source_rec.header_id;
5727: -- bug 5051532
5728:
5721: where line_id = l_hold_source_rec.line_id;
5722: end if;
5723:
5724: oe_debug_pub.add('l_hold_source_rec.header_id ' || l_hold_source_rec.header_id);
5725: oe_debug_pub.add('l_hold_source_rec.line_id ' || l_hold_source_rec.line_id);
5726: l_order_rec.header_id := l_hold_source_rec.header_id;
5727: -- bug 5051532
5728:
5729: Validate_Order (l_hold_source_rec.header_id,
5731: x_return_status => x_return_status,
5732: x_msg_count => x_msg_count,
5733: x_msg_data => x_msg_data
5734: );
5735: oe_debug_pub.add('After Validate_Order with x_return_status'||x_return_status,2);
5736:
5737: -- Added for bug 7112725
5738: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5739: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5748: p_hold_source_rec => l_hold_source_rec,
5749: x_return_status => x_return_status,
5750: x_msg_count => x_msg_count,
5751: x_msg_data => x_msg_data );
5752: oe_debug_pub.add('After oe_holds_pvt.release_orders with x_return_status' || x_return_status, 1);
5753: --bug 3977747 ends--
5754: else
5755:
5756: if (p_hold_source_rec.hold_source_id is null )then
5789: l_order_rec OE_HOLDS_PVT.order_rec_type;
5790: l_hold_release_rec OE_HOLDS_PVT.hold_release_rec_type;
5791: BEGIN
5792: x_return_status := FND_API.G_RET_STS_SUCCESS;
5793: oe_debug_pub.add('In Release Holds..Orders',1);
5794: for j in 1..p_order_tbl.COUNT loop
5795: -- IF p_order_tbl(j).header_id IS NULL AND
5796: -- p_order_tbl(j).line_id NULL THEN ERROR
5797: l_order_rec.header_id := p_order_tbl(j).header_id;
5798: l_order_rec.line_id := p_order_tbl(j).line_id;
5799: l_hold_release_rec.release_reason_code := p_release_reason_code;
5800: l_hold_release_rec.release_comment := p_release_comment;
5801: l_hold_source_rec.hold_id := p_hold_id;
5802: oe_debug_pub.add('HeaderID:' || l_order_rec.header_id );
5803: oe_debug_pub.add('LineID:' || l_order_rec.Line_id );
5804: -- XXX Need some analysis
5805: -- When release holds for orders, check to see if this order was put on
5806: -- hold as an
5799: l_hold_release_rec.release_reason_code := p_release_reason_code;
5800: l_hold_release_rec.release_comment := p_release_comment;
5801: l_hold_source_rec.hold_id := p_hold_id;
5802: oe_debug_pub.add('HeaderID:' || l_order_rec.header_id );
5803: oe_debug_pub.add('LineID:' || l_order_rec.Line_id );
5804: -- XXX Need some analysis
5805: -- When release holds for orders, check to see if this order was put on
5806: -- hold as an
5807: -- Order based hold. If yes, then release the Hold_Source from hold and
5818: x_return_status => x_return_status,
5819: x_msg_count => x_msg_count,
5820: x_msg_data => x_msg_data
5821: );
5822: oe_debug_pub.add('After Validate_Order with x_return_status'|| x_return_status);
5823:
5824: -- Added for bug 7112725
5825: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5826: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5829: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5830: END IF;
5831: END IF;
5832:
5833: oe_debug_pub.add('l_order_rec.header_id'||l_order_rec.header_id);
5834: oe_debug_pub.add('lhldrlsrec.rel_reas_code' || l_hold_release_rec.release_reason_code);
5835: oe_holds_pvt.release_orders (
5836: p_hold_release_rec => l_hold_release_rec,
5837: p_order_rec => l_order_rec,
5830: END IF;
5831: END IF;
5832:
5833: oe_debug_pub.add('l_order_rec.header_id'||l_order_rec.header_id);
5834: oe_debug_pub.add('lhldrlsrec.rel_reas_code' || l_hold_release_rec.release_reason_code);
5835: oe_holds_pvt.release_orders (
5836: p_hold_release_rec => l_hold_release_rec,
5837: p_order_rec => l_order_rec,
5838: p_hold_source_rec => l_hold_source_rec,
5838: p_hold_source_rec => l_hold_source_rec,
5839: x_return_status => x_return_status,
5840: x_msg_count => x_msg_count,
5841: x_msg_data => x_msg_data );
5842: oe_debug_pub.add('After oe_holds_pvt.release_orders:' ||
5843: x_return_status);
5844: -- If the entity code is order, then release hold source also, if no
5845: -- other order hold records exist for this hold source. This would
5846: -- be the case if selected lines of an order were put on hold.
5856: AND ROWNUM = 1;
5857:
5858: EXCEPTION
5859: WHEN NO_DATA_FOUND THEN
5860: OE_Debug_PUB.Add('Setting hold source status to released');
5861: UPDATE OE_HOLD_SOURCES
5862: SET RELEASED_FLAG = 'Y'
5863: , HOLD_RELEASE_ID = l_hold_release_id
5864: , LAST_UPDATED_BY = l_user_id
5869: END IF;
5870: */
5871:
5872: end loop;
5873: oe_debug_pub.add('After release_orders with x_return_status'|| x_return_status,3);
5874: END Release_Holds;
5875:
5876: function check_system_holds(
5877: p_hold_id IN NUMBER,
5915:
5916: /* ============================= */
5917: /* IR ISO Change Management Ends */
5918:
5919: oe_debug_pub.add('renga: hold not authorized - ');
5920: l_authorized_or_not := 'N';
5921:
5922: END IF;
5923:
5964:
5965: BEGIN
5966: oe_msg_pub.initialize;
5967:
5968: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_apply_holds_lines',1);
5969:
5970: IF check_system_holds(
5971: p_hold_id => p_hold_id,
5972: x_return_status => x_return_status,
5972: x_return_status => x_return_status,
5973: x_msg_count => x_msg_count,
5974: x_msg_data => x_msg_data) = 'N' THEN
5975:
5976: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(p_hold_id));
5977: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
5978: OE_MSG_PUB.ADD;
5979: x_return_status := FND_API.G_RET_STS_ERROR;
5980: OE_MSG_PUB.Count_And_Get
5982: , p_data => x_msg_data
5983: );
5984: RETURN;
5985: END IF;
5986: OE_DEBUG_PUB.Add('After calling Check_System_Holds');
5987:
5988: IF check_authorization ( p_hold_id => p_hold_id
5989: ,p_authorized_action_code => 'APPLY'
5990: ,p_responsibility_id => l_resp_id
5992: ,x_return_status => x_return_status
5993: ,x_msg_count => x_msg_count
5994: ,x_msg_data => x_msg_data
5995: ) = 'N' THEN
5996: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(p_hold_id));
5997:
5998: fnd_message.set_name('ONT','ONT_APPLY');
5999: l_msg_token := fnd_message.get;
6000:
6010: );
6011: RETURN;
6012: END IF;
6013:
6014: OE_DEBUG_PUB.Add('After calling Check_Authorization');
6015:
6016: SAVEPOINT process_apply_holds_lines;
6017:
6018: l_prev_org_id := null;
6018: l_prev_org_id := null;
6019: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
6020: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99)
6021: THEN
6022: OE_DEBUG_PUB.Add('Mo_Global.Set_Policy_Context to:' || p_sel_rec_tbl(j).Org_Id );
6023: Mo_Global.Set_Policy_Context (p_access_mode => 'S',
6024: p_org_id => p_sel_rec_tbl(j).Org_Id);
6025: l_prev_org_id := p_sel_rec_tbl(j).org_id;
6026: END IF;
6030: ( p_line_id => l_line_id,
6031: x_line_rec => l_line_rec
6032: );
6033:
6034: OE_DEBUG_PUB.Add('Header_id: '||l_line_rec.header_id);
6035: OE_DEBUG_PUB.Add('Line_id: '||to_char(l_line_rec.line_id));
6036:
6037: l_hold_source_rec.hold_entity_code := 'O';
6038: l_hold_source_rec.hold_entity_id := l_line_rec.header_id;
6031: x_line_rec => l_line_rec
6032: );
6033:
6034: OE_DEBUG_PUB.Add('Header_id: '||l_line_rec.header_id);
6035: OE_DEBUG_PUB.Add('Line_id: '||to_char(l_line_rec.line_id));
6036:
6037: l_hold_source_rec.hold_entity_code := 'O';
6038: l_hold_source_rec.hold_entity_id := l_line_rec.header_id;
6039: l_hold_source_rec.LINE_ID := l_line_rec.line_id;
6047: from oe_hold_definitions
6048: where hold_id = l_hold_source_rec.hold_id;
6049: END IF;
6050:
6051: OE_DEBUG_PUB.Add('Before calling oe_holds_pvt.apply_holds');
6052: /*Calling overloaded procedure apply_holds, modified for WF_HOLDS ER (bug 6449458)*/
6053: /*oe_holds_pvt.apply_Holds(
6054: p_hold_source_rec => l_hold_source_rec
6055: ,p_hold_existing_flg => 'Y'
6070: ,x_is_hold_applied => l_is_hold_applied);
6071:
6072: IF NVL(l_is_hold_applied,FALSE) THEN
6073: l_count_of_holds_applied := l_count_of_holds_applied + 1;
6074: OE_DEBUG_PUB.Add('Hold is applied : TRUE',2);
6075: END IF;
6076: l_is_hold_applied := FALSE;
6077:
6078:
6075: END IF;
6076: l_is_hold_applied := FALSE;
6077:
6078:
6079: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6080: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6081: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6082:
6083: /*OE_DEBUG_PUB.Add('Before calling OE_Holds_PVT.Apply_Holds');
6076: l_is_hold_applied := FALSE;
6077:
6078:
6079: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6080: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6081: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6082:
6083: /*OE_DEBUG_PUB.Add('Before calling OE_Holds_PVT.Apply_Holds');
6084: oe_holds_pvt.apply_Holds(
6077:
6078:
6079: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6080: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6081: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6082:
6083: /*OE_DEBUG_PUB.Add('Before calling OE_Holds_PVT.Apply_Holds');
6084: oe_holds_pvt.apply_Holds(
6085: p_hold_source_rec => l_hold_source_rec
6079: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6080: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6081: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6082:
6083: /*OE_DEBUG_PUB.Add('Before calling OE_Holds_PVT.Apply_Holds');
6084: oe_holds_pvt.apply_Holds(
6085: p_hold_source_rec => l_hold_source_rec
6086: ,p_hold_existing_flg => 'Y'
6087: ,p_hold_future_flg => 'Y'
6089: ,x_msg_count => x_msg_count
6090: ,x_msg_data => x_msg_data);*/
6091:
6092: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6093: oe_debug_pub.add('process_apply_holds_lines unexpected failure',1);
6094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6095: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6096: oe_debug_pub.add('process_apply_holds_lines expected failure',1);
6097: RAISE FND_API.G_EXC_ERROR;
6092: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6093: oe_debug_pub.add('process_apply_holds_lines unexpected failure',1);
6094: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6095: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6096: oe_debug_pub.add('process_apply_holds_lines expected failure',1);
6097: RAISE FND_API.G_EXC_ERROR;
6098: END IF;
6099:
6100: END LOOP;
6112: THEN
6113: fnd_message.set_name('ONT', 'OE_NO_HOLD_FOR_ACTIVITY');
6114: fnd_message.set_token('WF_ACT',l_user_activity_name);
6115: OE_MSG_PUB.ADD;
6116: oe_debug_pub.add(' Hold Not applied for the requested line');
6117: RAISE FND_API.G_EXC_ERROR;
6118: ELSIF l_count_of_holds_applied = 0
6119: AND p_sel_rec_tbl.COUNT > 1 THEN
6120: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
6119: AND p_sel_rec_tbl.COUNT > 1 THEN
6120: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
6121: fnd_message.set_token('WF_ACT',l_user_activity_name);
6122: OE_MSG_PUB.ADD;
6123: oe_debug_pub.add('Hold Not applied for ALL requested lines');
6124: RAISE FND_API.G_EXC_ERROR;
6125: ELSIF p_sel_rec_tbl.COUNT > l_count_of_holds_applied
6126: AND p_sel_rec_tbl.COUNT > 1 THEN
6127: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
6126: AND p_sel_rec_tbl.COUNT > 1 THEN
6127: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
6128: fnd_message.set_token('WF_ACT',l_user_activity_name);
6129: OE_MSG_PUB.ADD;
6130: oe_debug_pub.add('Hold Not applied for FEW requested lines');
6131: ELSE
6132: NULL; -- No messages are required to be logged.
6133: END IF;
6134: END IF; -- End of WF_HOLDS ER (bug 6449458) IF Condition
6139: ( p_count => x_msg_count
6140: , p_data => x_msg_data
6141: );
6142:
6143: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_apply_holds_lines',1);
6144:
6145: EXCEPTION /* Procedure exception handler */
6146:
6147: WHEN FND_API.G_EXC_ERROR THEN
6213:
6214: BEGIN
6215: oe_msg_pub.initialize;
6216:
6217: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_apply_holds_orders',1);
6218:
6219: IF check_system_holds( p_hold_id => p_hold_id
6220: , x_return_status => x_return_status
6221: , x_msg_count => x_msg_count
6220: , x_return_status => x_return_status
6221: , x_msg_count => x_msg_count
6222: , x_msg_data => x_msg_data
6223: ) = 'N' THEN
6224: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(p_hold_id));
6225: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
6226: OE_MSG_PUB.ADD;
6227: x_return_status := FND_API.G_RET_STS_ERROR;
6228: OE_MSG_PUB.Count_And_Get
6231: );
6232: RETURN;
6233: END IF;
6234:
6235: OE_DEBUG_PUB.Add('After calling Check_System_Holds');
6236:
6237: IF check_authorization ( p_hold_id => p_hold_id
6238: , p_authorized_action_code => 'APPLY'
6239: , p_responsibility_id => l_resp_id
6241: , x_return_status => x_return_status
6242: , x_msg_count => x_msg_count
6243: , x_msg_data => x_msg_data
6244: ) = 'N' THEN
6245: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(p_hold_id));
6246:
6247: fnd_message.set_name('ONT','ONT_APPLY');
6248: l_msg_token := fnd_message.get;
6249:
6259: );
6260: RETURN;
6261: END IF;
6262:
6263: OE_DEBUG_PUB.Add('After calling Check_Authorization');
6264:
6265: SAVEPOINT process_apply_holds_orders;
6266: l_prev_org_id := null;
6267: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
6264:
6265: SAVEPOINT process_apply_holds_orders;
6266: l_prev_org_id := null;
6267: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
6268: OE_DEBUG_PUB.Add('p_sel_rec_tbl orgID:' || p_sel_rec_tbl(j).org_id ||
6269: ', l_prev_org_id:' || l_prev_org_id);
6270:
6271: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99)
6272: THEN
6269: ', l_prev_org_id:' || l_prev_org_id);
6270:
6271: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99)
6272: THEN
6273: OE_DEBUG_PUB.Add('Mo_Global.Set_Policy_Context to:' || p_sel_rec_tbl(j).Org_Id );
6274: Mo_Global.Set_Policy_Context (p_access_mode => 'S',
6275: p_org_id => p_sel_rec_tbl(j).Org_Id);
6276: OE_DEBUG_PUB.Add('After the ORG is :' || MO_GLOBAL.get_current_org_id);
6277: l_prev_org_id := p_sel_rec_tbl(j).org_id;
6272: THEN
6273: OE_DEBUG_PUB.Add('Mo_Global.Set_Policy_Context to:' || p_sel_rec_tbl(j).Org_Id );
6274: Mo_Global.Set_Policy_Context (p_access_mode => 'S',
6275: p_org_id => p_sel_rec_tbl(j).Org_Id);
6276: OE_DEBUG_PUB.Add('After the ORG is :' || MO_GLOBAL.get_current_org_id);
6277: l_prev_org_id := p_sel_rec_tbl(j).org_id;
6278: END IF;
6279:
6280: l_hold_source_rec.hold_entity_code := 'O';
6290: from oe_hold_definitions
6291: where hold_id = l_hold_source_rec.hold_id;
6292: END IF;
6293:
6294: OE_DEBUG_PUB.Add('Before calling oe_holds_pvt.apply_holds');
6295: /*oe_holds_pvt.apply_Holds(
6296: p_hold_source_rec => l_hold_source_rec
6297: ,p_hold_existing_flg => 'Y'
6298: ,p_hold_future_flg => 'Y'
6313: ,x_is_hold_applied => l_is_hold_applied);
6314:
6315: IF NVL(l_is_hold_applied,FALSE) THEN
6316: l_count_of_holds_applied := l_count_of_holds_applied + 1;
6317: OE_DEBUG_PUB.Add('Hold is applied : TRUE',2);
6318: END IF;
6319: l_is_hold_applied := FALSE;
6320:
6321: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6317: OE_DEBUG_PUB.Add('Hold is applied : TRUE',2);
6318: END IF;
6319: l_is_hold_applied := FALSE;
6320:
6321: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6322: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6323: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6324:
6325: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6318: END IF;
6319: l_is_hold_applied := FALSE;
6320:
6321: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6322: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6323: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6324:
6325: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6326: Oe_debug_pub.add('process_apply_holds_orders unexpected failure',3);
6319: l_is_hold_applied := FALSE;
6320:
6321: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6322: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6323: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6324:
6325: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6326: Oe_debug_pub.add('process_apply_holds_orders unexpected failure',3);
6327: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6322: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6323: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6324:
6325: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6326: Oe_debug_pub.add('process_apply_holds_orders unexpected failure',3);
6327: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6328: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6329: oe_debug_pub.add('process_apply_holds_orders expected failure',3);
6330: RAISE FND_API.G_EXC_ERROR;
6325: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6326: Oe_debug_pub.add('process_apply_holds_orders unexpected failure',3);
6327: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6328: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6329: oe_debug_pub.add('process_apply_holds_orders expected failure',3);
6330: RAISE FND_API.G_EXC_ERROR;
6331: END IF;
6332:
6333: END LOOP;
6345: THEN
6346: fnd_message.set_name('ONT', 'OE_NO_HOLD_FOR_ACTIVITY');
6347: fnd_message.set_token('WF_ACT',l_user_activity_name);
6348: OE_MSG_PUB.ADD;
6349: oe_debug_pub.add(' Hold Not applied for the requested line');
6350: RAISE FND_API.G_EXC_ERROR;
6351: ELSIF l_count_of_holds_applied = 0
6352: AND p_sel_rec_tbl.COUNT > 1 THEN
6353: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
6352: AND p_sel_rec_tbl.COUNT > 1 THEN
6353: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
6354: fnd_message.set_token('WF_ACT',l_user_activity_name);
6355: OE_MSG_PUB.ADD;
6356: oe_debug_pub.add('Hold Not applied for ALL requested lines');
6357: RAISE FND_API.G_EXC_ERROR;
6358: ELSIF p_sel_rec_tbl.COUNT > l_count_of_holds_applied
6359: AND p_sel_rec_tbl.COUNT > 1 THEN
6360: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
6359: AND p_sel_rec_tbl.COUNT > 1 THEN
6360: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
6361: fnd_message.set_token('WF_ACT',l_user_activity_name);
6362: OE_MSG_PUB.ADD;
6363: oe_debug_pub.add('Hold Not applied for FEW requested lines');
6364: ELSE
6365: NULL; -- No messages are required to be logged.
6366: END IF;
6367: END IF; -- End of WF_HOLDS ER (bug 6449458) IF Condition
6372: ( p_count => x_msg_count
6373: , p_data => x_msg_data
6374: );
6375:
6376: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_apply_holds_orders',1);
6377:
6378: EXCEPTION /* Procedure exception handler */
6379:
6380: WHEN FND_API.G_EXC_ERROR THEN
6450: l_msg_token VARCHAR2(100);
6451: BEGIN
6452: oe_msg_pub.initialize;
6453:
6454: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_release_holds_lines',1);
6455:
6456: IF check_system_holds( p_hold_id => p_hold_id
6457: ,x_return_status => x_return_status
6458: ,x_msg_count => x_msg_count
6457: ,x_return_status => x_return_status
6458: ,x_msg_count => x_msg_count
6459: ,x_msg_data => x_msg_data
6460: ) = 'N' THEN
6461: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(p_hold_id));
6462: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
6463: OE_MSG_PUB.ADD;
6464: x_return_status := FND_API.G_RET_STS_ERROR;
6465: OE_MSG_PUB.Count_And_Get
6468: );
6469: RETURN;
6470: END IF;
6471:
6472: OE_DEBUG_PUB.Add('After calling Check_System_Holds');
6473:
6474: IF check_authorization ( p_hold_id => p_hold_id
6475: ,p_authorized_action_code => 'REMOVE'
6476: ,p_responsibility_id => l_resp_id
6478: ,x_return_status => x_return_status
6479: ,x_msg_count => x_msg_count
6480: ,x_msg_data => x_msg_data
6481: ) = 'N' THEN
6482: OE_DEBUG_PUB.Add('Not authorize to Release this Hold:'||to_char(p_hold_id));
6483: fnd_message.set_name('ONT','ONT_RELEASE');
6484: l_msg_token := fnd_message.get;
6485:
6486: fnd_message.set_name('ONT', 'OE_HOLDS_AUTHORIZATION_FAILED');
6495: );
6496: RETURN;
6497: END IF;
6498:
6499: OE_DEBUG_PUB.Add('After calling Check_Authorization');
6500:
6501: SAVEPOINT process_release_holds_lines;
6502:
6503: l_prev_org_id := null;
6503: l_prev_org_id := null;
6504: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
6505: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99)
6506: THEN
6507: OE_DEBUG_PUB.Add('Mo_Global.Set_Policy_Context to:' || p_sel_rec_tbl(j).Org_Id );
6508: Mo_Global.Set_Policy_Context (p_access_mode => 'S',
6509: p_org_id => p_sel_rec_tbl(j).Org_Id);
6510: l_prev_org_id := p_sel_rec_tbl(j).org_id;
6511: END IF;
6515: OE_LINE_UTIL.Query_Row
6516: ( p_line_id => l_line_id,
6517: x_line_rec => l_line_rec
6518: );
6519: OE_DEBUG_PUB.Add('header_id: '||l_line_rec.header_id);
6520: OE_DEBUG_PUB.Add('Line_id: '||to_char(l_line_rec.line_id));
6521: l_order_tbl(1).header_id := l_line_rec.header_id;
6522: l_order_tbl(1).line_id := l_line_rec.line_id;
6523:
6516: ( p_line_id => l_line_id,
6517: x_line_rec => l_line_rec
6518: );
6519: OE_DEBUG_PUB.Add('header_id: '||l_line_rec.header_id);
6520: OE_DEBUG_PUB.Add('Line_id: '||to_char(l_line_rec.line_id));
6521: l_order_tbl(1).header_id := l_line_rec.header_id;
6522: l_order_tbl(1).line_id := l_line_rec.line_id;
6523:
6524:
6521: l_order_tbl(1).header_id := l_line_rec.header_id;
6522: l_order_tbl(1).line_id := l_line_rec.line_id;
6523:
6524:
6525: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds:HeaderID'
6526: || l_order_tbl(1).header_id || ', LineID:' || l_order_tbl(1).line_id);
6527: oe_holds_pvt.release_holds(
6528: p_order_tbl => l_order_tbl,
6529: p_hold_id => p_hold_id,
6533: x_msg_count => x_msg_count,
6534: x_msg_data => x_msg_data
6535: );
6536:
6537: OE_DEBUG_PUB.Add('After oe_holds_pvt.release_holds:' || x_return_status,3);
6538:
6539:
6540: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6541: oe_debug_pub.add('process_release_holds_lines unexpected failure',3);
6537: OE_DEBUG_PUB.Add('After oe_holds_pvt.release_holds:' || x_return_status,3);
6538:
6539:
6540: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6541: oe_debug_pub.add('process_release_holds_lines unexpected failure',3);
6542: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6543: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6544: oe_debug_pub.add('process_release_holds_lines failure',3);
6545: RAISE FND_API.G_EXC_ERROR;
6540: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6541: oe_debug_pub.add('process_release_holds_lines unexpected failure',3);
6542: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6543: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6544: oe_debug_pub.add('process_release_holds_lines failure',3);
6545: RAISE FND_API.G_EXC_ERROR;
6546: END IF;
6547:
6548: END LOOP;
6553: ( p_count => x_msg_count
6554: , p_data => x_msg_data
6555: );
6556:
6557: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_release_holds_lines',1);
6558:
6559: EXCEPTION /* Procedure exception handler */
6560:
6561: WHEN FND_API.G_EXC_ERROR THEN
6625: l_msg_token VARCHAR2(100);
6626: BEGIN
6627: oe_msg_pub.initialize;
6628:
6629: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_release_holds_orders',1);
6630:
6631: IF check_system_holds( p_hold_id => p_hold_id
6632: ,x_return_status => x_return_status
6633: ,x_msg_count => x_msg_count
6632: ,x_return_status => x_return_status
6633: ,x_msg_count => x_msg_count
6634: ,x_msg_data => x_msg_data
6635: ) = 'N' THEN
6636: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(p_hold_id));
6637: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
6638: OE_MSG_PUB.ADD;
6639: x_return_status := FND_API.G_RET_STS_ERROR;
6640: OE_MSG_PUB.Count_And_Get
6643: );
6644: RETURN;
6645: END IF;
6646:
6647: OE_DEBUG_PUB.Add('After calling Check_System_Holds');
6648:
6649: IF check_authorization ( p_hold_id => p_hold_id
6650: ,p_authorized_action_code => 'REMOVE'
6651: ,p_responsibility_id => l_resp_id
6653: ,x_return_status => x_return_status
6654: ,x_msg_count => x_msg_count
6655: ,x_msg_data => x_msg_data
6656: ) = 'N' THEN
6657: OE_DEBUG_PUB.Add('Not authorize to Release this Hold:'|| to_char(p_hold_id),1);
6658: fnd_message.set_name('ONT','ONT_RELEASE');
6659: l_msg_token := fnd_message.get;
6660:
6661: fnd_message.set_name('ONT', 'OE_HOLDS_AUTHORIZATION_FAILED');
6672:
6673: RETURN;
6674: END IF;
6675:
6676: OE_DEBUG_PUB.Add('After calling Check_Authorization');
6677:
6678: SAVEPOINT process_release_holds_orders;
6679:
6680: OE_DEBUG_PUB.Add('Release_Reason_Code: '||p_release_reason_code);
6676: OE_DEBUG_PUB.Add('After calling Check_Authorization');
6677:
6678: SAVEPOINT process_release_holds_orders;
6679:
6680: OE_DEBUG_PUB.Add('Release_Reason_Code: '||p_release_reason_code);
6681:
6682: l_prev_org_id := null;
6683: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
6684: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99)
6682: l_prev_org_id := null;
6683: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
6684: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99)
6685: THEN
6686: OE_DEBUG_PUB.Add('Mo_Global.Set_Policy_Context to:' || p_sel_rec_tbl(j).Org_Id );
6687: Mo_Global.Set_Policy_Context (p_access_mode => 'S',
6688: p_org_id => p_sel_rec_tbl(j).Org_Id);
6689: l_prev_org_id := p_sel_rec_tbl(j).org_id;
6690: END IF;
6689: l_prev_org_id := p_sel_rec_tbl(j).org_id;
6690: END IF;
6691: l_order_tbl(1).header_id := p_sel_rec_tbl(j).Id1;
6692:
6693: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds 4 HeaderID' || l_order_tbl(1).header_id);
6694: oe_holds_pvt.release_holds(
6695: p_order_tbl => l_order_tbl,
6696: p_hold_id => p_hold_id,
6697: p_release_reason_code => p_release_reason_code,
6700: x_msg_count => x_msg_count,
6701: x_msg_data => x_msg_data
6702: );
6703:
6704: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6705: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6706: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6707:
6708: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6701: x_msg_data => x_msg_data
6702: );
6703:
6704: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6705: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6706: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6707:
6708: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6709: oe_debug_pub.add('process_release_holds_orders unexpected failure',3);
6702: );
6703:
6704: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6705: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6706: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6707:
6708: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6709: oe_debug_pub.add('process_release_holds_orders unexpected failure',3);
6710: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6705: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
6706: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
6707:
6708: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6709: oe_debug_pub.add('process_release_holds_orders unexpected failure',3);
6710: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6711: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6712: oe_debug_pub.add('process_release_holds_orders failure',3);
6713: RAISE FND_API.G_EXC_ERROR;
6708: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6709: oe_debug_pub.add('process_release_holds_orders unexpected failure',3);
6710: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6711: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6712: oe_debug_pub.add('process_release_holds_orders failure',3);
6713: RAISE FND_API.G_EXC_ERROR;
6714: END IF;
6715:
6716:
6722: ( p_count => x_msg_count
6723: , p_data => x_msg_data
6724: );
6725:
6726: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_release_holds_orders',1);
6727:
6728: EXCEPTION /* Procedure exception handler */
6729:
6730: WHEN FND_API.G_EXC_ERROR THEN
6792: l_count_of_holds_applied NUMBER := 0;
6793:
6794: --
6795: BEGIN
6796: oe_debug_pub.add('In process_create_holds', 1);
6797: oe_msg_pub.initialize; --bug 5548778
6798: l_hold_source_rec := p_hold_source_rec;
6799:
6800: if check_system_holds( p_hold_id => l_hold_source_rec.hold_id
6801: ,x_return_status => x_return_status
6802: ,x_msg_count => x_msg_count
6803: ,x_msg_data => x_msg_data
6804: ) = 'N' THEN
6805: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(l_hold_source_rec.hold_id));
6806: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
6807: OE_MSG_PUB.ADD;
6808: x_return_status := FND_API.G_RET_STS_ERROR;
6809: OE_MSG_PUB.Count_And_Get
6820: ,x_return_status => x_return_status
6821: ,x_msg_count => x_msg_count
6822: ,x_msg_data => x_msg_data
6823: ) = 'N' THEN
6824: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'||
6825: to_char(l_hold_source_rec.hold_id));
6826:
6827: fnd_message.set_name('ONT','ONT_APPLY');
6828: l_msg_token := fnd_message.get;
6840: return;
6841: END IF;
6842:
6843:
6844: oe_debug_pub.add('p_hold_existing_flg->' || p_hold_existing_flg,3);
6845: oe_debug_pub.add('p_hold_future_flg->' || p_hold_future_flg,3);
6846: /*added the select statement for WF_HOLDS ER (bug 6449458)*/
6847: select item_type, activity_name
6848: into l_item_type, l_activity_name
6841: END IF;
6842:
6843:
6844: oe_debug_pub.add('p_hold_existing_flg->' || p_hold_existing_flg,3);
6845: oe_debug_pub.add('p_hold_future_flg->' || p_hold_future_flg,3);
6846: /*added the select statement for WF_HOLDS ER (bug 6449458)*/
6847: select item_type, activity_name
6848: into l_item_type, l_activity_name
6849: from oe_hold_definitions
6849: from oe_hold_definitions
6850: where hold_id = l_hold_source_rec.hold_id;
6851:
6852: if (p_hold_existing_flg = 'Y' AND p_hold_future_flg = 'Y') then
6853: oe_debug_pub.add('Calling oe_holds_pvt.apply_Holds',3);
6854: /*oe_holds_pvt.apply_Holds(
6855: p_hold_source_rec => l_hold_source_rec
6856: ,x_return_status => x_return_status
6857: ,x_msg_count => x_msg_count
6870: ,x_msg_data => x_msg_data
6871: ,x_is_hold_applied => l_is_hold_applied
6872: );
6873:
6874: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6875: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6876: oe_debug_pub.add('process_create_holds unexpected failure',1);
6877: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6878: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6872: );
6873:
6874: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
6875: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6876: oe_debug_pub.add('process_create_holds unexpected failure',1);
6877: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6878: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6879: oe_debug_pub.add('process_create_holds expected failure',1);
6880: RAISE FND_API.G_EXC_ERROR;
6875: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
6876: oe_debug_pub.add('process_create_holds unexpected failure',1);
6877: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6878: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6879: oe_debug_pub.add('process_create_holds expected failure',1);
6880: RAISE FND_API.G_EXC_ERROR;
6881: END IF;
6882: x_return_status := FND_API.G_RET_STS_SUCCESS;
6883: elsif (p_hold_existing_flg = 'Y' AND p_hold_future_flg = 'N') then
6883: elsif (p_hold_existing_flg = 'Y' AND p_hold_future_flg = 'N') then
6884: -- Create_Hold_Source (Create the Hold Source)
6885: -- Create_Order_Holds (Put the existing Orders on Hold)
6886: -- Create_Release_Source (Release the source.Do not release orders from hold)
6887: oe_debug_pub.add('Calling Create_Hold_Source',3);
6888: Create_Hold_Source (
6889: p_hold_source_rec => l_hold_source_rec,
6890: p_org_id =>p_org_id, --ER#7479609
6891: x_hold_source_id => l_hold_source_id,
6900: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
6901: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6902: END IF;
6903: END IF;
6904: OE_DEBUG_PUB.Add('l_hold_source_id->' || to_char(l_hold_source_id) ,3);
6905: l_hold_source_rec.hold_source_id := l_hold_source_id;
6906: OE_DEBUG_PUB.Add ('Calling Create_Order_Holds',3);
6907: Create_Order_Holds (
6908: p_hold_source_rec => l_hold_source_rec
6902: END IF;
6903: END IF;
6904: OE_DEBUG_PUB.Add('l_hold_source_id->' || to_char(l_hold_source_id) ,3);
6905: l_hold_source_rec.hold_source_id := l_hold_source_id;
6906: OE_DEBUG_PUB.Add ('Calling Create_Order_Holds',3);
6907: Create_Order_Holds (
6908: p_hold_source_rec => l_hold_source_rec
6909: ,p_org_id =>p_org_id --ER#7479609
6910: ,x_return_status => x_return_status
6910: ,x_return_status => x_return_status
6911: ,x_msg_count => x_msg_count
6912: ,x_msg_data => x_msg_data
6913: );
6914: OE_DEBUG_PUB.Add('After Create_Order_Holds',3);
6915: -- XX Should be a new reason code
6916: l_hold_release_rec.hold_source_id := l_hold_source_id;
6917: l_hold_release_rec.release_reason_code := 'EXPIRE';
6918: l_hold_release_rec.release_comment := 'Released automatically by System';
6915: -- XX Should be a new reason code
6916: l_hold_release_rec.hold_source_id := l_hold_source_id;
6917: l_hold_release_rec.release_reason_code := 'EXPIRE';
6918: l_hold_release_rec.release_comment := 'Released automatically by System';
6919: oe_debug_pub.add('Calling Create_Release_Source',3);
6920: OE_HOLDS_PVT.Create_Release_Source(p_hold_release_rec => l_hold_release_rec,
6921: x_hold_release_id => l_hold_release_id,
6922: x_return_status => x_return_status,
6923: x_msg_count => x_msg_count,
6921: x_hold_release_id => l_hold_release_id,
6922: x_return_status => x_return_status,
6923: x_msg_count => x_msg_count,
6924: x_msg_data => x_msg_data);
6925: OE_DEBUG_PUB.Add('Create_Order_Holds Status' || x_return_status,3);
6926:
6927: elsif (p_hold_existing_flg = 'N' AND p_hold_future_flg = 'Y') then
6928: -- call create_hold_source for only future holds to go on hold.
6929: OE_DEBUG_PUB.Add('Calling Create_Hold_Source',3);
6925: OE_DEBUG_PUB.Add('Create_Order_Holds Status' || x_return_status,3);
6926:
6927: elsif (p_hold_existing_flg = 'N' AND p_hold_future_flg = 'Y') then
6928: -- call create_hold_source for only future holds to go on hold.
6929: OE_DEBUG_PUB.Add('Calling Create_Hold_Source',3);
6930: Create_Hold_Source (
6931: p_hold_source_rec => l_hold_source_rec,
6932: p_org_id =>p_org_id, --ER#7479609
6933: x_hold_source_id => l_hold_source_id,
6943: END IF;
6944: END IF;
6945: else
6946: -- Should not get here
6947: oe_debug_pub.add ('Both of Hold Future and Existing Flag are unset',3);
6948: oe_debug_pub.add ('Do nothing',3);
6949: end if; -- (p_hold_existing_flg = 'Y' AND p_hold_future_flg 'Y')
6950: -- Get message count and data
6951:
6944: END IF;
6945: else
6946: -- Should not get here
6947: oe_debug_pub.add ('Both of Hold Future and Existing Flag are unset',3);
6948: oe_debug_pub.add ('Do nothing',3);
6949: end if; -- (p_hold_existing_flg = 'Y' AND p_hold_future_flg 'Y')
6950: -- Get message count and data
6951:
6952: OE_MSG_PUB.Count_And_Get
7027:
7028: /* Bug 1946783 - Added Check for credit hold created from AR */
7029:
7030: If l_hold_id = 1 AND (l_hold_entity_code ='C' OR l_hold_entity_code = 'B') then
7031: OE_DEBUG_PUB.Add('Bug 1946783 Credit Hold should be released from AR',1);
7032:
7033: fnd_message.set_name('ONT', 'OE_CC_AR_HOLD_NOT_RELEASED');
7034: OE_MSG_PUB.ADD;
7035: RAISE FND_API.G_EXC_ERROR;
7048: ,x_return_status => x_return_status
7049: ,x_msg_count => x_msg_count
7050: ,x_msg_data => x_msg_data
7051: ) = 'N' THEN
7052: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(l_hold_id));
7053: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
7054: fnd_message.set_token('ACTION', 'Release');
7055: OE_MSG_PUB.ADD;
7056: x_return_status := FND_API.G_RET_STS_ERROR;
7068: ,x_return_status => x_return_status
7069: ,x_msg_count => x_msg_count
7070: ,x_msg_data => x_msg_data
7071: ) = 'N' THEN
7072: OE_DEBUG_PUB.Add('Not authorize to Release this Hold:'|| to_char(l_hold_id));
7073:
7074: fnd_message.set_name('ONT','ONT_RELEASE');
7075: l_msg_token := fnd_message.get;
7076:
7098: ,x_return_status => x_return_status
7099: ,x_msg_count => x_msg_count
7100: ,x_msg_data => x_msg_data
7101: );
7102: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
7103:
7104: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7105: oe_debug_pub.add('process_release_source unexpected failure',3);
7106: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7101: );
7102: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
7103:
7104: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7105: oe_debug_pub.add('process_release_source unexpected failure',3);
7106: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7107: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7108: oe_debug_pub.add('process_release_source expected failure',3);
7109: RAISE FND_API.G_EXC_ERROR;
7104: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7105: oe_debug_pub.add('process_release_source unexpected failure',3);
7106: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7107: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7108: oe_debug_pub.add('process_release_source expected failure',3);
7109: RAISE FND_API.G_EXC_ERROR;
7110: END IF;
7111:
7112: x_return_status := FND_API.G_RET_STS_SUCCESS;
7207: l_msg_token VARCHAR2(100);
7208: BEGIN
7209: oe_msg_pub.initialize;
7210:
7211: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_release_holds_lines',1);
7212:
7213: IF check_system_holds( p_hold_id => p_hold_id
7214: ,x_return_status => x_return_status
7215: ,x_msg_count => x_msg_count
7214: ,x_return_status => x_return_status
7215: ,x_msg_count => x_msg_count
7216: ,x_msg_data => x_msg_data
7217: ) = 'N' THEN
7218: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(p_hold_id));
7219: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
7220: OE_MSG_PUB.ADD;
7221: x_return_status := FND_API.G_RET_STS_ERROR;
7222: OE_MSG_PUB.Count_And_Get
7225: );
7226: RETURN;
7227: END IF;
7228:
7229: OE_DEBUG_PUB.Add('After calling Check_System_Holds');
7230:
7231: IF check_authorization ( p_hold_id => p_hold_id
7232: ,p_authorized_action_code => 'REMOVE'
7233: ,p_responsibility_id => l_resp_id
7235: ,x_return_status => x_return_status
7236: ,x_msg_count => x_msg_count
7237: ,x_msg_data => x_msg_data
7238: ) = 'N' THEN
7239: OE_DEBUG_PUB.Add('Not authorize to Release this Hold:'||to_char(p_hold_id));
7240: fnd_message.set_name('ONT','ONT_RELEASE');
7241: l_msg_token := fnd_message.get;
7242:
7243: fnd_message.set_name('ONT', 'OE_HOLDS_AUTHORIZATION_FAILED');
7252: );
7253: RETURN;
7254: END IF;
7255:
7256: OE_DEBUG_PUB.Add('After calling Check_Authorization');
7257:
7258: SAVEPOINT process_release_holds_lines;
7259:
7260: l_prev_org_id := null;
7260: l_prev_org_id := null;
7261: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
7262: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99)
7263: THEN
7264: OE_DEBUG_PUB.Add('Mo_Global.Set_Policy_Context to:' || p_sel_rec_tbl(j).Org_Id );
7265: Mo_Global.Set_Policy_Context (p_access_mode => 'S',
7266: p_org_id => p_sel_rec_tbl(j).Org_Id);
7267: l_prev_org_id := p_sel_rec_tbl(j).org_id;
7268: END IF;
7271: OE_LINE_UTIL.Query_Row
7272: ( p_line_id => l_line_id,
7273: x_line_rec => l_line_rec
7274: );
7275: OE_DEBUG_PUB.Add('header_id: '||l_line_rec.header_id);
7276: OE_DEBUG_PUB.Add('Line_id: '||to_char(l_line_rec.line_id));
7277: l_order_tbl(1).header_id := l_line_rec.header_id;
7278: l_order_tbl(1).line_id := l_line_rec.line_id;
7279:
7272: ( p_line_id => l_line_id,
7273: x_line_rec => l_line_rec
7274: );
7275: OE_DEBUG_PUB.Add('header_id: '||l_line_rec.header_id);
7276: OE_DEBUG_PUB.Add('Line_id: '||to_char(l_line_rec.line_id));
7277: l_order_tbl(1).header_id := l_line_rec.header_id;
7278: l_order_tbl(1).line_id := l_line_rec.line_id;
7279:
7280:
7277: l_order_tbl(1).header_id := l_line_rec.header_id;
7278: l_order_tbl(1).line_id := l_line_rec.line_id;
7279:
7280:
7281: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds:HeaderID'
7282: || l_order_tbl(1).header_id || ', LineID:' || l_order_tbl(1).line_id);
7283: oe_holds_pvt.release_holds(
7284: p_order_tbl => l_order_tbl,
7285: p_hold_id => p_hold_id,
7289: x_msg_count => x_msg_count,
7290: x_msg_data => x_msg_data
7291: );
7292:
7293: OE_DEBUG_PUB.Add('After oe_holds_pvt.release_holds:' || x_return_status,3);
7294:
7295: -- Changes begin : ER 1373910
7296: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7297:
7295: -- Changes begin : ER 1373910
7296: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7297:
7298: IF p_wf_release_action = 'Y' THEN
7299: OE_DEBUG_PUB.Add('Before calling Oe_holds_pvt.progress_order');
7300: progress_order( p_hold_id,
7301: p_num_of_records,
7302: l_order_tbl,
7303: x_return_status,
7302: l_order_tbl,
7303: x_return_status,
7304: x_msg_count,
7305: x_msg_data );
7306: OE_DEBUG_PUB.Add('After Oe_holds_pvt.progress_order : ' || x_return_status,3);
7307: END IF;
7308: END IF;
7309: -- Changes End : ER 1373910
7310:
7308: END IF;
7309: -- Changes End : ER 1373910
7310:
7311: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7312: oe_debug_pub.add('process_release_holds_lines unexpected failure',3);
7313: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7314: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7315: oe_debug_pub.add('process_release_holds_lines failure',3);
7316: RAISE FND_API.G_EXC_ERROR;
7311: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7312: oe_debug_pub.add('process_release_holds_lines unexpected failure',3);
7313: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7314: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7315: oe_debug_pub.add('process_release_holds_lines failure',3);
7316: RAISE FND_API.G_EXC_ERROR;
7317: END IF;
7318:
7319: END LOOP;
7324: ( p_count => x_msg_count
7325: , p_data => x_msg_data
7326: );
7327:
7328: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_release_holds_lines',1);
7329:
7330: EXCEPTION /* Procedure exception handler */
7331:
7332: WHEN FND_API.G_EXC_ERROR THEN
7415: l_msg_token VARCHAR2(100);
7416: BEGIN
7417: oe_msg_pub.initialize;
7418:
7419: OE_DEBUG_PUB.Add('Entering OE_Holds_PVT.process_release_holds_orders',1);
7420:
7421: IF check_system_holds( p_hold_id => p_hold_id
7422: ,x_return_status => x_return_status
7423: ,x_msg_count => x_msg_count
7422: ,x_return_status => x_return_status
7423: ,x_msg_count => x_msg_count
7424: ,x_msg_data => x_msg_data
7425: ) = 'N' THEN
7426: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(p_hold_id));
7427: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
7428: OE_MSG_PUB.ADD;
7429: x_return_status := FND_API.G_RET_STS_ERROR;
7430: OE_MSG_PUB.Count_And_Get
7433: );
7434: RETURN;
7435: END IF;
7436:
7437: OE_DEBUG_PUB.Add('After calling Check_System_Holds');
7438:
7439: IF check_authorization ( p_hold_id => p_hold_id
7440: ,p_authorized_action_code => 'REMOVE'
7441: ,p_responsibility_id => l_resp_id
7443: ,x_return_status => x_return_status
7444: ,x_msg_count => x_msg_count
7445: ,x_msg_data => x_msg_data
7446: ) = 'N' THEN
7447: OE_DEBUG_PUB.Add('Not authorize to Release this Hold:'|| to_char(p_hold_id),1);
7448: fnd_message.set_name('ONT','ONT_RELEASE');
7449: l_msg_token := fnd_message.get;
7450:
7451: fnd_message.set_name('ONT', 'OE_HOLDS_AUTHORIZATION_FAILED');
7462:
7463: RETURN;
7464: END IF;
7465:
7466: OE_DEBUG_PUB.Add('After calling Check_Authorization');
7467:
7468: SAVEPOINT process_release_holds_orders;
7469:
7470: OE_DEBUG_PUB.Add('Release_Reason_Code: '||p_release_reason_code);
7466: OE_DEBUG_PUB.Add('After calling Check_Authorization');
7467:
7468: SAVEPOINT process_release_holds_orders;
7469:
7470: OE_DEBUG_PUB.Add('Release_Reason_Code: '||p_release_reason_code);
7471:
7472: l_prev_org_id := null;
7473: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
7474: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99) THEN
7471:
7472: l_prev_org_id := null;
7473: FOR j in 1.. p_sel_rec_tbl.COUNT LOOP
7474: IF p_sel_rec_tbl(j).org_id <> nvl(l_prev_org_id, -99) THEN
7475: OE_DEBUG_PUB.Add('Mo_Global.Set_Policy_Context to:' || p_sel_rec_tbl(j).Org_Id );
7476: Mo_Global.Set_Policy_Context (p_access_mode => 'S',
7477: p_org_id => p_sel_rec_tbl(j).Org_Id);
7478: l_prev_org_id := p_sel_rec_tbl(j).org_id;
7479: END IF;
7478: l_prev_org_id := p_sel_rec_tbl(j).org_id;
7479: END IF;
7480: l_order_tbl(1).header_id := p_sel_rec_tbl(j).Id1;
7481:
7482: OE_DEBUG_PUB.Add('B4 Calling oe_holds_pvt.release_holds 4 HeaderID' || l_order_tbl(1).header_id);
7483: oe_holds_pvt.release_holds(
7484: p_order_tbl => l_order_tbl,
7485: p_hold_id => p_hold_id,
7486: p_release_reason_code => p_release_reason_code,
7489: x_msg_count => x_msg_count,
7490: x_msg_data => x_msg_data
7491: );
7492:
7493: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
7494: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
7495: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
7496:
7497: -- Changes begin : ER 1373910
7490: x_msg_data => x_msg_data
7491: );
7492:
7493: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
7494: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
7495: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
7496:
7497: -- Changes begin : ER 1373910
7498: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7491: );
7492:
7493: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,2);
7494: OE_DEBUG_PUB.Add('x_msg_count:' || to_char(x_msg_count),2);
7495: OE_DEBUG_PUB.Add('x_msg_data:' || x_msg_data,2);
7496:
7497: -- Changes begin : ER 1373910
7498: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7499: IF p_wf_release_action = 'Y' THEN
7496:
7497: -- Changes begin : ER 1373910
7498: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7499: IF p_wf_release_action = 'Y' THEN
7500: OE_DEBUG_PUB.Add('Before calling Oe_holds_pvt.progress_order');
7501: progress_order( p_hold_id,
7502: p_num_of_records,
7503: l_order_tbl,
7504: x_return_status,
7503: l_order_tbl,
7504: x_return_status,
7505: x_msg_count,
7506: x_msg_data);
7507: OE_DEBUG_PUB.Add('After Oe_holds_pvt.progress_order : ' || x_return_status,3);
7508: END IF;
7509: END IF;
7510: -- Changes end : ER 1373910
7511:
7509: END IF;
7510: -- Changes end : ER 1373910
7511:
7512: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7513: oe_debug_pub.add('process_release_holds_orders unexpected failure',3);
7514: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7515: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7516: oe_debug_pub.add('process_release_holds_orders failure',3);
7517: RAISE FND_API.G_EXC_ERROR;
7512: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7513: oe_debug_pub.add('process_release_holds_orders unexpected failure',3);
7514: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7515: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7516: oe_debug_pub.add('process_release_holds_orders failure',3);
7517: RAISE FND_API.G_EXC_ERROR;
7518: END IF;
7519:
7520:
7526: ( p_count => x_msg_count
7527: , p_data => x_msg_data
7528: );
7529:
7530: OE_DEBUG_PUB.Add('Exiting OE_Holds_PVT.process_release_holds_orders',1);
7531:
7532: EXCEPTION /* Procedure exception handler */
7533:
7534: WHEN FND_API.G_EXC_ERROR THEN
7617:
7618: /* Bug 1946783 - Added Check for credit hold created from AR */
7619:
7620: If l_hold_id = 1 AND (l_hold_entity_code ='C' OR l_hold_entity_code = 'B') then
7621: OE_DEBUG_PUB.Add('Bug 1946783 Credit Hold should be released from AR',1);
7622:
7623: fnd_message.set_name('ONT', 'OE_CC_AR_HOLD_NOT_RELEASED');
7624: OE_MSG_PUB.ADD;
7625: RAISE FND_API.G_EXC_ERROR;
7638: ,x_return_status => x_return_status
7639: ,x_msg_count => x_msg_count
7640: ,x_msg_data => x_msg_data
7641: ) = 'N' THEN
7642: OE_DEBUG_PUB.Add('Not authorize to Apply this Hold:'|| to_char(l_hold_id));
7643: fnd_message.set_name('ONT', 'ONT_HOLDS_SYSTEM_CHECK');
7644: fnd_message.set_token('ACTION', 'Release');
7645: OE_MSG_PUB.ADD;
7646: x_return_status := FND_API.G_RET_STS_ERROR;
7659: ,x_return_status => x_return_status
7660: ,x_msg_count => x_msg_count
7661: ,x_msg_data => x_msg_data
7662: ) = 'N' THEN
7663: OE_DEBUG_PUB.Add('Not authorize to Release this Hold:'|| to_char(l_hold_id));
7664:
7665: fnd_message.set_name('ONT','ONT_RELEASE');
7666: l_msg_token := fnd_message.get;
7667:
7691: ,x_msg_data => x_msg_data
7692: );
7693:
7694:
7695: OE_DEBUG_PUB.Add('x_return_status:' || x_return_status,1);
7696:
7697: -- Changes begin : ER 1373910
7698: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7699: IF p_wf_release_action = 'Y' THEN
7696:
7697: -- Changes begin : ER 1373910
7698: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7699: IF p_wf_release_action = 'Y' THEN
7700: Oe_debug_pub.Add('Before calling Oe_holds_pvt.progress_order');
7701: progress_order( p_hold_source_id,
7702: x_return_status,
7703: x_msg_count,
7704: x_msg_data);
7701: progress_order( p_hold_source_id,
7702: x_return_status,
7703: x_msg_count,
7704: x_msg_data);
7705: Oe_debug_pub.ADD('Oe_holds_pvt.progress order returned with : ' || x_return_status);
7706: END IF;
7707: END IF;
7708: -- Changes end : ER 1373910
7709:
7707: END IF;
7708: -- Changes end : ER 1373910
7709:
7710: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7711: oe_debug_pub.add('process_release_source unexpected failure',3);
7712: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7713: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7714: oe_debug_pub.add('process_release_source expected failure',3);
7715: RAISE FND_API.G_EXC_ERROR;
7710: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7711: oe_debug_pub.add('process_release_source unexpected failure',3);
7712: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7713: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7714: oe_debug_pub.add('process_release_source expected failure',3);
7715: RAISE FND_API.G_EXC_ERROR;
7716: END IF;
7717:
7718: x_return_status := FND_API.G_RET_STS_SUCCESS;
7946: l_is_hold_applied BOOLEAN;
7947:
7948: BEGIN
7949:
7950: OE_DEBUG_PUB.Add('In OE_Holds_pvt.Apply Holds, Creating Hold Source',1);
7951:
7952: x_return_status := FND_API.G_RET_STS_SUCCESS;
7953: l_hold_source_rec := p_hold_source_rec;
7954:
7955: Validate (p_hold_source_rec => p_hold_source_rec,
7956: x_return_status => x_return_status,
7957: x_msg_count => x_msg_count,
7958: x_msg_data => x_msg_data );
7959: OE_DEBUG_PUB.Add('Validate return status:' || x_return_status,1);
7960:
7961: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7962: OE_Debug_PUB.Add('Validate not successful',1);
7963: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
7958: x_msg_data => x_msg_data );
7959: OE_DEBUG_PUB.Add('Validate return status:' || x_return_status,1);
7960:
7961: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7962: OE_Debug_PUB.Add('Validate not successful',1);
7963: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
7964: RAISE FND_API.G_EXC_ERROR;
7965: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7966: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7965: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7966: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7967: END IF;
7968: ELSE
7969: OE_DEBUG_PUB.Add ('Calling Create_Hold_Source bug 5548778 overload',1);
7970: Create_Hold_Source (
7971: p_hold_source_rec => p_hold_source_rec,
7972: p_org_id => p_org_id, --ER#7479609
7973: x_hold_source_id => l_hold_source_id,
7975: x_return_status => x_return_status,
7976: x_msg_count => x_msg_count,
7977: x_msg_data => x_msg_data);
7978:
7979: OE_DEBUG_PUB.Add('x_return_status->' || x_return_status,1);
7980: OE_DEBUG_PUB.Add('x_msg_count->' || x_msg_count,1);
7981: OE_DEBUG_PUB.Add('x_msg_data' || x_msg_data,1);
7982: OE_DEBUG_PUB.Add('l_hold_exists' || l_hold_exists,1);
7983: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7976: x_msg_count => x_msg_count,
7977: x_msg_data => x_msg_data);
7978:
7979: OE_DEBUG_PUB.Add('x_return_status->' || x_return_status,1);
7980: OE_DEBUG_PUB.Add('x_msg_count->' || x_msg_count,1);
7981: OE_DEBUG_PUB.Add('x_msg_data' || x_msg_data,1);
7982: OE_DEBUG_PUB.Add('l_hold_exists' || l_hold_exists,1);
7983: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7984: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
7977: x_msg_data => x_msg_data);
7978:
7979: OE_DEBUG_PUB.Add('x_return_status->' || x_return_status,1);
7980: OE_DEBUG_PUB.Add('x_msg_count->' || x_msg_count,1);
7981: OE_DEBUG_PUB.Add('x_msg_data' || x_msg_data,1);
7982: OE_DEBUG_PUB.Add('l_hold_exists' || l_hold_exists,1);
7983: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7984: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
7985: RAISE FND_API.G_EXC_ERROR;
7978:
7979: OE_DEBUG_PUB.Add('x_return_status->' || x_return_status,1);
7980: OE_DEBUG_PUB.Add('x_msg_count->' || x_msg_count,1);
7981: OE_DEBUG_PUB.Add('x_msg_data' || x_msg_data,1);
7982: OE_DEBUG_PUB.Add('l_hold_exists' || l_hold_exists,1);
7983: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7984: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
7985: RAISE FND_API.G_EXC_ERROR;
7986: ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
7987: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7988: END IF;
7989: END IF;
7990:
7991: OE_DEBUG_PUB.Add('l_hold_source_id->' ||
7992: to_char(l_hold_source_id) ,1);
7993: l_hold_source_rec.hold_source_id := l_hold_source_id;
7994: --bug 5548778
7995:
7995:
7996: IF l_hold_exists = 'N' THEN
7997: IF p_wf_item_type IS NOT NULL AND p_wf_activity_name IS NOT
7998: NULL THEN
7999: OE_DEBUG_PUB.Add ('Calling Overloaded Create_Order_Holds Based on Workflow',1);
8000: Create_Order_Holds (
8001: p_hold_source_rec => l_hold_source_rec
8002: ,p_org_id => p_org_id --ER#7479609
8003: ,p_item_type => p_wf_item_type
8007: ,x_msg_data => x_msg_data
8008: ,x_is_hold_applied => l_is_hold_applied);
8009:
8010: ELSE
8011: OE_DEBUG_PUB.Add ('Calling Create_Order_Holds',1);
8012: Create_Order_Holds (
8013: p_hold_source_rec => l_hold_source_rec
8014: ,p_org_id =>p_org_id --ER#7479609
8015: ,x_return_status => x_return_status
8019: END IF; -- l_item_type and l_activity_name
8020:
8021: x_is_hold_applied := l_is_hold_applied;
8022:
8023: OE_DEBUG_PUB.Add('After Create_Order_Holds',1);
8024: END IF;
8025: END IF;
8026:
8027: END Apply_Holds;
8155: --ER#7479609 end
8156:
8157: l_header_rec OE_Order_PUB.Header_Rec_Type;
8158: l_line_rec OE_Order_PUB.Line_Rec_Type;
8159: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8160: -- GENESIS --
8161: BEGIN
8162: x_return_status := FND_API.G_RET_STS_SUCCESS;
8163: l_user_id := OE_HOLDS_PVT.get_user_id;
8164: l_org_id := MO_GLOBAL.get_current_org_id;
8165: x_is_hold_applied := NULL;
8166: IF l_org_id IS NULL THEN
8167: -- org_id is null, raise an error.
8168: oe_debug_pub.add('Org_Id is NULL',1);
8169: x_return_status := FND_API.G_RET_STS_ERROR;
8170: FND_MESSAGE.SET_NAME('FND','MO_ORG_REQUIRED');
8171: FND_MSG_PUB.ADD;
8172: RAISE FND_API.G_EXC_ERROR;
8171: FND_MSG_PUB.ADD;
8172: RAISE FND_API.G_EXC_ERROR;
8173: END IF;
8174:
8175: oe_debug_pub.add('p_hold_source_rec.hold_source_id:' ||
8176: p_hold_source_rec.hold_source_id);
8177: oe_debug_pub.add('Hold_entity_code/Hold_entity_id/' ||
8178: 'Hold_entity_code2/Hold_entity_id2:' ||
8179: p_hold_source_rec.Hold_entity_code || '/' ||
8173: END IF;
8174:
8175: oe_debug_pub.add('p_hold_source_rec.hold_source_id:' ||
8176: p_hold_source_rec.hold_source_id);
8177: oe_debug_pub.add('Hold_entity_code/Hold_entity_id/' ||
8178: 'Hold_entity_code2/Hold_entity_id2:' ||
8179: p_hold_source_rec.Hold_entity_code || '/' ||
8180: p_hold_source_rec.Hold_entity_id || '/' ||
8181: p_hold_source_rec.Hold_entity_code2 || '/' ||
8179: p_hold_source_rec.Hold_entity_code || '/' ||
8180: p_hold_source_rec.Hold_entity_id || '/' ||
8181: p_hold_source_rec.Hold_entity_code2 || '/' ||
8182: p_hold_source_rec.Hold_entity_id2);
8183: oe_debug_pub.add('p_hold_source_rec.header_id:' || p_hold_source_rec.header_id);
8184: oe_debug_pub.add('p_hold_source_rec.line_id:' || p_hold_source_rec.line_id);
8185: oe_debug_pub.add('Org Id:' || p_org_id);
8186: -- Insert a hold record for the order header or the order line.
8187: IF p_item_type = 'OEOH' and p_activity_name = 'CLOSE_ORDER' THEN
8180: p_hold_source_rec.Hold_entity_id || '/' ||
8181: p_hold_source_rec.Hold_entity_code2 || '/' ||
8182: p_hold_source_rec.Hold_entity_id2);
8183: oe_debug_pub.add('p_hold_source_rec.header_id:' || p_hold_source_rec.header_id);
8184: oe_debug_pub.add('p_hold_source_rec.line_id:' || p_hold_source_rec.line_id);
8185: oe_debug_pub.add('Org Id:' || p_org_id);
8186: -- Insert a hold record for the order header or the order line.
8187: IF p_item_type = 'OEOH' and p_activity_name = 'CLOSE_ORDER' THEN
8188: l_activity_name := 'CLOSE_HEADER';
8181: p_hold_source_rec.Hold_entity_code2 || '/' ||
8182: p_hold_source_rec.Hold_entity_id2);
8183: oe_debug_pub.add('p_hold_source_rec.header_id:' || p_hold_source_rec.header_id);
8184: oe_debug_pub.add('p_hold_source_rec.line_id:' || p_hold_source_rec.line_id);
8185: oe_debug_pub.add('Org Id:' || p_org_id);
8186: -- Insert a hold record for the order header or the order line.
8187: IF p_item_type = 'OEOH' and p_activity_name = 'CLOSE_ORDER' THEN
8188: l_activity_name := 'CLOSE_HEADER';
8189: ELSIF p_item_type = 'OEOL' and p_activity_name = 'PICK_LINE' THEN
8206: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8207: ||' and h.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8208:
8209:
8210: oe_debug_pub.add('Calling InsertTable_OOH_Line for I and C');
8211: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8212: ,p_line_id => p_hold_source_rec.line_id
8213: ,p_org_id => p_org_id
8214: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8216: ,p_activity_name => l_activity_name
8217: ,p_activity_status => l_act_status
8218: ,p_additional_where_clause => l_additional_where_clause
8219: ,x_is_hold_applied => x_is_hold_applied);
8220: oe_debug_pub.add('After Calling InsertTable_OOH_Line for I and C');
8221: --ER#7479609 end
8222:
8223:
8224: /*ER#7479609 start
8379:
8380: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
8381: fnd_message.set_token('WF_ACT', l_user_activity_name);
8382: OE_MSG_PUB.ADD;
8383: oe_debug_pub.add(' Hold Not applied for All requested records');
8384: RAISE FND_API.G_EXC_ERROR;
8385: ELSIF l_sql_rowcount > 0 THEN
8386: SELECT count(*)
8387: into l_parent_count
8397: where oh.header_id = h.header_id
8398: and oh.line_id = ol.line_id
8399: and oh.hold_source_id =
8400: p_hold_source_rec.hold_source_id );
8401: oe_debug_pub.add('l_parent_count/sql_count'||l_parent_count||sql%rowcount);
8402: -- Note: The above query is used based on WHERE clause of the l_sqlmt but without any query on Workflow or Shipping product tables.
8403:
8404: IF l_sql_rowcount < (l_parent_count+l_sql_rowcount) THEN
8405:
8412:
8413: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
8414: fnd_message.set_token('WF_ACT', l_user_activity_name);
8415: OE_MSG_PUB.ADD;
8416: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
8417: END IF;
8418: END IF;
8419: END IF;
8420: END IF;
8427: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8428: ||' and ol.SHIPPING_METHOD_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8429:
8430:
8431: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and SM');
8432: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8433: ,p_line_id => p_hold_source_rec.line_id
8434: ,p_org_id => p_org_id
8435: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8437: ,p_activity_name => l_activity_name
8438: ,p_activity_status => l_act_status
8439: ,p_additional_where_clause => l_additional_where_clause
8440: ,x_is_hold_applied => x_is_hold_applied);
8441: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and SM');
8442: --ER#7479609 end
8443:
8444:
8445: --ER#7479609 start
8449: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8450: ||' and ol.DELIVER_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8451:
8452:
8453: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and D');
8454: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8455: ,p_line_id => p_hold_source_rec.line_id
8456: ,p_org_id => p_org_id
8457: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8459: ,p_activity_name => l_activity_name
8460: ,p_activity_status => l_act_status
8461: ,p_additional_where_clause => l_additional_where_clause
8462: ,x_is_hold_applied => x_is_hold_applied);
8463: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and D');
8464: --ER#7479609 end
8465:
8466: --ER#7479609 start
8467: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
8470: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8471: ||' and ol.PRICE_LIST_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8472:
8473:
8474: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and PL');
8475: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8476: ,p_line_id => p_hold_source_rec.line_id
8477: ,p_org_id => p_org_id
8478: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8480: ,p_activity_name => l_activity_name
8481: ,p_activity_status => l_act_status
8482: ,p_additional_where_clause => l_additional_where_clause
8483: ,x_is_hold_applied => x_is_hold_applied);
8484: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and PL');
8485: --ER#7479609 end
8486:
8487: --ER#7479609 start
8488: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
8491: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8492: ||' and ol.LINE_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8493:
8494:
8495: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and LT');
8496: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8497: ,p_line_id => p_hold_source_rec.line_id
8498: ,p_org_id => p_org_id
8499: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8501: ,p_activity_name => l_activity_name
8502: ,p_activity_status => l_act_status
8503: ,p_additional_where_clause => l_additional_where_clause
8504: ,x_is_hold_applied => x_is_hold_applied);
8505: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and LT');
8506: --ER#7479609 end
8507:
8508: --ER#7479609 start
8509: ELSIF p_hold_source_rec.hold_entity_code = 'I' and
8512: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8513: ||' and ol.PROJECT_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8514:
8515:
8516: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and PR');
8517: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8518: ,p_line_id => p_hold_source_rec.line_id
8519: ,p_org_id => p_org_id
8520: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8522: ,p_activity_name => l_activity_name
8523: ,p_activity_status => l_act_status
8524: ,p_additional_where_clause => l_additional_where_clause
8525: ,x_is_hold_applied => x_is_hold_applied);
8526: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and PR');
8527: --ER#7479609 end
8528:
8529: ELSIF p_hold_source_rec.hold_entity_code = 'B' and
8530: p_hold_source_rec.hold_entity_code2 = 'O' THEN
8533: l_hold_entity_where_clause := 'and ol.INVOICE_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8534: ||' and h.HEADER_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8535:
8536:
8537: oe_debug_pub.add('Calling InsertTable_OOH_Line for B and O');
8538: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8539: ,p_line_id => p_hold_source_rec.line_id
8540: ,p_org_id => p_org_id
8541: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8543: ,p_activity_name => l_activity_name
8544: ,p_activity_status => l_act_status
8545: ,p_additional_where_clause => l_additional_where_clause
8546: ,x_is_hold_applied => x_is_hold_applied);
8547: oe_debug_pub.add('After Calling InsertTable_OOH_Line for B and O');
8548: --ER#7479609 end
8549:
8550: /*ER#7479609 start
8551: IF p_hold_source_rec.line_id IS NOT NULL THEN
8701:
8702: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
8703: fnd_message.set_token('WF_ACT', l_user_activity_name);
8704: OE_MSG_PUB.ADD;
8705: oe_debug_pub.add(' Hold Not applied for All requested records');
8706: RAISE FND_API.G_EXC_ERROR;
8707: ELSIF l_sql_rowcount > 0 THEN
8708: SELECT count(*)
8709: into l_parent_count
8736:
8737: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
8738: fnd_message.set_token('WF_ACT', l_user_activity_name);
8739: OE_MSG_PUB.ADD;
8740: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
8741: END IF;
8742: END IF;
8743: END IF;
8744: END IF;
8750: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8751: ||' and ol.INVOICE_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8752:
8753:
8754: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and B');
8755: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8756: ,p_line_id => p_hold_source_rec.line_id
8757: ,p_org_id => p_org_id
8758: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8760: ,p_activity_name => l_activity_name
8761: ,p_activity_status => l_act_status
8762: ,p_additional_where_clause => l_additional_where_clause
8763: ,x_is_hold_applied => x_is_hold_applied);
8764: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and B');
8765: --ER#7479609 end
8766:
8767: /*ER#7479609 start
8768: IF p_hold_source_rec.line_id IS NOT NULL THEN
8918:
8919: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
8920: fnd_message.set_token('WF_ACT', l_user_activity_name);
8921: OE_MSG_PUB.ADD;
8922: oe_debug_pub.add(' Hold Not applied for All requested records');
8923: RAISE FND_API.G_EXC_ERROR;
8924: ELSIF l_sql_rowcount > 0 THEN
8925: SELECT count(*)
8926: into l_parent_count
8951:
8952: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
8953: fnd_message.set_token('WF_ACT', l_user_activity_name);
8954: OE_MSG_PUB.ADD;
8955: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
8956: END IF;
8957: END IF;
8958: END IF;
8959: END IF;
8965: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
8966: ||' and ol.SHIP_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
8967:
8968:
8969: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and S');
8970: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
8971: ,p_line_id => p_hold_source_rec.line_id
8972: ,p_org_id => p_org_id
8973: ,p_hold_entity_where_clause => l_hold_entity_where_clause
8975: ,p_activity_name => l_activity_name
8976: ,p_activity_status => l_act_status
8977: ,p_additional_where_clause => l_additional_where_clause
8978: ,x_is_hold_applied => x_is_hold_applied);
8979: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and S');
8980: --ER#7479609 end
8981:
8982: /*ER#7479609 start
8983: IF p_hold_source_rec.line_id IS NOT NULL THEN
9131:
9132: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
9133: fnd_message.set_token('WF_ACT', l_user_activity_name);
9134: OE_MSG_PUB.ADD;
9135: oe_debug_pub.add(' Hold Not applied for All requested records');
9136: RAISE FND_API.G_EXC_ERROR;
9137: ELSIF l_sql_rowcount > 0 THEN
9138: SELECT count(*)
9139: into l_parent_count
9163:
9164: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
9165: fnd_message.set_token('WF_ACT', l_user_activity_name);
9166: OE_MSG_PUB.ADD;
9167: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
9168: END IF;
9169: END IF;
9170: END IF;
9171: END IF;
9178: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
9179: ||' and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
9180:
9181:
9182: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and W');
9183: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
9184: ,p_line_id => p_hold_source_rec.line_id
9185: ,p_org_id => p_org_id
9186: ,p_hold_entity_where_clause => l_hold_entity_where_clause
9188: ,p_activity_name => l_activity_name
9189: ,p_activity_status => l_act_status
9190: ,p_additional_where_clause => l_additional_where_clause
9191: ,x_is_hold_applied => x_is_hold_applied);
9192: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and W');
9193: --ER#7479609 end
9194:
9195: /*ER#7479609 start
9196: IF p_hold_source_rec.line_id IS NOT NULL THEN
9345:
9346: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
9347: fnd_message.set_token('WF_ACT', l_user_activity_name);
9348: OE_MSG_PUB.ADD;
9349: oe_debug_pub.add(' Hold Not applied for All requested records');
9350: RAISE FND_API.G_EXC_ERROR;
9351: ELSIF l_sql_rowcount > 0 THEN
9352: SELECT count(*)
9353: into l_parent_count
9378:
9379: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
9380: fnd_message.set_token('WF_ACT', l_user_activity_name);
9381: OE_MSG_PUB.ADD;
9382: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
9383: END IF;
9384: END IF;
9385: END IF;
9386: END IF;
9393: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
9394: ||' and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id2||'''';
9395:
9396:
9397: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I and H');
9398: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
9399: ,p_line_id => p_hold_source_rec.line_id
9400: ,p_org_id => p_org_id
9401: ,p_hold_entity_where_clause => l_hold_entity_where_clause
9403: ,p_activity_name => l_activity_name
9404: ,p_activity_status => l_act_status
9405: ,p_additional_where_clause => l_additional_where_clause
9406: ,x_is_hold_applied => x_is_hold_applied);
9407: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I and H');
9408: --ER#7479609 end
9409:
9410: /*ER#7479609 start
9411: IF p_hold_source_rec.line_id IS NOT NULL THEN
9559:
9560: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
9561: fnd_message.set_token('WF_ACT', l_user_activity_name);
9562: OE_MSG_PUB.ADD;
9563: oe_debug_pub.add(' Hold Not applied for All requested records');
9564: RAISE FND_API.G_EXC_ERROR;
9565: ELSIF l_sql_rowcount > 0 THEN
9566: SELECT count(*)
9567: into l_parent_count
9592:
9593: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
9594: fnd_message.set_token('WF_ACT', l_user_activity_name);
9595: OE_MSG_PUB.ADD;
9596: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
9597: END IF;
9598: END IF;
9599: END IF;
9600: END IF;
9610: l_hold_entity_where_clause := 'and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
9611: ||' and ol.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
9612:
9613:
9614: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and C');
9615: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
9616: ,p_line_id => p_hold_source_rec.line_id
9617: ,p_org_id => p_org_id
9618: ,p_hold_entity_where_clause => l_hold_entity_where_clause
9620: ,p_activity_name => l_activity_name
9621: ,p_activity_status => l_act_status
9622: ,p_additional_where_clause => l_additional_where_clause
9623: ,x_is_hold_applied => x_is_hold_applied);
9624: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and C');
9625: --ER#7479609 end
9626:
9627: /*ER#7479609 start
9628: IF p_hold_source_rec.line_id IS NOT NULL THEN
9777:
9778: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
9779: fnd_message.set_token('WF_ACT', l_user_activity_name);
9780: OE_MSG_PUB.ADD;
9781: oe_debug_pub.add(' Hold Not applied for All requested records');
9782: RAISE FND_API.G_EXC_ERROR;
9783: ELSIF l_sql_rowcount > 0 THEN
9784: SELECT count(*)
9785: into l_parent_count
9810:
9811: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
9812: fnd_message.set_token('WF_ACT', l_user_activity_name);
9813: OE_MSG_PUB.ADD;
9814: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
9815: END IF;
9816: END IF;
9817: END IF;
9818: END IF;
9826: l_hold_entity_where_clause := 'and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
9827: ||' and ol.SHIPPING_METHOD_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
9828:
9829:
9830: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and SM');
9831: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
9832: ,p_line_id => p_hold_source_rec.line_id
9833: ,p_org_id => p_org_id
9834: ,p_hold_entity_where_clause => l_hold_entity_where_clause
9836: ,p_activity_name => l_activity_name
9837: ,p_activity_status => l_act_status
9838: ,p_additional_where_clause => l_additional_where_clause
9839: ,x_is_hold_applied => x_is_hold_applied);
9840: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and SM');
9841: --ER#7479609 end
9842:
9843: --ER#7479609 start
9844: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
9848: l_hold_entity_where_clause := 'and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
9849: ||' and ol.SOURCE_TYPE_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
9850:
9851:
9852: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and ST');
9853: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
9854: ,p_line_id => p_hold_source_rec.line_id
9855: ,p_org_id => p_org_id
9856: ,p_hold_entity_where_clause => l_hold_entity_where_clause
9858: ,p_activity_name => l_activity_name
9859: ,p_activity_status => l_act_status
9860: ,p_additional_where_clause => l_additional_where_clause
9861: ,x_is_hold_applied => x_is_hold_applied);
9862: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and ST');
9863: --ER#7479609 end
9864:
9865:
9866: --ER#7479609 start
9871: l_hold_entity_where_clause := 'and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
9872: ||' and ol.LINE_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
9873:
9874:
9875: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and LT');
9876: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
9877: ,p_line_id => p_hold_source_rec.line_id
9878: ,p_org_id => p_org_id
9879: ,p_hold_entity_where_clause => l_hold_entity_where_clause
9881: ,p_activity_name => l_activity_name
9882: ,p_activity_status => l_act_status
9883: ,p_additional_where_clause => l_additional_where_clause
9884: ,x_is_hold_applied => x_is_hold_applied);
9885: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and LT');
9886: --ER#7479609 end
9887:
9888: --ER#7479609 start
9889: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
9892: l_hold_entity_where_clause := 'and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
9893: ||' and ol.DELIVER_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
9894:
9895:
9896: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and D');
9897: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
9898: ,p_line_id => p_hold_source_rec.line_id
9899: ,p_org_id => p_org_id
9900: ,p_hold_entity_where_clause => l_hold_entity_where_clause
9902: ,p_activity_name => l_activity_name
9903: ,p_activity_status => l_act_status
9904: ,p_additional_where_clause => l_additional_where_clause
9905: ,x_is_hold_applied => x_is_hold_applied);
9906: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and D');
9907: --ER#7479609 end
9908:
9909:
9910: ELSIF p_hold_source_rec.hold_entity_code = 'W' and
9914: l_hold_entity_where_clause := 'and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
9915: ||' and ol.INVOICE_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
9916:
9917:
9918: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and B');
9919: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
9920: ,p_line_id => p_hold_source_rec.line_id
9921: ,p_org_id => p_org_id
9922: ,p_hold_entity_where_clause => l_hold_entity_where_clause
9924: ,p_activity_name => l_activity_name
9925: ,p_activity_status => l_act_status
9926: ,p_additional_where_clause => l_additional_where_clause
9927: ,x_is_hold_applied => x_is_hold_applied);
9928: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and B');
9929: --ER#7479609 end
9930:
9931: /*ER#7479609 start
9932: IF p_hold_source_rec.line_id IS NOT NULL THEN
10082:
10083: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
10084: fnd_message.set_token('WF_ACT', l_user_activity_name);
10085: OE_MSG_PUB.ADD;
10086: oe_debug_pub.add(' Hold Not applied for All requested records');
10087: RAISE FND_API.G_EXC_ERROR;
10088: ELSIF l_sql_rowcount > 0 THEN
10089: SELECT count(*)
10090: into l_parent_count
10115:
10116: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
10117: fnd_message.set_token('WF_ACT', l_user_activity_name);
10118: OE_MSG_PUB.ADD;
10119: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
10120: END IF;
10121: END IF;
10122: END IF;
10123: END IF;
10130: l_hold_entity_where_clause := 'and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
10131: ||' and ol.SHIP_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10132:
10133:
10134: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W and S');
10135: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10136: ,p_line_id => p_hold_source_rec.line_id
10137: ,p_org_id => p_org_id
10138: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10140: ,p_activity_name => l_activity_name
10141: ,p_activity_status => l_act_status
10142: ,p_additional_where_clause => l_additional_where_clause
10143: ,x_is_hold_applied => x_is_hold_applied);
10144: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W and S');
10145: --ER#7479609 end
10146:
10147: /*ER#7479609 start
10148: IF p_hold_source_rec.line_id IS NOT NULL THEN
10296:
10297: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
10298: fnd_message.set_token('WF_ACT', l_user_activity_name);
10299: OE_MSG_PUB.ADD;
10300: oe_debug_pub.add(' Hold Not applied for All requested records');
10301: RAISE FND_API.G_EXC_ERROR;
10302: ELSIF l_sql_rowcount > 0 THEN
10303: SELECT count(*)
10304: into l_parent_count
10329:
10330: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
10331: fnd_message.set_token('WF_ACT', l_user_activity_name);
10332: OE_MSG_PUB.ADD;
10333: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
10334: END IF;
10335: END IF;
10336: END IF;
10337: END IF;
10347: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
10348: ||' and ol.INVOICE_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10349:
10350:
10351: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and B');
10352: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10353: ,p_line_id => p_hold_source_rec.line_id
10354: ,p_org_id => p_org_id
10355: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10357: ,p_activity_name => l_activity_name
10358: ,p_activity_status => l_act_status
10359: ,p_additional_where_clause => l_additional_where_clause
10360: ,x_is_hold_applied => x_is_hold_applied);
10361: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and B');
10362: --ER#7479609 end
10363:
10364: /*ER#7479609 start
10365: IF p_hold_source_rec.line_id IS NOT NULL THEN
10513:
10514: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
10515: fnd_message.set_token('WF_ACT', l_user_activity_name);
10516: OE_MSG_PUB.ADD;
10517: oe_debug_pub.add(' Hold Not applied for All requested records');
10518: RAISE FND_API.G_EXC_ERROR;
10519: ELSIF l_sql_rowcount > 0 THEN
10520: SELECT count(*)
10521: into l_parent_count
10546:
10547: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
10548: fnd_message.set_token('WF_ACT', l_user_activity_name);
10549: OE_MSG_PUB.ADD;
10550: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
10551: END IF;
10552: END IF;
10553: END IF;
10554: END IF;
10562: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
10563: ||' and ol.PAYMENT_TERM_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10564:
10565:
10566: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and PT');
10567: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10568: ,p_line_id => p_hold_source_rec.line_id
10569: ,p_org_id => p_org_id
10570: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10572: ,p_activity_name => l_activity_name
10573: ,p_activity_status => l_act_status
10574: ,p_additional_where_clause => l_additional_where_clause
10575: ,x_is_hold_applied => x_is_hold_applied);
10576: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and PT');
10577: --ER#7479609 end
10578:
10579:
10580: --ER#7479609 start
10584: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
10585: ||' and ol.PRICE_LIST_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10586:
10587:
10588: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and PL');
10589: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10590: ,p_line_id => p_hold_source_rec.line_id
10591: ,p_org_id => p_org_id
10592: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10594: ,p_activity_name => l_activity_name
10595: ,p_activity_status => l_act_status
10596: ,p_additional_where_clause => l_additional_where_clause
10597: ,x_is_hold_applied => x_is_hold_applied);
10598: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and PL');
10599: --ER#7479609 end
10600:
10601:
10602: --ER#7479609 start
10606: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
10607: ||' and ol.DELIVER_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10608:
10609:
10610: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and D');
10611: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10612: ,p_line_id => p_hold_source_rec.line_id
10613: ,p_org_id => p_org_id
10614: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10616: ,p_activity_name => l_activity_name
10617: ,p_activity_status => l_act_status
10618: ,p_additional_where_clause => l_additional_where_clause
10619: ,x_is_hold_applied => x_is_hold_applied);
10620: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and D');
10621: --ER#7479609 end
10622:
10623:
10624: --ER#7479609 start
10628: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
10629: ||' and ol.LINE_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10630:
10631:
10632: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and LT');
10633: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10634: ,p_line_id => p_hold_source_rec.line_id
10635: ,p_org_id => p_org_id
10636: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10638: ,p_activity_name => l_activity_name
10639: ,p_activity_status => l_act_status
10640: ,p_additional_where_clause => l_additional_where_clause
10641: ,x_is_hold_applied => x_is_hold_applied);
10642: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and LT');
10643: --ER#7479609 end
10644:
10645: --ER#7479609 start
10646: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
10649: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
10650: ||' and ol.SHIPPING_METHOD_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10651:
10652:
10653: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and SM');
10654: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10655: ,p_line_id => p_hold_source_rec.line_id
10656: ,p_org_id => p_org_id
10657: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10659: ,p_activity_name => l_activity_name
10660: ,p_activity_status => l_act_status
10661: ,p_additional_where_clause => l_additional_where_clause
10662: ,x_is_hold_applied => x_is_hold_applied);
10663: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and SM');
10664: --ER#7479609 end
10665:
10666:
10667: ELSIF p_hold_source_rec.hold_entity_code = 'H' and
10671: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
10672: ||' and ol.SHIP_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10673:
10674:
10675: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and S');
10676: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10677: ,p_line_id => p_hold_source_rec.line_id
10678: ,p_org_id => p_org_id
10679: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10681: ,p_activity_name => l_activity_name
10682: ,p_activity_status => l_act_status
10683: ,p_additional_where_clause => l_additional_where_clause
10684: ,x_is_hold_applied => x_is_hold_applied);
10685: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and S');
10686: --ER#7479609 end
10687:
10688: /*ER#7479609 start
10689: IF p_hold_source_rec.line_id IS NOT NULL THEN
10838:
10839: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
10840: fnd_message.set_token('WF_ACT', l_user_activity_name);
10841: OE_MSG_PUB.ADD;
10842: oe_debug_pub.add(' Hold Not applied for All requested records');
10843: RAISE FND_API.G_EXC_ERROR;
10844: ELSIF l_sql_rowcount > 0 THEN
10845: SELECT count(*)
10846: into l_parent_count
10871:
10872: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
10873: fnd_message.set_token('WF_ACT', l_user_activity_name);
10874: OE_MSG_PUB.ADD;
10875: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
10876: END IF;
10877: END IF;
10878: END IF;
10879: END IF;
10886: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
10887: ||' and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
10888:
10889:
10890: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and W');
10891: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
10892: ,p_line_id => p_hold_source_rec.line_id
10893: ,p_org_id => p_org_id
10894: ,p_hold_entity_where_clause => l_hold_entity_where_clause
10896: ,p_activity_name => l_activity_name
10897: ,p_activity_status => l_act_status
10898: ,p_additional_where_clause => l_additional_where_clause
10899: ,x_is_hold_applied => x_is_hold_applied);
10900: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and W');
10901: --ER#7479609 end
10902:
10903: /*ER#7479609 start
10904: IF p_hold_source_rec.line_id IS NOT NULL THEN
11052:
11053: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
11054: fnd_message.set_token('WF_ACT', l_user_activity_name);
11055: OE_MSG_PUB.ADD;
11056: oe_debug_pub.add(' Hold Not applied for All requested records');
11057: RAISE FND_API.G_EXC_ERROR;
11058: ELSIF l_sql_rowcount > 0 THEN
11059: SELECT count(*)
11060: into l_parent_count
11085:
11086: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
11087: fnd_message.set_token('WF_ACT', l_user_activity_name);
11088: OE_MSG_PUB.ADD;
11089: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
11090: END IF;
11091: END IF;
11092: END IF;
11093: END IF;
11100: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||''''
11101: ||' and ol.BLANKET_LINE_NUMBER = '||''''||p_hold_source_rec.hold_entity_id2||'''';
11102:
11103:
11104: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H and L');
11105: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
11106: ,p_line_id => p_hold_source_rec.line_id
11107: ,p_org_id => p_org_id
11108: ,p_hold_entity_where_clause => l_hold_entity_where_clause
11110: ,p_activity_name => l_activity_name
11111: ,p_activity_status => l_act_status
11112: ,p_additional_where_clause => l_additional_where_clause
11113: ,x_is_hold_applied => x_is_hold_applied);
11114: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H and L');
11115: --ER#7479609 end
11116:
11117: /*ER#7479609 start
11118: IF p_hold_source_rec.line_id IS NOT NULL THEN
11266:
11267: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
11268: fnd_message.set_token('WF_ACT', l_user_activity_name);
11269: OE_MSG_PUB.ADD;
11270: oe_debug_pub.add(' Hold Not applied for All requested records');
11271: RAISE FND_API.G_EXC_ERROR;
11272: ELSIF l_sql_rowcount > 0 THEN
11273: SELECT count(*)
11274: into l_parent_count
11299:
11300: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
11301: fnd_message.set_token('WF_ACT', l_user_activity_name);
11302: OE_MSG_PUB.ADD;
11303: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
11304: END IF;
11305: END IF;
11306: END IF;
11307: END IF;
11313: --ER#7479609 start
11314: l_hold_entity_where_clause := 'and ol.BLANKET_NUMBER = '||''''||p_hold_source_rec.hold_entity_id||'''';
11315:
11316:
11317: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for H');
11318: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
11319: ,p_line_id => p_hold_source_rec.line_id
11320: ,p_org_id => p_org_id
11321: ,p_hold_entity_where_clause => l_hold_entity_where_clause
11323: ,p_activity_name => l_activity_name
11324: ,p_activity_status => l_act_status
11325: ,p_additional_where_clause => l_additional_where_clause
11326: ,x_is_hold_applied => x_is_hold_applied);
11327: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for H');
11328: --ER#7479609 end
11329:
11330: /*ER#7479609 start
11331: IF p_hold_source_rec.line_id IS NOT NULL THEN
11477:
11478: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
11479: fnd_message.set_token('WF_ACT', l_user_activity_name);
11480: OE_MSG_PUB.ADD;
11481: oe_debug_pub.add(' Hold Not applied for All requested records');
11482: RAISE FND_API.G_EXC_ERROR;
11483: ELSIF l_sql_rowcount > 0 THEN
11484: SELECT count(*)
11485: into l_parent_count
11509:
11510: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
11511: fnd_message.set_token('WF_ACT', l_user_activity_name);
11512: OE_MSG_PUB.ADD;
11513: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
11514: END IF;
11515: END IF;
11516: END IF;
11517: END IF;
11657:
11658: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
11659: fnd_message.set_token('WF_ACT', l_user_activity_name);
11660: OE_MSG_PUB.ADD;
11661: oe_debug_pub.add(' Hold Not applied for All requested records');
11662: RAISE FND_API.G_EXC_ERROR;
11663: ELSIF l_sql_rowcount > 0 THEN
11664: x_is_hold_applied := TRUE;
11665: SELECT count(*)
11691:
11692: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
11693: fnd_message.set_token('WF_ACT', l_user_activity_name);
11694: OE_MSG_PUB.ADD;
11695: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
11696: END IF;
11697: END IF;
11698: END IF;
11699: END IF;
11708:
11709: --ER#7479609 start
11710: l_hold_entity_where_clause := 'and h.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
11711:
11712: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C');
11713:
11714: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
11715: ,p_header_id =>p_hold_source_rec.header_id
11716: ,p_org_id => p_org_id
11720: ,p_activity_status => l_act_status
11721: ,p_additional_where_clause => l_additional_where_clause
11722: ,x_is_hold_applied => x_is_hold_applied);
11723:
11724: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for C');
11725: --ER#7479609 end
11726:
11727:
11728: /*ER#7479609 start
11837:
11838: execute immediate l_sqlmt using l_user_id, l_user_id, p_hold_source_rec.hold_source_id, p_hold_source_rec.hold_entity_id,p_org_id, p_hold_source_rec.hold_source_id, 'OEOH', l_activity_name, l_act_status;
11839:
11840: l_sql_rowcount := sql%rowcount;
11841: oe_debug_pub.add('l_sql_rowcount - '||l_sql_rowcount);
11842: IF l_sql_rowcount = 0 THEN
11843:
11844: select meaning into l_user_activity_name
11845: from oe_lookups
11850:
11851: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
11852: fnd_message.set_token('WF_ACT', l_user_activity_name);
11853: OE_MSG_PUB.ADD;
11854: oe_debug_pub.add(' Hold Not applied for All requested records');
11855: RAISE FND_API.G_EXC_ERROR;
11856: ELSIF l_sql_rowcount > 0 THEN
11857: SELECT count(*)
11858: into l_parent_count
11867: and oh.hold_source_id =
11868: p_hold_source_rec.hold_source_id );
11869:
11870: -- Note: The above query is used based on WHERE clause of the l_sqlmt but without any query on Workflow -- or Shipping product tables.
11871: oe_debug_pub.add('l_parent_count - '||l_parent_count);
11872: IF l_sql_rowcount < (l_parent_count+l_sql_rowcount) THEN
11873:
11874: select meaning into l_user_activity_name
11875: from oe_lookups
11880:
11881: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
11882: fnd_message.set_token('WF_ACT', l_user_activity_name);
11883: OE_MSG_PUB.ADD;
11884: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
11885: END IF;
11886: END IF;
11887: END IF;
11888: END IF;
11896: l_hold_entity_where_clause := 'and ol.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
11897: ||' and ol.INVOICE_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
11898:
11899:
11900: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C and B');
11901: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
11902: ,p_line_id => p_hold_source_rec.line_id
11903: ,p_org_id => p_org_id
11904: ,p_hold_entity_where_clause => l_hold_entity_where_clause
11906: ,p_activity_name => l_activity_name
11907: ,p_activity_status => l_act_status
11908: ,p_additional_where_clause => l_additional_where_clause
11909: ,x_is_hold_applied => x_is_hold_applied);
11910: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C and B');
11911: --ER#7479609 end
11912:
11913:
11914: --ER#7479609 start
11919: l_hold_entity_where_clause := 'and ol.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
11920: ||' and ol.SHIP_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
11921:
11922:
11923: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C and S');
11924: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
11925: ,p_line_id => p_hold_source_rec.line_id
11926: ,p_org_id => p_org_id
11927: ,p_hold_entity_where_clause => l_hold_entity_where_clause
11929: ,p_activity_name => l_activity_name
11930: ,p_activity_status => l_act_status
11931: ,p_additional_where_clause => l_additional_where_clause
11932: ,x_is_hold_applied => x_is_hold_applied);
11933: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C and S');
11934: --ER#7479609 end
11935:
11936:
11937: --ER#7479609 start
11942: l_hold_entity_where_clause := 'and ol.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
11943: ||' and ol.DELIVER_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
11944:
11945:
11946: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C and D');
11947: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
11948: ,p_line_id => p_hold_source_rec.line_id
11949: ,p_org_id => p_org_id
11950: ,p_hold_entity_where_clause => l_hold_entity_where_clause
11952: ,p_activity_name => l_activity_name
11953: ,p_activity_status => l_act_status
11954: ,p_additional_where_clause => l_additional_where_clause
11955: ,x_is_hold_applied => x_is_hold_applied);
11956: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C and D');
11957: --ER#7479609 end
11958:
11959:
11960: --ER#7479609 start
11965: l_hold_entity_where_clause := 'and ol.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
11966: ||' and ol.PRICE_LIST_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
11967:
11968:
11969: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C and PL');
11970: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
11971: ,p_line_id => p_hold_source_rec.line_id
11972: ,p_org_id => p_org_id
11973: ,p_hold_entity_where_clause => l_hold_entity_where_clause
11975: ,p_activity_name => l_activity_name
11976: ,p_activity_status => l_act_status
11977: ,p_additional_where_clause => l_additional_where_clause
11978: ,x_is_hold_applied => x_is_hold_applied);
11979: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C and PL');
11980: --ER#7479609 end
11981:
11982:
11983: --ER#7479609 start
11988: l_hold_entity_where_clause := 'and ol.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
11989: ||' and ol.LINE_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
11990:
11991:
11992: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C and LT');
11993: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
11994: ,p_line_id => p_hold_source_rec.line_id
11995: ,p_org_id => p_org_id
11996: ,p_hold_entity_where_clause => l_hold_entity_where_clause
11998: ,p_activity_name => l_activity_name
11999: ,p_activity_status => l_act_status
12000: ,p_additional_where_clause => l_additional_where_clause
12001: ,x_is_hold_applied => x_is_hold_applied);
12002: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C and LT');
12003: --ER#7479609 end
12004:
12005:
12006: --ER#7479609 start
12011: l_hold_entity_where_clause := 'and ol.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
12012: ||' and ol.PAYMENT_TERM_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
12013:
12014:
12015: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C and PT');
12016: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
12017: ,p_line_id => p_hold_source_rec.line_id
12018: ,p_org_id => p_org_id
12019: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12021: ,p_activity_name => l_activity_name
12022: ,p_activity_status => l_act_status
12023: ,p_additional_where_clause => l_additional_where_clause
12024: ,x_is_hold_applied => x_is_hold_applied);
12025: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C and PT');
12026: --ER#7479609 end
12027:
12028:
12029: --ER#7479609 start
12033:
12034: l_hold_entity_where_clause := 'and h.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
12035: ||' and h.ORDER_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
12036:
12037: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C and OT');
12038: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
12039: ,p_header_id =>p_hold_source_rec.header_id
12040: ,p_org_id => p_org_id
12041: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12044: ,p_activity_status => l_activity_name
12045: ,p_additional_where_clause => l_additional_where_clause
12046: ,x_is_hold_applied => x_is_hold_applied);
12047:
12048: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for C and OT');
12049: --ER#7479609 end
12050:
12051: --ER#7479609 start
12052: ELSIF p_hold_source_rec.hold_entity_code = 'C' and
12052: ELSIF p_hold_source_rec.hold_entity_code = 'C' and
12053: p_hold_source_rec.hold_entity_code2 = 'P' THEN
12054:
12055:
12056: OE_DEBUG_PUB.ADD('Calling PaymentType_Hold for C and P');
12057: PaymentType_Hold (p_hold_source_rec => p_hold_source_rec
12058: ,p_org_id => p_org_id
12059: ,p_item_type => p_item_type
12060: ,p_activity_name => l_activity_name
12067: x_return_status := 'PAY_HOLD_NA';
12068: END IF;
12069: --ER#7479609 end
12070:
12071: OE_DEBUG_PUB.ADD('After Calling InsePaymentType_Hold for C and P');
12072: --ER#7479609 end
12073:
12074: --ER#7479609 start
12075: ELSIF p_hold_source_rec.hold_entity_code = 'C' and
12078:
12079: l_hold_entity_where_clause := 'and h.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
12080: ||' and h.TRANSACTIONAL_CURR_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
12081:
12082: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C and TC');
12083: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
12084: ,p_header_id =>p_hold_source_rec.header_id
12085: ,p_org_id => p_org_id
12086: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12089: ,p_activity_status => l_act_status
12090: ,p_additional_where_clause => l_additional_where_clause
12091: ,x_is_hold_applied => x_is_hold_applied);
12092:
12093: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for C and TC');
12094: --ER#7479609 end
12095:
12096: --ER#7479609 start
12097: ELSIF p_hold_source_rec.hold_entity_code = 'C' and
12101: l_hold_entity_where_clause := 'and ol.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
12102: ||' and ol.SOURCE_TYPE_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
12103:
12104:
12105: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for C and ST');
12106: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
12107: ,p_line_id => p_hold_source_rec.line_id
12108: ,p_org_id => p_org_id
12109: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12111: ,p_activity_name => l_activity_name
12112: ,p_activity_status => l_act_status
12113: ,p_additional_where_clause => l_additional_where_clause
12114: ,x_is_hold_applied => x_is_hold_applied);
12115: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for C and ST');
12116: --ER#7479609 end
12117:
12118:
12119: --ER#7479609 start
12123:
12124: l_hold_entity_where_clause := 'and h.SOLD_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
12125: ||' and h.SALES_CHANNEL_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
12126:
12127: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for C and SC');
12128: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
12129: ,p_header_id =>p_hold_source_rec.header_id
12130: ,p_org_id => p_org_id
12131: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12134: ,p_activity_status => l_act_status
12135: ,p_additional_where_clause => l_additional_where_clause
12136: ,x_is_hold_applied => x_is_hold_applied);
12137:
12138: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for C and SC');
12139: --ER#7479609 end
12140:
12141:
12142: /*********************************************************
12149: --ER#7479609 start
12150: l_hold_entity_where_clause := 'and ol.INVOICE_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
12151:
12152:
12153: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for B');
12154: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
12155: ,p_line_id => p_hold_source_rec.line_id
12156: ,p_org_id => p_org_id
12157: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12159: ,p_activity_name => l_activity_name
12160: ,p_activity_status => l_act_status
12161: ,p_additional_where_clause => l_additional_where_clause
12162: ,x_is_hold_applied => x_is_hold_applied);
12163: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for B');
12164: --ER#7479609 end
12165:
12166: /*ER#7479609 start
12167: IF p_hold_source_rec.line_id IS NOT NULL THEN
12312:
12313: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
12314: fnd_message.set_token('WF_ACT', l_user_activity_name);
12315: OE_MSG_PUB.ADD;
12316: oe_debug_pub.add(' Hold Not applied for All requested records');
12317: RAISE FND_API.G_EXC_ERROR;
12318: ELSIF l_sql_rowcount > 0 THEN
12319: SELECT count(*)
12320: into l_parent_count
12344:
12345: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
12346: fnd_message.set_token('WF_ACT', l_user_activity_name);
12347: OE_MSG_PUB.ADD;
12348: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
12349: END IF;
12350: END IF;
12351: END IF;
12352: END IF;
12362: --ER#7479609 start
12363: l_hold_entity_where_clause := 'and ol.SHIP_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
12364:
12365:
12366: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for S');
12367: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
12368: ,p_line_id => p_hold_source_rec.line_id
12369: ,p_org_id => p_org_id
12370: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12372: ,p_activity_name => l_activity_name
12373: ,p_activity_status => l_act_status
12374: ,p_additional_where_clause => l_additional_where_clause
12375: ,x_is_hold_applied => x_is_hold_applied);
12376: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for S');
12377: --ER#7479609 end
12378:
12379: /*ER#7479609 start
12380: IF p_hold_source_rec.line_id IS NOT NULL THEN
12524:
12525: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
12526: fnd_message.set_token('WF_ACT', l_user_activity_name);
12527: OE_MSG_PUB.ADD;
12528: oe_debug_pub.add(' Hold Not applied for All requested records');
12529: RAISE FND_API.G_EXC_ERROR;
12530: ELSIF l_sql_rowcount > 0 THEN
12531: SELECT count(*)
12532: into l_parent_count
12556:
12557: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
12558: fnd_message.set_token('WF_ACT', l_user_activity_name);
12559: OE_MSG_PUB.ADD;
12560: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
12561: END IF;
12562: END IF;
12563: END IF;
12564: END IF;
12573: --ER#7479609 start
12574: l_hold_entity_where_clause := 'and ol.SHIP_FROM_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
12575:
12576:
12577: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for W');
12578: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
12579: ,p_line_id => p_hold_source_rec.line_id
12580: ,p_org_id => p_org_id
12581: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12583: ,p_activity_name => l_activity_name
12584: ,p_activity_status => l_act_status
12585: ,p_additional_where_clause => l_additional_where_clause
12586: ,x_is_hold_applied => x_is_hold_applied);
12587: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for W');
12588: --ER#7479609 end
12589:
12590: /*ER#7479609 start
12591: IF p_hold_source_rec.line_id IS NOT NULL THEN
12735:
12736: fnd_message.set_name('ONT', 'OE_NO_HOLD_ALL_LINES');
12737: fnd_message.set_token('WF_ACT', l_user_activity_name);
12738: OE_MSG_PUB.ADD;
12739: oe_debug_pub.add(' Hold Not applied for All requested records');
12740: RAISE FND_API.G_EXC_ERROR;
12741: ELSIF l_sql_rowcount > 0 THEN
12742: SELECT count(*)
12743: into l_parent_count
12767:
12768: fnd_message.set_name('ONT', 'OE_NO_HOLD_FEW_LINES');
12769: fnd_message.set_token('WF_ACT', l_user_activity_name);
12770: OE_MSG_PUB.ADD;
12771: oe_debug_pub.add(' Hold Not applied for FEW of the requested records');
12772: END IF;
12773: END IF;
12774: END IF;
12775: END IF;
12784: --ER#7479609 start
12785: l_hold_entity_where_clause := 'and ol.INVENTORY_ITEM_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
12786:
12787:
12788: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for I');
12789: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
12790: ,p_line_id => p_hold_source_rec.line_id
12791: ,p_org_id => p_org_id
12792: ,p_hold_entity_where_clause => l_hold_entity_where_clause
12794: ,p_activity_name => l_activity_name
12795: ,p_activity_status => l_act_status
12796: ,p_additional_where_clause => l_additional_where_clause
12797: ,x_is_hold_applied => x_is_hold_applied);
12798: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for I');
12799: --ER#7479609 end
12800:
12801: /*ER#7479609 start
12802: IF p_hold_source_rec.line_id IS NOT NULL THEN
12993: l_hold_entity_where_clause := 'and ol.PROJECT_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
12994: ||' and ol.TASK_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
12995:
12996:
12997: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for PR and T');
12998: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
12999: ,p_line_id => p_hold_source_rec.line_id
13000: ,p_org_id => p_org_id
13001: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13003: ,p_activity_name => l_activity_name
13004: ,p_activity_status => l_act_status
13005: ,p_additional_where_clause => l_additional_where_clause
13006: ,x_is_hold_applied => x_is_hold_applied);
13007: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for PR and T');
13008: --ER#7479609 end
13009:
13010: --ER#7479609 start
13011: ELSIF p_hold_source_rec.hold_entity_code = 'PR' and
13013:
13014: l_hold_entity_where_clause := 'and ol.PROJECT_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
13015:
13016:
13017: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for PR and T');
13018: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
13019: ,p_line_id => p_hold_source_rec.line_id
13020: ,p_org_id => p_org_id
13021: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13023: ,p_activity_name => l_activity_name
13024: ,p_activity_status => l_act_status
13025: ,p_additional_where_clause => l_additional_where_clause
13026: ,x_is_hold_applied => x_is_hold_applied);
13027: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for PR and T');
13028: --ER#7479609 end
13029:
13030:
13031: --ER#7479609 start
13035: l_hold_entity_where_clause := 'and h.PRICE_LIST_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
13036: ||' and h.TRANSACTIONAL_CURR_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
13037:
13038:
13039: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for PL and TC');
13040: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
13041: ,p_header_id =>p_hold_source_rec.header_id
13042: ,p_org_id => p_org_id
13043: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13045: ,p_activity_name => l_activity_name
13046: ,p_activity_status => l_act_status
13047: ,p_additional_where_clause => l_additional_where_clause
13048: ,x_is_hold_applied => x_is_hold_applied);
13049: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for PR and T');
13050: --ER#7479609 end
13051:
13052:
13053: --ER#7479609 start
13056:
13057: l_hold_entity_where_clause := 'and h.PRICE_LIST_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
13058:
13059:
13060: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for PL and TC');
13061: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
13062: ,p_header_id =>p_hold_source_rec.header_id
13063: ,p_org_id => p_org_id
13064: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13066: ,p_activity_name => l_activity_name
13067: ,p_activity_status => l_act_status
13068: ,p_additional_where_clause => l_additional_where_clause
13069: ,x_is_hold_applied => x_is_hold_applied);
13070: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for PR and T');
13071: --ER#7479609 end
13072:
13073: --ER#7479609 start
13074: ELSIF p_hold_source_rec.hold_entity_code = 'OT' and
13077: l_hold_entity_where_clause := 'and h.ORDER_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
13078: ||' and ol.LINE_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id2||'''';
13079:
13080:
13081: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for OT and LT');
13082: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
13083: ,p_line_id => p_hold_source_rec.line_id
13084: ,p_org_id => p_org_id
13085: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13087: ,p_activity_name => l_activity_name
13088: ,p_activity_status => l_act_status
13089: ,p_additional_where_clause => l_additional_where_clause
13090: ,x_is_hold_applied => x_is_hold_applied);
13091: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for OT and LT');
13092: --ER#7479609 end
13093:
13094: --ER#7479609 start
13095: ELSIF p_hold_source_rec.hold_entity_code = 'OT' and
13098: l_hold_entity_where_clause := 'and h.ORDER_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id||''''
13099: ||' and h.TRANSACTIONAL_CURR_CODE = '||''''||p_hold_source_rec.hold_entity_id2||'''';
13100:
13101:
13102: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for OT and TC');
13103: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
13104: ,p_header_id =>p_hold_source_rec.header_id
13105: ,p_org_id => p_org_id
13106: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13108: ,p_activity_name => l_activity_name
13109: ,p_activity_status => l_act_status
13110: ,p_additional_where_clause => l_additional_where_clause
13111: ,x_is_hold_applied => x_is_hold_applied);
13112: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for OT and TC');
13113: --ER#7479609 end
13114:
13115: --ER#7479609 start
13116: ELSIF p_hold_source_rec.hold_entity_code = 'OT' and
13118:
13119: l_hold_entity_where_clause := 'and h.ORDER_TYPE_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
13120:
13121:
13122: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Header for OT and TC');
13123: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
13124: ,p_header_id =>p_hold_source_rec.header_id
13125: ,p_org_id => p_org_id
13126: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13128: ,p_activity_name => l_activity_name
13129: ,p_activity_status => l_act_status
13130: ,p_additional_where_clause => l_additional_where_clause
13131: ,x_is_hold_applied => x_is_hold_applied);
13132: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Header for OT and TC');
13133: --ER#7479609 end
13134:
13135:
13136: --ER#7479609 start
13140: l_hold_entity_where_clause := 'and to_char(ol.CREATION_DATE,''DD-MON-RRRR'') = '||''''||p_hold_source_rec.hold_entity_id||''''
13141: ||' and ol.CREATED_BY = '||''''||p_hold_source_rec.hold_entity_id2||'''';
13142:
13143:
13144: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for CD and CB');
13145: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
13146: ,p_line_id => p_hold_source_rec.line_id
13147: ,p_org_id => p_org_id
13148: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13150: ,p_activity_name => l_activity_name
13151: ,p_activity_status => l_act_status
13152: ,p_additional_where_clause => l_additional_where_clause
13153: ,x_is_hold_applied => x_is_hold_applied);
13154: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for CD and CB');
13155: --ER#7479609 end
13156:
13157: --ER#7479609 start
13158: ELSIF p_hold_source_rec.hold_entity_code = 'CD' and
13160:
13161: l_hold_entity_where_clause := 'and to_char(ol.CREATION_DATE,''DD-MON-RRRR'') = '||''''||p_hold_source_rec.hold_entity_id||'''';
13162:
13163:
13164: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for CD');
13165: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
13166: ,p_line_id => p_hold_source_rec.line_id
13167: ,p_org_id => p_org_id
13168: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13170: ,p_activity_name => l_activity_name
13171: ,p_activity_status => l_act_status
13172: ,p_additional_where_clause => l_additional_where_clause
13173: ,x_is_hold_applied => x_is_hold_applied);
13174: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for CD');
13175: --ER#7479609 end
13176:
13177:
13178: --ER#7479609 start
13181:
13182: l_hold_entity_where_clause := 'and h.SALES_CHANNEL_CODE = '||''''||p_hold_source_rec.hold_entity_id||'''';
13183:
13184:
13185: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for SC');
13186: InsertTable_OOH_Header (p_hold_source_id => p_hold_source_rec.hold_source_id
13187: ,p_header_id =>p_hold_source_rec.header_id
13188: ,p_org_id => p_org_id
13189: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13191: ,p_activity_name => l_activity_name
13192: ,p_activity_status => l_act_status
13193: ,p_additional_where_clause => l_additional_where_clause
13194: ,x_is_hold_applied => x_is_hold_applied);
13195: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for SC');
13196: --ER#7479609 end
13197:
13198: --ER#7479609 start
13199: ELSIF p_hold_source_rec.hold_entity_code = 'P'
13199: ELSIF p_hold_source_rec.hold_entity_code = 'P'
13200: AND p_hold_source_rec.hold_entity_code2 IS NULL THEN
13201:
13202:
13203: OE_DEBUG_PUB.ADD('Calling PaymentType_Hold for P');
13204: PaymentType_Hold (p_hold_source_rec => p_hold_source_rec
13205: ,p_org_id => p_org_id
13206: ,p_item_type => p_item_type
13207: ,p_activity_name => l_activity_name
13214: x_return_status := 'PAY_HOLD_NA';
13215: END IF;
13216: --ER#7479609 end
13217:
13218: OE_DEBUG_PUB.ADD('After Calling PaymentType_Hold for P');
13219: --ER#7479609 end
13220:
13221: --ER#7479609 start
13222: ELSIF p_hold_source_rec.hold_entity_code = 'SM'
13224:
13225: l_hold_entity_where_clause := 'and ol.SHIPPING_METHOD_CODE = '||''''||p_hold_source_rec.hold_entity_id||'''';
13226:
13227:
13228: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for SM');
13229: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
13230: ,p_line_id => p_hold_source_rec.line_id
13231: ,p_org_id => p_org_id
13232: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13234: ,p_activity_name => l_activity_name
13235: ,p_activity_status => l_act_status
13236: ,p_additional_where_clause => l_additional_where_clause
13237: ,x_is_hold_applied => x_is_hold_applied);
13238: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for SM');
13239: --ER#7479609 end
13240:
13241: --ER#7479609 start
13242: ELSIF p_hold_source_rec.hold_entity_code = 'D'
13245: --ER#7479609 start
13246: l_hold_entity_where_clause := 'and ol.DELIVER_TO_ORG_ID = '||''''||p_hold_source_rec.hold_entity_id||'''';
13247:
13248:
13249: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for D');
13250: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
13251: ,p_line_id => p_hold_source_rec.line_id
13252: ,p_org_id => p_org_id
13253: ,p_hold_entity_where_clause => l_hold_entity_where_clause
13255: ,p_activity_name => l_activity_name
13256: ,p_activity_status => l_act_status
13257: ,p_additional_where_clause => l_additional_where_clause
13258: ,x_is_hold_applied => x_is_hold_applied);
13259: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for D');
13260: --ER#7479609 end
13261:
13262:
13263: --ER#7479609 start
13294: END;
13295:
13296:
13297:
13298: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for TM:'||l_line_id_tab.count);
13299:
13300: FOR i in 1 .. l_line_id_tab.count LOOP
13301:
13302: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
13309: ,p_additional_where_clause => l_additional_where_clause
13310: ,x_is_hold_applied => x_is_hold_applied);
13311: END LOOP;
13312:
13313: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for TM');
13314:
13315: ELSIF p_hold_source_rec.hold_entity_code = 'TM'
13316: AND p_hold_source_rec.hold_entity_code2 = 'OI' THEN
13317:
13347: END;
13348:
13349:
13350:
13351: OE_DEBUG_PUB.ADD('Calling InsertTable_OOH_Line for TM and OI:'||l_line_id_tab.count);
13352:
13353: FOR i in 1 .. l_line_id_tab.count LOOP
13354:
13355: InsertTable_OOH_Line (p_hold_source_id => p_hold_source_rec.hold_source_id
13362: ,p_additional_where_clause => l_additional_where_clause
13363: ,x_is_hold_applied => x_is_hold_applied);
13364: END LOOP;
13365:
13366: OE_DEBUG_PUB.ADD('After Calling InsertTable_OOH_Line for TM and OI');
13367: --ER#7479609 end
13368:
13369:
13370: END IF;
13373: -- GENESIS --
13374: IF p_hold_source_rec.line_id IS NOT NULL THEN
13375:
13376: IF l_debug_level > 0 THEN
13377: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.line_id IS NOT NULL');
13378: END IF;
13379:
13380: OPEN check_line_hold_type_cur(p_hold_source_rec.line_id);
13381: FETCH check_line_hold_type_cur INTO l_check_hold;
13380: OPEN check_line_hold_type_cur(p_hold_source_rec.line_id);
13381: FETCH check_line_hold_type_cur INTO l_check_hold;
13382: CLOSE check_line_hold_type_cur;
13383: IF l_debug_level > 0 THEN
13384: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold' || l_check_hold);
13385: END IF;
13386:
13387: ELSIF p_hold_source_rec.line_id IS NULL AND
13388: p_hold_source_rec.header_id IS NOT NULL THEN
13386:
13387: ELSIF p_hold_source_rec.line_id IS NULL AND
13388: p_hold_source_rec.header_id IS NOT NULL THEN
13389: IF l_debug_level > 0 THEN
13390: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.header_id IS NOT NULL');
13391: END IF;
13392: OPEN check_hdr_hold_type_cur(p_hold_source_rec.header_id);
13393: FETCH check_hdr_hold_type_cur INTO l_check_hold;
13394: CLOSE check_hdr_hold_type_cur;
13392: OPEN check_hdr_hold_type_cur(p_hold_source_rec.header_id);
13393: FETCH check_hdr_hold_type_cur INTO l_check_hold;
13394: CLOSE check_hdr_hold_type_cur;
13395: IF l_debug_level > 0 THEN
13396: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold' || l_check_hold);
13397: END IF;
13398:
13399: ELSIF p_hold_source_rec.line_id IS NULL AND
13400: p_hold_source_rec.header_id IS NULL AND
13399: ELSIF p_hold_source_rec.line_id IS NULL AND
13400: p_hold_source_rec.header_id IS NULL AND
13401: p_hold_source_rec.hold_source_id IS NOT NULL THEN
13402: IF l_debug_level > 0 THEN
13403: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - p_hold_source_rec.hold_source_id IS NOT NULL');
13404: END IF;
13405: OPEN check_src_hold_type_cur(p_hold_source_rec.hold_source_id);
13406: FETCH check_src_hold_type_cur INTO l_check_hold;
13407: CLOSE check_src_hold_type_cur;
13406: FETCH check_src_hold_type_cur INTO l_check_hold;
13407: CLOSE check_src_hold_type_cur;
13408:
13409: IF l_debug_level > 0 THEN
13410: oe_debug_pub.add('OE_HOLDS_PVT-CREATE ORDER HOLDS - l_check_hold :' || l_check_hold);
13411: END IF;
13412:
13413: END IF;
13414:
13412:
13413: END IF;
13414:
13415: IF l_debug_level > 0 THEN
13416: oe_debug_pub.add( 'OE_HOLDS_PVT - CREATE ORDER HOLDS - BEFORE SYNC_HEADER_LINE');
13417: END IF;
13418:
13419: IF NVL(l_check_hold, 'N') = 'Y' THEN
13420: IF l_debug_level > 0 THEN
13417: END IF;
13418:
13419: IF NVL(l_check_hold, 'N') = 'Y' THEN
13420: IF l_debug_level > 0 THEN
13421: oe_debug_pub.add( 'OE_HOLDS_PVT - CREATE ORDER HOLDS - l_check_hold: ' || l_check_hold);
13422: END IF;
13423:
13424: IF p_hold_source_rec.hold_entity_code = 'O' THEN
13425: IF l_debug_level > 0 THEN
13422: END IF;
13423:
13424: IF p_hold_source_rec.hold_entity_code = 'O' THEN
13425: IF l_debug_level > 0 THEN
13426: oe_debug_pub.add(' p_hold_source_rec.hold_entity_code ' || p_hold_source_rec.hold_entity_code);
13427: END IF;
13428: IF p_hold_source_rec.hold_entity_id is NOT NULL THEN
13429: IF l_debug_level > 0 THEN
13430: oe_debug_pub.add(' p_hold_source_rec.hold_entity_id : ' || p_hold_source_rec.hold_entity_id);
13426: oe_debug_pub.add(' p_hold_source_rec.hold_entity_code ' || p_hold_source_rec.hold_entity_code);
13427: END IF;
13428: IF p_hold_source_rec.hold_entity_id is NOT NULL THEN
13429: IF l_debug_level > 0 THEN
13430: oe_debug_pub.add(' p_hold_source_rec.hold_entity_id : ' || p_hold_source_rec.hold_entity_id);
13431: END IF;
13432: oe_header_util.query_row ( p_header_id => p_hold_source_rec.hold_entity_id,
13433: x_header_rec => l_header_rec);
13434: END IF; -- p_hold_source_rec.hold_entity_id is NOT NULL
13434: END IF; -- p_hold_source_rec.hold_entity_id is NOT NULL
13435:
13436: IF p_hold_source_rec.line_id is not NULL THEN
13437: IF l_debug_level > 0 THEN
13438: oe_debug_pub.add(' p_hold_source_rec.line_id : ' || p_hold_source_rec.line_id );
13439: END IF;
13440: oe_line_util.query_row(
13441: p_line_id => p_hold_source_rec.line_id
13442: ,x_line_rec => l_line_rec
13451:
13452: ELSE --p_hold_source_rec.hold_entity_code = 'O'
13453:
13454: IF l_debug_level > 0 THEN
13455: oe_debug_pub.add(' p_hold_source_rec.hold_entity_code ' || p_hold_source_rec.hold_entity_code);
13456: END IF;
13457:
13458: IF p_hold_source_rec.header_id is NOT NULL THEN
13459: oe_header_util.query_row ( p_header_id => p_hold_source_rec.header_id,