[Home] [Help]
147:
148: -- Group Code subquery
149: CURSOR group_code_cur IS
150: SELECT distinct quota_group_code
151: FROM cn_quotas where quota_group_code is not null;
152:
153: -- Annual Subquery
154: -- To get
155: -- 1) Annual Quota Target and percent
157: (l_srp_plan_assign_id IN NUMBER,
158: l_quota_group_code IN VARCHAR) IS
159: SELECT nvl(sum(sqa.target),0) target
160: from cn_srp_quota_assigns sqa,
161: cn_quotas q
162: where sqa.srp_plan_assign_id = l_srp_plan_assign_id
163: and sqa.quota_id = q.quota_id
164: and q.quota_group_code = l_quota_group_code;
165:
169: CURSOR ytd_quota_target_cur
170: (l_srp_plan_assign_id IN NUMBER,
171: l_quota_group_code IN VARCHAR) IS
172: select nvl(sum(itd_target),0) itd_target
173: from cn_srp_period_quotas spq, cn_quotas q
174: where srp_plan_assign_id = l_srp_plan_assign_id
175: and period_id = p_period_id
176: and q.quota_id = spq.quota_id
177: and q.quota_group_code = l_quota_group_code;
184: l_srp_plan_assign_id IN NUMBER) IS
185: SELECT sum(nvl(cspq.perf_achieved_itd,0)) ytd_credit,
186: sum(nvl(cspq.commission_payed_itd,0)) ytd_earnings
187: FROM cn_srp_period_quotas cspq,
188: cn_quotas quota
189: WHERE cspq.period_id = p_period_id
190: AND cspq.srp_plan_assign_id = l_srp_plan_assign_id
191: AND cspq.quota_id = quota.quota_id
192: AND nvl(quota.quota_group_code, FND_API.G_MISS_CHAR) =
205: l_srp_plan_assign_id IN NUMBER) IS
206: SELECT sum(nvl(cspq.target_amount,0)) ptd_target,
207: sum(nvl(cspq.perf_achieved_ptd,0)) ptd_credit,
208: sum(nvl(cspq.commission_payed_ptd,0)) ptd_earnings
209: FROM cn_quotas quota,
210: cn_srp_period_quotas cspq
211: WHERE cspq.period_id = p_period_id
212: AND cspq.srp_plan_assign_id = l_srp_plan_assign_id
213: AND cspq.quota_id = quota.quota_id
271: PROCEDURE Get_Group_Codes
272: (x_result_tbl OUT pe_info_tbl_type) IS
273:
274: CURSOR group_codes IS
275: select distinct quota_group_code from cn_quotas
276: where quota_group_code is not null;
277: l_count NUMBER := 0;
278: BEGIN
279: FOR c in group_codes LOOP