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