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.29.12020000.2 2012/07/25 09:18:44 swpoddar ship $ */
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.29.12020000.2 2012/07/25 09:18:44 swpoddar ship $ */
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 830: p_source_object_code => 'CN_SRP_PMT_PLANS',

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

Line 966: cn_srp_pmt_plans_pkg.insert_row

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

Line 1022: FROM cn_srp_pmt_plans_all

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

Line 1099: FROM cn_srp_pmt_plans

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

Line 1224: cn_srp_pmt_plans_pkg.delete_row

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

Line 1228: cn_srp_pmt_plans_pkg.insert_row

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

Line 1250: update cn_srp_pmt_plans_all

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

Line 1290: -- Update pmt plan assignment into cn_srp_pmt_plans

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

Line 1291: cn_srp_pmt_plans_pkg.update_row

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

Line 1312: UPDATE cn_srp_pmt_plans_all

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

Line 1365: FROM cn_srp_pmt_plans_all

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

Line 1436: FROM cn_srp_pmt_plans

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

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

1536: l_pmt_plan_assign_rec pmt_plan_assign_rec;
1537:
1538: CURSOR spp_info_cur IS
1539: 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
1540: from cn_srp_pmt_plans_all spp, cn_pmt_plans_all p, cn_role_pmt_plans_all rpp, cn_roles r
1541: where spp.srp_pmt_plan_id = p_srp_pmt_plan_id
1542: and spp.role_pmt_plan_id = rpp.role_pmt_plan_id(+)
1543: and spp.pmt_plan_id = p.pmt_plan_id
1544: and rpp.role_id = r.role_id(+);

Line 1612: cn_srp_pmt_plans_pkg.delete_row

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

Line 1699: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;

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

Line 1712: l_start_date cn_srp_pmt_plans.start_date%TYPE;

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

Line 1713: l_end_date cn_srp_pmt_plans.start_date%TYPE;

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

Line 1892: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;

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

Line 1909: l_start_date_old cn_srp_pmt_plans.start_date%TYPE;

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

Line 1910: l_start_date_new cn_srp_pmt_plans.start_date%TYPE;

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

Line 1911: l_end_date_old cn_srp_pmt_plans.start_date%TYPE;

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

Line 1912: l_end_date_new cn_srp_pmt_plans.start_date%TYPE;

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

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

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

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

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

Line 1966: --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)

1962: IF (l_count <> 0)
1963: THEN
1964: --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
1965: --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
1966: --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)
1967: select spp.start_date, spp.end_date, spp.salesrep_id,
1968: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
1969: into l_start_date_old, l_end_date_old, l_salesrep_id_old,
1970: l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id

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

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

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

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

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

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

Line 2003: FROM cn_srp_pmt_plans cspp, cn_pmt_plans cpp

1999: and ((l_pp_start_date_new between start_date and nvl(end_date,l_null_date))
2000: or (nvl(l_pp_end_date_new,l_null_date) between start_date and nvl(end_date,l_null_date)));
2001: */
2002: SELECT COUNT(*) INTO l_count_srp_pmt_plan
2003: FROM cn_srp_pmt_plans cspp, cn_pmt_plans cpp
2004: WHERE cspp.salesrep_id = l_salesrep_id_old
2005: AND ((l_pp_start_date_new BETWEEN cspp.start_date AND NVL(cspp.end_date,l_pp_start_date_new))
2006: 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)))
2007: AND cspp.pmt_plan_id = cpp.pmt_plan_id

Line 2047: cn_payruns_all prun, cn_srp_pmt_plans_all spp

2043: /* commented below code by Naren to fix bug 12821986
2044:
2045: SELECT count(*) into l_worksheets
2046: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2047: cn_payruns_all prun, cn_srp_pmt_plans_all spp
2048: WHERE w.salesrep_id = spp.salesrep_id
2049: AND w.quota_id is null
2050: AND prun.pay_period_id = prd.period_id
2051: AND prun.org_id = prd.org_id

Line 2069: cn_payruns_all prun, cn_srp_pmt_plans_all spp

2065: /* Added by Naren to fix bug 12821986*/
2066:
2067: SELECT count(*) into l_worksheets
2068: FROM cn_payment_worksheets_all W, cn_period_statuses_all prd,
2069: cn_payruns_all prun, cn_srp_pmt_plans_all spp
2070: WHERE w.salesrep_id = spp.salesrep_id
2071: AND w.quota_id is null
2072: AND prun.pay_period_id = prd.period_id
2073: AND prun.org_id = prd.org_id

Line 2124: FROM cn_srp_pmt_plans

2120: THEN
2121:
2122: SELECT count(*)
2123: INTO l_count_srp_pmt_plan
2124: FROM cn_srp_pmt_plans
2125: WHERE salesrep_id = l_salesrep_id_new
2126: AND org_id = l_org_id
2127: AND ((l_start_date_new between start_date and nvl(end_date,l_end_of_time))
2128: OR (nvl(l_end_date_new,l_end_of_time) between

Line 2256: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;

2252: l_api_version CONSTANT NUMBER := 1.0;
2253: l_return_status VARCHAR2(2000);
2254: l_msg_count NUMBER;
2255: l_msg_data VARCHAR2(2000);
2256: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
2257: l_loading_status VARCHAR2(2000);
2258:
2259: newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
2260: l_salesrep_id cn_salesreps.salesrep_id%TYPE;

Line 2259: newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;

2255: l_msg_data VARCHAR2(2000);
2256: l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
2257: l_loading_status VARCHAR2(2000);
2258:
2259: newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
2260: l_salesrep_id cn_salesreps.salesrep_id%TYPE;
2261: l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
2262: l_min_amt cn_pmt_plans.minimum_amount%TYPE;
2263: l_max_amt cn_pmt_plans.maximum_amount%TYPE;

Line 2268: l_start_date cn_srp_pmt_plans.start_date%TYPE;

2264: l_pp_start_date cn_pmt_plans.start_date%TYPE;
2265: l_pp_end_date cn_pmt_plans.end_date%TYPE;
2266: l_srp_start_date cn_srp_roles.start_date%TYPE;
2267: l_srp_end_date cn_pmt_plans.end_date%TYPE;
2268: l_start_date cn_srp_pmt_plans.start_date%TYPE;
2269: l_end_date cn_srp_pmt_plans.start_date%TYPE;
2270: l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
2271: l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
2272:

Line 2269: l_end_date cn_srp_pmt_plans.start_date%TYPE;

2265: l_pp_end_date cn_pmt_plans.end_date%TYPE;
2266: l_srp_start_date cn_srp_roles.start_date%TYPE;
2267: l_srp_end_date cn_pmt_plans.end_date%TYPE;
2268: l_start_date cn_srp_pmt_plans.start_date%TYPE;
2269: l_end_date cn_srp_pmt_plans.start_date%TYPE;
2270: l_role_pp_start_date cn_role_pmt_plans.start_date%TYPE;
2271: l_role_pp_end_date cn_role_pmt_plans.end_date%TYPE;
2272:
2273:

Line 2276: FROM cn_srp_pmt_plans

2272:
2273:
2274: CURSOR spp_csr( l_srp_pmt_plan_id NUMBER ) IS
2275: SELECT *
2276: FROM cn_srp_pmt_plans
2277: WHERE srp_pmt_plan_id = l_srp_pmt_plan_id;
2278:
2279: l_spp_rec spp_csr%ROWTYPE;
2280: l_dummy NUMBER;

Line 2312: --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)

2308: BEGIN
2309:
2310: --Bug 3670276 by Julia Huang on 6/4/04 to avoid the following
2311: --1.Cartesian join. Line changed: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2312: --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)
2313: select spp.start_date, spp.end_date, spp.salesrep_id,
2314: cpp.minimum_amount, cpp.maximum_amount,
2315: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
2316: into l_start_date, l_end_date, l_salesrep_id,

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

2315: crpp.start_date, crpp.end_date, spp.srp_pmt_plan_id
2316: into l_start_date, l_end_date, l_salesrep_id,
2317: l_min_amt, l_max_amt,
2318: l_role_pp_start_date, l_role_pp_end_date, l_srp_pmt_plan_id
2319: from cn_srp_pmt_plans spp, cn_pmt_plans cpp, cn_role_pmt_plans crpp
2320: where spp.srp_role_id = p_srp_role_id
2321: AND spp.role_pmt_plan_id = crpp.role_pmt_plan_id --p_role_pmt_plan_id
2322: AND crpp.role_pmt_plan_id = p_role_pmt_plan_id
2323: AND cpp.pmt_plan_id = spp.pmt_plan_id;

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

2375: IF l_dummy > 0 then
2376: -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2377: -- FND_MESSAGE.SET_NAME ('CN' , 'CN_SRP_PMT_PLAN_USED');
2378: -- FND_MSG_PUB.Add;
2379: update cn_srp_pmt_plans set srp_role_id = null, role_pmt_plan_id = null
2380: where srp_pmt_plan_id = l_srp_pmt_plan_id;
2381:
2382: ElSIF l_dummy = 0 THEN
2383:

Line 2459: END cn_srp_pmt_plans_pvt;

2455: );
2456:
2457: END Delete_Mass_Asgn_Srp_Pmt_Plan;
2458:
2459: END cn_srp_pmt_plans_pvt;