[Home] [Help]
PACKAGE BODY: APPS.PN_VAR_UPG_TRX_PKG
Source
1 PACKAGE BODY PN_VAR_UPG_TRX_PKG AS
2 -- $Header: PNUPGTRB.pls 120.0 2011/10/10 10:20:08 acprakas noship $
3
4 --------------------------------------------------------------------------------
5 --
6 -- NAME : vr_upgrade_batch_process
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 -- 06-AUG-2007 piagrawa o Created
15 --------------------------------------------------------------------------------
16 PROCEDURE vr_upgrade_batch_process ( 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_TRX_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_TRX_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_TRX_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 EXCEPTION
126
127 WHEN OTHERS THEN
128 raise_application_error ('-20001','Error ' || to_char(sqlcode)||' error message '||SQLERRM);
129
130 END vr_upgrade_batch_process;
131
132
133
134 --------------------------------------------------------------------------------
135 --
136 -- NAME : process_vr_upgrade
137 -- DESCRIPTION :
138 -- PURPOSE :
139 -- INVOKED FROM : vr_upgrade_batch_process
140 -- ARGUMENTS :
141 -- REFERENCE : PN_COMMON.debug()
142 -- HISTORY :
143 --
144 -- 06-AUG-2007 piagrawa o Created
145 --------------------------------------------------------------------------------
146 PROCEDURE process_vr_upgrade( p_var_rent_id IN NUMBER)
147 IS
148
149
150 /* Check if invoices exist for this agreement */
151 CURSOR var_rent_cur IS
152 SELECT /*+ ROWID(vr) */ vr.var_rent_id, invoice_on
153 FROM pn_var_rents_all vr
154 WHERE vr.var_rent_id = p_var_rent_id
155 AND EXISTS (SELECT 1
156 FROM pn_var_rent_inv_all inv
157 WHERE inv.var_rent_id = p_var_rent_id
158 AND rownum=1);
159
160 /* Fetch the summary id and transaction id from respective tables */
161 CURSOR summ_id_cur (p_var_rent_id NUMBER) IS
162 SELECT /*+ leading(trx) */
163 summ.var_rent_summ_id, trx.trx_header_id
164 FROM pn_var_trx_headers_all trx, pn_var_rent_summ_all summ
165 WHERE trx.grp_date_id = summ.grp_date_id
166 AND trx.var_rent_id = p_var_rent_id;
167
168 TYPE summ_id_tab_type IS TABLE OF pn_var_trx_headers_all.var_rent_summ_id%TYPE
169 INDEX BY BINARY_INTEGER;
170
171 TYPE trx_id_tab_type IS TABLE OF pn_var_trx_headers_all.trx_header_id%TYPE
172 INDEX BY BINARY_INTEGER;
173
174 l_summ_id_tab summ_id_tab_type;
175 l_trx_id_tab trx_id_tab_type;
176
177 BEGIN
178
179 pnp_debug_pkg.log(' ++++++++++++++ process_vr_upgrade - START +++++++++++++');
180
181 FOR var_rent_rec IN var_rent_cur
182 LOOP
183
184 pnp_debug_pkg.log('var rent id ....'||var_rent_rec.var_rent_id);
185 pnp_debug_pkg.log('invoice on ....'||var_rent_rec.invoice_on);
186
187 l_summ_id_tab.DELETE;
188 l_trx_id_tab.DELETE;
189
190 pn_var_trx_pkg.populate_transactions(p_var_rent_id => p_var_rent_id);
191 pn_var_trx_pkg.populate_sales(p_var_rent_id => p_var_rent_id);
192
193 IF var_rent_rec.invoice_on = 'FORECASTED' THEN
194
195 /* after populating the sales, sales_vol_update_flag becomes NULL.
196 Therefore need to set it to 'Y' so that forecasted sales get populated */
197 UPDATE pn_var_lines_all
198 SET sales_vol_update_flag = 'Y'
199 WHERE var_rent_id = p_var_rent_id;
200
201 pn_var_trx_pkg.populate_sales_for(p_var_rent_id => p_var_rent_id);
202
203 END IF;
204
205 /* Fetch the mapping of trx headers and summary records */
206
207 OPEN summ_id_cur(p_var_rent_id => p_var_rent_id);
208 FETCH summ_id_cur BULK COLLECT INTO l_summ_id_tab,l_trx_id_tab;
209 CLOSE summ_id_cur;
210
211 FORALL i IN 1.. l_trx_id_tab.COUNT
212 UPDATE pn_var_trx_headers_all
213 SET var_rent_summ_id = l_summ_id_tab(i)
214 WHERE trx_header_id = l_trx_id_tab(i);
215
216 END LOOP;
217
218 pnp_debug_pkg.log(' ++++++++++++++ process_vr_upgrade - END +++++++++++++');
219 EXCEPTION
220
221 WHEN OTHERS THEN
222 raise_application_error ('-20001','Error ' || to_char(sqlcode)||' error message '||SQLERRM);
223 END process_vr_upgrade;
224
225 END PN_VAR_UPG_TRX_PKG;
226