1 PACKAGE BODY PA_RESOURCE_PKG
2 -- $Header: PARCTBHB.pls 120.1 2005/08/19 16:49:47 mwasowic noship $
3 AS
4
5 /* THE INSERT_ROW1 PROCEDURE INSERTS INTO PA_RESOURCES TABLE.
6 THE INSERT_ROW2 PROCEDURE INSERTS INTO PA_RESOURCE_TXN_ATTRIBUTES TABLE.
7 THE INSERT_ROW3 PROCEDURE INSERTS INTO PA_RESOURCE_OU TABLE.*/
8
9 PROCEDURE INSERT_ROW1(
10 X_ROWID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
11 X_RESOURCE_ID IN PA_RESOURCES.RESOURCE_ID%TYPE,
12 X_NAME IN PA_RESOURCES.NAME%TYPE,
13 X_RESOURCE_TYPE_ID IN PA_RESOURCES.RESOURCE_TYPE_ID%TYPE,
14 X_JTF_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,
15 X_START_DATE_ACTIVE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
16 X_END_DATE_ACTIVE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE,
17 X_REQUEST_ID IN NUMBER,
18 X_PROGRAM_ID IN NUMBER,
19 X_PROGRAM_UPDATE_DATE IN DATE,
20 X_PROGRAM_APPLICATION_ID IN NUMBER,
21 X_UNIT_OF_MEASURE IN PA_RESOURCES.UNIT_OF_MEASURE%TYPE, --added for bug 2599790
22 X_ROLLUP_QUANTITY_FLAG IN PA_RESOURCES.ROLLUP_QUANTITY_FLAG%TYPE, --added for bug 3921534
23 X_TRACK_AS_LABOR_FLAG IN PA_RESOURCES.TRACK_AS_LABOR_FLAG%TYPE, --added for bug 3921534
24 X_LAST_UPDATE_BY IN NUMBER,
25 X_LAST_UPDATE_DATE IN DATE,
26 X_CREATION_DATE IN DATE,
27 X_CREATED_BY IN NUMBER,
28 X_LAST_UPDATE_LOGIN IN NUMBER ,
29 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
30 IS
31 l_check_dup_id VARCHAR2(1);
32 --l_resource_id NUMBER;
33 --cursor to select rowid of current insert
34 cursor C is
35 select rowid from pa_resources
36 where resource_id = x_resource_id;
37 BEGIN
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39
40 --actual insert into pa_resources table
41 insert into PA_RESOURCES (
42 resource_id,
43 name,
44 description, -- Added for bug 4318765
45 resource_type_id,
46 jtf_resource_id,
47 start_date_active,
48 end_date_active,
49 request_id,
50 program_id,
51 program_update_date,
52 program_application_id,
53 unit_of_measure, --added for bug 2599790
54 rollup_quantity_flag, -- added for bug 3921534
55 track_as_labor_flag, -- added for bug 3921534
56 last_updated_by,
57 last_update_date,
58 creation_date,
59 created_by,
60 last_update_login )
61 values (
62 X_RESOURCE_ID,
63 X_NAME,
64 X_NAME, -- Added for bug 4318765
65 X_RESOURCE_TYPE_ID,
66 X_JTF_RESOURCE_ID,
67 X_START_DATE_ACTIVE,
68 X_END_DATE_ACTIVE,
69 X_REQUEST_ID,
70 X_PROGRAM_ID,
71 X_PROGRAM_UPDATE_DATE,
72 X_PROGRAM_APPLICATION_ID,
73 X_UNIT_OF_MEASURE, --added for bug 2599790
74 X_ROLLUP_QUANTITY_FLAG, -- added for bug 3921534
75 X_TRACK_AS_LABOR_FLAG, -- added for bug 3921534
76 X_LAST_UPDATE_BY,
77 X_LAST_UPDATE_DATE,
78 X_CREATION_DATE,
79 X_CREATED_BY,
80 X_LAST_UPDATE_LOGIN );
81
82 --check if record was inserted
83 open c;
84 fetch c into X_ROWID;
85 if (c%notfound) then
86 close c;
87 raise no_data_found;
88 end if;
89 close c;
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN -- catch the exceptions here
92 -- Set the current program unit name in the error stack
93 -- PA_Error_Utils.Set_Error_Stack('PA_RESOURCE_PKG.Insert_Row1');
94 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
95
96 WHEN OTHERS THEN -- catch the exceptions here
97 -- Set the current program unit name in the error stack
98 -- PA_Error_Utils.Set_Error_Stack('PA_RESOURCE_PKG.Insert_Row1');
99 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
100 RAISE;
101 END INSERT_ROW1;
102
103 --begin of Insert_row2 procedure
104 PROCEDURE INSERT_ROW2 (
105 X_ROWID IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
106 X_RESOURCE_TXN_ATTRIBUTE_ID IN PA_RESOURCE_TXN_ATTRIBUTES.
107 RESOURCE_TXN_ATTRIBUTE_ID%TYPE,
108 X_RESOURCE_ID IN PA_RESOURCE_TXN_ATTRIBUTES.
109 RESOURCE_ID%TYPE,
110 X_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.
111 PERSON_ID%TYPE,
112 X_RESOURCE_FORMAT_ID IN PA_RESOURCE_TXN_ATTRIBUTES.
113 RESOURCE_FORMAT_ID%TYPE,
114 X_REQUEST_ID IN NUMBER,
115 X_PROGRAM_ID IN NUMBER,
116 X_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.
117 PARTY_ID%TYPE,
118 X_PROGRAM_UPDATE_DATE IN DATE,
119 X_PROGRAM_APPLICATION_ID IN NUMBER,
120 X_LAST_UPDATE_BY IN NUMBER,
121 X_LAST_UPDATE_DATE IN DATE,
122 X_CREATION_DATE IN DATE,
123 X_CREATED_BY IN NUMBER,
124 X_LAST_UPDATE_LOGIN IN NUMBER ,
125 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
126 IS
127 l_check_dup_id VARCHAR2(1);
128 --l_resource_txn_attribute_id NUBMER;
129 --cursor to select rowid of current insert
130 cursor c is
131 select rowid from pa_resource_txn_attributes
132 where resource_txn_attribute_id = resource_txn_attribute_id;
133
134 BEGIN
135 x_return_status := FND_API.G_RET_STS_SUCCESS;
136
137 --actual insert into pa_resource_txn_attribute
138 insert into PA_RESOURCE_TXN_ATTRIBUTES (
139 resource_txn_attribute_id
140 ,resource_id
141 ,person_id
142 ,resource_format_id
143 ,request_id
144 ,party_id
145 ,program_id
146 ,program_update_date
147 ,program_application_id
148 ,last_updated_by
149 ,last_update_date
150 ,creation_date
151 ,created_by
152 ,last_update_login)
153 values (
154 X_RESOURCE_TXN_ATTRIBUTE_ID,
155 X_RESOURCE_ID ,
156 X_PERSON_ID ,
157 X_RESOURCE_FORMAT_ID ,
158 X_REQUEST_ID,
159 X_PARTY_ID,
160 X_PROGRAM_ID ,
161 X_PROGRAM_UPDATE_DATE ,
162 X_PROGRAM_APPLICATION_ID ,
163 X_LAST_UPDATE_BY,
164 X_LAST_UPDATE_DATE ,
165 X_CREATION_DATE ,
166 X_CREATED_BY ,
167 X_LAST_UPDATE_LOGIN );
168
169 --check to see if record was inserted
170 open c;
171 fetch c into X_ROWID;
172 if (c%notfound) then
173 close c;
174 raise no_data_found;
175 end if;
176 close c;
177 EXCEPTION
178 WHEN NO_DATA_FOUND THEN -- catch the exceptions here
179 -- Set the current program unit name in the error stack
180 -- PA_Error_Utils.Set_Error_Stack('PA_RESOURCE_PKG.Insert_Row2');
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
182
183 WHEN OTHERS THEN -- catch the exceptions here
184 -- Set the current program unit name in the error stack
185 -- PA_Error_Utils.Set_Error_Stack('PA_RESOURCE_PKG.Insert_Row2');
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
187 RAISE;
188 END INSERT_ROW2;
189
190 END PA_RESOURCE_PKG;
[Home] [Help]