The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_org_selection number;
select msc_designators_s.nextval
into v_designator_id
from dual;
select decode(p_dest_plan_type, 1, 3,
2, 2,
3, 4,
4, 5,
5,8,
6,9,
7,10,
8,11,
9,12,
101,101, -- added for RP
102,102,
103,103,
104,104,
105,105)
into v_designator_type
from dual;
select nvl(organization_selection,3),designator_id
into l_org_selection , v_prev_designator_id
from msc_designators
where designator = (select compile_designator
from msc_plans
where plan_id = p_source_plan_id)
and organization_id = p_organization_id
and sr_instance_id = p_sr_instance_id;
v_col_value(1).P_REFRESH_NUMBER := l_org_selection ;
v_col_value(1).P_ORGANIZATION_SELECTION := 2 ;
v_col_value(1).P_LAST_UPDATE_DATE := sysdate ;
v_col_value(1).P_LAST_UPDATED_BY := fnd_global.user_id ;
v_col_value(1).P_LAST_UPDATE_LOGIN := fnd_global.login_id ;
,'UPDATED'
,'SUBST_ITEM_FLAG'
,'STATUS'
,'APPLIED'
,'EXPIRATION_QUANTITY'
,'EXPIRATION_DATE'
,'NON_NETTABLE_QTY'
,'IMPLEMENT_WIP_START_DATE'
,'REFRESH_NUMBER'
,'REQUEST_ID'
,'PROGRAM_APPLICATION_ID'
,'PROGRAM_ID'
,'PROGRAM_UPDATE_DATE'
,'IMPLEMENT_DAILY_RATE'
,'NEED_BY_DATE'
,'SOURCE_SUPPLY_ID'
,'SR_MTL_SUPPLY_ID'
,'WIP_STATUS_CODE'
,'DEMAND_CLASS'
,'FROM_ORGANIZATION_ID'
,'WIP_SUPPLY_TYPE'
,'PO_LINE_ID'
,'LOAD_FACTOR_RATE'
,'ROUTING_SEQUENCE_ID'
,'BILL_SEQUENCE_ID'
,'COPRODUCTS_SUPPLY'
,'CFM_ROUTING_FLAG'
,'CUSTOMER_ID'
,'SHIP_TO_SITE_ID'
,'OLD_NEED_BY_DATE'
,'OLD_DAILY_RATE'
,'OLD_FIRST_UNIT_START_DATE'
,'OLD_LAST_UNIT_COMPLETION_DATE'
,'OLD_NEW_SCHEDULE_DATE'
,'OLD_QTY_COMPLETED'
,'OLD_NEW_ORDER_QUANTITY'
,'OLD_FIRM_QUANTITY'
,'OLD_FIRM_DATE'
,'PLANNING_PARTNER_SITE_ID'
,'PLANNING_TP_TYPE'
,'OWNING_PARTNER_SITE_ID'
,'OWNING_TP_TYPE'
,'VMI_FLAG'
,'EARLIEST_START_DATE'
,'EARLIEST_COMPLETION_DATE'
,'MIN_START_DATE'
,'SCHEDULED_DEMAND_ID'
,'EXPLOSION_DATE'
,'SCO_SUPPLY_DATE'
,'RECORD_SOURCE'
,'SUPPLY_IS_SHARED'
,'ULPSD'
,'ULPCD'
,'UEPSD'
,'UEPCD'
,'EACD'
,'ORIGINAL_NEED_BY_DATE'
,'ORIGINAL_QUANTITY'
,'ACCEPTANCE_REQUIRED_FLAG'
);
,'UPDATED'
,'SUBST_RES_FLAG'
,'REFRESH_NUMBER'
,'REQUEST_ID'
,'PROGRAM_APPLICATION_ID'
,'PROGRAM_ID'
,'PROGRAM_UPDATE_DATE'
,'SOURCE_ITEM_ID'
,'ASSEMBLY_ITEM_ID'
,'SUPPLY_TYPE'
,'FIRM_START_DATE'
,'FIRM_END_DATE'
,'FIRM_FLAG'
,'CUMMULATIVE_QUANTITY'
,'YIELD'
,'REVERSE_CUMULATIVE_YIELD'
,'BATCH_NUMBER'
,'MINIMUM_TRANSFER_QUANTITY'
,'REMAINING_CAPACITY'
,'OVERLOADED_CAPACITY'
,'EARLIEST_START_DATE'
,'EARLIEST_COMPLETION_DATE'
,'SCHEDULED_DEMAND_ID'
,'ULPSD'
,'ULPCD'
,'UEPSD'
,'UEPCD'
,'EACD'
,'PARENT_SEQ_NUM');
v_select_cols varchar2(8000);
v_insert_cols varchar2(8000);
v_insert_who varchar2(4000):= ',last_update_date,' ||
'last_updated_by,' ||
'creation_date,' ||
'created_by,' ||
'last_update_login ';
v_select_who varchar2(4000):= ',sysdate,' ||
'fnd_global.user_id,' ||
'sysdate,' ||
'fnd_global.user_id,' ||
'fnd_global.user_id ';
v_select_cols:= null;
if v_select_cols is null then
v_select_cols := v_table_name || '.'
||v_supp_col_list(i);
v_insert_cols := v_supp_col_list(i);
v_select_cols := v_select_cols||',' ||
v_table_name || '.' ||v_supp_col_list(i);
v_insert_cols := v_insert_cols||',' ||
v_supp_col_list(i);
v_supp_col_list.delete;
if v_select_cols is null then
v_select_cols := v_table_name || '.'
||v_res_col_list(i);
v_insert_cols := v_res_col_list(i);
v_select_cols := v_select_cols||',' ||
v_table_name || '.' ||v_res_col_list(i);
v_insert_cols := v_insert_cols||',' ||
v_res_col_list(i);
v_res_col_list.delete;
v_statement := 'INSERT into ' ||v_table_name ||'(plan_id, '||
v_insert_cols || v_insert_who || ')'
|| ' SELECT :Dest_Plan_Id, '||
v_select_cols || v_select_who
|| v_from_where;
PROCEDURE delete_temp_plan( errbuf out NOCOPY varchar2,
retcode out NOCOPY number,
P_desig_id IN number,
p_childreq IN boolean default false) is
v_request_id number;
End delete_temp_plan;
PROCEDURE delete_plan_options( errbuf out NOCOPY varchar2,
retcode out NOCOPY number,
P_plan_id IN number) is
v_statement varchar2(1000);
delete msc_designators
where (designator, organization_id, sr_instance_id) in (select
compile_designator, organization_id, sr_instance_id
from msc_plans
where plan_id=P_PLAN_ID);
v_statement := 'delete from ' || v_tab_name || ' where plan_id=' ||
':p_plan_id';
End Delete_plan_options;
update MSC_PLANS
set copy_plan_id = p_src_Plan_id
where plan_id = p_plan_id;
update MSC_DESIGNATORS
set copy_Designator_id = p_src_Desg_id
Where designator_id = p_designator_id;
p_source_table.delete;
select decode(p_column_name ,'DESIGNATOR_ID' ,
nvl(to_char(p_bind_var_col(1).P_DESIGNATOR_ID ) , 'null')
,'DESIGNATOR' ,
''''||p_bind_var_col(1).P_DEST_PLAN_NAME||''''
,'DESIGNATOR_TYPE' , nvl(to_char(
p_bind_var_col(1).P_DESIGNATOR_TYPE),'null')
,'ORGANIZATION_ID' ,
nvl(to_char(p_bind_var_col(1).P_ORGANIZATION_ID) , 'null')
,'MPS_RELIEF' ,
nvl(to_char(p_bind_var_col(1).P_MPS_RELIEF),'null')
,'INVENTORY_ATP_FLAG' ,
nvl(to_char(p_bind_var_col(1).P_INVENTORY_ATP_FLAG),'null')
,'PRODUCTION' ,
nvl(to_char(p_bind_var_col(1).P_PRODUCTION), 'null')
,'LAUNCH_WORKFLOW_FLAG' ,
nvl(to_char(p_bind_var_col(1).P_LAUNCH_WORKFLOW_FLAG) ,'null')
,'DESCRIPTION' , ':DESCRIPTION'
--''''||p_bind_var_col(1).P_DESCRIPTION||''''
,'DISABLE_DATE' ,
''''||decode(p_bind_var_col(1).P_DISABLE_DATE , null ,
p_bind_var_col(1).P_DISABLE_DATE
,to_char(p_bind_var_col(1).P_DISABLE_DATE
, 'DD-MON-RRRR'))||''''
,'SR_INSTANCE_ID' ,
nvl(to_char(p_bind_var_col(1).P_SR_INSTANCE_ID),'null')
,'REFRESH_NUMBER' , nvl(to_char(
p_bind_var_col(1).P_REFRESH_NUMBER) , 'null')
,'ORGANIZATION_SELECTION' , nvl(to_char(
p_bind_var_col(1).P_ORGANIZATION_SELECTION) , 'null')
,'LAST_UPDATE_DATE'
,''''||to_char(p_bind_var_col(1).P_LAST_UPDATE_DATE , 'DD-MON-RRRR')||''''
,'LAST_UPDATED_BY' , nvl(to_char(
p_bind_var_col(1).P_LAST_UPDATED_BY) ,'null')
,'CREATION_DATE' ,
''''||to_char(p_bind_var_col(1).P_CREATION_DATE , 'DD-MON-RRRR')||''''
,'CREATED_BY' ,
nvl(to_char(p_bind_var_col(1).P_CREATED_BY),'null')
, p_column_name /* 'null' */ ) into l_temp
from dual;
select decode(p_column_name ,'LAST_UPDATE_DATE' ,
''''||to_char(p_bind_var_col(1).P_LAST_UPDATE_DATE , 'DD-MON-RRRR')||''''
,'LAST_UPDATED_BY' , nvl(to_char(
p_bind_var_col(1).P_LAST_UPDATED_BY) ,'null')
,'CREATION_DATE' ,
''''||to_char(p_bind_var_col(1).P_CREATION_DATE , 'DD-MON-RRRR')||''''
,'CREATED_BY' ,
nvl(to_char(p_bind_var_col(1).P_CREATED_BY),'null')
,'LAST_UPDATE_LOGIN' ,nvl(to_char(
p_bind_var_col(1).P_LAST_UPDATE_LOGIN),'null')
,'COPY_PLAN' , nvl(to_char(
p_bind_var_col(1).P_COPY_PLAN ) ,'null')
,'PLAN_ID' ,
nvl(to_char(p_bind_var_col(1).P_DEST_PLAN_ID ) ,'null')
,'COMPILE_DESIGNATOR'
,''''||p_bind_var_col(1).P_DEST_PLAN_NAME||''''
,'CURR_PLAN_TYPE' , nvl(to_char(
p_bind_var_col(1).P_DEST_PLAN_TYPE) , 'null')
,'PLAN_TYPE' ,
nvl(to_char(p_bind_var_col(1).P_DEST_PLAN_TYPE),'null')
,'DESCRIPTION' , ':DESCRIPTION'
--''''||p_bind_var_col(1).P_DEST_PLAN_DESC||''''
,'PLAN_COMPLETION_DATE' , 'null'
,'PLAN_START_DATE' , 'null'
,'DATA_COMPLETION_DATE' , 'null'
,'PLAN_RUN_DATE' , 'null'
,'ORGANIZATION_SELECTION' ,
nvl(to_char(p_bind_var_col(1).P_REFRESH_NUMBER),'null')
, p_column_name) into l_temp from dual;
select decode(p_column_name ,'LAST_UPDATE_DATE'
,''''||to_char(p_bind_var_col(1).P_LAST_UPDATE_DATE , 'DD-MON-RRRR')||''''
,'LAST_UPDATED_BY' ,
nvl(to_char(p_bind_var_col(1).P_LAST_UPDATED_BY),'null')
,'CREATION_DATE' ,''''||to_char(
p_bind_var_col(1).P_CREATION_DATE , 'DD-MON-RRRR')||''''
,'CREATED_BY' ,
nvl(to_char(p_bind_var_col(1).P_CREATED_BY),'null')
,'LAST_UPDATE_LOGIN' ,
nvl(to_char(p_bind_var_col(1).P_LAST_UPDATE_LOGIN),'null')
,'PLAN_ID' ,
nvl(to_char(p_bind_var_col(1).P_DEST_PLAN_ID),'null')
, p_column_name) into l_temp from dual;
select column_name , data_type , data_length
from all_tab_cols
where table_name = l_table_name
and owner = l_owner
and (VIRTUAL_COLUMN = 'NO'
AND HIDDEN_COLUMN='NO'
and column_name not like '%#%');
v_statement := 'INSERT INTO '||p_table_name||' (DESIGNATOR ,DESCRIPTION ' ;
v_statement1 := ' SELECT :DESIGNATOR , :DESCRIPTION ' ;
v_statement := 'INSERT INTO '||p_table_name||' (COMPILE_DESIGNATOR
,DESCRIPTION ' ;
v_statement1 := ' SELECT :COMPILE_DESIGNATOR , :DESCRIPTION ' ;
v_statement := 'INSERT INTO '||p_table_name||' ('
||var.column_name ;
v_statement1 := ' SELECT ' ||Get_Column_Value(p_bind_var_col
,var.column_name , var.data_type , p_table_name );