[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_totdeleterows IN OUT NOCOPY INTEGER,
p_sizing BOOLEAN,
p_commitfrequency INTEGER,
p_disable_constraints BOOLEAN,
p_debug_flag BOOLEAN);
p_totdeleterows IN OUT NOCOPY INTEGER);
p_totdeleterows IN OUT NOCOPY INTEGER,
p_sizing BOOLEAN,
p_commitfrequency INTEGER,
p_inittime IN OUT NOCOPY DATE,
p_starttime IN OUT NOCOPY DATE,
p_disable_constraints BOOLEAN,
p_debug_flag BOOLEAN);
The main purpose of this is not to delete any transaction which have unposted rows in it. */
/* added new TEMP TABLE logic */
FUNCTION GLPOSTED_OPSO
(P_Purge_id in sy_purg_mst.purge_id%TYPE,
p_purge_type sy_purg_def.purge_type%TYPE,
p_owner user_users.username%TYPE,
p_debug_flag BOOLEAN)
RETURN LONG;
PROCEDURE Tempinsert(p_purge_id sy_purg_mst.purge_id%TYPE,
p_purge_type sy_purg_def.purge_type%TYPE,
p_all_ids number,
p_debug_flag BOOLEAN);
p_totdeleterows IN OUT NOCOPY INTEGER,
p_sizing BOOLEAN,
p_commitfrequency INTEGER,
p_disable_constraints BOOLEAN,
p_debug_flag BOOLEAN) IS
l_indexes_tab GMA_PURGE_DDL.g_statement_tab_type;
UPDATE sy_purg_mst
SET archive_table_count = (p_tablecount + 1)
, copy_elapsed_time =
trunc(((sysdate - l_copystarttime) * 86400),2)
, last_update_date = sysdate
, last_updated_by = p_user
WHERE purge_id = p_purge_id;
SELECT UC.column_name arctable
, UC.data_type drowtype
, decode(nvl(SD.archive_action,'K'),'D','D','K') arcaction
, UU.default_tablespace arctablespace
FROM all_tab_columns UC
, dba_users UU
, sy_purg_def_act SD
, sy_purg_def SP
WHERE UC.owner = c_schema_name
AND UU.USERNAME='GMA'
AND SD.purge_type = SP.purge_type
AND SD.table_name = UC.column_name
AND SP.purge_type = c_purge_type
AND UC.table_name = c_arctablename
ORDER BY UC.column_id;
SELECT UC.column_name arctable
, UC.data_type drowtype
, decode(nvl(SD.archive_action,'K'),'D','D','K') arcaction
, nvl(SD.target_tablespace,
nvl(SP.default_target_tablespace,
UU.default_tablespace
)
) arctablespace
FROM user_users UU
, sy_purg_def_act SD
, sy_purg_def SP
, all_tab_columns UC
WHERE UC.owner='GMA'
AND SD.purge_type = SP.purge_type
AND SD.table_name = UC.column_name
AND SP.purge_type = c_purge_type
AND UC.table_name = c_arctablename
ORDER BY UC.column_id;
SELECT SM.purge_type purgetype
, SD.sqlstatement arcsqlstatement
-- , NVL(SD.work_tablespace,
, UU.default_tablespace arctablespace
, SM.purge_status arcstatus
, SM.status testarcstatus
, nvl(SM.debug_flag,'F') debug_flag
, nvl(SM.disable_constraints_flag,'F') disable_constraints
, nvl(SM.calculate_storage_flag,'F') storage_flag
, nvl(SM.commit_frequency,750) commit_frequency
, nvl(SM.object_owner,'') object_owner
FROM dba_users UU
, sy_purg_mst SM
, sy_purg_def SD
WHERE SD.purge_type (+) = SM.purge_type
AND UU.username = 'GMA'
AND SM.purge_id = c_purge_id;
SELECT DC.crit_tag crit_tag
, REPLACE(NVL(DC.value_mask,'{X}'),
'{X}',
NVL(MC.crit_value,DC.default_value)) value
FROM sy_purg_mst_crit MC
, sy_purg_def_crit DC
, sy_purg_mst MS
WHERE MC.crit_tag = DC.crit_tag
AND MC.purge_id = MS.purge_id
AND DC.purge_type = MS.purge_type
AND MS.purge_id = c_purge_id;
SELECT username
FROM all_users
WHERE username = c_schema_name;
l_totdeleterows INTEGER;
'Purge owner ' || l_owner || ' can''''t be determined. (select schemaname)');
UPDATE sy_purg_mst
SET purge_status = l_status
, last_update_date = sysdate
, last_updated_by = l_user
, archive_start_time = decode(l_status,1,sysdate,archive_start_time)
, purge_start_time = decode(l_status,3,sysdate,purge_start_time)
WHERE purge_id = p_purge_id;
UPDATE sy_purg_mst
SET status = l_status
, last_update_date = sysdate
, last_updated_by = l_user
, archive_start_time = decode(l_status,1,sysdate,archive_start_time)
, purge_start_time = decode(l_status,3,sysdate,purge_start_time)
WHERE purge_id = p_purge_id;
l_totdeleterows,
l_sizing,
l_commitfrequency,
l_inittime,
l_starttime,
l_disable_constraints,
l_debug_flag);
UPDATE sy_purg_mst
-- SET rows_archived = decode(l_status,1,l_totarchiverows, rows_archived)
SET rows_deleted = decode(l_status,1,l_totdeleterows,
rows_deleted)
, archive_elapsed_time =
decode(l_status,1,l_elapsed,archive_elapsed_time)
, rows_per_second = trunc((l_totarchiverows/
decode(l_elapsed,
0,1,
l_elapsed))
,2)
, copy_rows_per_second = trunc((l_totarchiverows/
decode(copy_elapsed_time,
0,1,
copy_elapsed_time))
,2)
, last_update_date = sysdate
, last_updated_by = l_user
WHERE purge_id = p_purge_id;
UPDATE sy_purg_mst
SET rows_archived = decode(l_status,1,l_totarchiverows,
rows_archived)
-- , rows_deleted = decode(l_status,1,l_totdeleterows,
-- rows_deleted)
, archive_elapsed_time =
decode(l_status,1,l_elapsed,archive_elapsed_time)
, rows_per_second = trunc((l_totarchiverows/
decode(l_elapsed,
0,1,
l_elapsed))
,2)
, copy_rows_per_second = trunc((l_totarchiverows/
decode(copy_elapsed_time,
0,1,
copy_elapsed_time))
,2)
, last_update_date = sysdate
, last_updated_by = l_user
WHERE purge_id = p_purge_id;
UPDATE sy_purg_mst
SET purge_elapsed_time =
decode(l_status,3,l_elapsed,purge_elapsed_time)
, last_update_date = sysdate
, last_updated_by = l_user
WHERE purge_id = p_purge_id;
UPDATE sy_purg_mst
SET purge_status = l_status
, archive_start_time=to_date(pa_initiate_time,'DD-MM-YYYY HH24:MI:SS')
, last_update_date = sysdate
, last_updated_by = l_user
WHERE purge_id = p_purge_id;
UPDATE sy_purg_mst
SET status = l_status
, archive_start_time=to_date(pa_initiate_time,'DD-MM-YYYY HH24:MI:SS')
, last_update_date = sysdate
, last_updated_by = l_user
WHERE purge_id = p_purge_id;
UPDATE sy_purg_mst
SET purge_status = 0
, last_update_date = sysdate
, last_updated_by = l_user
, archive_start_time = decode(l_status,1,sysdate,archive_start_time)
, purge_start_time = decode(l_status,3,sysdate,purge_start_time)
WHERE purge_id = p_purge_id;
UPDATE sy_purg_mst
SET status = 0
, last_update_date = sysdate
, last_updated_by = l_user
, archive_start_time = decode(l_status,1,sysdate,archive_start_time)
, purge_start_time = decode(l_status,3,sysdate,purge_start_time)
WHERE purge_id = p_purge_id;
UPDATE sy_purg_mst SY
SET SY.purge_status = (SY.purge_status - (SY.purge_status * 2))
, SY.ora_status = l_orastatus
WHERE SY.purge_id = p_purge_id;
UPDATE sy_purg_mst SY
SET SY.status = (SY.status - (SY.status * 2))
, SY.ora_status = l_orastatus
WHERE SY.purge_id = p_purge_id;
p_totdeleterows IN OUT NOCOPY INTEGER) IS
-- distill results into log format
l_result INTEGER;
l_deleterows INTEGER; -- number of rows deleted from this table
l_deleterows := 0;
l_sqlstatement := 'SELECT COUNT(*) FROM ' ||Get_GmaSchemaName||'.'||
GMA_PURGE_UTILITIES.makearcname(p_purge_id,
p_arctables_tab(l_tableno));
l_deleterows := l_archiverows;
l_deleterows := 0;
INSERT INTO sy_purg_log
( purge_id
, table_name
, rows_archived
, rows_deleted
, creation_date
, created_by
, last_update_login
, last_update_date
, last_updated_by)
VALUES
( p_purge_id
, p_arctables_tab(l_tableno)
, l_archiverows
, l_deleterows
, sysdate
, p_user
,1
,sysdate
,1);
p_totdeleterows := p_totdeleterows + l_deleterows;
p_totdeleterows IN OUT NOCOPY INTEGER,
p_sizing BOOLEAN,
p_commitfrequency INTEGER,
p_inittime IN OUT NOCOPY DATE,
p_starttime IN OUT NOCOPY DATE,
p_disable_constraints BOOLEAN,
p_debug_flag BOOLEAN) IS
l_arcrowtable user_tables.table_name%TYPE;
p_totdeleterows := 0;
UPDATE sy_purg_mst
SET selection_elapsed_time =
trunc((p_inittime - p_starttime) * 86400)
, last_update_date = sysdate
, last_updated_by = p_user
WHERE purge_id = p_purge_id;
p_totdeleterows,
p_sizing,
p_commitfrequency,
p_disable_constraints,
p_debug_flag);
GMA_PURGE_ENGINE.PA_OPTION_NAME||' selection completed in ' ||
to_char(trunc((SYSDATE - p_inittime) * 86400))
|| ' seconds.');
p_totdeleterows);
SELECT table_name
FROM sy_purg_def_act
WHERE purge_type = c_purge_type;
select crit_tag,crit_value
from sy_purg_mst_crit
where purge_id=P_Purge_id;
P_MINMDATE op_ordr_hdr.last_update_date%type,
P_MAXMDATE op_ordr_hdr.last_update_date%type,
P_MINCDATE op_ordr_hdr.creation_date%type,
P_MAXCDATE op_ordr_hdr.creation_date%type,
P_OSTATUS op_ordr_hdr.order_status%type)
is
select distinct order_id
FROM op_ordr_hdr OH1
WHERE order_no >= P_MINORDER
AND order_no <= P_MAXORDER
AND orgn_code >= P_MINORGN
AND orgn_code <= P_MAXORGN
AND last_update_date >= P_MINMDATE
AND last_update_date <= P_MAXMDATE
AND creation_date >= P_MINCDATE
AND creation_date <= P_MAXCDATE
AND order_status = P_OSTATUS
AND (order_status = -1 or order_status = 25) ;
select count(*) COUNT_GL_POSTED_IND, 0 COUNT_COMPLETED_IND
from ic_tran_pnd
where doc_id =pdoc_id
and doc_type = 'OPSO'
and delete_mark=0
and gl_posted_ind <>1
UNION ALL
select 0,count(*)
from ic_tran_pnd
where doc_id = pdoc_id
and doc_type = 'OPSO'
and delete_mark=0
and completed_ind =0;
MINMDATE op_ordr_hdr.last_update_date%type;
MAXMDATE op_ordr_hdr.last_update_date%type;
GMA_PURGE_ENGINE.Tempinsert(P_purge_id,
p_purge_type,
c1Rec.order_id,
p_debug_flag);
select crit_tag,crit_value
from sy_purg_mst_crit
where purge_id=P_Purge_id;
P_MINMDATE ic_jrnl_mst.last_update_date%type,
P_MAXMDATE ic_jrnl_mst.last_update_date%type,
P_MINCDATE ic_jrnl_mst.creation_date%type,
P_MAXCDATE ic_jrnl_mst.creation_date%type)
is
select distinct IA1.doc_id
FROM ic_adjs_jnl IA1,
ic_jrnl_mst IJ1
WHERE IA1.journal_id = IJ1.journal_id
AND IJ1.posted_ind = 1
AND IA1.completed_ind = 1
AND IJ1.journal_no >= P_MINJRNL
AND IJ1.journal_no <= P_MAXJRNL
AND IJ1.orgn_code >= P_MINORGN
AND IJ1.orgn_code <= P_MAXORGN
AND IJ1.last_update_date >= P_MINMDATE
AND IJ1.last_update_date <= P_MAXMDATE
AND IJ1.creation_date >= P_MINCDATE
AND IJ1.creation_date <= P_MAXCDATE;
select count(*)
from ic_tran_cmp
where doc_id = pdoc_id
and doc_type in ('CREI','CRER','ADJI','ADJR','TRNI','TRNR')
--excludes ('GRDI','GRDR','STSI','STSR') types per bug 2441842
-- Bug #2602036 (JKB) Removed 'upper' and 'not in' above.
and gl_posted_ind <> 1;
MINMDATE ic_jrnl_mst.last_update_date%type;
MAXMDATE ic_jrnl_mst.last_update_date%type;
GMA_PURGE_ENGINE.Tempinsert(P_purge_id,
p_purge_type,
c1Rec.doc_id,
p_debug_flag);
select crit_tag,crit_value
from sy_purg_mst_crit
where purge_id=P_Purge_id;
P_MINMDATE gme_batch_header.last_update_date%type,
P_MAXMDATE gme_batch_header.last_update_date%type,
P_MINCDATE gme_batch_header.creation_date%type,
P_MAXCDATE gme_batch_header.creation_date%type,
P_PSTATUS gme_batch_header.batch_status%type)
is
select distinct BH2.batch_id
FROM gme_batch_header BH2
WHERE BH2.batch_no >= P_MINBATCH
AND BH2.batch_no <= P_MAXBATCH
AND BH2.plant_code >= P_MINPLANT
AND BH2.plant_code <= P_MAXPLANT
AND BH2.last_update_date >= P_MINMDATE
AND BH2.last_update_date <= P_MAXMDATE
AND BH2.creation_date >= P_MINCDATE
AND BH2.creation_date <= P_MAXCDATE
AND BH2.batch_status = P_PSTATUS
AND (BH2.batch_status = -1 or BH2.batch_status = -3 or BH2.batch_status = 4);
SELECT batch_id,parentline_id
FROM gme_batch_header
WHERE batch_id=P_batch_id;
select batch_id,batch_status,gl_posted_ind,update_inventory_ind
FROM GME_BATCH_HEADER
WHERE batch_id IN (SELECT DISTINCT batch_id
FROM gme_material_details
START WITH batch_id=P_batch_id
CONNECT BY batch_id = PRIOR phantom_id);
MINMDATE gme_batch_header.last_update_date%type;
MAXMDATE gme_batch_header.last_update_date%type;
if(RecPhantom.gl_posted_ind<>1 and RecPhantom.update_inventory_ind='Y') then
phantom_unposted_flag:=TRUE;
GMA_PURGE_ENGINE.Tempinsert(P_purge_id,
p_purge_type,
all_phantom_batch_id(i),
p_debug_flag);
icnt||' rows inserted in '||l_temptable||' table.');
select crit_tag,crit_value
from sy_purg_mst_crit
where purge_id=P_Purge_id;
P_MINMDATE po_ordr_hdr.last_update_date%type,
P_MAXMDATE po_ordr_hdr.last_update_date%type,
P_MINCDATE po_ordr_hdr.creation_date%type,
P_MAXCDATE po_ordr_hdr.creation_date%type)
is
select distinct PH2.po_id
FROM po_ordr_hdr PH2
WHERE PH2.po_no >= P_MINPO
AND PH2.po_no <= P_MAXPO
AND PH2.orgn_code >= P_MINORGN
AND PH2.orgn_code <= P_MAXORGN
AND PH2.last_update_date >= P_MINMDATE
AND PH2.last_update_date <= P_MAXMDATE
AND PH2.creation_date >= P_MINCDATE
AND PH2.creation_date <= P_MAXCDATE
AND (PH2.po_status = 20);
DELETE_MARK Standard: 0=Active record (default); 1=Marked for (logical) deletion.
select count(*)
from po_recv_hst
where po_id = ppo_id
and RECV_LINE_ID not in (SELECT RECV_LINE_ID from po_recv_hst where po_id=ppo_id and delete_mark=1)
and gl_posted_ind <> 1;
MINMDATE po_ordr_hdr.last_update_date%type;
MAXMDATE po_ordr_hdr.last_update_date%type;
GMA_PURGE_ENGINE.Tempinsert(P_purge_id,
p_purge_type,
c1Rec.po_id,
p_debug_flag);
l_sqlstatement := 'INSERT INTO '||p_owner ||'.'||l_temptable|| ' values(:V_bindfix)';
PROCEDURE Tempinsert(p_purge_id sy_purg_mst.purge_id%TYPE,
p_purge_type sy_purg_def.purge_type%TYPE,
p_all_ids number,
p_debug_flag BOOLEAN)
IS
-- create master rows table for archive
l_result INTEGER;
l_sqlstatement := 'INSERT INTO ' ||Get_GmaSchemaName||'.' ||
l_temptable|| ' values(:all_ids)';
'Problem raised in GMA_PURGE_ENGINE.tempinsert.');
'Problem raised in GMA_PURGE_ENGINE.tempinsert.');
END tempinsert;
SELECT owner,table_name
FROM all_tables
WHERE owner = c_schema_name
AND
table_name IN(
SELECT 'T' ||LPAD(TO_CHAR(A.purge_id),5,'0')||'_'||B.table_name
FROM SY_PURG_MST A, Sy_purg_def_act B
WHERE A.purge_type=B.purge_type AND A.purge_id=ppurge_id
union
SELECT 'T'||LPAD(TO_CHAR(A.purge_id),5,'0')||'_'||'ARCHIVEROWS'
FROM SY_PURG_MST A
WHERE A.purge_id=ppurge_id
union
SELECT 'T'||LPAD(TO_CHAR(A.purge_id),5,'0')||'_'||'TEMP'
FROM SY_PURG_MST A
WHERE A.purge_id=ppurge_id
);
Update sy_purg_mst set status=0 where purge_id=P_purge_id;