[Home] [Help]
PACKAGE BODY: APPS.EAM_ESTIMATE_ASSOCIATIONS_PKG
Source
1 PACKAGE BODY EAM_ESTIMATE_ASSOCIATIONS_PKG AS
2 /* $Header: EAMTESAB.pls 120.0.12010000.2 2008/12/24 02:31:52 devijay noship $ */
3 -- Start of Comments
4 -- Package name : EAM_ESTIMATE_ASSOCIATIONS_PKG
5 -- Purpose : Body of package EAM_ESTIMATE_ASSOCIATIONS_PKG
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'EAM_ESTIMATE_ASSOCIATIONS_PKG';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EAMTESAB.pls';
12
13 PROCEDURE INSERT_ROW(
14 px_ESTIMATE_ASSOCIATION_ID IN OUT NOCOPY NUMBER,
15 p_ORGANIZATION_ID NUMBER,
16 p_ESTIMATE_ID NUMBER,
17 p_CU_ID NUMBER,
18 p_CU_QTY NUMBER,
19 p_ACCT_CLASS_CODE VARCHAR2,
20 p_ACTIVITY_ID NUMBER,
21 p_ACTIVITY_QTY NUMBER,
22 p_DIFFICULTY_ID NUMBER,
23 p_RESOURCE_MULTIPLIER NUMBER,
24 p_CREATION_DATE DATE,
25 p_CREATED_BY NUMBER,
26 p_LAST_UPDATE_DATE DATE,
27 p_LAST_UPDATED_BY NUMBER,
28 p_LAST_UPDATE_LOGIN NUMBER
29 )
30 IS
31 CURSOR C IS SELECT EAM_ESTIMATE_ASSOCIATIONS_S.NEXTVAL FROM SYS.DUAL;
32 BEGIN
33 IF (px_ESTIMATE_ASSOCIATION_ID IS NULL) OR (px_ESTIMATE_ASSOCIATION_ID = FND_API.G_MISS_NUM) THEN
34 OPEN C;
35 FETCH C INTO px_ESTIMATE_ASSOCIATION_ID;
36 CLOSE C;
37 END IF;
38 INSERT INTO EAM_ESTIMATE_ASSOCIATIONS(
39 ESTIMATE_ASSOCIATION_ID,
40 ORGANIZATION_ID,
41 ESTIMATE_ID,
42 CU_ID,
43 CU_QTY,
44 ACCT_CLASS_CODE,
45 ACTIVITY_ID,
46 ACTIVITY_QTY,
47 DIFFICULTY_ID,
48 RESOURCE_MULTIPLIER,
49 CREATION_DATE,
50 CREATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN
54 ) VALUES (
55 px_ESTIMATE_ASSOCIATION_ID,
56 decode(p_ORGANIZATION_ID, FND_API.G_MISS_NUM, NULL, p_ORGANIZATION_ID),
57 decode(P_ESTIMATE_ID, FND_API.G_MISS_NUM, NULL, p_ESTIMATE_ID),
58 decode(p_CU_ID, FND_API.G_MISS_NUM, NULL, p_CU_ID),
59 decode(p_CU_QTY, FND_API.G_MISS_NUM, NULL, p_CU_QTY),
60 decode(p_ACCT_CLASS_CODE, FND_API.G_MISS_CHAR, NULL, p_ACCT_CLASS_CODE),
61 decode(p_ACTIVITY_ID, FND_API.G_MISS_NUM, NULL, p_ACTIVITY_ID),
62 decode(p_ACTIVITY_QTY, FND_API.G_MISS_NUM, NULL, p_ACTIVITY_QTY),
63 decode(p_DIFFICULTY_ID, FND_API.G_MISS_NUM, NULL, p_DIFFICULTY_ID),
64 decode(p_RESOURCE_MULTIPLIER, FND_API.G_MISS_NUM, NULL, p_RESOURCE_MULTIPLIER),
65 decode(p_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_CREATION_DATE),
66 decode(p_CREATED_BY, FND_API.G_MISS_NUM, NULL, p_CREATED_BY),
67 decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL), p_LAST_UPDATE_DATE),
68 decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATED_BY),
69 decode(p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL, p_LAST_UPDATE_LOGIN)
70 );
71 END INSERT_ROW;
72
73 PROCEDURE UPDATE_ROW(
74 p_ESTIMATE_ASSOCIATION_ID NUMBER,
75 p_ORGANIZATION_ID NUMBER,
76 p_ESTIMATE_ID NUMBER,
77 p_CU_ID NUMBER,
78 p_CU_QTY NUMBER,
79 p_ACCT_CLASS_CODE VARCHAR2,
80 p_ACTIVITY_ID NUMBER,
81 p_ACTIVITY_QTY NUMBER,
82 p_DIFFICULTY_ID NUMBER,
83 p_RESOURCE_MULTIPLIER NUMBER,
84 p_CREATION_DATE DATE,
85 p_CREATED_BY NUMBER,
86 p_LAST_UPDATE_DATE DATE,
87 p_LAST_UPDATED_BY NUMBER,
88 p_LAST_UPDATE_LOGIN NUMBER
89 )
90 IS
91 BEGIN
92 UPDATE EAM_ESTIMATE_ASSOCIATIONS
93 SET ORGANIZATION_ID = decode(p_ORGANIZATION_ID, FND_API.G_MISS_NUM, ORGANIZATION_ID, p_ORGANIZATION_ID),
94 ESTIMATE_ID = decode(p_ESTIMATE_ID, FND_API.G_MISS_NUM, ESTIMATE_ID, p_ESTIMATE_ID),
95 CU_ID = decode(p_CU_ID, FND_API.G_MISS_NUM, CU_ID, p_CU_ID),
96 CU_QTY = decode(p_CU_QTY, FND_API.G_MISS_NUM, CU_QTY, p_CU_QTY),
97 ACCT_CLASS_CODE = decode(p_ACCT_CLASS_CODE, FND_API.G_MISS_CHAR, ACCT_CLASS_CODE, p_ACCT_CLASS_CODE),
98 ACTIVITY_ID = decode(p_ACTIVITY_ID, FND_API.G_MISS_NUM, ACTIVITY_ID, p_ACTIVITY_ID),
99 ACTIVITY_QTY = decode(p_ACTIVITY_QTY, FND_API.G_MISS_NUM, ACTIVITY_QTY, p_ACTIVITY_QTY),
100 DIFFICULTY_ID = decode(p_DIFFICULTY_ID, FND_API.G_MISS_NUM, DIFFICULTY_ID, p_DIFFICULTY_ID),
101 RESOURCE_MULTIPLIER = decode(p_RESOURCE_MULTIPLIER, FND_API.G_MISS_NUM, RESOURCE_MULTIPLIER, p_RESOURCE_MULTIPLIER),
102 CREATION_DATE = decode(p_CREATION_DATE, FND_API.G_MISS_DATE, CREATION_DATE, p_CREATION_DATE),
103 CREATED_BY = decode(p_CREATED_BY, FND_API.G_MISS_NUM, CREATED_BY, p_CREATED_BY),
104 LAST_UPDATE_DATE = decode(p_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, LAST_UPDATE_DATE, p_LAST_UPDATE_DATE),
105 LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, FND_API.G_MISS_NUM, LAST_UPDATED_BY, p_LAST_UPDATED_BY),
106 LAST_UPDATE_LOGIN = decode(p_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, LAST_UPDATE_LOGIN, p_LAST_UPDATE_LOGIN)
107 WHERE ESTIMATE_ASSOCIATION_ID = p_ESTIMATE_ASSOCIATION_ID;
108
109 IF (SQL%NOTFOUND) THEN
110 RAISE NO_DATA_FOUND;
111 END IF;
112 END UPDATE_ROW;
113
114 PROCEDURE LOCK_ROW(
115 p_ESTIMATE_ASSOCIATION_ID NUMBER,
116 p_ORGANIZATION_ID NUMBER,
117 p_ESTIMATE_ID NUMBER,
118 p_CU_ID NUMBER,
119 p_CU_QTY NUMBER,
120 p_ACCT_CLASS_CODE VARCHAR2,
121 p_ACTIVITY_ID NUMBER,
122 p_ACTIVITY_QTY NUMBER,
123 p_DIFFICULTY_ID NUMBER,
124 p_RESOURCE_MULTIPLIER NUMBER,
125 p_CREATION_DATE DATE,
126 p_CREATED_BY NUMBER,
127 p_LAST_UPDATE_DATE DATE,
128 p_LAST_UPDATED_BY NUMBER,
129 p_LAST_UPDATE_LOGIN NUMBER
130 )
131 IS
132 CURSOR C IS
133 SELECT *
134 FROM EAM_ESTIMATE_ASSOCIATIONS
135 WHERE ESTIMATE_ASSOCIATION_ID = p_ESTIMATE_ASSOCIATION_ID
136 FOR UPDATE OF ESTIMATE_ASSOCIATION_ID NOWAIT;
137 Recinfo C%ROWTYPE;
138 BEGIN
139 OPEN C;
140 FETCH C INTO Recinfo;
141 IF (C%NOTFOUND) THEN
142 CLOSE C;
143 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
144 APP_EXCEPTION.RAISE_EXCEPTION;
145 END IF;
146 CLOSE C;
147
148 IF ((Recinfo.ESTIMATE_ASSOCIATION_ID = p_ESTIMATE_ASSOCIATION_ID)
149 AND ((Recinfo.ORGANIZATION_ID = p_ORGANIZATION_ID) OR ((Recinfo.ORGANIZATION_ID IS NULL) AND (p_ORGANIZATION_ID IS NULL)))
150 AND ((Recinfo.ESTIMATE_ID = p_ESTIMATE_ID) OR ((Recinfo.ESTIMATE_ID IS NULL) AND (p_ESTIMATE_ID IS NULL)))
151 AND ((Recinfo.CU_ID = p_CU_ID) OR ((Recinfo.CU_ID IS NULL) AND (p_CU_ID IS NULL)))
152 AND ((Recinfo.CU_QTY = p_CU_QTY) OR ((Recinfo.CU_QTY IS NULL) AND (p_CU_QTY IS NULL)))
153 AND ((Recinfo.ACCT_CLASS_CODE = p_ACCT_CLASS_CODE) OR ((Recinfo.ACCT_CLASS_CODE IS NULL) AND (p_ACCT_CLASS_CODE IS NULL)))
154 AND ((Recinfo.ACTIVITY_ID = p_ACTIVITY_ID) OR ((Recinfo.ACTIVITY_ID IS NULL) AND (p_ACTIVITY_ID IS NULL)))
155 AND ((Recinfo.ACTIVITY_QTY = p_ACTIVITY_QTY) OR ((Recinfo.ACTIVITY_QTY IS NULL) AND (p_ACTIVITY_QTY IS NULL)))
156 AND ((Recinfo.DIFFICULTY_ID = p_DIFFICULTY_ID) OR ((Recinfo.DIFFICULTY_ID IS NULL) AND (p_DIFFICULTY_ID IS NULL)))
157 AND ((Recinfo.RESOURCE_MULTIPLIER = p_RESOURCE_MULTIPLIER) OR ((Recinfo.RESOURCE_MULTIPLIER IS NULL) AND (p_RESOURCE_MULTIPLIER IS NULL)))
158 AND ((Recinfo.CREATION_DATE = p_CREATION_DATE) OR ((Recinfo.CREATION_DATE IS NULL) AND (p_CREATION_DATE IS NULL)))
159 AND ((Recinfo.CREATED_BY = p_CREATED_BY) OR ((Recinfo.CREATED_BY IS NULL) AND (p_CREATED_BY IS NULL)))
160 AND ((Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) OR ((Recinfo.LAST_UPDATE_DATE IS NULL) AND (p_LAST_UPDATE_DATE IS NULL)))
161 AND ((Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) OR ((Recinfo.LAST_UPDATED_BY IS NULL) AND (p_LAST_UPDATED_BY IS NULL)))
162 AND ((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR ((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND (p_LAST_UPDATE_LOGIN IS NULL)))
163 )
164 THEN
165 RETURN;
166 ELSE
167 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
168 APP_EXCEPTION.RAISE_EXCEPTION;
169 END IF;
170 END LOCK_ROW;
171
172 PROCEDURE DELETE_ROW(
173 p_ESTIMATE_ASSOCIATION_ID NUMBER
174 )
175 IS
176 BEGIN
177 DELETE FROM EAM_ESTIMATE_ASSOCIATIONS
178 WHERE ESTIMATE_ASSOCIATION_ID = p_ESTIMATE_ASSOCIATION_ID;
179
180 IF (SQL%NOTFOUND) THEN
181 RAISE NO_DATA_FOUND;
182 END IF;
183 END DELETE_ROW;
184
185 END EAM_ESTIMATE_ASSOCIATIONS_PKG;