[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_CREDITS_LOG_PKG
Source
1 PACKAGE BODY AS_SALES_CREDITS_LOG_PKG as
2 /* $Header: asxtsclb.pls 120.2 2005/09/02 04:05:34 appldev ship $ */
3 PROCEDURE Insert_Row(p_new_lead_id NUMBER,
4 p_new_lead_line_id NUMBER,
5 p_new_sales_credit_id NUMBER,
6 p_new_last_update_date DATE,
7 p_new_last_updated_by NUMBER,
8 p_new_last_update_login NUMBER,
9 p_new_creation_date DATE,
10 p_new_created_by NUMBER,
11 p_new_salesforce_id NUMBER,
12 p_new_salesgroup_id NUMBER,
13 p_new_credit_type_id NUMBER,
14 p_new_credit_percent NUMBER,
15 p_new_credit_amount NUMBER,
16 p_new_opp_worst_frcst_amount NUMBER,
17 p_new_opp_frcst_amount NUMBER,
18 p_new_opp_best_frcst_amount NUMBER,
19 p_endday_log_flag VARCHAR2,
20 p_TRIGGER_MODE VARCHAR2)
21 IS
22 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
23
24 BEGIN
25 -- dbms_output.put_line('In AS_SALES_CREDITS_LOG_PKG Before Insert Statement');
26
27 IF l_debug THEN
28 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
29 'asxtsclb: In AS_SALES_CREDITS_LOG_PKG Before Insert Statement');
30 END IF;
31 Insert into AS_SALES_CREDITS_LOG (
32 log_id,
33 lead_id,
34 lead_line_id,
35 sales_credit_id,
36 last_update_date,
37 last_updated_by,
38 last_update_login,
39 creation_date,
40 created_by,
41 log_mode,
42 salesforce_id,
43 salesgroup_id,
44 credit_type_id,
45 credit_percent,
46 credit_amount,
47 opp_worst_forecast_amount,
48 opp_forecast_amount,
49 opp_best_forecast_amount,
50 endday_log_flag) VALUES (
51 AS_SALES_CREDIT_LOG_S.nextval,
52 decode( p_new_lead_id, FND_API.G_MISS_NUM, NULL, p_new_lead_id),
53 decode( p_new_lead_line_id,FND_API.G_MISS_NUM, NULL, p_new_lead_line_id),
54 decode( p_new_sales_credit_id,FND_API.G_MISS_NUM, NULL, p_new_sales_credit_id),
55 decode( p_new_last_update_date,FND_API.G_MISS_DATE, TO_DATE(NULL), p_new_last_update_date),
56 decode( p_new_last_updated_by,FND_API.G_MISS_NUM, NULL, p_new_last_updated_by),
57 decode( p_new_last_update_login,FND_API.G_MISS_NUM, NULL, p_new_last_update_login),
58 decode( p_new_creation_date,FND_API.G_MISS_DATE, TO_DATE(NULL), p_new_creation_date),
59 decode( p_new_created_by,FND_API.G_MISS_NUM, NULL, p_new_created_by),
60 p_TRIGGER_MODE,
61 decode( p_new_salesforce_id,FND_API.G_MISS_NUM, NULL, p_new_salesforce_id),
62 decode( p_new_salesgroup_id,FND_API.G_MISS_NUM, NULL, p_new_salesgroup_id),
63 decode( p_new_credit_type_id,FND_API.G_MISS_NUM, NULL, p_new_credit_type_id),
64 decode( p_new_credit_percent,FND_API.G_MISS_NUM, NULL, p_new_credit_percent),
65 decode( p_new_credit_amount,FND_API.G_MISS_NUM, NULL, p_new_credit_amount),
66 decode( p_new_opp_worst_frcst_amount,FND_API.G_MISS_NUM, NULL, p_new_opp_worst_frcst_amount),
67 decode( p_new_opp_frcst_amount,FND_API.G_MISS_NUM, NULL, p_new_opp_frcst_amount),
68 decode( p_new_opp_best_frcst_amount,FND_API.G_MISS_NUM, NULL, p_new_opp_best_frcst_amount),
69 decode( p_endday_log_flag,FND_API.G_MISS_CHAR, NULL, p_endday_log_flag));
70 EXCEPTION
71 WHEN OTHERS THEN
72 -- dbms_output.put_line('In AS_SALES_CREDITS_LOG_PKG After Insert Statement Seems some error');
73 -- dbms_output.put_line('Error Number:'||SQLCODE);
74 -- dbms_output.put_line('Error Message:'|| SUBSTR(SQLERRM, 1, 200));
75 IF l_debug THEN
76 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
77 'asxtsclb: In AS_SALES_CREDITS_LOG_PKG After Insert Statement Seems some error');
78 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
79 'asxtsclb: Error Number: '||SQLCODE||' and Error Message: ' || SUBSTR(SQLERRM, 1, 200));
80 END IF;
81 END Insert_Row;
82
83 PROCEDURE Update_Row(p_new_lead_id NUMBER,
84 p_new_lead_line_id NUMBER,
85 p_old_sales_credit_id NUMBER,
86 p_old_last_update_date DATE,
87 p_new_last_update_date DATE,
88 p_new_last_updated_by NUMBER,
89 p_new_last_update_login NUMBER,
90 p_new_creation_date DATE,
91 p_new_created_by NUMBER,
92 p_new_salesforce_id NUMBER,
93 p_new_salesgroup_id NUMBER,
94 p_new_credit_type_id NUMBER,
95 p_new_credit_percent NUMBER,
96 p_new_credit_amount NUMBER,
97 p_new_opp_worst_frcst_amount NUMBER,
98 p_new_opp_frcst_amount NUMBER,
99 p_new_opp_best_frcst_amount NUMBER,
100 p_endday_log_flag VARCHAR2,
101 p_TRIGGER_MODE VARCHAR2)
102 IS
103 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
104
105 BEGIN
106 -- dbms_output.put_line('In AS_SALES_CREDITS_LOG_PKG before Update_Row');
107 IF l_debug THEN
108 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
109 'asxtsclb: In AS_SALES_CREDITS_LOG_PKG before Update_Row');
110 END IF;
111
112 Update AS_SALES_CREDITS_LOG
113 SET object_version_number = nvl(object_version_number,0) + 1, lead_id = decode( p_new_lead_id, FND_API.G_MISS_NUM, NULL, p_new_lead_id),
114 lead_line_id = decode( p_new_lead_line_id,FND_API.G_MISS_NUM, NULL, p_new_lead_line_id),
115 last_update_date = decode( p_new_last_update_date,FND_API.G_MISS_DATE, TO_DATE(NULL), p_new_last_update_date),
116 last_updated_by = decode( p_new_last_updated_by,FND_API.G_MISS_NUM, NULL, p_new_last_updated_by),
117 last_update_login = decode( p_new_last_update_login,FND_API.G_MISS_NUM, NULL, p_new_last_update_login),
118 LOG_MODE = p_TRIGGER_MODE,
119 salesforce_id = decode( p_new_salesforce_id,FND_API.G_MISS_NUM, NULL, p_new_salesforce_id),
120 salesgroup_id = decode( p_new_salesgroup_id,FND_API.G_MISS_NUM, NULL, p_new_salesgroup_id),
121 credit_type_id = decode( p_new_credit_type_id,FND_API.G_MISS_NUM, NULL, p_new_credit_type_id),
122 credit_percent = decode( p_new_credit_percent,FND_API.G_MISS_NUM, NULL, p_new_credit_percent),
123 credit_amount = decode( p_new_credit_amount,FND_API.G_MISS_NUM, NULL, p_new_credit_amount),
124 opp_worst_forecast_amount = decode( p_new_opp_worst_frcst_amount,FND_API.G_MISS_NUM, NULL, p_new_opp_worst_frcst_amount),
125 opp_forecast_amount = decode( p_new_opp_frcst_amount,FND_API.G_MISS_NUM, NULL, p_new_opp_frcst_amount),
126 opp_best_forecast_amount = decode( p_new_opp_best_frcst_amount,FND_API.G_MISS_NUM, NULL, p_new_opp_best_frcst_amount),
127 endday_log_flag = decode( p_endday_log_flag,FND_API.G_MISS_CHAR, endday_log_flag, p_endday_log_flag)
128 WHERE log_id = (select max(log_id)
129 from AS_SALES_CREDITS_LOG
130 where SALES_CREDIT_ID = p_old_sales_credit_id);
131
132 If (SQL%NOTFOUND) then
133 -- dbms_output.put_line('In AS_SALES_CREDITS_LOG_PKG after Update statement : Data No found seems');
134 -- dbms_output.put_line('Error Number:'||SQLCODE);
135 -- dbms_output.put_line('Error Message:'|| SUBSTR(SQLERRM, 1, 200));
136
137 IF l_debug THEN
138 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
139 'asxtsclb: In AS_SALES_CREDITS_LOG_PKG after Update statement : Data No found seems');
140 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
141 'asxtsclb: Error Number: '||SQLCODE||' and Error Message: ' || SUBSTR(SQLERRM, 1, 200));
142 END IF;
143 RAISE NO_DATA_FOUND;
144 End If;
145 END Update_Row;
146
147 Procedure Delete_Row(p_old_lead_id NUMBER,
148 p_old_lead_line_id NUMBER,
149 p_old_sales_credit_id NUMBER,
150 p_old_last_update_date DATE,
151 p_old_last_updated_by NUMBER,
152 p_old_last_update_login NUMBER,
153 p_old_creation_date DATE,
154 p_old_created_by NUMBER,
155 p_endday_log_flag VARCHAR2)
156 IS
157 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
158
159 BEGIN
160 /*
161 Update AS_SALES_CREDITS_LOG
162 set object_version_number = nvl(object_version_number,0) + 1, log_mode = 'D'
163 where sales_credit_id = p_old_sales_credit_id and last_update_date = p_old_last_update_date;
164 */
165 Insert into AS_SALES_CREDITS_LOG (
166 log_id,
167 lead_id,
168 lead_line_id,
169 sales_credit_id,
170 last_update_date,
171 last_updated_by,
172 last_update_login,
173 creation_date,
174 created_by,
175 log_mode,
176 endday_log_flag) VALUES (
177 AS_SALES_CREDIT_LOG_S.nextval,
178 decode( p_old_lead_id, FND_API.G_MISS_NUM, NULL, p_old_lead_id),
179 decode( p_old_lead_line_id,FND_API.G_MISS_NUM, NULL, p_old_lead_line_id),
180 decode( p_old_sales_credit_id,FND_API.G_MISS_NUM, NULL, p_old_sales_credit_id),
181 sysdate,
182 decode( p_old_last_updated_by,FND_API.G_MISS_NUM, NULL, p_old_last_updated_by),
183 decode( p_old_last_update_login,FND_API.G_MISS_NUM, NULL, p_old_last_update_login),
184 decode( p_old_creation_date,FND_API.G_MISS_DATE, TO_DATE(NULL), p_old_creation_date),
185 decode( p_old_created_by,FND_API.G_MISS_NUM, NULL, p_old_created_by),
186 'D',
187 decode( p_endday_log_flag,FND_API.G_MISS_CHAR, NULL, p_endday_log_flag));
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 -- dbms_output.put_line('In AS_SALES_CREDITS_LOG_PKG After Delete Statement Seems some error');
192 -- dbms_output.put_line('Error Number:'||SQLCODE);
193 -- dbms_output.put_line('Error Message:'|| SUBSTR(SQLERRM, 1, 200));
194 IF l_debug THEN
195 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
196 'asxtsclb: In AS_SALES_CREDITS_LOG_PKG After Delete Statement Seems some error');
197 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
198 'asxtsclb: Error Number: '||SQLCODE||' and Error Message: ' || SUBSTR(SQLERRM, 1, 200));
199 END IF;
200
201 END Delete_Row;
202 END AS_SALES_CREDITS_LOG_PKG;