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.PN_VAR_RENT_PKG dependencies on PN_VAR_PERIODS

Line 6: -- DESCRIPTION : Create recORds IN the PN_VAR_PERIODS table based on

2: /* $Header: PNVRFUNB.pls 120.44.12010000.2 2008/09/04 12:27:24 mumohan ship $ */
3:
4: /*===========================================================================+
5: -- NAME : INSERT_PERIODS_ROW
6: -- DESCRIPTION : Create recORds IN the PN_VAR_PERIODS table based on
7: -- VALUES IN PN_VAR_RENT_DATES
8: -- PURPOSE :
9: -- INVOKED FROM :
10: -- ARGUMENTS : IN:

Line 23: -- NOTES : Create recORds IN the PN_VAR_PERIODS table bASed on VALUES IN

19: -- X_ROWID
20: -- X_PERIOD_ID
21: -- X_PERIOD_NUM
22: -- REFERENCE : PN_COMMON.debug()
23: -- NOTES : Create recORds IN the PN_VAR_PERIODS table bASed on VALUES IN
24: -- PN_VAR_RENT_DATESurrently beINg used IN view
25: -- "PN_PAYMENT_SCHEDULES_V"
26: -- HISTORY :
27: --

Line 30: -- 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_PERIODS with _ALL table.

26: -- HISTORY :
27: --
28: -- 31-AUG-01 DThota o Created
29: -- 20-JUN-02 DThota o Added ORg_id FOR multi-ORg changes
30: -- 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_PERIODS with _ALL table.
31: -- 26-OCT-05 piagrawa o Bug#4702709 - Passed org id in insert row handler.
32: -- 01-DEC-05 pikhar o Fetched org_id using cursor
33: +===========================================================================*/
34: PROCEDURE INSERT_PERIODS_ROW

Line 69: FROM PN_VAR_PERIODS_ALL

65: ) IS
66:
67: CURSOR C IS
68: SELECT ROWID
69: FROM PN_VAR_PERIODS_ALL
70: WHERE PERIOD_ID = X_PERIOD_ID;
71:
72: CURSOR org_id_cur IS
73: SELECT org_id

Line 88: FROM PN_VAR_PERIODS_ALL pnp

84: -- We need to generate the period number
85: -------------------------------------------------------
86: SELECT NVL(MAX(pnp.PERIOD_NUM),0)
87: INTO X_PERIOD_NUM
88: FROM PN_VAR_PERIODS_ALL pnp
89: WHERE pnp.VAR_RENT_ID = X_VAR_RENT_ID;
90:
91: X_PERIOD_NUM := X_PERIOD_NUM + 1;
92:

Line 97: SELECT pn_var_periods_s.nextval

93: -------------------------------------------------------
94: -- SELECT the nextval FOR period id
95: -------------------------------------------------------
96: IF ( X_PERIOD_ID IS NULL) THEN
97: SELECT pn_var_periods_s.nextval
98: INTO X_PERIOD_ID
99: FROM dual;
100: END IF;
101:

Line 110: INSERT INTO PN_VAR_PERIODS_ALL

106: ELSE
107: l_org_id := X_ORG_ID;
108: END IF;
109:
110: INSERT INTO PN_VAR_PERIODS_ALL
111: (
112: PERIOD_ID,
113: PERIOD_NUM,
114: LAST_UPDATE_DATE,

Line 192: | DELETE recORds FROM the PN_VAR_PERIODS

Line 205: | NOTES : DELETE recORds FROM the PN_VAR_PERIODS table

201: | OUT:
202: |
203: | RETURNS : None
204: |
205: | NOTES : DELETE recORds FROM the PN_VAR_PERIODS table
206: |
207: | MODIFICATION HISTORY
208: |
209: | 03-SEP-2001 Daniel Thota o Created

Line 211: | 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_PERIODS with _ALL table.

207: | MODIFICATION HISTORY
208: |
209: | 03-SEP-2001 Daniel Thota o Created
210: | 27-DEC-2001 Daniel Thota o INcluded parameter x_term_date
211: | 14-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_PERIODS with _ALL table.
212: +===========================================================================*/
213: PROCEDURE DELETE_PERIODS_ROW (
214: X_VAR_RENT_ID IN NUMBER,
215: X_TERM_DATE IN DATE

Line 226: DELETE FROM PN_VAR_PERIODS_ALL

222: pnp_debug_pkg.debug ('PN_VAR_RENT_PKG.DELETE_PERIODS_ROW (+)');
223:
224: l_date := NVL(x_term_date,(TO_DATE('01/01/1776','mm/dd/yyyy')));
225:
226: DELETE FROM PN_VAR_PERIODS_ALL
227: WHERE VAR_RENT_ID = X_VAR_RENT_ID
228: AND START_DATE > l_date
229: AND END_DATE > l_date;
230:

Line 601: -- values in pn_var_periods

597:
598: /*============================================================================+
599: -- NAME : INSERT_GRP_DATE_ROW
600: -- DESCRIPTION : create records in the pn_var_grp_dates table based on
601: -- values in pn_var_periods
602: -- PURPOSE :
603: -- INVOKED FROM :
604: -- ARGUMENTS : IN:
605: -- X_ROWID

Line 627: -- in pn_var_periods

623: -- X_GRP_DATE_ID
624: --
625: -- REFERENCE : PN_COMMON.debug()
626: -- NOTES : create records in the pn_var_grp_dates table based on values
627: -- in pn_var_periods
628: -- HISTORY :
629: --
630: -- 31-AUG-01 DThota o Created
631: -- 01-NOV-01 DThota o Added columns PRORATION_FACTOR,ACTUAL_EXP_CODE,

Line 816: -- DESCRIPTION : create variable rent periods record in PN_VAR_PERIODS and

812: END DELETE_GRP_DATE_ROW;
813:
814: /*=============================================================================+
815: -- NAME : CREATE_VAR_RENT_PERIODS
816: -- DESCRIPTION : create variable rent periods record in PN_VAR_PERIODS and
817: -- corresponding group date/invoice date records in the
818: -- PN_VAR_GRP_DATES table for a variable rent record.
819: --
820: -- PURPOSE :

Line 830: -- NOTES : create variable rent periods record in pn_var_periods and

826: -- p_term_date
827: -- OUT:
828: --
829: -- REFERENCE : PN_COMMON.debug()
830: -- NOTES : create variable rent periods record in pn_var_periods and
831: -- corresponding group date/invoice date records in the
832: -- pn_var_grp_dates table for a variable rent record.
833: -- calls insert_periods_row and insert_grp_date_row procedures
834: -- HISTORY :

Line 858: -- 28-nov-05 pikhar o Replaced pn_var_periods with _ALL table

854: -- AND remove the period_year condition.
855: -- 14-JUL-05 hareesha o Bug 4284035 - Replaced pn_var_grp_dates with _ALL tbl
856: -- 26-OCT-05 piagrawa o Bug#4702709 - Passed org id in insert row handler.
857: -- pass org id to PN_VAR_RENT_PKG.INSERT_GRP_DATE_ROW
858: -- 28-nov-05 pikhar o Replaced pn_var_periods with _ALL table
859: -- 11-JAN-07 Pseeram o Added the call to cretae_report_dates
860: -- 21-MAR-07 Lbala o Bug # 5937888 - added code to change reptg_due_date
861: +=============================================================================*/
862: PROCEDURE CREATE_VAR_RENT_PERIODS(p_var_rent_id in NUMBER,

Line 1016: FROM pn_var_periods

1012: ORDER BY start_date;
1013:
1014: CURSOR period_dates IS
1015: SELECT period_id,start_date, end_date, proration_factor
1016: FROM pn_var_periods
1017: WHERE var_rent_id = p_var_rent_id
1018: AND start_date <= p_vr_term_date
1019: AND end_date >= p_vr_comm_date
1020: ORDER BY start_date;

Line 1032: FROM pn_var_periods_all

1028:
1029: /* Fetches the period id which starts on a specified date for a VR agreement */
1030: CURSOR period_cur (p_start_date DATE) IS
1031: SELECT period_id, end_date, status
1032: FROM pn_var_periods_all
1033: WHERE var_rent_id = p_var_rent_id
1034: AND start_date = p_start_date;
1035:
1036: /* Fetches the group id in which a specified invoice date lies for a VR agreement */

Line 1050: l_status pn_var_periods_all.status%TYPE;

1046: l_group_exists VARCHAR2(1) := 'N';
1047: l_invoice_exists VARCHAR2(1) := 'N';
1048: l_period_id NUMBER := NULL;
1049: l_end_date DATE := NULL;
1050: l_status pn_var_periods_all.status%TYPE;
1051: l_grp_date_id NUMBER;
1052: l_grp_end_date DATE := NULL;
1053:
1054:

Line 1218: --call to insert into PN_VAR_PERIODS;

1214: pnp_debug_pkg.debug('');
1215: END LOOP;
1216:
1217: IF l_period_exists = 'N' THEN /* Insert the period if it does not exist */
1218: --call to insert into PN_VAR_PERIODS;
1219:
1220: pnp_debug_pkg.debug(' period exists ...N');
1221: PN_VAR_RENT_PKG.INSERT_PERIODS_ROW
1222: (X_ROWID => l_rowId,

Line 1273: UPDATE pn_var_periods_all

1269: /* Make the period as active if it is inactive */
1270: IF l_status = pn_var_rent_pkg.status THEN
1271:
1272: pnp_debug_pkg.debug('period is inactive ..'||l_period_id);
1273: UPDATE pn_var_periods_all
1274: SET status = NULL
1275: WHERE period_id = l_period_id;
1276:
1277: END IF;

Line 1282: UPDATE pn_var_periods_all

1278:
1279: ELSIF p_per_end_date > l_end_date THEN /* period is partial */
1280:
1281: /* Updte the end date and partial flag for the period */
1282: UPDATE pn_var_periods_all
1283: SET end_date = p_per_end_date,
1284: partial_period = l_partial_period,
1285: status = NULL
1286: WHERE period_id = l_period_id;

Line 2082: -- DESCRIPTION : Create variable rent periods recORd IN PN_VAR_PERIODS AND

2078: END CREATE_VAR_RENT_PERIODS;
2079:
2080: /*=============================================================================+
2081: -- NAME : CREATE_VAR_RENT_PERIODS_NOCAL
2082: -- DESCRIPTION : Create variable rent periods recORd IN PN_VAR_PERIODS AND
2083: -- corresponding group date/invoice date records in the
2084: -- PN_VAR_GRP_DATES table for a variable rent recORd when a
2085: -- GL calendar is not specified in the VR agreement.
2086: -- PURPOSE :

Line 2096: -- NOTES : Create variable rent periods recORd IN PN_VAR_PERIODS AND

2092: -- p_term_date
2093: -- OUT:
2094: --
2095: -- REFERENCE : PN_COMMON.debug()
2096: -- NOTES : Create variable rent periods recORd IN PN_VAR_PERIODS AND
2097: -- corresponding group date/invoice date records in the
2098: -- PN_VAR_GRP_DATES table for a variable rent record for
2099: -- yearly VR periods depending on the year start date.
2100: -- Calls INSERT_PERIODS_ROW AND INSERT_GRP_DATE_ROW procedures

Line 2188: l_status pn_var_periods_all.status%TYPE;

2184:
2185: l_org_id NUMBER;
2186: l_add_flag VARCHAR2(1) := 'N';
2187: l_end_date DATE := NULL;
2188: l_status pn_var_periods_all.status%TYPE;
2189: l_dummy VARCHAR2(1) := NULL;
2190: l_group_exists VARCHAR2(1) := 'N';
2191: l_grp_date_id NUMBER;
2192: l_grp_end_date DATE := NULL;

Line 2209: FROM pn_var_periods_all

2205:
2206: /* Fetches the period id which starts on a specified date for a VR agreement */
2207: CURSOR period_cur (p_start_date DATE) IS
2208: SELECT period_id, end_date, status
2209: FROM pn_var_periods_all
2210: WHERE var_rent_id = p_var_rent_id
2211: AND start_date = p_start_date;
2212:
2213: BEGIN

Line 2365: UPDATE pn_var_periods_all

2361: IF l_status = pn_var_rent_pkg.status THEN
2362:
2363: pnp_debug_pkg.debug('period is inactive ..'||l_periodId);
2364:
2365: UPDATE pn_var_periods_all
2366: SET status = NULL
2367: WHERE period_id = l_periodId;
2368:
2369: END IF;

Line 2385: UPDATE pn_var_periods_all

2381: l_partial_period := 'Y';
2382: END IF;
2383:
2384: /* Updte the end date and partial flag for the period */
2385: UPDATE pn_var_periods_all
2386: SET
2387: end_date = vr_periods(i).l_period_end_date,
2388: partial_period = l_partial_period,
2389: status = NULL

Line 2638: | Delete variable rent periods record in PN_VAR_PERIODS, corresponding

Line 2655: | NOTES : Delete variable rent periods record in PN_VAR_PERIODS,

Line 2808: | Update variable rent periods record in PN_VAR_PERIODS, corresponding

Line 2824: | NOTES : Update variable rent periods record in PN_VAR_PERIODS, corresponding

2820: | OUT:
2821: |
2822: | RETURNS : None
2823: |
2824: | NOTES : Update variable rent periods record in PN_VAR_PERIODS, corresponding
2825: | group date/invoice date records in the PN_VAR_GRP_DATES table
2826: | for a variable rent record contraction
2827: |
2828: | MODIFICATION HISTORY

Line 2831: | 04-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_PERIODS with _ALL table

2827: |
2828: | MODIFICATION HISTORY
2829: |
2830: | 29-DEC-01 Daniel o Created
2831: | 04-JUL-05 hareesha o Bug 4284035 - Replaced PN_VAR_PERIODS with _ALL table
2832: +=============================================================================*/
2833: PROCEDURE UPDATE_VAR_RENT_PERIODS(p_var_rent_id IN NUMBER,
2834: p_term_date IN DATE ) IS
2835:

Line 2870: FROM PN_VAR_PERIODS_ALL per

2866: -- updated with the new proration factor after lease contraction
2867:
2868: SELECT per.start_date
2869: INTO p_per_start_date
2870: FROM PN_VAR_PERIODS_ALL per
2871: WHERE per.var_rent_id = p_var_rent_id
2872: AND per.start_date <= p_term_date
2873: AND per.end_date >= p_term_date
2874: AND rownum < 2;

Line 2900: --call to update PN_VAR_PERIODS;

2896: IF p_proration_factor > 1 THEN
2897: p_proration_factor := 1;
2898: END IF;
2899:
2900: --call to update PN_VAR_PERIODS;
2901: UPDATE pn_var_periods_all
2902: SET end_date = p_term_date,
2903: proration_factor = p_proration_factor,
2904: last_update_date = p_last_update_date,

Line 2901: UPDATE pn_var_periods_all

2897: p_proration_factor := 1;
2898: END IF;
2899:
2900: --call to update PN_VAR_PERIODS;
2901: UPDATE pn_var_periods_all
2902: SET end_date = p_term_date,
2903: proration_factor = p_proration_factor,
2904: last_update_date = p_last_update_date,
2905: last_updated_by = p_last_updated_by,

Line 2988: FROM PN_VAR_PERIODS_ALL

2984: CURSOR c1 IS
2985: SELECT constraint_id
2986: FROM PN_VAR_CONSTRAINTS_ALL
2987: WHERE period_id IN (SELECT period_id
2988: FROM PN_VAR_PERIODS_ALL
2989: WHERE var_rent_id = p_var_rent_id
2990: AND start_date > l_date
2991: AND end_date > l_date);
2992:

Line 3060: FROM pn_var_periods_ALL

3056: CURSOR c1 is
3057: SELECT line_item_id
3058: FROM pn_var_lines
3059: WHERE period_id IN (SELECT period_id
3060: FROM pn_var_periods_ALL
3061: WHERE var_rent_id = p_var_rent_id
3062: AND start_date > l_date
3063: AND end_date > l_date);
3064:

Line 3380: FROM pn_var_periods_ALL periods

3376: SELECT 1
3377: INTO l_period_exists
3378: FROM dual
3379: WHERE EXISTS ( SELECT periods.period_id
3380: FROM pn_var_periods_ALL periods
3381: WHERE periods.var_rent_id = p_var_rent_id);
3382:
3383: RETURN l_period_exists;
3384:

Line 3536: FROM pn_var_periods_ALL

3532: CURSOR c1 is
3533: SELECT line_item_id
3534: FROM pn_var_lines
3535: WHERE period_id IN (SELECT period_id
3536: FROM pn_var_periods_ALL
3537: WHERE var_rent_id = p_var_rent_id
3538: AND start_date > l_date
3539: AND end_date > l_date);
3540:

Line 3743: FROM pn_var_periods_ALL

3739: FROM dual
3740: WHERE EXISTS ( SELECT line_item_id
3741: FROM pn_var_lines_ALL
3742: WHERE period_id IN (SELECT period_id
3743: FROM pn_var_periods_ALL
3744: WHERE var_rent_id = p_var_rent_id
3745: AND period_id = NVL(p_period_id,period_id)
3746: AND start_date > l_date
3747: AND end_date > l_date)

Line 3923: FROM pn_var_periods_ALL

3919: CURSOR c_bkptshd_var_rent ( p_id IN NUMBER ) IS
3920: SELECT bkpt_header_id, line_item_id
3921: FROM pn_var_bkpts_head_ALL
3922: WHERE period_id IN ( SELECT period_id
3923: FROM pn_var_periods_ALL
3924: WHERE var_rent_id = p_id );
3925:
3926: l_volhist_bkpts_exist NUMBER := NULL;
3927:

Line 4017: FROM PN_VAR_PERIODS_ALL

4013: FROM dual
4014: WHERE EXISTS ( SELECT constraint_id
4015: FROM pn_var_constraints_ALL
4016: WHERE period_id IN (SELECT period_id
4017: FROM PN_VAR_PERIODS_ALL
4018: WHERE var_rent_id = p_var_rent_id
4019: AND start_date > l_date
4020: AND end_date > l_date)
4021: );

Line 4427: pn_var_periods_ALL per,

4423: SELECT SUM(item.ACTUAL_AMOUNT)
4424: INTO l_base_rent
4425: FROM pn_payment_items_ALL item,
4426: pn_payment_terms_ALL term,
4427: pn_var_periods_ALL per,
4428: pn_var_rents_ALL var,
4429: pn_payment_schedules_all sched
4430: WHERE item.PAYMENT_TERM_ID = term.PAYMENT_TERM_ID
4431: AND sched.PAYMENT_SCHEDULE_ID = item.PAYMENT_SCHEDULE_ID

Line 4451: pn_var_periods_ALL per,

4447: SELECT SUM(item.ACTUAL_AMOUNT)
4448: INTO l_base_rent
4449: FROM pn_payment_items_ALL item,
4450: pn_payment_terms_ALL term,
4451: pn_var_periods_ALL per,
4452: pn_var_rents_ALL var,
4453: pn_payment_schedules_all sched
4454: WHERE item.PAYMENT_TERM_ID = term.PAYMENT_TERM_ID
4455: AND sched.PAYMENT_SCHEDULE_ID = item.PAYMENT_SCHEDULE_ID

Line 4606: FROM pn_var_periods_ALL p1, pn_var_periods_ALL p2,

4602: g1.proration_factor first_group_gl_days,
4603: (g2.grp_end_date-g2.grp_start_date)+1 last_group_pro_days,
4604: g2.proration_factor last_group_gl_days
4605: INTO l_proration_factor
4606: FROM pn_var_periods_ALL p1, pn_var_periods_ALL p2,
4607: pn_var_grp_dates_ALL g1, pn_var_grp_dates_ALL g2
4608: WHERE p1.var_rent_id = p2.var_rent_id
4609: AND p1.var_rent_id = p_var_rent_id
4610: AND g1.var_rent_id = g2.var_rent_id

Line 4613: FROM pn_var_periods_ALL

4609: AND p1.var_rent_id = p_var_rent_id
4610: AND g1.var_rent_id = g2.var_rent_id
4611: AND g1.var_rent_id = p_var_rent_id
4612: AND p1.period_id = (SELECT min(period_id)
4613: FROM pn_var_periods_ALL
4614: WHERE var_rent_id = p_var_rent_id)
4615: AND p2.period_id = (SELECT max(period_id)
4616: FROM pn_var_periods_ALL
4617: WHERE var_rent_id = p_var_rent_id)

Line 4616: FROM pn_var_periods_ALL

4612: AND p1.period_id = (SELECT min(period_id)
4613: FROM pn_var_periods_ALL
4614: WHERE var_rent_id = p_var_rent_id)
4615: AND p2.period_id = (SELECT max(period_id)
4616: FROM pn_var_periods_ALL
4617: WHERE var_rent_id = p_var_rent_id)
4618: AND g1.grp_date_id = (SELECT min(grp_date_id)
4619: FROM pn_var_grp_dates
4620: WHERE var_rent_id = p_var_rent_id)

Line 4848: pn_var_periods_all b,

4844: INTO l_exported
4845: FROM dual
4846: WHERE EXISTS ( SELECT grp_date_id
4847: FROM pn_var_grp_dates_all a,
4848: pn_var_periods_all b,
4849: pn_var_lines_all c
4850: WHERE a.period_id = b.period_id
4851: AND b.period_id = c.period_id
4852: AND c.line_default_id = p_id

Line 5167: FROM pn_var_periods_all pd, pn_var_lines_all ln, pn_var_bkpts_head_all bp

5163: PROCEDURE delete_inv_summ (p_var_rent_id IN NUMBER) IS
5164:
5165: CURSOR C_EXIST IS
5166: SELECT bp.bkpt_header_id
5167: FROM pn_var_periods_all pd, pn_var_lines_all ln, pn_var_bkpts_head_all bp
5168: WHERE pd.var_rent_id = p_var_rent_id
5169: AND pd.period_id = ln.period_id
5170: AND ln.lINe_item_id = bp.lINe_item_id
5171: AND bp.break_type = 'NATURAL';

Line 5429: FROM pn_var_periods_all

5425: CURSOR periods_vr_c(p_vr_id IN NUMBER) IS
5426: SELECT period_id
5427: ,start_date
5428: ,end_date
5429: FROM pn_var_periods_all
5430: WHERE var_rent_id = p_vr_id
5431: ORDER BY start_date;
5432:
5433: CURSOR periods_c(p_prd_id IN NUMBER) IS

Line 5437: FROM pn_var_periods_all

5433: CURSOR periods_c(p_prd_id IN NUMBER) IS
5434: SELECT period_id
5435: ,start_date
5436: ,end_date
5437: FROM pn_var_periods_all
5438: WHERE period_id = p_prd_id;
5439:
5440: CURSOR line_items_c(p_prd_id IN NUMBER) IS
5441: SELECT line_item_id

Line 5828: FROM pn_var_periods_all

5824: l_line_t(1) := p_line_item_id;
5825:
5826: SELECT start_date , end_date
5827: INTO l_period_st_dt, l_period_end_dt
5828: FROM pn_var_periods_all
5829: WHERE period_id =(SELECT period_id
5830: FROM pn_var_lines_all
5831: WHERE line_item_id = p_line_item_id);
5832: END IF;

Line 6103: FROM pn_var_periods_all

6099: CURSOR periods_vr_c(p_vr_id IN NUMBER) IS
6100: SELECT period_id
6101: ,start_date
6102: ,end_date
6103: FROM pn_var_periods_all
6104: WHERE var_rent_id = p_vr_id
6105: ORDER BY start_date;
6106:
6107: CURSOR periods_c(p_prd_id IN NUMBER) IS

Line 6111: FROM pn_var_periods_all

6107: CURSOR periods_c(p_prd_id IN NUMBER) IS
6108: SELECT period_id
6109: ,start_date
6110: ,end_date
6111: FROM pn_var_periods_all
6112: WHERE period_id = p_prd_id;
6113:
6114: CURSOR constr_c(p_period_id IN NUMBER) IS
6115: SELECT constraint_id

Line 6308: FROM pn_var_periods_all

6304: IS
6305:
6306: Cursor var_periods_cur IS
6307: SELECT *
6308: FROM pn_var_periods_all
6309: WHERE var_rent_id = p_var_rent_id
6310: -- AND (start_date = NVL(p_start_date,start_date) OR
6311: -- end_date = NVL(p_end_date ,end_date) )
6312: Order by start_date ;

Line 6410: UPDATE pn_var_periods_all

6406: pnp_debug_pkg.debug(' v_new_periods_tbl(i).end_date:'||v_new_periods_tbl(i).end_date);
6407: pnp_debug_pkg.debug(' l_partial_period:'||l_partial_period);*/
6408: --Srini End 30-JUL-2004
6409:
6410: UPDATE pn_var_periods_all
6411: SET end_date = v_new_periods_tbl(i).end_date,
6412: partial_period = l_partial_period, --Srini
6413: last_update_date = sysdate,
6414: last_updated_by = FND_GLOBAL.USER_ID,

Line 6555: FROM pn_var_periods_all

6551:
6552: CURSOR periods_cur
6553: IS
6554: SELECT start_date, end_date ,proration_factor
6555: FROM pn_var_periods_all
6556: WHERE period_id = p_period_id;
6557:
6558: CURSOR pn_var_grp_dates_cur
6559: IS

Line 7068: pn_var_periods_all b

7064: IS
7065: CURSOR var_rent_cur IS
7066: SELECT a.proration_rule
7067: FROM pn_var_rents_all a,
7068: pn_var_periods_all b
7069: WHERE a.var_rent_id = NVL(p_var_rent_id,a.var_rent_id)
7070: AND a.var_rent_id = b.var_rent_id
7071: AND b.period_id = NVL(p_period_id,b.period_id);
7072:

Line 7092: pn_var_periods_all b

7088: BEGIN
7089: SELECT proration_rule
7090: INTO l_proration_rule
7091: FROM pn_var_rents_all a,
7092: pn_var_periods_all b
7093: WHERE a.var_rent_id = b.var_rent_id
7094: AND b.period_id = NVL(p_period_id,b.period_id);
7095: EXCEPTION
7096: WHEN OTHERS THEN

Line 7723: FROM pn_var_periods_all

7719: FUNCTION is_partial_period (p_period_id IN NUMBER)
7720: RETURN VARCHAR2 IS
7721: CURSOR is_partial_period_cur IS
7722: SELECT partial_period
7723: FROM pn_var_periods_all
7724: WHERE period_id = p_period_id;
7725: l_return VARCHAR2(1) := 'X';
7726: BEGIN
7727: OPEN is_partial_period_cur;

Line 7862: FROM pn_var_periods_all

7858: /* Delete the data from main tables */
7859: DELETE pn_var_vol_hist_all
7860: WHERE start_date > p_new_termn_date
7861: AND period_id IN (SELECT period_id
7862: FROM pn_var_periods_all
7863: WHERE var_rent_id = p_var_rent_id);
7864:
7865: DELETE pn_var_bkpts_det_all
7866: WHERE bkpt_start_date > p_new_termn_date

Line 7876: FROM pn_var_periods_all

7872:
7873: DELETE pn_var_deductions_all
7874: WHERE start_date > p_new_termn_date
7875: AND period_id IN (SELECT period_id
7876: FROM pn_var_periods_all
7877: WHERE var_rent_id = p_var_rent_id);
7878:
7879: DELETE pn_var_rent_summ_all
7880: WHERE grp_date_id IN (SELECT grp_date_id

Line 7888: FROM pn_var_periods_all

7884:
7885: DELETE pn_var_constraints_all
7886: WHERE constr_start_date > p_new_termn_date
7887: AND period_id IN (SELECT period_id
7888: FROM pn_var_periods_all
7889: WHERE var_rent_id = p_var_rent_id);
7890:
7891:
7892: DELETE pn_var_lines_all

Line 7894: FROM pn_var_periods_all

7890:
7891:
7892: DELETE pn_var_lines_all
7893: WHERE period_id IN (SELECT period_id
7894: FROM pn_var_periods_all
7895: WHERE var_rent_id = p_var_rent_id
7896: AND start_date > p_new_termn_date);
7897:
7898: /* Delete data from defaults table */

Line 7949: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp

7945:
7946: /* This cursor fetches the distinct invoices for periods starting after new termination date */
7947: CURSOR invoice_date_cur IS
7948: SELECT DISTINCT pvi.invoice_date, pvi.period_id
7949: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp
7950: WHERE pvp.period_id = pvi.period_id
7951: AND pvp.start_date > p_new_termn_date
7952: AND pvp.var_rent_id = p_var_rent_id;
7953:

Line 8000: FROM pn_var_periods_all

7996:
7997: /* This cursor fetches periods starting after new termination date in descending order of start date */
7998: CURSOR period_cur IS
7999: SELECT period_id
8000: FROM pn_var_periods_all
8001: WHERE var_rent_id = p_var_rent_id
8002: AND start_date > p_new_termn_date
8003: ORDER BY start_date DESC;
8004:

Line 8014: FROM pn_var_periods_all per

8010:
8011: /* This cursor fetches periods staring after new termination date for which there exists invoices */
8012: CURSOR period_inv_cur IS
8013: SELECT period_id
8014: FROM pn_var_periods_all per
8015: WHERE per.var_rent_id = p_var_rent_id
8016: AND per.start_date > p_new_termn_date
8017: AND EXISTS (SELECT var_rent_inv_id
8018: FROM pn_var_rent_inv_all

Line 8084: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp

8080: /* Delete draft terms for invoices for all periods which lie after the new termination date */
8081: DELETE pn_payment_terms_all
8082: WHERE status = 'DRAFT'
8083: AND var_rent_inv_id IN (SELECT var_rent_inv_id
8084: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp
8085: WHERE pvi.period_id = pvp.period_id
8086: AND pvp.var_rent_id = p_var_rent_id
8087: AND pvp.start_date > p_new_termn_date);
8088:

Line 8092: FROM pn_payment_terms_all ppt, pn_var_rent_inv_all pvi, pn_var_periods_all pvp

8088:
8089: /* Delete invoices for which there are no terms */
8090: DELETE pn_var_rent_inv_all
8091: WHERE var_rent_inv_id NOT IN (SELECT ppt.var_rent_inv_id
8092: FROM pn_payment_terms_all ppt, pn_var_rent_inv_all pvi, pn_var_periods_all pvp
8093: WHERE ppt.var_rent_inv_id = pvi.var_rent_inv_id
8094: AND pvi.period_id = pvp.period_id
8095: AND pvp.var_rent_id = p_var_rent_id
8096: AND pvp.start_date > p_new_termn_date)

Line 8099: FROM pn_var_periods_all

8095: AND pvp.var_rent_id = p_var_rent_id
8096: AND pvp.start_date > p_new_termn_date)
8097: AND var_rent_id = p_var_rent_id
8098: AND period_id IN (SELECT period_id
8099: FROM pn_var_periods_all
8100: WHERE var_rent_id = p_var_rent_id
8101: AND start_date > p_new_termn_date);
8102:
8103: pnp_debug_pkg.debug ('Loop through the period which start after new termination date');

Line 8127: DELETE pn_var_periods_all

8123: /* Delete group dates and periods for which there exists no invoice */
8124: DELETE pn_var_grp_dates_all
8125: WHERE period_id = period_rec.period_id;
8126:
8127: DELETE pn_var_periods_all
8128: WHERE period_id = period_rec.period_id;
8129: END IF;
8130:
8131: END LOOP;

Line 8243: UPDATE pn_var_periods_all

8239: END LOOP;
8240:
8241: /* OPEN ISSUE */
8242: /* Update the periods after new termination date - set status = 'Reversed' */
8243: UPDATE pn_var_periods_all
8244: SET status = pn_var_rent_pkg.status
8245: WHERE var_rent_id = p_var_rent_id
8246: AND start_date > p_new_termn_date;
8247:

Line 8497: UPDATE pn_var_periods_all

8493: SET end_date = l_inv_end_dt
8494: WHERE var_rent_id = p_var_rent_id
8495: AND p_new_termn_date BETWEEN start_date AND end_date;
8496:
8497: UPDATE pn_var_periods_all
8498: SET end_date = p_new_termn_date,
8499: Partial_period = 'Y'
8500: WHERE var_rent_id = p_var_rent_id
8501: AND period_id = p_period_id;

Line 8627: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp

8623: /* Delete draft terms for invoices for this period */
8624: DELETE pn_payment_terms_all
8625: WHERE status = 'DRAFT'
8626: AND var_rent_inv_id IN (SELECT var_rent_inv_id
8627: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp
8628: WHERE pvi.period_id = p_period_id );
8629:
8630: /* Delete invoices for which there are no terms */
8631: DELETE pn_var_rent_inv_all

Line 8759: FROM pn_var_periods_all

8755:
8756: /* Delete the data from main tables */
8757: DELETE pn_var_vol_hist_all
8758: WHERE period_id IN (SELECT period_id
8759: FROM pn_var_periods_all
8760: WHERE var_rent_id = p_var_rent_id);
8761:
8762: DELETE pn_var_bkpts_det_all
8763: WHERE var_rent_id = p_var_rent_id;

Line 8770: FROM pn_var_periods_all

8766: WHERE var_rent_id = p_var_rent_id;
8767:
8768: DELETE pn_var_deductions_all
8769: WHERE period_id IN (SELECT period_id
8770: FROM pn_var_periods_all
8771: WHERE var_rent_id = p_var_rent_id);
8772:
8773: DELETE pn_var_rent_summ_all
8774: WHERE grp_date_id IN (SELECT grp_date_id

Line 8780: FROM pn_var_periods_all

8776: WHERE var_rent_id = p_var_rent_id);
8777:
8778: DELETE pn_var_constraints_all
8779: WHERE period_id IN (SELECT period_id
8780: FROM pn_var_periods_all
8781: WHERE var_rent_id = p_var_rent_id);
8782:
8783: DELETE pn_var_lines_all
8784: WHERE period_id IN (SELECT period_id

Line 8785: FROM pn_var_periods_all

8781: WHERE var_rent_id = p_var_rent_id);
8782:
8783: DELETE pn_var_lines_all
8784: WHERE period_id IN (SELECT period_id
8785: FROM pn_var_periods_all
8786: WHERE var_rent_id = p_var_rent_id);
8787:
8788: /* Delete data from defaults table */
8789:

Line 8813: DELETE pn_var_periods_all

8809:
8810: DELETE pn_var_grp_dates_all
8811: WHERE var_rent_id = p_var_rent_id;
8812:
8813: DELETE pn_var_periods_all
8814: WHERE var_rent_id = p_var_rent_id;
8815:
8816: DELETE pn_var_rents_all
8817: WHERE var_rent_id = p_var_rent_id;

Line 8867: FROM pn_var_periods_all

8863:
8864: /* Fetch the period in which the new termination date lies */
8865: CURSOR period_id_cur(l_var_rent_id NUMBER) IS
8866: SELECT period_id, end_date
8867: FROM pn_var_periods_all
8868: WHERE var_rent_id = l_var_rent_id
8869: AND p_new_termn_date BETWEEN start_date AND end_date;
8870:
8871: /* Fetch the agreements which start after the new termination date */

Line 8885: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp,

8881: SELECT 'Y' approve_term_exits
8882: FROM pn_payment_terms_all
8883: WHERE status = 'APPROVED'
8884: AND var_rent_inv_id IN (SELECT var_rent_inv_id
8885: FROM pn_var_rent_inv_all pvi, pn_var_periods_all pvp,
8886: pn_var_rents_all pvr
8887: WHERE pvi.period_id = pvp.period_id
8888: AND pvr.var_rent_id = pvp.var_rent_id
8889: AND pvr.var_rent_id = p_var_rent_id);

Line 9108: FROM pn_var_periods_all

9104: FROM DUAL
9105: WHERE EXISTS (SELECT constraint_id
9106: FROM pn_var_constraints_all
9107: WHERE period_id IN (SELECT PERIOD_ID
9108: FROM pn_var_periods_all
9109: WHERE var_rent_id = p_var_rent_id)
9110: AND constr_default_id IS NOT NULL);
9111:
9112: /* Get the details of breakpoint header default */

Line 9194: FROM pn_var_periods_all

9190: pnp_debug_pkg.debug ('calling procedure pn_var_defaults_pkg.create_default_constraints ..');
9191: FOR rec IN constr_data_exists LOOP
9192: DELETE FROM pn_var_constraints_all
9193: WHERE period_id IN (SELECT period_id
9194: FROM pn_var_periods_all
9195: WHERE var_rent_id = p_var_rent_id);
9196:
9197: pn_var_defaults_pkg.create_default_constraints (x_var_rent_id => p_var_rent_id);
9198: END LOOP;

Line 9222: FROM pn_var_periods_all

9218:
9219: /* This cursor fetches period, which start after the last period */
9220: CURSOR period_cur IS
9221: SELECT period_id, start_date, end_date
9222: FROM pn_var_periods_all
9223: WHERE var_rent_id = p_var_rent_id
9224: AND start_date > ( SELECT end_date
9225: FROM pn_var_periods_all
9226: WHERE period_id = p_period_id);

Line 9225: FROM pn_var_periods_all

9221: SELECT period_id, start_date, end_date
9222: FROM pn_var_periods_all
9223: WHERE var_rent_id = p_var_rent_id
9224: AND start_date > ( SELECT end_date
9225: FROM pn_var_periods_all
9226: WHERE period_id = p_period_id);
9227:
9228: /* This cursor fetches information abouth the period which was the last one
9229: before contraction */

Line 9232: FROM pn_var_periods_all

9228: /* This cursor fetches information abouth the period which was the last one
9229: before contraction */
9230: CURSOR last_period_cur IS
9231: SELECT end_date
9232: FROM pn_var_periods_all
9233: WHERE period_id = p_period_id;
9234:
9235: /* This cursor fetches the line record data for last period before expansion */
9236: CURSOR line_cur IS

Line 9603: FROM pn_var_periods_all

9599: ,p_period_id IN NUMBER)
9600: IS
9601: CURSOR period_cur IS
9602: SELECT end_date
9603: FROM pn_var_periods_all
9604: WHERE var_rent_id = p_var_rent_id
9605: AND period_id = p_period_id;
9606:
9607: BEGIN

Line 9850: FROM pn_var_periods_all

9846:
9847: /* Fetches the period in which the old termination date falls */
9848: CURSOR last_period_cur (p_var_rent_id NUMBER) IS
9849: SELECT period_id, partial_period
9850: FROM pn_var_periods_all
9851: WHERE var_rent_id = p_var_rent_id
9852: AND p_old_termn_date BETWEEN start_date AND end_date;
9853:
9854: /* This retrieves the variable rent agreements which need to be extended */

Line 10340: FROM pn_var_periods_all per,

10336: /* Cursor used to fetch distinct periods */
10337: CURSOR periods_cur(p_var_rent_id NUMBER) IS
10338: SELECT DISTINCT per.period_id period_id,
10339: var.rent_num rent_num
10340: FROM pn_var_periods_all per,
10341: pn_var_rents_all var
10342: WHERE per.var_rent_id = p_var_rent_id
10343: AND var.var_rent_id = p_var_rent_id;
10344:

Line 10372: FROM pn_var_periods_all per

10368: CURSOR no_per_lines_exists(p_var_rent_id NUMBER,
10369: p_sales_type_code VARCHAR2,
10370: p_item_category_code VARCHAR2) IS
10371: SELECT per.period_id, per.org_id
10372: FROM pn_var_periods_all per
10373: WHERE per.period_id not in (
10374: SELECT lines.period_id
10375: FROM pn_var_lines_all lines
10376: WHERE lines.var_rent_id = p_var_rent_id

Line 10489: pn_var_periods_all per

10485: ,cons.constr_default_id
10486: ,cons.constr_start_date
10487: ,cons.constr_end_date
10488: FROM pn_var_constraints_all cons,
10489: pn_var_periods_all per
10490: WHERE per.var_rent_id = p_var_rent_id
10491: AND cons.period_id = per.period_id;
10492:
10493:

Line 11061: FROM pn_var_periods_all

11057: /* Cursor to get var periods */
11058: CURSOR periods_vr_c(p_vr_id IN NUMBER) IS
11059: SELECT start_date
11060: ,end_date
11061: FROM pn_var_periods_all
11062: WHERE var_rent_id = p_vr_id
11063: ORDER BY start_date;
11064:
11065: /* Cursor to select Breakpoints whose start date