[Home] [Help]
PACKAGE BODY: APPS.QA_SEQ_AUDIT_PKG
Source
1 PACKAGE BODY QA_SEQ_AUDIT_PKG as
2 /* $Header: qaseqadb.pls 115.0 2003/08/26 13:50:31 rponnusa noship $ */
3
4 PROCEDURE Insert_Row(P_Rowid IN OUT NOCOPY VARCHAR2,
5 P_Plan_Id NUMBER,
6 P_Collection_Id NUMBER,
7 P_Occurrence NUMBER,
8 P_Char_Id NUMBER,
9 P_Txn_Header_Id NUMBER,
10 P_Sequence_Value VARCHAR2,
11 P_User_Id NUMBER,
12 P_Source_Code VARCHAR2,
13 P_Source_Id NUMBER,
14 P_Audit_Type VARCHAR2,
15 P_Audit_Date DATE,
16 P_Last_Update_Date DATE,
17 P_Last_Updated_By NUMBER,
18 P_Creation_Date DATE,
19 P_Created_By NUMBER,
20 P_Last_Update_Login NUMBER
21 ) IS
22
23 CURSOR C IS SELECT rowid FROM QA_SEQ_AUDIT_HISTORY
24 WHERE char_id = P_Char_Id
25 AND plan_id = P_Plan_Id
26 AND collection_id = P_Collection_Id
27 AND occurrence = P_Occurrence;
28
29 BEGIN
30 INSERT INTO QA_SEQ_AUDIT_HISTORY(
31 plan_id,
32 collection_id,
33 occurrence,
34 char_id,
35 txn_header_id,
36 sequence_value,
37 user_id,
38 source_code,
39 source_id,
40 audit_type,
41 audit_date,
42 last_update_date,
43 last_updated_by,
44 creation_date,
45 created_by,
46 last_update_login
47 )VALUES(
48 P_Plan_Id,
49 P_Collection_Id,
50 P_Occurrence,
51 P_Char_Id,
52 P_Txn_Header_Id,
53 P_Sequence_Value,
54 P_User_Id,
55 P_Source_Code,
56 P_Source_Id,
57 P_Audit_Type,
58 P_Audit_Date,
59 P_Last_Update_Date,
60 P_Last_Updated_By,
61 P_Creation_Date,
62 P_Created_By,
63 P_Last_Update_Login
64 );
65 OPEN C;
66 FETCH C INTO P_Rowid;
67
68 IF (C%NOTFOUND) THEN
69 CLOSE C;
70 Raise NO_DATA_FOUND;
71 END IF;
72
73 CLOSE C;
74
75 END Insert_Row;
76
77 PROCEDURE Lock_Row (P_Rowid VARCHAR2,
78 P_Plan_Id NUMBER,
79 P_Collection_Id NUMBER,
80 P_Occurrence NUMBER,
81 P_Char_Id NUMBER,
82 P_Txn_Header_Id NUMBER,
83 P_Sequence_Value VARCHAR2,
84 P_User_Id NUMBER,
85 P_Source_Code VARCHAR2,
86 P_Source_Id NUMBER,
87 P_Audit_Type VARCHAR2,
88 P_Audit_Date DATE,
89 P_Last_Update_Date DATE,
90 P_Last_Updated_By NUMBER,
91 P_Creation_Date DATE,
92 P_Created_By NUMBER,
93 P_Last_Update_Login NUMBER
94 ) IS
95
96 CURSOR C IS
97 SELECT *
98 FROM QA_SEQ_AUDIT_HISTORY
99 WHERE rowid = P_Rowid
100 FOR UPDATE of char_id NOWAIT;
101
102 Recinfo C%ROWTYPE;
103
104 BEGIN
105 OPEN C;
106 FETCH C INTO Recinfo;
107
108 IF (C%NOTFOUND) then
109 CLOSE C;
110 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
111 APP_EXCEPTION.Raise_Exception;
112 END IF;
113
114 CLOSE C;
115
116 IF (
117 (Recinfo.plan_id = P_Plan_Id)
118 AND (Recinfo.collection_id = P_Collection_Id)
119 AND (Recinfo.occurrence = P_Occurrence)
120 AND (Recinfo.char_id = P_Char_Id)
121 AND (Recinfo.txn_header_id = P_Txn_Header_Id
122 OR (Recinfo.txn_header_id IS NULL
123 AND P_Txn_Header_Id IS NULL))
124 AND (Recinfo.sequence_value = P_Sequence_Value)
125 AND (Recinfo.user_id = P_User_Id)
126 AND (Recinfo.source_code = P_Source_Code
127 OR (Recinfo.source_code IS NULL
128 AND P_Source_Code IS NULL))
129 AND (Recinfo.source_id = P_Source_Id
130 OR (Recinfo.source_id IS NULL
131 AND P_Source_Id IS NULL))
132 AND (Recinfo.audit_type = P_Audit_Type)
133 AND (Recinfo.audit_date = P_Audit_Date)
134 AND (Recinfo.last_update_date = P_Last_Update_Date)
135 AND (Recinfo.last_updated_by = P_Last_Updated_By)
136 AND (Recinfo.creation_date = P_Creation_Date)
137 AND (Recinfo.created_by = P_Created_By)
138 ) THEN
139 return;
140 ELSE
141 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
142 APP_EXCEPTION.Raise_Exception;
143 END IF;
144
145 END Lock_Row;
146
147 PROCEDURE Update_Row(P_Rowid VARCHAR2,
148 P_Plan_Id NUMBER,
149 P_Collection_Id NUMBER,
150 P_Occurrence NUMBER,
151 P_Char_Id NUMBER,
152 P_Txn_Header_Id NUMBER,
153 P_Sequence_Value VARCHAR2,
154 P_User_Id NUMBER,
155 P_Source_Code VARCHAR2,
156 P_Source_Id NUMBER,
157 P_Audit_Type VARCHAR2,
158 P_Audit_Date DATE,
159 P_Last_Update_Date DATE,
160 P_Last_Updated_By NUMBER,
161 P_Creation_Date DATE,
162 P_Created_By NUMBER,
163 P_Last_Update_Login NUMBER
164 )IS
165 BEGIN
166 UPDATE QA_SEQ_AUDIT_HISTORY
167 SET
168 plan_id = P_Plan_Id,
169 collection_id = P_Collection_Id,
170 occurrence = P_Occurrence,
171 char_id = P_Char_Id,
172 txn_header_id = P_Txn_Header_Id,
173 sequence_value = P_Sequence_Value,
174 user_id = P_User_Id,
175 source_code = P_Source_Code,
176 source_id = P_Source_Id,
177 audit_type = P_Audit_Type,
178 audit_date = P_Audit_Date,
179 last_update_date = P_Last_Update_Date,
180 last_updated_by = P_Last_Updated_By,
181 creation_date = P_Creation_Date,
182 created_by = P_Created_By,
183 last_update_login = P_Last_Update_Login
184 WHERE rowid = P_Rowid;
185
186 IF (SQL%NOTFOUND) THEN
187 Raise NO_DATA_FOUND;
188 END IF;
189
190 END Update_Row;
191
192 PROCEDURE Delete_Row(P_Rowid VARCHAR2) IS
193 BEGIN
194 DELETE FROM QA_SEQ_AUDIT_HISTORY
195 WHERE rowid = P_Rowid;
196
197 IF (SQL%NOTFOUND) THEN
198 Raise NO_DATA_FOUND;
199 END IF;
200
201 END Delete_Row;
202
203 END QA_SEQ_AUDIT_PKG;