[Home] [Help]
PACKAGE BODY: APPS.MTL_CYCLE_COUNT_CLASSES_PKG
Source
1 PACKAGE BODY MTL_CYCLE_COUNT_CLASSES_PKG as
2 /* $Header: INVADC3B.pls 120.1 2005/06/19 05:07:35 appldev $ */
3 --Added NOCOPY hint to X_Rowid IN OUT parameter to comply with
4 --GSCC standard File.Sql.39. BUg:4410902
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Abc_Class_Id NUMBER,
7 X_Cycle_Count_Header_Id NUMBER,
8 X_Organization_Id NUMBER,
9 X_Last_Update_Date DATE,
10 X_Last_Updated_By NUMBER,
11 X_Creation_Date DATE,
12 X_Created_By NUMBER,
13 X_Last_Update_Login NUMBER,
14 X_Num_Counts_Per_Year NUMBER,
15 X_Approval_Tolerance_Positive NUMBER,
16 X_Approval_Tolerance_Negative NUMBER,
17 X_Cost_Tolerance_Positive NUMBER,
18 X_Cost_Tolerance_Negative NUMBER,
19 X_Hit_Miss_Tolerance_Positive NUMBER,
20 X_Hit_Miss_Tolerance_Negative NUMBER,
21 X_Abc_Assignment_Group_Id NUMBER
22 ) IS
23 CURSOR C IS SELECT rowid FROM mtl_cycle_count_classes
24 WHERE ( (cycle_count_header_id = X_Cycle_Count_Header_Id)
25 or (cycle_count_header_id is NULL and X_Cycle_Count_Header_Id is NULL));
26
27 BEGIN
28
29 INSERT INTO mtl_cycle_count_classes(
30 abc_class_id,
31 cycle_count_header_id,
32 organization_id,
33 last_update_date,
34 last_updated_by,
35 creation_date,
36 created_by,
37 last_update_login,
38 num_counts_per_year,
39 approval_tolerance_positive,
40 approval_tolerance_negative,
41 cost_tolerance_positive,
42 cost_tolerance_negative,
43 hit_miss_tolerance_positive,
44 hit_miss_tolerance_negative,
45 abc_assignment_group_id
46 ) VALUES (
47 X_Abc_Class_Id,
48 X_Cycle_Count_Header_Id,
49 X_Organization_Id,
50 X_Last_Update_Date,
51 X_Last_Updated_By,
52 X_Creation_Date,
53 X_Created_By,
54 X_Last_Update_Login,
55 X_Num_Counts_Per_Year,
56 X_Approval_Tolerance_Positive,
57 X_Approval_Tolerance_Negative,
58 X_Cost_Tolerance_Positive,
59 X_Cost_Tolerance_Negative,
60 X_Hit_Miss_Tolerance_Positive,
61 X_Hit_Miss_Tolerance_Negative,
62 X_Abc_Assignment_Group_Id
63 );
64
65 OPEN C;
66 FETCH C INTO X_Rowid;
67 if (C%NOTFOUND) then
68 CLOSE C;
69 Raise NO_DATA_FOUND;
70 end if;
71 CLOSE C;
72 END Insert_Row;
73
74
75 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
76 X_Abc_Class_Id NUMBER,
77 X_Cycle_Count_Header_Id NUMBER,
78 X_Organization_Id NUMBER,
79 X_Num_Counts_Per_Year NUMBER,
80 X_Approval_Tolerance_Positive NUMBER,
81 X_Approval_Tolerance_Negative NUMBER,
82 X_Cost_Tolerance_Positive NUMBER,
83 X_Cost_Tolerance_Negative NUMBER,
84 X_Hit_Miss_Tolerance_Positive NUMBER,
85 X_Hit_Miss_Tolerance_Negative NUMBER,
86 X_Abc_Assignment_Group_Id NUMBER
87 ) IS
88 CURSOR C IS
89 SELECT *
90 FROM mtl_cycle_count_classes
91 WHERE rowid = X_Rowid
92 FOR UPDATE of Cycle_Count_Header_Id NOWAIT;
93 Recinfo C%ROWTYPE;
94
95
96 BEGIN
97 OPEN C;
98 FETCH C INTO Recinfo;
99 if (C%NOTFOUND) then
100 CLOSE C;
101 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
102 APP_EXCEPTION.Raise_Exception;
103 end if;
104 CLOSE C;
105 if (
106 (Recinfo.abc_class_id = X_Abc_Class_Id)
107 AND ( (Recinfo.cycle_count_header_id = X_Cycle_Count_Header_Id)
108 OR ( (Recinfo.cycle_count_header_id IS NULL)
109 AND (X_Cycle_Count_Header_Id IS NULL)))
110 AND (Recinfo.organization_id = X_Organization_Id)
111 AND ( (Recinfo.num_counts_per_year = X_Num_Counts_Per_Year)
112 OR ( (Recinfo.num_counts_per_year IS NULL)
113 AND (X_Num_Counts_Per_Year IS NULL)))
114 AND ( (Recinfo.approval_tolerance_positive = X_Approval_Tolerance_Positive)
115 OR ( (Recinfo.approval_tolerance_positive IS NULL)
116 AND (X_Approval_Tolerance_Positive IS NULL)))
117 AND ( (Recinfo.approval_tolerance_negative = X_Approval_Tolerance_Negative)
118 OR ( (Recinfo.approval_tolerance_negative IS NULL)
119 AND (X_Approval_Tolerance_Negative IS NULL)))
120 AND ( (Recinfo.cost_tolerance_positive = X_Cost_Tolerance_Positive)
121 OR ( (Recinfo.cost_tolerance_positive IS NULL)
122 AND (X_Cost_Tolerance_Positive IS NULL)))
123 AND ( (Recinfo.cost_tolerance_negative = X_Cost_Tolerance_Negative)
124 OR ( (Recinfo.cost_tolerance_negative IS NULL)
125 AND (X_Cost_Tolerance_Negative IS NULL)))
126 AND ( (Recinfo.hit_miss_tolerance_positive = X_Hit_Miss_Tolerance_Positive)
127 OR ( (Recinfo.hit_miss_tolerance_positive IS NULL)
128 AND (X_Hit_Miss_Tolerance_Positive IS NULL)))
129 AND ( (Recinfo.hit_miss_tolerance_negative = X_Hit_Miss_Tolerance_Negative)
130 OR ( (Recinfo.hit_miss_tolerance_negative IS NULL)
131 AND (X_Hit_Miss_Tolerance_Negative IS NULL)))
132 AND ( (Recinfo.abc_assignment_group_id = X_Abc_Assignment_Group_Id)
133 OR ( (Recinfo.abc_assignment_group_id IS NULL)
134 AND (X_Abc_Assignment_Group_Id IS NULL)))
135 ) then
136 return;
137 else
138 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
139 APP_EXCEPTION.Raise_Exception;
140 end if;
141 END Lock_Row;
142
143
144 PROCEDURE Update_Row(X_Rowid VARCHAR2,
145 X_Abc_Class_Id NUMBER,
146 X_Cycle_Count_Header_Id NUMBER,
147 X_Organization_Id NUMBER,
148 X_Last_Update_Date DATE,
149 X_Last_Updated_By NUMBER,
150 X_Last_Update_Login NUMBER,
151 X_Num_Counts_Per_Year NUMBER,
152 X_Approval_Tolerance_Positive NUMBER,
153 X_Approval_Tolerance_Negative NUMBER,
154 X_Cost_Tolerance_Positive NUMBER,
155 X_Cost_Tolerance_Negative NUMBER,
156 X_Hit_Miss_Tolerance_Positive NUMBER,
157 X_Hit_Miss_Tolerance_Negative NUMBER,
158 X_Abc_Assignment_Group_Id NUMBER
159 ) IS
160 BEGIN
161 UPDATE mtl_cycle_count_classes
162 SET
163 abc_class_id = X_Abc_Class_Id,
164 cycle_count_header_id = X_Cycle_Count_Header_Id,
165 organization_id = X_Organization_Id,
166 last_update_date = X_Last_Update_Date,
167 last_updated_by = X_Last_Updated_By,
168 last_update_login = X_Last_Update_Login,
169 num_counts_per_year = X_Num_Counts_Per_Year,
170 approval_tolerance_positive = X_Approval_Tolerance_Positive,
171 approval_tolerance_negative = X_Approval_Tolerance_Negative,
172 cost_tolerance_positive = X_Cost_Tolerance_Positive,
173 cost_tolerance_negative = X_Cost_Tolerance_Negative,
174 hit_miss_tolerance_positive = X_Hit_Miss_Tolerance_Positive,
175 hit_miss_tolerance_negative = X_Hit_Miss_Tolerance_Negative,
176 abc_assignment_group_id = X_Abc_Assignment_Group_Id
177 WHERE rowid = X_Rowid;
178
179 if (SQL%NOTFOUND) then
180 Raise NO_DATA_FOUND;
181 end if;
182 END Update_Row;
183 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
184 BEGIN
185 DELETE FROM mtl_cycle_count_classes
186 WHERE rowid = X_Rowid;
187
188 if (SQL%NOTFOUND) then
189 Raise NO_DATA_FOUND;
190 end if;
191 END Delete_Row;
192
193
194 END MTL_CYCLE_COUNT_CLASSES_PKG;