[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_item_update_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'IO';
l_sql_string := 'SELECT inventory_item_id, '||
'item_name, '||
'bom_item_type, '||
'bom_itype_desc, '||
'primary_unit_of_measure, '||
'description, '||
'inventory_item_status_code, '||
'item_status_desc, '||
'item_type, '||
'item_type_desc, '||
'primary_uom_code, '||
'start_date_active, '||
'end_date_active, '||
'attribute1, '||
'attribute2, '||
'attribute3, '||
'attribute4, '||
'attribute5, '||
'attribute6, '||
'attribute7, '||
'attribute8, '||
'attribute9, '||
'attribute10, '||
'attribute11, '||
'attribute12, '||
'attribute13, '||
'attribute14, '||
'attribute15 '||
'FROM xnb_itemmst_cats_v '||
'WHERE organization_id = ''' || p_org_id || '''';
-- The item has not been successfully published. Set indicator 'I' - Insert.
IF (l_cln_stat = 0 ) then
l_indicator := 'I';
-- The item has been successfully published earlier. Set indicator 'U' - Update.
ELSIF (l_cln_stat = 1) then
l_indicator := 'U';
--The Item_Id in this Array is used to update the Collaboration history
----------------------------------------------------------------------------------------------
l_item_id(l_rec_count) := l_item_rec.item_id;
-- The item has not been successfully published. Set indicator 'I' - Insert.
IF (l_cln_stat = 0 ) then
l_indicator := 'I';
-- The item has been successfully published earlier. Set indicator 'U' - Update.
ELSIF (l_cln_stat = 1) then
l_indicator := 'U';
--The Item_Id in this Array is used to update the Collaboration history
----------------------------------------------------------------------------------------------
l_item_id(l_rec_count) := l_item_rec.item_id;
/**** Private API to create and update the collaboration */
PROCEDURE create_cln_items ( p_bill_app_code IN VARCHAR2,
i IN NUMBER,
cln_result OUT NOCOPY NUMBER)
AS
l_key_create varchar2(90);
l_key_update varchar2(90);
l_parameter_list_update wf_parameter_list_t := wf_parameter_list_t();
SELECT party_type,
party_id,
party_site_id
INTO l_party_type,
l_party_id,
l_party_site
FROM ecx_oag_controlarea_tp_v
WHERE transaction_type = g_xnb_transaction_type
AND transaction_subtype = g_item_update_txn_subtype;
p_value => g_item_update_txn_subtype,
p_parameterlist => l_parameter_list_create);
--Update the collaboration for all items and make the status to be success
--for the trading partner.
-----------------------------------------------------------------------------------------
l_key_update := 'XNB'||'COLL_UPDATE_'||i||'_'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
xnb_debug.log('create_cln_items','After creating the key for update');
p_parameterlist => l_parameter_list_update
);
xnb_debug.log('create_cln_items','Document Status update done');
p_parameterlist => l_parameter_list_update
);
xnb_debug.log('create_cln_items','Orig Reference update done');
p_parameterlist => l_parameter_list_update
);
xnb_debug.log('create_cln_items','Reference Id update done');
p_parameterlist => l_parameter_list_update
);
xnb_debug.log('create_cln_items','Message Text update done');
p_parameterlist => l_parameter_list_update);
xnb_debug.log('create_cln_items','Transaction Type update done');
p_parameterlist => l_parameter_list_update);
xnb_debug.log('create_cln_items','Transaction SubType update done');
p_parameterlist => l_parameter_list_update);
wf_event.raise ( p_event_name => 'oracle.apps.cln.ch.collaboration.update',
p_event_key => l_key_update,
p_parameters => l_parameter_list_update);
xnb_debug.log('create_cln_items','Collaboration Updated for Doc no '|| l_item_id(i));
x_sql_string := x_sql_string || ' and trunc(last_update_date) >= trunc(to_date('''||p_from_date||''',''YYYY/MM/DD HH24:MI:SS''))';
--Else it is UPDATE
-----------------------------------------------------------------------------------------
IF l_pub_cnt = 0 then
wf_event.AddParameterToList (
p_name =>'PARAMETER1',
p_value => 'ADD',
p_parameterlist => l_wf_parameter_list);
p_value => 'UPDATE',
p_parameterlist => l_wf_parameter_list);
l_transaction_subtype := g_item_update_txn_subtype;
l_sql_string := 'SELECT inventory_item_id, '||
'item_name, '||
'bom_item_type, '||
'bom_itype_desc, '||
'primary_unit_of_measure, '||
'description, '||
'inventory_item_status_code, '||
'item_status_desc, '||
'item_type, '||
'item_type_desc, '||
'primary_uom_code, '||
'start_date_active, '||
'end_date_active, '||
'attribute1, '||
'attribute2, '||
'attribute3, '||
'attribute4, '||
'attribute5, '||
'attribute6, '||
'attribute7, '||
'attribute8, '||
'attribute9, '||
'attribute10, '||
'attribute11, '||
'attribute12, '||
'attribute13, '||
'attribute14, '||
'attribute15 '||
'FROM xnb_itemmst_cats_v '||
'WHERE organization_id = ''' || p_org_id || '''';
--Create new collaboration for all items and update the status to be success
--for the trading partner.
-----------------------------------------------------------------------------------------
--debug
xnb_debug.log('publish_item',' Before CLN Items Creation');
--Create new collaboration for all items and update the status to be success
--for the trading partner.
-----------------------------------------------------------------------------------------
FOR i IN 1..l_rec_cnt LOOP
create_cln_items (p_bill_app_code, i, cln_result);
SELECT invoiceable_item_flag
INTO l_flag
FROM mtl_system_items_vl
WHERE organization_id = l_org_id
and inventory_item_id = l_inv_item_id;