[Home] [Help]
PACKAGE BODY: APPS.QA_PC_RESULTS_REL_PKG
Source
1 PACKAGE BODY QA_PC_RESULTS_REL_PKG as
2 /* $Header: qapcresb.pls 120.0.12010000.2 2009/03/19 06:12:44 skolluku ship $ */
3 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
4 X_Parent_Plan_Id NUMBER,
5 X_Parent_Collection_Id NUMBER,
6 X_Parent_Occurrence NUMBER,
7 X_Child_Plan_Id NUMBER,
8 X_Child_Collection_Id NUMBER,
9 X_Child_Occurrence NUMBER,
10 X_Enabled_Flag NUMBER,
11 X_Last_Update_Date DATE,
12 X_Last_Updated_By NUMBER,
13 X_Creation_Date DATE,
14 X_Created_By NUMBER,
15 X_Last_Update_Login NUMBER,
16 X_Child_Txn_Header_Id NUMBER
17 ) IS
18 CURSOR C IS SELECT rowid FROM QA_PC_RESULTS_RELATIONSHIP
19 WHERE parent_plan_id = X_Parent_Plan_Id
20 AND parent_collection_id = X_Parent_Collection_Id
21 AND parent_occurrence = X_Parent_Occurrence
22 AND child_plan_id = X_Child_Plan_Id
23 AND child_collection_id = X_Child_Collection_Id
24 AND child_occurrence = X_Child_Occurrence;
25 BEGIN
26
27 --
28 -- Bug 8273401
29 -- Checking if a record for this combination already exists.
30 -- If it does, then do not insert one more row and exit.
31 -- This would avoid duplicate entries from being entered into
32 -- the qa_pc_results_relationship table.
33 -- skolluku
34 --
35 OPEN C;
36 FETCH C INTO X_Rowid;
37 if (C%FOUND) then
38 CLOSE C;
39 return;
40 end if;
41 CLOSE C;
42
43 INSERT INTO QA_PC_RESULTS_RELATIONSHIP(
44 parent_plan_id,
45 parent_collection_id,
46 parent_occurrence,
47 child_plan_id,
48 child_collection_id,
49 child_occurrence,
50 enabled_flag,
51 last_update_date,
52 last_updated_by,
53 creation_date,
54 created_by,
55 last_update_login,
56 child_txn_header_id
57 )VALUES(
58 X_Parent_Plan_Id,
59 X_Parent_Collection_Id,
60 X_Parent_Occurrence,
61 X_Child_Plan_Id,
62 X_Child_Collection_Id,
63 X_Child_Occurrence,
64 X_Enabled_Flag,
65 X_Last_Update_Date,
66 X_Last_Updated_By,
67 X_Creation_Date,
68 X_Created_By,
69 X_Last_Update_Login,
70 X_Child_Txn_Header_Id
71 );
72 OPEN C;
73 FETCH C INTO X_Rowid;
74 if (C%NOTFOUND) then
75 CLOSE C;
76 Raise NO_DATA_FOUND;
77 end if;
78 CLOSE C;
79 END Insert_Row;
80
81
82 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
83 X_Parent_Plan_Id NUMBER,
84 X_Parent_Collection_Id NUMBER,
85 X_Parent_Occurrence NUMBER,
86 X_Child_Plan_Id NUMBER,
87 X_Child_Collection_Id NUMBER,
88 X_Child_Occurrence NUMBER,
89 X_Enabled_Flag NUMBER,
90 X_Last_Update_Date DATE,
91 X_Last_Updated_By NUMBER,
92 X_Creation_Date DATE,
93 X_Created_By NUMBER,
94 X_Child_Txn_Header_Id NUMBER
95
96 ) IS
97 CURSOR C IS
98 SELECT *
99 FROM QA_PC_RESULTS_RELATIONSHIP
100 WHERE rowid = X_Rowid
101 FOR UPDATE of Child_Occurrence NOWAIT;
102 Recinfo C%ROWTYPE;
103
104 BEGIN
105 OPEN C;
106 FETCH C INTO Recinfo;
107 if (C%NOTFOUND) then
108 CLOSE C;
109 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
110 APP_EXCEPTION.Raise_Exception;
111 end if;
112 CLOSE C;
113 if (
114 (Recinfo.parent_plan_id = X_Parent_Plan_Id)
115 AND (Recinfo.parent_collection_id = X_Parent_Collection_Id)
116 AND (Recinfo.parent_occurrence = X_Parent_Occurrence)
117 AND (Recinfo.child_plan_id = X_Child_Plan_Id)
118 AND (Recinfo.child_collection_id = X_Child_Collection_Id)
119 AND (Recinfo.child_occurrence = X_Child_Occurrence)
120 AND (Recinfo.enabled_flag = X_Enabled_Flag)
121 AND (Recinfo.last_update_date = X_Last_Update_Date)
122 AND (Recinfo.last_updated_by = X_Last_Updated_By)
123 AND (Recinfo.creation_date = X_Creation_Date)
124 AND (Recinfo.created_by = X_Created_By)
125 AND (Recinfo.child_txn_header_id = X_Child_Txn_Header_Id)
126 ) then
127 return;
128 else
129 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
130 APP_EXCEPTION.Raise_Exception;
131 end if;
132
133 END Lock_Row;
134
135 PROCEDURE Update_Row(X_Rowid VARCHAR2,
136 X_Parent_Plan_Id NUMBER,
137 X_Parent_Collection_Id NUMBER,
138 X_Parent_Occurrence NUMBER,
139 X_Child_Plan_Id NUMBER,
140 X_Child_Collection_Id NUMBER,
141 X_Child_Occurrence NUMBER,
142 X_Enabled_Flag NUMBER,
143 X_Last_Update_Date DATE,
144 X_Last_Updated_By NUMBER,
145 X_Creation_Date DATE,
146 X_Created_By NUMBER,
147 X_Last_Update_Login NUMBER,
148 X_Child_Txn_Header_Id NUMBER
149
150 ) IS
151 BEGIN
152 UPDATE QA_PC_RESULTS_RELATIONSHIP
153 SET
154 parent_plan_id = X_Parent_Plan_Id,
155 parent_collection_id = X_Parent_Collection_Id,
156 parent_occurrence = X_Parent_Occurrence,
157 child_plan_id = X_Child_Plan_Id,
158 child_collection_id = X_Child_Collection_Id,
159 child_occurrence = X_Child_Occurrence,
160 enabled_flag = X_Enabled_Flag,
161 last_update_date = X_Last_Update_Date,
162 last_updated_by = X_Last_Updated_By,
163 creation_date = X_Creation_Date,
164 created_by = X_Created_By,
165 last_update_login = X_Last_Update_Login,
166 child_txn_header_id = X_Child_Txn_Header_Id
167 WHERE rowid = X_Rowid;
168
169 if (SQL%NOTFOUND) then
170 Raise NO_DATA_FOUND;
171 end if;
172 END Update_Row;
173
174 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
175 BEGIN
176 DELETE FROM QA_PC_RESULTS_RELATIONSHIP
177 WHERE rowid = X_Rowid;
178
179 if (SQL%NOTFOUND) then
180 Raise NO_DATA_FOUND;
181 end if;
182 END Delete_Row;
183
184
185 END QA_PC_RESULTS_REL_PKG;