[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_insert IN OUT NOCOPY VARCHAR2,
x_catgry OUT NOCOPY VARCHAR2,
x_catsetname OUT NOCOPY VARCHAR2 )
IS
l_error_code NUMBER;
x_insert := 'TRUE';
x_insert := 'FALSE';
SELECT SUBSTR(p_concatgset, INSTR(p_concatgset,'=', 1, 1)+1,INSTR(p_concatgset,'|',1,1)-INSTR(p_concatgset,'=',1,1)-1)
INTO x_catgry
FROM dual;
SELECT SUBSTR(p_concatgset, INSTR(p_concatgset,'=', 1, 2)+1)
INTO x_catsetname
FROM dual;
x_insert := 'FALSE';
PROCEDURE RAISE_UPDATE_EVENT(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
p_internal_control_number IN NUMBER,
p_sender_header_id IN NUMBER,
p_receiver_header_id IN NUMBER,
x_supplier_name OUT NOCOPY VARCHAR2,
x_master_organization_id OUT NOCOPY NUMBER,
x_set_process_id OUT NOCOPY NUMBER,
x_cost_group_id OUT NOCOPY NUMBER)
IS
l_cln_ch_parameters wf_parameter_list_t;
cln_debug_pub.Add('-------- ENTERING RAISE_UPDATE_EVENT --------------', 2);
SELECT cln_generic_s.nextval INTO x_set_process_id FROM dual;
SELECT cst_lists_s.nextval INTO x_cost_group_id FROM dual;
SELECT PARTY_ID, PARTY_SITE_ID
INTO l_party_id,l_party_site_id
FROM ECX_TP_HEADERS
WHERE TP_HEADER_ID = p_sender_header_id ;
SELECT ORG_ID
INTO l_organization_id
FROM PO_VENDOR_SITES_ALL
WHERE VENDOR_ID = l_party_id
AND VENDOR_SITE_ID = l_party_site_id ;
SELECT MASTER_ORGANIZATION_ID
INTO l_master_organization_id
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_organization_id ;
SELECT NAME
INTO l_buyer_organization
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = l_master_organization_id;
SELECT VENDOR_NAME
INTO l_supplier_name
FROM PO_VENDORS
WHERE VENDOR_ID = l_party_id ;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
SELECT CLN_SYNCITEM_S.nextval into l_syncitem_seq from dual;
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
cln_debug_pub.Add('----------- EXITING RAISE_UPDATE_EVENT ------------', 2);
cln_debug_pub.Add('----------- ERROR:EXITING RAISE_UPDATE_EVENT ------------', 2);
cln_debug_pub.Add('----------- ERROR:EXITING RAISE_UPDATE_EVENT ------------', 2);
END RAISE_UPDATE_EVENT;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
IF (p_sync_indicator = 'Delete') THEN
IF(p_new_deletion_flag = 'N')THEN
p_new_deletion_flag := 'Y';
l_reference1 := 'Sync Ind: Delete';
IF (p_sync_indicator <> 'Delete') THEN
-- check for new revision here only if the sync indicator is
-- not marked as delete .Set the revision flag here.
BEGIN
IF (l_Debug_Level <= 1) THEN
cln_debug_pub.Add('check for new revision.....',1);
SELECT DISTINCT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item_number
AND organization_id = p_organization_id;
SELECT count(*)
INTO l_count
FROM mtl_item_revisions
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = p_organization_id
AND revision = p_item_revision;
SELECT HAZARD_CLASS_ID
INTO x_hazardous_id
FROM PO_HAZARD_CLASSES_TL
WHERE HAZARD_CLASS= p_hazardous_class
AND LANGUAGE = USERENV('LANG');
PROCEDURE INSERT_DATA(
p_return_status IN VARCHAR2,
p_sync_indicator IN VARCHAR2,
x_insert_data OUT NOCOPY VARCHAR2 )
IS
l_error_code NUMBER;
cln_debug_pub.Add('---------- ENTERING INSERT_DATA ------------', 2);
IF ((p_return_status = 'S') AND (p_sync_indicator <>'Delete')) THEN
x_insert_data := 'TRUE';
x_insert_data := 'FALSE';
cln_debug_pub.Add('Data To be Inserted -->'||x_insert_data, 1);
cln_debug_pub.Add('----------- EXITING INSERT_DATA ------------', 2);
cln_debug_pub.Add('----------- ERROR:EXITING INSERT_DATA ------------', 2);
END INSERT_DATA;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
cln_debug_pub.Add('------Calling RAISE_UPDATE_EVENT with ERROR status------',1);
cln_debug_pub.Add('---- SETTING EVENT PARAMETERS FOR UPDATE COLLABORATION ----', 1);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
SELECT PARTY_ID, PARTY_SITE_ID,PARTY_TYPE
INTO l_party_id, l_party_site_id, l_party_type
FROM ECX_TP_HEADERS
WHERE TP_HEADER_ID = l_sender_header_id ;
SELECT VENDOR_NAME
INTO l_supplier_name
FROM PO_VENDORS
WHERE VENDOR_ID = l_party_id ;
l_update_coll_msg VARCHAR2(2000);
SELECT status_code,completion_text,phase_code
INTO l_status_code, l_concurrent_msg,l_phase_code
FROM fnd_concurrent_requests
WHERE request_id = l_request_id;
l_update_coll_msg := NULL;
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
SELECT 'x'
INTO l_count_failed_rows
FROM DUAL
WHERE EXISTS (
/*SELECT 'x'
FROM MTL_INTERFACE_ERRORS
WHERE REQUEST_ID = l_request_id
AND TRANSACTION_ID > 0
AND rownum < 2*/
SELECT 'x'
FROM mtl_system_items_interface msit
WHERE process_flag IN (3,4)
AND set_process_id = l_set_process_id
UNION
SELECT 'x'
FROM mtl_item_revisions_interface mri
WHERE process_flag IN (3,4)
AND set_process_id = l_set_process_id
UNION
SELECT 'x'
FROM mtl_item_categories_interface mici
WHERE process_flag IN (3,4)
AND set_process_id = l_set_process_id
);
IF (l_update_coll_msg IS NULL) THEN
FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_SUCCESS_1');
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
IF (l_update_coll_msg IS NULL) THEN
FND_MESSAGE.SET_NAME('CLN','CLN_CH_CONCURRENT_SUCCESS_2');
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_update_coll_msg, l_cln_ch_parameters);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
sql_statement_error_msg := ' SELECT ERROR_MESSAGE, TRANSACTION_ID, TABLE_NAME'
||' FROM MTL_INTERFACE_ERRORS'
||' WHERE REQUEST_ID = '||l_request_id
||' AND TRANSACTION_ID > 0';
execute immediate 'select item_number from '||l_table_name ||' where TRANSACTION_ID = :1 and REQUEST_ID = :2'
into l_reference1
using l_transaction_id, l_request_id ;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
SELECT resource_id
INTO l_resource_id
FROM BOM_RESOURCES
WHERE cost_element_id = 1
AND organization_id = l_master_organization_id
AND rownum < 2;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
cln_debug_pub.Add('Message for update collaboration = '||l_msg_data, 1);
cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
sql_statement := ' SELECT DISTINCT INVENTORY_ITEM_ID,'
||' item_number, process_flag'
||' FROM mtl_system_items_interface msit'
||' WHERE process_flag IN (3,4)'
||' AND set_process_id = '||l_set_process_id
||' AND process_flag IN (3,4)'
||' UNION'
||' SELECT DISTINCT INVENTORY_ITEM_ID,'
||' item_number, process_flag'
||' FROM mtl_item_revisions_interface mri'
||' WHERE process_flag IN (3,4)'
||' AND set_process_id = '||l_set_process_id
||' AND process_flag IN (3,4)'
||' UNION'
||' SELECT DISTINCT INVENTORY_ITEM_ID,'
||' item_number, process_flag'
||' FROM mtl_item_categories_interface mici'
||' WHERE process_flag IN (3,4)'
||' AND set_process_id = '||l_set_process_id
||' AND process_flag IN (3,4)';
DELETE FROM CLN_CST_DTLS_TEMP
WHERE item_number = l_item_number
AND group_id = l_cst_group_id;
sql_statement_1 := ' SELECT DISTINCT ITEM_NUMBER'
||' FROM CLN_CST_DTLS_TEMP'
||' WHERE group_id = '|| l_cst_group_id;
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b
WHERE SEGMENT1 = l_item_number AND organization_id = l_master_organization_id;
UPDATE CLN_CST_DTLS_TEMP
SET inventory_item_id = l_inventory_item_id,
resource_id = l_resource_id
-- cost_type = l_cost_type
WHERE item_number = l_item_number
AND group_id = l_cst_group_id;
cln_debug_pub.Add('Insertion of the rows in the interface table ......', 1);
INSERT INTO CST_ITEM_CST_DTLS_INTERFACE
(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
RESOURCE_ID,
USAGE_RATE_OR_AMOUNT,
COST_ELEMENT_ID,
PROCESS_FLAG,
COST_TYPE,
GROUP_ID,
ITEM_NUMBER,
ITEM_COST
)(
SELECT
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
RESOURCE_ID,
USAGE_RATE_OR_AMOUNT,
COST_ELEMENT_ID,
PROCESS_FLAG,
COST_TYPE,
GROUP_ID,
ITEM_NUMBER,
ITEM_COST
FROM
CLN_CST_DTLS_TEMP
WHERE group_id = l_cst_group_id
);
cln_debug_pub.Add('Insertion of the rows in the interface table successful', 1);
PROCEDURE UPDATE_COLLB_STATUS (
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
x_resultout OUT NOCOPY VARCHAR2 )
IS
l_internal_control_number NUMBER;
l_update_coll_msg VARCHAR2(2000);
cln_debug_pub.Add('------ Entering UPDATE_COLLB_STATUS API ------ ', 2);
SELECT trim(message_standard)
INTO l_message_standard
FROM ecx_doclogs
WHERE INTERNAL_CONTROL_NUMBER = l_internal_control_number;
SELECT status_code,completion_text,phase_code
INTO l_status_code, l_concurrent_msg,l_phase_code
FROM fnd_concurrent_requests
WHERE request_id = l_request_id;
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
SELECT COUNT(*)
INTO l_count_failed_rows
FROM dual
WHERE exists
( SELECT 'x'
FROM cst_item_cst_dtls_interface cicdi
WHERE error_flag = 'E'
AND group_id = l_cst_group_id
AND rownum < 2
);
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_update_coll_msg, l_cln_ch_parameters);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
cln_debug_pub.Add('Message for the trading partner : '||l_update_coll_msg, 1);
wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_update_coll_msg);
p_notification_desc => l_update_coll_msg,
p_status => 'ERROR',
p_tp_id => l_sender_header_id,
p_reference => NULL,
p_coll_point => 'APPS',
p_int_con_no => l_internal_control_number);
sql_statement_error_msg := ' SELECT ERROR_EXPLANATION, ITEM_NUMBER'
||' FROM CST_ITEM_CST_DTLS_INTERFACE'
||' WHERE ORGANIZATION_ID = '||l_master_organization_id
||' AND GROUP_ID = '||l_cst_group_id
||' AND ERROR_EXPLANATION IS NOT NULL';
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
SELECT MANUFACTURER_ID
INTO l_manufacture_id
FROM mtl_manufacturers
WHERE MANUFACTURER_NAME = l_supplier_name
AND rownum < 2;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
cln_debug_pub.Add('Message for update collaboration = '||l_return_desc_tp, 1);
cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
cln_debug_pub.Add('------- Exiting UPDATE_COLLB_STATUS API --------- ',2);
WF_CORE.CONTEXT('CLN_SYNC_ITEM_PKG', 'UPDATE_COLLB_STATUS', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
FND_MESSAGE.SET_TOKEN('ACTIVITY','STATUS_UPDATE');
cln_debug_pub.Add('------- ERROR:Exiting UPDATE_COLLB_STATUS API --------- ',6);
END UPDATE_COLLB_STATUS;
l_update_coll_msg VARCHAR2(2000);
SELECT status_code,completion_text,phase_code
INTO l_status_code, l_concurrent_msg,l_phase_code
FROM fnd_concurrent_requests
WHERE request_id = l_request_id;
l_update_coll_msg := l_concurrent_msg;
l_update_coll_msg := FND_MESSAGE.GET;
l_return_desc_tp := l_update_coll_msg;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_update_coll_msg, l_cln_ch_parameters);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
cln_debug_pub.Add('Message for the trading partner : '||l_update_coll_msg, 1);
wf_engine.SetItemAttrText(p_itemtype, p_itemkey, 'RETURN_MSG_TP', l_update_coll_msg);
p_notification_desc => l_update_coll_msg,
p_status => 'ERROR',
p_tp_id => l_sender_header_id,
p_reference => NULL,
p_coll_point => 'APPS',
p_int_con_no => l_internal_control_number);
DELETE FROM CLN_CST_DTLS_TEMP
WHERE group_id = l_cst_group_id;
cln_debug_pub.Add('Rows from the CLN_CST_DTLS_TEMP table deleted...', 1);
PROCEDURE UPDATE_COLLB_STATUS_RN (
p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
x_resultout OUT NOCOPY VARCHAR2 )
IS
l_internal_control_number NUMBER;
l_update_coll_msg VARCHAR2(2000);
cln_debug_pub.Add('------ Entering UPDATE_COLLB_STATUS_RN API ------ ', 2);
l_msg_data := 'Collaboration updated with appropriate status';
l_update_coll_msg := FND_MESSAGE.GET;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
cln_debug_pub.Add('Message for update collaboration = '||l_update_coll_msg, 1);
cln_debug_pub.Add('Event Key for update collaboration = '||l_event_key, 1);
WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_update_coll_msg, l_cln_ch_parameters);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
WF_EVENT.Raise('oracle.apps.cln.ch.collaboration.update',l_event_key, NULL, l_cln_ch_parameters, NULL);
cln_debug_pub.Add('------- Exiting UPDATE_COLLB_STATUS_RN API --------- ',2);
cln_debug_pub.Add('------- ERROR:Exiting UPDATE_COLLB_STATUS_RN API --------- ',6);
END UPDATE_COLLB_STATUS_RN;
SELECT cln_generic_s.nextval INTO l_event_key FROM dual;
l_reference1 := 'Sync Ind: Delete';