[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
p_update_collaboration_flag IN BOOLEAN,
p_update_coll_mess_flag IN BOOLEAN,
p_all_notification_codes IN VARCHAR2,
p_int_con_no IN VARCHAR2,
p_coll_point IN VARCHAR2,
p_doc_dir IN VARCHAR2,
p_coll_id IN NUMBER,
x_dtl_coll_id IN OUT NOCOPY VARCHAR2,
p_collaboration_standard IN VARCHAR2,
p_notification_event IN WF_EVENT_T,
p_application_id IN NUMBER )
IS
l_application_id NUMBER(10);
SELECT codes.notification_message,details.action_dtl_id, details.action_code, details.attribute1,
details.attribute2, details.attribute3, details.attribute4, details.attribute5, details.attribute6,
details.attribute7, details.attribute8, details.attribute9, details.attribute10, details.attribute11,
details.attribute12, details.attribute13, details.attribute14, details.attribute15
FROM CLN_NOTIFICATION_CODES codes, CLN_NOTIFICATION_ACTION_HDR header, CLN_NOTIFICATION_ACTION_DTL details
WHERE codes.NOTIFICATION_CODE = p_notification_code and codes.collaboration_point = p_coll_point
and header.notification_id = codes.notification_id and header.application_id = p_application_id
and header.collaboration_type = p_collaboration_type and header.ACTION_HDR_ID = details.ACTION_HDR_ID
and details.active_flag = 'Y'
ORDER BY details.ACTION_DTL_ID;
IF(p_update_collaboration_flag) THEN
IF (l_Debug_Level <= 1) THEN
ecx_cln_debug_pub.Add('p_update_collaboration_flag:TRUE', 1);
ecx_cln_debug_pub.Add('p_update_collaboration_flag:FALSE', 1);
IF(p_update_coll_mess_flag) THEN
IF (l_Debug_Level <= 1) THEN
ecx_cln_debug_pub.Add('p_update_coll_mess_flag:TRUE', 1);
ecx_cln_debug_pub.Add('p_update_coll_mess_flag:FALSE', 1);
SELECT application_id, collaboration_type, org_id, document_no,
doc_revision_no, release_no, collaboration_id, document_owner
INTO l_application_id, l_collaboration_type, l_org_id, l_document_number,
l_revision_number, l_release_number, l_collaboration_id, l_document_owner
FROM CLN_COLL_HIST_HDR
WHERE collaboration_id = p_coll_id;
SELECT application_id, collaboration_type, org_id, document_no,
doc_revision_no, release_no, collaboration_id,document_owner
INTO l_application_id, l_collaboration_type, l_org_id, l_document_number,
l_revision_number, l_release_number, l_collaboration_id, l_document_owner
FROM CLN_COLL_HIST_HDR
WHERE APPLICATION_REFERENCE_ID = p_reference;
SELECT application_id, collaboration_type, org_id, document_no,
doc_revision_no, release_no, collaboration_id, l_document_owner
INTO l_application_id, l_collaboration_type, l_org_id, l_document_number,
l_revision_number, l_release_number, l_collaboration_id, l_document_owner
FROM CLN_COLL_HIST_HDR
WHERE xmlg_internal_control_number = p_int_con_no;
SELECT xmlg_transaction_type, xmlg_transaction_subtype, xmlg_document_id, xmlg_msg_id
INTO l_xmlg_transaction_type, l_xmlg_transaction_subtype, l_xmlg_document_id, l_msg_id
FROM CLN_COLL_HIST_DTL where collaboration_dtl_id =
(SELECT MAX(collaboration_dtl_id) FROM CLN_COLL_HIST_DTL
WHERE document_direction = 'OUT' AND collaboration_id = l_collaboration_id);
SELECT msgid
INTO l_msg_id
FROM ECX_DOCLOGS
WHERE transaction_type = l_xmlg_transaction_type AND
transaction_subtype = l_xmlg_transaction_subtype AND document_number = l_xmlg_document_id
AND direction = 'OUT';
IF p_update_collaboration_flag THEN
IF p_statuslvl = '00' THEN
l_document_status := 'SUCCESS';
ecx_cln_debug_pub.Add('INVOKING CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION', 2);
CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
x_return_status => l_return_status,
x_msg_data => l_msg_data,
p_msg_text => p_header_desc,
p_coll_status => l_collaboration_status,
p_doc_type => l_doc_type,
p_doc_dir => l_doc_dir,
p_coll_pt => p_coll_point,
p_doc_status => l_document_status,
p_notification_id => l_all_notification_codes,
p_coll_id => l_collaboration_id,
p_xmlg_internal_control_number => p_int_con_no,
p_xmlg_msg_id => NULL,
p_rosettanet_check_required => FALSE,
x_dtl_coll_id => x_dtl_coll_id,
p_collaboration_standard => p_collaboration_standard);
ecx_cln_debug_pub.Add('INVOKING CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION', 2);
CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
x_return_status => l_return_status,
x_msg_data => l_msg_data,
p_msg_text => p_header_desc,
p_coll_pt => p_coll_point,
p_doc_status => l_document_status,
p_notification_id => l_all_notification_codes,
p_coll_id => l_collaboration_id,
p_xmlg_internal_control_number => p_int_con_no,
p_xmlg_msg_id => NULL,
p_rosettanet_check_required => FALSE,
x_dtl_coll_id => x_dtl_coll_id,
p_collaboration_standard => p_collaboration_standard);
ecx_cln_debug_pub.Add('COMPLETED CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION', 2);
IF p_update_coll_mess_flag THEN
CLN_CH_COLLABORATION_PKG.ADD_COLLABORATION_MESSAGES(
x_return_status => l_return_status,
x_msg_data => l_msg_data,
p_dtl_coll_id => x_dtl_coll_id,
p_ref1 => p_notification_code,
p_dtl_msg => p_notification_desc);
SELECT application_name INTO l_application_name
FROM fnd_application_vl
WHERE application_id = l_application_id;
SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
SELECT 'x'
INTO l_temp
FROM WF_ROLES
WHERE NAME = l_role
AND rownum < 2;
SELECT email_address
INTO l_admin_email
FROM WF_USERS
WHERE NAME = 'CLN_ADMINISTRATOR';
SELECT OWNER_ROLE
INTO l_role
FROM CLN_COLL_HIST_HDR
where COLLABORATION_ID = l_collaboration_id;
SELECT 'x'
INTO l_temp
FROM WF_ROLES
WHERE NAME = l_role
AND rownum < 2;
SELECT email_address
INTO l_admin_email
FROM WF_USERS
WHERE NAME = 'CLN_ADMINISTRATOR';
SELECT company_admin_email
INTO l_email
FROM ecx_tp_headers
where tp_header_id = l_tp_id;
SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
l_update_collaboration_flag BOOLEAN; -- For updating collaboration history only once
l_update_collaboration_flag := TRUE; -- IF TRUE collaboration is updated
l_update_collaboration_flag, true, p_reason_code,
p_int_con_no, p_coll_point, p_doc_dir,
p_coll_id, l_dtl_coll_id,p_collaboration_standard, null,null);
l_update_collaboration_flag := FALSE; -- no need to update second or third time
SELECT REPLACE(p_reason_code, '::', ':') INTO l_all_reason_codes FROM DUAL;
SELECT REPLACE(p_line_desc, '::', ':') INTO l_all_reason_desc FROM DUAL;
l_update_collaboration_flag, true, p_reason_code,
p_int_con_no, p_coll_point, p_doc_dir,
p_coll_id, l_dtl_coll_id,p_collaboration_standard,null,null);
l_update_collaboration_flag := FALSE; -- no need to update second or third time
SELECT to_char(eth.tp_header_id)
INTO p_tr_partner_id
FROM ECX_DOCLOGS doclogs, ecx_ext_processes eep, ecx_tp_details etd, ecx_tp_headers eth, ecx_standards estd
WHERE doclogs.internal_control_number = p_xmlg_internal_control_number
AND eep.ext_type = doclogs.transaction_type
AND eep.ext_subtype = doclogs.transaction_subtype
AND eep.standard_id = estd.standard_id
AND estd.standard_code = doclogs.message_standard
AND eep.ext_process_id = etd.ext_process_id
AND etd.source_tp_location_code = doclogs.party_site_id
AND eep.direction = 'IN'
AND eth.party_type = NVL(doclogs.party_type,eth.party_type);
SELECT 'x'
INTO l_temp
FROM WF_ROLES
WHERE NAME = l_role
AND rownum < 2;
SELECT email_address
INTO l_admin_email
FROM WF_USERS
WHERE NAME = 'CLN_ADMINISTRATOR';
SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;
SELECT ecxproc.EXT_TYPE,ecxproc.EXT_SUBTYPE
INTO l_xmlg_transaction_type, l_xmlg_transaction_subtype
FROM ecx_tp_headers eth, ecx_tp_details etd, ECX_TRANSACTIONS ecxtrans, ECX_EXT_PROCESSES ecxproc, ecx_standards estd
WHERE eth.party_id = l_tr_partner_id
AND eth.party_site_id = l_tr_partner_site
AND eth.party_type = nvl(l_tr_partner_type, eth.party_type)
AND eth.tp_header_id = etd.tp_header_id
AND etd.ext_process_id = ecxproc.ext_process_id
AND ecxtrans.transaction_id = ecxproc.transaction_id
AND ecxtrans.transaction_type = l_xmlg_int_transaction_type
AND ecxtrans.transaction_subtype = l_xmlg_int_transaction_subtype
AND ecxproc.direction = nvl(l_doc_dir,ecxproc.direction)
AND estd.standard_id = ecxproc.standard_id;
p_update_collaboration_flag => false,
p_update_coll_mess_flag => false,
p_all_notification_codes => null,
p_int_con_no => l_xmlg_internal_control_number,
p_coll_point => l_collaboration_pt,
p_doc_dir => l_doc_dir,
p_coll_id => l_coll_id,
x_dtl_coll_id => l_dtl_coll_id,
p_collaboration_standard => null,
p_notification_event => p_notification_event,
p_application_id => l_application_id );
SELECT application_name
INTO l_application_name
FROM fnd_application_vl
WHERE application_id = l_application_id;
SELECT tl.display_label
INTO l_attribute_col_value
FROM CLN_CH_DISPLAY_LABELS_DTL_tl tl, CLN_CH_DISPLAY_LABELS_DTL_VL vl, CLN_CH_DISPLAY_LABELS_hdr hdr
WHERE tl.guid = vl.guid
AND parent_guid = hdr.guid
AND collaboration_standard = l_collaboration_std
AND application_id = l_application_id
AND cln_columns = l_attribute_name
AND collaboration_type IS NULL
AND LANGUAGE = USERENV('LANG');
SELECT codestl.NOTIFICATION_MESSAGE
INTO l_notification_desc
FROM CLN_NOTIFICATION_CODES codes, CLN_NOTIFICATION_CODES_TL codestl
WHERE codes.NOTIFICATION_ID = codestl.NOTIFICATION_ID
AND NOTIFICATION_CODE = p_notification_code
AND COLLABORATION_POINT = p_collaboration_point
AND LANGUAGE = USERENV('LANG');
SELECT 'x'
INTO l_temp
FROM WF_ROLES
WHERE NAME = l_notif_receiver_role
AND rownum < 2;
SELECT email_address
INTO l_admin_email
FROM WF_USERS
WHERE NAME = 'CLN_ADMINISTRATOR';
SELECT cln_np_notification_workflow_s.nextval INTO l_notification_flow_key FROM dual;