[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT payload into l_xmlDoc FROM ecx_doclogs WHERE msgid = HEXTORAW(p_msgId);
SELECT payload into l_xmlDoc FROM ecx_doclogs WHERE msgid = HEXTORAW(p_msgId);
SELECT payload into l_xmlDoc FROM ecx_doclogs WHERE msgid = HEXTORAW(p_msgId);
SELECT meaning INTO l_meaning FROM fnd_lookups
WHERE lookup_code = p_app_id AND lookup_type = 'CLN_APPLICATION_ID';
SELECT meaning INTO l_meaning FROM fnd_lookups
WHERE lookup_code = p_doc_dir AND lookup_type = 'CLN_COLLABORATION_DOC_DIRECTN';
SELECT meaning INTO l_meaning FROM fnd_lookups
WHERE lookup_code = p_doc_type AND lookup_type = 'CLN_COLLABORATION_DOC_TYPE';
SELECT meaning INTO l_meaning FROM fnd_lookups
WHERE lookup_code = p_coll_status AND lookup_type = 'CLN_COLLABORATION_STATUS';
SELECT meaning INTO l_meaning FROM fnd_lookups
WHERE lookup_code = p_coll_pt AND lookup_type = 'CLN_COLLABORATION_POINT';
SELECT meaning INTO l_meaning FROM fnd_lookups
WHERE lookup_code = p_coll_type AND lookup_type = 'CLN_COLLABORATION_TYPE';
SELECT meaning INTO l_meaning FROM fnd_lookups
WHERE lookup_code = p_doc_status AND lookup_type = 'CLN_COLLABORATION_DOC_STATUS';
SELECT meaning INTO l_meaning FROM fnd_lookups
WHERE lookup_code = p_disposition AND lookup_type = 'CLN_DISPOSITION';
PROCEDURE UPDATE_COLLABORATION_INIT(
x_return_status OUT NOCOPY VARCHAR2,
p_coll_id IN NUMBER,
p_doc_type IN VARCHAR2,
p_doc_dir IN VARCHAR2,
p_coll_pt IN VARCHAR2,
p_org_ref IN VARCHAR2,
p_doc_status IN VARCHAR2,
p_notification_id IN VARCHAR2,
p_msg_text IN VARCHAR2,
p_xmlg_transaction_type IN VARCHAR2,
p_xmlg_transaction_subtype IN VARCHAR2,
p_xmlg_document_id IN VARCHAR2,
p_xmlg_msg_id IN VARCHAR2,
p_xmlg_internal_control_number IN NUMBER,
p_resend_flag IN VARCHAR2,
p_xmlg_int_transaction_type IN VARCHAR2,
p_xmlg_int_transaction_subtype IN VARCHAR2,
p_xml_event_key IN VARCHAR2)
IS
l_dtl_coll_id NUMBER;
ecx_cln_debug_pub.Add('------- Entering UPDATE_COLLABORATION_INIT API -------- ',2);
SELECT cln_collaboration_dtl_id_s.nextval INTO l_dtl_coll_id FROM dual ;
INSERT INTO CLN_COLL_HIST_DTL(
COLLABORATION_DTL_ID,COLLABORATION_ID,COLLABORATION_DOCUMENT_TYPE,
DOCUMENT_DIRECTION,COLLABORATION_POINT,
ORIGINATOR_REFERENCE,DOCUMENT_STATUS,NOTIFICATION_ID,MESSAGE_TEXT,CREATION_DATE,CREATED_BY,
LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN, XMLG_TRANSACTION_TYPE, XMLG_TRANSACTION_SUBTYPE,
XMLG_DOCUMENT_ID, XMLG_MSG_ID, XMLG_INTERNAL_CONTROL_NUMBER,
RESEND_FLAG, XMLG_INT_TRANSACTION_TYPE, XMLG_INT_TRANSACTION_SUBTYPE, XML_EVENT_KEY)
VALUES(l_dtl_coll_id,p_coll_id,nvl(p_doc_type,'UNKNOWN'),p_doc_dir,p_coll_pt,p_org_ref,p_doc_status,
p_notification_id,p_msg_text,SYSDATE,FND_GLOBAL.USER_ID,
SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID, p_xmlg_transaction_type,
p_xmlg_transaction_subtype,p_xmlg_document_id, p_xmlg_msg_id, p_xmlg_internal_control_number,
p_resend_flag, p_xmlg_int_transaction_type, p_xmlg_int_transaction_subtype, p_xml_event_key);
ecx_cln_debug_pub.Add('--------- Exiting UPDATE_COLLABORATION_INIT API -------- ',2);
ecx_cln_debug_pub.Add('--------- Exiting UPDATE_COLLABORATION_INIT API -------- ',2);
ecx_cln_debug_pub.Add('--------- Exiting UPDATE_COLLABORATION_INIT API -------- ',2);
ecx_cln_debug_pub.Add('--------- Exiting UPDATE_COLLABORATION_INIT API -------- ',2);
END UPDATE_COLLABORATION_INIT;
l_update_reqd BOOLEAN;
IS_UPDATE_REQUIRED(
x_return_status, x_msg_data, l_doc_dir, l_xmlg_transaction_type,
l_xmlg_transaction_subtype, l_tr_partner_type, l_tr_partner_id,
l_tr_partner_site, l_sender_component, l_update_reqd );
ecx_cln_debug_pub.Add('Status Code Returned By IS_UPDATE_REQUIRED :'||x_return_status,1);
IF (l_update_reqd <> TRUE) THEN
IF (l_Debug_Level <= 1) THEN
ecx_cln_debug_pub.Add('Update Reqd as Returned By IS_UPDATE_REQUIRED -FALSE',1);
SELECT cln_collaboration_hdr_id_s.NEXTVAL INTO x_coll_id FROM dual;
INSERT INTO CLN_COLL_HIST_HDR(
COLLABORATION_ID,APPLICATION_ID,APPLICATION_REFERENCE_ID,ORG_ID,RELEASE_NO,DOCUMENT_NO,
DOC_REVISION_NO,PARTNER_DOCUMENT_NO,COLLABORATION_TYPE,TRADING_PARTNER,RESEND_FLAG,
RESEND_COUNT,DOCUMENT_OWNER,INITIATION_DATE,DOCUMENT_CREATION_DATE,DOCUMENT_REVISION_DATE,
COLLABORATION_STATUS,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,XMLG_MSG_ID,UNIQUE_ID1,UNIQUE_ID2,UNIQUE_ID3,UNIQUE_ID4,UNIQUE_ID5,
XMLG_TRANSACTION_TYPE, XMLG_TRANSACTION_SUBTYPE,XMLG_DOCUMENT_ID, TRADING_PARTNER_TYPE, TRADING_PARTNER_SITE,
XMLG_INTERNAL_CONTROL_NUMBER, DOCUMENT_DIRECTION, XMLG_INT_TRANSACTION_TYPE, XMLG_INT_TRANSACTION_SUBTYPE,
XML_EVENT_KEY, COLLABORATION_STANDARD,OWNER_ROLE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,
ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
ATTRIBUTE14,ATTRIBUTE15,DATTRIBUTE1,DATTRIBUTE2,DATTRIBUTE3,DATTRIBUTE4,DATTRIBUTE5 )
VALUES( x_coll_id,l_app_id,l_ref_id,p_org_id,p_rel_no,l_doc_no,p_doc_rev_no,
p_partner_doc_no,nvl(l_coll_type,'UNKNOWN'),l_tr_partner_id,l_resend_flag,p_resend_count,
l_doc_owner,nvl(p_init_date,SYSDATE),l_doc_creation_date,p_doc_revision_date,l_coll_status,
SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID,l_xmlg_msg_id,
p_unique1,p_unique2,p_unique3,p_unique4,p_unique5,
l_xmlg_transaction_type, l_xmlg_transaction_subtype, l_xmlg_document_id,l_tr_partner_type, l_tr_partner_site,
l_xmlg_internal_control_number, l_doc_dir, l_xmlg_int_transaction_type, l_xmlg_int_transaction_subtype,
l_xml_event_key, l_collaboration_standard,l_owner_role,p_attribute1,p_attribute2,p_attribute3,p_attribute4,p_attribute5,
p_attribute6,p_attribute7,p_attribute8,p_attribute9,p_attribute10,p_attribute11,p_attribute12,p_attribute13,
p_attribute14,p_attribute15, p_dattribute1,p_dattribute2,p_dattribute3,p_dattribute4,p_dattribute5 );
UPDATE_COLLABORATION_INIT(
x_return_status,x_coll_id,l_doc_type,l_doc_dir,l_coll_pt,l_doc_no,'SUCCESS',null,
l_msg_text, l_xmlg_transaction_type, l_xmlg_transaction_subtype, l_xmlg_document_id,
l_xmlg_msg_id, l_xmlg_internal_control_number, l_resend_flag, l_xmlg_int_transaction_type,
l_xmlg_int_transaction_subtype, l_xml_event_key );
ecx_cln_debug_pub.Add('Status Code Returned By UPDATE_COLLABORATION_INIT :'||x_return_status,1);
PROCEDURE UPDATE_COLLABORATION(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_coll_id IN NUMBER,
p_app_id IN VARCHAR2,
p_ref_id IN VARCHAR2,
p_rel_no IN VARCHAR2,
p_doc_no IN VARCHAR2,
p_doc_rev_no IN VARCHAR2,
p_xmlg_transaction_type IN VARCHAR2,
p_xmlg_transaction_subtype IN VARCHAR2,
p_xmlg_document_id IN VARCHAR2,
p_resend_flag IN VARCHAR2,
p_resend_count IN NUMBER,
p_disposition IN VARCHAR2,
p_coll_status IN VARCHAR2,
p_doc_type IN VARCHAR2,
p_doc_dir IN VARCHAR2,
p_coll_pt IN VARCHAR2,
p_org_ref IN VARCHAR2,
p_doc_status IN VARCHAR2,
p_notification_id IN VARCHAR2,
p_msg_text IN VARCHAR2,
p_bsr_verb IN VARCHAR2,
p_bsr_noun IN VARCHAR2,
p_bsr_rev IN VARCHAR2,
p_sdr_logical_id IN VARCHAR2,
p_sdr_component IN VARCHAR2,
p_sdr_task IN VARCHAR2,
p_sdr_refid IN VARCHAR2,
p_sdr_confirmation IN VARCHAR2,
p_sdr_language IN VARCHAR2,
p_sdr_codepage IN VARCHAR2,
p_sdr_authid IN VARCHAR2,
p_sdr_datetime_qualifier IN VARCHAR2,
p_sdr_datetime IN VARCHAR2,
p_sdr_timezone IN VARCHAR2,
p_attr1 IN VARCHAR2,
p_attr2 IN VARCHAR2,
p_attr3 IN VARCHAR2,
p_attr4 IN VARCHAR2,
p_attr5 IN VARCHAR2,
p_attr6 IN VARCHAR2,
p_attr7 IN VARCHAR2,
p_attr8 IN VARCHAR2,
p_attr9 IN VARCHAR2,
p_attr10 IN VARCHAR2,
p_attr11 IN VARCHAR2,
p_attr12 IN VARCHAR2,
p_attr13 IN VARCHAR2,
p_attr14 IN VARCHAR2,
p_attr15 IN VARCHAR2,
p_xmlg_msg_id IN VARCHAR2,
p_unique1 IN VARCHAR2,
p_unique2 IN VARCHAR2,
p_unique3 IN VARCHAR2,
p_unique4 IN VARCHAR2,
p_unique5 IN VARCHAR2,
p_tr_partner_type IN VARCHAR2,
p_tr_partner_id IN VARCHAR2,
p_tr_partner_site IN VARCHAR2,
p_sender_component IN VARCHAR2,
p_rosettanet_check_required IN BOOLEAN,
x_dtl_coll_id OUT NOCOPY NUMBER,
p_xmlg_internal_control_number IN NUMBER,
p_partner_doc_no IN VARCHAR2,
p_org_id IN NUMBER,
p_doc_creation_date IN DATE,
p_doc_revision_date IN DATE,
p_doc_owner IN VARCHAR2,
p_xmlg_int_transaction_type IN VARCHAR2,
p_xmlg_int_transaction_subtype IN VARCHAR2,
p_xml_event_key IN VARCHAR2,
p_collaboration_standard IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_dattribute1 IN DATE,
p_dattribute2 IN DATE,
p_dattribute3 IN DATE,
p_dattribute4 IN DATE,
p_dattribute5 IN DATE,
p_owner_role IN VARCHAR2 )
IS
l_dtl_coll_id NUMBER;
l_update_reqd BOOLEAN;
ecx_cln_debug_pub.Add('--------- Entering UPDATE_COLLABORATION API ------------ ',2);
l_msg_data := 'Collaboration successfully updated ';
IS_UPDATE_REQUIRED(
x_return_status, x_msg_data, l_doc_dir, l_xmlg_transaction_type,
l_xmlg_transaction_subtype, l_tr_partner_type, l_tr_partner_id,
l_tr_partner_site, l_sender_component, l_update_reqd );
ecx_cln_debug_pub.Add('Status Code Returned By IS_UPDATE_REQUIRED :'||x_return_status,1);
IF (l_update_reqd <> TRUE) THEN
IF (l_Debug_Level <= 1) THEN
ecx_cln_debug_pub.Add('Update Reqd as Returned By IS_UPDATE_REQUIRED -FALSE',1);
SELECT APPLICATION_ID, COLLABORATION_TYPE, DOCUMENT_CREATION_DATE
INTO l_app_id, l_coll_type, l_doc_creation_date
FROM CLN_COLL_HIST_HDR
WHERE COLLABORATION_ID = l_coll_id;
FND_MESSAGE.SET_TOKEN('ACTION','update');
l_msg_data := 'Failed to update Collaboration as required parameters Collaboration Status / Document Direction not found';
UPDATE CLN_COLL_HIST_HDR
SET APPLICATION_ID = NVL(l_app_id,APPLICATION_ID),
APPLICATION_REFERENCE_ID = NVL(l_ref_id,APPLICATION_REFERENCE_ID),
RELEASE_NO = NVL(p_rel_no,RELEASE_NO),
DOC_REVISION_NO = NVL(p_doc_rev_no,DOC_REVISION_NO),
RESEND_FLAG = NVL(l_resend_flag,RESEND_FLAG),
RESEND_COUNT = NVL(p_resend_count,RESEND_COUNT),
DISPOSITION = NVL(p_disposition,DISPOSITION),
COLLABORATION_STATUS = NVL(l_coll_status,COLLABORATION_STATUS),
DOCUMENT_NO = NVL(p_doc_no,DOCUMENT_NO),
XMLG_MSG_ID = NVL(l_xmlg_msg_id,XMLG_MSG_ID),
XMLG_TRANSACTION_TYPE = NVL(l_xmlg_transaction_type,XMLG_TRANSACTION_TYPE),
XMLG_TRANSACTION_SUBTYPE = NVL(l_xmlg_transaction_subtype,XMLG_TRANSACTION_SUBTYPE),
XMLG_INT_TRANSACTION_TYPE = NVL(l_xmlg_int_transaction_type,XMLG_INT_TRANSACTION_TYPE),
XMLG_INT_TRANSACTION_SUBTYPE = NVL(l_xmlg_int_transaction_subtype,XMLG_INT_TRANSACTION_SUBTYPE),
XMLG_DOCUMENT_ID = NVL(l_xmlg_document_id,XMLG_DOCUMENT_ID),
UNIQUE_ID1 = NVL(p_unique1,UNIQUE_ID1),
UNIQUE_ID2 = NVL(p_unique2,UNIQUE_ID2),
UNIQUE_ID3 = NVL(p_unique3,UNIQUE_ID3),
UNIQUE_ID4 = NVL(p_unique4,UNIQUE_ID4),
UNIQUE_ID5 = NVL(p_unique5,UNIQUE_ID5),
XMLG_INTERNAL_CONTROL_NUMBER = NVL(l_xmlg_internal_control_number,XMLG_INTERNAL_CONTROL_NUMBER),
DOCUMENT_DIRECTION = NVL(l_doc_dir,DOCUMENT_DIRECTION),
PARTNER_DOCUMENT_NO = NVL(p_partner_doc_no,PARTNER_DOCUMENT_NO),
ORG_ID = NVL(p_org_id,ORG_ID),
DOCUMENT_CREATION_DATE = NVL(DOCUMENT_CREATION_DATE,l_doc_creation_date),
DOCUMENT_REVISION_DATE = NVL(p_doc_revision_date,DOCUMENT_REVISION_DATE),
DOCUMENT_OWNER = NVL(l_doc_owner,DOCUMENT_OWNER),
XML_EVENT_KEY = NVL(l_xml_event_key, XML_EVENT_KEY),
COLLABORATION_STANDARD = NVL(p_collaboration_standard,NVL(COLLABORATION_STANDARD,l_collaboration_standard)),
ATTRIBUTE1 = NVL(p_attribute1 ,ATTRIBUTE1),
ATTRIBUTE2 = NVL(p_attribute2 ,ATTRIBUTE2),
ATTRIBUTE3 = NVL(p_attribute3 ,ATTRIBUTE3),
ATTRIBUTE4 = NVL(p_attribute4 ,ATTRIBUTE4),
ATTRIBUTE5 = NVL(p_attribute5 ,ATTRIBUTE5),
ATTRIBUTE6 = NVL(p_attribute6 ,ATTRIBUTE6),
ATTRIBUTE7 = NVL(p_attribute7 ,ATTRIBUTE7),
ATTRIBUTE8 = NVL(p_attribute8 ,ATTRIBUTE8),
ATTRIBUTE9 = NVL(p_attribute9 ,ATTRIBUTE9),
ATTRIBUTE10 = NVL(p_attribute10 ,ATTRIBUTE10),
ATTRIBUTE11 = NVL(p_attribute11 ,ATTRIBUTE11),
ATTRIBUTE12 = NVL(p_attribute12 ,ATTRIBUTE12),
ATTRIBUTE13 = NVL(p_attribute13 ,ATTRIBUTE13),
ATTRIBUTE14 = NVL(p_attribute14 ,ATTRIBUTE14),
ATTRIBUTE15 = NVL(p_attribute15 ,ATTRIBUTE15),
DATTRIBUTE1 = NVL(p_dattribute1 ,DATTRIBUTE1),
DATTRIBUTE2 = NVL(p_dattribute2 ,DATTRIBUTE2),
DATTRIBUTE3 = NVL(p_dattribute3 ,DATTRIBUTE3),
DATTRIBUTE4 = NVL(p_dattribute4 ,DATTRIBUTE4),
DATTRIBUTE5 = NVL(p_dattribute5 ,DATTRIBUTE5),
OWNER_ROLE = NVL(p_owner_role,OWNER_ROLE)
WHERE (COLLABORATION_ID = l_coll_id);
/* Note on collaboraiton standard update in the above query
------------------------------------------------------------
1. The criteria is, if caller passes collaboration standard to the API explicitely,
then the column has to be updated with that value.
2. If caller doesnt pass and if the column is already filled in,
then the value of the column should be unchanged
3. If caller doesnt pass and if the column is empty,
then the value obtained by us from xml gateway tables needs to be filled in
===========================================================================*/
IF (l_Debug_Level <= 1) THEN
ecx_cln_debug_pub.Add('--- After SQL Query : Updating CLN_COLL_HIST_HDR ---',1);
ecx_cln_debug_pub.Add('Collaboration Details successfully updated in CLN_COLL_HIST_HDR TABLE',1);
SELECT cln_collaboration_dtl_id_s.nextval INTO l_dtl_coll_id FROM dual ;
INSERT INTO CLN_COLL_HIST_DTL(
COLLABORATION_DTL_ID, COLLABORATION_ID,
COLLABORATION_DOCUMENT_TYPE, DOCUMENT_DIRECTION, COLLABORATION_POINT,
ORIGINATOR_REFERENCE, DOCUMENT_STATUS, NOTIFICATION_ID, MESSAGE_TEXT,
BSR_VERB, BSR_NOUN, BSR_REVISION,SENDER_LOGICAL_ID,SENDER_COMPONENT,
SENDER_TASK,SENDER_REFERENCEID,SENDER_CONFIRMATION,SENDER_LANGUAGE,
SENDER_CODEPAGE, SENDER_AUTHID, SENDER_DATETIME_QUALIFIER,
SENDER_DATETIME,SENDER_TIMEZONE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,
ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9, ATTRIBUTE10,
ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15, CREATION_DATE,CREATED_BY,
LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,XMLG_TRANSACTION_TYPE,
XMLG_TRANSACTION_SUBTYPE, XMLG_DOCUMENT_ID, XMLG_MSG_ID, XMLG_INTERNAL_CONTROL_NUMBER,
RESEND_FLAG, XMLG_INT_TRANSACTION_TYPE, XMLG_INT_TRANSACTION_SUBTYPE, XML_EVENT_KEY )
VALUES( x_dtl_coll_id,l_coll_id,nvl(l_doc_type,'UNKNOWN'),l_doc_dir,
l_coll_pt,p_org_ref,l_doc_status,
p_notification_id,l_msg_text,p_bsr_verb,p_bsr_noun,p_bsr_rev,p_sdr_logical_id,
p_sdr_component,p_sdr_task,p_sdr_refid,p_sdr_confirmation,p_sdr_language,p_sdr_codepage,
p_sdr_authid,p_sdr_datetime_qualifier,p_sdr_datetime,p_sdr_timezone,p_attr1,p_attr2,p_attr3,
p_attr4,p_attr5,p_attr6,p_attr7,p_attr8,p_attr9,p_attr10,
p_attr11,p_attr12,p_attr13,p_attr14,p_attr15,SYSDATE,FND_GLOBAL.USER_ID,
SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID, l_xmlg_transaction_type,
l_xmlg_transaction_subtype, l_xmlg_document_id, l_xmlg_msg_id, l_xmlg_internal_control_number,
l_resend_flag, l_xmlg_int_transaction_type, l_xmlg_int_transaction_subtype, l_xml_event_key);
FND_MESSAGE.SET_NAME('CLN','CLN_CH_COLLABORATION_UPDATED');
ecx_cln_debug_pub.Add('------ Exiting UPDATE_COLLABORATION API ------- ',2);
ecx_cln_debug_pub.Add('------ Exiting UPDATE_COLLABORATION API ------- ',2);
ecx_cln_debug_pub.Add('------ Exiting UPDATE_COLLABORATION API ------- ',2);
ecx_cln_debug_pub.Add('------ Exiting UPDATE_COLLABORATION API ------- ',2);
END UPDATE_COLLABORATION;
SELECT COLLABORATION_STATUS INTO x_coll_status FROM CLN_COLL_HIST_HDR
WHERE (COLLABORATION_ID = p_coll_id)
OR (APPLICATION_REFERENCE_ID = p_ref_id)
OR (APPLICATION_ID = p_app_id
AND UNIQUE_ID1 = p_unique1)
OR (APPLICATION_ID = p_app_id
AND UNIQUE_ID2 = p_unique2)
OR (APPLICATION_ID = p_app_id
AND UNIQUE_ID3 = p_unique3)
OR (APPLICATION_ID = p_app_id
AND UNIQUE_ID4 = p_unique4)
OR (APPLICATION_ID = p_app_id
AND UNIQUE_ID5 = p_unique5)
OR XMLG_MSG_ID = p_xmlg_msg_id
OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number;
SELECT COLLABORATION_STATUS INTO x_coll_status
FROM CLN_COLL_HIST_HDR hdr, CLN_COLL_HIST_DTL dtl
WHERE hdr.COLLABORATION_ID = dtl.COLLABORATION_ID
AND ( dtl.XMLG_MSG_ID = p_xmlg_msg_id
OR dtl.XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number
)
AND ROWNUM < 2;
Select hdr.APPLICATION_ID,hdr.APPLICATION_REFERENCE_ID,
hdr.RELEASE_NO,hdr.DOCUMENT_NO,hdr.DOC_REVISION_NO,
hdr.RESEND_FLAG,hdr.RESEND_COUNT,hdr.DISPOSITION,
hdr.COLLABORATION_STATUS,hdr.ORG_ID,hdr.TRADING_PARTNER,
hdr.DOCUMENT_OWNER,hdr.INITIATION_DATE,hdr.DOCUMENT_CREATION_DATE,
hdr.DOCUMENT_REVISION_DATE,hdr.XMLG_MSG_ID,dtl.COLLABORATION_DOCUMENT_TYPE,
dtl.DOCUMENT_DIRECTION,dtl.COLLABORATION_POINT,
dtl.ORIGINATOR_REFERENCE,dtl.DOCUMENT_STATUS,dtl.NOTIFICATION_ID,
dtl.MESSAGE_TEXT,dtl.BSR_VERB,dtl.BSR_NOUN,dtl.BSR_REVISION,
dtl.SENDER_LOGICAL_ID,dtl.SENDER_COMPONENT,dtl.SENDER_TASK,
dtl.SENDER_REFERENCEID,dtl.SENDER_CONFIRMATION,
dtl.SENDER_LANGUAGE,dtl.SENDER_CODEPAGE,dtl.SENDER_AUTHID,
dtl.SENDER_DATETIME_QUALIFIER,dtl.SENDER_DATETIME,dtl.SENDER_TIMEZONE,
dtl.ATTRIBUTE1,dtl.ATTRIBUTE2,dtl.ATTRIBUTE3,dtl.ATTRIBUTE4,dtl.ATTRIBUTE5,
dtl.ATTRIBUTE6,dtl.ATTRIBUTE7,dtl.ATTRIBUTE8,dtl.ATTRIBUTE9,dtl.ATTRIBUTE10,
dtl.ATTRIBUTE11,dtl.ATTRIBUTE12,dtl.ATTRIBUTE13,dtl.ATTRIBUTE14,dtl.ATTRIBUTE15,
dtl.XMLG_TRANSACTION_TYPE, dtl.XMLG_TRANSACTION_SUBTYPE,dtl.XMLG_DOCUMENT_ID, dtl.XMLG_INTERNAL_CONTROL_NUMBER
INTO x_app_id,x_ref_id,x_rel_no,x_doc_no,x_doc_rev_no,x_resend_flag,
x_resend_count,x_disposition,x_coll_status,x_org_id,
x_tr_partner_id,x_doc_owner,x_init_date,x_doc_creation_date,
x_doc_revision_date,x_xmlg_msg_id,x_doc_type,
x_doc_dir,x_coll_pt,x_org_ref,x_doc_status,
x_notification_id,x_msg_text,x_bsr_verb,x_bsr_noun,x_bsr_rev,x_sdr_logical_id,
x_sdr_component,x_sdr_task,x_sdr_refid,x_sdr_confirmation,x_sdr_language,x_sdr_codepage,
x_sdr_authid,x_sdr_datetime_qualifier,x_sdr_datetime,x_sdr_timezone,x_attr1,x_attr2,x_attr3,
x_attr4,x_attr5,x_attr6,x_attr7,x_attr8,x_attr9,x_attr10,
x_attr11,x_attr12,x_attr13,x_attr14,x_attr15,
p_xmlg_transaction_type, p_xmlg_transaction_subtype, p_xmlg_document_id, p_xmlg_internal_control_number
FROM CLN_COLL_HIST_HDR hdr,CLN_COLL_HIST_DTL dtl
WHERE hdr.COLLABORATION_ID = dtl.COLLABORATION_ID
AND ( dtl.COLLABORATION_DTL_ID = p_dtl_coll_id
OR (
((hdr.COLLABORATION_ID = p_coll_id)
OR (APPLICATION_REFERENCE_ID = x_ref_id)
OR (APPLICATION_ID = x_app_id
AND UNIQUE_ID1 = p_unique1)
OR (APPLICATION_ID = x_app_id
AND UNIQUE_ID2 = p_unique2)
OR (APPLICATION_ID = x_app_id
AND UNIQUE_ID3 = p_unique3)
OR (APPLICATION_ID = x_app_id
AND UNIQUE_ID4 = p_unique4)
OR (APPLICATION_ID = x_app_id
AND UNIQUE_ID5 = p_unique5)
)
AND dtl.COLLABORATION_POINT = x_coll_pt
AND dtl.DOCUMENT_DIRECTION = x_doc_dir
)
OR dtl.XMLG_MSG_ID = x_xmlg_msg_id
OR dtl.XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number
);
SELECT cln_collaboration_msg_id_s.nextval INTO l_dtl_msg_id FROM dual ;
SELECT collaboration_dtl_id
INTO l_coll_dtl_id
FROM cln_coll_hist_dtl
WHERE collaboration_dtl_id = p_dtl_coll_id;
INSERT INTO CLN_COLL_MESSAGES(
DTL_MESSAGE_ID,COLLABORATION_DTL_ID,REFERENCE1,REFERENCE2,REFERENCE3,REFERENCE4,REFERENCE5,
DTL_MESSAGE_TEXT,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
VALUES( l_dtl_msg_id,l_coll_dtl_id,p_ref1,p_ref2,p_ref3,p_ref4,p_ref5,l_dtl_msg,
SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,FND_GLOBAL.LOGIN_ID);
PROCEDURE IS_UPDATE_REQUIRED(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_doc_dir IN VARCHAR2,
p_xmlg_transaction_type IN VARCHAR2,
p_xmlg_transaction_subtype IN VARCHAR2,
p_tr_partner_type IN VARCHAR2,
p_tr_partner_id IN VARCHAR2,
p_tr_partner_site IN VARCHAR2,
p_sender_component IN VARCHAR2,
x_update_reqd OUT NOCOPY BOOLEAN)
IS
l_error_code NUMBER;
ecx_cln_debug_pub.Add('------ Entering IS_UPDATE_REQUIRED API ------ ',2);
x_update_reqd := FALSE;
x_update_reqd := FALSE;
ecx_cln_debug_pub.Add('Collaboration need not be created/updated',1);
SELECT protocol_type,hub_user_id
INTO l_protocol_type, l_hub_user_id
FROM ecx_tp_details etd, ecx_tp_headers eth,
ecx_ext_processes eep
WHERE (eth.party_id = p_tr_partner_id or p_tr_partner_id is null )
AND eth.party_site_id = p_tr_partner_site
AND eth.party_type = p_tr_partner_type
AND eth.tp_header_id = etd.tp_header_id
AND eep.ext_type = p_xmlg_transaction_type
AND eep.ext_subtype = p_xmlg_transaction_subtype
AND eep.ext_process_id = etd.ext_process_id
AND eep.direction = 'OUT';
SELECT protocol_type
INTO l_protocol_type
FROM ecx_hubs eh, ecx_hub_users ehu
WHERE eh.hub_id = ehu.hub_id
AND ehu.hub_user_id = l_hub_user_id;
ecx_cln_debug_pub.Add('Collaboration need not be created/updated',1);
x_update_reqd :=FALSE;
ecx_cln_debug_pub.Add('Collaboration can be updated',1);
x_update_reqd := TRUE;
ecx_cln_debug_pub.Add('Collaboration can be updated',1);
x_update_reqd := TRUE;
ecx_cln_debug_pub.Add('Collaboration need not be created/updated',1);
x_update_reqd := FALSE;
x_update_reqd := TRUE;
ecx_cln_debug_pub.Add('------ Exiting IS_UPDATE_REQUIRED ------- ',2);
ecx_cln_debug_pub.Add('------ Exiting IS_UPDATE_REQUIRED ------- ',2);
ecx_cln_debug_pub.Add('------ Exiting IS_UPDATE_REQUIRED ------- ',2);
END IS_UPDATE_REQUIRED;
SELECT MAX(collaboration_dtl_id) INTO x_dtl_coll_id
FROM CLN_COLL_HIST_DTL
WHERE COLLABORATION_POINT = nvl(p_coll_point,'APPS')
AND ( XMLG_MSG_ID = p_xmlg_msg_id
OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number
);
SELECT MAX(collaboration_dtl_id) INTO x_dtl_coll_id
FROM CLN_COLL_HIST_DTL
WHERE COLLABORATION_POINT = nvl(p_coll_point,'APPS')
AND ( (XMLG_TRANSACTION_TYPE = p_xmlg_transaction_type
AND XMLG_TRANSACTION_SUBTYPE = p_xmlg_transaction_subtype
AND XMLG_DOCUMENT_ID = p_xmlg_document_id
AND DOCUMENT_DIRECTION = nvl( p_doc_direction, DOCUMENT_DIRECTION)
AND (XML_EVENT_KEY is null or p_xml_event_key is null or XML_EVENT_KEY = p_xml_event_key)
)
OR
(XMLG_INT_TRANSACTION_TYPE = p_xmlg_int_transaction_type
AND XMLG_INT_TRANSACTION_SUBTYPE = p_xmlg_int_transaction_subtype
AND XMLG_DOCUMENT_ID = p_xmlg_document_id
AND DOCUMENT_DIRECTION = nvl( p_doc_direction, DOCUMENT_DIRECTION)
AND (XML_EVENT_KEY is null or p_xml_event_key is null or XML_EVENT_KEY = p_xml_event_key)
)
);
SELECT COLLABORATION_ID INTO l_coll_id
FROM CLN_COLL_HIST_HDR
WHERE APPLICATION_REFERENCE_ID = p_ref_id
OR XMLG_MSG_ID = p_xmlg_msg_id
OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number;
SELECT COLLABORATION_ID INTO l_coll_id
FROM CLN_COLL_HIST_HDR
WHERE (APPLICATION_ID = p_app_id AND UNIQUE_ID1 = p_unique1)
OR (APPLICATION_ID = p_app_id AND UNIQUE_ID2 = p_unique2)
OR (APPLICATION_ID = p_app_id AND UNIQUE_ID3 = p_unique3)
OR (APPLICATION_ID = p_app_id AND UNIQUE_ID4 = p_unique4)
OR (APPLICATION_ID = p_app_id AND UNIQUE_ID5 = p_unique5);
SELECT max(COLLABORATION_DTL_ID) INTO x_dtl_coll_id
FROM CLN_COLL_HIST_DTL
WHERE COLLABORATION_ID = l_coll_id
AND COLLABORATION_DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_DIRECTION = p_doc_direction
AND COLLABORATION_POINT = p_coll_point;
SELECT internal_control_number, msgid, transaction_type, transaction_subtype,
document_number, party_type, partyid, party_site_id, direction, protocol_type,
message_standard, message_type, event_key
INTO l_xmlg_internal_control_number, l_xmlg_msg_id, l_xmlg_transaction_type, l_xmlg_transaction_subtype,
l_xmlg_document_id, l_tr_partner_type, l_tr_partner_id, l_tr_partner_site, l_doc_dir, l_sender_component,
l_xmlg_msg_standard, l_xmlg_msg_type, l_xml_event_key
FROM ECX_DOCLOGS
WHERE MSGID = HEXTORAW(p_xmlg_msg_id);
SELECT internal_control_number, msgid, transaction_type, transaction_subtype,
document_number, party_type, partyid, party_site_id, direction, protocol_type,
message_standard, message_type, event_key
INTO l_xmlg_internal_control_number, l_xmlg_msg_id, l_xmlg_transaction_type,
l_xmlg_transaction_subtype, l_xmlg_document_id, l_tr_partner_type, l_tr_partner_id,
l_tr_partner_site, l_doc_dir, l_sender_component, l_xmlg_msg_standard, l_xmlg_msg_type, l_xml_event_key
FROM ECX_DOCLOGS
WHERE INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number;
SELECT internal_control_number, msgid, transaction_type, transaction_subtype,
document_number, party_type, partyid, party_site_id, direction, protocol_type,
message_standard, message_type, event_key
INTO l_xmlg_internal_control_number, l_xmlg_msg_id, l_xmlg_transaction_type,
l_xmlg_transaction_subtype, l_xmlg_document_id, l_tr_partner_type, l_tr_partner_id,
l_tr_partner_site, l_doc_dir, l_sender_component, l_xmlg_msg_standard, l_xmlg_msg_type, l_xml_event_key
FROM ECX_DOCLOGS
WHERE transaction_type = p_xmlg_transaction_type
AND transaction_subtype = p_xmlg_transaction_subtype
AND document_number = p_xmlg_document_id
AND direction = NVL(p_doc_dir,direction)
AND (event_key is null or p_xml_event_key is null or event_key = p_xml_event_key);
SELECT doclogs.internal_control_number, doclogs.msgid, doclogs.transaction_type, doclogs.transaction_subtype,
doclogs.document_number, doclogs.party_type, doclogs.partyid, doclogs.party_site_id, doclogs.direction,
doclogs.protocol_type, doclogs.message_standard, doclogs.message_type, doclogs.event_key
INTO l_xmlg_internal_control_number, l_xmlg_msg_id, l_xmlg_transaction_type, l_xmlg_transaction_subtype,
l_xmlg_document_id, l_tr_partner_type, l_tr_partner_id,l_tr_partner_site, l_doc_dir,
l_sender_component, l_xmlg_msg_standard, l_xmlg_msg_type, l_xml_event_key
FROM ECX_TRANSACTIONS ecxtrans, ECX_EXT_PROCESSES ecxproc, ECX_DOCLOGS doclogs
WHERE ecxtrans.TRANSACTION_TYPE = NVL(p_xmlg_int_transaction_type,l_xmlg_int_transaction_type)
AND ecxtrans.TRANSACTION_SUBTYPE = NVL(p_xmlg_int_transaction_subtype,l_xmlg_int_transaction_subtype)
AND ecxproc.TRANSACTION_ID = ecxtrans.TRANSACTION_ID
AND ecxproc.DIRECTION = NVL(p_doc_dir,ecxproc.direction)
AND ecxproc.DIRECTION = doclogs.DIRECTION
AND (event_key is null or p_xml_event_key is null or event_key = p_xml_event_key)
AND doclogs.transaction_type = ecxproc.EXT_TYPE
AND doclogs.transaction_subtype = ecxproc.EXT_SUBTYPE
AND doclogs.document_number = p_xmlg_document_id;
SELECT ecxproc.EXT_TYPE,ecxproc.EXT_SUBTYPE, estd.standard_code
INTO l_xmlg_transaction_type, l_xmlg_transaction_subtype, l_xmlg_msg_standard
FROM ecx_tp_headers eth, ecx_tp_details etd, ECX_TRANSACTIONS ecxtrans, ECX_EXT_PROCESSES ecxproc, ecx_standards estd
WHERE eth.party_id = p_tr_partner_id
AND eth.party_site_id = p_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 = nvl(p_xmlg_int_transaction_type,l_xmlg_int_transaction_type)
AND ecxtrans.transaction_subtype = nvl(p_xmlg_int_transaction_subtype,l_xmlg_int_transaction_subtype)
AND ecxproc.direction = nvl(p_doc_dir,ecxproc.direction)
AND estd.standard_id = ecxproc.standard_id;
SELECT distinct ecxtrans.TRANSACTION_TYPE, ecxtrans.TRANSACTION_SUBTYPE
INTO l_xmlg_int_transaction_type, l_xmlg_int_transaction_subtype
FROM ECX_TRANSACTIONS ecxtrans, ECX_EXT_PROCESSES ecxproc
WHERE ecxtrans.TRANSACTION_ID = ecxproc.TRANSACTION_ID
AND ecxproc.EXT_TYPE = NVL(p_xmlg_transaction_type,l_xmlg_transaction_type)
AND ecxproc.EXT_SUBTYPE = NVL(p_xmlg_transaction_subtype,l_xmlg_transaction_subtype)
AND ecxproc.DIRECTION = NVL(p_doc_dir,l_doc_dir);
SELECT eth.party_id, eth.party_site_id, eth.party_type, estd.standard_code
INTO l_txn_partner_id, l_txn_partner_site, l_txn_partner_type, l_xmlg_msg_standard
FROM ecx_ext_processes eep, ecx_tp_details etd, ecx_tp_headers eth, ecx_standards estd
WHERE eep.ext_type = l_xmlg_transaction_type
AND eep.ext_subtype = l_xmlg_transaction_subtype
AND eep.standard_id = estd.standard_id
AND estd.standard_code = l_xmlg_msg_standard
AND eep.ext_process_id = etd.ext_process_id
AND etd.source_tp_location_code = l_tr_partner_site
AND eep.direction = l_doc_dir
AND eth.party_type = NVL(l_tr_partner_type,eth.party_type)
AND eth.tp_header_id = etd.tp_header_id
AND estd.standard_type = l_xmlg_msg_type;
SELECT eth.party_id, eth.party_site_id, eth.party_type, estd.standard_code
INTO l_txn_partner_id, l_txn_partner_site, l_txn_partner_type, l_xmlg_msg_standard
FROM ecx_ext_processes eep, ecx_tp_details etd, ecx_tp_headers eth, ecx_standards estd
WHERE eep.ext_type = l_xmlg_transaction_type
AND eep.ext_subtype = l_xmlg_transaction_subtype
AND eep.standard_id = estd.standard_id
AND estd.standard_code = l_xmlg_msg_standard
AND eep.ext_process_id = etd.ext_process_id
AND etd.source_tp_location_code = l_tr_partner_site
AND eep.direction = l_doc_dir
AND eth.tp_header_id = etd.tp_header_id
AND estd.standard_type = l_xmlg_msg_type;
SELECT application_id, collaboration_type, document_type
INTO l_application_id, l_collaboration_type, l_document_type
FROM CLN_CH_XMLGTXN_MAPPING
WHERE XMLG_TRANSACTION_TYPE = p_xmlg_transaction_type
AND XMLG_TRANSACTION_SUBTYPE = p_xmlg_transaction_subtype
AND DOCUMENT_DIRECTION = p_doc_dir ;
SELECT message_count
INTO l_message_count
FROM CLN_COLL_COMPLETED_STATUS
WHERE application_id = p_app_id
AND collaboration_type = p_coll_type
AND nvl(collaboration_standard,nvl(p_coll_standard,'~')) = nvl(p_coll_standard,'~')
AND document_type = p_doc_type
AND document_direction = p_doc_dir;
SELECT count('x')
INTO l_msg_count_in_hist
FROM CLN_COLL_HIST_HDR hdr, CLN_COLL_HIST_DTL dtl
WHERE hdr.collaboration_id = p_coll_id
AND hdr.collaboration_id = dtl.collaboration_id
AND hdr.application_id = p_app_id
AND hdr.collaboration_type = p_coll_type
AND dtl.collaboration_document_type = p_doc_type
AND dtl.document_direction = p_doc_dir;
SELECT COLLABORATION_ID INTO x_coll_id
FROM CLN_COLL_HIST_HDR
WHERE (APPLICATION_REFERENCE_ID=p_ref_id)
OR XMLG_MSG_ID = p_xmlg_msg_id
OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number;
SELECT COLLABORATION_ID INTO x_coll_id
FROM CLN_COLL_HIST_HDR
WHERE (APPLICATION_ID = p_app_id AND UNIQUE_ID1 = p_unique1)
OR (APPLICATION_ID = p_app_id AND UNIQUE_ID2 = p_unique2)
OR (APPLICATION_ID = p_app_id AND UNIQUE_ID3 = p_unique3)
OR (APPLICATION_ID = p_app_id AND UNIQUE_ID4 = p_unique4)
OR (APPLICATION_ID = p_app_id AND UNIQUE_ID5 = p_unique5);
SELECT COLLABORATION_ID INTO x_coll_id
FROM CLN_COLL_HIST_DTL
WHERE (XMLG_MSG_ID = p_xmlg_msg_id
OR XMLG_INTERNAL_CONTROL_NUMBER = p_xmlg_internal_control_number)
AND ROWNUM < 2;
SELECT COLLABORATION_ID INTO x_coll_id
FROM CLN_COLL_HIST_HDR
WHERE XMLG_TRANSACTION_TYPE = p_xmlg_transaction_type
AND XMLG_TRANSACTION_SUBTYPE = p_xmlg_transaction_subtype
AND XMLG_DOCUMENT_ID = p_xmlg_document_id
AND DOCUMENT_DIRECTION = nvl(p_doc_dir, DOCUMENT_DIRECTION)
AND (XML_EVENT_KEY is null or p_xml_event_key is null or XML_EVENT_KEY = p_xml_event_key);
SELECT doclogs.internal_control_number
INTO l_corrspnd_internal_cntrl_num
FROM ecx_doclogs doclogs, ecx_ext_processes eep, ecx_tp_details etd, ecx_tp_headers eth, ecx_standards estd
WHERE doclogs.direction = 'IN' and doclogs.field7 = l_data_area_refid
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 = doclogs.direction
--AND eth.party_type = NVL(doclogs.party_type,eth.party_type)
AND eth.tp_header_id = etd.tp_header_id
AND estd.standard_type = doclogs.message_type
AND eth.party_id = p_tr_partner_id
AND eth.party_site_id = p_tr_partner_site
AND eth.party_type = p_tr_partner_type;
SELECT distinct COLLABORATION_ID INTO x_coll_id
FROM cln_coll_hist_dtl
WHERE XMLG_INTERNAL_CONTROL_NUMBER = l_corrspnd_internal_cntrl_num;
l_msg_data := 'Collaboration successfully created/updated ';
l_coll_id := null; -- so that update collaboration is not called immediately after create collaboration.
ecx_cln_debug_pub.Add('.....Call to Update Collaboration API...........',1);
UPDATE_COLLABORATION(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
p_coll_id => l_coll_id,
p_app_id => l_app_id,
p_ref_id => p_ref_id,
p_rel_no => p_rel_no,
p_doc_no => p_doc_no,
p_doc_rev_no => p_doc_rev_no,
p_xmlg_transaction_type => l_xmlg_transaction_type,
p_xmlg_transaction_subtype => l_xmlg_transaction_subtype,
p_xmlg_document_id => l_xmlg_document_id,
p_resend_flag => p_resend_flag,
p_resend_count => p_resend_count,
p_disposition => p_disposition,
p_coll_status => p_coll_status,
p_doc_type => l_doc_type,
p_doc_dir => l_doc_dir,
p_coll_pt => p_coll_pt,
p_org_ref => p_org_ref,
p_doc_status => p_doc_status,
p_notification_id => p_notification_id,
p_msg_text => p_msg_text,
p_attr1 => p_attr1,
p_attr2 => p_attr2,
p_attr3 => p_attr3,
p_attr4 => p_attr4,
p_attr5 => p_attr5,
p_attr6 => p_attr6,
p_attr7 => p_attr7,
p_attr8 => p_attr8,
p_attr9 => p_attr9,
p_attr10 => p_attr10,
p_attr11 => p_attr11,
p_attr12 => p_attr12,
p_attr13 => p_attr13,
p_attr14 => p_attr14,
p_attr15 => p_attr15,
p_xmlg_msg_id => l_xmlg_msg_id,
p_unique1 => p_unique1,
p_unique2 => p_unique2,
p_unique3 => p_unique3,
p_unique4 => p_unique4,
p_unique5 => p_unique5,
p_tr_partner_type => l_tr_partner_type,
p_tr_partner_id => l_tr_partner_id,
p_tr_partner_site => l_tr_partner_site,
p_sender_component => l_sender_component,
p_rosettanet_check_required => p_rosettanet_check_required,
x_dtl_coll_id => l_dtl_coll_id,
p_xmlg_internal_control_number => l_xmlg_internal_control_number,
p_partner_doc_no => p_partner_doc_no,
p_org_id => p_org_id,
p_doc_creation_date => p_doc_creation_date,
p_doc_revision_date => p_doc_revision_date,
p_doc_owner => p_doc_owner,
p_xmlg_int_transaction_type => l_xmlg_int_transaction_type,
p_xmlg_int_transaction_subtype => l_xmlg_int_transaction_subtype,
p_xml_event_key => l_xml_event_key,
p_collaboration_standard => l_collaboration_standard,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_dattribute1 => p_dattribute1,
p_dattribute2 => p_dattribute2,
p_dattribute3 => p_dattribute3,
p_dattribute4 => p_dattribute4,
p_dattribute5 => p_dattribute5,
p_owner_role => p_owner_role );
l_msg_data := 'Error in UPDATE_COLLABORATION';