The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_LAST_UPDATED_BY DBMS_SQL.Number_Table;
v_LAST_UPDATE_DATE DBMS_SQL.Date_Table;
vSelectStmt VARCHAR2(8000);
v_InsertStmt VARCHAR2(8000);
v_insert_cursor INTEGER ;
vExLastUpdateDate DATE;
vLastUpdateDate DATE := sysdate;
selectCounter in number,
wfAttrValue out NOCOPY Varchar2 );
Procedure fetchAndInsertDetails;
Procedure DeletePreviousData( l_exception_id in number);
Procedure insertExceptionSummary(l_exception_id in number);
Procedure updateExceptionSummary;
PROCEDURE Delete_Item(l_type in varchar2, l_key in varchar2);
PROCEDURE deleteResolvedExceptions;
select 1
from MSC_EXCEPTION_PREFERENCES ep,
fnd_user u
where ep.user_id = u.user_id
and u.user_name = p_user
and exception_type_lookup_code = p_excep_type
and rank > 0;
l_select_flag number;
fetch check_user into l_select_flag;
SELECT cont.name
FROM msc_partner_contacts cont,
msc_system_items sys,
msc_trading_partners mtp
WHERE sys.item_name = p_item
AND sys.plan_id = -1
AND cont.partner_id = sys.buyer_id
AND cont.partner_type = 4
and sys.organization_id=mtp.sr_tp_id
and mtp.organization_code = p_org
and mtp.partner_type=3;
SELECT cont.name
FROM msc_partner_contacts cont,
msc_system_items sys,
msc_trading_partners mtp
WHERE sys.item_name = p_item
AND sys.plan_id = -1
AND cont.partner_id = sys.buyer_id
AND cont.partner_type = 4
and sys.organization_id=mtp.sr_tp_id
and mtp.sr_tp_id =mtp.sr_tp_id
and mtp.sr_tp_id =mtp.master_organization
and mtp.partner_type=3;
log_message('Warning: Buyer is specified for notification but item is not selected');
select
distinct mpc.name
from
msc_partner_contacts mpc,
msc_trading_partners mtp,
msc_trading_partner_sites mtps,
msc_trading_partners mtporg
where
mpc.partner_id=mtp.partner_id
and mpc.partner_site_id=mtps.partner_site_id
and mpc.PARTNER_TYPE =1--supplier
and mtp.partner_name = p_sup_name--supplier_name
and mtp.sr_instance_id=mtporg.sr_instance_id
and mtporg.partner_type=3
and mtporg.organization_code = p_publisher_site_name--org_NAME
and mpc.sr_instance_id=mtporg.sr_instance_id
and mtps.tp_site_code = p_sup_site_name--supplier_site_name
and mtps.sr_instance_id=mtporg.sr_instance_id
and mtps.partner_id=mtp.partner_id;
select
distinct mpc.name
from
msc_partner_contacts mpc,
msc_trading_partners mtp,
msc_trading_partners mtporg
where
mpc.partner_id=mtp.MODELED_SUPPLIER_ID
and mpc.partner_site_id=mtp.MODELED_SUPPLIER_SITE_ID
and mpc.PARTNER_TYPE =1--supplier
and mtp.organization_code = p_sup_site_name--supplier modelled as an org
and mtp.partner_type=3
and mtp.sr_instance_id=mpc.sr_instance_id
and mtporg.partner_name=p_sup_name
and mtp.sr_instance_id=mtporg.sr_instance_id;
log_message('Warning: Supplier Contact is selected for notification but Company:Site is not defined in the Output attribute of the Custom Exception.');
log_message('Warning: Supplier Contact is selected for notification but either Company:Supplier is not defined in the Output attribute of the Custom Exception or the Supplier does not exist.');
log_message('Warning: Supplier Contact is selected for notification but Company:Supplier Site is not defined in the Output attribute of the Custom Exception.');
v_insert_cursor := DBMS_SQL.OPEN_CURSOR;
log_message('before select parseStmt' );
parseStmt(v_fetch_cursor,vSelectStmt||vFromClause||
vWhereStmt||vGroupByStmt||vHavingWhere||vSortStmt);
log_message('before DeletePreviousData' );
DeletePreviousData( v_exception_id);
insertExceptionSummary(v_exception_id);
log_message('before insert parseStmt' );
parseStmt(v_insert_cursor, v_InsertStmt||v_ValueStmt);
/* fetchAndInsertDetails does following
1. fetch rows,
2. insert into exception detail table
3. Commit data
4. For each row, create workflow process, iniitialize workflow attributes,
build notification title, build urls in notification, launch workflow
*/
if v_debug then
log_message('before fetchAndInsertDetails' );
fetchAndInsertDetails;
log_message('before deleteResolvedExceptions');
deleteResolvedExceptions ;
updateExceptionSummary;
if dbms_sql.is_open(v_insert_cursor) THEN
dbms_sql.close_cursor(v_insert_cursor);
if dbms_sql.is_open(v_insert_cursor) THEN
dbms_sql.close_cursor(v_insert_cursor);
select
ex.NAME,
FND.USER_NAME,
ex.WF_ITEM_TYPE,
ex.WF_PROCESS,
ex.WF_LAUNCH_FLAG,
translate(ex.NOTIFICATION_TEXT, fnd_global.local_chr(13) || fnd_global.local_chr(10), ' '),
ex.company_id,
ex.LAST_RUN_DATE,
ex.LAST_UPDATE_DATE,
ex.REFRESH_NUMBER
--ex.item_planner_ntf_flag
from
MSC_USER_EXCEPTIONS ex
,fnd_user fnd
where
ex.exception_id = v_exception_id
and ex.CREATED_BY = fnd.USER_ID;
vExLastUpdateDate,
vExRefreshNumber;
Select
AkRegItem.attribute3,
AkRegItem.attribute4,
comp.seq_num ,
decode(comp.component_type,1,'SELECT',2,'FILTER','5','ADVWHERE','6','SIMPLECONDITION',NULL) , -- bug# 2365812
comp.component_type comp_order, -- 2393803
akattr.DATA_TYPE,
AkRegItem.DISPLAY_VALUE_LENGTH,
nvl(comp.LABEL, AkRegItemTl.ATTRIBUTE_LABEL_LONG),
NULL,
comp.COMPONENT_VALUE1,
comp.COMPONENT_VALUE2,
comp.DATE_FILTER_FLAG,
comp.ROLLING_DATE_FLAG,
comp.ROLLING_NUMBER,
comp.ROLLING_TYPE,
comp.attribute1 --bug# 2410159
BULK COLLECT INTO ColumnNameList,DetColumnNameList,SeqNumList,
AttributeTypeList, OrderTypeList, DataTypeList,DisplayLengthList,
DisplayLableList,CalculationNameList,CompValueList1,CompValueList2,
DateFilterFlagList,RollingDateFlagList,RollingNumberList,RollingTypeList,OpColumnNameList
From
ak_attributes akattr,
ak_region_items AkRegItem,
ak_region_items_tl AkRegItemTl,
MSC_USER_EXCEPTIONS mse,
MSC_USER_EXCEPTION_COMPONENTS comp
where
mse.region_code = 'MSCUSEREXCEPTION'
AND mse.exception_id = v_exception_id
AND mse.region_code = AkRegItem.region_code
AND mse.exception_id = comp.exception_id
AND comp.component_type in (1, 2, 5, 6) -- bug# 2365812
AND comp.ak_attribute_code = AkRegItem.attribute_code
AND AkRegItem.REGION_APPLICATION_ID = 724
AND AkRegItem.ATTRIBUTE_APPLICATION_ID = akattr.ATTRIBUTE_APPLICATION_ID
AND AkRegItem.ATTRIBUTE_CODE = akattr.ATTRIBUTE_CODE
AND AkRegItem.REGION_CODE = AkRegItemTl.REGION_CODE
AND AkRegItem.ATTRIBUTE_CODE = AkRegItemTl.ATTRIBUTE_CODE
AND AkRegItem.REGION_APPLICATION_ID = AkRegItemTl.REGION_APPLICATION_ID
AND AkRegItem.ATTRIBUTE_APPLICATION_ID = AkRegItemTl.ATTRIBUTE_APPLICATION_ID
AND AkRegItemTl.LANGUAGE = 'US'
/* this will give you user calculations */
union
Select
exp.Expression1,
NULL,
comp.seq_num,
decode(comp.component_type,4,'CALCULATION',NULL) ,
decode(comp.component_type,4 ,1,NULL) comp_order , --2393803
exp.CALCULATION_DATATYPE,
exp.DISPLAY_LENGTH,
comp.Label,
exp.NAME,
NULL,
NULL,
NULL,
NULL,
to_number(null),
NULL,
NULL --bug# 2410159
from
MSC_USER_EXCEPTION_COMPONENTS comp,
MSC_USER_ADV_EXPRESSIONS exp
where
comp.exception_id = v_exception_id
AND comp. component_type in (4)
AND comp.expression_id = exp.expression_id
order by comp_order, seq_num; -- bug# 2365812
vSelectStmt := NULL;
v_InsertStmt := NULL;
if AttributeTypeList(i) in ( 'SELECT','CALCULATION') Then
num1 := num1 + 1;
if vSelectStmt is null then
vSelectStmt := ColumnNameList(i);
vSelectStmt := vSelectStmt||','||ColumnNameList(i);
We need to build insert and values string too.
Whatever is selected to be used for insert.
For 'SELECT' p_out_column can be null
*/
if DetColumnNameList(i) is not null then
if v_InsertStmt is null then
v_InsertStmt := ' Insert into MSC_X_EXCEPTION_DETAILS( '|| DetColumnNameList(i);
v_InsertStmt := v_InsertStmt ||','||DetColumnNameList(i);
if v_InsertStmt is null then
v_InsertStmt := ' Insert into MSC_X_EXCEPTION_DETAILS( '||
l_colStr;
v_InsertStmt := v_InsertStmt || ','||l_colStr;
select attribute3 into OpColumnNameList(i)
from ak_region_items
where attribute_code = OpColumnNameList(i)
and REGION_APPLICATION_ID = 724
and region_code = 'MSCUSEREXCEPTION';
2. there is no group by but having clause specfied, insert group by before having
3. there is no group by and and no having clause specified, generate group by
*/
groupByPos1 := INSTR(upper(vAdvWhere),'GROUP BY');
if vSelectStmt is null then
FND_MESSAGE.SET_NAME('MSC', 'MSC_UDE_SELECT_NULL');
elsif vSelectStmt is not null and
vGroupByFlag <> 'Y' then
vGroupByStmt := NULL;
if INSTR(vSelectStmt,'COMPANY.' ) <> 0 OR
INSTR(vWhereStmt,'COMPANY.') <> 0 then
vSelectStmt := vSelectStmt||', COMPANY.TRANSACTION_ID ';
v_InsertStmt := v_InsertStmt||', TRANSACTION_ID1 ';
if INSTR(vSelectStmt,'CUSTOMER.' ) <> 0 OR
INSTR(vWhereStmt,'CUSTOMER.') <> 0 then
vSelectStmt := vSelectStmt||', CUSTOMER.TRANSACTION_ID ';
v_InsertStmt := v_InsertStmt||', TRANSACTION_ID2 ';
if INSTR(vSelectStmt,'SUPPLIER.' ) <> 0 OR
INSTR(vWhereStmt,'SUPPLIER.') <> 0 then
vSelectStmt := vSelectStmt||', SUPPLIER.TRANSACTION_ID ';
v_InsertStmt := v_InsertStmt||', TRANSACTION_ID3 ';
vSelectStmt := 'Select ' || vSelectStmt ;
v_InsertStmt := v_InsertStmt ||
',CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,'||
'EXCEPTION_DETAIL_ID,EXCEPTION_TYPE,EXCEPTION_TYPE_NAME,EXCEPTION_GROUP, '||
'EXCEPTION_GROUP_NAME,OWNING_COMPANY_ID ) ';
',:CREATED_BY,:CREATION_DATE,:LAST_UPDATED_BY,:LAST_UPDATE_DATE,'||
':EXCEPTION_DETAIL_ID,:EXCEPTION_TYPE,:EXCEPTION_TYPE_NAME,:EXCEPTION_GROUP,'||
':EXCEPTION_GROUP_NAME ,:OWNING_COMPANY_ID) ';
if ( INSTR(UPPER(vSelectStmt),'COMPANY.') <> 0 ) OR
(INSTR(UPPER(vWhereStmt),'COMPANY.') <> 0 ) then
vFromClause := ' FROM msc_sup_dem_ent_custom_ex_v COMPANY ';
if ( INSTR(UPPER(vSelectStmt),'SUPPLIER.') <> 0 ) OR
( INSTR(UPPER(vWhereStmt),'SUPPLIER.') <> 0 ) then
if vFromClause is null then
vFromClause := ' FROM msc_sup_dem_ent_custom_ex_v SUPPLIER ';
if ( INSTR(UPPER(vSelectStmt),'CUSTOMER.') <> 0 ) OR
( INSTR(UPPER(vWhereStmt),'CUSTOMER.') <> 0 ) then
if vFromClause is null then
vFromClause := ' FROM msc_sup_dem_ent_custom_ex_v CUSTOMER ';
cursor lastUpdateC is
select max(LAST_UPDATE_DATE)
from MSC_USER_EXCEPTION_COMPONENTS
where exception_id = v_exception_id;
cursor lastUpdateE is
select max(LAST_UPDATE_DATE)
from MSC_USER_ADV_EXPRESSIONS
where expression_id in ( select expression_id
from MSC_USER_EXCEPTION_COMPONENTS
where exception_id = v_exception_id);
l_lastupdatedateC DATE;
l_lastupdatedateE DATE;
capsString := upper(vWhereStmt||vSelectStmt);
select max(LAST_REFRESH_NUMBER)
into vNewExRefreshNumber
from msc_sup_Dem_entries;
open lastUpdateC ;
fetch lastUpdateC into l_lastUpdateDateC;
close lastUpdateC;
open lastUpdateE ;
fetch lastUpdateE into l_lastUpdateDateE;
close lastUpdateE;
if l_lastUpdateDateC > vLastRunDate OR
l_lastUpdateDateE > vLastRunDate Then
--vExLastUpdateDate > vLastRunDate Then
log_message('Exception definition changed since last Run. Exception to be run for complete data');
log_message('CompDate='||l_lastUpdateDateC||' Exp Date='||l_lastUpdateDateE||' LastRun='||vLastRunDate);
log_message('Previous exception output to be deleted');
if AttributeTypeList(columnCounter) in ( 'SELECT','CALCULATION') then
if DataTypeList(columnCounter) in ('DATE','DATETIME') then
dateCounter := dateCounter + 1;
Procedure DeletePreviousData( l_exception_id in number) is
cursor detailIds is
select to_char(exception_Detail_id)
from msc_x_exception_details
where EXCEPTION_TYPE = l_exception_id
and EXCEPTION_GROUP = -99;
Delete_Item(v_item_type,lExceptionDetailsId);
delete from msc_x_exception_details
where EXCEPTION_TYPE = l_exception_id
and EXCEPTION_GROUP = -99;
log_message('Number of records deleted from MSC_X_EXCEPTION_DETAILS ='||SQL%ROWCOUNT);
update msc_item_exceptions
set exception_count = 0
where EXCEPTION_TYPE = l_exception_id
and EXCEPTION_GROUP = -99;
log_message('Number of records updated from MSC_ITEM_EXCEPTIONS = '||SQL%ROWCOUNT);
End DeletePreviousData;
Procedure insertExceptionSummary(l_exception_id in number) is
Cursor summaryRow is
select count(*)
from msc_item_exceptions
where EXCEPTION_TYPE = l_exception_id
and EXCEPTION_GROUP = -99;
log_message('Inserting into msc_item_exceptions. Exception_type='||l_exception_id);
insert into msc_item_exceptions(
PLAN_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
INVENTORY_ITEM_ID,
EXCEPTION_TYPE,
EXCEPTION_COUNT,
EXCEPTION_GROUP,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
COMPANY_ID
) VALUES
(
-1,
-1,
-1,
-1,
l_exception_id,
null,
-99,
sysdate,
v_user_id,
sysdate,
v_user_id,
v_request_id,
v_company_id
);
end insertExceptionSummary;
Procedure fetchAndInsertDetails is
pItemKey VARCHAR2(30);
log_message('Before column_value for select and bind array for insert');
if AttributeTypeList(columnCounter) in ('SELECT','CALCULATION') then
if DataTypeList(columnCounter) in ('DATE','DATETIME') then
dateCounter := dateCounter + 1;
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':TRANSACTION_ID1',vTransactionId1,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':TRANSACTION_ID2',vTransactionId2,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':TRANSACTION_ID3',vTransactionId3,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
v_CREATION_DATE(i) := vLastUpdateDate;
v_LAST_UPDATED_BY(i) := v_user_id;
v_LAST_UPDATE_DATE(i) := vLastUpdateDate;
select msc_x_exception_details_s.nextval into v_EXCEPTION_DETAIL_ID(i) from dual;
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':CREATED_BY',v_CREATED_BY,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':CREATION_DATE',v_CREATION_DATE,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':LAST_UPDATED_BY',v_LAST_UPDATED_BY,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':LAST_UPDATE_DATE',v_LAST_UPDATE_DATE,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_DETAIL_ID',v_EXCEPTION_DETAIL_ID,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_TYPE',vExceptionTypeArray,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_TYPE_NAME',vExceptionTypeNameArray,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_GROUP',vExceptionGroupArray,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':EXCEPTION_GROUP_NAME',vExceptionGroupNameArray,1,v_NumRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':OWNING_COMPANY_ID',vOwningCompanyIdArray,1,v_NumRows);
delete from msc_x_exception_details
where EXCEPTION_TYPE = v_exception_id
and EXCEPTION_GROUP = -99
and TRANSACTION_ID1 = vTransactionId1(i);
delete from msc_x_exception_details
where EXCEPTION_TYPE = v_exception_id
and EXCEPTION_GROUP = -99
and TRANSACTION_ID2 = vTransactionId2(i);
delete from msc_x_exception_details
where EXCEPTION_TYPE = v_exception_id
and EXCEPTION_GROUP = -99
and TRANSACTION_ID3 = vTransactionId3(i);
log_message('Before insert execution ');
v_ReturnCode := DBMS_SQL.EXECUTE(v_insert_cursor);
log_message('number of records inserted='||v_ReturnCode);
DBMS_SQL.CLOSE_CURSOR(v_insert_cursor);
end fetchAndInsertDetails;
Procedure deleteResolvedExceptions is
lDeletSqlStr VARCHAR2(4000);
capsString := upper(vWhereStmt||vSelectStmt);
lDeletSqlStr := 'delete from msc_x_exception_details edtl where edtl.exception_type = :lExceptionType '||
' and edtl.exception_group = -99 and edtl.last_update_date <> :vLastUpdateDate '||
' and edtl.'||lTransactionIdDetName1||' in '||
' ( select '||lviewName||'.'||'transaction_id '|| lFromView ||' where '||v_addedWhereClause ||
')';
using v_exception_id,vLastUpdateDate,
v_company_id,v_company_id,v_company_id;
End deleteResolvedExceptions ;
Procedure updateExceptionSummary is
begin
update msc_item_exceptions
set EXCEPTION_COUNT = (select count(*)
from msc_x_exception_details
where EXCEPTION_TYPE = v_exception_id
and EXCEPTION_GROUP = -99 )
where EXCEPTION_TYPE = v_exception_id
and EXCEPTION_GROUP = -99;
update MSC_USER_EXCEPTIONS
set
LAST_RUN_DATE = sysdate
,REFRESH_NUMBER = vNewExRefreshNumber
,REQUEST_ID = v_request_id
where EXCEPTION_ID = v_exception_id;
end updateExceptionSummary;
selectCounter Number;
selectCounter := 0;
if AttributeTypeList(columnCounter) in ('SELECT','CALCULATION') then
selectCounter := selectCounter +1;
if AttributeTypeList(columnCounter) = 'SELECT' Then
-- attributes are named as p.db_column_name, s.db_column_name and
--c.db_column_name in workflow as internal name cannot be greater then 30 in wf
l_temp_position := INSTR(ColumnNameList(columnCounter),'.');
selectCounter,
wfAttrValue);
if AttributeTypeList(columnCounter) = 'SELECT' Then
columnNtfTableHeader := columnNtfTableHeader||''||DisplayLableList(columnCounter)||' ';
fnd_message.set_token('CREATION_DATE',vLastUpdateDate);
/* select fnd_profile.VALUE_SPECIFIC('APPS_WEB_AGENT',v_user_id)
into details_url from dual;
Select function_id into lFunctionId
from fnd_form_functions
where function_name = 'MSC_EXCEPTION_DETAILS';
selectCounter in number,
wfAttrValue out NOCOPY varchar2) is
l_DateValue DATE;
if AttributeTypeList(columnCounter) = 'SELECT' then
wf_engine.SetItemAttrDate ( itemtype => v_item_type,
itemkey => l_item_key,
aname => wfAttributeName ,
avalue => l_dateValue );
if AttributeTypeList(columnCounter) = 'SELECT' then
wf_engine.SetItemAttrNumber ( itemtype => v_item_type,
itemkey => l_item_key,
aname => wfAttributeName ,
avalue => l_SelValue );
if AttributeTypeList(columnCounter) = 'SELECT' then
wf_engine.SetItemAttrText ( itemtype => v_item_type,
itemkey => l_item_key,
aname => wfAttributeName ,
avalue => l_varcharValue );
log_message( 'col_counter = '||selectCounter||' and tok1='||v_notificationToken1||' and tok2='||v_notificationToken2);
if selectCounter = v_notificationToken1 OR
selectCounter = v_notificationToken2 OR
selectCounter = v_notificationToken3 Then
v_notificationTitle := substr(replace(v_notificationTitle ,
vNotificationSep||to_char(selectCounter),
l_SelValue),
1,2000 );
select meaning
into l_message_group
from fnd_lookup_values
where lookup_type = 'MSC_X_EXCEPTION_GROUP'
and lookup_code = p_exception_group
and language = userenv('LANG') ;
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date1,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar1,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number1,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date2,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar2,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number2,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date3,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar3,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number3,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date4,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar4,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number4,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date5,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar5,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number5,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date6,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar6,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number6,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_Date7,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar7,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number7,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar8,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number8,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar9,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number9,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar10,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_number10,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar11,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar12,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar13,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar14,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar15,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar16,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar17,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar18,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar19,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
DBMS_SQL.BIND_ARRAY(v_insert_cursor,':A'||to_char(columnCounter), v_varchar20,vTotalFetchedRows-v_NumRows+1,vTotalFetchedRows);
select WF_ROLE,WF_ROLE_TYPE
from MSC_USER_EXCEPTION_NTFS
where EXCEPTION_ID = l_exception_id and
SEND_NTF_FLAG = 'Y' ;
select NAME
from wf_local_users
where Name = lEmailAddress;
select distinct pl.user_name,
pl.planner_code
from msc_planners pl,
msc_system_items itm
where itm.plan_id = -1
--and itm.organization_id = p_organization_id
and itm.item_name = p_item
--and itm.sr_instance_id = pl.sr_instance_id
and pl.organization_id = itm.organization_id
and pl.planner_code = itm.planner_code;
select distinct pl.user_name,
pl.planner_code
from msc_planners pl,
msc_system_items itm,
msc_trading_partners mtp
where itm.plan_id = -1
and itm.organization_id = pl.organization_id
and itm.item_name = p_item
and pl.organization_id = itm.organization_id
and pl.planner_code = itm.planner_code
and pl.organization_id=mtp.sr_tp_id
and mtp.organization_code =p_org
and mtp.partner_type=3;
select distinct pl.user_name,
pl.planner_code
from msc_planners pl,
msc_system_items itm,
msc_trading_partners mtp
where itm.plan_id = -1
and itm.organization_id = pl.organization_id
and itm.item_name = p_item
and pl.organization_id = itm.organization_id
and pl.planner_code = itm.planner_code
and pl.organization_id=mtp.sr_tp_id
and mtp.sr_tp_id =mtp.master_organization
and mtp.partner_type=3;
log_message('Warning: Planner Code is specified for notification but item is not selected');
PROCEDURE Delete_Item(l_type in varchar2, l_key in varchar2)
IS
CURSOR c1 IS
select nt.notification_id,nt.status
from wf_item_activity_statuses st,
wf_notifications nt
where st.item_type = l_type
and st.item_key like l_key
and nt.notification_id = st.notification_id
union
select nt.notification_id,nt.status
from wf_item_activity_statuses_h st1,wf_notifications nt
where st1.item_type = l_type
and st1.item_key like l_key
and
nt.notification_id = st1.notification_id;
update wf_notifications set
end_date = sysdate
where notification_id = aRec.notification_id;
update wf_items set
end_date = sysdate
where item_type = l_type
and item_key like l_key;
update wf_item_activity_statuses set
end_date = sysdate
where item_type = l_type
and item_key like l_key;
update wf_item_activity_statuses_h set
end_date = sysdate
where item_type = l_type
and item_key like l_key;
END Delete_Item;
lSQLString := ' select 1 '||' from ' || fromString || ' where rownum = 1 and '||capsAdvString;
lSQLString := 'select 1 from dual where rownum = 1 and '||capsAdvString;
lSQLString := 'select '||pCalculationString||fromString;
lSQLString := 'select ( '||pCalculationString||') from dual ';
log_message('before select parseStmt' );
oSqlStmt := vSelectStmt||vFromClause||vWhereStmt||vGroupByStmt||vHavingWhere||vSortStmt;
if dbms_sql.is_open(v_insert_cursor) THEN
dbms_sql.close_cursor(v_insert_cursor);
select
MSC_USER_ADV_EXPRESSIONS_S.NEXTVAL
,ex.Expression_Id
,ex.NAME
,ex.DESCRIPTION
,ex.COMPONENT_TYPE
,ex.DISPLAY_LENGTH
,ex.CALCULATION_DATATYPE
,ex.REGION_CODE
,ex.GLOBAL_FLAG
,ex.COMPANY_ID
,ex.EXPRESSION1
,ex.ATTRIBUTE1
,ex.ATTRIBUTE2
,ex.ATTRIBUTE3
,ex.ATTRIBUTE4
,ex.ATTRIBUTE5
,ex.ATTRIBUTE6
,ex.ATTRIBUTE7
,ex.ATTRIBUTE8
,ex.ATTRIBUTE9
,ex.ATTRIBUTE10
,ex.ATTRIBUTE11
,ex.ATTRIBUTE12
,ex.ATTRIBUTE13
,ex.ATTRIBUTE14
,ex.ATTRIBUTE15
,ex.CONTEXT
FROM MSC_USER_ADV_EXPRESSIONS ex,
MSC_USER_EXCEPTION_COMPONENTS comp
WHERE ex.EXPRESSION_ID = comp.EXPRESSION_ID
and comp.EXCEPTION_ID = exceptionId;
select MSC_USER_EXCEPTIONS_S.NEXTVAL
into lexceptionId from dual;
insert into MSC_USER_EXCEPTIONS(
EXCEPTION_ID
,NAME
,DESCRIPTION
,REGION_CODE
,COMPANY_ID
,SECURITY_FLAG
--,GROUP_BY_FLAG
,WF_ITEM_TYPE
,WF_PROCESS
,WF_LAUNCH_FLAG
,NOTIFICATION_TEXT
,REQUEST_ID
,START_FLAG
,RECURRENCE_FLAG
,EVENT
,START_DATE
,REPEAT_INTERVAL
,REPEAT_TYPE
,REPEAT_END_TIME
,LAST_RUN_DATE
,FULL_DATA_FLAG
,REFRESH_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
)
select
lexceptionId
,newExName
,newDescription
,REGION_CODE
,COMPANY_ID
,SECURITY_FLAG
--,GROUP_BY_FLAG
,WF_ITEM_TYPE
,WF_PROCESS
,WF_LAUNCH_FLAG
,NOTIFICATION_TEXT
,NULL
,START_FLAG
,RECURRENCE_FLAG
,EVENT
,START_DATE
,REPEAT_INTERVAL
,REPEAT_TYPE
,REPEAT_END_TIME
,NULL
,FULL_DATA_FLAG
,NULL
,sysdate
,lUserId
,sysdate
,lUserId
,null
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
from MSC_USER_EXCEPTIONS
where exception_id = exceptionId;
insert into MSC_USER_EXCEPTION_COMPONENTS(
COMPONENT_ID
,EXCEPTION_ID
,SEQ_NUM
,COMPONENT_TYPE
,AK_ATTRIBUTE_CODE
,LABEL
,EXPRESSION_ID
,COMPONENT_VALUE1
,COMPONENT_VALUE2
,DATE_FILTER_FLAG
,ROLLING_DATE_FLAG
,ROLLING_NUMBER
,ROLLING_TYPE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
)
select
MSC_USER_EXCEPTION_COMP_S.NEXTVAL
,lexceptionId
,SEQ_NUM
,COMPONENT_TYPE
,AK_ATTRIBUTE_CODE
,LABEL
,EXPRESSION_ID
,COMPONENT_VALUE1
,COMPONENT_VALUE2
,DATE_FILTER_FLAG
,ROLLING_DATE_FLAG
,ROLLING_NUMBER
,ROLLING_TYPE
,sysdate
,lUserId
,sysdate
,lUserId
,NULL
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
from MSC_USER_EXCEPTION_COMPONENTS
where EXCEPTION_ID = exceptionId ;
insert into MSC_USER_ADV_EXPRESSIONS(
EXPRESSION_ID
,NAME
,DESCRIPTION
,COMPONENT_TYPE
,DISPLAY_LENGTH
,CALCULATION_DATATYPE
,REGION_CODE
,GLOBAL_FLAG
,COMPANY_ID
,EXPRESSION1
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
) values
(
lExpressionId,
lName,lDescription,lComponentType,lDisplayLength,lCalculationDatatype,
lRegionCode,lGlobalFlag,lCompanyId,lExpression1,
sysdate ,lUserId ,sysdate ,lUserId ,null,
lAttribute1,lAttribute2,lAttribute3,lAttribute4,lAttribute5,lAttribute6,lAttribute7,
lAttribute8,lAttribute9,lAttribute10,lAttribute11,lAttribute12,lAttribute13,lAttribute14,
lAttribute15,lContext
) ;
update MSC_USER_EXCEPTION_COMPONENTS comp1
set EXPRESSION_ID = lExpressionId
where EXPRESSION_ID = oldExpressionId
and EXCEPTION_ID = lexceptionId;
insert into MSC_USER_EXCEPTION_NTFS(
NOTIFICATION_ENTRY_ID
,EXCEPTION_ID
,WF_ROLE
,WF_ROLE_TYPE
,SEND_NTF_FLAG
,SHOW_EXCEPTION_DTL_FLAG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
)
select
MSC_USER_EXCEPTION_NTFS_S.NEXTVAL
,lexceptionId
,WF_ROLE
,WF_ROLE_TYPE
,SEND_NTF_FLAG
,SHOW_EXCEPTION_DTL_FLAG
,sysdate
,lUserId
,sysdate
,lUserId
,NULL
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
from MSC_USER_EXCEPTION_NTFS
where EXCEPTION_ID = exceptionId;
insert into MSC_RELATED_EXCEPTIONS
(
RELATION_ID
,EXCEPTION_ID
,LINK_TYPE
,RELATED_EXCEPTION_ID
,URLNAME
,URL
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
)
select
MSC_RELATED_EXCEPTIONS_S.NEXTVAL
,lexceptionId
,LINK_TYPE
,RELATED_EXCEPTION_ID
,URLNAME
,URL
,sysdate
,lUserId
,sysdate
,lUserId
,NULL
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CONTEXT
from MSC_RELATED_EXCEPTIONS
where EXCEPTION_ID = lexceptionId;
Procedure deleteException(exceptionId IN NUMBER,
status OUT NOCOPY NUMBER,
returnMessage OUT NOCOPY VARCHAR2) IS
cursor detailIds is
select to_char(exception_Detail_id)
from msc_x_exception_details
where EXCEPTION_TYPE = exceptionId
and EXCEPTION_GROUP = -99;
select WF_ITEM_TYPE
from MSC_USER_EXCEPTIONS
where EXCEPTION_ID = exceptionId;
Delete_Item(lItemType,lExceptionDetailsId);
delete from msc_x_exception_details
where exception_type = exceptionId
and exception_group = -99;
/*delete from MSC_EXCEPTION_PREFERENCES
where EXCEPTION_TYPE_LOOKUP_CODE = exceptionId; */
delete from msc_item_exceptions
where exception_type = exceptionId
and exception_group = -99;
/* delete from MSC_USER_EXCEPTION_NTFS
where EXCEPTION_ID = exceptionId;
delete from MSC_RELATED_EXCEPTIONS
where EXCEPTION_ID = exceptionId;
delete from MSC_USER_ADV_EXPRESSIONS exp
where exp.expression_id in (
select ex.expression_id
from MSC_USER_EXCEPTION_COMPONENTS ex
where ex.EXCEPTION_ID = exceptionId
);
delete from MSC_USER_EXCEPTION_COMPONENTS
where EXCEPTION_ID = exceptionId;
delete from MSC_USER_EXCEPTIONS
where EXCEPTION_ID = exceptionId;
select exception_id,NAME from
msc_user_exceptions ex
where RECURRENCE_FLAG = '0';
update msc_user_exceptions
set request_id= lRequestId
where exception_id = lExceptionId;