1 PACKAGE BODY as_sales_lead_card_rules_pkg AS
2 /* $Header: asxtscrb.pls 120.1 2005/06/24 17:02:27 appldev ship $ */
3 PROCEDURE insert_row(
4 x_rowid IN OUT NOCOPY VARCHAR2
5 , x_card_rule_id NUMBER
6 , x_scorecard_id NUMBER
7 , x_last_update_date DATE
8 , x_last_updated_by NUMBER
9 , x_creation_date DATE
10 , x_created_by NUMBER
11 , x_last_update_login NUMBER
12 , x_description VARCHAR2
13 , x_start_date_active DATE
14 , x_end_date_active DATE
15 , x_score NUMBER
16 ) IS
17 CURSOR l_insert IS
18 SELECT ROWID
19 FROM as_sales_lead_card_rules
20 WHERE card_rule_id = x_card_rule_id;
21 BEGIN
22 INSERT INTO as_sales_lead_card_rules (
23 card_rule_id
24 , scorecard_id
25 , last_update_date
26 , last_updated_by
27 , creation_date
28 , created_by
29 , last_update_login
30 , description
31 , start_date_active
32 , end_date_active
33 , score
34 ) VALUES (
35 x_card_rule_id
36 , DECODE(x_scorecard_id,FND_API.G_MISS_NUM,NULL,x_scorecard_id)
37 , DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
38 , DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
39 , DECODE(x_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_creation_date)
40 , DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
41 , DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
42 , DECODE(x_description,FND_API.G_MISS_CHAR,NULL,x_description)
43 , DECODE(x_start_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_start_date_active)
44 , DECODE(x_end_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_end_date_active)
45 , DECODE(x_score,FND_API.G_MISS_NUM,NULL,x_score)
46 );
47
48 OPEN l_insert;
49 FETCH l_insert INTO x_rowid;
50 IF (l_insert%NOTFOUND) THEN
51 CLOSE l_insert;
52 RAISE NO_DATA_FOUND;
53 END IF;
54 END insert_row;
55
56 PROCEDURE delete_row(
57 x_card_rule_id NUMBER
58 ) IS
59 BEGIN
60 DELETE FROM as_sales_lead_card_rules
61 WHERE card_rule_id = x_card_rule_id;
62 IF (SQL%NOTFOUND) THEN
63 RAISE NO_DATA_FOUND;
64 END IF;
65 END delete_row;
66
67 PROCEDURE update_row(
68 x_rowid VARCHAR2
69 , x_card_rule_id NUMBER
70 , x_scorecard_id NUMBER
71 , x_last_update_date DATE
72 , x_last_updated_by NUMBER
73 , x_creation_date DATE
74 , x_created_by NUMBER
75 , x_last_update_login NUMBER
76 , x_description VARCHAR2
77 , x_start_date_active DATE
78 , x_end_date_active DATE
79 , x_score NUMBER
80 ) IS
81 BEGIN
82 UPDATE as_sales_lead_card_rules
83 SET
84 card_rule_id=DECODE(x_card_rule_id,FND_API.G_MISS_NUM,NULL,x_card_rule_id)
85 , scorecard_id=DECODE(x_scorecard_id,FND_API.G_MISS_NUM,NULL,x_scorecard_id)
86 , last_update_date=DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
87 , last_updated_by=DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
88 , creation_date=DECODE(x_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_creation_date)
89 , created_by=DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
90 , last_update_login=DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
91 , description=DECODE(x_description,FND_API.G_MISS_CHAR,NULL,x_description)
92 , start_date_active=DECODE(x_start_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_start_date_active)
93 , end_date_active=DECODE(x_end_date_active,FND_API.G_MISS_DATE,TO_DATE(NULL),x_end_date_active)
94 , score=DECODE(x_score,FND_API.G_MISS_NUM,NULL,x_score)
95 WHERE ROWID = x_rowid;
96 IF (SQL%NOTFOUND) THEN
97 RAISE NO_DATA_FOUND;
98 END IF;
99 END update_row;
100
101 PROCEDURE lock_row(
102 x_rowid VARCHAR2
103 , x_card_rule_id NUMBER
104 , x_scorecard_id NUMBER
105 , x_last_update_date DATE
106 , x_last_updated_by NUMBER
107 , x_creation_date DATE
108 , x_created_by NUMBER
109 , x_last_update_login NUMBER
110 , x_description VARCHAR2
111 , x_start_date_active DATE
112 , x_end_date_active DATE
113 , x_score NUMBER
114 ) IS
115 CURSOR l_lock IS
116 SELECT *
117 FROM as_sales_lead_card_rules
118 WHERE rowid = x_rowid
119 FOR UPDATE OF card_rule_id NOWAIT;
120 l_table_rec l_lock%ROWTYPE;
121 BEGIN
122 OPEN l_lock;
123 FETCH l_lock INTO l_table_rec;
124 IF (l_lock%NOTFOUND) THEN
125 CLOSE l_lock;
126 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
127 APP_EXCEPTION.RAISE_EXCEPTION;
128 END IF;
129 CLOSE l_lock;
130 IF (
131 ((l_table_rec.card_rule_id = x_card_rule_id)
132 OR ((l_table_rec.card_rule_id IS NULL)
133 AND ( x_card_rule_id IS NULL)))
134 AND ((l_table_rec.scorecard_id = x_scorecard_id)
135 OR ((l_table_rec.scorecard_id IS NULL)
136 AND ( x_scorecard_id IS NULL)))
137 AND ((l_table_rec.last_update_date = x_last_update_date)
138 OR ((l_table_rec.last_update_date IS NULL)
139 AND ( x_last_update_date IS NULL)))
140 AND ((l_table_rec.last_updated_by = x_last_updated_by)
141 OR ((l_table_rec.last_updated_by IS NULL)
142 AND ( x_last_updated_by IS NULL)))
143 AND ((l_table_rec.creation_date = x_creation_date)
144 OR ((l_table_rec.creation_date IS NULL)
145 AND ( x_creation_date IS NULL)))
146 AND ((l_table_rec.created_by = x_created_by)
147 OR ((l_table_rec.created_by IS NULL)
148 AND ( x_created_by IS NULL)))
149 AND ((l_table_rec.last_update_login = x_last_update_login)
150 OR ((l_table_rec.last_update_login IS NULL)
151 AND ( x_last_update_login IS NULL)))
152 AND ((l_table_rec.description = x_description)
153 OR ((l_table_rec.description IS NULL)
154 AND ( x_description IS NULL)))
155 AND ((l_table_rec.start_date_active = x_start_date_active)
156 OR ((l_table_rec.start_date_active IS NULL)
157 AND ( x_start_date_active IS NULL)))
158 AND ((l_table_rec.end_date_active = x_end_date_active)
159 OR ((l_table_rec.end_date_active IS NULL)
160 AND ( x_end_date_active IS NULL)))
161 AND ((l_table_rec.score = x_score)
162 OR ((l_table_rec.score IS NULL)
163 AND ( x_score IS NULL)))
164 ) THEN
165 RETURN;
166 ELSE
167 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
168 APP_EXCEPTION.RAISE_EXCEPTION;
169 END IF;
170 END lock_row;
171 END as_sales_lead_card_rules_pkg;
[Home] [Help]