[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_NAME => 'ONT_SEND_STATUS_UPDATE_NOTIFICATION' ,
X_VALUE => p_value ,
X_LEVEL_NAME => 'USER' ,
X_LEVEL_VALUE => To_Char(p_user_id),
X_LEVEL_VALUE_APP_ID => NULL,
X_LEVEL_VALUE2 => NULL );
l_header_text := 'Order Number# ' || l_header_rec.order_number || ' has an update of type '
||l_event_type ||newline
||newline
||'Header Details' || newline
||'---------------------' || newline
||'Order Number : ' || l_header_rec.order_number || newline
||'Order Status : ' || l_header_rec.flow_status_code || newline
||'Ordered Date : ' || l_header_rec.ordered_date || newline
||'Request Date : ' || l_header_rec.request_date ;
SELECT item
INTO l_item_name
FROM oe_items_v
WHERE item_id = l_line_rec.ordered_item_id
AND inventory_item_id = l_line_rec.inventory_item_id
AND item_identifier_type = l_line_rec.item_identifier_type;
SELECT to_char(l_line_rec.line_number) ||
decode(l_line_rec.shipment_number, null, null, '.'
|| to_char(l_line_rec.shipment_number))||
decode(l_line_rec.option_number, null, null, '.'
|| to_char(l_line_rec.option_number)) ||
decode(l_line_rec.component_number, null, null,
decode(l_line_rec.option_number, null, '.',null)||
'.'||to_char(l_line_rec.component_number))||
decode(l_line_rec.service_number,null,null,
decode(l_line_rec.component_number, null, '.' , null) ||
decode(l_line_rec.option_number, null, '.', null ) ||
'.'|| to_char(l_line_rec.service_number)) LINE_NUM
INTO l_line_num
FROM dual;
SELECT item
INTO l_item_name
FROM oe_items_v
WHERE item_id = l_line_rec.ordered_item_id
AND inventory_item_id = l_line_rec.inventory_item_id
AND item_identifier_type = l_line_rec.item_identifier_type;
SELECT to_char(l_line_rec.line_number) ||
decode(l_line_rec.shipment_number, null, null, '.'
|| to_char(l_line_rec.shipment_number))||
decode(l_line_rec.option_number, null, null, '.'
|| to_char(l_line_rec.option_number)) ||
decode(l_line_rec.component_number, null, null,
decode(l_line_rec.option_number, null, '.',null)||
'.'||to_char(l_line_rec.component_number))||
decode(l_line_rec.service_number,null,null,
decode(l_line_rec.component_number, null, '.' , null) ||
decode(l_line_rec.option_number, null, '.', null ) ||
'.'|| to_char(l_line_rec.service_number)) LINE_NUM
INTO l_line_num
FROM dual;
SELECT employee_id
INTO l_eid
FROM fnd_user
WHERE user_id = G_USER_TAB(idx).user_id;
SELECT name
INTO l_role_name
FROM wf_roles
WHERE orig_system = 'FND_USR'
AND orig_system_id = G_USER_TAB(idx).user_id;
SELECT name
INTO l_role_name
FROM wf_roles
WHERE orig_system = 'PER'
AND parent_orig_system = 'HZ_PARTY'
AND orig_system_id = l_eid;
l_nid := WF_NOTIFICATION.Send(l_role_name, 'OENF', 'ORDER_STATUS_UPDATED');
SELECT item_type_code
INTO l_item_type
FROM oe_order_lines_all
WHERE line_id = l_line_id;
SELECT invoice_to_org_id, sold_to_org_id
INTO l_invoice_to_org_id, l_sold_to_org_id
FROM oe_order_headers_all
WHERE header_id = l_header_id;
SELECT cust.cust_account_id
INTO l_customer_id
FROM hz_cust_site_uses_all site,
hz_cust_acct_sites_all cas,
hz_cust_accounts cust,
hz_parties party
WHERE site.site_use_code = 'BILL_TO'
AND site.site_use_id = l_invoice_to_org_id
AND site.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = cust.cust_account_id
AND cust.party_id=party.party_id;
SELECT DISTINCT user_id, user_name -- 12616799
BULK COLLECT INTO G_USER_TAB
FROM (
SELECT fu.user_id, fu.user_name
FROM hz_cust_account_roles hcar,
fnd_user fu
WHERE hcar.cust_account_id = l_customer_id
AND hcar.party_id = fu.customer_id
AND hcar.role_type = 'CONTACT'
AND hcar.current_role_state = 'A'
-- AND hcar.cust_acct_site_id IS NULL -- 12616799
AND (hcar.end_date IS NULL OR hcar.end_date > SYSDATE)
AND (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND fnd_profile.value_specific('ONT_SEND_STATUS_UPDATE_NOTIFICATION',fu.user_id) = 'Y'
UNION
SELECT fu.user_id, fu.user_name
FROM hz_cust_account_roles hcar,
fnd_user fu,
hz_relationships hr
WHERE hcar.cust_account_id = l_customer_id
AND hcar.party_id = hr.party_id
AND hcar.role_type = 'CONTACT'
AND hcar.current_role_state = 'A'
-- AND hcar.cust_acct_site_id IS NULL -- 12616799
AND (hcar.end_date IS NULL OR hcar.end_date > SYSDATE)
AND hr.subject_id = fu.customer_id
AND hr.party_id = hcar.party_id
AND hr.relationship_code = 'CONTACT_OF'
AND hr.status = 'A'
AND hr.subject_table_name = 'HZ_PARTIES'
AND (hr.end_date IS NULL OR hr.end_date > SYSDATE)
AND (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND fnd_profile.value_specific('ONT_SEND_STATUS_UPDATE_NOTIFICATION',fu.user_id) = 'Y'
);