[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, item_type
INTO l_item_key, l_item_type --l_header_id ame er 16084377
FROM wf_item_activity_statuses
WHERE notification_id = TO_NUMBER (document_id);
SELECT item_key, item_type
INTO l_item_key, l_item_type --l_header_id ame er 16084377
FROM wf_item_activity_statuses_h
WHERE notification_id = TO_NUMBER (document_id);
SELECT org_id
INTO l_org_id
FROM oe_order_headers_all
WHERE header_id = l_header_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 item_key
INTO l_line_id
FROM wf_item_activity_statuses_h
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_hdr AND item_key = TO_CHAR (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);
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_lin
AND item_key = TO_CHAR (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);
SELECT COUNT (1)
INTO l_count
FROM wf_items
--where item_type=OE_GLOBALS.G_WFI_NGO
WHERE item_type = oe_globals.g_wfi_bkt --13020709
AND item_key = TO_CHAR (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_ORDER_WF_UTIL: DELETE TYPE NOT IN HEADER,LINE,NEGOTIATE,BLANKET'
);
oe_msg_pub.add_exc_msg (g_pkg_name, '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);
( 'Update_Flow_Status_Code for Quote:'
|| p_flow_status_code,
5
);
UPDATE oe_order_headers_all
SET flow_status_code = p_flow_status_code
--Bug 8435596
,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
lock_control = lock_control + 1
WHERE header_id = p_header_id;
UPDATE oe_order_lines_all
SET flow_status_code = p_flow_status_code
--Bug 8435596
,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
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
);
( '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_globals.g_opr_update;
( '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
--Bug 8435596
,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
lock_control = lock_control + 1
WHERE header_id = p_header_id;
( '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
--Bug 8435596
,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
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_updated_by =
NVL (oe_standard_wf.g_user_id, fnd_global.user_id),
-- For bug 9386040
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
);
( '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_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. *
***********************************************************/
IF l_debug_level > 0
THEN
oe_debug_pub.ADD
( ' GENESIS : UPDATE FLOW STATUS - p_header_id: '
|| p_header_id
);
( ' GENESIS : UPDATE FLOW STATUS - p_line_id: '
|| p_line_id
);
( ' GENESIS : UPDATE FLOW STATUS - l_header_rec.order_source_id: '
|| l_header_rec.order_source_id
);
( ' GENESIS : UPDATE FLOW STATUS - l_order_source_id: '
|| l_order_source_id
);
( ' GENESIS : UPDATE FLOW STATUS - l_line_tbl(1).order_source_id: '
|| l_line_tbl (1).order_source_id
);
( ' GENESIS : UPDATE FLOW STATUS - l_line_tbl(1).header_id: '
|| l_line_tbl (1).header_id
);
( ' GENESIS : UPDATE FLOW STATUS - g_aso_installed: '
|| oe_globals.g_aso_installed
);
( ' GENESIS : UPDATE FLOW STATUS - DBI Installed: '
|| NVL (fnd_profile.VALUE ('ONT_DBI_INSTALLED'),
'No.'
)
);
( ' GENESIS : UPDATE FLOW STATUS - p_flow_status_code: '
|| p_flow_status_code
);
SELECT oe_xml_message_seq_s.NEXTVAL
INTO l_itemkey_sso
FROM DUAL;
( ' 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
--Bug 8435596
,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
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
);
( '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_globals.g_opr_update;
('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);
oe_msg_pub.add_exc_msg (g_pkg_name, '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
--Bug 8435596
,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE header_id = l_header_id;
UPDATE oe_order_lines_all
SET flow_status_code = p_flow_status_code
--Bug 8435596
,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
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,
-- Bug 12849250
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE header_id = l_header_id;
l_updated_flag := 'Y';
UPDATE oe_order_lines_all
SET open_flag = p_open_flag,
-- Bug 12849250
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE header_id = l_header_id;
UPDATE oe_order_headers_all
SET draft_submitted_flag = p_draft_submitted_flag,
-- Bug 12849250
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
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
);
( '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
--Bug 8435596
,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE header_id = l_header_id;
l_updated_flag := 'Y';
UPDATE oe_blanket_headers_all
SET open_flag = p_open_flag,
-- Bug 12849250
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE header_id = l_header_id;
UPDATE oe_blanket_lines_all
SET open_flag = p_open_flag,
-- Bug 12849250
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE header_id = l_header_id;
l_updated_flag := 'Y';
UPDATE oe_blanket_headers_all
SET draft_submitted_flag = p_draft_submitted_flag,
-- Bug 12849250
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
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);
oe_msg_pub.add_exc_msg (g_pkg_name, '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 item_key, item_type
INTO l_item_key, l_item_type
FROM wf_item_activity_statuses_h
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, headers.org_id
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, l_org_id
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 TO_CHAR (hs.hold_entity_id) =
TO_CHAR (p_hold_entity_id)
--9371206
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);