[Home] [Help]
27: RETURN BOOLEAN
28: IS
29: l_dummy VARCHAR2(10);
30: l_site_use_code VARCHAR2(30) := 'SHIP_TO';
31: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
32:
33: BEGIN
34: IF l_debug_level > 0 then
35: oe_debug_pub.add('Entering Validate_ship_to_org',1);
31: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
32:
33: BEGIN
34: IF l_debug_level > 0 then
35: oe_debug_pub.add('Entering Validate_ship_to_org',1);
36: oe_debug_pub.add('ship_to_org_id :'||to_char(p_ship_to_org_id),2);
37: END IF;
38:
39: IF g_customer_relations = 'N' THEN
32:
33: BEGIN
34: IF l_debug_level > 0 then
35: oe_debug_pub.add('Entering Validate_ship_to_org',1);
36: oe_debug_pub.add('ship_to_org_id :'||to_char(p_ship_to_org_id),2);
37: END IF;
38:
39: IF g_customer_relations = 'N' THEN
40:
45: AND site_use_id = p_ship_to_org_id
46: AND status = 'A'
47: AND address_status ='A'; --bug 2752321
48: IF l_debug_level > 0 then
49: oe_debug_pub.add('Exiting Validate_ship_to_org',1);
50: END IF;
51: RETURN TRUE;
52: ELSIF g_customer_relations = 'Y' THEN
53: IF l_debug_level > 0 then
50: END IF;
51: RETURN TRUE;
52: ELSIF g_customer_relations = 'Y' THEN
53: IF l_debug_level > 0 then
54: oe_debug_pub.add ('Cr: Yes Line Ship',2);
55: END IF;
56:
57: SELECT /* MOAC_SQL_CHANGE */ 'VALID'
58: Into l_dummy
106: IS
107: l_dummy VARCHAR2(10);
108: l_site_use_code VARCHAR2(30) := 'DELIVER_TO';
109:
110: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
111: BEGIN
112: IF l_debug_level > 0 then
113: oe_debug_pub.add('Entering OE_VALIDATE_LINE.Validate_Deliver_To_Org',1);
114: oe_debug_pub.add('deliver_to_org_id :'||to_char(p_deliver_to_org_id),2);
109:
110: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
111: BEGIN
112: IF l_debug_level > 0 then
113: oe_debug_pub.add('Entering OE_VALIDATE_LINE.Validate_Deliver_To_Org',1);
114: oe_debug_pub.add('deliver_to_org_id :'||to_char(p_deliver_to_org_id),2);
115: END IF;
116:
117:
110: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
111: BEGIN
112: IF l_debug_level > 0 then
113: oe_debug_pub.add('Entering OE_VALIDATE_LINE.Validate_Deliver_To_Org',1);
114: oe_debug_pub.add('deliver_to_org_id :'||to_char(p_deliver_to_org_id),2);
115: END IF;
116:
117:
118: IF g_customer_relations = 'N' THEN
123: AND site_use_id = p_deliver_to_org_id
124: AND status = 'A'
125: AND address_status ='A';--bug 2752321
126: IF l_debug_level > 0 then
127: oe_debug_pub.add('Exiting OE_VALIDATE_LINE.Validate_Deliver_To_Org',1);
128: END IF;
129: RETURN TRUE;
130:
131: ELSIF g_customer_relations = 'Y' THEN
129: RETURN TRUE;
130:
131: ELSIF g_customer_relations = 'Y' THEN
132: IF l_debug_level > 0 then
133: oe_debug_pub.add('Cr: Yes Line Deliver',2);
134: END IF;
135:
136: SELECT /* MOAC_SQL_CHANGE */ 'VALID'
137: Into l_dummy
154: ACCT_SITE.CUST_ACCOUNT_ID AND STATUS='A')
155: AND ROWNUM = 1;
156:
157: IF l_debug_level > 0 then
158: oe_debug_pub.add('Exiting OE_VALIDATE_LINE.Validate_Deliver_To_Org',1);
159: END IF;
160: RETURN TRUE;
161:
162: ELSIF g_customer_relations = 'A' THEN
169: AND DEL.ADDRESS_STATUS ='A' --bug 2752321
170: AND SYSDATE BETWEEN NVL(DEL.START_DATE_ACTIVE, SYSDATE)
171: AND NVL(DEL.END_DATE_ACTIVE, SYSDATE);
172: IF l_debug_level > 0 then
173: oe_debug_pub.add('Exiting OE_VALIDATE_LINE.Validate_Deliver_To_Org',1);
174: END IF;
175: RETURN TRUE;
176:
177:
176:
177:
178: END IF;
179: IF l_debug_level > 0 then
180: oe_debug_pub.add('Exiting OE_VALIDATE_LINE.Validate_Deliver_To_Org',1);
181: END IF;
182: RETURN TRUE;
183:
184: EXCEPTION
196: IS
197: l_dummy VARCHAR2(10);
198: l_site_use_code VARCHAR2(30) := 'BILL_TO';
199:
200: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
201: BEGIN
202: IF l_debug_level > 0 then
203: oe_debug_pub.add('Entering Validate_Invoice_To_Org',1);
204: oe_debug_pub.add('Invoice_to_org_id :'||to_char(p_Invoice_to_org_id),2);
199:
200: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
201: BEGIN
202: IF l_debug_level > 0 then
203: oe_debug_pub.add('Entering Validate_Invoice_To_Org',1);
204: oe_debug_pub.add('Invoice_to_org_id :'||to_char(p_Invoice_to_org_id),2);
205: END IF;
206:
207:
200: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
201: BEGIN
202: IF l_debug_level > 0 then
203: oe_debug_pub.add('Entering Validate_Invoice_To_Org',1);
204: oe_debug_pub.add('Invoice_to_org_id :'||to_char(p_Invoice_to_org_id),2);
205: END IF;
206:
207:
208: IF g_customer_relations = 'N' THEN
217: RETURN TRUE;
218:
219: ELSIF g_customer_relations = 'Y' THEN
220: IF l_debug_level > 0 then
221: oe_debug_pub.add('Cr: Yes Line Inv',2);
222: END IF;
223:
224: SELECT /* MOAC_SQL_CHANGE */ 'VALID'
225: Into l_dummy
289: I number := p_Index;
290: lheader_id number;
291:
292: --
293: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
294: --
295: BEGIN
296: /* set the list_header_id to g_miss_num */
297:
295: BEGIN
296: /* set the list_header_id to g_miss_num */
297:
298: if l_debug_level > 0 then
299: oe_debug_pub.add('Blanket In create price list');
300: end if;
301: IF oe_delayed_requests_pvt.g_delayed_requests(I).param1 IS NULL THEN
302: RETURN;
303: END IF;
344: AND
345: oe_delayed_requests_pvt.g_delayed_requests(I).entity_code =
346: oe_blanket_pub.g_entity_blanket_LINE THEN
347: if l_debug_level > 0 then
348: oe_debug_pub.add('Blanket In create price list - line');
349: end if;
350: IF oe_delayed_requests_pvt.g_delayed_requests(I).param1 IS NULL THEN
351: if l_debug_level > 0 then
352: oe_debug_pub.add('Blanket In Skip');
348: oe_debug_pub.add('Blanket In create price list - line');
349: end if;
350: IF oe_delayed_requests_pvt.g_delayed_requests(I).param1 IS NULL THEN
351: if l_debug_level > 0 then
352: oe_debug_pub.add('Blanket In Skip');
353: end if;
354: GOTO SKIP_LINE;
355: END IF;
356: g_line_id_tbl(K).line_id := oe_delayed_requests_pvt.g_delayed_requests(I).entity_id;
391: AND OE_Code_Control.Get_Code_Release_Level >= '110510'
392: THEN
393: gpr_price_list_line_tbl(K).customer_item_id :=
394: oe_delayed_requests_pvt.g_delayed_requests(I).param8;
395: oe_debug_pub.add('sending cust item id :'||
396: gpr_price_list_line_tbl(K).customer_item_id);
397: END IF;
398:
399: END IF;
426: gpr_qualifiers_tbl(K).qualifier_attribute := 'QUALIFIER_ATTRIBUTE6';
427: gpr_qualifiers_tbl(K).comparison_operator_code := '=';
428: -- Blanket Line ID is the qualifier attribute value
429: gpr_qualifiers_tbl(K).qualifier_attr_value := oe_delayed_requests_pvt.g_delayed_requests(I).entity_id;
430: oe_debug_pub.add('entity id :'||oe_delayed_requests_pvt.g_delayed_requests(I).entity_id);
431: gpr_qualifiers_tbl(K).operation := QP_GLOBALS.G_OPR_CREATE;
432: END IF;
433: */
434:
475: gpr_pricing_attr_tbl(K).pricing_attr_value_from :=
476: ppr_price_list_line_tbl(K).list_line_id;
477: gpr_pricing_attr_tbl(K).comparison_operator_code := '=';
478: if l_debug_level > 0 then
479: oe_debug_pub.add('pricing attr id :'||
480: gpr_pricing_attr_tbl(K).pricing_attribute_id);
481: oe_debug_pub.add('list line id :'||
482: gpr_pricing_attr_tbl(K).pricing_attr_value_from);
483: end if;
477: gpr_pricing_attr_tbl(K).comparison_operator_code := '=';
478: if l_debug_level > 0 then
479: oe_debug_pub.add('pricing attr id :'||
480: gpr_pricing_attr_tbl(K).pricing_attribute_id);
481: oe_debug_pub.add('list line id :'||
482: gpr_pricing_attr_tbl(K).pricing_attr_value_from);
483: end if;
484: K := ppr_price_list_line_tbl.next(K);
485: END LOOP;
620: I number := p_req_ind;
621: lheader_id number;
622:
623: --
624: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
625: --
626: BEGIN
627: --begin commented the below if statement for bug 4762658
628: /*IF oe_delayed_requests_pvt.g_delayed_requests(I).param3 IS NULL THEN
633: IF (oe_delayed_requests_pvt.g_delayed_requests(I).param1 IS NULL OR oe_delayed_requests_pvt.g_delayed_requests(I).param1=FND_API.G_MISS_NUM)
634: THEN
635: IF l_debug_level > 0
636: THEN
637: oe_debug_pub.add('Skip for null values');
638: END IF;
639: oe_delayed_requests_pvt.g_delayed_requests.delete(I);
640: GOTO SKIP_LINE1;
641: END IF;
678: AND OE_Code_Control.Get_Code_Release_Level >= '110510'
679: THEN
680: gpr_price_list_line_tbl(K).customer_item_id :=
681: oe_delayed_requests_pvt.g_delayed_requests(I).param8;
682: oe_debug_pub.add('sending cust item id :'||
683: gpr_price_list_line_tbl(K).customer_item_id);
684: END IF;
685:
686: END IF;
735: gpr_pricing_attr_tbl(K).pricing_attr_value_from :=
736: ppr_price_list_line_tbl(K).list_line_id;
737: gpr_pricing_attr_tbl(K).comparison_operator_code := '=';
738: if l_debug_level > 0 then
739: oe_debug_pub.add('pricing attr id :'||
740: gpr_pricing_attr_tbl(K).pricing_attribute_id);
741: oe_debug_pub.add('list line id :'||
742: gpr_pricing_attr_tbl(K).pricing_attr_value_from);
743: end if;
737: gpr_pricing_attr_tbl(K).comparison_operator_code := '=';
738: if l_debug_level > 0 then
739: oe_debug_pub.add('pricing attr id :'||
740: gpr_pricing_attr_tbl(K).pricing_attribute_id);
741: oe_debug_pub.add('list line id :'||
742: gpr_pricing_attr_tbl(K).pricing_attr_value_from);
743: end if;
744: K := ppr_price_list_line_tbl.next(K);
745: END LOOP;
829: ppr_qualifiers_val_tbl QP_Qualifier_Rules_Pub.Qualifiers_Val_Tbl_Type;
830: ppr_pricing_attr_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Tbl_Type;
831: ppr_pricing_attr_val_tbl QP_PRICE_LIST_PUB.Pricing_Attr_Val_Tbl_Type;
832: k number := 1;
833: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
834: --
835: BEGIN
836: x_return_status:= FND_API.G_RET_STS_SUCCESS;
837: gpr_price_list_line_tbl(K).list_line_id := oe_delayed_requests_pvt.g_delayed_requests(p_req_ind).Param1 ;
882:
883: WHEN FND_API.G_EXC_ERROR THEN
884:
885: IF l_debug_level > 0 THEN
886: OE_DEBUG_PUB.Add('Expected Error in Clear_Blanket_List_line...',4);
887: END IF;
888: x_return_status := FND_API.G_RET_STS_ERROR;
889:
890:
890:
891: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
892:
893: IF l_debug_level > 0 THEN
894: OE_DEBUG_PUB.Add('UnExpected Error in Clear_Blanket_list_line...'||sqlerrm,4);
895: END IF;
896: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
897:
898:
916: , x_return_status OUT NOCOPY VARCHAR2
917: ) IS
918: l_dummy VARCHAR2(10);
919: --
920: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
921: --
922: BEGIN
923:
924: if l_debug_level > 0 then
921: --
922: BEGIN
923:
924: if l_debug_level > 0 then
925: oe_debug_pub.add('Entering OE_BLANKET_UTIL.VALIDATE_ATTRIBUTES',1);
926: end if;
927: x_return_status := FND_API.G_RET_STS_SUCCESS;
928:
929: -- Validate header attributes
1263: p_old_header_rec.context IS NULL )))
1264: THEN
1265:
1266:
1267: oe_debug_pub.add('Before calling header_desc_flex',2);
1268: IF OE_ORDER_CACHE.IS_FLEX_ENABLED('OE_BLKT_HEADER_ATTRIBUTES') = 'Y' THEN
1269:
1270: IF NOT OE_VALIDATE.Header_Desc_Flex
1271: (p_context => p_x_header_rec.context
1459: END IF ; -- If flex enabled
1460: END IF;
1461:
1462: if l_debug_level > 0 then
1463: oe_debug_pub.add('After blanket header_desc_flex ' || x_return_status,2);
1464: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.VALIDATE_ATTRIBUTES',1);
1465: end if;
1466: EXCEPTION
1467:
1460: END IF;
1461:
1462: if l_debug_level > 0 then
1463: oe_debug_pub.add('After blanket header_desc_flex ' || x_return_status,2);
1464: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.VALIDATE_ATTRIBUTES',1);
1465: end if;
1466: EXCEPTION
1467:
1468: WHEN FND_API.G_EXC_ERROR THEN
1502: WHERE (application_id = 660)
1503: AND (descriptive_flexfield_name = l_flex_name);
1504: -- Bug # 5562785}
1505: --
1506: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1507: --
1508: BEGIN
1509:
1510: if l_debug_level > 0 then
1507: --
1508: BEGIN
1509:
1510: if l_debug_level > 0 then
1511: oe_debug_pub.add('Enter procedure OE_blanket_util.validate line Attributes',1);
1512: end if;
1513:
1514: x_return_status := FND_API.G_RET_STS_SUCCESS;
1515: -- Compare line attributes with header record if the header record is
1524: p_old_line_rec.accounting_rule_id OR
1525: p_old_line_rec.accounting_rule_id IS NULL )
1526: THEN
1527: if l_debug_level > 0 then
1528: oe_debug_pub.add('Calling OE_VALIDATE for accounting_rule',1);
1529: end if;
1530: IF NOT OE_Validate.Accounting_Rule(p_x_line_rec.accounting_rule_id) THEN
1531: IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL AND
1532: p_x_line_rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
1654: OPEN c_check_context('OE_BLKT_LINE_ATTRIBUTES');
1655: FETCH c_check_context INTO l_context_required_flag,l_default_context_field_name;
1656: CLOSE c_check_context;
1657:
1658: oe_debug_pub.add('Maitrayee: Entering the code change in Validate_Attributes');
1659: -- Skip the Validation if not changes are made in the DFF from the UI.
1660:
1661: IF l_context_required_flag = 'Y' AND ( p_x_line_rec.context IS NULL OR p_x_line_rec.context = FND_API.G_MISS_CHAR ) AND (OE_GLOBALS.G_UI_FLAG) THEN
1662:
1661: IF l_context_required_flag = 'Y' AND ( p_x_line_rec.context IS NULL OR p_x_line_rec.context = FND_API.G_MISS_CHAR ) AND (OE_GLOBALS.G_UI_FLAG) THEN
1662:
1663: l_validate_line := 'N';
1664: IF l_debug_level > 0 then
1665: oe_debug_pub.add('Skipping Validation');
1666: END IF;
1667:
1668: ELSIF l_context_required_flag = 'Y' AND ( p_x_line_rec.context IS NULL OR p_x_line_rec.context = FND_API.G_MISS_CHAR ) AND NOT (OE_GLOBALS.G_UI_FLAG) THEN
1669:
1673: FND_MESSAGE.SET_NAME('FND', 'ONT_BLKT_CONTEXT_NOT_FOUND');
1674:
1675: OE_MSG_PUB.ADD;
1676: IF l_debug_level > 0 THEN
1677: oe_debug_pub.add( 'Context not set for OE_BLKT_LINE_ATTRIBUTES DFF ' ) ;
1678: END IF;
1679: RAISE FND_API.G_EXC_ERROR;
1680: ELSE
1681:
1683:
1684: l_validate_line := 'Y';
1685:
1686: IF l_debug_level > 0 then
1687: oe_debug_pub.add('Validating the Flex Field');
1688: END IF;
1689:
1690: END IF;
1691:
1780: p_old_line_rec.context OR
1781: p_old_line_rec.context IS NULL )))
1782: THEN
1783:
1784: oe_debug_pub.add('Before calling line_desc_flex',2);
1785: IF Oe_Order_Cache.IS_FLEX_ENABLED('OE_BLKT_LINE_ATTRIBUTES') = 'Y' THEN
1786:
1787: IF NOT OE_VALIDATE.Line_Desc_Flex
1788: (p_context => p_x_line_rec.context
1974: -- end of assignments, bug 2511313
1975: END IF; -- Flex Validation successfull
1976: END IF; -- Is flex enabled
1977:
1978: oe_debug_pub.add('After line_desc_flex ' || x_return_status,2);
1979:
1980: END IF; -- For Additional Line Information
1981:
1982: END IF; -- 5562785
1981:
1982: END IF; -- 5562785
1983:
1984: if l_debug_level > 0 then
1985: oe_debug_pub.add('Exiting procedure OE_BLANKET_UTIL.Validate Line Attributes',1);
1986: end if;
1987:
1988: EXCEPTION
1989:
2040: ORDER BY 1;
2041: /* end of code added for 2219230 */
2042:
2043: --
2044: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2045: --
2046: BEGIN
2047: if l_debug_level > 0 then
2048: oe_debug_pub.add('In validate Item Fields', 1);
2044: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2045: --
2046: BEGIN
2047: if l_debug_level > 0 then
2048: oe_debug_pub.add('In validate Item Fields', 1);
2049:
2050: oe_debug_pub.add('item identifier type :'||p_item_identifier_type);
2051: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2052: oe_debug_pub.add('ordered item id :'||p_ordered_item_id);
2046: BEGIN
2047: if l_debug_level > 0 then
2048: oe_debug_pub.add('In validate Item Fields', 1);
2049:
2050: oe_debug_pub.add('item identifier type :'||p_item_identifier_type);
2051: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2052: oe_debug_pub.add('ordered item id :'||p_ordered_item_id);
2053: oe_debug_pub.add('ordered item :'||p_ordered_item);
2054: oe_debug_pub.add('sold to org id :'||p_sold_to_org_id);
2047: if l_debug_level > 0 then
2048: oe_debug_pub.add('In validate Item Fields', 1);
2049:
2050: oe_debug_pub.add('item identifier type :'||p_item_identifier_type);
2051: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2052: oe_debug_pub.add('ordered item id :'||p_ordered_item_id);
2053: oe_debug_pub.add('ordered item :'||p_ordered_item);
2054: oe_debug_pub.add('sold to org id :'||p_sold_to_org_id);
2055: end if;
2048: oe_debug_pub.add('In validate Item Fields', 1);
2049:
2050: oe_debug_pub.add('item identifier type :'||p_item_identifier_type);
2051: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2052: oe_debug_pub.add('ordered item id :'||p_ordered_item_id);
2053: oe_debug_pub.add('ordered item :'||p_ordered_item);
2054: oe_debug_pub.add('sold to org id :'||p_sold_to_org_id);
2055: end if;
2056: --perform validation of inventory_item_id based on context
2049:
2050: oe_debug_pub.add('item identifier type :'||p_item_identifier_type);
2051: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2052: oe_debug_pub.add('ordered item id :'||p_ordered_item_id);
2053: oe_debug_pub.add('ordered item :'||p_ordered_item);
2054: oe_debug_pub.add('sold to org id :'||p_sold_to_org_id);
2055: end if;
2056: --perform validation of inventory_item_id based on context
2057: IF p_item_identifier_type = 'INT' THEN --validate inventory_item_id
2050: oe_debug_pub.add('item identifier type :'||p_item_identifier_type);
2051: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2052: oe_debug_pub.add('ordered item id :'||p_ordered_item_id);
2053: oe_debug_pub.add('ordered item :'||p_ordered_item);
2054: oe_debug_pub.add('sold to org id :'||p_sold_to_org_id);
2055: end if;
2056: --perform validation of inventory_item_id based on context
2057: IF p_item_identifier_type = 'INT' THEN --validate inventory_item_id
2058: if l_debug_level > 0 then
2055: end if;
2056: --perform validation of inventory_item_id based on context
2057: IF p_item_identifier_type = 'INT' THEN --validate inventory_item_id
2058: if l_debug_level > 0 then
2059: oe_debug_pub.add('Blanket In validate INT');
2060: end if;
2061: SELECT 'valid'
2062: INTO l_dummy
2063: FROM mtl_system_items_b
2066: AND customer_order_enabled_flag = 'Y';
2067:
2068: ELSIF p_item_identifier_type = 'CUST' THEN
2069: if l_debug_level > 0 then
2070: oe_debug_pub.add('Blanket In validate CUST',1);
2071: end if;
2072: SELECT 'valid'
2073: INTO l_dummy
2074: FROM mtl_customer_items citems
2085: AND cxref.inactive_flag = 'N';
2086:
2087: ELSIF p_item_identifier_type = 'CAT' THEN
2088: if l_debug_level > 0 then
2089: oe_debug_pub.add('Blanket In validate CAT');
2090: end if;
2091: SELECT 'VALID'
2092: INTO l_dummy
2093: FROM MTL_CATEGORIES_VL
2095: AND ENABLED_FLAG = 'Y';
2096:
2097: ELSE
2098: if l_debug_level > 0 then
2099: oe_debug_pub.add('Blanket In validate generic');
2100: end if;
2101: --code taken from OE_validate_line.validate_item_fields
2102: /* SELECT replaced for with the following for 2219230 */
2103: if l_debug_level > 0 then
2100: end if;
2101: --code taken from OE_validate_line.validate_item_fields
2102: /* SELECT replaced for with the following for 2219230 */
2103: if l_debug_level > 0 then
2104: oe_debug_pub.add('Validating generic item, item_val_org:'||to_char(item_val_org),5);
2105: end if;
2106: OPEN xref;
2107: FETCH xref INTO l_org_flag;
2108: IF xref%NOTFOUND OR l_org_flag <> 1 THEN
2106: OPEN xref;
2107: FETCH xref INTO l_org_flag;
2108: IF xref%NOTFOUND OR l_org_flag <> 1 THEN
2109: if l_debug_level > 0 then
2110: oe_debug_pub.add('Blanket Invalid Generic Item', 1);
2111: end if;
2112: CLOSE xref;
2113: END IF;
2114: CLOSE xref;
2119:
2120: EXCEPTION
2121: WHEN NO_DATA_FOUND THEN
2122: if l_debug_level > 0 then
2123: oe_debug_pub.add('Validate Item based on context: No data found',1);
2124: end if;
2125: RETURN FALSE;
2126: WHEN OTHERS THEN
2127: if l_debug_level > 0 then
2124: end if;
2125: RETURN FALSE;
2126: WHEN OTHERS THEN
2127: if l_debug_level > 0 then
2128: oe_debug_pub.add('Validate_Item_Fields: When Others',1);
2129: end if;
2130: RETURN FALSE;
2131: END Validate_Item_Fields;
2132:
2139: RETURN BOOLEAN
2140: IS
2141: l_rule_type VARCHAR2(10);
2142: l_item_org NUMBER := to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'));
2143: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2144: l_service_item_flag VARCHAR2(1);
2145: l_bom_item_type NUMBER;
2146: l_dummy VARCHAR2(25);
2147:
2146: l_dummy VARCHAR2(25);
2147:
2148: BEGIN
2149: if l_debug_level > 0 then
2150: oe_debug_pub.add('In validate accounting rule', 1);
2151: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2152: oe_debug_pub.add('l_item_org :'||l_item_org);
2153: end if;
2154: --Bug 5169363
2147:
2148: BEGIN
2149: if l_debug_level > 0 then
2150: oe_debug_pub.add('In validate accounting rule', 1);
2151: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2152: oe_debug_pub.add('l_item_org :'||l_item_org);
2153: end if;
2154: --Bug 5169363
2155: --bug5220335 moved the query under BEGIN-EXCEPTION-END
2148: BEGIN
2149: if l_debug_level > 0 then
2150: oe_debug_pub.add('In validate accounting rule', 1);
2151: oe_debug_pub.add('inventory item id :'||p_inventory_item_id);
2152: oe_debug_pub.add('l_item_org :'||l_item_org);
2153: end if;
2154: --Bug 5169363
2155: --bug5220335 moved the query under BEGIN-EXCEPTION-END
2156: BEGIN
2178: IF NOT (l_bom_item_type = 4 and l_service_item_flag = 'Y') THEN -- not service
2179: IF p_accounting_rule_id <> FND_API.G_MISS_NUM AND
2180: p_accounting_rule_id IS NOT NULL THEN
2181: IF l_debug_level > 0 THEN
2182: oe_debug_pub.add('Getting accounting rule type');
2183: END IF;
2184: SELECT type
2185: INTO l_rule_type
2186: FROM ra_rules
2185: INTO l_rule_type
2186: FROM ra_rules
2187: WHERE rule_id = p_accounting_rule_id;
2188: IF l_debug_level > 0 THEN
2189: oe_debug_pub.add('Rule Type is :'||l_rule_type);
2190: END IF;
2191: IF l_rule_type = 'PP_DR_ALL' or l_rule_type = 'PP_DR_PP' THEN
2192: RETURN FALSE;
2193: END IF; --End of rule type
2200:
2201: EXCEPTION
2202: WHEN OTHERS THEN
2203: if l_debug_level > 0 then
2204: oe_debug_pub.add('Validate_Accounting Rule: When Others',1);
2205: oe_debug_pub.add('Error message...'||sqlerrm);
2206: end if;
2207: RETURN FALSE;
2208: END Validate_Accounting_Rule;
2201: EXCEPTION
2202: WHEN OTHERS THEN
2203: if l_debug_level > 0 then
2204: oe_debug_pub.add('Validate_Accounting Rule: When Others',1);
2205: oe_debug_pub.add('Error message...'||sqlerrm);
2206: end if;
2207: RETURN FALSE;
2208: END Validate_Accounting_Rule;
2209: --bug 4893057
2221: l_site_use_code VARCHAR2(30);
2222: l_return_status varchar2(1);
2223:
2224: --
2225: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2226: --
2227: l_list_line_exists VARCHAR2(1) := 'N';
2228: BEGIN
2229:
2229:
2230: x_return_status := FND_API.G_RET_STS_SUCCESS;
2231:
2232: if l_debug_level > 0 then
2233: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate ENTITY - Line',1);
2234: oe_debug_pub.add('Operation : '||p_line_rec.operation);
2235: oe_debug_pub.add('Price List ID : '||p_line_rec.price_list_id);
2236: oe_debug_pub.add('Unit List Price : '||p_line_rec.unit_list_price);
2237: oe_debug_pub.add('Line end date : '||p_line_rec.end_date_active);
2230: x_return_status := FND_API.G_RET_STS_SUCCESS;
2231:
2232: if l_debug_level > 0 then
2233: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate ENTITY - Line',1);
2234: oe_debug_pub.add('Operation : '||p_line_rec.operation);
2235: oe_debug_pub.add('Price List ID : '||p_line_rec.price_list_id);
2236: oe_debug_pub.add('Unit List Price : '||p_line_rec.unit_list_price);
2237: oe_debug_pub.add('Line end date : '||p_line_rec.end_date_active);
2238: oe_debug_pub.add('Header end date : '||g_header_rec.end_date_active);
2231:
2232: if l_debug_level > 0 then
2233: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate ENTITY - Line',1);
2234: oe_debug_pub.add('Operation : '||p_line_rec.operation);
2235: oe_debug_pub.add('Price List ID : '||p_line_rec.price_list_id);
2236: oe_debug_pub.add('Unit List Price : '||p_line_rec.unit_list_price);
2237: oe_debug_pub.add('Line end date : '||p_line_rec.end_date_active);
2238: oe_debug_pub.add('Header end date : '||g_header_rec.end_date_active);
2239: end if;
2232: if l_debug_level > 0 then
2233: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate ENTITY - Line',1);
2234: oe_debug_pub.add('Operation : '||p_line_rec.operation);
2235: oe_debug_pub.add('Price List ID : '||p_line_rec.price_list_id);
2236: oe_debug_pub.add('Unit List Price : '||p_line_rec.unit_list_price);
2237: oe_debug_pub.add('Line end date : '||p_line_rec.end_date_active);
2238: oe_debug_pub.add('Header end date : '||g_header_rec.end_date_active);
2239: end if;
2240:
2233: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate ENTITY - Line',1);
2234: oe_debug_pub.add('Operation : '||p_line_rec.operation);
2235: oe_debug_pub.add('Price List ID : '||p_line_rec.price_list_id);
2236: oe_debug_pub.add('Unit List Price : '||p_line_rec.unit_list_price);
2237: oe_debug_pub.add('Line end date : '||p_line_rec.end_date_active);
2238: oe_debug_pub.add('Header end date : '||g_header_rec.end_date_active);
2239: end if;
2240:
2241: -- Load old line rec if not passed
2234: oe_debug_pub.add('Operation : '||p_line_rec.operation);
2235: oe_debug_pub.add('Price List ID : '||p_line_rec.price_list_id);
2236: oe_debug_pub.add('Unit List Price : '||p_line_rec.unit_list_price);
2237: oe_debug_pub.add('Line end date : '||p_line_rec.end_date_active);
2238: oe_debug_pub.add('Header end date : '||g_header_rec.end_date_active);
2239: end if;
2240:
2241: -- Load old line rec if not passed
2242: IF p_line_rec.operation = oe_globals.g_opr_update THEN
2282: -- Check required attributes.
2283: -----------------------------------------------------------
2284:
2285: if l_debug_level > 0 then
2286: oe_debug_pub.add('Blanket In validate entity 1');
2287: oe_debug_pub.add('1 '||x_return_status, 1);
2288: end if;
2289:
2290: IF p_line_rec.line_id IS NULL
2283: -----------------------------------------------------------
2284:
2285: if l_debug_level > 0 then
2286: oe_debug_pub.add('Blanket In validate entity 1');
2287: oe_debug_pub.add('1 '||x_return_status, 1);
2288: end if;
2289:
2290: IF p_line_rec.line_id IS NULL
2291: THEN
2341:
2342: END IF;
2343:
2344: if l_debug_level > 0 then
2345: oe_debug_pub.add('Blanket In validate entity 2');
2346: end if;
2347:
2348: -- Customer Item Validation
2349: IF (p_line_rec.operation = oe_globals.g_opr_update or p_line_rec.operation = oe_globals.g_opr_create) AND
2351: oe_blanket_util.Get_Inventory_Item(p_x_line_rec => p_line_rec,
2352: x_return_status => l_return_status);
2353: END if;
2354: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2355: oe_debug_pub.add('Raise and Error in Customer ITem Validation oe_blanket_util.Get_Inventory_Item');
2356: RAISE FND_API.G_EXC_ERROR;
2357: END IF;
2358: --PP Revenue Recognition
2359: --bug 4893057
2461:
2462: END IF;
2463:
2464: if l_debug_level > 0 then
2465: oe_debug_pub.add('before logging request for item uniqueness');
2466: end if;
2467:
2468: --validate item uniqueness
2469: OE_Delayed_Requests_Pvt.Log_Request(p_Entity_Code =>
2498: -- ER 5743580
2499: IF ( NOT Validate_Ship_To_Org(p_line_rec.ship_to_org_id,
2500: p_line_rec.sold_to_org_id )) THEN
2501: if l_debug_level > 0 then
2502: oe_debug_pub.add ('Blanket In: No data found',2);
2503: end if;
2504: x_return_status := FND_API.G_RET_STS_ERROR;
2505: fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2506: FND_MESSAGE.SET_TOKEN('ATTRIBUTE'
2510: END IF; -- Ship to
2511:
2512: -- Deliver to Org id depends on sold to org.
2513: if l_debug_level > 0 then
2514: oe_debug_pub.add('Validating deliver_to_org_id :'|| to_char(p_line_rec.deliver_to_org_id),2);
2515: oe_debug_pub.add(' Customer Relations :'|| g_customer_relations, 1);
2516: end if;
2517: IF p_line_rec.deliver_to_org_id IS NOT NULL AND
2518: ( NOT OE_GLOBALS.EQUAL(p_line_rec.deliver_to_org_id,l_old_line_rec.deliver_to_org_id) OR
2511:
2512: -- Deliver to Org id depends on sold to org.
2513: if l_debug_level > 0 then
2514: oe_debug_pub.add('Validating deliver_to_org_id :'|| to_char(p_line_rec.deliver_to_org_id),2);
2515: oe_debug_pub.add(' Customer Relations :'|| g_customer_relations, 1);
2516: end if;
2517: IF p_line_rec.deliver_to_org_id IS NOT NULL AND
2518: ( NOT OE_GLOBALS.EQUAL(p_line_rec.deliver_to_org_id,l_old_line_rec.deliver_to_org_id) OR
2519: NOT OE_GLOBALS.EQUAL(p_line_rec.sold_to_org_id ,l_old_line_rec.sold_to_org_id)) THEN
2546: END IF;
2547: END IF; -- Invoice to org
2548:
2549: if l_debug_level > 0 then
2550: oe_debug_pub.add('Blanket In validate entity 3');
2551: end if;
2552:
2553: -- Date Validations
2554: --Changes made for Bug No 5528599 start
2559: AND p_line_rec.operation = OE_GLOBALS.G_OPR_CREATE)
2560: then
2561: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
2562: OE_MSG_PUB.ADD;
2563: oe_debug_pub.add('1: Not Setting Error',1);
2564:
2565: else
2566: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
2567: OE_MSG_PUB.ADD;
2564:
2565: else
2566: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
2567: OE_MSG_PUB.ADD;
2568: oe_debug_pub.add('1: Setting Error',1);
2569: x_return_status := FND_API.G_RET_STS_ERROR;
2570: end if;
2571: --Changes made for Bug No 5528599
2572: end if;
2592: end if;
2593: end if;
2594:
2595: if l_debug_level > 0 then
2596: oe_debug_pub.add('Blanket In validate entity 4, status :'||
2597: x_return_status);
2598: end if;
2599:
2600: -- pricing uom is required if it is a blanket price list
2676:
2677: IF p_line_rec.operation = oe_globals.g_opr_update THEN
2678:
2679: if l_debug_level > 0 then
2680: oe_debug_pub.add('Blanket In validate entity 4: update');
2681: end if;
2682:
2683: --not allowed to change any attributes on a previously closed blanket
2684: IF p_line_rec.end_date_active < trunc(sysdate) and
2698: IF nvl(p_line_rec.released_amount,0) < p_line_rec.blanket_min_amount THEN
2699: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_CLOSE_MIN_VALUES');
2700: OE_MSG_PUB.ADD;
2701: if l_debug_level > 0 then
2702: oe_debug_pub.add('Blanket line close min amount ');
2703: end if;
2704:
2705: ELSIF nvl(p_line_rec.released_quantity,0) < p_line_rec.blanket_min_quantity THEN
2706: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_CLOSE_MIN_VALUES');
2705: ELSIF nvl(p_line_rec.released_quantity,0) < p_line_rec.blanket_min_quantity THEN
2706: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_CLOSE_MIN_VALUES');
2707: OE_MSG_PUB.ADD;
2708: if l_debug_level > 0 then
2709: oe_debug_pub.add('Blanket line close min quantity ');
2710: end if;
2711: END IF;
2712: END IF;
2713:
2776: -- always post error even if user was updating some other field
2777: -- on the blanket line.
2778: -- Therefore commenting out NOCOPY {file.sql.39 change} the check, logging of price list
2779: -- line creation is anyway restricted to line CREATE operation.
2780: oe_debug_pub.add('old pricing uom :'||l_old_line_rec.pricing_uom);
2781: oe_debug_pub.add('new pricing uom :'||p_line_rec.pricing_uom);
2782: --pricing uom not allowed to change if blanket price list
2783: if NOT OE_GLOBALS.EQUAL(p_line_rec.pricing_uom,
2784: l_old_line_rec.pricing_uom) THEN
2777: -- on the blanket line.
2778: -- Therefore commenting out NOCOPY {file.sql.39 change} the check, logging of price list
2779: -- line creation is anyway restricted to line CREATE operation.
2780: oe_debug_pub.add('old pricing uom :'||l_old_line_rec.pricing_uom);
2781: oe_debug_pub.add('new pricing uom :'||p_line_rec.pricing_uom);
2782: --pricing uom not allowed to change if blanket price list
2783: if NOT OE_GLOBALS.EQUAL(p_line_rec.pricing_uom,
2784: l_old_line_rec.pricing_uom) THEN
2785: if (g_new_price_list) THEN
2801: end if;
2802: */
2803:
2804: if l_debug_level > 0 then
2805: oe_debug_pub.add('Blanket In validate entity update 1');
2806: end if;
2807: if NOT OE_GLOBALS.EQUAL(p_line_rec.released_amount,
2808: l_old_line_rec.released_amount) THEN
2809: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_UPDATE_LINE_ATTRIBUTE');
2851: OE_MSG_PUB.ADD;
2852: x_return_status := FND_API.G_RET_STS_ERROR;
2853: end if;
2854: if l_debug_level > 0 then
2855: oe_debug_pub.add('Blanket In validate entity update 2');
2856: end if;
2857:
2858: END IF; --if g_opr_update
2859:
2866: if (NOT OE_GLOBALS.EQUAL(trunc(p_line_rec.end_date_active),
2867: trunc(l_old_line_rec.end_date_active))
2868: AND trunc(p_line_rec.end_date_active) < trunc(sysdate))
2869: THEN
2870: oe_debug_pub.add('2:source_document_type_id :'|| p_line_rec.source_document_type_id,1);
2871: oe_debug_pub.add('2:source_document_line_id :'|| p_line_rec.source_document_line_id,1);
2872:
2873: if ( nvl(p_line_rec.source_document_type_id,0) = 2 --bug#5528507
2874: AND p_line_rec.operation = OE_GLOBALS.G_OPR_CREATE)
2867: trunc(l_old_line_rec.end_date_active))
2868: AND trunc(p_line_rec.end_date_active) < trunc(sysdate))
2869: THEN
2870: oe_debug_pub.add('2:source_document_type_id :'|| p_line_rec.source_document_type_id,1);
2871: oe_debug_pub.add('2:source_document_line_id :'|| p_line_rec.source_document_line_id,1);
2872:
2873: if ( nvl(p_line_rec.source_document_type_id,0) = 2 --bug#5528507
2874: AND p_line_rec.operation = OE_GLOBALS.G_OPR_CREATE)
2875: then
2874: AND p_line_rec.operation = OE_GLOBALS.G_OPR_CREATE)
2875: then
2876: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
2877: OE_MSG_PUB.ADD;
2878: oe_debug_pub.add('2: Not Setting Error',1);
2879:
2880: else
2881: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
2882: OE_MSG_PUB.ADD;
2880: else
2881: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
2882: OE_MSG_PUB.ADD;
2883: x_return_status := FND_API.G_RET_STS_ERROR;
2884: oe_debug_pub.add('2: Setting Error',1);
2885: end if;
2886: end if;
2887: --Changes made for Bug No 5528599
2888: -- End Expiration Date < sysdate check
2906: IF (g_new_price_list AND p_line_rec.price_list_id IS NULL)
2907: THEN
2908:
2909: if l_debug_level > 0 then
2910: oe_debug_pub.add('log CREATE_BLANKET_PRICE_LIST');
2911: end if;
2912:
2913: --for bug 3229225.Commented out NOCOPY /* file.sql.39 change */ the If condition and added modified condition
2914: -- IF p_line_rec.unit_list_price IS NOT NULL
2953: OR OE_Code_Control.Get_Code_Release_Level >= '110510'
2954: THEN
2955:
2956: if l_debug_level > 0 then
2957: oe_debug_pub.add('qp list line id :'||p_line_rec.qp_list_line_id);
2958: end if;
2959:
2960:
2961: --for bug 3229225.Commented out NOCOPY /* file.sql.39 change */ the If condition and added modified condition
2988:
2989: if l_list_line_exists = 'N' THEN
2990:
2991: if l_debug_level > 0 then
2992: oe_debug_pub.add('log ADD_BLANKET_PRICE_LIST_LINE');
2993: end if;
2994:
2995: oe_delayed_requests_pvt.Log_request(p_Entity_Code =>
2996: OE_BLANKET_pub.G_ENTITY_BLANKET_LINE,
3036: ,p_blanket_header_id => p_line_rec.header_id))
3037: AND (p_line_rec.qp_list_line_id is not null))
3038: THEN
3039: if l_debug_level > 0 then
3040: oe_debug_pub.add('qp list line id :'||p_line_rec.qp_list_line_id);
3041: oe_debug_pub.add('Log delayed request: CLEAR_BLANKET_PRICE_LIST_LINE');
3042: end if;
3043:
3044: oe_delayed_requests_pvt.Log_request(p_Entity_Code =>
3037: AND (p_line_rec.qp_list_line_id is not null))
3038: THEN
3039: if l_debug_level > 0 then
3040: oe_debug_pub.add('qp list line id :'||p_line_rec.qp_list_line_id);
3041: oe_debug_pub.add('Log delayed request: CLEAR_BLANKET_PRICE_LIST_LINE');
3042: end if;
3043:
3044: oe_delayed_requests_pvt.Log_request(p_Entity_Code =>
3045: OE_BLANKET_pub.G_ENTITY_BLANKET_LINE,
3067: THEN
3068:
3069: IF p_line_rec.modifier_list_line_id IS NOT NULL
3070: THEN
3071: oe_debug_pub.add('modifier list line id is not null');
3072: fnd_message.set_name('ONT','OE_BLKT_CANNOT_UPDATE_DIS');
3073: oe_msg_pub.add;
3074: x_return_status := FND_API.G_RET_STS_ERROR;
3075: ELSIF g_header_rec.new_modifier_list_name IS NULL
3110: END IF; --bug 3443777, need to call validate entity for reasons capture
3111:
3112: if l_debug_level > 0 then
3113: -- Done validating entity
3114: oe_debug_pub.add('Exit OE_BLANKET_UTIL.Validate ENTITY- Line',1);
3115: end if;
3116:
3117: EXCEPTION
3118:
3151: l_list_header_id NUMBER;
3152: l_new_pl_name VARCHAR2(240);
3153: l_site_use_code VARCHAR2(30);
3154: --
3155: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3156: --
3157: BEGIN
3158: x_return_status := FND_API.G_RET_STS_SUCCESS;
3159:
3157: BEGIN
3158: x_return_status := FND_API.G_RET_STS_SUCCESS;
3159:
3160: if l_debug_level > 0 then
3161: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate_ENTITY- Header',1);
3162: oe_debug_pub.add('Operation :'||p_header_rec.operation);
3163: oe_debug_pub.add('new end date :'||p_header_rec.end_date_active);
3164: oe_debug_pub.add('old end date :'||l_old_header_rec.end_date_active);
3165: end if;
3158: x_return_status := FND_API.G_RET_STS_SUCCESS;
3159:
3160: if l_debug_level > 0 then
3161: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate_ENTITY- Header',1);
3162: oe_debug_pub.add('Operation :'||p_header_rec.operation);
3163: oe_debug_pub.add('new end date :'||p_header_rec.end_date_active);
3164: oe_debug_pub.add('old end date :'||l_old_header_rec.end_date_active);
3165: end if;
3166:
3159:
3160: if l_debug_level > 0 then
3161: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate_ENTITY- Header',1);
3162: oe_debug_pub.add('Operation :'||p_header_rec.operation);
3163: oe_debug_pub.add('new end date :'||p_header_rec.end_date_active);
3164: oe_debug_pub.add('old end date :'||l_old_header_rec.end_date_active);
3165: end if;
3166:
3167: IF p_header_rec.operation = oe_globals.g_opr_update THEN
3160: if l_debug_level > 0 then
3161: oe_debug_pub.add('Enter OE_BLANKET_UTIL.Validate_ENTITY- Header',1);
3162: oe_debug_pub.add('Operation :'||p_header_rec.operation);
3163: oe_debug_pub.add('new end date :'||p_header_rec.end_date_active);
3164: oe_debug_pub.add('old end date :'||l_old_header_rec.end_date_active);
3165: end if;
3166:
3167: IF p_header_rec.operation = oe_globals.g_opr_update THEN
3168: if l_old_header_rec.header_id is null THEN
3300: AND p_header_rec.operation = OE_GLOBALS.G_OPR_CREATE
3301: then
3302: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
3303: OE_MSG_PUB.ADD;
3304: oe_debug_pub.add('3: Not Setting Error',1);
3305:
3306: else
3307: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
3308: OE_MSG_PUB.ADD;
3306: else
3307: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_DATE_RANGE');
3308: OE_MSG_PUB.ADD;
3309: x_return_status := FND_API.G_RET_STS_ERROR;
3310: oe_debug_pub.add('3: Setting Error',1);
3311: end if;
3312: end if;
3313: --Changes made for Bug No 5528599 start
3314: -- End Expiration Date < sysdate check
3320: -- ER 5743580
3321: IF ( NOT Validate_Ship_To_Org(p_header_rec.ship_to_org_id,
3322: p_header_rec.sold_to_org_id )) THEN
3323: if l_debug_level > 0 then
3324: oe_debug_pub.add ('Blanket In: No data found',2);
3325: end if;
3326: x_return_status := FND_API.G_RET_STS_ERROR;
3327: fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3328: FND_MESSAGE.SET_TOKEN('ATTRIBUTE'
3390:
3391: -- hashraf end of pack J
3392:
3393: if l_debug_level > 0 then
3394: oe_debug_pub.add('Validating deliver_to_org_id :'|| to_char(p_header_rec.deliver_to_org_id),2);
3395: oe_debug_pub.add(' Customer Relations :'|| g_customer_relations, 1);
3396: end if;
3397: IF p_header_rec.deliver_to_org_id IS NOT NULL AND
3398: ( NOT OE_GLOBALS.EQUAL(p_header_rec.deliver_to_org_id,l_old_header_rec.deliver_to_org_id) OR
3391: -- hashraf end of pack J
3392:
3393: if l_debug_level > 0 then
3394: oe_debug_pub.add('Validating deliver_to_org_id :'|| to_char(p_header_rec.deliver_to_org_id),2);
3395: oe_debug_pub.add(' Customer Relations :'|| g_customer_relations, 1);
3396: end if;
3397: IF p_header_rec.deliver_to_org_id IS NOT NULL AND
3398: ( NOT OE_GLOBALS.EQUAL(p_header_rec.deliver_to_org_id,l_old_header_rec.deliver_to_org_id) OR
3399: NOT OE_GLOBALS.EQUAL(p_header_rec.sold_to_org_id ,l_old_header_rec.sold_to_org_id)) THEN
3472: IF p_header_rec.price_list_id IS NULL AND
3473: p_header_rec.price_list_name IS NOT NULL THEN
3474: BEGIN
3475: if l_debug_level > 0 then
3476: oe_debug_pub.add('Derive price_list_id',1);
3477: end if;
3478: --
3479: SELECT list_header_id
3480: INTO p_header_rec.price_list_id
3487: --
3488: EXCEPTION
3489: WHEN NO_DATA_FOUND THEN
3490: if l_debug_level > 0 then
3491: oe_debug_pub.add('New price list needs to be created',1);
3492: end if;
3493: g_new_price_list := true;
3494: END;
3495: END IF;
3500: -- price list and existing price list ID will store the standard price
3501: -- list.
3502:
3503: if l_debug_level > 0 then
3504: oe_debug_pub.add('pl name :'||p_header_rec.new_price_list_name);
3505: oe_debug_pub.add('old pl name :'||p_old_header_rec.new_price_list_name);
3506: oe_debug_pub.add('pl id :'||p_header_rec.new_price_list_id);
3507: oe_debug_pub.add('old pl id :'||p_old_header_rec.new_price_list_id);
3508: oe_debug_pub.add('ml name :'||p_header_rec.new_modifier_list_name);
3501: -- list.
3502:
3503: if l_debug_level > 0 then
3504: oe_debug_pub.add('pl name :'||p_header_rec.new_price_list_name);
3505: oe_debug_pub.add('old pl name :'||p_old_header_rec.new_price_list_name);
3506: oe_debug_pub.add('pl id :'||p_header_rec.new_price_list_id);
3507: oe_debug_pub.add('old pl id :'||p_old_header_rec.new_price_list_id);
3508: oe_debug_pub.add('ml name :'||p_header_rec.new_modifier_list_name);
3509: oe_debug_pub.add('old ml name :'||p_old_header_rec.new_modifier_list_name);
3502:
3503: if l_debug_level > 0 then
3504: oe_debug_pub.add('pl name :'||p_header_rec.new_price_list_name);
3505: oe_debug_pub.add('old pl name :'||p_old_header_rec.new_price_list_name);
3506: oe_debug_pub.add('pl id :'||p_header_rec.new_price_list_id);
3507: oe_debug_pub.add('old pl id :'||p_old_header_rec.new_price_list_id);
3508: oe_debug_pub.add('ml name :'||p_header_rec.new_modifier_list_name);
3509: oe_debug_pub.add('old ml name :'||p_old_header_rec.new_modifier_list_name);
3510: oe_debug_pub.add('ml id :'||p_header_rec.new_modifier_list_id);
3503: if l_debug_level > 0 then
3504: oe_debug_pub.add('pl name :'||p_header_rec.new_price_list_name);
3505: oe_debug_pub.add('old pl name :'||p_old_header_rec.new_price_list_name);
3506: oe_debug_pub.add('pl id :'||p_header_rec.new_price_list_id);
3507: oe_debug_pub.add('old pl id :'||p_old_header_rec.new_price_list_id);
3508: oe_debug_pub.add('ml name :'||p_header_rec.new_modifier_list_name);
3509: oe_debug_pub.add('old ml name :'||p_old_header_rec.new_modifier_list_name);
3510: oe_debug_pub.add('ml id :'||p_header_rec.new_modifier_list_id);
3511: oe_debug_pub.add('old ml id :'||p_old_header_rec.new_modifier_list_id);
3504: oe_debug_pub.add('pl name :'||p_header_rec.new_price_list_name);
3505: oe_debug_pub.add('old pl name :'||p_old_header_rec.new_price_list_name);
3506: oe_debug_pub.add('pl id :'||p_header_rec.new_price_list_id);
3507: oe_debug_pub.add('old pl id :'||p_old_header_rec.new_price_list_id);
3508: oe_debug_pub.add('ml name :'||p_header_rec.new_modifier_list_name);
3509: oe_debug_pub.add('old ml name :'||p_old_header_rec.new_modifier_list_name);
3510: oe_debug_pub.add('ml id :'||p_header_rec.new_modifier_list_id);
3511: oe_debug_pub.add('old ml id :'||p_old_header_rec.new_modifier_list_id);
3512: end if;
3505: oe_debug_pub.add('old pl name :'||p_old_header_rec.new_price_list_name);
3506: oe_debug_pub.add('pl id :'||p_header_rec.new_price_list_id);
3507: oe_debug_pub.add('old pl id :'||p_old_header_rec.new_price_list_id);
3508: oe_debug_pub.add('ml name :'||p_header_rec.new_modifier_list_name);
3509: oe_debug_pub.add('old ml name :'||p_old_header_rec.new_modifier_list_name);
3510: oe_debug_pub.add('ml id :'||p_header_rec.new_modifier_list_id);
3511: oe_debug_pub.add('old ml id :'||p_old_header_rec.new_modifier_list_id);
3512: end if;
3513:
3506: oe_debug_pub.add('pl id :'||p_header_rec.new_price_list_id);
3507: oe_debug_pub.add('old pl id :'||p_old_header_rec.new_price_list_id);
3508: oe_debug_pub.add('ml name :'||p_header_rec.new_modifier_list_name);
3509: oe_debug_pub.add('old ml name :'||p_old_header_rec.new_modifier_list_name);
3510: oe_debug_pub.add('ml id :'||p_header_rec.new_modifier_list_id);
3511: oe_debug_pub.add('old ml id :'||p_old_header_rec.new_modifier_list_id);
3512: end if;
3513:
3514: IF NOT OE_GLOBALS.EQUAL(p_header_rec.new_price_list_id,
3507: oe_debug_pub.add('old pl id :'||p_old_header_rec.new_price_list_id);
3508: oe_debug_pub.add('ml name :'||p_header_rec.new_modifier_list_name);
3509: oe_debug_pub.add('old ml name :'||p_old_header_rec.new_modifier_list_name);
3510: oe_debug_pub.add('ml id :'||p_header_rec.new_modifier_list_id);
3511: oe_debug_pub.add('old ml id :'||p_old_header_rec.new_modifier_list_id);
3512: end if;
3513:
3514: IF NOT OE_GLOBALS.EQUAL(p_header_rec.new_price_list_id,
3515: p_old_header_rec.new_price_list_id)
3566: ,p_old_header_rec.new_modifier_list_name
3567: )
3568: THEN
3569:
3570: oe_debug_pub.add('check if ml exists');
3571:
3572: BEGIN
3573:
3574: -- Check if name already exists
3632: IF NOT OE_GLOBALS.Equal(p_header_rec.end_date_active, l_old_header_rec.end_date_active) AND
3633: p_header_rec.end_date_active <= trunc(sysdate) AND
3634: nvl(p_header_rec.released_amount,0) < p_header_rec.blanket_min_amount THEN
3635: if l_debug_level > 0 then
3636: oe_debug_pub.add('Blanket header close min amount ');
3637: end if;
3638: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_CLOSE_MIN_VALUES');
3639: OE_MSG_PUB.ADD;
3640: END IF;
3858: FROM OE_BLANKET_HEADERS_ALL
3859: WHERE HEADER_ID = p_header_rec.header_id;
3860:
3861: if l_debug_level > 0 then
3862: oe_debug_pub.add('new version :'||p_header_rec.version_number);
3863: oe_debug_pub.add('old version :'||l_old_header_rec.version_number);
3864: oe_debug_pub.add('DB version :'||l_db_version_number);
3865: end if;
3866:
3859: WHERE HEADER_ID = p_header_rec.header_id;
3860:
3861: if l_debug_level > 0 then
3862: oe_debug_pub.add('new version :'||p_header_rec.version_number);
3863: oe_debug_pub.add('old version :'||l_old_header_rec.version_number);
3864: oe_debug_pub.add('DB version :'||l_db_version_number);
3865: end if;
3866:
3867: IF p_header_rec.version_number
3860:
3861: if l_debug_level > 0 then
3862: oe_debug_pub.add('new version :'||p_header_rec.version_number);
3863: oe_debug_pub.add('old version :'||l_old_header_rec.version_number);
3864: oe_debug_pub.add('DB version :'||l_db_version_number);
3865: end if;
3866:
3867: IF p_header_rec.version_number
3868: <= l_old_header_rec.version_number
3978: END IF; -- For bug #4447494
3979:
3980: IF g_new_price_list THEN
3981: if l_debug_level > 0 then
3982: oe_debug_pub.add('logging request to create blanket price list',1);
3983: end if;
3984: if oe_code_control.get_code_release_level >= '110510' then
3985: l_new_pl_name := p_header_rec.new_price_list_name;
3986: else
4026:
4027: g_header_rec := p_header_rec;
4028:
4029: if l_debug_level > 0 then
4030: oe_debug_pub.add('Exit OE_BLANKET_UTIL.Validate_ENTITY- Header',1);
4031: end if;
4032:
4033: EXCEPTION
4034: WHEN FND_API.G_EXC_ERROR THEN
4049: l_org_id NUMBER;
4050: l_upgraded_flag varchar2(1) ;
4051: l_lock_control NUMBER := 1;
4052: --
4053: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4054: --
4055: BEGIN
4056:
4057: x_return_status := FND_API.G_RET_STS_SUCCESS;
4056:
4057: x_return_status := FND_API.G_RET_STS_SUCCESS;
4058:
4059: if l_debug_level > 0 then
4060: oe_debug_pub.add('Entering OE_BLANKET_UTIL.INSERT_ROW - Header ID :'||
4061: p_header_rec.header_id);
4062: end if;
4063: --bug 7139169
4064: OE_GLOBALS.Set_Context;
4273: );
4274:
4275: OE_Blanket_Header_Security.g_check_all_cols_constraint := 'Y'; /* Bug # 5516348 */
4276: if l_debug_level > 0 then
4277: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.INSERT_ROW- Header', 1);
4278: end if;
4279:
4280: EXCEPTION
4281:
4300: )
4301: IS
4302: l_return_status VARCHAR2(1);
4303: --
4304: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4305: --
4306: BEGIN
4307:
4308: x_return_status := FND_API.G_RET_STS_SUCCESS;
4430: AND END_DATE_ACTIVE IS NULL;
4431:
4432: OE_Blanket_Header_Security.g_check_all_cols_constraint := 'Y'; /* Bug # 5516348 */
4433: if l_debug_level > 0 then
4434: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.UPDATE_ROW', 1);
4435: end if;
4436:
4437: EXCEPTION
4438:
4457:
4458: IS
4459: --
4460: l_transaction_phase_code VARCHAR2(30);
4461: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4462: l_return_status VARCHAR2(1);
4463: -- added for delete articles
4464: l_msg_count NUMBER;
4465: l_msg_data VARCHAR2(2000);
4477: -- hashraf ... start of pack J
4478: x_return_status := FND_API.G_RET_STS_SUCCESS;
4479:
4480: if l_debug_level > 0 then
4481: oe_debug_pub.add('Entering OE_BLANKET_UTIL.DELETE_ROW - HEADER ID :'||
4482: p_header_id);
4483: end if;
4484:
4485: BEGIN
4503:
4504: IF l_transaction_phase_code IS NOT NULL THEN
4505:
4506: IF l_transaction_phase_code = 'N' THEN
4507: oe_debug_pub.add('OE_BLANKET_UTIL.Delete_Row WF negotiate');
4508: OE_Order_WF_Util.delete_row(p_type=>'NEGOTIATE', p_id => p_header_id);
4509: ELSE
4510: oe_debug_pub.add('OE_BLANKET_UTIL.Delete_Row WF blanket');
4511: OE_Order_WF_Util.delete_row(p_type=>'BLANKET', p_id => p_header_id);
4506: IF l_transaction_phase_code = 'N' THEN
4507: oe_debug_pub.add('OE_BLANKET_UTIL.Delete_Row WF negotiate');
4508: OE_Order_WF_Util.delete_row(p_type=>'NEGOTIATE', p_id => p_header_id);
4509: ELSE
4510: oe_debug_pub.add('OE_BLANKET_UTIL.Delete_Row WF blanket');
4511: OE_Order_WF_Util.delete_row(p_type=>'BLANKET', p_id => p_header_id);
4512: END IF;
4513:
4514: END IF;
4511: OE_Order_WF_Util.delete_row(p_type=>'BLANKET', p_id => p_header_id);
4512: END IF;
4513:
4514: END IF;
4515: oe_debug_pub.add('OE_BLANKET_UTIL.Delete_Row after WF delete');
4516:
4517: -- 11i10 Pricing Changes
4518: OE_Blanket_Pricing_Util.Deactivate_Pricing
4519: (p_blanket_header_id => p_header_id
4563: DELETE FROM OE_BLANKET_HEADERS_EXT
4564: WHERE order_number=l_order_number;
4565:
4566: if l_debug_level > 0 then
4567: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.DELETE_ROW', 1);
4568: end if;
4569:
4570: EXCEPTION
4571: WHEN FND_API.G_EXC_ERROR THEN
4609: l_lock_control NUMBER:= 1;
4610: l_index NUMBER;
4611: l_return_status varchar2(1);
4612: --
4613: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4614: --
4615: BEGIN
4616:
4617: x_return_status := FND_API.G_RET_STS_SUCCESS;
4616:
4617: x_return_status := FND_API.G_RET_STS_SUCCESS;
4618:
4619: if l_debug_level > 0 then
4620: oe_debug_pub.add('Entering OE_BLANKET_UTIL.INSERT_ROW - Line ID :'||
4621: p_line_rec.line_id);
4622: end if;
4623:
4624: --Versioning changes
4840: );
4841:
4842: OE_Blanket_Line_Security.g_check_all_cols_constraint := 'Y'; /* Bug # 5516348 */
4843: if l_debug_level > 0 then
4844: oe_debug_pub.add('Exiting OE_blanket_UTIL.INSERT_ROW', 1);
4845: end if;
4846:
4847: EXCEPTION
4848:
4867: , x_return_status OUT NOCOPY VARCHAR2)
4868: IS
4869: l_return_status VARCHAR2(1);
4870: --
4871: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4872: --
4873: BEGIN
4874: if l_debug_level > 0 then
4875: oe_debug_pub.add('Entering OE_Blanket_UTIL.UPDATE_ROW - LINE', 1);
4871: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4872: --
4873: BEGIN
4874: if l_debug_level > 0 then
4875: oe_debug_pub.add('Entering OE_Blanket_UTIL.UPDATE_ROW - LINE', 1);
4876: end if;
4877: x_return_status := FND_API.G_RET_STS_SUCCESS;
4878:
4879: --Versioning changes
4991: WHERE line_id = p_line_rec.line_id;
4992:
4993: OE_Blanket_Line_Security.g_check_all_cols_constraint := 'Y'; /* Bug # 5516348 */
4994: if l_debug_level > 0 then
4995: oe_debug_pub.add('Exiting OE_Blanket_UTIL.UPDATE_ROW - LINE', 1);
4996: end if;
4997:
4998: EXCEPTION
4999:
5019: IS
5020: l_header_id NUMBER := g_header_rec.header_id;
5021: l_return_status varchar2(1);
5022: --
5023: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5024: --
5025: BEGIN
5026: -- hashraf ... start of pack J
5027: x_return_status := FND_API.G_RET_STS_SUCCESS;
5026: -- hashraf ... start of pack J
5027: x_return_status := FND_API.G_RET_STS_SUCCESS;
5028:
5029: if l_debug_level > 0 then
5030: oe_debug_pub.add('Entering OE_BLANKET_UTIL.DELETE_ROW - LINE ID :'||
5031: p_line_id);
5032: end if;
5033:
5034: IF l_header_id IS NULL or
5085: DELETE FROM OE_BLANKET_LINES_HIST
5086: WHERE line_id = p_line_id;
5087:
5088: if l_debug_level > 0 then
5089: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.DELETE_ROW', 1);
5090: end if;
5091:
5092: EXCEPTION
5093: WHEN FND_API.G_EXC_ERROR THEN
5315: l_org_id NUMBER;
5316: l_current_version_number NUMBER := NULL;
5317:
5318: --
5319: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5320: --
5321: BEGIN
5322:
5323: x_return_status := FND_API.G_RET_STS_SUCCESS;
5322:
5323: x_return_status := FND_API.G_RET_STS_SUCCESS;
5324:
5325: if l_debug_level > 0 then
5326: oe_debug_pub.add('Entering OE_BLANKET_UTIL.QUERY_ROW', 1);
5327: end if;
5328:
5329: IF p_version_number IS NOT NULL THEN
5330: SELECT /* MOAC_SQL_CHANGE */ version_number INTO l_current_version_number FROM OE_BLANKET_HEADERS_ALL WHERE header_id = p_header_id;
5550: CLOSE l_hdr_hist_csr;
5551: END IF;
5552:
5553: if l_debug_level > 0 then
5554: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.QUERY_ROW -Header', 1);
5555: end if;
5556:
5557: EXCEPTION
5558:
5782: AND (PHASE_CHANGE_FLAG = p_phase_change_flag
5783: OR (nvl(p_phase_change_flag, 'NULL') <> 'Y'));
5784:
5785: --
5786: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5787: --
5788: BEGIN
5789:
5790: x_return_status := FND_API.G_RET_STS_SUCCESS;
6104:
6105:
6106: -- Return fetched table
6107: if l_debug_level > 0 then
6108: oe_debug_pub.add('Exiting OE_LINE_UTIL.QUERY_ROWS', 1);
6109: end if;
6110:
6111: EXCEPTION
6112:
6145: l_return_status varchar2(1);
6146: l_version_number NUMBER := p_version_number;
6147: l_current_version_number NUMBER;
6148: --
6149: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6150: --
6151: BEGIN
6152:
6153: x_return_status := FND_API.G_RET_STS_SUCCESS;
6199: IS
6200: l_return_status varchar2(1);
6201: l_line_tbl OE_Blanket_PUB.Line_Tbl_Type;
6202: --
6203: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6204: --
6205: BEGIN
6206:
6207: Query_Lines
6228: )
6229: IS
6230: l_return_status VARCHAR2(1);
6231: --
6232: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6233: --
6234: BEGIN
6235:
6236: if l_debug_level > 0 then
6233: --
6234: BEGIN
6235:
6236: if l_debug_level > 0 then
6237: oe_debug_pub.add('Entering OE_BLANKET_UTIL.LOCK_ROW', 1);
6238: end if;
6239:
6240: OE_MSG_PUB.initialize;
6241:
6262: , p_data => x_msg_data
6263: );
6264:
6265: if l_debug_level > 0 then
6266: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.LOCK_ROW', 1);
6267: end if;
6268:
6269: EXCEPTION
6270: WHEN FND_API.G_EXC_ERROR THEN
6348: AND BL.SALES_DOCUMENT_TYPE_CODE = 'B'
6349: FOR UPDATE NOWAIT;
6350:
6351: --
6352: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6353: --
6354: BEGIN
6355:
6356:
6354: BEGIN
6355:
6356:
6357: if l_debug_level > 0 then
6358: oe_debug_pub.add('Entering OE_BLANKET_UTIL.LOCK_BLANKET', 1);
6359: end if;
6360:
6361: OE_MSG_PUB.initialize;
6362:
6401:
6402: -- p_x_lock_control := l_db_lock_control;
6403:
6404: if l_debug_level > 0 then
6405: oe_debug_pub.add('p_x_lock_control'||p_x_lock_control, 3);
6406: -- oe_debug_pub.add('is line lock control'||l_is_line_lock_control, 3);
6407: oe_debug_pub.add('l_db_lock_control'||l_db_lock_control, 3);
6408: end if;
6409:
6402: -- p_x_lock_control := l_db_lock_control;
6403:
6404: if l_debug_level > 0 then
6405: oe_debug_pub.add('p_x_lock_control'||p_x_lock_control, 3);
6406: -- oe_debug_pub.add('is line lock control'||l_is_line_lock_control, 3);
6407: oe_debug_pub.add('l_db_lock_control'||l_db_lock_control, 3);
6408: end if;
6409:
6410: if l_debug_level > 0 then
6403:
6404: if l_debug_level > 0 then
6405: oe_debug_pub.add('p_x_lock_control'||p_x_lock_control, 3);
6406: -- oe_debug_pub.add('is line lock control'||l_is_line_lock_control, 3);
6407: oe_debug_pub.add('l_db_lock_control'||l_db_lock_control, 3);
6408: end if;
6409:
6410: if l_debug_level > 0 then
6411: oe_debug_pub.add('selected for update, now compare', 3);
6407: oe_debug_pub.add('l_db_lock_control'||l_db_lock_control, 3);
6408: end if;
6409:
6410: if l_debug_level > 0 then
6411: oe_debug_pub.add('selected for update, now compare', 3);
6412: end if;
6413:
6414:
6415: -- If lock_control is not passed(is null or missing), then return the locked record.
6442: l_db_lock_control)
6443: THEN
6444:
6445: if l_debug_level > 0 then
6446: oe_debug_pub.add('done comparison, success', 1);
6447: end if;
6448: -- Row has not changed. Set out NOCOPY /* file.sql.39 change */ parameter.
6449:
6450: -- Set return status
6453:
6454: ELSE
6455:
6456: if l_debug_level > 0 then
6457: oe_debug_pub.add('row changed by other user', 1);
6458: end if;
6459:
6460: -- Row has changed by another user.
6461:
6468:
6469: END IF;
6470:
6471: if l_debug_level > 0 then
6472: oe_debug_pub.add('Exiting OE_BLANKET_UTIL.LOCK_BLANKET', 1);
6473: end if;
6474:
6475: OE_GLOBALS.G_LOCK_TEST := 'N';
6476:
6481:
6482: x_return_status := FND_API.G_RET_STS_ERROR;
6483:
6484: if l_debug_level > 0 then
6485: oe_debug_pub.add('no data found in blanket lock_blanket', 1);
6486: end if;
6487:
6488: fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
6489: OE_MSG_PUB.Add;
6494:
6495: x_return_status := FND_API.G_RET_STS_ERROR;
6496:
6497: if l_debug_level > 0 then
6498: oe_debug_pub.add('record_lock in blanket lock_blanket', 1);
6499: end if;
6500:
6501: fnd_message.set_name('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
6502: OE_MSG_PUB.Add;
6505:
6506: WHEN OTHERS THEN
6507:
6508: if l_debug_level > 0 then
6509: oe_debug_pub.add('others in blanket lock_blanket', 1);
6510: end if;
6511: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6512:
6513: OE_MSG_PUB.Add_Exc_Msg
6524: (p_x_header_rec IN OUT NOCOPY OE_Blanket_PUB.Header_rec_type
6525: ,x_return_status OUT NOCOPY VARCHAR2
6526: ) IS
6527: --
6528: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6529: --
6530:
6531: CURSOR c_contract_template_exist (cp_trans_type_id NUMBER) IS
6532: SELECT contract_template_id
6539: BEGIN
6540: x_return_status := FND_API.G_RET_STS_SUCCESS;
6541:
6542: if l_debug_level > 0 then
6543: oe_debug_pub.add('Enter Default_Attributes - Header');
6544: oe_debug_pub.add('operation is : '||p_x_header_rec.operation);
6545: end if;
6546:
6547: -- Default Blanket Header ID
6540: x_return_status := FND_API.G_RET_STS_SUCCESS;
6541:
6542: if l_debug_level > 0 then
6543: oe_debug_pub.add('Enter Default_Attributes - Header');
6544: oe_debug_pub.add('operation is : '||p_x_header_rec.operation);
6545: end if;
6546:
6547: -- Default Blanket Header ID
6548: IF p_x_header_rec.header_id IS NULL THEN
6703: ,x_return_status OUT NOCOPY VARCHAR2
6704: ) IS
6705: l_return_status varchar2(1);
6706: --
6707: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6708: --
6709: BEGIN
6710: x_return_status := FND_API.G_RET_STS_SUCCESS;
6711:
6758: AND ORGANIZATION_ID = g_header_rec.org_id;
6759: EXCEPTION
6760: WHEN NO_DATA_FOUND THEN
6761: if l_debug_level > 0 then
6762: oe_debug_pub.add('no inventory item exists for this organization');
6763: end if;
6764: END;
6765: END IF;
6766:
6919: , x_return_status OUT NOCOPY VARCHAR2)
6920: IS
6921: l_return_status varchar2(1);
6922: --
6923: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6924: --
6925: BEGIN
6926: IF g_header_rec.header_id is NULL
6927: OR g_header_rec.header_id <> p_header_id
6976: lcount NUMBER;
6977: x_result NUMBER;
6978:
6979: --
6980: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6981: --
6982: BEGIN
6983: if l_debug_level > 0 then
6984: oe_debug_pub.add('Entering OR_BLANKET_UTIL.Get_Order_Number',1);
6980: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6981: --
6982: BEGIN
6983: if l_debug_level > 0 then
6984: oe_debug_pub.add('Entering OR_BLANKET_UTIL.Get_Order_Number',1);
6985: end if;
6986:
6987: --x_header_rec := p_header_rec;
6988: x_return_status := FND_API.G_RET_STS_SUCCESS;
6992: IF p_x_header_rec.order_type_id IS NOT NULL THEN
6993: x_doc_category_code := p_x_header_rec.order_type_id;
6994:
6995: if l_debug_level > 0 then
6996: oe_debug_pub.ADD('before calling get_seq_info ', 2);
6997: oe_debug_pub.ADD('Category Code'||x_doc_category_code, 3);
6998: oe_debug_pub.ADD('Set of Books'||x_set_of_books_id, 3);
6999: end if;
7000: x_result := fnd_seqnum.get_seq_info(
6993: x_doc_category_code := p_x_header_rec.order_type_id;
6994:
6995: if l_debug_level > 0 then
6996: oe_debug_pub.ADD('before calling get_seq_info ', 2);
6997: oe_debug_pub.ADD('Category Code'||x_doc_category_code, 3);
6998: oe_debug_pub.ADD('Set of Books'||x_set_of_books_id, 3);
6999: end if;
7000: x_result := fnd_seqnum.get_seq_info(
7001: 660,
6994:
6995: if l_debug_level > 0 then
6996: oe_debug_pub.ADD('before calling get_seq_info ', 2);
6997: oe_debug_pub.ADD('Category Code'||x_doc_category_code, 3);
6998: oe_debug_pub.ADD('Set of Books'||x_set_of_books_id, 3);
6999: end if;
7000: x_result := fnd_seqnum.get_seq_info(
7001: 660,
7002: x_doc_category_code,
7013: X_Msg_Flag
7014: );
7015:
7016: if l_debug_level > 0 then
7017: oe_debug_pub.ADD('after calling get_seq_info ', 2);
7018: end if;
7019:
7020: IF x_result <> FND_SEQNUM.SEQSUCC THEN
7021: IF x_result = FND_SEQNUM.NOTUSED THEN
7044: RAISE FND_API.G_EXC_ERROR;
7045: END IF;
7046:
7047: if l_debug_level > 0 then
7048: oe_debug_pub.ADD('fndseqresult'||to_char(x_result), 2);
7049: oe_debug_pub.ADD('fndseqtype'||x_doc_sequence_value, 2);
7050: end if;
7051: p_x_header_rec.order_number := x_doc_sequence_value;
7052: --ELSIF (t = 'M') THEN
7045: END IF;
7046:
7047: if l_debug_level > 0 then
7048: oe_debug_pub.ADD('fndseqresult'||to_char(x_result), 2);
7049: oe_debug_pub.ADD('fndseqtype'||x_doc_sequence_value, 2);
7050: end if;
7051: p_x_header_rec.order_number := x_doc_sequence_value;
7052: --ELSIF (t = 'M') THEN
7053: ELSE
7077: END IF;
7078: END IF; -- Blanket category code not null
7079:
7080: if l_debug_level > 0 then
7081: oe_debug_pub.add('Exiting OR_BLANKET_UTIL.Get_Order_Number',1);
7082: end if;
7083:
7084: EXCEPTION
7085: WHEN FND_API.G_EXC_ERROR THEN
7084: EXCEPTION
7085: WHEN FND_API.G_EXC_ERROR THEN
7086: x_return_status := FND_API.G_RET_STS_ERROR;
7087: if l_debug_level > 0 then
7088: oe_debug_pub.ADD('Get blanket Number-Exp exception ', 1);
7089: end if;
7090: RAISE FND_API.G_EXC_ERROR;
7091:
7092:
7091:
7092:
7093: WHEN OTHERS THEN
7094: if l_debug_level > 0 then
7095: oe_debug_pub.ADD('Get blanket Number-exception ', 1);
7096: end if;
7097:
7098: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7099:
7114: lcount number ;
7115: I number;
7116: l_return_status varchar2(1);
7117: --
7118: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7119: --
7120:
7121: lx_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
7122: lx_msg_count NUMBER := 0;
7155:
7156:
7157: IF oe_code_control.get_code_release_level >= '110510' and
7158: G_Header_Rec.operation = OE_GLOBALS.G_OPR_CREATE THEN
7159: oe_debug_pub.ADD('Create and Start Flow srini : '||G_Header_Rec.operation) ;
7160: oe_blanket_wf_util.create_and_start_flow(
7161: p_header_id => G_header_rec.header_id,
7162: p_transaction_phase_code => G_header_rec.transaction_phase_code,
7163: p_blanket_number => G_header_rec.order_number,
7165: x_msg_count => lx_msg_count,
7166: x_msg_data => lx_msg_Data);
7167: IF lx_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7168: if l_debug_level > 0 then
7169: oe_debug_pub.ADD('OE_Blanket_Util.Process_Object unexp error in Create and Start flow ', 1);
7170: end if;
7171: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7172: ELSIF lx_return_status = FND_API.G_RET_STS_ERROR THEN
7173: if l_debug_level > 0 then
7170: end if;
7171: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7172: ELSIF lx_return_status = FND_API.G_RET_STS_ERROR THEN
7173: if l_debug_level > 0 then
7174: oe_debug_pub.ADD('OE_Blanket_Util.Process_Object exc error in Create and Start Flow ', 1);
7175: end if;
7176: RAISE FND_API.G_EXC_ERROR;
7177: END IF;
7178:
7203: );
7204:
7205: IF lx_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7206: if l_debug_level > 0 then
7207: oe_debug_pub.ADD('OE_Blanket_Util.Process_Object unexp error in inst. doc terms ', 1);
7208: end if;
7209: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7210: ELSIF lx_return_status = FND_API.G_RET_STS_ERROR THEN
7211: if l_debug_level > 0 then
7208: end if;
7209: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7210: ELSIF lx_return_status = FND_API.G_RET_STS_ERROR THEN
7211: if l_debug_level > 0 then
7212: oe_debug_pub.ADD('OE_Blanket_Util.Process_Object exc error in inst. doc terms ', 1);
7213: end if;
7214: RAISE FND_API.G_EXC_ERROR;
7215: END IF;
7216:
7226: x_return_status => l_return_status);
7227:
7228: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7229: if l_debug_level > 0 then
7230: oe_debug_pub.ADD('OE_Blanket_Util.Process_Object unexp error in perform versioning ', 1);
7231: end if;
7232: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7233: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
7234: if l_debug_level > 0 then
7231: end if;
7232: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7233: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
7234: if l_debug_level > 0 then
7235: oe_debug_pub.ADD('OE_Blanket_Util.Process_Object exc error in perform versioning ', 1);
7236: end if;
7237: RAISE FND_API.G_EXC_ERROR;
7238: END IF;
7239:
7247:
7248:
7249: IF lx_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
7250: if l_debug_level > 0 then
7251: oe_debug_pub.ADD('OE_Blanket_Util.Process_Object unexp error in perform versioning ', 1);
7252: end if;
7253: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7254: ELSIF lx_return_status = FND_API.G_RET_STS_ERROR THEN
7255: if l_debug_level > 0 then
7252: end if;
7253: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7254: ELSIF lx_return_status = FND_API.G_RET_STS_ERROR THEN
7255: if l_debug_level > 0 then
7256: oe_debug_pub.ADD('OE_Blanket_Util.Process_Object exc error in perform versioning ', 1);
7257: end if;
7258: RAISE FND_API.G_EXC_ERROR;
7259: END IF;
7260:
7293: )
7294: IS
7295: l_valid varchar2(1);
7296: --
7297: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7298: --
7299: BEGIN
7300: x_return_status := FND_API.G_RET_STS_SUCCESS;
7301:
7330: EXCEPTION
7331:
7332: WHEN FND_API.G_EXC_ERROR THEN
7333: if l_debug_level > 0 then
7334: oe_debug_pub.ADD('Validate line Number-Exp exception ', 1);
7335: end if;
7336: x_return_status := FND_API.G_RET_STS_ERROR;
7337: RAISE FND_API.G_EXC_ERROR;
7338:
7338:
7339:
7340: WHEN OTHERS THEN
7341: if l_debug_level > 0 then
7342: oe_debug_pub.ADD('Validate line Number-exception ', 1);
7343: end if;
7344:
7345: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7346: IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7373: l_ordered_item VARCHAR2(2000);--bug6826787
7374:
7375:
7376: --
7377: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7378: --
7379: BEGIN
7380:
7381: x_return_status := FND_API.G_RET_STS_SUCCESS;
7402: l_end_date_active :=
7403: oe_delayed_requests_pvt.g_delayed_requests(p_req_ind).date_param2;
7404:
7405: if l_debug_level > 0 then
7406: oe_debug_pub.add('In validate Item Uniqueness');
7407: oe_debug_pub.add('item identifier type :'|| l_item_identifier_type);
7408: oe_debug_pub.add('inventory item id :'|| l_item_id);
7409: oe_debug_pub.add('header id :'|| l_header_id);
7410: oe_debug_pub.add('line id :'|| l_line_id);
7403: oe_delayed_requests_pvt.g_delayed_requests(p_req_ind).date_param2;
7404:
7405: if l_debug_level > 0 then
7406: oe_debug_pub.add('In validate Item Uniqueness');
7407: oe_debug_pub.add('item identifier type :'|| l_item_identifier_type);
7408: oe_debug_pub.add('inventory item id :'|| l_item_id);
7409: oe_debug_pub.add('header id :'|| l_header_id);
7410: oe_debug_pub.add('line id :'|| l_line_id);
7411: oe_debug_pub.add('start date :'|| l_start_date_active);
7404:
7405: if l_debug_level > 0 then
7406: oe_debug_pub.add('In validate Item Uniqueness');
7407: oe_debug_pub.add('item identifier type :'|| l_item_identifier_type);
7408: oe_debug_pub.add('inventory item id :'|| l_item_id);
7409: oe_debug_pub.add('header id :'|| l_header_id);
7410: oe_debug_pub.add('line id :'|| l_line_id);
7411: oe_debug_pub.add('start date :'|| l_start_date_active);
7412: oe_debug_pub.add('end date :'|| l_end_date_active);
7405: if l_debug_level > 0 then
7406: oe_debug_pub.add('In validate Item Uniqueness');
7407: oe_debug_pub.add('item identifier type :'|| l_item_identifier_type);
7408: oe_debug_pub.add('inventory item id :'|| l_item_id);
7409: oe_debug_pub.add('header id :'|| l_header_id);
7410: oe_debug_pub.add('line id :'|| l_line_id);
7411: oe_debug_pub.add('start date :'|| l_start_date_active);
7412: oe_debug_pub.add('end date :'|| l_end_date_active);
7413: end if;
7406: oe_debug_pub.add('In validate Item Uniqueness');
7407: oe_debug_pub.add('item identifier type :'|| l_item_identifier_type);
7408: oe_debug_pub.add('inventory item id :'|| l_item_id);
7409: oe_debug_pub.add('header id :'|| l_header_id);
7410: oe_debug_pub.add('line id :'|| l_line_id);
7411: oe_debug_pub.add('start date :'|| l_start_date_active);
7412: oe_debug_pub.add('end date :'|| l_end_date_active);
7413: end if;
7414:
7407: oe_debug_pub.add('item identifier type :'|| l_item_identifier_type);
7408: oe_debug_pub.add('inventory item id :'|| l_item_id);
7409: oe_debug_pub.add('header id :'|| l_header_id);
7410: oe_debug_pub.add('line id :'|| l_line_id);
7411: oe_debug_pub.add('start date :'|| l_start_date_active);
7412: oe_debug_pub.add('end date :'|| l_end_date_active);
7413: end if;
7414:
7415: --modified the following sql to check for uiqueness of
7408: oe_debug_pub.add('inventory item id :'|| l_item_id);
7409: oe_debug_pub.add('header id :'|| l_header_id);
7410: oe_debug_pub.add('line id :'|| l_line_id);
7411: oe_debug_pub.add('start date :'|| l_start_date_active);
7412: oe_debug_pub.add('end date :'|| l_end_date_active);
7413: end if;
7414:
7415: --modified the following sql to check for uiqueness of
7416: -- (inventory_item_id,item_idenitfier_type,ordered_item_id) for bug6826787
7465:
7466: EXCEPTION
7467: WHEN FND_API.G_EXC_ERROR THEN
7468: if l_debug_level > 0 then
7469: oe_debug_pub.ADD('Validate item uniqr-Exp exception ', 1);
7470: end if;
7471: x_return_status := FND_API.G_RET_STS_ERROR;
7472: RAISE FND_API.G_EXC_ERROR;
7473: WHEN OTHERS THEN
7471: x_return_status := FND_API.G_RET_STS_ERROR;
7472: RAISE FND_API.G_EXC_ERROR;
7473: WHEN OTHERS THEN
7474: if l_debug_level > 0 then
7475: oe_debug_pub.ADD('Validate item uniqueness-exception ', 1);
7476: end if;
7477: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7478: IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7479: THEN
7491: RETURN BOOLEAN IS
7492: l_dummy VARCHAR2(30);
7493: l_result BOOLEAN;
7494: --
7495: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7496: --
7497: BEGIN
7498:
7499: -- 11i10 pricing change - source code is BSO in 11i10.
7533:
7534: EXCEPTION
7535: WHEN NO_DATA_FOUND THEN
7536: if l_debug_level > 0 then
7537: oe_debug_pub.ADD('Not a blanket price list', 1);
7538: end if;
7539: RETURN FALSE;
7540:
7541: END IS_BLANKET_PRICE_LIST;
7548: IS
7549: l_return_status VARCHAR2(1);
7550: l_new_version_number NUMBER;
7551: --
7552: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7553: --
7554: BEGIN
7555: if l_debug_level > 0 then
7556: oe_debug_pub.ADD('Entering Record_Blanket_History from delayed req', 1);
7552: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7553: --
7554: BEGIN
7555: if l_debug_level > 0 then
7556: oe_debug_pub.ADD('Entering Record_Blanket_History from delayed req', 1);
7557: end if;
7558:
7559: x_return_status := FND_API.G_RET_STS_SUCCESS;
7560:
7563: FROM oe_blanket_headers_all
7564: WHERE header_id = g_old_header_hist_rec.header_id;
7565:
7566: if l_debug_level > 0 then
7567: oe_debug_pub.ADD('old versn :'||g_old_header_hist_rec.version_number);
7568: oe_debug_pub.ADD('new versn :'||l_new_version_number);
7569: end if;
7570:
7571: -- This check is needed as user could change version number multiple times
7564: WHERE header_id = g_old_header_hist_rec.header_id;
7565:
7566: if l_debug_level > 0 then
7567: oe_debug_pub.ADD('old versn :'||g_old_header_hist_rec.version_number);
7568: oe_debug_pub.ADD('new versn :'||l_new_version_number);
7569: end if;
7570:
7571: -- This check is needed as user could change version number multiple times
7572: -- but in the end, latest version number could be same as the old version
7591:
7592: EXCEPTION
7593: WHEN OTHERS THEN
7594: if l_debug_level > 0 then
7595: oe_debug_pub.ADD('Record Blanket History Exception ', 1);
7596: end if;
7597:
7598: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7599:
7624: l_return_status VARCHAR2(1);
7625: l_header_id NUMBER;
7626: l_count NUMBER;
7627: --
7628: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7629: --
7630: BEGIN
7631:
7632: if l_debug_level > 0 then
7629: --
7630: BEGIN
7631:
7632: if l_debug_level > 0 then
7633: oe_debug_pub.ADD('Entering Copy_blanket ', 1);
7634: end if;
7635:
7636: OE_MSG_PUB.initialize;
7637:
7827: );
7828:
7829: WHEN OTHERS THEN
7830: if l_debug_level > 0 then
7831: oe_debug_pub.ADD('Copy Blanket Exception ', 1);
7832: end if;
7833:
7834: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7835:
7856: x_return_status IN OUT NOCOPY VARCHAR2
7857: )
7858: IS
7859: --
7860: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7861: --
7862: BEGIN
7863:
7864:
7865: IF (p_min_value IS NOT NULL)
7866: AND (p_max_value IS NOT NULL)
7867: AND (p_min_value > p_max_value) THEN
7868: if l_debug_level > 0 then
7869: oe_debug_pub.add('Invalid min/max for attribute :'||p_attribute);
7870: end if;
7871: FND_MESSAGE.SET_NAME('ONT','OE_BLKT_INVALID_MIN_MAX_RANGE');
7872: FND_MESSAGE.SET_TOKEN('MIN', p_min_value);
7873: FND_MESSAGE.SET_TOKEN('MAX', p_max_value);
7878: EXCEPTION
7879:
7880: WHEN OTHERS THEN
7881: if l_debug_level > 0 then
7882: oe_debug_pub.ADD('Validate min max range - U exeception ', 1);
7883: end if;
7884:
7885: IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7886: THEN
7903: )
7904: IS
7905: l_org_id NUMBER;
7906: --
7907: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7908: --
7909: BEGIN
7910: --
7911: --l_org_id := to_number(FND_PROFILE.VALUE('ORG_ID'));
7911: --l_org_id := to_number(FND_PROFILE.VALUE('ORG_ID'));
7912: --
7913:
7914: if l_debug_level > 0 then
7915: oe_debug_pub.ADD('Before inserting blanket history header_id ' || p_header_rec.header_id, 5);
7916: end if;
7917:
7918: INSERT INTO OE_BLANKET_HEADERS_HIST
7919: ( ACCOUNTING_RULE_ID
8121:
8122: FOR I IN 1..p_line_tbl.COUNT LOOP
8123:
8124: if l_debug_level > 0 then
8125: oe_debug_pub.ADD('Before inserting blanket line history line_id ' || p_line_tbl(I).line_id, 5);
8126: end if;
8127:
8128: INSERT INTO OE_BLANKET_LINES_HIST
8129: ( ACCOUNTING_RULE_ID
8319:
8320: END LOOP;
8321:
8322: if l_debug_level > 0 then
8323: oe_debug_pub.ADD('After inserting blanket history', 1);
8324: end if;
8325:
8326: END Insert_History_Records;
8327:
8335:
8336: l_blanket_number number;
8337: l_blanket_line_number number;
8338: --
8339: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8340: --
8341: BEGIN
8342: if l_debug_level > 0 then
8343: oe_debug_pub.add('Enter OE_Blanket_Util.Returns_Exist');
8339: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8340: --
8341: BEGIN
8342: if l_debug_level > 0 then
8343: oe_debug_pub.add('Enter OE_Blanket_Util.Returns_Exist');
8344: end if;
8345:
8346: If p_validation_entity_short_name = 'BLANKET_LINE' Then
8347: l_blanket_number := oe_blanket_line_security.g_record.order_number;
8387:
8388: l_blanket_number number;
8389: l_blanket_line_number number;
8390: --
8391: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8392: --
8393: BEGIN
8394: if l_debug_level > 0 then
8395: oe_debug_pub.add('Enter OE_Blanket_Util.Release_Lines_Exist');
8391: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8392: --
8393: BEGIN
8394: if l_debug_level > 0 then
8395: oe_debug_pub.add('Enter OE_Blanket_Util.Release_Lines_Exist');
8396: end if;
8397:
8398: If p_validation_entity_short_name = 'BLANKET_LINE' Then
8399: l_blanket_number := oe_blanket_line_security.g_record.order_number;
8437:
8438: l_blanket_number number;
8439: l_blanket_line_number number;
8440: --
8441: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8442: --
8443: BEGIN
8444: if l_debug_level > 0 then
8445: oe_debug_pub.add('Enter OE_Blanket_Util.Release_Lines_Exist');
8441: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8442: --
8443: BEGIN
8444: if l_debug_level > 0 then
8445: oe_debug_pub.add('Enter OE_Blanket_Util.Release_Lines_Exist');
8446: end if;
8447:
8448: If p_validation_entity_short_name = 'BLANKET_LINE' Then
8449: l_blanket_number := oe_blanket_line_security.g_record.order_number;
8485:
8486: l_blanket_number number;
8487: l_blanket_line_number number;
8488: --
8489: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8490: --
8491: BEGIN
8492: if l_debug_level > 0 then
8493: oe_debug_pub.add('Enter OE_Blanket_Util.Open_Release_Lines_Exist');
8489: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8490: --
8491: BEGIN
8492: if l_debug_level > 0 then
8493: oe_debug_pub.add('Enter OE_Blanket_Util.Open_Release_Lines_Exist');
8494: end if;
8495:
8496: If p_validation_entity_short_name = 'BLANKET_LINE' Then
8497: l_blanket_number := oe_blanket_line_security.g_record.order_number;
8537:
8538: l_blanket_number number;
8539: l_blanket_line_number number;
8540: --
8541: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8542: --
8543: BEGIN
8544: if l_debug_level > 0 then
8545: oe_debug_pub.add('Enter OE_Blanket_Util.Open_Release_Lines_Exist');
8541: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8542: --
8543: BEGIN
8544: if l_debug_level > 0 then
8545: oe_debug_pub.add('Enter OE_Blanket_Util.Open_Release_Lines_Exist');
8546: end if;
8547:
8548: If p_validation_entity_short_name = 'BLANKET_LINE' Then
8549: l_blanket_number := oe_blanket_line_security.g_record.order_number;
8585: p_scope in VARCHAR2,
8586: p_result OUT NOCOPY NUMBER ) IS
8587:
8588: --
8589: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8590: --
8591: BEGIN
8592: if l_debug_level > 0 then
8593: oe_debug_pub.add('Enter OE_Blanket_Util.Is_Expired');
8589: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
8590: --
8591: BEGIN
8592: if l_debug_level > 0 then
8593: oe_debug_pub.add('Enter OE_Blanket_Util.Is_Expired');
8594: end if;
8595:
8596: If p_validation_entity_short_name = 'BLANKET_LINE' Then
8597: If (trunc(sysdate) >= OE_Blanket_Line_Security.g_record.start_date_active
9226: post message OE_INVALIDATES_CUSTOMER_ITEM
9227: set return status to error.
9228: */
9229: x_return_status := FND_API.G_RET_STS_SUCCESS;
9230: oe_debug_pub.add('Entering Oe_blanket_util Get_Inventory_Item', 1);
9231: IF (p_x_line_rec.ship_to_org_id IS NOT NULL AND
9232: p_x_line_rec.ship_to_org_id <> FND_API.G_MISS_NUM) THEN
9233:
9234: SELECT u.cust_acct_site_id,s.cust_account_id
9237: FROM HZ_CUST_SITE_USES u,HZ_CUST_ACCT_SITES s
9238: WHERE u.cust_acct_site_id = s.cust_acct_site_id
9239: AND u.site_use_id = p_x_line_rec.ship_to_org_id
9240: AND u.site_use_code = 'SHIP_TO';
9241: oe_debug_pub.add('ship to address:' || l_address_id||' - Customer:'||to_char(l_cust_id));
9242:
9243: IF l_cust_id <> p_x_line_rec.sold_to_org_id THEN
9244: oe_debug_pub.add('Sold-To Customer:'||to_char(p_x_line_rec.sold_to_org_id));
9245: l_address_id := NULL;
9240: AND u.site_use_code = 'SHIP_TO';
9241: oe_debug_pub.add('ship to address:' || l_address_id||' - Customer:'||to_char(l_cust_id));
9242:
9243: IF l_cust_id <> p_x_line_rec.sold_to_org_id THEN
9244: oe_debug_pub.add('Sold-To Customer:'||to_char(p_x_line_rec.sold_to_org_id));
9245: l_address_id := NULL;
9246: END IF;
9247:
9248: END IF;
9246: END IF;
9247:
9248: END IF;
9249:
9250: oe_debug_pub.add('INVENTORY_ITEM_ID Before calling CI_Attribute_Value '
9251: ||to_char(p_x_line_rec.inventory_item_id), 1);
9252: INV_CUSTOMER_ITEM_GRP.CI_Attribute_Value(
9253: Z_Customer_Item_Id => p_x_line_rec.ordered_item_id
9254: , Z_Customer_Id => p_x_line_rec.sold_to_org_id
9261: , Error_Message => l_error_message
9262: , Attribute_Value => l_attribute_value
9263: );
9264:
9265: oe_debug_pub.add('INVENTORY_ITEM_ID After call is '||l_attribute_value, 1);
9266: IF (l_attribute_value IS NOT NULL AND
9267: to_number(l_attribute_value) <> p_x_line_rec.inventory_item_id) THEN
9268: oe_debug_pub.add('Assigning new inventory_item_id', 1);
9269: l_update_inventory_item := FND_API.G_TRUE;
9264:
9265: oe_debug_pub.add('INVENTORY_ITEM_ID After call is '||l_attribute_value, 1);
9266: IF (l_attribute_value IS NOT NULL AND
9267: to_number(l_attribute_value) <> p_x_line_rec.inventory_item_id) THEN
9268: oe_debug_pub.add('Assigning new inventory_item_id', 1);
9269: l_update_inventory_item := FND_API.G_TRUE;
9270: l_inventory_item_id := TO_NUMBER(l_attribute_value);
9271: ELSIF to_number(l_attribute_value) = p_x_line_rec.inventory_item_id THEN
9272: NULL;
9270: l_inventory_item_id := TO_NUMBER(l_attribute_value);
9271: ELSIF to_number(l_attribute_value) = p_x_line_rec.inventory_item_id THEN
9272: NULL;
9273: ELSE
9274: oe_debug_pub.add('Issue error message', 1);
9275: fnd_message.set_name('ONT','OE_INVALIDATES_CUSTOMER_ITEM');
9276: OE_MSG_PUB.Add;
9277: x_return_status := FND_API.G_RET_STS_ERROR;
9278: END IF;
9283: p_sold_to_org_id IN NUMBER,
9284: x_return_status OUT NOCOPY VARCHAR2)
9285: IS
9286: --
9287: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
9288: L_DUMMY VARCHAR2(30) := 'VALID';
9289: l_invoice_to_org_id number;
9290: l_deliver_to_org_id number;
9291: l_ship_To_org_id number;
9304: BEGIN
9305: OE_MSG_PUB.initialize;
9306: x_return_status := FND_API.G_RET_STS_SUCCESS;
9307: IF l_debug_level > 0 THEN
9308: oe_debug_pub.add('ENTERING OE_Blanket_util.validate_sold_to:');
9309: oe_debug_pub.add('DB Line ID : '|| TO_CHAR (mc1x.line_id) ,1);
9310: oe_debug_pub.add('DB Sold To Org ID : '|| TO_CHAR (mc1x.sold_to_org_id) ,1);
9311: oe_debug_pub.add('actual Hdr Sold To Org ID : '|| TO_CHAR (p_sold_to_org_id) ,1);
9312: END IF;
9305: OE_MSG_PUB.initialize;
9306: x_return_status := FND_API.G_RET_STS_SUCCESS;
9307: IF l_debug_level > 0 THEN
9308: oe_debug_pub.add('ENTERING OE_Blanket_util.validate_sold_to:');
9309: oe_debug_pub.add('DB Line ID : '|| TO_CHAR (mc1x.line_id) ,1);
9310: oe_debug_pub.add('DB Sold To Org ID : '|| TO_CHAR (mc1x.sold_to_org_id) ,1);
9311: oe_debug_pub.add('actual Hdr Sold To Org ID : '|| TO_CHAR (p_sold_to_org_id) ,1);
9312: END IF;
9313:
9306: x_return_status := FND_API.G_RET_STS_SUCCESS;
9307: IF l_debug_level > 0 THEN
9308: oe_debug_pub.add('ENTERING OE_Blanket_util.validate_sold_to:');
9309: oe_debug_pub.add('DB Line ID : '|| TO_CHAR (mc1x.line_id) ,1);
9310: oe_debug_pub.add('DB Sold To Org ID : '|| TO_CHAR (mc1x.sold_to_org_id) ,1);
9311: oe_debug_pub.add('actual Hdr Sold To Org ID : '|| TO_CHAR (p_sold_to_org_id) ,1);
9312: END IF;
9313:
9314: -- Open the cursor
9307: IF l_debug_level > 0 THEN
9308: oe_debug_pub.add('ENTERING OE_Blanket_util.validate_sold_to:');
9309: oe_debug_pub.add('DB Line ID : '|| TO_CHAR (mc1x.line_id) ,1);
9310: oe_debug_pub.add('DB Sold To Org ID : '|| TO_CHAR (mc1x.sold_to_org_id) ,1);
9311: oe_debug_pub.add('actual Hdr Sold To Org ID : '|| TO_CHAR (p_sold_to_org_id) ,1);
9312: END IF;
9313:
9314: -- Open the cursor
9315: OPEN c1;
9315: OPEN c1;
9316: loop
9317: FETCH c1 into mc1x;
9318: exit when c1%NOTFOUND;
9319: oe_debug_pub.add('DB Line To : '|| TO_CHAR (mc1x.Line_id) ,1);
9320: oe_debug_pub.add('DB Ship To : '|| TO_CHAR (mc1x.ship_to_org_id) ,1);
9321: oe_debug_pub.add('DB Del ID : '|| TO_CHAR (mc1x.deliver_to_org_id) ,1);
9322: oe_debug_pub.add('DB Invo ID : '|| TO_CHAR (mc1x.invoice_to_org_id) ,1);
9323: if mc1x.ship_to_org_id is not null then
9316: loop
9317: FETCH c1 into mc1x;
9318: exit when c1%NOTFOUND;
9319: oe_debug_pub.add('DB Line To : '|| TO_CHAR (mc1x.Line_id) ,1);
9320: oe_debug_pub.add('DB Ship To : '|| TO_CHAR (mc1x.ship_to_org_id) ,1);
9321: oe_debug_pub.add('DB Del ID : '|| TO_CHAR (mc1x.deliver_to_org_id) ,1);
9322: oe_debug_pub.add('DB Invo ID : '|| TO_CHAR (mc1x.invoice_to_org_id) ,1);
9323: if mc1x.ship_to_org_id is not null then
9324: --Ship to
9317: FETCH c1 into mc1x;
9318: exit when c1%NOTFOUND;
9319: oe_debug_pub.add('DB Line To : '|| TO_CHAR (mc1x.Line_id) ,1);
9320: oe_debug_pub.add('DB Ship To : '|| TO_CHAR (mc1x.ship_to_org_id) ,1);
9321: oe_debug_pub.add('DB Del ID : '|| TO_CHAR (mc1x.deliver_to_org_id) ,1);
9322: oe_debug_pub.add('DB Invo ID : '|| TO_CHAR (mc1x.invoice_to_org_id) ,1);
9323: if mc1x.ship_to_org_id is not null then
9324: --Ship to
9325: BEGIN
9318: exit when c1%NOTFOUND;
9319: oe_debug_pub.add('DB Line To : '|| TO_CHAR (mc1x.Line_id) ,1);
9320: oe_debug_pub.add('DB Ship To : '|| TO_CHAR (mc1x.ship_to_org_id) ,1);
9321: oe_debug_pub.add('DB Del ID : '|| TO_CHAR (mc1x.deliver_to_org_id) ,1);
9322: oe_debug_pub.add('DB Invo ID : '|| TO_CHAR (mc1x.invoice_to_org_id) ,1);
9323: if mc1x.ship_to_org_id is not null then
9324: --Ship to
9325: BEGIN
9326: SELECT 'VALID' INTO l_dummy
9331: and address_status='A';
9332: EXCEPTION
9333: WHEN NO_DATA_FOUND THEN
9334: if l_debug_level > 0 then
9335: oe_debug_pub.add ('OE_blanket_util.validate_sold_to Blanket In ship to No data found',2);
9336: end if;
9337: l_dummy := 'INVALID';
9338: x_return_status := FND_API.G_RET_STS_ERROR;
9339: fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
9341: , OE_Order_Util.Get_Attribute_Name('ship_to_org_id'));
9342: OE_MSG_PUB.Add;
9343: WHEN OTHERS THEN
9344: if l_debug_level > 0 then
9345: oe_debug_pub.add ('OE_blanket_util.validate_sold_to WOE Blanket In Ship No data found',2);
9346: end if;
9347: l_dummy := 'INVALID';
9348: x_return_status := FND_API.G_RET_STS_ERROR;
9349: IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
9364:
9365: EXCEPTION
9366: WHEN NO_DATA_FOUND THEN
9367: if l_debug_level > 0 then
9368: oe_debug_pub.add ('OE_blanket_util.validate_sold_to Blanket In deliver No data found',2);
9369: end if;
9370: l_dummy := 'INVALID';
9371: x_return_status := FND_API.G_RET_STS_ERROR;
9372: fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
9374: OE_Order_Util.Get_Attribute_Name('deliver_to_org_id'));
9375: OE_MSG_PUB.Add;
9376: WHEN OTHERS THEN
9377: if l_debug_level > 0 then
9378: oe_debug_pub.add ('OE_blanket_util.validate_sold_to WOE Blanket In Deliver No data found',2);
9379: end if;
9380: l_dummy := 'INVALID';
9381: x_return_status := FND_API.G_RET_STS_ERROR;
9382: IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
9394: AND site_use_id = mc1x.invoice_to_org_id;
9395: EXCEPTION
9396: WHEN NO_DATA_FOUND THEN
9397: if l_debug_level > 0 then
9398: oe_debug_pub.add ('OE_blanket_util.validate_sold_to Blanket In invoice No data found',2);
9399: end if;
9400: l_dummy := 'INVALID';
9401: x_return_status := FND_API.G_RET_STS_ERROR;
9402: fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
9404: OE_Order_Util.Get_Attribute_Name('invoice_to_org_id'));
9405: OE_MSG_PUB.Add;
9406: WHEN OTHERS THEN
9407: if l_debug_level > 0 then
9408: oe_debug_pub.add ('OE_blanket_util.validate_sold_to WOE Blanket In Invoice No data found',2);
9409: end if;
9410: l_dummy := 'INVALID';
9411: x_return_status := FND_API.G_RET_STS_ERROR;
9412: IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
9417: end if;
9418: END LOOP;
9419: CLOSE C1;
9420: if l_debug_level > 0 then
9421: oe_debug_pub.add ('OE_blanket_util.validate_sold_to After Close In Invoice '||x_return_status,2);
9422: end if;
9423:
9424: if x_return_status = FND_API.G_RET_STS_ERROR then
9425: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9427: update oe_blanket_lines_all set sold_to_org_id = p_sold_to_org_id
9428: where header_id = p_header_id;
9429: end if;
9430: IF l_debug_level > 0 THEN
9431: oe_debug_pub.add('EXISTING OE_Blanket_util.validate_sold_to: '|| TO_CHAR (p_header_id) ,1);
9432: END IF;
9433: END validate_sold_to;
9434:
9435: -- New procedure added for 5528599 start
9440: x_msg_data OUT NOCOPY VARCHAR2)
9441:
9442: IS
9443:
9444: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
9445: l_header_rec OE_Blanket_Pub.header_rec_type;
9446: l_line_tbl OE_Blanket_Pub.line_tbl_type;
9447: K NUMBER :=1;
9448: BEGIN
9447: K NUMBER :=1;
9448: BEGIN
9449: --bug#5528507
9450: x_return_status := FND_API.G_RET_STS_SUCCESS;
9451: oe_debug_pub.add('In the valid_blanket_dates',1);
9452:
9453: Query_Blanket(p_header_id => p_header_id
9454: ,p_x_header_rec => l_header_rec
9455: ,p_x_line_tbl => l_line_tbl
9457:
9458:
9459: if ( x_return_status = 'S')
9460: then
9461: oe_debug_pub.add('End Date :'||trunc(l_header_rec.end_date_active),1);
9462: oe_debug_pub.add('Sysdate :'||trunc(sysdate),1);
9463:
9464: OE_MSG_PUB.set_msg_context(
9465: p_entity_code => 'BLANKET_HEADER'
9458:
9459: if ( x_return_status = 'S')
9460: then
9461: oe_debug_pub.add('End Date :'||trunc(l_header_rec.end_date_active),1);
9462: oe_debug_pub.add('Sysdate :'||trunc(sysdate),1);
9463:
9464: OE_MSG_PUB.set_msg_context(
9465: p_entity_code => 'BLANKET_HEADER'
9466: ,p_entity_id => l_header_rec.header_id
9482: end if;
9483:
9484:
9485: K := l_line_tbl.FIRST;
9486: oe_debug_pub.add('validating Lines ',1);
9487: WHILE K IS NOT NULL
9488: LOOP
9489:
9490: OE_MSG_PUB.set_msg_context(
9499: ,p_source_document_line_id => null
9500: ,p_order_source_id => null
9501: ,p_source_document_type_id => null);
9502:
9503: oe_debug_pub.add('start date active:'|| l_line_tbl(K).start_date_active,1);
9504: oe_debug_pub.add('end_date_active :'|| l_line_tbl(K).end_date_active,1);
9505: oe_debug_pub.add('SYSDATE :'|| trunc(sysdate),1);
9506:
9507: if (l_line_tbl(K).end_date_active is not NULL and
9500: ,p_order_source_id => null
9501: ,p_source_document_type_id => null);
9502:
9503: oe_debug_pub.add('start date active:'|| l_line_tbl(K).start_date_active,1);
9504: oe_debug_pub.add('end_date_active :'|| l_line_tbl(K).end_date_active,1);
9505: oe_debug_pub.add('SYSDATE :'|| trunc(sysdate),1);
9506:
9507: if (l_line_tbl(K).end_date_active is not NULL and
9508: (l_line_tbl(K).start_date_active)
9501: ,p_source_document_type_id => null);
9502:
9503: oe_debug_pub.add('start date active:'|| l_line_tbl(K).start_date_active,1);
9504: oe_debug_pub.add('end_date_active :'|| l_line_tbl(K).end_date_active,1);
9505: oe_debug_pub.add('SYSDATE :'|| trunc(sysdate),1);
9506:
9507: if (l_line_tbl(K).end_date_active is not NULL and
9508: (l_line_tbl(K).start_date_active)
9509: > (l_line_tbl(K).end_date_active)) THEN
9543: p_scope in VARCHAR2,
9544: p_result OUT NOCOPY NUMBER ) IS
9545:
9546: --
9547: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
9548: --
9549: BEGIN
9550: if l_debug_level > 0 then
9551: oe_debug_pub.add('Enter OE_Blanket_Util.Batch_Call');
9547: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
9548: --
9549: BEGIN
9550: if l_debug_level > 0 then
9551: oe_debug_pub.add('Enter OE_Blanket_Util.Batch_Call');
9552: end if;
9553:
9554: if OE_GLOBALS.G_UI_FLAG THEN
9555: oe_debug_pub.add(' UI Call ');
9551: oe_debug_pub.add('Enter OE_Blanket_Util.Batch_Call');
9552: end if;
9553:
9554: if OE_GLOBALS.G_UI_FLAG THEN
9555: oe_debug_pub.add(' UI Call ');
9556: ELSE
9557: oe_debug_pub.add(' Batch call' );
9558: end if;
9559: IF NOT (OE_GLOBALS.G_UI_FLAG ) THEN
9553:
9554: if OE_GLOBALS.G_UI_FLAG THEN
9555: oe_debug_pub.add(' UI Call ');
9556: ELSE
9557: oe_debug_pub.add(' Batch call' );
9558: end if;
9559: IF NOT (OE_GLOBALS.G_UI_FLAG ) THEN
9560: p_result :=1;
9561: ELSE