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_PERIODS_PVT dependencies on CN_SRP_PERIODS

Line 1: PACKAGE BODY CN_SRP_PERIODS_PVT AS

1: PACKAGE BODY CN_SRP_PERIODS_PVT AS
2: /* $Header: cnvsprdb.pls 120.1.12000000.2 2007/08/06 21:21:11 jxsingh ship $ */
3:
4: -- Global variable
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PERIODS_PVT';

Line 5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PERIODS_PVT';

1: PACKAGE BODY CN_SRP_PERIODS_PVT AS
2: /* $Header: cnvsprdb.pls 120.1.12000000.2 2007/08/06 21:21:11 jxsingh ship $ */
3:
4: -- Global variable
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SRP_PERIODS_PVT';
6: G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvsprdb.pls';
7:
8: --| -----------------------------------------------------------------------+
9: --| Function : Get_Pay_Period

Line 77: --| cn_srp_periods

73:
74: --| -----------------------------------------------------------------------+
75: --| Procedure : Sync_Accum_Balances_Start_Pd
76: --| Desc : Procedure to update begin balance and summary records in
77: --| cn_srp_periods
78: --| Note : 2 prerequisite conditions:
79: --| 1) that periods have all been created and xtd records populated
80: --| 2) there exists a summary record (null quota ID) for each actual record
81: --| -----------------------------------------------------------------------+

Line 92: l_prev_quota cn_srp_periods.quota_id%TYPE := -1;

88: p_start_period_id IN NUMBER) IS
89:
90: l_prev_pd_id cn_period_statuses.period_id%TYPE := -1;
91: l_prev_year cn_period_statuses.period_year%TYPE := -1;
92: l_prev_quota cn_srp_periods.quota_id%TYPE := -1;
93: l_prev_spa cn_srp_periods.srp_plan_assign_id%TYPE := -1;
94: l_reset_balances boolean := false;
95: l_srp_period_id number;
96: l_cache_bal1_bbc number;

Line 93: l_prev_spa cn_srp_periods.srp_plan_assign_id%TYPE := -1;

89:
90: l_prev_pd_id cn_period_statuses.period_id%TYPE := -1;
91: l_prev_year cn_period_statuses.period_year%TYPE := -1;
92: l_prev_quota cn_srp_periods.quota_id%TYPE := -1;
93: l_prev_spa cn_srp_periods.srp_plan_assign_id%TYPE := -1;
94: l_reset_balances boolean := false;
95: l_srp_period_id number;
96: l_cache_bal1_bbc number;
97: l_cache_bal1_bbd number;

Line 108: SELECT /+ index (sp, CN_SRP_PERIODS_U2)/

104: l_cache_bal5_bbc number;
105: l_cache_bal5_bbd number;
106:
107: CURSOR get_bals IS
108: SELECT /*+ index (sp, CN_SRP_PERIODS_U2)*/
109: srp_period_id, sp.period_id, quota_id,
110: p.period_year, srp_plan_assign_id,
111: balance1_ctd, balance1_dtd,
112: balance1_bbc, balance1_bbd,

Line 121: FROM cn_srp_periods_all sp, cn_period_statuses_all p

117: balance4_ctd, balance4_dtd,
118: balance4_bbc, balance4_bbd,
119: balance5_ctd, balance5_dtd,
120: balance5_bbc, balance5_bbd
121: FROM cn_srp_periods_all sp, cn_period_statuses_all p
122: WHERE role_id = p_role_id
123: AND salesrep_id = p_salesrep_id
124: AND sp.org_id = p_org_id
125: AND credit_type_id = p_credit_type_id

Line 132: select /+ index (p2, CN_SRP_PERIODS_U2)/

128: AND sp.org_id = p.org_id
129: ORDER BY quota_id, sp.period_id;
130:
131: CURSOR get_summ_srp_periods IS
132: select /*+ index (p2, CN_SRP_PERIODS_U2)*/
133: p1.srp_period_id, p1.period_id,
134: nvl(sum(p2.balance1_ctd),0) balance1_ctd,
135: nvl(sum(p2.balance1_dtd),0) balance1_dtd,
136: nvl(sum(p2.balance2_ctd),0) balance2_ctd,

Line 156: from cn_srp_periods_all p1, cn_srp_periods_all p2,

152: nvl(sum(p2.balance4_bbd),0) balance4_bbd,
153: nvl(sum(p2.balance5_bbc),0) balance5_bbc,
154: nvl(sum(p2.balance5_bbd),0) balance5_bbd,
155: p.period_year
156: from cn_srp_periods_all p1, cn_srp_periods_all p2,
157: cn_period_statuses_all p
158: where p1.salesrep_id = p_salesrep_id
159: and p1.credit_type_id = p_credit_type_id
160: AND p1.org_id = p_org_id

Line 179: FROM cn_srp_periods_all sp

175: balance2_bbd, balance2_bbc,
176: balance3_bbd, balance3_bbc,
177: balance4_bbd, balance4_bbc,
178: balance5_bbd, balance5_bbc
179: FROM cn_srp_periods_all sp
180: WHERE role_id = -1
181: AND salesrep_id = p_salesrep_id
182: AND org_id = p_org_id
183: AND credit_type_id = p_credit_type_id

Line 215: update cn_srp_periods_all

211: end if;
212:
213: -- no need to update periods before start period
214: if b.period_id >= p_start_period_id then
215: update cn_srp_periods_all
216: set balance1_bbc = l_cache_bal1_bbc,
217: balance1_bbd = l_cache_bal1_bbd,
218: balance2_bbc = l_cache_bal2_bbc,
219: balance2_bbd = l_cache_bal2_bbd,

Line 259: update cn_srp_periods_all

255: l_cache_bal5_bbc := 0;
256: l_cache_bal5_bbd := 0;
257: for p in get_summ_srp_periods loop
258: if p.period_id >= p_start_period_id then
259: update cn_srp_periods_all
260: set balance1_ctd = p.balance1_ctd,
261: balance1_dtd = p.balance1_dtd,
262: balance2_ctd = p.balance2_ctd,
263: balance2_dtd = p.balance2_dtd,

Line 291: update cn_srp_periods_all

287: -- ***************************************
288: -- Bug5707688 is fixed by changing > to >=
289: -- ***************************************
290: if p.period_id >= p_start_period_id then
291: update cn_srp_periods_all
292: SET balance1_bbc = l_cache_bal1_bbc,
293: balance1_bbd = l_cache_bal1_bbd,
294: balance2_bbc = l_cache_bal2_bbc,
295: balance2_bbd = l_cache_bal2_bbd,

Line 313: update cn_srp_periods_all

309: open get_carry_bal(p.period_id);
310: fetch get_carry_bal into cb;
311: close get_carry_bal;
312:
313: update cn_srp_periods_all
314: SET balance1_bbc=l_cache_bal1_bbc - p.balance1_bbc + cb.balance1_bbc,
315: balance1_bbd=l_cache_bal1_bbd - p.balance1_bbd + cb.balance1_bbd,
316: balance2_bbc=l_cache_bal2_bbc - p.balance2_bbc + cb.balance2_bbc,
317: balance2_bbd=l_cache_bal2_bbd - p.balance2_bbd + cb.balance2_bbd,

Line 359: --| Desc : Procedure to create a new row in cn_srp_periods

355: END Sync_Accum_Balances;
356:
357: --| -----------------------------------------------------------------------+
358: --| Procedure : Create_Srp_Periods
359: --| Desc : Procedure to create a new row in cn_srp_periods
360: --| Note : This is called by srp_pay_group_assign
361: --| -----------------------------------------------------------------------+
362: PROCEDURE Create_Srp_Periods
363: (p_api_version IN NUMBER,

Line 399: --| Desc : Procedure to create a new row in cn_srp_periods for a new quota

395: END Create_Srp_Periods;
396:
397: --| -----------------------------------------------------------------------+
398: --| Procedure : Create_Srp_Periods_Per_Quota
399: --| Desc : Procedure to create a new row in cn_srp_periods for a new quota
400: --| Note : This is called by srp_pay_group_assign
401: --| -----------------------------------------------------------------------+
402: PROCEDURE Create_Srp_Periods_Per_Quota
403: (p_api_version IN NUMBER,

Line 423: l_srp_period_id cn_srp_periods.srp_period_id%TYPE;

419: l_api_name CONSTANT VARCHAR2(30) := 'Create_Srp_Periods';
420: l_api_version CONSTANT NUMBER := 1.0;
421:
422: l_pay_period_rec_tbl pay_period_rec_tbl_type;
423: l_srp_period_id cn_srp_periods.srp_period_id%TYPE;
424: l_min_period_id cn_srp_periods.period_id%TYPE;
425: l_dummy NUMBER;
426: l_org_id NUMBER;
427: l_pg_found BOOLEAN;

Line 424: l_min_period_id cn_srp_periods.period_id%TYPE;

420: l_api_version CONSTANT NUMBER := 1.0;
421:
422: l_pay_period_rec_tbl pay_period_rec_tbl_type;
423: l_srp_period_id cn_srp_periods.srp_period_id%TYPE;
424: l_min_period_id cn_srp_periods.period_id%TYPE;
425: l_dummy NUMBER;
426: l_org_id NUMBER;
427: l_pg_found BOOLEAN;
428:

Line 457: CURSOR get_summ_pds(c_credit_type_id cn_srp_periods.credit_type_id%TYPE) IS

453: WHERE comp_plan_id = p_comp_plan_id)
454: AND quota_id = nvl(p_quota_id, quota_id));
455:
456:
457: CURSOR get_summ_pds(c_credit_type_id cn_srp_periods.credit_type_id%TYPE) IS
458: select p.period_id, p.start_date, p.end_date
459: from cn_period_statuses_all p, cn_repositories_all r
460: where p.period_id >= l_min_period_id
461: and r.period_type_id = p.period_type_id

Line 465: and not exists (select 1 from cn_srp_periods_all

461: and r.period_type_id = p.period_type_id
462: and r.period_set_id = p.period_set_id
463: AND p.org_id = l_org_id
464: AND r.org_id = l_org_id
465: and not exists (select 1 from cn_srp_periods_all
466: where salesrep_id = p_salesrep_id and period_id = p.period_id
467: and role_id is null and quota_id is NULL
468: AND org_id = l_org_id
469: AND credit_type_id = c_credit_type_id)

Line 473: CURSOR get_carry_pds(c_credit_type_id cn_srp_periods.credit_type_id%TYPE) IS

469: AND credit_type_id = c_credit_type_id)
470:
471: order by 1;
472:
473: CURSOR get_carry_pds(c_credit_type_id cn_srp_periods.credit_type_id%TYPE) IS
474: select p.period_id, p.start_date, p.end_date
475: from cn_period_statuses_all p, cn_repositories_all r
476: where p.period_id >= l_min_period_id
477: and r.period_type_id = p.period_type_id

Line 481: and not exists (select 1 from cn_srp_periods_all

477: and r.period_type_id = p.period_type_id
478: and r.period_set_id = p.period_set_id
479: AND p.org_id = l_org_id
480: AND r.org_id = l_org_id
481: and not exists (select 1 from cn_srp_periods_all
482: where salesrep_id = p_salesrep_id and period_id = p.period_id
483: and role_id = -1 and quota_id = -1000
484: AND org_id = l_org_id
485: AND credit_type_id = c_credit_type_id)

Line 491: from cn_srp_periods_all

487:
488:
489: CURSOR get_srp_cts IS
490: select distinct credit_type_id
491: from cn_srp_periods_all
492: where salesrep_id = p_salesrep_id
493: AND org_id = l_org_id
494: and quota_id is not null
495: and credit_type_id is not null;

Line 567: FROM cn_srp_periods_all

563: EXIT WHEN c_quota_csr%NOTFOUND;
564:
565: FOR i IN 1 .. l_pay_period_rec_tbl.COUNT LOOP
566: SELECT count(1) INTO l_dummy
567: FROM cn_srp_periods_all
568: WHERE salesrep_id = p_salesrep_id
569: AND org_id = l_org_id
570: AND period_id = l_pay_period_rec_tbl(i).period_id
571: AND role_id = p_role_id

Line 581: cn_srp_periods_pkg.insert_row

577: FETCH c_get_spa INTO l_srp_plan_assign_id;
578: CLOSE c_get_spa;
579:
580: IF l_dummy = 0 THEN
581: cn_srp_periods_pkg.insert_row
582: (x_srp_period_id => l_srp_period_id
583: ,x_salesrep_id => p_salesrep_id
584: ,x_org_id => l_org_id
585: ,x_period_id => l_pay_period_rec_tbl(i).period_id

Line 601: update cn_srp_periods_all

597: ,x_last_update_login => FND_GLOBAL.LOGIN_ID
598: );
599: ELSE
600: -- records exist - update plan assign ID
601: update cn_srp_periods_all
602: set srp_plan_assign_id = l_srp_plan_assign_id,
603: start_date = l_pay_period_rec_tbl(i).start_date,
604: end_date = l_pay_period_rec_tbl(i).end_date
605: where salesrep_id = p_salesrep_id

Line 633: from cn_srp_periods_all

629: -- credit types
630: -- get min srp period ID
631: for ct in get_srp_cts loop
632: select min(period_id) into l_min_period_id
633: from cn_srp_periods_all
634: where salesrep_id = p_salesrep_id
635: AND org_id = l_org_id
636: and quota_id is not null
637: and credit_type_id = ct.credit_type_id;

Line 643: cn_srp_periods_pkg.insert_row

639: -- Bug 2690859
640: -- Add ct.credit_type_id to cursor get_summ_pds() so it'll create
641: -- summary record for different credit_type_id
642: for p in get_summ_pds(ct.credit_type_id) loop
643: cn_srp_periods_pkg.insert_row
644: (x_srp_period_id => l_srp_period_id
645: ,x_salesrep_id => p_salesrep_id
646: ,x_org_id => l_org_id
647: ,x_period_id => p.period_id

Line 664: cn_srp_periods_pkg.insert_row

660: );
661: end loop; -- periods
662:
663: for p in get_carry_pds(ct.credit_type_id) loop
664: cn_srp_periods_pkg.insert_row
665: (x_srp_period_id => l_srp_period_id
666: ,x_salesrep_id => p_salesrep_id
667: ,x_org_id => l_org_id
668: ,x_period_id => p.period_id

Line 766: --| Desc : Procedure to update row in cn_srp_periods, add deltas into it

762: END Create_Srp_Periods_Per_Quota;
763:
764: --| -----------------------------------------------------------------------+
765: --| Procedure : Update_Delta_Srp_Pds_No_Sync
766: --| Desc : Procedure to update row in cn_srp_periods, add deltas into it
767: --| -----------------------------------------------------------------------+
768:
769: PROCEDURE Update_Delta_Srp_Pds_No_Sync
770: (p_api_version IN NUMBER,

Line 801: UPDATE cn_srp_periods_all

797: x_loading_status := 'CN_UPDATED';
798: -- API body
799:
800: -- Update record's ctd and dtd
801: UPDATE cn_srp_periods_all
802: SET
803: balance1_ctd = (Nvl(balance1_ctd,0) +
804: Nvl(p_del_srp_prd_rec.del_balance1_ctd,0)),
805: balance1_dtd = (Nvl(balance1_dtd,0) +

Line 876: --| Desc : Procedure to update row in cn_srp_periods, add deltas into it

872: END Update_Delta_Srp_Pds_No_Sync;
873:
874: --| -----------------------------------------------------------------------+
875: --| Procedure : Update_Delta_Srp_Periods
876: --| Desc : Procedure to update row in cn_srp_periods, add deltas into it
877: --| Note : updates xtd and bbx columns and summary srp periods
878: --| -----------------------------------------------------------------------+
879:
880: PROCEDURE Update_Delta_Srp_Periods

Line 921: FROM cn_srp_periods_all

917: -- populate header info of delta srp period rec
918: BEGIN
919: SELECT salesrep_id, credit_type_id, role_id, org_id
920: INTO l_salesrep_id, l_credit_type_id, l_role_id, l_org_id
921: FROM cn_srp_periods_all
922: WHERE srp_period_id = p_del_srp_prd_rec.srp_period_id;
923: EXCEPTION
924: WHEN NO_DATA_FOUND THEN
925: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)

Line 1002: --| Desc : Procedure to update row in cn_srp_periods, add deltas into it

998: END Update_Delta_Srp_Periods;
999:
1000: --| -----------------------------------------------------------------------+
1001: --| Procedure : Update_Pmt_Delta_Srp_Periods
1002: --| Desc : Procedure to update row in cn_srp_periods, add deltas into it
1003: --| Note : no longer used - obsolete
1004: --| -----------------------------------------------------------------------+
1005:
1006: PROCEDURE Update_Pmt_Delta_Srp_Periods

Line 1022: END CN_SRP_PERIODS_PVT ;

1018: BEGIN
1019: null;
1020: END Update_Pmt_Delta_Srp_Periods ;
1021:
1022: END CN_SRP_PERIODS_PVT ;