1 package body PA_ALLOC_RESOURCES_PKG AS
2 /* $Header: PAXATRSB.pls 120.2 2005/08/19 16:18:57 ramurthy noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_RULE_ID in NUMBER,
6 X_MEMBER_TYPE in VARCHAR2,
7 X_RESOURCE_LIST_MEMBER_ID in NUMBER,
8 X_EXCLUDE_FLAG in VARCHAR2,
9 X_TARGET_EXPND_TYPE in VARCHAR2,
10 X_OFFSET_EXPND_TYPE in VARCHAR2,
11 X_RESOURCE_PERCENTAGE in NUMBER,
12 X_CREATED_BY in NUMBER,
13 X_CREATION_DATE in DATE,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from PA_ALLOC_RESOURCES
19 where RULE_ID = X_RULE_ID
20 and MEMBER_TYPE = X_MEMBER_TYPE
21 and RESOURCE_LIST_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID;
22
23 begin
24
25 insert into PA_ALLOC_RESOURCES (
26 RULE_ID,
27 MEMBER_TYPE,
28 RESOURCE_LIST_MEMBER_ID,
29 EXCLUDE_FLAG,
30 TARGET_EXPND_TYPE,
31 OFFSET_EXPND_TYPE,
32 RESOURCE_PERCENTAGE,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_RULE_ID,
40 X_MEMBER_TYPE,
41 X_RESOURCE_LIST_MEMBER_ID,
42 X_EXCLUDE_FLAG,
43 X_TARGET_EXPND_TYPE,
44 X_OFFSET_EXPND_TYPE,
45 X_RESOURCE_PERCENTAGE,
46 X_CREATION_DATE,
47 X_CREATED_BY,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_BY,
50 X_LAST_UPDATE_LOGIN
51 );
52
53 open c;
54 fetch c into X_ROWID;
55 if (c%notfound) then
56 close c;
57 raise no_data_found;
58 end if;
59 close c;
60
61 end INSERT_ROW;
62
63 procedure LOCK_ROW (
64 X_RULE_ID in NUMBER,
65 X_MEMBER_TYPE in VARCHAR2,
66 X_RESOURCE_LIST_MEMBER_ID in NUMBER,
67 X_EXCLUDE_FLAG in VARCHAR2,
68 X_TARGET_EXPND_TYPE in VARCHAR2,
69 X_OFFSET_EXPND_TYPE in VARCHAR2,
70 X_RESOURCE_PERCENTAGE in NUMBER
71 ) is
72 cursor c1 is select
73 EXCLUDE_FLAG,
74 TARGET_EXPND_TYPE,
75 OFFSET_EXPND_TYPE,
76 RESOURCE_PERCENTAGE
77 from PA_ALLOC_RESOURCES
78 where RULE_ID = X_RULE_ID
79 and MEMBER_TYPE = X_MEMBER_TYPE
80 and RESOURCE_LIST_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID
81 for update of RULE_ID nowait;
82 tlinfo c1%rowtype;
83
84 begin
85 open c1;
86 fetch c1 into tlinfo;
87 if (c1%notfound) then
88 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
89 app_exception.raise_exception;
90 close c1;
91 return;
92 end if;
93 close c1;
94
95 if ( (tlinfo.EXCLUDE_FLAG = X_EXCLUDE_FLAG)
96 AND ((tlinfo.TARGET_EXPND_TYPE = X_TARGET_EXPND_TYPE)
97 OR ((tlinfo.TARGET_EXPND_TYPE is null)
98 AND (X_TARGET_EXPND_TYPE is null)))
99 AND ((tlinfo.OFFSET_EXPND_TYPE = X_OFFSET_EXPND_TYPE)
100 OR ((tlinfo.OFFSET_EXPND_TYPE is null)
101 AND (X_OFFSET_EXPND_TYPE is null)))
102 AND ((tlinfo.RESOURCE_PERCENTAGE = X_RESOURCE_PERCENTAGE)
103 OR ((tlinfo.RESOURCE_PERCENTAGE is null)
104 AND (X_RESOURCE_PERCENTAGE is null)))
105 ) then
106 null;
107 else
108 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
109 app_exception.raise_exception;
110 end if;
111 return;
112 end LOCK_ROW;
113
114 procedure UPDATE_ROW (
115 X_ROWID in varchar2,
116 X_RULE_ID in NUMBER,
117 X_MEMBER_TYPE in VARCHAR2,
118 X_RESOURCE_LIST_MEMBER_ID in NUMBER,
119 X_EXCLUDE_FLAG in VARCHAR2,
120 X_TARGET_EXPND_TYPE in VARCHAR2,
121 X_OFFSET_EXPND_TYPE in VARCHAR2,
122 X_RESOURCE_PERCENTAGE in NUMBER,
123 X_LAST_UPDATE_DATE in DATE,
124 X_LAST_UPDATED_BY in NUMBER,
125 X_LAST_UPDATE_LOGIN in NUMBER
126 )
127 is
128
129 begin
130
131 update PA_ALLOC_RESOURCES set
132 RESOURCE_LIST_MEMBER_ID = X_RESOURCE_LIST_MEMBER_ID,
133 EXCLUDE_FLAG = X_EXCLUDE_FLAG,
134 TARGET_EXPND_TYPE = X_TARGET_EXPND_TYPE,
135 OFFSET_EXPND_TYPE = X_OFFSET_EXPND_TYPE,
136 RESOURCE_PERCENTAGE = X_RESOURCE_PERCENTAGE,
137 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
138 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
139 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
140 where ROWID = X_ROWID;
141
142 if (sql%notfound) then
143 raise no_data_found;
144 end if;
145 end UPDATE_ROW;
146
147 procedure DELETE_ROW (X_ROWID in varchar2
148 ) is
149 begin
150 delete from PA_ALLOC_RESOURCES
151 where ROWID = X_ROWID;
152 if (sql%notfound) then
153 raise no_data_found;
154 end if;
155 end DELETE_ROW;
156
157 end PA_ALLOC_RESOURCES_PKG;
[Home] [Help]