[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT freight_code
INTO l_freight_code
FROM wsh_carriers wsh_ca,wsh_carrier_services wsh,
wsh_org_carrier_services wsh_org
WHERE wsh_org.organization_id = p_line_rec.ship_from_org_id
AND wsh.carrier_service_id = wsh_org.carrier_service_id
AND wsh_ca.carrier_id = wsh.carrier_id
AND wsh.ship_method_code = p_line_rec.shipping_method_code
AND wsh_org.enabled_flag = 'Y';
Select freight_code
into l_freight_code
from wsh_carrier_ship_methods
where ship_method_code = p_line_rec.shipping_method_code
and ORGANIZATION_ID = p_line_rec.ship_from_org_id;
/*SELECT booked_flag
INTO l_booked_flag
FROM oe_order_headers_all
WHERE header_id = g_line_rec.header_id; */
SELECT agreement_id
INTO l_agreement_id
FROM oe_agreements_vl
WHERE name = (select name from oe_agreements_vl
where
agreement_id = p_x_line_rec.agreement_id)
AND trunc(nvl(p_x_line_rec.pricing_date,sysdate)) BETWEEN
trunc(nvl(START_DATE_ACTIVE,add_months(sysdate,-10000)))
AND trunc(nvl(END_DATE_ACTIVE,add_months(sysdate,+10000)));
SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
INTO x_blanket_number,
x_blanket_version_number,
x_blanket_line_number
FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BL.CUST_PO_NUMBER = p_cust_po_number
AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(x_blanket_request_date)
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
AND BL.INVENTORY_ITEM_ID = p_inventory_item_id
AND BL.item_identifier_type = p_item_identifier_type
AND decode(BL.item_identifier_type,'INT',to_char(BL.inventory_item_id),
'CUST',to_char(ordered_item_id),
NVL(BL.ordered_item,'XXXX') )= decode ( p_item_identifier_type,'INT', to_char(p_inventory_item_id)
, 'CUST', to_char(p_ordered_item_id)
, NVL(p_ordered_item,'XXXX') )
AND BL.ITEM_IDENTIFIER_TYPE NOT IN ('CAT','ALL')
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
INTO x_blanket_number,
x_blanket_version_number,
x_blanket_line_number
FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BH.ORDER_NUMBER = l_blanket_number
-- Do not match customer if blanket number is supplied
-- With 11.5.10, customer on blanket could be
-- related customer or it could be a null customer
-- AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(x_blanket_request_date)
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
AND BL.INVENTORY_ITEM_ID = p_inventory_item_id
AND BL.item_identifier_type = p_item_identifier_type
AND decode(BL.item_identifier_type,'INT',to_char(BL.inventory_item_id),
'CUST',to_char(ordered_item_id),
NVL(BL.ordered_item,'XXXX') )= decode ( p_item_identifier_type,'INT', to_char(p_inventory_item_id)
, 'CUST', to_char(p_ordered_item_id)
, NVL(p_ordered_item,'XXXX') )
AND BL.ITEM_IDENTIFIER_TYPE NOT IN ('CAT','ALL')
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
INTO x_blanket_number,
x_blanket_version_number,
x_blanket_line_number
FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BL.CUST_PO_NUMBER = p_cust_po_number
AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(x_blanket_request_date)
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
AND BL.INVENTORY_ITEM_ID = p_inventory_item_id
AND BL.ITEM_IDENTIFIER_TYPE ='INT' --bug6826787
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
INTO x_blanket_number,
x_blanket_version_number,
x_blanket_line_number
FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BH.ORDER_NUMBER = l_blanket_number
-- Do not match customer if blanket number is supplied
-- With 11.5.10, customer on blanket could be
-- related customer or it could be a null customer
-- AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(x_blanket_request_date)
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
AND BL.INVENTORY_ITEM_ID = p_inventory_item_id
AND BL.ITEM_IDENTIFIER_TYPE ='INT' --bug6826787
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
INTO x_blanket_number,
x_blanket_version_number,
x_blanket_line_number
FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL, MTL_ITEM_CATEGORIES IC,
OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BL.CUST_PO_NUMBER = p_cust_po_number
AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(x_blanket_request_date)
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
AND BL.ITEM_IDENTIFIER_TYPE = 'CAT'
AND IC.ORGANIZATION_ID = l_item_validation_org
AND IC.INVENTORY_ITEM_ID = p_inventory_item_id
AND BL.INVENTORY_ITEM_ID = IC.CATEGORY_ID
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
INTO x_blanket_number,
x_blanket_version_number,
x_blanket_line_number
FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL, MTL_ITEM_CATEGORIES IC
,OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BH.ORDER_NUMBER = l_blanket_number
-- Do not match customer if blanket number is supplied
-- With 11.5.10, customer on blanket could be
-- related customer or it could be a null customer
-- AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(x_blanket_request_date)
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
AND BL.ITEM_IDENTIFIER_TYPE = 'CAT'
AND IC.ORGANIZATION_ID = l_item_validation_org
AND IC.INVENTORY_ITEM_ID = p_inventory_item_id
AND BL.INVENTORY_ITEM_ID = IC.CATEGORY_ID
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
INTO x_blanket_number,
x_blanket_version_number,
x_blanket_line_number
FROM OE_BLANKET_HEADERS BH, OE_BLANKET_LINES_ALL BL,
OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BL.CUST_PO_NUMBER = p_cust_po_number
AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(x_blanket_request_date)
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
AND BL.ITEM_IDENTIFIER_TYPE = 'ALL'
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
SELECT /* MOAC_SQL_CHANGE */ BH.ORDER_NUMBER, BH.VERSION_NUMBER, BL.LINE_NUMBER
INTO x_blanket_number,
x_blanket_version_number,
x_blanket_line_number
FROM OE_BLANKET_HEADERS_ALL BH, OE_BLANKET_LINES BL,
OE_BLANKET_HEADERS_EXT BHE,OE_BLANKET_LINES_EXT BLE
WHERE BH.HEADER_ID = BL.HEADER_ID
AND BH.ORDER_NUMBER = l_blanket_number
-- Do not match customer if blanket number is supplied
-- With 11.5.10, customer on blanket could be
-- related customer or it could be a null customer
-- AND BH.SOLD_TO_ORG_ID = p_sold_to_org_id
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(x_blanket_request_date)
BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, x_blanket_request_date))
AND BL.ITEM_IDENTIFIER_TYPE = 'ALL'
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BH.SALES_DOCUMENT_TYPE_CODE ='B';
SELECT /* MOAC_SQL_CHANGE */ BH.VERSION_NUMBER
INTO l_blanket_version_number
FROM OE_BLANKET_LINES_ALL BL,OE_BLANKET_LINES_EXT BLE,
OE_BLANKET_HEADERS BH
WHERE BLE.ORDER_NUMBER = p_x_line_rec.blanket_number
AND BLE.LINE_NUMBER = p_x_line_rec.blanket_line_number
AND BL.LINE_ID = BLE.LINE_ID
AND BH.HEADER_ID = BL.HEADER_ID
AND BL.SALES_DOCUMENT_TYPE_CODE = 'B';
SELECT /* MOAC_SQL_CHANGE */
BL.LINE_NUMBER,
BH.VERSION_NUMBER
INTO
x_blanket_line_number,
x_blanket_version_number
FROM OE_BLANKET_LINES BL,
OE_BLANKET_HEADERS_ALL BH,
OE_BLANKET_HEADERS_EXT BHE,
OE_BLANKET_LINES_EXT BLE
WHERE BH.ORDER_NUMBER = p_blanket_number
AND BL.INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID
AND BH.HEADER_ID = BL.HEADER_ID
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(sysdate) BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, sysdate))
AND BL.ITEM_IDENTIFIER_TYPE NOT IN ('ALL')
AND p_blanket_number is not null
AND BL.SALES_DOCUMENT_TYPE_CODE = 'B';
SELECT /* MOAC_SQL_CHANGE */
BL.LINE_NUMBER,
BH.VERSION_NUMBER
INTO
x_blanket_line_number,
x_blanket_version_number
FROM OE_BLANKET_LINES BL,
OE_BLANKET_HEADERS_ALL BH,
OE_BLANKET_HEADERS_EXT BHE,
OE_BLANKET_LINES_EXT BLE
WHERE BH.ORDER_NUMBER = p_blanket_number
AND BH.HEADER_ID = BL.HEADER_ID
AND BH.ORDER_NUMBER = BHE.ORDER_NUMBER
AND BL.LINE_ID = BLE.LINE_ID
AND BHE.ON_HOLD_FLAG = 'N'
AND trunc(sysdate) BETWEEN trunc(BLE.START_DATE_ACTIVE)
AND trunc(nvl(BLE.END_DATE_ACTIVE, sysdate))
AND BL.ITEM_IDENTIFIER_TYPE = 'ALL'
AND p_blanket_number is not null
AND BL.SALES_DOCUMENT_TYPE_CODE = 'B';
Insert_into_set
(p_line_id => g_line_rec.top_model_line_id,
p_child_line_id => g_line_rec.line_id,
x_return_status => l_return_status);
in case of updates
----------------------------------------------------------------*/
PROCEDURE Model_Option_update
(p_x_line_rec IN OUT NOCOPY OE_ORDER_PUB.line_rec_type)
IS
l_top_model_line_rec OE_ORDER_PUB.line_rec_type;
oe_debug_pub.add( 'ENTERING MODEL_OPTION_UPDATE' , 1 ) ;
/* These updates are valid only for Options/clasees/included items */
IF p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE OR
p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_STANDARD OR
(p_x_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT AND
p_x_line_rec.line_id = p_x_line_rec.top_modeL_line_id)
THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'RETURNING FOR ITEM TYPE SERVICE , MODEL , STANDARD AND TOP LEVEL KITS' , 1 ) ;
oe_debug_pub.add( 'EXITING MODEL_OPTION_UPDATE' , 1 ) ;
oe_debug_pub.add( 'NO DATA FOUND IN MODEL_OPTION_UPDATE' , 1 ) ;
'Model_Option_update'
);
oe_debug_pub.add( 'OTHERS IN MODEL_OPTION_UPDATE' , 1 ) ;
END Model_Option_Update;
the select statement for getting ato_line_id in case of
pto+ato case is modified. look at the bug for more details.
also made same change in OEXVCFGB.pls:update_ato_line_attribs.
Bug 2513840
Added Code to handle TOO_MANY_ROWS in Exception
--------------------------------------------------------------*/
FUNCTION Get_ATO_Line
RETURN NUMBER
IS
l_ato_line_id NUMBER;
SELECT ato_config_item_id
INTO l_ato_config_item_id
FROM cz_config_details_v
WHERE config_hdr_id = g_line_rec.config_header_id
AND config_rev_nbr = g_line_rec.config_rev_nbr
AND config_item_id = g_line_rec.configuration_id
AND inventory_item_id = g_line_rec.inventory_item_id;
SELECT line_id
INTO l_ato_line_id
FROM OE_ORDER_LINES_ALL OEOPT
WHERE line_id =
(SELECT line_id
FROM oe_order_lines OEATO
WHERE OEOPT.top_model_line_id = OEATO.top_model_line_id
AND OEATO.configuration_id = l_ato_config_item_id
AND OEATO.open_flag = 'Y')
AND top_model_line_id = g_line_rec.top_model_line_id;
oe_debug_pub.add( 'PTO+ATO SELECT '||L_ATO_LINE_ID , 1 ) ;
SELECT line_id
INTO l_ato_line_id
FROM OE_ORDER_LINES_ALL
WHERE top_model_line_id = g_line_rec.top_model_line_id
AND item_type_code = 'CLASS'
AND component_code =
SUBSTR( g_line_rec.component_code, 1,
LENGTH(component_code))
AND ato_line_id is not null
AND open_flag = 'Y'
AND component_code =
( SELECT MIN(OEMIN.component_code)
FROM OE_ORDER_LINES_ALL OEMIN
WHERE OEMIN.top_model_line_id
= g_line_rec.top_model_line_id
AND OEMIN.component_code =
SUBSTR( g_line_rec.component_code, 1,
LENGTH( OEMIN.component_code))
AND OEMIN.ato_line_id is not null
AND OEMIN.open_flag = 'Y')
AND (SUBSTR(g_line_rec.component_code,
LENGTH(component_code) + 1, 1) = '-' OR
SUBSTR(g_line_rec.component_code,
LENGTH(component_code) + 1, 1) is NULL);
oe_debug_pub.add( 'PTO+ATO SELECT '||L_ATO_LINE_ID , 1 ) ;
PROCEDURE Insert_into_set
This procedure will insert children of model into fulfillment
set id if the parent is part of a set.
Parent might exists in multiple fulfillment sets,
so get all the set_id's that
parent belong to and insert the children in all sets.
---------------------------------------------------------------------*/
PROCEDURE Insert_Into_set
( p_line_id IN NUMBER
,p_child_line_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR parent_sets IS
Select set_id
From oe_line_sets
Where line_id = p_line_id;
oe_debug_pub.add( 'ENTERING INTO INSERT_INTO_SET' , 1 ) ;
oe_debug_pub.add( 'EXITING FROM INSERT_INTO_SET' , 1 ) ;
END Insert_Into_set;
IF p_line_rec.operation = oe_globals.g_opr_update THEN
IF (p_old_line_rec.item_type_code <> FND_API.G_MISS_CHAR AND
p_old_line_rec.item_type_code IS NOT NULL) THEN
IF NOT OE_GLOBALS.EQUAL(p_old_line_rec.item_type_code,
p_item_type_code) THEN
FND_MESSAGE.SET_NAME('ONT','OE_ITEM_TYPE_CONST');
SELECT OE_ORDER_LINES_S.NEXTVAL
INTO l_line_id
FROM DUAL;
IF (p_line_rec.operation = oe_globals.g_opr_update AND
p_old_line_rec.line_category_code = 'RETURN' AND
p_line_rec.line_category_code = 'ORDER' AND
p_line_rec.order_source_id = 27 ) THEN
l_category := 'ORDER';
IF p_line_rec.operation = oe_globals.g_opr_update THEN
IF (p_old_line_rec.line_category_code <> FND_API.G_MISS_CHAR AND
p_old_line_rec.line_category_code IS NOT NULL) THEN
IF NOT OE_GLOBALS.EQUAL(p_old_line_rec.line_category_code,
l_category) THEN
--retro{In the case of price increase for original line, the initial
--retrobill line will be created with line_category return and has to be
--updated to order.The original flow doesn't allow and raises exception,To
--prevent the exception a if loop is added in the case of retrobilling
IF (p_line_rec.operation = oe_globals.g_opr_update AND
p_old_line_rec.line_category_code = 'RETURN' AND
p_line_rec.line_category_code = 'ORDER' AND
p_line_rec.order_source_id = 27 AND
p_line_rec.retrobill_request_id is not null) THEN
null;
IF p_line_rec.operation = oe_globals.g_opr_update THEN
IF (p_old_line_rec.line_category_code <> FND_API.G_MISS_CHAR AND
p_old_line_rec.line_category_code IS NOT NULL) THEN
IF NOT OE_GLOBALS.EQUAL(p_old_line_rec.line_category_code,
l_category) THEN
FND_MESSAGE.SET_NAME('ONT', 'OE_LINE_CAT_CONST');
SELECT NVL(MAX(LINE_NUMBER)+1,1)
INTO l_line_number
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = g_line_rec.header_id;
SELECT NVL(MAX(SHIPMENT_NUMBER)+1,1)
INTO l_ship_number
FROM OE_ORDER_LINES
WHERE HEADER_ID = g_line_rec.header_id
AND LINE_NUMBER = g_line_rec.line_number;
SELECT l.line_id
INTO l_order_line_id
FROM oe_order_lines l,
mtl_unit_transactions_all_v u,
mtl_material_transactions m
WHERE l.Inventory_item_id = to_number(p_return_attribute1)
AND m.transaction_source_type_id=2
AND m.trx_source_line_id=l.line_id
AND m.transaction_id = u.transaction_id
AND u.serial_number = p_return_attribute2
AND u.inventory_item_id = to_number(p_return_attribute1)
AND rownum = 1;
SELECT /* MOAC_SQL_CHANGE */ rctl.customer_trx_line_id
INTO l_invoice_line_id
FROM ra_customer_trx_lines_all rctl,
ra_customer_trx rct,
ar_lookups arlup
WHERE rct.status_trx = arlup.lookup_code
AND arlup.lookup_type = 'INVOICE_TRX_STATUS'
AND rct.customer_trx_id = rctl.customer_trx_id
AND rctl.interface_line_context='ORDER ENTRY'
AND rctl.interface_line_attribute6 = to_char(l_order_line_id)
AND rctl.line_type = 'LINE'
AND rctl.interface_line_attribute11 = '0' --Bug2721441
AND rctl.org_id=rct.org_id
AND rownum = 1;
SELECT /* MOAC_SQL_CHANGE */ rctl.customer_trx_line_id
INTO l_invoice_line_id
FROM ra_customer_trx_lines_all rctl,
ra_customer_trx rct,
ar_lookups arlup
WHERE rct.status_trx = arlup.lookup_code
AND arlup.lookup_type = 'INVOICE_TRX_STATUS'
AND rct.customer_trx_id = rctl.customer_trx_id
AND rctl.interface_line_context='ORDER ENTRY'
AND rctl.interface_line_attribute6 = p_return_attribute2
AND rctl.line_type = 'LINE'
AND rctl.interface_line_attribute11 = '0' --Bug2721441
AND rctl.org_id=rct.org_id
AND rownum = 1;
SELECT /* MOAC_SQL_CHANGE */ DISTINCT l.line_id line_id
FROM oe_order_lines_all l,
mtl_unit_transactions_all_v u,
mtl_material_transactions m,
oe_order_headers h
WHERE l.Inventory_item_id = to_number(attr1)
AND m.transaction_source_type_id=2
AND m.trx_source_line_id=l.line_id
AND m.transaction_id = u.transaction_id
AND m.transaction_type_id IN (33,34,50,62)
AND u.serial_number = attr2
AND u.inventory_item_id = to_number(attr1)
AND l.ship_from_org_id = m.organization_id
AND l.inventory_item_id = m.inventory_item_id
AND l.header_id = h.header_id
AND h.sold_to_org_id = p_sold_to_org_id
-- 6916542 AND h.transactional_curr_code = p_curr_code
AND l.cancelled_flag <> 'Y'
order by l.line_id;
SELECT /* MOAC_SQL_CHANGE */ DISTINCT l.line_id line_id
FROM mtl_material_transactions m,
mtl_transaction_lot_val_v t,
mtl_unit_transactions_all_v u,
oe_order_lines_all l,
oe_order_headers h
WHERE u.Inventory_item_id = to_number(attr1)
AND u.ORGANIZATION_ID = t.ORGANIZATION_ID
AND u.serial_number = attr2
AND t.serial_transaction_id = u.transaction_id
AND m.transaction_id = t.transaction_id
AND t.ORGANIZATION_ID = u.ORGANIZATION_ID
AND t.inventory_item_id = u.inventory_item_id
AND m.INVENTORY_ITEM_ID = l.inventory_item_id
AND m.ORGANIZATION_ID = l.ship_from_org_id
AND m.trx_source_line_id=l.line_id
AND m.transaction_source_type_id = 2
AND m.transaction_type_id IN (33,34,50,62)
AND l.cancelled_flag <> 'Y'
AND l.header_id = h.header_id
AND h.sold_to_org_id = p_sold_to_org_id
-- 6916542 AND h.transactional_curr_code = p_curr_code
order by l.line_id;
SELECT ls.line_id,ls.line_set_id,ls.from_serial_number,ls.to_serial_number
FROM oe_lot_serial_numbers ls, oe_order_lines ol
WHERE ls.line_id = ol.line_id
AND nvl(ol.cancelled_flag,'N') <> 'Y'
AND (ls.from_serial_number = p_serial_num OR ls.to_serial_number = p_serial_num );
SELECT line_id,line_set_id,from_serial_number,to_serial_number
FROM oe_lot_serial_numbers
WHERE from_serial_number = p_serial_num
OR to_serial_number = p_serial_num;
SELECT /* MOAC_SQL_CHANGE */ ooh.order_number, ott.name
FROM oe_order_lines_all ool,
oe_order_headers ooh,
oe_transaction_types_tl ott
WHERE ool.line_id = ord_line_id
AND ooh.header_id = ool.header_id
AND ott.transaction_type_id = ooh.order_type_id
-- 6916542 AND ooh.transactional_curr_code = p_curr_code
AND ooh.sold_to_org_id = p_sold_to_org_id
AND ott.language =
(select language_code
from fnd_languages
where installed_flag = 'B');
SELECT decode(msi.lot_control_code,2,'Y','N')
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_inventory_item_id
AND msi.organization_id =
OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID');
SELECT /* MOAC_SQL_CHANGE */ to_number(rctl.interface_line_attribute6)
INTO p_ref_line_id
FROM ra_customer_trx_lines_all rctl,
ra_customer_trx_all rct,
oe_order_lines_all l,
oe_order_headers h
WHERE to_number(p_return_attribute2) = rctl.customer_trx_line_id
and rctl.customer_trx_id = rct.customer_trx_id
AND l.line_id = rctl.interface_line_attribute6
AND l.header_id = h.header_id
AND p_curr_code = rct.invoice_currency_code
AND h.sold_to_org_id = p_sold_to_org_id
-- 6916542 AND h.transactional_curr_code = p_curr_code
and rctl.org_id=h.org_id;
SELECT count(*)
INTO l_index1
FROM OE_ORDER_LINES
WHERE reference_line_id = C_LINE.line_id
AND line_category_code = 'RETURN'
AND cancelled_flag <> 'Y';
SELECT count(*)
INTO l_index1
FROM OE_ORDER_LINES
WHERE reference_line_id = C_LINE.line_id
AND line_category_code = 'RETURN'
AND cancelled_flag <> 'Y';
select distinct reference_line_id
into l_ref_line_tbl(l_index2)
from oe_line_sets a,
oe_order_lines b
where a.set_id = C2.line_set_id
and a.line_id = b.line_id
and b.cancelled_flag <> 'Y';
select reference_line_id
into l_ref_line_tbl(l_index2)
from oe_order_lines
where line_id = C2.line_id;
SELECT /* MOAC_SQL_CHANGE */ rctl.customer_trx_line_id
INTO l_invoice_line_id
FROM ra_customer_trx_lines_all rctl,
ra_customer_trx rct,
ar_lookups arlup
WHERE rct.status_trx = arlup.lookup_code
AND arlup.lookup_type = 'INVOICE_TRX_STATUS'
AND rct.customer_trx_id = rctl.customer_trx_id
AND rctl.interface_line_context = 'ORDER ENTRY'
AND rctl.interface_line_attribute1 = to_char(l_order_number)
AND rctl.interface_line_attribute2 = l_trxn_type_name
AND rctl.interface_line_attribute6 = to_char(l_order_line_id)
AND rctl.line_type = 'LINE'
AND rctl.interface_line_attribute11 = '0' --Bug2721441
AND rctl.org_id=rct.org_id
AND rownum = 1;
SELECT /* MOAC_SQL_CHANGE */ rctl.customer_trx_line_id
INTO l_invoice_line_id
FROM ra_customer_trx_lines_all rctl,
ra_customer_trx rct,
ar_lookups arlup
WHERE rct.status_trx = arlup.lookup_code
AND arlup.lookup_type = 'INVOICE_TRX_STATUS'
AND rct.customer_trx_id = rctl.customer_trx_id
AND p_return_attribute2 = rctl.interface_line_attribute6
AND rctl.interface_line_context = 'ORDER ENTRY'
AND rctl.interface_line_attribute1 = to_char(l_order_number)
AND rctl.interface_line_attribute2 = l_trxn_type_name
AND rctl.line_type = 'LINE'
AND rctl.interface_line_attribute11 = '0' --Bug2721441
and rctl.org_id=rct.org_id
AND rownum = 1;
SELECT l.line_id
INTO l_order_line_id
FROM oe_order_lines l,
mtl_unit_transactions_all_v u,
mtl_material_transactions m
WHERE L.Inventory_item_id = to_number(p_return_attribute1)
AND m.transaction_id = u.transaction_id
AND l.line_category_code = 'ORDER'
AND m.transaction_source_type_id=2
AND m.trx_source_line_id=l.line_id
AND u.serial_number = p_return_attribute2
AND u.inventory_item_id = to_number(p_return_attribute1)
AND rownum = 1;
SELECT to_number(rctl.interface_line_attribute6)
INTO l_order_line_id
FROM ra_customer_trx_lines rctl
WHERE to_number(p_return_attribute2) = rctl.customer_trx_line_id;
SELECT /* MOAC_SQL_CHANGE */ rctl.quantity_invoiced,
rctl.tax_exempt_flag,
rctl.tax_exempt_reason_code,
rctl.tax_exempt_number,
rctl.uom_code
INTO l_quantity,
l_tax_exempt_flag,
l_tax_exempt_reason_code,
l_tax_exempt_number,
l_uom_code
FROM ra_customer_trx_lines_all rctl,
oe_order_lines l
WHERE rctl.customer_trx_line_id = p_invoice_line_id
AND to_number(rctl.interface_line_attribute6) = l.line_id;
select decode(revision_qty_control_code, 2, 'Y', 'N')
into l_revision_controlled
from mtl_system_items
where inventory_item_id = p_x_line_rec.inventory_item_id
and organization_id = OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID');
select distinct revision
into p_x_line_rec.item_revision
from mtl_material_transactions
where transaction_source_type_id = 2
and transaction_type_id = 33
and trx_source_line_id = p_x_line_rec.reference_line_id
and inventory_item_id = p_x_line_rec.inventory_item_id
and organization_id = (select ship_from_org_id
from oe_order_lines_all
where line_id = p_x_line_rec.reference_line_id);
** Insert_Rma_Options_Included and derive the pricing quantity
*/
IF l_debug_level > 0 THEN
oe_debug_pub.add( ' l_line_rec.ordered_quantity = '||l_line_rec.ordered_quantity,5 ) ;
For update operation need to raise an error for line_category_code. */
ELSIF g_line_rec.operation = oe_globals.g_opr_update
AND NOT OE_GLOBALS.EQUAL(g_line_rec.line_category_code
,p_old_line_rec.line_category_code)
THEN
--3365705For retrobill we change the order type from return to order
-- and the exception shouldn't be raised
IF (
g_line_rec.order_source_id = 27 AND
g_line_rec.retrobill_request_id is NOT NULL ) THEN
null;
SELECT 'Y'
INTO l_exists
FROM oe_def_condn_elems
WHERE value_string = 'ORGANIZATION'
AND attribute_code = 'PARTY_TYPE'
AND rownum = 1;
SELECT party.party_type
INTO l_party_type
FROM hz_cust_accounts cust_acct,
hz_parties party
WHERE party.party_id = cust_acct.party_id
AND cust_acct.cust_account_id = l_in_rec.sold_to_org_id;
IF p_x_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
Default_Active_Agr_Revision
( p_x_line_rec => p_x_line_rec,
p_old_line_rec => p_old_line_rec);
line is requested into a set and also gets cascaded if the operation is update
and the children of the model has been already created
this logic fires only when the scheduling branch profiel is set to Yes */
-- 4118431
--IF NVL(FND_PROFILE.VALUE('ONT_BRANCH_SCHEDULING'),'N') = 'Y'--Bug4504362
IF p_x_line_rec.line_id > 0 THEN
oe_Set_util.Default_line_set
(p_x_line_rec => p_x_line_rec,
p_old_line_rec => p_old_line_rec);
IF p_x_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE
THEN
model_option_update (p_x_line_rec => p_x_line_rec);