[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select wf_assign.process_name
from oe_workflow_assignments wf_assign,
oe_order_headers_all header
where header.header_id = to_number(itemkey)
and header.order_type_id = wf_assign.order_type_id
and sysdate >= wf_assign.start_date_active
and trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
and wf_assign.line_type_id IS NULL
and nvl(wf_assign.wf_item_type, OE_GLOBALS.G_WFI_HDR) = OE_GLOBALS.G_WFI_HDR;
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign,
oe_order_headers_all header
WHERE nvl(p_wfasgn_item_type,'-99') =
nvl(wf_assign.item_type_code,nvl(p_wfasgn_item_type,'-99'))
AND header.header_id = p_line_rec.header_id AND
header.order_type_id = wf_assign.order_type_id
AND p_line_rec.line_type_id = wf_assign.line_type_id
AND wf_assign.line_type_id IS NOT NULL
AND sysdate >= wf_assign.start_date_active
AND trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
Order by wf_assign.item_type_code;
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign,
oe_order_headers_all header
WHERE header.header_id = to_number(itemkey)
and header.order_type_id = wf_assign.order_type_id
and sysdate >= wf_assign.start_date_active
and trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
and wf_assign.wf_item_type = OE_GLOBALS.G_WFI_NGO
and wf_assign.line_type_id IS NULL;
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign,
oe_blanket_headers_all header
WHERE header.header_id = to_number(itemkey)
and header.order_type_id = wf_assign.order_type_id
and sysdate >= wf_assign.start_date_active
and trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
and wf_assign.wf_item_type = OE_GLOBALS.G_WFI_NGO
and wf_assign.line_type_id IS NULL;
SELECT wf_assign.process_name
FROM oe_workflow_assignments wf_assign,
oe_blanket_headers_all blanket
WHERE blanket.header_id = to_number(itemkey)
and blanket.order_type_id = wf_assign.order_type_id
and sysdate >= wf_assign.start_date_active
and trunc(sysdate) <= nvl(wf_assign.end_date_active, sysdate)
and wf_assign.wf_item_type = OE_GLOBALS.G_WFI_BKT
and wf_assign.line_type_id IS NULL;
SELECT item_key
INTO l_header_id
FROM wf_item_activity_statuses
where notification_id = to_number(document_id);
SELECT order_number, order_type_id, order_category_code
into l_order_number, l_order_type_id, l_order_category_code
from oe_order_headers_all
where header_id = l_header_id;
SELECT T.NAME
INTO l_order_type_name
FROM OE_TRANSACTION_TYPES_TL T
WHERE T.LANGUAGE = userenv('LANG')
AND T.TRANSACTION_TYPE_ID = l_order_type_id;
SELECT item_key
INTO l_line_id
FROM wf_item_activity_statuses
where notification_id = to_number(document_id);
SELECT header_id, line_number, shipment_number, option_number, service_number
into l_header_id, l_line_number, l_shipment_number, l_option_number, l_service_number
FROM oe_order_lines_all
WHERE line_id = l_line_id;
SELECT order_number, order_type_id, order_category_code
into l_order_number, l_order_type_id, l_order_category_code
from oe_order_headers_all
where header_id = l_header_id;
SELECT T.NAME
INTO l_order_type_name
FROM OE_TRANSACTION_TYPES_TL T
WHERE T.LANGUAGE = userenv('LANG')
AND T.TRANSACTION_TYPE_ID = l_order_type_id;
SELECT count(1)
INTO l_count
FROM wf_items
WHERE item_type=OE_GLOBALS.G_WFI_NGO
AND item_key =to_char(p_header_rec.header_id);
SELECT user_name
INTO l_owner_role
FROM FND_USER
WHERE USER_ID = FND_GLOBAL.USER_ID;
select user_name
into l_user_name
from fnd_user
where user_id = FND_GLOBAL.USER_ID;
SELECT user_name
INTO l_owner_role
FROM FND_USER
WHERE USER_ID = FND_GLOBAL.USER_ID;
Select order_type_id
into l_type_id
from oe_order_headers_all
where header_id = OE_GLOBALS.G_START_HEADER_FLOW;
Select line_type_id, item_type_code,
ato_line_id, line_id
into l_type_id, l_item_type_code,
l_ato_line_id, l_line_id
from oe_order_lines_all
where line_id = OE_GLOBALS.G_START_LINE_FLOWS_TBL(ctr).line_id;
OE_GLOBALS.G_START_LINE_FLOWS_TBL.DELETE;
Select header_id
into l_header_id
from oe_order_headers_all
where header_id = OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW;
Select header_id
into l_header_id
from oe_blanket_headers_all
where header_id = OE_GLOBALS.G_START_NEGOTIATE_HEADER_FLOW;
Select header_id
into l_header_id
from oe_blanket_headers_all
where header_id = OE_GLOBALS.G_START_BLANKET_HEADER_FLOW;
OE_GLOBALS.G_START_LINE_FLOWS_TBL.DELETE;
PROCEDURE Delete_Row
( p_type IN VARCHAR2,
p_id IN NUMBER
) IS
l_status VARCHAR2(30);
oe_debug_pub.add( 'IN OE_ORDER_WF_UTIL.DELETE_ROW' ) ;
select count(1)
into l_count
from wf_items
where item_type=OE_GLOBALS.G_WFI_NGO
and item_key=to_char(p_id);
SELECT transaction_phase_code
INTO l_transaction_phase_code
FROM oe_order_lines_all
WHERE line_id = p_id;
select count(1)
into l_count
from wf_items
where item_type=OE_GLOBALS.G_WFI_NGO
and item_key=to_char(p_id);
oe_debug_pub.add( 'OE_ORDER_WF_UTIL: DELETE TYPE NOT IN HEADER,LINE,NEGOTIATE,BLANKET' ) ;
, 'Delete_Row'
);
oe_debug_pub.add( 'EXITING OE_ORDER_WF_UTIL.DELETE_ROW' ) ;
END Delete_Row;
PROCEDURE Update_Flow_Status_Code
(
p_header_id IN NUMBER DEFAULT NULL,
p_line_id IN NUMBER DEFAULT NULL,
p_flow_status_code IN VARCHAR2,
p_item_type IN VARCHAR2 DEFAULT NULL,
p_sales_document_type_code IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_flow_status_code VARCHAR2(30);
SELECT line_id, lock_control
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = p_header_id
FOR UPDATE NOWAIT;
oe_debug_pub.add('ENTERING UPDATE_FLOW_STATUS_CODE' , 5 ) ;
SAVEPOINT UPDATE_FLOW_STATUS_CODE;
SELECT lookup_code
INTO l_flow_status_code
FROM oe_lookups
WHERE lookup_type= 'FLOW_STATUS'
AND lookup_code = p_flow_status_code
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE);
oe_debug_pub.add('Update_Flow_Status_Code for Quote:' || p_flow_status_code, 5 ) ;
UPDATE OE_ORDER_HEADERS_ALL
SET FLOW_STATUS_CODE = p_flow_status_code
, LOCK_CONTROL = LOCK_CONTROL + 1
WHERE HEADER_ID = p_header_id;
UPDATE OE_ORDER_LINES_ALL
SET FLOW_STATUS_CODE = p_flow_status_code,
LOCK_CONTROL = LOCK_CONTROL + 1
WHERE HEADER_ID = p_header_id;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_old_header_rec => l_old_header_rec,
p_Header_rec => l_header_rec,
p_header_id => p_header_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add('UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: ' || L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
OE_ORDER_UTIL.g_header_rec.operation:=OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add('Update_Flow_Status_Code for Blanket Negotiation:' || p_flow_status_code, 5 ) ;
SELECT lock_control
INTO l_blanket_lock_control
FROM oe_blanket_headers_all
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
UPDATE OE_BLANKET_HEADERS_ALL
SET FLOW_STATUS_CODE = p_flow_status_code
, LOCK_CONTROL = LOCK_CONTROL + 1
WHERE HEADER_ID = p_header_id;
oe_debug_pub.add('Update_Flow_Status_Code for Blanket Fulfillment:' || p_flow_status_code, 5 ) ;
SELECT lock_control
INTO l_blanket_lock_control
FROM oe_blanket_headers_all
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
UPDATE OE_BLANKET_HEADERS_ALL
SET FLOW_STATUS_CODE = p_flow_status_code
, LOCK_CONTROL = LOCK_CONTROL + 1
WHERE HEADER_ID = p_header_id;
SELECT lookup_code
INTO l_flow_status_code
FROM oe_lookups
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = p_flow_status_code
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE);
SELECT source_document_id,
source_document_line_id,
header_id,
orig_sys_document_ref,
orig_sys_line_ref,
order_source_id,
orig_sys_shipment_ref,
change_sequence,
source_document_type_id
INTO l_source_document_id,
l_source_document_line_id,
l_header_id,
l_orig_sys_document_ref,
l_orig_sys_line_ref,
l_order_source_id,
l_orig_sys_shipment_ref,
l_change_sequence,
l_source_document_type_id
FROM OE_ORDER_LINES_ALL
WHERE line_id = p_line_id
FOR UPDATE NOWAIT;
SELECT lookup_code
INTO l_flow_status_code
FROM oe_lookups
WHERE lookup_type = 'FLOW_STATUS'
AND lookup_code = p_flow_status_code
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE);
UPDATE OE_ORDER_LINES_ALL
SET FLOW_STATUS_CODE = p_flow_status_code,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
LOCK_CONTROL = LOCK_CONTROL + 1
WHERE LINE_ID = p_line_id;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_header_id => l_line_tbl(1).header_id,
p_old_line_rec => l_old_line_tbl(1),
p_line_rec =>l_line_tbl(1),
p_line_id => p_line_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE LINE FLOW STATUS CODE IS: ' || L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_line_tbl(l_index).last_update_date:=l_line_tbl(1).last_update_date;
OE_ORDER_UTIL.g_line_tbl(l_index).operation:=OE_GLOBALS.G_OPR_UPDATE;
* the update_flow_status is getting called directly. So *
* we need to call synch_header_line for AIA enabled order *
* sources. *
***********************************************************/
-- Corrected the debug message texts while working on
-- Bug 7261522.
IF l_debug_level > 0 THEN
oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - p_header_id: ' || p_header_id);
oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - p_line_id: ' || p_line_id);
oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - l_header_rec.order_source_id: ' || l_header_rec.order_source_id);
oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - l_order_source_id: ' || l_order_source_id);
oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - l_line_tbl(1).order_source_id: ' || l_line_tbl(1).order_source_id);
oe_debug_pub.add(' GENESIS : UPDATE FLOW STATUS - l_line_tbl(1).header_id: '||l_line_tbl(1).header_id);
select OE_XML_MESSAGE_SEQ_S.nextval
into l_itemkey_sso
from dual;
oe_debug_pub.add( ' GENESIS : UPDATE FLOW STATUS - l_itemkey_sso'||l_itemkey_sso);
oe_debug_pub.add( ' GENESIS : UPDATE FLOW STATUS');
OE_SYNC_ORDER_PVT.INSERT_SYNC_lINE(P_LINE_rec => l_line_tbl(1),
p_change_type => 'LINE_STATUS',
p_req_id => l_itemkey_sso,
X_RETURN_STATUS => L_RETURN_STATUS);
UPDATE OE_ORDER_HEADERS_ALL
SET FLOW_STATUS_CODE = p_flow_status_code
, LOCK_CONTROL = LOCK_CONTROL + 1
WHERE HEADER_ID = p_header_id;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_old_header_rec => l_old_header_rec,
p_Header_rec =>l_header_rec,
p_header_id => p_header_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: ' || L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
OE_ORDER_UTIL.g_header_rec.operation:=OE_GLOBALS.G_OPR_UPDATE;
oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: HEADER_ID AND LINE_ID ARE NULL' ) ;
oe_debug_pub.add('ENTERING UPDATE_FLOW_STATUS_CODE' , 5 ) ;
oe_debug_pub.add('EXITING UPDATE_FLOW_STATUS_CODE' , 5 ) ;
ROLLBACK TO UPDATE_FLOW_STATUS_CODE;
oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: NO_DATA_FOUND' ) ;
ROLLBACK TO UPDATE_FLOW_STATUS_CODE;
oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: ERROR' , 5 ) ;
ROLLBACK TO UPDATE_FLOW_STATUS_CODE;
oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: LOCK EXC' , 5 ) ;
ROLLBACK TO UPDATE_FLOW_STATUS_CODE;
oe_debug_pub.add( 'UPDATE_FLOW_STATUS_CODE: UNEXP ERROR' , 5 ) ;
, 'Update_Flow_Status_Code'
);
END Update_Flow_Status_Code;
SELECT CREATED_BY
INTO v_id
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
SELECT HEADER_ID
INTO v_header_id
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = TO_NUMBER(ITEMKEY);
SELECT CREATED_BY
INTO v_id
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = v_header_id;
SELECT USER_NAME
INTO v_value
FROM FND_USER
WHERE USER_ID = v_id
AND (EMPLOYEE_ID is null
OR
EMPLOYEE_ID in (SELECT PERSON_ID
FROM PER_PEOPLE_F));
SELECT SALESREP_ID
INTO v_id
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
SELECT CREATED_BY
INTO v_id
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
SELECT CREATED_BY
INTO v_id
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = TO_NUMBER(ITEMKEY);
SELECT USER_NAME
INTO v_value
FROM FND_USER
WHERE USER_ID = v_id
AND (EMPLOYEE_ID is null
OR
EMPLOYEE_ID in (SELECT PERSON_ID
FROM PER_PEOPLE_F));
SELECT SALESREP_ID
INTO v_id
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID = TO_NUMBER(ITEMKEY);
SELECT SALESREP_ID
INTO v_id
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = TO_NUMBER(ITEMKEY);
PROCEDURE Update_Quote_Blanket(p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_flow_status_code IN VARCHAR2 DEFAULT NULL,
p_open_flag IN VARCHAR2 DEFAULT NULL,
p_draft_submitted_flag IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_header_id NUMBER;
l_updated_flag VARCHAR2(1) := 'N';
SELECT line_id, lock_control
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID = l_header_id
FOR UPDATE NOWAIT;
SELECT line_id, lock_control
FROM OE_BLANKET_LINES_ALL
WHERE HEADER_ID = l_header_id
FOR UPDATE NOWAIT;
oe_debug_pub.add( 'ENTERING UPDATE_QUOTE_BLANKET' , 5 ) ;
SAVEPOINT UPDATE_QUOTE_BLANKET;
SELECT lookup_code
INTO l_flow_status_code
FROM oe_lookups
WHERE lookup_type= 'FLOW_STATUS'
AND lookup_code = p_flow_status_code
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE);
UPDATE OE_ORDER_HEADERS_ALL
SET FLOW_STATUS_CODE = p_flow_status_code
WHERE HEADER_ID = l_header_id;
UPDATE OE_ORDER_LINES_ALL
SET FLOW_STATUS_CODE = p_flow_status_code,
LOCK_CONTROL = LOCk_CONTROL + 1
WHERE HEADER_ID = l_header_id;
l_updated_flag := 'Y';
UPDATE OE_ORDER_HEADERS_ALL
SET OPEN_FLAG = p_open_flag
WHERE HEADER_ID = l_header_id;
l_updated_flag := 'Y';
UPDATE OE_ORDER_LINES_ALL
SET OPEN_FLAG = p_open_flag
WHERE HEADER_ID = l_header_id;
UPDATE OE_ORDER_HEADERS_ALL
SET DRAFT_SUBMITTED_FLAG = p_draft_submitted_flag
WHERE HEADER_ID = l_header_id;
l_updated_flag := 'Y';
IF l_updated_flag = 'Y' THEN
UPDATE OE_ORDER_HEADERS_ALL
SET LOCK_CONTROL = LOCK_CONTROL + 1
WHERE HEADER_ID = l_header_id;
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists =>False,
p_old_header_rec => l_old_header_rec,
p_Header_rec =>l_header_rec,
p_header_id => l_header_id,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_WF_ORDER_UTIL.UPDATE HEADER FLOW STATUS CODE IS: ' || L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
OE_ORDER_UTIL.g_header_rec.operation:=OE_GLOBALS.G_OPR_UPDATE;
SELECT lock_control
INTO l_blanket_lock_control
FROM oe_blanket_headers_all
WHERE header_id = l_header_id
FOR UPDATE NOWAIT;
UPDATE OE_BLANKET_HEADERS_ALL
SET FLOW_STATUS_CODE = p_flow_status_code
WHERE HEADER_ID = l_header_id;
l_updated_flag := 'Y';
UPDATE OE_BLANKET_HEADERS_ALL
SET OPEN_FLAG = p_open_flag
WHERE HEADER_ID = l_header_id;
update OE_BLANKET_LINES_ALL
SET OPEN_FLAG = p_open_flag
WHERE HEADER_ID = l_header_id;
l_updated_flag := 'Y';
UPDATE OE_BLANKET_HEADERS_ALL
SET DRAFT_SUBMITTED_FLAG = p_draft_submitted_flag
WHERE HEADER_ID = l_header_id;
l_updated_flag := 'Y';
IF l_updated_flag = 'Y' THEN
UPDATE OE_BLANKET_HEADERS_ALL
SET LOCK_CONTROL = LOCK_CONTROL + 1
WHERE HEADER_ID = l_header_id;
oe_debug_pub.add( 'EXITING UPDATE_QUOTE_BLANKET' , 5 ) ;
ROLLBACK TO UPDATE_QUOTE_BLANKET;
oe_debug_pub.add( 'UPDATE_QUOTE_BLANKET: NO_DATA_FOUND' ) ;
ROLLBACK TO UPDATE_QUOTE_BLANKET;
oe_debug_pub.add( 'UPDATE_QUOTE_BLANKET: ERROR' , 5 ) ;
ROLLBACK TO UPDATE_QUOTE_BLANKET;
oe_debug_pub.add( 'UPDATE_QUOTE_BLANKET: LOCK EXC' , 5 ) ;
ROLLBACK TO UPDATE_QUOTE_BLANKET;
oe_debug_pub.add( 'UPDATE_QUOTE_BLANKET: UNEXP ERROR' , 5 ) ;
, 'Update_Quote_Blanket'
);
END Update_Quote_Blanket;
select name
into l_valid_process
from wf_activities
where item_type=p_item_type
and name=p_process_name
and runnable_flag = 'Y'
and end_date is null;
SELECT user_name
INTO l_owner_role
FROM FND_USER
WHERE USER_ID = p_user_id;
SELECT count(1)
INTO l_count
FROM wf_items
WHERE item_type=OE_GLOBALS.G_WFI_NGO
AND item_key =to_char(p_header_id);
SELECT user_name
INTO l_owner_role
FROM FND_USER
WHERE USER_ID = FND_GLOBAL.USER_ID;
select user_name
into l_user_name
from fnd_user
where user_id = FND_GLOBAL.USER_ID;
select ITEM_KEY, ITEM_TYPE
into l_item_key, l_item_type
from WF_ITEM_ACTIVITY_STATUSES
where NOTIFICATION_ID = to_number(document_id);
select /* MOAC_SQL_CHANGE */ headers.order_number, headers.CREATION_DATE,
shipto.location name,
invoiceto.LOCATION name,
terms.NAME,
blnk_ext.BLANKET_MIN_AMOUNT, blnk_ext.BLANKET_MAX_AMOUNT,
blnk_ext.START_DATE_ACTIVE, blnk_ext.END_DATE_ACTIVE
INTO l_order_number, l_creation_date, l_ship_to_address, l_invoice_to_address,
l_payment_term,
l_blanket_min_amount, l_blanket_max_amount,
l_start_date_active, l_end_date_active
FROM oe_blanket_headers_all headers,
oe_blanket_headers_ext blnk_ext,
hz_cust_site_uses_all shipto,
hz_cust_site_uses_all invoiceto,
ra_terms_tl terms
where headers.header_id = l_transaction_id
and headers.order_number = blnk_ext.order_number(+)
and headers.ship_to_org_id = shipto.site_use_id(+)
and shipto.site_use_code(+) = 'SHIP_TO'
and shipto.org_id(+) = headers.org_id
and headers.invoice_to_org_id = invoiceto.site_use_id(+)
and invoiceto.site_use_code(+) = 'BILL_TO'
and invoiceto.org_id(+) = headers.org_id
and headers.payment_term_id = terms.term_id(+)
and terms.language(+) = userenv('LANG');
SELECT ol.line_number || '.' ||ol.shipment_number ||'.' ||
ol.option_number ||'.'|| ol.component_number,
msi.concatenated_segments,
ol.order_quantity_uom,
ol.ordered_quantity,
ol.unit_selling_price,
ol.ordered_quantity * ol.unit_selling_price,
ol.line_id, --- the following column needed for line_margin API
ol.inventory_item_id,
ol.item_type_code,
ol.open_flag,
ol.SHIPPED_QUANTITY,
ol.ORDERED_QUANTITY,
ol.SOURCE_TYPE_CODE,
ol.SHIP_FROM_ORG_ID,
ol.PROJECT_ID,
ol.ACTUAL_SHIPMENT_DATE,
ol.FULFILLMENT_DATE
FROM oe_order_lines_all ol,
mtl_system_items_kfv msi
WHERE ol.header_id = v_header_id
AND ol.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = nvl(ol.ship_from_org_id,
oe_sys_parameters.Value('MASTER_ORGANIZATION_ID')) -- Bug 6215694
ORDER BY line_number, shipment_number, option_number, component_number;
select org_id into l_org_id from oe_order_headers_all where header_id = l_transaction_id;
select /* MOAC_SQL_CHANGE */ headers.CREATION_DATE,shipto.name,
invoiceto.LOCATION name,
headers.TRANSACTIONAL_CURR_CODE, terms.NAME
INTO l_creation_date, l_ship_to_address, l_invoice_to_address,
l_transactional_curr_code, l_payment_term
FROM oe_order_headers_all headers,
oe_ship_to_orgs_v shipto,
HZ_CUST_SITE_USES_ALL invoiceto,
ra_terms terms
where headers.header_id = l_transaction_id
and headers.SHIP_TO_ORG_ID = shipto.organization_id(+)
and headers.INVOICE_TO_ORG_ID = invoiceto.SITE_USE_ID(+)
and invoiceto.SITE_USE_CODE(+) = 'BILL_TO'
and invoiceto.ORG_ID(+) = headers.org_id
and headers.payment_term_id = terms.term_id(+);
select 'Y'
into l_result_out
from oe_hold_sources HS,
oe_hold_definitions h
where HS.hold_entity_code = p_hold_entity_code
and HS.hold_entity_id = p_hold_entity_id
and HS.hold_id = 1
and HS.released_flag = 'N'
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >= ROUND( SYSDATE )
AND hs.hold_id = h.hold_id
AND SYSDATE BETWEEN NVL( H.START_DATE_ACTIVE, SYSDATE )
AND NVL( H.END_DATE_ACTIVE, SYSDATE );
select pa.activity_name
from wf_item_activity_statuses s,
wf_process_activities pa, wf_lookups l,
wf_activities_vl act
where s.activity_status = l.lookup_code
and l.lookup_type = l_wfeng_status
and s.process_activity = pa.instance_id
and pa.activity_item_type = act.item_type
and pa.activity_name = act.name
and pa.process_name <> l_root
and act.version = (select max(version)
from wf_activities_vl act2
where act.item_type = act2.item_type
and act.name = act2.name)
and upper(s.activity_status) = l_eng_notified
and s.item_type = l_oenh
and s.item_key = p_header_id
and act.function = l_standard_block;
SELECT 'Y'
FROM WF_ITEM_ACTIVITY_STATUSES WIAS
, WF_PROCESS_ACTIVITIES WPA
WHERE WIAS.item_type = 'OEOH'
AND WIAS.item_key = p_header_id
AND WIAS.activity_status = 'NOTIFIED'
AND WPA.activity_name = 'BOOK_ELIGIBLE'
AND WPA.instance_id = WIAS.process_activity;
SELECT 'Y'
FROM WF_ITEM_ACTIVITY_STATUSES WIAS
, WF_PROCESS_ACTIVITIES WPA
WHERE WIAS.item_type = 'OEOH'
AND WIAS.item_key = p_header_id
AND WIAS.activity_status = 'DEFERRED'
AND WPA.activity_name = 'BOOK_DEFER'
AND WPA.instance_id = WIAS.process_activity;
select booked_flag, flow_status_code
into l_booked_flag, l_flow_status_code
from oe_order_headers
where header_id = p_header_id;
select MEANING
into l_flow_status
from oe_lookups
WHERE lookup_type= 'FLOW_STATUS'
AND lookup_code = l_flow_status_code
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE);