eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CN_SRP_PMT_PLANS_PVT dependencies on CN_SRP_PMT_PLANS

Line 1: PACKAGE BODY CN_SRP_PMT_PLANS_PVT AS

1: PACKAGE BODY CN_SRP_PMT_PLANS_PVT AS
2: /* $Header: cnvsppab.pls 120.26 2006/10/05 13:49:41 chanthon noship $ */
3:
4: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PMT_PLANS_PVT';
5: G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvsppab.pls';

Line 4: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PMT_PLANS_PVT';

1: PACKAGE BODY CN_SRP_PMT_PLANS_PVT AS
2: /* $Header: cnvsppab.pls 120.26 2006/10/05 13:49:41 chanthon noship $ */
3:
4: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PMT_PLANS_PVT';
5: G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvsppab.pls';
6:
7: procedure get_date_range_intersect(a_start_date in date, a_end_date in date,
8: b_start_date in date, b_end_date in date,

Line 37: l_start_date cn_srp_pmt_plans.start_date%TYPE;

33: p_srp_role_id IN NUMBER,
34: x_start_date OUT NOCOPY DATE,
35: x_end_date OUT NOCOPY DATE) IS
36:
37: l_start_date cn_srp_pmt_plans.start_date%TYPE;
38: l_end_date cn_srp_pmt_plans.start_date%TYPE;
39:
40: l_res_start_date cn_srp_pmt_plans.start_date%TYPE;
41: l_res_end_date cn_srp_pmt_plans.start_date%TYPE;

Line 38: l_end_date cn_srp_pmt_plans.start_date%TYPE;

34: x_start_date OUT NOCOPY DATE,
35: x_end_date OUT NOCOPY DATE) IS
36:
37: l_start_date cn_srp_pmt_plans.start_date%TYPE;
38: l_end_date cn_srp_pmt_plans.start_date%TYPE;
39:
40: l_res_start_date cn_srp_pmt_plans.start_date%TYPE;
41: l_res_end_date cn_srp_pmt_plans.start_date%TYPE;
42:

Line 40: l_res_start_date cn_srp_pmt_plans.start_date%TYPE;

36:
37: l_start_date cn_srp_pmt_plans.start_date%TYPE;
38: l_end_date cn_srp_pmt_plans.start_date%TYPE;
39:
40: l_res_start_date cn_srp_pmt_plans.start_date%TYPE;
41: l_res_end_date cn_srp_pmt_plans.start_date%TYPE;
42:
43: l_role_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
44: l_role_pp_end_date cn_srp_pmt_plans.start_date%TYPE;

Line 41: l_res_end_date cn_srp_pmt_plans.start_date%TYPE;

37: l_start_date cn_srp_pmt_plans.start_date%TYPE;
38: l_end_date cn_srp_pmt_plans.start_date%TYPE;
39:
40: l_res_start_date cn_srp_pmt_plans.start_date%TYPE;
41: l_res_end_date cn_srp_pmt_plans.start_date%TYPE;
42:
43: l_role_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
44: l_role_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
45:

Line 43: l_role_pp_start_date cn_srp_pmt_plans.start_date%TYPE;

39:
40: l_res_start_date cn_srp_pmt_plans.start_date%TYPE;
41: l_res_end_date cn_srp_pmt_plans.start_date%TYPE;
42:
43: l_role_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
44: l_role_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
45:
46: l_srp_role_start_date cn_srp_pmt_plans.start_date%TYPE;
47: l_srp_role_end_date cn_srp_pmt_plans.start_date%TYPE;

Line 44: l_role_pp_end_date cn_srp_pmt_plans.start_date%TYPE;

40: l_res_start_date cn_srp_pmt_plans.start_date%TYPE;
41: l_res_end_date cn_srp_pmt_plans.start_date%TYPE;
42:
43: l_role_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
44: l_role_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
45:
46: l_srp_role_start_date cn_srp_pmt_plans.start_date%TYPE;
47: l_srp_role_end_date cn_srp_pmt_plans.start_date%TYPE;
48:

Line 46: l_srp_role_start_date cn_srp_pmt_plans.start_date%TYPE;

42:
43: l_role_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
44: l_role_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
45:
46: l_srp_role_start_date cn_srp_pmt_plans.start_date%TYPE;
47: l_srp_role_end_date cn_srp_pmt_plans.start_date%TYPE;
48:
49: l_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
50: l_pp_end_date cn_srp_pmt_plans.start_date%TYPE;

Line 47: l_srp_role_end_date cn_srp_pmt_plans.start_date%TYPE;

43: l_role_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
44: l_role_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
45:
46: l_srp_role_start_date cn_srp_pmt_plans.start_date%TYPE;
47: l_srp_role_end_date cn_srp_pmt_plans.start_date%TYPE;
48:
49: l_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
50: l_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
51:

Line 49: l_pp_start_date cn_srp_pmt_plans.start_date%TYPE;

45:
46: l_srp_role_start_date cn_srp_pmt_plans.start_date%TYPE;
47: l_srp_role_end_date cn_srp_pmt_plans.start_date%TYPE;
48:
49: l_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
50: l_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
51:
52: l_org_id NUMBER;
53: l_salesrep_id NUMBER;

Line 50: l_pp_end_date cn_srp_pmt_plans.start_date%TYPE;

46: l_srp_role_start_date cn_srp_pmt_plans.start_date%TYPE;
47: l_srp_role_end_date cn_srp_pmt_plans.start_date%TYPE;
48:
49: l_pp_start_date cn_srp_pmt_plans.start_date%TYPE;
50: l_pp_end_date cn_srp_pmt_plans.start_date%TYPE;
51:
52: l_org_id NUMBER;
53: l_salesrep_id NUMBER;
54: l_pmt_plan_id NUMBER;

Line 154: FROM cn_srp_pmt_plans_all

150: x_loading_status := p_loading_status ;
151:
152: -- Check if already exist( duplicate assigned,unique key violation check)
153: SELECT COUNT(1) INTO l_dummy
154: FROM cn_srp_pmt_plans_all
155: WHERE salesrep_id = p_salesrep_id
156: AND pmt_plan_id = p_pmt_plan_id
157: AND start_date = p_start_date
158: AND ( (end_date = p_end_date) OR

Line 286: FROM cn_srp_pmt_plans_all cspp, cn_pmt_plans_all cpp

282: -- Ensure pmt plan assignments do not overlap each other in same role
283: -- only 1 payment plan is active at each date
284: -- Returns an error message and raises an exception if overlap occurs.
285: SELECT COUNT(1) INTO l_dummy
286: FROM cn_srp_pmt_plans_all cspp, cn_pmt_plans_all cpp
287: WHERE (((cspp.end_date IS NULL)
288: AND (p_end_date IS NULL))
289: OR
290: ((cspp.end_date IS NULL) AND

Line 326: /* credit type ID in cn_srp_pmt_plans is Obsolete!

322: x_loading_status := 'CN_SRP_PMT_PLAN_OVERLAPS';
323: RAISE FND_API.G_EXC_ERROR ;
324: END IF;
325:
326: /* credit type ID in cn_srp_pmt_plans is Obsolete!
327:
328: -- Check the credit types of plan element assigned to this salesrep within this date range
329: -- Added by Zack Sep 12th, 2001
330: -- Modified on Oct. 8th, checking for credit type compatibility.

Line 397: cn_payruns_all prun, cn_srp_pmt_plans_all spp

393:
394: CURSOR get_del_payruns IS
395: SELECT DISTINCT prun.name
396: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
397: cn_payruns_all prun, cn_srp_pmt_plans_all spp
398: WHERE w.salesrep_id = spp.salesrep_id
399: AND w.quota_id is null
400: AND prun.pay_period_id = prd.period_id
401: AND prun.org_id = prd.org_id

Line 415: cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp

411:
412: CURSOR get_paid_del_payruns IS
413: SELECT 'ERROR' as estatus
414: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
415: cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp
416: WHERE w.salesrep_id = spp.salesrep_id
417: AND w.quota_id is null
418: AND prun.pay_period_id = prd.period_id
419: AND prun.org_id = prd.org_id

Line 435: cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp

431:
432: CURSOR get_adj_del_payruns IS
433: SELECT 'ERROR' as estatus
434: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
435: cn_payruns_all prun, cn_srp_pmt_plans_all spp,cn_pmt_plans_all pp
436: -- cn_payment_transactions pt
437: WHERE w.salesrep_id = spp.salesrep_id
438: AND w.quota_id is null
439: AND prun.pay_period_id = prd.period_id

Line 507: FROM cn_srp_pmt_plans_all

503: AND prd.end_date > l_fixed_end_date;
504:
505: CURSOR get_date_range(param_srp_pmt_plan_id NUMBER) IS
506: SELECT start_date, Nvl(end_date, l_end_of_time) as end_date
507: FROM cn_srp_pmt_plans_all
508: WHERE srp_pmt_plan_id = param_srp_pmt_plan_id;
509:
510: --Added by Christina------------------------------------------------------------
511: -- This cursor returns an error status if there are any paid/unpaid worksheets

Line 518: cn_payruns_all prun, cn_srp_pmt_plans_all spp

514:
515: CURSOR get_adj_upd_payruns IS
516: SELECT 'ERROR' as estatus
517: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
518: cn_payruns_all prun, cn_srp_pmt_plans_all spp
519: -- cn_payment_transactions pt
520: WHERE w.salesrep_id = spp.salesrep_id
521: AND w.quota_id is null
522: AND prun.pay_period_id = prd.period_id

Line 829: p_source_object_code => 'CN_SRP_PMT_PLANS',

825: x_return_status => x_return_status,
826: x_msg_count => x_msg_count,
827: x_msg_data => x_msg_data,
828: p_source_object_id => p_srp_pmt_plan_id,
829: p_source_object_code => 'CN_SRP_PMT_PLANS',
830: p_notes => p_msg,
831: p_notes_detail => p_msg,
832: p_note_type => 'CN_SYSGEN', -- for system generated
833: x_jtf_note_id => x_note_id -- returned

Line 965: cn_srp_pmt_plans_pkg.insert_row

961: from cn_pmt_plans_all
962: where pmt_plan_id = p_pmt_plan_assign_rec.pmt_plan_id;
963:
964:
965: cn_srp_pmt_plans_pkg.insert_row
966: ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
967: ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
968: ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
969: ,x_org_id => p_pmt_plan_assign_rec.org_id

Line 1021: FROM cn_srp_pmt_plans_all

1017:
1018: -- populate ovn
1019: SELECT object_version_number
1020: INTO p_pmt_plan_assign_rec.object_version_number
1021: FROM cn_srp_pmt_plans_all
1022: WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1023:
1024: -- End of API body
1025:

Line 1098: FROM cn_srp_pmt_plans

1094: l_credit_type_id NUMBER;
1095:
1096: CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
1097: SELECT *
1098: FROM cn_srp_pmt_plans
1099: WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
1100:
1101: l_oldrec spp_csr%ROWTYPE;
1102: l_oldname cn_pmt_plans.name%TYPE;

Line 1223: cn_srp_pmt_plans_pkg.delete_row

1219: RAISE FND_API.G_EXC_ERROR ;
1220: END IF;
1221: */
1222: -- Delete record
1223: cn_srp_pmt_plans_pkg.delete_row
1224: (x_srp_pmt_plan_id => l_oldrec.srp_pmt_plan_id);
1225:
1226: -- Insert new record w/ validation
1227: cn_srp_pmt_plans_pkg.insert_row

Line 1227: cn_srp_pmt_plans_pkg.insert_row

1223: cn_srp_pmt_plans_pkg.delete_row
1224: (x_srp_pmt_plan_id => l_oldrec.srp_pmt_plan_id);
1225:
1226: -- Insert new record w/ validation
1227: cn_srp_pmt_plans_pkg.insert_row
1228: ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
1229: ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
1230: ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
1231: ,x_org_id => p_pmt_plan_assign_rec.org_id

Line 1249: update cn_srp_pmt_plans_all

1245: ,x_role_pmt_plan_id => p_pmt_plan_assign_rec.role_pmt_plan_id
1246: ,x_lock_flag => p_pmt_plan_assign_rec.lock_flag);
1247:
1248: -- sync ID back
1249: update cn_srp_pmt_plans_all
1250: set srp_pmt_plan_id = l_oldrec.srp_pmt_plan_id
1251: where srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1252:
1253: p_pmt_plan_assign_rec.srp_pmt_plan_id := l_oldrec.srp_pmt_plan_id;

Line 1289: -- Update pmt plan assignment into cn_srp_pmt_plans

1285: END IF;
1286:
1287: END IF;
1288: */
1289: -- Update pmt plan assignment into cn_srp_pmt_plans
1290: cn_srp_pmt_plans_pkg.update_row
1291: ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
1292: ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
1293: ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id

Line 1290: cn_srp_pmt_plans_pkg.update_row

1286:
1287: END IF;
1288: */
1289: -- Update pmt plan assignment into cn_srp_pmt_plans
1290: cn_srp_pmt_plans_pkg.update_row
1291: ( x_srp_pmt_plan_id => p_pmt_plan_assign_rec.srp_pmt_plan_id
1292: ,x_pmt_plan_id => p_pmt_plan_assign_rec.pmt_plan_id
1293: ,x_salesrep_id => p_pmt_plan_assign_rec.salesrep_id
1294: ,x_org_id => p_pmt_plan_assign_rec.org_id

Line 1311: UPDATE cn_srp_pmt_plans_all

1307: );
1308:
1309: -- if the lock_flag is being set, then blow away role_pmt_plan_id
1310: IF p_pmt_plan_assign_rec.lock_flag = 'Y' THEN
1311: UPDATE cn_srp_pmt_plans_all
1312: SET role_pmt_plan_id = NULL,
1313: srp_role_id = NULL
1314: WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1315: END IF;

Line 1364: FROM cn_srp_pmt_plans_all

1360:
1361: -- pick up new object version number
1362: SELECT object_version_number
1363: INTO p_pmt_plan_assign_rec.object_version_number
1364: FROM cn_srp_pmt_plans_all
1365: WHERE srp_pmt_plan_id = p_pmt_plan_assign_rec.srp_pmt_plan_id;
1366: -- End of API body.
1367:
1368: -- Standard check of p_commit.

Line 1435: FROM cn_srp_pmt_plans

1431: l_api_name CONSTANT VARCHAR2(30) := 'Valid_Delete_Srp_Pmt_Plan';
1432:
1433: CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
1434: SELECT *
1435: FROM cn_srp_pmt_plans
1436: WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
1437:
1438: l_spp_rec spp_csr%ROWTYPE;
1439:

Line 1539: from cn_srp_pmt_plans_all spp, cn_pmt_plans_all p, cn_role_pmt_plans_all rpp, cn_roles r

1535: l_pmt_plan_assign_rec pmt_plan_assign_rec;
1536:
1537: CURSOR spp_info_cur IS
1538: select p.name, r.name role_name, spp.start_date, spp.end_date, spp.minimum_amount, spp.maximum_amount, spp.lock_flag, spp.salesrep_id
1539: from cn_srp_pmt_plans_all spp, cn_pmt_plans_all p, cn_role_pmt_plans_all rpp, cn_roles r
1540: where spp.srp_pmt_plan_id = p_srp_pmt_plan_id
1541: and spp.role_pmt_plan_id = rpp.role_pmt_plan_id(+)
1542: and spp.pmt_plan_id = p.pmt_plan_id
1543: and rpp.role_id = r.role_id(+);

Line 1611: cn_srp_pmt_plans_pkg.delete_row

1607: x_jtf_note_id => x_note_id -- returned
1608: );
1609:
1610: -- Delete record
1611: cn_srp_pmt_plans_pkg.delete_row
1612: (x_srp_pmt_plan_id => p_srp_pmt_plan_id);
1613:
1614: -- raise business event
1615: l_pmt_plan_assign_rec.srp_pmt_plan_id := p_srp_pmt_plan_id;

Line 1698: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;

1694: l_api_version CONSTANT NUMBER := 1.0;
1695: l_return_status VARCHAR2(2000);
1696: l_msg_count NUMBER;
1697: l_msg_data VARCHAR2(2000);
1698: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
1699: l_loading_status VARCHAR2(2000);
1700:
1701: newrec pmt_plan_assign_rec;
1702: l_salesrep_id cn_salesreps.salesrep_id%TYPE;

Line 1711: l_start_date cn_srp_pmt_plans.start_date%TYPE;

1707: l_pp_start_date cn_pmt_plans.start_date%TYPE;
1708: l_pp_end_date cn_pmt_plans.end_date%TYPE;
1709: l_srp_start_date cn_srp_roles.start_date%TYPE;
1710: l_srp_end_date cn_pmt_plans.end_date%TYPE;
1711: l_start_date cn_srp_pmt_plans.start_date%TYPE;
1712: l_end_date cn_srp_pmt_plans.start_date%TYPE;
1713:
1714: BEGIN
1715:

Line 1712: l_end_date cn_srp_pmt_plans.start_date%TYPE;

1708: l_pp_end_date cn_pmt_plans.end_date%TYPE;
1709: l_srp_start_date cn_srp_roles.start_date%TYPE;
1710: l_srp_end_date cn_pmt_plans.end_date%TYPE;
1711: l_start_date cn_srp_pmt_plans.start_date%TYPE;
1712: l_end_date cn_srp_pmt_plans.start_date%TYPE;
1713:
1714: BEGIN
1715:
1716: -- Standard Start of API savepoint

Line 1891: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;

1887: l_api_version CONSTANT NUMBER := 1.0;
1888: l_return_status VARCHAR2(2000);
1889: l_msg_count NUMBER;
1890: l_msg_data VARCHAR2(2000);
1891: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
1892: l_loading_status VARCHAR2(2000);
1893: l_count NUMBER;
1894: l_count_srp_pmt_plan NUMBER;
1895:

Line 1908: l_start_date_old cn_srp_pmt_plans.start_date%TYPE;

1904: l_pp_end_date_new cn_pmt_plans.end_date%TYPE;
1905: l_srp_start_date_new cn_srp_roles.start_date%TYPE;
1906: --l_srp_end_date_new cn_pmt_plans.end_date%TYPE;
1907: l_srp_end_date_new cn_srp_roles.end_date%TYPE;
1908: l_start_date_old cn_srp_pmt_plans.start_date%TYPE;
1909: l_start_date_new cn_srp_pmt_plans.start_date%TYPE;
1910: l_end_date_old cn_srp_pmt_plans.start_date%TYPE;
1911: l_end_date_new cn_srp_pmt_plans.start_date%TYPE;
1912: l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;

Line 1909: l_start_date_new cn_srp_pmt_plans.start_date%TYPE;

1905: l_srp_start_date_new cn_srp_roles.start_date%TYPE;
1906: --l_srp_end_date_new cn_pmt_plans.end_date%TYPE;
1907: l_srp_end_date_new cn_srp_roles.end_date%TYPE;
1908: l_start_date_old cn_srp_pmt_plans.start_date%TYPE;
1909: l_start_date_new cn_srp_pmt_plans.start_date%TYPE;
1910: l_end_date_old cn_srp_pmt_plans.start_date%TYPE;
1911: l_end_date_new cn_srp_pmt_plans.start_date%TYPE;
1912: l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
1913: l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;

Line 1910: l_end_date_old cn_srp_pmt_plans.start_date%TYPE;

1906: --l_srp_end_date_new cn_pmt_plans.end_date%TYPE;
1907: l_srp_end_date_new cn_srp_roles.end_date%TYPE;
1908: l_start_date_old cn_srp_pmt_plans.start_date%TYPE;
1909: l_start_date_new cn_srp_pmt_plans.start_date%TYPE;
1910: l_end_date_old cn_srp_pmt_plans.start_date%TYPE;
1911: l_end_date_new cn_srp_pmt_plans.start_date%TYPE;
1912: l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
1913: l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
1914:

Line 1911: l_end_date_new cn_srp_pmt_plans.start_date%TYPE;

1907: l_srp_end_date_new cn_srp_roles.end_date%TYPE;
1908: l_start_date_old cn_srp_pmt_plans.start_date%TYPE;
1909: l_start_date_new cn_srp_pmt_plans.start_date%TYPE;
1910: l_end_date_old cn_srp_pmt_plans.start_date%TYPE;
1911: l_end_date_new cn_srp_pmt_plans.start_date%TYPE;
1912: l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
1913: l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
1914:
1915: --Added payment group code for bug 3560026 by Julia Huang on 4/7/2004.

Line 1955: --Added to check if the role_pay_group_id is existing in cn_srp_pmt_plans

1951: from cn_srp_roles
1952: where srp_role_id = p_srp_role_id
1953: and org_id = l_org_id;
1954:
1955: --Added to check if the role_pay_group_id is existing in cn_srp_pmt_plans
1956: select count(*) into l_count from cn_srp_pmt_plans
1957: where salesrep_id = l_salesrep_id_old
1958: AND srp_role_id = p_srp_role_id
1959: AND role_pmt_plan_id = p_role_pmt_plan_id;

Line 1956: select count(*) into l_count from cn_srp_pmt_plans

1952: where srp_role_id = p_srp_role_id
1953: and org_id = l_org_id;
1954:
1955: --Added to check if the role_pay_group_id is existing in cn_srp_pmt_plans
1956: select count(*) into l_count from cn_srp_pmt_plans
1957: where salesrep_id = l_salesrep_id_old
1958: AND srp_role_id = p_srp_role_id
1959: AND role_pmt_plan_id = p_role_pmt_plan_id;
1960:

Line 1965: --2.Full Table Scan. Added 1 index: create index cn_srp_pmt_plans_n1 ON cn_srp_pmt_plans_all(srp_role_id,org_id)

1961: IF (l_count <> 0)
1962: THEN
1963: --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
1964: --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1965: --2.Full Table Scan. Added 1 index: create index cn_srp_pmt_plans_n1 ON cn_srp_pmt_plans_all(srp_role_id,org_id)
1966: select spp.start_date, spp.end_date, spp.salesrep_id,
1967: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
1968: into l_start_date_old, l_end_date_old, l_salesrep_id_old,
1969: l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id

Line 1970: from cn_srp_pmt_plans_all spp, cn_pmt_plans_all cpp, cn_role_pmt_plans_all crpp

1966: select spp.start_date, spp.end_date, spp.salesrep_id,
1967: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
1968: into l_start_date_old, l_end_date_old, l_salesrep_id_old,
1969: l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
1970: from cn_srp_pmt_plans_all spp, cn_pmt_plans_all cpp, cn_role_pmt_plans_all crpp
1971: where spp.srp_role_id = p_srp_role_id
1972: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1973: AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
1974: AND cpp.pmt_plan_id = spp.pmt_plan_id;

Line 1995: --Added to check if the a record exists in cn_srp_pmt_plans for the dates passed for bug 3147026

1991: from cn_srp_roles
1992: where srp_role_id = p_srp_role_id
1993: and org_id = l_org_id;
1994:
1995: --Added to check if the a record exists in cn_srp_pmt_plans for the dates passed for bug 3147026
1996: /* Commented out by Julia Huang for bug 3560026 by Julia Huang on 4/7/2004
1997: select count(*) into l_count_srp_pmt_plan from cn_srp_pmt_plans where salesrep_id=l_salesrep_id_old
1998: and ((l_pp_start_date_new between start_date and nvl(end_date,l_null_date))
1999: or (nvl(l_pp_end_date_new,l_null_date) between start_date and nvl(end_date,l_null_date)));

Line 1997: select count(*) into l_count_srp_pmt_plan from cn_srp_pmt_plans where salesrep_id=l_salesrep_id_old

1993: and org_id = l_org_id;
1994:
1995: --Added to check if the a record exists in cn_srp_pmt_plans for the dates passed for bug 3147026
1996: /* Commented out by Julia Huang for bug 3560026 by Julia Huang on 4/7/2004
1997: select count(*) into l_count_srp_pmt_plan from cn_srp_pmt_plans where salesrep_id=l_salesrep_id_old
1998: and ((l_pp_start_date_new between start_date and nvl(end_date,l_null_date))
1999: or (nvl(l_pp_end_date_new,l_null_date) between start_date and nvl(end_date,l_null_date)));
2000: */
2001: SELECT COUNT(*) INTO l_count_srp_pmt_plan

Line 2002: FROM cn_srp_pmt_plans cspp, cn_pmt_plans cpp

1998: and ((l_pp_start_date_new between start_date and nvl(end_date,l_null_date))
1999: or (nvl(l_pp_end_date_new,l_null_date) between start_date and nvl(end_date,l_null_date)));
2000: */
2001: SELECT COUNT(*) INTO l_count_srp_pmt_plan
2002: FROM cn_srp_pmt_plans cspp, cn_pmt_plans cpp
2003: WHERE cspp.salesrep_id = l_salesrep_id_old
2004: AND ((l_pp_start_date_new BETWEEN cspp.start_date AND NVL(cspp.end_date,l_pp_start_date_new))
2005: OR (NVL(l_pp_end_date_new,l_pp_start_date_new) BETWEEN cspp.start_date AND NVL(cspp.end_date,l_pp_end_date_new)))
2006: AND cspp.pmt_plan_id = cpp.pmt_plan_id

Line 2045: cn_payruns_all prun, cn_srp_pmt_plans_all spp

2041: if (l_count>0) then
2042:
2043: SELECT count(*) into l_worksheets
2044: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2045: cn_payruns_all prun, cn_srp_pmt_plans_all spp
2046: WHERE w.salesrep_id = spp.salesrep_id
2047: AND w.quota_id is null
2048: AND prun.pay_period_id = prd.period_id
2049: AND prun.org_id = prd.org_id

Line 2098: FROM cn_srp_pmt_plans

2094: THEN
2095:
2096: SELECT count(*)
2097: INTO l_count_srp_pmt_plan
2098: FROM cn_srp_pmt_plans
2099: WHERE salesrep_id = l_salesrep_id_new
2100: AND org_id = l_org_id
2101: AND ((l_start_date_new between start_date and nvl(end_date,l_end_of_time))
2102: OR (nvl(l_end_date_new,l_end_of_time) between

Line 2230: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;

2226: l_api_version CONSTANT NUMBER := 1.0;
2227: l_return_status VARCHAR2(2000);
2228: l_msg_count NUMBER;
2229: l_msg_data VARCHAR2(2000);
2230: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
2231: l_loading_status VARCHAR2(2000);
2232:
2233: newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
2234: l_salesrep_id cn_salesreps.salesrep_id%TYPE;

Line 2233: newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;

2229: l_msg_data VARCHAR2(2000);
2230: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
2231: l_loading_status VARCHAR2(2000);
2232:
2233: newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
2234: l_salesrep_id cn_salesreps.salesrep_id%TYPE;
2235: l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
2236: l_min_amt cn_pmt_plans.minimum_amount%TYPE;
2237: l_max_amt cn_pmt_plans.maximum_amount%TYPE;

Line 2242: l_start_date cn_srp_pmt_plans.start_date%TYPE;

2238: l_pp_start_date cn_pmt_plans.start_date%TYPE;
2239: l_pp_end_date cn_pmt_plans.end_date%TYPE;
2240: l_srp_start_date cn_srp_roles.start_date%TYPE;
2241: l_srp_end_date cn_pmt_plans.end_date%TYPE;
2242: l_start_date cn_srp_pmt_plans.start_date%TYPE;
2243: l_end_date cn_srp_pmt_plans.start_date%TYPE;
2244: l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
2245: l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
2246:

Line 2243: l_end_date cn_srp_pmt_plans.start_date%TYPE;

2239: l_pp_end_date cn_pmt_plans.end_date%TYPE;
2240: l_srp_start_date cn_srp_roles.start_date%TYPE;
2241: l_srp_end_date cn_pmt_plans.end_date%TYPE;
2242: l_start_date cn_srp_pmt_plans.start_date%TYPE;
2243: l_end_date cn_srp_pmt_plans.start_date%TYPE;
2244: l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
2245: l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
2246:
2247:

Line 2250: FROM cn_srp_pmt_plans

2246:
2247:
2248: CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
2249: SELECT *
2250: FROM cn_srp_pmt_plans
2251: WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
2252:
2253: l_spp_rec spp_csr%ROWTYPE;
2254: l_dummy NUMBER;

Line 2286: --2.Full Table Scan. Added 1 index: create index cn_srp_pmt_plans_n1 ON cn_srp_pmt_plans_all(srp_role_id,org_id)

2282: BEGIN
2283:
2284: --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
2285: --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2286: --2.Full Table Scan. Added 1 index: create index cn_srp_pmt_plans_n1 ON cn_srp_pmt_plans_all(srp_role_id,org_id)
2287: select spp.start_date, spp.end_date, spp.salesrep_id,
2288: cpp.minimum_amount, cpp.maximum_amount,
2289: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
2290: into l_start_date, l_end_date, l_salesrep_id,

Line 2293: from cn_srp_pmt_plans spp, cn_pmt_plans cpp, cn_role_pmt_plans crpp

2289: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
2290: into l_start_date, l_end_date, l_salesrep_id,
2291: l_min_amt, l_max_amt,
2292: l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
2293: from cn_srp_pmt_plans spp, cn_pmt_plans cpp, cn_role_pmt_plans crpp
2294: where spp.srp_role_id = p_srp_role_id
2295: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2296: AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
2297: AND cpp.pmt_plan_id = spp.pmt_plan_id;

Line 2353: update cn_srp_pmt_plans set srp_role_id = null, role_pmt_plan_id = null

2349: IF l_dummy > 0 then
2350: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2351: -- FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PMT_PLAN_USED');
2352: -- FND_MSG_PUB.Add;
2353: update cn_srp_pmt_plans set srp_role_id = null, role_pmt_plan_id = null
2354: where srp_pmt_plan_id = l_srp_pmt_plan_id;
2355:
2356: ElSIF l_dummy = 0 THEN
2357:

Line 2433: END cn_srp_pmt_plans_pvt;

2429: );
2430:
2431: END Delete_Mass_Asgn_Srp_Pmt_Plan;
2432:
2433: END cn_srp_pmt_plans_pvt;