[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row
(
X_ROWID IN OUT NOCOPY VARCHAR2
,X_CONTACT_ASSIGNMENT_ID IN OUT NOCOPY NUMBER
,X_LAST_UPDATE_DATE IN DATE
,X_LAST_UPDATED_BY IN NUMBER
,X_CREATION_DATE IN DATE
,X_CREATED_BY IN NUMBER
,X_LAST_UPDATE_LOGIN IN NUMBER
,X_COMPANY_ID IN NUMBER
,X_COMPANY_SITE_ID IN NUMBER
,X_LEASE_ID IN NUMBER
,X_LEASE_CHANGE_ID IN NUMBER
,X_LOCATION_ID IN NUMBER
,X_STATUS IN VARCHAR2
,X_ATTRIBUTE_CATEGORY IN VARCHAR2
,X_ATTRIBUTE1 IN VARCHAR2
,X_ATTRIBUTE2 IN VARCHAR2
,X_ATTRIBUTE3 IN VARCHAR2
,X_ATTRIBUTE4 IN VARCHAR2
,X_ATTRIBUTE5 IN VARCHAR2
,X_ATTRIBUTE6 IN VARCHAR2
,X_ATTRIBUTE7 IN VARCHAR2
,X_ATTRIBUTE8 IN VARCHAR2
,X_ATTRIBUTE9 IN VARCHAR2
,X_ATTRIBUTE10 IN VARCHAR2
,X_ATTRIBUTE11 IN VARCHAR2
,X_ATTRIBUTE12 IN VARCHAR2
,X_ATTRIBUTE13 IN VARCHAR2
,X_ATTRIBUTE14 IN VARCHAR2
,X_ATTRIBUTE15 IN VARCHAR2
,x_org_id IN NUMBER
)
IS
CURSOR c IS
SELECT ROWID
FROM PN_CONTACT_ASSIGNMENTS_ALL
WHERE CONTACT_ASSIGNMENT_ID = X_CONTACT_ASSIGNMENT_ID;
SELECT org_id
FROM PN_COMPANIES_ALL
WHERE COMPANY_ID = X_COMPANY_ID;
SELECT PN_CONTACT_ASSIGNMENTS_S.NEXTVAL
INTO X_CONTACT_ASSIGNMENT_ID
FROM DUAL;
INSERT INTO PN_CONTACT_ASSIGNMENTS_ALL
(
CONTACT_ASSIGNMENT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,COMPANY_ID
,COMPANY_SITE_ID
,LEASE_ID
,LEASE_CHANGE_ID
,LOCATION_ID
,STATUS
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,org_id
)
VALUES
(
X_CONTACT_ASSIGNMENT_ID
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_CREATION_DATE
,X_CREATED_BY
,X_LAST_UPDATE_LOGIN
,X_COMPANY_ID
,X_COMPANY_SITE_ID
,X_LEASE_ID
,X_LEASE_CHANGE_ID
,X_LOCATION_ID
,X_STATUS
,X_ATTRIBUTE_CATEGORY
,X_ATTRIBUTE1
,X_ATTRIBUTE2
,X_ATTRIBUTE3
,X_ATTRIBUTE4
,X_ATTRIBUTE5
,X_ATTRIBUTE6
,X_ATTRIBUTE7
,X_ATTRIBUTE8
,X_ATTRIBUTE9
,X_ATTRIBUTE10
,X_ATTRIBUTE11
,X_ATTRIBUTE12
,X_ATTRIBUTE13
,X_ATTRIBUTE14
,X_ATTRIBUTE15
,l_org_id
);
END Insert_Row;
SELECT *
FROM pn_contact_assignments_all
WHERE contact_assignment_id = x_contact_assignment_id
FOR UPDATE OF contact_assignment_id NOWAIT;
PROCEDURE Update_Row
(
X_CONTACT_ASSIGNMENT_ID IN NUMBER
,X_LAST_UPDATE_DATE IN DATE
,X_LAST_UPDATED_BY IN NUMBER
,X_LAST_UPDATE_LOGIN IN NUMBER
,X_COMPANY_ID IN NUMBER
,X_COMPANY_SITE_ID IN NUMBER
,X_LEASE_ID IN NUMBER
,X_LEASE_CHANGE_ID IN NUMBER
,X_LOCATION_ID IN NUMBER
,X_STATUS IN VARCHAR2
,X_ATTRIBUTE_CATEGORY IN VARCHAR2
,X_ATTRIBUTE1 IN VARCHAR2
,X_ATTRIBUTE2 IN VARCHAR2
,X_ATTRIBUTE3 IN VARCHAR2
,X_ATTRIBUTE4 IN VARCHAR2
,X_ATTRIBUTE5 IN VARCHAR2
,X_ATTRIBUTE6 IN VARCHAR2
,X_ATTRIBUTE7 IN VARCHAR2
,X_ATTRIBUTE8 IN VARCHAR2
,X_ATTRIBUTE9 IN VARCHAR2
,X_ATTRIBUTE10 IN VARCHAR2
,X_ATTRIBUTE11 IN VARCHAR2
,X_ATTRIBUTE12 IN VARCHAR2
,X_ATTRIBUTE13 IN VARCHAR2
,X_ATTRIBUTE14 IN VARCHAR2
,X_ATTRIBUTE15 IN VARCHAR2
)
IS
l_leaseStatus VARCHAR2(2);
SELECT *
FROM PN_CONTACT_ASSIGNMENTS_ALL
WHERE CONTACT_ASSIGNMENT_ID = X_CONTACT_ASSIGNMENT_ID;
SELECT pn_contact_assign_hIStory_s.nextval
INTO l_contactHIStoryId
FROM DUAL;
INSERT INTO PN_CONTACT_ASSIGN_HISTORY
(
CONTACT_ASSIGN_HISTORY_ID
,CONTACT_ASSIGNMENT_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,COMPANY_ID
,COMPANY_SITE_ID
,LEASE_ID
,LEASE_CHANGE_ID
,NEW_LEASE_CHANGE_ID
,LOCATION_ID
,STATUS
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
)
VALUES
(
l_contactHIStoryId
,recInfoForHist.CONTACT_ASSIGNMENT_ID
,recInfoForHist.LAST_UPDATE_DATE
,recInfoForHist.LAST_UPDATED_BY
,recInfoForHist.CREATION_DATE
,recInfoForHist.CREATED_BY
,recInfoForHist.LAST_UPDATE_LOGIN
,recInfoForHist.COMPANY_ID
,recInfoForHist.COMPANY_SITE_ID
,recInfoForHist.LEASE_ID
,recInfoForHist.LEASE_CHANGE_ID
,X_LEASE_CHANGE_ID
,recInfoForHist.LOCATION_ID
,recInfoForHist.STATUS
,recInfoForHist.ATTRIBUTE_CATEGORY
,recInfoForHist.ATTRIBUTE1
,recInfoForHist.ATTRIBUTE2
,recInfoForHist.ATTRIBUTE3
,recInfoForHist.ATTRIBUTE4
,recInfoForHist.ATTRIBUTE5
,recInfoForHist.ATTRIBUTE6
,recInfoForHist.ATTRIBUTE7
,recInfoForHist.ATTRIBUTE8
,recInfoForHist.ATTRIBUTE9
,recInfoForHist.ATTRIBUTE10
,recInfoForHist.ATTRIBUTE11
,recInfoForHist.ATTRIBUTE12
,recInfoForHist.ATTRIBUTE13
,recInfoForHist.ATTRIBUTE14
,recInfoForHist.ATTRIBUTE15
);
UPDATE PN_CONTACT_ASSIGNMENTS_ALL
SET LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
,LAST_UPDATED_BY = X_LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
,COMPANY_ID = X_COMPANY_ID
,COMPANY_SITE_ID = X_COMPANY_SITE_ID
,LEASE_ID = X_LEASE_ID
,LEASE_CHANGE_ID = X_LEASE_CHANGE_ID
,LOCATION_ID = X_LOCATION_ID
,STATUS = X_STATUS
,ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY
,ATTRIBUTE1 = X_ATTRIBUTE1
,ATTRIBUTE2 = X_ATTRIBUTE2
,ATTRIBUTE3 = X_ATTRIBUTE3
,ATTRIBUTE4 = X_ATTRIBUTE4
,ATTRIBUTE5 = X_ATTRIBUTE5
,ATTRIBUTE6 = X_ATTRIBUTE6
,ATTRIBUTE7 = X_ATTRIBUTE7
,ATTRIBUTE8 = X_ATTRIBUTE8
,ATTRIBUTE9 = X_ATTRIBUTE9
,ATTRIBUTE10 = X_ATTRIBUTE10
,ATTRIBUTE11 = X_ATTRIBUTE11
,ATTRIBUTE12 = X_ATTRIBUTE12
,ATTRIBUTE13 = X_ATTRIBUTE13
,ATTRIBUTE14 = X_ATTRIBUTE14
,ATTRIBUTE15 = X_ATTRIBUTE15
WHERE CONTACT_ASSIGNMENT_ID = X_CONTACT_ASSIGNMENT_ID;
END Update_Row;
PROCEDURE Delete_Row
(
X_CONTACT_ASSIGNMENT_ID IN NUMBER
)
IS
BEGIN
DELETE
FROM PN_CONTACT_ASSIGNMENTS_ALL
WHERE CONTACT_ASSIGNMENT_ID = X_CONTACT_ASSIGNMENT_ID
AND STATUS <> 'F';
END Delete_Row;