The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT RT.ENIGMA_ROUTE_ID,
RT.ATA_CODE,
RT.PDF_FILE_NAME,
RT.ENIGMA_DOC_ID,
RT.DML_OPERATION,
RT.REVISION_DATE,
RT.CONTEXT,
RT.PUBLISH_DATE,
RT.DESCRIPTION
FROM AHL_ENIGMA_RT_INTERFACE RT
WHERE ISRECOVERABLE = 'Y';
SELECT OP.ENIGMA_OP_ID,
OP.ATA_CODE,
OP.ENIGMA_DOC_ID,
OP.DML_OPERATION,
OP.OPERATOR,
OP.EQUIPMENT,
OP.OEM_DOC_TYPE,
OP.CHAPTER_SECTION_SUBJECT,
OP.AMTOSS_FUNCTION,
OP.OP_SEQ,
OP.CONFIG_LETTER,
OP.PUBLISH_DATE,
OP.CONTEXT,
OP.DESCRIPTION
FROM AHL_ENIGMA_OP_INTERFACE OP
WHERE ISRECOVERABLE = 'Y';
SELECT sysdate INTO timebefore from dual;
DELETE FROM AHL_ENIGMA_OP_INTERFACE
WHERE ISRECOVERABLE = 'Y'
AND PROCESS_DATE <= timebefore;
SELECT sysdate INTO timebefore from dual;
DELETE FROM AHL_ENIGMA_RT_INTERFACE
WHERE ISRECOVERABLE = 'Y'
AND PROCESS_DATE <= timebefore;
-- Delete all the routes in pending status from the stagin table
-- as they have already been queried by the cursor
-- moving this code out side of loop
-- Delete all the operations from the stagin table that are in the pending status
-- and that correspond to the parent route
DELETE FROM AHL_RT_OPER_INTERFACE
WHERE PARENT_ENIGMA_ROUTE_ID = l_enigma_route_id AND STATUS = 'PENDING';
-- Delete the pending route records processed
DELETE FROM AHL_RT_OPER_INTERFACE
WHERE STATUS = 'PENDING' AND PARENT_enigma_route_id IS NULL;*/
PROCEDURE Update_Route_Interface_table
(
p_enigma_route_rec IN enigma_route_rec_type,
p_context IN VARCHAR2,
p_pub_date IN DATE,
p_isrecoverable IN VARCHAR2,
p_reason IN VARCHAR2
)
IS
l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'Update_Route_Interface_table';
fnd_log.string(fnd_log.level_statement,l_debug_module,'Before Inserting into interface table');
INSERT INTO AHL_ENIGMA_RT_INTERFACE
(
CONTEXT,
PUBLISH_DATE,
ENIGMA_ROUTE_ID,
PDF_FILE_NAME,
REVISION_DATE,
DML_OPERATION,
ATA_CODE,
DESCRIPTION,
ERROR_MESSAGE,
ENIGMA_DOC_ID,
PROCESS_DATE,
ISRECOVERABLE
)
VALUES
(
p_context,
p_pub_date,
p_enigma_route_rec.enigma_route_id,
p_enigma_route_rec.pdf_file_name,
p_enigma_route_rec.revision_date,
p_enigma_route_rec.dml_operation,
p_enigma_route_rec.ata_code,
p_enigma_route_rec.description,
p_reason,
p_enigma_route_rec.enigma_doc_id,
sysdate,
p_isrecoverable
);
fnd_log.string(fnd_log.level_statement,l_debug_module,'After Inserting into interface table');
END Update_Route_Interface_table;
SELECT route_operation_id,operation_id,object_version_number
FROM ahl_route_operations
WHERE route_id = p_route_id;
SELECT operation_id
FROM ahl_operations_b
WHERE enigma_op_id = p_enigma_op_id
AND TRUNC(NVL(end_date_active, sysdate + 1)) > TRUNC(sysdate)
AND (revision_status_code = 'COMPLETE' OR revision_status_code='DRAFT') ;
SELECT NVL(max(step),0)
FROM ahl_route_operations
WHERE route_id = p_route_id;
-- change = 0 : Delete
-- change = 1 : Retained
-- change = 2 : New Addition
-- Get the existing associations details created through enigma.
l_old_ctr := 1;
-- Determine which associations are to be retained,which to be deleted and which are new associations
l_upper_bound := old_op_assos_table.COUNT;
--populating the record for delete
IF (old_op_assos_table(l_old_ctr).change = 0) THEN
l_route_op_asso_table(l_ctr).route_operation_id :=old_op_assos_table(l_old_ctr).assoc_id;
SELECT route_id,object_version_number,revision_status_code revision_status
FROM AHL_ROUTES_B
WHERE UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_enigma_route_id))
AND REVISION_NUMBER =(
SELECT MAX( revision_number )
FROM AHL_ROUTES_B
WHERE UPPER(TRIM(ENIGMA_ROUTE_ID)) = UPPER(TRIM(p_enigma_route_id)));
SELECT count(*)
FROM AHL_ROUTES_B
WHERE ENIGMA_ROUTE_ID = p_enigma_route_id;
--Functionality:IF DML_OPERATION = 'NC' Then Only pdf will be updated for the route.
--The Pvt API,AHL_RM_ROUTE_PVT.process_route expects DML_OPERATION To be VARCHAR2(1),
--So from this public wrapper,DML_OPERATION will be passed as 'N' to that api
IF (p_enigma_route_rec.DML_OPERATION = 'NC') THEN
-- IF the route is in Approval Pending status , then insert the Enigma Record into the staging table with status as pending.
IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'Y',
'Route is in approval pending status'
);*/
-- Call the API for update
AHL_RM_ROUTE_PVT.process_route
(
'1.0',
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
FND_API.G_FALSE,
p_module_type,--NULL Sthilak for bug #14036337,,
x_return_status,
x_msg_count,
x_msg_data,
p_process_route_input_rec
);
fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling process_route in update mode x_return_status -> ' || x_return_status);
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
-- Insert the transaction record into the staging table, with status as success
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'SUCCESS',
'Route updated Successfully'
);*/
-- Check if the flag id delete and if so delete the route from the CMRO system .
IF (p_enigma_route_rec.DML_OPERATION = 'D') THEN
-- IF the route is in Approval Pending status , then insert the Enigma Record into the staging table with status as pending.
IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'Y',
'Route is in approval pending status'
);*/
-- Call delete_route procedure to delete the route from CMRO End.
AHL_RM_ROUTE_PVT.delete_route
(
'1.0',
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
FND_API.G_FALSE,
p_module_type,--'ENIGMA' Sthilak for bug #14036337,,
x_return_status,
x_msg_count,
x_msg_data,
l_get_latest_route_rev.route_id,
l_get_latest_route_rev.object_version_number
);
fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling delete_routes..Return Status: ' || x_return_status);
fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_ROUTE_PVT.delete_route Error');
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
-- IF the route is in Approval Pending status , then insert the Enigma Record into the staging table with status as pending.
IF ( upper(l_get_latest_route_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_ROUTE_APR_PENDING');
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'Y',
'Route is in approval pending status'
);*/
INSERT INTO AHL_RT_OPER_INTERFACE
(
CONTEXT,
PUBLISH_DATE,
ENIGMA_OPERATION_ID,
PARENT_ENIGMA_ROUTE_ID,
DML_OPERATION,
ATA_CODE,
DESCRIPTION,
STATUS,
REASON,
ENIGMA_DOC_ID
)
VALUES
(
p_context,
p_pub_date,
p_enigma_route_rec.op_asso_tbl(i),
p_enigma_route_rec.enigma_route_id,
NULL,--p_enigma_op_tbl(i).DML_OPERATION,
NULL,--p_enigma_op_tbl(i).ata_code,
NULL,--p_enigma_op_tbl(i).description,
'PENDING',
'Parent Route is in approval pending status',
NULL--p_enigma_op_tbl(i).ENIGMA_DOC_ID
);
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
SELECT object_version_number INTO l_ovn from
AHL_ROUTES_B where route_id = x_route_id;
fnd_log.string(fnd_log.level_statement,l_debug_module,'Before calling process_route in update mode');
-- Call the API for update
AHL_RM_ROUTE_PVT.process_route
(
'1.0',
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
FND_API.G_FALSE,
p_module_type,--NULL Sthilak for bug #14036337,,
x_return_status,
x_msg_count,
x_msg_data,
p_process_route_input_rec
);
fnd_log.string(fnd_log.level_statement,l_debug_module,'After calling process_route in update mode x_return_status -> ' || x_return_status);
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
-- Insert the transaction record into the staging table, with status as success
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'SUCCESS',
'Route updated Successfully'
);*/
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
-- Call the API for update.
AHL_RM_ROUTE_PVT.process_route
(
'1.0',
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
FND_API.G_FALSE,
p_module_type,--NULL Sthilak for bug #14036337,,
x_return_status,
x_msg_count,
x_msg_data,
p_process_route_input_rec
);
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
/*Update_Route_Interface_table(
p_enigma_route_rec,
p_context,
p_pub_date,
'SUCCESS',
'Route updated Successfully'
);*/
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'SUCCESS',
'Route Created Successfully'
);*/
/*Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
'N',
x_msg_data
);*/
Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
isrecoverable,
x_msg_data
);
Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
isrecoverable,
x_msg_data
);
Update_Route_Interface_table
(
p_enigma_route_rec,
p_context,
p_pub_date,
isrecoverable,
x_msg_data
);
PROCEDURE Update_Op_Interface_table
(
p_enigma_op_rec IN enigma_op_rec_type,
p_context IN VARCHAR2,
p_pub_date IN DATE,
p_isrecoverable IN VARCHAR2,
p_reason IN VARCHAR2
)
IS
l_debug_module CONSTANT VARCHAR2(100) := 'AHL.PLSQL.'||'AHL_ENIGMA_ROUTE_OP_PUB'||'.'||'Update_Op_Interface_table';
INSERT INTO AHL_ENIGMA_OP_INTERFACE
(
ENIGMA_OP_ID,
ATA_CODE,
ENIGMA_DOC_ID,
DML_OPERATION,
OPERATOR,
EQUIPMENT,
OEM_DOC_TYPE,
CHAPTER_SECTION_SUBJECT,
AMTOSS_FUNCTION,
OP_SEQ,
CONFIG_LETTER,
PUBLISH_DATE,
PROCESS_DATE,
CONTEXT,
DESCRIPTION,
ERROR_MESSAGE,
ISRECOVERABLE
)
VALUES
(
p_enigma_op_rec.enigma_op_id,
p_enigma_op_rec.ata_code,
p_enigma_op_rec.enigma_doc_id,
p_enigma_op_rec.dml_operation,
p_enigma_op_rec.operator,
p_enigma_op_rec.equipment,
p_enigma_op_rec.oem_doc_type,
p_enigma_op_rec.chapter_section_subject,
p_enigma_op_rec.amtoss_function,
p_enigma_op_rec.op_seq,
p_enigma_op_rec.config_letter,
p_pub_date,
sysdate,
p_context,
p_enigma_op_rec.description,
p_reason,
p_isrecoverable
);
End Update_Op_Interface_table;
SELECT operation_id,object_version_number,revision_status_code revision_status
FROM AHL_OPERATIONS_B
WHERE UPPER(TRIM(ENIGMA_OP_ID)) = UPPER(TRIM(c_ENIGMA_OP_ID))
AND REVISION_NUMBER =
( SELECT MAX( revision_number )
FROM AHL_OPERATIONS_B
WHERE UPPER(TRIM(ENIGMA_OP_ID)) = UPPER(TRIM(c_ENIGMA_OP_ID))
);
SELECT aop.object_version_number
FROM ahl_operations_b aop
WHERE aop.operation_id = c_oper_id;
SELECT AHL_ENIGMA_OP_SEQ_S.NEXTVAL
INTO l_op_sq
FROM DUAL;
-- Call the API for update
AHL_RM_OPERATION_PVT.process_operation
(
'1.0',
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
FND_API.G_FALSE,
p_module_type,--NULL Sthilak for bug #14036337,,
x_return_status,
x_msg_count,
x_msg_data,
p_process_oper_input_rec
);
fnd_log.string(fnd_log.level_statement,l_debug_module,'before inserting the operations into staging table ');
-- Insert the transaction record into the staging table, with status as success
OP_interface_insert_row(
X_CONTEXT => p_context,
X_PUBLISH_DATE => p_pub_date,
X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
X_DESCRIPTION => p_enigma_op_rec.description,
X_STATUS => 'SUCCESS',
X_REASON => 'Operation created successfully',
X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
);*/
-- If the operation is in Approval Pending status , then insert the operation record into the
-- staging table with status as pending.
IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OP_APR_PENDING');
/*OP_interface_insert_row(
X_CONTEXT => p_context,
X_PUBLISH_DATE => p_pub_date,
X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
X_DESCRIPTION => p_enigma_op_rec.description,
X_STATUS => 'PENDING',
X_REASON => 'Operation is in approval pending status',
X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
);*/
-- Call the API for update
AHL_RM_OPERATION_PVT.process_operation
(
'1.0',
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
FND_API.G_FALSE,
p_module_type,--NULL Sthilak for bug #14036337,,
x_return_status,
x_msg_count,
x_msg_data,
p_process_oper_input_rec
);
fnd_log.string(fnd_log.level_statement,l_debug_module,'Update Operation-> Draft');
-- Call the API for update
AHL_RM_OPERATION_PVT.process_operation
(
'1.0',
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
FND_API.G_FALSE,
p_module_type,--NULL Sthilak for bug #14036337,,
x_return_status,
x_msg_count,
x_msg_data,
p_process_oper_input_rec
);
fnd_log.string(fnd_log.level_statement,l_debug_module,'Before inserting into the staging table');
-- Insert the transaction record into the staging table, with status as success
/*OP_interface_insert_row(
X_CONTEXT => p_context,
X_PUBLISH_DATE => p_pub_date,
X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
X_DESCRIPTION => p_enigma_op_rec.description,
X_STATUS => 'SUCCESS',
X_REASON => 'Operation updated successfully',
X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
);*/
fnd_log.string(fnd_log.level_statement,l_debug_module,'After inserting into the staging table');
fnd_log.string(fnd_log.level_statement,l_debug_module,'Delete :Cursor Found :Operation Exist ');
-- IF the operation is in Approval Pending status , then insert the operation record into the staging table with status as pending.
-- This table is going to be restructured and based on new table this part of code will be modified.
IF ( upper(l_get_latest_oper_rev.revision_status) = 'APPROVAL_PENDING' ) THEN
FND_MESSAGE.Set_Name('AHL','AHL_ENIGMA_OP_APR_PENDING');
/*OP_interface_insert_row(
X_CONTEXT => p_context,
X_PUBLISH_DATE => p_pub_date,
X_ENIGMA_OPERATION_ID => p_enigma_op_rec.ENIGMA_OP_ID,
X_DML_OPERATION => p_enigma_op_rec.DML_OPERATION,
X_DESCRIPTION => p_enigma_op_rec.description,
X_STATUS => 'PENDING',
X_REASON => 'Operation is in Pending Status',
X_ENIGMA_DOC_ID => p_enigma_op_rec.ENIGMA_DOC_ID
);*/
fnd_log.string(fnd_log.level_statement,l_debug_module,'Inside else , calling delete operation');
-- Call the delete operation API
AHL_RM_OPERATION_PVT.delete_operation
(
1.0,
FND_API.G_TRUE,
FND_API.G_FALSE,
FND_API.G_VALID_LEVEL_FULL,
FND_API.G_FALSE,
NULL,
x_return_status,
x_msg_count,
x_msg_data,
l_get_latest_oper_rev.operation_id,
l_get_latest_oper_rev.object_version_number
);
fnd_log.string(fnd_log.level_statement,l_debug_module,'After AHL_RM_OPERATION_PVT.delete_operation');
fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.delete_operation Error');
fnd_log.string(fnd_log.level_statement,l_debug_module,'AHL_RM_OPERATION_PVT.delete_operation -> deletion Successful');
Update_OP_Interface_table
(
p_enigma_op_rec,
p_context,
p_pub_date,
isrecoverable,
x_msg_data
);
Update_OP_Interface_table
(
p_enigma_op_rec,
p_context,
p_pub_date,
isrecoverable,
x_msg_data
);
Update_OP_Interface_table
(
p_enigma_op_rec,
p_context,
p_pub_date,
isrecoverable,
x_msg_data
);
select fnd_lobs_s.nextval into seqNo from dual;
insert into fnd_lobs(
file_id,
file_name,
file_content_type,
language,
file_data,
file_format,
upload_date
)
values(seqNo,
p_file_name,
'application/octet-stream',
'US',
l_contentBlob,
'binary',
sysdate
);