[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* eff_date,op_seq,delete_type.
* Parameters OUT: err_text
* return : 0 -success , other - SQL Exception
* Purpose : This function will write to conc-log
******************************************************************/
FUNCTION write_log(alt_desg IN VARCHAR2,
org_name IN VARCHAR2,
item_name IN VARCHAR2,
comp_name IN VARCHAR2,
eff_date IN VARCHAR2,
op_seq IN NUMBER,
delete_type IN NUMBER,
err_text OUT NOCOPY VARCHAR2) RETURN NUMBER;
* Parameters IN : inventory_item_id,organization_id,delete_entity_type
* Parameters OUT: err_text
* return : 0 -success ,2-error, other - SQL Exception
* Purpose : Configuration Item Purge - Consolidate Item
******************************************************************/
FUNCTION config_item_consolidate( p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_delete_entity_type IN NUMBER,
err_text OUT NOCOPY VARCHAR2) return NUMBER;
* Parameters IN :token_list-TOKEN_RECORD, delete_seq_id, delete_entity_type
* Parameters OUT: err_text
* return : 0 -success ,2-error, other - SQL Exception
* Purpose : This function checks all the constraints from
* bom_delete_sql_statements table that are valid for given
* delete entity type
******************************************************************/
FUNCTION constraint_checker( token_list IN TOKEN_RECORD,
delete_seq_id IN NUMBER,
delete_entity_type IN NUMBER,
err_text OUT NOCOPY varchar2) return NUMBER;
insert_table IN VARCHAR2,
table_name IN VARCHAR2,
where_clause IN VARCHAR2,
bind_list IN BIND_TABLE,
err_text OUT NOCOPY VARCHAR2 ) return NUMBER ;
* FUNCTION : update_op_sequences
* Parameters IN :tdelete_entity_type ,routing_seq_id
* Parameters OUT: err_text
* return : 0 -success , other - SQL Exception
* Purpose : updates op sequences to 1 in BOM_INVENTORY_COMPONENTS
******************************************************************/
FUNCTION update_op_sequences(delete_entity_type IN NUMBER,
routing_seq_id IN NUMBER,
op_seq_id IN NUMBER,
err_text OUT NOCOPY VARCHAR2 ) return NUMBER;
* FUNCTION : execute_delete
* Parameters IN :token_list-TOKEN_RECORD, delete_entity_type
* archive_flag
* Parameters OUT: err_text, action_status(4-delete,3-error)
* return : 0 -success , other - SQL Exception
* Purpose : This function executes the delete statements from
* bom_delete_sql_statements table that are valid for given
* delete entity type
******************************************************************/
FUNCTION execute_delete(delete_entity_type IN NUMBER,
token_list IN Token_Record,
archive_flag IN NUMBER,
action_status OUT NOCOPY NUMBER,
err_text OUT NOCOPY VARCHAR2) return NUMBER;
* FUNCTION : execute_update
* Parameters IN : delete_entity_type Type of delete entity
* token_list Records of tokens to be substituted
* Parameters OUT: action_status 4-delete, 3-error
* err_text Error message in case of exception
* return : 0 -success , other - SQL Exception
* Purpose : This function executes the update statements from
* bom_delete_sql_statements table that are valid for given
* delete entity type
******************************************************************/
FUNCTION execute_update (
delete_entity_type IN NUMBER,
token_list IN Token_Record,
action_status OUT NOCOPY NUMBER,
err_text OUT NOCOPY VARCHAR2) RETURN NUMBER;
* FUNCTION : do_delete
* Parameters IN :delete group id ,delete type
* action_type (check or delete),archive_flag
* Parameters OUT: err_text
* return : 0 -success ,2-error, other - SQL Exception
* Purpose : This function checks all the constraints,
* arvhives the data based on the archive_flag option
* deletes the data based on action_type.
******************************************************************/
FUNCTION do_delete(group_id IN NUMBER,
delete_type IN NUMBER,
action_type IN NUMBER,
archive_flag IN NUMBER,
err_text OUT NOCOPY VARCHAR2,
process_errored_rows IN VARCHAR2) return NUMBER;
ACT_DELETE CONSTANT NUMBER := 2;
* Procedure : delete groups
* Parameters IN :
* delete_group_id,action_type,delete_type,archive
* Parameters OUT: ERRBUF, RETCODE
* Purpose : Main procedure for checking and deleting a delete group
******************************************************************/
PROCEDURE delete_groups
(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
delete_group_id IN NUMBER:= '0',
action_type IN NUMBER:= '1',
delete_type IN NUMBER:= '1',
archive IN NUMBER:='1',
process_errored_rows IN VARCHAR2
) is
CONC_FAILURE EXCEPTION;
CURSOR delete_errors ( c_delete_group_id NUMBER )
IS
SELECT 1
FROM
BOM_DELETE_ENTITIES bdent,
BOM_DELETE_SUB_ENTITIES bdsubent
WHERE
bdent.DELETE_ENTITY_SEQUENCE_ID = bdsubent.DELETE_ENTITY_SEQUENCE_ID(+)
AND ( ( bdent.DELETE_STATUS_TYPE = 3 ) OR ( bdsubent.DELETE_STATUS_TYPE = 3 ) )
AND bdent.DELETE_GROUP_SEQUENCE_ID = c_delete_group_id;
Added a call to Package BOM_DELETE_ENTITY.insert_common_bills
This inserts the common Bill entities for the current org, or all Orgs
or Org Hierarchy depending on the option chosen on the Delete Groups
Form
*/
bom_delete_entity.insert_common_bills(delete_group_id,delete_type);
if (do_delete(delete_group_id, delete_type, action_type,
archive, ERRBUF, process_errored_rows) <> 0) then
/*
** if delete returned failure, write error message to log file
** rollback and return CONC_FAILURE
*/
raise CONC_FAILURE;
Added a call to Package BOM_DELETE_ENTITY.insert_original_bills
This inserts original Bill entities for the all Orgs
or Org Hierarchy depending on the option chosen on the Delete Groups Form
*/
bom_delete_entity.insert_original_bills(delete_group_id,delete_type);
if (do_delete(delete_group_id, delete_type, action_type,
archive , ERRBUF, process_errored_rows) <> 0) then
/*
** if delete returned failure, write error message to log file
** rollback and return CONC_FAILURE
*/
raise CONC_FAILURE;
FOR l_del_errors_rec IN delete_errors(delete_group_id)
LOOP
RETCODE := '1';
ERRBUF := ERRBUF||'Bom_Delete_Groups_Api '||stmt_num||' '||substrb(SQLERRM,1,500);
* FUNCTION : do_delete
* Parameters IN :delete group id ,delete type
* action_type (check or delete),archive_flag
* Parameters OUT: err_text
* return : 0 -success ,2-error, other - SQL Exception
* Purpose : This function checks all the constraints,
* arvhives the data based on the archive_flag option
* deletes the data based on action_type.
******************************************************************/
FUNCTION do_delete(group_id IN NUMBER,
delete_type IN NUMBER,
action_type IN NUMBER,
archive_flag IN NUMBER,
err_text OUT NOCOPY varchar2,
process_errored_rows IN VARCHAR2) return NUMBER is
CURSOR entity_cursor (p_group_id NUMBER )is
SELECT /*+ ORDERED */ BDE.INVENTORY_ITEM_ID inventory_item_id,
BDE.ORGANIZATION_ID organization_id,
ALTERNATE_DESIGNATOR alternate_designator,
DELETE_ENTITY_TYPE delete_entity_type,
nvl(BILL_SEQUENCE_ID, -1) bill_seq_id,
nvl(ROUTING_SEQUENCE_ID, -1) routing_seq_id,
-1 component_seq_id,
-1 operation_seq_id,
DELETE_ENTITY_SEQUENCE_ID delete_entity_seq_id,
MP.ORGANIZATION_CODE organization_code,
BDE.ITEM_CONCAT_SEGMENTS item_name, --bug:6193035 Removed substrb
BDE.ITEM_DESCRIPTION description
FROM BOM_DELETE_ENTITIES BDE, MTL_PARAMETERS MP
WHERE DELETE_GROUP_SEQUENCE_ID = p_group_id
AND DELETE_STATUS_TYPE in (1,2, decode(process_errored_rows, 'Y', 3, 1))
AND BDE.ORGANIZATION_ID = MP.ORGANIZATION_ID
ORDER BY decode(MP.MASTER_ORGANIZATION_ID,
BDE.ORGANIZATION_ID, 2, 1),
decode(BDE.DELETE_ENTITY_TYPE,2,9999,BDE.DELETE_ENTITY_TYPE) DESC,
BDE.ALTERNATE_DESIGNATOR
FOR UPDATE OF ALTERNATE_DESIGNATOR;
SELECT /*+ ORDERED */ A.INVENTORY_ITEM_ID inventory_item_id,
A.ORGANIZATION_ID organization_id,
A.ALTERNATE_DESIGNATOR alternate_designator,
nvl(BILL_SEQUENCE_ID, -1) bill_seq_id,
nvl(ROUTING_SEQUENCE_ID, -1) routing_seq_id,
nvl(B.COMPONENT_SEQUENCE_ID, -1) component_seq_id,
nvl(B.OPERATION_SEQUENCE_ID, -1) operation_seq_id,
B.DELETE_ENTITY_SEQUENCE_ID delete_entity_seq_id,
B.component_item_id component_item_id,
MP.ORGANIZATION_CODE organization_code,
B.OPERATION_SEQ_NUM op_seq_num,
to_char(B.EFFECTIVITY_DATE, 'YYYY/MM/DD HH24:MI') effectivity_date,
A.ITEM_CONCAT_SEGMENTS item_name,
B.COMPONENT_CONCAT_SEGMENTS comp_name,
B.description description
FROM BOM_DELETE_ENTITIES A, BOM_DELETE_SUB_ENTITIES B,
MTL_PARAMETERS MP
WHERE A.DELETE_GROUP_SEQUENCE_ID = p_group_id
AND B.DELETE_STATUS_TYPE in (1,2,3)
AND A.DELETE_ENTITY_SEQUENCE_ID =
B.DELETE_ENTITY_SEQUENCE_ID
AND MP.ORGANIZATION_ID = A.ORGANIZATION_ID
FOR UPDATE OF B.OPERATION_SEQUENCE_ID;
delete_success NUMBER := 0;
** retrieve all rows from DELETE_ENTITIES or DELETE_SUB_ENTITIES table
** depending on delete
*/
if (delete_type =1 OR delete_type=2 OR delete_type=3 OR delete_type=6 OR delete_type=7 ) THEN
cursor_type := ENTITY;
current_seq_id := entity_record.delete_entity_seq_id;
buff => 'delete_entity_Seq_id = '|| to_char(current_seq_id));
curr_del_entity_type:= entity_record.delete_entity_type;
delete all errors if any for this entity row since we are going to
rerun the delete on it
*/
IF (curr_del_entity_type=1 OR curr_del_entity_type=2 OR curr_del_entity_type=3 )THEN
DELETE FROM BOM_DELETE_ERRORS
WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id;
** perform delete here if action_type is delete
*/
if (action_type = ACT_DELETE) then
stmt_num := 7;
if (execute_delete(entity_record.delete_entity_type,
token_list, archive_flag, action_status,
err_text ) <> 0 ) then
ROLLBACK TO SAVEPOINT start_process;
delete_success := 1;
** write to log file for every lntity deleted
*/
if ( write_log(
entity_record.alternate_designator,
entity_record.organization_code,
entity_record.item_name,
null,
null,
null,
entity_record.delete_entity_type,
err_text )<>0) then
return 2;
entity_record.delete_entity_type,
err_text) <> 0) then
return 2;
entity_record.delete_entity_type,
err_text) <>0) then
return 2;
UPDATE BOM_DELETE_ENTITIES
SET DELETE_STATUS_TYPE = action_status,
DELETE_DATE = decode(action_status, 4,
sysdate, NULL),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = user_id,
REQUEST_ID = req_id,
PROGRAM_UPDATE_DATE = SYSDATE,
PROGRAM_ID = prog_id,
PROGRAM_APPLICATION_ID = prog_appl_id
WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id;
current_seq_id := sub_entity_record.DELETE_ENTITY_SEQ_ID;
buff => 'delete_entity_Seq_id = '|| to_char(current_seq_id));
curr_del_entity_type:= delete_type;
** delete all errors if any for this entity row since we are going to
** rerun the delete on it
*/
if (curr_del_entity_type=4 ) then
curr_comp_seq_id := sub_entity_record.component_seq_id;
DELETE FROM BOM_DELETE_ERRORS
WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
AND COMPONENT_SEQUENCE_ID = curr_comp_seq_id;
DELETE FROM BOM_DELETE_ERRORS
WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
AND OPERATION_SEQUENCE_ID = curr_op_seq_id;
** perform delete here if action_type is delete
*/
if (action_type = ACT_DELETE) then
if (execute_delete(curr_del_entity_type,
token_list, archive_flag, action_status,
err_text ) <>0) THEN
ROLLBACK TO SAVEPOINT start_process;
delete_success := 1;
** write to log file for every entity deleted
*/
if(write_log(
sub_entity_record.alternate_designator,
sub_entity_record.organization_code,
sub_entity_record.item_name,
sub_entity_record.comp_name,
sub_entity_record.effectivity_date,
sub_entity_record.op_seq_num,
delete_type,err_text)<>0)then
return 2;
delete_type, err_text)<>0)then
return 2;
/* bug:5726408 Execute UPDATE statement for entity */
IF ( execute_update( curr_del_entity_type,
token_list,
action_status,
err_text ) <> 0 )
THEN
ROLLBACK TO SAVEPOINT start_process;
delete_success := 1;
delete_type,err_text)<>0)then
return 2;
UPDATE BOM_DELETE_SUB_ENTITIES
SET DELETE_STATUS_TYPE = action_status,
DELETE_DATE = decode(action_status, 4,
sysdate, NULL),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = user_id,
REQUEST_ID = req_id,
PROGRAM_UPDATE_DATE = SYSDATE,
PROGRAM_ID = prog_id,
PROGRAM_APPLICATION_ID = prog_appl_id
WHERE DELETE_ENTITY_SEQUENCE_ID = current_seq_id
AND ((delete_type = 4
AND COMPONENT_SEQUENCE_ID =
curr_comp_seq_id)
OR
(curr_del_entity_type = 5
AND OPERATION_SEQUENCE_ID =
curr_op_seq_id));
if (delete_success = 1) then
return 2;
err_text := err_text||'do_delete '||stmt_num||' '||substrb(SQLERRM,1,500);
* Parameters IN :token_list-TOKEN_RECORD, delete_seq_id, delete_entity_type
* Parameters OUT: err_text
* return : 0 -success ,2-error, other - SQL Exception
* Purpose : This function checks all the constraints from
* bom_delete_sql_statements table that are valid for given
* delete entity type
******************************************************************/
FUNCTION constraint_checker( token_list IN Token_Record,
delete_seq_id IN NUMBER,
delete_entity_type IN NUMBER,
err_text OUT NOCOPY varchar2) return NUMBER is
CURSOR constraint_cursor(p_delete_entity_type NUMBER) IS
SELECT SQL_STATEMENT_NAME stmt_name,
DELETE_ON_SUCCESS_FLAG delete_on_success_flag,
MESSAGE_NAME,
SQL_STATEMENT
FROM BOM_DELETE_SQL_STATEMENTS
WHERE SQL_STATEMENT_TYPE = 1
AND ACTIVE_FLAG = 1
AND DELETE_ENTITY_TYPE = p_delete_entity_type;
OPEN constraint_cursor(delete_entity_type);
if ((delete_entity_type = 1 OR delete_entity_type = 2 OR
delete_entity_type = 3) AND first_time = 0)THEN
stmt_num := 3;
delete_entity_type, err_text) <> 0 )THEN
return (FATAL_ERROR);
SELECT SQL_STATEMENT
INTO constraint_stmt
FROM BOM_DELETE_SQL_STATEMENTS
WHERE SQL_STATEMENT_NAME = cur_rec.stmt_name;
** select. In which case, this constraint should not be executed
*/
stmt_num := 5;
if ( instr(constraint_stmt,'SELECT') =0) THEN
return(FATAL_ERROR);
if (cnt =0 AND cur_rec.delete_on_success_flag = 1) OR
(cnt <> 0 and cur_rec.delete_on_success_flag =2) then
error_sequence_number := error_sequence_number +1;
INSERT INTO BOM_DELETE_ERRORS (
DELETE_ENTITY_SEQUENCE_ID,
COMPONENT_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
ERROR_SEQUENCE_NUMBER,
SQL_STATEMENT_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES (
delete_seq_id,
component_sequence_id,
operation_sequence_id,
error_sequence_number,
cur_rec.stmt_name,
SYSDATE,
user_id,
SYSDATE,
user_id);
* Parameters IN : inventory_item_id,organization_id,delete_entity_type
* Parameters OUT: err_text
* return : 0 -success ,2-error, other - SQL Exception
* Purpose : Configuration Item Purge - Consolidate Item
******************************************************************/
FUNCTION config_item_consolidate( p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_delete_entity_type IN NUMBER,
err_text OUT NOCOPY VARCHAR2) return NUMBER is
config_flag VARCHAR2(1) := ' ';
delete_status VARCHAR2(10) := ' ';
buff => 'delete_entity_type:'||to_char(p_delete_entity_type));
select MSI.AUTO_CREATED_CONFIG_FLAG,
MSI.INVENTORY_ITEM_STATUS_CODE,
MSI.BASE_ITEM_ID
into config_flag,
item_status,
base_id
from MTL_SYSTEM_ITEMS MSI
where MSI.ORGANIZATION_ID = p_organization_id
and MSI.INVENTORY_ITEM_ID = p_inventory_item_id;
select BP.BOM_DELETE_STATUS_CODE
into delete_status
from BOM_PARAMETERS BP
where BP.ORGANIZATION_ID = p_organization_id;
IF item_status = delete_status THEN
BEGIN
IF base_id <> p_inventory_item_id and base_id IS NOT NULL THEN
BEGIN
job_count := 0;
IF p_delete_entity_type in (2,3) THEN /* bill or routing */
BEGIN
stmt_num := 4;
select count(*)
into job_count
from WIP_DISCRETE_JOBS WDJ
where WDJ.ORGANIZATION_ID = p_organization_id
and WDJ.PRIMARY_ITEM_ID = p_inventory_item_id
and WDJ.STATUS_TYPE <> 12 /*Closed-no charges allowed*/
and rownum = 1; /* get just the first one that */
update WIP_ENTITIES WE
set WE.PRIMARY_ITEM_ID = base_id
where WE.ORGANIZATION_ID =p_organization_id
and WE.PRIMARY_ITEM_ID = p_inventory_item_id;
update WIP_DISCRETE_JOBS WDJ
set WDJ.PRIMARY_ITEM_ID = base_id,
WDJ.ALTERNATE_BOM_DESIGNATOR = NULL,
WDJ.ALTERNATE_ROUTING_DESIGNATOR = NULL
where WDJ.ORGANIZATION_ID = p_organization_id
and WDJ.PRIMARY_ITEM_ID = p_inventory_item_id;
update WIP_MOVE_TRANSACTIONS WMT
set WMT.PRIMARY_ITEM_ID = base_id
where WMT.ORGANIZATION_ID = p_organization_id
and WMT.PRIMARY_ITEM_ID = p_inventory_item_id;
update WIP_MOVE_TXN_INTERFACE WMTI
set WMTI.PRIMARY_ITEM_ID = base_id
where WMTI.ORGANIZATION_ID = p_organization_id
and WMTI.PRIMARY_ITEM_ID = p_inventory_item_id;
update WIP_REQUIREMENT_OPERATIONS WRO
set WRO.INVENTORY_ITEM_ID = base_id
where WRO.ORGANIZATION_ID = p_organization_id
and WRO.INVENTORY_ITEM_ID = p_inventory_item_id;
update WIP_COST_TXN_INTERFACE WCTI
set WCTI.PRIMARY_ITEM_ID = base_id
where WCTI.ORGANIZATION_ID = p_organization_id
and WCTI.PRIMARY_ITEM_ID = p_inventory_item_id;
update WIP_TRANSACTIONS WT
set WT.PRIMARY_ITEM_ID = base_id
where WT.ORGANIZATION_ID = p_organization_id
and WT.PRIMARY_ITEM_ID = p_inventory_item_id;
IF p_delete_entity_type = 1 THEN /* item */
BEGIN
stmt_num := 12;
update MTL_MATERIAL_TRANSACTIONS MT
set MT.INVENTORY_ITEM_ID = base_id
where MT.ORGANIZATION_ID = p_organization_id
and MT.INVENTORY_ITEM_ID = p_inventory_item_id;
update MTL_TRANSACTION_LOT_NUMBERS MTLN
set MTLN.INVENTORY_ITEM_ID = base_id
where MTLN.ORGANIZATION_ID = p_organization_id
and MTLN.INVENTORY_ITEM_ID = p_inventory_item_id;
update MTL_UNIT_TRANSACTIONS MUT
set MUT.INVENTORY_ITEM_ID = base_id
where MUT.ORGANIZATION_ID = p_organization_id
and MUT.INVENTORY_ITEM_ID = p_inventory_item_id;
update MTL_TRANSACTION_ACCOUNTS MTA
set MTA.INVENTORY_ITEM_ID = base_id
where MTA.ORGANIZATION_ID = p_organization_id
and MTA.INVENTORY_ITEM_ID = p_inventory_item_id;
* FUNCTION : execute_delete
* Parameters IN :token_list-TOKEN_RECORD, delete_entity_type
* archive_flag
* Parameters OUT: err_text, action_status(4-delete,3-error)
* return : 0 -success , other - SQL Exception
* Purpose : This function executes the delete statements from
* bom_delete_sql_statements table that are valid for given
* delete entity type
******************************************************************/
FUNCTION execute_delete(delete_entity_type IN NUMBER,
token_list IN Token_Record,
archive_flag IN NUMBER,
action_status OUT NOCOPY NUMBER,
err_text OUT NOCOPY VARCHAR2) return NUMBER is
CURSOR delete_cursor(p_delete_entity_type NUMBER) IS
SELECT sql_statement stmt, ARCHIVE_TABLE_NAME,
length(ARCHIVE_TABLE_NAME) archive_table_length,
SQL_STATEMENT_NAME stmt_name
FROM BOM_DELETE_SQL_STATEMENTS
WHERE SQL_STATEMENT_TYPE = 2
AND ACTIVE_FLAG = 1
AND DELETE_ENTITY_TYPE = p_delete_entity_type
ORDER BY SEQUENCE_NUMBER;
delete_stmt LONG;
for cur_rec in delete_cursor(delete_entity_type) LOOP
stmt_num := 2;
delete_stmt := upper(cur_rec.stmt);
buff => delete_stmt);
** select. In which case, this constraint should not be executed
*/
stmt_num := 4;
if ( instr(delete_stmt,'DELETE') =0) THEN
return(FATAL_ERROR);
if (substitute_tokens( token_list,delete_stmt,bind_list,err_text)<>0) THEN
return 2;
buff => delete_stmt);
table_name := extract_table_name(delete_stmt,err_text);
where_stmt := extract_where(delete_stmt,err_text);
DBMS_SQL.PARSE(cursor_name, delete_stmt,dbms_sql.native);
action_status := 4; -- deleted successfully
err_text := err_text||'exec_delete '||stmt_num||' '||substrb(SQLERRM,1,500);
* FUNCTION : execute_update
* Parameters IN : delete_entity_type Type of delete entity
* token_list Records of tokens to be substituted
* Parameters OUT: action_status 4-delete, 3-error
* err_text Error message in case of exception
* return : 0 -success , other - SQL Exception
* Purpose : This function executes the update statements from
* bom_delete_sql_statements table that are valid for given
* delete entity type
******************************************************************/
FUNCTION execute_update (
delete_entity_type IN NUMBER,
token_list IN Token_Record,
action_status OUT NOCOPY NUMBER,
err_text OUT NOCOPY VARCHAR2) RETURN NUMBER
IS
CURSOR delete_cursor(p_delete_entity_type NUMBER)
IS
SELECT
SQL_STATEMENT stmt,
SQL_STATEMENT_NAME stmt_name
FROM BOM_DELETE_SQL_STATEMENTS
WHERE
SQL_STATEMENT_TYPE = 3
AND ACTIVE_FLAG = 1
AND DELETE_ENTITY_TYPE = p_delete_entity_type
ORDER BY SEQUENCE_NUMBER;
delete_stmt LONG;
FOR cur_rec IN delete_cursor ( delete_entity_type )
LOOP
/* check to see if sql stmt was truncated, if so then allocate and
* retrieve again */
stmt_num := 2;
delete_stmt := UPPER(cur_rec.stmt);
buff => delete_stmt);
/* check to see if the first word in the statement is UPDATE. */
stmt_num := 3;
IF ( INSTR( delete_stmt, 'UPDATE' ) = 0 )
THEN
RETURN( FATAL_ERROR );
IF ( substitute_tokens( token_list, delete_stmt, bind_list, err_text ) <> 0 )
THEN
RETURN 2;
buff => delete_stmt );
DBMS_SQL.PARSE( cursor_name, delete_stmt, DBMS_SQL.NATIVE );
END LOOP; -- end FOR cur_rec IN delete_cursor
action_status := 4; -- deleted successfully
err_text := err_text||'exec_update '||stmt_num||' '||substrb(SQLERRM,1,500);
END execute_update;
* FUNCTION : update_op_sequences
* Parameters IN :tdelete_entity_type ,routing_seq_id
* Parameters OUT: err_text
* return : 0 -success , other - SQL Exception
* Purpose : updates op sequences to 1 in BOM_INVENTORY_COMPONENTS
******************************************************************/
FUNCTION update_op_sequences(delete_entity_type IN NUMBER,
routing_seq_id IN NUMBER,
op_seq_id IN NUMBER,
err_text OUT NOCOPY varchar2) return NUMBER is
stmt_num NUMBER := 0;
if (delete_entity_type = 3) THEN/* routing delete */
stmt_num := 1;
UPDATE BOM_INVENTORY_COMPONENTS
SET OPERATION_SEQ_NUM = 1
WHERE BILL_SEQUENCE_ID = (SELECT BILL_SEQUENCE_ID
FROM BOM_BILL_OF_MATERIALS BOM,
BOM_OPERATIONAL_ROUTINGS BOR
WHERE BOR.ROUTING_SEQUENCE_ID = routing_seq_id
AND BOR.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND BOR.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND nvl(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE') =
nvl(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE'));
else /* operation delete */
stmt_num := 2;
UPDATE BOM_INVENTORY_COMPONENTS BIC
SET OPERATION_SEQ_NUM = 1
WHERE BILL_SEQUENCE_ID = (SELECT BILL_SEQUENCE_ID
FROM BOM_BILL_OF_MATERIALS BOM,
BOM_OPERATIONAL_ROUTINGS BOR
WHERE BOR.ROUTING_SEQUENCE_ID = routing_seq_id
AND BOR.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND BOR.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND nvl(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE') =
nvl(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE'))
AND OPERATION_SEQ_NUM = (SELECT OPERATION_SEQ_NUM
FROM BOM_OPERATION_SEQUENCES
WHERE OPERATION_SEQUENCE_ID = op_seq_id);
err_text := err_text||'update_op_sequences'||stmt_num||' '||substrb(SQLERRM,1,500);
insert_table IN varchar2,
table_name IN varchar2,
where_clause IN varchar2,
bind_list IN BIND_TABLE,
err_text OUT NOCOPY varchar2) return NUMBER is
l_schema VARCHAR2(30);
SELECT distinct ATC.COLUMN_NAME COLUMN_NAME
FROM ALL_TAB_COLUMNS ATC,
ALL_OBJECTS AO
WHERE TABLE_NAME = trim(prod_table)
AND ( ( AO.OBJECT_TYPE = 'TABLE' AND ATC.OWNER = schema_name )
OR ( AO.OBJECT_TYPE = 'VIEW' AND ATC.OWNER = oracle_user ) )
AND AO.OBJECT_NAME = trim(prod_table)
AND AO.OWNER = ATC.OWNER
ORDER BY COLUMN_NAME;
insert_stmt LONG;
update_stmt LONG;
** update the standard who columns before archiving the data. Need to
** do it this way, since if I try to update after archiving, then I don't
** know which rows were updated. If for some reason there is a failure
** then it rollsback the updates anyways
*/
stmt_num := 0;
update_stmt:= 'UPDATE' || table_name || ' SET REQUEST_ID = ' || req_id
||', PROGRAM_ID = ' || prog_id ||', PROGRAM_APPLICATION_ID =' || resp_appl_id
||', PROGRAM_UPDATE_DATE = sysdate ';
buff => update_stmt);
update_stmt := update_stmt ||' WHERE '||where_clause;
DBMS_SQL.PARSE(cursor_name, update_stmt,dbms_sql.native);
archive_table := insert_table;
SELECT 1
INTO dummy
FROM DUAL
WHERE EXISTS (
SELECT NULL
FROM ALL_TAB_COLUMNS COL1
WHERE TABLE_NAME = trim(prod_table)
AND NOT EXISTS (
SELECT NULL
FROM ALL_TAB_COLUMNS COL2
WHERE TABLE_NAME = trim(archive_table)
AND COL2.COLUMN_NAME = COL1.COLUMN_NAME));
SELECT
ORACLE_USERNAME INTO l_oracleUser
FROM
FND_ORACLE_USERID
WHERE
READ_ONLY_FLAG = 'U';
insert_stmt := insert_stmt || ' INSERT INTO '||insert_table || '( ';
insert_stmt := insert_stmt || col_list.column_name || ' , ';
insert_stmt:= trim (insert_stmt);
insert_stmt := substr(insert_stmt,1,length(insert_stmt)-1 );
insert_stmt := insert_stmt || ' ) SELECT ';
insert_stmt := insert_stmt || col_list.column_name || ' , ';
insert_stmt := trim (insert_stmt);
insert_stmt := substr(insert_stmt,1,length(insert_stmt)-1 );
insert_stmt := insert_stmt || ' FROM '|| prod_table || ' WHERE ' ||where_clause;
buff => insert_stmt);
DBMS_SQL.PARSE(cursor_name, insert_stmt,dbms_sql.native);
position1 := instr (stmt, 'DELETE',1) +6;
* eff_date,op_seq,delete_type.
* Parameters OUT: err_text
* return : 0 -success , other - SQL Exception
* Purpose : This function will write to conc-log
******************************************************************/
FUNCTION write_log(alt_desg IN VARCHAR2,
org_name IN VARCHAR2,
item_name IN VARCHAR2,
comp_name IN VARCHAR2,
eff_date IN VARCHAR2,
op_seq IN NUMBER,
delete_type IN NUMBER,
err_text OUT NOCOPY VARCHAR2) return NUMBER is
err_text1 varchar2(2000);
if(delete_type = 1)then
/* item delete */
Fnd_Message.set_name('BOM', 'BOM_ITEM_DELETED');
elsif(delete_type =2 )then
/* bill delete */
Fnd_Message.set_name('BOM', 'BOM_BILL_DELETED');
elsif(delete_type =3 )then
/* routing delete */
Fnd_Message.set_name('BOM', 'BOM_ROUTING_DELETED');
elsif(delete_type =4 )then
/* component delete */
Fnd_Message.set_name('BOM', 'BOM_COMPONENT_DELETED1');
Fnd_Message.set_name('BOM', 'BOM_COMPONENT_DELETED2');
elsif(delete_type =5 )then
/* operation delete */
Fnd_Message.set_name('BOM', 'BOM_OPERATION_DELETED1');
Fnd_Message.set_name('BOM', 'BOM_OPERATION_DELETED2');
* bill_id, comp_id,delete_type
* Parameters OUT: err_text
* return : 0 -success ,other - SQL Exception
* Purpose : This function will invokde different Business Events
* depending on the parameters passed.
******************************************************************/
FUNCTION invoke_events( p_action_type IN NUMBER,
p_org_id IN NUMBER,
p_assembly_id IN NUMBER,
p_alternate IN VARCHAR2,
p_item_name VARCHAR2,
p_description VARCHAR2,
p_bill_id IN NUMBER,
p_comp_id IN NUMBER,
p_delete_type IN NUMBER,
err_text OUT NOCOPY VARCHAR2) return NUMBER is
l_ret_status varchar2(1);
if (p_delete_type = 1) then
/* Call IP api */ -- bug 4323967
IF (BOM_VALIDATE.Object_Exists(
p_object_type => 'PACKAGE',
p_object_name => 'ICX_CAT_POPULATE_MI_GRP') = 'Y') THEN
SELECT DECODE(master_organization_id, p_org_id, 'Y', 'N'), organization_code
INTO l_master_org_flag, l_org_code
FROM MTL_PARAMETERS
WHERE organization_id = p_org_id;
' ,P_DML_TYPE => ''DELETE'' '||
' , P_INVENTORY_ITEM_ID =>:p_assembly_id '||
' , P_ITEM_NUMBER =>:p_item_name '||
' , P_ORGANIZATION_ID =>:p_org_id '||
' , P_ORGANIZATION_CODE =>:l_org_code '||
' , P_MASTER_ORG_FLAG =>:l_master_org_flag '||
' , P_ITEM_DESCRIPTION =>:p_description '||
' ,X_RETURN_STATUS => :l_ret_status ); '||
' EGO_COMMON_PVT.CANCEL_NIR_FOR_DELETE_ITEM( '||
' P_INVENTORY_ITEM_ID =>:p_assembly_id '||
' , P_ORGANIZATION_ID =>:p_org_id '||
' , P_ITEM_NUMBER =>:p_item_name ); '||
elsif (p_delete_type =2 ) then
Bom_Business_Event_PKG.Raise_Bill_Event
( p_pk1_value =>to_char( p_assembly_id)
,p_pk2_value => to_char(p_org_id)
,p_obj_name => null
,p_structure_name => p_alternate
,p_structure_comment => null
,p_organization_id => p_org_id
,p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_DEL_SUCCESS_EVENT);
elsif (p_delete_type =4) then
Bom_Business_Event_PKG.Raise_Component_Event
( p_bill_sequence_id => p_bill_id
,p_pk1_value => to_char(p_comp_id)
,p_pk2_value => to_char(p_org_id)
,p_obj_name => null
,p_organization_id => p_org_id
,p_comp_item_name => p_item_name
,p_comp_description => p_description
,p_Event_Name => Bom_Business_Event_PKG.G_COMPONENT_DEL_SUCCESS_EVENT);
end if; --if (p_delete_type = 1) then
if (p_delete_type = 1) then
Bom_Business_Event_PKG.Raise_Item_Event
( p_Inventory_Item_Id => p_assembly_id
,p_Organization_Id => p_org_id
,p_item_name => p_item_name
,p_item_description => p_description
,p_Event_Name => Bom_Business_Event_PKG.G_ITEM_DEL_ERROR_EVENT);
elsif (p_delete_type =2 ) then
Bom_Business_Event_PKG.Raise_Bill_Event
( p_pk1_value => to_char(p_assembly_id)
,p_pk2_value => to_char(p_org_id)
,p_obj_name => null
,p_structure_name => p_alternate
,p_structure_comment => null
,p_organization_id => p_org_id
,p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_DEL_ERROR_EVENT);
elsif (p_delete_type =4) then
Bom_Business_Event_PKG.Raise_Component_Event
( p_bill_sequence_id => p_bill_id
,p_pk1_value => to_char(p_comp_id)
,p_pk2_value => to_char(p_org_id)
,p_obj_name => null
,p_organization_id => p_org_id
,p_comp_item_name => p_item_name
,p_comp_description => p_description
,p_Event_Name => Bom_Business_Event_PKG.G_COMPONENT_DEL_ERROR_EVENT);
end if; --if (p_delete_type = 1) then
PROCEDURE delete_groups
(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
delete_group_id IN NUMBER:= '0',
action_type IN NUMBER:= '1',
delete_type IN NUMBER:= '1',
archive IN NUMBER:='1'
)
is
begin
delete_groups(
ERRBUF => ERRBUF,
RETCODE => RETCODE,
delete_group_id => delete_group_id ,
action_type => action_type,
delete_type => delete_type,
archive => archive,
process_errored_rows => 'Y');