[Home] [Help]
137: null, --cl.attribute15
138: cl.org_id,
139: 0
140: FROM cn_commission_lines_all cl,
141: cn_quotas_all pe
142: WHERE cl.commission_line_id = p_commission_line_id
143: AND cl.quota_id = pe.quota_id
144: AND cl.srp_payee_assign_id IS NULL)
145: UNION --this is added for assign payees for fixing bug#2495614
195: cl.org_id,
196: 0
197: FROM cn_commission_lines_all cl,
198: cn_srp_payee_assigns_all payee,
199: cn_quotas_all pe
200: WHERE cl.commission_line_id = p_commission_line_id
201: AND cl.quota_id = pe.quota_id
202: AND cl.srp_payee_assign_id IS NOT NULL
203: AND payee.srp_payee_assign_id = cl.srp_payee_assign_id);
896: l_input_achieved_itd_tbl cn_formula_common_pkg.num_table_type;
897:
898: CURSOR l_quota_csr (l_quota_id NUMBER ) IS
899: SELECT q.incremental_type, cr.name, q.interval_type_id, q.org_id
900: FROM cn_quotas_all q,
901: cn_credit_types cr
902: WHERE q.quota_id = l_quota_id
903: AND cr.credit_type_id = q.credit_type_id
904: AND cr.org_id = q.org_id;
1398: FROM cn_cal_per_int_types_all p2
1399: WHERE (p2.interval_type_id, p2.org_id, p2.interval_number) IN
1400: (SELECT p1.interval_type_id, p1.org_id, p1.interval_number
1401: FROM cn_cal_per_int_types_all p1,
1402: cn_quotas_all q
1403: WHERE p1.cal_period_id = p_period_id
1404: AND q.quota_id = p_quota_id
1405: AND p1.org_id = q.org_id
1406: AND p1.interval_type_id = q.interval_type_id);
1421: FROM cn_cal_per_int_types_all p2
1422: WHERE (p2.interval_type_id, p2.org_id, p2.interval_number) IN
1423: (SELECT p1.interval_type_id, p1.org_id, p1.interval_number
1424: FROM cn_cal_per_int_types_all p1,
1425: cn_quotas_all q
1426: WHERE p1.cal_period_id = p_period_id
1427: AND q.quota_id = p_quota_id
1428: AND p1.org_id = q.org_id
1429: AND p1.interval_type_id = q.interval_type_id);
1448: INTO l_start_period_id
1449: from cn_cal_per_int_types_all a,
1450: cn_period_statuses_all b
1451: where (a.interval_type_id, a.org_id) = (select interval_type_id, org_id
1452: from cn_quotas_all
1453: where quota_id = p_quota_id)
1454: and a.interval_number = (select interval_number
1455: from cn_cal_per_int_types_all
1456: where cal_period_id = p_period_id
1454: and a.interval_number = (select interval_number
1455: from cn_cal_per_int_types_all
1456: where cal_period_id = p_period_id
1457: and (interval_type_id, org_id) = (select interval_type_id, org_id
1458: from cn_quotas_all
1459: where quota_id = p_quota_id))
1460: and a.cal_period_id = b.period_id
1461: and b.quarter_num = (select quarter_num
1462: from cn_period_statuses_all
1460: and a.cal_period_id = b.period_id
1461: and b.quarter_num = (select quarter_num
1462: from cn_period_statuses_all
1463: where period_id = p_period_id
1464: and org_id = (select org_id from cn_quotas_all where quota_id = p_quota_id));
1465:
1466: RETURN l_start_period_id;
1467: END get_quarter_start_period_id;
1468:
1473: INTO l_end_period_id
1474: from cn_cal_per_int_types_all a,
1475: cn_period_statuses_all b
1476: where (a.interval_type_id, a.org_id) = (select interval_type_id, org_id
1477: from cn_quotas_all
1478: where quota_id = p_quota_id)
1479: and a.interval_number = (select interval_number from cn_cal_per_int_types_all
1480: where cal_period_id = p_period_id
1481: and (interval_type_id, org_id) = (select interval_type_id, org_id
1478: where quota_id = p_quota_id)
1479: and a.interval_number = (select interval_number from cn_cal_per_int_types_all
1480: where cal_period_id = p_period_id
1481: and (interval_type_id, org_id) = (select interval_type_id, org_id
1482: from cn_quotas_all
1483: where quota_id = p_quota_id))
1484: and a.cal_period_id = b.period_id
1485: and b.quarter_num = (select quarter_num
1486: from cn_period_statuses_all
1484: and a.cal_period_id = b.period_id
1485: and b.quarter_num = (select quarter_num
1486: from cn_period_statuses_all
1487: where period_id = p_period_id
1488: and org_id = (select org_id from cn_quotas_all where quota_id = p_quota_id));
1489:
1490: RETURN l_end_period_id;
1491: END get_quarter_end_period_id;
1492:
1607: l_formula_name,
1608: l_formula_type,
1609: l_org_id,
1610: l_pe_name
1611: FROM cn_quotas_all q,
1612: cn_calc_formulas_all f
1613: WHERE q.quota_id = p_quota_id
1614: AND q.calc_formula_id = f.calc_formula_id(+)
1615: AND q.org_id = f.org_id(+);
1742: SELECT spq.quota_id,
1743: qa.quota_sequence
1744: FROM cn_srp_period_quotas_all spq,
1745: cn_quota_assigns_all qa,
1746: cn_quotas_all q
1747: WHERE spq.srp_plan_assign_id = l_srp_plan_assign_id
1748: AND spq.salesrep_id = l_salesrep_id
1749: AND spq.period_id = l_period_id
1750: and qa.comp_plan_id = (select comp_plan_id
1786: SELECT spa.srp_plan_assign_id srp_plan_assign_id, batch.salesrep_id salesrep_id, qa.quota_sequence quota_sequence,
1787: batch.end_period_id end_period_id, batch.end_date end_date, pe.quota_id quota_id, pe.interval_type_id interval_type_id
1788: FROM cn_srp_plan_assigns_all spa,
1789: cn_quota_assigns_all qa,
1790: cn_quotas_all pe,
1791: cn_process_batches_all batch
1792: WHERE batch.physical_batch_id = p_physical_batch_id
1793: AND batch.salesrep_id = spa.salesrep_id
1794: AND spa.org_id = batch.org_id
1814: SELECT spa.srp_plan_assign_id srp_plan_assign_id, batch.salesrep_id salesrep_id, qa.quota_sequence quota_sequence,
1815: batch.end_period_id end_period_id, batch.end_date end_date, pe.quota_id quota_id, pe.interval_type_id interval_type_id
1816: FROM cn_srp_plan_assigns_all spa,
1817: cn_quota_assigns_all qa,
1818: cn_quotas_all pe,
1819: cn_process_batches_all batch
1820: WHERE batch.physical_batch_id = p_physical_batch_id
1821: AND batch.salesrep_id = spa.salesrep_id
1822: AND spa.org_id = batch.org_id
1904: cn_commission_lines_all amtline
1905: where amthead.commission_header_id = amtline.commission_header_id
1906: and amtline.commission_line_id = line.commission_line_id
1907: ),
1908: line.credit_type_id = (select credit_type_id from cn_quotas_all where quota_id = line.quota_id),
1909: last_update_date = sysdate,
1910: last_updated_by = g_last_updated_by,
1911: last_update_login = g_last_update_login
1912: WHERE line.commission_line_id in
3331: and cl.processed_date between batch.start_date and batch.end_date
3332: and cl.status = 'CALC'
3333: and cl.posting_status = 'POSTED'
3334: and cl.trx_type = 'BONUS'
3335: and (exists (select 1 from cn_quotas_all
3336: where quota_id = cl.quota_id
3337: and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
3338: and (l_calc_sub_batch_id = -1000 or
3339: cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
3370: and cl.org_id = l_org_id
3371: --and cl.processed_period_id between batch.period_id and batch.end_period_id
3372: and cl.processed_date between batch.start_date and batch.end_date
3373: and cl.trx_type = 'BONUS'
3374: and (exists (select 1 from cn_quotas_all
3375: where quota_id = cl.quota_id
3376: and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
3377: and (l_calc_sub_batch_id = -1000 or
3378: cl.quota_id in (select quota_id from cn_calc_sub_quotas_all
3389: and cl.org_id =l_org_id
3390: --and cl.processed_period_id between batch.period_id and batch.end_period_id
3391: and cl.processed_date between batch.start_date and batch.end_date
3392: and cl.trx_type = 'BONUS'
3393: and (exists (select 1 from cn_quotas_all
3394: where quota_id = cl.quota_id
3395: and (l_interval_type_id = interval_type_id or l_interval_type_id = -1003)))
3396: and (l_calc_sub_batch_id = -1000 or
3397: cl.quota_id in (select quota_id from cn_calc_sub_quotas_all