1 package body PA_ALLOC_TARGET_LINES_PKG AS
2 /* $Header: PAXATTLB.pls 120.2 2005/08/19 16:19:08 ramurthy noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_RULE_ID in NUMBER,
6 X_LINE_NUM in NUMBER,
7 X_PROJECT_ORG_ID in NUMBER,
8 X_TASK_ORG_ID in NUMBER,
9 X_PROJECT_TYPE in VARCHAR2,
10 X_CLASS_CATEGORY in VARCHAR2,
11 X_CLASS_CODE in VARCHAR2,
12 X_SERVICE_TYPE in VARCHAR2,
13 X_PROJECT_ID in NUMBER,
14 X_TASK_ID in NUMBER,
15 X_EXCLUDE_FLAG in VARCHAR2,
16 X_BILLABLE_ONLY_FLAG in VARCHAR2,
17 X_LINE_PERCENT in NUMBER,
18 X_CREATED_BY in NUMBER,
19 X_CREATION_DATE in DATE,
20 X_LAST_UPDATE_DATE in DATE,
21 X_LAST_UPDATED_BY in NUMBER,
22 X_LAST_UPDATE_LOGIN in NUMBER
23 ) is
24 cursor C is select ROWID from PA_ALLOC_TARGET_LINES
25 where RULE_ID = X_RULE_ID
26 and LINE_NUM = X_LINE_NUM;
27 begin
28 insert into PA_ALLOC_TARGET_LINES (
29 RULE_ID,
30 LINE_NUM,
31 PROJECT_ORG_ID,
32 TASK_ORG_ID,
33 PROJECT_TYPE,
34 CLASS_CATEGORY,
35 CLASS_CODE,
36 SERVICE_TYPE,
37 PROJECT_ID,
38 TASK_ID,
39 EXCLUDE_FLAG,
40 BILLABLE_ONLY_FLAG,
41 LINE_PERCENT,
42 CREATED_BY,
43 CREATION_DATE,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN
47 ) values (
48 X_RULE_ID,
49 X_LINE_NUM,
50 X_PROJECT_ORG_ID,
51 X_TASK_ORG_ID,
52 X_PROJECT_TYPE,
53 X_CLASS_CATEGORY,
54 X_CLASS_CODE,
55 X_SERVICE_TYPE,
56 X_PROJECT_ID,
57 X_TASK_ID,
58 X_EXCLUDE_FLAG,
59 X_BILLABLE_ONLY_FLAG,
60 X_LINE_PERCENT,
61 X_CREATED_BY,
62 X_CREATION_DATE,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATED_BY,
65 X_LAST_UPDATE_LOGIN
66 );
67
68 open c;
69 fetch c into X_ROWID;
70 if (c%notfound) then
71 close c;
72 raise no_data_found;
73 end if;
74 close c;
75
76 end INSERT_ROW;
77
78 procedure LOCK_ROW (
79 X_RULE_ID in NUMBER,
80 X_LINE_NUM in NUMBER,
81 X_PROJECT_ORG_ID in NUMBER,
82 X_TASK_ORG_ID in NUMBER,
83 X_PROJECT_TYPE in VARCHAR2,
84 X_CLASS_CATEGORY in VARCHAR2,
85 X_CLASS_CODE in VARCHAR2,
86 X_SERVICE_TYPE in VARCHAR2,
87 X_PROJECT_ID in NUMBER,
88 X_TASK_ID in NUMBER,
89 X_EXCLUDE_FLAG in VARCHAR2,
90 X_BILLABLE_ONLY_FLAG in VARCHAR2,
91 X_LINE_PERCENT in NUMBER
92 ) is
93 cursor c1 is select
94 PROJECT_ORG_ID,
95 TASK_ORG_ID,
96 PROJECT_TYPE,
97 CLASS_CATEGORY,
98 CLASS_CODE,
99 SERVICE_TYPE,
100 PROJECT_ID,
101 TASK_ID,
102 EXCLUDE_FLAG,
103 BILLABLE_ONLY_FLAG,
104 LINE_PERCENT
105 from PA_ALLOC_TARGET_LINES
106 where RULE_ID = X_RULE_ID
107 and LINE_NUM = X_LINE_NUM
108 for update of RULE_ID nowait;
109 tlinfo c1%rowtype;
110
111 begin
112 open c1;
113 fetch c1 into tlinfo;
114 if (c1%notfound) then
115 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
116 app_exception.raise_exception;
117 close c1;
118 return;
119 end if;
120 close c1;
121
122 if ( ((tlinfo.PROJECT_ORG_ID = X_PROJECT_ORG_ID)
123 OR ((tlinfo.PROJECT_ORG_ID is null)
124 AND (X_PROJECT_ORG_ID is null)))
125 AND ((tlinfo.TASK_ORG_ID = X_TASK_ORG_ID)
126 OR ((tlinfo.TASK_ORG_ID is null)
127 AND (X_TASK_ORG_ID is null)))
128 AND ((tlinfo.PROJECT_TYPE = X_PROJECT_TYPE)
129 OR ((tlinfo.PROJECT_TYPE is null)
130 AND (X_PROJECT_TYPE is null)))
131 AND ((tlinfo.CLASS_CATEGORY = X_CLASS_CATEGORY)
132 OR ((tlinfo.CLASS_CATEGORY is null)
133 AND (X_CLASS_CATEGORY is null)))
134 AND ((tlinfo.CLASS_CODE = X_CLASS_CODE)
135 OR ((tlinfo.CLASS_CODE is null)
136 AND (X_CLASS_CODE is null)))
137 AND ((tlinfo.SERVICE_TYPE = X_SERVICE_TYPE)
138 OR ((tlinfo.SERVICE_TYPE is null)
139 AND (X_SERVICE_TYPE is null)))
140 AND ((tlinfo.PROJECT_ID = X_PROJECT_ID)
141 OR ((tlinfo.PROJECT_ID is null)
142 AND (X_PROJECT_ID is null)))
143 AND ((tlinfo.TASK_ID = X_TASK_ID)
144 OR ((tlinfo.TASK_ID is null)
145 AND (X_TASK_ID is null)))
146 AND (tlinfo.EXCLUDE_FLAG = X_EXCLUDE_FLAG)
147 AND ((tlinfo.BILLABLE_ONLY_FLAG = X_BILLABLE_ONLY_FLAG)
148 OR ((tlinfo.BILLABLE_ONLY_FLAG is null)
149 AND (X_BILLABLE_ONLY_FLAG is null)))
150 AND ((tlinfo.LINE_PERCENT = X_LINE_PERCENT)
151 OR ((tlinfo.LINE_PERCENT is null)
152 AND (X_LINE_PERCENT is null)))
153 ) then
154 null;
155 else
156 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
157 app_exception.raise_exception;
158 end if;
159 return;
160 end LOCK_ROW;
161
162 procedure UPDATE_ROW (
163 X_ROWID in VARCHAR2,
164 X_RULE_ID in NUMBER,
165 X_LINE_NUM in NUMBER,
166 X_PROJECT_ORG_ID in NUMBER,
167 X_TASK_ORG_ID in NUMBER,
168 X_PROJECT_TYPE in VARCHAR2,
169 X_CLASS_CATEGORY in VARCHAR2,
170 X_CLASS_CODE in VARCHAR2,
171 X_SERVICE_TYPE in VARCHAR2,
172 X_PROJECT_ID in NUMBER,
173 X_TASK_ID in NUMBER,
174 X_EXCLUDE_FLAG in VARCHAR2,
175 X_BILLABLE_ONLY_FLAG in VARCHAR2,
176 X_LINE_PERCENT in NUMBER,
177 X_LAST_UPDATE_DATE in DATE,
178 X_LAST_UPDATED_BY in NUMBER,
179 X_LAST_UPDATE_LOGIN in NUMBER
180 ) is
181
182 begin
183
184 update PA_ALLOC_TARGET_LINES set
185 LINE_NUM = X_LINE_NUM,
186 PROJECT_ORG_ID = X_PROJECT_ORG_ID,
187 TASK_ORG_ID = X_TASK_ORG_ID,
188 PROJECT_TYPE = X_PROJECT_TYPE,
189 CLASS_CATEGORY = X_CLASS_CATEGORY,
190 CLASS_CODE = X_CLASS_CODE,
191 SERVICE_TYPE = X_SERVICE_TYPE,
192 PROJECT_ID = X_PROJECT_ID,
193 TASK_ID = X_TASK_ID,
194 EXCLUDE_FLAG = X_EXCLUDE_FLAG,
195 BILLABLE_ONLY_FLAG = X_BILLABLE_ONLY_FLAG,
196 LINE_PERCENT = X_LINE_PERCENT,
197 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
198 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
200 where ROWID = X_ROWID;
201 --RULE_ID = X_RULE_ID
202 -- and LINE_NUM = X_LINE_NUM
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207 end UPDATE_ROW;
208
209 procedure DELETE_ROW (X_ROWID in VARCHAR2
210 --X_RULE_ID in NUMBER,
211 --X_LINE_NUM in NUMBER
212 ) is
213 begin
214 delete from PA_ALLOC_TARGET_LINES
215 where ROWID = X_ROWID;
216 --RULE_ID = X_RULE_ID
217 -- and LINE_NUM = X_LINE_NUM;
218 if (sql%notfound) then
219 raise no_data_found;
220 end if;
221 end DELETE_ROW;
222 end PA_ALLOC_TARGET_LINES_PKG;
[Home] [Help]