1 PACKAGE BODY PA_PM_CONTROL_RULES_PKG as
2 /* $Header: PAPMPCRB.pls 120.2 2005/08/23 22:39:02 avaithia noship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
5 X_Control_Rule_id IN NUMBER,
6 X_Pm_product_code IN VARCHAR2,
7 X_Field_value_code IN VARCHAR2,
8 X_Start_Date IN DATE,
9 X_End_Date IN DATE,
10 X_Creation_Date IN DATE,
11 X_Created_By IN NUMBER,
12 X_Last_Update_Date IN DATE,
13 X_Last_Updated_By IN NUMBER,
14 X_Last_Update_Login IN NUMBER
15 ) IS
16
17 CURSOR C IS SELECT rowid FROM pa_pm_product_control_rules
18 WHERE control_rule_id = X_Control_Rule_id
19 AND pm_product_code = X_Pm_product_code ;
20 BEGIN
21
22 INSERT INTO pa_pm_product_control_rules(
23 control_rule_id,
24 pm_product_code,
25 field_value_code,
26 start_date_active,
27 end_date_active,
28 creation_date,
29 created_by,
30 last_update_date,
31 last_updated_by,
32 last_update_login
33 ) VALUES
34 (
35 X_Control_Rule_id,
36 X_Pm_product_code,
37 X_Field_value_code,
38 X_Start_Date,
39 X_End_Date,
40 X_Creation_Date,
41 X_Created_By,
42 X_Last_Update_Date,
43 X_Last_Updated_By,
44 X_Last_Update_Login
45 );
46
47 OPEN C;
48 FETCH C INTO X_Rowid;
49 if (C%NOTFOUND) then
50 CLOSE C;
51 Raise NO_DATA_FOUND;
52 end if;
53 CLOSE C;
54 END Insert_Row;
55
56
57 PROCEDURE Lock_Row(X_Rowid IN VARCHAR2,
58 X_Control_Rule_id IN NUMBER,
59 X_Field_value_code IN VARCHAR2,
60 X_Start_Date IN DATE,
61 X_End_Date IN DATE
62 ) IS
63
64 CURSOR C IS
65 SELECT *
66 FROM pa_pm_product_control_rules
67 WHERE rowid = X_Rowid
68 FOR UPDATE of end_date_active NOWAIT;
69 Recinfo C%ROWTYPE;
70
71 BEGIN
72 OPEN C;
73 FETCH C INTO Recinfo;
74 if (C%NOTFOUND) then
75 CLOSE C;
76 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
77 APP_EXCEPTION.Raise_Exception;
78 end if;
79 CLOSE C;
80 if (
81 (Recinfo.control_rule_id = X_Control_Rule_id)
82 AND (Recinfo.start_date_active = X_Start_Date)
83 AND ( (Recinfo.end_date_active = X_end_Date)
84 OR
85 ( (Recinfo.end_date_active IS NULL )
86 AND (X_end_date IS NULL)))
87 AND ( (Recinfo.field_value_code = X_Field_value_code)
88 OR
89 ( (Recinfo.field_value_code IS NULL)
90 AND (X_Field_value_code IS NULL )))
91 ) then
92 return;
93 else
94 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
95 APP_EXCEPTION.Raise_Exception;
96 end if;
97 END Lock_Row;
98
99 PROCEDURE Update_Row(X_Rowid IN VARCHAR2,
100 X_Start_Date IN DATE,
101 X_End_Date IN DATE,
102 X_Control_rule_id IN NUMBER,
103 X_Last_Update_Date IN DATE,
104 X_Last_Updated_By IN NUMBER,
105 X_Last_Update_Login IN NUMBER
106
107 ) IS
108 BEGIN
109 UPDATE pa_pm_product_control_rules
110 SET
111 start_date_active = x_start_date,
112 end_date_active = X_end_date
113 WHERE rowid = X_Rowid;
114
115 if (SQL%NOTFOUND) then
116 Raise NO_DATA_FOUND;
117 end if;
118 END Update_Row;
119
120 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
121 BEGIN
122 DELETE FROM pa_pm_product_control_rules
123 WHERE rowid = X_Rowid;
124
125 if (SQL%NOTFOUND) then
126 Raise NO_DATA_FOUND;
127 end if;
128 END Delete_Row;
129
130 END PA_PM_CONTROL_RULES_PKG;
[Home] [Help]