[Home] [Help]
PACKAGE BODY: APPS.AS_SCN_FORECAST_PVT
Source
1 PACKAGE BODY AS_SCN_FORECAST_PVT as
2 /* $Header: asxvpemb.pls 115.17 2004/07/13 09:34:55 gbatra ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='AS_SCN_FORECAST_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12):='asxvpemb.pls';
6
7 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
9 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
10 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
11 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
12
13
14 PROCEDURE Get_Forecast_Amounts (
15 p_api_version_number IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_check_access_flag IN VARCHAR2,
18 p_resource_id IN NUMBER,
19 p_quota_id IN NUMBER,
20 p_period_name IN VARCHAR2,
21 p_to_currency_code IN VARCHAR2,
22 x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 x_forecast_amount_tbl OUT NOCOPY AS_SCN_FORECAST_PUB.FORECAST_TBL_TYPE)
26 IS
27
28 l_api_name CONSTANT VARCHAR2(30) := 'Get_Forecast_Amounts';
29 l_api_version_number CONSTANT NUMBER := 2.0;
30 l_return_status VARCHAR2(1);
31 l_period_set_name VARCHAR2(15);
32 i INTEGER :=0;
33 Cursor cur_salesforce (c_resource_id NUMBER,
34 c_quota_id NUMBER,
35 c_period_name VARCHAR2,
36 c_calendar VARCHAR2,
37 c_credit_type_id NUMBER,
38 c_toCurrency VARCHAR2) IS
39
40 SELECT apwl.product_category_id,
41 apwl.product_cat_set_id,
42 SUM(DECODE(apwl.FORECAST_AMOUNT_FLAG,'N',1,0)+R.CONVERSION_STATUS_FLAG) CONVERSION_FLAG ,
43 SUM(ROUND(apwl.WORST_FORECAST_AMOUNT*R.CONVERSION_RATE,0)),
44 SUM(ROUND(apwl.FORECAST_AMOUNT*R.CONVERSION_RATE,0)),
45 SUM(ROUND(apwl.BEST_FORECAST_AMOUNT*R.CONVERSION_RATE,0))
46 FROM as_prod_worksheet_lines apwl,
47 AS_PERIOD_RATES R,
48 as_pe_int_categories apic
49 WHERE apwl.product_category_id = apic.product_category_id
50 AND apwl.product_cat_set_id = apic.product_cat_set_id
51 AND apic.quota_id = c_quota_id
52 AND apwl.salesforce_id = c_resource_id
53 AND apwl.period_name = c_period_name
54 AND apwl.status_code ='SAVED'
55 AND apwl.end_date_active is null
56 AND apwl.credit_type_id = c_credit_type_id
57 AND R.PERIOD_SET_NAME = c_calendar
58 AND R.PERIOD_NAME = c_period_name
59 AND R.TO_CURRENCY= c_toCurrency
60 AND R.FROM_CURRENCY=apwl.CURRENCY_CODE
61 GROUP BY
62 apwl.product_category_id,
63 apwl.product_cat_set_id
64 UNION
65 SELECT apwl.product_category_id,
66 apwl.product_cat_set_id,
67 SUM(DECODE(apwl.FORECAST_AMOUNT_FLAG,'N',1,0)+R.CONVERSION_STATUS_FLAG) CONVERSION_FLAG ,
68 SUM(ROUND(apwl.WORST_FORECAST_AMOUNT*R.CONVERSION_RATE,0)),
69 SUM(ROUND(apwl.FORECAST_AMOUNT*R.CONVERSION_RATE,0)),
70 SUM(ROUND(apwl.BEST_FORECAST_AMOUNT*R.CONVERSION_RATE,0))
71 FROM as_prod_worksheet_lines apwl,
72 as_pe_int_categories apic,
73 as_sales_groups_v sg,
74 AS_PERIOD_RATES R,
75 gl_periods pd
76 WHERE apwl.product_category_id = apic.product_category_id
77 AND apwl.product_cat_set_id = apic.product_cat_set_id
78 AND apic.quota_id = c_quota_id
79 AND pd.period_name= c_period_name
80 AND pd.period_set_name ='Accounting'
81 AND sg.manager_salesforce_id = c_resource_id
82 AND (sg.mgr_start_date <= pd.end_date or sg.mgr_start_date is null)
83 AND (sg.mgr_end_date >= pd.start_date or sg.mgr_end_date is null)
84 AND apwl.salesforce_id is null
85 AND apwl.sales_group_id = sg.sales_group_id
86 AND apwl.period_name =c_period_name
87 AND apwl.status_code ='SAVED'
88 AND apwl.end_date_active is null
89 AND apwl.credit_type_id = c_credit_type_id
90 AND R.PERIOD_SET_NAME = c_calendar
91 AND R.PERIOD_NAME = c_period_name
92 AND R.TO_CURRENCY= c_toCurrency
93 AND R.FROM_CURRENCY=apwl.CURRENCY_CODE
94 GROUP BY
95 apwl.product_category_id,
96 apwl.product_cat_set_id;
97
98 l_calendar VARCHAR2(15);
99 l_credit_type_id NUMBER := FND_PROFILE.value('AS_FORECAST_CREDIT_TYPE_ID');
100 l_conversion NUMBER:=0;
101 l_rate NUMBER:=0;
102
103
104 TYPE SCN_RESULT_Rec_Type IS RECORD (
105 PRODUCT_CATEGORY_ID NUMBER ,
106 PRODUCT_CAT_SET_ID NUMBER ,
107 CONVERSION_FLAG NUMBER := 0,
108 WORST_FORECAST_AMOUNT NUMBER := 0,
109 FORECAST_AMOUNT NUMBER := 0,
110 BEST_FORECAST_AMOUNT NUMBER := 0);
111
112
113 TYPE SCN_RESULT_Tbl_Type IS TABLE OF SCN_RESULT_Rec_Type INDEX BY BINARY_INTEGER;
114 L_SCN_FORECAST_TBL SCN_RESULT_Tbl_Type;
115
116
117 BEGIN
118
119 -- Standard call to check for call compatibility.
120 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
121 p_api_version_number,
122 l_api_name,
123 G_PKG_NAME) THEN
124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
125 END IF;
126
127 -- Initialize message list if p_init_msg_list is set to TRUE.
128
129 IF FND_API.to_Boolean( p_init_msg_list ) THEN
130 FND_MSG_PUB.initialize;
131 END IF;
132
133 -- Initialize API return status to success
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136 l_calendar := FND_PROFILE.value('AS_FORECAST_CALENDAR');
137
138 OPEN cur_salesforce(p_resource_id,p_quota_id, p_period_name,l_calendar, l_credit_type_id,p_to_currency_code);
139 LOOP
140 i:=i+1;
141 FETCH cur_salesforce INTO L_SCN_FORECAST_TBL(i);
142 EXIT WHEN cur_salesforce%NOTFOUND;
143
144 -- Note: INTEREST_TYPE_ID field is being assigned PRODUCT_CATEGORY_ID
145 x_forecast_amount_tbl(i).INTEREST_TYPE_ID :=L_SCN_FORECAST_TBL(i).PRODUCT_CATEGORY_ID;
146 -- Note: PRI_INTEREST_CODE_ID field is being assigned PRODUCT_CAT_SET_ID
147 x_forecast_amount_tbl(i).PRI_INTEREST_CODE_ID :=L_SCN_FORECAST_TBL(i).PRODUCT_CAT_SET_ID;
148 x_forecast_amount_tbl(i).WORST_FORECAST_AMOUNT :=L_SCN_FORECAST_TBL(i).WORST_FORECAST_AMOUNT;
149 x_forecast_amount_tbl(i).FORECAST_AMOUNT :=L_SCN_FORECAST_TBL(i).FORECAST_AMOUNT;
150 x_forecast_amount_tbl(i).BEST_FORECAST_AMOUNT :=L_SCN_FORECAST_TBL(i).BEST_FORECAST_AMOUNT;
151
152 x_forecast_amount_tbl(i).WORST_FORECAST_AMOUNT := L_SCN_FORECAST_TBL(i).WORST_FORECAST_AMOUNT;
153 x_forecast_amount_tbl(i).FORECAST_AMOUNT := L_SCN_FORECAST_TBL(i).FORECAST_AMOUNT;
154 x_forecast_amount_tbl(i).BEST_FORECAST_AMOUNT := L_SCN_FORECAST_TBL(i).BEST_FORECAST_AMOUNT;
155
156 IF L_SCN_FORECAST_TBL(i).CONVERSION_FLAG >0 THEN
157
158 x_forecast_amount_tbl(i).WORST_FORECAST_AMOUNT_FLAG:='N';
159 x_forecast_amount_tbl(i).FORECAST_AMOUNT_FLAG:='N';
160 x_forecast_amount_tbl(i).BEST_FORECAST_AMOUNT_FLAG:='N';
161
162 ELSE
163 x_forecast_amount_tbl(i).WORST_FORECAST_AMOUNT_FLAG:='Y';
164 x_forecast_amount_tbl(i).FORECAST_AMOUNT_FLAG:='Y';
165 x_forecast_amount_tbl(i).BEST_FORECAST_AMOUNT_FLAG:='Y';
166 END IF;
167
168 END LOOP;
169
170 CLOSE cur_salesforce;
171
172 EXCEPTION
173
174 WHEN FND_API.G_EXC_ERROR THEN
175
176 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
177 P_API_NAME => L_API_NAME
178 ,P_PKG_NAME => G_PKG_NAME
179 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
180 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
181 ,P_ROLLBACK_FLAG => 'N'
182 ,X_MSG_COUNT => X_MSG_COUNT
183 ,X_MSG_DATA => X_MSG_DATA
184 ,X_RETURN_STATUS => X_RETURN_STATUS);
185
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187
188 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
189 P_API_NAME => L_API_NAME
190 ,P_PKG_NAME => G_PKG_NAME
191 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
192 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
193 ,P_ROLLBACK_FLAG => 'N'
194 ,X_MSG_COUNT => X_MSG_COUNT
195 ,X_MSG_DATA => X_MSG_DATA
196 ,X_RETURN_STATUS => X_RETURN_STATUS);
197
198 WHEN OTHERS THEN
199
200 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
201 P_API_NAME => L_API_NAME
202 ,P_PKG_NAME => G_PKG_NAME
203 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
204 ,P_SQLCODE => SQLCODE
205 ,P_SQLERRM => SQLERRM
206 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
207 ,P_ROLLBACK_FLAG => 'N'
208 ,X_MSG_COUNT => X_MSG_COUNT
209 ,X_MSG_DATA => X_MSG_DATA
210 ,X_RETURN_STATUS => X_RETURN_STATUS);
211
212 IF cur_salesforce%ISOPEN
213 THEN
214 CLOSE cur_salesforce;
215 END IF;
216
217 END Get_Forecast_Amounts;
218
219
220 END AS_SCN_FORECAST_PVT;
221