[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT delivery_detail_id,
source_header_id,
source_line_id,
source_header_number,
source_line_number,
inventory_item_id,
organization_id,
ship_to_location_id
FROM WSH_DLVY_DELIVERABLES_V
WHERE delivery_id = p_delivery_id
Order by delivery_detail_id;
SELECT party_id
FROM hz_party_sites
WHERE party_site_id = p_ship_to_location_id;
SELECT DISTINCT CATEGORY_DESCRIPTION,
FILE_NAME,
USER_ENTITY_NAME,
MEDIA_ID,
DOCUMENT_ID
FROM FND_ATTACHED_DOCS_FORM_VL
WHERE (security_type=4 OR publish_flag='Y')
AND ((entity_name= 'OE_ORDER_LINES'
AND pk1_value = LocalDeliverydetail.source_line_id )
OR (entity_name= 'WSH_DELIVERY_DETAILS'
AND pk1_value = LocalDeliverydetail.delivery_detail_id ))
AND CATEGORY_ID IN (SELECT category_id
FROM FND_DOCUMENT_CATEGORIES_VL
WHERE UPPER(user_name) like l_bind_var_msds
OR UPPER(user_name) like l_bind_var_safety
OR UPPER(user_name) like l_bind_var_hazard)
/*
** 28-May-2004 Mercy Thomas 3211481 removed the column seq_num from the order by clause
*/
order by user_entity_name;
Select segment1
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_organization_id;
/* Call Update Dispatch History API with creation_source = 1 (Internal)*/
GR_DISPATCH_HISTORY_PUB.create_dispatch_history (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_item => l_item_code,
p_organization_id => LocalDeliverydetail.organization_id,
p_inventory_item_id => LocalDeliverydetail.inventory_item_id,
p_recipient_id => l_recipient_id,
p_recipient_site_id => LocalDeliverydetail.ship_to_location_id,
p_date_sent => SYSDATE,
p_dispatch_method_code => 3,
p_document_id => localAttachDocDetail.document_id,
p_user_id => l_user_id,
p_creation_source => 1,
p_cas_number => NULL,
p_document_location => NULL,
p_document_name => NULL,
p_document_version => NULL,
p_document_category => NULL,
p_file_format => NULL,
p_file_description => NULL,
p_document_code => NULL,
p_disclosure_code => NULL,
p_language => NULL,
p_organization_code => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_error_msg );
SELECT delivery_detail_id,
source_line_id,
inventory_item_id ,
organization_id,
ship_to_location_id,
customer_id
FROM WSH_DLVY_DELIVERABLES_V
WHERE delivery_id = p_delivery_id
Order by delivery_detail_id;
SELECT
a.entity_name
FROM fnd_attached_documents a,
fnd_documents d
WHERE
d.category_id IN
(SELECT category_id
FROM FND_DOCUMENT_CATEGORIES
WHERE ( UPPER(name) like l_bind_var_msds or
UPPER(name) like l_bind_var_hazard or
UPPER(name) like l_bind_var_safety ) AND
UPPER(name) NOT like l_bind_var_msds_rejected
)
AND d.document_id = a.document_id
AND (( a.entity_name = 'OE_ORDER_LINES' AND a.pk1_value = LocalDeliverydetail.source_line_id )
OR (a.entity_name = 'WSH_DELIVERY_DETAILS' AND a.pk1_value = LocalDeliverydetail.delivery_detail_id ));
Select segment1
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_organization_id;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1 FROM gr_item_general where item_code = l_item_code)
OR EXISTS (SELECT 1 FROM gr_generic_items_b where item_no = l_item_code); */
select p.document_code, p.language, p.disclosure_code
from GR_COUNTRY_PROFILES p,
HZ_LOCATIONS l
where p.territory_code = l.country and
l.location_id = LocalDeliverydetail.ship_to_location_id;
SELECT country
FROM HZ_LOCATIONS
WHERE location_id = LocalDeliverydetail.ship_to_location_id;
SELECT *
FROM fnd_documents_vl
WHERE DOC_ATTRIBUTE1 = l_item_code
and DOC_ATTRIBUTE2 = l_territory_details.DOCUMENT_CODE
and DOC_ATTRIBUTE3 = l_territory_details.LANGUAGE
and DOC_ATTRIBUTE4 = l_territory_details.DISCLOSURE_CODE
and ( UPPER(DOC_ATTRIBUTE_CATEGORY) like l_bind_var_msds or
UPPER(DOC_ATTRIBUTE_CATEGORY) like l_bind_var_hazard or
UPPER(DOC_ATTRIBUTE_CATEGORY) like l_bind_var_safety )
AND UPPER(DOC_ATTRIBUTE_CATEGORY) NOT like l_bind_var_msds_rejected
And publish_flag = 'Y'
ORDER BY CREATION_DATE DESC;
Select max(date_sent)
From GR_DISPATCH_HISTORY_V
Where INVENTORY_ITEM_ID = LocalDeliverydetail.inventory_item_id -- BUG#4431025
and ORGANIZATION_ID = LocalDeliverydetail.organization_id -- BUG#4431025
and DOCUMENT_CODE = l_territory_details.DOCUMENT_CODE
and DOCUMENT_LANGUAGE = l_territory_details.LANGUAGE
and DISCLOSURE_CODE = l_territory_details.DISCLOSURE_CODE
and Recipient_id = LocalDeliveryDetail.customer_id;
SELECT NVL(max(seq_num),0) + 10
FROM fnd_attached_documents
WHERE entity_name= 'WSH_DELIVERY_DETAILS'
AND pk1_value = LocalDeliverydetail.delivery_detail_id;
SELECT fnd_attached_documents_s.nextval
FROM dual;
FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(
X_Rowid => l_rowid,
X_attached_document_id => l_attached_document_id,
X_document_id => LocalLatestDocument.document_id,
X_creation_date => SYSDATE,
X_created_by => l_user_id,
X_last_update_date => SYSDATE,
X_last_updated_by => l_user_id,
X_last_update_login => NULL,
X_seq_num => l_seq,
X_entity_name => 'WSH_DELIVERY_DETAILS',
X_column1 => NULL,
X_pk1_value => LocalDeliverydetail.delivery_detail_id,
X_pk2_value => NULL,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_automatically_added_flag => 'Y',
X_datatype_id => LocalLatestDocument.datatype_id,
X_category_id => LocalLatestDocument.category_id,
X_security_type => LocalLatestDocument.security_type,
X_security_id => LocalLatestDocument.security_id,
X_publish_flag => LocalLatestDocument.publish_flag,
X_storage_type => LocalLatestDocument.storage_type,
X_usage_type => LocalLatestDocument.usage_type,
X_language => LocalLatestDocument.doc_attribute3,
X_description => LocalLatestDocument.description,
X_file_name => LocalLatestDocument.file_name,
X_media_id => LocalLatestDocument.media_id,
X_attribute_category => LocalLatestDocument.doc_attribute_category,
X_attribute1 => LocalLatestDocument.doc_attribute1,
X_attribute2 => LocalLatestDocument.doc_attribute2,
X_attribute3 => LocalLatestDocument.doc_attribute3,
X_attribute4 => LocalLatestDocument.doc_attribute4,
X_attribute5 => LocalLatestDocument.doc_attribute5,
X_attribute6 => LocalLatestDocument.doc_attribute6,
X_attribute7 => LocalLatestDocument.doc_attribute7,
X_attribute8 => LocalLatestDocument.doc_attribute8,
X_attribute9 => LocalLatestDocument.doc_attribute9,
X_attribute10 => LocalLatestDocument.doc_attribute10,
X_attribute11 => LocalLatestDocument.doc_attribute11,
X_attribute12 => LocalLatestDocument.doc_attribute12,
X_attribute13 => LocalLatestDocument.doc_attribute13,
X_attribute14 => LocalLatestDocument.doc_attribute14,
X_attribute15 => LocalLatestDocument.doc_attribute15,
X_create_doc => 'N');
,p_update_dispatch_history IN VARCHAR2
,p_recipient_site IN VARCHAR2
) IS
l_orgn_code varchar2(3);
SELECT *
FROM fnd_documents_vl a
WHERE a.creation_date = (SELECT max(b.creation_date) FROM fnd_documents_vl b
WHERE a.doc_attribute1 = b.doc_attribute1
AND a.doc_attribute3 = b.doc_attribute3
AND a.doc_attribute5 = l_orgn_code
AND (UPPER(b.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_msds
OR UPPER(b.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_safety
OR UPPER(b.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_hazard)
AND UPPER(b.DOC_ATTRIBUTE_CATEGORY) not like l_bind_var_msds_rejected
AND ( (a.DOC_ATTRIBUTE_CATEGORY = p_document_category))
AND b.publish_flag = 'Y')
AND a.DOC_ATTRIBUTE1 >= nvl(p_from_item,' ')
AND a.DOC_ATTRIBUTE1 <= nvl(p_to_item,'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ')
AND a.DOC_ATTRIBUTE3 >= nvl(p_from_language,' ')
AND a.DOC_ATTRIBUTE3 <= nvl(p_to_language,'ZZZZ')
AND (UPPER(a.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_msds
OR UPPER(a.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_safety
OR UPPER(a.DOC_ATTRIBUTE_CATEGORY) like l_bind_var_hazard)
AND UPPER(a.DOC_ATTRIBUTE_CATEGORY) not like l_bind_var_msds_rejected
AND ((a.DOC_ATTRIBUTE_CATEGORY = p_document_category))
AND a.publish_flag = 'Y';
SELECT party_id, party_site_id
FROM hz_party_sites
WHERE party_site_number = p_recipient_site;
SELECT ORGANIZATION_CODE from mtl_parameters
where ORGANIZATION_ID = l_orgn_id;
SELECT inventory_item_id from mtl_system_items
where ORGANIZATION_ID = l_orgn_id
and SEGMENT1 = l_item_code;
/* Select documents */
OPEN c_get_doc_info;
IF p_update_dispatch_history = 'Y' THEN
OPEN c_get_recipient_details ;
IF p_update_dispatch_history = 'Y' THEN
FND_MESSAGE.SET_NAME('GR', 'GR_CREATING_DISPATCH_HISTORY');
END IF; /* update dispatch history */