[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_UPG_VOL_PKG
Source
1 PACKAGE BODY PN_VAR_UPG_VOL_PKG AS
2 -- $Header: PNUPGVOB.pls 120.0.12010000.1 2009/10/19 07:07:59 rrambati noship $
3
4 --------------------------------------------------------------------------------
5 --
6 -- NAME : vr_update_volume_status
7 -- DESCRIPTION : Main procedure called from Upgrade of VR agreements
8 -- PURPOSE :
9 -- INVOKED FROM :
10 -- ARGUMENTS :
11 -- REFERENCE : PN_COMMON.debug()
12 -- HISTORY :
13 --
14 -- 07-AUG-2007 pikhar o Created
15 --------------------------------------------------------------------------------
16 PROCEDURE vr_update_volume_status ( errbuf OUT NOCOPY VARCHAR2,
17 retcode OUT NOCOPY VARCHAR2,
18 p_property_code IN VARCHAR2,
19 p_property_name IN VARCHAR2,
20 p_location_code_from IN VARCHAR2,
21 p_location_code_to IN VARCHAR2,
22 p_lease_num_from IN VARCHAR2,
23 p_lease_num_to IN VARCHAR2,
24 p_vrent_num_from IN VARCHAR2,
25 p_vrent_num_to IN VARCHAR2)
26
27 IS
28
29 CURSOR csr_get_vrent_wprop IS
30 SELECT pvr.var_rent_id
31 FROM pn_leases pl,
32 pn_var_rents_all pvr,
33 pn_locations_all ploc
34 WHERE pl.lease_id = pvr.lease_id
35 AND ploc.location_id = pvr.location_id
36 AND ploc.location_id IN (SELECT location_id
37 FROM pn_locations_all
38 START WITH location_id
39 IN
40 (SELECT location_id
41 FROM pn_locations_all
42 WHERE property_id IN(SELECT property_id
43 FROM pn_properties_all
44 WHERE property_code=NVL(p_property_code,property_code)
45 AND property_name=NVL(p_property_name,property_name))
46 )
47 CONNECT BY PRIOR location_id=parent_location_id)
48 AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
49 AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
50 AND ploc.location_code >= NVL(p_location_code_from, ploc.location_code)
51 AND ploc.location_code <= NVL(p_location_code_to, ploc.location_code)
52 AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
53 AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
54 ORDER BY pl.lease_id, pvr.var_rent_id;
55
56 CURSOR csr_get_vrent_wloc IS
57 SELECT pvr.var_rent_id
58 FROM pn_leases pl,
59 pn_var_rents_all pvr,
60 pn_locations_all ploc
61 WHERE pl.lease_id = pvr.lease_id
62 AND ploc.location_id = pvr.location_id
63 AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
64 AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
65 AND ploc.location_code >= NVL(p_location_code_from, ploc.location_code)
66 AND ploc.location_code <= NVL(p_location_code_to, ploc.location_code)
67 AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
68 AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
69 ORDER BY pl.lease_id, pvr.var_rent_id;
70
71 CURSOR csr_get_vrent_woloc IS
72 SELECT pvr.var_rent_id
73 FROM pn_var_rents_all pvr,
74 pn_leases_all pl
75 WHERE pl.lease_id = pvr.lease_id
76 AND pl.lease_num >= NVL(p_lease_num_from, pl.lease_num)
77 AND pl.lease_num <= NVL(p_lease_num_to, pl.lease_num)
78 AND pvr.rent_num >= NVL(p_vrent_num_from,pvr.rent_num)
79 AND pvr.rent_num <= NVL(p_vrent_num_to,pvr.rent_num)
80 ORDER BY pl.lease_id, pvr.var_rent_id;
81
82
83 l_var_rent_id NUMBER;
84
85 BEGIN
86
87 pnp_debug_pkg.log
88 ('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
89 pnp_debug_pkg.log('+++++++++ vr_upgrade_batch_process START +++++++++++');
90 pnp_debug_pkg.log(' ');
91 pnp_debug_pkg.log('p_property_code '||p_property_code );
92 pnp_debug_pkg.log('p_property_name '||p_property_name );
93 pnp_debug_pkg.log('p_lease_num_from '||p_lease_num_from );
94 pnp_debug_pkg.log('p_lease_num_to '||p_lease_num_to );
95 pnp_debug_pkg.log('p_location_code_from'||p_location_code_from );
96 pnp_debug_pkg.log('p_location_code_to '||p_location_code_to );
97 pnp_debug_pkg.log('p_vrent_num_from '||p_vrent_num_from );
98 pnp_debug_pkg.log('p_vrent_num_to '||p_vrent_num_to );
99
100 IF p_property_code IS NOT NULL OR p_property_name IS NOT NULL THEN
101
102 FOR rec IN csr_get_vrent_wprop LOOP
103 PN_VAR_UPG_VOL_PKG.process_vr_upgrade
104 ( p_var_rent_id => rec.var_rent_id);
105 END LOOP;
106
107 ELSIF p_location_code_from IS NOT NULL or p_location_code_to IS NOT NULL THEN
108
109 FOR rec IN csr_get_vrent_wloc LOOP
110 PN_VAR_UPG_VOL_PKG.process_vr_upgrade
111 ( p_var_rent_id => rec.var_rent_id);
112 END LOOP;
113
114 ELSE
115
116 FOR rec IN csr_get_vrent_woloc LOOP
117 PN_VAR_UPG_VOL_PKG.process_vr_upgrade
118 ( p_var_rent_id => rec.var_rent_id);
119 END LOOP;
120
121 END IF;
122
123 pnp_debug_pkg.log(' ++++++++++++++ vr_upgrade_batch_process - END
124 +++++++++++++');
125
126 EXCEPTION
127
128 WHEN OTHERS THEN
129 raise_application_error ('-20001','Error ' || to_char(sqlcode)||' error
130 message '||SQLERRM);
131
132 END vr_update_volume_status;
133
134
135
136 --------------------------------------------------------------------------------
137 --
138 -- NAME : process_vr_upgrade
139 -- DESCRIPTION :
140 -- PURPOSE :
141 -- INVOKED FROM : vr_update_volumen_status
142 -- ARGUMENTS :
143 -- REFERENCE : PN_COMMON.debug()
144 -- HISTORY :
145 --
146 -- 07-AUG-2007 pikhar o Created
147 --------------------------------------------------------------------------------
148 PROCEDURE process_vr_upgrade( p_var_rent_id IN NUMBER)
149 IS
150
151 CURSOR csr_get_period IS
152 SELECT period_id
153 FROM pn_var_periods_all
154 WHERE var_rent_id = p_var_rent_id;
155
156 BEGIN
157
158 pnp_debug_pkg.log(' ++++++++++++++ process_vr_upgrade - START +++++++++++++');
159
160 FOR rec IN csr_get_period LOOP
161
162 UPDATE PN_VAR_VOL_HIST_ALL vol
163 SET vol_hist_status_code = 'APPROVED'
164 WHERE period_id = rec.period_id;
165
166 END LOOP;
167
168 pnp_debug_pkg.log(' ++++++++++++++ process_vr_upgrade - END +++++++++++++');
169
170 EXCEPTION
171
172 WHEN OTHERS THEN
173 raise_application_error ('-20001','Error ' || to_char(sqlcode)||' error
174 message '||SQLERRM);
175
176 END process_vr_upgrade;
177
178 END PN_VAR_UPG_VOL_PKG;
179