[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select count(title)
From AHL_MR_HEADERS_APP_V
Where title=C_TITLE
And (MR_STATUS_CODE='DRAFT'
OR MR_STATUS_CODE='APPROVAL_REJECTED'
OR MR_STATUS_CODE='APPROVAL_PENDING')
AND MR_HEADER_ID > p_source_mr_header_id ;
Select count(*)
From ahl_mr_headers_APP_V
Where title=C_TITLE
-- And mr_status_code='COMPLETE'
And mr_header_id >C_MR_HEADER_ID
And version_number>C_VERSION_NUMBER;
Select MR_STATUS_CODE,TITLE,VERSION_NUMBER
into l_status,l_title,l_version_number
From ahl_mr_headers_app_v
Where mr_header_id=p_source_mr_header_id;
select mr_header_id
from AHL_MR_HEADERS_APP_V
where mr_header_id=p_source_mr_header_id;
SELECT
MR_HEADER_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TITLE,
VERSION_NUMBER,
-- pdoki, Preceding MR Header ID is obsoleted after SBE Project.
PRECEDING_MR_HEADER_ID,
CATEGORY_CODE,
SERVICE_TYPE_CODE,
MR_STATUS_CODE,
IMPLEMENT_STATUS_CODE,
REPETITIVE_FLAG,
SHOW_REPETITIVE_CODE,
WHICHEVER_FIRST_CODE,
COPY_ACCOMPLISHMENT_FLAG,
PROGRAM_TYPE_CODE,
PROGRAM_SUBTYPE_CODE,
EFFECTIVE_FROM,
EFFECTIVE_TO,
REVISION,
BILLING_ITEM_ID,
BILLING_ORG_ID,
SPACE_CATEGORY_CODE,
--sareepar service actegory rank
SERVICE_CATEGORY_RANK,
QA_INSPECTION_TYPE_CODE,
DESCRIPTION,
COMMENTS,
SERVICE_REQUEST_TEMPLATE_ID,
TYPE_CODE,
DOWN_TIME,
--CHANDANK, 29-Sep-2010, added supplier warranty ID for supplier warranty project
WARRANTY_TEMPLATE_ID,
UOM_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
AUTO_SIGNOFF_FLAG,
COPY_INIT_ACCOMPL_FLAG,
COPY_DEFERRALS_FLAG,
APPLICATION_USG_CODE
from AHL_MR_HEADERS_APP_V
where mr_header_id=p_source_mr_header_id;
select
A.DOC_TITLE_ASSO_ID,
A.OBJECT_VERSION_NUMBER,
A.LAST_UPDATE_DATE,
A.LAST_UPDATED_BY,
A.CREATION_DATE,
A.CREATED_BY,
A.LAST_UPDATE_LOGIN,
A.DOC_REVISION_ID,
A.ASO_OBJECT_TYPE_CODE,
A.ASO_OBJECT_ID,
A.DOCUMENT_ID,
A.USE_LATEST_REV_FLAG,
A.SERIAL_NO,
A.SECURITY_GROUP_ID,
A.ATTRIBUTE_CATEGORY,
A.ATTRIBUTE1,
A.ATTRIBUTE2,
A.ATTRIBUTE3,
A.ATTRIBUTE4,
A.ATTRIBUTE5,
A.ATTRIBUTE6,
A.ATTRIBUTE7,
A.ATTRIBUTE8,
A.ATTRIBUTE9,
A.ATTRIBUTE10,
A.ATTRIBUTE11,
A.ATTRIBUTE12,
A.ATTRIBUTE13,
A.ATTRIBUTE14,
A.ATTRIBUTE15,
A.SOURCE_REF_CODE,
b.chapter,
b.section,
b.subject,
b.page,
b.figure,
b.note
from AHL_DOC_TITLE_ASSOS_B A,AHL_DOC_TITLE_ASSOS_TL B
where A.ASO_OBJECT_TYPE_CODE='MR'
AND A.ASO_OBJECT_ID=p_source_mr_header_id
and A.doc_title_asso_id=B.doc_title_asso_id
AND B.LANGUAGE=USERENV('LANG')
AND A.ASO_OBJECT_ID NOT IN (SELECT DOCUMENT_ID
FROM AHL_DOC_REVISIONS_VL
WHERE DOCUMENT_ID=A.ASO_OBJECT_ID
AND NVL(REVISION_STATUS_CODE,'CURRENT')='OBSOLETE');
SELECT
MR_ROUTE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_HEADER_ID,
ROUTE_ID,
--MANESING::VWP Enhancements, 21-Jan-2011, added stage type code
STAGE_TYPE_CODE,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
STAGE
FROM AHL_MR_ROUTES A
WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID
AND ROUTE_ID IN (SELECT ROUTE_ID
FROM AHL_ROUTES_B
WHERE ROUTE_ID=A.ROUTE_ID
AND NVL(END_DATE_ACTIVE,sysdate+1)>SYSDATE
AND REVISION_STATUS_CODE='COMPLETE'
);
SELECT MR_ROUTE_SEQUENCE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_ROUTE_ID,
RELATED_MR_ROUTE_ID,
SEQUENCE_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_MR_ROUTE_SEQUENCES C
WHERE MR_ROUTE_ID=C_MR_ROUTE_ID
AND EXISTS
(SELECT MR_ROUTE_ID
FROM AHL_MR_ROUTES A
WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID
AND MR_ROUTE_ID=C.RELATED_MR_ROUTE_ID
AND ROUTE_ID IN (SELECT ROUTE_ID
FROM AHL_ROUTES_B
WHERE ROUTE_ID=A.ROUTE_ID
AND NVL(END_DATE_ACTIVE,sysdate+1)>SYSDATE
AND REVISION_STATUS_CODE='COMPLETE'
)
);
SELECT
MR_EFFECTIVITY_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_HEADER_ID,
NAME,
THRESHOLD_DATE,
INVENTORY_ITEM_ID,
INVENTORY_ORG_ID,
RELATIONSHIP_ID,
PC_NODE_ID,
DEFAULT_FLAG,
PROGRAM_DURATION,
PROGRAM_DURATION_UOM_CODE,
FLEET_HEADER_ID, -- SANSATPA added for MR effectivity UI
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_MR_EFFECTIVITIES_APP_V
WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID;
SELECT
MR_INTERVAL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_EFFECTIVITY_ID,
COUNTER_ID,
INTERVAL_VALUE,
EARLIEST_DUE_VALUE,
START_VALUE,
STOP_VALUE,
START_DATE,
CALC_DUEDATE_RULE_CODE, --pdoki added for ADAT ER
STOP_DATE,
TOLERANCE_BEFORE,
TOLERANCE_AFTER,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
RESET_VALUE
FROM AHL_MR_INTERVALS_APP_V
WHERE MR_EFFECTIVITY_ID=C_MR_EFFECTIVITY_ID;
SELECT
MR_EFFECTIVITY_DETAIL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_EFFECTIVITY_ID,
EXCLUDE_FLAG,
MANUFACTURER_ID,
COUNTRY_CODE,
SERIAL_NUMBER_FROM,
SERIAL_NUMBER_TO,
MANUFACTURE_DATE_FROM,
MANUFACTURE_DATE_TO,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_MR_EFFECTIVITY_DTLS_APP_V
WHERE MR_EFFECTIVITY_ID=C_MR_EFFECTIVITY_ID;
SELECT
MR_EFFECTIVITY_EXT_DTL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_EFFECTIVITY_ID,
EXCLUDE_FLAG,
EFFECT_EXT_DTL_REC_TYPE,
OWNER_ID,
LOCATION_TYPE_CODE,
CSI_EXT_ATTRIBUTE_CODE,
CSI_EXT_ATTRIBUTE_VALUE,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_MR_EFFECTIVITY_EXT_DTLS
WHERE MR_EFFECTIVITY_ID=C_MR_EFFECTIVITY_ID;
SELECT
MR_RELATIONSHIP_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_HEADER_ID,
RELATED_MR_HEADER_ID,
RELATIONSHIP_CODE,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_MR_RELATIONSHIPS_APP_V A
WHERE (MR_HEADER_ID=P_SOURCE_MR_HEADER_ID or RELATED_MR_HEADER_ID=P_SOURCE_MR_HEADER_ID)
AND EXISTS(SELECT MR_HEADER_ID
FROM AHL_MR_HEADERS_APP_V
WHERE ( MR_HEADER_ID=A.MR_HEADER_ID
OR MR_HEADER_ID=A.RELATED_MR_HEADER_ID)
AND MR_STATUS_CODE<>'TERMINATED'
AND NVL(EFFECTIVE_TO,SYSDATE+1) >SYSDATE);
SELECT
MR_VISIT_TYPE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_VISIT_TYPE_CODE,
MR_HEADER_ID,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_MR_VISIT_TYPES
WHERE MR_HEADER_ID=P_SOURCE_MR_HEADER_ID;
SELECT rel.MR_RELATIONSHIP_ID,
rel.OBJECT_VERSION_NUMBER,
rel.LAST_UPDATE_DATE,
rel.LAST_UPDATED_BY,
rel.CREATION_DATE,
rel.CREATED_BY,
rel.LAST_UPDATE_LOGIN,
rel.MR_HEADER_ID,
rel.START_MR_RELATIONSHIP_ID,
rel.RELATIONSHIP_CODE,
rel.SEQUENCE_NUMBER,
rel.SECURITY_GROUP_ID,
rel.ATTRIBUTE_CATEGORY,
rel.ATTRIBUTE1,
rel.ATTRIBUTE2,
rel.ATTRIBUTE3,
rel.ATTRIBUTE4,
rel.ATTRIBUTE5,
rel.ATTRIBUTE6,
rel.ATTRIBUTE7,
rel.ATTRIBUTE8,
rel.ATTRIBUTE9,
rel.ATTRIBUTE10,
rel.ATTRIBUTE11,
rel.ATTRIBUTE12,
rel.ATTRIBUTE13,
rel.ATTRIBUTE14,
rel.ATTRIBUTE15
FROM ahl_mr_loop_chain_relns rel,
ahl_mr_headers_b mr
WHERE mr.mr_header_id = rel.mr_header_id
AND rel.mr_relationship_id IN
(
SELECT rel.MR_RELATIONSHIP_ID
FROM ahl_mr_loop_chain_relns rel
WHERE start_mr_relationship_id IN
(SELECT mr_relationship_id
FROM ahl_mr_loop_chain_relns
WHERE mr_header_id = p_mr_header_id_csr
)
)
AND TRUNC(sysdate) <= TRUNC(NVL(MR.effective_to,sysdate+1))
AND mr.mr_status_code <> 'TERMINATED'
ORDER BY rel.START_MR_RELATIONSHIP_ID,rel.sequence_number;
SELECT rel.MR_RELATIONSHIP_ID,
rel.OBJECT_VERSION_NUMBER,
rel.LAST_UPDATE_DATE,
rel.LAST_UPDATED_BY,
rel.CREATION_DATE,
rel.CREATED_BY,
rel.LAST_UPDATE_LOGIN,
rel.MR_HEADER_ID,
rel.START_MR_RELATIONSHIP_ID,
rel.RELATIONSHIP_CODE,
rel.SEQUENCE_NUMBER,
rel.SECURITY_GROUP_ID,
rel.ATTRIBUTE_CATEGORY,
rel.ATTRIBUTE1,
rel.ATTRIBUTE2,
rel.ATTRIBUTE3,
rel.ATTRIBUTE4,
rel.ATTRIBUTE5,
rel.ATTRIBUTE6,
rel.ATTRIBUTE7,
rel.ATTRIBUTE8,
rel.ATTRIBUTE9,
rel.ATTRIBUTE10,
rel.ATTRIBUTE11,
rel.ATTRIBUTE12,
rel.ATTRIBUTE13,
rel.ATTRIBUTE14,
rel.ATTRIBUTE15
FROM ahl_mr_loop_chain_relns rel,
ahl_mr_headers_b mr
WHERE mr.mr_header_id = rel.mr_header_id
AND rel.mr_relationship_id IN
(SELECT mr_relationship_id
FROM ahl_mr_loop_chain_relns
WHERE mr_header_id = p_mr_header_id_csr
AND mr_relationship_id <> start_mr_relationship_id
)
AND TRUNC(sysdate) <= TRUNC(NVL(MR.effective_to,sysdate+1))
AND mr.mr_status_code <> 'TERMINATED'
ORDER BY rel.START_MR_RELATIONSHIP_ID,rel.sequence_number;
AHL_MR_HEADERS_PKG.INSERT_ROW (
X_MR_HEADER_ID =>x_new_mr_header_id,
X_OBJECT_VERSION_NUMBER =>1,
X_CATEGORY_CODE =>l_mr_header_Rec.CATEGORY_CODE,
X_SERVICE_TYPE_CODE =>l_mr_header_Rec.SERVICE_TYPE_CODE,
X_MR_STATUS_CODE =>'DRAFT',
X_IMPLEMENT_STATUS_CODE =>l_mr_header_Rec.IMPLEMENT_STATUS_CODE,
X_REPETITIVE_FLAG =>l_mr_header_Rec.REPETITIVE_FLAG,
X_SHOW_REPETITIVE_CODE =>l_mr_header_Rec.SHOW_REPETITIVE_CODE,
X_WHICHEVER_FIRST_CODE =>l_mr_header_Rec.WHICHEVER_FIRST_CODE,
X_COPY_ACCOMPLISHMENT_FLAG=> 'Y',-- defaulting l_mr_header_Rec.COPY_ACCOMPLISHMENT_FLAG,
X_PROGRAM_TYPE_CODE =>l_mr_header_Rec.PROGRAM_TYPE_CODE ,
X_PROGRAM_SUBTYPE_CODE =>l_mr_header_Rec.PROGRAM_SUBTYPE_CODE,
X_EFFECTIVE_FROM =>l_date,
X_EFFECTIVE_TO =>NULL,
X_REVISION =>l_mr_header_Rec.REVISION,
X_ATTRIBUTE_CATEGORY =>l_mr_header_Rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>l_mr_header_Rec.ATTRIBUTE1,
X_ATTRIBUTE2 =>l_mr_header_Rec.ATTRIBUTE2,
X_ATTRIBUTE3 =>l_mr_header_Rec.ATTRIBUTE3,
X_ATTRIBUTE4 =>l_mr_header_Rec.ATTRIBUTE4,
X_ATTRIBUTE5 =>l_mr_header_Rec.ATTRIBUTE5,
X_ATTRIBUTE6 =>l_mr_header_Rec.ATTRIBUTE6,
X_ATTRIBUTE7 =>l_mr_header_Rec.ATTRIBUTE7,
X_ATTRIBUTE8 =>l_mr_header_Rec.ATTRIBUTE8,
X_ATTRIBUTE9 =>l_mr_header_Rec.ATTRIBUTE9,
X_ATTRIBUTE10 =>l_mr_header_Rec.ATTRIBUTE10,
X_ATTRIBUTE11 =>l_mr_header_Rec.ATTRIBUTE11,
X_ATTRIBUTE12 =>l_mr_header_Rec.ATTRIBUTE12,
X_ATTRIBUTE13 =>l_mr_header_Rec.ATTRIBUTE13,
X_ATTRIBUTE14 =>l_mr_header_Rec.ATTRIBUTE14,
X_ATTRIBUTE15 =>l_mr_header_Rec.ATTRIBUTE15,
X_TITLE =>l_mr_header_Rec.TITLE,
X_VERSION_NUMBER =>l_version_number,
-- pdoki, Preceding MR Header ID is obsoleted after SBE Project.
X_PRECEDING_MR_HEADER_ID=>l_mr_header_Rec.PRECEDING_MR_HEADER_ID,
X_SERVICE_REQUEST_TEMPLATE_ID=>l_mr_header_Rec.SERVICE_REQUEST_TEMPLATE_ID,
X_TYPE_CODE =>l_mr_header_Rec.TYPE_CODE,
X_DOWN_TIME =>l_mr_header_Rec.DOWN_TIME,
--CHANDANK, 29-Sep-2010, added supplier warranty ID for supplier warranty project
X_WARRANTY_TEMPLATE_ID =>l_mr_header_Rec.WARRANTY_TEMPLATE_ID,
X_UOM_CODE =>l_mr_header_Rec.UOM_CODE,
X_DESCRIPTION =>l_mr_header_Rec.DESCRIPTION,
X_COMMENTS =>l_mr_header_Rec.COMMENTS,
X_SPACE_CATEGORY_CODE =>l_mr_header_Rec.SPACE_CATEGORY_CODE,
--sareepar service category rank
X_SERVICE_CATEGORY_RANK =>l_mr_header_Rec.SERVICE_CATEGORY_RANK,
X_QA_INSPECTION_TYPE_CODE =>l_mr_header_Rec.QA_INSPECTION_TYPE_CODE,
X_BILLING_ITEM_ID =>l_mr_header_Rec.BILLING_ITEM_ID,
X_AUTO_SIGNOFF_FLAG =>l_mr_header_Rec.AUTO_SIGNOFF_FLAG,
-- defaulting to Yes for these attributes when new revision is created
X_COPY_INIT_ACCOMPL_FLAG =>'Y',
X_COPY_DEFERRALS_FLAG =>'Y',
X_CREATION_DATE =>sysdate,
X_CREATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_DATE =>sysdate,
X_LAST_UPDATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
SELECT AHL_DOC_TITLE_ASSOS_B_S.Nextval INTO
l_doc_title_asso_id from DUAL;
AHL_DOC_TITLE_ASSOS_PKG.INSERT_ROW(
X_ROWID =>l_row_id,
X_DOC_TITLE_ASSO_ID =>l_doc_title_asso_id,
X_SERIAL_NO =>l_association_rec.serial_no,
X_ATTRIBUTE_CATEGORY =>l_association_rec.attribute_category,
X_ATTRIBUTE1 =>l_association_rec.attribute1,
X_ATTRIBUTE2 =>l_association_rec.attribute2,
X_ATTRIBUTE3 =>l_association_rec.attribute3,
X_ATTRIBUTE4 =>l_association_rec.attribute4,
X_ATTRIBUTE5 =>l_association_rec.attribute5,
X_ATTRIBUTE6 =>l_association_rec.attribute6,
X_ATTRIBUTE7 =>l_association_rec.attribute7,
X_ATTRIBUTE8 =>l_association_rec.attribute8,
X_ATTRIBUTE9 =>l_association_rec.attribute9,
X_ATTRIBUTE10 =>l_association_rec.attribute10,
X_ATTRIBUTE11 =>l_association_rec.attribute11,
X_ATTRIBUTE12 =>l_association_rec.attribute12,
X_ATTRIBUTE13 =>l_association_rec.attribute13,
X_ATTRIBUTE14 =>l_association_rec.attribute14,
X_ATTRIBUTE15 =>l_association_rec.attribute15,
X_ASO_OBJECT_TYPE_CODE =>l_association_rec.aso_object_type_code,
X_SOURCE_REF_CODE =>l_association_rec.source_ref_code,
X_ASO_OBJECT_ID =>x_new_mr_header_id,
X_DOCUMENT_ID =>l_association_rec.document_id,
X_USE_LATEST_REV_FLAG =>l_association_rec.use_latest_rev_flag,
X_DOC_REVISION_ID =>l_association_rec.doc_revision_id,
X_OBJECT_VERSION_NUMBER =>1,
X_CHAPTER =>l_association_rec.chapter,
X_SECTION =>l_association_rec.section,
X_SUBJECT =>l_association_rec.subject,
X_FIGURE =>l_association_rec.figure,
X_PAGE =>l_association_rec.page,
X_NOTE =>l_association_rec.note,
X_CREATION_DATE =>sysdate,
X_CREATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_DATE =>sysdate,
X_LAST_UPDATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id);
AHL_MR_ROUTES_PKG.INSERT_ROW (
X_MR_ROUTE_ID =>l_new_mr_ROUTE_ID,
X_STAGE =>l_mr_route_rec.STAGE,
X_OBJECT_VERSION_NUMBER =>1,
X_MR_HEADER_ID =>x_new_mr_header_id,
X_ROUTE_ID =>l_mr_route_Rec.ROUTE_ID,
--MANESING::VWP Enhancements, 21-Jan-2011, added stage type code column
X_STAGE_TYPE_CODE =>l_mr_route_Rec.STAGE_TYPE_CODE,
X_ATTRIBUTE_CATEGORY =>l_mr_route_Rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>l_mr_route_Rec.ATTRIBUTE1,
X_ATTRIBUTE2 =>l_mr_route_Rec.ATTRIBUTE2,
X_ATTRIBUTE3 =>l_mr_route_Rec.ATTRIBUTE3,
X_ATTRIBUTE4 =>l_mr_route_Rec.ATTRIBUTE4,
X_ATTRIBUTE5 =>l_mr_route_Rec.ATTRIBUTE5,
X_ATTRIBUTE6 =>l_mr_route_Rec.ATTRIBUTE6,
X_ATTRIBUTE7 =>l_mr_route_Rec.ATTRIBUTE7,
X_ATTRIBUTE8 =>l_mr_route_Rec.ATTRIBUTE8,
X_ATTRIBUTE9 =>l_mr_route_Rec.ATTRIBUTE9,
X_ATTRIBUTE10 =>l_mr_route_Rec.ATTRIBUTE11,
X_ATTRIBUTE11 =>l_mr_route_Rec.ATTRIBUTE12,
X_ATTRIBUTE12 =>l_mr_route_Rec.ATTRIBUTE13,
X_ATTRIBUTE13 =>l_mr_route_Rec.ATTRIBUTE14,
X_ATTRIBUTE14 =>l_mr_route_Rec.ATTRIBUTE15,
X_ATTRIBUTE15 =>l_mr_route_Rec.ATTRIBUTE15,
X_CREATION_DATE =>sysdate,
X_CREATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_DATE =>sysdate,
X_LAST_UPDATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
AHL_MR_ROUTE_SEQUENCES_PKG.INSERT_ROW (
X_MR_ROUTE_SEQUENCE_ID =>l_new_mr_route_seq_id,
X_RELATED_MR_ROUTE_ID =>l_seq_rel_mr_route_id,
X_SEQUENCE_CODE =>l_mr_route_seq_rec.SEQUENCE_CODE,
X_MR_ROUTE_ID =>l_seq_mr_route_id,
X_OBJECT_VERSION_NUMBER =>1,
X_ATTRIBUTE_CATEGORY =>l_mr_route_seq_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 =>l_mr_route_seq_rec.ATTRIBUTE1,
X_ATTRIBUTE2 =>l_mr_route_seq_rec.ATTRIBUTE2,
X_ATTRIBUTE3 =>l_mr_route_seq_rec.ATTRIBUTE3,
X_ATTRIBUTE4 =>l_mr_route_seq_rec.ATTRIBUTE4,
X_ATTRIBUTE5 =>l_mr_route_seq_rec.ATTRIBUTE5,
X_ATTRIBUTE6 =>l_mr_route_seq_rec.ATTRIBUTE6,
X_ATTRIBUTE7 =>l_mr_route_seq_rec.ATTRIBUTE7,
X_ATTRIBUTE8 =>l_mr_route_seq_rec.ATTRIBUTE8,
X_ATTRIBUTE9 =>l_mr_route_seq_rec.ATTRIBUTE9,
X_ATTRIBUTE10 =>l_mr_route_seq_rec.ATTRIBUTE10,
X_ATTRIBUTE11 =>l_mr_route_seq_rec.ATTRIBUTE11,
X_ATTRIBUTE12 =>l_mr_route_seq_rec.ATTRIBUTE12,
X_ATTRIBUTE13 =>l_mr_route_seq_rec.ATTRIBUTE13,
X_ATTRIBUTE14 =>l_mr_route_seq_rec.ATTRIBUTE14,
X_ATTRIBUTE15 =>l_mr_route_seq_rec.ATTRIBUTE15,
X_CREATION_DATE =>sysdate,
X_CREATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_DATE =>sysdate,
X_LAST_UPDATED_BY =>fnd_global.user_id,
X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
INSERT INTO AHL_MR_EFFECTIVITIES
(
MR_EFFECTIVITY_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_HEADER_ID,
NAME,
THRESHOLD_DATE,
INVENTORY_ITEM_ID,
INVENTORY_ORG_ID,
RELATIONSHIP_ID,
PC_NODE_ID,
FLEET_HEADER_ID, -- SANSATPA added for MR effectivity UI
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(
AHL_MR_EFFECTIVITIES_S.NEXTVAL,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
x_new_mr_header_id,
l_mr_eff_rec.NAME,
l_mr_eff_rec.THRESHOLD_DATE,
l_mr_eff_rec.INVENTORY_ITEM_ID,
l_mr_eff_rec.INVENTORY_ORG_ID,
l_mr_eff_rec.RELATIONSHIP_ID,
l_mr_eff_rec.PC_NODE_ID,
l_mr_eff_rec.FLEET_HEADER_ID, -- SANSATPA added for MR effectivity UI
l_mr_eff_rec.ATTRIBUTE_CATEGORY,
l_mr_eff_rec.ATTRIBUTE1,
l_mr_eff_rec.ATTRIBUTE2,
l_mr_eff_rec.ATTRIBUTE3,
l_mr_eff_rec.ATTRIBUTE4,
l_mr_eff_rec.ATTRIBUTE5,
l_mr_eff_rec.ATTRIBUTE6,
l_mr_eff_rec.ATTRIBUTE7,
l_mr_eff_rec.ATTRIBUTE8,
l_mr_eff_rec.ATTRIBUTE9,
l_mr_eff_rec.ATTRIBUTE10,
l_mr_eff_rec.ATTRIBUTE11,
l_mr_eff_rec.ATTRIBUTE12,
l_mr_eff_rec.ATTRIBUTE13,
l_mr_eff_rec.ATTRIBUTE14,
l_mr_eff_rec.ATTRIBUTE15
)
RETURNING mr_effectivity_id INTO l_new_mr_effectivity_id;
INSERT INTO AHL_MR_INTERVALS
(
MR_INTERVAL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_EFFECTIVITY_ID,
COUNTER_ID,
INTERVAL_VALUE,
EARLIEST_DUE_VALUE,
START_VALUE,
STOP_VALUE,
START_DATE,
CALC_DUEDATE_RULE_CODE, --pdoki added for ADAT ER
STOP_DATE,
TOLERANCE_BEFORE,
TOLERANCE_AFTER,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(
AHL_MR_INTERVALS_S.NEXTVAL,
l_interval_rec.OBJECT_VERSION_NUMBER,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
l_new_mr_effectivity_id,
l_interval_rec.COUNTER_ID,
l_interval_rec.INTERVAL_VALUE,
l_interval_rec.EARLIEST_DUE_VALUE,
l_interval_rec.START_VALUE,
l_interval_rec.STOP_VALUE,
l_interval_rec.START_DATE,
l_interval_rec.CALC_DUEDATE_RULE_CODE, --pdoki added for ADAT ER
l_interval_rec.STOP_DATE,
l_interval_rec.TOLERANCE_BEFORE,
l_interval_rec.TOLERANCE_AFTER,
l_interval_rec.SECURITY_GROUP_ID,
l_interval_rec.ATTRIBUTE_CATEGORY,
l_interval_rec.ATTRIBUTE1,
l_interval_rec.ATTRIBUTE2,
l_interval_rec.ATTRIBUTE3,
l_interval_rec.ATTRIBUTE4,
l_interval_rec.ATTRIBUTE5,
l_interval_rec.ATTRIBUTE6,
l_interval_rec.ATTRIBUTE7,
l_interval_rec.ATTRIBUTE8,
l_interval_rec.ATTRIBUTE9,
l_interval_rec.ATTRIBUTE10,
l_interval_rec.ATTRIBUTE11,
l_interval_rec.ATTRIBUTE12,
l_interval_rec.ATTRIBUTE13,
l_interval_rec.ATTRIBUTE14,
l_interval_rec.ATTRIBUTE15
);
INSERT INTO AHL_MR_EFFECTIVITY_DTLS
(
MR_EFFECTIVITY_DETAIL_ID,
MR_EFFECTIVITY_ID,
EXCLUDE_FLAG,
MANUFACTURER_ID,
COUNTRY_CODE,
SERIAL_NUMBER_FROM,
SERIAL_NUMBER_TO,
MANUFACTURE_DATE_FROM,
MANUFACTURE_DATE_TO,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(
AHL_MR_EFFECTIVITY_DTLS_S.NEXTVAL,
l_new_mr_EFFECTIVITY_ID,
l_mr_effect_dtls_rec.EXCLUDE_FLAG,
l_mr_effect_dtls_rec.MANUFACTURER_ID,
l_mr_effect_dtls_rec.COUNTRY_CODE,
l_mr_effect_dtls_rec.SERIAL_NUMBER_FROM,
l_mr_effect_dtls_rec.SERIAL_NUMBER_TO,
l_mr_effect_dtls_rec.MANUFACTURE_DATE_FROM,
l_mr_effect_dtls_rec.MANUFACTURE_DATE_TO,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
l_mr_effect_dtls_rec.ATTRIBUTE_CATEGORY,
l_mr_effect_dtls_rec.ATTRIBUTE1,
l_mr_effect_dtls_rec.ATTRIBUTE2,
l_mr_effect_dtls_rec.ATTRIBUTE3,
l_mr_effect_dtls_rec.ATTRIBUTE4,
l_mr_effect_dtls_rec.ATTRIBUTE5,
l_mr_effect_dtls_rec.ATTRIBUTE6,
l_mr_effect_dtls_rec.ATTRIBUTE7,
l_mr_effect_dtls_rec.ATTRIBUTE8,
l_mr_effect_dtls_rec.ATTRIBUTE9,
l_mr_effect_dtls_rec.ATTRIBUTE10,
l_mr_effect_dtls_rec.ATTRIBUTE11,
l_mr_effect_dtls_rec.ATTRIBUTE12,
l_mr_effect_dtls_rec.ATTRIBUTE13,
l_mr_effect_dtls_rec.ATTRIBUTE14,
l_mr_effect_dtls_rec.ATTRIBUTE15
);
INSERT INTO AHL_MR_EFFECTIVITY_EXT_DTLS
(
MR_EFFECTIVITY_EXT_DTL_ID,
MR_EFFECTIVITY_ID,
EXCLUDE_FLAG,
EFFECT_EXT_DTL_REC_TYPE,
OWNER_ID,
LOCATION_TYPE_CODE,
CSI_EXT_ATTRIBUTE_CODE,
CSI_EXT_ATTRIBUTE_VALUE,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(
AHL_MR_EFFECTIVITY_EXT_DTLS_S.NEXTVAL,
l_new_mr_EFFECTIVITY_ID,
l_mr_effect_ext_dtls_rec.EXCLUDE_FLAG,
l_mr_effect_ext_dtls_rec.EFFECT_EXT_DTL_REC_TYPE,
l_mr_effect_ext_dtls_rec.OWNER_ID,
l_mr_effect_ext_dtls_rec.LOCATION_TYPE_CODE,
l_mr_effect_ext_dtls_rec.CSI_EXT_ATTRIBUTE_CODE,
l_mr_effect_ext_dtls_rec.CSI_EXT_ATTRIBUTE_VALUE,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
l_mr_effect_ext_dtls_rec.ATTRIBUTE_CATEGORY,
l_mr_effect_ext_dtls_rec.ATTRIBUTE1,
l_mr_effect_ext_dtls_rec.ATTRIBUTE2,
l_mr_effect_ext_dtls_rec.ATTRIBUTE3,
l_mr_effect_ext_dtls_rec.ATTRIBUTE4,
l_mr_effect_ext_dtls_rec.ATTRIBUTE5,
l_mr_effect_ext_dtls_rec.ATTRIBUTE6,
l_mr_effect_ext_dtls_rec.ATTRIBUTE7,
l_mr_effect_ext_dtls_rec.ATTRIBUTE8,
l_mr_effect_ext_dtls_rec.ATTRIBUTE9,
l_mr_effect_ext_dtls_rec.ATTRIBUTE10,
l_mr_effect_ext_dtls_rec.ATTRIBUTE11,
l_mr_effect_ext_dtls_rec.ATTRIBUTE12,
l_mr_effect_ext_dtls_rec.ATTRIBUTE13,
l_mr_effect_ext_dtls_rec.ATTRIBUTE14,
l_mr_effect_ext_dtls_rec.ATTRIBUTE15
);
INSERT INTO AHL_MR_RELATIONSHIPS(
MR_RELATIONSHIP_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_HEADER_ID,
RELATED_MR_HEADER_ID,
RELATIONSHIP_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
values(
AHL_MR_RELATIONSHIPS_S.NEXTVAL,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
l_rel_Rec.MR_HEADER_ID,
l_rel_Rec.RELATED_MR_HEADER_ID,
l_mr_relation_Rec.RELATIONSHIP_CODE,
l_mr_relation_Rec.ATTRIBUTE_CATEGORY,
l_mr_relation_Rec.ATTRIBUTE1,
l_mr_relation_Rec.ATTRIBUTE2,
l_mr_relation_Rec.ATTRIBUTE3,
l_mr_relation_Rec.ATTRIBUTE4,
l_mr_relation_Rec.ATTRIBUTE5,
l_mr_relation_Rec.ATTRIBUTE6,
l_mr_relation_Rec.ATTRIBUTE7,
l_mr_relation_Rec.ATTRIBUTE8,
l_mr_relation_Rec.ATTRIBUTE9,
l_mr_relation_Rec.ATTRIBUTE10,
l_mr_relation_Rec.ATTRIBUTE11,
l_mr_relation_Rec.ATTRIBUTE12,
l_mr_relation_Rec.ATTRIBUTE13,
l_mr_relation_Rec.ATTRIBUTE14,
l_mr_relation_Rec.ATTRIBUTE15);
INSERT INTO AHL_MR_VISIT_TYPES
(
MR_VISIT_TYPE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_HEADER_ID,
SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
MR_VISIT_TYPE_CODE
)
VALUES
(
AHL_MR_VISIT_TYPES_S.NEXTVAL,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
x_new_MR_HEADER_ID,
l_mrvisttype_rec.SECURITY_GROUP_ID,
l_mrvisttype_rec.ATTRIBUTE_CATEGORY,
l_mrvisttype_rec.ATTRIBUTE1,
l_mrvisttype_rec.ATTRIBUTE2,
l_mrvisttype_rec.ATTRIBUTE3,
l_mrvisttype_rec.ATTRIBUTE4,
l_mrvisttype_rec.ATTRIBUTE5,
l_mrvisttype_rec.ATTRIBUTE6,
l_mrvisttype_rec.ATTRIBUTE7,
l_mrvisttype_rec.ATTRIBUTE8,
l_mrvisttype_rec.ATTRIBUTE9,
l_mrvisttype_rec.ATTRIBUTE10,
l_mrvisttype_rec.ATTRIBUTE11,
l_mrvisttype_rec.ATTRIBUTE12,
l_mrvisttype_rec.ATTRIBUTE13,
l_mrvisttype_rec.ATTRIBUTE14,
l_mrvisttype_rec.ATTRIBUTE15,
l_mrvisttype_rec.MR_VISIT_TYPE_CODE
);
select PROGRAM_TYPE_CODE
into l_program_type_code
from ahl_mr_headers_b
where mr_header_id = p_source_mr_header_id;
INSERT INTO AHL_MR_LOOP_CHAIN_RELNS(
MR_RELATIONSHIP_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_HEADER_ID,
START_MR_RELATIONSHIP_ID,
SEQUENCE_NUMBER,
RELATIONSHIP_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
values(
l_mr_loop_chain_rel_id,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
l_start_loop_chain_rel_rec.MR_HEADER_ID,
l_start_mr_rel_id,
l_start_loop_chain_rel_rec.SEQUENCE_NUMBER,
l_start_loop_chain_rel_rec.RELATIONSHIP_CODE,
l_start_loop_chain_rel_rec.ATTRIBUTE_CATEGORY,
l_start_loop_chain_rel_rec.ATTRIBUTE1,
l_start_loop_chain_rel_rec.ATTRIBUTE2,
l_start_loop_chain_rel_rec.ATTRIBUTE3,
l_start_loop_chain_rel_rec.ATTRIBUTE4,
l_start_loop_chain_rel_rec.ATTRIBUTE5,
l_start_loop_chain_rel_rec.ATTRIBUTE6,
l_start_loop_chain_rel_rec.ATTRIBUTE7,
l_start_loop_chain_rel_rec.ATTRIBUTE8,
l_start_loop_chain_rel_rec.ATTRIBUTE9,
l_start_loop_chain_rel_rec.ATTRIBUTE10,
l_start_loop_chain_rel_rec.ATTRIBUTE11,
l_start_loop_chain_rel_rec.ATTRIBUTE12,
l_start_loop_chain_rel_rec.ATTRIBUTE13,
l_start_loop_chain_rel_rec.ATTRIBUTE14,
l_start_loop_chain_rel_rec.ATTRIBUTE15);
INSERT INTO AHL_MR_LOOP_CHAIN_RELNS(
MR_RELATIONSHIP_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MR_HEADER_ID,
START_MR_RELATIONSHIP_ID,
SEQUENCE_NUMBER,
RELATIONSHIP_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
values(
AHL_MR_LOOP_CHAIN_RELNS_S.NEXTVAL,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
l_loop_chain_rel_rec.MR_HEADER_ID,
l_loop_chain_rel_rec.start_mr_relationship_id,
l_loop_chain_rel_rec.SEQUENCE_NUMBER,
l_loop_chain_rel_rec.RELATIONSHIP_CODE,
l_loop_chain_rel_rec.ATTRIBUTE_CATEGORY,
l_loop_chain_rel_rec.ATTRIBUTE1,
l_loop_chain_rel_rec.ATTRIBUTE2,
l_loop_chain_rel_rec.ATTRIBUTE3,
l_loop_chain_rel_rec.ATTRIBUTE4,
l_loop_chain_rel_rec.ATTRIBUTE5,
l_loop_chain_rel_rec.ATTRIBUTE6,
l_loop_chain_rel_rec.ATTRIBUTE7,
l_loop_chain_rel_rec.ATTRIBUTE8,
l_loop_chain_rel_rec.ATTRIBUTE9,
l_loop_chain_rel_rec.ATTRIBUTE10,
l_loop_chain_rel_rec.ATTRIBUTE11,
l_loop_chain_rel_rec.ATTRIBUTE12,
l_loop_chain_rel_rec.ATTRIBUTE13,
l_loop_chain_rel_rec.ATTRIBUTE14,
l_loop_chain_rel_rec.ATTRIBUTE15);
SELECT MR_HEADER_ID,
TITLE,
VERSION_NUMBER,
MR_STATUS_CODE,
EFFECTIVE_FROM,
EFFECTIVE_TO,
TYPE_CODE
FROM AHL_MR_HEADERS_APP_V
WHERE MR_HEADER_ID=C_MR_HEADER_ID
and object_version_number=p_object_Version_number;
SELECT MR_HEADER_ID,
TITLE,
VERSION_NUMBER,
MR_STATUS_CODE,
EFFECTIVE_FROM,
EFFECTIVE_TO
FROM AHL_MR_HEADERS_APP_V
WHERE TITLE=C_TITLE
And version_number=c_version_number-1;
SELECT COUNT(*) INTO l_counter
FROM AHL_MR_ROUTES A
WHERE MR_HEADER_ID=l_activity_id
AND ROUTE_ID IN (SELECT ROUTE_ID FROM AHL_ROUTES_APP_V
WHERE ROUTE_ID=A.ROUTE_id
AND NVL(END_DATE_ACTIVE,SYSDATE+1)>SYSDATE
AND REVISION_STATUS_CODE='COMPLETE');
SELECT COUNT(*) INTO l_counter
FROM AHL_MR_HEADERS_APP_V
WHERE MR_HEADER_ID>l_activity_id
And Title=l_mr_rec.title
And Version_number >l_mr_rec.version_number;
UPDATE AHL_MR_HEADERS_B
SET MR_STATUS_CODE=l_upd_mr_status_code,
OBJECT_VERSION_number=object_version_number+1
WHERE MR_HEADER_ID=p_source_mr_header_id
And OBJECT_VERSION_NUMBER=p_object_Version_number;
UPDATE AHL_MR_HEADERS_B
SET MR_STATUS_CODE=L_UPD_MR_STATUS_CODE,
OBJECT_VERSION_number=OBJECT_VERSION_number+1
WHERE MR_HEADER_ID=p_source_mr_header_id
AND OBJECT_VERSION_NUMBER=p_object_Version_number;
SELECT MR_HEADER_ID,
VERSION_NUMBER,
EFFECTIVE_FROM,
TITLE,
MR_STATUS_CODE,
APPLICATION_USG_CODE
FROM AHL_MR_HEADERS_B
WHERE MR_HEADER_ID=C_MR_HEADER_ID;
SELECT MR_HEADER_ID,
VERSION_NUMBER,
EFFECTIVE_FROM,
TITLE,
MR_STATUS_CODE
FROM AHL_MR_HEADERS_B
WHERE TITLE=C_TITLE
AND VERSION_NUMBER=C_VERSION_NUMBER-1
AND APPLICATION_USG_CODE=C_APP_CODE;
select 'x'
from AHL_SB_POSITION_RULES
where mr_header_id = c_mr_header_id;
SELECT * FROM ahl_mr_loop_chain_relns WHERE
mr_relationship_id IN
(SELECT mr_relationship_id
FROM ahl_mr_loop_chain_relns
WHERE mr_header_id = p_mr_header_id_csr
AND mr_relationship_id <> start_mr_relationship_id
);
UPDATE AHL_MR_HEADERS_B
SET MR_STATUS_CODE=DECODE(MR_STATUS_CODE,'APPROVAL_PENDING','APPROVAL_REJECTED','TERMINATE_PENDING','COMPLETE')
WHERE MR_HEADER_ID=P_MR_HEADER_ID;
UPDATE AHL_MR_HEADERS_B
SET MR_STATUS_CODE=l_status,
EFFECTIVE_FROM=L_FR_DATE,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE MR_HEADER_ID=P_MR_HEADER_ID;
SELECT program_type_code
INTO l_program_type
FROM ahl_mr_headers_app_v
WHERE mr_header_id = P_MR_HEADER_ID;
AHL_SB_RULES_PVT.Update_Rules_For_MR
(
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_mr_header_id => p_mr_header_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE AHL_MR_HEADERS_B
SET MR_STATUS_CODE=l_status,
EFFECTIVE_TO=nvl(EFFECTIVE_TO,L_TO_DATE),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE MR_HEADER_ID=P_MR_HEADER_ID;
UPDATE AHL_MR_HEADERS_B
SET MR_STATUS_CODE=l_status,
EFFECTIVE_FROM=L_FR_DATE,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE MR_HEADER_ID=P_MR_HEADER_ID;
UPDATE AHL_MR_HEADERS_B
SET EFFECTIVE_TO=L_TO_DATE,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE MR_HEADER_ID=l_prev_mr_rec.MR_HEADER_ID;
is less than or equal to sysdate then delete the relationship */
IF TRUNC(SYSDATE) >= L_TO_DATE THEN
-- Delete the entire rel if its a starting MR
DELETE FROM AHL_MR_LOOP_CHAIN_RELNS
WHERE
start_mr_relationship_id IN
(SELECT mr_relationship_id
FROM ahl_mr_loop_chain_relns
WHERE mr_header_id = l_prev_mr_rec.MR_HEADER_ID
);
SELECT COUNT(sequence_number) INTO l_seq_count FROM ahl_mr_loop_chain_relns
WHERE sequence_number = l_mr_loop_chain_rel_rec.sequence_number
AND start_mr_relationship_id = l_mr_loop_chain_rel_rec.start_mr_relationship_id;
DELETE FROM ahl_mr_loop_chain_relns
WHERE start_mr_relationship_id = l_mr_loop_chain_rel_rec.start_mr_relationship_id;
DELETE FROM ahl_mr_loop_chain_relns
WHERE mr_relationship_id = l_mr_loop_chain_rel_rec.mr_relationship_id;
SELECT program_type_code
INTO l_prev_program_type
FROM ahl_mr_headers_app_v
WHERE mr_header_id = l_prev_mr_rec.MR_HEADER_ID;
SELECT program_type_code
INTO l_program_type
FROM ahl_mr_headers_app_v
WHERE mr_header_id = P_MR_HEADER_ID;
AHL_SB_RULES_PVT.Update_Rules_For_MR
(
p_api_version => 1.0,
p_commit => FND_API.G_FALSE,
p_mr_header_id => p_mr_header_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE AHL_MR_HEADERS_B
SET MR_STATUS_CODE=l_status,
EFFECTIVE_TO=NVL(EFFECTIVE_TO,l_to_date),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE MR_HEADER_ID=P_MR_HEADER_ID;
UPDATE AHL_MR_HEADERS_B
SET TERMINATION_REQUIRED_FLAG = 'Y'
WHERE MR_HEADER_ID=l_prev_mr_rec.MR_HEADER_ID;
SELECT count(*) into l_counter1
FROM AHL_MR_HEADERS_APP_V
Where mr_header_id=p_source_mr_header_id;
SELECT count(*) into l_counter2
FROM AHL_MR_HEADERS_APP_V
Where mr_header_id=p_source_mr_header_id
And mr_status_code='DRAFT' or mr_status_code='APPROVAL_REJECTED';